package com.sinosoft.common.memoryCondition.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import org.apache.log4j.Logger;

import com.persistence.DbConnection;
import com.persistence.service.PersistenceFactory;
import com.persistence.service.SysPersistence;
import com.persistence.service.assitant.generator.IdentityGenerator;
import com.sinosoft.common.memoryCondition.vo.MemoryCon;
import com.sinosoft.em.alert.report.manage.dao.QueryReportProgrammeDaoImpl;
import com.sinosoft.em.alert.report.manage.vo.ReportProgress;
import com.sysmodel.datamodel.xmlmodel.ModelFactory;
import com.sysmodel.datamodel.xmlmodel.able.SysModel;

public class MemoryConditionDao {

	private static SysModel			sysmodel	= ModelFactory.getSysmodel();

	@SuppressWarnings("unused")
	private static SysPersistence	persistence	= PersistenceFactory.getInstance(sysmodel);

	private final static Logger		log			= Logger.getLogger(QueryReportProgrammeDaoImpl.class);

	public String addMemoryCondition(String userId, String menupageid, String name, ArrayList<MemoryCon> paramslist) throws Exception {
		DbConnection db = new DbConnection();
		Connection conn = null;
		PreparedStatement ps = null;
		PreparedStatement ps1 = null;
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String now = sdf.format(new Date());
		try {
			String sql = "insert into SYS_USER_SEARCH_CONDITIONS(FD_OBJECTID, USER_ID, MENU_ID, NAME, IS_DEL, INPUT_TIME, UPDATEDATE) VALUES(?,?,?,?,?,?,?)";
			String sql1 = "insert into SYS_USER_SEARCH_CONDITIONS_FU(FD_OBJECTID, CONDITION_ID, CONDITION_NAME, CONDITION_VALUE, CONDITION_TYPE) VALUES(?,?,?,?,?)";
			conn = db.getConnection();
			conn.setAutoCommit(false);
			int flag1 = 0;
			ps = conn.prepareStatement(sql);
			ps1 = conn.prepareStatement(sql1);
			String fd_id = IdentityGenerator.getIdentityGenerator().gerenalIdentity(746);
			ps.setString(1, fd_id);
			ps.setString(2, userId);
			ps.setString(3, menupageid);
			ps.setString(4, name);
			ps.setString(5, "0");
			ps.setString(6, now);
			ps.setString(7, now);
			int  flag = ps.executeUpdate();
			
			for (MemoryCon meCon : paramslist) {
				ps1.setString(1, IdentityGenerator.getIdentityGenerator().gerenalIdentity(747));
				ps1.setString(2, fd_id);
				ps1.setString(3, meCon.getName());
				ps1.setString(4, meCon.getValue());
				ps1.setString(5, meCon.getType());
				ps1.addBatch();
			}		
			ps1.executeBatch();
			flag1++;
			
			if(flag==1 && flag1==1){
				conn.commit();
				return "sucsess";
			}else{
				conn.rollback();
				return "error";
			}
		} catch (Exception e) {
			throw e;
		}finally{
			db.close(ps1);
			db.close(ps);
			db.close(conn);
		}
		
	}

	public void deleteMemoryCon(String fd_id) throws Exception {
		DbConnection db = new DbConnection();
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			String sql = "update SYS_USER_SEARCH_CONDITIONS set IS_DEL='1' WHERE FD_OBJECTID=?";
			conn = db.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, fd_id);
			ps.executeUpdate();
		} catch (Exception e) {
			throw e;
		}finally{
			db.close(ps);
			db.close(conn);
		}
	}

	public String queryMemoryCondition(String userId, String menupageid) throws Exception {
		DbConnection db = new DbConnection();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sb = new StringBuffer();
		PreparedStatement ps1 = null;
		ResultSet rs1 = null;
		try {
			String sql = "select FD_OBJECTID,USER_ID,MENU_ID,NAME from SYS_USER_SEARCH_CONDITIONS where  IS_DEL='0' AND USER_ID=? AND MENU_ID=?";
			conn = db.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, userId);
			ps.setString(2, menupageid);
			rs = ps.executeQuery();
			sb.append("[");
			while(rs.next()){
				sb.append("{");
				String fd_id = rs.getString("FD_OBJECTID");
				String name = rs.getString("NAME");
				sb.append("\"fd_id\":\""+fd_id+"\"");
				sb.append(",\"name\":\""+name+"\"");
				String sql1 = "select CONDITION_NAME,CONDITION_VALUE,CONDITION_TYPE from SYS_USER_SEARCH_CONDITIONS_FU WHERE CONDITION_ID=? ";
				ps1 = conn.prepareStatement(sql1);
				ps1.setString(1, fd_id);
				rs1 = ps1.executeQuery();
				StringBuffer sb1 = new StringBuffer();
				while(rs1.next()){
					sb1.append("{");
					sb1.append("\"name\":\""+rs1.getString("CONDITION_NAME")+"\"");
					sb1.append(",\"value\":\""+rs1.getString("CONDITION_VALUE")+"\"");
					sb1.append(",\"type\":\""+rs1.getString("CONDITION_TYPE")+"\"");
					sb1.append("},");
				}
				if(sb1.length()>0){
					sb.append(",\"condition\":["+sb1.substring(0, sb1.length()-1)+"]");
				}else{
					sb.append(",\"condition\":[]");
				}
				sb.append("},");
			}
			String result = "";
			if(sb.length()>1){
				result = sb.substring(0,sb.length()-1);
			}else{
				result = sb.toString();
			}
			result += "]";
			return result;
		} catch (Exception e) {
			throw e;
		}finally{
			db.close(ps);
			db.close(conn);
		}
		
	}

	public String queryMenuId(String pageurl) throws Exception {
		DbConnection db = new DbConnection();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String menu_id = "";
		try {
			String sql = "select menu_id from sys_menu where menu_url=? ";
			conn = db.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, pageurl);
			rs = ps.executeQuery();
			while(rs.next()){
				menu_id = rs.getString("menu_id");
			}
			return menu_id;
		} catch (Exception e) {
			throw e;
		}finally{
			db.close(ps);
			db.close(conn);
		}
	}
}