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 BusinessTypeList()
{
BusinessTypeListViewData view = new BusinessTypeListViewData();
view.pageDataParents = new BaseFrameWork.Model.PaginationData();
view.pageDataParents.PageIndex = 1;
view.pageDataParents.PageSize = 10;
view.pageDataParents.PagintionJsFunction = "window.System.BusinessTypeList.firstSearch()";
view.pageDataParents.OrderBy = "Code";
string sql = "SELECT * FROM BusinessType WHERE ParentID IS NULL";
view.menuListParents = new Pagintion(sql, null, view.pageDataParents).ToList <BusinessType>();
view.pageDataChildren = new BaseFrameWork.Model.PaginationData();
view.pageDataChildren.PageIndex = 1;
view.pageDataChildren.PageSize = 10;
view.pageDataChildren.PagintionJsFunction = "window.System.BusinessTypeList.secondSearch()";
view.pageDataChildren.OrderBy = "Code";
Guid ParentGuid = view.menuListParents.Count > 0 ? view.menuListParents[0].TypeID : Guid.Empty;
sql = "SELECT A.*, B.[Name] AS ParentName FROM BusinessType AS A INNER JOIN BusinessType AS B ON A.ParentID = B.TypeID WHERE B.TypeID = @TypeID";
view.menuListChildren = new Pagintion(sql, new { TypeID = ParentGuid }, view.pageDataChildren).ToList <BusinessType>();
return(PageViewHelper.LoadPage("/System/BusinessTypeList.aspx", view));
}
public String CustomerMonthList()
{
BusinessTypeListViewData view = new BusinessTypeListViewData();
view.pageDataParents = new BaseFrameWork.Model.PaginationData();
view.pageDataParents.PageIndex = 1;
view.pageDataParents.PageSize = 10;
view.pageDataParents.PagintionJsFunction = "window.System.CustomerMonthList.search()";
view.pageDataParents.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 A.CreatedOn >= CAST(DATENAME(YEAR, GETDATE()) + '-' + DATENAME(MONTH, GETDATE()) + '-01' AS DATETIME)
AND A.CreatedOn < GETDATE()";
view.menuListParents = new Pagintion(sql, null, view.pageDataParents).ToList <BusinessType>();
return(PageViewHelper.LoadPage("/System/CustomerMonthList.aspx", view));
}
public String SelectBusinessType()
{
BusinessTypeListViewData view = new BusinessTypeListViewData();
view.pageDataParents = new BaseFrameWork.Model.PaginationData();
view.pageDataParents.PageIndex = 1;
view.pageDataParents.PageSize = 10;
view.pageDataParents.PagintionJsFunction = "window.System.SelectBusinessType.search()";
view.pageDataParents.OrderBy = "Code";
string sql = "SELECT * FROM BusinessType WHERE ParentID IS NULL";
view.menuListParents = new Pagintion(sql, null, view.pageDataParents).ToList <BusinessType>();
return(PageViewHelper.LoadPage("/System/SelectBusinessType.aspx", view));
}
public string SecondSearch(Guid parentID, PaginationData pd)
{
BusinessTypeListViewData view = new BusinessTypeListViewData();
view.pageDataChildren = new BaseFrameWork.Model.PaginationData();
view.pageDataChildren.PageIndex = pd.PageIndex;
view.pageDataChildren.PageSize = pd.PageSize;
view.pageDataChildren.PagintionJsFunction = "window.System.BusinessTypeList.secondSearch()";
view.pageDataChildren.OrderBy = pd.OrderBy;
string sql = "SELECT A.*, B.[Name] AS ParentName FROM BusinessType AS A INNER JOIN BusinessType AS B ON A.ParentID = B.TypeID WHERE B.TypeID = @TypeID";
view.menuListChildren = new Pagintion(sql, new { TypeID = parentID }, view.pageDataChildren).ToList <BusinessType>();
string table = UserControlExcutor.RenderUserControl("/System/UserControl/BusinessTypeListSecond.ascx", view);
string pagination = UserControlExcutor.RenderUserControl("/System/CommonUserControl/Pagination.ascx", view.pageDataChildren);
return(JsonHelper.ToJson(new { table = table, pagination = pagination }));
}
public string FirstSearch(PaginationData 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.BusinessTypeList.firstSearch()";
view.pageDataParents.OrderBy = pd.OrderBy;
string sql = "SELECT * FROM BusinessType WHERE ParentID IS NULL";
view.menuListParents = new Pagintion(sql, null, view.pageDataParents).ToList <BusinessType>();
string table = UserControlExcutor.RenderUserControl("/System/UserControl/BusinessTypeListFirst.ascx", view);
string pagination = UserControlExcutor.RenderUserControl("/System/CommonUserControl/Pagination.ascx", view.pageDataParents);
return(JsonHelper.ToJson(new { table = table, pagination = pagination }));
}
