Report_PRCR_New_EPF_Excel_File_Generator.java Source code

Java tutorial

Introduction

Here is the source code for Report_PRCR_New_EPF_Excel_File_Generator.java

Source

import java.awt.event.KeyEvent;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.SQLException;
import org.apache.commons.io.FileUtils;

import java.awt.Desktop;
import java.io.*;
import java.sql.ResultSet;
import org.apache.commons.io.FileUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
/**
 *
 * @author Pramo
 */
public class Report_PRCR_New_EPF_Excel_File_Generator extends javax.swing.JPanel {

    /**
     *
     */
    public Report_PRCR_New_EPF_Excel_File_Generator() {
        initComponents();
    }

    DateChooser_text datechooser = new DateChooser_text();
    Date_Handler datehandler = new Date_Handler();
    Report_gen generate = new Report_gen();
    UserAccountControl user = new UserAccountControl();
    DatabaseManager dbm = DatabaseManager.getDbCon();

    public void focus() {
        //dayfield.requestFocus();
        //dayfield.selectAll();
    }

    /**
     * This method is called from within the constructor to initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is always
     * regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        jPanel2 = new javax.swing.JPanel();
        datepanel = new javax.swing.JPanel();
        monthfield = new javax.swing.JTextField();
        yearfield = new javax.swing.JTextField();
        datePicker1 = new com.michaelbaranov.microba.calendar.DatePicker();
        jLabel1 = new javax.swing.JLabel();
        jLabel4 = new javax.swing.JLabel();
        view1 = new javax.swing.JButton();
        payment_date_panel = new javax.swing.JPanel();
        jLabel2 = new javax.swing.JLabel();
        datepanel1 = new javax.swing.JPanel();
        monthfield1 = new javax.swing.JTextField();
        yearfield1 = new javax.swing.JTextField();
        dayfield = new javax.swing.JTextField();
        datePicker2 = new com.michaelbaranov.microba.calendar.DatePicker();
        jLabel3 = new javax.swing.JLabel();
        payment_mode_combo = new javax.swing.JComboBox();
        jLabel5 = new javax.swing.JLabel();
        payment_referrence_textFiield = new javax.swing.JTextField();
        jLabel6 = new javax.swing.JLabel();
        chk = new javax.swing.JCheckBox();
        jButton1 = new javax.swing.JButton();

        setBorder(javax.swing.BorderFactory.createLineBorder(new java.awt.Color(0, 0, 0), 6));

        jPanel2.setBorder(javax.swing.BorderFactory.createLineBorder(new java.awt.Color(0, 51, 153), 2));

        datepanel.setBorder(javax.swing.BorderFactory.createEtchedBorder());

        monthfield.setText(datehandler.get_today_month());
        monthfield.addKeyListener(new java.awt.event.KeyAdapter() {
            public void keyPressed(java.awt.event.KeyEvent evt) {
                monthfieldKeyPressed(evt);
            }
        });

        yearfield.setText(datehandler.get_today_year());
        yearfield.addKeyListener(new java.awt.event.KeyAdapter() {
            public void keyPressed(java.awt.event.KeyEvent evt) {
                yearfieldKeyPressed(evt);
            }
        });

        datePicker1.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                datePicker1ActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout datepanelLayout = new javax.swing.GroupLayout(datepanel);
        datepanel.setLayout(datepanelLayout);
        datepanelLayout
                .setHorizontalGroup(datepanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                        .addGroup(datepanelLayout.createSequentialGroup().addContainerGap()
                                .addComponent(monthfield, javax.swing.GroupLayout.PREFERRED_SIZE, 43,
                                        javax.swing.GroupLayout.PREFERRED_SIZE)
                                .addGap(0, 0, 0)
                                .addComponent(yearfield, javax.swing.GroupLayout.PREFERRED_SIZE, 44,
                                        javax.swing.GroupLayout.PREFERRED_SIZE)
                                .addGap(0, 0, 0)
                                .addComponent(datePicker1, javax.swing.GroupLayout.PREFERRED_SIZE, 42,
                                        javax.swing.GroupLayout.PREFERRED_SIZE)
                                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)));
        datepanelLayout.setVerticalGroup(datepanelLayout
                .createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                .addGroup(datepanelLayout.createSequentialGroup().addGap(0, 0, 0).addGroup(datepanelLayout
                        .createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                        .addComponent(datePicker1, javax.swing.GroupLayout.PREFERRED_SIZE, 30,
                                javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addGroup(datepanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                                .addComponent(monthfield, javax.swing.GroupLayout.PREFERRED_SIZE,
                                        javax.swing.GroupLayout.DEFAULT_SIZE,
                                        javax.swing.GroupLayout.PREFERRED_SIZE)
                                .addComponent(yearfield, javax.swing.GroupLayout.PREFERRED_SIZE,
                                        javax.swing.GroupLayout.DEFAULT_SIZE,
                                        javax.swing.GroupLayout.PREFERRED_SIZE)))
                        .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)));

        jLabel1.setText("Year Month");

        javax.swing.GroupLayout jPanel2Layout = new javax.swing.GroupLayout(jPanel2);
        jPanel2.setLayout(jPanel2Layout);
        jPanel2Layout.setHorizontalGroup(jPanel2Layout
                .createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, jPanel2Layout.createSequentialGroup()
                        .addGap(23, 23, 23).addComponent(jLabel1).addGap(50, 50, 50)
                        .addComponent(datepanel, javax.swing.GroupLayout.PREFERRED_SIZE,
                                javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)));
        jPanel2Layout.setVerticalGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                .addGroup(jPanel2Layout.createSequentialGroup().addGap(24, 24, 24).addComponent(jLabel1)
                        .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
                .addGroup(javax.swing.GroupLayout.Alignment.TRAILING,
                        jPanel2Layout.createSequentialGroup()
                                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                                .addComponent(datepanel, javax.swing.GroupLayout.PREFERRED_SIZE, 40,
                                        javax.swing.GroupLayout.PREFERRED_SIZE)
                                .addContainerGap()));

        jLabel4.setFont(new java.awt.Font("Tahoma", 1, 24)); // NOI18N
        jLabel4.setText("EPF Excel File Generator");

        view1.setText("Generate EPF file for the Govt.");
        view1.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                view1MouseClicked(evt);
            }
        });
        view1.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                view1ActionPerformed(evt);
            }
        });

        payment_date_panel.setBorder(javax.swing.BorderFactory.createLineBorder(new java.awt.Color(0, 51, 153), 2));

        jLabel2.setText("Date of Payment");

        datepanel1.setBorder(javax.swing.BorderFactory.createEtchedBorder());

        monthfield1.setText(datehandler.get_today_month());
        monthfield1.addKeyListener(new java.awt.event.KeyAdapter() {
            public void keyPressed(java.awt.event.KeyEvent evt) {
                monthfield1KeyPressed(evt);
            }
        });

        yearfield1.setText(datehandler.get_today_year());
        yearfield1.addKeyListener(new java.awt.event.KeyAdapter() {
            public void keyPressed(java.awt.event.KeyEvent evt) {
                yearfield1KeyPressed(evt);
            }
        });

        dayfield.setText("" + Integer.parseInt(datehandler.get_today_day()));
        dayfield.addKeyListener(new java.awt.event.KeyAdapter() {
            public void keyPressed(java.awt.event.KeyEvent evt) {
                dayfieldKeyPressed(evt);
            }
        });

        datePicker2.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                datePicker2ActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout datepanel1Layout = new javax.swing.GroupLayout(datepanel1);
        datepanel1.setLayout(datepanel1Layout);
        datepanel1Layout
                .setHorizontalGroup(datepanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                        .addGroup(datepanel1Layout.createSequentialGroup().addContainerGap()
                                .addComponent(dayfield, javax.swing.GroupLayout.PREFERRED_SIZE, 41,
                                        javax.swing.GroupLayout.PREFERRED_SIZE)
                                .addGap(0, 0, 0)
                                .addComponent(monthfield1, javax.swing.GroupLayout.PREFERRED_SIZE, 43,
                                        javax.swing.GroupLayout.PREFERRED_SIZE)
                                .addGap(0, 0, 0)
                                .addComponent(yearfield1, javax.swing.GroupLayout.PREFERRED_SIZE, 44,
                                        javax.swing.GroupLayout.PREFERRED_SIZE)
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                                .addComponent(datePicker2, javax.swing.GroupLayout.PREFERRED_SIZE, 42,
                                        javax.swing.GroupLayout.PREFERRED_SIZE)
                                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)));
        datepanel1Layout.setVerticalGroup(datepanel1Layout
                .createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                .addGroup(datepanel1Layout.createSequentialGroup().addGap(0, 0, 0).addGroup(datepanel1Layout
                        .createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                        .addComponent(datePicker2, javax.swing.GroupLayout.PREFERRED_SIZE, 30,
                                javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addGroup(datepanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                                .addComponent(dayfield, javax.swing.GroupLayout.PREFERRED_SIZE,
                                        javax.swing.GroupLayout.DEFAULT_SIZE,
                                        javax.swing.GroupLayout.PREFERRED_SIZE)
                                .addComponent(monthfield1, javax.swing.GroupLayout.PREFERRED_SIZE,
                                        javax.swing.GroupLayout.DEFAULT_SIZE,
                                        javax.swing.GroupLayout.PREFERRED_SIZE)
                                .addComponent(yearfield1, javax.swing.GroupLayout.PREFERRED_SIZE,
                                        javax.swing.GroupLayout.DEFAULT_SIZE,
                                        javax.swing.GroupLayout.PREFERRED_SIZE)))
                        .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)));

        jLabel3.setText("Payment Mode");

        payment_mode_combo.setModel(new javax.swing.DefaultComboBoxModel(
                new String[] { "Cheque", "Cash", "Money Order", "Direct Debit" }));

        jLabel5.setText("Payment Reference");

        payment_referrence_textFiield.setFont(new java.awt.Font("Tahoma", 1, 12)); // NOI18N
        payment_referrence_textFiield.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                payment_referrence_textFiieldActionPerformed(evt);
            }
        });

        jLabel6.setText("Bank Code + Branch Code + Account Number");

        javax.swing.GroupLayout payment_date_panelLayout = new javax.swing.GroupLayout(payment_date_panel);
        payment_date_panel.setLayout(payment_date_panelLayout);
        payment_date_panelLayout.setHorizontalGroup(payment_date_panelLayout
                .createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                .addGroup(payment_date_panelLayout.createSequentialGroup().addGap(18, 18, 18)
                        .addGroup(payment_date_panelLayout
                                .createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                                .addGroup(payment_date_panelLayout.createSequentialGroup()
                                        .addGroup(payment_date_panelLayout
                                                .createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                                                .addComponent(jLabel2).addComponent(jLabel3))
                                        .addGroup(payment_date_panelLayout
                                                .createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                                                .addGroup(javax.swing.GroupLayout.Alignment.TRAILING,
                                                        payment_date_panelLayout.createSequentialGroup()
                                                                .addPreferredGap(
                                                                        javax.swing.LayoutStyle.ComponentPlacement.RELATED,
                                                                        javax.swing.GroupLayout.DEFAULT_SIZE,
                                                                        Short.MAX_VALUE)
                                                                .addComponent(jLabel6).addGap(23, 23, 23))
                                                .addGroup(payment_date_panelLayout.createSequentialGroup()
                                                        .addGap(32, 32, 32)
                                                        .addGroup(payment_date_panelLayout
                                                                .createParallelGroup(
                                                                        javax.swing.GroupLayout.Alignment.LEADING)
                                                                .addComponent(datepanel1,
                                                                        javax.swing.GroupLayout.PREFERRED_SIZE,
                                                                        javax.swing.GroupLayout.DEFAULT_SIZE,
                                                                        javax.swing.GroupLayout.PREFERRED_SIZE)
                                                                .addComponent(payment_mode_combo,
                                                                        javax.swing.GroupLayout.PREFERRED_SIZE,
                                                                        javax.swing.GroupLayout.DEFAULT_SIZE,
                                                                        javax.swing.GroupLayout.PREFERRED_SIZE))
                                                        .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE,
                                                                Short.MAX_VALUE))))
                                .addGroup(payment_date_panelLayout.createSequentialGroup().addComponent(jLabel5)
                                        .addGap(18, 18, 18)
                                        .addComponent(payment_referrence_textFiield,
                                                javax.swing.GroupLayout.PREFERRED_SIZE, 200,
                                                javax.swing.GroupLayout.PREFERRED_SIZE)
                                        .addContainerGap()))));
        payment_date_panelLayout.setVerticalGroup(payment_date_panelLayout
                .createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                .addGroup(payment_date_panelLayout.createSequentialGroup().addGroup(payment_date_panelLayout
                        .createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                        .addGroup(payment_date_panelLayout.createSequentialGroup().addContainerGap().addComponent(
                                datepanel1, javax.swing.GroupLayout.PREFERRED_SIZE, 40,
                                javax.swing.GroupLayout.PREFERRED_SIZE))
                        .addGroup(payment_date_panelLayout
                                .createSequentialGroup().addGap(24, 24, 24).addComponent(jLabel2)))
                        .addGap(28, 28, 28)
                        .addGroup(payment_date_panelLayout
                                .createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                                .addComponent(jLabel3).addComponent(payment_mode_combo,
                                        javax.swing.GroupLayout.PREFERRED_SIZE,
                                        javax.swing.GroupLayout.DEFAULT_SIZE,
                                        javax.swing.GroupLayout.PREFERRED_SIZE))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, 32, Short.MAX_VALUE)
                        .addGroup(payment_date_panelLayout
                                .createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                                .addComponent(jLabel5).addComponent(payment_referrence_textFiield,
                                        javax.swing.GroupLayout.PREFERRED_SIZE,
                                        javax.swing.GroupLayout.DEFAULT_SIZE,
                                        javax.swing.GroupLayout.PREFERRED_SIZE))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED).addComponent(jLabel6)
                        .addGap(12, 12, 12)));

        chk.setText("Generate only the member details Excel file");
        chk.addItemListener(new java.awt.event.ItemListener() {
            public void itemStateChanged(java.awt.event.ItemEvent evt) {
                chkItemStateChanged(evt);
            }
        });

        jButton1.setText("Generate EPF file for the bank");
        jButton1.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton1ActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(this);
        this.setLayout(layout);
        layout.setHorizontalGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                .addGroup(layout.createSequentialGroup()
                        .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                        .addComponent(jLabel4).addGap(38, 38, 38))
                .addGroup(layout.createSequentialGroup()
                        .addGroup(layout
                                .createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                                .addGroup(
                                        javax.swing.GroupLayout.Alignment.TRAILING,
                                        layout.createSequentialGroup().addGap(84, 84, 84).addComponent(chk)
                                                .addGap(57, 57, 57))
                                .addGroup(layout.createSequentialGroup().addContainerGap().addGroup(layout
                                        .createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                                        .addComponent(jPanel2, javax.swing.GroupLayout.DEFAULT_SIZE,
                                                javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                                        .addComponent(payment_date_panel, javax.swing.GroupLayout.DEFAULT_SIZE,
                                                javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                                        .addComponent(view1, javax.swing.GroupLayout.DEFAULT_SIZE,
                                                javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                                        .addComponent(jButton1, javax.swing.GroupLayout.DEFAULT_SIZE,
                                                javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))))
                        .addContainerGap()));
        layout.setVerticalGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                .addGroup(layout.createSequentialGroup().addContainerGap()
                        .addComponent(jLabel4, javax.swing.GroupLayout.DEFAULT_SIZE, 42, Short.MAX_VALUE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jPanel2, javax.swing.GroupLayout.PREFERRED_SIZE,
                                javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(payment_date_panel, javax.swing.GroupLayout.PREFERRED_SIZE,
                                javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED).addComponent(chk)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                        .addComponent(view1, javax.swing.GroupLayout.PREFERRED_SIZE, 33,
                                javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED).addComponent(jButton1,
                                javax.swing.GroupLayout.PREFERRED_SIZE, 26, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addContainerGap()));

        layout.linkSize(javax.swing.SwingConstants.VERTICAL, new java.awt.Component[] { jButton1, view1 });

    }// </editor-fold>//GEN-END:initComponents

    private void monthfieldKeyPressed(java.awt.event.KeyEvent evt) {//GEN-FIRST:event_monthfieldKeyPressed
        if (monthfield.getText().equals("Jan")) {
            if (evt.getKeyCode() == KeyEvent.VK_DOWN) {
                monthfield.setText("Dec");
                int yr = Integer.parseInt(yearfield.getText());

                yearfield.setText("" + (yr - 1));
                monthfield.selectAll();

            }
            if (evt.getKeyCode() == KeyEvent.VK_UP) {
                monthfield.setText("Feb");
                monthfield.selectAll();
            }

        } else if (monthfield.getText().equals("Feb")) {
            if (evt.getKeyCode() == KeyEvent.VK_DOWN) {
                monthfield.setText("Jan");
                int yr = Integer.parseInt(yearfield.getText());
                monthfield.selectAll();

            }
            if (evt.getKeyCode() == KeyEvent.VK_UP) {
                monthfield.setText("Mar");
                monthfield.selectAll();
            }

        } else if (monthfield.getText().equals("Mar")) {
            if (evt.getKeyCode() == KeyEvent.VK_DOWN) {
                monthfield.setText("Feb");
                int yr = Integer.parseInt(yearfield.getText());
                monthfield.selectAll();
            }
            if (evt.getKeyCode() == KeyEvent.VK_UP) {
                monthfield.setText("Apr");
                monthfield.selectAll();
            }

        } else if (monthfield.getText().equals("Apr")) {
            if (evt.getKeyCode() == KeyEvent.VK_DOWN) {
                monthfield.setText("Mar");
                int yr = Integer.parseInt(yearfield.getText());
                monthfield.selectAll();
            }
            if (evt.getKeyCode() == KeyEvent.VK_UP) {
                monthfield.setText("May");
                monthfield.selectAll();
            }

        } else if (monthfield.getText().equals("May")) {
            if (evt.getKeyCode() == KeyEvent.VK_DOWN) {
                monthfield.setText("Apr");
                int yr = Integer.parseInt(yearfield.getText());
                monthfield.selectAll();

            }
            if (evt.getKeyCode() == KeyEvent.VK_UP) {

                monthfield.setText("Jun");
                monthfield.selectAll();
            }

        } else if (monthfield.getText().equals("Jun")) {
            if (evt.getKeyCode() == KeyEvent.VK_DOWN) {
                monthfield.setText("May");
                int yr = Integer.parseInt(yearfield.getText());
                monthfield.selectAll();

            }
            if (evt.getKeyCode() == KeyEvent.VK_UP) {
                monthfield.setText("Jul");
                monthfield.selectAll();
            }

        } else if (monthfield.getText().equals("Jul")) {
            if (evt.getKeyCode() == KeyEvent.VK_DOWN) {
                monthfield.setText("Jun");
                int yr = Integer.parseInt(yearfield.getText());
                monthfield.selectAll();

            }
            if (evt.getKeyCode() == KeyEvent.VK_UP) {
                monthfield.setText("Aug");
                monthfield.selectAll();
            }

        } else if (monthfield.getText().equals("Aug")) {
            if (evt.getKeyCode() == KeyEvent.VK_DOWN) {
                monthfield.setText("Jul");
                int yr = Integer.parseInt(yearfield.getText());
                monthfield.selectAll();

            }
            if (evt.getKeyCode() == KeyEvent.VK_UP) {
                monthfield.setText("Sep");
                monthfield.selectAll();
            }

        } else if (monthfield.getText().equals("Sep")) {
            if (evt.getKeyCode() == KeyEvent.VK_DOWN) {
                monthfield.setText("Aug");
                int yr = Integer.parseInt(yearfield.getText());
                monthfield.selectAll();

            }
            if (evt.getKeyCode() == KeyEvent.VK_UP) {
                monthfield.setText("Oct");
                monthfield.selectAll();
            }

        } else if (monthfield.getText().equals("Oct")) {
            if (evt.getKeyCode() == KeyEvent.VK_DOWN) {
                monthfield.setText("Sep");
                int yr = Integer.parseInt(yearfield.getText());
                monthfield.selectAll();

            }
            if (evt.getKeyCode() == KeyEvent.VK_UP) {
                monthfield.setText("Nov");
                monthfield.selectAll();
            }

        } else if (monthfield.getText().equals("Nov")) {
            if (evt.getKeyCode() == KeyEvent.VK_DOWN) {
                monthfield.setText("Oct");
                int yr = Integer.parseInt(yearfield.getText());
                monthfield.selectAll();

            }
            if (evt.getKeyCode() == KeyEvent.VK_UP) {
                monthfield.setText("Dec");
                monthfield.selectAll();
            }

        } else if (monthfield.getText().equals("Dec")) {
            if (evt.getKeyCode() == KeyEvent.VK_DOWN) {
                monthfield.setText("Nov");
                int yr = Integer.parseInt(yearfield.getText());
                monthfield.selectAll();

            }
            if (evt.getKeyCode() == KeyEvent.VK_UP) {
                monthfield.setText("Jan");
                int yr = Integer.parseInt(yearfield.getText());

                yearfield.setText("" + (yr + 1));
                monthfield.selectAll();
            }

        }
        if (evt.getKeyCode() == KeyEvent.VK_LEFT) {
            // dayfield.requestFocus();
            // dayfield.selectAll();
        }
        if (evt.getKeyCode() == KeyEvent.VK_RIGHT) {
            yearfield.requestFocus();
            yearfield.selectAll();
        }

        if (evt.getKeyCode() == KeyEvent.VK_ENTER) { ////// ChaNGE  focus on enter////////////////
            //  dayfield2.requestFocus();
            // dayfield2.selectAll();

        }
    }//GEN-LAST:event_monthfieldKeyPressed

    private void yearfieldKeyPressed(java.awt.event.KeyEvent evt) {//GEN-FIRST:event_yearfieldKeyPressed
        if (evt.getKeyCode() == KeyEvent.VK_UP) {
            yearfield.setText("" + (Integer.parseInt(yearfield.getText()) + 1));
            yearfield.selectAll();
        }
        if (evt.getKeyCode() == KeyEvent.VK_DOWN) {
            yearfield.setText("" + (Integer.parseInt(yearfield.getText()) - 1));
            yearfield.selectAll();
        }
        if (evt.getKeyCode() == KeyEvent.VK_LEFT) {
            monthfield.requestFocus();
            monthfield.selectAll();
        }

        if (evt.getKeyCode() == KeyEvent.VK_ENTER) { ////// ChaNGE  focus on enter////////////////
            //   dayfield2.requestFocus();
            //  dayfield2.selectAll();

        }
    }//GEN-LAST:event_yearfieldKeyPressed

    private void datePicker1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_datePicker1ActionPerformed
        java.sql.Date datef = new java.sql.Date(datePicker1.getDate().getTime());

        // dayfield.setText(datehandler.get_day(datef));
        monthfield.setText(datehandler.get_month(datef));
        yearfield.setText(datehandler.get_year(datef));
        //  dayfield2.requestFocus();
        // dayfield2.selectAll();
    }//GEN-LAST:event_datePicker1ActionPerformed

    private void view1MouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_view1MouseClicked
        // TODO add your handling code here:
    }//GEN-LAST:event_view1MouseClicked

    private static void copyFileUsingApacheCommonsIO(File source, File dest) throws IOException {

        FileUtils.copyFile(source, dest);

    }

    private static String[] split_name(String name) {
        int i = 0;
        int check = 0;
        String return_name[] = new String[2];

        for (i = 0; i < name.length(); i++) {
            if (name.charAt(i) != ' ' && name.charAt(i) != '.') {
                check++;
            } else {
                check = 0;
            }

            if (check == 2) {
                if (i - 2 <= 0) {
                    return_name[0] = " ";
                    return_name[1] = name;
                } else {
                    return_name[0] = name.substring(0, i - 2);
                    return_name[1] = name.substring(i - 1, name.length());

                    return_name[0] = return_name[0].replaceAll("\\.", " ");
                }
                break;
            }
        }
        return return_name;
    }

    private void view1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_view1ActionPerformed
        try {

            DatabaseManager dbm = DatabaseManager.getDbCon();
            Date_Handler dt = new Date_Handler();

            String year = yearfield.getText();
            String month = dt.return_month_as_num(monthfield.getText());

            String employee_detail_file_location = dbm.checknReturnData("file_locations", "id", "7", "location")
                    + "/" + year + month + ".xls";
            System.out.println(employee_detail_file_location);
            copyFileUsingApacheCommonsIO(new File(dbm.checknReturnData("file_locations", "id", "6", "location")),
                    new File(employee_detail_file_location));

            InputStream inp = new FileInputStream(employee_detail_file_location);

            Workbook wb = WorkbookFactory.create(inp);

            org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(0);

            String payment_date_year_month_date = null;

            String table_name = "pr_workdata_" + year + "_" + month;

            String epf_backup_year_month = year + "_" + month;

            String previous_table_name = null;

            if (Integer.parseInt(month) == 1) {
                previous_table_name = "pr_workdata_" + (Integer.parseInt(year) - 1) + "_" + 12;
            } else {
                if ((Integer.parseInt(month) - 1) < 10) {
                    previous_table_name = "pr_workdata_" + year + "_0" + (Integer.parseInt(month) - 1);
                } else {
                    previous_table_name = "pr_workdata_" + year + "_" + (Integer.parseInt(month) - 1);
                }
            }

            double employee_contribution_percentage = Math.round(Double.parseDouble(
                    dbm.checknReturnData("prcr_new_epf_details", "name", "employee_contribution", "value")) * 100.0)
                    / 100.0;
            double employer_contribution_percentage = Math.round(Double.parseDouble(
                    dbm.checknReturnData("prcr_new_epf_details", "name", "employer_contribution", "value")) * 100.0)
                    / 100.0;

            String nic = null;
            String surname = null;
            String initials = null;
            int member_no = 0;
            double tot_contribution = 0;
            double employers_contribution = 0;
            double member_contribution = 0;
            double tot_earnings = 0;
            String member_status = null;
            String zone = null;
            int employer_number = 0;
            int contribution_period = 0;
            int data_submission_no = 0;
            double no_of_days_worked = 0;
            int occupation_classification_grade = 0;

            int payment_mode = 0;
            int payment_date = 0;
            String payment_reference = null;
            int d_o_code = 0;

            member_status = "E";
            zone = dbm.checknReturnData("prcr_new_epf_details", "name", "zone", "value");
            employer_number = Integer
                    .parseInt(dbm.checknReturnData("prcr_new_epf_details", "name", "employer_number", "value"));
            contribution_period = Integer.parseInt(year + month);
            data_submission_no = 1;
            occupation_classification_grade = Integer.parseInt(dbm.checknReturnData("prcr_new_epf_details", "name",
                    "occupation_classification_grade", "value"));

            int normal_days = 0;
            int sundays = 0;

            double ot_before = 0;
            double ot_after = 0;
            double hours_as_decimal = 0;

            int count = 0;
            double total_member_contribution = 0;
            int need_both_reports = 1;
            if (chk.isSelected()) {
                need_both_reports = 0;
            } else {
                need_both_reports = 1;
            }

            ResultSet query = dbm
                    .query("SELECT * FROM `" + table_name + "` WHERE `register_or_casual` = 1 AND `total_pay` > 0");
            while (query.next()) {
                ResultSet query1 = dbm
                        .query("SELECT * FROM `personal_info` WHERE `code` = '" + query.getInt("code") + "' ");
                while (query1.next()) {

                    nic = query1.getString("nic").replaceAll("\\s+", "");

                    surname = split_name(query1.getString("name"))[1];
                    initials = split_name(query1.getString("name"))[0];

                    member_no = Integer.parseInt(query1.getString("code"));
                    occupation_classification_grade = Integer.parseInt(query1.getString("occupation_grade"));

                    tot_earnings = Math.round(query.getDouble("total_pay") * 100.0) / 100.0;

                    if (dbm.checkWhetherDataExistsTwoColumns("prcr_epf_etf_backup", "month", epf_backup_year_month,
                            "code", member_no) == 1) {
                        tot_earnings = tot_earnings
                                + Double.parseDouble(dbm.checknReturnDatafor2checks("prcr_epf_etf_backup", "month",
                                        epf_backup_year_month, "code", member_no, "total_pay"));
                    }

                    employers_contribution = Math.round(tot_earnings * employer_contribution_percentage * 100.0)
                            / 100.0;
                    member_contribution = Math.round(tot_earnings * employee_contribution_percentage * 100.0)
                            / 100.0;
                    tot_contribution = employers_contribution + member_contribution;
                    total_member_contribution = total_member_contribution + tot_contribution;

                    normal_days = query.getInt("normal_days");
                    sundays = query.getInt("sundays");
                    ot_before = query.getDouble("ot_before_hours");
                    ot_after = query.getDouble("ot_after_hours");
                    if ((ot_before + ot_after) > 0) {
                        hours_as_decimal = (ot_before + ot_after) / 100;
                    } else {
                        hours_as_decimal = 0;
                    }
                    if ((normal_days + sundays + hours_as_decimal) > 0) {
                        no_of_days_worked = Math.round((normal_days + sundays + hours_as_decimal) * 100.0) / 100.0;
                    } else {
                        no_of_days_worked = 0;
                    }

                    if (dbm.checkWhetherDataExists(previous_table_name, "code", query1.getString("code")) == 1) {
                        member_status = "E";
                    } else {
                        member_status = "N";
                    }

                    Row row = sheet.getRow(4 + count);
                    if (row == null) {
                        row = sheet.createRow(4 + count);
                    }

                    for (int k = 0; k < 15; k++) {

                        Cell cell = row.getCell(k);

                        switch (k) {
                        case 0:
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cell.setCellValue(nic);
                            break;
                        case 1:
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cell.setCellValue(surname);
                            break;
                        case 2:
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cell.setCellValue(initials);
                            break;
                        case 3:
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(member_no);
                            break;
                        case 4:
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(tot_contribution);
                            break;
                        case 5:
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(employers_contribution);
                            break;
                        case 6:
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(member_contribution);
                            break;
                        case 7:
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(tot_earnings);
                            break;
                        case 8:
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cell.setCellValue(member_status);
                            break;
                        case 9:
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cell.setCellValue(zone);
                            break;
                        case 10:
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(employer_number);
                            break;
                        case 11:
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(contribution_period);
                            break;
                        case 12:
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(data_submission_no);
                            break;
                        case 13:
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(no_of_days_worked);
                            break;
                        case 14:
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(occupation_classification_grade);
                            break;
                        default:
                            break;
                        }

                    }
                    count++;
                }
                query1.close();
            }
            query.close();

            FileOutputStream fileOut = new FileOutputStream(employee_detail_file_location);
            wb.write(fileOut);
            fileOut.close();

            Desktop.getDesktop().open(new File(employee_detail_file_location));

            if (need_both_reports == 1) {

                if (Integer.parseInt(dayfield.getText()) < 10) {
                    payment_date_year_month_date = yearfield1.getText()
                            + dt.return_month_as_num(monthfield1.getText()) + "0" + dayfield.getText();
                } else {
                    payment_date_year_month_date = yearfield1.getText()
                            + dt.return_month_as_num(monthfield1.getText()) + dayfield.getText();
                }
                payment_date = Integer.parseInt(payment_date_year_month_date);
                payment_mode = payment_mode_combo.getSelectedIndex() + 1;

                payment_reference = payment_referrence_textFiield.getText();

                d_o_code = Integer
                        .parseInt(dbm.checknReturnData("prcr_new_epf_details", "name", "d_o_code", "value"));

                String total_contribution_file_location = dbm.checknReturnData("file_locations", "id", "9",
                        "location") + "/" + year + month + "_total_contribution.xls";

                copyFileUsingApacheCommonsIO(
                        new File(dbm.checknReturnData("file_locations", "id", "8", "location")),
                        new File(total_contribution_file_location));

                InputStream inp2 = new FileInputStream(total_contribution_file_location);

                Workbook wb2 = WorkbookFactory.create(inp2);

                org.apache.poi.ss.usermodel.Sheet sheet2 = wb2.getSheetAt(0);

                Row row = sheet2.getRow(17);
                if (row == null) {
                    row = sheet.createRow(17);
                }

                for (int k = 0; k < 10; k++) {

                    Cell cell = row.getCell(k);

                    switch (k) {
                    case 0:
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cell.setCellValue(zone);
                        break;
                    case 1:
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(employer_number);
                        break;
                    case 2:
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(contribution_period);
                        break;
                    case 3:
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(data_submission_no);
                        break;
                    case 4:
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(total_member_contribution);
                        break;
                    case 5:
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(count);
                        break;
                    case 6:
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(payment_mode);
                        break;
                    case 7:
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cell.setCellValue(payment_reference);
                        break;
                    case 8:
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(payment_date);
                        break;
                    case 9:
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(d_o_code);
                        break;
                    default:
                        break;
                    }

                }

                FileOutputStream fileOut2 = new FileOutputStream(total_contribution_file_location);
                wb2.write(fileOut2);
                fileOut2.close();

                Desktop.getDesktop().open(new File(total_contribution_file_location));

            }

        } catch (Exception ex) {
            System.out.println(ex);
            msg.showMessage(
                    "Problem Occured.Check whether the Excel file is alredy opened.Please close it and try again..",
                    "Error", "error");

        }
    }//GEN-LAST:event_view1ActionPerformed

    MessageBox msg = new MessageBox();

    private void monthfield1KeyPressed(java.awt.event.KeyEvent evt) {//GEN-FIRST:event_monthfield1KeyPressed
        if (monthfield1.getText().equals("Jan")) {
            if (evt.getKeyCode() == KeyEvent.VK_DOWN) {
                monthfield1.setText("Dec");
                int yr = Integer.parseInt(yearfield.getText());

                yearfield1.setText("" + (yr - 1));
                monthfield1.selectAll();

            }
            if (evt.getKeyCode() == KeyEvent.VK_UP) {
                monthfield1.setText("Feb");
                monthfield1.selectAll();
            }

        } else if (monthfield1.getText().equals("Feb")) {
            if (evt.getKeyCode() == KeyEvent.VK_DOWN) {
                monthfield1.setText("Jan");
                int yr = Integer.parseInt(yearfield.getText());
                monthfield1.selectAll();

            }
            if (evt.getKeyCode() == KeyEvent.VK_UP) {
                monthfield1.setText("Mar");
                monthfield1.selectAll();
            }

        } else if (monthfield1.getText().equals("Mar")) {
            if (evt.getKeyCode() == KeyEvent.VK_DOWN) {
                monthfield1.setText("Feb");
                int yr = Integer.parseInt(yearfield1.getText());
                monthfield1.selectAll();
            }
            if (evt.getKeyCode() == KeyEvent.VK_UP) {
                monthfield1.setText("Apr");
                monthfield1.selectAll();
            }

        } else if (monthfield1.getText().equals("Apr")) {
            if (evt.getKeyCode() == KeyEvent.VK_DOWN) {
                monthfield1.setText("Mar");
                int yr = Integer.parseInt(yearfield1.getText());
                monthfield1.selectAll();
            }
            if (evt.getKeyCode() == KeyEvent.VK_UP) {
                monthfield1.setText("May");
                monthfield1.selectAll();
            }

        } else if (monthfield1.getText().equals("May")) {
            if (evt.getKeyCode() == KeyEvent.VK_DOWN) {
                monthfield1.setText("Apr");
                int yr = Integer.parseInt(yearfield1.getText());
                monthfield1.selectAll();

            }
            if (evt.getKeyCode() == KeyEvent.VK_UP) {

                monthfield1.setText("Jun");
                monthfield1.selectAll();
            }

        } else if (monthfield1.getText().equals("Jun")) {
            if (evt.getKeyCode() == KeyEvent.VK_DOWN) {
                monthfield1.setText("May");
                int yr = Integer.parseInt(yearfield1.getText());
                monthfield1.selectAll();

            }
            if (evt.getKeyCode() == KeyEvent.VK_UP) {
                monthfield1.setText("Jul");
                monthfield1.selectAll();
            }

        } else if (monthfield1.getText().equals("Jul")) {
            if (evt.getKeyCode() == KeyEvent.VK_DOWN) {
                monthfield1.setText("Jun");
                int yr = Integer.parseInt(yearfield1.getText());
                monthfield1.selectAll();

            }
            if (evt.getKeyCode() == KeyEvent.VK_UP) {
                monthfield1.setText("Aug");
                monthfield1.selectAll();
            }

        } else if (monthfield1.getText().equals("Aug")) {
            if (evt.getKeyCode() == KeyEvent.VK_DOWN) {
                monthfield1.setText("Jul");
                int yr = Integer.parseInt(yearfield1.getText());
                monthfield1.selectAll();

            }
            if (evt.getKeyCode() == KeyEvent.VK_UP) {
                monthfield1.setText("Sep");
                monthfield1.selectAll();
            }

        } else if (monthfield1.getText().equals("Sep")) {
            if (evt.getKeyCode() == KeyEvent.VK_DOWN) {
                monthfield1.setText("Aug");
                int yr = Integer.parseInt(yearfield1.getText());
                monthfield1.selectAll();

            }
            if (evt.getKeyCode() == KeyEvent.VK_UP) {
                monthfield1.setText("Oct");
                monthfield1.selectAll();
            }

        } else if (monthfield1.getText().equals("Oct")) {
            if (evt.getKeyCode() == KeyEvent.VK_DOWN) {
                monthfield1.setText("Sep");
                int yr = Integer.parseInt(yearfield1.getText());
                monthfield1.selectAll();

            }
            if (evt.getKeyCode() == KeyEvent.VK_UP) {
                monthfield1.setText("Nov");
                monthfield1.selectAll();
            }

        } else if (monthfield1.getText().equals("Nov")) {
            if (evt.getKeyCode() == KeyEvent.VK_DOWN) {
                monthfield1.setText("Oct");
                int yr = Integer.parseInt(yearfield1.getText());
                monthfield1.selectAll();

            }
            if (evt.getKeyCode() == KeyEvent.VK_UP) {
                monthfield1.setText("Dec");
                monthfield1.selectAll();
            }

        } else if (monthfield1.getText().equals("Dec")) {
            if (evt.getKeyCode() == KeyEvent.VK_DOWN) {
                monthfield1.setText("Nov");
                int yr = Integer.parseInt(yearfield1.getText());
                monthfield1.selectAll();

            }
            if (evt.getKeyCode() == KeyEvent.VK_UP) {
                monthfield1.setText("Jan");
                int yr = Integer.parseInt(yearfield1.getText());

                yearfield1.setText("" + (yr + 1));
                monthfield1.selectAll();
            }

        }
        if (evt.getKeyCode() == KeyEvent.VK_LEFT) {
            dayfield.requestFocus();
            dayfield.selectAll();
        }
        if (evt.getKeyCode() == KeyEvent.VK_RIGHT) {
            yearfield1.requestFocus();
            yearfield1.selectAll();
        }

        if (evt.getKeyCode() == KeyEvent.VK_ENTER) { ////// ChaNGE  focus on enter////////////////
            // recieptNo.requestFocus();

        }
    }//GEN-LAST:event_monthfield1KeyPressed

    private void yearfield1KeyPressed(java.awt.event.KeyEvent evt) {//GEN-FIRST:event_yearfield1KeyPressed
        if (evt.getKeyCode() == KeyEvent.VK_UP) {
            yearfield.setText("" + (Integer.parseInt(yearfield.getText()) + 1));
            yearfield.selectAll();
        }
        if (evt.getKeyCode() == KeyEvent.VK_DOWN) {
            yearfield.setText("" + (Integer.parseInt(yearfield.getText()) - 1));
            yearfield.selectAll();
        }
        if (evt.getKeyCode() == KeyEvent.VK_LEFT) {
            monthfield.requestFocus();
            monthfield.selectAll();
        }

        if (evt.getKeyCode() == KeyEvent.VK_ENTER) {
        }
    }//GEN-LAST:event_yearfield1KeyPressed

    private void dayfieldKeyPressed(java.awt.event.KeyEvent evt) {//GEN-FIRST:event_dayfieldKeyPressed
        ///////////////////////////////////////////////////  Days Decrement/////////////////////////////////////////////////////////////////////////////

        if (dayfield.getText().equals("1")) { // Jumping to 31 and 30 from 1st
            if (evt.getKeyCode() == KeyEvent.VK_DOWN) {

                if (monthfield.getText().equals("Feb") || monthfield.getText().equals("Apr")
                        || monthfield.getText().equals("Jun") || monthfield.getText().equals("Aug")
                        || monthfield.getText().equals("Sep") || monthfield.getText().equals("Nov")
                        || monthfield.getText().equals("Feb")) {
                    dayfield.setText("31");

                    int mnth = datechooser.return_index(monthfield.getText());
                    monthfield.setText(datechooser.Return_month(mnth - 1));

                } else if (monthfield.getText().equals("May") || monthfield.getText().equals("Jul")
                        || monthfield.getText().equals("Oct") || monthfield.getText().equals("Dec")) {
                    dayfield.setText("30");
                    int mnth = datechooser.return_index(monthfield.getText());
                    monthfield.setText(datechooser.Return_month(mnth - 1));

                } else if (monthfield.getText().equals("Mar")) { // from march 1st jump to 28th or 29th checking leap years
                    int yr = Integer.parseInt(yearfield.getText());
                    if (yr % 4 == 0) {
                        if (yr % 100 == 0) {
                            if (yr % 400 == 0) {
                                dayfield.setText("29"); // Leap Year
                            }
                        }
                        if (yr % 100 == 0) {
                            if (yr % 400 != 0) {
                                dayfield.setText("28"); // not a leap year
                            }
                        }
                        dayfield.setText("29"); // leap year

                    }
                    if (yr % 4 != 0) {
                        dayfield.setText("28"); // not a leap year
                    }
                    int mnth = datechooser.return_index(monthfield.getText());
                    monthfield.setText(datechooser.Return_month(mnth - 1));

                } else if (monthfield.getText().equals("Jan")) { // From jan 1st jump to december 31st decrementing year
                    dayfield.setText("31");

                    int yr = Integer.parseInt(yearfield.getText());
                    monthfield.setText("Dec");
                    yearfield.setText("" + (yr - 1)); // year
                }
                dayfield.selectAll();
            } // /// decrementing normal values
        } else if (dayfield.getText().equals("2") || dayfield.getText().equals("3")
                || dayfield.getText().equals("4") || dayfield.getText().equals("5")
                || dayfield.getText().equals("6") || dayfield.getText().equals("7")
                || dayfield.getText().equals("8") || dayfield.getText().equals("9")
                || dayfield.getText().equals("10") || dayfield.getText().equals("11")
                || dayfield.getText().equals("12") || dayfield.getText().equals("13")
                || dayfield.getText().equals("14") || dayfield.getText().equals("15")
                || dayfield.getText().equals("16") || dayfield.getText().equals("17")
                || dayfield.getText().equals("18") || dayfield.getText().equals("19")
                || dayfield.getText().equals("20") || dayfield.getText().equals("21")
                || dayfield.getText().equals("22") || dayfield.getText().equals("23")
                || dayfield.getText().equals("24") || dayfield.getText().equals("25")
                || dayfield.getText().equals("26") || dayfield.getText().equals("27")
                || dayfield.getText().equals("28") || dayfield.getText().equals("29")
                || dayfield.getText().equals("30") || dayfield.getText().equals("31")) {
            if (evt.getKeyCode() == KeyEvent.VK_DOWN) {

                dayfield.setText("" + (Integer.parseInt(dayfield.getText()) - 1));
                dayfield.selectAll();
            }
        }
        /////////////////////////////////////////////////  Days Increment///////////////////////////////////////////////////////////////////////////////////////////////////
        if (dayfield.getText().equals("30")) { // from 30th to 1st of next month
            if (evt.getKeyCode() == KeyEvent.VK_UP) {

                if (monthfield.getText().equals("Apr") || monthfield.getText().equals("Jun")
                        || monthfield.getText().equals("Sep") || monthfield.getText().equals("Nov")) {
                    dayfield.setText("0");

                    int mnth = datechooser.return_index(monthfield.getText());
                    monthfield.setText(datechooser.Return_month(mnth + 1));

                }
                dayfield.setText("" + (Integer.parseInt(dayfield.getText()) + 1));
                dayfield.selectAll();
            }

        } else if (dayfield.getText().equals("31")) { // from 31st to 1st of next month
            if (evt.getKeyCode() == KeyEvent.VK_UP) {

                if (monthfield.getText().equals("Jan") || monthfield.getText().equals("Mar")
                        || monthfield.getText().equals("May") || monthfield.getText().equals("Jul")
                        || monthfield.getText().equals("Aug") || monthfield.getText().equals("Oct")) {
                    dayfield.setText("1");

                    int mnth = datechooser.return_index(monthfield.getText());
                    monthfield.setText(datechooser.Return_month(mnth + 1));

                } else if (monthfield.getText().equals("Dec")) { // December to january incrementing the year

                    dayfield.setText("1");

                    int yr = Integer.parseInt(yearfield.getText());
                    monthfield.setText("Jan");
                    yearfield.setText("" + (yr + 1));
                }
                dayfield.selectAll();
            }

        } else if (monthfield.getText().equals("Feb")) { // for february
            if (evt.getKeyCode() == KeyEvent.VK_UP) {
                if (dayfield.getText().equals("28")) { // at 28 check for leap year
                    int yr = Integer.parseInt(yearfield.getText());
                    if (yr % 4 == 0) {
                        if (yr % 100 == 0) {
                            if (yr % 400 == 0) {
                                dayfield.setText("29"); // Leap Year       // increment to 29
                            }
                        }
                        if (yr % 100 == 0) {
                            if (yr % 400 != 0) {
                                dayfield.setText("1");
                                int mnth = datechooser.return_index(monthfield.getText());
                                monthfield.setText(datechooser.Return_month(mnth + 1));

                                // not a leap year                             // jump to next month
                            }
                        }
                        dayfield.setText("29"); // leap year             // increment to 29th

                    }
                    if (yr % 4 != 0) {
                        dayfield.setText("1");
                        int mnth = datechooser.return_index(monthfield.getText());
                        monthfield.setText(datechooser.Return_month(mnth + 1)); // not a leap year
                    }

                } else if (dayfield.getText().equals("29")) { // at 29 jump to next month normally
                    dayfield.setText("1");

                    int mnth = datechooser.return_index(monthfield.getText());
                    monthfield.setText(datechooser.Return_month(mnth + 1));
                    // incrementing normal values/////////////////////// for february separately
                } else if (dayfield.getText().equals("1") || dayfield.getText().equals("2")
                        || dayfield.getText().equals("3") || dayfield.getText().equals("4")
                        || dayfield.getText().equals("5") || dayfield.getText().equals("6")
                        || dayfield.getText().equals("7") || dayfield.getText().equals("8")
                        || dayfield.getText().equals("9") || dayfield.getText().equals("10")
                        || dayfield.getText().equals("11") || dayfield.getText().equals("12")
                        || dayfield.getText().equals("13") || dayfield.getText().equals("14")
                        || dayfield.getText().equals("15") || dayfield.getText().equals("16")
                        || dayfield.getText().equals("17") || dayfield.getText().equals("18")
                        || dayfield.getText().equals("19") || dayfield.getText().equals("20")
                        || dayfield.getText().equals("21") || dayfield.getText().equals("22")
                        || dayfield.getText().equals("23") || dayfield.getText().equals("24")
                        || dayfield.getText().equals("25") || dayfield.getText().equals("26")
                        || dayfield.getText().equals("27") || dayfield.getText().equals("28")
                        || dayfield.getText().equals("29") || dayfield.getText().equals("30")
                        || dayfield.getText().equals("31")) {

                    dayfield.setText("" + (Integer.parseInt(dayfield.getText()) + 1));

                }
                dayfield.selectAll();
            }
            // incrementing normal values
        } else if (dayfield.getText().equals("1") || dayfield.getText().equals("2")
                || dayfield.getText().equals("3") || dayfield.getText().equals("4")
                || dayfield.getText().equals("5") || dayfield.getText().equals("6")
                || dayfield.getText().equals("7") || dayfield.getText().equals("8")
                || dayfield.getText().equals("9") || dayfield.getText().equals("10")
                || dayfield.getText().equals("11") || dayfield.getText().equals("12")
                || dayfield.getText().equals("13") || dayfield.getText().equals("14")
                || dayfield.getText().equals("15") || dayfield.getText().equals("16")
                || dayfield.getText().equals("17") || dayfield.getText().equals("18")
                || dayfield.getText().equals("19") || dayfield.getText().equals("20")
                || dayfield.getText().equals("21") || dayfield.getText().equals("22")
                || dayfield.getText().equals("23") || dayfield.getText().equals("24")
                || dayfield.getText().equals("25") || dayfield.getText().equals("26")
                || dayfield.getText().equals("27") || dayfield.getText().equals("28")
                || dayfield.getText().equals("29") || dayfield.getText().equals("30")
                || dayfield.getText().equals("31")) {
            if (evt.getKeyCode() == KeyEvent.VK_UP) {

                dayfield.setText("" + (Integer.parseInt(dayfield.getText()) + 1));
                dayfield.selectAll();

            }
        }
        if (evt.getKeyCode() == KeyEvent.VK_RIGHT) {
            monthfield.requestFocus();
            monthfield.selectAll();
        }

        if (evt.getKeyCode() == KeyEvent.VK_ENTER) {
        }
    }//GEN-LAST:event_dayfieldKeyPressed

    private void datePicker2ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_datePicker2ActionPerformed
        java.sql.Date datef = new java.sql.Date(datePicker1.getDate().getTime());

        dayfield.setText("" + Integer.parseInt(datehandler.get_day(datef)));
        monthfield.setText(datehandler.get_month(datef));
        yearfield.setText(datehandler.get_year(datef));
    }//GEN-LAST:event_datePicker2ActionPerformed

    private void chkItemStateChanged(java.awt.event.ItemEvent evt) {//GEN-FIRST:event_chkItemStateChanged
        if (chk.isSelected()) {
            payment_date_panel.setEnabled(false);
        } else {
            payment_date_panel.setEnabled(true);
        }
    }//GEN-LAST:event_chkItemStateChanged

    private void payment_referrence_textFiieldActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_payment_referrence_textFiieldActionPerformed
        // TODO add your handling code here:
    }//GEN-LAST:event_payment_referrence_textFiieldActionPerformed

    Main_versioning mv = new Main_versioning();

    private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed
        try {

            DatabaseManager dbm = DatabaseManager.getDbCon();
            Date_Handler dt = new Date_Handler();

            String year = yearfield.getText();
            String month = dt.return_month_as_num(monthfield.getText());
            String employee_detail_file_location;

            if (mv.SoftwareVersion() == 1) {
                employee_detail_file_location = dbm.checknReturnData("file_locations", "id", "11", "location") + "/"
                        + year + month + ".xls";
                System.out.println(employee_detail_file_location);
                copyFileUsingApacheCommonsIO(
                        new File(dbm.checknReturnData("file_locations", "id", "10", "location")),
                        new File(employee_detail_file_location));
            } else if (mv.SoftwareVersion() == 2) {
                employee_detail_file_location = dbm.checknReturnData("file_locations", "id", "12", "location") + "/"
                        + year + month + ".xls";
                System.out.println(employee_detail_file_location);
                copyFileUsingApacheCommonsIO(
                        new File(dbm.checknReturnData("file_locations", "id", "11", "location")),
                        new File(employee_detail_file_location));
            } else {
                employee_detail_file_location = dbm.checknReturnData("file_locations", "id", "11", "location") + "/"
                        + year + month + ".xls";
                System.out.println(employee_detail_file_location);
                copyFileUsingApacheCommonsIO(
                        new File(dbm.checknReturnData("file_locations", "id", "10", "location")),
                        new File(employee_detail_file_location));
            }

            InputStream inp = new FileInputStream(employee_detail_file_location);

            Workbook wb = WorkbookFactory.create(inp);

            org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(0);

            String payment_date_year_month_date = null;

            String table_name = "pr_workdata_" + year + "_" + month;

            String epf_backup_year_month = year + "_" + month;

            String previous_table_name = null;

            if (Integer.parseInt(month) == 1) {
                previous_table_name = "pr_workdata_" + (Integer.parseInt(year) - 1) + "_" + 12;
            } else {
                if ((Integer.parseInt(month) - 1) < 10) {
                    previous_table_name = "pr_workdata_" + year + "_0" + (Integer.parseInt(month) - 1);
                } else {
                    previous_table_name = "pr_workdata_" + year + "_" + (Integer.parseInt(month) - 1);
                }
            }

            double employee_contribution_percentage = Math.round(Double.parseDouble(
                    dbm.checknReturnData("prcr_new_epf_details", "name", "employee_contribution", "value")) * 100.0)
                    / 100.0;
            double employer_contribution_percentage = Math.round(Double.parseDouble(
                    dbm.checknReturnData("prcr_new_epf_details", "name", "employer_contribution", "value")) * 100.0)
                    / 100.0;

            String nic = null;
            String surname = null;
            String initials = null;
            int member_no = 0;
            double tot_contribution = 0;
            double employers_contribution = 0;
            double member_contribution = 0;
            double tot_earnings = 0;
            String member_status = null;
            String zone = null;
            int employer_number = 0;
            int contribution_period = 0;
            int data_submission_no = 0;
            double no_of_days_worked = 0;
            int occupation_classification_grade = 0;

            int payment_mode = 0;
            int payment_date = 0;
            String payment_reference = null;
            int d_o_code = 0;

            member_status = "E";
            zone = dbm.checknReturnData("prcr_new_epf_details", "name", "zone", "value");
            employer_number = Integer
                    .parseInt(dbm.checknReturnData("prcr_new_epf_details", "name", "employer_number", "value"));
            contribution_period = Integer.parseInt(year + month);
            data_submission_no = 1;
            occupation_classification_grade = Integer.parseInt(dbm.checknReturnData("prcr_new_epf_details", "name",
                    "occupation_classification_grade", "value"));

            int normal_days = 0;
            int sundays = 0;

            double ot_before = 0;
            double ot_after = 0;
            double hours_as_decimal = 0;

            int count = 0;
            double total_member_contribution = 0;
            int need_both_reports = 1;
            if (chk.isSelected()) {
                need_both_reports = 0;
            } else {
                need_both_reports = 1;
            }

            ResultSet query = dbm.query("SELECT * FROM `" + table_name + "` WHERE `register_or_casual` = 1 "); // AND `total_pay` > 0");
            while (query.next()) {

                if (query.getDouble("total_pay") <= 0 && dbm.checkWhetherDataExistsTwoColumns("prcr_epf_etf_backup",
                        "month", epf_backup_year_month, "code", query.getInt("code")) != 1) {
                    continue;
                }
                ResultSet query1 = dbm
                        .query("SELECT * FROM `personal_info` WHERE `code` = '" + query.getInt("code") + "' ");
                while (query1.next()) {

                    nic = query1.getString("nic").replaceAll("\\s+", "");

                    surname = split_name(query1.getString("name"))[1];
                    initials = split_name(query1.getString("name"))[0];

                    member_no = Integer.parseInt(query1.getString("code"));
                    occupation_classification_grade = Integer.parseInt(query1.getString("occupation_grade"));
                    d_o_code = Integer
                            .parseInt(dbm.checknReturnData("prcr_new_epf_details", "name", "d_o_code", "value"));

                    tot_earnings = Math.round(query.getDouble("total_pay") * 100.0) / 100.0;

                    if (dbm.checkWhetherDataExistsTwoColumns("prcr_epf_etf_backup", "month", epf_backup_year_month,
                            "code", member_no) == 1) {
                        tot_earnings = tot_earnings
                                + Double.parseDouble(dbm.checknReturnDatafor2checks("prcr_epf_etf_backup", "month",
                                        epf_backup_year_month, "code", member_no, "total_pay"));
                    }

                    employers_contribution = Math.round(tot_earnings * employer_contribution_percentage * 100.0)
                            / 100.0;
                    member_contribution = Math.round(tot_earnings * employee_contribution_percentage * 100.0)
                            / 100.0;
                    tot_contribution = employers_contribution + member_contribution;
                    total_member_contribution = total_member_contribution + tot_contribution;

                    normal_days = query.getInt("normal_days");
                    sundays = query.getInt("sundays");
                    /* ot_before = query.getDouble("ot_before_hours");
                     ot_after = query.getDouble("ot_after_hours");
                     if ((ot_before + ot_after) > 0) {
                     hours_as_decimal = (ot_before + ot_after) / 100;
                     } else {
                     hours_as_decimal = 0;
                     }
                     if ((normal_days + sundays + hours_as_decimal) > 0) {
                     no_of_days_worked = Math.round((normal_days + sundays + hours_as_decimal) * 100.0) / 100.0;
                     } else {
                     no_of_days_worked = 0;
                     } */
                    no_of_days_worked = normal_days + sundays;

                    if (dbm.checkWhetherDataExists(previous_table_name, "code", query1.getString("code")) == 1) {
                        member_status = "E";
                    } else {
                        member_status = "N";
                    }

                    Row row = sheet.getRow(1 + count);
                    if (row == null) {
                        row = sheet.createRow(1 + count);
                    }

                    for (int k = 0; k < 16; k++) {

                        Cell cell = row.getCell(k);

                        switch (k) {
                        case 0:
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cell.setCellValue(nic);
                            break;
                        case 1:
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cell.setCellValue(surname);
                            break;
                        case 2:
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cell.setCellValue(initials);
                            break;
                        case 3:
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(member_no);
                            break;
                        case 4:
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(tot_contribution);
                            break;
                        case 5:
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(employers_contribution);
                            break;
                        case 6:
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(member_contribution);
                            break;
                        case 7:
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(tot_earnings);
                            break;
                        case 8:
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cell.setCellValue(member_status);
                            break;
                        case 9:
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cell.setCellValue(zone);
                            break;
                        case 10:
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(employer_number);
                            break;
                        case 11:
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(contribution_period);
                            break;
                        case 12:
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(data_submission_no);
                            break;
                        case 13:
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(no_of_days_worked);
                            break;
                        case 14:
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(occupation_classification_grade);
                            break;
                        case 15:
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(d_o_code);
                            break;
                        default:
                            break;
                        }

                    }
                    count++;
                }
                query1.close();
            }
            query.close();

            FileOutputStream fileOut = new FileOutputStream(employee_detail_file_location);
            wb.write(fileOut);
            fileOut.close();

            Desktop.getDesktop().open(new File(employee_detail_file_location));

        } catch (Exception ex) {
            System.out.println(ex);
            msg.showMessage(
                    "Problem Occured.Check whether the Excel file is alredy opened.Please close it and try again..",
                    "Error", "error");

        }
    }//GEN-LAST:event_jButton1ActionPerformed

    public void duplicateTable(String original_table_name, String table_copy_name) {
        DatabaseManager dbCon = DatabaseManager.getDbCon();
        try {
            if (dbCon.TableExistence(table_copy_name)) {
                dbCon.insert("DROP TABLE " + table_copy_name + "");
            }
            dbCon.insert("CREATE TABLE " + table_copy_name + " LIKE " + original_table_name + "");
            dbCon.insert("INSERT " + table_copy_name + " SELECT * FROM " + original_table_name + "");
        } catch (SQLException ex) {
            MessageBox.showMessage(ex.getMessage(), "SQL Error", "error");
        }
    }

    public String getST(String month, String year) {
        String st;
        if (datehandler.return_index(month) < 10) {
            st = year + "_0" + datehandler.return_index(month);
        } else {
            st = year + "_" + datehandler.return_index(month);
        }

        return st;
    }

    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JCheckBox chk;
    private com.michaelbaranov.microba.calendar.DatePicker datePicker1;
    private com.michaelbaranov.microba.calendar.DatePicker datePicker2;
    private javax.swing.JPanel datepanel;
    private javax.swing.JPanel datepanel1;
    private javax.swing.JTextField dayfield;
    private javax.swing.JButton jButton1;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JLabel jLabel3;
    private javax.swing.JLabel jLabel4;
    private javax.swing.JLabel jLabel5;
    private javax.swing.JLabel jLabel6;
    private javax.swing.JPanel jPanel2;
    private javax.swing.JTextField monthfield;
    private javax.swing.JTextField monthfield1;
    private javax.swing.JPanel payment_date_panel;
    private javax.swing.JComboBox payment_mode_combo;
    private javax.swing.JTextField payment_referrence_textFiield;
    private javax.swing.JButton view1;
    private javax.swing.JTextField yearfield;
    private javax.swing.JTextField yearfield1;
    // End of variables declaration//GEN-END:variables
}