public Leave GetMyLeaveById(Guid leaveId, string userName)
{
using (var conn = ReadModelDbContext.GetConnection())
{
return(conn.Query <Leave>("select * from LeaveReadModel where Id = @leaveId and Applicant=@userName", new { leaveId, userName }).FirstOrDefault());
}
}
public IEnumerable <Leave> GetLeavesByUserName(string applicant)
{
using (var conn = ReadModelDbContext.GetConnection())
{
return(conn.Query <Leave>("select * from LeaveReadModel where Applicant = @Applicant order by AppliedOn desc", new { Applicant = applicant }));
}
}
public Leave GetLeaveById(Guid leaveId)
{
using (var conn = ReadModelDbContext.GetConnection())
{
return(conn.Query <Leave>("select * from LeaveReadModel where Id = @leaveId ", new { leaveId }).FirstOrDefault());
}
}
public IEnumerable <StatusItem> LeavesGroupByStatus(DateTime since)
{
using (var conn = ReadModelDbContext.GetConnection())
{
return(conn.Query <StatusItem>("select Status, count(1) as Count from LeaveReadModel where StartDate>@since group by Status", new { Status = Leave.StatusEnum.Approved, since }));
}
}
public IEnumerable <ReasonItem> ApprovedLeavesGroupByReason(DateTime since)
{
using (var conn = ReadModelDbContext.GetConnection())
{
return(conn.Query <ReasonItem>("select Reason, count(1) as Count from LeaveReadModel where Status = @Status and StartDate>@since group by Reason", new { Status = Leave.StatusEnum.Approved, since }));
}
}
public IEnumerable <Leave> OverlappedLeaves(DateTime startDate, DateTime endDate)
{
using (var conn = ReadModelDbContext.GetConnection())
{
return(conn.Query <Leave>("select * from LeaveReadModel where Status = @Status and @endDate >= StartDate and @startDate <= EndDate order by AppliedOn desc", new { Status = Leave.StatusEnum.Approved, startDate, endDate }));
}
}
public IEnumerable <Leave> ListLeavesToEvaluate()
{
using (var conn = ReadModelDbContext.GetConnection())
{
return(conn.Query <Leave>("select * from LeaveReadModel where Status = @Status order by AppliedOn desc", new { Status = Leave.StatusEnum.Applied }));
}
}
public void SyncLeave(Guid leaveId)
{
var leave = EventStore.Get(leaveId);
using (var conn = ReadModelDbContext.GetConnection())
{
conn.Execute(@"
begin tran
delete LeaveReadModel where Id = @Id
insert into
LeaveReadModel(Id, Applicant, Evaluator, StartDate, EndDate, ApplicantComment, EvaluatorComment, Reason, Status, AppliedOn, EvaluatedOn, WorkingDays)
values(@Id, @Applicant, @Evaluator, @StartDate, @EndDate, @ApplicantComment, @EvaluatorComment, @Reason, @Status, @AppliedOn, @EvaluatedOn, @WorkingDays)
commit tran
" , leave);
}
}
public void Init()
{
using (var conn = ReadModelDbContext.GetConnection())
conn.Execute(@"
if not exists (select * from sys.tables where name='leaveReadModel')
begin
create table leaveReadModel (
Id uniqueidentifier primary key,
Applicant varchar(200),
Evaluator varchar(200),
StartDate datetime2,
EndDate datetime2,
Reason int,
Status int,
AppliedOn datetime2,
EvaluatedOn datetime2,
ApplicantComment varchar(1024),
EvaluatorComment varchar(1024),
WorkingDays int
)
end
" );
}
