public ReconcilePaymentDetailRecord getServiceChargeForReconcile_All_Accounts_Journal(ReconcileRecord rcRec)
{
SQL = "select isnull(is_recon_cleared,'N') as is_checked, * from all_accounts_journal where isnull(is_recon_cleared,'N') <> 'Y' and elt_account_number =" + elt_account_number;
SQL += " and tran_num=" + rcRec.recon_id;
SQL += " and tran_type ='REC'";
SQL += " and memo ='Reconcile -- Bank Service Charge'";
SQL += " and gl_account_number ='" + rcRec.bank_account_number + "'";
DataTable dt = new DataTable();
SqlDataAdapter adp = new SqlDataAdapter(SQL, Con);
adp.Fill(dt);
ReconcilePaymentDetailRecord RPDRec = new ReconcilePaymentDetailRecord();
if (dt.Rows.Count == 1)
{
RPDRec.customer_name = dt.Rows[0]["customer_name"].ToString();
RPDRec.customer_number = Int32.Parse(dt.Rows[0]["customer_number"].ToString());
RPDRec.credit_amount = Decimal.Parse(dt.Rows[0]["credit_amount"].ToString());
RPDRec.elt_account_number = Int32.Parse(dt.Rows[0]["elt_account_number"].ToString());
RPDRec.gl_account_name = dt.Rows[0]["gl_account_name"].ToString();
RPDRec.gl_account_number = Int32.Parse(dt.Rows[0]["gl_account_number"].ToString());
RPDRec.tran_date = dt.Rows[0]["tran_date"].ToString();
RPDRec.tran_num = Int32.Parse(dt.Rows[0]["tran_num"].ToString());
RPDRec.tran_seq_num = Int32.Parse(dt.Rows[0]["tran_seq_num"].ToString());
RPDRec.tran_type = dt.Rows[0]["tran_type"].ToString();
RPDRec.memo = dt.Rows[0]["memo"].ToString();
RPDRec.is_recon_cleared = "N";
}
return(RPDRec);
}
public bool deleteReconcile(ReconcileRecord rcRec)
{
bool return_val = false;
Cmd = new SqlCommand();
Cmd.Connection = Con;
Con.Open();
SqlTransaction trans = Con.BeginTransaction();
Cmd.Transaction = trans;
try
{
if (rcRec.receivementDetailList != null)
{
for (int i = 0; i < rcRec.receivementDetailList.Count; i++)
{
ReconcileReceivementDetailRecord rrd = (ReconcileReceivementDetailRecord)rcRec.receivementDetailList[i];
SQL = "update [all_accounts_journal] set is_recon_cleared='N' where elt_account_number = " + elt_account_number + " and tran_seq_num=" + rrd.tran_seq_num;
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
}
}
if (rcRec.paymentDetailList != null)
{
for (int i = 0; i < rcRec.paymentDetailList.Count; i++)
{
ReconcilePaymentDetailRecord rpd = (ReconcilePaymentDetailRecord)rcRec.paymentDetailList[i];
SQL = "update [all_accounts_journal] set is_recon_cleared='N' where elt_account_number = " + elt_account_number + " and tran_seq_num=" + rpd.tran_seq_num;
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
}
}
//DELETE PREVIOUS RECEIVEMENT DETAILS
SQL = "DELETE FROM reconcile_receivement_detail WHERE elt_account_number = "
+ elt_account_number + " AND recon_id = " + rcRec.recon_id;
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
//DELETE PREVIOUS PAYMENT DETAILS
SQL = "DELETE FROM reconcile_payment_detail WHERE elt_account_number = "
+ elt_account_number + " AND recon_id = " + rcRec.recon_id;
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
SQL = "DELETE FROM reconcile WHERE elt_account_number = "
+ elt_account_number + " AND recon_id = " + rcRec.recon_id;
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
SQL = "DELETE FROM all_accounts_journal WHERE elt_account_number = "
+ elt_account_number + " AND tran_type='REC' AND tran_num = " + rcRec.recon_id;
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
trans.Commit();
return_val = true;
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
Con.Close();
}
return(return_val);
}
public bool update_Entry(ReconcileRecord rcRec)
{
bool return_val = false;
int tran_seq_no = AAJMgr.getNextTranSeqNumber();
int reconcile_id = rcRec.recon_id;
ArrayList AAJEntryList = rcRec.AAJEntryList;
Cmd = new SqlCommand();
Cmd.Connection = Con;
Con.Open();
SqlTransaction trans = Con.BeginTransaction();
Cmd.Transaction = trans;
try
{
//DELETE PREVIOUS RECEIVEMENT DETAILS
SQL = "DELETE FROM reconcile_receivement_detail WHERE elt_account_number = "
+ elt_account_number + " AND recon_id = " + rcRec.recon_id;
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
//DELETE PREVIOUS PAYMENT DETAILS
SQL = "DELETE FROM reconcile_payment_detail WHERE elt_account_number = "
+ elt_account_number + " AND recon_id = " + rcRec.recon_id;
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
//INSERT RECEIVEMENT DETAILS
for (int i = 0; i < rcRec.receivementDetailList.Count; i++)
{
ReconcileReceivementDetailRecord RRD = (ReconcileReceivementDetailRecord)rcRec.receivementDetailList[i];
RRD.replaceQuote();
SQL = "INSERT INTO [reconcile_receivement_detail] ";
SQL += "( elt_account_number, ";
SQL += "recon_id,";
SQL += "customer_name,";
SQL += "customer_number,";
SQL += "debit_amount,";
SQL += "gl_account_name,";
SQL += "gl_account_number,";
SQL += "tran_date,";
SQL += "tran_num,";
SQL += "tran_seq_num,";
SQL += "is_recon_cleared,";
SQL += "memo,";
SQL += "tran_type)";
SQL += "VALUES";
SQL += "('" + elt_account_number;
SQL += "','" + reconcile_id;
SQL += "','" + RRD.customer_name;
SQL += "','" + RRD.customer_number;
SQL += "','" + RRD.debit_amount;
SQL += "','" + RRD.gl_account_name;
SQL += "','" + RRD.gl_account_number;
SQL += "','" + RRD.tran_date;
SQL += "','" + RRD.tran_num;
SQL += "','" + RRD.tran_seq_num;
SQL += "','" + RRD.is_recon_cleared;
SQL += "','" + RRD.memo;
SQL += "','" + RRD.tran_type;
SQL += "')";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
SQL = "UPDATE [all_accounts_journal] SET is_recon_cleared='"
+ RRD.is_recon_cleared + "' WHERE elt_account_number = " + elt_account_number + " and tran_seq_num=" + RRD.tran_seq_num;
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
}
//INSERT PAYMENT DETAILS
for (int i = 0; i < rcRec.paymentDetailList.Count; i++)
{
ReconcilePaymentDetailRecord RPD = (ReconcilePaymentDetailRecord)rcRec.paymentDetailList[i];
RPD.replaceQuote();
SQL = "INSERT INTO [reconcile_payment_detail] ";
SQL += "( elt_account_number, ";
SQL += "recon_id,";
SQL += "customer_name,";
SQL += "customer_number,";
SQL += "credit_amount,";
SQL += "gl_account_name,";
SQL += "gl_account_number,";
SQL += "tran_date,";
SQL += "tran_num,";
SQL += "tran_seq_num,";
SQL += "is_recon_cleared,";
SQL += "memo,";
SQL += "tran_type)";
SQL += "VALUES";
SQL += "('" + elt_account_number;
SQL += "','" + reconcile_id;
SQL += "','" + RPD.customer_name;
SQL += "','" + RPD.customer_number;
SQL += "','" + RPD.credit_amount;
SQL += "','" + RPD.gl_account_name;
SQL += "','" + RPD.gl_account_number;
SQL += "','" + RPD.tran_date;
SQL += "','" + RPD.tran_num;
SQL += "','" + RPD.tran_seq_num;
SQL += "','" + RPD.is_recon_cleared;
SQL += "','" + RPD.memo;
SQL += "','" + RPD.tran_type;
SQL += "')";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
SQL = "UPDATE [all_accounts_journal] SET is_recon_cleared='"
+ RPD.is_recon_cleared + "' WHERE elt_account_number = " + elt_account_number + " and tran_seq_num=" + RPD.tran_seq_num;
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
}
SQL = "UPDATE [reconcile] SET";
SQL += " elt_account_number=" + elt_account_number + ",";
SQL += "modified_date ='" + rcRec.modified_date + "',";
SQL += "recon_state='" + rcRec.recon_state + "',";
if (rcRec.service_charge_date != "")
{
SQL += "service_charge_date='" + rcRec.service_charge_date + "',";
}
SQL += "system_balance_asof_recon_date=" + rcRec.system_balance_asof_recon_date + ",";
SQL += "statement_ending_balance=" + rcRec.statement_ending_balance + ",";
SQL += "interest_earned=" + rcRec.interest_earned + ",";
if (rcRec.interested_earned_date != "")
{
SQL += "interested_earned_date='" + rcRec.interested_earned_date + "',";
}
SQL += "opening_balance=" + rcRec.opening_balance + ",";
SQL += "system_balance_asof_statement=" + rcRec.system_balance_asof_statement + ",";
SQL += "total_cleared=" + rcRec.total_cleared + ",";
SQL += "total_unclear_after_statement=" + rcRec.total_unclear_after_statement + ",";
SQL += "total_uncleared=" + rcRec.total_uncleared + ",";
SQL += "service_charge=" + rcRec.service_charge + ",";
SQL += "gj_entry_no=" + rcRec.gj_entry_no + "";
SQL += " where elt_account_number =" + elt_account_number + " and recon_id=" + rcRec.recon_id;
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
//DELETE AAJ ENTRIES
SQL = "Delete FROM all_accounts_journal WHERE elt_account_number = "
+ elt_account_number + " AND tran_num = " + rcRec.recon_id + " AND tran_type = 'REC'";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
for (int i = 0; i < AAJEntryList.Count; i++)
{
((AllAccountsJournalRecord)AAJEntryList[i]).replaceQuote();
((AllAccountsJournalRecord)AAJEntryList[i]).tran_num = rcRec.recon_id;
SQL = "INSERT INTO [all_accounts_journal] ";
SQL += "( elt_account_number, ";
SQL += "tran_num,";
SQL += "gl_account_number,";
SQL += "gl_account_name,";
SQL += "tran_seq_num,";
SQL += "tran_type,";
SQL += "tran_date,";
SQL += "Customer_Number,";
SQL += "Customer_Name,";
SQL += "debit_amount,";
SQL += "credit_amount,";
SQL += "balance,";
SQL += "previous_balance,";
SQL += "gl_balance,";
SQL += "memo,";
SQL += "is_recon_cleared,";
SQL += "gl_previous_balance)";
SQL += "VALUES";
SQL += "('" + elt_account_number;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).tran_num;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).gl_account_number;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).gl_account_name;
SQL += "','" + tran_seq_no++;
SQL += "','" + "REC";
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).tran_date;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).customer_number;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).customer_name;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).debit_amount;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).credit_amount;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).balance;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).previous_balance;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).gl_balance;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).memo;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).is_recon_cleared;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).gl_previous_balance;
SQL += "')";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
}
trans.Commit();
return_val = true;
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
Con.Close();
}
return(return_val);
}
