Search:

Matching Pages:

# Software

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

# 2014-06-04 Homebrew, GitHub

A reminder for my future self: When fixing something in /usr/local (which is the domain of Homebrew, just commit it, and then:

git push git@github.com:kensanata/homebrew.git

Commits will show up in my Homebrew fork and I’ll be able to create a pull request from there. Such as this one.

Also, as noted in the pull request: “For future reference the preferred commit message format for simple version updates is libcaca 0.99b19.”

You are welcome, future me.

Also, I managed to make a mess of merges, consecutive commits, pushed and all, and now I’m confused. Aaargh!

Tags:

# 2014-05-16 Firefox and DRM

I just sent the following email to Andreas Gal, following a call by the Free Software Foundation, FSF condemns partnership between Mozilla and Adobe to support Digital Restrictions Management. Gal is the Chief Technology Officer at Mozilla and wrote Reconciling Mozilla’s Mission and W3C EME. In my email I also mention Mitchell Baker’s blog post DRM and the Challenge of Serving Users. If you’re wondering what Iceweasel is: It’s a rebranded Firefox by the Debian Project.

I guess it’s fairly obvious where I stand. Free Culture! Maybe Cory Doctorow’s piece can serve as an introduction, Firefox’s adoption of closed-source DRM breaks my heart.

(I also fixed some typos in this copy.)

To: agal@mozilla.com
Subject: DRM
From: Alex Schroeder <alex@gnu.org>

Dear Mr. Gal

I recently learned of Mozilla’s decision to include DRM in their product. In the past, people have been switching from IE to Firefox. It was the obvious choice. These days people seem to be switching to Chrome. Apparently it is faster or whatever. I didn’t care very much, because I felt that Firefox was the obvious choice for users that valued their privacy and their freedom. None of my peers made the switch to Chrome because they wanted DRM. Those of us who stayed with Firefox, those of us who keep recommending Firefox to younger users, those of us who care about privacy and freedom, however—we might end up leaving. I’m not quite sure what I’ll choose instead. Perhaps it will simply be Iceweasel.

By choosing to cave in to the demands for DRM, Mozilla has become one of them instead of remaining one of us. It hurts! From what I’ve read online, Mozilla has tried to soften the blow, and I appreciate that. Nevertheless, DRM is a symbol of oppression instead of freedom. DRM is a symbol for disenfranchisement instead of empowerment. DRM is a symbol for big business streaming their content down to silent users instead of Remix Culture. Is this the future we want for ourselves? Even if a lot of people wanted this future, is this the future Mozilla wants? Instead, Mozilla could be the future it wanted to be. DRM takes away the rights we have as users and forces us to place our trust in companies that only think of our money. We must necessarily hope that in their mercy they will allow us to exercise our rights (Fair Use and similar regulations in other countries).

To illustrate this absurdity, I’d like to illustrate the situation here in Switzerland where I live. I have the explicit right to make copies of copyrighted works for myself and my close circle of friends and family. DRM prevents me from exercising this right. In order to exercise my right, I need to use software that removes DRM, and this is legal in Switzerland. Advertising for such software, however, is illegal. What were they thinking? I don’t know. All I know is that DRM is degrading. It tramples my rights and requires me to dabble with the Dark Side. That’s not the future I want to be!

All of the above doesn’t even touch the issues around Free Software and proprietary DRM blobs. Yuck!

I’m not sure if there’s a good way out of this situation. In Mitchell Baker’s blog post she said that each user “will be able to decide whether to activate the DRM implementation or to leave it off and not watch DRM-controlled content.” Perhaps a good first step would be simply distribute two versions of Firefox: Firefox and Firefox+DRM. The unfortunate symbol would still stand. The separation would have benefits, however: The separation would make sure that the separation remains a simple one on the source level. It would make life easier for people repackaging Firefox as totally free software. I’m thinking of Iceweasel, obviously. It would make it easier to ditch DRM later. It would assure us that the sandbox wasn’t even there if we didn’t want it. Less code to maintain, less code to review, less bugs to fix, for those of us that don’t care about DRM. It would also allow you to discover whether 30% of the traffic also corresponds to 30% of the users. Who knows whether that is true?

Anyway, I’m hoping to see Mozilla leave at least a foot and hopefully all of its feet in the free software camp. I’d love to see Mozilla take steps to make a change in course at a later date easy to implement. Pack the flexibility to change the stance on DRM into the source code, don’t just make it a setting.

Yours sincerely
Alex Schroeder

# 2014-04-26 Down the rabbit hole of software installing

It all started with this:

alex@Megabombus:~$inkscape dyld: Library not loaded: /usr/local/lib/libpng15.15.dylib Referenced from: /usr/local/bin/inkscape Reason: image not found Trace/BPT trap: 5 It seemed I had 1.5 and 1.6 in the Homebrew Cellar, but only 1.6 was installed. I tried to have both libraries linked but missed this hint: ln -s /usr/local/Cellar/libpng/1.5.18/lib/libpng15.15.dylib /usr/local/lib/libpng15.15.dylib (It would have been 1.5.17 in my case.) Instead I deleted both versions of libpng and hoped that reinstalling all the stuff would work. brew update && brew upgrade But look at this: alex@Megabombus:~$ brew reinstall inkscape
==> Reinstalling inkscape
intltool: Unsatisfied dependency: XML::Parser
Homebrew does not provide Perl dependencies; install with:
cpan -i XML::Parser
Error: An unsatisfied requirement failed this build.

Now we’re switching from Homebrew issues to Perl issues!

alex@Megabombus:~$cpanm XML::Parser --> Working on XML::Parser Fetching http://www.cpan.org/authors/id/T/TO/TODDR/XML-Parser-2.41.tar.gz ... OK Configuring XML-Parser-2.41 ... OK Building and testing XML-Parser-2.41 ... FAIL ! Installing XML::Parser failed. See /Users/alex/.cpanm/work/1398513458.18494/build.log for details. Retry with --force to force install it. alex@Megabombus:~$ less /Users/alex/.cpanm/work/1398513458.18494/build.log

Looking at the log file:

PERL_DL_NONLAZY=1 /Users/alex/perl5/perlbrew/perls/perl-5.18.1/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/*.t
Can't load '/Users/alex/.cpanm/work/1398513458.18494/XML-Parser-2.41/blib/arch/auto/XML/Parser/Expat/Expat.bundle' for module XML::Parser::Expat: dlopen(/Users/alex/.cpanm/work/1398513458.18494/XML-Parser-2.41/blib/arch/auto/XML/Parser/Expat/Expat.bundle, 2): no suitable image found.  Did find:
/Users/alex/.cpanm/work/1398513458.18494/XML-Parser-2.41/blib/arch/auto/XML/Parser/Expat/Expat.bundle: mach-o, but wrong architecture at /Users/alex/perl5/perlbrew/perls/perl-5.18.1/lib/5.18.1/darwin-2level/DynaLoader.pm line 194.
at /Users/alex/.cpanm/work/1398513458.18494/XML-Parser-2.41/blib/lib/XML/Parser.pm line 18.
Compilation failed in require at /Users/alex/.cpanm/work/1398513458.18494/XML-Parser-2.41/blib/lib/XML/Parser.pm line 18.

WTF “wrong architecture”? Now I’m thinking of our recent upgrade of the laptop to OSX Mavericks. Oh, and before that I migrated my account from the old Mac Mini using Mac OS 10.6.8 to this laptop… Considering that we’re using Perlbrew and that the resulting Perl is stored in my home directory, this might indeed be an issue.

Thus, deeper into the rabbit hole we go!

alex@Megabombus:~$perlbrew install perl-stable Fetching perl 5.18.2 as /Users/alex/perl5/perlbrew/dists/perl-5.18.2.tar.bz2 Download http://www.cpan.org/src/5.0/perl-5.18.2.tar.bz2 to /Users/alex/perl5/perlbrew/dists/perl-5.18.2.tar.bz2 Installing /Users/alex/perl5/perlbrew/build/perl-5.18.2 into ~/perl5/perlbrew/perls/perl-5.18.2 ... I think I will go and take some pretty pictures in the mean time. In the end, everything seems to be ready. alex@Megabombus:~$ cpanm XML::Parser
!
! Can't write to /Library/Perl/5.16 and /usr/local/bin: Installing modules to /Users/alex/perl5
! To turn off this warning, you have to do one of the following:
!   - run me as a root or with --sudo option (to install to /Library/Perl/5.16 and /usr/local/bin)
!   - Configure local::lib your existing local::lib in this shell to set PERL_MM_OPT etc.
!   - Install local::lib by running the following commands
!
!         cpanm --local-lib=~/perl5 local::lib && eval $(perl -I ~/perl5/lib/perl5/ -Mlocal::lib) ! XML::Parser is up to date. (2.41) alex@Megabombus:~$ cpanm --local-lib=~/perl5 local::lib && eval $(perl -I ~/perl5/lib/perl5/ -Mlocal::lib) --> Working on local::lib Fetching http://www.cpan.org/authors/id/E/ET/ETHER/local-lib-2.000011.tar.gz ... OK Configuring local-lib-2.000011 ... OK ==> Found dependencies: ExtUtils::MakeMaker --> Working on ExtUtils::MakeMaker Fetching http://www.cpan.org/authors/id/B/BI/BINGOS/ExtUtils-MakeMaker-6.96.tar.gz ... OK Configuring ExtUtils-MakeMaker-6.96 ... OK Building and testing ExtUtils-MakeMaker-6.96 ... OK Successfully installed ExtUtils-MakeMaker-6.96 (upgraded from 6.63_02) Building and testing local-lib-2.000011 ... OK Successfully installed local-lib-2.000011 2 distributions installed I did notice something strange, though. When I opened a new terminal: ERROR: The installation "perl-5.18.1" is unknown. I had to manually change perl-5.18.1 to perl-5.18.2 in my ~/.perlbrew/init file: # DO NOT EDIT THIS FILE export PERLBREW_MANPATH="/Users/alex/perl5/perlbrew/perls/perl-5.18.2/man" export PERLBREW_PATH="/Users/alex/perl5/perlbrew/bin:/Users/alex/perl5/perlbrew/perls/perl-5.18.2/bin" export PERLBREW_PERL="perl-5.18.2" export PERLBREW_ROOT="/Users/alex/perl5/perlbrew" export PERLBREW_VERSION="0.66" Yeah, ignore the comment on the first line. I wonder why perlbrew didn’t do this automatically when I uninstalled 5.18.1 and installed 5.18.2. Oh well. And now, Inkscape! alex@Megabombus:~$ brew reinstall inkscape
==> Reinstalling inkscape
==> Installing inkscape dependency: boost-build
######################################################################## 100.0%
==> ./bootstrap.sh
=º  /usr/local/Cellar/boost-build/1.55.0: 269 files, 3.0M, built in 65 seconds
==> Installing inkscape
==> ./configure --prefix=/usr/local/Cellar/inkscape/0.48.4 --enable-lcms --disable-poppler-cairo
==> make install
collect2: ld returned 1 exit status
make[1]: *** [inkview] Error 1
make[1]: *** Waiting for unfinished jobs....
make[1]: *** [inkscape] Error 1
make: *** [install-recursive] Error 1

These open issues may also help:
inkscape fails on 10.9.2 (https://github.com/Homebrew/homebrew/issues/28556)

Whaaaaaat!?

OK, following the instructions of mistydemeo:

alex@Megabombus:~$rm -rf /Library/Caches/Homebrew/inkscape--bzr alex@Megabombus:~$ brew install inkscape --HEAD
alex@Megabombus:~$brew uninstall inkscape Uninstalling /usr/local/Cellar/inkscape/HEAD... alex@Megabombus:~$ brew install inkscape --HEAD
==> Cloning lp:inkscape/0.48.x
Not checking SSL certificate for xmlrpc.launchpad.net.
You have not informed bzr of your Launchpad ID, and you must do this to
==> ./autogen.sh/s / Build phase:Apply phase:adding file 280/282
==> make install
🍺  /usr/local/Cellar/inkscape/HEAD: 853 files, 81M, built in 22.6 minutes

Testing… YES!! 😺 👍

Tags:

# 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

(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
Referenced from: /usr/local/bin/gnutls-cli

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
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
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
This is free software. It is licensed for use, modification and
redistribution under the terms of the GNU General Public License,

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

That seems to have fixed it!

I wonder how this happened in the first place.

Tags:

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. Comments on 2014-04-03 Eich and Mozilla 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 Also, Jürgen Hubert links to this piece, on Google+: This Is What Can Happen When Your New Boss Is Homophobic. AlexSchroeder 2014-04-22 05:08 UTC 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: 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:

# 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:

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

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

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

# 2013-02-27 SVG Filters

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

This is what I want to achieve:

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:

# 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:

(Also on GitHub.)