SQLHelperParametric.cs 34 KB

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