📄 preimburse2.jsp
字号:
<%@ page language="java" contentType="text/html; charset=gb2312" pageEncoding="gb2312"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<%@page import="TeaWeb.PReimburseDetail"%>
<%@page import="TeaWeb.PReimburseItemDetail"%>
<%@page import="TeaWeb.OracleHepler"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.ResultSet"%>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<title>天缘茗茶</title>
<LINK href="TeaWebCSS.css" type="text/css" rel="stylesheet" />
</head>
<body topmargin="0" leftmargin="0">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td height="60" colspan="3"><%@ include file="head.jsp" %></td>
</tr>
<tr>
<td width="180" rowspan="2" align="right" valign="top"><%@ include file="left.jsp" %></td>
<td height="6" colspan="2" background="Images/TopLine.jpg"></td>
</tr>
<tr>
<td width="5" height="600" align="left" valign="top"><img src="Images/Corner.jpg" width="5" height="5" /></td>
<td width="100%" align="center" valign="top"><table width="90%" height="20" border="0" cellpadding="0" cellspacing="0">
<tr>
<td height="20" align="center" valign="middle"> </td>
</tr>
</table>
<table width="90%" height="20" border="0" cellpadding="0" cellspacing="0">
<tr>
<td height="20" align="left" valign="middle">
<!--
数据更新：1、插入退货信息表2、插入退货明细表（可能会是多条记录）3、更新库存表中的库存量4、更新仓库的表的仓库的剩余容量
-->
<%
PReimburseDetail pd = (PReimburseDetail)session.getAttribute("PReimburse");
//
//创建ORACLE的连接，执行SQL语句
OracleHepler db = new OracleHepler();
Connection cn = db.getconnection();
try
{
//不让它自动提交
cn.setAutoCommit(false);
//下面构建退货信息表的插入语句
String sql_PReimburseDetail = "Insert into PReimburse(PReimID,Code,AutoCode,ReturnTime,Operater,Note,PReimUpDate)";
sql_PReimburseDetail += "Values(Seq_PReimburse.NextVal,?,?,To_Date(?,'YYYY-MM-DD HH24:MI:SS'),?,?,sysdate)";
PreparedStatement pstms = cn.prepareStatement(sql_PReimburseDetail);
pstms.setString(1,pd.Code);
pstms.setString(2,pd.AutoCode);
pstms.setString(3, "2008-11-11 16:01:01");
pstms.setString(4,pd.perater);
pstms.setString(5,pd.Note);
pstms.execute();
//下面构建退货明细表的插入语句、仓库可用空间的更新语句、更新库存表中的库存量
for (int i = 0; i <= pd.Product.size() - 1; i++)
{
//退货明细表的插入语句
String str = "";
PReimburseItemDetail pdi = (PReimburseItemDetail)pd.Product.get(i);
str += "Insert Into PReimburseItem(PReimItID,ReturnCode,ProductCode,Price,Num,PReimItCost,Reason,Disposal,Result)";
str += " Values(Seq_PReimburseItem.NextVal,?,?,?,?,?,?,?,?)";
pstms = cn.prepareStatement(str);
pstms.setString(1,pd.Code);
pstms.setString(2,pdi.ProductCode);
pstms.setFloat(3,pdi.Price);
pstms.setInt(4,pdi.Num);
pstms.setFloat(5,pdi.PReimItCost);
pstms.setString(6,pdi.Reason);
pstms.setString(7,pdi.Disposal);
pstms.setString(8,pdi.Result);
pstms.execute();
//仓库可用空间的更新语句
//select * from Storage where Code=(select WarehouseCode from PurchaseItem where PurchaseCode='10011');
//查询产品的存储仓库号可用库存表或进货表最好是用库存表因为进货表可能被删,同时我前几条不是通过进货来添加的在这里没考虑同一产品在不同的仓库的情况
//其实这也是错的，因该是进货表所有的刚开始时并没有库存表
str = "Update Storage Set Capability = Capability + ? Where Code = (select WarehouseCode from PurchaseItem where ProductCode= ? and (rownum <=1))";
//str = "Update Storage Set Capability = Capability + ? Where Code = (select StorageCode from Stock where ProductCode= ?)";
pstms = cn.prepareStatement(str);
pstms.setInt(1,pdi.Num);
pstms.setString(2,pdi.ProductCode);
pstms.execute();
//其实退货时必须指定那个仓库的,这里并没指定，这里所有的相同产品都在同一个仓库
//更新库存表中的库存量
str = "Select * from Stock Where (ProductCode = ?) And (Num > ?)";
pstms = cn.prepareStatement(str);
//注意这里的Num一定要大于pdi.Num
pstms.setString(1,pdi.ProductCode);
pstms.setInt(2,pdi.Num);
ResultSet rs = pstms.executeQuery();
if (rs.next())
{
str = "Update Stock Set Num = Num - ? Where (ProductCode = ?)";
}
else
{
str = "delete from Stock where (Num=?) and (ProductCode = ?)";
}
//如果没有了则删了
pstms = cn.prepareStatement(str);
pstms.setInt(1,pdi.Num);
pstms.setString(2,pdi.ProductCode);
pstms.execute();
//在这里没考虑同一产品在不同的仓库的情况，
//else //刚好没了时则要删除这条记录其实也不一定要这样
//{
// str = "Insert into Stock(Num,ProductCode,StorageCode,StocUpDate)";
// str += "Values(?,?,?,sysdate)";
// }
}
//都执行完了，咱就提交了
cn.commit();
cn.setAutoCommit(true);
pstms.close(); //释放执行SQL的对象
cn.close(); //释放连接
session.setAttribute("PReimburse",null); //释放所占用的临时缓存
out.print("<script language='javascript'>");
out.print("document.location = 'Index.jsp';");
out.print("</script>");
}
catch(Exception e)
{
//失败了，回滚了
cn.rollback();
out.print(e.getMessage());
}
%>
</td>
</tr>
</table></td>
</tr>
</table>
</body>
</html>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -