// 归还单查询
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;
}
/**
* 功能:仪器仪表的送修返还头文件的生成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 getDataUpdateModel() throws SQLModelException {
SQLModel sqlModel = new SQLModel();
try {
List sqlArgs = new ArrayList();
AmsInstrumentHDTO amsInstrumentHInfo = (AmsInstrumentHDTO) dtoParameter;
String sqlStr =
"UPDATE AMS_INSTRU_TRANS_H\n"
+ " SET TRANS_ID = ?,\n"
+ " TRANS_NO = ?,\n"
+ " TRANS_TYPE = ?,\n"
+ " TRANS_STATUS = ?,\n"
+ " BORROW_USER = ?,\n"
+ " BORROW_DATE = ?,\n"
+
// " CONFIRM_USER = ?,\n" +
// " CONFIRM_DATE = ?,\n" +
" CANCEL_DATE = ?,\n"
+ " CANCEL_REASON = ?\n"
+ " WHERE TRANS_ID = ?";
sqlArgs.add(amsInstrumentHInfo.getTransId());
sqlArgs.add(amsInstrumentHInfo.getTransNo());
sqlArgs.add(amsInstrumentHInfo.getTransType());
sqlArgs.add(amsInstrumentHInfo.getTransStatus());
sqlArgs.add(amsInstrumentHInfo.getBorrowUser());
sqlArgs.add(amsInstrumentHInfo.getBorrowDate());
// sqlArgs.add(amsInstrumentHInfo.getConfirmUser());
// sqlArgs.add(amsInstrumentHInfo.getConfirmDate());
sqlArgs.add(amsInstrumentHInfo.getCancelDate());
sqlArgs.add(amsInstrumentHInfo.getCalPattern());
sqlArgs.add(amsInstrumentHInfo.getTransId());
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
} catch (CalendarException e) {
e.printLog();
throw new SQLModelException(e);
}
return sqlModel;
}
public SQLModel getPrimaryKeyDataModel() { // 明细找头表
SQLModel sqlModel = new SQLModel();
List sqlArgs = new ArrayList();
AmsInstrumentHDTO amsInstrumentHInfo = (AmsInstrumentHDTO) dtoParameter;
String sqlStr =
"SELECT AITH.TRANS_NO,\n"
+ " AITH.TRANS_STATUS,\n"
+ " AMS_PUB_PKG.GET_USER_NAME(AITH.RETURN_USER) RETURN_NAME,\n"
+ " AITH.RETURN_DATE,\n"
+ " AITH.TRANS_TYPE,\n"
+ " AITH.CHECK_USER,\n"
+ " AITH.REPARI_FACTORY VENDOR_NAME,\n"
+ " AITH.TRANS_ID,\n"
+ " AMS_PUB_PKG.GET_STATUS_NAME(AITH.TRANS_STATUS) TRANS_STATUS_NAME\n"
+ " FROM "
+ " AMS_INSTRU_TRANS_H AITH\n"
+ " WHERE AITH.TRANS_ID = ?";
sqlArgs.add(amsInstrumentHInfo.getTransId());
sqlModel.setSqlStr(sqlStr);
sqlModel.setArgs(sqlArgs);
return sqlModel;
}