2014-03-12 SQL Statements at Work
I was thinking of SQL Statements I wrote back in 2005 and was reminded of something I helped write in 2013, I think. It was a report to show you the Top 10 clients by turnover, with various search criteria. It was huge. The framework used is Eclipse Scout. The Database is Oracle. Some stuff edited...
SQL Statements I wrote back in 2005
/**
* Helper function for the Top 10 Companies Report.
*/
private SearchFilter getCompanyTopReportHavingFilter(CompanyTopReportSearchFormData formData, String turnover) {
SearchFilter havingFilter = new SearchFilter();
// turnover values
if (formData.getTurnoverFrom().getValue() != null
|| formData.getTurnoverTo().getValue() != null) {
havingFilter.addWhere(" AND ( " + turnover + " ) ");
if (formData.getTurnoverFrom().getValue() != null
&& formData.getTurnoverTo().getValue() != null) {
havingFilter.addWhere("BETWEEN :turnoverFrom AND :turnoverTo ",
new NVPair("turnoverFrom", formData.getTurnoverFrom()),
new NVPair("turnoverTo", formData.getTurnoverTo()));
} else if (formData.getTurnoverFrom().getValue() != null) {
havingFilter.addWhere(" >= :turnoverFrom ",
new NVPair("turnoverFrom", formData.getTurnoverFrom()));
} else if (formData.getTurnoverTo().getValue() != null) {
havingFilter.addWhere(" <= :turnoverTo ",
new NVPair("turnoverTo", formData.getTurnoverTo()));
}
}
return havingFilter;
}
private String currentProjectsActualTurnover (int presentationType, Long partitionUid, SearchFilter whereActualTurnoverFilter) {
String table = null;
// top company report
if (presentationType == 1) {
table = " ACTUAL_TURNOVER_VIEW ";
}
else if (presentationType == 2) {
table = " ACTUAL_TURNOVER_VIEW_HOLDG ";
}
String sql = " (" +
"SELECT NVL(SUM(AT.ACTUAL_TURNOVER), 0) " +
"FROM " + table + " AT " +
"WHERE AT.COMPANY_NR = C.COMPANY_NR " +
whereActualTurnoverFilter.getWhere();
if (partitionUid != null)
sql += " AND AT.PARTITION_UID = " + partitionUid;
sql += ") ";
return sql;
}
private String contact (Long partitionUid, SearchFilter whereContactFilter) {
String sql = " (" +
"SELECT COUNT(1) FROM VISIT V, USER VU " +
"WHERE C.COMPANY_NR = V.COMPANY_NR " +
"AND V.STATUS_UID = " + ContactStatusCodeType.TakenPlaceCode.ID + " " +
"AND V.REG_USER_NR = VU.USER_NR " +
whereContactFilter.getWhere();
if (partitionUid != null)
sql += " AND VU.PARTITION_UID = " + partitionUid;
sql += ") ";
return sql;
}
private String organizations () {
return
" (SELECT 1 " +
" FROM COMPANY_TYPE CT " +
" WHERE CT.COMPANY_NR = C.COMPANY_NR " +
" AND CT.TYPE_UID = " + ProjectCompanyRelationCodeType.OrganizationCode.ID + ") ";
}
private String contactPersonOne (Long partitionUid) {
return
" (SELECT CP_ONE_P.LAST_NAME || DECODE(CP_ONE_P.FIRST_NAME, //, //, ', ' || CP_ONE_P.FIRST_NAME) " +
" FROM COMPANY_PERSON CP_ONE, PERSON CP_ONE_P " +
" WHERE CP_ONE.COMPANY_NR = C.COMPANY_NR " +
" AND CP_ONE.TYPE_UID = " + CompanyPersonRelationCodeType.ContactPersonOneCode.ID + " " +
" AND CP_ONE.PARTITION_UID = " + partitionUid + " " +
" AND CP_ONE.JOIN_TYPE_UID = " + PersonOrTeamCodeType.PersonCode.ID + " " +
" AND CP_ONE_P.PERSON_NR = CP_ONE.JOIN_NR) ";
}
private String prioSalesCouncil (SearchFilter filter) {
if (filter.getWhere().length() == 0) {
return " NVL(C.PRIO_SALES_COUNCIL_UID, 0) ";
} else {
return
" (SELECT NVL(X.FK2_NR, 0) " +
" FROM CHANGE_HISTORY X" +
" WHERE X.JOIN_NR = C.COMPANY_NR" +
" AND X.JOIN_TYPE_ID = " + CRMFunctionCodeType.CompanyCode.ID +
" AND X.PARTITION_UID = 0" +
" AND X.EVT_CHANGE IN (" +
" SELECT MAX(CH.EVT_CHANGE)" +
" FROM CHANGE_HISTORY CH" +
" WHERE CH.JOIN_NR = C.COMPANY_NR" +
" AND CH.JOIN_TYPE_ID = " + CRMFunctionCodeType.CompanyCode.ID +
" AND CH.PARTITION_UID = 0" +
filter.getWhere() + ")" +
" AND ROWNUM = 1) ";
}
}
private String companyClassification (SearchFilter filter, Long partitionUid) {
if (filter.getWhere().length() == 0) {
return
" (SELECT PHASE_UID " +
" FROM COMPANY_CLASSIFICATION X" +
" WHERE X.COMPANY_NR = C.COMPANY_NR" +
" AND X.PARTITION_UID = " + partitionUid + ") ";
} else {
return
" (SELECT FK_NR " +
" FROM CHANGE_HISTORY X " +
" WHERE X.JOIN_NR = C.COMPANY_NR" +
" AND X.JOIN_TYPE_ID = " + CRMFunctionCodeType.CompanyCode.ID +
" AND X.PARTITION_UID = " + partitionUid +
" AND X.EVT_CHANGE IN (" +
" SELECT MAX(CH.EVT_CHANGE)" +
" FROM CHANGE_HISTORY CH" +
" WHERE CH.JOIN_NR = C.COMPANY_NR" +
" AND CH.JOIN_TYPE_ID = " + CRMFunctionCodeType.CompanyCode.ID +
" AND CH.PARTITION_UID = " + partitionUid + " " +
filter.getWhere() + ")" +
" AND ROWNUM = 1) ";
}
}
private SearchFilter setFilterNewCustomer(CompanyTopReportSearchFormData formData, SearchFilter filter) {
if (formData.getNewCustomerFrom().getValue() != null
|| formData.getNewCustomerTo().getValue() != null) {
filter.addWhere(" AND C.COMPANY_NR IN (SELECT COMPANY_NR FROM (SELECT COMPANY_NR, MIN(PI.EVT_APPROVAL) AS EVT_APPROVAL FROM PROJECT PI WHERE ACTIVE = 1 GROUP BY COMPANY_NR) PINNER WHERE 1=1");
if (formData.getNewCustomerFrom().getValue() != null) {
filter.addWhere(" AND PINNER.EVT_APPROVAL >= TRUNC(:newCustomerFrom) ",
new NVPair("newCustomerFrom", formData.getNewCustomerFrom()));
}
if (formData.getNewCustomerTo().getValue() != null) {
filter.addWhere(" AND PINNER.EVT_APPROVAL < TRUNC(:newCustomerTo) + 1 ",
new NVPair("newCustomerTo", formData.getNewCustomerTo()));
}
filter.addWhere(" ) ");
}
return filter;
}
private SearchFilter setFilterZ1Contact(CompanyTopReportSearchFormData formData, SearchFilter filter) {
if (formData.getTurnoverDateFrom().getValue() != null) {
filter.addWhere(" AND V.EVT_START >= TRUNC(:contactsZ1From) ",
new NVPair("contactsZ1From", formData.getTurnoverDateFrom()));
}
if (formData.getTurnoverDateTo().getValue() != null) {
filter.addWhere(" AND V.EVT_START < TRUNC(:contactsZ1To+1) ",
new NVPair("contactsZ1To", formData.getTurnoverDateTo()));
}
return filter;
}
private SearchFilter setFilterZ2Contact(CompanyTopReportSearchFormData formData, SearchFilter filter) {
if (formData.getDataFrom().getValue() != null) {
filter.addWhere(" AND V.EVT_START >= TRUNC(:contactsZ2From) ",
new NVPair("contactsZ2From", formData.getDataFrom()));
}
if (formData.getDataTo().getValue() != null) {
filter.addWhere(" AND V.EVT_START < TRUNC(:contactsZ2To+1) ",
new NVPair("contactsZ2To", formData.getDataTo()));
}
return filter;
}
private SearchFilter setFilterCompany(CompanyTopReportSearchFormData formData, SearchFilter filter) {
if (formData.getCompanyShortName().getValue() != null) {
filter.addWhere(" AND UPPER(C.CODE_NAME) LIKE UPPER(:companyShortName || '%') ",
new NVPair("companyShortName", formData.getCompanyShortName()));
}
if (formData.getCompanyName().getValue() != null) {
filter.addWhere(" AND UPPER(C.NAME) LIKE UPPER(:companyName || '%') ",
new NVPair("companyName", formData.getCompanyName()));
}
if (formData.getCompanyNo().getValue() != null) {
filter.addWhere(" AND UPPER(C.COMPANY_NO) LIKE UPPER(:companyNo || '%') ",
new NVPair("companyNo", formData.getCompanyNo()));
}
if (formData.getContactPersonOne().getValue() != null) {
filter.addWhere(" AND C.COMPANY_NR IN (SELECT COMPANY_NR FROM COMPANY_PERSON WHERE JOIN_NR = :contactPersonOne"
+ " AND TYPE_UID = " + CompanyPersonRelationCodeType.ContactPersonOneCode.ID
+ " AND 1=DECODE(::level(ReadPersonPermission), ::level(BasicCrmPermission.LEVEL_ALL), 1, ::level(BasicCrmPermission.LEVEL_NONE), 0, MY_UTIL.PERSON_PRIV(::level(ReadPersonPermission), JOIN_NR, :userNr, :organisationUid))) ",
new NVPair("contactPersonOne", formData.getContactPersonOne()));
}
if (formData.getContactPersonTwo().getValue() != null) {
filter.addWhere(" AND C.COMPANY_NR IN (SELECT COMPANY_NR FROM COMPANY_PERSON WHERE JOIN_NR = :contactPersonTwo"
+ " AND TYPE_UID = " + CompanyPersonRelationCodeType.ContactPersonTwoCode.ID
+ " AND 1=DECODE(::level(ReadPersonPermission), ::level(BasicCrmPermission.LEVEL_ALL), 1, ::level(BasicCrmPermission.LEVEL_NONE), 0, MY_UTIL.PERSON_PRIV(::level(ReadPersonPermission), JOIN_NR, :userNr, :organisationUid))) ",
new NVPair("contactPersonTwo", formData.getContactPersonTwo()));
}
if (formData.getBusinessArea().getValue() != null) {
filter.addWhere(" AND C.COMPANY_NR IN (SELECT COMPANY_NR FROM COMPANY_CLASSIFICATION WHERE PARTITION_UID = :customerofBUs) ",
new NVPair("customerofBUs", formData.getBusinessArea()));
}
if (formData.getCompanySector().getValue() != null) {
filter.addWhere(" AND C.SECTOR_UID = :companySector ",
new NVPair("companySector", formData.getCompanySector()));
}
if (formData.getLegalForm().getValue() != null) {
filter.addWhere(" AND C.LEGAL_FORM_UID = :legalForm ",
new NVPair("legalForm", formData.getLegalForm()));
}
if (formData.getLanguage().getValue() != null) {
filter.addWhere(" AND C.LANGUAGE_UID = :language ",
new NVPair("language", formData.getLanguage()));
}
if (formData.getRankFrom().getValue() != null) {
filter.addWhere(" AND C.RANK >= :rankFrom ",
new NVPair("rankFrom", formData.getRankFrom()));
}
if (formData.getRankTo().getValue() != null) {
filter.addWhere(" AND C.RANK <= :rankTo ",
new NVPair("rankTo", formData.getRankTo()));
}
if (formData.getSource().getValue() != null) {
filter.addWhere(" AND UPPER(C.SOURCE) LIKE UPPER(:source || '%') ",
new NVPair("source", formData.getSource()));
}
if (formData.getCompanyState().getValue() != null) {
if (formData.getCompanyState().getValue() == 0) {
filter.addWhereToken(" C.ACTIVE = 0 ");
}
else if (formData.getCompanyState().getValue() == 1) {
filter.addWhereToken(" C.ACTIVE = 1 ");
}
}
return filter;
}
private SearchFilter setFilterZ1PhasePrioSalesCouncil(CompanyTopReportSearchFormData formData, SearchFilter filter, SearchFilter changeHistoryFilter) {
if (formData.getTurnoverDateTo().getValue() == null) {
if (formData.getPartition().getValue() != null) {
filter.addWhere(" AND EXISTS (" +
" SELECT 1" +
" FROM COMPANY_CLASSIFICATION CC" +
" WHERE CC.COMPANY_NR = C.COMPANY_NR" +
" AND CC.PARTITION_UID = :partition",
new NVPair("partition", formData.getPartition()));
// phase requires a partition
if (formData.getPhase().getValue() != null) {
filter.addWhere(" AND CC.PHASE_UID = :phase",
new NVPair("phase", formData.getPhase()));
}
filter.addWhere(") ");
}
if (formData.getPrioSalesCouncil().getValue() != null) {
filter.addWhere(" AND C.PRIO_SALES_COUNCIL_UID = :prio ",
new NVPair("prio", formData.getPrioSalesCouncil()));
}
} else {
if (formData.getPartition().getValue() != null) {
filter.addWhere(" AND EXISTS (" +
" SELECT 1" +
" FROM CHANGE_HISTORY X" +
" WHERE X.JOIN_NR = C.COMPANY_NR" +
" AND X.JOIN_TYPE_ID = " + CRMFunctionCodeType.CompanyCode.ID +
" AND X.PARTITION_UID = :partition" +
" AND X.EVT_CHANGE IN (" +
" SELECT MAX(CH.EVT_CHANGE)" +
" FROM CHANGE_HISTORY CH" +
" WHERE CH.JOIN_NR = C.COMPANY_NR" +
" AND CH.JOIN_TYPE_ID = " + CRMFunctionCodeType.CompanyCode.ID +
" AND CH.PARTITION_UID = :partition " +
changeHistoryFilter.getWhere() + ")",
new NVPair("partition", formData.getPartition()));
// since whereCompanyFilter now contains references to bind variables in wherechangeHistoryFilter:
filter.getBindMap().putAll(changeHistoryFilter.getBindMap());
// phase requires a partition
if (formData.getPhase().getValue() != null) {
filter.addWhere(" AND X.FK_NR = :phase",
new NVPair("phase", formData.getPhase()));
}
filter.addWhere(") ");
}
// prio sales council also uses the change history, but goes for rows with partition_uid = 0!
if (formData.getPrioSalesCouncil().getValue() != null) {
filter.addWhere(" AND EXISTS (" +
" SELECT 1" +
" FROM CHANGE_HISTORY X" +
" WHERE X.JOIN_NR = C.COMPANY_NR" +
" AND X.JOIN_TYPE_ID = " + CRMFunctionCodeType.CompanyCode.ID +
" AND X.PARTITION_UID = 0" +
" AND X.FK2_NR = :prio" +
" AND X.EVT_CHANGE IN (" +
" SELECT MAX(CH.EVT_CHANGE)" +
" FROM CHANGE_HISTORY CH" +
" WHERE CH.JOIN_NR = C.COMPANY_NR" +
" AND CH.JOIN_TYPE_ID = " + CRMFunctionCodeType.CompanyCode.ID +
" AND CH.PARTITION_UID = 0 " +
changeHistoryFilter.getWhere() + "))",
new NVPair("prio", formData.getPrioSalesCouncil()));
// since whereCompanyFilter now contains references to bind variables in wherechangeHistoryFilter:
filter.getBindMap().putAll(changeHistoryFilter.getBindMap());
}
}
return filter;
}
private SearchFilter setFilterZ2Phase(CompanyTopReportSearchFormData formData, SearchFilter filter, SearchFilter changeHistoryFilter) {
if (formData.getDataTo().getValue() == null) {
if (formData.getPartitionZ2().getValue() != null) {
filter.addWhere(" AND EXISTS (" +
" SELECT 1" +
" FROM COMPANY_CLASSIFICATION CC" +
" WHERE CC.COMPANY_NR = C.COMPANY_NR" +
" AND CC.PARTITION_UID = :partitionZ2",
new NVPair("partitionZ2", formData.getPartitionZ2()));
// phase requires a partition
if (formData.getPhase().getValue() != null) {
filter.addWhere(" AND CC.PHASE_UID = :phaseZ2",
new NVPair("phaseZ2", formData.getPhaseZ2()));
}
filter.addWhere(") ");
}
} else {
if (formData.getPartitionZ2().getValue() != null) {
filter.addWhere(" AND EXISTS (" +
" SELECT 1" +
" FROM CHANGE_HISTORY X" +
" WHERE X.JOIN_NR = C.COMPANY_NR" +
" AND X.JOIN_TYPE_ID = " + CRMFunctionCodeType.CompanyCode.ID +
" AND X.PARTITION_UID = :partitionZ2" +
" AND X.EVT_CHANGE IN (" +
" SELECT MAX(CH.EVT_CHANGE)" +
" FROM CHANGE_HISTORY CH" +
" WHERE CH.JOIN_NR = C.COMPANY_NR" +
" AND CH.JOIN_TYPE_ID = " + CRMFunctionCodeType.CompanyCode.ID +
" AND CH.PARTITION_UID = :partitionZ2 " +
changeHistoryFilter.getWhere() + ")",
new NVPair("partitionZ2", formData.getPartitionZ2()));
// since whereCompanyFilter now contains references to bind variables in wherechangeHistoryFilter:
filter.getBindMap().putAll(changeHistoryFilter.getBindMap());
// phase requires a partition
if (formData.getPhaseZ2().getValue() != null) {
filter.addWhere(" AND X.FK_NR = :phaseZ2",
new NVPair("phaseZ2", formData.getPhaseZ2()));
}
filter.addWhere(") ");
}
}
return filter;
}
private SearchFilter setFilterPlannedTurnoverDates(CompanyTopReportSearchFormData formData, SearchFilter filter) {
if (formData.getTurnoverDateFrom().getValue() != null) {
filter.addWhere(
" AND TRUNC(:turnoverFromDate) <= P1.EVT_REQUEST (+) ",
new NVPair("turnoverFromDate", formData.getTurnoverDateFrom()));
}
if (formData.getTurnoverDateTo().getValue() != null) {
filter.addWhere(
" AND ADD_MONTHS(TRUNC(:turnoverToDate, 'MONTH'), 1) > P1.EVT_REQUEST (+) ",
new NVPair("turnoverToDate", formData.getTurnoverDateTo()));
}
return filter;
}
private SearchFilter setFilterZ1ActualTurnover(CompanyTopReportSearchFormData formData, SearchFilter filter) {
if (formData.getTurnoverDateFrom().getValue() != null) {
filter.addWhere(
" AND TRUNC(:turnoverFromDate, 'MONTH') <= AT.MONTH ",
new NVPair("turnoverFromDate", formData.getTurnoverDateFrom()));
}
if (formData.getTurnoverDateTo().getValue() != null) {
filter.addWhere(
" AND ADD_MONTHS(TRUNC(:turnoverToDate, 'MONTH'), 1) > AT.MONTH ",
new NVPair("turnoverToDate", formData.getTurnoverDateTo()));
}
return filter;
}
private SearchFilter setFilterZ2ActualTurnover(CompanyTopReportSearchFormData formData, SearchFilter filter) {
if (formData.getDataFrom().getValue() != null) {
filter.addWhere(
" AND TRUNC(:dataFromDate, 'MONTH') <= AT.MONTH ",
new NVPair("dataFromDate", formData.getDataFrom()));
}
if (formData.getDataTo().getValue() != null) {
filter.addWhere(
" AND ADD_MONTHS(TRUNC(:dataToDate, 'MONTH'), 1) > AT.MONTH ",
new NVPair("dataToDate", formData.getDataTo()));
}
return filter;
}
private SearchFilter setFilterZ1Z2(CompanyTopReportSearchFormData formData, SearchFilter filter) {
// Timeframe Z1
filter.addWhere(
(formData.getTurnoverDateFrom().getValue() != null ?
" AND (TRUNC(:turnoverFromDate) <= P.EVT_REQUEST (+) " :
" AND (1=1 "),
new NVPair("turnoverFromDate", formData.getTurnoverDateFrom()));
if (formData.getTurnoverDateTo().getValue() != null) {
filter.addWhere(" AND TRUNC(:turnoverToDate + 1) > P.EVT_REQUEST (+) ",
new NVPair("turnoverToDate", formData.getTurnoverDateTo()));
}
// Timeframe Z2
filter.addWhere(
(formData.getDataFrom().getValue() != null ?
" OR TRUNC(:dataFromDate) <= P.EVT_REQUEST (+) " :
" OR 1=1"),
new NVPair("dataFromDate", formData.getDataFrom()));
if (formData.getDataTo().getValue() != null) {
filter.addWhere(" AND TRUNC(:dataToDate + 1) > P.EVT_REQUEST (+) ",
new NVPair("dataToDate", formData.getDataTo()));
}
filter.addWhere(") ");
return filter;
}
private SearchFilter setFilterZ2(CompanyTopReportSearchFormData formData, SearchFilter filter) {
if (formData.getDataFrom().getValue() != null) {
filter.addWhere(" AND TRUNC(:dataFromDate) <= P2.EVT_REQUEST (+) ",
new NVPair("dataFromDate", formData.getDataFrom()));
}
if (formData.getDataTo().getValue() != null) {
filter.addWhere(" AND TRUNC(:dataToDate + 1) > P2.EVT_REQUEST (+) ",
new NVPair("dataToDate", formData.getDataTo()));
}
return filter;
}
private SearchFilter setFilterZ1ChangeHistory(CompanyTopReportSearchFormData formData, SearchFilter filter) {
// note that this filter does not take a FROM part!
if (formData.getTurnoverDateTo().getValue() != null) {
filter.addWhere(" AND TRUNC(:changeToDate) + 1 > CH.EVT_CHANGE ",
new NVPair("changeToDate", formData.getTurnoverDateTo()));
}
return filter;
}
private SearchFilter setFilterZ2ChangeHistory(CompanyTopReportSearchFormData formData, SearchFilter filter) {
// note that this filter does not take a FROM part!
if (formData.getDataTo().getValue() != null) {
filter.addWhere(" AND TRUNC(:dataToDate) + 1 > CH.EVT_CHANGE ",
new NVPair("dataToDate", formData.getDataTo()));
}
return filter;
}
private Double calculateTopNPlannedTurnover(SearchFilter whereCompanyFilter, SearchFilter wherePlannedTurnoverFilter, Integer plannedTopN) throws ProcessingException {
// Selecting the company_nr for debugging purposes.
String sql=
"SELECT COMPANY_NR, PLANNED_TURNOVER " +
"FROM (" +
" SELECT C.COMPANY_NR, " +
" SUM(P1.TURNOVER_PER_YEAR) AS PLANNED_TURNOVER " +
" FROM COMPANY C, " +
" PROJECT P1 " +
" WHERE NOT EXISTS " + organizations() +
" AND C.COMPANY_NR != 0 " +
" AND C.COMPANY_NR = P1.COMPANY_NR " +
" AND (P1.PHASE_UID IN ( " +
ProjectPhaseCodeType.SuspectCode.ID + ", " + ProjectPhaseCodeType.ProspectCode.ID + ") " +
" OR P1.PHASE_UID IN (SELECT UC_UID FROM UC WHERE PARENT_UID IN ( " +
ProjectPhaseCodeType.SuspectCode.ID + ", " + ProjectPhaseCodeType.ProspectCode.ID + "))) " +
" AND P1.ACTIVE = 1 " +
" AND 1 = DECODE(::level(ReadCompanyPermission), ::level(BasicCrmPermission.LEVEL_ALL), 1, ::level(BasicCrmPermission.LEVEL_NONE), 0, MY_UTIL.COMPANY_PRIV(::level(ReadCompanyPermission), C.COMPANY_NR, :userNr, :organisationUid)) " +
" AND 1 = DECODE(::level(ReadProjectPermission), ::level(BasicCrmPermission.LEVEL_ALL), 1, ::level(BasicCrmPermission.LEVEL_NONE), 0, MY_UTIL.PROJECT_PRIV(::level(ReadProjectPermission), P1.PROJECT_NR, :userNr, :organisationUid)) " +
whereCompanyFilter.getWhere() +
wherePlannedTurnoverFilter.getWhere() +
"GROUP BY C.COMPANY_NR " +
"ORDER BY 2 DESC )" +
"WHERE ROWNUM <= :n";
Object[][] data = SQL.selectLimited(sql, plannedTopN,
whereCompanyFilter.getBindMap(),
wherePlannedTurnoverFilter.getBindMap(),
new NVPair("n", plannedTopN));
// If we get a result, add to the HAVING clause
Double result = 0.1;
if (data.length > 0){
result = NumberUtility.toDouble((Number) data[data.length - 1][1]);
if (result == null || result == 0) {
result = 0.1;
}
}
return result;
}
private Double calculateTopNActualTurnover(int presentationType, SearchFilter whereCompanyFilter, SearchFilter whereActualTurnoverFilter, Integer topN) throws ProcessingException {
String table = null;
if (presentationType == 1) {
table = " ACTUAL_TURNOVER_VIEW ";
}
else if (presentationType == 2) {
table = " ACTUAL_TURNOVER_VIEW_HOLDG ";
}
// Don't look at planned turnover (and therefore do not use the
// whereProjectFilter nor visits (VISIT V, USER VU).
// Selecting the company_nr for debugging purposes.
String sql=
"SELECT COMPANY_NR, ACTUAL_TURNOVER " +
"FROM (" +
" SELECT C.COMPANY_NR, SUM(AT.ACTUAL_TURNOVER) AS ACTUAL_TURNOVER " +
" FROM COMPANY C, " +
table + " AT " +
" WHERE C.COMPANY_NR = AT.COMPANY_NR " +
" AND NOT EXISTS " + organizations() +
" AND C.COMPANY_NR != 0 " +
" AND AT.ACTUAL_TURNOVER IS NOT NULL " +
" AND 1 = DECODE(::level(ReadCompanyPermission), ::level(BasicCrmPermission.LEVEL_ALL), 1, ::level(BasicCrmPermission.LEVEL_NONE), 0, MY_UTIL.COMPANY_PRIV(::level(ReadCompanyPermission), C.COMPANY_NR, :userNr, :organisationUid)) " +
whereCompanyFilter.getWhere() +
whereActualTurnoverFilter.getWhere() +
" GROUP BY C.COMPANY_NR " +
" ORDER BY 2 DESC )" +
"WHERE ROWNUM <= :n";
Object[][] data = SQL.selectLimited(sql, topN,
whereCompanyFilter.getBindMap(),
whereActualTurnoverFilter.getBindMap(),
new NVPair("n", topN));
// If we get a result, recompute the HAVING clause. If you made any modifications to the HAVING
// clause in the code above, these will be lost now. Don't do it.
Double result = 0.1;
if (data.length > 0){
result = NumberUtility.toDouble((Number) data[data.length - 1][1]);
if (result == null || result == 0) {
result = 0.1;
}
}
return result;
}
/**
* Top 10 Companies Report.
* The SELECT statement for this report is big. Very big.
*
* When changing the column order in the SQL statement,
* please do not forget to adapt the column numbers in the methods
* getCompanyTopReportExcel().
*/
public Object[][] getCompanyTopReportTableData(int presentationType, CompanyTopReportSearchFormData formData, SearchFilter extendedSearchFilter) throws ProcessingException {
// top company report
if (presentationType == 1) {
if (ACCESS.getLevel(new ReadCompanyTopReportPermission()) <= BasicCrmPermission.LEVEL_NONE) {
throw new VetoException("NotAllowedToSeeThisData");
}
}
// top holding report
else if (presentationType == 2) {
if (ACCESS.getLevel(new ReadHoldingTopReportPermission()) <= BasicCrmPermission.LEVEL_NONE) {
throw new VetoException("NotAllowedToSeeThisData");
}
}
String headsOnlyClause = "";
if (presentationType == 2) {
headsOnlyClause = "" +
" AND NOT EXISTS (SELECT 1 " +
" FROM COMPANY_COMPANY CC " +
" WHERE CC.TYPE_UID = " + CompanyCompanyRelationCodeType.HoldingCode.ID + " " +
" AND CC.COMPANY_NR = C.COMPANY_NR)";
}
SearchFilter whereCompanyFilter = new SearchFilter(); // C
SearchFilter whereZ1ActualTurnover = new SearchFilter(); // AT
SearchFilter whereZ2ActualTurnover = new SearchFilter(); // AT
SearchFilter wherePlannedTurnoverFilter = new SearchFilter(); // P
SearchFilter whereZ1ChangeHistoryFilter = new SearchFilter(); // CH
SearchFilter whereZ2ChangeHistoryFilter = new SearchFilter(); // CH
SearchFilter whereZ1ContactFilter = new SearchFilter(); // V
SearchFilter whereZ2ContactFilter = new SearchFilter(); // V
SearchFilter whereZ2 = new SearchFilter();
SearchFilter whereZ1Z2 = new SearchFilter();
whereCompanyFilter = setFilterNewCustomer(formData, whereCompanyFilter);
whereZ1ContactFilter = setFilterZ1Contact(formData, whereZ1ContactFilter);
whereZ2ContactFilter = setFilterZ2Contact(formData, whereZ2ContactFilter);
whereCompanyFilter = setFilterCompany(formData, whereCompanyFilter);
wherePlannedTurnoverFilter = setFilterPlannedTurnoverDates(formData, wherePlannedTurnoverFilter);
whereZ1ActualTurnover = setFilterZ1ActualTurnover(formData, whereZ1ActualTurnover);
whereZ2ActualTurnover = setFilterZ2ActualTurnover(formData, whereZ2ActualTurnover);
whereZ1Z2 = setFilterZ1Z2(formData, whereZ1Z2);
whereZ2 = setFilterZ2(formData, whereZ2);
whereZ1ChangeHistoryFilter = setFilterZ1ChangeHistory(formData, whereZ1ChangeHistoryFilter);
whereZ2ChangeHistoryFilter = setFilterZ2ChangeHistory(formData, whereZ2ChangeHistoryFilter);
whereCompanyFilter = setFilterZ1PhasePrioSalesCouncil(formData, whereCompanyFilter, whereZ1ChangeHistoryFilter);
whereCompanyFilter = setFilterZ2Phase(formData, whereCompanyFilter, whereZ2ChangeHistoryFilter);
// initialize having filter if only getTurnoverFrom or getTurnoverTo has been provided
SearchFilter havingFilter = getCompanyTopReportHavingFilter(
formData, currentProjectsActualTurnover(presentationType, null, whereZ1ActualTurnover));
Integer topN = formData.getHighestTurnoverCompanies().getValue();
if (topN != null) {
// We need to compute the turnovers for all companies before continuing...
// This is why we need to set all the filters before coming here.
// This is an excellent opportunity for caching the results in the DB!
Double result = calculateTopNActualTurnover(presentationType, whereCompanyFilter, whereZ1ActualTurnover, topN);
if (formData.getTurnoverFrom().getValue() == null
|| formData.getTurnoverFrom().getValue() < result) {
formData.getTurnoverFrom().setValue(result);
havingFilter = getCompanyTopReportHavingFilter(
formData, currentProjectsActualTurnover(presentationType, null, whereZ1ActualTurnover));
}
}
String sql =
"WITH PROJECT_PHASE AS " +
" (SELECT UC_UID " +
" FROM UC " +
" WHERE (UC_UID IN (" + ProjectPhaseCodeType.IntegrationCode.ID + ", " + ProjectPhaseCodeType.SignedCustomerCode.ID + ") OR PARENT_UID IN (" + ProjectPhaseCodeType.IntegrationCode.ID + ", " + ProjectPhaseCodeType.SignedCustomerCode.ID + ")) " +
" AND CODE_TYPE = " + ProjectPhaseCodeType.ID + ") " +
"SELECT /*+ INDEX(A ADDRESSXPK) INDEX(C COMPANYXPK) INDEX(CA COMPANY_ADDRESSXIF36) INDEX(CA COMPANY_ADDRESSXPK) */ " +
" C.COMPANY_NR, " +
" C.CODE_NAME \"Firmenkurzname\", " +
" C.NAME \"Firmenname\", " +
" C.SECTOR_UID \"Branche\", " +
" C.COMPANY_NO \"CRM Firma Nr\", " +
" C.RANK \"Rank\", " +
prioSalesCouncil(whereZ1ChangeHistoryFilter) + " \"Prio Sales Council Z1\", " +
prioSalesCouncil(whereZ2ChangeHistoryFilter) + " \"Prio Sales Council Z2\", " +
currentProjectsActualTurnover(presentationType, null, whereZ1ActualTurnover) + " \"Laufende Projekte Z1 [Umsatz]\", " +
currentProjectsActualTurnover(presentationType, null, whereZ2ActualTurnover) + " \"Laufende Projekte Z2 [Umsatz]\", " +
companyClassification(whereZ1ChangeHistoryFilter, DepartmentCodeType.CodeA.ID) + " \"A Firmenphase Z1\", " +
currentProjectsActualTurnover(presentationType, DepartmentCodeType.CodeA.ID, whereZ1ActualTurnover) + " \"A Laufende Z1 [Umsatz]\", " +
companyClassification(whereZ2ChangeHistoryFilter, DepartmentCodeType.CodeA.ID) + " \"A Firmenphase Z2\", " +
currentProjectsActualTurnover(presentationType, DepartmentCodeType.CodeA.ID, whereZ2ActualTurnover) + " \"A Laufende Z2 [Umsatz]\", " +
contactPersonOne(DepartmentCodeType.CodeA.ID) + " \"Ansprechpartner 1 A\", " +
companyClassification(whereZ1ChangeHistoryFilter, DepartmentCodeType.CodeB.ID) + " \"B Firmenphase Z1\", " +
currentProjectsActualTurnover(presentationType, DepartmentCodeType.CodeB.ID, whereZ1ActualTurnover) + " \"B Laufende Z1 [Umsatz]\", " +
companyClassification(whereZ2ChangeHistoryFilter, DepartmentCodeType.CodeB.ID) + " \"B Firmenphase Z2\", " +
currentProjectsActualTurnover(presentationType, DepartmentCodeType.CodeB.ID, whereZ2ActualTurnover) + " \"B Laufende Z2 [Umsatz]\", " +
contactPersonOne(DepartmentCodeType.CodeB.ID) + " \"Ansprechpartner 1 B\", " +
companyClassification(whereZ1ChangeHistoryFilter, DepartmentCodeType.CodeC.ID) + " \"C Firmenphase Z1\", " +
currentProjectsActualTurnover(presentationType, DepartmentCodeType.CodeC.ID, whereZ1ActualTurnover) + " \"C Laufende Z1 [Umsatz]\", " +
companyClassification(whereZ2ChangeHistoryFilter, DepartmentCodeType.CodeC.ID) + " \"C Firmenphase Z2\", " +
currentProjectsActualTurnover(presentationType, DepartmentCodeType.CodeC.ID, whereZ2ActualTurnover) + " \"C Laufende Z2 [Umsatz]\", " +
contactPersonOne(DepartmentCodeType.CodeC.ID) + " \"Ansprechpartner 1 C\", " +
companyClassification(whereZ1ChangeHistoryFilter, DepartmentCodeType.CodeD.ID) + " \"D Firmenphase Z1\", " +
currentProjectsActualTurnover(presentationType, DepartmentCodeType.CodeD.ID, whereZ1ActualTurnover) + " \"D Laufende Z1 [Umsatz]\", " +
companyClassification(whereZ2ChangeHistoryFilter, DepartmentCodeType.CodeD.ID) + " \"D Firmenphase Z2\", " +
currentProjectsActualTurnover(presentationType, DepartmentCodeType.CodeD.ID, whereZ2ActualTurnover) + " \"D Laufende Z2 [Umsatz]\", " +
contactPersonOne(DepartmentCodeType.CodeD.ID) + " \"Ansprechpartner 1 D\", " +
companyClassification(whereZ1ChangeHistoryFilter, DepartmentCodeType.CodeE.ID) + " \"E Firmenphase Z1\", " +
currentProjectsActualTurnover(presentationType, DepartmentCodeType.CodeE.ID, whereZ1ActualTurnover) + " \"E Laufende Z1 [Umsatz]\", " +
companyClassification(whereZ2ChangeHistoryFilter, DepartmentCodeType.CodeE.ID) + " \"E Firmenphase Z2\", " +
currentProjectsActualTurnover(presentationType, DepartmentCodeType.CodeE.ID, whereZ2ActualTurnover) + " \"E Laufende Z2 [Umsatz]\", " +
contactPersonOne(DepartmentCodeType.CodeE.ID) + " \"Ansprechpartner 1 E\", " +
companyClassification(whereZ1ChangeHistoryFilter, DepartmentCodeType.CodeF.ID) + " \"F Firmenphase Z1\", " +
currentProjectsActualTurnover(presentationType, DepartmentCodeType.CodeF.ID, whereZ1ActualTurnover) + " \"F Laufende Z1 [Umsatz]\", " +
companyClassification(whereZ2ChangeHistoryFilter, DepartmentCodeType.CodeF.ID) + " \"F Firmenphase Z2\", " +
currentProjectsActualTurnover(presentationType, DepartmentCodeType.CodeF.ID, whereZ2ActualTurnover) + " \"F Laufende Z2 [Umsatz]\", " +
contactPersonOne(DepartmentCodeType.CodeF.ID) + " \"Ansprechpartner 1 F\", " +
companyClassification(whereZ1ChangeHistoryFilter, DepartmentCodeType.CodeG.ID) + " \"G Firmenphase Z1\", " +
currentProjectsActualTurnover(presentationType, DepartmentCodeType.CodeG.ID, whereZ1ActualTurnover) + " \"G Laufende Z1 [Umsatz]\", " +
companyClassification(whereZ2ChangeHistoryFilter, DepartmentCodeType.CodeG.ID) + " \"G Firmenphase Z2\", " +
currentProjectsActualTurnover(presentationType, DepartmentCodeType.CodeG.ID, whereZ2ActualTurnover) + " \"G Laufende Z2 [Umsatz]\", " +
contactPersonOne(DepartmentCodeType.CodeG.ID) + " \"Ansprechpartner 1 G\", " +
companyClassification(whereZ1ChangeHistoryFilter, DepartmentCodeType.CodeH.ID) + " \"H Firmenphase Z1\", " +
currentProjectsActualTurnover(presentationType, DepartmentCodeType.CodeH.ID, whereZ1ActualTurnover) + " \"H Laufende Z1 [Umsatz]\", " +
companyClassification(whereZ2ChangeHistoryFilter, DepartmentCodeType.CodeH.ID) + " \"H Firmenphase Z2\", " +
currentProjectsActualTurnover(presentationType, DepartmentCodeType.CodeH.ID, whereZ2ActualTurnover) + " \"H Laufende Z2 [Umsatz]\", " +
contactPersonOne(DepartmentCodeType.CodeH.ID) + " \"Ansprechpartner 1 H\", " +
companyClassification(whereZ1ChangeHistoryFilter, DepartmentCodeType.CodeI.ID) + " \"I Firmenphase Z1\", " +
currentProjectsActualTurnover(presentationType, DepartmentCodeType.CodeI.ID, whereZ1ActualTurnover) + " \"I Laufende Z1 [Umsatz]\", " +
companyClassification(whereZ2ChangeHistoryFilter, DepartmentCodeType.CodeI.ID) + " \"I Firmenphase Z2\", " +
currentProjectsActualTurnover(presentationType, DepartmentCodeType.CodeI.ID, whereZ2ActualTurnover) + " \"I Laufende Z2 [Umsatz]\", " +
contactPersonOne(DepartmentCodeType.CodeI.ID) + " \"Ansprechpartner 1 I\", " +
" C.LANGUAGE_UID \"Sprache\", " +
" CA.PHONE \"Telefon\", " +
" CA.FAX \"Fax\", " +
" CA.WWW \"www\", " +
" CA.ADD_NAME \"Zusatzname\", " +
" CA.STREET || ' ' || CA.HOUSE_NO \"Strasse + Hausnummer\", " +
" CA.PO_BOX \"Postfach\", " +
" A.ZIP_CODE \"PLZ\", " +
" A.CITY \"Ort\", " +
" A.COUNTRY_UID \"Land\", " +
" C.NOTES \"Bemerkungen\", " +
" UTIL.GET_USER_CODE(C.ACTIVE + 10, NVL(:languageUid,246), :partitionUid) \"Aktiv\" " +
"FROM " +
" COMPANY C, " +
" COMPANY_ADDRESS CA, " +
" ADDRESS A, "+
" PROJECT P, " +
" PROJECT P1, " +
" PROJECT P2, " +
" PROJECT_PHASE P1_PP, " +
" PROJECT_PHASE P2_PP " +
"WHERE 1=1 " +
headsOnlyClause +
"AND NOT EXISTS " +
" (SELECT 1 " +
" FROM COMPANY_TYPE CT " +
" WHERE C.COMPANY_NR = CT.COMPANY_NR AND CT.TYPE_UID = " + ProjectCompanyRelationCodeType.OrganizationCode.ID + ") " +
"AND C.COMPANY_NR != 0 " +
"AND C.COMPANY_NR = CA.COMPANY_NR (+) " +
"AND CA.TYPE_UID (+) = " + CompanyAddressTypeCodeType.PrimaryAddressCode.ID + " " +
"AND CA.ADDRESS_NR = A.ADDRESS_NR(+) " +
"AND C.COMPANY_NR = P.COMPANY_NR (+) " +
"AND P.PROJECT_NR = P1.PROJECT_NR (+) " +
"AND P.PROJECT_NR = P2.PROJECT_NR (+) " +
"AND P1.PHASE_UID = P1_PP.UC_UID (+) " +
"AND P2.PHASE_UID = P2_PP.UC_UID (+) " +
"AND 1 = P.ACTIVE (+) " +
"AND 1 = DECODE(::level(ReadCompanyPermission), ::level(BasicCrmPermission.LEVEL_ALL), 1, ::level(BasicCrmPermission.LEVEL_NONE), 0, MY_UTIL.COMPANY_PRIV(::level(ReadCompanyPermission), C.COMPANY_NR, :userNr, :organisationUid)) " +
whereCompanyFilter.getWhere() +
wherePlannedTurnoverFilter.getWhere() +
whereZ2.getWhere() +
extendedSearchFilter.getWhere() +
" GROUP BY C.CODE_NAME, " +
" C.NAME, " +
" C.SECTOR_UID, " +
" C.COMPANY_NR, " +
" C.COMPANY_NO, " +
" C.RANK, " +
" C.PRIO_SALES_COUNCIL_UID, " +
" C.SECTOR_UID, " +
" C.LANGUAGE_UID, " +
" CA.PHONE, " +
" CA.FAX, " +
" CA.WWW, " +
" CA.ADD_NAME, " +
" CA.STREET, CA.HOUSE_NO, " +
" CA.PO_BOX, " +
" A.ZIP_CODE, " +
" A.CITY, " +
" A.COUNTRY_UID, " +
" C.NOTES, " +
" C.ACTIVE " +
"HAVING 1=1 " +
havingFilter.getWhere() +
"ORDER BY 2, 3, 4 ";
return SQL.select(sql,
whereCompanyFilter.getBindMap(),
whereZ1ActualTurnover.getBindMap(),
whereZ2.getBindMap(),
wherePlannedTurnoverFilter.getBindMap(),
whereZ1ContactFilter.getBindMap(),
whereZ2ContactFilter.getBindMap(),
whereZ1ChangeHistoryFilter.getBindMap(),
whereZ2ChangeHistoryFilter.getBindMap(),
havingFilter.getBindMap(),
extendedSearchFilter.getBindMap());
}
#Software