package com.sharemao.manager.service.impl;

import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import com.sharemao.manager.sms.Submail;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.alibaba.fastjson.JSONObject;
import com.nianzai.base.dao.jdbc.Pager;
import com.nianzai.base.dao.util.DAOHelper;
import com.nianzai.util.DateTimeUtil;
import com.nianzai.util.PropertiesUtil;
import com.nianzai.util.Tools;

import com.sharemao.manager.entity.Account;
import com.sharemao.manager.entity.ISP;
import com.sharemao.manager.entity.Oplog;
import com.sharemao.manager.service.UfiService;
import com.sharemao.manager.service.base.DevBaseService;
import com.sharemao.manager.service.base.RCPService;
import com.sharemao.manager.util.ProjectConstants;
import com.sharemao.manager.util.ProjectTools;
import com.sharemao.rpc.mifi.entity.Result;

import jxl.Sheet;
import jxl.Workbook;

public class UfiServiceImpl extends DevBaseService implements UfiService{

	public static String surl=PropertiesUtil.getValue("server", "surl");
	
	private final Log logger = LogFactory.getLog(UfiServiceImpl.class);
	
	@Override
	public Map<String, Object> getDataList(int orgid, Map<String, String> para) 
	{
		String orgTm = para.get("orgid");
		//代理商查询
		if(!ProjectTools.isEmpty(orgTm) && !orgTm.equals("-1"))
			;
		else
			orgTm=getOrgids(orgid);
		
		String grpid = para.get("grpid");
		if(grpid==null) grpid="";
		if(grpid.equals("-1")) grpid="";
		
		String remk = para.get("remk");
		if(remk==null) remk="";
		
		String devstat = para.get("devstat");
		if(devstat==null) devstat="";
		if(devstat.equals("-1")) devstat="";
		
		String netmode = para.get("netmode");
		if(netmode==null) netmode="";
		if(netmode.equals("-1")) netmode="";
		
		String celladr = para.get("celladr");
		if(celladr==null) celladr="";
		
		String devnum = para.get("devnum");//devid
		if(devnum==null) devnum="";
		
		String pageSize = para.get("rows");//页大小
		String curPage = para.get("page");//当前页
		
		if(Tools.isEmpty(pageSize))
			pageSize="50";
		if(Tools.isEmpty(curPage))
			curPage="1";
		
		List<Map<String,Object>> devLst = new ArrayList<Map<String,Object>>();
		StringBuilder orgbuf = new StringBuilder();
		StringBuilder grpbuf = new StringBuilder();
		StringBuilder userbuf = new StringBuilder();
		Map<String,Object> orgmap = new HashMap<String,Object>();
		Map<String,Map<String,Object>> grpmap = new HashMap<String,Map<String,Object>>();
		Map<String,Object> usermap = new HashMap<String,Object>();
		Map<String,Object> uopenidmap = new HashMap<String,Object>();
		long nowTime = System.currentTimeMillis();
		
		//String url="http://"+surl+"/ufi/getufi.do?pageSize="+pageSize+"&curPage="+curPage+"&orgid="+orgTm+"&devstat="+devstat+"&celladdress="+celladr+"&remk="+remk+"&grpid="+grpid+"&devid="+devnum+"&iccid="+iccid;
		Map<String,String> paramap=new HashMap<String,String>();
		paramap.put("pageSize", pageSize);
		paramap.put("curPage", curPage);
		paramap.put("orgid", orgTm);
		paramap.put("devstat", devstat);
		paramap.put("netmode", netmode);
		paramap.put("celladdress", celladr);
		paramap.put("remk", remk);
		paramap.put("grpid", grpid);
		paramap.put("devid", devnum);
		paramap.put("sort", para.get("sort"));
		paramap.put("order", para.get("order"));
		
//		for (Map.Entry<String, String> entry : paramap.entrySet()) {
//	        String mapKey = entry.getKey();
//	        String mapValue = entry.getValue();
//	        logger.info(mapKey + "：" + mapValue);
//	    }

		Result res=RCPService.getMifiService().getSNs(paramap);

		if(res!=null && res.getTotal()>0) 
		{
			for (int i = 0; i < res.getList().size(); i++)
			{
				String devid=res.getList().get(i);
				
				Map<String,Object> map=new TreeMap<String,Object>();

				//拼装sql
				String sql = "SELECT dev.id,dev.orgid,dev.devid,dev.limtval,dev.devgrpid,dev.appuid,dev.devstat,dev.celladdress,"
						+ "	dev.lastcntm,dev.hardvers,dev.remk,dev.mode,dev.used,dev.usedct,dev.usedcu,dev.usedcm,dev.simshud,dev.flag,dev.gsim,dev.esim,dev.hardvers FROM "+ProjectTools.getTableNameBySN(devid)+" dev "
						+ "	WHERE dev.devid='"+devid+"'";
				map=RCPService.getMifiService().getMap(sql);
				//List<Map<String, Object>> list=DAOHelper.find("mifi", sql);
//				if(list!=null && list.size()>0)
//					map=list.get(0);
				
				sql ="SELECT sta.ssid,sta.battery,sta.wificount,sta.wifipsw,sta.speed,sta.sigint,sta.imei,sta.ssid5g,sta.wifipsw5g FROM devstatus sta WHERE sta.devid = '" + devid+"'" ;
				//List<Map<String, Object>> dlst = baseDao.getMapList(sql);
				List<Map<String, Object>> dlst =RCPService.getBaseService().getMapList(sql);
				if(dlst!=null && dlst.size()>0)
				{
					Map<String, Object> stamap = dlst.get(0);
					map.put("ssid", stamap.get("ssid"));
					map.put("wifipsw", stamap.get("wifipsw"));
					map.put("wificount", stamap.get("wificount"));
					map.put("speed", stamap.get("speed"));
					map.put("sigint", stamap.get("sigint"));
					map.put("imei", stamap.get("imei"));
					map.put("battery", stamap.get("battery"));
					map.put("ssid5g", stamap.get("ssid5g"));
					map.put("wifipsw5g", stamap.get("wifipsw5g"));
				}
				
				String inisql = "SELECT grpname FROM devinit WHERE devid="+devid;
				List<Map<String, Object>> dilst =RCPService.getBaseService().getMapList(inisql);
				map.put("grpnamefix", "");
				if(dilst!=null && dilst.size()>0)
				{
					Map<String, Object> inimap = dilst.get(0);
					map.put("grpnamefix", inimap.get("grpname"));
				}
				
				int upTime = 1*60*1000*5;
				long lastcntm = Long.parseLong(String.valueOf(map.get("lastcntm")));
				map.put("lastupd", DateTimeUtil.getDate(lastcntm, "yyyy年MM月dd日 HH:mm:ss"));
				String limtval = String.valueOf(map.get("limtval"));
				long acTime = nowTime - upTime;
				if(lastcntm > acTime){
					map.put("status",1);
					if(!limtval.equals("0")){
						map.put("status",limtval+"K");
					}
				}else{
					map.put("status", 0);
				}
				
				devLst.add(map);
				
				orgbuf.append(","+map.get("orgid"));
				grpbuf.append(","+map.get("devgrpid"));
				userbuf.append(","+map.get("appuid"));
	        }
		}
		
		if(devLst.size()>0)
		{
			String sql = "select id,orgname from organize where id in ("+orgbuf.substring(1)+")";
			//List<Map<String,Object>> orglst = baseDao.getMapList(sql);
			List<Map<String,Object>> orglst =RCPService.getBaseService().getMapList(sql);
			if(null!=orglst&&orglst.size()>0){
				for(Map<String,Object> omap:orglst){
					orgmap.put(String.valueOf(omap.get("id")), omap.get("orgname"));
				}
			}
			//sql = "select id,grpname,devtype,gongc from devicegroup where id in ("+grpbuf.substring(1)+")";
			sql = "SELECT grp.id,grp.grpname,grp.devtype,grp.gongc,devtype.typename FROM devicegroup grp,devtype WHERE devtype.id=grp.devtype and grp.id in ("+grpbuf.substring(1)+")";
			//List<Map<String,Object>> grplst = baseDao.getMapList(sql);
			List<Map<String,Object>> grplst =RCPService.getBaseService().getMapList(sql);
			if(null!=grplst&&grplst.size()>0){
				for(Map<String,Object> gmap:grplst){
					//grpmap.put(String.valueOf(gmap.get("id")), gmap.get("grpname"));
					if(gmap.get("gongc")==null)
						gmap.put("gongc", "");
					if(gmap.get("devtype")==null)
						gmap.put("devtype", 0);
					grpmap.put(String.valueOf(gmap.get("id")), gmap);
				}
			}
			
			sql = "select id,phonenum,openid from appuser where id in ("+userbuf.substring(1)+")";
			//List<Map<String,Object>> usrlst = baseDao.getMapList(sql);
			List<Map<String,Object>> usrlst =RCPService.getBaseService().getMapList(sql);
			if(null!=usrlst&&usrlst.size()>0){
				for(Map<String,Object> umap:usrlst){
					usermap.put(String.valueOf(umap.get("id")), umap.get("phonenum"));
					uopenidmap.put(String.valueOf(umap.get("id")), umap.get("openid"));
				}
			}
			
			for(Map<String,Object> dmap:devLst)
			{
				String orgstr = String.valueOf(dmap.get("orgid"));
				String grpstr = String.valueOf(dmap.get("devgrpid"));
				String phonenumstr = String.valueOf(dmap.get("appuid"));
				dmap.put("orgname", orgmap.get(orgstr));
				dmap.put("grpname", grpmap.get(grpstr).get("grpname").toString());
				dmap.put("devtype", grpmap.get(grpstr).get("devtype").toString());
				dmap.put("typename", grpmap.get(grpstr).get("typename").toString());
				dmap.put("gongc", grpmap.get(grpstr).get("gongc").toString());
				dmap.put("phonenum", usermap.get(phonenumstr));
				dmap.put("openid", uopenidmap.get(phonenumstr));
				String devid=dmap.get("devid").toString();
				if(devid.startsWith("1511") || devid.startsWith("1501"))
				{
					dmap.put("devtype", 0);
				}
				if(devid.startsWith("1304") || devid.startsWith("1301"))
				{
					dmap.put("devtype", 1);
				}
				if(devid.startsWith("5001") || devid.startsWith("5002"))
				{
					dmap.put("devtype", 2);
				}
			}
		}
		Map<String,Object> devMap =new HashMap<String,Object>();
		devMap.put("rows", devLst);
		devMap.put("total", res.getTotal());
		//返回页面信息
		return devMap;
	}
	
	@Override
	public Map<String, Object> getItemDetail(Map<String,String> para) {
		Pager pc=new Pager();
		pc.setCurrentPage(1);
		pc.setPageSize(1);
		
		String devid = para.get("devid");
//		String sql ="SELECT dev.gsim,dev.esim,dev.devdid,dev.iccid,dev.limtval,dev.authmd,dev.simshud,sta.ssid,sta.battery,sta.imei,"
//				+ "	sta.wificount,sta.wifipsw,sta.gsigint,sta.sigint,sta.ispid,sta.apnname,sta.speed FROM device dev "
//				+ "	LEFT JOIN devstatus sta ON sta.devid = dev.devid WHERE dev.devid = '" + devid+"'" ;
		List<Map<String, Object>> dlst = new ArrayList<Map<String, Object>>();
		String sql="select * from "+ProjectTools.getTableNameBySN(devid)+" where devid='"+devid+"'";
		Map<String, Object> map=RCPService.getMifiService().getMap(sql);
//		Map<String, Object> dmap = dlst.get(0);

		
		sql="select * from devstatus where devid='"+devid+"'";
		//Map<String, Object> m=baseDao.getMapList(sql).get(0);
		Map<String, Object> m=RCPService.getBaseService().getMapList(sql).get(0);
		map.put("imei", m.get("imei"));
		map.put("battery", m.get("battery"));
		map.put("apnname", m.get("apnname"));
		map.put("speed", m.get("speed"));
		map.put("ispid", m.get("ispid"));
		map.put("suspend", m.get("suspend"));
		
		String ispstr = String.valueOf(map.get("ispid"));
		
		if(ProjectTools.isEmpty(ispstr)){
			map.put("siminfo", null);
		}else{
			try {
				map.put("siminfo", ProjectTools.getOprtStr(Integer.parseInt(ispstr)));
			} catch (NumberFormatException e) {
				map.put("siminfo", ispstr);
			}
		}
		
		dlst.add(map);
		//返回页面信息
		Map<String,Object> pginfo = new HashMap<String,Object>();
		pginfo.put("total", 1);
		pginfo.put("rows", dlst);
		return pginfo;
	}
	
	@Override
	public int devchag(Map<String, String> para,Account account) throws ParseException {
		int orgid=account.getOrgid();
		int accid=account.getId();
		String uname=account.getUsername();
		List<String> sqlList = new ArrayList<String>();
		
		String devid = para.get("devid");	//设备ID
		String pkgid = para.get("pkgid");
		String timeStart = para.get("timeStart");
		int costType = Integer.parseInt(para.get("costType"));		//支付方式
		
		String sql = "SELECT pkv.pkid,pkv.price,pkv.costprice,pkg.pkgValue,pkg.pkgLimit,pkv.pkname,"
				+ "	pkg.areaid,pkg.invalidtime,pkg.mode FROM packageview pkv "
				+ "	LEFT JOIN package pkg ON pkg.id = pkv.pkid"
				+ "	WHERE pkv.pkid = "+pkgid+" AND pkv.orgid = "+orgid;
		//List<Map<String, Object>> consLst = baseDao.getMapList(sql);
		List<Map<String, Object>> consLst =RCPService.getBaseService().getMapList(sql);
		if(consLst == null || consLst.size()<1)
			return 1;
		Map<String,Object> consMap = consLst.get(0);
		String pkgName = String.valueOf(consMap.get("pkname"));								//套餐名
		int pkgLimit = Integer.parseInt(String.valueOf(consMap.get("pkgLimit")));
		int costPrice = Integer.parseInt(String.valueOf(consMap.get("costprice")));
		String areaId = String.valueOf(consMap.get("areaid"));
		int expiryDay = 0;			//有效期
		int mode = Integer.parseInt(String.valueOf(consMap.get("mode")));					//套餐模式
		int invalidtime = Integer.parseInt(String.valueOf(consMap.get("invalidtime")));		//有效时长
		int pkgValue = Integer.parseInt(String.valueOf(consMap.get("pkgValue")));			//流量大小
		int price = Integer.parseInt(String.valueOf(consMap.get("price")));					//套餐价格
		

		String orderid = "SYS"+Long.toString(System.currentTimeMillis()/1000)+ProjectTools.buildRandomStr(3);
		
		String nowTime = DateTimeUtil.longtime();
		/*********************************代理预付款 - BEGIN************************************/
//		long costNum = 0;		//代理需付款
//		int balance = 0;		//代理余额
//		
//		if(orgid != 1 && orgid != 2){
//			
//			String orgSql = "SELECT balance,flowbal FROM organize WHERE id = "+orgid;
//			//List<Map<String,Object>> orgLst = baseDao.getMapList(orgSql);
//			List<Map<String,Object>> orgLst =RCPService.getBaseService().getMapList(orgSql);
//			if(orgLst != null && orgLst.size()>0){
//				
//				balance = Integer.parseInt(String.valueOf(orgLst.get(0).get("balance")));
//				long flowbal = Long.parseLong(String.valueOf(orgLst.get(0).get("flowbal")));
//				if(costType == 0){
//					if(balance < costPrice)
//						return 6;
//					
//					costNum = balance - costPrice;
//					
//					String costSql = "UPDATE organize SET balance = "+costNum+" WHERE id = '"+orgid+"'";
//					String deSql = "INSERT INTO deductrcd (orgid,orderid,amount,befval,aftval,crttm,devid,paytype,devorgid) VALUES("
//							+ "'"+orgid+"','"+orderid+"',"+costPrice+","+balance+","+costNum+",'"+nowTime+"','"+devid+"',6,"+orgid+")";
//					sqlList.add(costSql);
//					sqlList.add(deSql);
//				}else if(costType == 1 && pkgLimit == 0){
//					if(flowbal < pkgValue)
//						return 6;
//					
//					costNum = flowbal - pkgValue;
//					
//					String costSql = "UPDATE organize SET flowbal = "+costNum+" WHERE id = '"+orgid+"'";
//					String deSql = "INSERT INTO deductrcd (orgid,orderid,pkvalue,befpkv,aftpkv,crttm,devid,paytype,devorgid) VALUES("
//							+ "'"+orgid+"','"+orderid+"',"+pkgValue+","+flowbal+","+costNum+",'"+nowTime+"','"+devid+"',7,"+orgid+")";
//					sqlList.add(costSql);
//					sqlList.add(deSql);
//				}else
//					return 10;
//			}else{
//				return 9;
//			}
//		}
		/*********************************代理预付款 - END************************************/
		//保存订单
		int devorg=orgid;
		int devgrpid=0;
		int appuid=0;
		String devsql="select orgid,devgrpid,appuid from "+ProjectTools.getTableNameBySN(devid)+" where devid = '"+devid+"'";
		Map<String,Object> devmap=RCPService.getMifiService().getMap(devsql);
		if(devmap!=null)
		{
			devorg=Integer.parseInt(devmap.get("orgid").toString());
			devgrpid=Integer.parseInt(devmap.get("devgrpid").toString());
			appuid=Integer.parseInt(devmap.get("appuid").toString());
		}
		
		String rcdSql = "INSERT INTO allorder(onum,appuid,orgid,packid,oprice,otype,ptype,ostat,crttm,mdftm,devid,acid,opt) VALUES("
				+ "	'"+orderid+"',"+ appuid+","+devorg+","+pkgid+",'"+price+"',0,6,2,'"+nowTime+"','"+nowTime+"','"+devid+"',"+accid+",'"+uname+"')";
		//sqlList.add(rcdSql);
		//baseDao.excuBatch(sqlList);
		logger.info(rcdSql);
		RCPService.getBaseService().execSql(rcdSql);
		
//		Map<String, Object> rm=RCPService.getAppService().rechargeByOrderID(orderid,orgid,timeStart);
//		int c=(Integer)rm.get("code");
//		if(c>0)
//			return c;
		
		/*********************************写入套餐 - BEGIN***********************************/
		int remainValue=pkgValue;
		if(pkgLimit>0 && pkgValue>40000)
			remainValue=40000;
		
		String suSql = "INSERT INTO usersuit(orgid,devid,remain,areaid,packid,limitval,effect,invalid,crttm,orderid,pkvalue) VALUES";
		
		Date time = new Date();
		if(!ProjectTools.isEmpty(timeStart)){
			SimpleDateFormat formatter = new SimpleDateFormat( "MM/dd/yyyy");
			time =  formatter.parse(timeStart);
		}
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
		
		String timesStart = sdf.format(time.getTime()) + "000000";
		String timeEnd = "";
		
		if(mode == 0){
			String dateStart = sdf.format(time.getTime()) + "000000";
			if(invalidtime == 0)	//一天
				time = DateTimeUtil.addDay(time, 1);
			if(invalidtime == 1)	//一月
				time = ProjectTools.addMonth(time, 1);
			if(invalidtime == 2)	//一季
				time = ProjectTools.addMonth(time, 3);
			if(invalidtime == 3)	//半年
				time = ProjectTools.addMonth(time, 6);
			if(invalidtime == 4)	//一年
				time = ProjectTools.addMonth(time, 12);
			if(invalidtime == 5)	//两年
				time = ProjectTools.addMonth(time, 24);
			if(invalidtime == 6)	//一周
				time = DateTimeUtil.addDay(time, 7);
			if(invalidtime == 7)	//三年
				time = ProjectTools.addMonth(time, 36);
			if(invalidtime == 8)	//三天
				time = DateTimeUtil.addDay(time, 3);
			if(invalidtime == 9)	//2天
				time = DateTimeUtil.addDay(time, 2);			
			
			String dateEnd = sdf.format(time.getTime()) + "000000";
			timeEnd = sdf.format(time.getTime()) + "000000";
			suSql += "("+orgid+",'"+devid+"',"+remainValue+","+areaId+","+pkgid+","+pkgLimit+",'"+dateStart+"','"+dateEnd+"','"+nowTime+"','"+orderid+"',"+pkgValue+")";
			
		}else if(mode == 1){
			if(invalidtime == 0)	//一天
				expiryDay = 1;
			if(invalidtime == 1)	//一月
				expiryDay = 30;
			if(invalidtime == 2)	//一季
				expiryDay = 90;
			if(invalidtime == 3)	//半年
				expiryDay = 180;
			if(invalidtime == 4)	//一年
				expiryDay = 365;
			if(invalidtime == 5)	//两年
				expiryDay = 730;
			if(invalidtime == 6)	//一周
				expiryDay = 7;
			if(invalidtime == 7)	//三年
				expiryDay = 1095;
			if(invalidtime == 8)	//三天
				expiryDay = 3;
//			pkgValue = pkgValue/expiryDay;
			
			for(int i=0;i<expiryDay;i++){
				String dateStart = sdf.format(time.getTime()) + "000000";
				time = addDay(time, 1);
				String dateEnd = sdf.format(time.getTime()) + "000000";
				timeEnd = sdf.format(time.getTime()) + "000000";
				suSql += "("+orgid+",'"+devid+"',"+remainValue+","+areaId+","+pkgid+","+pkgLimit+",'"+dateStart+"','"+dateEnd+"','"+nowTime+"','"+orderid+"',"+pkgValue+"),";
			}
			suSql = suSql.substring(0,suSql.length()-1);
			
		}else if(mode == 2){
			if(invalidtime == 1)	//一月
				expiryDay = 1;
			if(invalidtime == 2)	//一季
				expiryDay = 3;
			if(invalidtime == 3)	//半年
				expiryDay = 6;
			if(invalidtime == 4)	//一年
				expiryDay = 12;
			if(invalidtime == 5)	//两年
				expiryDay = 24;
			if(invalidtime == 7)	//三年
				expiryDay = 36;
			if(invalidtime == 11)	//7个月
				expiryDay = 7;
			if(invalidtime == 12)	//14个月
				expiryDay = 14;
			if(invalidtime == 18)	//15个月
				expiryDay = 15;
			if(invalidtime == 13)	//2年半
				expiryDay = 30;
			if(invalidtime == 14)	//3年8个月
				expiryDay = 44;
			if(invalidtime == 15)	//4年
				expiryDay = 48;
			if(invalidtime == 16)	//5年
				expiryDay = 60;
			if(invalidtime == 17)	//6年
				expiryDay = 72;

			
			for(int i=0;i<expiryDay;i++){
				String dateStart = sdf.format(time.getTime()) + "000000";
				time = ProjectTools.addMonth(time, 1);
				String dateEnd = sdf.format(time.getTime()) + "000000";
				timeEnd = sdf.format(time.getTime()) + "000000";
				suSql += "("+orgid+",'"+devid+"',"+remainValue+","+areaId+","+pkgid+","+pkgLimit+",'"+dateStart+"','"+dateEnd+"','"+nowTime+"','"+orderid+"',"+pkgValue+"),";
			}
			suSql = suSql.substring(0,suSql.length()-1);
		}
		sqlList.add(suSql);
		
		/**************************************写入套餐 - END**************************************/
		sql = "INSERT INTO oplog (accid,orgid,optype,content,appuid,packid,orderid,crttm,devid) VALUES("
				+ accid+","+orgid+",1,'设备充值>>>设备ID:"+devid+",充值套餐-"+pkgName+"','0',"+pkgid+",'"+orderid+"',"+DateTimeUtil.longtime()+",'"+devid+"' )";
		
		String pkgSql = "INSERT INTO packrcd(devid,pkid,pkname,orderid,crttm,datastart,dataend,orgid) VALUES"
				+ "('"+devid+"','"+pkgid+"','"+pkgName+"','"+orderid+"',"+DateTimeUtil.longtime()+","+timesStart+","+timeEnd+","+orgid+")";
		sqlList.add(pkgSql);
		sqlList.add(sql);
		logger.info(sqlList);
		baseDao.excuBatch(sqlList);
		//更改设备状态为正常
		if(devgrpid == 286)
		{//新讯需要解除限速
			String staSql = "UPDATE "+ProjectTools.getTableNameBySN(devid)+" SET devstat = 1, iflimt=3, limtval=0 WHERE devid = '"+devid+"'";
			//DAOHelper.execute("mifidevice", staSql);
			RCPService.getMifiService().execSql(staSql);
		} else {
			String staSql = "UPDATE "+ProjectTools.getTableNameBySN(devid)+" SET devstat = 1 WHERE devid = '"+devid+"'";
			//DAOHelper.execute("ufidevice", staSql);
			RCPService.getMifiService().execSql(staSql);
		}
		
		Map<String,String> simtypemap=getSimType(devid);
		if(simtypemap.get("simtype")!=null)
		{
			String simtype=simtypemap.get("simtype").toString();
			String iccid=simtypemap.get("iccid").toString();
			String kasid=simtypemap.get("kasid").toString();
			String accessNumber=simtypemap.get("access_number").toString();
			if(simtype.equals("1"))
			{
				logger.info("simtype devid="+devid);
				String simtasksql="insert into simtask(iccid,optask,crttm,kasid,devid,accessNumber) values ('"+iccid+"',2,"+DateTimeUtil.longtime()+","+kasid+",'"+devid+"','"+accessNumber+"')";
				logger.info(simtasksql);
				//DAOHelper.execute("mifi", simtasksql);
				RCPService.getBaseService().execSql(simtasksql);
			}
		}

        //发短信通知
        if (!ProjectTools.isEmpty(devid)) {
            sql = "select phonenum from appuser where defaultdev='" + devid + "'";
            List<Map<String, Object>> phonenumList = baseDao.getMapList(sql);
            if (phonenumList != null && phonenumList.size() > 0) {
                Map<String, Object> phonenumMap = phonenumList.get(0);
                String phonenum = String.valueOf(phonenumMap.get("phonenum"));
                if (!ProjectTools.isEmpty(phonenum) && !pkgName.contains("赠送")) {
                    Map<String, String> varsMap = new HashMap<String, String>();
                    varsMap.put("pkgName", pkgName);
                    String vars = JSONObject.toJSONString(varsMap);
                    Submail.xsend(phonenum, "g1WpD4", vars);
                }
            }
        }
		
		return 0;
	}
	
	public static int getPKTypeByDevid(String devid,String devgrpid)
	{
		int packType=0;
		if(devid.startsWith("1511") || devid.startsWith("1501") || devid.startsWith("1301"))
			packType=0;
		else if(devid.startsWith("1304") )
			packType=1;
		else if(devid.startsWith("5001") || devid.startsWith("5002"))
			packType=2;
		else
		{
			String typesql="SELECT * FROM `devicegroup` WHERE id="+devgrpid;
			Map<String,Object> result=RCPService.getBaseService().getMap(typesql);
			if(result!=null && result.size()>0 && result.get("devtype")!=null)
			{
				packType=Integer.parseInt(result.get("devtype").toString());
			}
		}
		return packType;
	}
	
	@Override
	public int devchagspkgs(Map<String, String> para, int orgid, int accid) throws ParseException {
		
		List<String> sqlList = new ArrayList<String>();
		
		String devid = para.get("devid");	//设备ID
		String spkgid = para.get("spkgid");
		String timeStart = para.get("effect");
		int costType = Integer.parseInt(para.get("costType"));		//支付方式
		
		String sql = "SELECT id,price,name,invalidtime FROM speedpack WHERE id = "+spkgid;
		//List<Map<String, Object>> consLst = baseDao.getMapList(sql);
		List<Map<String, Object>> consLst =RCPService.getBaseService().getMapList(sql);
		if(consLst == null || consLst.size()<1)
			return 1;
		Map<String,Object> consMap = consLst.get(0);
		String name = String.valueOf(consMap.get("name"));								//加速包名
		int price = Integer.parseInt(String.valueOf(consMap.get("price")));				//价格
		int expiryDay = 0;			//有效期
		int invalidtime = Integer.parseInt(String.valueOf(consMap.get("invalidtime")));	//有效时长
		
		
		String orderid = "SYS"+Long.toString(System.currentTimeMillis()/1000)+ProjectTools.buildRandomStr(3);
		
		String nowTime = DateTimeUtil.longtime();

		//保存订单
		int devorg=orgid;
		int appuid=0;
		String devsql="select orgid,appuid from "+ProjectTools.getTableNameBySN(devid)+" where devid = '"+devid+"'";
		Map<String,Object> devmap=RCPService.getMifiService().getMap(devsql);
		if(devmap!=null)
		{
			devorg=Integer.parseInt(devmap.get("orgid").toString());
			appuid=Integer.parseInt(devmap.get("appuid").toString());
		}
		
		String rcdSql = "INSERT INTO allorder(onum,appuid,orgid,packid,oprice,otype,ptype,ostat,crttm,mdftm,devid,acid) VALUES("
				+ "	'"+orderid+"',0,"+devorg+","+spkgid+",'"+price+"',3,6,2,'"+nowTime+"','"+nowTime+"','"+devid+"',"+accid+")";

		logger.info(rcdSql);
		RCPService.getBaseService().execSql(rcdSql);
		
		
		/*********************************写入加速包 - BEGIN***********************************/
		
		String spSql = "INSERT INTO userspeedpack (orderid,appuid,devid,speedpackid,effect,invalid,state) VALUES ('";
		
		Date time = new Date();
		String buytm = (new SimpleDateFormat("yyyyMMddHHmmss")).format(time);
		if(!ProjectTools.isEmpty(timeStart)){
			SimpleDateFormat formatter = new SimpleDateFormat( "MM/dd/yyyy");
			time =  formatter.parse(timeStart);
		}
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
		
		String timesStart = sdf.format(time.getTime()) + "000000";
		String timeEnd = "";

		if(invalidtime == 1)	//一月
			expiryDay = 1;
		if(invalidtime == 2)	//一季
			expiryDay = 3;
		if(invalidtime == 3)	//半年
			expiryDay = 6;
		if(invalidtime == 4)	//一年
			expiryDay = 12;
		if(invalidtime == 5)	//两年
			expiryDay = 24;
		if(invalidtime == 7)	//三年
			expiryDay = 36;
		if(invalidtime == 11)	//7个月
			expiryDay = 7;
		if(invalidtime == 12)	//14个月
			expiryDay = 14;
		if(invalidtime == 18)	//15个月
			expiryDay = 15;
		if(invalidtime == 13)	//2年半
			expiryDay = 30;
		if(invalidtime == 14)	//3年8个月
			expiryDay = 44;
		if(invalidtime == 15)	//4年
			expiryDay = 48;
		if(invalidtime == 16)	//5年
			expiryDay = 60;
		if(invalidtime == 17)	//6年
			expiryDay = 72;
		String dateStart = sdf.format(time.getTime()) + "000000";
		time = ProjectTools.addMonth(time, expiryDay);
		String dateEnd = sdf.format(time.getTime()) + "000000";
		timeEnd = sdf.format(time.getTime()) + "000000";
		
		String sqlspp = "INSERT INTO userspeedpack (orderid,appuid,devid,speedpackid,effect,invalid,state,buytm,orgid) VALUES ('"
				+ orderid+"',"+appuid+",'"+devid+"',"+spkgid+","+dateStart+","+dateEnd+",1,"+buytm+","+devorg+")";
		logger.info(sqlspp);
		sqlList.add(sqlspp);
		
		/**************************************写入套餐 - END**************************************/
		sql = "INSERT INTO oplog (accid,orgid,optype,content,appuid,packid,orderid,crttm,devid) VALUES("
				+ accid+","+orgid+",1,'设备充值>>>设备ID:"+devid+",充值加速包-"+name+"','0',"+spkgid+",'"+orderid+"',"+DateTimeUtil.longtime()+",'"+devid+"' )";
		
		sqlList.add(sql);
		baseDao.excuBatch(sqlList);
		
		return 0;
	}
	public Map<String, Object> getSPkgPriceAndEffect(Map<String, String> para, int orgid, int accid) throws ParseException 
	{
		Map<String, Object> result = new HashMap<String, Object>();
		SimpleDateFormat formatter = new SimpleDateFormat( "MM/dd/yyyy");
		Date d = new Date();
		result.put("price", 0);
		result.put("effect", formatter.format(d));
		
		String devid = para.get("devid");	//设备ID
		String spkgid = para.get("spkgid");
		
		String sql = "SELECT id,price,name,invalidtime FROM speedpack WHERE id = "+spkgid;
		//List<Map<String, Object>> consLst = baseDao.getMapList(sql);
		List<Map<String, Object>> consLst =RCPService.getBaseService().getMapList(sql);
		if(consLst == null || consLst.size()<1)
			return result;
		Map<String,Object> consMap = consLst.get(0);
		int price = Integer.parseInt(String.valueOf(consMap.get("price")));				//价格
		result.put("price", price);
		
		String devsql="select orgid,appuid from "+ProjectTools.getTableNameBySN(devid)+" where devid = '"+devid+"'";
		Map<String,Object> devmap=RCPService.getMifiService().getMap(devsql);
		int appuid=0;
		if(devmap!=null)
		{
			appuid=Integer.parseInt(devmap.get("appuid").toString());
		}
		String sqlsel = "select max(invalid) invalid from userspeedpack where state=1 and appuid="+appuid+" and devid='"+devid+"'";
		List<Map<String, Object>> list = baseDao.getMapList(sqlsel);
		if(null!=list && list.size() > 0){
			// 之前购买过加速包
			Map<String, Object> map = list.get(0);
			String invalid = String.valueOf(map.get("invalid"));
			if (invalid != null && !invalid.isEmpty() && !"null".equals(invalid)) {
			    try {
			    	SimpleDateFormat sdf2 = new SimpleDateFormat("yyyyMMddHHmmss");
			        Date invalidDate = sdf2.parse(invalid);
			        if (d.before(invalidDate)) {
			        	// 之前购买的加速包没有到期，自动续期
			        	d = DateTimeUtil.addDay(invalidDate, 1);
			        	result.put("effect", formatter.format(d));
			        }
			    } catch (Exception e) {
			        logger.error("日期格式解析错误: " + e.getMessage());
			    }
			}
		}
		
		return result;
	}

	@Override
	public int editDev(Account account, Map<String, String> para)throws ParseException 
	{
		String devid = para.get("devid");
		
		if(ProjectTools.isEmpty(devid))
			return 1;
		
		String devstat = para.get("devstat");
		String remk = para.get("remk");
		String simshud = para.get("simshud");
		String mode = para.get("mode");
		
//		String wifiBand = para.get("wifiBand");
//		String sql = "UPDATE "+ProjectTools.getTableNameBySN(devid)+" SET devstat='"+devstat+"',remk='"+remk+"',mode="+mode+",wifiBand="+wifiBand;

        Map<String, Object> originMap = RCPService.getMifiService().getMap("select devstat,remk,mode,simshud from " + ProjectTools.getTableNameBySN(devid) + " where devid='" + devid + "'");
        String devstatOrigin = originMap.get("devstat") == null ? null:String.valueOf(originMap.get("devstat"));
        String remkOrigin = originMap.get("remk") == null ? null:String.valueOf(originMap.get("remk"));
        String simshudOrigin = originMap.get("simshud") == null ? null:String.valueOf(originMap.get("simshud"));
        String modeOrigin = originMap.get("mode") == null ? null:String.valueOf(originMap.get("mode"));
        Map<String,String> originMap2 = new HashMap<String, String>();
        originMap2.put("devstat", devstatOrigin);
        originMap2.put("remk", remkOrigin);
        originMap2.put("simshud", simshudOrigin);
        originMap2.put("mode", modeOrigin);
        Map<String, String> logOriginMap = getLogMap(originMap2, devid, devstatOrigin, remkOrigin, simshudOrigin, modeOrigin);

        String sql = "UPDATE "+ProjectTools.getTableNameBySN(devid)+" SET devstat='"+devstat+"',remk='"+remk+"',mode="+mode;
		if(!ProjectTools.isEmpty(simshud))
			sql += ",simshud='"+simshud+"'";
		sql += " WHERE devid = '"+devid+"'";
		//DAOHelper.execute("ufidevice", sql);
		RCPService.getMifiService().execSql(sql);
		
		logger.info("修改设备状态：{devid："+devid+"，devstat："+devstat+",simshud="+simshud+"，remk："+remk+"，mode："+mode+"}");
		logger.info(sql);
		Map<String, String> logMap = getLogMap(para, devid, devstat, remk, simshud, mode);
		Oplog log = new Oplog(account.getId(), account.getOrgid(), 3, "修改设备属性，参数>>"+logMap+"；原数据>>"+logOriginMap, Long.parseLong(DateTimeUtil.longtime()),devid,0,0,"");
		baseDao.addObj(log);
		return 0;
	}

	private Map<String, String> getLogMap(Map<String, String> para, String devid, String devstat, String remk,
			String simshud, String mode) {
		Map<String, String> logMap = new LinkedHashMap<String, String>();
		try {
            if (devid!=null) {
                logMap.put("设备号", devid);
            }
			if (!ProjectTools.isEmpty(devstat)) {
				String[] devstatarr = { "禁用", "启用", "流量不足", "未绑定", "未实名", "", "", "", "", "eSim失效" };
				try {
					logMap.put("设备状态", devstatarr[Integer.parseInt(devstat)]);
				} catch (Exception e) {
					e.printStackTrace();
					logMap.put("设备状态", devstat);
				}
			}
			if (!ProjectTools.isEmpty(mode)) {
				String[] modearr = { "", "云卡", "内置卡1", "外插卡", "内置卡2", "自动", "eSim移动", "eSim联通", "eSim电信", "终端设置" };
				try {
                    String m;
                    if (mode.equals("23")){
                        m = "指定电信";
                    } else {
                        m = modearr[Integer.parseInt(mode)];
                    }
					logMap.put("运行模式", m);
				} catch (Exception e) {
					e.printStackTrace();
					logMap.put("运行模式", mode);
				}
			}
			if (!ProjectTools.isEmpty(para.get("wifiBand"))) {
				String[] wifiBandarr = { "", "2.4G", "5.8G"};
				try {
					logMap.put("WiFi模式", wifiBandarr[Integer.parseInt(para.get("wifiBand"))]);
				} catch (Exception e) {
					e.printStackTrace();
					logMap.put("WiFi模式", para.get("wifiBand"));
				}
			}
			if (!ProjectTools.isEmpty(para.get("iccid"))) {
				logMap.put("iccid", para.get("iccid"));
			}
			if (!ProjectTools.isEmpty(remk)) {
				logMap.put("备注", remk);
			}
			if (!ProjectTools.isEmpty(simshud)) {
				Map<String, String> simshudMap=new HashMap<String, String>();
				simshudMap.put("0", "自动");
				simshudMap.put("46003", "电信");
				simshudMap.put("46001", "联通");
				simshudMap.put("46000", "移动");
				String simshuVal=simshudMap.get(simshud);
				if (simshuVal!=null) {
					logMap.put("选卡信号", simshuVal);
				} else {
					logMap.put("选卡信号", simshud);
				}
			}
            if (para.get("ip")!=null) {
                logMap.put("ip", para.get("ip"));
            }
		} catch (Exception e) {
			e.printStackTrace();
			logMap=para;
		}
		return logMap;
	}
	
	@Override
	public Map<String, Object> doImportData(InputStream excelis, Account account, Map<String, String> para) throws Exception 
	{
		String orgid = para.get("orgid");
		String grpid = para.get("grpid");
//		String dftsuit = para.get("dftsuit");//是否充值默认套餐
		String mode = para.get("mode");
		String copu=para.get("copu");
		String grpname = para.get("grpname");
		
		Map<String,Object> resultMap = new HashMap<String, Object>();
		
//		String suitval = String.valueOf(baseDao.getOneField(" SELECT IFNULL(pkgValue,0) AS pkgValue FROM package WHERE id ="+ProjectConstants.testPkgId).get(0));
//		if(!ProjectTools.isEmpty(para.get("testval"))){
//			suitval = para.get("testval");
//		}
		
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
		Date date = ProjectTools.addMonth(new Date(), 12);
		String dateStart = sdf.format(new Date()) + "000000";
		String dateEnd = sdf.format(date) + "000000";
		long nowtm = Long.parseLong(DateTimeUtil.longtime());//当前时间
		
		Workbook workbook = Workbook.getWorkbook(excelis);
		Sheet sheet = workbook.getSheet(0);
		int rows = sheet.getRows();
		int p=0;
		for (int i = 1; i < rows; i++) 
		{
			//控制循环读取一行数据
			String devid = sheet.getCell(0, i).getContents() == null ? "" : sheet.getCell(0, i).getContents().trim();
			String iccid = sheet.getCell(1, i).getContents() == null ? "" : sheet.getCell(1, i).getContents().trim();
			if(!Tools.isEmpty(devid))
			{				
				p++;
				System.out.println(devid+","+iccid);
				long did=Long.parseLong(devid);
				int k=(int)(did % 32);
				String sql="select * from device"+k+" where devid='"+devid+"'";
				System.out.println(sql);
				//List<Map<String, Object>> list=DAOHelper.find(ProjectConstants.connMiFi, sql);
				List<Map<String, Object>> list=RCPService.getMifiService().getMapList(sql);
				if(list == null || list.size() == 0)
				{
					String insql="INSERT INTO device"+k+" (devid,iccid,orgid,devgrpid,devstat,crttm,mode) VALUES ('"+devid+"','"+iccid+"',"+orgid+","+grpid+",1,"+DateTimeUtil.longtime()+","+mode+")";
					System.out.println(insql);
					//DAOHelper.execute(ProjectConstants.connMiFi, insql);
					RCPService.getMifiService().execSql(insql);
					
					String indevgrpsql = "INSERT INTO devinit(devid,grpid,grpname) VALUES('"+devid+"',0,'"+grpname+"')";
					RCPService.getBaseService().execSql(indevgrpsql);
					
					// 是否充值默认套餐
//					if(ProjectTools.isEmpty(dftsuit))
//					{
//						String usuitsql = "INSERT INTO usersuit(devid,orgid,remain,areaid,packid,effect,invalid,crttm,pkvalue) VALUES ('"+devid+"',"+orgid+",'"+suitval+"',"+ProjectConstants.chinaArea+","+ProjectConstants.testPkgId+","+dateStart+","+dateEnd+","+nowtm+","+suitval+")";
//						System.out.println(usuitsql);
//						DAOHelper.execute(ProjectConstants.connDev, usuitsql);
//					}
					if(copu.equals("1"))//2天不限
					{
						String keysql="SELECT * FROM `packkey` WHERE packid=235 AND statu=0 and devid is null LIMIT 1";
						System.out.println(keysql);
						Map<String, Object> keymap=RCPService.getBaseService().getMap(keysql);
						String keyid=keymap.get("id").toString();
						String upkeysql="update packkey set devid='"+devid+"' where id="+keyid;
						System.out.println(upkeysql);
						RCPService.getBaseService().execSql(upkeysql);
					}
					if(copu.equals("2"))//3天100G
					{
						String keysql="SELECT * FROM `packkey` WHERE packid=163 AND statu=0 and devid is null LIMIT 1";
						System.out.println(keysql);
						Map<String, Object> keymap=RCPService.getBaseService().getMap(keysql);
						String keyid=keymap.get("id").toString();
						String upkeysql="update packkey set devid='"+devid+"' where id="+keyid;
						System.out.println(upkeysql);
						RCPService.getBaseService().execSql(upkeysql);
					}
					if(copu.equals("3"))//7天3G
					{
						String keysql="SELECT * FROM `packkey` WHERE packid=6 AND statu=0 and devid is null LIMIT 1";
						System.out.println(keysql);
						Map<String, Object> keymap=RCPService.getBaseService().getMap(keysql);
						String keyid=keymap.get("id").toString();
						String upkeysql="update packkey set devid='"+devid+"' where id="+keyid;
						System.out.println(upkeysql);
						RCPService.getBaseService().execSql(upkeysql);
					}
				}
			}
		}
		
		Oplog oplog = new Oplog(account.getId(), account.getOrgid(), 3, "导入"+p+"条设备数据", nowtm, "", 0, ProjectConstants.testPkgId, "入库");
		baseDao.saveObj(oplog);
		resultMap.put("code", 0);
		//拼装返回值
		resultMap.put("countnum", rows);
		resultMap.put("sucsnum", p);
		
		return resultMap;
	}
	
	@Override
	public List<Map<String, Object>> getDevGrp(int orgid) {
		String sql = "SELECT id,grpname AS text,mifi FROM devicegroup WHERE 1=1 ";
		sql=sql+" and orgid in("+getOrgids(orgid)+")";
		return baseDao.getMapList(sql);
	}

	@Override
	public int movePkgs(Account account,String devid,String movDevid) 
	{
		String devSql = "SELECT devid FROM "+ProjectTools.getTableNameBySN(movDevid)+" WHERE devid = '"+movDevid+"'";
		//List<Map<String,Object>> devLst = DAOHelper.find("ufidevice",devSql);
		List<Map<String,Object>> devLst=RCPService.getMifiService().getMapList(devSql);
		if(devLst == null || devLst.size() == 0)
			return 1;

		List<String> sqlList = new ArrayList<String>();
		String movSql = "UPDATE usersuit SET devid = '"+movDevid+"' WHERE devid = '"+devid+"'";
		String logSql = "INSERT INTO oplog (accid,orgid,optype,content,appuid,crttm,devid) VALUES("
				+ account.getId()+","+account.getOrgid()+",3,'设备套餐转移>>>设备ID："+devid+"，转移到的设备ID："+movDevid+"',0,"+DateTimeUtil.longtime()+",'"+devid+"')";
		sqlList.add(movSql);
		sqlList.add(logSql);
		baseDao.excuBatch(sqlList);
		return 0;
	}
	@Override
	public int moveSPkgs(Account account,String devid,String movDevid) 
	{
		String devSql = "SELECT devid FROM "+ProjectTools.getTableNameBySN(movDevid)+" WHERE devid = '"+movDevid+"'";
		//List<Map<String,Object>> devLst = DAOHelper.find("ufidevice",devSql);
		List<Map<String,Object>> devLst=RCPService.getMifiService().getMapList(devSql);
		if(devLst == null || devLst.size() == 0)
			return 1;
		
		List<String> sqlList = new ArrayList<String>();
		String movSql = "UPDATE userspeedpack SET devid = '"+movDevid+"' WHERE devid = '"+devid+"'";
		String logSql = "INSERT INTO oplog (accid,orgid,optype,content,appuid,crttm,devid) VALUES("
				+ account.getId()+","+account.getOrgid()+",3,'设备加速包套餐转移>>>设备ID："+devid+"，转移到的设备ID："+movDevid+"',0,"+DateTimeUtil.longtime()+",'"+devid+"')";
		sqlList.add(movSql);
		sqlList.add(logSql);
		baseDao.excuBatch(sqlList);
		return 0;
	}

	@Override
	public int unbind(Account account, String devid) 
	{
		String s="select appuid from "+ProjectTools.getTableNameBySN(devid)+" WHERE devid = '"+devid+"'";
		Map<String, Object> m=RCPService.getMifiService().getMap(s);
		if(m!=null && m.size()>0)
		{
			String appuid=m.get("appuid").toString();
			String sql = "UPDATE appuser SET defaultdev = '' WHERE id = "+appuid;
			RCPService.getBaseService().execSql(sql);
		}
		String sql = "UPDATE "+ProjectTools.getTableNameBySN(devid)+" SET appuid = 0 WHERE devid = '"+devid+"'";
		//DAOHelper.execute("ufidevice", sql);
		RCPService.getMifiService().execSql(sql);
		sql = "UPDATE appuser SET defaultdev = 0 WHERE devid = '"+devid+"'";
		sql = "INSERT INTO oplog (accid,orgid,optype,content,appuid,crttm,devid) VALUES("+ account.getId()+","+account.getOrgid()+",3,'设备解除绑定>>>设备ID："+devid+"',0,"+DateTimeUtil.longtime()+",'"+devid+"')";
		logger.info("设备解除绑定>>>设备ID："+devid);
		baseDao.excuSql(sql);
		return 0;
	}
	
	@Override
	public int userBind(int orgid, int accid, Map<String, String> para) 
	{
		List<String> sqlList = new ArrayList<String>();
		String devidstr = para.get("devidstr");
		String userphone = para.get("userphone");
		
		String sql = "SELECT devid,appuid FROM "+ProjectTools.getTableNameBySN(devidstr)+" WHERE devid = '"+devidstr+"' AND appuid != 0";
		//List<Map<String,Object>> devLst = DAOHelper.find("ufidevice", sql);
		List<Map<String,Object>> devLst = RCPService.getMifiService().getMapList(sql);
		if(devLst != null && devLst.size()>0)			//设备已绑定用户
			return 1;
		
		sql = "SELECT id,uname FROM appuser WHERE phonenum = '"+userphone+"'";
		List<Map<String,Object>> userLst = baseDao.getMapList(sql);
		if(userLst == null || userLst.size() == 0)		//用户不存在
			return 2;
		String appuid = String.valueOf(userLst.get(0).get("id"));
		
		sql = "INSERT INTO oplog (accid,orgid,optype,content,appuid,crttm,devid) VALUES("+ accid+","+orgid+",3,'绑定设备，设备ID:"+devidstr+",用户号码："+userphone+"',"+appuid+","+DateTimeUtil.longtime()+",'"+devidstr+"')";
		sqlList.add(sql);
		baseDao.excuBatch(sqlList);
		
		sql = "UPDATE "+ProjectTools.getTableNameBySN(devidstr)+" SET appuid = "+appuid+" WHERE devid = '"+devidstr+"'";
		//DAOHelper.execute("ufidevice", sql);
		RCPService.getMifiService().execSql(sql);
		return 0;
	}

	@Override
	public int delete(int orgid, int accid, Map<String, String> para) 
	{
		String devids = para.get("devids");
		String[] devid = devids.split(",");
		for(int i=0;i<devid.length;i++)
		{
			String sql = "DELETE FROM "+ProjectTools.getTableNameBySN(devid[i])+" WHERE devid ='"+devid[i]+"'";
			//DAOHelper.execute("ufidevice", sql);
			RCPService.getMifiService().execSql(sql);
		}
		return 0;
	}
	
	@Override
	public int resetpwd(int orgid, int accid, Map<String, String> para) {
		List<String> sqlList = new ArrayList<String>();
		String devids = para.get("devids");
		String[] devid = devids.split(",");
		for(int i=0;i<devid.length;i++)
		{
			String sql = "INSERT INTO oplog (accid,orgid,optype,content,appuid,crttm,devid) VALUES("+ accid+","+orgid+",3,'密码重置，设备ID:"+devids+"',0,"+DateTimeUtil.longtime()+",'"+devid[i]+"')";
			sqlList.add(sql);
			String sqla = "INSERT INTO devcrond(devid,operate,assist,state,crttm) VALUES('"+devid[i]+"','3','12345678',0,"+DateTimeUtil.longtime()+")";
			sqlList.add(sqla);
		}
		baseDao.excuBatch(sqlList);
		return 0;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Map<String, Object> batchPkg(InputStream excelis, Account account, Map<String, String> para) throws Exception {
		
		String pkgid = para.get("pkgid");
		String timeStart = para.get("timeStart");
		int sucsnum = 0;//成功写入到数据库的条数
		List<String> titleList = new ArrayList<String>();//文件中列名称集合
		StringBuffer devbuf = new StringBuffer();
		/*返回的结果map*/
		Map<String,Object> resultMap = new HashMap<String, Object>();
		
		//导入数据的列
		Map<String,String> devcolumn = new HashMap<String,String>();
		devcolumn.put("设备ID", "devid");
		
		Workbook workbook = Workbook.getWorkbook(excelis);
		Sheet sheet = workbook.getSheet(0);
		int rows = sheet.getRows();
		int cols = sheet.getColumns();
		int countnum = rows - 1;//总数据条数
		
		for (int i = 0; i < rows; i++) {//控制循环读取一行数据
			if (0 == i) {//如果是第一行 第一行为标题行 非数据行
				int suitColumNum = 0;  //可以导入的excel列数
				for (int z = 0; z < cols; z++) {//控制读取行的列数据
					String titleName=sheet.getCell(z, i).getContents() == null ? "" : sheet.getCell(z, i).getContents();//获得标题
					if (null != titleName) {
						titleList.add(titleName);//将标题放入标题List
						if(devcolumn.containsKey(titleName)){
							suitColumNum ++;
						}
					}
				}
				if(suitColumNum != 1){
					resultMap.put("code", 1);
					resultMap.put("msg", "导入文件与模版的列不匹配！");
					return resultMap;
				}
			}else{
				for (int j = 0; j < cols; j++) {//控制读取行的列数据
					String columnname = "";
					if (null != titleList.get(j)) {//如果本次导入的列的标题不为空
						columnname = devcolumn.get(titleList.get(j));
					}
					if(null != columnname){
						//获得当前列值
						String cellValue = sheet.getCell(j, i).getContents() == null ? "" : sheet.getCell(j, i).getContents().trim();
						if ("".equals(cellValue)){
							logger.info("DEVICE IMPORT:"+"rows>>>"+i+", column>>>"+j+", is null!");
							break;
						}else if(cellValue.indexOf("'")>0){
							logger.info("DEVICE IMPORT:"+"rows>>>"+i+", column>>>"+j+", exit ' ,can not import!");
							break;
						}else{
							devbuf.append(",'"+cellValue+"'");
							sucsnum ++;
						}
					}
				}
			}
		}
		Map<String, Object> record = new HashMap<String, Object>();
		pkgRech(record, account.getOrgid(), pkgid, timeStart, devbuf.substring(1), sucsnum, account.getId());
		//接口查询套餐信息
		logger.info("WRITE SUITS: res>>>>>>"+record);
		int code = Integer.parseInt(String.valueOf(record.get("code")));
		if(0 == code){
			String sql = "INSERT INTO oplog (accid,orgid,optype,content,appuid,crttm,devid) VALUES("
					+ account.getId()+","+account.getOrgid()+",1,'批量设备充值成功，"+sucsnum+"条设备数据',0,"+DateTimeUtil.longtime()+",'')";
			baseDao.excuSql(sql);
			resultMap.put("code", 0);
			resultMap.put("countnum", countnum);
			resultMap.put("sucsnum", sucsnum);
			
		}else if(5 == code){
			Map<String,Object> dataMap = (Map<String, Object>) record.get("data");
			String sql = "INSERT INTO oplog (accid,orgid,optype,content,appuid,crttm,devid) VALUES("
					+ account.getId()+","+account.getOrgid()+",1,'批量充值失败，编号有误的设备:"+dataMap.get("ecldev")+"',0,"+DateTimeUtil.longtime()+",'')";
			baseDao.excuSql(sql);
			resultMap.put("code", code);
			resultMap.put("countnum", countnum);
			resultMap.put("sucsnum", 0);
			
		}else{
			String sql = "INSERT INTO oplog (accid,orgid,optype,content,appuid,crttm,devid) VALUES("
					+ account.getId()+","+account.getOrgid()+",1,'"+String.valueOf(record.get("msg"))+"',0,"+DateTimeUtil.longtime()+",'')";
			baseDao.excuSql(sql);
			resultMap.put("code", code);
			resultMap.put("countnum", countnum);
			resultMap.put("sucsnum", 0);
		}
		return resultMap;
	}
	
	/**
	 * 设备充值
	 * @param record
	 * @param orgid
	 * @param pkgid		套餐id
	 * @param devids	充值设备
	 * @param sucsnum	设备条数
	 * @return
	 */
	private Map<String,Object> pkgRech(Map<String,Object> record,int orgid,String pkgid,String timeStart,String devids,int sucsnum,int accid){
		record.put("msg", "fail");
		record.put("code", 0);
		Map<String,Object> resmap = new HashMap<String,Object>();
		
		List<Map<String,Object>> devLst=new ArrayList<Map<String,Object>>();
		for(int i=0;i<32;i++)
		{
			String sql = "SELECT devid,orgid FROM device"+i+" WHERE devid IN("+devids+") ";
			if(orgid != 1)
				sql += " AND orgid IN ("+getOrgids(orgid)+")";
			List<Map<String,Object>> temp=RCPService.getMifiService().getMapList(sql);
			if(temp!=null && temp.size()>0)
				devLst.addAll(temp);
		}
		
		//List<Map<String,Object>> devLst = baseDao.getMapList(sql);
		//判断设备存在总数是否与要导入的数量相等，如果不相等，返回失败
//		if(devLst == null || devLst.size() < sucsnum){
//			String ecldev = devids;
//			for(Map<String,Object> devMap : devLst){
//				String dbdevid = String.valueOf(devMap.get("devid"));
//				if(devids.indexOf(dbdevid) > 0){
//					int bgnum = ecldev.indexOf(dbdevid);
//					//去掉不存在的设备编号
//					ecldev = ecldev.substring(0,bgnum-1) + ecldev.substring(bgnum+dbdevid.length()+2,ecldev.length());
//				}
//			}
//			record.put("code", 5);		//导入文件存在错误设备，返回错误
//			record.put("msg", "存在错误设备");
//			resmap.put("ecldev", ecldev.replace("'", ""));
//			//成功执行
//		}else
		{
			List<String> sqlList = new ArrayList<String>();
			//获取套餐成本价格
			String pkSql = "SELECT pkv.pkid,pkv.price,pkv.costprice,pkg.pkgValue,pkg.pkgLimit,pkv.pkname,pkg.areaid,"
					+ "	pkg.invalidtime,pkg.mode FROM packageview pkv LEFT JOIN package pkg ON pkg.id = pkv.pkid"
					+ "	WHERE pkv.pkid = "+pkgid+" AND pkv.orgid = "+orgid;
			List<Map<String,Object>> pkList = baseDao.getMapList(pkSql);
			if(pkList == null || pkList.size() != 1){
				record.put("msg", "套餐获取失败！");
				record.put("code", 6);
				return record;
			}
			//获取套餐信息
			String nowTime = DateTimeUtil.longtime();
			int costprice = Integer.parseInt(String.valueOf(pkList.get(0).get("costprice")));
			long costTotal = costprice*sucsnum;	//套餐总价格
			if(orgid > 2){
				//查询代理商余额
				String orgSql = "SELECT balance,flowbal FROM organize WHERE id = "+orgid;
				List<Map<String,Object>> orgList = baseDao.getMapList(orgSql);
				
				if(orgList == null || orgList.size() != 1){
					record.put("msg", "代理信息获取失败！");
					record.put("code", 7);
					return record;
				}
				
				int balance = Integer.parseInt(String.valueOf(orgList.get(0).get("balance")));
				
				//获取套餐代理成本价
				if(balance < costTotal){
					logger.info("代理商余额不足：{代理ID："+orgid+",套餐总价："+costTotal+"}");
					record.put("code", 8);
					record.put("msg", "套餐写入失败，代理余额不足！");
					return record;
				}
				long costNum = balance - costTotal;
				
				String costSql = "UPDATE organize SET balance = "+costNum+" WHERE id = '"+orgid+"'";
				String deSql = "INSERT INTO deductrcd (orgid,orderid,amount,befval,aftval,crttm,devid,paytype,devorgid) VALUES("
						+ "'"+orgid+"','',"+costTotal+","+balance+","+costNum+",'"+nowTime+"','',6,0)";
				sqlList.add(costSql);
				sqlList.add(deSql);
			}
			
			Map<String,Object> pkgMap = pkList.get(0);
			String pkgName = String.valueOf(pkgMap.get("pkname"));								//套餐名
			int pkgLimit = Integer.parseInt(String.valueOf(pkgMap.get("pkgLimit")));
			String areaId = String.valueOf(pkgMap.get("areaid"));
			int expiryDay = 0;			//有效期
			int mode = Integer.parseInt(String.valueOf(pkgMap.get("mode")));					//套餐模式
			int invalidtime = Integer.parseInt(String.valueOf(pkgMap.get("invalidtime")));		//有效时长
			int pkgValue = Integer.parseInt(String.valueOf(pkgMap.get("pkgValue")));			//流量大小
			
			for(int d=0;d<devLst.size();d++){
				String orderId = "SYS"+Long.toString(System.currentTimeMillis()/1000)+ProjectTools.buildRandomStr(3);
				
				String devid = String.valueOf(devLst.get(d).get("devid"));
				String devOrgId = String.valueOf(devLst.get(d).get("orgid"));
				
				Date time = new Date();
				if(!ProjectTools.isEmpty(timeStart)){
					SimpleDateFormat formatter = new SimpleDateFormat( "MM/dd/yyyy");
					try {
						time =  formatter.parse(timeStart);
					} catch (ParseException e) {
						e.printStackTrace();
						logger.info(Tools.getExceptionMessage(e));
					}
				}
				SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
				String timesStart = sdf.format(time.getTime()) + "000000";
				String timeEnd = "";
				
				//添加套餐
				String suSql = "INSERT INTO usersuit(orgid,devid,remain,areaid,packid,limitval,effect,invalid,crttm,orderid) VALUES";
				
				if(mode == 0){
					String dateStart = sdf.format(time.getTime()) + "000000";
					if(invalidtime == 0)	//一天
						time = DateTimeUtil.addDay(time, 1);
					if(invalidtime == 1)	//一月
						time = ProjectTools.addMonth(time, 1);
					if(invalidtime == 2)	//一季
						time = ProjectTools.addMonth(time, 3);
					if(invalidtime == 3)	//半年
						time = ProjectTools.addMonth(time, 6);
					if(invalidtime == 4)	//一年
						time = ProjectTools.addMonth(time, 12);
					if(invalidtime == 5)	//两年
						time = ProjectTools.addMonth(time, 24);
					if(invalidtime == 6)	//一周
						time = DateTimeUtil.addDay(time, 7);
					if(invalidtime == 7)	//三年
						time = ProjectTools.addMonth(time, 36);
					if(invalidtime == 8)	//三天
						time = DateTimeUtil.addDay(time, 3);
					
					String dateEnd = sdf.format(time.getTime()) + "000000";
					timeEnd = sdf.format(time.getTime()) + "000000";
					suSql += "("+devOrgId+",'"+devid+"',"+pkgValue+","+areaId+","+pkgid+","+pkgLimit+",'"+dateStart+"','"+dateEnd+"','"+nowTime+"','"+orderId+"')";
					
				}else if(mode == 1){
					if(invalidtime == 0)	//一天
						expiryDay = 1;
					if(invalidtime == 1)	//一月
						expiryDay = 30;
					if(invalidtime == 2)	//一季
						expiryDay = 90;
					if(invalidtime == 3)	//半年
						expiryDay = 180;
					if(invalidtime == 4)	//一年
						expiryDay = 365;
					if(invalidtime == 5)	//两年
						expiryDay = 730;
					if(invalidtime == 6)	//一周
						expiryDay = 7;
					if(invalidtime == 7)	//三年
						expiryDay = 1095;
					if(invalidtime == 8)	//三天
						expiryDay = 3;
					
					for(int i=0;i<expiryDay;i++){
						String dateStart = sdf.format(time.getTime()) + "000000";
						time = ProjectTools.addMonth(time, 1);
						String dateEnd = sdf.format(time.getTime()) + "000000";
						timeEnd = sdf.format(time.getTime()) + "000000";
						suSql += "("+devOrgId+",'"+devid+"',"+pkgValue+","+areaId+","+pkgid+","+pkgLimit+",'"+dateStart+"','"+dateEnd+"','"+nowTime+"','"+orderId+"'),";
					}
					suSql = suSql.substring(0,suSql.length()-1);
					
				}else if(mode == 2){
					if(invalidtime == 1)	//一月
						expiryDay = 1;
					if(invalidtime == 2)	//一季
						expiryDay = 3;
					if(invalidtime == 3)	//半年
						expiryDay = 6;
					if(invalidtime == 4)	//一年
						expiryDay = 12;
					if(invalidtime == 5)	//两年
						expiryDay = 24;
					if(invalidtime == 7)	//三年
						expiryDay = 36;
					
					for(int i=0;i<expiryDay;i++){
						String dateStart = sdf.format(time.getTime()) + "000000";
						time = ProjectTools.addMonth(time, 1);
						String dateEnd = sdf.format(time.getTime()) + "000000";
						timeEnd = sdf.format(time.getTime()) + "000000";
						suSql += "("+devOrgId+",'"+devid+"',"+pkgValue+","+areaId+","+pkgid+","+pkgLimit+",'"+dateStart+"','"+dateEnd+"','"+nowTime+"','"+orderId+"'),";
					}
					suSql = suSql.substring(0,suSql.length()-1);
				}
				sqlList.add(suSql);
				
				String pkgSql = "INSERT INTO packrcd(devid,pkid,pkname,orderid,crttm,datastart,dataend,orgid) VALUES"
						+ "('"+devid+"','"+pkgid+"','"+pkgName+"','"+orderId+"',"+nowTime+","+timesStart+","+timeEnd+","+orgid+")";
				
				//更改设备状态为正常
				String staSql = "UPDATE "+ProjectTools.getTableNameBySN(devid)+" SET devstat = 1 WHERE devid = '"+devid+"'";
				
				Map<String,String> simtypemap=getSimType(devid);
				if(simtypemap.get("simtype")!=null)
				{
					String simtype=simtypemap.get("simtype").toString();
					String iccid=simtypemap.get("iccid").toString();
					String kasid=simtypemap.get("kasid").toString();
					String accessNumber=simtypemap.get("access_number").toString();
					if(simtype.equals("1"))
					{
						logger.info("simtype devid="+devid);
						String simtasksql="insert into simtask(iccid,optask,crttm,kasid,devid,accessNumber) values ('"+iccid+"',2,"+DateTimeUtil.longtime()+","+kasid+",'"+devid+"','"+accessNumber+"')";
						logger.info(simtasksql);
						//DAOHelper.execute("mifi", simtasksql);
						RCPService.getBaseService().execSql(simtasksql);
					}
				}
				
//				String ordSql = "";
				
				sqlList.add(pkgSql);
				sqlList.add(staSql);
				sqlList.add(orderId);
				
			}
			baseDao.excuBatch(sqlList);
			logger.info(sqlList.toString());
			record.put("msg", "success");
		}
		record.put("data", resmap);
		return record;
	}
	
	public static Map<String,String> getSimType(String devid)
	{
		Map<String,String> mp=new HashMap<String,String>();
		mp.put("devid", devid);
		String sql="select mode from "+ProjectTools.getTableNameBySN(devid)+" where devid = '"+devid+"'";
		List<Map<String, Object>> dl=RCPService.getMifiService().getMapList(sql);
		if(dl!=null && dl.size()>0)
		{
			String mode=dl.get(0).get("mode").toString();
			mp.put("mode", mode);
			String icsql="SELECT iccid FROM smd WHERE sn='"+devid+"' AND rmode="+mode;
			List<Map<String, Object>> sl=RCPService.getBaseService().getMapList(icsql);
			if(sl!=null && sl.size()>0)
			{
				String iccid=sl.get(0).get("iccid").toString();
				mp.put("iccid", iccid);
				String ksql="SELECT kasid,access_number FROM iccidtemp WHERE iccid='"+iccid+"'";
				List<Map<String, Object>> kl=DAOHelper.find("ufiiccid", ksql);
				if(kl!=null && kl.size()>0)
				{
					String kasid=String.valueOf(kl.get(0).get("kasid"));
					String access_number=String.valueOf(kl.get(0).get("access_number"));
					mp.put("kasid", kasid);
					mp.put("access_number", access_number);
					String ks="SELECT simtype FROM kas WHERE id="+kasid;
					List<Map<String, Object>> kkl=RCPService.getBaseService().getMapList(ks);
					if(kkl!=null && kkl.size()>0)
					{
						String simtype=kkl.get(0).get("simtype").toString();
						mp.put("simtype", simtype);
					}
				}
			}
		}
		return mp;
	}
	
	@Override
	public int orgTrasf(Account account, Map<String, String> para)
	{
		String orgdids = para.get("orgdids");
		String dgorg = para.get("dgorg");
		if(ProjectTools.isEmpty(orgdids) || ProjectTools.isEmpty(dgorg)){
			return 1;
		}

		String[] cc=orgdids.substring(1).split(",");
		for(String c:cc)
		{
			String devSql = "UPDATE "+ProjectTools.getTableNameBySN(c)+" SET orgid="+dgorg+" WHERE devid ='"+c+"'";
			//DAOHelper.execute("ufidevice", devSql);
			RCPService.getMifiService().execSql(devSql);
		}
		
		String logSql = "INSERT INTO oplog (accid,orgid,optype,content,appuid,crttm,devid) VALUES("
				+ account.getId()+","+account.getOrgid()+",3,'批量划转设备代理>>>划转代理："+dgorg+"',0,"+DateTimeUtil.longtime()+",'')";
		logger.info("批量划转设备代理>>>{设备ID："+orgdids.replace("'", "")+"，划转代理："+dgorg+"}");
		baseDao.excuSql(logSql);
		return 0;
	}
	
	@Override
	public Map<String, Object> batchOrg(InputStream excelis, Account account, Map<String, String> para) throws Exception 
	{

		Map<String,Object> resultMap = new HashMap<String, Object>();
		long nowtm = Long.parseLong(DateTimeUtil.longtime());//当前时间
		String orgid = para.get("orgid");

		
		Workbook workbook = Workbook.getWorkbook(excelis);
		Sheet sheet = workbook.getSheet(0);
		int rows = sheet.getRows();
		int p=0;
		for (int i = 1; i < rows; i++) 
		{
			//控制循环读取一行数据
			String devid = sheet.getCell(0, i).getContents() == null ? "" : sheet.getCell(0, i).getContents().trim();
			if(!Tools.isEmpty(devid))
			{
				p++;
				String devSql = "UPDATE "+ProjectTools.getTableNameBySN(devid)+" SET orgid="+orgid+" WHERE devid='"+devid+"'";
				//DAOHelper.execute("ufidevice", devSql);
				RCPService.getMifiService().execSql(devSql);
			}
		}
		
		String logSql = "INSERT INTO oplog (accid,orgid,optype,content,appuid,crttm,devid) VALUES("	+ account.getId()+","+account.getOrgid()+",3,'批量划转设备代理成功"+p+"条>>>划转代理："+orgid+"',0,"+nowtm+",'')";
		logger.info("批量划转设备代理成功"+p+"条>>>，划转代理："+orgid+"}");
		baseDao.excuSql(logSql);
		resultMap.put("code", 0);
		resultMap.put("countnum", rows);
		resultMap.put("sucsnum", p);
		
		return resultMap;
	}

	@Override
	public int grpTrasf(Account account, Map<String, String> para) {
		String grpdids = para.get("grpdids");
		String dggrp = para.get("dggrp");
		if(ProjectTools.isEmpty(grpdids) || ProjectTools.isEmpty(dggrp)){
			return 1;
		}else{
			List<String> sqlList = new ArrayList<String>();
			String devids = grpdids.replace("'", "");
			devids = devids.substring(1);
			String[] devid = devids.split(",");
			for(int i=0;i<devid.length;i++)
			{
				String devsql = "UPDATE "+ProjectTools.getTableNameBySN(devid[i])+" SET devgrpid = "+dggrp+" WHERE devid ='"+devid[i]+"'";
				//DAOHelper.execute("ufidevice", devsql);
				RCPService.getMifiService().execSql(devsql);
			}
			String logSql = "INSERT INTO oplog (accid,orgid,optype,content,appuid,crttm,devid) VALUES("
					+ account.getId()+","+account.getOrgid()+",3,'批量划转设备组>>>设备编号:"+devids+"，设备组："+dggrp+"',0,"+DateTimeUtil.longtime()+",'')";
			
			sqlList.add(logSql);
			baseDao.excuBatch(sqlList);
			logger.info("批量划转设备组>>>{设备编号:"+devids+"，划转设备组："+dggrp+"}");
			return 0;
		}
	}
	
	/**
	 * 根据时间加减日
	 * @param date	时间
	 * @param month	月份
	 * @return
	 */
	public static Date addDay (Date date,int day){
		Calendar calendar = new GregorianCalendar();
		calendar.setTime(date);
		calendar.add(Calendar.DAY_OF_MONTH, day);
		return calendar.getTime();
	}

	@Override
	public List<Object[]> exportData(int orgid, Map<String, String> para) 
	{
		String grpid = para.get("grpid");
		List<Object[]> result = new ArrayList<Object[]>();
		
//		if(!ProjectTools.isEmpty(grpid) && !"-1".equals(grpid)){
			Object[] colTitle = new Object[11];
			colTitle[0] = "SN";
			colTitle[1] = "iccid";
			colTitle[2] = "最后上网时间";
			colTitle[3] = "设备模式";
			colTitle[4] = "绑定手机号";
			colTitle[5] = "代理商";
			colTitle[6] = "设备组";
			colTitle[7] = "设备类型";
			colTitle[8] = "工厂";
			colTitle[9] = "设备状态";
			colTitle[10] = "在线状态";
			result.add(colTitle);
			
			String grpsql = "SELECT grp.id,grp.grpname,grp.devtype,grp.gongc,devtype.typename FROM devicegroup grp,devtype WHERE devtype.id=grp.devtype";
            if(!ProjectTools.isEmpty(grpid) && !"-1".equals(grpid)) {
                grpsql+=  " and grp.id ="+grpid;
            }
			List<Map<String,Object>> grplist =RCPService.getBaseService().getMapList(grpsql);
            Map<String,Object> grpmap = new HashMap<String,Object>();
            Map<String,Object> typemap = new HashMap<String,Object>();
            Map<String,Object> gongcmap = new HashMap<String,Object>();
            grplist.forEach(map->{
                String id = map.get("id").toString();
                String grpname = map.get("grpname").toString();
                String gongc = map.get("gongc").toString();
                String typename = map.get("typename").toString();
                grpmap.put(id, grpname);
                typemap.put(id, typename);
                gongcmap.put(id, gongc);
            });

            String phonesql = "select id,phonenum from appuser";
            List<Map<String,Object>> phonelist =RCPService.getBaseService().getMapList(phonesql);
            Map<String,Object> phonemap = new HashMap<String,Object>();
            phonelist.forEach(map->{
                String phonenum = map.get("phonenum").toString();
                String uid=map.get("id").toString();
                phonemap.put(uid, phonenum);
            });
            String orgsql = "select id,orgname from organize";
            List<Map<String,Object>> orglist =RCPService.getBaseService().getMapList(orgsql);
            Map<String,Object> orgmap = new HashMap<String,Object>();
            orglist.forEach(map->{
                String orgname = map.get("orgname").toString();
                String uid=map.get("id").toString();
                orgmap.put(uid, orgname);
            });

            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

			for(int k=0;k<32;k++)
			{
//				String sql = "SELECT devid,appuid,iccid,reptm,devstat,mode FROM device"+k+" WHERE reptm>0 and orgid IN ("+getOrgids(orgid)+")";
				String sql = "SELECT devid,appuid,iccid,reptm,devstat,mode,devgrpid,lastcntm,limtval FROM device"+k+" WHERE 1=1";
                if(!ProjectTools.isEmpty(grpid) && !"-1".equals(grpid)) {
                    sql += " and devgrpid="+grpid;
                }
				
				List<Map<String,Object>> devLst = RCPService.getMifiService().getMapList(sql);
				if(devLst != null && devLst.size()>0)
				{
					for(int i=0;i<devLst.size();i++){
						Map<String,Object> map = devLst.get(i);
						String oid=String.valueOf(map.get("orgid"));
						String appuid=String.valueOf(map.get("appuid"));
						String devgrpid=String.valueOf(map.get("devgrpid"));
						Object[] col = new Object[11];
						String sn=String.valueOf(map.get("devid"));
						String devstat=String.valueOf(map.get("devstat"));
						col[0] = sn;
						//col[1] = String.valueOf(map.get("appuid"));
						String iccid="";
						if(map.get("iccid")!=null)
							iccid=String.valueOf(map.get("iccid"));
						col[1] = iccid;
						//logger.info(sn+","+iccid);
                        long lastcntm = Long.parseLong(String.valueOf(map.get("lastcntm")));
                        if (lastcntm==0){
                            col[2]="";
                        } else {
                            col[2] = sdf.format(new Date(lastcntm));
                        }
						/*String reptm="";
						try {
                            if(map.get("reptm")!=null) {
                                reptm=String.valueOf(map.get("reptm"));
                                if (reptm.length()==13){
                                    reptm = sdf.format(sdfOneM2.parse(reptm));
                                } else if (reptm.length()==14){
                                    reptm = sdf.format(sdf2.parse(reptm));
                                }
                            }
                        }catch (Exception e){
                            e.printStackTrace();
                            reptm=String.valueOf(map.get("reptm"));
                        }
						col[2] = reptm;*/
						
						String mstr = "";
						if(map.get("mode")!=null)
						{
							int mode = Integer.parseInt(String.valueOf(map.get("mode")));
							if(mode == 1)
								mstr = "云卡";
							else if(mode == 2)
								mstr = "内置卡1";
							else if(mode == 3)
								mstr = "外插卡";
							else if(mode == 4)
								mstr = "内置卡2";	
							else if(mode == 5)
								mstr = "内置卡3";	
							else if(mode == 6)
								mstr = "eSim移动";	
							else if(mode == 7)
								mstr = "eSim联通";		
							else if(mode == 8)
								mstr = "eSim电信";																										
							else if(mode == 9)
								mstr = "终端设置";
						}
						col[3]=mstr;
						col[4]="";
						col[5]="";
						if(!ProjectTools.isEmpty(appuid))
						{
                            col[4] = phonemap.get(appuid);
						}
						if(!ProjectTools.isEmpty(oid))
						{

                            col[5] = orgmap.get(oid);
						}
                        col[6] = grpmap.get(devgrpid);
                        col[7] = typemap.get(devgrpid);
                        col[8] = gongcmap.get(devgrpid);
                        if("0".equals(devstat))
                            col[9]="禁用";
                        else if("1".equals(devstat))
                            col[9]="正常";
                        else if("2".equals(devstat))
                            col[9]="流量不足";
                        else if("3".equals(devstat))
                            col[9]="未绑定";
                        else if("4".equals(devstat))
                            col[9]="未实名";
                        else if("9".equals(devstat))
                            col[9]="eSim失效";
                        int upTime = 1*60*1000*5;
                        long nowTime = System.currentTimeMillis();
                        String limtval = String.valueOf(map.get("limtval"));
                        long acTime = nowTime - upTime;
                        if(lastcntm > acTime){
                            col[10]="在线";
//                            if(!limtval.equals("0")){
//                                col[10]=limtval+"K";
//                            }
                        }else{
                            col[10]="离线";
                        }
                        result.add(col);
					}
				}
			}
//		} else {
//			Object[] colTitle = new Object[3];
//			colTitle[0] = "SN";
//			colTitle[1] = "iccid";
////			colTitle[2] = "设备状态";
//			colTitle[2] = "最后上网时间";
//			result.add(colTitle);
//
//			for(int k=0;k<32;k++)
//			{
//				String sql = "SELECT devid,appuid,iccid,reptm,devstat FROM device"+k+" WHERE reptm>0 and orgid IN ("+getOrgids(orgid)+")";
//				List<Map<String,Object>> devLst = RCPService.getMifiService().getMapList(sql);
//				if(devLst != null && devLst.size()>0)
//				{
//					for(int i=0;i<devLst.size();i++){
//						Map<String,Object> map = devLst.get(i);
//						Object[] col = new Object[3];
//						String sn=String.valueOf(map.get("devid"));
//						col[0] = sn;
//						//col[1] = String.valueOf(map.get("appuid"));
//						String iccid="";
//						if(map.get("iccid")!=null)
//							iccid=String.valueOf(map.get("iccid"));
//						col[1] = iccid;
//						//logger.info(sn+","+iccid);
//						String reptm="";
//						if(map.get("reptm")!=null)
//							reptm=String.valueOf(map.get("reptm"));
////						String reptm=String.valueOf(map.get("reptm"));
//////						if(reptm.length()>10)
//////							reptm=reptm.substring(0,4)+"-"+reptm.substring(4,6)+"-"+reptm.substring(6,8)+"-"+reptm.substring(8,10)+":"+reptm.substring(10,12)+":"+reptm.substring(12,14);
////						String devstat=String.valueOf(map.get("devstat"));
////						if(devstat.equals("0"))
////							devstat="禁用";
////						if(devstat.equals("1"))
////							devstat="正常";
////						if(devstat.equals("2"))
////							devstat="流量不足";
//						//col[2] = devstat;
//						col[2] = reptm;
//
////						String uid = String.valueOf(map.get("appuid"));
////						String usql="SELECT phonenum FROM appuser WHERE id="+uid;
////						Map<String, Object> umap=RCPService.getBaseService().getMap(usql);
////						String phone=umap.get("phonenum").toString();
////						col[1] = phone;
//
////						String osql="SELECT COUNT(*) AS c FROM allorder WHERE ostat>0 AND otype = 0 AND (packid not in(1,316,317)) AND devid='"+sn+"'";
////						logger.info(osql);
////						Map<String, Object> omap=RCPService.getBaseService().getMap(osql);
////						String c=omap.get("c").toString();
////						String p="0";
////						if(omap.get("p")!=null)
////							p=omap.get("p").toString();
//						//col[5] = c;
//						//col[6] = p;
//						result.add(col);
//					}
//				}
//			}
//		}
		return result;
	}

	@Override
	public int rptLog(Account account, Map<String, String> paras) {
		List<String> sqlList = new ArrayList<String>();
		String devid = paras.get("devid");
		String dbsql = "select count(1) from devcrond where devid = '"+devid+"'";
		String devsql = "";
		long num = baseDao.getLongField(dbsql).get(0);
		if(num>0){
			devsql = "update devcrond set operate = 4, assist = '"+ProjectConstants.logAddress
					+ "',state = 0, crttm="+DateTimeUtil.longtime()+" where devid = '"+devid+"'";
		}else{
			devsql = "insert into devcrond(devid,operate,assist,crttm) values ('"+devid+"',4,'"+ProjectConstants.logAddress
					+ "',"+DateTimeUtil.longtime()+")";
		}
		String logSql = "INSERT INTO oplog (accid,orgid,optype,content,appuid,crttm,devid) VALUES("
				+ account.getId()+","+account.getOrgid()+",3,'上传设备日志>>>设备编号:"+devid+"',0,"+DateTimeUtil.longtime()+",'')";
		sqlList.add(devsql);
		sqlList.add(logSql);
		baseDao.excuBatch(sqlList);
		return 0;
	}

	@Override
	public int reget(Account account, Map<String, String> paras) 
	{
		List<String> sqlList = new ArrayList<String>();
		String devid = paras.get("devid");
		String simshud = paras.get("simshud");
		
		String olsql = "SELECT lastcntm,fqpara FROM "+ProjectTools.getTableNameBySN(devid)+" where devid = '"+devid+"'";
		List<Map<String,Object>> reslst = RCPService.getMifiService().getMapList(olsql);
		
		String fqpara = String.valueOf(reslst.get(0).get("fqpara"));
		JSONObject fqJson = JSONObject.parseObject(fqpara);
		int upTime = Integer.parseInt(String.valueOf(fqJson.getIntValue("update")))*60*1000*2;
		long lastcntm = Long.parseLong(String.valueOf(reslst.get(0).get("lastcntm")));
		long acTime = System.currentTimeMillis() - upTime;
		if(lastcntm < acTime){
			return 1;
		}
		
		if(ProjectTools.isEmpty(simshud)){
			simshud = "0";
		}
		String sql = "UPDATE "+ProjectTools.getTableNameBySN(devid)+" SET simshud='"+simshud+"' where devid = '"+devid+"'";
		RCPService.getMifiService().execSql(sql);
		
		String dbsql = "select count(1) from devcrond where devid = '"+devid+"'";
		String devsql = "";
		long num = baseDao.getLongField(dbsql).get(0);
		if(num>0){
			devsql = "update devcrond set operate = 5, assist = '0', state = 0, crttm="+DateTimeUtil.longtime()
				+" where devid = '"+devid+"'";
		}else{
			devsql = "insert into devcrond(devid,operate,assist,crttm) values ('"+devid+"',5,'0',"+DateTimeUtil.longtime()+")";
		}
		String logSql = "INSERT INTO oplog (accid,orgid,optype,content,appuid,crttm,devid) VALUES("
				+ account.getId()+","+account.getOrgid()+",3,'设备重新取卡>>>设备编号:"+devid+"，选卡策略："+simshud+"',0,"
				+ DateTimeUtil.longtime()+",'')";
		sqlList.add(devsql);
		sqlList.add(logSql);
		baseDao.excuBatch(sqlList);
		return 0;
	}

	@Override
	public int updtDrv(Account account, Map<String, String> paras) {
		List<String> sqlList = new ArrayList<String>();
		List<String> devlst = new ArrayList<String>();
		String devids = paras.get("devids");
		String[] devary = devids.split(",");
		String ids = "";
		for(int i=0;i<devary.length;i++){
			ids += "'"+devary[i]+"',";
			devlst.add(devary[i]);
		}
		ids = ids.substring(0, ids.length()-1);
		String sql = "SELECT devid FROM devcrond WHERE devid IN ("+ids+")";
		List<Map<String,Object>> reslst = baseDao.getMapList(sql);
		if(reslst != null && reslst.size()>0){
			for(Map<String,Object> resmap:reslst){
				String devid = String.valueOf(resmap.get("devid"));
				sqlList.add("update devcrond set operate = 6, assist = '0', state = 0, crttm="+DateTimeUtil.longtime()
				+" where devid = '"+devid+"'");
				devlst.remove(devid);
			}
		}
		for(String dev:devlst){
			sqlList.add("insert into devcrond(devid,operate,assist,crttm) values ('"+dev+"',6,'0',"+DateTimeUtil.longtime()+")");
		}
		String logSql = "INSERT INTO oplog (accid,orgid,optype,content,appuid,crttm,devid) VALUES("
				+ account.getId()+","+account.getOrgid()+",3,'远程控制设备升级>>>设备编号:"+devids+"',0,"
				+ DateTimeUtil.longtime()+",'')";
		sqlList.add(logSql);
		baseDao.excuBatch(sqlList);
		return 0;
	}

	@Override
	public Object limit(Account account, Map<String, String> para) {
		String devids = para.get("devids");
		String limtval = para.get("limtval");
//		String[] devid = devids.split(",");
//		String ids = "";
//		for(int i=0;i<devid.length;i++){
//			ids += "'"+devid[i]+"',";
//		}
//		ids = ids.substring(0, ids.length()-1);
//		String sql = "SELECT devid,appuid FROM device WHERE devid IN ("+ids+")";
//		List<Map<String,Object>> devLst = baseDao.getMapList(sql);
//		if(devLst != null && devLst.size()>0) {
//		String updsql = "UPDATE `device` SET limtval="+limtval+",iflimt=5 WHERE devid IN ("+ids+")";
//		baseDao.excuSql(updsql);
//		return 0;
//		}
//		return 1;
		String updsql = "update usersuit set limitval="+limtval+" WHERE effect<"+DateTimeUtil.longtime()+" AND invalid>"+DateTimeUtil.longtime()+" AND packid IN(2,9,10,11,12,13) and devid IN ("+devids+")";
		logger.info(updsql);
		baseDao.excuSql(updsql);
		return 0;
	}

	@Override
	public List<ISP> getIsp(Map<String, String> paras) {
		List<ISP> list=new ArrayList<ISP>();
		ISP p=new ISP();
		p.setId("0");
		p.setName("自动");
		list.add(p);
		String grpid = paras.get("grpid");
		String sql = "select id,grpname,isp from devicegroup where id ="+grpid;
		List<Map<String,Object>> grplst = baseDao.getMapList(sql);
		if(null!=grplst&&grplst.size()>0){
			for(Map<String,Object> gmap:grplst){
				String isp=gmap.get("isp").toString();
				String[] cc=isp.split(",");
				for(String c:cc)
				{
					String[] bb=c.split(":");
					ISP sp=new ISP();
					if(bb.length>1)
					{
						sp.setId(bb[0]);
						sp.setName(bb[1]);
					}
					list.add(sp);
				}
			}
		}
		return list;
	}

	@Override
	public Map<String,Object> getDeviceFlow(Map<String, String> paras) {
		Map<String,Object> record = new HashMap<String, Object>();
		String devid = paras.get("devid");
		String monthSql = "SELECT SUM(usedchina+usedarea) as flow,calctm FROM `devflowmonth` where devid='"+devid+"' GROUP BY calctm ORDER BY calctm ASC";
		System.out.println(monthSql);
		//List<Map<String,Object>> monthList = baseDao.getMapList(monthSql);
		List<Map<String,Object>> monthList = RCPService.getStatisticsService().getMapList(monthSql);;
		List<String> monthTimeLst = new ArrayList<String>();	//时间集合
		List<Float> monthUsedLst = new ArrayList<Float>();	//数量集合
		
		if(monthList != null){
			for(int i=0 ;i<monthList.size(); i++){
				Map<String,Object> flowMap = monthList.get(i);
				float flow = Float.parseFloat(String.valueOf(flowMap.get("flow")))/1024;	//总流量
				monthUsedLst.add(flow);
				monthTimeLst.add(flowMap.get("calctm").toString());
				//System.out.println(flowMap.get("calctm").toString());
			}
		}
		record.put("monthTimeLst", monthTimeLst);	// 活跃设备时间
		record.put("monthUsedLst", monthUsedLst);	// 活跃设备数量
		return record;
	}

	@Override
	public int auto(int orgid, int accid, Map<String, String> para) 
	{
		List<String> sqlList = new ArrayList<String>();
		String devids = para.get("devids");
		String[] devid = devids.split(",");
		String ids = "";
		for(int i=0;i<devid.length;i++)
		{
			ids += "'"+devid[i]+"',";
			String sql = "update "+ProjectTools.getTableNameBySN(devid[i])+" set flag=1 WHERE devid ='"+devid[i]+"'";
			//DAOHelper.execute("ufidevice", sql);
			RCPService.getMifiService().execSql(sql);
		}
		ids = ids.substring(0, ids.length()-1);
		String sql = "INSERT INTO oplog (accid,orgid,optype,content,appuid,crttm,devid) VALUES("+ accid+","+orgid+",8,'自动化标记，设备ID:"+devids+"',0,"+DateTimeUtil.longtime()+",'')";
		sqlList.add(sql);
		baseDao.excuBatch(sqlList);

		return 0;
	}

	@Override
	public Map<String, Object> devreset(InputStream excelis, Account account,String copu,String rmode)	throws Exception 
	{
		int sucsnum = 0;//成功写入到数据库的条数
		List<String> titleList = new ArrayList<String>();//文件中列名称集合
		StringBuffer devbuf = new StringBuffer();
		/*返回的结果map*/
		Map<String,Object> resultMap = new HashMap<String, Object>();
		
		//导入数据的列
		Map<String,String> devcolumn = new HashMap<String,String>();
		devcolumn.put("设备ID", "devid");
		
		Workbook workbook = Workbook.getWorkbook(excelis);
		Sheet sheet = workbook.getSheet(0);
		int rows = sheet.getRows();
		int cols = sheet.getColumns();
		int countnum = rows - 1;//总数据条数
		
		for (int i = 0; i < rows; i++) {//控制循环读取一行数据
			if (0 == i) {//如果是第一行 第一行为标题行 非数据行
				int suitColumNum = 0;  //可以导入的excel列数
				for (int z = 0; z < cols; z++) {//控制读取行的列数据
					String titleName=sheet.getCell(z, i).getContents() == null ? "" : sheet.getCell(z, i).getContents();//获得标题
					if (null != titleName) {
						titleList.add(titleName);//将标题放入标题List
						if(devcolumn.containsKey(titleName)){
							suitColumNum ++;
						}
					}
				}
				if(suitColumNum != 1){
					resultMap.put("code", 1);
					resultMap.put("msg", "导入文件与模版的列不匹配！");
					return resultMap;
				}
			}else{
				for (int j = 0; j < cols; j++) {//控制读取行的列数据
					String columnname = "";
					if (null != titleList.get(j)) {//如果本次导入的列的标题不为空
						columnname = devcolumn.get(titleList.get(j));
					}
					if(null != columnname){
						//获得当前列值
						String cellValue = sheet.getCell(j, i).getContents() == null ? "" : sheet.getCell(j, i).getContents().trim();
						if ("".equals(cellValue)){
							logger.info("DEVICE IMPORT:"+"rows>>>"+i+", column>>>"+j+", is null!");
							break;
						}else if(cellValue.indexOf("'")>0){
							logger.info("DEVICE IMPORT:"+"rows>>>"+i+", column>>>"+j+", exit ' ,can not import!");
							break;
						}else{
							devbuf.append(",'"+cellValue+"'");
							sucsnum ++;
						}
					}
				}
			}
		}
		int orgid=account.getOrgid();
		
		List<Map<String,Object>> devLst=new ArrayList<Map<String,Object>>();
		for(int i=0;i<32;i++)
		{
			String sql = "SELECT devid,orgid FROM device"+i+" WHERE devid IN("+devbuf.substring(1)+") ";
			if(orgid != 1)
				sql += " AND orgid IN ("+getOrgids(orgid)+")";
			List<Map<String,Object>> temp=RCPService.getMifiService().getMapList(sql);
			if(temp!=null && temp.size()>0)
				devLst.addAll(temp);
		}
		
		String devids="";
		if(devLst == null || devLst.size() <= sucsnum){
			for(Map<String,Object> devMap : devLst){
				String dbdevid = String.valueOf(devMap.get("devid"));
				devids=devids+",'"+dbdevid+"'";
				
				String dbsql = "select count(1) from recycle where sn = '"+dbdevid+"'";
				String devsql = "";
				long num = baseDao.getLongField(dbsql).get(0);
				if(num>0){
					devsql = "update recycle set recount=recount+1,orgid="+orgid+" where sn = '"+dbdevid+"'";
				}else{
					devsql = "insert into recycle(sn,recount,orgid) values ('"+dbdevid+"',0,"+orgid+")";
				}	
				String logsql = "insert into recyclelog(sn,crttm,accid,accname,orgid,rmode) values ('"+dbdevid+"',"+DateTimeUtil.longtime()+","+account.getId()+",'"+account.getUsername()+"',"+orgid+","+rmode+")";
				baseDao.excuSql(devsql);
				baseDao.excuSql(logsql);
			}
		}
		else
			devids=devbuf.toString();
		
		for(int i=0;i<32;i++)
		{
			int devstat = 1;
			if ("6".equals(rmode) || "7".equals(rmode) || "8".equals(rmode) || "5".equals(rmode) || "23".equals(rmode)) {
				devstat = 9;
			}
			String sqli="UPDATE device"+i+" SET mode="+rmode+",appuid=0,flag=1,devstat="+devstat+",cellid='',celladdress='' WHERE devid IN("+devids.substring(1)+")";
			logger.info(sqli);
			RCPService.getMifiService().execSql(sqli);
		}
		
//		String delorder="DELETE FROM allorder WHERE devid IN("+devbuf.substring(1)+")";
//		logger.info(delorder);
//		RCPService.getBaseService().execSql(delorder);
		
		String delsu="DELETE FROM usersuit WHERE devid IN("+devbuf.substring(1)+")";
		logger.info(delsu);
		RCPService.getBaseService().execSql(delsu);
		
		String delkey="DELETE FROM packkey WHERE devid IN("+devbuf.substring(1)+")";
		logger.info(delkey);
		RCPService.getBaseService().execSql(delkey);
		
		String delstatus="DELETE FROM devstatus WHERE devid IN("+devbuf.substring(1)+")";
		logger.info(delstatus);
		RCPService.getBaseService().execSql(delstatus);
		
		String[] cc=devids.substring(1).split(",");
		for(String c:cc)
		{
			SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
			Date date = ProjectTools.addMonth(new Date(), 12);
			String dateStart = sdf.format(new Date()) + "000000";
			String dateEnd = sdf.format(date) + "000000";
			long nowtm = Long.parseLong(DateTimeUtil.longtime());//当前时间
			String suitval = String.valueOf(baseDao.getOneField(" SELECT IFNULL(pkgValue,0) AS pkgValue FROM package WHERE id ="+ProjectConstants.testPkgId).get(0));
			String usuitsql = "INSERT INTO usersuit(devid,orgid,remain,areaid,packid,effect,invalid,crttm,pkvalue) VALUES ("+c+","+orgid+",'"+suitval+"',"+ProjectConstants.chinaArea+","+ProjectConstants.testPkgId+","+dateStart+","+dateEnd+","+nowtm+",'"+suitval+"')";
			System.out.println(usuitsql);
			DAOHelper.execute(ProjectConstants.connDev, usuitsql);
			
			if(copu.equals("1"))//2天不限
			{
				String keysql="SELECT * FROM `packkey` WHERE packid=235 AND statu=0 and devid is null LIMIT 1";
				System.out.println(keysql);
				Map<String, Object> keymap=RCPService.getBaseService().getMap(keysql);
				String keyid=keymap.get("id").toString();
				String upkeysql="update packkey set devid="+c+" where id="+keyid;
				System.out.println(upkeysql);
				RCPService.getBaseService().execSql(upkeysql);
			}
			if(copu.equals("2"))//3天100G
			{
				String keysql="SELECT * FROM `packkey` WHERE packid=163 AND statu=0 and devid is null LIMIT 1";
				System.out.println(keysql);
				Map<String, Object> keymap=RCPService.getBaseService().getMap(keysql);
				String keyid=keymap.get("id").toString();
				String upkeysql="update packkey set devid="+c+" where id="+keyid;
				System.out.println(upkeysql);
				RCPService.getBaseService().execSql(upkeysql);
			}
			if(copu.equals("7"))//7天3G
			{
				String keysql="SELECT * FROM `packkey` WHERE packid=6 AND statu=0 and devid is null LIMIT 1";
				System.out.println(keysql);
				Map<String, Object> keymap=RCPService.getBaseService().getMap(keysql);
				String keyid=keymap.get("id").toString();
				String upkeysql="update packkey set devid="+c+" where id="+keyid;
				System.out.println(upkeysql);
				RCPService.getBaseService().execSql(upkeysql);
			}			
		}
		
//		String smdsql = "SELECT iccid,sn FROM smd WHERE flag=1 and sn IN("+devbuf.substring(1)+") ";
//		logger.info(smdsql);
//		List<Map<String,Object>> smdlist=RCPService.getBaseService().getMapList(smdsql);
//		if(smdlist!=null && smdlist.size()>0)
//		{
//			for(Map<String,Object> mp:smdlist)
//			{
//				String iccid=mp.get("iccid").toString();
//				if(iccid.length()==20)
//					iccid=iccid.substring(0,19);
//				String devid=mp.get("sn").toString();
//				String ksql="SELECT kasid,access_number FROM iccidtemp WHERE realstate='已实名' and iccid='"+iccid+"'";
//				logger.info(ksql);
//				List<Map<String, Object>> kl=RCPService.getIccidService().getMapList(ksql);
//				if(kl!=null && kl.size()>0)
//				{
//					String kasid=kl.get(0).get("kasid").toString();
//					String access_number=kl.get(0).get("access_number").toString();
//					
//					String simtasksql="insert into simtask(iccid,optask,crttm,kasid,devid,accessNumber) values ('"+iccid+"',3,"+DateTimeUtil.longtime()+","+kasid+",'"+devid+"','"+access_number+"')";
//					logger.info(simtasksql);
//					RCPService.getIccidService().execSql(simtasksql);
//				}
//			}
//		}
		
		// 设置卡为未实名和回仓时间
		String smdsql = "SELECT iccid,sn FROM smd WHERE sn IN("+devbuf.substring(1)+") ";
		logger.info(smdsql);
		List<Map<String,Object>> smdlist=RCPService.getBaseService().getMapList(smdsql);
		if(smdlist!=null && smdlist.size()>0)
		{
			StringBuilder iccidsb = new StringBuilder();
			for(Map<String,Object> mp:smdlist)
			{
				String iccid=String.valueOf(mp.get("iccid"));				
				String iccidnornesql="update iccidtemp set realstate='未实名',resettm='"+DateTimeUtil.longtime()+"' where iccid = '"+iccid+"'";
				RCPService.getIccidService().execSql(iccidnornesql);
				iccidsb.append(",'").append(iccid).append("'");
			}
		}
		
		if ("6".equals(rmode) || "7".equals(rmode) || "8".equals(rmode) || "1".equals(rmode) || "5".equals(rmode) || "23".equals(rmode)) {
			String smd="DELETE FROM smd WHERE sn IN("+devbuf.substring(1)+")";
			logger.info(smd);
			RCPService.getBaseService().execSql(smd);
		}
		
		String sql = "INSERT INTO oplog (accid,orgid,optype,content,appuid,crttm,devid) VALUES("+ account.getId()+","+account.getOrgid()+",1,'设备回仓，"+sucsnum+"条设备数据',0,"+DateTimeUtil.longtime()+",'')";
		baseDao.excuSql(sql);
		resultMap.put("code", 0);
		resultMap.put("countnum", countnum);
		resultMap.put("sucsnum", sucsnum);
		return resultMap;
	}

	@Override
	public int devreset(String devid, Account account,String copu,String rmode)	throws Exception 
	{
//		int sucsnum = 0;//成功写入到数据库的条数
//		List<String> titleList = new ArrayList<String>();//文件中列名称集合
//		StringBuffer devbuf = new StringBuffer();
//		devbuf.append(devid);
//		/*返回的结果map*/
//		Map<String,Object> resultMap = new HashMap<String, Object>();
//
//		int orgid=account.getOrgid();
//		
//		List<Map<String,Object>> devLst=new ArrayList<Map<String,Object>>();
//		for(int i=0;i<32;i++)
//		{
//			String sql = "SELECT devid,orgid FROM device"+i+" WHERE devid IN("+devbuf.substring(1)+") ";
//			if(orgid != 1)
//				sql += " AND orgid IN ("+getOrgids(orgid)+")";
//			List<Map<String,Object>> temp=RCPService.getMifiService().getMapList(sql);
//			if(temp!=null && temp.size()>0)
//				devLst.addAll(temp);
//		}
//		
//		String devids="";
//		if(devLst == null || devLst.size() <= sucsnum){
//			for(Map<String,Object> devMap : devLst){
//				String dbdevid = String.valueOf(devMap.get("devid"));
//				devids=devids+",'"+dbdevid+"'";
//				
//				String dbsql = "select count(1) from recycle where sn = '"+dbdevid+"'";
//				String devsql = "";
//				long num = baseDao.getLongField(dbsql).get(0);
//				if(num>0){
//					devsql = "update recycle set recount=recount+1,orgid="+orgid+" where sn = '"+dbdevid+"'";
//				}else{
//					devsql = "insert into recycle(sn,recount,orgid) values ('"+dbdevid+"',0,"+orgid+")";
//				}	
//				String logsql = "insert into recyclelog(sn,crttm,accid,accname,orgid,rmode) values ('"+dbdevid+"',"+DateTimeUtil.longtime()+","+account.getId()+",'"+account.getUsername()+"',"+orgid+","+rmode+")";
//				logger.info(devsql);
//				logger.info(logsql);
//				baseDao.excuSql(devsql);
//				baseDao.excuSql(logsql);
//			}
//		}
//		else
//			devids=devbuf.toString();
//		
//		for(int i=0;i<32;i++)
//		{
//			int devstat = 1;
//			if ("6".equals(rmode) || "7".equals(rmode) || "8".equals(rmode) || "5".equals(rmode)) {
//				devstat = 9;
//			}
//			String sqli="UPDATE device"+i+" SET mode="+rmode+",appuid=0,flag=1,devstat="+devstat+" WHERE devid IN("+devids.substring(1)+")";
//			logger.info(sqli);
//			RCPService.getMifiService().execSql(sqli);
//		}
//		
////		String delorder="DELETE FROM allorder WHERE devid IN("+devbuf.substring(1)+")";
////		logger.info(delorder);
////		RCPService.getBaseService().execSql(delorder);
//		
//		String delsu="DELETE FROM usersuit WHERE devid IN("+devbuf.substring(1)+")";
//		logger.info(delsu);
//		RCPService.getBaseService().execSql(delsu);
//		
//		String delkey="DELETE FROM packkey WHERE devid IN("+devbuf.substring(1)+")";
//		logger.info(delkey);
//		RCPService.getBaseService().execSql(delkey);
//		
//		String delstatus="DELETE FROM devstatus WHERE devid IN("+devbuf.substring(1)+")";
//		logger.info(delstatus);
//		RCPService.getBaseService().execSql(delstatus);
//		
//		String[] cc=devids.substring(1).split(",");
//		for(String c:cc)
//		{
//			SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
//			Date date = ProjectTools.addMonth(new Date(), 12);
//			String dateStart = sdf.format(new Date()) + "000000";
//			String dateEnd = sdf.format(date) + "000000";
//			long nowtm = Long.parseLong(DateTimeUtil.longtime());//当前时间
//			String suitval = String.valueOf(baseDao.getOneField(" SELECT IFNULL(pkgValue,0) AS pkgValue FROM package WHERE id ="+ProjectConstants.testPkgId).get(0));
//			String usuitsql = "INSERT INTO usersuit(devid,orgid,remain,areaid,packid,effect,invalid,crttm,pkvalue) VALUES ("+c+","+orgid+",'"+suitval+"',"+ProjectConstants.chinaArea+","+ProjectConstants.testPkgId+","+dateStart+","+dateEnd+","+nowtm+",'"+suitval+"')";
//			System.out.println(usuitsql);
//			DAOHelper.execute(ProjectConstants.connDev, usuitsql);
//			
//			if(copu.equals("1"))//2天不限
//			{
//				String keysql="SELECT * FROM `packkey` WHERE packid=235 AND statu=0 and devid is null LIMIT 1";
//				System.out.println(keysql);
//				Map<String, Object> keymap=RCPService.getBaseService().getMap(keysql);
//				String keyid=keymap.get("id").toString();
//				String upkeysql="update packkey set devid="+c+" where id="+keyid;
//				System.out.println(upkeysql);
//				RCPService.getBaseService().execSql(upkeysql);
//			}
//			if(copu.equals("2"))//3天100G
//			{
//				String keysql="SELECT * FROM `packkey` WHERE packid=163 AND statu=0 and devid is null LIMIT 1";
//				System.out.println(keysql);
//				Map<String, Object> keymap=RCPService.getBaseService().getMap(keysql);
//				String keyid=keymap.get("id").toString();
//				String upkeysql="update packkey set devid="+c+" where id="+keyid;
//				System.out.println(upkeysql);
//				RCPService.getBaseService().execSql(upkeysql);
//			}
//			if(copu.equals("7"))//7天3G
//			{
//				String keysql="SELECT * FROM `packkey` WHERE packid=6 AND statu=0 and devid is null LIMIT 1";
//				System.out.println(keysql);
//				Map<String, Object> keymap=RCPService.getBaseService().getMap(keysql);
//				String keyid=keymap.get("id").toString();
//				String upkeysql="update packkey set devid="+c+" where id="+keyid;
//				System.out.println(upkeysql);
//				RCPService.getBaseService().execSql(upkeysql);
//			}			
//		}
//		
////		String smdsql = "SELECT iccid,sn FROM smd WHERE flag=1 and sn IN("+devbuf.substring(1)+") ";
////		logger.info(smdsql);
////		List<Map<String,Object>> smdlist=RCPService.getBaseService().getMapList(smdsql);
////		if(smdlist!=null && smdlist.size()>0)
////		{
////			for(Map<String,Object> mp:smdlist)
////			{
////				String iccid=mp.get("iccid").toString();
////				if(iccid.length()==20)
////					iccid=iccid.substring(0,19);
////				String devid=mp.get("sn").toString();
////				String ksql="SELECT kasid,access_number FROM iccidtemp WHERE realstate='已实名' and iccid='"+iccid+"'";
////				logger.info(ksql);
////				List<Map<String, Object>> kl=RCPService.getIccidService().getMapList(ksql);
////				if(kl!=null && kl.size()>0)
////				{
////					String kasid=kl.get(0).get("kasid").toString();
////					String access_number=kl.get(0).get("access_number").toString();
////					
////					String simtasksql="insert into simtask(iccid,optask,crttm,kasid,devid,accessNumber) values ('"+iccid+"',3,"+DateTimeUtil.longtime()+","+kasid+",'"+devid+"','"+access_number+"')";
////					logger.info(simtasksql);
////					RCPService.getIccidService().execSql(simtasksql);
////				}
////			}
////		}
//		
//		// 设置卡为未实名和回仓时间
//		String smdsql = "SELECT iccid,sn FROM smd WHERE sn IN("+devbuf.substring(1)+") ";
//		logger.info(smdsql);
//		List<Map<String,Object>> smdlist=RCPService.getBaseService().getMapList(smdsql);
//		if(smdlist!=null && smdlist.size()>0)
//		{
//			for(Map<String,Object> mp:smdlist)
//			{
//				String iccid=mp.get("iccid").toString();
//				String iccidnornesql="update iccidtemp set realstate='未实名',resettm='"+DateTimeUtil.longtime()+"' where iccid = '"+iccid+"'";
//				RCPService.getIccidService().execSql(iccidnornesql);
//			}
//		}
//		
//		if ("6".equals(rmode) || "7".equals(rmode) || "8".equals(rmode) || "1".equals(rmode) || "5".equals(rmode)) {
//			String smd="DELETE FROM smd WHERE sn IN("+devbuf.substring(1)+")";
//			logger.info(smd);
//			RCPService.getBaseService().execSql(smd);
//		}
//		
//		String sql = "INSERT INTO oplog (accid,orgid,optype,content,appuid,crttm,devid) VALUES("+ account.getId()+","+account.getOrgid()+",1,'设备回仓，"+sucsnum+"条设备数据',0,"+DateTimeUtil.longtime()+",'')";
//		baseDao.excuSql(sql);
		return 0;
	}

	@Override
	public Map<String, Object> getDevAll(Map<String, String> para) 
	{
		Map<String,Object> pginfo=new HashMap<String,Object>();
		
		String devid=para.get("devid").toString();
		String sql="select * from smd where sn='"+devid+"'";
		List<Map<String, Object>> result=RCPService.getBaseService().getMapList(sql);
		if(result!=null && result.size()>0)
		{
			for(Map<String, Object> r:result)
			{
				String iccid=r.get("iccid").toString();
				int flag=Integer.parseInt(r.get("flag").toString());
//				String sq="select tdnet/1024 as tdnet,sysused/1024 as sysused from "+ProjectTools.getTableNameByIccid(iccid)+" where iccid='"+iccid+"'";
//				List<Map<String,Object>> mst=DAOHelper.find("ufiiccid", sq);
//				if(mst!=null && mst.size()>0)
//				{
//					Map<String,Object> m=mst.get(0);
//					String tdnet=m.get("tdnet").toString();
//					String sysused=m.get("sysused").toString();
//					r.put("tdnet", tdnet);
//					r.put("sysused", sysused);
//				}
//				else
//				{
//					r.put("tdnet", 0);
//					r.put("sysused", 0);
//				}
				
				String itp="";
				if(flag==3)
					itp="select kasid,cardstate,realstate from iccidtemp where iccid='"+iccid+"'";
				else
					itp="select kasid,cardstate,realstate from iccidtemp where LEFT(iccid,19)='"+iccid.substring(0,19)+"'";
				List<Map<String,Object>> itplist=DAOHelper.find("ufiiccid", itp);
				if(itplist!=null && itplist.size()>0)
				{
					Map<String,Object> m=itplist.get(0);
					String kasid=m.get("kasid").toString();
					
					String cardstate="";
					if(m.get("cardstate")==null)
						;
					else
						cardstate=m.get("cardstate").toString();
					
					String realstate="";
					if(m.get("realstate")==null)
						;
					else
						realstate=m.get("realstate").toString();
					
//					String flowA="0";
//					if(m.get("flowA")==null)
//						flowA="0";
//					else
//						flowA=m.get("flowA").toString();
					
					r.put("cardstate", cardstate);
					r.put("realstate", realstate);
//					r.put("flowA", flowA);
					r.put("kasid", kasid);
					
					String ks="select * from kas where id="+r.get("kasid").toString();
					Map<String, Object> mks=RCPService.getBaseService().getMap(ks);
					r.put("ksname", mks.get("kasname").toString());
				}
				else
				{
					r.put("cardstate", "");
					r.put("realstate", "");
//					r.put("flowA", "");
					r.put("ksname", "");
				}
				
				
			}
		}
		else
		{
			result= new ArrayList<Map<String, Object>>();

		}
		pginfo.put("rows", result);
		return pginfo;
	}

	@Override
	public Map<String, Object> getDevUsuit(int orgid,Map<String, String> para) {
		Map<String,Object> pginfo=new HashMap<String,Object>();
		
		String devid=para.get("devid").toString();
		String sql="SELECT u.devid,u.remain,u.effect,u.invalid,p.pkname FROM usersuit u,packageview p WHERE u.packid=p.pkid AND p.orgid="+orgid+" and u.devid='"+devid+"' and u.invalid>"+DateTimeUtil.longtime();
		List<Map<String, Object>> result=RCPService.getBaseService().getMapList(sql);
		if(result==null)
		{
			result= new ArrayList<Map<String, Object>>();
		}
		pginfo.put("rows", result);
		return pginfo;
	}

	@Override
	public Map<String, Object> getDevOrder(int orgid,Map<String, String> para) 
	{
		Map<String,Object> pginfo=new HashMap<String,Object>();
		
		String devid=para.get("devid").toString();
		String sql = "SELECT aor.onum,aor.crttm,aor.oprice,aor.ptype,p.pkname FROM allorder aor,packageview p WHERE aor.ostat>0 and aor.otype = 0 and aor.devid='"+devid+"' and aor.packid=p.pkid and p.orgid="+orgid+" order by aor.crttm desc";
		logger.info(sql);
		List<Map<String, Object>> result=RCPService.getBaseService().getMapList(sql);
		if(result==null)
		{
			result= new ArrayList<Map<String, Object>>();
		}
		pginfo.put("rows", result);
		return pginfo;
	}

	@Override
	public List<Map<String, Object>> getPackage(int orgid, Map<String, String> para)
	{
		int packType=0;
		String devid=para.get("devid").toString();
		String devgrpid=para.get("devgrpid").toString();
		if(devid.startsWith("1511") || devid.startsWith("1501") || devid.startsWith("1301"))
			packType=0;
		else if(devid.startsWith("1304") )
			packType=1;
		else if(devid.startsWith("5001") || devid.startsWith("5002"))
			packType=2;
		else
		{
			String typesql="SELECT * FROM `devicegroup` WHERE id="+devgrpid;
			Map<String,Object> result=RCPService.getBaseService().getMap(typesql);
			if(result!=null && result.size()>0 && result.get("devtype")!=null)
			{
				packType=Integer.parseInt(result.get("devtype").toString());
			}
		}
		String sql = "SELECT packageview.pkid AS id,packageview.pkname AS `text`,packageview.price,package.pktype FROM packageview,package WHERE packageview.pkid=package.id and packageview.orgid="+orgid+" and package.pktype="+packType+" order by packageview.reorder";
		List<Map<String,Object>> pkgList = baseDao.getMapList(sql);
		if(pkgList != null && pkgList.size()>0){
			for(int i=0;i<pkgList.size();i++){
				Map<String,Object> pkgMap = pkgList.get(i);
				String text = String.valueOf(pkgMap.get("text"));
				int pktype = Integer.parseInt(String.valueOf(pkgMap.get("pktype")));
				String pkp="";
				if(pktype==0)
					pkp="4G MiFi";
				if(pktype==1)
					pkp="4G CPE";
				if(pktype==2)
					pkp="5G CPE";
				if(pktype==3)
					pkp="5G MiFi";				
				float price = Float.parseFloat(String.valueOf(pkgMap.get("price")))/1000;
				String pStr = String.valueOf(price);
				pStr = pStr.replaceAll("0+?$", "");	//去掉后面无用的零
				pStr = pStr.replaceAll("[.]$", "");	//如小数点后面全是零则去掉小数点
				pkgMap.put("text", text+"("+pStr+"元){"+pkp+"}");
			}
		}
		return pkgList;
	}
	
	@Override
	public List<Map<String, Object>> getSPackage(int orgid, Map<String, String> para)
	{
		String sql = "SELECT id AS id,name AS `text`,price FROM speedpack WHERE orgid="+orgid;
		List<Map<String,Object>> pkgList = baseDao.getMapList(sql);
		if(pkgList != null && pkgList.size()>0){
			for(int i=0;i<pkgList.size();i++){
				Map<String,Object> pkgMap = pkgList.get(i);
				String text = String.valueOf(pkgMap.get("text"));			
				float price = Float.parseFloat(String.valueOf(pkgMap.get("price")))/1000;
				String pStr = String.valueOf(price);
				pStr = pStr.replaceAll("0+?$", "");	//去掉后面无用的零
				pStr = pStr.replaceAll("[.]$", "");	//如小数点后面全是零则去掉小数点
				pkgMap.put("text", text+"("+pStr+"元)");
			}
		}
		return pkgList;
	}
	

	@Override
	public Map<String, Object> devbatchupd(InputStream excelis, Account account,String dggrp,String rmode)	throws Exception 
	{
		int sucsnum = 0;//成功写入到数据库的条数
		List<String> titleList = new ArrayList<String>();//文件中列名称集合
		StringBuffer devbuf = new StringBuffer();
		/*返回的结果map*/
		Map<String,Object> resultMap = new HashMap<String, Object>();
		
		//导入数据的列
		Map<String,String> devcolumn = new HashMap<String,String>();
		devcolumn.put("设备ID", "devid");
		
		Workbook workbook = Workbook.getWorkbook(excelis);
		Sheet sheet = workbook.getSheet(0);
		int rows = sheet.getRows();
		int cols = sheet.getColumns();
		int countnum = rows - 1;//总数据条数
		
		for (int i = 0; i < rows; i++) {//控制循环读取一行数据
			if (0 == i) {//如果是第一行 第一行为标题行 非数据行
				int suitColumNum = 0;  //可以导入的excel列数
				for (int z = 0; z < cols; z++) {//控制读取行的列数据
					String titleName=sheet.getCell(z, i).getContents() == null ? "" : sheet.getCell(z, i).getContents();//获得标题
					if (null != titleName) {
						titleList.add(titleName);//将标题放入标题List
						if(devcolumn.containsKey(titleName)){
							suitColumNum ++;
						}
					}
				}
				if(suitColumNum != 1){
					resultMap.put("code", 1);
					resultMap.put("msg", "导入文件与模版的列不匹配！");
					return resultMap;
				}
			}else{
				for (int j = 0; j < cols; j++) {//控制读取行的列数据
					String columnname = "";
					if (null != titleList.get(j)) {//如果本次导入的列的标题不为空
						columnname = devcolumn.get(titleList.get(j));
					}
					if(null != columnname){
						//获得当前列值
						String cellValue = sheet.getCell(j, i).getContents() == null ? "" : sheet.getCell(j, i).getContents().trim();
						if ("".equals(cellValue)){
							logger.info("DEVICE IMPORT:"+"rows>>>"+i+", column>>>"+j+", is null!");
							break;
						}else if(cellValue.indexOf("'")>0){
							logger.info("DEVICE IMPORT:"+"rows>>>"+i+", column>>>"+j+", exit ' ,can not import!");
							break;
						}else{
							devbuf.append(",'"+cellValue+"'");
							sucsnum ++;
						}
					}
				}
			}
		}
		int orgid=account.getOrgid();
		
		
		for(int i=0;i<32;i++)
		{
			String sqli="UPDATE device"+i+" SET mode="+rmode+",devgrpid="+dggrp+" WHERE devid IN("+devbuf.substring(1)+")";
			logger.info(sqli);
			RCPService.getMifiService().execSql(sqli);
		}

		String sql = "INSERT INTO oplog (accid,orgid,optype,content,appuid,crttm,devid) VALUES("
				+ account.getId()+","+account.getOrgid()+",3,'设备批量修改，"+ sucsnum+"条设备数据>>>运行模式:"+rmode+"，设备组："+dggrp+"',0,"+DateTimeUtil.longtime()+",'')";
		baseDao.excuSql(sql);
		resultMap.put("code", 0);
		resultMap.put("countnum", countnum);
		resultMap.put("sucsnum", sucsnum);
		return resultMap;
	}
}