public O2CV1QueryDto GetQueryByName(string queryName)
{
using (var dc = new CriteriaDataContext())
{
var cq = dc.O2CVQueries.FirstOrDefault(x => x.QueryName == queryName);
if (cq == null)
{
return(null);
}
var qdto = new O2CV1QueryDto
{
CreatedBy = cq.CreatedBy,
CreatedDate = cq.CreatedDate,
Deleted = cq.Deleted,
DeletedBy = cq.DeletedBy,
DeletedDate = cq.DeletedDate,
Description = cq.Description,
QueryId = cq.Id.ToString(),
QueryName = cq.QueryName,
QuerySql = cq.QuerySql
};
return(qdto);
}
}
public static List <O2CV1QueryDto> GetQueries()
{
var queryDtoList = new List <O2CV1QueryDto>();
using (var dc = new CriteriaDataContext())
{
var result = dc.O2CVQueries;
foreach (var cq in dc.O2CVQueries)
{
var qdto = new O2CV1QueryDto();
qdto.CreatedBy = cq.CreatedBy;
qdto.CreatedDate = cq.CreatedDate;
qdto.Deleted = cq.Deleted;
qdto.DeletedBy = cq.DeletedBy;
qdto.DeletedDate = cq.DeletedDate;
qdto.Description = cq.Description;
qdto.QueryId = cq.Id.ToString();
qdto.QueryName = cq.QueryName;
qdto.QuerySql = cq.QuerySql;
queryDtoList.Add(qdto);
}
return(queryDtoList);
}
}
public void AddCriteriaToQuery(O2CV1QueryDto queryDto, CriteriaDto criteriaDto)
{
using (var dc = new CriteriaDataContext())
{
var query = dc.O2CVQueries.Single(q => q.QueryName == queryDto.QueryName);
var criteria = new O2CVQueryCriteria
{
Sequence = criteriaDto.Sequence,
TableName = criteriaDto.TableName,
TableColumn = criteriaDto.TableColumn,
CompareOperator = criteriaDto.CompareOperator,
CompareValue = criteriaDto.CompareValue,
Description = criteriaDto.Description,
Name =
$"{criteriaDto.TableName} {criteriaDto.TableColumn} {criteriaDto.CompareOperator} {criteriaDto.CompareValue}",
DisableBy = criteriaDto.Disabled ? criteriaDto.Createdby : null,
CreatedBy = criteriaDto.Createdby,
Disabled = criteriaDto.Disabled,
CreatedDate = Now
};
if (criteria.Disabled == true)
{
criteria.DisabledDate = Now;
}
query.O2CVQueryCriterias.Add(criteria);
dc.SubmitChanges();
}
}
private void BuildDtosForCriteriaAdd(CountsQueryModel model, out O2CV1QueryDto queryDto, out CriteriaDto criteriaDto)
{
queryDto = new O2CV1QueryDto
{
CreatedBy = Session.GetDataFromSession <string>("LogedUserFullname"),//User.Identity.Name,
Deleted = false,
Description = model.QueryName,
QueryName = model.QueryName,
QueryId = model.QueryId
};
criteriaDto = new CriteriaDto
{
CompareOperator = model.CriteriaModel.SelectedCriteria,
CompareValue = model.CriteriaModel.CriteriaCompareValue,
Createdby = Session.GetDataFromSession <string>("LogedUserFullname"),//User.Identity.Name,
TableName = model.SelectedTable,
Description =
$"{model.SelectedTable} {model.SelectedColumn} {model.CriteriaModel.SelectedCriteria} {model.CriteriaModel.CriteriaCompareValue}",
Disabled = false,
Name =
$"{model.SelectedTable} {model.SelectedColumn} {model.CriteriaModel.SelectedCriteria} {model.CriteriaModel.CriteriaCompareValue}",
TableColumn = model.SelectedColumn
};
}
private void ResetCountsQueryModel(CriteriaDto criteriaDto, O2CV1QueryDto queryDto, CountsQueryModel model)
{
var tableNames = _schemaRepository.GetSchemaTables();
IEnumerable <DropDownItem> temptablelist = tableNames.Select(name => new DropDownItem
{
DropDownDisplay = name,
DropDownValue = name
}).OrderBy(x => x.DropDownDisplay).ToList();
model.SelectedTable = criteriaDto.TableName;
model.QueryId = queryDto.QueryId;
model.QueryName = queryDto.QueryName;
model._tables = GetSelectListItems(temptablelist);
model.QueryId = queryDto.QueryId;
model.CriteriaModel = new CriteriaModel {
_criteria = BuildModelCriteria()
};
ViewBag.temptablelist = temptablelist;
var tableSchemaModels = _schemaRepository.GetSchemaTableColumns(criteriaDto.TableName);
IEnumerable <DropDownItem> tempColumnList = tableSchemaModels.MetaData.Select(col => new DropDownItem
{
DropDownDisplay = col.Name,
DropDownValue = $"{criteriaDto.TableName}.{col.Name}"
}).OrderBy(x => x.DropDownDisplay).ToList();
model._columns = tempColumnList.ToList();
CriteriaRepository criteriaRepository = new CriteriaRepository(_dbConnectionString);
var criteriaThisQuery = criteriaRepository.GetCriteriaForQuery(Convert.ToInt64(queryDto.QueryId));
model.QueryCriteria = CriteriaMapper.MapCriteriaDtoToCriteriaGridViewModel(criteriaThisQuery);
}
private void SaveQuerySqlChanges(string queryId, O2CV1QueryDto queryDto)
{
var sqlFromQueryBuilder = SqlFromQueryBuilder(queryId);
O2CV1Business o2Cv1Business = new O2CV1Business(_dbConnectionString);
queryDto.QuerySql = sqlFromQueryBuilder;
o2Cv1Business.SaveQuery(queryDto);
}
public void CreateQuery(O2CV1QueryDto queryDto)
{
using (var dc = new CriteriaDataContext())
{
var q = new O2CVQuery
{
QueryName = queryDto.QueryName,
QuerySql = queryDto.QuerySql,
CreatedBy = queryDto.CreatedBy,
Deleted = queryDto.Deleted,
Description = queryDto.Description,
CreatedDate = Now
};
dc.O2CVQueries.InsertOnSubmit(q);
dc.SubmitChanges();
}
}
public string CreateNextCriteriaForQuery(O2CV1QueryDto queryDto, CriteriaDto criteriaDto)
{
var criteriaRepository = new CriteriaRepository(_dbConnectionString);
if (criteriaRepository.DoesQueryExist(queryDto.QueryName))
{
var currentCountCriteria =
criteriaRepository.GetCountOfCriteriaForQuery(Convert.ToInt64(queryDto.QueryId));
criteriaDto.Sequence = currentCountCriteria += 1;
criteriaRepository.AddCriteriaToQuery(queryDto, criteriaDto);
}
else
{
criteriaRepository.AddQueryAndFirstCriteriaToQuery(queryDto, criteriaDto);
}
return(criteriaRepository.GetIdOfQuery(queryDto.QueryName));
}
public O2CV1QueryDto SaveQuery(O2CV1QueryDto queryDto)
{
using (var dc = new CriteriaDataContext())
{
var cq = dc.O2CVQueries.FirstOrDefault(x => x.Id == Convert.ToInt64(queryDto.QueryId));
if (cq == null)
{
throw new Exception(
$"Could not find query to save - query id {queryDto.QueryId} query name {queryDto.QueryName}");
}
cq.QueryName = queryDto.QueryName;
cq.QuerySql = queryDto.QuerySql;
cq.Deleted = queryDto.Deleted;
cq.Description = queryDto.Description;
cq.ModifiedBy = queryDto.ModifiedBy;
cq.ModifiedDate = Now;
dc.SubmitChanges();
return(queryDto);
}
}
public void AddQueryAndFirstCriteriaToQuery(O2CV1QueryDto queryDto, CriteriaDto criteriaDto)
{
using (var dc = new CriteriaDataContext())
{
var query = new O2CVQuery
{
QueryName = queryDto.QueryName,
QuerySql = queryDto.QuerySql,
CreatedBy = queryDto.CreatedBy,
Deleted = queryDto.Deleted,
Description = queryDto.Description,
CreatedDate = Now
};
var criteria = new O2CVQueryCriteria
{
Sequence = 1,
TableName = criteriaDto.TableName,
TableColumn = criteriaDto.TableColumn,
Name = criteriaDto.Description,
CompareOperator = criteriaDto.CompareOperator,
CompareValue = criteriaDto.CompareValue,
Description = criteriaDto.Description,
DisableBy = criteriaDto.Disabled ? criteriaDto.Createdby : null,
CreatedBy = criteriaDto.Createdby,
Disabled = criteriaDto.Disabled,
CreatedDate = Now
};
if (criteria.Disabled == true)
{
criteria.DisabledDate = Now;
}
query.O2CVQueryCriterias.Add(criteria);
dc.O2CVQueries.InsertOnSubmit(query);
dc.SubmitChanges();
}
}
public void SaveQuery(O2CV1QueryDto queryDto)
{
var queryRepository = new QueryRepository(_dbConnectionString);
queryRepository.SaveQuery(queryDto);
}
