import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.log4j.Logger;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.transform.AliasToEntityMapResultTransformer;

import com.google.gson.Gson;

public class ReleaseRepeatedOrders {
	
	private static SessionFactory factory; 
	static Logger logger = Logger.getLogger("LOG"); 

       public static void main(String[] args) throws IOException, ParseException 
       {
    	   String config_file_s=System.getProperty("user.dir")+"/hibernate.cfg.xml";
			File f1 = new File(config_file_s);
			Configuration configuration = new Configuration().configure(f1);
    	   factory = configuration.buildSessionFactory();
			Session session = factory.openSession();
			
			try 
			{	
				ReleaseRepeatedOrders(session);
				ReleaseGreenChannelCustomerOrders(session);
			} catch (Exception e) {
				e.printStackTrace();
			}
			finally
			{
				session.close();
				factory.close();
			}
			
			logger.info("*********Scheduler END**************");
			System.exit(0);
       }
                              
      public static void ReleaseRepeatedOrders(Session session) throws ParseException, FileNotFoundException
      {
    	
    	logger.info("*********Scheduler START**************");
    	List<Map<String,Object>> query_data_obj= new ArrayList<Map<String,Object>>();
    	
   
    	
    	String enlId="IN000945";
    	String orderNo="";
    	String res="";
    	try{
    		
    			String sql =" select crn_new_request.order_no,year_month,isnull(ticket_no,'') ticket_no,cust_code,crn_new_request.internal_code "
    						+ " from crn_new_request"
    						+ " inner join (select distinct order_no from crn_released_orders_as400 )crn_released_orders_as400 on "
    						+ " crn_new_request.order_no=crn_released_orders_as400.order_no "
							+"	where (repeate_order='Yes' "
							+"	and year_month=cast(convert(varchar(6),getdate(),112)as int) "
							+"	and  ui_status='Orders on Credit/Customer Hold Request Not Submitted to Credit Services') ";
							
       			System.out.println("ReleaseRepeatedOrders....."+sql);
    			SQLQuery qry = session.createSQLQuery(sql)
    				.addScalar("order_no", Hibernate.STRING)
    				.addScalar("cust_code", Hibernate.STRING)
    				.addScalar("internal_code", Hibernate.STRING)
    				.addScalar("year_month", Hibernate.STRING)
    				.addScalar("ticket_no", Hibernate.STRING);
    			
        		qry.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        		query_data_obj=qry.list();
    		if(query_data_obj.size()>0)
    		{
        		for(Map map:query_data_obj)
        		{
        			
        			 orderNo=map.get("order_no").toString();
        			 logger.info("Order No Processing ======"+orderNo);
        			System.out.println("orderNo...."+orderNo);
        			String year_month=map.get("year_month").toString();
        			String ticket_no=map.get("ticket_no").toString();
        			String cust_code=map.get("cust_code").toString();
        			String internal_code=map.get("internal_code").toString();
        			
        			int repOrderCount=GetRepeatOrderCount(session,orderNo);	
        			
        			if(repOrderCount>0)
        				res=UpdateReleaseFlagInAS400(orderNo,enlId);
        			 
        			 
        			 if(res.trim().equals("SUCCESS"))
		    		 {
        				 logger.info("Repeated Order Release SUCCESS======Order No======"+orderNo);
        				 UpdateTicketDetails(session,ticket_no,internal_code,enlId,"Repeated Order Auto release");
						 InsertIntoLog(session,year_month,ticket_no,cust_code,enlId,"Repeated Order Auto release");
		    		 }
        			 else
        			 {
        				 logger.info("Repeated Order Release FAILED======Order No======"+orderNo);
        			 }
        		
        			
        		}
    		}
    		else
    		{
    			logger.info("*********No Repeated Orders Found************");
    		}
    	}
    	catch(Exception e)
    	{
    		logger.info("Error in Releasing Order======Order No======"+orderNo+"====="+e.getMessage());
    		System.out.println(e.getMessage());
        }
      }
      
      
      
      
      public static void ReleaseGreenChannelCustomerOrders(Session session) throws ParseException, FileNotFoundException
      {
    	
    	logger.info("*********Release GREEN CHANNEL ORDERS**************");
    	List<Map<String,Object>> query_data_obj= new ArrayList<Map<String,Object>>();
  
    	
    	String enlId="IN000945";
    	String orderNo="";
    	String res="";
    	try{
    		
    			String sql =" select order_no,year_month,isnull(ticket_no,'') ticket_no,cust_code,internal_code "
							+ " from crn_new_request "
							+ " where focused_account='Yes' and  ui_status='Orders on Credit/Customer Hold Request Not Submitted to Credit Services' "
							+ " and order_date=convert(varchar(8),getdate(),112) ";
							
       			System.out.println("ReleaseRepeatedOrders....."+sql);
    			SQLQuery qry = session.createSQLQuery(sql)
    				.addScalar("order_no", Hibernate.STRING)
    				.addScalar("cust_code", Hibernate.STRING)
    				.addScalar("internal_code", Hibernate.STRING)
    				.addScalar("year_month", Hibernate.STRING)
    				.addScalar("ticket_no", Hibernate.STRING);
    			
        		qry.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        		query_data_obj=qry.list();
    		if(query_data_obj.size()>0)
    		{
        		for(Map map:query_data_obj)
        		{
        			
        			orderNo=map.get("order_no").toString();
        			logger.info("Order No Processing ======"+orderNo);
        			System.out.println("orderNo...."+orderNo);
        			String year_month=map.get("year_month").toString();
        			String ticket_no=map.get("ticket_no").toString();
        			String cust_code=map.get("cust_code").toString();
        			String internal_code=map.get("internal_code").toString();
        			
        			
        				  res=UpdateReleaseFlagInAS400(orderNo,enlId);
        			 
        			 if(res.trim().equals("SUCCESS"))
		    		 {
        				 logger.info("Repeated Order Release SUCCESS======Order No======"+orderNo);
        				 UpdateTicketDetails(session,ticket_no,internal_code,enlId,"Green Channel Customer Order Auto release");
						 InsertIntoLog(session,year_month,ticket_no,cust_code,enlId,"Green Channel Customer Order Auto release");
		    		 }
        			 else
        			 {
        				 logger.info("Repeated Order Release FAILED======Order No======"+orderNo);
        			 }
        		
        			
        		}
    		}
    		else
    		{
    			logger.info("*********No Repeated Orders Found************");
    		}
    	}
    	catch(Exception e)
    	{
    		logger.info("Error in Releasing Order======Order No======"+orderNo+"====="+e.getMessage());
    		System.out.println(e.getMessage());
        }
      }
      
      
      
      public static int GetRepeatOrderCount(Session session,String orderNo) 
      {

    	List<Map<String,Object>> query_data_obj= new ArrayList<Map<String,Object>>();  

    	Integer repOrderCount=0;
    	try{
    		
    			String sql =" select count(1) as repOrderCount from crn_released_orders_as400 "
    						+ " where order_no='"+orderNo+"' ";
							
       			System.out.println("ReleaseRepeatedOrders....."+sql);
    			SQLQuery qry = session.createSQLQuery(sql)
    				.addScalar("repOrderCount", Hibernate.INTEGER);
    			
        		qry.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        		query_data_obj=qry.list();
    		if(query_data_obj.size()>0)
    		{
        		for(Map map:query_data_obj)
        		{
        			
        			repOrderCount=(Integer)map.get("repOrderCount");
        		}
    		}
    		else
    		{
    			logger.info("*********No Repeated Orders Found************");
    		}
    	}
    	catch(Exception e)
    	{
    		
        }
    	return repOrderCount;
      }
      
      
      
      private static void UpdateTicketDetails(Session session,String ticket_no,String internal_code,String enlId,String callType)
      {
    	  Transaction tx = null;
    	  try{
			String ui_status="Order Released";
			String gp_auto_update_status=" ,gp_status='Release' ";
			String update_fields="ui_status='"+ui_status+"',ar_enl_id='"+enlId+"',sp_status='Release',sp_enl_id='"+enlId+"',sp_date=dateadd(MINUTE, 30, dateadd(HOUR, 10, getdate())),"
					+ "ar_status='Release',ar_cust_remarks='"+callType+"', ar_remarks='"+callType+"',ar_date=dateadd(MINUTE, 30, dateadd(HOUR, 10, getdate())) ";
			String up_qry=" update crn_new_request set  "
			+ "	modified_date=dateadd(MINUTE, 30, dateadd(HOUR, 10, getdate()))"
			+ " , ticket_no='"+ticket_no+"',"+update_fields+" "+gp_auto_update_status+" where internal_code="+internal_code;
			  
			   System.out.println("update crn_new_request....."+up_qry);
				tx = session.beginTransaction();	
				Query q = session.createSQLQuery(up_qry);
				q.executeUpdate();
			tx.commit(); 
    	  }
    	  catch(Exception e)
      	{
      		System.out.println(e.getMessage());
          }
      }
      
    private static void InsertIntoLog(Session session,String year_month,String ticket_no,String cust_code,String enlId,String callType)
    {
	    Transaction tx = null;
	    try{
		    String InsertHistory="insert into crn_new_request_history "
					 		+ " (year_month,ticket_no,customer_code,updated_by,user_role,customer_remarks,created_date)"
					 		+ " values ('"+year_month+"','"+ticket_no+"','"+cust_code+"','"+enlId+"','CR_FINANCE','"+callType+"',dateadd(MINUTE, 30, dateadd(HOUR, 10, getdate())) )";
		    	System.out.println("InsertIntoLog......."+InsertHistory);
				tx = session.beginTransaction();
				Query q = session.createSQLQuery(InsertHistory);
				q.executeUpdate(); 
				tx.commit(); 
	    	}
	    	catch(Exception e)
	    	{
	    		System.out.println(e.getMessage());
	    	}
    }
      
    private static String UpdateReleaseFlagInAS400(String orderNo,String userId)
  	{	
  		CallableStatement createCall = null;
  		JDBCAccessor obj=new JDBCAccessor();
  		Connection conn = null;
  		try {
  			conn = obj.getJTConnection("","","");
  		} catch (Exception e1) {
  			// TODO Auto-generated catch block
  			e1.printStackTrace();
  		}
  		
  		ResultSet rs=null;
  		List<Map<String,Object>> query_data_obj= new ArrayList<Map<String,Object>>();
  	
  		String result="";
  		Gson gson = new Gson();	
  		try {
  			createCall = conn.prepareCall("call BPCSLCLIN.OPNORDS2(?,?,?)");
  			createCall.setString(1, "CRRELEASE");
  			createCall.setString(2, userId.trim());
  			createCall.setString(3, orderNo.trim());
  		
  			
  			createCall.execute();
  			rs = createCall.getResultSet();
  			
  			query_data_obj=convertToJSON(rs);
  			System.out.println("result======"+query_data_obj);
  			 result=query_data_obj.get(0).get("FNAME").toString();
  			
  		}
  			catch (SQLException e) {
  				System.out.println("Error -"+e.getMessage());

  			} catch (Exception e) {
  				// TODO Auto-generated catch block
  				e.printStackTrace();
  			} finally {
  				try {
  					createCall.close();
  				} catch (SQLException e) {
  					// TODO Auto-generated catch block
  					e.printStackTrace();
  				}
  			}
  		
  		return result;
  		
  		
  	}
      
    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;
  	}

}
