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); } }