/**
* 查询差异结果的数据
*
* @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;
}
@SuppressWarnings("unchecked")
public SQLModel getMisProjectId_two(String projectNumber2) {
SQLModel sqlModel = new SQLModel();
DealTdProjectDiversityCheckDTO srvAssetCategory = (DealTdProjectDiversityCheckDTO) dtoParameter;
List sqlArgs = new ArrayList();
String sqlStr =
" SELECT "
// + " EPPA.PROJECT_ID,"
+ " EPPA.MIS_PROJECT_ID"
// + " EPPA.NAME PROJECT_NAME,"
// + " EPPA.SEGMENT1 PROJECT_NUMBER,"
// + " EPPA.PROJECT_TYPE"
+ " FROM ETS_PA_PROJECTS_ALL EPPA"
+ " WHERE ORGANIZATION_ID IN (SELECT ORGANIZATION_ID FROM ETS_OU_CITY_MAP WHERE BOOK_TYPE_CODE=?)"
+ " AND EPPA.SEGMENT1=?"
+ " ORDER BY EPPA.PROJECT_ID";
sqlArgs.add(srvAssetCategory.getBookTypeCode());
sqlArgs.add(projectNumber2);
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
return sqlModel;
}
@SuppressWarnings("unchecked")
public SQLModel getDataCreateModel() throws SQLModelException {
SQLModel sqlModel = new SQLModel();
List sqlArgs = new ArrayList();
DealTdProjectDiversityCheckDTO srvAssetCategory = (DealTdProjectDiversityCheckDTO) dtoParameter;
String sqlStr =
"INSERT INTO "
+ " ETS_TD_CUST_DETAIL_IMP2("
+ " TAG_NUMBER,"
+ " ASSET_NAME,"
+ " ASSET_DESCRIPTION,"
+ " MODEL_NUMBER,"
+ " ASSET_CATEGORY,"
+ " ASSET_CATEGORY_DESC,"
+ " UNIT_OF_MEASURE,"
+ " EMPLOYEE_NUMBER,"
+ " LOCATION_CODE,"
+ " ASSET_LOCATION,"
+ " MANUFACTORER_NAME,"
+ " ATTRIBUTE4,"
+ " ATTRIBUTE5,"
+ " ATTRIBUTE6,"
+ " ATTRIBUTE7,"
+ " BOOK_TYPE_CODE,"
+ " PROJRCT_NUMBER,"
+ " ASSET_UNITS," //
+ " TASK_ID,"
+ " PROJECT_ID,"
+ " PROJECT_ASSET_ID,"
+ " CREATE_USER_ID,"
+ " CREATION_DATE,"
+ " START_DATE , " //
+ " CUST_ID "
+ ") VALUES ("
+ " ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CONVERT(FLOAT, ?), CONVERT(FLOAT, ?), CONVERT(FLOAT, ?), CONVERT(FLOAT, ?), ?, GETDATE(), ? , NEWID())";
sqlArgs.add(srvAssetCategory.getTagNumber());
sqlArgs.add(srvAssetCategory.getAssetName());
sqlArgs.add(srvAssetCategory.getAssetDescription());
sqlArgs.add(srvAssetCategory.getModelNumber());
sqlArgs.add(srvAssetCategory.getAssetCategory());
sqlArgs.add(srvAssetCategory.getAssetCategoryDesc());
sqlArgs.add(srvAssetCategory.getUnitOfMeasure());
sqlArgs.add(srvAssetCategory.getEmployeeNumber());
sqlArgs.add(srvAssetCategory.getLocationCode());
sqlArgs.add(srvAssetCategory.getAssetLocation());
sqlArgs.add(srvAssetCategory.getManufactorerName());
sqlArgs.add(srvAssetCategory.getAttribute4());
sqlArgs.add(srvAssetCategory.getAttribute5());
sqlArgs.add(srvAssetCategory.getAttribute6());
sqlArgs.add(srvAssetCategory.getAttribute7());
sqlArgs.add(srvAssetCategory.getBookTypeCode());
sqlArgs.add(srvAssetCategory.getProjectNumber());
sqlArgs.add(srvAssetCategory.getAssetUnits()); //
sqlArgs.add(srvAssetCategory.getTaskId());
sqlArgs.add(srvAssetCategory.getProjectId());
sqlArgs.add(srvAssetCategory.getProjectAssetId());
sqlArgs.add(sfUser.getUserId());
// 增加start_date
sqlArgs.add(getHandleDateFromERP(srvAssetCategory.getDatePlacedInService())); //
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
return sqlModel;
}
@SuppressWarnings("unchecked")
public SQLModel getDataUpdateModel() throws SQLModelException {
SQLModel sqlModel = new SQLModel();
List sqlArgs = new ArrayList();
DealTdProjectDiversityCheckDTO srvAssetCategory = (DealTdProjectDiversityCheckDTO) dtoParameter;
String sqlStr =
"UPDATE ZTE_TD_CUST_DETAIL"
+ " SET"
+ " BOOK_TYPE_CODE = ?,"
+ " PROJECT_NUMBER = ?,"
+ " TASK_NUMBER = ?,"
+ " TASK_NAME = ?,"
+ " PROJECT_TYPE = ?,"
+ " TAG_NUMBER = ?,"
+ " ASSET_NAME = ?,"
+ " ASSET_DESCRIPTION = ?,"
+ " ASSET_NUMBER = ?,"
+ " ASSET_CATEGORY = ?,"
+ " ASSET_CATEGORY_DESC = ?,"
+ " ASSET_LOCATION = ?,"
+ " ASSET_UNITS = CONVERT(FLOAT, ?),"
+ " UNIT_OF_MEASURE = ?,"
+ " CAPITALIZED_COST = CONVERT(FLOAT, ?),"
+ " CAPITALIZED_DATE = ?,"
+ " ASSET_KEY_CCID_DESC = ?,"
+ " ASSET_EMPLOYEE = CONVERT(FLOAT, ?),"
+ " EMPLOYEE_NAME = ?,"
+ " EMPLOYEE_NUMBER = ?,"
+ " DEPRECIATION_EXPENSE_CCID = CONVERT(FLOAT, ?),"
+ " MODEL_NUMBER = ?,"
+ " MANUFACTURER_NAME = ?,"
+ " DATE_PLACED_IN_SERVICE = ?,"
+ " FA_PERIOD_NAME = ?,"
+ " LOCATION_ID = CONVERT(FLOAT, ?),"
+ " LOCATION_CODE = ?,"
+ " TASK_ID = CONVERT(FLOAT, ?),"
+ " PROJECT_ID = CONVERT(FLOAT, ?),"
+ " PROJECT_ASSET_ID = CONVERT(FLOAT, ?),"
+ " ATTRIBUTE4 = ?,"
+ " ATTRIBUTE5 = ?,"
+ " ATTRIBUTE6 = ?,"
+ " ATTRIBUTE7 = ?"
+ " WHERE PROJECT_ASSET_ID = ?"
+ " AND TASK_ID = ?"
+ " AND PROJECT_ID = ?";
sqlArgs.add(srvAssetCategory.getBookTypeCode());
sqlArgs.add(srvAssetCategory.getProjectNumber());
sqlArgs.add(srvAssetCategory.getTaskNumber());
sqlArgs.add(srvAssetCategory.getTaskName());
sqlArgs.add(srvAssetCategory.getProjectType());
sqlArgs.add(srvAssetCategory.getTagNumber());
sqlArgs.add(srvAssetCategory.getAssetName());
sqlArgs.add(srvAssetCategory.getAssetDescription());
sqlArgs.add(srvAssetCategory.getAssetNumber());
sqlArgs.add(srvAssetCategory.getAssetCategory());
sqlArgs.add(srvAssetCategory.getAssetCategoryDesc());
sqlArgs.add(srvAssetCategory.getAssetLocation());
sqlArgs.add(srvAssetCategory.getAssetUnits());
sqlArgs.add(srvAssetCategory.getUnitOfMeasure());
sqlArgs.add(srvAssetCategory.getCapitalizedCost());
sqlArgs.add(srvAssetCategory.getCapitalizedDate());
sqlArgs.add(srvAssetCategory.getAssetKeyCcidDesc());
sqlArgs.add(srvAssetCategory.getAssetEmployee());
sqlArgs.add(srvAssetCategory.getEmployeeName());
sqlArgs.add(srvAssetCategory.getEmployeeNumber());
sqlArgs.add(srvAssetCategory.getDepreciationExpenseCcid());
sqlArgs.add(srvAssetCategory.getModelNumber());
sqlArgs.add(srvAssetCategory.getManufactorerName());
sqlArgs.add(srvAssetCategory.getDatePlacedInService());
sqlArgs.add(srvAssetCategory.getFaPeriodName());
sqlArgs.add(srvAssetCategory.getLocationId());
sqlArgs.add(srvAssetCategory.getLocationCode());
sqlArgs.add(srvAssetCategory.getTaskId());
sqlArgs.add(srvAssetCategory.getProjectId());
sqlArgs.add(srvAssetCategory.getProjectAssetId());
sqlArgs.add(srvAssetCategory.getAttribute4());
sqlArgs.add(srvAssetCategory.getAttribute5());
sqlArgs.add(srvAssetCategory.getAttribute6());
sqlArgs.add(srvAssetCategory.getAttribute7());
sqlArgs.add(srvAssetCategory.getProjectAssetId());
sqlArgs.add(srvAssetCategory.getTaskId());
sqlArgs.add(srvAssetCategory.getProjectId());
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
return sqlModel;
}