public static DataTable GetCustomerData()
{
string sql = @"
SELECT CustomerID ,
CompanyName ,
ContactName ,
Phone ,
Address ,
EmailAddress FROM dbo.Customer";
DataTable dt = EFUtility.GetDataTableBySql(sql, null);
return(dt);
}
public static Pager <DeptEmpInfo> GetDeptEmpInfo(DeptEmpParameter parameter)
{
StringBuilder sqlLayout = new StringBuilder(@"
SELECT dep.DepartmentID ,
dep.DepartmentName ,
dep.Manager ,
dep.Depart_Description AS DepartDescription,
emp.EmployeeID ,
emp.EmployeeName ,
emp.Sex ,
emp.BirthDate ,
emp.HireDate ,
emp.Salary ,
emp.DepartmentID
FROM dbo.Department dep
LEFT JOIN dbo.Employee emp ON emp.DepartmentID = dep.DepartmentID
WHERE 1 = 1
");
// 动态查询条件的拼接
List <SqlParameter> paramList = new List <SqlParameter>();
if (!string.IsNullOrEmpty(parameter.DepartmentName))
{
sqlLayout.Append($" AND dep.DepartmentName like @DepartmentName ");
paramList.Add(new SqlParameter("@DepartmentName", $"%{parameter.DepartmentName}%"));
}
if (!string.IsNullOrEmpty(parameter.EmployeeName))
{
sqlLayout.Append($" AND emp.EmployeeName like @EmployeeName ");
paramList.Add(new SqlParameter("@EmployeeName", $"%{parameter.EmployeeName}%"));
}
Pager <DeptEmpInfo> result = new Pager <DeptEmpInfo>();
result.Rows = EFUtility.GetList <DeptEmpInfo>(sqlLayout.ToString(), paramList, parameter);
// 统计数目，需要判断是否开启分页，区分判断
if (parameter.IsPage)
{
result.Total = EFUtility.GetCount(sqlLayout.ToString(), paramList);
}
else
{
result.Total = result.Rows.Count;
}
return(result);
}
/// <summary>
/// 分页查询销售记录
/// </summary>
/// <param name="parameter"></param>
/// <returns></returns>
public static Pager <SellOrderDomain> GetSellOrderByPage(SellOrderParam parameter)
{
Pager <SellOrderDomain> result = new Pager <SellOrderDomain>();
StringBuilder sqlLayout = new StringBuilder(@"
SELECT a.SellOrderID ,
a.ProductID ,
a.SellOrderNumber ,
a.EmployeeID ,
a.CustomerID ,
a.SellOrderDate ,
prod.ProductID ,
prod.ProductName ,
prod.Price ,
prod.ProductStockNumber ,
prod.ProductSellNumber ,
cus.CustomerID ,
cus.CompanyName ,
cus.ContactName ,
cus.Phone ,
cus.Address ,
cus.EmailAddress ,
emp.EmployeeID ,
emp.EmployeeName ,
emp.Sex ,
emp.BirthDate ,
emp.HireDate ,
emp.Salary ,
emp.DepartmentID
FROM dbo.Sell_Order a
LEFT JOIN dbo.Product prod ON prod.ProductID = a.ProductID
LEFT JOIN dbo.Customer cus ON cus.CustomerID = a.CustomerID
LEFT JOIN dbo.Employee emp ON emp.EmployeeID = a.EmployeeID
WHERE 1=1
");
// 动态查询条件的拼接
List <SqlParameter> paramList = new List <SqlParameter>();
if (!string.IsNullOrEmpty(parameter.ProductName))
{
sqlLayout.Append($" AND prod.ProductName like @ProductName ");
paramList.Add(new SqlParameter("@ProductName", $"%{parameter.ProductName}%"));
}
if (!string.IsNullOrEmpty(parameter.EmployeeName))
{
sqlLayout.Append($" AND emp.EmployeeName like @EmployeeName ");
paramList.Add(new SqlParameter("@EmployeeName", $"%{parameter.EmployeeName}%"));
}
if (null != parameter.SellOrderDate)
{
sqlLayout.Append($" AND a.SellOrderDate = @SellOrderDate");
paramList.Add(new SqlParameter("@SellOrderDate", parameter.SellOrderDate));
}
// 统计数目
result.Total = EFUtility.GetCount(sqlLayout.ToString(), paramList);
result.Rows = EFUtility.GetList <SellOrderDomain>(sqlLayout.ToString(), paramList, parameter);
return(result);
}
