<%@ page import="ro.emid.common.constants.AppConstants" %>
<%@ page import="ro.emid.common.db.AccessDB" %>
<%@ page import="ro.emid.common.db.framework.DatabaseException" %>
<%@ page import="ro.emid.common.db.framework.ReportStatement" %>
<%@ page import="ro.emid.common.utils.ObjectUtils" %>
<%@ page import="java.sql.SQLException" %>
<%@ page import="java.util.LinkedList" %>
<%@ page import="java.util.List" %>


<%
    final String action = request.getParameter("act");
    if( "saveCorelatie".equals( action)){
//        final long oldId = ObjectUtils.getLongValue( request.getParameter("oldId"), -1);
        final String []strOldId = request.getParameterValues("oldId");
        final long newId = ObjectUtils.getLongValue( request.getParameter("newId"), -1);
        if( strOldId != null && strOldId.length > 0 && newId > 0){
            for (int i = 0; i < strOldId.length; i++) {
                final long oldId = ObjectUtils.getLongValue( strOldId[i], -1);
                if ( oldId <= 0 ) continue;

                AccessDB.executeSQL(new AccessDB("Save corelatie categorie") {
                    public Object doWork() throws SQLException, DatabaseException {

                        createStatementFromString("DELETE FROM legaturi_categorii WHERE ID_CATEGORY_OLD = ? AND ID_CATEGORY_CURRENT = ?");
                        statement.setLong(1, oldId);
                        statement.setLong(2, newId);
                        statement.executeUpdate();

                        createStatementFromString("INSERT INTO legaturi_categorii( ID_CATEGORY_OLD, ID_CATEGORY_CURRENT) VALUES (?, ?) ");
                        statement.setLong(1, oldId);
                        statement.setLong(2, newId);
                        statement.executeUpdate();

                        return null;
                    }
                });
            }
        }
    }

    if( "deleteCorelatie".equals( action)){
        final long oldId = ObjectUtils.getLongValue( request.getParameter("oldId"), -1);
        final long newId = ObjectUtils.getLongValue( request.getParameter("newId"), -1);
        if( oldId > 0 && newId > 0){
            AccessDB.executeSQL(new AccessDB("Delete corelatie categorie") {
                public Object doWork() throws SQLException, DatabaseException {

                    createStatementFromString("DELETE FROM legaturi_categorii WHERE ID_CATEGORY_OLD = ? AND ID_CATEGORY_CURRENT = ?");
                    statement.setLong(1, oldId);
                    statement.setLong(2, newId);
                    statement.executeUpdate();

                    return null;
                }
            });
        }
    }

    List idCategoriiHidden = (List) session.getAttribute("idCategoriiHidden");

    if( "hideCategorie".equals(action)){
        final long idCategorie = ObjectUtils.getLongValue( request.getParameter("idCategorie"), -1);
        if( idCategorie > 0){
            if( idCategoriiHidden == null){
                idCategoriiHidden = new LinkedList();
                session.setAttribute("idCategoriiHidden", idCategoriiHidden);
            }
            idCategoriiHidden.add( new Long(idCategorie));
        }
    }

    if( "clearHidden".equals( action)){
        if( idCategoriiHidden != null){
            idCategoriiHidden.clear();
        }
    }


    final String statusHidden = idCategoriiHidden != null && idCategoriiHidden.size() > 0 ? idCategoriiHidden.size() + " hidden" : "";
%>

<%
    final ReportStatement rsCategoriiOldCorelate = new ReportStatement(
               " SELECT old.id, old.name, old.cod_sap, c.ID, c.name, c.cod_sap  "+
               " FROM categories_old old LEFT JOIN legaturi_categorii leg ON(old.ID = leg.ID_CATEGORY_OLD) " +
                                              " LEFT JOIN categories c ON (leg.ID_CATEGORY_CURRENT = c.ID) ",
              " ORDER BY ''||old.id"
       ).addStringCriteria(" AND c.ID > 0");
    final List listCategoriiCorelate = rsCategoriiOldCorelate.getListOfArrays("Get categorii old");



    
    final ReportStatement rsCategoriiOldNecorelate = new ReportStatement(
               " SELECT old.id, old.name, old.cod_sap "+
               " FROM categories_old old LEFT JOIN legaturi_categorii leg ON(old.ID = leg.ID_CATEGORY_OLD) " +
                                              " LEFT JOIN categories c ON (leg.ID_CATEGORY_CURRENT = c.ID) ",
              " ORDER BY ''||old.id"
       ).addStringCriteria(" AND c.ID IS NULL OR c.ID = 0");
    final List listCategoriiOldNecorelate = rsCategoriiOldNecorelate.getListOfArrays("Get categorii old Necorelate ");



    final ReportStatement rsCategoriiNewNecorelate = new ReportStatement(
               " SELECT c.id, c.name, c.cod_sap "+
               " FROM categories c LEFT JOIN legaturi_categorii leg ON(c.ID = leg.ID_CATEGORY_CURRENT) " +
                                              " LEFT JOIN categories_old old ON (leg.ID_CATEGORY_OLD = old.ID) ",
              " ORDER BY ''||c.id"
       ).addStringCriteria(" AND old.ID IS NULL OR c.ID = 0");
    final List listCategoriiNewNecorelate = rsCategoriiNewNecorelate.getListOfArrays("Get categorii new Necorelate ");
%>
<style type="text/css">
    .panel{
        overflow: auto;
        height: 200px;
    }
</style>

<b>Corelare categorii Incidente 2 si categorii Incidente 2.1  </b> <br>
<hr  />
<form name="zaforma" action="<%=AppConstants.APP_NAME%>?action=corelareCategorii" method="post">
<input type="hidden" name="act" value="saveCorelatie" />
<table width="100%">
    <tr>
        <td width="50%">
            <div class="panel">
                Categorii vechi:
                <table>
                    <% for (int i = 0; i < listCategoriiOldNecorelate.size(); i++) {
                        final Object[] arr = (Object[]) listCategoriiOldNecorelate.get(i);

                        final Long idCategorie = new Long( arr[0].toString());

                        if ( idCategoriiHidden != null && idCategoriiHidden.contains( idCategorie )) continue;
                    %>
                        <tr>
                            <td><input id="old<%=i%>"  type="checkbox" name="oldId" value="<%=arr[0]%>" /></td>
                            <td>
                                <label for="old<%=i%>" onmouseover="this.style.color = 'red';" onmouseout="this.style.color = '';">
                                    <%=arr[2]%> - <%=arr[1]%>
                                </label>
                            </td>
                            <td>
                                <a href="<%=AppConstants.APP_NAME%>?action=corelareCategorii&act=hideCategorie&idCategorie=<%=idCategorie%>">hide</a>
                            </td>
                        </tr>
                    <%}%>
                </table>
            </div>
        </td>
        <td width="50%"> 
            <div class="panel">
                Categorii noi:
                <table>
                    <% for (int i = 0; i < listCategoriiNewNecorelate.size(); i++) {
                        final Object[] arr = (Object[]) listCategoriiNewNecorelate.get(i);

                        final Long idCategorie = new Long( arr[0].toString());

                        if ( idCategoriiHidden != null && idCategoriiHidden.contains( idCategorie )) continue;
                    %>
                        <tr>
                            <td><input id="new<%=i%>" type="radio" name="newId" value="<%=arr[0]%>" ondblclick="document.zaforma.submit();"  /></td>
                            <td>
                                <label for="new<%=i%>" onmouseover="this.style.color = 'red';" onmouseout="this.style.color = '';" ondblclick="document.zaforma.submit();" > 
                                    <%=arr[2]%> - <%=arr[1]%>
                                </label>
                            </td>
                            <td>
                                <a href="<%=AppConstants.APP_NAME%>?action=corelareCategorii&act=hideCategorie&idCategorie=<%=idCategorie%>">hide</a>
                            </td>
                        </tr>
                    <%}%>
                </table>
            </div>
        </td>
    </tr>
    <tr>
        <td colspan="2" align="center">
            <%if( statusHidden.length() > 0){%>
                <span style="float:right;">
                    <a href="<%=AppConstants.APP_NAME%>?action=corelareCategorii&act=clearHidden">Clear hidden (<%=statusHidden%>)</a>
                </span>
            <%}%>
            <input type="submit" value="Creaza corelatie"/>
        </td>
    </tr>
</table>
</form>

<hr />
Lista categorii corelate:
<table border="1" style="font-size:10px">
    <tr style="font-weight:bold;">
        <td>Old ID</td>
        <td>Old Name</td>
        <td>Old Cod SAP</td>
        <td>Current ID</td>
        <td>Current Name</td>
        <td>Current Cod SAP</td>
        <td></td>
    </tr>
    <% for (int i = 0; i < listCategoriiCorelate.size(); i++) {
        final Object[] arr = (Object[]) listCategoriiCorelate.get(i);

        final Object oldId = arr[0];
        final Object newId = arr[3];
    %>
        <tr>
            <%for (int j = 0; j < arr.length; j++) {
                final Object o = arr[j];%>
                <td><%=o%></td>
            <%}%>
            <td>
                <a href="<%=AppConstants.APP_NAME%>?action=corelareCategorii&act=deleteCorelatie&oldId=<%=oldId%>&newId=<%=newId%>">delete</a>
            </td>
        </tr>
    <%}%>
</table>
