package ibase.webitm.ejb.fin;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Map;
import javax.ejb.Stateless;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;


@Stateless
public class FixedAssetTransf extends ValidatorEJB implements FixedAssetTransfLocal,FixedAssetTransfRemote{

	public String wfValData(String xmlString, String xmlString1, String xmlString2,String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		System.out.println("------------ wfvalData method called-----------------");
		System.out.println("xtraParams --->>>  [["+xtraParams+"  ]]");
		System.out.println("xmlString1 --->>>  [["+xmlString1+"  ]]");
		System.out.println("xmlString2 --->>>  [["+xmlString2+"  ]]");
		System.out.println("editFlag --->>>  [["+editFlag+"  ]]");
		
		String errString = null;
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		try
		{
			dom2 = parseString(xmlString);
			dom1 = parseString(xmlString1);
			dom = parseString(xmlString2);
			errString = wfValData(dom,dom1,dom2,objContext,editFlag,xtraParams);
			
		}
		catch(Exception e)
		{
			System.out.println("Exception : FixedAssetTransf.java : wfValData(String xmlString) : ==>\n"+e.getMessage());
			throw new ITMException(e);
		}
		
		return errString;
	} //end of wfValData 
	
	
	public String wfValData(Document dom, Document dom1, Document dom2,String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	{		
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		int ctr=0;
		String childNodeName = null;
		String errString = "",errorType="",loginSite="",status="",invoiceID="",sreturnTranID="",sql1="",userId="";
		String errCode="";
		Connection conn = null;
		PreparedStatement pstmt;		
		ResultSet rs=null;
		int count=0,cnt=0;
		int currentFormNo=0,childNodeListLength;

		ConnDriver connDriver = new ConnDriver();
		//ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		ArrayList <String> errList = new ArrayList<String>();
		ArrayList <String>errFields = new ArrayList <String> ();
		GenericUtility genericUtility = GenericUtility.getInstance();
		StringBuffer errStringXml = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root><Errors>"); 
		 ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		try
		{					
			conn = connDriver.getConnectDB("DriverITM");
			
			userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginCode");
			System.out.println("user ID form XtraParam : "+userId +"Edit Flag -->>: "+editFlag);
			
			if(objContext != null && objContext.trim().length()>0)
			{
				currentFormNo = Integer.parseInt(objContext);
			}
			switch(currentFormNo)
			{
				case 1 :
				parentNodeList = dom.getElementsByTagName("Detail1");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();
				
				for(ctr = 0; ctr < childNodeListLength; ctr++)
				{
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();
					System.out.println("Child name --->> "+childNodeName); 				
					
					if (childNodeName.equalsIgnoreCase("site_code__to"))
					{
						String siteCodeTo="";	
						siteCodeTo = genericUtility.getColumnValue("site_code__to",dom);
						siteCodeTo=siteCodeTo == null ? "" :siteCodeTo.trim(); 
						System.out.println("siteCodeTo---->>["+siteCodeTo+"]");
						if(siteCodeTo.length() == 0){
						errCode = "VTSITETNN";//empty site code
						errList.add( errCode );
						errFields.add( childNodeName.toLowerCase() );
						}else{
						count=getDBRowCount(conn, "site", "site_code", siteCodeTo);						
						if(count ==0){
						errCode = "VTSITETND"; //Site code does not exist in master
						errList.add( errCode );
						errFields.add( childNodeName.toLowerCase() );
						}							
						}
					}
					if (childNodeName.equalsIgnoreCase("site_code__from"))
					{
						String siteCodeFrom="";	
						siteCodeFrom = genericUtility.getColumnValue("site_code__from",dom);
						siteCodeFrom=siteCodeFrom == null ? "" :siteCodeFrom.trim(); 
						System.out.println("siteCodeFrom---->>["+siteCodeFrom+"]");
						if(siteCodeFrom.length() == 0){
						errCode = "VTSITEFNN";//empty site code
						errList.add( errCode );
						errFields.add( childNodeName.toLowerCase() );
						}else{
						count=getDBRowCount(conn, "site", "site_code", siteCodeFrom);						
						if(count ==0){
						errCode = "VTSITEFND"; //Site code does not exist in master
						errList.add( errCode );
						errFields.add( childNodeName.toLowerCase() );
						}							
						}
					}
					 if(childNodeName.equalsIgnoreCase("link_type")){
						String linkType="";
						linkType = genericUtility.getColumnValue("link_type",dom);
						linkType=linkType==null ? "" :linkType.trim();
						System.out.println("linkType-->>["+linkType+"]");
						if(linkType.length() == 0){
							errCode = "VTLINKTN"; //Site code does not exist in master
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );	
						}
					}
				
				 } //end for loop 
				break;
				case 2 :
				parentNodeList = dom.getElementsByTagName("Detail2");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();
				for(ctr = 0; ctr < childNodeListLength; ctr++)
				{
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();					
					
					if(childNodeName.equalsIgnoreCase("asset_code__from"))
					{
						String assetCodeFrom="",xFrType="",siteCodeFrom="",SiteCode="",acctPrd="",prd="",
								tranDate="",sql="";
						assetCodeFrom=genericUtility.getColumnValue("asset_code__from", dom);
						siteCodeFrom=genericUtility.getColumnValue("site_code__from", dom);
						tranDate=genericUtility.getColumnValue("tran_date", dom);
						xFrType=genericUtility.getColumnValue("xfr_type", dom);
						assetCodeFrom=assetCodeFrom== null ? "" :assetCodeFrom.trim();
						siteCodeFrom=siteCodeFrom== null ? "" :siteCodeFrom.trim();
						tranDate=tranDate== null ? "" :tranDate.trim();
						xFrType=xFrType== null ? "" :xFrType.trim();
						System.out.println("assetCodeFrom-->>["+assetCodeFrom+"] tranDate-->["+tranDate+"]");
						System.out.println("xFrType-->>["+xFrType+"] siteCodeFrom--["+siteCodeFrom+"]");
						if(assetCodeFrom.length() == 0){
							errCode = "VTASSETCE"; //Old Asset Code can not be blank.
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );
						} else if ("T".equalsIgnoreCase(xFrType)){
							count=getDBRowCount(conn,"asset_register","asset_code",assetCodeFrom);
							System.out.println("asset_code count--->>["+count+"]");
							if(count== 0){
							errCode = "VTASSETCD"; // Entered Old Asset Code is not defined in asset register master.
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );
							}else{
								status=getNameOrDescrForCode(conn,"asset_register","status","asset_code",assetCodeFrom);
								SiteCode=getNameOrDescrForCode(conn,"asset_register","site_code","asset_code",assetCodeFrom);
								System.out.println("Status---->>["+status+"]");
								System.out.println("SiteCode---->>["+SiteCode+"]");
								if(!(SiteCode.equalsIgnoreCase(siteCodeFrom))){
									errCode = "VTASSTSITE"; 
									errList.add( errCode );
									errFields.add( childNodeName.toLowerCase() );	
								}
								else if(!("A".equalsIgnoreCase(status))){									
								errCode = "TTTTTT"; //VTASETSTAT
								errList.add( errCode );
								errFields.add( childNodeName.toLowerCase() );
								}else{
									//Cannot transfer the asset if Asset Depreciation is not calculated
									sql="select code from acctprd where ? between fr_date and to_date";
									pstmt=conn.prepareStatement(sql);
									pstmt.setString(1, tranDate);
									rs=pstmt.executeQuery();
									if(rs.next()){
										acctPrd=rs.getString(1);
									}
									if(rs!=null){
										rs.close();
										rs=null;
									}
									if(pstmt!=null){
										pstmt.close();
										pstmt=null;
									}
									System.out.println("acctPrd--->["+acctPrd+"]");
									sql="select code from period where ? between fr_date and to_date";
									pstmt=conn.prepareStatement(sql);
									pstmt.setString(1, tranDate);
									rs=pstmt.executeQuery();
									if(rs.next()){
										prd=rs.getString(1);
									}
									if(rs!=null){
										rs.close();
										rs=null;
									}
									if(pstmt!=null){
										pstmt.close();
										pstmt=null;
									}
									System.out.println("prd--->["+prd+"]");
									sql="select count(1) from asset_depr where asset_code = ? and acct_prd= ? and "
											+ "case when prd_code__upto is not null then prd_code__upto else ? end >= ?";
									pstmt=conn.prepareStatement(sql);
									pstmt.setString(1, assetCodeFrom);
									pstmt.setString(2, acctPrd);
									pstmt.setString(3, prd);
									pstmt.setString(4, prd);
									rs=pstmt.executeQuery();
									if(rs.next()){
										count=rs.getInt(1);
									}
									if(rs!=null){
										rs.close();
										rs=null;
									}
									if(pstmt!=null){
										pstmt.close();
										pstmt=null;
									}
									System.out.println("count--->["+count+"]");
									if(count == 0){
										errCode = "VTDEPRPROC"; //Asset Depreciation is not calculated for entered asset
										errList.add( errCode );
										errFields.add( childNodeName.toLowerCase() );		
									}
								}
							}
							
						}
						
					}	
					if(childNodeName.equalsIgnoreCase("asset_code__to"))
					{
						String assetCodeTo="";
						assetCodeTo=genericUtility.getColumnValue("asset_code__to", dom);
						assetCodeTo=assetCodeTo== null ? "" :assetCodeTo.trim();
						System.out.println("assetCodeFrom----->>["+assetCodeTo+"]");
						if(assetCodeTo.length() == 0){
							errCode = "VTASETNND"; //
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );
						}else{
							count=getDBRowCount(conn,"asset_register","asset_code",assetCodeTo);
							System.out.println("asset_code count--->>["+count+"]");
							if(count== 0){
							errCode = "VTASETNND"; //Asset Code is not present in Asset Register
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );
							}else{
								status=getNameOrDescrForCode(conn,"asset_register","status","asset_code",assetCodeTo);
								System.out.println("Status---->>["+status+"]");
								if(!("A".equalsIgnoreCase(status))){									
								errCode = "BBBBBB"; 
								errList.add( errCode );
								errFields.add( childNodeName.toLowerCase() ); 
								}
							}
							
						}
					}	
					
					if(childNodeName.equalsIgnoreCase("item_ser__to"))
					{
						count=0;
						String itemSerTo="";
						itemSerTo=genericUtility.getColumnValue("item_ser__to", dom);
						itemSerTo=itemSerTo== null ? "" :itemSerTo.trim();
						System.out.println("itemSerTo---->>["+itemSerTo+"]");
						if(itemSerTo.length() > 0){
							count=getDBRowCount(conn,"itemser","item_ser",itemSerTo);
							System.out.println("itemser count---->>["+count+"]");
							if(count==0){
								errCode = "VTITEMSER1"; //item series not present in master.
								errList.add( errCode );
								errFields.add( childNodeName.toLowerCase() ); 
							}						
						}					
					}
					if(childNodeName.equalsIgnoreCase("grp_code__to"))
					{
						count=0;
						String grpCodeTo="";
						grpCodeTo=genericUtility.getColumnValue("grp_code__to", dom);
						grpCodeTo=grpCodeTo== null ? "" :grpCodeTo.trim();
						System.out.println("grpCodeTo---->>["+grpCodeTo+"]");
						if(grpCodeTo.length() > 0){
							count=getDBRowCount(conn,"gencodes","fld_value",grpCodeTo);
							System.out.println("itemser count---->>["+count+"]");
							if(count==0){
								errCode = "VTPGRPCD1"; //grp_code not present in master.
								errList.add( errCode );
								errFields.add( childNodeName.toLowerCase() ); 
							}
						}						
					}
					if(childNodeName.equalsIgnoreCase("aloc_code__to"))
					{
						count=0;
						String alocCodeTo="",sql="",siteCodeTo="";
						alocCodeTo=genericUtility.getColumnValue("aloc_code__to", dom);
						siteCodeTo=genericUtility.getColumnValue("site_code__to", dom);
						alocCodeTo=alocCodeTo== null ? "" :alocCodeTo.trim();
						siteCodeTo=siteCodeTo== null ? "" :siteCodeTo.trim();
						System.out.println("grpCodeTo---->>["+alocCodeTo+"]");
						System.out.println("siteCodeTo---->>["+siteCodeTo+"]");
						if(alocCodeTo.length() > 0){
						sql="select count(*) from asset_location where aloc_code = ? and site_code = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1, alocCodeTo);
						pstmt.setString(2, siteCodeTo);
						
						rs=pstmt.executeQuery();
						if(rs.next()){
							count=rs.getInt(1);								
						}
						if(rs!=null){
							rs.close();
							rs=null;
						}
						if(pstmt!=null){
							pstmt.close();
							pstmt=null;
						}							
						System.out.println("itemser count---->>["+count+"]");
						if(count == 0){
						errCode = "VTALOC"; //The asset location is not valid..
						errList.add( errCode );
						errFields.add( childNodeName.toLowerCase() ); 
						}
						}
					}
					if(childNodeName.equalsIgnoreCase("depr_type__to"))
					{
						String deprTypeTo="";
						deprTypeTo=genericUtility.getColumnValue("depr_type__to", dom);
						deprTypeTo=deprTypeTo== null ? "" :deprTypeTo.trim();
						System.out.println("cctrCodeTo---->>["+deprTypeTo+"]");
						if(deprTypeTo.length() == 0){
							errCode = "VTDEPRTP"; //Select Depr Type 
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );	
						}						
					}
					if(childNodeName.equalsIgnoreCase("cctr_code__to"))
					{
						count=0;
						String cctrCodeTo="";
						cctrCodeTo=genericUtility.getColumnValue("cctr_code__to", dom);
						cctrCodeTo=cctrCodeTo== null ? "" :cctrCodeTo.trim();
						System.out.println("cctrCodeTo---->>["+cctrCodeTo+"]");
						if(cctrCodeTo.length() > 0){							
						count=getDBRowCount(conn,"costctr","cctr_code",cctrCodeTo);
						System.out.println("itemser count---->>["+count+"]");
						if(count==0){
						errCode = "VTCCTRCDPL"; //cctr code not present in master.
						errList.add( errCode );
						errFields.add( childNodeName.toLowerCase() ); 
						}
						}
					}	
					if(childNodeName.equalsIgnoreCase("asset_code__par_to"))
					{
						String assetCodeParTo="";
						assetCodeParTo=genericUtility.getColumnValue("asset_code__par_to", dom);
						assetCodeParTo=assetCodeParTo== null ? "" :assetCodeParTo.trim();
						System.out.println("assetCodeParTo---->>["+assetCodeParTo+"]");
						if(assetCodeParTo.length() > 0){
							count=getDBRowCount(conn,"asset_register","asset_code",assetCodeParTo);
							System.out.println("itemser count---->>["+count+"]");
							if(count==0){
							errCode = "VTASETPND"; //
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() ); 
							}	
						}

						
					}
				}
				break;
											
			} //end switch
			
			int errListSize = errList.size();
			cnt =0;
			String errFldName = null;
			if ( errList != null && errListSize > 0 )
			{
				for (cnt = 0; cnt < errListSize; cnt++ )
				{
					errCode = errList.get(cnt);
					errFldName = errFields.get(cnt);
					System.out.println("errCode .........."+errCode);
					errString = getErrorString( errFldName, errCode, userId );
					errorType =  errorType( conn, errCode );
					if ( errString.length() > 0)
					{
						String bifurErrString = errString.substring( errString.indexOf("<Errors>") + 8,errString.indexOf("<trace>"));
						bifurErrString =bifurErrString+errString.substring( errString.indexOf("</trace>") + 8,errString.indexOf("</Errors>"));
						errStringXml.append(bifurErrString);
						System.out.println("errStringXml .........."+errStringXml);
						errString = "";
					}
					if ( errorType.equalsIgnoreCase("E"))
					{
						break;
					}
				}
				errList.clear();
				errList = null;
				errFields.clear();
				errFields = null;
				
				errStringXml.append("</Errors></Root>\r\n");
			}
			else
			{
				errStringXml = new StringBuffer( "" );
			}	
			
		} //end try
		catch (SQLException se) {
			System.out.println("SQLException ::"+se);
			se.printStackTrace();
            throw new ITMException(se);
		}
		catch(Exception e)
		{
			System.out.println("Exception ::"+e);
			e.printStackTrace();
            throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(conn!=null)
				{
					conn.close();
					conn = null;
				}
				if(connDriver!=null){
					connDriver = null;
				}	
				if(rs !=null)
				{
					rs.close();
					rs=null;
				}
				
				
			}
			catch(Exception d)
			{
			  d.printStackTrace();
			}
		}
		
		System.out.println("ErrString ::[ "+errStringXml.toString()+" ]");
		return errStringXml.toString();
		
	
	}
	
	public String itemChanged(String xmlString,String xmlString1,String xmlString2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		System.out.println("------------------ itemChanged called------------------");
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String valueXmlString = "";
		try
		{   
			dom = parseString(xmlString);
			dom1 = parseString(xmlString1);
			dom2 = parseString(xmlString2);
			valueXmlString = itemChanged(dom,dom1,dom2,objContext,currentColumn,editFlag,xtraParams);
		}
		catch(Exception e)
		{
			System.out.println("Exception : [FixedAssetTransf ][itemChanged(String,String)] :==>\n"+e.getMessage());
			e.printStackTrace();
		}
		return valueXmlString;
	}
	
	public String itemChanged(Document dom, Document dom1,Document dom2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException,ITMException
	{	
		System.out.println("@@@@@@@ itemChanged called");
		StringBuffer valueXmlString = new StringBuffer();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		GenericUtility genericUtility = GenericUtility.getInstance();		
		ConnDriver connDriver = new ConnDriver();
		int currentFormNo =0;
		String columnValue = null;
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		int ctr = 0;
		String assetCodeFrom="",custName1="",sql="";		
		String itemSer="", grpCode="",itemCode="",siteCode="",alocCode="",assetType="",tranIdCreate="",
				tranSerCreate="", billNo="", suppCode="",currCode="",tranIdRem="",tranSerRem="",
				deprType="",deprMethod1="",deprMethod2="",empCodeVerify="", remarks="",assetCodeLink="", 
				empCode="",suppName="",suppNameNew="",acctPrdProc="",cctrCode="",assetCodePar="",itemDescr="";
		String rcpDateS="",instDateS="",remDateS="",useDateS="",verifiedOnS="",procDate1S="",procDate2S="",revalueDateS="";
		double exchRate=0, orginalValue=0, otherChgs=0, taxAmt=0,taxRecoAmt=0,diffAmtExch=0,
				revalueAmt=0,totalValue=0,deprPerc1=0,deprAmt1=0,deprAmt2=0,wdvAmt1=0, deprPerc2=0,
				wdvAmt2=0,remValue=0,exciseAmt=0, commChgs=0, installChgs=0, insurChgs=0,
				profitLoss=0,totValueBc=0,deprAmtRevl2=0,wdvAmtRevl2=0,octroiChgs=0;
		Timestamp rcpDate=null,instDate=null,remDate=null,useDate=null,verifiedOn=null,procDate1=null,procDate2=null,revalueDate=null;
		
		try
		{			
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver=null;
			if(objContext != null && objContext.trim().length()>0)
			{
				currentFormNo = Integer.parseInt(objContext.trim());
			}
			SimpleDateFormat simpleDateFormatObj = new SimpleDateFormat(genericUtility.getApplDateFormat());
			valueXmlString = new StringBuffer("<?xml version=\"1.0\"?><Root><header><editFlag>");
			valueXmlString.append(editFlag).append("</editFlag></header>");
			System.out.println("-------- currentFormNo : "+currentFormNo);
			
			switch(currentFormNo)
			{	
				case 1 :
				valueXmlString.append("<Detail1>");				
				System.out.println("[" + currentColumn + "] ==> '" + columnValue + "'");
				System.out.println("editFlag =>" +editFlag);
				
				if( currentColumn.trim().equalsIgnoreCase( "itm_default" ) )
				{
					System.out.println("------------in itm_default--------->");
					String confirm="N",loginSite="",loginSiteDescr="",xfrType1="T";
					 Calendar currentDate = Calendar.getInstance();					 
					 String tranDate = simpleDateFormatObj.format(currentDate.getTime());						 
					 valueXmlString.append("<tran_date>").append("<![CDATA[" + tranDate + "]]>").append("</tran_date>");
					 valueXmlString.append("<confirmed>").append("<![CDATA[" + confirm + "]]>").append("</confirmed>");
					 valueXmlString.append("<xfr_type>").append("<![CDATA[" + xfrType1 + "]]>").append("</xfr_type>");
					 
					 loginSite = getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");					
					 loginSite=loginSite==null ? "" :loginSite.trim(); 
					 System.out.println("loginSite ----->>["+loginSite+"]");	
					 loginSiteDescr=getNameOrDescrForCode(conn, "site", "descr", "site_code", loginSite);
					 valueXmlString.append("<site_code__from>").append("<![CDATA[" + loginSite + "]]>").append("</site_code__from>");
					 valueXmlString.append("<site_descr>").append("<![CDATA[" + loginSiteDescr + "]]>").append("</site_descr>");
					 
				}else if (currentColumn.trim().equals("site_code__to")){
					String siteCodeTo="",siteDescrTo="";
					siteCodeTo = genericUtility.getColumnValue("site_code__to",dom);
					System.out.println("");
					siteCodeTo=siteCodeTo==null ? "" :siteCodeTo.trim(); 
					 System.out.println("siteCodeTo----->>["+siteCodeTo+"]");	
					 siteDescrTo=getNameOrDescrForCode(conn, "site", "descr", "site_code", siteCodeTo);
					 if(siteCodeTo.length() > 0)
						 valueXmlString.append("<descr_1>").append("<![CDATA["+siteDescrTo+"]]>").append("</descr_1>");
					 else
						 valueXmlString.append("<descr_1>").append("<![CDATA[]]>").append("</descr_1>");
				}else if (currentColumn.trim().equals("site_code__from")){
					String siteCodeFrom="",siteDescrFrom="";
					siteCodeFrom = genericUtility.getColumnValue("site_code__from",dom);					
					siteCodeFrom=siteCodeFrom==null ? "" :siteCodeFrom.trim(); 
					 System.out.println("siteCodeFrom----->>["+siteCodeFrom+"]");	
					 siteDescrFrom=getNameOrDescrForCode(conn, "site", "descr", "site_code", siteCodeFrom);
					 if(siteCodeFrom.length() > 0)
						 valueXmlString.append("<site_descr>").append("<![CDATA["+siteDescrFrom+"]]>").append("</site_descr>");
					 else
						 valueXmlString.append("<site_descr>").append("<![CDATA[]]>").append("</site_descr>");
				}
				
				    valueXmlString.append("</Detail1>");
				  break;
				case 2:
					parentNodeList = dom.getElementsByTagName("Detail2");
					parentNode = parentNodeList.item(0);
					childNodeList = parentNode.getChildNodes();
					valueXmlString.append("<Detail2>");
					int childListLength = childNodeList.getLength();					
					if (currentColumn.trim().equals("asset_code__from"))
					{						
						assetCodeFrom = genericUtility.getColumnValue("asset_code__from",dom);
						assetCodeFrom=assetCodeFrom==null ? "" :assetCodeFrom.trim();
						System.out.println("assetCodeFrom ----->>["+assetCodeFrom+"]");
						
						sql="select	 item_ser, grp_code, item_code,site_code, aloc_code, asset_type," 
								+ "tran_id__create, tran_ser__create,rcp_date, bill_no, inst_date, supp_code,"
								+ "curr_code,exch_rate, orginal_value, other_chgs, tax_amt,tax_reco_amt,"
								+ "diff_amt__exch, revalue_amt, revalue_date,total_value, tran_id__rem, "
								+ "tran_ser__rem, rem_date,depr_type, depr_perc_1, depr_method_1, depr_amt_1,"
								+ "wdv_amt_1, depr_perc_2, depr_method_2, depr_amt_2,wdv_amt_2, verified_on, "
								+ "emp_code__verify, remarks,asset_code__link, emp_code, rem_value, use_date,"
								+ "excise_amt, comm_chgs, install_chgs, insur_chgs,profit_loss, supp_name,"
								+ "tot_value__bc, acct_prd__proc,cctr_code, asset_code__par, "
								+ "depr_amt_revl_2,wdv_amt_revl_2,octroi_chgs,proc_date_1,proc_date_2"
								+ " from asset_register where asset_code = ?";
						
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1, assetCodeFrom);
						rs=pstmt.executeQuery();
						if(rs.next()){		
							//item_ser, grp_code, item_code,site_code, aloc_code, asset_type, 6
							itemSer=rs.getString("item_ser") == null ? "" : rs.getString("item_ser").trim();
							grpCode=rs.getString("grp_code") == null ? "" : rs.getString("grp_code").trim();
							itemCode=rs.getString("item_code") == null ? "" : rs.getString("item_code").trim();
							siteCode=rs.getString("site_code") == null ? "" : rs.getString("site_code").trim();
							alocCode=rs.getString("aloc_code") == null ? "" : rs.getString("aloc_code").trim();
							assetType=rs.getString("asset_type") == null ? "" : rs.getString("asset_type").trim();						
							
							//tran_id__create, tran_ser__create,rcp_date, bill_no, inst_date, supp_code  6
							tranIdCreate=rs.getString("tran_id__create") == null ? "" : rs.getString("tran_id__create").trim();
							tranSerCreate=rs.getString("tran_ser__create") == null ? "" : rs.getString("tran_ser__create").trim();
							rcpDate=rs.getTimestamp("rcp_date");
							billNo=rs.getString("bill_no") == null ? "" : rs.getString("bill_no").trim();
							instDate=rs.getTimestamp("inst_date");
							suppCode=rs.getString("supp_code") == null ? "" : rs.getString("supp_code").trim();
							
							//curr_code,exch_rate, orginal_value, other_chgs, tax_amt,tax_reco_amt 6
							currCode=rs.getString("curr_code") == null ? "" : rs.getString("curr_code").trim();
							exchRate=rs.getDouble("exch_rate");
							orginalValue=rs.getDouble("orginal_value");
							otherChgs=rs.getDouble("other_chgs");
							taxAmt=rs.getDouble("tax_amt");
							taxRecoAmt=rs.getDouble("tax_reco_amt");
							
							//diff_amt__exch, revalue_amt, revalue_date,total_value, tran_id__rem 5							
							diffAmtExch=rs.getDouble("diff_amt__exch");
							revalueAmt=rs.getDouble("revalue_amt");
							revalueDate=rs.getTimestamp("revalue_date");
							totalValue=rs.getDouble("total_value");
							tranIdRem=rs.getString("tran_id__rem") == null ? "" : rs.getString("tran_id__rem").trim();
							
							//tran_ser__rem, rem_date,depr_type, depr_perc_1, depr_method_1, depr_amt_1  6
							tranSerRem=rs.getString("tran_ser__rem") == null ? "" : rs.getString("tran_ser__rem").trim();
							remDate=rs.getTimestamp("rem_date");
							deprType=rs.getString("depr_type") == null ? "" : rs.getString("depr_type").trim();
							deprPerc1=rs.getDouble("depr_perc_1");
							deprMethod1=rs.getString("depr_method_1") == null ? "" : rs.getString("depr_method_1").trim();
							deprAmt1=rs.getDouble("depr_amt_1");
							
							//wdv_amt_1, depr_perc_2, depr_method_2, depr_amt_2,wdv_amt_2, verified_on  6							
							wdvAmt1=rs.getDouble("wdv_amt_1");
							deprPerc2=rs.getDouble("depr_perc_2");
							deprMethod2=rs.getString("depr_method_2") == null ? "" : rs.getString("depr_method_2").trim();
							deprAmt2=rs.getDouble("depr_amt_2");
							wdvAmt2=rs.getDouble("wdv_amt_2");
							verifiedOn=rs.getTimestamp("verified_on");
							
							//emp_code__verify, remarks,asset_code__link, emp_code, rem_value, use_date 6
							empCodeVerify=rs.getString("emp_code__verify") == null ? "" : rs.getString("emp_code__verify").trim();
							remarks=rs.getString("remarks") == null ? "" : rs.getString("remarks");
							assetCodeLink=rs.getString("asset_code__link") == null ? "" : rs.getString("asset_code__link").trim();
							empCode=rs.getString("emp_code") == null ? "" : rs.getString("emp_code").trim();
							remValue=rs.getDouble("rem_value");
							useDate=rs.getTimestamp("use_date");							
							
							//excise_amt, comm_chgs, install_chgs, insur_chgs,profit_loss, supp_name  6
							exciseAmt=rs.getDouble("excise_amt");
							commChgs=rs.getDouble("comm_chgs");
							installChgs=rs.getDouble("install_chgs");
							insurChgs=rs.getDouble("insur_chgs");
							profitLoss=rs.getDouble("profit_loss");
							suppName=rs.getString("supp_name") == null ? "" : rs.getString("supp_name").trim();
							
							//tot_value__bc, acct_prd__proc,cctr_code, asset_code__par 4
							totValueBc=rs.getDouble("tot_value__bc");
							acctPrdProc=rs.getString("acct_prd__proc") == null ? "" : rs.getString("acct_prd__proc").trim();
							cctrCode=rs.getString("cctr_code") == null ? "" : rs.getString("cctr_code").trim();
							assetCodePar=rs.getString("asset_code__par") == null ? "" : rs.getString("asset_code__par").trim();
							//depr_amt_revl_2,wdv_amt_revl_2,octroi_chgs,proc_date_1,proc_date_2  5
							deprAmtRevl2=rs.getDouble("depr_amt_revl_2");
							wdvAmtRevl2=rs.getDouble("wdv_amt_revl_2"); 
							octroiChgs=rs.getDouble("octroi_chgs");
							procDate1=rs.getTimestamp("proc_date_1");	
							procDate2=rs.getTimestamp("proc_date_2");				
							
							
							//Formatting Dates
							useDateS=useDate==null ? "" : simpleDateFormatObj.format(useDate);
							rcpDateS=rcpDate==null ? "" : simpleDateFormatObj.format(rcpDate);
							instDateS=instDate==null ? "" : simpleDateFormatObj.format(instDate);							
							revalueDateS=revalueDate==null ? "" : simpleDateFormatObj.format(revalueDate);
							remDateS=remDate==null ? "" : simpleDateFormatObj.format(remDate);
							verifiedOnS=verifiedOn==null ? "" : simpleDateFormatObj.format(verifiedOn);
							procDate1S=procDate1==null ? "" : simpleDateFormatObj.format(procDate1);
							procDate2S=procDate2==null ? "" : simpleDateFormatObj.format(procDate2);						
							
						}
						valueXmlString.append("<depr_type__to protect = \"0\">").append("<![CDATA[]]>").append("</depr_type__to>");
						//Basic
						valueXmlString.append("<asset_type>").append("<![CDATA[" + assetType + "]]>").append("</asset_type>");
						valueXmlString.append("<item_ser__from>").append("<![CDATA[" + itemSer + "]]>").append("</item_ser__from>");
						valueXmlString.append("<grp_code__from>").append("<![CDATA[" + grpCode + "]]>").append("</grp_code__from>");
						valueXmlString.append("<aloc_code__from>").append("<![CDATA[" + alocCode + "]]>").append("</aloc_code__from>");
						valueXmlString.append("<asset_code__par_from>").append("<![CDATA[" + assetCodePar + "]]>").append("</asset_code__par_from>");
						valueXmlString.append("<depr_type__from>").append("<![CDATA[" + deprType + "]]>").append("</depr_type__from>");
						valueXmlString.append("<cctr_code__from>").append("<![CDATA[" + cctrCode + "]]>").append("</cctr_code__from>");
						
						
						//Others   
						valueXmlString.append("<item_code>").append("<![CDATA[" + itemCode + "]]>").append("</item_code>");
						if(itemCode.length() > 0)
							itemDescr=getNameOrDescrForCode(conn,"item","descr","item_code",itemCode);
						else
							itemDescr="";
						System.out.println("ItemDescr---->>["+itemDescr+"]");
						valueXmlString.append("<descr_1>").append("<![CDATA[" + itemCode + "]]>").append("</descr_1>");
						valueXmlString.append("<tran_id__create>").append("<![CDATA[" + tranIdCreate + "]]>").append("</tran_id__create>");
						valueXmlString.append("<tran_ser__create>").append("<![CDATA[" + tranSerCreate + "]]>").append("</tran_ser__create>");						
						valueXmlString.append("<bill_no>").append("<![CDATA[" + billNo + "]]>").append("</bill_no>");
						valueXmlString.append("<supp_code>").append("<![CDATA[" + suppCode + "]]>").append("</supp_code>");
						
						if(suppName.length() == 0 && suppCode.length() > 0 ){
							suppNameNew=getNameOrDescrForCode(conn,"supplier","descr","supp_code",suppCode);
							valueXmlString.append("<supp_name>").append("<![CDATA[" + suppNameNew + "]]>").append("</supp_name>");
						}else{
							valueXmlString.append("<supp_name>").append("<![CDATA[" + suppName + "]]>").append("</supp_name>");
						}
						
						
						valueXmlString.append("<rcp_date>").append("<![CDATA[" +rcpDateS+ "]]>").append("</rcp_date>");
						valueXmlString.append("<revalue_date>").append("<![CDATA[" +revalueDateS+ "]]>").append("</revalue_date>");
						valueXmlString.append("<inst_date>").append("<![CDATA[" + instDateS + "]]>").append("</inst_date>");
						valueXmlString.append("<curr_code>").append("<![CDATA[" + currCode + "]]>").append("</curr_code>");
						valueXmlString.append("<exch_rate>").append("<![CDATA[" + (exchRate > 0 ? exchRate : "") + "]]>").append("</exch_rate>");
						
						valueXmlString.append("<use_date>").append("<![CDATA[" + useDateS + "]]>").append("</use_date>");
						valueXmlString.append("<orginal_value>").append("<![CDATA[" +(orginalValue > 0 ? orginalValue : "")+ "]]>").append("</orginal_value>");
						valueXmlString.append("<total_value>").append("<![CDATA[" + (totalValue > 0 ? totalValue : "") + "]]>").append("</total_value>");
						valueXmlString.append("<tax_amt>").append("<![CDATA[" + (taxAmt > 0 ? taxAmt : "") + "]]>").append("</tax_amt>");
						valueXmlString.append("<tax_reco_amt>").append("<![CDATA[" + (taxRecoAmt > 0 ? taxRecoAmt : "") + "]]>").append("</tax_reco_amt>");
						valueXmlString.append("<diff_amt__exch>").append("<![CDATA[" + (diffAmtExch > 0 ? diffAmtExch : "") + "]]>").append("</diff_amt__exch>");						
						valueXmlString.append("<revalue_amt>").append("<![CDATA[" + (revalueAmt > 0 ? revalueAmt : "") + "]]>").append("</revalue_amt>");
						valueXmlString.append("<excise_amt>").append("<![CDATA[" + (exciseAmt > 0 ? exciseAmt : "") + "]]>").append("</excise_amt>");
						
						valueXmlString.append("<insur_chgs>").append("<![CDATA[" + (insurChgs > 0 ? insurChgs : "") + "]]>").append("</insur_chgs>");
						valueXmlString.append("<other_chgs>").append("<![CDATA[" + (otherChgs > 0 ? otherChgs : "") + "]]>").append("</other_chgs>");
						valueXmlString.append("<octroi_chgs>").append("<![CDATA[" + (octroiChgs > 0 ? octroiChgs : "") + "]]>").append("</octroi_chgs>");
						
						valueXmlString.append("<install_chgs>").append("<![CDATA[" + (installChgs > 0 ? installChgs : "") + "]]>").append("</install_chgs>");
						valueXmlString.append("<comm_chgs>").append("<![CDATA[" + (commChgs > 0 ? commChgs : "") + "]]>").append("</comm_chgs>");						

						//Depreciation 14
						valueXmlString.append("<depr_method_1>").append("<![CDATA[" + deprMethod1 + "]]>").append("</depr_method_1>");
						valueXmlString.append("<depr_amt_1>").append("<![CDATA[" + (deprAmt1 > 0 ? deprAmt1 : "") + "]]>").append("</depr_amt_1>");
						valueXmlString.append("<depr_perc_1>").append("<![CDATA[" + (deprPerc1 > 0 ? deprPerc1 : "") + "]]>").append("</depr_perc_1>");						
						valueXmlString.append("<depr_method_2>").append("<![CDATA[" +deprMethod2 + "]]>").append("</depr_method_2>");
						valueXmlString.append("<depr_amt_2>").append("<![CDATA[" +(deprAmt2 > 0 ? deprAmt2 : "") + "]]>").append("</depr_amt_2>");
						valueXmlString.append("<depr_perc_2>").append("<![CDATA[" + (deprPerc2 > 0 ? deprPerc2 : "") + "]]>").append("</depr_perc_2>");						
						valueXmlString.append("<wdv_amt_1>").append("<![CDATA[" + (wdvAmt1 > 0 ? wdvAmt1 : "") + "]]>").append("</wdv_amt_1>");
						valueXmlString.append("<wdv_amt_2>").append("<![CDATA[" + (wdvAmt2 > 0 ? wdvAmt2 : "") + "]]>").append("</wdv_amt_2>");
						valueXmlString.append("<depr_amt_revl_2>").append("<![CDATA[" + (deprAmtRevl2 > 0 ? deprAmtRevl2 : "") + "]]>").append("</depr_amt_revl_2>");
						valueXmlString.append("<wdv_amt_revl_2>").append("<![CDATA[" + (wdvAmtRevl2 > 0 ? wdvAmtRevl2 : "")+ "]]>").append("</wdv_amt_revl_2>");						
						valueXmlString.append("<profit_loss>").append("<![CDATA[" +  (profitLoss > 0 ? profitLoss : "")+ "]]>").append("</profit_loss>");
						valueXmlString.append("<acct_prd__proc>").append("<![CDATA[" + acctPrdProc+ "]]>").append("</acct_prd__proc>");						
						valueXmlString.append("<asset_code__link>").append("<![CDATA[" + assetCodeLink + "]]>").append("</asset_code__link>");
						valueXmlString.append("<total_value__bc>").append("<![CDATA[" + (totValueBc > 0 ? totValueBc : "") + "]]>").append("</total_value__bc>");	
						
						//Verification 5
						valueXmlString.append("<proc_date_1>").append("<![CDATA[" + procDate1S + "]]>").append("</proc_date_1>");
						valueXmlString.append("<emp_code__verify>").append("<![CDATA[" + assetCodeLink + "]]>").append("</emp_code__verify>");
						valueXmlString.append("<proc_date_2>").append("<![CDATA[" + procDate2S + "]]>").append("</proc_date_2>");
						valueXmlString.append("<verified_on>").append("<![CDATA[" + verifiedOnS + "]]>").append("</verified_on>");
						valueXmlString.append("<remarks>").append("<![CDATA[" + remarks + "]]>").append("</remarks>");						
					
					}else if (currentColumn.trim().equals("item_ser__to")){
						String itemSerDescr="",itemSerTo="";
						itemSerTo = genericUtility.getColumnValue("item_ser__to",dom);
						itemSerTo=itemSerTo==null ? "" : itemSerTo.trim();
						System.out.println("itemSerTo--->["+itemSerTo+"]");
						if(itemSerTo.length() > 0){
							itemSerDescr=getNameOrDescrForCode(conn,"itemser","descr","item_ser",itemSerTo);
							valueXmlString.append("<descr>").append("<![CDATA["+itemSerDescr+"]]>").append("</descr>");
						}else{
							valueXmlString.append("<descr>").append("<![CDATA[]]>").append("</descr>");
						}						
					}
					
					
					
					valueXmlString.append("</Detail2>");
				}// end switch 
			valueXmlString.append("</Root>");			
			
			
		} //end try-------------------------------------------------------------
		catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception ::"+e.getMessage());			
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(conn!=null)
				{
					conn.close();
					conn = null;
				}
				if(rs != null)
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}				
			}
			catch(Exception d)
			{
			  d.printStackTrace();
			}			
		}
		
		return valueXmlString.toString();
	
	}
	private String errorType( Connection conn , String errorCode )
	{
		String msgType = "";
		PreparedStatement pstmt = null ; 
		ResultSet rs = null;
		try
		{			
			String  sql = " SELECT MSG_TYPE FROM MESSAGES WHERE MSG_NO =   ? ";
			
			pstmt = conn.prepareStatement( sql );			
			pstmt.setString(1, checkNull(errorCode));			
			rs = pstmt.executeQuery();
			while( rs.next() )
			{
				msgType = rs.getString("MSG_TYPE");
			}			
		}
		catch (Exception ex)
		{
			ex.printStackTrace();
		}		
		finally
		{
			try
			{
				if ( rs != null )
				{
					rs.close();
					rs = null;
				}
				if ( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch ( Exception e )
			{
				e.printStackTrace();
			}
		}		
		return msgType;
	}
	public String checkNull(String input)	
	{
		if (input == null)
		{
			input="";
		}
		return input.trim();
	}
	protected String getNameOrDescrForCode(Connection conn, String table_name, String descr_col_name,String whrCondCol, String whrCondVal)
	{
			String descr = null;
			
			if(conn!=null){
				
				ResultSet rs=null;
				PreparedStatement pstmt = null;
				
				String sql="SELECT "+descr_col_name+" FROM "+table_name+" WHERE "+whrCondCol+" = ?";
				
				System.out.println("SQL in getNameOrDescrForCode method : "+sql);
				try
				{
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,whrCondVal);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						descr = rs.getString(descr_col_name);
					}
					
					descr=descr==null ?"" :descr;
				}
				catch(SQLException e)
				{
					System.out.println("SQL Exception In getNameOrDescrForCode method of FixedAssetTransf Class : "+e.getMessage());
					e.printStackTrace();
				}
				catch(Exception ex)
				{
					System.out.println("Exception In getNameOrDescrForCode method of FixedAssetTransf Class : "+ex.getMessage());
					ex.printStackTrace();
				}finally{
					
					try{
						
						if(pstmt!=null){
							pstmt.close();
							pstmt = null;
						}
						if(rs!=null){
							rs.close();
							rs = null;
						}
					}catch (SQLException se) {
						se.printStackTrace();
					}
				}
			}else{
				try {
					throw new SQLException("Connection passed to FixedAssetTransf.getNameOrDescrForCode() method is null");
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
				
		return descr;
	}
	protected int getDBRowCount(Connection conn, String table_name, String whrCondCol, String whrCondVal)
	{
			int count=-1;
			
			if(conn!=null){
				
				ResultSet rs=null;
				PreparedStatement pstmt = null;
				
				String sql="select count(1) from "+table_name+" where "+whrCondCol+" = ?";
				System.out.println("SQL in getDBRowCount method : "+sql);
				try
				{
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,whrCondVal);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						count = rs.getInt(1);
					}
				}
				catch(SQLException e)
				{
					System.out.println("SQL Exception In getDBRowCount method of FixedAssetTransf Class : "+e.getMessage());
					e.printStackTrace();
				}
				catch(Exception ex)
				{
					System.out.println("Exception In getDBRowCount method of FixedAssetTransf Class : "+ex.getMessage());
					ex.printStackTrace();
				}finally{
					
					try{
						
						if(pstmt!=null){
							pstmt.close();
							pstmt = null;
						}
						if(rs!=null){
							rs.close();
							rs = null;
						}
					}catch (SQLException se) {
						se.printStackTrace();
					}
				}
			}else{
				try {
					throw new SQLException("Connection passed to FixedAssetTransf.getDBRowCount() method is null");
				} catch (SQLException e) {
					e.printStackTrace();					
				}
			}
				
		return count;
	}
}
