cexsheetadapter.java

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

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

	public List getExSheets(int custID, String date) {
		if (date == null || "".equals(date)) {
			return getExSheets(custID);
		}

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List list = null;

		try {
			DatabaseConnection dc = new DatabaseConnection(getJndiName());
			conn = dc.getConnection();
			list = new LinkedList();

			String sql = "select * from exSheet  where custID=? and isExtAcc=0 and isAcc=1 and isRegLed=1 and createDate >='"
					+ date + "'";
			System.out.println("sql:" + sql);
			System.out.println("custID:" + custID);
			System.out.println("date:" + date);
			pstmt = conn.prepareStatement(sql);

			pstmt.setInt(1, custID);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				setExSheetCode(rs.getString("exSheetCode"));
				setCreateDate(rs.getString("createDate"));
				setAmo(rs.getDouble("amo"));

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

	public boolean isFill(String code) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			DatabaseConnection dc = new DatabaseConnection(getJndiName());
			conn = dc.getConnection();
			String sql = "select * from exSheet  where orderID=? and isRegLed=1";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, code);
			rs = pstmt.executeQuery();

			while (rs.next()) {

				return true;
			}
			return false;
		} catch (Exception ex) {
			ex.printStackTrace();
			new WriteLog(getJndiName(), 0, 10000, "core", "database", "kernel",
					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 dirty(String orderSheetCode, List newList) {
		/*
		 * CExItemsAdapter oldItems = new CExItemsAdapter(getJndiName());
		 * CExItemsAdapter newItems = null; List l =
		 * oldItems.getItemsByExSheetCode(orderSheetCode); if (l.size() !=
		 * newList.size()) { return true; } for (int i = 0; i < l.size(); i++) {
		 * oldItems = (CExItemsAdapter) l.get(i); newItems = (CExItemsAdapter)
		 * newList.get(i); if (oldItems.getQua() != newItems.getQua() ||
		 * oldItems.getGoodsID() != newItems.getGoodsID()) { return true; } }
		 */
		return false;
	}

	public boolean modify2() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			DatabaseConnection dc = new DatabaseConnection(getJndiName());
			conn = dc.getConnection();
			String sql = "update exSheet set exSheetCode=?,compID=?,type=?,redBlue=?,isFill=?,isReg=?,isRegLed=?,isAcc=?,isEnd=?,isPosRec=?,isExt=?,isExtAcc=?,isBack=?,isVou=?,createDate=?,accDate=?,regDate=?,arrDate=?,emplID=?,depID=?,brandID=?,regName=?,auditingName=?,marketName=?,finName=?,custID=?,warehouseCode=?,currency=?,cRate=?,orderID=?,invoNo=?,invoVer=?,qua=?,amo=?,costAmo=?,discount=?,realAmo=?,payAmo=?,preAmo=?,logID=?,fillSheetID=?,remark=?,haveCheck=?,userTel=?,userAddress=?,applyState=?,recDate=?,isChecked=?,opTime=getDate(),userName=?,settleID=?,backRemark=?,checkRemark=?,noteSheetCode=?,notifySheetCode=?,orderMySheetCode=?,sheetPriceType=?,sheetOrderType=?,isRec=?,recAmo=?,msAmo=? where exSheetCode=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, this.getExSheetCode());
			pstmt.setInt(2, this.getCompID());
			pstmt.setInt(3, this.getType());
			pstmt.setBoolean(4, this.getRedBlue());
			pstmt.setBoolean(5, this.getIsFill());
			pstmt.setBoolean(6, this.getIsReg());
			pstmt.setBoolean(7, this.getIsRegLed());
			pstmt.setBoolean(8, this.getIsAcc());
			pstmt.setBoolean(9, this.getIsEnd());
			pstmt.setBoolean(10, this.getIsPosRec());
			pstmt.setBoolean(11, this.getIsExt());
			pstmt.setBoolean(12, this.getIsExtAcc());
			pstmt.setBoolean(13, this.getIsBack());
			pstmt.setBoolean(14, this.getIsVou());
			pstmt.setString(15, this.getCreateDate());
			pstmt.setString(16, this.getAccDate());
			pstmt.setString(17, this.getRegDate());
			pstmt.setString(18, this.getArrDate());
			pstmt.setInt(19, this.getEmplID());
			pstmt.setString(20, this.getDepID());
			pstmt.setInt(21, this.getBrandID());
			pstmt.setDouble(22, this.getRegName());
			pstmt.setString(23, this.getAuditingName());
			pstmt.setDouble(24, this.getMarketName());
			pstmt.setString(25, this.getFinName());
			pstmt.setInt(26, this.getCustID());
			pstmt.setInt(27, this.getWarehouseCode());
			pstmt.setString(28, this.getCurrency());
			pstmt.setDouble(29, this.getCRate());
			pstmt.setString(30, this.getOrderID());
			pstmt.setString(31, this.getInvoNo());
			pstmt.setString(32, this.getInvoVer());
			pstmt.setDouble(33, this.getQua());
			pstmt.setDouble(34, this.getAmo());
			pstmt.setDouble(35, this.getCostAmo());
			pstmt.setDouble(36, this.getDiscount());
			pstmt.setDouble(37, this.getRealAmo());
			pstmt.setDouble(38, this.getPayAmo());
			pstmt.setDouble(39, this.getPreAmo());
			pstmt.setString(40, this.getLogID());
			pstmt.setString(41, this.getFillSheetID());
			pstmt.setString(42, this.getRemark());
			pstmt.setDouble(43, this.getHaveCheck());
			pstmt.setString(44, this.getUserTel());
			pstmt.setString(45, this.getUserAddress());
			pstmt.setInt(46, this.getApplyState());
			pstmt.setString(47, this.getRecDate());
			pstmt.setBoolean(48, this.getIsChecked());
			pstmt.setInt(49, this.getUserName());
			if (this.getSettleID() != 0) {
				pstmt.setInt(50, this.getSettleID());
			} else {
				pstmt.setNull(50, java.sql.Types.INTEGER);
			}
			// pstmt.setString(52, this.opTime);
			pstmt.setString(51, this.getBackRemark());
			if (this.getIsChecked()) {
				pstmt.setString(52, this.getCheckRemark());
			} else {
				pstmt.setString(52, "");
			}

			pstmt.setString(53, this.getNoteSheetCode());
			pstmt.setString(54, this.getNotifySheetCode());
			pstmt.setString(55, this.getOrderMySheetCode());

			pstmt.setInt(56, this.getSheetPriceType());
			pstmt.setString(57, this.getSheetOrderType());

			pstmt.setBoolean(58, this.getIsRec());
			pstmt.setDouble(59, this.getRecAmo());
			pstmt.setDouble(60, this.getMsAmo());

			pstmt.setString(61, this.getExSheetCode());

			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 getSalePrice(int custID, int goodsID, String startDate,
			String endDate) {

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		double price[] = null;
		double qua[] = null;
		LinkedList list = new LinkedList();
		try {
			try {
				DatabaseConnection dc = new DatabaseConnection(getJndiName());
				conn = dc.getConnection();
				String sql = "select isnull((b.price * b.discount),0) as price,isnull(sum(b.qua),0) as qua,b.goodsID from exSheet a,exItems b where a.exSheetCode=b.exSheetCode and a.isAcc=1 and a.custID=? and b.goodsID=? and a.createDate between ? and ? group by isnull((b.price * b.discount),0), b.goodsID order by qua desc";
				stmt = conn.prepareStatement(sql, 1004, 1007);
				stmt.setInt(1, custID);
				stmt.setInt(2, goodsID);
				stmt.setString(3, startDate);
				stmt.setString(4, endDate);

				rs = stmt.executeQuery();
				rs.last();
				int row = rs.getRow();
				price = new double[row];
				qua = new double[row];
				rs.beforeFirst();
				int pos = 0;
				while (rs.next()) {
					price[pos] = rs.getDouble("price");
					qua[pos] = rs.getDouble("qua");
					pos++;
				}

				if (qua.length > 0) {
					qua[0] = qua[0]
							- getBackQua(custID, goodsID, startDate, endDate);
				}
				list.add(price);
				list.add(qua);

				return list;
			} catch (Exception ex) {
				ex.printStackTrace();
			}

			return list;
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}

	public double getBackQua(int custID, int goodsID, String startDate,
			String endDate) {

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			try {
				DatabaseConnection dc = new DatabaseConnection(getJndiName());
				conn = dc.getConnection();
				String sql = "select isnull(sum(b.qua),0) as qua,b.goodsID from ebSheet a,ebItems b where a.ebSheetCode=b.ebSheetCode and a.isAcc=1 and a.custID=? and b.goodsID=? and a.createDate between ? and ? group by b.goodsID order by b.goodsID";
				stmt = conn.prepareStatement(sql, 1004, 1007);
				stmt.setInt(1, custID);
				stmt.setInt(2, goodsID);
				stmt.setString(3, startDate);
				stmt.setString(4, endDate);

				rs = stmt.executeQuery();
				double qua = 0;
				if (rs.next()) {
					qua = rs.getDouble("qua");
				}
				return qua;
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			return 0;
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}

	public boolean setReg() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			DatabaseConnection dc = new DatabaseConnection(getJndiName());
			conn = dc.getConnection();

			String sql = "update exSheet set isReg=?,userName=?,isVou=1,isChecked=0 where exSheetCode=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setBoolean(1, getIsReg());
			pstmt.setInt(2, getUserName());
			pstmt.setString(3, getExSheetCode());

			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 setRegLed() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			DatabaseConnection dc = new DatabaseConnection(getJndiName());
			conn = dc.getConnection();

			String sql = "update exSheet set isRegLed=?,userName=?,isVou=1,isChecked=0 where exSheetCode=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setBoolean(1, getIsRegLed());
			pstmt.setInt(2, getUserName());
			pstmt.setString(3, getExSheetCode());

			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();
			}
		}
	}

	private boolean isFin = false;

	public boolean getIsFin() {
		return this.isFin;
	}

	public void setIsFin(String b) {
		if (b == null) {
			return;
		}
		if ("1".equals(b)) {
			this.isFin = true;
		} else {
			this.isFin = false;
		}
	}

	public void setIsFin(boolean b) {
		this.isFin = b;
	}

	public boolean isHasCH(String exSheetCode) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			DatabaseConnection dc = new DatabaseConnection(getJndiName());
			conn = dc.getConnection();

			String sql = "select * from exItems  where exSheetCode=? and (brandName is not null and brandName<>'') ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, exSheetCode);
			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();
			}
		}
	}

}