package com.sinosoft.em.warning.duty;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import com.formaction.Parameter;
import com.formaction.vo.Msg;
import com.persistence.DbConnection;
import com.persistence.service.assitant.generator.IdentityGenerator;
import com.persistence.service.exception.PersistenceException;
import com.sinosoft.am.org.jdbcUtil.ORGTemplate;
import com.sinosoft.cm.common.ArrayList;
import com.sinosoft.em.alert.report.manage.dao.QueryReportProgrammeDaoImpl;
import com.sinosoft.lz.system.sms.vo.SearchPeople;
import com.sinosoft.lz.system.sms.vo.Tree;

public class WarningDutyDao {


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


	public String loadDutyDetailByDeptIds(String deptids, String planId) {
		
		String[] deptIdArr = deptids.split(",");
		
		List<Map<String, String>> dateList = getDutyDate(planId);
		List<WarningDutyBean> dutyList = new ArrayList<WarningDutyBean>();
		for (int i = 0; i < dateList.size(); i++) {
			Map<String, String> map = dateList.get(i);
			WarningDutyBean  duty = new WarningDutyBean();
			String dutyDate = map.get("DUTY_DATE");
			String dutyTime = map.get("DUTY_TIME");
			String fuZhuId = map.get("FD_OBJECTID");
			duty.setDutyDate(dutyDate);
			duty.setDutyTime(dutyTime);
			//循环部门
			for (int j = 0; j < deptIdArr.length; j++) {
				String deptId = deptIdArr[j];
				List<Map<String, String>> list = getDutyDetailByDeptId(deptId,fuZhuId,planId);
				if(list!=null && list.size()>0){
					Map<String, String> map1 = list.get(0);
					String cz = map1.get("CZ");
					String zg = map1.get("ZG");
					String dutyPer = cz+"</br>"+zg;
					if("".equals(cz.trim()) && "".equals(zg.trim())){
						dutyPer = "";
					}
					if("R002105430213".equals(deptId)){//安监部
						String dbld = map1.get("DBLD");
						duty.setDbld(dbld);
						duty.setAjb(dutyPer);
					}
					if("R002105430210".equals(deptId)){//系统部
						duty.setXtb(dutyPer);
					}
					if("R002105430211".equals(deptId)){//设备部
						duty.setSbb(dutyPer);
					}
					if("R002105430212".equals(deptId)){//市场部
						duty.setScb(dutyPer);
					}
					if("R002105430213".equals(deptId)){//物资部
						duty.setAjb(dutyPer);
					}
					if("R002105430213".equals(deptId)){//办公厅
						duty.setAjb(dutyPer);
					}
					
				}
				
			}
			dutyList.add(duty);
		}
		return JSONArray.fromObject(dutyList).toString();
	}

	/**
	 * 通过任务id查询值班辅助表数据---值班日期和值班时间
	 * @param planId 任务id
	 * @return 
	 */
	public List<Map<String, String>> getDutyDate(String planId) {
		String sql = "select FD_OBJECTID,DUTY_DATE,DUTY_TIME,DUTY_PLAN_ID,SORT from BM_ECM_WARNING_DUTY_TABLE_ROW where DUTY_PLAN_ID='"+planId+"' order by duty_date,duty_time ";
		try {
			List<Map<String, String>> list = new ORGTemplate().query(sql);
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	
	/**
	 * 通过任务id和部门id查询该部门的值班安排详细
	 * @param deptId 部门（角色）id
	 * @param planId 任务id
	 * @param planId2 
	 * @param dutyTime 
	 * @return 
	 */
	public List<Map<String, String>> getDutyDetailByDeptId(String deptId, String fuZhuId, String planId) {
		String sql = "select * from (select a.FD_OBJECTID , DUTY_RECORDS_ID, TABLE_ROW_ID , DBLD , CZ , ZG , b.DUTY_DATE , b.DUTY_TIME from BM_ECM_WARNING_DUTY_DETAIL a LEFT JOIN BM_ECM_WARNING_DUTY_TABLE_ROW b ON a.TABLE_ROW_ID=b.FD_OBJECTID where b.fd_objectid='"+fuZhuId+"' order by  b.duty_date,b.duty_time ) d, BM_ECM_WARNING_DUTY_RECORDS c where c.fd_objectid=d.DUTY_RECORDS_ID and c.duty_plan_id='"+planId+"' and c.INPUT_PER_DEPT_ID='"+deptId+"' and c.RELEASE_STATE='1'";
		try {
			List<Map<String, String>> list = new ORGTemplate().query(sql);
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * （发布任务时）保存值班辅助表数据
	 * @param planId
	 * @param list 
	 * @return
	 */
	public Msg saveDutyFuZhu(String planId, List<Map<String, String>> list) {
		Msg m=new Msg();
		String sql="insert into  BM_ECM_WARNING_DUTY_TABLE_ROW( FD_OBJECTID, DUTY_DATE, DUTY_TIME, DUTY_PLAN_ID, SORT) VALUES(?,?,?,?,?);";
		String sql1="delete from   BM_ECM_WARNING_DUTY_TABLE_ROW  where DUTY_PLAN_ID='"+planId+"'";
		Connection conn = null;
		PreparedStatement st = null;
		PreparedStatement st1 = null;
		DbConnection db = new DbConnection();
		try {
			conn = db.getConnection();
			int num=0;
			st1 = conn.prepareStatement(sql1);
			st1.executeUpdate();
			conn.setAutoCommit(false);
			st = conn.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				Map<String, String> map  = list.get(i);
				String fd_id=IdentityGenerator.getIdentityGenerator().gerenalIdentity(313);
				st.setString(1, fd_id);
				st.setString(2,  map.get("dutydate"));
				st.setString(3,  map.get("dutytime"));
				st.setString(4,  planId);
				st.setString(5,  ""+i);
				
				st.executeUpdate();
				log.info(sql);
				num++;
			}
			if(num==list.size()){
				log.info("保存成功");
				 conn.commit();
				 m.setSucsess(new Boolean(true).toString());
				 m.setInfo(Parameter.DELETE_SUCSESS);
				 return m;
			}else{
				m.setSucsess(new Boolean(false).toString());
				m.setInfo(Parameter.DELETE_FAILURE);
				 conn.rollback(); 
				 log.info("保存失败");
				 return m;
			 }
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.close(st);
			db.close(conn);
			try {
					if(st1!= null)
					st1.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return m;
	}

	public String loadDutyDetailByRecordsId(String recordsId) {
		String sql = "select a.FD_OBJECTID,UPDATEDATE,IS_DEL,DUTY_RECORDS_ID,TABLE_ROW_ID,DBLD,CZ,ZG,b.DUTY_DATE,b.DUTY_TIME from BM_ECM_WARNING_DUTY_DETAIL a LEFT JOIN BM_ECM_WARNING_DUTY_TABLE_ROW b ON a.TABLE_ROW_ID=b.FD_OBJECTID where  DUTY_RECORDS_ID='"+recordsId+"'  order by b.DUTY_DATE,b.DUTY_TIME ";
		try {
			List<Map<String, String>> list = new ORGTemplate().query(sql);
			return JSONArray.fromObject(list).toString();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
		
	}

	public String loadPlanByDeptId(String deptId) {
		String result = "";
		String sql = "select FD_OBJECTID value,UPDATEDATE,IS_DEL,PLAN_NAME key,EVENT_ID,INPUT_PER_NAME,INPUT_PER_ID,INPUT_PER_UNIT_NAME,"
				+ "INPUT_PER_UNIT_ID,INPUT_PER_DEPT_NAME,INPUT_PER_DEPT_ID,START_DATE,END_DATE,DUTY_NUM,START_TIME,"
				+ "WAIT_INPUT_LINGDAO_DEPT_ID,WAIT_INPUT_LINGDAO_DEPT_NAME,WAIT_INPUT_DUTY_DEPT_ID,WAIT_INPUT_DUTY_DEPT_NAME,"
				+ "RELEASE_STATE from  BM_ECM_WARNING_DUTY_PLAN where  RELEASE_STATE='1' AND IS_DEL='0' "
				+ "and WAIT_INPUT_DUTY_DEPT_ID like '%"+deptId+"%' order by updatedate desc";
		List<Map<String, String>> list = new ArrayList<Map<String,String>>();
		try {
			list = new ORGTemplate().query(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if(list.size()==0){
			Map<String, String> map = new HashMap<String, String>();
			map.put("key", "请选择");
			map.put("value", "请选择");
			list.add(map);
		}
		result = JSONArray.fromObject(list).toString();
		result = result.replaceAll("KEY", "key").replaceAll("VALUE", "value");
		return result;
	}

	public Msg deleteDutyFuZhu(String planId) {
		Msg m=new Msg();
		String sql="delete from  BM_ECM_WARNING_DUTY_TABLE_ROW where DUTY_PLAN_ID=? ";
		Connection conn = null;
		PreparedStatement st = null;
		DbConnection db = new DbConnection();
		try {
			conn = db.getConnection();
			st = conn.prepareStatement(sql);
			st.setString(1, planId);
			st.executeUpdate();
			log.info(sql);
			log.info("保存成功");
			 m.setSucsess(new Boolean(true).toString());
			 m.setInfo(Parameter.DELETE_SUCSESS);
		} catch (Exception e) {
			log.info("保存失败");
			 m.setSucsess(new Boolean(false).toString());
			 m.setInfo(Parameter.DELETE_FAILURE);
			e.printStackTrace();
		} finally {
			db.close(st);
			db.close(conn);
		}
		return m;
	}
	
	public String getPeople(String name,int pageSize,int page,String id) throws PersistenceException{
		if(name.contains("'")){
			name = name.replaceAll("'", "");
		}
		
		
		
		String sql = "select distinct  top "+(page-1)*pageSize+","+pageSize+"  b.user_id,b.dept_id,b.real_name,b.mobile from sys_department a,sys_user_info b where a.dept_id=b.dept_id and (b.real_name like ? or b.login_name like ?)";
		String sql1 = "select distinct  b.user_id,b.dept_id,b.real_name,b.mobile from  sys_department a,sys_user_info b where  a.dept_id=b.dept_id and (b.real_name like ? or b.login_name like ? ) ";
	
		if(!"".equals(id)){
			sql+="and a.corp_id =? ";
			sql1 += " and a.corp_id = ? ";
		}
		String sql2 = "select distinct count(*) from ("+sql1+")";
		
		List<String[]> list1 = null;
		List<String[]> list2 = null;
		
		if(!"".equals(id)){
			try {
				list1 = new ORGTemplate().getSearchResult(sql,"%"+name+"%","%"+name+"%",id+"_needUsein");
				list2 = new ORGTemplate().getSearchResult(sql2,"%"+name+"%","%"+name+"%",id+"_needUsein");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}else{
			
			try {
				list1 = new ORGTemplate().getSearchResult(sql,"%"+name+"%","%"+name+"%");
				list2 = new ORGTemplate().getSearchResult(sql2,"%"+name+"%","%"+name+"%");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
			
			
		
		
		List<Object> list = new ArrayList<Object>();
		Tree t = new Tree();
		SearchPeople sp = null;
		for(String[] s:list1){
			sp = new SearchPeople();
			sp.setKey(s[2]);
			sp.setValue(s[3]);
			list.add(sp);
		}
		String totalRows = list2.get(0)[0];
		t.setTreeNodes(list);
		t.setTotalRows(totalRows);
		return JSONObject.fromObject(t).toString();
	}

}