package com.aims.dao;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.ResourceBundle;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import com.aims.common.service.ConnetionInfo;
import com.aims.common.util.ApplicationException;
import com.aims.common.util.Log;
import com.aims.common.util.UtilityBeanExcel;
public class Readingfinanceexceldata {
ResultSet rs = null;
Log log = new Log(Readingfinanceexceldata.class);
public static Connection getConnection()
{
System.out.println("Readingfinanceexceldata.getConnection()");
Connection con=null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
String station=ConnetionInfo.getInfo();
ResourceBundle bundle = ResourceBundle.getBundle("com.aims.resource.dbresource."+station);
String hostname=bundle.getString("hostname");
String portno=bundle.getString("portno");
String cstring=bundle.getString("connectionstring");
String username=bundle.getString("username");
String password=bundle.getString("password");
String url="jdbc:oracle:thin:@"+hostname+":"+portno+":"+cstring;
con=DriverManager.getConnection(url,username,password);
} catch(Exception e) {
System.out.println("the exception is"+e.toString());
}
return con;
}
/**
* @param args
* @throws ApplicationException
* @throws IOException
* @throws Exception
* @throws Exception
*/
public void verifyHeads(String file,String toMonth) throws Exception{
System.out.println("Readingfinanceexceldata.verifyHeads()");
Connection con =getConnection();
try {
System.out.println("file---verifyHeads-----"+file);
log.info("file---verifyHeads-----"+file);
Workbook workbook=Workbook.getWorkbook(new File(file));
Sheet sheet=workbook.getSheet(0);
Cell[] row = null;
Statement stmt=con.createStatement();
String headNames="";
for(int j=1; j<sheet.getColumns(); j++)
{
System.out.println("Columns j--------"+j);
for (int i = 0 ; i < 1 && !(sheet.getRow(i)[0].getContents()).equals(""); i++)
{
System.out.println("Row : i--------"+i);
row=sheet.getRow(i);
ResultSet rs=stmt.executeQuery("select tablename,earndeducd,TABLECOLUM from CONFIGURATIONEXCEL where EXCELCOLUMNAME='"+row[j].getContents()+"'") ;
if(!rs.next())
{
headNames=headNames+row[j].getContents()+", ";
}
if(rs!=null)
rs.close();
}
}
if(!headNames.equals(""))
throw new Exception("<b><font color=red> Salary Heads :</font></b> "+headNames+" Not Found In Masters");
}
catch (Exception e) {
throw new Exception(e.getMessage());
}
}
public void verifyEmployees(String file,String toMonth) throws Exception {
System.out.println("Readingfinanceexceldata.verifyEmployees()");
log.info("file---verifyEmployees(file)-----"+file);
Workbook workbook=Workbook.getWorkbook(new File(file));
Sheet sheet=workbook.getSheet(0);
Cell[] row = null;
Connection con =getConnection();
String empNames = "";
try {
log.info("DAOOOOOOOOOO");
Statement stmt=con.createStatement();
for(int j=1; j<=1; j++)
{
System.out.println("Columns j--------"+j);
for (int i = 1 ; i < sheet.getRows() && !(sheet.getRow(i)[0].getContents()).equals(""); i++)
{
System.out.println("Row : i--------"+i);
row=sheet.getRow(i);
ResultSet rs=stmt.executeQuery("select EMPLNUMBER from EMPLOYEEINFO where EMPLNUMBER='"+row[0].getContents()+"'") ;
if(!rs.next())
{
empNames=empNames+row[0].getContents()+", ";
}
if(rs!=null)
rs.close();
}
}
if (!empNames.equals(""))
throw new Exception("<b><font color=red > Employee Codes :</font></b> "+empNames+" Not Found In Masters");
} catch (Exception e) {
e.printStackTrace();
throw new Exception(e.getMessage());
}
}
public void verifyEmployeesAdjustments(String file,String toMonth) throws Exception {
System.out.println("Readingfinanceexceldata.verifyEmployeesAdjustments()");
log.info("file---verifyEmployeesAdjustments-----"+file);
Workbook workbook=Workbook.getWorkbook(new File(file));
Sheet sheet=workbook.getSheet(0);
Cell[] row = null;
Connection con =getConnection();
String adjustNames = "";
String headNames = "";
String str="";
try {
log.info("verifyEmployeesAdjustments DAO");
ArrayList earnigcodesList=new ArrayList();
Statement stmt=con.createStatement();
int l=0;
for(int j=1; j<sheet.getColumns(); j++)
{
System.out.println("Columns j--------"+j);
for (int i = 0 ; i < sheet.getRows() && !(sheet.getRow(i)[0].getContents()).equals(""); i++)
{
System.out.println("Row : i--------"+i);
row=sheet.getRow(i);
if(i==0)
{
ResultSet rs=stmt.executeQuery("select tablename,earndeducd,TABLECOLUM from CONFIGURATIONEXCEL where EXCELCOLUMNAME='"+row[j].getContents()+"'") ;
UtilityBeanExcel bean=null;
while(rs.next())
{
bean=new UtilityBeanExcel();
bean.setTableName(rs.getString("TABLENAME"));
bean.setTableColumn(rs.getString("TABLECOLUM"));
bean.setEarnedCd(rs.getString("EARNDEDUCD"));
bean.setExcelColumnName(row[j].getContents());
}
earnigcodesList.add(bean);
if(rs!=null)
rs.close();
}
}
}
if(stmt!=null)
stmt.close();
stmt=con.createStatement();
for(int j=1; j<sheet.getColumns(); j++)
{
System.out.println(" Columns : j--------"+j);
for (int i = 1 ; i < sheet.getRows() && !(sheet.getRow(i)[0].getContents()).equals(""); i++) {
UtilityBeanExcel bean=(UtilityBeanExcel)earnigcodesList.get(l);
System.out.println(" Rows : i-------"+i);
System.out.println(bean.getExcelColumnName());
row=sheet.getRow(i);
int existAdjRecCount = 0;
String ExistadjQuery="SELECT Nvl(Count(*), 0) FROM MONTHLY_SALADJ a, MONTHLY_SALADJ_DET b WHERE a.msaladjcd = b.msaladjcd AND a.payrollmonthid = "+toMonth+" and a.empno = (Select empno from EMPLOYEEINFO WHERE emplnumber='"+row[0].getContents()+"') and b.earndeducd = '"+bean.getEarnedCd().trim()+"' ";
System.out.println(" --> Qry :"+ExistadjQuery);
ResultSet rs2=stmt.executeQuery(ExistadjQuery);
if(rs2.next()) {
existAdjRecCount=rs2.getInt(1);
}
if(existAdjRecCount > 0) {
adjustNames = adjustNames +"<table border=0 width=300 align=center><tr><td align=left width=20%><font color=red>"+ row[0].getContents().trim()+"</font></td><td width=5% align=center>-</td><td align=left width=20%><font color=green>" +bean.getExcelColumnName().trim() +"</font></td></tr> ";
}
System.out.println("------existAdjRecCount :"+existAdjRecCount);
//System.out.println("------empNames :"+adjustNames);
if(rs2!=null)
rs2.close();
}
l++;
if(l==earnigcodesList.size()) {
l=0;
}
}
if (!adjustNames.equals(""))
throw new Exception("<b><table border=0 width=300 align=center><tr><td align=left width=20% nowrap><font color=red>Employee Codes</font></td><td width=5% align=center>-</td> <td align=left width=20%><td align=left nowrap width=20%><font color=green>Salary Heads </font></b></td></tr></table>"+adjustNames+"</table> Already having Adjustments for this Month");
} catch (Exception e) {
//e.printStackTrace();
throw new Exception(e.getMessage());
}
}
public void importAdjustmentExcelData(String file,String toMonth) throws Exception {
System.out.println("Readingfinanceexceldata.importAdjustmentExcelData()");
log.info("file---importAdjustmentExcelData-----"+file);
Workbook workbook=Workbook.getWorkbook(new File(file));
Sheet sheet=workbook.getSheet(0);
Cell[] row = null;
Connection con =getConnection();
try{
Statement stmt=con.createStatement();
FileOutputStream fos=new FileOutputStream("d:/output.txt");
ArrayList earnigcodesList=new ArrayList();
for(int j=1; j<sheet.getColumns(); j++)
{
System.out.println(" Columns : j--------"+j);
for (int i = 0 ; i < sheet.getRows() && !(sheet.getRow(i)[0].getContents()).equals(""); i++)
{
System.out.println(" Rows : i--------"+i);
row=sheet.getRow(i);
if(i==0)
{
ResultSet rs=stmt.executeQuery("select tablename,earndeducd,TABLECOLUM from CONFIGURATIONEXCEL where EXCELCOLUMNAME='"+row[j].getContents()+"'") ;
UtilityBeanExcel bean=null;
while(rs.next())
{
bean=new UtilityBeanExcel();
bean.setTableName(rs.getString("TABLENAME"));
bean.setTableColumn(rs.getString("TABLECOLUM"));
bean.setEarnedCd(rs.getString("EARNDEDUCD"));
bean.setExcelColumnName(row[j].getContents());
}
earnigcodesList.add(bean);
if(rs!=null)
rs.close();
}
}
}
if(stmt!=null)
stmt.close();
stmt=con.createStatement();
int l=0;
int count=0;
for(int j=1; j<sheet.getColumns(); j++)
{
String Insertquery2="";
System.out.println(" Columns : j--------"+j);
//System.out.println("J:"+j);
for(int i=1; i < sheet.getRows()&& !(sheet.getRow(i)[0].getContents()).equals(""); i++)
{
System.out.println(" Rows : i--------"+i);
row=sheet.getRow(i);
try{
UtilityBeanExcel bean=(UtilityBeanExcel)earnigcodesList.get(l);
System.out.println(bean.getExcelColumnName());
System.out.println("bean.getTableColumn()"+bean.getTableColumn());
System.out.println("bean.ddddd()"+bean.getExcelColumnName());
Insertquery2="Insert Into Monthly_Saladj_Det_Temp(MSALADJCD,EMPNO,PAYROLLMONTHID,EARNDEDUCD,EMPLNUMBER,"+bean.getTableColumn()+") Values((select MSALADJCD from monthly_saladj where EMPNO=(select empno from employeeinfo where emplnumber='"+row[0].getContents()+"' and PAYROLLMONTHID='"+toMonth+"')),( select EMPNO from EMPLOYEEINFO where EMPLNUMBER='"+row[0].getContents().trim()+"'),"+toMonth+",'"+bean.getEarnedCd()+"','"+row[0].getContents().trim()+"','"+ row[j].getContents()+"')";
System.out.println(j+"Insertquery2 - :"+Insertquery2);
count= stmt.executeUpdate(Insertquery2);
//int count=stmt.executeBatch();
if(count!=0)
con.commit();
}
catch(Exception e)
{
System.out.println("the exception is..."+e.toString());
e.printStackTrace();
//db.rollbackTrans();
System.out.println("the exception is.de9999999999999..");
}
}
l++;
if(l==earnigcodesList.size()) {
l=0;
}
}
String command = "{call modsalheadfromexcel(?)}"; // 1 Payroll Month ID: placeholders
CallableStatement cstmt = con.prepareCall (command);
cstmt.setInt(1, Integer.parseInt(toMonth));
cstmt.execute();
cstmt.close();
System.out.println("OVER-------");
}
catch(Exception e)
{
e.printStackTrace();
}
}
/*public static void main(String[] args) throws Exception {
Readingfinanceexceldata excel=new Readingfinanceexceldata();
try {
String fileName="D:/MAY.xls";
excel.verifyHeads(fileName);
excel.verifyEmployees(fileName);
excel.importAdjustmentExcelData(fileName);
}catch (Exception e) {
throw new Exception(e.getMessage());
}
}*/
}
No comments:
Post a Comment