/**
* Query by query sentence
*
* @param sql String Sql sentence
* @param firstResult int First result
* @param maxResult int Last result
* @return List Return query results
*/
public List listByCriteria(String sql, int firstResult, int maxResult) {
Session session;
List results = null;
HbConn hbconn = new HbConn();
try {
session = hbconn.getSession();
Query qry = session.createQuery(sql);
if (maxResult > 0) {
qry.setFirstResult(firstResult);
qry.setMaxResults(maxResult);
}
qry.setCacheable(false);
results = qry.list();
} catch (Exception e) {
// modified by xt System.out.println("Exception)");
} finally {
try {
hbconn.closeSession();
} catch (Exception e2) {
// modified by xt System.out.println("Exception closing session)");
}
return results;
}
}
public List getPartyUser(String stype, String spartyname, String susername, int nPage)
throws HibernateException {
try {
String sQuery;
stype = stype.trim();
if (stype.equals("")) {
sQuery =
"select u from UserLogin u,Party p left outer join p.partyRoles r where u.enable='y' and p.partyId=u.party and r.roleTypeId = ? and (?='' or p.description like ?) and (?='' or u.name like ?)";
} else {
sQuery =
"select u from UserLogin u,Party p left outer join p.partyRoles r where u.enable='y' and p.partyId=u.party and r.roleTypeId <> ? and (?='' or p.description like ?) and (?='' or u.name like ?)";
}
getSession();
Query rs = session.createQuery(sQuery);
rs.setString(0, PartyKeys.CUSTOMER_ROLE_KEY);
rs.setString(1, spartyname);
rs.setString(2, "%" + spartyname + "%");
rs.setString(3, susername);
rs.setString(4, "%" + susername + "%");
rs.setFirstResult((nPage) * QueryListKeys.QUERY_IN_EACH_PAGE);
rs.setMaxResults(QueryListKeys.QUERY_IN_EACH_PAGE);
return rs.list();
} finally {
closeSession();
}
}
public int GetPartySelectCount(String stype, String sdesc, String sname, String saddress)
throws HibernateException {
try {
String sQuery;
getSession();
stype = stype.trim();
if (stype.equals("")) {
sQuery =
"select count(p) from Party p left outer join p.partyRoles r where r.roleTypeId = ? and (?='' or p.description like ?) and (?='' or link_man like ?) and (?='' or address like ?)";
} else {
sQuery =
"select count(p) from Party p left outer join p.partyRoles r where r.roleTypeId <> ? and (?='' or p.description like ?) and (?='' or link_man like ?) and (?='' or address like ?)";
}
Query rs = session.createQuery(sQuery);
rs.setString(0, PartyKeys.CUSTOMER_ROLE_KEY);
rs.setString(1, sdesc);
rs.setString(2, "%" + sdesc + "%");
rs.setString(3, sname);
rs.setString(4, "%" + sname + "%");
rs.setString(5, saddress);
rs.setString(6, "%" + saddress + "%");
// rs.setFirstResult((nPage-1)*QueryListKeys.QUERY_IN_EACH_PAGE);
// rs.setMaxResults(QueryListKeys.QUERY_IN_EACH_PAGE);
List ls = rs.list();
if (!ls.isEmpty()) {
Integer count = (Integer) ls.get(0);
if (count != null) return count.intValue();
}
} finally {
closeSession();
}
return 0;
}
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 List getPartyUserPage(String spartyid, int nPage) {
try {
String sQuery = "select ul from UserLogin as ul where ul.enable='y' and ul.party=?";
getSession();
Query rs = session.createQuery(sQuery);
rs.setString(0, spartyid);
rs.setFirstResult((nPage) * QueryListKeys.QUERY_IN_EACH_PAGE);
rs.setMaxResults(QueryListKeys.QUERY_IN_EACH_PAGE);
return rs.list();
} catch (HibernateException e) {
return null;
} finally {
closeSession();
}
}
public int getPartyUserCount(String stype, String spartyname, String susername)
throws HibernateException {
try {
String sQuery;
stype = stype.trim();
if (stype.equals("")) {
sQuery =
"select count(u) from UserLogin u,Party p left outer join p.partyRoles r where u.enable='y' and p.partyId=u.party and r.roleTypeId = ? and (?='' or p.description like ?) and (?='' or u.name like ?)";
} else {
sQuery =
"select count(u) from UserLogin u,Party p left outer join p.partyRoles r where u.enable='y' and p.partyId=u.party and r.roleTypeId <> ? and (?='' or p.description like ?) and (?='' or u.name like ?)";
}
getSession();
Query rs = session.createQuery(sQuery);
rs.setString(0, PartyKeys.CUSTOMER_ROLE_KEY);
rs.setString(1, spartyname);
rs.setString(2, "%" + spartyname + "%");
rs.setString(3, susername);
rs.setString(4, "%" + susername + "%");
List ls = rs.list();
if (!ls.isEmpty()) {
Integer count = (Integer) ls.get(0);
if (count != null) return count.intValue();
}
} finally {
closeSession();
}
return 0;
}
/**
* Executes the HQL query.
*
* @param query HQL query to execute.
* @param sessionDataBean TODO
* @param isSecureExecute TODO
* @param columnIdsMap
* @return
* @throws ClassNotFoundException
* @throws SQLException
*/
public List executeQuery(
String query,
SessionDataBean sessionDataBean,
boolean isSecureExecute,
Map queryResultObjectDataMap)
throws ClassNotFoundException, DAOException {
List returner = null;
try {
Query hibernateQuery = session.createQuery(query);
returner = hibernateQuery.list();
} catch (HibernateException e) {
throw (new DAOException(e));
}
return returner;
}
public int getPartyUserCount(String spartyid, String susername) throws HibernateException {
try {
String sQuery =
"select count(ul) from UserLogin as ul where ul.enable='y' and ul.party=? and (?='' or name like ?)";
getSession();
Query rs = session.createQuery(sQuery);
rs.setString(0, spartyid);
rs.setString(1, susername);
rs.setString(2, "%" + susername + "%");
// rs.setFirstResult((nPage)*QueryListKeys.QUERY_IN_EACH_PAGE);
// rs.setMaxResults(QueryListKeys.QUERY_IN_EACH_PAGE);
List ls = rs.list();
if (!ls.isEmpty()) {
Integer count = (Integer) ls.get(0);
if (count != null) return count.intValue();
}
} finally {
closeSession();
}
return 0;
}
/**
* Find all events in the given month and year.
*
* @param month - 0-11 (0 = January)
* @param year
*/
public List findEventsFor(int month, int year) {
List events = null;
try {
startOperation();
Date firstDay = DateUtils.newDate(month, 1, year);
Date lastDay = DateUtils.newDate(month + 1, 1, year);
String q =
"from Event event where event.startDate >= :firstDay and event.startDate < :lastDay";
Query query = getSession().createQuery(q);
query.setParameter("firstDay", firstDay);
query.setParameter("lastDay", lastDay);
events = query.list();
getTx().commit();
} catch (HibernateException e) {
handleException(e);
} finally {
HibernateFactory.close(getSession());
}
return events;
}
public List getPartySelectPage(
String stype, String sdesc, String sname, String saddress, int nPage) {
try {
String sQuery;
getSession();
stype = stype.trim();
if (stype.equals("")) {
sQuery =
"select p from Party p left outer join p.partyRoles r where r.roleTypeId = ? and (?='' or p.description like ?) and (?='' or link_man like ?) and (?='' or address like ?)";
} else {
sQuery =
"select p from Party p left outer join p.partyRoles r where r.roleTypeId <> ? and (?='' or p.description like ?) and (?='' or link_man like ?) and (?='' or address like ?)";
}
Query rs = session.createQuery(sQuery);
rs.setString(0, PartyKeys.CUSTOMER_ROLE_KEY);
rs.setString(1, sdesc);
rs.setString(2, "%" + sdesc + "%");
rs.setString(3, sname);
rs.setString(4, "%" + sname + "%");
rs.setString(5, saddress);
rs.setString(6, "%" + saddress + "%");
rs.setFirstResult((nPage) * QueryListKeys.QUERY_IN_EACH_PAGE);
rs.setMaxResults(QueryListKeys.QUERY_IN_EACH_PAGE);
return rs.list();
} catch (HibernateException e) {
return null;
} finally {
closeSession();
}
}
/**
* Erzeugen einer vollständigen Query an der Session mit allen angegeben Suchkriterien. Mit
* attributeNamesToSelect können einzelne Attribute geladen werden.
*
* @param session
* @param attributeNamesToSelect null oder Länge = 0 führt zur Selektion des IMSFacilityImpl
* @return entweder ein IMSFacilityImpl, oder ein Object[] aus den gewählten Attributen
*/
public Query getQuery(Session session, String[] attributeNamesToSelect)
throws HibernateException {
// Der Buffer für die gesammelten where-clause-Häppchen.
StringBuffer whereClause = new StringBuffer();
// Der Zähler für die automatische Nummerierung der positional parameter.
int paramNumber = 0;
// Im ersten Durchgang werden alle übergebenen Suchkriterien abgeklappert, um daraus
// einen korrekten HQL-Query-String zu bauen.
// Im Anschluss an das Erzeugen der Query über die Session mit dem Query-String, müssen
// alle übergebenen Suchkriterien ein zweites Mal abgeklappert werden, um per
// setParameter() die Kriterien in die Query einzusetzen.
if (!id.isEmpty()) {
whereClause.append('(');
for (int i = 0; i < id.size(); i++) {
if (i > 0) {
whereClause.append(" or ");
}
whereClause.append("btsitetemplate.id = :QueryParam" + ++paramNumber);
}
whereClause.append(')');
}
if (!name.isEmpty()) {
if (paramNumber > 0) {
whereClause.append(" and ");
}
whereClause.append('(');
for (int i = 0; i < name.size(); i++) {
if (i > 0) {
whereClause.append(" or ");
}
whereClause.append(
"upper(btsitetemplate.name) like upper(:QueryParam" + ++paramNumber + ")");
}
whereClause.append(')');
}
if (!company.isEmpty()) {
if (paramNumber > 0) {
whereClause.append(" and ");
}
whereClause.append('(');
for (int i = 0; i < company.size(); i++) {
if (i > 0) {
whereClause.append(" or ");
}
whereClause.append("btsitetemplate.company = :QueryParam" + ++paramNumber);
}
whereClause.append(')');
}
if (!reference.isEmpty()) {
if (paramNumber > 0) {
whereClause.append(" and ");
}
whereClause.append('(');
for (int i = 0; i < reference.size(); i++) {
if (i > 0) {
whereClause.append(" or ");
}
whereClause.append(
"upper(btsitetemplate.reference) like upper(:QueryParam" + ++paramNumber + ")");
}
whereClause.append(')');
}
if (!contact.isEmpty()) {
if (paramNumber > 0) {
whereClause.append(" and ");
}
whereClause.append('(');
for (int i = 0; i < contact.size(); i++) {
if (i > 0) {
whereClause.append(" or ");
}
whereClause.append("btsitetemplate.contact = :QueryParam" + ++paramNumber);
}
whereClause.append(')');
}
if (!phone.isEmpty()) {
if (paramNumber > 0) {
whereClause.append(" and ");
}
whereClause.append('(');
for (int i = 0; i < phone.size(); i++) {
if (i > 0) {
whereClause.append(" or ");
}
whereClause.append("btsitetemplate.phone = :QueryParam" + ++paramNumber);
}
whereClause.append(')');
}
if (!fax.isEmpty()) {
if (paramNumber > 0) {
whereClause.append(" and ");
}
whereClause.append('(');
for (int i = 0; i < fax.size(); i++) {
if (i > 0) {
whereClause.append(" or ");
}
whereClause.append("btsitetemplate.fax = :QueryParam" + ++paramNumber);
}
whereClause.append(')');
}
if (!mobile.isEmpty()) {
if (paramNumber > 0) {
whereClause.append(" and ");
}
whereClause.append('(');
for (int i = 0; i < mobile.size(); i++) {
if (i > 0) {
whereClause.append(" or ");
}
whereClause.append("btsitetemplate.mobile = :QueryParam" + ++paramNumber);
}
whereClause.append(')');
}
if (!email.isEmpty()) {
if (paramNumber > 0) {
whereClause.append(" and ");
}
whereClause.append('(');
for (int i = 0; i < email.size(); i++) {
if (i > 0) {
whereClause.append(" or ");
}
whereClause.append("btsitetemplate.email = :QueryParam" + ++paramNumber);
}
whereClause.append(')');
}
if (!street.isEmpty()) {
if (paramNumber > 0) {
whereClause.append(" and ");
}
whereClause.append('(');
for (int i = 0; i < street.size(); i++) {
if (i > 0) {
whereClause.append(" or ");
}
whereClause.append("btsitetemplate.street = :QueryParam" + ++paramNumber);
}
whereClause.append(')');
}
if (!housenumber.isEmpty()) {
if (paramNumber > 0) {
whereClause.append(" and ");
}
whereClause.append('(');
for (int i = 0; i < housenumber.size(); i++) {
if (i > 0) {
whereClause.append(" or ");
}
whereClause.append("btsitetemplate.housenumber = :QueryParam" + ++paramNumber);
}
whereClause.append(')');
}
if (!zip.isEmpty()) {
if (paramNumber > 0) {
whereClause.append(" and ");
}
whereClause.append('(');
for (int i = 0; i < zip.size(); i++) {
if (i > 0) {
whereClause.append(" or ");
}
whereClause.append("btsitetemplate.zip = :QueryParam" + ++paramNumber);
}
whereClause.append(')');
}
if (!city.isEmpty()) {
if (paramNumber > 0) {
whereClause.append(" and ");
}
whereClause.append('(');
for (int i = 0; i < city.size(); i++) {
if (i > 0) {
whereClause.append(" or ");
}
whereClause.append(
"upper(btsitetemplate.city) like upper(:QueryParam" + ++paramNumber + ")");
}
whereClause.append(')');
}
if (!state.isEmpty()) {
if (paramNumber > 0) {
whereClause.append(" and ");
}
whereClause.append('(');
for (int i = 0; i < state.size(); i++) {
if (i > 0) {
whereClause.append(" or ");
}
whereClause.append("btsitetemplate.state = :QueryParam" + ++paramNumber);
}
whereClause.append(')');
}
if (!country.isEmpty()) {
if (paramNumber > 0) {
whereClause.append(" and ");
}
whereClause.append('(');
for (int i = 0; i < country.size(); i++) {
if (i > 0) {
whereClause.append(" or ");
}
whereClause.append("btsitetemplate.country like :QueryParam" + ++paramNumber);
}
whereClause.append(')');
}
if (!remarks.isEmpty()) {
if (paramNumber > 0) {
whereClause.append(" and ");
}
whereClause.append('(');
for (int i = 0; i < remarks.size(); i++) {
if (i > 0) {
whereClause.append(" or ");
}
whereClause.append("btsitetemplate.remarks = :QueryParam" + ++paramNumber);
}
whereClause.append(')');
}
if (!regionid.isEmpty()) {
if (paramNumber > 0) {
whereClause.append(" and ");
}
whereClause.append('(');
for (int i = 0; i < regionid.size(); i++) {
if (i > 0) {
whereClause.append(" or ");
}
whereClause.append("btsitetemplate.regionid = :QueryParam" + ++paramNumber);
}
whereClause.append(')');
}
if (!type.isEmpty()) {
if (paramNumber > 0) {
whereClause.append(" and ");
}
whereClause.append('(');
for (int i = 0; i < type.size(); i++) {
if (i > 0) {
whereClause.append(" or ");
}
whereClause.append("btsitetemplate.type= :QueryParam" + ++paramNumber);
}
whereClause.append(')');
}
// Endgültige Query erzeugen.
String where = "";
if (whereClause.length() > 0) {
where = " where " + whereClause.toString();
}
StringBuffer selectClause = new StringBuffer();
if (attributeNamesToSelect != null) {
for (int i = 0; i < attributeNamesToSelect.length; i++) {
selectClause.append("btsitetemplate.");
selectClause.append(attributeNamesToSelect[i]);
if (i + 1 < attributeNamesToSelect.length) {
selectClause.append(", ");
}
}
}
Query query =
session.createQuery(
"select "
+ selectClause.toString()
+ " from BTSiteTemplateImpl btsitetemplate "
+ where);
// Positional parameter setzen.
paramNumber = 0;
for (int i = 0; i < id.size(); i++) {
query.setParameter("QueryParam" + ++paramNumber, id.get(i));
}
for (int i = 0; i < name.size(); i++) {
query.setParameter("QueryParam" + ++paramNumber, name.get(i));
}
for (int i = 0; i < company.size(); i++) {
query.setParameter("QueryParam" + ++paramNumber, company.get(i));
}
for (int i = 0; i < reference.size(); i++) {
query.setParameter("QueryParam" + ++paramNumber, reference.get(i));
}
for (int i = 0; i < contact.size(); i++) {
query.setParameter("QueryParam" + ++paramNumber, contact.get(i));
}
for (int i = 0; i < phone.size(); i++) {
query.setParameter("QueryParam" + ++paramNumber, phone.get(i));
}
for (int i = 0; i < fax.size(); i++) {
query.setParameter("QueryParam" + ++paramNumber, fax.get(i));
}
for (int i = 0; i < mobile.size(); i++) {
query.setParameter("QueryParam" + ++paramNumber, mobile.get(i));
}
for (int i = 0; i < email.size(); i++) {
query.setParameter("QueryParam" + ++paramNumber, email.get(i));
}
for (int i = 0; i < street.size(); i++) {
query.setParameter("QueryParam" + ++paramNumber, street.get(i));
}
for (int i = 0; i < housenumber.size(); i++) {
query.setParameter("QueryParam" + ++paramNumber, housenumber.get(i));
}
for (int i = 0; i < zip.size(); i++) {
query.setParameter("QueryParam" + ++paramNumber, zip.get(i));
}
for (int i = 0; i < city.size(); i++) {
query.setParameter("QueryParam" + ++paramNumber, city.get(i));
}
for (int i = 0; i < state.size(); i++) {
query.setParameter("QueryParam" + ++paramNumber, state.get(i));
}
for (int i = 0; i < country.size(); i++) {
query.setParameter("QueryParam" + ++paramNumber, country.get(i));
}
for (int i = 0; i < remarks.size(); i++) {
query.setParameter("QueryParam" + ++paramNumber, remarks.get(i));
}
for (int i = 0; i < regionid.size(); i++) {
query.setParameter("QueryParam" + ++paramNumber, regionid.get(i));
}
for (int i = 0; i < type.size(); i++) {
query.setParameter("QueryParam" + ++paramNumber, type.get(i));
}
return query;
}
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;
}
public ActionForward perform(
ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) {
// Extract attributes we will need
Logger log = Logger.getLogger(PRMUserListAction.class.getName());
Locale locale = getLocale(request);
MessageResources messages = getResources();
String action = request.getParameter("FormAction");
DynaValidatorForm actionForm = (DynaValidatorForm) form;
HttpSession session = request.getSession();
List projectSelectArr = new ArrayList();
List result = new ArrayList();
List pageNumberList = new ArrayList();
PageBean pageBean = new PageBean();
String pageNumber = request.getParameter("pageNumber");
if (pageNumber == null) pageNumber = "";
if (!pageNumber.equals("")) {
pageBean = (PageBean) session.getAttribute("UserPageBean");
pageBean.setCurrentPage(new Integer(pageNumber).intValue());
pageNumberList = getPageNumberList(pageBean);
} else {
// init display
try {
net.sf.hibernate.Session hs = Hibernate2Session.currentSession();
Transaction tx = null;
String PartyId = request.getParameter("partyId");
String lStrOpt = request.getParameter("rad");
String srchStaff = request.getParameter("srchStaff");
String srchDep = request.getParameter("srchDep");
if (PartyId == null) PartyId = "";
if (lStrOpt == null) lStrOpt = "2";
if (srchStaff == null) srchStaff = "";
if (srchDep == null) srchDep = "";
String ChildPartyString = "'" + PartyId + "'";
String QryStr = "";
/** 获得指定机构的下级机构列表 */
Party party = (Party) hs.load(Party.class, PartyId);
PartyHelper ph = new PartyHelper();
List pList = ph.getAllSubPartysByPartyId(hs, PartyId);
if (pList != null) {
Iterator pIt = pList.iterator();
while (pIt.hasNext()) {
Party p = (Party) pIt.next();
ChildPartyString = ChildPartyString + ",'" + p.getPartyId() + "'";
}
}
QryStr =
"select ul from UserLogin as ul inner join ul.party as p where p.partyId in ("
+ ChildPartyString
+ ")";
if (!srchStaff.equals("")) {
if (lStrOpt.equals("2")) {
QryStr =
QryStr
+ " and (ul.userLoginId like '%"
+ srchStaff
+ "%' or ul.name like '%"
+ srchStaff
+ "%')";
} else {
QryStr =
QryStr
+ " and (ul.userLoginId = '"
+ srchStaff
+ "' or ul.name = '"
+ srchStaff
+ "')";
}
}
if (!srchDep.equals("")) {
if (lStrOpt.equals("2")) {
QryStr =
QryStr
+ " and (p.partyId like '%"
+ srchDep
+ "%' or p.description like '%"
+ srchDep
+ "%')";
} else {
QryStr =
QryStr
+ " and (p.partyId = '"
+ srchDep
+ "' or p.description = '"
+ srchDep
+ "')";
}
}
Query q = hs.createQuery(QryStr);
result = q.list();
pageBean.setItemList(result);
int recCount = result.size();
pageBean.setPage(MAXPAGE, recCount);
pageNumberList = ProjectListAction.getPageNumberList(pageBean);
} catch (Exception e) {
e.printStackTrace();
log.error(e.getMessage());
return (mapping.findForward("success"));
} finally {
try {
Hibernate2Session.closeSession();
} catch (HibernateException e1) {
log.error(e1.getMessage());
e1.printStackTrace();
} catch (SQLException e1) {
log.error(e1.getMessage());
e1.printStackTrace();
}
}
}
session.setAttribute("UserPageBean", pageBean);
request.setAttribute("pageName", "FindPRMPage");
request.setAttribute("PageNumberList", pageNumberList);
return (mapping.findForward("success"));
}
/**
* Retrieves the records for class name in sourceObjectName according to field values passed in
* the passed session.
*
* @param whereColumnName An array of field names.
* @param whereColumnCondition The comparision condition for the field values.
* @param whereColumnValue An array of field values.
* @param joinCondition The join condition.
* @param The session object.
*/
public List retrieve(
String sourceObjectName,
String[] selectColumnName,
String[] whereColumnName,
String[] whereColumnCondition,
Object[] whereColumnValue,
String joinCondition)
throws DAOException {
List list = null;
try {
StringBuffer sqlBuff = new StringBuffer();
String className = Utility.parseClassName(sourceObjectName);
// Logger.out.debug("***********className:"+className);
if (selectColumnName != null && selectColumnName.length > 0) {
sqlBuff.append("Select ");
// Added by Aarti
// --------------------------------
// if(sourceObjectName.equals(Site.class.getName()))
// {
// sqlBuff.append(className + "." + Constants.SYSTEM_IDENTIFIER);
// sqlBuff.append(", ");
// }
// ---------------------------------
for (int i = 0; i < selectColumnName.length; i++) {
sqlBuff.append(className + "." + selectColumnName[i]);
if (i != selectColumnName.length - 1) {
sqlBuff.append(", ");
}
}
sqlBuff.append(" ");
}
// Logger.out.debug(" String : "+sqlBuff.toString());
Query query = null;
sqlBuff.append("from " + sourceObjectName + " " + className);
// Logger.out.debug(" String : "+sqlBuff.toString());
if ((whereColumnName != null && whereColumnName.length > 0)
&& (whereColumnCondition != null && whereColumnCondition.length == whereColumnName.length)
&& (whereColumnValue != null)) {
if (joinCondition == null) joinCondition = Constants.AND_JOIN_CONDITION;
sqlBuff.append(" where ");
// Adds the column name and search condition in where clause.
for (int i = 0; i < whereColumnName.length; i++) {
sqlBuff.append(className + "." + whereColumnName[i] + " ");
if (whereColumnCondition[i].indexOf("in") != -1) {
sqlBuff.append(whereColumnCondition[i] + "( ");
Object valArr[] = (Object[]) whereColumnValue[i];
for (int j = 0; j < valArr.length; j++) {
// Logger.out.debug(sqlBuff);
sqlBuff.append("? ");
if ((j + 1) < valArr.length) sqlBuff.append(", ");
}
sqlBuff.append(") ");
} else if (whereColumnCondition[i].indexOf("is not null") != -1) {
sqlBuff.append(whereColumnCondition[i]);
} else if (whereColumnCondition[i].indexOf("is null") != -1) {
sqlBuff.append(whereColumnCondition[i]);
} else {
sqlBuff.append(whereColumnCondition[i] + " ? ");
}
if (i < (whereColumnName.length - 1)) sqlBuff.append(" " + joinCondition + " ");
}
// Logger.out.debug(sqlBuff.toString());
query = session.createQuery(sqlBuff.toString());
int index = 0;
// Adds the column values in where clause
for (int i = 0; i < whereColumnValue.length; i++) {
// Logger.out.debug("whereColumnValue[i]. " + whereColumnValue[i]);
if (whereColumnCondition[i].equals("is null")
|| whereColumnCondition[i].equals("is not null")) {
} else {
Object obj = whereColumnValue[i];
if (obj instanceof Object[]) {
Object[] valArr = (Object[]) obj;
for (int j = 0; j < valArr.length; j++) {
query.setParameter(index, valArr[j]);
index++;
}
} else {
query.setParameter(index, obj);
index++;
}
}
}
} else {
query = session.createQuery(sqlBuff.toString());
}
list = query.list();
// Added by Aarti
// --------------------------------
// if(sourceObjectName.equals(Site.class.getName()))
// {
// boolean isAuthorized;
// Object[] objects = null;
// Object[] newObjects = null;
// Long id;
// Site site;
//
// if (selectColumnName != null && selectColumnName.length > 0)
// {
// if(list != null)
// {
// for(int i=0; i<list.size();)
// {
// objects = (Object[]) list.get(i);
//
// if(objects != null)
// {
// newObjects = new Object[objects.length-1];
// id = (Long) objects[0];
// isAuthorized = SecurityManager.getInstance(this.getClass())
// .isAuthorized("*****@*****.**",
// sourceObjectName+"_"+id,
// Permissions.USE);
// Logger.out.debug(" User's Authorization to update
// "+sourceObjectName+"_"+id+" "+isAuthorized);
// list.remove(i);
// if(isAuthorized)
// {
// for(int x = 1;x<objects.length;x++ )
// {
// newObjects[x-1] = objects[x];
// }
// list.add(i,newObjects);
// i++;
// }
// }
// }
// }
// }
// else
// {
// if(list != null)
// {
// for(int i=0; i<list.size();)
// {
// site = (Site) list.get(i);
// if(site !=null)
// {
// isAuthorized = SecurityManager.getInstance(this.getClass())
// .isAuthorized("*****@*****.**",
// sourceObjectName+"_"+site.getId(),
// Permissions.USE);
// Logger.out.debug(" User's Authorization to update
// "+sourceObjectName+"_"+site.getId()+" "+isAuthorized);
//
// if(isAuthorized)
// {
// i++;
// }
// else
// {
// list.remove(i);
// }
// }
// }
// }
// }
// }
// ---------------------------------
// Logger.out.debug(" String : " + sqlBuff.toString());
} catch (HibernateException hibExp) {
Logger.out.error(hibExp.getMessage(), hibExp);
throw new DAOException("Error in retrieve " + hibExp.getMessage(), hibExp);
} catch (Exception exp) {
Logger.out.error(exp.getMessage(), exp);
throw new DAOException("Logical Erroe in retrieve method " + exp.getMessage(), exp);
}
return list;
}
