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 <OrganizationVm> Handle(Query request, CancellationToken cancellationToken)
{
var organize = new OrganizationVm();
if (request.Name == "company")
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" select c.company_code as value,case @Lang when 'th' then company_name_tha else coalesce(company_name_eng ,company_name_tha) end as text ");
sql.AppendLine(" ,p.is_default as default ");
sql.AppendLine(" from su_company c ");
sql.AppendLine(" inner join su_user_permission p on p.company_code = c.company_code ");
sql.AppendLine(" where p.user_id = @UserId ");
organize.Companies = await _context.QueryAsync <dynamic>(sql.ToString(), new { UserId = _user.UserId, Lang = request.Lang }, cancellationToken);
}
else if (request.Name == "division")
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" select d.div_code as value ");
sql.AppendLine(" ,concat(d.div_code,' : ',case @Lang when 'th' then div_name_tha else coalesce(div_name_eng ,div_name_tha) end) as text ");
sql.AppendLine(" from su_division d ");
sql.AppendLine(" inner join su_user_division u on u.company_code = d.company_code ");
sql.AppendLine(" and u.div_code = d.div_code ");
sql.AppendLine(" where u.user_id = @UserId ");
sql.AppendLine(" and u.company_code = @CompanyCode ");
sql.AppendLine(" order by value ");
organize.Divisions = await _context.QueryAsync <dynamic>(sql.ToString(), new { CompanyCode = request.CompanyCode, UserId = _user.UserId, Lang = request.Lang }, cancellationToken);
}
return(organize);
}
private Task <IEnumerable <dynamic> > GetEducation(CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT get_name(@lang, degree_name_tha, degree_name_eng) AS \"text\" ");
sql.AppendLine(" ,degree_id AS \"value\" ");
sql.AppendLine(" ,active AS \"active\" ");
sql.AppendLine("FROM db_degree ");
sql.AppendLine("ORDER BY degree_id");
return(_context.QueryAsync <dynamic>(sql.ToString(), new { lang = _user.Language }, cancellationToken));
}
private Task <IEnumerable <dynamic> > GetFacCode(CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT CONCAT(fac_code,' : ',get_name(@lang, fac_name_tha, fac_name_eng)) AS text, fac_code AS value");
sql.AppendLine(", active AS active ");
sql.AppendLine("FROM db_fac ");
sql.AppendLine("WHERE company_code = @CompanyCode ");
sql.AppendLine("ORDER BY fac_code::numeric ");
return(_context.QueryAsync <dynamic>(sql.ToString(), new { lang = _user.Language, CompanyCode = _user.Company }, cancellationToken));
}
private Task <IEnumerable <dynamic> > GetEducationTypeCode(CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT CONCAT(education_type_code,' : ',get_name(@Lang,education_type_name_tha,education_type_name_eng)) AS \"text\" ");
sql.AppendLine(" ,education_type_code AS \"value\" ");
sql.AppendLine(" ,active AS \"active\" ");
sql.AppendLine("FROM db_education_type");
sql.AppendLine("WHERE company_code = @CompanyCode ");
sql.AppendLine("ORDER BY education_type_code");
return(_context.QueryAsync <dynamic>(sql.ToString(), new { lang = _user.Language, CompanyCode = _user.Company }, cancellationToken));
}
private Task <IEnumerable <dynamic> > GetLocation(CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" SELECT CONCAT(location_code,' : ',get_name(@lang,location_name_tha,location_name_eng)) as \"text\" ");
sql.AppendLine(" ,location_code as \"value\" ");
sql.AppendLine(" ,active as \"active\" ");
sql.AppendLine(" FROM db_location ");
sql.AppendLine(" WHERE company_code = @Company ");
sql.AppendLine(" ORDER BY 1 ");
return(_context.QueryAsync <dynamic>(sql.ToString(), new { Company = _user.Company, lang = _user.Language }, cancellationToken));
}
public async Task <UserVm> Handle(Query request, CancellationToken cancellationToken)
{
var user = await _context.Set <SuUser>().Where(o => o.Id == request.Id).ProjectTo <UserVm>(_mapper.ConfigurationProvider).AsNoTracking().FirstOrDefaultAsync(cancellationToken);
if (user == null)
{
throw new RestException(HttpStatusCode.NotFound, "message.NotFound");
}
user.Profiles = user.Profiles.OrderBy(o => o.ProfileCode).ToList();
user.PasswordHash = null;
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT p.user_id as id");
sql.AppendLine(" ,p.company_code as companyCode ");
sql.AppendLine(" ,case 'th' when 'th' then c.company_name_tha else coalesce(c.company_name_eng,c.company_name_tha ) end as companyName");
sql.AppendLine(" ,is_default as isDefault ");
sql.AppendLine(" ,p.xmin as rowVersion");
sql.AppendLine(" from su_user_permission p ");
sql.AppendLine(" inner join su_company c on c.company_code = p.company_code ");
sql.AppendLine(" where p.user_id = @Id ");
user.Permissions = await _context.QueryAsync <UserPermissionVm>(sql.ToString(), new { Id = user.Id }, cancellationToken);
return(user);
}
private Task <IEnumerable <dynamic> > GetEducation(CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT CONCAT(education_type_level,' : ',get_name(@lang, education_type_level_name_tha, education_type_level_name_eng)) AS text, education_type_level AS value");
sql.AppendLine(", active AS active ");
sql.AppendLine("FROM db_education_type_level ");
sql.AppendLine("WHERE company_code = @CompanyCode");
sql.AppendLine("ORDER BY 1 ");
return(_context.QueryAsync <dynamic>(sql.ToString(), new { CompanyCode = _user.Company, lang = _user.Language }, cancellationToken));
}
private Task <IEnumerable <dynamic> > GetProvince(CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT CONCAT(province_code,' : ',get_name(@Lang, province_name_tha, province_name_eng)) AS \"text\" ");
sql.AppendLine(" ,province_id AS \"value\" ");
sql.AppendLine(" ,active AS \"active\" ");
sql.AppendLine("FROM db_province ");
sql.AppendLine("ORDER BY province_code::numeric");
return(_context.QueryAsync <dynamic>(sql.ToString(), new { lang = _user.Language }, cancellationToken));
}
private Task <IEnumerable <dynamic> > GetEducation(CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT CONCAT(list_item_code,' : ',get_name(@lang, list_item_name_tha, list_item_name_eng)) AS text, list_item_code AS value");
sql.AppendLine(", active AS active ");
sql.AppendLine("FROM db_list_item ");
sql.AppendLine("WHERE list_item_group_code = 'EduGroupLevel' ");
sql.AppendLine("ORDER BY sequence");
return(_context.QueryAsync <dynamic>(sql.ToString(), new { lang = _user.Language }, cancellationToken));
}
private Task <IEnumerable <dynamic> > GetStudents(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" select e.student_id as value,e.student_code as \"studentCode\",e.company_code as \"companyCode\" ");
sql.AppendLine(" ,coalesce(e.id_card,e.passport_no) as \"personalId\",e.email");
sql.AppendLine(" ,concat(e.student_code,' : ',get_name(@Lang,e.student_name_tha,e.student_name_eng)) as text ");
sql.AppendLine(" from sh_student e ");
sql.AppendLine(" inner join sh_student_status ss on ss.status_code = e.status_code ");
sql.AppendLine(" where 1=1 ");
if (request.StudentId.HasValue)
{
sql.AppendLine(" and e.student_id = @Id ");
sql.AppendLine(" and e.company_code = @PersonCompany ");
}
else
{
sql.AppendLine(" and company_code = @Company ");
sql.AppendLine(" and exists(select 'x' from su_parameter p ");
sql.AppendLine(" where p.parameter_group_code = 'StudentStatusGroup' ");
sql.AppendLine(" and p.parameter_code = 'NormalStatus' ");
sql.AppendLine(" and p.parameter_value = ss.status_group_code ) ");
sql.AppendLine(" and not exists(select 'x' ");
sql.AppendLine(" from su_user_type t ");
sql.AppendLine(" where t.company_code = e.company_code ");
sql.AppendLine(" and t.student_id = e.student_id )");
if (!string.IsNullOrWhiteSpace(request.Keyword))
{
sql.AppendLine(" and concat(e.student_code,e.student_name_tha,e.student_name_eng) ilike concat('%',@Keyword,'%') ");
}
sql.AppendLine(" order by e.student_code desc ");
if (string.IsNullOrWhiteSpace(request.Keyword))
{
sql.AppendLine($"limit {20}");
}
}
return(_context.QueryAsync <dynamic>(sql.ToString(), new { PersonCompany = request.PersonCompany, Company = _user.Company, Lang = _user.Language, Keyword = request.Keyword, Id = request.StudentId, }, cancellationToken));
}
private Task <IEnumerable <dynamic> > Get(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT CONCAT(sub_district_code,' : ',get_name(@Lang, sub_district_name_tha, sub_district_name_eng)) AS \"text\" ");
sql.AppendLine(" ,sub_district_id AS \"value\" ");
sql.AppendLine(" ,active AS \"active\" ");
sql.AppendLine(" FROM db_sub_district ");
sql.AppendLine(" WHERE district_id = @DistrictId ");
sql.AppendLine(" ORDER BY 1 ");
return(_context.QueryAsync <dynamic>(sql.ToString(), new{ Lang = _user.Language, DistrictId = request.DistrictId }, cancellationToken));
}
private Task <IEnumerable <dynamic> > GetStatuses(CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT s.status_value as value,get_name(@Lang,status_desc_tha,status_desc_eng) as text,status_color as \"color\"");
sql.AppendLine("FROM db_status s ");
sql.AppendLine("WHERE s.table_name = 'su_user' ");
sql.AppendLine("AND s.column_name = 'status' ");
sql.AppendLine("ORDER BY s.sequence ");
return(_context.QueryAsync <dynamic>(sql.ToString(), new { Lang = _user.Language }, cancellationToken));
}
private Task <IEnumerable <dynamic> > GetProgramCode(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT CONCAT(p.program_code, ' : ',get_name(@lang, p.program_name_tha, p.program_name_eng)) AS text, p.program_code AS value");
sql.AppendLine(", fp.active AS active ");
sql.AppendLine("FROM db_fac_program fp ");
sql.AppendLine("JOIN db_program p ON (p.company_code = fp.company_code AND p.program_code = fp.program_code) ");
sql.AppendLine("WHERE fp.company_code = @CompanyCode ");
sql.AppendLine("AND fp.fac_code = @FacCode ");
sql.AppendLine("ORDER BY 1 ");
return(_context.QueryAsync <dynamic>(sql.ToString(), new { lang = _user.Language, CompanyCode = _user.Company
, FacCode = request.FacCode }, cancellationToken));
}
public async Task <MasterData> Handle(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT parameter_group_code AS text,");
sql.AppendLine(" parameter_group_code AS value");
sql.AppendLine("FROM su_parameter");
sql.AppendLine("GROUP BY parameter_group_code");
sql.AppendLine("ORDER BY parameter_group_code");
var parameterGroupCodes = await _context.QueryAsync <dynamic>(sql.ToString(), null, cancellationToken);
return(new MasterData {
ParameterGroupCodes = parameterGroupCodes
});
}
public async Task <MasterData> Handle(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT CASE @Language WHEN @Tha THEN status_desc_tha ELSE COALESCE(status_desc_eng, status_desc_tha) END AS text,");
sql.AppendLine(" status_value AS value");
sql.AppendLine("FROM db_status");
sql.AppendLine("WHERE table_name = @TableName");
sql.AppendLine(" AND column_name = @ColumnName");
sql.AppendLine("ORDER BY COALESCE(sequence, 9999999),");
sql.AppendLine(" CASE @Language WHEN @Tha THEN status_desc_tha ELSE COALESCE(status_desc_eng, status_desc_tha) END");
var systemCodes = await _context.QueryAsync <dynamic>(sql.ToString(), new { Language = this._user.Language, Tha = "th", TableName = "su_menu", ColumnName = "system_code" }, cancellationToken);
sql = new StringBuilder();
sql.AppendLine("SELECT CONCAT(status_value, ' : ', CASE @Language WHEN @Tha THEN status_desc_tha ELSE COALESCE(status_desc_eng, status_desc_tha) END) AS text,");
sql.AppendLine(" status_value AS value");
sql.AppendLine("FROM db_status");
sql.AppendLine("WHERE table_name = @TableName");
sql.AppendLine(" AND column_name = @ColumnName");
sql.AppendLine("ORDER BY COALESCE(sequence, 9999999),");
sql.AppendLine(" CASE @Language WHEN @Tha THEN status_desc_tha ELSE COALESCE(status_desc_eng, status_desc_tha) END");
var moduleCodes = await _context.QueryAsync <dynamic>(sql.ToString(), new { Language = this._user.Language, Tha = "th", TableName = "su_program", ColumnName = "module_code" }, cancellationToken);
sql = new StringBuilder();
sql.AppendLine("SELECT CASE @Language WHEN @Tha THEN status_desc_tha ELSE COALESCE(status_desc_eng, status_desc_tha) END AS language");
sql.AppendLine("FROM db_status");
sql.AppendLine("WHERE table_name = @TableName");
sql.AppendLine(" AND column_name = @ColumnName");
sql.AppendLine("ORDER BY COALESCE(sequence, 9999999),");
sql.AppendLine(" CASE @Language WHEN @Tha THEN status_desc_tha ELSE COALESCE(status_desc_eng, status_desc_tha) END");
var languages = await _context.QueryAsync <dynamic>(sql.ToString(), new { Language = this._user.Language, Tha = "th", TableName = "lang", ColumnName = "lang_code" }, cancellationToken);
return(new MasterData {
SystemCodes = systemCodes, ModuleCodes = moduleCodes, Languages = languages
});
}
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 <IEnumerable <MenuVm> > Handle(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("select m.menu_code,concat('menu.',m.menu_code) as title,p.program_path as url,m.main_menu as \"mainMenu\",m.icon,false as active");
sql.AppendLine("from su_menu m");
sql.AppendLine("left join su_program p on p.program_code = m.program_code");
sql.AppendLine("where m.system_code = 'bbo'");
sql.AppendLine(" and exists(select 'x' ");
sql.AppendLine(" from su_menu_profile mp ");
sql.AppendLine(" inner join su_user_profile p on p.profile_code = mp.profile_code ");
sql.AppendLine(" where mp.menu_code = m.menu_code ");
sql.AppendLine(" and p.user_id = @UserId ) ");
sql.AppendLine("order by m.menu_code");
var menus = await _context.QueryAsync <MenuVm>(sql.ToString(), new {
UserId = _user.UserId
}, cancellationToken);
var root = new MenuVm();
this.GetMenus(root, menus, null);
return(root.SubMenus);
}
public async Task <MasterData> Handle(Query request, CancellationToken cancellationToken)
{
var master = new MasterData();
StringBuilder sql = new StringBuilder();
switch (request.FieldName)
{
// SystemCodeAndLanguage
case "SystemCodeAndLanguage":
sql.AppendLine("SELECT CASE @Language WHEN @Tha THEN status_desc_tha ELSE COALESCE(status_desc_eng, status_desc_tha) END AS text,");
sql.AppendLine(" status_value AS value");
sql.AppendLine("FROM db_status");
sql.AppendLine("WHERE table_name = @TableName");
sql.AppendLine(" AND column_name = @ColumnName");
sql.AppendLine("ORDER BY COALESCE(sequence, 9999999),");
sql.AppendLine(" CASE @Language WHEN @Tha THEN status_desc_tha ELSE COALESCE(status_desc_eng, status_desc_tha) END");
var systemCodes = await _context.QueryAsync <dynamic>(sql.ToString(), new { Language = this._user.Language, Tha = "th", TableName = "su_menu", ColumnName = "system_code" }, cancellationToken);
sql = new StringBuilder();
sql.AppendLine("SELECT CASE @Language WHEN @Tha THEN status_desc_tha ELSE COALESCE(status_desc_eng, status_desc_tha) END AS text,");
sql.AppendLine(" status_value::Lang AS value");
sql.AppendLine("FROM db_status");
sql.AppendLine("WHERE table_name = @TableName");
sql.AppendLine(" AND column_name = @ColumnName");
sql.AppendLine("ORDER BY COALESCE(sequence, 9999999),");
sql.AppendLine(" CASE @Language WHEN @Tha THEN status_desc_tha ELSE COALESCE(status_desc_eng, status_desc_tha) END");
var langCodes = await _context.QueryAsync <dynamic>(sql.ToString(), new { Language = this._user.Language, Tha = "th", TableName = "lang", ColumnName = "lang_code" }, cancellationToken);
master = new MasterData {
SystemCodes = systemCodes, LangCodes = langCodes
};
break;
// MainMenusAndProgramCodes
case "MainMenusAndProgramCodes":
sql.AppendLine("SELECT CONCAT(a.menu_code, ' : ', CASE @Language WHEN @Tha THEN b.menuNameTha ELSE COALESCE(b.menuNameEng, b.menuNameTha) END) AS text,");
sql.AppendLine(" a.menu_code AS value,");
sql.AppendLine(" a.active");
sql.AppendLine("FROM su_menu AS a");
sql.AppendLine("INNER JOIN (SELECT a.menu_code,");
sql.AppendLine(" a.menu_name AS menuNameTha,");
sql.AppendLine(" b.menu_name AS menuNameEng");
sql.AppendLine(" FROM su_menu_label AS a");
sql.AppendLine(" LEFT JOIN (SELECT menu_code, menu_name FROM su_menu_label WHERE lang_code = @Eng::Lang) AS b");
sql.AppendLine(" ON a.menu_code = b.menu_code");
sql.AppendLine(" WHERE a.lang_code = @Tha::Lang) AS b");
sql.AppendLine("ON a.menu_code = b.menu_code");
sql.AppendLine("WHERE a.system_code = @SystemCode");
if (!string.IsNullOrEmpty(request.MenuCode))
{
sql.AppendLine(" AND a.menu_code != @MenuCode");
}
sql.AppendLine("ORDER BY a.menu_code");
var mainMenus = await _context.QueryAsync <dynamic>(sql.ToString(), new { Language = this._user.Language, Tha = "th", Eng = "en", SystemCode = request.SystemCode, MenuCode = request.MenuCode }, cancellationToken);
sql = new StringBuilder();
sql.AppendLine("SELECT CONCAT(program_code, ' : ', program_name) AS text,");
sql.AppendLine(" program_code AS value");
sql.AppendLine("FROM su_program");
sql.AppendLine("WHERE system_code = @SystemCode");
sql.AppendLine("ORDER BY program_code");
var programCodes = await _context.QueryAsync <dynamic>(sql.ToString(), new { SystemCode = request.SystemCode }, cancellationToken);
master = new MasterData {
MainMenus = mainMenus, ProgramCodes = programCodes
};
break;
}
return(master);
}
private Task <IEnumerable <dynamic> > GetDiv(CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" SELECT div_code AS \"value\" ");
sql.AppendLine(" ,CONCAT(div_code,' : ',get_name(@Lang, div_name_tha, div_name_eng)) AS \"text\" ");
sql.AppendLine(" FROM su_division ");
sql.AppendLine(" WHERE company_code = @Company ");
sql.AppendLine(" ORDER BY 1 ");
return(_context.QueryAsync <dynamic>(sql.ToString(), new { Company = _user.Company, Lang = _user.Language }, cancellationToken));
}
public async Task <MasterData> Handle(Query request, CancellationToken cancellationToken)
{
var master = new MasterData();
StringBuilder sql;;
foreach (string formName in request.FormName.Split(','))
{
switch (formName.Trim().ToLower())
{
case "personaltaxtype":
sql = new StringBuilder();
sql.AppendLine("SELECT CASE @Language WHEN @Tha THEN status_desc_tha ELSE COALESCE(status_desc_eng, status_desc_tha) END AS text,");
sql.AppendLine(" status_value AS value");
sql.AppendLine("FROM db_status");
sql.AppendLine("WHERE table_name = @TableName");
sql.AppendLine(" AND column_name = @ColumnName");
sql.AppendLine("ORDER BY COALESCE(sequence, 9999999),");
sql.AppendLine(" CASE @Language WHEN @Tha THEN status_desc_tha ELSE COALESCE(status_desc_eng, status_desc_tha) END");
master.PersonalTaxTypes = await _context.QueryAsync <dynamic>(sql.ToString(), new { Language = this._user.Language, Tha = "th", TableName = "su_company", ColumnName = "personal_tax_type" }, cancellationToken);
break;
case "country":
sql = new StringBuilder();
sql.AppendLine("SELECT CASE @Language WHEN @Tha THEN country_name_tha ELSE COALESCE(country_name_eng, country_name_tha) END AS text,");
sql.AppendLine(" country_id AS value,");
sql.AppendLine(" active");
sql.AppendLine("FROM db_country");
sql.AppendLine("ORDER BY CASE @Language WHEN @Tha THEN country_name_tha ELSE COALESCE(country_name_eng, country_name_tha) END");
master.Countries = await _context.QueryAsync <dynamic>(sql.ToString(), new { Language = this._user.Language, Tha = "th" }, cancellationToken);
break;
case "province":
sql = new StringBuilder();
sql.AppendLine("SELECT CASE @Language WHEN @Tha THEN province_name_tha ELSE COALESCE(province_name_eng, province_name_tha) END AS text,");
sql.AppendLine(" province_id AS value,");
sql.AppendLine(" active");
sql.AppendLine("FROM db_province");
sql.AppendLine("WHERE country_id = @CountryId");
sql.AppendLine("ORDER BY CASE @Language WHEN @Tha THEN province_name_tha ELSE COALESCE(province_name_eng, province_name_tha) END");
master.Provinces = await _context.QueryAsync <dynamic>(sql.ToString(), new { Language = this._user.Language, Tha = "th", CountryId = request.CountryId }, cancellationToken);
break;
case "district":
sql = new StringBuilder();
sql.AppendLine("SELECT CASE @Language WHEN @Tha THEN district_name_tha ELSE COALESCE(district_name_eng, district_name_tha) END AS text,");
sql.AppendLine(" district_id AS value,");
sql.AppendLine(" active");
sql.AppendLine("FROM db_district");
sql.AppendLine("WHERE province_id = @ProvinceId");
sql.AppendLine("ORDER BY CASE @Language WHEN @Tha THEN district_name_tha ELSE COALESCE(district_name_eng, district_name_tha) END");
master.Districts = await _context.QueryAsync <dynamic>(sql.ToString(), new { Language = this._user.Language, Tha = "th", ProvinceId = request.ProvinceId }, cancellationToken);
break;
case "subdistrict":
sql = new StringBuilder();
sql.AppendLine("SELECT CASE @Language WHEN @Tha THEN sub_district_name_tha ELSE COALESCE(sub_district_name_eng, sub_district_name_tha) END AS text,");
sql.AppendLine(" sub_district_id AS value,");
sql.AppendLine(" active");
sql.AppendLine("FROM db_sub_district");
sql.AppendLine("WHERE district_id = @DistrictId");
sql.AppendLine("ORDER BY CASE @Language WHEN @Tha THEN sub_district_name_tha ELSE COALESCE(sub_district_name_eng, sub_district_name_tha) END");
master.SubDistricts = await _context.QueryAsync <dynamic>(sql.ToString(), new { Language = this._user.Language, Tha = "th", DistrictId = request.DistrictId }, cancellationToken);
break;
case "postalcode":
sql = new StringBuilder();
sql.AppendLine("SELECT postal_code AS text,");
sql.AppendLine(" postal_code_id AS value,");
sql.AppendLine(" active");
sql.AppendLine("FROM db_postal_code");
sql.AppendLine("WHERE province_id = @ProvinceId");
sql.AppendLine(" AND district_id = @DistrictId");
sql.AppendLine(" AND sub_district_id = @SubDistrictId");
sql.AppendLine("ORDER BY CASE @Language WHEN @Tha THEN postal_name_tha ELSE COALESCE(postal_name_eng, postal_name_tha) END");
master.PostalCodes = await _context.QueryAsync <dynamic>(sql.ToString(), new { Language = this._user.Language, Tha = "th", ProvinceId = request.ProvinceId, DistrictId = request.DistrictId, SubDistrictId = request.SubDistrictId }, cancellationToken);
break;
case "divparent":
sql = new StringBuilder();
sql.AppendLine("SELECT CONCAT(div_code, ' : ', CASE @Language WHEN @Tha THEN div_name_tha ELSE COALESCE(div_name_eng, div_name_tha) END) AS text,");
sql.AppendLine(" div_code AS value");
sql.AppendLine("FROM su_division");
sql.AppendLine("WHERE company_code = @CompanyCode");
if (!string.IsNullOrEmpty(request.DivCode))
{
sql.AppendLine(" AND div_code != @DivCode");
sql.AppendLine(" AND (div_parent != @DivCode OR div_parent ISNULL)");
}
sql.AppendLine("ORDER BY div_code");
master.DivParents = await _context.QueryAsync <dynamic>(sql.ToString(), new { Language = this._user.Language, Tha = "th", DivCode = request.DivCode, CompanyCode = request.CompanyCode }, cancellationToken);
break;
case "divtype":
sql = new StringBuilder();
sql.AppendLine("select case @Language when 'th' then status_desc_tha else coalesce(status_desc_eng, status_desc_tha) end as text,");
sql.AppendLine(" status_value as value");
sql.AppendLine("from db_status");
sql.AppendLine("where table_name = 'su_division'");
sql.AppendLine("and column_name = 'div_type'");
sql.AppendLine("order by sequence");
master.DivTypes = await _context.QueryAsync <dynamic>(sql.ToString(), new { Language = this._user.Language }, cancellationToken);
break;
case "location":
sql = new StringBuilder();
sql.AppendLine("select concat(location_code, ' : ', case @Language when 'th' then location_name_tha else coalesce(location_name_eng, location_name_tha) end) as text,");
sql.AppendLine(" location_code as value,");
sql.AppendLine(" active");
sql.AppendLine("from db_location");
sql.AppendLine("where company_code = @CompanyCode");
sql.AppendLine("order by location_code");
master.Locations = await _context.QueryAsync <dynamic>(sql.ToString(), new { Language = this._user.Language, CompanyCode = request.CompanyCode }, cancellationToken);
break;
case "faculty":
sql = new StringBuilder();
sql.AppendLine("select concat(fac_code, ' : ', case @Language when 'th' then fac_name_tha else coalesce(fac_name_eng, fac_name_tha) end) as text,");
sql.AppendLine(" fac_code as value,");
sql.AppendLine(" active");
sql.AppendLine("from db_fac");
sql.AppendLine("where company_code = @CompanyCode");
sql.AppendLine("order by fac_code");
master.Faculties = await _context.QueryAsync <dynamic>(sql.ToString(), new { Language = this._user.Language, CompanyCode = request.CompanyCode }, cancellationToken);
break;
case "program":
sql = new StringBuilder();
sql.AppendLine("select concat(program_code, ' : ', case @Language when 'th' then program_name_tha else coalesce(program_name_eng, program_name_tha) end) as text,");
sql.AppendLine(" program_code as value,");
sql.AppendLine(" active");
sql.AppendLine("from db_program");
sql.AppendLine("where company_code = @CompanyCode");
sql.AppendLine("order by program_code");
master.Programs = await _context.QueryAsync <dynamic>(sql.ToString(), new { Language = this._user.Language, CompanyCode = request.CompanyCode }, cancellationToken);
break;
case "facultyofprogram":
sql = new StringBuilder();
sql.AppendLine("select distinct concat(df.fac_code, ' : ', case @Language when 'th' then df.fac_name_tha else coalesce(df.fac_name_eng, df.fac_name_tha) end) as text,");
sql.AppendLine(" df.fac_code as value,");
sql.AppendLine(" df.active");
sql.AppendLine("from db_fac_program as dfp");
sql.AppendLine("inner join db_fac as df");
sql.AppendLine("on df.fac_code = dfp.fac_code");
sql.AppendLine(" and df.company_code = dfp.company_code");
sql.AppendLine("where dfp.company_code = @CompanyCode");
sql.AppendLine(" and dfp.program_code = @Program");
sql.AppendLine("order by df.fac_code");
master.FacultiesOfPrograms = await _context.QueryAsync <dynamic>(sql.ToString(), new { Language = this._user.Language, CompanyCode = request.CompanyCode, Program = request.ProgramCode }, cancellationToken);
break;
}
}
return(master);
}