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