Esempio n. 1
0
  /**
   * 条件查询
   *
   * @return String
   */
  public String gltmsumSelect() throws Exception {
    // 拼接查询条件
    String sql = "";
    this.terminfoList = new ArrayList<Terminfo>();
    // 关联组
    Organization org = (Organization) request.getSession().getAttribute("currentOrganization");
    String innerCode = (String) orgDAO.getOrgInnerInfo(org)[0];
    // 获得该组拥有的终端
    terminfoList = orgTermService.collectOrgTerms(org);

    // 需要先取得pageBean实例
    pageBean = (PageBean) request.getSession().getAttribute("pageBean");
    if (pageBean == null) {
      pageBean = new PageBean();
      request.getSession().setAttribute("pageBean", pageBean);
    }
    if (dateMin != null && (!dateMin.equals(""))) {
      dateMin = dateMin.substring(0, 4) + dateMin.substring(5, 7);
      if (dateMax != null && (!dateMax.equals(""))) {
        dateMax = dateMax.substring(0, 4) + dateMax.substring(5, 7);
        sql += " and v.date between '" + dateMin + "' and '" + dateMax + "'";
      } else {
        sql += " and v.date > '" + dateMin + "'";
      }
    } else {
      if (dateMax != null && (!dateMax.equals(""))) {
        dateMax = dateMax.substring(0, 4) + dateMax.substring(5, 7);
        sql += " and v.date<'" + dateMax + "'";
      }
    }

    if (terminfoId != null && (!terminfoId.equals(""))) {
      sql += " and v.terminfo.terminfoId=" + terminfoId;
    }

    sql += " and otv.id.orgInnerCode like '" + innerCode + "%'";

    this.pageNo = 1;
    this.request.getSession().setAttribute("sql", sql);
    return SUCCESS;
  }
  /**
   * 查询通讯费支付信息
   *
   * @return String
   */
  public String comsPayCommunicationSearch() {
    // 需要先取得pageBean实例
    pageBean = (PageBean) request.getSession().getAttribute("pageBean");
    if (pageBean == null) {
      pageBean = new PageBean();
      request.getSession().setAttribute("pageBean", pageBean);
    }
    String sql = "";
    if (this.getPageNo() <= 0) pageBean.setCurrentPage(1);
    else pageBean.setCurrentPage(this.getPageNo());

    if (getItemUids() != null
        && (!getItemUids().trim().equals(""))
        && (!getItemUids().trim().equals(AppCommonVarRegiter.ALL_VALUE_TAG))
        && (!getItemUids().trim().equals(AppCommonVarRegiter.NULL_VALUE_TAG))) {
      sql += " and v.itemUid like '" + getItemUids().trim() + "%'";
    }

    if (getOrgCode() != null && !getOrgCode().trim().equals("")) {
      Organization orgSelected = orgDao.findByOrgId(getOrgCode());
      String innercodeSelected = (String) orgDao.getOrgInnerInfo(orgSelected)[0];
      if (orgSelected.getOrgType().trim().equals("00"))
        sql += " and v.orgInnerCode01 like '" + innercodeSelected + "%'";
      else if (orgSelected.getOrgType().trim().equals("01"))
        sql += " and v.orgInnerCode02 like '" + innercodeSelected + "%'";
      else if (orgSelected.getOrgType().trim().equals("02"))
        sql += " and v.orgInnerCode03 like '" + innercodeSelected + "%'";
    }
    if (getTermSeqs() != null
        && (!getTermSeqs().trim().equals(""))
        && (!getTermSeqs().trim().equals(AppCommonVarRegiter.ALL_VALUE_TAG))
        && (!getTermSeqs().trim().equals(AppCommonVarRegiter.NULL_VALUE_TAG))) {
      sql += " and v.termseq = '" + getTermSeqs().trim() + "'";
    }
    if (getTermnos() != null
        && (!getTermnos().trim().equals(""))
        && (!getTermnos().trim().equals(AppCommonVarRegiter.ALL_VALUE_TAG))
        && (!getTermnos().trim().equals(AppCommonVarRegiter.NULL_VALUE_TAG))) {
      sql += " and v.termno = '" + getTermnos().trim() + "'";
    }
    if (this.getSearchStartDate() != null && !getSearchStartDate().equals("")) {
      sql =
          sql
              + " and v.costDate >='"
              + DateConvert.convertAtoB(getSearchStartDate(), "yyyy-MM", "yyyyMM")
              + "'";
    }
    if (this.getSearchEndDate() != null && !getSearchEndDate().equals("")) {
      sql =
          sql
              + " and v.costDate <='"
              + DateConvert.convertAtoB(getSearchEndDate(), "yyyy-MM", "yyyyMM")
              + "'";
    }
    String state = this.getSearchPayState();
    if (state != null && !state.equals("")) {
      if (state != "0" && !state.equals("0")) {
        sql = sql + " and v.payState ='" + state + "'";
      }
    }

    // 构造查询
    String queryStr =
        " select max(v.batchNum) as batchNum,max(v.termno) as termno,"
            + " max(v.termseq) as termseq,max(v.address) as address,max(v.payTotalMoney) as money,"
            + "(select min(f.costDate) from  ComsPayCommunication f where f.termBatchNum = v.termBatchNum) as startCostDate,"
            + "(select max(f.costDate) from  ComsPayCommunication f where f.termBatchNum = v.termBatchNum) as endCostDate "
            + "from ComsPayCommunication v where 1=1 ";
    queryStr = queryStr + sql + " group by v.termBatchNum order by max(v.batchNum)";

    String countStr =
        " select count(distinct v.termBatchNum) from ComsPayCommunication v where 1=1  ";
    countStr = countStr + sql;

    payList =
        (List<Object[]>) comsPayCommunicationDao.findByPageSupport(countStr, queryStr, pageBean);

    if (payList.size() > 0) {
      for (Object[] cpf : payList) {
        String startCostDate = cpf[5].toString();
        String endCostDate = cpf[6].toString();
        cpf[5] = DateConvert.convertAtoB(startCostDate, "yyyyMM", "yyyy-MM");
        cpf[6] = DateConvert.convertAtoB(endCostDate, "yyyyMM", "yyyy-MM");
      }
    }

    return SUCCESS;
  }
  @SuppressWarnings("unchecked")
  public String makeReport() {
    String sql = "";
    if (getItemUids() != null
        && (!getItemUids().trim().equals(""))
        && (!getItemUids().trim().equals(AppCommonVarRegiter.ALL_VALUE_TAG))
        && (!getItemUids().trim().equals(AppCommonVarRegiter.NULL_VALUE_TAG))) {
      sql += " and v.itemUid like '" + getItemUids().trim() + "%'";
    }
    Organization orgSelected = null;
    if (getOrgCode() != null && !getOrgCode().trim().equals("")) {
      orgSelected = orgDao.findByOrgId(getOrgCode());
      String innercodeSelected = (String) orgDao.getOrgInnerInfo(orgSelected)[0];
      if (orgSelected.getOrgType().trim().equals("00"))
        sql += " and v.orgInnerCode01 like '" + innercodeSelected + "%'";
      else if (orgSelected.getOrgType().trim().equals("01"))
        sql += " and v.orgInnerCode02 like '" + innercodeSelected + "%'";
      else if (orgSelected.getOrgType().trim().equals("02"))
        sql += " and v.orgInnerCode03 like '" + innercodeSelected + "%'";
    }
    if (getTermSeqs() != null
        && (!getTermSeqs().trim().equals(""))
        && (!getTermSeqs().trim().equals(AppCommonVarRegiter.ALL_VALUE_TAG))
        && (!getTermSeqs().trim().equals(AppCommonVarRegiter.NULL_VALUE_TAG))) {
      sql += " and v.termseq = '" + getTermSeqs().trim() + "'";
    }
    if (getTermnos() != null
        && (!getTermnos().trim().equals(""))
        && (!getTermnos().trim().equals(AppCommonVarRegiter.ALL_VALUE_TAG))
        && (!getTermnos().trim().equals(AppCommonVarRegiter.NULL_VALUE_TAG))) {
      sql += " and v.termno = '" + getTermnos().trim() + "'";
    }
    if (this.getSearchStartDate() != null && !getSearchStartDate().equals("")) {
      sql =
          sql
              + " and v.costDate >='"
              + DateConvert.convertAtoB(getSearchStartDate(), "yyyy-MM", "yyyyMM")
              + "'";
    }
    if (this.getSearchEndDate() != null && !getSearchEndDate().equals("")) {
      sql =
          sql
              + " and v.costDate <='"
              + DateConvert.convertAtoB(getSearchEndDate(), "yyyy-MM", "yyyyMM")
              + "'";
    }
    String state = this.getSearchPayState();
    if (state != null && !state.equals("")) {
      if (state != "0" && !state.equals("0")) {
        sql = sql + " and v.payState ='" + state + "'";
      }
    }

    // 构造查询
    String queryStr =
        " select v from ComsPayCommunication v where 1=1 " + sql + " order by v.termno,v.costDate";
    List<ComsPayCommunication> dataList = new ArrayList<ComsPayCommunication>();
    dataList.addAll(comsPayCommunicationDao.getAllDataByQuery(queryStr));
    if (dataList != null && dataList.size() > 0) {
      // 翻译
      for (ComsPayCommunication pay : dataList) {
        pay.setCostDate(
            AppCommonVarRegiter.changeTimeStyle("yyyyMM", pay.getCostDate(), "yyyy-MM"));
        // 运营机构
        pay.setOrgInnerCode01(orgDao.findByInnerCode(pay.getOrgInnerCode01()).getOrgNameCn());
        // 项目
        pay.setItemUid(comsProjectDao.findById(pay.getItemUid()).getItemName());
      }
      Authuser authuser = (Authuser) request.getSession().getAttribute("currentUser");
      filename = "通讯费支付历史明细报表.xls";

      Map beans = new HashMap();
      beans.put("orgName", orgSelected.getOrgNameCn());
      beans.put("authorUser", authuser.getRealName());
      Date createDate = new Date();
      beans.put("createDate", createDate);
      beans.put("dataList", dataList);
      HSSFWorkbook wb = new HSSFWorkbook();
      Configuration config = new Configuration();
      XLSTransformer transformer = new XLSTransformer(config);
      InputStream is = null;
      try {
        is = new FileInputStream(request.getRealPath("/WEB-INF") + filepath);
        wb = transformer.transformXLS(is, beans);
        // 产生POI输出流
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        wb.write(os);
        excelStream = new ByteArrayInputStream(os.toByteArray());
      } catch (IOException e) {
        e.printStackTrace();
      }
      message = "生成报表:" + filename;
      new LogAssistant(
              authuser.getUsername().trim(),
              request.getRemoteAddr() + ":" + TrackOpterationEnum.MAKEREPORT.getOperation(),
              message)
          .logUserOpt();
      return "excel";
    } else {
      message = "没有通讯费支付记录!";
    }
    return SUCCESS;
  }
Esempio n. 4
0
  @SuppressWarnings("unchecked")
  public String search() {
    pageBean = (PageBean) request.getSession().getAttribute("pageBean");
    if (pageBean == null) {
      pageBean = new PageBean();
      request.getSession().setAttribute("pageBean", pageBean);
    }
    if (this.getPageNo() <= 0) pageBean.setCurrentPage(1);
    else pageBean.setCurrentPage(this.getPageNo());

    Organization org = (Organization) request.getSession().getAttribute("currentOrganization");
    if (!orgCode.equals("")) {
      org = orgDAO.findById(orgCode);
    }

    // 构造查询
    String queryStr = "";
    String countStr = "";
    int min = 0, max = 10000000;
    if (!minMount.equals("")) {
      min = Integer.valueOf(minMount);
    }
    if (!maxMount.equals("")) {
      max = Integer.valueOf(maxMount);
    }
    String db_startDate = AppCommonVarRegiter.getXun(startDate, start_xun);
    String db_endDate = AppCommonVarRegiter.getXun(endDate, end_xun);
    String orgField = "";
    if (org.getOrgType().equals("00")) orgField = "orgInnerCode01";
    else if (org.getOrgType().equals("01")) orgField = "orgInnerCode02";
    else orgField = "orgInnerCode03";
    String matchCode = (String) orgDAO.getOrgInnerInfo(org)[0];
    String db_itemuid = "";
    String db_termno = "";
    String db_termseq = "";
    if (itemUids != null
        && !itemUids.equals("")
        && !itemUids.equals(AppCommonVarRegiter.ALL_VALUE_TAG)
        && !itemUids.equals(AppCommonVarRegiter.NULL_VALUE_TAG)) {
      db_itemuid = itemUids;
    }
    if (termnos != null
        && !termnos.equals("")
        && !termnos.equals(AppCommonVarRegiter.ALL_VALUE_TAG)
        && !termnos.equals(AppCommonVarRegiter.NULL_VALUE_TAG)) {
      db_termno = termnos;
    }
    if (termSeqs != null
        && !termSeqs.equals("")
        && !termSeqs.equals(AppCommonVarRegiter.ALL_VALUE_TAG)
        && !termSeqs.equals(AppCommonVarRegiter.NULL_VALUE_TAG)) {
      db_termseq = termSeqs.trim();
    }
    queryStr =
        "select m from TranStatXun m, Terminfo t where t.delflag is null and t.registType = '1' and t.termno = m.id.termno and t.termSeq = m.id.termseq and t."
            + orgField
            + " like '"
            + matchCode
            + "%' and t.itemUid like '"
            + db_itemuid
            + "%' and m.id.termno like '"
            + db_termno
            + "%' and m.id.termseq like '"
            + db_termseq
            + "%' and m.id.statDate >= '"
            + db_startDate
            + "' and m.id.statDate <= '"
            + db_endDate
            + "' and m.id.sourceTag = '"
            + sourceTag
            + "' and m.tranAmount >= "
            + min
            + " and m.tranAmount <= "
            + max
            + " order by m.id.statDate,m.id.termno,m.tranAmount";
    countStr =
        "select count(*) from TranStatXun m, Terminfo t where t.delflag is null and t.registType = '1' and t.termno = m.id.termno and t.termSeq = m.id.termseq and t."
            + orgField
            + " like '"
            + matchCode
            + "%' and t.itemUid like '"
            + db_itemuid
            + "%' and m.id.termno like '"
            + db_termno
            + "%' and m.id.termseq like '"
            + db_termseq
            + "%' and m.id.statDate >= '"
            + db_startDate
            + "' and m.id.statDate <= '"
            + db_endDate
            + "' and m.id.sourceTag = '"
            + sourceTag
            + "' and m.tranAmount >= "
            + min
            + " and m.tranAmount <= "
            + max;
    xunStatList.addAll(xunStatDAO.findByPageSupport(countStr, queryStr, pageBean));
    if (xunStatList.size() > 0) {
      // 翻译业务码
      for (TranStatXun data : xunStatList) {
        data.setTradeType(TranTypeLib.trantypeHs.get(data.getTradeType()));
      }
    }
    return SUCCESS;
  }
Esempio n. 5
0
 @SuppressWarnings("unchecked")
 public String excelReport() {
   // 从session中获取当前机构及当前用户
   Authuser user = (Authuser) request.getSession().getAttribute("currentUser");
   Organization org = null;
   if (!orgCode.equals("")) {
     org = orgDAO.findById(orgCode);
   } else {
     org = (Organization) request.getSession().getAttribute("currentOrganization");
   }
   String db_startDate = AppCommonVarRegiter.getXun(startDate, start_xun);
   String db_endDate = AppCommonVarRegiter.getXun(endDate, end_xun);
   // 构造查询
   String matchCode = (String) orgDAO.getOrgInnerInfo(org)[0];
   // 计算机构下终端总数
   int totalTerm = termDAO.countOrgTerms(matchCode, org.getOrgType());
   String orgField = "";
   if (org.getOrgType().equals("00")) orgField = "orgInnerCode01";
   else if (org.getOrgType().equals("01")) orgField = "orgInnerCode02";
   else orgField = "orgInnerCode03";
   int min = 0, max = 10000000;
   if (!minMount.equals("")) {
     min = Integer.valueOf(minMount);
   }
   if (!maxMount.equals("")) {
     max = Integer.valueOf(maxMount);
   }
   String db_itemuid = "";
   String db_termno = "";
   String db_termseq = "";
   if (itemUids != null
       && !itemUids.equals("")
       && !itemUids.equals(AppCommonVarRegiter.ALL_VALUE_TAG)
       && !itemUids.equals(AppCommonVarRegiter.NULL_VALUE_TAG)) {
     db_itemuid = itemUids;
   }
   if (termnos != null
       && !termnos.equals("")
       && !termnos.equals(AppCommonVarRegiter.ALL_VALUE_TAG)
       && !termnos.equals(AppCommonVarRegiter.NULL_VALUE_TAG)) {
     db_termno = termnos;
   }
   if (termSeqs != null
       && !termSeqs.equals("")
       && !termSeqs.equals(AppCommonVarRegiter.ALL_VALUE_TAG)
       && !termSeqs.equals(AppCommonVarRegiter.NULL_VALUE_TAG)) {
     db_termseq = termSeqs.trim();
   }
   xunStatList.addAll(
       xunStatDAO.findByQuery(
           orgField,
           matchCode,
           db_itemuid,
           db_termno,
           db_termseq,
           db_startDate,
           db_endDate,
           sourceTag,
           min,
           max));
   if (xunStatList.size() > 0) {
     // 总交易笔数
     int totalTrade = 0;
     // 翻译业务码
     for (TranStatXun data : xunStatList) {
       data.setTradeType(TranTypeLib.trantypeHs.get(data.getTradeType()));
       // 交易笔数汇总
       totalTrade = totalTrade + data.getTranAmount();
       // 填写地址信息
       data.setAddress(
           termDAO.getAddresOfTerm(data.getId().getTermno(), data.getId().getTermseq()));
     }
     // 模板位置
     String templetFile = "";
     templetFile = context.getRealPath(File.separator + templateFileName);
     fileName = org.getOrgNameCn() + "交易旬累计汇总报表_" + startDate + "_" + endDate + ".xls";
     // 构造参数Beans
     Map beans = new HashMap();
     beans.put("orgName", org.getOrgNameCn());
     beans.put("dataList", xunStatList);
     beans.put("authorUser", user.getRealName());
     SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
     String makeDate = df.format(new Date());
     beans.put("createDate", makeDate);
     // 总台数
     beans.put("termNum", totalTerm);
     // 交易总笔数
     beans.put("tradeNum", totalTrade);
     float avg = 0.0f;
     if (totalTerm > 0) avg = totalTrade / totalTerm;
     beans.put("avg", avg);
     excelStream = new ExcelReport().makeReportFromTemplet(templetFile, beans);
     if (excelStream == null) {
       message = "报表模板文件不存在,请确认报表模板文件已放入指定位置!";
       request.getSession().setAttribute("ERRORMEG", message);
       return INPUT;
     }
     return "excel";
   }
   message = org.getOrgNameCn() + "在" + startDate + "-" + endDate + "期间没有旬累计汇总数据!";
   return SUCCESS;
 }