SQLHelperParametric.cs 33 KB

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