登录后显示各状态数量
LoginAction.java:
Map<String, Object> orderMap = orderCounterFacade.orderCount(user.getAgent().getClienttype(),user.getAgent().getAgentcode(),user.getUsertype(), user.getLoginname().trim());request.getSession().setAttribute("ORDER_COUNT_MAP", orderMap);?public Map<String, Object> orderCount(final int clienttype, final String agentcode, final int usertype, final String username) {return (Map<String, Object>) this.getBaseDao().getHibernateTemplate().execute(new HibernateCallback() {public Object doInHibernate(Session session)throws HibernateException, SQLException {log.info("enter get order count");Map<String,Object> orderMap = new HashMap<String, Object>();Connection conn = null;CallableStatement stmt = null;//----search by client type if client type = 0 show all booking----StringBuffer sb = new StringBuffer();if(clienttype != 0) {sb.append(" and clienttype = " + clienttype);//还要加上agent code的对比sb.append(" and agentcode = '" + agentcode + "'");//还要加上usertype的对比if(usertype != 1) {sb.append(" and createby = '"+ username + "'");}}log.info("init sql : " + sb.toString());try {conn = session.connection();stmt = conn.prepareCall("{call OrderCountUpdate (?,?,?,?," + "?,?,?,?," + "?,?,?,?," + "?,?,?,?," + "?,?,?,?,?,?,?)}");Date day = new Date();Date oneDay = DateUtil.dateAdd(day, 1, Calendar.DAY_OF_YEAR);String startDate = DateUtil.dateStartToString(day);String endDate = DateUtil.dateStartToString(oneDay);Date threeDay = DateUtil.dateAdd(day, 4, Calendar.DAY_OF_YEAR);String threeEndDate = DateUtil.dateStartToString(threeDay);stmt.setString(1, startDate);stmt.setString(2, endDate);stmt.setString(3, startDate);stmt.setString(4, threeEndDate);stmt.setInt(5, clienttype);stmt.setInt(6, usertype);stmt.setString(7, agentcode);stmt.setString(8, username);stmt.registerOutParameter(9, Types.INTEGER); //显示的数量，下同stmt.registerOutParameter(10, Types.INTEGER);stmt.registerOutParameter(11, Types.INTEGER);stmt.registerOutParameter(12, Types.INTEGER);stmt.registerOutParameter(13, Types.INTEGER);stmt.registerOutParameter(14, Types.INTEGER);stmt.registerOutParameter(15, Types.INTEGER);stmt.registerOutParameter(16, Types.INTEGER);stmt.registerOutParameter(17, Types.INTEGER);stmt.registerOutParameter(18, Types.INTEGER);stmt.registerOutParameter(19, Types.VARCHAR);stmt.registerOutParameter(20, Types.VARCHAR);stmt.registerOutParameter(21, Types.VARCHAR);stmt.registerOutParameter(22, Types.VARCHAR);stmt.registerOutParameter(23, Types.VARCHAR);stmt.execute();//输出到页面orderMap.put("pendingCount", stmt.getInt(9));orderMap.put("confirmedCount", stmt.getInt(10));orderMap.put("guaranteeCount", stmt.getInt(11));orderMap.put("cancelPendingCount", stmt.getInt(12));orderMap.put("deadlineCount", stmt.getInt(13));orderMap.put("threeDeadlineCount", stmt.getInt(14));orderMap.put("totalcancel", stmt.getInt(15));orderMap.put("totalroomnight", stmt.getInt(16));orderMap.put("totalconfirmed", stmt.getInt(17));orderMap.put("totaltran", stmt.getInt(18));orderMap.put("totalreject", stmt.getInt(19));log.info("newsid : " + stmt.getString(20));log.info("newssubject : " + stmt.getString(21));if(stmt.getString(20) != null && !"".equals(stmt.getString(20))) { String[] newsId = stmt.getString(20).split("\\|");String[] newsSubject = stmt.getString(21).split("\\|");List<News> newList = new ArrayList<News>();for (int i = 0; i < newsId.length; i++) {long id = Long.parseLong(newsId[i]);News news = new News();news.setInternalid(id);news.setSubject(newsSubject[i]);newList.add(news);}orderMap.put("newsList", newList);}if(stmt.getString(22)!=null && !"".equals(stmt.getString(22))) {String[] annsId = stmt.getString(22).split("\\|");String[] annsSubject = stmt.getString(23).split("\\|");List<Announcement> annsList = new ArrayList<Announcement>();for(int i=0; i<annsId.length; i++) {long id = Long.parseLong(annsId[i]);Announcement anns = new Announcement();anns.setInternalid(id);anns.setSubject(annsSubject[i]);annsList.add(anns);}orderMap.put("annsList", annsList);}log.info("leave get order count");} catch (SQLException e) {log.error("get orderCount error: " + e.getMessage());} finally {try {if(null != stmt) {stmt.close();stmt = null;}if(null != conn) {conn.close();conn = null;}} catch (SQLException e) {}}return orderMap;}});}?
存储过程：
/****** Object: Stored Procedure [OrderCountUpdate] Script Date: 2011/8/4 11:06:29 ******/USE [hotel_nh_egl];GOSET ANSI_NULLS ON;GOSET QUOTED_IDENTIFIER ON;GO-- =============================================-- Author:KO-- Create date: 11Feb2011-- Description:get user login information-- =============================================CREATE PROCEDURE [OrderCountUpdate](@deadlinestartdate varchar(20), @deadlineenddate varchar(20), @deadlineThreeStartdate varchar(20), @deadlineThreeEnddate varchar(20), @clienttype int=0, @usertype int=0, @agentcode varchar(20), @username varchar(50), @pendingCount int=0 OUTPUT, @confirmedCount int=0 OUTPUT, @guaranteeCount int=0 OUTPUT, @cancelPendingCount int=0 OUTPUT, @deadlineCount int=0 OUTPUT, @threeDeadlineCount int=0 OUTPUT, @totalcancel int=0 OUTPUT, @totalroomnight int=0 OUTPUT, @totalconfirmed int=0 OUTPUT, @totaltran int=0 OUTPUT, @totalreject int=0 OUTPUT, @newsid nvarchar(620) OUTPUT, @newssubject nvarchar(620) OUTPUT, @annsid nvarchar(620) OUTPUT, @annssubject nvarchar(620) OUTPUT)WITH EXECUTE AS CALLERASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- select top 3 newsDECLARE @filterfetch intDECLARE @tmpid nvarchar(620)DECLARE @tmpsubject nvarchar(620)DECLARE @tmpnewsId nvarchar(620)DECLARE @tmpnewssubject nvarchar(620)DECLARE news_cursor CURSOR DYNAMIC FORSELECT Top 3 [internalid],subject FROM [FareAdmin].[news] where expiry>@deadlinestartdate or noexpiry = 1 ORDER BY updatedate descOPEN news_cursorFETCH FIRST FROM news_cursor into @tmpid,@tmpsubjectSET @filterfetch = @@FETCH_STATUSSET @tmpnewsId = @tmpid SET @tmpnewssubject = @tmpsubjectWHILE @filterfetch = 0BEGINIF @tmpid != @tmpnewsIdBEGINSET @tmpnewsId = @tmpnewsId + '|' + @tmpidSET @tmpnewssubject = @tmpnewssubject + '|' + @tmpsubjectENDFETCH NEXT FROM news_cursor INTO @tmpid,@tmpsubjectSET @filterfetch = @@FETCH_STATUSENDCLOSE news_cursor-- select top3 announcementsSET @tmpid = ''SET @tmpsubject = ''DECLARE @tmpannsId nvarchar(620)DECLARE @tmpannsubject nvarchar(620)DECLARE anns_cursor CURSOR DYNAMIC FORSELECT Top 3 [internalid],subject FROM [FareAdmin].[announcement] where expiry>@deadlinestartdate ORDER BY updatedate descOPEN anns_cursorFETCH FIRST FROM anns_cursor into @tmpid,@tmpsubjectSET @filterfetch = @@FETCH_STATUSSET @tmpannsId = @tmpid SET @tmpannsubject = @tmpsubjectWHILE @filterfetch = 0BEGINIF @tmpid != @tmpannsIdBEGINSET @tmpannsId = @tmpannsId + '|' + @tmpidSET @tmpannsubject = @tmpannsubject + '|' + @tmpsubjectENDFETCH NEXT FROM anns_cursor INTO @tmpid,@tmpsubjectSET @filterfetch = @@FETCH_STATUSENDCLOSE anns_cursor -- Insert statements for procedure hereSELECT @newsid = @tmpnewsIdSELECT @newssubject = @tmpnewssubjectSELECT @annsid = @tmpannsIdSELECT @annssubject = @tmpannsubject --search by client type if client type = 0 show all bookingIF @clienttype = 0BEGINSELECT @totaltran =count(internalid) from [FareAdmin].[hotelbooktransportation] where bookingno in (select bookingno from [FareAdmin].[hotelbookheader] where status != 3)SELECT @totalroomnight =count(internalid) from [FareAdmin].[hotelbookpaxdetail] where bookingno in (select bookingno from [FareAdmin].[hotelbookheader] where status != 3)SELECT @totalconfirmed =count(bookingno) from [FareAdmin].[hotelbookheader] where status = 1SELECT @totalcancel =count(bookingno) from [FareAdmin].[hotelbookheader] where status = 2SELECT @totalreject =count(bookingno) from [FareAdmin].[hotelbookheader] where status = 4SELECT @pendingCount =count(bookingno) from [FareAdmin].[hotelbookheader] where (status = 0 or status = 6) and [createdate] >= @deadlinestartdate and [createdate] < @deadlineenddateSELECT @confirmedCount =count(bookingno) from [FareAdmin].[hotelbookheader] where status = 1 and [createdate] >= @deadlinestartdate and [createdate] < @deadlineenddateSELECT @guaranteeCount =count(bookingno) from [FareAdmin].[hotelbookheader] where status = 7 and [createdate] >= @deadlinestartdate and [createdate] < @deadlineenddateSELECT @cancelPendingCount =count(bookingno) from [FareAdmin].[hotelbookheader] where status = 5 and [cancelbookingdate] < @deadlineenddateSELECT @deadlineCount =count(bookingno) from [FareAdmin].[hotelbookheader] where [cancellationdeadline] >= @deadlinestartdate and [cancellationdeadline] < @deadlineenddateSELECT @threeDeadlineCount =count(bookingno) from [FareAdmin].[hotelbookheader] where [cancellationdeadline] >= @deadlineThreeStartdate and [cancellationdeadline] < @deadlineThreeEnddateENDELSEIF @usertype != 1 --usertype!= 1 search criteria add creadeby = login user nameBEGINSELECT @totaltran =count(internalid) from [FareAdmin].[hotelbooktransportation] where bookingno in (select bookingno from [FareAdmin].[hotelbookheader] where status != 3 and clienttype = @clienttype and agentcode = @agentcode and createby = @username)SELECT @totalroomnight =count(internalid) from [FareAdmin].[hotelbookpaxdetail] where bookingno in (select bookingno from [FareAdmin].[hotelbookheader] where status != 3 and clienttype = @clienttype and agentcode = @agentcode and createby = @username)SELECT @totalconfirmed =count(bookingno) from [FareAdmin].[hotelbookheader] where status = 1 and clienttype = @clienttype and agentcode = @agentcode and createby = @usernameSELECT @totalcancel =count(bookingno) from [FareAdmin].[hotelbookheader] where status = 2 and clienttype = @clienttype and agentcode = @agentcode and createby = @usernameSELECT @totalreject =count(bookingno) from [FareAdmin].[hotelbookheader] where status = 4 and clienttype = @clienttype and agentcode = @agentcode and createby = @usernameSELECT @pendingCount =count(bookingno) from [FareAdmin].[hotelbookheader] where (status = 0 or status=6) and [createdate] >= @deadlinestartdate and [createdate] < @deadlineenddate and clienttype = @clienttype and agentcode = @agentcode and createby = @usernameSELECT @confirmedCount =count(bookingno) from [FareAdmin].[hotelbookheader] where status = 1 and [createdate] >= @deadlinestartdate and [createdate] < @deadlineenddate and clienttype = @clienttype and agentcode = @agentcode and createby = @usernameSELECT @guaranteeCount =count(bookingno) from [FareAdmin].[hotelbookheader] where status = 7 and [createdate] >= @deadlinestartdate and [createdate] < @deadlineenddate and clienttype = @clienttype and agentcode = @agentcode and createby = @username SELECT @cancelPendingCount =count(bookingno) from [FareAdmin].[hotelbookheader] where status = 5 and [cancelbookingdate] < @deadlineenddate and clienttype = @clienttype and agentcode = @agentcode and createby = @usernameSELECT @deadlineCount =count(bookingno) from [FareAdmin].[hotelbookheader] where [cancellationdeadline] >= @deadlinestartdate and [cancellationdeadline] < @deadlineenddate and clienttype = @clienttype and agentcode = @agentcode and createby = @usernameSELECT @threeDeadlineCount =count(bookingno) from [FareAdmin].[hotelbookheader] where [cancellationdeadline] >= @deadlineThreeStartdate and [cancellationdeadline] < @deadlineThreeEnddate and clienttype = @clienttype and agentcode = @agentcode and createby = @usernameENDELSEBEGIN -- usertype == 1 search criteria add agentcode = login user agentcode and clientyp = login user clientypeSELECT @totaltran =count(internalid) from [FareAdmin].[hotelbooktransportation] where bookingno in (select bookingno from [FareAdmin].[hotelbookheader] where status != 3 and clienttype = @clienttype and agentcode = @agentcode)SELECT @totalroomnight =count(internalid) from [FareAdmin].[hotelbookpaxdetail] where bookingno in (select bookingno from [FareAdmin].[hotelbookheader] where status != 3 and clienttype = @clienttype and agentcode = @agentcode)SELECT @totalconfirmed =count(bookingno) from [FareAdmin].[hotelbookheader] where status = 1 and clienttype = @clienttype and agentcode = @agentcodeSELECT @totalcancel =count(bookingno) from [FareAdmin].[hotelbookheader] where status = 2 and clienttype = @clienttype and agentcode = @agentcodeSELECT @totalreject =count(bookingno) from [FareAdmin].[hotelbookheader] where status = 4 and clienttype = @clienttype and agentcode = @agentcodeSELECT @pendingCount =count(bookingno) from [FareAdmin].[hotelbookheader] where (status = 0 or status=6) and [createdate] >= @deadlinestartdate and [createdate] < @deadlineenddate and clienttype = @clienttype and agentcode = @agentcodeSELECT @confirmedCount =count(bookingno) from [FareAdmin].[hotelbookheader] where status = 1 and [createdate] >= @deadlinestartdate and [createdate] < @deadlineenddate and clienttype = @clienttype and agentcode = @agentcodeSELECT @guaranteeCount =count(bookingno) from [FareAdmin].[hotelbookheader] where status = 7 and [createdate] >= @deadlinestartdate and [createdate] < @deadlineenddate and clienttype = @clienttype and agentcode = @agentcode SELECT @cancelPendingCount =count(bookingno) from [FareAdmin].[hotelbookheader] where status = 5 and [cancelbookingdate] < @deadlineenddate and clienttype = @clienttype and agentcode = @agentcodeSELECT @deadlineCount =count(bookingno) from [FareAdmin].[hotelbookheader] where [cancellationdeadline] >= @deadlinestartdate and [cancellationdeadline] < @deadlineenddate and clienttype = @clienttype and agentcode = @agentcodeSELECT @threeDeadlineCount =count(bookingno) from [FareAdmin].[hotelbookheader] where [cancellationdeadline] >= @deadlineThreeStartdate and [cancellationdeadline] < @deadlineThreeEnddate and clienttype = @clienttype and agentcode = @agentcodeENDENDGO
?
jsp页面：
<td rowspan="3" style="vertical-align: top;"> <table cellpadding="0" cellspacing="0" border="0" style="border:1px solid #E2E2E2;height: 428px;width: 100%;"> <tr> <td style="text-align: right;"> Booking status </td> </tr> <tr> <td style="text-align: right;"> <a onclick="processing();" style="color:#808080;" href="<%=basePath %>searchBooking.do?method=query&bookingStatus=0&dealineDay=0&index=1"> ${ORDER_COUNT_MAP['pendingCount'] } Pending today </a> </td> </tr> <tr> <td style="text-align: right;"> <a onclick="processing();" style="color:#00B050;" href="<%=basePath %>searchBooking.do?method=query&bookingStatus=1&dealineDay=0&index=1"> ${ORDER_COUNT_MAP['confirmedCount'] } Confirmed within 24 hours </a> </td> </tr> <tr> <td style="text-align: right;"> <a onclick="processing();" style="color:#808080;" href="<%=basePath %>searchBooking.do?method=query&bookingStatus=7&dealineDay=0&index=1"> ${ORDER_COUNT_MAP['guaranteeCount'] } Guarantee today </a> </td> </tr> <c:if test="${CLIENTUSER.agent.agentcode eq 'admin' || CLIENTUSER.agent.agentcode eq 'leisure'}"> <tr> <td style="text-align:right;"> <a onclick="processing();" style="color:#000000;" href="<%=basePath %>searchBooking.do?method=query&bookingStatus=5&dealineDay=0&index=1"> ${ORDER_COUNT_MAP['cancelPendingCount'] } Cancelled </a> </td> </tr> </c:if> <tr> <td style="text-align: right;"> <a onclick="processing();" style="color:#2730E9;" href="<%=basePath %>destination.do"> 0 Messages </a> </td> </tr> <tr> <td style="text-align: right;"> Deadline </td> </tr> <tr> <td style="text-align: right;"> <a onclick="processing();" style="color:#2730E9;" href="<%=basePath %>searchBooking.do?method=query&dealineDay=1&index=1"> ${ORDER_COUNT_MAP['deadlineCount'] } Deadline today </a> </td> </tr> <tr> <td style="text-align: right;"> <a onclick="processing();" style="color:#2730E9;" href="<%=basePath %>searchBooking.do?method=query&dealineDay=3&index=1"> ${ORDER_COUNT_MAP['threeDeadlineCount'] } Deadline within 3 days </a> </td> </tr> <tr> <td style="text-align: right;"> Statistic </td> </tr> <tr> <td style="text-align: right;color: #A6A6A6;"> ${ORDER_COUNT_MAP['totaltran'] } Total Transactions </td> </tr> <tr> <td style="text-align: right;color: #A6A6A6;"> ${ORDER_COUNT_MAP['totalroomnight'] } Total room nights </td> </tr> <tr> <td style="text-align: right;color: #A6A6A6;"> ${ORDER_COUNT_MAP['totalconfirmed'] } booking confirmed </td> </tr> <tr> <td style="text-align: right;color: #A6A6A6;"> ${ORDER_COUNT_MAP['totalcancel'] } booking cancelled </td> </tr> <tr> <td style="text-align: right;color: #A6A6A6;"> ${ORDER_COUNT_MAP['totalreject'] } booking rejected </td> </tr> <tr> <td style="text-align: right;"> Quick Search </td> </tr> <tr> <td style="text-align: right;padding-right: 113px;color: #A6A6A6;"> Booking no </td> </tr> <tr> <td style="text-align: right;vertical-align: top;"><html:form action="/searchBooking" styleId="form1" stylename="bookingSearch.bookingno" id="bookingSearch.bookingno"/><input type="hidden" name="bookingSearch.bookingStatus" value="-1" id="bookingSearch.bookingStatus"/><input type="hidden" name="index" value="0"/> <a href="javascript:doQuery();">GO</a></html:form> </td> </tr> </table> </td>?。。。
主要看看存储过程如何实现即可！