Exemple #1
0
  public static Table assetSummaryDetail(String beginDate, String endDate, String orgIDs) {
    // System.out.println("2----- beginDate="+beginDate+"--endDate="+endDate+"--orgIDs="+orgIDs);
    String orclSql = "", msSql = "", mySql = "", orgCondition = "";
    String beginDateOrcl = "",
        endDateOrcl = "",
        beginDateMS = "",
        endDateMS = "",
        beginDateMySQL = "",
        endDateMySQL = "";

    beginDateOrcl = "to_date('" + beginDate + "','YYYY-MM-DD')";
    endDateOrcl = "to_date('" + endDate + "','YYYY-MM-DD')";

    beginDateMS = "cast('" + beginDate + "' as datetime) ";
    endDateMS = "cast('" + endDate + "' as datetime) ";

    beginDateMySQL = "str_to_date('" + beginDate + "','%Y-%m-%d')";
    endDateMySQL = "str_to_date('" + endDate + "','%Y-%m-%d')";

    if (orgIDs.equals("") || orgIDs.equals(null)) {
      orgCondition = " and 1=1 ";
    } else {
      orgIDs = "'" + orgIDs + "'";
      orgIDs = orgIDs.replace(" ", "','");
      orgCondition = " and m.fCreateDeptID in (" + orgIDs + ")";
    }

    orclSql =
        "select t.fkind FKINDNAME,t.FNAME,sum(t.fBuyNum) NUM,sum(t.fAmount) AMOUNT from (select d.fkind,d.fname,fBuyNum,d.fAmount from OA_AS_BuyApplyD d join OA_AS_BuyApplyM m on d.fmasterid=m.fid and m.fBizState='bsFinished' and m.fcreatetime >= "
            + beginDateOrcl
            + " and m.fcreatetime <= "
            + endDateOrcl
            + orgCondition
            + ") t group by t.fkind,t.fname order by t.fkind,t.fname";
    msSql =
        "select t.fkind FKINDNAME,t.FNAME,sum(t.fBuyNum) NUM,sum(t.fAmount) AMOUNT from (select d.fkind,d.fname,fBuyNum,d.fAmount from OA_AS_BuyApplyD d join OA_AS_BuyApplyM m on d.fmasterid=m.fid and m.fBizState='bsFinished' and m.fcreatetime >= "
            + beginDateMS
            + " and m.fcreatetime <= "
            + endDateMS
            + orgCondition
            + ") t group by t.fkind,t.fname order by t.fkind,t.fname";
    mySql =
        "select t.fkind FKINDNAME,t.FNAME,sum(t.fBuyNum) NUM,sum(t.fAmount) AMOUNT from (select d.fkind,d.fname,fBuyNum,d.fAmount from OA_AS_BuyApplyD d join OA_AS_BuyApplyM m on d.fmasterid=m.fid and m.fBizState='bsFinished' and m.fcreatetime >= "
            + beginDateMySQL
            + " and m.fcreatetime <= "
            + endDateMySQL
            + orgCondition
            + ") t group by t.fkind,t.fname order by t.fkind,t.fname";
    // System.out.println("orclSql="+orclSql);
    HashMap<String, String> sqlMap = new HashMap<String, String>();
    sqlMap.put("ORACLE", orclSql);
    sqlMap.put("MSSQL", msSql);
    sqlMap.put("MYSQL", mySql);
    return SQL.select(sqlMap, null, "/OA/asset/data");
  }
Exemple #2
0
  public static Table assetSummaryMain(
      String beginDate, String endDate, String dayNum, String orgIDs) throws DocumentException {
    // System.out.println("1-----
    // beginDate="+beginDate+"--endDate="+endDate+"--dayNum="+dayNum+"--orgIDs="+orgIDs);
    String orclSql = "", msSql = "", mySql = "", orgCondition = "";
    String beginDateOrcl = "",
        endDateOrcl = "",
        beginDateMS = "",
        endDateMS = "",
        beginDateMySQL = "",
        endDateMySQL = "";
    String dbNameORCL = "", dbNameMS = "", dbNameMySQL = "";
    String webAbsPath = WEBConstants.getWebRootAbsPath();
    String configPath =
        webAbsPath.substring(0, webAbsPath.indexOf("apache-tomcat"))
            + "apache-tomcat\\conf\\server.xml";
    SAXReader xmlReader = new SAXReader();
    Node ndORCL =
        xmlReader
            .read(configPath)
            .getRootElement()
            .selectSingleNode("//Resource[@name='system']/@username");
    dbNameORCL = ndORCL.getStringValue();
    Node ndMS =
        xmlReader
            .read(configPath)
            .getRootElement()
            .selectSingleNode("//Resource[@name='system']/@url");
    dbNameMS =
        ndMS.getStringValue()
            .substring(ndMS.getStringValue().lastIndexOf("/") + 1, ndMS.getStringValue().length());
    dbNameMySQL = dbNameMS;

    beginDateOrcl = "to_date('" + beginDate + "','YYYY-MM-DD')";
    endDateOrcl = "to_date('" + endDate + "','YYYY-MM-DD')";

    beginDateMS = "cast('" + beginDate + "' as datetime) ";
    endDateMS = "cast('" + endDate + "' as datetime) ";

    beginDateMySQL = "str_to_date('" + beginDate + "','%Y-%m-%d')";
    endDateMySQL = "str_to_date('" + endDate + "','%Y-%m-%d')";

    if (orgIDs.equals("") || orgIDs.equals(null)) {
      orgCondition = " and 1=1 ";
    } else {
      orgIDs = "'" + orgIDs + "'";
      orgIDs = orgIDs.replace(" ", "','");
      orgCondition = " and m.fCreateDeptID in (" + orgIDs + ")";
    }
    if (dayNum.equals("") || dayNum.equals(null)) {
      orclSql =
          "select t1.ct SQDSL,null CYYDZQRWS,null DYYDZQRWS,null DAYNUM,"
              + beginDateOrcl
              + " BEGINDATE,"
              + endDateOrcl
              + " ENDDATE from "
              + "(select count(m.fid) ct from OA_AS_BuyApplyM m where m.fcreatetime >= "
              + beginDateOrcl
              + " and m.fcreatetime <= "
              + endDateOrcl
              + orgCondition
              + ") t1";

      msSql =
          "select t1.ct SQDSL,null CYYDZQRWS,null DYYDZQRWS,null DAYNUM,"
              + beginDateMS
              + " BEGINDATE,"
              + endDateMS
              + " ENDDATE from "
              + "(select count(m.fid) ct from OA_AS_BuyApplyM m where m.fcreatetime >= "
              + beginDateMS
              + " and m.fcreatetime <= "
              + endDateMS
              + orgCondition
              + ") t1";

      mySql =
          "select t1.ct SQDSL,null CYYDZQRWS,null DYYDZQRWS,null DAYNUM,"
              + beginDateMS
              + " BEGINDATE,"
              + endDateMS
              + " ENDDATE from "
              + "(select count(m.fid) ct from OA_AS_BuyApplyM m where m.fcreatetime >= "
              + beginDateMS
              + " and m.fcreatetime <= "
              + endDateMS
              + orgCondition
              + ") t1";
    } else {
      orclSql =
          "select t1.ct SQDSL,t2.ct CYYDZQRWS,t3.ct DYYDZQRWS,"
              + dayNum
              + " DAYNUM,"
              + beginDateOrcl
              + " BEGINDATE,"
              + endDateOrcl
              + " ENDDATE from "
              + "(select count(m.fid) ct from OA_AS_BuyApplyM m where m.fcreatetime >= "
              + beginDateOrcl
              + " and m.fcreatetime <= "
              + endDateOrcl
              + orgCondition
              + ") t1,"
              + "(select count(d.fid) ct from (select fid from OA_AS_BuyApplyM m where m.fcreatetime >= "
              + beginDateOrcl
              + " and m.fcreatetime <= "
              + endDateOrcl
              + orgCondition
              + " ) d join "
              + dbNameORCL
              + ".sa_task s on d.fid=s.sdata1 and s.sparentid is null and (s.sstatusid = 'tesFinished' and s.sactualfinishtime > s.sactualstarttime +("
              + dayNum
              + "-1))) t2,"
              + "(select count(d.fid) ct from (select fid from OA_AS_BuyApplyM m where m.fcreatetime >= "
              + beginDateOrcl
              + " and m.fcreatetime <= "
              + endDateOrcl
              + orgCondition
              + " ) d join "
              + dbNameORCL
              + ".sa_task s on d.fid=s.sdata1 and s.sparentid is null and (s.sstatusid = 'tesFinished' and s.sactualfinishtime < =s.sactualstarttime + ("
              + dayNum
              + "-1))) t3";

      msSql =
          "select t1.ct SQDSL,t2.ct CYYDZQRWS,t3.ct DYYDZQRWS,"
              + dayNum
              + " DAYNUM,"
              + beginDateMySQL
              + " BEGINDATE,"
              + endDateMySQL
              + " ENDDATE from "
              + "(select count(m.fid) ct from OA_AS_BuyApplyM m where m.fcreatetime >= "
              + beginDateMySQL
              + " and m.fcreatetime <= "
              + endDateMySQL
              + orgCondition
              + ") t1,"
              + "(select count(d.fid) ct from (select fid from OA_AS_BuyApplyM m where m.fcreatetime >= "
              + beginDateMySQL
              + " and m.fcreatetime <= "
              + endDateMySQL
              + orgCondition
              + " ) d join "
              + dbNameMySQL
              + ".sa_task s on d.fid=s.sdata1 and s.sparentid is null and (s.sstatusid = 'tesFinished' and s.sactualfinishtime > s.sactualstarttime +("
              + dayNum
              + "-1))) t2,"
              + "(select count(d.fid) ct from (select fid from OA_AS_BuyApplyM m where m.fcreatetime >= "
              + beginDateMySQL
              + " and m.fcreatetime <= "
              + endDateMySQL
              + orgCondition
              + " ) d join "
              + dbNameMySQL
              + ".sa_task s on d.fid=s.sdata1 and s.sparentid is null and (s.sstatusid = 'tesFinished' and s.sactualfinishtime <= s.sactualstarttime + ("
              + dayNum
              + "-1))) t3";

      mySql =
          "select t1.ct SQDSL,t2.ct CYYDZQRWS,t3.ct DYYDZQRWS,"
              + dayNum
              + " DAYNUM,"
              + beginDateMySQL
              + " BEGINDATE,"
              + endDateMySQL
              + " ENDDATE from "
              + "(select count(m.fid) ct from OA_AS_BuyApplyM m where m.fcreatetime >= "
              + beginDateMySQL
              + " and m.fcreatetime <= "
              + endDateMySQL
              + orgCondition
              + ") t1,"
              + "(select count(d.fid) ct from (select fid from OA_AS_BuyApplyM m where m.fcreatetime >= "
              + beginDateMySQL
              + " and m.fcreatetime <= "
              + endDateMySQL
              + orgCondition
              + " ) d join "
              + dbNameMySQL
              + ".sa_task s on d.fid=s.sdata1 and s.sparentid is null and (s.sstatusid = 'tesFinished' and s.sactualfinishtime > date_add(s.sactualstarttime ,INTERVAL ("
              + dayNum
              + "-1) day))) t2,"
              + "(select count(d.fid) ct from (select fid from OA_AS_BuyApplyM m where m.fcreatetime >= "
              + beginDateMySQL
              + " and m.fcreatetime <= "
              + endDateMySQL
              + orgCondition
              + " ) d join "
              + dbNameMySQL
              + ".sa_task s on d.fid=s.sdata1 and s.sparentid is null and (s.sstatusid = 'tesFinished' and s.sactualfinishtime <= date_add(s.sactualstarttime ,INTERVAL ("
              + dayNum
              + "-1) day))) t3";
    }

    HashMap<String, String> sqlMap = new HashMap<String, String>();
    sqlMap.put("ORACLE", orclSql);
    sqlMap.put("MSSQL", msSql);
    sqlMap.put("MYSQL", mySql);

    return SQL.select(sqlMap, null, "/OA/asset/data");
  }