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 @@
})
// 闇�瑕佸０鏄庣殑鍙橀噺闆�
- 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