package cn.ksource.web.facade.tj;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;

import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Service;

import cn.ksource.beans.CMDB_CI_CATEGORY;
import cn.ksource.core.dao.BaseDao;
import cn.ksource.core.dao.SqlParameter;
import cn.ksource.core.util.ConvertUtil;
import cn.ksource.core.util.JsonUtil;
import cn.ksource.core.util.StringUtil;
import cn.ksource.core.util.TreeUtil;
import cn.ksource.core.web.WebUtil;
import cn.ksource.web.Constants;
import cn.ksource.web.util.ChartUtil;
@Service
public class SparePartStatisFacadeImpl implements SparePartStatisFacade{
	@Resource
	private BaseDao baseDao;

	public List<Map> getCiTypeChart(String lv1Id,String lv2Id,String queryType){
		if(StringUtil.isBlank(queryType)){
			queryType = "CHART";
		}
		
		
		String level = "1";
		String where_c = "";
		String where_q = "";
		
		String column = "cmdb.lv1_id id,cmdb.lv1_name name";
		String column1 = "cmdb.lv1_id ,cmdb.lv1_name ";
		String group = "  cmdb.lv1_id ";
		if(StringUtil.isNotBlank(lv1Id)){
			if(StringUtil.isBlank(lv2Id)){
				column = "cmdb.lv2_id id,cmdb.lv2_name name,cmdb.lv1_id lv_id";
				column1 = "cmdb.lv2_id ,cmdb.lv2_name ,cmdb.lv1_id ";
				where_q = " and cmdb.LV1_ID =:lv1Id";
				level = "2";
				where_c = " and PID =:lv1Id ";
				group=" cmdb.lv2_id";
			}else{
				column = "cmdb.lv3_id id,cmdb.lv3_name name, cmdb.lv1_id lv1_id,\n" +
						"  cmdb.lv2_id lv2_id ";
				column1 = "cmdb.lv3_id ,cmdb.lv3_name , cmdb.lv1_id ,\n" +
						"  cmdb.lv2_id  ";
				where_q = " and cmdb.LV2_ID =:lv2Id ";
				level = "3";
				where_c = " and PID =:lv2Id ";
				group=" cmdb.lv3_id";
			}
		}
		if(StringUtil.isNotBlank(lv2Id)){
			column = "cmdb.lv3_id id,cmdb.lv3_name name, cmdb.lv1_id lv_id,\n" +
					"  cmdb.lv2_id lv2_id ";
			column1 = "cmdb.lv3_id ,cmdb.lv3_name , cmdb.lv1_id ,\n" +
					"  cmdb.lv2_id  ";
			where_q = " and cmdb.LV2_ID =:lv2Id ";
			level = "3";
			where_c = " and PID =:lv2Id ";
			group=" cmdb.lv3_id";
		}
		Map param = new HashMap();
		param.put("level", level);
		param.put("lv1Id", lv1Id);
		param.put("lv2Id", lv2Id);
		
		StringBuilder sql = new StringBuilder();
		String realSql;
		sql.append(" select count(SPARE_PART.id) num,b.lv, $column from SPARE_PART,CMDB_CI_CATEGORY  cmdb,CMDB_CI_CATEGORY b");
		sql.append(" where SPARE_PART.state=1 and SPARE_PART.CATE_ID = cmdb.ID $where and cmdb.state=1 and b.id=$group and b.state=1");
		sql.append(" group by $group,b.lv,$groCol ");
		realSql = sql.toString().replace("$column", column);
		realSql = realSql.replace("$where", where_q);
		realSql = realSql.replace("$group", group);
		realSql = realSql.replace("$groCol",column1);
		List<Map> queryList = baseDao.queryForList(realSql,param);
		List<Map> result = new ArrayList<Map>();
		Integer sum_qt=0;
		Integer a=queryList.size();
		if(queryList!=null&&queryList.size()>0) {
			for (Map map : queryList) {
				if(result.size()>=9 && queryList.size()!=10){
					sum_qt+=ConvertUtil.obj2Int(map.get("num"));
				}else {
					Map series_map = new HashMap();
					series_map.put("id", ConvertUtil.obj2StrBlank(map.get("id")));
					series_map.put("name", ConvertUtil.obj2StrBlank(map.get("name")));
					series_map.put("y", ConvertUtil.obj2Int(map.get("num")));
					series_map.put("lv", ConvertUtil.obj2Int(map.get("lv")));
					if (ConvertUtil.obj2Int(map.get("lv")) == 2) {
						series_map.put("lv1_id", ConvertUtil.obj2StrBlank(map.get("lv_id")));
					}
					result.add(series_map);
				}
			}
		}
		if(sum_qt>0){
			Map series_map = new HashMap();
			series_map.put("name","其它");
			series_map.put("y", sum_qt);
			result.add(series_map);
		}
		System.out.println("----------"+result);
		return result;
	}
	
	public Map getCiCountChart(String cusId){
		Map param = new HashMap();
		param.put("cusId", cusId);
		
		StringBuilder sql = new StringBuilder();
		sql.append(" select total.lv3_id id,total.total_num," +
				"IFNULL(normal.nor_num,'-') nor_num,IFNULL(abandon.abd_num,'-') abd_num," +
				"IFNULL(new.new_num,'-') new_num from  ");
		sql.append(" ( ");
		sql.append(" 	select count(id) total_num,lv3_id  ");
		sql.append("  	from cmdb_ci_base where cus_ID = :cusId ");
		sql.append("  	group by lv3_id  ");
		sql.append("  ) total ");
		sql.append("  LEFT JOIN ");
		sql.append("  ( ");
		sql.append("  	select count(id) nor_num,lv3_id  ");
		sql.append("  	from  cmdb_ci_base where state = 1 and cus_ID = :cusId  ");
		sql.append("  	group by lv3_id  ");
		sql.append("  ) normal ");
		sql.append("  on total.lv3_id = normal.lv3_id ");
		sql.append("  LEFT JOIN ");
		sql.append("  ( ");
		sql.append("  	select count(id) abd_num,lv3_id  ");
		sql.append("  	from  cmdb_ci_base where state = 2 and cus_ID = :cusId   ");
		sql.append("  	group by lv3_id ");
		sql.append("  ) abandon  ");
		sql.append("  on total.lv3_id = abandon.lv3_id ");
		sql.append("  LEFT JOIN ");
		sql.append("  ( ");
		sql.append("  	select count(id) new_num,lv3_id   ");
		sql.append("  	from  cmdb_ci_base where state = 3 and cus_ID = :cusId   ");
		sql.append("  	group by lv3_id ");
		sql.append("  ) new  ");
		sql.append("  on total.lv3_id = new.lv3_id ");
		List<Map> queryList = baseDao.queryForList(sql.toString(),param);
		
		
		sql.setLength(0);
		sql.append(" select id,name text,pid parentId,serial sortId ");
		sql.append(" from cmdb_ci_category  ");
		sql.append(" where type=1 and state=1  ");
		List<Map> categoryList = baseDao.queryForList(sql.toString(),param);
		TreeUtil treeUtil = new TreeUtil();
		Map rootMap = treeUtil.createTree(categoryList,queryList);
		
		Map resMap = new HashMap();
		resMap.put("rootMap", rootMap);
		return resMap;
	}
	
	public List getSpareCategoryList(String lv1Id){
		StringBuilder sql = new StringBuilder();
		Map param = new HashMap();
		param.put("lv1Id", lv1Id);
		
		sql.append(" select id,name from cmdb_ci_category ");
		sql.append(" where CATE_TYPE=2 ");
		if(StringUtil.isBlank(lv1Id)){
			sql.append(" and lv =1 ");
		}else{
			sql.append(" and lv =2 and pid = :lv1Id ");
		}
		sql.append(" and state=1 order by SERIAL ");
		return baseDao.queryForList(sql.toString(),param);
	}
	/**
	 * 备品备件数量统计
	 * @param beginTime
	 * @param endTime
	 * @param cate_id
	 * @return
	 */
	public List<Map> getSpareTableTypeChart(String cate_id,String beginTime,String endTime,String type){
		StringBuilder sql = new StringBuilder();
		Map param = new HashMap();
		List<Map> list=new ArrayList();
		if(StringUtil.isBlank(cate_id) && type.equals("bjcrktj")){
				String rksl_string="SELECT\n" +
						"			count( distinct SPARE_PART.id) rksl\n" +
						"		FROM\n" +
						"			SPARE_PART,\n" +
						"			WORKFLOW_BASE,\n" +
						"			SPARE_PART_STORAGE\n" +
						"		WHERE\n" +
						"			SPARE_PART.LINK_STORAGE_ID = SPARE_PART_STORAGE.ID\n" +
						"		AND	WORKFLOW_BASE.id = SPARE_PART_STORAGE.FLOW_ID\n" +
						"		AND SPARE_PART_STORAGE.STATE = 3 ";
				if(StringUtil.isNotBlank(beginTime)){
					rksl_string+=" and WORKFLOW_BASE.ENDTIME>=:beginTime ";
					param.put("beginTime", beginTime+"000000");
				}
				if(StringUtil.isNotBlank(endTime)){
					rksl_string+=" and WORKFLOW_BASE.ENDTIME<=:endTime ";
					param.put("endTime", endTime+"235959");
				}
				Integer rksl=baseDao.queryForInteger(rksl_string, param);
				Map map=new HashMap();
				map.put("rksl", rksl);
				
				String cksl_string="SELECT\n" +
						"			count(distinct SPARE_PART.id)\n" +
						"		FROM\n" +
						"			SPARE_PART,\n" +
						"			SPARE_PART_DELIVERY,\n" +
						"			SPARE_PART_APPLY_ENTITY,\n" +
						"			WORKFLOW_BASE\n" +
						"		WHERE\n" +
						"		 SPARE_PART_APPLY_ENTITY.ENTITY_ID = SPARE_PART.ID\n" +
						"		AND SPARE_PART_APPLY_ENTITY.DELIVERY_ORDER_ID=SPARE_PART_DELIVERY.ID\n" +
						"		AND SPARE_PART.STATE = 3\n" +
						"		AND WORKFLOW_BASE.id =SPARE_PART_DELIVERY.FLOW_ID \n" +
						"		AND SPARE_PART_DELIVERY.state = 3 ";
				if(StringUtil.isNotBlank(beginTime)){
					cksl_string+=" and WORKFLOW_BASE.ENDTIME>=:beginTime ";
					param.put("beginTime", beginTime+"000000");
				}
				if(StringUtil.isNotBlank(endTime)){
					cksl_string+=" and WORKFLOW_BASE.ENDTIME<=:endTime ";
					param.put("endTime", endTime+"235959");
				}
				Integer cksl=baseDao.queryForInteger(cksl_string, param);
				map.put("cksl", cksl);
				
				
				String slsl_string="	SELECT\n" +
						"				count(distinct SPARE_PART.id)\n" +
						"			FROM\n" +
						"				SPARE_PART_APPLY,\n" +
						"				SPARE_PART_APPLY_CATE,\n" +
						"				SPARE_PART_APPLY_ENTITY,\n" +
						"				WORKFLOW_BASE,\n" +
						"				SPARE_PART\n" +
						"			WHERE\n" +
						"				SPARE_PART_APPLY_CATE.APPLY_ORDER_ID = SPARE_PART_APPLY.id\n" +
						"			AND SPARE_PART.ID = SPARE_PART_APPLY_ENTITY.ENTITY_ID\n" +
						"			AND SPARE_PART_APPLY_ENTITY.LINK_ID = SPARE_PART_APPLY_CATE.ID\n" +
						"			AND SPARE_PART_APPLY.STATE IN (3, 4, 5)\n" +
						"		    AND WORKFLOW_BASE.id =SPARE_PART_APPLY.FLOW_ID \n" +
						"			AND SPARE_PART.state = '3'\n" +
						"			AND SPARE_PART_APPLY_ENTITY.CI_ID IS NOT NULL";
				if(StringUtil.isNotBlank(beginTime)){
					slsl_string+=" and SPARE_PART_APPLY_ENTITY.GMT_USAGE>=:beginTime ";
					param.put("beginTime", beginTime+"000000");
				}
				if(StringUtil.isNotBlank(endTime)){
					slsl_string+=" and SPARE_PART_APPLY_ENTITY.GMT_USAGE<=:endTime ";
					param.put("endTime", endTime+"235959");
				}
				Integer slsl=baseDao.queryForInteger(slsl_string, param);
				map.put("slsl", slsl);
				list.add(map);
		}else{
						sql.append("select t.id,t.lv1_name,t.lv2_name,t.lv3_name,t.rksl,t.cksl,t.slsl from (" +
								"SELECT\n" +
									"	cmdb.LV1_NAME,\n" +
									"	cmdb.LV2_NAME,\n" +
									"	cmdb.LV3_NAME,\n" +
								    "   cmdb.id,\n" +
									"	(\n" +
									"		SELECT\n" +
									"			count(SPARE_PART.id)\n" +
									"		FROM\n" +
									"			SPARE_PART,\n" +
									"			WORKFLOW_BASE,\n" +
									"			SPARE_PART_STORAGE\n" +
									"		WHERE\n" +
									"			SPARE_PART.LINK_STORAGE_ID = SPARE_PART_STORAGE.ID\n" +
									"		AND	WORKFLOW_BASE.id = SPARE_PART_STORAGE.FLOW_ID\n" +
									"		AND SPARE_PART_STORAGE.STATE = 3");
						
						
						if(StringUtil.isNotBlank(beginTime)){
							sql.append(" and WORKFLOW_BASE.ENDTIME>=:beginTime ");
							param.put("beginTime", beginTime+"000000");
						}
						if(StringUtil.isNotBlank(endTime)){
							sql.append(" and WORKFLOW_BASE.ENDTIME<=:endTime ");
							param.put("endTime", endTime+"235959");
						}
						
						sql.append("  AND SPARE_PART.CATE_ID = spare.CATE_ID\n" +
								"	) rksl,\n" +
								"	(\n" +
								"		SELECT\n" +
								"			count(distinct SPARE_PART.id)\n" +
								"		FROM\n" +
								"			SPARE_PART,\n" +
								"			SPARE_PART_DELIVERY,\n" +
								"			SPARE_PART_APPLY_ENTITY,\n" +
								"			WORKFLOW_BASE\n" +
								"		WHERE\n" +
								"		 SPARE_PART_APPLY_ENTITY.ENTITY_ID = SPARE_PART.ID\n" +
								"		AND SPARE_PART.STATE = 3\n" +
								"   AND SPARE_PART_APPLY_ENTITY.DELIVERY_ORDER_ID=SPARE_PART_DELIVERY.ID\n" +
								"		AND WORKFLOW_BASE.id =SPARE_PART_DELIVERY.FLOW_ID \n");

						if(StringUtil.isNotBlank(beginTime)){
							sql.append(" and WORKFLOW_BASE.ENDTIME>=:beginTime ");
							param.put("beginTime", beginTime+"000000");
						}
						if(StringUtil.isNotBlank(endTime)){
							sql.append(" and WORKFLOW_BASE.ENDTIME<=:endTime ");
							param.put("endTime", endTime+"235959");
						}
						
						
						
						sql.append("  AND SPARE_PART.CATE_ID = spare.CATE_ID\n" +
								"	) cksl,\n" +
								"	ifnull(\n" +
								"		(\n" +
								"			SELECT\n" +
								"				count(distinct SPARE_PART.id)\n" +
								"			FROM\n" +
								"				SPARE_PART_APPLY,\n" +
								"				SPARE_PART_APPLY_ENTITY,\n" +
								"				WORKFLOW_BASE,\n" +
								"				SPARE_PART\n" +
								"			WHERE\n" +
								"			 SPARE_PART.ID = SPARE_PART_APPLY_ENTITY.ENTITY_ID\n" +
								"			AND SPARE_PART_APPLY.STATE IN (3, 4, 5)\n" +
								"		    AND WORKFLOW_BASE.id =SPARE_PART_APPLY.FLOW_ID \n");

						
						if(StringUtil.isNotBlank(beginTime)){
							sql.append(" and SPARE_PART_APPLY_ENTITY.GMT_USAGE>=:beginTime ");
							param.put("beginTime", beginTime+"000000");
						}
						if(StringUtil.isNotBlank(endTime)){
							sql.append(" and SPARE_PART_APPLY_ENTITY.GMT_USAGE<=:endTime ");
							param.put("endTime", endTime+"235959");
						}	
						sql.append("	AND SPARE_PART.CATE_ID = spare.CATE_ID\n" +
								"			AND SPARE_PART.state = '3'\n" +
								"			AND SPARE_PART_APPLY_ENTITY.CI_ID IS NOT NULL\n" +
								"		),\n" +
								"		0\n" +
								"	) slsl \n" +
									"   FROM\n" +
									"	SPARE_PART spare,\n" +
									"	CMDB_CI_CATEGORY cmdb\n" +
									"WHERE\n" +
									"	spare.CATE_ID = cmdb.id\n");
									//"AND spare.state = 1");
											
					
						if(StringUtil.isNotBlank(cate_id)){
							sql.append(" and spare.CATE_ID=:cate_id ");
							param.put("cate_id", cate_id);
						}
							sql.append(")t GROUP BY t.id,t.lv1_name,t.lv2_name,t.lv3_name,t.rksl,t.cksl,t.slsl  order by t.rksl desc");
							list=baseDao.queryForList(sql.toString(),param);
		}
		
		return list;
	}
	/**
	 * 项目备件申领统计数据
	 */
	
	public List getProjectSpareTableTypeChart(String customer_id,String beginTime,String endTime){
		StringBuilder sql = new StringBuilder();
		Map param = new HashMap();
				sql.append("SELECT\n" +
						"	count(spare.id) zs,\n" +
						"	apply.PROJECT_NAME,\n" +
						"	sum(spare.SALE_PRICE) je\n" +
						"FROM\n" +
						"	SPARE_PART_APPLY apply,\n" +
						"	SPARE_PART_APPLY_ENTITY entity,\n" +
						"	SPARE_PART spare,\n" +
						"	SC_PARTNER_CUSTOMER_INFO\n" +
						"WHERE\n" +
						"	entity.ORDER_ID = apply.ID\n" +
						"AND entity.CI_ID IS NOT NULL\n" +
						"AND entity.ENTITY_ID = spare.id\n" +
						"AND apply.PROJECT_ID = SC_PARTNER_CUSTOMER_INFO.id\n" +
						"AND SC_PARTNER_CUSTOMER_INFO.CUSTOMER_STATE = 1");
		
				if(StringUtil.isNotBlank(customer_id)){
					sql.append(" and apply.PROJECT_ID=:customer_id ");
					param.put("customer_id", customer_id);
				}	
				if(StringUtil.isNotBlank(beginTime)){
					sql.append(" and entity.gmt_usage>=:beginTime ");
					param.put("beginTime", beginTime+"000000");
				}
				if(StringUtil.isNotBlank(endTime)){
					sql.append(" and entity.gmt_usage<=:endTime ");
					param.put("endTime", endTime+"235959");
				}
				
				sql.append(" GROUP BY apply.PROJECT_ID ,apply.PROJECT_NAME order by zs desc");
				return baseDao.queryForList(sql.toString(),param);
	}
	
	/**
	 *供应商统计领统计数据
	 */
	
	public List getSupplierSpareTableTypeChart(String cate_id,String beginTime,String endTime,String pxtj,String pxlx){
		StringBuilder sql = new StringBuilder();
		Map param = new HashMap();
				sql.append("select t.SUPPLIER_id,t.SUPPLIER_name,t.rksl,t.cksl,t.slsl,t.bssl from (" +
						"SELECT\n" +
						"	spare.SUPPLIER_name,\n" +
						"   spare.supplier_id,\n" +
						"	(\n" +
						"		SELECT\n" +
						"			count(SPARE_PART.id)\n" +
						"		FROM\n" +
						"			SPARE_PART,\n" +
						"			SPARE_PART_STORAGE,\n" +
						"			WORKFLOW_BASE\n" +
						"		WHERE\n" +
						"			SPARE_PART.LINK_STORAGE_ID = SPARE_PART_STORAGE.ID\n" +
						"		AND	WORKFLOW_BASE.id = SPARE_PART_STORAGE.FLOW_ID\n" +
						"		AND SPARE_PART_STORAGE.STATE = 3");
		
				if(StringUtil.isNotBlank(cate_id)){
					sql.append(" and SPARE_PART.CATE_ID=:cate_id ");
					param.put("cate_id", cate_id);
				}	
				if(StringUtil.isNotBlank(beginTime)){
					sql.append(" and WORKFLOW_BASE.ENDTIME>=:beginTime ");
					param.put("beginTime", beginTime+"000000");
				}
				if(StringUtil.isNotBlank(endTime)){
					sql.append(" and WORKFLOW_BASE.ENDTIME<=:endTime ");
					param.put("endTime", endTime+"235959");
				}
				
				
				
				sql.append("AND SPARE_PART.SUPPLIER_id = spare.SUPPLIER_id\n" +
						"	) rksl,\n" +
						"	(\n" +
						"		SELECT\n" +
						"			count(SPARE_PART.id)\n" +
						"		FROM\n" +
						"			SPARE_PART,\n" +
						"			SPARE_PART_DELIVERY,\n" +
						"			SPARE_PART_APPLY_CATE,\n" +
						"			SPARE_PART_APPLY_ENTITY,\n" +
						"			WORKFLOW_BASE\n" +
						"		WHERE\n" +
						"			SPARE_PART_DELIVERY.id = SPARE_PART_APPLY_CATE.DELIVERY_ORDER_ID\n" +
						"		AND SPARE_PART_APPLY_ENTITY.LINK_ID = SPARE_PART_APPLY_CATE.ID\n" +
						"		AND SPARE_PART_APPLY_ENTITY.ENTITY_ID = SPARE_PART.ID\n" +
						"		AND SPARE_PART.STATE = 3\n" +
						"		AND WORKFLOW_BASE.id =SPARE_PART_DELIVERY.FLOW_ID \n" +
						"		AND SPARE_PART_APPLY_ENTITY.STATE = 2");
				
				
				if(StringUtil.isNotBlank(cate_id)){
					sql.append(" and SPARE_PART.CATE_ID=:cate_id ");
					param.put("cate_id", cate_id);
				}	
				if(StringUtil.isNotBlank(beginTime)){
					sql.append(" and WORKFLOW_BASE.ENDTIME>=:beginTime ");
					param.put("beginTime", beginTime+"000000");
				}
				if(StringUtil.isNotBlank(endTime)){
					sql.append(" and WORKFLOW_BASE.ENDTIME<=:endTime ");
					param.put("endTime", endTime+"235959");
				}			
				sql.append("AND SPARE_PART.SUPPLIER_id = spare.SUPPLIER_id\n" +
						"		AND SPARE_PART_DELIVERY.state = 3\n" +
					/*	"		AND (\n" +
						"			(\n" +
						"				SPARE_PART_DELIVERY.APPLY_ORDER_ID IS NOT NULL\n" +
						"				AND SPARE_PART_APPLY_ENTITY.CI_ID IS NOT NULL\n" +
						"			)\n" +
						"			OR SPARE_PART_DELIVERY.APPLY_ORDER_ID IS NULL\n" +
						"		)\n" +*/
						"	) cksl,\n" +
						"	ifnull(\n" +
						"		(\n" +
						"			SELECT\n" +
						"				count(SPARE_PART.id)\n" +
						"			FROM\n" +
						"				SPARE_PART_APPLY,\n" +
						"				SPARE_PART_APPLY_CATE,\n" +
						"				SPARE_PART_APPLY_ENTITY,\n" +
						"				WORKFLOW_BASE,\n" +
						"				SPARE_PART\n" +
						"			WHERE\n" +
						"				SPARE_PART_APPLY_CATE.APPLY_ORDER_ID = SPARE_PART_APPLY.id\n" +
						"			AND SPARE_PART.ID = SPARE_PART_APPLY_ENTITY.ENTITY_ID\n" +
						"			AND SPARE_PART_APPLY_ENTITY.LINK_ID = SPARE_PART_APPLY_CATE.ID\n" +
						"			AND SPARE_PART_APPLY.STATE IN (3, 4, 5)\n" +
						"		    AND WORKFLOW_BASE.id =SPARE_PART_APPLY.FLOW_ID \n" +
						"			AND SPARE_PART_APPLY_ENTITY.STATE = 2");
				
				
				
				if(StringUtil.isNotBlank(cate_id)){
					sql.append(" and SPARE_PART.CATE_ID=:cate_id ");
					param.put("cate_id", cate_id);
				}	
				if(StringUtil.isNotBlank(beginTime)){
					sql.append(" and SPARE_PART_APPLY_ENTITY.GMT_USAGE>=:beginTime ");
					param.put("beginTime", beginTime+"000000");
				}
				if(StringUtil.isNotBlank(endTime)){
					sql.append(" and SPARE_PART_APPLY_ENTITY.GMT_USAGE<=:endTime ");
					param.put("endTime", endTime+"235959");
				}
				
				
				
				
				sql.append("AND SPARE_PART.SUPPLIER_id = spare.SUPPLIER_id\n" +
								"			AND SPARE_PART.state = '3'\n" +
								"			AND SPARE_PART_APPLY_ENTITY.CI_ID IS NOT NULL\n" +
								"		),\n" +
								"		0\n" +
								"	) slsl,\n" +
								"	(\n" +
								"		SELECT\n" +
								"			count(SPARE_PART.id)\n" +
								"		FROM\n" +
								"			OVERAGE_LOSS,\n" +
								"			OVERAGE_LOSS_DETAIL,\n" +
								"			WORKFLOW_BASE,\n" +
								"			SPARE_PART\n" +
								"		WHERE\n" +
								"			OVERAGE_LOSS.id = OVERAGE_LOSS_DETAIL.ORDER_ID\n" +
								"		AND	WORKFLOW_BASE.id = OVERAGE_LOSS.FLOW_ID\n" +
								"		AND SPARE_PART.id = OVERAGE_LOSS_DETAIL.ENTITY_ID");
									
				

				if(StringUtil.isNotBlank(cate_id)){
					sql.append(" and SPARE_PART.CATE_ID=:cate_id ");
					param.put("cate_id", cate_id);
				}	
				if(StringUtil.isNotBlank(beginTime)){
					sql.append(" and WORKFLOW_BASE.ENDTIME>=:beginTime ");
					param.put("beginTime", beginTime+"000000");
				}
				if(StringUtil.isNotBlank(endTime)){
					sql.append(" and WORKFLOW_BASE.ENDTIME<=:endTime ");
					param.put("endTime", endTime+"235959");
				}
				
				sql.append("AND OVERAGE_LOSS.BUS_TYPE = 1\n" +
							"		AND SPARE_PART.state = 2\n" +
							"		AND SPARE_PART.SUPPLIER_id = spare.SUPPLIER_id\n" +
							"	) bssl\n" +
							"FROM\n" +
							"	SPARE_PART spare ,SUPPLIER_INFO where spare.SUPPLIER_id=SUPPLIER_INFO.id and  SUPPLIER_INFO.STATE=1\n)t " +
							"GROUP BY\n" +
							"	t.SUPPLIER_id,t.SUPPLIER_name,t.rksl,t.cksl,t.slsl,t.bssl ");
				sql.append(" order BY "+" t."+ pxtj+"  "+pxlx+"");
				return baseDao.queryForList(sql.toString(),param);
	}
	public List getSpareMainufacturerChart(HttpServletRequest request){
        String beginTime = request.getParameter("beginTime");
        String endTime = request.getParameter("endTime");
        String cate_id = request.getParameter("cate_id");
        String entity_no = request.getParameter("entity_no");
        String name = request.getParameter("name");
        String asset_no = request.getParameter("asset_no");
        String supplier_id = request.getParameter("supplier_id");

        Map param = new HashMap();
        param.put("beginTime", beginTime+"000000");
        param.put("endTime",  endTime+"235959" );
        param.put("cate_id", cate_id);
        param.put("entity_no", "%" + entity_no + "%");
        param.put("name", "%" + name + "%");
        param.put("asset_no", "%" + asset_no + "%");
        param.put("supplier_id", supplier_id );
      //  var param = {'beginTime':beginTime,'endTime':endTime,'cate_id':cate_id,'entity_no':entity_no,'name':name,'asset_no':asset_no,'supplier_id':supplier_id};
        //开始查询
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT\n" +
        		"	SPARE_PART.*, CONCAT(\n" +
        		"		LV1_NAME,\n" +
        		"		'-',\n" +
        		"		LV2_NAME,\n" +
        		"		'-',\n" +
        		"		LV3_NAME\n" +
        		"	) CATENAME\n" +
        		"FROM\n" +
        		"	SPARE_PART,\n" +
        		"	cmdb_ci_category\n" +
        		"WHERE\n" +
        		"	SPARE_PART.CATE_ID = cmdb_ci_category.id\n" +
        		"AND SPARE_PART.state = 1");
        if (StringUtil.notEmpty(cate_id)) {
            sql.append(" AND SPARE_PART.CATE_ID =:cate_id");
        }
        if (StringUtil.isNotBlank(beginTime)) {
            sql.append(" and SPARE_PART.GMT_CREATE>=:beginTime");
        }
        if (StringUtil.isNotBlank(endTime)) {
            sql.append(" and SPARE_PART.GMT_CREATE<= :endTime");
        }
        if (StringUtil.isNotBlank(entity_no)) {
            sql.append(" and SPARE_PART.entity_no like :entity_no ");
        }
        if (StringUtil.isNotBlank(name)) {
            sql.append(" and SPARE_PART.name like :name ");
        }
        if (StringUtil.isNotBlank(asset_no)) {
            sql.append(" and SPARE_PART.asset_no like :asset_no ");
        }
        if (StringUtil.isNotBlank(supplier_id)) {
            sql.append(" and SPARE_PART.supplier_id =:supplier_id ");
        }
        sql.append(" order by SPARE_PART.GMT_CREATE desc ");
        return baseDao.queryForList(sql.toString(), param);
	}
	@Override
	public Map queryStorageData(String ciId) {
		Map data = new HashMap();
		Map label = new HashMap();
		String selectSql = "SELECT * FROM PROP_DATA WHERE BUS_ID =:ciId";
		List<Map> datas = baseDao.queryForList(selectSql,new SqlParameter("ciId",ciId));
		
		if(null!=datas && datas.size()>0) {
			for(Map d : datas) {
				String column_id = ConvertUtil.obj2StrBlank(d.get("COLUMN_ID"));
				String column_value = ConvertUtil.obj2StrBlank(d.get("COLUMN_VALUE"));
				String column_text = ConvertUtil.obj2StrBlank(d.get("COLUMN_TEXT"));
				
				data.put(column_id, column_value);
				label.put(column_id, column_text);
			}
		}
		Map resultMap = new HashMap();
		resultMap.put("data", data);
		resultMap.put("label", label);
		System.out.print("data11111111111111111111111111"+data);
		System.out.print("label11111111111111111111111111"+label);
		
		return resultMap;
	}
	
	
	@Override
	public List queryGroupPropsTwo(String cate) {
		CMDB_CI_CATEGORY category = new CMDB_CI_CATEGORY(cate).getInstanceById();
		String pid = category.getPid();
		Map paramMap = new HashMap();
		paramMap.put("pid", pid);
		paramMap.put("cate", cate);
	
		
		StringBuilder builder = new StringBuilder("SELECT D.* FROM (");
		//先查询分组信息
		String ejGroup = "SELECT A.*,2 AS CATE FROM PROP_GROUP A,CMDB_CI_CATEGORY A1  WHERE A.link_id = A1.id and  A.link_id = :pid AND A.STATE = 1";
		builder.append(ejGroup);
		builder.append(" UNION ");
		String sjGroup = "SELECT B.*,3 AS CATE FROM PROP_GROUP B,CMDB_CI_CATEGORY B1  WHERE B.link_id = B1.ID AND  B.link_id = :cate AND B.STATE = 1";
		builder.append(sjGroup);
		builder.append(") D ORDER BY D.CATE,D.SERIAL ");	
		
		List<Map> groups = baseDao.queryForList(builder.toString(),paramMap);
		System.out.println(JsonUtil.list2Json(groups));
		
		if(null!=groups && groups.size()>0) {
			String selectPropSql = "SELECT A.* FROM PROP_COLUMN A,PROP_GROUP B WHERE A.GROUP_ID = B.ID AND (B.link_id = :pid OR B.link_id = :cate ) AND A.STATE = 1 AND B.STATE = 1  ORDER BY A.SERIAL";
			List<Map> props = baseDao.queryForList(selectPropSql,paramMap);
			
			Map cacheMap = new HashMap();
			Map cacheMap2 = new HashMap();
			if(null!=props && props.size()>0) {
				
				props = queryListValue(props);
				
				
				for(Map prop : props) {
					String groupId = ConvertUtil.obj2StrBlank(prop.get("GROUP_ID"));
					Map m = new HashMap();
					int datatype = ConvertUtil.obj2Int(prop.get("DATATYPE"));
					if(datatype == Constants.CMDB_CI_EXTEND_COLUMN_DATATYPE_TEXT ) {
						m = cacheMap2;
					} else {
						m = cacheMap;
					}
					
					if(m.containsKey(groupId)) {
						List ls = (List)m.get(groupId);
						ls.add(prop);
					} else {
						List ls = new ArrayList();
						ls.add(prop);
						m.put(groupId, ls);
					}
				}
			}
			
			for(Map group : groups) {
				group.put("props", cacheMap.get(group.get("ID")));
			
				group.put("props1", cacheMap2.get(group.get("ID")));
				System.out.print("cacheMap21111111111111111111111111111"+cacheMap2);
			}
		 }
		 return groups;
		}
		
		private List<Map> queryListValue(List<Map> props) {
			String selectSql = "SELECT * FROM PROP_SEL WHERE STATE = 1 ORDER BY SERIAL ";
			List<Map> items = baseDao.queryForList(selectSql);
			Map cacheMap = new HashMap();
			if(null!=items && items.size()>0) {
				for(Map item : items) {
					String extendId = ConvertUtil.obj2StrBlank(item.get("COLUMN_ID"));
					if(cacheMap.containsKey(extendId)) {
						List list = (List)cacheMap.get(extendId);
						list.add(item);
					} else {
						List list = new ArrayList();
						list.add(item);
						cacheMap.put(extendId, list);
					}
				}
			}
			
			for(Map prop : props) {
				String id = ConvertUtil.obj2StrBlank(prop.get("ID"));
				if(cacheMap.containsKey(id)) {
					prop.put("items", cacheMap.get(id));
				}
			}
			
			return props;
		}
		
		@Override
		public Map queryCommonProps(String ciId) {
			String selectSq1 ="SELECT\n" +
								"	p.*\n" +
								"FROM\n" +
								"	(\n" +
								"		SELECT\n" +
								"			g.id,\n" +
								"			g.group_name,\n" +
								"			g.serial\n" +
								"		FROM\n" +
								"			prop_group g,\n" +
								"			cmdb_ci_category c\n" +
								"		WHERE\n" +
								"			g.link_id = c.id\n" +
								"		AND c.state = 1\n" +
								"		AND (\n" +
								"			c.id ='"+ciId+"'\n" +
								/*"			OR c.id =c.LV2_ID \n" +*/
								"		)\n" +
								"	) g,\n" +
								"	prop_column p\n" +
								"WHERE\n" +
								"	g.id = p.group_id\n" +
								"AND p.state = 1\n" +
								"ORDER BY SERIAL\n";
								
			List<Map> commonProps = baseDao.queryForList(selectSq1);
			Map resultMap = new HashMap();
			List commonProp = new ArrayList();
			List textareaProp = new ArrayList();
			
			//如果通用属性扩展下为复选或单选，则查询出其属性
			if(null!=commonProps && commonProps.size()>0) {
				commonProps = queryListValue(commonProps);
				
				for(Map m : commonProps) {
					int datatype = ConvertUtil.obj2Int(m.get("DATATYPE"));
					if(datatype == Constants.CMDB_CI_EXTEND_COLUMN_DATATYPE_TEXT) {
						textareaProp.add(m);
					} else {
						commonProp.add(m);
					}
				}
			}
			
			resultMap.put("commonProp", commonProp);
			resultMap.put("textareaProp", textareaProp);
			
			return resultMap;
		}

	@Override
	public Map getCiCountChart(HttpServletRequest request) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Map getCiMainufacturerChart(String cusId, String queryType) {
		// TODO Auto-generated method stub
		return null;
	}
	@Override
	public Map incidentInfluenceCol(HttpServletRequest request) {
		SqlParameter param = new SqlParameter();
		param.addValue("cusId", request.getParameter("cusId"))
			 .addValue("beginTime", request.getParameter("beginTime")+"000000")
			 .addValue("endTime", request.getParameter("endTime")+"235959")
		     .addValue("lv1Id", request.getParameter("lv1Id"))
		     .addValue("lv2Id", request.getParameter("lv2Id"));
		String sqlString="SELECT\n" +
				"	count(SPARE_PART.id) sum,";
		String lv1Id=request.getParameter("lv1Id");
		String lv2Id=request.getParameter("lv2Id");
		String cusId=request.getParameter("cusId");
		String beginTime=request.getParameter("beginTime");
		String endTime=request.getParameter("endTime");
		/*if(StringUtil.isNotBlank(lv1Id)){
			sqlString+="  and CATE_ID in (select id from CMDB_CI_CATEGORY where lv1_id=:lv1Id and lv=3)";
		}else{

		}*/
		if(StringUtil.isBlank(lv1Id)){
			sqlString+=" b.lv1_name NAME";
		}else if(StringUtil.isNotBlank(lv1Id) && StringUtil.isBlank(lv2Id)){
			sqlString+=" b.lv2_name NAME";
		}else if(StringUtil.isNotBlank(lv1Id) && StringUtil.isNotBlank(lv2Id) ){
			sqlString+=" b.lv3_name NAME";
		}
		sqlString+="  FROM\n" +
				"	SPARE_PART,\n" +
				"	SPARE_PART_APPLY,\n" +
				"	SPARE_PART_APPLY_ENTITY,\n" +
				"	CMDB_CI_CATEGORY a,\n" +
				"	CMDB_CI_CATEGORY b\n" +
				"WHERE\n" +
				"	SPARE_PART_APPLY.id = SPARE_PART_APPLY_ENTITY.ORDER_ID\n" +
				"AND a.id = SPARE_PART.CATE_ID\n" +
				"AND a.id = b.lv3_id\n" +
				"AND SPARE_PART_APPLY_ENTITY.ENTITY_ID = SPARE_PART.id\n" +
				"AND SPARE_PART_APPLY_ENTITY.STATE = 2\n" +
				"AND SPARE_PART_APPLY_ENTITY.CI_ID IS NOT NULL";
		if(StringUtil.isNotBlank(cusId)){
			sqlString+="  and SPARE_PART_APPLY.PROJECT_ID=:cusId";
		}
		
		if(StringUtil.isNotBlank(beginTime)){
			sqlString+="  and SPARE_PART_APPLY_ENTITY.GMT_USAGE>=:beginTime";
		}
		if(StringUtil.isNotBlank(endTime)){
			sqlString+="  and SPARE_PART_APPLY_ENTITY.GMT_USAGE<=:endTime";
		}
		if(StringUtil.isBlank(lv1Id)){
			sqlString+=" group by  b.lv1_name";
		}else if(StringUtil.isNotBlank(lv1Id) && StringUtil.isBlank(lv2Id)){
			sqlString+=" and b.lv1_id=:lv1Id group by  b.lv2_name";
		}else if(StringUtil.isNotBlank(lv1Id) && StringUtil.isNotBlank(lv2Id) ){
			sqlString+=" and b.lv1_id=:lv1Id and  b.lv2_id=:lv2Id group by b.lv3_name ";
		}
		sqlString+=" order by sum desc LIMIT 0,10";
		List<Map> datas = baseDao.queryForList(sqlString,param);
		Map seriesMap = new HashMap();
		List<Map> series = new ArrayList<Map>();
		List seriesData = new ArrayList();
		HashMap chartMap = new HashMap();
		List<String> categories = new ArrayList<String>();
		if(datas!=null && datas.size()>0) {
			for (Map<String, String> category : datas) {
				seriesMap.put("name", "数量");
				Integer aInteger = ConvertUtil.obj2Integer((category.get("sum")));
				seriesData.add(aInteger);
				seriesMap.put("data", seriesData);
				categories.add(ConvertUtil.obj2StrBlank(category.get("name")));
			}
			series.add(seriesMap);
			chartMap.put("series", series);
			chartMap.put("categories", categories);
		}

		System.out.print(chartMap);
		
		return chartMap;
	}
	
}