manHourHousekeeper/fhKeeper/formulahousekeeper/customerBuler-crm/src/pages/biReport/function.ts

/**
 * 生成拖拽表和关联表的 SQL
 * @param tableList 拖拽表数据
 * @param rangeFilterList 数据范围
 * @param addEditData 添加表单数据
 * @returns string
 */
export function generateSql(tableList: any[] = [], rangeFilterList: any[] = [], addEditData: addEditDataType) {
  // 添加表明前缀
  const alphabet = 'abcdefghijklmnopqrstuvwxyz';
  addEditData.mindMapJSON.selectNodes.forEach((obj, index) => {
    obj.data.seqPrefix = alphabet[index % 26]; // 通过取余处理
  });

  // 用到的那些表
  const list = filterTableData(tableList, rangeFilterList, addEditData)
  const tableSqlList = generateDragAndDropTableSql(list, tableList)
  const relationshipSqlList = associationRelationshipArray(addEditData.mindMapJSON.selectNodes, list)

  // 生成拖拽表 SQL
  const tableSqlStr = `select ${tableSqlList.map(item => `${item.seqPrefix}.${item.columnName} as '${item.columnComment}'`).join(', ')}`

  // 生成关联关系 SQL
  const relationshipTableSqlStr = relationshipSqlList.map(item => item.paragraphSql).join(' ')

  console.log(tableSqlStr, '<======= 表格SQL')
  console.log(relationshipTableSqlStr, '<======= 关联关系SQL')

  return `${tableSqlStr} ${relationshipTableSqlStr}`
}

/**
 * 生成数据范围 SQL
 * @param rangeFilterList 数据范围数据
 * @param addEditData 新建表单页面数据
 * @returns string
 */
export function rangeGetSql(rangeFilterList: any[] = [], addEditData: addEditDataType) {
  const alphabet = 'abcdefghijklmnopqrstuvwxyz';
  addEditData.mindMapJSON.selectNodes.forEach((obj, index) => {
    obj.data.seqPrefix = alphabet[index % 26]; // 通过取余处理
  });
  const nodes = addEditData.mindMapJSON.selectNodes.map(item => item.data)
  const list = rangeFilterList.filter(item => item.filterValueThree)
  const newList = list.map((item) => {
    let sql = ''
    const seqPrefix = nodes.find(node => node.tblName == item.tableName).seqPrefix
    // 自定义日期筛选条件
    if(['datetime', 'timetamp', 'timestamp'].includes(item.dataType) && item.filterValueTwo == '自定义' && !item.valueFlag) {
      const templateItem = (item.list || []).find((em: any) => em.value == item.filterValueTwo)
      sql = templateItem.sql.replace(/表别名/g, seqPrefix)
      .replace(/字段名称/g, item.columnName)
      .replace(/值1/g, item.filterValueThree[0])
      .replace(/值2/g, item.filterValueThree[1]);
    }

    // 普通日期筛选条件
    if(['datetime', 'timetamp', 'timestamp'].includes(item.dataType) && item.filterValueTwo != '自定义' && !item.valueFlag) {
      const templateItem = (item.list || []).find((em: any) => em.value == item.filterValueTwo)
      sql = templateItem.sql.replace(/表别名/g, seqPrefix)
      .replace(/字段名称/g, item.columnName)
      .replace(/值/g, item.filterValueThree)
    }

    // 文本框
    if(['varchar', 'text'].includes(item.dataType) && !item.valueFlag) {
      const templateItem = (item.list || []).find((em: any) => em.value == item.filterValueTwo)
      sql = `${seqPrefix}.${item.columnName} ${templateItem.sql} concat('%','${item.filterValueThree}','%')`
    }

    // 数字输入框
    if(['decimal', 'int'].includes(item.dataType) && !item.valueFlag) {
      const templateItem = (item.list || []).find((em: any) => em.value == item.filterValueTwo)
      sql = templateItem.sql.replace(/表别名/g, seqPrefix)
      .replace(/字段名称/g, item.columnName)
      .replace(/值/g, item.filterValueThree)
    }

    // 下拉框
    if(item.valueFlag) {
      sql = `${seqPrefix}.${item.columnName} = '${item.filterValueThree}`
    }

    return {
      ...item,
      strSQL: sql
    }
  })

  return `where ${newList.map((item: any) => item.strSQL).join(' and ')}`
}

/**
 * 生成关联关系 SQL 的数组
 * @param nodes 节点数据
 * @param list 用到的那些表
 * @returns Array
 */
function associationRelationshipArray(nodes: any[] = [], _list: any[]) {
  const nodeList = nodes.map(item => item.data)
  const mainTable = nodeList.filter(item => item.seqPrefix == 'a').map(item => {
    return {
      ...item,
      paragraphSql: `from ${item.tblName} ${item.seqPrefix}`
    }
  })
  const newList = nodeList.filter(item => item.seqPrefix != 'a')

  const sqlList = newList.map(item => {
    const strSeqPrefix = nodeList.find(node => node.tblName == item.fromTbl).seqPrefix
    let strSql = `left join ${item.toTbl} ${item.seqPrefix} on ${strSeqPrefix}.${item.fromCol} = ${item.seqPrefix}.${item.toCol}`
    if(item.toTbl == 'sys_dict') {
      strSql = strSql + ` and ${item.seqPrefix}.code = '${item.dictCode}'`
    }
    item.paragraphSql = strSql
    return item
  })

  return [...mainTable, ...sqlList]
}

/**
 * 生成拖拽表 SQL 的数组
 * @param list 用到的那些表
 * @param tableList 拖拽表数据
 * @returns Array
 */
function generateDragAndDropTableSql(list: any[] = [], tableList: any[] = []) {
  const result = tableList.filter(item1 => {
    return list.some(item2 => item2.tblName === item1.tableName);
  }).map(item1 => {
    const matchedItem2 = list.find(item2 => `${item2.tblName}_${item2.dictCode}` === `${item1.tableName}_${item1.dictCode}`);
    const matchedItem2s = list.find(item2 => item2.tblName === item1.tableName);
    return {
      seqPrefix: matchedItem2?.seqPrefix || matchedItem2s.seqPrefix,
      tblName: matchedItem2?.tblName || matchedItem2s.seqPrefix,
      columnComment: item1.columnComment,
      columnName: item1.columnName
    };
  });

  return result
}

/**
 * 过滤数据中所用到的表数据
 * @param tableList 拖拽表数据
 * @param rangeFilterList 数据范围数据
 * @param addEditData 新建表单页面数据
 * @returns Array
 */
function filterTableData(tableList: any[] = [], rangeFilterList: any[] = [], addEditData: addEditDataType) {
  // 所有节点表数据
  const nodesAllDataList = addEditData.mindMapJSON.selectNodes.map(item => item.data)
  // tableList 所用到的表数据
  const tableNodesDataList = nodesAllDataList.filter(item1 =>
    tableList.some(item2 => item1.tblName === item2.tableName)
  );
  // rangeFilter 所用到的表数据
  const rangeFilterNodesDataList = nodesAllDataList.filter(item1 =>
    rangeFilterList.some(item2 => item1.tblName === item2.tableName)
  );
  // 所用到的表数据
  const tableUsedList = [...new Set([...tableNodesDataList, ...rangeFilterNodesDataList].filter(item => item.tblName))]

  return tableUsedList || []
}