public async Task <PageDto> Handle(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" SELECT CASE @Language WHEN @Tha THEN b.status_desc_tha ELSE COALESCE(b.status_desc_eng, b.status_desc_tha) END AS \"system\",");
sql.AppendLine(" a.module_code as \"moduleCode\",CASE @Language WHEN @Tha THEN c.status_desc_tha ELSE COALESCE(c.status_desc_eng, c.status_desc_tha) END AS \"module\",");
sql.AppendLine(" a.program_code AS \"programCode\",");
sql.AppendLine(" a.program_name AS \"programName\",");
sql.AppendLine(" a.program_path AS \"programPath\",");
sql.AppendLine(" a.xmin AS \"rowVersion\"");
sql.AppendLine(" FROM su_program AS a");
sql.AppendLine(" LEFT JOIN db_status AS b");
sql.AppendLine(" ON a.system_code = b.status_value");
sql.AppendLine(" AND b.table_name = @TableName_B");
sql.AppendLine(" AND b.column_name = @ColumnName_B");
sql.AppendLine(" LEFT JOIN db_status AS c");
sql.AppendLine(" ON a.module_code = c.status_value");
sql.AppendLine(" AND c.table_name = @TableName_C");
sql.AppendLine(" AND c.column_name = @ColumnName_C");
if (!string.IsNullOrWhiteSpace(request.Keyword))
{
sql.AppendLine("WHERE CONCAT(b.status_desc_tha,");
sql.AppendLine(" b.status_desc_eng,");
sql.AppendLine(" c.status_desc_tha,");
sql.AppendLine(" c.status_desc_eng,");
sql.AppendLine(" a.program_code,");
sql.AppendLine(" a.program_name)");
sql.AppendLine(" ILIKE CONCAT('%', @Keyword, '%')");
}
return(await _context.GetPage(sql.ToString(), new { Language = this._user.Language, Tha = "th", TableName_B = "su_menu", ColumnName_B = "system_code", TableName_C = "su_program", ColumnName_C = "module_code", Keyword = request.Keyword }, (RequestPageQuery)request, cancellationToken));
}
public async Task <PageDto> Handle(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("select sm.menu_code as \"menuCode\",");
sql.AppendLine(" case @Language when 'th' then sml_th.menu_name else coalesce(sml_en.menu_name ,sml_th.menu_name) end as \"menuName\"");
sql.AppendLine("from su_menu as sm");
sql.AppendLine("left join su_menu_label as sml_th");
sql.AppendLine("on sml_th.menu_code = sm.menu_code");
sql.AppendLine(" and sml_th.lang_code = 'th'");
sql.AppendLine("left join su_menu_label as sml_en");
sql.AppendLine("on sml_en.menu_code = sm.menu_code");
sql.AppendLine(" and sml_en.lang_code = 'en'");
sql.AppendLine("where sm.active = true");
if (!string.IsNullOrWhiteSpace(request.Keyword))
{
sql.AppendLine(" and concat(sm.menu_code,");
sql.AppendLine(" sml_th.menu_name,");
sql.AppendLine(" sml_en.menu_name)");
sql.AppendLine(" ilike concat('%', @Keyword, '%')");
}
sql.AppendLine("order by sm.menu_code");
return(await _context.GetPage(sql.ToString(), new { Keyword = request.Keyword, Language = this._user.Language }, (RequestPageQuery)request, cancellationToken));
}
public async Task <PageDto> Handle(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" SELECT g.list_item_group_code as \"listItemGroupCode\" ");
sql.AppendLine(" ,i.list_item_code as \"listItemCode\" ");
sql.AppendLine(" ,i.list_item_code_mua as \"listItemCodeMua\" ");
sql.AppendLine(" ,i.list_item_name_tha as \"listItemNameTha\" ");
sql.AppendLine(" ,i.list_item_name_eng as \"listItemNameEng\" ");
sql.AppendLine(" ,i.list_item_short_name_tha as \"listItemShortNameTha\" ");
sql.AppendLine(" ,i.list_item_short_name_eng as \"listItemShortNameEng\" ");
sql.AppendLine(" ,i.active as \"active\" ");
sql.AppendLine(" ,i.xmin as \"rowVersion\" ");
sql.AppendLine("FROM db_list_item i");
sql.AppendLine("inner join db_list_item_group g on i.list_item_group_code = g.list_item_group_code");
if (!string.IsNullOrWhiteSpace(request.Keyword))
{
sql.AppendLine("WHERE CONCAT(list_item_code,list_item_code_mua,list_item_name_tha,list_item_name_eng,list_item_short_name_tha,list_item_short_name_eng) ILIKE '%' || @Keyword || '%'");
}
if (!string.IsNullOrWhiteSpace(request.ListItemGroupCode))
{
sql.AppendLine("AND g.list_item_group_code::text = @ListItemGroupCode");
}
sql.AppendLine("ORDER BY i.sequence ASC");
return(await _context.GetPage(sql.ToString(), new { ListItemGroupCode = request.ListItemGroupCode, Keyword = request.Keyword }, (RequestPageQuery)request, cancellationToken));
}
public async Task <PageDto> Handle(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" SELECT m.major_code AS \"majorCode\" ");
sql.AppendLine(" ,m.major_code_mua AS \"majorCodeMua\" ");
sql.AppendLine(" ,m.fac_code AS \"facCode\" ");
sql.AppendLine(" ,m.program_code AS \"programCode\" ");
sql.AppendLine(" ,m.major_name_tha AS \"majorNameTha\" ");
sql.AppendLine(" ,m.major_name_eng AS \"majorNameEng\" ");
sql.AppendLine(" ,get_name(@Lang, f.fac_name_tha, f.fac_name_eng) AS \"facName\" ");
sql.AppendLine(" ,get_name(@Lang, p.program_name_tha, p.program_name_eng) AS \"programName\" ");
sql.AppendLine(" ,m.active AS \"active\" ");
sql.AppendLine(" ,m.xmin AS \"rowVersion\" ");
sql.AppendLine(" FROM db_major m ");
sql.AppendLine(" JOIN db_fac f ON f.fac_code = m.fac_code ");
sql.AppendLine(" JOIN db_program p ON p.program_code = m.program_code ");
sql.AppendLine(" WHERE m.company_code = @Company ");
sql.AppendLine(" AND f.company_code = @Company ");
sql.AppendLine(" AND p.company_code = @Company ");
if (!string.IsNullOrWhiteSpace(request.Keyword))
{
sql.AppendLine("AND CONCAT(major_code, major_code_mua, major_name_tha, major_name_eng, get_name(@Lang, f.fac_name_tha, f.fac_name_eng), get_name(@Lang, p.program_name_tha, p.program_name_eng) ) ");
sql.AppendLine(" ILIKE '%' || @Keyword || '%' ");
}
sql.AppendLine("ORDER BY m.major_code, m.fac_code, m.program_code, get_name(@Lang, f.fac_name_tha, f.fac_name_eng) ,get_name(@Lang, p.program_name_tha, p.program_name_eng) ");
return(await _context.GetPage(sql.ToString(), new { lang = _user.Language, Company = _user.Company, Keyword = request.Keyword }, (RequestPageQuery)request, cancellationToken));
}
public async Task <PageDto> Handle(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" SELECT p.project_id AS \"projectId\" ");
sql.AppendLine(" ,p.project_code AS \"projectCode\" ");
sql.AppendLine(" ,p.project_name_tha AS \"projectNameTha\" ");
sql.AppendLine(" ,p.project_name_eng AS \"projectNameEng\" ");
sql.AppendLine(" ,get_name(@Lang, et.education_type_name_tha, et.education_type_name_eng) AS \"educationTypeNameTha\" ");
sql.AppendLine(" ,get_name(@Lang, f.fac_name_tha, f.fac_name_eng) AS \"facNameTha\" ");
sql.AppendLine(" ,get_name(@Lang, st.student_type_name, st.student_type_name_eng) AS \"studentTypeName\" ");
sql.AppendLine(" ,p.erp_code AS \"erpCode\" ");
sql.AppendLine(" ,p.erp_activity AS \"erpActivity\" ");
sql.AppendLine(" ,p.erp_product AS \"erpProduct\" ");
sql.AppendLine(" ,p.erp_project AS \"erpProject\" ");
sql.AppendLine(" ,p.active AS \"active\" ");
sql.AppendLine(" ,p.xmin AS \"rowVersion\" ");
sql.AppendLine(" FROM db_project p");
sql.AppendLine(" LEFT JOIN db_education_type et ON p.education_type_code = et.education_type_code ");
sql.AppendLine(" AND et.company_code = @Company ");
sql.AppendLine(" LEFT JOIN db_fac f on p.fac_code = f.fac_code ");
sql.AppendLine(" AND f.company_code = @Company ");
sql.AppendLine(" LEFT JOIN sh_student_type st on p.student_type_code = st.student_type_code ");
sql.AppendLine(" WHERE p.company_code = @Company ");
if (!string.IsNullOrWhiteSpace(request.Keyword))
{
sql.AppendLine("AND CONCAT(project_code, project_name_tha, project_name_eng, get_name(@Lang, et.education_type_name_tha, et.education_type_name_eng), get_name(@Lang, f.fac_name_tha, f.fac_name_eng), get_name(@Lang, st.student_type_name, st.student_type_name_eng), erp_code, erp_activity, erp_product, erp_project ) ILIKE '%' || @Keyword || '%'");
}
return(await _context.GetPage(sql.ToString(), new { lang = _user.Language, Company = _user.Company, Keyword = request.Keyword }, (RequestPageQuery)request, cancellationToken));
}
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 <PageDto> Handle(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" SELECT e.employee_code AS \"employeeCode\" ");
sql.AppendLine(" ,get_name(@Lang, e.t_name_concat, e.e_name_concat) AS \"employeeName\" ");
sql.AppendLine(" ,get_name(@Lang, d.div_name_tha, d.div_name_eng) AS \"divName\" ");
sql.AppendLine(" ,get_name(@Lang,li.list_item_name_tha,li.list_item_name_eng) AS \"groupTypeCode\" ");
sql.AppendLine(" ,get_name(@Lang,li.list_item_name_tha,li.list_item_name_eng) AS \"groupTypeCode\" ");
sql.AppendLine(" ,e.turnover_date AS \"turnoverDate\" ");
sql.AppendLine(" ,e.xmin as \"rowVersion\" ");
sql.AppendLine(" From db_employee e");
sql.AppendLine(" LEFT JOIN su_division d on e.div_code = d.div_code");
sql.AppendLine(" AND d.company_code = @company ");
sql.AppendLine(" LEFT JOIN db_list_item li on e.group_type_code = li.list_item_code");
sql.AppendLine(" AND li.list_item_group_code = 'GroupTypeCode' ");
sql.AppendLine(" WHERE e.company_code = @company ");
if (!string.IsNullOrWhiteSpace(request.EmployeeCode))
{
sql.AppendLine(" AND e.employee_code ILIKE '%' || @employeeCode || '%'");
}
if (!string.IsNullOrWhiteSpace(request.EmployeeName))
{
sql.AppendLine(" AND get_name(@Lang, e.t_name_concat, e.e_name_concat) ILIKE '%' || @employeeName || '%'");
}
if (!string.IsNullOrWhiteSpace(request.DivCode))
{
sql.AppendLine(" AND e.div_code = @divCode ");
}
if (!string.IsNullOrWhiteSpace(request.GroupTypeCode))
{
sql.AppendLine(" AND e.group_type_code = @groupTypeCode ");
}
if (request.TurnoverDate == "2")
{
sql.AppendLine(" AND e.turnover_date IS NULL ");
}
if (request.TurnoverDate == "3")
{
sql.AppendLine(" AND e.turnover_date is NOT NULL ");
}
return(await _context.GetPage(sql.ToString(), new { company = _user.Company,
employeeCode = request.EmployeeCode,
employeeName = request.EmployeeName,
divCode = request.DivCode,
groupTypeCode = request.GroupTypeCode,
turnoverDate = request.TurnoverDate,
lang = _user.Language }, (RequestPageQuery)request, cancellationToken));
}
public async Task <PageDto> Handle(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" SELECT distinct(g.list_item_group_code) as \"listItemGroupCode\" ");
sql.AppendLine(" ,g.list_item_group_name as \"listItemGroupName\" ");
sql.AppendLine(" ,g.system_code as \"systemCode\" ");
sql.AppendLine(" ,g.xmin as \"rowVersion\" ");
sql.AppendLine("FROM db_list_item i");
sql.AppendLine("right join db_list_item_group g on i.list_item_group_code = g.list_item_group_code");
if (!string.IsNullOrWhiteSpace(request.Keyword))
{
sql.AppendLine("WHERE CONCAT(g.list_item_group_code,g.list_item_group_name,g.system_code) ILIKE '%' || @Keyword || '%'");
}
return(await _context.GetPage(sql.ToString(), new { Keyword = request.Keyword }, (RequestPageQuery)request, cancellationToken));
}
public async Task <PageDto> Handle(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" SELECT company_code as \"companyCode\" ");
sql.AppendLine(" ,location_code as \"locationCode\" ");
sql.AppendLine(" ,location_name_tha as \"locationNameTha\" ");
sql.AppendLine(" ,location_name_eng as \"locationNameEng\" ");
sql.AppendLine(" ,active as \"active\" ");
sql.AppendLine(" ,xmin as \"rowVersion\" ");
sql.AppendLine(" FROM db_location ");
sql.AppendLine(" WHERE company_code = @Company ");
if (!string.IsNullOrWhiteSpace(request.Keyword))
{
sql.AppendLine("AND CONCAT(location_code,location_name_tha,location_name_eng) ILIKE '%' || @Keyword || '%'");
}
return(await _context.GetPage(sql.ToString(), new { Company = _user.Company, Keyword = request.Keyword }, (RequestPageQuery)request, cancellationToken));
}
public async Task <PageDto> Handle(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT fac_code as \"facCode\" ");
sql.AppendLine(",fn_payin_code as \"fnPayinCode\", format_code as \"formatCode\" ");
sql.AppendLine(",fac_name_tha as \"facNameTha\",fac_name_eng as \"facNameEng\" ");
sql.AppendLine(",fac_short_name_tha \"facShortNameTha\",fac_short_name_eng \"facShortNameEng\" ");
sql.AppendLine(",fac_code_mua as \"facCodeMua\" ");
sql.AppendLine(",active,xmin as \"rowVersion\" ");
sql.AppendLine("FROM db_fac");
sql.AppendLine("WHERE company_code = @Company");
if (!string.IsNullOrWhiteSpace(request.Keyword))
{
sql.AppendLine("AND CONCAT(fac_code,' ',fac_code_mua,' ',fn_payin_code,' ',format_code,' ',fac_name_tha,fac_name_eng,' ',fac_name_eng,fac_short_name_tha,' ',fac_short_name_eng) ILIKE '%' || @Keyword || '%'");
}
return(await _context.GetPage(sql.ToString(), new { Company = _user.Company, Keyword = request.Keyword }, (RequestPageQuery)request, cancellationToken));
}
public async Task <PageDto> Handle(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" SELECT degree_id as \"degreeId\" ");
sql.AppendLine(" ,degree_name_tha as \"degreeNameTha\" ");
sql.AppendLine(" ,degree_name_eng as \"degreeNameEng\" ");
sql.AppendLine(" ,degree_short_name_tha as\"degreeShortNameTha\" ");
sql.AppendLine(" ,degree_short_name_eng as\"degreeShortNameEng\" ");
sql.AppendLine(" ,active as\"active\" ");
sql.AppendLine(" ,xmin as \"rowVersion\" ");
sql.AppendLine("FROM db_degree");
if (!string.IsNullOrWhiteSpace(request.Keyword))
{
sql.AppendLine("WHERE CONCAT(degree_name_tha,degree_name_eng,degree_short_name_tha,degree_short_name_eng) ILIKE '%' || @Keyword || '%'");
}
return(await _context.GetPage(sql.ToString(), new { Keyword = request.Keyword }, (RequestPageQuery)request, cancellationToken));
}
public async Task <PageDto> Handle(Query request, CancellationToken cancellationToken)
{
request.CompanyCode = _user.Company;
StringBuilder sql = new StringBuilder();
sql.AppendLine(" SELECT building_id as \"buildingId\" ");
sql.AppendLine(" ,building_code as \"buildingCode\" ");
sql.AppendLine(" ,building_name_tha as \"buildingNameTha\" ");
sql.AppendLine(" ,building_name_eng as \"buildingNameEng\" ");
sql.AppendLine(" ,active as \"active\" ");
sql.AppendLine(" ,xmin as \"rowVersion\" ");
sql.AppendLine(" FROM db_building ");
sql.AppendLine(" WHERE db_building.company_code = @Company ");
if (!string.IsNullOrWhiteSpace(request.Keyword))
{
sql.AppendLine("AND CONCAT(building_code,building_name_tha,building_name_eng) ILIKE '%' || @Keyword || '%'");
}
return(await _context.GetPage(sql.ToString(), new { Keyword = request.Keyword, Company = _user.Company }, (RequestPageQuery)request, cancellationToken));
}
public async Task <PageDto> 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");
if (!string.IsNullOrWhiteSpace(request.Keyword))
{
sql.AppendLine("WHERE CONCAT(profile_code,");
sql.AppendLine(" profile_desc)");
sql.AppendLine(" ILIKE CONCAT('%', @Keyword, '%')");
}
return(await _context.GetPage(sql.ToString(), new { Keyword = request.Keyword }, (RequestPageQuery)request, cancellationToken));
}
public async Task <PageDto> Handle(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT ds.sub_degree_id AS \"subDegreeId\" ");
sql.AppendLine(" ,get_name(@lang,d.degree_name_tha,d.degree_name_eng) AS \"degreeId\" ");
sql.AppendLine(" ,ds.sub_degree_name_tha AS \"subDegreeNameTha\" ");
sql.AppendLine(" ,ds.sub_degree_name_eng AS \"subDegreeNameEng\" ");
sql.AppendLine(" ,ds.sub_degree_short_name_tha AS \"subDegreeShortNameTha\" ");
sql.AppendLine(" ,ds.sub_degree_short_name_eng AS \"subDegreeShortNameEng\" ");
sql.AppendLine(" ,ds.active as \"active\" ");
sql.AppendLine(" ,ds.xmin as \"rowVersion\" ");
sql.AppendLine("FROM db_degree_sub ds");
sql.AppendLine("JOIN db_degree d ON ds.degree_id = d.degree_id");
if (!string.IsNullOrWhiteSpace(request.Keyword))
{
sql.AppendLine("WHERE CONCAT(sub_degree_name_tha,sub_degree_name_eng,sub_degree_short_name_tha,sub_degree_short_name_eng,get_name(@lang,d.degree_name_tha,d.degree_name_eng)) ILIKE '%' || @Keyword || '%'");
}
return(await _context.GetPage(sql.ToString(), new { Keyword = request.Keyword, Lang = _user.Language }, (RequestPageQuery)request, cancellationToken));
}
public async Task <PageDto> Handle(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT d.district_id AS \"districtId\" ");
sql.AppendLine(" ,d.district_code AS \"districtCode\" ");
sql.AppendLine(" ,d.district_code_mua AS \"districtCodeMua\" ");
sql.AppendLine(" ,d.district_name_tha AS \"districtNameTha\" ");
sql.AppendLine(" ,d.district_name_eng AS \"districtNameEng\" ");
sql.AppendLine(" ,get_name(@Lang, p.province_name_tha, p.province_name_eng) AS \"provinceId\" ");
sql.AppendLine(" ,d.active AS \"active\" ");
sql.AppendLine(" ,d.xmin AS \"rowVersion\" ");
sql.AppendLine("FROM db_district d");
sql.AppendLine("JOIN db_province p on d.province_id = p.province_id");
if (!string.IsNullOrWhiteSpace(request.Keyword))
{
sql.AppendLine("WHERE CONCAT(district_code,' ',district_code_mua,' ',district_name_tha,' ',district_name_eng,' ',get_name(@Lang, p.province_name_tha, p.province_name_eng)) ILIKE '%' || @Keyword || '%'");
}
return(await _context.GetPage(sql.ToString(), new { Keyword = request.Keyword, Lang = _user.Language }, (RequestPageQuery)request, cancellationToken));
}
public async Task <PageDto> Handle(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" SELECT f.fac_code AS \"facCode\" ");
sql.AppendLine(" , d.department_code AS \"departmentCode\" ");
sql.AppendLine(" , p.program_code AS \"programCode\" ");
sql.AppendLine(" , get_name(@lang, f.fac_name_tha, f.fac_name_eng) AS \"facName\" ");
sql.AppendLine(" , get_name(@lang, d.department_name_tha, d.department_name_eng ) AS \"departmentName\" ");
sql.AppendLine(" , p.program_name_tha AS \"programNameTha\" ");
sql.AppendLine(" , p.program_name_eng AS \"programNameEng\" ");
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(" , fp.active ");
sql.AppendLine(" , fp.xmin AS \"rowVersion\" ");
sql.AppendLine(" FROM db_fac_program fp ");
sql.AppendLine(" JOIN db_fac f ");
sql.AppendLine(" ON f.company_code = fp.company_code ");
sql.AppendLine(" AND f.fac_code = fp.fac_code ");
sql.AppendLine(" JOIN db_program p ");
sql.AppendLine(" ON p.company_code = fp.company_code ");
sql.AppendLine(" AND p.program_code = fp.program_code ");
sql.AppendLine(" LEFT JOIN db_fac_program_detail fpd ");
sql.AppendLine(" ON fpd.fac_code = fp.fac_code ");
sql.AppendLine(" AND fpd.program_code = fp.program_code ");
sql.AppendLine(" AND fpd.company_code = fp.company_code ");
sql.AppendLine(" LEFT JOIN db_department d ");
sql.AppendLine(" ON d.company_code = fpd.company_code ");
sql.AppendLine(" AND d.department_code = fpd.department_code ");
sql.AppendLine(" WHERE fp.company_code = @companyCode ");
if (!string.IsNullOrWhiteSpace(request.Keyword))
{
sql.AppendLine(" AND CONCAT( get_name(@lang, f.fac_name_tha, f.fac_name_eng), get_name(@lang, p.program_name_tha, p.program_name_eng) ) ILIKE '%' || COALESCE(@keyword, '') || '%' ");
}
sql.AppendLine(" ORDER BY f.fac_code, d.department_code, p.program_code ");
return(await _context.GetPage(sql.ToString(), new { companyCode = _user.Company, keyword = request.Keyword, lang = _user.Language }, (RequestPageQuery)request, cancellationToken));
}
public async Task <PageDto> Handle(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" select password_policy_code as \"passwordPolicyCode\",");
sql.AppendLine(" password_policy_name as \"passwordPolicyName\",");
sql.AppendLine(" password_policy_description as \"passwordPolicyDescription\",");
sql.AppendLine(" active,");
sql.AppendLine(" xmin as \"rowVersion\"");
sql.AppendLine(" from su_password_policy");
if (!string.IsNullOrWhiteSpace(request.Keyword))
{
sql.AppendLine(" where concat(password_policy_code,");
sql.AppendLine(" password_policy_name,");
sql.AppendLine(" password_policy_description)");
sql.AppendLine(" ilike concat('%', @Keyword, '%')");
}
return(await _context.GetPage(sql.ToString(), new { Keyword = request.Keyword }, (RequestPageQuery)request, cancellationToken));
}
public async Task <PageDto> Handle(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" SELECT p.province_id as \"provinceId\" ");
sql.AppendLine(" ,p.province_code as \"provinceCode\" ");
sql.AppendLine(" ,p.province_code_mua as \"provinceCodeMua\" ");
sql.AppendLine(" ,p.province_name_tha as \"provinceNameTha\" ");
sql.AppendLine(" ,p.province_name_eng as \"provinceNameEng\" ");
sql.AppendLine(" ,get_name(@Lang,c.country_name_tha,c.country_name_eng) as \"countryId\" ");
sql.AppendLine(" ,p.active as \"active\" ");
sql.AppendLine(" ,p.xmin as \"rowVersion\" ");
sql.AppendLine("FROM db_province p");
sql.AppendLine("JOIN db_country c on p.country_id = c.country_id");
sql.AppendLine("ORDER BY length(province_code),province_code");
if (!string.IsNullOrWhiteSpace(request.Keyword))
{
sql.AppendLine("WHERE CONCAT(province_code,' ',province_code_mua,' ',province_name_tha,' ',province_name_eng,' ',get_name(@Lang,c.country_name_tha,c.country_name_eng)) ILIKE '%' || @Keyword || '%'");
}
return(await _context.GetPage(sql.ToString(), new { Keyword = request.Keyword, Lang = _user.Language }, (RequestPageQuery)request, cancellationToken));
}
public async Task <PageDto> Handle(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" SELECT country_id as \"countryId\" ");
sql.AppendLine(" ,country_code as \"countryCode\" ");
sql.AppendLine(" ,country_code_mua as \"countryCodeMua\" ");
sql.AppendLine(" ,country_name_tha as \"countryNameTha\" ");
sql.AppendLine(" ,country_name_eng as \"countryNameEng\" ");
sql.AppendLine(" ,country_short_name_tha as \"countryShortNameTha\" ");
sql.AppendLine(" ,country_short_name_eng as \"countryShortNameEng\" ");
sql.AppendLine(" ,active as \"active\" ");
sql.AppendLine(" ,xmin as \"rowVersion\" ");
sql.AppendLine("FROM db_country");
sql.AppendLine("ORDER BY length(country_code),country_code");
if (!string.IsNullOrWhiteSpace(request.Keyword))
{
sql.AppendLine("WHERE CONCAT(country_code,' ',country_code_mua,' ',country_name_tha,' ',country_name_eng,' ',country_short_name_tha,' ',country_short_name_eng) ILIKE '%' || @Keyword || '%'");
}
return(await _context.GetPage(sql.ToString(), new { Keyword = request.Keyword }, (RequestPageQuery)request, cancellationToken));
}
public async Task <PageDto> Handle(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT company_code AS \"companyCode\",");
sql.AppendLine(" company_name_tha AS \"companyNameTha\",");
sql.AppendLine(" company_name_eng AS \"companyNameEng\",");
sql.AppendLine(" active,");
sql.AppendLine(" xmin AS \"rowVersion\"");
sql.AppendLine("FROM su_company");
if (!string.IsNullOrWhiteSpace(request.Keyword))
{
sql.AppendLine("WHERE CONCAT(company_code,");
sql.AppendLine(" company_name_tha,");
sql.AppendLine(" company_name_eng)");
sql.AppendLine(" ILIKE CONCAT('%', @Keyword, '%')");
}
return(await _context.GetPage(sql.ToString(), new { Keyword = request.Keyword }, (RequestPageQuery)request, cancellationToken));
}
public async Task <PageDto> Handle(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" SELECT CASE @Language WHEN @Tha THEN d.status_desc_tha ELSE COALESCE(d.status_desc_eng, d.status_desc_tha) END AS \"systemCode\",");
sql.AppendLine(" a.menu_code AS \"menuCode\",");
sql.AppendLine(" a.main_menu AS \"mainMenu\",");
sql.AppendLine(" a.program_code AS \"programCode\",");
sql.AppendLine(" b.menuNameTha AS \"menuNameTha\",");
sql.AppendLine(" b.menuNameEng AS \"menuNameEng\",");
sql.AppendLine(" a.active,");
sql.AppendLine(" a.xmin AS \"rowVersion\"");
sql.AppendLine(" FROM su_menu AS a");
sql.AppendLine(" LEFT JOIN (SELECT x.menu_code,");
sql.AppendLine(" x.menu_name AS menuNameTha,");
sql.AppendLine(" y.menu_name AS menuNameEng");
sql.AppendLine(" FROM su_menu_label AS x");
sql.AppendLine(" LEFT JOIN (SELECT menu_code, menu_name 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(" LEFT JOIN db_status AS d");
sql.AppendLine(" ON a.system_code = d.status_value");
sql.AppendLine(" AND d.table_name = @TableName");
sql.AppendLine(" AND d.column_name = @ColumnName");
if (!string.IsNullOrWhiteSpace(request.Keyword))
{
sql.AppendLine("WHERE CONCAT(d.status_desc_tha,");
sql.AppendLine(" d.status_desc_eng,");
sql.AppendLine(" a.menu_code,");
sql.AppendLine(" a.main_menu,");
sql.AppendLine(" a.program_code,");
sql.AppendLine(" b.menuNameTha,");
sql.AppendLine(" b.menuNameEng)");
sql.AppendLine(" ILIKE CONCAT('%', @Keyword, '%')");
}
return(await _context.GetPage(sql.ToString(), new { Language = this._user.Language, Tha = "th", Eng = "en", TableName = "su_menu", ColumnName = "system_code", Keyword = request.Keyword }, (RequestPageQuery)request, cancellationToken));
}
public async Task <PageDto> Handle(Query request, CancellationToken cancellationToken)
{
request.CompanyCode = _user.Company;
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT education_type_level as \"educationTypeLevel\" ");
sql.AppendLine(" , prefix as \"prefix\", format_code as \"formatCode\", education_type_code_mua as \"educationTypeCodeMua\" ");
sql.AppendLine(" , education_type_level_name_tha as \"educationTypeLevelNameTha\",education_type_level_name_eng as \"educationTypeLevelNameEng\" ");
sql.AppendLine(" , education_level_short_name_tha as \"educationLevelShortNameTha\",education_level_short_name_eng as \"educationLevelShortNameEng\" ");
sql.AppendLine(" , graduated_name_tha as \"graduatedNameTha\", graduated_name_eng as \"graduatedNameEng\" ");
sql.AppendLine(" , list_item_name_tha as \"groupLevel\",db_education_type_level.active ");
sql.AppendLine(" , db_education_type_level.xmin as \"rowVersion\" ");
sql.AppendLine("FROM db_education_type_level ");
sql.AppendLine("JOIN db_list_item ON (db_education_type_level.group_level = db_list_item.list_item_code AND list_item_group_code = 'EduGroupLevel' ) ");
sql.AppendLine("WHERE db_education_type_level.company_code = @Company");
if (!string.IsNullOrWhiteSpace(request.Keyword))
{
sql.AppendLine("AND CONCAT(education_type_level,prefix,format_code,education_type_code_mua,education_type_level_name_tha,education_type_level_name_eng,education_level_short_name_tha,education_level_short_name_eng) ILIKE '%' || @Keyword || '%'");
}
return(await _context.GetPage(sql.ToString(), new { Company = _user.Company, Keyword = request.Keyword }, (RequestPageQuery)request, cancellationToken));
}
public async Task <PageDto> Handle(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT a.pre_name_id as \"preNameId\",");
sql.AppendLine(" a.pre_name_code_mua as \"preNameCodeMua\",");
sql.AppendLine(" a.pre_name_tha as \"preNameTha\",");
sql.AppendLine(" a.pre_name_eng as \"preNameEng\",");
sql.AppendLine(" a.pre_name_short_tha as\"preNameShortTha\",");
sql.AppendLine(" a.pre_name_short_eng as\"preNameShortEng\",");
sql.AppendLine(" CASE @Language WHEN @Tha THEN list_item_name_tha ELSE COALESCE(list_item_name_tha) END as\"preNameType\",");
sql.AppendLine(" a.active,a.xmin as \"rowVersion\"");
sql.AppendLine("FROM db_pre_name as a");
sql.AppendLine("RIGHT JOIN db_list_item as b");
sql.AppendLine("ON a.pre_name_type = b.list_item_code");
sql.AppendLine("WHERE b.list_item_group_code = 'PreNameType'");
if (!string.IsNullOrWhiteSpace(request.Keyword))
{
sql.AppendLine("AND CONCAT(pre_name_id,' ',pre_name_code_mua,' ',pre_name_tha,' ',pre_name_eng,' ',pre_name_short_tha,' ',pre_name_short_eng) ILIKE '%' || @Keyword || '%'");
}
return(await _context.GetPage(sql.ToString(), new { Language = this._user.Language, Company = this._user.Company, Tha = "th", TableName = "db_pre_name", ColumnName = "pre_name_type", Keyword = request.Keyword }, (RequestPageQuery)request, cancellationToken));
}
public async Task <PageDto> Handle(Query request, CancellationToken cancellationToken)
{
request.CompanyCode = _user.Company;
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT et.education_type_code as \"educationTypeCode\",et.education_type_code_mua as \"educationTypeCodeMua\" ");
sql.AppendLine(",et.education_type_name_tha as \"educationTypeNameTha\",et.education_type_name_eng as \"educationTypeNameEng\" ");
sql.AppendLine(",et.education_short_name_tha as \"educationShortNameTha\",et.education_short_name_eng as \"educationShortNameEng\" ");
sql.AppendLine(",et.summary_year as \"summaryYear\",et.new_level_code as \"newLevelCode\" ");
sql.AppendLine(",get_name(@Lang,etl.education_type_level_name_tha,etl.education_type_level_name_eng) as \"typeLevel\" ");
sql.AppendLine(",et.format_code as \"formatCode\",et.active,et.xmin as \"rowVersion\" ");
sql.AppendLine("FROM db_education_type et ");
sql.AppendLine("JOIN db_education_type_level etl ON (etl.education_type_level = et.type_level AND etl.company_code = @Company ) ");
sql.AppendLine("WHERE et.company_code = @Company ");
if (!string.IsNullOrWhiteSpace(request.Keyword))
{
sql.AppendLine("AND CONCAT(get_name(@Lang,etl.education_type_level_name_tha,etl.education_type_level_name_eng) ");
sql.AppendLine(",et.education_type_code_mua,et.education_type_code ");
sql.AppendLine(",et.education_type_name_tha,et.education_type_name_eng ");
sql.AppendLine(",et.education_short_name_tha,et.education_short_name_eng ) ILIKE '%' || @Keyword || '%' ");
}
return(await _context.GetPage(sql.ToString(), new { Company = _user.Company, Keyword = request.Keyword, Lang = _user.Language }, (RequestPageQuery)request, cancellationToken));
}
public async Task <PageDto> Handle(Query request, CancellationToken cancellationToken)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" SELECT parameter_group_code AS \"parameterGroupCode\",");
sql.AppendLine(" parameter_code AS \"parameterCode\",");
sql.AppendLine(" parameter_value AS \"parameterValue\",");
sql.AppendLine(" remark,");
sql.AppendLine(" xmin AS \"rowVersion\"");
sql.AppendLine(" FROM su_parameter");
if (!string.IsNullOrWhiteSpace(request.Keyword) || !string.IsNullOrEmpty(request.ParameterGroupCode))
{
sql.AppendLine("WHERE");
if (!string.IsNullOrEmpty(request.ParameterGroupCode))
{
sql.AppendLine(" parameter_group_code = @ParameterGroupCode");
if (!string.IsNullOrWhiteSpace(request.Keyword))
{
sql.AppendLine("AND");
}
}
if (!string.IsNullOrWhiteSpace(request.Keyword))
{
sql.AppendLine(" CONCAT( parameter_code,");
sql.AppendLine(" parameter_value,");
sql.AppendLine(" remark)");
sql.AppendLine(" ILIKE CONCAT('%', @Keyword, '%')");
}
}
return(await _context.GetPage(sql.ToString(), new { ParameterGroupCode = request.ParameterGroupCode, Keyword = request.Keyword }, (RequestPageQuery)request, cancellationToken));
}
public async Task <PageDto> Handle(Query request, CancellationToken cancellationToken)
{
var student = await _context.GetParameterValue <string>("SuUserType", "Student", cancellationToken);
StringBuilder sql = new StringBuilder();
sql.AppendLine(" select u.user_id as id,u.user_name as \"username\" ");
if (request.UserType == student)
{
sql.AppendLine(" ,e.student_code as \"employeeCode\" ");
sql.AppendLine(" , get_name(@Lang,e.student_name_tha,e.student_name_eng) as \"employeeName\" ");
}
else
{
sql.AppendLine(" ,e.employee_code as \"employeeCode\" ");
sql.AppendLine(" , get_name(@Lang,e.t_name_concat,e.e_name_concat) as \"employeeName\" ");
}
sql.AppendLine(" , u.active,u.force_change_password as \"forceChange\",case when u.lockout_end >= current_timestamp then true else false end as locked ");
sql.AppendLine(" , u.xmin as \"rowVersion\" ");
sql.AppendLine(" from su_user u ");
sql.AppendLine(" inner join su_user_type t on t.user_id = u.user_id ");
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(" and group_type_code = @UserGroup ");
}
sql.AppendLine(" where 1=1 ");
if (!string.IsNullOrEmpty(request.Username))
{
sql.AppendLine("and u.user_name ilike concat('%',@Username,'%') ");
}
if (request.UserType == student && !string.IsNullOrEmpty(request.Employee))
{
sql.AppendLine("and replace(concat(e.student_code,e.student_name_tha,e.student_name_eng),' ','') ilike concat('%',replace(@Employee,' ',''),'%') ");
}
else if (request.UserType != student && !string.IsNullOrEmpty(request.Employee))
{
sql.AppendLine("and replace(concat(e.employee_code,e.t_name_concat,e.e_name_concat),' ','') ilike concat('%',replace(@Employee,' ',''),'%') ");
}
switch (request.Status)
{
case "A":
sql.AppendLine(" AND u.active = true ");
break;
case "I":
sql.AppendLine(" AND coalesce(u.active,false) = false");
break;
case "F":
sql.AppendLine(" AND u.force_change_password = true ");
break;
case "L":
sql.AppendLine(" AND u.lockout_end is not null ");
sql.AppendLine(" AND current_timestamp <= lockout_end ");
break;
}
return(await _context.GetPage(sql.ToString(), new
{
Company = this._user.Company,
Username = request.Username,
Employee = request.Employee,
Status = request.Status,
UserGroup = request.UserGroup,
Lang = _user.Language
}, (RequestPageQuery)request, cancellationToken));
}