/*
 * Copyright 2006-2007 Queplix Corp.
 *
 * Licensed under the Queplix Public License, Version 1.1.1 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * http://www.queplix.com/solutions/commercial-open-source/queplix-public-license/
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 *
 */

package com.queplix.core.utils.www;

import com.queplix.core.utils.SystemHelper;
import com.queplix.core.utils.sql.SqlWrapper;
import com.queplix.core.utils.sql.SqlWrapperFactory;
import com.queplix.core.utils.sql.vendor.mssql.MssqlText;

import javax.servlet.ServletConfig;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.TimeZone;

/**
 * Simple servlet for internal testing.
 * Loads at the web-app startup.
 *
 * @author [ALB] Baranov Andrey
 * @author [ONZ] Oleg N. Zhovtanyuk
 * @version $Revision: 1.1.1.1 $ $Date: 2005/09/12 15:31:24 $
 */
public class AutoServlet extends AbstractServlet {

    // ===================================================== Servlet API methods

    /* (non-Javadoc)
     * @see GenericServlet#init(ServletConfig)
     */

    public void init(ServletConfig conf) throws ServletException {
        System.out.println("  init()...");
        System.out.println(
                "  is production mode: " + SystemHelper.isProductionMode());
    }

    /* (non-Javadoc)
     * @see HttpServlet#doGet(HttpServletRequest, HttpServletResponse)
     */
    public void service(HttpServletRequest request,
                        HttpServletResponse response) throws ServletException {

        System.out.println("  doGet()...");

        // [ALB] Test MS SQL.
//        testMssqlText();

        // [ALB] Load data.
//        deleteAll();
//        fillLanguages();
//        fillTimeZones();
//        fillCountries();

        ServletContext ctx = request.getSession().getServletContext();
        // Test custom fields for grid
//        ApplicationMetadataHelper.testSetCustomFieldsForGrid(ctx);

    }

    // ========================================================= Private methods

    private void testMssqlText() {

        System.out.println("  testMssqlText()...");
        String sql
                = "SELECT PKEY, DATALENGTH(NOTES), TEXTPTR(NOTES) FROM EMPLOYEE WHERE PKEY < 10";

        SqlWrapper sqlWrapper = SqlWrapperFactory.getSqlWrapper();
        Connection con = null;
        Statement stat = null;

        try {
            con = sqlWrapper.doConnection();
            stat = sqlWrapper.doStatement(con);
            ResultSet rs = stat.executeQuery(sql);
            while(rs.next()) {
                Long pkey = sqlWrapper.getLongParser().getValue(rs, 1);
                Integer size = sqlWrapper.getIntParser().getValue(rs, 2);
                char[] notes = null;
                if(size != null) {
                    notes = MssqlText.getObject(rs, 3, "employee", "notes",
                            size.intValue());
                }
                System.out.println(">>> pkey=" + pkey + " size=" + (
                        notes == null ? 0:notes.length));
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            sqlWrapper.closeConnection(con, stat);
        }

    }

    private void deleteAll() {

        System.out.println("  deleteAll()...");

        String sql0 = "DELETE FROM QX_CAPTION";
        String sql1 = "DELETE FROM QX_COUNTRY";
        String sql2 = "DELETE FROM QX_LANGUAGE";
        String sql3 = "DELETE FROM QX_TIMEZONE";

        SqlWrapper sw = SqlWrapperFactory.getSqlWrapper();
        Connection con = null;

        try {

            con = sw.doConnection();
            PreparedStatement ps = sw.doPreparedStatement(con, sql0);
            sw.executeUpdate(ps);
            ps.close();

            ps = sw.doPreparedStatement(con, sql1);
            sw.executeUpdate(ps);
            ps.close();

            ps = sw.doPreparedStatement(con, sql2);
            sw.executeUpdate(ps);
            ps.close();

            ps = sw.doPreparedStatement(con, sql3);
            sw.executeUpdate(ps);
            ps.close();

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            sw.closeConnection(con);
        }

    }

    private void fillLanguages() {
        System.out.println("  fillLanguages()...");

        String sql = "INSERT INTO QX_LANGUAGE(LANGUAGE_ID,NAME) VALUES (?, ?)";

        SqlWrapper sw = SqlWrapperFactory.getSqlWrapper();
        Connection con = null;
        try {
            con = sw.doConnection();
            PreparedStatement ps = sw.doPreparedStatement(con, sql);
            ps.setString(1, "en");
            ps.setString(2, "English");
            sw.executeUpdate(ps);

            ps.setString(1, "de");
            ps.setString(2, "German");
            sw.executeUpdate(ps);

            ps.setString(1, "ru");
            ps.setString(2, "Russian");
            sw.executeUpdate(ps);

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            sw.closeConnection(con);
        }
    }

    private void fillTimeZones() {
        System.out.println("  fillTimeZones()...");

        String sql
                = "INSERT INTO QX_TIMEZONE(TIMEZONE_ID,NAME,OFFSET) VALUES (?, ?, ?)";

        SqlWrapper sw = SqlWrapperFactory.getSqlWrapper();
        Connection con = null;

        try {

            con = sw.doConnection();
            PreparedStatement ps = sw.doPreparedStatement(con, sql);

            ArrayList insertedTz = new ArrayList();
            String[] ids = TimeZone.getAvailableIDs();
            for(int i = 0; i < ids.length; i++) {

                TimeZone tz = TimeZone.getTimeZone(ids[i]);

                String id = ids[i];
                int offset = tz.getRawOffset() / 60 / 1000;
                String displayName = tz.getDisplayName();
                if(insertedTz.contains(displayName)) {
                    continue;
                } else {
                    insertedTz.add(displayName);
                }

                int mod = Math.abs(offset);
                int h = mod / 60;
                int min = mod - h * 60;
                String hS;
                if(h <= 9) {
                    hS = "0" + h;
                } else {
                    hS = "" + h;
                }
                String minS;
                if(min <= 9) {
                    minS = "0" + min;
                } else {
                    minS = "" + min;
                }

                String name = "(GMT";
                if(offset > 0) {
                    name += "+";
                } else {
                    name += "-";
                }

                name += hS + ":" + minS + ") " + displayName;

                System.out.println("insert id=" + id);

                ps.setString(1, id);
                ps.setString(2, name);
                ps.setInt(3, offset);
                sw.executeUpdate(ps);
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            sw.closeConnection(con);
        }

    }

    private void fillCountries() {

        System.out.println("  fillCountries()...");

        String sql =
                "INSERT INTO QX_COUNTRY(COUNTRY_ID,NAME,DEF_LANG_ID,DEF_TIMEZONE_ID) VALUES (?, ?, ?, ?)";

        SqlWrapper sw = SqlWrapperFactory.getSqlWrapper();
        Connection con = null;

        try {

            con = sw.doConnection();
            PreparedStatement ps = sw.doPreparedStatement(con, sql);
            ps.setString(1, "US");
            ps.setString(2, "USA");
            ps.setString(3, "en");
            ps.setString(4, "America/Los_Angeles");
            sw.executeUpdate(ps);

            ps.setString(1, "EN");
            ps.setString(2, "England");
            ps.setString(3, "en");
            ps.setString(4, "UTC");
            sw.executeUpdate(ps);

            ps.setString(1, "RU");
            ps.setString(2, "Russia");
            ps.setString(3, "ru");
            ps.setString(4, "Europe/Moscow");
            sw.executeUpdate(ps);

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            sw.closeConnection(con);
        }

    }

}