private bool ExportToExcelFile(string AFilename)
{
bool ExportOnlyLowestLevel = false;
// Add the parameter export_only_lowest_level to the Parameters if you don't want to export the
// higher levels. In some reports (Supporting Churches Report or Partner Contact Report) the csv
// output looks much nicer if it doesn't contain the unnecessary higher levels.
if (FParameterList.Exists("csv_export_only_lowest_level"))
{
ExportOnlyLowestLevel = FParameterList.Get("csv_export_only_lowest_level").ToBool();
}
XmlDocument doc = FResultList.WriteXmlDocument(FParameterList, ExportOnlyLowestLevel);
if (doc != null)
{
using (FileStream fs = new FileStream(AFilename, FileMode.Create))
{
if (TCsv2Xml.Xml2ExcelStream(doc, fs, false))
{
fs.Close();
}
}
return(true);
}
return(false);
}
public void TestCSVWriter()
{
Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
XmlDocument doc = CreateTestDoc();
// now test the csv file
string filename = PathToTestData + "test.csv";
TCsv2Xml.Xml2Csv(doc, filename + ".new");
Assert.AreEqual(true, TTextFile.SameContent(filename,
filename + ".new"), "the files should be the same: " + filename);
System.IO.File.Delete(filename + ".new");
}
/// <summary>
/// link the fields in the current ledger
/// </summary>
/// <param name="AFieldCSVFile"></param>
public static void GenerateFieldsFinanceOnly(string AFieldCSVFile)
{
XmlDocument doc = TCsv2Xml.ParseCSV2Xml(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);
}
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);
}
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);
}
/// <summary>
/// export to an Excel xlsx file
/// </summary>
public void ExportToExcelFile()
{
if (dlgSaveXLSXFile.FileName.Length == 0)
{
dlgSaveXLSXFile.FileName = this.ReportName + '.' + dlgSaveXLSXFile.DefaultExt;
}
if (dlgSaveXLSXFile.ShowDialog() == DialogResult.OK)
{
bool ExportOnlyLowestLevel = false;
// Add the parameter export_only_lowest_level to the Parameters if you don't want to export the
// higher levels. In some reports (Supporting Churches Report or Partner Contact Report) the csv
// output looks much nicer if it doesn't contain the unnecessary higher levels.
if (Parameters.Exists("csv_export_only_lowest_level"))
{
ExportOnlyLowestLevel = Parameters.Get("csv_export_only_lowest_level").ToBool();
}
XmlDocument doc = Results.WriteXmlDocument(Parameters, ExportOnlyLowestLevel);
if (doc != null)
{
using (FileStream fs = new FileStream(dlgSaveXLSXFile.FileName, FileMode.Create))
{
if (TCsv2Xml.Xml2ExcelStream(doc, fs, false))
{
fs.Close();
}
}
try
{
System.Diagnostics.Process excelProcess;
excelProcess = new System.Diagnostics.Process();
excelProcess.EnableRaisingEvents = false;
excelProcess.StartInfo.FileName = dlgSaveXLSXFile.FileName;
excelProcess.Start();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, Catalog.GetString("Failed to save file"), MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
}
}
}
public void TestCSVParser()
{
CreateTestDoc();
// load from csv, is it the same xml code?
string filename = PathToTestData + "test.csv";
XmlDocument docFromCSV = TCsv2Xml.ParseCSV2Xml(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 TestExcelImportStream()
{
string filename = PathToTestData + "testReading.xlsx";
// display error messages in english
Thread.CurrentThread.CurrentUICulture = new CultureInfo("en-GB");
TLogging.Log("reading " + filename);
using (FileStream fs = File.OpenRead(filename))
{
DataTable table = TCsv2Xml.ParseExcelWorkbook2DataTable(fs, true);
Assert.AreEqual("test1", table.Columns[0].ColumnName, "name of first column");
Assert.AreEqual("1", table.Rows[0][0].ToString(), "value of first row, first column");
Assert.AreEqual("2", table.Rows[0]["test2"].ToString(), "value of first row, second column");
Assert.AreEqual("vier", table.Rows[0][3].ToString(), "value of first row, forth column");
}
}
private static void SendEmailToAdmin(DataTable AErrors)
{
// Create excel output of the errors table
string excelfile = TAppSettingsManager.GetValue("DataChecks.TempPath") + "/errors.xlsx";
try
{
using (StreamWriter sw = new StreamWriter(excelfile))
{
using (MemoryStream m = new MemoryStream())
{
if (!TCsv2Xml.DataTable2ExcelStream(AErrors, m))
{
return;
}
m.WriteTo(sw.BaseStream);
m.Close();
sw.Close();
}
}
}
catch (Exception e)
{
TLogging.Log("Problems writing to file " + excelfile);
TLogging.Log(e.ToString());
return;
}
if (TAppSettingsManager.HasValue("DataChecks.Email.Recipient"))
{
new TSmtpSender().SendEmail("<" + TAppSettingsManager.GetValue("DataChecks.Email.Sender") + ">",
"OpenPetra DataCheck Robot",
TAppSettingsManager.GetValue("DataChecks.Email.Recipient"),
"Data Check",
"there are " + AErrors.Rows.Count.ToString() + " errors. Please see attachment!",
new string[] { excelfile });
}
else
{
TLogging.Log("there is no email sent because DataChecks.Email.Recipient is not defined in the config file");
}
}
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 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.ParseCSV2Xml(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");
}
/// <summary>
/// generate the applications
/// </summary>
public static void GenerateApplications(string AApplicationCSVFile)
{
XmlDocument doc = TCsv2Xml.ParseCSV2Xml(AApplicationCSVFile, ",", Encoding.UTF8);
XmlNode RecordNode = doc.FirstChild.NextSibling.FirstChild;
// create registration offices
GenerateRegistrationOffices("United Kingdom", 21000000, "GB");
GenerateRegistrationOffices("France", 22000000, "FR");
GenerateRegistrationOffices("Norway", 23000000, "NO");
GenerateRegistrationOffices("USA", 24000000, "US");
GenerateRegistrationOffices("Germany", 43000000, "DE");
PUnitTable unitTable = new PUnitTable();
// get a list of fields (all class UNIT, with unit type F)
string sqlGetFieldPartnerKeys = "SELECT * FROM PUB_p_unit WHERE u_unit_type_code_c = 'F'";
DBAccess.GDBAccessObj.SelectDT(unitTable, sqlGetFieldPartnerKeys, null, new OdbcParameter[0], 0, 0);
PcConferenceTable conferenceTable = PcConferenceAccess.LoadByPrimaryKey(1110198, null);
DateTime StartOfConference = conferenceTable[0].Start.Value;
int counterApplicants = 0;
while (RecordNode != null)
{
string JSONFormData = "{'RegistrationOffice':'#REGISTRATIONOFFICE_VALUE'," +
"'EventIdentifier':'#EVENTCODE','EventPartnerKey':'#EVENTPARTNERKEY'," +
"'RegistrationCountryCode':'#CULTURECODE','EventPartnerKey':'#EVENTPARTNERKEY'," +
"'Role':'#ROLE','FirstName':'#FIRSTNAME','LastName':'#LASTNAME'," +
"'Street':'#STREET','Postcode':'#POSTCODE','City':'#CITY','Country':'#COUNTRY_VALUE'," +
"'Phone':'#PHONE','Email':'#EMAIL','DateOfBirth':'#DATEOFBIRTH','ImageID':'#IMAGEID'," +
"'DateOfArrival':'#DATEOFARRIVAL','DateOfDeparture':'#DATEOFDEPARTURE'," +
"'Gender':'#GENDER','Vegetarian':'#VEGETARIAN','MedicalNeeds':'#MEDICALNEEDS','PaymentInfo':'#PAYMENTINFO'}";
StringBuilder json = new StringBuilder(JSONFormData);
Dictionary <string, string> values = new Dictionary <string, string>();
values.Add("EventCode", "SC001CNGRSS08");
values.Add("EventPartnerKey", conferenceTable[0].ConferenceKey.ToString());
string cultureCode = TXMLParser.GetAttribute(RecordNode, "RegistrationCountryCode");
cultureCode = cultureCode.Substring(0, 2) + "-" + cultureCode.Substring(2, 2);
values.Add("RegistrationCountryCode", cultureCode);
Int64 RegistrationOffice = 43000000;
string CountryIsoCode = "DE";
foreach (PUnitRow unitRow in unitTable.Rows)
{
if (cultureCode.EndsWith(unitRow.CountryCode))
{
RegistrationOffice = unitRow.PartnerKey;
CountryIsoCode = unitRow.CountryCode;
}
}
string role = TXMLParser.GetAttribute(RecordNode, "role");
int age = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "age"));
if (role == "TEEN")
{
// make the age fit
age = 12 + age % 6;
}
else if (role == "CHILD")
{
age = age % 11;
}
else
{
age = 18 + age % 40;
}
DateTime DateOfBirth = Convert.ToDateTime(
TXMLParser.GetAttribute(RecordNode, "DateOfBirth"));
int CurrentAge = StartOfConference.Year - DateOfBirth.Year;
if (DateOfBirth > StartOfConference.AddYears(-age))
{
CurrentAge--;
}
DateOfBirth = DateOfBirth.AddYears(CurrentAge - age);
if (age <= 11)
{
role = "TS-CHILD";
}
else if (age <= 15)
{
role = "TS-TEEN-A";
}
else if (age <= 17)
{
role = "TS-TEEN-O";
}
else
{
role = "TS-" + TXMLParser.GetAttribute(RecordNode, "role");
}
values.Add("RegistrationOffice_Value", RegistrationOffice.ToString());
values.Add("Role", role);
values.Add("FormsId", "\"");
values.Add("culturecode", cultureCode);
values.Add("FirstName", TXMLParser.GetAttribute(RecordNode, "FirstName"));
values.Add("LastName", TXMLParser.GetAttribute(RecordNode, "FamilyName"));
values.Add("Gender",
(TXMLParser.GetAttribute(RecordNode, "Gender") == "MALE" ? "Male" : "Female"));
values.Add("Vegetarian", "No");
string EmailAddress = TXMLParser.GetAttribute(RecordNode, "Email");
EmailAddress = EmailAddress.Substring(0, EmailAddress.IndexOf("@")) + "@sample.openpetra.org";
values.Add("Email", EmailAddress);
values.Add("Street", TXMLParser.GetAttribute(RecordNode, "Street"));
values.Add("Postcode", TXMLParser.GetAttribute(RecordNode, "PostCode"));
values.Add("City", TXMLParser.GetAttribute(RecordNode, "City"));
values.Add("Country_VALUE", CountryIsoCode);
values.Add("MedicalNeeds", "test with \"quote\" in text");
values.Add("PaymentInfo", "NONE");
Catalog.Init("en-GB", cultureCode);
values.Add("DateOfBirth", DateOfBirth.ToShortDateString()); // in the culture of the country code
values.Add("DateOfArrival", StartOfConference.ToShortDateString());
values.Add("DateOfDeparture", StartOfConference.AddDays(5).ToShortDateString());
values.Add("IMAGEID", "temp.jpg");
// copy photo to the data/photos directory
string photo = "469px-Ernest_Hemingway_1923_passport_photo.TIF.jpg";
if (TXMLParser.GetAttribute(RecordNode, "Gender") == "FEMALE")
{
photo = "388px-Droste-Hülshoff_2.jpg";
}
File.Copy(TAppSettingsManager.GetValue("Server.PathTemp") + "/../webserver/Samples/UploadDemo/" + photo,
TAppSettingsManager.GetValue("Server.PathTemp") + Path.DirectorySeparatorChar +
"temp.jpg", true);
foreach (string key in values.Keys)
{
string value = values[key].ToString().Trim();
json.Replace("#" + key.ToUpper(), value);
}
string result = TImportPartnerForm.DataImportFromForm("RegisterPerson", json.ToString(), false);
if (TLogging.DebugLevel >= 10)
{
TLogging.Log(result);
}
counterApplicants++;
if (counterApplicants % 100 == 0)
{
TLogging.Log("created " + counterApplicants.ToString() + " applicants");
}
RecordNode = RecordNode.NextSibling;
}
// TODO accept applications
// TODO give permissions to Demo user. create one user for each registration office
}
/// <summary>
/// export all the Data of the batches matching the parameters to an Excel file
/// </summary>
/// <param name="ALedgerNumber"></param>
/// <param name="ABatchNumberStart"></param>
/// <param name="ABatchNumberEnd"></param>
/// <param name="ABatchDateFrom"></param>
/// <param name="ABatchDateTo"></param>
/// <param name="ADateFormatString"></param>
/// <param name="ASummary"></param>
/// <param name="AUseBaseCurrency"></param>
/// <param name="ADateForSummary"></param>
/// <param name="ANumberFormat">American or European</param>
/// <param name="ATransactionsOnly"></param>
/// <param name="AExtraColumns"></param>
/// <param name="ARecipientNumber"></param>
/// <param name="AFieldNumber"></param>
/// <param name="AIncludeUnposted"></param>
/// <param name="AExportExcel">the export file as Excel file</param>
/// <param name="AVerificationMessages">Additional messages to display in a messagebox</param>
/// <returns>number of exported batches, -1 if cancelled, -2 if error</returns>
public Int32 ExportAllGiftBatchData(
Int32 ALedgerNumber,
Int32 ABatchNumberStart,
Int32 ABatchNumberEnd,
DateTime?ABatchDateFrom,
DateTime?ABatchDateTo,
string ADateFormatString,
bool ASummary,
bool AUseBaseCurrency,
DateTime?ADateForSummary,
string ANumberFormat,
bool ATransactionsOnly,
bool AExtraColumns,
Int64 ARecipientNumber,
Int64 AFieldNumber,
bool AIncludeUnposted,
out String AExportExcel,
out TVerificationResultCollection AVerificationMessages)
{
int ReturnGiftBatchCount = 0;
AExportExcel = string.Empty;
FStringWriter = new StringWriter();
FMainDS = new GiftBatchTDS();
FDelimiter = ",";
FLedgerNumber = ALedgerNumber;
FDateFormatString = ADateFormatString;
Boolean Summary = ASummary;
FUseBaseCurrency = AUseBaseCurrency;
FDateForSummary = ADateForSummary;
FCultureInfo = new CultureInfo(ANumberFormat.Equals("American") ? "en-US" : "de-DE");
FTransactionsOnly = ATransactionsOnly;
FExtraColumns = AExtraColumns;
String RecipientFilter = (ARecipientNumber != 0) ? " AND PUB_a_gift_detail.p_recipient_key_n = " + ARecipientNumber.ToString() : "";
String FieldFilter = (AFieldNumber != 0) ? " AND PUB_a_gift_detail.a_recipient_ledger_number_n = " + AFieldNumber.ToString() : "";
String StatusFilter =
(AIncludeUnposted) ? " AND (PUB_a_gift_batch.a_batch_status_c = 'Posted' OR PUB_a_gift_batch.a_batch_status_c = 'Unposted')"
: " AND PUB_a_gift_batch.a_batch_status_c = 'Posted'";
TDataBase db = DBAccess.Connect("ExportAllGiftBatchData");
try
{
db.ReadTransaction(
ref FTransaction,
delegate
{
try
{
myStringHelper.CurrencyFormatTable = db.SelectDT("SELECT * FROM PUB_a_currency", "a_currency", FTransaction);
ALedgerAccess.LoadByPrimaryKey(FMainDS, FLedgerNumber, FTransaction);
String BatchRangeFilter = (ABatchNumberStart > -1) ?
" AND (PUB_a_gift_batch.a_batch_number_i >= " + ABatchNumberStart.ToString() +
" AND PUB_a_gift_batch.a_batch_number_i <= " + ABatchNumberEnd.ToString() +
")" : "";
// If I've specified a BatchRange, I can't also have a DateRange:
String DateRangeFilter = (BatchRangeFilter == "") ?
" AND (PUB_a_gift_batch.a_gl_effective_date_d >= '" +
ABatchDateFrom.Value.ToString("yyyy-MM-dd") +
"' AND PUB_a_gift_batch.a_gl_effective_date_d <= '" +
ABatchDateTo.Value.ToString("yyyy-MM-dd") +
"')" : "";
string StatementCore =
" FROM PUB_a_gift_batch, PUB_a_gift, PUB_a_gift_detail" +
" WHERE PUB_a_gift_batch.a_ledger_number_i = " + FLedgerNumber +
RecipientFilter +
FieldFilter +
DateRangeFilter +
BatchRangeFilter +
StatusFilter +
" AND PUB_a_gift.a_ledger_number_i = PUB_a_gift_batch.a_ledger_number_i" +
" AND PUB_a_gift.a_batch_number_i = PUB_a_gift_batch.a_batch_number_i" +
" AND PUB_a_gift_detail.a_ledger_number_i = PUB_a_gift_batch.a_ledger_number_i" +
" AND PUB_a_gift_detail.a_batch_number_i = PUB_a_gift_batch.a_batch_number_i" +
" AND PUB_a_gift_detail.a_gift_transaction_number_i = PUB_a_gift.a_gift_transaction_number_i";
string sqlStatement = "SELECT DISTINCT PUB_a_gift_batch.* " +
StatementCore +
" ORDER BY " + AGiftBatchTable.GetBatchNumberDBName();
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("Retrieving gift batch records"),
5);
if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true)
{
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
throw new ApplicationException(Catalog.GetString("Export of Batches was cancelled by user"));
}
db.Select(FMainDS,
sqlStatement,
FMainDS.AGiftBatch.TableName,
FTransaction
);
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("Retrieving gift records"),
10);
if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true)
{
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
throw new ApplicationException(Catalog.GetString("Export of Batches was cancelled by user"));
}
sqlStatement = "SELECT DISTINCT PUB_a_gift.* " +
StatementCore +
" ORDER BY " + AGiftBatchTable.GetBatchNumberDBName() +
", " +
AGiftTable.GetGiftTransactionNumberDBName();
db.Select(FMainDS,
sqlStatement,
FMainDS.AGift.TableName,
FTransaction);
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("Retrieving gift detail records"),
15);
if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true)
{
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
throw new ApplicationException(Catalog.GetString("Export of Batches was cancelled by user"));
}
sqlStatement = "SELECT DISTINCT PUB_a_gift_detail.* " + StatementCore;
db.Select(FMainDS,
sqlStatement,
FMainDS.AGiftDetail.TableName,
FTransaction);
}
catch (Exception ex)
{
TLogging.LogException(ex, Utilities.GetMethodSignature());
throw;
}
});
TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(),
Catalog.GetString("Exporting Gift Batches"), 100);
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("Retrieving records"),
5);
string BaseCurrency = FMainDS.ALedger[0].BaseCurrency;
FCurrencyCode = BaseCurrency; // Depending on FUseBaseCurrency, this will be overwritten for each gift.
SortedDictionary <String, AGiftSummaryRow> sdSummary = new SortedDictionary <String, AGiftSummaryRow>();
UInt32 counter = 0;
// TProgressTracker Variables
UInt32 GiftCounter = 0;
AGiftSummaryRow giftSummary = null;
FMainDS.AGiftDetail.DefaultView.Sort =
AGiftDetailTable.GetLedgerNumberDBName() + "," +
AGiftDetailTable.GetBatchNumberDBName() + "," +
AGiftDetailTable.GetGiftTransactionNumberDBName();
foreach (AGiftBatchRow giftBatch in FMainDS.AGiftBatch.Rows)
{
if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true)
{
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
throw new ApplicationException(Catalog.GetString("Export of Batches was cancelled by user"));
}
ReturnGiftBatchCount++;
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
string.Format(Catalog.GetString("Batch {0}"), giftBatch.BatchNumber),
20);
GiftCounter = 0;
if (!FTransactionsOnly & !Summary)
{
WriteGiftBatchLine(giftBatch);
}
foreach (AGiftRow gift in FMainDS.AGift.Rows)
{
if (gift.BatchNumber.Equals(giftBatch.BatchNumber) && gift.LedgerNumber.Equals(giftBatch.LedgerNumber))
{
if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true)
{
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
throw new ApplicationException(Catalog.GetString("Export of Batches was cancelled by user"));
}
// Update progress tracker every 25 records
if (++GiftCounter % 25 == 0)
{
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
string.Format(Catalog.GetString("Batch {0} - Exporting gifts"), giftBatch.BatchNumber),
(GiftCounter / 25 + 4) * 5 > 90 ? 90 : (GiftCounter / 25 + 4) * 5);
}
DataRowView[] selectedRowViews = FMainDS.AGiftDetail.DefaultView.FindRows(
new object[] { gift.LedgerNumber, gift.BatchNumber, gift.GiftTransactionNumber });
foreach (DataRowView rv in selectedRowViews)
{
AGiftDetailRow giftDetail = (AGiftDetailRow)rv.Row;
if (Summary)
{
FCurrencyCode = FUseBaseCurrency ? BaseCurrency : giftBatch.CurrencyCode;
decimal mapExchangeRateToBase = FUseBaseCurrency ? 1 : giftBatch.ExchangeRateToBase;
counter++;
String DictionaryKey = FCurrencyCode + ";" + giftBatch.BankCostCentre + ";" + giftBatch.BankAccountCode + ";" +
giftDetail.RecipientKey + ";" + giftDetail.MotivationGroupCode + ";" +
giftDetail.MotivationDetailCode;
if (sdSummary.TryGetValue(DictionaryKey, out giftSummary))
{
giftSummary.GiftTransactionAmount += giftDetail.GiftTransactionAmount;
giftSummary.GiftAmount += giftDetail.GiftAmount;
}
else
{
giftSummary = new AGiftSummaryRow();
/*
* summary_data.a_transaction_currency_c = lv_stored_currency_c
* summary_data.a_bank_cost_centre_c = a_gift_batch.a_bank_cost_centre_c
* summary_data.a_bank_account_code_c = a_gift_batch.a_bank_account_code_c
* summary_data.a_recipient_key_n = a_gift_detail.p_recipient_key_n
* summary_data.a_motivation_group_code_c = a_gift_detail.a_motivation_group_code_c
* summary_data.a_motivation_detail_code_c = a_gift_detail.a_motivation_detail_code_c
* summary_data.a_exchange_rate_to_base_n = lv_exchange_rate_n
* summary_data.a_gift_type_c = a_gift_batch.a_gift_type_c */
giftSummary.CurrencyCode = FCurrencyCode;
giftSummary.BankCostCentre = giftBatch.BankCostCentre;
giftSummary.BankAccountCode = giftBatch.BankAccountCode;
giftSummary.RecipientKey = giftDetail.RecipientKey;
giftSummary.MotivationGroupCode = giftDetail.MotivationGroupCode;
giftSummary.MotivationDetailCode = giftDetail.MotivationDetailCode;
giftSummary.GiftTransactionAmount = giftDetail.GiftTransactionAmount;
giftSummary.GiftAmount = giftDetail.GiftAmount;
sdSummary.Add(DictionaryKey, giftSummary);
}
//overwrite always because we want to have the last
giftSummary.ExchangeRateToBase = mapExchangeRateToBase;
}
else // not summary
{
WriteGiftLine(gift, giftDetail);
}
}
}
}
}
if (Summary)
{
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("Export Summary"),
95);
bool first = true;
foreach (KeyValuePair <string, AGiftSummaryRow> kvp in sdSummary)
{
if (!FTransactionsOnly && first)
{
WriteGiftBatchSummaryLine(kvp.Value);
first = false;
}
WriteGiftSummaryLine(kvp.Value);
}
}
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("Gift batch export successful"),
100);
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
}
catch (ApplicationException)
{
//Show cancel condition
ReturnGiftBatchCount = -1;
TProgressTracker.CancelJob(DomainManager.GClientID.ToString());
}
catch (Exception ex)
{
TLogging.Log(ex.ToString());
//Show error condition
ReturnGiftBatchCount = -2;
FMessages.Add(new TVerificationResult(
"Exporting Gift Batches Terminated Unexpectedly",
ex.Message,
"An unexpected error occurred during the export of gift batches",
string.Empty,
TResultSeverity.Resv_Critical,
Guid.Empty));
TProgressTracker.CancelJob(DomainManager.GClientID.ToString());
}
if (ReturnGiftBatchCount > 0)
{
MemoryStream mstream = new MemoryStream();
if (TCsv2Xml.CSV2ExcelStream(FStringWriter.ToString(), mstream, FDelimiter, "giftbatch"))
{
AExportExcel = Convert.ToBase64String(mstream.ToArray());
}
}
AVerificationMessages = FMessages;
return(ReturnGiftBatchCount);
} // ExportAllGiftBatchData
/// <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 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);
}
public static void Main(string[] args)
{
try
{
// establish connection to database
TCmdOpts settings = new TCmdOpts();
if (!settings.IsFlagSet("Server.ODBC_DSN"))
{
Console.WriteLine(
"sample call: " +
"ExportDataProgress.exe -Server.ODBC_DSN:Petra2_2sa -username:demo_sql -password:demo -sql:\"SELECT * from pub.a_account\" -output:test.xml");
Environment.Exit(-1);
}
new TLogging("debug.log");
TDataBase db = new TDataBase();
TDBType dbtype = TDBType.ProgressODBC;
if (settings.IsFlagSet("Server.RDBMSType"))
{
dbtype = CommonTypes.ParseDBType(settings.GetOptValue("Server.RDBMSType"));
}
if (dbtype != TDBType.ProgressODBC)
{
throw new Exception("at the moment only Progress ODBC db is supported");
}
db.EstablishDBConnection(dbtype,
settings.GetOptValue("Server.ODBC_DSN"),
"",
"",
settings.GetOptValue("username"),
settings.GetOptValue("password"),
"");
DBAccess.GDBAccessObj = db;
TLogging.DebugLevel = 10;
string sqlText = "";
if (!settings.IsFlagSet("sql"))
{
Console.WriteLine("Please enter sql and finish with semicolon: ");
while (!sqlText.Trim().EndsWith(";"))
{
sqlText += " " + Console.ReadLine();
}
sqlText = sqlText.Substring(0, sqlText.Length - 1);
}
else
{
sqlText = settings.GetOptValue("sql");
Console.WriteLine(sqlText);
}
TDBTransaction transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadUncommitted);
DataTable table = db.SelectDT(sqlText, "temp", transaction);
XmlDocument doc = TDataBase.DataTableToXml(table);
if (settings.IsFlagSet("output"))
{
if (settings.GetOptValue("output").EndsWith("yml"))
{
TYml2Xml.Xml2Yml(doc, settings.GetOptValue("output"));
}
else if (settings.GetOptValue("output").EndsWith("csv"))
{
TCsv2Xml.Xml2Csv(doc, settings.GetOptValue("output"));
}
else if (settings.GetOptValue("output").EndsWith("xml"))
{
StreamWriter sw = new StreamWriter(settings.GetOptValue("output"));
sw.Write(TXMLParser.XmlToString2(doc));
sw.Close();
}
}
else
{
TYml2Xml.Xml2Yml(doc, "temp.yml");
StreamReader sr = new StreamReader("temp.yml");
Console.WriteLine(sr.ReadToEnd());
sr.Close();
}
db.RollbackTransaction();
db.CloseDBConnection();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.WriteLine(e.StackTrace);
}
}
/// <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;
}
TDataBase db = DBAccess.Connect("GenerateOrganisationPartners");
TDBTransaction Transaction = new TDBTransaction();
bool SubmitOK = false;
db.WriteTransaction(
ref Transaction,
ref SubmitOK,
delegate
{
PartnerEditTDSAccess.SubmitChanges(MainDS, db);
AApSupplierAccess.SubmitChanges(supplierTable, Transaction);
SubmitOK = true;
});
db.CloseDBConnection();
}
/// <summary>
/// This stores the resultlist into a XmlDocument (to be saved as Excel file)
/// </summary>
/// <param name="AParameters"></param>
/// <param name="AExportOnlyLowestLevel">if true, only the lowest level of AParameters are exported (level with higest depth)
/// otherwise all levels in AParameter are exported</param>
/// <returns>the XmlDocument</returns>
public XmlDocument WriteXmlDocument(TParameterList AParameters, Boolean AExportOnlyLowestLevel = false)
{
List <string> lines = WriteCSVInternal(AParameters, ";", false, AExportOnlyLowestLevel);
return(TCsv2Xml.ParseCSV2Xml(lines, ";"));
}
private static void SendEmailForUser(TDataBase ADataBaseObj, string AUserId, DataTable AErrors)
{
TDBTransaction ReadTransaction = new TDBTransaction();
SUserRow userrow = null;
// get the email address of the user
ADataBaseObj.ReadTransaction(ref ReadTransaction,
delegate
{
userrow = SUserAccess.LoadByPrimaryKey(AUserId, ReadTransaction)[0];
});
string excelfile = TAppSettingsManager.GetValue("DataChecks.TempPath") + "/errors" + AUserId + ".xlsx";
DateTime Errors_SinceDate = DateTime.Today.AddDays(-1 * SENDREPORTFORDAYS_TOUSERS);
DataView v = new DataView(AErrors,
"(CreatedBy='" + AUserId + "' AND ModifiedBy IS NULL AND DateCreated > #" + Errors_SinceDate.ToString("MM/dd/yyyy") + "#) " +
"OR (ModifiedBy='" + AUserId + "' AND DateModified > #" + Errors_SinceDate.ToString("MM/dd/yyyy") + "#)",
string.Empty, DataViewRowState.CurrentRows);
try
{
using (StreamWriter sw = new StreamWriter(excelfile))
{
using (MemoryStream m = new MemoryStream())
{
if (!TCsv2Xml.DataTable2ExcelStream(v.ToTable(), m))
{
return;
}
m.WriteTo(sw.BaseStream);
m.Close();
sw.Close();
}
}
}
catch (Exception e)
{
TLogging.Log("Problems writing to file " + excelfile);
TLogging.Log(e.ToString());
return;
}
string recipientEmail = string.Empty;
if (!userrow.IsEmailAddressNull())
{
recipientEmail = userrow.EmailAddress;
}
else if (TAppSettingsManager.HasValue("DataChecks.Email.Recipient.UserDomain"))
{
recipientEmail = userrow.FirstName + "." + userrow.LastName + "@" + TAppSettingsManager.GetValue(
"DataChecks.Email.Recipient.UserDomain");
}
else if (TAppSettingsManager.HasValue("DataChecks.Email.Recipient"))
{
recipientEmail = TAppSettingsManager.GetValue("DataChecks.Email.Recipient");
}
if (recipientEmail.Length > 0)
{
new TSmtpSender().SendEmail("<" + TAppSettingsManager.GetValue("DataChecks.Email.Sender") + ">",
"OpenPetra DataCheck Robot",
recipientEmail,
"Data Check for " + AUserId,
"there are " + v.Count.ToString() + " errors. Please see attachment!",
new string[] { excelfile });
}
else
{
TLogging.Log("no email can be sent to " + AUserId);
}
}
/// <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();
XmlDocument doc = TCsv2Xml.ParseCSV2Xml(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);
}
public static bool GenerateHOSAFiles(int ALedgerNumber,
int APeriodNumber,
int AIchNumber,
string ACostCentre,
String ACurrencySelect,
string AFileName,
out TVerificationResultCollection AVerificationResult
)
{
bool Successful = false;
GLBatchTDS MainDS = new GLBatchTDS();
TVerificationResultCollection VerificationResult = new TVerificationResultCollection();
AVerificationResult = VerificationResult;
TDBTransaction DBTransaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref DBTransaction,
delegate
{
//Load tables needed: AccountingPeriod, Ledger, Account, Cost Centre, Transaction, Gift Batch, ICHStewardship
ALedgerAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, DBTransaction);
/* Retrieve info on the ledger. */
ALedgerRow LedgerRow = (ALedgerRow)MainDS.ALedger.Rows[0];
String Currency = (ACurrencySelect == MFinanceConstants.CURRENCY_BASE) ? LedgerRow.BaseCurrency : LedgerRow.IntlCurrency;
/* String StoreNumericFormat = "#" + CultureInfo.CurrentCulture.NumberFormat.NumberGroupSeparator + "##0";
*
* if (CultureInfo.CurrentCulture.NumberFormat.NumberDecimalDigits > 0)
* {
* string DecPls = new String('0', CultureInfo.CurrentCulture.NumberFormat.NumberDecimalDigits);
* StoreNumericFormat += CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator + DecPls;
* }
*/
AAccountingPeriodTable AccountingPeriodTable =
AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, APeriodNumber, DBTransaction);
AAccountingPeriodRow AccountingPeriodRow = (AAccountingPeriodRow)AccountingPeriodTable.Rows[0];
String MonthName = AccountingPeriodRow.AccountingPeriodDesc;
//Create table definitions
DataTable TableForExport = new DataTable();
TableForExport.Columns.Add("CostCentre", typeof(string));
TableForExport.Columns.Add("Account", typeof(string));
TableForExport.Columns.Add("LedgerMonth", typeof(string));
TableForExport.Columns.Add("ICHPeriod", typeof(string));
TableForExport.Columns.Add("Date", typeof(DateTime));
TableForExport.Columns.Add("IndividualDebitTotal", typeof(decimal));
TableForExport.Columns.Add("IndividualCreditTotal", typeof(decimal));
string TableForExportHeader = "/** Header **" + "," +
APeriodNumber.ToString() + "," +
TLedgerInfo.GetStandardCostCentre(ALedgerNumber) + "," +
ACostCentre + "," +
DateTime.Today.ToShortDateString() + "," +
Currency;
//See gi3200.p ln: 170
//Select any gift transactions to export
string strSql = TDataBase.ReadSqlFile("ICH.HOSAExportGifts.sql");
OdbcParameter parameter;
List <OdbcParameter> parameters = new List <OdbcParameter>();
parameter = new OdbcParameter("LedgerNumber", OdbcType.Int);
parameter.Value = ALedgerNumber;
parameters.Add(parameter);
parameter = new OdbcParameter("Year", OdbcType.Int);
parameter.Value = LedgerRow.CurrentFinancialYear;
parameters.Add(parameter);
parameter = new OdbcParameter("CostCentre", OdbcType.VarChar);
parameter.Value = ACostCentre;
parameters.Add(parameter);
DataTable TmpTable = DBAccess.GDBAccessObj.SelectDT(strSql, "table", DBTransaction, parameters.ToArray());
foreach (DataRow untypedTransRow in TmpTable.Rows)
{
string gLMAcctCode = untypedTransRow[3].ToString();
string gLMCostCCode = untypedTransRow[4].ToString();
string gLMAcctType = untypedTransRow[5].ToString();
if (gLMAcctType == MFinanceConstants.ACCOUNT_TYPE_INCOME) //a_account.a_account_type_c
{
DateTime PeriodStartDate = AccountingPeriodRow.PeriodStartDate;
DateTime PeriodEndDate = AccountingPeriodRow.PeriodEndDate;
/*RUN Export_gifts(INPUT pv_ledger_number_i...*/
//gi3200-1.i
ExportGifts(ALedgerNumber,
ACostCentre,
gLMAcctCode,
MonthName,
APeriodNumber,
PeriodStartDate,
PeriodEndDate,
ACurrencySelect,
AIchNumber,
TableForExport,
VerificationResult);
}
/* Then see if there are any GL transactions to export */
//gi3200.i ln:33
/*
* This scheme with ODBC parameters consistently causes an "input string is the wrong type" eror:
*
* strSql = TDataBase.ReadSqlFile("ICH.HOSAExportGLTrans.sql");
* OdbcParameter[] SqlParams = new OdbcParameter[] {
* new OdbcParameter("LedgerNumber", (Int32)ALedgerNumber),
* new OdbcParameter("Account", (String)gLMAcctCode),
* new OdbcParameter("CostCentre", (String)gLMCostCCode),
* new OdbcParameter("Narrative", (String)MFinanceConstants.NARRATIVE_YEAR_END_REALLOCATION),
* new OdbcParameter("ICHNumber", (Int32)AIchNumber),
* new OdbcParameter("ICHNumber2", (Int32)AIchNumber),
* new OdbcParameter("PeriodNumber", (Int32)APeriodNumber)
* };
* DataTable TmpTransTable = DBAccess.GDBAccessObj.SelectDT(strSql, "Transactions", DBTransaction, SqlParams);
*/
strSql = "SELECT Trans.a_ledger_number_i, Trans.a_batch_number_i, Trans.a_journal_number_i, Trans.a_transaction_number_i, " +
"Trans.a_account_code_c, Trans.a_cost_centre_code_c, Trans.a_transaction_date_d, Trans.a_transaction_amount_n, " +
"Trans.a_amount_in_base_currency_n, Trans.a_amount_in_intl_currency_n, Trans.a_ich_number_i, Trans.a_system_generated_l, "
+
"Trans.a_narrative_c, Trans.a_debit_credit_indicator_l FROM public.a_transaction AS Trans, public.a_journal AS Journal "
+
"WHERE Trans.a_ledger_number_i = Journal.a_ledger_number_i AND Trans.a_batch_number_i = Journal.a_batch_number_i " +
"AND Trans.a_journal_number_i = Journal.a_journal_number_i " +
String.Format(
"AND Trans.a_ledger_number_i = {0} AND Trans.a_account_code_c = '{1}' AND Trans.a_cost_centre_code_c = '{2}' " +
"AND Trans.a_transaction_status_l = true AND NOT (Trans.a_narrative_c LIKE '{3}%' AND Trans.a_system_generated_l = true) "
+
"AND ((Trans.a_ich_number_i + {4}) = Trans.a_ich_number_i OR Trans.a_ich_number_i = {4}) " +
"AND Journal.a_journal_period_i = {5};",
ALedgerNumber,
gLMAcctCode,
gLMCostCCode,
MFinanceConstants.NARRATIVE_YEAR_END_REALLOCATION,
AIchNumber,
APeriodNumber
);
DataTable TmpTransTable = DBAccess.GDBAccessObj.SelectDT(strSql, "Transactions", DBTransaction);
foreach (DataRow untypedTransactRow in TmpTransTable.Rows)
{
Decimal DebitTotal = 0;
Decimal CreditTotal = 0;
bool Debit = Convert.ToBoolean(untypedTransactRow[13]); //a_transaction.a_debit_credit_indicator_l
bool SystemGenerated = Convert.ToBoolean(untypedTransactRow[11]); //a_transaction.a_system_generated_l
//TODO: Calendar vs Financial Date Handling - Check if number of ledger periods needs to be used here and not 12 assumed
string Narrative = untypedTransactRow[12].ToString(); //a_transaction.a_narrative_c
DateTime TransactionDate = Convert.ToDateTime(untypedTransactRow[6]); //a_transaction.a_transaction_date_d
if (ACurrencySelect == MFinanceConstants.CURRENCY_BASE)
{
decimal AmountInBaseCurrency = Convert.ToDecimal(untypedTransactRow[8]); //a_transaction.a_amount_in_base_currency_n
/* find transaction amount and store as debit or credit */
if (Debit)
{
DebitTotal += AmountInBaseCurrency;
}
else
{
CreditTotal += AmountInBaseCurrency;
}
}
else
{
decimal AmountInIntlCurrency = Convert.ToDecimal(untypedTransactRow[9]); //a_transaction.a_amount_in_intl_currency_n
if (Debit)
{
DebitTotal += AmountInIntlCurrency;
}
else
{
CreditTotal += AmountInIntlCurrency;
}
}
TLogging.LogAtLevel(4, "HOSA-Narrative: " + Narrative);
//Check for specific narrative strings
bool IsNarrativeGBGiftBatch = false;
int LenNarrativeGBGiftBatch = MFinanceConstants.NARRATIVE_GB_GIFT_BATCH.Length;
bool IsNarrativeGiftsReceivedGiftBatch = false;
int LenNarrativeGiftsReceivedGiftBatch = MFinanceConstants.NARRATIVE_GIFTS_RECEIVED_GIFT_BATCH.Length;
if (Narrative.Length >= LenNarrativeGiftsReceivedGiftBatch)
{
IsNarrativeGiftsReceivedGiftBatch =
(Narrative.Substring(0,
LenNarrativeGiftsReceivedGiftBatch) == MFinanceConstants.NARRATIVE_GIFTS_RECEIVED_GIFT_BATCH);
}
if (Narrative.Length >= LenNarrativeGBGiftBatch)
{
IsNarrativeGBGiftBatch =
(Narrative.Substring(0, LenNarrativeGBGiftBatch) == MFinanceConstants.NARRATIVE_GB_GIFT_BATCH);
}
if ((gLMAcctType.ToUpper() != MFinanceConstants.ACCOUNT_TYPE_INCOME.ToUpper()) ||
!(SystemGenerated && (IsNarrativeGBGiftBatch || IsNarrativeGiftsReceivedGiftBatch)))
{
// Put transaction information
DataRow DR = (DataRow)TableForExport.NewRow();
DR[0] = gLMCostCCode;
DR[1] = ConvertAccount(gLMAcctCode);
DR[2] = ALedgerNumber.ToString() + MonthName + ":" + Narrative;
DR[3] = "ICH-" + APeriodNumber.ToString("00");
DR[4] = TransactionDate;
DR[5] = DebitTotal;
DR[6] = CreditTotal;
TableForExport.Rows.Add(DR);
}
}
}
TableForExport.AcceptChanges();
TLogging.LogAtLevel(4, "HOSA-TableForExport: " + TableForExport.Rows.Count.ToString());
//DataTables to XML to CSV
XmlDocument doc = TDataBase.DataTableToXml(TableForExport);
TCsv2Xml.Xml2Csv(doc, AFileName);
//Replace the default CSV header row with OM specific
ReplaceHeaderInFile(AFileName, TableForExportHeader, ref VerificationResult);
Successful = true;
}); // Get NewOrExisting AutoReadTransaction
return(Successful);
} // Generate HOSA Files
static void Main(string[] args)
{
string BugIDs = String.Empty;
new TAppSettingsManager(false);
if ((!TAppSettingsManager.HasValue("sf-username")) ||
(!TAppSettingsManager.HasValue("sf-pwd")) ||
(!TAppSettingsManager.HasValue("bugs-csv-file")) ||
(!TAppSettingsManager.HasValue("version-fixed-in-earlier-than"))
)
{
Console.WriteLine(
"call: MantisCloseResolvedBugs.exe -sf-username:pokorra -sf-pwd:xyz -bugs-csv-file:resolvedbugs.csv -version-fixed-in-earlier-than:\"Alpha 0.2.20\"");
return;
}
// Process CSV file: turn it into an XmlDocument for ease of use
string bugsCSVFile = TAppSettingsManager.GetValue("bugs-csv-file");
XmlDocument bugsXmlDoc = TCsv2Xml.ParseCSV2Xml(bugsCSVFile, ",");
XmlNode RecordNode = bugsXmlDoc.FirstChild.NextSibling.FirstChild;
// Extract all Bug ID's from the XmlDocument
while (RecordNode != null)
{
BugIDs += TXMLParser.GetAttribute(RecordNode, "Id") + ",";
RecordNode = RecordNode.NextSibling;
}
BugIDs = BugIDs.Substring(0, BugIDs.Length - 1); // remove last comma ','
// Start the processing in the web browser
string loginURL = TAppSettingsManager.GetValue("login-url", "http://sourceforge.net/account/login.php");
string mantisURL = TAppSettingsManager.GetValue("mantis-url", "https://sourceforge.net/apps/mantisbt/openpetraorg/");
IWebDriver driver = new FirefoxDriver();
try
{
LoginToSourceforge(driver, loginURL, TAppSettingsManager.GetValue("sf-username"), TAppSettingsManager.GetValue("sf-pwd"));
string[] bugids = BugIDs.Split(new char[] { ',' });
// Process each Bug
foreach (string bugid in bugids)
{
SetResolvedBugToClosed(
driver,
mantisURL + "bug_update_page.php?bug_id=" + bugid,
TAppSettingsManager.GetValue("version-fixed-in-earlier-than"));
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
StreamWriter sw = new StreamWriter("error.html");
sw.WriteLine(driver.PageSource.Replace("a0:", "").Replace(":a0", ""));
sw.Close();
Console.WriteLine("please check " + Path.GetFullPath("error.html"));
}
finally
{
driver.Quit();
}
}