public IEnumerable <ChartModel> GetLaborForPieChart(string projectNumber, string searchValue, int skip, int take, string orderBy, string dir, List <AdvancedSearchRequest> postValue, DateTime?startDate, DateTime?endDate) { if (startDate.HasValue && endDate.HasValue) { postValue.Add(new AdvancedSearchRequest { Attribute = new AdvancedSearchAttribute { AttributeName = "TransactionDate", AttributeType = (int)ResourceAttributeType.DateTime }, IsEntity = false, Operator = new AdvancedSearchOperator { OperatorName = (int)OperatorName.DateTimeBetween, OperatorType = (int)ResourceAttributeType.DateTime }, Value = startDate, Value2 = endDate }); } var searchSpec = new SearchSpecCP { AdvancedSearchCriteria = postValue, Direction = dir, OrderBy = orderBy, ProjectNumber = projectNumber, SearchText = searchValue, Skip = skip, Take = take }; return(_laborRepository.GetLaborForPieChart(searchSpec)); }
public int GetCount(string projectNumber, string searchValue, List <AdvancedSearchRequest> postValue, DateTime?startDate, DateTime?endDate) { if (startDate.HasValue && endDate.HasValue) { postValue.Add(new AdvancedSearchRequest { Attribute = new AdvancedSearchAttribute { AttributeName = "TransactionDate", AttributeType = (int)ResourceAttributeType.DateTime }, IsEntity = false, Operator = new AdvancedSearchOperator { OperatorName = (int)OperatorName.DateTimeBetween, OperatorType = (int)ResourceAttributeType.DateTime }, Value = startDate, Value2 = endDate }); } var searchSpec = new SearchSpecCP { AdvancedSearchCriteria = postValue, ProjectNumber = projectNumber, SearchText = searchValue }; return(_laborRepository.GetLaborCount(searchSpec)); }
public string OffsetQuery(SearchSpecCP searchSpec) { var str = $@" OFFSET { searchSpec.Skip} ROWS FETCH NEXT { searchSpec.Take} ROWS ONLY"; return(str); }
public int GetCount(string projectNumber, string searchValue, List <AdvancedSearchRequest> postValue) { var searchSpec = new SearchSpecCP { AdvancedSearchCriteria = postValue, ProjectNumber = projectNumber, SearchText = searchValue }; return(_wbsRepository.GetCount(searchSpec)); }
public IEnumerable <ChartModel> GetLaborForBarChart(SearchSpecCP searchSpec) { var queryBuilder = new AdvancedSearchQueryBuilder(searchSpec.AdvancedSearchCriteria.ToList()); var where = WhereAnd(queryBuilder.RawSql, searchSpec); var parameters = (DynamicParameters)queryBuilder.Parameters; parameters.Add("ProjectNumber", searchSpec.ProjectNumber); parameters.Add("searchValue", "%" + searchSpec.SearchText + "%"); var sql = $"select Right('0'+Cast(Month(TransactionDate) as Varchar),2) as Name, Sum(TotalAmount) as Amount from Labor {where} Group By TransactionDate"; return(_context.Connection.Query <ChartModel>(sql, parameters)); }
public int GetLaborCount(SearchSpecCP searchSpec) { var queryBuilder = new AdvancedSearchQueryBuilder(searchSpec.AdvancedSearchCriteria.ToList()); var where = WhereAnd(queryBuilder.RawSql, searchSpec); var parameters = (DynamicParameters)queryBuilder.Parameters; parameters.Add("ProjectNumber", searchSpec.ProjectNumber); parameters.Add("searchValue", "%" + searchSpec.SearchText + "%"); var sql = $"select count(*) from Labor {where}"; return(_context.Connection.ExecuteScalar <int>(sql, parameters)); }
private string WhereAnd(string rawSqlAndConditions, SearchSpecCP searchSpec) { string searchParam = ""; if (!string.IsNullOrEmpty(searchSpec.SearchText)) { searchParam = " AND ( Name LIKE @searchValue or Wbs LIKE @searchValue)"; } var str = $@" WHERE projectNumber = @projectNumber {searchParam} {rawSqlAndConditions}"; return(str); }
public IEnumerable <ChartModel> GetCostForBarChart(SearchSpecCP searchSpec) { var queryBuilder = new AdvancedSearchQueryBuilder(searchSpec.AdvancedSearchCriteria.ToList()); var where = WhereAnd(queryBuilder.RawSql, searchSpec); var parameters = (DynamicParameters)queryBuilder.Parameters; parameters.Add("ProjectNumber", searchSpec.ProjectNumber); parameters.Add("searchValue", "%" + searchSpec.SearchText + "%"); var sql = $"select Period as Name, Sum(Amount) as Amount from Cost {where} Group by Period ORDER BY Cast(Period as int)"; return(_context.Connection.Query <ChartModel>(sql, parameters)); }
public IEnumerable <WbsCP> GetWbs(SearchSpecCP searchSpec) { var queryBuilder = new AdvancedSearchQueryBuilder(searchSpec.AdvancedSearchCriteria.ToList()); var where = WhereAnd(queryBuilder.RawSql, searchSpec); var orderBy = queryBuilder.OrderBy(searchSpec); var offSet = queryBuilder.OffsetQuery(searchSpec); var parameters = (DynamicParameters)queryBuilder.Parameters; parameters.Add("ProjectNumber", searchSpec.ProjectNumber); parameters.Add("searchValue", "%" + searchSpec.SearchText + "%"); var sql = $"select * from Wbs {where} {orderBy} {offSet}"; return(_context.Connection.Query <WbsCP>(sql, parameters)); }
public IEnumerable <WbsCP> GetWbs(string projectNumber, string searchValue, int skip, int take, string orderBy, string dir, List <AdvancedSearchRequest> postValue) { var searchSpec = new SearchSpecCP { AdvancedSearchCriteria = postValue, Direction = dir, OrderBy = orderBy, ProjectNumber = projectNumber, SearchText = searchValue, Skip = skip, Take = take }; return(_wbsRepository.GetWbs(searchSpec)); }
private string WhereAnd(string rawSqlAndConditions, SearchSpecCP searchSpec) { string searchParam = ""; if (!string.IsNullOrEmpty(searchSpec.SearchText)) { searchParam = " AND ( Wbs LIKE @searchValue or Description LIKE @searchValue)"; } var str = ""; if (searchSpec.ProjectNumber != null) { str = $@" WHERE projectNumber = @projectNumber {searchParam} {rawSqlAndConditions}"; } else { str = $@" WHERE 1=1 {searchParam} {rawSqlAndConditions}"; } return(str); }
public string OrderBy(SearchSpecCP searchSpec) { var orderByString = $@" ORDER BY {searchSpec.OrderBy} {searchSpec.Direction}"; return(orderByString); }