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 |   89 ++++++++++++++++++++++++++++++++++++++++++++
 1 files changed, 88 insertions(+), 1 deletions(-)

diff --git a/src/templates/sharecomponent/actioncomponent/verifyexcelin/index.jsx b/src/templates/sharecomponent/actioncomponent/verifyexcelin/index.jsx
index c58226f..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, '&nbsp;&nbsp;'))
+        }
+      }
     ],
     scriptsColumns: [
       {
@@ -980,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