From c98e45bfac25e9110ad0383faac54a54d98ea9d5 Mon Sep 17 00:00:00 2001 From: king <18310653075@163.com> Date: 星期四, 18 十一月 2021 20:47:04 +0800 Subject: [PATCH] 2021-11-18 --- src/utils/utils.js | 275 ++++++++++++++++++++++++++++++++++++++++++++++++++++--- 1 files changed, 260 insertions(+), 15 deletions(-) diff --git a/src/utils/utils.js b/src/utils/utils.js index ca334ec..80283fb 100644 --- a/src/utils/utils.js +++ b/src/utils/utils.js @@ -248,6 +248,7 @@ item.hidden = item.Hide === 'true' item.required = !item.hidden && item.required === 'true' item.advanced = item.advanced === 'true' + item.$forbid = item.query === 'false' if (item.type === 'date') { // 鏃堕棿鎼滅储 item.initval = item.initval ? moment().subtract(item.initval, 'days').format('YYYY-MM-DD') : '' @@ -256,7 +257,11 @@ } else if (item.type === 'dateweek') { item.initval = item.initval ? moment().subtract(item.initval * 7, 'days').format('YYYY-MM-DD') : '' } else if (item.type === 'daterange') { - if (item.initval) { + if (item.initval === 'week') { + item.initval = [moment().startOf('week').format('YYYY-MM-DD'), moment().endOf('week').format('YYYY-MM-DD')].join(',') + } else if (item.initval === 'month') { + item.initval = [moment().startOf('month').format('YYYY-MM-DD'), moment().endOf('month').format('YYYY-MM-DD')].join(',') + } else if (item.initval) { try { let _initval = JSON.parse(item.initval) let _vals = [moment().subtract(_initval[0], 'days').format('YYYY-MM-DD'), moment().subtract(_initval[1], 'days').format('YYYY-MM-DD')] @@ -352,7 +357,8 @@ type: cell.type, label: cell.label, value: cell.initval, - required: cell.required + required: cell.required, + forbid: cell.$forbid } if (cell.type === 'multiselect' || (cell.type === 'checkcard' && cell.multiple === 'true')) { @@ -703,14 +709,15 @@ if (item.type === 'link') { arrfield.push(item.linkField) - } else if ((item.type === 'select' || item.type === 'radio') && item.linkSubField && item.linkSubField.length > 0) { - arrfield.push(...item.linkSubField) } else if (item.type === 'checkcard') { arrfield = item.fields.map(f => f.field) arrfield.push(item.cardValField) if (item.urlField) { arrfield.push(item.urlField) } + } + if (['select', 'radio', 'link', 'checkcard'].includes(item.type) && item.linkSubField && item.linkSubField.length > 0) { + arrfield.push(...item.linkSubField) } if (item.disableField) { arrfield.push(item.disableField) @@ -728,9 +735,11 @@ arrfield = arrfield.join(',') if (item.orderBy) { - sql = `select ${item.type === 'checkcard' ? 'top 20' : ''} ${arrfield} from (select distinct ${arrfield},${item.orderBy} as orderfield from ${_datasource} ) a order by orderfield ${item.orderType}` + // sql = `select ${item.type === 'checkcard' ? 'top 20' : ''} ${arrfield} from (select distinct ${arrfield},${item.orderBy} as orderfield from ${_datasource} ) a order by orderfield ${item.orderType}` + sql = `select distinct ${arrfield},${item.orderBy} as orderfield from ${_datasource} order by orderfield ${item.orderType}` } else { - sql = `select ${item.type === 'checkcard' ? 'top 20' : ''} ${arrfield} from (select distinct ${arrfield} from ${_datasource}) a` + // sql = `select ${item.type === 'checkcard' ? 'top 20' : ''} ${arrfield} from (select distinct ${arrfield} from ${_datasource}) a` + sql = `select distinct ${arrfield} from ${_datasource}` } if (sessionStorage.getItem('dataM') === 'true') { // 鏁版嵁鏉冮檺 @@ -1018,9 +1027,9 @@ _sql = ` /* 绯荤粺鐢熸垚 */ declare @${sheet} table (${declarefields.join(',')},jskey nvarchar(50),BID nvarchar(50) ) - Declare @UserName nvarchar(50),@FullName nvarchar(50),@RoleID nvarchar(512),@departmentcode nvarchar(50),@organization nvarchar(50),@login_city nvarchar(50),@ErrorCode nvarchar(50),@retmsg nvarchar(4000),@tbid Nvarchar(512) + Declare @UserName nvarchar(50),@FullName nvarchar(50),@RoleID nvarchar(512),@mk_departmentcode nvarchar(50),@mk_organization nvarchar(50),@login_city nvarchar(50),@ErrorCode nvarchar(50),@retmsg nvarchar(4000),@tbid Nvarchar(512) - Select @ErrorCode='', @retmsg='', @UserName='${userName}', @FullName='${fullName}', @RoleID='${RoleID}', @departmentcode='${departmentcode}', @organization='${organization}', @login_city='${city}' + Select @ErrorCode='', @retmsg='', @UserName='${userName}', @FullName='${fullName}', @RoleID='${RoleID}', @mk_departmentcode='${departmentcode}', @mk_organization='${organization}', @login_city='${city}' ${_initCustomScript} ` _sqlInsert = `Insert into @${sheet} (${fields},jskey,BID)` @@ -1053,9 +1062,9 @@ _sql = ` /* 绯荤粺鐢熸垚 */ declare @${sheet} table (jskey nvarchar(50)) - Declare @UserName nvarchar(50),@FullName nvarchar(50),@RoleID nvarchar(512),@departmentcode nvarchar(50),@organization nvarchar(50),@login_city nvarchar(50),@ErrorCode nvarchar(50),@retmsg nvarchar(4000),@tbid Nvarchar(512) + Declare @UserName nvarchar(50),@FullName nvarchar(50),@RoleID nvarchar(512),@mk_departmentcode nvarchar(50),@mk_organization nvarchar(50),@login_city nvarchar(50),@ErrorCode nvarchar(50),@retmsg nvarchar(4000),@tbid Nvarchar(512) - Select @ErrorCode='', @retmsg='', @UserName='${userName}', @FullName='${fullName}', @RoleID='${RoleID}', @departmentcode='${departmentcode}', @organization='${organization}', @login_city='${city}' + Select @ErrorCode='', @retmsg='', @UserName='${userName}', @FullName='${fullName}', @RoleID='${RoleID}', @mk_departmentcode='${departmentcode}', @mk_organization='${organization}', @login_city='${city}' ` } @@ -1070,6 +1079,236 @@ insert: _sqlInsert, bottom: _sqlBottom, errors: errors.join('; ') + } +} + +/** + * @description 鑾峰彇excel瀵煎叆鍙傛暟 + * @return {Object} item 鎸夐挳淇℃伅 + * @return {Array} data excel鏁版嵁 + */ +export function getEditTableSql (verify, data, columns) { + let btn = verify + let userName = sessionStorage.getItem('User_Name') || '' + let fullName = sessionStorage.getItem('Full_Name') || '' + let RoleID = sessionStorage.getItem('role_id') || '' + let departmentcode = sessionStorage.getItem('departmentcode') || '' + let organization = sessionStorage.getItem('organization') || '' + let city = sessionStorage.getItem('city') || '' + let _sheet = btn.sheet + let BID = data[0].$$BID || '' + + if (sessionStorage.getItem('isEditState') === 'true') { + userName = sessionStorage.getItem('CloudUserName') || '' + fullName = sessionStorage.getItem('CloudFullName') || '' + } + + if (window.GLOB.externalDatabase !== null) { + _sheet = _sheet.replace(/@db@/ig, window.GLOB.externalDatabase) + } + + let database = _sheet.match(/(.*)\.(.*)\./ig) + let sheet = _sheet.replace(/(.*)\.(.*)\./ig, '') + + database = database ? (database[0] || '') : '' + + let getuuid = () => { + let uuid = [] + let timestamp = new Date().getTime() + let _options = '0123456789abcdefghigklmnopqrstuv' + for (let i = 0; i < 19; i++) { + uuid.push(_options.substr(Math.floor(Math.random() * 0x20), 1)) + } + uuid = timestamp + uuid.join('') + return uuid + } + + // let upId = getuuid() + + let _initCustomScript = '' // 鍒濆鍖栬剼鏈� + let _prevCustomScript = '' // 榛樿sql鍓嶆墽琛岃剼鏈� + let _backCustomScript = '' // 榛樿sql鍚庢墽琛岃剼鏈� + + if (btn.scripts && btn.intertype === 'system') { + btn.scripts.forEach(script => { + if (script.status === 'false') return + + if (script.position === 'init') { + _initCustomScript += ` + /* 鑷畾涔夎剼鏈� */ + ${script.sql} + ` + } else if (script.position === 'front') { + _prevCustomScript += ` + /* 鑷畾涔夎剼鏈� */ + ${script.sql} + ` + } else { + _backCustomScript += ` + /* 鑷畾涔夎剼鏈� */ + ${script.sql} + ` + } + }) + } + + // 鎺у埗鍙版墦鍗版暟鎹� + let conLtext = [] + + let _Ltext = data.map(item => { + let vals = [] + let convals = [] + columns.forEach(col => { + let val = item[col.field] + + vals.push(`'${val}'`) + convals.push(`'${val}' as ${col.field}`) + }) + + let key = item.$$uuid + let type = 'upt' + if (key === '$new') { + key = getuuid() + type = 'add' + } else if (item.$deleted) { + type = 'del' + } + + vals.push(`'${key}'`) + vals.push(`'${type}'`) + vals.push(`'${BID}'`) + + convals.push(`'${key}' as jskey`) + convals.push(`'${type}' as data_type`) + convals.push(`'${BID}' as BID`) + conLtext.push(`Select ${convals.join(',')}`) + + return `Select ${vals.join(',')}` + }) + + let result = [] + for(let i = 0; i < _Ltext.length; i += 20) { + result.push(_Ltext.slice(i, i + 20)) + } + + let _sql = '' + let _sqlInsert = '' + let _sqlBottom = '' + + if (btn.intertype === 'system') { + let _uniquesql = '' + if (btn.uniques && btn.uniques.length > 0) { + btn.uniques.forEach(unique => { + if (unique.status === 'false') return + + let _fields = unique.field.split(',') + let _fields_ = _fields.map(_field => `a.${_field}=b.${_field}`) + let _afields = _fields.map(_field => `a.${_field}`) + _fields_ = _fields_.join(' and ') + + if (unique.verifyType !== 'physical') { + _fields_ += ' and b.deleted=0' + } + + _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 + + Set @tbid='' + Select top 1 @tbid=${_afields.join('+\' \'+')} from @${sheet} a Inner join ${sheet} b on ${_fields_} + + If @tbid!='' + Begin + select @ErrorCode='${unique.errorCode}',@retmsg=@tbid+' 涓庡凡鏈夋暟鎹噸澶�' + goto aaa + end + ` + }) + } + + let declarefields = [] + let fields = [] + + columns.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 = ` + /* 绯荤粺鐢熸垚 */ + declare @${sheet} table (${declarefields.join(',')},jskey nvarchar(50),data_type nvarchar(50),BID nvarchar(50) ) + Declare @UserName nvarchar(50),@FullName nvarchar(50),@RoleID nvarchar(512),@mk_departmentcode nvarchar(50),@mk_organization nvarchar(50),@login_city nvarchar(50),@ErrorCode nvarchar(50),@retmsg nvarchar(4000),@tbid Nvarchar(512) + + Select @ErrorCode='', @retmsg='', @UserName='${userName}', @FullName='${fullName}', @RoleID='${RoleID}', @mk_departmentcode='${departmentcode}', @mk_organization='${organization}', @login_city='${city}' + ${_initCustomScript} + ` + _sqlInsert = `Insert into @${sheet} (${fields},jskey,data_type,BID)` + _sqlBottom = ` + /* 榛樿sql */ + delete tmp_excel_in where upid=@upid@ + + delete tmp_excel_in where datediff(day,createdate,getdate())>15 + ${_uniquesql} + ${_prevCustomScript} + ${_insert} + ${_backCustomScript} + Delete @${sheet} + + aaa: select @ErrorCode as ErrorCode,@retmsg as retmsg` + + if ((window.GLOB.systemType !== 'production' && options.sysType !== 'cloud') || window.debugger === true) { + let fsql = ` + ${_sql} + ${_sqlInsert} + + /* table鏁版嵁 */ + ${conLtext.join(' Union all \n')} + ${_sqlBottom} + ` + fsql = fsql.replace(/\n\s{8}/ig, '\n') + console.info(fsql) + } + } else { // s_sDataDictb_excelIn 浜戠瀵嗛挜楠岃瘉鍙傛暟 + _sql = ` + /* 绯荤粺鐢熸垚 */ + declare @${sheet} table (jskey nvarchar(50)) + Declare @UserName nvarchar(50),@FullName nvarchar(50),@RoleID nvarchar(512),@mk_departmentcode nvarchar(50),@mk_organization nvarchar(50),@login_city nvarchar(50),@ErrorCode nvarchar(50),@retmsg nvarchar(4000),@tbid Nvarchar(512) + + Select @ErrorCode='', @retmsg='', @UserName='${userName}', @FullName='${fullName}', @RoleID='${RoleID}', @mk_departmentcode='${departmentcode}', @mk_organization='${organization}', @login_city='${city}' + ` + } + + return { + sql: _sql, + lines: result.map((list, index) => { + return { + Ltext: window.btoa(window.encodeURIComponent(list.join(' Union all '))), + Sort: (index + 1) * 10 + } + }), + insert: _sqlInsert, + bottom: _sqlBottom } } @@ -1123,7 +1362,7 @@ }) // 闇�瑕佸0鏄庣殑鍙橀噺闆� - let _vars = ['tbid', 'errorcode', 'retmsg', 'billcode', 'bvoucher', 'fibvoucherdate', 'fiyear', 'username', 'fullname', 'modulardetailcode'] + let _vars = ['tbid', 'errorcode', 'retmsg', 'billcode', 'bvoucher', 'fibvoucherdate', 'fiyear', 'username', 'fullname', 'modulardetailcode', 'roleid', 'mk_departmentcode', 'mk_organization', 'login_city', 'bid'] // 涓婚敭瀛楁 let primaryKey = setting.primaryKey || 'id' @@ -1245,7 +1484,7 @@ _declarefields = ',' + _declarefields } _sql = `/* 绯荤粺鐢熸垚 */ - Declare @tbid nvarchar(50),@ErrorCode nvarchar(50),@retmsg nvarchar(4000),@BillCode nvarchar(50),@BVoucher nvarchar(50),@FIBVoucherDate nvarchar(50), @FiYear nvarchar(50), @UserName nvarchar(50),@FullName nvarchar(50),@RoleID nvarchar(512),@departmentcode nvarchar(50),@organization nvarchar(50),@login_city nvarchar(50),@ModularDetailCode nvarchar(50)${_declarefields} + Declare @tbid nvarchar(50),@ErrorCode nvarchar(50),@retmsg nvarchar(4000),@BillCode nvarchar(50),@BVoucher nvarchar(50),@FIBVoucherDate nvarchar(50), @FiYear nvarchar(50), @UserName nvarchar(50),@FullName nvarchar(50),@RoleID nvarchar(512),@mk_departmentcode nvarchar(50),@mk_organization nvarchar(50),@login_city nvarchar(50),@bid nvarchar(50),@ModularDetailCode nvarchar(50)${_declarefields} ` // 琛ㄥ崟鍙橀噺璧嬪�� @@ -1292,7 +1531,7 @@ // 鍒濆鍖栧嚟璇佸強鐢ㄦ埛淇℃伅瀛楁 _sql += ` /* 鍑瘉鍙婄敤鎴蜂俊鎭垵濮嬪寲璧嬪�� */ - select @BVoucher='',@FIBVoucherDate='',@FiYear='',@ErrorCode='',@retmsg='',@UserName='${userName}', @FullName='${fullName}', @RoleID='${RoleID}', @departmentcode='${departmentcode}', @organization='${organization}', @login_city='${city}', @BillCode='', @ModularDetailCode='' + select @BVoucher='',@FIBVoucherDate='',@FiYear='',@ErrorCode='',@retmsg='',@UserName='${userName}', @FullName='${fullName}', @RoleID='${RoleID}', @mk_departmentcode='${departmentcode}', @mk_organization='${organization}', @login_city='${city}', @BillCode='', @ModularDetailCode='' ` if (retmsg) { @@ -1668,9 +1907,15 @@ if (!_arr.includes('submituserid')) { _form.push('submituserid=@userid@') } + if (!_arr.includes('submituser')) { + _form.push('submituser=@username') + } } else { if (!_arr.includes('modifydate')) { _form.push('modifydate=getdate()') + } + if (!_arr.includes('modifyuser')) { + _form.push('modifyuser=@username') } if (!_arr.includes('modifyuserid')) { _form.push('modifyuserid=@userid@') @@ -1719,7 +1964,7 @@ _sql += ` /* 榛樿sql */ - update ${btn.sql} set deleted=1,modifydate=getdate(),modifyuserid=@userid@ where ${primaryKey}${_ID};` + update ${btn.sql} set deleted=1,modifydate=getdate(),modifyuser=@username,modifyuserid=@userid@ where ${primaryKey}${_ID};` } else if (_actionType === 'delete') { // 鐗╃悊鍒犻櫎 let _msg = '' @@ -2035,7 +2280,7 @@ form = ` insert into ${param.name} (${field1},createuserid) select ${field2},@UserID - update ${param.name} set ${field3},modifydate=getdate(),modifyuserid=@UserID + update ${param.name} set ${field3},modifydate=getdate(),modifyuser=@username,modifyuserid=@UserID ` } else if (btn.OpenType === 'prompt' || btn.OpenType === 'exec') { form = ` -- Gitblit v1.8.0