public string DoSalesItemDelete(DsInventory.SALESINVDETAILRow dr) { SqlTransaction tran = null; var retval = "0"; try { var cn = new SqlConnection(_constr); cn.Open(); tran = cn.BeginTransaction(IsolationLevel.ReadUncommitted); var dt = new DsInventory.SALESINVDETAIL_DELDataTable(); var tastock = new DsInventoryTableAdapters.STOCKMASTERTableAdapter(); var tasalede = new DsInventoryTableAdapters.SALESINVDETAIL_DELTableAdapter(); // var tasales = new DsInventoryTableAdapters.SALESINVDETAILTableAdapter(); tastock.AssignConnection(cn, tran); tasalede.AssignConnection(cn, tran); // tasales.AssignConnection(cn,tran); var dr1 = (DsInventory.SALESINVDETAIL_DELRow)dt.NewRow(); dr1.SINVNO = dr.SINVNO; dr1.ITEMNO = dr.ITEMNO; dr1.BARCODE = dr.BARCODE; dr1.QTY = dr.QTY; dr1.INVRATE = dr.INVRATE; dr1.SPRICE = dr.SPRICE; dr1.CPRICE = dr.CPRICE; if (!dr.IsEXPDATENull()) dr1.EXPDATE = dr.EXPDATE; dr1.STOCKID = dr.STOCKID; dr1.STOCK = dr.STOCK; dr1.DOCTORID = dr.DOCTORID; dr1.ITEMNAME = dr.ITEMNAME; dr1.COMPANY = Utils.Company; dr1.TRANDATE = DateTime.Now; dr1.USERID = Utils.Userid; dr1.ISRETURN = dr.ISRETURN; dt.Rows.Add(dr1); //tasalede.Insert(dr.SINVNO, dr.ITEMNO, dr.BARCODE, dr.QTY, dr.INVRATE, dr.SPRICE, dr.CPRICE, dr.EXPDATE, // dr.STOCKID, dr.STOCK, dr.DOCTORID, dr.ITEMNAME, Utils.Company, DateTime.Now, Utils.Userid,dr.ISRETURN); tasalede.Update(dr1); if (dr.STOCKID > 0) { if(dr.ISRETURN) tastock.UpdateSaleQtyByStockid((-1* dr.QTY), dr.STOCKID); else tastock.UpdateSaleQtyByStockid(dr.QTY, dr.STOCKID); } // tasales.Update(salesinvdetail); tran.Commit(); retval = "1"; } catch (Exception ex) { retval = ex.ToString(); tran.Rollback(); } return retval; }
public string UpdateQtyAndSalesTable(int stockid,int qty,DsInventory.SALESINVDETAILDataTable salesinvdetail) { string retval = "0"; SqlTransaction tran = null; try { var cn = new SqlConnection(_constr); cn.Open(); tran = cn.BeginTransaction(IsolationLevel.ReadUncommitted); var ds = new DsInventory(); var tasalDetail = new DsInventoryTableAdapters.SALESINVDETAILTableAdapter(); var tastock = new DsInventoryTableAdapters.STOCKMASTERTableAdapter(); tasalDetail.AssignConnection(cn, tran); tastock.AssignConnection(cn, tran); tastock.UpdateSaleQtyByStockid(qty, stockid); tasalDetail.Update(salesinvdetail); tran.Commit(); retval = "1"; } catch (Exception ex) { retval = ex.ToString(); tran.Rollback(); } return retval; }
public void DoSalesItemDelete(DsInventory.SALESINVDETAILRow dRow, ref SqlConnection cn, ref SqlTransaction tran) { // tran = cn.BeginTransaction(IsolationLevel.ReadUncommitted); var dt = new DsInventory.SALESINVDETAIL_DELDataTable(); var tastock = new DsInventoryTableAdapters.STOCKMASTERTableAdapter(); var tasalede = new DsInventoryTableAdapters.SALESINVDETAIL_DELTableAdapter(); tastock.AssignConnection(cn, tran); tasalede.AssignConnection(cn, tran); var dr = (DsInventory.SALESINVDETAIL_DELRow) dt.NewRow(); dr.SINVNO = dRow.SINVNO; dr.ITEMNO = dRow.ITEMNO; dr.BARCODE = dRow.BARCODE; dr.QTY = dRow.QTY; dr.INVRATE = dRow.INVRATE; dr.SPRICE = dRow.SPRICE; dr.CPRICE = dRow.CPRICE; if(!dRow.IsEXPDATENull()) dr.EXPDATE = dRow.EXPDATE; dr.STOCKID = dRow.STOCKID; dr.STOCK = dRow.STOCK; dr.DOCTORID = dRow.DOCTORID; dr.ITEMNAME = dRow.ITEMNAME; dr.COMPANY = Utils.Company; dr.TRANDATE = DateTime.Now; dr.USERID = Utils.Userid; tasalede.Update(dr); if (dr.STOCKID > 0) { if (dr.ISRETURN) tastock.UpdateSaleQtyByStockid(-dr.QTY, dr.STOCKID); else tastock.UpdateSaleQtyByStockid(dr.QTY, dr.STOCKID); } //if (dRow.STOCKID > 0) //{ // // tastock.UpdateQtyByStockID(dRow.QTY, dRow.STOCKID); // tastock.UpdateSaleQtyByStockid(dRow.QTY, dRow.STOCKID); //} }
public void UpdateQty(int stockid, int qty) { try { var ta = new DsInventoryTableAdapters.STOCKMASTERTableAdapter { Connection = {ConnectionString = _constr} }; ta.UpdateSaleQtyByStockid(qty, stockid); } catch (Exception ex) { throw ex; } }
public void AddNewItemItemInStock(ref SqlConnection cn, ref SqlTransaction tran, DataRow d,DsInventory.STOCKMASTERDataTable dsm) { string wh = Utils.SalesStoreCode; const string storetype = "S"; // var ds = new DsInventory().STOCKMASTER; var ta = new DsInventoryTableAdapters.STOCKMASTERTableAdapter { Connection = { ConnectionString = _constr } }; var taup = new DsInventoryTableAdapters.STOCKMASTERTableAdapter {Connection = cn, Transaction = tran}; // ta.FillByItemNoAll(ds, d["ITEMNO"].ToString(), Utils.Company); DataRow[] ds = dsm.Select( "ITEMNO='" + d["ITEMNO"].ToString() + "'"); int qty = int.Parse(d["qty"].ToString()) * -1; // if (ds.Rows.Count > 0) //{ foreach (DsInventory.STOCKMASTERRow dr in ds) { if (dr.SPRICE == decimal.Parse(d["INVRATE"].ToString())) { taup.UpdateSaleQtyByStockid(qty, dr.STOCKID); taup.UpdateQtyByStockID(qty, dr.STOCKID); return; } } //} var cprice = new ItemMaster().GetItemCostPrice(d["itemno"].ToString()); var sprice = decimal.Parse(d["INVRATE"].ToString()); var sdisc = new ItemMaster().GetPurchaseDist(d["itemno"].ToString()); if (cprice == 0) { sprice = decimal.Parse(d["INVRATE"].ToString()); cprice = sprice - sprice * sdisc / 100; } if(!string.IsNullOrEmpty(d["EXPDATE"].ToString())) taup.Insert(d["ITEMNO"].ToString(), d["BARCODE"].ToString(), qty, sprice, cprice, DateTime.Parse(d["EXPDATE"].ToString()), storetype, null, null, null, false, wh, Utils.Company, qty, null); else taup.Insert(d["ITEMNO"].ToString(), d["BARCODE"].ToString(), qty, sprice, cprice, null, storetype, null, null, null, false, wh, Utils.Company, qty, null); }
public void AddNewItemwithNegativeQty(DataRow d) { string wh = Utils.SalesStoreCode; const string storetype = "S"; var ds = new DsInventory().STOCKMASTER; var ta = new DsInventoryTableAdapters.STOCKMASTERTableAdapter { Connection = { ConnectionString = _constr } }; //ta.FillByItemNo(ds, d["ITEMNO"].ToString(), "S", Utils.Company); ta.FillByItemNoAll(ds, d["ITEMNO"].ToString(), Utils.Company); if (ds.Rows.Count > 0) { foreach (DsInventory.STOCKMASTERRow dr in ds.Rows) { if (dr.SPRICE == decimal.Parse(d["SPRICE"].ToString())) { ta.UpdateSaleQtyByStockid(-int.Parse(d["DQNTY"].ToString()), dr.STOCKID); return; } } } ta.Insert(d["ITEMNO"].ToString(), d["BARCODE"].ToString(), 0, decimal.Parse(d["SPRICE"].ToString()), decimal.Parse(d["CPRICE"].ToString()), null, storetype, null, null, null, false, wh, Utils.Company, -int.Parse(d["DQNTY"].ToString()),null); }
public static void UpdateQtyInStockDataSet(ref SqlConnection connection, ref SqlTransaction transaction,DataTable dt, bool isbonusqty) { var ta = new DsInventoryTableAdapters.STOCKMASTERTableAdapter(); ta.Connection = connection; ta.Transaction = transaction; foreach (DataRow d in dt.Rows) { if (d.RowState != DataRowState.Deleted) { int bonusqty = 0; if (isbonusqty) { if (!string.IsNullOrEmpty(d["BONUSQTY"].ToString())) { bonusqty = int.Parse(d["BONUSQTY"].ToString()); } } int QTY = int.Parse(d["QTY"].ToString()) + bonusqty; ta.UpdateQtyByStockID(-QTY, int.Parse(d["STOCKID"].ToString())); ta.UpdateSaleQtyByStockid(-QTY, int.Parse(d["STOCKID"].ToString())); } } }
public void CreateStock(ref SqlConnection connection, ref SqlTransaction transaction, DataTable dataTable, string wh, string storetype, bool isbonusqty, bool isreturn) { var ta = new DsInventoryTableAdapters.STOCKMASTERTableAdapter(); var ds = new DsInventory().STOCKMASTER; ta.Connection = connection; ta.Transaction = transaction; foreach (DataRow d in dataTable.Rows) { if (d.RowState != DataRowState.Deleted) { int qty = 0; if (isbonusqty) { if (!string.IsNullOrEmpty(d["BONUSQTY"].ToString())) { if (!string.IsNullOrEmpty(d["QTY"].ToString())) { qty = int.Parse(d["QTY"].ToString()) + int.Parse(d["BONUSQTY"].ToString()); } else { qty = int.Parse(d["BONUSQTY"].ToString()); } } else { qty = int.Parse(d["QTY"].ToString()); } } else { qty = int.Parse(d["QTY"].ToString()); } int cnt = 0; // var avgcost = GetAvgCprice(d["ITEMNO"].ToString()); int negtivestock = ta.FillByItemcodeWithNegativeStock(ds, d["ITEMNO"].ToString(), storetype, wh, Utils.Company); if (negtivestock == 0) { cnt = !string.IsNullOrEmpty(d["EXPDATE"].ToString()) ? ta.FillByStockIDForUpdate(ds, d["ITEMNO"].ToString(), DateTime.Parse(d["EXPDATE"].ToString()), storetype, wh, Utils.Company,decimal.Parse(d["SPRICE"].ToString())) : ta.FillByStockIDForUpdateNullDate(ds, d["ITEMNO"].ToString(), storetype, wh, Utils.Company,decimal.Parse(d["SPRICE"].ToString())); } else { cnt = negtivestock; } if (isreturn) { qty = -1 * qty; } if (cnt > 0) { var d1 = ds.Rows[0]; bool isneg = false; foreach (DsInventory.STOCKMASTERRow d2 in ds.Rows) { if (d2.QTY < 0) { isneg = true; d1 = d2; } if (d2.QTY > 0 && !isneg) d1 = d2; } ta.UpdateQtyByStockID(qty, int.Parse(d1["STOCKID"].ToString())); if(negtivestock > 0) { ta.UpdateSPRICE(decimal.Parse(d["SPRICE"].ToString()), int.Parse(d1["STOCKID"].ToString())); } ta.UpdateSaleQtyByStockid(qty, int.Parse(d1["STOCKID"].ToString())); int qty2 = 0; decimal cp1 = 0; foreach (DataRow costrow in ds.Rows) { qty2 += int.Parse(costrow["QTY"].ToString()); if (!String.IsNullOrEmpty(costrow["CPRICE"].ToString())) cp1 = decimal.Parse(costrow["CPRICE"].ToString()); } if (qty2 < 0) qty2 *= -1; if ((qty + qty2) > 0) { var newavgcp = ((qty*decimal.Parse(d["CPRICE"].ToString())) + (qty2*cp1))/(qty + qty2); foreach (DataRow costrow in ds.Rows) { ta.UpdateCPRICE(newavgcp, int.Parse(costrow["STOCKID"].ToString())); } } } else { if (!isreturn) { if (string.IsNullOrEmpty(d["EXPDATE"].ToString())) { ta.Insert(d["ITEMNO"].ToString(), d["BARCODE"].ToString(), qty, decimal.Parse(d["SPRICE"].ToString()), decimal.Parse(d["CPRICE"].ToString()), null, storetype, null, null, null, false, wh,Utils.Company, qty,null); } else { ta.Insert(d["ITEMNO"].ToString(), d["BARCODE"].ToString(), qty, decimal.Parse(d["SPRICE"].ToString()), decimal.Parse(d["CPRICE"].ToString()), DateTime.Parse(d["EXPDATE"].ToString()), storetype, null, null, null, false, wh, Utils.Company, qty, null); } } } } } }