PageRenderTime 49ms CodeModel.GetById 18ms RepoModel.GetById 0ms app.codeStats 0ms

/Models/ItemsRepository.cs

https://bitbucket.org/roy_d_merkel/gameoflifeapionly
C# | 227 lines | 173 code | 33 blank | 21 comment | 11 complexity | 574b1dde22b0d8b0e7c1b05c8128ff62 MD5 | raw file
Possible License(s): GPL-3.0
  1. //
  2. // ExpenseRepository.cs
  3. //
  4. // Author:
  5. // Joe Merkel <merkeljoe9@gmail.com>
  6. //
  7. // Copyright (c) 2018 Joe Merkel
  8. //
  9. // This program is free software: you can redistribute it and/or modify
  10. // it under the terms of the GNU General Public License as published by
  11. // the Free Software Foundation, either version 3 of the License, or
  12. // (at your option) any later version.
  13. //
  14. // This program is distributed in the hope that it will be useful,
  15. // but WITHOUT ANY WARRANTY; without even the implied warranty of
  16. // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  17. // GNU General Public License for more details.
  18. //
  19. // You should have received a copy of the GNU General Public License
  20. // along with this program. If not, see <http://www.gnu.org/licenses/>.
  21. using System;
  22. using System.Collections.Generic;
  23. using GameOfLife.Entities;
  24. using T4R.Data;
  25. namespace GameOfLife.Models
  26. {
  27. public class ItemsRepository
  28. {
  29. public Items Get(DBConnection conn, int studentId)
  30. {
  31. Items result = null;
  32. int cashOnHand = -1;
  33. List<Item> items = null;
  34. List<Dictionary<string, object>> rs = null;
  35. string query;
  36. switch (conn.DriverClass)
  37. {
  38. case "mysql":
  39. query =
  40. @"SELECT i.*
  41. FROM item i
  42. WHERE i.user_id = ? AND NOT removed;";
  43. rs = conn.ExecuteQuery(query, new ParamType(typeof(int), studentId), new ParamType(typeof(int), studentId));
  44. break;
  45. case "postgresql":
  46. query =
  47. @"SELECT i.*
  48. FROM item i
  49. WHERE i.user_id = :1 AND NOT removed;";
  50. rs = conn.ExecuteQuery(query, new ParamType(typeof(int), studentId));
  51. break;
  52. case "sqlserver":
  53. query =
  54. @"SELECT i.*
  55. FROM item i
  56. WHERE i.user_id = @1 AND NOT removed;";
  57. rs = conn.ExecuteQuery(query, new ParamType(typeof(int), studentId));
  58. break;
  59. default:
  60. throw new NotSupportedException("driver class: " + conn.DriverClass + " is not supported.");
  61. }
  62. foreach (Dictionary<string, object> row in rs)
  63. {
  64. object id;
  65. object student_id;
  66. object amount;
  67. object name;
  68. object price;
  69. row.TryGetValue("item_id", out id);
  70. row.TryGetValue("user_id", out student_id);
  71. row.TryGetValue("amount", out amount);
  72. row.TryGetValue("name", out name);
  73. row.TryGetValue("price", out price);
  74. if(items == null)
  75. {
  76. items = new List<Item>();
  77. }
  78. if(id != null)
  79. {
  80. Item item = new Item((int)id, (int)student_id, (int)amount, (string)name, (int)price);
  81. items.Add(item);
  82. }
  83. }
  84. BankRepository bank = new BankRepository();
  85. cashOnHand = (bank.Get(conn, studentId) ?? new Bank(studentId, 0, 0, 0)).cash_on_hand;
  86. if (items != null)
  87. {
  88. result = new Items(studentId, cashOnHand, items.ToArray());
  89. }
  90. else
  91. {
  92. result = new Items(studentId, cashOnHand, new Item[0]);
  93. }
  94. return result;
  95. }
  96. public bool Add(DBConnection conn, int studentId, Item item)
  97. {
  98. bool result = false;
  99. string query;
  100. switch (conn.DriverClass)
  101. {
  102. case "mysql":
  103. query =
  104. @"INSERT INTO item (user_id, amount, name, price)
  105. VALUES (?, ?, ?, ?);";
  106. result = (conn.ExecuteUpdate(query, new ParamType(typeof(int), studentId), new ParamType(typeof(int), item.amount), new ParamType(typeof(string), item.name), new ParamType(typeof(int), item.price)) > 0);
  107. break;
  108. case "postgresql":
  109. query =
  110. @"INSERT INTO item (user_id, amount, name, price)
  111. VALUES (:1, :2, :3, :4);";
  112. result = (conn.ExecuteUpdate(query, new ParamType(typeof(int), studentId), new ParamType(typeof(int), item.amount), new ParamType(typeof(string), item.name), new ParamType(typeof(int), item.price)) > 0);
  113. break;
  114. case "sqlserver":
  115. query =
  116. @"INSERT INTO item (user_id, amount, name, price)
  117. VALUES (@1, @2, @3, @4);";
  118. result = (conn.ExecuteUpdate(query, new ParamType(typeof(int), studentId), new ParamType(typeof(int), item.amount), new ParamType(typeof(string), item.name), new ParamType(typeof(int), item.price)) > 0);
  119. break;
  120. default:
  121. throw new NotSupportedException("driver class: " + conn.DriverClass + " is not supported.");
  122. }
  123. return result;
  124. }
  125. public bool Edit(DBConnection conn, int studentId, int itemId, Item item)
  126. {
  127. bool result = false;
  128. string query;
  129. switch (conn.DriverClass)
  130. {
  131. case "mysql":
  132. query =
  133. @"UPDATE item SET amount=?, name=?, price=?
  134. WHERE user_id = ? AND item_id = ?;";
  135. result = (conn.ExecuteUpdate(query, new ParamType(typeof(int), item.amount), new ParamType(typeof(string), item.name), new ParamType(typeof(int), item.price), new ParamType(typeof(int), studentId), new ParamType(typeof(int), itemId)) > 0);
  136. break;
  137. case "postgresql":
  138. query =
  139. @"UPDATE item SET amount=:1, name=:2, price=:3
  140. WHERE user_id = :4 AND item_id = :5;";
  141. result = (conn.ExecuteUpdate(query, new ParamType(typeof(int), item.amount), new ParamType(typeof(string), item.name), new ParamType(typeof(int), item.price), new ParamType(typeof(int), studentId), new ParamType(typeof(int), itemId)) > 0);
  142. break;
  143. case "sqlserver":
  144. query =
  145. @"UPDATE item SET amount=@1, name=@2, price=@3
  146. WHERE user_id = @4 AND item_id = @5;";
  147. result = (conn.ExecuteUpdate(query, new ParamType(typeof(int), item.amount), new ParamType(typeof(string), item.name), new ParamType(typeof(int), item.price), new ParamType(typeof(int), studentId), new ParamType(typeof(int), itemId)) > 0);
  148. break;
  149. default:
  150. throw new NotSupportedException("driver class: " + conn.DriverClass + " is not supported.");
  151. }
  152. return result;
  153. }
  154. public bool Delete(DBConnection conn, int studentId, int itemId)
  155. {
  156. bool result = false;
  157. string query;
  158. // See if the loan already exists.
  159. if (itemId > 0)
  160. {
  161. switch (conn.DriverClass)
  162. {
  163. case "mysql":
  164. query =
  165. @"UPDATE item
  166. SET removed = 1
  167. WHERE user_id = ? AND item_id = ?;";
  168. result = (conn.ExecuteUpdate(query, new ParamType(typeof(int), studentId),
  169. new ParamType(typeof(int), itemId))) > 0;
  170. break;
  171. case "postgresql":
  172. query =
  173. @"UPDATE item
  174. SET removed = 1
  175. WHERE user_id = :1 AND item_id = :2;";
  176. result = (conn.ExecuteUpdate(query, new ParamType(typeof(int), studentId),
  177. new ParamType(typeof(int), itemId))) > 0;
  178. break;
  179. case "sqlserver":
  180. query =
  181. @"UPDATE item
  182. SET removed = 1
  183. WHERE user_id = @1 AND item_id = @2;";
  184. result = (conn.ExecuteUpdate(query, new ParamType(typeof(int), studentId),
  185. new ParamType(typeof(int), itemId))) > 0;
  186. break;
  187. default:
  188. throw new NotSupportedException("driver class: " + conn.DriverClass + " is not supported.");
  189. }
  190. }
  191. return result;
  192. }
  193. }
  194. }