TRA061EXP.jsp

<%@page contentType="text/html; charset=UTF-8"%>
<%@page import="com.csc.library.entry.HelpEntry,com.csc.library.system.*,com.csc.library.utilities.*"%>
<%@page import="com.csc.library.entry.*,com.csc.library.database.*,com.csc.library.session.*"%>
<%@page import="com.csc.library.components.*,java.rmi.RemoteException"%>

<jsp:useBean id="TRA061" class="com.csc.library.system.Task" scope="page"/>
<jsp:useBean id="TRA061_SEARCH" class="com.csc.library.system.Task" scope="page"/>

<%

response.setHeader("Cache-Control","must-revalidate");
response.setContentType("application/vnd.ms-excel; UTF-8");
response.setHeader("Content-Disposition", "inline; filename=" + "export.xls");

TRA061.setChannel(request ,response);
ProcessEntry screen=(ProcessEntry)TRA061.process("ProcessEntry","Process");
screen.process();

TRA061_SEARCH.setChannel(request, response);
screenque = (HelpEntry) TRA061_SEARCH.process("HelpEntry","TRA061HELP");

CheckNull chk = CheckNull.getInstance();
String cmd = chk.chkNullString(request.getParameter("__cmd"));
String lang = screen.getUProfile().get("lang");
String screenTitle = lang.equalsIgnoreCase("tha") ? "กำหนดเงื่อนไขหลักสูตร" : "กำหนดเงื่อนไขหลักสูตร";

String tname = "โอนย้ายพนักงานเข้าหลักสูตร";
String ename = "Tranfer Employee To Course";

String col1 = chk.chkNullString(request.getParameter("__col1"));
String col2 = chk.chkNullString(request.getParameter("__col2"));
String col3 = chk.chkNullString(request.getParameter("__col3"));
String col4 = chk.chkNullString(request.getParameter("__col4"));
String col5 = chk.chkNullString(request.getParameter("__col5"));
String col6 = chk.chkNullString(request.getParameter("__col6"));

String col1data = chk.chkNullString(request.getParameter("__col1data"));
String col2data = chk.chkNullString(request.getParameter("__col2data"));
String col3data = chk.chkNullString(request.getParameter("__col3data"));
String col4data = chk.chkNullString(request.getParameter("__col4data"));
String col5data = chk.chkNullString(request.getParameter("__col5data"));
String col6data = chk.chkNullString(request.getParameter("__col6data"));

String crsid = chk.chkNullString(request.getParameter("__crsid"));
String searchall = chk.chkNullString(request.getParameter("__searchall"));
String searchprobation = chk.chkNullString(request.getParameter("__searchprobation"));
String crsrequire = chk.chkNullString(request.getParameter("__crsrequire"));
String condition = chk.chkNullString(request.getParameter("__condition"));
String orderby = chk.chkNullString(request.getParameter("__orderby"));
String goPage = chk.chkNullString(request.getParameter("__goPage"),"1");
goPage = goPage.replace("+","");
goPage = goPage.replace("-","");
int searchdata = 0;
String crslist = "";
String filter = "";
String filter2 = "";
String mainFilter = "";
String filterEmpInCondition = "";
String filterEmpPassInCourse = "";
String filterEmpNotInCourse = "";
int empInCondition = 0;
int empPassInCourse = 0;
int empNotInCourse = 0;

inqHtraintrnee = new InitialInquiry(screenque.getUProfile()).getDbInquiry("htraintrnee");

mcourseconfig = new InitialRecord(screenque.getUProfile()).getDbRecord("MCOURSECONFIG");
mcourseconfig.setColumn("*");
mcourseconfig.set("CRSID", crsid);

if (cmd.equalsIgnoreCase("generatedata") || cmd.equalsIgnoreCase("saveconfig") || cmd.equalsIgnoreCase("cleanconfig") || cmd.equalsIgnoreCase("save") || cmd.equalsIgnoreCase("exportdata")) {
    mcourseconfig.set("searchall", searchall);
    mcourseconfig.set("searchprobation", searchprobation);
    mcourseconfig.set("crsrequire", crsrequire);
    mcourseconfig.set("condition", condition);
    mcourseconfig.set("orderby", orderby);
    mcourseconfig.set("col1", col1);
    mcourseconfig.set("col2", col2);
    mcourseconfig.set("col3", col3);
    mcourseconfig.set("col4", col4);
    mcourseconfig.set("col5", col5);
    mcourseconfig.set("col6", col6);

    if (cmd.equalsIgnoreCase("saveconfig")) {
        mcourseconfig.save();
        String msg = lang.equalsIgnoreCase("tha") ? "บันทึกข้อมูลเรียบร้อย" : "Save data completely.";
        out.println("<script>alert('"+msg+"');</script>");
        searchdata = 1;
    }
} else if (cmd.equalsIgnoreCase("searchconfig") || cmd.equalsIgnoreCase("deleteconfig")) {
    searchdata = mcourseconfig.search();

    if (cmd.equalsIgnoreCase("deleteconfig") && searchdata == 1) {
        mcourseconfig.delete();
        String msg = lang.equalsIgnoreCase("tha") ? "ลบข้อมูลเรียบร้อย" : "Delete data sucessfully.";
        out.println("<script>alert('"+msg+"');</script>");
        searchdata = 0;
    } else if (cmd.equalsIgnoreCase("deleteconfig") && searchdata == 0) {
        String msg = lang.equalsIgnoreCase("tha") ? "ลบข้อมูลไม่ได้ เนื่องจากไม่มีข้อมูล" : "Data not found, Unable to delete.";
        out.println("<script>alert('"+msg+"');</script>");
        searchdata = 0;
    }
}

filter                      = "select traineeid from htraintrnee where trainingid in (select trainingid from htraining where crsid = '"+mcourseconfig.getString("crsid")+"') and trstatid in (select trstatid from mtrainstat where trsuccess = 'Y')";
mainFilter                  = "employeeid not in (" + filter + ")";
filterEmpPassInCourse       = "employeeid in (" + filter + ")";
filterEmpNotInCourse        = "employeeid not in (" + filter + ")";

if (!mcourseconfig.getString("crsrequire").equals("")) {
    filter2                 = "select traineeid from htraintrnee where trainingid in (select trainingid from htraining where " + getFilterCourseRequire(mcourseconfig.getString("crsrequire")) + ") and trstatid in (select trstatid from mtrainstat where trsuccess = 'Y')";
    //filterEmpInCondition  = chkFilter(filterEmpInCondition)   + "employeeid in (" + filter2 + ")";
    filterEmpPassInCourse   = chkFilter(filterEmpPassInCourse)  + "employeeid in (" + filter2 + ")";
    filterEmpNotInCourse    = "employeeid not in (select employeeid from memployee where employeeid in (" + filter + ") and employeeid in (" + filter2 + "))";
    mainFilter              = "employeeid not in (select employeeid from memployee where employeeid in (" + filter + ") and employeeid in (" + filter2 + "))";
}
if(mcourseconfig.getString("searchall").equals("")) {
    //mainFilter              = chkFilter(mainFilter)             + "employeeid in (" + filter + ")";
    //filterEmpInCondition    = chkFilter(filterEmpInCondition)   + "employeeid in (" + filter + ")";
    //filterEmpPassInCourse   = chkFilter(filterEmpPassInCourse)  + "employeeid in (" + filter + ")";
    //filterEmpNotInCourse    = chkFilter(filterEmpNotInCourse)   + "employeeid not in (" + filter + ")";
} else if(mcourseconfig.getString("searchall").equals("1")) {
    mainFilter              = "";
}
if(mcourseconfig.getString("searchprobation").equals("")) {
    CscCalendar today       = new CscCalendar();
    mainFilter              = chkFilter(mainFilter)             + "status in (select statuscode from mempl_status where statustype = 'A' and statuscode != 'V')";
    filterEmpInCondition    = chkFilter(filterEmpInCondition)   + "status in (select statuscode from mempl_status where statustype = 'A' and statuscode != 'V')";
    filterEmpPassInCourse   = chkFilter(filterEmpPassInCourse)  + "status in (select statuscode from mempl_status where statustype = 'A' and statuscode != 'V')";
    filterEmpNotInCourse    = chkFilter(filterEmpNotInCourse)   + "status in (select statuscode from mempl_status where statustype = 'A' and statuscode != 'V')";
} else if(mcourseconfig.getString("searchprobation").equals("1")) {
    mainFilter              = chkFilter(mainFilter)             + "status in (select statuscode from mempl_status where statustype = 'A')";
    filterEmpInCondition    = chkFilter(filterEmpInCondition)   + "status in (select statuscode from mempl_status where statustype = 'A')";
    filterEmpPassInCourse   = chkFilter(filterEmpPassInCourse)  + "status in (select statuscode from mempl_status where statustype = 'A')";
    filterEmpNotInCourse    = chkFilter(filterEmpNotInCourse)   + "status in (select statuscode from mempl_status where statustype = 'A')";
}
if (!mcourseconfig.getString("condition").equals("")) {
    mainFilter              = chkFilter(mainFilter)             + "(" + mcourseconfig.getString("condition").replaceAll("workage.","") + ")";
    filterEmpInCondition    = chkFilter(filterEmpInCondition)   + "(" + mcourseconfig.getString("condition").replaceAll("workage.","") + ")";
    filterEmpPassInCourse   = chkFilter(filterEmpPassInCourse)  + "(" + mcourseconfig.getString("condition").replaceAll("workage.","") + ")";
    filterEmpNotInCourse    = chkFilter(filterEmpNotInCourse)   + "(" + mcourseconfig.getString("condition").replaceAll("workage.","") + ")";
}

/*out.println("##filter ==> "+filter);
out.println("<BR>##filter2 ==> "+filter2);
out.println("<BR>##filterEmpInCondition ==> "+filterEmpInCondition);
out.println("<BR>##filterEmpPassInCourse ==> "+filterEmpPassInCourse);
out.println("<BR>##filterEmpNotInCourse ==> "+filterEmpNotInCourse);
out.println("<BR>##mainFilter ==> "+mainFilter);*/

inqEmp = new InitialInquiry(screenque.getUProfile()).getDbInquiry("memployee");
inqEmp.setColumn("*");

inqEmp.setFilter(filterEmpInCondition);
inqEmp.refresh();
empInCondition = inqEmp.recCount();

inqEmp.setFilter(filterEmpPassInCourse);
inqEmp.refresh();
empPassInCourse = inqEmp.recCount();

inqEmp.setFilter(filterEmpNotInCourse);
inqEmp.refresh();
empNotInCourse = inqEmp.recCount();

screenque.getInquiry().setColumn("*");
screenque.getInquiry().setFilter(mainFilter);
screenque.getInquiry().setOrderBy(mcourseconfig.getString("orderby"));

int maxLine = 50;
int beginRow=((Integer.parseInt(goPage)-1)*maxLine)+1;
int endRow=(Integer.parseInt(goPage))*maxLine;
//screenque.setMaxLine(maxLine);
screenque.getInquiry().refresh();
screenque.getInquiry().referLangOff();
//screenque.getInquiry().setBetween(beginRow, endRow);
screenque.getInquiry().clearOldCondition();
screenque.countRec(screenque.getInquiry());
screenque.getInquiry().setRefreshBeforeNext(true);

%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
    <head>
        <title></title>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
        <!--<link href="../CSS/TISCO.css" rel="stylesheet" type="text/css"/>
        <link href="../CSS/bootstrap.min.css" rel="stylesheet" type="text/css"/>-->
        <script type='text/javascript' src='../JS/SCREEN.js'></script>
        <script language="javascript" src="../JS/DATEINPUT.js"></script>
        <script language="javascript" src="../JS/VIEWCALENDAR.js"></script>
        <script type='text/javascript' src='../JS/ICONSMENU.js'></script>
        <script src="../JS/jquery-1.11.1.min.js" type="text/javascript"></script>
        <script language="javascript" src="../JS/CscBase.js"></script>
        <script language="javascript" src="../JS/CscCalendarV3.js"></script>
        <script src="../JS/bootstrap.min.js" type="text/javascript"></script>
        <script language='javascript'>setLang('<%=lang%>');</script>
        <style type="text/css">
        img[height="20"] {
            padding: 0px;
            vertical-align: top;
        }
        label {
            font-weight: normal;
        }
        .btn {
            padding: 2px 12px;
            font-size: 11px;
            vertical-align: top;
        }
        .title1 {
            font-weight: bold !important;
        }
        #tabledata {
            border-spacing: 1px;
            border-collapse: separate;
            background-color: grey;
        }
        .format_text{
            mso-number-format:"\@";/*force text*/
        }

        </style>
        <script language="JavaScript" >
        function GoSave(){
            with(document.cscform){
                if (chkReq()) {
                    __cmd.value = "saveconfig";
                    submit();
                }
            }
        }

        function GoDelete(){
            with(document.cscform){
                if (chkReq()) {
                    __cmd.value = "deleteconfig";
                    submit();
                }
            }
        }

        function GoSearch(){
            with(document.cscform){
                if (chkReq()) {
                    __cmd.value = "generatedata";
                    submit();
                }
            }
        }

        function GoClean(){
            with(document.cscform){
                __cmd.value = "cleanconfig";
                __crsid.value = "";
                __orderby.value = "employeeid";
                __searchall.checked = false;
                __condition.value = "";
                __col1.value = "";
                __col2.value = "";
                __col3.value = "";
                __col4.value = "";
                __col5.value = "";
                __col6.value = "";
                submit();
            }
        }

        function chkReq(){
            var chk = true;

            with(document.cscform){
                if (__crsid.value === "") {
                    var msg = getLang() === "tha" ? "กรุณาเลือก รหัสหลักสูตร" : "Please select Course ID.";
                    alert(msg);
                    chk = false;
                }
            }

            return chk;
        }

        function goPage(page){
            with(document.cscform){
                __cmd.value = "generatedata";
                __goPage.value = page;
                submit();
            }
        }

        function goHelp(helpName,inputName,targetReturn){
            var helpReturn;
            var fixCon = "";
            var oldvalue = "";
            if(inputName=="__crsid" ){
                document.cscform.selectcourse.value = "1";
                helpReturn="__crsid:crsid,MCOURSE@CRSDESC";
                linkHelp_Return(helpName,helpReturn,fixCon);
            }
            if(inputName=="__crsrequire" ){
                helpReturn="__crsrequire:crsid";
                oldvalue = document.cscform.__crsrequire.value;
                openHelp(helpName,helpReturn,fixCon,oldvalue);
            }
            if(inputName=="empInCondition"){
                fixCon = document.cscform.filterEmpInCondition.value;
                helpReturn = "a";
                openHelp(helpName,helpReturn,fixCon);
            }
            if(inputName=="empPassInCourse"){
                fixCon = document.cscform.filterEmpPassInCourse.value;
                helpReturn = "a";
                openHelp(helpName,helpReturn,fixCon);
            }
            if(inputName=="empNotInCourse"){
                fixCon = document.cscform.filterEmpNotInCourse.value;
                helpReturn = "a";
                openHelp(helpName,helpReturn,fixCon);
            }
            if(inputName.indexOf("conditionparam") !== -1 ){
                document.getElementById("searchstatus").value="1";
                helpReturn=inputName+":"+targetReturn;
                linkHelp_Return(helpName,helpReturn,fixCon);
            }
        }

        function postReturn(){
            with(document.cscform){
                if ( selectcourse.value == "1" ){
                    selectcourse.value = "";
                    __cmd.value = "searchconfig";
                    submit();
                }
                if ( document.getElementById("searchstatus").value == "1") {
                    document.getElementById("searchstatus").value = "";
                    generateCondition();
                }
            }
        }

        function openHelp(help,input,fixCon,oldvalue){
            with(document.cscform){
                var filter = fixCon || __fixCon.value;
                var oldval = oldvalue || "";
                var param="TRA061HELP.jsp?__helpName="+help+"&__helpReturn="+input+"&__fixCon="+filter+"&__oldValue="+oldval;
                win = window.open(param,"Help","left=150,top=150,width=800,height=400,toolbar=no,status=yes,scrollbars=yes,resizable=yes");
                if(window.focus){win.focus();}
            }
        }

        $(function() {
            createConditionParam();

            $('form[name=cscform]').on('click', '#configsave', function() {
                GoSave();
            }).on('click', '#configdelete', function() {
                GoDelete();
            }).on('click', '#configclear', function() {
                GoClean();
            }).on('click', '#addconditionand', function() {
                $('#oparetorselected').val("AND");
                generateCondition();
            }).on('click', '#addconditionor', function() {
                $('#oparetorselected').val("OR");
                generateCondition();
            }).on('click', '#addconditionparam', function() {
                addConditionParam();
            }).on('click', '.cdelete', function() {
                $('.cline:eq('+$(this).index('.cdelete')+')').remove();
                generateCondition();
            }).on('click', '#generatedata', function() {
                GoSearch();
            }).on('click', '#exportdata', function() {

            }).on('click', '#tranferdata', function() {

            }).on('change', '.coparetorselected', function() {
                generateCondition();
            }).on('change', '.cfieldselected', function() {
                changeConditionInput($(this),'.cfieldselected');
            });

            $('.colhead1').text( $('select[name=__col1]').find('option:selected').text() );
            $('.colhead2').text( $('select[name=__col2]').find('option:selected').text() );
            $('.colhead3').text( $('select[name=__col3]').find('option:selected').text() );
            $('.colhead4').text( $('select[name=__col4]').find('option:selected').text() );
            $('.colhead5').text( $('select[name=__col5]').find('option:selected').text() );
            $('.colhead6').text( $('select[name=__col6]').find('option:selected').text() );
        });

        function createConditionParam(){
            var condition = $('textarea[name=__condition]').val();
            if(condition === ""){
                addConditionParam();
            } else {
                var condition_arr = [condition];
                if(condition.indexOf(" AND ") !== -1) {
                    condition_arr = condition.split(" AND ");
                    $('#oparetorselected').val("AND");
                } else if(condition.indexOf(" OR ") !== -1) {
                    condition_arr = condition.split(" OR ");
                    $('#oparetorselected').val("OR");
                }
                for(var i=0; i<condition_arr.length; i++){
                    var index = $('#line').val();
                    var fieldstart = 0;
                    if (condition_arr[i].charAt(0) == "(") fieldstart = 1;
                    var field = condition_arr[i].substring(fieldstart,condition_arr[i].indexOf(" "));
                    var oparetor = condition_arr[i].substring(condition_arr[i].indexOf(" ")+1,condition_arr[i].indexOf("'")-1);
                    var inputdata = condition_arr[i].substring(condition_arr[i].indexOf("'")+1,condition_arr[i].lastIndexOf("'"));
                    addConditionParam();
                    $('.cfieldselected:eq('+index+')').val(field);
                    changeConditionInput($('.cfieldselected:eq('+index+')'),'.cfieldselected');
                    $('.coparetorselected:eq('+index+')').val(oparetor);
                    if($('.ctargetinput:eq('+index+')').hasClass('calendar')){
                        var date = new CscCalendar(inputdata);
                        $('.ctargetinput:eq('+index+')').val(date.getDDMMYYYY());
                    } else {
                        $('.ctargetinput:eq('+index+')').val(inputdata);
                    }
                }
            }
        }

        function generateCondition(){
            var condition = "";
            $('.cline').each(function(index) {
                if (condition !== "") {
                    condition += " " + $('#oparetorselected').val() + " ";
                }
                condition += $('.cfieldselected:eq('+index+')').val() + " ";
                condition += $('.coparetorselected:eq('+index+')').val() + " ";
                if($('.ctargetinput:eq('+index+')').hasClass('calendar')){
                    var date = new CscCalendar($('.ctargetinput:eq('+index+')').val());
                    condition += "'" + date.getYYYYMMDD() + "'";
                } else {
                    condition += "'" + $('.ctargetinput:eq('+index+')').val() + "'";
                }
            });
            $('textarea[name=__condition]').val(condition);
        }

        function changeConditionInput($this,targetindex){
            var index = $this.index(targetindex);
            var name = $('.cline:eq('+index+')').attr('id');
            var targetfield = $this.find('option:selected').attr('targetreturn');
            var type = $this.find('option:selected').attr('type');
            var helpName = $this.find('option:selected').attr('help');
            $('.ctargetinput:eq('+index+')').remove();
            $('.cgohelp:eq('+index+')').remove();
            $('.tdinput:eq('+index+')')
            .append( getTargetInput(name, type) )
            .append( getGoHelp(name, targetfield, type, helpName) );
        }

        function addConditionParam(targetfield, gohelptype, help){
            var $line = $('#line');
            var line = parseInt( $line.val() );
            var name = "conditionparam"+line;
            var targetfield = targetfield || "employeeid";
            var gtype = gohelptype || "gohelp";
            var helpName = help || "MEMPLOYEEHELP";
            $('#conditionparam')
            .append(
                $('<tr>').addClass('cline').attr('id',name)
                .append(
                    $('<td>')
                    .append(
                        $('<select>')
                        .addClass('cfieldselected')
                        .attr('onchange','sysIsEdit();')
                        // add option Here
                        .append($('<option>').val('employeeid').text('employeeid : <%=screenque.getLabel("employeeid")%>').attr('type','gohelp').attr('help','MEMPLOYEEHELP').attr('targetreturn','employeeid'))
                        .append($('<option>').val('fname').text('fname : <%=screenque.getLabel("fname")%>').attr('type','gohelp').attr('help','MEMPLOYEEHELP').attr('targetreturn','fname'))
                        .append($('<option>').val('lname').text('lname : <%=screenque.getLabel("lname")%>').attr('type','gohelp').attr('help','MEMPLOYEEHELP').attr('targetreturn','lname'))
                        .append($('<option>').val('efname').text('efname : <%=screenque.getLabel("efname")%>').attr('type','gohelp').attr('help','MEMPLOYEEHELP').attr('targetreturn','efname'))
                        .append($('<option>').val('elname').text('elname : <%=screenque.getLabel("elname")%>').attr('type','gohelp').attr('help','MEMPLOYEEHELP').attr('targetreturn','elname'))
                        .append($('<option>').val('emp_position').text('emp_position : <%=screenque.getLabel("emp_position")%>').attr('type','gohelp').attr('help','MPOSITIONHELP').attr('targetreturn','positionid'))
                        .append($('<option>').val('pl').text('pl : <%=screenque.getLabel("pl")%>').attr('type','gohelp').attr('help','MPERSONALLEVELHELP').attr('targetreturn','plid'))
                        .append($('<option>').val('bu1').text('bu1 : <%=screenque.getLabel("bu1")%>').attr('type','gohelp').attr('help','MBU1HELP').attr('targetreturn','bu1id'))
                        .append($('<option>').val('bu2').text('bu2 : <%=screenque.getLabel("bu2")%>').attr('type','gohelp').attr('help','MBU2HELP').attr('targetreturn','bu2id'))
                        .append($('<option>').val('bu3').text('bu3 : <%=screenque.getLabel("bu3")%>').attr('type','gohelp').attr('help','MBU3HELP').attr('targetreturn','bu3id'))
                        .append($('<option>').val('bu4').text('bu4 : <%=screenque.getLabel("bu4")%>').attr('type','gohelp').attr('help','MBU4HELP').attr('targetreturn','bu4id'))
                        .append($('<option>').val('bu5').text('bu5 : <%=screenque.getLabel("bu5")%>').attr('type','gohelp').attr('help','MBU5HELP').attr('targetreturn','bu5id'))
                        .append($('<option>').val('eff_position').text('eff_position : วันที่เริ่มตำแหน่ง').attr('type','calendar').attr('help','').attr('targetreturn',''))
                        .append($('<option>').val('eff_pl').text('eff_pl : วันที่เริ่มระดับ').attr('type','calendar').attr('help','').attr('targetreturn',''))
                        .append($('<option>').val('firsthiredate').text('firsthiredate : <%=screenque.getLabel("firsthiredate")%>').attr('type','calendar').attr('help','').attr('targetreturn',''))
                        .append($('<option>').val('startdate').text('startdate : <%=screenque.getLabel("startdate")%>').attr('type','calendar').attr('help','').attr('targetreturn',''))
                        .append($('<option>').val('branch').text('branch : <%=screenque.getLabel("branch")%>').attr('type','gohelp').attr('help','MBRANCHHELP').attr('targetreturn','branchid'))
                        .append($('<option>').val('emp_type').text('emp_type : <%=screenque.getLabel("emp_type")%>').attr('type','gohelp').attr('help','MEMPLOYMENT_TYPEHELP').attr('targetreturn','codeid'))
                        .append($('<option>').val('emp_group').text('emp_group : <%=screenque.getLabel("emp_group")%>').attr('type','gohelp').attr('help','MGROUPHELP').attr('targetreturn','groupid'))
                        .append($('<option>').val('workarea').text('workarea : <%=screenque.getLabel("workarea")%>').attr('type','gohelp').attr('help','MWORKAREAHELP').attr('targetreturn','workareaid'))
                        .append($('<option>').val('job').text('job : <%=screenque.getLabel("job")%>').attr('type','gohelp').attr('help','MJOBCODEHELP').attr('targetreturn','jobcodeid'))
                    )
                )
                .append(
                    $('<td>')
                    .append(
                        $('<select>')
                        .addClass('coparetorselected')
                        .attr('onchange','sysIsEdit();')
                        // add option Here
                        .append($('<option>').val('=').text( getLang() === "tha" ? "เท่ากับ":"Equal" ))
                        .append($('<option>').val('<>').text( getLang() === "tha" ? "ไม่เท่ากับ":"Not Equal" ))
                        .append($('<option>').val('<').text( getLang() === "tha" ? "น้อยกว่า":"Less" ))
                        .append($('<option>').val('<=').text( getLang() === "tha" ? "น้อยกว่าหรือเท่ากับ":"Less or Equal" ))
                        .append($('<option>').val('>').text( getLang() === "tha" ? "มากกว่า":"Greater" ))
                        .append($('<option>').val('>=').text( getLang() === "tha" ? "มากกว่าหรือเท่ากับ":"Greater or Equal" ))
                        .append($('<option>').val('like').text( getLang() === "tha" ? "เหมือนกับ":"Contains" ))
                    )
                )
                .append(
                    $('<td>').addClass('tdinput')
                    .append( getTargetInput(name, gtype) )
                    .append( getGoHelp(name, targetfield, gtype, helpName) )
                )
                .append($('<td>')
                .append($('<button>')
                .addClass('btn btn-primary cdelete')
                .attr('type','button')
                .text('Delete')
            )
        )
    );
    line++;
    $line.val( line );
}

function getTargetInput(name, type){
    var $input = "";
    if(type === "gohelp"){
        $input = $('<input>')
        .addClass('ctargetinput')
        .attr('type','text')
        .attr('size','15')
        .attr('onkeyup','sysIsEdit();')
        .attr('onblur','generateCondition();')
        .attr('name',name);
    } else if(type === "calendar"){
        $input = $('<input>')
        .addClass('ctargetinput calendar')
        .attr('type','text')
        .attr('size','15')
        .attr('maxlength','10')
        .attr('onkeypress','chkInteger(event);')
        .attr('onkeyup','sysIsEdit();formatDate(this);')
        .attr('onblur','generateCondition();')
        .attr('onfocus','this.select();')
        .attr('name',name);
    }
    return $input;
}

function getGoHelp(name, targetfield, type, helpName){
    var $gohelp = "";
    if(type === "gohelp"){
        $gohelp = $('<a>')
        .addClass('cgohelp')
        .attr('href',"javascript:goHelp('"+helpName+"','"+name+"','"+targetfield+"');")
        .attr('onmouseout','MM_swapImgRestore()')
        .attr('onmouseover',"MM_swapImage('"+name+"_image','','../IMAGES/BUTTON/SWAP/SEARCH_SWAP20.gif',1)")
        .append(
            $('<img>')
            .attr('src',"../IMAGES/BUTTON/MAIN/SEARCH20.gif")
            .attr('alt','SEARCH')
            .attr('name',name+'_image')
            .attr('width','20')
            .attr('height','20')
            .attr('border','0')
            .attr('align','absmiddle')
            .attr('style','margin-left: 4px; margin-right: 15px;')
        );
    } else if(type === "calendar"){
        $gohelp = $('<a>')
        .addClass('cgohelp')
        .attr('href',"#")
        .attr('onmouseout','MM_swapImgRestore()')
        .attr('onmouseover',"MM_swapImage('"+name+"_image','','../IMAGES/BUTTON/SWAP/CALENDAR_SWAP20.gif',1)")
        .attr('onclick',"viewCal('"+name+"')")
        .append(
            $('<img>')
            .attr('src',"../IMAGES/BUTTON/MAIN/CALENDAR20.gif")
            .attr('alt','Calendar')
            .attr('name',name+'_image')
            .attr('width','20')
            .attr('height','20')
            .attr('border','0')
            .attr('align','absmiddle')
            .attr('style','margin-left: 4px; margin-right: 15px;')
        );
    }
    return $gohelp;
}

</script>
</head>
<body leftmargin="0" topmargin="0">
  <form name="cscform" method="post" action="#">
    <table class="maxsize" cellpadding="0" cellspacing="3" border="1" id="tabledata">
      <tr>
        <td class="row3" align="center">ลำดับ</td>
        <td class="row3" align="center"><%=screenque.getLabel("employeeid")%></td>
        <td class="row3" align="center"><%=screenque.getLabel("fullname")%></td>
        <td class="row3" align="center"><%=screenque.getLabel("positionname")%></td>
        <td class="row3" align="center"><%=screenque.getLabel("startdate")%></td>
        <%if(!col1.equals("")){%><td class="row3 colhead1" align="center"><%=request.getParameter("__col1name")%></td><%}%>
        <%if(!col2.equals("")){%><td class="row3 colhead2" align="center"><%=request.getParameter("__col2name")%></td><%}%>
        <%if(!col3.equals("")){%><td class="row3 colhead3" align="center"><%=request.getParameter("__col3name")%></td><%}%>
        <%if(!col4.equals("")){%><td class="row3 colhead4" align="center"><%=request.getParameter("__col4name")%></td><%}%>
        <%if(!col5.equals("")){%><td class="row3 colhead5" align="center"><%=request.getParameter("__col5name")%></td><%}%>
        <%if(!col6.equals("")){%><td class="row3 colhead6" align="center"><%=request.getParameter("__col6name")%></td><%}%>
      </tr>
      <%
      screenque.getInquiry().referLangOn();
      int i = 1;
      while(screenque.nextRec()){
        %>
        <tr>
          <td align="center"><%=i%></td>
          <td align="center" class="format_text"><%=screenque.getDataLabel("employeeid")%></td>
          <td align="left"><%=screenque.getDataLabel("fullname")%></td>
          <td align="left"><%=screenque.getDataLabel("positionname")%></td>
          <td align="center"><%=screenque.getDataLabel("startdate")%></td>
          <%if(!col1.equals("")){%><td align="center"><%=showData(col1data)%></td><%}%>
          <%if(!col2.equals("")){%><td align="center"><%=showData(col2data)%></td><%}%>
          <%if(!col3.equals("")){%><td align="center"><%=showData(col3data)%></td><%}%>
          <%if(!col4.equals("")){%><td align="center"><%=showData(col4data)%></td><%}%>
          <%if(!col5.equals("")){%><td align="center"><%=showData(col5data)%></td><%}%>
          <%if(!col6.equals("")){%><td align="center"><%=showData(col6data)%></td><%}%>
        </tr>
        <%
        i++;
      }
      %>
    </table>
  </form>
</body>
</html>
<%!
DbInquiry inqEmp = null;
HelpEntry screenque = null;
DbRecord mcourseconfig = null;
DbInquiry inqHtraintrnee = null;

private String getColumnList(String selectdata){
    StringBuilder sb = new StringBuilder();

    sb.append( getOption(selectdata,    "mpersonallevel.tdesc",        "ระดับพนักงาน PL") );
    //sb.append( getOption(selectdata,    "mposition.tdesc",             screenque.getLabel("emp_position")) );
    sb.append( getOption(selectdata,    "mbu1.tdesc",                  screenque.getLabel("bu1")) );
    sb.append( getOption(selectdata,    "mbu2.tdesc",                  screenque.getLabel("bu2")) );
    sb.append( getOption(selectdata,    "mbu3.tdesc",                  screenque.getLabel("bu3")) );
    sb.append( getOption(selectdata,    "mbu4.tdesc",                  screenque.getLabel("bu4")) );
    sb.append( getOption(selectdata,    "mbu5.tdesc",                  screenque.getLabel("bu5")) );
    sb.append( getOption(selectdata,    "workage.eff_position",        "อายุงานในตำแหน่ง") );
    sb.append( getOption(selectdata,    "workage.eff_pl",              "อายุงานในระดับ") );
    sb.append( getOption(selectdata,    "workage.firsthiredate",       "อายุงานในบริษัท (First Hire Date)") );
    sb.append( getOption(selectdata,    "workage.startdate",           "อายุงานในบริษัท (Strating Date)") );
    sb.append( getOption(selectdata,    "mjobgroup.tdesc",             screenque.getLabel("mjobcode","jobgroup")) );
    sb.append( getOption(selectdata,    "mbranch.tdesc",               screenque.getLabel("branch")) );
    sb.append( getOption(selectdata,    "memployment_type.tdesc",      screenque.getLabel("emp_type")) );
    //sb.append( getOption(selectdata,    "systemcode.emp_type",		   screenque.getLabel("emp_type")) );
    sb.append( getOption(selectdata,    "mgroup.tdesc",                screenque.getLabel("emp_group")) );
    sb.append( getOption(selectdata,    "mworkarea.tdesc",             screenque.getLabel("workarea")) );
    sb.append( getOption(selectdata,    "mjobcode.tdesc",              "ลักษณะงาน") );
    sb.append( getOption(selectdata,    "mjobcode_level.tdesc",        screenque.getLabel("mjobcode","jobcode_level")) );
    sb.append( getOption(selectdata,    "mjobgrade0.tdesc",            "Job Grade") );
    sb.append( getOption(selectdata,    "probation_pass",              "สถานะผ่านทดลองงาน") );
    sb.append( getOption(selectdata,    "course_req",                  "หลักสูตรก่อนหน้า") );
    sb.append( getOption(selectdata,    "course_pass",                 "ผ่านการอบรมมาแล้ว") );

    return sb.toString();
}

private String getOption(String selectdata, String value, String label){
    StringBuilder sb = new StringBuilder();

    sb.append("<option value=\""+value+"\" ");
    if(selectdata.equalsIgnoreCase(value)) {
        sb.append("selected");
    }
    sb.append(">" + label + "</option>");

    return sb.toString();
}

private String getLangLabel(String thai, String eng){
    if (screenque.getUProfile().get("lang").equalsIgnoreCase("tha")) {
        return thai;
    } else {
        return eng;
    }
}

private String showData(String column) throws RemoteException{
    if(!column.equalsIgnoreCase("")){
        if(column.equalsIgnoreCase("probation_pass")){
            CscCalendar probationdate = new CscCalendar(screenque.getDataLabel("APPROVE_DATE"));
            CscCalendar today = new CscCalendar();
            if (probationdate.beforeEqualsDate(today) && !screenque.getDataLabel("STATUS").equalsIgnoreCase("V")) {
                return getLangLabel("ผ่านทดลองงาน","Probation Pass");
            } else {
                return getLangLabel("ไม่ผ่านทดลองงาน","Probation Not Pass");
            }
        } else if(column.equalsIgnoreCase("course_req")){
            if (mcourseconfig.getString("crsrequire").equals("")) {
                return getLangLabel("ผ่านการอบรม","Course Pass");
            } else {
                inqHtraintrnee.setColumn("*");
                inqHtraintrnee.setFilter("traineeid = '"+screenque.getDataLabel("employeeid")+"' and trainingid in (select trainingid from htraining where "+getFilterCourseRequire(mcourseconfig.getString("crsrequire"))+") and trstatid in (select trstatid from mtrainstat where trsuccess = 'Y')");
                inqHtraintrnee.refresh();
                if (inqHtraintrnee.recCount() > 0) {
                    return getLangLabel("ผ่านการอบรม","Course Pass");
                } else {
                    return getLangLabel("ไม่ผ่านการอบรม","Course Not Pass");
                }
            }
        } else if(column.equalsIgnoreCase("course_pass")){
            inqHtraintrnee.setColumn("*");
            inqHtraintrnee.setFilter("traineeid = '"+screenque.getDataLabel("employeeid")+"' and trainingid in (select trainingid from htraining where crsid = '"+mcourseconfig.getString("crsid")+"') and trstatid in (select trstatid from mtrainstat where trsuccess = 'Y')");
            inqHtraintrnee.refresh();
            if (inqHtraintrnee.recCount() > 0) {
                return getLangLabel("ผ่านการอบรม","Course Pass");
            } else {
                return getLangLabel("ไม่ผ่านการอบรม","Course Not Pass");
            }
        } else {
            String table = column.split("\\.")[0];
            String field = column.split("\\.")[1];
            if(table.equalsIgnoreCase("workage")){
                CscCalendar startDate = new CscCalendar(screenque.getDataLabel(field));
                CscCalendar endDate = new CscCalendar();
                return new CountAge(screenque.getUProfile().get("lang")).getCountAges(startDate,endDate,true) ;
            } else if(table.equalsIgnoreCase("systemcode")) {
                return screenque.getSystemCode(field);
            } else {
                return screenque.getDataLabel(table, field);
            }
        }
    }
    return "";
}

private String chkFilter(String str){
    if (!str.equals("")) {
        str += " AND ";
    }
    return str;
}

private String getFilterCourseRequire(String crsrequire){
    String str = "";
    if(!crsrequire.equals("")){
        String[] crs = crsrequire.split(",");
        for (int i = 0; i < crs.length; i++) {
            str = chkFilter(str) + "crsid = '"+crs[i]+"'";
        }
    }
    return str;
}

private String getSystemcode(String[] systemcode){
	String str = "";
	for (int i=0; i<systemcode.length; i++){
		if (!str.equals("")) str += ",";
		str += systemcode[i].replace(",","#");
	}
	return str;
}
%>