package com.srk.pkg;

 

import java.io.File;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.OutputStream;

import java.io.PrintWriter;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.text.DateFormat;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

 

import javax.servlet.RequestDispatcher;

import javax.servlet.ServletException;

import javax.servlet.annotation.WebServlet;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

 

import org.apache.catalina.connector.OutputBuffer;

import org.apache.jasper.tagplugins.jstl.core.Out;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.hssf.util.HSSFColor;

 

import jxl.Workbook;

import jxl.write.Label;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

import jxl.write.WriteException;

import jxl.write.biff.RowsExceededException;

 

/**

 * Servlet implementation class PlanInfo

 */

@WebServlet("/PlanInfo")

public class PlanInfo extends HttpServlet {

private static final long serialVersionUID = 1L;

private String username;

private String db_nm;

private String dbusername;

private String dbpwd;

private String env;

private String htmlRespone;

private int con=0;

private String CITY_NM;

private String STATE_C;

private String ZIP_C;

private String HireDate;

protected String SSN_N;

protected String LST_NM;

protected String FST_NM;

protected String EMPL_N;

protected String DVSN_NM;

protected String REGN_NM;

protected String DVSN_C;

protected String HIGH_CMPS_I;

protected String ANNL_SAL_A;

protected String MRTL_STAT_C;

protected String GENDER_C;

protected String STAT_C;

protected String CO_C;

protected String DSTC_C;

protected String DEPT_C;

protected String UNION_C;

protected String MISC_C;

protected String ELIG_D;

protected String ADJ_HIRE_D;

private String LINE_1_AD;

private String LINE_2_AD;

private String LINE_3_AD;

private String CITY_AD;

private String ST_AD;

private String ZIP5_AD;

private String PartDate;

private String VestDate;

private String DOB;

private String TermDate;

private String urlx;

private String daymonthyearsecond;

private static String url;

static File file = new File("");

static String currentDirectory1 = file.getAbsolutePath();

    /**

     * @see HttpServlet#HttpServlet()

     */

    public PlanInfo() {

        super();

        // TODO Auto-generated constructor stub

    }

 

/**

* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)

* 

* 

*/

    

   

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

// TODO Auto-generated method stub

}

 

/**

* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)

*/

protected void doPost(HttpServletRequest request,

            HttpServletResponse response) throws ServletException, IOException

{

DateFormat dateFormat3 = new SimpleDateFormat("dd-MM-yyyy-HH-mm");

  Date date6 = new Date();

  daymonthyearsecond= dateFormat3.format(date6);

         

        // read form fields

        username = request.getParameter("username");

        dbusername=request.getParameter("dbusername");

        

        

         

        System.out.println("username: " + username);

        System.out.println("dbusername: " + dbusername);

        urlx=username+"_"+daymonthyearsecond;

 

        // do some processing here...

        

        

        

        // get response writer

        PrintWriter writer = response.getWriter();

        try

        {

        Class.forName("com.ibm.db2.jcc.DB2Driver");  

       

        String url="jdbc:db2://db2-conn-tst-pwi:5020/DB2DFMR";

             String user=dbusername;

             String password=dbpwd;

        Connection connection = DriverManager.getConnection(url, user, password);

        if(connection!=null){

                 System.out.println("Connected successfully.");

                 con=con+1;

        }

        System.out.println(con);

       

         

            String query="Select DB_NM from FP"+env+"0DBO.T_IDIP_IDB_PLAN WHERE plan_n ='"+ username+"'";

           

           Statement stmt = connection.createStatement();

           ResultSet rs = stmt.executeQuery(query);

        

           while(rs.next()){

         

         db_nm  = rs.getString("DB_NM");

         System.out.println("DB_NM: " + db_nm);

       }

       

        

        rs.close();

       

        connection.close();

     

        } catch (SQLException | ClassNotFoundException e) {

            e.printStackTrace();

        }

        

 

        // build HTML code

        if(con!=1)

        {

     

        htmlRespone = "<html>";

        htmlRespone += "<h2 id='xx' style='display:block;font-weight:bolder;font-size: 20px;color: lightgreen;'>DB UserName or Password is incorrect."+"<br/>";      

           

        htmlRespone += "</html>";

        writer.println(htmlRespone);

        

        request.setAttribute("mes_add_pageTitle",htmlRespone); //Setting UsernameLabel to mes_add_pageTitle

        }

        else if(db_nm.length()==0)

        {

          htmlRespone = "<html>";

            htmlRespone += "<h2 id='xx' style='display:block;font-weight:bolder;font-size: 20px;color: lightgreen;'>The entered Plan Number: "+username +" is incorrect."+"<br/>";      

               

            htmlRespone += "</html>";

            writer.println(htmlRespone);

            

            request.setAttribute("mes_add_pageTitle",htmlRespone); //Setting UsernameLabel to mes_add_pageTitle

        }

        else

        {

        htmlRespone = "<html>";

        htmlRespone += "<a href='P:\\eclipseserver\\PlanApp\\WebContent\\results\\'+urlx+'.xls'><img border='0' src='../PlanApp/images/xls.ico' width='35' height='35' style='margin-left: 85%;'></a><a href='P:/eclipseserver/PlanApp/WebContent/results/'+urlx+'.xls'><img border='0' src='../PlanApp/images/pdf.ico' width='35' height='35' style='margin-left: 0.5%;'></a>"; 

        htmlRespone += "<h2 id='xx' style='display:block;font-weight:bolder;font-size: 20px;color: lightgreen;'>The Plan: "+username +" is associated with clone:"+"<h2 style='color:white;font-size: 20px;font-weight:bolder;'>"+db_nm+"</h2>"+ "<br/>";      

             htmlRespone += "</html>";

             writer.println(htmlRespone);

             

             request.setAttribute("mes_add_pageTitle",htmlRespone); //Setting UsernameLabel to mes_add_pageTitle

             Print2Excel pi=new Print2Excel();

             try {

            pi.allinfo();

      pi.calldbvalues(username,dbusername,dbpwd,env,urlx);

      } catch (RowsExceededException e) {

      // TODO Auto-generated catch block

      e.printStackTrace();

      } catch (WriteException e) {

      // TODO Auto-generated catch block

      e.printStackTrace();

      }

        }

         

        // return response

       

       

        //pi.allinfo();

        RequestDispatcher rd=request.getRequestDispatcher("/index.jsp");

        rd.forward(request,response);  //forwarded to welcome.jsp

        db_nm="";

        con=0;

        

}

        

        

        

        

 

}