SQLHelper.cs 34 KB

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