SQLHelperParametric.cs 34 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852
  1. using Newtonsoft.Json.Linq;
  2. using System;
  3. using System.Collections;
  4. using System.Collections.Generic;
  5. using System.Linq;
  6. using System.Text;
  7. using System.Text.Json;
  8. using System.Text.RegularExpressions;
  9. using TEAMModelOS.SDK.Context.Exception;
  10. using TEAMModelOS.SDK.Helper.Common.CollectionHelper;
  11. namespace TEAMModelOS.SDK.Module.AzureCosmosDBV3
  12. {
  13. public class SQLHelperParametric
  14. {
  15. static readonly string[] LogicOpers = new string[] { " and ", " or " };
  16. static readonly string[] CompareOpers = new string[] { " > ", " < ", " <= ", " >= ", " = ", " != ", " like ", " not like ", " in " };
  17. public static void ReplaceKeyWords(ref StringBuilder sql)
  18. {
  19. sql.Replace(".order.", "['order'].");
  20. sql.Replace(".order ", "['order'] ");
  21. sql.Replace(".Order.", "['Order'].");
  22. sql.Replace(".Order ", "['Order'] ");
  23. sql.Replace(".group.", "['group'].");
  24. sql.Replace(".group ", "['group'] ");
  25. sql.Replace(".Group.", "['Group'].");
  26. sql.Replace(".Group ", "['Group'] ");
  27. sql.Replace(".end.", "['end'].");
  28. sql.Replace(".end ", "['end'] ");
  29. sql.Replace(".End.", "['End'].");
  30. sql.Replace(".End ", "['End'] ");
  31. sql.Replace(".having.", "['having'].");
  32. sql.Replace(".having ", "['having'] ");
  33. sql.Replace(".Having.", "['Having'].");
  34. sql.Replace(".Having ", "['Having'] ");
  35. }
  36. public static StringBuilder GetSQLSelect(List<string> propertys)
  37. {
  38. StringBuilder sql;
  39. if (propertys.IsNotEmpty())
  40. {
  41. string tmpSQL = " select ";
  42. int i = 0;
  43. foreach (string item in propertys)
  44. {
  45. if (i == 0)
  46. {
  47. tmpSQL += " c." + item + " ";
  48. }
  49. else
  50. {
  51. tmpSQL += " ,c." + item + " ";
  52. }
  53. i++;
  54. }
  55. tmpSQL += "from c";
  56. sql = new StringBuilder(tmpSQL);
  57. }
  58. else
  59. {
  60. sql = new StringBuilder("select value(c) from c");
  61. }
  62. return sql;
  63. }
  64. public static void DictIsNotNULL(Dictionary<string, object> dict)
  65. {
  66. Dictionary<string, object> keyValuePairs = new Dictionary<string, object>();
  67. foreach (KeyValuePair<string, object> keyValuePair in dict)
  68. {
  69. if (keyValuePair.Value is JArray array)
  70. {
  71. if (array == null || array.Count == 0) keyValuePairs.Add(keyValuePair.Key, keyValuePair.Value);
  72. }
  73. else if (keyValuePair.Value is IList enumerable && !(keyValuePair.Value is String))
  74. {
  75. if (enumerable == null || enumerable.Count == 0) keyValuePairs.Add(keyValuePair.Key, keyValuePair.Value);
  76. }
  77. else if (keyValuePair.Value is JsonElement jsonElement && jsonElement.ValueKind is JsonValueKind.Array)
  78. {
  79. if (jsonElement.EnumerateArray().Count() == 0)
  80. {
  81. keyValuePairs.Add(keyValuePair.Key, keyValuePair.Value);
  82. }
  83. }
  84. else if (keyValuePair.Value is null)
  85. {
  86. keyValuePairs.Add(keyValuePair.Key, keyValuePair.Value);
  87. }
  88. }
  89. if (keyValuePairs.Count > 0)
  90. {
  91. foreach (KeyValuePair<string, object> keyValuePair in keyValuePairs)
  92. {
  93. dict.Remove(keyValuePair.Key);
  94. }
  95. }
  96. }
  97. public static CosmosDbQuery GetSQL(Dictionary<string, object> dict, StringBuilder sql)
  98. {
  99. if (dict != null)
  100. {
  101. DictIsNotNULL(dict);
  102. Dictionary<string, object> parmeters = new Dictionary<string, object>();
  103. int offsetNum = -1;
  104. int limitNum = 0;
  105. bool pageBool = false;
  106. GetPageNum(dict, ref offsetNum, ref limitNum, ref pageBool);
  107. string OrderByValue = "";
  108. // 处理OrderBy
  109. if (dict.TryGetValue("@ASC", out object ASC))
  110. {
  111. OrderByValue = Regex.Replace(ASC.ToString(), "\\s{1,}", "");
  112. dict.Remove("@ASC");
  113. }
  114. if (dict.TryGetValue("@DESC", out object DESC))
  115. {
  116. OrderByValue = Regex.Replace(DESC.ToString(), "\\s{1,}", "");
  117. dict.Remove("@DESC");
  118. };
  119. //处理顺序
  120. Stack<KeyValuePair<string, object>> stack = new Stack<KeyValuePair<string, object>>();
  121. Dictionary<string, object> forGetParmeter = new Dictionary<string, object>();
  122. foreach (string item in dict.Keys)
  123. {
  124. if (item.EndsWith(".|"))
  125. {
  126. stack.Push(new KeyValuePair<string, object>(item, dict[item]));
  127. }
  128. }
  129. foreach (string item in dict.Keys)
  130. {
  131. if (!item.EndsWith(".|"))
  132. {
  133. stack.Push(new KeyValuePair<string, object>(item, dict[item]));
  134. }
  135. }
  136. string Join = " join ";
  137. string instring = " in ";
  138. Dictionary<string, string> keyValues = new Dictionary<string, string>();
  139. StringBuilder WhereString = new StringBuilder();
  140. int heada = 0;
  141. string[] sqlHead = new string[] { "A", "B", "C", "D", "E", "F" };
  142. int kslength = 0;
  143. int logicOperNum = 0;
  144. bool keyListValueList = false;
  145. int keyListValueListNum = 0;
  146. int stackCount = stack.Count;
  147. for (int k = 0; k < stackCount; k++)
  148. {
  149. KeyValuePair<string, object> item = stack.Pop();
  150. forGetParmeter.Add(item.Key, item.Value);
  151. bool isLikeSQL = false;
  152. if (item.Key.StartsWith("$.") || item.Key.StartsWith("!$."))
  153. {
  154. isLikeSQL = true;
  155. }
  156. string key = item.Key;
  157. string[] keyHead = key.Split(".");
  158. int index = 0;
  159. int compareOper = 4;
  160. int logicOper = 0;
  161. if (key.EndsWith(".&"))
  162. {
  163. logicOper = (int)LogicOper.and;
  164. key = key.Replace(".&", "");
  165. }
  166. else if (key.EndsWith(".|"))
  167. {
  168. logicOper = (int)LogicOper.or;
  169. key = key.Replace(".|", "");
  170. }
  171. CompareOperSwitch(keyHead[0], ref key, ref compareOper);
  172. string[] keyBody = key.Split("[*]");
  173. if (keyBody.Length > 1)
  174. {
  175. key = key.Replace("[*]", "");
  176. key = key.Replace(".", "");
  177. key += k;
  178. kslength += keyBody.Length;
  179. if (kslength < (7 + heada))
  180. {
  181. StringBuilder sqlitem = new StringBuilder();
  182. for (int i = 0; i < keyBody.Length - 1; i++)
  183. {
  184. //Console.WriteLine(ks[i]);
  185. if (i == 0)
  186. {
  187. sqlitem.Append(Join);
  188. string a = sqlHead[heada] + index;
  189. sqlitem.Append(a + " ");
  190. //keyValues.Add(ks[i], a);
  191. keyValues[keyBody[i]] = a;
  192. sqlitem.Append(instring);
  193. sqlitem.Append("c.");
  194. sqlitem.Append(keyBody[i]);
  195. }
  196. else
  197. {
  198. sqlitem.Append(Join);
  199. string a = sqlHead[heada] + index;
  200. sqlitem.Append(a + " ");
  201. //keyValues.Add(ks[i], a);
  202. keyValues[keyBody[i]] = a;
  203. sqlitem.Append(instring);
  204. sqlitem.Append(keyValues[keyBody[i - 1]]);
  205. sqlitem.Append(keyBody[i]);
  206. }
  207. index += 1;
  208. }
  209. sql.Append(sqlitem);
  210. string s = "";
  211. if (isLikeSQL)
  212. {
  213. s = ValueIsLike(sqlHead[heada] + (keyBody.Length - 2) + keyBody[index] + "", key, item.Value, LogicOpers[logicOper], logicOperNum, compareOper, ref keyListValueList);
  214. }
  215. else
  216. {
  217. s = ValueNotLike(sqlHead[heada] + (keyBody.Length - 2) + keyBody[index] + "", key, item.Value, LogicOpers[logicOper], logicOperNum, compareOper, ref keyListValueList);
  218. }
  219. WhereString.Append(s);
  220. if (keyListValueList && keyListValueListNum == 0)
  221. {
  222. sql = sql.Replace("select ", "select distinct ");
  223. keyListValueList = false;
  224. keyListValueListNum++;
  225. }
  226. }
  227. else
  228. {
  229. throw new BizException("数组总共深度不能超过5层", ResponseCode.PARAMS_ERROR);
  230. }
  231. heada += 1;
  232. }
  233. else
  234. {
  235. string itemKey = key.Replace(".", "");
  236. itemKey += k;
  237. WhereString.Append(KeyNotElement(dict[item.Key], item.Key, itemKey, LogicOpers[logicOper], logicOperNum, compareOper));
  238. }
  239. logicOperNum += 1;
  240. }
  241. sql.Append(" where 1=1 ").Append(WhereString);
  242. if (ASC != null)
  243. {
  244. sql.Append(" Order By c." + OrderByValue );
  245. //sql.Append(" Order By c." + "@OrderByValue");
  246. }
  247. else if (DESC != null) {
  248. sql.Append(" Order By c." + OrderByValue + " DESC ");
  249. // sql.Append(" Order By c." + "@OrderByValue"+ " DESC " );
  250. }
  251. if (pageBool && offsetNum != -1 && limitNum != 0)
  252. {
  253. //sql.Append(" OFFSET " + offsetNum + " LIMIT " + limitNum);
  254. sql.Append(" OFFSET " + " @offsetNum " + " LIMIT " + " @limitNum ");
  255. }
  256. //替换关键字
  257. ReplaceKeyWords(ref sql);
  258. //参数化查询拼接 参数dict
  259. parmeters = GetParmeter(forGetParmeter, parmeters, offsetNum, limitNum);
  260. CosmosDbQuery cosmosDbQuery = new CosmosDbQuery
  261. {
  262. QueryText = sql.ToString(),
  263. Parameters = parmeters
  264. };
  265. return cosmosDbQuery;
  266. }
  267. return null;
  268. }
  269. private static void GetPageNum(Dictionary<string, object> dict, ref int offsetNum, ref int limitNum, ref bool pageBool)
  270. {
  271. dict.TryGetValue("@CURRPAGE", out object page);
  272. dict.Remove("@CURRPAGE");
  273. dict.TryGetValue("@PAGESIZE", out object limit);
  274. dict.Remove("@PAGESIZE");
  275. if (page != null && limit != null)
  276. {
  277. pageBool = true;
  278. limitNum = int.Parse(limit.ToString());
  279. if (limitNum < 0) {
  280. throw new BizException("PAGESIZE can't be less than 0 !");
  281. }
  282. offsetNum = (int.Parse(page.ToString()) - 1) * limitNum;
  283. if (offsetNum < 0) {
  284. throw new BizException("CURRPAGE can't be less than 1 !");
  285. }
  286. }
  287. }
  288. private static void CompareOperSwitch(string keyHead, ref string key, ref int compareOper)
  289. {
  290. switch (keyHead)
  291. {
  292. case ">":
  293. compareOper = (int)CompareOper.moreThan;
  294. key = key.Replace(">.", "");
  295. break;
  296. case "<":
  297. compareOper = (int)CompareOper.lessThan;
  298. key = key.Replace("<.", "");
  299. break;
  300. case "<=":
  301. compareOper = (int)CompareOper.notMoreThan;
  302. key = key.Replace("<=.", "");
  303. break;
  304. case ">=":
  305. compareOper = (int)CompareOper.notLessThan;
  306. key = key.Replace(">=.", "");
  307. break;
  308. case "=":
  309. compareOper = (int)CompareOper.equal;
  310. key = key.Replace("=.", "");
  311. break;
  312. case "!=":
  313. compareOper = (int)CompareOper.notEqual;
  314. key = key.Replace("!=.", "");
  315. break;
  316. case "$":
  317. compareOper = (int)CompareOper.like;
  318. key = key.Replace("$.", "");
  319. break;
  320. case "!$":
  321. compareOper = (int)CompareOper.notLike;
  322. key = key.Replace("!$.", "");
  323. break;
  324. default:
  325. compareOper = 4;
  326. break;
  327. }
  328. }
  329. private static string ValueNotLike(string key, string key1, object value, string logicOperParams, int logicOperNum, int compareOperNum, ref bool keyListValueList)
  330. {
  331. string logicOper = " and ";
  332. string compareOper = " = ";
  333. if (compareOperNum != 4) compareOper = CompareOpers[compareOperNum];
  334. if (logicOperNum != 0) logicOper = logicOperParams;
  335. StringBuilder sql = new StringBuilder(logicOper + key + " in (");
  336. if (value is JArray array)
  337. {
  338. keyListValueList = true;
  339. int aa = 0;
  340. foreach (JValue obja in array)
  341. {
  342. sql.Append(" @" + key1 + aa + " ,");
  343. aa++;
  344. }
  345. string sqls = sql.ToString().Substring(0, sql.Length - 1);
  346. sqls += " ) ";
  347. return sqls;
  348. }
  349. else if (value is IEnumerable enumerable && !(value is String))
  350. {
  351. keyListValueList = true;
  352. int aa = 0;
  353. foreach (object obja in enumerable)
  354. {
  355. sql.Append(" @" + key1 + aa + " ,");
  356. aa++;
  357. }
  358. string sqls = sql.ToString().Substring(0, sql.Length - 1);
  359. sqls += ") ";
  360. return sqls;
  361. }
  362. else if (value is JsonElement jsonElement)
  363. {
  364. if (jsonElement.ValueKind is JsonValueKind.Array)
  365. {
  366. keyListValueList = true;
  367. int aa = 0;
  368. foreach (JsonElement obja in jsonElement.EnumerateArray().ToArray())
  369. {
  370. sql.Append(" @" + key1 + aa + " ,");
  371. aa++;
  372. }
  373. }
  374. else
  375. {
  376. if (compareOperNum != 4) keyListValueList = true;
  377. return logicOper + key + compareOper + " @" + key1 + " ";
  378. }
  379. string sqls = sql.ToString().Substring(0, sql.Length - 1);
  380. sqls += " ) ";
  381. return sqls;
  382. }
  383. else
  384. {
  385. Type s = value.GetType();
  386. TypeCode typeCode = Type.GetTypeCode(s);
  387. if (compareOperNum != 4) keyListValueList = true;
  388. return logicOper + key + compareOper + " @" + key1 + " ";
  389. }
  390. }
  391. private static string ValueIsLike(string key, string key1, object value, string logicOperParams, int logicOperNum, int compareOperNum, ref bool keyListValueList)
  392. {
  393. string compareOperBool = " true ";
  394. compareOperBool = CompareBoolSwitch(compareOperNum);
  395. string logicOper = " and ";
  396. if (logicOperNum != 0) logicOper = logicOperParams;
  397. StringBuilder s = new StringBuilder(logicOper + " ( Contains( ");
  398. if (value is JArray array)
  399. {
  400. keyListValueList = true;
  401. int aa = 0;
  402. foreach (JValue obja in array)
  403. {
  404. if (aa != 0) s.Append("or Contains(");
  405. s.Append(key + "," + " @" + key1 + aa + " ) = " + compareOperBool + " ");
  406. if (obja.Value is string)
  407. {
  408. s.Append(key + "," + " @" + key1 + aa + " ) = " + compareOperBool + " ");
  409. }
  410. else
  411. {
  412. s.Append("ToString( " + key + " )," + " \'@" + key1 + aa + "\' ) = " + compareOperBool + " ");
  413. }
  414. aa++;
  415. }
  416. }
  417. else if (value is IEnumerable enumerable && !(value is String))
  418. {
  419. keyListValueList = true;
  420. int aa = 0;
  421. foreach (object obja in enumerable)
  422. {
  423. if (aa != 0) s.Append("or Contains(");
  424. if (obja is string)
  425. {
  426. s.Append(key + "," + " @" + key1 + aa + " ) = " + compareOperBool + " ");
  427. }
  428. else
  429. {
  430. s.Append("ToString( " + key + " )," + " \'@" + key1 + aa + "\' ) = " + compareOperBool + " ");
  431. }
  432. aa++;
  433. }
  434. }
  435. else if (value is JsonElement jsonElement && jsonElement.ValueKind is JsonValueKind.Array)
  436. {
  437. keyListValueList = true;
  438. int aa = 0;
  439. foreach (JsonElement obja in jsonElement.EnumerateArray().ToArray())
  440. {
  441. if (aa != 0) s.Append("or Contains(");
  442. if (obja.ValueKind is JsonValueKind.String)
  443. {
  444. s.Append(key + "," + " @" + key1 + aa + " ) = " + compareOperBool + " ");
  445. }
  446. else
  447. {
  448. s.Append("ToString( " + key + " )," + " \'@" + key1 + aa + "\' ) = " + compareOperBool + " ");
  449. }
  450. aa++;
  451. }
  452. }
  453. else
  454. {
  455. Type stype = value.GetType();
  456. TypeCode typeCode = Type.GetTypeCode(stype);
  457. if(compareOperNum == 7) keyListValueList = true;
  458. string sql = "";
  459. if (value is string)
  460. {
  461. sql = logicOper + "Contains( " + key + " , @" + key1 + " ) = " + compareOperBool + " ";
  462. }
  463. else if (value is JsonElement jsonElement1 && jsonElement1.ValueKind is JsonValueKind.String)
  464. {
  465. sql = logicOper + "Contains( " + key + " , @" + key1 + " ) = " + compareOperBool + " ";
  466. }
  467. else
  468. {
  469. sql = logicOper + "Contains( ToString(" + key + " ), \'@" + key1 + "\' ) = " + compareOperBool + " ";
  470. }
  471. return sql;
  472. }
  473. s.Append(" )");
  474. return s.ToString();
  475. }
  476. private static string CompareBoolSwitch(int compareOperNum)
  477. {
  478. return compareOperNum switch
  479. {
  480. 6 => " true ",
  481. 7 => " false ",
  482. _ => " true ",
  483. };
  484. }
  485. private static string KeyNotElement(object value, string key, string key1, string logicOperParams, int logicOperNum, int compareOperNum)
  486. {
  487. string compareOperBool = " true ";
  488. compareOperBool = CompareBoolSwitch(compareOperNum);
  489. string logicOper = " and ";
  490. int compareOper = 4;
  491. if (logicOperNum != 0) logicOper = logicOperParams;
  492. if (key.EndsWith(".&"))
  493. {
  494. key = key.Replace(".&", "");
  495. }
  496. else if (key.EndsWith(".|"))
  497. {
  498. key = key.Replace(".|", "");
  499. }
  500. string[] keyHead = key.Split(".");
  501. CompareOperSwitch(keyHead[0], ref key, ref compareOper);
  502. if (compareOper == 6 || compareOper == 7)
  503. {
  504. StringBuilder sql = new StringBuilder(logicOper + " ( Contains( ");
  505. if (value is JArray jarray)
  506. {
  507. int aa = 0;
  508. foreach (JValue obja in jarray)
  509. {
  510. if (aa != 0) sql.Append("or Contains(");
  511. if (obja.Value is string)
  512. {
  513. sql.Append(" c." + key + "," + " @" + key1 + aa + " ) = " + compareOperBool + " ");
  514. }
  515. else
  516. {
  517. sql.Append("ToString( c." + key + " )," + " \'@" + key1 + aa + "\' ) = " + compareOperBool + " ");
  518. }
  519. sql.Append(" c." + key + ", @" + key1 + aa + " )= " + compareOperBool + " ");
  520. aa++;
  521. }
  522. }
  523. else if (value is IEnumerable enumerable && !(value is String))
  524. {
  525. int aa = 0;
  526. foreach (object obja in enumerable)
  527. {
  528. if (aa != 0) sql.Append("or Contains(");
  529. if (obja is string)
  530. {
  531. sql.Append(" c." + key + "," + " @" + key1 + aa + " ) = " + compareOperBool + " ");
  532. }
  533. else
  534. {
  535. sql.Append("ToString( c." + key + " )," + " \'@" + key1 + aa + "\' ) = " + compareOperBool + " ");
  536. }
  537. aa++;
  538. }
  539. }
  540. else if (value is JsonElement jsonElement && jsonElement.ValueKind is JsonValueKind.Array)
  541. {
  542. int aa = 0;
  543. foreach (JsonElement obja in jsonElement.EnumerateArray().ToArray())
  544. {
  545. if (aa != 0) sql.Append("or Contains(");
  546. if (obja.ValueKind is JsonValueKind.String)
  547. {
  548. sql.Append(" c." + key + "," + " @" + key1 + aa + " ) = " + compareOperBool + " ");
  549. }
  550. else
  551. {
  552. sql.Append("ToString( c." + key + " )," + " \'@" + key1 + aa + "\' ) = " + compareOperBool + " ");
  553. }
  554. aa++;
  555. }
  556. }
  557. else
  558. {
  559. Type s = value.GetType();
  560. TypeCode typeCode = Type.GetTypeCode(s);
  561. string sql1 = "";
  562. if (value is string)
  563. {
  564. sql1 = logicOper + "Contains( c." + key + " , @" + key1 + " ) = " + compareOperBool + " ";
  565. }
  566. if (value is JsonElement jsonElement1 && jsonElement1.ValueKind is JsonValueKind.String)
  567. {
  568. sql1 = logicOper + "Contains( c." + key + " , @" + key1 + " ) = " + compareOperBool + " ";
  569. }
  570. else
  571. {
  572. sql1 = logicOper + "Contains( ToString( c." + key + " ), \'@" + key1 + "\' ) = " + compareOperBool + " ";
  573. }
  574. return sql1;
  575. }
  576. sql.Append(")");
  577. return sql.ToString();
  578. }
  579. else
  580. {
  581. StringBuilder sql = new StringBuilder(logicOper + " c." + key + " in (");
  582. if (value is JArray array)
  583. {
  584. int aa = 0;
  585. foreach (JValue obja in array)
  586. {
  587. sql.Append(" @" + key1 + aa + " ,");
  588. aa++;
  589. }
  590. string sqls = sql.ToString().Substring(0, sql.Length - 1);
  591. sqls += " ) ";
  592. return sqls;
  593. }
  594. else if (value is IEnumerable enumerable && !(value is String))
  595. {
  596. int aa = 0;
  597. foreach (object obja in enumerable)
  598. {
  599. sql.Append(" @" + key1 + aa + " ,");
  600. aa++;
  601. }
  602. string sqls = sql.ToString().Substring(0, sql.Length - 1);
  603. sqls += " ) ";
  604. return sqls;
  605. }
  606. else if (value is JsonElement jsonElement)
  607. {
  608. if (jsonElement.ValueKind is JsonValueKind.Array)
  609. {
  610. int aa = 0;
  611. foreach (JsonElement obja in jsonElement.EnumerateArray().ToArray())
  612. {
  613. sql.Append(" @" + key1 + aa + " ,");
  614. aa++;
  615. }
  616. }
  617. else
  618. {
  619. return logicOper + " c." + key + CompareOpers[compareOperNum] + " @" + key1;
  620. }
  621. string sqls = sql.ToString().Substring(0, sql.Length - 1);
  622. sqls += " ) ";
  623. return sqls;
  624. }
  625. else
  626. {
  627. Type s = value.GetType();
  628. TypeCode typeCode = Type.GetTypeCode(s);
  629. return typeCode switch
  630. {
  631. TypeCode.String => logicOper + " c." + key + CompareOpers[compareOperNum] + " @" + key1,// + "\'" + value.ToString() + "\'",
  632. TypeCode.Char => logicOper + " c." + key + CompareOpers[compareOperNum] + " @" + key1,// + "\'" + value.ToString() + "\'",
  633. TypeCode.Int32 => logicOper + " c." + key + CompareOpers[compareOperNum] + " @" + key1,// + int.Parse(value.ToString()),
  634. TypeCode.Double => logicOper + " c." + key + CompareOpers[compareOperNum] + " @" + key1,// + double.Parse(value.ToString()),
  635. TypeCode.Boolean => logicOper + " c." + key + CompareOpers[compareOperNum] + " @" + key1,// + bool.Parse(value.ToString()),
  636. TypeCode.DateTime => logicOper + " c." + key + CompareOpers[compareOperNum] + " @" + key1,// + (DateTime)value,
  637. TypeCode.Int64 => logicOper + " c." + key + CompareOpers[compareOperNum] + " @" + key1,// + long.Parse(value.ToString()),
  638. _ => null,
  639. };
  640. }
  641. }
  642. }
  643. public enum LogicOper : int
  644. {
  645. and = 0, or = 1
  646. }
  647. public enum CompareOper : int
  648. {
  649. moreThan = 0, lessThan = 1, notMoreThan = 2, notLessThan = 3, equal = 4, notEqual = 5, like = 6, notLike = 7, IN = 8
  650. }
  651. private static Dictionary<string, object> GetParmeter(Dictionary<string, object> dict, Dictionary<string, object> parmeters,int offset = 0,int limit = 0)
  652. {
  653. int i = 0;
  654. foreach (KeyValuePair<string, object> keyValue in dict)
  655. {
  656. string key = "";
  657. string[] keyHead = keyValue.Key.Split(".");
  658. switch (keyHead[0])
  659. {
  660. case ">":
  661. key = keyValue.Key.Replace(">.", "");
  662. break;
  663. case "<":
  664. key = keyValue.Key.Replace("<.", "");
  665. break;
  666. case "<=":
  667. key = keyValue.Key.Replace("<=.", "");
  668. break;
  669. case ">=":
  670. key = keyValue.Key.Replace(">=.", "");
  671. break;
  672. case "=":
  673. key = keyValue.Key.Replace("=.", "");
  674. break;
  675. case "!=":
  676. key = keyValue.Key.Replace("!=.", "");
  677. break;
  678. case "$":
  679. key = keyValue.Key.Replace("$.", "");
  680. break;
  681. case "!$":
  682. key = keyValue.Key.Replace("!$.", "");
  683. break;
  684. default:
  685. key = keyValue.Key;
  686. break;
  687. }
  688. if (key.EndsWith(".&"))
  689. {
  690. key = key.Replace(".&", "");
  691. }
  692. else if (key.EndsWith(".|"))
  693. {
  694. key = key.Replace(".|", "");
  695. }
  696. key = key.Replace("[*]", "");
  697. key = key.Replace(".", "");
  698. key += i;
  699. if (keyValue.Value is JArray array)
  700. {
  701. int aa = 0;
  702. foreach (JValue obja in array)
  703. {
  704. parmeters.Add("@" + key + aa, obja);
  705. aa++;
  706. }
  707. }
  708. else if (keyValue.Value is IEnumerable enumerable && !(keyValue.Value is String))
  709. {
  710. int aa = 0;
  711. foreach (object obja in enumerable)
  712. {
  713. parmeters.Add("@" + key + aa, obja);
  714. aa++;
  715. }
  716. }
  717. else if (keyValue.Value is JsonElement jsonElement)
  718. {
  719. if (jsonElement.ValueKind is JsonValueKind.Array)
  720. {
  721. int aa = 0;
  722. foreach (JsonElement obja in jsonElement.EnumerateArray().ToArray())
  723. {
  724. if (obja.ValueKind is JsonValueKind.String)
  725. {
  726. parmeters.Add("@" + key + aa, obja.ToString());
  727. }
  728. if (obja.ValueKind is JsonValueKind.Number)
  729. {
  730. parmeters.Add("@" + key + aa, double.Parse(obja.ToString()));
  731. }
  732. if (obja.ValueKind is JsonValueKind.True)
  733. {
  734. parmeters.Add("@" + key + aa, bool.Parse(obja.ToString()));
  735. }
  736. if (obja.ValueKind is JsonValueKind.False)
  737. {
  738. parmeters.Add("@" + key + aa, bool.Parse(obja.ToString()));
  739. }
  740. aa++;
  741. }
  742. }
  743. else
  744. {
  745. if (jsonElement.ValueKind is JsonValueKind.String)
  746. {
  747. parmeters.Add("@" + key, keyValue.Value.ToString());
  748. }
  749. else if (jsonElement.ValueKind is JsonValueKind.Number)
  750. {
  751. parmeters.Add("@" + key, double.Parse(keyValue.Value.ToString()));
  752. }
  753. else if (jsonElement.ValueKind is JsonValueKind.True)
  754. {
  755. parmeters.Add("@" + key, bool.Parse(keyValue.Value.ToString()));
  756. }
  757. else if (jsonElement.ValueKind is JsonValueKind.False)
  758. {
  759. parmeters.Add("@" + key, bool.Parse(keyValue.Value.ToString()));
  760. }
  761. else
  762. {
  763. parmeters.Add("@" + key, keyValue.Value.ToString());
  764. }
  765. }
  766. }
  767. else
  768. {
  769. parmeters.Add("@" + key, keyValue.Value);
  770. }
  771. i++;
  772. }
  773. if (offset != -1 && limit != 0)
  774. {
  775. parmeters.Add("@offsetNum", offset);
  776. parmeters.Add("@limitNum", limit);
  777. }
  778. return parmeters;
  779. }
  780. }
  781. }