CNG/src/com/runzhixing/packageProcedure/InformationAnnouncementProcedure.java

package com.runzhixing.packageProcedure;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.apache.struts2.ServletActionContext;

import com.runzhixing.bean.Announcement;
import com.runzhixing.bean.CNGMachineState;
import com.runzhixing.bean.CylinderFollow;
import com.runzhixing.bean.User;
import com.runzhixing.constant.Constant;
import com.runzhixing.db.JDBCUtils;
import com.runzhixing.tool.Util;

import oracle.jdbc.OracleTypes;


/**
 * 加气机运行状态存储函数
 * @author 小黄
 *
 */
public class InformationAnnouncementProcedure {
	/**
	 * 加气机运行状态存储函数
	 * @param un
	 * @param ps
	 * @return
	 */
//加气机使用状态
	@SuppressWarnings("unchecked")
	public List<Announcement> areatedmachinerunningstate(int pagesize,int pageno,int id,String stationno,String districtno,String acceptType,Date startTime,Date endTime,int direct) {
		List<Announcement> list=new ArrayList<Announcement>();
		Map<Integer, Object> reValues = new HashMap<Integer, Object>();
		String sql = "{?=call PA_BugReport.GetPublishInfo(?,?,?,?,?,?,?,?,?,?,?,?)}";
		ResultSet rs = null;
		Connection conn =null;
		
		CallableStatement call = null;
		
		
		try {
			conn = JDBCUtils.getConnection();
			
			call = (CallableStatement) conn.prepareCall(sql);
			call.registerOutParameter(1, Types.INTEGER);// 返回值
			call.setInt(2,pagesize);// 每次取的行数
			call.setInt(3, pageno);// 第几页
			call.setInt(4,id);
			call.setString(5, stationno);//发布的站点
			call.setString(6, districtno);//接收站点
			call.setString(7,acceptType);//接收站点类型
			call.setDate(8, startTime); // --开始时间
			call.setDate(9, endTime); // --结束时间
			call.setInt(10, Util.calculateFlag(startTime, endTime)); // 时间查询组合 0时间无效 1开始时间有效 2结束时间有效 3都有效
			call.setInt(11, direct);//排序方式 0-升序 1-降序
			call.registerOutParameter(12, Types.INTEGER); // --总行数
			call.registerOutParameter(13, OracleTypes.CURSOR); // --结果集
			call.execute();
			
			int total = ((Integer) call.getObject(12)).intValue();
			HttpServletRequest req = ServletActionContext.getRequest();
			User user = new User();
			user.setAnnounceSum(total);
			req.getSession().setAttribute(Constant.AnnounceSum, user);
			
			
			rs = (ResultSet) call.getObject(13);
			//Map<String,String> map=new HashMap<String,String>();
//			List<ManaOperator> list=new ArrayList<ManaOperator>();//helper.fillDomain(ManaOperator.class, rs,null);
			while(rs.next()){
				Announcement obj=new Announcement();
				obj.setId(rs.getInt("id"));
				obj.setContent(rs.getString("content"));
				obj.setPublisher(rs.getString("publisher"));
				obj.setPublishTime(rs.getTimestamp("publishtime"));
				obj.setPublishTime1(rs.getString("publishtime"));
				
				obj.setPublishStationNo(rs.getString("publishstationno"));
				obj.setPublishStation(rs.getString("publishstation"));
				obj.setRecordOperator(rs.getString("recordoperator"));
				obj.setRecordOperatorName(rs.getString("recordoperatorname"));
				obj.setAcceptDistrict(rs.getString("acceptdistrict"));
				obj.setAcceptDistrictName(rs.getString("acceptdistrictname"));
				obj.setAcceptStationType(rs.getString("acceptstationtype"));
				obj.setAcceptTypeName(rs.getString("accepttypename"));
				obj.setStateName(rs.getString("statename"));
				obj.setState(rs.getInt("state"));
				obj.setTitle(rs.getString("title"));
				list.add(obj);
				obj=null;//释放obj
			}	
			reValues.put(1, list);
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			JDBCUtils.release(conn, call, rs);
		}
		return (List<Announcement>) reValues.get(1);
			
	}
	@SuppressWarnings("unchecked")
	public List<Announcement> areatedmachinerunningstate1( int pagesize,int pageno,int id,String stationno,String districtno,String acceptType,Date startTime,Date endTime,int direct) {
		List<Announcement> list=new ArrayList<Announcement>();
		Map<Integer, Object> reValues = new HashMap<Integer, Object>();
		String sql = "{?=call PA_BugReport.GetPublishInfo(?,?,?,?,?,?,?,?,?,?,?,?)}";
		ResultSet rs = null;
		Connection conn =null;
		
		CallableStatement call = null;
		
		
		try {
			conn = JDBCUtils.getConnection();
			
			call = (CallableStatement) conn.prepareCall(sql);
			call.registerOutParameter(1, Types.INTEGER);// 返回值
			call.setInt(2,pagesize);// 每次取的行数
			call.setInt(3, pageno);// 第几页
			call.setInt(4,id);
			call.setString(5, stationno);//发布的站点
			call.setString(6, districtno);//接收站点
			call.setString(7,acceptType);//接收站点类型
			call.setDate(8, startTime); // --开始时间
			call.setDate(9, endTime); // --结束时间
			call.setInt(10, Util.calculateFlag(startTime, endTime)); // 时间查询组合 0时间无效 1开始时间有效 2结束时间有效 3都有效
			call.setInt(11, direct);//排序方式 0-升序 1-降序
			call.registerOutParameter(12, Types.INTEGER); // --总行数
			call.registerOutParameter(13, OracleTypes.CURSOR); // --结果集
			call.execute();
			
			int total = ((Integer) call.getObject(12)).intValue();
			//HttpServletRequest req = ServletActionContext.getRequest();
//			User user = new User();
//			user.setAnnounceSum(total);
//			req.getSession().setAttribute(Constant.AnnounceSum, user);
//			
			
			rs = (ResultSet) call.getObject(13);
			//Map<String,String> map=new HashMap<String,String>();
//			List<ManaOperator> list=new ArrayList<ManaOperator>();//helper.fillDomain(ManaOperator.class, rs,null);
			while(rs.next()){
				Announcement obj=new Announcement();
				obj.setId(rs.getInt("id"));
				obj.setContent(rs.getString("content"));
				obj.setPublisher(rs.getString("publisher"));
				obj.setPublishTime(rs.getTimestamp("publishtime"));
				obj.setPublishTime1(rs.getString("publishtime"));
				
				obj.setPublishStationNo(rs.getString("publishstationno"));
				obj.setPublishStation(rs.getString("publishstation"));
				obj.setRecordOperator(rs.getString("recordoperator"));
				obj.setRecordOperatorName(rs.getString("recordoperatorname"));
				obj.setAcceptDistrict(rs.getString("acceptdistrict"));
				obj.setAcceptDistrictName(rs.getString("acceptdistrictname"));
				obj.setAcceptStationType(rs.getString("acceptstationtype"));
				obj.setAcceptTypeName(rs.getString("accepttypename"));
				obj.setStateName(rs.getString("statename"));
				obj.setState(rs.getInt("state"));
				obj.setTitle(rs.getString("title"));
				list.add(obj);
				obj=null;//释放obj
			}	
			reValues.put(1, list);
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			JDBCUtils.release(conn, call, rs);
		}
		return (List<Announcement>) reValues.get(1);
			
	}
	
	public int insertAnnounce(int id,String title,String content,String publisher,java.sql.Date pubtime,String stationno,String recorder,String dist,String stationtype,int type,int state) {
		Map<Integer, Object> reValues = new HashMap<Integer, Object>();
		String sql = "{?=call PA_BugReport.WritePublishInfo(?,?,?,?,?,?,?,?,?,?,?)}";
		ResultSet rs = null;
		Connection conn =null;
		
		CallableStatement call = null;
		
		
		try {
			conn = JDBCUtils.getConnection();
			
			call = (CallableStatement) conn.prepareCall(sql);
			call.registerOutParameter(1, Types.INTEGER);// 返回值
			call.setInt(2, id);//id
			call.setString(3, title);//title
			call.setString(4, content);//content
			call.setString(5, publisher);//发布人
			call.setDate(6,pubtime);//发布时间
			call.setString(7, stationno);//发布站点
			call.setString(8,recorder);//录入人
			call.setString(9, dist);//有效区域
			call.setString(10, stationtype);
			call.setInt(11, type);//操作类型
			call.setInt(12, state);//信息状态
			call.execute();
			
			reValues.put(1, (Integer)call.getObject(1));
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			JDBCUtils.release(conn, call, rs);
		}
		return (Integer) reValues.get(1);
			
	}
	
	
	
	@SuppressWarnings("unchecked")
	public List<Announcement> checkValidity(String sStationNo,String sOPeratorNo,String vDistrictNo,int vType) {
		List<Announcement> list=new ArrayList<Announcement>();
		Map<Integer, Object> reValues = new HashMap<Integer, Object>();
		String sql = "{?=call pa_SubTran_new.GetStation2(?,?,?,?)}";
		ResultSet rs = null;
		Connection conn =null;
		
		CallableStatement call = null;
		
		
		try {
			conn = JDBCUtils.getConnection();
			
			call = (CallableStatement) conn.prepareCall(sql);
//			call.registerOutParameter(1, Types.INTEGER);// 返回值
			call.registerOutParameter(1, OracleTypes.CURSOR);// 返回值
			call.setString(2, sStationNo);//站点
			call.setString(3, sOPeratorNo);//用户
			call.setString(4,vDistrictNo);//地区
			call.setInt(5, vType); // 身份
//			call.registerOutParameter(1, OracleTypes.CURSOR); // --结果集
			call.execute();
			
//			
			
			rs = (ResultSet) call.getObject(1);
//			rs.getMetaData();
//			ResultSetMetaData rsmd=rs.getMetaData();//取得结果集的属性
//			int  cols=rsmd.getColumnCount();//得到列数
//			int rowcount=0;
//			int col=1;
//			for (col=1;col<=cols;col++)//循环取列名(字段)
//			{
//				System.out.println(rsmd.getColumnLabel(col)+",");
//			}
			while(rs.next()){
				String validity=rs.getString("VALIDITY");
				if(validity!=null&&!validity.trim().equals("")){
					String nowTime = Util.df.format(new java.util.Date());
					long l=new Util().daysOfTwo_2(nowTime,validity);
					if(l<=30){
						Announcement obj=new Announcement();
						obj.setStationNo(rs.getString("stationNo"));
						obj.setStationName(rs.getString("stationName"));
						obj.setValidity(validity.substring(0, 10));
						obj.setLicence(rs.getString("LICENCE"));
						obj.setLicenceorg(rs.getString("LICENCEORG"));
						String licencedate=rs.getString("LICENCEDATE");
						if(licencedate!=null&&!licencedate.trim().equals("")){
							obj.setLicencedate(licencedate.substring(0, 10));
						}
						obj.setManager(rs.getString("MANAGER"));
						obj.setPhone(rs.getString("PHONE"));
						obj.setL(l);
						list.add(obj);
						obj=null;//释放obj
					}
					
				}
				
			}	
			reValues.put(1, list);
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			JDBCUtils.release(conn, call, rs);
		}
		return (List<Announcement>) reValues.get(1);
			
	}
	public List<CylinderFollow> checkValidity1(String sStationNo,String sOPeratorNo,String vDistrictNo,int vType) {
		List<CylinderFollow> list=new ArrayList<CylinderFollow>();
		Map<Integer, Object> reValues = new HashMap<Integer, Object>();
		String sql = "{?=call pa_SubTran_new.GetStation2(?,?,?,?)}";
		ResultSet rs = null;
		Connection conn =null;
		
		CallableStatement call = null;
		
		
		try {
			conn = JDBCUtils.getConnection();
			
			call = (CallableStatement) conn.prepareCall(sql);
//			call.registerOutParameter(1, Types.INTEGER);// 返回值
			call.registerOutParameter(1, OracleTypes.CURSOR);// 返回值
			call.setString(2, sStationNo);//站点
			call.setString(3, sOPeratorNo);//用户
			call.setString(4,vDistrictNo);//地区
			call.setInt(5, vType); // 身份
//			call.registerOutParameter(1, OracleTypes.CURSOR); // --结果集
			call.execute();
			
			
			rs = (ResultSet) call.getObject(1);
			while(rs.next()){
				String validity=rs.getString("VALIDITY");
				if(validity!=null&&!validity.trim().equals("")){
					String nowTime = Util.df.format(new java.util.Date());
					long l=new Util().daysOfTwo_2(nowTime,validity);
					if(l<=30){
						CylinderFollow obj=new CylinderFollow();
						obj.setStationNo(rs.getString("stationNo"));
						obj.setStationName(rs.getString("stationName"));
						obj.setValidity(validity.substring(0, 10));
						obj.setLicence(rs.getString("LICENCE"));
						obj.setLicenceorg(rs.getString("LICENCEORG"));
						String licencedate=rs.getString("LICENCEDATE");
						if(licencedate!=null&&!licencedate.trim().equals("")){
							obj.setLicencedate(licencedate.substring(0, 10));
						}
						
						obj.setManager(rs.getString("MANAGER"));
						obj.setPhone(rs.getString("PHONE"));
						obj.setL(l);
						list.add(obj);
						obj=null;//释放obj
					}
					
				}
				
			}	
			reValues.put(1, list);
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			JDBCUtils.release(conn, call, rs);
		}
		return (List<CylinderFollow>) reValues.get(1);
	}
	
}