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; }