clsSpreadsheet.cs 5.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Reflection;
  5. using System.Text;
  6. namespace TFABot
  7. {
  8. public class clsSpreadsheet
  9. {
  10. clsSpreadsheetReader sheet;
  11. public clsSpreadsheet(string sheeturl)
  12. {
  13. sheet = new clsSpreadsheetReader(sheeturl);
  14. }
  15. public String LoadSettings()
  16. {
  17. var sb = new StringBuilder();
  18. try
  19. {
  20. ReadSheetSettings("Settings",sb);
  21. ReadSheet<clsNotificationPolicy>("NotificationPolicy",Program.NotificationPolicyList,sb);
  22. ReadSheet<clsNetwork>("Networks",Program.NetworkList,sb);
  23. ReadSheet<clsUser>("Users",Program.UserList,sb);
  24. ReadSheet<clsNodeGroup>("NodeGroups",Program.NodeGroupList,sb);
  25. ReadSheet<clsNode>("Nodes",Program.NodesList,sb);
  26. ReadSheet<clsSSLCerts>("SSLCerts",Program.SSLCertsList,sb);
  27. }
  28. catch (Exception ex)
  29. {
  30. sb.AppendLine($"Error loading Settings {ex.Message}");
  31. }
  32. return sb.ToString();
  33. }
  34. public void ReadSheetSettings(String SheetName, StringBuilder sb)
  35. {
  36. try
  37. {
  38. sb.Append($"Loading {SheetName}..... ");
  39. var userSS = sheet.ReadSheet<clsSetting>(SheetName,sb);
  40. foreach (var setting in userSS)
  41. {
  42. if (Program.SettingsList.ContainsKey(setting.Key))
  43. {
  44. Program.SettingsList[setting.Key] = setting.Value;
  45. }
  46. else
  47. {
  48. Program.SettingsList.Add(setting.Key,setting.Value);
  49. }
  50. }
  51. sb.AppendLine($"{Program.SettingsList.Count} items, OK");
  52. }
  53. catch (Exception ex)
  54. {
  55. sb.AppendLine($"Error: {ex.Message}");
  56. Console.WriteLine(ex.Message);
  57. }
  58. }
  59. public void ReadSheet<T>(String SheetName, Dictionary<string,T> MainList, StringBuilder sb) where T : class
  60. {
  61. try
  62. {
  63. sb.Append($"Loading {SheetName}..... ");
  64. var userSS = sheet.ReadSheet<T>(SheetName,sb);
  65. Merge(MainList,userSS);
  66. sb.AppendLine($"{MainList.Count} items, OK");
  67. }
  68. catch (Exception ex)
  69. {
  70. sb.AppendLine($"Error: {ex.Message}");
  71. Console.WriteLine(ex.Message);
  72. }
  73. }
  74. //Merge Spreadsheet data
  75. public void Merge<T>(Dictionary<string,T> mainList, List<T> SList) where T : class
  76. {
  77. //Use reflection to get index field
  78. var type = typeof(T);
  79. var indexProperty = type.GetProperties().Where(x=>x.GetCustomAttribute<ASheetColumnHeader>()!=null && x.GetCustomAttribute<ASheetColumnHeader>().IsIndex).ElementAt(0);
  80. //Copy the main listm and sort
  81. var mainCopyList = mainList.Values.OrderBy(x=>indexProperty.GetValue(x,null)).ToList();
  82. //Sort Spreadsheet list on it's index property
  83. SList.Sort((x, y) => ((string)indexProperty.GetValue(x,null)).CompareTo(indexProperty.GetValue(y,null)));
  84. int u=0;
  85. int s=0;
  86. while (u < mainCopyList.Count || s < SList.Count)
  87. {
  88. //as both our lists are sorted, we can match them, to see if there are new/missing/matching entries.
  89. int match;
  90. if (u >= mainCopyList.Count) //Nothing to match, so we have a new entry
  91. {
  92. match = 1;
  93. }
  94. else if (s >= SList.Count) //Exceeded existing list, so we delete
  95. {
  96. match = -1;
  97. }
  98. else
  99. {
  100. match = ((string)indexProperty.GetValue(mainCopyList[u],null)).CompareTo(((string)indexProperty.GetValue(SList[s],null)));
  101. }
  102. switch (match)
  103. {
  104. case 0: //match
  105. ((ISpreadsheet<T>)mainCopyList[u]).Update(SList[s]);
  106. u++;
  107. s++;
  108. break;
  109. case 1: //new
  110. mainList.Add(((string)indexProperty.GetValue(SList[s],null)),SList[s]); //new
  111. s++;
  112. break;
  113. case -1: //delete
  114. mainList.Remove(((string)indexProperty.GetValue(mainCopyList[u],null)));
  115. u++;
  116. break;
  117. }
  118. }
  119. }
  120. }
  121. }