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;
}
// 查询纳税户领购发票信息
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;
}