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