public ArrayList getPaymentDetailList(int payment_no)
{
SQL = "select * from customer_payment_detail where elt_account_number = " + elt_account_number + " and payment_no=" + payment_no;
DataTable dt = new DataTable();
SqlDataAdapter ad = new SqlDataAdapter(SQL, Con);
GeneralUtility gUtil = new GeneralUtility();
ArrayList resultList = new ArrayList();
try
{
ad.Fill(dt);
gUtil.removeNull(ref dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
PaymentDetailRecord pdRec = new PaymentDetailRecord();
pdRec.amt_due = Decimal.Parse(dt.Rows[0]["amt_due"].ToString());
pdRec.invoice_date = dt.Rows[0]["invoice_date"].ToString();
pdRec.invoice_no = Int32.Parse((dt.Rows[0]["invoice_no"].ToString()));
pdRec.item_id = Int32.Parse(dt.Rows[0]["item_id"].ToString());
pdRec.orig_amt = Decimal.Parse(dt.Rows[0]["orig_amt"].ToString());
pdRec.payment = Decimal.Parse(dt.Rows[0]["payment"].ToString());
pdRec.payment_no = Int32.Parse((dt.Rows[0]["payment_no"].ToString()));
pdRec.type = dt.Rows[0]["type"].ToString();
resultList.Add(pdRec);
}
}
catch (Exception ex)
{
throw ex;
}
return(resultList);
}
public DataTable getPaymentDetailListDt(int payment_no)
{
SQL = "select * from customer_payment_detail where elt_account_number = " + elt_account_number + " and payment_no=" + payment_no;
DataTable dt = new DataTable();
SqlDataAdapter ad = new SqlDataAdapter(SQL, Con);
PaymentDetailRecord pdRec = new PaymentDetailRecord();
GeneralUtility gUtil = new GeneralUtility();
try
{
ad.Fill(dt);
gUtil.removeNull(ref dt);
}
catch (Exception ex)
{
throw ex;
}
return(dt);
}
public bool checkIfExistPaymentDetail(PaymentDetailRecord pdRec)
{
try
{
SQL = "select item_id from customer_payment_detail where elt_account_number = "
+ elt_account_number + " and payment_no="
+ pdRec.payment_no + " and item_id="
+ pdRec.item_id;
SqlDataAdapter adp = new SqlDataAdapter(SQL, Con);
DataTable dt = new DataTable();
adp.Fill(dt);
if (dt.Rows.Count >= 1)
{
return(true);
}
}
catch (Exception ex)
{
throw ex;
}
return(false);
}
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);
}
