package UIM;

import java.io.StringWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.w3c.dom.Document;
import org.w3c.dom.Element;

import UIB.COM.XmlSqlParsersFactory;
import UIM.UIM01.BEANS.YardArea;

import CoreFS.SA01.CoreIComponent;
import CoreFS.SA06.CoreReturnObject;

/**
 * 冷轧原料库库存现况查询
 * 
 * @author siy
 * @date 2010-09-02
 */
public class UIM010070 extends CoreIComponent {
	/**
	 * 查询库存区域信息
	 * 
	 * @param areaType
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject queryYardArea(Integer areaType) throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		// 查询区域信息
		String sql = XmlSqlParsersFactory.getSql("UIM010070_01.SELECT");

		PreparedStatement prepStat = this.getDao("KgDao").getPreparedStatement(
				sql);
		prepStat.setInt(1, areaType.intValue());
		ResultSet rs = prepStat.executeQuery();
		List<YardArea> list = new ArrayList<YardArea>();
		YardArea area = null;

		while (rs.next()) {
			area = new YardArea();
			area.setAreaNo(rs.getLong("area_no"));
			area.setAreaName(rs.getString("area_name"));

			// 查询区域下列信息
			StringBuffer sqlBuffer2 = new StringBuffer();
			sqlBuffer2
					.append("select distinct(y.clf_name||'-'||trim(to_char(y.clf_col,'00')))||'_'||y.area_no clf_name\n");
			sqlBuffer2.append(" from c_tbk08_coil_yard y\n");
			sqlBuffer2.append(" where area_no = ? order by clf_name\n");
			PreparedStatement prepStat2 = this.getDao("KgDao")
					.getPreparedStatement(sqlBuffer2.toString());
			prepStat2.setLong(1, area.getAreaNo());
			ResultSet rs2 = prepStat2.executeQuery();
			List<String> cols = new ArrayList<String>();
			while (rs2.next()) {
				cols.add(rs2.getString("clf_name"));
			}
			area.setYardCols(cols.toArray());
			rs2.close();
			Connection con2 = prepStat2.getConnection();
			prepStat2.close();
			con2.close();

			list.add(area);
		}

		rs.close();
		Connection con = prepStat.getConnection();
		prepStat.close();
		con.close();

		String xmlStr = areaToXMLString(list);
		cro.setResult(xmlStr);
		return cro;
	}

	/**
	 * 区域列信息转换成XML字符串
	 * 
	 * @param list
	 * @return
	 */
	private String areaToXMLString(List<YardArea> list) {
		String xmlStr = null;
		if (null != list && 0 < list.size()) {
			try {
				DocumentBuilderFactory dbf = DocumentBuilderFactory
						.newInstance();
				DocumentBuilder db = dbf.newDocumentBuilder();
				Document doc = db.newDocument();
				Element root = doc.createElement("areas");
				doc.appendChild(root);
				for (int i = 0; i < list.size(); i++) {
					YardArea area = list.get(i);
					Element areaEle = doc.createElement("area");
					areaEle
							.setAttribute("id", String
									.valueOf(area.getAreaNo()));
					areaEle.setAttribute("name", area.getAreaName());
					root.appendChild(areaEle);
					Object[] cols = area.getYardCols();
					if (null != cols) {
						for (int j = 0; j < cols.length; j++) {
							Element colElement = doc.createElement("col");
							colElement.setAttribute("name", cols[j].toString());
							areaEle.appendChild(colElement);
						}
					}
				}

				TransformerFactory transFactory = TransformerFactory
						.newInstance();
				Transformer transFormer = transFactory.newTransformer();
				transFormer.setOutputProperty(OutputKeys.ENCODING, "GB2312");
				DOMSource domSource = new DOMSource(doc);

				StringWriter sw = new StringWriter();
				StreamResult xmlResult = new StreamResult(sw);

				transFormer.transform(domSource, xmlResult);

				xmlStr = sw.toString();
			} catch (ParserConfigurationException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (TransformerException te) {
				te.printStackTrace();
			}
		}
		return xmlStr;
	}

	/**
	 * 查询每列垛位数及钢卷数
	 * 
	 * @param areaType
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject queryColCoils(Integer areaType) throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();

		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer
				.append("select y.clf_name||'-'||trim(to_char(y.clf_col,'00'))||'_'||y.area_no col_name,\n");
		sqlBuffer
				.append(" count(y.area_no)||'_('||count(y.coil_no)||')' col_count\n");
		sqlBuffer
				.append(" from c_tbk08_coil_yard y,c_tbk08_coil_yard_area a\n");
		sqlBuffer.append(" where y.area_no = a.area_no and a.area_type = ?\n");
		sqlBuffer.append(" group by y.clf_name,y.clf_col,y.area_no\n");

		cro = this.getDao("KgDao").ExcuteQuery(sqlBuffer.toString(),
				new Object[] { areaType.intValue() });
		return cro;
	}

	/**
	 * 查询钢卷总数及综合判定总数
	 * 
	 * @param areaType
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject queryCoilCount(Integer areaType)
			throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();

		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("select count(y.coil_no) coil_count,\n");
		sqlBuffer.append(" count(k.tot_dec_grd) tot_count from\n");
		sqlBuffer.append(" c_tbk08_coil_yard y, c_tbk02_coil_comm k,\n");
		sqlBuffer.append(" c_tbk08_coil_yard_area a\n");
		sqlBuffer.append(" where y.coil_no = k.coil_no\n");
		sqlBuffer.append(" and y.area_no = a.area_no\n");
		sqlBuffer.append(" and a.area_type = ?\n");

		cro = this.getDao("KgDao").ExcuteQuery(sqlBuffer.toString(),
				new Object[] { areaType.intValue() });

		return cro;
	}

	public CoreReturnObject queryYardRows(Integer areaType, Integer areaNo,
			String coilCol) {
		CoreReturnObject cro = new CoreReturnObject();

		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer
				.append("select y.clf_no,y.clf_name,y.clf_col,clf_row,y.clf_fl from\n");
		sqlBuffer.append(" c_tbk08_coil_yard y,c_tbk08_coil_yard_area a\n");
		sqlBuffer.append(" where y.area_no = a.area_no\n");
		sqlBuffer.append(" and a.area_type = ?\n");
		sqlBuffer.append(" and a.area_no = ?\n");
		sqlBuffer
				.append(" and y.clf_name||'-'||trim(to_char(y.clf_col,'00'))= ?\n");

		cro = this.getDao("KgDao")
				.ExcuteQuery(
						sqlBuffer.toString(),
						new Object[] { areaType.intValue(), areaNo.intValue(),
								coilCol });

		return cro;
	}

	/**
	 * 查询区域下一行的垛位中钢卷信息
	 * 
	 * @param areaType
	 * @param coilCol
	 * @return
	 */
	public CoreReturnObject queryYardRowCoils(Integer areaType, Integer areaNo,
			String coilCol) {
		CoreReturnObject cro = new CoreReturnObject();

		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("select y.clf_row,y.clf_fl,k.cur_load_loc, --位置\n");
		sqlBuffer.append(" k.coil_no,k.stl_grd,\n");
		sqlBuffer.append(" k.coil_outdia,k.coil_india,--外径，内径\n");
		sqlBuffer
				.append(" k.instr_coil_thk||'*'||k.instr_coil_wth it_iw,--订单规格\n");
		sqlBuffer.append(" k.coil_thk||'*'||k.coil_wth t_w,--实际规格\n");
		sqlBuffer.append(" k.act_wgt,k.devlmt_dtime,\n");
		sqlBuffer.append(" k.ord_no,k.ord_seq,\n");
		sqlBuffer
				.append(" decode(k.tot_dec_grd,'1','合格','2','不合格','3','次品','4','废品') tot_dec_grd,\n");
		sqlBuffer.append(" decode(k.ord_fl,'1','订单材','2','余材') ord_fl\n");
		sqlBuffer.append(" from c_tbk08_coil_yard y,\n");
		sqlBuffer.append(" c_tbk02_coil_comm k,\n");
		sqlBuffer.append(" c_tbk08_coil_yard_area a\n");
		sqlBuffer.append(" where y.coil_no = k.coil_no\n");
		sqlBuffer.append(" and y.area_no = a.area_no\n");
		sqlBuffer.append(" and a.area_type = ?\n");
		sqlBuffer.append(" and a.area_no = ?\n");
		sqlBuffer
				.append(" and y.clf_name||'-'||trim(to_char(y.clf_col,'00')) = ?\n");

		cro = this.getDao("KgDao")
				.ExcuteQuery(
						sqlBuffer.toString(),
						new Object[] { areaType.intValue(), areaNo.intValue(),
								coilCol });
		return cro;
	}

	/**
	 * 查询一行中编号最小的列
	 * 
	 * @param areaType
	 * @param areaNo
	 * @param coilCol
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject queryMinYardRow(Integer areaType, Integer areaNo,
			String coilCol) throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("select  min(clf_row) clf_row from\n");
		sqlBuffer.append(" c_tbk08_coil_yard y,c_tbk08_coil_yard_area a\n");
		sqlBuffer.append(" where y.area_no = a.area_no\n");
		sqlBuffer.append(" and a.area_type = ?\n");
		sqlBuffer.append(" and a.area_no = ?\n");
		sqlBuffer
				.append(" and y.clf_name||'-'||trim(to_char(y.clf_col,'00'))= ?\n");

		PreparedStatement pSta = this.getDao("KgDao").getPreparedStatement(
				sqlBuffer.toString());
		pSta.setInt(1, areaType.intValue());
		pSta.setInt(2, areaNo.intValue());
		pSta.setString(3, coilCol);
		ResultSet rs = pSta.executeQuery();

		int minRow = 1;

		if (rs.next()) {
			minRow = rs.getInt("CLF_ROW");
		}

		cro.setResult(minRow);
		rs.close();
		Connection con = pSta.getConnection();
		pSta.close();
		con.close();
		return cro;
	}
}