package com.cim.idm.wmsextend.generic.wmsmail;

import com.alibaba.fastjson.JSON;
import com.cim.idm.framework.IDMFrameServiceProxy;
import com.cim.idm.framework.data.EventInfo;
import com.cim.idm.wmsextend.generic.wmsmail.MWMSMail;
import com.cim.idm.wmsextend.generic.wmsmail.MailSenderInfo;
import com.cim.idm.wmsextend.generic.wmsmail.SimpleMailSender;
import com.cim.idm.wmspackage.mail.management.data.MailSendHistory;
import com.cim.idm.wmspackage.materialundo.info.MaterialUnDoinfo;
import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.write.*;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.jdom.Document;
import org.jdom.Element;
import org.springframework.beans.factory.annotation.Autowired;

import javax.mail.AuthenticationFailedException;
import javax.mail.MessagingException;
import javax.mail.SendFailedException;
import javax.mail.internet.AddressException;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.*;

public class MailUtil{
	@Autowired
	private com.cim.idm.wmsextend.generic.wmsmail.MWMSMail MWMSMail;
	private static Log log = LogFactory.getLog("MailUtil");

	//20171115 wzj 入库完即发送质检邮件
		public void sendPreOQAMail(String siteName,List<MaterialUnDoinfo> undolist) throws Exception
		{
			List<Map<String, Object>> getSiteName = getRecMailUsersSiteName("scheduleMethodQIP");//获取工厂名		
			//String siteName=getSiteName.get(i).get("SITENAME").toString();		
			String undoid=undolist.get(0).getUnDoID().toString();
			String dbSql  =  "SELECT  MATERIALSPECNAME, DESCRIPTION, LOCATIONNAME, RECEIVEACTNO, SUPPLIERNAME, SUM (MATERIALQUANTITY) AS MATERIALQUANTITY, MATERIALUNIT, "
			+ " ERPLOCATION, MATERIALPROCESSGROUPNAME, TO_CHAR (RECEIVETIME, 'yyyy-mm-dd hh24:mi:ss') AS RECEIVETIME, MAILTIMES, MAILLASTDATE, SITENAME "
			+ " FROM  (SELECT  A.SITENAME, A.MATERIALPACKINGNAME, A.MATERIALSPECNAME, B.DESC_CN AS DESCRIPTION, A.LOCATIONNAME, A.RECEIVEACTNO, "
			+ " D.SUPPLIERNAME, A.MATERIALQUANTITY, A.ERPLOCATION, A.MATERIALPROCESSGROUPNAME, A.RECEIVETIME, B.MATERIALUNIT, C.MAILTIMES, C.MAILLASTDATE"
			+ " FROM   MATERIALPACKING A, MATERIALSPEC B, MATERIALPROCESSGROUP C, SUPPLIER D "	
			+ " WHERE   A.SITENAME = :SITENAME "
			+ " AND A.SITENAME = B.SITENAME "
			+ " AND A.SITENAME = C.SITENAME "
			+ " AND A.UNDOID = :UNDOID "
			+ " AND A.SUPPLIERNAME = D.SUPPLIERNO "
			+ " AND C.MATERIALPROCESSGROUPNAME = A.MATERIALPROCESSGROUPNAME "
			+ " AND A.MATERIALSPECNAME = B.MATERIALSPECNAME "
			+ " AND A.MATERIALQUANTITY > 0 "
			+ " AND C.PROCESSGROUPSTATE = 'PREOQA' "
			+ " AND A.STOCKSTATE = 'Stocked' "
			+ " AND A.PACKINGSTATE = 'Released' ) E "
			+ " GROUP BY MAILTIMES, MAILLASTDATE, SITENAME, MATERIALSPECNAME, MATERIALPROCESSGROUPNAME, RECEIVETIME, ERPLOCATION, DESCRIPTION, RECEIVEACTNO, LOCATIONNAME, SUPPLIERNAME, MATERIALUNIT "		
			+ " ORDER BY RECEIVETIME, RECEIVEACTNO ASC ";		
			List<Map<String, Object>> sqlResult = IDMFrameServiceProxy.getSqlTemplate().queryForList(dbSql,new Object[]{siteName,undoid});

			if(sqlResult != null && !sqlResult.isEmpty())
			{
			Date sendDate = new Date(); // mail send  time
			String mailSendDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(sendDate);
			String fs=System.getProperties().getProperty("file.separator");
			createDir(fs+"attachment");			
			String fileUrl =fs+"attachment"+ fs + new SimpleDateFormat("yyyyMMddHHmmsss").format(sendDate) + ".xls"; 

			//生成excel
			FileOutputStream fout;
			try {
				fout = new FileOutputStream(fileUrl);
				createQualityInspectionPendingExcel(sqlResult,fout);
			} catch (WriteException e) {e.printStackTrace();
			} catch (IOException e) {e.printStackTrace();}
			
			MailSenderInfo mailInfo = new MailSenderInfo();
			mailInfo.setMailServerHost(MWMSMail.getHost());   
			mailInfo.setMailServerPort(MWMSMail.getPort());   
			mailInfo.setValidate(true);   
			mailInfo.setUserName(MWMSMail.getUsername());   
			mailInfo.setPassword(MWMSMail.getPassword());
			mailInfo.setFromAddress(MWMSMail.getFromAddress());
			
			MailSendHistory mailSendHistory = new MailSendHistory(); 
			
			mailSendHistory.setSendTime(mailSendDate);
			mailSendHistory.setInterfaceType("scheduleMethodQIP");
			
		 	String[] receiver = getRecMailUsers("scheduleMethodQIP",siteName);
			if(receiver != null){
				mailInfo.setToAddress(receiver);
	            mailInfo.setAttachFileNames(new String[]{fileUrl});
				mailInfo.setSubject("品质待检");  
				
				SimpleMailSender sms = new SimpleMailSender();

				mailInfo.setContent(getQualityInspectionPending(sqlResult).toString());   
				if(mailInfo.getContent() == null){
					mailSendHistory.setErrorType("邮件内容是空的");
					mailSendHistory.setSendState("false");
				}else{
					try {
						sms.sendHtmlMail(mailInfo);
						mailSendHistory.setSendState("true");
						//品质待检的db操作 每天定时扫描的db操作
						updateSendTimesAndDate(sqlResult,mailSendDate);
					} catch (Exception e) {
						if(e.getClass() == SendFailedException.class){
							mailSendHistory.setErrorType("收件人地址错误");
							mailSendHistory.setSendState("false");
						}else if(e.getClass() == AuthenticationFailedException.class){
							mailSendHistory.setErrorType("发件人用户名密码错误");
							mailSendHistory.setSendState("false");
						}else if (e.getClass() == MessagingException.class) {
							mailSendHistory.setErrorType("连接发件服务器失败");
							mailSendHistory.setSendState("false");
						}
					}
				}
			}else{
				mailSendHistory.setErrorType("收件人地址列表是空的");
				mailSendHistory.setSendState("false");
			}			
				//updateMailSendHistory(mailSendHistory);
			}	
		}

	public void SendSimpleMail(){
		List<Map<String, Object>> getSiteName = getRecMailUsersSiteName("scheduleMethodQIP");//获取工厂名
		for(int i=0;i<getSiteName.size();i++)
		{
			String siteName=getSiteName.get(i).toString();
		String dbSql  =  "SELECT MATERIALSPECNAME,DESCRIPTION,RECEIVEACTNO,SUPPLIERNAME,SUM(MATERIALQUANTITY) AS MATERIALQUANTITY," +
				" MATERIALUNIT,ERPLOCATION,MATERIALPROCESSGROUPNAME,to_char(RECEIVETIME,'yyyy-mm-dd hh:mm:ss') as RECEIVETIME,MAILTIMES,MAILLASTDATE,SITENAME "+
				"FROM ("+
				"SELECT "+
				"C.MAILTIMES,C.MAILLASTDATE,A.SITENAME,A.MATERIALPACKINGNAME,A.MATERIALSPECNAME,B.DESCRIPTION,"+
				"A.RECEIVEACTNO,A.SUPPLIERNAME,A.MATERIALQUANTITY,A.ERPLOCATION,A.MATERIALPROCESSGROUPNAME,A.RECEIVETIME,B.MATERIALUNIT"+
				" FROM MATERIALPACKING A, MATERIALSPEC B, MATERIALPROCESSGROUP C"+
				" WHERE C.PROCESSGROUPSTATE = 'PREOQA' "+
				" AND C.MATERIALPROCESSGROUPNAME = A.MATERIALPROCESSGROUPNAME"+
				" AND A.MATERIALSPECNAME = B.MATERIALSPECNAME"+
				" AND A.SITENAME = B.SITENAME "+
				" AND A.STOCKSTATE = 'Stocked'"+
				" AND A.PACKINGSTATE = 'Released'"+
				" AND A.SITENAME = '"+ siteName +"'"+
				" AND A.SITENAME = C.SITENAME)C GROUP BY "
				+ " MAILTIMES,MAILLASTDATE,SITENAME,MATERIALSPECNAME,MATERIALPROCESSGROUPNAME,RECEIVETIME,ERPLOCATION,DESCRIPTION,RECEIVEACTNO,SUPPLIERNAME,MATERIALUNIT  " + " ORDER BY RECEIVETIME,RECEIVEACTNO ASC";

		List<Map<String, Object>> sqlResult = IDMFrameServiceProxy.getSqlTemplate().queryForList(dbSql);

		if(sqlResult != null && !sqlResult.isEmpty()){
			Date sendDate = new Date(); // mail send  time
			String mailSendDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(sendDate);

			createDir("/attachment");			
			String fileUrl ="/attachment/" + new SimpleDateFormat("yyyyMMddHHmmsss").format(sendDate) + ".xls"; //E:/students.xls

			//生成excel
			FileOutputStream fout;
			try {
				fout = new FileOutputStream(fileUrl);
				createQualityInspectionPendingExcel(sqlResult,fout);
			} catch (WriteException e) {e.printStackTrace();
			} catch (IOException e) {e.printStackTrace();}


			MailSenderInfo mailInfo = new MailSenderInfo();   
			mailInfo.setMailServerHost(MWMSMail.getHost());   
			mailInfo.setMailServerPort(MWMSMail.getPort());   
			mailInfo.setValidate(true);   
			mailInfo.setUserName(MWMSMail.getUsername());   
			mailInfo.setPassword(MWMSMail.getPassword());
			mailInfo.setFromAddress(MWMSMail.getFromAddress());
			mailInfo.setSubject("品质待检列表");  

			SimpleMailSender sms = new SimpleMailSender();
			//sms.sendTextMail(mailInfo);//发送文体格式   
			MailSendHistory mailSendHistory = new MailSendHistory();  

			mailSendHistory.setSendTime(mailSendDate);
			mailSendHistory.setInterfaceType("SendSimpleMail");

			
			String[] receiver = getRecMailUsers("SendSimpleMail",siteName);
			if(receiver != null){
				mailInfo.setToAddress(receiver);
				mailInfo.setAttachFileNames(new String[]{fileUrl});
				mailInfo.setContent(getQualityInspectionPending(sqlResult).toString());  

				if(mailInfo.getContent() == null){
					mailSendHistory.setErrorType("邮件内容是空的");
					mailSendHistory.setSendState("false");
				}else{
					try {
						sms.sendHtmlMail(mailInfo);
						mailSendHistory.setSendState("true");
						//修改 MATERIALPROCESSGROUP表邮件发送历史
						updateSendTimes(sqlResult,mailSendDate);
					} catch (Exception e) {
						if(e.getClass() == SendFailedException.class){
							mailSendHistory.setErrorType("收件人地址错误");
							mailSendHistory.setSendState("false");
						}else if(e.getClass() == AuthenticationFailedException.class){
							mailSendHistory.setErrorType("发件人用户名密码错误");
							mailSendHistory.setSendState("false");
						}else if (e.getClass() == MessagingException.class) {
							mailSendHistory.setErrorType("连接发件服务器失败");
							mailSendHistory.setSendState("false");
						}
					}
				}


				
				
			}else{
				mailSendHistory.setErrorType("收件人地址列表是空的");
				mailSendHistory.setSendState("false");
			}
			//品质待检的db操作
			updateMailSendHistory(mailSendHistory);
		}
		}
	}
	

/**
 * 处理品质待检发邮件时候的db操作
 * 参数依次：发送邮件的时间、发送状态，调用接口名
 */
	public void updateMailSendHistory(MailSendHistory mailSH){
		//insert into mailsendhistory(ids,sendtime,sendstate,interfacetype) values(seq_mail.nextval,'2016 13:14','true','sendsimplemail');
		 
		 String sql = "insert into mailsendhistory(ids,sendtime,sendstate,errortype,interfacetype) "
		 		+ "values(seq_mail.nextval,:sendtime,:sendstate,:errortype,:interfacetype)";
		 Map<String, Object> bindMap = new HashMap<String, Object>();
		 bindMap.put("sendtime", mailSH.getSendTime());
		 bindMap.put("sendstate", mailSH.getSendState());
		 bindMap.put("errortype", mailSH.getErrorType());
		 bindMap.put("interfacetype",mailSH.getInterfaceType());
		 
		 IDMFrameServiceProxy.getSqlTemplate().update(sql, bindMap);
	}
	/**
	 * 修改mail 只对没有发过邮件的记录进行操作，更新发送次数位1
	 */
	public void updateSendTimes(List<Map<String, Object>> sqlResult,String sendDate){
		if(!sqlResult.isEmpty()){
			for(int i = 0;i < sqlResult.size();i++){
				if(sqlResult.get(i).get("SITENAME")!=null&&!"".equals(sqlResult.get(i).get("SITENAME"))){// pk SITENAME + MATERIALPROCESSGROUPNAME
					if(sqlResult.get(i).get("MATERIALPROCESSGROUPNAME")!=null&&!"".equals(sqlResult.get(i).get("MATERIALPROCESSGROUPNAME"))){
						if(sqlResult.get(i).get("MAILTIMES") == null || 
								sqlResult.get(i).get("MAILTIMES") == "" ||
								sqlResult.get(i).get("MAILTIMES").toString().equals("0") ){//检测是不是第一次发邮件
							
							String sqlString = "UPDATE MATERIALPROCESSGROUP set  MAILTIMES=1,MAILLASTDATE='"+sendDate+"'  where SITENAME=:sitename and MATERIALPROCESSGROUPNAME=:mpgName";
							 Map<String, Object> bindMap = new HashMap<String, Object>();
							 bindMap.put("sitename", sqlResult.get(i).get("SITENAME"));
							 bindMap.put("mpgName",sqlResult.get(i).get("MATERIALPROCESSGROUPNAME"));
							 
							IDMFrameServiceProxy.getSqlTemplate().update(sqlString, bindMap);
						}
						
					}
				}
			}
		}
	}
	/**
	 * 定时扫描，更新发送次数和当前发送时间
	 */
	public void updateSendTimesAndDate(List<Map<String, Object>> sqlResult,String sendDate){
		if(!sqlResult.isEmpty()){
			for(int i = 0;i < sqlResult.size();i++){
				if(sqlResult.get(i).get("SITENAME")!=null&&!"".equals(sqlResult.get(i).get("SITENAME"))){// pk SITENAME + MATERIALPROCESSGROUPNAME
					if(sqlResult.get(i).get("MATERIALPROCESSGROUPNAME")!=null&&!"".equals(sqlResult.get(i).get("MATERIALPROCESSGROUPNAME"))){

							int times = Integer.valueOf(sqlResult.get(i).get("MAILTIMES").toString()) + 1;
						
							String sqlString = "UPDATE MATERIALPROCESSGROUP set  MAILTIMES=:mailtimes,MAILLASTDATE=:maillastdate  where SITENAME=:sitename and MATERIALPROCESSGROUPNAME=:mpgName";
							 Map<String, Object> bindMap = new HashMap<String, Object>();
							 bindMap.put("mailtimes", times);
							 bindMap.put("maillastdate",sendDate);
							 bindMap.put("sitename", sqlResult.get(i).get("SITENAME"));
							 bindMap.put("mpgName",sqlResult.get(i).get("MATERIALPROCESSGROUPNAME"));
							 
							IDMFrameServiceProxy.getSqlTemplate().update(sqlString, bindMap);
					}
				}
			}
		}
	}
	
	/**
	 * 品质待检html代码
	 */
	public StringBuffer getQualityInspectionPending(List<Map<String, Object>> sqlResult){
		StringBuffer mailText = new StringBuffer();

		mailText.append("<table width='100%'>");
		mailText.append("<tr height='30px'>");
		mailText.append("<td align='left' width='30%'>您好！<br/><br/></td>");
		mailText.append("</tr>");
		mailText.append("<tr>");
		mailText.append("<td align='left' width='30%'>目前有以下材料到货【具体信息如下】，请及时检查。谢谢<br/><br/></td>");
		mailText.append("</tr>");
		mailText.append("<tr><td>");
		mailText.append("<table border='1' width='100%' style='text-align:center;border-collapse: collapse;border: none;border: solid #000 1px;'>");
		mailText.append("<tr>");
		mailText.append("<td width='5%'>序号</td><td width='5%'>工厂</td><td width='10%'>物料号</td><td width='15%'>物料描述</td>");
		mailText.append("<td width='10%'>抬头文本</td><td width='10%'>供应商</td><td width='5%'>到货数量</td>");
		mailText.append("<td width='5%'>单位</td><td width='8%'>库存地</td><td width='10%'>货位地</td><td width='10%'>批次</td><td width='7%'>入库日期</td>");
		mailText.append("</tr>");

		if(sqlResult != null && !sqlResult.isEmpty())
			for(int i = 0 ; i<sqlResult.size(); i++){
				Map<String, Object> tmp = sqlResult.get(i);

				mailText.append("<tr><td>" + (i+1) + "</td>");
				mailText.append("<td>" + (tmp.get("SITENAME")!=null&&!"".equals(tmp.get("SITENAME"))?tmp.get("SITENAME"):" ") + "</td>");
				mailText.append("<td>" + (tmp.get("MATERIALSPECNAME")!=null&&!"".equals(tmp.get("MATERIALSPECNAME"))?tmp.get("MATERIALSPECNAME"):" ") + "</td>");
				mailText.append("<td>" + (tmp.get("DESCRIPTION") != null&&!"".equals(tmp.get("DESCRIPTION"))?tmp.get("DESCRIPTION"):" ") + "</td>");
				mailText.append("<td>" + (tmp.get("RECEIVEACTNO") != null&&!"".equals(tmp.get("RECEIVEACTNO"))?tmp.get("RECEIVEACTNO"):" ") + "</td>");
				mailText.append("<td>" + (tmp.get("SUPPLIERNAME") != null&&!"".equals(tmp.get("SUPPLIERNAME"))?tmp.get("SUPPLIERNAME"):" ") + "</td>");
				mailText.append("<td>" + (tmp.get("MATERIALQUANTITY") != null&&!"".equals(tmp.get("MATERIALQUANTITY"))?tmp.get("MATERIALQUANTITY"):" ") + "</td>");
				mailText.append("<td>" + (tmp.get("MATERIALUNIT") != null&&!"".equals(tmp.get("MATERIALUNIT"))?tmp.get("MATERIALUNIT"):" ") + "</td>");
				mailText.append("<td>" + (tmp.get("ERPLOCATION") != null&&!"".equals(tmp.get("ERPLOCATION"))?tmp.get("ERPLOCATION"):" ") + "</td>");
				mailText.append("<td>" + (tmp.get("LOCATIONNAME") != null&&!"".equals(tmp.get("LOCATIONNAME"))?tmp.get("LOCATIONNAME"):" ") + "</td>");
				mailText.append("<td>" + (tmp.get("MATERIALPROCESSGROUPNAME") != null&&!"".equals(tmp.get("MATERIALPROCESSGROUPNAME"))?tmp.get("MATERIALPROCESSGROUPNAME"):" ") + "</td>");
				mailText.append("<td>" + (tmp.get("RECEIVETIME") != null&&!"".equals(tmp.get("RECEIVETIME"))?tmp.get("RECEIVETIME"):" ") + "</td></tr>");

			}
		mailText.append("</table>");
		mailText.append("</td>");
		mailText.append("</tr>");
		mailText.append("<tr><td></td></tr>");
		mailText.append("</table>");

		return mailText;
	}

	


	/**
	 * 品质待检excel 代码
	 * EXECL code Quality Inspection Pending
	 */
	
	public void createQualityInspectionPendingExcel(List<Map<String, Object>> sqlResult,OutputStream os) throws WriteException,IOException {
		WritableWorkbook workbook = Workbook.createWorkbook(os);
		WritableSheet sheet = workbook.createSheet("First Sheet", 0);

		sheet.mergeCells(0, 0, 9, 0);//添加合并单元格，第一个参数是起始列，第二个参数是起始行，第三个参数是终止列，第四个参数是终止行
		WritableFont bold = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD);//设置字体种类和黑体显示,字体为Arial,字号大小为10,采用黑体显示
		WritableCellFormat titleFormate = new WritableCellFormat(bold);//生成一个单元格样式控制对象
		titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.TOP);//单元格的内容垂直方向居中
		titleFormate.setWrap(true);
		sheet.addCell(new Label(0,0,"您好！\n\n目前有以下材料到货【具体信息如下】，请及时检查。谢谢\n",titleFormate));

		sheet.setRowView(0, 1000, false);

		sheet.addCell(new Label(0,1,"序号"));
		sheet.addCell(new Label(1,1,"工厂"));
		sheet.addCell(new Label(2,1,"物料号"));
		sheet.addCell(new Label(3,1,"物料描述"));
		sheet.addCell(new Label(4,1,"抬头文本"));
		sheet.addCell(new Label(5,1,"供应商"));
		sheet.addCell(new Label(6,1,"到货数量"));
		sheet.addCell(new Label(7,1,"单位"));
		sheet.addCell(new Label(8,1,"库存地"));
		sheet.addCell(new Label(9,1,"货位地"));
		sheet.addCell(new Label(10,1,"批次"));
		sheet.addCell(new Label(11,1,"入库日期"));
		sheet.addCell(new Label(12,1,"到期剩余"));

		if(sqlResult != null && !sqlResult.isEmpty())
			for(int i = 0 ; i<sqlResult.size(); i++){
				Map<String, Object> tmp = sqlResult.get(i);

				sheet.addCell(new Label(0,2+i,(i+1)+""));
				sheet.addCell(new Label(1,2+i,(tmp.get("SITENAME")!=null?tmp.get("SITENAME").toString():"")));
				sheet.addCell(new Label(2,2+i,(tmp.get("MATERIALSPECNAME")!=null?tmp.get("MATERIALSPECNAME").toString():"")));
				sheet.addCell(new Label(3,2+i,(tmp.get("DESCRIPTION") != null?tmp.get("DESCRIPTION").toString():"")));
				sheet.addCell(new Label(4,2+i,(tmp.get("RECEIVEACTNO")!= null?tmp.get("RECEIVEACTNO").toString():"")));
				sheet.addCell(new Label(5,2+i,(tmp.get("SUPPLIERNAME")!= null?tmp.get("SUPPLIERNAME").toString():"")));
				sheet.addCell(new Label(6,2+i,(tmp.get("MATERIALQUANTITY")!= null?tmp.get("MATERIALQUANTITY").toString():"")));
				sheet.addCell(new Label(7,2+i,(tmp.get("MATERIALUNIT")!= null?tmp.get("MATERIALUNIT").toString():"")));
				sheet.addCell(new Label(8,2+i,(tmp.get("ERPLOCATION")!= null?tmp.get("ERPLOCATION").toString():"")));
				sheet.addCell(new Label(9,2+i,(tmp.get("LOCATIONNAME")!= null?tmp.get("LOCATIONNAME").toString():"")));
				sheet.addCell(new Label(10,2+i,(tmp.get("MATERIALPROCESSGROUPNAME")!= null?tmp.get("MATERIALPROCESSGROUPNAME").toString():"")));
				sheet.addCell(new Label(11,2+i,(tmp.get("RECEIVETIME")!= null?tmp.get("RECEIVETIME").toString():"")));
				sheet.addCell(new Label(12,2+i,(tmp.get("DAYS")!= null?tmp.get("DAYS").toString():"")));
			}
		//把创建的内容写入到输出流中，并关闭输出流
		workbook.write();
		workbook.close();
		os.close();

	}
	/*生成excel demo*/	
	public void createExcel(OutputStream os) throws WriteException,IOException {
		//创建工作薄
		WritableWorkbook workbook = Workbook.createWorkbook(os);
		//创建新的一页
		WritableSheet sheet = workbook.createSheet("First Sheet", 0);

		//构造表头
		sheet.mergeCells(0, 0, 9, 0);//添加合并单元格，第一个参数是起始列，第二个参数是起始行，第三个参数是终止列，第四个参数是终止行
		WritableFont bold = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD);//设置字体种类和黑体显示,字体为Arial,字号大小为10,采用黑体显示
		WritableCellFormat titleFormate = new WritableCellFormat(bold);//生成一个单元格样式控制对象
		titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.TOP);//单元格的内容垂直方向居中
		titleFormate.setWrap(true);
		sheet.addCell(new Label(0,0,"您好！\n\n目前有以下材料到货【具体信息如下】，请及时检查。谢谢\n",titleFormate));

		sheet.setRowView(0, 1000, false);

		/*
        //创建要显示的具体内容
        Label formate = new Label(0,0,"数据格式");
        sheet.addCell(formate);
        Label floats = new Label(1,0,"浮点型");
        sheet.addCell(floats);
        Label integers = new Label(2,0,"整型");
        sheet.addCell(integers);
        Label booleans = new Label(3,0,"布尔型");
        sheet.addCell(booleans);
        Label dates = new Label(4,0,"日期格式");
        sheet.addCell(dates);

        Label example = new Label(0,1,"数据示例");
        sheet.addCell(example);
        //浮点数据
        Number number = new Number(1,1,3.1415926535);
        sheet.addCell(number);
        //整形数据
        Number ints = new Number(2,1,15042699);
        sheet.addCell(ints);
        Boolean bools = new Boolean(3,1,true);
        sheet.addCell(bools);
        //日期型数据
        Calendar c = Calendar.getInstance();
        Date date = c.getTime();
        WritableCellFormat cf1 = new WritableCellFormat(DateFormats.FORMAT1);
        DateTime dt = new DateTime(4,1,date,cf1);
        sheet.addCell(dt);
		 */
		sheet.addCell(new Label(0,1,"序号"));
		sheet.addCell(new Label(1,1,"工厂"));
		sheet.addCell(new Label(2,1,"物料号"));
		sheet.addCell(new Label(3,1,"物料描述"));
		sheet.addCell(new Label(4,1,"抬头文本"));
		sheet.addCell(new Label(5,1,"供应商"));
		sheet.addCell(new Label(6,1,"到货数量"));
		sheet.addCell(new Label(7,1,"单位"));
		sheet.addCell(new Label(8,1,"库存地"));
		sheet.addCell(new Label(9,1,"货位地"));
		sheet.addCell(new Label(10,1,"批次"));
		sheet.addCell(new Label(11,1,"入库日期"));
		//把创建的内容写入到输出流中，并关闭输出流
		workbook.write();
		workbook.close();
		os.close();
	}
	
	/**
	 * 待检每天定时刷新接口
	 */
	public void scheduleMethodQIP() {
		
		List<Map<String, Object>> getSiteName = getRecMailUsersSiteName("scheduleMethodQIP");//获取工厂名
		for(int i=0;i<getSiteName.size();i++)
		{
			String siteName=getSiteName.get(i).get("SITENAME").toString();
		
 
		String dbSql  =  "  SELECT DISTINCT "+
         " A.MATERIALSPECNAME, "+
         " A.SITENAME, "+
         " A.DESC_CN AS DESCRIPTION,"+
         " B.ERPLOCATION,"+
         " B.LOCATIONNAME,"+
         " B.OQARESULTSTATE,"+
     " SUM (materialquantity)"+
            " AS MATERIALQUANTITY,"+
         " A.MATERIALUNIT,"+
         " TO_CHAR (B.MAKEDATE, 'yyyy-mm-dd hh:mm:ss') AS MAKEDATE,"+
         " TO_CHAR (B.RECEIVETIME, 'yyyy-mm-dd hh:mm:ss') AS RECEIVETIME,"+
         " TO_CHAR (ADD_MONTHS (B.MAKEDATE, A.EXPIRATIONDAY),"+
                  " 'yyyy-mm-dd hh:mm:ss')"+
            " AS EXPIRATIONDAY,"+
         " ROUND (ADD_MONTHS (MAKEDATE, EXPIRATIONDAY) - SYSDATE) AS DAYS"+
    " FROM MATERIALSPEC A, MATERIALPACKING B"+
   " WHERE     A.EXPIRATIONDATEFLAG = 'Y'"+
         " AND EXPIRATIONDAY > 0"+
         " AND A.SITENAME = B.SITENAME"+
         " AND A.SITENAME = ?"+
         " AND B.STOCKSTATE = 'Stocked'"+
         " AND B.PACKINGSTATE = 'Released'"+
         " AND A.MATERIALSPECNAME = B.MATERIALSPECNAME"+
         " AND B.MATERIALQUANTITY>0"+
         " AND ROUND (ADD_MONTHS (MAKEDATE, EXPIRATIONDAY) - SYSDATE) <= 30"+
         " GROUP BY A.MATERIALSPECNAME,A.SITENAME,B.ERPLOCATION,DESC_CN,MATERIALUNIT,B.LOCATIONNAME,OQARESULTSTATE,B.MAKEDATE,RECEIVETIME,EXPIRATIONDAY"+
         " ORDER BY DAYS DESC";
		
		List<Map<String, Object>> sqlResult = IDMFrameServiceProxy.getSqlTemplate().queryForList(dbSql,new Object[]{siteName});

		if(sqlResult != null && !sqlResult.isEmpty()){
			Date sendDate = new Date(); // mail send  time
			String mailSendDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(sendDate);
			createDir("/attachment");			
			String fileUrl ="/attachment/" + new SimpleDateFormat("yyyyMMddHHmmsss").format(sendDate) + ".xls"; //E:/students.xls

			//生成excel
			FileOutputStream fout;
			try {
				fout = new FileOutputStream(fileUrl);
				createQualityInspectionPendingExcel(sqlResult,fout);
			} catch (WriteException e) {e.printStackTrace();
			} catch (IOException e) {e.printStackTrace();}
			MailSenderInfo mailInfo = new MailSenderInfo();   
			mailInfo.setMailServerHost(MWMSMail.getHost());   
			mailInfo.setMailServerPort(MWMSMail.getPort());   
			mailInfo.setValidate(true);   
			mailInfo.setUserName(MWMSMail.getUsername());   
			mailInfo.setPassword(MWMSMail.getPassword());
			mailInfo.setFromAddress(MWMSMail.getFromAddress());
			
			MailSendHistory mailSendHistory = new MailSendHistory(); 
			
			mailSendHistory.setSendTime(mailSendDate);
			mailSendHistory.setInterfaceType("scheduleMethodQIP");
			
		 	String[] receiver = getRecMailUsers("scheduleMethodQIP",siteName);
			if(receiver != null){
				mailInfo.setToAddress(receiver);
				mailInfo.setAttachFileNames(new String[]{fileUrl});
				mailInfo.setSubject("品质待检");  
				
				SimpleMailSender sms = new SimpleMailSender();

				mailInfo.setContent(getQualityInspectionPending(sqlResult).toString());   
				if(mailInfo.getContent() == null){
					mailSendHistory.setErrorType("邮件内容是空的");
					mailSendHistory.setSendState("false");
				}else{
					try {
						sms.sendHtmlMail(mailInfo);
						mailSendHistory.setSendState("true");
						//品质待检的db操作 每天定时扫描的db操作
						updateSendTimesAndDate(sqlResult,mailSendDate);
					} catch (Exception e) {
						if(e.getClass() == SendFailedException.class){
							mailSendHistory.setErrorType("收件人地址错误");
							mailSendHistory.setSendState("false");
						}else if(e.getClass() == AuthenticationFailedException.class){
							mailSendHistory.setErrorType("发件人用户名密码错误");
							mailSendHistory.setSendState("false");
						}else if (e.getClass() == MessagingException.class) {
							mailSendHistory.setErrorType("连接发件服务器失败");
							mailSendHistory.setSendState("false");
						}
					}
				}
			}else{
				mailSendHistory.setErrorType("收件人地址列表是空的");
				mailSendHistory.setSendState("false");
			}
			
			updateMailSendHistory(mailSendHistory);
		}
		}
	}
	
	
	
	/**
	 * 寿命管理定时任务接口
	 */
	public void SendLifeManagementMail(){
		List<Map<String, Object>> getSiteName = getRecMailUsersSiteName("SendLifeManagementMail");//获取工厂名
		for(int i=0;i<getSiteName.size();i++)
		{
			String siteName=getSiteName.get(i).get("SITENAME").toString();
			
		String dbSql  =  " SELECT DISTINCT  A.MATERIALSPECNAME,A.SITENAME,A.DESC_CN AS DESCRIPTION, B.ERPLOCATION,B.LOCATIONNAME, B.OQARESULTSTATE,SUM (materialquantity)AS MATERIALQUANTITY,"
				+" A.MATERIALUNIT, TO_CHAR (B.MAKEDATE, 'yyyy-mm-dd ') AS MAKEDATE,TO_CHAR (B.RECEIVETIME, 'yyyy-mm-dd ') AS RECEIVETIME,TO_CHAR (ADD_MONTHS (B.MAKEDATE, A.EXPIRATIONDAY),'yyyy-mm-dd ') AS EXPIRATIONDAY,"
				+ " ROUND (ADD_MONTHS (MAKEDATE, EXPIRATIONDAY) - SYSDATE) AS DAYS FROM MATERIALSPEC A, MATERIALPACKING B"+
		        " WHERE     A.EXPIRATIONDATEFLAG = 'Y' AND EXPIRATIONDAY > 0 AND A.SITENAME = B.SITENAME "+
		         " AND A.SITENAME = ? "+
		         " AND A.MATERIALSPECNAME = B.MATERIALSPECNAME"+
		         " AND B.STOCKSTATE = 'Stocked'"+
		         " AND B.PACKINGSTATE = 'Released'"+ 
		         " AND B.MATERIALQUANTITY>0 AND ROUND (ADD_MONTHS (MAKEDATE, EXPIRATIONDAY) - SYSDATE) <= 30"+
		         " GROUP BY A.MATERIALSPECNAME,A.SITENAME,B.ERPLOCATION,DESC_CN,MATERIALUNIT,B.LOCATIONNAME,OQARESULTSTATE,B.MAKEDATE,RECEIVETIME,EXPIRATIONDAY  ORDER BY DAYS DESC ";
		List<Map<String, Object>> sqlResult = IDMFrameServiceProxy.getSqlTemplate().queryForList(dbSql,new Object[]{siteName});;

		if(sqlResult != null && !sqlResult.isEmpty()){
			Date sendDate = new Date(); // mail send  time
			String mailSendDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(sendDate);
			
			createDir("/attachment");			
			String fileUrl ="/attachment/lm" + new SimpleDateFormat("yyyyMMddHHmmsss").format(sendDate) + ".xls"; //E:/students.xls

			//生成excel
			FileOutputStream fout;
			try {
				fout = new FileOutputStream(fileUrl);
				createLifeManagementExcel(sqlResult,fout);
			} catch (WriteException e) {e.printStackTrace();
			} catch (IOException e) {e.printStackTrace();}


			MailSenderInfo mailInfo = new MailSenderInfo();   
			mailInfo.setMailServerHost(MWMSMail.getHost());   
			mailInfo.setMailServerPort(MWMSMail.getPort());   
			mailInfo.setValidate(true);   
			mailInfo.setUserName(MWMSMail.getUsername());   
			mailInfo.setPassword(MWMSMail.getPassword());
			mailInfo.setFromAddress(MWMSMail.getFromAddress());
			mailInfo.setAttachFileNames(new String[]{fileUrl});
			mailInfo.setSubject("寿命到期提醒");  
			
			MailSendHistory mailSendHistory = new MailSendHistory();  

			mailSendHistory.setSendTime(mailSendDate);
			mailSendHistory.setInterfaceType("SendLifeManagementMail");
			
			String[] receiver = getRecMailUsers("SendLifeManagementMail",siteName);
			if(receiver != null){
				mailInfo.setToAddress(receiver);
				SimpleMailSender sms = new SimpleMailSender();
				mailInfo.setContent(getLifeManageMentHtml(sqlResult).toString());   

				if(mailInfo.getContent() == null){
					mailSendHistory.setErrorType("邮件内容是空的");
					mailSendHistory.setSendState("false");
				}else{
					try {
						sms.sendHtmlMail(mailInfo);
						mailSendHistory.setSendState("true");
					} catch (Exception e) {
						if(e.getClass() == SendFailedException.class){
							mailSendHistory.setErrorType("收件人地址错误");
							mailSendHistory.setSendState("false");
						}else if(e.getClass() == AuthenticationFailedException.class){
							mailSendHistory.setErrorType("发件人用户名密码错误");
							mailSendHistory.setSendState("false");
						}else if (e.getClass() == MessagingException.class) {
							mailSendHistory.setErrorType("连接发件服务器失败");
							mailSendHistory.setSendState("false");
						}else if(e.getClass() == AddressException.class){
							mailSendHistory.setErrorType("收件人地址非法");
							mailSendHistory.setSendState("false");
						}
					}
				}
			}else{
				mailSendHistory.setErrorType("收件人地址列表是空的");
				mailSendHistory.setSendState("false");
			}
			//寿命管理的db操作
			updateMailSendHistory(mailSendHistory);
		}
		}
	}
	/* 
	 * 长期库存超过6个月发送提醒
	 * */
	public void SendLongTimeStorageMail() {
		List<Map<String, Object>> getSiteName = getRecMailUsersSiteName("SendLongTimeStorageMail");//获取工厂名
		String dbSql="";		
		String siteName="";
		for(int i=0;i<getSiteName.size();i++)
		{
		  siteName=getSiteName.get(i).get("SITENAME").toString();
	    // 
				dbSql = "SELECT A.SITENAME,A.ERPFACTORY,A.MATERIALSPECNAME,B.MATERIALUNIT,B.DESC_CN AS DESCRIPTION,A.ERPLOCATION,"
						+
						// " SUM( CASE WHEN (180<(SYSDATE-A.RECEIVETIME) )THEN MATERIALQUANTITY ELSE 0 END) AS TIMESIX   "+
						" SUM(MATERIALQUANTITY ) AS TIMESIX "
						+ " FROM  MATERIALPACKING A , MATERIALSPEC B WHERE A.MATERIALSPECNAME=B.MATERIALSPECNAME "
						+ " AND A.SITENAME=B.SITENAME "
						+ " AND A.STOCKSTATE = 'Stocked'"
                        +" AND A.PACKINGSTATE = 'Released' "
						+ " AND A.SITENAME = ?"
						+ " AND (180<SYSDATE-A.RECEIVETIME)"
						+ " GROUP BY A.SITENAME,A.ERPFACTORY,A.MATERIALSPECNAME, B.MATERIALUNIT,B.DESC_CN,B.DESCRIPTION,A.ERPLOCATION"
						+ " ORDER BY A.ERPFACTORY,A.MATERIALSPECNAME,A.ERPLOCATION";
			
		List<Map<String, Object>> sqlResult = IDMFrameServiceProxy.getSqlTemplate().queryForList(dbSql,new Object[]{siteName});;

		if(sqlResult != null && !sqlResult.isEmpty()){
			Date sendDate = new Date(); // mail send  time
			String mailSendDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(sendDate);
			
//			测试服务器
//			createDir("/usr01/wmstst/attachment");
//			String fileUrl ="/usr01/wmstst/attachment/" + new SimpleDateFormat("yyyyMMddHHmmsss").format(sendDate) + ".xls";
			
//			量产服务器
//			createDir("/wmsap/wmsadm/attachment");
			String fileUrl ="/wmsap/wmsadm/attachment/" + new SimpleDateFormat("yyyyMMddHHmmsss").format(sendDate) + ".xls";

			//生成excel
			FileOutputStream fout;
			try {
				fout = new FileOutputStream(fileUrl);
				createLongTimeStorageExcel(sqlResult,fout);
			} catch (WriteException e) {e.printStackTrace();
			} catch (IOException e) {e.printStackTrace();}
			
			
			MWMSMail mail = this.initMailSender();
			
			MailSenderInfo mailInfo = new MailSenderInfo();   
			mailInfo.setMailServerHost(mail.getHost());   
			mailInfo.setMailServerPort(mail.getPort());   
			mailInfo.setValidate(true);   
			mailInfo.setUserName(mail.getUsername());   
			mailInfo.setPassword(mail.getPassword());
			mailInfo.setFromAddress(mail.getFromAddress());
			
			MailSendHistory mailSendHistory = new MailSendHistory(); 
			
			mailSendHistory.setSendTime(mailSendDate);
			mailSendHistory.setInterfaceType("SendLongTimeStorageMail");
			
			String[] receiver = getRecMailUsers("SendLongTimeStorageMail",siteName);
			if(receiver != null){
				mailInfo.setToAddress(receiver);
				mailInfo.setAttachFileNames(new String[]{fileUrl});
				mailInfo.setSubject("长期库存超过6个月提醒");  
				
				SimpleMailSender sms = new SimpleMailSender();

				mailInfo.setContent(getLongTimeStoragehtml(sqlResult).toString());   
				if(mailInfo.getContent() == null){
					mailSendHistory.setErrorType("邮件内容是空的");
					mailSendHistory.setSendState("false");
				}else{
					try {
						sms.sendHtmlMail(mailInfo);
						mailSendHistory.setSendState("true");
						//db操作 每天定时扫描的db操作
						updateSendTimesAndDate(sqlResult,mailSendDate);
					} catch (Exception e) {
						if(e.getClass() == SendFailedException.class){
							mailSendHistory.setErrorType("收件人地址错误");
							mailSendHistory.setSendState("false");
						}else if(e.getClass() == AuthenticationFailedException.class){
							mailSendHistory.setErrorType("发件人用户名密码错误");
							mailSendHistory.setSendState("false");
						}else if (e.getClass() == MessagingException.class) {
							mailSendHistory.setErrorType("连接发件服务器失败");
							mailSendHistory.setSendState("false");
						}
					}
				}
			}else{
				mailSendHistory.setErrorType("收件人地址列表是空的");
				mailSendHistory.setSendState("false");
			}
			
			updateMailSendHistory(mailSendHistory);
		}
		}
	}
	/*
	 * 长期库存超过6个月提醒HTML代码
	 */
  public StringBuffer getLongTimeStoragehtml(List<Map<String, Object>> sqlResult)
	{
		StringBuffer mailText = new StringBuffer();

		mailText.append("<table width='100%'>");
		mailText.append("<tr height='30px'>");
		mailText.append("<td align='left' width='30%'>您好！<br/><br/></td>");
		mailText.append("</tr>");
		mailText.append("<tr>");
		mailText.append("<td align='left' width='30%'>目前有以下材料长期库存超过6个月，请及时检查。谢谢<br/><br/></td>");
		mailText.append("</tr>");
		mailText.append("<tr><td>");
		mailText.append("<table border='1' width='100%' style='text-align:center;border-collapse: collapse;border: none;border: solid #000 1px;'>");
		mailText.append("<tr>");
		mailText.append("<td width='10%'>序号</td><td width='10%'>工厂</td><td width='10%'>ERP工厂</td><td width='10%'>ERP库位</td><td width='20%'>物料号</td>");
		mailText.append("<td width='10%'>单位</td><td width='20%'>物料描述</td><td width='10%'>库位</td>");
		mailText.append("<td width='10%'>超过6个月</td>");
		mailText.append("</tr>");

		if(sqlResult != null && !sqlResult.isEmpty())
			for(int i = 0 ; i<sqlResult.size(); i++){
				Map<String, Object> tmp = sqlResult.get(i);

				mailText.append("<tr><td>" + (i+1) + "</td>");
				mailText.append("<td>" + (tmp.get("SITENAME")!=null&&!"".equals(tmp.get("SITENAME"))?tmp.get("SITENAME"):" ") + "</td>");
				mailText.append("<td>" + (tmp.get("ERPFACTORY")!=null&&!"".equals(tmp.get("ERPFACTORY"))?tmp.get("ERPFACTORY"):" ") + "</td>");
				mailText.append("<td>" + (tmp.get("ERPLOCATION")!=null&&!"".equals(tmp.get("ERPLOCATION"))?tmp.get("ERPLOCATION"):" ") + "</td>");
				mailText.append("<td>" + (tmp.get("MATERIALSPECNAME")!=null&&!"".equals(tmp.get("MATERIALSPECNAME"))?tmp.get("MATERIALSPECNAME"):" ") + "</td>");
				mailText.append("<td>" + (tmp.get("MATERIALUNIT") != null&&!"".equals(tmp.get("MATERIALUNIT"))?tmp.get("MATERIALUNIT"):" ") + "</td>");
				mailText.append("<td>" + (tmp.get("DESCRIPTION") != null&&!"".equals(tmp.get("DESCRIPTION"))?tmp.get("DESCRIPTION"):" ") + "</td>");
				mailText.append("<td>" + (tmp.get("ERPLOCATION") != null&&!"".equals(tmp.get("ERPLOCATION"))?tmp.get("ERPLOCATION"):" ") + "</td>");
				mailText.append("<td>" + (tmp.get("TIMESIX") != null&&!"".equals(tmp.get("TIMESIX"))?tmp.get("TIMESIX"):" ") + "</td>");

			}
		mailText.append("</table>");
		mailText.append("</td>");
		mailText.append("</tr>");
		mailText.append("<tr><td></td></tr>");
		mailText.append("</table>");

		return mailText;
	}

  /*
   * 安全库存信息
   * */
	public void SendSafetyStockInfoMail() {
		List<Map<String, Object>> getSiteName = getRecMailUsersSiteName("SendSafetyStockInfoMail");// 获取工厂名
		String dbSql = "";
		String siteName = "";
		for (int i = 0; i < getSiteName.size(); i++) {
			siteName = getSiteName.get(i).get("SITENAME").toString();
			dbSql = " SELECT C.*,D.MAXSAFETYSTOCK FROM(SELECT A.ERPFACTORY, A.MATERIALSPECNAME,B.DESC_CN,SUM(A.MATERIALQUANTITY)MATERIALQUANTITY ,B.MATERIALUNIT "
					+ " FROM  MATERIALPACKING A , MATERIALSPEC B WHERE A.MATERIALSPECNAME=B.MATERIALSPECNAME  "
					+ " AND A.SITENAME=B.SITENAME "
					+ " AND A.STOCKSTATE = 'Stocked'"
					+ " AND A.PACKINGSTATE = 'Released' "
					+ " AND A.SITENAME = ? "
					+"  AND A.ERPFACTORY='3060' "
					+ " GROUP BY A.ERPFACTORY,A.MATERIALSPECNAME,B.DESC_CN,B.MATERIALUNIT ORDER BY A.MATERIALSPECNAME)C,MATERIALSPEC D  "
					+ " WHERE C.MATERIALSPECNAME=D.MATERIALSPECNAME AND C.MATERIALQUANTITY<D.MAXSAFETYSTOCK ";

			List<Map<String, Object>> sqlResult = IDMFrameServiceProxy
					.getSqlTemplate()
					.queryForList(dbSql, new Object[] { siteName });
			;

			if (sqlResult != null && !sqlResult.isEmpty()) {
				Date sendDate = new Date(); // mail send time
				String mailSendDate = new SimpleDateFormat(
						"yyyy-MM-dd HH:mm:ss").format(sendDate);
				createDir("/attachment");
				String fileUrl = "/attachment/"
						+ new SimpleDateFormat("yyyyMMddHHmmsss")
								.format(sendDate) + ".xls"; // E:/students.xls

				// 生成excel
				FileOutputStream fout;
				try {
					fout = new FileOutputStream(fileUrl);
					createLongTimeStorageExcel(sqlResult, fout);
				} catch (WriteException e) {
					e.printStackTrace();
				} catch (IOException e) {
					e.printStackTrace();
				}
				MailSenderInfo mailInfo = new MailSenderInfo();
				mailInfo.setMailServerHost(MWMSMail.getHost());
				mailInfo.setMailServerPort(MWMSMail.getPort());
				mailInfo.setValidate(true);
				mailInfo.setUserName(MWMSMail.getUsername());
				mailInfo.setPassword(MWMSMail.getPassword());
				mailInfo.setFromAddress(MWMSMail.getFromAddress());

				MailSendHistory mailSendHistory = new MailSendHistory();

				mailSendHistory.setSendTime(mailSendDate);
				mailSendHistory.setInterfaceType("SendSafetyStockInfoMail");

				String[] receiver = getRecMailUsers("SendSafetyStockInfoMail",
						siteName);
				if (receiver != null) {
					mailInfo.setToAddress(receiver);
					mailInfo.setAttachFileNames(new String[] { fileUrl });
					mailInfo.setSubject("安全库存提醒");

					SimpleMailSender sms = new SimpleMailSender();

					mailInfo.setContent(SendSafetyStockInfoToHTML(sqlResult)
							.toString());
					if (mailInfo.getContent() == null) {
						mailSendHistory.setErrorType("邮件内容是空的");
						mailSendHistory.setSendState("false");
					} else {
						try {
							sms.sendHtmlMail(mailInfo);
							mailSendHistory.setSendState("true");
							// db操作 每天定时扫描的db操作
							updateSendTimesAndDate(sqlResult, mailSendDate);
						} catch (Exception e) {
							if (e.getClass() == SendFailedException.class) {
								mailSendHistory.setErrorType("收件人地址错误");
								mailSendHistory.setSendState("false");
							} else if (e.getClass() == AuthenticationFailedException.class) {
								mailSendHistory.setErrorType("发件人用户名密码错误");
								mailSendHistory.setSendState("false");
							} else if (e.getClass() == MessagingException.class) {
								mailSendHistory.setErrorType("连接发件服务器失败");
								mailSendHistory.setSendState("false");
							}
						}
					}
				} else {
					mailSendHistory.setErrorType("收件人地址列表是空的");
					mailSendHistory.setSendState("false");
				}

				updateMailSendHistory(mailSendHistory);
			}
		}
	}

	/*
	 * 安全库存提醒HTML代码
	 */
	public StringBuffer SendSafetyStockInfoToHTML(
			List<Map<String, Object>> sqlResult) {
		StringBuffer mailText = new StringBuffer();

		mailText.append("<table width='100%'>");
		mailText.append("<tr height='30px'>");
		mailText.append("<td align='left' width='30%'>您好！<br/><br/></td>");
		mailText.append("</tr>");
		mailText.append("<tr>");
		mailText.append("<td align='left' width='30%'>以下物料实际库存小于安全库存，请关注！<br/><br/></td>");
		mailText.append("</tr>");
		mailText.append("<tr><td>");
		mailText.append("<table border='1' width='100%' style='text-align:center;border-collapse: collapse;border: none;border: solid #000 1px;'>");
		mailText.append("<tr>");
		mailText.append("<td width='5%'>序号</td><td width='10%'>ERP工厂</td><td width='15%'>物料号</td>");
		mailText.append("<td width='35%'>物料描述</td><td width='15%'>在库数量</td><td width='15%'>安全库存</td>");
		mailText.append("<td width='5%'>单位</td>");
		mailText.append("</tr>");

		if (sqlResult != null && !sqlResult.isEmpty())
			for (int i = 0; i < sqlResult.size(); i++) {
				Map<String, Object> tmp = sqlResult.get(i);

				mailText.append("<tr><td>" + (i + 1) + "</td>");
				mailText.append("<td>"
						+ (tmp.get("ERPFACTORY") != null
								&& !"".equals(tmp.get("ERPFACTORY")) ? tmp
								.get("ERPFACTORY") : " ") + "</td>");
				mailText.append("<td>"
						+ (tmp.get("MATERIALSPECNAME") != null
								&& !"".equals(tmp.get("MATERIALSPECNAME")) ? tmp
								.get("MATERIALSPECNAME") : " ") + "</td>");
				mailText.append("<td>"
						+ (tmp.get("DESC_CN") != null
								&& !"".equals(tmp.get("DESC_CN")) ? tmp
								.get("DESC_CN") : " ") + "</td>");
				mailText.append("<td>"
						+ (tmp.get("MATERIALQUANTITY") != null
								&& !"".equals(tmp.get("MATERIALQUANTITY")) ? tmp
								.get("MATERIALQUANTITY") : " ") + "</td>");
				mailText.append("<td>"
						+ (tmp.get("MAXSAFETYSTOCK") != null
								&& !"".equals(tmp.get("MAXSAFETYSTOCK")) ? tmp
								.get("MAXSAFETYSTOCK") : " ") + "</td>");
				mailText.append("<td>"
						+ (tmp.get("MATERIALUNIT") != null
								&& !"".equals(tmp.get("MATERIALUNIT")) ? tmp
								.get("MATERIALUNIT") : " ") + "</td>");

			}
		mailText.append("</table>");
		mailText.append("</td>");
		mailText.append("</tr>");
		mailText.append("<tr><td></td></tr>");
		mailText.append("</table>");

		return mailText;
	}
  
	/* 
	 * 过期库存自动hold功能
	 * */
public void HoldAbnormalBox() {
		
		String dbSql="";		
		String siteName="";
		String erpFactory="1100";
		List<Map<String, Object>> getSiteName = getRecMailUsersSiteName("SendLongTimeStorageMail");//获取工厂名
		siteName=getSiteName.get(0).get("SITENAME").toString();
		if(siteName.equalsIgnoreCase("B3")){
			
			List<Map<String, Object>> sqlResult=null;	
			
			 dbSql  =  "SELECT MATERIALSPECNAME,MATERIALPACKINGNAME,receivetime,MAKEDATE,LOCATIONNAME FROM MATERIALPACKING A WHERE A.STOCKSTATE = 'Stocked' "+
			            "AND A.PACKINGSTATE = 'Released'  AND  A.HOLDSTATE='N' AND A.MATERIALTYPE <> 'ERSA' "
			            + " AND ((A.EXPIRINGDATE -SYSDATE)<0 OR (SYSDATE-a.receivetime)>180)"
			            +" AND A.ERPFACTORY= '" +erpFactory +"'"
					    + " AND A.SITENAME= '"+siteName+"'"
					    + " order by EXPIRINGDATE asc";
						 
					sqlResult = IDMFrameServiceProxy.getSqlTemplate().queryForList(dbSql);
						

					if(sqlResult != null && !sqlResult.isEmpty()){
						//将过期库存Hold住	
						String dbSql1="";		
						dbSql1=" UPDATE  MATERIALPACKING SET HOLDSTATE ='Y',REASONCODE='PastDue',LASTEVENTCOMMENT='系统自动Holding物料',LASTEVENTTIME=sysdate WHERE  MATERIALPACKINGNAME IN( " +
						"SELECT MATERIALPACKINGNAME " +
						"FROM MATERIALPACKING B " +
						"WHERE B.STOCKSTATE = 'Stocked' " +
						"and B.PACKINGSTATE = 'Released' " +
						"AND B.ERPFACTORY= '" +erpFactory+"'"+
						"AND B.SITENAME = '"+ siteName +"'"+
						"AND B.HOLDSTATE='N' AND A.MATERIALTYPE <> 'ERSA' " +
						" AND ((B.EXPIRINGDATE -SYSDATE)<0 OR (SYSDATE-B.receivetime)>180) ) ";
						IDMFrameServiceProxy.getSqlTemplate().update(dbSql1);
				}		
			
		}
		
}
  
  
  /**
 * 长期库存生成excel
 * */
public void createLongTimeStorageExcel(List<Map<String, Object>> sqlResult,OutputStream os) throws WriteException,IOException {
	WritableWorkbook workbook = Workbook.createWorkbook(os);
	WritableSheet sheet = workbook.createSheet("First Sheet", 0);

	sheet.mergeCells(0, 0, 9, 0);//添加合并单元格，第一个参数是起始列，第二个参数是起始行，第三个参数是终止列，第四个参数是终止行
	WritableFont bold = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD);//设置字体种类和黑体显示,字体为Arial,字号大小为10,采用黑体显示
	WritableCellFormat titleFormate = new WritableCellFormat(bold);//生成一个单元格样式控制对象
	titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.TOP);//单元格的内容垂直方向居中
	titleFormate.setWrap(true);
	sheet.addCell(new Label(0,0,"您好！\n\n目前有以下材料长期库存超过6个月，请及时检查。谢谢\n",titleFormate));

	sheet.setRowView(0, 1000, false);

	sheet.addCell(new Label(0,1,"序号"));
	sheet.addCell(new Label(1,1,"工厂"));
	sheet.addCell(new Label(2,1,"ERP工厂"));
	sheet.addCell(new Label(3,1,"ERP库位"));
	sheet.addCell(new Label(4,1,"物料号"));
	sheet.addCell(new Label(5,1,"单位"));
	sheet.addCell(new Label(6,1,"物料描述"));
	sheet.addCell(new Label(7,1,"库位"));
	sheet.addCell(new Label(8,1,"超过6个月"));
	
	if(sqlResult != null && !sqlResult.isEmpty())
		for(int i = 0 ; i<sqlResult.size(); i++){
			Map<String, Object> tmp = sqlResult.get(i);

			sheet.addCell(new Label(0,2+i,(i+1)+""));
			sheet.addCell(new Label(1,2+i,(tmp.get("SITENAME")!=null?tmp.get("SITENAME").toString():"")));
			sheet.addCell(new Label(2,2+i,(tmp.get("ERPFACTORY")!=null?tmp.get("ERPFACTORY").toString():"")));
			sheet.addCell(new Label(3,2+i,(tmp.get("ERPLOCATION")!=null?tmp.get("ERPLOCATION").toString():"")));
			sheet.addCell(new Label(4,2+i,(tmp.get("MATERIALSPECNAME") != null?tmp.get("MATERIALSPECNAME").toString():"")));
			sheet.addCell(new Label(5,2+i,(tmp.get("MATERIALUNIT")!= null?tmp.get("MATERIALUNIT").toString():"")));
			sheet.addCell(new Label(6,2+i,(tmp.get("DESCRIPTION")!= null?tmp.get("DESCRIPTION").toString():"")));
			sheet.addCell(new Label(7,2+i,(tmp.get("ERPLOCATION")!= null?tmp.get("ERPLOCATION").toString():"")));
			sheet.addCell(new Label(8,2+i,(tmp.get("TIMESIX")!= null?tmp.get("TIMESIX").toString():"")));

		}
	//把创建的内容写入到输出流中，并关闭输出流
	workbook.write();
	workbook.close();
	os.close();

}
	/**
	 * 寿命管理html代码
	 */
	
	public StringBuffer getLifeManageMentHtml(List<Map<String, Object>> sqlResult)
	{
		StringBuffer mailText = new StringBuffer();

		mailText.append("<table width='100%'>");
		mailText.append("<tr height='30px'>");
		mailText.append("<td align='left' width='30%'>您好！<br/><br/></td>");
		mailText.append("</tr>");
		mailText.append("<tr>");
		mailText.append("<td align='left' width='30%'>目前有以下材料寿命即将到期，请及时检查。谢谢<br/><br/></td>");
		mailText.append("</tr>");
		mailText.append("<tr><td>");
		mailText.append("<table border='1' width='100%' style='text-align:center;border-collapse: collapse;border: none;border: solid #000 1px;'>");
		mailText.append("<tr>");
		mailText.append("<td width='6%'>序号</td><td width='4%'>工厂</td><td width='10%'>物料号</td><td width='10%'>物料描述</td>");
		mailText.append("<td width='10%'>ERP工厂</td><td width='10%'>ERP库位</td><td width='10%'>货位地</td><td width='6%'>库存状态</td>");
		mailText.append("<td width='4%'>数量</td><td width='4%'>单位</td><td width='10%'>生产日期</td>");
		mailText.append("<td width='10%'>入库时间</td><td width='10%'>寿命到期日</td><td width='6%'>到期剩余</td>");
		mailText.append("</tr>");

		if(sqlResult != null && !sqlResult.isEmpty())
			for(int i = 0 ; i<sqlResult.size(); i++){
				Map<String, Object> tmp = sqlResult.get(i);
				if((10>=Integer.parseInt(tmp.get("DAYS").toString()))&& (Integer.parseInt(tmp.get("DAYS").toString()) >= 0))
				{
					mailText.append("<td style='background-color:orange;'>" + (i+1) + "</td>");
					mailText.append("<td style='background-color:orange;'>" + (tmp.get("SITENAME")!=null&&!"".equals(tmp.get("SITENAME"))?tmp.get("SITENAME"):" ") + "</td>");
					mailText.append("<td style='background-color:orange;'>" + (tmp.get("MATERIALSPECNAME")!=null&&!"".equals(tmp.get("MATERIALSPECNAME"))?tmp.get("MATERIALSPECNAME"):" ") + "</td>");
					mailText.append("<td style='background-color:orange;'>" + (tmp.get("DESCRIPTION") != null&&!"".equals(tmp.get("DESCRIPTION"))?tmp.get("DESCRIPTION"):" ") + "</td>");
					mailText.append("<td style='background-color:orange;'>"+ (tmp.get("ERPFACTORY") != null&&!"".equals(tmp.get("ERPFACTORY"))?tmp.get("ERPFACTORY"):" ") + "</td>");
					mailText.append("<td style='background-color:orange;'>"+ (tmp.get("ERPLOCATION") != null&&!"".equals(tmp.get("ERPLOCATION"))?tmp.get("ERPLOCATION"):" ") + "</td>");
					mailText.append("<td style='background-color:orange;'>" + (tmp.get("LOCATIONNAME") != null&&!"".equals(tmp.get("LOCATIONNAME"))?tmp.get("LOCATIONNAME"):" ") + "</td>");
					mailText.append("<td style='background-color:orange;'>" + (tmp.get("OQARESULTSTATE") != null&&!"".equals(tmp.get("OQARESULTSTATE"))?tmp.get("OQARESULTSTATE"):" ") + "</td>");
					mailText.append("<td style='background-color:orange;'>" + (tmp.get("MATERIALQUANTITY") != null&&!"".equals(tmp.get("MATERIALQUANTITY"))?tmp.get("MATERIALQUANTITY"):" ") + "</td>");
					mailText.append("<td style='background-color:orange;'>" + (tmp.get("MATERIALUNIT") != null&&!"".equals(tmp.get("MATERIALUNIT"))?tmp.get("MATERIALUNIT"):" ") + "</td>");
					mailText.append("<td style='background-color:orange;'>"+ (tmp.get("MAKEDATE") != null&&!"".equals(tmp.get("MAKEDATE"))?tmp.get("MAKEDATE"):" ") + "</td>");
					mailText.append("<td style='background-color:orange;'>" + (tmp.get("RECEIVETIME") != null&&!"".equals(tmp.get("RECEIVETIME"))?tmp.get("RECEIVETIME"):" ") + "</td>");
					mailText.append("<td style='background-color:orange;'>" + (tmp.get("EXPIRATIONDAY") != null&&!"".equals(tmp.get("EXPIRATIONDAY"))?tmp.get("EXPIRATIONDAY"):" ") + "</td>");
					mailText.append("<td style='background-color:orange;'>" + (tmp.get("DAYS") != null&&!"".equals(tmp.get("DAYS"))?tmp.get("DAYS"):" ") + "</td></tr>");

				}else if((20>=Integer.parseInt(tmp.get("DAYS").toString()))&& (Integer.parseInt(tmp.get("DAYS").toString()) >= 11))
				{
					mailText.append("<td style='background-color:orange;'>" + (i+1) + "</td>");
					mailText.append("<td style='background-color:orange;'>" + (tmp.get("SITENAME")!=null&&!"".equals(tmp.get("SITENAME"))?tmp.get("SITENAME"):" ") + "</td>");
					mailText.append("<td style='background-color:orange;'>" + (tmp.get("MATERIALSPECNAME")!=null&&!"".equals(tmp.get("MATERIALSPECNAME"))?tmp.get("MATERIALSPECNAME"):" ") + "</td>");
					mailText.append("<td style='background-color:orange;'>" + (tmp.get("DESCRIPTION") != null&&!"".equals(tmp.get("DESCRIPTION"))?tmp.get("DESCRIPTION"):" ") + "</td>");
					mailText.append("<td style='background-color:orange;'>"+ (tmp.get("ERPFACTORY") != null&&!"".equals(tmp.get("ERPFACTORY"))?tmp.get("ERPFACTORY"):" ") + "</td>");
					mailText.append("<td style='background-color:orange;'>"+ (tmp.get("ERPLOCATION") != null&&!"".equals(tmp.get("ERPLOCATION"))?tmp.get("ERPLOCATION"):" ") + "</td>");
					mailText.append("<td style='background-color:orange;'>" + (tmp.get("LOCATIONNAME") != null&&!"".equals(tmp.get("LOCATIONNAME"))?tmp.get("LOCATIONNAME"):" ") + "</td>");
					mailText.append("<td style='background-color:orange;'>" + (tmp.get("OQARESULTSTATE") != null&&!"".equals(tmp.get("OQARESULTSTATE"))?tmp.get("OQARESULTSTATE"):" ") + "</td>");
					mailText.append("<td style='background-color:orange;'>" + (tmp.get("MATERIALQUANTITY") != null&&!"".equals(tmp.get("MATERIALQUANTITY"))?tmp.get("MATERIALQUANTITY"):" ") + "</td>");
					mailText.append("<td style='background-color:orange;'>" + (tmp.get("MATERIALUNIT") != null&&!"".equals(tmp.get("MATERIALUNIT"))?tmp.get("MATERIALUNIT"):" ") + "</td>");
					mailText.append("<td style='background-color:orange;'>"+ (tmp.get("MAKEDATE") != null&&!"".equals(tmp.get("MAKEDATE"))?tmp.get("MAKEDATE"):" ") + "</td>");
					mailText.append("<td style='background-color:orange;'>" + (tmp.get("RECEIVETIME") != null&&!"".equals(tmp.get("RECEIVETIME"))?tmp.get("RECEIVETIME"):" ") + "</td>");
					mailText.append("<td style='background-color:orange;'>" + (tmp.get("EXPIRATIONDAY") != null&&!"".equals(tmp.get("EXPIRATIONDAY"))?tmp.get("EXPIRATIONDAY"):" ") + "</td>");
					mailText.append("<td style='background-color:orange;'>" + (tmp.get("DAYS") != null&&!"".equals(tmp.get("DAYS"))?tmp.get("DAYS"):" ") + "</td></tr>");
				}else if((30>=Integer.parseInt(tmp.get("DAYS").toString()))&& (Integer.parseInt(tmp.get("DAYS").toString()) >= 21)){
					mailText.append("<td style='background-color:yellow;'>" + (i+1) + "</td>");
					mailText.append("<td style='background-color:yellow;'>" + (tmp.get("SITENAME")!=null&&!"".equals(tmp.get("SITENAME"))?tmp.get("SITENAME"):" ") + "</td>");
					mailText.append("<td style='background-color:yellow;'>" + (tmp.get("MATERIALSPECNAME")!=null&&!"".equals(tmp.get("MATERIALSPECNAME"))?tmp.get("MATERIALSPECNAME"):" ") + "</td>");
					mailText.append("<td style='background-color:yellow;'>" + (tmp.get("DESCRIPTION") != null&&!"".equals(tmp.get("DESCRIPTION"))?tmp.get("DESCRIPTION"):" ") + "</td>");
					mailText.append("<td style='background-color:yellow;'>"+ (tmp.get("ERPFACTORY") != null&&!"".equals(tmp.get("ERPFACTORY"))?tmp.get("ERPFACTORY"):" ") + "</td>");
					mailText.append("<td style='background-color:yellow;'>"+ (tmp.get("ERPLOCATION") != null&&!"".equals(tmp.get("ERPLOCATION"))?tmp.get("ERPLOCATION"):" ") + "</td>");
					mailText.append("<td style='background-color:yellow;'>" + (tmp.get("LOCATIONNAME") != null&&!"".equals(tmp.get("LOCATIONNAME"))?tmp.get("LOCATIONNAME"):" ") + "</td>");
					mailText.append("<td style='background-color:yellow;'>" + (tmp.get("OQARESULTSTATE") != null&&!"".equals(tmp.get("OQARESULTSTATE"))?tmp.get("OQARESULTSTATE"):" ") + "</td>");
					mailText.append("<td style='background-color:yellow;'>" + (tmp.get("MATERIALQUANTITY") != null&&!"".equals(tmp.get("MATERIALQUANTITY"))?tmp.get("MATERIALQUANTITY"):" ") + "</td>");
					mailText.append("<td style='background-color:yellow;'>" + (tmp.get("MATERIALUNIT") != null&&!"".equals(tmp.get("MATERIALUNIT"))?tmp.get("MATERIALUNIT"):" ") + "</td>");
					mailText.append("<td style='background-color:yellow;'>"+ (tmp.get("MAKEDATE") != null&&!"".equals(tmp.get("MAKEDATE"))?tmp.get("MAKEDATE"):" ") + "</td>");
					mailText.append("<td style='background-color:yellow;'>" + (tmp.get("RECEIVETIME") != null&&!"".equals(tmp.get("RECEIVETIME"))?tmp.get("RECEIVETIME"):" ") + "</td>");
					mailText.append("<td style='background-color:yellow;'>" + (tmp.get("EXPIRATIONDAY") != null&&!"".equals(tmp.get("EXPIRATIONDAY"))?tmp.get("EXPIRATIONDAY"):" ") + "</td>");
					mailText.append("<td style='background-color:yellow;'>" + (tmp.get("DAYS") != null&&!"".equals(tmp.get("DAYS"))?tmp.get("DAYS"):" ") + "</td></tr>");
				}
				else{
					mailText.append("<td style='background-color:red;'>" + (i+1) + "</td>");
					mailText.append("<td style='background-color:red;'>" + (tmp.get("SITENAME")!=null&&!"".equals(tmp.get("SITENAME"))?tmp.get("SITENAME"):" ") + "</td>");
					mailText.append("<td style='background-color:red;'>" + (tmp.get("MATERIALSPECNAME")!=null&&!"".equals(tmp.get("MATERIALSPECNAME"))?tmp.get("MATERIALSPECNAME"):" ") + "</td>");
					mailText.append("<td style='background-color:red;'>" + (tmp.get("DESCRIPTION") != null&&!"".equals(tmp.get("DESCRIPTION"))?tmp.get("DESCRIPTION"):" ") + "</td>");
					mailText.append("<td style='background-color:red;'>"+ (tmp.get("ERPFACTORY") != null&&!"".equals(tmp.get("ERPFACTORY"))?tmp.get("ERPFACTORY"):" ") + "</td>");
					mailText.append("<td style='background-color:red;'>"+ (tmp.get("ERPLOCATION") != null&&!"".equals(tmp.get("ERPLOCATION"))?tmp.get("ERPLOCATION"):" ") + "</td>");
					mailText.append("<td style='background-color:red;'>" + (tmp.get("LOCATIONNAME") != null&&!"".equals(tmp.get("LOCATIONNAME"))?tmp.get("LOCATIONNAME"):" ") + "</td>");
					mailText.append("<td style='background-color:red;'>" + (tmp.get("OQARESULTSTATE") != null&&!"".equals(tmp.get("OQARESULTSTATE"))?tmp.get("OQARESULTSTATE"):" ") + "</td>");
					mailText.append("<td style='background-color:red;'>" + (tmp.get("MATERIALQUANTITY") != null&&!"".equals(tmp.get("MATERIALQUANTITY"))?tmp.get("MATERIALQUANTITY"):" ") + "</td>");
					mailText.append("<td style='background-color:red;'>" + (tmp.get("MATERIALUNIT") != null&&!"".equals(tmp.get("MATERIALUNIT"))?tmp.get("MATERIALUNIT"):" ") + "</td>");
					mailText.append("<td style='background-color:red;'>"+ (tmp.get("MAKEDATE") != null&&!"".equals(tmp.get("MAKEDATE"))?tmp.get("MAKEDATE"):" ") + "</td>");
					mailText.append("<td style='background-color:red;'>" + (tmp.get("RECEIVETIME") != null&&!"".equals(tmp.get("RECEIVETIME"))?tmp.get("RECEIVETIME"):" ") + "</td>");
					mailText.append("<td style='background-color:red;'>" + (tmp.get("EXPIRATIONDAY") != null&&!"".equals(tmp.get("EXPIRATIONDAY"))?tmp.get("EXPIRATIONDAY"):" ") + "</td>");
					mailText.append("<td style='background-color:red;'>" + (tmp.get("DAYS") != null&&!"".equals(tmp.get("DAYS"))?tmp.get("DAYS"):" ") + "</td></tr>");
				}
				
			}
		mailText.append("</table>");
		mailText.append("</td>");
		mailText.append("</tr>");
		mailText.append("<tr><td></td></tr>");
		mailText.append("</table>");

		return mailText;
	}
	
	/**
	 * 在库html代码
	 */
	
	public StringBuffer getYardStockListManageMentHtml(List<Map<String, Object>> sqlResult)
	{
		StringBuffer mailText = new StringBuffer();

		mailText.append("<table width='100%'>");
		mailText.append("<tr height='30px'>");
		mailText.append("<td align='left' width='30%'>您好！<br/><br/></td>");
		mailText.append("</tr>");
		mailText.append("<tr>");
		mailText.append("<td align='left' width='30%'>设备在库List随附件，请查收！<br/><br/></td>");
		mailText.append("</tr>");
		mailText.append("<tr><td>");
		
		/*
		 * mailText.
		 * append("<table border='1' width='100%' style='text-align:center;border-collapse: collapse;border: none;border: solid #000 1px;'>"
		 * ); mailText.append("<tr>"); mailText.
		 * append("<td width='6%'>序号</td><td width='4%'>工厂</td><td width='10%'>PONO.</td><td width='10%'>入库单号</td>"
		 * ); mailText.
		 * append("<td width='10%'>设备编号</td><td width='10%'>设备名称</td><td width='6%'>库存状态</td><td width='6%'>箱号</td>"
		 * ); mailText.
		 * append("<td width='10%'>使用部门</td><td width='10%'>是否掏箱</td><td width='10%'>存放位置</td><td width='10%'>异常情况</td>"
		 * ); mailText.
		 * append("<td width='10%'>备注</td><td width='10%'>车辆信息</td><td width='15%'>Length(包装尺寸cm)</td><td width='15%'>Width(包装尺寸cm)</td>"
		 * ); mailText.
		 * append("<td width='10%'>Height(包装尺寸cm)</td><td width='10%'>毛重(kg)</td><td width='15%'>净重(kg)</td><td width='8%'>入库人</td>"
		 * ); mailText.append("<td width='10%'>入库时间</td><td width='10%'>入库类型</td>");
		 * mailText.append("</tr>");
		 * 
		 * if(sqlResult != null && !sqlResult.isEmpty()) for(int i = 0 ;
		 * i<sqlResult.size(); i++){ Map<String, Object> tmp = sqlResult.get(i);
		 * 
		 * mailText.append("<td>" + (i+1) + "</td>"); mailText.append("<td>" +
		 * (tmp.get("SITENAME")!=null&&!"".equals(tmp.get("SITENAME"))?tmp.get(
		 * "SITENAME"):" ") + "</td>"); mailText.append("<td>" +
		 * (tmp.get("PONO")!=null&&!"".equals(tmp.get("PONO"))?tmp.get("PONO"):" ") +
		 * "</td>"); mailText.append("<td>" + (tmp.get("RECEIVEREQUESTNAME") !=
		 * null&&!"".equals(tmp.get("RECEIVEREQUESTNAME"))?tmp.get("RECEIVEREQUESTNAME")
		 * :" ") + "</td>"); mailText.append("<td>"+ (tmp.get("DEVICE") !=
		 * null&&!"".equals(tmp.get("DEVICE"))?tmp.get("DEVICE"):" ") + "</td>");
		 * mailText.append("<td>"+ (tmp.get("DEVICENAME") !=
		 * null&&!"".equals(tmp.get("DEVICENAME"))?tmp.get("DEVICENAME"):" ") +
		 * "</td>"); mailText.append("<td>" + (tmp.get("STOCKSTATE") !=
		 * null&&!"".equals(tmp.get("STOCKSTATE"))?tmp.get("STOCKSTATE"):" ") +
		 * "</td>"); mailText.append("<td>" + (tmp.get("CASENO") !=
		 * null&&!"".equals(tmp.get("CASENO"))?tmp.get("CASENO"):" ") + "</td>");
		 * mailText.append("<td>" + (tmp.get("DEPARTMENT") !=
		 * null&&!"".equals(tmp.get("DEPARTMENT"))?tmp.get("DEPARTMENT"):" ") +
		 * "</td>"); mailText.append("<td>" + (tmp.get("UNPACKING") !=
		 * null&&!"".equals(tmp.get("UNPACKING"))?tmp.get("UNPACKING"):" ") + "</td>");
		 * mailText.append("<td>"+ (tmp.get("LOCATIONNAME") !=
		 * null&&!"".equals(tmp.get("LOCATIONNAME"))?tmp.get("LOCATIONNAME"):" ") +
		 * "</td>"); mailText.append("<td>" + (tmp.get("EXCEPTION") !=
		 * null&&!"".equals(tmp.get("EXCEPTION"))?tmp.get("EXCEPTION"):" ") + "</td>");
		 * mailText.append("<td>" + (tmp.get("DESCRIPTION") !=
		 * null&&!"".equals(tmp.get("DESCRIPTION"))?tmp.get("DESCRIPTION"):" ") +
		 * "</td>"); mailText.append("<td>" + (tmp.get("CARINFO") !=
		 * null&&!"".equals(tmp.get("CARINFO"))?tmp.get("CARINFO"):" ") + "</td>");
		 * mailText.append("<td>" + (tmp.get("LENGTH") !=
		 * null&&!"".equals(tmp.get("LENGTH"))?tmp.get("LENGTH"):" ") + "</td>");
		 * mailText.append("<td>" + (tmp.get("WIDTH") !=
		 * null&&!"".equals(tmp.get("WIDTH"))?tmp.get("WIDTH"):" ") + "</td>");
		 * mailText.append("<td>" + (tmp.get("HEIGHT") !=
		 * null&&!"".equals(tmp.get("HEIGHT"))?tmp.get("HEIGHT"):" ") + "</td>");
		 * mailText.append("<td>" + (tmp.get("CROSS") !=
		 * null&&!"".equals(tmp.get("CROSS"))?tmp.get("CROSS"):" ") + "</td>");
		 * mailText.append("<td>" + (tmp.get("NET") !=
		 * null&&!"".equals(tmp.get("NET"))?tmp.get("NET"):" ") + "</td>");
		 * mailText.append("<td>" + (tmp.get("RECEIVEUSER") !=
		 * null&&!"".equals(tmp.get("RECEIVEUSER"))?tmp.get("RECEIVEUSER"):" ") +
		 * "</td>"); mailText.append("<td>" + (tmp.get("RECEIVETIME") !=
		 * null&&!"".equals(tmp.get("RECEIVETIME"))?tmp.get("RECEIVETIME"):" ") +
		 * "</td>"); mailText.append("<td>" + (tmp.get("STOCKINTYPE") !=
		 * null&&!"".equals(tmp.get("STOCKINTYPE"))?tmp.get("STOCKINTYPE"):" ") +
		 * "</td></tr>"); } mailText.append("</table>");
		 */
		mailText.append("</td>");
		mailText.append("</tr>");
		mailText.append("<tr><td></td></tr>");
		mailText.append("</table>");

		return mailText;
	}
	
	/**
	 * 入库html代码
	 */
	
	public StringBuffer getYardStockInListManageMentHtml(List<Map<String, Object>> sqlResult)
	{
		StringBuffer mailText = new StringBuffer();

		mailText.append("<table width='100%'>");
		mailText.append("<tr height='30px'>");
		mailText.append("<td align='left' width='30%'>您好！<br/><br/></td>");
		mailText.append("</tr>");
		mailText.append("<tr>");
		mailText.append("<td align='left' width='30%'>设备入库List随附件，请查收！<br/><br/></td>");
		mailText.append("</tr>");
		mailText.append("<tr><td>");
		/*
		 * mailText.
		 * append("<table border='1' width='100%' style='text-align:center;border-collapse: collapse;border: none;border: solid #000 1px;'>"
		 * ); mailText.append("<tr>"); mailText.
		 * append("<td width='6%'>序号</td><td width='4%'>工厂</td><td width='10%'>PONO.</td><td width='10%'>入库单号</td>"
		 * ); mailText.
		 * append("<td width='10%'>设备编号</td><td width='10%'>设备名称</td><td width='6%'>库存状态</td><td width='6%'>箱号</td>"
		 * ); mailText.
		 * append("<td width='10%'>使用部门</td><td width='10%'>是否掏箱</td><td width='10%'>存放位置</td><td width='10%'>异常情况</td>"
		 * ); mailText.
		 * append("<td width='10%'>备注</td><td width='10%'>车辆信息</td><td width='15%'>Length(包装尺寸cm)</td><td width='15%'>Width(包装尺寸cm)</td>"
		 * ); mailText.
		 * append("<td width='10%'>Height(包装尺寸cm)</td><td width='10%'>毛重(kg)</td><td width='15%'>净重(kg)</td><td width='8%'>入库人</td>"
		 * ); mailText.append("<td width='10%'>入库时间</td><td width='10%'>入库类型</td>");
		 * mailText.append("</tr>");
		 * 
		 * if(sqlResult != null && !sqlResult.isEmpty()) for(int i = 0 ;
		 * i<sqlResult.size(); i++){ Map<String, Object> tmp = sqlResult.get(i);
		 * 
		 * mailText.append("<td>" + (i+1) + "</td>"); mailText.append("<td>" +
		 * (tmp.get("SITENAME")!=null&&!"".equals(tmp.get("SITENAME"))?tmp.get(
		 * "SITENAME"):" ") + "</td>"); mailText.append("<td>" +
		 * (tmp.get("PONO")!=null&&!"".equals(tmp.get("PONO"))?tmp.get("PONO"):" ") +
		 * "</td>"); mailText.append("<td>" + (tmp.get("RECEIVEREQUESTNAME") !=
		 * null&&!"".equals(tmp.get("RECEIVEREQUESTNAME"))?tmp.get("RECEIVEREQUESTNAME")
		 * :" ") + "</td>"); mailText.append("<td>"+ (tmp.get("DEVICE") !=
		 * null&&!"".equals(tmp.get("DEVICE"))?tmp.get("DEVICE"):" ") + "</td>");
		 * mailText.append("<td>"+ (tmp.get("DEVICENAME") !=
		 * null&&!"".equals(tmp.get("DEVICENAME"))?tmp.get("DEVICENAME"):" ") +
		 * "</td>"); mailText.append("<td>" + (tmp.get("STOCKSTATE") !=
		 * null&&!"".equals(tmp.get("STOCKSTATE"))?tmp.get("STOCKSTATE"):" ") +
		 * "</td>"); mailText.append("<td>" + (tmp.get("CASENO") !=
		 * null&&!"".equals(tmp.get("CASENO"))?tmp.get("CASENO"):" ") + "</td>");
		 * mailText.append("<td>" + (tmp.get("DEPARTMENT") !=
		 * null&&!"".equals(tmp.get("DEPARTMENT"))?tmp.get("DEPARTMENT"):" ") +
		 * "</td>"); mailText.append("<td>" + (tmp.get("UNPACKING") !=
		 * null&&!"".equals(tmp.get("UNPACKING"))?tmp.get("UNPACKING"):" ") + "</td>");
		 * mailText.append("<td>"+ (tmp.get("LOCATIONNAME") !=
		 * null&&!"".equals(tmp.get("LOCATIONNAME"))?tmp.get("LOCATIONNAME"):" ") +
		 * "</td>"); mailText.append("<td>" + (tmp.get("EXCEPTION") !=
		 * null&&!"".equals(tmp.get("EXCEPTION"))?tmp.get("EXCEPTION"):" ") + "</td>");
		 * mailText.append("<td>" + (tmp.get("DESCRIPTION") !=
		 * null&&!"".equals(tmp.get("DESCRIPTION"))?tmp.get("DESCRIPTION"):" ") +
		 * "</td>"); mailText.append("<td>" + (tmp.get("CARINFO") !=
		 * null&&!"".equals(tmp.get("CARINFO"))?tmp.get("CARINFO"):" ") + "</td>");
		 * mailText.append("<td>" + (tmp.get("LENGTH") !=
		 * null&&!"".equals(tmp.get("LENGTH"))?tmp.get("LENGTH"):" ") + "</td>");
		 * mailText.append("<td>" + (tmp.get("WIDTH") !=
		 * null&&!"".equals(tmp.get("WIDTH"))?tmp.get("WIDTH"):" ") + "</td>");
		 * mailText.append("<td>" + (tmp.get("HEIGHT") !=
		 * null&&!"".equals(tmp.get("HEIGHT"))?tmp.get("HEIGHT"):" ") + "</td>");
		 * mailText.append("<td>" + (tmp.get("CROSS") !=
		 * null&&!"".equals(tmp.get("CROSS"))?tmp.get("CROSS"):" ") + "</td>");
		 * mailText.append("<td>" + (tmp.get("NET") !=
		 * null&&!"".equals(tmp.get("NET"))?tmp.get("NET"):" ") + "</td>");
		 * mailText.append("<td>" + (tmp.get("RECEIVEUSER") !=
		 * null&&!"".equals(tmp.get("RECEIVEUSER"))?tmp.get("RECEIVEUSER"):" ") +
		 * "</td>"); mailText.append("<td>" + (tmp.get("RECEIVETIME") !=
		 * null&&!"".equals(tmp.get("RECEIVETIME"))?tmp.get("RECEIVETIME"):" ") +
		 * "</td>"); mailText.append("<td>" + (tmp.get("STOCKINTYPE") !=
		 * null&&!"".equals(tmp.get("STOCKINTYPE"))?tmp.get("STOCKINTYPE"):" ") +
		 * "</td></tr>"); } mailText.append("</table>");
		 */
		mailText.append("</td>");
		mailText.append("</tr>");
		mailText.append("<tr><td></td></tr>");
		mailText.append("</table>");

		return mailText;
	}
	/**
	 * 出库html代码
	 */
	
	public StringBuffer getYardStockOutListManageMentHtml(List<Map<String, Object>> sqlResult)
	{
		StringBuffer mailText = new StringBuffer();

		mailText.append("<table width='100%'>");
		mailText.append("<tr height='30px'>");
		mailText.append("<td align='left' width='30%'>您好！<br/><br/></td>");
		mailText.append("</tr>");
		mailText.append("<tr>");
		mailText.append("<td align='left' width='30%'>设备出库List随附件，请查收！<br/><br/></td>");
		mailText.append("</tr>");
		mailText.append("<tr><td>");
		
		/*
		 * mailText.
		 * append("<table border='1' width='100%' style='text-align:center;border-collapse: collapse;border: none;border: solid #000 1px;'>"
		 * ); mailText.append("<tr>"); mailText.
		 * append("<td width='6%'>序号</td><td width='10%'>PONO.</td><td width='10%'>提货单号</td>"
		 * ); mailText.
		 * append("<td width='10%'>设备编号</td><td width='10%'>设备名称</td><td width='6%'>箱号</td><td width='6%'>长度</td>"
		 * ); mailText.
		 * append("<td width='6%'>宽度</td><td width='6%'>高度</td><td width='6%'>毛重</td><td width='6%'>净重</td>"
		 * ); mailText.
		 * append("<td width='10%'>搬入日期</td><td width='10%'>搬入口</td><td width='10%'>搬入顺序</td><td width='10%'>运输要求</td>"
		 * ); mailText.
		 * append("<td width='10%'>备注</td><td width='6%'>出库人</td><td width='10%'>出库时间</td>"
		 * ); mailText.append("</tr>");
		 * 
		 * if(sqlResult != null && !sqlResult.isEmpty()) for(int i = 0 ;
		 * i<sqlResult.size(); i++){ Map<String, Object> tmp = sqlResult.get(i);
		 * mailText.append("<td>" + (i+1) + "</td>"); mailText.append("<td>" +
		 * (tmp.get("PONO")!=null&&!"".equals(tmp.get("PONO"))?tmp.get("PONO"):" ") +
		 * "</td>"); mailText.append("<td>" + (tmp.get("SHIPREQUESTNAME") !=
		 * null&&!"".equals(tmp.get("SHIPREQUESTNAME"))?tmp.get("SHIPREQUESTNAME"):" ")
		 * + "</td>"); mailText.append("<td>"+ (tmp.get("DEVICE") !=
		 * null&&!"".equals(tmp.get("DEVICE"))?tmp.get("DEVICE"):" ") + "</td>");
		 * mailText.append("<td>"+ (tmp.get("DEVICENAME") !=
		 * null&&!"".equals(tmp.get("DEVICENAME"))?tmp.get("DEVICENAME"):" ") +
		 * "</td>"); mailText.append("<td>" + (tmp.get("CASENO") !=
		 * null&&!"".equals(tmp.get("CASENO"))?tmp.get("CASENO"):" ") + "</td>");
		 * mailText.append("<td>" + (tmp.get("LENGTH") !=
		 * null&&!"".equals(tmp.get("LENGTH"))?tmp.get("LENGTH"):" ") + "</td>");
		 * mailText.append("<td>" + (tmp.get("WIDTH") !=
		 * null&&!"".equals(tmp.get("WIDTH"))?tmp.get("WIDTH"):" ") + "</td>");
		 * mailText.append("<td>" + (tmp.get("HEIGHT") !=
		 * null&&!"".equals(tmp.get("HEIGHT"))?tmp.get("HEIGHT"):" ") + "</td>");
		 * mailText.append("<td>" + (tmp.get("CROSS") !=
		 * null&&!"".equals(tmp.get("CROSS"))?tmp.get("CROSS"):" ") + "</td>");
		 * mailText.append("<td>" + (tmp.get("NET") !=
		 * null&&!"".equals(tmp.get("NET"))?tmp.get("NET"):" ") + "</td>");
		 * mailText.append("<td>" + (tmp.get("MOVEDATE") !=
		 * null&&!"".equals(tmp.get("MOVEDATE"))?tmp.get("MOVEDATE"):" ") + "</td>");
		 * mailText.append("<td>" + (tmp.get("MOVEENTRANCE") !=
		 * null&&!"".equals(tmp.get("MOVEENTRANCE"))?tmp.get("MOVEENTRANCE"):" ") +
		 * "</td>"); mailText.append("<td>"+ (tmp.get("MOVEORDER") !=
		 * null&&!"".equals(tmp.get("MOVEORDER"))?tmp.get("MOVEORDER"):" ") + "</td>");
		 * mailText.append("<td>" + (tmp.get("TRANSPORTREQUIRE") !=
		 * null&&!"".equals(tmp.get("TRANSPORTREQUIRE"))?tmp.get("TRANSPORTREQUIRE"):" "
		 * ) + "</td>"); mailText.append("<td>" + (tmp.get("DESCRIPTION") !=
		 * null&&!"".equals(tmp.get("DESCRIPTION"))?tmp.get("DESCRIPTION"):" ") +
		 * "</td>"); mailText.append("<td>" + (tmp.get("LASTEVENTUSER") !=
		 * null&&!"".equals(tmp.get("LASTEVENTUSER"))?tmp.get("LASTEVENTUSER"):" ") +
		 * "</td>"); mailText.append("<td>" + (tmp.get("LASTEVENTTIME") !=
		 * null&&!"".equals(tmp.get("LASTEVENTTIME"))?tmp.get("LASTEVENTTIME"):" ") +
		 * "</td></tr>"); } mailText.append("</table>");
		 */
		mailText.append("</td>");
		mailText.append("</tr>");
		mailText.append("<tr><td></td></tr>");
		mailText.append("</table>");
		return mailText;
	}

	/**
	 * 寿命管理附件生成excel
	 * @param sqlResult
	 * @param fout
	 */
	public void createLifeManagementExcel(List<Map<String, Object>> sqlResult,OutputStream os) throws WriteException,IOException {
		WritableWorkbook workbook = Workbook.createWorkbook(os);
		WritableSheet sheet = workbook.createSheet("First Sheet", 0);

		sheet.mergeCells(0, 0, 9, 0);//添加合并单元格，第一个参数是起始列，第二个参数是起始行，第三个参数是终止列，第四个参数是终止行
		WritableFont bold = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD);//设置字体种类和黑体显示,字体为Arial,字号大小为10,采用黑体显示
		WritableCellFormat titleFormate = new WritableCellFormat(bold);//生成一个单元格样式控制对象
		titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.TOP);//单元格的内容垂直方向居中
		titleFormate.setWrap(true);
		sheet.addCell(new Label(0,0,"您好！\n\n目前有以下材料寿命即将到期，请及时检查。谢谢\n",titleFormate));

		sheet.setRowView(0, 1000, false);

		sheet.addCell(new Label(0,1,"序号"));
		sheet.addCell(new Label(1,1,"工厂"));
		sheet.addCell(new Label(2,1,"物料号"));
		sheet.addCell(new Label(3,1,"物料描述"));
		sheet.addCell(new Label(4,1,"库存地"));
		sheet.addCell(new Label(5,1,"货位地"));
		sheet.addCell(new Label(6,1,"库存状态"));
		sheet.addCell(new Label(7,1,"数量"));
		sheet.addCell(new Label(8,1,"单位"));
		sheet.addCell(new Label(9,1,"生产日期"));
		sheet.addCell(new Label(10,1,"入库时间"));
		sheet.addCell(new Label(11,1,"寿命到期日"));
		sheet.addCell(new Label(12,1,"到期剩余"));

		if(sqlResult != null && !sqlResult.isEmpty())
			for(int i = 0 ; i<sqlResult.size(); i++){
				Map<String, Object> tmp = sqlResult.get(i);

				sheet.addCell(new Label(0,2+i,(i+1)+""));
				sheet.addCell(new Label(1,2+i,(tmp.get("SITENAME")!=null?tmp.get("SITENAME").toString():"")));
				sheet.addCell(new Label(2,2+i,(tmp.get("MATERIALSPECNAME")!=null?tmp.get("MATERIALSPECNAME").toString():"")));
				sheet.addCell(new Label(3,2+i,(tmp.get("DESCRIPTION") != null?tmp.get("DESCRIPTION").toString():"")));
				sheet.addCell(new Label(4,2+i,(tmp.get("ERPLOCATION")!= null?tmp.get("ERPLOCATION").toString():"")));
				sheet.addCell(new Label(5,2+i,(tmp.get("LOCATIONNAME")!= null?tmp.get("LOCATIONNAME").toString():"")));
				sheet.addCell(new Label(6,2+i,(tmp.get("OQARESULTSTATE")!= null?tmp.get("OQARESULTSTATE").toString():"")));
				sheet.addCell(new Label(7,2+i,(tmp.get("MATERIALQUANTITY")!= null?tmp.get("MATERIALQUANTITY").toString():"")));
				sheet.addCell(new Label(8,2+i,(tmp.get("MATERIALUNIT")!= null?tmp.get("MATERIALUNIT").toString():"")));
				sheet.addCell(new Label(9,2+i,(tmp.get("MAKEDATE")!= null?tmp.get("MAKEDATE").toString():"")));
				sheet.addCell(new Label(10,2+i,(tmp.get("RECEIVETIME")!= null?tmp.get("RECEIVETIME").toString():"")));
				sheet.addCell(new Label(11,2+i,(tmp.get("EXPIRATIONDAY")!= null?tmp.get("EXPIRATIONDAY").toString():"")));
				sheet.addCell(new Label(12,2+i,(tmp.get("DAYS")!= null?tmp.get("DAYS").toString():"")));

			}
		//把创建的内容写入到输出流中，并关闭输出流
		workbook.write();
		workbook.close();
		os.close();

	}
	
	public String[] getRecMailUsers(String type,String siteName){
		String dbSql  =  "select  receiver from mailtoaddressspec where type=? and sitename=? ";

		List<Map<String, Object>> sqlResult = IDMFrameServiceProxy.getSqlTemplate().queryForList(dbSql,new Object[]{type,siteName});
		
		if(sqlResult.get(0).get("RECEIVER") != null && sqlResult.get(0).get("RECEIVER")!=""){
			return sqlResult.get(0).get("receiver").toString().trim().split(",");
		}
		
		return null;
	}
	
	public String[] getRecMailUsersForYard(String type,String siteName){
		String dbSql  =  "select receiver from YARD_MAILDETAIL where num=?";
		List<Map<String, Object>> sqlResult = IDMFrameServiceProxy.getSqlTemplate().queryForList(dbSql,new Object[]{type});
		ArrayList<String> list = new ArrayList<String>();
		String[] arr = new String[sqlResult.size()];
		if (sqlResult.size() > 0) {
			for (int i = 0; i< sqlResult.size(); i++) {
				list.add(sqlResult.get(i).get("receiver").toString());
			}
			list.toArray(arr);
			return arr;
		}
		return null;
	}
	public List<Map<String, Object>> getRecMailUsersSiteName(String type){
		String dbSql  =  "select distinct SITENAME from mailtoaddressspec where TYPE=:TYPE ";		
		Map<String,Object> siteMap=new HashMap<String, Object>();
		siteMap.put("TYPE", type);
		List<Map<String, Object>> sqlResult=IDMFrameServiceProxy.getSqlTemplate().queryForList(dbSql, siteMap);
		return sqlResult;
	}
	
	public MWMSMail getMWMSMail() {
		return MWMSMail;
	}

	public void setMWMSMail(MWMSMail MWMSMail) {
		this.MWMSMail = MWMSMail;
	}
	
	public void createDir(String url){
		File file = new File(url);
		  //判断是否存在
		  if (!file.exists() && !file.isDirectory()) 
		  {
		   try 
		   {
		    //生成文件夹
		    file.mkdir();
		   }
		   catch (Exception e) 
		   {
		    e.printStackTrace();
		   }
		  }
	}
	
	/**
	 * 
	 */
	public void AssignLocReport(){
		List<Map<String, Object>> getSiteName = getRecMailUsersSiteName("SendAssignLocReport");//获取工厂名
		for(int i=0;i<getSiteName.size();i++)
		{
			String siteName = getSiteName.get(i).get("SITENAME").toString();
			SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
			Date date = new Date();
			Calendar c= Calendar.getInstance();
			c.setTime(date);
			c.add(Calendar.DAY_OF_MONTH, -1);
			String receiveTime = sdf.format(c.getTime()) + " 20:00:00";		
			
			String dbSql  = "SELECT  '0' ROW_NUM "
					+" ,'TTL' ERPFACTORY "
					+" ,'-' ERPLOCATION "
					+" ,COUNT(MATERIALPACKINGNAME) AS SUM_QTY "
					+" ,SUM (CASE WHEN receivetime<to_date(:RECEIVETIME,'yyyy-MM-dd  hh24:mi:ss')  THEN 1 ELSE 0 END) BEFQUANTITY "
					+" ,SUM (CASE WHEN length(trim(LOCATIONNAME))>0 AND receivetime<to_date(:RECEIVETIME,'yyyy-MM-dd  hh24:mi:ss')  THEN 1 ELSE 0 END) LOCQUANTITY "
					+" ,(CASE WHEN (SUM (CASE WHEN receivetime<to_date(:RECEIVETIME,'yyyy-MM-dd  hh24:mi:ss')  THEN 1 ELSE 0 END))>0 THEN  "
					+" (cast(cast(100*sum(CASE WHEN length(trim(LOCATIONNAME))>0 AND receivetime<to_date(:RECEIVETIME,'yyyy-MM-dd  hh24:mi:ss')   "
					+"                          THEN 1 ELSE 0 END)/SUM (CASE WHEN receivetime<to_date(:RECEIVETIME,'yyyy-MM-dd  hh24:mi:ss')   "
					+"                            THEN 1 ELSE 0 END) as decimal(18,2)) as varchar(5))||'%') "
					+"                              ELSE '0%' END) as RADIO "
					+ " ,'' RESPPERSON"
					+"  FROM MATERIALPACKING "
					+"  where stockstate='Stocked' "
					+"   AND MATERIALPACKING.SITENAME=:SITENAME "
					+ "  AND MATERIALQUANTITY <> '0'  "
					+"   AND ERPFACTORY||ERPLOCATION in (select ERPFACTORY||ERPLOCATION from ERPLOCATIONCONFIGVALUE  "
					+"     where SITENAME=:SITENAME AND OPERATIONTYPE=:OPERATIONTYPE) "
					+"   AND not regexp_like(materialspecname,(select substr(max(sys_connect_by_path(ENUMVALUE,'|')),2) as materialspecname from ( "
					+" select ENUMVALUE,rownum ro  "
					+" from ENUMDEFVALUE  "
					+" where enumname='PackSpec'  "
					+" ) newtab  "
					+" start with newtab.ro = 1  "
					+" connect by prior newtab.ro = newtab.ro - 1)) "
					+"  UNION "
					+" SELECT  "
					+" '1' ROW_NUM "
					+" ,ERPFACTORY "
					+" ,ERPLOCATION "
					+" ,COUNT(MATERIALPACKINGNAME) AS SUM_QTY "
					+" ,SUM (CASE WHEN receivetime<to_date(:RECEIVETIME,'yyyy-MM-dd  hh24:mi:ss')  THEN 1 ELSE 0 END) BEFQUANTITY "
					+" ,SUM (CASE WHEN length(trim(LOCATIONNAME))>0 AND receivetime<to_date(:RECEIVETIME,'yyyy-MM-dd  hh24:mi:ss')  THEN 1 ELSE 0 END) LOCQUANTITY "
					+" ,(CASE WHEN (SUM (CASE WHEN receivetime<to_date(:RECEIVETIME,'yyyy-MM-dd  hh24:mi:ss')  THEN 1 ELSE 0 END))>0 THEN  "
					+" (cast(cast(100*sum(CASE WHEN length(trim(LOCATIONNAME))>0 AND receivetime<to_date(:RECEIVETIME,'yyyy-MM-dd  hh24:mi:ss')   "
					+"                          THEN 1 ELSE 0 END)/SUM (CASE WHEN receivetime<to_date(:RECEIVETIME,'yyyy-MM-dd  hh24:mi:ss')   "
					+"                            THEN 1 ELSE 0 END) as decimal(18,2)) as varchar(5))||'%') "
					+"                              ELSE '0%' END) as RADIO "
					+ " ,erp.RESPPERSON "
					+"  FROM MATERIALPACKING "
					+ " LEFT JOIN ERPLOCATION erp ON MATERIALPACKING.ERPLOCATION  = erp.ERPLOCATIONNAME"
					+"  where stockstate='Stocked' "
					+"  AND MATERIALPACKING.SITENAME=:SITENAME "
					+ "  AND MATERIALQUANTITY <> '0'  "
					+"  AND ERPFACTORY||ERPLOCATION in (select ERPFACTORY||ERPLOCATION from ERPLOCATIONCONFIGVALUE  "
					+"     where SITENAME=:SITENAME AND OPERATIONTYPE=:OPERATIONTYPE) "
					+"  AND not regexp_like(materialspecname,(select substr(max(sys_connect_by_path(ENUMVALUE,'|')),2) as materialspecname from ( "
					+" select ENUMVALUE,rownum ro  "
					+" from ENUMDEFVALUE  "
					+" where enumname='PackSpec'  "
					+" ) newtab  "
					+" start with newtab.ro = 1  "
					+" connect by prior newtab.ro = newtab.ro - 1)) "
					+"  GROUP BY ERPFACTORY,ERPLOCATION,erp.RESPPERSON ";
			Map<String, Object> bindMap_Query = new HashMap<String, Object>();
			bindMap_Query.put("SITENAME", siteName);
			bindMap_Query.put("RECEIVETIME", receiveTime);
			bindMap_Query.put("OPERATIONTYPE", "AssignLocation");
			List<Map<String, Object>> sqlResult = IDMFrameServiceProxy.getSqlTemplate().queryForList(dbSql,bindMap_Query);
			log.info("货位绑定报表查询回来的结果 sqlResult.size() => " + sqlResult.size());

			if(sqlResult != null && !sqlResult.isEmpty()){
				log.info("货位绑定报表准备发送 1 => ");
				Date sendDate = new Date(); // mail send  time
				String mailSendDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(sendDate);
				
				//本地环境
//				createDir("/attachment");			
//				String fileUrl ="/attachment/" + new SimpleDateFormat("yyyyMMddHHmmsss").format(sendDate) + ".xls"; //E:/students.xls
				//量产环境路径
				createDir("/home/mwms/file");			
				String fileUrl ="/home/mwms/file/" + new SimpleDateFormat("yyyyMMddHHmmsss").format(sendDate) + ".xls"; //E:/students.xls
				//测试环境路径
				//createDir("/usr01/wms/mwms/file");			
				//String fileUrl ="/usr01/wms/mwms/file/" + new SimpleDateFormat("yyyyMMddHHmmsss").format(sendDate) + ".xls"; //E:/students.xls

//				String fileUrl ="D:\\home\\mwms\\file\\student.xlsx"; //本地电脑测试
				//生成excel
				FileOutputStream fout;
				try {
					fout = new FileOutputStream(fileUrl);
					createAssignedLocationReportExcel(sqlResult,fout);
				} catch (WriteException e) {e.printStackTrace();
				} catch (IOException e) {e.printStackTrace();}

				
				MailSenderInfo mailInfo = new MailSenderInfo();
				
//				mailInfo.setMailServerHost(MWMSMail.getHost());   
//				mailInfo.setMailServerPort(MWMSMail.getPort());   
//				mailInfo.setValidate(true);   
//				mailInfo.setUserName(MWMSMail.getUsername());   
//				mailInfo.setPassword(MWMSMail.getPassword());
//				mailInfo.setFromAddress(MWMSMail.getFromAddress());
				
				mailInfo.setMailServerHost("10.251.65.44");   
				mailInfo.setMailServerPort("25");   
				mailInfo.setValidate(true);   
				mailInfo.setUserName("wangsichen@boe.com.cn");   
				mailInfo.setPassword("wscE16301147");
				mailInfo.setFromAddress("wangsichen@boe.com.cn");
				
//				MWMSMail mail = this.initMailSender();
				
				mailInfo.setAttachFileNames(new String[]{fileUrl});
				mailInfo.setSubject("货位绑定报表");  
				
				MailSendHistory mailSendHistory = new MailSendHistory();  

				mailSendHistory.setSendTime(mailSendDate);
				mailSendHistory.setInterfaceType("SendAssignLocReport");
				
				String[] receiver = getRecMailUsers("SendAssignLocReport",siteName);
				log.info("货位绑定报表准备发送2  => " + JSON.toJSONString(mailInfo));
				if(receiver != null){
					mailInfo.setToAddress(receiver);
					SimpleMailSender sms = new SimpleMailSender();
					mailInfo.setContent(getAssignLocationHtml(sqlResult).toString());   

					if(mailInfo.getContent() == null){
						mailSendHistory.setErrorType("邮件内容是空的");
						mailSendHistory.setSendState("false");
					}else{
						try {
							sms.sendHtmlMail(mailInfo);
							mailSendHistory.setSendState("true");
						} catch (Exception e) {
							if(e.getClass() == SendFailedException.class){
								mailSendHistory.setErrorType("收件人地址错误");
								mailSendHistory.setSendState("false");
							}else if(e.getClass() == AuthenticationFailedException.class){
								mailSendHistory.setErrorType("发件人用户名密码错误");
								mailSendHistory.setSendState("false");
							}else if (e.getClass() == MessagingException.class) {
								mailSendHistory.setErrorType("连接发件服务器失败");
								mailSendHistory.setSendState("false");
							}else if(e.getClass() == AddressException.class){
								mailSendHistory.setErrorType("收件人地址非法");
								mailSendHistory.setSendState("false");
							}
						}
					}
				}else{
					mailSendHistory.setErrorType("收件人地址列表是空的");
					mailSendHistory.setSendState("false");
				}
				//寿命管理的db操作
				updateMailSendHistory(mailSendHistory);
			}
		}
	}
	
	/**
	 * 绑定货位报表附件生成excel
	 * @param sqlResult
	 * @param fout
	 */
	public void createAssignedLocationReportExcel(List<Map<String, Object>> sqlResult,OutputStream os) throws WriteException,IOException {
		WritableWorkbook workbook = Workbook.createWorkbook(os);
		WritableSheet sheet = workbook.createSheet("First Sheet", 0);

		sheet.mergeCells(0, 0, 9, 0);//添加合并单元格，第一个参数是起始列，第二个参数是起始行，第三个参数是终止列，第四个参数是终止行
		WritableFont bold = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD);//设置字体种类和黑体显示,字体为Arial,字号大小为10,采用黑体显示
		WritableCellFormat titleFormate = new WritableCellFormat(bold);//生成一个单元格样式控制对象
		titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.TOP);//单元格的内容垂直方向居中
		titleFormate.setWrap(true);
		sheet.addCell(new Label(0,0,"您好！\n\n这是货位绑定报表。谢谢"
				+ "\n\n货位绑定率=绑定货位Box/在库总Box，统计范围为非当天入库原材料（不含材制损/包材）、备件（不含报废）、耗材"
				+ "\n\n定点取数（早8点取前一天晚上8点前入库的Box），用SAP库位区分需绑定货位物料"
				+ "\n\n原材库位包含包材，用包材料号段区分，取数时剔除包材",titleFormate));

		sheet.setRowView(0, 1000, false);

		sheet.addCell(new Label(0,1,"序号"));
		sheet.addCell(new Label(1,1,"工厂代码"));
		sheet.addCell(new Label(2,1,"库位"));
		sheet.addCell(new Label(3,1,"在库总箱数"));
		sheet.addCell(new Label(4,1,"在库总箱数箱数（非当天入）"));
		sheet.addCell(new Label(5,1,"在库总箱数箱数（非当天入）绑定货位Box"));
		sheet.addCell(new Label(6,1,"货位绑定率"));
		sheet.addCell(new Label(7,1,"货位责任人"));

		if(sqlResult != null && !sqlResult.isEmpty())
			for(int i = 0 ; i<sqlResult.size(); i++){
				Map<String, Object> tmp = sqlResult.get(i);

				sheet.addCell(new Label(0,2+i,(i+1)+""));
				sheet.addCell(new Label(1,2+i,(tmp.get("ERPFACTORY")!=null?tmp.get("ERPFACTORY").toString():"")));
				sheet.addCell(new Label(2,2+i,(tmp.get("ERPLOCATION")!=null?tmp.get("ERPLOCATION").toString():"")));
				sheet.addCell(new Label(3,2+i,(tmp.get("SUM_QTY") != null?tmp.get("SUM_QTY").toString():"")));
				sheet.addCell(new Label(4,2+i,(tmp.get("BEFQUANTITY")!= null?tmp.get("BEFQUANTITY").toString():"")));
				sheet.addCell(new Label(5,2+i,(tmp.get("LOCQUANTITY")!= null?tmp.get("LOCQUANTITY").toString():"")));
				sheet.addCell(new Label(6,2+i,(tmp.get("RADIO")!= null?tmp.get("RADIO").toString():"")));
				sheet.addCell(new Label(7,2+i,(tmp.get("RESPPERSON")!= null?tmp.get("RESPPERSON").toString():"")));

			}
		//把创建的内容写入到输出流中，并关闭输出流
		workbook.write();
		workbook.close();
		os.close();

	}
	/**
	 * 货位绑定报表html代码
	 */
	
	public StringBuffer getAssignLocationHtml(List<Map<String, Object>> sqlResult)
	{
		StringBuffer mailText = new StringBuffer();

		mailText.append("<table width='100%'>");
		mailText.append("<tr height='30px'>");
		mailText.append("<td align='left' width='30%'>您好！<br/><br/></td>");
		mailText.append("</tr>");
		mailText.append("<tr>");
		mailText.append("<td align='left' width='30%'>仓库绑定货位报表如附件！<br/><br/></td>");
		mailText.append("</tr>");
		mailText.append("<tr>");

		return mailText;
	}
	
	public MWMSMail initMailSender() {
		MWMSMail mailInfo = new MWMSMail();
		mailInfo.setHost("10.251.65.44");
		mailInfo.setPort("25"); 
		mailInfo.setUsername("cim-b20@boe.com.cn");
		mailInfo.setPassword("Cimb20@987654");
		mailInfo.setFromAddress("cim-b20@boe.com.cn");
		
//		mailInfo.setUsername("wangsichen@boe.com.cn");
//		mailInfo.setPassword("wscE16301147");
//		mailInfo.setFromAddress("wangsichen@boe.com.cn");
		
		return mailInfo;
	}

	/**
	 * 每日寿命资材反馈，已过期/一个月内到期等（每天凌晨0：00发送）
	 */
	public void sendMaterialLifeReport() {
		
		log.info("开始执行寿命资材的邮件发送任务2  ==========> ");
		
		List<Map<String, Object>> getSiteName = getRecMailUsersSiteName("SendMaterialLifeReport");//获取工厂名
		for(int i=0;i<getSiteName.size();i++){
			String siteName=getSiteName.get(i).get("SITENAME").toString();
			String dbSql  =  "SELECT B.MATERIALSPECNAME"
				+ ",B.MATERIALPACKINGNAME"
				+ ",B.SITENAME"
				+ ",A.DESC_CN AS DESCRIPTION"
				+ ",B.ERPFACTORY"
				+ ",B.ERPLOCATION"
				+ ",B.LOCATIONNAME"
				+ ",B.OQARESULTSTATE"
				+ ",B.MATERIALQUANTITY"
				+ ",A.MATERIALUNIT"
				+ ",TO_CHAR (B.MAKEDATE, 'yyyy-mm-dd hh:mm:ss') AS MAKEDATE"
				+ ",TO_CHAR (B.RECEIVETIME, 'yyyy-mm-dd hh:mm:ss') AS RECEIVETIME"
				+ ",TO_CHAR (B.EXPIRINGDATE, 'yyyy-mm-dd hh:mm:ss') AS EXPIRATIONDAY"
				+ ",Round(TO_NUMBER(EXPIRINGDATE - TO_DATE(TO_CHAR(SYSDATE,'yyyy-MM-dd'),'yyyy-MM-dd')),0) AS DAYS"
				+ " FROM MATERIALPACKING B"
				+ " LEFT JOIN MATERIALSPEC A ON B.MATERIALSPECNAME = A.MATERIALSPECNAME"
				+ " WHERE B.SITENAME = ?"
				+ " AND B.STOCKSTATE = 'Stocked'"
				+ " AND B.PACKINGSTATE = 'Released'"
				+ " AND B.MATERIALQUANTITY > 0 AND"
				+ " Round(TO_NUMBER(EXPIRINGDATE - TO_DATE(TO_CHAR(SYSDATE,'yyyy-MM-dd'),'yyyy-MM-dd')),0) <= 30"
				+ " ORDER BY DAYS DESC";
		
			List<Map<String, Object>> sqlResult = IDMFrameServiceProxy.getSqlTemplate().queryForList(dbSql,new Object[]{siteName});
	
			if(sqlResult != null && !sqlResult.isEmpty()){
				Date sendDate = new Date(); // mail send  time
				String mailSendDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(sendDate);
//				测试服务器
//				createDir("/usr01/wmstst/attachment");
//				String fileUrl ="/usr01/wmstst/attachment/" + new SimpleDateFormat("yyyyMMddHHmmsss").format(sendDate) + ".xls";
				
//				量产服务器
//				createDir("/wmsap/wmsadm/attachment");
				String fileUrl ="/wmsap/wmsadm/attachment/" + new SimpleDateFormat("yyyyMMddHHmmsss").format(sendDate) + ".xls";
				
				log.info("每日发送邮件附件位置 每日寿命资材反馈  fileUrl  => " + fileUrl);
	
				//生成excel
				FileOutputStream fout;
				try {
					fout = new FileOutputStream(fileUrl);
//					createQualityInspectionPendingExcel(sqlResult,fout);
					createMaterialLifeExcel(sqlResult,fout);
				} catch (WriteException e) {e.printStackTrace();
				} catch (IOException e) {e.printStackTrace();}
				MailSenderInfo mailInfo = new MailSenderInfo();
				
				MWMSMail mail = this.initMailSender();
				
				mailInfo.setMailServerHost(mail.getHost());
				mailInfo.setMailServerPort(mail.getPort());  
				mailInfo.setValidate(true);
				mailInfo.setUserName(mail.getUsername());
				mailInfo.setPassword(mail.getPassword());
				mailInfo.setFromAddress(mail.getFromAddress());
				
				MailSendHistory mailSendHistory = new MailSendHistory(); 
				
				mailSendHistory.setSendTime(mailSendDate);
				mailSendHistory.setInterfaceType("SendMaterialLifeReport");
				
			 	String[] receiver = getRecMailUsers("SendMaterialLifeReport",siteName);
				if(receiver != null){
					mailInfo.setToAddress(receiver);
					mailInfo.setAttachFileNames(new String[]{fileUrl});
					mailInfo.setSubject("每日寿命资材反馈");  
					
					SimpleMailSender sms = new SimpleMailSender();
	
//					mailInfo.setContent(getQualityInspectionPending(sqlResult).toString());
					mailInfo.setContent(getLifeManageMentHtml(sqlResult).toString());
					if(mailInfo.getContent() == null){
						mailSendHistory.setErrorType("邮件内容是空的");
						mailSendHistory.setSendState("false");
					}else{
						try {
							sms.sendHtmlMail(mailInfo);
							mailSendHistory.setSendState("true");
							//品质待检的db操作 每天定时扫描的db操作
							updateSendTimesAndDate(sqlResult,mailSendDate);
						} catch (Exception e) {
							if(StringUtils.isNoneEmpty(e.getMessage())) {
								log.info("每日寿命资材反馈看板发送邮件失败  e.getMessage() => " + e.getMessage());
							}
							if(e.getClass() == SendFailedException.class){
								mailSendHistory.setErrorType("收件人地址错误");
								mailSendHistory.setSendState("false");
							}else if(e.getClass() == AuthenticationFailedException.class){
								mailSendHistory.setErrorType("发件人用户名密码错误");
								mailSendHistory.setSendState("false");
							}else if (e.getClass() == MessagingException.class) {
								mailSendHistory.setErrorType("连接发件服务器失败");
								mailSendHistory.setSendState("false");
							}
						}
					}
				}else{
					mailSendHistory.setErrorType("收件人地址列表是空的");
					mailSendHistory.setSendState("false");
				}
				//Mail历史表
				updateMailSendHistory(mailSendHistory);
			}
		}
	}
	
	/**
	 * 设备在库List（每天6：00发送）
	 */
	public void sendYardStockedListMail() {
		
		log.info("开始执行堆场在库的邮件发送任务2  ==========> ");
		String siteName = "B20";
		String sql = "SELECT\r\n" + 
				"yd.SITENAME, \r\n" + 
				" yd.PONO,\r\n" + 
				" yd.RECEIVEREQUESTNAME,\r\n" + 
				" yd.DEVICE,\r\n" + 
				" yd.DEVICENAME,\r\n" + 
				" yd.CASENO,\r\n" + 
				" yd.DEPARTMENT,\r\n" + 
				" yd.UNPACKING,\r\n" + 
				" yd.LOCATIONNAME,\r\n" + 
				" yd.\"EXCEPTION\",\r\n" + 
				" yd.DESCRIPTION,\r\n" + 
				" yd.CARINFO, \r\n" + 
				" yd.\"LENGTH\",\r\n" + 
				" yd.WIDTH,\r\n" + 
				" yd.HEIGHT,\r\n" + 
				" yd.\"CROSS\",\r\n" + 
				" yd.NET,\r\n" + 
				" yd.STOCKUSER,\r\n" + 
				" yd.STOCKTIME,\r\n" + 
				" yd.THROUGH_STORAGE_YARD INTYPE," + 
				" yd.STOCKINTYPE \r\n" + 
				" FROM YARD_DEVICE yd\r\n" + 
				" WHERE SITENAME = ?\r\n" + 
				" AND STOCKSTATE = 'Stocked'";
		
		List<Map<String, Object>> sqlResult = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql,new Object[]{siteName});

		if(sqlResult != null && !sqlResult.isEmpty()){
			Date sendDate = new Date(); // mail send  time
			String mailSendDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(sendDate);
			
			String fileNameStr = "ZK" + new SimpleDateFormat("yyyyMMddHHmmsss").format(sendDate);
			
			String fileUrl = "/usr01/mwmsadm/attachment/" + fileNameStr + ".xls";
			//String fileUrl = "E:/mailData/" + fileNameStr + ".xls";
			//生成excel
			FileOutputStream fout;
			try {
				fout = new FileOutputStream(fileUrl);
				createYardStockListExcel(sqlResult,fout);
			} catch (WriteException e) {e.printStackTrace();
			} catch (IOException e) {e.printStackTrace();}
			MailSenderInfo mailInfo = new MailSenderInfo();
			
			MWMSMail mail = this.initMailSender();
			
			mailInfo.setMailServerHost(mail.getHost());
			mailInfo.setMailServerPort(mail.getPort());  
			mailInfo.setValidate(true);
			mailInfo.setUserName(mail.getUsername());
			mailInfo.setPassword(mail.getPassword());
			mailInfo.setFromAddress(mail.getFromAddress());
			
			MailSendHistory mailSendHistory = new MailSendHistory(); 
			
			mailSendHistory.setSendTime(mailSendDate);
			mailSendHistory.setInterfaceType("YardStockList");
			
		 	String[] receiver = getRecMailUsersForYard("YardStockList",siteName);
			if(receiver != null){
				mailInfo.setToAddress(receiver);
				mailInfo.setAttachFileNames(new String[]{fileUrl});
				mailInfo.setSubject("设备在库List");  
				
				SimpleMailSender sms = new SimpleMailSender();

//					mailInfo.setContent(getQualityInspectionPending(sqlResult).toString());
				//mailInfo.setContent(getLifeManageMentHtml(sqlResult).toString());
				mailInfo.setContent(getYardStockListManageMentHtml(sqlResult).toString());

				if(mailInfo.getContent() == null){
					mailSendHistory.setErrorType("邮件内容是空的");
					mailSendHistory.setSendState("false");
				}else{
					try {
						sms.sendHtmlMail(mailInfo);
						mailSendHistory.setSendState("true");
						//品质待检的db操作 每天定时扫描的db操作
						updateSendTimesAndDate(sqlResult,mailSendDate);
					} catch (Exception e) {
						if(StringUtils.isNoneEmpty(e.getMessage())) {
							log.info("设备在库List发送邮件失败  e.getMessage() => " + e.getMessage());
						}
						if(e.getClass() == SendFailedException.class){
							mailSendHistory.setErrorType("收件人地址错误");
							mailSendHistory.setSendState("false");
						}else if(e.getClass() == AuthenticationFailedException.class){
							mailSendHistory.setErrorType("发件人用户名密码错误");
							mailSendHistory.setSendState("false");
						}else if (e.getClass() == MessagingException.class) {
							mailSendHistory.setErrorType("连接发件服务器失败");
							mailSendHistory.setSendState("false");
						}
					}
				}
			}else{
				mailSendHistory.setErrorType("收件人地址列表是空的");
				mailSendHistory.setSendState("false");
			}
			//Mail历史表
			updateMailSendHistory(mailSendHistory);
		}
		
	}
	
	/**
	 * 设备入库List（每天6：00发送）
	 */
	public void sendYardStockInListMail() {
		
		log.info("开始执行堆场入库的邮件发送任务2  ==========> ");
		String siteName = "B20";
		String sql = "SELECT\r\n" + 
				"yd.SITENAME, \r\n" + 
				" yd.PONO,\r\n" + 
				" yd.RECEIVEREQUESTNAME,\r\n" + 
				" yd.DEVICE,\r\n" + 
				" yd.DEVICENAME,\r\n" + 
				" yd.CASENO,\r\n" + 
				" yd.DEPARTMENT,\r\n" + 
				" yd.UNPACKING,\r\n" + 
				" yd.LOCATIONNAME,\r\n" + 
				" yd.\"EXCEPTION\",\r\n" + 
				" yd.DESCRIPTION,\r\n" + 
				" yd.CARINFO, \r\n" + 
				" yd.\"LENGTH\",\r\n" + 
				" yd.WIDTH,\r\n" + 
				" yd.HEIGHT,\r\n" + 
				" yd.\"CROSS\",\r\n" + 
				" yd.NET,\r\n" + 
				" yd.STOCKUSER RECEIVEUSER,\r\n" + 
				" TO_CHAR(yd.STOCKTIME, 'YYYY-MM-DD') RECEIVETIME,\r\n" + 
				" yd.THROUGH_STORAGE_YARD INTYPE,\r\n" + 
				" yd.STOCKINTYPE" + 
				" FROM YARD_DEVICEHISTORY yd\r\n" + 
				" WHERE SITENAME = ?\r\n" + 
				" AND STOCKSTATE = 'Stocked'" + 
				" AND TRUNC(STOCKTIME) <= TRUNC(SYSDATE) + (6/24)" +
				" AND yd.LASTEVENTNAME IN ('YardDirectStockIn', 'ReturnStockIn', 'YardStockIn', 'ReturnYardStock', 'RemoveYardUn')";
		
		List<Map<String, Object>> sqlResult = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql,new Object[]{siteName});

		if(sqlResult != null && !sqlResult.isEmpty()){
			Date sendDate = new Date(); // mail send  time
			String mailSendDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(sendDate);
			
			String fileNameStr = "RK" + new SimpleDateFormat("yyyyMMddHHmmsss").format(sendDate);
			
			String fileUrl = "/usr01/mwmsadm/attachment/" + fileNameStr + ".xls";
			//String fileUrl = "E:/mailData/" + fileNameStr + ".xls";

			log.info("每日发送邮件 入库list fileUrl  => " + fileUrl);

			//生成excel
			FileOutputStream fout;
			try {
				fout = new FileOutputStream(fileUrl);
				createYardStockInListExcel(sqlResult,fout);
			} catch (WriteException e) {e.printStackTrace();
			} catch (IOException e) {e.printStackTrace();}
			MailSenderInfo mailInfo = new MailSenderInfo();
			
			MWMSMail mail = this.initMailSender();
			
			mailInfo.setMailServerHost(mail.getHost());
			mailInfo.setMailServerPort(mail.getPort());  
			mailInfo.setValidate(true);
			mailInfo.setUserName(mail.getUsername());
			mailInfo.setPassword(mail.getPassword());
			mailInfo.setFromAddress(mail.getFromAddress());
			
			MailSendHistory mailSendHistory = new MailSendHistory(); 
			
			mailSendHistory.setSendTime(mailSendDate);
			mailSendHistory.setInterfaceType("YardStockInList");
			
		 	String[] receiver = getRecMailUsersForYard("YardStockInList",siteName);
			if(receiver != null){
				mailInfo.setToAddress(receiver);
				mailInfo.setAttachFileNames(new String[]{fileUrl});
				mailInfo.setSubject("设备入库List");  
				
				SimpleMailSender sms = new SimpleMailSender();
				mailInfo.setContent(getYardStockInListManageMentHtml(sqlResult).toString());

				if(mailInfo.getContent() == null){
					mailSendHistory.setErrorType("邮件内容是空的");
					mailSendHistory.setSendState("false");
				}else{
					try {
						sms.sendHtmlMail(mailInfo);
						mailSendHistory.setSendState("true");
						//品质待检的db操作 每天定时扫描的db操作
						updateSendTimesAndDate(sqlResult,mailSendDate);
					} catch (Exception e) {
						if(StringUtils.isNoneEmpty(e.getMessage())) {
							log.info("设备入库List发送邮件失败  e.getMessage() => " + e.getMessage());
						}
						if(e.getClass() == SendFailedException.class){
							mailSendHistory.setErrorType("收件人地址错误");
							mailSendHistory.setSendState("false");
						}else if(e.getClass() == AuthenticationFailedException.class){
							mailSendHistory.setErrorType("发件人用户名密码错误");
							mailSendHistory.setSendState("false");
						}else if (e.getClass() == MessagingException.class) {
							mailSendHistory.setErrorType("连接发件服务器失败");
							mailSendHistory.setSendState("false");
						}
					}
				}
			}else{
				mailSendHistory.setErrorType("收件人地址列表是空的");
				mailSendHistory.setSendState("false");
			}
			//Mail历史表
			updateMailSendHistory(mailSendHistory);
		}
		
	}
	
	/**
	 * 设备出库List（每天6：00发送）
	 */
	public void sendYardStockOutListMail() {
		
		log.info("开始执行堆场出库的邮件发送任务2  ==========> ");
		String siteName = "B20";
		String sql = "SELECT yd.SITENAME, yd.PONO,\r\n" + 
				" yd.SHIPREQUESTNAME,\r\n" + 
				" yd.DEVICE,\r\n" + 
				" yd.DEVICENAME,\r\n" + 
				" yd.CASENO,\r\n" + 
				" yd.\"LENGTH\",\r\n" + 
				" yd.WIDTH,\r\n" + 
				" yd.HEIGHT,\r\n" + 
				" yd.\"CROSS\",\r\n" + 
				" yd.NET,\r\n" + 
				" yd.MOVEDATE,\r\n" + 
				" yd.MOVEENTRANCE,\r\n" + 
				" yd.MOVEORDER,\r\n" + 
				" yd.TRANSPORTREQUIRE,\r\n" + 
				" yd.DESCRIPTION,\r\n" + 
				" yd.LASTEVENTUSER,\r\n" + 
				" TO_CHAR(yd.LASTEVENTTIME, 'YYYY-MM-DD') LASTEVENTTIME,\r\n" + 
				" yd.THROUGH_STORAGE_YARD," + 
				" yd.STOCKINTYPE" + 
				" FROM YARD_DEVICEHISTORY yd\r\n" + 
				" WHERE SITENAME = ?" + 
				" AND STOCKSTATE = 'StockOut'\r\n" + 
				//" AND TRUNC(SHIPTIME) >= TRUNC(SYSDATE) - 1" + 
				" AND TRUNC(SHIPTIME) <= TRUNC(SYSDATE) + (6/24)";
		
		List<Map<String, Object>> sqlResult = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql,new Object[]{siteName});

		if(sqlResult != null && !sqlResult.isEmpty()){
			Date sendDate = new Date(); // mail send  time
			String mailSendDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(sendDate);
			String fileNameStr = "CK" + new SimpleDateFormat("yyyyMMddHHmmsss").format(sendDate);

			String fileUrl = "/usr01/mwmsadm/attachment/" + fileNameStr + ".xls";
			//String fileUrl = "E:/mailData/" + fileNameStr + ".xls";
			log.info("每日发送邮件设备出库List反馈  fileUrl  => " + fileUrl);

			//生成excel
			FileOutputStream fout;
			try {
				fout = new FileOutputStream(fileUrl);
				createYardStockOutListExcel(sqlResult,fout);
			} catch (WriteException e) {e.printStackTrace();
			} catch (IOException e) {e.printStackTrace();}
			MailSenderInfo mailInfo = new MailSenderInfo();
			
			MWMSMail mail = this.initMailSender();
			
			mailInfo.setMailServerHost(mail.getHost());
			mailInfo.setMailServerPort(mail.getPort());  
			mailInfo.setValidate(true);
			mailInfo.setUserName(mail.getUsername());
			mailInfo.setPassword(mail.getPassword());
			mailInfo.setFromAddress(mail.getFromAddress());
			
			MailSendHistory mailSendHistory = new MailSendHistory(); 
			
			mailSendHistory.setSendTime(mailSendDate);
			mailSendHistory.setInterfaceType("YardStockOutList");
			
		 	String[] receiver = getRecMailUsersForYard("YardStockOutList",siteName);
			if(receiver != null){
				mailInfo.setToAddress(receiver);
				mailInfo.setAttachFileNames(new String[]{fileUrl});
				mailInfo.setSubject("设备出库List");  
				
				SimpleMailSender sms = new SimpleMailSender();
				mailInfo.setContent(getYardStockOutListManageMentHtml(sqlResult).toString());

				if(mailInfo.getContent() == null){
					mailSendHistory.setErrorType("邮件内容是空的");
					mailSendHistory.setSendState("false");
				}else{
					try {
						sms.sendHtmlMail(mailInfo);
						mailSendHistory.setSendState("true");
						//品质待检的db操作 每天定时扫描的db操作
						updateSendTimesAndDate(sqlResult,mailSendDate);
					} catch (Exception e) {
						if(StringUtils.isNoneEmpty(e.getMessage())) {
							log.info("设备入库List发送邮件失败  e.getMessage() => " + e.getMessage());
						}
						if(e.getClass() == SendFailedException.class){
							mailSendHistory.setErrorType("收件人地址错误");
							mailSendHistory.setSendState("false");
						}else if(e.getClass() == AuthenticationFailedException.class){
							mailSendHistory.setErrorType("发件人用户名密码错误");
							mailSendHistory.setSendState("false");
						}else if (e.getClass() == MessagingException.class) {
							mailSendHistory.setErrorType("连接发件服务器失败");
							mailSendHistory.setSendState("false");
						}
					}
				}
			}else{
				mailSendHistory.setErrorType("收件人地址列表是空的");
				mailSendHistory.setSendState("false");
			}
			//Mail历史表
			updateMailSendHistory(mailSendHistory);
		}
		
	}
	
	/* 
	 * 6点库存查询功能
	 * */
	public void GetStorageForSixClock() {
		//String deleteSql = "DELETE FROM MATERIALPACKING_COPY";
	   	String copySql = "INSERT\r\n" + 
	   			"	INTO\r\n" + 
	   			"	MATERIALPACKING_COPY (SITENAME,\r\n" + 
	   			"	MATERIALPACKINGNAME,\r\n" + 
	   			"	MATERIALPACKINGTYPE,\r\n" + 
	   			"	CONTENTMATERIALTYPE,\r\n" + 
	   			"	MATERIALTYPE,\r\n" + 
	   			"	MATERIALSPECNAME,\r\n" + 
	   			"	SUPERMATERIALPACKINGNAME,\r\n" + 
	   			"	PACKINGGRADE,\r\n" + 
	   			"	SUBPACKINGQUANTITY,\r\n" + 
	   			"	MATERIALCREATEQUANTITY,\r\n" + 
	   			"	PACKINGSTATE,\r\n" + 
	   			"	OLDPACKINGSTATE,\r\n" + 
	   			"	STOCKSTATE,\r\n" + 
	   			"	OLDSTOCKSTATE,\r\n" + 
	   			"	HOLDSTATE,\r\n" + 
	   			"	AREANAME,\r\n" + 
	   			"	LOCATIONNAME,\r\n" + 
	   			"	MATERIALPROCESSGROUPNAME,\r\n" + 
	   			"	LASTEVENTNAME,\r\n" + 
	   			"	LASTEVENTTIMEKEY,\r\n" + 
	   			"	LASTEVENTTIME,\r\n" + 
	   			"	LASTEVENTUSER,\r\n" + 
	   			"	LASTEVENTCOMMENT,\r\n" + 
	   			"	CREATETIME,\r\n" + 
	   			"	CREATEUSER,\r\n" + 
	   			"	RECEIVEREQUESTNAME,\r\n" + 
	   			"	RECEIVEREQUESTDETAILNAME,\r\n" + 
	   			"	MAKEDATE,\r\n" + 
	   			"	RECEIVEACTNO,\r\n" + 
	   			"	RECEIVETIME,\r\n" + 
	   			"	RECEIVEUSER,\r\n" + 
	   			"	SHIPREQUESTNAME,\r\n" + 
	   			"	SHIPREQUESTDETAILNAME,\r\n" + 
	   			"	SHIPTIME,\r\n" + 
	   			"	SHIPUSER,\r\n" + 
	   			"	REASONCODETYPE,\r\n" + 
	   			"	REASONCODE,\r\n" + 
	   			"	ERPLOCATION,\r\n" + 
	   			"	ETCSTATE,\r\n" + 
	   			"	ERPFACTORY,\r\n" + 
	   			"	STOCKINTYPE,\r\n" + 
	   			"	OQARESULTSTATE,\r\n" + 
	   			"	OQARESULT,\r\n" + 
	   			"	CHARGE,\r\n" + 
	   			"	SUPPLIERNAME,\r\n" + 
	   			"	UNDOID,\r\n" + 
	   			"	UNDODETAILNAME,\r\n" + 
	   			"	ERPEVENTTIME,\r\n" + 
	   			"	NPFLAG,\r\n" + 
	   			"	ERPSTATE,\r\n" + 
	   			"	MESSAGEID,\r\n" + 
	   			"	OQAMETHOD,\r\n" + 
	   			"	REMARK,\r\n" + 
	   			"	IMPROVMENTFLAG,\r\n" + 
	   			"	EXPIRINGDATE,\r\n" + 
	   			"	PROCESSSTATE,\r\n" + 
	   			"	ETCOUTQUANTITY,\r\n" + 
	   			"	MESRECEIVEFLAG,\r\n" + 
	   			"	MESRECEIVETIME,\r\n" + 
	   			"	RECEIPTOR,\r\n" + 
	   			"	DURABLENAME,\r\n" + 
	   			"	OEMOQAFLAG,\r\n" + 
	   			"	MATERIALQUANTITY,\r\n" + 
	   			"	FIFOFLAG,\r\n" + 
	   			"	SPECIALFLAG,\r\n" +  
	   			"	STOCKDATEFLAG)\r\n" + 
	   			"	SELECT SITENAME,\r\n" + 
	   			"	MATERIALPACKINGNAME,\r\n" + 
	   			"	MATERIALPACKINGTYPE,\r\n" + 
	   			"	CONTENTMATERIALTYPE,\r\n" + 
	   			"	MATERIALTYPE,\r\n" + 
	   			"	MATERIALSPECNAME,\r\n" + 
	   			"	SUPERMATERIALPACKINGNAME,\r\n" + 
	   			"	PACKINGGRADE,\r\n" + 
	   			"	SUBPACKINGQUANTITY,\r\n" + 
	   			"	MATERIALCREATEQUANTITY,\r\n" + 
	   			"	PACKINGSTATE,\r\n" + 
	   			"	OLDPACKINGSTATE,\r\n" + 
	   			"	STOCKSTATE,\r\n" + 
	   			"	OLDSTOCKSTATE,\r\n" + 
	   			"	HOLDSTATE,\r\n" + 
	   			"	AREANAME,\r\n" + 
	   			"	LOCATIONNAME,\r\n" + 
	   			"	MATERIALPROCESSGROUPNAME,\r\n" + 
	   			"	LASTEVENTNAME,\r\n" + 
	   			"	LASTEVENTTIMEKEY,\r\n" + 
	   			"	LASTEVENTTIME,\r\n" + 
	   			"	LASTEVENTUSER,\r\n" + 
	   			"	LASTEVENTCOMMENT,\r\n" + 
	   			"	CREATETIME,\r\n" + 
	   			"	CREATEUSER,\r\n" + 
	   			"	RECEIVEREQUESTNAME,\r\n" + 
	   			"	RECEIVEREQUESTDETAILNAME,\r\n" + 
	   			"	MAKEDATE,\r\n" + 
	   			"	RECEIVEACTNO,\r\n" + 
	   			"	RECEIVETIME,\r\n" + 
	   			"	RECEIVEUSER,\r\n" + 
	   			"	SHIPREQUESTNAME,\r\n" + 
	   			"	SHIPREQUESTDETAILNAME,\r\n" + 
	   			"	SHIPTIME,\r\n" + 
	   			"	SHIPUSER,\r\n" + 
	   			"	REASONCODETYPE,\r\n" + 
	   			"	REASONCODE,\r\n" + 
	   			"	ERPLOCATION,\r\n" + 
	   			"	ETCSTATE,\r\n" + 
	   			"	ERPFACTORY,\r\n" + 
	   			"	STOCKINTYPE,\r\n" + 
	   			"	OQARESULTSTATE,\r\n" + 
	   			"	OQARESULT,\r\n" + 
	   			"	CHARGE,\r\n" + 
	   			"	SUPPLIERNAME,\r\n" + 
	   			"	UNDOID,\r\n" + 
	   			"	UNDODETAILNAME,\r\n" + 
	   			"	ERPEVENTTIME,\r\n" + 
	   			"	NPFLAG,\r\n" + 
	   			"	ERPSTATE,\r\n" + 
	   			"	MESSAGEID,\r\n" + 
	   			"	OQAMETHOD,\r\n" + 
	   			"	REMARK,\r\n" + 
	   			"	IMPROVMENTFLAG,\r\n" + 
	   			"	EXPIRINGDATE,\r\n" + 
	   			"	PROCESSSTATE,\r\n" + 
	   			"	ETCOUTQUANTITY,\r\n" + 
	   			"	MESRECEIVEFLAG,\r\n" + 
	   			"	MESRECEIVETIME,\r\n" + 
	   			"	RECEIPTOR,\r\n" + 
	   			"	DURABLENAME,\r\n" + 
	   			"	OEMOQAFLAG,\r\n" + 
	   			"	MATERIALQUANTITY,\r\n" + 
	   			"	FIFOFLAG,\r\n" + 
	   			"	SPECIALFLAG,\r\n" + 
	   			"	SYSDATE FROM MATERIALPACKING m WHERE m.STOCKSTATE = 'Stocked' ";
	   	//IDMFrameServiceProxy.getSqlTemplate().update(deleteSql);
	   	IDMFrameServiceProxy.getSqlTemplate().update(copySql);
	}
	
	
	/* 
	 * 堆场6点库存查询功能
	 * */
	public void GetYardStorageForSixClock() {
	   	String copySql = "INSERT\r\n" + 
	   			"	INTO\r\n" + 
	   			"	MATERIALPACKING_COPY (SITENAME,\r\n" + 
	   			"	MATERIALPACKINGNAME,\r\n" + 
	   			"	MATERIALPACKINGTYPE,\r\n" + 
	   			"	CONTENTMATERIALTYPE,\r\n" + 
	   			"	MATERIALTYPE,\r\n" + 
	   			"	MATERIALSPECNAME,\r\n" + 
	   			"	SUPERMATERIALPACKINGNAME,\r\n" + 
	   			"	PACKINGGRADE,\r\n" + 
	   			"	SUBPACKINGQUANTITY,\r\n" + 
	   			"	MATERIALCREATEQUANTITY,\r\n" + 
	   			"	PACKINGSTATE,\r\n" + 
	   			"	OLDPACKINGSTATE,\r\n" + 
	   			"	STOCKSTATE,\r\n" + 
	   			"	OLDSTOCKSTATE,\r\n" + 
	   			"	HOLDSTATE,\r\n" + 
	   			"	AREANAME,\r\n" + 
	   			"	LOCATIONNAME,\r\n" + 
	   			"	MATERIALPROCESSGROUPNAME,\r\n" + 
	   			"	LASTEVENTNAME,\r\n" + 
	   			"	LASTEVENTTIMEKEY,\r\n" + 
	   			"	LASTEVENTTIME,\r\n" + 
	   			"	LASTEVENTUSER,\r\n" + 
	   			"	LASTEVENTCOMMENT,\r\n" + 
	   			"	CREATETIME,\r\n" + 
	   			"	CREATEUSER,\r\n" + 
	   			"	RECEIVEREQUESTNAME,\r\n" + 
	   			"	RECEIVEREQUESTDETAILNAME,\r\n" + 
	   			"	MAKEDATE,\r\n" + 
	   			"	RECEIVEACTNO,\r\n" + 
	   			"	RECEIVETIME,\r\n" + 
	   			"	RECEIVEUSER,\r\n" + 
	   			"	SHIPREQUESTNAME,\r\n" + 
	   			"	SHIPREQUESTDETAILNAME,\r\n" + 
	   			"	SHIPTIME,\r\n" + 
	   			"	SHIPUSER,\r\n" + 
	   			"	REASONCODETYPE,\r\n" + 
	   			"	REASONCODE,\r\n" + 
	   			"	ERPLOCATION,\r\n" + 
	   			"	ETCSTATE,\r\n" + 
	   			"	ERPFACTORY,\r\n" + 
	   			"	STOCKINTYPE,\r\n" + 
	   			"	OQARESULTSTATE,\r\n" + 
	   			"	OQARESULT,\r\n" + 
	   			"	CHARGE,\r\n" + 
	   			"	SUPPLIERNAME,\r\n" + 
	   			"	UNDOID,\r\n" + 
	   			"	UNDODETAILNAME,\r\n" + 
	   			"	ERPEVENTTIME,\r\n" + 
	   			"	NPFLAG,\r\n" + 
	   			"	ERPSTATE,\r\n" + 
	   			"	MESSAGEID,\r\n" + 
	   			"	OQAMETHOD,\r\n" + 
	   			"	REMARK,\r\n" + 
	   			"	IMPROVMENTFLAG,\r\n" + 
	   			"	EXPIRINGDATE,\r\n" + 
	   			"	PROCESSSTATE,\r\n" + 
	   			"	ETCOUTQUANTITY,\r\n" + 
	   			"	MESRECEIVEFLAG,\r\n" + 
	   			"	MESRECEIVETIME,\r\n" + 
	   			"	RECEIPTOR,\r\n" + 
	   			"	DURABLENAME,\r\n" + 
	   			"	OEMOQAFLAG,\r\n" + 
	   			"	MATERIALQUANTITY,\r\n" + 
	   			"	FIFOFLAG,\r\n" + 
	   			"	SPECIALFLAG,\r\n" +  
	   			"	STOCKDATEFLAG)\r\n" + 
	   			"	SELECT SITENAME,\r\n" + 
	   			"	MATERIALPACKINGNAME,\r\n" + 
	   			"	MATERIALPACKINGTYPE,\r\n" + 
	   			"	CONTENTMATERIALTYPE,\r\n" + 
	   			"	MATERIALTYPE,\r\n" + 
	   			"	MATERIALSPECNAME,\r\n" + 
	   			"	SUPERMATERIALPACKINGNAME,\r\n" + 
	   			"	PACKINGGRADE,\r\n" + 
	   			"	SUBPACKINGQUANTITY,\r\n" + 
	   			"	MATERIALCREATEQUANTITY,\r\n" + 
	   			"	PACKINGSTATE,\r\n" + 
	   			"	OLDPACKINGSTATE,\r\n" + 
	   			"	STOCKSTATE,\r\n" + 
	   			"	OLDSTOCKSTATE,\r\n" + 
	   			"	HOLDSTATE,\r\n" + 
	   			"	AREANAME,\r\n" + 
	   			"	LOCATIONNAME,\r\n" + 
	   			"	MATERIALPROCESSGROUPNAME,\r\n" + 
	   			"	LASTEVENTNAME,\r\n" + 
	   			"	LASTEVENTTIMEKEY,\r\n" + 
	   			"	LASTEVENTTIME,\r\n" + 
	   			"	LASTEVENTUSER,\r\n" + 
	   			"	LASTEVENTCOMMENT,\r\n" + 
	   			"	CREATETIME,\r\n" + 
	   			"	CREATEUSER,\r\n" + 
	   			"	RECEIVEREQUESTNAME,\r\n" + 
	   			"	RECEIVEREQUESTDETAILNAME,\r\n" + 
	   			"	MAKEDATE,\r\n" + 
	   			"	RECEIVEACTNO,\r\n" + 
	   			"	RECEIVETIME,\r\n" + 
	   			"	RECEIVEUSER,\r\n" + 
	   			"	SHIPREQUESTNAME,\r\n" + 
	   			"	SHIPREQUESTDETAILNAME,\r\n" + 
	   			"	SHIPTIME,\r\n" + 
	   			"	SHIPUSER,\r\n" + 
	   			"	REASONCODETYPE,\r\n" + 
	   			"	REASONCODE,\r\n" + 
	   			"	ERPLOCATION,\r\n" + 
	   			"	ETCSTATE,\r\n" + 
	   			"	ERPFACTORY,\r\n" + 
	   			"	STOCKINTYPE,\r\n" + 
	   			"	OQARESULTSTATE,\r\n" + 
	   			"	OQARESULT,\r\n" + 
	   			"	CHARGE,\r\n" + 
	   			"	SUPPLIERNAME,\r\n" + 
	   			"	UNDOID,\r\n" + 
	   			"	UNDODETAILNAME,\r\n" + 
	   			"	ERPEVENTTIME,\r\n" + 
	   			"	NPFLAG,\r\n" + 
	   			"	ERPSTATE,\r\n" + 
	   			"	MESSAGEID,\r\n" + 
	   			"	OQAMETHOD,\r\n" + 
	   			"	REMARK,\r\n" + 
	   			"	IMPROVMENTFLAG,\r\n" + 
	   			"	EXPIRINGDATE,\r\n" + 
	   			"	PROCESSSTATE,\r\n" + 
	   			"	ETCOUTQUANTITY,\r\n" + 
	   			"	MESRECEIVEFLAG,\r\n" + 
	   			"	MESRECEIVETIME,\r\n" + 
	   			"	RECEIPTOR,\r\n" + 
	   			"	DURABLENAME,\r\n" + 
	   			"	OEMOQAFLAG,\r\n" + 
	   			"	MATERIALQUANTITY,\r\n" + 
	   			"	FIFOFLAG,\r\n" + 
	   			"	SPECIALFLAG,\r\n" + 
	   			"	SYSDATE FROM MATERIALPACKING m WHERE m.STOCKSTATE = 'Stocked' ";
	   	//IDMFrameServiceProxy.getSqlTemplate().update(deleteSql);
	   	IDMFrameServiceProxy.getSqlTemplate().update(copySql);
	}
	
	public void createMaterialLifeExcel(List<Map<String, Object>> sqlResult,OutputStream os) throws WriteException,IOException {
		WritableWorkbook workbook = Workbook.createWorkbook(os);
		WritableSheet sheet = workbook.createSheet("First Sheet", 0);

		sheet.mergeCells(0, 0, 9, 0);//添加合并单元格，第一个参数是起始列，第二个参数是起始行，第三个参数是终止列，第四个参数是终止行
		WritableFont bold = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD);//设置字体种类和黑体显示,字体为Arial,字号大小为10,采用黑体显示
		WritableCellFormat titleFormate = new WritableCellFormat(bold);//生成一个单元格样式控制对象
		titleFormate.setBorder(Border.ALL, BorderLineStyle.THIN);
		titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.TOP);//单元格的内容垂直方向居中
		titleFormate.setWrap(true);
		sheet.addCell(new Label(0,0,"您好！\n\n目前有以下材料已过期/一个月内到期，请及时检查。谢谢\n",titleFormate));

		sheet.setRowView(0, 1000, false);

		sheet.addCell(new Label(0,1,"序号"));
		sheet.addCell(new Label(1,1,"工厂"));
		sheet.addCell(new Label(2,1,"物料号"));
		sheet.addCell(new Label(3,1,"物料描述"));
		sheet.addCell(new Label(4,1,"抬头文本"));
		sheet.addCell(new Label(5,1,"供应商"));
		sheet.addCell(new Label(6,1,"到货数量"));
		sheet.addCell(new Label(7,1,"单位"));
		sheet.addCell(new Label(8,1,"ERP工厂"));
		sheet.addCell(new Label(9,1,"ERP库位"));
		sheet.addCell(new Label(10,1,"货位地"));
		sheet.addCell(new Label(11,1,"批次"));
		sheet.addCell(new Label(12,1,"生产日期"));
		sheet.addCell(new Label(13,1,"入库日期"));
		sheet.addCell(new Label(14,1,"到期剩余"));

		if(sqlResult != null && !sqlResult.isEmpty())
			for(int i = 0 ; i<sqlResult.size(); i++){
				Map<String, Object> tmp = sqlResult.get(i);

				sheet.addCell(new Label(0,2+i,(i+1)+""));
				sheet.addCell(new Label(1,2+i,(tmp.get("SITENAME")!=null?tmp.get("SITENAME").toString():"")));
				sheet.addCell(new Label(2,2+i,(tmp.get("MATERIALSPECNAME")!=null?tmp.get("MATERIALSPECNAME").toString():"")));
				sheet.addCell(new Label(3,2+i,(tmp.get("DESCRIPTION") != null?tmp.get("DESCRIPTION").toString():"")));
				sheet.addCell(new Label(4,2+i,(tmp.get("RECEIVEACTNO")!= null?tmp.get("RECEIVEACTNO").toString():"")));
				sheet.addCell(new Label(5,2+i,(tmp.get("SUPPLIERNAME")!= null?tmp.get("SUPPLIERNAME").toString():"")));
				sheet.addCell(new Label(6,2+i,(tmp.get("MATERIALQUANTITY")!= null?tmp.get("MATERIALQUANTITY").toString():"")));
				sheet.addCell(new Label(7,2+i,(tmp.get("MATERIALUNIT")!= null?tmp.get("MATERIALUNIT").toString():"")));
				sheet.addCell(new Label(8,2+i,(tmp.get("ERPFACTORY")!= null?tmp.get("ERPFACTORY").toString():"")));
				sheet.addCell(new Label(9,2+i,(tmp.get("ERPLOCATION")!= null?tmp.get("ERPLOCATION").toString():"")));
				sheet.addCell(new Label(10,2+i,(tmp.get("LOCATIONNAME")!= null?tmp.get("LOCATIONNAME").toString():"")));
				sheet.addCell(new Label(11,2+i,(tmp.get("MATERIALPROCESSGROUPNAME")!= null?tmp.get("MATERIALPROCESSGROUPNAME").toString():"")));
				sheet.addCell(new Label(12,2+i,(tmp.get("MAKEDATE")!= null?tmp.get("MAKEDATE").toString():"")));
				sheet.addCell(new Label(13,2+i,(tmp.get("RECEIVETIME")!= null?tmp.get("RECEIVETIME").toString():"")));
				sheet.addCell(new Label(14,2+i,(tmp.get("DAYS")!= null?tmp.get("DAYS").toString():"")));
			}
		//把创建的内容写入到输出流中，并关闭输出流
		workbook.write();
		workbook.close();
		os.close();

	}
	
	// 堆场在库
	public void createYardStockListExcel(List<Map<String, Object>> sqlResult,OutputStream os) throws WriteException,IOException {
		WritableWorkbook workbook = Workbook.createWorkbook(os);
		WritableSheet sheet = workbook.createSheet("First Sheet", 0);

		sheet.mergeCells(0, 0, 9, 0);//添加合并单元格，第一个参数是起始列，第二个参数是起始行，第三个参数是终止列，第四个参数是终止行
		WritableFont bold = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD);//设置字体种类和黑体显示,字体为Arial,字号大小为10,采用黑体显示
		WritableCellFormat titleFormate = new WritableCellFormat(bold);//生成一个单元格样式控制对象
		titleFormate.setBorder(Border.ALL, BorderLineStyle.THIN);
		titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.TOP);//单元格的内容垂直方向居中
		titleFormate.setWrap(true);
		sheet.addCell(new Label(0,0,"您好！\n\n设备在库List，请查收。谢谢\n",titleFormate));

		sheet.setRowView(0, 1000, false);

		sheet.addCell(new Label(0,1,"序号"));
		sheet.addCell(new Label(1,1,"工厂"));
		sheet.addCell(new Label(2,1,"PONO."));
		sheet.addCell(new Label(3,1,"入库单号"));
		sheet.addCell(new Label(4,1,"设备编号"));
		sheet.addCell(new Label(5,1,"设备名称"));
		sheet.addCell(new Label(6,1,"箱号"));
		sheet.addCell(new Label(7,1,"使用部门"));
		sheet.addCell(new Label(8,1,"是否掏箱"));
		sheet.addCell(new Label(9,1,"存放位置"));
		sheet.addCell(new Label(10,1,"异常情况"));
		sheet.addCell(new Label(11,1,"备注"));
		sheet.addCell(new Label(12,1,"车辆信息"));
		sheet.addCell(new Label(13,1,"Length(包装尺寸cm)"));
		sheet.addCell(new Label(14,1,"Width(包装尺寸cm)"));
		sheet.addCell(new Label(15,1,"Height(包装尺寸cm)"));
		sheet.addCell(new Label(16,1,"毛重(kg)"));
		sheet.addCell(new Label(17,1,"净重(kg)"));
		sheet.addCell(new Label(18,1,"入库人"));
		sheet.addCell(new Label(19,1,"入库时间"));
		sheet.addCell(new Label(20,1,"堆场/直搬"));
		sheet.addCell(new Label(21,1,"入库类型"));


		if(sqlResult != null && !sqlResult.isEmpty())
			for(int i = 0 ; i<sqlResult.size(); i++){
				Map<String, Object> tmp = sqlResult.get(i);

				sheet.addCell(new Label(0,2+i,(i+1)+""));
				sheet.addCell(new Label(1,2+i,(tmp.get("SITENAME")!=null?tmp.get("SITENAME").toString():"")));
				sheet.addCell(new Label(2,2+i,(tmp.get("PONO")!=null?tmp.get("PONO").toString():"")));
				sheet.addCell(new Label(3,2+i,(tmp.get("RECEIVEREQUESTNAME") != null?tmp.get("RECEIVEREQUESTNAME").toString():"")));
				sheet.addCell(new Label(4,2+i,(tmp.get("DEVICE")!= null?tmp.get("DEVICE").toString():"")));
				sheet.addCell(new Label(5,2+i,(tmp.get("DEVICENAME")!= null?tmp.get("DEVICENAME").toString():"")));
				sheet.addCell(new Label(6,2+i,(tmp.get("CASENO")!= null?tmp.get("CASENO").toString():"")));
				sheet.addCell(new Label(7,2+i,(tmp.get("DEPARTMENT")!= null?tmp.get("DEPARTMENT").toString():"")));
				sheet.addCell(new Label(8,2+i,(tmp.get("UNPACKING")!= null?tmp.get("UNPACKING").toString():"")));
				sheet.addCell(new Label(9,2+i,(tmp.get("LOCATIONNAME")!= null?tmp.get("LOCATIONNAME").toString():"")));
				sheet.addCell(new Label(10,2+i,(tmp.get("EXCEPTION")!= null?tmp.get("EXCEPTION").toString():"")));
				sheet.addCell(new Label(11,2+i,(tmp.get("DESCRIPTION")!= null?tmp.get("DESCRIPTION").toString():"")));
				sheet.addCell(new Label(12,2+i,(tmp.get("CARINFO")!= null?tmp.get("CARINFO").toString():"")));
				sheet.addCell(new Label(13,2+i,(tmp.get("LENGTH")!= null?tmp.get("LENGTH").toString():"")));			
				sheet.addCell(new Label(14,2+i,(tmp.get("WIDTH")!= null?tmp.get("WIDTH").toString():"")));
				sheet.addCell(new Label(15,2+i,(tmp.get("HEIGHT")!= null?tmp.get("HEIGHT").toString():"")));
				sheet.addCell(new Label(16,2+i,(tmp.get("NET")!= null?tmp.get("NET").toString():"")));
				sheet.addCell(new Label(17,2+i,(tmp.get("CROSS")!= null?tmp.get("CROSS").toString():"")));
				sheet.addCell(new Label(18,2+i,(tmp.get("RECEIVEUSER")!= null?tmp.get("RECEIVEUSER").toString():"")));
				sheet.addCell(new Label(19,2+i,(tmp.get("RECEIVETIME")!= null?tmp.get("RECEIVETIME").toString():"")));
				sheet.addCell(new Label(20,2+i,(tmp.get("INTYPE")!= null?tmp.get("INTYPE").toString():"")));
				sheet.addCell(new Label(21,2+i,(tmp.get("STOCKINTYPE")!= null?tmp.get("STOCKINTYPE").toString():"")));
			}
		//把创建的内容写入到输出流中，并关闭输出流
		workbook.write();
		workbook.close();
		os.close();

	}
	// 堆场入库
	public void createYardStockInListExcel(List<Map<String, Object>> sqlResult,OutputStream os) throws WriteException,IOException {
		WritableWorkbook workbook = Workbook.createWorkbook(os);
		WritableSheet sheet = workbook.createSheet("First Sheet", 0);

		sheet.mergeCells(0, 0, 9, 0);//添加合并单元格，第一个参数是起始列，第二个参数是起始行，第三个参数是终止列，第四个参数是终止行
		WritableFont bold = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD);//设置字体种类和黑体显示,字体为Arial,字号大小为10,采用黑体显示
		WritableCellFormat titleFormate = new WritableCellFormat(bold);//生成一个单元格样式控制对象
		titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.TOP);//单元格的内容垂直方向居中
		titleFormate.setWrap(true);
		sheet.addCell(new Label(0,0,"您好！\n\n设备入库List，请查收。谢谢\n",titleFormate));

		sheet.setRowView(0, 1000, false);

		sheet.addCell(new Label(0,1,"序号"));
		sheet.addCell(new Label(1,1,"工厂"));
		sheet.addCell(new Label(2,1,"PONO."));
		sheet.addCell(new Label(3,1,"入库单号"));
		sheet.addCell(new Label(4,1,"设备编号"));
		sheet.addCell(new Label(5,1,"设备名称"));
		//sheet.addCell(new Label(6,1,"库存状态"));
		sheet.addCell(new Label(6,1,"箱号"));
		sheet.addCell(new Label(7,1,"使用部门"));
		sheet.addCell(new Label(8,1,"是否掏箱"));
		sheet.addCell(new Label(9,1,"存放位置"));
		sheet.addCell(new Label(10,1,"异常情况"));
		sheet.addCell(new Label(11,1,"备注"));
		sheet.addCell(new Label(12,1,"车辆信息"));
		sheet.addCell(new Label(13,1,"Length(包装尺寸cm)"));
		sheet.addCell(new Label(14,1,"Width(包装尺寸cm)"));
		sheet.addCell(new Label(15,1,"Height(包装尺寸cm)"));
		sheet.addCell(new Label(16,1,"毛重(kg)"));
		sheet.addCell(new Label(17,1,"净重(kg)"));
		sheet.addCell(new Label(18,1,"入库人"));
		sheet.addCell(new Label(19,1,"入库时间"));
		sheet.addCell(new Label(20,1,"堆场/直搬"));
		sheet.addCell(new Label(21,1,"入库类型"));


		if(sqlResult != null && !sqlResult.isEmpty())
			for(int i = 0 ; i<sqlResult.size(); i++){
				Map<String, Object> tmp = sqlResult.get(i);

				sheet.addCell(new Label(0,2+i,(i+1)+""));
				sheet.addCell(new Label(1,2+i,(tmp.get("SITENAME")!=null?tmp.get("SITENAME").toString():"")));
				sheet.addCell(new Label(2,2+i,(tmp.get("PONO")!=null?tmp.get("PONO").toString():"")));
				sheet.addCell(new Label(3,2+i,(tmp.get("RECEIVEREQUESTNAME") != null?tmp.get("RECEIVEREQUESTNAME").toString():"")));
				sheet.addCell(new Label(4,2+i,(tmp.get("DEVICE")!= null?tmp.get("DEVICE").toString():"")));
				sheet.addCell(new Label(5,2+i,(tmp.get("DEVICENAME")!= null?tmp.get("DEVICENAME").toString():"")));
				sheet.addCell(new Label(6,2+i,(tmp.get("CASENO")!= null?tmp.get("CASENO").toString():"")));
				sheet.addCell(new Label(7,2+i,(tmp.get("DEPARTMENT")!= null?tmp.get("DEPARTMENT").toString():"")));
				sheet.addCell(new Label(8,2+i,(tmp.get("UNPACKING")!= null?tmp.get("UNPACKING").toString():"")));
				sheet.addCell(new Label(9,2+i,(tmp.get("LOCATIONNAME")!= null?tmp.get("LOCATIONNAME").toString():"")));
				sheet.addCell(new Label(10,2+i,(tmp.get("EXCEPTION")!= null?tmp.get("EXCEPTION").toString():"")));
				sheet.addCell(new Label(11,2+i,(tmp.get("DESCRIPTION")!= null?tmp.get("DESCRIPTION").toString():"")));
				sheet.addCell(new Label(12,2+i,(tmp.get("CARINFO")!= null?tmp.get("CARINFO").toString():"")));
				sheet.addCell(new Label(13,2+i,(tmp.get("LENGTH")!= null?tmp.get("LENGTH").toString():"")));
				sheet.addCell(new Label(14,2+i,(tmp.get("WIDTH")!= null?tmp.get("WIDTH").toString():"")));
				sheet.addCell(new Label(15,2+i,(tmp.get("HEIGHT")!= null?tmp.get("HEIGHT").toString():"")));
				sheet.addCell(new Label(16,2+i,(tmp.get("NET")!= null?tmp.get("NET").toString():"")));
				sheet.addCell(new Label(17,2+i,(tmp.get("CROSS")!= null?tmp.get("CROSS").toString():"")));
				sheet.addCell(new Label(18,2+i,(tmp.get("RECEIVEUSER")!= null?tmp.get("RECEIVEUSER").toString():"")));
				sheet.addCell(new Label(19,2+i,(tmp.get("RECEIVETIME")!= null?tmp.get("RECEIVETIME").toString():"")));
				sheet.addCell(new Label(20,2+i,(tmp.get("INTYPE")!= null?tmp.get("INTYPE").toString():"")));
				sheet.addCell(new Label(21,2+i,(tmp.get("STOCKINTYPE")!= null?tmp.get("STOCKINTYPE").toString():"")));
			}
		//把创建的内容写入到输出流中，并关闭输出流
		workbook.write();
		workbook.close();
		os.close();

	}
	
	// 堆场出库
	public void createYardStockOutListExcel(List<Map<String, Object>> sqlResult,OutputStream os) throws WriteException,IOException {
		WritableWorkbook workbook = Workbook.createWorkbook(os);
		WritableSheet sheet = workbook.createSheet("First Sheet", 0);

		sheet.mergeCells(0, 0, 9, 0);//添加合并单元格，第一个参数是起始列，第二个参数是起始行，第三个参数是终止列，第四个参数是终止行
		WritableFont bold = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD);//设置字体种类和黑体显示,字体为Arial,字号大小为10,采用黑体显示
		WritableCellFormat titleFormate = new WritableCellFormat(bold);//生成一个单元格样式控制对象
		titleFormate.setBorder(Border.ALL, BorderLineStyle.THIN);
		titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.TOP);//单元格的内容垂直方向居中
		titleFormate.setWrap(true);
		sheet.addCell(new Label(0,0,"您好！\n\n设备出库List，请查收。谢谢\n",titleFormate));

		sheet.setRowView(0, 1000, false);

		sheet.addCell(new Label(0,1,"序号"));
		sheet.addCell(new Label(1,1,"工厂"));
		sheet.addCell(new Label(2,1,"PONO."));
		sheet.addCell(new Label(3,1,"提货单号"));
		sheet.addCell(new Label(4,1,"设备编号"));
		sheet.addCell(new Label(5,1,"设备名称"));
		sheet.addCell(new Label(6,1,"CaseNo"));
		sheet.addCell(new Label(7,1,"长度"));
		sheet.addCell(new Label(8,1,"宽度"));
		sheet.addCell(new Label(9,1,"高度"));
		sheet.addCell(new Label(10,1,"毛重"));
		sheet.addCell(new Label(11,1,"净重"));
		sheet.addCell(new Label(12,1,"搬入日期"));
		sheet.addCell(new Label(13,1,"搬入口"));
		sheet.addCell(new Label(14,1,"搬入顺序"));
		sheet.addCell(new Label(15,1,"运输要求"));
		sheet.addCell(new Label(16,1,"备注"));
		sheet.addCell(new Label(17,1,"出库人"));
		sheet.addCell(new Label(18,1,"出库时间"));
		sheet.addCell(new Label(19,1,"堆场/直搬"));
		sheet.addCell(new Label(20,1,"出库类型"));



		if(sqlResult != null && !sqlResult.isEmpty())
			for(int i = 0 ; i<sqlResult.size(); i++){
				Map<String, Object> tmp = sqlResult.get(i);

				sheet.addCell(new Label(0,2+i,(i+1)+""));
				sheet.addCell(new Label(1,2+i,(tmp.get("SITENAME")!=null?tmp.get("SITENAME").toString():"")));
				sheet.addCell(new Label(2,2+i,(tmp.get("PONO")!=null?tmp.get("PONO").toString():"")));
				sheet.addCell(new Label(3,2+i,(tmp.get("SHIPREQUESTNAME") != null?tmp.get("SHIPREQUESTNAME").toString():"")));
				sheet.addCell(new Label(4,2+i,(tmp.get("DEVICE")!= null?tmp.get("DEVICE").toString():"")));
				sheet.addCell(new Label(5,2+i,(tmp.get("DEVICENAME")!= null?tmp.get("DEVICENAME").toString():"")));
				sheet.addCell(new Label(6,2+i,(tmp.get("CASENO")!= null?tmp.get("CASENO").toString():"")));
				sheet.addCell(new Label(7,2+i,(tmp.get("LENGTH")!= null?tmp.get("LENGTH").toString():"")));
				sheet.addCell(new Label(8,2+i,(tmp.get("WIDTH")!= null?tmp.get("WIDTH").toString():"")));
				sheet.addCell(new Label(9,2+i,(tmp.get("HEIGHT")!= null?tmp.get("HEIGHT").toString():"")));
				sheet.addCell(new Label(10,2+i,(tmp.get("NET")!= null?tmp.get("NET").toString():"")));
				sheet.addCell(new Label(11,2+i,(tmp.get("CROSS")!= null?tmp.get("CROSS").toString():"")));
				sheet.addCell(new Label(12,2+i,(tmp.get("MOVEDATE")!= null?tmp.get("MOVEDATE").toString():"")));
				sheet.addCell(new Label(13,2+i,(tmp.get("MOVEENTRANCE")!= null?tmp.get("MOVEENTRANCE").toString():"")));
				sheet.addCell(new Label(14,2+i,(tmp.get("MOVEORDER")!= null?tmp.get("MOVEORDER").toString():"")));
				sheet.addCell(new Label(15,2+i,(tmp.get("TRANSPORTREQUIRE")!= null?tmp.get("TRANSPORTREQUIRE").toString():"")));
				sheet.addCell(new Label(16,2+i,(tmp.get("DESCRIPTION")!= null?tmp.get("DESCRIPTION").toString():"")));
				sheet.addCell(new Label(17,2+i,(tmp.get("LASTEVENTUSER")!= null?tmp.get("LASTEVENTUSER").toString():"")));
				sheet.addCell(new Label(18,2+i,(tmp.get("LASTEVENTTIME")!= null?tmp.get("LASTEVENTTIME").toString():"")));
				sheet.addCell(new Label(19,2+i,(tmp.get("THROUGH_STORAGE_YARD")!= null?tmp.get("THROUGH_STORAGE_YARD").toString():"")));
				sheet.addCell(new Label(20,2+i,(tmp.get("STOCKINTYPE")!= null?tmp.get("STOCKINTYPE").toString():"")));

			}
		//把创建的内容写入到输出流中，并关闭输出流
		workbook.write();
		workbook.close();
		os.close();

	}
	
	/**
	 * 每日质检看板邮件（每天凌晨0：00发送库存为质检状态的材料）
	 */
	public void sendPreOQAReport() {
		
		log.info("开始执行材料库存为质检状态的邮件发送任务2  ==========> ");
		
		List<Map<String, Object>> getSiteName = getRecMailUsersSiteName("SendPreOQAReport");//获取工厂名
		for(int i=0;i<getSiteName.size();i++)
		{
			String siteName=getSiteName.get(i).get("SITENAME").toString();
 
			String dbSql  =  "SELECT B.MATERIALSPECNAME"
					+ ",B.MATERIALPACKINGNAME"
					+ ",B.SITENAME"
					+ ",A.DESC_CN AS DESCRIPTION"
					+ ",B.ERPLOCATION"
					+ ",B.ERPFACTORY"
					+ ",B.LOCATIONNAME"
					+ ",B.OQARESULTSTATE"
					+ ",B.MATERIALQUANTITY"
					+ ",A.MATERIALUNIT"
					+ ",TO_CHAR (B.MAKEDATE, 'yyyy-mm-dd hh:mm:ss') AS MAKEDATE"
					+ ",TO_CHAR (B.RECEIVETIME, 'yyyy-mm-dd hh:mm:ss') AS RECEIVETIME"
					+ ",TO_CHAR (B.EXPIRINGDATE, 'yyyy-mm-dd hh:mm:ss') AS EXPIRATIONDAY"
					+ ",Round(TO_NUMBER(EXPIRINGDATE - TO_DATE(TO_CHAR(SYSDATE,'yyyy-MM-dd'),'yyyy-MM-dd')),0) AS DAYS"
					+ ",Round(TO_NUMBER(TO_DATE(TO_CHAR(SYSDATE,'yyyy-MM-dd'),'yyyy-MM-dd') - RECEIVETIME),0) AS STOCKDAYS"
					+ ",B.CHARGE AS MATERIALPROCESSGROUPNAME "
					+ "FROM MATERIALPACKING B LEFT JOIN MATERIALSPEC A ON B.MATERIALSPECNAME = A.MATERIALSPECNAME "
					+ "WHERE B.SITENAME = ? "
					+ "AND B.OQARESULTSTATE = 'PREOQA' "
					+ "AND B.STOCKSTATE = 'Stocked' "
					+ "AND B.PACKINGSTATE = 'Released' "
					+ "AND B.MATERIALQUANTITY > 0 "
//					+ "ORDER BY DAYS DESC";
					+ "ORDER BY RECEIVETIME";
			
			List<Map<String, Object>> sqlResult = IDMFrameServiceProxy.getSqlTemplate().queryForList(dbSql,new Object[]{siteName});
	
			if(sqlResult != null && !sqlResult.isEmpty()){
				Date sendDate = new Date(); // mail send  time
				String mailSendDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(sendDate);
				
//				测试服务器
//				createDir("/usr01/wmstst/attachment");
//				String fileUrl ="/usr01/wmstst/attachment/" + new SimpleDateFormat("yyyyMMddHHmmsss").format(sendDate) + ".xls";
				
//				量产服务器
//				createDir("/wmsap/wmsadm/attachment");
				String fileUrl ="/wmsap/wmsadm/attachment/" + new SimpleDateFormat("yyyyMMddHHmmsss").format(sendDate) + ".xls";
				
				log.info("每日发送邮件附件位置 每日质检看板 fileUrl  => " + fileUrl);
				
				//生成excel
				FileOutputStream fout;
				try {
					fout = new FileOutputStream(fileUrl);
					createPreOQAExcel(sqlResult,fout);
				} catch (WriteException e) {e.printStackTrace();
				} catch (IOException e) {e.printStackTrace();}
				
				MailSenderInfo mailInfo = new MailSenderInfo();
				
				MWMSMail mail = this.initMailSender();
				
				mailInfo.setMailServerHost(mail.getHost());
				mailInfo.setMailServerPort(mail.getPort());  
				mailInfo.setValidate(true);
				mailInfo.setUserName(mail.getUsername());
				mailInfo.setPassword(mail.getPassword());
				mailInfo.setFromAddress(mail.getFromAddress());
				
				MailSendHistory mailSendHistory = new MailSendHistory(); 
				
				mailSendHistory.setSendTime(mailSendDate);
				mailSendHistory.setInterfaceType("SendPreOQAReport");
				
			 	String[] receiver = getRecMailUsers("SendPreOQAReport",siteName);
				if(receiver != null){
					mailInfo.setToAddress(receiver);
					mailInfo.setAttachFileNames(new String[]{fileUrl});
					mailInfo.setSubject("每日质检看板");  
					
					SimpleMailSender sms = new SimpleMailSender();
	
					mailInfo.setContent(createPreOQAMailText(sqlResult).toString());   
					if(mailInfo.getContent() == null){
						mailSendHistory.setErrorType("邮件内容是空的");
						mailSendHistory.setSendState("false");
					}else{
						try {
							sms.sendHtmlMail(mailInfo);
							mailSendHistory.setSendState("true");
							//品质待检的db操作 每天定时扫描的db操作
							updateSendTimesAndDate(sqlResult,mailSendDate);
						} catch (Exception e) {
							if(StringUtils.isNoneEmpty(e.getMessage())) {
								log.info("每日质检看板发送邮件失败  e => " + JSON.toJSONString(e));
							}
							if(e.getClass() == SendFailedException.class){
								mailSendHistory.setErrorType("收件人地址错误");
								mailSendHistory.setSendState("false");
							}else if(e.getClass() == AuthenticationFailedException.class){
								mailSendHistory.setErrorType("发件人用户名密码错误");
								mailSendHistory.setSendState("false");
							}else if (e.getClass() == MessagingException.class) {
								mailSendHistory.setErrorType("连接发件服务器失败");
								mailSendHistory.setSendState("false");
							}
						}
					}
				}else{
					mailSendHistory.setErrorType("收件人地址列表是空的");
					mailSendHistory.setSendState("false");
				}
				
				updateMailSendHistory(mailSendHistory);
			}
		}
	}
	
	public StringBuffer createPreOQAMailText(List<Map<String, Object>> sqlResult){
		StringBuffer mailText = new StringBuffer();

		mailText.append("<table width='100%'>");
		mailText.append("<tr height='30px'>");
		mailText.append("<td align='left' width='30%'>您好！<br/><br/></td>");
		mailText.append("</tr>");
		mailText.append("<tr>");
		mailText.append("<td align='left' width='30%'>见信好，以下待质检材料已完成入库，请品质同事进行检查。谢谢~~<br/><br/></td>");
		mailText.append("</tr>");
		mailText.append("<tr><td>");
		mailText.append("<table border='1' width='100%' style='text-align:center;border-collapse: collapse;border: none;border: solid #000 1px;'>");
		mailText.append("<tr>");
		mailText.append("<td width='5%'>序号</td><td width='5%'>工厂</td><td width='10%'>物料号</td><td width='10%'>物料描述</td>");
//		mailText.append("<td width='10%'>抬头文本</td><td width='10%'>供应商</td><td width='5%'>到货数量</td>");
		mailText.append("<td width='5%'>到货数量</td>");
		mailText.append("<td width='5%'>单位</td><td width='8%'>ERP工厂</td><td width='8%'>ERP库位</td><td width='10%'>货位地</td><td width='10%'>批次</td><td width='7%'>入库日期</td><td width='5%'>在库时间</td><td width='5%'>到期剩余</td>");
//		mailText.append("<td width='5%'>在库时间</td>");
//		mailText.append("<td width='5%'>到期剩余</td>");
		mailText.append("</tr>");

		if(sqlResult != null && !sqlResult.isEmpty())
			for(int i = 0 ; i<sqlResult.size(); i++){
				Map<String, Object> tmp = sqlResult.get(i);
				mailText.append("<tr><td>" + (i+1) + "</td>");
				mailText.append("<td>" + (tmp.get("SITENAME")!=null&&!"".equals(tmp.get("SITENAME"))?tmp.get("SITENAME"):" ") + "</td>");
				mailText.append("<td>" + (tmp.get("MATERIALSPECNAME")!=null&&!"".equals(tmp.get("MATERIALSPECNAME"))?tmp.get("MATERIALSPECNAME"):" ") + "</td>");
				mailText.append("<td>" + (tmp.get("DESCRIPTION") != null&&!"".equals(tmp.get("DESCRIPTION"))?tmp.get("DESCRIPTION"):" ") + "</td>");
//				mailText.append("<td>" + (tmp.get("RECEIVEACTNO") != null&&!"".equals(tmp.get("RECEIVEACTNO"))?tmp.get("RECEIVEACTNO"):" ") + "</td>");
//				mailText.append("<td>" + (tmp.get("SUPPLIERNAME") != null&&!"".equals(tmp.get("SUPPLIERNAME"))?tmp.get("SUPPLIERNAME"):" ") + "</td>");
				mailText.append("<td>" + (tmp.get("MATERIALQUANTITY") != null&&!"".equals(tmp.get("MATERIALQUANTITY"))?tmp.get("MATERIALQUANTITY"):" ") + "</td>");
				mailText.append("<td>" + (tmp.get("MATERIALUNIT") != null&&!"".equals(tmp.get("MATERIALUNIT"))?tmp.get("MATERIALUNIT"):" ") + "</td>");
				mailText.append("<td>" + (tmp.get("ERPFACTORY") != null&&!"".equals(tmp.get("ERPFACTORY"))?tmp.get("ERPFACTORY"):" ") + "</td>");
				mailText.append("<td>" + (tmp.get("ERPLOCATION") != null&&!"".equals(tmp.get("ERPLOCATION"))?tmp.get("ERPLOCATION"):" ") + "</td>");
				mailText.append("<td>" + (tmp.get("LOCATIONNAME") != null&&!"".equals(tmp.get("LOCATIONNAME"))?tmp.get("LOCATIONNAME"):" ") + "</td>");
				mailText.append("<td>" + (tmp.get("MATERIALPROCESSGROUPNAME") != null&&!"".equals(tmp.get("MATERIALPROCESSGROUPNAME"))?tmp.get("MATERIALPROCESSGROUPNAME"):" ") + "</td>");
				mailText.append("<td>" + (tmp.get("RECEIVETIME") != null&&!"".equals(tmp.get("RECEIVETIME"))?tmp.get("RECEIVETIME"):" ") + "</td>");
				mailText.append("<td>" + (tmp.get("STOCKDAYS") != null&&!"".equals(tmp.get("STOCKDAYS"))?tmp.get("STOCKDAYS"):" ") + "</td>");
				mailText.append("<td>" + (tmp.get("DAYS") != null&&!"".equals(tmp.get("DAYS"))?tmp.get("DAYS"):" ") + "</td></tr>");
			}
		mailText.append("</table>");
		mailText.append("</td>");
		mailText.append("</tr>");
		mailText.append("<tr><td></td></tr>");
		mailText.append("</table>");

		return mailText;
	}
	
	public void createPreOQAExcel(List<Map<String, Object>> sqlResult,OutputStream os) throws WriteException,IOException {
		WritableWorkbook workbook = Workbook.createWorkbook(os);
		WritableSheet sheet = workbook.createSheet("First Sheet", 0);

		sheet.mergeCells(0, 0, 9, 0);//添加合并单元格，第一个参数是起始列，第二个参数是起始行，第三个参数是终止列，第四个参数是终止行
		WritableFont bold = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD);//设置字体种类和黑体显示,字体为Arial,字号大小为10,采用黑体显示
		WritableCellFormat titleFormate = new WritableCellFormat(bold);//生成一个单元格样式控制对象
		titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.TOP);//单元格的内容垂直方向居中
		titleFormate.setWrap(true);
		sheet.addCell(new Label(0,0,"您好！\n\n目前有以下库存为质检状态的材料，请及时检查。谢谢\n",titleFormate));

		sheet.setRowView(0, 1000, false);

//		sheet.addCell(new Label(0,1,"序号"));
//		sheet.addCell(new Label(1,1,"工厂"));
//		sheet.addCell(new Label(2,1,"物料号"));
//		sheet.addCell(new Label(3,1,"物料描述"));
//		sheet.addCell(new Label(4,1,"抬头文本"));
//		sheet.addCell(new Label(5,1,"供应商"));
//		sheet.addCell(new Label(6,1,"到货数量"));
//		sheet.addCell(new Label(7,1,"单位"));
//		sheet.addCell(new Label(8,1,"库存地"));
//		sheet.addCell(new Label(9,1,"货位地"));
//		sheet.addCell(new Label(10,1,"批次"));
//		sheet.addCell(new Label(11,1,"入库日期"));
//		sheet.addCell(new Label(12,1,"到期剩余"));
		
		sheet.addCell(new Label(0,1,"序号"));
		sheet.addCell(new Label(1,1,"工厂"));
		sheet.addCell(new Label(2,1,"物料号"));
		sheet.addCell(new Label(3,1,"物料描述"));
		sheet.addCell(new Label(4,1,"到货数量"));
		sheet.addCell(new Label(5,1,"单位"));
		sheet.addCell(new Label(6,1,"ERP工厂"));
		sheet.addCell(new Label(7,1,"ERP库位"));
		sheet.addCell(new Label(8,1,"货位地"));
		sheet.addCell(new Label(9,1,"批次"));
		sheet.addCell(new Label(10,1,"入库日期"));
		sheet.addCell(new Label(11,1,"在库时间"));
		sheet.addCell(new Label(12,1,"到期剩余"));

		if(sqlResult != null && !sqlResult.isEmpty())
			for(int i = 0 ; i<sqlResult.size(); i++){
				Map<String, Object> tmp = sqlResult.get(i);
				sheet.addCell(new Label(0,2+i,(i+1)+""));
				sheet.addCell(new Label(1,2+i,(tmp.get("SITENAME")!=null?tmp.get("SITENAME").toString():"")));
				sheet.addCell(new Label(2,2+i,(tmp.get("MATERIALSPECNAME")!=null?tmp.get("MATERIALSPECNAME").toString():"")));
				sheet.addCell(new Label(3,2+i,(tmp.get("DESCRIPTION") != null?tmp.get("DESCRIPTION").toString():"")));
//				sheet.addCell(new Label(4,2+i,(tmp.get("RECEIVEACTNO")!= null?tmp.get("RECEIVEACTNO").toString():"")));
//				sheet.addCell(new Label(5,2+i,(tmp.get("SUPPLIERNAME")!= null?tmp.get("SUPPLIERNAME").toString():"")));
				sheet.addCell(new Label(4,2+i,(tmp.get("MATERIALQUANTITY")!= null?tmp.get("MATERIALQUANTITY").toString():"")));
				sheet.addCell(new Label(5,2+i,(tmp.get("MATERIALUNIT")!= null?tmp.get("MATERIALUNIT").toString():"")));
				sheet.addCell(new Label(6,2+i,(tmp.get("ERPFACTORY")!= null?tmp.get("ERPFACTORY").toString():"")));
				sheet.addCell(new Label(7,2+i,(tmp.get("ERPLOCATION")!= null?tmp.get("ERPLOCATION").toString():"")));
				sheet.addCell(new Label(8,2+i,(tmp.get("LOCATIONNAME")!= null?tmp.get("LOCATIONNAME").toString():"")));
				sheet.addCell(new Label(9,2+i,(tmp.get("MATERIALPROCESSGROUPNAME")!= null?tmp.get("MATERIALPROCESSGROUPNAME").toString():"")));
				sheet.addCell(new Label(10,2+i,(tmp.get("RECEIVETIME")!= null?tmp.get("RECEIVETIME").toString():"")));
				sheet.addCell(new Label(11,2+i,(tmp.get("STOCKDAYS")!= null?tmp.get("STOCKDAYS").toString():"")));
				sheet.addCell(new Label(12,2+i,(tmp.get("DAYS")!= null?tmp.get("DAYS").toString():"")));
			}
		//把创建的内容写入到输出流中，并关闭输出流
		workbook.write();
		workbook.close();
		os.close();

	}
	
	/**
	 * 质检看板时时发送
	 */
	public void sendPreOQAReportNow(List<String> boxlist) {
		
		log.info("入库后时时发送质检状态的BOX邮件任务2  ==========> ");
		
		List<Map<String, Object>> getSiteName = getRecMailUsersSiteName("SendPreOQAReport");//获取工厂名
		for(int i=0;i<getSiteName.size();i++)
		{
			String siteName=getSiteName.get(i).get("SITENAME").toString();
 
			String dbSql  =  "SELECT B.MATERIALSPECNAME"
					+ ",B.MATERIALPACKINGNAME"
					+ ",B.SITENAME"
					+ ",A.DESC_CN AS DESCRIPTION"
					+ ",B.ERPFACTORY"
					+ ",B.ERPLOCATION"
					+ ",B.LOCATIONNAME"
					+ ",B.OQARESULTSTATE"
					+ ",B.MATERIALQUANTITY"
					+ ",A.MATERIALUNIT"
					+ ",TO_CHAR (B.MAKEDATE, 'yyyy-mm-dd hh:mm:ss') AS MAKEDATE"
					+ ",TO_CHAR (B.RECEIVETIME, 'yyyy-mm-dd hh:mm:ss') AS RECEIVETIME"
					+ ",TO_CHAR (B.EXPIRINGDATE, 'yyyy-mm-dd hh:mm:ss') AS EXPIRATIONDAY"
					+ ",Round(TO_NUMBER(EXPIRINGDATE - TO_DATE(TO_CHAR(SYSDATE,'yyyy-MM-dd'),'yyyy-MM-dd')),0) AS DAYS"
					+ ",Round(TO_NUMBER(TO_DATE(TO_CHAR(SYSDATE,'yyyy-MM-dd'),'yyyy-MM-dd') - RECEIVETIME),0) AS STOCKDAYS"
					+ ",B.CHARGE AS MATERIALPROCESSGROUPNAME "
					+ "FROM MATERIALPACKING B LEFT JOIN MATERIALSPEC A ON B.MATERIALSPECNAME = A.MATERIALSPECNAME "
					+ "WHERE B.MATERIALPACKINGNAME IN (:BOXLIST) "
					+ "ORDER BY DAYS DESC";
	
			Map<String,Object> bindMap = new HashMap<String,Object>();
			bindMap.put("BOXLIST", boxlist);
			List<Map<String, Object>> sqlResult = IDMFrameServiceProxy.getSqlTemplate().queryForList(dbSql, bindMap);
			
			if(sqlResult != null && !sqlResult.isEmpty()){
				Date sendDate = new Date(); // mail send  time
				String mailSendDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(sendDate);
				
//				测试服务器
//				createDir("/usr01/wmstst/attachment");
//				String fileUrl ="/usr01/wmstst/attachment/" + new SimpleDateFormat("yyyyMMddHHmmsss").format(sendDate) + ".xls";
				
//				量产服务器
//				createDir("/wmsap/wmsadm/attachment");
				String fileUrl ="/home/yardadm/attachment/" + new SimpleDateFormat("yyyyMMddHHmmsss").format(sendDate) + ".xls";
				
				log.info("入库后时时发送质检状态的BOX邮件任务 fileUrl  => " + fileUrl);

				//生成excel
				FileOutputStream fout;
				try {
					fout = new FileOutputStream(fileUrl);
					createPreOQAExcel(sqlResult,fout);
				} catch (WriteException e) {e.printStackTrace();
				} catch (IOException e) {e.printStackTrace();}
				
				MailSenderInfo mailInfo = new MailSenderInfo();
				
				MWMSMail mail = this.initMailSender();
				
				mailInfo.setMailServerHost(mail.getHost());
				mailInfo.setMailServerPort(mail.getPort());  
				mailInfo.setValidate(true);
				mailInfo.setUserName(mail.getUsername());
				mailInfo.setPassword(mail.getPassword());
				mailInfo.setFromAddress(mail.getFromAddress());
				
				MailSendHistory mailSendHistory = new MailSendHistory(); 
				
				mailSendHistory.setSendTime(mailSendDate);
				mailSendHistory.setInterfaceType("SendPreOQAReport");
				
			 	String[] receiver = getRecMailUsers("SendPreOQAReport",siteName);
				if(receiver != null){
					mailInfo.setToAddress(receiver);
					mailInfo.setAttachFileNames(new String[]{fileUrl});
					mailInfo.setSubject("B20物料质检邀请");
					if(boxlist.contains("加急质检")) {
						mailInfo.setSubject("【紧急】B20物料质检邀请");
					}
					
					SimpleMailSender sms = new SimpleMailSender();
	
					mailInfo.setContent(createPreOQAMailText(sqlResult).toString());   
					if(mailInfo.getContent() == null){
						mailSendHistory.setErrorType("邮件内容是空的");
						mailSendHistory.setSendState("false");
					}else{
						try {
							sms.sendHtmlMail(mailInfo);
							mailSendHistory.setSendState("true");
							//品质待检的db操作 每天定时扫描的db操作
							updateSendTimesAndDate(sqlResult,mailSendDate);
						} catch (Exception e) {
							if(e.getClass() == SendFailedException.class){
								mailSendHistory.setErrorType("收件人地址错误");
								mailSendHistory.setSendState("false");
							}else if(e.getClass() == AuthenticationFailedException.class){
								mailSendHistory.setErrorType("发件人用户名密码错误");
								mailSendHistory.setSendState("false");
							}else if (e.getClass() == MessagingException.class) {
								mailSendHistory.setErrorType("连接发件服务器失败");
								mailSendHistory.setSendState("false");
							}
						}
					}
				}else{
					mailSendHistory.setErrorType("收件人地址列表是空的");
					mailSendHistory.setSendState("false");
				}
				
				updateMailSendHistory(mailSendHistory);
			}
		}
	}
	
	/**
	 * 发送检查邀请书邮件
	 * @param eventInfo
	 */
	public void sendInvitationMail(String siteName, EventInfo eventInfo, Document message) {
		Element root = message.getDocument().getRootElement();
		Element bodyElement = root.getChild("Body");
		Element dataListElement = bodyElement.getChild("DATALIST");
		
		List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
		
		for (Iterator<?> iterator = dataListElement.getChildren().iterator(); iterator.hasNext();) {
			Map<String, Object> tmp = new HashMap<String, Object>();
			Element dataElement = (Element) iterator.next();
			tmp.put("INPUTTIME", dataElement.getChild("INPUTTIME").getText());
			tmp.put("MATERIALSPECNAME", dataElement.getChild("MATERIALSPECNAME").getText());
			tmp.put("DESCRIPTION", dataElement.getChild("DESCRIPTION").getText());
			tmp.put("SUPPLIERNAME", dataElement.getChild("SUPPLIERNAME").getText());
			tmp.put("MATERIALUNIT", dataElement.getChild("MATERIALUNIT").getText());
			tmp.put("SUM", dataElement.getChild("SUM").getText());
			dataList.add(tmp);
		}
		
		if(dataList != null && !dataList.isEmpty()){
			Date sendDate = new Date();
			String mailSendDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(sendDate);
//			测试服务器
//			createDir("/usr01/wmstst/attachment");
//			String fileUrl ="/usr01/wmstst/attachment/" + new SimpleDateFormat("yyyyMMddHHmmsss").format(sendDate) + ".xls";
			
//			量产服务器
//			createDir("/wmsap/wmsadm/attachment");
			String fileUrl ="D:/wms/excel/" + new SimpleDateFormat("yyyyMMddHHmmsss").format(sendDate) + ".xls";
			
			log.info("每日发送邮件附件位置 检查邀请书  fileUrl  => " + fileUrl);

			//生成excel
			FileOutputStream fout;
			try {
				fout = new FileOutputStream(fileUrl);
				createInvitationExcel(eventInfo,dataList,fout);
			} catch (WriteException e) {e.printStackTrace();
			} catch (IOException e) {e.printStackTrace();}
			
			MailSenderInfo mailInfo = new MailSenderInfo();
			
			MWMSMail mail = this.initMailSender();
			
			mailInfo.setMailServerHost(mail.getHost());
			mailInfo.setMailServerPort(mail.getPort());  
			mailInfo.setValidate(true);
			mailInfo.setUserName(mail.getUsername());
			mailInfo.setPassword(mail.getPassword());
			mailInfo.setFromAddress(mail.getFromAddress());
			
			MailSendHistory mailSendHistory = new MailSendHistory(); 
			
			mailSendHistory.setSendTime(mailSendDate);
			mailSendHistory.setInterfaceType("SendInvitationMail");
			
		 	String[] receiver = getRecMailUsers("SendInvitationMail",siteName);
			if(receiver != null){
				mailInfo.setToAddress(receiver);
				mailInfo.setAttachFileNames(new String[]{fileUrl});
				mailInfo.setSubject("检查邀请书");  
				
				SimpleMailSender sms = new SimpleMailSender();

				mailInfo.setContent(getInvitationHtml(dataList).toString());
				if(mailInfo.getContent() == null){
					mailSendHistory.setErrorType("邮件内容是空的");
					mailSendHistory.setSendState("false");
				}else{
					try {
						sms.sendHtmlMail(mailInfo);
						mailSendHistory.setSendState("true");
						updateSendTimesAndDate(dataList,mailSendDate);
					} catch (Exception e) {
						if(StringUtils.isNoneEmpty(e.getMessage())) {
							log.info("检查邀请书发送邮件失败  e.getMessage() => " + e.getMessage());
						}
						if(e.getClass() == SendFailedException.class){
							mailSendHistory.setErrorType("收件人地址错误");
							mailSendHistory.setSendState("false");
						}else if(e.getClass() == AuthenticationFailedException.class){
							mailSendHistory.setErrorType("发件人用户名密码错误");
							mailSendHistory.setSendState("false");
						}else if (e.getClass() == MessagingException.class) {
							mailSendHistory.setErrorType("连接发件服务器失败");
							mailSendHistory.setSendState("false");
						}
					}
				}
			}else{
				mailSendHistory.setErrorType("收件人地址列表是空的");
				mailSendHistory.setSendState("false");
			}
			//Mail历史表
			updateMailSendHistory(mailSendHistory);
		}
	}
	
	/**
	 * 检查邀请书Excel
	 * @param eventInfo
	 * @param sqlResult
	 * @param os
	 * @throws WriteException
	 * @throws IOException
	 */
	public void createInvitationExcel(EventInfo eventInfo, List<Map<String, Object>> dataList, OutputStream os) throws WriteException,IOException {
		//创建工作薄
		WritableWorkbook workbook = Workbook.createWorkbook(os);
		//创建新的一页
		WritableSheet sheet = workbook.createSheet("First Sheet", 0);

		//构造表头
		sheet.mergeCells(0, 0, 10, 0);//添加合并单元格，第一个参数是起始列，第二个参数是起始行，第三个参数是终止列，第四个参数是终止行
		WritableFont bold = new WritableFont(WritableFont.createFont("宋体"),18,WritableFont.BOLD);//设置字体种类和黑体显示,字体为Arial,字号大小为10,采用黑体显示
		WritableCellFormat titleFormate = new WritableCellFormat(bold);//生成一个单元格样式控制对象
		titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//单元格的内容垂直方向居中
		titleFormate.setAlignment(jxl.format.Alignment.CENTRE);
		titleFormate.setWrap(true);
		sheet.addCell(new Label(0,0,"北京京东方创元科技有限公司",titleFormate));

		sheet.setRowView(0, 600, false);
		
		sheet.mergeCells(0, 1, 10, 1);
		WritableFont normal = new WritableFont(WritableFont.createFont("宋体"),20);
		WritableCellFormat titleFormate1 = new WritableCellFormat(normal);//生成一个单元格样式控制对象
		titleFormate1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//单元格的内容垂直方向居中
		titleFormate1.setAlignment(jxl.format.Alignment.CENTRE);
		titleFormate1.setWrap(true);
		sheet.addCell(new Label(0,1,"检查邀请书",titleFormate1));
		sheet.setRowView(1, 525, false);

		WritableFont text = new WritableFont(WritableFont.createFont("宋体"),11);
		WritableCellFormat textFormate = new WritableCellFormat(text);
		textFormate.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//单元格的内容垂直方向居中
		textFormate.setAlignment(jxl.format.Alignment.CENTRE);
		textFormate.setWrap(true);
		
		//设置列名
		sheet.addCell(new Label(0,2,"序号",textFormate));
		sheet.addCell(new Label(1,2,"入库日期",textFormate));
		sheet.addCell(new Label(2,2,"P/N",textFormate));	
		sheet.addCell(new Label(3,2,"描述",textFormate));	
		sheet.addCell(new Label(4,2,"供应商",textFormate));
		sheet.addCell(new Label(5,2,"单位",textFormate));
		sheet.addCell(new Label(6,2,"数量",textFormate));
		sheet.addCell(new Label(7,2,"邀请日期",textFormate));
		sheet.addCell(new Label(8,2,"邀请时间",textFormate));
		sheet.addCell(new Label(9,2,"邀请人（库房）",textFormate));
		sheet.addCell(new Label(10,2,"接收人（品质）",textFormate));
		sheet.setRowView(2, 705, false);
		
		//设置列宽
		sheet.setColumnView(0, 9);
		sheet.setColumnView(1, 13);
		sheet.setColumnView(2, 19);
		sheet.setColumnView(3, 44);
		sheet.setColumnView(4, 30);
		sheet.setColumnView(5, 9);
		sheet.setColumnView(6, 9);
		sheet.setColumnView(7, 13);
		sheet.setColumnView(8, 10);
		sheet.setColumnView(9, 11);
		sheet.setColumnView(10, 11);
		
		//插入数据
		if(dataList != null && !dataList.isEmpty()) {
			Date sendDate = new Date();
	        String format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(sendDate);
	        
	        WritableFont text1 = new WritableFont(WritableFont.createFont("宋体"),10);
			WritableCellFormat textFormate1 = new WritableCellFormat(text1);
			textFormate1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//单元格的内容垂直方向居中
			textFormate1.setAlignment(jxl.format.Alignment.LEFT);
			textFormate1.setWrap(true);
	        
			for(int i = 0 ; i<dataList.size(); i++){
				Map<String, Object> tmp = dataList.get(i);
				sheet.addCell(new Label(0,3+i,(i+1)+"",textFormate1));
				sheet.addCell(new Label(1,3+i,(tmp.get("INPUTTIME")!=null?tmp.get("INPUTTIME").toString().split(" ")[0]:""),textFormate1));
				sheet.addCell(new Label(2,3+i,(tmp.get("MATERIALSPECNAME")!=null?tmp.get("MATERIALSPECNAME").toString():""),textFormate1));
				sheet.addCell(new Label(3,3+i,(tmp.get("DESCRIPTION") != null?tmp.get("DESCRIPTION").toString():""),textFormate1));
				sheet.addCell(new Label(4,3+i,(tmp.get("SUPPLIERNAME")!= null?tmp.get("SUPPLIERNAME").toString():""),textFormate1));
				sheet.addCell(new Label(5,3+i,(tmp.get("MATERIALUNIT")!= null?tmp.get("MATERIALUNIT").toString():""),textFormate1));
				sheet.addCell(new Label(6,3+i,(tmp.get("SUM")!= null?tmp.get("SUM").toString():""),textFormate1));
				sheet.addCell(new Label(7,3+i,format.split(" ")[0],textFormate1));
				sheet.addCell(new Label(8,3+i,format.split(" ")[1],textFormate1));
				sheet.setRowView(3+i, 405, false);
				
			}
		}
		
		sheet.mergeCells(9, 3, 9, 3+dataList.size()-1);
		WritableFont name = new WritableFont(WritableFont.createFont("宋体"),10);//设置字体种类和黑体显示,字体为Arial,字号大小为10,采用黑体显示
		WritableCellFormat nameFormate = new WritableCellFormat(name);//生成一个单元格样式控制对象
		nameFormate.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//单元格的内容垂直方向居中
		nameFormate.setAlignment(jxl.format.Alignment.CENTRE);
		nameFormate.setWrap(true);
		sheet.addCell(new Label(9,3,eventInfo.getEventUser(),nameFormate));
		
		sheet.mergeCells(10, 3, 10, 3+dataList.size()-1);
		
		
		workbook.write();
		workbook.close();
		os.close();
	}
	
	/**
	 * 检查邀请书HTML
	 * @param dataList
	 * @return
	 */
	public StringBuffer getInvitationHtml(List<Map<String, Object>> dataList)
	{
		StringBuffer mailText = new StringBuffer();

		mailText.append("<table width='100%'>");
		mailText.append("<tr height='30px'>");
		mailText.append("<td align='left' width='30%'>您好！<br/><br/></td>");
		mailText.append("</tr>");
		mailText.append("<tr>");
		mailText.append("<td align='left' width='30%'>检查邀请书，请及时检查。谢谢<br/><br/></td>");
		mailText.append("</tr>");
		mailText.append("<tr><td>");
		mailText.append("<table border='1' width='100%' style='text-align:center;border-collapse: collapse;border: none;border: solid #000 1px;'>");
		mailText.append("<tr>");
		mailText.append("<td width='6%'>序号</td><td width='8%'>入库日期</td><td width='10%'>P/N</td><td width='24%'>物料描述</td>");
		mailText.append("<td width='18%'>供应商</td><td width='4%'>单位</td><td width='4%'>数量</td><td width='8%'>邀请日期</td>");
		mailText.append("<td width='6%'>邀请时间</td><td width='6%'>邀请人（库房）</td><td width='6%'>接收人（品质）</td>");
		mailText.append("</tr>");

		if(dataList != null && !dataList.isEmpty()) {
			Date sendDate = new Date();
	        String format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(sendDate);
	        
			for(int i = 0 ; i<dataList.size(); i++){
				Map<String, Object> tmp = dataList.get(i);
				mailText.append("<td style='background-color:white;'>" + (i+1) + "</td>");
				mailText.append("<td style='background-color:white;'>" + (tmp.get("INPUTTIME")!=null?tmp.get("INPUTTIME").toString().split(" ")[0]:" ") + "</td>");
				mailText.append("<td style='background-color:white;'>" + (tmp.get("MATERIALSPECNAME")!=null?tmp.get("MATERIALSPECNAME").toString():" ") + "</td>");
				mailText.append("<td style='background-color:white;'>" + (tmp.get("DESCRIPTION") != null?tmp.get("DESCRIPTION").toString():" ") + "</td>");
				mailText.append("<td style='background-color:white;'>" + (tmp.get("SUPPLIERNAME")!= null?tmp.get("SUPPLIERNAME").toString():" ") + "</td>");
				mailText.append("<td style='background-color:white;'>" + (tmp.get("MATERIALUNIT")!= null?tmp.get("MATERIALUNIT"):" ") + "</td>");
				mailText.append("<td style='background-color:white;'>" + (tmp.get("SUM")!= null?tmp.get("SUM").toString():" ") + "</td>");
				mailText.append("<td style='background-color:white;'>" + format.split(" ")[0] + "</td>");
				mailText.append("<td style='background-color:white;'>" + format.split(" ")[1] + "</td>");
			}
		}
		mailText.append("</table>");
		mailText.append("</td>");
		mailText.append("</tr>");
		mailText.append("<tr><td></td></tr>");
		mailText.append("</table>");

		return mailText;
	}
}
