|
- using DataSendApi.Program.BLL.Token;
- using DataSendApi.Program.CustomizeAttribute;
- using DataSendApi.Program.Model;
- using DataSendApi.Program.Oracle;
- using System;
- using System.Collections;
- using System.Collections.Generic;
- using System.Linq;
- namespace DataSendApi.Program.BLL
- {
- class DataTableModel
- {
- /// <summary>
- /// 当前类型
- /// </summary>
- public Type Type { get; set; }
- /// <summary>
- /// 当前表
- /// </summary>
- public CustomizeTableAttribute CustomizeTable { get; set; } = new CustomizeTableAttribute();
- /// <summary>
- /// 当前列集合
- /// </summary>
- public List<CustomizeFieldAttribute> CustomizeFieldLst { get; set; } = new List<CustomizeFieldAttribute>();
- /// <summary>
- /// 当前数据集合
- /// </summary>
- public List<Hashtable> CustomizeData { get; set; } = new List<Hashtable>();
- }
- public class BusinessProcess
- {
- /// <summary>
- /// 通过excel地址, 处理数据到数据库
- /// </summary>
- public ReturnEntity HandleByDatabase(string path)
- {
- var retObj = new ReturnEntity();
- //获取Excel的数据模型
- var data = new HandleExcel().ReadFromExcelFile(path);
- //表名
- string TableName = string.Empty;
- //列名
- List<string> ColoumnsName = new List<string>();
- //数据集合
- List<List<string>> DataLst = new List<List<string>>();
-
-
- #region 效验数据 并获取数据集合
- //校验数据模型
- if (data.Count <= 4)
- {
- retObj.Code = 1;
- retObj.Message = "数据导入失败,表格行数异常或无数据";
- return retObj;
- }
- if (!data[1][0].Contains("$"))
- {
- retObj.Code = 1;
- retObj.Message = "数据导入失败,模板异常,请重新下载模板";
- return retObj;
- }
- //获取表名
- TableName = data[1][0].Split('$')[1].ToUpper();
- if (!Common.AllTableName.Contains(TableName))
- {
- retObj.Code = 1;
- retObj.Message = "数据导入失败,该表不存在,模板异常,请重新下载模板";
- return retObj;
- }
- //获取列名
- foreach (var item in data[2])
- {
- if (!item.Contains("$"))
- {
- retObj.Code = 1;
- retObj.Message = "数据导入失败,模板异常,请重新下载模板";
- return retObj;
- }
- string coloumnName = item.Split('$')[1].ToUpper();
- if (!Common.AllColoumnName.Contains(TableName + "." + coloumnName))
- {
- retObj.Code = 1;
- retObj.Message = "数据导入失败,模板异常,请重新下载模板";
- return retObj;
- }
- ColoumnsName.Add(coloumnName);
- }
-
- for (int i = 4; i < data.Count; i++)
- {
- var _tempData = new List<string>();
- for (int j = 0; j < ColoumnsName.Count; j++)
- {
- if (j + 1 <= data[i].Count())
- {
- _tempData.Add(data[i][j]);
- }
- else
- {
- _tempData.Add("");
- }
- }
- DataLst.Add(_tempData);
- }
- #endregion
-
- #region 获取表 列属性
- //获取表列属性
- var curType = Type.GetType($"DataSendApi.Program.Model.{TableName}Entity");
- List<CustomizeFieldAttribute> FieldAttrLst = new List<CustomizeFieldAttribute>();
- CustomizeTableAttribute TableAttr = curType.GetCustomAttributes(typeof(CustomizeTableAttribute), false)[0] as CustomizeTableAttribute;
- foreach (var item in ColoumnsName)
- {
- var curPType = curType.GetProperty(item).GetCustomAttributes(typeof(CustomizeFieldAttribute), false)[0] as CustomizeFieldAttribute;
- FieldAttrLst.Add(curPType);
- }
- #endregion
-
- #region 验证数据
- //循环每行数据
- for (int i = 0; i < DataLst.Count; i++)
- {
- var rowCount = i + 1;
- var rowData = DataLst[i];
- //循环每列数据
- for (int j = 0; j < FieldAttrLst.Count; j++)
- {
- var rowAttr = FieldAttrLst[j];
- //数据校验
-
- //验证效验列
- if (rowAttr.IsExcelVerify && rowData[j] == "")
- {
- retObj.Code = 2;
- retObj.Message = $"数据导入失败,第[{rowCount}]行,[{rowAttr.ChineseColumnName}]列为必填项!";
- return retObj;
- }
- //验证填写基础数据是否正确
- if (rowAttr.IsJson)
- {
- if (rowAttr.TsVerify == "1" && rowData[j].Replace("|", "").Length > 0)
- {
- string _t = string.Empty;
- foreach (var ts in rowData[j].Split('|'))
- {
- if (string.IsNullOrEmpty(ts)) continue;
-
- var _temp = Common.GGSJZDLst.FirstOrDefault(P => P.ZDLX == rowAttr.JsonName && P.MC == ts);
- if (_temp == null || string.IsNullOrEmpty(_temp.DM))
- {
- retObj.Code = 2;
- retObj.Message = $"数据导入失败,第[{rowCount}]行,[{rowAttr.ChineseColumnName}]请参照对应的字段表填写名称!";
- return retObj;
- }
- _t += _temp.DM + "||";
- }
- rowData[j] = _t.Substring(0, _t.Length - 2);
- }
- else
- {
- var _temp = Common.GGSJZDLst.FirstOrDefault(P => P.ZDLX == rowAttr.JsonName && P.MC == rowData[j]);
- if (_temp == null || string.IsNullOrEmpty(_temp.DM))
- {
- retObj.Code = 2;
- retObj.Message = $"数据导入失败,第[{rowCount}]行,[{rowAttr.ChineseColumnName}]请参照对应的字段表填写名称!";
- return retObj;
- }
- rowData[j] = _temp.DM;
- }
- }
- //验证值是否为数字
- if (rowAttr.ColumnType == "decimal")
- {
- try
- {
- Convert.ToDecimal(rowData[j]);
- }
- catch
- {
- retObj.Code = 2;
- retObj.Message = $"数据导入失败,第[{rowCount}]行,[{rowAttr.ChineseColumnName}]列只能填写数字!";
- return retObj;
- }
- }
- //数据格式验证
- if (rowAttr.ColumnFormat != "" && rowAttr.ColumnFormat.Length > 0)
- {
- try
- {
- if (rowData[j] != null && rowData[j] != "")
- {
- if (rowData[j].Length == rowAttr.ColumnFormat.Length)
- {
- Convert.ToDateTime(rowData[j]);
- }
- else
- {
- retObj.Code = 2;
- retObj.Message = $"数据导入失败,第[{rowCount}]行,[{rowAttr.ChineseColumnName}]列未按照格式填写,参考格式{rowAttr.ColumnFormat}!";
- return retObj;
- }
- }
- }
- catch
- {
- retObj.Code = 2;
- retObj.Message = $"数据导入失败,第[{rowCount}]行,[{rowAttr.ChineseColumnName}]列未按照格式填写,参考格式{rowAttr.ColumnFormat}!";
- return retObj;
- }
- }
- //数据长度验证
- if (rowAttr.ColumnType == "string")
- {
- if (!rowAttr.IsJson && rowData[j].Length > rowAttr.ColumnLength)
- {
-
- retObj.Code = 2;
- retObj.Message = $"数据导入失败,第[{rowCount}]行,[{rowAttr.ChineseColumnName}]列字符过多,不能超过{rowAttr.ColumnLength}个字符!";
- return retObj;
- }
- }
- //换行验证
- if (rowData[j] != null && rowData[j] != "")
- {
- if (rowData[j].IndexOf('\n') >= 0)
- {
- retObj.Code = 2;
- retObj.Message = $"数据导入失败,第[{rowCount}]行,[{rowAttr.ChineseColumnName}]列不允许换行!";
- return retObj;
- }
- }
-
-
- }
- }
- #endregion
-
-
- #region 封装数据
- //封装数据
- DataTableModel dataTableModel = new DataTableModel();
- dataTableModel.Type = curType;
- dataTableModel.CustomizeTable = TableAttr;
-
- foreach (var item in dataTableModel.Type.GetProperties())
- {
- var _temp = (item.GetCustomAttributes(typeof(CustomizeFieldAttribute), false)[0] as CustomizeFieldAttribute);
- if (_temp.IsDatabase)
- dataTableModel.CustomizeFieldLst.Add(_temp);
- }
- for (int i = 0; i < DataLst.Count; i++)
- {
- var _data = DataLst[i];
- Hashtable _ht = new Hashtable();
-
- foreach (var item in dataTableModel.CustomizeFieldLst)
- {
- if (item.IsPrimaryKey)
- {
- _ht.Add(item.DatabaseColumnName, Guid.NewGuid().ToString().Replace("-", ""));
- continue;
- }
- if (item.DatabaseColumnName == "SJCJSJ")
- {
- _ht.Add("SJCJSJ", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"));
- continue;
- }
- if (item.DatabaseColumnName == "IsPush")
- {
- _ht.Add("IsPush", "0");
- continue;
- }
-
- for (int j = 0; j < FieldAttrLst.Count; j++)
- {
- if (FieldAttrLst[j].DatabaseColumnName == item.DatabaseColumnName)
- {
- _ht.Add(item.DatabaseColumnName, _data[j]);
- }
- }
- }
- dataTableModel.CustomizeData.Add(_ht);
- }
- #endregion
-
-
-
- return ConnentionDataByDatabase(dataTableModel);
- }
- /// <summary>
- /// 获取推送数据
- /// </summary>
- public ReturnEntity GetDataPush(string table)
- {
- var retObj = new ReturnEntity();
-
- #region 获取表 列属性
- //获取表列属性
- var curType = Type.GetType($"DataSendApi.Program.Model.{table.ToUpper()}Entity");
- List<CustomizeFieldAttribute> FieldAttrLst = new List<CustomizeFieldAttribute>();
- CustomizeTableAttribute TableAttr = curType.GetCustomAttributes(typeof(CustomizeTableAttribute), false)[0] as CustomizeTableAttribute;
- foreach (var item in curType.GetProperties())
- {
- var curPType = item.GetCustomAttributes(typeof(CustomizeFieldAttribute), false)[0] as CustomizeFieldAttribute;
- FieldAttrLst.Add(curPType);
- }
- #endregion
-
- #region 获取数据
- string _sql = $"SELECT * FROM {table.ToUpper()} WHERE IsPush = '0' ";
- var _tempLst = DbContext.Query<dynamic>(_sql);
- if (_tempLst == null || _tempLst.Count() <= 0)
- {
- retObj.Code = 4;
- retObj.Message = "该表无可推送数据";
- return retObj;
- }
- string retrunJson = $@"
- {{
- ""dataObjName"" : ""{table.ToLower()}"",
- ""fileds"" : [";
-
- foreach (var item in _tempLst)
- {
- var fields = item as IDictionary<string, object>;
- retrunJson += $@"
- {{";
- foreach (var val in FieldAttrLst)
- {
- if (val.IsExcel)
- {
- var value = string.Empty;
- if (fields[val.DatabaseColumnName] != null)
- value = fields[val.DatabaseColumnName].ToString();
- //获取特殊数据库数据
- if (val.TsVerify == "2")
- {
- value = DbContext.ExecuteScalar<string>(" SELECT COUNT(*) FROM ods_dyfzqkjcsj");
- }
-
- var num = value.IndexOf('\n');
- if ((num) >= 0)
- {
- value = value.Replace("\n", ",");
- }
-
-
- retrunJson += $"\"{val.DatabaseColumnName.ToLower()}\" : \"{value}\",";
- }
- }
- retrunJson = retrunJson.Substring(0, retrunJson.Length - 1);
- retrunJson += $@"
- }},";
- }
- retrunJson = retrunJson.Substring(0, retrunJson.Length - 1);
- retrunJson += @"
- ]
- }";
- retObj.Data = retrunJson;
-
- return retObj;
- #endregion
- }
-
- /// <summary>
- /// 执行推送
- /// </summary>
- public ReturnEntity ExecDataPush(string tablename)
- {
- var ret = GetDataPush(tablename);
-
- if (ret.Code != 0) return ret;
-
-
-
- return new DataCollectionService().PushData(ret.Data as string);
- }
-
- /// <summary>
- /// 数据增量查询
- /// </summary>
- /// <param name="tableName">表名</param>
- /// <param name="startTime">开始时间(yyyy-MM-dd hh:mm:ss)</param>
- /// <param name="endTime">结束时间(yyyy-MM-dd hh:mm:ss)</param>
- /// <param name="page">页码</param>
- /// <param name="limit">每页大小</param>
- /// <returns></returns>
- public ReturnEntity GetPushDataAddCount(
- string tableName,
- string startTime,
- string endTime,
- int page = 1,
- int limit = 100
- )
- {
- return new DataCollectionService().GetPushDataAddCount(tableName, startTime, endTime, page, limit);
- }
-
- public ReturnEntity GetTableCount(string tableName)
- {
- string _sql = "";
- foreach (var item in tableName.Split(','))
- {
- if (string.IsNullOrEmpty(item)) continue;
- _sql += $@"
- SELECT '{item}' as tablename ,COUNT(*) as count FROM {item.ToUpper()} WHERE IsPush = '0'
- ";
- _sql += " UNION ALL";
- }
- var retObj = new ReturnEntity();
-
- _sql = _sql.Length > 0 ? _sql.Substring(0, _sql.Length - 10) : _sql;
- if (!string.IsNullOrEmpty(_sql))
- {
-
- retObj.Data = DbContext.Query<TableNameModel>(_sql);
-
- }
-
-
- return retObj;
- }
-
- public ReturnEntity UpdatePushStatus(string tableName)
- {
-
- var retObj = new ReturnEntity();
- string _sql = $"UPDATE {tableName.ToUpper()} SET IsPush = '1' WHERE IsPush = '0' ";
-
- retObj.Data = DbContext.Execute(_sql);
-
- return retObj;
- }
-
- /// <summary>
- /// 将数据写入数据库
- /// </summary>
- private ReturnEntity ConnentionDataByDatabase(DataTableModel dm)
- {
- var retObj = new ReturnEntity();
-
- var tran = DapperHelper.OpenCurrentDbConnection().BeginTransaction();
- int row = 1;
- int addCount = 0;
- int updateCount = 0;
- try
- {
- foreach (var item in dm.CustomizeData)
- {
- string _sqlJoinVerify = JoinVerify(dm.CustomizeTable, dm.CustomizeFieldLst, item);
- var wyzj = DbContext.ExecuteScalar<string>(_sqlJoinVerify, null, tran);
- if (!string.IsNullOrEmpty(wyzj))
- {
- //更新
- string _sqlJoinuUpdate = JoinuUpdate(dm.CustomizeTable, dm.CustomizeFieldLst, item, wyzj);
- DbContext.Execute(_sqlJoinuUpdate, null, tran);
- updateCount++;
- }
- else
- {
- //插入
- string _sqlJoinInsert = JoinInsert(dm.CustomizeTable, dm.CustomizeFieldLst, item);
- DbContext.Execute(_sqlJoinInsert, null, tran);
- addCount++;
- }
- row++;
- }
- tran.Commit();
- }
- catch (Exception ex)
- {
- tran.Rollback();
- retObj.Code = 2;
- retObj.Message = $"第{row}行数据写入数据库时异常,数据已回滚,异常原因:" + ex.Message;
- }
- retObj.Data = new { addCount = addCount, updateCount = updateCount };
- return retObj;
- }
-
- /// <summary>
- /// 拼接查询验证SQL
- /// </summary>
- private string JoinVerify(CustomizeTableAttribute customizeTable, List<CustomizeFieldAttribute> fieldAttributes, Hashtable hashtable)
- {
- string _sql = string.Empty;
-
- _sql = $" SELECT * FROM {customizeTable.DatabaseTableName} WHERE 1 = 1 ";
- foreach (var item in fieldAttributes)
- {
- if (item.IsExcelVerify)
- _sql += $" AND {item.DatabaseColumnName} = '{hashtable[item.DatabaseColumnName].ToString()}' ";
- }
- return _sql;
- }
-
- /// <summary>
- /// 拼接插入SQL
- /// </summary>
- private string JoinInsert(CustomizeTableAttribute customizeTable, List<CustomizeFieldAttribute> fieldAttributes, Hashtable hashtable)
- {
- string _sql = string.Empty;
- _sql = $" INSERT INTO {customizeTable.DatabaseTableName} (";
-
- foreach (var item in fieldAttributes)
- {
- _sql += $"{item.DatabaseColumnName},";
- }
- _sql = _sql.Substring(0, _sql.Length - 1);
- _sql += ") VALUES (";
- foreach (var item in fieldAttributes)
- {
- _sql += $"'{hashtable[item.DatabaseColumnName]}',";
- }
-
- _sql = _sql.Substring(0, _sql.Length - 1);
- return _sql += ")";
- }
-
- /// <summary>
- /// 拼接更新SQL
- /// </summary>
- private string JoinuUpdate(CustomizeTableAttribute customizeTable, List<CustomizeFieldAttribute> fieldAttributes, Hashtable hashtable, string wyzj)
- {
- string _sql = string.Empty;
- _sql = $" UPDATE {customizeTable.DatabaseTableName} SET ";
- string _tempSql = string.Empty;
- foreach (var item in fieldAttributes)
- {
-
- if (!item.IsPrimaryKey && !item.IsExcelVerify)
- _sql += $"{item.DatabaseColumnName} = '{hashtable[item.DatabaseColumnName]}',";
- if (item.IsPrimaryKey)
- _tempSql += $" WHERE {item.DatabaseColumnName} = '{wyzj}'";
- }
- return _sql.Substring(0, _sql.Length - 1) + _tempSql;
- }
-
- }
-
- public class TableNameModel
- {
- public string TABLENAME { get; set; }
- public string COUNT { get; set; }
- }
- }
|