usersubhisbean.java

WAP PUSH后台源码,WAP PUSH后台源码

package com.sxit.wap.usersubhis;
import java.sql.*;
import java.util.*;
import com.sxit.wap.common.*;
import com.sxit.wap.exception.*;

public class UserSubHisBean extends UserSubHisDao{
  public static int getLastMonthFee(String userMdn) throws SysException {
    String sql = "SELECT SUM(FEE_CODE) FROM " + tableName + " WHERE USER_MDN = '" + userMdn + "' AND " + DateUtil.getDateCompareSql(Database.dbType, "SUB_DATE", DateUtil.getLastMonthFirstDay(), DateUtil.getCurrMonthFirstDay());
    return getRowCountBySql(sql);
  }
  public static int getCurrMonthFee(String userMdn) throws SysException {
    String sql = "SELECT SUM(FEE_CODE) FROM " + tableName + " WHERE USER_MDN = '" + userMdn + "' AND " + DateUtil.getDateCompareSql(Database.dbType, "SUB_DATE", DateUtil.getCurrMonthFirstDay(), DateUtil.getNextMonthFirstDay());
    System.out.println(sql);
    return getRowCountBySql(sql);
  }
  public static Collection getSpChannelId( java.sql.Timestamp start,java.sql.Timestamp end,int cpId) throws SysException{
        String sql = "SELECT HIS.CHANNEL_ID FROM WAP_USER_SUB_HIS HIS,WAP_CHANNEL CH WHERE  CH.ID = HIS.CHANNEL_ID AND CH.CP_ID="+cpId + " AND ";
        sql +=  DateUtil.getDateCompareSql ( Database.dbType, "SUB_DATE", start, DateUtil.getSomeDate(end, 1));
        sql += " GROUP BY CHANNEL_ID";
        System.out.println(sql);
        return queryBySql(sql);
}
 public static int getChannelIncome(java.sql.Timestamp start,java.sql.Timestamp end,int channelId) throws SysException{
        String sql = "SELECT SUM(FEE_CODE) FROM WAP_USER_SUB_HIS WHERE CHANNEL_ID = " + channelId  +" AND ";
        sql +=  DateUtil.getDateCompareSql ( Database.dbType, "SUB_DATE", start, DateUtil.getSomeDate(end, 1));
        System.out.println(sql);
        return getRowCountBySql(sql);
 }
 public static int getChannelAccess(java.sql.Timestamp start,java.sql.Timestamp end,int channelId) throws SysException{
        String sql = "SELECT COUNT(*) FROM WAP_ACCESS WHERE CHANNEL_ID = " + channelId + " AND ";
        sql +=  DateUtil.getDateCompareSql ( Database.dbType, "VISIT_TIME", start, DateUtil.getSomeDate(end, 1));
        System.out.println(sql);
        return getRowCountBySql(sql);
 }
 public static int getChannelOrder(java.sql.Timestamp start,java.sql.Timestamp end,int channelId) throws SysException{
        String sql = "SELECT COUNT(*) FROM WAP_USER_SUB_HIS WHERE CHANNEL_ID = " + channelId + " AND ";
        sql +=  DateUtil.getDateCompareSql ( Database.dbType, "SUB_DATE", start, DateUtil.getSomeDate(end, 1));
        System.out.println(sql);
        return getRowCountBySql(sql);
      }
public static Collection getUserCdr ( java.sql.Timestamp startDate, java.sql.Timestamp endDate, String mdn ) throws SysException {
                Timestamp bt = new java.sql.Timestamp ( startDate.getTime () );
                Timestamp et = new java.sql.Timestamp ( endDate.getTime () );
                String sql ="SELECT S.SUB_DATE, c.channel_name ,s.fee_code ,fe.fee_name";
                sql += " FROM WAP_USER_SUB_HIS S, WAP_CHANNEL C,WAP_FEE_TYPE fe";
                sql += " WHERE S.CHANNEL_ID = C.ID and fe.fee_type = s.fee_type  ";
                sql += " AND S.USER_MDN = '" + mdn + "' and ";
                sql += DateUtil.getDateCompareSql ( Database.dbType, "S.SUB_DATE", bt, et );

                return queryBySql ( sql );
        }
        public static Collection queryByUserMdn(String userMdn) throws SysException {
          String sql = "SELECT U.*, C.CHANNEL_NAME, C.CHANNEL_URL FROM WAP_USER_SUB_HIS U, WAP_CHANNEL C WHERE U.CHANNEL_ID = C.ID AND U.USER_MDN = '" + userMdn + "'";
          sql +=  " AND " + DateUtil.getDateCompareSql(Database.dbType, "SUB_DATE", DateUtil.getFirstDayInMonth(DateUtil.getCurrTime()), DateUtil.getNextMonthFirstDay())+" order by to_char(U.SUB_DATE,'yyyyMMddHH24miss')";
          System.out.println(sql);
          return queryBySql(sql);
        }
        public static boolean isUserSubed(String userMdn,int channelId)throws SysException{
          String sql = "SELECT COUNT(*) FROM WAP_USER_SUB_HIS WHERE USER_MDN = '" + userMdn +"' AND CHANNEL_ID = " + channelId;
          return getRowCountBySql(sql) > 0;
        }
        public static boolean getUserFeeCode(String userMdn,int channelId,Timestamp time)throws SysException{
          String sql = "SELECT COUNT(*) FROM WAP_USER_SUB_HIS WHERE USER_MDN = '" + userMdn +"' AND FEE_TYPE = 3 AND CHANNEL_ID = " + channelId;
          sql +=  " AND " + DateUtil.getDateCompareSql ( Database.dbType, "SUB_DATE", new Timestamp(System.currentTimeMillis() - 60*1000),new Timestamp(System.currentTimeMillis()));
          System.out.println(sql);
          return getRowCountBySql(sql) > 0;
        }
        public static void main(String[] args){
          System.out.println(DateUtil.getDateCompareSql ( Database.dbType, "SUB_DATE", new Timestamp(System.currentTimeMillis() - 60*1000),new Timestamp(System.currentTimeMillis())));
        }

}