| | |
| | | let _fields = record.field.split(',') |
| | | let _fields_ = _fields.map(_field => `a.${_field}=b.${_field}`) |
| | | _fields_ = _fields_.join(' and ') |
| | | _fields_ += ` and a.jskey != b.${this.props.config.setting.primaryKey || 'id'}` |
| | | |
| | | let _where = [] |
| | | _fields.forEach(f => { |
| | |
| | | this.setState({verify: {...verify, uniques}}) |
| | | } |
| | | |
| | | scriptsChange = (values) => { |
| | | scriptsChange = (values, resolve, reject) => { |
| | | let verify = JSON.parse(JSON.stringify(this.state.verify)) |
| | | |
| | | if (values.uuid) { |
| | |
| | | verify.scripts.push(values) |
| | | } |
| | | |
| | | MKEmitter.emit('editLineId', values.uuid) |
| | | let sql = this.getEditTableSql(verify) |
| | | |
| | | this.setState({ |
| | | verify: verify |
| | | if (resolve) { |
| | | Api.sDebug(sql).then(res => { |
| | | if (res.status || res.ErrCode === '-2') { |
| | | resolve() |
| | | values && MKEmitter.emit('editLineId', values.uuid) |
| | | |
| | | this.setState({ verify }) |
| | | } else { |
| | | reject() |
| | | |
| | | Modal.error({ |
| | | title: res.message |
| | | }) |
| | | } |
| | | }) |
| | | } else { |
| | | Api.sDebug(sql, true) |
| | | |
| | | MKEmitter.emit('editLineId', values.uuid) |
| | | |
| | | this.setState({ verify }) |
| | | } |
| | | } |
| | | |
| | | getEditTableSql = (verify) => { |
| | | const { columns, setting, cols } = this.props.config |
| | | |
| | | 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 mk_user_type = sessionStorage.getItem('mk_user_type') || '' |
| | | let nation = sessionStorage.getItem('nation') || '' |
| | | let province = sessionStorage.getItem('province') || '' |
| | | let city = sessionStorage.getItem('city') || '' |
| | | let district = sessionStorage.getItem('district') || '' |
| | | let address = sessionStorage.getItem('address') || '' |
| | | |
| | | let _sheet = btn.sheet |
| | | let BID = 'bid' |
| | | |
| | | 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 vals = [] |
| | | let forms = [] |
| | | let _forms = {} |
| | | let index = 0 |
| | | |
| | | let getColumns = (cols) => { |
| | | cols.forEach(item => { |
| | | if (item.type === 'colspan') { |
| | | getColumns(item.subcols) |
| | | } else if (item.editable === 'true') { |
| | | item.$sort = index |
| | | _forms[item.field] = item |
| | | index++ |
| | | } |
| | | }) |
| | | } |
| | | |
| | | getColumns(cols) |
| | | |
| | | columns.forEach(item => { |
| | | if (item.field === setting.primaryKey) return |
| | | |
| | | if (_forms[item.field]) { |
| | | let _item = {..._forms[item.field]} |
| | | if (_item.editType === 'date') { |
| | | _item.datatype = _item.declareType || 'datetime' |
| | | } else { |
| | | _item.datatype = item.datatype |
| | | } |
| | | |
| | | forms.push(_item) |
| | | } else { |
| | | forms.push({...item, $sort: 999}) |
| | | } |
| | | }) |
| | | |
| | | forms.sort((a, b) => a.$sort - b.$sort) |
| | | |
| | | forms.forEach(col => { |
| | | if (/date/.test(col.datatype)) { |
| | | vals.push(`'1949-10-01'`) |
| | | } else if (col.type === 'number') { |
| | | vals.push(`1`) |
| | | } else { |
| | | vals.push(`'mk'`) |
| | | } |
| | | }) |
| | | |
| | | vals.push(`'uuid'`) |
| | | vals.push(`'upt'`) |
| | | vals.push(`'${BID}'`) |
| | | |
| | | vals = `Select ${vals.join(',')}` |
| | | |
| | | let sql = '' |
| | | |
| | | let _initCustomScript = '' // 初始化脚本 |
| | | let _prevCustomScript = '' // 默认sql前执行脚本 |
| | | let _backCustomScript = '' // 默认sql后执行脚本 |
| | | let isDM = sessionStorage.getItem('dataM') === 'true' |
| | | 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})`}, |
| | | {reg: /@typename@/ig, value: `'admin'`}, |
| | | {reg: /\$@/ig, value: isDM ? '/*' : ''}, |
| | | {reg: /@\$/ig, value: isDM ? '*/' : ''}, |
| | | {reg: /@datam@/ig, value: isDM ? `'Y'` : `''`}, |
| | | ] |
| | | |
| | | 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 ') |
| | | _fields_ += ` and a.jskey != b.${setting.primaryKey || 'id'}` |
| | | |
| | | 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 = [] |
| | | let upFields = [] |
| | | |
| | | 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) |
| | | upFields.push(`${col.field}=t.${col.field}`) |
| | | }) |
| | | |
| | | fields = fields.join(',') |
| | | upFields = upFields.join(',') |
| | | |
| | | let _insert = '' |
| | | if (btn.default !== 'false') { |
| | | _insert = ` |
| | | /* 默认sql */ |
| | | update a set ${upFields},modifydate=getdate(),modifyuserid='${sessionStorage.getItem('UserID') || ''}',modifyuser=@username,modifystaff=@fullname,deleted=0 |
| | | from (select * from #${sheet} where data_type='upt') t |
| | | inner join ${database}${sheet} a on t.jskey=a.${setting.primaryKey || 'id'} |
| | | |
| | | update a set deleted=1,modifydate=getdate(),modifyuserid='${sessionStorage.getItem('UserID') || ''}',modifyuser=@username,modifystaff=@fullname |
| | | from (select * from #${sheet} where data_type='del') t |
| | | inner join ${database}${sheet} a on t.jskey=a.${setting.primaryKey || 'id'} |
| | | |
| | | delete t from #${sheet} t inner join ${database}${sheet} a on t.jskey=a.${setting.primaryKey || 'id'} |
| | | |
| | | Insert into ${database}${sheet} (${fields},createuserid,createuser,createstaff,bid) |
| | | Select ${fields},'${sessionStorage.getItem('UserID') || ''}',@username,@fullname,'${BID}' From #${sheet} |
| | | ` |
| | | } |
| | | |
| | | sql = `create table #${sheet} (${declarefields.join(',')},jskey nvarchar(50),data_type nvarchar(50),BID nvarchar(256)) |
| | | Declare @UserName nvarchar(50),@FullName nvarchar(50),@RoleID nvarchar(512),@mk_departmentcode nvarchar(512),@mk_organization nvarchar(512),@mk_user_type nvarchar(20),@mk_nation nvarchar(50),@mk_province nvarchar(50),@mk_city nvarchar(50),@mk_district nvarchar(50),@mk_address nvarchar(100),@ErrorCode nvarchar(50),@retmsg nvarchar(4000),@tbid Nvarchar(512) |
| | | |
| | | Select @ErrorCode='', @retmsg='', @UserName='${userName}', @FullName='${fullName}', @RoleID='${RoleID}', @mk_departmentcode='${departmentcode}', @mk_organization='${organization}', @mk_user_type='${mk_user_type}', @mk_nation='${nation}', @mk_province='${province}', @mk_city='${city}', @mk_district='${district}', @mk_address='${address}' |
| | | ${_initCustomScript} |
| | | Insert into #${sheet} (${fields},jskey,data_type,BID) |
| | | |
| | | /* excel数据*/ |
| | | ${vals} |
| | | |
| | | ${_uniquesql} |
| | | ${_prevCustomScript} |
| | | ${_insert} |
| | | ${_backCustomScript} |
| | | |
| | | drop table #${sheet} |
| | | |
| | | aaa: select @ErrorCode as ErrorCode,@retmsg as retmsg` |
| | | |
| | | return sql |
| | | } |
| | | |
| | | handleDelete = (record, type) => { |
| | |
| | | type="fullscreen" |
| | | btn={verify} |
| | | usefulfields={fields} |
| | | setting={config.setting} |
| | | scripts={verify.scripts} |
| | | systemScripts={this.state.systemScripts} |
| | | scriptsChange={this.scriptsChange} |
| | |
| | | <CustomScript |
| | | btn={verify} |
| | | usefulfields={fields} |
| | | setting={config.setting} |
| | | scripts={verify.scripts} |
| | | systemScripts={this.state.systemScripts} |
| | | scriptsChange={this.scriptsChange} |