//返回SalesBOM
public static List <SalesBom> GetSalesBomByID(string strOcID, int Ver)
{
string strSql = string.Format("Select a.*,b.name as ProductCdesc,b.do_color From oc_OrderBom a With(nolock),it_goods b WHERE a.ProductID=b.id and a.OcID='{0}' And a.Ver={1}", strOcID, Ver);
List <SalesBom> lstBom = new List <SalesBom>();
DataTable dt = SQLHelper.ExecuteSqlReturnDataTable(strSql);
for (int i = 0; i < dt.Rows.Count; i++)
{
SalesBom objModel = new SalesBom();
objModel.OcID = dt.Rows[i]["OcID"].ToString();
objModel.Ver = int.Parse(dt.Rows[i]["Ver"].ToString());
objModel.UpperSeq = dt.Rows[i]["UpperSeq"].ToString();
objModel.Seq = dt.Rows[i]["Seq"].ToString();
objModel.PrimaryKey = dt.Rows[i]["PrimaryKey"].ToString();
objModel.ProductID = dt.Rows[i]["ProductID"].ToString();
objModel.ProductCdesc = dt.Rows[i]["ProductCdesc"].ToString();
objModel.Dosage = int.Parse(dt.Rows[i]["Dosage"].ToString());
objModel.UnitCode = dt.Rows[i]["UnitCode"].ToString();
objModel.Remark = dt.Rows[i]["Remark"].ToString();
objModel.DoColor = dt.Rows[i]["do_color"].ToString();
objModel.ActualToHKQty = string.IsNullOrEmpty(dt.Rows[i]["ActualToHKQty"].ToString()) ? 0 : decimal.Parse(dt.Rows[i]["ActualToHKQty"].ToString());
objModel.ActualToHKDate = dt.Rows[i]["ActualToHKDate"].ToString();
lstBom.Add(objModel);
}
return(lstBom);
}
//返回SalesBOM明細資料
//public static List<SalesBom> GetSalesBomByID(string OcID,int Ver,string UpperSeq,string ProductID)
public static List <SalesBom> GetSalesBomByID(SalesBom model)
{
/*
* string strSql =string.Format(
* @"Select a.OcID, a.Ver,a.UpperSeq, a.Seq, a.PrimaryKey,a.ProductID, b.name as ProductCdesc, a.Dosage, a.UnitCode, a.Remark,
* Isnull(a.ActualToHKQty,0) AS ActualToHKQty,Convert(Varchar(10),a.ActualToHKDate,111) AS ActualToHKDate
* FROM oc_OrderBom a Left Join it_goods b ON a.ProductID=b.id
* Where a.OcID='{0}' and Ver={1} and UpperSeq='{2}' ORDER BY a.Seq",model.OcID,model.Ver,model.UpperSeq);
*/
//2021-03-12改為從geo查出SalesBOM
string strSql = string.Format(
@"SELECT '' AS OcID,'0' AS Ver,'' AS UpperSeq,'' AS Seq,B.primary_key AS PrimaryKey,B.goods_id AS ProductID,C.name AS ProductCdesc,
Convert(int,B.dosage) AS Dosage,B.unit_code AS UnitCode,'' AS Remark,0 AS ActualToHKQty,'' AS ActualToHKDate
FROM {0}it_bom_mostly A
INNER JOIN {0}it_bom B ON A.within_code=B.within_code AND A.id=B.id AND A.exp_id=B.exp_id
INNER JOIN {0}it_goods C ON B.within_code=C.within_code AND B.goods_id=C.id
WHERE A.within_code='0000'", strRemoteDB);
if (!string.IsNullOrEmpty(model.ProductID))
{
strSql += string.Format(" AND A.id Like '{0}%'", model.ProductID);
}
else
{
strSql += " AND 1=0";//返回空數據
}
DataTable dt = SQLHelper.ExecuteSqlReturnDataTable(strSql);
List <SalesBom> lsDetails = new List <SalesBom>();
for (int i = 0; i < dt.Rows.Count; i++)
{
SalesBom mdj = new SalesBom();
DataRow dr = dt.Rows[i];
mdj.OcID = dr["OcID"].ToString();
mdj.Ver = int.Parse(dr["Ver"].ToString());
mdj.UpperSeq = dr["UpperSeq"].ToString();
mdj.Seq = dr["Seq"].ToString();
mdj.PrimaryKey = dr["PrimaryKey"].ToString();
mdj.ProductID = dr["ProductID"].ToString();
mdj.ProductCdesc = dr["ProductCdesc"].ToString();
mdj.Dosage = int.Parse(dr["Dosage"].ToString());
mdj.UnitCode = dr["UnitCode"].ToString();
mdj.Remark = dr["Remark"].ToString();
mdj.ActualToHKQty = Convert.ToInt32(dr["ActualToHKQty"]);
mdj.ActualToHKDate = dr["ActualToHKDate"].ToString();
lsDetails.Add(mdj);
}
return(lsDetails);
}
public ActionResult AddSalesBom(SalesBom model)
{
//listOrderInfo.Add(model);
//var result = new { total = 100, rows = listOrderInfo };
//return Json(result, JsonRequestBehavior.AllowGet);
//string OcID = Request["OcID"] == null ? "" : Request["OcID"].ToString();
//model.OcID = OcID;
string result = SalesOrderDAL.UpdateSalesBom(model);
if (result == "")
{
return(Json("OK"));
}
else
{
return(Json("Error"));
}
}
//返回SalesBom明細資料
public JsonResult SalesBomList(SalesBom model)
{
//构造成Json的格式传递
//if (!string.IsNullOrEmpty(model.OcID))
//{
// //var list = SalesOrderDAL.GetSalesBomByID(model.OcID,model.Ver,model.UpperSeq);
// //2021/03/12參數改為數據模型傳值
// var list = SalesOrderDAL.GetSalesBomByID(model);
// var result = new { total = list.Count, rows = list };
// return Json(result, JsonRequestBehavior.AllowGet);
//}
//else
//{
// return Json(null, JsonRequestBehavior.AllowGet);
//}
var list = SalesOrderDAL.GetSalesBomByID(model);
return(Json(list, JsonRequestBehavior.AllowGet));
}
public static string UpdateSalesBom(SalesBom model)
{
string result = "";
string strSql_i = "";
string strSql_u = "";
string Seq = GetMaxSeq(model.OcID, model.Ver, model.UpperSeq);
strSql_i = string.Format(
@"Insert Into oc_OrderBom(OcID,Ver,UpperSeq,Seq,PrimaryKey,ProductID,Dosage,UnitCode,Remark) Values ('{0}','{1}', '{2}', '{3}', '{4}', '{5}', {6}, '{7}', '{8}')",
model.OcID, model.Ver, model.UpperSeq, Seq, model.PrimaryKey, model.ProductID, model.Dosage, model.UnitCode, model.Remark);
strSql_u = string.Format(
@"Update oc_OrderBom Set PrimaryKey='{4}',ProductID='{5}',Dosage={6},UnitCode='{7}',Remark ='{8}' WHERE OcID='{0}' And Ver='{1}' And UpperSeq='{2}' And Seq='{3}'",
model.OcID, model.Ver, model.UpperSeq, model.Seq, model.PrimaryKey, model.ProductID, model.Dosage, model.UnitCode, model.Remark);
if (model.ActionType == "NEW")
{
result = SQLHelper.ExecuteSqlUpdate(strSql_i);
}
else
{
result = SQLHelper.ExecuteSqlUpdate(strSql_u);
}
return(result);
}