Java tutorial
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 }