SourceHelper.cs 6.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146
  1. using System;
  2. using System.Linq;
  3. using System.Threading;
  4. using Library;
  5. using LitJson;
  6. using MySystem.Models.Push;
  7. namespace MySystem
  8. {
  9. public class SourceHelper
  10. {
  11. public readonly static SourceHelper Instance = new SourceHelper();
  12. private SourceHelper()
  13. {
  14. }
  15. public void Start()//启动
  16. {
  17. Thread thread = new Thread(threadStart);
  18. thread.IsBackground = true;
  19. thread.Start();
  20. }
  21. private void threadStart()
  22. {
  23. while (true)
  24. {
  25. DoSomeThing();
  26. Thread.Sleep(1000);
  27. }
  28. }
  29. //要执行的方法
  30. public void DoSomeThing()
  31. {
  32. WebCMSEntities db = new WebCMSEntities();
  33. var sources = db.SourceData.Where(m => m.Status == 1).ToList();
  34. foreach(var source in sources)
  35. {
  36. string Title = source.Title; //说明
  37. string TableName = source.TableName; //数据表
  38. string FieldList = source.FieldList; //原始数据字段
  39. string TargetFieldList = source.TargetFieldList; //目标数据字段
  40. string QueryField = "";
  41. JsonData fieldJson = JsonMapper.ToObject(FieldList);
  42. for (int i = 0; i < fieldJson.Count; i++)
  43. {
  44. JsonData item = fieldJson[i];
  45. QueryField += item["name"].ToString() + ",";
  46. }
  47. //构造抓取数据
  48. string sql = "select " + QueryField.TrimEnd(',') + " from " + TableName + " where 1=1";
  49. var condiList = db.SourceCondition.Where(m => m.SourceId == source.Id).ToList();
  50. foreach(var condi in condiList)
  51. {
  52. int QueryCondition = condi.QueryCondition;
  53. if(QueryCondition == 1)
  54. {
  55. sql += " and " + condi.QueryField + "='" + condi.QueryVal + "'";
  56. }
  57. else if(QueryCondition == 2)
  58. {
  59. sql += " and " + condi.QueryField + " like '%" + condi.QueryVal + "%'";
  60. }
  61. else if(QueryCondition == 3)
  62. {
  63. string[] QueryValList = condi.QueryVal.Split('|');
  64. sql += " and " + condi.QueryField + ">=" + QueryValList[0] + " and " + condi.QueryField + "<=" + QueryValList[1] + "";
  65. }
  66. else if(QueryCondition == 4)
  67. {
  68. string[] QueryValList = condi.QueryVal.Split('|');
  69. sql += " and " + condi.QueryField + ">='" + QueryValList[0] + "' and " + condi.QueryField + "<='" + QueryValList[1] + "'";
  70. }
  71. else if(QueryCondition == 5)
  72. {
  73. sql += " and " + condi.QueryField + " in (" + condi.QueryVal + ")";
  74. }
  75. else if(QueryCondition == 6)
  76. {
  77. sql += " and " + condi.QueryField + " in ('" + condi.QueryVal.Replace(",", "','") + "')";
  78. }
  79. else if(QueryCondition == 7)
  80. {
  81. string[] QueryValList = condi.QueryVal.Split(',');
  82. sql += " and (";
  83. int index = 0;
  84. foreach(string QueryVal in QueryValList)
  85. {
  86. index += 1;
  87. sql += condi.QueryField + "=" + QueryVal;
  88. if(index < QueryValList.Length)
  89. {
  90. sql += " or ";
  91. }
  92. }
  93. sql += ")";
  94. }
  95. else
  96. {
  97. sql += " and " + condi.QueryField + "=" + condi.QueryVal;
  98. }
  99. }
  100. //创建原始数据表
  101. var sourceEdit = db.SourceData.FirstOrDefault(m => m.Id == source.Id && m.Version == 0);
  102. if(sourceEdit != null)
  103. {
  104. CreateTable(Title, TableName, TargetFieldList);
  105. sourceEdit.Version = 1;
  106. db.SaveChanges();
  107. }
  108. //插入数据
  109. string TargetQueryField = "";
  110. JsonData TargetFieldJson = JsonMapper.ToObject(TargetFieldList);
  111. for (int i = 0; i < TargetFieldJson.Count; i++)
  112. {
  113. JsonData item = TargetFieldJson[i];
  114. TargetQueryField += item["name"].ToString() + ",";
  115. }
  116. string insertSql = "insert into " + TableName + " (" + TargetQueryField + ")" + sql;
  117. CustomerSqlConn.op(insertSql, AppConfig.Base.SqlConnStr);
  118. }
  119. db.Dispose();
  120. }
  121. //创建表
  122. public void CreateTable(string Title, string tableName, string fieldList)
  123. {
  124. string sql = "CREATE TABLE `" + tableName + "` (\n";
  125. sql += "`id` int(11) NOT NULL AUTO_INCREMENT,\n";
  126. sql += "`status` int(11) NOT NULL,\n";
  127. JsonData fieldJson = JsonMapper.ToObject(fieldList);
  128. for (int i = 0; i < fieldJson.Count; i++)
  129. {
  130. JsonData item = fieldJson[i];
  131. sql += "`" + item["name"].ToString() + "` varchar(" + item["len"].ToString() + ") DEFAULT NULL COMMENT '" + item["title"].ToString() + "',\n";
  132. }
  133. sql += "PRIMARY KEY (`id`)\n";
  134. sql += ") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='" + Title + "';";
  135. CustomerSqlConn.op(sql, AppConfig.Base.SqlConnStr);
  136. }
  137. }
  138. }