From 95afd40fc2741ac0ce59c2091f6cfce1f98877d4 Mon Sep 17 00:00:00 2001
From: king <18310653075@163.com>
Date: 星期四, 24 六月 2021 09:15:45 +0800
Subject: [PATCH] 2021-06-24

---
 src/utils/utils.js |  622 ++++++++++++++++++++++++++++++++++++++++++++++++++++++--
 1 files changed, 599 insertions(+), 23 deletions(-)

diff --git a/src/utils/utils.js b/src/utils/utils.js
index ce58c46..2687da4 100644
--- a/src/utils/utils.js
+++ b/src/utils/utils.js
@@ -598,6 +598,10 @@
           key: search.datefield,
           value: '0'
         })
+        options.push({
+          key: search.datefield + '1',
+          value: '0'
+        })
         if (search.transfer === 'true') {
           options.push(item)
         }
@@ -698,7 +702,10 @@
       arrfield.push(...item.linkSubField)
     } else if (item.type === 'checkcard') {
       arrfield = item.fields.map(f => f.field)
-      arrfield.push(item.valueField)
+      arrfield.push(item.cardValField)
+      if (item.urlField) {
+        arrfield.push(item.urlField)
+      }
     }
 
     arrfield = Array.from(new Set(arrfield))
@@ -718,6 +725,22 @@
       sql = `select ${item.type === 'checkcard' ? 'top 20' : ''} ${arrfield} from (select distinct ${arrfield} from ${_datasource}) a`
     }
 
+    if (sessionStorage.getItem('dataM') === 'true') { // 鏁版嵁鏉冮檺
+      sql = sql.replace(/\$@/ig, '/*')
+      sql = sql.replace(/@\$/ig, '*/')
+    } else {
+      sql = sql.replace(/@\$|\$@/ig, '')
+    }
+
+    // 澶栬仈鏁版嵁搴撴浛鎹�
+    if (window.GLOB.externalDatabase !== null) {
+      sql = sql.replace(/@db@/ig, window.GLOB.externalDatabase)
+    }
+
+    if ((window.GLOB.systemType !== 'production' && options.sysType !== 'cloud') || window.debugger === true) {
+      console.info(sql)
+    }
+
     return {
       sql: sql,
       field: arrfield
@@ -727,14 +750,17 @@
 
 /**
  * @description 鑾峰彇excel瀵煎叆鍙傛暟
- * @return {String} btn   鎸夐挳
- * @return {String} data  excel鏁版嵁
+ * @return {Object} item   鎸夐挳淇℃伅
+ * @return {Array}  data   excel鏁版嵁
+ * @return {Object} dict   瀛楀吀椤�
+ * @return {String} BID    涓婄骇Id
  */
 export function getExcelInSql (item, data, dict, BID) {
   let btn = item.verify
   let keys = ['delete', 'drop', 'insert', 'truncate', 'update']
   let userName = sessionStorage.getItem('User_Name') || ''
   let fullName = sessionStorage.getItem('Full_Name') || ''
+  let city = sessionStorage.getItem('city') || ''
 
   if (sessionStorage.getItem('isEditState') === 'true') {
     userName = sessionStorage.getItem('CloudUserName') || ''
@@ -968,9 +994,9 @@
     _sql = `
       /* 绯荤粺鐢熸垚 */
       declare @${sheet} table (${declarefields.join(',')},jskey nvarchar(50),BID nvarchar(50) )
-      Declare @UserName nvarchar(50),@FullName nvarchar(50),@ErrorCode nvarchar(50),@retmsg nvarchar(4000),@tbid Nvarchar(512)
+      Declare @UserName nvarchar(50),@FullName nvarchar(50),@login_city nvarchar(50),@ErrorCode nvarchar(50),@retmsg nvarchar(4000),@tbid Nvarchar(512)
       
-      Select  @ErrorCode='', @retmsg='', @UserName='${userName}', @FullName='${fullName}'
+      Select  @ErrorCode='', @retmsg='', @UserName='${userName}', @FullName='${fullName}', @login_city='${city}'
       ${_initCustomScript}
       `
     _sqlInsert = `Insert into @${sheet} (${fields},jskey,BID)`
@@ -1003,9 +1029,9 @@
     _sql = `
       /* 绯荤粺鐢熸垚 */
       declare @${sheet} table (jskey nvarchar(50))
-      Declare @UserName nvarchar(50),@FullName nvarchar(50),@ErrorCode nvarchar(50),@retmsg nvarchar(4000),@tbid Nvarchar(512)
+      Declare @UserName nvarchar(50),@FullName nvarchar(50),@login_city nvarchar(50),@ErrorCode nvarchar(50),@retmsg nvarchar(4000),@tbid Nvarchar(512)
       
-      Select  @ErrorCode='', @retmsg='', @UserName='${userName}', @FullName='${fullName}'
+      Select  @ErrorCode='', @retmsg='', @UserName='${userName}', @FullName='${fullName}', @login_city='${city}'
       `
   }
 
@@ -1025,16 +1051,23 @@
 
 /**
  * @description 浣跨敤绯荤粺鍑芥暟鏃讹紙sPC_TableData_InUpDe 锛夛紝鐢熸垚sql璇彞
- * @return {String} type   鎵ц绫诲瀷
- * @return {String} table  琛ㄥ悕
+ * @return {Object}  btn       鎸夐挳淇℃伅
+ * @return {Object}  setting   鑿滃崟鎴栫粍浠舵暟鎹簮璁剧疆
+ * @return {Array}   formdata  琛ㄥ崟
+ * @return {Object}  param     璇锋眰鍙傛暟
+ * @return {Array}   data      鍒楄〃琛屾暟鎹�
+ * @return {Array}   columns   鏄剧ず鍒�
+ * @return {Object}  tab       鏍囩淇℃伅
+ * @return {Boolean} retmsg    鏄惁闇�瑕佹暟鎹繑鍥�
  */
-export function getSysDefaultSql (btn, setting, formdata, param, data, columns, tab, retmsg = false) {
+export function getSysDefaultSql (btn, setting, formdata, param, data, columns, tab, retmsg = false, moduleParams, getOptions) {
   let primaryId = param.ID
   let BID = param.BID
   let verify = btn.verify || {}
   let datavars = {}                 // 澹版槑鐨勫彉閲忥紝琛ㄥ崟鍙婃樉绀哄垪
   let _actionType = null
   let _callbacksql = ''
+  let foreignKey = tab && tab.foreignKey ? tab.foreignKey.toLowerCase() : ''
 
   if (verify.default !== 'false') { // 鍒ゆ柇鏄惁浣跨敤榛樿sql
     _actionType = btn.sqlType
@@ -1127,7 +1160,7 @@
   }
 
   // 娣诲姞鏁版嵁涓瓧娈碉紝琛ㄥ崟鍊间紭鍏�(鎸夐挳涓嶉�夎鎴栧琛屾嫾鎺ユ椂璺宠繃)
-  if (data && btn.Ot !== 'notRequired' && btn.Ot !== 'requiredOnce') {
+  if (data && !btn.$forbid && btn.Ot !== 'notRequired' && btn.Ot !== 'requiredOnce') {
     datavars = {...data, ...datavars}
 
     const setField = (col) => {
@@ -1184,7 +1217,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),@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),@login_city nvarchar(50),@ModularDetailCode nvarchar(50)${_declarefields}
     `
 
   // 琛ㄥ崟鍙橀噺璧嬪��
@@ -1218,6 +1251,7 @@
 
   let userName = sessionStorage.getItem('User_Name') || ''
   let fullName = sessionStorage.getItem('Full_Name') || ''
+  let city = sessionStorage.getItem('city') || ''
 
   if (sessionStorage.getItem('isEditState') === 'true') {
     userName = sessionStorage.getItem('CloudUserName') || ''
@@ -1227,7 +1261,7 @@
   // 鍒濆鍖栧嚟璇佸強鐢ㄦ埛淇℃伅瀛楁
   _sql += `
       /* 鍑瘉鍙婄敤鎴蜂俊鎭垵濮嬪寲璧嬪�� */
-      select @BVoucher='',@FIBVoucherDate='',@FiYear='',@ErrorCode='',@retmsg='',@UserName='${userName}', @FullName='${fullName}', @BillCode='', @ModularDetailCode=''
+      select @BVoucher='',@FIBVoucherDate='',@FiYear='',@ErrorCode='',@retmsg='',@UserName='${userName}', @FullName='${fullName}', @login_city='${city}', @BillCode='', @ModularDetailCode=''
       `
 
   if (retmsg) {
@@ -1258,16 +1292,55 @@
   }
 
   // 澶辨晥楠岃瘉锛屾坊鍔犳暟鎹椂涓嶇敤
-  if (btn.sqlType !== 'insert' && verify.invalid === 'true' && setting.dataresource) {
+  if (btn.sqlType !== 'insert' && btn.Ot !== 'notRequired' && verify.invalid === 'true' && setting.dataresource) {
     let datasource = setting.dataresource
+    let customScript = setting.customScript || ''
+    let search = moduleParams ? moduleParams.search : null
+    let orderBy = moduleParams ? moduleParams.orderBy : setting.order
+
     if (/\s/.test(datasource) && !/tb$/.test(datasource)) { // 鎷兼帴鍒悕
       datasource = '(' + datasource + ') tb'
     }
 
-    if (setting.customScript) {
+    if (getOptions && (setting.queryType === 'statistics' || customScript)) {
+      let allSearch = getOptions(search)
+
+      let regoptions = allSearch.map(item => {
+        return {
+          reg: new RegExp('@' + item.key + '@', 'ig'),
+          value: `'${item.value}'`
+        }
+      })
+      regoptions.push({
+        reg: new RegExp('@login_city@', 'ig'),
+        value: `'${city}'`
+      }, {
+        reg: new RegExp('@userName@', 'ig'),
+        value: `'${userName}'`
+      }, {
+        reg: new RegExp('@fullName@', 'ig'),
+        value: `'${fullName}'`
+      }, {
+        reg: new RegExp('@orderBy@', 'ig'),
+        value: orderBy
+      }, {
+        reg: new RegExp('@pageSize@', 'ig'),
+        value: 999999
+      }, {
+        reg: new RegExp('@pageIndex@', 'ig'),
+        value: 1
+      })
+
+      regoptions.forEach(item => {
+        datasource = datasource.replace(item.reg, item.value)
+        customScript = customScript.replace(item.reg, item.value)
+      })
+    }
+
+    if (customScript) {
       _sql += `
       /* 鏁版嵁婧愯嚜瀹氫箟鑴氭湰锛岃娉ㄦ剰鍙橀噺瀹氫箟鏄惁閲嶅 */
-      ${setting.customScript}
+      ${customScript}
       `
     }
 
@@ -1405,9 +1478,9 @@
         if (_key === 'bid' && !datavars.bid) { // 琛ㄥ崟涓病鏈塨id鍒欎娇鐢ㄧ郴缁焍id鍙橀噺
           _fval = '@BID@'
         }
-        if (_key === 'bid' && tab && tab.foreignKey) {
-          arr.push(tab.foreignKey.toLowerCase())
-          _fieldValue.push(`${tab.foreignKey}=${_fval}`)
+        if (_key === 'bid' && foreignKey) {
+          arr.push(foreignKey)
+          _fieldValue.push(`${foreignKey}=${_fval}`)
         } else {
           arr.push(_key)
           _fieldValue.push(`${_key}=${_fval}`)
@@ -1528,14 +1601,14 @@
       values.push('@fullname')
     }
     if (!keys.includes('bid')) {
-      if (tab && tab.foreignKey && !keys.includes(tab.foreignKey.toLowerCase())) {
-        keys.push(tab.foreignKey.toLowerCase())
+      if (foreignKey && !keys.includes(foreignKey)) {
+        keys.push(foreignKey)
       } else {
         keys.push('bid')
       }
       values.push('@BID@')
-    } else if (tab && tab.foreignKey && !keys.includes(tab.foreignKey.toLowerCase())) {
-      keys.push(tab.foreignKey.toLowerCase())
+    } else if (foreignKey && !keys.includes(foreignKey)) {
+      keys.push(foreignKey)
       values.push('@BID@')
     }
 
@@ -1681,4 +1754,507 @@
   } else {
     return _sql
   }
+}
+
+/**
+ * @description 鐢熸垚鏇挎崲鍑芥暟鍒楄〃
+ */
+export function setGLOBFuncs () {
+  window.GLOB.funcs = []
+  if (!window.GLOB.WebSql && !window.GLOB.IndexDB) {
+    return
+  }
+
+  if (window.GLOB.WebSql) {
+    window.GLOB.WebSql.transaction(tx => {
+      tx.executeSql("SELECT * FROM FUNCS", [], (tx, results) => {
+        let rows = results.rows
+        if (!rows || rows.length === 0) return
+        for (let i = 0; i < rows.length; i++) {
+          window.GLOB.funcs.push({
+            func_code: rows[i].func_code,
+            key_sql: window.decodeURIComponent(window.atob(rows[i].key_sql))
+          })
+        }
+      })
+    })
+  } else {
+    let objectStore = window.GLOB.IndexDB.transaction('funcs').objectStore('funcs')
+
+    objectStore.openCursor().onsuccess = (event) => {
+      let cursor = event.target.result
+
+      if (cursor) {
+        window.GLOB.funcs.push({
+          func_code: cursor.value.func_code,
+          key_sql: window.decodeURIComponent(window.atob(cursor.value.key_sql))
+        })
+        cursor.continue()
+      }
+    }
+  }
+
+}
+
+/**
+ * @description 鍒涘缓瀛樺偍杩囩▼绫�
+ */
+export class FuncUtils {
+  /**
+   * @description 鍒犻櫎瀛樺偍杩囩▼sql
+   * @return {String} name 瀛樺偍杩囩▼鍚嶇О
+   */
+  static dropfunc (name) {
+    return `IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('${name}') AND type in (N'P', N'PC'))  mdrpk PROCEDURE ${name}`
+  }
+
+  /**
+   * @description 鍒涘缓椤甸潰瀛樺偍杩囩▼
+   * @return {String}
+   */
+  static getTableFunc (param, menu, config) {
+    let form = ''
+    let formParam = ''
+    let _vars = ['bid', 'pageindex', 'pagesize', 'ordercol', 'ordertype', 'exceltype', 'septmenuno', 'lang', 'debug', 'loginuid', 'sessionuid', 'userid', 'errorcode', 'retmsg']
+    let _columns = []
+    let primaryKey = config.setting.primaryKey || 'ID'
+
+    if (!_vars.includes(primaryKey.toLowerCase())) {
+      _vars.push(primaryKey.toLowerCase())
+      formParam = `mchr13k@${primaryKey} nvarchar(50)='',`
+    }
+
+    if (config.search && config.search.length > 0) {
+      let _fields = new Map()
+      config.search.forEach(item => {
+        if (item.field) {
+          let type = ''
+
+          if (item.type.match(/date/ig)) {
+            type = 'datetime=null'
+          } else {
+            type = 'nvarchar(50)=\'\''
+          }
+
+          item.field.split(',').forEach(cell => {
+            let _f = cell
+            if (_fields.has(cell)) {
+              _f = _f + '1'
+            }
+  
+            _fields.set(cell, true)
+
+            if (!_vars.includes(_f.toLowerCase())) {
+              _vars.push(_f.toLowerCase())
+              formParam = formParam + `mchr13k@${_f} ${type},`
+            }
+          })
+        }
+      })
+    }
+
+    if (config.columns && config.columns.length > 0) {
+      config.columns.forEach(item => {
+        if (item.field) {
+          _columns.push(`${item.field} as ${item.label}`)
+        }
+      })
+
+      form = `
+        declare @dc table (${_columns.join(',')})
+        
+        @tableid ='${menu.MenuID}'
+      `
+    }
+
+    let Ltext = `create proc ${param.innerFunc}
+    ( /*${menu.MenuName}*/
+    @appkey nvarchar(50)='',
+    @BID nvarchar(50)='',${formParam}
+    @PageIndex nvarchar(50)='',
+    @PageSize nvarchar(50)='',
+    @OrderCol nvarchar(50)='',
+    @OrderType nvarchar(50)='',
+    @exceltype nvarchar(50)='',
+    @sEPTMenuNo nvarchar(50)='${menu.MenuNo}',
+    @lang nvarchar(50)='',
+    @debug nvarchar(50)='',
+    @LoginUID nvarchar(50)='',
+    @SessionUid nvarchar(50)='',
+    @UserID nvarchar(50),
+    @ErrorCode nvarchar(50) out,
+    @retmsg nvarchar(4000) out
+    )
+    as
+    begin
+    declare  @BegindateTest datetime,@EnddateTest datetime
+    select  @BegindateTest=getdate()
+    set @ErrorCode=''
+    set @retmsg=''
+    BEGIN TRY
+      /*浜嬪姟鎿嶄綔*/
+      BEGIN TRAN
+        /*鍏蜂綋涓氬姟鎿嶄綔*/
+        
+        /* 
+        select top 10 * from sProcExcep order by id desc
+        
+        declare @UserName  nvarchar(50),@FullName nvarchar(50)
+        
+        select @UserName=UserName,@FullName=FullName from SUsers where UID=@UserID
+        ${form}
+        if 1=2
+        begin
+          set @ErrorCode='E'
+          set @retmsg='鍦ㄦ鍐欐姤閿�'
+          goto GOTO_RETURN
+        end
+        
+        insert into sNote (remark,createuserid,CreateUser,CreateStaff)
+        select '鍦ㄦ鍐欐棩蹇�',@UserID,@UserName,@FullName
+        */
+        
+      COMMIT TRAN
+      SET NOCOUNT ON
+      RETURN
+    END TRY
+    BEGIN CATCH
+      /*閿欒澶勭悊*/
+      ROLLBACK TRAN
+      DECLARE @ErrorMessage NVARCHAR(4000);
+      DECLARE @ErrorSeverity INT;
+      DECLARE @ErrorState INT;
+      
+      /*鎶婅嚜瀹氫箟鐨勫弸濂界殑閿欒淇℃伅鎻愮ず鍔犱笂*/
+      set @ErrorCode=cast(ERROR_NUMBER() as nvarchar(50))
+      SET @retmsg=ERROR_MESSAGE();
+      SELECT @ErrorMessage=ERROR_MESSAGE(),
+        @ErrorSeverity=ERROR_SEVERITY(),
+        @ErrorState=ERROR_STATE();
+        
+      RAISERROR(@ErrorMessage, /*-- Message text.*/
+        @ErrorSeverity, /*-- Severity.*/
+        @ErrorState  /*-- State.*/
+      );
+    END CATCH
+    
+    GOTO_RETURN:
+      ROLLBACK TRAN
+      
+    END`
+
+    Ltext = Ltext.replace(/\n\s{4}/ig, 'mchr13k')
+
+    return Ltext
+  }
+
+  /**
+   * @description 鍒涘缓瀛樺偍杩囩▼
+   * @return {String}
+   */
+  static getfunc (param, btn, menu, config) {
+    let form = ''
+    let formParam = ''
+    let _vars = ['bid', 'septmenuno', 'lang', 'debug', 'loginuid', 'sessionuid', 'userid', 'errorcode', 'retmsg']
+    let columns = config.columns
+    let primaryKey = config.setting.primaryKey || 'ID'
+
+    if (!_vars.includes(primaryKey.toLowerCase())) {
+      _vars.push(primaryKey.toLowerCase())
+      let _type = '50'
+      if (btn.Ot === 'requiredOnce') { // 澶氳鎷兼帴鏃讹紝涓婚敭璁句负max
+        _type = 'max'
+      }
+      formParam = `mchr13k@${primaryKey} nvarchar(${_type})='',`
+    }
+
+    if (param.fields && param.fields.length > 0) {
+      let _fields = []
+      param.fields.forEach(item => {
+        if (item.field) {
+          let type = ''
+          if (item.type.match(/date/ig)) {
+            type = 'datetime=null'
+          } else if (item.type === 'number') {
+            type = `decimal(18,${item.decimal})=0`
+          } else {
+            type = 'nvarchar(50)=\'\''
+          }
+
+          if (!_vars.includes(item.field.toLowerCase())) {
+            _vars.push(item.field.toLowerCase())
+            formParam = formParam + `mchr13k@${item.field} ${type},`
+          }
+
+          _fields.push(item.field)
+        }
+      })
+
+      let field1 = _fields.join(',')
+      let field2 = _fields.join(',@')
+      let field3 = _fields.map(cell => {
+        return cell + '=@' + cell
+      })
+
+      field2 = field2 ? '@' + field2 : ''
+      field3 = field3.join(',')
+
+      form = `
+        insert into ${param.name} (${field1},createuserid) select ${field2},@UserID
+        
+        update ${param.name} set ${field3},modifydate=getdate(),modifyuserid=@UserID
+      `
+    } else if (btn.OpenType === 'prompt' || btn.OpenType === 'exec') {
+      form = `
+        update ${param.name} set ModifyDate=getdate(),ModifyUserID=@UserID where ${primaryKey}=@${primaryKey}
+      `
+    }
+
+    if (columns) {
+      let _col = []
+      let _field = []
+      columns.forEach(col => {
+        if (col.field) {
+          if (col.type === 'number') {
+            _col.push(col.field + ' decimal(18,2)')
+          } else {
+            _col.push(col.field + ' nvarchar(50)')
+          }
+          _field.push(col.field)
+        }
+      })
+      _col = _col.join(',')
+      _field = _field.join(',')
+
+      form = form + `
+        declare @dc table (${_col})
+        
+        insert into @dc (${_field})
+
+        @tableid ='${menu.MenuID}'
+      `
+    }
+
+    // 鎵撳嵃鑷畾涔夋ā鏉垮瓧娈垫彁绀�
+    let _printRemark = ''
+    if (btn.funcType === 'print') {
+      _printRemark = '/* 鑷畾涔夋暟鎹墦鍗版ā鏉挎椂锛岃浣跨敤TemplateID瀛楁 */'
+    }
+
+    let Ltext = `create proc ${param.funcName}
+    ( /*${menu.MenuName}  ${btn.label}*/
+    @appkey nvarchar(50)='',
+    @BID nvarchar(50)='',${formParam}
+    @sEPTMenuNo nvarchar(50)='${param.menuNo}',
+    @lang nvarchar(50)='',
+    @debug nvarchar(50)='',
+    @LoginUID nvarchar(50)='',
+    @SessionUid nvarchar(50)='',
+    @UserID nvarchar(50),
+    @ErrorCode nvarchar(50) out,
+    @retmsg nvarchar(4000) out
+    )
+    as
+    begin
+    declare  @BegindateTest datetime,@EnddateTest datetime
+    select  @BegindateTest=getdate()
+    set @ErrorCode=''
+    set @retmsg=''
+    BEGIN TRY
+      /*浜嬪姟鎿嶄綔*/
+      BEGIN TRAN
+        /*鍏蜂綋涓氬姟鎿嶄綔*/
+        ${_printRemark}
+        /* 
+        select top 10 * from sProcExcep order by id desc
+        
+        declare @UserName  nvarchar(50),@FullName nvarchar(50)
+        
+        select @UserName=UserName,@FullName=FullName from SUsers where UID=@UserID
+        ${form}
+        if 1=2
+        begin
+          set @ErrorCode='E'
+          set @retmsg='鍦ㄦ鍐欐姤閿�'
+          goto GOTO_RETURN
+        end
+        
+        insert into sNote (remark,createuserid,CreateUser,CreateStaff)
+        select '鍦ㄦ鍐欐棩蹇�',@UserID,@UserName,@FullName
+        */
+        
+      COMMIT TRAN
+      SET NOCOUNT ON
+      RETURN
+    END TRY
+    BEGIN CATCH
+      /*閿欒澶勭悊*/
+      ROLLBACK TRAN
+      DECLARE @ErrorMessage NVARCHAR(4000);
+      DECLARE @ErrorSeverity INT;
+      DECLARE @ErrorState INT;
+      
+      /*鎶婅嚜瀹氫箟鐨勫弸濂界殑閿欒淇℃伅鎻愮ず鍔犱笂*/
+      set @ErrorCode=cast(ERROR_NUMBER() as nvarchar(50))
+      SET @retmsg=ERROR_MESSAGE();
+      SELECT @ErrorMessage=ERROR_MESSAGE(),
+        @ErrorSeverity=ERROR_SEVERITY(),
+        @ErrorState=ERROR_STATE();
+        
+      RAISERROR(@ErrorMessage, /*-- Message text.*/
+        @ErrorSeverity, /*-- Severity.*/
+        @ErrorState  /*-- State.*/
+      );
+    END CATCH
+    
+    GOTO_RETURN:
+      ROLLBACK TRAN
+      
+    END`
+
+    Ltext = Ltext.replace(/\n\s{4}/ig, 'mchr13k')
+
+    return Ltext
+  }
+
+  /**
+   * @description 鍒涘缓瀵煎叆瀛樺偍杩囩▼
+   * @return {String}
+   */
+  static getexcelInfunc (param, btn, menu) {
+    let _verify = btn.verify
+
+    let _uniquesql = ''
+    if (_verify.uniques && _verify.uniques.length > 0) {
+      _verify.uniques.forEach(unique => {
+        if (unique.status === 'false') return
+
+        let _fields = unique.field.split(',')
+        let _fields_ = _fields.map(_field => {
+          return `a.${_field}=b.${_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 @${btn.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=${_fields.join('+\' \'+')} from  @${btn.sheet} a
+        Inner join ${btn.sheet} b on ${_fields_}
+
+        If @tbid!=''
+        Begin
+          select @ErrorCode='${unique.errorCode}',@retmsg=@tbid+' 涓庡凡鏈夋暟鎹噸澶�'
+          goto aaa
+        end
+        `
+      })
+
+      if (_uniquesql) {
+        _uniquesql = `
+        Declare @tbid Nvarchar(512)
+        ${_uniquesql}`
+      }
+    }
+
+    let declarefields = []
+    let fields = []
+
+    _verify.columns.forEach(col => {
+      declarefields.push(`${col.Column} ${col.type}`)
+      fields.push(col.Column)
+    })
+
+    fields = fields.join(',')
+
+    let _sql = `declare @${btn.sheet} table (${declarefields.join(',')},jskey nvarchar(50))
+      Declare @UserName nvarchar(50),@FullName nvarchar(50)
+      
+      select @UserName=UserName,@FullName=FullName from SUsers where UID=@UserID
+      
+      Insert into @${btn.sheet} (${fields},jskey)
+
+      exec s_KeyWords_Replace
+      @LText=@LText, @BID=@BID,@LoginUID=@LoginUID,@SessionUid=@SessionUid,@UserID=@UserID,@ID=@ID
+      ${_uniquesql}
+      Insert into ${btn.sheet} (${fields},createuserid,createuser,createstaff,bid) 
+      Select ${fields},@userid,@username,@fullname,@BID From @${btn.sheet}
+
+      Delete @${btn.sheet}`
+
+    let Ltext = `create proc ${param.funcName}
+    ( /*${menu.MenuName}  ${btn.label}*/
+      @appkey nvarchar(50)='',
+      @ID nvarchar(50)='',
+      @BID nvarchar(50)='',
+      @Ltext nvarchar(max)='',
+      @sEPTMenuNo nvarchar(50)='${param.menuNo}', 
+      @secretkey nvarchar(50)='',
+      @timestamp nvarchar(50)='',
+      @lang nvarchar(50)='',
+      @LoginUID nvarchar(50)='',
+      @SessionUid nvarchar(50)='',
+      @UserID nvarchar(50), 
+      @ErrorCode nvarchar(50) out,
+      @retmsg nvarchar(4000) out
+    )
+    as
+    begin
+    declare @BegindateTest datetime,@EnddateTest datetime 
+    select @BegindateTest=getdate() 
+    set @ErrorCode=''
+    set @retmsg=''
+    BEGIN TRY
+      /*浜嬪姟鎿嶄綔*/
+      BEGIN TRAN
+        /*鍏蜂綋涓氬姟鎿嶄綔*/
+
+        /* 
+        ${_sql}
+        */
+        
+      COMMIT TRAN
+      SET NOCOUNT ON
+      RETURN
+    END TRY
+    BEGIN CATCH
+      /*閿欒澶勭悊*/
+      ROLLBACK TRAN
+      DECLARE @ErrorMessage NVARCHAR(4000);
+      DECLARE @ErrorSeverity INT;
+      DECLARE @ErrorState INT;
+      
+      /*鎶婅嚜瀹氫箟鐨勫弸濂界殑閿欒淇℃伅鎻愮ず鍔犱笂*/
+      set @ErrorCode=cast(ERROR_NUMBER() as nvarchar(50))
+      SET @retmsg=ERROR_MESSAGE();
+      SELECT @ErrorMessage=ERROR_MESSAGE(),
+        @ErrorSeverity=ERROR_SEVERITY(),
+        @ErrorState=ERROR_STATE();
+        
+      RAISERROR(@ErrorMessage, /*-- Message text.*/
+        @ErrorSeverity, /*-- Severity.*/
+        @ErrorState  /*-- State.*/
+      );
+    END CATCH
+    
+    GOTO_RETURN:
+      ROLLBACK TRAN
+      
+    END`
+
+    Ltext = Ltext.replace(/\n\s{4}/ig, 'mchr13k')
+
+    return Ltext
+  }
 }
\ No newline at end of file

--
Gitblit v1.8.0