public void CustomerAlertListExport(string searchKey, string company, DateTime?from, DateTime?to)
{
CustomerListViewData view = new CustomerListViewData();
view.PageData = new BaseFrameWork.Model.PaginationData();
view.PageData.PageIndex = 1;
view.PageData.PageSize = int.MaxValue;
view.PageData.PagintionJsFunction = "window.System.CustomerList.search()";
view.PageData.OrderBy = "CreatedOn DESC, Name";
string sql = @"SELECT A.*, B.Name AS CompanyName, C.Name AS BusinessTypeName, SUM(BidCost) OVER() AS TotalBidCost, SUM(SaleAmount) OVER() AS TotalSaleAmount,
SUM(FactCost) OVER() AS TotalFactCost, SUM(Profit) OVER() AS TotalProfit, D.StepName, D.EstimateDate
FROM dbo.Customer AS A
LEFT JOIN dbo.Company AS B ON A.CompanyID = B.CompanyID
LEFT JOIN dbo.BusinessType AS C ON A.BusinessType = C.TypeID
OUTER APPLY (SELECT TOP 1 IA.ID AS StepID, IA.StepName, IA.EstimateDate
FROM dbo.ProcessStep AS IA
WHERE A.CustomerID = IA.CustomerID
AND IA.EstimateDate > GETDATE()
AND IA.EstimateDate < GETDATE() + @AlertDays
AND FinishDate IS NULL
ORDER BY IA.EstimateDate, IA.CreatedOn) AS D
WHERE D.StepID IS NOT NULL
AND (((ISNULL(@CustomerName, '') <> '') AND A.Name LIKE '%' + @CustomerName + '%') OR (ISNULL(@CustomerName, '') = ''))
AND (((ISNULL(@CompanyName, '') <> '') AND B.Name LIKE '%' + @CompanyName + '%') OR (ISNULL(@CompanyName, '') = ''))
AND (((ISNULL(@From, '') <> '') AND A.CreatedOn >= @From) OR (ISNULL(@From, '') = ''))
AND (((ISNULL(@To, '') <> '') AND A.CreatedOn < DATEADD(DAY, 1, @To)) OR (ISNULL(@To, '') = ''))";
DataTable dt = new Pagintion(sql, new
{
AlertDays = Convert.ToInt32(ConfigurationManager.AppSettings["AlertDays"]),
CustomerName = searchKey,
CompanyName = company,
From = from, To = to
}, view.PageData).ToDataTable();
dt = dt.DefaultView.ToTable(true, new string[] { "Name", "CompanyName", "BusinessTypeName", "BidCost", "SaleAmount", "FactCost", "Profit", "Phone", "StartPlace", "DestinationPlace", "StepName", "EstimateDate" });
dt.Columns["Name"].ColumnName = "客户名称";
dt.Columns["CompanyName"].ColumnName = "所属公司";
dt.Columns["BusinessTypeName"].ColumnName = "业务类型";
dt.Columns["BidCost"].ColumnName = "报价";
dt.Columns["SaleAmount"].ColumnName = "销售额";
dt.Columns["FactCost"].ColumnName = "成本";
dt.Columns["Profit"].ColumnName = "利润";
dt.Columns["Phone"].ColumnName = "联系电话";
dt.Columns["StartPlace"].ColumnName = "起运地";
dt.Columns["DestinationPlace"].ColumnName = "目的地";
dt.Columns["StepName"].ColumnName = "下一步";
dt.Columns["EstimateDate"].ColumnName = "预计处理日期";
ExcelHelper eh = new ExcelHelper();
eh.FillDataNew("客户列表", dt, "客户列表", true);
eh.ExportExcelFile("客户列表文件");
}
public void CustomerStatisticListExport(string searchKey, string company, DateTime?from, DateTime?to, int num)
{
CustomerListViewData view = new CustomerListViewData();
view.PageData = new BaseFrameWork.Model.PaginationData();
view.PageData.PageIndex = 1;
view.PageData.PageSize = int.MaxValue;
view.PageData.PagintionJsFunction = "window.System.CustomerList.search()";
view.PageData.OrderBy = "CreatedOn DESC, Name";
string sql = @"SELECT A.*, B.Name AS CompanyName, C.Name AS BusinessTypeName
FROM dbo.Customer AS A
LEFT JOIN dbo.Company AS B ON A.CompanyID = B.CompanyID
LEFT JOIN dbo.BusinessType AS C ON A.BusinessType = C.TypeID
OUTER APPLY (SELECT COUNT(*) AS StepCount
FROM dbo.ProcessStep AS IA
WHERE FinishDate IS NULL) AS D
WHERE (((ISNULL(@CustomerName, '') <> '') AND A.Name LIKE '%' + @CustomerName + '%') OR (ISNULL(@CustomerName, '') = ''))
AND (((ISNULL(@CompanyName, '') <> '') AND B.Name LIKE '%' + @CompanyName + '%') OR (ISNULL(@CompanyName, '') = ''))
AND (((ISNULL(@From, '') <> '') AND A.CreatedOn >= @From) OR (ISNULL(@From, '') = ''))
AND (((ISNULL(@To, '') <> '') AND A.CreatedOn < DATEADD(DAY, 1, @To)) OR (ISNULL(@To, '') = ''))
AND CASE WHEN D.StepCount = 0 THEN 0 ELSE 1 END = @Num";
DataTable dt = new Pagintion(sql, new { Num = num, CustomerName = searchKey, CompanyName = company, From = from, To = to }, view.PageData).ToDataTable();
dt = dt.DefaultView.ToTable(true, new string[] { "Name", "CompanyName", "BusinessTypeName", "BidCost", "SaleAmount", "FactCost", "Profit", "Phone", "StartPlace", "DestinationPlace", "Comment" });
dt.Columns["Name"].ColumnName = "客户名称";
dt.Columns["CompanyName"].ColumnName = "所属公司";
dt.Columns["BusinessTypeName"].ColumnName = "业务类型";
dt.Columns["BidCost"].ColumnName = "报价";
dt.Columns["SaleAmount"].ColumnName = "销售额";
dt.Columns["FactCost"].ColumnName = "成本";
dt.Columns["Profit"].ColumnName = "利润";
dt.Columns["Phone"].ColumnName = "联系电话";
dt.Columns["StartPlace"].ColumnName = "起运地";
dt.Columns["DestinationPlace"].ColumnName = "目的地";
dt.Columns["Comment"].ColumnName = "评价";
ExcelHelper eh = new ExcelHelper();
eh.FillDataNew("客户列表", dt, "客户列表", true);
eh.ExportExcelFile("客户列表文件");
}
public void CustomerMonthListExport(string searchKey, string company, DateTime?from, DateTime?to)
{
CustomerListViewData view = new CustomerListViewData();
view.PageData = new BaseFrameWork.Model.PaginationData();
view.PageData.PageIndex = 1;
view.PageData.PageSize = int.MaxValue;
view.PageData.PagintionJsFunction = "window.System.CustomerList.search()";
view.PageData.OrderBy = "Code";
string sql = @"SELECT DISTINCT C.Code, C.Name, SUM(BidCost) OVER(PARTITION BY A.BusinessType, C.Code, C.NAME) AS BidCost,
SUM(SaleAmount) OVER(PARTITION BY A.BusinessType, C.Code, C.NAME) AS SaleAmount,
SUM(FactCost) OVER(PARTITION BY A.BusinessType, C.Code, C.NAME) AS FactCost,
SUM(Profit) OVER(PARTITION BY A.BusinessType, C.Code, C.NAME) AS Profit,
SUM(BidCost) OVER() AS TotalBidCost, SUM(SaleAmount) OVER() AS TotalSaleAmount,
SUM(FactCost) OVER() AS TotalFactCost, SUM(Profit) OVER() AS TotalProfit
FROM dbo.Customer AS A
LEFT JOIN dbo.Company AS B ON A.CompanyID = B.CompanyID
LEFT JOIN dbo.BusinessType AS C ON A.BusinessType = C.TypeID
WHERE (((ISNULL(@CustomerName, '') <> '') AND A.Name LIKE '%' + @CustomerName + '%') OR (ISNULL(@CustomerName, '') = ''))
AND (((ISNULL(@CompanyName, '') <> '') AND B.Name LIKE '%' + @CompanyName + '%') OR (ISNULL(@CompanyName, '') = ''))
AND (((ISNULL(@From, '') <> '') AND A.CreatedOn >= @From) OR (ISNULL(@From, '') = ''))
AND (((ISNULL(@To, '') <> '') AND A.CreatedOn < DATEADD(DAY, 1, @To)) OR (ISNULL(@To, '') = ''))";
DataTable dt = new Pagintion(sql, new { CustomerName = searchKey, CompanyName = company, From = from, To = to }, view.PageData).ToDataTable();
dt = dt.DefaultView.ToTable(true, new string[] { "Code", "Name", "BidCost", "SaleAmount", "FactCost", "Profit" });
dt.Columns["Code"].ColumnName = "业务类型编码";
dt.Columns["Name"].ColumnName = "业务类型名称";
dt.Columns["BidCost"].ColumnName = "报价";
dt.Columns["SaleAmount"].ColumnName = "销售额";
dt.Columns["FactCost"].ColumnName = "实际成本";
dt.Columns["Profit"].ColumnName = "利润";
ExcelHelper eh = new ExcelHelper();
eh.FillDataNew("客户列表", dt, "客户列表", true);
eh.ExportExcelFile("客户列表文件");
}
