API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Apr 20th, 2018
221
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 250.18 KB | None | 0 0
raw download clone embed print report
  1. using LumenWorks.Framework.IO.Csv;
  2. using System;
  3. using System.Collections.Generic;
  4. using System.Data;
  5. using System.Data.OleDb;
  6. using System.Data.SqlClient;
  7. using System.Globalization;
  8. using System.IO;
  9. using System.Linq;
  10. using System.Text;
  11. using System.Threading.Tasks;
  12.  
  13.  
  14.  
  15. namespace HonanImportScript {
  16.  
  17. class Program {
  18. //================================================================================
  19. public const string WINDOWS_AUTHENTICATION_USERNAME = "WINDOWS";
  20.  
  21. //--------------------------------------------------------------------------------
  22. public enum Team {
  23. None,
  24. All,
  25. RisksmartGCC,
  26. RisksmartProperty,
  27. PropertyClaims,
  28. GCCClaims
  29. }
  30.  
  31. public enum WinbeatLedger {
  32. None,
  33. Melbourne,
  34. RealEstate,
  35. Underwriting
  36. }
  37.  
  38. public enum Operation {
  39. None,
  40. Insert,
  41. Update
  42. }
  43.  
  44.  
  45. //================================================================================
  46. private static StreamWriter sLogWriter = null;
  47. private static StreamWriter sSummaryLogWriter = null;
  48.  
  49. private static string sInforConnectionString;
  50. private static string sWBRisksmartPropertyConnectionString;
  51. private static string sWBRealEstateConnectionString;
  52. private static string sWBMelbourneConnectionString;
  53.  
  54. private static Dictionary<string, string> sClaimNumberSubstitutions = new Dictionary<string, string>();
  55. private static Dictionary<string, string> sPolicyNumberSubstitutions = new Dictionary<string, string>();
  56. private static Dictionary<string, string> sRGCPolicyNumberSubstitutions = new Dictionary<string, string>();
  57. private static Dictionary<string, string> sRSPPolicyNumberSubstitutions = new Dictionary<string, string>();
  58. private static Dictionary<string, string> sPRCPolicyNumberSubstitutions = new Dictionary<string, string>();
  59. private static Dictionary<string, string> sGCCPolicyNumberSubstitutions = new Dictionary<string, string>();
  60. private static Dictionary<string, string> sAccountNameSubstitutions = new Dictionary<string, string>();
  61. private static Dictionary<string, string> sCustomerCodeSubstitutions = new Dictionary<string, string>();
  62. private static Dictionary<string, string> sUnderwriterCodeSubstitutions = new Dictionary<string, string>();
  63. private static Dictionary<string, string> sAssociateCodeSubstitutions = new Dictionary<string, string>();
  64. private static Dictionary<string, string> sUserNameSubstitutions = new Dictionary<string, string>();
  65.  
  66. private static Team sTeam = Team.None;
  67. private static string sTime = null;
  68. private static int sLine = -1;
  69.  
  70. private static Dictionary<string, Tuple<int, int>> sMissingAccounts = new Dictionary<string, Tuple<int, int>>();
  71. private static Dictionary<string, Tuple<int, int>> sMissingAssociates = new Dictionary<string, Tuple<int, int>>();
  72. private static Dictionary<string, Tuple<int, int>> sMissingUnderwriters = new Dictionary<string, Tuple<int, int>>();
  73. private static Dictionary<string, Tuple<int, int>> sMissingCustomers = new Dictionary<string, Tuple<int, int>>();
  74. private static Dictionary<string, Tuple<int, int>> sMissingPolicies = new Dictionary<string, Tuple<int, int>>();
  75. private static Dictionary<string, Tuple<int, int>> sMissingClaims = new Dictionary<string, Tuple<int, int>>();
  76. private static Dictionary<string, Tuple<int, int>> sMissingUsers = new Dictionary<string, Tuple<int, int>>();
  77.  
  78.  
  79. //================================================================================
  80. //--------------------------------------------------------------------------------
  81. [STAThread]
  82. static void Main(string[] args) {
  83. // Console
  84. Console.WindowWidth = 160;
  85. //Console.SetBufferSize(Console.BufferWidth, 15000);
  86.  
  87. // Open logs
  88. OpenLog("log.txt");
  89. OpenSummaryLog("summarylog.txt");
  90.  
  91. // Log session
  92. Log("\n################################################################################\n");
  93.  
  94. // Arguments
  95. if (args.Length == 0) {
  96. // Launcher
  97. Launcher launcher = new Launcher();
  98. launcher.ShowDialog();
  99. }
  100. else if (args.Length == 5) {
  101. // Team
  102. switch (args[0].ToLower()) {
  103. case "risksmart gcc": case "risksmart_gcc": case "risksmartgcc": sTeam = Team.RisksmartGCC; break;
  104. case "risksmart property": case "risksmart_property": case "risksmartproperty": sTeam = Team.RisksmartProperty; break;
  105. case "property claims": case "property_claims": case "propertyclaims": sTeam = Team.PropertyClaims; break;
  106. case "gcc claims": case "gcc_claims": case "gccclaims": sTeam = Team.GCCClaims; break;
  107. default: sTeam = Team.All; break;
  108. }
  109.  
  110. // TODO: Add winbeat connection details here
  111.  
  112. // Infor connection
  113. SetInforConnectionDetails(args[1], args[2], args[3], args[4]);
  114.  
  115. // Import
  116. Import(new ImportConfig(sTeam));
  117. }
  118. else {
  119. Log("Usage: [team], [source], [database], [username], [password]");
  120. return;
  121. }
  122.  
  123. // Close logs
  124. CloseLog();
  125. CloseSummaryLog();
  126. }
  127.  
  128.  
  129. // INFOR CONNECTION ================================================================================
  130. //--------------------------------------------------------------------------------
  131. public static void SetInforConnectionDetails(string source, string database, string username, string password) {
  132. sInforConnectionString = "Provider=SLXOLEDB.1;Data Source=" + source + ";Initial Catalog=" + database + ";" +
  133. "User ID=" + username + ";Password=" + password + ";Persist Security Info=True";
  134. }
  135.  
  136.  
  137. // WINBEAT CONNECTION ================================================================================
  138. //--------------------------------------------------------------------------------
  139. public static void SetWinBEATConnectionDetails(string instance, string username, string password, string risksmartPropertyDatabase, string realEstateDatabase, string melbourneDatabase) {
  140. // Risksmart property
  141. sWBRisksmartPropertyConnectionString = "Data Source=" + instance + ";Initial Catalog=" + risksmartPropertyDatabase + ";";
  142. if (!username.Equals(WINDOWS_AUTHENTICATION_USERNAME))
  143. sWBRisksmartPropertyConnectionString += "User ID=" + username + ";Password=" + password + ";Persist Security Info=True";
  144. else
  145. sWBRisksmartPropertyConnectionString += "Integrated Security=SSPI";
  146.  
  147. // Real estate
  148. sWBRealEstateConnectionString = "Data Source=" + instance + ";Initial Catalog=" + realEstateDatabase + ";";
  149. if (!username.Equals(WINDOWS_AUTHENTICATION_USERNAME))
  150. sWBRealEstateConnectionString += "User ID=" + username + ";Password=" + password + ";Persist Security Info=True";
  151. else
  152. sWBRealEstateConnectionString += "Integrated Security=SSPI";
  153.  
  154. // Melbourne
  155. sWBMelbourneConnectionString = "Data Source=" + instance + ";Initial Catalog=" + melbourneDatabase + ";";
  156. if (!username.Equals(WINDOWS_AUTHENTICATION_USERNAME))
  157. sWBMelbourneConnectionString += "User ID=" + username + ";Password=" + password + ";Persist Security Info=True";
  158. else
  159. sWBMelbourneConnectionString += "Integrated Security=SSPI";
  160. }
  161.  
  162.  
  163. // TEAMS ================================================================================
  164. //--------------------------------------------------------------------------------
  165. public static string TeamName() {
  166. switch (sTeam) {
  167. case Team.RisksmartGCC: return "Risksmart GCC";
  168. case Team.RisksmartProperty: return "Risksmart Property";
  169. case Team.PropertyClaims: return "Property Claims";
  170. case Team.GCCClaims: return "GCC Claims";
  171. default: return "";
  172. }
  173. }
  174.  
  175.  
  176. // DELETION ================================================================================
  177. //--------------------------------------------------------------------------------
  178. public static void DeleteImportedData(bool claimsOnly = false) {
  179. try {
  180. Log("Deleting imported data...");
  181. OleDbConnection connection = new OleDbConnection(sInforConnectionString);
  182. connection.Open();
  183.  
  184. OleDbCommand command;
  185.  
  186. if (!claimsOnly) {
  187. command = new OleDbCommand("delete from Address where Imported = 'T'", connection);
  188. command.ExecuteNonQuery();
  189.  
  190. command = new OleDbCommand("delete from Contact where Imported = 'T'", connection);
  191. command.ExecuteNonQuery();
  192.  
  193. command = new OleDbCommand("delete from Account where Imported = 'T'", connection);
  194. command.ExecuteNonQuery();
  195.  
  196. command = new OleDbCommand("delete from H_Policy where Imported = 'T'", connection);
  197. command.ExecuteNonQuery();
  198. }
  199.  
  200. command = new OleDbCommand("delete from H_Claims where Imported = 'T'", connection);
  201. command.ExecuteNonQuery();
  202.  
  203. command = new OleDbCommand("delete from H_Billings where Imported = 'T'", connection);
  204. command.ExecuteNonQuery();
  205.  
  206. command = new OleDbCommand("delete from H_Payments where Imported = 'T'", connection);
  207. command.ExecuteNonQuery();
  208.  
  209. command = new OleDbCommand("delete from H_KeyContacts where Imported = 'T'", connection);
  210. command.ExecuteNonQuery();
  211.  
  212. connection.Close();
  213. Log("Done.");
  214. }
  215. catch (Exception e) {
  216. LogError(e.Message);
  217. return;
  218. }
  219. }
  220.  
  221.  
  222. // IMPORT ================================================================================
  223. //--------------------------------------------------------------------------------
  224. public static void Import(ImportConfig config) {
  225. // Log
  226. Log("********************************************************************************");
  227.  
  228. // Team / time
  229. sTeam = Team.None;
  230. sTime = DateTime.Now.ToString();
  231.  
  232. // Accounts
  233. if (!string.IsNullOrEmpty(config.accountsContactsCSVPath)) {
  234. try { ImportCSVAccountContacts(Team.RisksmartGCC, config.accountsContactsCSVPath); }
  235. catch (Exception e) {
  236. LogError(e.Message);
  237. return;
  238. }
  239. }
  240.  
  241. // Policies
  242. if (!string.IsNullOrEmpty(config.policiesCSVPath)) {
  243. try { ImportCSVPolicies(Team.RisksmartGCC, config.policiesCSVPath); }
  244. catch (Exception e) {
  245. LogError(e.Message);
  246. return;
  247. }
  248. }
  249.  
  250. // Import
  251. sTeam = config.team;
  252. switch (config.team) {
  253. case Team.RisksmartGCC: RisksmartGCC_Import(config); break;
  254. case Team.RisksmartProperty: RisksmartProperty_Import(config); break;
  255. case Team.PropertyClaims: PropertyClaims_Import(config); break;
  256. case Team.GCCClaims: GCCClaims_Import(config); break;
  257. }
  258. sTeam = Team.None;
  259.  
  260. // Post import
  261. // Timeslips
  262. if (!string.IsNullOrEmpty(config.timeslipsCSVPath)) {
  263. try { ImportCSVTimeslips(Team.RisksmartGCC, config.timeslipsCSVPath); }
  264. catch (Exception e) {
  265. LogError(e.Message);
  266. return;
  267. }
  268. }
  269.  
  270. // Key contacts
  271. if (!string.IsNullOrEmpty(config.keyContactsCSVPath)) {
  272. try { ImportCSVKeyContacts(config.keyContactsCSVPath); }
  273. catch (Exception e) {
  274. LogError(e.Message);
  275. return;
  276. }
  277. }
  278.  
  279. // Payments
  280. if (!string.IsNullOrEmpty(config.paymentsCSVPath)) {
  281. try { ImportCSVPayments(config.paymentsCSVPath); }
  282. catch (Exception e) {
  283. LogError(e.Message);
  284. return;
  285. }
  286. }
  287.  
  288. // Reserves
  289. if (!string.IsNullOrEmpty(config.reservesCSVPath)) {
  290. try { ImportCSVReserves(config.reservesCSVPath); }
  291. catch (Exception e) {
  292. LogError(e.Message);
  293. return;
  294. }
  295. }
  296.  
  297. // Done
  298. Log("\nDone.");
  299. }
  300.  
  301.  
  302. // RISKSMART GCC ================================================================================
  303. //--------------------------------------------------------------------------------
  304. private static void RisksmartGCC_Import(ImportConfig config) {
  305. Log("IMPORTING: Risksmart GCC\n");
  306.  
  307. // Claim amendments
  308. if (!string.IsNullOrEmpty(config.accessClaimAmendmentsCSVPath)) {
  309. try { ImportCSVClaimAmendments(config.accessClaimAmendmentsCSVPath); }
  310. catch (Exception e) {
  311. LogError(e.Message);
  312. return;
  313. }
  314. }
  315.  
  316. // Claims
  317. if (config.importClaims) {
  318. try { ImportAccessClaims(); }
  319. catch (Exception e) {
  320. LogError(e.Message);
  321. return;
  322. }
  323. }
  324. }
  325.  
  326.  
  327. // RISKSMART PROPERTY ================================================================================
  328. //--------------------------------------------------------------------------------
  329. private static void RisksmartProperty_Import(ImportConfig config) {
  330. Log("IMPORTING: Risksmart Property\n");
  331.  
  332. // Claim amendments
  333. if (!string.IsNullOrEmpty(config.accessClaimAmendmentsCSVPath)) {
  334. try { ImportCSVClaimAmendments(config.accessClaimAmendmentsCSVPath); }
  335. catch (Exception e) {
  336. LogError(e.Message);
  337. return;
  338. }
  339. }
  340.  
  341. // Claims
  342. if (config.importClaims) {
  343. try {
  344. ImportAccessClaims();
  345. ImportWinBEATClaims(WinbeatLedger.Underwriting, !config.updateWinBEATClaims);
  346. }
  347. catch (Exception e) {
  348. LogError(e.Message);
  349. return;
  350. }
  351. }
  352. }
  353.  
  354.  
  355. // PROPERTY CLAIMS ================================================================================
  356. //--------------------------------------------------------------------------------
  357. private static void PropertyClaims_Import(ImportConfig config) {
  358. Log("IMPORTING: Property Claims\n");
  359.  
  360. // Claims
  361. if (config.importClaims) {
  362. try {
  363. ImportWinBEATClaims(WinbeatLedger.RealEstate, !config.updateWinBEATClaims);
  364. ImportWinBEATClaims(WinbeatLedger.Melbourne, !config.updateWinBEATClaims);
  365. }
  366. catch (Exception e) {
  367. LogError(e.Message);
  368. return;
  369. }
  370. }
  371. }
  372.  
  373.  
  374. // GCC CLAIMS ================================================================================
  375. //--------------------------------------------------------------------------------
  376. private static void GCCClaims_Import(ImportConfig config) {
  377. Log("IMPORTING: GCC Claims\n");
  378.  
  379. // Claims
  380. if (config.importClaims) {
  381. /*try {*/ ImportWinBEATClaims(WinbeatLedger.Melbourne, !config.updateWinBEATClaims); /*}
  382. catch (Exception e) {
  383. LogError(e.Message);
  384. return;
  385. }*/
  386. }
  387. }
  388.  
  389.  
  390. // CSV ================================================================================
  391. //--------------------------------------------------------------------------------
  392. private static void ImportCSVAccountContacts(Team team, string csvPath) {
  393. // Log
  394. Log("================================================================================");
  395. Log("Importing accounts and contacts (" + sTime + ")");
  396. Log("================================================================================");
  397.  
  398. // Connection
  399. OleDbConnection connection = new OleDbConnection(sInforConnectionString);
  400. connection.Open();
  401.  
  402. // CSV
  403. CSV csv = new CSV(csvPath);
  404.  
  405. // Delegates
  406. csv.missingField += new CSV.MissingFieldDelegate(MissingField);
  407. csv.parseError += new CSV.ParseErrorDelegate(ParseError);
  408. csv.truncated += new CSV.TruncatedDelegate(Truncated);
  409.  
  410. // Mandatory columns
  411. string missingColumn = csv.FindMissingColumn(new string[] { "account name", "client code", "underwriter code", "account type" });
  412. if (missingColumn != null) {
  413. LogError("Missing account/contacts column: '" + missingColumn + "'");
  414. return;
  415. }
  416.  
  417. // Line
  418. sLine = 1;
  419.  
  420. // Records
  421. while (csv.ReadNext()) {
  422. // Line
  423. ++sLine;
  424.  
  425. // Mandatory fields
  426. string missingField = csv.FindMissingField(new string[] { "account name", "account type" });
  427. if (missingField != null) {
  428. LogError("Missing account/contacts field: '" + missingField + "'");
  429. IssueTracker.AddError(IssueTracker.Issue.MISSING_CSV_FIELD_VALUE, sLine.ToString(), missingField);
  430. continue;
  431. }
  432.  
  433. if (!csv.HeaderIndices.ContainsKey("client code") && !csv.HeaderIndices.ContainsKey("underwriter code")) {
  434. LogError("Missing account/contacts field: 'client code' OR 'underwriter code'");
  435. IssueTracker.AddError(IssueTracker.Issue.MISSING_CSV_FIELD_VALUE, sLine.ToString(), "'client code' OR 'underwriter code'");
  436. continue;
  437. }
  438.  
  439. // Account
  440. string accountName = csv.FieldOrNull("account name", 128);
  441. string clientCode = csv.FieldOrNull("client code", 20);
  442. string underwriterCode = csv.FieldOrNull("underwriter code", 32);
  443. string accountType = ConvertAccountType(csv.FieldOrNull("account type", 64));
  444.  
  445. // Duplicate check
  446. OleDbCommand command;
  447. if (clientCode != null) {
  448. command = new OleDbCommand("select top 1 AccountID from Account where Client_Code=? and Account=? and Type=?", connection);
  449. command.Parameters.AddWithValue("@Client_Code", clientCode);
  450. }
  451. else {
  452. command = new OleDbCommand("select top 1 AccountID from Account where ExternalAccountNo=? and Account=? and Type=?", connection);
  453. command.Parameters.AddWithValue("@ExternalAccountNo", underwriterCode);
  454. }
  455. command.Parameters.AddWithValue("@Account", accountName);
  456. command.Parameters.AddWithValue("@Type", accountType);
  457.  
  458. object accountID = command.ExecuteScalar();
  459. command.Dispose();
  460.  
  461. // Add account if missing
  462. if ((accountID == null) || (accountID == DBNull.Value)) {
  463. // Account ID
  464. accountID = Infor.IDFor("Account", connection);
  465.  
  466. // Address
  467. object addressID = ImportCSVAddress(accountID, csv, connection);
  468.  
  469. // Insert
  470. command = new OleDbCommand("insert into Account (AccountID, Account, Client_Code, ExternalAccountNo, Type, AddressID, Mainphone, Email, Billing_Method," +
  471. " Billable_Rate, Service_Rage, Status, Imported, Userfield10, AccountManagerID, SeccodeID) " +
  472. "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", connection);
  473. command.Parameters.AddWithValue("@AccountID", accountID);
  474. command.Parameters.AddWithValue("@Account", accountName);
  475. command.Parameters.AddWithValue("@Client_Code", (object)clientCode ?? DBNull.Value);
  476. command.Parameters.AddWithValue("@ExternalAccountNo", (object)underwriterCode ?? DBNull.Value);
  477. command.Parameters.AddWithValue("@Type", accountType);
  478. command.Parameters.AddWithValue("@AddressID", addressID);
  479. command.Parameters.AddWithValue("@Mainphone", csv.FieldOrNull("main phone", 32));
  480. bool hasContact = (csv.FieldOrNull("first name", 32) != null) && !csv.FieldOrNull("first name", 32).ToLower().Equals("n/a");
  481. command.Parameters.AddWithValue("@Email", !hasContact ? (object)csv.FieldOrNull("email address", 128) : DBNull.Value);
  482. command.Parameters.AddWithValue("@Billing_Method", csv.FieldOrNull("billing method", 32));
  483. command.Parameters.AddWithValue("@Billable_Rate", csv.DBFieldFloatFlexible("fee per billing method"));
  484. command.Parameters.AddWithValue("@Service_Rage", csv.DBFieldFloatFlexible("fee for service"));
  485. command.Parameters.AddWithValue("@Status", "Active");
  486. command.Parameters.AddWithValue("@Imported", "T");
  487. command.Parameters.AddWithValue("@Userfield10", "Imported CSV");
  488. command.Parameters.AddWithValue("@AccountManagerID", Infor.GetField("UserID", "UserInfo", "Username = 'Ms Dalia Ismaiel'", connection));
  489. command.Parameters.AddWithValue("@SeccodeID", Infor.GetField("SeccodeID", "Seccode", "SeccodeDesc = 'Everyone' and SeccodeType = 'S'", connection));
  490. command.ExecuteNonQuery();
  491. command.Dispose();
  492. Log("Account (" + accountID + "): " + accountName + ", " + clientCode + ", " + accountType);
  493. }
  494.  
  495. // Contact
  496. //create address a second time - one for account, one for contact
  497. string firstName = csv.FieldOrNull("first name", 32);
  498. string lastName = csv.FieldOrNull("last name", 32) ?? "";
  499. if ((firstName != null) && !firstName.ToLower().Equals("n/a")) {
  500. // Duplicate check
  501. command = new OleDbCommand("select top 1 ContactID from Contact where AccountID=? and FirstName=? and LastName=?", connection);
  502. command.Parameters.AddWithValue("@AccountID", accountID);
  503. command.Parameters.AddWithValue("@FirstName", firstName);
  504. command.Parameters.AddWithValue("@LastName", lastName);
  505. object contactID = command.ExecuteScalar();
  506. command.Dispose();
  507.  
  508. // Add contact if missing
  509. if ((contactID == null) || (contactID == DBNull.Value)) {
  510. // Contact ID
  511. contactID = Infor.IDFor("Contact", connection);
  512.  
  513. // Address
  514. object addressID = ImportCSVAddress(contactID, csv, connection);
  515.  
  516. // Insert
  517. command = new OleDbCommand("insert into Contact (ContactID, AccountID, Account, AddressID, FirstName, LastName, Title, WorkPhone, Mobile, Email, Imported) " +
  518. "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", connection);
  519. command.Parameters.AddWithValue("@ContactID", contactID);
  520. command.Parameters.AddWithValue("@AccountID", accountID);
  521. string account = (string)Infor.GetField("Account", "Account", "AccountID = '" + accountID + "'", connection);
  522. command.Parameters.AddWithValue("@Account", account);
  523. command.Parameters.AddWithValue("@AddressID", addressID);
  524. command.Parameters.AddWithValue("@FirstName", firstName);
  525. command.Parameters.AddWithValue("@LastName", lastName);
  526. command.Parameters.AddWithValue("@Title", csv.FieldOrNull("title", 64));
  527. command.Parameters.AddWithValue("@WorkPhone", csv.FieldOrNull("main phone", 32));
  528. command.Parameters.AddWithValue("@Mobile", csv.FieldOrNull("mobile phone", 32));
  529. command.Parameters.AddWithValue("@Email", csv.FieldOrNull("email address", 128));
  530. command.Parameters.AddWithValue("@Imported", "T");
  531. command.ExecuteNonQuery();
  532. command.Dispose();
  533. Log("Contact (" + contactID + ", " + accountID + "): " + firstName + ", " + lastName + ", " + account);
  534. }
  535. }
  536. }
  537.  
  538. // Line
  539. sLine = -1;
  540.  
  541. // Close
  542. csv.Dispose();
  543. connection.Close();
  544.  
  545. // Summary
  546. IssueTracker.LogSummary("Importing accounts and contacts (" + sTime + ")");
  547.  
  548. // Missing
  549. LogMissingSummary("Importing accounts and contacts (" + sTime + ")");
  550.  
  551. // Whitespace
  552. Log("\n");
  553. }
  554.  
  555. //--------------------------------------------------------------------------------
  556. private static object ImportCSVAddress(object entityID, CSV csv, OleDbConnection connection) {
  557. // Fields
  558. string address1 = csv.FieldOrNull("address 1", 64);
  559. string address2 = csv.FieldOrNull("address 2", 64);
  560. string city = csv.FieldOrNull("suburb", 32);
  561. string state = csv.FieldOrNull("state", 32);
  562. string postalcode = csv.FieldOrNull("postalcode", 24);
  563.  
  564. // Add if available
  565. if ((address1 != null) || (address2 != null) || (city != null) || (state != null) || (postalcode != null)) {
  566. // ID
  567. object addressID = Infor.IDFor("Address", connection);
  568.  
  569. // Insert
  570. OleDbCommand command = new OleDbCommand("insert into Address (AddressID, EntityID, Address1, Address2, City, State, Postalcode, IsPrimary, IsMailing) " +
  571. "values (?, ?, ?, ?, ?, ?, ?, ?, ?)", connection);
  572. command.Parameters.AddWithValue("@AddressID", addressID);
  573. command.Parameters.AddWithValue("@EntityID", entityID);
  574. command.Parameters.AddWithValue("@Address1", (object)address1 ?? DBNull.Value);
  575. command.Parameters.AddWithValue("@Address2", (object)address2 ?? DBNull.Value);
  576. command.Parameters.AddWithValue("@City", (object)city ?? DBNull.Value);
  577. command.Parameters.AddWithValue("@State", (object)state ?? DBNull.Value);
  578. command.Parameters.AddWithValue("@Postalcode", (object)postalcode ?? DBNull.Value);
  579. command.Parameters.AddWithValue("@IsPrimary", "T");
  580. command.Parameters.AddWithValue("@IsMailing", "T");
  581. command.ExecuteNonQuery();
  582. Log("Address (" + addressID + ", " + entityID + "): " + (address1 ?? "") + ", " + (address2 ?? "") + ", " + (city ?? "") + ", " + (state ?? "") + ", " + (postalcode ?? ""));
  583.  
  584. return addressID;
  585. }
  586. else
  587. return DBNull.Value;
  588. }
  589.  
  590. //--------------------------------------------------------------------------------
  591. private static void ImportCSVPolicies(Team team, string csvPath) {
  592. // Log
  593. Log("================================================================================");
  594. Log("Importing policies... (" + sTime + ")");
  595. Log("================================================================================");
  596.  
  597. // Connection
  598. OleDbConnection connection = new OleDbConnection(sInforConnectionString);
  599. connection.Open();
  600.  
  601. // CSV
  602. CSV csv = new CSV(csvPath);
  603.  
  604. // Delegates
  605. csv.missingField += new CSV.MissingFieldDelegate(MissingField);
  606. csv.parseError += new CSV.ParseErrorDelegate(ParseError);
  607. csv.truncated += new CSV.TruncatedDelegate(Truncated);
  608.  
  609. // Mandatory columns
  610. string missingColumn = csv.FindMissingColumn(new string[] { "policy number", "policy status", "client code", "insured name", "policy class",
  611. "underwriter code", "inception date policy", "inception date invoiced",
  612. "expiry date policy", "expiry date invoice", "basic excess" });
  613. if (missingColumn != null) {
  614. LogError("Missing policy column: '" + missingColumn + "'");
  615. return;
  616. }
  617.  
  618. // Line
  619. sLine = 1;
  620.  
  621. // Records
  622. while (csv.ReadNext()) {
  623. // Line
  624. ++sLine;
  625.  
  626. // Mandatory fields
  627. string missingField = csv.FindMissingField(new string[] { "policy number", "policy status", "client code", "insured name", "policy class",
  628. "underwriter code", "inception date policy", "inception date invoiced",
  629. "expiry date policy", "expiry date invoice" });
  630. if (missingField != null) {
  631. LogError("Missing policy field: '" + missingField + "'");
  632. IssueTracker.AddError(IssueTracker.Issue.MISSING_CSV_FIELD_VALUE, sLine.ToString(), missingField);
  633. continue;
  634. }
  635.  
  636. // Policy
  637. string policyNumber = csv.FieldOrNull("policy number", 32);
  638. string policyStatus = csv.FieldOrNull("policy status", 32);
  639. string clientCode = csv.FieldOrNull("client code", 20);
  640. string insuredName = csv.FieldOrNull("insured name", 256);
  641. string policyClass = csv.FieldOrNull("policy class", 64);
  642. string underwriterCode = csv.FieldOrNull("underwriter code", 128);
  643. DateTime inceptionDatePolicy = (DateTime)csv.DBFieldDateTime("inception date policy");
  644. DateTime inceptionDateInvoice = (DateTime)csv.DBFieldDateTime("inception date invoiced");
  645. DateTime expiryDatePolicy = (DateTime)csv.DBFieldDateTime("expiry date policy");
  646. DateTime expiryDateInvoice = (DateTime)csv.DBFieldDateTime("expiry date invoice");
  647.  
  648. // Duplicate check
  649. /*OleDbCommand command = new OleDbCommand("select top 1 _P.H_PolicyID from H_Policy _P " +
  650. "left join Account _O on _P.Owner_AccountID = _O.AccountID left join Account _U on _P.Underwriter_AccountID = _U.AccountID " +
  651. "where Policy_Number = ? and Policy_Status = ? and _O.Client_Code = ? and InsuredName = ? and Policy_Class = ? and _U.Account = ? and " +
  652. "Inception_Date = ? and Inception_Date_Invoice = ? and Expiry_Date = ? and Expiry_Date_Invoice = ?", connection);*/
  653. OleDbCommand command = new OleDbCommand("select top 1 _P.H_PolicyID from H_Policy _P " +
  654. "where Policy_Number = ? and Policy_Status = ? and InsuredName = ? and Policy_Class = ? and " +
  655. "Inception_Date = ? and Inception_Date_Invoice = ? and Expiry_Date = ? and Expiry_Date_Invoice = ?", connection);
  656. command.Parameters.AddWithValue("@PolicyNumber", policyNumber);
  657. command.Parameters.AddWithValue("@PolicyStatus", policyStatus);
  658. //command.Parameters.AddWithValue("@ClientCode", clientCode);
  659. command.Parameters.AddWithValue("@InsuredName", insuredName);
  660. command.Parameters.AddWithValue("@PolicyClass", policyClass);
  661. //command.Parameters.AddWithValue("@UnderwriterName", underwriterName);
  662. command.Parameters.AddWithValue("@InceptionDatePolicy", inceptionDatePolicy);
  663. command.Parameters.AddWithValue("@InceptionDateInvoice", inceptionDateInvoice);
  664. command.Parameters.AddWithValue("@ExpiryDatePolicy", expiryDatePolicy);
  665. command.Parameters.AddWithValue("@ExpiryDateInvoice", expiryDateInvoice);
  666.  
  667. object policyID = command.ExecuteScalar();
  668. command.Dispose();
  669.  
  670. // Add policy if missing
  671. if ((policyID == null) || (policyID == DBNull.Value)) {
  672. // Policy ID
  673. policyID = Infor.IDFor("Policy", connection);
  674.  
  675. // Insert
  676. command = new OleDbCommand("insert into H_Policy (H_PolicyID, Policy_Number, Policy_Status, InsuredName, Policy_Class, Inception_Date, Inception_Date_Invoice, Expiry_Date," +
  677. " Expiry_Date_Invoice, Basic_Excess, Imported, Year_Built, SeccodeID, Owner_AccountID, Underwriter_AccountID) " +
  678. "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", connection);
  679. command.Parameters.AddWithValue("@PolicyID", policyID);
  680. command.Parameters.AddWithValue("@PolicyNumber", policyNumber);
  681. command.Parameters.AddWithValue("@PolicyStatus", policyStatus);
  682. command.Parameters.AddWithValue("@InsuredName", insuredName);
  683. command.Parameters.AddWithValue("@PolicyClass", policyClass);
  684. command.Parameters.AddWithValue("@InceptionDate", inceptionDatePolicy);
  685. command.Parameters.AddWithValue("@InceptionDateInvoice", inceptionDateInvoice);
  686. command.Parameters.AddWithValue("@ExpiryDate", expiryDatePolicy);
  687. command.Parameters.AddWithValue("@ExpiryDateInvoice", expiryDateInvoice);
  688. command.Parameters.AddWithValue("@BasicExcess", csv.DBFieldDecimalFlexible("basic excess"));
  689. command.Parameters.AddWithValue("@Imported", "T");
  690. command.Parameters.AddWithValue("@YearBuilt", 2000);
  691.  
  692. // Seccode ID
  693. object seccodeID = DBNull.Value;
  694. switch (team) {
  695. case Team.RisksmartGCC: seccodeID = Infor.GetField("SeccodeID", "Seccode", "SeccodeDesc = 'Risksmart GCC Policies' and SeccodeType = 'G'", connection); break;
  696. }
  697. command.Parameters.AddWithValue("@SeccodeID", seccodeID);
  698.  
  699. // Owner account
  700. object ownerAccountID = Infor.GetField("AccountID", "Account", "Type = 'Customer' and Client_Code = '" + clientCode + "'", connection) ?? DBNull.Value;
  701. if (ownerAccountID == DBNull.Value) {
  702. LogError("Owner account not found: '" + clientCode + "'");
  703. IssueTracker.AddError(IssueTracker.Issue.CUSTOMER_NOT_FOUND, sLine.ToString(), clientCode);
  704. AddMissingCustomer("ClientCode : " + clientCode);
  705. continue;
  706. }
  707. command.Parameters.AddWithValue("@OwnerAccountID", ownerAccountID);
  708.  
  709. // Underwriter account
  710. object underwriterAccountID = Infor.GetField("AccountID", "Account", "Type = 'Underwriter' and ExternalAccountNo = '" + underwriterCode + "'", connection) ?? DBNull.Value;
  711. if (underwriterAccountID == DBNull.Value) {
  712. LogError("Underwriter account not found: '" + underwriterCode + "'");
  713. IssueTracker.AddError(IssueTracker.Issue.UNDERWRITER_NOT_FOUND, sLine.ToString(), underwriterCode);
  714. AddMissingUnderwriter(underwriterCode);
  715. continue;
  716. }
  717. command.Parameters.AddWithValue("@UnderwriterAccountID", underwriterAccountID);
  718.  
  719. // Execute
  720. command.ExecuteNonQuery();
  721. Log("Policy (" + policyID + "): " + policyNumber + ", " + policyClass + ", " + clientCode + ", " + insuredName + ", " + underwriterCode);
  722. }
  723. //else
  724. // LogWarning("Policy already exists: " + policyID);
  725. }
  726.  
  727. // Line
  728. sLine = -1;
  729.  
  730. // Close
  731. csv.Dispose();
  732. connection.Close();
  733.  
  734. // Summary
  735. IssueTracker.LogSummary("Importing policies... (" + sTime + ")");
  736.  
  737. // Missing
  738. LogMissingSummary("Importing policies... (" + sTime + ")");
  739.  
  740. // Whitespace
  741. Log("\n");
  742. }
  743.  
  744. //--------------------------------------------------------------------------------
  745. private static void ImportCSVClaimAmendments(string csvPath) {
  746. // Log
  747. Log("================================================================================");
  748. Log("Importing claim amendments (" + sTime + ")");
  749. Log("================================================================================");
  750.  
  751. // Connection
  752. OleDbConnection connection = new OleDbConnection(sInforConnectionString);
  753. connection.Open();
  754.  
  755. // CSV
  756. CSV csv = new CSV(csvPath);
  757.  
  758. // Delegates
  759. csv.missingField += new CSV.MissingFieldDelegate(MissingField);
  760. csv.parseError += new CSV.ParseErrorDelegate(ParseError);
  761. csv.truncated += new CSV.TruncatedDelegate(Truncated);
  762.  
  763. // Mandatory clumns
  764. string missingColumn = csv.FindMissingColumn(new string[] { "claim no", "claim type", "policy number", "insured", "assigned user" });
  765. if (missingColumn != null) {
  766. LogError("Missing claim amendments column: '" + missingColumn + "'");
  767. return;
  768. }
  769.  
  770. // Line
  771. sLine = 1;
  772.  
  773. // Records
  774. while (csv.ReadNext()) {
  775. // Line
  776. ++sLine;
  777.  
  778. // Mandatory fields
  779. string missingField = csv.FindMissingField(new string[] { "claim no" });
  780. if (missingField != null) {
  781. LogError("Missing claim amendments field: '" + missingField + "'");
  782. IssueTracker.AddError(IssueTracker.Issue.MISSING_CSV_FIELD_VALUE, sLine.ToString(), missingField);
  783. continue;
  784. }
  785.  
  786. // Staged claim
  787. string claimNo = csv.FieldOrNull("claim no", 255);
  788. OleDbDataAdapter adapter = new OleDbDataAdapter("select top 1 * from S_Acc_Claim where Claim_No = '" + claimNo + "'", sInforConnectionString);
  789. DataSet dataSet = new DataSet();
  790. adapter.Fill(dataSet, "S_Acc_Claim");
  791. DataTable claimTable = dataSet.Tables["S_Acc_Claim"];
  792.  
  793. if (claimTable.Rows.Count == 0) {
  794. LogError("Claim not found: '" + claimNo + "'");
  795. IssueTracker.AddError(IssueTracker.Issue.CLAIM_NOT_FOUND, sLine.ToString(), claimNo);
  796. continue;
  797. }
  798. DataRow claimRow = claimTable.Rows[0];
  799.  
  800. // Policy number
  801. object policyNumber = claimRow["Policy_Number"];
  802. string csvPolicyNumber = csv.FieldOrNull("policy number");
  803.  
  804. if (csvPolicyNumber != null) {
  805. csvPolicyNumber = csvPolicyNumber.Trim();
  806. if ((policyNumber == DBNull.Value) || ((string)policyNumber).ToLower().Contains("xx") || ((string)policyNumber).ToLower().Contains("tba")) {
  807. policyNumber = csvPolicyNumber; // Update
  808. Log("Policy number updated (" + claimNo + "): " + policyNumber);
  809. }
  810. else if (!csvPolicyNumber.Equals(((string)policyNumber).Trim())) {
  811. LogError("Policy number differs (" + claimNo + "): " + csvPolicyNumber + ", " + policyNumber);
  812. IssueTracker.AddError(IssueTracker.Issue.POLICY_NUMBER_DIFFERS, sLine.ToString(), csvPolicyNumber + ", " + policyNumber);
  813. }
  814. }
  815.  
  816. // Claim type
  817. object claimType = ConvertDDField(claimRow, "DD_Claim_Type", "Claim_Type", new string[] { "Claim", "Notification" });
  818. string csvClaimType = csv.FieldOrNull("claim type");
  819.  
  820. if (csvClaimType != null) {
  821. csvClaimType = csvClaimType.First().ToString().ToUpper() + csvClaimType.Substring(1);
  822. if ((claimType == DBNull.Value) || (!new string[] { "claim", "notification" }.Contains(((string)claimType).ToLower()))) {
  823. claimType = csvClaimType; // Update
  824. Log("Claim type updated (" + claimNo + "): " + claimType);
  825. }
  826. else if (!csvClaimType.Equals(claimType)) {
  827. LogError("Claim type differs (" + claimNo + "): " + csvClaimType + ", " + claimType);
  828. IssueTracker.AddError(IssueTracker.Issue.CLAIM_TYPE_DIFFERS, sLine.ToString(), csvClaimType + ", " + claimType);
  829. }
  830. }
  831.  
  832. // Insured
  833. object insured = ConvertDDField(claimRow, "DD_Insured", "Insured");
  834. string csvInsured = csv.FieldOrNull("insured");
  835.  
  836. if (csvInsured != null) {
  837. if (insured == DBNull.Value) {
  838. insured = csvInsured; // Update
  839. Log("Insured updated (" + claimNo + "): " + insured);
  840. }
  841. else if (!csvInsured.Equals(insured)) {
  842. LogError("Insured differs (" + claimNo + "): " + csvInsured + ", " + insured);
  843. IssueTracker.AddError(IssueTracker.Issue.INSURED_DIFFERS, sLine.ToString(), csvInsured + ", " + insured);
  844. }
  845. }
  846.  
  847. // Assigned user
  848. object assignedUser = claimRow["Claim_Operator"];
  849. string csvAssignedUser = csv.FieldOrNull("assigned user");
  850.  
  851. if (csvAssignedUser != null) {
  852. assignedUser = csvAssignedUser;
  853. Log("Assigned user updated (" + claimNo + "): " + assignedUser);
  854. }
  855.  
  856. // Update
  857. OleDbCommand command = new OleDbCommand("update S_Acc_Claim set Policy_Number = ?, DD_Claim_Type = ?, DD_Insured = ?, Claim_Operator = ? where Claim_No = ?", connection);
  858. command.Parameters.AddWithValue("@PolicyNumber", policyNumber);
  859. command.Parameters.AddWithValue("@ClaimType", claimType);
  860. command.Parameters.AddWithValue("@Insured", insured);
  861. command.Parameters.AddWithValue("@AssignedUser", assignedUser);
  862. command.Parameters.AddWithValue("@ClaimNo", claimNo);
  863. command.ExecuteNonQuery();
  864. }
  865.  
  866. // Line
  867. sLine = -1;
  868.  
  869. // Close
  870. csv.Dispose();
  871. connection.Close();
  872.  
  873. // Summary
  874. IssueTracker.LogSummary("Importing claim amendments (" + sTime + ")");
  875.  
  876. // Missing
  877. LogMissingSummary("Importing claim amendments (" + sTime + ")");
  878.  
  879. // Whitespace
  880. Log("\n");
  881. }
  882.  
  883. //--------------------------------------------------------------------------------
  884. private static void ImportCSVTimeslips(Team team, string csvPath) {
  885. // Log
  886. Log("================================================================================");
  887. Log("Importing timeslips (" + sTime + ")");
  888. Log("================================================================================");
  889.  
  890. // Connection
  891. OleDbConnection connection = new OleDbConnection(sInforConnectionString);
  892. connection.Open();
  893.  
  894. // CSV
  895. CSV csv = new CSV(csvPath);
  896.  
  897. // Delegates
  898. csv.missingField += new CSV.MissingFieldDelegate(MissingField);
  899. csv.parseError += new CSV.ParseErrorDelegate(ParseError);
  900. csv.truncated += new CSV.TruncatedDelegate(Truncated);
  901.  
  902. // Mandatory columns
  903. string missingColumn = csv.FindMissingColumn(new string[] { "claim ref", "service by", "service date", /*"start time", "end time",*/ "units",
  904. "billable", "invoiced", "work done" });
  905. if (missingColumn != null) {
  906. LogError("Missing timeslip column: '" + missingColumn + "'");
  907. return;
  908. }
  909.  
  910. // Line
  911. sLine = 1;
  912.  
  913. // Records
  914. while (csv.ReadNext()) {
  915. // Line
  916. ++sLine;
  917.  
  918. // Mandatory fields
  919. string missingField = csv.FindMissingField(new string[] { "claim ref", "service by", "service date", "units", "billable", "invoiced", "work done" });
  920. if (missingField != null) {
  921. LogError("Missing timeslip field: '" + missingField + "'");
  922. IssueTracker.AddError(IssueTracker.Issue.MISSING_CSV_FIELD_VALUE, sLine.ToString(), missingField);
  923. continue;
  924. }
  925.  
  926. // Timeslip
  927. string claimNo = (string)ClaimNumber(csv.FieldOrNull("claim ref", 12));
  928. string serviceByName = csv.FieldOrNull("service by", 64) ?? "Ms Dalia Ismaiel";
  929. DateTime serviceDate = (DateTime)csv.DBFieldDateTime("service date");
  930. int units = (int)csv.DBFieldInt("units");
  931. decimal billable = (decimal)csv.DBFieldDecimalFlexible("billable");
  932. string invoiced = (bool)csv.DBFieldBool("invoiced") ? "T" : "F";
  933. string workDoneShort = csv.FieldOrNull("work done", 400);
  934.  
  935. // Duplicate check
  936. OleDbCommand command = new OleDbCommand("select top 1 H_BillingsID from H_Billings where Claim_No = ? and Service_By_Name = ? and Service_Date = ? and " +
  937. "Units = ? and Billable = ? and Invoice_Processed = ? and Work_Done_Short = ?", connection);
  938. command.Parameters.AddWithValue("@ClaimNo", claimNo);
  939. command.Parameters.AddWithValue("@ServiceByName", serviceByName);
  940. command.Parameters.AddWithValue("@ServiceDate", serviceDate);
  941. command.Parameters.AddWithValue("@Units", units);
  942. command.Parameters.AddWithValue("@Billable", billable);
  943. command.Parameters.AddWithValue("@Invoiced", invoiced);
  944. command.Parameters.AddWithValue("@WorkDoneShort", workDoneShort);
  945.  
  946. object billingID = command.ExecuteScalar();
  947. command.Dispose();
  948.  
  949. // Add billing if missing
  950. if ((billingID == null) || (billingID == DBNull.Value)) {
  951. // Billing ID
  952. billingID = Infor.IDFor("H_Billings", connection);
  953.  
  954. // Insert
  955. command = new OleDbCommand("insert into H_Billings (H_BillingsID, H_ClaimsID, Claim_No, Service_By, Service_By_Name, Service_Date, Start_Time, End_Time," +
  956. " Units, Is_Billable, Billable, Invoice_Processed, Work_Done, Work_Done_Short, Imported, PolicyID, Client_Code, Quarter," +
  957. " Rate, Rate_Per_Unit, Timeslip_Checked, Checked_By, Checked_Date, Invoice_Processed_By, Invoice_Processed_Date," +
  958. " Invoice_No, Invoice_Date) " +
  959. "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", connection);
  960. command.Parameters.AddWithValue("@BillingID", billingID);
  961.  
  962. // Claim
  963. object claimID = Infor.GetField("H_ClaimsID", "H_Claims", "Claim_Reference_Num = '" + claimNo + "'", connection) ?? DBNull.Value;
  964. if (claimID == DBNull.Value) {
  965. LogError("Claim not found: '" + claimNo + "'");
  966. IssueTracker.AddError(IssueTracker.Issue.CLAIM_NOT_FOUND, sLine.ToString(), claimNo);
  967. AddMissingClaim(claimNo);
  968. continue;
  969. }
  970. command.Parameters.AddWithValue("@ClaimID", claimID);
  971. command.Parameters.AddWithValue("@ClaimNo", claimNo);
  972.  
  973. // Service
  974. object serviceByID = Infor.GetField("UserID", "UserInfo", "Username = '" + serviceByName + "'", connection) ?? DBNull.Value;
  975. if (serviceByID == DBNull.Value) {
  976. LogError("Service by user not found: '" + serviceByName + "'");
  977. IssueTracker.AddError(IssueTracker.Issue.SERVICE_BY_USER_NOT_FOUND, sLine.ToString(), serviceByName);
  978. AddMissingUser(serviceByName);
  979. continue;
  980. }
  981. command.Parameters.AddWithValue("@ServiceBy", serviceByID);
  982. command.Parameters.AddWithValue("@ServiceByName", serviceByName);
  983. command.Parameters.AddWithValue("@ServiceDate", serviceDate);
  984.  
  985. // Start time
  986. object startTime = csv.DBFieldDateTime("start time");
  987. if (startTime == DBNull.Value)
  988. startTime = new DateTime(1899, 12, 31, 9, 0, 0);
  989. command.Parameters.AddWithValue("@StartTime", startTime);
  990.  
  991. // End time
  992. object endTime = csv.DBFieldDateTime("end time");
  993. if (endTime == DBNull.Value)
  994. endTime = ((DateTime)startTime).AddMinutes(units * 6);
  995. command.Parameters.AddWithValue("@StartTime", endTime);
  996.  
  997. // Fields
  998. command.Parameters.AddWithValue("@Units", units);
  999. command.Parameters.AddWithValue("@IsBillable", billable > 0.0m ? "T" : "F");
  1000. command.Parameters.AddWithValue("@Billable", billable);
  1001. command.Parameters.AddWithValue("@InvoiceProcessed", invoiced);
  1002. command.Parameters.AddWithValue("@WorkDone", csv.FieldOrNull("work done"));
  1003. command.Parameters.AddWithValue("@WorkDoneShort", workDoneShort);
  1004. command.Parameters.AddWithValue("@Imported", "T");
  1005.  
  1006. // Policy
  1007. command.Parameters.AddWithValue("@PolicyID", Infor.GetField("Policy_No", "H_Claims", "H_ClaimsID = '" + claimID + "'", connection) ?? DBNull.Value);
  1008.  
  1009. // Account
  1010. object accountID = Infor.GetField("AccountID", "H_Claims", "H_ClaimsID = '" + claimID + "'", connection) ?? DBNull.Value;
  1011. command.Parameters.AddWithValue("@AccountID", accountID);
  1012.  
  1013. // Quarter
  1014. command.Parameters.AddWithValue("@Quarter", "Quarter " + (serviceDate.Month <= 3 ? 1 : (serviceDate.Month <= 6 ? 2 : (serviceDate.Month <= 9 ? 3 : 4))) + " " + serviceDate.Year);
  1015.  
  1016. // Rates
  1017. object rate = csv.DBFieldDecimalFlexible("rate p hr");
  1018. object ratePerUnit = csv.DBFieldDecimalFlexible("rate p unit");
  1019.  
  1020. if ((accountID != DBNull.Value) && (rate == DBNull.Value)) {
  1021. string billingMethod = ((string)(Infor.GetField("Billing_Method", "Account", "AccountID = '" + accountID + "'", connection) ?? "")).ToLower();
  1022. if (billingMethod.Equals("per hour"))
  1023. rate = Infor.GetField("Service_Rage", "Account", "AccountID = '" + accountID + "'", connection) ?? DBNull.Value;
  1024. else
  1025. rate = Infor.GetField("Billable_Rate", "Account", "AccountID = '" + accountID + "'", connection) ?? DBNull.Value;
  1026. if (rate != DBNull.Value) {
  1027. ratePerUnit = (decimal)((double)rate / 10.0f);
  1028. rate = (decimal)(double)rate;
  1029. }
  1030. }
  1031.  
  1032. command.Parameters.AddWithValue("@Rate", rate);
  1033. command.Parameters.AddWithValue("@RatePerUnit", ratePerUnit);
  1034.  
  1035. // Status
  1036. command.Parameters.AddWithValue("@TimeslipChecked", invoiced);
  1037. //command.Parameters.AddWithValue("@CheckedBy", invoiced.Equals("T") ? (object)"ADMIN" : DBNull.Value);
  1038. command.Parameters.AddWithValue("@CheckedBy", DBNull.Value);
  1039. //command.Parameters.AddWithValue("@CheckedDate", invoiced.Equals("T") ? (object)DateTime.Now : DBNull.Value);
  1040. command.Parameters.AddWithValue("@CheckedDate", DBNull.Value);
  1041. command.Parameters.AddWithValue("@InvoiceProcessedBy", invoiced.Equals("T") ? (object)"ADMIN" : DBNull.Value);
  1042. object invoiceDate = csv.DBFieldDateTime("service date");
  1043. command.Parameters.AddWithValue("@InvoiceProcessedDate", invoiced.Equals("T") ? (invoiceDate != DBNull.Value ? invoiceDate : (object)DateTime.Now) : DBNull.Value);
  1044. object invoiceNumber = csv.DBFieldOrNull("invoice number");
  1045. command.Parameters.AddWithValue("@InvoiceNo", invoiceNumber != DBNull.Value ? invoiceNumber : (invoiced.Equals("T") ? (object)"IMPORTED" : DBNull.Value));
  1046. command.Parameters.AddWithValue("@InvoiceDate", invoiced.Equals("T") ? (object)DateTime.Now : DBNull.Value);
  1047.  
  1048. // Execute
  1049. command.ExecuteNonQuery();
  1050. Log("Billing (" + billingID + "): " + claimNo + ", " + serviceByName + ", " + serviceDate + ", " + units + ", " + billable);
  1051. }
  1052. //else
  1053. // LogWarning("Timeslip already exists: " + billingID);
  1054. }
  1055.  
  1056. // Line
  1057. sLine = -1;
  1058.  
  1059. // Close
  1060. csv.Dispose();
  1061. connection.Close();
  1062.  
  1063. // Summary
  1064. IssueTracker.LogSummary("Importing timeslips (" + sTime + ")");
  1065.  
  1066. // Missing
  1067. LogMissingSummary("Importing timeslips (" + sTime + ")");
  1068.  
  1069. // Whitespace
  1070. Log("\n");
  1071. }
  1072.  
  1073. //--------------------------------------------------------------------------------
  1074. private static void ImportCSVKeyContacts(string csvPath) {
  1075. // Log
  1076. Log("================================================================================");
  1077. Log("Importing key contacts (" + sTime + ")");
  1078. Log("================================================================================");
  1079.  
  1080. // Connection
  1081. OleDbConnection connection = new OleDbConnection(sInforConnectionString);
  1082. connection.Open();
  1083.  
  1084. // CSV
  1085. CSV csv = new CSV(csvPath);
  1086.  
  1087. // Delegates
  1088. csv.missingField += new CSV.MissingFieldDelegate(MissingField);
  1089. csv.parseError += new CSV.ParseErrorDelegate(ParseError);
  1090. csv.truncated += new CSV.TruncatedDelegate(Truncated);
  1091.  
  1092. // Mandatory columns
  1093. string missingColumn = csv.FindMissingColumn(new string[] { "account name", "name", "description", "address", "phone", "email", "notes" });
  1094. if (missingColumn != null) {
  1095. LogError("Missing key contacts column: '" + missingColumn + "'");
  1096. return;
  1097. }
  1098.  
  1099. // Line
  1100. sLine = 1;
  1101.  
  1102. // Records
  1103. while (csv.ReadNext()) {
  1104. // Line
  1105. ++sLine;
  1106.  
  1107. // Mandatory fields
  1108. string missingField = csv.FindMissingField(new string[] { "account name", "name" });
  1109. if (missingField != null) {
  1110. LogError("Missing key contacts field: '" + missingField + "'");
  1111. IssueTracker.AddError(IssueTracker.Issue.MISSING_CSV_FIELD_VALUE, sLine.ToString(), missingField);
  1112. continue;
  1113. }
  1114.  
  1115. // Key contact
  1116. string accountName = csv.FieldOrNull("account name", 128);
  1117. string contactName = csv.FieldOrNull("name", 200);
  1118.  
  1119. // Claims
  1120. OleDbCommand command = new OleDbCommand("select H_ClaimsID, Claim_Reference_Num from H_Claims where Account_Name = ?", connection);
  1121. command.Parameters.AddWithValue("@AccountName", accountName);
  1122. OleDbDataReader reader = command.ExecuteReader();
  1123. while (reader.Read()) {
  1124. // Claim
  1125. string claimID = (string)reader[0];
  1126. string claimNumber = reader[1] != null ? (string)reader[1] : "";
  1127.  
  1128. // Duplicate check
  1129. command = new OleDbCommand("select top 1 H_KeyContactsID from H_KeyContacts where H_ClaimsID = ? and Account_Name = ? and Contact_Name = ?", connection);
  1130. command.Parameters.AddWithValue("@ClaimID", claimID);
  1131. command.Parameters.AddWithValue("@AccountName", accountName);
  1132. command.Parameters.AddWithValue("@ContactName", contactName);
  1133. object keyContactID = command.ExecuteScalar();
  1134. command.Dispose();
  1135.  
  1136. // Add key contact if missing
  1137. if ((keyContactID == null) || (keyContactID == DBNull.Value)) {
  1138. // Key contact ID
  1139. keyContactID = Infor.IDFor("H_KeyContactsID", connection);
  1140.  
  1141. // Insert
  1142. command = new OleDbCommand("insert into H_KeyContacts (H_KeyContactsID, H_ClaimsID, Account_Name, AccountID, Contact_Name," +
  1143. " Description, Address, Phone, Email_Address, Notes, Imported) " +
  1144. "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", connection);
  1145. command.Parameters.AddWithValue("@KeyContactID", keyContactID);
  1146. command.Parameters.AddWithValue("@ClaimID", claimID);
  1147.  
  1148. // Account
  1149. object accountID = Infor.GetField("AccountID", "Account", "Account = '" + accountName.Replace("'", "''") + "'", connection) ?? DBNull.Value;
  1150. if (accountID == DBNull.Value) {
  1151. LogWarning("Key contact account not found: '" + accountName + "'");
  1152. IssueTracker.AddWarning(IssueTracker.Issue.ACCOUNT_NOT_FOUND, sLine.ToString(), accountName);
  1153. AddMissingAccount(accountName);
  1154. }
  1155. command.Parameters.AddWithValue("@AccountName", accountName);
  1156. command.Parameters.AddWithValue("@AccountID", accountID);
  1157.  
  1158. // Contact
  1159. command.Parameters.AddWithValue("@ContactName", contactName);
  1160.  
  1161. // Fields
  1162. command.Parameters.AddWithValue("@Description", csv.FieldOrNull("description", 400));
  1163. command.Parameters.AddWithValue("@Address", csv.FieldOrNull("address"));
  1164. command.Parameters.AddWithValue("@Phone", csv.FieldOrNull("phone", 200));
  1165. command.Parameters.AddWithValue("@Email_Address", csv.FieldOrNull("email", 200));
  1166. command.Parameters.AddWithValue("@Notes", csv.FieldOrNull("notes"));
  1167. command.Parameters.AddWithValue("@Imported", "T");
  1168.  
  1169. // Execute
  1170. command.ExecuteNonQuery();
  1171. command.Dispose();
  1172. Log("Key Contact (" + keyContactID + "): " + claimNumber + ", " + contactName + ", " + accountName);
  1173. }
  1174. }
  1175. reader.Close();
  1176. }
  1177.  
  1178. // Line
  1179. sLine = -1;
  1180.  
  1181. // Close
  1182. csv.Dispose();
  1183. connection.Close();
  1184.  
  1185. // Summary
  1186. IssueTracker.LogSummary("Importing key contacts (" + sTime + ")");
  1187.  
  1188. // Missing
  1189. LogMissingSummary("Importing key contacts (" + sTime + ")");
  1190.  
  1191. // Whitespace
  1192. Log("\n");
  1193. }
  1194.  
  1195. //--------------------------------------------------------------------------------
  1196. private static void ImportCSVPayments(string csvPath) {
  1197. // Log
  1198. Log("================================================================================");
  1199. Log("Importing payments (" + sTime + ")");
  1200. Log("================================================================================");
  1201.  
  1202. // Connection
  1203. OleDbConnection connection = new OleDbConnection(sInforConnectionString);
  1204. connection.Open();
  1205.  
  1206. // CSV
  1207. CSV csv = new CSV(csvPath);
  1208.  
  1209. // Delegates
  1210. csv.missingField += new CSV.MissingFieldDelegate(MissingField);
  1211. csv.parseError += new CSV.ParseErrorDelegate(ParseError);
  1212. csv.truncated += new CSV.TruncatedDelegate(Truncated);
  1213.  
  1214. // Mandatory columns
  1215. string missingColumn = csv.FindMissingColumn(new string[] { "id", "claim no", "payment type", "gross paid to date", "gst", "net paid to date", "payor", "payee",
  1216. "payment method", "cheque no", "pay date" });
  1217. if (missingColumn != null) {
  1218. LogError("Missing payment column: '" + missingColumn + "'");
  1219. return;
  1220. }
  1221.  
  1222. // Line
  1223. sLine = 1;
  1224.  
  1225. // Counts
  1226. int newPaymentCount = 0;
  1227. int updatedPaymentCount = 0;
  1228.  
  1229. // Records
  1230. while (csv.ReadNext()) {
  1231. // Line
  1232. ++sLine;
  1233.  
  1234. // Mandatory fields
  1235. string missingField = csv.FindMissingField(new string[] { "id", "claim no", "gross paid to date", "net paid to date" });
  1236. if (missingField != null) {
  1237. LogError("Missing payment field: '" + missingField + "'");
  1238. IssueTracker.AddError(IssueTracker.Issue.MISSING_CSV_FIELD_VALUE, sLine.ToString(), missingField);
  1239. continue;
  1240. }
  1241.  
  1242. // Amounts
  1243. decimal grossPaid = (decimal)csv.DBFieldDecimalFlexible("gross paid to date");
  1244. decimal netPaid = (decimal)csv.DBFieldDecimalFlexible("net paid to date");
  1245. if ((grossPaid <= 0.0m) && (netPaid <= 0.0m)) {
  1246. LogError("Gross paid and net paid both zero: " + grossPaid + ", " + netPaid);
  1247. IssueTracker.AddError(IssueTracker.Issue.ZERO_GROSS_PAID_NET_PAID, sLine.ToString(), grossPaid + ", " + netPaid);
  1248. continue;
  1249. }
  1250.  
  1251. if (grossPaid < netPaid) {
  1252. LogWarning("Gross paid less than net paid");
  1253. IssueTracker.AddWarning(IssueTracker.Issue.GROSS_PAID_LESS_THAN_NET_PAID, sLine.ToString(), grossPaid + ", " + netPaid);
  1254. //LogWarning("Gross paid less than net paid, swapping: " + grossPaid + " < " + netPaid);
  1255. //decimal temporary = grossPaid;
  1256. //grossPaid = netPaid;
  1257. //netPaid = temporary;
  1258. }
  1259.  
  1260. // Payment
  1261. int accessID = (int)csv.DBFieldInt("id");
  1262. string claimNo = (string)ClaimNumber(csv.FieldOrNull("claim no", 20));
  1263.  
  1264. // Duplicate check
  1265. OleDbCommand command = new OleDbCommand("select top 1 H_PaymentsID from H_Payments _P left join H_Claims _C on _P.H_ClaimsID = _C.H_ClaimsID " +
  1266. "where _P.AccessID = ? and _C.Claim_Reference_Num = ?", connection);
  1267. command.Parameters.AddWithValue("@AccessID", accessID);
  1268. command.Parameters.AddWithValue("@ClaimNo", (object)claimNo ?? DBNull.Value);
  1269.  
  1270. object paymentID = command.ExecuteScalar();
  1271. command.Dispose();
  1272.  
  1273. // Insert / update
  1274. Operation operation = ((paymentID == null) || (paymentID == DBNull.Value)) ? Operation.Insert : Operation.Update;
  1275. if (operation == Operation.Insert) {
  1276. command = new OleDbCommand("insert into H_Payments (H_PaymentsID, AccessID, H_ClaimsID, Reserve_Type, Invoice_Reference, Total_Gross, Total_Net, GST_Included, GST," +
  1277. " Date_Invoice_Paid, Payment_Status, Payment_Type, Payment_Method, Payment_To, Payment_To_Name," +
  1278. " Payee_Account, Payee_Account_Name, Is_Settlement, Imported) " +
  1279. "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'T')", connection);
  1280. paymentID = Infor.IDFor("H_Payments", connection);
  1281. command.Parameters.AddWithValue("@PaymentID", paymentID);
  1282. command.Parameters.AddWithValue("@AccessID", accessID);
  1283. }
  1284. else {
  1285. command = new OleDbCommand("update H_Payments set H_ClaimsID = ?, Reserve_Type = ?, Invoice_Reference = ?, Total_Gross = ?, Total_Net = ?, GST_Included = ?, GST = ?," +
  1286. " Date_Invoice_Paid = ?, Payment_Status = ?, Payment_Type = ?, Payment_Method = ?, Payment_To = ?, Payment_To_Name = ?," +
  1287. " Payee_Account = ?, Payee_Account_Name = ?, Is_Settlement = ? " +
  1288. "where H_PaymentsID = ? and AccessID = ? and Imported = 'T'", connection);
  1289. }
  1290.  
  1291. // Claim
  1292. object claimID = Infor.GetField("H_ClaimsID", "H_Claims", "Claim_Reference_Num = '" + claimNo + "'", connection) ?? DBNull.Value;
  1293. if (claimID == DBNull.Value) {
  1294. LogError("Claim not found: '" + claimNo + "'");
  1295. IssueTracker.AddError(IssueTracker.Issue.CLAIM_NOT_FOUND, sLine.ToString(), claimNo);
  1296. AddMissingClaim(claimNo);
  1297. continue;
  1298. }
  1299. command.Parameters.AddWithValue("@ClaimID", claimID);
  1300.  
  1301. // Claim status (FINANCIALS CHANGE)
  1302. /*string claimStatus = (string)Infor.GetField("Claim_Status", "H_Claims", "H_ClaimsID = '" + claimID +"'", connection);
  1303. if ((claimStatus != null) && claimStatus.ToLower().Contains("open")) {
  1304. LogWarning("Claim is open: '" + claimNo + "'");
  1305. IssueTracker.AddWarning(IssueTracker.Issue.CLAIM_IS_OPEN, sLine.ToString(), claimNo);
  1306. continue;
  1307. }*/
  1308.  
  1309. // Reserve type
  1310. string claimTeam = Infor.GetFieldString("Claim_Team_Name", "H_Claims", "Claim_Reference_Num = '" + claimNo + "'", connection) ?? "";
  1311. string reserveType = null;
  1312. //if (claimTeam.ToLower().Equals("risksmart gcc"))
  1313. // reserveType = "Liability Reserve";
  1314. string paymentType = csv.FieldOrNull("payment type", 200); // FINANCIALS CHANGE
  1315. if ((paymentType != null) && (paymentType.ToLower().Contains("legal") || paymentType.ToLower().Contains("assessor")))
  1316. reserveType = "Defence Reserve";
  1317. else
  1318. reserveType = "Liability Reserve";
  1319. command.Parameters.AddWithValue("@ReserveType", reserveType);
  1320.  
  1321. // Fields
  1322. command.Parameters.AddWithValue("@InvoiceReference", csv.FieldOrNull("cheque no", 100));
  1323. command.Parameters.AddWithValue("@TotalGross", grossPaid);
  1324. command.Parameters.AddWithValue("@TotalNet", netPaid);
  1325.  
  1326. // GST
  1327. bool gstIncluded = false;
  1328. /*if ((grossPaid > 0.0m) && (netPaid > 0.0m) && (grossPaid > netPaid)) {
  1329. double differenceRatio = (double)((grossPaid - netPaid) / netPaid);
  1330. gstIncluded = (differenceRatio > 0.0999) && (differenceRatio < 0.1001);
  1331. }*/
  1332. command.Parameters.AddWithValue("@GSTIncluded", gstIncluded ? "T" : "F");
  1333. command.Parameters.AddWithValue("@GST", 0.1);
  1334.  
  1335. // Fields
  1336. command.Parameters.AddWithValue("@DateInvoicePaid", csv.DBFieldDateTime("pay date"));
  1337. command.Parameters.AddWithValue("@PaymentStatus", csv.DBFieldDateTime("pay date") != DBNull.Value ? (object)"Paid" : DBNull.Value);
  1338. command.Parameters.AddWithValue("@PaymentType", csv.FieldOrNull("payment type", 200));
  1339. command.Parameters.AddWithValue("@PaymentMethod", csv.FieldOrNull("payment method", 200));
  1340.  
  1341. // Payment to
  1342. object paymentToID = DBNull.Value;
  1343. object paymentTo = AccountName(csv.FieldOrNull("payor", 200));
  1344. if (paymentTo != null) {
  1345. paymentToID = Infor.GetField("AccountID", "Account", "Account = '" + ((string)paymentTo).Replace("'", "''") + "'", connection) ?? DBNull.Value;
  1346. if (paymentToID == DBNull.Value) {
  1347. LogWarning("Payment to account not found: '" + paymentTo + "'");
  1348. IssueTracker.AddWarning(IssueTracker.Issue.ACCOUNT_NOT_FOUND, sLine.ToString(), paymentTo.ToString());
  1349. AddMissingAccount((string)paymentTo);
  1350. }
  1351. }
  1352. command.Parameters.AddWithValue("@PaymentTo", paymentToID);
  1353. command.Parameters.AddWithValue("@PaymentToName", paymentTo);
  1354.  
  1355. // Payee
  1356. object payeeAccountID = DBNull.Value;
  1357. object payeeAccount = AccountName(csv.FieldOrNull("payee", 200));
  1358. if (payeeAccount != null) {
  1359. payeeAccountID = Infor.GetField("AccountID", "Account", "Account = '" + ((string)payeeAccount).Replace("'", "''") + "'", connection) ?? DBNull.Value;
  1360. if (payeeAccountID == DBNull.Value) {
  1361. LogWarning("Payee account not found: '" + payeeAccount + "'");
  1362. IssueTracker.AddWarning(IssueTracker.Issue.ACCOUNT_NOT_FOUND, sLine.ToString(), payeeAccount.ToString());
  1363. AddMissingAccount((string)payeeAccount);
  1364. }
  1365. }
  1366. command.Parameters.AddWithValue("@PayeeAccount", payeeAccountID);
  1367. command.Parameters.AddWithValue("@PayeeAccountName", payeeAccount);
  1368.  
  1369. // Settlement
  1370. bool isSettlement = (csv.FieldOrNull("payment type", 200) ?? "").ToLower().Equals("settlement");
  1371. command.Parameters.AddWithValue("@IsSettlement", isSettlement ? "T" : "F");
  1372.  
  1373. // Claim settlement
  1374. if (isSettlement) {
  1375. OleDbCommand claimCommand = new OleDbCommand("update H_Claims set Outcome_Settlement = 'T' where Claim_Reference_Num = ?", connection);
  1376. claimCommand.Parameters.AddWithValue("@ClaimReferenceNum", claimNo);
  1377. claimCommand.ExecuteNonQuery();
  1378. claimCommand.Dispose();
  1379. Log("Claim Settlement (" + claimNo + ")");
  1380. }
  1381.  
  1382. // Where
  1383. if (operation == Operation.Update) {
  1384. command.Parameters.AddWithValue("@PaymentID", paymentID);
  1385. command.Parameters.AddWithValue("@AccessID", accessID);
  1386. }
  1387.  
  1388. // Execute
  1389. command.ExecuteNonQuery();
  1390. command.Dispose();
  1391. Log((operation == Operation.Update ? "Updated " : "") + "Payment (" + paymentID + "): " + claimNo + ", " + accessID + ", " + grossPaid + ", " + netPaid);
  1392. if (operation == Operation.Insert)
  1393. ++newPaymentCount;
  1394. else
  1395. ++updatedPaymentCount;
  1396. }
  1397.  
  1398. // Line
  1399. sLine = -1;
  1400.  
  1401. // Close
  1402. csv.Dispose();
  1403. connection.Close();
  1404.  
  1405. // Summary
  1406. Log("\n--------------------------------------------------------------------------------");
  1407. Log("RESULTS (Importing payments (" + sTime + ")):");
  1408. Log(" " + newPaymentCount + " payments added.");
  1409. Log(" " + updatedPaymentCount + " payments updated.\n");
  1410.  
  1411. // Summary
  1412. IssueTracker.LogSummary("Importing payments (" + sTime + ")");
  1413.  
  1414. // Missing
  1415. LogMissingSummary("Importing payments (" + sTime + ")");
  1416.  
  1417. // Whitespace
  1418. Log("\n");
  1419. }
  1420.  
  1421. //--------------------------------------------------------------------------------
  1422. private static void ImportCSVReserves(string csvPath) {
  1423. // Log
  1424. Log("================================================================================");
  1425. Log("Importing reserves (" + sTime + ")");
  1426. Log("================================================================================");
  1427.  
  1428. // Reserves
  1429. Dictionary<string, List<Tuple<int, decimal>>> reserves = new Dictionary<string, List<Tuple<int, decimal>>>();
  1430.  
  1431. // Connection
  1432. OleDbConnection connection = new OleDbConnection(sInforConnectionString);
  1433. connection.Open();
  1434.  
  1435. // CSV
  1436. CSV csv = new CSV(csvPath);
  1437.  
  1438. // Delegates
  1439. csv.missingField += new CSV.MissingFieldDelegate(MissingField);
  1440. csv.parseError += new CSV.ParseErrorDelegate(ParseError);
  1441. csv.truncated += new CSV.TruncatedDelegate(Truncated);
  1442.  
  1443. // Mandatory columns
  1444. string missingColumn = csv.FindMissingColumn(new string[] { "id", "claim no", "reserve" });
  1445. if (missingColumn != null) {
  1446. LogError("Missing reserve column: '" + missingColumn + "'");
  1447. return;
  1448. }
  1449.  
  1450. // Line
  1451. sLine = 1;
  1452.  
  1453. // Records
  1454. while (csv.ReadNext()) {
  1455. // Line
  1456. ++sLine;
  1457.  
  1458. // Mandatory fields
  1459. string missingField = csv.FindMissingField(new string[] { "id", "claim no", "reserve" });
  1460. if (missingField != null) {
  1461. LogError("Missing reserve field: '" + missingField + "'");
  1462. IssueTracker.AddError(IssueTracker.Issue.MISSING_CSV_FIELD_VALUE, sLine.ToString(), missingField);
  1463. continue;
  1464. }
  1465.  
  1466. // Reserve
  1467. decimal reserve = (decimal)csv.DBFieldDecimalFlexible("reserve");
  1468. if (reserve <= 0.0m) {
  1469. LogError("Reserve is zero: " + reserve);
  1470. IssueTracker.AddError(IssueTracker.Issue.ZERO_RESERVE, sLine.ToString(), reserve.ToString());
  1471. continue;
  1472. }
  1473.  
  1474. // Details
  1475. int accessID = (int)csv.DBFieldInt("id");
  1476. string claimNo = (string)ClaimNumber(csv.FieldOrNull("claim no", 20));
  1477.  
  1478. // Add
  1479. if (!reserves.ContainsKey(claimNo))
  1480. reserves.Add(claimNo, new List<Tuple<int, decimal>>());
  1481. reserves[claimNo].Add(new Tuple<int, decimal>(accessID, reserve));
  1482. }
  1483.  
  1484. // Line
  1485. sLine = -1;
  1486.  
  1487. // Close csv
  1488. csv.Dispose();
  1489.  
  1490. // Process reserves
  1491. int ambiguousCount = 0;
  1492. foreach (KeyValuePair<string, List<Tuple<int, decimal>>> c in reserves) {
  1493. // Already imported
  1494. //OleDbCommand command = new OleDbCommand("select H_ClaimsID from H_Claims where Claim_Reference_Num = ? and Imported = 'T' and Imported_Reserve is null or Imported_Reserve <> 'T'", connection);
  1495. //OleDbCommand command = new OleDbCommand("select H_ClaimsID from H_Claims where Claim_Reference_Num = ? and Imported = 'T'", connection);
  1496. OleDbCommand command = new OleDbCommand("select H_ClaimsID from H_Claims where Claim_Reference_Num = ? and Imported = 'T' and (Claim_Status is null or Claim_Status not like '%open%')", connection); // FINANCIALS CHANGE
  1497. command.Parameters.AddWithValue("@ClaimReferenceNum", c.Key);
  1498. object claimID = command.ExecuteScalar();
  1499. command.Dispose();
  1500.  
  1501. if ((claimID == null) || (claimID == DBNull.Value)) {
  1502. //Log("Skipped reserve (" + c.Key + ")"); // Shows missing claims, not just skipped ones
  1503. continue;
  1504. }
  1505.  
  1506. // First reserve
  1507. int firstID = int.MaxValue;
  1508. decimal firstReserve = 0;
  1509.  
  1510. foreach (Tuple<int, decimal> r in c.Value) {
  1511. if (r.Item1 < firstID) {
  1512. firstID = r.Item1;
  1513. firstReserve = r.Item2;
  1514. }
  1515. }
  1516.  
  1517. // Update
  1518. command = new OleDbCommand("update H_Claims set Liability_Res_Source = ?, Imported_Reserve = 'T' where Claim_Reference_Num = ?", connection);
  1519. command.Parameters.AddWithValue("@LiabilityResSource", firstReserve);
  1520. command.Parameters.AddWithValue("@ClaimReferenceNum", c.Key);
  1521. command.ExecuteNonQuery();
  1522. command.Dispose();
  1523. Log("Reserve (" + c.Key + "): " + firstReserve);
  1524.  
  1525. // Ambiguous
  1526. if (c.Value.Count > 1) {
  1527. ++ambiguousCount;
  1528.  
  1529. // String
  1530. string ambiguousReservesString = "RESERVE (" + DateTime.Now + "):";
  1531. string reserveList = "";
  1532. foreach (Tuple<int, decimal> r in c.Value) {
  1533. ambiguousReservesString += "\n $" + r.Item2;
  1534. reserveList += (reserveList.Length > 0 ? ", " : "") + "$" + r.Item2;
  1535. }
  1536.  
  1537. // Note
  1538. object claimNoteObject = Infor.GetField("Claim_Note", "H_Claims", "Claim_Reference_Num = '" + c.Key.Replace("'", "''") + "'", connection) ?? DBNull.Value;
  1539. string claimNote = (claimNoteObject != DBNull.Value ? (string)claimNoteObject : "");
  1540.  
  1541. // Apply indication
  1542. command = new OleDbCommand("update H_Claims set Ambiguous_Reserve = 'T', Claim_Note = ? " +
  1543. "where Claim_Reference_Num = ?", connection);
  1544. command.Parameters.AddWithValue("@ClaimNote", ambiguousReservesString + "\n\n" + claimNote);
  1545. command.Parameters.AddWithValue("@ClaimReferenceNum", c.Key);
  1546. command.ExecuteNonQuery();
  1547. command.Dispose();
  1548.  
  1549. // Log
  1550. Log("Ambiguous Reserve (" + c.Key + "): " + reserveList);
  1551. IssueTracker.AddWarning(IssueTracker.Issue.AMBIGUOUS_RESERVE, c.Key, reserveList);
  1552. }
  1553. }
  1554.  
  1555. // Close connection
  1556. connection.Close();
  1557.  
  1558. // Summary
  1559. Log("\n--------------------------------------------------------------------------------");
  1560. Log("RESULTS (Importing reserves (" + sTime + ")):\n");
  1561. Log(" " + ambiguousCount + " ambiguous reserves.\n");
  1562.  
  1563. // Summary
  1564. IssueTracker.LogSummary("Importing reserves (" + sTime + ")");
  1565.  
  1566. // Whitespace
  1567. Log("\n");
  1568. }
  1569.  
  1570.  
  1571. // ACCESS ================================================================================
  1572. //--------------------------------------------------------------------------------
  1573. private static void ImportAccessClaims() {
  1574. // Log
  1575. Log("================================================================================");
  1576. Log(TeamName() + ": Importing Access claims (" + sTime + ")");
  1577. Log("================================================================================");
  1578.  
  1579. // Connection
  1580. OleDbConnection connection = new OleDbConnection(sInforConnectionString);
  1581. connection.Open();
  1582.  
  1583. // Adapters
  1584. Log("Retrieving claim data...");
  1585. OleDbDataAdapter stagingAdapter = new OleDbDataAdapter("select * from S_Acc_Claim", sInforConnectionString);
  1586. OleDbDataAdapter claimAdapter = new OleDbDataAdapter("select * from H_Claims where Claim_Reference_Num in (select Claim_No from S_Acc_Claim) and Imported = 'T'", sInforConnectionString);
  1587. OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(claimAdapter);
  1588. claimAdapter.InsertCommand = commandBuilder.GetInsertCommand(true);
  1589. claimAdapter.UpdateCommand = commandBuilder.GetUpdateCommand(true);
  1590.  
  1591. // Dataset
  1592. DataSet dataSet = new DataSet();
  1593. stagingAdapter.SelectCommand.CommandTimeout = 1200;
  1594. stagingAdapter.Fill(dataSet, "S_Acc_Claim");
  1595. claimAdapter.SelectCommand.CommandTimeout = 1200;
  1596. claimAdapter.Fill(dataSet, "H_Claims");
  1597.  
  1598. // Tables
  1599. DataTable accessClaim = dataSet.Tables["S_Acc_Claim"];
  1600. DataTable claim = dataSet.Tables["H_Claims"];
  1601. int i = 1;
  1602.  
  1603. // Log
  1604. Log("Importing...");
  1605.  
  1606. // Counts
  1607. int newClaimCount = 0;
  1608. int updatedClaimCount = 0;
  1609.  
  1610. foreach (DataRow s in accessClaim.Rows) {
  1611. // Claim number
  1612. object claimNumber = ClaimNumber(s["Claim_No"]);
  1613.  
  1614. // Incident type
  1615. string incidentType = null;
  1616.  
  1617. // Claim type
  1618. object claimTypeObject = Truncate(200, ConvertDDField(s, "DD_Claim_Type", "Claim_Type", new string[] { "Claim", "Notification" }));
  1619. string claimType = "";
  1620. if (claimTypeObject != DBNull.Value)
  1621. claimType = ((string)claimTypeObject).ToLower();
  1622. if (!new string[] { "claim", "notification" }.Contains(claimType)) {
  1623. if ((sTeam == Team.RisksmartGCC) && claimType.Contains("motor vehicle")) {
  1624. claimType = "claim";
  1625. incidentType = "Recovery - Motor Vehicle Impact/ Accident";
  1626. }
  1627. else
  1628. claimType = "unknown";
  1629. }
  1630.  
  1631. claimType = claimType.First().ToString().ToUpper() + claimType.Substring(1);
  1632.  
  1633. // Status
  1634. object statusObject = Truncate(200, ConvertDDField(s, "DD_Claim_Status", "Claim_Status"));
  1635. string status = "";
  1636. if (statusObject != DBNull.Value)
  1637. status = ((string)statusObject).ToLower();
  1638.  
  1639. // Open or notification
  1640. bool isOpen = status.Contains("open");
  1641. bool isOpenOrNotification = isOpen || (new string[] { "Claim", "Notification" }.Contains(claimType));
  1642.  
  1643. // Team check
  1644. string clientGroup = (s["DD_Client_Group"] != DBNull.Value ? ((string)s["DD_Client_Group"]).ToLower() : "");
  1645. if (sTeam == Team.RisksmartGCC) {
  1646. if (!(new string[] { "aldi", "ezko property services (aust) retail pty ltd", "ezko property services (aust) pty ltd", "whirlpool", "millers liability",
  1647. "folkestone limited", " hydro flow pty ltd", "hydro flow pty ltd" }).Contains(clientGroup))
  1648. {
  1649. continue; // Skip
  1650. }
  1651. }
  1652. else if (sTeam == Team.RisksmartProperty) {
  1653. if (!(new string[] { "chu strata", /*"honan lloyds facility",*/ "honan residential landlords property" }).Contains(clientGroup))
  1654. continue; // Skip
  1655. }
  1656.  
  1657. // Validation
  1658. if (claimNumber == DBNull.Value) {
  1659. LogError("Missing claim field: 'claim_no'");
  1660. continue;
  1661. }
  1662. if ((s["DD_Claim_Type"] == DBNull.Value) && (s["Claim_Type"] == DBNull.Value)) {
  1663. //LogError("Missing claim field (" + claimNumber + "): 'claim_type'");
  1664. //continue;
  1665. LogError("Missing claim field (" + claimNumber + "): 'claim_type'");
  1666. IssueTracker.AddError(IssueTracker.Issue.MISSING_CLAIM_TYPE, (string)claimNumber);
  1667. continue;
  1668. }
  1669. else if (((s["DD_Claim_Type"] == DBNull.Value) || (!new string[] { "claim", "notification" }.Contains(((string)s["DD_Claim_Type"]).ToLower()))) &&
  1670. ((s["Claim_Type"] == DBNull.Value) || (!new string[] { "claim", "notification" }.Contains(((string)s["Claim_Type"]).ToLower()))))
  1671. {
  1672. //LogError("Invalid claim type (" + claimNumber + "): '" + s["DD_Claim_Type"] + "' / '" + s["Claim_Type"] + "'");
  1673. //continue;
  1674. LogWarning("Invalid claim type (" + claimNumber + "): '" + s["DD_Claim_Type"] + "' / '" + s["Claim_Type"] + "'");
  1675. IssueTracker.AddWarning(IssueTracker.Issue.INVALID_CLAIM_TYPE, (string)claimNumber, s["DD_Claim_Type"] + "', '" + s["Claim_Type"] + "'");
  1676. }
  1677. /*if ((s["Policy_Number"] == DBNull.Value) || ((string)s["Policy_Number"]).ToLower().Contains("xx") || ((string)s["Policy_Number"]).ToLower().Contains("tba")) {
  1678. LogError("Missing claim field (" + claimNumber + "): 'policy_number'");
  1679. continue;
  1680. }*/
  1681. //if ((s["DD_Insured"] == DBNull.Value) && (s["Insured"] == DBNull.Value)) {
  1682. // LogError("Missing claim field (" + claimNumber + "): 'dd_unsured/insured'");
  1683. // continue;
  1684. //}
  1685. //if (s["Date_Reported"] == DBNull.Value) {
  1686. // LogError("Missing claim field (" + claimNumber + "): 'date_reported'");
  1687. // continue;
  1688. //}
  1689. //if ((sTeam == Team.RisksmartGCC) && (s["Incident_Name"] == DBNull.Value)) {
  1690. // LogError("Missing claim field (" + claimNumber + "): 'incident_name'");
  1691. // continue;
  1692. //}
  1693. //if (s["Incident_Date"] == DBNull.Value) {
  1694. // LogError("Missing claim field (" + claimNumber + "): 'incident_date'");
  1695. // continue;
  1696. //}
  1697.  
  1698. // Existing claim
  1699. DataRow c = null;
  1700. DataRow[] claims = claim.Select("Claim_Reference_Num = '" + claimNumber + "'");
  1701. if (claims.Length > 0) {
  1702. if (claims.Length > 1) {
  1703. LogWarning("Claim found more than once: '" + s["Claim_No"] + "'");
  1704. IssueTracker.AddWarning(IssueTracker.Issue.MULTIPLE_CLAIMS_FOUND, (string)claimNumber);
  1705. }
  1706. c = claims[0];
  1707. }
  1708.  
  1709. // Claim row
  1710. Operation operation = (c == null ? Operation.Insert : Operation.Update);
  1711. if (c == null)
  1712. c = claim.NewRow();
  1713.  
  1714. // ID
  1715. object claimID = ((operation == Operation.Insert) ? Infor.IDFor("H_Claims", connection) : c["H_ClaimsID"]);
  1716.  
  1717. // SLX
  1718. c["H_ClaimsID"] = claimID;
  1719. c["CreateUser"] = "ADMIN";
  1720. //c["CreateDate"] = DateTime.UtcNow.ToString("s", System.Globalization.CultureInfo.InvariantCulture);
  1721. c["CreateDate"] = ConvertDateTime(s["Date_Registered"], claimNumber);
  1722. c["ModifyUser"] = "ADMIN";
  1723. //c["ModifyDate"] = c["CreateDate"];
  1724. c["ModifyDate"] = DateTime.UtcNow.ToString("s", System.Globalization.CultureInfo.InvariantCulture);
  1725.  
  1726. // Claim
  1727. c["Imported"] = "T";
  1728. c["Claim_Reference_Num"] = Truncate(20, claimNumber);
  1729. //c["Claim_Type"] = Truncate(200, ConvertDDField(s, "DD_Claim_Type", "Claim_Type", new string[] { "Claim", "Notification" }));
  1730. c["Claim_Type"] = claimType;
  1731. c["Claim_Status"] = statusObject;
  1732. c["Reported_Date"] = ConvertDateTime(s["Date_Reported"], claimNumber);
  1733. c["Reported_Time"] = ConvertDateTime(s["Date_Reported"], claimNumber);
  1734. c["Client_Group"] = Truncate(400, s["DD_Client_Group"]);
  1735.  
  1736. // Team
  1737. c["Claim_Team_Name"] = TeamName();
  1738. switch (sTeam) {
  1739. case Team.RisksmartGCC: c["Claim_Team"] = Infor.GetField("SeccodeID", "Seccode", "SeccodeTYpe = 'G' and SeccodeDesc = 'Risksmart GCC Team'", connection); break;
  1740. case Team.RisksmartProperty: c["Claim_Team"] = Infor.GetField("SeccodeID", "Seccode", "SeccodeTYpe = 'G' and SeccodeDesc = 'Risksmart Property Team'", connection); break;
  1741. }
  1742. c["SeccodeID"] = c["Claim_Team"];
  1743.  
  1744. // Policy
  1745. c["Policy_No_Name"] = PolicyNumber(Truncate(200, s["Policy_Number"]));
  1746. c["Imported_Policy_Number"] = c["Policy_No_Name"];
  1747. if (/*isOpenOrNotification && */(c["Policy_No_Name"] != DBNull.Value)) {
  1748. c["Policy_No"] = Infor.GetField("H_PolicyID", "H_Policy", "Policy_Number = '" + ((string)c["Policy_No_Name"]).Replace("'", "''") + "'", connection) ?? DBNull.Value;
  1749. if (c["Policy_No"] == DBNull.Value) {
  1750. LogWarning("Policy not found (" + claimNumber + "): '" + c["Policy_No_Name"] + "'");
  1751. IssueTracker.AddWarning(IssueTracker.Issue.POLICY_NOT_FOUND, (string)claimNumber, c["Policy_No_Name"].ToString());
  1752. AddMissingPolicy((string)c["Policy_No_Name"], isOpen);
  1753. }
  1754. }
  1755. c["Binder"] = Truncate(200, s["Binder_Number"]);
  1756. c["Policy_Section"] = Truncate(400, ConvertDDField(s, "DD_Policy_Section", "Segment"));
  1757.  
  1758. // Account
  1759. c["Account_Name"] = AccountName(Truncate(200, ConvertDDField(s, "DD_Insured", "Insured"))) ?? DBNull.Value;
  1760. c["Imported_Account_Name"] = c["Account_Name"];
  1761. if (/*isOpenOrNotification && */(c["Account_Name"] != DBNull.Value)) {
  1762. c["AccountID"] = Infor.GetField("AccountID", "Account", "Type = 'Customer' and (Account = '" + ((string)c["Account_Name"]).Replace("'", "''") + "' or Client_Code = '" + c["Account_Name"] +
  1763. "' or ExternalAccountNo = '" + c["Account_Name"] + "')", connection) ?? DBNull.Value;
  1764. if (c["AccountID"] != DBNull.Value)
  1765. c["Account_Name"] = Infor.GetField("Account", "Account", "AccountID = '" + c["AccountID"] + "'", connection);
  1766. else {
  1767. LogWarning("Account not found (" + claimNumber + "): '" + c["Account_Name"] + "'");
  1768. IssueTracker.AddWarning(IssueTracker.Issue.CUSTOMER_NOT_FOUND, (string)claimNumber, c["Account_Name"].ToString());
  1769. AddMissingCustomer((string)c["Account_Name"], isOpen);
  1770. }
  1771. }
  1772.  
  1773. // Insurance
  1774. c["Insurer_Name"] = AccountName(Truncate(200, ConvertDDField(s, "DD_Insurer", "Insurer"))) ?? DBNull.Value;
  1775. c["Imported_Insurer_Name"] = c["Insurer_Name"];
  1776. if (/*isOpenOrNotification && */(c["Insurer_Name"] != DBNull.Value)) {
  1777. c["Insurer"] = Infor.GetField("AccountID", "Account", "Type = 'Underwriter' and (Account = '" + ((string)c["Insurer_Name"]).Replace("'", "''") + "' or Client_Code = '" + c["Insurer_Name"] +
  1778. "' or ExternalAccountNo = '" + c["Insurer_Name"] + "')", connection) ?? DBNull.Value;
  1779. if (c["Insurer"] != DBNull.Value)
  1780. c["Insurer_Name"] = Infor.GetField("Account", "Account", "AccountID = '" + c["Insurer"] + "'", connection);
  1781. else {
  1782. LogWarning("Insurer not found (" + claimNumber + "): '" + c["Insurer_Name"] + "'");
  1783. IssueTracker.AddWarning(IssueTracker.Issue.UNDERWRITER_NOT_FOUND, (string)claimNumber, c["Insurer_Name"].ToString());
  1784. AddMissingUnderwriter((string)c["Insurer_Name"], isOpen);
  1785. }
  1786. }
  1787. c["Insured_Name"] = Truncate(200, ConvertDDField(s, "DD_Insured", "Insured"));
  1788. c["Insurer_Ref"] = Truncate(200, s["Insurer_Reference"]);
  1789.  
  1790. // Broker
  1791. c["Broker_Company_Name"] = AccountName(Truncate(200, ConvertDDField(s, "DD_Broker", "Broker"))) ?? DBNull.Value;
  1792. c["Imported_Broker_Name"] = c["Broker_Company_Name"];
  1793. if (/*isOpenOrNotification && */(c["Broker_Company_Name"] != DBNull.Value)) {
  1794. c["Broker_Company"] = Infor.GetField("AccountID", "Account", "Type = 'Associate' and (Account = '" + ((string)c["Broker_Company_Name"]).Replace("'", "''") + "' or Client_Code = '" + c["Broker_Company_Name"] +
  1795. "' or ExternalAccountNo = '" + c["Broker_Company_Name"] + "')", connection) ?? DBNull.Value;
  1796. if (c["Broker_Company"] != DBNull.Value)
  1797. c["Broker_Company_Name"] = Infor.GetField("Account", "Account", "AccountID = '" + c["Broker_Company"] + "'", connection);
  1798. else {
  1799. LogWarning("Broker company not found (" + claimNumber + "): '" + c["Broker_Company_Name"] + "'");
  1800. IssueTracker.AddWarning(IssueTracker.Issue.ASSOCIATE_NOT_FOUND, (string)claimNumber, c["Broker_Company_Name"].ToString());
  1801. AddMissingAssociate((string)c["Broker_Company_Name"], isOpen);
  1802. }
  1803. }
  1804.  
  1805. // Assigned user
  1806. if (s["Claim_Operator"] != DBNull.Value) {
  1807. string username = (string)UserName(s["Claim_Operator"]);
  1808. c["Assigned_User"] = Infor.GetField("UserID", "UserInfo", "Username = '" + username + "'", connection) ?? DBNull.Value;
  1809. if (c["Assigned_User"] == DBNull.Value) {
  1810. // Log
  1811. LogWarning("Assigned user not found: '" + username + "'");
  1812. IssueTracker.AddWarning(IssueTracker.Issue.ASSIGNED_USER_NOT_FOUND, (string)claimNumber, username);
  1813. AddMissingUser(username, isOpen);
  1814.  
  1815. // Fallback user
  1816. c["Assigned_User"] = Infor.GetField("UserID", "UserInfo", "Username = 'Ms Megan Peacock'", connection) ?? DBNull.Value;
  1817. }
  1818. }
  1819. c["Assigned_Date"] = ConvertDateTime(s["Date_Registered"], claimNumber);
  1820.  
  1821. // Claimant
  1822. c["Claimant_First_Name"] = Truncate(200, ConvertFirstName(s["Claimant_1"]));
  1823. c["Claimant_Last_Name"] = Truncate(200, ConvertLastName(s["Claimant_1"]));
  1824. c["Claimant_Address"] = Truncate(400, JoinStringFields(s["Claimant_Address"], s["Claimant_1_State"], ", "));
  1825. c["Date_Of_Birth"] = Truncate(32, s["Date_Of_Birth"]);
  1826. c["Claimant_Work_Phone"] = Truncate(200, ConvertWorkPhone(s["Contact_No"]));
  1827. c["Claimant_Mobile_Phone"] = Truncate(200, ConvertMobilePhone(s["Contact_No"]));
  1828. c["Email_Address"] = Truncate(200, s["Email_Address"]);
  1829.  
  1830. // Incident
  1831. c["Incident_Name"] = Truncate(400, s["Incident_Name"]);
  1832. object incidentAddress = JoinStringFields(s["DD_Incident_Location_Address"], s["DD_Incident_Location_Suburb"], ", ");
  1833. incidentAddress = JoinStringFields(incidentAddress, s["DD_Incident_Location_State"], ", ");
  1834. incidentAddress = JoinStringFields(incidentAddress, s["DD_Incident_Location_Postcode"], ", ");
  1835. c["Incident_Address"] = Truncate(400, incidentAddress);
  1836. c["Incident_Date"] = ConvertDateTime(s["Incident_Date"], claimNumber);
  1837. c["Incident_Time"] = ConvertDateTime(s["Incident_Date"], claimNumber);
  1838. c["Incident_Category"] = Truncate(400, ConvertDDField(s, "DD_Incident_Category", "Incident_Category"));
  1839. c["Incident_Summary"] = s["Incident_Description"];
  1840. if (incidentType != null)
  1841. c["Incident_Type"] = incidentType;
  1842. c["Juristiction"] = Truncate(32, ConvertDDField(s, "DD_Jurisdiction", "Jurisdiction"));
  1843. c["Bodily_Location"] = Truncate(200, ConvertDDField(s, "DD_Bodily_Location", "Bodily_Location"));
  1844.  
  1845. // Store
  1846. c["StoreID_Name"] = Truncate(200, ConvertDDField(s, "DD_Incident_Location_Code", "Incident_Location"));
  1847. if ((c["StoreID_Name"] != null) && (c["StoreID_Name"] != DBNull.Value))
  1848. c["Region"] = Truncate(200, RegionFromStore((string)c["StoreID_Name"], (string)claimNumber));
  1849.  
  1850. // Trading name
  1851. //c["Trading_Name"] = s["Trading"]
  1852.  
  1853. // Property
  1854. //c["Property_Address_1"] = Truncate(200, s["DD_Incident_Location_Address"]);
  1855. c["Property_Address_1"] = Truncate(200, JoinStringFields(s["Claimant_Address"], s["Claimant_1_State"], ", "));
  1856. c["Property_Suburb"] = Truncate(200, s["DD_Incident_Location_Suburb"]);
  1857. c["Property_State"] = Truncate(32, s["DD_Incident_Location_State"]);
  1858. c["Property_Postalcode"] = Truncate(32, s["DD_Incident_Location_Postcode"]);
  1859. if (sTeam == Team.RisksmartProperty)
  1860. c["Loss_Details"] = s["Incident_Description"];
  1861.  
  1862. // Other
  1863. c["Date_Of_Loss"] = ConvertDateTime(s["Incident_Date"], claimNumber);
  1864. //c["Are_You_GST_Registered"] = ; // Claim doesn't have a gst property - only financial t records do
  1865.  
  1866. // Reserve
  1867. c["Liability_Res_Source"] = s["Reserve"];
  1868.  
  1869. // State
  1870. //c["Review"] = (ConvertDateTime(s["Date_Reviewed"], claimNumber) != DBNull.Value ? "T" : "F");
  1871. //c["Review_Date"] = ConvertDateTime(s["Date_Reviewed"], claimNumber);
  1872. c["Claim_Closed"] = (ConvertDateTime(s["Date_Closed"], claimNumber) != DBNull.Value ? "T" : "F");
  1873. c["Claim_Closed_Date"] = ConvertDateTime(s["Date_Closed"], claimNumber);
  1874.  
  1875. // E-mail on 09/10/2017 - RE: Important Task - Data Check - Risksmart GCC Imported Claims Data
  1876. // Decision was to remove inferring value of these fields.
  1877. //c["Outcome_Settlement"] = (new string[] { "closed paid", "open pending settlement" }.Contains(status)) ? "T" : "F";
  1878. //c["Outcome_Declined"] = (new string[] { "closed declined", "open declined (pending mgt review)" }.Contains(status)) ? "T" : "F";
  1879.  
  1880. //if (new string[] { "closed paid", "open pending settlement" }.Contains(status))
  1881. // c["Outcome"] = "Settled";
  1882. //else if (new string[] { "closed declined", "open declined (pending mgt review)" }.Contains(status))
  1883. // c["Outcome"] = "Denied";
  1884. //else if (new string[] { "closed withdrawn" }.Contains(status))
  1885. // c["Outcome"] = "Withdrawn";
  1886. //else if (new string[] { "closed" }.Contains(status))
  1887. // c["Outcome"] = "No Further Action";
  1888. //if (c["Outcome"] != DBNull.Value)
  1889. // c["Outcome_Date"] = ConvertDateTime(s["Date_Closed"], claimNumber);
  1890.  
  1891. // Add
  1892. if (operation == Operation.Insert)
  1893. claim.Rows.Add(c);
  1894.  
  1895. // Log
  1896. Log((operation == Operation.Update ? "Updated " : "") + "Claim (" + claimID + "): " + claimNumber);
  1897. if (operation == Operation.Insert)
  1898. ++newClaimCount;
  1899. else
  1900. ++updatedClaimCount;
  1901. ++i;
  1902. }
  1903.  
  1904. // Insert
  1905. Log("Applying to database...");
  1906. claimAdapter.InsertCommand.CommandTimeout = 3000;
  1907. claimAdapter.UpdateCommand.CommandTimeout = 3000;
  1908. claimAdapter.Update(claim);
  1909.  
  1910. // Delete duplicates
  1911. Log("Culling duplicates...");
  1912. OleDbCommand command = new OleDbCommand("delete from S_Acc_Claim where Claim_No in (select Claim_Reference_Num from H_Claims where Imported = 'T' and Claim_Team_Name = '" + TeamName() + "')", connection);
  1913. command.ExecuteNonQuery();
  1914.  
  1915. // Close connection
  1916. connection.Close();
  1917.  
  1918. // Summary
  1919. IssueTracker.LogSummary(TeamName() + ": Importing Access claims (" + sTime + ")");
  1920.  
  1921. // Missing
  1922. LogMissingSummary(TeamName() + ": Importing Access claims (" + sTime + ")");
  1923.  
  1924. // Summary
  1925. Log("\n--------------------------------------------------------------------------------");
  1926. Log("RESULTS (" + TeamName() + ": Importing Access claims (" + sTime + ")):");
  1927. Log(" " + newClaimCount + " claims added.");
  1928. Log(" " + updatedClaimCount + " claims updated.\n");
  1929.  
  1930. // Whitespace
  1931. Log("\n");
  1932. }
  1933.  
  1934. //--------------------------------------------------------------------------------
  1935. private static void ImportAccessClaims_OLD() {
  1936. // DEV
  1937. //Log("DEV: Deleting imported claims to keep S_ACC_CLAIM populated");
  1938. //DeleteImportedData(true);
  1939.  
  1940. // Log
  1941. Log("\n================================================================================");
  1942. Log("Importing Access claims...");
  1943. Log("================================================================================");
  1944. Log("Team: " + TeamName());
  1945.  
  1946. // Connection
  1947. OleDbConnection connection = new OleDbConnection(sInforConnectionString);
  1948. connection.Open();
  1949.  
  1950. // Delete duplicates
  1951. Log("Culling duplicates...");
  1952. OleDbCommand command = new OleDbCommand("delete from S_Acc_Claim where Claim_No in (select Claim_Reference_Num from H_Claims)", connection);
  1953. command.ExecuteNonQuery();
  1954.  
  1955. // Adapters
  1956. Log("Retrieving imported claim data...");
  1957. OleDbDataAdapter stagingAdapter = new OleDbDataAdapter("select * from S_Acc_Claim", sInforConnectionString);
  1958. OleDbDataAdapter claimAdapter = new OleDbDataAdapter("select * from H_Claims where 1=0", sInforConnectionString);
  1959. OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(claimAdapter);
  1960. claimAdapter.InsertCommand = commandBuilder.GetInsertCommand(true);
  1961. claimAdapter.UpdateCommand = commandBuilder.GetUpdateCommand(true);
  1962.  
  1963. // Dataset
  1964. DataSet dataSet = new DataSet();
  1965. stagingAdapter.Fill(dataSet, "S_Acc_Claim");
  1966. claimAdapter.Fill(dataSet, "H_Claims");
  1967.  
  1968. // Convert
  1969. DataTable accessClaim = dataSet.Tables["S_Acc_Claim"];
  1970. DataTable claim = dataSet.Tables["H_Claims"];
  1971. int i = 1;
  1972.  
  1973. foreach (DataRow s in accessClaim.Rows) {
  1974. // Claim number
  1975. object claimNumber = ClaimNumber(s["Claim_No"]);
  1976.  
  1977. // Incident type
  1978. string incidentType = null;
  1979.  
  1980. // Claim type
  1981. object claimTypeObject = Truncate(200, ConvertDDField(s, "DD_Claim_Type", "Claim_Type", new string[] { "Claim", "Notification" }));
  1982. string claimType = "";
  1983. if (claimTypeObject != DBNull.Value)
  1984. claimType = ((string)claimTypeObject).ToLower();
  1985. if (!new string[] { "claim", "notification" }.Contains(claimType)) {
  1986. if ((sTeam == Team.RisksmartGCC) && claimType.Contains("motor vehicle")) {
  1987. claimType = "claim";
  1988. incidentType = "Recovery - Motor Vehicle Impact/ Accident";
  1989. }
  1990. else
  1991. claimType = "unknown";
  1992. }
  1993.  
  1994. claimType = claimType.First().ToString().ToUpper() + claimType.Substring(1);
  1995.  
  1996. // Status
  1997. object statusObject = Truncate(200, ConvertDDField(s, "DD_Claim_Status", "Claim_Status"));
  1998. string status = "";
  1999. if (statusObject != DBNull.Value)
  2000. status = ((string)statusObject).ToLower();
  2001.  
  2002. // Open or notification
  2003. bool isOpen = status.Contains("open");
  2004. bool isOpenOrNotification = isOpen || (new string[] { "Claim", "Notification" }.Contains(claimType));
  2005.  
  2006. // Team check
  2007. string clientGroup = (s["DD_Client_Group"] != DBNull.Value ? ((string)s["DD_Client_Group"]).ToLower() : "");
  2008. if (sTeam == Team.RisksmartGCC) {
  2009. if (!(new string[] { "aldi", "ezko property services (aust) retail pty ltd", "ezko property services (aust) pty ltd", "whirlpool", "millers liability",
  2010. "folkestone limited", " hydro flow pty ltd", "hydro flow pty ltd" }).Contains(clientGroup))
  2011. {
  2012. continue; // Skip
  2013. }
  2014. }
  2015. else if (sTeam == Team.RisksmartProperty) {
  2016. if (!(new string[] { "chu strata", /*"honan lloyds facility",*/ "honan residential landlords property" }).Contains(clientGroup))
  2017. continue; // Skip
  2018. }
  2019.  
  2020. // Validation
  2021. if (claimNumber == DBNull.Value) {
  2022. LogError("Missing claim field: 'claim_no'");
  2023. continue;
  2024. }
  2025. if ((s["DD_Claim_Type"] == DBNull.Value) && (s["Claim_Type"] == DBNull.Value)) {
  2026. //LogError("Missing claim field (" + claimNumber + "): 'claim_type'");
  2027. //continue;
  2028. LogError("Missing claim field (" + claimNumber + "): 'claim_type'");
  2029. IssueTracker.AddError(IssueTracker.Issue.MISSING_CLAIM_TYPE, (string)claimNumber);
  2030. continue;
  2031. }
  2032. else if (((s["DD_Claim_Type"] == DBNull.Value) || (!new string[] { "claim", "notification" }.Contains(((string)s["DD_Claim_Type"]).ToLower()))) &&
  2033. ((s["Claim_Type"] == DBNull.Value) || (!new string[] { "claim", "notification" }.Contains(((string)s["Claim_Type"]).ToLower()))))
  2034. {
  2035. //LogError("Invalid claim type (" + claimNumber + "): '" + s["DD_Claim_Type"] + "' / '" + s["Claim_Type"] + "'");
  2036. //continue;
  2037. LogWarning("Invalid claim type (" + claimNumber + "): '" + s["DD_Claim_Type"] + "' / '" + s["Claim_Type"] + "'");
  2038. IssueTracker.AddWarning(IssueTracker.Issue.INVALID_CLAIM_TYPE, (string)claimNumber, s["DD_Claim_Type"] + "', '" + s["Claim_Type"] + "'");
  2039. }
  2040. /*if ((s["Policy_Number"] == DBNull.Value) || ((string)s["Policy_Number"]).ToLower().Contains("xx") || ((string)s["Policy_Number"]).ToLower().Contains("tba")) {
  2041. LogError("Missing claim field (" + claimNumber + "): 'policy_number'");
  2042. continue;
  2043. }*/
  2044. //if ((s["DD_Insured"] == DBNull.Value) && (s["Insured"] == DBNull.Value)) {
  2045. // LogError("Missing claim field (" + claimNumber + "): 'dd_unsured/insured'");
  2046. // continue;
  2047. //}
  2048. //if (s["Date_Reported"] == DBNull.Value) {
  2049. // LogError("Missing claim field (" + claimNumber + "): 'date_reported'");
  2050. // continue;
  2051. //}
  2052. //if ((sTeam == Team.RisksmartGCC) && (s["Incident_Name"] == DBNull.Value)) {
  2053. // LogError("Missing claim field (" + claimNumber + "): 'incident_name'");
  2054. // continue;
  2055. //}
  2056. //if (s["Incident_Date"] == DBNull.Value) {
  2057. // LogError("Missing claim field (" + claimNumber + "): 'incident_date'");
  2058. // continue;
  2059. //}
  2060.  
  2061. // New row
  2062. DataRow c = claim.NewRow();
  2063.  
  2064. // SLX
  2065. object claimID = Infor.IDFor("H_Claims", connection);
  2066. c["H_ClaimsID"] = claimID;
  2067. c["CreateUser"] = "ADMIN";
  2068. //c["CreateDate"] = DateTime.UtcNow.ToString("s", System.Globalization.CultureInfo.InvariantCulture);
  2069. c["CreateDate"] = ConvertDateTime(s["Date_Registered"], claimNumber);
  2070. c["ModifyUser"] = "ADMIN";
  2071. c["ModifyDate"] = c["CreateDate"];
  2072.  
  2073. // Claim
  2074. c["Imported"] = "T";
  2075. c["Claim_Reference_Num"] = Truncate(20, claimNumber);
  2076. //c["Claim_Type"] = Truncate(200, ConvertDDField(s, "DD_Claim_Type", "Claim_Type", new string[] { "Claim", "Notification" }));
  2077. c["Claim_Type"] = claimType;
  2078. c["Claim_Status"] = statusObject;
  2079. c["Reported_Date"] = ConvertDateTime(s["Date_Reported"], claimNumber);
  2080. c["Reported_Time"] = ConvertDateTime(s["Date_Reported"], claimNumber);
  2081. c["Client_Group"] = Truncate(400, s["DD_Client_Group"]);
  2082.  
  2083. // Team
  2084. c["Claim_Team_Name"] = TeamName();
  2085. switch (sTeam) {
  2086. case Team.RisksmartGCC: c["Claim_Team"] = Infor.GetField("SeccodeID", "Seccode", "SeccodeTYpe = 'G' and SeccodeDesc = 'Risksmart GCC Team'", connection); break;
  2087. case Team.RisksmartProperty: c["Claim_Team"] = Infor.GetField("SeccodeID", "Seccode", "SeccodeTYpe = 'G' and SeccodeDesc = 'Risksmart Property Team'", connection); break;
  2088. }
  2089. c["SeccodeID"] = c["Claim_Team"];
  2090.  
  2091. // Policy
  2092. c["Policy_No_Name"] = PolicyNumber(Truncate(200, s["Policy_Number"]));
  2093. c["Imported_Policy_Number"] = c["Policy_No_Name"];
  2094. if (/*isOpenOrNotification && */(c["Policy_No_Name"] != DBNull.Value)) {
  2095. c["Policy_No"] = Infor.GetField("H_PolicyID", "H_Policy", "Policy_Number = '" + ((string)c["Policy_No_Name"]).Replace("'", "''") + "'", connection) ?? DBNull.Value;
  2096. if (c["Policy_No"] == DBNull.Value) {
  2097. LogWarning("Policy not found (" + claimNumber + "): '" + c["Policy_No_Name"] + "'");
  2098. IssueTracker.AddWarning(IssueTracker.Issue.POLICY_NOT_FOUND, (string)claimNumber, c["Policy_No_Name"].ToString());
  2099. AddMissingPolicy((string)c["Policy_No_Name"], isOpen);
  2100. }
  2101. }
  2102. c["Binder"] = Truncate(200, s["Binder_Number"]);
  2103. c["Policy_Section"] = Truncate(400, ConvertDDField(s, "DD_Policy_Section", "Segment"));
  2104.  
  2105. // Account
  2106. c["Account_Name"] = AccountName(Truncate(200, ConvertDDField(s, "DD_Insured", "Insured"))) ?? DBNull.Value;
  2107. c["Imported_Account_Name"] = c["Account_Name"];
  2108. if (/*isOpenOrNotification && */(c["Account_Name"] != DBNull.Value)) {
  2109. c["AccountID"] = Infor.GetField("AccountID", "Account", "Type = 'Customer' and (Account = '" + ((string)c["Account_Name"]).Replace("'", "''") + "' or Client_Code = '" + c["Account_Name"] +
  2110. "' or ExternalAccountNo = '" + c["Account_Name"] + "')", connection) ?? DBNull.Value;
  2111. if (c["AccountID"] != DBNull.Value)
  2112. c["Account_Name"] = Infor.GetField("Account", "Account", "AccountID = '" + c["AccountID"] + "'", connection);
  2113. else {
  2114. LogWarning("Account not found (" + claimNumber + "): '" + c["Account_Name"] + "'");
  2115. IssueTracker.AddWarning(IssueTracker.Issue.CUSTOMER_NOT_FOUND, (string)claimNumber, c["Account_Name"].ToString());
  2116. AddMissingCustomer((string)c["Account_Name"], isOpen);
  2117. }
  2118. }
  2119.  
  2120. // Insurance
  2121. c["Insurer_Name"] = AccountName(Truncate(200, ConvertDDField(s, "DD_Insurer", "Insurer"))) ?? DBNull.Value;
  2122. c["Imported_Insurer_Name"] = c["Insurer_Name"];
  2123. if (/*isOpenOrNotification && */(c["Insurer_Name"] != DBNull.Value)) {
  2124. c["Insurer"] = Infor.GetField("AccountID", "Account", "Type = 'Underwriter' and (Account = '" + ((string)c["Insurer_Name"]).Replace("'", "''") + "' or Client_Code = '" + c["Insurer_Name"] +
  2125. "' or ExternalAccountNo = '" + c["Insurer_Name"] + "')", connection) ?? DBNull.Value;
  2126. if (c["Insurer"] != DBNull.Value)
  2127. c["Insurer_Name"] = Infor.GetField("Account", "Account", "AccountID = '" + c["Insurer"] + "'", connection);
  2128. else {
  2129. LogWarning("Insurer not found (" + claimNumber + "): '" + c["Insurer_Name"] + "'");
  2130. IssueTracker.AddWarning(IssueTracker.Issue.UNDERWRITER_NOT_FOUND, (string)claimNumber, c["Insurer_Name"].ToString());
  2131. AddMissingUnderwriter((string)c["Insurer_Name"], isOpen);
  2132. }
  2133. }
  2134. c["Insured_Name"] = Truncate(200, ConvertDDField(s, "DD_Insured", "Insured"));
  2135. c["Insurer_Ref"] = Truncate(200, s["Insurer_Reference"]);
  2136.  
  2137. // Broker
  2138. c["Broker_Company_Name"] = AccountName(Truncate(200, ConvertDDField(s, "DD_Broker", "Broker"))) ?? DBNull.Value;
  2139. c["Imported_Broker_Name"] = c["Broker_Company_Name"];
  2140. if (/*isOpenOrNotification && */(c["Broker_Company_Name"] != DBNull.Value)) {
  2141. c["Broker_Company"] = Infor.GetField("AccountID", "Account", "Type = 'Associate' and (Account = '" + ((string)c["Broker_Company_Name"]).Replace("'", "''") + "' or Client_Code = '" + c["Broker_Company_Name"] +
  2142. "' or ExternalAccountNo = '" + c["Broker_Company_Name"] + "')", connection) ?? DBNull.Value;
  2143. if (c["Broker_Company"] != DBNull.Value)
  2144. c["Broker_Company_Name"] = Infor.GetField("Account", "Account", "AccountID = '" + c["Broker_Company"] + "'", connection);
  2145. else {
  2146. LogWarning("Broker company not found (" + claimNumber + "): '" + c["Broker_Company_Name"] + "'");
  2147. IssueTracker.AddWarning(IssueTracker.Issue.ASSOCIATE_NOT_FOUND, (string)claimNumber, c["Broker_Company_Name"].ToString());
  2148. AddMissingAssociate((string)c["Broker_Company_Name"], isOpen);
  2149. }
  2150. }
  2151.  
  2152. // Assigned user
  2153. if (s["Claim_Operator"] != DBNull.Value) {
  2154. string username = (string)UserName(s["Claim_Operator"]);
  2155. c["Assigned_User"] = Infor.GetField("UserID", "UserInfo", "Username = '" + username + "'", connection) ?? DBNull.Value;
  2156. if (c["Assigned_User"] == DBNull.Value) {
  2157. // Log
  2158. LogWarning("Assigned user not found: '" + username + "'");
  2159. IssueTracker.AddWarning(IssueTracker.Issue.ASSIGNED_USER_NOT_FOUND, (string)claimNumber, username);
  2160. AddMissingUser(username, isOpen);
  2161.  
  2162. // Fallback user
  2163. c["Assigned_User"] = Infor.GetField("UserID", "UserInfo", "Username = 'Ms Megan Peacock'", connection) ?? DBNull.Value;
  2164. }
  2165. }
  2166. c["Assigned_Date"] = ConvertDateTime(s["Date_Registered"], claimNumber);
  2167.  
  2168. // Claimant
  2169. c["Claimant_First_Name"] = Truncate(200, ConvertFirstName(s["Claimant_1"]));
  2170. c["Claimant_Last_Name"] = Truncate(200, ConvertLastName(s["Claimant_1"]));
  2171. c["Claimant_Address"] = Truncate(400, JoinStringFields(s["Claimant_Address"], s["Claimant_1_State"], ", "));
  2172. c["Date_Of_Birth"] = Truncate(32, s["Date_Of_Birth"]);
  2173. c["Claimant_Work_Phone"] = Truncate(200, ConvertWorkPhone(s["Contact_No"]));
  2174. c["Claimant_Mobile_Phone"] = Truncate(200, ConvertMobilePhone(s["Contact_No"]));
  2175. c["Email_Address"] = Truncate(200, s["Email_Address"]);
  2176.  
  2177. // Incident
  2178. c["Incident_Name"] = Truncate(400, s["Incident_Name"]);
  2179. object incidentAddress = JoinStringFields(s["DD_Incident_Location_Address"], s["DD_Incident_Location_Suburb"], ", ");
  2180. incidentAddress = JoinStringFields(incidentAddress, s["DD_Incident_Location_State"], ", ");
  2181. incidentAddress = JoinStringFields(incidentAddress, s["DD_Incident_Location_Postcode"], ", ");
  2182. c["Incident_Address"] = Truncate(400, incidentAddress);
  2183. c["Incident_Date"] = ConvertDateTime(s["Incident_Date"], claimNumber);
  2184. c["Incident_Time"] = ConvertDateTime(s["Incident_Date"], claimNumber);
  2185. c["Incident_Category"] = Truncate(400, ConvertDDField(s, "DD_Incident_Category", "Incident_Category"));
  2186. c["Incident_Summary"] = s["Incident_Description"];
  2187. if (incidentType != null)
  2188. c["Incident_Type"] = incidentType;
  2189. c["Juristiction"] = Truncate(32, ConvertDDField(s, "DD_Jurisdiction", "Jurisdiction"));
  2190. c["Bodily_Location"] = Truncate(200, ConvertDDField(s, "DD_Bodily_Location", "Bodily_Location"));
  2191.  
  2192. // Store
  2193. c["StoreID_Name"] = Truncate(200, ConvertDDField(s, "DD_Incident_Location_Code", "Incident_Location"));
  2194. if ((c["StoreID_Name"] != null) && (c["StoreID_Name"] != DBNull.Value))
  2195. c["Region"] = Truncate(200, RegionFromStore((string)c["StoreID_Name"], (string)claimNumber));
  2196.  
  2197. // Trading name
  2198. //c["Trading_Name"] = s["Trading"]
  2199.  
  2200. // Property
  2201. //c["Property_Address_1"] = Truncate(200, s["DD_Incident_Location_Address"]);
  2202. c["Property_Address_1"] = Truncate(200, JoinStringFields(s["Claimant_Address"], s["Claimant_1_State"], ", "));
  2203. c["Property_Suburb"] = Truncate(200, s["DD_Incident_Location_Suburb"]);
  2204. c["Property_State"] = Truncate(32, s["DD_Incident_Location_State"]);
  2205. c["Property_Postalcode"] = Truncate(32, s["DD_Incident_Location_Postcode"]);
  2206. if (sTeam == Team.RisksmartProperty)
  2207. c["Loss_Details"] = s["Incident_Description"];
  2208.  
  2209. // Other
  2210. c["Date_Of_Loss"] = ConvertDateTime(s["Incident_Date"], claimNumber);
  2211. //c["Are_You_GST_Registered"] = ; // Claim doesn't have a gst property - only financial t records do
  2212.  
  2213. // Reserve
  2214. c["Liability_Res_Source"] = s["Reserve"];
  2215.  
  2216. // State
  2217. //c["Review"] = (ConvertDateTime(s["Date_Reviewed"], claimNumber) != DBNull.Value ? "T" : "F");
  2218. //c["Review_Date"] = ConvertDateTime(s["Date_Reviewed"], claimNumber);
  2219. c["Claim_Closed"] = (ConvertDateTime(s["Date_Closed"], claimNumber) != DBNull.Value ? "T" : "F");
  2220. c["Claim_Closed_Date"] = ConvertDateTime(s["Date_Closed"], claimNumber);
  2221.  
  2222. // E-mail on 09/10/2017 - RE: Important Task - Data Check - Risksmart GCC Imported Claims Data
  2223. // Decision was to remove inferring value of these fields.
  2224. //c["Outcome_Settlement"] = (new string[] { "closed paid", "open pending settlement" }.Contains(status)) ? "T" : "F";
  2225. //c["Outcome_Declined"] = (new string[] { "closed declined", "open declined (pending mgt review)" }.Contains(status)) ? "T" : "F";
  2226.  
  2227. //if (new string[] { "closed paid", "open pending settlement" }.Contains(status))
  2228. // c["Outcome"] = "Settled";
  2229. //else if (new string[] { "closed declined", "open declined (pending mgt review)" }.Contains(status))
  2230. // c["Outcome"] = "Denied";
  2231. //else if (new string[] { "closed withdrawn" }.Contains(status))
  2232. // c["Outcome"] = "Withdrawn";
  2233. //else if (new string[] { "closed" }.Contains(status))
  2234. // c["Outcome"] = "No Further Action";
  2235. //if (c["Outcome"] != DBNull.Value)
  2236. // c["Outcome_Date"] = ConvertDateTime(s["Date_Closed"], claimNumber);
  2237.  
  2238. // Add
  2239. claim.Rows.Add(c);
  2240. Log("Claim (" + claimID + "): " + claimNumber);
  2241. ++i;
  2242. }
  2243.  
  2244. // Insert
  2245. Log("Applying to database...");
  2246. claimAdapter.InsertCommand.CommandTimeout = 1800;
  2247. claimAdapter.Update(claim);
  2248.  
  2249. // Close connection
  2250. connection.Close();
  2251.  
  2252. // Summary
  2253. IssueTracker.LogSummary("");
  2254.  
  2255. // Missing
  2256. LogMissingSummary("");
  2257. }
  2258.  
  2259.  
  2260. // WINBEAT ================================================================================
  2261. //--------------------------------------------------------------------------------
  2262. private static void ImportWinBEATClaims(WinbeatLedger ledger, bool insertOnly = true) {
  2263. // Log
  2264. Log("================================================================================");
  2265. Log(TeamName() + ": Importing " + (insertOnly ? "" : "and updating ") + "WinBEAT claims (" + sTime + ")");
  2266. Log("================================================================================");
  2267.  
  2268. // Connection string
  2269. string connectionString;
  2270. switch (ledger) {
  2271. case WinbeatLedger.Melbourne: connectionString = sWBMelbourneConnectionString; break;
  2272. case WinbeatLedger.RealEstate: connectionString = sWBRealEstateConnectionString; break;
  2273. case WinbeatLedger.Underwriting: connectionString = sWBRisksmartPropertyConnectionString; break;
  2274. default:
  2275. LogError("Invalid team selected");
  2276. return;
  2277. }
  2278.  
  2279. // Connection
  2280. OleDbConnection inforConnection = new OleDbConnection(sInforConnectionString);
  2281. inforConnection.Open();
  2282.  
  2283. // Query
  2284. string query = "select _C.*, _U.CatCode as UDD_CatCode, _U.ClaimStatus as UDD_ClaimStatus, ";
  2285.  
  2286. if (ledger != WinbeatLedger.Underwriting) {
  2287. query += "_U.ClaimantName as UDD_ClaimantName, _U.ClaimNextAction as UDD_ClaimNextAction, _U.ProximateCause as UDD_ProximateCause, " +
  2288. "_U.ClaimDetail as UDD_ClaimDetail, _U.ClaimCategory as UDD_ClaimCategory, ";
  2289. }
  2290.  
  2291. if (ledger == WinbeatLedger.RealEstate)
  2292. query += "_U.GSTStatus as UDD_GSTStatus, _U.ClaimsHandler as UDD_ClaimsHandler, ";
  2293. else if (ledger == WinbeatLedger.Melbourne)
  2294. query += "_U.ClaimOfficer as UDD_ClaimOfficer, ";
  2295.  
  2296. query += "_CC.Code as Client_Code, _CC.Name as Client_Name, _CC.ABN as Client_ABN, " +
  2297. "_AM.Code as AccountManager_Code, " +
  2298. "_CCG.Abbrev as ClientGroup_Abbrev, _CCG.Code as ClientGroup_Code, " +
  2299. "_CU.Code as Underwriter_Code, _CU.Name as Underwriter_Name, " +
  2300. "_P.PolicyNumber as Policy_PolicyNumber, _P.InceptionDate as Policy_InceptionDate, _P.ExpiryDate as Policy_ExpiryDate, " +
  2301. "_PC.Abbrev as PolicyClass_Abbrev, _PC.Description as PolicyClass_Description, " +
  2302. "_PU.Code as PolicyUnderwriter_Code, " +
  2303. "_PS.Code as PolicySubInter_Code, _PS.Name as PolicySubInter_Name " +
  2304. "from Claim _C " +
  2305. "left join UDDataClaim _U on _C.ClaimID = _U.ClaimID " +
  2306. "left join Client _CC on _C.ClaimClientID = _CC.ClientID " +
  2307. "left join AccountManager _AM on _CC.AccountManagerID = _AM.AccountManagerID " +
  2308. "left join ClientGroup _CCG on _CC.ClientGroupID = _CCG.ClientGroupID " +
  2309. "left join Underwriter _CU on _C.ClaimUnderwriterID = _CU.UnderwriterID " +
  2310. "left join Policy _P on _C.PolicyID = _P.PolicyID " +
  2311. "left join Class _PC on _P.ClassID = _PC.ClassID " +
  2312. "left join Underwriter _PU on _P.UnderwriterID = _PU.UnderwriterID " +
  2313. "left join SubInter _PS on _P.SubInterID = _PS.SubInterID";
  2314.  
  2315. // Adapter
  2316. Log("Retrieving claim data...");
  2317. SqlDataAdapter winbeatAdapter = new SqlDataAdapter(query, connectionString);
  2318. OleDbDataAdapter inforAdapter = new OleDbDataAdapter("select * from H_Claims where 1=0", sInforConnectionString);
  2319. OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(inforAdapter);
  2320. inforAdapter.InsertCommand = commandBuilder.GetInsertCommand(true);
  2321. inforAdapter.UpdateCommand = commandBuilder.GetUpdateCommand(true);
  2322.  
  2323. // Dataset
  2324. DataSet dataSet = new DataSet();
  2325. winbeatAdapter.SelectCommand.CommandTimeout = 1200;
  2326. winbeatAdapter.Fill(dataSet, "Claim");
  2327. inforAdapter.SelectCommand.CommandTimeout = 1200;
  2328. inforAdapter.Fill(dataSet, "H_Claims");
  2329.  
  2330. // Tables
  2331. DataTable winbeatClaim = dataSet.Tables["Claim"];
  2332. DataTable claim = dataSet.Tables["H_Claims"];
  2333.  
  2334. // Log
  2335. Log("Importing...");
  2336.  
  2337. // Counts
  2338. int processedCount = 0;
  2339. int importedCount = 0;
  2340. int updatedCount = 0;
  2341. int duplicateCount = 0;
  2342.  
  2343. // Import
  2344. foreach (DataRow w in winbeatClaim.Rows) {
  2345. // Counts
  2346. ++processedCount;
  2347.  
  2348. // Claim number
  2349. object claimNumber = ClaimNumber(w["ClaimNumber"]);
  2350. if (claimNumber == DBNull.Value) {
  2351. LogError("Missing claim field: 'claimnumber'");
  2352. continue;
  2353. }
  2354.  
  2355. // Status
  2356. bool isOpen = true;
  2357. string status = "Open";
  2358. if ((w["DatePaidByUnderwriter"] != DBNull.Value) || (w["DateRejected"] != DBNull.Value) || (w["DateWithdrawn"] != DBNull.Value) ||
  2359. (w["DateReportOnly"] != DBNull.Value) || (w["DatePaidByClient"] != DBNull.Value) || (w["DatePaidByOther"] != DBNull.Value))
  2360. {
  2361. isOpen = false;
  2362. status = "Closed";
  2363. }
  2364.  
  2365. // WA strata
  2366. if (ledger == WinbeatLedger.Melbourne) {
  2367. string accountManagerCode = (w["AccountManager_Code"] != DBNull.Value ? ((string)w["AccountManager_Code"]).ToLower() : "");
  2368. bool isWAStrata = (accountManagerCode.Equals("wastr1") || accountManagerCode.Equals("wastr2"));
  2369. if ((sTeam == Team.PropertyClaims) && !isWAStrata)
  2370. continue;
  2371. else if ((sTeam == Team.GCCClaims) && isWAStrata)
  2372. continue;
  2373. }
  2374.  
  2375. // Duplicate check
  2376. OleDbCommand duplicateCommand = new OleDbCommand("select top 1 H_ClaimsID from H_Claims where Claim_Reference_Num = ?", inforConnection);
  2377. duplicateCommand.Parameters.AddWithValue("@Claim_Reference_Num", Truncate(20, claimNumber));
  2378. object claimID = duplicateCommand.ExecuteScalar();
  2379.  
  2380. // Operation
  2381. Operation operation = (((claimID == null) || (claimID == DBNull.Value)) ? Operation.Insert : Operation.Update);
  2382.  
  2383. // Clear
  2384. claim.Rows.Clear(); // Important, otherwise there may be leftover claims from the below retrieval of existing claim(s)
  2385.  
  2386. // Claim row
  2387. DataRow c = null;
  2388. if (operation == Operation.Insert)
  2389. c = claim.NewRow();
  2390. else if ((operation == Operation.Update) && !insertOnly) {
  2391. // Existing
  2392. OleDbDataAdapter existingAdapter = new OleDbDataAdapter("select * from H_Claims where Claim_Reference_Num = '" + claimNumber + "' and Imported = 'T'", sInforConnectionString);
  2393. DataSet existingDataset = new DataSet();
  2394. existingAdapter.Fill(existingDataset, "H_Claims");
  2395. claim = existingDataset.Tables["H_Claims"];
  2396.  
  2397. // Row
  2398. DataRow[] claims = claim.Select("Claim_Reference_Num = '" + claimNumber + "'");
  2399. if (claims.Length > 0) {
  2400. if (claims.Length > 1) {
  2401. LogWarning("Claim found more than once: '" + claimNumber + "'");
  2402. IssueTracker.AddWarning(IssueTracker.Issue.MULTIPLE_CLAIMS_FOUND, (string)claimNumber);
  2403. }
  2404. c = claims[0];
  2405. }
  2406. }
  2407.  
  2408. // Claim no longer found
  2409. if (c == null) {
  2410. if (!insertOnly) {
  2411. LogError("Claim not in data set: '" + claimNumber + "'");
  2412. IssueTracker.AddError(IssueTracker.Issue.CLAIM_NOT_IN_DATA_SET, (string)claimNumber);
  2413. }
  2414. continue;
  2415. }
  2416.  
  2417. // Claim
  2418. if ((operation == Operation.Insert) || !insertOnly) {
  2419. // SLX
  2420. claimID = ((operation == Operation.Insert) ? Infor.IDFor("H_Claims", inforConnection) : c["H_ClaimsID"]);
  2421. c["H_ClaimsID"] = claimID;
  2422. c["CreateUser"] = "ADMIN";
  2423. if (operation == Operation.Insert)
  2424. c["CreateDate"] = DateTime.UtcNow.ToString("s", System.Globalization.CultureInfo.InvariantCulture);
  2425. c["ModifyUser"] = "ADMIN";
  2426. c["ModifyDate"] = ((operation == Operation.Insert) ? c["CreateDate"] : DateTime.UtcNow.ToString("s", System.Globalization.CultureInfo.InvariantCulture));
  2427.  
  2428. // Claim
  2429. c["Imported"] = "T";
  2430. c["Claim_Reference_Num"] = Truncate(20, claimNumber);
  2431. c["Claim_Type"] = "Claim";
  2432. c["Claim_Status"] = status;
  2433. if (ledger != WinbeatLedger.Underwriting)
  2434. c["Claim_Stage"] = Truncate(200, w["UDD_ClaimNextAction"]);
  2435. c["Reported_Date"] = ConvertDateTime(w["DateBrokerAdvised"], claimNumber);
  2436. if (c["Reported_Date"] == DBNull.Value)
  2437. c["Reported_Date"] = ConvertDateTime(w["DateOfLoss"], claimNumber);
  2438. c["Reported_Time"] = ConvertDateTime(w["DateBrokerAdvised"], claimNumber);
  2439. c["Client_Group"] = Truncate(400, w["ClientGroup_Abbrev"]);
  2440. c["Claim_Detail"] = Truncate(400, w["Description"]);
  2441.  
  2442. // Team
  2443. c["Claim_Team_Name"] = TeamName();
  2444. switch (sTeam) {
  2445. case Team.RisksmartProperty: c["Claim_Team"] = Infor.GetField("SeccodeID", "Seccode", "SeccodeTYpe = 'G' and SeccodeDesc = 'Risksmart Property Team'", inforConnection); break;
  2446. case Team.PropertyClaims: c["Claim_Team"] = Infor.GetField("SeccodeID", "Seccode", "SeccodeTYpe = 'G' and SeccodeDesc = 'Property Claims Team'", inforConnection); break;
  2447. case Team.GCCClaims: c["Claim_Team"] = Infor.GetField("SeccodeID", "Seccode", "SeccodeTYpe = 'G' and SeccodeDesc = 'GCC Claim Team'", inforConnection); break;
  2448. }
  2449. c["SeccodeID"] = c["Claim_Team"];
  2450.  
  2451. // Policy
  2452. c["Policy_No_Name"] = PolicyNumber(Truncate(200, w["Policy_PolicyNumber"]));
  2453. c["Imported_Policy_Number"] = c["Policy_No_Name"];
  2454. c["Policy_No"] = DBNull.Value;
  2455. if (c["Policy_No_Name"] != DBNull.Value) {
  2456. c["Policy_No"] = Infor.GetField("H_PolicyID", "H_Policy", "Policy_Number = '" + ((string)c["Policy_No_Name"]).Replace("'", "''") + "'", inforConnection) ?? DBNull.Value;
  2457. if (c["Policy_No"] == DBNull.Value) {
  2458. LogWarning("Policy not found (" + claimNumber + "): '" + c["Policy_No_Name"] + "'");
  2459. IssueTracker.AddWarning(IssueTracker.Issue.POLICY_NOT_FOUND, (string)claimNumber, c["Policy_No_Name"].ToString());
  2460. AddMissingPolicy((string)c["Policy_No_Name"], isOpen);
  2461. }
  2462. }
  2463. //c["Policy_Section"] = Truncate(400, w["PolicyClass_Description"]);
  2464.  
  2465. // Account
  2466. c["Account_Name"] = w["Client_Name"];
  2467. object accountCode = CustomerCode(w["Client_Code"]);
  2468. c["Imported_Account_Code"] = Truncate(32, accountCode);
  2469. c["Imported_Account_Name"] = c["Account_Name"];
  2470. if (accountCode != DBNull.Value) {
  2471. object accountCodeArg = ((string)accountCode).Replace("'", "''");
  2472. c["AccountID"] = Infor.GetField("AccountID", "Account", "Type = 'Customer' and (Client_Code = '" + accountCodeArg +
  2473. "' or ExternalAccountNo = '" + accountCodeArg + "')", inforConnection) ?? DBNull.Value;
  2474. if (c["AccountID"] != DBNull.Value)
  2475. c["Account_Name"] = Infor.GetField("Account", "Account", "AccountID = '" + c["AccountID"] + "'", inforConnection);
  2476. else {
  2477. LogWarning("Account not found (" + claimNumber + "): '" + w["Client_Code"] + "'");
  2478. IssueTracker.AddWarning(IssueTracker.Issue.CUSTOMER_NOT_FOUND, (string)claimNumber, w["Client_Code"].ToString());
  2479. AddMissingCustomer((string)w["Client_Code"], isOpen);
  2480. }
  2481. }
  2482.  
  2483. c["Account_Period_From"] = w["Policy_InceptionDate"];
  2484. c["Account_Period_To"] = w["Policy_ExpiryDate"];
  2485.  
  2486. // Insurance
  2487. c["Insurer_Name"] = w["Underwriter_Name"];
  2488. object insurerCode = UnderwriterCode(w["Underwriter_Code"]);
  2489. c["Imported_Insurer_Code"] = Truncate(32, insurerCode);
  2490. c["Imported_Insurer_Name"] = c["Insurer_Name"];
  2491. if (insurerCode != DBNull.Value) {
  2492. object insurerCodeArg = ((string)insurerCode).Replace("'", "''");
  2493. c["Insurer"] = Infor.GetField("AccountID", "Account", "Type = 'Underwriter' and (Client_Code = '" + insurerCodeArg +
  2494. "' or ExternalAccountNo = '" + insurerCodeArg + "')", inforConnection) ?? DBNull.Value;
  2495. if (c["Insurer"] != DBNull.Value)
  2496. c["Insurer_Name"] = Infor.GetField("Account", "Account", "AccountID = '" + c["Insurer"] + "'", inforConnection);
  2497. else {
  2498. LogWarning("Insurer not found (" + claimNumber + "): '" + w["Underwriter_Code"] + "'");
  2499. IssueTracker.AddWarning(IssueTracker.Issue.UNDERWRITER_NOT_FOUND, (string)claimNumber, w["Underwriter_Code"].ToString());
  2500. AddMissingUnderwriter((string)w["Underwriter_Code"], isOpen);
  2501. }
  2502. }
  2503. c["Insured_Name"] = c["Account_Name"]; // Is this really correct?
  2504. c["Insurer_Ref"] = Truncate(200, w["UnderwriterClaimNumber"]);
  2505.  
  2506. // Broker
  2507. c["Broker_Company_Name"] = w["PolicySubInter_Name"];
  2508. object brokerCode = AssociateCode(w["PolicySubInter_Code"]);
  2509. c["Imported_Broker_Code"] = Truncate(32, brokerCode);
  2510. c["Imported_Broker_Name"] = c["Broker_Company_Name"];
  2511. if (brokerCode != DBNull.Value) {
  2512. object brokerCodeArg = ((string)brokerCode).Replace("'", "''");
  2513. c["Broker_Company"] = Infor.GetField("AccountID", "Account", "Type = 'Associate' and (Client_Code = '" + brokerCodeArg +
  2514. "' or ExternalAccountNo = '" + brokerCodeArg + "')", inforConnection) ?? DBNull.Value;
  2515. if (c["Broker_Company"] != DBNull.Value)
  2516. c["Broker_Company_Name"] = Infor.GetField("Account", "Account", "AccountID = '" + c["Broker_Company"] + "'", inforConnection);
  2517. else {
  2518. LogWarning("Broker company not found (" + claimNumber + "): '" + w["PolicySubInter_Code"] + "'");
  2519. IssueTracker.AddWarning(IssueTracker.Issue.ASSOCIATE_NOT_FOUND, (string)claimNumber, w["PolicySubInter_Code"].ToString());
  2520. AddMissingAssociate((string)c["Broker_Company_Name"], isOpen);
  2521. }
  2522. }
  2523.  
  2524. // Assigned user
  2525. c["Assigned_User"] = DBNull.Value;
  2526. string username = null;
  2527. if (ledger == WinbeatLedger.RealEstate)
  2528. username = w["UDD_ClaimsHandler"] != DBNull.Value ? (string)UserName(w["UDD_ClaimsHandler"]) : null;
  2529. else if (ledger == WinbeatLedger.Melbourne)
  2530. username = w["UDD_ClaimOfficer"] != DBNull.Value ? (string)UserName(w["UDD_ClaimOfficer"]) : null;
  2531.  
  2532. if (username != null) {
  2533. c["Assigned_User"] = Infor.GetField("UserID", "UserInfo", "Username = '" + username + "'", inforConnection) ?? DBNull.Value;
  2534. if (c["Assigned_User"] == DBNull.Value) {
  2535. // Log
  2536. LogWarning("Assigned user not found: '" + username + "'");
  2537. IssueTracker.AddWarning(IssueTracker.Issue.ASSIGNED_USER_NOT_FOUND, (string)claimNumber, username);
  2538. AddMissingUser(username, isOpen);
  2539.  
  2540. // Fallback user
  2541. c["Assigned_User"] = Infor.GetField("UserID", "UserInfo", "Username = 'Ms Megan Peacock'", inforConnection) ?? DBNull.Value;
  2542. }
  2543. }
  2544.  
  2545. // Claimant
  2546. if (ledger != WinbeatLedger.Underwriting) {
  2547. c["Claimant_First_Name"] = Truncate(200, ConvertFirstName(w["UDD_ClaimantName"]));
  2548. c["Claimant_Last_Name"] = Truncate(200, ConvertLastName(w["UDD_ClaimantName"]));
  2549. }
  2550.  
  2551. // Incident
  2552. c["Incident_Date"] = ConvertDateTime(w["DateOfLoss"], claimNumber);
  2553. c["Incident_Time"] = ConvertDateTime(w["DateOfLoss"], claimNumber);
  2554. c["Incident_Summary"] = w["Description"];
  2555. //if (ledger == WinbeatLedger.RealEstate)
  2556. // c["Incident_Type"] = Truncate(200, w["UDD_ProximateCause"]);
  2557. //else if (ledger == WinbeatLedger.Melbourne)
  2558. // c["Incident_Type"] = Truncate(200, w["UDD_ClaimDetail"]);
  2559. c["Incident_Type"] = Truncate(200, w["PolicyClass_Description"]);
  2560. if (ledger != WinbeatLedger.Underwriting)
  2561. c["Incident_Category"] = Truncate(400, w["UDD_ProximateCause"]);
  2562.  
  2563. // Property
  2564. c["Property_Claim_Ref"] = Truncate(200, claimNumber);
  2565. if (c["Policy_No"] != DBNull.Value) {
  2566. string policyNumber = ((string)c["Policy_No"]).Replace("'", "''");
  2567. object streetNumber = Infor.GetField("Situation_Street_Number", "H_Policy", "Policy_Number = '" + policyNumber + "'", inforConnection) ?? DBNull.Value;
  2568. object street = Infor.GetField("Situation_Street", "H_Policy", "Policy_Number = '" + policyNumber + "'", inforConnection) ?? DBNull.Value;
  2569. if ((streetNumber != DBNull.Value) || (street != DBNull.Value))
  2570. c["Property_Address_1"] = Truncate(200, (streetNumber != DBNull.Value ? (string)streetNumber + " " : "") + (street != DBNull.Value ? (string)street : ""));
  2571. c["Property_Suburb"] = Truncate(200, Infor.GetField("Situation_Suburb", "H_Policy", "Policy_Number = '" + policyNumber + "'", inforConnection));
  2572. c["Property_State"] = Truncate(32, Infor.GetField("Situation_State", "H_Policy", "Policy_Number = '" + policyNumber + "'", inforConnection));
  2573. c["Property_Postalcode"] = Truncate(32, Infor.GetField("Situation_Postcode", "H_Policy", "Policy_Number = '" + policyNumber + "'", inforConnection));
  2574. }
  2575. c["Account_Manager_Property"] = Truncate(200, w["AccountManager_Code"]);
  2576.  
  2577. // Misc
  2578. c["ABN_Num"] = Truncate(200, w["Client_ABN"]);
  2579. c["Cat_Code"] = Truncate(200, w["UDD_CatCode"]);
  2580. c["Date_Of_Loss"] = ConvertDateTime(w["DateOfLoss"], claimNumber);
  2581. if (ledger != WinbeatLedger.Underwriting) {
  2582. c["Causation"] = Truncate(400, w["UDD_ProximateCause"]);
  2583. //c["Complexity"] = Truncate(200, w["UDD_ClaimDetail"]);
  2584. c["Complexity"] = Truncate(200, w["UDD_ClaimCategory"]);
  2585. }
  2586. if (ledger == WinbeatLedger.RealEstate)
  2587. c["Are_You_GST_Registered"] = ConvertGST(w["UDD_GSTStatus"]); // Field quality is too poor
  2588.  
  2589. // Notes
  2590. if ((w["Notes"] != DBNull.Value) || (w["RejectReason"] != DBNull.Value) || (w["PaymentNotes"] != DBNull.Value)) {
  2591. string notes = "";
  2592. notes += w["Notes"] != DBNull.Value ? w["Notes"] + "\n\n" : "";
  2593. notes += w["Notes"] != DBNull.Value ? "REJECT REASON:\n" + w["RejectReason"] + "\n\n" : "";
  2594. notes += w["Notes"] != DBNull.Value ? "PAYMENT NOTES:\n" + w["PaymentNotes"] : "";
  2595. c["Claim_Note"] = notes;
  2596. }
  2597.  
  2598. // Reserve
  2599. c["Liability_Res_Source"] = w["EstAmount"];
  2600.  
  2601. // State
  2602. object uddStatusObject = w["UDD_ClaimStatus"] ?? DBNull.Value;
  2603. string uddStatus = (uddStatusObject != DBNull.Value) ? uddStatusObject.ToString().ToLower() : "";
  2604.  
  2605. c["Claim_Lodged"] = (uddStatus.Contains("lodge") && !uddStatus.Contains("not")) ? "T" : "F";
  2606. c["Claim_Not_Lodged"] = (uddStatus.Contains("lodge") && uddStatus.Contains("not")) ? "T" : "F";
  2607. c["Claim_Acknowledged"] = uddStatus.Contains("acknowl") ? "T" : "F";
  2608. bool paymentSettlement = ((w["PaymentNotes"] != DBNull.Value) && ((string)w["PaymentNotes"]).ToLower().Contains("settlement"));
  2609. c["Outcome_Settlement"] = (uddStatus.Contains("settle") || paymentSettlement) ? "T" : "F";
  2610. c["Claim_Approved"] = uddStatus.Contains("approve") ? "T" : "F";
  2611. c["Outcome_Declined"] = ((uddStatus.Contains("decline") || uddStatus.Contains("denied")) && !uddStatus.Contains("partial")) ? "T" : "F";
  2612. c["Expert_Appointed"] = (uddStatus.Contains("appoint") || uddStatus.Contains("assessor")) ? "T" : "F";
  2613. c["Indemnity_Granted"] = (uddStatus.Contains("indemnity") && !uddStatus.Contains("partial")) ? "T" : "F";
  2614. c["Partial_Indemnity_Granted"] = (uddStatus.Contains("indemnity") && uddStatus.Contains("partial")) ? "T" : "F";
  2615.  
  2616. c["Review"] = (ConvertDateTime(w["DateFollowup"], claimNumber) != DBNull.Value ? "T" : "F");
  2617. c["Review_Date"] = ConvertDateTime(w["DateFollowup"], claimNumber);
  2618.  
  2619. c["Claim_Closed"] = uddStatus.Contains("closed") ? "T" : "F";
  2620. object[] potentialClosedDates = new object[] { w["DatePaidByUnderwriter"], w["DateRejected"], w["DateWithdrawn"],
  2621. w["DateReportOnly"], w["DatePaidByClient"], w["DatePaidByOther"]};
  2622. foreach (object d in potentialClosedDates) {
  2623. object closedDate = ConvertDateTime(d, claimNumber);
  2624. if (closedDate != DBNull.Value) {
  2625. c["Claim_Closed"] = "T";
  2626. c["Claim_Closed_Date"] = closedDate;
  2627. }
  2628. }
  2629.  
  2630. // Outcome
  2631. c["Outcome"] = DBNull.Value;
  2632. if ((uddStatus.Contains("decline") || uddStatus.Contains("denied")) && !uddStatus.Contains("partial"))
  2633. c["Outcome"] = "Declined";
  2634. else if ((uddStatus.Contains("decline") || uddStatus.Contains("denied")) && uddStatus.Contains("partial"))
  2635. c["Outcome"] = "Partial Decline";
  2636. else if (uddStatus.Contains("paid"))
  2637. c["Outcome"] = "Paid";
  2638. else if (uddStatus.Contains("withdraw"))
  2639. c["Outcome"] = "Withdrawn";
  2640.  
  2641. // Insert
  2642. object duplicateClaimID = duplicateCommand.ExecuteScalar();
  2643. bool notDuplicate = (duplicateClaimID == null) || (duplicateClaimID == DBNull.Value);
  2644. if (notDuplicate || ((operation == Operation.Update) && !notDuplicate)) {
  2645. // Claim
  2646. if (operation == Operation.Insert)
  2647. claim.Rows.Add(c);
  2648. inforAdapter.Update(claim);
  2649. claim.Rows.Clear();
  2650. Log((operation == Operation.Update ? "Updated " : "") + "Claim (" + claimID + "): " + claimNumber);
  2651.  
  2652. if (operation == Operation.Insert)
  2653. ++importedCount;
  2654. else
  2655. ++updatedCount;
  2656.  
  2657. // Payment
  2658. ImportWinBEATClaims_Payment(claimID, claimNumber.ToString(), operation, w, inforConnection);
  2659. }
  2660. }
  2661. else
  2662. ++duplicateCount;
  2663.  
  2664. // Dispose
  2665. duplicateCommand.Dispose();
  2666. }
  2667.  
  2668. // Close connection
  2669. inforConnection.Close();
  2670.  
  2671. // Summary
  2672. Log("\n--------------------------------------------------------------------------------");
  2673. Log("RESULTS (" + TeamName() + ": Importing WinBEAT claims (" + sTime + ")):");
  2674. Log(" " + processedCount + " processed.");
  2675. Log(" " + importedCount + " imported.");
  2676. Log(" " + updatedCount + " updated.");
  2677. Log(" " + duplicateCount + " ignored (duplicates)\n");
  2678.  
  2679. // Summary
  2680. IssueTracker.LogSummary(TeamName() + ": Importing WinBEAT claims (" + sTime + ")");
  2681.  
  2682. // Missing
  2683. LogMissingSummary(TeamName() + ": Importing WinBEAT claims (" + sTime + ")");
  2684.  
  2685. // Whitespace
  2686. Log("\n");
  2687. }
  2688.  
  2689. //--------------------------------------------------------------------------------
  2690. private static object ImportWinBEATClaims_Payment(object claimID, string claimNumber, Operation operation, DataRow r, OleDbConnection connection) {
  2691. // Has payment
  2692. bool hasPayment = (r["AmountPaid"] != DBNull.Value) && (Single.Parse(r["AmountPaid"].ToString()) > 0.0f);
  2693. if (!hasPayment) {
  2694. if (operation == Operation.Insert)
  2695. return DBNull.Value;
  2696. else {
  2697. // Delete previously imported payment, if it exists and is unchanged
  2698. OleDbCommand deleteCommand = new OleDbCommand("delete from H_Payments where H_ClaimsID = ? and Imported = 'T' and ModifyDate = Imported_Date", connection);
  2699. deleteCommand.Parameters.AddWithValue("@ClaimID", claimID);
  2700. deleteCommand.ExecuteNonQuery();
  2701. deleteCommand.Dispose();
  2702. Log("Deleted Redundant Payment: " + claimNumber);
  2703. return DBNull.Value;
  2704. }
  2705. }
  2706.  
  2707. // ID
  2708. object paymentID = null;
  2709. if (operation == Operation.Insert)
  2710. paymentID = Infor.IDFor("H_Payments", connection);
  2711. if (operation == Operation.Update) {
  2712. // Existing
  2713. OleDbCommand existingCommand = new OleDbCommand("select top 1 H_PaymentsID from H_Payments where H_ClaimsID = ? and Imported = 'T' and ModifyDate = Imported_Date", connection);
  2714. existingCommand.Parameters.AddWithValue("@ClaimID", claimID);
  2715. paymentID = existingCommand.ExecuteScalar();
  2716. existingCommand.Dispose();
  2717.  
  2718. // Not found
  2719. if ((paymentID == null) || (paymentID == DBNull.Value)) {
  2720. LogWarning("Payment changed or missing (" + claimNumber + ")");
  2721. IssueTracker.AddWarning(IssueTracker.Issue.PAYMENT_CHANGED_OR_MISSING, (string)claimNumber);
  2722. return DBNull.Value;
  2723. }
  2724. }
  2725.  
  2726. // Settlement
  2727. bool isSettlement = ((r["PaymentNotes"] != DBNull.Value) && ((string)r["PaymentNotes"]).ToLower().Contains("settlement"));
  2728.  
  2729. // Date
  2730. string date = DateTime.UtcNow.ToString("s", System.Globalization.CultureInfo.InvariantCulture);
  2731.  
  2732. // Insert / update
  2733. OleDbCommand command;
  2734. if (operation == Operation.Insert) {
  2735. command = new OleDbCommand("insert into H_Payments (H_PaymentsID, H_ClaimsID, ModifyDate, Reserve_Type, Total_Gross, Total_Net, GST_Included, GST, Payment_Status, " +
  2736. " Payment_Type, Is_Settlement, Date_Invoice_Received, Payment_Note, Imported, Imported_Date) " +
  2737. "values (?, ?, ?, 'Liability Reserve', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", connection);
  2738. command.Parameters.AddWithValue("@PaymentID", paymentID);
  2739. }
  2740. else {
  2741. command = new OleDbCommand("update H_Payments set H_ClaimsID = ?, ModifyDate = ?, Reserve_Type = 'Liability Reserve', Total_Gross = ?, Total_Net = ?, GST_Included = ?, GST = ?, " +
  2742. " Payment_Status = ?, Payment_Type = ?, Is_Settlement = ?, Date_Invoice_Received = ?, Payment_Note = ?, Imported = ?, Imported_Date = ? " +
  2743. "where H_PaymentsID = ?", connection);
  2744. }
  2745.  
  2746. command.Parameters.AddWithValue("@ClaimID", claimID);
  2747. command.Parameters.AddWithValue("@ModifyDate", date);
  2748. command.Parameters.AddWithValue("@TotalGross", r["AmountPaid"]);
  2749. command.Parameters.AddWithValue("@TotalNet", r["AmountPaid"]);
  2750. command.Parameters.AddWithValue("@GSTIncluded", "F");
  2751. command.Parameters.AddWithValue("@GST", 0.1);
  2752. command.Parameters.AddWithValue("@PaymentStatus", "Paid");
  2753. command.Parameters.AddWithValue("@PaymentType", isSettlement ? (object)"Settlement" : DBNull.Value);
  2754. command.Parameters.AddWithValue("@IsSettlement", isSettlement ? "T" : "F");
  2755.  
  2756. object dateInvoiceReceived = DBNull.Value;
  2757. if (r["DatePaidByUnderwriter"] != DBNull.Value)
  2758. dateInvoiceReceived = r["DatePaidByUnderwriter"];
  2759. if (r["DatePaidByClient"] != DBNull.Value)
  2760. dateInvoiceReceived = r["DatePaidByClient"];
  2761. if (r["DatePaidByOther"] != DBNull.Value)
  2762. dateInvoiceReceived = r["DatePaidByOther"];
  2763. command.Parameters.AddWithValue("@DateInvoiceReceived", dateInvoiceReceived);
  2764.  
  2765. command.Parameters.AddWithValue("@PaymentNote", r["PaymentNotes"]);
  2766. command.Parameters.AddWithValue("@Imported", "T");
  2767. command.Parameters.AddWithValue("@ImportedDate", date);
  2768. if (operation == Operation.Update)
  2769. command.Parameters.AddWithValue("@PaymentID", paymentID);
  2770. command.ExecuteNonQuery();
  2771. command.Dispose();
  2772.  
  2773. // Log
  2774. Log((operation == Operation.Update ? "Updated " : "") + "Payment (" + paymentID + "): " + claimNumber + ", " + r["AmountPaid"]);
  2775.  
  2776. return paymentID;
  2777. }
  2778.  
  2779. //--------------------------------------------------------------------------------
  2780. private static void ImportWinBEATClaims_OLD(WinbeatLedger ledger) {
  2781. // Log
  2782. Log("================================================================================");
  2783. Log(TeamName() + ": Importing WinBEAT claims (" + sTime + ")");
  2784. Log("================================================================================");
  2785.  
  2786. // Connection string
  2787. string connectionString;
  2788. switch (ledger) {
  2789. case WinbeatLedger.Melbourne: connectionString = sWBMelbourneConnectionString; break;
  2790. case WinbeatLedger.RealEstate: connectionString = sWBRealEstateConnectionString; break;
  2791. case WinbeatLedger.Underwriting: connectionString = sWBRisksmartPropertyConnectionString; break;
  2792. default:
  2793. LogError("Invalid team selected");
  2794. return;
  2795. }
  2796.  
  2797. // Connection
  2798. OleDbConnection inforConnection = new OleDbConnection(sInforConnectionString);
  2799. inforConnection.Open();
  2800.  
  2801. // Query
  2802. string query = "select _C.*, _U.CatCode as UDD_CatCode, _U.ClaimStatus as UDD_ClaimStatus, ";
  2803.  
  2804. if (ledger != WinbeatLedger.Underwriting) {
  2805. query += "_U.ClaimantName as UDD_ClaimantName, _U.ClaimNextAction as UDD_ClaimNextAction, _U.ProximateCause as UDD_ProximateCause, " +
  2806. "_U.ClaimDetail as UDD_ClaimDetail, _U.ClaimCategory as UDD_ClaimCategory, ";
  2807. }
  2808.  
  2809. if (ledger == WinbeatLedger.RealEstate)
  2810. query += "_U.GSTStatus as UDD_GSTStatus, _U.ClaimsHandler as UDD_ClaimsHandler, ";
  2811. else if (ledger == WinbeatLedger.Melbourne)
  2812. query += "_U.ClaimOfficer as UDD_ClaimOfficer, ";
  2813.  
  2814. query += "_CC.Code as Client_Code, _CC.Name as Client_Name, _CC.ABN as Client_ABN, " +
  2815. "_AM.Code as AccountManager_Code, " +
  2816. "_CCG.Abbrev as ClientGroup_Abbrev, _CCG.Code as ClientGroup_Code, " +
  2817. "_CU.Code as Underwriter_Code, _CU.Name as Underwriter_Name, " +
  2818. "_P.PolicyNumber as Policy_PolicyNumber, _P.InceptionDate as Policy_InceptionDate, _P.ExpiryDate as Policy_ExpiryDate, " +
  2819. "_PC.Abbrev as PolicyClass_Abbrev, _PC.Description as PolicyClass_Description, " +
  2820. "_PU.Code as PolicyUnderwriter_Code, " +
  2821. "_PS.Code as PolicySubInter_Code, _PS.Name as PolicySubInter_Name " +
  2822. "from Claim _C " +
  2823. "left join UDDataClaim _U on _C.ClaimID = _U.ClaimID " +
  2824. "left join Client _CC on _C.ClaimClientID = _CC.ClientID " +
  2825. "left join AccountManager _AM on _CC.AccountManagerID = _AM.AccountManagerID " +
  2826. "left join ClientGroup _CCG on _CC.ClientGroupID = _CCG.ClientGroupID " +
  2827. "left join Underwriter _CU on _C.ClaimUnderwriterID = _CU.UnderwriterID " +
  2828. "left join Policy _P on _C.PolicyID = _P.PolicyID " +
  2829. "left join Class _PC on _P.ClassID = _PC.ClassID " +
  2830. "left join Underwriter _PU on _P.UnderwriterID = _PU.UnderwriterID " +
  2831. "left join SubInter _PS on _P.SubInterID = _PS.SubInterID";
  2832.  
  2833. // Adapter
  2834. Log("Retrieving claim data...");
  2835. SqlDataAdapter winbeatAdapter = new SqlDataAdapter(query, connectionString);
  2836. OleDbDataAdapter inforAdapter = new OleDbDataAdapter("select * from H_Claims where 1=0", sInforConnectionString);
  2837. OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(inforAdapter);
  2838. inforAdapter.InsertCommand = commandBuilder.GetInsertCommand(true);
  2839. inforAdapter.UpdateCommand = commandBuilder.GetUpdateCommand(true);
  2840.  
  2841. // Dataset
  2842. DataSet dataSet = new DataSet();
  2843. winbeatAdapter.SelectCommand.CommandTimeout = 1200;
  2844. winbeatAdapter.Fill(dataSet, "Claim");
  2845. inforAdapter.SelectCommand.CommandTimeout = 1200;
  2846. inforAdapter.Fill(dataSet, "H_Claims");
  2847.  
  2848. // Tables
  2849. DataTable winbeatClaim = dataSet.Tables["Claim"];
  2850. DataTable claim = dataSet.Tables["H_Claims"];
  2851.  
  2852. // Counts
  2853. int processedCount = 0;
  2854. int importedCount = 0;
  2855. int duplicateCount = 0;
  2856.  
  2857. // Import
  2858. foreach (DataRow w in winbeatClaim.Rows) {
  2859. // Counts
  2860. ++processedCount;
  2861.  
  2862. // Claim number
  2863. object claimNumber = ClaimNumber(w["ClaimNumber"]);
  2864. if (claimNumber == DBNull.Value) {
  2865. LogError("Missing claim field: 'claimnumber'");
  2866. continue;
  2867. }
  2868.  
  2869. // Status
  2870. bool isOpen = true;
  2871. string status = "Open";
  2872. if ((w["DatePaidByUnderwriter"] != DBNull.Value) || (w["DateRejected"] != DBNull.Value) || (w["DateWithdrawn"] != DBNull.Value) ||
  2873. (w["DateReportOnly"] != DBNull.Value) || (w["DatePaidByClient"] != DBNull.Value) || (w["DatePaidByOther"] != DBNull.Value))
  2874. {
  2875. isOpen = false;
  2876. status = "Closed";
  2877. }
  2878.  
  2879. // WA strata
  2880. if (ledger == WinbeatLedger.Melbourne) {
  2881. string accountManagerCode = (w["AccountManager_Code"] != DBNull.Value ? ((string)w["AccountManager_Code"]).ToLower() : "");
  2882. bool isWAStrata = (accountManagerCode.Equals("wastr1") || accountManagerCode.Equals("wastr2"));
  2883. if ((sTeam == Team.PropertyClaims) && !isWAStrata)
  2884. continue;
  2885. else if ((sTeam == Team.GCCClaims) && isWAStrata)
  2886. continue;
  2887. }
  2888.  
  2889. // Duplicate check
  2890. OleDbCommand duplicateCommand = new OleDbCommand("select top 1 H_ClaimsID from H_Claims where Claim_Reference_Num = ?", inforConnection);
  2891. duplicateCommand.Parameters.AddWithValue("@Claim_Reference_Num", Truncate(20, claimNumber));
  2892. object claimID = duplicateCommand.ExecuteScalar();
  2893.  
  2894. // Claim
  2895. if ((claimID == null) || (claimID == DBNull.Value)) {
  2896. // New row
  2897. DataRow c = claim.NewRow();
  2898.  
  2899. // SLX
  2900. claimID = Infor.IDFor("H_Claims", inforConnection);
  2901. c["H_ClaimsID"] = claimID;
  2902. c["CreateUser"] = "ADMIN";
  2903. c["CreateDate"] = DateTime.UtcNow.ToString("s", System.Globalization.CultureInfo.InvariantCulture);
  2904. c["ModifyUser"] = "ADMIN";
  2905. c["ModifyDate"] = c["CreateDate"];
  2906.  
  2907. // Claim
  2908. c["Imported"] = "T";
  2909. c["Claim_Reference_Num"] = Truncate(20, claimNumber);
  2910. c["Claim_Type"] = "Claim";
  2911. c["Claim_Status"] = status;
  2912. if (ledger != WinbeatLedger.Underwriting)
  2913. c["Claim_Stage"] = Truncate(200, w["UDD_ClaimNextAction"]);
  2914. c["Reported_Date"] = ConvertDateTime(w["DateBrokerAdvised"], claimNumber);
  2915. if (c["Reported_Date"] == DBNull.Value)
  2916. c["Reported_Date"] = ConvertDateTime(w["DateOfLoss"], claimNumber);
  2917. c["Reported_Time"] = ConvertDateTime(w["DateBrokerAdvised"], claimNumber);
  2918. c["Client_Group"] = Truncate(400, w["ClientGroup_Abbrev"]);
  2919. c["Claim_Detail"] = Truncate(400, w["Description"]);
  2920.  
  2921. // Team
  2922. c["Claim_Team_Name"] = TeamName();
  2923. switch (sTeam) {
  2924. case Team.RisksmartProperty: c["Claim_Team"] = Infor.GetField("SeccodeID", "Seccode", "SeccodeTYpe = 'G' and SeccodeDesc = 'Risksmart Property Team'", inforConnection); break;
  2925. case Team.PropertyClaims: c["Claim_Team"] = Infor.GetField("SeccodeID", "Seccode", "SeccodeTYpe = 'G' and SeccodeDesc = 'Property Claims Team'", inforConnection); break;
  2926. case Team.GCCClaims: c["Claim_Team"] = Infor.GetField("SeccodeID", "Seccode", "SeccodeTYpe = 'G' and SeccodeDesc = 'GCC Claim Team'", inforConnection); break;
  2927. }
  2928. c["SeccodeID"] = c["Claim_Team"];
  2929.  
  2930. // Policy
  2931. c["Policy_No_Name"] = PolicyNumber(Truncate(200, w["Policy_PolicyNumber"]));
  2932. c["Imported_Policy_Number"] = c["Policy_No_Name"];
  2933. c["Policy_No"] = DBNull.Value;
  2934. if (c["Policy_No_Name"] != DBNull.Value) {
  2935. c["Policy_No"] = Infor.GetField("H_PolicyID", "H_Policy", "Policy_Number = '" + ((string)c["Policy_No_Name"]).Replace("'", "''") + "'", inforConnection) ?? DBNull.Value;
  2936. if (c["Policy_No"] == DBNull.Value) {
  2937. LogWarning("Policy not found (" + claimNumber + "): '" + c["Policy_No_Name"] + "'");
  2938. IssueTracker.AddWarning(IssueTracker.Issue.POLICY_NOT_FOUND, (string)claimNumber, c["Policy_No_Name"].ToString());
  2939. AddMissingPolicy((string)c["Policy_No_Name"], isOpen);
  2940. }
  2941. }
  2942. //c["Policy_Section"] = Truncate(400, w["PolicyClass_Description"]);
  2943.  
  2944. // Account
  2945. c["Account_Name"] = w["Client_Name"];
  2946. object accountCode = CustomerCode(w["Client_Code"]);
  2947. c["Imported_Account_Code"] = Truncate(32, accountCode);
  2948. c["Imported_Account_Name"] = c["Account_Name"];
  2949. if (accountCode != DBNull.Value) {
  2950. object accountCodeArg = ((string)accountCode).Replace("'", "''");
  2951. c["AccountID"] = Infor.GetField("AccountID", "Account", "Type = 'Customer' and (Client_Code = '" + accountCodeArg +
  2952. "' or ExternalAccountNo = '" + accountCodeArg + "')", inforConnection) ?? DBNull.Value;
  2953. if (c["AccountID"] != DBNull.Value)
  2954. c["Account_Name"] = Infor.GetField("Account", "Account", "AccountID = '" + c["AccountID"] + "'", inforConnection);
  2955. else {
  2956. LogWarning("Account not found (" + claimNumber + "): '" + w["Client_Code"] + "'");
  2957. IssueTracker.AddWarning(IssueTracker.Issue.CUSTOMER_NOT_FOUND, (string)claimNumber, w["Client_Code"].ToString());
  2958. AddMissingCustomer((string)w["Client_Code"], isOpen);
  2959. }
  2960. }
  2961.  
  2962. c["Account_Period_From"] = w["Policy_InceptionDate"];
  2963. c["Account_Period_To"] = w["Policy_ExpiryDate"];
  2964.  
  2965. // Insurance
  2966. c["Insurer_Name"] = w["Underwriter_Name"];
  2967. object insurerCode = UnderwriterCode(w["Underwriter_Code"]);
  2968. c["Imported_Insurer_Code"] = Truncate(32, insurerCode);
  2969. c["Imported_Insurer_Name"] = c["Insurer_Name"];
  2970. if (insurerCode != DBNull.Value) {
  2971. object insurerCodeArg = ((string)insurerCode).Replace("'", "''");
  2972. c["Insurer"] = Infor.GetField("AccountID", "Account", "Type = 'Underwriter' and (Client_Code = '" + insurerCodeArg +
  2973. "' or ExternalAccountNo = '" + insurerCodeArg + "')", inforConnection) ?? DBNull.Value;
  2974. if (c["Insurer"] != DBNull.Value)
  2975. c["Insurer_Name"] = Infor.GetField("Account", "Account", "AccountID = '" + c["Insurer"] + "'", inforConnection);
  2976. else {
  2977. LogWarning("Insurer not found (" + claimNumber + "): '" + w["Underwriter_Code"] + "'");
  2978. IssueTracker.AddWarning(IssueTracker.Issue.UNDERWRITER_NOT_FOUND, (string)claimNumber, w["Underwriter_Code"].ToString());
  2979. AddMissingUnderwriter((string)w["Underwriter_Code"], isOpen);
  2980. }
  2981. }
  2982. c["Insured_Name"] = c["Account_Name"]; // Is this really correct?
  2983. c["Insurer_Ref"] = Truncate(200, w["UnderwriterClaimNumber"]);
  2984.  
  2985. // Broker
  2986. c["Broker_Company_Name"] = w["PolicySubInter_Name"];
  2987. object brokerCode = AssociateCode(w["PolicySubInter_Code"]);
  2988. c["Imported_Broker_Code"] = Truncate(32, brokerCode);
  2989. c["Imported_Broker_Name"] = c["Broker_Company_Name"];
  2990. if (brokerCode != DBNull.Value) {
  2991. object brokerCodeArg = ((string)brokerCode).Replace("'", "''");
  2992. c["Broker_Company"] = Infor.GetField("AccountID", "Account", "Type = 'Associate' and (Client_Code = '" + brokerCodeArg +
  2993. "' or ExternalAccountNo = '" + brokerCodeArg + "')", inforConnection) ?? DBNull.Value;
  2994. if (c["Broker_Company"] != DBNull.Value)
  2995. c["Broker_Company_Name"] = Infor.GetField("Account", "Account", "AccountID = '" + c["Broker_Company"] + "'", inforConnection);
  2996. else {
  2997. LogWarning("Broker company not found (" + claimNumber + "): '" + w["PolicySubInter_Code"] + "'");
  2998. IssueTracker.AddWarning(IssueTracker.Issue.ASSOCIATE_NOT_FOUND, (string)claimNumber, w["PolicySubInter_Code"].ToString());
  2999. AddMissingAssociate((string)c["Broker_Company_Name"], isOpen);
  3000. }
  3001. }
  3002.  
  3003. // Assigned user
  3004. c["Assigned_User"] = DBNull.Value;
  3005. string username = null;
  3006. if (ledger == WinbeatLedger.RealEstate)
  3007. username = w["UDD_ClaimsHandler"] != DBNull.Value ? (string)UserName(w["UDD_ClaimsHandler"]) : null;
  3008. else if (ledger == WinbeatLedger.Melbourne)
  3009. username = w["UDD_ClaimOfficer"] != DBNull.Value ? (string)UserName(w["UDD_ClaimOfficer"]) : null;
  3010.  
  3011. if (username != null) {
  3012. c["Assigned_User"] = Infor.GetField("UserID", "UserInfo", "Username = '" + username + "'", inforConnection) ?? DBNull.Value;
  3013. if (c["Assigned_User"] == DBNull.Value) {
  3014. // Log
  3015. LogWarning("Assigned user not found: '" + username + "'");
  3016. IssueTracker.AddWarning(IssueTracker.Issue.ASSIGNED_USER_NOT_FOUND, (string)claimNumber, username);
  3017. AddMissingUser(username, isOpen);
  3018.  
  3019. // Fallback user
  3020. c["Assigned_User"] = Infor.GetField("UserID", "UserInfo", "Username = 'Ms Megan Peacock'", inforConnection) ?? DBNull.Value;
  3021. }
  3022. }
  3023.  
  3024. // Claimant
  3025. if (ledger != WinbeatLedger.Underwriting) {
  3026. c["Claimant_First_Name"] = Truncate(200, ConvertFirstName(w["UDD_ClaimantName"]));
  3027. c["Claimant_Last_Name"] = Truncate(200, ConvertLastName(w["UDD_ClaimantName"]));
  3028. }
  3029.  
  3030. // Incident
  3031. c["Incident_Date"] = ConvertDateTime(w["DateOfLoss"], claimNumber);
  3032. c["Incident_Time"] = ConvertDateTime(w["DateOfLoss"], claimNumber);
  3033. c["Incident_Summary"] = w["Description"];
  3034. //if (ledger == WinbeatLedger.RealEstate)
  3035. // c["Incident_Type"] = Truncate(200, w["UDD_ProximateCause"]);
  3036. //else if (ledger == WinbeatLedger.Melbourne)
  3037. // c["Incident_Type"] = Truncate(200, w["UDD_ClaimDetail"]);
  3038. c["Incident_Type"] = Truncate(200, w["PolicyClass_Description"]);
  3039. if (ledger != WinbeatLedger.Underwriting)
  3040. c["Incident_Category"] = Truncate(400, w["UDD_ProximateCause"]);
  3041.  
  3042. // Property
  3043. c["Property_Claim_Ref"] = Truncate(200, claimNumber);
  3044. if (c["Policy_No"] != DBNull.Value) {
  3045. string policyNumber = ((string)c["Policy_No"]).Replace("'", "''");
  3046. object streetNumber = Infor.GetField("Situation_Street_Number", "H_Policy", "Policy_Number = '" + policyNumber + "'", inforConnection) ?? DBNull.Value;
  3047. object street = Infor.GetField("Situation_Street", "H_Policy", "Policy_Number = '" + policyNumber + "'", inforConnection) ?? DBNull.Value;
  3048. if ((streetNumber != DBNull.Value) || (street != DBNull.Value))
  3049. c["Property_Address_1"] = Truncate(200, (streetNumber != DBNull.Value ? (string)streetNumber + " " : "") + (street != DBNull.Value ? (string)street : ""));
  3050. c["Property_Suburb"] = Truncate(200, Infor.GetField("Situation_Suburb", "H_Policy", "Policy_Number = '" + policyNumber + "'", inforConnection));
  3051. c["Property_State"] = Truncate(32, Infor.GetField("Situation_State", "H_Policy", "Policy_Number = '" + policyNumber + "'", inforConnection));
  3052. c["Property_Postalcode"] = Truncate(32, Infor.GetField("Situation_Postcode", "H_Policy", "Policy_Number = '" + policyNumber + "'", inforConnection));
  3053. }
  3054. c["Account_Manager_Property"] = Truncate(200, w["AccountManager_Code"]);
  3055.  
  3056. // Misc
  3057. c["ABN_Num"] = Truncate(200, w["Client_ABN"]);
  3058. c["Cat_Code"] = Truncate(200, w["UDD_CatCode"]);
  3059. c["Date_Of_Loss"] = ConvertDateTime(w["DateOfLoss"], claimNumber);
  3060. if (ledger != WinbeatLedger.Underwriting) {
  3061. c["Causation"] = Truncate(400, w["UDD_ProximateCause"]);
  3062. //c["Complexity"] = Truncate(200, w["UDD_ClaimDetail"]);
  3063. c["Complexity"] = Truncate(200, w["UDD_ClaimCategory"]);
  3064. }
  3065. if (ledger == WinbeatLedger.RealEstate)
  3066. c["Are_You_GST_Registered"] = ConvertGST(w["UDD_GSTStatus"]); // Field quality is too poor
  3067.  
  3068. // Notes
  3069. if ((w["Notes"] != DBNull.Value) || (w["RejectReason"] != DBNull.Value) || (w["PaymentNotes"] != DBNull.Value)) {
  3070. string notes = "";
  3071. notes += w["Notes"] != DBNull.Value ? w["Notes"] + "\n\n" : "";
  3072. notes += w["Notes"] != DBNull.Value ? "REJECT REASON:\n" + w["RejectReason"] + "\n\n" : "";
  3073. notes += w["Notes"] != DBNull.Value ? "PAYMENT NOTES:\n" + w["PaymentNotes"] : "";
  3074. c["Claim_Note"] = notes;
  3075. }
  3076.  
  3077. // Reserve
  3078. c["Liability_Res_Source"] = w["EstAmount"];
  3079.  
  3080. // State
  3081. object uddStatusObject = w["UDD_ClaimStatus"] ?? DBNull.Value;
  3082. string uddStatus = (uddStatusObject != DBNull.Value) ? uddStatusObject.ToString().ToLower() : "";
  3083.  
  3084. c["Claim_Lodged"] = (uddStatus.Contains("lodge") && !uddStatus.Contains("not")) ? "T" : "F";
  3085. c["Claim_Not_Lodged"] = (uddStatus.Contains("lodge") && uddStatus.Contains("not")) ? "T" : "F";
  3086. c["Claim_Acknowledged"] = uddStatus.Contains("acknowl") ? "T" : "F";
  3087. bool paymentSettlement = ((w["PaymentNotes"] != DBNull.Value) && ((string)w["PaymentNotes"]).ToLower().Contains("settlement"));
  3088. c["Outcome_Settlement"] = (uddStatus.Contains("settle") || paymentSettlement) ? "T" : "F";
  3089. c["Claim_Approved"] = uddStatus.Contains("approve") ? "T" : "F";
  3090. c["Outcome_Declined"] = ((uddStatus.Contains("decline") || uddStatus.Contains("denied")) && !uddStatus.Contains("partial")) ? "T" : "F";
  3091. c["Expert_Appointed"] = (uddStatus.Contains("appoint") || uddStatus.Contains("assessor")) ? "T" : "F";
  3092. c["Indemnity_Granted"] = (uddStatus.Contains("indemnity") && !uddStatus.Contains("partial")) ? "T" : "F";
  3093. c["Partial_Indemnity_Granted"] = (uddStatus.Contains("indemnity") && uddStatus.Contains("partial")) ? "T" : "F";
  3094.  
  3095. c["Review"] = (ConvertDateTime(w["DateFollowup"], claimNumber) != DBNull.Value ? "T" : "F");
  3096. c["Review_Date"] = ConvertDateTime(w["DateFollowup"], claimNumber);
  3097.  
  3098. c["Claim_Closed"] = uddStatus.Contains("closed") ? "T" : "F";
  3099. object[] potentialClosedDates = new object[] { w["DatePaidByUnderwriter"], w["DateRejected"], w["DateWithdrawn"],
  3100. w["DateReportOnly"], w["DatePaidByClient"], w["DatePaidByOther"]};
  3101. foreach (object d in potentialClosedDates) {
  3102. object closedDate = ConvertDateTime(d, claimNumber);
  3103. if (closedDate != DBNull.Value) {
  3104. c["Claim_Closed"] = "T";
  3105. c["Claim_Closed_Date"] = closedDate;
  3106. }
  3107. }
  3108.  
  3109. // Outcome
  3110. c["Outcome"] = DBNull.Value;
  3111. if ((uddStatus.Contains("decline") || uddStatus.Contains("denied")) && !uddStatus.Contains("partial"))
  3112. c["Outcome"] = "Declined";
  3113. else if ((uddStatus.Contains("decline") || uddStatus.Contains("denied")) && uddStatus.Contains("partial"))
  3114. c["Outcome"] = "Partial Decline";
  3115. else if (uddStatus.Contains("paid"))
  3116. c["Outcome"] = "Paid";
  3117. else if (uddStatus.Contains("withdraw"))
  3118. c["Outcome"] = "Withdrawn";
  3119.  
  3120. // Insert
  3121. object duplicateClaimID = duplicateCommand.ExecuteScalar();
  3122. if ((duplicateClaimID == null) || (duplicateClaimID == DBNull.Value)) {
  3123. // Claim
  3124. claim.Rows.Add(c);
  3125. inforAdapter.Update(claim);
  3126. claim.Rows.Clear();
  3127. Log("Claim (" + claimID + "): " + claimNumber);
  3128.  
  3129. ++importedCount;
  3130.  
  3131. // Payment
  3132. ImportWinBEATClaims_Payment(claimID, claimNumber.ToString(), Operation.Insert, w, inforConnection);
  3133. }
  3134. }
  3135. else
  3136. ++duplicateCount;
  3137.  
  3138. // Dispose
  3139. duplicateCommand.Dispose();
  3140. }
  3141.  
  3142. // Close connection
  3143. inforConnection.Close();
  3144.  
  3145. // Summary
  3146. Log("\n--------------------------------------------------------------------------------");
  3147. Log("RESULTS (" + TeamName() + ": Importing WinBEAT claims (" + sTime + ")):");
  3148. Log(" " + processedCount + " processed.");
  3149. Log(" " + importedCount + " imported.");
  3150. Log(" " + duplicateCount + " ignored (duplicates)\n");
  3151.  
  3152. // Summary
  3153. IssueTracker.LogSummary(TeamName() + ": Importing WinBEAT claims (" + sTime + ")");
  3154.  
  3155. // Missing
  3156. LogMissingSummary(TeamName() + ": Importing WinBEAT claims (" + sTime + ")");
  3157.  
  3158. // Whitespace
  3159. Log("\n");
  3160. }
  3161.  
  3162.  
  3163. // LINKING ================================================================================
  3164. //--------------------------------------------------------------------------------
  3165. public static void LinkImportedClaims(Team team = Team.All, bool logOnly = false) {
  3166. // Time
  3167. sTime = DateTime.Now.ToString();
  3168.  
  3169. // Log
  3170. Log("================================================================================");
  3171. Log("Linking imported claims (" + sTime + ")");
  3172. Log("================================================================================");
  3173.  
  3174. // Connection
  3175. OleDbConnection inforConnection = new OleDbConnection(sInforConnectionString);
  3176. inforConnection.Open();
  3177.  
  3178. // Query
  3179. string query = "select H_ClaimsID, Claim_Reference_Num, Claim_Status, Policy_No, Policy_No_Name, AccountID, Account_Name, Insurer, Insurer_Name, Broker_Company, Broker_Company_Name, " +
  3180. "Incident_Date, Date_Of_Loss, Imported_Policy_Number, Imported_Account_Name, Imported_Account_Code, Imported_Insurer_Name, Imported_Insurer_Code, Imported_Broker_Name, " +
  3181. "Imported_Broker_Code from H_Claims where Imported = 'T'";
  3182.  
  3183. // Team
  3184. sTeam = team;
  3185. if (team != Team.All)
  3186. query += " and Claim_Team_Name = '" + TeamName() + "'";
  3187.  
  3188. // Adapter
  3189. Log("Retrieving claim data...");
  3190. OleDbDataAdapter inforAdapter = new OleDbDataAdapter(query, sInforConnectionString);
  3191. OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(inforAdapter);
  3192.  
  3193. // Dataset
  3194. DataSet dataSet = new DataSet();
  3195. inforAdapter.SelectCommand.CommandTimeout = 1200;
  3196. inforAdapter.Fill(dataSet, "H_Claims");
  3197.  
  3198. // Tables
  3199. DataTable claim = dataSet.Tables["H_Claims"];
  3200.  
  3201. // Counts
  3202. int updatedPolicyCount = 0;
  3203. int missingPolicyCount = 0;
  3204. int updatedAccountCount = 0;
  3205. int missingAccountCount = 0;
  3206. int updatedInsurerCount = 0;
  3207. int missingInsurerCount = 0;
  3208. int updatedBrokerCount = 0;
  3209. int missingBrokerCount = 0;
  3210.  
  3211. // Import
  3212. foreach (DataRow c in claim.Rows) {
  3213. // Status
  3214. bool isOpen = c["Claim_Status"].Equals("Open");
  3215.  
  3216. // Policy
  3217. object policyID = DBNull.Value;
  3218. object policyNumber = DBNull.Value;
  3219. if (c["Imported_Policy_Number"] != DBNull.Value) {
  3220. //if ((c["Policy_No"] == DBNull.Value) && c["Policy_No_Name"].Equals(c["Imported_Policy_Number"])) {
  3221. if (c["Policy_No_Name"].Equals(c["Imported_Policy_Number"])) {
  3222. string importedPolicyNumber = (string)PolicyNumber(c["Imported_Policy_Number"]);
  3223.  
  3224. // Query
  3225. string policyWhereNumber = "Policy_Number = '" + importedPolicyNumber.Replace("'", "''") + "'";
  3226. string policyWhereAccount = ((c["Account_Name"] != DBNull.Value) ? "Owner_AccountID in (select AccountID from Account where Account = '" + ((string)c["Account_Name"]).Replace("'", "''") + "')" : "1=1");
  3227. string policyWhereDates = ((c["Incident_Date"] != DBNull.Value) ? "Inception_Date <= '" + ((DateTime)c["Incident_Date"]).ToString("s", CultureInfo.InvariantCulture) + "'" : "1=1");
  3228. policyID = Infor.GetField("H_PolicyID", "H_Policy", policyWhereNumber + " and " + policyWhereAccount + " and " + policyWhereDates, inforConnection) ?? DBNull.Value;
  3229.  
  3230. // Outcome
  3231. if (policyID != DBNull.Value) {
  3232. if ((c["Policy_No"] == DBNull.Value) || !policyID.Equals(c["Policy_No"])) {
  3233. policyNumber = importedPolicyNumber;
  3234. ++updatedPolicyCount;
  3235.  
  3236. // Log
  3237. if (logOnly) {
  3238. object policyAccount = "";
  3239. object policyInceptionDate = "";
  3240. if (c["Policy_No"] != DBNull.Value) {
  3241. policyAccount = Infor.GetField("_A.Account", "H_Policy _P left join Account _A on _P.Owner_AccountID = _A.AccountID", "H_PolicyID = '" + c["Policy_No"] + "'", inforConnection) ?? DBNull.Value;
  3242. policyInceptionDate = Infor.GetField("Inception_Date", "H_Policy", "H_PolicyID = '" + c["Policy_No"] + "'", inforConnection) ?? DBNull.Value;
  3243. }
  3244. object newPolicyAccount = Infor.GetField("_A.Account", "H_Policy _P left join Account _A on _P.Owner_AccountID = _A.AccountID", "H_PolicyID = '" + policyID + "'", inforConnection) ?? DBNull.Value;
  3245. object newPolicyInceptionDate = Infor.GetField("Inception_Date", "H_Policy", "H_PolicyID = '" + policyID + "'", inforConnection) ?? DBNull.Value;
  3246. Log("Claim Policy (" + c["H_ClaimsID"] + "): " + c["Claim_Reference_Num"] + ", account:'" + c["Account_Name"] + "', " +
  3247. "policy=[" + policyID + ", '" + policyNumber + "', '" + policyAccount + "', '" + policyInceptionDate + "'], " +
  3248. "old_policy=[" + c["Policy_No"] + ", '" + c["Policy_No_Name"] + "', '" + newPolicyAccount + "', '" + newPolicyInceptionDate + "']");
  3249. }
  3250. }
  3251. else
  3252. policyID = DBNull.Value;
  3253. }
  3254. else {
  3255. IssueTracker.AddWarning(IssueTracker.Issue.POLICY_NOT_FOUND, (string)c["Claim_Reference_Num"], (string)c["Imported_Policy_Number"]);
  3256. AddMissingPolicy((string)c["Imported_Policy_Number"], isOpen);
  3257. ++missingPolicyCount;
  3258. }
  3259. }
  3260. }
  3261.  
  3262. // Account
  3263. object accountID = DBNull.Value;
  3264. object accountName = DBNull.Value;
  3265. if ((c["Imported_Account_Name"] != DBNull.Value) || (c["Imported_Account_Code"] != DBNull.Value)) {
  3266. if ((c["AccountID"] == DBNull.Value) && c["Account_Name"].Equals(c["Imported_Account_Name"])) {
  3267. if (c["Imported_Account_Code"] != DBNull.Value) {
  3268. string importedAccountCode = ((string)CustomerCode(c["Imported_Account_Code"])).Replace("'", "''");
  3269. accountID = Infor.GetField("AccountID", "Account", "Type = 'Customer' and (Client_Code = '" + importedAccountCode +
  3270. "' or ExternalAccountNo = '" + importedAccountCode + "')", inforConnection) ?? DBNull.Value;
  3271. if (accountID != DBNull.Value)
  3272. accountName = Infor.GetField("Account", "Account", "AccountID = '" + accountID + "'", inforConnection) ?? DBNull.Value;
  3273. }
  3274. else {
  3275. object importedAccountName = AccountName(c["Imported_Account_Name"]);
  3276. if (importedAccountName != null)
  3277. importedAccountName = ((string)importedAccountName).Replace("'", "''");
  3278. accountID = Infor.GetField("AccountID", "Account", "Type = 'Customer' and (Account = '" + importedAccountName + "' or Client_Code = '" + importedAccountName +
  3279. "' or ExternalAccountNo = '" + importedAccountName + "')", inforConnection) ?? DBNull.Value;
  3280. if (accountID != DBNull.Value)
  3281. accountName = importedAccountName;
  3282. }
  3283.  
  3284. // Result
  3285. if (accountID != DBNull.Value)
  3286. ++updatedAccountCount;
  3287. else {
  3288. string missing = (c["Imported_Account_Name"] != DBNull.Value ? (string)c["Imported_Account_Name"] : "");
  3289. missing += (c["Imported_Account_Code"] != DBNull.Value ? (missing.Length != 0 ? " " : "") + "(" + (string)c["Imported_Account_Code"] + ")" : "");
  3290. IssueTracker.AddWarning(IssueTracker.Issue.CUSTOMER_NOT_FOUND, (string)c["Claim_Reference_Num"], missing);
  3291. AddMissingCustomer(missing, isOpen);
  3292. ++missingAccountCount;
  3293. }
  3294. }
  3295. }
  3296.  
  3297. // Insurer
  3298. object insurerID = DBNull.Value;
  3299. object insurerName = DBNull.Value;
  3300. if ((c["Imported_Insurer_Name"] != DBNull.Value) || (c["Imported_Insurer_Code"] != DBNull.Value)) {
  3301. if ((c["Insurer"] == DBNull.Value) && c["Insurer_Name"].Equals(c["Imported_Insurer_Name"])) {
  3302. if (c["Imported_Insurer_Code"] != DBNull.Value) {
  3303. string importedInsurerCode = ((string)UnderwriterCode(c["Imported_Insurer_Code"])).Replace("'", "''");
  3304. insurerID = Infor.GetField("AccountID", "Account", "Type = 'Underwriter' and (Client_Code = '" + importedInsurerCode +
  3305. "' or ExternalAccountNo = '" + importedInsurerCode + "')", inforConnection) ?? DBNull.Value;
  3306. if (insurerID != DBNull.Value)
  3307. insurerName = Infor.GetField("Account", "Account", "AccountID = '" + insurerID + "'", inforConnection) ?? DBNull.Value;
  3308. }
  3309. else {
  3310. object importedInsurerName = AccountName(c["Imported_Insurer_Name"]);
  3311. if (importedInsurerName != null)
  3312. importedInsurerName = ((string)importedInsurerName).Replace("'", "''");
  3313. insurerID = Infor.GetField("AccountID", "Account", "Type = 'Underwriter' and (Account = '" + importedInsurerName + "' or Client_Code = '" + importedInsurerName +
  3314. "' or ExternalAccountNo = '" + importedInsurerName + "')", inforConnection) ?? DBNull.Value;
  3315. if (insurerID != DBNull.Value)
  3316. insurerName = importedInsurerName;
  3317. }
  3318.  
  3319. // Result
  3320. if (insurerID != DBNull.Value)
  3321. ++updatedInsurerCount;
  3322. else {
  3323. string missing = (c["Imported_Insurer_Name"] != DBNull.Value ? (string)c["Imported_Insurer_Name"] : "");
  3324. missing += (c["Imported_Insurer_Code"] != DBNull.Value ? (missing.Length != 0 ? " " : "") + "(" + (string)c["Imported_Insurer_Code"] + ")" : "");
  3325. IssueTracker.AddWarning(IssueTracker.Issue.UNDERWRITER_NOT_FOUND, (string)c["Claim_Reference_Num"], missing);
  3326. AddMissingUnderwriter(missing, isOpen);
  3327. ++missingInsurerCount;
  3328. }
  3329. }
  3330. }
  3331.  
  3332. // Broker
  3333. object brokerID = DBNull.Value;
  3334. object brokerName = DBNull.Value;
  3335. if ((c["Imported_Broker_Name"] != DBNull.Value) || (c["Imported_Broker_Code"] != DBNull.Value)) {
  3336. if ((c["Broker_Company"] == DBNull.Value) && c["Broker_Company_Name"].Equals(c["Imported_Broker_Name"])) {
  3337. if (c["Imported_Broker_Code"] != DBNull.Value) {
  3338. string importedBrokerCode = ((string)AssociateCode(c["Imported_Broker_Code"])).Replace("'", "''");
  3339. brokerID = Infor.GetField("AccountID", "Account", "Type = 'Associate' and (Client_Code = '" + importedBrokerCode +
  3340. "' or ExternalAccountNo = '" + importedBrokerCode + "')", inforConnection) ?? DBNull.Value;
  3341. if (brokerID != DBNull.Value)
  3342. brokerName = Infor.GetField("Account", "Account", "AccountID = '" + brokerID + "'", inforConnection) ?? DBNull.Value;
  3343. }
  3344. else {
  3345. object importedBrokerName = AccountName(c["Imported_Broker_Name"]);
  3346. if (importedBrokerName != null)
  3347. importedBrokerName = ((string)importedBrokerName).Replace("'", "''");
  3348. brokerID = Infor.GetField("AccountID", "Account", "Type = 'Associate' and (Account = '" + importedBrokerName + "' or Client_Code = '" + importedBrokerName +
  3349. "' or ExternalAccountNo = '" + importedBrokerName + "')", inforConnection) ?? DBNull.Value;
  3350. if (brokerID != DBNull.Value)
  3351. brokerName = importedBrokerName;
  3352. }
  3353.  
  3354. // Result
  3355. if (brokerID != DBNull.Value)
  3356. ++updatedBrokerCount;
  3357. else {
  3358. string missing = (c["Imported_Broker_Name"] != DBNull.Value ? (string)c["Imported_Broker_Name"] : "");
  3359. missing += (c["Imported_Broker_Code"] != DBNull.Value ? (missing.Length != 0 ? " " : "") + "(" + (string)c["Imported_Broker_Code"] + ")" : "");
  3360. IssueTracker.AddWarning(IssueTracker.Issue.ASSOCIATE_NOT_FOUND, (string)c["Claim_Reference_Num"], missing);
  3361. AddMissingAssociate(missing, isOpen);
  3362. ++missingBrokerCount;
  3363. }
  3364. }
  3365. }
  3366.  
  3367. // Change check
  3368. if (logOnly || ((policyID == DBNull.Value) && (accountID == DBNull.Value) && (insurerID == DBNull.Value) && (brokerID == DBNull.Value)))
  3369. continue;
  3370.  
  3371. // Update query
  3372. string updateQuery = "update H_Claims set ";
  3373. int updateQueryLength = updateQuery.Length;
  3374. if (policyID != DBNull.Value)
  3375. updateQuery += ((updateQuery.Length > updateQueryLength) ? "," : "") + "Policy_No = ?, Policy_No_Name = ?";
  3376. if (accountID != DBNull.Value)
  3377. updateQuery += ((updateQuery.Length > updateQueryLength) ? "," : "") + "AccountID = ?, Account_Name = ?";
  3378. if (insurerID != DBNull.Value)
  3379. updateQuery += ((updateQuery.Length > updateQueryLength) ? "," : "") + "Insurer = ?, Insurer_Name = ?";
  3380. if (brokerID != DBNull.Value)
  3381. updateQuery += ((updateQuery.Length > updateQueryLength) ? "," : "") + "Broker_Company = ?, Broker_Company_Name = ?";
  3382. updateQuery += " where H_ClaimsID = ?";
  3383.  
  3384. // Update
  3385. OleDbCommand command = new OleDbCommand(updateQuery, inforConnection);
  3386. if (policyID != DBNull.Value) {
  3387. command.Parameters.AddWithValue("@PolicyID", policyID);
  3388. command.Parameters.AddWithValue("@PolicyNumber", policyNumber);
  3389. }
  3390. if (accountID != DBNull.Value) {
  3391. command.Parameters.AddWithValue("@AccountID", accountID);
  3392. command.Parameters.AddWithValue("@AccountName", accountName);
  3393. }
  3394. if (insurerID != DBNull.Value) {
  3395. command.Parameters.AddWithValue("@InsurerID", insurerID);
  3396. command.Parameters.AddWithValue("@InsurerName", insurerName);
  3397. }
  3398. if (brokerID != DBNull.Value) {
  3399. command.Parameters.AddWithValue("@BrokerID", brokerID);
  3400. command.Parameters.AddWithValue("@BrokerName", brokerName);
  3401. }
  3402. command.Parameters.AddWithValue("@ClaimID", c["H_ClaimsID"]);
  3403. command.ExecuteNonQuery();
  3404.  
  3405. // Log
  3406. Log("Claim (" + c["H_ClaimsID"] + "): " + c["Claim_Reference_Num"] + (policyID != DBNull.Value ? ", policy='" + policyNumber + "'" : "") +
  3407. (accountID != DBNull.Value ? ", account='" + accountName + "'" : "") + (insurerID != DBNull.Value ? ", insurer='" + insurerName + "'" : "") +
  3408. (brokerID != DBNull.Value ? ", broker='" + brokerName + "'" : ""));
  3409. }
  3410.  
  3411. // Close connection
  3412. inforConnection.Close();
  3413.  
  3414. // Summary
  3415. Log("\n--------------------------------------------------------------------------------");
  3416. Log("RESULTS (Linking imported claims (" + sTime + ")):");
  3417. Log(" " + updatedPolicyCount + " of " + (updatedPolicyCount + missingPolicyCount) + " missing policies updated.\n");
  3418. Log(" " + updatedAccountCount + " of " + (updatedAccountCount + missingAccountCount) + " missing accounts updated.\n");
  3419. Log(" " + updatedInsurerCount + " of " + (updatedInsurerCount + missingInsurerCount) + " missing insurers updated.\n");
  3420. Log(" " + updatedBrokerCount + " of " + (updatedBrokerCount + missingBrokerCount) + " missing brokers updated.\n");
  3421.  
  3422. // Summary
  3423. IssueTracker.LogSummary("Linking imported claims (" + sTime + ")");
  3424.  
  3425. // Missing
  3426. LogMissingSummary("Linking imported claims (" + sTime + ")");
  3427.  
  3428. // Whitespace
  3429. Log("\n");
  3430. }
  3431.  
  3432.  
  3433. // TBA POLICY NUMBERS ================================================================================
  3434. //--------------------------------------------------------------------------------
  3435. public static void ImportTBAPolicyNumbers() {
  3436. //select '(''', H_PolicyID, ''',''', OCNUM, ''',''', Invoice_Number, '''),' from sysdba.H_Policy
  3437. //where Policy_Number like '%t/b/a%' and ((OCNum is not null and OCNum <> '') and (Invoice_Number is not null and Invoice_Number <> ''))
  3438.  
  3439. //WinbeatLedger[] ledgers = new WinbeatLedger[] { WinbeatLedger.Melbourne, WinbeatLedger.RealEstate, WinbeatLedger.Underwriting };
  3440.  
  3441.  
  3442. // Connection
  3443. OleDbConnection connection = new OleDbConnection(sInforConnectionString);
  3444. connection.Open();
  3445.  
  3446. // Adapter
  3447. //Log("Retrieving policy data...");
  3448. //OleDbDataAdapter inforAdapter = new OleDbDataAdapter
  3449.  
  3450. // Close
  3451. connection.Close();
  3452. }
  3453.  
  3454.  
  3455. // DELEGATES ================================================================================
  3456. //--------------------------------------------------------------------------------
  3457. private static void MissingField(string name) {
  3458. LogWarning("Missing field '" + name + "'");
  3459. }
  3460.  
  3461. //--------------------------------------------------------------------------------
  3462. private static void ParseError(string name, Type type, string value) {
  3463. LogWarning("Invalid value '" + name + "' : '" + value + "', expected " + type.ToString());
  3464. }
  3465.  
  3466. //--------------------------------------------------------------------------------
  3467. private static void Truncated(string name, int length, int valueLength) {
  3468. LogWarning("Truncated value '" + name + "' : " + length + " from " + valueLength);
  3469. }
  3470.  
  3471.  
  3472. // ACCOUNTS ================================================================================
  3473. //--------------------------------------------------------------------------------
  3474. private static string ConvertAccountType(string accountType) {
  3475. if (accountType == null)
  3476. return null;
  3477. else if (accountType.ToLower().Equals("corporate"))
  3478. return "Customer";
  3479. else if (accountType.ToLower().Equals("insurer"))
  3480. return "Underwriter";
  3481. else
  3482. return accountType;
  3483. }
  3484.  
  3485.  
  3486. // CONVERSION ================================================================================
  3487. //--------------------------------------------------------------------------------
  3488. private static object ConvertDDField(DataRow sourceRow, string ddField, string field, string[] preferredValues = null) {
  3489. bool ddFieldHasValue = (sourceRow[ddField] != DBNull.Value);
  3490. bool ddFieldHasPreferred = false;
  3491. if ((preferredValues != null) && ddFieldHasValue)
  3492. ddFieldHasPreferred = preferredValues.Contains(sourceRow[ddField]);
  3493. ddFieldHasPreferred = true;
  3494.  
  3495. bool fieldHasValue = (sourceRow[field] != DBNull.Value);
  3496. bool fieldHasPreferred = false;
  3497. if ((preferredValues != null) && fieldHasValue)
  3498. fieldHasPreferred = preferredValues.Contains(sourceRow[field]);
  3499.  
  3500. if (ddFieldHasPreferred)
  3501. return sourceRow[ddField];
  3502. else if (fieldHasPreferred)
  3503. return sourceRow[field];
  3504. else if (ddFieldHasValue)
  3505. return sourceRow[ddField];
  3506. else if (fieldHasValue)
  3507. return sourceRow[field];
  3508. else
  3509. return DBNull.Value;
  3510. }
  3511.  
  3512. //--------------------------------------------------------------------------------
  3513. public static object Truncate(int maxLength, object value) {
  3514. if (value is string) {
  3515. string sValue = (string)value;
  3516. if (string.IsNullOrEmpty(sValue))
  3517. return sValue;
  3518. return (sValue.Length <= maxLength ? sValue : sValue.Substring(0, maxLength));
  3519. }
  3520. else
  3521. return value;
  3522. }
  3523.  
  3524. //--------------------------------------------------------------------------------
  3525. public static object ConvertDateTime(object value, object claimNumber) {
  3526. if ((value == DBNull.Value) || !(value is DateTime))
  3527. return DBNull.Value;
  3528.  
  3529. DateTime dateTime = (DateTime)value;
  3530. DateTime minimum = new DateTime(1753, 1, 1);
  3531. DateTime maximum = new DateTime(9999, 12, 31, 23, 59, 59, 997);
  3532.  
  3533. if ((dateTime >= minimum) && (dateTime <= maximum))
  3534. return dateTime;
  3535. else {
  3536. LogWarning("Date out of range (" + claimNumber + "): '" + value + "'");
  3537. IssueTracker.AddWarning(IssueTracker.Issue.DATE_OUT_OF_RANGE, (string)claimNumber, value.ToString());
  3538. return DBNull.Value;
  3539. }
  3540. }
  3541.  
  3542. //--------------------------------------------------------------------------------
  3543. public static object JoinStringFields(object field1, object field2, string joiner) {
  3544. if ((field1 == DBNull.Value) && (field2 == DBNull.Value))
  3545. return DBNull.Value;
  3546.  
  3547. string result = "";
  3548.  
  3549. if (field1 != DBNull.Value) {
  3550. result += field1;
  3551. if (field2 != DBNull.Value)
  3552. result += joiner;
  3553. }
  3554.  
  3555. if (field2 != DBNull.Value)
  3556. result += field2;
  3557.  
  3558. return result;
  3559. }
  3560.  
  3561. //--------------------------------------------------------------------------------
  3562. public static object ConvertFirstName(object name) {
  3563. // Checks
  3564. if (name == DBNull.Value)
  3565. return DBNull.Value;
  3566.  
  3567. // String
  3568. string nameString = ConvertNameString(name);
  3569.  
  3570. // First name
  3571. if (nameString.Contains(",")) {
  3572. // Last name, first name
  3573. string[] names = nameString.Split(new char[] { ' ', '\t', '\n', ',' }, StringSplitOptions.RemoveEmptyEntries);
  3574. //return ((names.Length >= 2) ? names[1] : "");
  3575. if (names.Length < 2)
  3576. return "";
  3577. return string.Join(" ", names, 1, Math.Max(1, names.Length - 1));
  3578. }
  3579. else {
  3580. // First name, last name
  3581. string[] names = nameString.Split(new char[] { ' ', '\t', '\n', ',' }, StringSplitOptions.RemoveEmptyEntries);
  3582. //return ((names.Length >= 1) ? names[0] : "");
  3583. if (names.Length == 0)
  3584. return "";
  3585. return string.Join(" ", names, 0, Math.Max(1, names.Length - 1));
  3586. }
  3587. }
  3588.  
  3589. //--------------------------------------------------------------------------------
  3590. public static object ConvertLastName(object name) {
  3591. // Checks
  3592. if (name == DBNull.Value)
  3593. return DBNull.Value;
  3594.  
  3595. // String
  3596. string nameString = ConvertNameString(name);
  3597.  
  3598. // First name
  3599. if (nameString.Contains(",")) {
  3600. // Last name, first name
  3601. string[] names = nameString.Split(new char[] { ' ', '\t', '\n', ',' }, StringSplitOptions.RemoveEmptyEntries);
  3602. return ((names.Length >= 1) ? names[0] : "");
  3603. }
  3604. else {
  3605. // First name, last name
  3606. string[] names = nameString.Split(new char[] { ' ', '\t', '\n', ',' }, StringSplitOptions.RemoveEmptyEntries);
  3607. //return ((names.Length >= 2) ? names[1] : "");
  3608. return ((names.Length >= 2) ? names[names.Length - 1] : "");
  3609. }
  3610. }
  3611.  
  3612. //--------------------------------------------------------------------------------
  3613. private static string ConvertNameString(object name) {
  3614. // String
  3615. string nameString = (string)name;
  3616.  
  3617. // & - drop everything after
  3618. int andIndex = nameString.IndexOf("&");
  3619. if (andIndex > 0)
  3620. nameString = nameString.Substring(0, andIndex);
  3621.  
  3622. // Trim
  3623. return nameString.Trim();
  3624. }
  3625.  
  3626. //--------------------------------------------------------------------------------
  3627. public static object ConvertWorkPhone(object phoneNumber) {
  3628. if (phoneNumber == DBNull.Value)
  3629. return DBNull.Value;
  3630.  
  3631. string phoneString = ((string)phoneNumber).Trim();
  3632. if (!phoneString.StartsWith("04"))
  3633. return phoneString;
  3634. else
  3635. return DBNull.Value;
  3636. }
  3637.  
  3638. //--------------------------------------------------------------------------------
  3639. public static object ConvertMobilePhone(object phoneNumber) {
  3640. if (phoneNumber == DBNull.Value)
  3641. return DBNull.Value;
  3642.  
  3643. string phoneString = ((string)phoneNumber).Trim();
  3644. if (phoneString.StartsWith("04"))
  3645. return phoneString;
  3646. else
  3647. return DBNull.Value;
  3648. }
  3649.  
  3650. //--------------------------------------------------------------------------------
  3651. public static string ConvertGST(object gst) {
  3652. if ((gst == null) || (gst == DBNull.Value))
  3653. return "No";
  3654.  
  3655. string gstLower = ((string)gst).ToLower();
  3656. if (gstLower.Contains("yes") || (gstLower.Length <= 3 && gstLower.StartsWith("y")) || gstLower.Equals("gst") || gstLower.Equals("100") ||
  3657. gstLower.Equals("100%") || (gstLower.Contains("registered") && !gstLower.Contains("not")))
  3658. {
  3659. return "Yes";
  3660. }
  3661. else
  3662. return "No";
  3663. }
  3664.  
  3665. //--------------------------------------------------------------------------------
  3666. public static object RegionFromStore(string store, string claimNumber) {
  3667. if (StringContains(store.ToLower(), new string[] { "minchinbury", "michinbury", "belmont", "blacktown", "gosford", "mt.druitt", "newcastle", "orange", "port macquarie", "richmond", "toukley" }))
  3668. return "NSW - Minchinbury";
  3669. else if (StringContains(store.ToLower(), new string[] { "prestons", "chatswood chase", "dee why", "hurtsville", "liverpool", "tuggeranong hyperdome", "wagga wagga" }))
  3670. return "NSW - Prestons";
  3671. else if (StringContains(store.ToLower(), new string[] { "brendale", "bundaberg", "caloundra", "hervey bay" }))
  3672. return "QLD - Brendale";
  3673. else if (StringContains(store.ToLower(), new string[] { "stapylton", "beenleigh", "coffs harbour", "mt gravatt" }))
  3674. return "QLD - Stapylton";
  3675. else if (StringContains(store.ToLower(), new string[] { "regency park", "modbury" }))
  3676. return "SA - Regency Park";
  3677. else if (StringContains(store.ToLower(), new string[] { "marion" }))
  3678. return "SA - Marion (Formerly Oaklands Park)";
  3679. else if (StringContains(store.ToLower(), new string[] { "dandenong", "morwell", "pakenham", "ringwood", "rosebud", "rosebud plaza" }))
  3680. return "VIC - Dandenong";
  3681. else if (StringContains(store.ToLower(), new string[] { "derrimut", "albury", "geelong", "horsham", "maryborough", "moonee ponds", "shepparton", "warrnambool", "wendouree", "werribee" }))
  3682. return "VIC - Derrimut";
  3683. else if (StringContains(store.ToLower(), new string[] { "jandakot" }))
  3684. return "WA - Jandakot";
  3685. else {
  3686. LogWarning("Region could not be determined from store value ( " + claimNumber + "): '" + store + "'");
  3687. IssueTracker.AddWarning(IssueTracker.Issue.REGION_COULD_NOT_BE_DETERMINED, (string)claimNumber, store);
  3688. return "Non-ALDI";
  3689. }
  3690. }
  3691.  
  3692.  
  3693. // CLAIM NUMBER SUBSTITUTIONS ================================================================================
  3694. //--------------------------------------------------------------------------------
  3695. private static void InitialiseClaimNumberSubstitutions() {
  3696. if (sClaimNumberSubstitutions.Count <= 0) {
  3697. sClaimNumberSubstitutions.Add("0279-hon", "RS0565");
  3698. }
  3699. }
  3700.  
  3701. //--------------------------------------------------------------------------------
  3702. private static object ClaimNumber(object claimNumber) {
  3703. if (claimNumber == DBNull.Value)
  3704. return DBNull.Value;
  3705. if (claimNumber == null)
  3706. return null;
  3707. string claimNumberString = ((string)claimNumber).ToLower();
  3708.  
  3709. InitialiseClaimNumberSubstitutions();
  3710. if (sClaimNumberSubstitutions.ContainsKey(claimNumberString))
  3711. return sClaimNumberSubstitutions[claimNumberString];
  3712. else
  3713. return claimNumber;
  3714. }
  3715.  
  3716.  
  3717. // POLICY NUMBER SUBSTITUTIONS ================================================================================
  3718. //--------------------------------------------------------------------------------
  3719. private static void InitialisePolicyNumberSubstitutions() {
  3720. if (sPolicyNumberSubstitutions.Count <= 0) {
  3721. // Univeral
  3722. sPolicyNumberSubstitutions.Add("71 22228344 glr", "71 2228344 GLR");
  3723.  
  3724. // Risksmart GCC
  3725. sRGCPolicyNumberSubstitutions.Add(" b0621chon00114b25", "B0621CHON00114B25");
  3726. sRGCPolicyNumberSubstitutions.Add("18a046139", "18A046139ISR");
  3727. sRGCPolicyNumberSubstitutions.Add("18a046139 isr", "18A046139ISR");
  3728. sRGCPolicyNumberSubstitutions.Add("18a046139isr", "18A046139ISR");
  3729. sRGCPolicyNumberSubstitutions.Add("18a046140 isr", "18A046140ISR");
  3730. sRGCPolicyNumberSubstitutions.Add("18a046140isr", "18A046140ISR");
  3731. sRGCPolicyNumberSubstitutions.Add("71-2955095gpr", "712955095 GPR");
  3732. sRGCPolicyNumberSubstitutions.Add("712228344 glr", "71 2228344 GLR");
  3733. sRGCPolicyNumberSubstitutions.Add("782222182mbd", "78 2222182 MBD");
  3734. sRGCPolicyNumberSubstitutions.Add("782222185mbd", "78 2222185 MBD");
  3735.  
  3736. // Risksmart Property
  3737. sRSPPolicyNumberSubstitutions.Add(" 93453595-20396", "93453595-20396");
  3738. sRSPPolicyNumberSubstitutions.Add(" 93453595-45126", "93453595-45126");
  3739. sRSPPolicyNumberSubstitutions.Add("03453595-42402", "93453595-42402");
  3740. sRSPPolicyNumberSubstitutions.Add("42402", "93453595-11302");
  3741. sRSPPolicyNumberSubstitutions.Add("11302", "93453595-11302");
  3742. sRSPPolicyNumberSubstitutions.Add("11596", "93453595-11596");
  3743. sRSPPolicyNumberSubstitutions.Add("11956", "93453595-11956");
  3744. sRSPPolicyNumberSubstitutions.Add("13719", "93453595-13719");
  3745. sRSPPolicyNumberSubstitutions.Add("14570", "93453595-14570");
  3746. sRSPPolicyNumberSubstitutions.Add("14583", "93453595-14583");
  3747. sRSPPolicyNumberSubstitutions.Add("14625", "93453595-14625");
  3748. sRSPPolicyNumberSubstitutions.Add("14738", "93453595-14738");
  3749. sRSPPolicyNumberSubstitutions.Add("15728", "93453595-15728");
  3750. sRSPPolicyNumberSubstitutions.Add("16001", "93453595-16001");
  3751. sRSPPolicyNumberSubstitutions.Add("17157", "93453595-17157");
  3752. sRSPPolicyNumberSubstitutions.Add("18294", "93453595-18294");
  3753. sRSPPolicyNumberSubstitutions.Add("18315", "93453595-18315");
  3754. sRSPPolicyNumberSubstitutions.Add("19498", "93453595-19498");
  3755. sRSPPolicyNumberSubstitutions.Add("19659", "93453595-19659");
  3756. sRSPPolicyNumberSubstitutions.Add("20400", "93453595-20400");
  3757. sRSPPolicyNumberSubstitutions.Add("20689", "93453595-20689");
  3758. sRSPPolicyNumberSubstitutions.Add("20771", "93453595-20771");
  3759. sRSPPolicyNumberSubstitutions.Add("20961", "93453595-20961");
  3760. sRSPPolicyNumberSubstitutions.Add("22304", "93453595-22304");
  3761. sRSPPolicyNumberSubstitutions.Add("22725", "93453595-22725");
  3762. sRSPPolicyNumberSubstitutions.Add("23678", "93453595-23678");
  3763. sRSPPolicyNumberSubstitutions.Add("23714", "93453595-23714");
  3764. sRSPPolicyNumberSubstitutions.Add("23924", "93453595-23924");
  3765. sRSPPolicyNumberSubstitutions.Add("23989", "93453595-23989");
  3766. sRSPPolicyNumberSubstitutions.Add("24057", "93453595-24057");
  3767. sRSPPolicyNumberSubstitutions.Add("24235", "93453595-24235");
  3768. sRSPPolicyNumberSubstitutions.Add("24274", "93453595-24274");
  3769. sRSPPolicyNumberSubstitutions.Add("24570", "93453595-24570");
  3770. sRSPPolicyNumberSubstitutions.Add("24662", "93453595-24662");
  3771. sRSPPolicyNumberSubstitutions.Add("24705", "93453595-24705");
  3772. sRSPPolicyNumberSubstitutions.Add("24875", "93453595-24875");
  3773. sRSPPolicyNumberSubstitutions.Add("24877", "93453595-24877");
  3774. sRSPPolicyNumberSubstitutions.Add("25139", "93453595-25139");
  3775. sRSPPolicyNumberSubstitutions.Add("25457", "93453595-25457");
  3776. sRSPPolicyNumberSubstitutions.Add("30439", "93453595-30439");
  3777. sRSPPolicyNumberSubstitutions.Add("31061", "93453595-31061");
  3778. sRSPPolicyNumberSubstitutions.Add("31529", "93453595-31529");
  3779. sRSPPolicyNumberSubstitutions.Add("31717", "93453595-31717");
  3780. sRSPPolicyNumberSubstitutions.Add("32121", "93453595-32121");
  3781. sRSPPolicyNumberSubstitutions.Add("32851", "93453595-32851");
  3782. sRSPPolicyNumberSubstitutions.Add("33465", "93453595-33465");
  3783. sRSPPolicyNumberSubstitutions.Add("93453595- 33465", "93453595-33465");
  3784. sRSPPolicyNumberSubstitutions.Add("35453595-40496", "93453595-40496");
  3785. sRSPPolicyNumberSubstitutions.Add("41791", "93453595-41791");
  3786. sRSPPolicyNumberSubstitutions.Add("41811", "93453595-41811");
  3787. sRSPPolicyNumberSubstitutions.Add("42290", "93453595-42290");
  3788. sRSPPolicyNumberSubstitutions.Add("42521", "93453595-42521");
  3789. sRSPPolicyNumberSubstitutions.Add("42567", "93453595-42567");
  3790. sRSPPolicyNumberSubstitutions.Add("42743", "93453595-42743");
  3791. sRSPPolicyNumberSubstitutions.Add("43098", "93453595-43098");
  3792. sRSPPolicyNumberSubstitutions.Add("43153", "93453595-43153");
  3793. sRSPPolicyNumberSubstitutions.Add("43259", "93453595-43259");
  3794. sRSPPolicyNumberSubstitutions.Add("43662", "93453595-43662");
  3795. sRSPPolicyNumberSubstitutions.Add("43738", "93453595-43738");
  3796. sRSPPolicyNumberSubstitutions.Add("43900", "93453595-43900");
  3797. sRSPPolicyNumberSubstitutions.Add("44207", "93453595-44207");
  3798. sRSPPolicyNumberSubstitutions.Add("44659", "93453595-44659");
  3799. sRSPPolicyNumberSubstitutions.Add("44699", "93453595-44699");
  3800. sRSPPolicyNumberSubstitutions.Add("44755", "93453595-44755");
  3801. sRSPPolicyNumberSubstitutions.Add("45110", "93453595-45110");
  3802. sRSPPolicyNumberSubstitutions.Add("45176", "93453595-45176");
  3803. sRSPPolicyNumberSubstitutions.Add("45688", "93453595-45688");
  3804. sRSPPolicyNumberSubstitutions.Add("934535695-20070", "93453595-20070");
  3805. sRSPPolicyNumberSubstitutions.Add("93453595- 00020", "93453595-000020HLL");
  3806. sRSPPolicyNumberSubstitutions.Add("93453595- 000127", "93453595-000127HLL");
  3807. sRSPPolicyNumberSubstitutions.Add("93453595- 000224", "93453595-000224HLL");
  3808. sRSPPolicyNumberSubstitutions.Add("93453595- 00894", "93453595-000894HLL");
  3809. sRSPPolicyNumberSubstitutions.Add("93453595- 000951", "93453595-000951HLL");
  3810. sRSPPolicyNumberSubstitutions.Add("93453595- 000979", "93453595-000979HLL");
  3811. sRSPPolicyNumberSubstitutions.Add("93453595- 001050", "93453595-001050HLL");
  3812. sRSPPolicyNumberSubstitutions.Add("93453595 - 31825", "93453595-31825");
  3813. sRSPPolicyNumberSubstitutions.Add("93453595- 10312", "93453595-10312");
  3814. sRSPPolicyNumberSubstitutions.Add("93453595- 10344", "93453595-10344");
  3815. sRSPPolicyNumberSubstitutions.Add("93453595- 11394", "93453595-11394");
  3816. sRSPPolicyNumberSubstitutions.Add("93453595 11732", "93453595-11732");
  3817. sRSPPolicyNumberSubstitutions.Add("93453595- 12005", "93453595-12005");
  3818. sRSPPolicyNumberSubstitutions.Add("93453595- 12519", "93453595-12519");
  3819. sRSPPolicyNumberSubstitutions.Add("93453595- 13437", "93453595-13437");
  3820. sRSPPolicyNumberSubstitutions.Add("93453595- 13450", "93453595-13450");
  3821. sRSPPolicyNumberSubstitutions.Add("93453595- 13580", "93453595-13580");
  3822. sRSPPolicyNumberSubstitutions.Add("93453595- 13651", "93453595-13651");
  3823. sRSPPolicyNumberSubstitutions.Add("93453595- 14573", "93453595-14573");
  3824. sRSPPolicyNumberSubstitutions.Add("93453595- 15286", "93453595-15286");
  3825. sRSPPolicyNumberSubstitutions.Add("93453595- 16292", "93453595-16292");
  3826. sRSPPolicyNumberSubstitutions.Add("93453595- 16885", "93453595-16885");
  3827. sRSPPolicyNumberSubstitutions.Add("93453595- 17618", "93453595-17618");
  3828. sRSPPolicyNumberSubstitutions.Add("93453595- 18183", "93453595-18183");
  3829. sRSPPolicyNumberSubstitutions.Add("93453595- 18296", "93453595-18296");
  3830. sRSPPolicyNumberSubstitutions.Add("93453595- 18331", "93453595-18331");
  3831. sRSPPolicyNumberSubstitutions.Add("93453595- 18830", "93453595-18830");
  3832. sRSPPolicyNumberSubstitutions.Add("93453595- 19410", "93453595-19410");
  3833. sRSPPolicyNumberSubstitutions.Add("93453595- 19703", "93453595-19703");
  3834. sRSPPolicyNumberSubstitutions.Add("93453595- 19931", "93453595-19931");
  3835. sRSPPolicyNumberSubstitutions.Add("93453595- 20049", "93453595-20049");
  3836. sRSPPolicyNumberSubstitutions.Add("93453595- 20089", "93453595-20089");
  3837. sRSPPolicyNumberSubstitutions.Add("93453595- 20132", "93453595-20132");
  3838. sRSPPolicyNumberSubstitutions.Add("93453595- 20268", "93453595-20268");
  3839. sRSPPolicyNumberSubstitutions.Add("93453595- 20683", "93453595-20683");
  3840. sRSPPolicyNumberSubstitutions.Add("93453595- 20716", "93453595-20716");
  3841. sRSPPolicyNumberSubstitutions.Add("93453595- 20924", "93453595-20924");
  3842. sRSPPolicyNumberSubstitutions.Add("93453595- 20938", "93453595-20938");
  3843. sRSPPolicyNumberSubstitutions.Add("93453595- 21125", "93453595-21125");
  3844. sRSPPolicyNumberSubstitutions.Add("93453595- 21537", "93453595-21537");
  3845. sRSPPolicyNumberSubstitutions.Add("93453595- 21894", "93453595-21894");
  3846. sRSPPolicyNumberSubstitutions.Add("93453595- 22700", "93453595-22700");
  3847. sRSPPolicyNumberSubstitutions.Add("93453595- 23112", "93453595-23112");
  3848. sRSPPolicyNumberSubstitutions.Add("93453595- 23383", "93453595-23383");
  3849. sRSPPolicyNumberSubstitutions.Add("93453595- 23430", "93453595-23430");
  3850. sRSPPolicyNumberSubstitutions.Add("93453595- 23640", "93453595-23640");
  3851. sRSPPolicyNumberSubstitutions.Add("93453595- 23835", "93453595-23835");
  3852. sRSPPolicyNumberSubstitutions.Add("93453595- 24011", "93453595-24011");
  3853. sRSPPolicyNumberSubstitutions.Add("93453595- 24070", "93453595-24070");
  3854. sRSPPolicyNumberSubstitutions.Add("93453595- 24098", "93453595-24098");
  3855. sRSPPolicyNumberSubstitutions.Add("93453595- 24202", "93453595-24202");
  3856. sRSPPolicyNumberSubstitutions.Add("93453595- 24470", "93453595-24470");
  3857. sRSPPolicyNumberSubstitutions.Add("93453595- 24695", "93453595-24695");
  3858. sRSPPolicyNumberSubstitutions.Add("93453595- 24751", "93453595-24751");
  3859. sRSPPolicyNumberSubstitutions.Add("93453595- 24783", "93453595-24783");
  3860. sRSPPolicyNumberSubstitutions.Add("93453595- 24804", "93453595-24804");
  3861. sRSPPolicyNumberSubstitutions.Add("93453595- 25167", "93453595-25167");
  3862. sRSPPolicyNumberSubstitutions.Add("93453595- 25575", "93453595-25575");
  3863. sRSPPolicyNumberSubstitutions.Add("93453595- 25666", "93453595-25666");
  3864. sRSPPolicyNumberSubstitutions.Add("93453595- 30411", "93453595-30411");
  3865. sRSPPolicyNumberSubstitutions.Add("93453595 31765", "93453595-31765");
  3866. sRSPPolicyNumberSubstitutions.Add("93453595- 32478", "93453595-32478");
  3867. sRSPPolicyNumberSubstitutions.Add("93453595- 32590", "93453595-32590");
  3868. sRSPPolicyNumberSubstitutions.Add("93453595- 33142", "93453595-33142");
  3869. sRSPPolicyNumberSubstitutions.Add("93453595- 33290", "93453595-33290");
  3870. sRSPPolicyNumberSubstitutions.Add("93453595- 40552", "93453595-40552");
  3871. sRSPPolicyNumberSubstitutions.Add("93453595- 41632", "93453595-41632");
  3872. sRSPPolicyNumberSubstitutions.Add("93453595- 41992", "93453595-41992");
  3873. sRSPPolicyNumberSubstitutions.Add("93453595- 42343", "93453595-42343");
  3874. sRSPPolicyNumberSubstitutions.Add("93453595 42385", "93453595-42385");
  3875. sRSPPolicyNumberSubstitutions.Add("93453595- 42454", "93453595-42454");
  3876. sRSPPolicyNumberSubstitutions.Add("93453595- 42553", "93453595-42553");
  3877. sRSPPolicyNumberSubstitutions.Add("93453595- 42707", "93453595-42707");
  3878. sRSPPolicyNumberSubstitutions.Add("93453595- 42807", "93453595-42807");
  3879. sRSPPolicyNumberSubstitutions.Add("93453595- 42959", "93453595-42959");
  3880. sRSPPolicyNumberSubstitutions.Add("93453595- 43079", "93453595-43079");
  3881. sRSPPolicyNumberSubstitutions.Add("93453595- 43380", "93453595-43380");
  3882. sRSPPolicyNumberSubstitutions.Add("93453595- 43568", "93453595-43568");
  3883. sRSPPolicyNumberSubstitutions.Add("93453595- 43897", "93453595-43897");
  3884. sRSPPolicyNumberSubstitutions.Add("93453595- 43900", "93453595-43900");
  3885. sRSPPolicyNumberSubstitutions.Add("93453595- 43907", "93453595-43907");
  3886. sRSPPolicyNumberSubstitutions.Add("93453595- 44001", "93453595-44001");
  3887. sRSPPolicyNumberSubstitutions.Add("93453595- 44152", "93453595-44152");
  3888. sRSPPolicyNumberSubstitutions.Add("93453595- 44217", "93453595-44217");
  3889. sRSPPolicyNumberSubstitutions.Add("93453595- 44306", "93453595-44306");
  3890. sRSPPolicyNumberSubstitutions.Add("93453595- 44413", "93453595-44413");
  3891. sRSPPolicyNumberSubstitutions.Add("93453595- 44518", "93453595-44518");
  3892. sRSPPolicyNumberSubstitutions.Add("93453595- 44561", "93453595-44561");
  3893. sRSPPolicyNumberSubstitutions.Add("93453595- 44742", "93453595-44742");
  3894. sRSPPolicyNumberSubstitutions.Add("93453595- 44799", "93453595-44799");
  3895. sRSPPolicyNumberSubstitutions.Add("93453595- 44828", "93453595-44828");
  3896. sRSPPolicyNumberSubstitutions.Add("93453595- 45418", "93453595-45418");
  3897. sRSPPolicyNumberSubstitutions.Add("93453595- 45570", "93453595-45570");
  3898. sRSPPolicyNumberSubstitutions.Add("93453595- 45785", "93453595-45785");
  3899. sRSPPolicyNumberSubstitutions.Add("93453595- 45812", "93453595-45812");
  3900. sRSPPolicyNumberSubstitutions.Add("93453595- 46049", "93453595-46049");
  3901. sRSPPolicyNumberSubstitutions.Add("9345359519906", "93453595-19906");
  3902. sRSPPolicyNumberSubstitutions.Add("93453595-30834 (9a)", "93453595-30834");
  3903. sRSPPolicyNumberSubstitutions.Add("93453595-30835 (9b)", "93453595-30835");
  3904. sRSPPolicyNumberSubstitutions.Add("93453595-43952 (u 5)", "93453595-43952");
  3905. sRSPPolicyNumberSubstitutions.Add("9345395-22956", "93453595-22956");
  3906. sRSPPolicyNumberSubstitutions.Add("higstr-000096- lodged incorrectly", "HIGSTR-000096");
  3907. sRSPPolicyNumberSubstitutions.Add("hu0029720\r\npolicy no:\r\nthe insured:\r\nstrata title - residential\r\nfrom 1/01/2017 to 1/01/2018\r\nhu0029720", "HU0029720");
  3908.  
  3909. // Property Claims
  3910. //sPRCPolicyNumberSubstitutions.Add();
  3911.  
  3912. // GCC Claims
  3913. //sGCCPolicyNumberSubstitutions.Add();
  3914. }
  3915. }
  3916.  
  3917. //--------------------------------------------------------------------------------
  3918. private static object PolicyNumber(object policyNumber) {
  3919. if (policyNumber == DBNull.Value)
  3920. return DBNull.Value;
  3921. if (policyNumber == null)
  3922. return null;
  3923. string policyNumberString = ((string)policyNumber).ToLower();
  3924.  
  3925. InitialisePolicyNumberSubstitutions();
  3926. if (sPolicyNumberSubstitutions.ContainsKey(policyNumberString))
  3927. return sPolicyNumberSubstitutions[policyNumberString];
  3928. else {
  3929. if ((sTeam == Team.RisksmartGCC) && sRGCPolicyNumberSubstitutions.ContainsKey(policyNumberString))
  3930. return sRGCPolicyNumberSubstitutions[policyNumberString];
  3931. else if ((sTeam == Team.RisksmartProperty) && sRSPPolicyNumberSubstitutions.ContainsKey(policyNumberString))
  3932. return sRSPPolicyNumberSubstitutions[policyNumberString];
  3933. else if ((sTeam == Team.PropertyClaims) && sPRCPolicyNumberSubstitutions.ContainsKey(policyNumberString))
  3934. return sPRCPolicyNumberSubstitutions[policyNumberString];
  3935. else if ((sTeam == Team.GCCClaims) && sGCCPolicyNumberSubstitutions.ContainsKey(policyNumberString))
  3936. return sGCCPolicyNumberSubstitutions[policyNumberString];
  3937. }
  3938.  
  3939. // No substitute
  3940. return policyNumber;
  3941. }
  3942.  
  3943.  
  3944. // ACCOUNT NAME SUBSTITUTIONS ================================================================================
  3945. //--------------------------------------------------------------------------------
  3946. private static void InitialiseAccountNameSubstitutions() {
  3947. if (sAccountNameSubstitutions.Count <= 0) {
  3948. sAccountNameSubstitutions.Add("accident & health international", "Accidental & Health International");
  3949. sAccountNameSubstitutions.Add("aldi - recovery settlement to aldi", "ALDI");
  3950. sAccountNameSubstitutions.Add("allianz", "Allianz Australia Insurance Limited");
  3951. sAccountNameSubstitutions.Add("bendigo district rsl", "Bendigo District RSL Sub-Branch Inc.");
  3952. sAccountNameSubstitutions.Add("box hill rsl", "Box Hill RSL Sub-Branch Inc");
  3953. sAccountNameSubstitutions.Add("catlin", "XL Catlin");
  3954. sAccountNameSubstitutions.Add("chu", "CHU-NSW");
  3955. sAccountNameSubstitutions.Add("chubb insurance", "Chubb Insurance Australia Ltd");
  3956. sAccountNameSubstitutions.Add("duke of edinburgh", "The Duke Of Edinburghs International Award");
  3957. sAccountNameSubstitutions.Add("ezko property services (aust) pty ltd", "Ezko");
  3958. sAccountNameSubstitutions.Add("ezko property services (aust) retail pty ltd", "Ezko");
  3959. sAccountNameSubstitutions.Add("folkestone limited", "Folkestone");
  3960. sAccountNameSubstitutions.Add("gjk facility services", "GJ&K Cleaning Service Pty Ltd");
  3961. sAccountNameSubstitutions.Add("guild insurance", "Guild Insurance Ltd");
  3962. sAccountNameSubstitutions.Add("honan insurance brokers", "Honan Insurance Group Pty Ltd");
  3963. //sAccountNameSubstitutions.Add("honan insurance group", "Honan Insurance Group Pty Ltd");
  3964. sAccountNameSubstitutions.Add("Lloyds Honan LL", "Lloyds Honan");
  3965. sAccountNameSubstitutions.Add("mecon winsure", "Mecon Insurance Group");
  3966. sAccountNameSubstitutions.Add("miller insurance", "Millers");
  3967. sAccountNameSubstitutions.Add("qbe", "QBE Insurance (Australia) Ltd - Melbourne");
  3968. sAccountNameSubstitutions.Add("romak hardware distributors (aust) pty ltd", "Romak Hardware Distributors (Aust) Pty Ltd");
  3969. sAccountNameSubstitutions.Add("whirlpool", "Whirlpool (Australia) Pty Ltd");
  3970. sAccountNameSubstitutions.Add("xl insurance", "XL Insurance Company SE (Australian Branch)");
  3971. sAccountNameSubstitutions.Add("xl", "XL Insurance Company SE (Australian Branch)");
  3972.  
  3973. sAccountNameSubstitutions.Add("self insured", null);
  3974. sAccountNameSubstitutions.Add("offshore market placement", null);
  3975. }
  3976. }
  3977.  
  3978. //--------------------------------------------------------------------------------
  3979. private static object AccountName(object accountName) {
  3980. if (accountName == DBNull.Value)
  3981. return DBNull.Value;
  3982. if (accountName == null)
  3983. return null;
  3984. string accountNameString = ((string)accountName).ToLower();
  3985.  
  3986. InitialiseAccountNameSubstitutions();
  3987. if (sAccountNameSubstitutions.ContainsKey(accountNameString))
  3988. return sAccountNameSubstitutions[accountNameString];
  3989. else
  3990. return accountName;
  3991. }
  3992.  
  3993.  
  3994. // CUSTOMER CODE SUBSTITUTIONS ================================================================================
  3995. //--------------------------------------------------------------------------------
  3996. private static void InitialiseCustomerCodeSubstitutions() {
  3997. if (sCustomerCodeSubstitutions.Count <= 0) {
  3998. //sCodeSubstitutions.Add("lloyds", "Accidental & Health International");
  3999. }
  4000. }
  4001.  
  4002. //--------------------------------------------------------------------------------
  4003. private static object CustomerCode(object customerCode) {
  4004. if (customerCode == DBNull.Value)
  4005. return DBNull.Value;
  4006. if (customerCode == null)
  4007. return null;
  4008. string customerCodeString = ((string)customerCode).ToLower();
  4009.  
  4010. InitialiseCustomerCodeSubstitutions();
  4011. if (sCustomerCodeSubstitutions.ContainsKey(customerCodeString))
  4012. return sCustomerCodeSubstitutions[customerCodeString];
  4013. else
  4014. return customerCode;
  4015. }
  4016.  
  4017.  
  4018. // UNDERWRITER CODE SUBSTITUTIONS ================================================================================
  4019. //--------------------------------------------------------------------------------
  4020. private static void InitialiseUnderwriterCodeSubstitutions() {
  4021. if (sUnderwriterCodeSubstitutions.Count <= 0) {
  4022. sUnderwriterCodeSubstitutions.Add("lloyds", "LLOYDS HON");
  4023. sUnderwriterCodeSubstitutions.Add("chubb ll", "CHUBB");
  4024. //sUnderwriterCodeSubstitutions.Add("guild ll", "guild");
  4025. sUnderwriterCodeSubstitutions.Add("wrbk - nsw", "WRBERK");
  4026. sUnderwriterCodeSubstitutions.Add("mob - vic", "MOB - NSW");
  4027. sUnderwriterCodeSubstitutions.Add("vero - vic", "VERO");
  4028. sUnderwriterCodeSubstitutions.Add("vero - nsw", "VERO");
  4029. sUnderwriterCodeSubstitutions.Add("qbe", "QBEC");
  4030. sUnderwriterCodeSubstitutions.Add("cgu", "CGU - VIC");
  4031. sUnderwriterCodeSubstitutions.Add("chu", "CHU - VIC");
  4032. }
  4033. }
  4034.  
  4035. //--------------------------------------------------------------------------------
  4036. private static object UnderwriterCode(object underwriterCode) {
  4037. if (underwriterCode == DBNull.Value)
  4038. return DBNull.Value;
  4039. if (underwriterCode == null)
  4040. return null;
  4041. string underwriterCodeString = ((string)underwriterCode).ToLower();
  4042.  
  4043. InitialiseUnderwriterCodeSubstitutions();
  4044. if (sUnderwriterCodeSubstitutions.ContainsKey(underwriterCodeString))
  4045. return sUnderwriterCodeSubstitutions[underwriterCodeString];
  4046. else
  4047. return underwriterCodeString;
  4048. }
  4049.  
  4050.  
  4051. // ASSOCIATE CODE SUBSTITUTIONS ================================================================================
  4052. //--------------------------------------------------------------------------------
  4053. private static void InitialiseAssociateCodeSubstitutions() {
  4054. if (sAssociateCodeSubstitutions.Count <= 0) {
  4055. //sAssociateCodeSubstitutions.Add("lloyds", "LLOYDS HON");
  4056. }
  4057. }
  4058.  
  4059. //--------------------------------------------------------------------------------
  4060. private static object AssociateCode(object associateCode) {
  4061. if (associateCode == DBNull.Value)
  4062. return DBNull.Value;
  4063. if (associateCode == null)
  4064. return null;
  4065. string associateCodeString = ((string)associateCode).ToLower();
  4066.  
  4067. InitialiseAssociateCodeSubstitutions();
  4068. if (sAssociateCodeSubstitutions.ContainsKey(associateCodeString))
  4069. return sAssociateCodeSubstitutions[associateCodeString];
  4070. else
  4071. return associateCodeString;
  4072. }
  4073.  
  4074.  
  4075. // USER NAME SUBSTITUTIONS ================================================================================
  4076. //--------------------------------------------------------------------------------
  4077. private static void InitialiseUserNameSubstitutions() {
  4078. if (sUserNameSubstitutions.Count <= 0) {
  4079. sUserNameSubstitutions.Add("admin", "Administrator");
  4080. sUserNameSubstitutions.Add("aneta chlond", "Ms Aneta Chlond");
  4081. sUserNameSubstitutions.Add("basia", "Ms Basia Harcombe");
  4082. sUserNameSubstitutions.Add("basiah", "Ms Basia Harcombe");
  4083. sUserNameSubstitutions.Add("basia harcombe", "Ms Basia Harcombe");
  4084. sUserNameSubstitutions.Add("clarisa", "Ms Clarissa Morataya");
  4085. sUserNameSubstitutions.Add("clarissa", "Ms Clarissa Morataya");
  4086. sUserNameSubstitutions.Add("clarissam", "Ms Clarissa Morataya");
  4087. sUserNameSubstitutions.Add("dak", "Mr Dak Wick");
  4088. sUserNameSubstitutions.Add("dakq", "Mr Dak Wick");
  4089. sUserNameSubstitutions.Add("dakw", "Mr Dak Wick");
  4090. sUserNameSubstitutions.Add("dale polard", "Dale Pollard");
  4091. sUserNameSubstitutions.Add("dalia i", "Ms Dalia Ismaiel");
  4092. sUserNameSubstitutions.Add("dalia ismaeil", "Ms Dalia Ismaiel");
  4093. sUserNameSubstitutions.Add("dalia ismaiel", "Ms Dalia Ismaiel");
  4094. sUserNameSubstitutions.Add("daliai", "Ms Dalia Ismaiel");
  4095. sUserNameSubstitutions.Add("gabby", "Ms Gabriell Wheeler");
  4096. sUserNameSubstitutions.Add("gabriell", "Ms Gabriell Wheeler");
  4097. sUserNameSubstitutions.Add("gabreillw", "Ms Gabriell Wheeler");
  4098. sUserNameSubstitutions.Add("gabriellw", "Ms Gabriell Wheeler");
  4099. sUserNameSubstitutions.Add("johnh", "Mr John Hawkins");
  4100. sUserNameSubstitutions.Add("jordank", "Mr Jordan Kelly");
  4101. sUserNameSubstitutions.Add("julian", "Julian Vyas");
  4102. sUserNameSubstitutions.Add("maja", "Ms Maja Vujinovic");
  4103. sUserNameSubstitutions.Add("majav", "Ms Maja Vujinovic");
  4104. sUserNameSubstitutions.Add("manish", "Mr Manish Sharma");
  4105. sUserNameSubstitutions.Add("manishs", "Mr Manish Sharma");
  4106. sUserNameSubstitutions.Add("mary", "Ms Mary Matias");
  4107. sUserNameSubstitutions.Add("mary matias", "Ms Mary Matias");
  4108. sUserNameSubstitutions.Add("marynm", "Ms Mary Matias");
  4109. sUserNameSubstitutions.Add("marym", "Ms Mary Matias");
  4110. sUserNameSubstitutions.Add("megan eacock", "Ms Megan Peacock");
  4111. sUserNameSubstitutions.Add("megan peacock", "Ms Megan Peacock");
  4112. sUserNameSubstitutions.Add("megan peacok", "Ms Megan Peacock");
  4113. sUserNameSubstitutions.Add("meganpeacock", "Ms Megan Peacock");
  4114. sUserNameSubstitutions.Add("megn peacock", "Ms Megan Peacock");
  4115. sUserNameSubstitutions.Add("nicole anderso", "Ms Nicole Anderson");
  4116. sUserNameSubstitutions.Add("nicole anderson", "Ms Nicole Anderson");
  4117. sUserNameSubstitutions.Add("perrie", "Mr Perry Xie");
  4118. sUserNameSubstitutions.Add("perry", "Mr Perry Xie");
  4119. sUserNameSubstitutions.Add("perry xie", "Mr Perry Xie");
  4120. sUserNameSubstitutions.Add("poppu", "Ms Poppy Foxton");
  4121. sUserNameSubstitutions.Add("poppy", "Ms Poppy Foxton");
  4122. sUserNameSubstitutions.Add("poppy foxton", "Ms Poppy Foxton");
  4123. sUserNameSubstitutions.Add("poppyf", "Ms Poppy Foxton");
  4124. sUserNameSubstitutions.Add("shelleyt", "Shelley Thompson");
  4125. sUserNameSubstitutions.Add("simon", "Simon Biggar");
  4126. sUserNameSubstitutions.Add("sosina", "Ms Sosina Payne");
  4127. sUserNameSubstitutions.Add("sosina payne", "Ms Sosina Payne");
  4128. sUserNameSubstitutions.Add("stan", "Mr Stan Ogrizek");
  4129. sUserNameSubstitutions.Add("stano", "Mr Stan Ogrizek");
  4130. sUserNameSubstitutions.Add("taras", "Ms Tara Strangwick");
  4131. sUserNameSubstitutions.Add("vaerie t", "Valerie Tuyau");
  4132. sUserNameSubstitutions.Add("valerie t", "Valerie Tuyau");
  4133. sUserNameSubstitutions.Add("yannick", "Mr Yannick Donnelly");
  4134. sUserNameSubstitutions.Add("yannickd", "Mr Yannick Donnelly");
  4135. }
  4136. }
  4137.  
  4138. //--------------------------------------------------------------------------------
  4139. private static object UserName(object userName) {
  4140. if (userName == DBNull.Value)
  4141. return DBNull.Value;
  4142. if (userName == null)
  4143. return null;
  4144. string userNameString = ((string)userName).ToLower();
  4145.  
  4146. InitialiseUserNameSubstitutions();
  4147. if (sUserNameSubstitutions.ContainsKey(userNameString))
  4148. return sUserNameSubstitutions[userNameString];
  4149. else
  4150. return userName;
  4151. }
  4152.  
  4153.  
  4154. // STRINGS ================================================================================
  4155. //--------------------------------------------------------------------------------
  4156. public static bool StringContains(string value, string[] contains) {
  4157. foreach (string c in contains) {
  4158. if (value.Contains(c))
  4159. return true;
  4160. }
  4161. return false;
  4162. }
  4163.  
  4164.  
  4165. // MISSING ================================================================================
  4166. //--------------------------------------------------------------------------------
  4167. private static void AddMissing(Dictionary<string, Tuple<int, int>> dictionary, string name, bool openClaim) {
  4168. if (!dictionary.ContainsKey(name))
  4169. dictionary.Add(name, new Tuple<int, int>(openClaim ? 1 : 0, !openClaim ? 1 : 0));
  4170. else if (openClaim)
  4171. dictionary[name] = new Tuple<int, int>(dictionary[name].Item1 + 1, dictionary[name].Item2);
  4172. else
  4173. dictionary[name] = new Tuple<int, int>(dictionary[name].Item1, dictionary[name].Item2 + 1);
  4174. }
  4175.  
  4176. //--------------------------------------------------------------------------------
  4177. private static void AddMissingAccount(string name, bool openClaim = false) { AddMissing(sMissingAccounts, name, openClaim); }
  4178. private static void AddMissingAssociate(string name, bool openClaim = false) { AddMissing(sMissingAssociates, name, openClaim); }
  4179. private static void AddMissingUnderwriter(string name, bool openClaim = false) { AddMissing(sMissingUnderwriters, name, openClaim); }
  4180. private static void AddMissingCustomer(string name, bool openClaim = false) { AddMissing(sMissingCustomers, name, openClaim); }
  4181. private static void AddMissingPolicy(string name, bool openClaim = false) { AddMissing(sMissingPolicies, name, openClaim); }
  4182. private static void AddMissingClaim(string name, bool openClaim = false) { AddMissing(sMissingClaims, name, openClaim); }
  4183. private static void AddMissingUser(string name, bool openClaim = false) { AddMissing(sMissingUsers, name, openClaim); }
  4184.  
  4185. //--------------------------------------------------------------------------------
  4186. private static void LogMissingSummary(string description) {
  4187. LogSummary("\n--------------------------------------------------------------------------------");
  4188. LogSummary("MISSING SUMMARY (" + description + "):");
  4189. LogMissingSummary("ACCOUNTS", sMissingAccounts);
  4190. LogMissingSummary("ASSOCIATES (open, closed/notification)", sMissingAssociates);
  4191. LogMissingSummary("UNDERWRITERS (open, closed/notification)", sMissingUnderwriters);
  4192. LogMissingSummary("CUSTOMERS (open, closed/notification)", sMissingCustomers);
  4193. LogMissingSummary("POLICIES (open, closed/notification)", sMissingPolicies);
  4194. LogMissingSummary("CLAIMS", sMissingClaims);
  4195. LogMissingSummary("USERS", sMissingUsers);
  4196. }
  4197.  
  4198. //--------------------------------------------------------------------------------
  4199. private static void LogMissingSummary(string heading, Dictionary<string, Tuple<int, int>> dictionary) {
  4200. // Checks
  4201. if (dictionary.Count <= 0)
  4202. return;
  4203.  
  4204. // Heading
  4205. LogSummary("\n " + heading + ":");
  4206.  
  4207. // Summary
  4208. // Open
  4209. foreach (KeyValuePair<string, Tuple<int, int>> m in dictionary) {
  4210. if (m.Value.Item1 > 0)
  4211. LogSummary(" '" + m.Key + "' (" + m.Value.Item1 + ", " + m.Value.Item2 + ")");
  4212. }
  4213.  
  4214. // Closed
  4215. foreach (KeyValuePair<string, Tuple<int, int>> m in dictionary) {
  4216. if (m.Value.Item1 <= 0)
  4217. LogSummary(" '" + m.Key + "' (" + m.Value.Item1 + ", " + m.Value.Item2 + ")");
  4218. }
  4219.  
  4220. // Clear
  4221. dictionary.Clear();
  4222. }
  4223.  
  4224.  
  4225. // LOGGING ================================================================================
  4226. //--------------------------------------------------------------------------------
  4227. private static void OpenLog(string filename) {
  4228. // Open
  4229. try { sLogWriter = new StreamWriter(filename); }
  4230. catch (Exception ex) { Log("Failed to open log: " + ex.Message); }
  4231. }
  4232.  
  4233. //--------------------------------------------------------------------------------
  4234. private static void CloseLog() {
  4235. if (sLogWriter != null) {
  4236. sLogWriter.Close();
  4237. sLogWriter = null;
  4238. }
  4239. }
  4240.  
  4241. //--------------------------------------------------------------------------------
  4242. private static void OpenSummaryLog(string filename) {
  4243. // Open
  4244. try { sSummaryLogWriter = new StreamWriter(filename, true); }
  4245. catch (Exception ex) { Log("Failed to open summary log: " + ex.Message); }
  4246. }
  4247.  
  4248. //--------------------------------------------------------------------------------
  4249. private static void CloseSummaryLog() {
  4250. if (sSummaryLogWriter != null) {
  4251. sSummaryLogWriter.Close();
  4252. sSummaryLogWriter = null;
  4253. }
  4254. }
  4255.  
  4256. //--------------------------------------------------------------------------------
  4257. public static void Log(string message) {
  4258. string output = (sLine > 0 ? "[" + sLine + "] " : "") + message;
  4259. if (sLogWriter != null)
  4260. sLogWriter.Write(output + "\n");
  4261. Console.WriteLine(output);
  4262. //Debug.WriteLine(output);
  4263. }
  4264.  
  4265. //--------------------------------------------------------------------------------
  4266. public static void LogError(string message) {
  4267. string output = (sLine > 0 ? "[" + sLine + "] " : "") + "ERROR: "+ message;
  4268. if (sLogWriter != null)
  4269. sLogWriter.Write(output + "\n");
  4270. Console.WriteLine(output);
  4271. //Debug.WriteLine(output);
  4272. }
  4273.  
  4274. //--------------------------------------------------------------------------------
  4275. public static void LogWarning(string message) {
  4276. string output = (sLine > 0 ? "[" + sLine + "] " : "") + "WARNING: " + message;
  4277. if (sLogWriter != null)
  4278. sLogWriter.Write(output + "\n");
  4279. Console.WriteLine(output);
  4280. //Debug.WriteLine(output);
  4281. }
  4282.  
  4283. //--------------------------------------------------------------------------------
  4284. public static void LogSummary(string message) {
  4285. if (sLogWriter != null)
  4286. sLogWriter.Write(message + "\n");
  4287. if (sSummaryLogWriter != null)
  4288. sSummaryLogWriter.Write(message + "\n");
  4289. Console.WriteLine(message);
  4290. //Debug.WriteLine(message);
  4291. }
  4292.  
  4293.  
  4294. //================================================================================
  4295. //********************************************************************************
  4296. public class ImportConfig {
  4297. public Team team;
  4298. public string accessClaimAmendmentsCSVPath = ""; // risksmart gcc
  4299. public string accountsContactsCSVPath = ""; // risksmart gcc
  4300. public string policiesCSVPath = ""; // risksmart gcc
  4301. public string timeslipsCSVPath = ""; // risksmart gcc
  4302. public string keyContactsCSVPath = ""; // risksmart gcc
  4303. public string paymentsCSVPath = ""; // access
  4304. public string reservesCSVPath = ""; // access
  4305. public bool importClaims = true; // risksmart gcc, risksmart property, property claims, gcc claims
  4306. public bool updateWinBEATClaims = false;
  4307.  
  4308. public ImportConfig(Team team = Team.None) {
  4309. this.team = team;
  4310. }
  4311. }
  4312. }
  4313.  
  4314. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!