SQL Tools Class

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";p rivate static final String pk_column_edituserid = "Edituserid";p rivate static final string Pk_column_audituserid = "Audituserid";p rivate static final String Pk_column_closeuserid = "Closeuserid";p rivate static F Inal string pk_column_confirmuserid = "Confirmuserid";p rivate static final String Pk_column_requestuserid = " Requestuserid ";p rivate static final String Pk_cOlumn_executorid = "Executorid";p rivate static final String Pk_column_submituserid = "Submituserid";p rivate static final String Pk_column_ownerid = "ownerID";p rivate static final String pk_column_upid = "Upid";p rivate static final string Pk_co Lumn_customerid = "CUSTOMERID";p rivate static final String pk_column_contactid = "CONTACTID";p rivate static final string P K_column_contractid = "Contractid";p rivate static final String Pk_column_opportunityid = "Opportunityid";p rivate static  Final string Pk_column_quotationid = "Quotationid";p rivate static final String Pk_column_clueid = "Clueid";p rivate static Final string Pk_column_activityid = "ActivityID";p rivate static final String pk_column_marketid = "Marketid";p rivate stat IC final String Pk_column_salestemplateid = "Salestemplateid";p rivate static final String pk_column_departmentid = "Depar Tmentid ";p rivate static final String pk_column_productid =" PRODUCTID ";p rivate static final String pk_column_projectid =" ProjectID ";p ublic 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 the where name like '%" + queryvalue + "% '";} else if (Columnname.equals (Pk_co Lumn_clueid) {query = "Select Clueid from Crm_clue where name like '%" + queryvalue + "% '";} else if (Columnname.equals (P K_column_opportunityid) {query = "Select Opportunityid from crm_opportunity where topic like '%" + queryvalue + "% '";} el Se if (columnname.equals (Pk_column_quotationid)) {query = "Select Quotationid from crm_quotation where name like '%" + que Ryvalue + "% '";} else if (columnname.equals (Pk_column_contractid)) {query = "Select Contractid from crm_contract where name like '%" + quer Yvalue + "% '";} else if (columnname.equals (Pk_column_activityid)) {query = "Select ActivityID from crm_activity where name like '%" + quer Yvalue + "% '";} 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 = "SelectProductId 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 '%" + Queryva Lue + "% '";} 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 (string Pool.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 whe Re (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_co Ntact 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 C Rm_contact COntact where (Contact.ownerid in "+ operatorutil.searchviewoperatorids (userid,tableconst.contact) +") Order BY Contact.createdate limit "; 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_marke T 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 W Here (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" + Operat Orutil.searchviewoperatorids (userid,tableconst.opportunity) + ")"; return SQL;} public static string Getopportunitytop10sql (Long userId) throws SystemException {String sql = "Select Opportunity.opportu Nityid from Crm_opportunity Opportunity where (Opportunity.ownerid in "+ Operatorutil.searchviewoperatorids (userId, tableconst.opportunity) + "ORDER by opportunity.createdate limit"; 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 o Rder_ 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.receivablespla NId 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);}

　　

SQL Tools Class