<%    /**
     * **********************************************************
     * @@ Program Name  : statistics_excel.jsp 
     * @@ Description	: /집계자/보고문서 목록 공통 
     * @@ Author	: 기현테크 
     * @@ Create Date	: 2010.11.21 
     * @@ History	: 2014.10.14 Update by jskim
     * @@***********************************************************
     */
%>
<%@ page contentType="text/html; charset=euc-kr"
         import="java.util.Date"
         import="java.util.List"
         import="java.text.SimpleDateFormat"
         import="java.net.URLEncoder"
         import="java.net.URLDecoder"
         import="kr.co.kihyun.beans.user.HttpSSOLogin"
         import="kr.co.kihyun.util.MDate"
         import="kr.co.kihyun.lang.MString"
         import="kr.co.kihyun.lang.MInteger"
         import="kr.co.kihyun.lang.MLong"
         import="kr.co.kihyun.lang.Encoder"
         import="kr.co.kihyun.moumi.MoumiConfig"
         import="kr.co.kihyun.moumi.doc.MDoc"
         import="kr.co.kihyun.moumi.report.MReport"
         import="kr.co.kihyun.beans.user.User"
         import="kr.co.kihyun.beans.user.Dept"
         import="kr.co.kihyun.beans.totsys.report.Report"
         import="kr.co.kihyun.beans.totsys.doc.Doc"
         import="javax.jdo.PersistenceManager"
         import="javax.jdo.Transaction"
         import="kr.co.kihyun.beans.entity.util.*"
         import="kr.co.kihyun.beans.entity.*"
         import="org.slf4j.Logger"
         import="org.slf4j.LoggerFactory"
         import="kr.co.kihyun.service.*" import="kr.co.kihyun.service.vo.*"
         import="java.util.*" import="java.text.*"
         import="javax.jdo.Query"%>
<%@ include file="/totsys/common/inc/sec/secure.inc.jsp"%>
<%    PersistenceManager pm = new MPersistenceManager(PMF.get().getPersistenceManager());
    Transaction tx = pm.currentTransaction();
    try {
        Logger LOG = LoggerFactory.getLogger(this.getClass());
        /**
         * ******** session내의 userId *********
         */
        String usID = HttpSSOLogin.getLoginID(request);
        int sysAuth = HttpSSOLogin.getSysAuth(request);
        String dpID = HttpSSOLogin.getDeptID(request);

        /**
         * **** Parameter *****
         */
        String searchType = clearXSS(request.getParameter("searchType"), "");
        String dateType = clearXSS(request.getParameter("dateType"), "");
        String stDate = clearXSS(request.getParameter("stDate"), "");
        String edDate = clearXSS(request.getParameter("edDate"), "");
        String searchOrgan = clearXSS(request.getParameter("search_organ"), "");
        String searchDept = clearXSS(request.getParameter("search_dept"), "");
        String chkboxDept = clearXSS(request.getParameter("chkbox_dept"), "");
        String chkboxSchool = clearXSS(request.getParameter("chkbox_school"), "");

        tx.begin();

        String deptName = "";
        if (searchOrgan.equals("ALL")) {
            MDept mDept = pm.getObjectById(MDept.class, dpID);
            deptName = mDept.getRootDept(pm).getName();
        } else {
            if (searchDept.equals("ALL")) {
                MDept mDept = pm.getObjectById(MDept.class, searchOrgan);
                deptName = mDept.getName();
            } else {
                if (chkboxDept.equals("D")) {
                    MDept mDept = pm.getObjectById(MDept.class, searchOrgan);
                    deptName = mDept.getName() + " - ";
                }
                MDept mDept = pm.getObjectById(MDept.class, searchDept);
                deptName += mDept.getName();
            }
        }
        //===========================================================
        String txtStartDate = stDate;
        String txtEndDate = edDate;

        String sDeptSchoolType = MString.checkNull(chkboxDept, chkboxSchool);

        StatisticsService service = new StatisticsService(pm);
        List list = null;
        Map params = new HashMap();
        params.put("organId", searchOrgan);
        params.put("deptId", searchDept);
        params.put("deptSchoolType", sDeptSchoolType);
        if (searchType.equals("all")) {
            params.put("startDate", stDate);
            params.put("endDate", edDate);

            list = service.getStatisticsForAll(params);
        } else if (searchType.equals("period")) {
            params.put("dateType", dateType);
            params.put("stDate", stDate);
            params.put("edDate", edDate);
            list = service.getStatisticsByDate(params);
            if (dateType.equals("month")) {
                txtStartDate = stDate.substring(0, stDate.lastIndexOf("-"));
                txtEndDate = edDate.substring(0, stDate.lastIndexOf("-"));
            } else if (dateType.equals("year")) {
                txtStartDate = stDate.substring(0, stDate.indexOf("-"));
                txtEndDate = edDate.substring(0, stDate.indexOf("-"));
            }
        }

        tx.rollback();

        DecimalFormat numberFormat = new DecimalFormat("#,###,###");

        response.setHeader("Accept-Ranges", "bytes");
                //response.setHeader("Content-Disposition","attachment; filename="+(URLEncoder.encode(docName, "UTF-8")).replaceAll(":","_").replace("+","%20" )+".xls;");
        //LJH

                //v2. 11.HTTP 응답분할 : Update by KWON,HAN
        //response.setHeader("Content-Disposition", "attachment; filename=statistics_"+searchType+".xls;");
        //수정 : 외부 입력값 필터링
        String filtered_searchType = searchType.replaceAll("\r", "").replaceAll("\n", "");
        response.setHeader("Content-Disposition", "attachment; filename=statistics_" + filtered_searchType + ".xls;");
        //=================================================================================================
%>
<html xmlns:o="urn:schemas-microsoft-com:office:office"
      xmlns:x="urn:schemas-microsoft-com:office:excel"
      xmlns="http://www.w3.org/TR/REC-html40">
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=euc-kr">
            <!--<meta http-equiv="Content-Type" content="text/html; charset=ks_c_5601-1987">-->
            <meta name="ProgId" content="Excel.Sheet">
                <meta name="Generator" content="Microsoft Excel 10">
                    </head>
                    <body>
                        <!-------------------------------------엑셀시작------------------------------------------------->
                        <% if (searchType.equals("") || searchType.equals("period")) {%>
                        <!------------------------------기간별 조회 시작----------------------------------->
                        <table width="100%" border="1" cellpadding="0" cellspacing="0">
                            <tr>
                                <td height="50" valign="bottom"><!----  목록 ---->
                                    <table width="100%" border="0" cellpadding="0" cellspacing="0">
                                        <tr>
                                            <td height="30" valign="bottom" colspan="6" align="middle">
                                                <font style=" font-size: 20px; font-weight: bold;"><%= deptName%></font>
                                            </td>
                                        </tr>
                                        <tr>
                                            <td height="20" valign="bottom" colspan="6" align="right">
                                                <font style="font-weight: bold;"><%= txtStartDate%> ~ <%= txtEndDate%></font>
                                            </td>
                                        </tr>
                                    </table>
                                </td>
                            </tr>
                            <tr>
                                <td>
                                    <table border="1" width="100%" cellpadding="0" cellspacing="0">
                                        <tr style="padding-top: 3">
                                            <!--<td align="center" valign="middle" class="kecttep_tdmenu" rowspan="2">선택</td>-->
                                            <td width="40" align="center" valign="middle" class="kecttep_tdmenu" style="font-weight: bold"><%=MoumiConfig.getMessageBundle().getString("moumi.message.tot_doc.day")%> <%=MoumiConfig.getMessageBundle().getString("moumi.message.tot_doc.person")%></td>
                                            <td align="center" valign="middle" nowrap class="kecttep_tdmenu" style="font-weight: bold"><%=MoumiConfig.getMessageBundle().getString("moumi.message.statistics.tot_doc")%> <%=MoumiConfig.getMessageBundle().getString("moumi.message.statistics.temp")%></td>
                                            <td align="center" valign="middle" nowrap class="kecttep_tdmenu" style="font-weight: bold"><%=MoumiConfig.getMessageBundle().getString("moumi.message.statistics.tot_doc")%> <%=MoumiConfig.getMessageBundle().getString("moumi.message.statistics.complete")%></td>
                                            <td align="center" valign="middle" nowrap class="kecttep_tdmenu" style="font-weight: bold"><%=MoumiConfig.getMessageBundle().getString("moumi.message.statistics.accept")%></td>
                                            <td width="90" align="center" valign="middle" nowrap class="kecttep_tdmenu" style="font-weight: bold"><%=MoumiConfig.getMessageBundle().getString("moumi.message.statistics.tot_report")%> <%=MoumiConfig.getMessageBundle().getString("moumi.message.statistics.temp")%></td>
                                            <td align="center" valign="middle" nowrap class="kecttep_last_tdmenu" style="font-weight: bold"><%=MoumiConfig.getMessageBundle().getString("moumi.message.statistics.tot_report")%> <%=MoumiConfig.getMessageBundle().getString("moumi.message.statistics.complete")%></td>
                                        </tr>
                                        <%
                                            int prssTot = 0;        //집계진행합계
                                            int endTot = 0;         //집계완료합계
                                            int acceptTot = 0;      //접수합계
                                            int tempTot = 0;        //제출진행합계    
                                            int compTot = 0;        //제출완료합계
                                            for (int i = 0; i < list.size(); i++) {
                                                Object[] row = (Object[]) list.get(i);
                                                prssTot += Integer.parseInt(row[1].toString());
                                                endTot += Integer.parseInt(row[2].toString());
                                                acceptTot += Integer.parseInt(row[3].toString());
                                                tempTot += Integer.parseInt(row[4].toString());
                                                compTot += Integer.parseInt(row[5].toString());
                                        %>
                                        <tr style="padding-top: 3px" valign="middle" onmouseover="this.style.backgroundColor = 'F9F9F9'" onmouseout="this.style.backgroundColor = 'white'">
                                            <td align="center" nowrap  class="kecttep_graytd" style="text-align: center;"><%= row[0]%></td>
                                            <td align="center" nowrap class="kecttep_graytd"><%= numberFormat.format(row[1])%></td>
                                            <td align="center" class="kecttep_graytd"><%= numberFormat.format(row[2])%></td>
                                            <td align="center" class="kecttep_graytd"><%= numberFormat.format(row[3])%></td>
                                            <td align="center" nowrap class="kecttep_graytd"><%= numberFormat.format(row[4])%></td>
                                            <td align="center" nowrap class="kecttep_last_graytd"><%= numberFormat.format(row[5])%></td>
                                        </tr>
                                        <%
                                            }
                                        %>
                                        <tr style="padding-top: 3px" valign="middle" onmouseover="this.style.backgroundColor = 'F9F9F9'" onmouseout="this.style.backgroundColor = 'white'">
                                            <td align="center" nowrap  class="kecttep_tdmenu" style="text-align: center; font-weight: bold;"><%=MoumiConfig.getMessageBundle().getString("moumi.message.total")%></td>
                                            <td align="center" nowrap class="kecttep_tdmenu"><%= numberFormat.format(prssTot)%></td>
                                            <td align="center" class="kecttep_tdmenu"><%= numberFormat.format(endTot)%></td>
                                            <td align="center" class="kecttep_tdmenu"><%= numberFormat.format(acceptTot)%></td>
                                            <td align="center" nowrap class="kecttep_tdmenu"><%= numberFormat.format(tempTot)%></td>
                                            <td align="center" nowrap class="kecttep_last_tdmenu"><%= numberFormat.format(compTot)%></td>
                                        </tr>
                                    </table>
                                </td>
                            </tr>
                        </table>
                        <!------------------------------기간별 조회 끝----------------------------------->
                        <%
                        } else if (searchType.equals("all")) {
                            Object[] row = (Object[]) list.get(0);
                        %>
                        <!------------------------------전체 조회 시작----------------------------------->
                        <table width="400" border="1" cellpadding="0" cellspacing="0">
                            <tr>
                                <td height="50" valign="bottom"><!----  목록 ---->
                                    <table width="400" border="0" cellpadding="0" cellspacing="0">
                                        <tr>
                                            <td height="30" valign="bottom" colspan="1" align="middle">
                                                <font style=" font-size: 20px; font-weight: bold;"><%= deptName%></font>
                                            </td>
                                        </tr>
                                        <tr>
                                            <td height="20" valign="bottom" colspan="1" align="right">
                                                <font style="font-weight: bold;"><%= txtStartDate%> ~ <%= txtEndDate%></font>
                                            </td>
                                        </tr>
                                    </table>
                                </td>
                            </tr>
                            <tr>
                                <td>
                                    <table width="400" border="1" cellspacing="0" cellpadding="0">
                                        <tr>
                                            <td>
                                                <table border="1" width="400" cellpadding="0" cellspacing="0">
                                                    <tr style="padding-top: 3px" valign="middle" onmouseover="this.style.backgroundColor = 'F9F9F9'" onmouseout="this.style.backgroundColor = 'white'">
                                                        <td colspan="2">
                                                            <%=MoumiConfig.getMessageBundle().getString("moumi.message.statistics.tot_doc")%>
                                                        </td>
                                                    </tr>
                                                    <tr style="padding-top: 3px" valign="middle" onmouseover="this.style.backgroundColor = 'F9F9F9'" onmouseout="this.style.backgroundColor = 'white'">
                                                        <td width="100" align="center" nowrap class="kecttep_tdmenu" style="font-weight: bold">
                                                            <%=MoumiConfig.getMessageBundle().getString("moumi.message.statistics.tot_doc")%> <%=MoumiConfig.getMessageBundle().getString("moumi.message.statistics.temp")%>
                                                        </td>
                                                        <td width="300" align="left" nowrap class="kecttep_graytd" style="font-weight: bold;padding-left: 20px;vertical-align: middle">
                                                            <%= numberFormat.format(row[0]).trim()%> <%=MoumiConfig.getMessageBundle().getString("moumi.message.statistics.job")%></span>
                                                        </td>
                                                    </tr>    
                                                    <tr style="padding-top: 3px" valign="middle" onmouseover="this.style.backgroundColor = 'F9F9F9'" onmouseout="this.style.backgroundColor = 'white'">
                                                        <td width="100" align="center" nowrap class="kecttep_tdmenu" style="font-weight: bold">
                                                            <%=MoumiConfig.getMessageBundle().getString("moumi.message.statistics.tot_doc")%> <%=MoumiConfig.getMessageBundle().getString("moumi.message.statistics.complete")%>
                                                        </td>
                                                        <td width="300" align="left" nowrap class="kecttep_graytd" style="font-weight: bold;padding-left: 20px;vertical-align: middle">
                                                            <%= numberFormat.format(row[1]).trim()%> <%=MoumiConfig.getMessageBundle().getString("moumi.message.statistics.job")%>
                                                        </td>
                                                    </tr>
                                                    <tr style="padding-top: 3px" valign="middle" onmouseover="this.style.backgroundColor = 'F9F9F9'" onmouseout="this.style.backgroundColor = 'white'">
                                                        <td colspan="2">
                                                            <%=MoumiConfig.getMessageBundle().getString("moumi.message.statistics.tot_report")%>
                                                        </td>
                                                    </tr>
                                                    <tr style="padding-top: 3px" valign="middle" onmouseover="this.style.backgroundColor = 'F9F9F9'" onmouseout="this.style.backgroundColor = 'white'">
                                                        <td width="100" align="center" nowrap class="kecttep_tdmenu" style="font-weight: bold">
                                                            <%=MoumiConfig.getMessageBundle().getString("moumi.message.statistics.accept")%>
                                                        </td>
                                                        <td width="300" align="left" nowrap class="kecttep_graytd" style="font-weight: bold;padding-left: 20px;vertical-align: middle">
                                                            <%= numberFormat.format(row[2]).trim()%> <%=MoumiConfig.getMessageBundle().getString("moumi.message.statistics.job")%>
                                                        </td>
                                                    </tr>
                                                    <tr style="padding-top: 3px" valign="middle" onmouseover="this.style.backgroundColor = 'F9F9F9'" onmouseout="this.style.backgroundColor = 'white'">
                                                        <td width="100" align="center" nowrap class="kecttep_tdmenu" style="font-weight: bold">
                                                            <%=MoumiConfig.getMessageBundle().getString("moumi.message.statistics.tot_report")%> <%=MoumiConfig.getMessageBundle().getString("moumi.message.statistics.temp")%>
                                                        </td>
                                                        <td width="300" align="left" nowrap class="kecttep_graytd" style="font-weight: bold;padding-left: 20px;vertical-align: middle">
                                                            <%= numberFormat.format(row[3]).trim()%> <%=MoumiConfig.getMessageBundle().getString("moumi.message.statistics.job")%>
                                                        </td>
                                                    </tr>
                                                    <tr style="padding-top: 3px" valign="middle" onmouseover="this.style.backgroundColor = 'F9F9F9'" onmouseout="this.style.backgroundColor = 'white'">
                                                        <td width="100" align="center" nowrap class="kecttep_tdmenu" style="font-weight: bold">
                                                            <%=MoumiConfig.getMessageBundle().getString("moumi.message.statistics.tot_report")%> <%=MoumiConfig.getMessageBundle().getString("moumi.message.statistics.complete")%>
                                                        </td>
                                                        <td width="300" align="left" nowrap class="kecttep_graytd" style="font-weight: bold;padding-left: 20px;vertical-align: middle">
                                                            <%= numberFormat.format(row[4]).trim()%> <%=MoumiConfig.getMessageBundle().getString("moumi.message.statistics.job")%>
                                                        </td>
                                                    </tr>
                                                </table>
                                            </td>
                                        </tr>
                                    </table>
                                </td>
                            </tr>
                        </table>
                        <!------------------------------전체 조회 끝----------------------------------->
                        <% } %>
                        <!-------------------------------------엑셀끝------------------------------------------------->
                    </body>
                    <%
                        } catch (Exception ex) {
                            //ex.printStackTrace();
                            out.println(kr.co.kihyun.text.html.ServletUtil.getJavaScript("location='/servlet/kr.co.kihyun.beans.user.HttpSSOLogin?mode=logout';"));
                        } finally {
                            if (tx.isActive()) {
                                tx.rollback();
                            }
                            pm.close();
                        }
                    %>