SourceHelper.cs 8.3 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195
  1. using System;
  2. using System.Data;
  3. using System.Linq;
  4. using System.Threading;
  5. using Library;
  6. using LitJson;
  7. using MySystem.Models.Push;
  8. namespace MySystem
  9. {
  10. public class SourceHelper
  11. {
  12. public readonly static SourceHelper Instance = new SourceHelper();
  13. private SourceHelper()
  14. {
  15. }
  16. public void Start()//启动
  17. {
  18. Thread thread = new Thread(threadStart);
  19. thread.IsBackground = true;
  20. thread.Start();
  21. }
  22. private void threadStart()
  23. {
  24. while (true)
  25. {
  26. DoSomeThing();
  27. Thread.Sleep(1000);
  28. }
  29. }
  30. //要执行的方法
  31. public void DoSomeThing()
  32. {
  33. WebCMSEntities db = new WebCMSEntities();
  34. var sources = db.SourceData.Where(m => m.Status == 1).ToList();
  35. foreach(var source in sources)
  36. {
  37. string Title = source.Title; //说明
  38. string TableName = source.TableName; //数据表
  39. string FieldList = source.FieldList; //原始数据字段
  40. string FlagField = source.TargetFieldList; //原始标记字段
  41. string QueryField = "";
  42. JsonData fieldJson = JsonMapper.ToObject(FieldList);
  43. for (int i = 0; i < fieldJson.Count; i++)
  44. {
  45. JsonData item = fieldJson[i];
  46. if(!string.IsNullOrEmpty(item["source_name"].ToString())) QueryField += item["source_name"].ToString() + ",";
  47. }
  48. //构造抓取数据
  49. int StartId = source.QueryId;
  50. string sql = "select " + QueryField.TrimEnd(',') + " from " + TableName + " where 1=1";
  51. var condiList = db.SourceCondition.Where(m => m.SourceId == source.Id).ToList();
  52. foreach(var condi in condiList)
  53. {
  54. int QueryCondition = condi.QueryCondition;
  55. if(QueryCondition == 1)
  56. {
  57. sql += " and " + condi.QueryField + "='" + condi.QueryVal + "'";
  58. }
  59. else if(QueryCondition == 2)
  60. {
  61. sql += " and " + condi.QueryField + " like '%" + condi.QueryVal + "%'";
  62. }
  63. else if(QueryCondition == 3)
  64. {
  65. sql += " and " + condi.QueryField + ">" + condi.QueryVal + "";
  66. }
  67. else if(QueryCondition == 4)
  68. {
  69. sql += " and " + condi.QueryField + "<" + condi.QueryVal + "";
  70. }
  71. else if(QueryCondition == 5)
  72. {
  73. string[] QueryValList = condi.QueryVal.Split('|');
  74. sql += " and " + condi.QueryField + ">=" + QueryValList[0] + " and " + condi.QueryField + "<=" + QueryValList[1] + "";
  75. }
  76. else if(QueryCondition == 6)
  77. {
  78. string[] QueryValList = condi.QueryVal.Split('|');
  79. sql += " and " + condi.QueryField + ">='" + QueryValList[0] + "' and " + condi.QueryField + "<='" + QueryValList[1] + "'";
  80. }
  81. else if(QueryCondition == 7)
  82. {
  83. sql += " and " + condi.QueryField + " in (" + condi.QueryVal + ")";
  84. }
  85. else if(QueryCondition == 8)
  86. {
  87. sql += " and " + condi.QueryField + " in ('" + condi.QueryVal.Replace(",", "','") + "')";
  88. }
  89. else if(QueryCondition == 9)
  90. {
  91. string[] QueryValList = condi.QueryVal.Split(',');
  92. sql += " and (";
  93. int index = 0;
  94. foreach(string QueryVal in QueryValList)
  95. {
  96. index += 1;
  97. sql += condi.QueryField + "=" + QueryVal;
  98. if(index < QueryValList.Length)
  99. {
  100. sql += " or ";
  101. }
  102. }
  103. sql += ")";
  104. }
  105. else
  106. {
  107. sql += " and " + condi.QueryField + "='" + condi.QueryVal + "'";
  108. }
  109. }
  110. //创建原始数据表
  111. var sourceEdit = db.SourceData.FirstOrDefault(m => m.Id == source.Id && m.Version == 0);
  112. if(sourceEdit != null)
  113. {
  114. CreateTable(Title, TableName, FieldList);
  115. sourceEdit.Version = 1;
  116. db.SaveChanges();
  117. }
  118. //插入数据
  119. string TargetQueryField = "";
  120. JsonData TargetFieldJson = JsonMapper.ToObject(FieldList);
  121. for (int i = 0; i < TargetFieldJson.Count; i++)
  122. {
  123. JsonData item = TargetFieldJson[i];
  124. if(!string.IsNullOrEmpty(item["source_name"].ToString()))
  125. {
  126. TargetQueryField += item["name"].ToString() + ",";
  127. }
  128. }
  129. string insertSql = "";
  130. sql = sql.Replace("${QueryId}$", StartId.ToString());
  131. sql += " order by Id limit 10";
  132. DataTable dt = CustomerSqlConn.dtable(sql, AppConfig.Base.SourceSqlConnStr);
  133. foreach(DataRow dr in dt.Rows)
  134. {
  135. string values = "";
  136. for (int i = 0; i < fieldJson.Count; i++)
  137. {
  138. JsonData item = fieldJson[i];
  139. if(!string.IsNullOrEmpty(item["source_name"].ToString()))
  140. {
  141. values += "'" + dr[item["source_name"].ToString()].ToString() + "',";
  142. }
  143. }
  144. insertSql += "insert into u_" + TableName + " (" + TargetQueryField.TrimEnd(',') + ") values (" + values.TrimEnd(',') + ");";
  145. StartId = int.Parse(dr["Id"].ToString());
  146. CustomerSqlConn.dtable("update " + TableName + " set " + FlagField + "=1 where Id=" + StartId, AppConfig.Base.SourceSqlConnStr);
  147. }
  148. var edit = db.SourceData.FirstOrDefault(m => m.Id == source.Id);
  149. if(edit != null)
  150. {
  151. edit.QueryId = StartId;
  152. db.SaveChanges();
  153. }
  154. CustomerSqlConn.op(insertSql, AppConfig.Base.SqlConnStr);
  155. }
  156. db.Dispose();
  157. }
  158. //创建表
  159. public void CreateTable(string Title, string tableName, string fieldList)
  160. {
  161. string sql = "CREATE TABLE `u_" + tableName + "` (\n";
  162. JsonData fieldJson = JsonMapper.ToObject(fieldList);
  163. string prikey = "";
  164. for (int i = 0; i < fieldJson.Count; i++)
  165. {
  166. JsonData item = fieldJson[i];
  167. string nullCondition = "";
  168. string defaultValue = item["default"].ToString();
  169. if(!string.IsNullOrEmpty(defaultValue))
  170. {
  171. nullCondition = " NOT NULL DEFAULT " + defaultValue;
  172. }
  173. sql += "`" + item["name"].ToString() + "` varchar(" + item["len"].ToString() + ")" + nullCondition + " COMMENT '" + item["title"].ToString() + "',\n";
  174. if(item["pri"].ToString() == "1")
  175. {
  176. prikey = item["name"].ToString();
  177. }
  178. }
  179. if(!string.IsNullOrEmpty(prikey))
  180. {
  181. sql += "PRIMARY KEY (`" + prikey + "`)\n";
  182. }
  183. sql += ") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='" + Title + "';";
  184. CustomerSqlConn.op(sql, AppConfig.Base.SqlConnStr);
  185. }
  186. }
  187. }