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);
}