<%@ page language="java" import="java.util.*" pageEncoding="gb2312"%>
<%@ page import=" com.zhuozhengsoft.pageoffice.*, com.zhuozhengsoft.pageoffice.excelreader.*,java.awt.*,
javax.servlet.*,javax.servlet.http.*,java.sql.*,java.text.SimpleDateFormat,java.util.Date,java.util.regex.Pattern,java.util.regex.Matcher"%>
<%@ taglib uri="http://java.pageoffice.cn" prefix="po" %>

<%
String errMsg="";//错误信息

Object userName=session.getAttribute("UserName");
String id=request.getParameter("ID");
if(userName==null||userName.toString().length()<=0){
	response.sendRedirect("login.jsp?tz=true");
}else{
	//pageoffice组件的使用
	Workbook wb = new Workbook(request,response);
	Sheet sheet=wb.openSheet("销售订单");
	
	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//载入驱动程序类别
	String strUrl = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ="
			+ this.getServletContext().getRealPath("demodata/") + "\\demo.mdb";
	Connection conn = DriverManager.getConnection(strUrl);
	Statement stmt = conn.createStatement();
	
	String sql="";
	//客户信息
	String orderNum	=sheet.openCell("OrderNum").getValue();
	String custName = sheet.openCell("CustName").getValue();
	String district = sheet.openCell("CustDistrict").getValue();
	String salesName = sheet.openCell("SalesName").getValue();
	String date = sheet.openCell("OrderDate").getValue();
	String amount = sheet.openCell("Amount").getValue();
	if(custName.length()<=0)
		errMsg+="请输入客户名称！";
	else{
		if(id!=null&&id.length()>0){
		//更新客户信息
		sql= "Update OrderMaster set orderNum = '" + orderNum + "',MakerName = '" + userName
	        + "',CustName='" + custName + "',CustDistrict='" + district + "',SalesName = '" + salesName 
	        + "' ,Amount= " + amount + " where ID = "+id;
		}else{
		 //添加客户信息
		 int maxId = 0;//OrderMaster表中最大ID号
	     sql = "select max(ID) as maxID from OrderMaster ";
	     ResultSet rset=stmt.executeQuery(sql);
	     if(rset.next()){
	     	maxId=rset.getInt(1);
	     	sql = "Insert into OrderMaster values(" + (maxId + 1) + ",'" + orderNum + "'," + date + ",'" 
	     	+ custName + "','" + district + "','" + userName + "','" + salesName + "'," + amount + ")";
	     }
	     else{
	     	errMsg+="添加客户信息失败！";
	     }
		}
	    //保存客户信息
		if(stmt.executeUpdate(sql)>0){
	    	Table table=sheet.openTable("OrderDetail");
	    	sql = "select * from OrderDetail where OrderID = "+id; 
	    	ResultSet rs = stmt.executeQuery(sql);
	    	if(rs.next()){
	    	//删除该客户的相应订单信息
	    		sql="Delete from OrderDetail where OrderID = "+id;
	    		if(stmt.executeUpdate(sql)<=0){
	    			errMsg+="删除客户相应的订单信息失败！";
	    		}	
	    	}
	    	//提交的table中有数据时,根据客户的ID将订单信息插入数据库
	    	while(!table.getEOF()){
	    		if(!table.getDataFields().getIsEmpty()){
	    			sql = "insert into OrderDetail(OrderID, ProductCode, ProductName, ProductType, Unit, Quantity, Price) values(" + id;
	    			int qua=0;
	    			String strQua= table.getDataFields().get(4).getValue().trim();//数量  
	    			Pattern pattern = Pattern.compile("[0-9]*"); 
	 				if( pattern.matcher(strQua).matches()){
	 					qua=Integer.parseInt(strQua);
	 				}else{
	 					errMsg+="请输入正确的数量值（需为整数数字）！";
	 				}
	 					   
	 				float price=0.00f;
	 				String strPrice= table.getDataFields().get(5).getValue().trim();//单价
	 				pattern = Pattern.compile("^[+-]?[0-9]{0,10}[.]?[0-9]{0,2}$"); 
	 				if( pattern.matcher(strPrice).matches()){
	 					price=Integer.parseInt(strPrice);
	 				}else{
	 					errMsg+="请输入正确的单价（需为数字）！";
	 				}
	 				sql+=",'" + table.getDataFields().get(0).getValue() + "','" + table.getDataFields().get(1).getValue().trim() + "','" +
	                    table.getDataFields().get(2).getValue().trim() + "','" + table.getDataFields().get(3).getValue().trim() + "'," +
	                    qua + ",'" + price + "')";
	            	if(stmt.executeUpdate(sql)<=0)
	                	errMsg+="相应客户的订单信息更新失败！";
	    		}
	    		table.nextRow();//循环下一行
	    	}	
		}else{
	    	errMsg+="客户信息保存失败！";
		}   
	}	
							
	//保存失败
	if(errMsg.length()>0){
		wb.showPage(400,400);//显示错误信息提示框
		wb.setCustomSaveResult("error");//返回操作结果
	}
	wb.close();
	stmt.close();
	conn.close();	
}
%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title></title>
    <link href="css/error.css" rel="stylesheet" type="text/css" />
</head>
<body>
    <form id="form1" action="post";>
    <div>
        <div class="errTopArea" style="text-align: left">
            [提示标题：这是一个开发人员可自定义的对话框]</div>
        <div class="errTxtArea" style="height: 150px; text-align: left">
            <b class="txt_title">
                <div style="color: #FF0000;">
                    错误信息如下：
                    <%=errMsg%>
                </div>
                
            </b>
        </div>
        <div class="errBtmArea">
            <input type="button" class="btnFn" value=" 关闭 " onclick="window.opener=null;window.open('','_self');window.close();" /></div>
    </div>
    </form>
</body>
</html>

