예제 #1
0
        public string StatisticSearch(SearchKeyPaginationData pd, string company, DateTime?from, DateTime?to, int num)
        {
            CustomerListViewData view = new CustomerListViewData();

            view.PageData                     = new BaseFrameWork.Model.PaginationData();
            view.PageData.PageIndex           = pd.PageIndex;
            view.PageData.PageSize            = pd.PageSize;
            view.PageData.PagintionJsFunction = "window.System.CustomerStatisticList.search()";
            view.PageData.OrderBy             = pd.OrderBy;

            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
  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";

            view.List = new Pagintion(sql, new { Num = num, CustomerName = pd.SearchKey, CompanyName = company, From = from, To = to }, view.PageData).ToList <Customer>();

            string table      = UserControlExcutor.RenderUserControl("/System/UserControl/CustomerStatisticList.ascx", view);
            string pagination = UserControlExcutor.RenderUserControl("/System/CommonUserControl/Pagination.ascx", view.PageData);

            return(JsonHelper.ToJson(new { table = table, pagination = pagination }));
        }
        public String CustomerAlertList()
        {
            CustomerListViewData view = new CustomerListViewData();

            view.PageData                     = new BaseFrameWork.Model.PaginationData();
            view.PageData.PageIndex           = 1;
            view.PageData.PageSize            = 10;
            view.PageData.PagintionJsFunction = "window.System.CustomerAlertList.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";

            view.List = new Pagintion(sql, new { AlertDays = Convert.ToInt32(ConfigurationManager.AppSettings["AlertDays"]) }, view.PageData).ToList <Customer>();

            return(PageViewHelper.LoadPage("/System/CustomerAlertList.aspx", view));
        }
예제 #3
0
        public string AlertSearch(SearchKeyPaginationData pd, string company, DateTime?from, DateTime?to)
        {
            CustomerListViewData view = new CustomerListViewData();

            view.PageData                     = new BaseFrameWork.Model.PaginationData();
            view.PageData.PageIndex           = pd.PageIndex;
            view.PageData.PageSize            = pd.PageSize;
            view.PageData.PagintionJsFunction = "window.System.CustomerAlertList.search()";
            view.PageData.OrderBy             = pd.OrderBy;

            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, '') = ''))";

            view.List = new Pagintion(sql, new { AlertDays = Convert.ToInt32(ConfigurationManager.AppSettings["AlertDays"]), CustomerName = pd.SearchKey, CompanyName = company, From = from, To = to }, view.PageData).ToList <Customer>();

            string table      = UserControlExcutor.RenderUserControl("/System/UserControl/CustomerAlertList.ascx", view);
            string pagination = UserControlExcutor.RenderUserControl("/System/CommonUserControl/Pagination.ascx", view.PageData);

            return(JsonHelper.ToJson(new { table = table, pagination = pagination }));
        }
        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 String CustomerList()
        {
            CustomerListViewData view = new CustomerListViewData();

            view.PageData                     = new BaseFrameWork.Model.PaginationData();
            view.PageData.PageIndex           = 1;
            view.PageData.PageSize            = 10;
            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
  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";

            view.List = new Pagintion(sql, null, view.PageData).ToList <Customer>();

            return(PageViewHelper.LoadPage("/System/CustomerList.aspx", view));
        }
        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("客户列表文件");
        }
        public String CustomerStatisticList()
        {
            CustomerListViewData view = new CustomerListViewData();

            view.PageData                     = new BaseFrameWork.Model.PaginationData();
            view.PageData.PageIndex           = 1;
            view.PageData.PageSize            = 10;
            view.PageData.PagintionJsFunction = "window.System.CustomerStatisticList.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
  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 CASE WHEN D.StepCount = 0 THEN 0 ELSE 1 END = @Num";

            view.List = new Pagintion(sql, new { Num = 0 }, view.PageData).ToList <Customer>();

            return(PageViewHelper.LoadPage("/System/CustomerStatisticList.aspx", view));
        }