| | |
| | | } |
| | | |
| | | /** |
| | | * @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, lindex) => { |
| | | 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),@departmentcode nvarchar(50),@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}' |
| | | ${_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),@departmentcode nvarchar(50),@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}' |
| | | ` |
| | | } |
| | | |
| | | 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 |
| | | } |
| | | } |
| | | |
| | | /** |
| | | * @description 使用系统函数时(sPC_TableData_InUpDe ),生成sql语句 |
| | | * @return {Object} btn 按钮信息 |
| | | * @return {Object} setting 菜单或组件数据源设置 |