public List qryCurrentMon(
Integer i,
Integer tyear,
Integer tmonth,
Integer year,
Integer month,
String date,
Integer sstore) {
String[] datete = date.split("/");
Integer yearthree = 0;
Integer monththree = 0;
yearthree = Days.getYearMonth(Integer.parseInt(datete[0]), Integer.parseInt(datete[1]), 2)[0];
monththree = Days.getYearMonth(Integer.parseInt(datete[0]), Integer.parseInt(datete[1]), 2)[1];
StringBuffer s = new StringBuffer(1024);
switch (i) {
case 1:
s.append(" DATE_FORMAT(DATE,'%Y%m%d') >=" + datete[0] + datete[1] + "01" + " \r\n");
s.append("AND DATE_FORMAT(DATE,'%Y%m%d') <=" + datete[0] + datete[1] + "31" + " \r\n");
break;
case 2:
Integer yearthree1 = 0;
Integer monththree1 = 0;
yearthree1 =
Days.getYearMonth(Integer.parseInt(datete[0]), Integer.parseInt(datete[1]), 3)[0];
monththree1 =
Days.getYearMonth(Integer.parseInt(datete[0]), Integer.parseInt(datete[1]), 3)[1];
s.append(
" DATE_FORMAT(DATE,'%Y%m%d') >=" + yearthree1 + auto(monththree1) + "01" + " \r\n");
s.append("AND DATE_FORMAT(DATE,'%Y%m%d') <=" + datete[0] + datete[1] + "31" + " \r\n");
break;
case 3:
Integer yearthree2 = 0;
Integer monththree2 = 0;
yearthree2 =
Days.getYearMonth(Integer.parseInt(datete[0]), Integer.parseInt(datete[1]), 6)[0];
monththree2 =
Days.getYearMonth(Integer.parseInt(datete[0]), Integer.parseInt(datete[1]), 6)[1];
Integer yearthree3 = 0;
Integer monththree3 = 0;
yearthree3 =
Days.getYearMonth(Integer.parseInt(datete[0]), Integer.parseInt(datete[1]), 4)[0];
monththree3 =
Days.getYearMonth(Integer.parseInt(datete[0]), Integer.parseInt(datete[1]), 4)[1];
s.append(
" DATE_FORMAT(DATE,'%Y%m%d') >=" + yearthree2 + auto(monththree2) + "01" + " \r\n");
s.append(
"AND DATE_FORMAT(DATE,'%Y%m%d') <=" + yearthree3 + auto(monththree3) + "31" + " \r\n");
break;
case 4:
Integer yearthree4 = 0;
Integer monththree4 = 0;
yearthree4 =
Days.getYearMonth(Integer.parseInt(datete[0]), Integer.parseInt(datete[1]), 6)[0];
monththree4 =
Days.getYearMonth(Integer.parseInt(datete[0]), Integer.parseInt(datete[1]), 6)[1];
s.append(" DATE_FORMAT(DATE,'%Y%m%d') <=" + yearthree4 + monththree4 + "31" + " \r\n");
break;
}
StringBuffer sql = new StringBuffer(" ");
sql.append("SELECT Count(0) GRADECOUNT,sum(cusdebt.DEBT) DEBT,customer.GRADE GRADE \r\n");
sql.append(" FROM customer ,cusdebt WHERE customer.CUSID = cusdebt.CUSID AND \r\n");
sql.append(s);
sql.append(" AND cusdebt.STOREID= '" + sstore + "'");
List res = dbUtil.getQueryList(sql.toString(), null);
return res;
}
public List getcusdebt(
Integer i, String c, Integer year, Integer month, String date, Integer sstore) {
String[] datete = date.split("/");
Integer yearthree = 0;
Integer monththree = 0;
yearthree = Days.getYearMonth(Integer.parseInt(datete[0]), Integer.parseInt(datete[1]), 2)[0];
monththree = Days.getYearMonth(Integer.parseInt(datete[0]), Integer.parseInt(datete[1]), 2)[1];
String strmonth = String.format("[0]", monththree);
StringBuffer s = new StringBuffer(" ");
switch (i) {
case 1:
s.append(" DATE_FORMAT(DATE,'%Y%m%d') >=" + datete[0] + datete[1] + "01" + " \r\n");
s.append("AND DATE_FORMAT(DATE,'%Y%m%d') <=" + datete[0] + datete[1] + "31" + " \r\n");
break;
case 2:
Integer yearthree1 = 0;
Integer monththree1 = 0;
yearthree1 =
Days.getYearMonth(Integer.parseInt(datete[0]), Integer.parseInt(datete[1]), 3)[0];
monththree1 =
Days.getYearMonth(Integer.parseInt(datete[0]), Integer.parseInt(datete[1]), 3)[1];
s.append(
" DATE_FORMAT(DATE,'%Y%m%d') >=" + yearthree1 + auto(monththree1) + "01" + " \r\n");
s.append("AND DATE_FORMAT(DATE,'%Y%m%d') <=" + datete[0] + datete[1] + "31" + " \r\n");
break;
case 3:
Integer yearthree2 = 0;
Integer monththree2 = 0;
yearthree2 =
Days.getYearMonth(Integer.parseInt(datete[0]), Integer.parseInt(datete[1]), 6)[0];
monththree2 =
Days.getYearMonth(Integer.parseInt(datete[0]), Integer.parseInt(datete[1]), 6)[1];
Integer yearthree3 = 0;
Integer monththree3 = 0;
yearthree3 =
Days.getYearMonth(Integer.parseInt(datete[0]), Integer.parseInt(datete[1]), 4)[0];
monththree3 =
Days.getYearMonth(Integer.parseInt(datete[0]), Integer.parseInt(datete[1]), 4)[1];
s.append(
" DATE_FORMAT(DATE,'%Y%m%d') >=" + yearthree2 + auto(monththree2) + "01" + " \r\n");
s.append(
"AND DATE_FORMAT(DATE,'%Y%m%d') <=" + yearthree3 + auto(monththree3) + "31" + " \r\n");
break;
case 4:
Integer yearthree4 = 0;
Integer monththree4 = 0;
yearthree4 =
Days.getYearMonth(Integer.parseInt(datete[0]), Integer.parseInt(datete[1]), 6)[0];
monththree4 =
Days.getYearMonth(Integer.parseInt(datete[0]), Integer.parseInt(datete[1]), 6)[1];
s.append("AND DATE_FORMAT(DATE,'%Y%m%d') <=" + yearthree4 + monththree4 + "31" + " \r\n");
break;
}
StringBuffer sql = new StringBuffer(" ");
sql.append(
"SELECT customer.NAME CUSNAME,cusdebt.DEBT,cusdebt.DEPART,cusdebt.STOREID,customer.GRADE AS GRADE,cusdebt.DEBT,cusdebt.`DATE`,cusdebt.REMARK \r\n");
sql.append("FROM customer ,cusdebt \r\n");
sql.append(
"WHERE customer.CUSID = cusdebt.CUSID AND customer.GRADE='" + c + "' AND \r\n");
sql.append(s);
sql.append("AND STOREID= '" + sstore + "'");
List res = dbUtil.getQueryList(sql.toString(), null);
return res;
}