//////////////////
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);
}
//-- function --
string ReceiveItemFunc(sapi.db db, Dictionary <string, string> vals, string mySt)
{
Dictionary <string, string> aVal = new Dictionary <string, string>();
string re = "";
string screen = "tblReceiveNew";
string screenItem = "tblReceiveItemNew";
string whid = "0";
sapi.sapi cls = new sapi.sapi();
db.beginTran();
re = cls.saveRecord(screen, vals, db, aVal, ignoreROF: true);
var str = JsonConvert.DeserializeObject <dynamic>(re);
if (str.tbl != null)
{
if (str.tbl[0].status == "ok")
{
whid = vals["rece_WarehouseID".ToLower()];
clsGlobal clsglobal = new clsGlobal();
string hid = (string)str.tbl[0].msg;
aVal.Clear();
aVal.Add("reit_receiveid", hid);
//foreach (var st in Request.Form["N"].ToString().Split(','))
foreach (var st in mySt.ToString().Split(','))
{
{
string re2 = "";
Dictionary <string, string> v = cls.getItemVals(screenItem, vals, db, st);
v.Add("rece_Date".ToLower(), vals["rece_Date".ToLower()]);
double quit_Qty = 0;
double quit_Price = 0;
if (v.ContainsKey("reit_Qty".ToLower()))
{
quit_Qty = db.cNum(v["reit_Qty".ToLower()]);
}
if (v.ContainsKey("reit_Price".ToLower()))
{
quit_Price = db.cNum(v["reit_Price".ToLower()]);
}
if (v.ContainsKey("reit_Total".ToLower()))
{
v["reit_Total".ToLower()] = (quit_Qty * quit_Price).ToString();
}
else
{
v.Add("reit_Total".ToLower(), (quit_Qty * quit_Price).ToString());
}
re2 = cls.saveRecord(screenItem, v, db, aVal, st);
str = JsonConvert.DeserializeObject <dynamic>(re2);
if (str.tbl != null)
{
if (str.tbl[0].status != "ok")
{
db.rollback();
return(re2);
}
else
{
clsglobal.receiveItem(db, v, whid);
}
}
}
}
clsglobal.ReceiveTotal(hid, db);
}
}
db.commit();
return(re);
}
public static async Task <string> UploadInvoice(sapi.db db, invoiceDetailListDTO newDTOs, string token)
{
Dictionary <string, string> vals = new Dictionary <string, string>();
var users = Helper.GetUserProfile(token);
if (users == null)
{
throw new HttpException((int)HttpStatusCode.Unauthorized, "Invalid Token !");
}
if (newDTOs.results.Count <= 0)
{
throw new HttpException((int)HttpStatusCode.NotFound, "No Item(s) !");
}
DataTable tblSetting = db.readData("Select * from sys_Setting");
DataTable tblSalesman = db.readData("Select * from tblSalesman Where salm_SalesmanID = " + db.cNum(users.user_SalesmanID.ToString()));
if (tblSalesman.Rows.Count <= 0)
{
if (tblSetting.Rows[0]["sett_useSalesman"].ToString().ToLower() == "y")
{
throw new HttpException((int)HttpStatusCode.NotFound, "Salesman Not Found !");
}
}
else
{
var ttt = tblSetting.Rows[0]["sett_useProvince"].ToString().ToLower();
if (tblSetting.Rows[0]["sett_useProvince"].ToString().ToLower() == "y")
{
if (String.IsNullOrEmpty(tblSalesman.Rows[0]["salm_Province"].ToString()))
{
throw new HttpException((int)HttpStatusCode.NotFound, "Salesman's province Not Found !");
}
}
}
DataTable tblSett = db.readData("select * from sys_setting");
if (tblSett.Rows.Count <= 0)
{
throw new HttpException((int)HttpStatusCode.NotFound, "Warehouse not Found !");
}
string invo_WarehouseID = "";
foreach (DataRow row in tblSett.Rows)
{
invo_WarehouseID = row["sett_WarehouseID"].ToString();
if (string.IsNullOrEmpty(invo_WarehouseID))
{
throw new HttpException((int)HttpStatusCode.NotFound, "Warehouse not Found !");
}
}
HttpContext.Current.Session["userid"] = users.id;
HttpContext.Current.Session["user"] = users.userName;
string re = "";
string re2 = "";
string hid = "";
string errStr = "";
sapi.sapi cls = new sapi.sapi();
string screenItem = "tblInvoiceItemNew";
string screen = "tblInvoiceNew";
Dictionary <string, string> aVal = new Dictionary <string, string>();
DataTable tblResult = new DataTable();
tblResult.Rows.Add();
tblResult.Columns.Add("status");
tblResult.Columns.Add("msg");
if (db.connect())
{
string salm_SalesmanID = "";
string salm_Province = "";
if (tblSalesman.Rows.Count > 0)
{
salm_SalesmanID = tblSalesman.Rows[0]["salm_SalesmanID"].ToString();
salm_Province = tblSalesman.Rows[0]["salm_Province"].ToString();
}
string invo_PriceListID = newDTOs.invo_PriceListID.ToString();
DataTable tblCust = db.readData("Select * from tblCustomer Where cust_CustomerID = " + db.cNum(newDTOs.invo_CustomerID.ToString()));
foreach (DataRow row in tblCust.Rows)
{
invo_PriceListID = row["cust_PriceListID"].ToString();
}
vals.Add("invo_Date".ToLower(), newDTOs.invo_Date?.ToString("dd/MM/yyyy"));
vals.Add("invo_Province".ToLower(), salm_Province);
vals.Add("invo_Company".ToLower(), newDTOs.invo_Company);
vals.Add("invo_CustomerID".ToLower(), newDTOs.invo_CustomerID.ToString());
vals.Add("invo_PriceListID".ToLower(), invo_PriceListID);
vals.Add("invo_WarehouseID".ToLower(), invo_WarehouseID);
vals.Add("invo_SalesmanID".ToLower(), salm_SalesmanID);
vals.Add("invo_Status".ToLower(), "New");
string wh = "";
string mbid = "";
DataTable tblMB = db.readData("select * from tblMobileSale " +
" inner join tblMobileSaleItem on msit_MobileSaleID = mbsl_MobileSaleID and msit_Deleted is null " +
" where mbsl_CheckIn is null and msit_UserID = " + users.id);
foreach (DataRow rowMB in tblMB.Rows)
{
wh = rowMB["mbsl_warehouseID"].ToString();
mbid = rowMB["mbsl_MobileSaleID"].ToString();
vals["invo_WarehouseID".ToLower()] = wh;
aVal.Add("invo_MobileSaleID", mbid);
}
foreach (var item in newDTOs.results)
{
decimal?qty = 0;
foreach (var item1 in newDTOs.results)
{
if (item.init_ItemID == item1.init_ItemID)
{
qty = qty + item1.init_Qty;
}
}
errStr += stockVerification(db, cls, item.init_ItemID.ToString(), (double)qty, newDTOs.invo_WarehouseID.ToString());
}
if (errStr.Length > 0)
{
db.close();
tblResult.Rows[0]["status"] = "error";
tblResult.Rows[0]["msg"] = errStr;
return(db.tblToJson(tblResult));
}
bool isCredit = false;
if (vals.ContainsKey("isCredit".ToLower()))
{
isCredit = true;
}
db.beginTran();
if (!vals.ContainsKey("invo_invoiceid"))
{
aVal.Add("invo_WorkflowID", "6");
aVal.Add("invo_WorkflowItemID", "12");
}
if (vals.ContainsKey("invo_exrate"))
{
aVal.Add("invo_ExRate", vals["invo_exrate".ToLower()]);
vals.Remove("invo_exrate".ToLower());
}
if (vals.ContainsKey("invo_cashin"))
{
aVal.Add("invo_CashIn", vals["invo_cashin".ToLower()]);
vals.Remove("invo_CashIn".ToLower());
}
if (vals.ContainsKey("invo_cashin2"))
{
aVal.Add("invo_CashIn2", vals["invo_cashin2".ToLower()]);
vals.Remove("invo_cashin2".ToLower());
}
if (vals.ContainsKey("invo_status"))
{
vals["invo_status"] = "completed";
}
if (vals.ContainsKey("invo_MobileSaleID".ToLower()))
{
aVal.Add("invo_MobileSaleID", vals["invo_MobileSaleID".ToLower()]);
vals.Remove("invo_MobileSaleID".ToLower());
}
if (isCredit)
{
aVal["invo_CashIn"] = "0";
aVal["invo_CashIn2"] = "0";
}
if (!vals.ContainsKey("invo_Date".ToLower()))
{
vals.Add("invo_Date".ToLower(), db.getDate(DateTime.UtcNow.AddHours(7).ToString("yyyy-MM-dd HH:mm"), 1));
}
else
{
vals.Remove("invo_Date".ToLower());
vals.Add("invo_Date".ToLower(), db.getDate(DateTime.UtcNow.AddHours(7).ToString("yyyy-MM-dd HH:mm"), 1));
}
re = cls.saveRecord("tblInvoiceNew", vals, db, aVals: aVal, ignoreROF: true);
var str = JsonConvert.DeserializeObject <dynamic>(re);
if (str.tbl != null)
{
if (str.tbl[0].status == "ok")
{
hid = (string)str.tbl[0].msg;
if (!vals.ContainsKey("invo_invoiceid"))
{
if (vals.ContainsKey("invo_customerid"))
{
if (!string.IsNullOrEmpty(vals["invo_customerid"]))
{
var tmp = db.execData("Update tblCustomer Set cust_Type='Customer',cust_LastTransDate=GETDATE() Where /*cust_Type='Lead' and*/ cust_CustomerID=" +
vals["invo_customerid"].ToString());
if (tmp != "ok")
{
db.rollback();
throw new HttpException((int)HttpStatusCode.Unauthorized, tmp);
}
}
}
}
foreach (var item in newDTOs.results)
{
Dictionary <string, string> iVals = new Dictionary <string, string>();
aVal.Clear();
aVal.Add("init_InvoiceID", hid);
iVals.Add("init_ItemID".ToLower(), item.init_ItemID.ToString());
iVals.Add("init_Description".ToLower(), item.init_Description);
iVals.Add("init_Qty".ToLower(), item.init_Qty.ToString());
iVals.Add("init_Price".ToLower(), item.init_Price.ToString());
iVals.Add("init_Total".ToLower(), (item.init_Price * item.init_Qty).ToString());
iVals.Add("init_WarehouseID".ToLower(), newDTOs.invo_WarehouseID.ToString());
aVal.Add("init_RPrice", item.init_Price.ToString());
aVal.Add("init_BQty", item.init_Price.ToString());
re = stockDeduction(db, item.init_ItemID.ToString(), newDTOs.invo_WarehouseID.ToString(), (double)item.init_Qty);
if (re == "")
{
re = cls.saveRecord("tblInvoiceItemNew", iVals, db, aVal, ignoreROF: true);
str = JsonConvert.DeserializeObject <dynamic>(re);
if (str.tbl != null)
{
if (str.tbl[0].status != "ok")
{
db.rollback();
throw new HttpException((int)HttpStatusCode.Unauthorized, "Unable To Save Line Item !");
}
}
}
else
{
db.rollback();
throw new HttpException((int)HttpStatusCode.Unauthorized, "Erro Validate Stock !");
}
}
invoiceTotal(hid, db);
}
}
db.commit();
re = hid;
}
return(re);
}
string uploadSO(sapi.db db, Dictionary <string, string> vals, string mySt)
{
string token = Request.Headers.GetValues("token").First();
UserProfileViewDTO userProfile = Helper.Helper.GetUserProfile(token);
HttpContext.Current.Session["userid"] = userProfile.id;
HttpContext.Current.Session["user"] = userProfile.userName;
string re = "";
string re2 = "";
string hid = "";
sapi.sapi cls = new sapi.sapi();
string screenItem = "tblSaleOrderItemNew";
string screen = "tblSaleOrderNew";
Dictionary <string, string> aVal = new Dictionary <string, string>();
DataTable tblResult = new DataTable();
tblResult.Rows.Add();
tblResult.Columns.Add("status");
tblResult.Columns.Add("msg");
db.beginTran();
if (!vals.ContainsKey("sord_Date".ToLower()))
{
vals.Add("sord_Date".ToLower(), db.getDate(DateTime.UtcNow.AddHours(7).ToString("yyyy-MM-dd HH:mm"), 1));
}
else
{
vals.Remove("sord_Date".ToLower());
vals.Add("sord_Date".ToLower(), db.getDate(DateTime.UtcNow.AddHours(7).ToString("yyyy-MM-dd HH:mm"), 1));
}
if (vals.ContainsKey("invo_daystoexp"))
{
double invo_daystoexp = db.cNum(vals["invo_daystoexp"]);
if (!vals.ContainsKey("sord_Date".ToLower()))
{
vals.Add("sord_EndDate".ToLower(), db.getDate(DateTime.UtcNow.AddDays(invo_daystoexp).AddHours(7).ToString("yyyy-MM-dd HH:mm"), 1));
}
else
{
vals.Remove("sord_EndDate".ToLower());
vals.Add("sord_EndDate".ToLower(), db.getDate(DateTime.UtcNow.AddDays(invo_daystoexp).AddHours(7).ToString("yyyy-MM-dd HH:mm"), 1));
}
}
if (vals.ContainsKey("sord_saleorderid"))
{
if (string.IsNullOrEmpty(vals["sord_saleorderid"]) || vals["sord_saleorderid"] == "0")
{
vals.Remove("sord_saleorderid");
}
}
if (vals.ContainsKey("tbls_tableid"))
{
if (!string.IsNullOrEmpty(vals["tbls_tableid"]))
{
db.execData("Update tblTable Set tbls_Status = 'O' Where tbls_TableID = " + vals["tbls_tableid"]);
aVal.Add("sord_TableID", vals["tbls_tableid"]);
}
}
string sord_assignedto = "";
if (vals.ContainsKey("sord_assignedto".ToLower()))
{
if (!string.IsNullOrEmpty(vals["sord_assignedto".ToLower()]))
{
sord_assignedto = vals["sord_assignedto".ToLower()];
}
}
if (string.IsNullOrEmpty(sord_assignedto))
{
DataTable tmpTbl = db.readData("exec notificationSetup 'SO'");
if (tmpTbl.Rows.Count > 0)
{
sord_assignedto = tmpTbl.Rows[0][0].ToString();
}
}
if (!vals.ContainsKey("sord_assignedto".ToLower()))
{
vals["sord_assignedto"] = sord_assignedto;
}
else
{
vals.Add("sord_assignedto", sord_assignedto);
}
re = cls.saveRecord(screen, vals, db, aVals: aVal, ignoreROF: true);
var str = JsonConvert.DeserializeObject <dynamic>(re);
if (str.tbl != null)
{
if (str.tbl[0].status == "ok")
{
hid = (string)str.tbl[0].msg;
//foreach (var st in Request.Form["N"].ToString().Split(','))
foreach (var st in mySt.ToString().Split(','))
{
aVal.Clear();
aVal.Add("soit_SaleOrderID", hid);
if (string.IsNullOrEmpty(st))
{
continue;
}
Dictionary <string, string> v = cls.getItemVals(screenItem, vals, db, st);
v["soit_total"] = (db.cNum(v["soit_Qty".ToLower()].ToString()) * db.cNum(v["soit_Price".ToLower()].ToString())).ToString();
aVal.Add("soit_ShipQty", "0");
aVal.Add("soit_RemainQty", v["soit_Qty".ToLower()]);
if (vals.ContainsKey("soit_saleorderitemid"))
{
if (string.IsNullOrEmpty(vals["soit_saleorderitemid"]) || vals["soit_saleorderitemid"] == "0")
{
vals.Remove("soit_saleorderitemid");
}
}
re2 = cls.saveRecord(screenItem, v, db, aVal, st, ignoreROF: true);
str = JsonConvert.DeserializeObject <dynamic>(re2);
if (str.tbl != null)
{
if (str.tbl[0].status != "ok")
{
db.rollback();
return(re2);
}
}
}
new clsGlobal().SOTotal(hid, db);
if (!string.IsNullOrEmpty(sord_assignedto))
{
db.execData("insert into [dbo].[sys_notification]" +
"([notf_Name],[notf_objectID],[notf_objectValue],[notf_Module],[notf_UserID],[notf_FromUserID])" +
"values(NULL," +
"1035," + hid + ",'SO'," + sord_assignedto + "," + userProfile.id + ")");
new clsGlobal().notification(userProfile.id.ToString(), "SO", sord_assignedto, db);
}
}
else
{
db.rollback();
return(re);
}
}
db.commit();
return(re);
}
