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 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]); }
/// <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); }
/// <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> /// main function /// </summary> /// <param name="args"></param> public static void Main(string[] args) { new TAppSettingsManager(false); new TLogging("Ict.Tools.DataDumpPetra2.log"); if (!TAppSettingsManager.HasValue("debuglevel")) { Console.Error.WriteLine("dumps one single table or all tables from Progress Petra 2.3 into Postgresql SQL load format"); Console.Error.WriteLine( "usage: Ict.Tools.DataDumpPetra2 -debuglevel:<0..10> -table:<single table or all> -oldpetraxml:<path and filename of old petra.xml> -newpetraxml:<path and filename of petra.xml>"); Console.Error.WriteLine("will default to processing all tables, and using petra23.xml and petra.xml from the current directory"); Console.Error.WriteLine( "usage for creating fulldump23.p: Ict.Tools.DataDumpPetra2 -operation:createProgressCode"); Console.Error.WriteLine(""); } try { TLogging.DebugLevel = TAppSettingsManager.GetInt16("debuglevel", 0); if (TAppSettingsManager.GetValue("operation", false) == "createProgressCode") { TCreateFulldumpProgressCode createProgressCode = new TCreateFulldumpProgressCode(); createProgressCode.GenerateFulldumpCode(); return; } if (TAppSettingsManager.GetValue("clean", "false") == "true") { TLogging.Log("deleting all resulting files..."); // delete sql.gz files, also _*.txt string[] FilesToDelete = Directory.GetFiles(TAppSettingsManager.GetValue("fulldumpPath", "fulldump"), "*.sql.gz"); foreach (string file in FilesToDelete) { File.Delete(file); } FilesToDelete = Directory.GetFiles(TAppSettingsManager.GetValue("fulldumpPath", "fulldump"), "_*.txt"); foreach (string file in FilesToDelete) { File.Delete(file); } } StringCollection tables = StringHelper.StrSplit(TAppSettingsManager.GetValue("table", ""), ","); // the upgrade process is split into two steps, to make testing quicker // Step 1: dump from Progress Petra 2.3 to CSV files, write gz files to keep size of fulldump small // this takes about 7 minutes for the german database // use the generated fulldump23.p if ((TAppSettingsManager.GetValue("operation", "dump23") == "dump23") && File.Exists("fulldump23.r")) { TDumpProgressToPostgresql dumper = new TDumpProgressToPostgresql(); if (tables.Count == 0) { dumper.DumpTablesToCSV(String.Empty); } else { foreach (var ProcessTable in tables) { dumper.DumpTablesToCSV(ProcessTable); } } } // Step 2: produce one or several sql load files for PostgreSQL // can be called independant from first step: for all tables or just one table // for tables merged into one: append to previous file // this takes 50 minutes on my virtual machine on the german server for all tables. on a faster machine, it is only 25 minutes if (TAppSettingsManager.GetValue("operation", "load30") == "load30") { TDumpProgressToPostgresql dumper = new TDumpProgressToPostgresql(); if (tables.Count == 0) { dumper.LoadTablesToPostgresql(String.Empty); } else { foreach (var ProcessTable in tables) { dumper.LoadTablesToPostgresql(ProcessTable); } } } // Step 3: concatenate all existing sql.gz files into one load sql file, gzipped. in the correct order if (TAppSettingsManager.GetValue("operation", "createSQL") == "createSQL") { TDumpProgressToPostgresql dumper = new TDumpProgressToPostgresql(); if (tables.Count == 0) { dumper.CreateNewSQLFile(String.Empty); } else { foreach (var ProcessTable in tables) { dumper.CreateNewSQLFile(ProcessTable); } } } // TODO: also anonymize the names of the partners (use random names from external list of names)? what about amounts? } catch (Exception e) { TLogging.Log(e.Message); if (e.InnerException != null) { TLogging.Log(e.InnerException.Message); } TLogging.Log(e.StackTrace); } }
/// <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); }