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