public static List <CustomerOrderModel> SelectOrderByOrderDate(string keyword)
{
DateTime date = new DateTime();
if (!StringChecker.IsDate(keyword))
{
throw new MyException("请输入一个日期");
}
else
{
date = Convert.ToDateTime(keyword);
}
using (IDbConnection connection = new SqlConnection(Tools.GetConnectionStringValue()))
{
string sql = @"SELECT co.*, cu.username, bo.title, cs.*
FROM [dbo].[CustomerOrders] co
LEFT JOIN [dbo].[Customers] cu ON co.customer_id = cu.id
LEFT JOIN [dbo].[Books] bo ON co.book_isbn = bo.ISBN
LEFT JOIN [dbo].[CustomerOrderStatus] cs ON co.status_id = cs.id
WHERE DATEPART(YEAR, co.order_date) = @Year and DATEPART(MONTH, co.order_date) = @Month and DATEPART(DAY, co.order_date) = @Day";
List <CustomerOrderModel> customerOrders = connection.Query <CustomerOrderModel, CustomerOrderStatusModel, CustomerOrderModel>(sql,
(order, status) => { order.Status = status; return(order); },
new { Year = date.Year, Month = date.Month, Day = date.Day }, splitOn: "id").ToList();
return(customerOrders);
}
}
public static List <SupplierOrderModel> SelectOrderByOrderDate(string keyword)
{
DateTime date = new DateTime();
if (!StringChecker.IsDate(keyword))
{
throw new MyException("请输入一个日期");
}
else
{
date = Convert.ToDateTime(keyword);
}
using (IDbConnection connection = new SqlConnection(Tools.GetConnectionStringValue()))
{
string sql = @"SELECT dbo.SupplierOrders.*, dbo.SupplierOrderStatus.*, dbo.Suppliers.*
FROM dbo.SupplierOrders INNER JOIN
dbo.SupplierOrderStatus ON dbo.SupplierOrders.status_id = dbo.SupplierOrderStatus.id INNER JOIN
dbo.Suppliers ON dbo.SupplierOrders.supplier_id = dbo.Suppliers.id
WHERE DATEPART(YEAR, dbo.SupplierOrders.order_date) = @Year and DATEPART(MONTH, dbo.SupplierOrders.order_date) = @Month and DATEPART(DAY, dbo.SupplierOrders.order_date)";
List <SupplierOrderModel> supplierOrders = connection.Query <SupplierOrderModel, SupplierOrderStatusModel, SupplierModel, SupplierOrderModel>(sql,
(order, status, supplier) =>
{
order.Status = status;
order.Supplier = supplier;
return(order);
}, new { Year = date.Year, Month = date.Month, Day = date.Day }, splitOn: "id,id").ToList();
return(supplierOrders);
}
}