public int countByParents(String listParent) throws SystemException {
Session session = null;
try {
session = openSession();
StringBuffer query = new StringBuffer();
query.append("SELECT COUNT(*) as total FROM Vlookupprice WHERE listParent LIKE ?");
SQLQuery q = session.createSQLQuery(query.toString());
q.addScalar("total", Type.LONG);
int queryPos = 0;
q.setString(queryPos++, "%" + listParent + "%");
Long count = null;
Iterator ite = q.list().iterator();
while (ite.hasNext()) {
count = (Long) ite.next();
}
if (count == null) {
count = new Long(0);
}
return count.intValue();
} catch (Exception e) {
throw new SystemException(e);
} finally {
closeSession(session);
}
}
@Override
public List<CalEvent> findByFutureReminders() {
Calendar calendar = Calendar.getInstance();
calendar.add(Calendar.HOUR, -24);
Timestamp calendar_TS = CalendarUtil.getTimestamp(calendar.getTime());
Session session = null;
try {
session = openSession();
String sql = CustomSQLUtil.get(FIND_BY_FUTURE_REMINDERS);
SQLQuery q = session.createSynchronizedSQLQuery(sql);
q.addEntity("CalEvent", CalEventImpl.class);
QueryPos qPos = QueryPos.getInstance(q);
qPos.add(CalEventConstants.REMIND_BY_NONE);
qPos.add(calendar_TS);
qPos.add(calendar_TS);
return q.list(true);
} catch (Exception e) {
throw new SystemException(e);
} finally {
closeSession(session);
}
}
public int countByS_G_U(long groupId, long userId) throws SystemException {
Session session = null;
try {
session = openSession();
String sql = CustomSQLUtil.get(COUNT_BY_S_G_U);
SQLQuery q = session.createSQLQuery(sql);
q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
QueryPos qPos = QueryPos.getInstance(q);
qPos.add(PortalUtil.getClassNameId(MBCategory.class.getName()));
qPos.add(groupId);
qPos.add(userId);
Iterator<Long> itr = q.list().iterator();
if (itr.hasNext()) {
Long count = itr.next();
if (count != null) {
return count.intValue();
}
}
return 0;
} catch (Exception e) {
throw new SystemException(e);
} finally {
closeSession(session);
}
}
@Override
public List<JournalArticle> findByReviewDate(
long classNameId, Date reviewDateLT, Date reviewDateGT) {
Timestamp reviewDateLT_TS = CalendarUtil.getTimestamp(reviewDateLT);
Timestamp reviewDateGT_TS = CalendarUtil.getTimestamp(reviewDateGT);
Session session = null;
try {
session = openSession();
String sql = CustomSQLUtil.get(FIND_BY_REVIEW_DATE);
SQLQuery q = session.createSynchronizedSQLQuery(sql);
q.addEntity(JournalArticleImpl.TABLE_NAME, JournalArticleImpl.class);
QueryPos qPos = QueryPos.getInstance(q);
qPos.add(classNameId);
qPos.add(reviewDateGT_TS);
qPos.add(reviewDateLT_TS);
return q.list(true);
} catch (Exception e) {
throw new SystemException(e);
} finally {
closeSession(session);
}
}
@Override
public List<JournalArticle> findByExpirationDate(
long classNameId, Date expirationDateLT, QueryDefinition<JournalArticle> queryDefinition) {
Timestamp expirationDateLT_TS = CalendarUtil.getTimestamp(expirationDateLT);
Session session = null;
try {
session = openSession();
String sql = CustomSQLUtil.get(FIND_BY_EXPIRATION_DATE, queryDefinition);
SQLQuery q = session.createSynchronizedSQLQuery(sql);
q.addEntity(JournalArticleImpl.TABLE_NAME, JournalArticleImpl.class);
QueryPos qPos = QueryPos.getInstance(q);
qPos.add(classNameId);
qPos.add(queryDefinition.getStatus());
qPos.add(expirationDateLT_TS);
return q.list(true);
} catch (Exception e) {
throw new SystemException(e);
} finally {
closeSession(session);
}
}
@Override
public List<Layout> findByScopeGroup(long groupId, boolean privateLayout) {
Session session = null;
try {
session = openSession();
String sql = CustomSQLUtil.get(FIND_BY_SCOPE_GROUP);
SQLQuery q = session.createSynchronizedSQLQuery(sql);
q.addEntity("Layout", LayoutImpl.class);
QueryPos qPos = QueryPos.getInstance(q);
qPos.add(groupId);
qPos.add(privateLayout);
return q.list(true);
} catch (Exception e) {
throw new SystemException(e);
} finally {
closeSession(session);
}
}
@Override
public List<Layout> findByNoPermissions(long roleId) {
Session session = null;
try {
session = openSession();
String sql = CustomSQLUtil.get(FIND_BY_NO_PERMISSIONS);
SQLQuery q = session.createSynchronizedSQLQuery(sql);
q.addEntity("Layout", LayoutImpl.class);
QueryPos qPos = QueryPos.getInstance(q);
qPos.add(ResourceConstants.SCOPE_INDIVIDUAL);
qPos.add(roleId);
return q.list(true);
} catch (Exception e) {
throw new SystemException(e);
} finally {
closeSession(session);
}
}
protected List<Long> countByC_FN_MN_LN_SN_EA_S(
Session session,
long companyId,
String[] firstNames,
String[] middleNames,
String[] lastNames,
String[] screenNames,
String[] emailAddresses,
int status,
LinkedHashMap<String, Object> params,
boolean andOperator) {
String sql = CustomSQLUtil.get(COUNT_BY_C_FN_MN_LN_SN_EA_S);
sql =
CustomSQLUtil.replaceKeywords(
sql, "lower(User_.firstName)", StringPool.LIKE, false, firstNames);
sql =
CustomSQLUtil.replaceKeywords(
sql, "lower(User_.middleName)", StringPool.LIKE, false, middleNames);
sql =
CustomSQLUtil.replaceKeywords(
sql, "lower(User_.lastName)", StringPool.LIKE, false, lastNames);
sql =
CustomSQLUtil.replaceKeywords(
sql, "lower(User_.screenName)", StringPool.LIKE, false, screenNames);
sql =
CustomSQLUtil.replaceKeywords(
sql, "lower(User_.emailAddress)", StringPool.LIKE, true, emailAddresses);
if (status == WorkflowConstants.STATUS_ANY) {
sql = StringUtil.replace(sql, _STATUS_SQL, StringPool.BLANK);
}
sql = replaceJoinAndWhere(sql, params);
sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);
SQLQuery q = session.createSQLQuery(sql);
q.addScalar("userId", Type.LONG);
QueryPos qPos = QueryPos.getInstance(q);
setJoin(qPos, params);
qPos.add(companyId);
qPos.add(false);
qPos.add(firstNames, 2);
qPos.add(middleNames, 2);
qPos.add(lastNames, 2);
qPos.add(screenNames, 2);
qPos.add(emailAddresses, 2);
if (status != WorkflowConstants.STATUS_ANY) {
qPos.add(status);
}
return q.list(true);
}
public int searchCount(
long companyId,
String keywords,
Boolean configured,
Long paymentMethodId,
boolean withPermissionCheck)
throws SystemException {
Session session = null;
SQLQuery q = null;
try {
session = openSession();
q =
buildSearchQuery(
session,
companyId,
keywords,
configured,
paymentMethodId,
null,
true,
withPermissionCheck);
Iterator<Long> itr = q.list().iterator();
if (itr.hasNext()) {
Long count = itr.next();
if (count != null) {
return count.intValue();
}
}
return 0;
} catch (Exception e) {
if (q != null) _log.error("Query failed : " + q.toString());
throw new SystemException(e);
} finally {
closeSession(session);
}
}
public int countByTransactionAndPaymentMethod(
long transactionId, long paymentMethodId, Boolean configured) throws SystemException {
Session session = null;
try {
session = openSession();
String sql = CustomSQLUtil.get(COUNT_BY_TRANSACTIONID_AND_PAYMENTMETHODID);
if (configured == null) {
sql = StringUtil.replace(sql, "(Payment_PaymentPlugin.configured = ?) AND", "");
sql = StringUtil.replace(sql, "(Payment_PaymentPluginConfig.configured = ?) AND", "");
}
SQLQuery q = session.createSQLQuery(sql);
q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
QueryPos qPos = QueryPos.getInstance(q);
qPos.add(transactionId);
qPos.add(paymentMethodId);
if (configured != null) {
qPos.add(configured);
qPos.add(configured);
}
Iterator<Long> itr = q.list().iterator();
if (itr.hasNext()) {
Long count = itr.next();
if (count != null) {
return count.intValue();
}
}
return 0;
} catch (Exception e) {
throw new SystemException(e);
} finally {
closeSession(session);
}
}
@Override
public JournalArticle findByR_D(long resourcePrimKey, Date displayDate)
throws NoSuchArticleException {
Timestamp displayDate_TS = CalendarUtil.getTimestamp(displayDate);
Session session = null;
try {
session = openSession();
String sql = CustomSQLUtil.get(FIND_BY_R_D);
SQLQuery q = session.createSynchronizedSQLQuery(sql);
q.addEntity(JournalArticleImpl.TABLE_NAME, JournalArticleImpl.class);
QueryPos qPos = QueryPos.getInstance(q);
qPos.add(resourcePrimKey);
qPos.add(displayDate_TS);
List<JournalArticle> articles = q.list();
if (!articles.isEmpty()) {
return articles.get(0);
}
} catch (Exception e) {
throw new SystemException(e);
} finally {
closeSession(session);
}
StringBundler sb = new StringBundler(6);
sb.append("No JournalArticle exists with the key ");
sb.append("{resourcePrimKey=");
sb.append(resourcePrimKey);
sb.append(", displayDate=");
sb.append(displayDate);
sb.append("}");
throw new NoSuchArticleException(sb.toString());
}
public List<ViewPerformanceAppraisalLevels> getCdsPALevels(
int userId, int periodId, int competencyId) throws SystemException {
Session session = null;
try {
session = openSession();
String sql = CustomSQLUtil.get(GET_PALEVELS);
SQLQuery query = session.createSQLQuery(sql);
query.addEntity("ViewPerformanceAppraisalLevels", ViewPerformanceAppraisalLevelsImpl.class);
QueryPos qPos = QueryPos.getInstance(query);
qPos.add(userId);
qPos.add(periodId);
qPos.add(competencyId);
return (List) query.list();
} catch (Exception e) {
System.out.println(
"ERROR in ViewPerformanceAppraisalLevelsFinderImpl.getCdsPALevelsFinder " + e.toString());
return null;
}
}
public List<User> findByNoGroups() throws SystemException {
Session session = null;
try {
session = openSession();
String sql = CustomSQLUtil.get(FIND_BY_NO_GROUPS);
SQLQuery q = session.createSQLQuery(sql);
q.addEntity("User_", UserImpl.class);
return q.list(true);
} catch (Exception e) {
throw new SystemException(e);
} finally {
closeSession(session);
}
}
// dem tong so luong van ban ma touser nhan duoc, ko can biet do la vb den hay di
public int getTotalDocumentByUser(List<Long> fromUserList, long toUserId) throws Exception {
String fromUserString = "";
if (fromUserList.size() != 0) {
for (int i = 0; i < fromUserList.size() - 1; i++) {
fromUserString += fromUserList.get(i) + ", ";
}
fromUserString += fromUserList.get(fromUserList.size() - 1);
} else {
fromUserString += "0";
}
Session session = null;
try {
session = openSession();
String sql = "SELECT COUNT(fromuserid) AS COUNT_VALUE FROM pml_message_note";
sql += " WHERE fromuserid in (" + fromUserString + ") AND touserid = " + toUserId;
sql += " AND isalert = false";
SQLQuery q = session.createSQLQuery(sql);
q.addScalar(COUNT_COLUMN_NAME, Type.LONG);
Long count = null;
Iterator<Long> itr = q.list().iterator();
if (itr.hasNext()) {
count = itr.next();
}
if (count == null) {
count = new Long(0);
}
return count.intValue();
} catch (Exception e) {
throw processException(e);
} finally {
closeSession(session);
}
}
@Override
public List<Layout> findByNullFriendlyURL() {
Session session = null;
try {
session = openSession();
String sql = CustomSQLUtil.get(FIND_BY_NULL_FRIENDLY_URL);
SQLQuery q = session.createSynchronizedSQLQuery(sql);
q.addEntity("Layout", LayoutImpl.class);
return q.list(true);
} catch (Exception e) {
throw new SystemException(e);
} finally {
closeSession(session);
}
}
@Override
public List<CalEvent> findByNoAssets() {
Session session = null;
try {
session = openSession();
String sql = CustomSQLUtil.get(FIND_BY_NO_ASSETS);
SQLQuery q = session.createSynchronizedSQLQuery(sql);
q.addEntity("CalEvent", CalEventImpl.class);
return q.list(true);
} catch (Exception e) {
throw new SystemException(e);
} finally {
closeSession(session);
}
}
@Override
public List<SyncDLObject> filterFindByC_R(long companyId, long repositoryId) {
Session session = null;
try {
session = openSession();
String sql = CustomSQLUtil.get(FIND_BY_FOLDER_TYPE);
sql = sql.concat(_EVENT_SQL);
sql =
InlineSQLHelperUtil.replacePermissionCheck(
sql,
DLFolder.class.getName(),
"SyncDLObject.typePK",
null,
"SyncDLObject.repositoryId",
new long[] {repositoryId},
null);
sql = StringUtil.replace(sql, _PARENT_FOLDER_ID_SQL, StringPool.BLANK);
SQLQuery q = session.createSynchronizedSQLQuery(sql);
q.addEntity("SyncDLObject", SyncDLObjectImpl.class);
QueryPos qPos = QueryPos.getInstance(q);
qPos.add(companyId);
qPos.add(0);
qPos.add(repositoryId);
return q.list();
} catch (Exception e) {
throw new SystemException(e);
} finally {
closeSession(session);
}
}
public int countByPrice(String title, String listParent, int formPrice, int toPrice)
throws SystemException {
Session session = null;
try {
session = openSession();
StringBuffer query = new StringBuffer();
query.append(
"SELECT COUNT(*) as total FROM Vlookupprice WHERE (listParent LIKE ?) AND (title LIKE ?) ");
if (formPrice != 0) {
query.append("AND (price >= ?) ");
}
if (toPrice != 0) {
query.append("AND (price <= ?) ");
}
SQLQuery q = session.createSQLQuery(query.toString());
q.addScalar("total", Type.LONG);
int queryPos = 0;
q.setString(queryPos++, "%" + listParent + "%");
q.setString(queryPos++, "%" + title + "%");
if (formPrice != 0) {
q.setInteger(queryPos++, formPrice);
}
if (toPrice != 0) {
q.setInteger(queryPos++, toPrice);
}
Long count = null;
Iterator ite = q.list().iterator();
while (ite.hasNext()) {
count = (Long) ite.next();
}
if (count == null) {
count = new Long(0);
}
return count.intValue();
} catch (Exception e) {
throw new SystemException(e);
} finally {
closeSession(session);
}
}
@Override
public List<Long> filterFindByR_U_T(long groupId, long userId, long[] typePKs) {
if (ArrayUtil.isEmpty(typePKs)) {
return Collections.emptyList();
}
Session session = null;
try {
session = openSession();
String sql = CustomSQLUtil.get(getClass(), FIND_BY_TYPE_PKS);
sql =
StringUtil.replace(
sql,
new String[] {"[$TYPE_PKS$]", "[$ROLE_IDS_OR_OWNER_ID$]"},
new String[] {getTypePKsSQL(typePKs), getRoleOwnerIdsSQL(groupId, userId)});
SQLQuery sqlQuery = session.createSynchronizedSQLQuery(sql);
sqlQuery.addScalar("primKey", Type.LONG);
QueryPos qPos = QueryPos.getInstance(sqlQuery);
qPos.add(CompanyThreadLocal.getCompanyId());
qPos.add(ResourceConstants.SCOPE_INDIVIDUAL);
return (List<Long>) sqlQuery.list();
} catch (Exception e) {
throw new SystemException(e);
} finally {
closeSession(session);
}
}
public List<User> findByNoAnnouncementsDeliveries(String type) throws SystemException {
Session session = null;
try {
session = openSession();
String sql = CustomSQLUtil.get(FIND_BY_NO_ANNOUNCEMENTS_DELIVERIES);
SQLQuery q = session.createSQLQuery(sql);
q.addEntity("User_", UserImpl.class);
QueryPos qPos = QueryPos.getInstance(q);
qPos.add(type);
return q.list(true);
} catch (Exception e) {
throw new SystemException(e);
} finally {
closeSession(session);
}
}
public List findClipType(ClipType clipType) throws Exception {
Session session = null;
try {
session = openSession();
StringBuffer query = new StringBuffer();
query.append("SELECT * FROM ClipType ");
String str = "";
// clip has id
if ((clipType.getId() != null) && (!clipType.getId().trim().equals(""))) {
str = str + "(AND id = ?) ";
}
// language
if ((clipType.getLanguage() != null) && (!clipType.getLanguage().trim().equals(""))) {
str = str + "AND (language LIKE ?) ";
}
// Name
if ((clipType.getName() != null) && (!clipType.getName().trim().equals(""))) {
str = str + "AND (name LIKE ?) ";
}
// createdDate
if ((clipType.getCreatedDate() != null)
&& (!clipType.getCreatedDate().toString().equals(""))) {
str = str + "AND (createdDate = ?) ";
}
// modifiedDate
if ((clipType.getModifiedDate() != null)
&& (!clipType.getModifiedDate().toString().equals(""))) {
str = str + "AND (modifiedDate = ?) ";
}
// description
if ((clipType.getDescription() != null) && (!clipType.getDescription().equals(""))) {
str = str + "AND (description LIKE ?) ";
}
if (!str.equals("")) {
query.append("WHERE ");
query.append(str);
}
SQLQuery q = session.createSQLQuery(query.toString());
q.addEntity("ClipType", ClipTypeImpl.class);
// clipType has id
int queryPos = 0;
if ((clipType.getId() != null) && (!clipType.getId().trim().equals(""))) {
q.setString(queryPos++, clipType.getId());
}
// language
if ((clipType.getLanguage() != null) && (!clipType.getLanguage().trim().equals(""))) {
q.setString(queryPos++, "%" + clipType.getLanguage() + "%");
}
// Name
if ((clipType.getName() != null) && (!clipType.getName().trim().equals(""))) {
q.setString(queryPos++, "%" + clipType.getName() + "%");
}
// createdDate
if ((clipType.getCreatedDate() != null)
&& (!clipType.getCreatedDate().toString().equals(""))) {
q.setTimestamp(queryPos++, new Timestamp(clipType.getCreatedDate().getTime()));
}
// modifiedDate
if ((clipType.getModifiedDate() != null)
&& (!clipType.getModifiedDate().toString().equals(""))) {
q.setTimestamp(queryPos++, new Timestamp(clipType.getModifiedDate().getTime()));
}
// description
if ((clipType.getDescription() != null) && (!clipType.getDescription().equals(""))) {
q.setString(queryPos++, "%" + clipType.getDescription() + "%");
}
return q.list();
} catch (Exception e) {
// TODO: handle exception
return new ArrayList();
} finally {
closeSession(session);
}
}
@Override
public List<SyncDLObject> filterFindByC_M_R_P(
long companyId, long modifiedTime, long repositoryId, long parentFolderId) {
Session session = null;
try {
session = openSession();
StringBundler sb = new StringBundler(5);
String sql = StringPool.BLANK;
if (modifiedTime != -1) {
sql = CustomSQLUtil.get(FIND_BY_DELETE_EVENT);
sb.append(sql);
sb.append(" UNION ALL ");
}
sql = CustomSQLUtil.get(FIND_BY_FOLDER_TYPE);
if (modifiedTime == -1) {
sql = sql + " AND (SyncDLObject.event != 'trash') ";
}
sql =
InlineSQLHelperUtil.replacePermissionCheck(
sql,
DLFolder.class.getName(),
"SyncDLObject.typePK",
null,
"SyncDLObject.repositoryId",
new long[] {repositoryId},
null);
sb.append(sql);
sb.append(" UNION ALL ");
sql = CustomSQLUtil.get(FIND_BY_FILE_OR_PWC_TYPE);
if (modifiedTime == -1) {
sql = sql + " AND (SyncDLObject.event != 'trash') ";
}
sql =
InlineSQLHelperUtil.replacePermissionCheck(
sql,
DLFileEntry.class.getName(),
"SyncDLObject.typePK",
null,
"SyncDLObject.repositoryId",
new long[] {repositoryId},
null);
sb.append(sql);
sql = sb.toString();
if (parentFolderId == -1) {
sql = StringUtil.replace(sql, _PARENT_FOLDER_ID_SQL, StringPool.BLANK);
}
SQLQuery q = session.createSynchronizedSQLQuery(sql);
q.addEntity("SyncDLObject", SyncDLObjectImpl.class);
QueryPos qPos = QueryPos.getInstance(q);
if (modifiedTime != -1) {
qPos.add(companyId);
qPos.add(modifiedTime);
qPos.add(repositoryId);
if (parentFolderId != -1) {
qPos.add(parentFolderId);
}
}
qPos.add(companyId);
qPos.add(modifiedTime);
qPos.add(repositoryId);
if (parentFolderId != -1) {
qPos.add(parentFolderId);
}
qPos.add(companyId);
qPos.add(modifiedTime);
qPos.add(repositoryId);
if (parentFolderId != -1) {
qPos.add(parentFolderId);
}
qPos.add(PrincipalThreadLocal.getUserId());
return q.list();
} catch (Exception e) {
throw new SystemException(e);
} finally {
closeSession(session);
}
}
public int countClipType(ClipType clipType) throws Exception {
Session session = null;
try {
session = openSession();
StringBuffer query = new StringBuffer();
query.append("SELECT COUNT(*) as total FROM ClipType ");
String str = "";
// clip has id
if ((clipType.getId() != null) && (!clipType.getId().trim().equals(""))) {
str = str + "(AND id = ?) ";
}
// language
if ((clipType.getLanguage() != null) && (!clipType.getLanguage().trim().equals(""))) {
str = str + "AND (language LIKE ?) ";
}
// Name
if ((clipType.getName() != null) && (!clipType.getName().trim().equals(""))) {
str = str + "AND (name LIKE ?) ";
}
// createdDate
if ((clipType.getCreatedDate() != null)
&& (!clipType.getCreatedDate().toString().equals(""))) {
str = str + "AND (createdDate = ?) ";
}
// modifiedDate
if ((clipType.getModifiedDate() != null)
&& (!clipType.getModifiedDate().toString().equals(""))) {
str = str + "AND (modifiedDate = ?) ";
}
// description
if ((clipType.getDescription() != null) && (!clipType.getDescription().equals(""))) {
str = str + "AND (description LIKE ?) ";
}
if (!str.equals("")) {
query.append("WHERE ");
query.append(str);
}
SQLQuery q = session.createSQLQuery(query.toString());
q.addScalar("total", Type.LONG);
// clipType has id
int queryPos = 0;
if ((clipType.getId() != null) && (!clipType.getId().trim().equals(""))) {
q.setString(queryPos++, clipType.getId());
}
// language
if ((clipType.getLanguage() != null) && (!clipType.getLanguage().trim().equals(""))) {
q.setString(queryPos++, "%" + clipType.getLanguage() + "%");
}
// Name
if ((clipType.getName() != null) && (!clipType.getName().trim().equals(""))) {
q.setString(queryPos++, "%" + clipType.getName() + "%");
}
// createdDate
if ((clipType.getCreatedDate() != null)
&& (!clipType.getCreatedDate().toString().equals(""))) {
q.setTimestamp(queryPos++, new Timestamp(clipType.getCreatedDate().getTime()));
}
// modifiedDate
if ((clipType.getModifiedDate() != null)
&& (!clipType.getModifiedDate().toString().equals(""))) {
q.setTimestamp(queryPos++, new Timestamp(clipType.getModifiedDate().getTime()));
}
// description
if ((clipType.getDescription() != null) && (!clipType.getDescription().equals(""))) {
q.setString(queryPos++, "%" + clipType.getDescription() + "%");
}
Long count = null;
Iterator<Long> ite = q.list().iterator();
while (ite.hasNext()) {
count = ite.next();
}
if (count == null) {
count = new Long(0);
}
return count.intValue();
} catch (Exception e) {
// TODO: handle exception
return 0;
} finally {
closeSession(session);
}
}
