示例#1
0
    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);
    }