智慧大脑
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 
 
 
 
 

546 line
21 KiB

  1. using DataSendApi.Program.BLL.Token;
  2. using DataSendApi.Program.CustomizeAttribute;
  3. using DataSendApi.Program.Model;
  4. using DataSendApi.Program.Oracle;
  5. using System;
  6. using System.Collections;
  7. using System.Collections.Generic;
  8. using System.Linq;
  9. namespace DataSendApi.Program.BLL
  10. {
  11. class DataTableModel
  12. {
  13. /// <summary>
  14. /// 当前类型
  15. /// </summary>
  16. public Type Type { get; set; }
  17. /// <summary>
  18. /// 当前表
  19. /// </summary>
  20. public CustomizeTableAttribute CustomizeTable { get; set; } = new CustomizeTableAttribute();
  21. /// <summary>
  22. /// 当前列集合
  23. /// </summary>
  24. public List<CustomizeFieldAttribute> CustomizeFieldLst { get; set; } = new List<CustomizeFieldAttribute>();
  25. /// <summary>
  26. /// 当前数据集合
  27. /// </summary>
  28. public List<Hashtable> CustomizeData { get; set; } = new List<Hashtable>();
  29. }
  30. public class BusinessProcess
  31. {
  32. /// <summary>
  33. /// 通过excel地址, 处理数据到数据库
  34. /// </summary>
  35. public ReturnEntity HandleByDatabase(string path)
  36. {
  37. var retObj = new ReturnEntity();
  38. //获取Excel的数据模型
  39. var data = new HandleExcel().ReadFromExcelFile(path);
  40. //表名
  41. string TableName = string.Empty;
  42. //列名
  43. List<string> ColoumnsName = new List<string>();
  44. //数据集合
  45. List<List<string>> DataLst = new List<List<string>>();
  46. #region 效验数据 并获取数据集合
  47. //校验数据模型
  48. if (data.Count <= 4)
  49. {
  50. retObj.Code = 1;
  51. retObj.Message = "数据导入失败,表格行数异常或无数据";
  52. return retObj;
  53. }
  54. if (!data[1][0].Contains("$"))
  55. {
  56. retObj.Code = 1;
  57. retObj.Message = "数据导入失败,模板异常,请重新下载模板";
  58. return retObj;
  59. }
  60. //获取表名
  61. TableName = data[1][0].Split('$')[1].ToUpper();
  62. if (!Common.AllTableName.Contains(TableName))
  63. {
  64. retObj.Code = 1;
  65. retObj.Message = "数据导入失败,该表不存在,模板异常,请重新下载模板";
  66. return retObj;
  67. }
  68. //获取列名
  69. foreach (var item in data[2])
  70. {
  71. if (!item.Contains("$"))
  72. {
  73. retObj.Code = 1;
  74. retObj.Message = "数据导入失败,模板异常,请重新下载模板";
  75. return retObj;
  76. }
  77. string coloumnName = item.Split('$')[1].ToUpper();
  78. if (!Common.AllColoumnName.Contains(TableName + "." + coloumnName))
  79. {
  80. retObj.Code = 1;
  81. retObj.Message = "数据导入失败,模板异常,请重新下载模板";
  82. return retObj;
  83. }
  84. ColoumnsName.Add(coloumnName);
  85. }
  86. for (int i = 4; i < data.Count; i++)
  87. {
  88. var _tempData = new List<string>();
  89. for (int j = 0; j < ColoumnsName.Count; j++)
  90. {
  91. if (j + 1 <= data[i].Count())
  92. {
  93. _tempData.Add(data[i][j]);
  94. }
  95. else
  96. {
  97. _tempData.Add("");
  98. }
  99. }
  100. DataLst.Add(_tempData);
  101. }
  102. #endregion
  103. #region 获取表 列属性
  104. //获取表列属性
  105. var curType = Type.GetType($"DataSendApi.Program.Model.{TableName}Entity");
  106. List<CustomizeFieldAttribute> FieldAttrLst = new List<CustomizeFieldAttribute>();
  107. CustomizeTableAttribute TableAttr = curType.GetCustomAttributes(typeof(CustomizeTableAttribute), false)[0] as CustomizeTableAttribute;
  108. foreach (var item in ColoumnsName)
  109. {
  110. var curPType = curType.GetProperty(item).GetCustomAttributes(typeof(CustomizeFieldAttribute), false)[0] as CustomizeFieldAttribute;
  111. FieldAttrLst.Add(curPType);
  112. }
  113. #endregion
  114. #region 验证数据
  115. //循环每行数据
  116. for (int i = 0; i < DataLst.Count; i++)
  117. {
  118. var rowCount = i + 1;
  119. var rowData = DataLst[i];
  120. //循环每列数据
  121. for (int j = 0; j < FieldAttrLst.Count; j++)
  122. {
  123. var rowAttr = FieldAttrLst[j];
  124. //数据校验
  125. //验证效验列
  126. if (rowAttr.IsExcelVerify && rowData[j] == "")
  127. {
  128. retObj.Code = 2;
  129. retObj.Message = $"数据导入失败,第[{rowCount}]行,[{rowAttr.ChineseColumnName}]列为必填项!";
  130. return retObj;
  131. }
  132. //验证填写基础数据是否正确
  133. if (rowAttr.IsJson)
  134. {
  135. if (rowAttr.TsVerify == "1" && rowData[j].Replace("|", "").Length > 0)
  136. {
  137. string _t = string.Empty;
  138. foreach (var ts in rowData[j].Split('|'))
  139. {
  140. if (string.IsNullOrEmpty(ts)) continue;
  141. var _temp = Common.GGSJZDLst.FirstOrDefault(P => P.ZDLX == rowAttr.JsonName && P.MC == ts);
  142. if (_temp == null || string.IsNullOrEmpty(_temp.DM))
  143. {
  144. retObj.Code = 2;
  145. retObj.Message = $"数据导入失败,第[{rowCount}]行,[{rowAttr.ChineseColumnName}]请参照对应的字段表填写名称!";
  146. return retObj;
  147. }
  148. _t += _temp.DM + "||";
  149. }
  150. rowData[j] = _t.Substring(0, _t.Length - 2);
  151. }
  152. else
  153. {
  154. var _temp = Common.GGSJZDLst.FirstOrDefault(P => P.ZDLX == rowAttr.JsonName && P.MC == rowData[j]);
  155. if (_temp == null || string.IsNullOrEmpty(_temp.DM))
  156. {
  157. retObj.Code = 2;
  158. retObj.Message = $"数据导入失败,第[{rowCount}]行,[{rowAttr.ChineseColumnName}]请参照对应的字段表填写名称!";
  159. return retObj;
  160. }
  161. rowData[j] = _temp.DM;
  162. }
  163. }
  164. //验证值是否为数字
  165. if (rowAttr.ColumnType == "decimal")
  166. {
  167. try
  168. {
  169. Convert.ToDecimal(rowData[j]);
  170. }
  171. catch
  172. {
  173. retObj.Code = 2;
  174. retObj.Message = $"数据导入失败,第[{rowCount}]行,[{rowAttr.ChineseColumnName}]列只能填写数字!";
  175. return retObj;
  176. }
  177. }
  178. //数据格式验证
  179. if (rowAttr.ColumnFormat != "" && rowAttr.ColumnFormat.Length > 0)
  180. {
  181. try
  182. {
  183. if (rowData[j] != null && rowData[j] != "")
  184. {
  185. if (rowData[j].Length == rowAttr.ColumnFormat.Length)
  186. {
  187. Convert.ToDateTime(rowData[j]);
  188. }
  189. else
  190. {
  191. retObj.Code = 2;
  192. retObj.Message = $"数据导入失败,第[{rowCount}]行,[{rowAttr.ChineseColumnName}]列未按照格式填写,参考格式{rowAttr.ColumnFormat}!";
  193. return retObj;
  194. }
  195. }
  196. }
  197. catch
  198. {
  199. retObj.Code = 2;
  200. retObj.Message = $"数据导入失败,第[{rowCount}]行,[{rowAttr.ChineseColumnName}]列未按照格式填写,参考格式{rowAttr.ColumnFormat}!";
  201. return retObj;
  202. }
  203. }
  204. //数据长度验证
  205. if (rowAttr.ColumnType == "string")
  206. {
  207. if (!rowAttr.IsJson && rowData[j].Length > rowAttr.ColumnLength)
  208. {
  209. retObj.Code = 2;
  210. retObj.Message = $"数据导入失败,第[{rowCount}]行,[{rowAttr.ChineseColumnName}]列字符过多,不能超过{rowAttr.ColumnLength}个字符!";
  211. return retObj;
  212. }
  213. }
  214. //换行验证
  215. if (rowData[j] != null && rowData[j] != "")
  216. {
  217. if (rowData[j].IndexOf('\n') >= 0)
  218. {
  219. retObj.Code = 2;
  220. retObj.Message = $"数据导入失败,第[{rowCount}]行,[{rowAttr.ChineseColumnName}]列不允许换行!";
  221. return retObj;
  222. }
  223. }
  224. }
  225. }
  226. #endregion
  227. #region 封装数据
  228. //封装数据
  229. DataTableModel dataTableModel = new DataTableModel();
  230. dataTableModel.Type = curType;
  231. dataTableModel.CustomizeTable = TableAttr;
  232. foreach (var item in dataTableModel.Type.GetProperties())
  233. {
  234. var _temp = (item.GetCustomAttributes(typeof(CustomizeFieldAttribute), false)[0] as CustomizeFieldAttribute);
  235. if (_temp.IsDatabase)
  236. dataTableModel.CustomizeFieldLst.Add(_temp);
  237. }
  238. for (int i = 0; i < DataLst.Count; i++)
  239. {
  240. var _data = DataLst[i];
  241. Hashtable _ht = new Hashtable();
  242. foreach (var item in dataTableModel.CustomizeFieldLst)
  243. {
  244. if (item.IsPrimaryKey)
  245. {
  246. _ht.Add(item.DatabaseColumnName, Guid.NewGuid().ToString().Replace("-", ""));
  247. continue;
  248. }
  249. if (item.DatabaseColumnName == "SJCJSJ")
  250. {
  251. _ht.Add("SJCJSJ", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"));
  252. continue;
  253. }
  254. if (item.DatabaseColumnName == "IsPush")
  255. {
  256. _ht.Add("IsPush", "0");
  257. continue;
  258. }
  259. for (int j = 0; j < FieldAttrLst.Count; j++)
  260. {
  261. if (FieldAttrLst[j].DatabaseColumnName == item.DatabaseColumnName)
  262. {
  263. _ht.Add(item.DatabaseColumnName, _data[j]);
  264. }
  265. }
  266. }
  267. dataTableModel.CustomizeData.Add(_ht);
  268. }
  269. #endregion
  270. return ConnentionDataByDatabase(dataTableModel);
  271. }
  272. /// <summary>
  273. /// 获取推送数据
  274. /// </summary>
  275. public ReturnEntity GetDataPush(string table)
  276. {
  277. var retObj = new ReturnEntity();
  278. #region 获取表 列属性
  279. //获取表列属性
  280. var curType = Type.GetType($"DataSendApi.Program.Model.{table.ToUpper()}Entity");
  281. List<CustomizeFieldAttribute> FieldAttrLst = new List<CustomizeFieldAttribute>();
  282. CustomizeTableAttribute TableAttr = curType.GetCustomAttributes(typeof(CustomizeTableAttribute), false)[0] as CustomizeTableAttribute;
  283. foreach (var item in curType.GetProperties())
  284. {
  285. var curPType = item.GetCustomAttributes(typeof(CustomizeFieldAttribute), false)[0] as CustomizeFieldAttribute;
  286. FieldAttrLst.Add(curPType);
  287. }
  288. #endregion
  289. #region 获取数据
  290. string _sql = $"SELECT * FROM {table.ToUpper()} WHERE IsPush = '0' ";
  291. var _tempLst = DbContext.Query<dynamic>(_sql);
  292. if (_tempLst == null || _tempLst.Count() <= 0)
  293. {
  294. retObj.Code = 4;
  295. retObj.Message = "该表无可推送数据";
  296. return retObj;
  297. }
  298. string retrunJson = $@"
  299. {{
  300. ""dataObjName"" : ""{table.ToLower()}"",
  301. ""fileds"" : [";
  302. foreach (var item in _tempLst)
  303. {
  304. var fields = item as IDictionary<string, object>;
  305. retrunJson += $@"
  306. {{";
  307. foreach (var val in FieldAttrLst)
  308. {
  309. if (val.IsExcel)
  310. {
  311. var value = string.Empty;
  312. if (fields[val.DatabaseColumnName] != null)
  313. value = fields[val.DatabaseColumnName].ToString();
  314. //获取特殊数据库数据
  315. if (val.TsVerify == "2")
  316. {
  317. value = DbContext.ExecuteScalar<string>(" SELECT COUNT(*) FROM ods_dyfzqkjcsj");
  318. }
  319. var num = value.IndexOf('\n');
  320. if ((num) >= 0)
  321. {
  322. value = value.Replace("\n", ",");
  323. }
  324. retrunJson += $"\"{val.DatabaseColumnName.ToLower()}\" : \"{value}\",";
  325. }
  326. }
  327. retrunJson = retrunJson.Substring(0, retrunJson.Length - 1);
  328. retrunJson += $@"
  329. }},";
  330. }
  331. retrunJson = retrunJson.Substring(0, retrunJson.Length - 1);
  332. retrunJson += @"
  333. ]
  334. }";
  335. retObj.Data = retrunJson;
  336. return retObj;
  337. #endregion
  338. }
  339. /// <summary>
  340. /// 执行推送
  341. /// </summary>
  342. public ReturnEntity ExecDataPush(string tablename)
  343. {
  344. var ret = GetDataPush(tablename);
  345. if (ret.Code != 0) return ret;
  346. return new DataCollectionService().PushData(ret.Data as string);
  347. }
  348. /// <summary>
  349. /// 数据增量查询
  350. /// </summary>
  351. /// <param name="tableName">表名</param>
  352. /// <param name="startTime">开始时间(yyyy-MM-dd hh:mm:ss)</param>
  353. /// <param name="endTime">结束时间(yyyy-MM-dd hh:mm:ss)</param>
  354. /// <param name="page">页码</param>
  355. /// <param name="limit">每页大小</param>
  356. /// <returns></returns>
  357. public ReturnEntity GetPushDataAddCount(
  358. string tableName,
  359. string startTime,
  360. string endTime,
  361. int page = 1,
  362. int limit = 100
  363. )
  364. {
  365. return new DataCollectionService().GetPushDataAddCount(tableName, startTime, endTime, page, limit);
  366. }
  367. public ReturnEntity GetTableCount(string tableName)
  368. {
  369. string _sql = "";
  370. foreach (var item in tableName.Split(','))
  371. {
  372. if (string.IsNullOrEmpty(item)) continue;
  373. _sql += $@"
  374. SELECT '{item}' as tablename ,COUNT(*) as count FROM {item.ToUpper()} WHERE IsPush = '0'
  375. ";
  376. _sql += " UNION ALL";
  377. }
  378. var retObj = new ReturnEntity();
  379. _sql = _sql.Length > 0 ? _sql.Substring(0, _sql.Length - 10) : _sql;
  380. if (!string.IsNullOrEmpty(_sql))
  381. {
  382. retObj.Data = DbContext.Query<TableNameModel>(_sql);
  383. }
  384. return retObj;
  385. }
  386. public ReturnEntity UpdatePushStatus(string tableName)
  387. {
  388. var retObj = new ReturnEntity();
  389. string _sql = $"UPDATE {tableName.ToUpper()} SET IsPush = '1' WHERE IsPush = '0' ";
  390. retObj.Data = DbContext.Execute(_sql);
  391. return retObj;
  392. }
  393. /// <summary>
  394. /// 将数据写入数据库
  395. /// </summary>
  396. private ReturnEntity ConnentionDataByDatabase(DataTableModel dm)
  397. {
  398. var retObj = new ReturnEntity();
  399. var tran = DapperHelper.OpenCurrentDbConnection().BeginTransaction();
  400. int row = 1;
  401. int addCount = 0;
  402. int updateCount = 0;
  403. try
  404. {
  405. foreach (var item in dm.CustomizeData)
  406. {
  407. string _sqlJoinVerify = JoinVerify(dm.CustomizeTable, dm.CustomizeFieldLst, item);
  408. var wyzj = DbContext.ExecuteScalar<string>(_sqlJoinVerify, null, tran);
  409. if (!string.IsNullOrEmpty(wyzj))
  410. {
  411. //更新
  412. string _sqlJoinuUpdate = JoinuUpdate(dm.CustomizeTable, dm.CustomizeFieldLst, item, wyzj);
  413. DbContext.Execute(_sqlJoinuUpdate, null, tran);
  414. updateCount++;
  415. }
  416. else
  417. {
  418. //插入
  419. string _sqlJoinInsert = JoinInsert(dm.CustomizeTable, dm.CustomizeFieldLst, item);
  420. DbContext.Execute(_sqlJoinInsert, null, tran);
  421. addCount++;
  422. }
  423. row++;
  424. }
  425. tran.Commit();
  426. }
  427. catch (Exception ex)
  428. {
  429. tran.Rollback();
  430. retObj.Code = 2;
  431. retObj.Message = $"第{row}行数据写入数据库时异常,数据已回滚,异常原因:" + ex.Message;
  432. }
  433. retObj.Data = new { addCount = addCount, updateCount = updateCount };
  434. return retObj;
  435. }
  436. /// <summary>
  437. /// 拼接查询验证SQL
  438. /// </summary>
  439. private string JoinVerify(CustomizeTableAttribute customizeTable, List<CustomizeFieldAttribute> fieldAttributes, Hashtable hashtable)
  440. {
  441. string _sql = string.Empty;
  442. _sql = $" SELECT * FROM {customizeTable.DatabaseTableName} WHERE 1 = 1 ";
  443. foreach (var item in fieldAttributes)
  444. {
  445. if (item.IsExcelVerify)
  446. _sql += $" AND {item.DatabaseColumnName} = '{hashtable[item.DatabaseColumnName].ToString()}' ";
  447. }
  448. return _sql;
  449. }
  450. /// <summary>
  451. /// 拼接插入SQL
  452. /// </summary>
  453. private string JoinInsert(CustomizeTableAttribute customizeTable, List<CustomizeFieldAttribute> fieldAttributes, Hashtable hashtable)
  454. {
  455. string _sql = string.Empty;
  456. _sql = $" INSERT INTO {customizeTable.DatabaseTableName} (";
  457. foreach (var item in fieldAttributes)
  458. {
  459. _sql += $"{item.DatabaseColumnName},";
  460. }
  461. _sql = _sql.Substring(0, _sql.Length - 1);
  462. _sql += ") VALUES (";
  463. foreach (var item in fieldAttributes)
  464. {
  465. _sql += $"'{hashtable[item.DatabaseColumnName]}',";
  466. }
  467. _sql = _sql.Substring(0, _sql.Length - 1);
  468. return _sql += ")";
  469. }
  470. /// <summary>
  471. /// 拼接更新SQL
  472. /// </summary>
  473. private string JoinuUpdate(CustomizeTableAttribute customizeTable, List<CustomizeFieldAttribute> fieldAttributes, Hashtable hashtable, string wyzj)
  474. {
  475. string _sql = string.Empty;
  476. _sql = $" UPDATE {customizeTable.DatabaseTableName} SET ";
  477. string _tempSql = string.Empty;
  478. foreach (var item in fieldAttributes)
  479. {
  480. if (!item.IsPrimaryKey && !item.IsExcelVerify)
  481. _sql += $"{item.DatabaseColumnName} = '{hashtable[item.DatabaseColumnName]}',";
  482. if (item.IsPrimaryKey)
  483. _tempSql += $" WHERE {item.DatabaseColumnName} = '{wyzj}'";
  484. }
  485. return _sql.Substring(0, _sql.Length - 1) + _tempSql;
  486. }
  487. }
  488. public class TableNameModel
  489. {
  490. public string TABLENAME { get; set; }
  491. public string COUNT { get; set; }
  492. }
  493. }