2024-05-24

king

2024-05-24 0f6c07ed2f8dddd3ad6e37268bf06df6d82961ed

 src/templates/sharecomponent/actioncomponent/verifyexcelin/index.jsx

@@ -17,8 +17,11 @@

const { TabPane } = Tabs
const { confirm } = Modal
const { Search } = Input
const { Paragraph } = Typography

const EditTable = asyncComponent(() => import('@/templates/zshare/editTable'))
const CodeMirror = asyncComponent(() => import('@/templates/zshare/codemirror'))
const FullScripts = asyncComponent(() => import('@/templates/zshare/verifycard/fullScripts'))

class VerifyCard extends Component {
@@ -31,6 +34,7 @@
    verify: {},
    systemScripts: [],
    activeKey: 'basemsg',
    searchKey: '',
    excelColumns: [
      {
        title: '字段',
@@ -39,6 +43,8 @@
        inputType: 'input',
        unique: true,
        strict: true,
        searchable: true,
        copy: true,
        editable: true
      },
      {
@@ -46,6 +52,7 @@
        dataIndex: 'Text',
        width: '14%',
        inputType: 'input',
        searchable: true,
        editable: true
      },
      {
@@ -54,6 +61,7 @@
        width: '15%',
        editable: true,
        inputType: 'select',
        keyCol: true,
        options: [
          { value: 'Nvarchar(10)', text: 'Nvarchar(10)' },
          { value: 'Nvarchar(20)', text: 'Nvarchar(20)' },
@@ -109,7 +117,10 @@
        required: false,
        inputType: 'number',
        unlimit: true,
        editable: true
        editable: true,
        keyVals: ['Int', 'Decimal(18,0)', 'Decimal(18,2)', 'Decimal(18,4)', 'Decimal(18,6)'],
        render: (text, record) => /^Decimal/ig.test(record.type) || /^int/ig.test(record.type) ? text : ''
        
      },
      {
        title: '最大值',
@@ -118,7 +129,9 @@
        required: false,
        inputType: 'number',
        unlimit: true,
        editable: true
        editable: true,
        keyVals: ['Int', 'Decimal(18,0)', 'Decimal(18,2)', 'Decimal(18,4)', 'Decimal(18,6)'],
        render: (text, record) => /^Decimal/ig.test(record.type) || /^int/ig.test(record.type) ? text : ''
      }
    ],
    uniqueColumns: [
@@ -192,6 +205,89 @@
            </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

          let database = _sheet.match(/(.*)\.(.*)\.|@db@/ig) || ''
          let sheet = _sheet.replace(/(.*)\.(.*)\.|@db@/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: [
      {
@@ -296,16 +392,27 @@
      _verify.range = 1
    }

    _verify.excelHandle = _verify.excelHandle || 'false'
    _verify.default = _verify.default || 'true'
    _verify.sheet = _verify.sheet || 'Sheet1'
    _verify.range = _verify.range || 0
    _verify.columns = _columns
    _verify.scripts = _verify.scripts || []
    _verify.uniques = _verify.uniques || []

    if (window.GLOB.process && card.intertype === 'system') {
      _verify.workFlow = _verify.workFlow || 'false'
      _verify.flowType = 'start'
      _verify.flowSql = _verify.flowSql || 'true'
    } else {
      delete _verify.workFlow
      delete _verify.flowType
      delete _verify.flowSql
    }

    this.setState({
      verify: {
        ..._verify,
        default: _verify.default || 'true',
        sheet: _verify.sheet || 'Sheet1',
        range: _verify.range || 0,
        columns: _columns,
        scripts: _verify.scripts || [],
        uniques: _verify.uniques || []
      }
      searchKey: '',
      verify: _verify
    }, () => {
      this.resetUniqueColumns()
    })
@@ -325,17 +432,18 @@
    
    let _scriptSql = `Select distinct func+Remark as funcname,longparam, s.Sort from  s_custom_script s inner join (select OpenID from sapp where ID=@Appkey@) p on s.openid = case when s.appkey='' then s.openid else p.OpenID end order by s.Sort`

    _scriptSql = Utils.formatOptions(_scriptSql)
    _scriptSql = Utils.formatOptions(_scriptSql, 'x')

    let _sParam = {
      func: 'sPC_Get_SelectedList',
      LText: _scriptSql,
      obj_name: 'data',
      arr_field: 'funcname,longparam'
      arr_field: 'funcname,longparam',
      exec_type: 'x'
    }
    
    _sParam.timestamp = moment().format('YYYY-MM-DD HH:mm:ss')
    _sParam.secretkey = Utils.encrypt(_sParam.LText, _sParam.timestamp)
    _sParam.secretkey = Utils.encrypt('', _sParam.timestamp)
    _sParam.open_key = Utils.encryptOpenKey(_sParam.secretkey, _sParam.timestamp) // 云端数据验证
    
    Api.getCloudConfig(_sParam).then(res => {
@@ -441,6 +549,8 @@

      confirm({
        content: '部分字段名称与显示列不一致，是否更新？',
        okText: '更新',
        cancelText: '不更新',
        onOk() {
          _columns = _columns.map(item => {
            let key = item.Column.toLowerCase()
@@ -504,7 +614,7 @@
    })
  }

  columnChange = (values) => {
  columnChange = (values, callback) => {
    let verify = JSON.parse(JSON.stringify(this.state.verify))

    let fields = verify.columns.map(item => item.Column)
@@ -515,6 +625,8 @@
        duration: 5
      })
      return
    } else {
      callback()
    }

    values.uuid = Utils.getuuid()
@@ -697,33 +809,44 @@
        if (!err) {
          let _verify = {...verify, ...values}

          if (_verify.excelHandle !== 'true') { 
            delete _verify.excel_func
          }
          if (_verify.default === 'false' && _verify.scripts.length === 0) {
            notification.warning({
              top: 92,
              message: '不执行默认sql时，必须设置自定义脚本！',
              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: '请设置Excel列字段!',
              duration: 5
            })
            return
            error = '请设置Excel列字段!'
          } 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: '忽略首行时，会使用Text值校验Excel首行内容，Text值与Excel表首行内容相同，且均不可为空！',
                duration: 5
              })
              return
              error = '忽略首行时，会使用Text值校验Excel首行内容，Text值与Excel表首行内容相同，且均不可为空！'
            }
          }

          if (error) {
            notification.warning({
              top: 92,
              message: error,
              duration: 5
            })
            return
          }

          _verify.columns.sort((a, b) => {
@@ -756,22 +879,17 @@
            resolve(_verify)
          }
        } else {
          notification.warning({
            top: 92,
            message: '请设置Excel表名!',
            duration: 5
          })
          this.setState({activeKey: 'basemsg'})
        }
      })
    })
  }

  onOptionChange = (e, key) => {
  onOptionChange = (value, key) => {
    const { verify } = this.state
    let value = e.target.value

    this.setState({
      verify: {...verify, default: value}
      verify: {...verify, [key]: value}
    })
  }

@@ -833,7 +951,7 @@
  render() {
    const { card } = this.props
    const { getFieldDecorator } = this.props.form
    const { verify, excelColumns, scriptsColumns, uniqueColumns, activeKey } = this.state
    const { verify, excelColumns, scriptsColumns, uniqueColumns, activeKey, searchKey } = this.state
    const formItemLayout = {
      labelCol: {
        xs: { span: 24 },
@@ -852,7 +970,31 @@
          <TabPane tab="基础验证" key="basemsg">
            <Form {...formItemLayout}>
              <Row gutter={24}>
                {card.intertype === 'system' ? <Col span={8}>
                  <Form.Item label="默认sql">
                    <Radio.Group value={verify.default} onChange={(e) => this.onOptionChange(e.target.value, 'default')}>
                      <Radio value="true">执行</Radio>
                      <Radio value="false">不执行</Radio>
                    </Radio.Group>
                  </Form.Item>
                </Col> : null}
                <Col span={8}>
                  <Form.Item label={
                    <Tooltip placement="topRight" title="自定义验证Excel格式，可用参数为 XLSX、workbook、btn、callback。">
                      <QuestionCircleOutlined className="mk-form-tip" />
                      导入格式
                    </Tooltip>
                  }>
                    {getFieldDecorator('excelHandle', {
                      initialValue: verify.excelHandle
                    })(
                    <Radio.Group onChange={(e) => this.onOptionChange(e.target.value, 'excelHandle')}>
                      <Radio value="false">默认</Radio>
                      <Radio value="true">自定义</Radio>
                    </Radio.Group>)}
                  </Form.Item>
                </Col>
                {verify.excelHandle !== 'true' ? <Col span={8}>
                  <Form.Item label={
                    <Tooltip placement="bottomLeft" title="导入时工作表名与excel中必须一致，注：工作表名为Sheet1且excel中仅有一个工作表时不进行表名验证。">
                      <QuestionCircleOutlined className="mk-form-tip" />
@@ -869,7 +1011,7 @@
                      ]
                    })(<Input placeholder="" autoComplete="off" />)}
                  </Form.Item>
                </Col>
                </Col> : null}
                <Col span={8}>
                  <Form.Item label={
                    <Tooltip placement="bottomLeft" title="忽略首行时，会校验excel中表头名称与excel列设置是否一致。">
@@ -882,9 +1024,35 @@
                    })(<InputNumber min={0} max={100} precision={0} />)}
                  </Form.Item>
                </Col>
                {card.intertype === 'system' ? <Col span={8}>
                  <Form.Item label={'默认sql'}>
                    <Radio.Group value={verify.default} onChange={this.onOptionChange}>
                {verify.excelHandle === 'true' ? <Col span={24} style={{paddingLeft: '30px'}}>
                  <Form.Item wrapperCol={ {xs: { span: 24 }, sm: { span: 24 }} } label="">
                    {getFieldDecorator('excel_func', {
                      initialValue: verify.excel_func || '',
                      rules: [
                        {
                          required: true,
                          message: '请填写自定义逻辑!'
                        }
                      ]
                    })(<CodeMirror mode="text/javascript" theme="cobalt" />)}
                  </Form.Item>
                </Col> : null}
                {window.GLOB.process && card.intertype === 'system' ? <Col span={8}>
                  <Form.Item label={
                    <Tooltip placement="bottomLeft" title="导入Excel工作流仅支持发起流程。">
                      <QuestionCircleOutlined className="mk-form-tip" />
                      工作流
                    </Tooltip>
                  }>
                    <Radio.Group value={verify.workFlow} onChange={(e) => {this.onOptionChange(e.target.value, 'workFlow')}}>
                      <Radio value="true">开启</Radio>
                      <Radio value="false">不开启</Radio>
                    </Radio.Group>
                  </Form.Item>
                </Col> : null}
                {verify.workFlow === 'true' ? <Col span={8}>
                  <Form.Item label="默认sql（工作流）">
                    <Radio.Group value={verify.flowSql} onChange={(e) => {this.onOptionChange(e.target.value, 'flowSql')}}>
                      <Radio value="true">执行</Radio>
                      <Radio value="false">不执行</Radio>
                    </Radio.Group>
@@ -901,13 +1069,13 @@
          } key="excelcolumn">
            <ColumnForm columnChange={this.columnChange}/>
            <Button className="excel-col-add mk-green" title="添加显示列字段" onClick={this.columnFieldInput}>
              同步显示列
              同步字段集
            </Button>
            <Button className="excel-col-add mk-red" title="清空Excel列" onClick={this.clearField}>
              清空Excel列
            </Button>
            <Col style={{fontSize: '12px', color: '#757575', paddingLeft: '10px'}} span={24}>注：数值类型（int 或 decimal），内容为必填；最大值和最小值在类型为数值时有效。导入-初始化：用于excel中不存在，导入时需要初始化的字段</Col>
            <EditTable actions={['edit', 'move', 'copy', 'del', 'extra:required:是否必填']} type="excelcolumn" data={verify.columns} columns={excelColumns} onChange={this.changeColumns}/>
            <EditTable actions={['edit', 'move', 'copy', 'del', 'extra:required:是否必填']} searchKey={searchKey} type="excelcolumn" data={verify.columns} columns={excelColumns} onChange={this.changeColumns}/>
          </TabPane>
          {card.intertype === 'system' ? <TabPane tab={
            <span>
@@ -916,7 +1084,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>
@@ -925,7 +1093,7 @@
            </span>
          } key="scripts" id="mk-exin-script">
            <FullScripts
              verify={verify}
              scripts={verify.scripts}
              getScriptsFullForm={() => this.scriptsFullForm}
              getScriptsForm={() => this.scriptsForm}
              handleStatus={this.handleStatus}
@@ -936,6 +1104,7 @@
                btn={this.props.card}
                usefulfields={verify.columns}
                scripts={verify.scripts}
                workFlow={verify.workFlow}
                systemScripts={this.state.systemScripts}
                scriptsChange={this.scriptsChange}
                wrappedComponentRef={(inst) => this.scriptsFullForm = inst}
@@ -945,13 +1114,19 @@
              btn={this.props.card}
              usefulfields={verify.columns}
              scripts={verify.scripts}
              workFlow={verify.workFlow}
              systemScripts={this.state.systemScripts}
              scriptsChange={this.scriptsChange}
              wrappedComponentRef={(inst) => this.scriptsForm = inst}
            />
            <EditTable actions={['move']} data={verify.scripts} columns={scriptsColumns} onChange={(scripts) => {this.setState({verify: {...verify, scripts}})}}/>
          </TabPane> : null}
          <TabPane tab="信息提示" key="tip">
          <TabPane tab={
            <span>
              信息提示
              {activeKey === 'excelcolumn' ? <span onClick={(e) => {e.stopPropagation()}}><Search className="mk-search-fields" defaultValue={searchKey} allowClear onSearch={(val, e) => {e.stopPropagation();this.setState({searchKey: val})}} /></span> : null}
            </span>
          } key="tip">
            <Form {...formItemLayout}>
              <Row gutter={24}>
                <Col offset={6} span={6}>