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