Report_PRCR_New_ETF_Excel_File_Generator.java Source code

Java tutorial

Introduction

Here is the source code for Report_PRCR_New_ETF_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.openxml4j.opc.OPCPackage;
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;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/*
 * 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_ETF_Excel_File_Generator extends javax.swing.JPanel {

    /**
     *
     */
    public Report_PRCR_New_ETF_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();
        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("ETF Excel File Generator");

        jButton1.setText("Generate ETF File");
        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(55, Short.MAX_VALUE).addComponent(jLabel4)
                        .addGap(38, 38, 38))
                .addGroup(layout.createSequentialGroup().addContainerGap()
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                                .addComponent(jButton1, javax.swing.GroupLayout.DEFAULT_SIZE,
                                        javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                                .addComponent(jPanel2, 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,
                                javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                        .addGap(40, 40, 40)
                        .addComponent(jPanel2, javax.swing.GroupLayout.PREFERRED_SIZE,
                                javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, 84, Short.MAX_VALUE)
                        .addComponent(jButton1, javax.swing.GroupLayout.PREFERRED_SIZE, 40,
                                javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addGap(27, 27, 27)));
    }// </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 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;
    }

    MessageBox msg = new MessageBox();
    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", "13", "location") + "/"
                        + year + month + ".xls";
                System.out.println(employee_detail_file_location);
                copyFileUsingApacheCommonsIO(
                        new File(dbm.checknReturnData("file_locations", "id", "12", "location")),
                        new File(employee_detail_file_location));
            } else if (mv.SoftwareVersion() == 2) {
                employee_detail_file_location = dbm.checknReturnData("file_locations", "id", "14", "location") + "/"
                        + year + month + ".xls";
                System.out.println(employee_detail_file_location);
                copyFileUsingApacheCommonsIO(
                        new File(dbm.checknReturnData("file_locations", "id", "13", "location")),
                        new File(employee_detail_file_location));
            } else {
                employee_detail_file_location = dbm.checknReturnData("file_locations", "id", "13", "location") + "/"
                        + year + month + ".xls";
                System.out.println(employee_detail_file_location);
                copyFileUsingApacheCommonsIO(
                        new File(dbm.checknReturnData("file_locations", "id", "12", "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 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", "etf_rate", "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;
            }
            */
            int count = 0;
            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"));
                    //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;
                    //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;
                    member_contribution = Math.round(tot_earnings * employee_contribution_percentage * 100.0)
                            / 100.0;
                    //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 < 5; k++) {

                        Cell cell = row.getCell(k);

                        switch (k) {
                        case 0:
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(member_no);
                            break;
                        case 1:
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cell.setCellValue(initials);
                            break;
                        case 2:
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cell.setCellValue(surname);
                            break;
                        case 3:
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cell.setCellValue(nic);
                            break;
                        case 4:
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(member_contribution);
                            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 com.michaelbaranov.microba.calendar.DatePicker datePicker1;
    private javax.swing.JPanel datepanel;
    private javax.swing.JButton jButton1;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel4;
    private javax.swing.JPanel jPanel2;
    private javax.swing.JTextField monthfield;
    private javax.swing.JTextField yearfield;
    // End of variables declaration//GEN-END:variables
}