/**
* 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;
}
/**
* 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;
}
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;
}
/**
* 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;
}
