/**
   * 功能:插入地点信息
   *
   * @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;
  }