public Dictionary <string, int> FindFilteredCaseCount()
{
try
{
using (var context = new CTDSContext())
{
var a = context.CaseStatus.AsQueryable()
.Join(context.CaseInformation.AsQueryable(),
cs => cs.CaseId, ci => ci.CaseId,
(cs, ci) => new { cs.Status, ci.Priority })
.GroupBy(cs => cs.Status).ToList();
var filteredList = new Dictionary <string, int>();
foreach (var item in a)
{
var processing = item.GroupBy(x => x.Priority).ToList();
foreach (var priority in processing)
{
string status = item.Key.ToString();
string key = status + priority.Key.ToString();
filteredList.Add(key, priority.Count());
}
}
return(filteredList);
}
}
catch (Exception exception)
{
throw exception;
}
}
public DeclarationSeeder()
{
string connectionString = ConfigurationManager.ConnectionStrings["CTDSContext"].ConnectionString;
CTDSContext = new CTDSContext(connectionString);
Random = new Random();
}
public FilterDeclarationsDto GetAllDeclarationsWithQuery(List <QueryDto> queries, int pageNumber, int maxRowsPerPage, string sortBy, bool sortDesc)
{
try
{
using (var context = new CTDSContext())
{
FilterDeclarationsDto filterDeclarationsDto = new FilterDeclarationsDto();
var expression = QueryBuilder(queries);
var filteredDeclarations = context.Declaration
.Where(expression)
.ToList();
filterDeclarationsDto.TotalCount = filteredDeclarations.Count;
var sortedList = new List <Declaration>();
if (sortDesc)
{
sortedList = filteredDeclarations.OrderByDescending(a => a.GetType().GetProperty(sortBy).GetValue(a, null)).ToList();
}
else
{
sortedList = filteredDeclarations.OrderBy(a => a.GetType().GetProperty(sortBy).GetValue(a, null)).ToList();
}
var perPageFilteredDeclarations = sortedList.Skip((pageNumber - 1) * maxRowsPerPage).Take(maxRowsPerPage).ToList();
var perPageFilteredDeclarationDtos = Mapper.DeclarationListToDtoList(perPageFilteredDeclarations).ToList();
filterDeclarationsDto.Declarations = perPageFilteredDeclarationDtos;
return(filterDeclarationsDto);
}
}
catch (Exception e)
{
throw e;
}
}
public List <CaseTableDto> GetAllCasesByStatus(CaseStatusType?status, DateTime startDate, DateTime endDate)
{
List <CaseTableDto> cases = new List <CaseTableDto>();
try
{
using (var context = new CTDSContext())
{
if (status == null)
{
cases = (from mcase in context.Case
join caseStatus in context.CaseStatus on mcase.Id equals caseStatus.CaseId
join caseInformation in context.CaseInformation on mcase.Id equals caseInformation.CaseId
join client in context.Client on mcase.Id equals client.CaseId
join notes in context.Notes on mcase.Id equals notes.CaseId
select new CaseTableDto
{
Id = mcase.Id,
CaseId = mcase.CaseId,
Status = caseStatus.Status,
Priority = caseInformation.Priority,
CreatedOn = mcase.CreatedOn,
Description = caseInformation.Description,
Client = client.ClientIdentifier,
Notes = notes.NotesByCpa
})
.Where(c => startDate <= c.CreatedOn && c.CreatedOn <= endDate)
.ToList();
return(cases);
}
cases = (from mcase in context.Case
join caseStatus in context.CaseStatus on mcase.Id equals caseStatus.CaseId
join caseInformation in context.CaseInformation on mcase.Id equals caseInformation.CaseId
join client in context.Client on mcase.Id equals client.CaseId
join notes in context.Notes on mcase.Id equals notes.CaseId
select new CaseTableDto
{
Id = mcase.Id,
CaseId = mcase.CaseId,
Status = caseStatus.Status,
Priority = caseInformation.Priority,
CreatedOn = mcase.CreatedOn,
Description = caseInformation.Description,
Client = client.ClientIdentifier,
Notes = notes.NotesByCpa
})
.Where(c => c.Status == status && startDate <= c.CreatedOn && c.CreatedOn <= endDate)
.ToList();
return(cases);
}
}
catch (Exception)
{
throw;
}
}
public OpenCasesDto GetAllCasesWithQuery(List <QueryDto> queries, int pageNumber, int maxRowsPerPage, string sortBy, bool sortDesc)
{
try
{
using (var context = new CTDSContext())
{
var expression = QueryBuilder(queries);
var filteredCaseTableDtos = (from mcase in context.Case
join caseStatus in context.CaseStatus on mcase.Id equals caseStatus.CaseId
join caseInformation in context.CaseInformation on mcase.Id equals caseInformation.CaseId
join client in context.Client on mcase.Id equals client.CaseId
join notes in context.Notes on mcase.Id equals notes.CaseId
select new CaseTableDto
{
Id = mcase.Id,
CaseId = mcase.CaseId,
Status = caseStatus.Status,
Priority = caseInformation.Priority,
CreatedOn = mcase.CreatedOn,
Description = caseInformation.Description,
Client = client.ClientIdentifier,
Notes = notes.NotesByCpa
})
.Where(expression)
.ToList();
var sortedList = new List <CaseTableDto>();
if (sortDesc)
{
sortedList = filteredCaseTableDtos.OrderByDescending(a => a.GetType().GetProperty(sortBy).GetValue(a, null)).ToList();
}
else
{
sortedList = filteredCaseTableDtos.OrderBy(a => a.GetType().GetProperty(sortBy).GetValue(a, null)).ToList();
}
var perPageFilteredCaseTableDtos = sortedList.Skip((pageNumber - 1) * maxRowsPerPage).Take(maxRowsPerPage).ToList();
OpenCasesDto openCasesDto = new OpenCasesDto()
{
TotalCount = filteredCaseTableDtos.Count,
Cases = perPageFilteredCaseTableDtos
};
return(openCasesDto);
}
}
catch (Exception e)
{
throw e;
}
}
public int FindCaseCount()
{
try
{
using (var context = new CTDSContext())
{
return(context.Case.Count());
}
}
catch (Exception exception)
{
throw exception;
}
}
public List <T> Find(Expression <Func <T, bool> > predicate)
{
try
{
using (var context = new CTDSContext())
{
var DbSet = context.Set <T>();
return(DbSet.Where(predicate).ToList());
}
}
catch (Exception exception)
{
throw exception;
}
}
public T FindById(int id)
{
try
{
using (var context = new CTDSContext())
{
var DbSet = context.Set <T>();
return(DbSet.Find(id));
}
}
catch (Exception exception)
{
throw exception;
}
}
public int FindCasesInLastSevenDays()
{
DateTime date = DateTime.Now.AddDays(-6);
try
{
using (var context = new CTDSContext())
{
return(context.Case.Where(c => c.CreatedOn >= date).Count());
}
}
catch (Exception)
{
throw;
}
}
public void AddReference(ReferenceDto reference)
{
try
{
using (var context = new CTDSContext())
{
var data = Mapper.DtoToReferenceModel(reference);
context.Reference.AddOrUpdate(d => d.ReferenceId, data);
context.SaveChanges();
}
}
catch (Exception)
{
throw;
}
}
public void Delete(T entity)
{
try
{
using (var context = new CTDSContext())
{
var DbSet = context.Set <T>();
DbSet.Remove(entity);
context.SaveChanges();
}
}
catch (Exception exception)
{
throw exception;
}
}
public void DeleteRange(IEnumerable <T> entityList)
{
try
{
using (var context = new CTDSContext())
{
var DbSet = context.Set <T>();
DbSet.RemoveRange(entityList);
context.SaveChanges();
}
}
catch (Exception exception)
{
throw exception;
}
}
public int GetCount()
{
int count = 0;
try
{
using (var context = new CTDSContext())
{
count = context.Declaration.Count();
}
return(count);
}
catch (Exception)
{
throw;
}
}
public List <DeclarationTableDto> GetAllDeclarationByStatus(String status, DateTime startDate, DateTime endDate)
{
List <DeclarationTableDto> declarations = new List <DeclarationTableDto>();
try
{
using (var context = new CTDSContext())
{
if (status != null)
{
declarations = (from declaration in context.Declaration
select new DeclarationTableDto
{
DecId = declaration.DecId,
DeclarationId = declaration.DeclarationId,
Status = declaration.Status,
CreatedOn = declaration.CreatedOn,
MessageName = declaration.MessageName
})
.Where(c => c.Status == status && startDate <= c.CreatedOn && c.CreatedOn <= endDate)
.ToList();
}
else
{
declarations = (from declaration in context.Declaration
select new DeclarationTableDto
{
DecId = declaration.DecId,
DeclarationId = declaration.DeclarationId,
Status = declaration.Status,
CreatedOn = declaration.CreatedOn,
MessageName = declaration.MessageName
})
.Where(c => startDate <= c.CreatedOn && c.CreatedOn <= endDate)
.ToList();
}
}
return(declarations);
}
catch (Exception)
{
throw;
}
}
public int GetCountForLastSevenDays()
{
int count = 0;
DateTime date = DateTime.Now.AddDays(-6);
try
{
using (var context = new CTDSContext())
{
count = context.Declaration.Where(x => x.CreatedOn >= date).Count();
}
return(count);
}
catch (Exception)
{
throw;
}
}
public IEnumerable <DeclarationDto> GetAllDeclarations()
{
IEnumerable <DeclarationDto> declarationDtoList;
try
{
using (var context = new CTDSContext())
{
var declarationList = context.Declaration.ToList();
declarationDtoList = Mapper.DeclarationListToDtoList(declarationList);
}
return(declarationDtoList);
}
catch (Exception)
{
throw;
}
}
public IEnumerable <MasterDataDto> GetMasterData(String listType)
{
IEnumerable <MasterDataDto> masterDataList;
try
{
using (var context = new CTDSContext())
{
var items = context.MasterData.Where(d => d.Type.Equals(listType)).ToList();
masterDataList = Mapper.ModelListToDtoList(items);
}
return(masterDataList);
}
catch (Exception)
{
throw;
}
}
public IEnumerable <ReferenceDto> GetReferenceData(Guid id)
{
IEnumerable <ReferenceDto> refData;
try
{
using (var context = new CTDSContext())
{
var data = context.Reference.Where(x => x.DeclarationId == id).ToList();
refData = Mapper.ReferenceListToDtoList(data);
}
return(refData);
}
catch (Exception)
{
throw;
}
}
public DeclarationDto GetDeclarationById(Guid id)
{
DeclarationDto declarationDto = new DeclarationDto();
try
{
using (var context = new CTDSContext())
{
var declaration = context.Declaration.Find(id);
declarationDto = Mapper.DeclarationToDto(declaration);
}
return(declarationDto);
}
catch (Exception)
{
throw;
}
}
public T Add(T entity)
{
entity.ModifiedOn = DateTime.Now;
entity.CreatedOn = DateTime.Now;
try
{
using (var context = new CTDSContext())
{
var DbSet = context.Set <T>();
DbSet.Add(entity);
context.SaveChanges();
return(entity);
}
}
catch (Exception exception)
{
throw exception;
}
}
public List <Case> GetAllFilteredCases(int index, string sort)
{
List <Case> cases;
try
{
using (var context = new CTDSContext())
{
int maxRows = 3;
var query = context.Case;
cases = query.CustomOrderBy(sort).Skip((index - 1) * maxRows).Take(maxRows).ToList();
}
return(cases);
}
catch (Exception)
{
throw;
}
}
public IEnumerable <DeclarationDto> GetAllDeclarations(int index, string sort)
{
IEnumerable <DeclarationDto> declarationDtoList;
try
{
using (var context = new CTDSContext())
{
int maxRows = 3;
var query = context.Declaration;
var declarationList = query.CustomOrderBy(sort).Skip((index - 1) * maxRows).Take(maxRows).ToList();
declarationDtoList = Mapper.DeclarationListToDtoList(declarationList);
}
return(declarationDtoList);
}
catch (Exception)
{
throw;
}
}
public bool SendToCustom(DeclarationDto declarationDto)
{
bool result = false;
try
{
using (var context = new CTDSContext())
{
var declaration = Mapper.DtoToDeclaration(declarationDto);
context.Declaration.AddOrUpdate(declaration);
context.SaveChanges();
result = true;
}
return(result);
}
catch (Exception)
{
throw;
}
}
public void AddRange(IEnumerable <T> entityList)
{
foreach (var item in entityList)
{
item.CreatedOn = DateTime.Now;
item.ModifiedOn = DateTime.Now;
}
try
{
using (var context = new CTDSContext())
{
var DbSet = context.Set <T>();
DbSet.AddRange(entityList);
context.SaveChanges();
}
}
catch (Exception exception)
{
throw exception;
}
}
public bool EditDeclaration(DeclarationDto declarationDto)
{
bool response;
try
{
using (var context = new CTDSContext())
{
//_context.Declaration.Log = s => { System.Diagnostics.Debug.WriteLine(s); };
var declaration = Mapper.DtoToDeclaration(declarationDto);
context.Declaration.AddOrUpdate(declaration);
context.SaveChanges();
response = true;
}
return(response);
}
catch (Exception)
{
throw;
}
}
public Guid AddDeclaration(DeclarationDto declarationDto)
{
Guid success;
try
{
using (var context = new CTDSContext())
{
var declaration = Mapper.DtoToDeclaration(declarationDto);
var newDeclaration = context.Declaration.Add(declaration);
context.SaveChanges();
success = newDeclaration.DeclarationId;
}
return(success);
}
catch (Exception)
{
throw;
}
}
public T Update(T entity)
{
try
{
using (var context = new CTDSContext())
{
var DbSet = context.Set <T>();
entity.ModifiedOn = DateTime.Now;
if (entity.CreatedOn == DateTime.MinValue)
{
entity.CreatedOn = DateTime.Now;
}
DbSet.AddOrUpdate(entity);
context.SaveChanges();
return(entity);
}
}
catch (Exception exception)
{
throw exception;
}
}
public List <int> FindCasesPerDayLastWeek()
{
List <int> caseCountList = new List <int>();
DateTime date = DateTime.Now.AddDays(-6);
try
{
using (var context = new CTDSContext())
{
for (DateTime i = date.Date; i <= DateTime.Now.Date; i = i.AddDays(1).Date)
{
int count = context.Case.Where(c => c.CreatedOn == i).Count();
caseCountList.Add(count);
}
return(caseCountList);
}
}
catch (Exception exception)
{
throw exception;
}
}
public List <int> FindPerDayDeclarationCount()
{
List <int> declarationCountList = new List <int>();
DateTime date = DateTime.Now.AddDays(-6);
try
{
using (var context = new CTDSContext())
{
for (DateTime i = date.Date; i <= DateTime.Now.Date; i = i.AddDays(1).Date)
{
int count = context.Declaration.Where(c => c.CreatedOn == i).Count();
declarationCountList.Add(count);
}
return(declarationCountList);
}
}
catch (Exception)
{
throw;
}
}
public FilterDeclarationsDto GetAllDeclarationsWithQuery(List <QueryDto> queries, int pageNumber, int maxRowsPerPage)
{
try
{
using (var context = new CTDSContext())
{
FilterDeclarationsDto filterDeclarationsDto = new FilterDeclarationsDto();
var expression = QueryBuilder(queries);
var filteredDeclarations = context.Declaration
.Where(expression)
.ToList();
filterDeclarationsDto.TotalCount = filteredDeclarations.Count;
var perPageFilteredDeclarations = filteredDeclarations.Skip((pageNumber - 1) * maxRowsPerPage).Take(maxRowsPerPage).ToList();
var perPageFilteredDeclarationDtos = Mapper.DeclarationListToDtoList(perPageFilteredDeclarations).ToList();
filterDeclarationsDto.Declarations = perPageFilteredDeclarationDtos;
return(filterDeclarationsDto);
}
}
catch (Exception e)
{
throw e;
}
}