public OrderResults <IEnumerable <OrderDataVM> > GetOrderData(int Orderid)
{
var dynamicParams = new DynamicParameters();//←動態參數
dynamicParams.Add("OrderID", Orderid);
var result = new OrderResults <IEnumerable <OrderDataVM> >();
SqlConnection conn = new SqlConnection("Data Source=howardorder.database.windows.net;Initial Catalog=OrderDatabase;Persist Security Info=True;User Id =howard;Password=Yihao1222");
conn.Open();
var SQL = conn.Query <OrderDataVM>(
@"Select Ord.OrderID, Ord.ShipName , OD.ProductID, PD.ProductName, PD.UnitPrice, EP.EmployeeID, EP.LastName + ' ' + EP.FirstName as 'EmployeeName', Ord.CustomerID, CT.CompanyName, CT.ContactName
From Orders Ord
join Employees EP on Ord.EmployeeID = EP.EmployeeID
join Customers CT on Ord.CustomerID = CT.CustomerID
join [Order Details] OD on Ord.OrderID = OD.OrderID
join Products PD on OD.ProductID = PD.ProductID
where Ord.OrderID = @OrderID
Group By Ord.OrderID, Ord.ShipName ,OD.ProductID ,PD.ProductName, PD.UnitPrice, EP.EmployeeID, EP.LastName, EP.FirstName, Ord.CustomerID, CT.CompanyName, CT.ContactName"
, dynamicParams);
conn.Close();
result.Payload = SQL;
return(result);
}
private void SalesReportWindow_Load(object sender, EventArgs e)
{
List <List <String> > report = new List <List <String> >();
SqlCeDataReader OrderResults, IndividualOrders;
OrderResults = sr.CustomerSales(Date);
bool first = true;
decimal totalSale = 0, productTotal = 0;
while (OrderResults.Read())
{
String OrderId = Convert.ToString(OrderResults["OrderID"]);
IndividualOrders = sr.OrderDetail(OrderId, Date);
while (IndividualOrders.Read())
{
if (first == true)
{
rctBoxReport.SelectionFont = new Font(rctBoxReport.Font, FontStyle.Bold);
rctBoxReport.AppendText(String.Format("Order ID : "));
rctBoxReport.SelectionFont = new Font(rctBoxReport.Font, FontStyle.Regular);
rctBoxReport.AppendText(Convert.ToString(IndividualOrders["OrderID"]));
rctBoxReport.SelectionFont = new Font(rctBoxReport.Font, FontStyle.Bold);
rctBoxReport.AppendText(String.Format("\nCustomer ID : "));
rctBoxReport.SelectionFont = new Font(rctBoxReport.Font, FontStyle.Regular);
rctBoxReport.AppendText(Convert.ToString(IndividualOrders["CID"]));
rctBoxReport.SelectionFont = new Font(rctBoxReport.Font, FontStyle.Bold);
rctBoxReport.AppendText(String.Format("\nCompany Name: "));
rctBoxReport.SelectionFont = new Font(rctBoxReport.Font, FontStyle.Regular);
rctBoxReport.AppendText(Convert.ToString(IndividualOrders["CompanyName"]));
rctBoxReport.SelectionFont = new Font(rctBoxReport.Font, FontStyle.Bold);
rctBoxReport.AppendText(string.Format("\n\n{0,-15} {1,-35} {2,-10:C} {3,-8} {4:C}\n", "Product ID", "Name", "Price", "Qty", "Total"));
rctBoxReport.AppendText("_______________________________________________________________________________\n");
rctBoxReport.SelectionFont = new Font(rctBoxReport.Font, FontStyle.Regular);
first = false;
}
productTotal = Convert.ToDecimal(IndividualOrders["Total"]);
totalSale += productTotal;
rctBoxReport.AppendText(String.Format("{0,-15} {1,-35} {2,-10:C} {3,-8} {4:C}\n", IndividualOrders["ProductCode"],
IndividualOrders["Name"], Convert.ToDecimal(IndividualOrders["Price"]), IndividualOrders["Quantity"], productTotal));
}
rctBoxReport.AppendText("_______________________________________________________________________________\n");
rctBoxReport.AppendText(string.Format("{0,68} {1,10:C}", "Total", totalSale));
totalSale = 0M;
rctBoxReport.AppendText("\n\n\n\n\n");
first = true;
}
}
public async Task <UpdateOrderResponseDto> Handle(UpdateOrderCommand request, CancellationToken cancellationToken)
{
var OrderId = new SqlParameter("@OrderId", SqlDbType.Int)
{
Value = request.UpdateOrderRequestDto.OrderId
};
var OrderItemId = new SqlParameter("@OrderItemId", SqlDbType.Int)
{
Value = request.UpdateOrderRequestDto.OrderItemId
};
var CustId = new SqlParameter("@CustId", SqlDbType.Int)
{
Value = request.UpdateOrderRequestDto.CustomerId
};
var StaffId = new SqlParameter("@StaffId", SqlDbType.Int)
{
Value = request.UpdateOrderRequestDto.StaffId
};
var StoreId = new SqlParameter("@StoreId", SqlDbType.Int)
{
Value = request.UpdateOrderRequestDto.StoreId
};
var ProductId = new SqlParameter("@ProductId", SqlDbType.Int)
{
Value = request.UpdateOrderRequestDto.ProductId
};
var Quantity = new SqlParameter("@Quantity", SqlDbType.Int)
{
Value = request.UpdateOrderRequestDto.Quantity
};
var OrderStatus = new SqlParameter("@OrderStatus", SqlDbType.VarChar, 255)
{
Value = request.UpdateOrderRequestDto.OrderStatus
};
//var ShippedDate = new SqlParameter("@ShippedDate", SqlDbType.DateTime2)
//{
// Value = DateTime.UtcNow.AddDays(_configuration.GetValue<int>("AppSettings:ExpectingDeliveryInDays"))
//};
var IsSuccess = new SqlParameter("@IsError", SqlDbType.Bit)
{
Direction = ParameterDirection.Output
};
var ResponseMessage = new SqlParameter("@ResponseMessage", SqlDbType.VarChar, 255)
{
Direction = ParameterDirection.Output
};
bool? isSuccess = null;
string message = "";
using var dbcontext = _dataContextFactory.SpawnDbContext();
var result = await dbcontext.Database.ExecuteSqlInterpolatedAsync($"EXEC updateOrderDetails @OrderId={OrderId.Value}, @OrderItemId={OrderId.Value}, @CustId={CustId.Value}, @StaffId={StaffId.Value}, @StoreId={StoreId.Value}, @ProductId={ProductId.Value}, @Quantity={Quantity.Value}, @OrderStatus={OrderStatus.Value}, @IsSuccess={IsSuccess} out, @ResponseMessage={ResponseMessage} out");
if (IsSuccess.Value != DBNull.Value)
{
isSuccess = (bool)IsSuccess.Value;
}
if (ResponseMessage.Value != DBNull.Value)
{
message = (string)ResponseMessage.Value;
}
await dbcontext.SaveChangesAsync().ConfigureAwait(false);
var entity = new OrderResults
{
Success = isSuccess,
Message = message
};
return(_mapper.Map <UpdateOrderResponseDto>(entity));
}
