warehousemonthitems.java

我的系在线考试系统我的系在线考试系统我的系在线考试系统我的系在线考试系统我的系在线考试系统我的系在线考试系统我的系在线考试系统我的系在线考试系统

package net.intuitsoft.xeon.table.warehouse.month;

import intuitsoft.eclipse.table.common.DatabaseConnection;
import intuitsoft.eclipse.table.common.ToolKit;
import intuitsoft.eclipse.table.common.WriteLog;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

/**
 * <p>
 * Title: 零售连锁管理系统
 * </p>
 * <p>
 * Description: 零售业最佳应用
 * </p>
 * <p>
 * Copyright: Copyright (c) 1999 - 2004
 * </p>
 * <p>
 * Company: 直觉软件科技
 * </p>
 * 
 * @author: not attributable
 * @version 1.0 Since 2004-07-07
 */

public class WarehouseMonthItems extends WarehouseMonthItemsBean {
	public WarehouseMonthItems(String jndi) {
		super(jndi);
	}

	private String gdsStandard = "";

	public String getGdsStandard() {
		return this.gdsStandard;
	}

	public void setGdsStandard(String sV) {
		if (sV != null) {
			this.gdsStandard = sV;
		}
	}

	private String gdsName = "";

	private String gdsNo = "";

	private String unit = "";

	public void setUnit(String sV) {
		if (sV != null) {
			this.unit = sV;
		}
	}

	public String getUnit() {
		return this.unit;
	}

	public void setGdsNo(String sV) {
		if (sV != null) {
			this.gdsNo = sV;
		}
	}

	public String getGdsNo() {
		return this.gdsNo;
	}

	public void setGdsName(String sV) {
		if (sV != null) {
			this.gdsName = sV;
		}
	}

	public String getGdsName() {
		return this.gdsName;
	}

	public boolean buildReport(String warehouseMonthCode, String sMonth) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List l = this.getWarehouses();
		String ids = ToolKit.convertToSql(l);
		if (ids == null) {
			return true;
		}
		try {

			DatabaseConnection dc = new DatabaseConnection(getJndiName());
			conn = dc.getConnection();
			StringBuffer sb = new StringBuffer(1000);
			sb
					.append("insert into warehouseMonthItems(warehouseMonthCode,sMonth,warehouseCode,goodsID,prevShortQua,prevShortAmo,shortQua,shortAmo,opTime,shortPrice)");
			sb
					.append("select '"
							+ warehouseMonthCode
							+ "','"
							+ sMonth
							+ "',cu.warehouseCode,cu.goodsID,isnull(sum(cu.prevShortQua),0) as prevShortQua,isnull(sum(cu.prevShortAmo),0) as prevShortAmo,isnull(sum(cu.shortQua),0) as shortQua,isnull(sum(cu.shortAmo),0) as shortAmo,getDate(),CASE sum(cu.shortQua)  WHEN 0 THEN 0  ELSE isnull(sum(cu.shortAmo),0)/isnull(sum(cu.shortQua),1)  END from (");

			sb
					.append("select a.warehouseCode,a.goodsID,0 as prevShortQua, 0 as prevShortAmo, a.balQua as shortQua,a.balAmo as shortAmo from balance a where a.balQua <> 0");
			sb.append(" and a.warehouseCode in (" + ids + ")");
			sb.append(" union all ");
			sb
					.append("select b.warehouseCode,b.goodsID,b.shortQua as prevShortQua, b.shortAmo as prevShortAmo, 0 as shortQua,0 as shortAmo from warehouseMonth a,warehouseMonthItems b where a.warehouseMonthCode=b.warehouseMonthCode and a.sMonth =(select convert(nvarchar(10),max(sMonth),120) from warehouseMonthItems)  ");
			sb.append(" and b.warehouseCode in (" + ids + ")");

			sb.append(") as cu group by cu.warehouseCode,cu.goodsID");

			pstmt = conn.prepareStatement(sb.toString());
			pstmt.execute();
			return true;
		} catch (Exception ex) {
			ex.printStackTrace();
			new WriteLog(getJndiName(), 0, 10000, "core", "kernel", "database",
					this.getClass().getName() + ": " + ex.getMessage(), true, 0);
			return false;
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}

	public boolean delete(String warehouseMonthCode) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			DatabaseConnection dc = new DatabaseConnection(getJndiName());
			conn = dc.getConnection();
			String sql = "delete from warehouseMonthItems  where warehouseMonthCode=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, warehouseMonthCode);

			pstmt.execute();
			return true;
		} catch (Exception ex) {
			ex.printStackTrace();
			new WriteLog(getJndiName(), 0, 10000, "core", "kernel", "database",
					this.getClass().getName() + ": " + ex.getMessage(), true, 0);
			return false;
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}

	public List getWarehouses() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		LinkedList ll = null;
		try {
			DatabaseConnection dc = new DatabaseConnection(getJndiName());
			conn = dc.getConnection();
			String sql = "select warehouseCode from warehouse ";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			ll = new LinkedList();

			while (rs.next()) {
				ll.add(String.valueOf(rs.getInt("warehouseCode")));
			}

			return ll;
		} catch (Exception ex) {
			ex.printStackTrace();
			new WriteLog(getJndiName(), 0, 10000, "core", "kernel", "database",
					this.getClass().getName() + ": " + ex.getMessage(), true, 0);
			return ll;
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}

	public boolean updateDayAfter(int warehouseCode, String date, double qua,
			int goodsID) {
		Connection conn = null;
		Statement pstmt = null;

		try {
			DatabaseConnection dc = new DatabaseConnection(getJndiName());
			conn = dc.getConnection();
			pstmt = conn.createStatement();

			String sql = "insert into warehouseMonthItems (sMonth,warehouseCode,goodsID,prevShortQua,shortQua) select distinct sMonth,"
					+ warehouseCode
					+ ","
					+ goodsID
					+ ",0,0 from warehouseMonth where sMonth > '"
					+ date
					+ "' and sMonth  not in (select distinct sMonth FROM warehouseMonthItems where sMonth>'"
					+ date
					+ "' and warehouseCode="
					+ warehouseCode
					+ " AND goodsID =" + goodsID + " )";

			pstmt.addBatch(sql);
			sql = "update warehouseMonthItems set prevShortQua=prevShortQua+"
					+ qua
					+ ",prevShortAmo=prevShortQua*shortPrice,shortQua=shortQua+"
					+ qua + ",shortAmo=shortQua*shortPrice  where sMonth >'"
					+ date + "' and warehouseCode=" + warehouseCode
					+ " and  goodsID=" + goodsID;
			pstmt.addBatch(sql);

			pstmt.executeBatch();
			return true;
		} catch (Exception ex) {
			ex.printStackTrace();
			new WriteLog(getJndiName(), 0, 10000, "core", "kernel", "database",
					this.getClass().getName() + ": " + ex.getMessage(), true, 0);
			return false;
		} finally {
			try {

				if (pstmt != null) {
					pstmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}

	public boolean resetDayCur(int warehouseCode, String date, int goodsID) {
		if (!WarehouseMonth.isExistsByCode(getJndiName(), date)) {
			return true;
		}

		Connection conn = null;
		Statement pstmt = null;
		try {
			DatabaseConnection dc = new DatabaseConnection(getJndiName());
			conn = dc.getConnection();
			pstmt = conn.createStatement();
			String sql = "";

			sql = "delete from warehouseMonthItems where sMonth='" + date
					+ "' and warehouseCode=" + warehouseCode + " and goodsID="
					+ goodsID;
			pstmt.addBatch(sql);
			sql = "insert into warehouseMonthItems(sMonth,warehouseCode,goodsID,prevShortQua,shortQua) select top 1 '"
					+ date
					+ "',"
					+ warehouseCode
					+ ","
					+ goodsID
					+ ",0,0 from warehouseMonthItems where sMonth='"
					+ date
					+ "'";
			pstmt.addBatch(sql);

			pstmt.executeBatch();
			return true;
		} catch (Exception ex) {
			ex.printStackTrace();
			new WriteLog(getJndiName(), 0, 10000, "core", "kernel", "database",
					this.getClass().getName() + ": " + ex.getMessage(), true, 0);
			return false;
		} finally {
			try {

				if (pstmt != null) {
					pstmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}

	public boolean resetDayCur(int warehouseCode, String date) {
		if (!WarehouseMonth.isExistsByCode(getJndiName(), date)) {
			return true;
		}

		Connection conn = null;
		Statement pstmt = null;
		try {
			DatabaseConnection dc = new DatabaseConnection(getJndiName());
			conn = dc.getConnection();
			pstmt = conn.createStatement();
			String sql = "";

			sql = "delete from warehouseMonthItems where sMonth='" + date
					+ "' and warehouseCode=" + warehouseCode;
			pstmt.addBatch(sql);
			sql = "insert into warehouseMonthItems(sMonth,warehouseCode,goodsID,prevShortQua,shortQua) select top 1 '"
					+ date + "'," + warehouseCode + ",goodsID ,0,0 from goods ";
			pstmt.addBatch(sql);

			pstmt.executeBatch();
			return true;
		} catch (Exception ex) {
			ex.printStackTrace();
			new WriteLog(getJndiName(), 0, 10000, "core", "kernel", "database",
					this.getClass().getName() + ": " + ex.getMessage(), true, 0);
			return false;
		} finally {
			try {

				if (pstmt != null) {
					pstmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}

	public boolean updateDayCur(int warehouseCode, String date, double qua,
			int goodsID) {
		if (!WarehouseMonth.isExistsByCode(getJndiName(), date)) {
			return true;
		}

		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			DatabaseConnection dc = new DatabaseConnection(getJndiName());
			conn = dc.getConnection();
			String sql = "update warehouseMonthItems set shortQua=shortQua+?,shortAmo=shortQua*shortPrice where sMonth =? and warehouseCode=? and  goodsID=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setDouble(1, qua);
			pstmt.setString(2, date);
			pstmt.setInt(3, warehouseCode);
			pstmt.setInt(4, goodsID);
			pstmt.execute();
			return true;
		} catch (Exception ex) {
			ex.printStackTrace();
			new WriteLog(getJndiName(), 0, 10000, "core", "kernel", "database",
					this.getClass().getName() + ": " + ex.getMessage(), true, 0);
			return false;
		} finally {
			try {

				if (pstmt != null) {
					pstmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}

	public String getMaxDate() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			DatabaseConnection dc = new DatabaseConnection(getJndiName());
			conn = dc.getConnection();
			String sql = "select MAX(sMonth) as month from warehouseMonthItems";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				return rs.getString("month");
			}
			return null;
		} catch (Exception ex) {
			ex.printStackTrace();
			new WriteLog(getJndiName(), 0, 10000, "core", "kernel", "database",
					this.getClass().getName() + ": " + ex.getMessage(), true, 0);
			return null;
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}

	public String getMinDate() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			DatabaseConnection dc = new DatabaseConnection(getJndiName());
			conn = dc.getConnection();
			String sql = "select min(sMonth) as month from warehouseMonthItems";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				return rs.getString("month");
			}
			return null;
		} catch (Exception ex) {
			ex.printStackTrace();
			new WriteLog(getJndiName(), 0, 10000, "core", "kernel", "database",
					this.getClass().getName() + ": " + ex.getMessage(), true, 0);
			return null;
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}

	public boolean isExists(int warehouseCode, String createDate) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			DatabaseConnection dc = new DatabaseConnection(getJndiName());
			conn = dc.getConnection();
			String sql = "select top 1 * from warehouseMonthItems  where sMonth=? and warehouseCode=?  ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, createDate);
			pstmt.setInt(2, warehouseCode);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				return true;
			}
			return false;
		} catch (Exception ex) {
			ex.printStackTrace();
			new WriteLog(getJndiName(), 0, 10000, "core", "kernel", "database",
					this.getClass().getName() + ": " + ex.getMessage(), true, 0);
			return false;
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}
}