public int GetAvailableQty(string itemno,string newcon="P",bool usenewcon=false) { var ta = new DsInventoryTableAdapters.STOCKMASTERTableAdapter { Connection = { ConnectionString = usenewcon ?newcon: _constr } }; return Convert.ToInt32(ta.GetAvailableQty(itemno)); }
public void UpdateSpriceByItemno(string itemno, decimal sprice, ref SqlConnection connection,ref SqlTransaction transaction) { var ta = new DsInventoryTableAdapters.STOCKMASTERTableAdapter { Connection = connection, Transaction = transaction }; ta.UpdateSpriceByItemno(sprice, itemno); }
public int GetAvailableQty(string itemno) { var ta = new DsInventoryTableAdapters.STOCKMASTERTableAdapter {Connection = {ConnectionString = _constr}}; return ta.GetSaleStockByItemNo(itemno, Utils.Company) ?? 0; }
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 UpdateSprice(decimal sprice,int stockid) { var ta = new DsInventoryTableAdapters.STOCKMASTERTableAdapter {Connection = {ConnectionString = _constr}}; ta.UpdateSPRICE(sprice, stockid); }
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 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 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 decimal GetSalesPriceByItemno(string itemno) { var ta = new DsInventoryTableAdapters.STOCKMASTERTableAdapter { Connection = { ConnectionString = _constr } }; return ta.GetSalesPriceByItemno(itemno, Utils.Company)??0; }
public DsInventory.STOCKMASTERDataTable GetStockItemBySprice(string itemno,string storetype,decimal sprice) { var ta = new DsInventoryTableAdapters.STOCKMASTERTableAdapter() { Connection = { ConnectionString = _constr } }; var ds = new DsInventory().STOCKMASTER; ta.FillByItemnoBySprice(ds, itemno,sprice,storetype, Utils.Company); return ds; }
public decimal SumSalePrice(bool useconstr = false, string newconstr = "p") { var ta = new DsInventoryTableAdapters.STOCKMASTERTableAdapter { Connection = { ConnectionString = useconstr ? newconstr : _constr } }; return (decimal) (ta.SumSPrice(Utils.Company) ?? 0); }
public string UpdateStockDataSet(DataTable dt) { SqlTransaction tran = null; string retval ="0"; try { var cn = new SqlConnection(_constr); cn.Open(); //int autosed = int.Parse(Utils.Company)*1000000; //var str = " DBCC CHECKIDENT (STOCKMASTER , RESEED, + autosed +) "; //var cmd = new SqlCommand(str, cn); //cmd.ExecuteNonQuery(); tran = cn.BeginTransaction(IsolationLevel.ReadUncommitted); var ta = new DsInventoryTableAdapters.STOCKMASTERTableAdapter(); ta.AssignConnection(cn, tran); if (ta.Update((DsInventory.STOCKMASTERDataTable) dt) > 0) retval = "1"; tran.Commit(); } catch (Exception ex) { if (tran != null) tran.Rollback(); retval = ex.ToString(); } return retval; }
public void UpdateStockQtyByStockid( int qty,int stockid) { var ta = new DsInventoryTableAdapters.STOCKMASTERTableAdapter{ Connection = { ConnectionString = _constr } }; ta.UpdateStockQtyByStockid(qty, stockid); }
public DsInventory.STOCKMASTERDataTable GetStockTableWithItemCodes(DsInventory.SALESINVDETAILDataTable salesinvdetail) { var dsItemCodes = new DsInventory().STOCKMASTER; var ta = new DsInventoryTableAdapters.STOCKMASTERTableAdapter { Connection = { ConnectionString = _constr } }; string itemcodes = string.Empty; foreach (DsInventory.SALESINVDETAILRow drts in salesinvdetail.Rows) { if (drts.ISRETURN) { itemcodes += drts.ITEMNO + ","; } } ta.FillByItemcodes(dsItemCodes, itemcodes); return dsItemCodes; }
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 UpdateQty(int stockid, int qty) { try { var ta = new DsInventoryTableAdapters.STOCKMASTERTableAdapter { Connection = {ConnectionString = _constr} }; ta.UpdateSaleQtyByStockid(qty, stockid); } catch (Exception ex) { throw ex; } }
public DsInventory.STOCKMASTERDataTable GetNegativeStock() { var ta = new DsInventoryTableAdapters.STOCKMASTERTableAdapter {Connection = {ConnectionString = _constr}}; var ds = new DsInventory().STOCKMASTER; ta.FillByNegativeStock(ds, Utils.Company); return ds; }
public void UpdateStockQty(ref SqlConnection cn, ref SqlTransaction tran, int stockid, int qty,DataRow d) { string wh = Utils.SalesStoreCode; const string storetype = "S"; var ta = new DsInventoryTableAdapters.STOCKMASTERTableAdapter { Connection = cn, Transaction = tran }; if(stockid==0) { ta.Insert(d["ITEMNO"].ToString(), d["BARCODE"].ToString(), int.Parse(d["QTY"].ToString()), decimal.Parse(d["SPRICE"].ToString()), decimal.Parse(d["CPRICE"].ToString()), !string.IsNullOrEmpty(d["EXPDATE"].ToString()) ? DateTime.Parse(d["EXPDATE"].ToString()) : (DateTime?)null, storetype, null, null, null,false, wh, Utils.Company,int.Parse(d["QTY"].ToString()),null); } else { ta.UpdateQtyByStockID(qty, stockid); } }
public int GetAvailableQty(int stockid) { var ta = new DsInventoryTableAdapters.STOCKMASTERTableAdapter { Connection = { ConnectionString = _constr } }; return Convert.ToInt32(ta.AvailableQty(stockid)); }
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 int GetStockid(string itemno) { var ta = new DsInventoryTableAdapters.STOCKMASTERTableAdapter { Connection = { ConnectionString = _constr } }; return Convert.ToInt32(ta.GetStockIdofItem(itemno)); }
public string GetExpiryDt(string itemno) { var ta = new DsInventoryTableAdapters.STOCKMASTERTableAdapter { Connection = { ConnectionString = _constr } }; return ta.GetExpiryDt(itemno, Utils.Company).ToString(); }
public DsInventory.STOCKMASTERDataTable GetStockMasterTabeDataTable() { var ta = new DsInventoryTableAdapters.STOCKMASTERTableAdapter { Connection = { ConnectionString = _constr } }; return ta.GetData(); }
public int GetAvailableSQty(int itemid) { var ta = new DsInventoryTableAdapters.STOCKMASTERTableAdapter { Connection = { ConnectionString = _constr } }; return ta.GetAvailableSQty(itemid) ?? 0; }
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); } } } } } }