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