<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<%@ page contentType="text/html; charset=UTF-8"%>
<%@ page import="com.csc.library.entry.*,com.csc.library.system.*,com.csc.library.session.*,com.csc.library.utilities.*,java.text.DecimalFormat"%>
<%@ page import="java.util.*,java.io.*,java.sql.*,org.json.simple.*"%>
<%@ page import="java.text.SimpleDateFormat" %>
<%@ page import="java.util.Locale" %>
<%@ page import="java.util.Date" %>
<%@ page import="com.csc.library.components.*" %>
<%@ page import="com.csc.library.report.ReportUDF" %>
<%@ page import="com.csc.api.recruit.*" %>
<%@ page import="com.csc.library.databean.Simmcandidatewithgson" %>
<%@ page import="com.csc.library.databean.Simmapplicantwithgson" %>
<%@ page import="com.csc.library.databean.Simmappointmentwithgson" %>
<%@ page import="com.csc.library.databean.Simmapplicanthisotherwithgson" %>
<%@ page import="com.csc.library.database.MyHashMap" %>
<%@ page import="com.csc.library.database.DataSet" %>
<%-- <%@ page import="com.csc.library.utilities.gsonmapping.*" %>
<%@ page import="com.google.gson.*" %> --%>
<%
CheckNull chk = new CheckNull();
CscCalendar c = new CscCalendar();
ReportUDF udf = new ReportUDF();
UProfile up = null;
if (session !=null){
up = (UProfile)session.getAttribute("userprofile");
}
String lang = up.get("lang");
String submitdate = chk.chkNullString(request.getParameter("__submitdate"));
String submitdate2 = chk.chkNullString(request.getParameter("__submitdate2"));
String sourcejob = chk.chkNullString(request.getParameter("__sourcejob"));
String cmd = chk.chkNullString(request.getParameter("__cmd"));
String isprocess =chk.chkNullString(request.getParameter("__isprocess"));
String filter = "1=1";
String filter2 = "1=1";
if(!sourcejob.equals("")){
if(sourcejob.equals("all")){
}
else{
filter += " and sourcejobid ='"+sourcejob+"'";
}
}
if(!submitdate.equals("") && !submitdate2.equals("")){
filter2 += " and applicantdate between '"+c.setCscCalendar(submitdate).getYYYYMMDD()+"' and '"+c.setCscCalendar(submitdate2).getYYYYMMDD()+"' ";
}
DbRecord dbrec = new InitialRecord(up).getDbRecord("memployee");
dbrec.setColumn("*");
%>
<html ng-app="myHR">
<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.css" rel="stylesheet">
<script language="JavaScript" src="../JS/SCREEN.js"></script>
<SCRIPT TYPE='text/JavaScript' SRC='../JS/DATEINPUT.js'></SCRIPT>
<SCRIPT TYPE='text/JavaScript' SRC='../JS/VIEWCALENDAR.js'></SCRIPT>
<script type="text/javascript" src="../JS/jquery-2.1.3.min.js"></script>
<script src="../JS/bootstrap.min.js"></script>
<script type="text/javascript" src="../JS/angular-1.3.15/angular.js"></script>
<script type="text/javascript" src="../JS/ANGULAR/SCREEN_ANGULAR.js"></script>
<script language="javascript">setLang('<%=up.get("lang")%>');</script>
<script language="JavaScript" type="text/JavaScript">
function GoExport(){
with(document.cscform){
// alert(__filter.value);
var param = "RRC029_EXP.jsp?__filter="+__filter.value +"&__filter2="+__filter2.value;
window.open(param);
}
}
function GoProcess(){
with( document.cscform){
__cmd.value = "search"
__isprocess.value = "1"
submit();
}
}
function goHelp(helpName,inputName){
var helpReturn;
var fixcon;
if(inputName=="__job"){
helpReturn = inputName+":jobcodeid,"+inputName+"_desc:tdesc";
}
fixcon="";
linkHelp_Return(helpName,helpReturn,fixcon);
}
$(function() {
$('#export').click(function() {
GoExport();
});
$('#search').click(function() {
GoProcess();
});
});
</script>
<style>
img[height="20"] {
padding: 0px;
}
.center{
text-align: center;
}
.wd-40{
width:40%;
}
</style>
</head>
<%
String helpReturn="";
if(request.getParameter("__helpReturn")!=null){
helpReturn=request.getParameter("__helpReturn");
}
String fieldSearch="";
if(request.getParameter("__fieldSearch")!=null){
fieldSearch=request.getParameter("__fieldSearch");
}
%>
<body leftmargin="0" topmargin="0">
<form name="cscform" method="post" action="RRC029.jsp">
<!----------BEGIN CSC INITIAL ZONE ------------------------>
<%/*=screen.InitialVariable()*/%>
<!----------END CSC INITIAL ZONE -------------------------->
<input type="hidden" name="__cmd" value="">
<input type="hidden" name="__screen" value="RRC029">
<input type="hidden" name="__help" value="">
<input type="hidden" name="__goPage" value="">
<input type="hidden" name="__language" value="<%=up.get("lang")%>">
<input type="hidden" name="__orderBy" value="<%=request.getParameter("__orderBy")%>">
<input type="hidden" name="__helpReturn" value="<%=helpReturn%>">
<input type="hidden" name="__fieldSearch" value="<%=fieldSearch%>">
<input type="hidden" name="__helpName" value="<%=request.getParameter("__helpName")%>">
<input type="hidden" name="__pageCall" value="<%=request.getParameter("__screen")%>">
<input type="hidden" name="__fixCon" value="<%=request.getParameter("__fixCon")%>">
<input type="hidden" name="__export" value="">
<input type="hidden" name="__isprocess" value="0">
<input type="hidden" name="__filter" value="<%=filter%>">
<input type="hidden" name="__filter2" value="<%=filter2%>">
<input type="hidden" name="__calendar" value="">
<script language="javascript">getInputFormatDate()</script>
<br>
<div align="center">
<table class="large" cellpadding="0" cellspacing="1" >
<tr>
<td colspan=2 class="header">RRC029, รายงานสรุปผลการสมัครงาน</td>
</tr>
<tr>
<td colspan=2>
<table border="0" cellpadding="0" cellspacing="1" align="center" class="largest">
<tr>
<td colspan=2 class="blankspace"></td>
</tr>
<tr>
<td width="40%" align="right"><%=lang.equalsIgnoreCase("tha")?"วันที่":"Date"%></td>
<td style="text-align: left;">
<input type="text" id="submitdate" name="__submitdate" value="<%=submitdate%>" size="15" maxlength="10" onkeypress="chkIntegerOfDate(this.value,event);" onkeyup="sysIsEdit();formatDate(this);" onblur="chkInputDate(this);" onfocus="this.select();">
<a href="javascript:;" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('Image1','','../IMAGES/BUTTON/SWAP/CALENDAR_SWAP20.gif',1)" onClick="viewCal('submitdate')"><img src="../IMAGES/BUTTON/MAIN/CALENDAR20.gif" alt="Calendar" name="Image1" width="20" height="20" border="0" style="vertical-align: middle"></a>
-
<input type="text" id="submitdate2" name="__submitdate2" value="<%=submitdate2%>" size="15" maxlength="10" onkeypress="chkIntegerOfDate(this.value,event);" onkeyup="sysIsEdit();formatDate(this);" onblur="chkInputDate(this);" onfocus="this.select();">
<a href="javascript:;" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('Image2','','../IMAGES/BUTTON/SWAP/CALENDAR_SWAP20.gif',1)" onClick="viewCal('submitdate2')"><img src="../IMAGES/BUTTON/MAIN/CALENDAR20.gif" alt="Calendar" name="Image2" width="20" height="20" border="0" style="vertical-align: middle"></a>
</td>
</tr>
<tr>
<td class='wd-40' align="right" >Channal </td>
<td>
<select name='__sourcejob'>
<%
DbInquiry select_sourcejob = new InitialInquiry(up).getDbInquiry("msourcejob");
select_sourcejob.refresh();
%>
<option value='all' <%=sourcejob.equals("all")?"selected":""%>>ALL</option>
<%while(select_sourcejob.next()){ %>
<option value='<%=select_sourcejob.getString("msourcejob","sourcejobid")%>' <%=sourcejob.equals(select_sourcejob.getString("msourcejob","sourcejobid"))?"selected":""%> ><%=select_sourcejob.getString("msourcejob","tdesc")%></option>
<% } %>
</select>
</tr>
<tr>
<td colspan=2 class="blankspace"></td>
</tr>
<tr>
<td colspan="2" style="text-align: center;">
<div id="search" class="btn btn-primary btn-sm" style="padding-top: 2px;">
Search
</div>
<%
if(isprocess.equals("1")){
%>
<div id="export" class="btn btn-primary btn-sm" style="padding-top: 2px;">
Export
</div>
<%
}
%>
</td>
</tr>
</table>
<%
if(isprocess.equals("1")){
%>
<table class="maxsize table table-bordered " border="1" cellpadding="0" cellspacing="1" cols=10 bgcolor="#CCCCCC">
</tr>
<td class="row1 center" colspan='7'><%=lang.equalsIgnoreCase("tha")?"รายงานสรุปการสรรหาพนักงานแบ่งตามแหล่งที่มา":"Recruiting Analysis by Channel"%></td>
</tr>
<tr>
<td class="row1 center"><%=lang.equalsIgnoreCase("tha")?"แหล่งที่มา":"Channel"%></td>
<td class="row1 center"><%=lang.equalsIgnoreCase("tha")?"ผู้สมัครที่ถูกคัดเลือก":"Shortlisted"%></td>
<td class="row1 center"><%=lang.equalsIgnoreCase("tha")?"ผู้สมัครที่รับข้อเสนอ":"Offer Accepted"%></td>
<td class="row1 center"><%=lang.equalsIgnoreCase("tha")?"บัญชีดำ":"Blacklist"%></td>
<td class="row1 center"><%=lang.equalsIgnoreCase("tha")?"ตำแหน่งที่ได้รับความนิยมอันดับ 1":"Position Name Ranking No.1"%></td>
<td class="row1 center"><%=lang.equalsIgnoreCase("tha")?"ตำแหน่งที่ได้รับความนิยมอันดับ 2":"Position Name Ranking No.2"%></td>
<td class="row1 center"><%=lang.equalsIgnoreCase("tha")?"ตำแหน่งที่ได้รับความนิยมอันดับ 3":"Position Name Ranking No.3"%></td>
</tr>
<%
// >>>>>>>>>>>> Current Zone <<<<<<<<<<<<
DecimalFormat DFO = new DecimalFormat("#,###.##");
DbInquiry inqcan = new InitialInquiry(up).getDbInquiry("Simmcandidatewithgson");
DbInquiry inqpre = new InitialInquiry(up).getDbInquiry("mprefix");
DbInquiry inqrequest = new InitialInquiry(up).getDbInquiry("mrequest");
DbInquiry inqjob = new InitialInquiry(up).getDbInquiry("mjobcode");
DbInquiry inqsourcejob = new InitialInquiry(up).getDbInquiry("msourcejob");
DbInquiry inqstat = new InitialInquiry(up).getDbInquiry("mstatus");
DbInquiry inqcandidate = new InitialInquiry(up).getDbInquiry("mcandidate");
DbInquiry inqcandidate_his = new InitialInquiry(up).getDbInquiry("mapplicant_his");
inqcan.setColumn("*");
inqcan.putChild(inqjob);
inqcan.putChild(inqcandidate);
inqcan.putChild(inqrequest);
inqcan.putChild(inqsourcejob);
inqcan.putChild(inqstat);
DbInquiry inqapphis = new InitialInquiry(up).getDbInquiry("Simmapplicanthisotherwithgson");
DbInquiry inqemp2 = new InitialInquiry(up).getDbInquiry("memployee");
inqemp2.setColumn("*");
// inqcan.putChild(inqcandidate_his);
inqapphis.putChild(inqjob);
inqapphis.putChild(inqemp2);
inqapphis.putChild(inqstat);
inqapphis.putChild(inqsourcejob);
inqapphis.setColumn("*");
String sim3="";
String sim4="";
//out.print(filter);
inqsourcejob.setFilter(filter);
inqsourcejob.refresh();
int recCount = inqsourcejob.recCount();
int arr0 = 0;
int arr1 = 0;
int numall = 0;
int numBlacklist=0,hire=0,pass=0,other = 0;
int[] Ranking1 = new int[3];
int alldata = 0;
int alldatahis = 0;
MyHashMap TOP1 = new MyHashMap();
String[][] Loopdata = new String[recCount][1000];
// inqcandidate.setFilter("filter2");
inqcandidate.refresh();
while(inqsourcejob.next()){
inqcan.setFilter(filter2);
inqcan.refresh();
inqapphis.setFilter(filter2);
inqapphis.refresh();
while(inqcan.next()){
Simmcandidatewithgson simmcandidatewithgson = (Simmcandidatewithgson) inqcan.getCurrentDbRecord();
if(simmcandidatewithgson.mcandidateOther.sourcejob.equals(inqsourcejob.getString("msourcejob","sourcejobid"))){
numall++;
if(inqcan.getString("status").equals("14")){
numBlacklist++;
}else if(inqcan.getString("status").equals("8") || inqcan.getString("status").equals("9")){
hire++;
}else if(inqcan.getString("status").equals("1") || inqcan.getString("status").equals("2") || inqcan.getString("status").equals("3") ||
inqcan.getString("status").equals("4") || inqcan.getString("status").equals("5") || inqcan.getString("status").equals("6") ||
inqcan.getString("status").equals("7") || inqcan.getString("status").equals("10") || inqcan.getString("status").equals("11") ||
inqcan.getString("status").equals("12") || inqcan.getString("status").equals("13") ){
pass++;
}else {
other++;
}
arr0++;
}
alldata = numall;
//fix error data/0
if(alldata == 0){
alldata =1;
}
if(String.valueOf(arr1+1).equals(simmcandidatewithgson.mcandidateOther.sourcejob)){
Loopdata[arr1][3] = DFO.format(Double.valueOf(numBlacklist * 100/alldata )).toString()+"%";
Loopdata[arr1][2] = DFO.format(Double.valueOf(hire * 100/alldata )).toString()+"%";
Loopdata[arr1][1] = DFO.format(Double.valueOf(pass * 100/alldata )).toString()+"%";
TOP1.putToSum(inqcan.getString("mjobcode","jobcodeid"),1);
}
}
//HISTORY from inqcandidate_his
inqapphis.refresh();
while(inqapphis.next()){
Simmapplicanthisotherwithgson simmapplicanthisotherwithgson = (Simmapplicanthisotherwithgson) inqapphis.getCurrentDbRecord();
if(simmapplicanthisotherwithgson.mapplicanthisother.sourcejob.equals(inqsourcejob.getString("msourcejob","sourcejobid"))){
alldatahis++;
if(inqapphis.getString("status").equals("14")){
numBlacklist++;
}else if(inqapphis.getString("status").equals("8") || inqapphis.getString("status").equals("9")){
hire++;
}else if(inqapphis.getString("status").equals("1") || inqapphis.getString("status").equals("2") || inqapphis.getString("status").equals("3") ||
inqapphis.getString("status").equals("4") || inqapphis.getString("status").equals("5") || inqapphis.getString("status").equals("6") ||
inqapphis.getString("status").equals("7") || inqapphis.getString("status").equals("10") || inqapphis.getString("status").equals("11") ||
inqapphis.getString("status").equals("12") || inqapphis.getString("status").equals("13") ){
pass++;
}else {
other++;
}
arr0++;
}
alldata = numall + alldatahis; // summary
//fix error data/0
if(alldata == 0){
alldata =1;
}
if(String.valueOf(arr1+1).equals(simmapplicanthisotherwithgson.mapplicanthisother.sourcejob)){
Loopdata[arr1][3] = DFO.format(Double.valueOf(numBlacklist * 100/alldata )).toString()+"%";
Loopdata[arr1][2] = DFO.format(Double.valueOf(hire * 100/alldata )).toString()+"%";
Loopdata[arr1][1] = DFO.format(Double.valueOf(pass * 100/alldata )).toString()+"%";
TOP1.putToSum(inqcan.getString("mjobcode","jobcodeid"),1);
}
}
String sql_sourcejob = inqsourcejob.getString("msourcejob","sourcejobid");
String sql_count = "select count(*) as count from mcandidate";
String dbName = up.getDbName();
String dbtype = up.getDbType();
String sql = " WITH query1 AS (SELECT COUNT(MCAN.JOBID) AS COUNT,MCAN.JOBID,JOB.TDESC "+
" FROM MCANDIDATE MCAN "+
" INNER JOIN MJOBCODE JOB ON JOB.JOBCODEID = MCAN.JOBID "+
" WHERE "+filter2+" AND OTHER LIKE '%\"sourcejob\":\""+sql_sourcejob+"\"%' "+
" GROUP BY JOBID,JOB.TDESC "+
" UNION ALL "+
" SELECT COUNT(MHIS.JOBID) AS COUNT,MHIS.JOBID,JOB.TDESC "+
" FROM MAPPLICANT_HIS MHIS "+
" INNER JOIN MJOBCODE JOB ON JOB.JOBCODEID = MHIS.JOBID "+
" WHERE "+filter2+" AND OTHER LIKE '%\"sourcejob\":\""+sql_sourcejob+"\"%' "+
" GROUP BY JOBID,JOB.TDESC )"+
" SELECT sum(count) as count2,jobid,tdesc "+
" from query1 " +
" GROUP BY count,jobid,tdesc "+
" ORDER BY count2 desc,jobid ";
int old0 = 0, new0 = 0;
String old1 = "",new1 = "";
String tableName = "MCANDIDATE&MAPPLICANT_HIS";
DataSet dataset1 = new DataSet(sql_count, dbName, tableName ); //หาจำนวนบรรทัด
dataset1.initConnection(up);
dataset1.next();
String index1 = chk.chkNullString(dataset1.getString("jobid"));
dataset1.closeConnection();
dataset1.setSql(sql); // หา Record
dataset1.initConnection(up);
int loop = 4; // setArray[][4]
int addloop = 0; // Set if oldCount==newCount to same column
int countdataset = 0;
while(dataset1.next()){
if(addloop == 0){
new0 = Integer.valueOf(dataset1.getString("count2"));
new1 = dataset1.getString("tdesc");
Loopdata[arr1][loop+addloop] = new1;
}
else{
old0 = new0;
old1 = new1;
new0 = Integer.valueOf(dataset1.getString("count2"));
new1 = dataset1.getString("tdesc");
if(old0 == new0){ // if oldCount == newCount get to same column
addloop--;
Loopdata[arr1][loop+addloop] +=", " + dataset1.getString("tdesc");
}else{
//if blank :some no tdesc or jobcodeid
Loopdata[arr1][loop+addloop] = dataset1.getString("tdesc");
}
}
addloop++;
countdataset++;
}
dataset1.closeConnection();
Loopdata[arr1][0] = inqsourcejob.getString("msourcejob","tdesc");
%>
<tr>
<td class='center'><%=chk.chkNullString(Loopdata[arr1][0])%></td>
<td class='center'><%=chk.chkNullString(Loopdata[arr1][1],"0%")%></td>
<td class='center'><%=chk.chkNullString(Loopdata[arr1][2],"0%")%></td>
<td class='center'><%=chk.chkNullString(Loopdata[arr1][3],"0%")%></td>
<td class='center'><%=chk.chkNullString(Loopdata[arr1][4],"-")%></td>
<td class='center'><%=chk.chkNullString(Loopdata[arr1][5],"-")%></td>
<td class='center'><%=chk.chkNullString(Loopdata[arr1][6],"-")%></td>
</tr>
<%
numall=0;
arr1++;
numBlacklist=0;
hire=0;
pass=0;
arr0=0;
alldata=0;
alldatahis=0;
TOP1.clear();// To Check Data in Table
}
%>
</table>
<%
}
%>
</td>
</tr>
<tr>
<td colspan=2 class="blankspace"></td>
</tr>
<tr>
<td colspan=2 class="blankspace"></td>
</tr>
<tr bordercolor="0069B3">
<td colspan=2 class="bottom"></td>
</tr>
</table>
</div>
</form>
</body>
</html>