Example #1
0
 public int insertOldSbsj() {
   String sql =
       "select c.CODE,c.name,a.start_date,a.end_date,a.NORMAL_CNT,a.BACK_CNT,a.DEPOSE_CNT,b.machine_no,(a.NORMAL_CNT+a.BACK_CNT+a.DEPOSE_CNT) as totalSum,a.normal_sum,a.back_sum,(select count(SN) as num from SERVER_DECLARE_MAKE WHERE substring(DATETIME,0,9)>=a.start_date AND substring(DATETIME,0,9)<=a.end_date AND MACHINE_NO IN(SELECT MACHINE_NO FROM SERVER_TAXPAYER_FISCAL  WHERE USER_SN IN(SELECT SN FROM SERVER_TAXPAYER_USER WHERE CODE=c.CODE))) AS YSBSUM  FROM SERVER_DECLARE a  left join SERVER_TAXPAYER_FISCAL b  on a.FISCAL_CARD_NO=b.FISCAL_CARD_NO left outer join SERVER_TAXPAYER_USER c on b.USER_SN = c.SN WHERE a.START_DATE>='20110101' AND a.END_DATE<='20110228' group by c.CODE,a.start_date,a.end_date,c.name,a.NORMAL_CNT,a.BACK_CNT,a.DEPOSE_CNT,a.normal_sum,a.back_sum,b.machine_no";
   int result = Query.executeSql(sql);
   if (result == -1) {
     result = 1;
   } else result = -1;
   return result;
 }
Example #2
0
 // 查询纳税户领购发票信息
 public ArrayList selectFplg(String sqlStr) {
   ArrayList al = null;
   Connection conn = null;
   try {
     conn = DBConnection.getConnection();
     if (conn != null) {
       String sql =
           "select a.*,b.FPMC,c.NSRMC from SKQ_FPJMX a left outer join SKQ_FP b on a.FPBM=b.FPBM left outer join SKQ_NSRXX c on a.NSRWJBM=c.NSRWJBM where 1=1 and a.SID in("
               + sqlStr
               + ") order by a.fpqsh DESC";
       PreparedStatement ps = conn.prepareStatement(sql);
       ResultSet rs = ps.executeQuery();
       al = new ArrayList();
       while (rs.next()) {
         Fpjmx fpjmx = new Fpjmx();
         fpjmx.setSid(rs.getInt("SID"));
         fpjmx.setNsrwjbm(rs.getString("NSRWJBM"));
         fpjmx.setNsrmc(Util.iso8859togbk(rs.getString("NSRMC")));
         fpjmx.setFpbm(rs.getString("FPBM"));
         fpjmx.setFpmc(Util.iso8859togbk(rs.getString("FPMC")));
         fpjmx.setFpdm(rs.getString("FPDM"));
         fpjmx.setFpqsh(rs.getInt("FPQSH"));
         fpjmx.setFpjzh(rs.getInt("FPJZH"));
         fpjmx.setFpdw(rs.getInt("FPDW"));
         fpjmx.setJqbh(rs.getString("JQBH"));
         fpjmx.setFpxfzt(rs.getInt("FPXFZT"));
         fpjmx.setFplgzt(rs.getInt("FPLGZT"));
         fpjmx.setMxsczt(rs.getInt("MXSCZT"));
         fpjmx.setFpzs(rs.getInt("FPJZH") + 1 - rs.getInt("FPQSH"));
         fpjmx.setFplgrq(rs.getString("FPLGRQ").substring(0, 10));
         String sql1 =
             "select top 1 * from SKQ_FPKJ where FPDM='"
                 + Util.zerosFront(rs.getString("FPDM"), 20)
                 + "' and JQBH='"
                 + fpjmx.getJqbh()
                 + "' and FPHM>="
                 + rs.getInt("FPQSH")
                 + " and"
                 + " FPHM<="
                 + rs.getInt("FPJZH")
                 + " order by FPHM DESC";
         System.out.println("sql1==" + sql1);
         int hasUsed = 0;
         int fpjzhm = Query.getFieldInt(sql1, "FPHM"); // 发票截至号码
         System.out.println(fpjzhm);
         if (fpjzhm == 0) {
           hasUsed = 0;
         } else {
           hasUsed = fpjzhm + 1 - fpjmx.getFpqsh();
         }
         fpjmx.setHasUsed(hasUsed);
         al.add(fpjmx);
       }
       rs.close();
       ps.close();
     }
   } catch (SQLException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
   } finally {
     if (conn != null) {
       try {
         conn.close();
       } catch (SQLException e) {
         // TODO Auto-generated catch block
         e.printStackTrace();
       }
     }
   }
   return al;
 }