/**
* 功能：插入地点信息
*
* @throws DataHandleException
*/
private void addAddressData(EtsFaAssetsDTO faDTO) throws DataHandleException, QueryException {
try {
EtsObjectDTO obDTO = new EtsObjectDTO();
obDTO.setWorkorderObjectCode(faDTO.getAssetsLocationCode());
obDTO.setWorkorderObjectName(faDTO.getAssetsLocation());
obDTO.setWorkorderObjectLocation(faDTO.getAssetsLocation());
obDTO.setOrganizationId(faDTO.getOrganizationId());
obDTO.setObjectCategory("81");
SQLModel sqlModel = modelProducer.getCount(faDTO.getDepreciationAccount().substring(5, 11));
SimpleQuery sq = new SimpleQuery(sqlModel, conn);
sq.executeQuery();
if (sq.hasResult()) {
Row row = sq.getFirstRow();
obDTO.setCountyCode(row.getStrValue("COUNTY_CODE"));
}
if (obDTO.getWorkorderObjectName().equals("")) {
return;
}
EtsObjectDAO etsObjectDAO = new EtsObjectDAO(userAccount, obDTO, conn);
etsObjectDAO.createData2();
} catch (ContainerException ex) {
ex.printLog();
throw new QueryException(ex);
}
}
/**
* 功能：获得MIS的员工信息
*
* @throws QueryException
* @throws ContainerException
*/
private void appendAddressData(EtsItemInfoDTO itemDTO, EtsFaAssetsDTO faDTO)
throws QueryException {
try {
SQLModel sqlModel =
modelProducer.getLocId(faDTO.getAssetsLocationCode(), faDTO.getOrganizationId());
SimpleQuery sq = new SimpleQuery(sqlModel, conn);
sq.executeQuery();
if (sq.hasResult()) {
Row row = sq.getFirstRow();
itemDTO.setAddressId((String) row.getValue("ADDRESS_ID"));
}
} catch (ContainerException ex) {
ex.printLog();
throw new QueryException(ex);
}
}
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;
}
/**
* 功能:判断项目id的存在性。
*
* @return
*/
private boolean isProjectId(EtsFaAssetsDTO faDTO) throws QueryException {
boolean hasResule = false;
SQLModel sqlModel = modelProducer.hasProjId(faDTO.getProjectid());
SimpleQuery sq = new SimpleQuery(sqlModel, conn);
sq.executeQuery();
if (sq.hasResult()) {
hasResule = true;
}
return hasResule;
}
/**
* 功能:判断barcode的存在性。
*
* @return
*/
private boolean isExist(EtsFaAssetsDTO faDTO) throws QueryException {
boolean hasResule = false;
SQLModel sqlModel = modelProducer.hasBarcode(faDTO.getTagNumber());
SimpleQuery sq = new SimpleQuery(sqlModel, conn);
sq.executeQuery();
if (sq.hasResult()) {
hasResule = true;
}
return hasResule;
}
/**
* 功能：获得ProjectId
*
* @throws QueryException
* @throws ContainerException
*/
private void appendProjIdData(EtsItemInfoDTO itemDTO, EtsFaAssetsDTO faDTO)
throws QueryException {
try {
SQLModel sqlModel = modelProducer.getProId(faDTO.getProjectid());
SimpleQuery sq = new SimpleQuery(sqlModel, conn);
sq.executeQuery();
if (sq.hasResult()) {
Row row = sq.getFirstRow();
itemDTO.setProjectid((String) row.getValue("PROJECT_ID"));
}
} catch (ContainerException ex) {
ex.printLog();
throw new QueryException(ex);
}
}
/**
* 功能：获得MIS的员工信息
*
* @throws QueryException
* @throws ContainerException
*/
private void appendUserData(EtsItemInfoDTO itemDTO, EtsFaAssetsDTO faDTO) throws QueryException {
try {
SQLModel sqlModel = modelProducer.getMISemployee(faDTO.getAssignedToNumber());
SimpleQuery sq = new SimpleQuery(sqlModel, conn);
sq.executeQuery();
if (sq.hasResult()) {
Row row = sq.getFirstRow();
itemDTO.setResponsibilityDept(row.getStrValue("DEPT_CODE"));
itemDTO.setResponsibilityUser(row.getStrValue("EMPLOYEE_ID"));
}
} catch (ContainerException ex) {
ex.printLog();
throw new QueryException(ex);
}
}
/**
* 功能：插入地点信息
*
* @throws DataHandleException
*/
private void matchData(EtsItemInfoDTO itemDTO, EtsFaAssetsDTO faDTO) throws DataHandleException {
String[] systemids = new String[] {itemDTO.getSystemId() + "@@@" + faDTO.getAssetId()};
BarcodeMatchDTO baDTO = new BarcodeMatchDTO();
BarcodeMatchDAO barcodeMatchDAO = new BarcodeMatchDAO(userAccount, baDTO, conn);
barcodeMatchDAO.doMatch(systemids);
}
/**
* 提交工单
*
* @param dtoSet
* @return
* @throws QueryException
* @throws DataHandleException
*/
private boolean importEAMhouse(DTOSet dtoSet) throws DataHandleException {
boolean operatorResult = false;
try {
if (dtoSet != null && dtoSet.getSize() > 0) {
EtsFaAssetsDTO faDTO = null;
EtsItemInfoDTO itemDTO = null;
AmsHouseInfoDTO houseDTO = null;
SystemItemHouse systemItemUtil = new SystemItemHouse();
for (int i = 0; i < dtoSet.getSize(); i++) {
faDTO = (EtsFaAssetsDTO) dtoSet.getDTO(i);
itemDTO = new EtsItemInfoDTO();
itemDTO.setBarcode(faDTO.getTagNumber());
itemDTO.setItemName(faDTO.getAssetsDescription());
itemDTO.setItemSpec(faDTO.getModelNumber());
itemDTO.setItemQty(StrUtil.nullToString(faDTO.getCurrentUnits()));
itemDTO.setStartDate(faDTO.getDatePlacedInService().toString());
itemDTO.setItemCategory("HOUSE");
itemDTO.setAssetId(faDTO.getAssetId());
itemDTO.setOrganizationId(faDTO.getOrganizationId());
itemDTO.setFinanceProp("ASSETS");
itemDTO = systemItemUtil.checkSysItem(conn, itemDTO);
appendProjIdData(itemDTO, faDTO);
appendUserData(itemDTO, faDTO);
appendAddressData(itemDTO, faDTO);
if (StrUtil.isEmpty(itemDTO.getAddressId())) {
addAddressData(faDTO);
appendAddressData(itemDTO, faDTO);
}
if (StrUtil.isEmpty(itemDTO.getAddressId())) {
continue;
}
houseDTO = new AmsHouseInfoDTO();
houseDTO.setBarcode(faDTO.getTagNumber());
houseDTO.setHouseAddress(faDTO.getAssetsLocation());
houseDTO.setHremark("house信息导入");
itemDTO.setSystemId(getNextSystemId());
itemDTO.setRemark("house信息导入");
if (!isExist(faDTO)) {
insertEtsItemInfo(itemDTO); // IN ams_item_info
}
insertHouesInfo(houseDTO); // IN ams_house_info
matchData(itemDTO, faDTO); // 进行匹配操作
}
}
operatorResult = true;
} catch (DataHandleException ex) {
ex.printLog();
throw new DataHandleException(ex);
} catch (SQLException ex) {
Logger.logError(ex);
throw new DataHandleException(ex);
} catch (ContainerException ex) {
ex.printLog();
throw new DataHandleException(ex);
} catch (QueryException ex) {
ex.printLog();
throw new DataHandleException(ex);
} catch (CalendarException ex) {
ex.printLog();
throw new DataHandleException(ex);
}
return operatorResult;
}
/**
* 功能：构造翻页查询SQL
*
* @return SQLModel
* @throws SQLModelException
*/
public SQLModel getPageQueryModel() throws SQLModelException {
SQLModel sqlModel = new SQLModel();
EtsFaAssetsDTO dto = (EtsFaAssetsDTO) dtoParameter;
List sqlArgs = new ArrayList();
String sqlStr =
"SELECT EOCM.COMPANY, "
+ " AMD1.DEPT_NAME RESPONSIBILITY_DEPT,"
+ " EII.BARCODE,"
+ " ESI.ITEM_NAME,"
+ " ESI.ITEM_SPEC,"
+ " AME.USER_NAME,"
+ " AMD2.DEPT_NAME,"
+ " EII.CONTENT_CODE,"
+ " EII.CONTENT_NAME,"
+ " EO.WORKORDER_OBJECT_CODE,"
+ " EO.WORKORDER_OBJECT_NAME"
+ " FROM ETS_ITEM_INFO EII,"
+ " ETS_SYSTEM_ITEM ESI,"
+ " AMS_MIS_DEPT AMD1,"
+ " AMS_MIS_DEPT AMD2,"
+ " AMS_MIS_EMPLOYEE AME,"
+ " ETS_OU_CITY_MAP EOCM,"
+ " AMS_OBJECT_ADDRESS AOA,"
+ " ETS_OBJECT EO,"
+ " ETS_FLEX_VALUE_SET EFVS,"
+ " ETS_FLEX_VALUES EFV"
+ " WHERE ESI.ITEM_CODE = EII.ITEM_CODE "
+ " AND (EO.DISABLE_DATE = '' OR EO.DISABLE_DATE IS NULL)"
+
// " AND (EII.DISABLE_DATE = '' OR EII.ITEM_STATUS = 'DISCARDED')" +
" AND EFVS.FLEX_VALUE_SET_ID = EFV.FLEX_VALUE_SET_ID "
+ " AND EFVS.CODE = 'OBJECT_CATEGORY' "
+ " AND EO.OBJECT_CATEGORY = EFV.CODE ";
if (dto.getUserName().equals("")) {
sqlStr += " AND EII.RESPONSIBILITY_USER *= AME.EMPLOYEE_ID";
} else {
sqlStr += " AND EII.RESPONSIBILITY_USER = AME.EMPLOYEE_ID";
}
sqlStr += " AND EII.RESPONSIBILITY_DEPT *= AMD1.DEPT_CODE";
sqlStr +=
" AND AME.DEPT_CODE *= AMD2.DEPT_CODE"
+ " AND EII.ORGANIZATION_ID *= EOCM.ORGANIZATION_ID"
+ " AND EII.ADDRESS_ID = AOA.ADDRESS_ID"
+ " AND AOA.OBJECT_NO = EO.WORKORDER_OBJECT_NO"
+ " AND EII.ITEM_STATUS <> 'DISCARDED'"
+ " AND NOT EXISTS"
+ " (SELECT 1"
+ " FROM (SELECT EII.BARCODE"
+ " FROM ETS_ITEM_INFO EII,"
+ " AMS_MIS_EMPLOYEE AME,"
+ " AMS_MIS_DEPT AMD"
+ " WHERE EII.RESPONSIBILITY_USER = AME.EMPLOYEE_ID"
+ " AND EII.RESPONSIBILITY_DEPT = AMD.DEPT_CODE"
+ " AND AME.DEPT_CODE = AMD.DEPT_CODE) TMP"
+ " WHERE EII.BARCODE = TMP.BARCODE)";
if (dto.getOrganizationId() != -1) {
sqlStr += " AND EII.ORGANIZATION_ID = ? ";
sqlArgs.add(dto.getOrganizationId());
}
if (!dto.getResponsibilityDept().equals("")) {
sqlStr += " AND AMD1.DEPT_CODE = ? ";
sqlArgs.add(dto.getResponsibilityDept());
}
if (!dto.getUserName().equals("")) {
sqlStr += " AND AME.USER_NAME LIKE ? ";
sqlArgs.add(dto.getUserName());
}
if (StrUtil.isNotEmpty(dto.getAddressId())) {
sqlStr += " AND EII.ADDRESS_ID = ? ";
sqlArgs.add(dto.getAddressId());
}
if (!dto.getContentCode().equals("")) {
sqlStr += " AND EII.CONTENT_CODE = ? ";
sqlArgs.add(dto.getContentCode());
}
if (!dto.getItemName().equals("")) {
sqlStr += " AND ESI.ITEM_NAME LIKE ? ";
sqlArgs.add(dto.getItemName());
}
sqlStr += " ORDER BY EII.SYSTEMID";
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
return sqlModel;
}