private static SortedList <DateTime, List <XmlNode> > SortGiftsByDate(string AInputBeneratorFile) { XmlDocument doc = TCsv2Xml.ParseCSVFile2Xml(AInputBeneratorFile, ","); XmlNode RecordNode = doc.FirstChild.NextSibling.FirstChild; SortedList <DateTime, List <XmlNode> > GiftsPerDate = new SortedList <DateTime, List <XmlNode> >(); while (RecordNode != null) { // depending on frequency, and start date, add the gift to the batch list string frequency = TXMLParser.GetAttribute(RecordNode, "frequency"); int monthStep = 0; DateTime startdate = Convert.ToDateTime(TXMLParser.GetAttribute(RecordNode, "startdate")); DateTime dateForGift = startdate; if (frequency == "once") { // no further gift, leave at 0 } else if (frequency == "monthly") { monthStep = 1; } else if (frequency == "quarterly") { monthStep = 3; } do { if (!GiftsPerDate.ContainsKey(dateForGift)) { GiftsPerDate.Add(dateForGift, new List <XmlNode>()); } GiftsPerDate[dateForGift].Add(RecordNode); dateForGift = dateForGift.AddMonths(monthStep); } while (monthStep > 0 && dateForGift.Year <= DateTime.Now.Year); RecordNode = RecordNode.NextSibling; } return(GiftsPerDate); }
/// <summary> /// link the fields in the current ledger /// </summary> /// <param name="AFieldCSVFile"></param> public static void GenerateFieldsFinanceOnly(string AFieldCSVFile) { XmlDocument doc = TCsv2Xml.ParseCSVFile2Xml(AFieldCSVFile, ","); XmlNode RecordNode = doc.FirstChild.NextSibling.FirstChild; GLSetupTDS GLSetupDS = new GLSetupTDS(); PCountryTable CountryTable = null; TDBTransaction Transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { CountryTable = PCountryAccess.LoadAll(Transaction); }); while (RecordNode != null) { long id = 100 + Convert.ToInt64(TXMLParser.GetAttribute(RecordNode, "id")); string CountryCode = TXMLParser.GetAttribute(RecordNode, "Name"); string UnitName = ((PCountryRow)CountryTable.Rows.Find(CountryCode)).CountryName; Int64 PartnerKey = id * 1000000; // create cost centre ACostCentreRow CostCentreRow = GLSetupDS.ACostCentre.NewRowTyped(); CostCentreRow.LedgerNumber = FLedgerNumber; CostCentreRow.CostCentreCode = (id * 100).ToString("0000"); CostCentreRow.CostCentreName = UnitName; CostCentreRow.CostCentreToReportTo = MFinanceConstants.INTER_LEDGER_HEADING; CostCentreRow.CostCentreType = MFinanceConstants.FOREIGN_CC_TYPE; GLSetupDS.ACostCentre.Rows.Add(CostCentreRow); // create foreign ledger, cost centre link validledgernumber AValidLedgerNumberRow ValidLedgerNumber = GLSetupDS.AValidLedgerNumber.NewRowTyped(); ValidLedgerNumber.LedgerNumber = FLedgerNumber; ValidLedgerNumber.PartnerKey = PartnerKey; ValidLedgerNumber.CostCentreCode = CostCentreRow.CostCentreCode; ValidLedgerNumber.IltProcessingCentre = Convert.ToInt64(MFinanceConstants.ICH_COST_CENTRE) * 10000; GLSetupDS.AValidLedgerNumber.Rows.Add(ValidLedgerNumber); RecordNode = RecordNode.NextSibling; } GLSetupTDSAccess.SubmitChanges(GLSetupDS); }
public void TestImportCSV() { XmlDocument doc = TCsv2Xml.ParseCSVFile2Xml("../../demodata/partners/samplePartnerImport.csv", ";"); TVerificationResultCollection VerificationResult = null; PartnerImportExportTDS MainDS = TImportExportWebConnector.ImportFromCSVFile(TXMLParser.XmlToString(doc), "DMY", out VerificationResult); if (VerificationResult != null) { Assert.IsFalse(VerificationResult.HasCriticalErrors, "there was an error importing the csv file"); } // there should be 2 partners imported Assert.AreEqual(2, MainDS.PPartner.Rows.Count); }
public void TestCSVParser() { CreateTestDoc(); // load from csv, is it the same xml code? string filename = PathToTestData + "test.csv"; XmlDocument docFromCSV = TCsv2Xml.ParseCSVFile2Xml(filename); filename = PathToTestData + "test.xml"; StreamWriter sw = new StreamWriter(filename + ".new"); sw.Write(TXMLParser.XmlToString2(docFromCSV)); sw.Close(); Assert.AreEqual(true, TTextFile.SameContent(filename, filename + ".new"), "after importing from csv: the files should be the same: " + filename); System.IO.File.Delete(filename + ".new"); }
public void TestImportCSV2() { XmlDocument doc = TCsv2Xml.ParseCSVFile2Xml("../../demodata/partners/samplefilepartnerimport2.csv", ","); TVerificationResultCollection VerificationResult = null; Console.WriteLine(TXMLParser.XmlToString(doc)); PartnerImportExportTDS MainDS = TImportExportWebConnector.ImportFromCSVFile(TXMLParser.XmlToString(doc), "dMy", out VerificationResult); if (VerificationResult != null) { Assert.IsFalse(VerificationResult.HasCriticalErrors, "there was an error importing the csv file"); } foreach (PFamilyRow f in MainDS.PFamily.Rows) { Console.WriteLine("Family name : " + f.FamilyName); } // we are currently ignoring UNIT and ORGANISATION partners, only importing the 7 FAMILY partners. // due to the strange format of the file, each row is imported as a separate partner, ending up with 27 invalid partners Assert.AreEqual(7, MainDS.PPartner.Rows.Count); }
public void TestImportCSV_Dates_DMY() { XmlDocument doc = TCsv2Xml.ParseCSVFile2Xml("../../demodata/partners/samplePartnerImport_dates_dmy.csv", ";"); TVerificationResultCollection VerificationResult = null; PartnerImportExportTDS MainDS = TImportExportWebConnector.ImportFromCSVFile(TXMLParser.XmlToString(doc), "dmy", out VerificationResult); if (VerificationResult != null) { Assert.IsFalse(VerificationResult.HasCriticalErrors, "there was an error importing the csv file"); } // there should be 4 partners imported (2 x family + 2 x person) Assert.AreEqual(4, MainDS.PPartner.Rows.Count, "Wrong number of partners"); Assert.AreEqual(2, MainDS.PmPassportDetails.Rows.Count, "Wrong number of persons"); Assert.AreEqual(((PmPassportDetailsRow)MainDS.PmPassportDetails.Rows[0]).DateOfIssue, new DateTime(2016, 4, 22), "passport date of issue is wrong!"); // Now try with the wrong date format VerificationResult = null; MainDS = TImportExportWebConnector.ImportFromCSVFile(TXMLParser.XmlToString(doc), "mdy", out VerificationResult); Assert.IsNotNull(VerificationResult, "Expected to get errors"); int numErrors = 0; for (int i = 0; i < VerificationResult.Count; i++) { if (VerificationResult[i].ResultSeverity != TResultSeverity.Resv_Status) { numErrors++; } } Assert.AreEqual(5, numErrors, "Wrong number of errors"); }
/// <summary> /// generate the partners from a text file that was generated with Benerator /// </summary> /// <param name="AInputBeneratorFile"></param> public static void GenerateWorkers(string AInputBeneratorFile) { PartnerEditTDS MainDS = new PartnerEditTDS(); PersonnelTDS PersonnelDS = new PersonnelTDS(); // get a list of fields (all class UNIT, with unit type F) string sqlGetFieldPartnerKeys = "SELECT p_partner_key_n, p_unit_name_c FROM PUB_p_unit WHERE u_unit_type_code_c = 'F'"; DataTable FieldKeys = DBAccess.GDBAccessObj.SelectDT(sqlGetFieldPartnerKeys, "keys", null); // get a list of banks (all class BANK) string sqlGetBankPartnerKeys = "SELECT p_partner_key_n FROM PUB_p_bank"; DataTable BankKeys = DBAccess.GDBAccessObj.SelectDT(sqlGetBankPartnerKeys, "keys", null); // AlanP: May 2016 - We may no longer need the UTF8 because the method now automatically discovers the encoding even with no BOM XmlDocument doc = TCsv2Xml.ParseCSVFile2Xml(AInputBeneratorFile, ",", Encoding.UTF8); XmlNode RecordNode = doc.FirstChild.NextSibling.FirstChild; while (RecordNode != null) { string familySituation = TXMLParser.GetAttribute(RecordNode, "familySituation"); PFamilyRow familyRecord = null; if (familySituation == "singleMan") { familyRecord = GenerateFamilyRecord(RecordNode, "Male", MainDS); GeneratePersonRecord(RecordNode, familyRecord, "Male", MainDS); } else if (familySituation == "singleWoman") { familyRecord = GenerateFamilyRecord(RecordNode, "Female", MainDS); GeneratePersonRecord(RecordNode, familyRecord, "Female", MainDS); } else if (familySituation == "family") { familyRecord = GenerateFamilyRecord(RecordNode, "Male", MainDS); GeneratePersonRecord(RecordNode, familyRecord, "Male", MainDS); GeneratePersonRecord(RecordNode, familyRecord, "Female", MainDS); int AgeDifferenceSpouse = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "AgeDifferenceSpouse")); DataView FamilyView = new DataView(MainDS.PPerson); FamilyView.RowFilter = PPersonTable.GetFamilyKeyDBName() + " = " + familyRecord.PartnerKey.ToString(); FamilyView.Sort = PPersonTable.GetFamilyIdDBName(); PPersonRow HusbandPersonRow = (PPersonRow)FamilyView[0].Row; PPersonRow WifePersonRow = (PPersonRow)FamilyView[1].Row; WifePersonRow.DateOfBirth = WifePersonRow.DateOfBirth.Value.AddYears( AgeDifferenceSpouse - (WifePersonRow.DateOfBirth.Value.Year - HusbandPersonRow.DateOfBirth.Value.Year)); if (DateTime.Today.Year - WifePersonRow.DateOfBirth.Value.Year < 19) { WifePersonRow.DateOfBirth.Value.AddYears( 19 - (DateTime.Today.Year - WifePersonRow.DateOfBirth.Value.Year)); } int NumberOfChildren = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "numberOfChildren")); for (int countChild = 0; countChild < NumberOfChildren; countChild++) { DateTime DateOfBirthChild = Convert.ToDateTime( TXMLParser.GetAttribute(RecordNode, "Child" + (countChild + 1).ToString() + "DateOfBirth")); // mother must have been 19 when the child was born if (DateOfBirthChild.Year < WifePersonRow.DateOfBirth.Value.Year + 19) { continue; } GeneratePersonRecord(RecordNode, familyRecord, "Child" + (countChild + 1).ToString(), MainDS); } } GenerateAddressForFamily(RecordNode, familyRecord, MainDS); GenerateCommitmentRecord(RecordNode, familyRecord, MainDS, PersonnelDS, FieldKeys); GenerateBankDetails(RecordNode, familyRecord, MainDS, BankKeys); if (MainDS.PFamily.Rows.Count % 100 == 0) { TLogging.Log("created worker " + MainDS.PFamily.Rows.Count.ToString() + " " + familyRecord.FamilyName); } RecordNode = RecordNode.NextSibling; } MainDS.ThrowAwayAfterSubmitChanges = true; PartnerEditTDSAccess.SubmitChanges(MainDS); PersonnelDS.ThrowAwayAfterSubmitChanges = true; PersonnelTDSAccess.SubmitChanges(PersonnelDS); TLogging.Log("after saving workers"); }
/// <summary> /// generate the units /// </summary> /// <param name="AFieldCSVFile"></param> public static void GenerateFields(string AFieldCSVFile) { XmlDocument doc = TCsv2Xml.ParseCSVFile2Xml(AFieldCSVFile, ","); XmlNode RecordNode = doc.FirstChild.NextSibling.FirstChild; PartnerImportExportTDS PartnerDS = new PartnerImportExportTDS(); GLSetupTDS GLSetupDS = new GLSetupTDS(); PCountryTable CountryTable = null; TDBTransaction Transaction = new TDBTransaction(); DBAccess.ReadTransaction( ref Transaction, delegate { CountryTable = PCountryAccess.LoadAll(Transaction); }); while (RecordNode != null) { PUnitRow UnitRow = PartnerDS.PUnit.NewRowTyped(); long id = 100 + Convert.ToInt64(TXMLParser.GetAttribute(RecordNode, "id")); UnitRow.PartnerKey = id * 1000000; string CountryCode = TXMLParser.GetAttribute(RecordNode, "Name"); UnitRow.UnitName = ((PCountryRow)CountryTable.Rows.Find(CountryCode)).CountryName; UnitRow.UnitTypeCode = "F"; PartnerDS.PUnit.Rows.Add(UnitRow); PPartnerRow PartnerRow = PartnerDS.PPartner.NewRowTyped(); PartnerRow.PartnerKey = UnitRow.PartnerKey; PartnerRow.PartnerShortName = UnitRow.UnitName; PartnerRow.PartnerClass = MPartnerConstants.PARTNERCLASS_UNIT; PartnerRow.StatusCode = MPartnerConstants.PARTNERSTATUS_ACTIVE; PartnerDS.PPartner.Rows.Add(PartnerRow); // add empty location so that the partner can be found in the Partner Find screen PPartnerLocationRow PartnerLocationRow = PartnerDS.PPartnerLocation.NewRowTyped(); PartnerLocationRow.PartnerKey = UnitRow.PartnerKey; PartnerLocationRow.LocationKey = 0; PartnerLocationRow.SiteKey = 0; PartnerDS.PPartnerLocation.Rows.Add(PartnerLocationRow); // create unit hierarchy UmUnitStructureRow UnitStructureRow = PartnerDS.UmUnitStructure.NewRowTyped(); UnitStructureRow.ParentUnitKey = 1000000; UnitStructureRow.ChildUnitKey = UnitRow.PartnerKey; PartnerDS.UmUnitStructure.Rows.Add(UnitStructureRow); // create special type PPartnerTypeRow PartnerTypeRow = PartnerDS.PPartnerType.NewRowTyped(); PartnerTypeRow.PartnerKey = UnitRow.PartnerKey; PartnerTypeRow.TypeCode = MPartnerConstants.PARTNERTYPE_LEDGER; PartnerDS.PPartnerType.Rows.Add(PartnerTypeRow); // create cost centre ACostCentreRow CostCentreRow = GLSetupDS.ACostCentre.NewRowTyped(); CostCentreRow.LedgerNumber = FLedgerNumber; CostCentreRow.CostCentreCode = (id * 100).ToString("0000"); CostCentreRow.CostCentreName = UnitRow.UnitName; CostCentreRow.CostCentreToReportTo = MFinanceConstants.INTER_LEDGER_HEADING; CostCentreRow.CostCentreType = MFinanceConstants.FOREIGN_CC_TYPE; GLSetupDS.ACostCentre.Rows.Add(CostCentreRow); // create foreign ledger, cost centre link validledgernumber AValidLedgerNumberRow ValidLedgerNumber = GLSetupDS.AValidLedgerNumber.NewRowTyped(); ValidLedgerNumber.LedgerNumber = FLedgerNumber; ValidLedgerNumber.PartnerKey = UnitRow.PartnerKey; ValidLedgerNumber.CostCentreCode = CostCentreRow.CostCentreCode; ValidLedgerNumber.IltProcessingCentre = Convert.ToInt64(MFinanceConstants.ICH_COST_CENTRE) * 10000; GLSetupDS.AValidLedgerNumber.Rows.Add(ValidLedgerNumber); RecordNode = RecordNode.NextSibling; } PartnerImportExportTDSAccess.SubmitChanges(PartnerDS); GLSetupTDSAccess.SubmitChanges(GLSetupDS); }
/// <summary> /// generate the key ministries /// </summary> /// <param name="AKeyMinCSVFile"></param> public static void GenerateKeyMinistries(string AKeyMinCSVFile) { XmlDocument doc = TCsv2Xml.ParseCSVFile2Xml(AKeyMinCSVFile, ","); XmlNode RecordNode = doc.FirstChild.NextSibling.FirstChild; PartnerImportExportTDS PartnerDS = new PartnerImportExportTDS(); TDataBase db = DBAccess.Connect("GenerateKeyMinistries"); TDBTransaction Transaction = db.BeginTransaction(IsolationLevel.ReadCommitted); // get a list of fields (all class UNIT, with unit type F) string sqlGetFieldPartnerKeys = "SELECT p_partner_key_n, p_unit_name_c FROM PUB_p_unit WHERE u_unit_type_code_c = 'F'"; DataTable FieldKeys = db.SelectDT(sqlGetFieldPartnerKeys, "keys", Transaction); Transaction.Rollback(); Int32 NumberOfPartnerKeysReserved = 100; Int64 NextPartnerKey = TNewPartnerKey.ReservePartnerKeys(-1, ref NumberOfPartnerKeysReserved); while (RecordNode != null) { int FieldID = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "field")) % FieldKeys.Rows.Count; long FieldPartnerKey = Convert.ToInt64(FieldKeys.Rows[FieldID].ItemArray[0]); PUnitRow UnitRow = PartnerDS.PUnit.NewRowTyped(); if (NumberOfPartnerKeysReserved == 0) { NumberOfPartnerKeysReserved = 100; NextPartnerKey = TNewPartnerKey.ReservePartnerKeys(-1, ref NumberOfPartnerKeysReserved); } long UnitPartnerKey = NextPartnerKey; NextPartnerKey++; NumberOfPartnerKeysReserved--; UnitRow.PartnerKey = UnitPartnerKey; UnitRow.UnitName = FieldKeys.Rows[FieldID].ItemArray[1].ToString() + " - " + TXMLParser.GetAttribute(RecordNode, "KeyMinName"); UnitRow.UnitTypeCode = "KEY-MIN"; PartnerDS.PUnit.Rows.Add(UnitRow); PPartnerRow PartnerRow = PartnerDS.PPartner.NewRowTyped(); PartnerRow.PartnerKey = UnitRow.PartnerKey; PartnerRow.PartnerShortName = UnitRow.UnitName; PartnerRow.PartnerClass = MPartnerConstants.PARTNERCLASS_UNIT; PartnerRow.StatusCode = MPartnerConstants.PARTNERSTATUS_ACTIVE; PartnerDS.PPartner.Rows.Add(PartnerRow); // add empty location so that the partner can be found in the Partner Find screen PPartnerLocationRow PartnerLocationRow = PartnerDS.PPartnerLocation.NewRowTyped(); PartnerLocationRow.PartnerKey = UnitRow.PartnerKey; PartnerLocationRow.LocationKey = 0; PartnerLocationRow.SiteKey = 0; PartnerDS.PPartnerLocation.Rows.Add(PartnerLocationRow); // create unit hierarchy UmUnitStructureRow UnitStructureRow = PartnerDS.UmUnitStructure.NewRowTyped(); UnitStructureRow.ParentUnitKey = FieldPartnerKey; UnitStructureRow.ChildUnitKey = UnitRow.PartnerKey; PartnerDS.UmUnitStructure.Rows.Add(UnitStructureRow); RecordNode = RecordNode.NextSibling; } PartnerImportExportTDSAccess.SubmitChanges(PartnerDS); }
/// <summary> /// generate the partners from a text file that was generated with Benerator /// </summary> /// <param name="AInputBeneratorFile"></param> public static void GenerateOrganisationPartners(string AInputBeneratorFile) { PartnerEditTDS MainDS = new PartnerEditTDS(); AApSupplierTable supplierTable = new AApSupplierTable(); // AlanP: May 2016 - We may no longer need the UTF8 because the method now automatically discovers the encoding even with no BOM XmlDocument doc = TCsv2Xml.ParseCSVFile2Xml(AInputBeneratorFile, ",", Encoding.UTF8); XmlNode RecordNode = doc.FirstChild.NextSibling.FirstChild; Int32 NumberOfPartnerKeysReserved = 100; Int64 NextPartnerKey = TNewPartnerKey.ReservePartnerKeys(-1, ref NumberOfPartnerKeysReserved); while (RecordNode != null) { if (NumberOfPartnerKeysReserved == 0) { NumberOfPartnerKeysReserved = 100; NextPartnerKey = TNewPartnerKey.ReservePartnerKeys(-1, ref NumberOfPartnerKeysReserved); } long OrgPartnerKey = NextPartnerKey; NextPartnerKey++; NumberOfPartnerKeysReserved--; POrganisationRow organisationRecord = MainDS.POrganisation.NewRowTyped(); organisationRecord.PartnerKey = OrgPartnerKey; organisationRecord.OrganisationName = TXMLParser.GetAttribute(RecordNode, "OrganisationName"); MainDS.POrganisation.Rows.Add(organisationRecord); PPartnerRow PartnerRow = MainDS.PPartner.NewRowTyped(); PartnerRow.PartnerKey = organisationRecord.PartnerKey; PartnerRow.PartnerClass = MPartnerConstants.PARTNERCLASS_ORGANISATION; PartnerRow.StatusCode = MPartnerConstants.PARTNERSTATUS_ACTIVE; PartnerRow.PartnerShortName = organisationRecord.OrganisationName; PartnerRow.AddresseeTypeCode = MPartnerConstants.ADDRESSEETYPE_ORGANISATION; MainDS.PPartner.Rows.Add(PartnerRow); PLocationRow locationRow = MainDS.PLocation.NewRowTyped(); locationRow.SiteKey = 0; // DomainManager.GSiteKey; locationRow.LocationKey = (MainDS.PLocation.Count + 1) * -1; locationRow.StreetName = TXMLParser.GetAttribute(RecordNode, "Addr2"); locationRow.PostalCode = TXMLParser.GetAttribute(RecordNode, "PostCode"); locationRow.City = TXMLParser.GetAttribute(RecordNode, "City"); locationRow.County = TXMLParser.GetAttribute(RecordNode, "Province"); locationRow.CountryCode = TXMLParser.GetAttribute(RecordNode, "CountryCode"); MainDS.PLocation.Rows.Add(locationRow); PPartnerLocationRow organisationLocationRow = MainDS.PPartnerLocation.NewRowTyped(); organisationLocationRow.PartnerKey = PartnerRow.PartnerKey; organisationLocationRow.LocationKey = locationRow.LocationKey; organisationLocationRow.SiteKey = locationRow.SiteKey; organisationLocationRow.LocationType = MPartnerConstants.LOCATIONTYPE_BUSINESS; organisationLocationRow.SendMail = true; if (TXMLParser.GetAttribute(RecordNode, "IsSupplier") == "yes") { AApSupplierRow supplierRow = supplierTable.NewRowTyped(true); supplierRow.PartnerKey = organisationRecord.PartnerKey; supplierRow.CurrencyCode = TXMLParser.GetAttribute(RecordNode, "Currency"); if (supplierRow.CurrencyCode == "GBP") { supplierRow.DefaultBankAccount = "6210"; } else { supplierRow.DefaultBankAccount = "6200"; } supplierRow.DefaultApAccount = "9100"; supplierRow.DefaultCostCentre = (FLedgerNumber * 100).ToString("0000"); supplierRow.DefaultExpAccount = "4200"; supplierTable.Rows.Add(supplierRow); } MainDS.PPartnerLocation.Rows.Add(organisationLocationRow); RecordNode = RecordNode.NextSibling; } PartnerEditTDSAccess.SubmitChanges(MainDS); AApSupplierAccess.SubmitChanges(supplierTable, null); }
/// <summary> /// generate the invoices from a text file that was generated with Benerator /// </summary> /// <param name="AInputBeneratorFile"></param> /// <param name="AYear">eg. 2013</param> /// <param name="ASmallNumber">boolean to keep the size of the demo database down</param> public static void GenerateInvoices(string AInputBeneratorFile, int AYear, bool ASmallNumber) { MaxInvoicesPerYear = (ASmallNumber ? 25 : 200); XmlDocument doc = TCsv2Xml.ParseCSVFile2Xml(AInputBeneratorFile, ","); XmlNode RecordNode = doc.FirstChild.NextSibling.FirstChild; AccountsPayableTDS MainDS = new AccountsPayableTDS(); // get a list of potential suppliers string sqlGetSupplierPartnerKeys = "SELECT PUB_a_ap_supplier.p_partner_key_n, PUB_a_ap_supplier.a_currency_code_c " + "FROM PUB_p_organisation, PUB_a_ap_supplier WHERE PUB_a_ap_supplier.p_partner_key_n = PUB_p_organisation.p_partner_key_n"; DataTable SupplierKeys = DBAccess.GDBAccessObj.SelectDT(sqlGetSupplierPartnerKeys, "keys", null); // get a list of potential expense account codes string sqlGetExpenseAccountCodes = "SELECT a_account_code_c FROM PUB_a_account WHERE a_ledger_number_i = " + FLedgerNumber.ToString() + " AND a_account_type_c = 'Expense' AND a_account_active_flag_l = true AND a_posting_status_l = true"; DataTable AccountCodes = DBAccess.GDBAccessObj.SelectDT(sqlGetExpenseAccountCodes, "codes", null); while (RecordNode != null) { int supplierID = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "Supplier")) % SupplierKeys.Rows.Count; Int64 SupplierKey = Convert.ToInt64(SupplierKeys.Rows[supplierID].ItemArray[0]); String CurrencyCode = Convert.ToString(SupplierKeys.Rows[supplierID].ItemArray[1]); AApDocumentRow invoiceRow = MainDS.AApDocument.NewRowTyped(true); invoiceRow.LedgerNumber = FLedgerNumber; invoiceRow.ApDocumentId = (Int32)TSequenceWebConnector.GetNextSequence(TSequenceNames.seq_ap_document); invoiceRow.ApNumber = invoiceRow.ApDocumentId; invoiceRow.DocumentCode = invoiceRow.ApDocumentId.ToString(); invoiceRow.PartnerKey = SupplierKey; invoiceRow.Reference = "something"; invoiceRow.DateIssued = Convert.ToDateTime(TXMLParser.GetAttribute(RecordNode, "DateIssued")); invoiceRow.DateIssued = new DateTime(AYear, invoiceRow.DateIssued.Month, invoiceRow.DateIssued.Day); invoiceRow.DateEntered = invoiceRow.DateIssued; invoiceRow.TotalAmount = Convert.ToDecimal(TXMLParser.GetAttribute(RecordNode, "Amount")) / 100.0m; invoiceRow.CurrencyCode = CurrencyCode; invoiceRow.ApAccount = "9100"; invoiceRow.DocumentStatus = MFinanceConstants.AP_DOCUMENT_APPROVED; invoiceRow.LastDetailNumber = 1; // TODO reasonable exchange rate for non base currency. need to check currency of supplier invoiceRow.ExchangeRateToBase = 1.0m; MainDS.AApDocument.Rows.Add(invoiceRow); AApDocumentDetailRow detailRow = MainDS.AApDocumentDetail.NewRowTyped(true); detailRow.ApDocumentId = invoiceRow.ApDocumentId; detailRow.DetailNumber = 1; detailRow.LedgerNumber = invoiceRow.LedgerNumber; detailRow.CostCentreCode = (FLedgerNumber * 100).ToString("0000"); int accountID = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "ExpenseAccount")) % AccountCodes.Rows.Count; detailRow.AccountCode = AccountCodes.Rows[accountID].ItemArray[0].ToString(); detailRow.Amount = invoiceRow.TotalAmount; MainDS.AApDocumentDetail.Rows.Add(detailRow); if (MainDS.AApDocument.Rows.Count > MaxInvoicesPerYear) { break; } RecordNode = RecordNode.NextSibling; } AccountsPayableTDSAccess.SubmitChanges(MainDS); }
/// <summary> /// generate the partners from a text file that was generated with Benerator /// </summary> /// <param name="AInputBeneratorFile"></param> public static void GenerateFamilyPartners(string AInputBeneratorFile) { TDataBase db = DBAccess.Connect("GenerateFamilyPartners"); TDBTransaction Transaction = db.BeginTransaction(IsolationLevel.ReadCommitted); // get a list of banks (all class BANK) string sqlGetBankPartnerKeys = "SELECT p_partner_key_n FROM PUB_p_bank"; DataTable BankKeys = db.SelectDT(sqlGetBankPartnerKeys, "keys", Transaction); Transaction.Rollback(); PartnerEditTDS MainDS = new PartnerEditTDS(); // AlanP: May 2016 - We may no longer need the UTF8 because the method now automatically discovers the encoding even with no BOM XmlDocument doc = TCsv2Xml.ParseCSVFile2Xml(AInputBeneratorFile, ",", Encoding.UTF8); XmlNode RecordNode = doc.FirstChild.NextSibling.FirstChild; while (RecordNode != null) { string familySituation = TXMLParser.GetAttribute(RecordNode, "familySituation"); PFamilyRow familyRecord = null; PPartnerRow FamilyPartnerRow = null; if (familySituation == "singleMan") { familyRecord = SampleDataWorkers.GenerateFamilyRecord(RecordNode, "Male", MainDS); SampleDataWorkers.GeneratePersonRecord(RecordNode, familyRecord, "Male", MainDS); } else if (familySituation == "singleWoman") { familyRecord = SampleDataWorkers.GenerateFamilyRecord(RecordNode, "Female", MainDS); SampleDataWorkers.GeneratePersonRecord(RecordNode, familyRecord, "Female", MainDS); } else if (familySituation == "family") { familyRecord = SampleDataWorkers.GenerateFamilyRecord(RecordNode, "Male", MainDS); SampleDataWorkers.GeneratePersonRecord(RecordNode, familyRecord, "Male", MainDS); SampleDataWorkers.GeneratePersonRecord(RecordNode, familyRecord, "Female", MainDS); int NumberOfChildren = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "numberOfChildren")); if (NumberOfChildren > 0) { FamilyPartnerRow = (PPartnerRow)MainDS.PPartner.Rows.Find(familyRecord.PartnerKey); FamilyPartnerRow.AddresseeTypeCode = MPartnerConstants.ADDRESSEETYPE_FAMILY; } } FamilyPartnerRow = (PPartnerRow)MainDS.PPartner.Rows.Find(familyRecord.PartnerKey); FamilyPartnerRow.ReceiptEachGift = false; FamilyPartnerRow.ReceiptLetterFrequency = "Annual"; SampleDataWorkers.GenerateAddressForFamily(RecordNode, familyRecord, MainDS); SampleDataWorkers.GenerateBankDetails(RecordNode, familyRecord, MainDS, BankKeys); if (MainDS.PFamily.Rows.Count % 100 == 0) { TLogging.Log("created donor " + MainDS.PFamily.Rows.Count.ToString() + " " + familyRecord.FamilyName); } RecordNode = RecordNode.NextSibling; } // we do not save person records for normal family partners MainDS.PPerson.Clear(); // need to clear all partner records of the PERSON partners as well DataView PersonPartners = new DataView(MainDS.PPartner); PersonPartners.RowFilter = string.Format("{0} = '{1}'", PPartnerTable.GetPartnerClassDBName(), MPartnerConstants.PARTNERCLASS_PERSON); DataView PartnerLocations = new DataView(MainDS.PPartnerLocation); foreach (DataRowView rv in PersonPartners) { PPartnerRow partnerRow = (PPartnerRow)rv.Row; PartnerLocations.RowFilter = string.Format("{0} = {1}", PPartnerLocationTable.GetPartnerKeyDBName(), partnerRow.PartnerKey); PartnerLocations[0].Row.Delete(); partnerRow.Delete(); } MainDS.ThrowAwayAfterSubmitChanges = true; PartnerEditTDSAccess.SubmitChanges(MainDS); TLogging.Log("after saving donors"); }
/// <summary> /// generate the banks /// </summary> public static void GenerateBanks(string ABankCSVFile) { if (!File.Exists(ABankCSVFile)) { TLogging.Log("there is no bank file " + ABankCSVFile); return; } TLogging.Log("creating banks from file " + ABankCSVFile); XmlDocument doc = TCsv2Xml.ParseCSVFile2Xml(ABankCSVFile, ","); XmlNode RecordNode = doc.FirstChild.NextSibling.FirstChild; PartnerImportExportTDS PartnerDS = new PartnerImportExportTDS(); Int32 NumberOfPartnerKeysReserved = 100; Int64 NextPartnerKey = TNewPartnerKey.ReservePartnerKeys(-1, ref NumberOfPartnerKeysReserved); while (RecordNode != null) { PBankRow BankRow = PartnerDS.PBank.NewRowTyped(); if (NumberOfPartnerKeysReserved == 0) { NumberOfPartnerKeysReserved = 100; NextPartnerKey = TNewPartnerKey.ReservePartnerKeys(-1, ref NumberOfPartnerKeysReserved); } BankRow.PartnerKey = NextPartnerKey; NextPartnerKey++; NumberOfPartnerKeysReserved--; BankRow.BranchName = TXMLParser.GetAttribute(RecordNode, "Branchname"); BankRow.BranchCode = TXMLParser.GetAttribute(RecordNode, "Branchcode"); BankRow.Bic = TXMLParser.GetAttribute(RecordNode, "Bic"); PartnerDS.PBank.Rows.Add(BankRow); if (PartnerDS.PBank.Rows.Count % 1000 == 0) { TLogging.Log("created bank " + PartnerDS.PBank.Rows.Count.ToString() + " " + BankRow.BranchName); } PPartnerRow PartnerRow = PartnerDS.PPartner.NewRowTyped(); PartnerRow.PartnerKey = BankRow.PartnerKey; PartnerRow.PartnerShortName = BankRow.BranchName; PartnerRow.PartnerClass = MPartnerConstants.PARTNERCLASS_BANK; PartnerRow.StatusCode = MPartnerConstants.PARTNERSTATUS_ACTIVE; PartnerDS.PPartner.Rows.Add(PartnerRow); // add empty location so that the partner can be found in the Partner Find screen PPartnerLocationRow PartnerLocationRow = PartnerDS.PPartnerLocation.NewRowTyped(); PartnerLocationRow.PartnerKey = BankRow.PartnerKey; PartnerLocationRow.LocationKey = 0; PartnerLocationRow.SiteKey = 0; PartnerDS.PPartnerLocation.Rows.Add(PartnerLocationRow); RecordNode = RecordNode.NextSibling; } PartnerDS.ThrowAwayAfterSubmitChanges = true; PartnerImportExportTDSAccess.SubmitChanges(PartnerDS); TLogging.Log("after saving banks"); }