From c10fc10d5fd307a7b334bcde6af5c1175aaa0410 Mon Sep 17 00:00:00 2001 From: king <18310653075@163.com> Date: 星期三, 28 八月 2024 17:47:28 +0800 Subject: [PATCH] 2024-08-28 --- src/utils/utils-custom.js | 1472 ++++++++++++++++++++++++++++++++++++++++++++++++++++----- 1 files changed, 1,323 insertions(+), 149 deletions(-) diff --git a/src/utils/utils-custom.js b/src/utils/utils-custom.js index e79674e..2d86e2d 100644 --- a/src/utils/utils-custom.js +++ b/src/utils/utils-custom.js @@ -1985,10 +1985,18 @@ let _quot = sql.match(/'{1}/g) let _lparen = sql.match(/\({1}/g) let _rparen = sql.match(/\){1}/g) + let _ch_b = sql.match(/\$check@/ig) + let _ch_d = sql.match(/@check\$/ig) + let _m_b = sql.match(/\$@/ig) + let _m_d = sql.match(/@\$/ig) _quot = _quot ? _quot.length : 0 _lparen = _lparen ? _lparen.length : 0 _rparen = _rparen ? _rparen.length : 0 + _ch_b = _ch_b ? _ch_b.length : 0 + _ch_d = _ch_d ? _ch_d.length : 0 + _m_b = _m_b ? _m_b.length : 0 + _m_d = _m_d ? _m_d.length : 0 if (_quot % 2 !== 0) { notification.warning({ @@ -2001,6 +2009,20 @@ notification.warning({ top: 92, message: 'sql涓�()蹇呴』鎴愬鍑虹幇', + duration: 5 + }) + return false + } else if (_ch_b !== _ch_d) { + notification.warning({ + top: 92, + message: 'sql涓� $check@ 涓� @check$ 蹇呴』鎴愬鍑虹幇', + duration: 5 + }) + return false + } else if (_m_b !== _m_d) { + notification.warning({ + top: 92, + message: 'sql涓� $@ 涓� @$ 蹇呴』鎴愬鍑虹幇', duration: 5 }) return false @@ -2855,14 +2877,22 @@ /** * @description 鑾峰彇鎵ц鑴氭湰 */ -export function getAllSqls (config) { +export function getAllSqls (conf) { + let config = fromJS(conf).toJS() + let sqls = [] let urlFields = config.urlFields let appType = sessionStorage.getItem('appType') let process = config.process === 'true' + if (urlFields) { + urlFields = urlFields.map(n => n.toLowerCase()) + } + let filterComponent = (components, mainSearch) => { components.forEach(item => { + item.$menuname = (config.MenuName || '') + '-' + (item.name || '') + if (item.type === 'tabs') { item.subtabs.forEach(tab => { let _mainSearch = mainSearch || [] @@ -2888,6 +2918,15 @@ if (appType === 'mob' && item.type !== 'search' && item.type !== 'topbar' && item.search && item.search.length > 0) { item.search = [] } + if (appType !== 'mob' && item.search && item.search.length > 0) { + item.search.forEach(cell => { + if (['select', 'link', 'multiselect', 'checkcard', 'radio'].includes(cell.type) && cell.resourceType === '1' && cell.dataSource) { + let msg = getFormSql(cell) + + sqls.push({uuid: cell.uuid, type: 'sForm', ...msg}) + } + }) + } if (item.subtype === 'tablecard') { // 鍏煎 item.type = 'card' @@ -2901,7 +2940,7 @@ let msg = getDataSource(item, mainSearch) - sqls.push({uuid: item.uuid, ...msg}) + sqls.push({uuid: item.uuid, type: 'datasource', ...msg}) } else if (item.setting && item.setting.useMSearch === 'true') { let searches = item.search || [] if (mainSearch.length > 0) { @@ -2930,6 +2969,24 @@ resetButton(item, cell) }) + } else if (item.subtype === 'editable' && col.editable === 'true') { + if (col.editType === 'select' && col.resourceType === '1') { + let msg = getFormSql(col) + + sqls.push({uuid: col.uuid, type: 'tbForm', ...msg}) + } else if (col.editType === 'popSelect') { + if (col.pops) { + col.pops.forEach(n => { + let msg = getPopSelectSql(n) + + sqls.push({uuid: n.uuid, type: 'popSource', ...msg}) + }) + } else { + let msg = getPopSelectSql(col) + + sqls.push({uuid: col.uuid, type: 'popSource', ...msg}) + } + } } }) } @@ -2937,7 +2994,9 @@ getCols(item.cols) if (item.subtype === 'editable' && item.submit.intertype === 'system') { - // let sql = this.getEditTableSql(item.submit, item.cols, item.columns) + let msg = getEditTableSql(item.submit, item.cols, item.columns) + + sqls.push({uuid: 'submit_' + item.uuid, type: 'editable', ...msg}) } } else if (item.type === 'card' || item.type === 'carousel' || item.type === 'timeline') { item.subcards && item.subcards.forEach(card => { @@ -2989,61 +3048,84 @@ let msg = getSysDefaultSql(cell, _item) - sqls.push({uuid: cell.uuid, ...msg}) + sqls.push({uuid: cell.uuid, type: 'button', ...msg}) } else { let msg = getSysDefaultSql(cell, item) - sqls.push({uuid: cell.uuid, ...msg}) + sqls.push({uuid: cell.uuid, type: 'button', ...msg}) } } if (cell.callbackType === 'script' && cell.verify && cell.verify.cbScripts) { - // if (item.subtype === 'dualdatacard' && isback) { - // let _item = fromJS(item).toJS() - // _item.columns = _item.subColumns || [] - // _item.setting.primaryKey = _item.setting.subKey + if (item.subtype === 'dualdatacard' && isback) { + let _item = fromJS(item).toJS() + _item.columns = _item.subColumns || [] - // getSysDefaultSql(cell, _item, true) - // } else { - - // getSysDefaultSql(cell, item, true) - // } + let msg = getSysBackSql(cell, _item) - // let backSql = this.getSysBackSql(cell.verify.cbScripts, cell.cbTable) + sqls.push({uuid: 'back_' + cell.uuid, type: 'btnCallBack', ...msg}) + } else { + let msg = getSysBackSql(cell, item) - // if (backSql) { - // backSql = `${defSql} - // ${backSql} - // ` - - // if (cell.output) { - // backSql += ` - // aaa: select @ErrorCode as ErrorCode,@retmsg as retmsg,${cell.output} as mk_b_id` - // } else { - // backSql += ` - // aaa: select @ErrorCode as ErrorCode,@retmsg as retmsg` - // } - - // backSql = backSql.replace(/@typename@/ig, `'typename'`) - // backSql = backSql.replace(/@\$|\$@/ig, '').replace(/@datam@/ig, `''`) - // } + sqls.push({uuid: 'back_' + cell.uuid, type: 'btnCallBack', ...msg}) + } + } + if (['pop', 'formSubmit'].includes(cell.OpenType) && cell.modal && cell.modal.fields) { + cell.modal.fields.forEach(form => { + // 鏁版嵁婧恠ql璇彞锛岄澶勭悊锛屾潈闄愰粦鍚嶅崟瀛楁璁剧疆涓洪殣钘忚〃鍗� + if (['select', 'link', 'multiselect', 'radio', 'checkbox', 'checkcard'].includes(form.type) && form.resourceType === '1') { + let msg = getFormSql(form) + + sqls.push({uuid: form.uuid, type: 'form', ...msg}) + } else if (form.type === 'popSelect') { + let msg = getPopSelectSql(form) + + sqls.push({uuid: form.uuid, type: 'popSource', ...msg}) + } + }) } } else if (cell.OpenType === 'excelIn') { if (cell.intertype === 'system') { - // sql = this.getExcelInSql(cell, process) + let msg = getExcelInSql(cell) + + sqls.push({uuid: cell.uuid, type: 'excelIn', ...msg}) } } else if (cell.OpenType === 'excelOut') { if (cell.intertype === 'system' && cell.verify && cell.verify.dataType === 'custom') { - // sql = this.getExcelOutSql(cell, item) + let msg = getExcelOutSql(cell, item) + + sqls.push({uuid: cell.uuid, type: 'excelOut', ...msg}) } } else if (cell.OpenType === 'funcbutton') { if (cell.funcType === 'print') { - + if (cell.intertype === 'system' && cell.verify && cell.verify.dataType === 'custom') { + let msg = getPrintSql(cell) + + sqls.push({uuid: cell.uuid, type: 'print', ...msg}) + } + if (cell.execMode === 'pop' && cell.modal && cell.modal.fields) { + cell.modal.fields.forEach(form => { + // 鏁版嵁婧恠ql璇彞锛岄澶勭悊锛屾潈闄愰粦鍚嶅崟瀛楁璁剧疆涓洪殣钘忚〃鍗� + if (['select', 'link', 'multiselect', 'radio', 'checkbox', 'checkcard'].includes(form.type) && form.resourceType === '1') { + let msg = getFormSql(form) + + sqls.push({uuid: form.uuid, type: 'form', ...msg}) + } else if (form.type === 'popSelect') { + let msg = getPopSelectSql(form) + + sqls.push({uuid: form.uuid, type: 'popSource', ...msg}) + } + }) + } } else if ((cell.funcType === 'refund' || cell.funcType === 'pay') && cell.payMode === 'system') { - // sql = this.getPaySql(cell, item) + let msg = getPaySql(cell, item) + + sqls.push({uuid: cell.uuid, type: 'pay', ...msg}) } } else if (cell.OpenType === 'innerpage' || cell.OpenType === 'outerpage') { if (cell.pageTemplate === 'pay' && cell.payMode === 'system') { - // sql = this.getPaySql(cell, item) + let msg = getPaySql(cell, item) + + sqls.push({uuid: cell.uuid, type: 'pay', ...msg}) } } else if (cell.OpenType === 'popview') { if (cell.config && cell.config.components) { @@ -3077,7 +3159,6 @@ let getSearches = (searches) => { let sFields = [] - let query = false searches.forEach(item => { if (!item.field) return @@ -3119,13 +3200,9 @@ } else { sFields.push(item.field) } - - if (query) return - - query = item.query !== 'false' }) - return { sFields, query } + return sFields } let getSysDefaultSql = (btn, component) => { @@ -3151,6 +3228,11 @@ verify.invalid = 'false' } else if (btn.Ot === 'notRequired') { verify.invalid = 'false' + } + } + if (verify.uniques && verify.uniques.length > 0 && btn.Ot === 'requiredOnce') { + if (component.wrap && (component.wrap.datatype === 'static' || component.wrap.datatype === 'public')) { + verify.uniques = [] } } @@ -3203,7 +3285,8 @@ key: item.field, fieldlen: item.fieldlength || 50, writein: item.writein !== 'false', - type: item.type + type: item.type, + isconst: item.constant === 'true' } if (_item.type === 'datemonth') { @@ -3256,6 +3339,8 @@ _initFormfields.push(`@${_key}='@mk_${_key}_mk@'`) } else if (form.type === 'select' || form.type === 'link' || form.type === 'radio') { _initFormfields.push(`@${_key}='@mk_${_key}_mk@'`) + } else if (form.isconst) { + _initFormfields.push(`@${_key}=N'@mk_${_key}_mk@'`) } else { _initFormfields.push(`@${_key}='@mk_${_key}_mk@'`) } @@ -3364,7 +3449,7 @@ select @tbid='', @ErrorCode='',@retmsg='' select @tbid='X' from ${datasource} right join (select ID from dbo.SplitComma(@ID@)) sp on tb.${primaryKey} =sp.id where tb.${primaryKey} is null - + If @tbid!='' Begin select @ErrorCode='E',@retmsg='鏁版嵁宸插け鏁�' @@ -3521,9 +3606,9 @@ _sql += ` /* 鍚岀被鏁版嵁楠岃瘉 */ Set @tbid='' - + Select top 1 @tbid='X' from (select distinct ${item.field},1 as n from ${datasource} inner join (select ID from dbo.SplitComma(@ID@)) sp on tb.${primaryKey}=sp.ID ) a having sum(n)>1 - + If @tbid!='' Begin Set @ErrorCode='E' Set @retmsg='${item.fieldlabel} 鍊间笉鍞竴' @@ -3769,7 +3854,7 @@ select @works_flow_statuscharone=statuscharone,@works_flow_statuschartwo=statuschartwo,@works_flow_statuscharthree=statuscharthree,@works_flow_statuscharfour=statuscharfour,@works_flow_statuscharfive=statuscharfive,@works_flow_key_id=id,@s_my_works_flow_log_param=works_flow_param,@s_my_works_flow_log_status=status,@s_my_works_flow_log_statusname=statusname,@s_my_works_flow_log_detail_id=works_flow_detail_id from s_my_works_flow where works_flow_id=@ID@ and works_flow_code=@works_flow_code@ and deleted=0 - if @works_flow_statuscharone!='' and @works_flow_statuschartwo!='' and @works_flow_statuscharthree!='' and @works_flow_statuscharfour!='' and @works_flow_statuscharfive!='' + if @works_flow_statuscharone + @works_flow_statuschartwo + @works_flow_statuscharthree + @works_flow_statuscharfour + @works_flow_statuscharfive = @works_flow_sign_values@ begin set @works_flow_key_status='Y' end @@ -3895,15 +3980,24 @@ } if (verify.workFlow === 'true' && process) { - // works_flow_error 娴佺▼閿欒 - // works_flow_countersign 浼氱/鎴栫 鏍囪 - // works_begin_branch 椹冲洖鑷冲紑濮嬪垎鏀紙line.mknode === 'startEdge'锛� - // works_flow_sign_field 浼氱 鏍囪瀛楁 statuscharone/statuschartwo/statuscharthree/statuscharfour/statuscharfive - // works_flow_sign_label 浼氱 鏍囪鍊� ***/***/宸插鏍� - let worksReFields = ['works_flow_error', 'works_flow_countersign', 'works_begin_branch', 'works_flow_sign_label', 'start_type', 'check_type', 'notice_type', 'check_userids', 'notice_userids', 'works_flow_sign', 'works_flow_code', 'works_flow_name', 'works_flow_param', 'works_flow_detail_id', 'status', 'statusname', 'work_group', 'work_grade'] - worksReFields.forEach(n => { - _sql = _sql.replace(new RegExp('@' + n + '@', 'ig'), `'@${n}@'`) - }) + if (verify.flowType === 'start') { + // works_flow_error 娴佺▼閿欒 + let worksReFields = ['works_flow_error', 'works_flow_code', 'works_flow_name', 'works_flow_param', 'works_flow_detail_id', 'status', 'statusname', 'work_group', 'work_grade', 'start_type'] + worksReFields.forEach(n => { + _sql = _sql.replace(new RegExp('@' + n + '@', 'ig'), `'@${n}@'`) + }) + } else { + // works_flow_error 娴佺▼閿欒 + // works_flow_countersign 浼氱/鎴栫鏍囪 浼氱涓� Y + // works_begin_branch 椹冲洖鑷冲紑濮嬪垎鏀紙line.mknode === 'startEdge'锛� + // works_flow_sign_field 浼氱 鏍囪瀛楁 statuscharone/statuschartwo/statuscharthree/statuscharfour/statuscharfive + // works_flow_sign_label 浼氱 鏍囪鍊� ***/***/宸插鏍� + // works_flow_sign_values 浼氱鏍囪鎷兼帴鍊硷紙闄ゆ湰浜哄锛� + let worksReFields = ['works_flow_error', 'works_flow_countersign', 'works_flow_sign_values', 'works_begin_branch', 'works_flow_sign_label', 'works_flow_code', 'works_flow_name', 'works_flow_param', 'works_flow_detail_id', 'status', 'statusname', 'work_group', 'work_grade', 'check_type', 'notice_type', 'check_userids', 'notice_userids', 'works_flow_sign'] + worksReFields.forEach(n => { + _sql = _sql.replace(new RegExp('@' + n + '@', 'ig'), `'@${n}@'`) + }) + } } if (btn.procMode === 'system') { @@ -3973,6 +4067,14 @@ decSql = [...decSql, ..._declares] + _sql = `Declare ${decSql.join(',')} + /* 鍑瘉鍙婄敤鎴蜂俊鎭垵濮嬪寲璧嬪�� */ + select ${secSql.join(',')} + ${_sql} + ` + _sql = _sql.replace(/\n\s{8,10}/g, '\n') + _sql = _sql.replace(/\n\s+\/\*/g, `\n/*`) + let regs = ['ID', 'BID', 'time_id', 'datam', 'LoginUID', 'SessionUid', 'UserID', 'Appkey', 'lang', 'typename'] regs.forEach(s => { @@ -3980,13 +4082,6 @@ reps.push(s) } }) - - _sql = `Declare ${decSql.join(',')} - /* 鍑瘉鍙婄敤鎴蜂俊鎭垵濮嬪寲璧嬪�� */ - select ${secSql.join(',')} - ${_sql} - ` - _sql = _sql.replace(/\n\s{8,10}/g, '\n') reps.forEach(n => { _sql = _sql.replace(new RegExp('@' + n + '@', 'ig'), `'@${n}@'`) @@ -4001,22 +4096,277 @@ _sql = _sql.replace(/\$check@/ig, '@mk_check_begin@').replace(/@check\$/ig, '@mk_check_end@') reps.push('mk_check_begin', 'mk_check_end') } + if (/@db@/ig.test(_sql)) { + reps.push('db') + } reps = Array.from(new Set([...reps, ...colreps])) return { LText: _sql, reps } } + let getSysBackSql = (btn, component) => { + let verify = btn.verify || {} + let columns = component.columns || [] + let colreps = [] // 寰呮浛鎹㈠彉閲忛泦 + let _prev = '' + let _back = '' + let tables = [] + let reps = [] + + verify.cbScripts.forEach(script => { + if (script.status === 'false') return + + if (/\s#[a-z0-9_]+(\s|\()/ig.test(script.sql)) { + tables.push(...script.sql.match(/\s#[a-z0-9_]+(\s|\()/ig)) + } + + if (script.position === 'front') { + _prev += ` + /* 鑷畾涔夎剼鏈� */ + ${script.sql} + ` + } else { + _back += ` + /* 鑷畾涔夎剼鏈� */ + ${script.sql} + ` + } + }) + + tables = tables.map(tb => tb.replace(/\s|\(/g, '')) + + // 闇�瑕佸0鏄庣殑鍙橀噺闆� + let _vars = ['tbid', 'errorcode', 'retmsg', 'billcode', 'bvoucher', 'fibvoucherdate', 'fiyear', 'username', 'fullname', 'modulardetailcode', 'roleid', 'mk_departmentcode', 'mk_organization', 'mk_user_type', 'mk_nation', 'mk_province', 'mk_city', 'mk_district', 'mk_address', 'mk_deleted', 'bid'] + + let _sql = '' + + let _initFormfields = [] + let _declares = [] + + let formdata = null + let formkeys = [] + if (btn.OpenType === 'pop' || btn.OpenType === 'formSubmit') { + formdata = [] + if (btn.modal && btn.modal.fields) { + btn.modal.fields.forEach(item => { + if (!item.field) return + + let _item = { + key: item.field, + fieldlen: item.fieldlength || 50, + writein: item.writein !== 'false', + type: item.type, + isconst: item.constant === 'true' + } + + if (_item.type === 'datemonth') { + _item.type = 'text' + } else if (_item.type === 'number' || _item.type === 'rate') { + _item.fieldlen = item.decimal || 0 + } else if (_item.type === 'date') { + _item.type = item.declareType === 'nvarchar(50)' ? 'text' : 'date' + } else if (_item.type === 'datetime') { + _item.type = 'date' + } else if (item.declare === 'decimal') { + _item.type = 'number' + _item.fieldlen = item.decimal || 0 + } + + formdata.push(_item) + }) + } + } else if (btn.OpenType === 'form') { + formdata = [] + + let item = { + type: 'text', + readin: true, + writein: true, + fieldlen: 50, + key: btn.field + } + if (btn.formType === 'counter') { + item.type = 'number' + item.fieldlen = 0 + } else if (btn.formType === 'switch' || btn.formType === 'radio') { + if (typeof(btn.openVal) === 'number') { + item.type = 'number' + item.fieldlen = 0 + } + } + formdata.push(item) + } + + // 鑾峰彇瀛楁閿�煎 + formdata && formdata.forEach(form => { + let _key = form.key.toLowerCase() + + if (!formkeys.includes(_key)) { + formkeys.push(_key) + if (form.type === 'number' || form.type === 'rate') { + _initFormfields.push(`@${_key}=@mk_${_key}_mk@`) + } else if (form.type === 'date') { + _initFormfields.push(`@${_key}='@mk_${_key}_mk@'`) + } else if (form.type === 'select' || form.type === 'link' || form.type === 'radio') { + _initFormfields.push(`@${_key}='@mk_${_key}_mk@'`) + } else if (form.isconst) { + _initFormfields.push(`@${_key}=N'@mk_${_key}_mk@'`) + } else { + _initFormfields.push(`@${_key}='@mk_${_key}_mk@'`) + } + } + + if (!_vars.includes(_key)) { + _vars.push(_key) + + if (form.fieldlen && form.fieldlen > 4000) { + form.fieldlen = 'max' + } + + let _type = `nvarchar(${form.fieldlen})` + + if (form.type.match(/date/ig)) { + _type = 'datetime' + } else if (form.type === 'number') { + _type = `decimal(18,${form.fieldlen})` + } else if (form.type === 'rate') { + _type = `decimal(18,2)` + } + + _declares.push(`@${_key} ${_type}`) + } + }) + + // 琛ㄥ崟鍙橀噺璧嬪�� + if (_initFormfields.length > 0) { + _sql += ` + /* 琛ㄥ崟鍙橀噺璧嬪�� */ + select ${_initFormfields.join(',')} + ` + } + + let testSql = _prev + _back + + // 娣诲姞鏁版嵁涓瓧娈碉紝琛ㄥ崟鍊间紭鍏�(鎸夐挳涓嶉�夎鎴栧琛屾嫾鎺ユ椂璺宠繃) + if (btn.Ot !== 'notRequired' && columns.length > 0) { + let _initColfields = [] + columns.forEach(col => { + let _key = col.field.toLowerCase() + + if (formkeys.includes(_key) || !new RegExp('@' + _key + '[^0-9a-z_]', 'ig').test(testSql)) return + if (_key === 'id' && !/@id[^0-9a-z_@]/ig.test(testSql)) return + + colreps.push(col.field) + + if (col.type === 'number') { + _initColfields.push(`@${_key}=@mk_${_key}_mk@`) + } else { + _initColfields.push(`@${_key}='@mk_${_key}_mk@'`) + } + + if (!_vars.includes(_key)) { + _declares.push(`@${_key} ${col.datatype || 'nvarchar(50)'}`) + } + }) + + // 鏄剧ず鍒楀彉閲忚祴鍊� + if (_initColfields.length > 0) { + _sql += ` + /* 鏄剧ず鍒楀彉閲忚祴鍊� */ + select ${_initColfields.join(',')} + ` + } + } + + _sql += ` + ${_prev} + /* 澶栭儴鎺ュ彛鍏ュ弬 */ + @mk_outer_params@ + ${_back} + ` + + if (btn.output) { + _sql += ` + aaa: select @ErrorCode as ErrorCode,@retmsg as retmsg,${btn.output} as mk_b_id` + } else { + _sql += ` + aaa: select @ErrorCode as ErrorCode,@retmsg as retmsg` + } + + let syses = ['tbid', 'BillCode', 'BVoucher', 'FIBVoucherDate', 'FiYear', 'ModularDetailCode', 'mk_deleted', 'bid', 'UserName', 'FullName', 'RoleID', 'mk_departmentcode', 'mk_organization', 'mk_user_type', 'mk_nation', 'mk_province', 'mk_city', 'mk_district', 'mk_address'] + let decSql = [`@ErrorCode nvarchar(50),@retmsg nvarchar(4000)`] + let secSql = [`@ErrorCode='',@retmsg=''`] + + syses.forEach(s => { + if (new RegExp('@' + s + '[^0-9a-z_]', 'ig').test(_sql)) { + if (['RoleID', 'mk_departmentcode', 'mk_organization'].includes(s)) { + decSql.push(`@${s} nvarchar(512)`) + } else if (['mk_address'].includes(s)) { + decSql.push(`@mk_address nvarchar(100)`) + } else if (['mk_deleted'].includes(s)) { + secSql.push(`@mk_deleted int`) + } else { + decSql.push(`@${s} nvarchar(50)`) + } + if (['tbid', 'BillCode', 'BVoucher', 'FIBVoucherDate', 'FiYear', 'ModularDetailCode'].includes(s)) { + secSql.push(`@${s}=''`) + } else if (['mk_deleted'].includes(s)) { + secSql.push(`@mk_deleted=1`) + } else if (['bid'].includes(s)) { + secSql.push(`@bid=@BID@`) + } else { + secSql.push(`@${s}=@${s}@`) + reps.push(s) + } + } + }) + + decSql = [...decSql, ..._declares] + + _sql = `Declare ${decSql.join(',')} + /* 鍒濆鍖栬祴鍊� */ + select ${secSql.join(',')} + ${_sql} + ` + + _sql = _sql.replace(/\n\s{8,10}/g, '\n') + _sql = _sql.replace(/\n\s+\/\*/g, `\n/*`) + + let regs = ['ID', 'BID', 'time_id', 'datam', 'LoginUID', 'SessionUid', 'UserID', 'Appkey', 'lang', 'typename'] + + regs.forEach(s => { + if (new RegExp('@' + s + '@', 'ig').test(_sql)) { + reps.push(s) + } + }) + + reps.forEach(n => { + _sql = _sql.replace(new RegExp('@' + n + '@', 'ig'), `'@${n}@'`) + }) + + if (/\$@/ig.test(_sql)) { + _sql = _sql.replace(/\$@/ig, '@datam_begin@').replace(/@\$/ig, '@datam_end@') + reps.push('datam_begin', 'datam_end') + } + if (/@db@/ig.test(_sql)) { + reps.push('db') + } + + reps = Array.from(new Set([...reps, ...colreps])) + + return { LText: _sql, reps, tbs: tables } + } + let getDataSource = (item, mainSearch = []) => { if (!item.setting || item.setting.interType !== 'system') return - item.setting.primaryKey = item.setting.primaryKey || 'ID' let searches = item.search || [] if (item.setting.useMSearch === 'true' && mainSearch.length > 0) { searches = [...searches, ...mainSearch] } item.$searches = fromJS(searches).toJS() - let { sFields, query } = getSearches(searches) + let sFields = getSearches(searches) let _columns = [] if (item.subtype === 'dualdatacard') { @@ -4043,8 +4393,18 @@ } }) + if (_customScript || _tailScript) { + _tailScript += `${_tailScript} + aaa: + if @ErrorCode!='' + insert into tmp_err_retmsg (ID, ErrorCode, retmsg, CreateUserID) select @time_id@,@ErrorCode, @retmsg,@UserID@ + ` + } + + let _search = '' if (item.setting.execute !== 'false') { _dataresource = item.setting.dataresource || '' + _search = '@mk_search@' } if (/\s/.test(_dataresource)) { @@ -4053,11 +4413,6 @@ item.setting.dataresource = _dataresource item.setting.customScript = _customScript - - let _search = '@search@' - if (item.setting.queryType === 'statistics' || !query) { - _search = '' - } let testSql = _dataresource + _customScript + _tailScript @@ -4065,9 +4420,6 @@ let secSql = [`@ErrorCode='',@retmsg =''`] let reps = [] - if (_customScript || _tailScript) { - reps.push('UserID', 'time_id') - } let syses = ['UserName', 'FullName', 'RoleID', 'mk_departmentcode', 'mk_organization', 'mk_user_type', 'mk_nation', 'mk_province', 'mk_city', 'mk_district', 'mk_address'] syses.forEach(s => { @@ -4088,7 +4440,7 @@ select ${secSql.join(',')}` // 涓嶉渶瑕佸崟寮曞彿锛歰rderBy銆乸ageSize銆乸ageIndex銆乨b - let regs = [...sFields, 'orderBy', 'pageSize', 'pageIndex', 'typename', 'datam', 'ID', 'BID', 'LoginUID', 'SessionUid', 'UserID', 'Appkey', 'lang', 'typename'] + let regs = [...sFields, 'orderBy', 'pageSize', 'pageIndex', 'ID', 'BID', 'time_id', 'LoginUID', 'SessionUid', 'UserID', 'Appkey', 'lang', 'datam', 'typename'] if (item.hasExtend) { regs.push('mk_time') @@ -4098,9 +4450,6 @@ } if (window.GLOB.getLocation) { regs.push('mk_longitude', 'mk_latitude') - } - if (window.GLOB.externalDatabase !== null) { - regs.push('db') } if (urlFields) { regs.push(...urlFields) @@ -4118,102 +4467,917 @@ let LText = '' let DateCount = '' if (_dataresource) { + /*system_query*/ if (/@pageSize@|@orderBy@|@mk_total/i.test(testSql)) { - LText = `/*system_query*/select ${arr_field} from ${_dataresource} ${_search} ` + LText = `select ${arr_field} from ${_dataresource} ${_search} ` } else if (item.setting.laypage === 'true' && item.setting.order) { - LText = `/*system_query*/select top @pageSize@ ${arr_field} from (select ${arr_field} ,ROW_NUMBER() over(order by @orderBy@) as rows from ${_dataresource} ${_search}) tmptable where rows > @mk_rows_begin@ order by tmptable.rows ` - reps.push('pageSize', 'orderBy', 'mk_rows_begin') + LText = `select top @pageSize@ ${arr_field} from (select ${arr_field} ,ROW_NUMBER() over(order by @orderBy@) as rows from ${_dataresource} ${_search}) tmptable where rows > @pageSize@ * (@pageIndex@ - 1) order by tmptable.rows ` + reps.push('pageSize', 'orderBy', 'pageIndex') if (item.subtype === 'dualdatacard') { - DateCount = `/*system_query*/select count(1) as total from (select distinct ${item.setting.primaryKey} from ${_dataresource} ${_search})a` + DateCount = `select count(1) as total from (select distinct ${item.setting.primaryKey || 'ID'} from ${_dataresource} ${_search})a` } else { - DateCount = `/*system_query*/select count(1) as total from ${_dataresource} ${_search}` + DateCount = `select count(1) as total from ${_dataresource} ${_search}` } } else if (item.setting.$top) { if (item.setting.order) { - LText = `/*system_query*/select top 1 ${arr_field} from ${_dataresource} ${_search} order by @orderBy@ ` + LText = `select top 1 ${arr_field} from ${_dataresource} ${_search} order by @orderBy@ ` reps.push('orderBy') } else { - LText = `/*system_query*/select top 1 ${arr_field} from ${_dataresource} ${_search} ` + LText = `select top 1 ${arr_field} from ${_dataresource} ${_search} ` } } else if (item.setting.order) { - LText = `/*system_query*/select ${arr_field} from ${_dataresource} ${_search} order by @orderBy@ ` + LText = `select ${arr_field} from ${_dataresource} ${_search} order by @orderBy@ ` reps.push('orderBy') } else { - LText = `/*system_query*/select ${arr_field} from ${_dataresource} ${_search} ` + LText = `select ${arr_field} from ${_dataresource} ${_search} ` } - } - - if (_customScript) { - _customScript = `${decSql} - ${_customScript} - ` - if (DateCount) { - DateCount = `${DateCount} - ${_tailScript} - aaa: - if @ErrorCode!='' - insert into tmp_err_retmsg (ID, ErrorCode, retmsg, CreateUserID) select @time_id@,@ErrorCode,@retmsg,@UserID@ - ` - } else if (LText) { - LText = `${LText} - ${_tailScript} - aaa: - if @ErrorCode!='' - insert into tmp_err_retmsg (ID, ErrorCode, retmsg, CreateUserID) select @time_id@,@ErrorCode,@retmsg,@UserID@ - ` - } else { - _customScript = `${_customScript} - ${_tailScript} - aaa: - if @ErrorCode!='' - insert into tmp_err_retmsg (ID, ErrorCode, retmsg, CreateUserID) select @time_id@,@ErrorCode, @retmsg,@UserID@ - ` - } - } else if (_tailScript && DateCount) { - LText = `${decSql} - ${LText} - ` - DateCount = `${DateCount} - ${_tailScript} - aaa: - if @ErrorCode!='' - insert into tmp_err_retmsg (ID, ErrorCode, retmsg, CreateUserID) select @time_id@,@ErrorCode, @retmsg,@UserID@ - ` - } else if (_tailScript) { - LText = `${decSql} - ${LText} - ${_tailScript} - aaa: - if @ErrorCode!='' - insert into tmp_err_retmsg (ID, ErrorCode, retmsg, CreateUserID) select @time_id@,@ErrorCode, @retmsg,@UserID@ - ` - } else { - LText = `${decSql} - ${LText} - ` } reps = Array.from(new Set(reps)) - LText = LText.replace(/\n\s{6,8}/g, '\n') - DateCount = DateCount.replace(/\n\s{6,8}/g, '\n') - _customScript = _customScript.replace(/\n\s{6,8}/g, '\n') + let sub_name = '' + let tabid = '' + let parid = '' + let sub_field = '' + + if (item.subtype === 'dualdatacard') { + arr_field = item.columns.map(col => col.field).join(',') + sub_name = item.setting.subdata + tabid = item.setting.primaryKey || '' + parid = item.setting.subBID || '' + sub_field = item.subColumns.map(col => col.field).join(',') + } + + let sql = `SELECT obj_name='@mk_obj_name@',prm_field='',str_field='', + arr_field='${arr_field}',tabid='${tabid}',parid='${parid}',sub_name='${sub_name}',sub_field='${sub_field}' + ` + + if (DateCount) { + sql += `UNION ALL + SELECT obj_name='DateCount',prm_field='total',str_field='', + arr_field='',tabid='',parid='',sub_name='',sub_field='' + ` + } + + sql += ` + ${decSql} + ${_customScript} + ${LText} + ${DateCount} + ${_tailScript} + ` reps.forEach(n => { - if (['orderBy', 'pageSize', 'pageIndex', 'mk_rows_begin'].includes(n)) return + if (['orderBy', 'pageSize', 'pageIndex'].includes(n)) return - LText = LText.replace(new RegExp('@' + n + '@', 'ig'), `'@${n}@'`) - DateCount = DateCount.replace(new RegExp('@' + n + '@', 'ig'), `'@${n}@'`) - _customScript = _customScript.replace(new RegExp('@' + n + '@', 'ig'), `'@${n}@'`) + sql = sql.replace(new RegExp('@' + n + '@', 'ig'), `'@${n}@'`) }) - if (/\$@/ig.test(testSql)) { - LText = LText.replace(/\$@/ig, '@datam_begin@').replace(/@\$/ig, '@datam_end@') - DateCount = DateCount.replace(/\$@/ig, '@datam_begin@').replace(/@\$/ig, '@datam_end@') - _customScript = _customScript.replace(/\$@/ig, '@datam_begin@').replace(/@\$/ig, '@datam_end@') + if (/\$@/ig.test(sql)) { + sql = sql.replace(/\$@/ig, '@datam_begin@').replace(/@\$/ig, '@datam_end@') reps.push('datam_begin', 'datam_end') } + if (/@db@/ig.test(sql)) { + reps.push('db') + } + reps.push('mk_obj_name') + + sql = sql.replace(/\n\x20{6,8}/g, '\n').replace(/\n{3,}/g, '\n\n').replace(/^\s+|\s+$/g, '') + + return {LText: sql, reps} + } + + let getExcelInSql = (item) => { + let btn = item.verify + let sheet = item.sheet.replace(/@db@/ig, '') + let database = '' + if (/@db@/ig.test(item.sheet)) { + database = '@db@' + } + + let sql = '' - return {LText, DateCount, customScript: _customScript, reps} + let _initCustomScript = '' // 鍒濆鍖栬剼鏈� + let _prevCustomScript = '' // 榛樿sql鍓嶆墽琛岃剼鏈� + let _backCustomScript = '' // 榛樿sql鍚庢墽琛岃剼鏈� + let _regs = [ + {reg: new RegExp('(^|\\s)@' + sheet + '(\\s|$)', 'ig'), value: ` #${sheet} `}, + {reg: new RegExp('(^|\\s)@' + sheet + '\\(', 'ig'), value: ` #${sheet}(`}, + {reg: new RegExp('(^|\\s)@' + sheet + '\\)', 'ig'), value: ` #${sheet})`}, + ] + + btn.scripts && btn.scripts.forEach(script => { + if (script.status === 'false') return + + let _sql = script.sql + + _regs.forEach(item => { + _sql = _sql.replace(item.reg, item.value) + }) + + if (script.position === 'init') { + _initCustomScript += ` + /* 鑷畾涔夎剼鏈� */ + ${_sql} + ` + } else if (script.position === 'front') { + _prevCustomScript += ` + /* 鑷畾涔夎剼鏈� */ + ${_sql} + ` + } else { + _backCustomScript += ` + /* 鑷畾涔夎剼鏈� */ + ${_sql} + ` + } + }) + + let _uniquesql = '' + if (btn.uniques && btn.uniques.length > 0) { + let textFields = [] + let numberFields = [] + let dateFields = [] + btn.columns.forEach((col) => { + if (/Nvarchar/ig.test(col.type)) { + textFields.push(col.Column) + } else if (/Decimal|int/ig.test(col.type)) { + numberFields.push(col.Column) + } else if (/date/ig.test(col.type)) { + dateFields.push(col.Column) + } + }) + btn.uniques.forEach(unique => { + if (unique.status === 'false' || !unique.verifyType) return + + let _fields = unique.field.split(',') + let _fields_ = _fields.map(_field => `a.${_field}=b.${_field}`) + _fields_ = _fields_.join(' and ') + + let _where = [] + _fields.forEach(f => { + if (textFields.includes(f)) { + _where.push(`${f}!=''`) + } else if (numberFields.includes(f)) { + _where.push(`${f}!=0`) + } else if (dateFields.includes(f)) { + _where.push(`${f}>'1949-10-01'`) + } + }) + _where = _where.length ? `where ${_where.join(' and ')} ` : '' + + if (unique.verifyType === 'logic' || unique.verifyType === 'logic_temp') { + _fields_ += ' and b.deleted=0' + } + + let _afields = [] + _fields = _fields.map(f => { + if (numberFields.includes(f)) { + _afields.push(`cast(a.${f} as nvarchar(50))`) + return `cast(${f} as nvarchar(50))` + } else if (dateFields.includes(f)) { + _afields.push(`CONVERT(nvarchar(50), a.${f}, 21)`) + return `CONVERT(nvarchar(50), ${f}, 21)` + } + _afields.push(`a.${f}`) + + return f + }) + + _uniquesql += ` + /* 閲嶅鎬ч獙璇� */ + Set @tbid='' + Select top 1 @tbid=${_fields.join('+\' \'+')} from (select 1 as n,${unique.field} from #${sheet} ) a group by ${unique.field} having sum(n)>1 + + If @tbid!='' + Begin + select @ErrorCode='${unique.errorCode}',@retmsg=@tbid+' 閲嶅' + goto aaa + end + + ${unique.verifyType.indexOf('temp') === -1 ? `Set @tbid='' + Select top 1 @tbid=${_afields.join('+\' \'+')} from ${_where ? `(select * from #${sheet} ${_where})` : `#${sheet}`} a Inner join ${sheet} b on ${_fields_} + + If @tbid!='' + Begin + select @ErrorCode='${unique.errorCode}',@retmsg=@tbid+' 涓庡凡鏈夋暟鎹噸澶�' + goto aaa + end` : ''} + ` + }) + } + + let declarefields = [] + let fields = [] + + btn.columns.forEach(col => { + if (col.import === 'false') return + + if (col.type === 'date') { + declarefields.push(`${col.Column} Nvarchar(50)`) + } else { + declarefields.push(`${col.Column} ${col.type}`) + } + fields.push(col.Column) + }) + + fields = fields.join(',') + + let _insert = '' + if (btn.default !== 'false') { + _insert = ` + /* 榛樿sql */ + Insert into ${database}${sheet} (${fields},createuserid,createuser,createstaff,bid) + Select ${fields},@UserID@,@username,@fullname,@BID@ From #${sheet} + ` + } + + sql = `create table #${sheet} (${declarefields.join(',')},jskey nvarchar(50),BID nvarchar(50)) + Declare @ErrorCode nvarchar(50),@retmsg nvarchar(4000),@tbid Nvarchar(512)@mk_init_declare@ + + Select @ErrorCode='',@retmsg=''@mk_init_select@ + ${_initCustomScript} + + Insert into #${sheet} (${fields},jskey,BID) + + /* excel鏁版嵁*/ + @mk_excel_data@ + + ${_uniquesql} + ${_prevCustomScript} + ${_insert}` + + if (btn.workFlow === 'true' && process) { + if (btn.flowSql === 'true') { + sql += ` + /* 宸ヤ綔娴佸紓甯竤ql */ + if @works_flow_error@ != '' + select @ErrorCode='E',@retmsg=@works_flow_error@ goto aaa + + /* 宸ヤ綔娴侀粯璁ql */ + insert into s_my_works_flow (works_flow_id,works_flow_code,works_flow_name,works_flow_param,status,statusname,work_group,works_flow_detail_id,work_grade,bid,createuserid,CreateUser,CreateStaff,upid) + select jskey,@works_flow_code@,@works_flow_name@,@works_flow_param@,@status@,@statusname@,@work_group@,@works_flow_detail_id@,@work_grade@,@bid@,@UserID@,@UserName,@FullName,@time_id@ + from #${sheet} + + insert into s_my_works_flow_log (works_flow_id,works_flow_code,works_flow_name,works_flow_param,status,statusname,works_flow_detail_id,work_group,work_grade,bid,createuserid,CreateUser,CreateStaff,upid) + select jskey,@works_flow_code@,@works_flow_name@ ,@works_flow_param@,@status@,@statusname@,@works_flow_detail_id@,@work_group@,@work_grade@,@bid@,@UserID@,@UserName,@FullName,@time_id@ + from #${sheet} + + insert into s_my_works_flow_notice (works_flow_id,works_flow_code,works_flow_detail_id,userid,notice_type,createuserid,CreateUser,CreateStaff,upid) + select jskey,@works_flow_code@,@works_flow_detail_id@,@userid@,@start_type@,@userid@,@UserName,@FullName,@time_id@ + from #${sheet} + + insert into s_my_works_flow_role (works_flow_id,works_flow_code,userid,works_flow_detail_id,createuserid,CreateUser,CreateStaff,upid,typecharone) + select jskey,@works_flow_code@,@userid@,@works_flow_detail_id@,@userid@,@UserName,@FullName,@time_id@,'begin' + from #${sheet} + ` + } + + sql += ` + ${_backCustomScript} + + drop table #${sheet} + + aaa: select @ErrorCode as ErrorCode,@retmsg as retmsg` + + // works_flow_error 娴佺▼閿欒 + let worksReFields = ['works_flow_error', 'works_flow_code', 'works_flow_name', 'works_flow_param', 'works_flow_detail_id', 'status', 'statusname', 'work_group', 'work_grade', 'start_type'] + worksReFields.forEach(n => { + sql = sql.replace(new RegExp('@' + n + '@', 'ig'), `'@${n}@'`) + }) + } else { + sql += ` + ${_backCustomScript} + + drop table #${sheet} + + aaa: select @ErrorCode as ErrorCode,@retmsg as retmsg` + } + + let reps = [] + let decSql = [] + let secSql = [] + + let syses = ['UserName', 'FullName', 'RoleID', 'mk_departmentcode', 'mk_organization', 'mk_user_type', 'mk_nation', 'mk_province', 'mk_city', 'mk_district', 'mk_address'] + syses.forEach(s => { + if (new RegExp('@' + s + '[^0-9a-z_]', 'ig').test(sql)) { + if (['RoleID', 'mk_departmentcode', 'mk_organization'].includes(s)) { + decSql.push(`@${s} nvarchar(512)`) + } else if (['mk_address'].includes(s)) { + decSql.push(`@mk_address nvarchar(100)`) + } else { + decSql.push(`@${s} nvarchar(50)`) + } + secSql.push(`@${s}=@${s}@`) + reps.push(s) + } + }) + + decSql = decSql.length ? `,${decSql.join(',')}` : '' + secSql = secSql.length ? `,${secSql.join(',')}` : '' + + sql = sql.replace(/@mk_init_declare@/ig, decSql) + sql = sql.replace(/@mk_init_select@/ig, secSql) + + let regs = ['ID', 'BID', 'time_id', 'datam', 'LoginUID', 'SessionUid', 'UserID', 'Appkey', 'lang', 'typename'] + + regs.forEach(s => { + if (new RegExp('@' + s + '@', 'ig').test(sql)) { + reps.push(s) + } + }) + + reps.forEach(n => { + sql = sql.replace(new RegExp('@' + n + '@', 'ig'), `'@${n}@'`) + }) + + sql = sql.replace(/\n\s{6,10}/g, '\n') + sql = sql.replace(/\n\s+\/\*/g, `\n/*`) + + if (/\$@/ig.test(sql)) { + sql = sql.replace(/\$@/ig, '@datam_begin@').replace(/@\$/ig, '@datam_end@') + reps.push('datam_begin', 'datam_end') + } + if (/@db@/ig.test(sql)) { + reps.push('db') + } + + return {LText: sql, reps} + } + + let getEditTableSql = (btn, cols, columns) => { + let sheet = btn.sheet.replace(/@db@/ig, '') + let database = '' + if (/@db@/ig.test(btn.sheet)) { + database = '@db@' + } + + let forms = [] + let _fields = {} + + columns.forEach(col => { + _fields[col.field] = col.datatype + }) + + let getColumns = (cols) => { + cols.forEach(item => { + if (item.type === 'colspan') { + getColumns(item.subcols) + } else if (item.editable === 'true') { + forms.push({ + field: item.field, + type: item.type, + datatype: _fields[item.field] || 'nvarchar(50)' + }) + } + }) + } + + getColumns(cols) + + let sql = '' + + let _initCustomScript = '' // 鍒濆鍖栬剼鏈� + let _prevCustomScript = '' // 榛樿sql鍓嶆墽琛岃剼鏈� + let _backCustomScript = '' // 榛樿sql鍚庢墽琛岃剼鏈� + let _regs = [ + {reg: new RegExp('(^|\\s)@' + sheet + '(\\s|$)', 'ig'), value: ` #${sheet} `}, + {reg: new RegExp('(^|\\s)@' + sheet + '\\(', 'ig'), value: ` #${sheet}(`}, + {reg: new RegExp('(^|\\s)@' + sheet + '\\)', 'ig'), value: ` #${sheet})`}, + ] + + btn.scripts && btn.scripts.forEach(script => { + if (script.status === 'false') return + + let _sql = script.sql + + _regs.forEach(item => { + _sql = _sql.replace(item.reg, item.value) + }) + + if (script.position === 'init') { + _initCustomScript += ` + /* 鑷畾涔夎剼鏈� */ + ${_sql} + ` + } else if (script.position === 'front') { + _prevCustomScript += ` + /* 鑷畾涔夎剼鏈� */ + ${_sql} + ` + } else { + _backCustomScript += ` + /* 鑷畾涔夎剼鏈� */ + ${_sql} + ` + } + }) + + let _uniquesql = '' + if (btn.uniques && btn.uniques.length > 0) { + let textFields = [] + let numberFields = [] + let dateFields = [] + columns.forEach((col) => { + if (/Nvarchar/ig.test(col.datatype)) { + textFields.push(col.field) + } else if (/Decimal|int/ig.test(col.datatype)) { + numberFields.push(col.field) + } else if (/date/ig.test(col.datatype)) { + dateFields.push(col.field) + } + }) + btn.uniques.forEach(unique => { + if (unique.status === 'false' || !unique.verifyType) return + + let _fields = unique.field.split(',') + let _fields_ = _fields.map(_field => `a.${_field}=b.${_field}`) + _fields_ = _fields_.join(' and ') + + let _where = [] + _fields.forEach(f => { + if (textFields.includes(f)) { + _where.push(`${f}!=''`) + } else if (numberFields.includes(f)) { + _where.push(`${f}!=0`) + } else if (dateFields.includes(f)) { + _where.push(`${f}>'1949-10-01'`) + } + }) + _where = _where.length ? `where ${_where.join(' and ')} ` : '' + + if (unique.verifyType === 'logic' || unique.verifyType === 'logic_temp') { + _fields_ += ' and b.deleted=0' + } + + let _afields = [] + _fields = _fields.map(f => { + if (numberFields.includes(f)) { + _afields.push(`cast(a.${f} as nvarchar(50))`) + return `cast(${f} as nvarchar(50))` + } else if (dateFields.includes(f)) { + _afields.push(`CONVERT(nvarchar(50), a.${f}, 21)`) + return `CONVERT(nvarchar(50), ${f}, 21)` + } + _afields.push(`a.${f}`) + + return f + }) + + _uniquesql += ` + /* 閲嶅鎬ч獙璇� */ + Set @tbid='' + Select top 1 @tbid=${_fields.join('+\' \'+')} from (select 1 as n,${unique.field} from #${sheet} ) a group by ${unique.field} having sum(n)>1 + + If @tbid!='' + Begin + select @ErrorCode='${unique.errorCode}',@retmsg=@tbid+' 閲嶅' + goto aaa + end + + ${unique.verifyType.indexOf('temp') === -1 ? `Set @tbid='' + Select top 1 @tbid=${_afields.join('+\' \'+')} from ${_where ? `(select * from #${sheet} ${_where})` : `#${sheet}`} a Inner join ${sheet} b on ${_fields_} + + If @tbid!='' + Begin + select @ErrorCode='${unique.errorCode}',@retmsg=@tbid+' 涓庡凡鏈夋暟鎹噸澶�' + goto aaa + end` : ''} + ` + }) + } + + let declarefields = [] + let fields = [] + + forms.forEach(col => { + let key = col.field.toLowerCase() + if (key === 'jskey' || key === 'bid' || key === 'data_type') return + + declarefields.push(`${col.field} ${col.datatype}`) + fields.push(col.field) + }) + + fields = fields.join(',') + + let _insert = '' + if (btn.default !== 'false') { + _insert = ` + /* 榛樿sql */ + Insert into ${database}${sheet} (${fields},createuserid,createuser,createstaff,bid) + Select ${fields},@UserID@,@username,@fullname,@BID@ From #${sheet} + ` + } + + sql = `create table #${sheet} (${declarefields.join(',')},jskey nvarchar(50),data_type nvarchar(50),BID nvarchar(256)) + Declare @ErrorCode nvarchar(50),@retmsg nvarchar(4000),@tbid Nvarchar(512)@mk_init_declare@ + Select @ErrorCode='',@retmsg=''@mk_init_select@ + + ${_initCustomScript} + Insert into #${sheet} (${fields},jskey,data_type,BID) + + /* table鏁版嵁*/ + @mk_excel_data@ + + ${_uniquesql} + ${_prevCustomScript} + ${_insert} + ${_backCustomScript} + + drop table #${sheet} + + aaa: select @ErrorCode as ErrorCode,@retmsg as retmsg` + + let reps = [] + let decSql = [] + let secSql = [] + + let syses = ['UserName', 'FullName', 'RoleID', 'mk_departmentcode', 'mk_organization', 'mk_user_type', 'mk_nation', 'mk_province', 'mk_city', 'mk_district', 'mk_address'] + syses.forEach(s => { + if (new RegExp('@' + s + '[^0-9a-z_]', 'ig').test(sql)) { + if (['RoleID', 'mk_departmentcode', 'mk_organization'].includes(s)) { + decSql.push(`@${s} nvarchar(512)`) + } else if (['mk_address'].includes(s)) { + decSql.push(`@mk_address nvarchar(100)`) + } else { + decSql.push(`@${s} nvarchar(50)`) + } + secSql.push(`@${s}=@${s}@`) + reps.push(s) + } + }) + + decSql = decSql.length ? `,${decSql.join(',')}` : '' + secSql = secSql.length ? `,${secSql.join(',')}` : '' + + sql = sql.replace(/@mk_init_declare@/ig, decSql) + sql = sql.replace(/@mk_init_select@/ig, secSql) + + let regs = ['BID', 'time_id', 'datam', 'LoginUID', 'SessionUid', 'UserID', 'Appkey', 'lang', 'typename'] + + regs.forEach(s => { + if (new RegExp('@' + s + '@', 'ig').test(sql)) { + reps.push(s) + } + }) + + reps.forEach(n => { + sql = sql.replace(new RegExp('@' + n + '@', 'ig'), `'@${n}@'`) + }) + + sql = sql.replace(/\n\s{6,10}/g, '\n') + sql = sql.replace(/\n\s+\/\*/g, `\n/*`) + + if (/\$@/ig.test(sql)) { + sql = sql.replace(/\$@/ig, '@datam_begin@').replace(/@\$/ig, '@datam_end@') + reps.push('datam_begin', 'datam_end') + } + if (/@db@/ig.test(sql)) { + reps.push('db') + } + + return {LText: sql, reps} + } + + let getExcelOutSql = (btn, component) => { + let item = {setting: {}, columns: [], search: [], useMSearch: 'false'} + + btn.verify.columns.forEach(col => { + if (col.output === 'false' || !col.Column || col.Column === '$Index') return + item.columns.push({ + field: col.Column + }) + }) + + if (btn.verify.useSearch !== 'false') { + item.search = component.$searches + } + + item.setting.interType = 'system' + item.setting.execute = btn.verify.defaultSql || 'true' + item.setting.dataresource = btn.verify.dataresource || '' + item.setting.queryType = btn.verify.queryType + item.setting.laypage = btn.pagination + item.setting.order = btn.verify.order || '' + + if (btn.Ot === 'requiredOnce') { + item.setting.primaryKey = btn.verify.primaryKey || component.setting.primaryKey || 'ID' + } + + item.scripts = btn.verify.scripts || [] + + let msg = getDataSource(item, []) + + return msg + } + + let getPrintSql = (btn) => { + let item = {setting: {}, columns: btn.verify.columns || [], search: [], useMSearch: 'false'} + + item.setting.interType = 'system' + item.setting.execute = btn.verify.setting.defaultSql || 'true' + item.setting.dataresource = btn.verify.setting.dataresource || '' + item.setting.queryType = btn.verify.setting.queryType + item.setting.laypage = 'false' + item.setting.order = btn.verify.setting.order || '' + + item.scripts = btn.verify.scripts || [] + + let msg = getDataSource(item, []) + + return msg + } + + let getPaySql = (btn, component) => { + let _sql = `Declare @ErrorCode nvarchar(50),@retmsg nvarchar(4000),@tbid nvarchar(50)@mk_init_declare@ + Select @ErrorCode='',@retmsg=''@mk_init_select@ + ` + + btn.verify.scripts.forEach(item => { + if (item.status === 'false') return + + _sql += ` + ${item.sql} + ` + }) + + if (btn.output) { + _sql += ` + aaa: select @ErrorCode as ErrorCode,@retmsg as retmsg,${btn.output} as mk_b_id` + } else { + _sql += ` + aaa: select @ErrorCode as ErrorCode,@retmsg as retmsg` + } + + let reps = [] + let decSql = [] + let secSql = [] + + let syses = ['UserName', 'FullName', 'RoleID', 'mk_departmentcode', 'mk_organization', 'mk_user_type', 'mk_nation', 'mk_province', 'mk_city', 'mk_district', 'mk_address', 'BID'] + syses.forEach(s => { + if (new RegExp('@' + s + '[^0-9a-z_]', 'ig').test(_sql)) { + if (['RoleID', 'mk_departmentcode', 'mk_organization'].includes(s)) { + decSql.push(`@${s} nvarchar(512)`) + } else if (['mk_address'].includes(s)) { + decSql.push(`@mk_address nvarchar(100)`) + } else { + decSql.push(`@${s} nvarchar(50)`) + } + secSql.push(`@${s}=@${s}@`) + reps.push(s) + } + }) + + let regs = ['ID', 'time_id', 'datam', 'LoginUID', 'SessionUid', 'UserID', 'Appkey', 'lang', 'typename'] + + regs.forEach(s => { + if (new RegExp('@' + s + '@', 'ig').test(_sql)) { + reps.push(s) + } + }) + + syses = syses.map(n => n.toLowerCase()) + syses.push('tbid') + + let colreps = [] + component.columns.forEach(col => { + let _key = col.field.toLowerCase() + + if (syses.includes(_key) || !new RegExp('@' + _key + '[^0-9a-z_]', 'ig').test(_sql)) return + if (_key === 'id' && !/@id[^0-9a-z_@]/ig.test(_sql)) return + + colreps.push(col.field) + + decSql.push(`@${col.field} ${col.datatype || 'nvarchar(50)'}`) + + if (col.type === 'number') { + secSql.push(`@${col.field}=@mk_${col.field}_mk@`) + } else { + secSql.push(`@${col.field}='@mk_${col.field}_mk@'`) + } + }) + + decSql = decSql.length ? `,${decSql.join(',')}` : '' + secSql = secSql.length ? `,${secSql.join(',')}` : '' + + _sql = _sql.replace(/@mk_init_declare@/ig, decSql) + _sql = _sql.replace(/@mk_init_select@/ig, secSql) + + reps.forEach(n => { + _sql = _sql.replace(new RegExp('@' + n + '@', 'ig'), `'@${n}@'`) + }) + + reps = [...reps, ...colreps] + + _sql = _sql.replace(/\n\s{6,8}/g, '\n') + + if (/\$@/ig.test(_sql)) { + _sql = _sql.replace(/\$@/ig, '@datam_begin@').replace(/@\$/ig, '@datam_end@') + reps.push('datam_begin', 'datam_end') + } + if (/@db@/ig.test(_sql)) { + reps.push('db') + } + + return {LText: _sql, reps} + } + + let getFormSql = (item) => { + let arrfield = [item.valueField, item.valueText] + + if (item.type === 'checkcard') { + arrfield = item.fields ? item.fields.map(f => f.field) : [] + arrfield.push(item.cardValField) + if (item.urlField) { + arrfield.push(item.urlField) + } else if (item.colorField) { + arrfield.push(item.colorField) + } else if (item.parentField) { + arrfield.push(item.parentField) + } + } + + if (item.linkField) { + arrfield.push(item.linkField) + } + if (['select', 'radio', 'link', 'checkcard'].includes(item.type) && item.linkSubField && item.linkSubField.length > 0) { + arrfield.push(...item.linkSubField) + } else if (item.type === 'text' && item.editType === 'select' && item.linkSubField && item.linkSubField.length > 0) { // 鍙紪杈戣〃 + arrfield.push(...item.linkSubField) + } + if (item.disableField) { + arrfield.push(item.disableField) + } + + arrfield = Array.from(new Set(arrfield)) + + let _datasource = item.dataSource + let sql = '' + + if (/\s/.test(_datasource)) { // 鎷兼帴鍒悕 + _datasource = '(' + _datasource + ') tb' + } + + arrfield = arrfield.join(',') + + if (item.orderBy) { + sql = `select distinct ${arrfield},${item.orderBy} as orderfield from ${_datasource} order by orderfield ${item.orderType}` + } else { + sql = `select distinct ${arrfield} from ${_datasource}` + } + + let reps = [] + + let decSql = [] + let secSql = [] + + let syses = ['mk_departmentcode', 'mk_organization', 'mk_user_type'] + syses.forEach(s => { + if (new RegExp('@' + s + '[^0-9a-z_]', 'ig').test(sql)) { + if (['mk_departmentcode', 'mk_organization'].includes(s)) { + decSql.push(`@${s} nvarchar(512)`) + } else { + decSql.push(`@${s} nvarchar(20)`) + } + secSql.push(`@${s}=@${s}@`) + reps.push(s) + } + }) + + decSql = decSql.join(',') + secSql = secSql.join(',') + + if (decSql) { + sql = `Declare ${decSql} select ${secSql} + ${sql} + ` + } + + let regs = ['ID', 'BID', 'datam', 'LoginUID', 'SessionUid', 'UserID', 'Appkey', 'lang'] + + regs.forEach(s => { + if (new RegExp('@' + s + '@', 'ig').test(sql)) { + reps.push(s) + } + }) + + reps.forEach(n => { + sql = sql.replace(new RegExp('@' + n + '@', 'ig'), `'@${n}@'`) + }) + + if (/\$@/ig.test(sql)) { + sql = sql.replace(/\$@/ig, '@datam_begin@').replace(/@\$/ig, '@datam_end@') + reps.push('datam_begin', 'datam_end') + } + if (/@db@/ig.test(sql)) { + reps.push('db') + } + + return {LText: sql, reps} + } + + let getPopSelectSql = (item) => { + let arrfield = item.columns.map(f => f.field) + + if (item.linkSubField && item.linkSubField.length > 0) { + item.linkSubField.forEach(n => { + if (!arrfield.includes(n)) { + arrfield.push(n) + } + }) + } + + arrfield = arrfield.join(',') + if (/\s/.test(item.dataSource)) { // 鎷兼帴鍒悕 + item.dataSource = '(' + item.dataSource + ') tb' + } + + let LText = '' + let DateCount = '' + let _search = '' + let reps = [] + let sFields = [] + + if (item.searchKey) { + _search = '@mk_search@' + sFields = item.searchKey.split(',') + } + + // 涓嶉渶瑕佸崟寮曞彿锛歰rderBy銆乸ageSize銆乸ageIndex銆乨b + let regs = [...sFields, 'orderBy', 'pageSize', 'pageIndex', 'ID', 'BID', 'time_id', 'LoginUID', 'SessionUid', 'UserID', 'Appkey', 'lang', 'datam'] + + regs.forEach(s => { + if (new RegExp('@' + s + '@', 'ig').test(item.dataSource)) { + reps.push(s) + } + }) + + let decSql = [] + let secSql = [] + + let syses = ['mk_departmentcode', 'mk_organization', 'mk_user_type'] + syses.forEach(s => { + if (new RegExp('@' + s + '[^0-9a-z_]', 'ig').test(item.dataSource)) { + if (['mk_departmentcode', 'mk_organization'].includes(s)) { + decSql.push(`@${s} nvarchar(512)`) + } else { + decSql.push(`@${s} nvarchar(20)`) + } + secSql.push(`@${s}=@${s}@`) + reps.push(s) + } + }) + + decSql = decSql.join(',') + secSql = secSql.join(',') + + if (decSql) { + decSql = `Declare ${decSql} select ${secSql}` + } + + if (item.laypage === 'true') { + /*system_query*/ + LText = `select top @pageSize@ ${arrfield} from (select ${arrfield} ,ROW_NUMBER() over(order by @orderBy@) as rows from ${item.dataSource} ${_search}) tmptable where rows > @pageSize@ * (@pageIndex@ - 1) order by tmptable.rows ` + DateCount = `select count(1) as total from ${item.dataSource} ${_search}` + + reps.push('pageSize', 'orderBy', 'pageIndex') + } else { + LText = `select ${arrfield} from ${item.dataSource} ${_search} order by @orderBy@ ` + reps.push('orderBy') + } + + let sql = `SELECT obj_name='data',prm_field='',str_field='', + arr_field='${arrfield}',tabid='',parid='',sub_name='',sub_field='' + ` + + if (DateCount) { + sql += `UNION ALL + SELECT obj_name='DateCount',prm_field='total',str_field='', + arr_field='',tabid='',parid='',sub_name='',sub_field='' + ` + } + + sql += ` + ${decSql} + ${LText} + ${DateCount} + ` + + reps.forEach(n => { + if (['orderBy', 'pageSize', 'pageIndex'].includes(n)) return + + sql = sql.replace(new RegExp('@' + n + '@', 'ig'), `'@${n}@'`) + }) + if (/\$@/ig.test(sql)) { + sql = sql.replace(/\$@/ig, '@datam_begin@').replace(/@\$/ig, '@datam_end@') + reps.push('datam_begin', 'datam_end') + } + if (/@db@/ig.test(sql)) { + reps.push('db') + } + + sql = sql.replace(/\n\x20{6,8}/g, '\n').replace(/\n{3,}/g, '\n\n').replace(/^\s+|\s+$/g, '') + + return {LText: sql, reps} } let _mainSearch = [] @@ -4250,6 +5414,16 @@ } search.push(...group.fields) }) + + if (search.length > 0) { + search.forEach(cell => { + if (['select', 'link', 'multiselect', 'checkcard', 'radio'].includes(cell.type) && cell.resourceType === '1' && cell.dataSource) { + let msg = getFormSql(cell) + + sqls.push({uuid: cell.uuid, type: 'sForm', ...msg}) + } + }) + } } if (search.length > 0) { @@ -4272,7 +5446,7 @@ let msg = getDataSource(m, _mainSearch) - sqls.push({uuid: m.uuid, ...msg}) + sqls.push({uuid: m.uuid, type: 'interface', ...msg}) }) } -- Gitblit v1.8.0