Beispiel #1
0
        private void btnupdate_Click(object sender, EventArgs e)
        {
            try
            {
                if (txtPName.Text == "" || txtPName.Text == string.Empty || txtDesc.Text == string.Empty ||
                    txtQuantity.Text == string.Empty)
                {
                    MessageBox.Show("Incomplete Data!");
                    return;
                }
                if (!tglTempStockRecord.Checked)
                {
                    if (cmbSupplier.SelectedIndex == -1 || txtStockDate.Text == string.Empty)
                    {
                        MessageBox.Show("Incomplete Data!");
                        return;
                    }
                }


                int typed = Convert.ToInt32(txtQuantity.Text);
                if (typed == 0 || typed > quantity)
                {
                    txtQuantity.Text = quantity.ToString();

                    MessageBox.Show("Please enter valid data!");
                    return;
                }

                using (TransactionScope txScope = new TransactionScope())
                {
                    //firstly update the sales product table
                    int           returnQuantity = Convert.ToInt32(txtQuantity.Text);
                    string        desc           = txtDesc.Text;
                    sales_product SalePro        = new sales_product();
                    Sales_BM      saleObject     = new Sales_BM();
                    int           stepOneCount   = 0;
                    BusinessObjects.SalesReturn salesReturnObject = new BusinessObjects.SalesReturn();
                    if (quantity == returnQuantity)
                    {
                        //delete record from sales_product
                        SalePro.DeleteSalesReturn(con, sales_id, ProdID);//this will delete the relevant product from sales product data


                        //calculating new amount
                        decimal deletedAMount = quantity * soldPrice;
                        saleObject = saleObject.getSalesByIDSalesReturn(con, sales_id);//getting the relevant sales data
                        decimal newTotal  = saleObject.total - deletedAMount;
                        decimal newCredit = newTotal - saleObject.paid;

                        //update the sales table
                        saleObject.UpdateSalesReturn(con, newTotal, newCredit);//updating amount and the credit of that sales

                        //insert to sales return table
                        int salesReturnTableCount = 0;
                        salesReturnTableCount = salesReturnObject.GetCountSalesReturn(con, sales_id);

                        if (salesReturnTableCount > 0)
                        {
                            int salesReturnProductTableCount = 0;
                            salesReturnProductTableCount = salesReturnObject.GetCountSalesProduct(con, sales_id, ProdID);

                            decimal ReturnAMount = returnQuantity * soldPrice;
                            if (salesReturnProductTableCount > 0)
                            {
                                string salesReturnProductsQuery = @"update sales_return_products set quantity= quantity + " + returnQuantity
                                                                  + "  where product_id=" + ProdID + " AND sales_id=" + sales_id + "";

                                salesReturnObject.ExecuteNonQuery(salesReturnProductsQuery, con);
                            }
                            else
                            {
                                string salesReturnProductsQuery = @"insert sales_return_products (product_id, sales_id, quantity, product_sold_price)
                        values (" + ProdID + "," + sales_id + "," + returnQuantity + "," + soldPrice + ")";
                                salesReturnObject.ExecuteNonQuery(salesReturnProductsQuery, con);
                            }
                        }
                        else
                        {
                            string salesReturnQuery = @"insert sales_return (sales_id, Total, _date, customer,paid)
                    values (" + saleObject.sales_id + "," + saleObject.total
                                                      + ",'" + saleObject.date_time.ToString("G", DateTimeFormatInfo.InvariantInfo)
                                                      + "','" + saleObject.customer_name + "'," + saleObject.paid + ")";
                            salesReturnObject.ExecuteNonQuery(salesReturnQuery, con);


                            string salesReturnProductsQuery = @"insert sales_return_products (product_id, sales_id, quantity, product_sold_price)
                        values (" + ProdID + "," + sales_id + "," + returnQuantity + "," + soldPrice + ")";
                            salesReturnObject.ExecuteNonQuery(salesReturnProductsQuery, con);
                        }
                    }

                    else
                    {
                        //updating sale_product table
                        int     newQuantity = quantity - returnQuantity;
                        decimal newTotalSalesProductPrice = newQuantity * soldPrice;
                        SalePro.UpdateSalesReturn(con, sales_id, ProdID, newQuantity, newTotalSalesProductPrice);


                        // //update the sales table
                        decimal deletedAMount = returnQuantity * soldPrice;
                        saleObject = saleObject.getSalesByIDSalesReturn(con, sales_id);

                        decimal newTotal  = saleObject.total - deletedAMount;
                        decimal newCredit = newTotal - saleObject.paid;
                        saleObject.UpdateSalesReturn(con, newTotal, newCredit);

                        //insert to sales return table
                        int salesReturnTableCount = 0;
                        salesReturnTableCount = salesReturnObject.GetCountSalesReturn(con, sales_id);

                        if (salesReturnTableCount > 0)
                        {
                            int salesReturnProductTableCount = 0;
                            salesReturnProductTableCount = salesReturnObject.GetCountSalesProduct(con, sales_id, ProdID);

                            decimal ReturnAMount = returnQuantity * soldPrice;
                            if (salesReturnProductTableCount > 0)
                            {
                                //int countAvalQun= salesReturnProductTableCount = salesReturnObject.GetQuantitySalesProduct(con, sales_id, ProdID);
                                //int newQuanttt = returnQuantity + countAvalQun;
                                string salesReturnProductsQuery = @"update sales_return_products set quantity=quantity+" + returnQuantity
                                                                  + "  where product_id=" + ProdID + " AND sales_id=" + sales_id + "";

                                salesReturnObject.ExecuteNonQuery(salesReturnProductsQuery, con);
                            }
                            else
                            {
                                string salesReturnProductsQuery = @"insert sales_return_products (product_id, sales_id, quantity, product_sold_price)
                        values (" + ProdID + "," + sales_id + "," + returnQuantity + "," + soldPrice + ")";
                                salesReturnObject.ExecuteNonQuery(salesReturnProductsQuery, con);
                            }
                        }
                        else
                        {
                            string salesReturnQuery = @"insert sales_return (sales_id, Total, _date, customer,paid)
                    values (" + saleObject.sales_id + "," + saleObject.total
                                                      + ",'" + saleObject.date_time.ToString("G", DateTimeFormatInfo.InvariantInfo)
                                                      + "','" + saleObject.customer_name + "'," + saleObject.paid + ")";
                            salesReturnObject.ExecuteNonQuery(salesReturnQuery, con);


                            string salesReturnProductsQuery = @"insert sales_return_products (product_id, sales_id, quantity, product_sold_price)
                        values (" + ProdID + "," + sales_id + "," + returnQuantity + "," + soldPrice + ")";
                            salesReturnObject.ExecuteNonQuery(salesReturnProductsQuery, con);
                        }
                    }

                    int SalesTableProductCount = salesReturnObject.GetCountSalesProductTable(con, sales_id);
                    if (SalesTableProductCount == 0)
                    {
                        string salesDeleteQueru = @"Delete from _Sales where sales_id=" + sales_id + "";
                        salesReturnObject.ExecuteNonQuery(salesDeleteQueru, con);
                    }



                    //update the stock data here*********************************************************************
                    // returnQuantity  ProdID  cmbSupplier.SelectedIndex txtStockInvoice.text;

                    #region removed_by_new_code

                    /*
                     *
                     #region if
                     * if (tglTempStockRecord.Checked)
                     * {
                     *  //string invoiceNum = txtStockInvoice.Text;
                     *  string custStockName = "SalesReturnStock";
                     *  string getTemStockRecordExistece = @"select stock_id from  _Stokc where sup_name='" + custStockName + "'";
                     *  int existence= 0;
                     *  if (BusinessObjects.SalesReturn.getScalar(con, getTemStockRecordExistece) != DBNull.Value)
                     *  {
                     *      object temPonj=BusinessObjects.SalesReturn.getScalar(con, getTemStockRecordExistece);
                     *      if (temPonj != null)
                     *          existence = (int)temPonj;
                     *  }
                     *  if (existence > 0)//this means the temp sales return data is in stock table
                     *  {
                     *      string getCountOfstokcreturn = @"select quantity from  _stock_product where pid=" + ProdID + " AND stock_id='" + existence + "'";
                     *      int AllreadyAvaliableQuant = 0;
                     *      if (BusinessObjects.SalesReturn.getScalar(con, getCountOfstokcreturn) != DBNull.Value)
                     *      {
                     *          object temPobj = BusinessObjects.SalesReturn.getScalar(con, getCountOfstokcreturn);
                     *          if (temPobj != null)
                     *              AllreadyAvaliableQuant = (int)temPobj;
                     *      }
                     *      if (AllreadyAvaliableQuant > 0)
                     *      {
                     *          int newQuan = AllreadyAvaliableQuant + returnQuantity;
                     *
                     *          string stockProductUpdateQuery = @"update _stock_product set quantity=" + newQuan + " where pid=" + ProdID + " and stock_id =" + existence + "";
                     *
                     *          salesReturnObject.ExecuteNonQuery(stockProductUpdateQuery, con);
                     *      }
                     *      else
                     *      {
                     *
                     *          string stockProductInsertQuery = @"insert _stock_product  (pid,quantity,stock_id) values(" + ProdID + "," + returnQuantity + "," + existence + ") ";
                     *          salesReturnObject.ExecuteNonQuery(stockProductInsertQuery, con);
                     *
                     *      }
                     *  }
                     *  else //this means the temp stock return data is not availeble in stock table.
                     *  {
                     *      //addign data to stock table first (SalesReturn Temp Record)
                     *      string addTempStockData = @"insert _Stokc  (sup_name,s_date,invoice_no) values('SalesReturnStock','" + DateTime.Now.ToString("G", DateTimeFormatInfo.InvariantInfo) + "','00000') ";
                     *      salesReturnObject.ExecuteNonQuery(addTempStockData, con);
                     *
                     *      //getting last inserted ID
                     *      string querySI = @"select IDENT_CURRENT('_Stokc')";
                     *      int lastInsertedID = Convert.ToInt32(Sales_BM.getScalar(con, querySI));
                     *
                     *      //now we add data the the stock product table
                     *      string AfterInsertSPQuery = @"insert _stock_product  (pid,quantity,stock_id) values(" + ProdID + "," + returnQuantity + "," + lastInsertedID + ") ";
                     *      salesReturnObject.ExecuteNonQuery(AfterInsertSPQuery, con);
                     *  }
                     * }
                     #endregion
                     #region else
                     * else
                     * {
                     *  //string invoiceNum = txtStockInvoice.Text;
                     *  int stockIDRt=Convert.ToInt32(txtStokIDRetrieved.Text);
                     *  string getCountOfstokcreturn = @"select quantity from  _stock_product where pid=" + ProdID + " AND stock_id='" + stockIDRt + "'";
                     *  int AllreadyAvaliableQuant=0;
                     *  if (BusinessObjects.SalesReturn.getScalar(con, getCountOfstokcreturn) != DBNull.Value)
                     *  {
                     *      object objTemp=BusinessObjects.SalesReturn.getScalar(con, getCountOfstokcreturn);
                     *      if (objTemp != null)
                     *          AllreadyAvaliableQuant = (int)objTemp;
                     *  }
                     *  if (AllreadyAvaliableQuant > 0)
                     *  {
                     *      int newQuan=AllreadyAvaliableQuant+returnQuantity;
                     *
                     *      string stockProductUpdateQuery = @"update _stock_product set quantity=" + newQuan + " where pid=" + ProdID + " and stock_id =" + stockIDRt + "";
                     *
                     *      salesReturnObject.ExecuteNonQuery(stockProductUpdateQuery, con);
                     *  }
                     *  else
                     *  {
                     *      string stockProductInsertQuery = @"insert _stock_product  (pid,quantity,stock_id) values(" + ProdID + "," + returnQuantity + "," + stockIDRt + ") ";
                     *      salesReturnObject.ExecuteNonQuery(stockProductInsertQuery, con);
                     *  }
                     *  //returnQuantity;ProdID
                     *   // cmbSupplier.SelectedIndex
                     * }
                     #endregion
                     *
                     */
                    #endregion

                    int stockIDD = Convert.ToInt32(cmbSupplier.SelectedValue.ToString());
                    updateStock(ProdID, stockIDD, returnQuantity);

                    txScope.Complete();//transaction commit
                    MetroMessageBox.Show(this, "Transaction Complete ", "Process completed", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                }
            }
            catch (Exception ex)
            {
                MetroMessageBox.Show(this, "System Error " + ex.Message, "System Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
Beispiel #2
0
        private void updateStock(int productID, int StockID, int Quanityt)
        {
            int existenceStockTable        = 0;
            int existenceStockProductTable = 0;

            BusinessObjects.SalesReturn salesReturnObject = new BusinessObjects.SalesReturn();
            string queryExistenseStockTable = "select count (*) from _Stokc where stock_id=" + StockID + " ";
            object objTemp = BusinessObjects.SalesReturn.getScalar(con, queryExistenseStockTable);

            if (objTemp != null)
            {
                existenceStockTable = (int)objTemp;
            }
            if (existenceStockTable > 0)
            {
                string queryExistenseStockProductTable = @"select count (*) from _stock_product where stock_id=" + StockID + " and pid=" + productID + " ";
                object objTemp2 = BusinessObjects.SalesReturn.getScalar(con, queryExistenseStockProductTable);
                if (objTemp2 != null)
                {
                    existenceStockProductTable = (int)objTemp;
                }

                if (existenceStockProductTable > 0)
                {
                    //update the stoc_product table
                    string updateQuerySP1 = @"update _stock_product set quantity=quantity+" + Quanityt + " where pid=" +
                                            ProdID + " and stock_id =" + StockID + "";
                    salesReturnObject.ExecuteNonQuery(updateQuerySP1, con);
                }
                else
                {
                    //add to the stockProduct table
                    decimal cost = 0;
                    string  selectQuerySotkcProductDetails = @"select cost from purchase_product where stock_id=" +
                                                             StockID + " and pid=" + productID + " ";
                    object objTemp3 = BusinessObjects.SalesReturn.getScalar(con, selectQuerySotkcProductDetails);
                    if (objTemp3 != null)
                    {
                        cost = (decimal)objTemp3;
                    }

                    string stockProductInsert = @"insert _stock_product  (pid,quantity,stock_id,price) values(" + ProdID
                                                + "," + Quanityt + "," + StockID + ",'" + cost + "') ";
                    salesReturnObject.ExecuteNonQuery(stockProductInsert, con);
                }
            }
            else
            {
                //add to stock
                Purchase pm = new Purchase();
                pm = pm.GetPurchaseAndPProdBySID(con, StockID);

                string queryGetSupID = @"select Id from Supplier where Name='" + pm.sup_name + "'";
                int    supID         = 1;
                object objTemp4      = BusinessObjects.SalesReturn.getScalar(con, queryGetSupID);
                if (objTemp4 != null)
                {
                    supID = (int)objTemp4;
                }
                string o           = "(return)";
                string insertStock = @"insert _Stokc  (sup_name,s_date,invoice_no,Sup_id) values('" + pm.sup_name + o
                                     + "','" + pm.s_date.ToString("G", DateTimeFormatInfo.InvariantInfo) + "','" + pm.invoice_no
                                     + "'," + supID + ") ";
                salesReturnObject.ExecuteNonQuery(insertStock, con);
                //getting last inserted ID
                string queryx         = @"select IDENT_CURRENT('_Stokc')";
                int    lastInsertedID = Convert.ToInt32(Sales_BM.getScalar(con, queryx));
                //adding stock product
                stock_product sp = new stock_product();
                sp.price    = pm.StockProductList.Find(prod => prod.pid == productID).cost;
                sp.quantity = Quanityt;
                sp.stock_id = lastInsertedID;
                sp.pid      = ProdID;
                sp.Add(con);
            }
        }