public SQLModel getPageQueryModel() throws SQLModelException {
AmsAssetsAddressVDTO dto = (AmsAssetsAddressVDTO) dtoParameter;
SQLModel sqlModel = new SQLModel();
List sqlArgs = new ArrayList();
String sqlStr =
"SELECT EOCM.ORGANIZATION_ID,\n"
+ " EOCM.COMPANY,\n"
+ " COUNT(1) LASTING_COUNT\n"
+ "FROM ETS_ITEM_INFO EII,\n"
+ " ETS_OU_CITY_MAP EOCM\n"
+ "WHERE EII.ORGANIZATION_ID = EOCM.ORGANIZATION_ID\n"
+
// "AND EII.ATTRIBUTE1 = 'RENT'\n" +
"AND EII.FINANCE_PROP = 'RENT_ASSETS'\n"
+ "AND ( EII.DISABLE_DATE IS NULL OR EII.DISABLE_DATE "
+ SyBaseSQLUtil.isNullNoParam()
+ " ) \n"
+ "AND ("
+ SyBaseSQLUtil.nullIntParam()
+ " OR EII.ORGANIZATION_ID = ?)\n"
+ "GROUP BY EOCM.ORGANIZATION_ID,\n"
+ " EOCM.COMPANY";
// sqlArgs.add(dto.getOrganizationId());
// sqlArgs.add(dto.getOrganizationId());
SyBaseSQLUtil.nullIntParamArgs(sqlArgs, dto.getOrganizationId());
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
return sqlModel;
}
/**
* 得到查询所有的MODEL
*
* @return
*/
public SQLModel getPageQueryModel() throws SQLModelException {
String fromDate = "";
String toDate = "";
// String orgId = "";
int orgId = SyBaseSQLUtil.NULL_INT_VALUE;
boolean hasSqlProduced = false;
SQLModel sqlModel = new SQLModel();
final String CONDITION_SQL = "{CONDITION_SQL}";
try {
SitusStatisticsDTO Situsdto = (SitusStatisticsDTO) dtoParameter;
orgId = Situsdto.getOrganizationId();
List strArg = new ArrayList();
String strSql = "";
String tmpASqlStr =
" SELECT\n" // 按时完成工单
+ " SU.USERNAME,\n"
+ " COUNT(EW.SYSTEMID) COUNT1\n"
+ " FROM \n"
+ " ETS_WORKORDER EW,\n"
+ " SF_USER SU\n"
+ " WHERE\n"
+ " CONVERT(VARCHAR,EW.IMPLEMENT_BY) *= CONVERT(VARCHAR,SU.USER_ID) AND\n"
+ " EW.UPLOAD_DATE <= dbo.AWP_GET_DEADLINE_DATE(EW.START_DATE,EW.IMPLEMENT_DAYS) AND\n"
+ " CONVERT(INT,EW.WORKORDER_FLAG) > "
+ DictConstant.WORKORDER_STATUS_NEW
+ " AND\n"
+ " CONVERT(INT,EW.WORKORDER_FLAG) < "
+ DictConstant.WORKORDER_STATUS_CANCELE
+ "\n"
+ " "
+ CONDITION_SQL
+ "\n"
+ " GROUP BY\n"
+ " SU.USERNAME\n";
String tmpBSqlStr =
" SELECT\n " // 超时完成工单
+ " SU.USERNAME,\n"
+ " COUNT(EW.SYSTEMID) COUNT2\n"
+ " FROM \n"
+ " ETS_WORKORDER EW,\n"
+ " SF_USER SU\n"
+ " WHERE\n"
+ " CONVERT(VARCHAR,EW.IMPLEMENT_BY) *= CONVERT(VARCHAR,SU.USER_ID) AND\n"
+ " EW.UPLOAD_DATE > dbo.AWP_GET_DEADLINE_DATE(EW.START_DATE,EW.IMPLEMENT_DAYS) AND\n"
+ " CONVERT(INT,EW.WORKORDER_FLAG) > "
+ DictConstant.WORKORDER_STATUS_NEW
+ " AND\n"
+ " CONVERT(INT,EW.WORKORDER_FLAG) < "
+ DictConstant.WORKORDER_STATUS_CANCELE
+ "\n"
+ " "
+ CONDITION_SQL
+ "\n"
+ " GROUP BY\n"
+ " SU.USERNAME\n";
String tmpCSqlStr =
" SELECT\n" // 超时未完成工单
+ " SU.USERNAME,\n"
+ " COUNT(EW.SYSTEMID) COUNT3\n"
+ " FROM \n"
+ " ETS_WORKORDER EW,\n"
+ " SF_USER SU\n"
+ " WHERE\n"
+ " CONVERT(VARCHAR,EW.IMPLEMENT_BY) *= CONVERT(VARCHAR,SU.USER_ID) AND\n"
+ " EW.UPLOAD_DATE "
+ SyBaseSQLUtil.isNullNoParam()
+ " AND\n"
+ " dbo.AWP_GET_DEADLINE_DATE(EW.START_DATE,EW.IMPLEMENT_DAYS) < GETDATE() AND\n"
+ " CONVERT(INT,EW.WORKORDER_FLAG) > "
+ DictConstant.WORKORDER_STATUS_NEW
+ " AND\n"
+ " CONVERT(INT,EW.WORKORDER_FLAG) < "
+ DictConstant.WORKORDER_STATUS_CANCELE
+ "\n"
+ " "
+ CONDITION_SQL
+ "\n"
+ " GROUP BY\n"
+ " SU.USERNAME\n";
String tmpDSqlStr =
" SELECT\n" // 总工单数
+ " SU.USERNAME,\n"
+ " COUNT(EW.SYSTEMID) TOTAL_COUNT\n"
+ " FROM \n"
+ " ETS_WORKORDER EW,\n"
+ " SF_USER SU\n"
+ " WHERE\n"
+ " CONVERT(VARCHAR,EW.IMPLEMENT_BY) *= CONVERT(VARCHAR,SU.USER_ID) AND\n"
+ " CONVERT(INT,EW.WORKORDER_FLAG) > "
+ DictConstant.WORKORDER_STATUS_NEW
+ " AND\n"
+ " CONVERT(INT,EW.WORKORDER_FLAG) < "
+ DictConstant.WORKORDER_STATUS_CANCELE
+ "\n"
+ " "
+ CONDITION_SQL
+ "\n"
+ " GROUP BY SU.USERNAME \n";
String tmpESqlStr =
" SELECT\n" // 正常处理中工单
+ " SU.USERNAME,\n"
+ " COUNT(EW.SYSTEMID) COUNT4\n"
+ " FROM \n"
+ " ETS_WORKORDER EW,\n"
+ " SF_USER SU\n"
+ " WHERE\n"
+ " CONVERT(VARCHAR,EW.IMPLEMENT_BY) *= CONVERT(VARCHAR,SU.USER_ID) AND\n"
+ " ( ?='' OR EW.UPLOAD_DATE LIKE ? )AND\n"
+ " dbo.AWP_GET_DEADLINE_DATE(EW.START_DATE,EW.IMPLEMENT_DAYS) >= GETDATE() AND\n"
+ " CONVERT(INT,EW.WORKORDER_FLAG) > "
+ DictConstant.WORKORDER_STATUS_NEW
+ " AND\n"
+ " CONVERT(INT,EW.WORKORDER_FLAG) < "
+ DictConstant.WORKORDER_STATUS_CANCELE
+ "\n"
+ " "
+ CONDITION_SQL
+ "\n"
+ " GROUP BY\n"
+ " SU.USERNAME\n";
strArg.add("");
strArg.add("");
String sqlCondit = "";
if (!StrUtil.isEmpty(Situsdto.getFromDate())) {
sqlCondit += " AND EW.DISTRIBUTE_DATE >= '" + Situsdto.getFromDate() + "'\n";
}
if (!StrUtil.isEmpty(Situsdto.getToDate())) {
sqlCondit +=
" AND dateadd(day,-1,EW.DISTRIBUTE_DATE) <= '" + Situsdto.getFromDate() + "'\n";
}
if (orgId == SyBaseSQLUtil.NULL_INT_VALUE) {
sqlCondit += " AND 1<>1\n";
} else if (orgId > 0) {
sqlCondit += " AND CONVERT(VARCHAR,EW.ORGANIZATION_ID) =' " + orgId + "'\n";
}
String sqlStr =
"SELECT\n"
+ " TMP_D.USERNAME,\n"
+ " dbo.NVL(TMP_A.COUNT1, 0) IN_TIME_COUNT,\n"
+ " dbo.NVL(TMP_B.COUNT2, 0) OVER_TIME_COUNT1,\n"
+ " dbo.NVL(TMP_C.COUNT3, 0) OVER_TIME_COUNT2,"
+ " dbo.NVL(TMP_E.COUNT4, 0) NORMAL_PROCESS_COUNT,"
+ " CONVERT(VARCHAR,ROUND((dbo.NVL(TMP_A.COUNT1, 0)/TMP_D.TOTAL_COUNT),2)*100)||'%' RATE\n"
+ " FROM\n "
+ " ("
+ tmpASqlStr
+ ") TMP_A,\n"
+ " ("
+ tmpBSqlStr
+ ") TMP_B,\n"
+ " ("
+ tmpCSqlStr
+ ") TMP_C,\n"
+ " ("
+ tmpDSqlStr
+ ") TMP_D,\n"
+ " ("
+ tmpESqlStr
+ ") TMP_E\n"
+ " WHERE\n"
+ " TMP_D.USERNAME *= TMP_A.USERNAME AND\n"
+ " TMP_D.USERNAME *= TMP_B.USERNAME AND\n"
+ " TMP_D.USERNAME *= TMP_C.USERNAME AND\n"
+ " TMP_D.USERNAME *= TMP_E.USERNAME\n";
sqlStr = StrUtil.replaceStr(sqlStr, CONDITION_SQL, sqlCondit);
hasSqlProduced = true;
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(strArg);
} catch (CalendarException ex) {
ex.printLog();
throw new SQLModelException(ex);
}
return sqlModel;
}
