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