From 9a11e62adeb8d435b52a361eb62d5b59e1deef2a Mon Sep 17 00:00:00 2001
From: king <18310653075@163.com>
Date: 星期四, 23 五月 2024 21:14:50 +0800
Subject: [PATCH] 2024-05-23
---
src/templates/sharecomponent/actioncomponent/verifyexcelin/index.jsx | 134 ++++++++++++++++++++++++++++++++++++++------
1 files changed, 114 insertions(+), 20 deletions(-)
diff --git a/src/templates/sharecomponent/actioncomponent/verifyexcelin/index.jsx b/src/templates/sharecomponent/actioncomponent/verifyexcelin/index.jsx
index 3bba3de..9986355 100644
--- a/src/templates/sharecomponent/actioncomponent/verifyexcelin/index.jsx
+++ b/src/templates/sharecomponent/actioncomponent/verifyexcelin/index.jsx
@@ -205,6 +205,93 @@
</div>
)
},
+ {
+ dataIndex: 'sqlRender',
+ render: (record) => {
+ let columns = this.state.verify.columns
+
+ let textFields = []
+ let numberFields = []
+ let dateFields = []
+ 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)
+ }
+ })
+
+ let _fields = record.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 (record.verifyType === 'logic' || record.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
+ })
+
+ let _sheet = this.props.card.sheet
+
+ 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 sql = `
+ /* 閲嶅鎬ч獙璇� */
+ Set @tbid=''
+ Select top 1 @tbid=${_fields.join('+\' \'+')} from (select 1 as n,${record.field} from #${sheet} ${_where}) a group by ${record.field} having sum(n)>1
+
+ If @tbid!=''
+ Begin
+ select @ErrorCode='${record.errorCode}',@retmsg=@tbid+' 閲嶅'
+ goto aaa
+ end
+
+ ${record.verifyType.indexOf('temp') === -1 ? `Set @tbid=''
+ Select top 1 @tbid=${_afields.join('+\' \'+')} from ${_where ? `(select * from #${sheet} ${_where})` : `#${sheet}`} a Inner join ${database}${sheet} b on ${_fields_}
+
+ If @tbid!=''
+ Begin
+ select @ErrorCode='${record.errorCode}',@retmsg=@tbid+' 涓庡凡鏈夋暟鎹噸澶�'
+ goto aaa
+ end` : ''}
+ `
+
+ return sql.split(/\n\s{10}/ig).map(n => n.replace(/^\s{2}/ig, ' '))
+ }
+ }
],
scriptsColumns: [
{
@@ -716,37 +803,44 @@
if (!err) {
let _verify = {...verify, ...values}
- if (_verify.excelHandle !== 'true') {
+ if (_verify.excelHandle !== 'true') {
delete _verify.excel_func
+ }
+ if (_verify.default === 'false' && _verify.scripts.length === 0) {
+ notification.warning({
+ top: 92,
+ message: '涓嶆墽琛岄粯璁ql鏃讹紝蹇呴』璁剧疆鑷畾涔夎剼鏈紒',
+ duration: 5
+ })
+ return
}
let cols = _verify.columns.map(col => col.Column.toLowerCase())
cols = Array.from(new Set(cols))
+ let error = ''
if (_verify.columns.length === 0) {
- notification.warning({
- top: 92,
- message: '璇疯缃瓻xcel鍒楀瓧娈�!',
- duration: 5
- })
- return
+ error = '璇疯缃瓻xcel鍒楀瓧娈�!'
} else if (_verify.columns.length > cols.length) {
- notification.warning({
- top: 92,
- message: 'Excel鍒楀瓧娈靛悕锛屼笉鍙噸澶�!',
- duration: 5
- })
- return
+ error = 'Excel鍒楀瓧娈靛悕锛屼笉鍙噸澶�!'
+ } else if (cols.includes('bid')) {
+ error = 'bid瀛楁涓轰繚鐣欏瓧锛屼笉鍙娇鐢�!'
+ } else if (cols.includes('jskey')) {
+ error = 'jskey瀛楁涓轰繚鐣欏瓧锛屼笉鍙娇鐢�!'
} else if (_verify.range === 1) {
let tEmptys = _verify.columns.filter(op => !op.Text)
if (tEmptys.length > 0) {
- notification.warning({
- top: 92,
- message: '蹇界暐棣栬鏃讹紝浼氫娇鐢═ext鍊兼牎楠孍xcel棣栬鍐呭锛孴ext鍊间笌Excel琛ㄩ琛屽唴瀹圭浉鍚岋紝涓斿潎涓嶅彲涓虹┖锛�',
- duration: 5
- })
- return
+ error = '蹇界暐棣栬鏃讹紝浼氫娇鐢═ext鍊兼牎楠孍xcel棣栬鍐呭锛孴ext鍊间笌Excel琛ㄩ琛屽唴瀹圭浉鍚岋紝涓斿潎涓嶅彲涓虹┖锛�'
}
+ }
+
+ if (error) {
+ notification.warning({
+ top: 92,
+ message: error,
+ duration: 5
+ })
+ return
}
_verify.columns.sort((a, b) => {
@@ -973,7 +1067,7 @@
</span>
} key="unique">
<UniqueForm fields={verify.columns} uniqueChange={this.uniqueChange}/>
- <EditTable actions={['edit', 'move', 'del', 'status']} data={verify.uniques} columns={uniqueColumns} onChange={this.changeUniques}/>
+ <EditTable actions={['edit', 'move', 'del', 'status', 'sql']} data={verify.uniques} columns={uniqueColumns} onChange={this.changeUniques}/>
</TabPane> : null}
{card.intertype === 'system' ? <TabPane tab={
<span>
--
Gitblit v1.8.0