Example #1
0
 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;
 }
Example #2
0
  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;
  }