<%@ page import="ro.emid.common.types.Category" %>
<%@ page import="ro.emid.common.db.CategoryAccessDB" %>
<%@ page import="ro.emid.common.db.framework.ReportStatement" %>
<%@ page import="ro.emid.common.utils.ObjectUtils" %>
<%@ page import="ro.emid.common.constants.RoleConstants" %>
<%@ page import="java.util.List" %>
<%@ page import="ro.emid.common.constants.GlobalAppConstants" %>
<%@ page import="java.util.Calendar" %>
<%@ page import="ro.emid.common.constants.InformatieConstants" %>
<%@ page import="java.util.Vector" %>
<%@ page import="java.sql.*" %>
<%@ page import="ro.emid.common.db.framework.AppConnManager" %>
<%@ page import="ro.emid.common.db.DeranjamentAccessDB" %>
<%@ page import="ro.emid.common.constants.AppConstants" %>

<%@ include file="../callcenter/declaratii.jsp"%>
<%
    final Calendar c = Calendar.getInstance();
    final int anSelectat = ObjectUtils.getIntValue(request.getParameter("an"), c.get(Calendar.YEAR));
    final int tip = ObjectUtils.getIntValue(request.getParameter("tip"), 1);

    final Connection con = AppConnManager.getInstance().getConnection();
    final Vector vani = new Vector();
    final PreparedStatement ps = con.prepareStatement("SELECT DISTINCT TO_CHAR(DATA, 'YYYY') FROM informatii ORDER BY TO_CHAR(DATA, 'YYYY') ASC");
    final ResultSet rs = ps.executeQuery();

    while (rs.next()) {
        vani.add(new Long(rs.getLong(1)));
    }

    rs.close();
    ps.close();
    con.close();

    String sql = "";
    String group = "";
    String titluRaport = "";
    String titluColoana = "";

    final StringBuffer buffer = new StringBuffer();
    buffer.append(" SELECT CASE WHEN ZONA = " ).append(InformatieConstants.ZONA_MS_BUCURESTI ).append( " THEN 'Bucuresti' " ).append(
            " WHEN ZONA = " ).append( InformatieConstants.ZONA_MS_ILFOV ).append( " THEN 'Ilfov' " ).append(
            " WHEN ZONA = " ).append( InformatieConstants.ZONA_MS_GIURGIU ).append( " THEN 'Giurgiu' " ).append(
            "ELSE '-' END AS textZona" ).append(
            ", ZONA, COUNT(*) cnt FROM informatii");


    switch( tip){
        case 1:
            sql = " SELECT MOTIV1, COUNT(*) cnt FROM informatii";
            group = " GROUP BY MOTIV1 ORDER BY cnt DESC, MOTIV1 ";
            titluRaport = "Raport Motive Solicitari informatii";
            titluColoana = "Motiv";
            break;
        case 2:
            sql = " SELECT NUME_CLIENT, COUNT(*) cnt FROM informatii";
            group = " GROUP BY NUME_CLIENT ORDER BY cnt DESC, NUME_CLIENT";
            titluRaport = "Raport Clienti Solicitari informatii";
            titluColoana = "Client";
            break;
        case 3:
            sql = buffer.toString();
            group = " GROUP BY ZONA ORDER BY cnt DESC, ZONA";
            titluRaport = "Raport Zona Solicitari informatii";
            titluColoana = "Zona";
            break;


    }

    final ReportStatement raportMotive = new ReportStatement(sql, group)
            .addOrSetFilter("TO_CHAR(DATA, 'YYYY')", "=", new Long(anSelectat));


    final ReportStatement totalCountLuni = new ReportStatement("SELECT TO_CHAR(DATA, 'MM'), COUNT(*) FROM informatii", "GROUP BY TO_CHAR(DATA, 'MM')")
            .addOrSetFilter("TO_CHAR(DATA, 'YYYY')", "=", new Long(anSelectat));


    final List matrix = totalCountLuni.getListOfArrays("Get Total informatii grupate pe Luni");

    long totalSolicitari = 0;
    final long[] totalLuni = new long[12];
    for (int k = 0; k < 12; k++) {
        totalLuni[k] = DeranjamentAccessDB.getLongByMonth(matrix, k + 1);
        totalSolicitari += totalLuni[k];
    }
%>

<TABLE border=0 cellPading=0 cellSpacing=0 width="100%">
    <TR>
        <TD>
            <SPAN style="FONT-SIZE: 12px; FONT-WEIGHT: bold">

                <A class="link" href="<%=GlobalAppConstants.APP_NAME%>?action=rapoarte">Rapoarte</A> - Raport Solicitari informatii
            </span>
        </TD>
    </TR>
</TABLE>

<table><tr><td>
    <center>
        <br />
        <span style="font-size: 14px"><%=titluRaport%></span>
    </center>
<div class="spacer"></div>
<table class="gsaptd" width="100%"><tr><td>
<form name="zaforma" action="<%=GlobalAppConstants.APP_NAME%>" method="post">
    <input type="hidden" name="action" value="raport-top10-motive-solicitari-informatii">


    Tip:
    <select name="tip">
        <option value="1" <%=tip == 1 ? "selected" : ""%> >Motiv solicitare</option>
        <option value="2" <%=tip == 2 ? "selected" : ""%> >Client</option>
        <option value="3" <%=tip == 3 ? "selected" : ""%> >Zona</option>
    </select>

    &nbsp;&nbsp;&nbsp;Anul:
    <select name="an">
        <% for (int i = 0; i < vani.size(); i++) {
            final Long an = (Long)vani.get( i);%>
            <option value="<%=an%>" <%=an.equals(new Long(anSelectat)) ? "selected" : ""%> ><%=an%></option>
        <% } %>
    </select>
    &nbsp;&nbsp;
    <input type="submit" value="Aplica">
</form>
</td></tr></table>
<div class="spacer"></div>

<TABLE id="table" frame="hsides" rules="groups" border=1 cellPadding=3 cellSpacing=1 width="100%" class="gSAPTHEAD" style="BACKGROUND-COLOR: white">
    <col>
    <tr noSelect="true">
        <th class="gsapthead"><%=titluColoana%></th>
        <th class="gsapthead">Ian</th>
        <th class="gsapthead">Feb</th>
        <th class="gsapthead">Mar</th>
        <th class="gsapthead">Apr</th>
        <th class="gsapthead">Mai</th>
        <th class="gsapthead">Iun</th>
        <th class="gsapthead">Iul</th>
        <th class="gsapthead">Aug</th>
        <th class="gsapthead">Sep</th>
        <th class="gsapthead">Oct</th>
        <th class="gsapthead">Noi</th>
        <th class="gsapthead">Dec</th>
        <th class="gsapthead">Total</th>
    </tr>
    <%
        final List rows = raportMotive.getListOfArrays("Get Motive Informatii");
        if( rows == null || rows.size() == 0){%>
        <tr noSelect="true">
            <td>Nu exista informatii...</td>
        </tr>
       <%} else {
            final long total = 0;

            for (int i = 0; i < rows.size(); i++) {
                final Object[] values = (Object[]) rows.get(i);
                long countLinie = 0;%>
                <tbody>
                <tr>
                    <%  String text = "";
                        if( tip == 1){
                            int columnIndex = 0;
                            final int motiv1 = ObjectUtils.getIntValue( values[ columnIndex++], -1);
                            countLinie = ObjectUtils.getLongValue( values[ columnIndex++], 0);

                            final String strMotiv1 = InformatieConstants.getMotiv1( motiv1);

                            totalCountLuni
                                    .addOrSetFilter("MOTIV1", "=", new Long( motiv1));

                            text = AppConstants.format(strMotiv1);
                        }else if( tip == 2){
                            countLinie = ObjectUtils.getLongValue( values[ 1], 0);
                            text = values[0] != null ? (String) values[0] : "";
                            totalCountLuni
                                    .addOrSetFilter("NUME_CLIENT", "=", text);

                        }else if ( tip == 3){
                            text = (String) values[0];

                            countLinie = ObjectUtils.getLongValue(values[2], 0);

                            final int zona = ObjectUtils.getIntValue( values[ 1], -1);

                            totalCountLuni
                                    .addOrSetFilter("ZONA", "=", new Integer( zona));

                        } else throw new Exception("Not Implemented Exception ");

                    %>
                    <td nowrap><%=text.trim().length() == 0 ? "Nespecificat" : text%></td>
                    <% final List matrix2 = totalCountLuni.getListOfArrays("Get Count Per Luni");%>
                    <% for(int k = 0; k < 12 ; k++) {
                        int pondere = 0;
                        long countLuna = 0;
                        if( totalLuni[k] > 0){
                            countLuna = DeranjamentAccessDB.getLongByMonth( matrix2, k+1);
                            pondere = (int)(countLuna * 100/totalLuni [k]);
                        }
                        %><td align="right" title="<%=countLuna%> solicitari din total <%=totalLuni[k]%> (luna <%=k+1%>)"><%=pondere%>%</td><%
                    }%>
                    <td align="right" title="<%=countLinie%> din <%=totalSolicitari%>"><%=(int)(countLinie*100/totalSolicitari)%>%</td>
                </tr>
                </tbody>
            <%} %>
        <%}%>
</TABLE>
</td></tr></table>

<script type="text/javascript">
    var table = new Table("table", "table");
</script>