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)); }
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 <dynamic> GetCostForPieChart(string projectNumber, string searchValue, int skip, int take, string orderBy, string dir, List <AdvancedSearchRequest> postValue, DateTime?startDate, DateTime?endDate) { string searchParam = ""; string searchString = ""; if (startDate.HasValue && endDate.HasValue) { int stYear = startDate.Value.Year; int stMonth = startDate.Value.Month; int enYear = endDate.Value.Year; int enMonth = endDate.Value.Month; postValue.Add(new AdvancedSearchRequest { Attribute = new AdvancedSearchAttribute { AttributeName = "FiscalYear", AttributeType = 4 }, Operator = new AdvancedSearchOperator { OperatorName = 13, OperatorType = 4 }, Value = stYear, Value2 = enYear }); postValue.Add(new AdvancedSearchRequest { Attribute = new AdvancedSearchAttribute { AttributeName = "Period", AttributeType = 4 }, Operator = new AdvancedSearchOperator { OperatorName = 13, OperatorType = 4 }, Value = stMonth, Value2 = enMonth }); } var queryBuilder = new AdvancedSearchQueryBuilder(postValue); var query = queryBuilder.getQuery(); var _builder = new SqlBuilder(); var selector = _builder.AddTemplate(" /**where**/"); foreach (dynamic d in query) { _builder.Where(d.sql, d.value); } var where = selector.RawSql.Replace("WHERE", " AND "); var parameters = (Dapper.DynamicParameters)selector.Parameters; if (!string.IsNullOrEmpty(searchValue)) { searchString = "%" + searchValue + "%"; searchParam = " AND ( Description LIKE @searchValue or VendorName LIKE @searchValue or PO_ID like @searchValue)"; } parameters.Add("ProjectNumber", projectNumber); parameters.Add("searchValue", searchString); var sql = $"select Description, Sum(Amount) as Amount from Cost WHERE projectNumber=@projectNumber {searchParam} {where} GROUP BY Description ORDER BY Description"; return(_context.Connection.Query <CostCP>(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 <ProjectCP> GetProjects(Guid UserGuid, string searchValue, int skip, int take, string orderBy, string dir, List <AdvancedSearchRequest> postValue) { var queryBuilder = new AdvancedSearchQueryBuilder(postValue); var query = queryBuilder.getQuery(); var _builder = new SqlBuilder(); var selector = _builder.AddTemplate(" /**where**/"); foreach (dynamic d in query) { _builder.Where(d.sql, d.value); } var sql = "select * from Projects"; return(_context.Connection.Query <ProjectCP>(sql, new { UserGuid })); }
private WhereBuildReturn ProjectModWhereBuilder(string ProjectNumber, string searchValue, List <AdvancedSearchRequest> postValue) { var queryBuilder = new AdvancedSearchQueryBuilder(postValue); var query = queryBuilder.getQuery(); var _builder = new SqlBuilder(); var selector = _builder.AddTemplate(" /**where**/"); foreach (dynamic d in query) { _builder.Where(d.sql, d.value); } var where = selector.RawSql.Replace("WHERE", " AND "); var parameters = (Dapper.DynamicParameters)selector.Parameters; parameters.Add("PROJ_ID", ProjectNumber); string sql = $"WHERE PROJ_ID='{ProjectNumber}' {where}"; return(new WhereBuildReturn { where = sql, parameters = parameters }); }