public static bool CanDeleteCorporateExchangeRate(DateTime ADateEffectiveFrom, string AIntlCurrency, string ATransactionCurrency) { bool ReturnValue = true; TDBTransaction ReadTransaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("CanDeleteCorporateExchangeRate"); db.ReadTransaction( ref ReadTransaction, delegate { // get accounting period for when the exchange rate is effective (if it exists) string Query = "SELECT * FROM a_accounting_period" + " WHERE a_accounting_period.a_period_end_date_d >= '" + DataUtilities.DateToSQLString(ADateEffectiveFrom) + "'" + " AND a_accounting_period.a_period_start_date_d <= '" + DataUtilities.DateToSQLString(ADateEffectiveFrom) + "'"; AAccountingPeriodTable AccountingPeriodTable = new AAccountingPeriodTable(); db.SelectDT(AccountingPeriodTable, Query, ReadTransaction); // no accounting period if effective in a year other that the current year if ((AccountingPeriodTable == null) || (AccountingPeriodTable.Rows.Count == 0)) { return; } AAccountingPeriodRow AccountingPeriodRow = AccountingPeriodTable[0]; // search for batches for the found accounting period string Query2 = "SELECT CASE WHEN EXISTS (" + "SELECT * FROM a_batch, a_journal, a_ledger" + " WHERE a_batch.a_date_effective_d <= '" + DataUtilities.DateToSQLString( AccountingPeriodRow.PeriodEndDate) + "'" + " AND a_batch.a_date_effective_d >= '" + DataUtilities.DateToSQLString( AccountingPeriodRow.PeriodStartDate) + "'" + " AND a_journal.a_ledger_number_i = a_batch.a_ledger_number_i" + " AND a_journal.a_batch_number_i = a_batch.a_batch_number_i" + " AND a_ledger.a_ledger_number_i = a_batch.a_ledger_number_i" + " AND ((a_journal.a_transaction_currency_c = '" + ATransactionCurrency + "'" + " AND a_ledger.a_intl_currency_c = '" + AIntlCurrency + "')" + " OR (a_journal.a_transaction_currency_c = '" + AIntlCurrency + "'" + " AND a_ledger.a_intl_currency_c = '" + ATransactionCurrency + "'))" + ") THEN 'TRUE'" + " ELSE 'FALSE' END"; DataTable DT = db.SelectDT(Query2, "temp", ReadTransaction); // a batch has been found if ((DT != null) && (DT.Rows.Count > 0) && (DT.Rows[0][0].ToString() == "TRUE")) { ReturnValue = false; return; } }); return(ReturnValue); }
public void SpeedTestLoadIntoTypedTable() { TDataBase db = DBAccess.Connect("test"); TDBTransaction ReadTransaction = new TDBTransaction(); DateTime before = DateTime.Now; DateTime after = DateTime.Now; GiftBatchTDS ds = new GiftBatchTDS(); db.ReadTransaction( ref ReadTransaction, delegate { string sql = "SELECT PUB_a_gift_detail.*, false AS AlreadyMatched, PUB_a_gift_batch.a_batch_status_c AS BatchStatus " + "FROM PUB_a_gift_batch, PUB_a_gift_detail " + "WHERE 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"; before = DateTime.Now; DataTable untyped = db.SelectDT(sql, "test", ReadTransaction); after = DateTime.Now; TLogging.Log(String.Format("loading all {0} gift details into an untyped table took {1} milliseconds", untyped.Rows.Count, (after.Subtract(before)).TotalMilliseconds)); GiftBatchTDSAGiftDetailTable typed = new GiftBatchTDSAGiftDetailTable(); before = DateTime.Now; db.SelectDT(typed, sql, ReadTransaction, new OdbcParameter[0], 0, 0); after = DateTime.Now; TLogging.Log(String.Format("loading all {0} gift details into a typed table took {1} milliseconds", typed.Rows.Count, (after.Subtract(before)).TotalMilliseconds)); AMotivationDetailAccess.LoadAll(ds, ReadTransaction); before = DateTime.Now; db.Select(ds, sql, ds.AGiftDetail.TableName, ReadTransaction); after = DateTime.Now; }); TLogging.Log(String.Format("loading all {0} gift details into a typed dataset took {1} milliseconds", ds.AGiftDetail.Rows.Count, (after.Subtract(before)).TotalMilliseconds)); before = DateTime.Now; GiftBatchTDS ds2 = new GiftBatchTDS(); ds2.Merge(ds.AGiftDetail); after = DateTime.Now; TLogging.Log(String.Format("merging typed table into other dataset took {0} milliseconds", (after.Subtract(before)).TotalMilliseconds)); }
/// <summary> /// Load all GLMP rows for this Cost Centre in this period /// </summary> /// <returns></returns> public Boolean LoadByCostCentreAccountPeriod(String ACostCentreCode, String AAccountCode, Int32 AYear, Int32 APeriod) { Boolean LoadedOk = false; TDBTransaction transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("LoadByCostCentreAccountPeriod", FDataBase); FGLMpTable = new AGeneralLedgerMasterPeriodTable(); db.ReadTransaction(ref transaction, delegate { db.SelectDT( FGLMpTable, "SELECT a_general_ledger_master_period.* FROM" + " a_general_ledger_master_period, a_general_ledger_master" + " WHERE" + " a_general_ledger_master_period.a_glm_sequence_i=a_general_ledger_master.a_glm_sequence_i" + " AND a_general_ledger_master.a_ledger_number_i = " + FLedgerNumber + " AND a_general_ledger_master.a_year_i = " + AYear + " AND a_general_ledger_master.a_account_code_c = '" + AAccountCode + "'" + " AND a_general_ledger_master.a_cost_centre_code_c = '" + ACostCentreCode + "'" + " AND a_general_ledger_master_period.a_period_number_i=" + APeriod, transaction); LoadedOk = (FGLMpTable.Rows.Count > 0); }); // AutoReadTransaction if (FDataBase == null) { db.CloseDBConnection(); } FGLMpRow = (LoadedOk) ? FGLMpTable[0] : null; return(LoadedOk); }
public static bool TypeAheadMotivationDetail(Int32 ALedgerNumber, string ASearch, Int32 ALimit, out DataTable AResult) { TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("TypeAheadMotivationDetail"); DataTable result = new DataTable(); db.ReadTransaction( ref Transaction, delegate { string SqlStmt = TDataBase.ReadSqlFile("Finance.TypeAheadMotivationDetail.sql"); OdbcParameter[] parameters = new OdbcParameter[3]; parameters[0] = new OdbcParameter("LedgerNumber", OdbcType.Int); parameters[0].Value = ALedgerNumber; parameters[1] = new OdbcParameter("MotivationDetailCode", OdbcType.VarChar); parameters[1].Value = "%" + ASearch + "%"; parameters[2] = new OdbcParameter("DescDetail", OdbcType.VarChar); parameters[2].Value = "%" + ASearch + "%"; SqlStmt += " LIMIT " + ALimit.ToString(); result = db.SelectDT(SqlStmt, "Search", Transaction, parameters); }); db.CloseDBConnection(); AResult = result; return(result.Rows.Count > 0); }
private static string GetUserIDFromEmail(string AUserEmail) { TDataBase db = DBAccess.Connect("GetUserIDFromEmail"); TDBTransaction Transaction = new TDBTransaction(); string UserID = AUserEmail; db.ReadTransaction(ref Transaction, delegate { string sql = "SELECT s_user_id_c FROM PUB_s_user WHERE UPPER(s_email_address_c) = ?"; OdbcParameter[] parameters = new OdbcParameter[1]; parameters[0] = new OdbcParameter("EmailAddress", OdbcType.VarChar); parameters[0].Value = AUserEmail.ToUpper(); DataTable result = db.SelectDT(sql, "user", Transaction, parameters); if (result.Rows.Count == 1) { UserID = result.Rows[0][0].ToString(); } else { TLogging.Log("Login with E-Mail address failed for " + AUserEmail + ". " + "We found " + result.Rows.Count.ToString() + " matching rows for this address."); throw new Exception("multiple users are matching this email address"); } }); db.CloseDBConnection(); return(UserID); }
public static List <string> GetUniqueTypes( Int64 APartnerKey ) { TDBTransaction T = new TDBTransaction(); TDataBase DB = DBAccess.Connect("Get get unique data types"); List <OdbcParameter> SQLParameter = new List <OdbcParameter>(); List <string> Types = new List <string>(); DB.ReadTransaction(ref T, delegate { string sql = "SELECT DISTINCT `p_type_c` FROM `p_data_history` " + "WHERE `p_partner_key_n` = ?"; SQLParameter.Add(new OdbcParameter("PartnerKey", OdbcType.VarChar) { Value = APartnerKey.ToString() }); DataTable AllTypes = DB.SelectDT(sql, "UniqueTypes", T, SQLParameter.ToArray()); foreach (DataRow TypeRow in AllTypes.Rows) { Types.Add(TypeRow.Field <string>("p_type_c")); } }); return(Types); }
/// <summary> /// Direct access to the unposted gifts /// </summary> /// <param name="ALedgerNumber"></param> /// <param name="ADateEndOfPeriod"></param> public GetUnpostedGiftInfo(int ALedgerNumber, DateTime ADateEndOfPeriod) { OdbcParameter[] ParametersArray; ParametersArray = new OdbcParameter[3]; ParametersArray[0] = new OdbcParameter("", OdbcType.Int); ParametersArray[0].Value = ALedgerNumber; ParametersArray[1] = new OdbcParameter("", OdbcType.Date); ParametersArray[1].Value = ADateEndOfPeriod; ParametersArray[2] = new OdbcParameter("", OdbcType.VarChar); ParametersArray[2].Value = MFinanceConstants.BATCH_UNPOSTED; TDBTransaction transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("GetUnpostedGiftInfo"); db.ReadTransaction( ref transaction, delegate { string strSQL = "SELECT * FROM PUB_" + AGiftBatchTable.GetTableDBName() + " WHERE " + AGiftBatchTable.GetLedgerNumberDBName() + " = ?" + " AND " + AGiftBatchTable.GetGlEffectiveDateDBName() + " <= ?" + " AND " + AGiftBatchTable.GetBatchStatusDBName() + " = ? " + " ORDER BY " + AGiftBatchTable.GetBatchNumberDBName(); FDataTable = db.SelectDT( strSQL, AAccountingPeriodTable.GetTableDBName(), transaction, ParametersArray); }); db.CloseDBConnection(); }
} // Select Gift Recipients /// <summary> /// Find all the gifts for a worker, presenting the results in four year columns. /// NOTE - the user can select the field of the donor. /// /// All the DB work was previously done by the Select Gift Recipients function above. /// I only need to filter the table by recipientKey. /// </summary> /// <returns></returns> public static DataTable SelectGiftDonors(TParameterList AParameters, TResultList AResults) { Int64 recipientKey = AParameters.Get("RecipientKey").ToInt64(); // TotalGiftsPerRecipient must not be static. DataTable TotalGiftsPerRecipient = null; TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("SelectGiftDonors"); string SqlQuery = TSession.GetVariable("QueryFinanceReport_SelectGiftRecipients").ToString(); SqlQuery = SqlQuery.Replace("GROUP by ", "AND recipient.p_partner_key_n = " + recipientKey + " GROUP by "); db.ReadTransaction( ref Transaction, delegate { TotalGiftsPerRecipient = db.SelectDT(SqlQuery, "result", Transaction); }); TotalGiftsPerRecipient.DefaultView.RowFilter = "RecipientKey = " + recipientKey.ToString(); DataTable resultTable = TotalGiftsPerRecipient.DefaultView.ToTable(true, new String[] { "DonorKey", "DonorName", "DonorClass", "YearTotal0", "YearTotal1", "YearTotal2", "YearTotal3" }); return(resultTable); }
/// <summary> /// Any foreign account that has a non-zero opening balance should be marked /// for revaluation. /// </summary> private void NoteForexRevalRequired(Int32 ALedgerNumber, Int32 AYear, Int32 ABatchPeriod) { TDBTransaction transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("NoteForexRevalRequired", FDataBase); Boolean submissionOK = true; if (ABatchPeriod == FledgerInfo.NumberOfAccountingPeriods) { if (FledgerInfo.NumberFwdPostingPeriods == 0) { return; } else { // If we're closing the final period, SetNextPeriod() doesn't update FledgerInfo.CurrentPeriod. But we still want to // check the next period for foreign forward-posted amounts, so we have to increment the period locally here. ABatchPeriod++; } } db.WriteTransaction( ref transaction, ref submissionOK, delegate { string strSQL = "SELECT Account.a_account_code_c," + "SUM (GLMP.a_actual_foreign_n) AS Balance" + " FROM PUB_a_account Account, PUB_a_general_ledger_master GLM, PUB_a_general_ledger_master_period GLMP" + " WHERE Account.a_ledger_number_i=" + ALedgerNumber + " AND Account.a_foreign_currency_flag_l=true" + " AND GLM.a_ledger_number_i=" + ALedgerNumber + " AND GLM.a_account_code_c=Account.a_account_code_c" + " AND GLM.a_year_i= " + AYear + " AND GLMP.a_glm_sequence_i=GLM.a_glm_sequence_i" + " AND GLMP.a_period_number_i=" + ABatchPeriod + " GROUP BY Account.a_account_code_c"; DataTable Balance = db.SelectDT(strSQL, "Balance", transaction); foreach (DataRow Row in Balance.Rows) { if (Convert.ToDecimal(Row["Balance"]) != 0) { TLedgerInitFlag flag = new TLedgerInitFlag(ALedgerNumber, "", transaction.DataBaseObj); flag.SetFlagComponent(MFinanceConstants.LEDGER_INIT_FLAG_REVAL, Row["a_account_code_c"].ToString()); } } }); if (FDataBase == null) { db.CloseDBConnection(); } }
/// <summary> /// Returns the current sequence value for the given Sequence from the DB. /// </summary> /// <param name="ASequenceName">Name of the Sequence.</param> /// <param name="ATransaction">An instantiated Transaction in which the Query /// to the DB will be enlisted.</param> /// <param name="ADatabase">the database object that can be used for querying</param> /// <returns>Sequence Value.</returns> public System.Int64 GetCurrentSequenceValue(String ASequenceName, TDBTransaction ATransaction, TDataBase ADatabase) { DataTable table = ADatabase.SelectDT( "SELECT PUB." + ASequenceName + ".CURRVAL FROM PUB.\"_Sequence\" WHERE \"_Seq-Name\" = \"" + ASequenceName + "\"", "sequence", ATransaction); if (table.Rows.Count > 0) { return(Convert.ToInt64(table.Rows[0][0])); } return(-1); }
public static List <string> GetUniqueTypes( Int64 APartnerKey ) { TDBTransaction T = new TDBTransaction(); TDataBase DB = DBAccess.Connect("Get get unique data types"); List <OdbcParameter> SQLParameter = new List <OdbcParameter>(); List <string> Types = new List <string>(); DB.ReadTransaction(ref T, delegate { string sql = "SELECT DISTINCT `p_type_c` FROM `p_consent_history` " + "WHERE `p_partner_key_n` = ?"; SQLParameter.Add(new OdbcParameter("PartnerKey", OdbcType.BigInt) { Value = APartnerKey }); DataTable AllTypes = DB.SelectDT(sql, "UniqueTypes", T, SQLParameter.ToArray()); foreach (DataRow TypeRow in AllTypes.Rows) { Types.Add(TypeRow.Field <string>("p_type_c")); } // also get types for existing values of the address that don't have a consent yet if (!Types.Contains(MPartnerConstants.CONSENT_TYPE_ADDRESS)) { Types.Add(MPartnerConstants.CONSENT_TYPE_ADDRESS); } if (!Types.Contains(MPartnerConstants.CONSENT_TYPE_EMAIL)) { Types.Add(MPartnerConstants.CONSENT_TYPE_EMAIL); } if (!Types.Contains(MPartnerConstants.CONSENT_TYPE_LANDLINE)) { Types.Add(MPartnerConstants.CONSENT_TYPE_LANDLINE); } if (!Types.Contains(MPartnerConstants.CONSENT_TYPE_MOBILE)) { Types.Add(MPartnerConstants.CONSENT_TYPE_MOBILE); } }); return(Types); }
public static void UpdateUnpostedGiftsTaxDeductiblePct(Int64 ARecipientKey, decimal ANewPct, DateTime ADateFrom) { TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("UpdateUnpostedGiftsTaxDeductiblePct"); bool SubmissionOK = false; db.WriteTransaction( ref Transaction, ref SubmissionOK, delegate { string Query = "SELECT a_gift_detail.*" + " FROM a_gift_detail, a_gift_batch, a_gift" + " WHERE a_gift_detail.p_recipient_key_n = " + ARecipientKey + " AND a_gift_detail.a_tax_deductible_pct_n <> " + ANewPct + " AND a_gift_detail.a_modified_detail_l <> true" + " AND a_gift_detail.a_tax_deductible_l = true" + " AND a_gift_batch.a_ledger_number_i = a_gift_detail.a_ledger_number_i" + " AND a_gift_batch.a_batch_number_i = a_gift_detail.a_batch_number_i" + " AND a_gift_batch.a_batch_status_c = 'Unposted'" + " AND a_gift.a_ledger_number_i = a_gift_detail.a_ledger_number_i" + " AND a_gift.a_batch_number_i = a_gift_detail.a_batch_number_i" + " AND a_gift.a_gift_transaction_number_i = a_gift_detail.a_gift_transaction_number_i" + " AND a_gift.a_date_entered_d >= '" + ADateFrom.ToString("yyyy-MM-dd") + "'"; AGiftDetailTable Table = new AGiftDetailTable(); db.SelectDT(Table, Query, Transaction); // update fields for each row for (int i = 0; i < Table.Rows.Count; i++) { AGiftDetailRow Row = Table[i]; Row.TaxDeductiblePct = ANewPct; TaxDeductibility.UpdateTaxDeductibiltyAmounts(ref Row); } AGiftDetailAccess.SubmitChanges(Table, Transaction); SubmissionOK = true; }); }
private static void CheckModule(TDataBase ADataBaseObj, string AModule) { // get all sql files starting with module string[] sqlfiles = Directory.GetFiles(Path.GetFullPath(TAppSettingsManager.GetValue("SqlFiles.Path", ".")), AModule + "*.sql"); DataTable errors = new DataTable(AModule + "Errors"); foreach (string sqlfile in sqlfiles) { string sql = TDataBase.ReadSqlFile(Path.GetFileName(sqlfile)); // extend the sql to load the s_date_created_d, s_created_by_c, s_date_modified_d, s_modified_by_c // only for the first table in the FROM clause string firstTableAlias = sql.Substring(sql.ToUpper().IndexOf("FROM ") + "FROM ".Length); firstTableAlias = firstTableAlias.Substring(0, firstTableAlias.ToUpper().IndexOf("WHERE")); int indexOfAs = firstTableAlias.ToUpper().IndexOf(" AS "); if (indexOfAs > -1) { firstTableAlias = firstTableAlias.Substring(indexOfAs + " AS ".Length).Trim(); if (firstTableAlias.Contains(",")) { firstTableAlias = firstTableAlias.Substring(0, firstTableAlias.IndexOf(",")).Trim(); } } sql = sql.Replace("FROM ", ", " + firstTableAlias + ".s_date_created_d AS DateCreated, " + firstTableAlias + ".s_created_by_c AS CreatedBy, " + firstTableAlias + ".s_date_modified_d AS DateModified, " + firstTableAlias + ".s_modified_by_c AS ModifiedBy FROM "); errors.Merge(ADataBaseObj.SelectDT(sql, "temp", null)); } if (errors.Rows.Count > 0) { SendEmailToAdmin(errors); SendEmailsPerUser(ADataBaseObj, errors); } }
public static bool IsPartnerARecipient(Int64 APartnerKey, out DataTable AGiftTotals, decimal APct, DateTime ADateFrom) { DataTable Table = new DataTable(); TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("IsPartnerARecipient"); db.ReadTransaction( ref Transaction, delegate { string Query = "SELECT" + " a_gift_detail.a_ledger_number_i AS LedgerNumber," + " SUM(CASE WHEN a_gift_batch.a_batch_status_c = 'Unposted' " + " AND a_gift.a_date_entered_d >= '" + ADateFrom.ToString("yyyy-MM-dd") + "'" + " THEN 1 ELSE 0 END) AS Unposted," + " SUM(CASE WHEN a_gift_batch.a_batch_status_c = 'Posted' " + " AND a_gift_batch.a_gl_effective_date_d >= '" + ADateFrom.ToString("yyyy-MM-dd") + "'" + " THEN 1 ELSE 0 END) AS Posted" + " FROM a_gift_detail, a_gift_batch, a_gift" + " WHERE a_gift_detail.p_recipient_key_n = " + APartnerKey + " AND a_gift_detail.a_tax_deductible_pct_n <> " + APct + " AND a_gift_detail.a_modified_detail_l <> true" + " AND a_gift_detail.a_tax_deductible_l = true" + " AND a_gift_batch.a_ledger_number_i = a_gift_detail.a_ledger_number_i" + " AND a_gift_batch.a_batch_number_i = a_gift_detail.a_batch_number_i" + " AND a_gift_batch.a_ledger_number_i = a_gift_detail.a_ledger_number_i" + " AND a_gift.a_ledger_number_i = a_gift_detail.a_ledger_number_i" + " AND a_gift.a_batch_number_i = a_gift_detail.a_batch_number_i" + " AND a_gift.a_gift_transaction_number_i = a_gift_detail.a_gift_transaction_number_i" + " GROUP BY a_gift_detail.a_ledger_number_i"; Table = db.SelectDT(Query, "Gifts", Transaction); }); AGiftTotals = Table; return(Table != null && Table.Rows.Count > 0); }
public static PTypeTable GetChildrenStatusOptions() { TDBTransaction t = new TDBTransaction(); TDataBase db = DBAccess.Connect("FindChildrenStatusOptions"); string sql = "SELECT p_type_code_c, p_type_description_c " + "FROM PUB_p_type " + "WHERE p_valid_type_l = 1 AND p_category_code_c = 'SPONSORED_CHILD_STATUS'"; PTypeTable result = new PTypeTable(); db.ReadTransaction(ref t, delegate { db.SelectDT(result, sql, t); }); db.CloseDBConnection(); return(result); }
private int GetGlmSequenceFromDB(TDataBase databaseConnection, int pv_ledger_number_i, String pv_cost_centre_code_c, String pv_account_code_c, int pv_year_i) { int ReturnValue = -1; String strSql = "SELECT a_glm_sequence_i FROM PUB_a_general_ledger_master WHERE a_ledger_number_i = " + pv_ledger_number_i + " AND a_cost_centre_code_c = '" + pv_cost_centre_code_c + "' AND a_account_code_c = '" + pv_account_code_c + "' AND a_year_i = " + pv_year_i; DataTable tab = databaseConnection.SelectDT(strSql, "GetGlmSequenceFromDB_TempTable", databaseConnection.Transaction); if (tab.Rows.Count == 1) { ReturnValue = Convert.ToInt32(tab.Rows[0]["a_glm_sequence_i"]); } return(ReturnValue); }
public static DataConsentTDS LastKnownEntry( Int64 APartnerKey, string ADataType ) { TDBTransaction T = new TDBTransaction(); TDataBase DB = DBAccess.Connect("Get Last known entry"); DataConsentTDS Set = new DataConsentTDS(); List <OdbcParameter> SQLParameter = new List <OdbcParameter>(); DB.ReadTransaction(ref T, delegate { string sql = "SELECT " + "`p_data_history`.*, " + "GROUP_CONCAT(`p_data_history_permission`.`p_purpose_code_c` SEPARATOR ',') AS `AllowedPurposes` " + "FROM `p_data_history` " + "LEFT JOIN `p_data_history_permission` " + "ON `p_data_history`.`p_entry_id_i` = `p_data_history_permission`.`p_data_history_entry_i` " + "WHERE `p_data_history`.`p_partner_key_n` = ? " + "AND `p_data_history`.`p_type_c` = ? " + "GROUP BY `p_data_history`.`p_entry_id_i` " + "ORDER BY `p_data_history`.`p_entry_id_i` DESC " + "LIMIT 1"; SQLParameter.Add(new OdbcParameter("PartnerKey", OdbcType.VarChar) { Value = APartnerKey.ToString() }); SQLParameter.Add(new OdbcParameter("DataType", OdbcType.VarChar) { Value = ADataType }); // DB.SelectDT(Set, sql, T, SQLParameter.ToArray(), 0,0); DB.SelectDT(Set.PDataHistory, sql, T, SQLParameter.ToArray()); PConsentChannelAccess.LoadAll(Set, T); PPurposeAccess.LoadAll(Set, T); }); return(Set); }
public static DataConsentTDS GetHistory( Int64 APartnerKey, string ADataType ) { TDBTransaction T = new TDBTransaction(); TDataBase DB = DBAccess.Connect("Get data history for partner"); DataConsentTDS Set = new DataConsentTDS(); List <OdbcParameter> SQLParameter = new List <OdbcParameter>(); DB.ReadTransaction(ref T, delegate { // prepare for one huge cunk sql string sql = "" + "SELECT " + " `pdh`.*, " + " GROUP_CONCAT(`pdhp`.`p_purpose_code_c` SEPARATOR ',') AS `AllowedPurposes` " + "FROM `p_data_history` AS `pdh` " + "LEFT JOIN `p_data_history_permission` AS `pdhp` " + " ON `pdh`.`p_entry_id_i` = `pdhp`.`p_data_history_entry_i` " + "WHERE `pdh`.`p_partner_key_n` = ? " + " AND `pdh`.`p_type_c` = ? " + "GROUP BY `pdh`.`p_entry_id_i` " + "ORDER BY `pdh`.`p_entry_id_i` DESC"; SQLParameter.Add(new OdbcParameter("PartnerKey", OdbcType.BigInt) { Value = APartnerKey.ToString() }); SQLParameter.Add(new OdbcParameter("DataType", OdbcType.VarChar) { Value = ADataType }); Set.PDataHistory.Constraints.Clear(); //mmmm... DB.SelectDT(Set.PDataHistory, sql, T, SQLParameter.ToArray()); PConsentChannelAccess.LoadAll(Set, T); PPurposeAccess.LoadAll(Set, T); }); return(Set); }
/// <summary> /// The contructor gets the root information /// </summary> public GetBatchInfo(Int32 ALedgerNumber, Int32 AYear, Int32 ABatchPeriod) { TDBTransaction transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("GetBatchInfo"); db.ReadTransaction( ref transaction, delegate { string strSQL = "SELECT * FROM PUB_a_batch" + " WHERE a_ledger_number_i=" + ALedgerNumber + " AND a_batch_year_i=" + AYear + " AND a_batch_period_i=" + ABatchPeriod + " AND a_batch_status_c <> '" + MFinanceConstants.BATCH_POSTED + "'" + " AND a_batch_status_c <> '" + MFinanceConstants.BATCH_CANCELLED + "'" + " ORDER BY a_batch_number_i"; db.SelectDT(Fbatches, strSQL, transaction); }); db.CloseDBConnection(); }
public static SUserTable GetSponsorAdmins() { TDBTransaction t = new TDBTransaction(); TDataBase db = DBAccess.Connect("FindSponsorAdmins"); string sql = "SELECT u.s_user_id_c, u.s_first_name_c, u.s_last_name_c " + "FROM PUB_s_user_module_access_permission p " + "JOIN PUB_s_user u " + "ON u.s_user_id_c = p.s_user_id_c AND p.s_module_id_c = 'SPONSORADMIN'"; SUserTable result = new SUserTable(); db.ReadTransaction(ref t, delegate { db.SelectDT(result, sql, t); }); db.CloseDBConnection(); return(result); }
public static bool TypeAheadAccountCode(Int32 ALedgerNumber, string ASearch, bool APostingOnly, bool AExcludePosting, bool AActiveOnly, bool ABankAccountOnly, Int32 ALimit, out DataTable AResult) { DataTable result = new DataTable(); TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("TypeAheadAccountCode"); db.ReadTransaction( ref Transaction, delegate { string SqlStmt = TDataBase.ReadSqlFile("Finance.TypeAheadAccountCode.sql"); OdbcParameter[] parameters = new OdbcParameter[5]; parameters[0] = new OdbcParameter("LedgerNumber", OdbcType.Int); parameters[0].Value = ALedgerNumber; parameters[1] = new OdbcParameter("AccountCode", OdbcType.VarChar); parameters[1].Value = "%" + ASearch + "%"; parameters[2] = new OdbcParameter("ShortDesc", OdbcType.VarChar); parameters[2].Value = "%" + ASearch + "%"; parameters[3] = new OdbcParameter("LongDesc", OdbcType.VarChar); parameters[3].Value = "%" + ASearch + "%"; parameters[4] = new OdbcParameter("PostingOnly", OdbcType.TinyInt); parameters[4].Value = APostingOnly; SqlStmt += " LIMIT " + ALimit.ToString(); result = db.SelectDT(SqlStmt, "Search", Transaction, parameters); }); db.CloseDBConnection(); AResult = result; return(result.Rows.Count > 0); }
/// <summary> /// Get the name for this Ledger /// </summary> public string GetLedgerName() { #region Validate Arguments if (FLedgerNumber <= 0) { throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString( "Function:{0} - The Ledger number must be greater than 0!"), Utilities.GetMethodName(true)), FLedgerNumber); } #endregion Validate Arguments String ReturnValue = string.Empty; TDBTransaction ReadTransaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("GetLedgerName", FDataBase); db.ReadTransaction( ref ReadTransaction, delegate { String strSql = "SELECT p_partner_short_name_c FROM PUB_a_ledger, PUB_p_partner WHERE a_ledger_number_i=" + FLedgerNumber + " AND PUB_a_ledger.p_partner_key_n = PUB_p_partner.p_partner_key_n"; DataTable tab = db.SelectDT(strSql, "GetLedgerName_TempTable", ReadTransaction); if (tab.Rows.Count > 0) { ReturnValue = Convert.ToString(tab.Rows[0][PPartnerTable.GetPartnerShortNameDBName()]); //"p_partner_short_name_c" } }); if (FDataBase == null) { db.CloseDBConnection(); } return(ReturnValue); }
/// <summary> /// todoComment /// </summary> /// <param name="databaseConnection"></param> /// <param name="pv_ledger_number_i"></param> /// <param name="pv_account_code_c"></param> /// <param name="accountType">returns the type of account, ie. Income, Expense, Assets, Liab, Equity</param> /// <param name="postingAccount">returns true if the account is a posting account /// </param> /// <param name="debitCreditIndicator"></param> /// <returns></returns> private bool TAccountInfo(TDataBase databaseConnection, System.Int32 pv_ledger_number_i, String pv_account_code_c, out String accountType, out bool postingAccount, out bool debitCreditIndicator) { bool ReturnValue; string strSql; DataTable tab; ReturnValue = false; debitCreditIndicator = false; accountType = ""; List <OdbcParameter> parameters = new List <OdbcParameter>(); OdbcParameter param = new OdbcParameter("accountcode", OdbcType.VarChar); param.Value = pv_account_code_c; parameters.Add(param); postingAccount = false; strSql = "SELECT a_account_type_c, a_posting_status_l, a_debit_credit_indicator_l FROM PUB_a_account" + " WHERE a_ledger_number_i = " + pv_ledger_number_i.ToString() + " AND a_account_code_c = ?"; tab = databaseConnection.SelectDT(strSql, "AccountType", databaseConnection.Transaction, parameters.ToArray()); if (tab.Rows.Count > 0) { accountType = Convert.ToString(tab.Rows[0]["a_account_type_c"]); postingAccount = (Boolean)tab.Rows[0]["a_posting_status_l"]; debitCreditIndicator = (Boolean)tab.Rows[0]["a_debit_credit_indicator_l"]; ReturnValue = true; } return(ReturnValue); }
/// <summary> /// todoComment /// </summary> /// <param name="databaseConnection"></param> /// <param name="pv_ledger_number_i"></param> /// <param name="pv_account_code_c"></param> /// <param name="accountType">returns the type of account, ie. Income, Expense, Assets, Liab, Equity</param> /// <param name="postingAccount">returns true if the account is a posting account /// </param> /// <param name="debitCreditIndicator"></param> /// <returns></returns> private bool TAccountInfo(TDataBase databaseConnection, System.Int32 pv_ledger_number_i, String pv_account_code_c, out String accountType, out bool postingAccount, out bool debitCreditIndicator) { bool ReturnValue; string strSql; DataTable tab; ReturnValue = false; debitCreditIndicator = false; accountType = ""; List <OdbcParameter>parameters = new List <OdbcParameter>(); OdbcParameter param = new OdbcParameter("accountcode", OdbcType.VarChar); param.Value = pv_account_code_c; parameters.Add(param); postingAccount = false; strSql = "SELECT a_account_type_c, a_posting_status_l, a_debit_credit_indicator_l FROM PUB_a_account" + " WHERE a_ledger_number_i = " + pv_ledger_number_i.ToString() + " AND a_account_code_c = ?"; tab = databaseConnection.SelectDT(strSql, "AccountType", databaseConnection.Transaction, parameters.ToArray()); if (tab.Rows.Count > 0) { accountType = Convert.ToString(tab.Rows[0]["a_account_type_c"]); postingAccount = (Boolean)tab.Rows[0]["a_posting_status_l"]; debitCreditIndicator = (Boolean)tab.Rows[0]["a_debit_credit_indicator_l"]; ReturnValue = true; } return ReturnValue; }
/// <summary> /// Find recipient Partner Key and name for all partners who received gifts in the timeframe. /// NOTE - the user can select the PartnerType of the recipient. /// /// With only a little more load on the DB, I can get all the data that the report will need, /// and store it in a DataTable local to this class, so that when more detailed data is requested below, /// I don't need another DB query. /// </summary> /// <returns>RecipientKey, RecipientName</returns> public static DataTable SelectGiftRecipients(TParameterList AParameters, TResultList AResults) { DataTable TotalGiftsPerRecipient = null; Int32 LedgerNum = AParameters.Get("param_ledger_number_i").ToInt32(); Boolean onlySelectedTypes = AParameters.Get("param_type_selection").ToString() == "selected_types"; Boolean onlySelectedFields = AParameters.Get("param_field_selection").ToString() == "selected_fields"; Boolean fromExtract = AParameters.Get("param_recipient").ToString() == "Extract"; Boolean oneRecipient = AParameters.Get("param_recipient").ToString() == "One Recipient"; String period0Start = AParameters.Get("param_from_date_0").ToDate().ToString("yyyy-MM-dd"); String period0End = AParameters.Get("param_to_date_0").ToDate().ToString("yyyy-MM-dd"); String period1Start = AParameters.Get("param_from_date_1").ToDate().ToString("yyyy-MM-dd"); String period1End = AParameters.Get("param_to_date_1").ToDate().ToString("yyyy-MM-dd"); String period2Start = AParameters.Get("param_from_date_2").ToDate().ToString("yyyy-MM-dd"); String period2End = AParameters.Get("param_to_date_2").ToDate().ToString("yyyy-MM-dd"); String period3Start = AParameters.Get("param_from_date_3").ToDate().ToString("yyyy-MM-dd"); String period3End = AParameters.Get("param_to_date_3").ToDate().ToString("yyyy-MM-dd"); String amountFieldName = (AParameters.Get("param_currency").ToString() == "International") ? "detail.a_gift_amount_intl_n" : "detail.a_gift_amount_n"; string SqlQuery = "SELECT DISTINCT " + "gift.p_donor_key_n AS DonorKey, " + "donor.p_partner_short_name_c AS DonorName, donor.p_partner_class_c AS DonorClass, " + "recipient.p_partner_key_n AS RecipientKey, " + "recipient.p_partner_short_name_c AS RecipientName, " + "SUM(CASE WHEN gift.a_date_entered_d BETWEEN '" + period0Start + "' AND '" + period0End + "' " + "THEN " + amountFieldName + " ELSE 0 END )as YearTotal0, " + "SUM(CASE WHEN gift.a_date_entered_d BETWEEN '" + period1Start + "' AND '" + period1End + "' " + "THEN " + amountFieldName + " ELSE 0 END )as YearTotal1, " + "SUM(CASE WHEN gift.a_date_entered_d BETWEEN '" + period2Start + "' AND '" + period2End + "' " + "THEN " + amountFieldName + " ELSE 0 END )as YearTotal2, " + "SUM(CASE WHEN gift.a_date_entered_d BETWEEN '" + period3Start + "' AND '" + period3End + "' " + "THEN " + amountFieldName + " ELSE 0 END )as YearTotal3 " + "FROM PUB_a_gift as gift, PUB_a_gift_detail as detail, PUB_a_gift_batch AS GiftBatch, PUB_p_partner AS donor, PUB_p_partner AS recipient "; if (onlySelectedTypes) { SqlQuery += ", PUB_p_partner_type AS RecipientType "; } if (fromExtract) { String extractName = AParameters.Get("param_extract_name").ToString(); SqlQuery += (", PUB_m_extract AS Extract, PUB_m_extract_master AS ExtractMaster " + "WHERE " + "recipient.p_partner_key_n = Extract.p_partner_key_n " + "AND Extract.m_extract_id_i = ExtractMaster.m_extract_id_i " + "AND ExtractMaster.m_extract_name_c = '" + extractName + "' " + "AND " ); } else { SqlQuery += "WHERE "; } SqlQuery += ("detail.a_ledger_number_i = " + LedgerNum + " " + "AND detail.p_recipient_key_n = recipient.p_partner_key_n " + "AND gift.p_donor_key_n = donor.p_partner_key_n " + "AND detail.a_batch_number_i = gift.a_batch_number_i " + "AND detail.a_gift_transaction_number_i = gift.a_gift_transaction_number_i " + "AND gift.a_date_entered_d BETWEEN '" + period3Start + "' AND '" + period0End + "' " + "AND gift.a_ledger_number_i = " + LedgerNum + " " + "AND GiftBatch.a_batch_status_c = 'Posted' " + "AND GiftBatch.a_batch_number_i = gift.a_batch_number_i " + "AND GiftBatch.a_ledger_number_i = " + LedgerNum + " " ); if (oneRecipient) { String recipientKey = AParameters.Get("param_recipient_key").ToString(); SqlQuery += ("AND recipient.p_partner_key_n = " + recipientKey + " "); } if (onlySelectedFields) { String selectedFieldList = AParameters.Get("param_clbFields").ToString(); selectedFieldList = selectedFieldList.Replace('\'', ' '); SqlQuery += ("AND detail.a_recipient_ledger_number_n IN (" + selectedFieldList + ") "); } if (onlySelectedTypes) { String selectedTypeList = "'" + AParameters.Get("param_clbTypes").ToString() + "'"; selectedTypeList = selectedTypeList.Replace(",", "','"); SqlQuery += ("AND RecipientType.p_partner_key_n = detail.p_recipient_key_n " + "AND RecipientType.p_type_code_c IN (" + selectedTypeList + ") "); } SqlQuery += ( "GROUP by gift.p_donor_key_n, donor.p_partner_short_name_c, donor.p_partner_class_c, recipient.p_partner_key_n, recipient.p_partner_short_name_c " + "ORDER BY recipient.p_partner_short_name_c"); TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("SelectGiftRecipients"); // store the query in the session to be called again in SelectGiftDonors TSession.SetVariable("QueryFinanceReport_SelectGiftRecipients", SqlQuery); db.ReadTransaction( ref Transaction, delegate { TotalGiftsPerRecipient = db.SelectDT(SqlQuery, "result", Transaction); }); // // Ok, I've got a DataTable with ALL THE DATA I need for the report, // but for this calculation I only want a list of partner keys and names... // DataTable resultTable = new DataTable(); resultTable.Columns.Add("RecipientKey", typeof(String)); // These are the names of the variables resultTable.Columns.Add("RecipientName", typeof(String)); // returned by this calculation. Int64 previousPartner = -1; foreach (DataRow Row in TotalGiftsPerRecipient.Rows) { Int64 partnerKey = Convert.ToInt64(Row["RecipientKey"]); if (partnerKey != previousPartner) { previousPartner = partnerKey; DataRow NewRow = resultTable.NewRow(); NewRow["RecipientKey"] = Row["RecipientKey"]; NewRow["RecipientName"] = Row["RecipientName"]; resultTable.Rows.Add(NewRow); } } return(resultTable); } // Select Gift Recipients
} // Total Gifts Through Field Month /// <summary> /// Find all the gifts for a year, returning "worker", "field" and "total" results. /// </summary> public static DataTable TotalGiftsThroughFieldYear(TParameterList AParameters, TResultList AResults) { bool TaxDeductiblePercentageEnabled = new TSystemDefaults().GetBooleanDefault(SharedConstants.SYSDEFAULT_TAXDEDUCTIBLEPERCENTAGE, false); Int32 LedgerNum = AParameters.Get("param_ledger_number_i").ToInt32(); Int32 NumberOfYears = AParameters.Get("param_NumberOfYears").ToInt32(); string SqlQuery = "SELECT batch.a_gl_effective_date_d as Date, motive.a_report_column_c AS ReportColumn, "; if (AParameters.Get("param_currency").ToString() == "Base") { SqlQuery += "detail.a_gift_amount_n AS Amount"; if (TaxDeductiblePercentageEnabled) { SqlQuery += ", detail.a_tax_deductible_amount_base_n AS TaxDeductAmount"; } } else { SqlQuery += "detail.a_gift_amount_intl_n AS Amount"; if (TaxDeductiblePercentageEnabled) { SqlQuery += ", detail.a_tax_deductible_amount_intl_n AS TaxDeductAmount"; } } SqlQuery += (" FROM PUB_a_gift as gift, PUB_a_gift_detail as detail, PUB_a_gift_batch as batch, PUB_a_motivation_detail AS motive" + " WHERE detail.a_ledger_number_i = " + LedgerNum + " AND batch.a_batch_status_c = 'Posted'" + " AND batch.a_batch_number_i = gift.a_batch_number_i" + " AND batch.a_ledger_number_i = " + LedgerNum + " AND gift.a_ledger_number_i = " + LedgerNum + " AND detail.a_batch_number_i = gift.a_batch_number_i" + " AND detail.a_gift_transaction_number_i = gift.a_gift_transaction_number_i" + " AND motive.a_ledger_number_i = " + LedgerNum + " AND motive.a_motivation_group_code_c = detail.a_motivation_group_code_c" + " AND motive.a_motivation_detail_code_c = detail.a_motivation_detail_code_c" + " AND motive.a_receipt_l=true"); DataTable tempTbl = null; TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("TotalGiftsThroughFieldYear"); db.ReadTransaction( ref Transaction, delegate { tempTbl = db.SelectDT(SqlQuery, "result", Transaction); }); DataTable resultTable = new DataTable(); resultTable.Columns.Add("SummaryYear", typeof(Int32)); // resultTable.Columns.Add("YearWorker", typeof(Decimal)); // These are the names of the variables resultTable.Columns.Add("YearWorkerCount", typeof(Int32)); // returned by this calculation. resultTable.Columns.Add("YearField", typeof(Decimal)); // resultTable.Columns.Add("YearFieldCount", typeof(Int32)); // resultTable.Columns.Add("YearTotal", typeof(Decimal)); // resultTable.Columns.Add("YearTotalCount", typeof(Int32)); // resultTable.Columns.Add("YearWorkerTaxDeduct", typeof(Decimal)); resultTable.Columns.Add("YearFieldTaxDeduct", typeof(Decimal)); resultTable.Columns.Add("YearTotalTaxDeduct", typeof(Decimal)); Int32 Year = DateTime.Now.Year; for (Int32 YearIdx = 0; YearIdx < NumberOfYears; YearIdx++) { //TODO: Calendar vs Financial Date Handling - Check if financial year start/end should be used not calendar string yearStart = String.Format("#{0:0000}-01-01#", Year - YearIdx); string yearEnd = String.Format("#{0:0000}-12-31#", Year - YearIdx); tempTbl.DefaultView.RowFilter = "Date >= " + yearStart + " AND Date < " + yearEnd; Decimal WorkerTotal = 0; Decimal FieldTotal = 0; Int32 WorkerCount = 0; Int32 FieldCount = 0; Int32 TotalCount = tempTbl.DefaultView.Count; Decimal WorkerTotalTaxDeduct = 0; Decimal FieldTotalTaxDeduct = 0; for (int i = 0; i < TotalCount; i++) { DataRow Row = tempTbl.DefaultView[i].Row; if (Row["ReportColumn"].ToString() == "Worker") { WorkerCount++; WorkerTotal += Convert.ToDecimal(Row["Amount"]); if (TaxDeductiblePercentageEnabled) { WorkerTotalTaxDeduct += Convert.ToDecimal(Row["TaxDeductAmount"]); } } else { FieldCount++; FieldTotal += Convert.ToDecimal(Row["Amount"]); if (TaxDeductiblePercentageEnabled) { FieldTotalTaxDeduct += Convert.ToDecimal(Row["TaxDeductAmount"]); } } } DataRow resultRow = resultTable.NewRow(); resultRow["SummaryYear"] = Year - YearIdx; resultRow["YearWorker"] = WorkerTotal; resultRow["YearWorkerCount"] = WorkerCount; resultRow["YearField"] = FieldTotal; resultRow["YearFieldCount"] = FieldCount; resultRow["YearTotal"] = WorkerTotal + FieldTotal; resultRow["YearTotalCount"] = TotalCount; resultRow["YearWorkerTaxDeduct"] = WorkerTotalTaxDeduct; resultRow["YearFieldTaxDeduct"] = FieldTotalTaxDeduct; resultRow["YearTotalTaxDeduct"] = WorkerTotalTaxDeduct + FieldTotalTaxDeduct; resultTable.Rows.Add(resultRow); } return(resultTable); }
private static void CheckModule(TDataBase ADBAccessObj, string AModule) { // get all sql files starting with module string[] sqlfiles = Directory.GetFiles(Path.GetFullPath(TAppSettingsManager.GetValue("SqlFiles.Path", ".")), AModule + "*.sql"); DataTable errors = new DataTable(AModule + "Errors"); foreach (string sqlfile in sqlfiles) { string sql = TDataBase.ReadSqlFile(Path.GetFileName(sqlfile)); // extend the sql to load the s_date_created_d, s_created_by_c, s_date_modified_d, s_modified_by_c // only for the first table in the FROM clause string firstTableAlias = sql.Substring(sql.ToUpper().IndexOf("FROM ") + "FROM ".Length); firstTableAlias = firstTableAlias.Substring(0, firstTableAlias.ToUpper().IndexOf("WHERE")); int indexOfAs = firstTableAlias.ToUpper().IndexOf(" AS "); if (indexOfAs > -1) { firstTableAlias = firstTableAlias.Substring(indexOfAs + " AS ".Length).Trim(); if (firstTableAlias.Contains(",")) { firstTableAlias = firstTableAlias.Substring(0, firstTableAlias.IndexOf(",")).Trim(); } } sql = sql.Replace("FROM ", ", " + firstTableAlias + ".s_date_created_d AS DateCreated, " + firstTableAlias + ".s_created_by_c AS CreatedBy, " + firstTableAlias + ".s_date_modified_d AS DateModified, " + firstTableAlias + ".s_modified_by_c AS ModifiedBy FROM "); errors.Merge(ADBAccessObj.SelectDT(sql, "temp", null)); } if (errors.Rows.Count > 0) { SendEmailToAdmin(errors); SendEmailsPerUser(errors); } }
/// <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); }
private static GiftBatchTDS CreateGiftBatches(SortedList <DateTime, List <XmlNode> > AGiftsPerDate, int APeriodNumber) { GiftBatchTDS MainDS = new GiftBatchTDS(); ALedgerTable LedgerTable = null; TDBTransaction ReadTransaction = new TDBTransaction(); DBAccess.ReadTransaction(ref ReadTransaction, delegate { TDataBase db = ReadTransaction.DataBaseObj; // get a list of potential donors (all class FAMILY) string sqlGetFamilyPartnerKeys = "SELECT p_partner_key_n FROM PUB_p_family"; DataTable FamilyKeys = db.SelectDT(sqlGetFamilyPartnerKeys, "keys", ReadTransaction); // get a list of workers (all class FAMILY, with special type WORKER) string sqlGetWorkerPartnerKeys = "SELECT PUB_p_family.p_partner_key_n FROM PUB_p_family, PUB_p_partner_type WHERE PUB_p_partner_type.p_partner_key_n = PUB_p_family.p_partner_key_n AND p_type_code_c = 'WORKER'"; DataTable WorkerKeys = db.SelectDT(sqlGetWorkerPartnerKeys, "keys", ReadTransaction); // get a list of fields (all class UNIT, with unit type F) string sqlGetFieldPartnerKeys = String.Format( "SELECT U.p_partner_key_n FROM PUB_p_unit U WHERE u_unit_type_code_c = 'F' AND EXISTS (SELECT * FROM PUB_a_valid_ledger_number V WHERE V.a_ledger_number_i = {0} AND V.p_partner_key_n = U.p_partner_key_n)", FLedgerNumber); DataTable FieldKeys = db.SelectDT(sqlGetFieldPartnerKeys, "keys", ReadTransaction); // get a list of key ministries (all class UNIT, with unit type KEY-MIN), and their field ledger number and cost centre code string sqlGetKeyMinPartnerKeys = "SELECT u.p_partner_key_n, us.um_parent_unit_key_n, vl.a_cost_centre_code_c " + "FROM PUB_p_unit u, PUB_um_unit_structure us, PUB_a_valid_ledger_number vl " + "WHERE u.u_unit_type_code_c = 'KEY-MIN' " + "AND us.um_child_unit_key_n = u.p_partner_key_n " + "AND vl.p_partner_key_n = us.um_parent_unit_key_n " + "AND vl.a_ledger_number_i = " + FLedgerNumber.ToString(); DataTable KeyMinistries = db.SelectDT(sqlGetKeyMinPartnerKeys, "keys", ReadTransaction); LedgerTable = ALedgerAccess.LoadByPrimaryKey(FLedgerNumber, ReadTransaction); AAccountingPeriodRow AccountingPeriodRow = AAccountingPeriodAccess.LoadByPrimaryKey(FLedgerNumber, APeriodNumber, ReadTransaction)[0]; // create a gift batch for each day. // TODO: could create one batch per month, if there are not so many gifts (less than 100 per month) foreach (DateTime GlEffectiveDate in AGiftsPerDate.Keys) { if ((GlEffectiveDate.CompareTo(AccountingPeriodRow.PeriodStartDate) < 0) || (GlEffectiveDate.CompareTo(AccountingPeriodRow.PeriodEndDate) > 0)) { // only create gifts in that period continue; } AGiftBatchRow giftBatch = TGiftBatchFunctions.CreateANewGiftBatchRow(ref MainDS, ref ReadTransaction, ref LedgerTable, FLedgerNumber, GlEffectiveDate); TLogging.LogAtLevel(1, "create gift batch for " + GlEffectiveDate.ToShortDateString()); giftBatch.BatchDescription = "Benerator Batch for " + GlEffectiveDate.ToShortDateString(); giftBatch.BatchTotal = 0.0m; foreach (XmlNode RecordNode in AGiftsPerDate[GlEffectiveDate]) { AGiftRow gift = MainDS.AGift.NewRowTyped(); gift.LedgerNumber = giftBatch.LedgerNumber; gift.BatchNumber = giftBatch.BatchNumber; gift.GiftTransactionNumber = giftBatch.LastGiftNumber + 1; gift.DateEntered = GlEffectiveDate; // set donorKey int donorID = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "donor")) % FamilyKeys.Rows.Count; gift.DonorKey = Convert.ToInt64(FamilyKeys.Rows[donorID].ItemArray[0]); // calculate gift detail information int countDetails = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "splitgift")); for (int counter = 1; counter <= countDetails; counter++) { AGiftDetailRow giftDetail = MainDS.AGiftDetail.NewRowTyped(); giftDetail.LedgerNumber = gift.LedgerNumber; giftDetail.BatchNumber = gift.BatchNumber; giftDetail.GiftTransactionNumber = gift.GiftTransactionNumber; giftDetail.MotivationGroupCode = "GIFT"; giftDetail.GiftTransactionAmount = Convert.ToDecimal(TXMLParser.GetAttribute(RecordNode, "amount_" + counter.ToString())); giftDetail.GiftAmount = giftDetail.GiftTransactionAmount; giftBatch.BatchTotal += giftDetail.GiftAmount; string motivation = TXMLParser.GetAttribute(RecordNode, "motivation_" + counter.ToString()); if (motivation == "SUPPORT") { if (WorkerKeys.Rows.Count == 0) { continue; } giftDetail.MotivationDetailCode = "SUPPORT"; int recipientID = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "recipient_support_" + counter.ToString())) % WorkerKeys.Rows.Count; giftDetail.RecipientKey = Convert.ToInt64(WorkerKeys.Rows[recipientID].ItemArray[0]); giftDetail.RecipientLedgerNumber = TGiftTransactionWebConnector.GetRecipientFundNumber(giftDetail.RecipientKey, giftBatch.GlEffectiveDate); // ignore this gift detail, if there is no valid commitment period for the worker if (giftDetail.RecipientLedgerNumber == 0) { continue; } } else if (motivation == "FIELD") { if (FieldKeys.Rows.Count == 0) { continue; } giftDetail.MotivationDetailCode = "FIELD"; int recipientID = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "recipient_field_" + counter.ToString())) % FieldKeys.Rows.Count; giftDetail.RecipientKey = Convert.ToInt64(FieldKeys.Rows[recipientID].ItemArray[0]); giftDetail.RecipientLedgerNumber = giftDetail.RecipientKey; giftDetail.CostCentreCode = (giftDetail.RecipientKey / 10000).ToString("0000"); } else if (motivation == "KEYMIN") { if (KeyMinistries.Rows.Count == 0) { continue; } giftDetail.MotivationDetailCode = "KEYMIN"; int recipientID = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "recipient_keymin_" + counter.ToString())) % KeyMinistries.Rows.Count; giftDetail.RecipientKey = Convert.ToInt64(KeyMinistries.Rows[recipientID].ItemArray[0]); giftDetail.RecipientLedgerNumber = Convert.ToInt64(KeyMinistries.Rows[recipientID].ItemArray[1]); // TTransactionWebConnector.GetRecipientFundNumber(giftDetail.RecipientKey); giftDetail.CostCentreCode = KeyMinistries.Rows[recipientID].ItemArray[2].ToString(); // TTransactionWebConnector.IdentifyPartnerCostCentre(FLedgerNumber, giftDetail.RecipientLedgerNumber); } giftDetail.DetailNumber = gift.LastDetailNumber + 1; MainDS.AGiftDetail.Rows.Add(giftDetail); gift.LastDetailNumber = giftDetail.DetailNumber; } if (gift.LastDetailNumber > 0) { MainDS.AGift.Rows.Add(gift); giftBatch.LastGiftNumber = gift.GiftTransactionNumber; } if (giftBatch.LastGiftNumber >= MaxGiftsPerBatch) { break; } } if (TLogging.DebugLevel > 0) { TLogging.Log( GlEffectiveDate.ToShortDateString() + " " + giftBatch.LastGiftNumber.ToString()); } } }); // need to save the last gift batch number in a_ledger if (LedgerTable != null) { TDBTransaction WriteTransaction = new TDBTransaction(); bool SubmissionOk = false; DBAccess.WriteTransaction(ref WriteTransaction, ref SubmissionOk, delegate { ALedgerAccess.SubmitChanges(LedgerTable, WriteTransaction); SubmissionOk = true; }); if (!SubmissionOk) { TLogging.Log("An Exception occured during the creation of Gift Batches" + Environment.NewLine); } } return(MainDS); }
/// <summary> /// Returns the current sequence value for the given Sequence from the DB. /// </summary> /// <param name="ASequenceName">Name of the Sequence.</param> /// <param name="ATransaction">An instantiated Transaction in which the Query /// to the DB will be enlisted.</param> /// <param name="ADatabase">the database object that can be used for querying</param> /// <returns>Sequence Value.</returns> public System.Int64 GetCurrentSequenceValue(String ASequenceName, TDBTransaction ATransaction, TDataBase ADatabase) { DataTable table = ADatabase.SelectDT( "SELECT PUB." + ASequenceName + ".CURRVAL FROM PUB.\"_Sequence\" WHERE \"_Seq-Name\" = \"" + ASequenceName + "\"", "sequence", ATransaction); if (table.Rows.Count > 0) { return Convert.ToInt64(table.Rows[0][0]); } return -1; }
private int GetGlmSequenceFromDB(TDataBase databaseConnection, int pv_ledger_number_i, String pv_cost_centre_code_c, String pv_account_code_c, int pv_year_i) { int ReturnValue = -1; String strSql = "SELECT a_glm_sequence_i FROM PUB_a_general_ledger_master WHERE a_ledger_number_i = " + pv_ledger_number_i + " AND a_cost_centre_code_c = '" + pv_cost_centre_code_c + "' AND a_account_code_c = '" + pv_account_code_c + "' AND a_year_i = " + pv_year_i; DataTable tab = databaseConnection.SelectDT(strSql, "GetGlmSequenceFromDB_TempTable", databaseConnection.Transaction); if (tab.Rows.Count == 1) { ReturnValue = Convert.ToInt32(tab.Rows[0]["a_glm_sequence_i"]); } return ReturnValue; }
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); } }