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;
}
/**
* 功能：框架自动生成应急保障设备名称字典表 AMS_YJ_ITEM页面翻页查询SQLModel，请根据实际需要修改。
*
* @return SQLModel 返回页面翻页查询SQLModel
* @throws SQLModelException 发生日历异常时转化为该异常抛出
*/
public SQLModel getPageQueryModel() throws SQLModelException {
SQLModel sqlModel = new SQLModel();
List sqlArgs = new ArrayList();
AmsYjItemDTO amsYjItem = (AmsYjItemDTO) dtoParameter;
String sqlStr =
"SELECT "
+ " CONVERT(VARCHAR,ITEM_CODE) ITEM_CODE,"
+ " ITEM_NAME,"
+ " ITEM_CATEGORY,"
+ " CREATION_DATE,"
+ " dbo.APP_GET_USER_NAME(CREATE_USER) CREATE_USER,"
+ " LAST_UPDATE_DATE,"
+ " dbo.APP_GET_USER_NAME(LAST_UPDATE_USER) LAST_UPDATE_USER,"
+ " DISABLE_DATE"
+ " FROM"
+ " AMS_YJ_ITEM"
+ " WHERE"
+ " ("
+ SyBaseSQLUtil.isNull()
+ " OR ITEM_NAME LIKE ?)";
if (!amsYjItem.getItemCode().equals("")) {
sqlStr += " AND ITEM_CODE = CONVERT(FLOAT,?)";
} else {
sqlStr += " AND CONVERT(FLOAT,?) =0 ";
}
sqlArgs.add(amsYjItem.getItemName());
sqlArgs.add(amsYjItem.getItemName());
sqlArgs.add(amsYjItem.getItemCode());
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
return sqlModel;
}
// 归还单查询
public SQLModel getPageQueryModel() throws SQLModelException {
SQLModel sqlModel = new SQLModel();
try {
List sqlArgs = new ArrayList();
AmsInstrumentHDTO amsInstrumentHInfo = (AmsInstrumentHDTO) dtoParameter;
String sqlStr =
" SELECT "
+ " AITH.TRANS_NO,\n"
+ " AMS_PUB_PKG.GET_USER_NAME(AITH.RETURN_USER) RNAME,\n"
+ " AITH.RETURN_DATE,\n"
+ " AMS_PUB_PKG.GET_USER_NAME(AITH.CONFIRM_USER) QNAME,\n"
+ " AITH.CONFIRM_DATE,\n"
+ " AITH.TRANS_STATUS,\n"
+ " AITH.RETURN_USER,\n"
+ " AITH.TRANS_ID,\n"
+ " AITH.REPARI_FACTORY VENDOR_NAME,\n"
+ " AITH.ORGANIZATION_ID,\n"
+ " EFV.VALUE TRANS_STATUS_NAME\n"
+ " FROM "
+ " AMS_INSTRU_TRANS_H AITH,\n"
+ " ETS_FLEX_VALUES EFV,\n"
+ " ETS_FLEX_VALUE_SET EFVS\n"
+
// " SF_USER SU\n" +
" WHERE "
+ " EFV.FLEX_VALUE_SET_ID = EFVS.FLEX_VALUE_SET_ID\n"
+ " AND EFVS.CODE = 'ORDER_STATUS'\n"
+ " AND AITH.TRANS_TYPE = 'INS-VRE'\n"
+ " AND EFV.CODE = AITH.TRANS_STATUS\n"
+
// " AND SU.USER_ID = AITH.CONFIRM_USER\n" +
" AND ( "
+ SyBaseSQLUtil.isNull()
+ " OR AITH.TRANS_NO LIKE ?)\n"
+ " AND AITH.RETURN_DATE >= dbo.NVL(?, AITH.RETURN_DATE)\n"
+ " AND AITH.RETURN_DATE <= dbo.NVL(?, AITH.RETURN_DATE)\n";
// " AND AITH.TRANS_STATUS = dbo.NVL(?, AITH.TRANS_STATUS)";
sqlArgs.add(amsInstrumentHInfo.getTransNo());
sqlArgs.add(amsInstrumentHInfo.getTransNo());
sqlArgs.add(amsInstrumentHInfo.getFromDate());
sqlArgs.add(amsInstrumentHInfo.getToDate());
// sqlArgs.add(amsInstrumentHInfo.getTransStatus());
if ((!userAccount.isProvinceUser()) && (!userAccount.isSysAdmin())) {
sqlStr += "AND AITH.ORGANIZATION_ID = ?";
sqlArgs.add(userAccount.getOrganizationId());
}
sqlStr += "ORDER BY AITH.RETURN_DATE DESC";
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
} catch (CalendarException e) {
e.printLog();
throw new SQLModelException(e);
}
return sqlModel;
}
public SQLModel getPageQueryModel() throws SQLModelException {
SQLModel sqlModel = new SQLModel();
List sqlArgs = new ArrayList();
String sqlStr =
"SELECT EFA.ORGANIZATION_ID,\n"
+ " EFA.TAG_NUMBER,\n"
+ " EFA.ASSET_NUMBER,\n"
+ " EFA.ASSETS_DESCRIPTION,\n"
+ " EFA.MODEL_NUMBER,\n"
+ " EFA.LIFE_IN_YEARS,\n"
+ " EFA.DATE_PLACED_IN_SERVICE,\n"
+ " EFA.ASSIGNED_TO_NAME,\n"
+ " EFA.ASSIGNED_TO_NUMBER,\n"
+ " AMS_PUB_PKG.GET_ORGNIZATION_NAME(EFA.ORGANIZATION_ID) ORGANIZATION_NAME,\n"
+ " EFA.FA_CATEGORY_CODE\n"
+ " FROM ETS_FA_ASSETS EFA\n"
+ " WHERE (EFA.IS_RETIREMENTS = 0 OR EFA.IS_RETIREMENTS = 2)\n"
+ " AND NOT EXISTS\n"
+ " (SELECT 1 FROM ETS_ITEM_MATCH EIM WHERE EIM.ASSET_ID = EFA.ASSET_ID)\n"
+ " AND NOT EXISTS (SELECT 1\n"
+ " FROM ETS_ITEM_MATCH_ASSIST_MIS EIMAM\n"
+ " WHERE EIMAM.ASSET_ID = EFA.ASSET_ID)\n"
+ " AND EFA.ORGANIZATION_ID = ISNULL(?, EFA.ORGANIZATION_ID)\n"
+ " AND EFA.ASSET_NUMBER LIKE dbo.NVL(?, EFA.ASSET_NUMBER)"
+ " AND EFA.TAG_NUMBER LIKE dbo.NVL(?, EFA.TAG_NUMBER)"
+ " AND EFA.ASSETS_DESCRIPTION LIKE dbo.NVL(?, EFA.ASSETS_DESCRIPTION)"
+ " AND ( "
+ SyBaseSQLUtil.isNull()
+ " OR EFA.MODEL_NUMBER LIKE ?)"
+ " AND EFA.FA_CATEGORY_CODE LIKE dbo.NVL(?, EFA.FA_CATEGORY_CODE)";
sqlArgs.add(dto.getOrganizationId());
sqlArgs.add(dto.getAssetNumber());
sqlArgs.add(dto.getTagNumber());
sqlArgs.add(dto.getAssetsDescription());
sqlArgs.add(dto.getModelNumber());
sqlArgs.add(dto.getModelNumber());
sqlArgs.add(dto.getFaCategoryCode());
try {
if (dto.getEndDate().toString().equals("")) {
sqlStr +=
" AND GETDATE() - EFA.DATE_PLACED_IN_SERVICE >=\n" + " EFA.LIFE_IN_YEARS * 365";
} else {
sqlStr +=
" AND TO_DATE(?,'YYYY-MM-DD HH24:MI:SS') - EFA.DATE_PLACED_IN_SERVICE >=\n"
+ " EFA.LIFE_IN_YEARS * 365";
sqlArgs.add(dto.getSQLEndDate().toString());
}
} catch (CalendarException e) {
throw new SQLModelException(e);
}
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
return sqlModel;
}
public SQLModel getPageQueryModel() throws SQLModelException {
SQLModel sqlModel = new SQLModel();
List sqlArgs = new ArrayList();
try {
AmsSpareCategoryDTO amsSpareCategory = (AmsSpareCategoryDTO) dtoParameter;
String sqlStr =
"SELECT AMSC.BARCODE,\n"
+ " AMSC.ITEM_CODE,\n"
+ " AMSC.CREATION_DATE,\n"
+ " AMSC.CREATED_BY,\n"
+ " AMSC.LAST_UPDATE_DATE,\n"
+ " AMSC.LAST_UPDATE_BY,\n"
+ " AMSC.SPARE_USAGE,\n"
+ " AMSC.ORGANIZATION_ID,\n"
+ " AMSC.ITEM_NAME,\n"
+ " AMSC.ITEM_SPEC,\n"
+ " AMSC.ITEM_CATEGORY,\n"
+ " AMSC.VENDOR_ID,\n"
+ " ASV.VENDOR_NAME,\n"
+ " AMSC.ITEM_UNIT,\n"
+ " AMSC.REMARK,\n"
+ " CASE AMSC.ENABLED WHEN 'Y' THEN 'ÊÇ' ELSE '·ñ' END ENABLED,\n"
+ " SU.USERNAME\n"
+ " FROM AMS_SPARE_CATEGORY AMSC,\n"
+ " AMS_SPARE_VENDORS ASV,\n"
+ " SF_USER SU\n"
+ " WHERE AMSC.VENDOR_ID = ASV.VENDOR_ID\n"
+ " AND AMSC.CREATED_BY *= SU.USER_ID\n"
+ " AND ("
+ SyBaseSQLUtil.isNull()
+ " OR AMSC.ITEM_NAME LIKE ?)\n"
+ " AND ("
+ SyBaseSQLUtil.isNull()
+ " OR AMSC.ITEM_SPEC LIKE ?)\n"
+ " AND ("
+ SyBaseSQLUtil.isNull()
+ " OR AMSC.ITEM_CATEGORY LIKE ?)\n"
+ " AND ("
+ SyBaseSQLUtil.isNull()
+ " OR ASV.VENDOR_ID LIKE ?)\n"
+ " AND ("
+ SyBaseSQLUtil.isNull()
+ " OR AMSC.SPARE_USAGE LIKE ?)\n"
+ " AND ("
+ SyBaseSQLUtil.isNull()
+ " OR AMSC.BARCODE LIKE ?)\n"
+ " AND ("
+ SyBaseSQLUtil.isNull()
+ " OR SU.USERNAME LIKE ?)\n"
+ " AND ("
+ SyBaseSQLUtil.isNull()
+ " OR AMSC.CREATION_DATE >= ?)\n"
+ " AND ("
+ SyBaseSQLUtil.isNull()
+ " OR AMSC.CREATION_DATE <= ?)";
sqlArgs.add(amsSpareCategory.getItemName());
sqlArgs.add(amsSpareCategory.getItemName());
sqlArgs.add(amsSpareCategory.getItemSpec());
sqlArgs.add(amsSpareCategory.getItemSpec());
sqlArgs.add(amsSpareCategory.getItemCategory());
sqlArgs.add(amsSpareCategory.getItemCategory());
sqlArgs.add(amsSpareCategory.getVendorId());
sqlArgs.add(amsSpareCategory.getVendorId());
sqlArgs.add(amsSpareCategory.getSpareUsage());
sqlArgs.add(amsSpareCategory.getSpareUsage());
sqlArgs.add(amsSpareCategory.getBarcode());
sqlArgs.add(amsSpareCategory.getBarcode());
sqlArgs.add(amsSpareCategory.getCreatedByName());
sqlArgs.add(amsSpareCategory.getCreatedByName());
sqlArgs.add(amsSpareCategory.getStartDate());
sqlArgs.add(amsSpareCategory.getStartDate());
sqlArgs.add(amsSpareCategory.getSQLEndDate());
sqlArgs.add(amsSpareCategory.getSQLEndDate());
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
} catch (CalendarException ex) {
ex.printLog();
throw new SQLModelException(ex);
}
return sqlModel;
}
public SQLModel getMuxDataModel() throws SQLModelException {
SQLModel sqlModel = new SQLModel();
try {
List sqlArgs = new ArrayList();
AmsSpareCategoryDTO amsSpareCategory = (AmsSpareCategoryDTO) dtoParameter;
String sqlStr =
"SELECT"
+ " BARCODE,"
+ " ITEM_CODE,"
+ " CREATION_DATE,"
+ " CREATED_BY,"
+ " LAST_UPDATE_DATE,"
+ " LAST_UPDATE_BY,"
+ " SPARE_USAGE,"
+ " ORGANIZATION_ID,"
+ " ITEM_NAME,"
+ " ITEM_SPEC,"
+ " ITEM_CATEGORY,"
+ " VENDOR_ID,"
+ " ITEM_UNIT,"
+ " REMARK,"
+ " ENABLED"
+ " FROM"
+ " AMS_SPARE_CATEGORY"
+ " WHERE"
+ " ("
+ SyBaseSQLUtil.isNull()
+ " OR BARCODE LIKE ?)"
+ " AND ("
+ SyBaseSQLUtil.isNull()
+ " OR ITEM_CODE LIKE ?)"
+ " AND ("
+ SyBaseSQLUtil.isNull()
+ " OR CREATION_DATE LIKE ?)"
+ " AND (? = -1 OR CREATED_BY = ?)"
+ " AND ("
+ SyBaseSQLUtil.isNull()
+ " OR LAST_UPDATE_DATE LIKE ?)"
+ " AND (? = -1 OR LAST_UPDATE_BY = ?)"
+ " AND ("
+ SyBaseSQLUtil.isNull()
+ " OR SPARE_USAGE LIKE ?)"
+ " AND (? = -1 OR ORGANIZATION_ID = ?)"
+ " AND ("
+ SyBaseSQLUtil.isNull()
+ " OR ITEM_NAME LIKE ?)"
+ " AND ("
+ SyBaseSQLUtil.isNull()
+ " OR ITEM_SPEC LIKE ?)"
+ " AND ("
+ SyBaseSQLUtil.isNull()
+ " OR ITEM_CATEGORY LIKE ?)"
+ " AND ("
+ SyBaseSQLUtil.isNull()
+ " OR VENDOR_ID = ?)"
+ " AND ("
+ SyBaseSQLUtil.isNull()
+ " OR ITEM_UNIT LIKE ?)"
+ " AND ("
+ SyBaseSQLUtil.isNull()
+ " OR REMARK LIKE ?)"
+ " AND ("
+ SyBaseSQLUtil.isNull()
+ " OR ENABLED LIKE ?)";
sqlArgs.add(amsSpareCategory.getBarcode());
sqlArgs.add(amsSpareCategory.getBarcode());
sqlArgs.add(amsSpareCategory.getItemCode());
sqlArgs.add(amsSpareCategory.getItemCode());
sqlArgs.add(amsSpareCategory.getCreationDate());
sqlArgs.add(amsSpareCategory.getCreationDate());
sqlArgs.add(amsSpareCategory.getCreatedBy());
sqlArgs.add(amsSpareCategory.getCreatedBy());
sqlArgs.add(amsSpareCategory.getLastUpdateDate());
sqlArgs.add(amsSpareCategory.getLastUpdateDate());
sqlArgs.add(amsSpareCategory.getLastUpdateBy());
sqlArgs.add(amsSpareCategory.getLastUpdateBy());
sqlArgs.add(amsSpareCategory.getSpareUsage());
sqlArgs.add(amsSpareCategory.getSpareUsage());
sqlArgs.add(amsSpareCategory.getOrganizationId());
sqlArgs.add(amsSpareCategory.getOrganizationId());
sqlArgs.add(amsSpareCategory.getItemName());
sqlArgs.add(amsSpareCategory.getItemName());
sqlArgs.add(amsSpareCategory.getItemSpec());
sqlArgs.add(amsSpareCategory.getItemSpec());
sqlArgs.add(amsSpareCategory.getItemCategory());
sqlArgs.add(amsSpareCategory.getItemCategory());
sqlArgs.add(amsSpareCategory.getVendorId());
sqlArgs.add(amsSpareCategory.getVendorId());
sqlArgs.add(amsSpareCategory.getItemUnit());
sqlArgs.add(amsSpareCategory.getItemUnit());
sqlArgs.add(amsSpareCategory.getRemark());
sqlArgs.add(amsSpareCategory.getRemark());
sqlArgs.add(amsSpareCategory.getEnabled());
sqlArgs.add(amsSpareCategory.getEnabled());
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
} catch (CalendarException ex) {
ex.printLog();
throw new SQLModelException(ex);
}
return sqlModel;
}
/**
* 功能： 获取闲置资产投资方向SQL
*
* @return SQLModel
* @throws com.sino.base.exception.SQLModelException
* @author 李轶
*/
public SQLModel getPageQueryModel() throws SQLModelException {
SQLModel sqlModel = new SQLModel();
List sqlArgs = new ArrayList();
DeptAssetsReportDTO dto = (DeptAssetsReportDTO) dtoParameter;
String sqlStr =
"SELECT TOTAL.COMPANY,\n"
+ " DECODE(TOTAL.PROJECT_NAME, '--', '投资项目为空', TOTAL.PROJECT_NAME) PROJECT_NAME,\n"
+ " TOTAL.SUM_COUNT,\n"
+ " TOTAL.SUM_COST, \n"
+ " (DECODE(TRUNC(100 * TOTAL.SUM_COST / SUM_COST.TOTAL),\n"
+ " 0,\n"
+ " STR_REPLACE(ROUND(100 * TOTAL.SUM_COST / SUM_COST.TOTAL, 3),\n"
+ " '.',\n"
+ " '0.'),\n"
+ " TO_CHAR(ROUND(100 * TOTAL.SUM_COST / SUM_COST.TOTAL, 3))) || '%') ASSETS_RATE,\n"
+ " \n"
+ " (DECODE(SUM_LAST_YEAR_COST.SUM_COST, NULL , '0', DECODE(TRUNC(100 * TOTAL.SUM_COST / SUM_LAST_YEAR_COST.SUM_COST - 100),\n"
+ " 0,\n"
+ " STR_REPLACE(ROUND(100 * TOTAL.SUM_COST / SUM_LAST_YEAR_COST.SUM_COST - 100, 3),\n"
+ " '.',\n"
+ " '0.'),\n"
+ " TO_CHAR(ROUND(100 * TOTAL.SUM_COST / SUM_LAST_YEAR_COST.SUM_COST - 100, 3)))) || '%') LAST_YEAR_RATE,\n"
+ " \n"
+ " (DECODE(SUM_LAST_FOUR_YEAR_COST.SUM_COST, NULL , '0', DECODE(TRUNC(100 * SUM_LAST_THREE_YEAR_COST.SUM_COST / SUM_LAST_FOUR_YEAR_COST.SUM_COST - 100),\n"
+ " 0,\n"
+ " STR_REPLACE(ROUND(100 * SUM_LAST_THREE_YEAR_COST.SUM_COST / SUM_LAST_FOUR_YEAR_COST.SUM_COST - 100, 3),\n"
+ " '.',\n"
+ " '0.'),\n"
+ " TO_CHAR(ROUND(100 * SUM_LAST_THREE_YEAR_COST.SUM_COST / SUM_LAST_FOUR_YEAR_COST.SUM_COST - 100, 3)))) || '%') THREE_YEER_THREE_RATE,\n"
+ " \n"
+ " (DECODE(SUM_LAST_THREE_YEAR_COST.SUM_COST, NULL , '0', DECODE(TRUNC(100 * SUM_LAST_TWO_YEAR_COST.SUM_COST / SUM_LAST_THREE_YEAR_COST.SUM_COST - 100),\n"
+ " 0,\n"
+ " STR_REPLACE(ROUND(100 * SUM_LAST_TWO_YEAR_COST.SUM_COST / SUM_LAST_THREE_YEAR_COST.SUM_COST - 100, 3),\n"
+ " '.',\n"
+ " '0.'),\n"
+ " TO_CHAR(ROUND(100 * SUM_LAST_TWO_YEAR_COST.SUM_COST / SUM_LAST_THREE_YEAR_COST.SUM_COST - 100, 3)))) || '%') THREE_YEER_TWO_RATE,\n"
+ " \n"
+ " (DECODE(SUM_LAST_TWO_YEAR_COST.SUM_COST, NULL , '0', DECODE(TRUNC(100 * SUM_LAST_ONE_YEAR_COST.SUM_COST / SUM_LAST_TWO_YEAR_COST.SUM_COST - 100),\n"
+ " 0,\n"
+ " STR_REPLACE(ROUND(100 * SUM_LAST_ONE_YEAR_COST.SUM_COST / SUM_LAST_TWO_YEAR_COST.SUM_COST - 100, 3),\n"
+ " '.',\n"
+ " '0.'),\n"
+ " TO_CHAR(ROUND(100 * SUM_LAST_ONE_YEAR_COST.SUM_COST / SUM_LAST_TWO_YEAR_COST.SUM_COST - 100, 3)))) || '%') THREE_YEER_ONE_RATE\n"
+ " \n"
+ "FROM (SELECT SUM(EFAHR.COST) TOTAL\n"
+ " FROM ETS_FA_ASSETS_HIS_REP EFAHR,\n"
+ " ETS_ITEM_INFO EII,\n"
+ " ETS_ITEM_MATCH EIM\n"
+ " WHERE EII.ITEM_STATUS = 'FREE'"
+ " AND EII.SYSTEMID = EIM.SYSTEMID\n"
+ " AND EFAHR.ASSET_ID = EIM.ASSET_ID\n"
+ " AND EFAHR.PERIOD_NAME = ? \n"
+ " AND ( "
+ SyBaseSQLUtil.isNull()
+ " OR EFAHR.ORGANIZATION_ID = ?)) SUM_COST,\n"
+ " (SELECT EOCM.COMPANY,\n"
+ " (AC.INVEST_CATEGORY1 || '-' || AC.INVEST_CATEGORY2 || '-' ||\n"
+ " AC.INVEST_CAT_NAME) PROJECT_NAME,\n"
+ " COUNT(EII.ITEM_QTY) SUM_COUNT,\n"
+ " SUM(EFAHR.COST) SUM_COST,\n"
+ " EOCM.ORGANIZATION_ID,\n"
+ " AC.INVEST_CAT_CODE\n"
+ " FROM ETS_FA_ASSETS_HIS_REP EFAHR,\n"
+ " ETS_ITEM_INFO EII,\n"
+ " ETS_ITEM_MATCH EIM,\n"
+ " AMS_CEX AC,\n"
+ " ETS_OU_CITY_MAP EOCM,\n"
+ " ETS_ITEM_INFO_ATTR_CHG EIIAC\n"
+ " WHERE EII.ITEM_STATUS = 'FREE'\n"
+ " AND EII.SYSTEMID = EIM.SYSTEMID\n"
+ " AND EFAHR.ASSET_ID = EIM.ASSET_ID\n"
+ " AND EFAHR.CEX_ID *= AC.AMS_CEX_ID\n"
+ " AND EOCM.ORGANIZATION_ID = EFAHR.ORGANIZATION_ID\n"
+ " AND EFAHR.TAG_NUMBER = EIIAC.BAR_CODE\n"
+ " AND EIIAC.PERIOD_NAME = EFAHR.PERIOD_NAME"
+ " AND ( "
+ SyBaseSQLUtil.isNull()
+ " OR EOCM.ORGANIZATION_ID = ?)\n"
+ " AND EFAHR.PERIOD_NAME = ?\n"
+ " GROUP BY AC.INVEST_CATEGORY1 || '-' || AC.INVEST_CATEGORY2 || '-' ||\n"
+ " AC.INVEST_CAT_NAME,\n"
+ " EOCM.COMPANY,\n"
+ " EOCM.ORGANIZATION_ID,\n"
+ " AC.INVEST_CAT_CODE) TOTAL\n"
+ " LEFT JOIN \n"
+ " (SELECT SUM(EFAHR.COST) SUM_COST,\n"
+ " EOCM.ORGANIZATION_ID,\n"
+ " AC.INVEST_CAT_CODE\n"
+ " FROM ETS_FA_ASSETS_HIS_REP EFAHR,\n"
+ " ETS_ITEM_INFO EII,\n"
+ " ETS_ITEM_MATCH EIM,\n"
+ " AMS_CEX AC,\n"
+ " ETS_OU_CITY_MAP EOCM,\n"
+ " ETS_ITEM_INFO_ATTR_CHG EIIAC\n"
+ " WHERE EII.ITEM_STATUS = 'FREE'\n"
+ " AND EII.SYSTEMID = EIM.SYSTEMID\n"
+ " AND EFAHR.ASSET_ID = EIM.ASSET_ID\n"
+ " AND EFAHR.CEX_ID *= AC.AMS_CEX_ID\n"
+ " AND EFAHR.TAG_NUMBER = EIIAC.BAR_CODE\n"
+ " AND EIIAC.PERIOD_NAME = EFAHR.PERIOD_NAME"
+ " AND EOCM.ORGANIZATION_ID = EFAHR.ORGANIZATION_ID\n"
+ " AND ( "
+ SyBaseSQLUtil.isNull()
+ " OR EOCM.ORGANIZATION_ID = ?)\n"
+ " AND EFAHR.PERIOD_NAME = ? \n"
+ " GROUP BY EOCM.ORGANIZATION_ID,\n"
+ " AC.INVEST_CAT_CODE) SUM_LAST_YEAR_COST \n"
+ " ON TOTAL.ORGANIZATION_ID = SUM_LAST_YEAR_COST.ORGANIZATION_ID\n"
+ " AND TOTAL.INVEST_CAT_CODE = SUM_LAST_YEAR_COST.INVEST_CAT_CODE\n"
+ " LEFT JOIN \n"
+ " (SELECT SUM(EFAHR.COST) SUM_COST,\n"
+ " EOCM.ORGANIZATION_ID,\n"
+ " AC.INVEST_CAT_CODE\n"
+ " FROM ETS_FA_ASSETS_HIS_REP EFAHR,\n"
+ " ETS_ITEM_INFO EII,\n"
+ " ETS_ITEM_MATCH EIM,\n"
+ " AMS_CEX AC,\n"
+ " ETS_OU_CITY_MAP EOCM,\n"
+ " ETS_ITEM_INFO_ATTR_CHG EIIAC\n"
+ " WHERE EII.SYSTEMID = EIM.SYSTEMID\n"
+ " AND EFAHR.TAG_NUMBER = EIIAC.BAR_CODE\n"
+ " AND EIIAC.PERIOD_NAME = EFAHR.PERIOD_NAME"
+ " AND EFAHR.ASSET_ID = EIM.ASSET_ID\n"
+ " AND EFAHR.CEX_ID *= AC.AMS_CEX_ID\n"
+ " AND EOCM.ORGANIZATION_ID = EFAHR.ORGANIZATION_ID\n"
+ " AND ( "
+ SyBaseSQLUtil.isNull()
+ " OR EOCM.ORGANIZATION_ID = ?)\n"
+ " AND EFAHR.PERIOD_NAME = ? \n"
+ " GROUP BY EOCM.ORGANIZATION_ID,\n"
+ " AC.INVEST_CAT_CODE) SUM_LAST_FOUR_YEAR_COST \n"
+ " ON SUM_LAST_YEAR_COST.ORGANIZATION_ID = SUM_LAST_FOUR_YEAR_COST.ORGANIZATION_ID\n"
+ " AND SUM_LAST_YEAR_COST.INVEST_CAT_CODE = SUM_LAST_FOUR_YEAR_COST.INVEST_CAT_CODE\n"
+ " LEFT JOIN \n"
+ " (SELECT SUM(EFAHR.COST) SUM_COST,\n"
+ " EOCM.ORGANIZATION_ID,\n"
+ " AC.INVEST_CAT_CODE\n"
+ " FROM ETS_FA_ASSETS_HIS_REP EFAHR,\n"
+ " ETS_ITEM_INFO EII,\n"
+ " ETS_ITEM_MATCH EIM,\n"
+ " AMS_CEX AC,\n"
+ " ETS_OU_CITY_MAP EOCM,\n"
+ " ETS_ITEM_INFO_ATTR_CHG EIIAC\n"
+ " WHERE EII.ITEM_STATUS = 'FREE'\n"
+ " AND EII.SYSTEMID = EIM.SYSTEMID\n"
+ " AND EFAHR.TAG_NUMBER = EIIAC.BAR_CODE\n"
+ " AND EIIAC.PERIOD_NAME = EFAHR.PERIOD_NAME"
+ " AND EFAHR.ASSET_ID = EIM.ASSET_ID\n"
+ " AND EFAHR.CEX_ID *= AC.AMS_CEX_ID\n"
+ " AND EOCM.ORGANIZATION_ID = EFAHR.ORGANIZATION_ID\n"
+ " AND ( "
+ SyBaseSQLUtil.isNull()
+ " OR EOCM.ORGANIZATION_ID = ?)\n"
+ " AND EFAHR.PERIOD_NAME = ?\n"
+ " GROUP BY EOCM.ORGANIZATION_ID,\n"
+ " AC.INVEST_CAT_CODE) SUM_LAST_THREE_YEAR_COST \n"
+ " ON SUM_LAST_FOUR_YEAR_COST.ORGANIZATION_ID = SUM_LAST_THREE_YEAR_COST.ORGANIZATION_ID\n"
+ " AND SUM_LAST_FOUR_YEAR_COST.INVEST_CAT_CODE = SUM_LAST_THREE_YEAR_COST.INVEST_CAT_CODE\n"
+ " LEFT JOIN \n"
+ " (SELECT SUM(EFAHR.COST) SUM_COST,\n"
+ " EOCM.ORGANIZATION_ID,\n"
+ " AC.INVEST_CAT_CODE\n"
+ " FROM ETS_FA_ASSETS_HIS_REP EFAHR,\n"
+ " ETS_ITEM_INFO EII,\n"
+ " ETS_ITEM_MATCH EIM,\n"
+ " AMS_CEX AC,\n"
+ " ETS_OU_CITY_MAP EOCM,\n"
+ " ETS_ITEM_INFO_ATTR_CHG EIIAC\n"
+ " WHERE EII.ITEM_STATUS = 'FREE'\n"
+ " AND EII.SYSTEMID = EIM.SYSTEMID\n"
+ " AND EFAHR.TAG_NUMBER = EIIAC.BAR_CODE\n"
+ " AND EIIAC.PERIOD_NAME = EFAHR.PERIOD_NAME"
+ " AND EFAHR.CEX_ID *= AC.AMS_CEX_ID\n"
+ " AND EOCM.ORGANIZATION_ID = EFAHR.ORGANIZATION_ID\n"
+ " AND ( "
+ SyBaseSQLUtil.isNull()
+ " OR EOCM.ORGANIZATION_ID = ?)\n"
+ " AND EFAHR.PERIOD_NAME = ?\n"
+ " GROUP BY EOCM.ORGANIZATION_ID,\n"
+ " AC.INVEST_CAT_CODE) SUM_LAST_TWO_YEAR_COST \n"
+ " ON SUM_LAST_THREE_YEAR_COST.ORGANIZATION_ID = SUM_LAST_TWO_YEAR_COST.ORGANIZATION_ID\n"
+ " AND SUM_LAST_THREE_YEAR_COST.INVEST_CAT_CODE = SUM_LAST_TWO_YEAR_COST.INVEST_CAT_CODE\n"
+ " LEFT JOIN \n"
+ " (SELECT SUM(EFAHR.COST) SUM_COST,\n"
+ " EOCM.ORGANIZATION_ID,\n"
+ " AC.INVEST_CAT_CODE\n"
+ " FROM ETS_FA_ASSETS_HIS_REP EFAHR,\n"
+ " ETS_ITEM_INFO EII,\n"
+ " ETS_ITEM_MATCH EIM,\n"
+ " AMS_CEX AC,\n"
+ " ETS_OU_CITY_MAP EOCM,\n"
+ " ETS_ITEM_INFO_ATTR_CHG EIIAC\n"
+ " WHERE EII.ITEM_STATUS = 'FREE'\n"
+ " AND EII.SYSTEMID = EIM.SYSTEMID\n"
+ " AND EFAHR.ASSET_ID = EIM.ASSET_ID\n"
+ " AND EFAHR.TAG_NUMBER = EIIAC.BAR_CODE\n"
+ " AND EIIAC.PERIOD_NAME = EFAHR.PERIOD_NAME"
+ " AND EFAHR.CEX_ID *= AC.AMS_CEX_ID\n"
+ " AND EOCM.ORGANIZATION_ID = EFAHR.ORGANIZATION_ID\n"
+ " AND ( "
+ SyBaseSQLUtil.isNull()
+ " OR EOCM.ORGANIZATION_ID = ?)\n"
+ " AND EFAHR.PERIOD_NAME = ?\n"
+ " GROUP BY EOCM.ORGANIZATION_ID,\n"
+ " AC.INVEST_CAT_CODE) SUM_LAST_ONE_YEAR_COST \n"
+ " ON SUM_LAST_TWO_YEAR_COST.ORGANIZATION_ID = SUM_LAST_ONE_YEAR_COST.ORGANIZATION_ID\n"
+ " AND SUM_LAST_TWO_YEAR_COST.INVEST_CAT_CODE = SUM_LAST_ONE_YEAR_COST.INVEST_CAT_CODE\n"
+ "ORDER BY TOTAL.ORGANIZATION_ID,\n"
+ " TOTAL.INVEST_CAT_CODE";
sqlArgs.add(dto.getPeriodNameByHisRep());
sqlArgs.add(dto.getOrganizationId());
sqlArgs.add(dto.getOrganizationId());
sqlArgs.add(dto.getOrganizationId());
sqlArgs.add(dto.getOrganizationId());
sqlArgs.add(dto.getPeriodNameByHisRep());
sqlArgs.add(dto.getOrganizationId());
sqlArgs.add(dto.getOrganizationId());
sqlArgs.add(dto.getLastYearPeriodNameByHisRep());
sqlArgs.add(dto.getOrganizationId());
sqlArgs.add(dto.getOrganizationId());
sqlArgs.add(dto.getLastFourYearPeriodNameByHisRep());
sqlArgs.add(dto.getOrganizationId());
sqlArgs.add(dto.getOrganizationId());
sqlArgs.add(dto.getLastThreeYearPeriodNameByHisRep());
sqlArgs.add(dto.getOrganizationId());
sqlArgs.add(dto.getOrganizationId());
sqlArgs.add(dto.getLastTwoYearPeriodNameByHisRep());
sqlArgs.add(dto.getOrganizationId());
sqlArgs.add(dto.getOrganizationId());
sqlArgs.add(dto.getLastOneYearPeriodNameByHisRep());
sqlModel.setArgs(sqlArgs);
sqlModel.setSqlStr(sqlStr);
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;
}