And be happy you are alive.
Did you know that:
Search the Feed
make sqlite work with snow leopard
29 March 2010 | 11:14 am by codeboxer
mv /Applications/Firefox.app/Contents/MacOS/libsqlite3.dylib /Applications/Firefox.app/Contents/MacOS/libsqlite3.dylib.orig cp /usr/lib/libsqlite3.dylib /Applications/Firefox.app/Contents/MacOS/libsqlite3.dylib
copied from:
http://support.mozilla.com/tiki-view_forum_thread.php?locale=nl&forumId=...
the hackingtosh
20 August 2009 | 12:01 pm by codeboxer I am always intrigued by new slang terms, especially ones that apply to tech.
Behold the new hackingtosh, linked here: http://lifehacker.com/software/hack-attack/build-a-hackintosh-mac-for-un...
Which is really just the concept of putting Mac OSX on a laptop that was not originally an Apple. I already have my MacBook Pro which is more or less awesome, but I do plan on picking up an Ubuntu (Linux) netbook eventually as a travel laptop, and I have my eye on this one:
http://system76.com/product_info.php?cPath=28&products_id=92&osCsid=d482...
Holy lowered price point, Batman!
php headers for expiring includes
1 April 2009 | 2:32 am by codeboxer By default, representations processed by PHP are not assigned validators, and are therefore uncacheable. However, developers can set HTTP headers by using the Header() function.
For example, this will create a Cache-Control header, as well as an Expires header three days in the future:
?php
Header("Cache-Control: must-revalidate");
$offset = 60 * 60 * 24 * 3;
$ExpStr = "Expires: " . gmdate("D, d M Y H:i:s", time() + $offset) . " GMT";
Header($ExpStr);
?
Remember that the Header() function MUST come before any other output.
As you can see, you'll have to create the HTTP date for an Expires header by hand; PHP doesn't provide a function to do it for you (although recent versions have made it easier; see the PHP's date documentation). Of course, it's easy to set a Cache-Control: max-age header, which is just as good for most situations.
For more information, see the manual entry for header at http://us2.php.net/manual/en/function.header.php
... php headers for expiring includes ...
this site rocks
30 March 2009 | 3:40 am by codeboxer
Awesome free css layouts? Ok!
Original post blogged on codeboxer.com.
i missed writing sql
25 March 2009 | 12:17 am by codeboxer Here is the first stored procedure that I have written in long time, fully finished. This puppy runs like a charm.
CREATE PROCEDURE sp_generate_forecast (IN p_run_day INT(10), IN p_month INT(10), IN p_year INT(10), IN revplanid INT(10), IN month_days INT(10))
BEGIN
DECLARE r_units, r_day, r_partner int;
DECLARE done int DEFAULT 0;
-- fields - partial_total day_ave retail_chg margin_chg
-- retail_margin net_sales p_rev_share gross_margin
-- clear out any previous forecast
DELETE FROM revplan_infos
WHERE revplan_id = revplanid
AND (revplan_type = 'Forecast' OR revplan_type = 'Variance');
-- enter Forecast
INSERT INTO revplan_infos (partner_id, partial_total, revplan_id, revplan_type,
created_at, updated_at, retail_margin, net_sales, p_rev_share,
gross_margin, amount, day_ave, p_name)
SELECT p.id partner_id
,sum(s.total) partial_total
,revplanid
,'Forecast'
,SYSDATE()
,SYSDATE()
,-1 * (((sum(s.total)/p_run_day) * month_days) * (p_info.retail_margin/100)) retail_margin
,((sum(s.total)/p_run_day) * month_days) - (((sum(s.total)/p_run_day) * month_days) * (p_info.retail_margin/100)) net_sales
,-1 * ((((sum(s.total)/p_run_day) * month_days) - (((sum(s.total)/p_run_day) * month_days) * (p_info.retail_margin/100))) * p_info.rev_share/100) p_rev_share
,((((sum(s.total)/p_run_day) * month_days) - (((sum(s.total)/p_run_day) * month_days) * (p_info.retail_margin/100))) * ((100-p_info.rev_share)/100)) gross
,(sum(s.total)/p_run_day) * month_days forecast_total
,(sum(s.total)/p_run_day) day_ave
,p.name
FROM summaries s
,accounts a
,partners p
JOIN partner_infos p_info
ON p_info.partner_id = p.id
WHERE item_month = p_month
AND item_year = p_year
AND s.account_id = a.id
AND a.partner_id = p.id
GROUP BY p.name
ORDER BY sum(s.total) DESC;
-- UPDATE REV PLAN
UPDATE revplan_infos AS ri SET
ri.retail_margin = -1 * (SELECT (ri.amount * partner_infos.retail_margin/100)
FROM partner_infos
WHERE ri.partner_id = partner_infos.partner_id),
ri.net_sales = (SELECT ri.amount - (ri.amount * partner_infos.retail_margin/100)
FROM partner_infos
WHERE ri.partner_id = partner_infos.partner_id),
ri.p_rev_share = -1 * (SELECT (ri.amount - (ri.amount * partner_infos.retail_margin/100)) * partner_infos.rev_share/100
FROM partner_infos
WHERE ri.partner_id = partner_infos.partner_id),
ri.gross_margin = (SELECT (ri.amount - (ri.amount * partner_infos.retail_margin/100)) * (100-partner_infos.rev_share)/100
FROM partner_infos
WHERE ri.partner_id = partner_infos.partner_id),
ri.p_name = (SELECT name FROM partners where partners.id = ri.partner_id)
WHERE revplan_id = revplanid
AND revplan_type = 'Plan';
INSERT INTO revplan_infos (partner_id, amount, revplan_id, revplan_type, retail_margin, net_sales,
p_rev_share, gross_margin, retail_chg, margin_chg, created_at, updated_at, p_name)
SELECT t_plan.partner_id
,for_amount - plan_amount GROSS_RETAIL
,revplanid
,'Variance'
,(for_amount * p_infos.retail_margin/100) - (plan_amount * p_infos.retail_margin/100) RETAIL_MARGIN
,(for_amount - (for_amount * p_infos.retail_margin/100)) - (plan_amount - (plan_amount * p_infos.retail_margin/100)) NET_SALES
,((for_amount - (for_amount * p_infos.retail_margin/100)) * p_infos.rev_share/100) - ((plan_amount - (plan_amount * p_infos.retail_margin/100)) * p_infos.rev_share/100) P_REV_SHARE
,((for_amount - (for_amount * p_infos.retail_margin/100)) * (100-p_infos.rev_share)/100) - ((plan_amount - (plan_amount * p_infos.retail_margin/100)) * (100-p_infos.rev_share)/100) GROSS_MARGIN
,(((for_amount - (for_amount * p_infos.retail_margin/100)) * (100-p_infos.rev_share)/100) - ((plan_amount - (plan_amount * p_infos.retail_margin/100)) * (100-p_infos.rev_share)/100))/plan_amount RETAIL_CHG
,(((for_amount - (for_amount * p_infos.retail_margin/100)) * (100-p_infos.rev_share)/100) - ((plan_amount - (plan_amount * p_infos.retail_margin/100)) * (100-p_infos.rev_share)/100))/t_plan.gross_margin MARGIN_CHG
,SYSDATE()
,SYSDATE()
,p.name
FROM
(SELECT partner_id, amount plan_amount, gross_margin
FROM revplan_infos
WHERE revplan_type = 'Plan'
AND revplan_id = revplanid) t_plan
JOIN (
SELECT partner_id, amount for_amount
FROM revplan_infos
WHERE revplan_type = 'Forecast'
AND revplan_id = revplanid) t_for
ON t_for.partner_id = t_plan.partner_id
JOIN partner_infos p_infos
ON p_infos.partner_id = t_plan.partner_id
JOIN partners p
ON p.id = t_plan.partner_id;
END
Original post blogged on codeboxer.com.
an old pivot table report procedure
26 February 2009 | 10:24 am by codeboxer Back in the day, I wrote reports for Rupert himself at FOX IT. Here is an old query from those days (written in T-SQL for .NET Reporting Services):
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.PR_T09 Script Date: 2/3/2006 4:17:47 PM ******/
ALTER PROCEDURE dbo.PR_T09
(
@YEAR1 char(4) = '2006'
,@QTR1 char(6) = 'Q1'
,@VER1 char(5) = '100'
,@YEAR2 char(4) = '2006'
,@QTR2 char(6) = 'Q2'
,@VER2 char(5) = '100'
,@BU VARCHAR(50) = ''
,@BU_GRP VARCHAR(50) = ''
,@BU_GRP_SUM VARCHAR(50) = 'TCFT-CONS-NOFOX21'
,@PERIOD_START INT = 1
,@PERIOD_END INT = 12
,@AMT BIT = 1
,@PRE_AMT BIT = 1
,@POST_AMT BIT = 1
,@II BIT = 0
)
/*************************************************************************
Created By: Krister Tidics - copied from F221 written by Livia Squires
Created Date: Feb 2, 2006
Called From: reporting services TCFT T09
Description: TCFT Operating Profit and Loss PL VARIANCE
Input: 1] Version info
2] II - switch to set whether Imputed interest is broken out separately
Output: PL data pivot for Operating Profit and Loss Reports
Revision History
Date: Initials: Desc:
**************************************************************************/
AS
BEGIN
SET NOCOUNT ON
/** Test harness
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DROP TABLE #PROJ
DECLARE
@YEAR1 CHAR(4)
,@QTR1 CHAR(6)
,@VER1 CHAR(5)
,@YEAR2 CHAR(4)
,@QTR2 CHAR(6)
,@VER2 CHAR(5)
,@BU_GRP_SUM VARCHAR(50)
,@BU_GRP VARCHAR(50)
,@BU CHAR(5)
,@II BIT
,@BY_PERIOD BIT
,@AMT INT
,@PRE_AMT INT
,@POST_AMT INT
,@PERIOD_START INT
,@PERIOD_END INT
SET @YEAR1 = '2006'
SET @QTR1 = 'Q1'
SET @VER1 = '100'
SET @YEAR2 = '2006'
SET @QTR2 = 'Q2'
SET @VER2 = '100'
SET @BU_GRP_SUM = 'TCFT-CONS-NOFOX21'
SET @BU_GRP = ''
SET @BU = ''
SET @II = 0
SET @BY_PERIOD = 0
SET @AMT = 1
SET @PRE_AMT = 1
SET @POST_AMT = 1
SET @PERIOD_START = 1
SET @PERIOD_END = 12
--*/
DECLARE @BU_NAME VARCHAR(50), @SCALE INT, @VER_ID INT
SET @SCALE = 5 -- no rounding
--DROP TABLE #PROJ
CREATE TABLE #PROJ (
COMPANY CHAR(5)
,PLAN_YEAR CHAR(4)
,FORECAST_QUARTER CHAR(6)
,VERSION_ID CHAR(5)
,BUSINESS_UNIT CHAR(5)
,REPORT_BUSINESS_UNIT CHAR(5)
,PERIOD INT NULL
,BUDGET_CODE CHAR(10)
,BC_DESC VARCHAR(60)
,PROJECT_ID CHAR(6) NOT NULL
,REPORT_PROJECT_ID CHAR(6) --NOT NULL
,SERIES_ID CHAR(6)
,TBD_ID CHAR(6)
,REPORT_SERIES_ID CHAR(6)
,FIRST_SEASON_PROJECT_ID CHAR(6) NULL
,LAST_SEASON_PROJECT_ID CHAR(6) NULL
,SEASON_COUNT CHAR(1) NULL
,PROJECT_GROUP NVARCHAR(15)
,PROJECT_GROUP_NAME NVARCHAR(50)
,PROJECT_GROUP_ORDER INT
,PROJECT_GROUP_ROLLUP NVARCHAR(15)
,PROJECT_GROUP_ROLLUP_NAME NVARCHAR(50)
,PROJECT_GROUP_ROLLUP_ORDER INT
,PROJECT_GROUP_MASTER NVARCHAR(15)
,PROJECT_GROUP_MASTER_NAME NVARCHAR(50)
,PROJECT_GROUP_MASTER_ORDER INT
,PROJECT_DESC VARCHAR (100)
,SERIES_DESC VARCHAR (100)
,INIT_AIR VARCHAR(5)
,AMOUNT MONEY
,PRE_AMOUNT MONEY
,POST_AMOUNT MONEY
,NETWORK MONEY
,DOM_SYN MONEY
,INT_TV MONEY
,DOM_HV MONEY
,INT_HV MONEY
,LM MONEY
,MUSIC MONEY
,PAY_TV MONEY
,OTHER MONEY
,REL_COST MONEY
,SERIES_PROD MONEY
,FINANCING MONEY
,POH MONEY
,RES MONEY
,PAR MONEY
,AMORT_ADJ MONEY
,VER_ID INTEGER NULL
)
CREATE NONCLUSTERED INDEX IX_PROJECT_ID ON #PROJ
(
PROJECT_ID
,PERIOD
)
CREATE NONCLUSTERED INDEX IX_FIRST_SEASON_PROJECT_ID ON #PROJ
(
FIRST_SEASON_PROJECT_ID
)
CREATE NONCLUSTERED INDEX IX_LAST_SEASON_PROJECT_ID ON #PROJ
(
LAST_SEASON_PROJECT_ID
)
CREATE NONCLUSTERED INDEX IX_SERIES_ID ON #PROJ
(
SERIES_ID
)
-- GET THE PROJECT DATA
-- VER 1
INSERT #PROJ (
COMPANY
,PLAN_YEAR
,FORECAST_QUARTER
,VERSION_ID
,BUSINESS_UNIT
--,PERIOD
,BUDGET_CODE
,BC_DESC
,PROJECT_ID
,SERIES_ID
,TBD_ID
,PROJECT_GROUP
,FIRST_SEASON_PROJECT_ID
,LAST_SEASON_PROJECT_ID
,SEASON_COUNT
,PROJECT_DESC
,SERIES_DESC
,INIT_AIR
,AMOUNT
,PRE_AMOUNT
,POST_AMOUNT
,NETWORK
,DOM_SYN
,INT_TV
,DOM_HV
,INT_HV
,LM
,MUSIC
,PAY_TV
,OTHER
,REL_COST
,SERIES_PROD
,FINANCING
,POH
,RES
,PAR
,AMORT_ADJ
,VER_ID
)
EXEC dbo.PR_TCFT_OPL_BU
@YEAR1
,@QTR1
,@VER1
,@BU
,@BU_GRP
,@BU_GRP_SUM
,@PERIOD_START
,@PERIOD_END
,@II
,@AMT
,@PRE_AMT
,@POST_AMT
,1 -- TEMP TABLE
,1 -- VER_ID
-- GET THE PROJECT DATA
-- VER 2
INSERT #PROJ (
COMPANY
,PLAN_YEAR
,FORECAST_QUARTER
,VERSION_ID
,BUSINESS_UNIT
--,PERIOD
,BUDGET_CODE
,BC_DESC
,PROJECT_ID
,SERIES_ID
,TBD_ID
,PROJECT_GROUP
,FIRST_SEASON_PROJECT_ID
,LAST_SEASON_PROJECT_ID
,SEASON_COUNT
,PROJECT_DESC
,SERIES_DESC
,INIT_AIR
,AMOUNT
,PRE_AMOUNT
,POST_AMOUNT
,NETWORK
,DOM_SYN
,INT_TV
,DOM_HV
,INT_HV
,LM
,MUSIC
,PAY_TV
,OTHER
,REL_COST
,SERIES_PROD
,FINANCING
,POH
,RES
,PAR
,AMORT_ADJ
,VER_ID
)
EXEC dbo.PR_TCFT_OPL_BU
@YEAR2
,@QTR2
,@VER2
,@BU
,@BU_GRP
,@BU_GRP_SUM
,@PERIOD_START
,@PERIOD_END
,@II
,@AMT
,@PRE_AMT
,@POST_AMT
,1 -- TEMP TABLE
,2 -- VER_ID
-- UPDATE THE TBD PROJECTS IN PRIOR TO MATCH THE PROJECT HEADERS IN THE CURRENT VERSION
UPDATE PRIOR_VER
SET PRIOR_VER.REPORT_PROJECT_ID = CURR_VER.REPORT_PROJECT_ID
,PRIOR_VER.REPORT_SERIES_ID = CURR_VER.REPORT_SERIES_ID
,PRIOR_VER.SERIES_DESC = CURR_VER.SERIES_DESC
,PRIOR_VER.PROJECT_DESC = CURR_VER.PROJECT_DESC
--,PRIOR_VER.RELEASE_STATUS = CURR_VER.RELEASE_STATUS
--SELECT *
FROM #PROJ PRIOR_VER
JOIN
(SELECT REPORT_PROJECT_ID,REPORT_SERIES_ID,TBD_ID,SERIES_DESC,PROJECT_DESC--,RELEASE_STATUS
FROM #PROJ
WHERE VER_ID = 1 AND TBD_ID IS NOT NULL)
CURR_VER
ON CURR_VER.TBD_ID = PRIOR_VER.PROJECT_ID
WHERE PRIOR_VER.VER_ID = 2
/** The next 4 insert queries are to make sure both versions have the same project ids
and budget codes in order for the inner join to work in the final select
**/
--insert any projects in ver2 that dont exist in ver1 into ver1
INSERT #PROJ (
VER_ID
--,PERIOD
,BUDGET_CODE
,BC_DESC
,COMPANY
,PROJECT_ID
,REPORT_PROJECT_ID
,SERIES_ID
,REPORT_SERIES_ID
,PROJECT_DESC
,SERIES_DESC
,BUSINESS_UNIT
,REPORT_BUSINESS_UNIT
,TBD_ID
--,RELEASE_STATUS
,AMOUNT
,NETWORK
,DOM_SYN
,INT_TV
,DOM_HV
,INT_HV
,LM
,MUSIC
,PAY_TV
,OTHER
,REL_COST
,SERIES_PROD
,FINANCING
,POH
,RES
,PAR
,AMORT_ADJ
)
SELECT 1 VER_ID
--,VER2.PERIOD
,VER2.BUDGET_CODE
,VER2.BC_DESC
,VER2.COMPANY
,VER2.PROJECT_ID
,VER2.REPORT_PROJECT_ID
,VER2.SERIES_ID
,VER2.REPORT_SERIES_ID
,VER2.PROJECT_DESC
,VER2.SERIES_DESC
,VER2.BUSINESS_UNIT
,VER2.REPORT_BUSINESS_UNIT
,VER2.TBD_ID
--,VER2.RELEASE_STATUS
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
FROM #PROJ VER2
WHERE VER2.VER_ID = 2
AND NOT EXISTS (SELECT VER1.PROJECT_ID
FROM #PROJ VER1
WHERE VER1.VER_ID = 1
AND VER2.PROJECT_ID = VER1.PROJECT_ID
)
--projects in ver1 that don't exist in ver2 into ver2
UNION ALL
SELECT 2 VER_ID
--,VER1.PERIOD
,VER1.BUDGET_CODE
,VER1.BC_DESC
,VER1.COMPANY
,VER1.PROJECT_ID
,VER1.REPORT_PROJECT_ID
,VER1.SERIES_ID
,VER1.REPORT_SERIES_ID
,VER1.PROJECT_DESC
,VER1.SERIES_DESC
,VER1.BUSINESS_UNIT
,VER1.REPORT_BUSINESS_UNIT
,VER1.TBD_ID
--,VER1.RELEASE_STATUS
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
FROM #PROJ VER1
WHERE VER1.VER_ID = 1
AND NOT EXISTS (SELECT VER2.PROJECT_ID
FROM #PROJ VER2
WHERE VER2.VER_ID = 2
AND VER2.PROJECT_ID = VER1.PROJECT_ID
)
-- This update is to move Amort in the prior version up to the series level
-- of the current version on the report. It needs to be done here
-- so that the next query for inserting missing budget codes into VER1
-- does not miss the amort which we are moving to a different series id
UPDATE #PROJ
SET PROJECT_ID = TBD_NEW_SERIES.SERIES_ID
--select #FIN.*,TBD_NEW_SERIES.TBD_ID,TBD_NEW_SERIES.SERIES_ID
FROM #PROJ
JOIN (
SELECT REPORT_SERIES_ID SERIES_ID,TBD_ID
FROM #PROJ
WHERE VER_ID = 1 AND TBD_ID IS NOT NULL
)
TBD_NEW_SERIES ON TBD_NEW_SERIES.TBD_ID = #PROJ.PROJECT_ID
WHERE #PROJ.VER_ID = 2
AND (#PROJ.AMORT_ADJ <> 0 OR #PROJ.PAR <> 0 OR #PROJ.RES <> 0 )
-- insert budget codes that exist in ver2 but not ver1 into ver1
-- and vice versa
INSERT #PROJ (
VER_ID
--,PERIOD
,BUDGET_CODE
,BC_DESC
,PROJECT_ID
,REPORT_PROJECT_ID
,SERIES_ID
,REPORT_SERIES_ID
,PROJECT_DESC
,SERIES_DESC
,BUSINESS_UNIT
,REPORT_BUSINESS_UNIT
,TBD_ID
--,RELEASE_STATUS
,AMOUNT
,NETWORK
,DOM_SYN
,INT_TV
,DOM_HV
,INT_HV
,LM
,MUSIC
,PAY_TV
,OTHER
,REL_COST
,SERIES_PROD
,FINANCING
,POH
,RES
,PAR
,AMORT_ADJ
)
SELECT 1 VER_ID
--,FIN2.PERIOD
,FIN2.BUDGET_CODE
,FIN2.BC_DESC
,FIN2.PROJECT_ID
,FIN2.REPORT_PROJECT_ID
,FIN2.SERIES_ID
,FIN2.REPORT_SERIES_ID
,FIN2.PROJECT_DESC
,FIN2.SERIES_DESC
,FIN2.BUSINESS_UNIT
,FIN2.REPORT_BUSINESS_UNIT
,FIN2.TBD_ID
--,FIN2.RELEASE_STATUS
,0.00 AMOUNT
,0.00 NETWORK
,0.00 DOM_SYN
,0.00 INT_TV
,0.00 DOM_HV
,0.00 INT_HV
,0.00 LM
,0.00 MUSIC
,0.00 PAY_TV
,0.00 OTHER
,0.00 REL_COST
,0.00 SERIES_PROD
,0.00 FINANCING
,0.00 POH
,0.00 RES
,0.00 PAR
,0.00 AMORT_ADJ
FROM #PROJ FIN2
WHERE FIN2.VER_ID = 2
AND NOT EXISTS (SELECT *
FROM #PROJ FIN1
WHERE FIN1.VER_ID = 1
AND FIN1.PROJECT_ID = FIN2.PROJECT_ID
AND FIN1.BUDGET_CODE = FIN2.BUDGET_CODE)
-- budget codes that exist in ver1 but not ver2 into ver2
UNION ALL
SELECT 2 VER_ID
--,FIN1.PERIOD
,FIN1.BUDGET_CODE
,FIN1.BC_DESC
,FIN1.PROJECT_ID
,FIN1.REPORT_PROJECT_ID
,FIN1.SERIES_ID
,FIN1.REPORT_SERIES_ID
,FIN1.PROJECT_DESC
,FIN1.SERIES_DESC
,FIN1.BUSINESS_UNIT
,FIN1.REPORT_BUSINESS_UNIT
,FIN1.TBD_ID
--,FIN1.RELEASE_STATUS
,0.00 AMOUNT
,0.00 NETWORK
,0.00 DOM_SYN
,0.00 INT_TV
,0.00 DOM_HV
,0.00 INT_HV
,0.00 LM
,0.00 MUSIC
,0.00 PAY_TV
,0.00 OTHER
,0.00 REL_COST
,0.00 SERIES_PROD
,0.00 FINANCING
,0.00 POH
,0.00 RES
,0.00 PAR
,0.00 AMORT_ADJ
FROM #PROJ FIN1
WHERE FIN1.VER_ID = 1
AND NOT EXISTS (SELECT *
FROM #PROJ FIN2
WHERE FIN2.VER_ID = 2
AND FIN1.PROJECT_ID = FIN2.PROJECT_ID
AND FIN1.BUDGET_CODE = FIN2.BUDGET_CODE)
--UPDATE PROJECT_GROUPING
UPDATE #PROJ
SET PROJECT_GROUP_NAME = RG3.GROUP_NAME
,PROJECT_GROUP_ORDER = RG3.GROUP_SORT
,PROJECT_GROUP_ROLLUP = RG2.GROUP_CODE
,PROJECT_GROUP_ROLLUP_NAME = RG2.GROUP_NAME
,PROJECT_GROUP_ROLLUP_ORDER = RG2.GROUP_SORT
,PROJECT_GROUP_MASTER = RG1.GROUP_CODE
,PROJECT_GROUP_MASTER_NAME = RG1.GROUP_NAME
,PROJECT_GROUP_MASTER_ORDER = RG1.GROUP_SORT
FROM REPORT R
JOIN REPORT_FRAME F ON R.REPORT_FRAME_ID = F.REPORT_FRAME_ID
JOIN REPORT_GROUP RG1 ON RG1.REPORT_FRAME_ID = F.REPORT_FRAME_ID AND RG1.GROUP_LEVEL = 1
JOIN REPORT_GROUP RG2 ON RG2.PARENT_REPORT_GROUP_ID = RG1.REPORT_GROUP_ID AND RG2.GROUP_LEVEL = 2
JOIN REPORT_GROUP RG3 ON RG3.PARENT_REPORT_GROUP_ID = RG2.REPORT_GROUP_ID AND RG3.GROUP_LEVEL = 3
JOIN #PROJ ON #PROJ.PROJECT_GROUP = RG3.GROUP_CODE
WHERE R.REPORT_NUMBER = 'T01A'
--SELECT * FROM #PROJ
--ADDED KT - I AM UPDATING THE REPORT_PROJECT_IDs for all those that don't come in
UPDATE #PROJ
SET REPORT_PROJECT_ID = PROJECT_ID
,REPORT_BUSINESS_UNIT = BUSINESS_UNIT
,REPORT_SERIES_ID = SERIES_ID
WHERE REPORT_PROJECT_ID IS NULL
-- final select statement
SELECT VER1.SERIES_DESC
,VER1.REPORT_SERIES_ID
,VER1.REPORT_PROJECT_ID
,VER1.PROJECT_DESC
--VER1.RELEASE_STATUS,
--,VER1.PERIOD
,VER1.BUDGET_CODE
--VER1.BC_CAT,
,VER1.BC_DESC
,VER1.PROJECT_GROUP
,VER1.PROJECT_GROUP_NAME
,VER1.PROJECT_GROUP_ORDER
,VER1.PROJECT_GROUP_ROLLUP
,VER1.PROJECT_GROUP_ROLLUP_NAME
,VER1.PROJECT_GROUP_ROLLUP_ORDER
,VER1.PROJECT_GROUP_MASTER
,VER1.PROJECT_GROUP_MASTER_NAME
,VER1.PROJECT_GROUP_MASTER_ORDER
,(VER1.AMOUNT - VER2.AMOUNT) AMOUNT
,(VER1.NETWORK - VER2.NETWORK) NETWORK
,(VER1.DOM_SYN - VER2.DOM_SYN) DOM_SYN
,(VER1.INT_TV - VER2.INT_TV) INT_TV
,(VER1.DOM_HV - VER2.DOM_HV) DOM_HV
,(VER1.INT_HV - VER2.INT_HV) INT_HV
,(VER1.LM - VER2.LM) LM
,(VER1.MUSIC - VER2.MUSIC) MUSIC
,(VER1.PAY_TV - VER2.PAY_TV) PAY_TV
,(VER1.OTHER - VER2.OTHER) OTHER
,(VER1.REL_COST - VER2.REL_COST) REL_COST
,(VER1.SERIES_PROD - VER2.SERIES_PROD) SERIES_PROD
,(VER1.FINANCING - VER2.FINANCING) FINANCING
,(VER1.POH - VER2.POH) POH
,(VER1.RES - VER2.RES) RES
,(VER1.PAR - VER2.PAR) PAR
,(VER1.AMORT_ADJ - VER2.AMORT_ADJ) AMORT_ADJ
FROM (
SELECT
--#PROJ.PERIOD,
#PROJ.SERIES_DESC,
#PROJ.REPORT_SERIES_ID,
#PROJ.REPORT_PROJECT_ID,
#PROJ.PROJECT_DESC,
--#PROJ.RELEASE_STATUS,
#PROJ.BUDGET_CODE
,#PROJ.BC_DESC
,#PROJ.PROJECT_GROUP
,#PROJ.PROJECT_GROUP_NAME
,#PROJ.PROJECT_GROUP_ORDER
,#PROJ.PROJECT_GROUP_ROLLUP
,#PROJ.PROJECT_GROUP_ROLLUP_NAME
,#PROJ.PROJECT_GROUP_ROLLUP_ORDER
,#PROJ.PROJECT_GROUP_MASTER
,#PROJ.PROJECT_GROUP_MASTER_NAME
,#PROJ.PROJECT_GROUP_MASTER_ORDER,
--#PROJ.BC_CAT,
--#PROJ.BC_DESC,
-1*SUM(AMOUNT) AMOUNT,
-1*SUM(NETWORK) NETWORK,
-1*SUM(DOM_SYN) DOM_SYN,
-1*SUM(INT_TV) INT_TV,
-1*SUM(DOM_HV) DOM_HV,
-1*SUM(INT_HV) INT_HV,
-1*SUM(LM) LM,
-1*SUM(MUSIC) MUSIC,
-1*SUM(PAY_TV) PAY_TV,
-1*SUM(OTHER) OTHER,
-1*SUM(REL_COST) REL_COST,
-1*SUM(SERIES_PROD) SERIES_PROD,
-1*SUM(FINANCING) FINANCING,
-1*SUM(POH) POH,
-1*SUM(RES) RES,
-1*SUM(PAR) PAR,
-1*SUM(AMORT_ADJ) AMORT_ADJ
FROM #PROJ
WHERE #PROJ.VER_ID =1
GROUP BY #PROJ.PROJECT_GROUP
,#PROJ.PROJECT_GROUP_NAME
,#PROJ.PROJECT_GROUP_ORDER
,#PROJ.PROJECT_GROUP_ROLLUP
,#PROJ.PROJECT_GROUP_ROLLUP_NAME
,#PROJ.PROJECT_GROUP_ROLLUP_ORDER
,#PROJ.PROJECT_GROUP_MASTER
,#PROJ.PROJECT_GROUP_MASTER_NAME
,#PROJ.PROJECT_GROUP_MASTER_ORDER
,#PROJ.SERIES_DESC
,#PROJ.REPORT_SERIES_ID
,#PROJ.REPORT_PROJECT_ID
,#PROJ.PROJECT_DESC
--,#PROJ.RELEASE_STATUS
--,#PROJ.PERIOD
,#PROJ.BUDGET_CODE
--,#FIN.BC_CAT
,#PROJ.BC_DESC
--ORDER BY #PROJ.RELEASE_ORDER,#PROJ.SERIES_DESC,#PROJ.REPORT_PROJECT_ID,#FIN.BUDGET_CODE
--ORDER BY #PROJ.REPORT_PROJECT_ID,#FIN.BUDGET_CODE
) VER1
JOIN (
SELECT
#PROJ.SERIES_DESC,
--#PROJ.PERIOD,
#PROJ.REPORT_SERIES_ID,
#PROJ.REPORT_PROJECT_ID,
#PROJ.PROJECT_DESC,
--#PROJ.RELEASE_STATUS,
#PROJ.BUDGET_CODE
,#PROJ.BC_DESC
,#PROJ.PROJECT_GROUP
,#PROJ.PROJECT_GROUP_NAME
,#PROJ.PROJECT_GROUP_ORDER
,#PROJ.PROJECT_GROUP_ROLLUP
,#PROJ.PROJECT_GROUP_ROLLUP_NAME
,#PROJ.PROJECT_GROUP_ROLLUP_ORDER
,#PROJ.PROJECT_GROUP_MASTER
,#PROJ.PROJECT_GROUP_MASTER_NAME
,#PROJ.PROJECT_GROUP_MASTER_ORDER,
--#PROJ.BC_CAT,
--#PROJ.BC_DESC,
-1*SUM(AMOUNT) AMOUNT,
-1*SUM(NETWORK) NETWORK,
-1*SUM(DOM_SYN) DOM_SYN,
-1*SUM(INT_TV) INT_TV,
-1*SUM(DOM_HV) DOM_HV,
-1*SUM(INT_HV) INT_HV,
-1*SUM(LM) LM,
-1*SUM(MUSIC) MUSIC,
-1*SUM(PAY_TV) PAY_TV,
-1*SUM(OTHER) OTHER,
-1*SUM(REL_COST) REL_COST,
-1*SUM(SERIES_PROD) SERIES_PROD,
-1*SUM(FINANCING) FINANCING,
-1*SUM(POH) POH,
-1*SUM(RES) RES,
-1*SUM(PAR) PAR,
-1*SUM(AMORT_ADJ) AMORT_ADJ
FROM #PROJ
WHERE #PROJ.VER_ID =2
GROUP BY #PROJ.PROJECT_GROUP
,#PROJ.PROJECT_GROUP_NAME
,#PROJ.PROJECT_GROUP_ORDER
,#PROJ.PROJECT_GROUP_ROLLUP
,#PROJ.PROJECT_GROUP_ROLLUP_NAME
,#PROJ.PROJECT_GROUP_ROLLUP_ORDER
,#PROJ.PROJECT_GROUP_MASTER
,#PROJ.PROJECT_GROUP_MASTER_NAME
,#PROJ.PROJECT_GROUP_MASTER_ORDER
,#PROJ.SERIES_DESC
,#PROJ.REPORT_SERIES_ID
,#PROJ.REPORT_PROJECT_ID
,#PROJ.PROJECT_DESC
--,#PROJ.PERIOD
--,#PROJ.RELEASE_STATUS
,#PROJ.BUDGET_CODE
--,#PROJ.BC_CAT
,#PROJ.BC_DESC
) VER2
ON VER1.REPORT_PROJECT_ID = VER2.REPORT_PROJECT_ID
AND VER1.BUDGET_CODE = VER2.BUDGET_CODE
ORDER BY VER1.SERIES_DESC
,VER1.REPORT_PROJECT_ID
,VER1.BUDGET_CODE
--select * from #PROJ
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Original post blogged on codeboxer.com.
git ftw
19 February 2009 | 3:00 pm by codeboxer copied from:
Cleanly Migrate Your Subversion Repository To a GIT Repository
The first thing we need to do is create a users file that maps all your SVN users to your GIT users. Just make a file on your Desktop named 'users.txt'. Map the users using this format:
jmaddox = Jon Maddox
bigpappa = Brian Biggs
Simple. Now here are the commands you'll run. I'll explain them below.
mkdir my_blog_tmp
cd my_blog_tmp
git-svn init http://code.yoursite.net/my_blog/trunk/ --no-metadata
git config svn.authorsfile ~/Desktop/users.txt
git-svn fetch
The first two are self explanatory, we're making a new directory for the temporary repository. The second command initializes the directory as a git-svn hybrid thing and points the origin at your SVN repository. The flag, --no-metadata, tells GIT to leave all the SVN details behind (not the commit log). The fourth command tells GIT to remap all the SVN users to GIT users when it sucks down the source and history. The last command actually does the fetching.
Ok, so now after a few LONG minutes, your source is all there. Do a git log to see that your users have been mapped. Sweet!
Now you just have one last step. You need to clone this repository. Why do we do this? When doing a normal git clone it will take everything we want from the temporary repository, while leaving behind all the SVN cruft that was there to support the git-svn stuff.
git clone my_blog_tmp my_blog
Original post blogged on codeboxer.com.
gem install id3lib ruby mac command
17 February 2009 | 5:37 pm by codeboxer you need this to install the ruby id3lib library under mac if you installed the actual id3lib library with MacPorts:
ARCHFLAGS="-arch i386" gem install id3lib-ruby -- --build-flags --with-opt-dir=/opt/local
Original post blogged on codeboxer.com.
Streamclip we love it
8 February 2009 | 12:49 pm by codeboxer http://en.wikipedia.org/wiki/MPEG_Streamclip
I needed to rip an old live dvd from about 5 years ago with an archaic encryption and this little puppy did it - on my Intel MacBook Pro. I also had to buy the http://www.apple.com/quicktime/mpeg2/ Quicktime encoder. Leave it to QT to fleece you every change they get. The streamclip utility works really well and will allow to you crop video - something QT would be happy to charge you another $30 for (Quicktime 'Pro'). Don't do it. You need the mpeg2 encoder but don't buy the Quicktime Pro - it is more or less worthless.
more info for streamclip - http://www.squared5.com/Original post blogged on codeboxer.com.
dump in your stored proc via rake task
2 February 2009 | 3:35 pm by codeboxer These days, a remote login to your prod database is a luxury, one not everyone has.
See below for how insert a stored proc (or 'stored routine', as MySQL likes to say) via rake task:
namespace :db do
task :load_sp1 do
sqlconn = ActiveRecord::Base.connection();
sql = %Q! CREATE PROCEDURE `sp_generate_forecast`(IN p_run_day INT(2), IN p_month INT(2), IN p_year INT(4), IN revplanid INT(10), IN month_days INT(2))
BEGIN
DECLARE r_units, r_day, r_partner int;
DECLARE done int DEFAULT 0;
-- fields - partial_total day_ave retail_chg margin_chg
-- retail_margin net_sales p_rev_share gross_margin
-- clear out any previous forecast
DELETE FROM revplan_infos
WHERE revplan_id = revplanid
AND (revplan_type = 'Forecast' OR revplan_type = 'Variance');
INSERT INTO revplan_infos (partner_id, amount, revplan_id, revplan_type, retail_margin, net_sales,
p_rev_share, gross_margin, retail_chg, margin_chg, created_at, updated_at, p_name)
SELECT t_plan.partner_id
,for_amount - plan_amount GROSS_RETAIL
,revplanid
,'Variance'
,(for_amount * p_infos.retail_margin/100) - (plan_amount * p_infos.retail_margin/100) RETAIL_MARGIN
,(for_amount - (for_amount * p_infos.retail_margin/100)) - (plan_amount - (plan_amount * p_infos.retail_margin/100)) NET_SALES
,((for_amount - (for_amount * p_infos.retail_margin/100)) * p_infos.rev_share/100) - ((plan_amount - (plan_amount * p_infos.retail_margin/100)) * p_infos.rev_share/100) P_REV_SHARE
,((for_amount - (for_amount * p_infos.retail_margin/100)) * (100-p_infos.rev_share)/100) - ((plan_amount - (plan_amount * p_infos.retail_margin/100)) * (100-p_infos.rev_share)/100) GROSS_MARGIN
,(((for_amount - (for_amount * p_infos.retail_margin/100)) * (100-p_infos.rev_share)/100) - ((plan_amount - (plan_amount * p_infos.retail_margin/100)) * (100-p_infos.rev_share)/100))/plan_amount RETAIL_CHG
,(((for_amount - (for_amount * p_infos.retail_margin/100)) * (100-p_infos.rev_share)/100) - ((plan_amount - (plan_amount * p_infos.retail_margin/100)) * (100-p_infos.rev_share)/100))/t_plan.gross_margin MARGIN_CHG
,SYSDATE()
,SYSDATE()
,p.name
FROM
(SELECT partner_id, amount plan_amount, gross_margin
FROM revplan_infos
WHERE revplan_type = 'Plan'
AND revplan_id = revplanid) t_plan
JOIN (
SELECT partner_id, amount for_amount
FROM revplan_infos
WHERE revplan_type = 'Forecast'
AND revplan_id = revplanid) t_for
ON t_for.partner_id = t_plan.partner_id
JOIN partner_infos p_infos
ON p_infos.partner_id = t_plan.partner_id
JOIN partners p
ON p.id = t_plan.partner_id;
END!
sqlconn.execute("DROP PROCEDURE IF EXISTS `sp_generate_forecast`")
sqlconn.execute sql
end
end
Original post blogged on codeboxer.com.
minor league pivot query
27 January 2009 | 10:33 pm by codeboxer This would be a pivot query 101 kind of snippet. They teach this in SQL kindergarten - I only imagine, of course, because I never went.
The use case: it's a single table, with values grouped by partner_id and two enums for revplan_type. You need a variance report. What do you do?
SELECT t_plan.partner_id, plan_amount - for_amount variance
FROM
(SELECT partner_id, amount plan_amount
FROM revplan_infos
WHERE revplan_type = 'Plan') t_plan
JOIN (
SELECT partner_id, amount for_amount
FROM revplan_infos
WHERE revplan_type = 'Forecast') t_for
ON t_for.partner_id = t_plan.partner_id
Original post blogged on codeboxer.com.
how to call a stored routine from Rails
27 January 2009 | 9:43 pm by codeboxer So you wanna call your own stored routine, eh?
Here ya go - the right syntax. I read on a blog somewhere that you send any command to the database through a .find_by_sql command, but that turned out to be a load of baloney. Is there anything Ruby + ActiveRecord can't do?
def forecast
x = params[:revplan_id].to_i
@revplan = Revplan.find(x)
sql = ActiveRecord::Base.connection();
sql.execute "SET autocommit=0";
sql.begin_db_transaction
result = sql.execute("call sp_generate_forecast(#{Time.now.month}, #{Time.now.year}, #{x}, #{month_days(Time.now.month)})")
sql.commit_db_transaction
end
Clothing, I mean transaction support is optional.Original post blogged on codeboxer.com.
MySQL 5.0 SP with Cursor Logic
23 January 2009 | 11:43 am by codeboxer I have written a bunch of SPs over the years (stored procedure, as I learned it, but MySQL calls them Stored Routines - SRs?). I even have my best/largest/most complicated SP ever linked here on codeboxer.com at http://codeboxer.com/read.php?a=the-Monstertrak-migration.
That one was of course written in PL/SQL, and I have now officially moved out of the world of Oracle and into the world of MySQL 5.0 - which is sort of like moving from New York City to San Jose (weather notwithstanding).
Anyway, I found precious little RECENT coverage on how MySQL implemented it's 'stored routines', and I got to tell ya it took some digging - never mind trying to figure out how cursors work. MySQL, btw, has an ok documentation section but it is mediocre at best, somehow following in the fragmented example of the Rails community in what seems to be the curse of the open-source ADD.
So in the spirit of this site which has always been to keep pieces of code lying around that I may use later - here is my very first MySQL sp, which does nothing really useful except use a cursor to insert values into a table. Come back next week for the syntax needed to use parameters, lol.
Other things to notice - you have to declare a separate handler for pulling out of a cursor loop when you run out of records (does anyone else remember fondly how easy ADO recordsets were to use?) but other than that things look all right in MySQL land. Bye Oracle!
CREATE PROCEDURE testy ()
BEGIN
declare a, b int;
DECLARE done int DEFAULT 0;
declare masterCursor CURSOR FOR
SELECT SUM(sales.amount) amt, sales.item_month
FROM sales
WHERE RETAILER_ID = 336
GROUP BY sales.item_month;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN masterCursor;
REPEAT
FETCH masterCursor INTO a, b;
IF NOT done THEN
insert into revplan (amount, item_month)
values (a,b);
END IF;
UNTIL done END REPEAT;
CLOSE masterCursor;
END
ps, gotta give a shout out to http://www.razorsql.com/ which so far has been easy to use and has served as stand-in IDE for Toad, which is what I was used to.Original post blogged on codeboxer.com.
Perl linux and database backups
21 January 2009 | 4:52 pm by codeboxer I got to tell ya, most of the greatest people don't get the recognition they deserve. Open Source is all about everyone putting in what they got. So mosey on over to http://worldcommunity.com/opensource/ and tell Peter Brown that his Perl-based mysql backup script works like a charm.
It is very easy to set up, so I won't go too far into it - just download and follow the instructions. I also opted in to for the ftp push, so after the script dumps out a mysql txt file for every table, it will tar it, zip it, and push it to the ftp server of your choice. Cool.
This is where it gets tricky - I pulled this little gem out of a comment on Peter Brown's site. This script leaves you with a directory of txt files - most of us are used to the single .sql file of the whole db, so getting at a full restore from a bunch of txt files does not sound fun - until linux jumps in for the rescue.
Get this, just untar it:
tar -xf bak.mysql.2009-01-20_14.55.30_.tar.gz
Move into that directory:
cd bak.mysql.2009-01-20_14.55.30
and hit it with the cat xargs combo, shooting it right into mysql:
find . -name "*.txt" -print | xargs -t --replace cat {} | mysql your_db_name -u your_db_user -p -h subdir.yoursite.com
and you're done! sweet, huh?
p.s. - the -h switch at the end (-h subdir.yoursite.com) is not always necessary, if it is localhost you can leave it out.Original post blogged on codeboxer.com.
right joins versus the subquery
14 January 2009 | 4:37 pm by codeboxer If you're anything like me, you are a serious pragmatist. I like to get at the problem immediately, and if there are shortcuts, then we have to weight short-term versus long-term gain before we can choose sides.
Here is a great SQL example of this principle.
The old-school way to write this query would be with the subquery, like so:
select id from retailers
where id in (select retailer_id from sales)
and merchant = 'Ralphs'
I am simply trying to verify that I have not seen sales for the 'Ralphs' retailer come in yet, despite that 'merchant' value already being present in the retailers table.
I know, it's a little confusing, but bear with me.
One might also write:
select id from sales
where retailer_id in (select id from retailers where merchant = 'Ralphs')
Neither of these run very well. Without some added indexing, the first query won't return anything at all before it times out, and the second one runs ok but it is about 3 to 4 times slower than the right join (below).
select r.id from retailers r
right join sales s on s.retailer_id = r.id
where r.merchant = 'Ralphs'
There you have it - right joins FTW.Original post blogged on codeboxer.com.
scoping for dummies
6 January 2009 | 5:30 pm by codeboxer I hope I don't have to explain what scoping is. It is sort of analogous to the 'parameterized' in 'parameterized query'. Basically, once you start getting all hot and heavy with active record, you don't want to have write a specific model based routine to match EVERY query use-case, so enter scoping.
Here is an example:
class SummariesController < ApplicationController
layout 'streamlined'
acts_as_streamlined
around_filter :scope_by_user
def scope_by_user
Summary.find_with_user_scope(current_user) { yield }
end
end
and the model:
class Summary < ActiveRecord::Base
belongs_to :account
belongs_to :retailer
def self.find_with_user_scope(user)
if user.is_super_admin?
with_scope(:find => { :conditions => ["id > 0"] }) { yield }
else
with_scope(:find => { :conditions => ["account_id in (select id from accounts where active = 1 and partner_id = ?)", user.partner.id ] }) { yield }
end
end
end
Go nuts, people! The possibilities are endless.Original post blogged on codeboxer.com.
Sample class with a unit test
12 December 2008 | 10:40 am by codeboxer Also from a job interview a while back.
Here is the class that they gave me, which I extended -
class YPSet
#new and improved (sorry for the double email)
def initialize(*args)
@collect = []
args.each do |this|
@collect << this.to_i
end
@collect.sort!
@count = @collect.size
end
def average
sum = 0
@collect.each do |this|
sum+=this.to_f
end
(sum/@count)
end
def first_rec
@collect[0] ? @collect[0].to_i : nil
end
def lowest
first_rec
end
def highest
@collect[@count-1] ? @collect[@count-1].to_i : nil
end
def median
even_check=0
@collect.each do |this|
#loop to skip even method which is not importing properly
even_check = even_check==0 ? 1 : 0
end
is_even = even_check == 0 ? true : false
if is_even
#average median
half_position = (@count/2)
#first value
x1 = @collect[half_position-1]
#second value
x2 = @collect[half_position]
((x1+x2).to_f/2)
else
#single target
target_position = ((@count.to_i-1)/2) + 1
# value
@collect[target_position-1]
end
end
def average_dev
#return average deviation from main average per item
sum_of_deviations = 0
ave = self.average
@collect.each do |this|
sum_of_deviations += (this.to_f-ave).abs
end
sum_of_deviations/@count.to_i
end
def number_of_odds
count = 0
@collect.each do |this|
even_check = 0
for x in 1..this
#loop to skip even method (again)
even_check = even_check==0 ? 1 : 0
end
count += even_check
end
count
end
def number_of_evens
@count - number_of_odds
end
def partition(subdivisions)
#split into evenly sized partitions
single_part_size = (@count/subdivisions).to_i
new_array = []
cur_division = 1
x = 0
count_leftover = @count % subdivisions
original_leftover_count = count_leftover
while cur_division <= subdivisions
#test divisions
sub_array = []
while (x < ((single_part_size*cur_division) + (cur_division<=original_leftover_count && original_leftover_count>0) ? cur_division))
end
And the unit test I wrote for it -
require "test/unit"
require "yp_set"
class YPSetTest < Test::Unit::TestCase
def setup
end
def test_should_calculate_average
assert_equal 6, YPSet.new(8, 10, 2, 6, 4).average
assert_equal 5.5, YPSet.new(10, 11, 0, 1).average
end
# The median is the middle value in a set of values. If there are
# an even number of values, it should be the average of the two middle
# values
def test_should_calculate_median
assert_equal 6, YPSet.new(8, 10, 2, 6, 4).median
assert_equal 4.5, YPSet.new(10, 0, 1, 9, 8, 1).median
end
def test_should_calculate_with_string_args
assert_equal 2, YPSet.new(1, "2", 3, "2", 2).average
assert_equal 3, YPSet.new("7", "2", 3, "8", 2).median
end
def test_should_be_ordered_correctly
#added per point #3
assert_equal 2, YPSet.new(9, "11", 2, "2", 6).first_rec
end
def test_average_dev
#added per point #4
assert_equal 2, YPSet.new(1, 1, 2, 2, 3, 9).average_dev
end
def test_lowest_and_highest
#added per point #4
assert_operator YPSet.new(99, 44, 33).lowest, "<", YPSet.new(33, 99, 44).highest
end
def test_number_of_evens
#added per point #4 - no need to test odds as they use the same function
assert_equal 6, YPSet.new(99, 44, 33, 2, 6, 8, 9, 12, 14).number_of_evens
end
def test_should_split_into_evenly_sized_subsets
values = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15]
subsets = YPSet.new(*values).partition(3)
assert_equal 3, subsets.length
subsets.each do |subset|
assert_equal 5, subset.length
end
assert_equal values.length, subsets.flatten.length
values << 16
subsets = YPSet.new(*values).partition(3)
assert_equal 3, subsets.length
subsets.each do |subset|
assert_in_delta 5, subset.length, 1
end
assert_equal values.length, subsets.flatten.length
end
end
Good times!Original post blogged on codeboxer.com.
Do the Alexa Dance aka a review of Alexa reviews
10 December 2008 | 2:22 pm by codeboxer I won't name names, or link links as it were, but ever since I discovered my own Alexa ranking(s) I have been obsessed with understanding how to optimize them - and as a matter of course I have read quite a few blogs about it. Without succumbing to the cliche of promising '20 Ways to Game Your Ranking', I will share what I know.
The only people that matter are the users that have installed the Alexa toolbar from http://www.alexa.com/site/download.
The traffic to your site of these users with the toolbar installed over the last 3 months is what determines your rank. So at the most basic level, the logical thing to do is bump up marketing (really?) and hope that a good percentage of those new clicks are Alexa users. This may be effective, but it is also wasteful to some degree.
It is also worth noting that the Alexa user base reputedly skews very much towards webmasters, so another good way to attract a stronger percentage of Alexa users relative to your total user base is to target content towards webmasters, like a laser. You zap in their clicks with post titles like '20 Sure Fire Tips on How to Be An Alexa Jedi' or 'I Made It Into the Alexa Top 1000 - In Only 10 Minutes!'. Of course you also need to have real stuff for them to read, or else the Jedi jokes can get your Force cut off. Anyway, my angle is that I work with Ruby on Rails, I post about Ruby On Rails, and I am a webmaster - so there's your targeted audience.
That being said, I have started a new category (http://www.digbox.net/index.php/alexa/) to track my newfound obsession. And if you are reading this and haven't already installed your very own toolbar then you don't deserve to be reading this, and I'll probably cry myself to sleep tonight. I hope you're happy.
Tune in next week for more observations.Original post blogged on codeboxer.com.
polymorphic user list class part II
9 December 2008 | 5:41 pm by codeboxer This is part II of a series.
start here: http://www.digbox.net/index.php/RoR/polymorphic-user-list-class
class User < ActiveRecord::Base
has_many :user_lists
has_many :invites
def join_group(id)
x = UserList.new
x.user_id = self.id
x.user_listable_type = "Group"
x.user_listable_id = id
if x.save
return true
else
return false
end
end
def join_project(id)
x = UserList.new
x.user_id = self.id
x.user_listable_type = "Project"
x.user_listable_id = id
if x.save
return true
else
return false
end
end
def join_event(id)
x = UserList.new
x.user_id = self.id
x.user_listable_type = "Event"
x.user_listable_id = id
if x.save
return true
else
return false
end
end
def groups
return UserList.find(:all, :conditions => ["user_listable_type ='Group' and user_id = ?", self.id])
end
def projects
return UserList.find(:all, :conditions => ["user_listable_type ='Project' and user_id = ?", self.id])
end
def events
return UserList.find(:all, :conditions => ["user_listable_type ='Event' and user_id = ?", self.id])
end
end
Original post blogged on codeboxer.com.
add aggregates to a group by
9 December 2008 | 3:02 pm by codeboxer Pull in YTD aggregates, along with the numbers grouped by month - good example of a self-join as well as a 'derived table' which in this example is called 'counted'.
select s.product, count(s.serial_number) number_sold, year(s.item_date) y, month(s.item_date) m, counted.units
from sales s
join (select product, count(serial_number) units from sales group by product) counted on s.product = counted.product
group by s.product, year(s.item_date), month(s.item_date)
order by s.product, year(s.item_date), month(s.item_date)
Original post blogged on codeboxer.com.
Tableless models and a checkbox collection
4 December 2008 | 5:44 pm by codeboxer I have always loved the sound of a table-less model. It feels so dirty. Here is one from the school of hard knocks, known as the Merchant model:
class Merchant < ActiveRecord::Base
def self.columns() @columns ||= []; end
def self.column(name, sql_type = nil, default = nil, null = true)
columns << ActiveRecord::ConnectionAdapters::Column.new(name.to_s, default, sql_type.to_s, null)
end
column :merchant, :string
validates_presence_of :merchant
def self.get_edit_values(page_id)
new_arr = []
Summary.find_by_sql(["
SELECT merchant, CASE LENGTH(selected) > 0 WHEN TRUE THEN 1 ELSE 0 END value FROM
(SELECT value merchant, merchant selected
FROM
(SELECT merchant from merchant_pages where page_id=?) signed_up
RIGHT JOIN (SELECT DISTINCT MERCHANT value FROM retailers) distinct_m
ON distinct_m. value = signed_up. merchant) new_alias
ORDER BY merchant
", page_id.to_i]).each do |x|
new_arr << [x.merchant, x.value]
end
new_arr
end
def self.get_values
Summary.find_by_sql(["
SELECT DISTINCT MERCHANT value FROM retailers order by merchant
"]).each do |x|
@columns << x.value
end
@columns
end
end
Of course, it does hit the database, but there is no Merchants table, per se, there is just this model. What returns from a 'SELECT * FROM MERCHANTS' is the distinct selection for the value of 'merchant' from the Retailers table. These come in from the partners, and they are very consistent. I can't tell you how helpful this is. The system auto-magically created a Target.com that showed up immediately alongside it's older bother, Target. Watch out, however, for cache-ing concerns that will necessarily stem from this approach in terms of scale - in this case, that was not a concern because we have a very small (but highly lucrative) user base.
This is the corresponding controller code. This is from the pages_controller, because there is no direct merchants_controller.
def update
@page = Page.find(params[:id])
if @page.update_attributes(params[:page])
if dump_all_merchant_pages(@page.id)
params[:merchants].each do |p|
create_or_update_merchant_page(p[0].to_s, @page.id, true)
end
end
flash[:notice] = 'Page was successfully updated.'
redirect_to pages_path
else
render :action => 'edit'
end
end
the functions, from the bottom of the page:
private
def dump_all_merchant_pages(page_id)
Page.find(page_id).merchant_pages.each do |x|
x.destroy
end
true
end
def create_or_update_merchant_page(m, p, val)
x = MerchantPage.find_by_sql(["SELECT merchant
FROM merchant_pages
WHERE merchant = ?
AND page_id = ? LIMIT 1", m, p])
if !x.first
x = MerchantPage.new(:merchant => m, :page_id => p)
x.save
else
if !val
x.destroy
end
true
end
end
Finally, the _page partial:
form_for ([@page]) do |f|
Page Name
f.text_field :title, :size => 60
Main Content
f.text_area :page_body, :cols => 100
Locator Footer (Link back to Partner homepage)
f.text_area :page_footer, :cols => 100, :rows => 5
Merchants
ul class="Merchant-list"
if button_name=="Save Changes"
@merchants.each do |m|
li INPUT type="checkbox" name="merchants[<=m[0]%>][]" <=m[1]=="1" ? "checked" : "" label m[0].to_s /label
end
else
@merchants.each do |m|
li INPUT type="checkbox" name="merchants[<=m>][]"/> <=m.to_s /label
end
end
/ul
f.submit button_name
endOriginal post blogged on codeboxer.com.
The old INSERT SELECT syntax example
4 December 2008 | 2:29 am by codeboxer A good example of SQL's favorite timesaver.
INSERT INTO `evo_bloggroups`
SELECT '4',
bloggroup_group_ID,
bloggroup_ismember,
bloggroup_perm_poststatuses,
bloggroup_perm_edit,
bloggroup_perm_delpost,
bloggroup_perm_comments,
bloggroup_perm_cats,
bloggroup_perm_properties,
bloggroup_perm_admin,
bloggroup_perm_media_upload,
bloggroup_perm_media_browse,
bloggroup_perm_media_change
FROM `evo_bloggroups`
WHERE bloggroup_blog_ID = 1
Original post blogged on codeboxer.com.
polymorphic user list class
4 December 2008 | 2:15 am by codeboxer I wrote this for a job interview that never panned out. Tune in next week for the matching User class (linked here).
Question: why do interviewers often admit to not being very technical, yet continue on to ask complicated questions and become bewildered as a consequence?
And furthermore, how good is the guy they hire if no one knows how to talk to him? Ah well, it is almost Christmas.
class UserList < ActiveRecord::Base
belongs_to :user_listable, :polymorphic => true
belongs_to :user
def invite_user(target_user, note)
if target_user
x = Invite.new
x.user_id = target_user.id
x.title = "user #{self.user.id} has invited user #{target_user.id} to #{self.user_listable_type} #{self.user_listable_id}"
x.note = note
x.user_list_id = self.id
if x.save
message = "#{x.title} - #{note}"
return message
else
return false
end
else
return "given user id matches nothing."
end
end
end
Original post blogged on codeboxer.com.
Ruby find_by_sql and a select statement
4 December 2008 | 2:05 am by codeboxer This is how I still do things. I don't think it's terribly old-fashioned. Databases are still faster than web servers, aren't they?
Summary.find_by_sql(["
(select 'GMG' name, 0 id, 1 orderby
from partners where 1=1)
union
(select display_val name, p.id, 2 orderby
from partners p where active = 1)
order by orderby, name, id"
])
Original post blogged on codeboxer.com.
Be a CSS Sherlock Holmes
3 December 2008 | 6:00 pm by codeboxer Sometimes you want to do the right thing. Sometimes you want to do the right thing, AND it's possible. This is one of those times. I'm about as guilty as anyone for putting inline CSS all over the place, but I have rebuilt my blog and I am not going to make the same mistakes I did last time.
Question: how do you match a piece of 'source code' to the actual master style sheet reference?
Last year, I might have just overridden the inline div with a "style='display: xxx; color=NEWCOLOR;'" kind of thing, and shoved a bunch of css into the div wherever it happened to be. Today, not so much. Having just set up my new skin the way I like it, using a very few tweaks to the existing style sheet, I needed to track down a way of changing the color of the CODEBOXER.COM in the top left of my screen. You may appreciate that it is now a soothing and complimentary shade of charcoal. It used to be a very whorish red. What did I do?I became a CSS Sherlock Holmes, and you can too!
STEP ONE: VIEW SOURCE
As you must know by now, anyone can view anyone's HTML just by right clicking on their web page. Of course, this isn't the whole show, because this 'source code' is fully 'executed', which is something we don't really need to worry about for this discussion. The reason we are doing it in this case is to see how certain parts of the website are referencing their CSS-ness, post-execution. Behold:
STEP TWO: SECURE THE EVIDENCE
STEP THREE: SEARCH FOR YOUR KEYWORD
and this is what you find:
Basically, the evo_title_area div begat the title_container div which begat the widget_core_coll_title div which within itself held an "a" tag which was enclosed in an "h2" tag.
Do we have enough evidence?
Wait for it...
div.evo_title_area div.widget_core_coll_title h2 a {
color: #4b473b;
}
div.evo_title_area div.widget_core_coll_title h2 a:hover {
color: #aaa;
text-decoration:none;
}
Extra points for the hover.Original post blogged on codeboxer.com.
Yes my routes are messed up
3 December 2008 | 4:40 pm by codeboxer And I do apologize. My permalinks are gone. My podcast is down. You may have noticed.
I am having what is now a bit of a ritual December web asset rejuvenation party, and yesterday both http://digbox.net and http://codeboxer.com got smacked pretty hard. Anyway, I think this site looks better than ever and if you got here from a strange link, just press your back button, look at the link, press forward again and type something into the search box about it (on the right, halfway down the page). This site is about quality, not quantity - so you will probably find what you are looking for.
Finally, if you need to look at a list of everything it is right here.
The podcast will be back up in a few days with new songs for everyone.
Lighthouse API integration in 10 minutes or less
15 November 2008 | 2:45 am by codeboxer As a second part in this series, I am going to talk about Lighthouse for a second. They rock. I built up a calendar app based on their API and the Ruby calendar_helper, and it barely took me a day. True story. I ended up using an API token, and I learned a lot about a lib/ based connection object, very interesting.
Here are a few caveats:
Don't mess with the root class too much. It was not really necessary for most of what I had to do. This is actually one of the cases where it makes sense to load the logic into the controller (a little bit). Plus there isn't much more meat on the bone, so to speak, the root class covers 100% of a ludicrously simple back-end.
All Lighthouse database objects are not created equal. Tickets are king, and everything else is nothing. I will explain more later - just don't try to get picky with your call for Milestones. All I have ever been able to do is return a simple dump.
Installation: all I had to do was click through to the GIT repository. From there click lib/, and then lighthouse.rb. Cut and paste the code you see into a lighthouse.rb file in your lib/ folder. Everything you need is right there.
code snippet from my controller action:
if @project
#calculate i differential
month_now = Time.now.month
year_now = Time.now.year
i = ((@year - year_now)*12)+ (@month - month_now).abs
if i == 1
@tickets = @project.tickets(:q => "created:'before #{Time.now.day} day#{Time.now.day>1 ? 's' : ''} ago'")
elsif i > 1
@tickets = @project.tickets(:q => "created:'before #{i-1} months ago'")
else
@tickets = @project.tickets(:q => "created:'since 1 month ago'")
end
@milestones = @project.milestones(:q => "due_on:'#{@month}/#{@year}'")
else
false
end
Thankfully, you can get picky with your call for Tickets. That is where the meat of the API is. The text based date searching is pretty cool, because you get a hard limit of 30 rows returned from your query, so you need to be granular enough to catch only what you want.
This is a remote controller action that I use to get info about a particular ticket:
def ticket_body
ticket_id = params[:id]
@project = Lighthouse::Project.find(:first)
ticket = Lighthouse::Ticket.find ticket_id, :params => {:project_id => @project.id}
out_string = ""
#counter = 0
ticket.versions.each do |version|
this_body = version.body_html
out_string << "on #{(version.created_at - (60 * 60 * 8)).strftime("%m/%d at %I:%M%p")} #{get_owner_name(version.user_id)} said: #{this_body ? this_body : "status change"}"
end
render :text => out_string
end
Notice the 8 hour time differential from UTC code, and that you have to iterate through the ticket.versions to pull out the notes (thanks to Lighthouse help staff for that one).
And then, of course, I have this at the top of my controller:
def initialize
Lighthouse.account = 'xxx'
Lighthouse.token = '1111919192384584XXXXXXXXXXX'
@project = Lighthouse::Project.find(:first)
end
like I said before, somehow the @project.milestones method won't take a :q argument, so you're stuck with just a simple 'select *' query for your @milestones recordset. Maybe they will change this soon.Original post blogged on codeboxer.com.
My Site Links
Screenshots are featured above. If you visit gmgpulse, you may login as demo/demo.
Other Links
More clickables for the terminally click-addicted.