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 MonthSearch(SearchKeyPaginationData pd, string company, DateTime?from, DateTime?to)
{
BusinessTypeListViewData view = new BusinessTypeListViewData();
view.pageDataParents = new BaseFrameWork.Model.PaginationData();
view.pageDataParents.PageIndex = pd.PageIndex;
view.pageDataParents.PageSize = pd.PageSize;
view.pageDataParents.PagintionJsFunction = "window.System.CustomerMonthList.search()";
view.pageDataParents.OrderBy = pd.OrderBy;
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, '') = ''))";
view.menuListParents = new Pagintion(sql, new { CustomerName = pd.SearchKey, CompanyName = company, From = from, To = to }, view.pageDataParents).ToList <BusinessType>();
string table = UserControlExcutor.RenderUserControl("/System/UserControl/CustomerMonthList.ascx", view);
string pagination = UserControlExcutor.RenderUserControl("/System/CommonUserControl/Pagination.ascx", view.pageDataParents);
return(JsonHelper.ToJson(new { table = table, pagination = pagination }));
}
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 string Search(SearchKeyPaginationData pd)
{
CompanyListViewData view = new CompanyListViewData();
view.PageData = new BaseFrameWork.Model.PaginationData();
view.PageData.PageIndex = pd.PageIndex;
view.PageData.PageSize = pd.PageSize;
view.PageData.PagintionJsFunction = "window.System.CompanyList.search()";
view.PageData.OrderBy = pd.OrderBy;
string sql = @"SELECT A.*, B.Name AS CompanyCharacterName, C.Name AS PayTypeName, D.Processing, E.AllCustomer - D.Processing AS Finished
FROM dbo.Company AS A
LEFT JOIN dbo.KeyValuePair AS B ON A.PayType = B.Code AND B.[Group] = 'CompanyCharacter'
LEFT JOIN dbo.KeyValuePair AS C ON A.PayType = C.Code AND C.[Group] = 'PayType'
OUTER APPLY (SELECT COUNT(DISTINCT IA.CustomerID) AS Processing
FROM dbo.Customer AS IA
INNER JOIN dbo.ProcessStep AS IB ON IA.CustomerID = IB.CustomerID
WHERE IB.FinishDate IS NULL
AND IA.CompanyID = A.CompanyID) AS D
OUTER APPLY(SELECT COUNT(*) AS AllCustomer FROM dbo.Customer AS IA WHERE IA.CompanyID = A.CompanyID) AS E
WHERE A.Name LIKE '%' + @key + '%'";
view.List = new Pagintion(sql, new { key = pd.SearchKey }, view.PageData).ToList <Company>();
string table = UserControlExcutor.RenderUserControl("/System/UserControl/CompanyList.ascx", view);
string pagination = UserControlExcutor.RenderUserControl("/System/CommonUserControl/Pagination.ascx", view.PageData);
return(JsonHelper.ToJson(new { table = table, pagination = pagination }));
}
public string SelectSearch(SearchKeyPaginationData pd)
{
PriceListViewData view = new PriceListViewData();
view.PageData = new BaseFrameWork.Model.PaginationData();
view.PageData.PageIndex = pd.PageIndex;
view.PageData.PageSize = pd.PageSize;
view.PageData.PagintionJsFunction = "window.System.SelectPrice.search()";
view.PageData.OrderBy = pd.OrderBy;
string sql = @"SELECT * FROM dbo.PriceList WHERE StartPlace LIKE '%' + @Place + '%' OR DestinationPlace LIKE '%' + @Place + '%' OR InterimPlace LIKE '%' + @Place + '%'";
view.List = new Pagintion(sql, new { Place = pd.SearchKey }, view.PageData).ToList <PriceList>();
string table = UserControlExcutor.RenderUserControl("/System/UserControl/SelectPrice.ascx", view);
string pagination = UserControlExcutor.RenderUserControl("/System/CommonUserControl/Pagination.ascx", view.PageData);
return(JsonHelper.ToJson(new { table = table, pagination = pagination }));
}
public string SelectSearch(SearchKeyPaginationData pd)
{
BusinessTypeListViewData view = new BusinessTypeListViewData();
view.pageDataParents = new BaseFrameWork.Model.PaginationData();
view.pageDataParents.PageIndex = pd.PageIndex;
view.pageDataParents.PageSize = pd.PageSize;
view.pageDataParents.PagintionJsFunction = "window.System.SelectBusinessType.search()";
view.pageDataParents.OrderBy = pd.OrderBy;
string sql = "SELECT * FROM BusinessType WHERE ParentID IS NULL AND Name LIKE '%' + @key + '%'";
view.menuListParents = new Pagintion(sql, new { key = pd.SearchKey }, view.pageDataParents).ToList <BusinessType>();
string table = UserControlExcutor.RenderUserControl("/System/UserControl/SelectBusinessType.ascx", view);
string pagination = UserControlExcutor.RenderUserControl("/System/CommonUserControl/Pagination.ascx", view.pageDataParents);
return(JsonHelper.ToJson(new { table = table, pagination = pagination }));
}
public string SelectSearch(SearchKeyPaginationData pd)
{
AgentCompanyListViewData view = new AgentCompanyListViewData();
view.PageData = new BaseFrameWork.Model.PaginationData();
view.PageData.PageIndex = pd.PageIndex;
view.PageData.PageSize = pd.PageSize;
view.PageData.PagintionJsFunction = "window.System.SelectAgentCompany.search()";
view.PageData.OrderBy = pd.OrderBy;
string sql = @"SELECT A.*, B.Name AS AgentCompanyCharacterName
FROM dbo.AgentCompany AS A
LEFT JOIN dbo.KeyValuePair AS B ON A.[Character] = B.Code AND B.[Group] = 'CompanyCharacter'
WHERE A.Name LIKE '%' + @key + '%'";
view.List = new Pagintion(sql, new { key = pd.SearchKey }, view.PageData).ToList <AgentCompany>();
string table = UserControlExcutor.RenderUserControl("/System/UserControl/SelectAgentCompany.ascx", view);
string pagination = UserControlExcutor.RenderUserControl("/System/CommonUserControl/Pagination.ascx", view.PageData);
return(JsonHelper.ToJson(new { table = table, pagination = pagination }));
}
