private TSubmitChangesResult StoreManualCode(ref CorporateExchangeSetupTDS ASubmitChanges,
out TVerificationResultCollection AVerificationResult)
{
AVerificationResult = null;
int TransactionsChanged;
TSubmitChangesResult Result = TRemote.MFinance.Setup.WebConnectors.SaveCorporateExchangeSetupTDS(
ref ASubmitChanges, out TransactionsChanged, out AVerificationResult);
if ((Result == TSubmitChangesResult.scrOK) && (TransactionsChanged > 0) && (FPetraUtilsObject.GetCallerForm() != null))
{
MessageBox.Show(string.Format(Catalog.GetPluralString(
"{0} GL Transaction was automatically updated with the new corporate exchange rate.",
"{0} GL Transactions were automatically updated with the new corporate exchange rate.",
TransactionsChanged, true), TransactionsChanged),
Catalog.GetString("Save Corporate Exchange Rates"), MessageBoxButtons.OK);
}
return(Result);
}
public static TSubmitChangesResult SaveCorporateExchangeSetupTDS(ref CorporateExchangeSetupTDS AInspectDS,
out int ATransactionsChanged, out TVerificationResultCollection AVerificationResult)
{
AVerificationResult = new TVerificationResultCollection();
ATransactionsChanged = -1;
int TransactionsChanged = -1;
AInspectDS = AInspectDS.GetChangesTyped(true);
if (AInspectDS == null)
{
AVerificationResult.Add(new TVerificationResult(
Catalog.GetString("Save Corportate Exchange Rates"),
Catalog.GetString("No changes - nothing to do"),
TResultSeverity.Resv_Info));
return TSubmitChangesResult.scrNothingToBeSaved;
}
TDBTransaction Transaction = null;
bool SubmissionOK = false;
CorporateExchangeSetupTDS InspectDS = AInspectDS;
DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction(IsolationLevel.Serializable,
ref Transaction, ref SubmissionOK,
delegate
{
foreach (ACorporateExchangeRateRow Row in InspectDS.ACorporateExchangeRate.Rows)
{
if ((Row.RowState == DataRowState.Modified) || (Row.RowState == DataRowState.Added))
{
// should only be -1 if no exchange rates were modified or created
if (TransactionsChanged == -1)
{
TransactionsChanged = 0;
}
// update international amounts for all gl transaction using modified or new exchange rate
string Query = "UPDATE a_transaction SET a_amount_in_intl_currency_n = " +
"(a_amount_in_base_currency_n / " + Row.RateOfExchange.ToString(CultureInfo.InvariantCulture) + ")" +
" FROM a_ledger" +
" WHERE EXTRACT(MONTH FROM a_transaction.a_transaction_date_d) = " + Row.DateEffectiveFrom.Month +
" AND EXTRACT(YEAR FROM a_transaction.a_transaction_date_d) = " + Row.DateEffectiveFrom.Year +
" AND a_ledger.a_ledger_number_i = a_transaction.a_ledger_number_i" +
" AND a_ledger.a_base_currency_c = '" + Row.FromCurrencyCode + "'" +
" AND a_ledger.a_intl_currency_c = '" + Row.ToCurrencyCode + "'";
TransactionsChanged += DBAccess.GDBAccessObj.ExecuteNonQuery(Query, Transaction);
}
}
// save changes to exchange rates
ACorporateExchangeRateAccess.SubmitChanges(InspectDS.ACorporateExchangeRate, Transaction);
SubmissionOK = true;
});
TSubmitChangesResult SubmissionResult;
if (SubmissionOK)
{
SubmissionResult = TSubmitChangesResult.scrOK;
}
else
{
SubmissionResult = TSubmitChangesResult.scrError;
}
AInspectDS = InspectDS;
ATransactionsChanged = TransactionsChanged;
return SubmissionResult;
}
public static TSubmitChangesResult SaveCorporateExchangeSetupTDS(ref CorporateExchangeSetupTDS AInspectDS,
out int ATransactionsChanged, out TVerificationResultCollection AVerificationResult)
{
AVerificationResult = new TVerificationResultCollection();
ATransactionsChanged = -1;
int TransactionsChanged = -1;
AInspectDS = AInspectDS.GetChangesTyped(true);
if (AInspectDS == null)
{
AVerificationResult.Add(new TVerificationResult(
Catalog.GetString("Save Corportate Exchange Rates"),
Catalog.GetString("No changes - nothing to do"),
TResultSeverity.Resv_Info));
return(TSubmitChangesResult.scrNothingToBeSaved);
}
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("SaveCorporateExchangeSetupTDS");
bool SubmissionOK = true;
CorporateExchangeSetupTDS InspectDS = AInspectDS;
db.WriteTransaction(
ref Transaction, ref SubmissionOK,
delegate
{
foreach (ACorporateExchangeRateRow xchangeRateRow in InspectDS.ACorporateExchangeRate.Rows)
{
if ((xchangeRateRow.RowState == DataRowState.Modified) || (xchangeRateRow.RowState == DataRowState.Added))
{
// should only be -1 if no exchange rates were modified or created
if (TransactionsChanged == -1)
{
TransactionsChanged = 0;
}
String rateOfExchangeStr = xchangeRateRow.RateOfExchange.ToString(
CultureInfo.InvariantCulture);
// update international amounts for all gl transaction using modified or new exchange rate
string Query = "UPDATE a_transaction SET a_amount_in_intl_currency_n = " +
"ROUND (a_amount_in_base_currency_n / " + rateOfExchangeStr + ", 2)" +
" FROM a_ledger" +
" WHERE EXTRACT(MONTH FROM a_transaction.a_transaction_date_d) = " +
xchangeRateRow.DateEffectiveFrom.Month +
" AND EXTRACT(YEAR FROM a_transaction.a_transaction_date_d) = " + xchangeRateRow.DateEffectiveFrom.Year +
" AND a_ledger.a_ledger_number_i = a_transaction.a_ledger_number_i" +
" AND a_ledger.a_base_currency_c = '" + xchangeRateRow.FromCurrencyCode + "'" +
" AND a_ledger.a_intl_currency_c = '" + xchangeRateRow.ToCurrencyCode + "'";
TransactionsChanged += db.ExecuteNonQuery(Query, Transaction);
}
if (TransactionsChanged > 0)
{
//
// I also need to correct entries in GLM and GLMP after modifying these transactions:
DataTable ledgerTbl = db.SelectDT(
"SELECT * FROM a_ledger WHERE " +
" a_ledger.a_base_currency_c = '" + xchangeRateRow.FromCurrencyCode + "'" +
" AND a_ledger.a_intl_currency_c = '" + xchangeRateRow.ToCurrencyCode + "'",
"a_ledger", Transaction);
foreach (DataRow ledgerRow in ledgerTbl.Rows)
{
Int32 ledgerNumber = Convert.ToInt32(ledgerRow["a_ledger_number_i"]);
DataTable tempTbl = db.SelectDT(
"SELECT DISTINCT a_batch.a_batch_period_i, a_batch.a_batch_year_i FROM" +
" a_batch, a_transaction, a_ledger WHERE" +
" EXTRACT(MONTH FROM a_transaction.a_transaction_date_d) = " + xchangeRateRow.DateEffectiveFrom.Month +
" AND EXTRACT(YEAR FROM a_transaction.a_transaction_date_d) = " + xchangeRateRow.DateEffectiveFrom.Year +
" AND a_transaction.a_ledger_number_i =" + ledgerNumber +
" AND a_batch.a_batch_number_i = a_transaction.a_batch_number_i " +
" ORDER BY a_batch_period_i",
"temp", Transaction);
if ((tempTbl == null) || (tempTbl.Rows.Count == 0))
{
continue;
}
Int32 transactionPeriod = Convert.ToInt32(tempTbl.Rows[0]["a_batch_period_i"]);
Int32 transactionYear = Convert.ToInt32(tempTbl.Rows[0]["a_batch_year_i"]);
DataTable glmTbl = db.SelectDT(
"SELECT * from a_general_ledger_master" +
" WHERE a_ledger_number_i = " + ledgerNumber +
" AND a_year_i = " + transactionYear,
"temp", Transaction);
Boolean seemsToWorkOk = true;
// TLogging.Log("GLM correction: ");
Int32 glmSequence = 0;
String accountCode = "";
String costCentreCode = "";
String problem = "";
foreach (DataRow glmRow in glmTbl.Rows)
{
glmSequence = Convert.ToInt32(glmRow["a_glm_sequence_i"]);
accountCode = glmRow["a_account_code_c"].ToString();
costCentreCode = glmRow["a_cost_centre_code_c"].ToString();
tempTbl = db.SelectDT(
"SELECT sum(a_amount_in_intl_currency_n) AS debit_total FROM a_transaction WHERE " +
" EXTRACT(MONTH FROM a_transaction.a_transaction_date_d) = " + xchangeRateRow.DateEffectiveFrom.Month +
" AND EXTRACT(YEAR FROM a_transaction.a_transaction_date_d) = " + xchangeRateRow.DateEffectiveFrom.Year +
" AND a_account_code_c = '" + accountCode + "'" +
" AND a_cost_centre_code_c = '" + costCentreCode + "'" +
" AND a_debit_credit_indicator_l", "temp", Transaction);
Boolean hasDebitTransactions = (
tempTbl != null &&
tempTbl.Rows[0]["debit_total"].GetType() != typeof(System.DBNull)
);
Decimal debitTotal = 0;
if (hasDebitTransactions)
{
seemsToWorkOk = (tempTbl.Rows.Count == 1);
if (!seemsToWorkOk)
{
problem = "DebitTotal";
break;
}
debitTotal = Convert.ToDecimal(tempTbl.Rows[0]["debit_total"]);
}
tempTbl = db.SelectDT(
"SELECT sum(a_amount_in_intl_currency_n) AS credit_total FROM a_transaction WHERE " +
" EXTRACT(MONTH FROM a_transaction.a_transaction_date_d) = " + xchangeRateRow.DateEffectiveFrom.Month +
" AND EXTRACT(YEAR FROM a_transaction.a_transaction_date_d) = " + xchangeRateRow.DateEffectiveFrom.Year +
" AND a_account_code_c = '" + accountCode + "'" +
" AND a_cost_centre_code_c = '" + costCentreCode + "'" +
" AND NOT a_debit_credit_indicator_l", "temp", Transaction);
Boolean hasCreditTransactions = (
tempTbl != null &&
tempTbl.Rows[0]["credit_total"].GetType() != typeof(System.DBNull)
);
Decimal creditTotal = 0;
if (hasCreditTransactions)
{
seemsToWorkOk = (tempTbl.Rows.Count == 1);
if (!seemsToWorkOk)
{
problem = "CreditTotal";
break;
}
creditTotal = Convert.ToDecimal(tempTbl.Rows[0]["credit_total"]);
}
if (!hasDebitTransactions && !hasCreditTransactions)
{
// TLogging.Log("CostCentre " + costCentreCode + " Account " + accountCode + " - no transactions.");
continue;
}
Decimal lastMonthBalance = 0;
if (transactionPeriod > 1)
{
tempTbl = db.SelectDT(
"SELECT a_actual_intl_n as last_month_balance " +
" FROM a_general_ledger_master_period WHERE " +
" a_glm_sequence_i = " + glmSequence + " AND a_period_number_i = " + (transactionPeriod - 1),
"temp", Transaction);
seemsToWorkOk = (tempTbl.Rows.Count == 1);
if (!seemsToWorkOk)
{
problem = "lastMonthBalance";
break;
}
lastMonthBalance = Convert.ToDecimal(tempTbl.Rows[0]["last_month_balance"]);
}
else
{
lastMonthBalance = Convert.ToInt32(glmRow["a_start_balance_intl_n"]);
}
tempTbl = db.SelectDT(
"SELECT a_actual_intl_n as this_month_balance " +
" FROM a_general_ledger_master_period WHERE " +
" a_glm_sequence_i = " + glmSequence + " AND a_period_number_i = " + transactionPeriod,
"temp", Transaction);
seemsToWorkOk = (tempTbl.Rows.Count == 1);
if (!seemsToWorkOk)
{
problem = "thisMonthBalance";
break;
}
Decimal thisMonthBalance = Convert.ToDecimal(tempTbl.Rows[0]["this_month_balance"]);
tempTbl = db.SelectDT(
"SELECT a_debit_credit_indicator_l AS debit_indicator FROM " +
" a_account WHERE a_account_code_c = '" + accountCode + "'" +
" AND a_ledger_number_i = " + ledgerNumber,
"temp", Transaction);
seemsToWorkOk = (tempTbl.Rows.Count == 1);
if (!seemsToWorkOk)
{
problem = "debitCreditIndicator";
break;
}
Boolean debitCreditIndicator = Convert.ToBoolean(tempTbl.Rows[0]["debit_indicator"]);
Decimal newPeriodBalance = (debitCreditIndicator) ?
lastMonthBalance + debitTotal - creditTotal
:
lastMonthBalance - debitTotal + creditTotal;
Decimal discrepency = newPeriodBalance - thisMonthBalance;
db.ExecuteNonQuery(
"UPDATE a_general_ledger_master_period SET " +
" a_actual_intl_n = a_actual_intl_n + " + discrepency.ToString(CultureInfo.InvariantCulture) +
" WHERE a_glm_sequence_i = " + glmSequence +
" AND a_period_number_i >= " + transactionPeriod, Transaction);
db.ExecuteNonQuery(
"UPDATE a_general_ledger_master SET " +
" a_ytd_actual_intl_n = a_ytd_actual_intl_n + " + discrepency.ToString(CultureInfo.InvariantCulture) +
" WHERE a_glm_sequence_i = " + glmSequence, Transaction);
// TLogging.Log("Discrepency for CostCentre " + costCentreCode + " Account " + accountCode + " is " + discrepency);
} // foreach glmRow
if (!seemsToWorkOk)
{
TLogging.Log("SaveCorporateExchangeSetupTDS: unable to read " + problem + " for CostCentre " + costCentreCode +
" Account " + accountCode);
SubmissionOK = false;
}
} // foreach ledgerRow
} // if TransactionsChanged
}
// save changes to exchange rates
ACorporateExchangeRateAccess.SubmitChanges(InspectDS.ACorporateExchangeRate, Transaction);
}); // WriteTransaction
TSubmitChangesResult SubmissionResult;
if (SubmissionOK)
{
SubmissionResult = TSubmitChangesResult.scrOK;
}
else
{
SubmissionResult = TSubmitChangesResult.scrError;
}
AInspectDS = InspectDS;
ATransactionsChanged = TransactionsChanged;
return(SubmissionResult);
}
public static TSubmitChangesResult SaveCorporateExchangeSetupTDS(ref CorporateExchangeSetupTDS AInspectDS,
out int ATransactionsChanged, out TVerificationResultCollection AVerificationResult)
{
AVerificationResult = new TVerificationResultCollection();
ATransactionsChanged = -1;
int TransactionsChanged = -1;
AInspectDS = AInspectDS.GetChangesTyped(true);
if (AInspectDS == null)
{
AVerificationResult.Add(new TVerificationResult(
Catalog.GetString("Save Corportate Exchange Rates"),
Catalog.GetString("No changes - nothing to do"),
TResultSeverity.Resv_Info));
return(TSubmitChangesResult.scrNothingToBeSaved);
}
TDBTransaction Transaction = null;
bool SubmissionOK = false;
CorporateExchangeSetupTDS InspectDS = AInspectDS;
DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction(IsolationLevel.Serializable,
ref Transaction, ref SubmissionOK,
delegate
{
foreach (ACorporateExchangeRateRow Row in InspectDS.ACorporateExchangeRate.Rows)
{
if ((Row.RowState == DataRowState.Modified) || (Row.RowState == DataRowState.Added))
{
// should only be -1 if no exchange rates were modified or created
if (TransactionsChanged == -1)
{
TransactionsChanged = 0;
}
// update international amounts for all gl transaction using modified or new exchange rate
string Query = "UPDATE a_transaction SET a_amount_in_intl_currency_n = " +
"(a_amount_in_base_currency_n / " + Row.RateOfExchange.ToString(CultureInfo.InvariantCulture) + ")" +
" FROM a_ledger" +
" WHERE EXTRACT(MONTH FROM a_transaction.a_transaction_date_d) = " + Row.DateEffectiveFrom.Month +
" AND EXTRACT(YEAR FROM a_transaction.a_transaction_date_d) = " + Row.DateEffectiveFrom.Year +
" AND a_ledger.a_ledger_number_i = a_transaction.a_ledger_number_i" +
" AND a_ledger.a_base_currency_c = '" + Row.FromCurrencyCode + "'" +
" AND a_ledger.a_intl_currency_c = '" + Row.ToCurrencyCode + "'";
TransactionsChanged += DBAccess.GDBAccessObj.ExecuteNonQuery(Query, Transaction);
}
}
// save changes to exchange rates
ACorporateExchangeRateAccess.SubmitChanges(InspectDS.ACorporateExchangeRate, Transaction);
SubmissionOK = true;
});
TSubmitChangesResult SubmissionResult;
if (SubmissionOK)
{
SubmissionResult = TSubmitChangesResult.scrOK;
}
else
{
SubmissionResult = TSubmitChangesResult.scrError;
}
AInspectDS = InspectDS;
ATransactionsChanged = TransactionsChanged;
return(SubmissionResult);
}
private TSubmitChangesResult StoreManualCode(ref CorporateExchangeSetupTDS ASubmitChanges,
out TVerificationResultCollection AVerificationResult)
{
AVerificationResult = null;
int TransactionsChanged;
TSubmitChangesResult Result = TRemote.MFinance.Setup.WebConnectors.SaveCorporateExchangeSetupTDS(
ref ASubmitChanges, out TransactionsChanged, out AVerificationResult);
if ((Result == TSubmitChangesResult.scrOK) && (TransactionsChanged != -1))
{
MessageBox.Show(string.Format(Catalog.GetPluralString(
"{0} GL Transaction was automatically updated with the new corporate exchange rate.",
"{0} GL Transactions were automatically updated with the new corporate exchange rate.",
TransactionsChanged, true), TransactionsChanged),
Catalog.GetString("Save Corporate Exchange Rates"), MessageBoxButtons.OK);
}
return Result;
}
