package com.ebizwindow.crm.utils; import java.util.List; import com.ebizwindow.crm.constants.SqlConst;
import com.ebizwindow.crm.constants.TableConst;
import com.ebizwindow.crm.model.TableDefinition;
import com.ebizwindow.crm.portlet.base.SystemStatus;
import com.ebizwindow.crm.service.OpportunityLocalServiceUtil;
import com.ebizwindow.crm.service.TableDefinitionLocalServiceUtil;
import com.liferay.portal.kernel.exception.PortalException;
import com.liferay.portal.kernel.exception.SystemException;
import com.liferay.portal.kernel.util.StringPool; public class SQLUtil {
private static final String PK_COLUMN_CREATEUSERID = "createUserId";
private static final String PK_COLUMN_EDITUSERID = "editUserId";
private static final String PK_COLUMN_AUDITUSERID = "auditUserId";
private static final String PK_COLUMN_CLOSEUSERID = "closeUserId";
private static final String PK_COLUMN_CONFIRMUSERID = "confirmUserId";
private static final String PK_COLUMN_REQUESTUSERID = "requestUserId";
private static final String PK_COLUMN_EXECUTORID = "executorId";
private static final String PK_COLUMN_SUBMITUSERID = "submitUserId";
private static final String PK_COLUMN_OWNERID = "ownerId";
private static final String PK_COLUMN_UPID = "upId";
private static final String PK_COLUMN_CUSTOMERID = "customerId";
private static final String PK_COLUMN_CONTACTID = "contactId";
private static final String PK_COLUMN_CONTRACTID = "contractId";
private static final String PK_COLUMN_OPPORTUNITYID = "opportunityId";
private static final String PK_COLUMN_QUOTATIONID = "quotationId";
private static final String PK_COLUMN_CLUEID = "clueId";
private static final String PK_COLUMN_ACTIVITYID = "activityId";
private static final String PK_COLUMN_MARKETID = "marketId";
private static final String PK_COLUMN_SALESTEMPLATEID = "salesTemplateId";
private static final String PK_COLUMN_DEPARTMENTID = "departmentId";
private static final String PK_COLUMN_PRODUCTID = "productId";
private static final String PK_COLUMN_PROJECTID = "projectId"; public static String getQueryValue(String queryValue, String columnName, long companyId) throws SystemException {
String results = StringPool.BLANK;
String query = StringPool.BLANK;
if (columnName.equals(PK_COLUMN_CREATEUSERID)
|| columnName.equals(PK_COLUMN_EDITUSERID)
|| columnName.equals(PK_COLUMN_AUDITUSERID)
|| columnName.equals(PK_COLUMN_OWNERID)
|| columnName.equals(PK_COLUMN_CLOSEUSERID)
|| columnName.equals(PK_COLUMN_CONFIRMUSERID)
|| columnName.equals(PK_COLUMN_REQUESTUSERID)
|| columnName.equals(PK_COLUMN_SUBMITUSERID)
|| columnName.equals(PK_COLUMN_EXECUTORID) ) { query = "select userId from User_ where firstName like '%" + queryValue + "%'"; } else if (columnName.equals(PK_COLUMN_UPID) || columnName.equals(PK_COLUMN_CUSTOMERID)) { query = "select customerId from CRM_Customer where chineseName like '%" + queryValue + "%'"; } else if (columnName.equals(PK_COLUMN_CONTACTID)) { query = "select contactId from CRM_Contact where chineseName like '%" + queryValue + "%'"; } else if (columnName.equals(PK_COLUMN_MARKETID)) { query = "select marketId from CRM_Market where name like '%" + queryValue + "%'"; } else if (columnName.equals(PK_COLUMN_CLUEID)) { query = "select clueId from CRM_Clue where name like '%" + queryValue + "%'"; } else if (columnName.equals(PK_COLUMN_OPPORTUNITYID)) { query = "select opportunityId from CRM_Opportunity where topic like '%" + queryValue + "%'"; } else if (columnName.equals(PK_COLUMN_QUOTATIONID)) { query = "select quotationId from CRM_Quotation where name like '%" + queryValue + "%'"; } else if (columnName.equals(PK_COLUMN_CONTRACTID)) { query = "select contractId from CRM_Contract where name like '%" + queryValue + "%'"; } else if (columnName.equals(PK_COLUMN_ACTIVITYID)) { query = "select activityId from CRM_Activity where name like '%" + queryValue + "%'"; } else if (columnName.equals(PK_COLUMN_SALESTEMPLATEID)) { query = "select salesTemplateId from CRM_SalesTemplate where name like '%" + queryValue + "%'"; } else if (columnName.equals(PK_COLUMN_PRODUCTID)) { query = "select productId from CRM_Product where name like '%" + queryValue + "%'"; } else if (columnName.equals(PK_COLUMN_DEPARTMENTID)) { query = "select departmentId from OPERATOR_Department where departmentName like '%" + queryValue + "%'"; } else if (columnName.equals(PK_COLUMN_PROJECTID)) { query = "select projectId from CRM_Project where name like '%" + queryValue + "%'"; } query += " and companyId = '" + companyId + "'"; List<Long> entityIDs = OpportunityLocalServiceUtil.searchBySQLQueryString(query, -1, -1); String entityIDsStr = entityIDs.toString();
results = StringPool.OPEN_PARENTHESIS + entityIDsStr.subSequence(1, entityIDsStr.length() - 1) + StringPool.CLOSE_PARENTHESIS; return results;
} public static String symbolToString(String symbol, String value) {
String str = StringPool.BLANK;
if (!symbol.equals(StringPool.BLANK)) {
if (symbol.equals("eq") || symbol.equals(StringPool.EQUAL)) { str = " = '" + value + "'"; } else if (symbol.equals("gt") || symbol.equals(StringPool.GREATER_THAN)) { str = " > '" + value + "'"; } else if (symbol.equals("lt") || symbol.equals(StringPool.LESS_THAN)) { str = " < '" + value + "'"; } else if (symbol.equals("gteq") || symbol.equals(StringPool.GREATER_THAN_OR_EQUAL)) { str = " >= '" + value + "'"; } else if (symbol.equals("lteq") || symbol.equals(StringPool.LESS_THAN_OR_EQUAL)) { str = " <= '" + value + "'"; } else if (symbol.equals("ne") || symbol.equals(StringPool.NOT_EQUAL)) { str = " <> '" + value + "'"; } else if (symbol.equals("c")) { str = " like '%" + value + "%'"; } else if (symbol.equals("sl")) { str = " like '" + value + "%'"; } else if (symbol.equals("sr")) { str = " like '%" + value + "'"; } else if (symbol.equals("nn")) { str = " <> '' "; } else if (symbol.equals("n")) { str = " = '' "; } else if (symbol.equals("isn")) { str = " is null ";
} else if (symbol.equals("!eq")) {
str = " != '" + value + "'";
} else if (symbol.equals("tc")) {
str = " in " + value;
}
} else {
str = " = '' ";
}
return str;
} public static String getActivitySQL(long userId) throws SystemException {
StringBuffer sb = new StringBuffer("select activity.activityId from CRM_Activity activity where (activity.executorId in ")
.append(OperatorUtil.searchViewOperatorIds(userId,TableConst.ACTIVITY))
.append(" or activity.createUserId in ")
.append(OperatorUtil.searchViewOperatorIds(userId,TableConst.ACTIVITY))
.append(")");
return sb.toString();
} public static String getCustomerSQL(long userId) throws SystemException {
String sql = "select customer.customerId from CRM_Customer customer where (customer.ownerId in "
+ OperatorUtil.searchViewOperatorIds(userId, TableConst.CUSTOMER) + ")";
return sql;
} public static String getContactSQL(long userId) throws SystemException {
String sql = "select contact.contactId from CRM_Contact contact where (contact.ownerId in "
+ OperatorUtil.searchViewOperatorIds(userId,TableConst.CONTACT) + ")";
return sql;
} public static String getContactTop10SQL(long userId) throws SystemException {
String sql = "select contact.contactId from CRM_Contact contact where (contact.ownerId in "
+ OperatorUtil.searchViewOperatorIds(userId,TableConst.CONTACT) + ") order by contact.createDate limit 10 ";
return sql;
} public static String getContractSQL(long userId) throws SystemException {
String sql = "select contract.contractId from CRM_Contract contract where (contract.ownerId in "
+ OperatorUtil.searchViewOperatorIds(userId,TableConst.CONTRACT) + ")";
return sql;
} public static String getMarketSQL(long userId) throws SystemException {
String sql = "select market.marketId from CRM_Market market where (market.ownerId in "
+ OperatorUtil.searchViewOperatorIds(userId,TableConst.MARKET) + ")";
return sql;
} public static String getClueSQL(long userId) throws SystemException {
String sql = "select clue.clueId from CRM_Clue clue where (clue.ownerId in "
+ OperatorUtil.searchViewOperatorIds(userId,TableConst.CLUE) + ") and clue.auditStatus='"+SystemStatus.Audit.getStatus()+"'";
return sql;
} public static String getOpportunitySQL(long userId) throws SystemException {
String sql = "select opportunity.opportunityId from CRM_Opportunity opportunity where (opportunity.ownerId in "
+ OperatorUtil.searchViewOperatorIds(userId,TableConst.OPPORTUNITY) + ")";
return sql;
} public static String getOpportunityTop10SQL(long userId) throws SystemException {
String sql = "select opportunity.opportunityId from CRM_Opportunity opportunity where (opportunity.ownerId in "
+ OperatorUtil.searchViewOperatorIds(userId,TableConst.OPPORTUNITY) + ") order by opportunity.createDate limit 10";
return sql;
} public static String getQuotationSQL(long userId) throws SystemException {
String sql = "select quotation.quotationId from CRM_Quotation quotation where (quotation.ownerId in "
+ OperatorUtil.searchViewOperatorIds(userId,TableConst.QUOTATION) + ")";
return sql;
} public static String getOrderSQL(long userId) throws SystemException {
String sql = "select order_.orderId from CRM_Order order_ where (order_.ownerId in "
+ OperatorUtil.searchViewOperatorIds(userId,TableConst.ORDER) + ")"; return sql;
} public static String getProductSQL(long companyId) throws SystemException {
String sql = "select product.productId from CRM_Product product where product.companyId = '" + companyId + "'";
return sql;
} public static String getRPlanSQL(long userId) throws SystemException {
String sql = "select receivablesPlan.receivablesPlanId from CRM_ReceivablesPlan receivablesPlan where (receivablesPlan.ownerId in "
+ OperatorUtil.searchViewOperatorIds(userId,TableConst.RPLAN) + ")";
return sql;
} public static String getRRecordSQL(long userId) throws SystemException{
String sql = "select receivablesRecord.receivablesRecordId from CRM_ReceivablesRecord receivablesRecord where (receivablesRecord.ownerId in "
+ OperatorUtil.searchViewOperatorIds(userId,TableConst.RRECORD) + ")";
return sql;
} public static String getSQLBeginningByTableDefinitionId(long tableDefinitionId) throws PortalException, SystemException {
String result = "";
TableDefinition tableDefinition = TableDefinitionLocalServiceUtil.getTableDefinition(tableDefinitionId);
String tableName = tableDefinition.getTableName();
if (tableName.equals(TableConst.CRM_Customer)) {
result = SqlConst.CUSTOMER_SQL_BEGINNING;
} else if (tableName.equals(TableConst.CRM_Customer)) {
result = SqlConst.CUSTOMER_SQL_BEGINNING;
} else if (tableName.equals(TableConst.CRM_Contact)) {
result = SqlConst.CONTACT_SQL_BEGINNING;
} else if (tableName.equals(TableConst.CRM_Market)) {
result = SqlConst.MARKET_SQL_BEGINNING;
} else if (tableName.equals(TableConst.CRM_Clue)) {
result = SqlConst.CLUE_SQL_BEGINNING;
} else if (tableName.equals(TableConst.CRM_Activity)) {
result = SqlConst.ACTIVITY_SQL_BEGINNING;
} else if (tableName.equals(TableConst.CRM_Opportunity)) {
result = SqlConst.OPPORTUNITY_SQL_BEGINNING;
} else if (tableName.equals(TableConst.CRM_Quotation)) {
result = SqlConst.QUOTATION_SQL_BEGINNING;
} else if (tableName.equals(TableConst.CRM_Contract)) {
result = SqlConst.CONTRACT_SQL_BEGINNING;
} else if (tableName.equals(TableConst.CRM_Product)) {
result = SqlConst.PRODUCT_SQL_BEGINNING;
} else {
result = SqlConst.CUSTOMER_SQL_BEGINNING;
}
return result;
} public static String filterQuery(String columnName){
if (columnName.equals("type") || columnName.equals("code")) {
return columnName + StringPool.UNDERLINE;
} else {
return columnName;
}
} //private static Log _log = LogFactoryUtil.getLog(SQLUtil.class); }