public SQLModel getPrimaryKeyDataModel() {
SQLModel sqlModel = new SQLModel();
List sqlArgs = new ArrayList();
AmsSpareCategoryDTO amsSpareCategory = (AmsSpareCategoryDTO) dtoParameter;
String sqlStr =
"SELECT "
+ " AMSC.BARCODE,"
+ " AMSC.ITEM_NAME,"
+ " AMSC.ITEM_SPEC,"
+ " AMSC.ITEM_CATEGORY,"
+ " AMSC.SPARE_USAGE,"
+ " AMSC.VENDOR_ID,"
+ " AMSC.CREATION_DATE,"
+ " AMSC.CREATED_BY,"
+ " AMSC.LAST_UPDATE_DATE,"
+ " AMSC.LAST_UPDATE_BY,"
+ " AMSC.ITEM_UNIT,"
+ " AMSC.REMARK,"
+ " AMSC.ENABLED"
+ " FROM AMS_SPARE_CATEGORY AMSC"
+ " WHERE"
+ " AMSC.BARCODE = ?";
sqlArgs.add(amsSpareCategory.getBarcode());
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
return sqlModel;
}
public SQLModel getDataUpdateModel() throws SQLModelException {
SQLModel sqlModel = new SQLModel();
List sqlArgs = new ArrayList();
AmsSpareCategoryDTO amsSpareCategory = (AmsSpareCategoryDTO) dtoParameter;
String sqlStr =
"UPDATE AMS_SPARE_CATEGORY"
+ " SET"
+ " ITEM_NAME = ?,"
+ " ITEM_SPEC = ?,"
+ " ITEM_CATEGORY = ?,"
+ " SPARE_USAGE = ?,"
+ " VENDOR_ID = ?,"
+ " ITEM_UNIT = ?,"
+ " REMARK = ?,"
+ " LAST_UPDATE_DATE = GETDATE(),"
+ " LAST_UPDATE_BY = ?"
+ " WHERE "
+ " BARCODE = ?";
sqlArgs.add(amsSpareCategory.getItemName());
sqlArgs.add(amsSpareCategory.getItemSpec());
sqlArgs.add(amsSpareCategory.getItemCategory());
sqlArgs.add(amsSpareCategory.getSpareUsage());
sqlArgs.add(amsSpareCategory.getVendorId());
sqlArgs.add(amsSpareCategory.getItemUnit());
sqlArgs.add(amsSpareCategory.getRemark());
sqlArgs.add(sfUser.getUserId());
sqlArgs.add(amsSpareCategory.getBarcode());
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
return sqlModel;
}
/**
* 查询差异结果的数据
*
* @return
*/
@SuppressWarnings("unchecked")
@Override
public SQLModel getPageQueryModel() throws SQLModelException {
SQLModel sqlModel = new SQLModel();
List sqlArgs = new ArrayList();
DealTdProjectDiversityCheckDTO srvAssetCategory = (DealTdProjectDiversityCheckDTO) dtoParameter;
String sqlStr =
"SELECT "
+ " EDPVE.BOOK_TYPE_CODE,"
+ " EDPVE.MIS_PROJECT_ID,"
+ " EPPA.NAME PROJECT_NAME,"
+ " EDPVE.TAG_NUMBER,"
+ " EDPVE.DIFF_TYPE_CODE,"
+ " EDPVE.DIFF_TYPE,"
+ " EDPVE.PA_VALUE,"
+ " EDPVE.EAM_VALUE,"
+ " EPPA.SEGMENT1"
+ " FROM EAM_DIFF_PA_VS_EII_TD EDPVE,ETS_PA_PROJECTS_ALL EPPA,ETS_OU_CITY_MAP EOCM"
+ " WHERE"
+ " EDPVE.MIS_PROJECT_ID IN("
+ srvAssetCategory.getMisProjectId()
+ ")"
+ " AND EDPVE.BOOK_TYPE_CODE=EOCM.BOOK_TYPE_CODE" // 帐套限制
+ " AND EOCM.ORGANIZATION_ID=EPPA.ORGANIZATION_ID" // 帐套限制
+ " AND EDPVE.DIFF_TYPE_CODE IN ("
+ srvAssetCategory.getDiffTypeCode()
+ ")"
+ " AND EDPVE.MIS_PROJECT_ID=EPPA.MIS_PROJECT_ID"
+ " ORDER BY 1,2,3,4";
// sqlArgs.add(srvAssetCategory.getMisProjectId());
// sqlArgs.add(srvAssetCategory.getDiffTypeCode());
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
return sqlModel;
}
public SQLModel getDataCreateModel() throws SQLModelException {
SQLModel sqlModel = new SQLModel();
List sqlArgs = new ArrayList();
AmsSpareCategoryDTO amsSpareCategory = (AmsSpareCategoryDTO) dtoParameter;
String sqlStr =
"INSERT INTO "
+ " AMS_SPARE_CATEGORY("
+ " BARCODE,"
+ " ITEM_NAME,"
+ " ITEM_SPEC,"
+ " ITEM_CATEGORY,"
+ " SPARE_USAGE,"
+ " VENDOR_ID,"
+ " ITEM_UNIT,"
+ " REMARK,"
+ " CREATION_DATE,"
+ " CREATED_BY"
+ ") VALUES ("
+ " ?, ?, ?, ?, ?, ?, ?, ?, GETDATE(), ?)";
sqlArgs.add(amsSpareCategory.getBarcode());
sqlArgs.add(amsSpareCategory.getItemName());
sqlArgs.add(amsSpareCategory.getItemSpec());
sqlArgs.add(amsSpareCategory.getItemCategory());
sqlArgs.add(amsSpareCategory.getSpareUsage());
sqlArgs.add(amsSpareCategory.getVendorId());
sqlArgs.add(amsSpareCategory.getItemUnit());
sqlArgs.add(amsSpareCategory.getRemark());
sqlArgs.add(sfUser.getUserId());
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
return sqlModel;
}
/**
* 功能：执行新增数据操作。
*
* @return SQLModel 返回数据插入用SQLModel
*/
public SQLModel getDataCreateModel() {
SQLModel sqlModel = new SQLModel();
// try {
List sqlArgs = new ArrayList();
AssetsAddDTO dto = (AssetsAddDTO) dtoParameter;
String sqlStr =
"INSERT INTO "
+ " ETS_ASSETS_ADD_H("
+ " HEAD_ID,"
+ " BILL_NO,"
+ " STATUS,"
+ " CREATE_USER,"
+ " CREATED_DATE,"
+ " SPEC_DEPT,"
+ " REMARK"
+ ") "
+ " VALUES (?, ?, ?, ?, GETDATE(), ?, ?)";
sqlArgs.add(dto.getHeadId());
sqlArgs.add(dto.getBillNo());
sqlArgs.add(StrUtil.strToInt(dto.getStatus()));
sqlArgs.add(userAccount.getUserId());
sqlArgs.add(dto.getSpecialityDept());
sqlArgs.add(dto.getRemark());
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
return sqlModel;
}
/**
* 功能：框架自动生成备件事务头表(EAM) ETS_ASSETS_ADD_H数据详细信息查询SQLModel，请根据实际需要修改。
*
* @return SQLModel 返回数据详细信息查询用SQLModel
*/
public SQLModel getPrimaryKeyDataModel() {
SQLModel sqlModel = new SQLModel();
List sqlArgs = new ArrayList();
AssetsAddDTO dto = (AssetsAddDTO) dtoParameter;
String sqlStr =
"SELECT EAAH.HEAD_ID,\n"
+ " EAAH.BILL_NO,\n"
+ " CASE WHEN EAAH.STATUS = 0 THEN '未完成' ELSE '已完成' END STATUS,"
+ " EAAH.CREATE_USER CREATED_BY,\n "
+ " EAAH.CREATED_DATE CREATED_DATE,\n"
+ " EAAH.REMARK,\n"
+ " EAAH.SPEC_DEPT,\n"
+ " AMD.DEPT_NAME SPECIALITY_DEPT, \n"
+ " SU.USERNAME CREATE_USER\n"
+ " FROM ETS_ASSETS_ADD_H EAAH,\n"
+ " AMS_MIS_DEPT AMD, \n"
+ " SF_USER SU\n"
+ " WHERE EAAH.CREATE_USER = SU.USER_ID\n"
+ " AND EAAH.SPEC_DEPT *= AMD.DEPT_CODE \n"
+ " AND EAAH.HEAD_ID = ?";
sqlArgs.add(dto.getHeadId());
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;
}
/**
* 功能:仪器仪表的送修返还头文件的生成model
*
* @return
* @throws SQLModelException
*/
public SQLModel getDataCreateModel() throws SQLModelException {
SQLModel sqlModel = new SQLModel();
try {
List sqlArgs = new ArrayList();
AmsInstrumentHDTO amsInstrumentHInfo = (AmsInstrumentHDTO) dtoParameter;
String sqlStr =
"INSERT INTO AMS_INSTRU_TRANS_H\n"
+ " (TRANS_ID, TRANS_NO, TRANS_TYPE, TRANS_STATUS,CONFIRM_USER,CONFIRM_DATE, RETURN_USER, RETURN_DATE,REPARI_FACTORY,ORGANIZATION_ID)\n"
+ "VALUES\n"
+ " (?, ?, ?, ?, ?,GETDATE(), ?, ?,?,?)";
sqlArgs.add(amsInstrumentHInfo.getTransId());
sqlArgs.add(amsInstrumentHInfo.getTransNo());
sqlArgs.add(amsInstrumentHInfo.getTransType());
sqlArgs.add(amsInstrumentHInfo.getTransStatus());
sqlArgs.add(userAccount.getUserId());
// sqlArgs.add(amsInstrumentHInfo.getReturnUser());
sqlArgs.add(amsInstrumentHInfo.getReturnUser());
sqlArgs.add(amsInstrumentHInfo.getReturnDate());
sqlArgs.add(amsInstrumentHInfo.getVendorName()); // 取供应商编号
sqlArgs.add(userAccount.getOrganizationId());
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
} catch (CalendarException e) {
e.printLog();
throw new SQLModelException(e);
}
return sqlModel;
}
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;
}
/**
* 功能：构造获取当前用户某一盘点工单下的所有待盘点资产SQL
*
* @return SQLModel
*/
public SQLModel getOrderAssetsModel() {
SQLModel sqlModel = new SQLModel();
EamDhCheckHeaderDTO dto = (EamDhCheckHeaderDTO) dtoParameter;
List sqlArgs = new ArrayList();
String sqlStr =
"SELECT"
+ " EDCL.HEADER_ID,"
+ " EDCL.BARCODE,"
+ " EDCL.ITEM_CODE,"
+ " EDCL.ITEM_CATEGORY,"
+ " EDCL.ITEM_NAME,"
+ " EDCL.ITEM_SPEC,"
+ " EDCL.ITEM_CATEGORY2,"
+ " EDCL.VENDOR_BARCODE,"
+ " EDCL.PRICE,"
+ " EDCL.START_DATE,"
+ " EDCL.RESPONSIBILITY_USER,"
+ " EDCL.DEPT_CODE RESPONSIBILITY_DEPT,"
+ " EDCL.MAINTAIN_USER,"
+ " EDCL.VENDOR_ID,"
+ " EDCL.VENDOR_NAME"
+ " FROM"
+ " EAM_DH_CHECK_LINE EDCL"
+ " WHERE"
+ " EDCL.BARCODE = EDCL.BARCODE"
+ " AND EDCL.HEADER_ID = ?";
sqlArgs.add(dto.getHeaderId());
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
return sqlModel;
}
/**
* 功能：框架自动生成资产拓展信息表－ETS_ITEM_INFO_EX ETS_ITEM_INFO_EX页面翻页查询SQLModel，请根据实际需要修改。
*
* @return SQLModel 返回页面翻页查询SQLModel
* @throws SQLModelException 发生日历异常时转化为该异常抛出
*/
public SQLModel getPageQueryModel() throws SQLModelException {
SQLModel sqlModel = new SQLModel();
List sqlArgs = new ArrayList();
EtsItemInfoExSearchDTO dto = (EtsItemInfoExSearchDTO) dtoParameter;
String sqlStr =
"SELECT "
+ " EIIE.ITEM_INFO_EX_ID,"
+ " EII.BARCODE,"
+ " ESI.ITEM_NAME,"
+ " ESI.ITEM_SPEC,"
+ " EII.RESPONSIBILITY_DEPT,"
+ " EIIE.ATTRIBUTE1,"
+ " EIIE.ATTRIBUTE2,"
+ " EIIE.ATTRIBUTE3,"
+ " EIIE.ATTRIBUTE4,"
+ " EIIE.ATTRIBUTE5,"
+ " dbo.APP_GET_DEPT_NAME(EII.RESPONSIBILITY_DEPT) DEPT_NAME,"
+ " EII.RESPONSIBILITY_USER,"
+ " dbo.EAM_MAINTAIN_GET_EMPLOYEE_NAME(EII.RESPONSIBILITY_USER) EMPLOYEE_NAME,"
+ " EII.FINANCE_PROP,"
+ " dbo.APP_GET_FLEX_VALUE(EII.FINANCE_PROP,'FINANCE_PROP') FINANCE_PROP_NAME,"
+ " EFA.DATE_PLACED_IN_SERVICE"
+ " FROM"
+ " ETS_ITEM_INFO EII,"
+ " ETS_SYSTEM_ITEM ESI,"
+ " ETS_ITEM_INFO_EX EIIE,"
+ " ETS_ITEM_MATCH EIM,"
+ " ETS_FA_ASSETS EFA"
+ " WHERE"
+ " EII.ITEM_CODE = ESI.ITEM_CODE"
+ " AND EII.SYSTEMID = EIIE.SYSTEM_ID"
+ " AND EII.SYSTEMID = EIM.SYSTEMID"
+ " AND EIM.ASSET_ID = EFA.ASSET_ID"
+ " AND EIIE.ITEM_TYPE='IT'"
+ " AND EII.ORGANIZATION_ID=?"
+ " AND (LTRIM(?) IS NULL OR EII.BARCODE LIKE '%' || ? || '%')"
+ " AND (LTRIM(?) IS NULL OR ESI.ITEM_NAME LIKE '%' || ? || '%')"
+ " AND (LTRIM(?) IS NULL OR ESI.ITEM_SPEC LIKE '%' || ? || '%')"
+ " AND (LTRIM(?) IS NULL OR EII.RESPONSIBILITY_DEPT LIKE '%' || ? || '%')"
+ " AND (LTRIM(?) IS NULL OR EII.RESPONSIBILITY_USER LIKE '%' || ? || '%')"
+ " AND EII.FINANCE_PROP = dbo.NVL(LTRIM(?), EII.FINANCE_PROP)";
sqlArgs.add(userAccount.getOrganizationId());
sqlArgs.add(dto.getBarcode());
sqlArgs.add(dto.getBarcode());
sqlArgs.add(dto.getItemName());
sqlArgs.add(dto.getItemName());
sqlArgs.add(dto.getItemSpec());
sqlArgs.add(dto.getItemSpec());
sqlArgs.add(dto.getResponsibilityDept());
sqlArgs.add(dto.getResponsibilityDept());
sqlArgs.add(dto.getResponsibilityUser());
sqlArgs.add(dto.getResponsibilityUser());
sqlArgs.add(dto.getFinanceProp());
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
return sqlModel;
}
/**
* 功能：新增管理资产查询。
*
* @return SQLModel 返回页面翻页查询SQLModel
* @throws com.sino.base.exception.SQLModelException 发生日历异常时转化为该异常抛出
*/
public SQLModel getPageQueryModel() throws SQLModelException {
SQLModel sqlModel = new SQLModel();
try {
List sqlArgs = new ArrayList();
AssetsAddDTO dto = (AssetsAddDTO) dtoParameter;
// AssetsAddLDTO dtoL = (AssetsAddLDTO) dtoParameter;
String sqlStr =
"SELECT EAAH.HEAD_ID,\n"
+ " EAAH.BILL_NO,\n"
+ " CASE WHEN EAAH.STATUS=0 THEN '未完成' ELSE '已完成' END STATUS,"
+ " EAAH.CREATE_USER,\n"
+ " EAAH.CREATED_DATE,\n"
+ " EAAH.SPEC_DEPT,\n"
+ " EAAL.BARCODE,\n"
+ " EAAL.ITEM_CODE,\n"
+ " ESI.ITEM_NAME,\n"
+ " ESI.ITEM_SPEC,\n"
+ " SU.USERNAME\n"
+ "FROM ETS_ASSETS_ADD_H EAAH,\n"
+ " ETS_ASSETS_ADD_L EAAL,\n"
+ " ETS_SYSTEM_ITEM ESI,\n"
+ " SF_USER SU\n"
+ "WHERE EAAH.HEAD_ID = EAAL.HEAD_ID\n"
+ " AND EAAL.ITEM_CODE = ESI.ITEM_CODE\n"
+ " AND EAAH.CREATE_USER = SU.USER_ID\n"
+ " AND SU.ORGANIZATION_ID = ?\n"
+ " AND SU.USERNAME LIKE dbo.NVL(?, SU.USERNAME)\n"
+ " AND ESI.ITEM_SPEC LIKE dbo.NVL(?, ESI.ITEM_SPEC)\n"
+ " AND ESI.ITEM_NAME LIKE dbo.NVL(?, ESI.ITEM_NAME)\n"
+ " AND EAAH.STATUS = ISNULL(?, EAAH.STATUS)\n"
+ " AND (? = '' OR EAAH.CREATED_DATE >= ISNULL(?, EAAH.CREATED_DATE))\n"
+ " AND (? = '' OR EAAH.CREATED_DATE <= ISNULL(?, EAAH.CREATED_DATE))\n"
+ " AND EAAL.BARCODE LIKE dbo.NVL(?, EAAL.BARCODE)\n"
+ "ORDER BY EAAH.BILL_NO,\n"
+ " EAAL.BARCODE";
sqlArgs.add(sfDto.getOrganizationId());
sqlArgs.add(dto.getCreateUser());
sqlArgs.add(dto.getItemSpec());
sqlArgs.add(dto.getItemName());
if ("".equals(dto.getStatus()) || null == dto.getStatus()) {
sqlArgs.add(null);
} else {
sqlArgs.add(StrUtil.strToInt(dto.getStatus()));
}
sqlArgs.add(dto.getFromDate());
sqlArgs.add(dto.getFromDate());
sqlArgs.add(dto.getToDate());
sqlArgs.add(dto.getToDate());
sqlArgs.add(dto.getBarcod());
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
} catch (CalendarException ex) {
ex.printLog();
throw new SQLModelException(ex);
}
return sqlModel;
}
/**
* 查询项目编号
*
* @param bookTypeCode
* @return
*/
@SuppressWarnings("unchecked")
public SQLModel getProjectNo(String bookTypeCode) {
SQLModel sqlModel = new SQLModel();
List sqlArgs = new ArrayList();
String sqlStr = "";
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
return sqlModel;
}
public SQLModel doVerify(String itemName) {
SQLModel sqlModel = new SQLModel();
List strArg = new ArrayList();
String strSql = "SELECT 1 FROM AMS_YJ_ITEM AYI WHERE AYI.ITEM_NAME = ?";
strArg.add(itemName);
sqlModel.setSqlStr(strSql);
sqlModel.setArgs(strArg);
return sqlModel;
}
/** 导入错误日志处理 ETS_FA_NEW_LOC */
public SQLModel getUpdateSynErrorLogModel3(String objectNo, String errorMessage) {
SQLModel sqlModel = new SQLModel();
List sqlArgs = new ArrayList();
String sqlStr = "UPDATE ETS_FA_NEW_LOC SET STATUS = 2, MSG = ? WHERE CODE= ?";
sqlArgs.add(errorMessage);
sqlArgs.add(objectNo);
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
return sqlModel;
}
public SQLModel getDeleteByTransIdModel(String transId) {
SQLModel sqlModel = new SQLModel();
List sqlArgs = new ArrayList();
String sqlStr = "DELETE FROM" + " AMS_INSTRU_TRANS_L" + " WHERE" + " TRANS_ID = ?";
sqlArgs.add(transId);
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
return sqlModel;
}
public SQLModel deleteByBarcodeNoModel(String barcodeNo) {
SQLModel sqlModel = new SQLModel();
List sqlArgs = new ArrayList();
String sqlStr = "DELETE FROM" + " AMS_INSTRUMENT_RESERVED" + " WHERE" + " BARCODE = ?";
sqlArgs.add(barcodeNo);
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 updateInfoModel(String barcodeNo) {
SQLModel sqlModel = new SQLModel();
List sqlArgs = new ArrayList();
String sqlStr = "UPDATE AMS_INSTRUMENT_INFO SET CURR_KEEP_USER = null WHERE BARCODE = ?";
sqlArgs.add(barcodeNo);
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
return sqlModel;
}
@SuppressWarnings("unchecked")
public SQLModel getSyncErrorModel() {
SQLModel sqlModel = new SQLModel();
List sqlArgs = new ArrayList();
String sqlStr =
"SELECT COUNT(1) SYNC_ERROR_COUNT FROM ETS_AUTO_SYN_LOG EASL WHERE EASL.SYN_TYPE = 'SYN_CUST_DETAIL' AND EASL.CREATED_BY = ?";
sqlArgs.add(sfUser.getUserId());
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
return sqlModel;
}
public SQLModel getDataDeleteModel() {
SQLModel sqlModel = new SQLModel();
List sqlArgs = new ArrayList();
AmsSpareCategoryDTO amsSpareCategory = (AmsSpareCategoryDTO) dtoParameter;
String sqlStr = "DELETE FROM" + " AMS_SPARE_CATEGORY" + " WHERE BARCODE = ?";
sqlArgs.add(amsSpareCategory.getBarcode());
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
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;
}
@SuppressWarnings("unchecked")
public SQLModel getDataLogDeleteModel() throws SQLModelException {
SQLModel sqlModel = new SQLModel();
List sqlArgs = new ArrayList();
String sqlStr =
"DELETE FROM ETS_AUTO_SYN_LOG WHERE SYN_TYPE = 'SYN_CUST_DETAIL' AND CREATED_BY = ?";
sqlArgs.add(sfUser.getUserId());
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
return sqlModel;
}
/**
* 功能：返回页面翻页查询"MIS资产折旧"时所需要的SQLModel
*
* @return SQLModel
* @throws SQLModelException
*/
public SQLModel getPageQueryModel() throws SQLModelException {
SQLModel sqlModel = new SQLModel();
AmsDeprnAssetsDTO dto = (AmsDeprnAssetsDTO) dtoParameter;
String sqlStr =
"SELECT"
+ " ZFDD.BOOK_TYPE_CODE,\n"
+ " EOCM.BOOK_TYPE_NAME,\n"
+ " ZFDD.ASSET_ID,\n"
+ " ZFDD.TAG_NUMBER,\n"
+ " ZFDD.ASSET_NUMBER,\n"
+ " EFA.FA_CATEGORY1,\n"
+ " EFA.FA_CATEGORY2,\n"
+ " ZFDD.DESCRIPTION,\n"
+ " EOCM.COMPANY SEGMENT1,\n"
+ " ZFDD.COST,\n"
+ " ZFDD.NET_BOOK_VALUE,\n"
+ " ZFDD.PTD_IMPAIRMENT,\n"
+ " ZFDD.YTD_IMPAIRMENT,\n"
+ " ZFDD.IMPAIRMENT_RESERVE,\n"
+ " (ZFDD.COST - ZFDD.DEPRN_RESERVE - ZFDD.IMPAIRMENT_RESERVE) LIMIT_VALUE,\n"
+ " ZFDD.PTD_DEPRN,\n"
+ " ZFDD.YTD_DEPRN,\n"
+ " ZFDD.DEPRN_RESERVE,\n"
+ " ZFDD.PERIOD_NAME,\n"
+ " ZFDD.DEPRN_LEFT_MONTH,\n"
+ " ZFDD.LAST_UPDATE_DATE \n"
+ " FROM SOA.ZTE_FA_DEPRN_DETAIL ZFDD, ETS_OU_CITY_MAP EOCM, ETS_FA_ASSETS EFA\n"
+ " WHERE ZFDD.SEGMENT1 = EOCM.COMPANY_CODE\n"
+ " AND ZFDD.ASSET_ID = EFA.ASSET_ID\n"
+ " AND ZFDD.BOOK_TYPE_CODE = dbo.NVL(?, ZFDD.BOOK_TYPE_CODE)\n"
+ " AND ZFDD.TAG_NUMBER = dbo.NVL(?, ZFDD.TAG_NUMBER)\n"
+ " AND ZFDD.DESCRIPTION LIKE dbo.NVL(?, ZFDD.DESCRIPTION)\n"
+ " AND ZFDD.SEGMENT1 = dbo.NVL(?, ZFDD.SEGMENT1)\n"
+ " AND ZFDD.PERIOD_NAME LIKE dbo.NVL(?, ZFDD.PERIOD_NAME)\n"
+ " AND EOCM.ORGANIZATION_ID = ISNULL(?, EOCM.ORGANIZATION_ID)\n";
List sqlArgs = new ArrayList();
sqlArgs.add(dto.getBookTypeCode());
sqlArgs.add(dto.getTagNumber());
sqlArgs.add(dto.getDescription());
sqlArgs.add(dto.getSegment1());
sqlArgs.add(dto.getPeriodName());
sqlArgs.add(dto.getOrganizationId());
if (!(userAccount.isProvinceUser())) {
sqlStr += " AND EOCM.ORGANIZATION_ID = ?";
sqlArgs.add(userAccount.getOrganizationId());
}
sqlStr += " ORDER BY ZFDD.BOOK_TYPE_CODE,ZFDD.ASSET_ID";
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
return sqlModel;
}
public SQLModel updateHModel(String transId, String transStatus) {
SQLModel sqlModel = new SQLModel();
List sqlArgs = new ArrayList();
String sqlStr =
"UPDATE AMS_INSTRU_TRANS_H SET CONFIRM_USER =? ,CONFIRM_DATE= GETDATE() ,"
+ " TRANS_STATUS=? WHERE TRANS_ID=?";
sqlArgs.add(userAccount.getUserId());
sqlArgs.add(transStatus);
sqlArgs.add(transId);
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
return sqlModel;
}
public void truncateData(String tableName, Connection conn) {
// Connection conn = null;
try {
// conn = DBManager.getDBConnection();
SQLModel sqlModel = new SQLModel();
String sqlStr = "TRUNCATE TABLE " + tableName;
sqlModel.setSqlStr(sqlStr);
DBOperator.updateRecord(sqlModel, conn);
} catch (Exception e) {
e.printStackTrace();
} /*finally {
DBManager.closeDBConnection(conn);
}*/
}
/**
* 功能：获取地点同步成功后，日志更新的SQL模型列表
*
* @param batchId 同步批ID
* @param primaryKeys 同步地点第二段数据时生成的主键
* @return 获取地点同步成功后，日志更新的SQL模型列表
*/
public List<SQLModel> getSynLocationSuccessModel(String batchId, List<String> primaryKeys) {
List<SQLModel> sqlModelList = new ArrayList<SQLModel>();
SQLModel sqlModel = new SQLModel();
for (String primaryKey : primaryKeys) {
List<String> sqlArgs = new ArrayList<String>();
String sqlStr = "UPDATE ETS_FA_NEW_LOC SET STATUS = 1 WHERE CODE = ? AND BATCH_ID = ?";
sqlArgs.add(primaryKey);
sqlArgs.add(batchId);
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
sqlModelList.add(sqlModel);
}
return sqlModelList;
}
@SuppressWarnings("unchecked")
public SQLModel getDataImpDeleteModel(String projectNum, String bookTypeCode)
throws SQLModelException {
SQLModel sqlModel = new SQLModel();
List sqlArgs = new ArrayList();
String sqlStr =
"DELETE FROM ETS_TD_CUST_DETAIL_IMP2 WHERE BOOK_TYPE_CODE=? AND CREATE_USER_ID = ? AND PROJRCT_NUMBER= ? ";
sqlArgs.add(bookTypeCode);
sqlArgs.add(sfUser.getUserId());
sqlArgs.add(projectNum);
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
return sqlModel;
}
public SQLModel insertLData(String barcodeNo, String transId) {
SQLModel sqlModel = new SQLModel();
List sqlArgs = new ArrayList();
String sqlStr =
"INSERT INTO AMS_INSTRU_TRANS_L\n"
+ " (LINE_ID,\n"
+ " BARCODE,\n"
+ " TRANS_ID)\n"
+ "VALUES\n"
+ " ( NEWID(),?,?)";
sqlArgs.add(barcodeNo);
sqlArgs.add(transId);
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
return sqlModel;
}
/**
* 功能：框架自动生成应急保障设备名称字典表 AMS_YJ_ITEM多条数据信息查询SQLModel，请根据实际需要修改。
*
* @return SQLModel 返回多条数据信息查询用SQLModel
* @throws SQLModelException 发生日历异常时转化为该异常抛出
*/
public SQLModel getMuxDataModel() throws SQLModelException {
SQLModel sqlModel = new SQLModel();
try {
List sqlArgs = new ArrayList();
AmsYjItemDTO amsYjItem = (AmsYjItemDTO) dtoParameter;
String sqlStr =
"SELECT "
+ " ITEM_CODE,"
+ " ITEM_NAME,"
+ " ITEM_CATEGORY,"
+ " CREATION_DATE,"
+ " CREATE_USER,"
+ " LAST_UPDATE_DATE,"
+ " LAST_UPDATE_USER"
+ " FROM"
+ " AMS_YJ_ITEM"
+ " WHERE"
+ " (? IS NULL OR ITEM_CODE LIKE ?)"
+ " AND (? IS NULL OR ITEM_NAME LIKE ?)"
+ " AND (? IS NULL OR ITEM_CATEGORY LIKE ?)"
+ " AND (? IS NULL OR CREATION_DATE LIKE ?)"
+ " AND (? IS NULL OR CREATE_USER LIKE ?)"
+ " AND (? IS NULL OR LAST_UPDATE_DATE LIKE ?)"
+ " AND (? IS NULL OR LAST_UPDATE_USER LIKE ?)";
sqlArgs.add(amsYjItem.getItemCode());
sqlArgs.add(amsYjItem.getItemCode());
sqlArgs.add(amsYjItem.getItemName());
sqlArgs.add(amsYjItem.getItemName());
sqlArgs.add(amsYjItem.getItemCategory());
sqlArgs.add(amsYjItem.getItemCategory());
sqlArgs.add(amsYjItem.getCreationDate());
sqlArgs.add(amsYjItem.getCreationDate());
sqlArgs.add(amsYjItem.getCreateUser());
sqlArgs.add(amsYjItem.getCreateUser());
sqlArgs.add(amsYjItem.getLastUpdateDate());
sqlArgs.add(amsYjItem.getLastUpdateDate());
sqlArgs.add(amsYjItem.getLastUpdateUser());
sqlArgs.add(amsYjItem.getLastUpdateUser());
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
} catch (CalendarException ex) {
ex.printLog();
throw new SQLModelException(ex);
}
return sqlModel;
}