public bool insert_Entry(ref ReconcileRecord rcRec)
{
bool return_val = false;
ArrayList AAJEntryList = rcRec.AAJEntryList;
for (int i = 0; i < AAJEntryList.Count; i++)
{
AAJMgr.checkInitial_Acct_Record((AllAccountsJournalRecord)AAJEntryList[i]);
}
//GET NEXT RECON ID
int reconcile_id = this.getNewReconID();
rcRec.recon_id = reconcile_id;
int tran_seq_no = AAJMgr.getNextTranSeqNumber();
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 = " + reconcile_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 = " + reconcile_id;
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
//INSERT RECEIVEMENT DETAILS
if (rcRec.receivementDetailList != null)
{
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
if (rcRec.paymentDetailList != null)
{
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 = "INSERT INTO [reconcile] ";
SQL += "( elt_account_number, ";
SQL += "recon_id,";
SQL += "recon_state,";
SQL += "system_balance_asof_recon_date,";
if (rcRec.interested_earned_date != "")
{
SQL += "interested_earned_date,";
}
if (rcRec.modified_date != "")
{
SQL += "modified_date,";
}
if (rcRec.service_charge_date != "")
{
SQL += "service_charge_date,";
}
if (rcRec.statement_ending_date != "")
{
SQL += "statement_ending_date,";
}
if (rcRec.created_date != "")
{
SQL += "created_date,";
}
SQL += "bank_account_number,";
SQL += "statement_ending_balance,";
SQL += "interest_earned,";
SQL += "opening_balance,";
SQL += "system_balance_asof_statement,";
SQL += "total_cleared,";
SQL += "total_unclear_after_statement,";
SQL += "total_uncleared,";
SQL += "gj_entry_no,";
SQL += "service_charge)";
SQL += "VALUES";
SQL += "('" + elt_account_number;
SQL += "','" + reconcile_id;
SQL += "','" + rcRec.recon_state;
SQL += "','" + rcRec.system_balance_asof_recon_date;
if (rcRec.interested_earned_date != "")
{
SQL += "','" + rcRec.interested_earned_date;
}
if (rcRec.modified_date != "")
{
SQL += "','" + rcRec.modified_date;
}
if (rcRec.service_charge_date != "")
{
SQL += "','" + rcRec.service_charge_date;
}
if (rcRec.statement_ending_date != "")
{
SQL += "','" + rcRec.statement_ending_date;
}
if (rcRec.created_date != "")
{
SQL += "','" + rcRec.created_date;
}
SQL += "','" + rcRec.bank_account_number;
SQL += "','" + rcRec.statement_ending_balance;
SQL += "','" + rcRec.interest_earned;
SQL += "','" + rcRec.opening_balance;
SQL += "','" + rcRec.system_balance_asof_statement;
SQL += "','" + rcRec.total_cleared;
SQL += "','" + rcRec.total_unclear_after_statement;
SQL += "','" + rcRec.total_uncleared;
SQL += "','" + rcRec.gj_entry_no;
SQL += "','" + rcRec.service_charge;
SQL += "')";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
//INSERT ALL_ACCOUNT_JOURNAL ENTRY
for (int i = 0; i < AAJEntryList.Count; i++)
{
((AllAccountsJournalRecord)AAJEntryList[i]).replaceQuote();
((AllAccountsJournalRecord)AAJEntryList[i]).tran_num = reconcile_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);
}
public bool insert_Entries(ArrayList gjeList, int tran_no)
{
bool return_val = false;
for (int i = 0; i < gjeList.Count; i++)
{
AllAccountsJournalRecord AAJEntry = ((GeneralJournalRecord)gjeList[i]).All_Accounts_Journal_Entry;
aajMgr.checkInitial_Acct_Record(AAJEntry);
}
Cmd = new SqlCommand();
Cmd.Connection = Con;
Con.Open();
SqlTransaction trans = Con.BeginTransaction();
Cmd.Transaction = trans;
try
{
int next_tran_seq_no = aajMgr.getNextTranSeqNumber();
for (int i = 0; i < gjeList.Count; i++)
{
AllAccountsJournalRecord AAJEntry = ((GeneralJournalRecord)gjeList[i]).All_Accounts_Journal_Entry;
((GeneralJournalRecord)gjeList[i]).entry_no = tran_no;
((GeneralJournalRecord)gjeList[i]).replaceQuote();
AAJEntry.replaceQuote();
AAJEntry.tran_type = "GJE";
AAJEntry.tran_num = tran_no;
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 += "gl_previous_balance)";
SQL += "VALUES";
SQL += "('" + elt_account_number;
SQL += "','" + AAJEntry.tran_num;
SQL += "','" + AAJEntry.gl_account_number;
SQL += "','" + AAJEntry.gl_account_name;
SQL += "','" + next_tran_seq_no++;
SQL += "','" + AAJEntry.tran_type;
SQL += "','" + AAJEntry.tran_date;
SQL += "','" + AAJEntry.customer_number;
SQL += "','" + AAJEntry.customer_name;
SQL += "','" + AAJEntry.debit_amount;
SQL += "','" + AAJEntry.credit_amount;
SQL += "','" + AAJEntry.balance;
SQL += "','" + AAJEntry.previous_balance;
SQL += "','" + AAJEntry.gl_balance;
SQL += "','" + AAJEntry.gl_previous_balance;
SQL += "')";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
SQL = "INSERT INTO [general_journal_entry] ";
SQL += "(credit, ";
SQL += "debit,";
SQL += "dt,";
SQL += "elt_account_number,";
SQL += "entry_no,";
SQL += "gl_account_number,";
SQL += "item_no,";
SQL += "memo,";
SQL += "org_acct)";
SQL += "VALUES";
SQL += "('" + ((GeneralJournalRecord)gjeList[i]).credit;
SQL += "','" + ((GeneralJournalRecord)gjeList[i]).debit;
SQL += "','" + ((GeneralJournalRecord)gjeList[i]).dt;
SQL += "','" + ((GeneralJournalRecord)gjeList[i]).elt_account_number;
SQL += "','" + ((GeneralJournalRecord)gjeList[i]).entry_no;
SQL += "','" + ((GeneralJournalRecord)gjeList[i]).gl_account_number;
SQL += "','" + ((GeneralJournalRecord)gjeList[i]).item_no;
SQL += "','" + ((GeneralJournalRecord)gjeList[i]).memo;
SQL += "'," + ((GeneralJournalRecord)gjeList[i]).org_acct;
SQL += ")";
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 insertBillRecord(ref BillRecord bRec, string tran_type)
{
bRec.replaceQuote();
bool return_val = false;
int bill_number = getNewbillNumber();
bRec.Bill_number = bill_number;
int next_item_id = bdMgr.getNextItemIDForBill(bill_number);
if (bRec.All_accounts_journal_list != null)
{
setTranNoForAllAccountsJournalEntries(bRec.All_accounts_journal_list, bill_number);
}
for (int i = 0; i < bRec.BillDetailList.Count; i++)
{
((BillDetailRecord)bRec.BillDetailList[i]).bill_number = bill_number;
}
ArrayList bdList = bRec.BillDetailList;
ArrayList AAJEntryList = bRec.All_accounts_journal_list;
if (bRec.All_accounts_journal_list != null)
{
for (int i = 0; i < AAJEntryList.Count; i++)
{
AAJMgr.checkInitial_Acct_Record((AllAccountsJournalRecord)AAJEntryList[i]);
}
}
int next_tran_seq_no = AAJMgr.getNextTranSeqNumber();
Cmd = new SqlCommand();
Cmd.Connection = Con;
Con.Open();
SqlTransaction trans = Con.BeginTransaction();
Cmd.Transaction = trans;
try
{
//Keept the ID from OPERATION/UPDATE BILL DETAILS SINCE BILL_DETAIL CAN BE ALREAY EXISTING/ generated From MB/
for (int i = 0; i < bdList.Count; i++)
{
BillDetailRecord bDRec = (BillDetailRecord)bdList[i];
bDRec.bill_number = bill_number;
if (bDRec.item_id != -1 && bDRec.item_id != 0)
{
SQL = "UPDATE [bill_detail] ";
SQL += "set elt_account_number= '" + elt_account_number + "',";
SQL += "item_ap= '" + bDRec.item_ap + "',";
SQL += "item_id= '" + bDRec.item_id + "',";
SQL += "item_no= '" + bDRec.item_no + "',";
SQL += "item_amt= '" + bDRec.item_amt + "',";
SQL += "is_manual= '" + bDRec.is_manual + "',";
SQL += "item_expense_acct= '" + bDRec.item_expense_acct + "',";
SQL += "tran_date= '" + bDRec.tran_date + "',";
//--------------here is where update can be effective for the bill_detail at AP Queue
if (bDRec.Is_checked)
{
SQL += "bill_number= '" + bDRec.bill_number + "',";
}
else
{
SQL += "bill_number= 0,";
bDRec.bill_number = 0;
}
//--------------
SQL += "invoice_no= '" + bDRec.invoice_no + "',";
SQL += "agent_debit_no= '" + bDRec.agent_debit_no + "',";
SQL += "mb_no= '" + bDRec.mb_no + "',";
SQL += "ref= '" + bDRec.ref_no + "',";
SQL += "iType= '" + bDRec.iType + "',";
SQL += "vendor_number= '" + bDRec.vendor_number + "'";
SQL += "WHERE elt_account_number = " + elt_account_number;
SQL += " AND invoice_no = " + bDRec.invoice_no;
SQL += " AND item_id=" + bDRec.item_id;
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
}
else
{
bDRec.item_id = next_item_id++;
SQL = "INSERT INTO [bill_detail] ";
SQL += "( elt_account_number, ";
SQL += "item_ap,";
SQL += "item_id,";
SQL += "item_no,";
SQL += "item_amt,";
SQL += "is_manual,";
SQL += "item_expense_acct,";
SQL += "tran_date,";
SQL += "bill_number,";
SQL += "invoice_no,";
SQL += "agent_debit_no,";
SQL += "mb_no,";
SQL += "ref,";
SQL += "iType,";
SQL += "vendor_number)";
SQL += "VALUES";
SQL += "('" + elt_account_number;
SQL += "','" + bDRec.item_ap;
SQL += "','" + bDRec.item_id;
SQL += "','" + bDRec.item_no;
SQL += "','" + bDRec.item_amt;
SQL += "','" + bDRec.is_manual;
SQL += "','" + bDRec.item_expense_acct;
SQL += "','" + bDRec.tran_date;
SQL += "','" + bDRec.bill_number;
SQL += "','" + bDRec.invoice_no;
SQL += "','" + bDRec.agent_debit_no;
SQL += "','" + bDRec.mb_no;
SQL += "','" + bDRec.ref_no;
SQL += "','" + bDRec.iType;
SQL += "','" + bDRec.vendor_number;
SQL += "')";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
}
}
if (bRec.All_accounts_journal_list != null)
{
for (int i = 0; i < AAJEntryList.Count; i++)
{
((AllAccountsJournalRecord)AAJEntryList[i]).replaceQuote();
((AllAccountsJournalRecord)AAJEntryList[i]).tran_num = bill_number;
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 += "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 += "','" + next_tran_seq_no++;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).tran_type;
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]).gl_previous_balance;
SQL += "')";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
}
}
SQL = "INSERT INTO [bill] ";
SQL += "(elt_account_number,";
SQL += "bill_number,";
SQL += "bill_type,";
SQL += "vendor_number,";
SQL += "vendor_name,";
SQL += "bill_date,";
SQL += "bill_due_date,";
SQL += "bill_amt,";
SQL += "bill_amt_paid,";
SQL += "bill_amt_due,";
SQL += "ref_no,";
SQL += "bill_expense_acct,";
SQL += "bill_ap,";
SQL += "bill_status,";
SQL += "print_id,";
SQL += "lock,";
SQL += "pmt_method,";
SQL += "is_org_merged)";
SQL += "VALUES";
SQL += "('" + elt_account_number;
SQL += "','" + bRec.Bill_number;
SQL += "','" + bRec.Bill_type;
SQL += "','" + bRec.Vendor_number;
SQL += "','" + bRec.Vendor_name;
SQL += "','" + bRec.Bill_date;
SQL += "','" + bRec.Bill_due_date;
SQL += "','" + bRec.Bill_amt;
SQL += "','" + bRec.Bill_amt_paid;
SQL += "','" + bRec.Bill_amt_due;
SQL += "','" + bRec.Ref_no;
SQL += "','" + bRec.Bill_expense_acct;
SQL += "','" + bRec.Bill_ap;
SQL += "','" + bRec.Bill_status;
SQL += "','" + bRec.Print_id;
SQL += "','" + bRec.Lock;
SQL += "','" + bRec.Pmt_method;
SQL += "','" + bRec.Is_org_merged;
SQL += "')";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
SQL = "Delete FROM bill_detail WHERE elt_account_number = "
+ elt_account_number + " AND bill_number = " + bill_number + " AND item_amt = " + 0;
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
trans.Commit();
return_val = true;
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
Con.Close();
}
MBCostItemsManager MBCostItemMgr = new MBCostItemsManager(elt_account_number);
IVCostItemsManager IVCostItemMgr = new IVCostItemsManager(elt_account_number);
int invoice_no = 0;
string mb_no = "";
int item_id = 0;
for (int i = 0; i < bdList.Count; i++)
{
if (((BillDetailRecord)bdList[i]).bill_number == 0)
{
if (((BillDetailRecord)bdList[i]).mb_no != "")
{
mb_no = ((BillDetailRecord)bdList[i]).mb_no;
item_id = ((BillDetailRecord)bdList[i]).item_id;
MBCostItemMgr.resetAPLock(mb_no, item_id, "N");
}
if (((BillDetailRecord)bdList[i]).invoice_no != 0)
{
invoice_no = ((BillDetailRecord)bdList[i]).invoice_no;
item_id = ((BillDetailRecord)bdList[i]).item_id;
IVCostItemMgr.resetAPLock(invoice_no, item_id, "N");
}
}
else
{
if (((BillDetailRecord)bdList[i]).mb_no != "")
{
mb_no = ((BillDetailRecord)bdList[i]).mb_no;
item_id = ((BillDetailRecord)bdList[i]).item_id;
MBCostItemMgr.resetAPLock(mb_no, item_id, "Y");
}
if (((BillDetailRecord)bdList[i]).invoice_no != 0)
{
invoice_no = ((BillDetailRecord)bdList[i]).invoice_no;
item_id = ((BillDetailRecord)bdList[i]).item_id;
IVCostItemMgr.resetAPLock(invoice_no, item_id, "Y");
}
}
}
return(return_val);
}
public bool insertPaymentRecord(ref PaymentRecord pRec, string tran_type)
{
bool return_val = false;
pRec.replaceQuote();
int payment_no = getNewpaymentNumber();
ArrayList AAJEntryList = pRec.AllAccountsJournalList;
ArrayList pdList = pRec.PaymentDetailList;
ArrayList IVList = pRec.InvoiceList;
setTranNoForAllAccountsJournalEntries(pRec.AllAccountsJournalList, payment_no);
for (int i = 0; i < AAJEntryList.Count; i++)
{
AAJMgr.checkInitial_Acct_Record((AllAccountsJournalRecord)AAJEntryList[i]);
}
int tran_seq_no = AAJMgr.getNextTranSeqNumber();
Cmd = new SqlCommand();
Cmd.Connection = Con;
Con.Open();
SqlTransaction trans = Con.BeginTransaction();
Cmd.Transaction = trans;
try
{
//-------------------------UPDATE PAYMENT DETAILS-------------------------
//1) DELETE PAYMENT DETAIL LIST
SQL = "DELETE FROM customer_payment_detail WHERE elt_account_number =";
SQL += elt_account_number + " and payment_no="
+ payment_no;
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
//2) INSERT PAYMENT DETAIL LIST
for (int i = 0; i < pdList.Count; i++)
{
PaymentDetailRecord pdRec = (PaymentDetailRecord)pdList[i];
pdRec.payment_no = payment_no;
SQL = "INSERT INTO [customer_payment_detail] ";
SQL += "( elt_account_number, ";
SQL += "amt_due,";
SQL += "item_id,";
SQL += "invoice_date,";
SQL += "invoice_no,";
SQL += "orig_amt,";
SQL += "payment,";
SQL += "type,";
SQL += "payment_no)";
SQL += "VALUES";
SQL += "('" + elt_account_number;
SQL += "','" + pdRec.amt_due;
SQL += "','" + i;
SQL += "','" + pdRec.invoice_date;
SQL += "','" + pdRec.invoice_no;
SQL += "','" + pdRec.orig_amt;
SQL += "','" + pdRec.payment;
SQL += "','" + pdRec.type;
SQL += "','" + pdRec.payment_no;
SQL += "')";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
}
//----------------UPDATE LIST OF INVOICE RECORD
for (int i = 0; i < IVList.Count; i++)
{
InvoiceRecord ivRec = (InvoiceRecord)IVList[i];
SQL = "update invoice set ";
SQL += "amount_paid= '" + ivRec.amount_paid + "' ,";
SQL += "balance= '" + ivRec.balance + "' ,";
SQL += "deposit_to= '" + ivRec.deposit_to + "' ,";
SQL += "lock_ar= 'Y' ,";
if (ivRec.balance == 0)
{
SQL += "pay_status= 'P' ,";
}
else
{
SQL += "pay_status= 'A' ,";
}
SQL += "pmt_method= '" + ivRec.pmt_method + "'";
SQL += " WHERE elt_account_number = " + elt_account_number + " and invoice_no=" + ivRec.Invoice_no;
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
}
//----------UPDATE ALL ACCOUNT JOURNAL ENTRIES
//1) DELETE AAJ ENTRIES WITH THE SAME PAYMNET NO
SQL = "Delete FROM all_accounts_journal WHERE elt_account_number = "
+ elt_account_number + " AND tran_num = " + payment_no + " AND tran_type = '" + tran_type + "'";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
//2)INSERT AAJ ENTRIES
for (int i = 0; i < AAJEntryList.Count; i++)
{
((AllAccountsJournalRecord)AAJEntryList[i]).replaceQuote();
((AllAccountsJournalRecord)AAJEntryList[i]).tran_num = payment_no;
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 += "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 += "','" + "PMT";
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]).gl_previous_balance;
SQL += "')";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
}
//INSERT PAYMNET RECORD
SQL = "INSERT INTO [customer_payment] ";
SQL += "(elt_account_number,";
SQL += "payment_no,";
SQL += "accounts_receivable,";
SQL += "added_amt,";
SQL += "balance,";
SQL += "branch,";
SQL += "customer_name,";
SQL += "customer_number,";
SQL += "deposit_to,";
SQL += "existing_credits,";
SQL += "payment_date,";
SQL += "pmt_method,";
SQL += "received_amt,";
SQL += "ref_no,";
SQL += "unapplied_amt)";
SQL += "VALUES";
SQL += "('" + elt_account_number;
SQL += "','" + payment_no;
SQL += "','" + pRec.accounts_receivable;
SQL += "','" + pRec.added_amt;
SQL += "','" + pRec.balance;
SQL += "','" + pRec.branch;
SQL += "','" + pRec.customer_name;
SQL += "','" + pRec.customer_number;
SQL += "','" + pRec.deposit_to;
SQL += "','" + pRec.existing_credits;
SQL += "','" + pRec.payment_date;
SQL += "','" + pRec.pmt_method;
SQL += "','" + pRec.received_amt;
SQL += "','" + pRec.ref_no;
SQL += "','" + pRec.unapplied_amt;
SQL += "')";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
trans.Commit();
return_val = true;
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
Con.Close();
}
pRec.payment_no = payment_no;
return(return_val);
}
public bool insert_customer_credit(customerCreditRecord ccRec)//农贰调老 版快/Refund 老 版快
{
int return_val;
ccRec.replaceQuote();
ArrayList AAJEntryList = ccRec.all_accounts_journal_list;
if (AAJEntryList == null)
{
AAJEntryList = new ArrayList();
}
for (int i = 0; i < AAJEntryList.Count; i++)
{
AAJMgr.checkInitial_Acct_Record((AllAccountsJournalRecord)AAJEntryList[i]);
}
int next_tran_seq_no = AAJMgr.getNextTranSeqNumber();
int entry_no = getNextEntryNo(ccRec.customer_no);
Cmd = new SqlCommand();
Cmd.Connection = Con;
Con.Open();
SqlTransaction trans = Con.BeginTransaction();
Cmd.Transaction = trans;
if (AAJEntryList.Count > 0)
{
for (int i = 0; i < AAJEntryList.Count; i++)
{
((AllAccountsJournalRecord)AAJEntryList[i]).replaceQuote();
if (((AllAccountsJournalRecord)AAJEntryList[i]).tran_num == null || ((AllAccountsJournalRecord)AAJEntryList[i]).tran_num == 0)//Only when there is no entry
{
((AllAccountsJournalRecord)AAJEntryList[i]).tran_num = entry_no;
}
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 += "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 += "','" + next_tran_seq_no++;
SQL += "','" + ((AllAccountsJournalRecord)AAJEntryList[i]).tran_type;
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]).gl_previous_balance;
SQL += "')";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();
}
}
SQL = "INSERT INTO [customer_credit_info] ";
SQL += "(elt_account_number, ";
SQL += "customer_no,";
SQL += "customer_name,";
SQL += "tran_date,";
SQL += "memo,";
SQL += "entry_no,";
SQL += "ref_no,";
SQL += "invoice_no,";
SQL += "credit)";
SQL += "VALUES";
SQL += "('" + elt_account_number;
SQL += "','" + ccRec.customer_no;
SQL += "','" + ccRec.customer_name;
SQL += "','" + ccRec.tran_date;
SQL += "','" + ccRec.memo;
SQL += "','" + entry_no;
SQL += "','" + ccRec.ref_no;
SQL += "','" + ccRec.invoice_no;
SQL += "','" + ccRec.credit;
SQL += "')";
Cmd.CommandText = SQL;
return_val = Cmd.ExecuteNonQuery();
try
{
trans.Commit();
}
catch (Exception ex)
{
throw ex;
}
finally
{
Con.Close();
}
if (return_val == 1)
{
return(true);
}
else
{
return(false);
}
}