DatabaseUpdateScripts.cs 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347
  1. using Comal.Classes;
  2. using InABox.Core;
  3. using InABox.Database;
  4. using System;
  5. using System.Collections.Generic;
  6. using System.Linq;
  7. using System.Linq.Expressions;
  8. using System.Text;
  9. using System.Threading.Tasks;
  10. using Syncfusion.Windows.Tools.Controls;
  11. namespace PRS.Shared
  12. {
  13. public static class DatabaseUpdateScripts
  14. {
  15. public static void RegisterScripts()
  16. {
  17. DataUpdater.RegisterUpdateScript("6.31", Update_6_31);
  18. DataUpdater.RegisterUpdateScript("6.37", Update_6_37);
  19. DataUpdater.RegisterUpdateScript("6.38", Update_6_38);
  20. DataUpdater.RegisterUpdateScript("6.39", Update_6_39);
  21. DataUpdater.RegisterUpdateScript("6.43", Update_6_43);
  22. DataUpdater.RegisterUpdateScript("7.00", Update_7_00);
  23. }
  24. private static Dictionary<string, Tuple<string, string>> _6_31_module_map = new()
  25. {
  26. { "Assignments", new("Assignments", "Assignments") },
  27. { "Daily Report", new("Daily Report", "Assignments") },
  28. { "Delivered On Site", new("Delivered On Site", "Delivery Items") },
  29. { "Deliveries", new("Deliveries", "Deliveries") },
  30. { "Digital Forms", new("Digital Forms", "DigitalForm") },
  31. { "Employee List", new("Employees", "Employee") },
  32. { "Equipment List", new("Equipment", "Equipment") },
  33. { "Factory Floor", new("Factory", "Manufacturing Packets") },
  34. { "Incoming Consignments", new("Consignments", "Consignment") },
  35. { "Manufacturing Status", new("Manufacturing Packets", "Manufacturing Packets") },
  36. { "Product List", new("Products", "Products") },
  37. { "Projects", new("Job Details", "Job Details") },
  38. { "Purchase Orders", new("Purchase Orders", "PurchaseOrder") },
  39. { "Quotes", new("Quotes", "Quotes") },
  40. { "Rack List", new("Shipping", "Shipments") },
  41. { "Site Requisitions", new("Requisitions", "Requisition") },
  42. { "Staff TimeSheets", new("Timesheets", "TimeSheet") },
  43. { "Stock Locations", new("Stock Locations", "StockLocation") },
  44. { "Stock Movements", new("Stock Movements", "StockMovement") },
  45. { "Task List", new("Tasks By Status", "Kanban") },
  46. };
  47. private static bool Update_6_31()
  48. {
  49. var modules = DbFactory.Provider.Query(new Filter<CustomModule>().All())
  50. .Rows.Select(x => x.ToObject<CustomModule>()).ToList();
  51. foreach(var module in modules)
  52. {
  53. if (!string.IsNullOrWhiteSpace(module.Section))
  54. {
  55. if (_6_31_module_map.TryGetValue(module.Section, out var map))
  56. {
  57. module.Section = map.Item1;
  58. module.DataModel = map.Item2;
  59. module.AllRecords = true;
  60. }
  61. else
  62. {
  63. Logger.Send(LogType.Error, "", $"Custom Module '{module.Name}' has section name '{module.Section}' and will no longer be visible!");
  64. }
  65. }
  66. }
  67. DbFactory.Provider.Save(modules);
  68. return true;
  69. }
  70. private static bool Update_6_37()
  71. {
  72. Logger.Send(LogType.Information, "", "Recreating views");
  73. DbFactory.Provider.ForceRecreateViews();
  74. return true;
  75. }
  76. private static bool Update_6_38()
  77. {
  78. Logger.Send(LogType.Information, "", "Converting Job Requisition Dates to Due Dates");
  79. List<JobRequisition> updates = new List<JobRequisition>();
  80. var columns = new Columns<JobRequisition>(x => x.ID);
  81. columns.Add("Date");
  82. CoreTable requis = DbFactory.Provider.Query<JobRequisition>(null, columns);
  83. foreach (var row in requis.Rows)
  84. {
  85. var requi = row.ToObject<JobRequisition>();
  86. requi.Approved = row.Get<DateTime>("Date");
  87. updates.Add(requi);
  88. }
  89. DbFactory.Provider.Save(updates);
  90. return true;
  91. }
  92. private static bool Update_6_39()
  93. {
  94. void ConvertJobDocumentIssuedDates()
  95. {
  96. Logger.Send(LogType.Information, "", "Converting Job Document Issued Dates");
  97. List<JobDocumentSetMileStone> updates = new List<JobDocumentSetMileStone>();
  98. var columns = new Columns<JobDocumentSetMileStone>(x => x.ID).Add(x => x.Submitted).Add(x => x.Status);
  99. columns.Add("Issued");
  100. CoreTable milestones = DbFactory.Provider.Query<JobDocumentSetMileStone>(null, columns);
  101. foreach (var row in milestones.Rows)
  102. {
  103. var milestone = row.ToObject<JobDocumentSetMileStone>();
  104. if (milestone.Status == JobDocumentSetMileStoneStatus.Unknown)
  105. milestone.Status = JobDocumentSetMileStoneStatus.Submitted;
  106. milestone.Submitted = row.Get<DateTime>("Issued");
  107. updates.Add(milestone);
  108. }
  109. DbFactory.Provider.Save(updates);
  110. }
  111. void ConvertProductUnitsOfMeasure()
  112. {
  113. Logger.Send(LogType.Information, "", "Converting Product Units of Measure");
  114. List<ProductDimensionUnit> updates = new List<ProductDimensionUnit>();
  115. var columns = new Columns<ProductDimensionUnit>(x => x.ID).Add(x => x.Description);
  116. CoreTable units = DbFactory.Provider.Query<ProductDimensionUnit>(new Filter<ProductDimensionUnit>(x=>x.Code).IsEqualTo(""), columns);
  117. foreach (var row in units.Rows)
  118. {
  119. var unit = row.ToObject<ProductDimensionUnit>();
  120. unit.Code = unit.Description;
  121. updates.Add(unit);
  122. }
  123. DbFactory.Provider.Save(updates);
  124. }
  125. void ConvertQuoteUnitsOfMeasure()
  126. {
  127. Logger.Send(LogType.Information, "", "Converting Quote Units of Measure");
  128. List<QuoteTakeOffUnit> updates = new List<QuoteTakeOffUnit>();
  129. var columns = new Columns<QuoteTakeOffUnit>(x => x.ID).Add(x => x.Description);
  130. CoreTable units = DbFactory.Provider.Query<QuoteTakeOffUnit>(new Filter<QuoteTakeOffUnit>(x=>x.Code).IsEqualTo(""), columns);
  131. foreach (var row in units.Rows)
  132. {
  133. var unit = row.ToObject<QuoteTakeOffUnit>();
  134. unit.Code = unit.Description;
  135. updates.Add(unit);
  136. }
  137. DbFactory.Provider.Save(updates);
  138. }
  139. ConvertJobDocumentIssuedDates();
  140. ConvertProductUnitsOfMeasure();
  141. ConvertQuoteUnitsOfMeasure();
  142. return true;
  143. }
  144. private static bool Update_6_43()
  145. {
  146. void ConvertSupplierProductLinks()
  147. {
  148. Logger.Send(LogType.Information, "", "Converting Supplier/Product Links");
  149. List<SupplierProduct> updates = new List<SupplierProduct>();
  150. var columns = new Columns<SupplierProduct>(x => x.ID).Add(x=>x.Product.ID);
  151. columns.Add("ProductLink.ID");
  152. CoreTable products = DbFactory.Provider.Query<SupplierProduct>(null, columns);
  153. foreach (var row in products.Rows)
  154. {
  155. Guid id = row.Get<SupplierProduct,Guid>(x=>x.ID);
  156. Guid oldid = row.Get<Guid>("ProductLink.ID");
  157. Guid newid = row.Get<SupplierProduct,Guid>(x=>x.Product.ID);
  158. if ((oldid != Guid.Empty) && (newid == Guid.Empty))
  159. {
  160. var update = new SupplierProduct() { ID = id };
  161. update.CommitChanges();
  162. update.Product.ID = oldid;
  163. updates.Add(update);
  164. }
  165. }
  166. DbFactory.Provider.Save(updates);
  167. }
  168. ConvertSupplierProductLinks();
  169. return true;
  170. }
  171. private struct Map<T>
  172. {
  173. public String Old;
  174. public Expression<Func<T, object>> New;
  175. public Map(String oldcolumn, Expression<Func<T, object>> newcolumn)
  176. {
  177. Old = oldcolumn;
  178. New = newcolumn;
  179. }
  180. }
  181. private static bool Update_7_00()
  182. {
  183. static void Convert<T>(
  184. Filter<T> filter,
  185. params Map<T>[] maps
  186. ) where T : Entity, IPersistent, IRemotable, new()
  187. {
  188. Logger.Send(LogType.Information, "", $"Converting {typeof(T).EntityName().Split('.').Last()}...");
  189. List<T> updates = new List<T>();
  190. var columns = new Columns<T>(x => x.ID);
  191. foreach (var map in maps)
  192. {
  193. if (!columns.Items.Any(x=>String.Equals(x.Property,map.Old)))
  194. columns.Add(map.Old);
  195. if (!columns.Items.Any(x=>String.Equals(x.Property,CoreUtils.GetFullPropertyName<T, object>(map.New, "."))))
  196. columns.Add(map.New);
  197. }
  198. CoreTable table = DbFactory.Provider.Query<T>(filter,columns);
  199. int iCount = 0;
  200. foreach (var row in table.Rows)
  201. {
  202. var update = row.ToObject<T>();
  203. foreach (var map in maps)
  204. CoreUtils.SetPropertyValue(update, CoreUtils.GetFullPropertyName<T, object>(map.New, "."), CoreUtils.GetPropertyValue(update, map.Old));
  205. if (update.IsChanged())
  206. updates.Add(update);
  207. if (updates.Count == 100)
  208. {
  209. iCount += updates.Count;
  210. Logger.Send(LogType.Information, "", $"Converting {typeof(T).EntityName().Split('.').Last()} Times ({iCount}/{table.Rows.Count}");
  211. DbFactory.Provider.Save(updates);
  212. updates.Clear();
  213. }
  214. }
  215. if (updates.Count > 0)
  216. {
  217. iCount += updates.Count;
  218. Logger.Send(LogType.Information, "", $"Converting {typeof(T).EntityName().Split('.').Last()} Times ({iCount}/{table.Rows.Count})");
  219. DbFactory.Provider.Save(updates);
  220. updates.Clear();
  221. }
  222. }
  223. Convert<Assignment>(
  224. new Filter<Assignment>(x=>x.Booked.Start).IsEqualTo(DateTime.MinValue)
  225. .And(x=>x.Booked.Finish).IsEqualTo(DateTime.MinValue)
  226. .And(x=>x.Actual.Finish).IsEqualTo(DateTime.MinValue)
  227. .And(x=>x.Actual.Finish).IsEqualTo(DateTime.MinValue),
  228. new Map<Assignment>("Start",x => x.Booked.Start),
  229. new Map<Assignment>("Finish",x => x.Booked.Finish),
  230. new Map<Assignment>("Start",x => x.Actual.Start),
  231. new Map<Assignment>("Finish",x => x.Actual.Finish)
  232. );
  233. // ConvertTimes<TimeSheet>(
  234. // x => x.Actual.Duration,
  235. // new TimeExpressions<TimeSheet>(x => x.Actual.Start, x => x.Actual.Finish),
  236. // new TimeExpressions<TimeSheet>(x => x.Approved.Start, x => x.Approved.Finish)
  237. // );
  238. void Convert_StandardLeaves_and_LeaveRequests()
  239. {
  240. // Delete from TimeSheet where processed={} and leaverequestlink.id != empty
  241. var unprocessedtimesheets = DbFactory.Provider.Query<TimeSheet>(
  242. new Filter<TimeSheet>(x => x.Processed).IsEqualTo(DateTime.MinValue)
  243. .And(x => x.LeaveRequestLink.ID).IsNotEqualTo(Guid.Empty),
  244. new Columns<TimeSheet>(x=>x.ID)
  245. ).Rows.Select(x=>x.ToObject<TimeSheet>()).ToArray();
  246. int iTimes = 0;
  247. while (iTimes < unprocessedtimesheets.Length)
  248. {
  249. var deletions = unprocessedtimesheets.Skip(iTimes).Take(100).ToArray();
  250. DbFactory.Provider.Purge<TimeSheet>(deletions);
  251. iTimes += deletions.Length;
  252. }
  253. //DbFactory.Provider.Delete<TimeSheet>(unprocessedtimesheets,"");
  254. // Find all Leave Requests where public holiday != empty
  255. var standardleaverequests = DbFactory.Provider.Query<LeaveRequest>(
  256. new Filter<LeaveRequest>(x => x.PublicHoliday.ID).IsNotEqualTo(Guid.Empty),
  257. new Columns<LeaveRequest>(x=>x.ID)
  258. .Add(x=>x.PublicHoliday.ID)
  259. ).Rows.Select(x => x.ToObject<LeaveRequest>()).ToArray();
  260. foreach (var standardleaverequest in standardleaverequests)
  261. {
  262. // Find all timesheets for this leave request
  263. var standardleavetimesheets = DbFactory.Provider.Query<TimeSheet>(
  264. new Filter<TimeSheet>(x=>x.LeaveRequestLink.ID).IsEqualTo(standardleaverequest.ID),
  265. new Columns<TimeSheet>(x=>x.ID)
  266. .Add(x=>x.LeaveRequestLink.ID)
  267. ).Rows.Select(x=>x.ToObject<TimeSheet>()).ToArray();
  268. // Redirect timesheet from leaverequest to standardleave
  269. foreach (var standardleavetimesheet in standardleavetimesheets)
  270. {
  271. standardleavetimesheet.StandardLeaveLink.ID = standardleaverequest.PublicHoliday.ID;
  272. standardleavetimesheet.LeaveRequestLink.ID = Guid.Empty;
  273. }
  274. if (standardleavetimesheets.Any())
  275. DbFactory.Provider.Save(standardleavetimesheets);
  276. }
  277. // delete these leave requests
  278. int iRequests = 0;
  279. while (iRequests < standardleaverequests.Length)
  280. {
  281. var deletions = standardleaverequests.Skip(iRequests).Take(100).ToArray();
  282. DbFactory.Provider.Purge<LeaveRequest>(deletions);
  283. iRequests += deletions.Length;
  284. }
  285. // Delete from Assignment where leaverequestlink id != empty
  286. var leaveassignments = DbFactory.Provider.Query<Assignment>(
  287. new Filter<Assignment>(x => x.LeaveRequestLink.ID).IsNotEqualTo(Guid.Empty),
  288. new Columns<Assignment>(x=>x.ID)
  289. ).Rows.Select(x=>x.ToObject<Assignment>()).ToArray();
  290. int iAssignments = 0;
  291. while (iAssignments < leaveassignments.Length)
  292. {
  293. var deletions = leaveassignments.Skip(iAssignments).Take(100).ToArray();
  294. DbFactory.Provider.Purge<Assignment>(deletions);
  295. iAssignments += deletions.Length;
  296. }
  297. }
  298. Convert_StandardLeaves_and_LeaveRequests();
  299. return true;
  300. }
  301. }
  302. }