package com.fr.cm;
/*
 * 库存分析-库龄分析表
 * */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;

import com.fr.base.FRContext;
import com.fr.base.Parameter;
import com.fr.data.AbstractTableData;
//import com.fr.base.Parameter;

public class KLTest extends AbstractTableData {
	private static final long serialVersionUID = -1L;
	// 列名数组，保存程序数据集所有列名
	private String[] columnNames = null;
	// 定义程序数据集的列数量
	private int columnNum = 17;
	// 保存查询表的实际列数量
	private int colNum = 0;
	// 保存查询得到列值
	@SuppressWarnings("rawtypes")
	private ArrayList valueList = null;
	private String ck = null;

	// 构造函数，定义表结构
	public KLTest() {
		// 定义参数
		setDefaultParameters(new Parameter[] { new Parameter("ck") });
		// 定义程序数据集列名
		columnNames = new String[17];
		columnNames[0] = "LA004";
		columnNames[1] = "dbdh";
		columnNames[2] = "LA009";
		columnNames[3] = "LA011";
		columnNames[4] = "LA012";
		columnNames[5] = "LA013";
		columnNames[6] = "LA001";
		columnNames[7] = "LA010";
		columnNames[8] = "cfrq";
		columnNames[9] = "MB002";
		columnNames[10] = "MC002";
		columnNames[11] = "MB143";
		columnNames[12] = "TC006";
		columnNames[13] = "MV002";
		columnNames[14] = "TC004";
		columnNames[15] = "MA002";
		columnNames[16] = "TCI01";
	}

	// 实现其他四个方法
	public int getColumnCount() {
		return columnNum;
	}

	public String getColumnName(int columnIndex) {
		return columnNames[columnIndex];
	}

	public int getRowCount() {
		init();
		return valueList.size();
	}

	public Object getValueAt(int rowIndex, int columnIndex) {
		init();
		if (columnIndex >= colNum) {
			return null;
		}
		return ((Object[]) valueList.get(rowIndex))[columnIndex];
	}

	// 准备数据
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public void init() {
		// 确保只被执行一次
		if (valueList != null) {
			return;
		}
		ck = parameters[0].getValue().toString();
		// 保存得到的结果集
		valueList = new ArrayList();
		// 下面开始建立数据库连接，按照刚才的SQL语句进行查询
		Connection conn = this.getConnection();
		try {
			// 查询库存
			String sql = "select MC001 from INVLA where MC007>0 and MC002='"+ck+"' order by MC001";
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			// 用对象保存数据
			Object[] objArray = null;
			while (rs.next()) {
				String MC001 = rs.getString("MC001");
				Statement st2 = conn.createStatement();
				// 查询当前物料出库金额
				ResultSet rs2 = st2.executeQuery(" select sum(LA012,0) as chu,a.LA001,a.LA009 from INVLA where LA001='"
								+ MC001	+ "' and LA009='" + ck + "'");
				while (rs2.next()){
					String LA001 = rs2.getString("LA001");
					String LA009 = rs2.getString("LA009");
					int chu = rs2.getInt("chu");
					int a = 0;
					boolean b = false;
					Statement st3 = conn.createStatement();
					// 查询入库记录
					ResultSet rs3 = st3.executeQuery(" select * from INVLA where LA001='"
								+ LA001 + "' and LA009 ='" + LA009 + "'");
					ResultSetMetaData rsmd = rs3.getMetaData();
					colNum = rsmd.getColumnCount();
					while (rs3.next()) {
						objArray = new Object[colNum];
						a = a + rs3.getInt("LA011");
						if (a > chu) {
							if (b == false) {
								objArray[0] = rs3.getObject(1);
								objArray[1] = rs3.getObject(2);
								objArray[2] = rs3.getObject(3);
								objArray[3] = a - chu;
								objArray[4] = rs3.getObject(5);
								objArray[5] = 100 * rs3.getDouble("LA012") * (a - chu) / 100;
								objArray[6] = rs3.getObject(7);
								objArray[7] = rs3.getObject(8);
								objArray[8] = rs3.getObject(9);
								objArray[9] = rs3.getObject(10);
								objArray[10] = rs3.getObject(11);
								objArray[11] = rs3.getObject(12);
								objArray[12] = rs3.getObject(13);
								objArray[13] = rs3.getObject(14);
								objArray[14] = rs3.getObject(15);
								objArray[15] = rs3.getObject(16);
								objArray[16] = rs3.getObject(17);
								b = true;
							} else {
								for (int i = 0; i < colNum; i++) {
									objArray[i] = rs3.getObject(i + 1);
								}
							}
							valueList.add(objArray);
						}
					}
					rs3.close();
					st3.close();
				}
				rs2.close();
				st2.close();
			}
			// 释放数据库资源
			rs.close();
			stmt.close();
			conn.close();
			// 打印一共取到的数据行数量
			FRContext.getLogger().info("Query SQL of ParamTableDataDemo: \n" + valueList.size() + " rows selected");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 获取数据库连接 driverName和 url 可以换成您需要的
	public Connection getConnection() {

		String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
		String url = "jdbc:sqlserver://localhost:1433;DatabaseName=test";
		String username = "sa";
		String password = "22222";
		Connection con = null;
		try {
			Class.forName(driverName);
			con = DriverManager.getConnection(url, username, password);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return con;
	}

	// 释放一些资源，因为可能会有重复调用，所以需释放valueList，将上次查询的结果释放掉
	public void release() throws Exception {
		super.release();
		this.valueList = null;
	}
}