My company is under considering of different content management systems.  Here are the following choice:

1. Magnolia (Java base) — powerful but really expensive.  Need steeper learning curve for me and my colleagues.  Support is wonderful but again expensive.  Plug ins are almost all included.  Feature is powerful.
2. WordPress (php base) — powerful, not expensive (open source), lots of support and free.  And professional paid support is available by 3rd party (wordpress itself doesn’t support much).  Learning curve is not a lot easy for us because we are all php programmer by default already.  Lot of open source plugin and excellent paid one you can get (cheap though).
3. Drupal (php base) — not rate yet but same easy to manage too.

Me and my colleagues personally like wordpress.  What do you think?  If you come across this, let me know what you think.

Last time I have done the formatting workaround with the negative parenthesis.  This time I have to optimize the speed with the sync of the InDesign Plugin in Mac called datalinker from teacup software.  It is a database one-way sync program that does the price for you in InDesign Template Document.  The price is done by my Flex and Java CMS program transferring the price from our provider with Excel sheet and translate each cell to sybase.

Originally, it takes 2 min to sync the value tag with one report of approx. 380 tags.  It mean it will run the procedure 380 times per report. The reason behind the length time is because of the query is taken from a half millions rows table and require the store procedure to do 3 query, 2 select into, and format the way we want.  So, I follow the sybase’s optimization recommendation and  I have done is the following:

1.)  Create Index for the query especially match to my “where” statement

2.)  Update Stats of that big table. ( I guess the DBA will do a cron job in production but definitely not in my dev server)

3.)  Drop the tempDb table asap when it is done.

Once I done all these, the time is gone from 2 min to 20 sec.  So, indexes really matter.  Also, Update statistic is a necessary for that table.

There are a few things I can work on, such as split the queries into 2 store procedures, make the query into a nest query. and etc ….

I have tried to use a few examples online with stackoverflow.com and etc, but for sybase 12 I can’t find anything to make my store procedure to convert the negative number to negative parenthesis.  Worst thing is our database server didn’t enable java and I can’t really use replace_regexp to change the negative number to negative parenthesis.  I have used sometime to get around the problem.

Here is the work around here and I only type out part of my code (ignore my temp table and my variable day range:

=============================================================

select distinct ##tempARFiveColTb.Unique_Fund_Code,
##tempARFiveColTb.Fund_WKS_Item_Date,
column_1_value =
case
when ##tempARFiveColTb.column_1_value < 0 then
“(“+rtrim(convert(varchar, abs(convert(money, ##tempARFiveColTb.column_1_value))))+”)”
when ##tempARFiveColTb.column_1_value = null then “–”
else convert(char, ##tempARFiveColTb.column_1_value)
end
from ##tempARFiveColTb where Fund_WKS_Item_Date like ‘%’ + @date +’%’

=============================================================

– result:

Unique_Fund_Code     Fund_WKS_Item_Date

19316                      Dec 31 2011 12:00AM         (3.00)
19316                      Dec 31 2011 12:00AM         5.00
19316                      Dec 31 2011 12:00AM         –

the last one is null so in my code it convert to “–”

=============================================================

In my code (look at the bolder part), I have a case inside the select field to make sure if they are negative number, then make sure you make it a absolute number, then convert it into char.  In my case, I need to trim the number space but not necessary for others.  Then add parenthesis on both side.

If it is not a negative number, then just convert it into char and print it out.  It is because I find out that in my code I can’t print out a numeric or a money datatype number but text only. But I think it should work to print out with numeric type with case.  But here I just show it out anyway and see it will hard other people to save sometime because many example online has already save my time and I hope I can also help others.  Enjoy !!

Successfully got multi-column store procedure work out with group-by.  Use me around a week to figure it out and work on.  Well, Doing a InDesign with a database plug-in to call store procedure is much complicated than I thought before.  Working with price list like a catalogs and flyer is much simpler with InDesign, but Annual Report with multi-column column in different time is a bit more complicated (isn’t it suppose to be a DBA or a database programmer’s job instead of a web developer?? Anyway, I do everything even graphic design).

Anyway it is done and the most important part is that you got to join the inner table with the outer table for a different year’s column. The code is here:

—- Setting the periods for all 5 dates for Annual Report with a fixed date (you can use system date for @date if you want)
SELECT @date = DATEADD(year, 0, @date)
SELECT @date2 = DATEADD(year, -1, @date)
SELECT @date3 = DATEADD(year, -2, @date)
SELECT @date4 = DATEADD(year, -3, @date)
SELECT @date5 = DATEADD(year, -4, @date)

– make a more temp table with customized year with five column such as 2011, 2010, 2009, 2008 and 2007.
INSERT INTO ##tempARFiveColTb
select distinct Combined_Item_ID,
Unique_Fund_Code,
Fund_WKS_Item_Date,
current_value as Current_Value,
(select distinct current_value from ##tempARValueTable
where Fund_WKS_Item_Date like ‘%’ + @date2 + ‘%’ and o.Combined_Item_ID = Combined_Item_ID) as prev_Last_year_end_dec31_value,
(select distinct current_value from ##tempARValueTable
where Fund_WKS_Item_Date like ‘%’ + @date3 + ‘%’ and o.Combined_Item_ID = Combined_Item_ID) as prev_2nd_year_dec31_value,
(select distinct current_value from ##tempARValueTable
where Fund_WKS_Item_Date like ‘%’ + @date4 + ‘%’ and o.Combined_Item_ID = Combined_Item_ID) as prev_3nd_year_dec31_value,
(select distinct current_value from ##tempARValueTable
where Fund_WKS_Item_Date like ‘%’ + @date5 + ‘%’ and o.Combined_Item_ID = Combined_Item_ID) as prev_4th_year_dec31_value
from ##tempARValueTable o
GROUP BY Combined_Item_ID

– list the table
select distinct ##tempARFiveColTb.Combined_Item_ID, ##tempARFiveColTb.Unique_Fund_Code, ##tempARFiveColTb.Fund_WKS_Item_Date, ##tempARFiveColTb.current_year_sept30_value, ##tempARFiveColTb.current_year_mar31_value, ##tempARFiveColTb.previous_1_year_mar31_value, ##tempARFiveColTb.previous_2_year_mar31_value, ##tempARFiveColTb.previous_3_year_mar31_value from ##tempARFiveColTb
where Fund_WKS_Item_Date like ‘%’ + @date +’%’
order by Combined_Item_ID asc, Unique_Fund_Code asc, Fund_WKS_Item_Date desc

BTW, I am using temp table in sybase. If you find this code useful, use it and have fun.

Welcome to my tech blog.  I am a web developer (can do some design).  You know, too many of them in the world but never be enough.

Web world is change so big for this 15 years.  Still remember from the first day I step into a web market company to write pages and program that we are using text base email with terminal without a browser to HTML 1.0 with mosaic.  Also back-end is change from c and Perl CGI back-end to java, .Net and PHP script base.  Front-end changes from HTML base to JavaScript, flash and now HTML5.  Device changes from a terminal computer to now we use a mobile device with phone apps.  These thing is just happening in a blink of our eyes.

Web is really a place central connect everyone together and that’s why I choose this name. But thing also get complicated too, security, fraud, crime and bullying happens everyday.  And you know, web can even use to put down a few political regime.  So,

Anyway, stay tune to anything I post that related to the web development, web technologies and web related topics.  Although I say it is a technical blog, but, you know, not necessary technical.  I might not be expert in many area but I think worth to share experiences in job, development and many web tech stuffs.  So, hope you enjoy !!

Henry