package flow;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;

import org.apache.log4j.Logger;

/**
 * A basic servlet which allows a user to define their code, generate
 * any output, and to select where to transition to next.
 * Last generated by Orchestration Designer at: 2022年2月15日 下午03时40分50秒
 */
public class ser_NodeSQL extends com.avaya.sce.runtime.BasicServlet {
	private static Logger log = Logger.getLogger(ser_NodeSQL.class);
	//{{START:CLASS:FIELDS
	//}}END:CLASS:FIELDS

	/**
	 * Default constructor
	 * Last generated by Orchestration Designer at: 2022年2月15日 下午03时40分50秒
	 */
	public ser_NodeSQL() {
		//{{START:CLASS:CONSTRUCTOR
		super();
		//}}END:CLASS:CONSTRUCTOR
	}

	/**
	 * This method allows for custom integration with other Java components.
	 * You may use Java for sophisticated logic or to integrate with custom
	 * connectors (i.e. JMS, custom web services, sockets, XML, JAXB, etc.)
	 *
	 * Any custom code added here should work as efficiently as possible to prevent delays.
	 * It's important to design your callflow so that the voice browser (Voice Portal/IR)
	 * is not waiting too long for a response as this can lead to a poor caller experience.
	 * Additionally, if the response to the client voice browser exceeds the configured
	 * timeout, the platform may throw an "error.badfetch". 
	 *
	 * Using this method, you have access to all session variables through the 
	 * SCESession object.
	 *
	 * The code generator will *** NOT *** overwrite this method in the future.
	 * Last generated by Orchestration Designer at: 2022年2月15日 下午03时40分50秒
	 */
	public void servletImplementation(com.avaya.sce.runtimecommon.SCESession mySession) {

		inputSQL(mySession);

	}
	
	public void inputSQL(com.avaya.sce.runtimecommon.SCESession mySession){
		ArrayList <IVR_NodeRecord> list =(ArrayList <IVR_NodeRecord>)mySession.getVariableField(IProjectVariables.NODE_RECORD_LIST).getObjectValue();
		log.info("---NodeRecordSQL程序开始---");
		String ibt = mySession.getVariableField(IProjectVariables.NODE_RECORD_INCOMING_BEGIN_TIME).getStringValue();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
		String iet = sdf.format(new Date());
		long endTime;
        long startTime;
        int npt=0;
		try {
			startTime =sdf.parse(ibt).getTime();
			endTime = sdf.parse(iet).getTime();
			long deff=(endTime-startTime);
			System.out.println("      "+deff);
			npt = new Long(deff).intValue();
			} catch (ParseException e) {
			System.out.println(e.getMessage());
		}
		for(IVR_NodeRecord inr : list){
			Properties properties = new Properties();
			InputStream in = null;
			ResultSet rs=null;
			Connection conn = null;
			PreparedStatement pre =null;
			inr.setTalkTime(npt);
			inr.setAllKeys(mySession.getVariableField(IProjectVariables.NODE_RECORD_ALL_KEY).getStringValue());
			inr.setIncomingEndTime(iet);
			inr.setIncomingBeginTime(ibt);
			/*if(inr.getTransferNumber().isEmpty()||inr.getTransferNumber().equals("")){
				inr.setTransferNumber("0");
			}*/
			try {
				String type = mySession.getVariableField(IProjectVariables.DB,IProjectVariables.DB_FIELD_TYPE).getStringValue();
				String driver = null;
				String username = null;
				String password = null;
				String url = null;
				// 若安装多个实例时，要确认相应的port及实例名
				if(type.equals("0")){
					log.debug("-----当前数据库为测试库-----");
					driver = mySession.getVariableField(IProjectVariables.DB,IProjectVariables.DB_FIELD_DRIVER_2).getStringValue();
					Class.forName(driver);
					username = mySession.getVariableField(IProjectVariables.DB,IProjectVariables.DB_FIELD_USER_2).getStringValue();
					password = mySession.getVariableField(IProjectVariables.DB,IProjectVariables.DB_FIELD_PASSWORD_2).getStringValue();
					url = mySession.getVariableField(IProjectVariables.DB,IProjectVariables.DB_FIELD_URL_2).getStringValue();
				}else {
					log.debug("-----当前数据库为正式库-----");
					driver = mySession.getVariableField(IProjectVariables.DB,IProjectVariables.DB_FIELD_DRIVER_1).getStringValue();
					Class.forName(driver);
					username = mySession.getVariableField(IProjectVariables.DB,IProjectVariables.DB_FIELD_USER_1).getStringValue();
					password = mySession.getVariableField(IProjectVariables.DB,IProjectVariables.DB_FIELD_PASSWORD_1).getStringValue();
					url = mySession.getVariableField(IProjectVariables.DB,IProjectVariables.DB_FIELD_URL_1).getStringValue();
				}
				log.debug("-----初始化数据库基础参数   URL:"+url+"   ,username:"+username+"   ,password:"+password);
				conn = null;
				Class.forName(driver);
				log.info("---NodeRecordSQL程序开始---创建数据库连接开始创建");
				//设置数据库链接时间
				DriverManager.setLoginTimeout(3);
				conn = DriverManager.getConnection(url, username, password);
				log.info("---NodeRecordSQL程序开始---创建数据库连接成功----并成功登入登入账户为"+username);
				//inr.setAllKeys(mySession.getVariableField(IProjectVariables.NODE_RECORD_ALL_KEYS).getStringValue());
				String ani = mySession.getVariableField(IProjectVariables.SESSION,IProjectVariables.SESSION_FIELD_ANI).getStringValue();
				String ucid = mySession.getVariableField(IProjectVariables.SESSION,IProjectVariables.SESSION_FIELD_UCID).getStringValue();			
				//String dnis = mySession.getVariableField(IProjectVariables.SESSION,IProjectVariables.SESSION_FIELD_DNIS).getStringValue();
				String visitHotline = mySession.getVariableField(IProjectVariables.DB,IProjectVariables.DB_FIELD_TABLE_3VISIT_HOTLINE).getStringValue();
				String table = mySession.getVariableField(IProjectVariables.DB,IProjectVariables.DB_FIELD_TABLE_3).getStringValue();
				String qsql="INSERT INTO "+table+"  ( NodeBeginTime ,UCID ,VisitHotline ,NodeCoding ,NodeName ,ANI ,NodeEndTime ,NodeProcessingTime ,UpperStoryYorN ,RepeatYorN ,FirstEntryYorN ,InputYorN ,TransferYorN ,TransferNumber ,NodeOrder ,IncomingBeginTime ,IncomingEndTime ,TalkTime ,AllKeys , NoInputCount ,NoMatchCount) VALUES " +
						"('"+ inr.getNodeBeginTime()  +"' , '"+ ucid  +"' , '"+ visitHotline  +"' , '"+ inr.getcMenuConfig().getMenuID()  +"' , '"+ inr.getcMenuConfig().MenuName  +"' , '"+ ani  +"' , '"+ inr.getNodeEndTime()  +"' , "+ inr.getNodeProcessingTime()  +" , "+ inr.getUpperStoryYorN()  +" , "+ inr.getRepeatYornYorN()  +" , "+ inr.getFirstEntryYorN()  +" , "+ inr.getInputYorN()  +" , "+ inr.getTransferYorN()  +" , '"+ inr.getTransferNumber()  +"' , "+ inr.getNodeOrder()  +" , '"+ inr.getIncomingBeginTime()  +"' , '"+ inr.getIncomingEndTime()  +"' , "+ inr.getTalkTime()  +" , '"+ inr.getAllKeys() +"' ,"+inr.getNoInputCount()+" , "+inr.getNoMatchCount()+" )";
				log.info(qsql);
				pre = conn.prepareStatement(qsql);
	            pre.execute();
           		log.info("---NodeRecordSQL程序开始---获取插入成功正常");
			} catch (Exception e) {
				System.out.println("---NodeRecordSQL程序开始---数据库连接这里出现问题："+e.getMessage());
				log.info("---NodeRecordSQL程序开始---数据库连接这里出现问题："+e.getMessage());
				
			}finally{
				try {
					if(pre!=null){
						pre.close();}
					if(conn!=null){
						conn.close();}
				} catch (Exception e) {
					System.out.println(e.getMessage());
				}
			}
		}
	}
	/**
	 * Builds the list of branches that are defined for this servlet object.
	 * This list is built automatically by defining Goto nodes in the call flow editor.
	 * It is the programmer's responsibilty to provide at least one enabled Goto.<BR>
	 *
	 * The user should override updateBranches() to determine which Goto that the
	 * framework will activate.  If there is not at least one enabled Goto item, 
	 * the framework will throw a runtime exception.<BR>
	 *
	 * This method is generated automatically and changes to it may
	 * be overwritten next time code is generated.  To modify the list
	 * of branches for the flow item, override:
	 *     <code>updateBranches(Collection branches, SCESession mySession)</code>
	 *
	 * @return a Collection of <code>com.avaya.sce.runtime.Goto</code>
	 * objects that will be evaluated at runtime.  If there are no gotos
	 * defined in the Servlet node, then this returns null.
	 * Last generated by Orchestration Designer at: 2023年4月14日 下午05时14分30秒
	 */
	public java.util.Collection getBranches(com.avaya.sce.runtimecommon.SCESession mySession) {
		java.util.List list = null;
		com.avaya.sce.runtime.Goto aGoto = null;
		list = new java.util.ArrayList(1);

		aGoto = new com.avaya.sce.runtime.Goto("end", 0, true, "Default");
		list.add(aGoto);

		return list;
	}
}
