public ActionForward query(
ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response)
throws Exception {
String room = request.getParameter("room");
String strDate = request.getParameter("date");
Date startDay = UtilDateTime.getThisWeekDay(strDate, 1);
Date endDay = UtilDateTime.getDiffDay(startDay, 6);
try {
Session hs = Hibernate2Session.currentSession();
Transaction tx = null;
tx = hs.beginTransaction();
List valueList = null;
if (room == null || room.trim().equals("")) {
Query query =
hs.createQuery(
"from BookingRoomVO as br where br.bookingDate>=? and br.bookingDate<=? order by br.bookingDate,br.room,br.startTime");
query.setDate(0, startDay);
query.setDate(1, endDay);
valueList = query.list();
} else {
Query query =
hs.createQuery(
"from BookingRoomVO as br where br.room=? and br.bookingDate>=? and br.bookingDate<=? order by br.bookingDate,br.room,br.startTime");
query.setString(0, room);
query.setDate(1, startDay);
query.setDate(2, endDay);
valueList = query.list();
}
request.setAttribute("valueList", valueList);
request.setAttribute("startDay", startDay);
hs.flush();
tx.commit();
} catch (Exception e) {
e.printStackTrace();
// log.error(e.getMessage());
} finally {
try {
Hibernate2Session.closeSession();
} catch (HibernateException e1) {
// log.error(e1.getMessage());
e1.printStackTrace();
} catch (SQLException e1) {
// log.error(e1.getMessage());
e1.printStackTrace();
}
}
return mapping.findForward("query-success");
}
public ActionForward perform(
ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) {
ActionErrors errors = this.getActionErrors(request.getSession());
Locale locale = getLocale(request);
MessageResources messages = getResources();
try {
SimpleDateFormat Date_formater = new SimpleDateFormat("yyyy-MM-dd");
Date nowDate = (java.util.Date) UtilDateTime.nowTimestamp();
String action = request.getParameter("FormAction");
String projType = request.getParameter("projType");
String departmentId = request.getParameter("departmentId");
String textcode = request.getParameter("textcode");
String textpm = request.getParameter("textpm");
String textcust = request.getParameter("textcust");
String texttype = request.getParameter("texttype");
boolean flag = false;
if (request.getParameter("flag") != null) flag = true;
if (texttype == null) texttype = "";
if (projType == null) projType = "";
if (departmentId == null) departmentId = "";
if (textcode == null) textcode = "";
if (textpm == null) textpm = "";
if (textcust == null) textcust = "";
if (action == null) action = "view";
if (action.equals("QueryForList")) {
SQLResults sr =
findQueryResult(
request, projType, departmentId, textcode, textpm, textcust, texttype, flag);
request.setAttribute("QryList", sr);
return (mapping.findForward("success"));
}
if (action.equals("ExportToExcel")) {
return ExportToExcel(
mapping,
request,
response,
projType,
departmentId,
textcode,
textpm,
textcust,
texttype,
flag);
}
} catch (Exception e) {
e.printStackTrace();
}
return (mapping.findForward("success"));
}
public ActionForward list4Add(
ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response)
throws Exception {
// Logger log = Logger.getLogger(EditPreSaleProjectAction.class.getName());
String action = request.getParameter("formAction");
// log.info("action=" + action);
String strDate = request.getParameter("date");
Date date = UtilDateTime.toDate2(strDate + " 00:00:00.000");
String room = request.getParameter("room");
if (action == null) {
action = "view";
}
try {
Session hs = Hibernate2Session.currentSession();
Transaction tx = null;
if (action.equals("create")) {
String personId = request.getParameter("iPerson");
String startTime = request.getParameter("iStartTime");
String endTime = request.getParameter("iEndTime");
BookingRoomVO tmpValue = new BookingRoomVO();
UserLogin person = new UserLogin();
tmpValue.setBookingDate(date);
tmpValue.setRoom(room);
tmpValue.setStartTime(startTime);
tmpValue.setEndTime(endTime);
tx = hs.beginTransaction();
person = (UserLogin) hs.load(person.getClass(), personId);
tmpValue.setPerson(person);
hs.save(tmpValue);
hs.flush();
tx.commit();
}
if (action.equals("remove")) {
Long id = Long.valueOf(request.getParameter("bookingId"));
tx = hs.beginTransaction();
BookingRoomVO tmpValue = (BookingRoomVO) hs.load(BookingRoomVO.class, id);
hs.delete(tmpValue);
hs.flush();
tx.commit();
}
if (action.equals("update")) {
String bookingId[] = request.getParameterValues("bookingId");
String startTime[] = request.getParameterValues("startTime");
String endTime[] = request.getParameterValues("endTime");
BookingRoomVO tmpValue = new BookingRoomVO();
tx = hs.beginTransaction();
int rowSize = java.lang.reflect.Array.getLength(bookingId);
for (int i = 0; i < rowSize; i++) {
Long id = null;
if (bookingId[i] != null && bookingId[i].length() > 0) {
id = Long.valueOf(bookingId[i]);
tmpValue = (BookingRoomVO) hs.load(BookingRoomVO.class, id);
tmpValue.setStartTime(startTime[i]);
tmpValue.setEndTime(endTime[i]);
hs.update(tmpValue);
}
}
tx.commit();
hs.flush();
}
if (action.equals("view")
|| action.equals("create")
|| action.equals("remove")
|| action.equals("update")) {
request.setAttribute("dateAdd", strDate);
request.setAttribute("roomAdd", room);
tx = hs.beginTransaction();
List valueList = null;
Query query =
hs.createQuery(
"from BookingRoomVO as br where br.room=? and br.bookingDate=? order by br.startTime");
query.setString(0, room);
query.setDate(1, date);
valueList = query.list();
request.setAttribute("valueList", valueList);
hs.flush();
tx.commit();
}
} catch (Exception e) {
e.printStackTrace();
// log.error(e.getMessage());
return (mapping.findForward("view"));
} finally {
try {
Hibernate2Session.closeSession();
} catch (HibernateException e1) {
// log.error(e1.getMessage());
e1.printStackTrace();
} catch (SQLException e1) {
// log.error(e1.getMessage());
e1.printStackTrace();
}
}
return mapping.findForward("list4Add-success");
}
private ActionForward ExportToExcel(
ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) {
try {
ActionErrors errors = this.getActionErrors(request.getSession());
String DataId = request.getParameter("DataId");
if ((DataId == null) || (DataId.length() < 1))
actionDebug.addGlobalError(errors, "error.context.required");
if (!errors.empty()) {
saveErrors(request, errors);
return null;
}
// Get Excel Template Path
String TemplatePath = GetTemplateFolder();
if (TemplatePath == null) return null;
// Fetch related Data
net.sf.hibernate.Session hs = Hibernate2Session.currentSession();
ExpenseMaster findmaster = (ExpenseMaster) hs.load(ExpenseMaster.class, new Long(DataId));
if (findmaster == null) return null;
UserLogin ul = findmaster.getExpenseUser();
List ExpList =
hs.createQuery("select et from ExpenseType as et order by et.expSeq ASC").list();
Iterator itExpType = ExpList.iterator();
Date dayStart = findmaster.getExpenseDate();
ArrayList DateList = new ArrayList();
for (int i = 0; i < 14; i++) {
DateList.add(UtilDateTime.getDiffDay(dayStart, i));
}
Iterator itDate = DateList.iterator();
Query q =
hs.createQuery(
"select ed from ExpenseDetail as ed inner join ed.ExpMaster as em inner join ed.ExpType as et where em.Id =:DataId order by ed.ExpenseDate, et.expSeq ASC");
q.setParameter("DataId", DataId);
List detailList = q.list();
q =
hs.createQuery(
"select ec from ExpenseComments as ec inner join ec.ExpMaster as em where em.Id =:DataId order by ec.ExpenseDate");
q.setParameter("DataId", DataId);
List CmtsList = q.list();
Iterator itDetail = detailList.iterator();
Iterator itCmts = CmtsList.iterator();
ExpenseDetail ed = null;
if (itDetail.hasNext()) {
ed = (ExpenseDetail) itDetail.next();
} else {
return null;
}
ExpenseComments ec = null;
if (itCmts.hasNext()) {
ec = (ExpenseComments) itCmts.next();
}
// Start to output the excel file
response.reset();
response.setHeader(
"Content-Disposition",
"attachment;filename=\"Expense" + findmaster.getFormCode() + ".xls\"");
response.setContentType("application/octet-stream");
// Use POI to read the selected Excel Spreadsheet
HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(TemplatePath + "\\" + ExcelTemplate));
// Select the first worksheet
HSSFSheet sheet = wb.getSheet(FormSheetName);
// Header
HSSFRow row = sheet.getRow(5);
HSSFCell cell = row.getCell((short) 1); // ER No.
cell.setCellValue(findmaster.getFormCode());
cell = row.getCell((short) 4); // Department
cell.setEncoding(HSSFCell.ENCODING_UTF_16); // 设置cell编码解决中文高位字节截断
cell.setCellValue(ul.getParty().getDescription());
cell = row.getCell((short) 8); // User Name
cell.setEncoding(HSSFCell.ENCODING_UTF_16); // 设置cell编码解决中文高位字节截断
cell.setCellValue(ul.getName());
cell = row.getCell((short) 12); // Customer
cell.setEncoding(HSSFCell.ENCODING_UTF_16); // 设置cell编码解决中文高位字节截断
cell.setCellValue(
findmaster.getProject().getCustomer().getPartyId()
+ ":"
+ findmaster.getProject().getCustomer().getDescription());
if (findmaster.getClaimType().equals("CY")) { // Paid by
cell = sheet.getRow(1).getCell((short) 12);
cell.setEncoding(HSSFCell.ENCODING_UTF_16); // 设置cell编码解决中文高位字节截断
cell.setCellValue(
findmaster.getProject().getBillTo().getPartyId()
+ ":"
+ findmaster.getProject().getBillTo().getDescription()
+ "("
+ findmaster.getProject().getBillTo().getChineseName()
+ ")");
}
cell = sheet.getRow(2).getCell((short) 12);
cell.setCellValue(findmaster.getExpenseCurrency().getCurrId());
cell = sheet.getRow(3).getCell((short) 12);
cell.setCellValue(findmaster.getCurrencyRate().floatValue());
cell = sheet.getRow(4).getCell((short) 12); // Project Information
cell.setEncoding(HSSFCell.ENCODING_UTF_16); // 设置cell编码解决中文高位字节截断
cell.setCellValue(
findmaster.getProject().getProjId()
+ ":"
+ findmaster.getProject().getProjName()
+ " ( "
+ findmaster.getProject().getDepartment().getDescription()
+ " )");
cell = sheet.getRow(25).getCell((short) 3);
cell.setEncoding(HSSFCell.ENCODING_UTF_16); // 设置cell编码解决中文高位字节截断
cell.setCellValue("Employee (" + ul.getName() + ")");
cell = sheet.getRow(27).getCell((short) 3);
cell.setEncoding(HSSFCell.ENCODING_UTF_16); // 设置cell编码解决中文高位字节截断
cell.setCellValue(
"Confirmed By (" + findmaster.getProject().getProjectManager().getName() + ")");
// List Header
int ExcelRow = 0;
int ExcelCol = 0;
// List
boolean NullData = true;
ExcelRow = ListStartRow;
while (itDate.hasNext()) {
Date fd = (Date) itDate.next();
row = sheet.getRow(ExcelRow);
cell = row.getCell((short) 0);
cell.setCellValue(fd);
ExcelCol = ListStartCol;
itExpType = ExpList.iterator();
while (itExpType.hasNext()) {
ExpenseType et = (ExpenseType) itExpType.next();
NullData = false;
ExcelCol++;
if (ed != null) {
if (ed.getExpenseDate().equals(fd) && ed.getExpType().equals(et)) {
cell = row.getCell((short) ExcelCol);
cell.setEncoding(HSSFCell.ENCODING_UTF_16); // 设置cell编码解决中文高位字节截断
cell.setCellValue(ed.getUserAmount().doubleValue());
if (itDetail.hasNext()) {
ed = (ExpenseDetail) itDetail.next();
}
}
}
}
if (ec != null) {
if (ec.getExpenseDate().equals(fd)) {
cell = row.getCell((short) 1);
cell.setEncoding(HSSFCell.ENCODING_UTF_16); // 设置cell编码解决中文高位字节截断
cell.setCellValue(ec.getComments());
if (itCmts.hasNext()) {
ec = (ExpenseComments) itCmts.next();
}
}
}
ExcelRow++;
}
// 写入Excel工作表
wb.write(response.getOutputStream());
// 关闭Excel工作薄对象
response.getOutputStream().close();
response.flushBuffer();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
