public async Task ExecuteAsync(UpdateJobRolePositionCompetencyCommand command, CancellationToken cancellationToken = new CancellationToken())
{
var jobposition = _db.JobPositionCompetencies.First(e => e.JobPositionId == command.JobPositionId);
jobposition.CompetencyId = command.CompetencyId;
jobposition.CompetencyTypeId = command.CompetencyTypeId;
await _db.SaveChangesAsync(cancellationToken);
var jobroleposition = _db.JobRolePositionCompetencies.First(e => e.JobPositionId == command.JobPositionId);
jobroleposition.CompetencyId = command.CompetencyId;
jobroleposition.CompetencyTypeId = command.CompetencyTypeId;
jobroleposition.JobGroupId = command.JobGroupId;
jobroleposition.JobGroupLevelId = command.JobGroupLevelId;
await _db.SaveChangesAsync(cancellationToken);
var jobrolepositionratings = _db.JobRolePositionCompetencyRatings.First(e => e.JobPositionId == command.JobPositionId);
await _db.JobRolePositionCompetencyRatings.AddAsync(new JobRolePositionCompetencyRating()
{
JobPositionId = command.JobPositionId,
CompetencyId = command.CompetencyId,
CompetencyTypeId = command.CompetencyTypeId,
JobGroupId = command.JobGroupId,
JobGroupLevelId = command.JobGroupLevelId,
CompetencyRatingLevelId = command.CompetencyRatingLevelId,
CompetencyLevelRequirementId = command.CompetencyLevelRequirementId
}, cancellationToken);
await _db.SaveChangesAsync(cancellationToken);
}
public async Task ExecuteAsync(AddJobRolePositionCompetencyCommand command, CancellationToken cancellationToken = new CancellationToken())
{
await _db.JobPositionCompetencies.AddAsync(new JobPositionCompetency()
{
JobPositionId = command.JobPositionId,
CompetencyId = command.CompetencyId,
CompetencyTypeId = command.CompetencyTypeId
}, cancellationToken);
await _db.JobRolePositionCompetencies.AddAsync(new JobRolePositionCompetency()
{
JobPositionId = command.JobPositionId,
CompetencyId = command.CompetencyId,
CompetencyTypeId = command.CompetencyTypeId,
JobGroupId = command.JobGroupId,
JobGroupLevelId = command.JobGroupLevelId,
SubJobGroupId = command.SubJobGroupId,
}, cancellationToken);
await _db.JobRolePositionCompetencyRatings.AddAsync(new JobRolePositionCompetencyRating()
{
JobPositionId = command.JobPositionId,
CompetencyId = command.CompetencyId,
CompetencyTypeId = command.CompetencyTypeId,
JobGroupId = command.JobGroupId,
JobGroupLevelId = command.JobGroupLevelId,
CompetencyRatingLevelId = command.CompetencyRatingLevelId,
CompetencyLevelRequirementId = command.CompetencyLevelRequirementId,
SubJobGroupId = command.SubJobGroupId
}, cancellationToken);
await _db.SaveChangesAsync(cancellationToken);
}
private async Task DeleteTables()
{
DeleteTable <CompetencyRatingGroup>();
DeleteTable <CompetencyTypeGroup>();
DeleteTable <JobGroupPosition>();
DeleteTable <JobPositionCompetency>();
DeleteTable <JobRolePositionCertificate>();
DeleteTable <JobRolePositionCompetency>();
DeleteTable <JobRolePositionCompetencyRating>();
DeleteTable <JobRolePositionHLCategory>();
DeleteTable <JobRolePositionLocation>();
DeleteTable <JobRole>();
DeleteTable <Competency>();
DeleteTable <CompetencyType>();
DeleteTable <Certificate>();
DeleteTable <CertificateDescription>();
DeleteTable <JobLocationRegion>();
DeleteTable <JobPosition>();
DeleteTable <JobHLCategory>();
DeleteTable <JobGroupLevel>();
DeleteTable <JobGroup>();
DeleteTable <CompetencyLevelRequirement>();
DeleteTable <CompetencyRatingLevel>();
await _db.SaveChangesAsync();
}
public async Task ExecuteAsync(DeleteJobPositionByIdCommand command, CancellationToken cancellationToken = new CancellationToken())
{
var jobposition = await _db.JobPositions.FindAsync(command.Id);
jobposition.Active = 0;
await _db.SaveChangesAsync(cancellationToken);
}
public async Task ExecuteAsync(UpdateJobCertificateDescriptionCommand command, CancellationToken cancellationToken = new CancellationToken())
{
var jobcertificatedesc = _db.CertificateDescriptions.First(e => e.Id == command.Id);
jobcertificatedesc.DescEng = string.IsNullOrEmpty(command.DescEng) ? string.Empty : command.DescEng;
jobcertificatedesc.DescFre = string.IsNullOrEmpty(command.DescFre) ? string.Empty : command.DescFre;
await _db.SaveChangesAsync(cancellationToken);
}
public async Task ExecuteAsync(UpdateJobPositionCommand command, CancellationToken cancellationToken = new CancellationToken())
{
var jobposition = _db.JobPositions.First(e => e.Id == command.Id);
jobposition.TitleEng = command.TitleEng;
jobposition.TitleFre = command.TitleFre;
jobposition.Active = command.Active;
await _db.SaveChangesAsync(cancellationToken);
}
public async Task ExecuteAsync(DeleteJobCompetencyByIdCommand command, CancellationToken cancellationToken = new CancellationToken())
{
//var competency = await _db.Competencies.FindAsync(command.Id);
//_db.Competencies.Remove(competency);
var jobcompetency = await _db.Competencies.FindAsync(command.Id);
jobcompetency.Active = 0;
await _db.SaveChangesAsync(cancellationToken);
}
public async Task ExecuteAsync(AddJobPositionCommand command, CancellationToken cancellationToken = new CancellationToken())
{
await _db.JobPositions.AddAsync(new JobPosition()
{
TitleEng = command.TitleEng,
TitleFre = command.TitleFre,
}, cancellationToken);
await _db.SaveChangesAsync(cancellationToken);
}
public async Task ExecuteAsync(AddJobCertificateDescriptionCommand command, CancellationToken cancellationToken = new CancellationToken())
{
await _db.CertificateDescriptions.AddAsync(new CertificateDescription()
{
DescEng = command.DescEng,
DescFre = command.DescFre
}, cancellationToken);
await _db.SaveChangesAsync(cancellationToken);
}
public async Task ExecuteAsync(UpdateSimilarPositionsCommand command, CancellationToken cancellationToken = new CancellationToken())
{
var jobposition = _db.SearchSimilarJobs.First(e => e.Position == command.Position);
jobposition.HundredPercent = string.IsNullOrEmpty(command.HundredPercent) ? string.Empty : command.HundredPercent;
jobposition.NinetyPercent = string.IsNullOrEmpty(command.NinetyPercent) ? string.Empty : command.NinetyPercent;
jobposition.EightyPercent = string.IsNullOrEmpty(command.EightyPercent) ? string.Empty : command.EightyPercent;
jobposition.SeventyPercent = string.IsNullOrEmpty(command.SeventyPercent) ? string.Empty : command.SeventyPercent;
await _db.SaveChangesAsync(cancellationToken);
}
public async Task ExecuteAsync(AddJobGroupPositionCommand command, CancellationToken cancellationToken = new CancellationToken())
{
await _db.JobGroupPositions.AddAsync(new JobGroupPosition()
{
JobPositionId = command.JobPositionId,
JobGroupId = command.JobGroupId,
JobGroupLevelId = command.JobGroupLevelId,
SubJobGroupId = command.SubJobGroupId
}, cancellationToken);
await _db.SaveChangesAsync(cancellationToken);
}
public async Task ExecuteAsync(DeleteJobCertificateByIdCommand command, CancellationToken cancellationToken = new CancellationToken())
{
//var certificate = await _db.Certificates.FindAsync(command.Id);
//_db.Certificates.Remove(certificate);
// var certificatedesc = await _db.CertificateDescriptions.FindAsync(command.Id);
// _db.CertificateDescriptions.Remove(certificatedesc);
//await _db.SaveChangesAsync(cancellationToken);
var certificate = await _db.Certificates.FindAsync(command.Id);
certificate.Active = 0;
await _db.SaveChangesAsync(cancellationToken);
}
public async Task ExecuteAsync(AddJobCertificateCommand command, CancellationToken cancellationToken = new CancellationToken())
{
await _db.Certificates.AddAsync(new Certificate()
{
NameEng = command.NameEng,
NameFre = command.NameFre,
DescEng = command.DescEng,
DescFre = command.DescFre,
Active = 1
}, cancellationToken);
await _db.SaveChangesAsync(cancellationToken);
}
public async Task ExecuteAsync(AddSimilarPositionsCommand command, CancellationToken cancellationToken = new CancellationToken())
{
var jobposition = new SearchSimilarJob()
{
Position = command.Position,
HundredPercent = string.IsNullOrEmpty(command.HundredPercent) ? string.Empty : command.HundredPercent,
NinetyPercent = string.IsNullOrEmpty(command.NinetyPercent) ? string.Empty : command.NinetyPercent,
EightyPercent = string.IsNullOrEmpty(command.EightyPercent) ? string.Empty : command.EightyPercent,
SeventyPercent = string.IsNullOrEmpty(command.SeventyPercent) ? string.Empty : command.SeventyPercent
};
await _db.AddAsync(jobposition);
await _db.SaveChangesAsync(cancellationToken);
}
public async Task ExecuteAsync(AddJobRolePositionCertificateCommand command, CancellationToken cancellationToken = new CancellationToken())
{
await _db.JobRolePositionCertificates.AddAsync(new JobRolePositionCertificate()
{
JobPositionId = command.JobPositionId,
CertificateId = command.CertificateId,
JobGroupId = command.JobGroupId,
SubJobGroupId = command.SubJobGroupId,
JobGroupLevelId = command.JobGroupLevelId,
CertificateDescriptionId = command.CertificateDescriptionId
}, cancellationToken);
await _db.SaveChangesAsync(cancellationToken);
}
public async Task <int> HandleAsync(PostJobPositionCommandGetJobPositionIdQuery query, CancellationToken cancellationToken = new CancellationToken())
{
var newjobposition = new JobPosition()
{
TitleEng = query.TitleEng,
TitleFre = query.TitleFre,
Active = 1
};
await _db.JobPositions.AddAsync(newjobposition, cancellationToken);
await _db.SaveChangesAsync(cancellationToken);
return(await _db.JobPositions.Where(e => e.TitleEng == query.TitleEng || e.TitleFre == query.TitleFre)
.Select(e => e.Id)
.FirstOrDefaultAsync(cancellationToken));
}
private async Task Save <T>(bool hasIdentityKey) where T : class
{
var tableName = _db.Model.FindEntityType(typeof(T)).GetTableName();
try
{
if (hasIdentityKey)
{
await _db.Database.ExecuteSqlRawAsync($"SET IDENTITY_INSERT {tableName} ON;");
}
await _db.SaveChangesAsync();
}
finally
{
if (hasIdentityKey)
{
await _db.Database.ExecuteSqlRawAsync($"SET IDENTITY_INSERT {tableName} OFF;");
}
}
}
public async Task ExecuteAsync(UpdateJobCompetencyCommand command, CancellationToken cancellationToken = new CancellationToken())
{
var jobcompetency = _db.Competencies.First(e => e.Id == command.Id);
jobcompetency.NameEng = command.NameEng;
jobcompetency.NameFre = command.NameFre;
jobcompetency.DescEng = string.IsNullOrEmpty(command.DescEng) ? string.Empty : command.DescEng;
jobcompetency.DescFre = string.IsNullOrEmpty(command.DescFre) ? string.Empty : command.DescFre;
jobcompetency.Active = command.Active;
var execlevelinc = command.TypeId == 4 ? 5 : 0;
var crg1 = _db.CompetencyRatingGroups
.Where(e => e.CompetencyId == command.Id && e.CompetencyRatingLevelId == 1 + execlevelinc).FirstOrDefault();
var crg2 = _db.CompetencyRatingGroups
.Where(e => e.CompetencyId == command.Id && e.CompetencyRatingLevelId == 2 + execlevelinc).FirstOrDefault();
var crg3 = _db.CompetencyRatingGroups
.Where(e => e.CompetencyId == command.Id && e.CompetencyRatingLevelId == 3 + execlevelinc).FirstOrDefault();
var crg4 = _db.CompetencyRatingGroups
.Where(e => e.CompetencyId == command.Id && e.CompetencyRatingLevelId == 4 + execlevelinc).FirstOrDefault();
var crg5 = _db.CompetencyRatingGroups
.Where(e => e.CompetencyId == command.Id && e.CompetencyRatingLevelId == 5 + execlevelinc).FirstOrDefault();
var jobcompetencylevel1req = _db.CompetencyLevelRequirements.First(e => e.Id == crg1.CompetencyLevelRequirementId);
var jobcompetencylevel2req = _db.CompetencyLevelRequirements.First(e => e.Id == crg2.CompetencyLevelRequirementId);
var jobcompetencylevel3req = _db.CompetencyLevelRequirements.First(e => e.Id == crg3.CompetencyLevelRequirementId);
var jobcompetencylevel4req = _db.CompetencyLevelRequirements.First(e => e.Id == crg4.CompetencyLevelRequirementId);
var jobcompetencylevel5req = _db.CompetencyLevelRequirements.First(e => e.Id == crg5.CompetencyLevelRequirementId);
jobcompetencylevel1req.DescEng = command.Level1DescEng;
jobcompetencylevel1req.DescFre = command.Level1DescFre;
jobcompetencylevel2req.DescEng = command.Level2DescEng;
jobcompetencylevel2req.DescFre = command.Level2DescFre;
jobcompetencylevel3req.DescEng = command.Level3DescEng;
jobcompetencylevel3req.DescFre = command.Level3DescFre;
jobcompetencylevel4req.DescEng = command.Level4DescEng;
jobcompetencylevel4req.DescFre = command.Level4DescFre;
jobcompetencylevel5req.DescEng = command.Level5DescEng;
jobcompetencylevel5req.DescFre = command.Level5DescFre;
await _db.SaveChangesAsync(cancellationToken);
}
public async Task ExecuteAsync(AddJobCompetencyCommand command, CancellationToken cancellationToken = new CancellationToken())
{
var newCompetency = new Competency()
{
NameEng = command.NameEng,
NameFre = command.NameFre,
DescEng = command.DescEng,
DescFre = command.DescFre,
Active = 1
};
var lr1 = new CompetencyLevelRequirement()
{
DescEng = command.Level1DescEng,
DescFre = command.Level1DescFre,
};
var lr2 = new CompetencyLevelRequirement()
{
DescEng = command.Level2DescEng,
DescFre = command.Level2DescFre
};
var lr3 = new CompetencyLevelRequirement()
{
DescEng = command.Level3DescEng,
DescFre = command.Level3DescFre
};
var lr4 = new CompetencyLevelRequirement()
{
DescEng = command.Level4DescEng,
DescFre = command.Level4DescFre
};
var lr5 = new CompetencyLevelRequirement()
{
DescEng = command.Level5DescEng,
DescFre = command.Level5DescFre
};
await _db.Competencies.AddAsync(newCompetency, cancellationToken);
await _db.CompetencyLevelRequirements.AddAsync(lr1, cancellationToken);
await _db.CompetencyLevelRequirements.AddAsync(lr2, cancellationToken);
await _db.CompetencyLevelRequirements.AddAsync(lr3, cancellationToken);
await _db.CompetencyLevelRequirements.AddAsync(lr4, cancellationToken);
await _db.CompetencyLevelRequirements.AddAsync(lr5, cancellationToken);
await _db.SaveChangesAsync(cancellationToken);
var execlevelinc = command.TypeId == 4 ? 5 : 0;
var typegroup = new CompetencyTypeGroup()
{
CompetencyId = newCompetency.Id,
CompetencyTypeId = command.TypeId
};
await _db.CompetencyTypeGroups.AddAsync(typegroup, cancellationToken);
await _db.CompetencyRatingGroups.AddAsync(new CompetencyRatingGroup()
{
CompetencyId = newCompetency.Id,
CompetencyRatingLevelId = 1 + execlevelinc,
CompetencyLevelRequirementId = lr1.Id
}, cancellationToken);
await _db.CompetencyRatingGroups.AddAsync(new CompetencyRatingGroup()
{
CompetencyId = newCompetency.Id,
CompetencyRatingLevelId = 2 + execlevelinc,
CompetencyLevelRequirementId = lr2.Id,
}, cancellationToken);
await _db.CompetencyRatingGroups.AddAsync(new CompetencyRatingGroup()
{
CompetencyId = newCompetency.Id,
CompetencyRatingLevelId = 3 + execlevelinc,
CompetencyLevelRequirementId = lr3.Id
}, cancellationToken);
await _db.CompetencyRatingGroups.AddAsync(new CompetencyRatingGroup()
{
CompetencyId = newCompetency.Id,
CompetencyRatingLevelId = 4 + execlevelinc,
CompetencyLevelRequirementId = lr4.Id
}, cancellationToken);
await _db.CompetencyRatingGroups.AddAsync(new CompetencyRatingGroup()
{
CompetencyId = newCompetency.Id,
CompetencyRatingLevelId = 5 + execlevelinc,
CompetencyLevelRequirementId = lr5.Id
}, cancellationToken);
await _db.SaveChangesAsync(cancellationToken);
}
