package com.cim.idm.yard.service;


import com.cim.idm.framework.IDMFrameServiceProxy;
import com.cim.idm.framework.data.EventInfo;
import com.cim.idm.framework.util.time.TimeStampUtil;
import com.cim.idm.yard.util.JavaMailUntil;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import javax.activation.DataHandler;
import javax.activation.DataSource;
import javax.activation.FileDataSource;
import javax.mail.*;
import javax.mail.internet.*;
import java.io.File;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

public class YardMailServiceImpI {
	private static Log logger = LogFactory.getLog("YardMailServiceImpI");
	
	/**
	 * B20堆场邮件增加
	 * @throws Exception 
	 */
	public  void CreateForYardMail(String num,String name,String receiver,String JobTime,String SiteName,EventInfo eventInfo,String JobdeScription) throws Exception
	{
		try {
		logger.info("堆场邮件管理创建   => " + num + "|" + name);
//		查询是否存在主表，不存在就新增
		String querySqlString="SELECT NUM,NAME FROM YARD_MAIL WHERE SITENAME = :SITENAME AND NUM = :NUM ";
		Map<String, String> queryMap = new HashMap<String, String>();
		queryMap.put("SITENAME", SiteName);
		queryMap.put("NUM", num); 
		List<Map<String, Object>> returnListStorge = IDMFrameServiceProxy.getSqlTemplate().queryForList(querySqlString, queryMap);
		if(returnListStorge != null && returnListStorge.size() > 0) {
			logger.info("堆场邮件编号存在   => " + num );
			 Map<String, Object> firstMap = returnListStorge.get(0);  
			 if(!(firstMap.get("NAME").equals(name)))//判断邮件名是否存在
			{
				 logger.info("堆场邮件编号存在,修改邮件名   => " + name );
				String Updatesql="UPDATE YARD_MAIL SET"
				 +" NAME=:NAME,JOBDESCRIPTION=:JOBDESCRIPTION,LASTEVENTNAME=:LASTEVENTNAME,LASTEVENTTIMEKEY=:LASTEVENTTIMEKEY,LASTEVENTTIME=:LASTEVENTTIME,LASTEVENTUSER=:LASTEVENTUSER,LASTEVENTCOMMENT=:LASTEVENTCOMMENT"+
				 " WHERE SITENAME=:SITENAME AND NUM=:NUM";
				Map<String, Object> UpdateMap = new HashMap<String, Object>();
				UpdateMap.put("NAME", name);
				UpdateMap.put("JOBDESCRIPTION",JobdeScription );
				UpdateMap.put("LASTEVENTNAME", "ModifyYardMail");
				UpdateMap.put("LASTEVENTTIMEKEY", TimeStampUtil.getCurrentEventTimeKey());
				UpdateMap.put("LASTEVENTTIME", eventInfo.getEventTime());
				UpdateMap.put("LASTEVENTUSER", eventInfo.getEventUser());
				UpdateMap.put("LASTEVENTCOMMENT", "ModifyYardMail");
				UpdateMap.put("SITENAME", SiteName);
				UpdateMap.put("NUM", num);
				IDMFrameServiceProxy.getSqlTemplate().update(Updatesql, UpdateMap);
				
				 logger.info("堆场邮件编号存在,添加明细表   => " + receiver );
				 String InsertSql="INSERT INTO YARD_MAILDETAIL"+
				 "(NUM,LINE, RECEIVER, CREATUSER, CREATTIME, LASTEVENTNAME, LASTEVENTTIMEKEY, LASTEVENTTIME, LASTEVENTUSER, LASTEVENTCOMMENT)"+
				 "VALUES(:NUM,SYS_GUID(),:RECEIVER,:CREATUSER,:CREATTIME,:LASTEVENTNAME,:LASTEVENTTIMEKEY,:LASTEVENTTIME,:LASTEVENTUSER,:LASTEVENTCOMMENT)";
				 Map<String, Object> InsertMailMap = new HashMap<String, Object>();
				 InsertMailMap.put("NUM", num);
				 InsertMailMap.put("LINE", UUID.randomUUID());
				 InsertMailMap.put("RECEIVER", receiver);
				 InsertMailMap.put("CREATUSER", eventInfo.getEventUser());
				 InsertMailMap.put("CREATTIME", eventInfo.getEventTime());
				 InsertMailMap.put("LASTEVENTNAME", eventInfo.getEventUser());
				 InsertMailMap.put("LASTEVENTTIMEKEY", TimeStampUtil.getCurrentEventTimeKey());
				 InsertMailMap.put("LASTEVENTTIME", eventInfo.getEventTime());
				 InsertMailMap.put("LASTEVENTUSER", eventInfo.getEventUser());
				 InsertMailMap.put("LASTEVENTCOMMENT", "Created");
				 IDMFrameServiceProxy.getSqlTemplate().update(InsertSql, InsertMailMap);
			}else {
				logger.info("堆场邮件编号存在,邮件名存在，添加明细表   => " + receiver );
				String InsertSql2="INSERT INTO YARD_MAILDETAIL"+
				"(NUM, LINE,RECEIVER, CREATUSER, CREATTIME, LASTEVENTNAME, LASTEVENTTIMEKEY, LASTEVENTTIME, LASTEVENTUSER, LASTEVENTCOMMENT)"+
				"VALUES(:NUM,SYS_GUID(),:RECEIVER,:CREATUSER,:CREATTIME,:LASTEVENTNAME,:LASTEVENTTIMEKEY,:LASTEVENTTIME,:LASTEVENTUSER,:LASTEVENTCOMMENT)";
				 Map<String, Object> InsertMailMap2 = new HashMap<String, Object>();
				 InsertMailMap2.put("NUM", num);
					/* InsertMailMap2.put("LINE", UUID.randomUUID()); */
				 InsertMailMap2.put("RECEIVER", receiver);
				 InsertMailMap2.put("CREATUSER", eventInfo.getEventUser());
				 InsertMailMap2.put("CREATTIME", eventInfo.getEventTime());
				 InsertMailMap2.put("LASTEVENTNAME", "Created");
				 InsertMailMap2.put("LASTEVENTTIMEKEY", TimeStampUtil.getCurrentEventTimeKey());
				 InsertMailMap2.put("LASTEVENTTIME", eventInfo.getEventTime());
				 InsertMailMap2.put("LASTEVENTUSER", eventInfo.getEventUser());
				 InsertMailMap2.put("LASTEVENTCOMMENT", "Created");
				 IDMFrameServiceProxy.getSqlTemplate().update(InsertSql2, InsertMailMap2);
			}
		}else {
			logger.info("堆场邮件编号不存在 ，添加编号  => " + num );
			String InsertMail="INSERT INTO YARD_MAIL"+
			"(SITENAME,NUM,NAME,JOBDESCRIPTION,JOBTIME,CREATUSER,CREATTIME,LASTEVENTNAME,LASTEVENTTIMEKEY,LASTEVENTTIME,LASTEVENTUSER,LASTEVENTCOMMENT)"+
			"VALUES(:SITENAME,:NUM,:NAME,:JOBDESCRIPTION,:JOBTIME,:CREATUSER,:CREATTIME,:LASTEVENTNAME,:LASTEVENTTIMEKEY,:LASTEVENTTIME,:LASTEVENTUSER,:LASTEVENTCOMMENT)";
			 Map<String, Object> InsertMail2 = new HashMap<String, Object>();
			 InsertMail2.put("SITENAME", SiteName);
			 InsertMail2.put("NUM", num);
			 InsertMail2.put("NAME", name);
			 InsertMail2.put("JOBDESCRIPTION", JobdeScription);
			 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			 Date date = sdf.parse(JobTime);
			 InsertMail2.put("JOBTIME", date);
			 InsertMail2.put("CREATUSER", eventInfo.getEventUser());
			 InsertMail2.put("CREATTIME", eventInfo.getEventTime());
			 InsertMail2.put("LASTEVENTNAME", eventInfo.getEventUser());
			 InsertMail2.put("LASTEVENTTIMEKEY", TimeStampUtil.getCurrentEventTimeKey());
			 InsertMail2.put("LASTEVENTTIME", eventInfo.getEventTime());
			 InsertMail2.put("LASTEVENTUSER", eventInfo.getEventUser());
			 InsertMail2.put("LASTEVENTCOMMENT", "Created");
			 IDMFrameServiceProxy.getSqlTemplate().update(InsertMail, InsertMail2);
			 
			 logger.info("堆场邮件编号存在,添加明细表   => " + receiver );
			 String InsertSql="INSERT INTO YARD_MAILDETAIL"+
			 "(NUM,LINE, RECEIVER,DESCRIPTION,CREATUSER,CREATTIME,LASTEVENTNAME,LASTEVENTTIMEKEY,LASTEVENTTIME,LASTEVENTUSER,LASTEVENTCOMMENT)"+
			"VALUES(:NUM,SYS_GUID(),:RECEIVER,:DESCRIPTION,:CREATUSER,:CREATTIME,:LASTEVENTNAME,:LASTEVENTTIMEKEY,:LASTEVENTTIME,:LASTEVENTUSER,:LASTEVENTCOMMENT)";
			 Map<String, Object> InsertMailMap = new HashMap<String, Object>();
			 InsertMailMap.put("NUM", num);
				/* InsertMailMap.put("LINE", UUID.randomUUID()); */
			 InsertMailMap.put("RECEIVER", receiver);
			 InsertMailMap.put("DESCRIPTION", null);
			 InsertMailMap.put("CREATUSER", eventInfo.getEventUser());
			 InsertMailMap.put("CREATTIME", eventInfo.getEventTime());
			 InsertMailMap.put("LASTEVENTNAME", eventInfo.getEventUser());
			 InsertMailMap.put("LASTEVENTTIMEKEY", TimeStampUtil.getCurrentEventTimeKey());
			 InsertMailMap.put("LASTEVENTTIME", eventInfo.getEventTime());
			 InsertMailMap.put("LASTEVENTUSER", eventInfo.getEventUser());
			 InsertMailMap.put("LASTEVENTCOMMENT", "Created");
			 IDMFrameServiceProxy.getSqlTemplate().update(InsertSql, InsertMailMap);
		}
		} catch (Exception e) {
			e.getMessage();
			throw e;
		}
	}
	
	/**
	 * B20堆场邮件修改
	 * @throws Exception 
	 */
	public void ModifyForYardMail(String SiteName,String num,String name,String JobTime, EventInfo eventInfo,String receiver,String JobdeScription,String LINE)
	{
		logger.info("堆场邮件管理修改   => " + num + "|" + name);
//		查询是否存在主表，不存在就新增	
		try {
				 logger.info("堆场邮件编号存在,修改邮件名   => " + name );
				String Updatesql="UPDATE YARD_MAIL SET"
				 +" NAME=:NAME,JOBDESCRIPTION=:JOBDESCRIPTION,JOBTIME=:JOBTIME,LASTEVENTNAME=:LASTEVENTNAME,LASTEVENTTIMEKEY=:LASTEVENTTIMEKEY,LASTEVENTTIME=:LASTEVENTTIME,LASTEVENTUSER=:LASTEVENTUSER,LASTEVENTCOMMENT=:LASTEVENTCOMMENT"+
				 " WHERE SITENAME=:SITENAME AND NUM=:NUM";
				Map<String, Object> UpdateMap = new HashMap<String, Object>();
				UpdateMap.put("NAME", name);
				
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				Date date = sdf.parse(JobTime);
				
				UpdateMap.put("JOBTIME",date);
				UpdateMap.put("JOBDESCRIPTION",JobdeScription);
				UpdateMap.put("LASTEVENTNAME", "ModifyYardMail");
				UpdateMap.put("LASTEVENTTIMEKEY", TimeStampUtil.getCurrentEventTimeKey());
				UpdateMap.put("LASTEVENTTIME", eventInfo.getEventTime());
				UpdateMap.put("LASTEVENTUSER", eventInfo.getEventUser());
				UpdateMap.put("LASTEVENTCOMMENT", "ModifyYardMail");
				UpdateMap.put("SITENAME", SiteName);
				UpdateMap.put("NUM", num);
				IDMFrameServiceProxy.getSqlTemplate().update(Updatesql, UpdateMap);
				
				 logger.info("堆场邮件编号存在,修改明细表   => " + receiver );
				 String InsertSql="UPDATE YARD_MAILDETAIL SET RECEIVER=:RECEIVER,LASTEVENTNAME=:LASTEVENTNAME,LASTEVENTTIMEKEY=:LASTEVENTTIMEKEY,LASTEVENTTIME=:LASTEVENTTIME,LASTEVENTUSER=:LASTEVENTUSER,LASTEVENTCOMMENT=:LASTEVENTCOMMENT WHERE NUM=:NUM AND LINE = :LINE";
				 Map<String, Object> InsertMailMap = new HashMap<String, Object>();
				 InsertMailMap.put("RECEIVER", receiver);
				 InsertMailMap.put("LINE", LINE);
				 InsertMailMap.put("LASTEVENTNAME", "ModifyYardMail");
				 InsertMailMap.put("LASTEVENTTIMEKEY", TimeStampUtil.getCurrentEventTimeKey());
				 InsertMailMap.put("LASTEVENTTIME", eventInfo.getEventTime());
				 InsertMailMap.put("LASTEVENTUSER", eventInfo.getEventUser());
				 InsertMailMap.put("LASTEVENTCOMMENT", "ModifyYardMail");
				 InsertMailMap.put("NUM", num);
				 IDMFrameServiceProxy.getSqlTemplate().update(InsertSql, InsertMailMap);
		}catch (ParseException e) {
		    e.printStackTrace(); // 或者你可以进行其他的异常处理操作
		}
			
	}
	
	/**
	 * B20堆场邮件删除
	 * @throws Exception 
	 */
	public void DelForYardMail(String sitename,String num,String RECEIVER)
	{
		Map<String, Object> insertMap = new HashMap<String, Object>();
		String delSql = " DELETE FROM YARD_MAILDETAIL yd WHERE yd.NUM = :NUM and RECEIVER = :RECEIVER ";
		insertMap.put("NUM", num);
		insertMap.put("RECEIVER", RECEIVER);
		IDMFrameServiceProxy.getSqlTemplate().update(delSql, insertMap);
	}
	
	
	public void CreatePreOutMailFile (String shipRequestName) {
		String file = this.getClass().getClassLoader().getResource("YardPickingSample.xls").getFile();
		try {
			File file2 = new File(file);
			
			Date sendDate = new Date(); // mail send time
	        String fs = System.getProperties().getProperty("file.separator"); // 文件分隔符
	        String property = System.getProperty("os.name");
	        String fileUrl = "";
	        if (property.contains("Windows")) {
	            //fs = "D:" + fs;
	            fileUrl = "D:" ;
	        } 
	        createDir(fs + "Data" + fs + "b20wms" + fs + "attachment");
	        String fileName = shipRequestName  + "-" +new SimpleDateFormat("yyyyMMddHHmmsss").format(sendDate) + ".xls";
	        fileUrl  += fs + "Data" + fs + "b20wms" + fs + "attachment" + fs + fileName;
	        
	        File file3 = new File(fileUrl);
			org.apache.commons.io.FileUtils.copyFile(file2, file3);
			createPreStockOutExcel(shipRequestName, file3);
			SendEmail(fileName, fileUrl);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void createDir(String url) {
        File file = new File(url);
        // 判断是否存在
        if (!file.exists() && !file.isDirectory()) {
            try {
                // 生成文件夹
                file.mkdir();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }


    }
	
	
	public  static void createPreStockOutExcel(String shipRequestName, File file)
            throws WriteException, IOException, BiffException {
		//通过订单去抓数据，有哪些信息
		String sql = "SELECT ROWNUM,'False' AS CHECKBOX\r\n" + 
				",yd.SITENAME\r\n" + 
				",yd.PONO\r\n" + 
				",yd.INVOICENO\r\n" + 
				",yd.RECEIVEREQUESTNAME\r\n" + 
				",yd.CONTAINER\r\n" + 
				",yd.DEVICE\r\n" + 
				",yd.DEVICENAME\r\n" + 
				",yd.STOCKSTATE\r\n" + 
				",yd.CHARGE\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.DEPARTMENT\r\n" + 
				"--,yd.UNPACKING\r\n" + 
				",(CASE WHEN yd.UNPACKING = 'True' THEN 'True' ELSE 'False' END) AS UNPACKING\r\n" + 
				",yd.LOCATIONNAME\r\n" + 
				",yd.DESCRIPTION\r\n" + 
				",yd.CARINFO\r\n" + 
				",yd.RECEIVEUSER\r\n" + 
				",yd.RECEIVETIME\r\n" + 
				",yd.STOCKUSER \r\n" + 
				",yd.STOCKTIME\r\n" + 
				",yd.INVENTORYRESULTS \r\n" + 
				",yd.INVENTORYDESCRIPTION\r\n" + 
				",yd.\"EXCEPTION\" \r\n" + 
				",TO_CHAR(yd.MOVEDATE,'YYYY-MM-DD') MOVEDATE\r\n" + 
				",yd.MOVEENTRANCE \r\n" + 
				",yd.MOVEORDER \r\n" + 
				",yd.TRANSPORTREQUIRE ,yd.SHIPREQUESTNAME,MOVEENTRANCE\r\n" + 
				"FROM YARD_DEVICE yd \r\n" + 
				"WHERE yd.SHIPREQUESTNAME = :SHIPREQUESTNAME\r\n" + 
				"AND  1= 1 ";
			HashMap<String,Object> hashMap = new HashMap<String,Object> ();
			hashMap.put("SHIPREQUESTNAME", shipRequestName);
			List<Map<String,Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, hashMap);
			if (queryForList == null || queryForList.size() <= 0) return;
		 	Workbook workbook = Workbook.getWorkbook(file);
		 	WritableWorkbook createWorkbook = Workbook.createWorkbook(file, workbook);
		 	//createWorkbook.copySheet("Template","First Sheet", 0);
			//createWorkbook.copySheet(0,"First Sheet", 1);
	        //WritableSheet sheet = createWorkbook.createSheet("First Sheet", 0);
		 	WritableSheet sheet = createWorkbook.getSheet("Template");
	        
//            excelMng.SetValue(4, 4, mpList[i].DEPARTMENT);//部门
//            excelMng.SetValue(3, 17, mpList[i].SHIPREQUESTNAME.ToString());//单号
//            excelMng.SetValue(4, 17, DateTime.Now.ToString("yyyy-MM-dd"));//创建日期
	        jxl.format.CellFormat cellFormat = sheet.getCell(6, 7).getCellFormat();
	        sheet.addCell(new Label(3, 3, setValue(queryForList.get(0).get("DEPARTMENT")),cellFormat));
	        sheet.addCell(new Label(16, 2, setValue(queryForList.get(0).get("SHIPREQUESTNAME")),cellFormat));
	        sheet.addCell(new Label(16, 3, new SimpleDateFormat("yyyy-MM-dd").format(TimeStampUtil.getCurrentTimestamp()),cellFormat));
	        
	        
            int SheetNumber = 0;
            int StartRowIdx = 7;

            for (int i = 0; i < queryForList.size(); i++)
            {
            	sheet.addCell(new Label(2,StartRowIdx,  setValue(queryForList.get(i).get("PONO")),cellFormat));
            	sheet.addCell(new Label(3,StartRowIdx,  "",cellFormat));
            	sheet.addCell(new Label(4,StartRowIdx,  setValue(queryForList.get(i).get("DEVICE")),cellFormat));
            	sheet.addCell(new Label(5,StartRowIdx,  setValue(queryForList.get(i).get("DEVICENAME")),cellFormat));
            	sheet.addCell(new Label(6,StartRowIdx,  setValue(queryForList.get(i).get("CASENO")),cellFormat));
            	sheet.addCell(new Label(7,StartRowIdx,  setValue(queryForList.get(i).get("LENGTH")),cellFormat));
            	sheet.addCell(new Label(8,StartRowIdx,  setValue(queryForList.get(i).get("WIDTH")),cellFormat));
            	sheet.addCell(new Label(9,StartRowIdx,  setValue(queryForList.get(i).get("HEIGHT")),cellFormat));
            	sheet.addCell(new Label(10,StartRowIdx,  setValue(queryForList.get(i).get("CROSS")),cellFormat));
            	sheet.addCell(new Label(11,StartRowIdx,  setValue(queryForList.get(i).get("NET")),cellFormat));
            	sheet.addCell(new Label(12,StartRowIdx,  setValue(queryForList.get(i).get("MOVEDATE")),cellFormat));
            	sheet.addCell(new Label(13,StartRowIdx,  setValue(queryForList.get(i).get("MOVEENTRANCE")),cellFormat));
            	sheet.addCell(new Label(14,StartRowIdx,  setValue(queryForList.get(i).get("MOVEORDER")),cellFormat));
            	sheet.addCell(new Label(15,StartRowIdx,  setValue(queryForList.get(i).get("TRANSPORTREQUIRE")),cellFormat));
            	sheet.addCell(new Label(16,StartRowIdx,  "",cellFormat));
                SheetNumber++;
                StartRowIdx++;

                if (SheetNumber == 40 || SheetNumber == 41)
                {
                	//createWorkbook.CopyBackUpSheetToNewSheet();
                    SheetNumber = 0;
                    StartRowIdx = 8;
                    sheet.addCell(new Label(3, 3, setValue(queryForList.get(0).get("DEPARTMENT")),cellFormat));
        	        sheet.addCell(new Label(16, 2, setValue(queryForList.get(0).get("SHIPREQUESTNAME")),cellFormat));
        	        sheet.addCell(new Label(16, 3, new SimpleDateFormat("yyyy-MM-dd").format(TimeStampUtil.getCurrentTimestamp()),cellFormat));
                }
            }
	        workbook.close();
	        createWorkbook.write();
	        createWorkbook.close();
	        
			/*
			 * FileInputStream inputStream = null; inputStream = new FileInputStream(file);
			 * XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
			 * 
			 * XSSFSheet createSheet = xssfWorkbook.createSheet("First Sheet");
			 * 
			 * createSheet.
			 */
	}
	
	
	// 出库List
	public static void createTodayStockOutExcel(String siteName, File file)
            throws WriteException, IOException, BiffException {
		//通过订单去抓数据，有哪些信息
		String sql = "SELECT 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" + 
				" yd.LASTEVENTTIME \r\n" + 
				" FROM YARD_DEVICE yd\r\n" + 
				" WHERE SITENAME = 'B20'\r\n" + 
				" AND STOCKSTATE = 'StockOut'\r\n" + 
				" AND TRUNC(SHIPTIME) = TRUNC(SYSDATE)";
			HashMap<String,Object> hashMap = new HashMap<String,Object> ();
			hashMap.put("SITENAME", siteName);
			List<Map<String,Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, hashMap);
			if (queryForList == null || queryForList.size() <= 0) return;
		 	Workbook workbook = Workbook.getWorkbook(file);
		 	WritableWorkbook createWorkbook = Workbook.createWorkbook(file, workbook);
		 	//createWorkbook.copySheet("Template","First Sheet", 0);
			//createWorkbook.copySheet(0,"First Sheet", 1);
	        //WritableSheet sheet = createWorkbook.createSheet("First Sheet", 0);
		 	WritableSheet sheet = createWorkbook.getSheet("Template");
	        
//            excelMng.SetValue(4, 4, mpList[i].DEPARTMENT);//部门
//            excelMng.SetValue(3, 17, mpList[i].SHIPREQUESTNAME.ToString());//单号
//            excelMng.SetValue(4, 17, DateTime.Now.ToString("yyyy-MM-dd"));//创建日期
	        jxl.format.CellFormat cellFormat = sheet.getCell(6, 7).getCellFormat();
	        sheet.addCell(new Label(3, 3, setValue(queryForList.get(0).get("DEPARTMENT")),cellFormat));
	        sheet.addCell(new Label(16, 2, setValue(queryForList.get(0).get("SHIPREQUESTNAME")),cellFormat));
	        sheet.addCell(new Label(16, 3, new SimpleDateFormat("yyyy-MM-dd").format(TimeStampUtil.getCurrentTimestamp()),cellFormat));
	        
	        
            int SheetNumber = 0;
            int StartRowIdx = 7;

            for (int i = 0; i < queryForList.size(); i++)
            {
            	sheet.addCell(new Label(2,StartRowIdx,  setValue(queryForList.get(i).get("PONO")),cellFormat));
            	sheet.addCell(new Label(3,StartRowIdx,  setValue(queryForList.get(i).get("SHIPREQUESTNAME")),cellFormat));
            	sheet.addCell(new Label(4,StartRowIdx,  setValue(queryForList.get(i).get("DEVICE")),cellFormat));
            	sheet.addCell(new Label(5,StartRowIdx,  setValue(queryForList.get(i).get("DEVICENAME")),cellFormat));
            	sheet.addCell(new Label(6,StartRowIdx,  setValue(queryForList.get(i).get("CASENO")),cellFormat));
            	sheet.addCell(new Label(7,StartRowIdx,  setValue(queryForList.get(i).get("LENGTH")),cellFormat));
            	sheet.addCell(new Label(8,StartRowIdx,  setValue(queryForList.get(i).get("WIDTH")),cellFormat));
            	sheet.addCell(new Label(9,StartRowIdx,  setValue(queryForList.get(i).get("HEIGHT")),cellFormat));
            	sheet.addCell(new Label(10,StartRowIdx,  setValue(queryForList.get(i).get("CROSS")),cellFormat));
            	sheet.addCell(new Label(11,StartRowIdx,  setValue(queryForList.get(i).get("NET")),cellFormat));
            	sheet.addCell(new Label(12,StartRowIdx,  setValue(queryForList.get(i).get("MOVEDATE")),cellFormat));
            	sheet.addCell(new Label(13,StartRowIdx,  setValue(queryForList.get(i).get("MOVEENTRANCE")),cellFormat));
            	sheet.addCell(new Label(14,StartRowIdx,  setValue(queryForList.get(i).get("MOVEORDER")),cellFormat));
            	sheet.addCell(new Label(15,StartRowIdx,  setValue(queryForList.get(i).get("TRANSPORTREQUIRE")),cellFormat));
            	sheet.addCell(new Label(16,StartRowIdx,  "",cellFormat));
                SheetNumber++;
                StartRowIdx++;

                if (SheetNumber == 40 || SheetNumber == 41)
                {
                	//createWorkbook.CopyBackUpSheetToNewSheet();
                    SheetNumber = 0;
                    StartRowIdx = 8;
                    sheet.addCell(new Label(3, 3, setValue(queryForList.get(0).get("DEPARTMENT")),cellFormat));
        	        sheet.addCell(new Label(16, 2, setValue(queryForList.get(0).get("SHIPREQUESTNAME")),cellFormat));
        	        sheet.addCell(new Label(16, 3, new SimpleDateFormat("yyyy-MM-dd").format(TimeStampUtil.getCurrentTimestamp()),cellFormat));
                }
            }
	        workbook.close();
	        createWorkbook.write();
	        createWorkbook.close();
	        
			/*
			 * FileInputStream inputStream = null; inputStream = new FileInputStream(file);
			 * XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
			 * 
			 * XSSFSheet createSheet = xssfWorkbook.createSheet("First Sheet");
			 * 
			 * createSheet.
			 */
	}
	
	
	// 入库List
	public static void createTodayStockInExcel(String siteName, File file)
            throws WriteException, IOException, BiffException {
		//通过订单去抓数据，有哪些信息
		String sql = "SELECT yd.PONO,\r\n" + 
				" yd.RECEIVEREQUESTNAME,\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" + 
				" yd.LASTEVENTTIME \r\n" + 
				" FROM YARD_DEVICE yd\r\n" + 
				" WHERE SITENAME = 'B20'\r\n" + 
				" AND STOCKSTATE = 'Stocked'\r\n" + 
				" AND TRUNC(STOCKTIME) = TRUNC(SYSDATE)";
			HashMap<String,Object> hashMap = new HashMap<String,Object> ();
			hashMap.put("SITENAME", siteName);
			List<Map<String,Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, hashMap);
			if (queryForList == null || queryForList.size() <= 0) return;
		 	Workbook workbook = Workbook.getWorkbook(file);
		 	WritableWorkbook createWorkbook = Workbook.createWorkbook(file, workbook);
		 	//createWorkbook.copySheet("Template","First Sheet", 0);
			//createWorkbook.copySheet(0,"First Sheet", 1);
	        //WritableSheet sheet = createWorkbook.createSheet("First Sheet", 0);
		 	WritableSheet sheet = createWorkbook.getSheet("Template");
	        
//	            excelMng.SetValue(4, 4, mpList[i].DEPARTMENT);//部门
//	            excelMng.SetValue(3, 17, mpList[i].SHIPREQUESTNAME.ToString());//单号
//	            excelMng.SetValue(4, 17, DateTime.Now.ToString("yyyy-MM-dd"));//创建日期
	        jxl.format.CellFormat cellFormat = sheet.getCell(6, 7).getCellFormat();
	        sheet.addCell(new Label(3, 3, setValue(queryForList.get(0).get("DEPARTMENT")),cellFormat));
	        sheet.addCell(new Label(16, 2, setValue(queryForList.get(0).get("RECEIVEREQUESTNAME")),cellFormat));
	        sheet.addCell(new Label(16, 3, new SimpleDateFormat("yyyy-MM-dd").format(TimeStampUtil.getCurrentTimestamp()),cellFormat));
	        
	        
            int SheetNumber = 0;
            int StartRowIdx = 7;

            for (int i = 0; i < queryForList.size(); i++)
            {
            	sheet.addCell(new Label(2,StartRowIdx,  setValue(queryForList.get(i).get("PONO")),cellFormat));
            	sheet.addCell(new Label(3,StartRowIdx,  setValue(queryForList.get(i).get("RECEIVEREQUESTNAME")),cellFormat));
            	sheet.addCell(new Label(4,StartRowIdx,  setValue(queryForList.get(i).get("DEVICE")),cellFormat));
            	sheet.addCell(new Label(5,StartRowIdx,  setValue(queryForList.get(i).get("DEVICENAME")),cellFormat));
            	sheet.addCell(new Label(6,StartRowIdx,  setValue(queryForList.get(i).get("CASENO")),cellFormat));
            	sheet.addCell(new Label(7,StartRowIdx,  setValue(queryForList.get(i).get("LENGTH")),cellFormat));
            	sheet.addCell(new Label(8,StartRowIdx,  setValue(queryForList.get(i).get("WIDTH")),cellFormat));
            	sheet.addCell(new Label(9,StartRowIdx,  setValue(queryForList.get(i).get("HEIGHT")),cellFormat));
            	sheet.addCell(new Label(10,StartRowIdx,  setValue(queryForList.get(i).get("CROSS")),cellFormat));
            	sheet.addCell(new Label(11,StartRowIdx,  setValue(queryForList.get(i).get("NET")),cellFormat));
            	sheet.addCell(new Label(12,StartRowIdx,  setValue(queryForList.get(i).get("MOVEDATE")),cellFormat));
            	sheet.addCell(new Label(13,StartRowIdx,  setValue(queryForList.get(i).get("MOVEENTRANCE")),cellFormat));
            	sheet.addCell(new Label(14,StartRowIdx,  setValue(queryForList.get(i).get("MOVEORDER")),cellFormat));
            	sheet.addCell(new Label(15,StartRowIdx,  setValue(queryForList.get(i).get("TRANSPORTREQUIRE")),cellFormat));
            	sheet.addCell(new Label(16,StartRowIdx,  "",cellFormat));
                SheetNumber++;
                StartRowIdx++;

                if (SheetNumber == 40 || SheetNumber == 41)
                {
                	//createWorkbook.CopyBackUpSheetToNewSheet();
                    SheetNumber = 0;
                    StartRowIdx = 8;
                    sheet.addCell(new Label(3, 3, setValue(queryForList.get(0).get("DEPARTMENT")),cellFormat));
        	        sheet.addCell(new Label(16, 2, setValue(queryForList.get(0).get("RECEIVEREQUESTNAME")),cellFormat));
        	        sheet.addCell(new Label(16, 3, new SimpleDateFormat("yyyy-MM-dd").format(TimeStampUtil.getCurrentTimestamp()),cellFormat));
                }
            }
	        workbook.close();
	        createWorkbook.write();
	        createWorkbook.close();
	        
			/*
			 * FileInputStream inputStream = null; inputStream = new FileInputStream(file);
			 * XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
			 * 
			 * XSSFSheet createSheet = xssfWorkbook.createSheet("First Sheet");
			 * 
			 * createSheet.
			 */
	}
	
	
	public static String setValue(Object obj) {
		if (obj == null) return "";
		return obj.toString();
	}
	
	//发送邮件公共方法
	public static void SendEmail (String fileName,String fileUrl) {
        try {
//        	创建邮件对象
        	Session session = JavaMailUntil.createSession();
            MimeMessage message = new MimeMessage(session);
            message.setSubject("出库单邮件");
            
            message.setFrom(new InternetAddress("17868840037@163.com"));
          
//          String[] receiverList= preStockOutInfo2.get(0).getReceiver().split(",");
            String sql = "SELECT ym.SITENAME , ym.NUM ,ym.NAME ,ym.JOBDESCRIPTION,ym.JOBTIME ,ymd.CREATUSER ,ymd.CREATTIME ,ymd.RECEIVER ,ymd.DESCRIPTION  ,ymd.LINE\r\n" + 
            		"FROM YARD_MAIL ym,YARD_MAILDETAIL ymd \r\n" + 
            		"WHERE ym.NUM =ymd.NUM AND ymd .num = 'PreStockOutMail'";
            
            HashMap<String, Object> hashMap = new HashMap<String,Object> ();
          List<Map<String, Object>> queryForList = IDMFrameServiceProxy.getSqlTemplate().queryForList(sql, hashMap);
          Address[] addresses = new Address[queryForList.size()];
          for (int i = 0; i < queryForList.size() ; i++) {
              addresses[i] = new InternetAddress(queryForList.get(i).get("RECEIVER").toString());
          }
          message.setRecipients(Message.RecipientType.TO,addresses);
            
            Multipart mainPart = new MimeMultipart();
            StringBuffer mailText = new StringBuffer();
            mailText.append("hello All: ");
            // 创建一个包含HTML内容的MimeBodyPart
            BodyPart html = new MimeBodyPart();
            html.setContent(mailText.toString(), "text/html; charset=utf-8");
            mainPart.addBodyPart(html);
            message.setContent(mainPart);

            html = new MimeBodyPart();
            String s = MimeUtility.decodeText(fileName);
            html.setFileName(s);
           // html.setDataHandler(new DataHandler(new ByteArrayDataSource(Files.readAllBytes(Paths.get("D:"+ fs + "attachment")),"application/octet-stream")));

            DataSource source = new FileDataSource(fileUrl);
            //设置附件的数据处理器
            html.setDataHandler(new DataHandler(source));
            // 设置HTML内容     建立第一部分： 文本正文
            //html.setContent(mailText.toString(), "text/html; charset=utf-8");
            mainPart.addBodyPart(html);
            // 将MiniMultipart对象设置为邮件内容   建立第二部分：附件

            Transport.send(message);
        } catch (Exception e) {
            e.printStackTrace();
        }
	}
	
}
