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 <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 }); }