public static string stockDeduction(sapi.db db, string itemID, string warehouseID, double quit_Qty, string inid_invoicedetailid = "", string init_InvoiceItemID = "", bool add = false, string module = "")
{
DataTable tblResult = new DataTable();
tblResult.Rows.Add();
tblResult.Columns.Add("status");
tblResult.Columns.Add("msg");
string re = "";
// stock Deduction
DataTable tblItem = db.readData("Select * from vItemWarehouse " +
" Where itwh_ItemID = " + db.sqlStr(itemID) +
" and itwh_WarehouseID = " + db.sqlStr(warehouseID));
foreach (DataRow rowItem in tblItem.Rows)
{
if (rowItem["item_IsStock"].ToString() == "Y" || rowItem["item_isSet"].ToString() == "Y")
{
DataTable tblItemSet = db.readData("Select sitm_Qty inid_Qty,sitm_ItemUsedID inid_ItemID from tblSubItem Where sitm_Deleted is null and sitm_ItemID=" +
itemID);
if (!string.IsNullOrEmpty(init_InvoiceItemID))
{
tblItemSet = db.readData("Select * from tblInvoiceItemDetail " +
" Where inid_Deleted is null and inid_InvoiceItemID=" +
init_InvoiceItemID);
}
if (tblItemSet.Rows.Count <= 0)
{
tblItemSet.Rows.Add();
}
foreach (DataRow rowItemSet in tblItemSet.Rows)
{
double qty = quit_Qty;
string itemid = itemID;
if (rowItem["item_isSet"].ToString() == "Y")
{
//qty = qty * db.cNum(rowItemSet["sitm_Qty"].ToString());
//itemid = rowItemSet["sitm_ItemUsedID"].ToString();
qty = qty * db.cNum(rowItemSet["inid_Qty"].ToString());
itemid = rowItemSet["inid_ItemID"].ToString();
}
if (rowItem["item_isSet"].ToString() == "Y")
{
if (inid_invoicedetailid != "")
{
db.execData("Insert into tblInvoiceItemDetail(inid_InvoiceItemID,inid_ItemID,inid_Qty) VALUES(" +
db.sqlStr(inid_invoicedetailid) + "," + db.sqlStr(itemid) + "," +
qty +
")");
}
}
string tmp = db.execData("Update tblItemWarehouse Set " +
" itwh_Qty = isNULL(itwh_Qty,0) " + (add ? " + " : " - ") + qty +
" where itwh_WarehouseID = " + db.sqlStr(warehouseID) +
" and itwh_ItemID = " + db.sqlStr(itemid)
);
if (tmp != "ok")
{
db.rollback();
tblResult.Rows[0]["status"] = "error";
tblResult.Rows[0]["msg"] = tmp;
re = ("{\"tbl\":" + db.tblToJson(tblResult) + "}");
return(re);
}
else
{
tmp = db.execData("Declare @tot decimal(18,6) " +
" Declare @tot2 decimal(18,6) " +
" Select " +
" @tot2 = SUM(isNull(itwh_Qty,0)) " +
" from tblItemWarehouse where itwh_ItemID = " + db.sqlStr(itemid) +
" update tblItem set " +
" item_Qty = @tot2 " +
" where item_ItemID = " + db.sqlStr(itemid)
);
if (tmp != "ok")
{
db.rollback();
tblResult.Rows[0]["status"] = "error";
tblResult.Rows[0]["msg"] = tmp;
re = ("{\"tbl\":" + db.tblToJson(tblResult) + "}");
return(re);
}
}
}
}
}
// End of stock Deduction
return(re);
}
//////////////////
public static string stockVerification(sapi.db db, sapi.sapi cls, string itemid, double qty, string whid,
string init_InvoiceItemID = "", string init_InvoiceID = "", string module = "")
{
string re = "";
double exQty = 0;
if (init_InvoiceItemID != "")
{
if (module == "")
{
string hid = db.readData("init_InvoiceID", "Select init_InvoiceID From tblInvoiceItem Where init_InvoiceItemID = " + init_InvoiceItemID);
exQty = db.cNum(db.readData("init_Qty",
"Select SUM(init_Qty) init_Qty From tblInvoiceItem Where init_Deleted is null and init_ItemID = " +
itemid + " and init_InvoiceID=" + hid));
}
if (module == "production")
{
string hid = db.readData("ptip_ProductionID", "Select ptip_ProductionID From tblProductionInput Where ptip_ProductionInputID = " + init_InvoiceItemID);
exQty = db.cNum(db.readData("ptip_Qty",
"Select SUM(ptip_Qty) ptip_Qty From tblProductionInput Where ptip_Deleted is null and ptip_ItemID = " +
itemid + " and ptip_ProductionID=" + hid));
}
}
if (init_InvoiceID != "")
{
string hid = init_InvoiceID;
if (module == "")
{
exQty = db.cNum(db.readData("init_Qty",
"Select SUM(init_Qty) init_Qty From tblInvoiceItem Where init_Deleted is null and init_ItemID = " +
itemid + " and init_InvoiceID=" + hid));
}
if (module == "production")
{
exQty = db.cNum(db.readData("ptip_Qty",
"Select SUM(ptip_Qty) ptip_Qty From tblProductionInput Where ptip_Deleted is null and ptip_ItemID = " +
itemid + " and ptip_ProductionID=" + hid));
}
}
string strErr = "";
DataTable tblItem = db.readData("Select * from vItemWarehouse " +
" Where itwh_ItemID = " + db.sqlStr(itemid) +
" and itwh_WarehouseID = " + db.sqlStr(whid));
foreach (DataRow rowItem in tblItem.Rows)
{
qty = qty - exQty;
if (rowItem["item_isSet"].ToString() == "Y")
{
DataTable tblItemSet = db.readData("Select * from vSubItem " +
" Where sitm_Deleted is null and sitm_ItemID=" + itemid);
string prestrErr = "";
foreach (DataRow rowItemSet in tblItemSet.Rows)
{
double initQty = 0;
if (rowItemSet["item_IsStock"].ToString() == "Y")
{
string ttt = "Select itwh_Qty from vItemWarehouse " +
" Where itwh_ItemID = " + db.sqlStr(rowItemSet["item_itemID"].ToString()) +
" and itwh_WarehouseID = " + db.sqlStr(whid);
double itemwhQty2 = db.cNum(db.readData("itwh_Qty", "Select itwh_Qty from vItemWarehouse " +
" Where itwh_ItemID = " + db.sqlStr(rowItemSet["item_itemID"].ToString()) +
" and itwh_WarehouseID = " + db.sqlStr(whid)));
initQty = qty * db.cNum(rowItemSet["sitm_Qty"].ToString());
if (initQty > itemwhQty2)
{
prestrErr = prestrErr +
" + " + rowItemSet["item_Name"] + "(" +
initQty + " / " +
itemwhQty2 + ")" +
"<br/>";
}
}
}
if (prestrErr.Length > 0)
{
strErr = strErr + " <h4>- " + rowItem["item_Name"] + " : </h4><br/>" + prestrErr +
"<hr class='thin bg-grayLighter'/>";
}
}
else
{
if (rowItem["item_IsStock"].ToString() == "Y")
{
if (qty > db.cNum(rowItem["itwh_Qty"].ToString()))
{
strErr = strErr + " <h4>- " + rowItem["item_Name"] + "(" +
qty.ToString() + " / " +
db.cNum(rowItem["itwh_Qty"].ToString()) + ")</h3>" + "<hr class='thin bg-grayLighter'/>";
}
}
}
}
return(strErr);
}