package UIB.UIB03;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Iterator;
import java.util.HashMap;
import java.util.Set;
import java.util.HashSet;
import java.util.Map;

import org.springframework.jdbc.support.rowset.SqlRowSet;
import UIB.COM.ComDBProcedure;
import UIB.COM.XmlSqlParsersFactory;
import UIB.UIB03.ZBS.DataRowList;
import UIB.UIB03.ZBS.IQltyGuaranteeEngine;
import UIB.UIB03.ZBS.QltyCodeDef;
import UIB.UIB03.ZBS.QltyGuaranteeEngine;
import UIB.UIB03.ZBS.RowSetConverter;
import UIB.UIB03.ZBS.MapConverter;
import CoreFS.SA01.CoreIComponent;
import CoreFS.SA06.CoreReturnObject;

/**
 * @desc   质保书发行主要包含质保书引擎、质保书构造器、字段标准定义，以及所衍生的行对象数据结构、行对象转换器、质保书行列转换、EXCEL风格设置器
 *		   质保书引擎：获取模板以及填充模板方式
 *		   质保书构造器: 按照不同类型模板进行填充
 *         字段标准定义：对质保书中所需要的字段名，特殊文字及符号等进行定义
 *         行对象数据结构：将查询出来的行对象转换为指定类型
 *         行对象转换器：将查询出来的RowSet集合按照不同的钢卷号（包号）进行转换
 *         质保书行列转换：质保书上的A、AB行列等转换为与jxl对应的行或者列
 *         EXCEL风格设置器：质保书可否读写、修改，以及单元格的颜色、字体大小、自动换行、自动缩小字体等属性设置
 * @author meiguiping
 * @date   2010 9:23:12 PM
 */
public class UIB030320	extends	CoreIComponent
{	
	
	/**
	 * @desc  质保打印次数
	 * @param tmplNo	模板号
	 * @param cretNo	质保书编号
	 * @return
	 */

	public  CoreReturnObject  PrintCnt(String print_ID, String cretNo)
	  {
	    CoreReturnObject  cro      =  new CoreReturnObject();
	    Connection      con      =   this.getDao("KgDao").getConnection();
	    String printCnt = null;
	    try
	    {
	      printCnt  =  "UIB030320_ZBS_02.UPDATES";
	      cro  =  this.getDao("KgDao").ExcuteNonQuery(XmlSqlParsersFactory.getSql(printCnt) , new Object[]{print_ID ,cretNo });//次数
	    }
	    catch(Exception ex)
	    {
	      ex.printStackTrace();
	    }
	    return cro;
	  }
	/**
	 * @desc  质保书发行预览
	 * @param tmplNo	模板号
	 * @param cretNo	质保书编号
	 * @param prodLine	产线
	 * @parm  language	语种
	 * @return
	 * @throws SQLException 
	 */
	public	CoreReturnObject	downLoad(String tmplNo , String cretNo , String prodLine , String invNo, String sendTP) throws SQLException
	{
		CoreReturnObject	cro			=	new CoreReturnObject();
		Connection			con			= 	this.getDao("KgDao").getConnection();
		
		PreparedStatement		pstm	=   null;
		SqlRowSet			srs			=	null;
		String				headSQL		=	null;//表头
		String				chemSQL		=	null;//成分
		String				qltySQL		=	null;//性能
		//String				printCnt	=	null;//次数
		
		
		try
		{
			if("热轧".equals(prodLine.trim()))
			{
				headSQL		=	"UIB030320_HOST_HEAD.SELECT";
				chemSQL		=	"UIB030320_HOST_CHEM.SELECT";
				qltySQL		=	"UIB030320_HOST_QLTY.SELECT";
			}
			else if("开平".equals(prodLine.trim()))
			{
				headSQL		=	"UIB030320_BANJIA_HEAD.SELECT";
				if("KF".equals(invNo.substring(0,2)))
				{
					chemSQL		=	"UIB030320_BANJIA_CHEM_KF.SELECT";
					qltySQL		=	"UIB030320_BANJIA_QLTY_KF.SELECT";
				}
				else
				{
					chemSQL		=	"UIB030320_BANJIA_CHEM.SELECT";
					qltySQL		=	"UIB030320_BANJIA_QLTY.SELECT";
				}
				
			}
			else if("酸轧".equals(prodLine.trim()))
			{
				headSQL		=	"UIB030320_SUAN_HEAD.SELECT";
				chemSQL		=	"UIB030320_SUAN_CHEM.SELECT";
				qltySQL		=	"UIB030320_SUAN_QLTY.SELECT";
				//printCnt	=	"UIB030320_ZBS_02.UPDATES";
			}
			else if("连退".equals(prodLine.trim()))
			{
				headSQL		=	"UIB030320_LIAN_HEAD.SELECT";
				chemSQL		=	"UIB030320_LIAN_CHEM.SELECT";
				qltySQL		=	"UIB030320_LIAN_QLTY.SELECT";
				//printCnt	=	"UIB030320_ZBS_02.UPDATES";
			}
			else if("板加(非)".equals(prodLine.trim()))
			{
				headSQL		=	"UIB030320_HOTBJ_HEAD.SELECT";
				chemSQL		=	"UIB030320_HOTBJ_CHEM.SELECT";
				qltySQL		=	"UIB030320_HOTBJ_QLTY.SELECT";
				//printCnt	=	"UIB030320_ZBS_02.UPDATES";
			}
			
			

			//1、获取查询数据，转换为指定数据类型
			///表头
			RowSetConverter		rsc		=	new RowSetConverter();
			MapConverter 		mc		= new MapConverter();
			cro	=	this.getDao("KgDao").ExecutequeryForRowSet(XmlSqlParsersFactory.getSql(headSQL) , new Object[]{cretNo });//表头
			srs							=	(SqlRowSet)cro.getResult();
			HashMap 		map			=	rsc.getHeadMap(srs);
			///成分
			cro	=	this.getDao("KgDao").ExecutequeryForRowSet(XmlSqlParsersFactory.getSql(chemSQL) , new Object[]{cretNo} );//成分
			List list 	=	this.getDao("KgDao").ExcuteQueryReturnList(XmlSqlParsersFactory.getSql(chemSQL) , new Object[]{cretNo} );//成分
			
			//srs							=	(SqlRowSet)set;
			srs							=	(SqlRowSet)cro.getResult();
			ArrayList chemAl=	rsc.getDataRowList(srs, QltyCodeDef.CHEM_CD);
			//ArrayList chemAl= mc.getDataRowList(list, QltyCodeDef.CHEM_CD);
			int		chemLen				=	chemAl.size();
			DataRowList[] 	chemList	=	new DataRowList[chemLen];
			for(int i = 0; i < chemLen; i++)
			{
				chemList[i]	=	(DataRowList)chemAl.get(i);
			}
			if(chemList.length == 0) {
				chemList = null;
				//cro.setV_errMsg("质保书异常：\n缺少成分信息，可能是材质判定不合格或者综合判定不合格！");
				cro.setV_errCode(5);
				cro.setResult("质保书异常：\n缺少成分信息，可能是材质判定不合格或者综合判定不合格！");
				return cro;
			}
			///材质
			cro	=	this.getDao("KgDao").ExecutequeryForRowSet(XmlSqlParsersFactory.getSql(qltySQL) , new Object[]{cretNo});//材质
			srs							=	(SqlRowSet)cro.getResult();
			ArrayList qltyAl=	rsc.getDataRowList(srs , QltyCodeDef.QLTY_CD);
			int				qltyLen		=	qltyAl.size();
			DataRowList[]   qltyList	=	new DataRowList[qltyLen];
			for(int i = 0; i < qltyLen; i++)
			{
				qltyList[i]	=	(DataRowList)qltyAl.get(i);
			}
			
			///2、获取模板，转换为输入流
			pstm	=  con.prepareStatement(XmlSqlParsersFactory.getSql("UIB030310_03.SELECT"));
			pstm.setString(1, tmplNo);
			
			/*    
			Connection			con1		= 	dbso.getDataSource2().getConnection();      
			String sql = "SELECT TMPL_EXCEL FROM tbb03_grte_tmpl WHERE tmpl_no='B00001'";            
			pstm = con1.prepareStatement(sql);    */        

			ResultSet				rs	=	pstm.executeQuery();
			if(rs.next())
			{
				//java.sql.Blob blob = rs.getBlob("TMPL_EXCEL"); 
				//oracle.sql.BLOB  blob   =   (oracle.sql.BLOB)rs.getBlob("TMPL_EXCEL");
				//InputStream 	is = blob.getBinaryStream(); 
				//InputStream		is		=	new ByteArrayInputStream(blob.getBytes(1	, (int)blob.length()));
				InputStream		is		=	new ByteArrayInputStream(this.dbproxy.getLobHandler().getBlobAsBytes(rs , "TMPL_EXCEL"));
			///3、调用引擎，返回输出流，生成质保书
				IQltyGuaranteeEngine		engine	=	new QltyGuaranteeEngine(tmplNo , map , chemList , qltyList);
				ByteArrayOutputStream		baos	=	(ByteArrayOutputStream)engine.getOutputStream(is);
				
			///发送质保书到外网
				if("SEND".equals(sendTP))
				{
					sendWWW(cretNo ,prodLine , baos);
				}
				
				baos.close();
				is.close();
				rs.close();
				//注意,必须关闭流后才能从内存中获取字节流，否则获取到的会为0字节
//				System.out.println("##############################################");
				cro			=	new CoreReturnObject();
				cro.setResult(baos.toByteArray());
//				cro.setResult("dd");		
				//删除二维码图片
				if(map.get("cdPath") != null){
					File	file	=	new File(map.get("cdPath").toString());
					file.delete();
				}
			}
			pstm.close();
			con.close();
			//cro	=	this.getDao("KgDao").ExcuteNonQuery(XmlSqlParsersFactory.getSql(printCnt) , new Object[]{cretNo });//次数+1
		}
		catch(Exception ex)
		{
			cro.setV_errMsg("质保书异常：\n"+ex.getMessage());
			ex.printStackTrace();
		} finally {  
			try{
				//添加了最终关闭连接
				if (pstm != null) {
					pstm.close();
				}
				if (con != null && !con.isClosed()) {
					con.close();
				}
			} catch (Exception e) {
			}
		}
		return cro;
	}//downLoad  end
	
	/**
	 * @desc  质保书审核，区分板加、热轧（冷轧）
	 * @param prodLine		产线区分
	 * @parm  cretNO   	    质保书号
	 * @param checkTp		审核或者取消审核
	 * @param regNm			操作人
	 * @return
	 */
	public 	CoreReturnObject	ZBSCheckTP(String prodLine , ArrayList  cretNO ,  String checkTp , String regNm  )throws Exception
	{
		CoreReturnObject	cro		=	new CoreReturnObject();

		//质保书审核
		if(cretNO != null)
		{
			String	sql	=	"";
			if("热轧".equals(prodLine.trim()) || "酸轧".equals(prodLine.trim()) || "连退".equals(prodLine.trim()) || "板加(非)".equals(prodLine.trim()))//热轧，冷轧，板加(非)
			{
				sql	=	"UIB030320_01.CALL";
			}
			else if("开平".equals(prodLine.trim()))
			{
				sql	=	"UIB030320_02.CALL";
			}
			ZBSCheck(cretNO , checkTp , regNm , sql);
		}

		return cro;
	}
	
	/**
	 * @desc  质保书审核
	 * @param cretNO		质保书号
	 * @param checkTp		审核或者取消审核
	 * @param regNm			操作人
	 */
	public void ZBSCheck(ArrayList  cretNO   , String checkTp , String regNm , String  sql)	throws Exception
	{
		ComDBProcedure		cdbp	=		new ComDBProcedure();	
		HashMap				map		=		null;
		for(int i = 0; i < cretNO.size(); i++)
		{
			map	=	new HashMap();
			map.put("i1", cretNO.get(i));
			map.put("i2", checkTp);
			map.put("i3", regNm);
			map.put("o4", "");
			cdbp.doXmlProcedure(sql, map);
		}
	}
	/**
	 * @desc				热轧（冷轧）、板加，发送质保书到外网
	 * @param cretNO		质保书号
	 * @param baos			质保书输入流
	 */
	public void sendWWW(String cretNO , String prodLine ,ByteArrayOutputStream	baos)	throws Exception
	{
		///增加信息到ZL_ZBS_DETAIL_DATA和ZL_ZBS_MAIN_DATA表
		ComDBProcedure		cdbp	=		new ComDBProcedure();	
		HashMap				map		=		map	=	new HashMap();
		String				sqlID	=		"";
		if("热轧".equals(prodLine) || "酸轧".equals(prodLine)|| "连退".equals(prodLine) || "板加(非)".equals(prodLine))
		{
			sqlID	=	"UIB030320_HOST_01.CALL";
		}
		else if("开平".equals(prodLine))
		{
			sqlID	=	"UIB030320_BANJA_02.CALL";
		}
		else 
			return;
		
		//填入其它信息
		map.put("i1", cretNO);
		map.put("o2", "");
		cdbp.doXmlProcedure(sqlID, map);
		
		///添加质保书EXCEL信息
		Connection				con	=	null;
		PreparedStatement		pstm	=	null;
		try
		{
//			con	= this.getDao("KgDao").getConnection();
					
			String	driverClassName	=	"oracle.jdbc.driver.OracleDriver";
			Class.forName(driverClassName);
			//连接数据库
			String	url	=	"jdbc:oracle:thin:@172.16.0.109:1521:mestest";
			String	userName	=	"xsuser";
			String	userPsw		=	"xsuser";
			
//			this.getDao("KgDao").ExceuteNonQueryForBlobList(XmlSqlParsersFactory.getSql("UIB030320_ZBS_01.UPDATES")	, new Object[]{bData,tmplNo });
			con	=	DriverManager.getConnection(url , userName , userPsw);
			
			con.setAutoCommit(false);
//									
//			//填入质保书EXCEL
			pstm	=	con.prepareStatement(XmlSqlParsersFactory.getSql("UIB030320_ZBS_01.UPDATES"));
			this.dbproxy.getLobHandler().getLobCreator().setBlobAsBytes(pstm, 1, baos.toByteArray());
			pstm.setString(2, cretNO);
			pstm.executeUpdate();
//			pstm.setString(1, cretNO);
//			ResultSet				rs		=	pstm.executeQuery();
//			
//			if(rs.next())
//			{
//				oracle.sql.BLOB  blob   =   (oracle.sql.BLOB)rs.getBlob("ZBSXLS");
//				OutputStream	os		=	blob.getBinaryOutputStream();
//				
//				InputStream		is 		=	new ByteArrayInputStream(baos.toByteArray());
//				
//				byte[] bb				=	new byte[blob.getBufferSize()];
//				int	len	=	0;
//				while((len	= is.read(bb)) != -1)
//				{
//					os.write(bb , 0 , len);
//				}
//			
//				is.close();
//				os.flush();
//				os.close();
				con.commit();
//			}
//			rs.close();
		}catch(Exception ex)
		{
			//2015-09-23 添加：若质保书内容发送到外网失败，则修改质保书为未发送 状态 
			String sql5 = XmlSqlParsersFactory.getSql("UIB030320_ZBS_05.UPDATES");
			this.getDao("KgDao").ExcuteNonQuery(sql5, new Object[]{cretNO});
			
			throw new Exception("质保书发送异常，请重新发送！\n"+ex.getMessage());
//			ex.printStackTrace();
		}
		finally
		{
			try
			{
				if(pstm != null)
					pstm.close();
				if(con !=null)
					con.close();
			}catch(Exception e)
			{
				throw new Exception("数据库连接关闭异常！\n"+e.getMessage());
			}
		}
	}//sendWWW end
	
}