/**
* 功能：框架自动生成应急保障设备名称字典表 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;
}