public void TestGetSellOrderInfo()
{
SellOrderInfo param = new SellOrderInfo();
//param.EmployeeName = "王";
param.page = 1;
param.limit = 10;
var res = SellOrderMgr.Mgr.GetSellOrderInfo(param);
Assert.IsNotNull(res);
}
public ResultPager <SellOrderInfo> GetSellOrderInfo(SellOrderInfo param)
{
string sql = @"
SELECT Row_number() over(order by a.ID) as RN,
a.ID ,
a.ProductID ,
a.SellOrderNumber ,
a.EmployeeID ,
a.CustomerID ,
a.SellOrderDate ,
pt.ProductName ,
pt.Price ,
pt.ProductStockNumber ,
pt.ProductSellNumber ,
em.ID EmployeeName ,
em.Sex ,
em.BirthDate ,
em.HireDate ,
em.Salary ,
em.DepartmentID ,
cm.CompanyName ,
cm.ContactName ,
cm.Phone ,
cm.Address ,
cm.EmailAddress
FROM dbo.T_SELL_ORDER a
LEFT JOIN dbo.T_PRODUCT pt ON a.ProductID = pt.ID
LEFT JOIN dbo.T_EMPLOYEE em ON a.EmployeeID = em.ID
LEFT JOIN dbo.T_CUSTOMER cm ON a.CustomerID = cm.ID
/**where**/
";
// Dapper 扩展 SqlBuilder
SqlBuilder builder = new SqlBuilder();
if (!string.IsNullOrEmpty(param.EmployeeName))
{
builder.Where("EmployeeName LIKE @EmployeeName", new { EmployeeName = string.Format($"%{param.EmployeeName}%") });
}
if (!string.IsNullOrEmpty(param.CompanyName))
{
builder.Where("CompanyName LIKE @CompanyName", new { CompanyName = string.Format($"%{param.CompanyName}%") });
}
if (param.SellOrderDate.HasValue)
{
builder.Where("SellOrderDate=@SellOrderDate", new { SellOrderDate = param.SellOrderDate });
}
ResultPager <SellOrderInfo> resPager = DapperHelper.GetResultPager <SellOrderInfo>(builder, sql, param);
return(resPager);
}
public List <SellOrderInfo> GetAllSellOrder(int id)
{
string query1 = @"SELECT s.[Id]
,s.Quentity
,c.CategoryName
,i.ItemName
,h.ShopName
,a.AreaName
FROM [FMCG_Db].[dbo].[tb_StockOut] s
inner join tb_Category c on s.CategoryId = c.Id
inner join tb_Item i on s.ItemId = i.Id
inner join tb_ShopInfo h on s.ShopId = h.Id
inner join tb_Area a on s.AreaId = a.Id
Where s.Id = '" + id + "'";
try
{
SqlCommand command = new SqlCommand(query1, _connection);
_connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <SellOrderInfo> sellorders = new List <SellOrderInfo>();
while (reader.Read())
{
SellOrderInfo sellorder = new SellOrderInfo();
sellorder.Id = (int)reader["Id"];
sellorder.CategoryName = reader["CategoryName"].ToString();
sellorder.ItemName = reader["ItemName"].ToString();
sellorder.Quentity = (int)reader["Quentity"];
sellorder.ShopName = reader["ShopName"].ToString();
sellorder.AreaName = reader["AreaName"].ToString();
sellorders.Add(sellorder);
}
reader.Close();
return(sellorders);
}
catch (Exception exception)
{
throw new Exception("Unable to connect Server", exception);
}
finally
{
_connection.Close();
}
}
public JsonResult GetSellOrderInfo(SellOrderInfo param)
{
var result = SellOrderMgr.Mgr.GetSellOrderInfo(param);
return(Json(result));
}
public ResultPager <SellOrderInfo> GetSellOrderInfo(SellOrderInfo param)
{
var res = SellOrderDAO.DAO.GetSellOrderInfo(param);
return(res);
}
