

import java.io.File;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jcifs.smb.NtlmPasswordAuthentication;
import jcifs.smb.SmbFile;


import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;




public class COA {
	private static SessionFactory factory; 
	String login_user_int_code="0";
	
	
	
	
	public static void main(String args[])
	{
		GetCOAFileNamesFromFTPServer();
	}
	public static String GetCOAFileNamesFromFTPServer()
	{
		
		String msg=""; 
		String config_file_s=System.getProperty("user.dir")+"/hibernate.cfg.xml";
		File file = new File(config_file_s);
		Configuration configuration = new Configuration().configure(file);
	   factory = configuration.buildSessionFactory();
		Session session = factory.openSession();
		
		
		
		try 
		{
			String domain="LAPA";
			
			List<Map<String,Object>>  jsn_obj=GetFTPServerDetails();
			String username=jsn_obj.get(0).get("NWLID").toString().trim();
			String password=jsn_obj.get(0).get("NWPWD").toString().trim();
			String path=jsn_obj.get(0).get("NWPATH").toString().trim();

			NtlmPasswordAuthentication auth = new NtlmPasswordAuthentication(domain, username, password); 
			
	
			SmbFile f1 = new SmbFile( "smb:"+path+"/", auth);  
			String sql="  ";
			String skuCode="";
			String batchId="";
			 String fileName="";
			 int loopCounter=0;
			for (SmbFile f : f1.listFiles())
			 {
				loopCounter++;
				if(loopCounter==1)
				{
					TruncateCOAErrorData(session);
					TruncateCOAFileData(session);
				}
				    fileName=f.getName();
				    String altertedFileName=fileName.replace("-", "_");
				    skuCode="";
					batchId="";
				   if(fileName.contains("_"))
				   {
					   System.out.println("fileName...."+fileName);
					   try {
							   	String[] batchIdArr=altertedFileName.split("_");
							   	skuCode=batchIdArr[1].toString();
							   	if(skuCode.length()>=10)
							   	{
								    for(int i=2;i<=batchIdArr.length-1;i++)
									{		
								    	batchId=batchIdArr[i].replace(".pdf", "").replace(".xls", "");
										if(!skuCode.isEmpty() && !batchId.isEmpty())
										{
											 ExecuteBatchQqry(session,fileName.replace("'", "''"),skuCode,batchId);																			   										   
										}
										
									}
							   	}
							   	else
								{
									 InsertCOAErrorDetails(session,fileName,"SKU Code is lesser than 10 chars.");
								}
							    
						} catch (Exception e) {
							//e.get;
						}
				   }
				   else
				   {
					   InsertCOAErrorDetails(session,fileName,"Does not contain underscore.");
				   }
				   
				
				    
				     
			 }
			
		
			
			
		}
		catch (Exception e) 
		{
			e.printStackTrace();
			msg=e.getMessage();
		}
		finally
		{
			session.close();
			factory.close();
		}
		return msg;
	}
	
	public static void TruncateCOAErrorData(Session session) 
	{	
			try {
				String sql=" truncate table coa_error_data ";
			
				Transaction tx=session.beginTransaction();
				System.out.println(sql);
				Query q = session.createSQLQuery(sql);
				q.executeUpdate(); 
				tx.commit();
			} catch (HibernateException e) {
				e.getMessage();
			} 
		
	}
	
	public static void TruncateCOAFileData(Session session) 
	{	
			try {
				String sql=" truncate table coa_file_data ";
			
				Transaction tx=session.beginTransaction();
				System.out.println(sql);
				Query q = session.createSQLQuery(sql);
				q.executeUpdate(); 
				tx.commit();
			} catch (HibernateException e) {
				e.getMessage();
			} 
		
	}
	
	
	
	public static void InsertCOAErrorDetails(Session session,String fileName,String reason) 
	{
		
			try {
				
				String sql=" insert into coa_error_data (file_name,reason,created_date) values ('"+fileName+"','"+reason+"',dateadd(MINUTE, 30, dateadd(HOUR, 10, getdate()))) ";
			
				Transaction tx=session.beginTransaction();
				System.out.println(sql);
				Query q = session.createSQLQuery(sql);
				q.executeUpdate(); 
				tx.commit();
			} catch (HibernateException e) {
				// TODO Auto-generated catch block
				e.getMessage();
			} 
		
	}
	
	
	public static void ExecuteBatchQqry(Session session,String fileName,String skuCode,String batchId) 
	{
		
			try {
				
				String sql=" insert into coa_file_data (file_name,sku_code,batch_code,created_date) values ('"+fileName+"','"+skuCode+"','"+batchId+"',dateadd(MINUTE, 30, dateadd(HOUR, 10, getdate()))) ";
			
				Transaction tx=session.beginTransaction();
				System.out.println(sql);
				Query q = session.createSQLQuery(sql);
				q.executeUpdate(); 
				tx.commit();
			} catch (HibernateException e) {
				// TODO Auto-generated catch block
				e.getMessage();
			} 
		
	}
	
	
	
	
	public static List<Map<String,Object>> GetFTPServerDetails() throws Exception 
	{
		 
		List<Map<String,Object>> query_data_obj= new ArrayList<Map<String,Object>>();
		ResultSet rs1 = null;
		
		CallableStatement createCall = null;
		JDBCAccessor obj=new JDBCAccessor();
		Connection conn=obj.getJTConnection("","","");
		 
		try {
			createCall = conn.prepareCall("call BPCSLCLIN.CMW010S('COAINPC','EDR','COAINATTH')");
		
			createCall.execute();
			 
			rs1 = createCall.getResultSet();
		 
			query_data_obj=convertToJSON(rs1);
			
			System.out.println("query_data_obj..."+query_data_obj);
			 
		}
		catch (SQLException e) {
			System.out.println("Error -"+e.getMessage());
			 
		

		}finally {
			 
			if (rs1 != null) 
				rs1.close();
			 conn.close();
		}
		return query_data_obj;  
	}
	
	public static List<Map<String,Object>> convertToJSON(ResultSet resultSet) throws Exception 
	{
		List<Map<String,Object>> query_data_obj= new ArrayList<Map<String,Object>>();
		 
		int k=0;
		while (resultSet.next()) 
		{
			int col_cnt = resultSet.getMetaData().getColumnCount();
		 

			 Map<String, Object> map = new HashMap<String, Object>();
			for (int i = 0; i < col_cnt; i++) 
			{
				map.put(resultSet.getMetaData().getColumnLabel(i + 1), GetObjectValueFromMap(resultSet.getObject(i + 1)));
			}

			query_data_obj.add(map);
			k++;
		}
		return   query_data_obj;
	}
	
	public static String GetObjectValueFromMap(Object msg_obj)
	{
		 
		 String msg_str="";
		 if(msg_obj!=null)
			 msg_str=msg_obj.toString().trim().replace("'","''");
		return msg_str;
	}
	
	
	
	
	
	

}
