public async Task <EmployeeVm> Handle(Query request, CancellationToken cancellationToken)
{
var student = await _context.GetParameterValue <string>("SuUserType", "Student", cancellationToken);
StringBuilder sql = new StringBuilder();
sql.AppendLine("select e.company_code as person_company,");
if (request.UserType == student)
{
sql.AppendLine(" e.student_id ");
}
else
{
sql.AppendLine(" e.employee_code ");
}
sql.AppendLine("from su_user_type t ");
if (request.UserType == student)
{
sql.AppendLine("inner join sh_student e on e.company_code = t.company_code ");
sql.AppendLine("and e.student_id = t.student_id ");
}
else
{
sql.AppendLine("inner join db_employee e on e.company_code = t.company_code ");
sql.AppendLine("and e.employee_code = t.employee_code ");
}
sql.AppendLine("where t.user_id = @User");
var emp = await _context.QueryFirstOrDefaultAsync <EmployeeVm>(sql.ToString(), new { User = request.UserId }, cancellationToken);
return(emp);
}
public async Task <DbBuilding> Handle(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("select b.*, b.xmin as \"rowVersion\" ");
sql.AppendLine("from db_building b");
sql.AppendLine("where b.building_id = @BuildingId");
sql.AppendLine("AND b.company_code = @CompanyCode ");
var subj = await _context.QueryFirstOrDefaultAsync <DbBuildingVm>(sql.ToString(), new { CompanyCode = _user.Company, BuildingId = request.BuildingId }, cancellationToken);
StringBuilder sqlRoom = new StringBuilder();
sqlRoom.AppendLine("select r.*, r.xmin as \"rowVersion\" ");
sqlRoom.AppendLine("from db_room r ");
sqlRoom.AppendLine("where r.building_id = @BuildingId");
subj.DbRoom = await _context.QueryAsync <DbRoom>(sqlRoom.ToString(), new { BuildingId = request.BuildingId }, cancellationToken);
StringBuilder sqlPrivilegeBuilding = new StringBuilder();
sqlPrivilegeBuilding.AppendLine("select pb.*, pb.xmin as \"rowVersion\" ");
sqlPrivilegeBuilding.AppendLine("from db_privilege_building pb ");
sqlPrivilegeBuilding.AppendLine("where pb.building_id = @BuildingId");
subj.DbPrivilegeBuilding = await _context.QueryAsync <DbPrivilegeBuilding>(sqlPrivilegeBuilding.ToString(), new { BuildingId = request.BuildingId }, cancellationToken);
if (subj == null)
{
throw new RestException(HttpStatusCode.NotFound, "Message.NotFound");
}
return(subj);
}
public async Task <Division> Handle(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("select company_code as \"companyCode\",");
sql.AppendLine(" case @Language when 'th' then company_name_tha else coalesce(company_name_eng, company_name_tha) end as \"companyName\",");
sql.AppendLine(" xmin as \"rowVersion\"");
sql.AppendLine("from su_company");
sql.AppendLine("where company_code = @CompanyCode");
var company = await _context.QueryFirstOrDefaultAsync <Company>(sql.ToString(), new { Language = this._user.Language, CompanyCode = request.CompanyCode }, cancellationToken);
sql = new StringBuilder();
sql.AppendLine("select div1.company_code as \"companyCode\",");
sql.AppendLine(" div1.div_code as \"divCode\",");
sql.AppendLine(" div1.div_name_tha as \"divNameTha\",");
sql.AppendLine(" div1.div_name_eng as \"divNameEng\",");
sql.AppendLine(" case @Language when 'th' then div2.div_name_tha else coalesce(div2.div_name_eng, div2.div_name_tha) end as \"divParent\",");
sql.AppendLine(" case status.status_value when 'N'");
sql.AppendLine(" then null");
sql.AppendLine(" else case @Language when 'th' then status.status_desc_tha else coalesce(status.status_desc_eng, status.status_desc_tha) end");
sql.AppendLine(" end as \"divType\",");
sql.AppendLine(" case div1.div_type when 'L' then concat(locat.location_code, ' : ', case @Language when 'th' then locat.location_name_tha else coalesce(locat.location_name_eng, locat.location_name_tha) end)");
sql.AppendLine(" when 'F' then concat(fac.fac_code, ' : ', case @Language when 'th' then fac.fac_name_tha else coalesce(fac.fac_name_eng, fac.fac_name_tha) end)");
sql.AppendLine(" when 'P' then concat(program.program_code, ' : ', case @Language when 'th' then program.program_name_tha else coalesce(program.program_name_eng, program.program_name_tha) end, ' ', fac.fac_code, ' : ', case @Language when 'th' then fac.fac_name_tha else coalesce(fac.fac_name_eng, fac.fac_name_tha) end)");
sql.AppendLine(" else null end as \"divTypeDetail\",");
sql.AppendLine(" div1.xmin as \"rowVersion\"");
sql.AppendLine("from su_division as div1");
sql.AppendLine("left join su_division as div2");
sql.AppendLine("on div1.company_code = div2.company_code");
sql.AppendLine(" and div1.div_parent = div2.div_code");
sql.AppendLine("left join db_status as status");
sql.AppendLine("on status.status_value = div1.div_type");
sql.AppendLine(" and status.table_name = 'su_division'");
sql.AppendLine(" and status.column_name = 'div_type'");
sql.AppendLine("left join db_location as locat");
sql.AppendLine("on locat.company_code = div1.company_code");
sql.AppendLine(" and locat.location_code = div1.location_code");
//sql.AppendLine(" and div1.div_type = 'L'");
sql.AppendLine("left join db_fac as fac");
sql.AppendLine("on fac.company_code = div1.company_code");
sql.AppendLine(" and fac.fac_code = div1.fac_code");
//sql.AppendLine(" and div1.div_type = 'F'");
sql.AppendLine("left join db_program as program");
sql.AppendLine("on program.company_code = div1.company_code");
sql.AppendLine(" and program.program_code = div1.program_code");
//sql.AppendLine(" and div1.div_type = 'P'");
sql.AppendLine("where div1.company_code = @CompanyCode");
if (!string.IsNullOrWhiteSpace(request.Keyword))
{
sql.AppendLine(" and concat(div1.div_code, div1.div_name_tha, div1.div_name_eng) ilike concat('%', @Keyword, '%')");
}
var divisions = await _context.GetPage(sql.ToString(), new { Language = this._user.Language, CompanyCode = request.CompanyCode, Keyword = request.Keyword }, (RequestPageQuery)request, cancellationToken);
return(new Division {
Company = company, Divisions = divisions
});
}
public async Task <PersonalVm> Handle(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" select e.employee_code as personalCode, get_name(@Lang,e.t_name_concat,e.e_name_concat) as personalName ");
sql.AppendLine(" from su_user_type t ");
sql.AppendLine(" inner join db_employee e on e.company_code = t.company_code ");
sql.AppendLine(" and e.employee_code = t.employee_code ");
sql.AppendLine(" where t.user_id = @UserId ");
sql.AppendLine(" limit 1 ");
return(await _context.QueryFirstOrDefaultAsync <PersonalVm>(sql.ToString(), new { UserId = _user.UserId, Lang = _user.Language }, cancellationToken));
}
public async Task <DbMajor> Handle(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT dmj.company_code AS \"companyCode\", dmj.major_code AS \"majorCode\" ");
sql.AppendLine(", dmj.fac_code AS \"facCode\", dmj.program_code AS \"programCode\" ");
sql.AppendLine(", dmj.major_name_tha AS \"majorNameTha\", dmj.major_name_eng AS \"majorNameEng\" ");
sql.AppendLine(", dmj.major_short_name_tha AS \"majorShortNameTha\", dmj.major_short_name_eng AS \"majorShortNameEng\" ");
sql.AppendLine(", dmj.format_code AS \"formatCode\", dmj.major_code_mua AS \"majorCodeMua\" ");
sql.AppendLine(", dmj.active, dmj.xmin as \"rowVersion\" ");
sql.AppendLine("FROM db_major dmj ");
sql.AppendLine("WHERE dmj.program_code = @ProgramCode ");
sql.AppendLine("AND dmj.company_code = @CompanyCode ");
sql.AppendLine("AND dmj.major_code = @MajorCode ");
sql.AppendLine("AND dmj.fac_code = @FacCode ");
var subj = await _context.QueryFirstOrDefaultAsync <DbMajorVm>(sql.ToString()
, new { CompanyCode = _user.Company, ProgramCode = request.ProgramCode, MajorCode = request.MajorCode
, FacCode = request.FacCode }, cancellationToken);
StringBuilder sqlSub = new StringBuilder();
sqlSub.AppendLine("SELECT dpf.company_code AS \"companyCode\", dpf.major_code AS \"majorCode\" ");
sqlSub.AppendLine(", dpf.fac_code AS \"facCode\", dpf.program_code AS \"programCode\" ");
sqlSub.AppendLine(", dpf.pro_code AS \"proCode\", dpf.pro_name_tha AS \"proNameTha\" ");
sqlSub.AppendLine(", dpf.pro_name_eng AS \"proNameEng\", dpf.pro_short_name_tha AS \"proShortNameTha\" ");
sqlSub.AppendLine(", dpf.pro_short_name_eng AS \"proShortNameEng\", dpf.format_code AS \"formatCode\" ");
sqlSub.AppendLine(", dpf.active, dpf.xmin as \"rowVersion\" ");
sqlSub.AppendLine("FROM db_professional dpf ");
sqlSub.AppendLine("WHERE dpf.program_code = @ProgramCode ");
sqlSub.AppendLine("AND dpf.company_code = @CompanyCode ");
sqlSub.AppendLine("AND dpf.major_code = @MajorCode ");
sqlSub.AppendLine("AND dpf.fac_code = @FacCode ");
sqlSub.AppendLine("ORDER BY dpf.major_code, dpf.fac_code, dpf.program_code ");
subj.dbProfessional = await _context.QueryAsync <DbProfessionalVm>(sqlSub.ToString()
, new { CompanyCode = _user.Company, ProgramCode = request.ProgramCode
, MajorCode = request.MajorCode, FacCode = request.FacCode }, cancellationToken);
if (subj == null)
{
throw new RestException(HttpStatusCode.NotFound, "Message.NotFound");
}
return(subj);
}
public async Task <DbFacProgram> Handle(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" SELECT dfp.company_code AS \"companyCode\" ");
sql.AppendLine(" , dfp.fac_code AS \"facCode\" ");
sql.AppendLine(" , dfp.program_code AS \"programCode\" ");
sql.AppendLine(" , p.ref_group_id AS \"refGroupId\" ");
sql.AppendLine(" , p.curr_id AS \"currId\" ");
sql.AppendLine(" , p.program_id AS \"programId\" ");
sql.AppendLine(" , p.isced_id AS \"iscedId\" ");
sql.AppendLine(" , p.program_code_mua AS \"programCodeMua\" ");
sql.AppendLine(" , dfp.active ");
sql.AppendLine(" , dfp.xmin as \"rowVersion\" ");
sql.AppendLine(" FROM db_fac_program dfp ");
sql.AppendLine(" JOIN db_program p ");
sql.AppendLine(" ON p.company_code = dfp.company_code ");
sql.AppendLine(" AND p.program_code = dfp.program_code ");
sql.AppendLine(" WHERE dfp.program_code = @ProgramCode ");
sql.AppendLine(" AND dfp.company_code = @CompanyCode ");
sql.AppendLine(" AND dfp.fac_code = @FacCode ");
var subj = await _context.QueryFirstOrDefaultAsync <DbFacProgramVm>(sql.ToString()
, new { CompanyCode = _user.Company, FacCode = request.FacCode, ProgramCode = request.ProgramCode }, cancellationToken);
StringBuilder sqlSub = new StringBuilder();
sqlSub.AppendLine("SELECT dfpd.company_code AS \"companyCode\" ");
sqlSub.AppendLine(", dfpd.fac_code AS \"facCode\", dfpd.program_code AS \"programCode\", dfpd.department_code AS \"departmentCode\" ");
sqlSub.AppendLine(", dfpd.active, dfpd.xmin as \"rowVersion\" ");
sqlSub.AppendLine("FROM db_fac_program_detail dfpd ");
sqlSub.AppendLine("WHERE dfpd.program_code = @ProgramCode ");
sqlSub.AppendLine("AND dfpd.company_code = @CompanyCode ");
sqlSub.AppendLine("AND dfpd.fac_code = @FacCode ");
subj.dbFacProgramDetail = await _context.QueryAsync <DbFacProgramDetailVm>(sqlSub.ToString()
, new { CompanyCode = _user.Company, FacCode = request.FacCode, ProgramCode = request.ProgramCode }, cancellationToken);
if (subj == null)
{
throw new RestException(HttpStatusCode.NotFound, "Message.NotFound");
}
return(subj);
}
public async Task <SuProfileVm> Handle(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT profile_code AS \"profileCode\",");
sql.AppendLine(" profile_desc AS \"profileDesc\",");
sql.AppendLine(" active,");
sql.AppendLine(" xmin AS \"rowVersion\"");
sql.AppendLine("FROM su_profile");
sql.AppendLine("WHERE profile_code = @ProfileCode");
var profile = await _context.QueryFirstOrDefaultAsync <SuProfileVm>(sql.ToString(), new { ProfileCode = request.ProfileCode }, cancellationToken);
if (profile == null)
{
throw new RestException(HttpStatusCode.NotFound, "Message.NotFound");
}
sql = new StringBuilder();
sql.AppendLine("SELECT a.profile_code AS \"profileCode\",");
sql.AppendLine(" a.menu_code AS \"menuCode\",");
sql.AppendLine(" CASE @Language WHEN @Tha THEN b.menuNameTha ELSE COALESCE(b.menuNameEng, b.menuNameTha) END AS \"menuName\",");
sql.AppendLine(" a.xmin AS \"rowVersion\"");
sql.AppendLine("FROM su_menu_profile AS a");
sql.AppendLine("INNER JOIN (SELECT x.menu_code,");
sql.AppendLine(" x.menu_name AS menuNameTha,");
sql.AppendLine(" y.menu_name AS menuNameEng,");
sql.AppendLine(" x.xmin AS rowVersionTha,");
sql.AppendLine(" y.xmin AS rowVersionEng");
sql.AppendLine(" FROM su_menu_label AS x");
sql.AppendLine(" LEFT JOIN (SELECT menu_code, menu_name, xmin FROM su_menu_label WHERE lang_code = @Eng::Lang) AS y");
sql.AppendLine(" ON x.menu_code = y.menu_code");
sql.AppendLine(" WHERE x.lang_code = @Tha::Lang) AS b");
sql.AppendLine("ON a.menu_code = b.menu_code");
sql.AppendLine("WHERE a.profile_code = @ProfileCode");
sql.AppendLine("ORDER BY a.profile_code");
profile.MenuProfiles = await _context.QueryAsync <SuMenuProfileVm>(sql.ToString(), new { Language = this._user.Language, Tha = "th", Eng = "en", ProfileCode = request.ProfileCode }, cancellationToken);
return(profile);
}
public async Task <DbDegreeSub> Handle(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("select spt.*,spt.xmin as \"rowVersion\" ");
sql.AppendLine("from db_degree_sub as spt");
sql.AppendLine("where spt.sub_degree_id = @SubDegreeId");
var subj = await _context.QueryFirstOrDefaultAsync <DbDegreeSubVm>(sql.ToString(), new { SubDegreeId = request.SubDegreeId }, cancellationToken);
StringBuilder sqlDegreeSub = new StringBuilder();
sqlDegreeSub.AppendLine("select spp.*, spp.xmin as \"rowVersion\" ");
sqlDegreeSub.AppendLine("from db_degree_sub_edu_group as spp ");
sqlDegreeSub.AppendLine("where spp.sub_degree_id = @SubDegreeId");
subj.DbDegreeSubEduGroup = await _context.QueryAsync <DbDegreeSubEduGroup>(sqlDegreeSub.ToString(), new { SubDegreeId = request.SubDegreeId }, cancellationToken);
if (subj == null)
{
throw new RestException(HttpStatusCode.NotFound, "Message.NotFound");
}
return(subj);
}
public async Task <SuEmailTemplate> GetTemplate(string templateCode)
{
return(await _context.QueryFirstOrDefaultAsync <SuEmailTemplate>("SELECT subject,content FROM su_email_template WHERE email_template_code = @Code", new { Code = templateCode }));
}
