public String SelectCompany() { CompanyListViewData view = new CompanyListViewData(); view.PageData = new BaseFrameWork.Model.PaginationData(); view.PageData.PageIndex = 1; view.PageData.PageSize = 10; view.PageData.PagintionJsFunction = "window.System.SelectCompany.search()"; view.PageData.OrderBy = "Name, CreatedOn"; 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.[Character] = 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"; view.List = new Pagintion(sql, null, view.PageData).ToList <Company>(); return(PageViewHelper.LoadPage("/System/SelectCompany.aspx", view)); }
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 })); }