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