Ejemplo n.º 1
0
        //--------------------  Update Payment Information ----------------------------------------------


        public void UpdatePaymentInformation(DBSite site, SalePurchaseEntity salePurchase, string ledgerIdNumber)
        {
            string ledger_id     = util.GetLedgerId(ledgerIdNumber);
            string ledger_number = util.GetLedgerNumber(ledgerIdNumber);

            // 444
            int party = Int32.Parse(util.GetLOVId(salePurchase.party));
            int mode  = Int32.Parse(util.GetLOVId(salePurchase.mode));


            if (salePurchase.transactionType == TransactionType.PaymentMade)
            {
                int tmp;
                tmp   = Int32.Parse(util.GetLOVId(salePurchase.mode));
                mode  = Int32.Parse(util.GetLOVId(salePurchase.party));
                party = tmp;
            }



            // sales - party
            string party_qry = " UPDATE tblLedger SET "
                               + " LDate = '" + salePurchase.BillDate + "' "
                               + ", AccountMasterId= " + mode
                               + ", modeid= " + party
                               + ", BillNumber= '" + util.GetLOVName(salePurchase.BillNumber) + "' "
                               + ", Particulars= '" + salePurchase.particulars + "' "
                               + ", debit = " + salePurchase.SalePurchaseAmount
                               + ", CheckNumber= '" + salePurchase.CheckNumber + "'  "
                               + ", subUserId = " + Util_BLL.User.Subusers[0].SubuserId
                               + Util_BLL.GetUserWhereCondition(Util_BLL.User)
                               // + " AND LedgerId=" + ledger_id
                               + " AND LedgerNumber=" + ledger_number
                               + " AND drcr = 'D'";



            // sales - mode
            string mode_qry = " UPDATE tblLedger SET "
                              + " LDate = '" + salePurchase.BillDate + "' "
                              + ", AccountMasterId= " + party
                              + ", modeId = " + mode
                              + ", BillNumber= '" + util.GetLOVName(salePurchase.BillNumber) + "' "
                              + ", Particulars= '" + salePurchase.particulars + "' "
                              + ", credit = " + salePurchase.SalePurchaseAmount
                              + ", CheckNumber= '" + salePurchase.CheckNumber + "' "
                              + ", subUserId = " + Util_BLL.User.Subusers[0].SubuserId
                              + Util_BLL.GetUserWhereCondition(Util_BLL.User)
                              + " AND LedgerNumber=" + ledger_number
                              + " AND drcr = 'C'";



            site.Execute(party_qry);
            site.Execute(mode_qry);
        }
Ejemplo n.º 2
0
        //------------------  Get Sale-Purchage Information -----------------------------------------------


        public List <SalePurchaseEntity> GetSalePurchase(DBSite site, string sp_ids = "")
        {
            List <SalePurchaseEntity> sale_purchase_entity_list = new List <SalePurchaseEntity>();
            SalePurchaseEntity        sale_purchase_entity      = null;


            //444
            string qry = " SELECT ledgerId "
                         + ", Billdate "
                         + ", AccountName "
                         + ",  credit " // if sales else debit
                         + ", particulars "
                         + ", discount "
                         + " FROM tblLedger l"
                         + Util_BLL.GetUserWhereCondition(Util_BLL.User);


            //string qry = GetQueryForSimpleFields();


            DataTable dt = dt = site.ExecuteSelect(qry);



            foreach (DataRow row in dt.Rows)
            {
                sale_purchase_entity = new SalePurchaseEntity();      //  -------- sale purchase entity  ----------

                //---------------  Normal Fields  ----------------------------

                sale_purchase_entity.SalePurchaseId     = util.CheckNullInt(row["LedgerId"]);
                sale_purchase_entity.BillDate           = util.CheckNull(row["LDate"]);
                sale_purchase_entity.party              = util.CheckNull(row["AccountName"]);
                sale_purchase_entity.BillNumber         = util.CheckNull(row["BillNumber"]);
                sale_purchase_entity.SalePurchaseAmount = util.CheckNullDouble(row["Debit"]);
                sale_purchase_entity.discount           = util.CheckNull(row["Particulars"]);
                sale_purchase_entity.discount           = util.CheckNull(row["Discount"]);
                double discount = util.GetDiscountAmount(sale_purchase_entity.discount, sale_purchase_entity.SalePurchaseAmount);
                sale_purchase_entity.TotalAmount = sale_purchase_entity.SalePurchaseAmount - discount;


                //----------------  sale detail entity [ product information ]------------------



                // --------------  tax information [ taxes on the whole ] ------------------------



                sale_purchase_entity_list.Add(sale_purchase_entity);
            }

            return(sale_purchase_entity_list);
        }
Ejemplo n.º 3
0
        //-----------------  Search Method [ For Payment ] ----------------------------

        public List <SalePurchaseEntity> GetMatchedRecords(DBSite site, string payment_made_or_received, string value_to_search)
        {
            List <SalePurchaseEntity> sale_purchase_entity_list = new List <SalePurchaseEntity>();
            SalePurchaseEntity        sale_purchase_entity      = null;

            string qry = " SELECT DISTINCT LedgerNumber FROM tblLedger as ldg "
                         + " LEFT OUTER JOIN tblAccountMaster as acc"
                         + " ON ldg.AccountMasterId = acc.AccountMasterId "
                         + " WHERE ( "
                         + " ( BillNumber LIKE '%" + value_to_search + "%')"
                         + " OR ( CheckNumber LIKE '%" + value_to_search + "%')"
                         + " OR ( AccountName LIKE '%" + value_to_search + "%')";

            if (IsDate(value_to_search))
            {
                qry += " OR ( LDate = '" + value_to_search + "' ) ";
            }


            if (util.IsNumber(value_to_search))
            {
                qry += " OR ( Credit = '" + value_to_search + "' ) ";
                qry += " OR( Debit = '" + value_to_search + "' ) ";
            }

            qry += " )";
            qry += " AND ldg.UserId = " + Util_BLL.User.UserId;
            qry += " AND ldg.FYear = " + Util_BLL.User.fYear;
            qry += " AND ldg.SourceId = " + payment_made_or_received;

            string    ledger_number = null;
            DataTable dt            = site.ExecuteSelect(qry);

            foreach (DataRow row in dt.Rows)
            {
                sale_purchase_entity = new SalePurchaseEntity();
                ledger_number        = util.CheckNull(row["LedgerNumber"]);
                sale_purchase_entity = GetPaymentInformation(site, payment_made_or_received, ledger_number)[0];

                sale_purchase_entity_list.Add(sale_purchase_entity);
            }

            return(sale_purchase_entity_list);
        }
Ejemplo n.º 4
0
//-------------   Sale purchse Entity  Objects  ---------------------------------


        public List <SalePurchaseEntity> Get(DBSite site, string sp_ids = "")
        {
            List <SalePurchaseEntity> sale_purchase_entity_list = new List <SalePurchaseEntity>();

            SalePurchaseEntity sale_purchase_entity_1 = new SalePurchaseEntity
            {
                SalePurchaseId     = 1001,
                party              = "gurgaon branch1|4",
                BillNumber         = "BN108",
                BillDate           = "02/02/2012",
                SalePurchaseAmount = 3000.00,
                discount           = "50%",
                TotalAmount        = 1500.00,
                particulars        = "Sale 1",
                SalePurchaseTaxes  = new List <Tax>(

                    new Tax[] {
                    new Tax {
                        TaxId      = 101,
                        TaxName    = "Cst|3",
                        TaxPercent = 0,
                        TaxAmount  = 40
                    }
                }

                    ),

                SalePurchaseDetails = new List <SalePurchaseDetail>(

                    new SalePurchaseDetail[] {
                    new SalePurchaseDetail {
                        SalePurchaseDetailId = 100,
                        productId            = "new Product 10|4",
                        qty = 20,
                        UOM = "Litre|6",
                        SalePurchaseDetailAmount = 2000,
                        discount = "30",

                        SalePurchaseDetailsTaxes = new List <Tax>(
                            new Tax[] {
                            new Tax {
                                TaxId      = 301,
                                TaxName    = "Vat|2",
                                TaxPercent = 10,
                                TaxAmount  = 100
                            }
                        }
                            )
                    }
                }
                    )
            };

            SalePurchaseEntity sale_purchase_entity_2 = new SalePurchaseEntity
            {
                SalePurchaseId     = 1002,
                party              = "newaccount 1|21",
                BillNumber         = "BN109",
                BillDate           = "02/05/2012",
                SalePurchaseAmount = 2000.00,
                discount           = "50%",
                TotalAmount        = 1000.00,
                particulars        = "Sale 2",


                SalePurchaseTaxes = new List <Tax>(

                    new Tax[] {
                    new Tax {
                        TaxId      = 102,
                        TaxName    = "Cst|3",
                        TaxPercent = 0,
                        TaxAmount  = 50
                    }
                }

                    ),

                SalePurchaseDetails = new List <SalePurchaseDetail>(

                    new SalePurchaseDetail[] {
                    new SalePurchaseDetail {
                        SalePurchaseDetailId = 101,
                        productId            = "new Product 10|4",
                        qty = 30,
                        UOM = "Litre|6",
                        SalePurchaseDetailAmount = 2000,
                        discount = "20",

                        SalePurchaseDetailsTaxes = new List <Tax>(
                            new Tax[] {
                            new Tax {
                                TaxId      = 302,
                                TaxName    = "Vat|2",
                                TaxPercent = 0,
                                TaxAmount  = 50
                            }
                        }
                            )
                    }
                }
                    )
            };

            sale_purchase_entity_list.Add(sale_purchase_entity_1);
            sale_purchase_entity_list.Add(sale_purchase_entity_2);

            return(sale_purchase_entity_list);
        }
Ejemplo n.º 5
0
        //-------------------  Get Payment Information --------------------------------------------------

        public List <SalePurchaseEntity> GetPaymentInformation(DBSite site, string payment_made_or_received, string ledger_number = "")
        {
            List <SalePurchaseEntity> sale_purchase_entity_list = new List <SalePurchaseEntity>();
            SalePurchaseEntity        sale_purchase_entity      = null;

            string qry = " SELECT ";

            qry += "  CONVERT( VARCHAR, LedgerId ) + '-' +  CONVERT( VARCHAR, LedgerNumber ) as LedgerNumber ";
            qry += ", LDate ";
            qry += ", AccountName + ' | ' +CONVERT( VARCHAR, AccountMasterId ) as AccountName ";
            qry += ", ModeName + ' | ' +CONVERT( VARCHAR, ModeId ) as ModeName ";
            if (Int32.Parse(payment_made_or_received) == TransactionType.PaymentMade)
            {
                qry += ", debit  amount";
            }
            else
            {
                qry += ", credit amount ";
            }

            qry += ", Particulars ";
            qry += ", BillNumber ";
            qry += ", CheckNumber ";


            qry += " FROM vwLedger as ldg ";

            qry += " WHERE SourceId= " + payment_made_or_received;
            if (Int32.Parse(payment_made_or_received) == TransactionType.PaymentMade)
            {
                qry += " AND drcr ='D'";
            }
            else
            {
                qry += " AND drcr ='C'";
            }
            qry += " AND ldg.UserId=" + Util_BLL.User.UserId;
            qry += " AND ldg.FYear=" + Util_BLL.User.fYear;


            if (ledger_number != "")
            {
                qry += " AND LedgerNumber = " + ledger_number;
            }


            DataTable dt  = site.ExecuteSelect(qry);
            DataRow   row = null;

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                sale_purchase_entity = new SalePurchaseEntity();
                row = dt.Rows[i];

                sale_purchase_entity.Id = util.CheckNull(row["LedgerNumber"]);

                DateTime date = Convert.ToDateTime(row["LDate"]);
                sale_purchase_entity.BillDate           = date.ToShortDateString();
                sale_purchase_entity.CheckNumber        = util.CheckNull(row["CheckNumber"]);
                sale_purchase_entity.particulars        = util.CheckNull(row["Particulars"]);
                sale_purchase_entity.BillNumber         = util.CheckNull(row["BillNumber"]);
                sale_purchase_entity.SalePurchaseAmount = util.CheckNullDouble(row["amount"]);
                sale_purchase_entity.party = util.CheckNull(row["AccountName"]);
                sale_purchase_entity.mode  = util.CheckNull(row["modeName"]);


                sale_purchase_entity_list.Add(sale_purchase_entity);
            }

            return(sale_purchase_entity_list);
        }
Ejemplo n.º 6
0
 public void UpdateSalePurchase(DBSite site, SalePurchaseEntity salePurchase)
 {
 }
Ejemplo n.º 7
0
        public void SaveProductLedger(DBSite site, SalePurchaseEntity salePurchase, int ledgerNumber)
        {
            string qry = "";

            for (int i = 0; i < salePurchase.SalePurchaseDetails.Count; i++)
            {
                int productLedgerNumber = util.GetAutoNumber(site, "ProductLedgerNumber");
                int theLocation         = -1;
                if (salePurchase.transactionType == TransactionType.Sales)
                {
                    theLocation = GetLocationId(site, "OUT");
                }
                else
                {
                    theLocation = GetLocationId(site, "IN");
                }


                qry = " INSERT INTO tblProductLedger(LedgerNumber, ProductLedgerNumber, drcr, BillDate, BillNumber, AccountId, ProductID, BoughtQty, Discount, rate, SourceId, LocationID, UserID, subuserId, FYear) "
                      + " VALUES ( "
                      + ledgerNumber
                      + ", " + productLedgerNumber
                      + ", 'C'"
                      + ", '" + salePurchase.BillDate + "'"
                      + ", '" + salePurchase.BillNumber + "'"
                      + ", '" + util.GetLOVId(salePurchase.party) + "'"
                      + ", " + salePurchase.SalePurchaseDetails[i].productAutoId
                      + ", " + salePurchase.SalePurchaseDetails[i].qty


                      + ", '" + salePurchase.SalePurchaseDetails[i].discount + " '"
                      + ", " + salePurchase.SalePurchaseDetails[i].rate
                      + ", " + salePurchase.SaleType
                      + ", " + theLocation
                      //+ ", " + salePurchase.SalePurchaseDetails[i].locationId
                      + ", " + util.GetUserInsertQry(Util_BLL.User)
                      + " ) ";

                site.Execute(qry);

                qry = " INSERT INTO tblProductLedger(LedgerNumber, ProductLedgerNumber, drcr, BillDate, BillNumber, AccountId, ProductID, SoldQty, Discount, rate, SourceId, LocationID, UserID, subuserId, FYear) "
                      + " VALUES ( "
                      + ledgerNumber
                      + ", " + productLedgerNumber
                      + ", 'D'"
                      + ", '" + salePurchase.BillDate + "'"
                      + ", '" + salePurchase.BillNumber + "'"
                      + ", '" + Modes.SaleAccount + "'"
                      + ", " + salePurchase.SalePurchaseDetails[i].productAutoId
                      + ", " + salePurchase.SalePurchaseDetails[i].qty


                      + ", '" + salePurchase.SalePurchaseDetails[i].discount + " '"
                      + ", " + salePurchase.SalePurchaseDetails[i].rate
                      + ", " + salePurchase.SaleType
                      + ", " + theLocation
                      //+ ", " + salePurchase.SalePurchaseDetails[i].locationId
                      + ", " + util.GetUserInsertQry(Util_BLL.User)
                      + " ) ";

                site.Execute(qry);



                string tax_qry = "";

                for (int j = 0; j < salePurchase.SalePurchaseDetails[i].SalePurchaseDetailsTaxes.Count; j++)
                {
                    tax_qry = " INSERT INTO tblTax(LedgerNumber, ProductLedgerNumber, TaxMasterId, TaxAmount, UserID, subUserId, FYear) "
                              + " VALUES ( "
                              + ledgerNumber
                              + ", " + productLedgerNumber
                              + ", " + salePurchase.SalePurchaseDetails[i].SalePurchaseDetailsTaxes[j].TaxId
                              + ", " + salePurchase.SalePurchaseDetails[i].SalePurchaseDetailsTaxes[j].TaxAmount
                              + ", " + util.GetUserInsertQry(Util_BLL.User)
                              + " ) ";

                    site.Execute(tax_qry);
                }
            }
        }
Ejemplo n.º 8
0
        public void SaveSalePurchase(DBSite site, SalePurchaseEntity salePurchase, bool isOpeningBalance)
        {
            Util_BLL util = new Util_BLL();


            /**
             *
             *  // payment made If GM.isFees = 2  Then
             * Dim tmp As String
             * tmp = mode
             * mode = party
             * party = tmp
             * End If
             *
             **/

            int party = Int32.Parse(util.GetLOVId(salePurchase.party));
            int mode  = Int32.Parse(util.GetLOVId(salePurchase.mode));



            if (salePurchase.transactionType == TransactionType.PaymentMade)
            {
                int tmp;
                tmp   = Int32.Parse(util.GetLOVId(salePurchase.mode));
                mode  = Int32.Parse(util.GetLOVId(salePurchase.party));
                party = tmp;
            }

            if (salePurchase.transactionType == TransactionType.Sales)
            {
                mode = Modes.SaleAccount;
            }

            int ledgerNumber = util.GetAutoNumber(site, "LedgerNumber");

            // opening balance

            string opening_qry = "";

            if (isOpeningBalance)
            {
                string credit_debit = "Debit";

                if (salePurchase.drcr == DrCr.Debit)
                {
                    credit_debit = "Debit";
                }
                else if (salePurchase.drcr == DrCr.Credit)
                {
                    credit_debit = "Credit";
                }


                opening_qry = " INSERT INTO tblLedger ("
                              + " LedgerNumber, Ldate, AccountMasterId, modeId, Particulars, " + credit_debit + ", sourceId, UserId, SubUserId, FYear )"
                              + " VALUES ( "
                              + ledgerNumber
                              + ", '" + salePurchase.BillDate + "' "
                              + ", " + party
                              + ", " + mode
                              + ", '" + salePurchase.particulars + "' "
                              + ", " + salePurchase.SalePurchaseAmount
                              + ", " + salePurchase.transactionType
                              + ", " + util.GetUserInsertQry(Util_BLL.User)
                              + " ) ";


                site.Execute(opening_qry);
            }



            // sales - party
            string party_qry = " INSERT INTO tblLedger ("
                               + " LedgerNumber, drcr, Ldate, AccountMasterId, modeId, BillNumber, Particulars, credit, Discount, sourceId, UserId, SubuserId, FYear )"
                               + " VALUES ( "
                               + ledgerNumber
                               + ", 'C' "
                               + ", '" + salePurchase.BillDate + "' "
                               + ", " + party
                               + ", " + mode
                               + ", '" + util.GetLOVName(salePurchase.BillNumber) + "' "
                               + ", '" + salePurchase.particulars + "' "
                               + ", " + salePurchase.SalePurchaseAmount
                               + ", '" + salePurchase.discount + "'"
                               + ", " + salePurchase.transactionType
                               + ", " + util.GetUserInsertQry(Util_BLL.User)
                               + " ) ";



            // sales - party
            string mode_qry = " INSERT INTO tblLedger ("
                              + " LedgerNumber,  drcr,  Ldate, AccountMasterId, modeId, BillNumber, Particulars, debit, Discount, sourceId, UserId, SubuserId, FYear )"
                              + " VALUES ( "
                              + ledgerNumber
                              + ", 'D' "
                              + ", '" + salePurchase.BillDate + "' "
                              + ", " + mode
                              + ", " + party
                              + ", '" + util.GetLOVName(salePurchase.BillNumber) + "' "
                              + ", '" + salePurchase.particulars + "' "
                              + ", " + salePurchase.SalePurchaseAmount
                              + ", '" + salePurchase.discount + "'"
                              + ", " + salePurchase.transactionType
                              + ", " + util.GetUserInsertQry(Util_BLL.User)
                              + " ) ";

            site.Execute(party_qry);
            site.Execute(mode_qry);


            if (salePurchase.transactionType != TransactionType.PaymentMade && salePurchase.transactionType != TransactionType.PaymentRcd)
            {
                string tax_qry = "";

                for (int i = 0; i < salePurchase.SalePurchaseTaxes.Count; i++)
                {
                    tax_qry = " INSERT INTO tblTax(LedgerNumber, TaxMasterId, TaxAmount,  UserID, SubuserId, FYear) "
                              + " VALUES ( "
                              + ledgerNumber
                              + ", " + salePurchase.SalePurchaseTaxes[i].TaxId
                              + ", " + salePurchase.SalePurchaseTaxes[i].TaxAmount
                              + ", " + util.GetUserInsertQry(Util_BLL.User)
                              + " ) ";

                    site.Execute(tax_qry);
                }

                SaveProductLedger(site, salePurchase, ledgerNumber);
            }
        }