private static void GetLedgerCurrentYear(string ALedgerNumber, out string ALedgerYearCurrentYear) { // load the file a_ledger.d.gz TTable LedgerTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("a_ledger"); TParseProgressCSV Parser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "a_ledger.d.gz", LedgerTableOld.grpTableField.Count); StringCollection LedgerColumnNames = GetColumnNames(LedgerTableOld); ALedgerYearCurrentYear = "0"; while (true) { string[] OldRow = Parser.ReadNextRow(); if (OldRow == null) { break; } if (GetValue(LedgerColumnNames, OldRow, "a_ledger_number_i") == ALedgerNumber) { ALedgerYearCurrentYear = GetValue(LedgerColumnNames, OldRow, "a_current_financial_year_i"); break; } } }
private static string GetSupplierCurrency(Int64 SupplierPartnerKey) { if (SupplierCurrencies == null) { SupplierCurrencies = new SortedList <long, string>(); // read supplier currencies from a_ap_supplier file TTable supplierTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("a_ap_supplier"); TParseProgressCSV Parser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "a_ap_supplier.d.gz", supplierTableOld.grpTableField.Count); StringCollection ColumnNames = GetColumnNames(supplierTableOld); while (true) { string[] OldRow = Parser.ReadNextRow(); if (OldRow == null) { break; } SupplierCurrencies.Add(Convert.ToInt64(GetValue(ColumnNames, OldRow, "p_partner_key_n")), GetValue(ColumnNames, OldRow, "a_currency_code_c")); } } return(SupplierCurrencies[SupplierPartnerKey]); }
private static void LoadLedgersCurrentFinancialYear() { LedgersCurrentFinancialYear = new SortedList <string, string>(); // load a_ledger table and a_accounting_period TTable ledgerTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("a_ledger"); TParseProgressCSV Parser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "a_ledger.d.gz", ledgerTableOld.grpTableField.Count); StringCollection LedgerColumnNames = GetColumnNames(ledgerTableOld); while (true) { string[] OldRow = Parser.ReadNextRow(); if (OldRow == null) { break; } string LedgerNumber = GetValue(LedgerColumnNames, OldRow, "a_ledger_number_i"); string CurrentFinancialYear = GetValue(LedgerColumnNames, OldRow, "a_current_financial_year_i"); LedgersCurrentFinancialYear.Add(LedgerNumber, CurrentFinancialYear); } TTable accountingPeriodTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("a_accounting_period"); Parser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "a_accounting_period.d.gz", accountingPeriodTableOld.grpTableField.Count); StringCollection PeriodColumnNames = GetColumnNames(accountingPeriodTableOld); while (true) { string[] OldRow = Parser.ReadNextRow(); if (OldRow == null) { break; } string PeriodNumber = GetValue(PeriodColumnNames, OldRow, "a_accounting_period_number_i"); if (PeriodNumber == "1") { string LedgerNumber = GetValue(PeriodColumnNames, OldRow, "a_ledger_number_i"); string RealYear = GetValue(PeriodColumnNames, OldRow, "a_period_start_date_d").Substring(6, 4); LedgersCurrentFinancialYear[LedgerNumber] += "," + RealYear; } } }
private static void LoadLedgersCurrentFinancialYear() { LedgersCurrentFinancialYear = new SortedList <string, string>(); // load a_ledger table and a_accounting_period TTable ledgerTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("a_ledger"); TParseProgressCSV Parser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "a_ledger.d.gz", ledgerTableOld.grpTableField.Count); StringCollection LedgerColumnNames = GetColumnNames(ledgerTableOld); while (true) { string[] OldRow = Parser.ReadNextRow(); if (OldRow == null) { break; } string LedgerNumber = GetValue(LedgerColumnNames, OldRow, "a_ledger_number_i"); string CurrentFinancialYear = GetValue(LedgerColumnNames, OldRow, "a_current_financial_year_i"); LedgersCurrentFinancialYear.Add(LedgerNumber, CurrentFinancialYear); } TTable accountingPeriodTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("a_accounting_period"); Parser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "a_accounting_period.d.gz", accountingPeriodTableOld.grpTableField.Count); StringCollection PeriodColumnNames = GetColumnNames(accountingPeriodTableOld); while (true) { string[] OldRow = Parser.ReadNextRow(); if (OldRow == null) { break; } string PeriodNumber = GetValue(PeriodColumnNames, OldRow, "a_accounting_period_number_i"); if (PeriodNumber == "1") { string LedgerNumber = GetValue(PeriodColumnNames, OldRow, "a_ledger_number_i"); string RealYear = GetValue(PeriodColumnNames, OldRow, "a_period_start_date_d").Substring(6, 4); LedgersCurrentFinancialYear[LedgerNumber] += "," + RealYear; } } }
/// Get all ABudgetPeriod records for a budget sequence number private static void GetBudgetPeriods(TParseProgressCSV AParser, StringCollection ABudgetPeriodColumnNames, ref string[] AOldRow, string AOldSequenceNumber, out List <string[]> ABudgetPeriods) { // true once a record with the budget sequence number has been found bool BudgetSequenceFound = false; ABudgetPeriods = new List <string[]>(); while (true) { if (GetValue(ABudgetPeriodColumnNames, AOldRow, "a_budget_sequence_i") == AOldSequenceNumber) { ABudgetPeriods.Add(AOldRow); BudgetSequenceFound = true; } else if (BudgetSequenceFound) { // we have now parsed through all records for the budget sequence number return; } AOldRow = AParser.ReadNextRow(); if ((AOldRow == null) && !BudgetSequenceFound) { throw new Exception( "TFinanceBudgetUpgrader.GetBudgetYears: No ABudgetPeriod records found for ABudget sequence number: " + AOldSequenceNumber); } else if (AOldRow == null) { // reached end of table break; } } }
/// <summary> /// Load the data from the 2.x Petra CSV file, and create psql load file /// </summary> public void LoadTablesToPostgresql(string ATableName) { GetStoreOld(); GetStoreNew(); DataDefinitionDiff.newVersion = "3.0"; TTable.GEnabledLoggingMissingFields = false; TParseProgressCSV.InitProgressCodePage(); TSequenceWriter.InitSequences(GetStoreNew().GetSequences()); CreatePostcodeRegionRangeTable(); CreateGiftDestinationTable(); if (ATableName.Length == 0) { List <TTable> newTables = storeNew.GetTables(); foreach (TTable newTable in newTables) { LoadTable(newTable); } GC.Collect(); } else { LoadTable(storeNew.GetTable(ATableName)); } // write some tables, if they have been used TFinanceAccountsPayableUpgrader.WriteAPDocumentNumberToId(); TSequenceWriter.WriteSequences(); TLogging.Log("Success: finished exporting the data"); TTable.GEnabledLoggingMissingFields = true; }
private static string GetSupplierCurrencyFromPayment(Int32 ALedgerNumber, Int32 APaymentNumber) { if (CurrencyPerPayment == null) { SortedList <Int64, string> CurrencyPerDocument = new SortedList <Int64, string>(); TTable documentTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("a_ap_document"); TParseProgressCSV Parser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "a_ap_document.d.gz", documentTableOld.grpTableField.Count); StringCollection ColumnNames = GetColumnNames(documentTableOld); while (true) { string[] OldRow = Parser.ReadNextRow(); if (OldRow == null) { break; } Int64 LedgerAndAPNumber = Convert.ToInt64(GetValue(ColumnNames, OldRow, "a_ledger_number_i")) * 1000000 + Convert.ToInt64(GetValue(ColumnNames, OldRow, "a_ap_number_i")); CurrencyPerDocument.Add(LedgerAndAPNumber, GetSupplierCurrency(Convert.ToInt64(GetValue(ColumnNames, OldRow, "p_partner_key_n")))); } CurrencyPerPayment = new SortedList <long, string>(); TTable documentpaymentTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("a_ap_document_payment"); Parser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "a_ap_document_payment.d.gz", documentpaymentTableOld.grpTableField.Count); ColumnNames = GetColumnNames(documentpaymentTableOld); while (true) { string[] OldRow = Parser.ReadNextRow(); if (OldRow == null) { break; } Int64 LedgerAndPaymentNumber = Convert.ToInt64(GetValue(ColumnNames, OldRow, "a_ledger_number_i")) * 1000000 + Convert.ToInt64(GetValue(ColumnNames, OldRow, "a_payment_number_i")); if (!CurrencyPerPayment.ContainsKey(LedgerAndPaymentNumber)) { CurrencyPerPayment.Add(LedgerAndPaymentNumber, CurrencyPerDocument[ Convert.ToInt64(GetValue(ColumnNames, OldRow, "a_ledger_number_i")) * 1000000 + Convert.ToInt64(GetValue(ColumnNames, OldRow, "a_ap_number_i"))]); } } } return(CurrencyPerPayment[ALedgerNumber * 1000000 + APaymentNumber]); }
/// Get all ABudgetPeriod records for a budget sequence number private static void GetBudgetPeriods(TParseProgressCSV AParser, StringCollection ABudgetPeriodColumnNames, ref string[] AOldRow, string AOldSequenceNumber, out List <string[]>ABudgetPeriods) { // true once a record with the budget sequence number has been found bool BudgetSequenceFound = false; ABudgetPeriods = new List <string[]>(); while (true) { if (GetValue(ABudgetPeriodColumnNames, AOldRow, "a_budget_sequence_i") == AOldSequenceNumber) { ABudgetPeriods.Add(AOldRow); BudgetSequenceFound = true; } else if (BudgetSequenceFound) { // we have now parsed through all records for the budget sequence number return; } AOldRow = AParser.ReadNextRow(); if ((AOldRow == null) && !BudgetSequenceFound) { throw new Exception( "TFinanceBudgetUpgrader.GetBudgetYears: No ABudgetPeriod records found for ABudget sequence number: " + AOldSequenceNumber); } else if (AOldRow == null) { // reached end of table break; } } }
/// <summary> /// Populate the empty table ABudgetRevision using tables ABudget and ABudgetPeriod /// </summary> public static int PopulateABudgetRevision(StringCollection AColumnNames, ref string[] ANewRow, StreamWriter AWriter, StreamWriter AWriterTest) { // in Petra 2.x, there never has been a record in this table. // so if there is a budget, we need to create a revision 0 for each year // load the file a_budget.d.gz so that we can access the values for each budget TTable BudgetTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("a_budget"); TParseProgressCSV BudgetParser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "a_budget.d.gz", BudgetTableOld.grpTableField.Count); StringCollection BudgetColumnNames = GetColumnNames(BudgetTableOld); // load the file a_budget_period.d.gz TTable BudgetPeriodTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("a_budget_period"); TParseProgressCSV BudgetPeriodParser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "a_budget_period.d.gz", BudgetPeriodTableOld.grpTableField.Count); StringCollection BudgetPeriodColumnNames = GetColumnNames(BudgetPeriodTableOld); // read first row in ABudgetPeriod string[] OldBudgetPeriodRow = BudgetPeriodParser.ReadNextRow(); // [0] last year, [1] current year, [2] next year. Null if budget does not exist for a year. string[] BudgetYears = new string[3]; List <string[]> BudgetPeriodRows = new List <string[]>(); List <string> Revisions = new List <string>(); string LedgerNumber = string.Empty; int RowCounter = 0; SetValue(AColumnNames, ref ANewRow, "a_revision_i", "0"); SetValue(AColumnNames, ref ANewRow, "a_description_c", "default"); SetValue(AColumnNames, ref ANewRow, "s_date_created_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_created_by_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_date_modified_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_modified_by_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_modification_id_t", "\\N"); // one a_budget_period record for each year of each ledger while (true) { BudgetYears[0] = null; BudgetYears[2] = null; string[] OldRow = BudgetParser.ReadNextRow(); if (OldRow == null) { break; } if (LedgerNumber != GetValue(BudgetColumnNames, OldRow, "a_ledger_number_i")) { LedgerNumber = GetValue(BudgetColumnNames, OldRow, "a_ledger_number_i"); // gets the current financial year for the current ledger GetLedgerCurrentYear(LedgerNumber, out BudgetYears[1]); } // Only three possible years for each ledger. Hence only three a_budget_revision records per ledger. // Move on to next ledger if data for all three years is found. if (!Revisions.Contains(LedgerNumber + "_" + (Convert.ToInt32(BudgetYears[1]) - 1).ToString()) && !Revisions.Contains(LedgerNumber + "_" + (Convert.ToInt32(BudgetYears[1])).ToString()) && !Revisions.Contains(LedgerNumber + "_" + (Convert.ToInt32(BudgetYears[1]) + 1).ToString())) { GetBudgetPeriods(BudgetPeriodParser, BudgetPeriodColumnNames, ref OldBudgetPeriodRow, GetValue(BudgetColumnNames, OldRow, "a_budget_sequence_i"), out BudgetPeriodRows); GetBudgetYears(BudgetPeriodColumnNames, BudgetPeriodRows, ref BudgetYears); foreach (string BudgetYear in BudgetYears) { if ((BudgetYear != null) && !Revisions.Contains(LedgerNumber + "_" + BudgetYear)) { SetValue(AColumnNames, ref ANewRow, "a_ledger_number_i", LedgerNumber); SetValue(AColumnNames, ref ANewRow, "a_year_i", BudgetYear); AWriter.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); if (AWriterTest != null) { AWriterTest.WriteLine("BEGIN; " + "COPY a_budget_revision FROM stdin;"); AWriterTest.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); AWriterTest.WriteLine("\\."); AWriterTest.WriteLine("ROLLBACK;"); } RowCounter++; Revisions.Add(LedgerNumber + "_" + BudgetYear); } } } } return(RowCounter); }
/// <summary> /// Fix table a_budget_period /// </summary> /// <remarks> /// In Petra, one ABudgetPeriod could cover three years. These must be split up for OP with one record for each year. /// </remarks> public static int FixABudgetPeriod(StringCollection AColumnNames, ref string[] ANewRow, StreamWriter AWriter, StreamWriter AWriterTest) { // load the file a_budget.d.gz so that we can access the values for each budget TTable BudgetTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("a_budget"); TParseProgressCSV BudgetParser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "a_budget.d.gz", BudgetTableOld.grpTableField.Count); StringCollection BudgetColumnNames = GetColumnNames(BudgetTableOld); // load the file a_budget_period.d.gz TTable BudgetPeriodTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("a_budget_period"); TParseProgressCSV BudgetPeriodParser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "a_budget_period.d.gz", BudgetPeriodTableOld.grpTableField.Count); StringCollection BudgetPeriodColumnNames = GetColumnNames(BudgetPeriodTableOld); // read first row in ABudgetPeriod string[] OldBudgetPeriodRow = BudgetPeriodParser.ReadNextRow(); // [0] last year, [1] current year, [2] next year. Null if budget does not exist for a year. string[] BudgetYears = new string[3]; List <string[]> BudgetPeriodRows = new List <string[]>(); string LedgerNumber = string.Empty; int RowCounter = 0; int SequenceNumber = 0; SetValue(AColumnNames, ref ANewRow, "s_date_created_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_created_by_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_date_modified_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_modified_by_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_modification_id_t", "\\N"); while (true) { BudgetYears[0] = null; BudgetYears[2] = null; string[] OldRow = BudgetParser.ReadNextRow(); if (OldRow == null) { break; } if (LedgerNumber != GetValue(BudgetColumnNames, OldRow, "a_ledger_number_i")) { LedgerNumber = GetValue(BudgetColumnNames, OldRow, "a_ledger_number_i"); // gets the current financial year for the current ledger GetLedgerCurrentYear(LedgerNumber, out BudgetYears[1]); } GetBudgetPeriods(BudgetPeriodParser, BudgetPeriodColumnNames, ref OldBudgetPeriodRow, GetValue(BudgetColumnNames, OldRow, "a_budget_sequence_i"), out BudgetPeriodRows); GetBudgetYears(BudgetPeriodColumnNames, BudgetPeriodRows, ref BudgetYears); for (int i = 0; i < 3; i++) { if (BudgetYears[i] != null) { SequenceNumber++; foreach (string[] BudgetPeriodRow in BudgetPeriodRows) { RowCounter++; SetValue(AColumnNames, ref ANewRow, "a_budget_sequence_i", SequenceNumber.ToString()); SetValue(AColumnNames, ref ANewRow, "a_period_number_i", GetValue(BudgetPeriodColumnNames, BudgetPeriodRow, "a_period_number_i")); if (i == 0) { SetValue(AColumnNames, ref ANewRow, "a_budget_base_n", GetValue(BudgetPeriodColumnNames, BudgetPeriodRow, "a_budget_last_year_n")); } else if (i == 1) { SetValue(AColumnNames, ref ANewRow, "a_budget_base_n", GetValue(BudgetPeriodColumnNames, BudgetPeriodRow, "a_budget_this_year_n")); } else if (i == 2) { SetValue(AColumnNames, ref ANewRow, "a_budget_base_n", GetValue(BudgetPeriodColumnNames, BudgetPeriodRow, "a_budget_next_year_n")); } AWriter.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); if (AWriterTest != null) { AWriterTest.WriteLine("BEGIN; " + "COPY a_budget_period FROM stdin;"); AWriterTest.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); AWriterTest.WriteLine("\\."); AWriterTest.WriteLine("ROLLBACK;"); } } } } } return(RowCounter); }
private static void GetLedgerCurrentYear(string ALedgerNumber, out string ALedgerYearCurrentYear) { // load the file a_ledger.d.gz TTable LedgerTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("a_ledger"); TParseProgressCSV Parser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "a_ledger.d.gz", LedgerTableOld.grpTableField.Count); StringCollection LedgerColumnNames = GetColumnNames(LedgerTableOld); ALedgerYearCurrentYear = "0"; while (true) { string[] OldRow = Parser.ReadNextRow(); if (OldRow == null) { break; } if (GetValue(LedgerColumnNames, OldRow, "a_ledger_number_i") == ALedgerNumber) { ALedgerYearCurrentYear = GetValue(LedgerColumnNames, OldRow, "a_current_financial_year_i"); break; } } }
private int MoveTables(string ANewTableName, string dumpFile, StreamWriter MyWriter, StreamWriter MyWriterTest, TTable newTable) { int ProcessedRows = 0; if (ANewTableName == "a_batch") { TLogging.Log("a_this_year_old_batch"); TTable oldTable = storeOld.GetTable("a_this_year_old_batch"); TParseProgressCSV Parser = new TParseProgressCSV( dumpFile.Replace(ANewTableName, "a_this_year_old_batch") + ".d.gz", oldTable.grpTableField.Count); ProcessedRows += TFixData.MigrateData(Parser, MyWriter, MyWriterTest, oldTable, newTable); TLogging.Log("a_previous_year_batch"); oldTable = storeOld.GetTable("a_previous_year_batch"); Parser = new TParseProgressCSV( dumpFile.Replace(ANewTableName, "a_previous_year_batch") + ".d.gz", oldTable.grpTableField.Count); ProcessedRows += TFixData.MigrateData(Parser, MyWriter, MyWriterTest, oldTable, newTable); } else if (ANewTableName == "a_journal") { TLogging.Log("a_this_year_old_journal"); TTable oldTable = storeOld.GetTable("a_this_year_old_journal"); TParseProgressCSV Parser = new TParseProgressCSV( dumpFile.Replace(ANewTableName, "a_this_year_old_journal") + ".d.gz", oldTable.grpTableField.Count); ProcessedRows += TFixData.MigrateData(Parser, MyWriter, MyWriterTest, oldTable, newTable); TLogging.Log("a_previous_year_journal"); oldTable = storeOld.GetTable("a_previous_year_journal"); Parser = new TParseProgressCSV( dumpFile.Replace(ANewTableName, "a_previous_year_journal") + ".d.gz", oldTable.grpTableField.Count); ProcessedRows += TFixData.MigrateData(Parser, MyWriter, MyWriterTest, oldTable, newTable); } else if (ANewTableName == "a_transaction") { TLogging.Log("a_this_year_old_transaction"); TTable oldTable = storeOld.GetTable("a_this_year_old_transaction"); TParseProgressCSV Parser = new TParseProgressCSV( dumpFile.Replace(ANewTableName, "a_this_year_old_transaction") + ".d.gz", oldTable.grpTableField.Count); ProcessedRows += TFixData.MigrateData(Parser, MyWriter, MyWriterTest, oldTable, newTable); TLogging.Log("a_previous_year_transaction"); oldTable = storeOld.GetTable("a_previous_year_transaction"); Parser = new TParseProgressCSV( dumpFile.Replace(ANewTableName, "a_previous_year_transaction") + ".d.gz", oldTable.grpTableField.Count); ProcessedRows += TFixData.MigrateData(Parser, MyWriter, MyWriterTest, oldTable, newTable); } else if (ANewTableName == "a_trans_anal_attrib") { TLogging.Log("a_thisyearold_trans_anal_attrib"); TTable oldTable = storeOld.GetTable("a_thisyearold_trans_anal_attrib"); TParseProgressCSV Parser = new TParseProgressCSV( dumpFile.Replace(ANewTableName, "a_thisyearold_trans_anal_attrib") + ".d.gz", oldTable.grpTableField.Count); ProcessedRows += TFixData.MigrateData(Parser, MyWriter, MyWriterTest, oldTable, newTable); TLogging.Log("a_prev_year_trans_anal_attrib"); oldTable = storeOld.GetTable("a_prev_year_trans_anal_attrib"); Parser = new TParseProgressCSV( dumpFile.Replace(ANewTableName, "a_prev_year_trans_anal_attrib") + ".d.gz", oldTable.grpTableField.Count); ProcessedRows += TFixData.MigrateData(Parser, MyWriter, MyWriterTest, oldTable, newTable); } else if (ANewTableName == "a_corporate_exchange_rate") { TLogging.Log("a_prev_year_corp_ex_rate"); TTable oldTable = storeOld.GetTable("a_prev_year_corp_ex_rate"); TParseProgressCSV Parser = new TParseProgressCSV( dumpFile.Replace(ANewTableName, "a_prev_year_corp_ex_rate") + ".d.gz", oldTable.grpTableField.Count); ProcessedRows += TFixData.MigrateData(Parser, MyWriter, MyWriterTest, oldTable, newTable); } return ProcessedRows; }
/// <summary> /// Populate the empty table PPartnerGiftDestination using PmStaffData /// </summary> public static int PopulatePContactLog(StringCollection AColumnNames, ref string[] ANewRow, StreamWriter AWriter, StreamWriter AWriterTest) { // load the file p_partner_contact.d.gz TTable PartnerContact = TDumpProgressToPostgresql.GetStoreOld().GetTable("p_partner_contact"); TParseProgressCSV ParserPartnerContact = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "p_partner_contact.d.gz", PartnerContact.grpTableField.Count); TTable ContactLog = TDumpProgressToPostgresql.GetStoreNew().GetTable("p_contact_log"); StringCollection ContactLogColumnNames = GetColumnNames(ContactLog); string[] ContactLogRow = new string[ContactLogColumnNames.Count]; int RowCounter = 0; string[] OldRow = ParserPartnerContact.ReadNextRow(); while (OldRow != null) { //string ContactLogKey = TSequenceWriter.GetNextSequenceValue("seq_contact").ToString(); for (int i = 0; i < OldRow.Length; i++) { OldRow[i] = string.IsNullOrWhiteSpace(OldRow[i]) ? "\\N" : OldRow[i]; } // p_contact_log row SetValue(ContactLogColumnNames, ref ContactLogRow, "p_contact_log_id_i", OldRow[0]); //ContactLogKey); SetValue(ContactLogColumnNames, ref ContactLogRow, "s_contact_date_d", OldRow[2]); SetValue(ContactLogColumnNames, ref ContactLogRow, "s_contact_time_i", OldRow[3]); if ((OldRow[4].Length == 0) || (OldRow[4] == "\\N")) { SetValue(ContactLogColumnNames, ref ContactLogRow, "p_contact_code_c", "UNKNOWN"); } else { SetValue(ContactLogColumnNames, ref ContactLogRow, "p_contact_code_c", OldRow[4].ToUpper()); } SetValue(ContactLogColumnNames, ref ContactLogRow, "p_contactor_c", OldRow[5]); SetValue(ContactLogColumnNames, ref ContactLogRow, "p_contact_message_id_c", OldRow[6]); SetValue(ContactLogColumnNames, ref ContactLogRow, "p_contact_comment_c", OldRow[7]); SetValue(ContactLogColumnNames, ref ContactLogRow, "s_module_id_c", OldRow[8]); SetValue(ContactLogColumnNames, ref ContactLogRow, "s_user_id_c", OldRow[9].ToUpper()); SetValue(ContactLogColumnNames, ref ContactLogRow, "p_mailing_code_c", OldRow[10].ToUpper()); SetValue(ContactLogColumnNames, ref ContactLogRow, "p_restricted_l", OldRow[11]); SetValue(ContactLogColumnNames, ref ContactLogRow, "p_contact_location_c", OldRow[12]); SetValue(ContactLogColumnNames, ref ContactLogRow, "s_date_created_d", OldRow[13]); SetValue(ContactLogColumnNames, ref ContactLogRow, "s_created_by_c", OldRow[14].ToUpper()); SetValue(ContactLogColumnNames, ref ContactLogRow, "s_date_modified_d", OldRow[15]); SetValue(ContactLogColumnNames, ref ContactLogRow, "s_modified_by_c", OldRow[16].ToUpper()); SetValue(ContactLogColumnNames, ref ContactLogRow, "s_modification_id_t", OldRow[17]); // p_partner_contact row SetValue(AColumnNames, ref ANewRow, "p_partner_key_n", OldRow[1]); SetValue(AColumnNames, ref ANewRow, "p_contact_log_id_i", OldRow[0]); //ContactLogKey); // write test records if (AWriterTest != null) { AWriterTest.WriteLine("BEGIN; " + "COPY p_contact_log FROM stdin;"); AWriterTest.WriteLine(StringHelper.StrMerge(ContactLogRow, '\t').Replace("\\\\N", "\\N").ToString()); AWriterTest.WriteLine("\\."); AWriterTest.WriteLine("ROLLBACK;"); } if (AWriterTest != null) { AWriterTest.WriteLine("BEGIN; " + "COPY p_partner_contact FROM stdin;"); AWriterTest.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); AWriterTest.WriteLine("\\."); AWriterTest.WriteLine("ROLLBACK;"); } OldRow = ParserPartnerContact.ReadNextRow(); AWriter.WriteLine("COPY p_contact_log FROM stdin;"); AWriter.WriteLine(StringHelper.StrMerge(ContactLogRow, '\t').Replace("\\\\N", "\\N").ToString()); AWriter.WriteLine("\\."); AWriter.WriteLine("COPY p_partner_contact FROM stdin;"); AWriter.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); if (OldRow != null) { AWriter.WriteLine("\\."); } RowCounter += 2; // ContactLog record and PartnerContact record } return RowCounter; }
/// <summary> /// Populate the empty table PPartnerGiftDestination using PmStaffData /// </summary> public static int PopulatePPartnerGiftDestination(StringCollection AColumnNames, ref string[] ANewRow, StreamWriter AWriter, StreamWriter AWriterTest) { List <string[]>ActiveCommitments = new List <string[]>(); List <string[]>Persons = new List <string[]>(); int RowCounter = 0; // default for all new records SetValue(AColumnNames, ref ANewRow, "p_active_l", "\\N"); SetValue(AColumnNames, ref ANewRow, "p_default_gift_destination_l", "\\N"); SetValue(AColumnNames, ref ANewRow, "p_partner_class_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "p_commitment_site_key_n", "\\N"); SetValue(AColumnNames, ref ANewRow, "p_commitment_key_n", "\\N"); SetValue(AColumnNames, ref ANewRow, "p_comment_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_date_created_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_created_by_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_date_modified_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_modified_by_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_modification_id_t", "\\N"); // load the file pm_staff_data.d.gz TTable StaffDataTable = TDumpProgressToPostgresql.GetStoreOld().GetTable("pm_staff_data"); TParseProgressCSV StaffDataParser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "pm_staff_data.d.gz", StaffDataTable.grpTableField.Count); StringCollection StaffDataColumnNames = GetColumnNames(StaffDataTable); // find which records are currently active while (true) { string[] StaffDataRow = StaffDataParser.ReadNextRow(); if (StaffDataRow == null) { break; } string strStartOfCommitment = GetValue(StaffDataColumnNames, StaffDataRow, "pm_start_of_commitment_d"); string strEndOfCommitment = GetValue(StaffDataColumnNames, StaffDataRow, "pm_end_of_commitment_d"); try { // if commitment is currently active if ((DateTime.ParseExact(strStartOfCommitment, "dd/mm/yyyy", CultureInfo.InvariantCulture) <= DateTime.Today) && ((strEndOfCommitment == "\\N") || (DateTime.ParseExact(strEndOfCommitment, "dd/mm/yyyy", CultureInfo.InvariantCulture) >= DateTime.Today)) && (strStartOfCommitment != strEndOfCommitment)) { ActiveCommitments.Add(StaffDataRow); } } catch { TLogging.Log("WARNING: Invalid date in commitment: " + strStartOfCommitment + " or " + strEndOfCommitment); } } // load the file p_person.d.gz TTable PersonTable = TDumpProgressToPostgresql.GetStoreOld().GetTable("p_person"); TParseProgressCSV PersonParser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "p_person.d.gz", PersonTable.grpTableField.Count); StringCollection PersonColumnNames = GetColumnNames(PersonTable); SortedList <string, List <PersonKeyAndRow>>FamilyKeysWithPersons = new SortedList <string, List <PersonKeyAndRow>>(); // add all Persons to a list while (true) { string[] PersonRow = PersonParser.ReadNextRow(); if (PersonRow == null) { break; } string familyKey = GetValue(PersonColumnNames, PersonRow, "p_family_key_n"); if (!FamilyKeysWithPersons.ContainsKey(familyKey)) { FamilyKeysWithPersons.Add(familyKey, new List <PersonKeyAndRow>()); } FamilyKeysWithPersons[familyKey].Add( new PersonKeyAndRow( GetValue(PersonColumnNames, PersonRow, "p_partner_key_n"), PersonRow)); Persons.Add(PersonRow); } // load the file p_family.d.gz TTable FamilyTable = TDumpProgressToPostgresql.GetStoreOld().GetTable("p_family"); TParseProgressCSV FamilyParser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "p_family.d.gz", FamilyTable.grpTableField.Count); StringCollection FamilyColumnNames = GetColumnNames(FamilyTable); // read through each family while (true) { string[] FamilyRow = FamilyParser.ReadNextRow(); if (FamilyRow == null) { break; } string familykey = GetValue(FamilyColumnNames, FamilyRow, "p_partner_key_n"); // find Person partners belonging to the family bool CommitmentFound = false; int MinimumFamilyId = int.MaxValue; // if family contains Persons if (FamilyKeysWithPersons.ContainsKey(familykey)) { // read through each of the Family's Persons foreach (PersonKeyAndRow PersonRecord in FamilyKeysWithPersons[familykey]) { // find if the Person has a currently active commitment string[] Commitment = ActiveCommitments.Find(e => GetValue(StaffDataColumnNames, e, "p_partner_key_n") == PersonRecord.PersonKey); // if currently active commitment exists create a new Gift Destination record if (Commitment != null) { int CurrentFamilyId = Convert.ToInt32(GetValue(PersonColumnNames, PersonRecord.PersonRow, "p_old_omss_family_id_i")); if (CurrentFamilyId < MinimumFamilyId) { SetValue(AColumnNames, ref ANewRow, "p_key_i", RowCounter.ToString()); SetValue(AColumnNames, ref ANewRow, "p_partner_key_n", GetValue(FamilyColumnNames, FamilyRow, "p_partner_key_n")); SetValue(AColumnNames, ref ANewRow, "p_field_key_n", GetValue(StaffDataColumnNames, Commitment, "pm_target_field_n")); SetValue(AColumnNames, ref ANewRow, "p_comment_c", "\\N"); TTableField tf = new TTableField(); tf.strName = "pm_start_of_commitment_d"; tf.strType = "DATE"; SetValue(AColumnNames, ref ANewRow, "p_date_effective_d", TFixData.FixValue(GetValue(StaffDataColumnNames, Commitment, "pm_start_of_commitment_d"), tf)); tf.strName = "pm_end_of_commitment_d"; SetValue(AColumnNames, ref ANewRow, "p_date_expires_d", TFixData.FixValue(GetValue(StaffDataColumnNames, Commitment, "pm_end_of_commitment_d"), tf)); CommitmentFound = true; MinimumFamilyId = CurrentFamilyId; // there can only be one active gift destination per family break; } } } } // if no active commitment is found then search for a "p_om_field_key_n" and use that to create a gift destination if (!CommitmentFound) { string OMFieldKey = GetValue(FamilyColumnNames, FamilyRow, "p_om_field_key_n"); if ((OMFieldKey != "\\N") && (OMFieldKey != "0")) { SetValue(AColumnNames, ref ANewRow, "p_key_i", RowCounter.ToString()); SetValue(AColumnNames, ref ANewRow, "p_partner_key_n", GetValue(FamilyColumnNames, FamilyRow, "p_partner_key_n")); SetValue(AColumnNames, ref ANewRow, "p_field_key_n", GetValue(FamilyColumnNames, FamilyRow, "p_om_field_key_n")); DateTime LastYear = DateTime.Today.AddYears(-1); SetValue(AColumnNames, ref ANewRow, "p_date_effective_d", string.Format("{0}-{1}-{2}", LastYear.Year, LastYear.Month, LastYear.Day)); SetValue(AColumnNames, ref ANewRow, "p_date_expires_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "p_comment_c", Catalog.GetString("Copied from Petra's OM Field Key.")); CommitmentFound = true; } } // write gift destination to file if (CommitmentFound) { AWriter.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); if (AWriterTest != null) { AWriterTest.WriteLine("BEGIN; " + "COPY p_partner_gift_destination FROM stdin;"); AWriterTest.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); AWriterTest.WriteLine("\\."); AWriterTest.WriteLine("ROLLBACK;"); } RowCounter++; } } return RowCounter; }
private static string GetSupplierCurrencyFromPayment(Int32 ALedgerNumber, Int32 APaymentNumber) { if (CurrencyPerPayment == null) { SortedList <Int64, string>CurrencyPerDocument = new SortedList <Int64, string>(); TTable documentTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("a_ap_document"); TParseProgressCSV Parser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "a_ap_document.d.gz", documentTableOld.grpTableField.Count); StringCollection ColumnNames = GetColumnNames(documentTableOld); while (true) { string[] OldRow = Parser.ReadNextRow(); if (OldRow == null) { break; } Int64 LedgerAndAPNumber = Convert.ToInt64(GetValue(ColumnNames, OldRow, "a_ledger_number_i")) * 1000000 + Convert.ToInt64(GetValue(ColumnNames, OldRow, "a_ap_number_i")); CurrencyPerDocument.Add(LedgerAndAPNumber, GetSupplierCurrency(Convert.ToInt64(GetValue(ColumnNames, OldRow, "p_partner_key_n")))); } CurrencyPerPayment = new SortedList <long, string>(); TTable documentpaymentTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("a_ap_document_payment"); Parser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "a_ap_document_payment.d.gz", documentpaymentTableOld.grpTableField.Count); ColumnNames = GetColumnNames(documentpaymentTableOld); while (true) { string[] OldRow = Parser.ReadNextRow(); if (OldRow == null) { break; } Int64 LedgerAndPaymentNumber = Convert.ToInt64(GetValue(ColumnNames, OldRow, "a_ledger_number_i")) * 1000000 + Convert.ToInt64(GetValue(ColumnNames, OldRow, "a_payment_number_i")); if (!CurrencyPerPayment.ContainsKey(LedgerAndPaymentNumber)) { CurrencyPerPayment.Add(LedgerAndPaymentNumber, CurrencyPerDocument[ Convert.ToInt64(GetValue(ColumnNames, OldRow, "a_ledger_number_i")) * 1000000 + Convert.ToInt64(GetValue(ColumnNames, OldRow, "a_ap_number_i"))]); } } } return CurrencyPerPayment[ALedgerNumber * 1000000 + APaymentNumber]; }
private int MoveTables(string ANewTableName, string dumpFile, StreamWriter MyWriter, StreamWriter MyWriterTest, TTable newTable) { int ProcessedRows = 0; if (ANewTableName == "a_batch") { TLogging.Log("a_this_year_old_batch"); TTable oldTable = storeOld.GetTable("a_this_year_old_batch"); TParseProgressCSV Parser = new TParseProgressCSV( dumpFile.Replace(ANewTableName, "a_this_year_old_batch") + ".d.gz", oldTable.grpTableField.Count); ProcessedRows += TFixData.MigrateData(Parser, MyWriter, MyWriterTest, oldTable, newTable); TLogging.Log("a_previous_year_batch"); oldTable = storeOld.GetTable("a_previous_year_batch"); Parser = new TParseProgressCSV( dumpFile.Replace(ANewTableName, "a_previous_year_batch") + ".d.gz", oldTable.grpTableField.Count); ProcessedRows += TFixData.MigrateData(Parser, MyWriter, MyWriterTest, oldTable, newTable); } else if (ANewTableName == "a_journal") { TLogging.Log("a_this_year_old_journal"); TTable oldTable = storeOld.GetTable("a_this_year_old_journal"); TParseProgressCSV Parser = new TParseProgressCSV( dumpFile.Replace(ANewTableName, "a_this_year_old_journal") + ".d.gz", oldTable.grpTableField.Count); ProcessedRows += TFixData.MigrateData(Parser, MyWriter, MyWriterTest, oldTable, newTable); TLogging.Log("a_previous_year_journal"); oldTable = storeOld.GetTable("a_previous_year_journal"); Parser = new TParseProgressCSV( dumpFile.Replace(ANewTableName, "a_previous_year_journal") + ".d.gz", oldTable.grpTableField.Count); ProcessedRows += TFixData.MigrateData(Parser, MyWriter, MyWriterTest, oldTable, newTable); } else if (ANewTableName == "a_transaction") { TLogging.Log("a_this_year_old_transaction"); TTable oldTable = storeOld.GetTable("a_this_year_old_transaction"); TParseProgressCSV Parser = new TParseProgressCSV( dumpFile.Replace(ANewTableName, "a_this_year_old_transaction") + ".d.gz", oldTable.grpTableField.Count); ProcessedRows += TFixData.MigrateData(Parser, MyWriter, MyWriterTest, oldTable, newTable); TLogging.Log("a_previous_year_transaction"); oldTable = storeOld.GetTable("a_previous_year_transaction"); Parser = new TParseProgressCSV( dumpFile.Replace(ANewTableName, "a_previous_year_transaction") + ".d.gz", oldTable.grpTableField.Count); ProcessedRows += TFixData.MigrateData(Parser, MyWriter, MyWriterTest, oldTable, newTable); } else if (ANewTableName == "a_trans_anal_attrib") { TLogging.Log("a_thisyearold_trans_anal_attrib"); TTable oldTable = storeOld.GetTable("a_thisyearold_trans_anal_attrib"); TParseProgressCSV Parser = new TParseProgressCSV( dumpFile.Replace(ANewTableName, "a_thisyearold_trans_anal_attrib") + ".d.gz", oldTable.grpTableField.Count); ProcessedRows += TFixData.MigrateData(Parser, MyWriter, MyWriterTest, oldTable, newTable); TLogging.Log("a_prev_year_trans_anal_attrib"); oldTable = storeOld.GetTable("a_prev_year_trans_anal_attrib"); Parser = new TParseProgressCSV( dumpFile.Replace(ANewTableName, "a_prev_year_trans_anal_attrib") + ".d.gz", oldTable.grpTableField.Count); ProcessedRows += TFixData.MigrateData(Parser, MyWriter, MyWriterTest, oldTable, newTable); } else if (ANewTableName == "a_corporate_exchange_rate") { TLogging.Log("a_prev_year_corp_ex_rate"); TTable oldTable = storeOld.GetTable("a_prev_year_corp_ex_rate"); TParseProgressCSV Parser = new TParseProgressCSV( dumpFile.Replace(ANewTableName, "a_prev_year_corp_ex_rate") + ".d.gz", oldTable.grpTableField.Count); ProcessedRows += TFixData.MigrateData(Parser, MyWriter, MyWriterTest, oldTable, newTable); } return(ProcessedRows); }
/// <summary> /// fix data that would cause problems for PostgreSQL constraints /// </summary> /// <returns>false if the row should be dropped</returns> public static bool FixData(string ATableName, StringCollection AColumnNames, ref string[] ANewRow) { if (ATableName == "a_budget") { return false; } if (ATableName == "a_budget_period") { return false; } // Process p_partner records only for the purpose of storing the Partner Class of each Partner in an in-memory-structure if (ATableName == "p_partner") { TPartnerContactDetails.PartnerClassInformation.Add( Convert.ToInt64(GetValue(AColumnNames, ANewRow, "p_partner_key_n")), GetValue(AColumnNames, ANewRow, "p_partner_class_c")); } // update pub.a_account_property set a_property_value_c = 'true' where a_property_code_c = 'Bank Account'; if (ATableName == "a_account_property") { if (GetValue(AColumnNames, ANewRow, "a_property_code_c") == "Bank Account") { SetValue(AColumnNames, ref ANewRow, "a_property_value_c", "true"); } } if (ATableName == "a_ap_document") { return TFinanceAccountsPayableUpgrader.FixAPDocument(AColumnNames, ref ANewRow); } if (ATableName == "a_ap_payment") { return TFinanceAccountsPayableUpgrader.FixAPPayment(AColumnNames, ref ANewRow); } if (ATableName == "a_ap_anal_attrib") { return TFinanceAccountsPayableUpgrader.FixAPAnalAttrib(AColumnNames, ref ANewRow); } if (ATableName == "a_ap_document_detail") { return TFinanceAccountsPayableUpgrader.FixAPDocumentDetail(AColumnNames, ref ANewRow); } if (ATableName == "a_ap_document_payment") { return TFinanceAccountsPayableUpgrader.FixAPDocumentPayment(AColumnNames, ref ANewRow); } if (ATableName == "s_login") { SetValue(AColumnNames, ref ANewRow, "s_login_process_id_r", TSequenceWriter.GetNextSequenceValue("seq_login_process_id").ToString()); string LoginTime = GetValue(AColumnNames, ANewRow, "s_login_time_i"); while (FPreviousLoginTime == LoginTime) { int intLoginTime = Convert.ToInt32(LoginTime); LoginTime = (intLoginTime + 1).ToString(); SetValue(AColumnNames, ref ANewRow, "s_login_time_i", LoginTime); } FPreviousLoginTime = LoginTime; } if (ATableName == "a_journal") { // date of entry must not be NULL if (GetValue(AColumnNames, ANewRow, "a_date_of_entry_d") == "\\N") { SetValue(AColumnNames, ref ANewRow, "a_date_of_entry_d", GetValue(AColumnNames, ANewRow, "a_date_effective_d")); } } // a_email_destination.a_conditional_value_c is sometimes null, but it is part of the primary key if (ATableName == "a_email_destination") { string ConditionalValue = GetValue(AColumnNames, ANewRow, "a_conditional_value_c"); if ((ConditionalValue == "\\N") || (ConditionalValue.Length == 0)) { SetValue(AColumnNames, ref ANewRow, "a_conditional_value_c", "NOT SET"); } } // s_user_group contains some SQL_* users, which are not part of the s_user table if (ATableName == "s_user_group") { if (GetValue(AColumnNames, ANewRow, "s_user_id_c").StartsWith("SQL_")) { // do not write this line return false; } } // there is a space in front of the code, which causes a duplicate primary key if (ATableName == "p_type") { if (GetValue(AColumnNames, ANewRow, "p_type_code_c") == " STAFF") { return false; } } // there is a space in front of the code, which causes a duplicate primary key if (ATableName == "p_reason_subscription_given") { if (GetValue(AColumnNames, ANewRow, "p_code_c") == " FREE") { return false; } } // fix foreign key, remove space if (ATableName == "p_subscription") { string value = GetValue(AColumnNames, ANewRow, "p_reason_subs_given_code_c"); if (value == " FREE") { SetValue(AColumnNames, ref ANewRow, "p_reason_subs_given_code_c", "FREE"); } else if (value.Length == 0) { // p_reason_subs_given_code_c must not be NULL SetValue(AColumnNames, ref ANewRow, "p_reason_subs_given_code_c", "FREE"); } } // pm_person_language, language code cannot be null, should be 99. // Old language levels need mapped to new levels. if (ATableName == "pm_person_language") { string val = GetValue(AColumnNames, ANewRow, "p_language_code_c"); if ((val.Length == 0) || (val == "\\N")) { SetValue(AColumnNames, ref ANewRow, "p_language_code_c", "99"); } int val2 = Convert.ToInt32(GetValue(AColumnNames, ANewRow, "pt_language_level_i")); if (val2 <= 3) { SetValue(AColumnNames, ref ANewRow, "pt_language_level_i", "1"); } else if ((val2 >= 4) && (val2 <= 7)) { SetValue(AColumnNames, ref ANewRow, "pt_language_level_i", "2"); } else if (val2 >= 8) { SetValue(AColumnNames, ref ANewRow, "pt_language_level_i", "3"); } } // um_unit_language, language code cannot be null, should be 99. // Old language levels need mapped to new levels. if (ATableName == "um_unit_language") { string val = GetValue(AColumnNames, ANewRow, "p_language_code_c"); if ((val.Length == 0) || (val == "\\N")) { SetValue(AColumnNames, ref ANewRow, "p_language_code_c", "99"); } int val2 = Convert.ToInt32(GetValue(AColumnNames, ANewRow, "pt_language_level_i")); if (val2 <= 3) { SetValue(AColumnNames, ref ANewRow, "pt_language_level_i", "1"); } else if ((val2 >= 4) && (val2 <= 7)) { SetValue(AColumnNames, ref ANewRow, "pt_language_level_i", "2"); } else if (val2 >= 8) { SetValue(AColumnNames, ref ANewRow, "pt_language_level_i", "3"); } } // Old language levels need mapped to new levels. if (ATableName == "um_job_language") { int val = Convert.ToInt32(GetValue(AColumnNames, ANewRow, "pt_language_level_i")); if (val <= 3) { SetValue(AColumnNames, ref ANewRow, "pt_language_level_i", "1"); } else if ((val >= 4) && (val <= 7)) { SetValue(AColumnNames, ref ANewRow, "pt_language_level_i", "2"); } else if (val >= 8) { SetValue(AColumnNames, ref ANewRow, "pt_language_level_i", "3"); } } // p_partner_contact, method of contact cannot be null, should be UNKNOWN if (ATableName == "p_partner_contact") { return false; } if (ATableName == "a_batch") { return TFinanceGeneralLedgerUpgrader.FixABatch(AColumnNames, ref ANewRow); } if (ATableName == "a_budget_type") { return TFinanceBudgetUpgrader.FixABudgetType(AColumnNames, ref ANewRow); } if (ATableName == "a_account") { return TFinanceBudgetUpgrader.FixABudgetType(AColumnNames, ref ANewRow); } if (ATableName == "a_motivation_detail") { return TFinanceGeneralLedgerUpgrader.FixAMotivationDetail(AColumnNames, ref ANewRow); } // wrong gift batch status, need to have case sensitive status if (ATableName == "a_gift_batch") { string val = GetValue(AColumnNames, ANewRow, "a_batch_status_c"); if (val == "posted") { SetValue(AColumnNames, ref ANewRow, "a_batch_status_c", "Posted"); } } // bank code has too many characters, remove spaces if (ATableName == "p_bank") { string val = GetValue(AColumnNames, ANewRow, "p_branch_code_c"); if (val.Length > 20) { SetValue(AColumnNames, ref ANewRow, "p_branch_code_c", val.Replace(" ", "")); } } // if target field is null or 0, use the home office partner key if (ATableName == "pm_staff_data") { string ReceivingField = GetValue(AColumnNames, ANewRow, "pm_receiving_field_n"); string HomeOffice = GetValue(AColumnNames, ANewRow, "pm_home_office_n"); if ((HomeOffice == "0") || (HomeOffice.Length == 0) || (HomeOffice == "\\N")) { HomeOffice = GetValue(AColumnNames, ANewRow, "pm_office_recruited_by_n"); SetValue(AColumnNames, ref ANewRow, "pm_home_office_n", HomeOffice); } if ((ReceivingField == "0") || (ReceivingField.Length == 0) || (ReceivingField == "\\N")) { SetValue(AColumnNames, ref ANewRow, "pm_receiving_field_n", HomeOffice); } } // pm_st_basic_outreach_id_c cannot be null if (ATableName == "pm_short_term_application") { string val = GetValue(AColumnNames, ANewRow, "pm_st_basic_outreach_id_c"); if ((val == "0") || (val.Length == 0) || (val == "\\N")) { SetValue(AColumnNames, ref ANewRow, "pm_st_basic_outreach_id_c", GetValue(AColumnNames, ANewRow, "pm_registration_office_n") + "-" + GetValue(AColumnNames, ANewRow, "pm_application_key_i")); } val = GetValue(AColumnNames, ANewRow, "pm_st_field_charged_n"); if ((val == "0") || (val.Length == 0) || (val == "\\N")) { SetValue(AColumnNames, ref ANewRow, "pm_st_field_charged_n", GetValue(AColumnNames, ANewRow, "pm_registration_office_n")); } } if (ATableName == "um_job_qualification") { if (GetValue(AColumnNames, ANewRow, "pt_qualification_area_name_c") == "") { SetValue(AColumnNames, ref ANewRow, "pt_qualification_area_name_c", "OTHER"); } } if (ATableName == "p_person") { string val = GetValue(AColumnNames, ANewRow, "p_family_id_i"); if ((val == "") || (val.Length == 0) || (val == "\\N")) { // p_family_id_i is now NOT NULL, but for merged partners, it is reset in Petra 2.x SetValue(AColumnNames, ref ANewRow, "p_family_id_i", "-1"); } } // pm_personal_data: move values from the p_person table for believer info if (ATableName == "pm_personal_data") { if (PPersonBelieverInfo == null) { PPersonBelieverInfo = new SortedList <string, string[]>(); // load the file p_person.d.gz so that we can access the values for each person TTable personTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("p_person"); TParseProgressCSV Parser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "p_person.d.gz", personTableOld.grpTableField.Count); StringCollection PersonColumnNames = GetColumnNames(personTableOld); string personKey = string.Empty; string believerSinceYear = string.Empty; string believerSinceComment = string.Empty; while (true) { string[] OldRow = Parser.ReadNextRow(); if (OldRow == null) { break; } personKey = GetValue(PersonColumnNames, OldRow, "p_partner_key_n"); believerSinceComment = GetValue(PersonColumnNames, OldRow, "p_believer_since_comment_c"); believerSinceYear = GetValue(PersonColumnNames, OldRow, "p_believer_since_year_i"); PPersonBelieverInfo.Add(personKey, new string[] { believerSinceComment, believerSinceYear }); } } string partnerkey = GetValue(AColumnNames, ANewRow, "p_partner_key_n"); string[] believerInfo = PPersonBelieverInfo[partnerkey]; SetValue(AColumnNames, ref ANewRow, "p_believer_since_comment_c", believerInfo[0]); SetValue(AColumnNames, ref ANewRow, "p_believer_since_year_i", believerInfo[1]); } // new sequence for pc_room_alloc if (ATableName == "pc_room_alloc") { SetValue(AColumnNames, ref ANewRow, "pc_key_i", TSequenceWriter.GetNextSequenceValue("seq_room_alloc").ToString()); } if (ATableName == "a_form_element") { if (GetValue(AColumnNames, ANewRow, "a_form_sequence_i") == "0") { // check if we have multiple rows with same formcode and formname, with sequence 0 // if patch 2.2.7 has been run, we should already have valid sequences string FormElementID = GetValue(AColumnNames, ANewRow, "a_form_code_c") + "::" + GetValue(AColumnNames, ANewRow, "a_form_name_c"); if (FormElementsWithSequence0.Contains(FormElementID)) { SetValue(AColumnNames, ref ANewRow, "a_form_sequence_i", FormSequence.ToString()); FormSequence++; } else { FormElementsWithSequence0.Add(FormElementID); } } } if (ATableName == "p_postcode_region") { string CurrentRegion = ANewRow[0]; foreach (string OldRegion in PostcodeRegionsList) { if (CurrentRegion == OldRegion) { return false; } } PostcodeRegionsList.Add(CurrentRegion); } // p_partner_status, 'DIED', 'INACTIVE' and 'MERGED' partners are not active if (ATableName == "p_partner_status") { string val = GetValue(AColumnNames, ANewRow, "p_status_code_c"); if ((val == "DIED") || (val == "INACTIVE") || (val == "MERGED")) { SetValue(AColumnNames, ref ANewRow, "p_partner_is_active_l", "0"); } } // Process p_partner_location records and migrate certain values of p_partner_location records to 'Contact Detail' records if (ATableName == "p_partner_location") { Int64 PartnerKey; DataTable PartnerLocationsDT; DataRow NewPartnerLocationDR; string DateEffectiveStr; string DateGoodUntilStr; object DateEffectiveColValue; object DateGoodUntilColValue; object SendMailColValue; string TelephoneNumber; string FaxNumber; string PhoneExtension; string FaxExtension; PartnerKey = Convert.ToInt64(GetValue(AColumnNames, ANewRow, "p_partner_key_n")); // If we haven't yet recorded the certain values of p_partner_location records for this Partner then create a new // data structure for this Partner that can hold those. That data structure will be processed and used for the // creation of records for the new 'Contact Details' scheme (held in the p_partner_attribute DB Table). if (!TPartnerContactDetails.PartnerLocationRecords.TryGetValue(PartnerKey, out PartnerLocationsDT)) { PartnerLocationsDT = TPartnerContactDetails.BestAddressHelper.GetNewPPartnerLocationTableInstance(); TPartnerContactDetails.PartnerLocationRecords.Add(PartnerKey, PartnerLocationsDT); } DateEffectiveStr = GetValue(AColumnNames, ANewRow, "p_date_effective_d"); DateEffectiveColValue = DateEffectiveStr == "\\N" ? System.DBNull.Value : (object)DateTime.ParseExact(DateEffectiveStr, "yyyy-dd-mm", CultureInfo.InvariantCulture); DateGoodUntilStr = GetValue(AColumnNames, ANewRow, "p_date_good_until_d"); DateGoodUntilColValue = DateGoodUntilStr == "\\N" ? System.DBNull.Value : (object)DateTime.ParseExact(DateGoodUntilStr, "yyyy-dd-mm", CultureInfo.InvariantCulture); SendMailColValue = GetValue(AColumnNames, ANewRow, "p_send_mail_l") == "1" ? (object)true : (object)false; // This is the value of the 'Mailing Address' CheckBox // Phone Extension: Ignore if value in the dumped data is either null or 0 PhoneExtension = GetValue(AColumnNames, ANewRow, "p_extension_i"); if ((PhoneExtension == "\\N") || (PhoneExtension == "0")) { PhoneExtension = String.Empty; } // Fax Extension: Ignore if value in the dumped data is either null or 0 FaxExtension = GetValue(AColumnNames, ANewRow, "p_fax_extension_i"); if ((FaxExtension == "\\N") || (FaxExtension == "0")) { FaxExtension = String.Empty; } TelephoneNumber = GetValue(AColumnNames, ANewRow, "p_telephone_number_c"); if (TelephoneNumber != "\\N") { // Concatenate Phone Number and Phone Extension ONLY if both of them aren't null and Phone Extension isn't 0 either. TelephoneNumber += PhoneExtension; } FaxNumber = GetValue(AColumnNames, ANewRow, "p_fax_number_c"); if (FaxNumber != "\\N") { // Concatenate Fax Number and Fax Extension ONLY if both of them aren't null and Fax Extension isn't 0 either. FaxNumber += FaxExtension; } // Create representation of key data of the p_partner_location row and add it to the TPartnerContactDetails.PartnerLocationRecords Data Structure NewPartnerLocationDR = PartnerLocationsDT.NewRow(); NewPartnerLocationDR["p_site_key_n"] = Convert.ToInt64(GetValue(AColumnNames, ANewRow, "p_site_key_n")); NewPartnerLocationDR["p_location_key_i"] = Convert.ToInt32(GetValue(AColumnNames, ANewRow, "p_location_key_i")); NewPartnerLocationDR["p_date_effective_d"] = DateEffectiveColValue; NewPartnerLocationDR["p_date_good_until_d"] = DateGoodUntilColValue; NewPartnerLocationDR["p_location_type_c"] = GetValue(AColumnNames, ANewRow, "p_location_type_c"); NewPartnerLocationDR["p_send_mail_l"] = SendMailColValue; NewPartnerLocationDR["p_telephone_number_c"] = TelephoneNumber; NewPartnerLocationDR["p_fax_number_c"] = FaxNumber; NewPartnerLocationDR["p_mobile_number_c"] = GetValue(AColumnNames, ANewRow, "p_mobile_number_c"); NewPartnerLocationDR["p_alternate_telephone_c"] = GetValue(AColumnNames, ANewRow, "p_alternate_telephone_c"); NewPartnerLocationDR["p_email_address_c"] = GetValue(AColumnNames, ANewRow, "p_email_address_c"); NewPartnerLocationDR["p_url_c"] = GetValue(AColumnNames, ANewRow, "p_url_c"); PartnerLocationsDT.Rows.Add(NewPartnerLocationDR); if (TAppSettingsManager.GetValue("wipe_partnerlocation_cont_det", "true") != "false") { // Set all Data Columns that we have just added to PartnerLocationsDT as a 'Contact Detail' to 'null' in // p_partner_location to 'wipe out' that data - as it will only be held in p_partner_attribute from now on! SetValue(AColumnNames, ref ANewRow, "p_telephone_number_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "p_extension_i", "\\N"); SetValue(AColumnNames, ref ANewRow, "p_fax_number_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "p_fax_extension_i", "\\N"); SetValue(AColumnNames, ref ANewRow, "p_mobile_number_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "p_alternate_telephone_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "p_email_address_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "p_url_c", "\\N"); } } // renaming "Gift Receipting" to "Gift Processing" (Mantis 1930) if (ATableName == "a_sub_system") { string val = GetValue(AColumnNames, ANewRow, "a_sub_system_name_c"); if (val == "Gift Receipting") { SetValue(AColumnNames, ref ANewRow, "a_sub_system_name_c", "Gift Processing"); } } // renaming "Gift Receipting" to "Gift Processing" (Mantis 1930) if (ATableName == "a_transaction_type") { string val = GetValue(AColumnNames, ANewRow, "a_transaction_type_description_c"); if (val == "Gift Receipting") { SetValue(AColumnNames, ref ANewRow, "a_transaction_type_description_c", "Gift Processing"); } } // A new password and password salt needs to be generated for every user. // Passwords are writed to a file in the fulldump folder. if (ATableName == "s_user") { string Password; string Salt; string PasswordHash; PasswordHelper.GetNewPasswordSaltAndHash(out Password, out Salt, out PasswordHash); SetValue(AColumnNames, ref ANewRow, "s_password_salt_c", Salt); SetValue(AColumnNames, ref ANewRow, "s_password_hash_c", PasswordHash); // the user will have to change their password the first time they log in SetValue(AColumnNames, ref ANewRow, "s_password_needs_change_l", "true"); // write userIDs and new passwords to a file string UserPasswordsDir = TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "_credentials.txt"; StreamWriter MyWriter; if (File.Exists(UserPasswordsDir)) { MyWriter = File.AppendText(UserPasswordsDir); } else { FileStream outStreamCount = File.Create(UserPasswordsDir); MyWriter = new StreamWriter(outStreamCount); } string UserID = GetValue(AColumnNames, ANewRow, "s_user_id_c"); MyWriter.WriteLine(UserID + "\t" + Password); MyWriter.Close(); } if (ATableName == "s_user_defaults") { string val = GetValue(AColumnNames, ANewRow, "s_default_code_c"); // replace old user default code that contains Petra screen name if (val == "GR1120-NEWDONOR") { SetValue(AColumnNames, ref ANewRow, "s_default_code_c", "NewDonorWarning"); } } if ((ATableName == "pt_congress_code") || (ATableName == "pt_arrival_point") || (ATableName == "pt_outreach_preference_level") || (ATableName == "pt_leadership_rating")) { string val = GetValue(AColumnNames, ANewRow, "pt_code_c"); // ignore record if code is blank if (val == "") { return false; } } if (ATableName == "pt_contact") { string val = GetValue(AColumnNames, ANewRow, "pt_contact_name_c"); // ignore record if this field is blank if (val == "") { return false; } } return true; }
/// <summary> /// process the data from the Progress dump file, so that Postgresql can read the result /// </summary> public void ProcessAndWritePostgresqlFile(string dumpFile, TTable newTable) { string oldTableName = DataDefinitionDiff.GetOldTableName(newTable.strName); TTable oldTable = storeOld.GetTable(oldTableName); // if this is a new table in OpenPetra, do not dump anything. the table will be empty in OpenPetra // (except p_postcode_region_range, a_budget_revision and p_partner_gift_destination which are populated here) if ((oldTable == null) && (newTable.strName != "p_postcode_region_range") && (newTable.strName != "a_budget_revision") && (newTable.strName != "p_partner_gift_destination") && (newTable.strName != "p_partner_attribute_category") && (newTable.strName != "p_partner_attribute_type")) { return; } string NewFileName = TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + newTable.strName + ".sql.gz"; // if (File.Exists(NewFileName)) // { // // for debugging: ignore files that have been written already // return; // } StreamWriter MyWriterCount = null; StreamWriter MyWriterTest = null; FileStream outStreamTest; Stream gzoStreamTest; TParseProgressCSV Parser = null; try { if (oldTable != null) { Parser = new TParseProgressCSV( dumpFile + ".d.gz", oldTable.grpTableField.Count); } FileStream outStream = File.Create(NewFileName); Stream gzoStream = new GZipOutputStream(outStream); StreamWriter MyWriter = new StreamWriter(gzoStream, Encoding.UTF8); if (TAppSettingsManager.GetValue("create_test_files", "true", false) == "true") { outStreamTest = File.Create( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + newTable.strName + "_test.sql.gz"); gzoStreamTest = new GZipOutputStream(outStreamTest); MyWriterTest = new StreamWriter(gzoStreamTest, Encoding.UTF8); } string rowCountDir = TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "_row_count.txt"; if (File.Exists(rowCountDir)) { MyWriterCount = File.AppendText(rowCountDir); } else { FileStream outStreamCount = File.Create(rowCountDir); MyWriterCount = new StreamWriter(outStreamCount); } // The p_partner_contact table needs to write p_contact_log records first, so handle the COPY manually if (newTable.strName != "p_partner_contact") { MyWriter.WriteLine("COPY " + newTable.strName + " FROM stdin;"); } int ProcessedRows = TFixData.MigrateData(Parser, MyWriter, MyWriterTest, oldTable, newTable); ProcessedRows += MoveTables(newTable.strName, dumpFile, MyWriter, MyWriterTest, newTable); MyWriter.WriteLine("\\."); MyWriter.WriteLine(); if (MyWriterTest != null) { MyWriterTest.WriteLine(); } MyWriterCount.WriteLine(newTable.strName); MyWriterCount.WriteLine(ProcessedRows); MyWriter.Close(); TLogging.Log(" after processing file, rows: " + ProcessedRows.ToString()); } catch (Exception e) { TLogging.Log("Memory usage: " + (GC.GetTotalMemory(false) / 1024 / 1024).ToString() + " MB"); TLogging.Log("WARNING Problems processing file " + dumpFile + ": " + e.ToString()); if (File.Exists(dumpFile + ".sql.gz")) { File.Delete(dumpFile + ".sql.gz"); } } finally { if (MyWriterCount != null) { MyWriterCount.Close(); } if (MyWriterTest != null) { MyWriterTest.Close(); } } }
/// <summary> /// Populate the empty table PPartnerGiftDestination using PmStaffData /// </summary> public static int PopulatePContactLog(StringCollection AColumnNames, ref string[] ANewRow, StreamWriter AWriter, StreamWriter AWriterTest) { // load the file p_partner_contact.d.gz TTable PartnerContact = TDumpProgressToPostgresql.GetStoreOld().GetTable("p_partner_contact"); TParseProgressCSV ParserPartnerContact = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "p_partner_contact.d.gz", PartnerContact.grpTableField.Count); TTable ContactLog = TDumpProgressToPostgresql.GetStoreNew().GetTable("p_contact_log"); StringCollection ContactLogColumnNames = GetColumnNames(ContactLog); string[] ContactLogRow = new string[ContactLogColumnNames.Count]; int RowCounter = 0; string[] OldRow = ParserPartnerContact.ReadNextRow(); while (OldRow != null) { //string ContactLogKey = TSequenceWriter.GetNextSequenceValue("seq_contact").ToString(); for (int i = 0; i < OldRow.Length; i++) { OldRow[i] = string.IsNullOrWhiteSpace(OldRow[i]) ? "\\N" : OldRow[i]; } // p_contact_log row SetValue(ContactLogColumnNames, ref ContactLogRow, "p_contact_log_id_i", OldRow[0]); //ContactLogKey); SetValue(ContactLogColumnNames, ref ContactLogRow, "s_contact_date_d", OldRow[2]); SetValue(ContactLogColumnNames, ref ContactLogRow, "s_contact_time_i", OldRow[3]); if ((OldRow[4].Length == 0) || (OldRow[4] == "\\N")) { SetValue(ContactLogColumnNames, ref ContactLogRow, "p_contact_code_c", "UNKNOWN"); } else { SetValue(ContactLogColumnNames, ref ContactLogRow, "p_contact_code_c", OldRow[4].ToUpper()); } SetValue(ContactLogColumnNames, ref ContactLogRow, "p_contactor_c", OldRow[5]); SetValue(ContactLogColumnNames, ref ContactLogRow, "p_contact_message_id_c", OldRow[6]); SetValue(ContactLogColumnNames, ref ContactLogRow, "p_contact_comment_c", OldRow[7]); SetValue(ContactLogColumnNames, ref ContactLogRow, "s_module_id_c", OldRow[8]); SetValue(ContactLogColumnNames, ref ContactLogRow, "s_user_id_c", OldRow[9].ToUpper()); SetValue(ContactLogColumnNames, ref ContactLogRow, "p_mailing_code_c", OldRow[10].ToUpper()); SetValue(ContactLogColumnNames, ref ContactLogRow, "p_restricted_l", OldRow[11]); SetValue(ContactLogColumnNames, ref ContactLogRow, "p_contact_location_c", OldRow[12]); SetValue(ContactLogColumnNames, ref ContactLogRow, "s_date_created_d", OldRow[13]); SetValue(ContactLogColumnNames, ref ContactLogRow, "s_created_by_c", OldRow[14].ToUpper()); SetValue(ContactLogColumnNames, ref ContactLogRow, "s_date_modified_d", OldRow[15]); SetValue(ContactLogColumnNames, ref ContactLogRow, "s_modified_by_c", OldRow[16].ToUpper()); SetValue(ContactLogColumnNames, ref ContactLogRow, "s_modification_id_t", OldRow[17]); // p_partner_contact row SetValue(AColumnNames, ref ANewRow, "p_partner_key_n", OldRow[1]); SetValue(AColumnNames, ref ANewRow, "p_contact_log_id_i", OldRow[0]); //ContactLogKey); // write test records if (AWriterTest != null) { AWriterTest.WriteLine("BEGIN; " + "COPY p_contact_log FROM stdin;"); AWriterTest.WriteLine(StringHelper.StrMerge(ContactLogRow, '\t').Replace("\\\\N", "\\N").ToString()); AWriterTest.WriteLine("\\."); AWriterTest.WriteLine("ROLLBACK;"); } if (AWriterTest != null) { AWriterTest.WriteLine("BEGIN; " + "COPY p_partner_contact FROM stdin;"); AWriterTest.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); AWriterTest.WriteLine("\\."); AWriterTest.WriteLine("ROLLBACK;"); } OldRow = ParserPartnerContact.ReadNextRow(); AWriter.WriteLine("COPY p_contact_log FROM stdin;"); AWriter.WriteLine(StringHelper.StrMerge(ContactLogRow, '\t').Replace("\\\\N", "\\N").ToString()); AWriter.WriteLine("\\."); AWriter.WriteLine("COPY p_partner_contact FROM stdin;"); AWriter.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); if (OldRow != null) { AWriter.WriteLine("\\."); } RowCounter += 2; // ContactLog record and PartnerContact record } return(RowCounter); }
/// <summary> /// Populate the empty table ABudgetRevision using tables ABudget and ABudgetPeriod /// </summary> public static int PopulateABudgetRevision(StringCollection AColumnNames, ref string[] ANewRow, StreamWriter AWriter, StreamWriter AWriterTest) { // in Petra 2.x, there never has been a record in this table. // so if there is a budget, we need to create a revision 0 for each year // load the file a_budget.d.gz so that we can access the values for each budget TTable BudgetTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("a_budget"); TParseProgressCSV BudgetParser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "a_budget.d.gz", BudgetTableOld.grpTableField.Count); StringCollection BudgetColumnNames = GetColumnNames(BudgetTableOld); // load the file a_budget_period.d.gz TTable BudgetPeriodTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("a_budget_period"); TParseProgressCSV BudgetPeriodParser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "a_budget_period.d.gz", BudgetPeriodTableOld.grpTableField.Count); StringCollection BudgetPeriodColumnNames = GetColumnNames(BudgetPeriodTableOld); // read first row in ABudgetPeriod string[] OldBudgetPeriodRow = BudgetPeriodParser.ReadNextRow(); // [0] last year, [1] current year, [2] next year. Null if budget does not exist for a year. string[] BudgetYears = new string[3]; List <string[]>BudgetPeriodRows = new List <string[]>(); List <string>Revisions = new List <string>(); string LedgerNumber = string.Empty; int RowCounter = 0; SetValue(AColumnNames, ref ANewRow, "a_revision_i", "0"); SetValue(AColumnNames, ref ANewRow, "a_description_c", "default"); SetValue(AColumnNames, ref ANewRow, "s_date_created_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_created_by_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_date_modified_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_modified_by_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_modification_id_t", "\\N"); // one a_budget_period record for each year of each ledger while (true) { BudgetYears[0] = null; BudgetYears[2] = null; string[] OldRow = BudgetParser.ReadNextRow(); if (OldRow == null) { break; } if (LedgerNumber != GetValue(BudgetColumnNames, OldRow, "a_ledger_number_i")) { LedgerNumber = GetValue(BudgetColumnNames, OldRow, "a_ledger_number_i"); // gets the current financial year for the current ledger GetLedgerCurrentYear(LedgerNumber, out BudgetYears[1]); } // Only three possible years for each ledger. Hence only three a_budget_revision records per ledger. // Move on to next ledger if data for all three years is found. if (!Revisions.Contains(LedgerNumber + "_" + (Convert.ToInt32(BudgetYears[1]) - 1).ToString()) && !Revisions.Contains(LedgerNumber + "_" + (Convert.ToInt32(BudgetYears[1])).ToString()) && !Revisions.Contains(LedgerNumber + "_" + (Convert.ToInt32(BudgetYears[1]) + 1).ToString())) { GetBudgetPeriods(BudgetPeriodParser, BudgetPeriodColumnNames, ref OldBudgetPeriodRow, GetValue(BudgetColumnNames, OldRow, "a_budget_sequence_i"), out BudgetPeriodRows); GetBudgetYears(BudgetPeriodColumnNames, BudgetPeriodRows, ref BudgetYears); foreach (string BudgetYear in BudgetYears) { if ((BudgetYear != null) && !Revisions.Contains(LedgerNumber + "_" + BudgetYear)) { SetValue(AColumnNames, ref ANewRow, "a_ledger_number_i", LedgerNumber); SetValue(AColumnNames, ref ANewRow, "a_year_i", BudgetYear); AWriter.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); if (AWriterTest != null) { AWriterTest.WriteLine("BEGIN; " + "COPY a_budget_revision FROM stdin;"); AWriterTest.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); AWriterTest.WriteLine("\\."); AWriterTest.WriteLine("ROLLBACK;"); } RowCounter++; Revisions.Add(LedgerNumber + "_" + BudgetYear); } } } } return RowCounter; }
/// <summary> /// Fix table a_budget_period /// </summary> /// <remarks> /// In Petra, one ABudgetPeriod could cover three years. These must be split up for OP with one record for each year. /// </remarks> public static int FixABudgetPeriod(StringCollection AColumnNames, ref string[] ANewRow, StreamWriter AWriter, StreamWriter AWriterTest) { // load the file a_budget.d.gz so that we can access the values for each budget TTable BudgetTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("a_budget"); TParseProgressCSV BudgetParser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "a_budget.d.gz", BudgetTableOld.grpTableField.Count); StringCollection BudgetColumnNames = GetColumnNames(BudgetTableOld); // load the file a_budget_period.d.gz TTable BudgetPeriodTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("a_budget_period"); TParseProgressCSV BudgetPeriodParser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "a_budget_period.d.gz", BudgetPeriodTableOld.grpTableField.Count); StringCollection BudgetPeriodColumnNames = GetColumnNames(BudgetPeriodTableOld); // read first row in ABudgetPeriod string[] OldBudgetPeriodRow = BudgetPeriodParser.ReadNextRow(); // [0] last year, [1] current year, [2] next year. Null if budget does not exist for a year. string[] BudgetYears = new string[3]; List <string[]>BudgetPeriodRows = new List <string[]>(); string LedgerNumber = string.Empty; int RowCounter = 0; int SequenceNumber = 0; SetValue(AColumnNames, ref ANewRow, "s_date_created_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_created_by_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_date_modified_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_modified_by_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_modification_id_t", "\\N"); while (true) { BudgetYears[0] = null; BudgetYears[2] = null; string[] OldRow = BudgetParser.ReadNextRow(); if (OldRow == null) { break; } if (LedgerNumber != GetValue(BudgetColumnNames, OldRow, "a_ledger_number_i")) { LedgerNumber = GetValue(BudgetColumnNames, OldRow, "a_ledger_number_i"); // gets the current financial year for the current ledger GetLedgerCurrentYear(LedgerNumber, out BudgetYears[1]); } GetBudgetPeriods(BudgetPeriodParser, BudgetPeriodColumnNames, ref OldBudgetPeriodRow, GetValue(BudgetColumnNames, OldRow, "a_budget_sequence_i"), out BudgetPeriodRows); GetBudgetYears(BudgetPeriodColumnNames, BudgetPeriodRows, ref BudgetYears); for (int i = 0; i < 3; i++) { if (BudgetYears[i] != null) { SequenceNumber++; foreach (string[] BudgetPeriodRow in BudgetPeriodRows) { RowCounter++; SetValue(AColumnNames, ref ANewRow, "a_budget_sequence_i", SequenceNumber.ToString()); SetValue(AColumnNames, ref ANewRow, "a_period_number_i", GetValue(BudgetPeriodColumnNames, BudgetPeriodRow, "a_period_number_i")); if (i == 0) { SetValue(AColumnNames, ref ANewRow, "a_budget_base_n", GetValue(BudgetPeriodColumnNames, BudgetPeriodRow, "a_budget_last_year_n")); } else if (i == 1) { SetValue(AColumnNames, ref ANewRow, "a_budget_base_n", GetValue(BudgetPeriodColumnNames, BudgetPeriodRow, "a_budget_this_year_n")); } else if (i == 2) { SetValue(AColumnNames, ref ANewRow, "a_budget_base_n", GetValue(BudgetPeriodColumnNames, BudgetPeriodRow, "a_budget_next_year_n")); } AWriter.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); if (AWriterTest != null) { AWriterTest.WriteLine("BEGIN; " + "COPY a_budget_period FROM stdin;"); AWriterTest.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); AWriterTest.WriteLine("\\."); AWriterTest.WriteLine("ROLLBACK;"); } } } } } return RowCounter; }
private static string GetSupplierCurrency(Int64 SupplierPartnerKey) { if (SupplierCurrencies == null) { SupplierCurrencies = new SortedList <long, string>(); // read supplier currencies from a_ap_supplier file TTable supplierTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("a_ap_supplier"); TParseProgressCSV Parser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "a_ap_supplier.d.gz", supplierTableOld.grpTableField.Count); StringCollection ColumnNames = GetColumnNames(supplierTableOld); while (true) { string[] OldRow = Parser.ReadNextRow(); if (OldRow == null) { break; } SupplierCurrencies.Add(Convert.ToInt64(GetValue(ColumnNames, OldRow, "p_partner_key_n")), GetValue(ColumnNames, OldRow, "a_currency_code_c")); } } return SupplierCurrencies[SupplierPartnerKey]; }
/// <summary> /// fix data that would cause problems for PostgreSQL constraints /// </summary> public static int MigrateData(TParseProgressCSV AParser, StreamWriter AWriter, StreamWriter AWriterTest, TTable AOldTable, TTable ANewTable) { StringCollection OldColumnNames = null; StringCollection NewColumnNames; int RowCounter = 0; if (AOldTable != null) { OldColumnNames = GetColumnNames(AOldTable); } NewColumnNames = GetColumnNames(ANewTable); string[] NewRow = CreateRow(NewColumnNames); // This existing and populated table's data is completely changed. We do not want to import any of it's contents. if ((ANewTable.strName == "pt_language_level") || (ANewTable.strName == "p_partner_attribute_category")) { RowCounter += FixData(ANewTable.strName, NewColumnNames, ref NewRow, AWriter, AWriterTest); return RowCounter; } List <TTableField>MappingOfFields = new List <TTableField>(); List <string>DefaultValues = new List <string>(); PrepareTable(AOldTable, ANewTable, ref MappingOfFields, ref DefaultValues); while (true) { string[] OldRow = AParser.ReadNextRow(); if (OldRow == null) { break; } FixRow(OldRow, ref NewRow, OldColumnNames, NewColumnNames, ANewTable, MappingOfFields, DefaultValues); if (FixData(ANewTable.strName, NewColumnNames, ref NewRow)) { RowCounter++; AWriter.WriteLine(CSVFile.StrMergeSpecial(NewRow)); if (AWriterTest != null) { AWriterTest.WriteLine("BEGIN; " + "COPY " + ANewTable.strName + " FROM stdin;"); AWriterTest.WriteLine(CSVFile.StrMergeSpecial(NewRow)); AWriterTest.WriteLine("\\."); AWriterTest.WriteLine("ROLLBACK;"); } } } RowCounter += FixData(ANewTable.strName, NewColumnNames, ref NewRow, AWriter, AWriterTest); return RowCounter; }
/// <summary> /// process the data from the Progress dump file, so that Postgresql can read the result /// </summary> public void ProcessAndWritePostgresqlFile(string dumpFile, TTable newTable) { string oldTableName = DataDefinitionDiff.GetOldTableName(newTable.strName); TTable oldTable = storeOld.GetTable(oldTableName); // if this is a new table in OpenPetra, do not dump anything. the table will be empty in OpenPetra // (except p_postcode_region_range, a_budget_revision and p_partner_gift_destination which are populated here) if ((oldTable == null) && (newTable.strName != "p_postcode_region_range") && (newTable.strName != "a_budget_revision") && (newTable.strName != "p_partner_gift_destination") && (newTable.strName != "p_partner_attribute_category") && (newTable.strName != "p_partner_attribute_type")) { return; } string NewFileName = TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + newTable.strName + ".sql.gz"; // if (File.Exists(NewFileName)) // { // // for debugging: ignore files that have been written already // return; // } StreamWriter MyWriterCount = null; StreamWriter MyWriterTest = null; FileStream outStreamTest; Stream gzoStreamTest; TParseProgressCSV Parser = null; try { if (oldTable != null) { Parser = new TParseProgressCSV( dumpFile + ".d.gz", oldTable.grpTableField.Count); } FileStream outStream = File.Create(NewFileName); Stream gzoStream = new GZipOutputStream(outStream); StreamWriter MyWriter = new StreamWriter(gzoStream, Encoding.UTF8); if (TAppSettingsManager.GetValue("create_test_files", "true", false) == "true") { outStreamTest = File.Create( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + newTable.strName + "_test.sql.gz"); gzoStreamTest = new GZipOutputStream(outStreamTest); MyWriterTest = new StreamWriter(gzoStreamTest, Encoding.UTF8); } string rowCountDir = TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "_row_count.txt"; if (File.Exists(rowCountDir)) { MyWriterCount = File.AppendText(rowCountDir); } else { FileStream outStreamCount = File.Create(rowCountDir); MyWriterCount = new StreamWriter(outStreamCount); } // The p_partner_contact table needs to write p_contact_log records first, so handle the COPY manually if (newTable.strName != "p_partner_contact") { MyWriter.WriteLine("COPY " + newTable.strName + " FROM stdin;"); } int ProcessedRows = TFixData.MigrateData(Parser, MyWriter, MyWriterTest, oldTable, newTable); ProcessedRows += MoveTables(newTable.strName, dumpFile, MyWriter, MyWriterTest, newTable); MyWriter.WriteLine("\\."); MyWriter.WriteLine(); if (MyWriterTest != null) { MyWriterTest.WriteLine(); } MyWriterCount.WriteLine(newTable.strName); MyWriterCount.WriteLine(ProcessedRows); MyWriter.Close(); TLogging.Log(" after processing file, rows: " + ProcessedRows.ToString()); } catch (Exception e) { TLogging.Log("Memory usage: " + (GC.GetTotalMemory(false) / 1024 / 1024).ToString() + " MB"); TLogging.Log("WARNING Problems processing file " + dumpFile + ": " + e.ToString()); if (File.Exists(dumpFile + ".sql.gz")) { File.Delete(dumpFile + ".sql.gz"); } } finally { if (MyWriterCount != null) { MyWriterCount.Close(); } if (MyWriterTest != null) { MyWriterTest.Close(); } } }
private static int FixData(string ATableName, StringCollection AColumnNames, ref string[] ANewRow, StreamWriter AWriter, StreamWriter AWriterTest) { int RowCounter = 0; if (ATableName == "a_budget_revision") { RowCounter = TFinanceBudgetUpgrader.PopulateABudgetRevision(AColumnNames, ref ANewRow, AWriter, AWriterTest); } if (ATableName == "a_budget") { RowCounter = TFinanceBudgetUpgrader.FixABudget(AColumnNames, ref ANewRow, AWriter, AWriterTest); } if (ATableName == "a_budget_period") { RowCounter = TFinanceBudgetUpgrader.FixABudgetPeriod(AColumnNames, ref ANewRow, AWriter, AWriterTest); } if (ATableName == "p_partner_contact") { RowCounter = TContactLogs.PopulatePContactLog(AColumnNames, ref ANewRow, AWriter, AWriterTest); } if (ATableName == "p_partner_gift_destination") { RowCounter = TPartnerGiftDestination.PopulatePPartnerGiftDestination(AColumnNames, ref ANewRow, AWriter, AWriterTest); } if (ATableName == "p_partner_attribute") { RowCounter = TPartnerContactDetails.PopulatePPartnerAttribute(AColumnNames, ref ANewRow, AWriter, AWriterTest); } if (ATableName == "s_system_defaults") { // load the file a_system_parameter.d.gz so that we can access s_system_parameter, s_site_key_n TTable systemParameterTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("s_system_parameter"); TParseProgressCSV Parser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "s_system_parameter.d.gz", systemParameterTableOld.grpTableField.Count); StringCollection ColumnNames = GetColumnNames(systemParameterTableOld); while (true) { string[] OldRow = Parser.ReadNextRow(); if (OldRow == null) { break; } // needs to be added to s_system_defaults name=SiteKey string SiteKey = GetValue(ColumnNames, OldRow, "s_site_key_n"); SetValue(AColumnNames, ref ANewRow, "s_default_code_c", "SiteKey"); SetValue(AColumnNames, ref ANewRow, "s_default_description_c", "there has to be one site key for the database"); SetValue(AColumnNames, ref ANewRow, "s_default_value_c", SiteKey); AWriter.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); if (AWriterTest != null) { AWriterTest.WriteLine("BEGIN; " + "COPY " + ATableName + " FROM stdin;"); AWriterTest.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); AWriterTest.WriteLine("\\."); AWriterTest.WriteLine("ROLLBACK;"); } RowCounter++; } } // this is a new table with new data (also in basedata) if (ATableName == "pt_skill_category") { // Default categories that old abilities and qualifications are mapped to. Basedata - pt_skill_category string[, ] SkillCategories = new string[, ] { { "COMMUNICATION", "e.g. Graphic Designer, Journalist, Photographer" }, { "EDUCATION", "e.g. Teacher, Lecturer" }, { "FINANCE", "e.g. Accountant, Auditor, Bookkeeper" }, { "FOOD", "e.g. Caterer, Baker, Cook" }, { "LAW", "e.g. Solicitor, Lawyer, Judge" }, { "MEDICAL", "e.g. Dentist, Doctor, Nurse" }, { "MINISTRY", "e.g. Pastor, Evangelist, Counsellor" }, { "MUSIC", "e.g. Musician, Singer" }, { "OFFICE", "e.g. Manager, Secretary, Computer Programmer" }, { "OTHER", "" }, { "PEOPLE", "e.g. Personnel Administrator, Social Worker" }, { "PRACTICAL", "e.g. Cleaner, Driver, Farmer" }, { "SEA", "e.g. Captain, Engineer, Deck Hand" }, { "TECHNICAL", "e.g. Engineer, Carpenter, Electrician" } }; for (int i = 0; i < SkillCategories.GetLength(0); i++) { SetValue(AColumnNames, ref ANewRow, "pt_code_c", SkillCategories[i, 0]); SetValue(AColumnNames, ref ANewRow, "pt_description_c", SkillCategories[i, 1]); SetValue(AColumnNames, ref ANewRow, "pt_unassignable_flag_l", "0"); SetValue(AColumnNames, ref ANewRow, "pt_unassignable_date_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "pt_deletable_flag_l", "0"); SetValue(AColumnNames, ref ANewRow, "s_date_created_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_created_by_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_date_modified_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_modified_by_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_modification_id_t", "\\N"); AWriter.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); if (AWriterTest != null) { AWriterTest.WriteLine("BEGIN; " + "COPY " + ATableName + " FROM stdin;"); AWriterTest.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); AWriterTest.WriteLine("\\."); AWriterTest.WriteLine("ROLLBACK;"); } RowCounter++; } } // this is a new table with new data (also in basedata) if (ATableName == "pt_skill_level") { // Default levels that old abilities and qualifications levels are mapped to. Basedata - pt_skill_level string[] SkillLevel = new string[] { "1", "2", "3", "4", "99" }; string[] SkillLevelDescription = new string[] { "Basic", "Moderate", "Competent", "Professional", "Level of ability not known" }; for (int i = 0; i < SkillLevel.Length; i++) { SetValue(AColumnNames, ref ANewRow, "pt_level_i", SkillLevel[i]); SetValue(AColumnNames, ref ANewRow, "pt_description_c", SkillLevelDescription[i]); SetValue(AColumnNames, ref ANewRow, "pt_unassignable_flag_l", "0"); SetValue(AColumnNames, ref ANewRow, "pt_unassignable_date_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "pt_deletable_flag_l", "0"); SetValue(AColumnNames, ref ANewRow, "s_date_created_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_created_by_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_date_modified_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_modified_by_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_modification_id_t", "\\N"); AWriter.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); if (AWriterTest != null) { AWriterTest.WriteLine("BEGIN; " + "COPY " + ATableName + " FROM stdin;"); AWriterTest.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); AWriterTest.WriteLine("\\."); AWriterTest.WriteLine("ROLLBACK;"); } RowCounter++; } } // data from pm_person_ability and pm_person_qualification are copied into this new table if (ATableName == "pm_person_skill") { // Default categories that old abilities and qualifications are mapped to. Basedata - pt_skill_category string[] SkillCategories = new string[] { "COMMUNICATION", "EDUCATION", "FINANCE", "FOOD", "LAW", "MEDICAL", "MINISTRY", "MUSIC", "OFFICE", "PEOPLE", "PRACTICAL", "SEA", "TECHNICAL" }; String SkillCategory; String Description; int SkillLevel; //*** Copy from pm_person_ability ***// // load the file pm_person_ability.d.gz TTable PersonAbility = TDumpProgressToPostgresql.GetStoreOld().GetTable("pm_person_ability"); TParseProgressCSV ParserAbility = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "pm_person_ability.d.gz", PersonAbility.grpTableField.Count); StringCollection PersonAbilityColumnNames = GetColumnNames(PersonAbility); // these columns will be the same for all records SetValue(AColumnNames, ref ANewRow, "pm_description_local_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "pm_description_language_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "pm_current_occupation_l", "0"); SetValue(AColumnNames, ref ANewRow, "pm_degree_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "pm_year_of_degree_i", "0"); string[] FixedRow = CreateRow(PersonAbilityColumnNames); List <TTableField>MappingOfFields = new List <TTableField>(); List <string>DefaultValues = new List <string>(); PrepareTable(PersonAbility, PersonAbility, ref MappingOfFields, ref DefaultValues); while (true) { string[] OldRow = ParserAbility.ReadNextRow(); if (OldRow == null) { break; } // we need to fix the row of PersonAbility, eg. so that the s_date_created_d will be converted for Postgresql FixRow(OldRow, ref FixedRow, PersonAbilityColumnNames, PersonAbilityColumnNames, PersonAbility, MappingOfFields, DefaultValues); // map old ability_area_name to new skill category String AbilityAreaName = GetValue(PersonAbilityColumnNames, FixedRow, "pt_ability_area_name_c"); SkillCategory = "OTHER"; Description = ""; foreach (string Category in SkillCategories) { if (AbilityAreaName.Substring(0, 3) == Category.Substring(0, 3)) { SkillCategory = Category; break; } } // copy old ability_area description from pt_ability_area to new description // load the file pt_ability_area.d.gz so that we can access the values for each person TTable AbilityArea = TDumpProgressToPostgresql.GetStoreOld().GetTable("pt_ability_area"); TParseProgressCSV ParserAbilityArea = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "pt_ability_area.d.gz", AbilityArea.grpTableField.Count); StringCollection AbilityAreaColumnNames = GetColumnNames(AbilityArea); while (true) { string[] OldAbilityRow = ParserAbilityArea.ReadNextRow(); if (OldAbilityRow == null) { break; } if (GetValue(AbilityAreaColumnNames, OldAbilityRow, "pt_ability_area_name_c") == AbilityAreaName) { Description = GetValue(AbilityAreaColumnNames, OldAbilityRow, "pt_ability_area_descr_c"); break; } } // map old ability level to new skill level int AbilityLevel = Convert.ToInt32(GetValue(PersonAbilityColumnNames, FixedRow, "pt_ability_level_i")); SkillLevel = 99; // remains 99 if unknown if ((AbilityLevel >= 0) && (AbilityLevel <= 3)) { SkillLevel = 1; } else if ((AbilityLevel >= 4) && (AbilityLevel <= 5)) { SkillLevel = 2; } else if ((AbilityLevel >= 6) && (AbilityLevel <= 7)) { SkillLevel = 3; } else if ((AbilityLevel >= 8) && (AbilityLevel <= 10)) { SkillLevel = 4; } string Comment = GetValue(PersonAbilityColumnNames, FixedRow, "pm_comment_c"); if (SkillCategory == "OTHER") { Comment += " Copied from Petra (Ability Area Name: " + AbilityAreaName; } SetValue(AColumnNames, ref ANewRow, "pm_person_skill_key_i", RowCounter.ToString()); SetValue(AColumnNames, ref ANewRow, "p_partner_key_n", GetValue(PersonAbilityColumnNames, FixedRow, "p_partner_key_n")); SetValue(AColumnNames, ref ANewRow, "pm_skill_category_code_c", SkillCategory); SetValue(AColumnNames, ref ANewRow, "pm_description_english_c", Description); SetValue(AColumnNames, ref ANewRow, "pm_skill_level_i", SkillLevel.ToString()); SetValue(AColumnNames, ref ANewRow, "pm_years_of_experience_i", GetValue(PersonAbilityColumnNames, FixedRow, "pm_years_of_experience_i")); SetValue(AColumnNames, ref ANewRow, "pm_years_of_experience_as_of_d", GetValue(PersonAbilityColumnNames, FixedRow, "pm_years_of_experience_as_of_d")); SetValue(AColumnNames, ref ANewRow, "pm_professional_skill_l", "0"); SetValue(AColumnNames, ref ANewRow, "pm_comment_c", Comment); SetValue(AColumnNames, ref ANewRow, "s_date_created_d", GetValue(PersonAbilityColumnNames, FixedRow, "s_date_created_d")); SetValue(AColumnNames, ref ANewRow, "s_created_by_c", GetValue(PersonAbilityColumnNames, FixedRow, "s_created_by_c")); SetValue(AColumnNames, ref ANewRow, "s_date_modified_d", GetValue(PersonAbilityColumnNames, FixedRow, "s_date_modified_d")); SetValue(AColumnNames, ref ANewRow, "s_modified_by_c", GetValue(PersonAbilityColumnNames, FixedRow, "s_modified_by_c")); SetValue(AColumnNames, ref ANewRow, "s_modification_id_t", GetValue(PersonAbilityColumnNames, FixedRow, "s_modification_id_t")); AWriter.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); if (AWriterTest != null) { AWriterTest.WriteLine("BEGIN; " + "COPY " + ATableName + " FROM stdin;"); AWriterTest.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); AWriterTest.WriteLine("\\."); AWriterTest.WriteLine("ROLLBACK;"); } RowCounter++; } //*** Copy from pm_person_qualification ***// // load the file pm_person_qualification.d.gz TTable PersonQualification = TDumpProgressToPostgresql.GetStoreOld().GetTable("pm_person_qualification"); TParseProgressCSV ParserQualification = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "pm_person_qualification.d.gz", PersonQualification.grpTableField.Count); StringCollection PersonQualificationColumnNames = GetColumnNames(PersonQualification); FixedRow = CreateRow(PersonQualificationColumnNames); MappingOfFields = new List <TTableField>(); DefaultValues = new List <string>(); PrepareTable(PersonQualification, PersonQualification, ref MappingOfFields, ref DefaultValues); while (true) { string[] OldRow = ParserQualification.ReadNextRow(); if (OldRow == null) { break; } // we need to fix the row of PersonQualification, eg. so that the s_date_created_d will be converted for Postgresql FixRow(OldRow, ref FixedRow, PersonQualificationColumnNames, PersonQualificationColumnNames, PersonQualification, MappingOfFields, DefaultValues); // map old qualification_area_name to new skill category String QualificationAreaName = GetValue(PersonQualificationColumnNames, FixedRow, "pt_qualification_area_name_c"); SkillCategory = "OTHER"; Description = ""; foreach (string Category in SkillCategories) { if (QualificationAreaName.Substring(0, 3) == Category.Substring(0, 3)) { SkillCategory = Category; break; } } // copy old qualification_area description from pt_qualification_area to new description // load the file pt_ability_area.d.gz so that we can access the values for each person TTable QualificationArea = TDumpProgressToPostgresql.GetStoreOld().GetTable("pt_qualification_area"); TParseProgressCSV ParserQualificationArea = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "pt_qualification_area.d.gz", QualificationArea.grpTableField.Count); StringCollection QualificationAreaColumnNames = GetColumnNames(QualificationArea); while (true) { string[] OldQualificationRow = ParserQualificationArea.ReadNextRow(); if (OldQualificationRow == null) { break; } if (GetValue(QualificationAreaColumnNames, OldQualificationRow, "pt_qualification_area_name_c") == QualificationAreaName) { Description = GetValue(QualificationAreaColumnNames, OldQualificationRow, "pt_qualification_area_descr_c"); break; } } // map old Qualification level to new skill level int QualificationLevel = Convert.ToInt32(GetValue(PersonQualificationColumnNames, FixedRow, "pt_qualification_level_i")); SkillLevel = 99; // remains 99 if unknown if ((QualificationLevel >= 0) && (QualificationLevel <= 3)) { SkillLevel = 1; } else if ((QualificationLevel >= 4) && (QualificationLevel <= 5)) { SkillLevel = 2; } else if ((QualificationLevel >= 6) && (QualificationLevel <= 7)) { SkillLevel = 3; } else if ((QualificationLevel >= 8) && (QualificationLevel <= 10)) { SkillLevel = 4; } string Comment = GetValue(PersonQualificationColumnNames, FixedRow, "pm_comment_c"); if (SkillCategory == "OTHER") { Comment += " Copied from Petra (Qualification Area Name: " + QualificationAreaName; } // copy old qualification level description from pt_qualification_area to new comment // load the file pt_qualification_level.d.gz so that we can access the values for each person TTable QualificationLevelTable = TDumpProgressToPostgresql.GetStoreOld().GetTable("pt_qualification_level"); TParseProgressCSV ParserQualificationLevel = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "pt_qualification_level.d.gz", QualificationLevelTable.grpTableField.Count); StringCollection QualificationLevelColumnNames = GetColumnNames(QualificationLevelTable); while (true) { string[] OldQualificationLevelRow = ParserQualificationLevel.ReadNextRow(); if (OldQualificationLevelRow == null) { break; } if (GetValue(QualificationLevelColumnNames, OldQualificationLevelRow, "pt_qualification_level_i") == QualificationLevel.ToString()) { Comment += " Qualification Level from Petra: " + QualificationLevel + " - " + GetValue(QualificationLevelColumnNames, OldQualificationLevelRow, "pt_qualification_level_descr_c"); break; } } SetValue(AColumnNames, ref ANewRow, "pm_person_skill_key_i", RowCounter.ToString()); SetValue(AColumnNames, ref ANewRow, "p_partner_key_n", GetValue(PersonQualificationColumnNames, FixedRow, "p_partner_key_n")); SetValue(AColumnNames, ref ANewRow, "pm_skill_category_code_c", SkillCategory); SetValue(AColumnNames, ref ANewRow, "pm_description_english_c", Description); SetValue(AColumnNames, ref ANewRow, "pm_skill_level_i", SkillLevel.ToString()); SetValue(AColumnNames, ref ANewRow, "pm_years_of_experience_i", GetValue(PersonQualificationColumnNames, FixedRow, "pm_years_of_experience_i")); SetValue(AColumnNames, ref ANewRow, "pm_years_of_experience_as_of_d", GetValue(PersonQualificationColumnNames, FixedRow, "pm_years_of_experience_as_of_d")); SetValue(AColumnNames, ref ANewRow, "pm_professional_skill_l", "1"); SetValue(AColumnNames, ref ANewRow, "pm_comment_c", Comment); SetValue(AColumnNames, ref ANewRow, "s_date_created_d", GetValue(PersonAbilityColumnNames, FixedRow, "s_date_created_d")); SetValue(AColumnNames, ref ANewRow, "s_created_by_c", GetValue(PersonAbilityColumnNames, FixedRow, "s_created_by_c")); SetValue(AColumnNames, ref ANewRow, "s_date_modified_d", GetValue(PersonAbilityColumnNames, FixedRow, "s_date_modified_d")); SetValue(AColumnNames, ref ANewRow, "s_modified_by_c", GetValue(PersonAbilityColumnNames, FixedRow, "s_modified_by_c")); SetValue(AColumnNames, ref ANewRow, "s_modification_id_t", GetValue(PersonAbilityColumnNames, FixedRow, "s_modification_id_t")); AWriter.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); if (AWriterTest != null) { AWriterTest.WriteLine("BEGIN; " + "COPY " + ATableName + " FROM stdin;"); AWriterTest.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); AWriterTest.WriteLine("\\."); AWriterTest.WriteLine("ROLLBACK;"); } RowCounter++; } } // This existing and populated table's data is completely changed. We do not want to import any of it's contents. // This data is also in the basedata if (ATableName == "pt_language_level") { // Default language levels data string[] LanguageLevels = new string[] { "1", "2", "3", "99" }; string[] LanguageLevelDescriptions = new string[] { "BASIC", "INTERMEDIATE", "ADVANCED", "UNKNOWN" }; string[] LanguageComments = new string[] { "Uses a narrow range of language, adequate for basic needs and simple situations. " + "Does not really have sufficient language to cope with normal day-to-day, real-life communication, " + "but basic communication is possible with adequate opportunities for assistance.", "Uses the language independently and effectively in familiar situations. Rather frequent lapses in accuracy, fluency, " + "appropriateness and organisation, but usually succeeds in communication and comprehending the general message.", "Uses a full range of language with proficiency approaching that in the learner's own mother tongue. " + "Copes well even with demanding and complex language situations. Makes minor lapses in accuracy, fluency, " + "appropriateness and organisation which do not affect communication.", "Speaks the language to some extent, level unknown." }; for (int i = 0; i < 4; i++) { SetValue(AColumnNames, ref ANewRow, "pt_language_level_i", LanguageLevels[i]); SetValue(AColumnNames, ref ANewRow, "pt_language_level_descr_c", LanguageLevelDescriptions[i]); SetValue(AColumnNames, ref ANewRow, "pt_unassignable_flag_l", "0"); SetValue(AColumnNames, ref ANewRow, "pt_unassignable_date_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "pt_deletable_flag_l", "0"); SetValue(AColumnNames, ref ANewRow, "pt_language_comment_c", LanguageComments[i]); SetValue(AColumnNames, ref ANewRow, "s_date_created_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_created_by_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_date_modified_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_modified_by_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_modification_id_t", "\\N"); AWriter.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); if (AWriterTest != null) { AWriterTest.WriteLine("BEGIN; " + "COPY " + ATableName + " FROM stdin;"); AWriterTest.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); AWriterTest.WriteLine("\\."); AWriterTest.WriteLine("ROLLBACK;"); } RowCounter++; } } // One record which we need to make sure is in this table if (ATableName == "pm_document_category") { // load the file pm_document_category.d.gz TTable Table = TDumpProgressToPostgresql.GetStoreOld().GetTable("pm_document_category"); TParseProgressCSV Parser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "pm_document_category.d.gz", Table.grpTableField.Count); StringCollection ColumnNames = GetColumnNames(Table); // check if record already exists. If it does then return. while (true) { string[] Row = Parser.ReadNextRow(); if (Row == null) { break; } else if (GetValue(ColumnNames, Row, "pm_code_c") == "GENERAL") { return RowCounter; } } SetValue(AColumnNames, ref ANewRow, "pm_code_c", "GENERAL"); SetValue(AColumnNames, ref ANewRow, "pm_description_c", "General Documents"); SetValue(AColumnNames, ref ANewRow, "pm_extendable_l", "0"); SetValue(AColumnNames, ref ANewRow, "pm_unassignable_flag_l", "0"); SetValue(AColumnNames, ref ANewRow, "pm_unassignable_date_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "pm_deletable_flag_l", "0"); SetValue(AColumnNames, ref ANewRow, "s_date_created_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_created_by_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_date_modified_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_modified_by_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_modification_id_t", "\\N"); AWriter.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); if (AWriterTest != null) { AWriterTest.WriteLine("BEGIN; " + "COPY " + ATableName + " FROM stdin;"); AWriterTest.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); AWriterTest.WriteLine("\\."); AWriterTest.WriteLine("ROLLBACK;"); } RowCounter++; } // One record which we need to make sure is in this table if (ATableName == "pm_document_type") { // load the file pm_document_type.d.gz TTable Table = TDumpProgressToPostgresql.GetStoreOld().GetTable("pm_document_type"); TParseProgressCSV Parser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "pm_document_type.d.gz", Table.grpTableField.Count); StringCollection ColumnNames = GetColumnNames(Table); // check if record already exists. If it does then return. while (true) { string[] Row = Parser.ReadNextRow(); if (Row == null) { break; } else if (GetValue(ColumnNames, Row, "pm_doc_code_c") == "DRIVINGLICENCE") { return RowCounter; } } SetValue(AColumnNames, ref ANewRow, "pm_doc_code_c", "DRIVINGLICENCE"); SetValue(AColumnNames, ref ANewRow, "pm_doc_category_c", "GENERAL"); SetValue(AColumnNames, ref ANewRow, "pm_description_c", "Driving Licence"); SetValue(AColumnNames, ref ANewRow, "pm_unassignable_flag_l", "0"); SetValue(AColumnNames, ref ANewRow, "pm_unassignable_date_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "pm_deletable_flag_l", "0"); SetValue(AColumnNames, ref ANewRow, "s_date_created_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_created_by_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_date_modified_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_modified_by_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_modification_id_t", "\\N"); AWriter.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); if (AWriterTest != null) { AWriterTest.WriteLine("BEGIN; " + "COPY " + ATableName + " FROM stdin;"); AWriterTest.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); AWriterTest.WriteLine("\\."); AWriterTest.WriteLine("ROLLBACK;"); } RowCounter++; } // add new documents for driving licences recorded in pm_personal_data if (ATableName == "pm_document") { // find the site key string SiteKey = "0"; TTable SystemParameterTable = TDumpProgressToPostgresql.GetStoreOld().GetTable("s_system_parameter"); TParseProgressCSV SystemParameterParser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "s_system_parameter.d.gz", SystemParameterTable.grpTableField.Count); StringCollection SystemParameterColumnNames = GetColumnNames(SystemParameterTable); while (true) { string[] SystemParameterRow = SystemParameterParser.ReadNextRow(); if (SystemParameterRow == null) { break; } SiteKey = GetValue(SystemParameterColumnNames, SystemParameterRow, "s_site_key_n"); if (SiteKey != "0") { break; } } // load the file pm_personal_data.d.gz TTable Table = TDumpProgressToPostgresql.GetStoreOld().GetTable("pm_personal_data"); TParseProgressCSV Parser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "pm_personal_data.d.gz", Table.grpTableField.Count); StringCollection ColumnNames = GetColumnNames(Table); // move sequence on by 1 TSequenceWriter.GetNextSequenceValue("seq_document"); while (true) { string[] Row = Parser.ReadNextRow(); if (Row == null) { break; } if (GetValue(ColumnNames, Row, "pm_gen_driver_license_l") == "yes") { // create comment from old data string Comment = ""; string DriverStatus = GetValue(ColumnNames, Row, "pm_driver_status_c"); if (GetValue(ColumnNames, Row, "pm_om_driver_license_l") == "yes") { Comment = Catalog.GetString("Approved to drive for us.") + " "; } if (!string.IsNullOrEmpty(DriverStatus)) { // load the file pt_driver_status.d.gz TTable DriverStatusTable = TDumpProgressToPostgresql.GetStoreOld().GetTable("pt_driver_status"); TParseProgressCSV DriverStatusParser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "pt_driver_status.d.gz", DriverStatusTable.grpTableField.Count); while (true) { string[] DriverStatusRow = DriverStatusParser.ReadNextRow(); if (DriverStatusRow == null) { break; } if (DriverStatusRow[0] == DriverStatus) { Comment += Catalog.GetString("Driver status") + ": " + DriverStatusRow[1]; break; } } } SetValue(AColumnNames, ref ANewRow, "p_site_key_n", SiteKey); SetValue(AColumnNames, ref ANewRow, "pm_document_key_n", TSequenceWriter.GetNextSequenceValue("seq_document").ToString()); SetValue(AColumnNames, ref ANewRow, "p_partner_key_n", GetValue(ColumnNames, Row, "p_partner_key_n")); SetValue(AColumnNames, ref ANewRow, "pm_doc_code_c", "DRIVINGLICENCE"); SetValue(AColumnNames, ref ANewRow, "pm_document_id_c", GetValue(ColumnNames, Row, "pm_driving_license_number_c")); SetValue(AColumnNames, ref ANewRow, "pm_place_of_issue_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "pm_date_of_issue_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "pm_date_of_start_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "pm_date_of_expiration_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "pm_doc_comment_c", Comment); SetValue(AColumnNames, ref ANewRow, "pm_assoc_doc_id_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "pm_contact_partner_key_n", "0"); SetValue(AColumnNames, ref ANewRow, "s_date_created_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_created_by_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_date_modified_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_modified_by_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_modification_id_t", "\\N"); AWriter.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); if (AWriterTest != null) { AWriterTest.WriteLine("BEGIN; " + "COPY " + ATableName + " FROM stdin;"); AWriterTest.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); AWriterTest.WriteLine("\\."); AWriterTest.WriteLine("ROLLBACK;"); } RowCounter++; } } } // this is a new table with new data (also in basedata) if (ATableName == "p_partner_attribute_category") { // Default Categories that p_partner_attribute_type records are mapped to. Basedata - p_partner_attribute_category string[, ] AttributeCategories = new string[, ] { { "Phone", "Phone and Fax numbers", "0", "1", "0" }, { "E-Mail", "E-Mail addresses", "1", "1", "0" }, { "Digital Media", "Social Media, Web Sites & Blogs", "2", "1", "0" }, { "Instant Messaging & Chat", "Instant Messaging, Internet Voice Communication", "3", "1", "0" }, { "PARTNERS_CONTACTDETAILS_SETTINGS", "A Partners' Contact Details Settings", "0", "0", "1" } }; for (int i = 0; i < AttributeCategories.GetLength(0); i++) { SetValue(AColumnNames, ref ANewRow, "p_category_code_c", AttributeCategories[i, 0]); SetValue(AColumnNames, ref ANewRow, "p_category_desc_c", AttributeCategories[i, 1]); SetValue(AColumnNames, ref ANewRow, "p_index_i", AttributeCategories[i, 2]); SetValue(AColumnNames, ref ANewRow, "p_partner_contact_category_l", AttributeCategories[i, 3]); SetValue(AColumnNames, ref ANewRow, "p_system_category_l", AttributeCategories[i, 4]); SetValue(AColumnNames, ref ANewRow, "p_deletable_l", "0"); SetValue(AColumnNames, ref ANewRow, "s_date_created_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_created_by_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_date_modified_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_modified_by_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_modification_id_t", "\\N"); AWriter.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); if (AWriterTest != null) { AWriterTest.WriteLine("BEGIN; " + "COPY " + ATableName + " FROM stdin;"); AWriterTest.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); AWriterTest.WriteLine("\\."); AWriterTest.WriteLine("ROLLBACK;"); } RowCounter++; } } // this table existed, but it has a different schema now and it never held data in Petra 2.x. Gets filled with new data (also in basedata) if (ATableName == "p_partner_attribute_type") { // Default Attribute Types that p_partner_attribute records are referring to. Basedata - p_partner_attribute_type string[, ] AttributeTypes = new string[, ] { { "Phone", "Mobile Phone", "Mobile/cellular phone number", "0", "CONTACTDETAIL_GENERAL", "\\N", "Business Mobile Phone" }, { "Phone", "Phone", "Landline phone number", "1", "CONTACTDETAIL_GENERAL", "\\N", "Business Phone" }, { "Phone", "Fax", "Facsimile number", "2", "CONTACTDETAIL_GENERAL", "\\N", "Business Fax" }, { "E-Mail", "E-Mail", "E-Mail address", "0", "CONTACTDETAIL_EMAILADDRESS", "\\N", "Business E-Mail" }, { "E-Mail", "Secure E-Mail", "Secure E-Mail address", "1", "CONTACTDETAIL_EMAILADDRESS", "\\N", "Business Secure E-Mail" }, { "Digital Media", "Facebook", "Facebook user name", "0", "CONTACTDETAIL_HYPERLINK_WITHVALUE", "https://www.facebook.com/{VALUE}", "Facebook (Business)" }, { "Digital Media", "Twitter", "Twitter user name", "1", "CONTACTDETAIL_HYPERLINK_WITHVALUE", "http://www.twitter.com/{VALUE}", "Twitter (Business)" }, { "Digital Media", "LinkedIn", "LinkedIn user name", "2", "CONTACTDETAIL_HYPERLINK", "\\N", "LinkedIn (Business)" }, { "Digital Media", "Xing", "Xing user name", "3", "CONTACTDETAIL_HYPERLINK_WITHVALUE", "http://www.xing.com/profile/{VALUE}", "Xing (Business)" }, { "Digital Media", "Instagram", "Instagram user name", "4", "CONTACTDETAIL_HYPERLINK", "\\N", "Instagram (Business)" }, { "Digital Media", "Pinterest", "Pinterest user name", "5", "CONTACTDETAIL_HYPERLINK", "\\N", "Pinterest (Business)" }, { "Digital Media", "Web Site", "Internet address of a Web Site", "6", "CONTACTDETAIL_HYPERLINK", "\\N", "Business Web Site" }, { "Digital Media", "Blog", "Internet address of a Blog", "7", "CONTACTDETAIL_HYPERLINK", "\\N", "Business Blog" }, { "Instant Messaging & Chat", "Skype", "Skype ID", "0", "CONTACTDETAIL_SKYPEID", "\\N", "Skype (Business)" }, { "Instant Messaging & Chat", "Lync", "Lync ID", "1", "CONTACTDETAIL_GENERAL", "\\N", "Lync (Business)" }, { "Instant Messaging & Chat", "WhatsApp", "WhatsApp ID", "2", "CONTACTDETAIL_GENERAL", "\\N", "WhatsApp (Business)" }, { "Instant Messaging & Chat", "Yahoo", "Yahoo ID", "3", "CONTACTDETAIL_GENERAL", "\\N", "Yahoo (Business)" }, { "Instant Messaging & Chat", "FaceTime", "FaceTime ID", "4", "CONTACTDETAIL_GENERAL", "\\N", "FaceTime (Business)" }, { "PARTNERS_CONTACTDETAILS_SETTINGS", "PARTNERS_PRIMARY_CONTACT_METHOD", "A Partners' Primary Contact Method", "0", "CONTACTDETAILSETTING", "\\N", "\\N" } }; for (int i = 0; i < AttributeTypes.GetLength(0); i++) { SetValue(AColumnNames, ref ANewRow, "p_category_code_c", AttributeTypes[i, 0]); SetValue(AColumnNames, ref ANewRow, "p_attribute_type_c", AttributeTypes[i, 1]); SetValue(AColumnNames, ref ANewRow, "p_description_c", AttributeTypes[i, 2]); SetValue(AColumnNames, ref ANewRow, "p_index_i", AttributeTypes[i, 3]); SetValue(AColumnNames, ref ANewRow, "p_attribute_type_value_kind_c", AttributeTypes[i, 4]); SetValue(AColumnNames, ref ANewRow, "p_hyperlink_format_c", AttributeTypes[i, 5]); SetValue(AColumnNames, ref ANewRow, "p_special_label_c", AttributeTypes[i, 6]); SetValue(AColumnNames, ref ANewRow, "p_unassignable_l", "0"); SetValue(AColumnNames, ref ANewRow, "p_unassignable_date_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "p_deletable_l", "0"); SetValue(AColumnNames, ref ANewRow, "s_date_created_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_created_by_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_date_modified_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_modified_by_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_modification_id_t", "\\N"); AWriter.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); if (AWriterTest != null) { AWriterTest.WriteLine("BEGIN; " + "COPY " + ATableName + " FROM stdin;"); AWriterTest.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); AWriterTest.WriteLine("\\."); AWriterTest.WriteLine("ROLLBACK;"); } RowCounter++; } } return RowCounter; }
/// <summary> /// Populate the empty table PPartnerGiftDestination using PmStaffData /// </summary> public static int PopulatePPartnerGiftDestination(StringCollection AColumnNames, ref string[] ANewRow, StreamWriter AWriter, StreamWriter AWriterTest) { List <string[]> ActiveCommitments = new List <string[]>(); List <string[]> Persons = new List <string[]>(); int RowCounter = 0; // default for all new records SetValue(AColumnNames, ref ANewRow, "p_active_l", "\\N"); SetValue(AColumnNames, ref ANewRow, "p_default_gift_destination_l", "\\N"); SetValue(AColumnNames, ref ANewRow, "p_partner_class_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "p_commitment_site_key_n", "\\N"); SetValue(AColumnNames, ref ANewRow, "p_commitment_key_n", "\\N"); SetValue(AColumnNames, ref ANewRow, "p_comment_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_date_created_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_created_by_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_date_modified_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_modified_by_c", "\\N"); SetValue(AColumnNames, ref ANewRow, "s_modification_id_t", "\\N"); // load the file pm_staff_data.d.gz TTable StaffDataTable = TDumpProgressToPostgresql.GetStoreOld().GetTable("pm_staff_data"); TParseProgressCSV StaffDataParser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "pm_staff_data.d.gz", StaffDataTable.grpTableField.Count); StringCollection StaffDataColumnNames = GetColumnNames(StaffDataTable); // find which records are currently active while (true) { string[] StaffDataRow = StaffDataParser.ReadNextRow(); if (StaffDataRow == null) { break; } string strStartOfCommitment = GetValue(StaffDataColumnNames, StaffDataRow, "pm_start_of_commitment_d"); string strEndOfCommitment = GetValue(StaffDataColumnNames, StaffDataRow, "pm_end_of_commitment_d"); try { // if commitment is currently active if ((DateTime.ParseExact(strStartOfCommitment, "dd/mm/yyyy", CultureInfo.InvariantCulture) <= DateTime.Today) && ((strEndOfCommitment == "\\N") || (DateTime.ParseExact(strEndOfCommitment, "dd/mm/yyyy", CultureInfo.InvariantCulture) >= DateTime.Today)) && (strStartOfCommitment != strEndOfCommitment)) { ActiveCommitments.Add(StaffDataRow); } } catch { TLogging.Log("WARNING: Invalid date in commitment: " + strStartOfCommitment + " or " + strEndOfCommitment); } } // load the file p_person.d.gz TTable PersonTable = TDumpProgressToPostgresql.GetStoreOld().GetTable("p_person"); TParseProgressCSV PersonParser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "p_person.d.gz", PersonTable.grpTableField.Count); StringCollection PersonColumnNames = GetColumnNames(PersonTable); SortedList <string, List <PersonKeyAndRow> > FamilyKeysWithPersons = new SortedList <string, List <PersonKeyAndRow> >(); // add all Persons to a list while (true) { string[] PersonRow = PersonParser.ReadNextRow(); if (PersonRow == null) { break; } string familyKey = GetValue(PersonColumnNames, PersonRow, "p_family_key_n"); if (!FamilyKeysWithPersons.ContainsKey(familyKey)) { FamilyKeysWithPersons.Add(familyKey, new List <PersonKeyAndRow>()); } FamilyKeysWithPersons[familyKey].Add( new PersonKeyAndRow( GetValue(PersonColumnNames, PersonRow, "p_partner_key_n"), PersonRow)); Persons.Add(PersonRow); } // load the file p_family.d.gz TTable FamilyTable = TDumpProgressToPostgresql.GetStoreOld().GetTable("p_family"); TParseProgressCSV FamilyParser = new TParseProgressCSV( TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "p_family.d.gz", FamilyTable.grpTableField.Count); StringCollection FamilyColumnNames = GetColumnNames(FamilyTable); // read through each family while (true) { string[] FamilyRow = FamilyParser.ReadNextRow(); if (FamilyRow == null) { break; } string familykey = GetValue(FamilyColumnNames, FamilyRow, "p_partner_key_n"); // find Person partners belonging to the family bool CommitmentFound = false; int MinimumFamilyId = int.MaxValue; // if family contains Persons if (FamilyKeysWithPersons.ContainsKey(familykey)) { // read through each of the Family's Persons foreach (PersonKeyAndRow PersonRecord in FamilyKeysWithPersons[familykey]) { // find if the Person has a currently active commitment string[] Commitment = ActiveCommitments.Find(e => GetValue(StaffDataColumnNames, e, "p_partner_key_n") == PersonRecord.PersonKey); // if currently active commitment exists create a new Gift Destination record if (Commitment != null) { int CurrentFamilyId = Convert.ToInt32(GetValue(PersonColumnNames, PersonRecord.PersonRow, "p_old_omss_family_id_i")); if (CurrentFamilyId < MinimumFamilyId) { SetValue(AColumnNames, ref ANewRow, "p_key_i", RowCounter.ToString()); SetValue(AColumnNames, ref ANewRow, "p_partner_key_n", GetValue(FamilyColumnNames, FamilyRow, "p_partner_key_n")); SetValue(AColumnNames, ref ANewRow, "p_field_key_n", GetValue(StaffDataColumnNames, Commitment, "pm_target_field_n")); SetValue(AColumnNames, ref ANewRow, "p_comment_c", "\\N"); TTableField tf = new TTableField(); tf.strName = "pm_start_of_commitment_d"; tf.strType = "DATE"; SetValue(AColumnNames, ref ANewRow, "p_date_effective_d", TFixData.FixValue(GetValue(StaffDataColumnNames, Commitment, "pm_start_of_commitment_d"), tf)); tf.strName = "pm_end_of_commitment_d"; SetValue(AColumnNames, ref ANewRow, "p_date_expires_d", TFixData.FixValue(GetValue(StaffDataColumnNames, Commitment, "pm_end_of_commitment_d"), tf)); CommitmentFound = true; MinimumFamilyId = CurrentFamilyId; // there can only be one active gift destination per family break; } } } } // if no active commitment is found then search for a "p_om_field_key_n" and use that to create a gift destination if (!CommitmentFound) { string OMFieldKey = GetValue(FamilyColumnNames, FamilyRow, "p_om_field_key_n"); if ((OMFieldKey != "\\N") && (OMFieldKey != "0")) { SetValue(AColumnNames, ref ANewRow, "p_key_i", RowCounter.ToString()); SetValue(AColumnNames, ref ANewRow, "p_partner_key_n", GetValue(FamilyColumnNames, FamilyRow, "p_partner_key_n")); SetValue(AColumnNames, ref ANewRow, "p_field_key_n", GetValue(FamilyColumnNames, FamilyRow, "p_om_field_key_n")); DateTime LastYear = DateTime.Today.AddYears(-1); SetValue(AColumnNames, ref ANewRow, "p_date_effective_d", string.Format("{0}-{1}-{2}", LastYear.Year, LastYear.Month, LastYear.Day)); SetValue(AColumnNames, ref ANewRow, "p_date_expires_d", "\\N"); SetValue(AColumnNames, ref ANewRow, "p_comment_c", Catalog.GetString("Copied from Petra's OM Field Key.")); CommitmentFound = true; } } // write gift destination to file if (CommitmentFound) { AWriter.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); if (AWriterTest != null) { AWriterTest.WriteLine("BEGIN; " + "COPY p_partner_gift_destination FROM stdin;"); AWriterTest.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString()); AWriterTest.WriteLine("\\."); AWriterTest.WriteLine("ROLLBACK;"); } RowCounter++; } } return(RowCounter); }