//返採購單明細總金額
public static BuyDetails GetTotalAmtByID(string strID, int Ver, string Seq)
{
string strSql = string.Format(@"Select Sum(a.TotalSum) As TotalSum From pu_BuyDetails a WHERE a.ID='{0}' And a.Ver={1} And a.Seq<>'{2}'", strID, Ver, Seq);
DataTable dt = SQLHelper.ExecuteSqlReturnDataTable(strSql);
BuyDetails objModel = new BuyDetails();
if (dt.Rows.Count > 0)
{
objModel.TotalSum = string.IsNullOrEmpty(dt.Rows[0]["TotalSum"].ToString()) ? 0 : decimal.Parse(dt.Rows[0]["TotalSum"].ToString());
}
else
{
objModel.TotalSum = 0;
}
strSql = string.Format(@"select Sum(FareSum) as FareSum From pu_BuyFare WHERE ID='{0}' And Ver={1} group by ID,Ver", strID, Ver);
dt = SQLHelper.ExecuteSqlReturnDataTable(strSql);
if (dt.Rows.Count > 0)
{
objModel.Price = string.IsNullOrEmpty(dt.Rows[0]["FareSum"].ToString()) ? 0 : decimal.Parse(dt.Rows[0]["FareSum"].ToString());
}
else
{
objModel.Price = 0;
}
return(objModel);
}
public void BuySelected(Buy buy)
{
BuyDetails.Clear();
using (var unitOfWork = new UnitOfWork(new MsContext()))
{
foreach (var buyDetail in unitOfWork.BuyDetailRepository.GetAll())
{
if (buyDetail.IdBuyForeignKey == buy.IdBuy)
{
string name_product;
try
{
name_product = unitOfWork.Product.Get(buyDetail.IdProductForeignKey).NameProduct;
}
catch (Exception e)
{
name_product = "";
}
BuyDetails.Add(new BuyDetail()
{
Id = buyDetail.IdBuyDetail,
PrixTotal = buyDetail.PrixTotal + "",
NameProcuct = name_product,
PrixUnit = buyDetail.PrixUnit + "",
Qte = buyDetail.Qte + "",
});
}
}
}
Console.WriteLine("1111111111111111111111111111111111");
}
// todo: use mapper
public Strategy ToEntity()
{
Strategy strategy = new Strategy();
strategy.Id = Id;
strategy.Name = Name;
strategy.CanCustomizeWidth = CanCustomizeWidth;
strategy.CanCustomizeWingspan = CanCustomizeWingspan;
strategy.CanCustomizeExpiry = CanCustomizeExpiry;
strategy.BuyDetails = BuyDetails.ToEntity();
strategy.BuyDetailsId = strategy.BuyDetails.Id;
strategy.SellDetails = SellDetails.ToEntity();
strategy.SellDetailsId = strategy.SellDetails.Id;
strategy.Legs = Legs.ConvertAll(m => m.ToEntity(Id));
strategy.PairStrategyId = PairStrategyId;
return(strategy);
}
//更新明細表
public static string UpdateBuyDetails(BuyDetails model)
{
string result = "";
string strSql_i = "";
string strSql_u = "";
string Seq = GetMaxSeq(model.ID);
strSql_i += string.Format(@" SET XACT_ABORT ON ");
strSql_i += string.Format(@" BEGIN TRANSACTION ");
strSql_i += string.Format(
@" Insert Into pu_BuyDetails(ID,Ver,Seq,ProductMo,ProductID,ProductCdesc,ArriveDate,OrderQty,OrderUnit,Price,PriceUnit,DiscountRate,DiscountAmt,TotalSum,Spec,Color,Weight,WeightUnit,Remarks)
Values ('{0}','{1}', '{2}', '{3}', '{4}', '{5}', '{6}', {7}, '{8}', {9}, '{10}', {11}, {12}, {13}, '{14}', '{15}', {16}, '{17}', '{18}')",
model.ID, model.Ver, Seq, model.ProductMo, model.ProductID, model.ProductCdesc, model.ArriveDate, model.OrderQty, model.OrderUnit, model.Price, model.PriceUnit, model.DiscountRate, model.DiscountAmt,
model.TotalSum, model.Spec, model.Color, model.Weight, model.WeightUnit, model.Remarks);
/*20210312取消插入新記錄時自動添加SalesBOM
* strSql_i += string.Format(
* @" Insert Into oc_OrderBom(OcID, Ver, UpperSeq,Seq,PrimaryKey,ProductID,Dosage,UnitCode) Values('{0}','{1}', '{2}', '{3}','{4}','{5}',{6},'{7}')",
* model.OcID, model.Ver, Seq,"001","1", model.ProductID,1,"PCS");
*/
strSql_i += string.Format(@" COMMIT TRANSACTION ");
strSql_u = string.Format(
@" Update pu_BuyDetails
Set ProductMo='{3}',ProductID='{4}',ProductCdesc='{5}',ArriveDate='{6}',OrderQty={7},OrderUnit='{8}',Price={9},PriceUnit='{10}',DiscountRate={11},DiscountAmt={12},TotalSum={13},Spec='{14}',
Color='{15}',Weight={16},WeightUnit='{17}',Remarks='{18}'
WHERE ID='{0}' And Ver='{1}' And Seq='{2}'", model.ID, model.Ver, model.Seq,
model.ProductMo, model.ProductID, model.ProductCdesc, model.ArriveDate, model.OrderQty, model.OrderUnit, model.Price, model.PriceUnit, model.DiscountRate, model.DiscountAmt, model.TotalSum, model.Spec,
model.Color, model.Weight, model.WeightUnit, model.Remarks);
if (model.ActionType == "NEW")
{
result = SQLHelper.ExecuteSqlUpdate(strSql_i);
}
else
{
result = SQLHelper.ExecuteSqlUpdate(strSql_u);
}
return(result);
}
public ActionResult AddList(BuyDetails model)
{
//listOrderInfo.Add(model);
//var result = new { total = 100, rows = listOrderInfo };
//return Json(result, JsonRequestBehavior.AllowGet);
//因明細頁沒有ID控件,所以用此方法重新賦值
string ID = Request["ID"] == null ? "" : Request["ID"].ToString();
int Ver = string.IsNullOrEmpty(Request["Ver"]) ? 0 : int.Parse(Request["Ver"].ToString());
model.ID = ID;
model.Ver = Ver;
string result = PurchaseDAL.UpdateBuyDetails(model);
if (result == "")
{
return(Json("OK"));
}
else
{
return(Json("Error"));
}
}
//返採購單其他費用總金額
public static BuyDetails GetTotalAmtOtherByID(string strID, int Ver, string strFareID)
{
string strSql = string.Format(
@"SELECT Sum(a.TotalSum) As TotalSum,Max(S.FareSum) AS FareSum
From pu_BuyDetails a with(nolock)
LEFT JOIN (Select ID,Ver,Sum(FareSum) as FareSum From pu_BuyFare with(nolock) WHERE ID='{0}' And Ver={1} AND FareID<>'{2}' Group By ID,Ver) S ON a.ID=S.ID AND a.Ver=S.Ver
WHERE a.ID='{0}' And a.Ver={1}", strID, Ver, strFareID);
DataTable dt = SQLHelper.ExecuteSqlReturnDataTable(strSql);
BuyDetails objModel = new BuyDetails();
if (dt.Rows.Count > 0)
{
objModel.TotalSum = string.IsNullOrEmpty(dt.Rows[0]["TotalSum"].ToString()) ? 0 : decimal.Parse(dt.Rows[0]["TotalSum"].ToString());
objModel.Price = string.IsNullOrEmpty(dt.Rows[0]["FareSum"].ToString()) ? 0 : decimal.Parse(dt.Rows[0]["FareSum"].ToString());
}
else
{
objModel.TotalSum = 0;
objModel.Price = 0;
}
return(objModel);
}
//返回明細資料
public static List <BuyDetails> GetBuyDetailsByID(string ID)
{
string strSql =
@"SELECT ID,Ver,Seq,ProductMo,ProductID,ProductCdesc,Convert(varchar(10),ArriveDate,120) AS ArriveDate,CONVERT(int,OrderQty) AS OrderQty,OrderUnit,Price,
PriceUnit,DiscountRate,DiscountAmt,TotalSum,Spec,Color,Weight,WeightUnit,Remarks
FROM dbo.pu_BuyDetails Where ID='" + ID + "' ORDER BY Seq";
DataTable dt = SQLHelper.ExecuteSqlReturnDataTable(strSql);
List <BuyDetails> lsDetails = new List <BuyDetails>();
for (int i = 0; i < dt.Rows.Count; i++)
{
BuyDetails mdj = new BuyDetails();
DataRow dr = dt.Rows[i];
mdj.ID = dr["ID"].ToString();
mdj.Ver = int.Parse(dr["Ver"].ToString());
mdj.Seq = dr["Seq"].ToString();
mdj.ProductMo = dr["ProductMo"].ToString();
mdj.ProductID = dr["ProductID"].ToString();
mdj.ProductCdesc = dr["ProductCdesc"].ToString();
mdj.ArriveDate = dr["ArriveDate"].ToString();
mdj.OrderQty = Convert.ToInt32(dr["OrderQty"]);
mdj.OrderUnit = dr["OrderUnit"].ToString();
mdj.Price = Convert.ToDecimal(dr["Price"]);
mdj.PriceUnit = dr["PriceUnit"].ToString();
mdj.DiscountRate = Convert.ToDecimal(string.IsNullOrEmpty(dr["DiscountRate"].ToString()) ? "0.00" : dr["DiscountRate"]);
mdj.DiscountAmt = Convert.ToDecimal(string.IsNullOrEmpty(dr["DiscountAmt"].ToString()) ? "0.00" : dr["DiscountAmt"]);
mdj.TotalSum = Convert.ToDecimal(string.IsNullOrEmpty(dr["TotalSum"].ToString()) ? "0.00" : dr["TotalSum"]);
mdj.Spec = dr["Spec"].ToString();
mdj.Color = dr["Color"].ToString();
mdj.Weight = Convert.ToDecimal(string.IsNullOrEmpty(dr["Weight"].ToString()) ? "0.00" : dr["Weight"]);
mdj.WeightUnit = dr["WeightUnit"].ToString();
mdj.Remarks = dr["Remarks"].ToString();
lsDetails.Add(mdj);
}
return(lsDetails);
}
