Diary SiteMap RecentChanges About Contact Calendar

Search:

Matching Pages:

Software

RSS Feed This page lists only the software pages. SoftwareUsed might be another page of interest. (But it’s rarely updated!)

2014-04-07 Homebrew Problems

I’ve been using Homebrew on our Mac for a while now. I like it very much. I’ve recently run into an issue, though. Sending emails from Emacs stopped working. I was getting was this:

Opening STARTTLS connection to `fencepost.gnu.org:25'...failed
network-stream-get-response: Wrong type argument: processp, nil

The important setting to remember to learn more is this:

(setq smtpmail-debug-info t)

Resent the mail and switch to a buffer called *trace of SMTP session to fencepost.gnu.org*.

There:

220 fencepost.gnu.org ESMTP Exim 4.71 Mon, 07 Apr 2014 01:00:52 -0400
250-fencepost.gnu.org Hello 178-83-163-103.dynamic.hispeed.ch [178.83.163.103]
250-SIZE 52428800
250-PIPELINING
250-AUTH CRAM-MD5
250-STARTTLS
250 HELP

Process smtpmail deleted
dyld: Library not loaded: /usr/local/lib/libnettle.4.7.dylib
  Referenced from: /usr/local/bin/gnutls-cli
  Reason: image not found

Process smtpmail trace/BPT trap: 5

Oops, it seems that I uninstalled something that gnutls required!

Time to check things:

alex@Megabombus:/usr/local/bin (master)$ brew update
...
alex@Megabombus:/usr/local/bin (master)$ brew upgrade nettle
Error: nettle-2.7.1 already installed
alex@Megabombus:/usr/local/bin (master)$ brew upgrade gnutls
Error: gnutls-3.2.12.1 already installed
alex@Megabombus:/usr/local/bin (master)$ ls /usr/local/lib/libnettle.*
/usr/local/lib/libnettle.4.5.dylib /usr/local/lib/libnettle.a
/usr/local/lib/libnettle.4.dylib   /usr/local/lib/libnettle.dylib
alex@Megabombus:/usr/local/bin (master)$ brew reinstall gnutls
==> Reinstalling gnutls
==> Downloading https://downloads.sf.net/project/machomebrew/Bottles/gnutls-3.2.
Already downloaded: /Library/Caches/Homebrew/gnutls-3.2.12.1.mavericks.bottle.tar.gz
==> Pouring gnutls-3.2.12.1.mavericks.bottle.tar.gz
🍺  /usr/local/Cellar/gnutls/3.2.12.1: 931 files, 8,4M
alex@Megabombus:/usr/local/bin (master)$ brew reinstall nettle
==> Reinstalling nettle
Warning: nettle-2.7.1 already installed, it's just not linked
alex@Megabombus:/usr/local/bin (master)$ brew link  nettle
Linking /usr/local/Cellar/nettle/2.7.1... 16 symlinks created
alex@Megabombus:/usr/local/bin (master)$ gnutls-cli --version
gnutls-cli 3.2.12
Copyright (C) 2000-2014 Free Software Foundation, and others, all rights reserved.
This is free software. It is licensed for use, modification and
redistribution under the terms of the GNU General Public License,
version 3 or later <http://gnu.org/licenses/gpl.html>


Please send bug reports to:  <bugs@gnutls.org>

That seems to have fixed it!

I wonder how this happened in the first place.

Tags: RSS RSS

Add Comment

2014-04-03 Eich and Mozilla

I use Firefox on my desktop both at work and at home. I don’t want Safari, Chrome or Internet Exploder. These days, the Mozilla foundation hired a dude called Eich who donated “$1,000 to the Proposition 8 effort to ban gay marriage in California.” See Exclusive: Mozilla CEO Eich says gay-marriage firestorm could hurt Firefox (Q&A) for details.

I had a conversation ranting monologue on IRC which revolved around two questions: How homophobic is Eich? Does “boycotting” a free product hurts him or the product?

My point was that I would like to tell Mozilla that having a homophobic boss is despicable. Writing a private email to let them know is not enough! Thus we enter the realm of politics where the actual boycott is not as important as news outlets writing about it. Let the scandal get some publicity.

In the interview, “Eich refuses to discuss his own beliefs explicitly or say whether they’ve changed…” I’m assuming they did not change much.

The article also says “Eich is the inventor of JavaScript…” A lesser sort of evil, I’d say! ;)

I think his argument is full of #fail. “If Mozilla cannot continue to operate according to its principles of inclusiveness…” and mentioning anti-gay marriage coders in Indonesia. How is that an argument? Does Mozilla also work with Hitler? (Just to cut that line of argument short.)

Karl Popper argued that you cannot be tolerant towards the intolerant. Actually, according to WikiQuote he said: “If we extend unlimited tolerance even to those who are intolerant, if we are not prepared to defend a tolerant society against the onslaught of the intolerant, then the tolerant will be destroyed, and tolerance with them.”

I think that’s why it’s not important for me to hurt Eich personally. After all, he’s free to say what he wants and to sponsor what he wants. I have limited tolerance. It’s important for me to tell Mozilla that it’s not OK to hire such a person!. I don’t have unlimited tolerance.

Anyway, here’s to not boycotting Firefox and still helping the scandal get some publicity.

Update: Mozilla CEO Brendan Eich Resigns After Protests from Gay Marriage Supporters.

Tags: RSS RSS RSS

Comments on 2014-04-03 Eich and Mozilla


AlexSchroeder
Similar thoughts by Jörgen Schäfer on Google+.

A different perspective is offered on this blog: according to the community participation guidelines “Mozillians should treat this as a private matter, not a Mozilla issue.” I don’t feel that I need to be bound by these guidelines. I think it was the ethical thing to do. If you sell youself based on your values, as Jörgen Schäfer writes, then you had better make sure that the public faces of your company reflect these values.

AlexSchroeder 2014-04-05

Add Comment

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…

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

Tags: RSS

Add Comment

2013-05-07 WMA to MP3

This is based on How to Convert Unprotected WMA Files Into MP3 Files. I’m leaving the instructions here for my future self. :)

for file in *.wma; do
  mplayer -vo null -vc dummy -af resample=44100 \
    -ao pcm:file="${file%%.[Ww][Mm][Aa]}.wav" "$file"
  lame --preset standard "${file%%.[Ww][Mm][Aa]}.wav" \
    "${file%%.[Ww][Mm][Aa]}.mp3"
done 

Use locate lame|grep 'lame$' and locate mplayer|grep 'mplayer$' to find the location of your executables. On a Mac, they’re probably not on your PATH… I installed MPlayer OSX and iTunes-LAME to get the executables. You’ll need to do something similar.

This is the result I just used for some files with upper case file extension:

for file in *.WMA; do
  "/Applications/MPlayer OSX.app/Contents/Resources/External_Binaries/mplayer_intel.app/Contents/MacOS/mplayer" \
    -vo null -vc dummy -af resample=44100 \
    -ao pcm:file="${file%%.[Ww][Mm][Aa]}.wav" "$file"
  "/Applications/iTunes-LAME.app/Contents/Resources/lame" \
    --preset standard "${file%%.[Ww][Mm][Aa]}.wav" \
    "${file%%.[Ww][Mm][Aa]}.mp3"
done 

Tags: RSS RSS RSS

Add Comment

2013-03-01 Text Mapper Progress

I’m a big fan of the Forgotten Depths. I wanted to use its map as the goal for my Text Mapper. As the mapper now allows the inclusion of other files, I’ve saved my library of colors and icons as default.txt and I’ve saved the map as forgotten-depths.txt. That’s why all you need to do now is include the map:

Forgotten Depths SVG

If you see white hexes, then I haven’t added any definitions for them, yet.

Tags: RSS RSS RSS RSS

Comments on 2013-03-01 Text Mapper Progress

Wow, this looks great! I think I’ll need to re-map parts of the Mystara campaign … I may have some time on Sunday.

– Harald 2013-03-01 10:56 UTC



AlexSchroeder
Too bad creating new icons takes so damn long. Also, two line labels would be nice. And big, diagonal labels to label areas. “Mirkwood” or something like it. Nice coastlines, roads, rivers…

Regarding roads and rivers: I’m discovering that Bézier curves need a bit more effort since simply doing a quadratic curveto (’T’ in the SVG spec) the hex midpoints isn’t working very well. I will need to rediscover my trigonometry basics!

AlexSchroeder 2013-03-01 12:00 UTC

Add Comment

2013-02-27 SVG Filters

I need help with SVG filters. This question is also on StackOverflow.

This is what I want to achieve:

https://farm9.staticflickr.com/8507/8511938807_5a793d726a.jpg

Previously, I’d duplicate the text element, make the background white, and blur it:

  <defs>
    <filter id="label-glow">
      <feGaussianBlur stdDeviation="1" />
    </filter>
  </defs>
  <text stroke="white" stroke-width="5" filter="url(#label-glow)">Harald's Repose</text>
  <text>Harald's Repose</text>

I’m trying not to duplicate the text element. Here’s how to do it using filters:

  <defs>
    <filter id="label-glow">
      <feFlood flood-color="white"/>
      <feComposite in2="SourceGraphic" operator="in"/>
      <feGaussianBlur stdDeviation="2"/>
      <feComposite operator="over"/>
      <feComposite operator="over"/>
      <feComposite operator="over"/>
      <feComposite operator="over"/>
      <feComposite in="SourceGraphic"/>
    </filter>
  </defs>
  <text filter="url(#label-glow)">Harald's Repose</text>

Unfortunately the output of the Gaussian blur is very weak which is why I need to overlay it multiple times. What am I missing?

Update: As I’ve been experimenting with it, I’ve decided to drop filters altogether. When using Firefox and printing the map to PDF, the filters all result in bitmap elements that don’t scale well.

Tags: RSS RSS

Add Comment

2013-02-26 Text Mapper

Recently I wanted to create a little hex map and I wanted to do it on an iPad, I wanted to eventually integrate it into my campaign wiki, I wanted it to be SVG based, and so on. All that meant, Hexographer wasn’t the appropriate tool. I needed to cook something up myself. Based on code I wrote nearly five years ago—the Old School Hex Mapper—I wrote up a little something.

I’m calling it the Text Mapper because it takes text input. No interactive fiddling with the map.

There are various sections of the input.

Map

This is simple. Coordinates, terrain.

# map definition
0101 mountain
0102 mountain
0103 hill
0104 forest
0201 mountain
0202 hill
0203 coast
0204 empty
0301 mountain
0302 mountain
0303 plain
0304 sea
0401 hill
0402 sand
0403 forest

The rest of the input determines how things look: what colors to use, what icons to display. When you start the Text Mapper, the input area already contains an example. Start with that before doing anything else.

Terrain attributes

Now it gets tricky. For every terrain we used, the script will generate a hex. We need to provide some SVG attributes! Need documentation? Check out the polygon in the SVG specification and click on show next to presentation attributes. That’s what you should be looking at.

# attributes
empty attributes fill="#ffffff" stroke="black" stroke-width="3"
plain attributes fill="#7cfc00" stroke="black" stroke-width="3"
forest attributes fill="#228b22" stroke="black" stroke-width="3"
hill attributes fill="#daa520" stroke="black" stroke-width="3"
mountain attributes fill="#708090" stroke="black" stroke-width="3"
sand attributes fill="#eedd82" stroke="black" stroke-width="3"
coast attributes fill="#7fffd4" stroke="black" stroke-width="3"
sea attributes fill="#4169e1" stroke="black" stroke-width="3"

Icons

In order to add little icons, you can specify a path per terrain.

hill path M -42,11 C -38,5 -34,0 -28,-3 C -20,-6 -11,-5 -5,-0 C -2,2 1,6 3,9 C 4,12 2,13 0,14 C -3,9 -7,5 -13,2 C -21,-1 -30,0 -36,6 C -38,9 -40,11 -43,14 C -43,15 -44,14 -44,13 C -43,12 -43,12 -42,11 z M -5,-0 C 0,-6 7,-12 15,-16 C 21,-18 28,-17 33,-14 C 39,-11 41,-5 43,-0 C 42,2 41,5 39,2 C 37,-2 33,-8 27,-10 C 20,-13 12,-12 6,-7 C 2,-4 -1,-1 -4,1 C -7,4 -6,0 -5,-0 z

plain path M -18,-13 C -13,-6 -13,4 -8,12 C -11,14 -15,21 -18,26 C -20,17 -22,4 -28,0 C -26,-4 -21,-9 -18,-13 z M 5,-31 C 4,-19 3,-6 6,5 C 1,10 -0,14 -3,19 C -2,6 -3,-4 -4,-16 C -4,-21 2,-26 5,-31 z M 26,-1 C 16,6 19,5 9,18 C 12,3 21,-8 34,-17 C 32,-12 29,-6 27,-1 z

If you don’t want this path to be black, you need to specify attributes for the path element.

plain path attributes fill="#76ee00"
hill path attributes fill="#b8860b"

And finally, the coordinates are simple text elements.

text font-size="20pt" dy="15px"

If you wanted a black and white map, for example, you could use different strokes…

empty attributes fill="white" stroke="#b3b3ff" stroke-width="3"
mountain attributes fill="white" stroke="#b3b3ff" stroke-width="3"
hill attributes fill="white" stroke="#b3b3ff" stroke-width="3"
forest attributes fill="white" stroke="#b3b3ff" stroke-width="3"

If you want to create new path elements yourself in Inkscape, you should draw them in a rectangle from (-100,-100) to (100,100) and extract the the path info from the SVG. Yeah, adding new icons isn’t easy.

I need to add more icons. I’d also like to add multiple icons for the same terrain such that the code will pick one at random.

I wonder how easy it’s be to add text labels, roads, rivers and borders. :)

The result of the above:

Example map

(Also on GitHub.)

Tags: RSS RSS RSS RSS RSS

Add Comment

2013-01-08 Online Photos

In 2012, I basically took pictures using Instagram, shared them with Facebook (part of the family), Twitter (another part of the family), Flickr (my favorite), and I used IFTTT to send a copy to Picasa Web because I felt that I’d like to have some pictures for people to see on Google+.

But then, when Instagram changed its terms of service (they rephrased them somewhat) and I decided that I might want to look into using something else for a bit.

I liked the new Flickr app somewhat.

I tried organizing my pictures on Picasa Web but was unable to create a new empty album in order to move some of my pictures in the Scrapbook album. When I selected more than 300 of the pictures and tried to move them, it also told me I could not move more than 100 at a time. I was confused. It looked like the user interface could use some improvements. I even started up my local Picasa copy and wasn’t clear on how to use it to manage my albums online. Perhaps you can’t. I was not impressed. It looks as if I’m not going to abandon iPhoto any time soon. Then again, I really want to abandon iPhoto one of these days because I hate it’s opaque storage regime.

Also, the lens or the protective plastic cover of my iPhone camera is scratched. The photos from the main camera now all look hazy. The terrible quality had resulted in me taking a lot of self-portraits from the second camera.

Since I didn’t want to use Instagram too often, I have uploaded the pictures to Flickr directly. This means that I haven’t posted any pictures to Facebook; I haven’t posted any pictures to Twitter, and none where uploaded to Picasa Web.

Strangely enough it doesn’t bother me.

Maybe it bothers me a bit because I’ve been using simple photos of my daily life as a replacement for status updates for my family and friends. We’ll see. Perhaps I’ll post status updates again. Or I’ll abandon Facebook. Or resume the use of Instagram.

http://farm9.staticflickr.com/8324/8361623336_2a0c20b3ac_m.jpg http://farm9.staticflickr.com/8046/8349494004_a65d7c1f1e_n.jpg http://farm9.staticflickr.com/8498/8344704179_37787e214f_m.jpg http://farm9.staticflickr.com/8082/8324968293_acd56ba786_n.jpg

Tags: RSS RSS RSS

Add Comment

2012-10-17 awk

Sometimes it’s hard to remember how cool awk used to be – I haven’t used awk ever since I switched to Perl about 15 years ago. :)

Today I wondered who had so many sleeping php-cgi processes on the server hosting Campaign Wiki:

ps aux | awk '/php-cgi$/ { user[$1]++} END { for (u in user) { print u, user[u] }}'
1231 233
greywulf 1

Thanks, awk. You might be awkward to use, but you get the job done. Now we just need to figure out who this user 1231 is. Who needs 233 php-cgi processes running?

Tags: RSS

Comments on 2012-10-17 awk


greywulf
Wish I could figure out what’s causing these. Most annoying :\

greywulf 2012-10-17 21:25 UTC



AlexSchroeder
Not your fault! When I looked you had one php-cgi process running. That makes total sense. It was this other user id that had more than 200 sleeping processes running that I cared about. (Added this clarification to the blog post, too.)

AlexSchroeder 2012-10-18 07:00 UTC

Add Comment

2012-09-29 External Fonts for SVG Files

I was writing a Perl CGI script that takes a SVG file I created with Inkscape, replaces a few placeholders and serves it via the web. How to supply this SVG file with an external TrueType font?

Upload the fonthttp://campaignwiki.org/Purisa.ttf

Add an appropriate MIME type – in the directory where the font file is, register an appropriate MIME type in your .htaccess file:

AddType application/x-font-ttf        .ttf

If you are creating a new .htaccess file, remember it needs a permission like -rw-r--r--.

Add an external style sheet to the SVG file – it is not obvious how to do this in Inkscape. I guess you can open the XML Editor and add an svg:style element. I just pasted the following into the beginning of my SVG file:

  <style
     type="text/css">
      @font-face {
          font-family: 'Purisa';
          src: local('Purisa'),
               url('/Purisa.ttf') format('truetype');
      }
  </style>

Care about Internet Explorer? I don’t have access to IE at the moment. I read that you might need to add an .eot file to support IE, and that would also require another MIME type to be added. You can convert a .ttf font to an .eot font using ttf2eot on the web.

Tags: RSS RSS

Add Comment

More...

Show Google +1