package com.sinosoft.am.org.linkerHistory.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

import com.persistence.DbConnection;

public class HistoryLinkerDao {

	public int addDataToTable1(String params,String date,
							  String madeMan,
							  String madeManId,
							  String madeManPhe,
							  String deptName,
							  String isIssue){//往表EMC_AM_ORG_INLINK_HISTORY中插数据
		/*try {
			params = java.net.URLDecoder.decode(params, "UTF-8");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}*/
		//String needId = Utils.getParameter("connectId", params) == null ? "" : Utils.getParameter("connectId", params);//装备需求id
		
		String sql = "insert into EMC_AM_ORG_INLINK_HISTORY "+
						 " (FD_OBJECTID,INLINK_INDEX,INLINK_NAME,EMC_TYPE,EMC_ROLE,MANAGER_DEPT,LANDLINE,PHONE,EMAIL,PARENT_ORG,"+
						 " PARENT_DEPT,JOB,IS_DEL,UPDATEDATE,PARENT_DEPT_ID,PARENT_ORG_ID,VIRTUAL_ORG_ID,MAN_REMARKS,ENTRY_MAN_ID,SORT_NO,"+
						 " IS_SHOW_TEL,MADE_MAN,MADE_MAN_PHONE,MADE_MAN_ID,IS_ISSUE,NAME,NUMBER) "+
					 " select "+
						 " FD_OBJECTID,INLINK_INDEX,INLINK_NAME,EMC_TYPE,EMC_ROLE,MANAGER_DEPT,LANDLINE,PHONE,EMAIL,PARENT_ORG,"+
						 " PARENT_DEPT,JOB,IS_DEL,'"+date+"',PARENT_DEPT_ID,PARENT_ORG_ID,VIRTUAL_ORG_ID,MAN_REMARKS,ENTRY_MAN_ID,SORT_NO,"+
						 " IS_SHOW_TEL,'"+madeMan+"','"+madeManPhe+"','"+madeManId+"','"+isIssue+"','"+deptName+"（"+date+"）','NULL' "+
					 " from EMC_AM_ORG_INLINK where " + params;
		StringBuffer sb = new StringBuffer();
		DbConnection dbConn = new DbConnection();		
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		int n = -1;
		try {
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sql);
			n = pstm.executeUpdate();//如果成功返回更新的条数
			if(n != 1){
				//sb.append(fd_id);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null)
					rs.close();
				if(pstm!=null)
					pstm.close();
				if(conn!=null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return n;
	}
	
	
	public String getSimpleData(){
		
		String sql = "select distinct top 100 name,made_man,made_man_phone,updatedate,is_issue,VIRTUAL_ORG_ID from EMC_AM_ORG_INLINK_HISTORY order by updatedate desc;";
		StringBuffer sb = new StringBuffer();
		DbConnection dbConn = new DbConnection();		
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		sb.append("{\"rows\":[");
		int n = 0;
		try {
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sql);
			rs=pstm.executeQuery();
			
			while(rs.next()){
				sb.append("{\"NAME\":\""+rs.getString("name")+"\",\"MADE_MAN\":\""+rs.getString("made_man")+"\","+
						   "\"MADE_MAN_PHONE\":\""+rs.getString("made_man_phone")+"\",\"IS_ISSUE\":\""+rs.getString("is_issue")+"\","+
						   "\"UPDATEDATE\":\""+rs.getString("updatedate")+"\",\"VIRTUAL_ORG_ID\":\""+rs.getString("VIRTUAL_ORG_ID")+"\"},");
				n = n + 1;
			}
			if(n>0){
				sb.deleteCharAt(sb.length()-1);
			}
			
			sb.append("]}");
			System.out.println();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null)
					rs.close();
				if(pstm!=null)
					pstm.close();
				if(conn!=null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return sb.toString();
	}
	
	//
	public String getPeopleMsg(String updatedate){
		
		String sql = "select "+
						 " FD_OBJECTID,INLINK_INDEX,INLINK_NAME,EMC_TYPE,EMC_ROLE,MANAGER_DEPT,LANDLINE,PHONE,EMAIL,PARENT_ORG,"+
					     " PARENT_DEPT,JOB,IS_DEL,UPDATEDATE,PARENT_DEPT_ID,PARENT_ORG_ID,VIRTUAL_ORG_ID,MAN_REMARKS,ENTRY_MAN_ID,SORT_NO,"+
					     " IS_SHOW_TEL,MADE_MAN,MADE_MAN_PHONE,MADE_MAN_ID,IS_ISSUE,NAME,NUMBER "+
				     "from EMC_AM_ORG_INLINK_HISTORY where UPDATEDATE='"+updatedate+"' order by EMC_ROLE;";
		StringBuffer sb = new StringBuffer();
		StringBuffer sbDept = new StringBuffer();
		StringBuffer sbDept1 = new StringBuffer();
		DbConnection dbConn = new DbConnection();		
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		sb.append("{\"rows\":[");
		int n = 0;
		try {
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sql);
			rs=pstm.executeQuery();
			
			while(rs.next()){
				sbDept.append(getDeptMsg(rs.getString("PARENT_ORG_ID")));
				sbDept1.append(getDeptMsg(rs.getString("PARENT_DEPT_ID")));
				sb.append("{"+
							"\"EMC_ROLE\":\""+rs.getString("EMC_ROLE")+"\","+
							"\"INLINK_NAME\":\""+rs.getString("INLINK_NAME")+"\","+
							//"\"PARENT_ORG_ID\":\""+rs.getString("PARENT_ORG_ID")+"\","+
							"\"PARENT_ORG_ID\":\""+sbDept.toString()+"\","+
							//"\"PARENT_DEPT_ID\":\""+rs.getString("PARENT_DEPT_ID")+"\","+
							"\"PARENT_DEPT_ID\":\""+sbDept1.toString()+"\","+
							"\"PHONE\":\""+rs.getString("PHONE")+"\","+
							"\"INLINK_INDEX\":\""+rs.getString("INLINK_INDEX")+"\""+
						"},");
				sbDept.delete(0, sbDept.length());
				sbDept1.delete(0, sbDept1.length());
				n = n + 1;
			}
			if(n>0){
				sb.deleteCharAt(sb.length()-1);
			}
			
			sb.append("]}");
			System.out.println();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null)
					rs.close();
				if(pstm!=null)
					pstm.close();
				if(conn!=null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return sb.toString();
	}
	//通过ID获取单位信息
	public String getDeptMsg(String deptId){
		
		String sql = "select dept_name from sys_department where dept_id='"+deptId+"'";
		StringBuffer sb = new StringBuffer();
		DbConnection dbConn = new DbConnection();		
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sql);
			rs=pstm.executeQuery();
			
			while(rs.next()){
				sb.append(
							//"\"DEPT_NAME\":\""+rs.getString("dept_name")+"\""
							rs.getString("dept_name")
						);
				
			}
			//System.out.println();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null)
					rs.close();
				if(pstm!=null)
					pstm.close();
				if(conn!=null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return sb.toString();
	}
	
	//通过ID获取单位信息
	public int updateIssueState(String updatedate,String issueState,String dateStr){
			String paramVoi =updatedate.split(",")[0];
			String paramDate ="'"+ updatedate.split(",")[1]+"'";
			
			String getName = getName(paramDate);//贵州电网公司应急指挥中心成员名单（2018-03-18 18:38:41）
			getName = getName.split("（")[0]+"（"+dateStr+"）";
			System.out.println("**************"+getName);
			
			String sql = "update EMC_AM_ORG_INLINK_HISTORY set IS_ISSUE='"+issueState+"',updatedate='"+dateStr+"',name='"+getName+"' where updatedate in ("+paramDate+") and VIRTUAL_ORG_ID='"+paramVoi+"';";
			DbConnection dbConn = new DbConnection();		
			Connection conn = null;
			PreparedStatement pstm = null;
			ResultSet rs = null;
			int n = -1;
			try {
				conn = dbConn.getConnection();
				pstm = conn.prepareStatement(sql);
				n = pstm.executeUpdate();
				
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			}finally{
				try {
					if(rs!=null)
						rs.close();
					if(pstm!=null)
						pstm.close();
					if(conn!=null)
						conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			return n;
	}
	
	public String getName(String paramDate){
		String sql = "select distinct name from EMC_AM_ORG_INLINK_HISTORY where updatedate="+paramDate+";";
		StringBuffer sb = new StringBuffer();
		DbConnection dbConn = new DbConnection();		
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sql);
			rs=pstm.executeQuery();
			
			while(rs.next()){
				sb.append(
							//"\"DEPT_NAME\":\""+rs.getString("dept_name")+"\""
							rs.getString("name")
						);
				
			}
			//System.out.println();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null)
					rs.close();
				if(pstm!=null)
					pstm.close();
				if(conn!=null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return sb.toString();
	}
	
	
	public int addDatatoInlink(String condition){//
			/*try {
			params = java.net.URLDecoder.decode(params, "UTF-8");
			} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
			}*/
			//String needId = Utils.getParameter("connectId", params) == null ? "" : Utils.getParameter("connectId", params);//装备需求id
			
			String sql = "insert into EMC_AM_ORG_INLINK "+
							"(FD_OBJECTID,INLINK_INDEX,INLINK_NAME,EMC_TYPE,EMC_ROLE,MANAGER_DEPT,LANDLINE,PHONE,EMAIL,"+
							" PARENT_ORG,PARENT_DEPT,JOB,IS_DEL,UPDATEDATE,PARENT_DEPT_ID,PARENT_ORG_ID,VIRTUAL_ORG_ID,"+
							" MAN_REMARKS,ENTRY_MAN_ID,SORT_NO,IS_SHOW_TEL) "+
						 "select "+
						 	" FD_OBJECTID,INLINK_INDEX,INLINK_NAME,EMC_TYPE,EMC_ROLE,MANAGER_DEPT,LANDLINE,PHONE,EMAIL,"+
						 	" PARENT_ORG,PARENT_DEPT,JOB,IS_DEL,UPDATEDATE,PARENT_DEPT_ID,PARENT_ORG_ID,VIRTUAL_ORG_ID,"+
						 	" MAN_REMARKS,ENTRY_MAN_ID,SORT_NO,IS_SHOW_TEL "+
						 "from EMC_AM_ORG_INLINK_HISTORY where updatedate in "+
							"(select  distinct top 1 updatedate from EMC_AM_ORG_INLINK_HISTORY " +
							"where is_issue='1' and "+condition+" order by updatedate desc);";
			StringBuffer sb = new StringBuffer();
			DbConnection dbConn = new DbConnection();		
			Connection conn = null;
			PreparedStatement pstm = null;
			ResultSet rs = null;
			int n = -1;
			try {
				conn = dbConn.getConnection();
				pstm = conn.prepareStatement(sql);
				n = pstm.executeUpdate();//如果成功返回更新的条数
				if(n != 1){
				//sb.append(fd_id);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			}finally{
				try {
				if(rs!=null)
					rs.close();
				if(pstm!=null)
					pstm.close();
				if(conn!=null)
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			return n;
	}
	
	
	public int delDatatoInlink(String condition){//
			/*try {
			params = java.net.URLDecoder.decode(params, "UTF-8");
			} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
			}*/
			//String needId = Utils.getParameter("connectId", params) == null ? "" : Utils.getParameter("connectId", params);//装备需求id
			
			String sql = "delete from EMC_AM_ORG_INLINK where "+condition+";";
			//String sql = "update EMC_AM_ORG_INLINK set is_del='1' where "+condition+";";
			StringBuffer sb = new StringBuffer();
			DbConnection dbConn = new DbConnection();		
			Connection conn = null;
			PreparedStatement pstm = null;
			ResultSet rs = null;
			int n = -1;
			try {
				conn = dbConn.getConnection();
				pstm = conn.prepareStatement(sql);
				n = pstm.executeUpdate();//如果成功返回更新的条数
				/*if(n != 1){
				//sb.append(fd_id);
				}*/
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			}finally{
				try {
				if(rs!=null)
					rs.close();
				if(pstm!=null)
					pstm.close();
				if(conn!=null)
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			return n;
	}
	
	
	public int updateDataOfInlinkHistory(String condition){//
		/*try {
		params = java.net.URLDecoder.decode(params, "UTF-8");
		} catch (UnsupportedEncodingException e) {
		e.printStackTrace();
		}*/
		//String needId = Utils.getParameter("connectId", params) == null ? "" : Utils.getParameter("connectId", params);//装备需求id
		
		String sql = "update EMC_AM_ORG_INLINK_HISTORY set is_issue='0' where is_issue='1' and "+condition+";";
		StringBuffer sb = new StringBuffer();
		DbConnection dbConn = new DbConnection();		
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		int n = -1;
		try {
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sql);
			n = pstm.executeUpdate();//如果成功返回更新的条数
			/*if(n != 1){
			//sb.append(fd_id);
			}*/
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}finally{
			try {
			if(rs!=null)
				rs.close();
			if(pstm!=null)
				pstm.close();
			if(conn!=null)
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return n;
	}
	
	
	public int updateIssueStateTo0(String updatedate){//历史人员信息页面发布时，把之前已发布的信息改为未发布
		/*try {
		params = java.net.URLDecoder.decode(params, "UTF-8");
		} catch (UnsupportedEncodingException e) {
		e.printStackTrace();
		}*/
		
		String voi = updatedate.split(",")[0];//VIRTUAL_ORG_ID字段值
		String updateDate = updatedate.split(",")[1];//updatedate字段值
		
		//String sql = "update EMC_AM_ORG_INLINK_HISTORY set is_issue='0' where is_del='0' and updatedate='"+updateDate+"' and VIRTUAL_ORG_ID='"+voi+"';";
		String sql = "update EMC_AM_ORG_INLINK_HISTORY set is_issue='0' where is_del='0' and VIRTUAL_ORG_ID='"+voi+"';";
		StringBuffer sb = new StringBuffer();
		DbConnection dbConn = new DbConnection();		
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		int n = -1;
		try {
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sql);
			n = pstm.executeUpdate();//如果成功返回更新的条数
			/*if(n != 1){
			//sb.append(fd_id);
			}*/
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}finally{
			try {
			if(rs!=null)
				rs.close();
			if(pstm!=null)
				pstm.close();
			if(conn!=null)
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return n;
	}
	
	
	public int deleteInlinkDate(String updatedate){//历史人员信息页面发布时，把之前已发布的信息改为未发布
		/*try {
		params = java.net.URLDecoder.decode(params, "UTF-8");
		} catch (UnsupportedEncodingException e) {
		e.printStackTrace();
		}*/
		String voi = updatedate.split(",")[0];//VIRTUAL_ORG_ID字段值
		String updateDate = updatedate.split(",")[1];//updatedate字段值
		
		//String sql = "update EMC_AM_ORG_INLINK set is_del='1' where is_del='0' and VIRTUAL_ORG_ID='"+voi+"';";
		String sql = "delete from EMC_AM_ORG_INLINK where is_del='0' and VIRTUAL_ORG_ID='"+voi+"';";
		StringBuffer sb = new StringBuffer();
		DbConnection dbConn = new DbConnection();		
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		int n = -1;
		try {
			conn = dbConn.getConnection();
			pstm = conn.prepareStatement(sql);
			n = pstm.executeUpdate();//如果成功返回更新的条数
			/*if(n != 1){
			//sb.append(fd_id);
			}*/
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}finally{
			try {
			if(rs!=null)
				rs.close();
			if(pstm!=null)
				pstm.close();
			if(conn!=null)
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return n;
	}
	
	public int insertDataToInlink(String updatedate,String issueState){//往表EMC_AM_ORG_INLINK中插数据
			/*try {
			params = java.net.URLDecoder.decode(params, "UTF-8");
			} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
			}*/
			//String needId = Utils.getParameter("connectId", params) == null ? "" : Utils.getParameter("connectId", params);//装备需求id
		String voi = updatedate.split(",")[0];//VIRTUAL_ORG_ID字段值
		String updateDate = updatedate.split(",")[1];//updatedate字段值 
			
			String sql = "insert into EMC_AM_ORG_INLINK"+
							 " (FD_OBJECTID,INLINK_INDEX,INLINK_NAME,EMC_TYPE,EMC_ROLE,MANAGER_DEPT,LANDLINE,PHONE,EMAIL,PARENT_ORG,"+
							 " PARENT_DEPT,JOB,IS_DEL,UPDATEDATE,PARENT_DEPT_ID,PARENT_ORG_ID,VIRTUAL_ORG_ID,MAN_REMARKS,ENTRY_MAN_ID,SORT_NO,"+
							 " IS_SHOW_TEL) "+
						 " select "+
							 " FD_OBJECTID,INLINK_INDEX,INLINK_NAME,EMC_TYPE,EMC_ROLE,MANAGER_DEPT,LANDLINE,PHONE,EMAIL,PARENT_ORG,"+
							 " PARENT_DEPT,JOB,IS_DEL,UPDATEDATE,PARENT_DEPT_ID,PARENT_ORG_ID,VIRTUAL_ORG_ID,MAN_REMARKS,ENTRY_MAN_ID,SORT_NO,"+
							 " IS_SHOW_TEL "+
						 " from EMC_AM_ORG_INLINK_HISTORY where VIRTUAL_ORG_ID='"+voi+"' and is_del='0' and is_issue='1'";
			StringBuffer sb = new StringBuffer();
			DbConnection dbConn = new DbConnection();		
			Connection conn = null;
			PreparedStatement pstm = null;
			ResultSet rs = null;
			int n = -1;
			try {
				conn = dbConn.getConnection();
				pstm = conn.prepareStatement(sql);
				n = pstm.executeUpdate();//如果成功返回更新的条数
				if(n != 1){
				//sb.append(fd_id);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			}finally{
			try {
				if(rs!=null)
					rs.close();
				if(pstm!=null)
					pstm.close();
				if(conn!=null)
					conn.close();
				} catch (SQLException e) {
				e.printStackTrace();
				}
			}
			return n;
	}
	
	
}