public string OrderUpdate([FromQuery] updateOrders UpdateData, [FromBody] UpdateOrderDetails UpdateDetailData)
{
//required information
if (UpdateData.OrderID == 0)
{
return("Please input OrderID");
}
string result, updateOrder, updateOrderDetial;
var Update = new DataUpdate();
//Determine if Order need to update
if (UpdateData.CustomerID != null || UpdateData.CustomerID != null || UpdateData.EmployeeID != null || UpdateData.OrderDate != null || UpdateData.RequiredDate != null || UpdateData.ShipVia != null ||
UpdateData.Freight != null || UpdateData.ShipName != null || UpdateData.ShipAddress != null || UpdateData.ShipCity != null || UpdateData.ShipRegion != null || UpdateData.ShipPostalCode != null || UpdateData.ShipCountry != null)
{
updateOrder = Update.UpdateOrderData(UpdateData);
}
else
{
updateOrder = "Order not Edit";
}
//Determine if Order Detail need to update & ProductID is required information
if (UpdateDetailData.ProductID != null & (UpdateDetailData.Quantity != null || UpdateDetailData.UnitPrice != null || UpdateDetailData.Discount != null))
{
updateOrderDetial = Update.UpdateOrderDetailData(UpdateDetailData, UpdateData.OrderID);
}
else
{
updateOrderDetial = "Order Detail not Edit";
}
result = updateOrder + " & " + updateOrderDetial;
return(result);
}
public string UpdateOrderDetailData(UpdateOrderDetails UOD, int getOrderID)
{
var dynamicParams = new DynamicParameters();//←動態參數
var sqlCondition = @"";
string SqlString, result;
var Update = new SqlServices();
SqlConnection conn = new SqlConnection("Data Source=howardorder.database.windows.net;Initial Catalog=OrderDatabase;Persist Security Info=True;User Id =howard;Password=Yihao1222");
conn.Open();
for (int i = 0; i < UOD.ProductID.Count(); i++)
{
sqlCondition = @"";
dynamicParams.Add("OrderID", getOrderID);
dynamicParams.Add("ProductID", UOD.ProductID[i]);
if (UOD.UnitPrice[i] != null)
{
dynamicParams.Add("UnitPrice", UOD.UnitPrice[i]);
}
else
{
var queryUnitPrice = conn.Query <queryPrice>(
@"Select a.Unitprice,b.Discount From [Products] a Join [Order Details] b on a.ProductID = b.ProductID Where b.OrderID = OrderID And b.ProductID = @ProductID Group by a.Unitprice,b.Discount"
, dynamicParams);
float?EndUnitPrice = queryUnitPrice.First().UnitPrice;
//計算金額
if (UOD.Discount[i] != null)
{
EndUnitPrice = EndUnitPrice - (EndUnitPrice * UOD.Discount[i]);
}
else
{
EndUnitPrice = queryUnitPrice.First().Discount *EndUnitPrice;
}
dynamicParams.Add("UnitPrice", EndUnitPrice);
}
sqlCondition = sqlCondition + "UnitPrice = @UnitPrice,";
if (UOD.Quantity[i] != null)
{
sqlCondition = sqlCondition + "Quantity = @Quantity,";
dynamicParams.Add("Quantity", UOD.Quantity[i]);
}
if (UOD.Discount[i] != null)
{
sqlCondition = sqlCondition + "Discount = @Discount,";
dynamicParams.Add("Discount", UOD.Discount[i]);
}
sqlCondition = sqlCondition.Remove(sqlCondition.LastIndexOf(","), 1);
var queryCustomer = conn.Query <updateOrders>(@"select PD.ProductID From Products PD Join[Order Details] OD on PD.ProductID = OD.ProductID
where OrderID = @OrderID And PD.ProductID = @ProductID", dynamicParams);
conn.Close();
if (queryCustomer.Count() != 0)
{
SqlString = $@"UPDATE [Order Details] SET
{sqlCondition}
WHERE OrderID = @OrderID
And ProductID = @ProductID";
result = Update.SqlUpdate(SqlString, dynamicParams);
}
else
{
return("No such ProductID");
}
}
return("Edit Order Detail - Success");
}
