Tuesday, December 25, 2012

Mexicali Chili

1 lb lean ground beef
1 medium onion, diced
1 14oz can chili beans
1 16oz jar corn salsa
3 large Idaho potatoes, diced
3 cups water
2 cups cold milk
2 tbl corn starch

Brown beef, season with 1/2 tsp cumin, 1/2 tsp salt, 1/2 tsp chili powder if desired.

Sweat onion in 1 tbl butter separately.

Mix beef, onion, beans, salsa, potatoes and water in large pot. Bring to a boil, then cover and simmer for 30 minutes.

Mix milk and cornstarch in small bowl.

When potatoes are tender, slowly stir in milk mixture. Mix for a minute or so as the chili thickens.

Serve with diced avacado, tomato, and maybe some feta?

Enjoy, we did!

Friday, November 09, 2012

Crystal Reports Server 2008, WACS and AD Auth on 2008 Domain

Image representing SAP as depicted in CrunchBase
Image via CrunchBase
This was kicking my butt the past couple of days. Up to this point, I'd simply been using the server to schedule and email reports out to people. What I wanted was to allow users to log in with their AD credentials, browse InfoView and run their own reports. Simple, right?

This is how I did it.
  1. Create a service account in AD. This account doesn't need elevated privileges  Tick the User cannot change password and Password never expires boxes. In my example the account is svc_crs.
  2. Use setspn.exe to create the Service Principal Name. Run the command on a DC in a command prompt with elevated privileges. Here is more info on setspn. The convention is setspn -a MySpnName/MySvcAcct.Domain.Com MySvcAcct. In my environment I used setspn.exe -a CrystalSvr/svc_crs.NETWORK.INTERNAL svc_crs.
  3. Open your service account Account Properties in Active Directory Users and Computers, select the Delegation tab, and change delegation to Trust this user for delegation to any service (Kerberos only).
  4. In Central Management Console (CMC), go to the Authentication area. Open Windows AD and Enable Windows Active Directory (AD)
    • Click the field to the right of AD Administration Name and add your service account UPN like mine, svc_crs@NETWORK.INTERNAL, and the Default AD Domain. Make sure you use ALL CAPS for your domain info in both the UPN and domain fields.
    • Map in an AD Group, using DOMAIN\GROUP convention.
    • Select Use Kerberos authentication and fill in your Service principal name, e.g. CrystalSvr/svc_crs.NETWORK.INTERNAL.
    • Tick the box under Synchronization of Credentials to ensure account sync on user log in.
  5. Schedule Users AD Alias Updates, and accept defaults. Select the options I've got selected below, and also accept the default scheduling for AD Group Graph Updates. Select Update and close this window. You should see users from your selected group populated in the Users and Groups area.
  6. Add the service account to the local administrators group on the Crystal Reports server.
  7. In the Local Security Policy settings on the server, expand Local Policies, and highlight User Rights Assignment. Add your service account to Act as part of the operating system.
  8. At this point you should be able to log into the server using client tools, like browsing the Enterprise Server using Crystal Reports 2008. If not, see my troubleshooting links below.
  9. Now you'll need to add a C:\WINNT directory on your server.
  10. Place a file named bscLogin.conf into this directory with these contents:

    com.businessobjects.security.jgss.initiate {
    com.sun.security.auth.module.Krb5LoginModule required debug=true;
    };
    
    
  11. Place a file named krb5.ini into this same directory with these contents, replacing MYDOMAIN.COM with your upper-case domain, and MYDCHOSTNAME.MYDOMAIN.COM with your AD controller host name followed by domain name in upper-case:

    [libdefaults]
    default_realm = MYDOMAIN.COM
    dns_lookup_kdc = true
    dns_lookup_realm = true
    default_tgs_enctypes = rc4-hmac
    default_tkt_enctypes = rc4-hmac
    udp_preference_limit = 1
    [realms]
    MYDOMAIN.COM = {
    kdc = MYDCHOSTNAME.MYDOMAIN.COM
    default_domain = MYDOMAIN.COM
    }
  12. You can validate your setup by executing kinit.exe in the BOintstall\javasdk\bin directory, enter your UPN with the domain name capitalized. You'll be prompted for your password, and should be told a ticket was issued and deposited in your profile directory.
  13. In CMC go to Servers, Service Categories, Core Services. Locate the Web Application Container Server in the description column. Open it.
  14. In the Properties area, locate Active Directory Configuration Settings.
  15. In the Krb5.ini File Location box, enter
    c:\winnt\krb5.ini
  16. In the bscLogin.conf File Location box, enter
    c:\winnt\bscLogin.conf
  17. Restart SIA
Good resources for info:
  • SAP site. Create a free account in their community. Look for the following KB articles:
    • KB 1483762, two great PDF documents at the end of this article.
    • KB 1529040, top KB's for setting this up on XI SP3 and later.
    • KB 1476374, detailed setup instructions.
  • Hexaware Blog
  • David Taylor's Blog
Enhanced by Zemanta

Saturday, October 20, 2012

New Sourcegforge Project

English: jQuery Mobile logo.
English: jQuery Mobile logo. (Photo credit: Wikipedia)
I just posted a new project on Sourceforge, called Anonymous Compliance Reporting.


Anonymous Compliance Reporting aims to be a solution for small organizations that desire to adhere to the corporate practice of providing a means for employees to report issues anonymously.

This project originated to provide a mechanism for my employers staff to report instances of compliance violation in the workplace, e.g. improper handling of PHI (Patient Health Information), HIPAA violations, and the like.

I took on the task of updating it recently, to a more robust "Web 2.0" application, adding JQuery, Ajax, and updated graphics.

It runs on both LAMP and WAMP servers.

Documentation is on this wiki https://sourceforge.net/p/anonrep/wiki/Home/ and in the README file in the download package.

The sample website is completely open to play with, the credentials are admin / password. Have fun!
Anonymous Compliance Reporting Web Site
Enhanced by Zemanta

Thursday, July 26, 2012

ADFS and AD User Name Changes

One of our users was recently married, and had her name changed.

Two days later she opened a helpdesk ticket indicating one of our claims aware applications denied her access to it's resources.

After enabling ADFS debug logging, I discovered that the server was still referencing her old user name.

After a few minutes of Googling, I found this article that points to a MS KB article outlining the use of LSA caching user SID's, creating my problem.

A quick Powershell script keeps this from happening again after creating the registry key noted in the KB article:

$RegKey="HKLM:\System\CurrentControlSet\Control\Lsa"
Set-ItemProperty -path $RegKey -name LsaLookupCacheMaxSize -value 0
Set-ItemProperty -path $RegKey -name LsaLookupCacheMaxSize -value 128

Enhanced by Zemanta

Thursday, July 05, 2012

Easy CSV import into Oracle

English: The logo of Oracle Corporation de:Bil...
English: The logo of Oracle Corporation de:Bild:Oracle-Logo.svg he:תמונה:Oracle Logo.jpg (Photo credit: Wikipedia)

The easiest way I've found to import text data sources into Oracle have been using the Microsoft SQL Server Import and Export Wizard that comes with Standard, Enterprise or Developer editions of MS SQL Server.

Assuming you've got the Oracle database drivers installed, just right click a database from within SQL Server Management Studio, choose Tasks -> Import Data.

As your datasource, choose your CSV or text file.

After you've configured the specifics of your data source, you'll need to choose a destination. Choose the OLE DB Provider for Oracle, then click Properties.

  • Instead of using your Server Name, enter the database instance name and the credentials. 
  • Click Text Connection to be sure it is correct, and click Allow saving password.
  • Click OK.
  • Click Next.


In the next window, choose the destination table you've created in Oracle. Edit your mappings and preview them to be sure the data is being added correctly, click next, and next again to Run Immediately.

You may get errors, but review them and your data to confirm your import was complete. My errors usually stem from not being able to retrieve column code page information which doesn't prevent any data loading.

Cheers!

Enhanced by Zemanta

Monday, June 04, 2012

ADFS AutoCertificateRollover

BITE (show)
BITE (show) (Photo credit: Wikipedia)
Leaving your ADFS 2.0 installation in AutoCertificateRollover mode will most certainly bite you in the ass at some point.

This is the default mode when you install ADFS, and when your certificate expires, you'll get something that looks like this:Error message
The key to your answer is in the first line:
ID4175: The issuer of the security token was not recognized by the IssuerNameRegistry.

All you need to do is insert the new thumbprint from your ADFS Token-signing certificate.

Make sure it's all in uppercase, and you've not added any invalid character codes, or spaces in the thumbprint or you will continue to get this error message.

You are better served by generating another certificate for a longer period than the default 1 year. You can easily do this by opening Windows PowerShell and issuing the following:

First, add your snapin:
Add-PsSnapin Microsoft.Adfs.Powershell

Show a list of your ADFS properties.
Get-ADFSProperties

Set your certificate duration for 3 years.
Set-AdfsProperties -CertificateDuration 1095

Immediately update your Issuing certificate, and break any existing RP's that don't consume your Federation Metadata automagically.
Update-AdfsCertificate -Urgent

Update
5/16/2013
This year our cert automatically rolled over, requiring me to not only to update our RP's with the latest certificate, but a custom web app I wrote needed the STS info updated to include BOTH Token Signing Certificate thumbprints.



Enhanced by Zemanta

Tuesday, April 03, 2012

OpenAir ETL Project

Image representing MySQL as depicted in CrunchBase
Image via CrunchBase
Reporting in OpenAir has presented our company with some challenges. As such, I was asked to take a look at how we might be able to create an ETL of this data, create our reports and automatically distribute them to our project managers.

This is how I did it.

First we needed to purchase OpenAir's Automated Backup service. This service will export data in text format or MySQL dumps of any or all tables in their database, zip them up and deposit them anywhere via FTP, FTPS, or SFTP. We set up an FTP server for our repository in the DMZ.

Once this bit was done, I needed an installation of MySQL Server and a method to programmatically pull the data from the DMZ, deposit it on a Windows share, unzip and insert the data. Below is a batch file used for this purpose.

@ECHO On  
SETLOCAL ENABLEDELAYEDEXPANSION

@SET _LOGFILE="C:\Export\LOGS\OpenAirMysqlImports.txt"
@SET _MYSQL="C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe"

@FOR /F "TOKENS=2,3,4 DELIMS=/ " %%A IN ('DATE /T') DO @( 
 @SET _MM=%%A
 @SET _DD=%%B
 @SET _YYYY=%%C
)

cd C:\openair

ECHO ------------------------------------------------------------------------------------------------------------- >> %_LOGFILE%
ECHO -- START %DATE% - %TIME% -- >> %_LOGFILE%
ECHO ------------------------------------------------------------------------------------------------------------- >> %_LOGFILE%

::  //////////////////////////////////////////////  
::  
::  Download the zip file from our repository.  
::

wget --quiet --ftp-user=MyUsername --ftp-password=MyPassword ftp://my.ftp.site/openair/openair_%_YYYY%-%_MM%-%_DD%.zip
SET _ERRORLVL==%ERRORLEVEL%
IF %_ERRORLVL%==1 SET _MSG=WGET Generic error code. 
IF %_ERRORLVL%==2 SET _MSG=WGET Parse error—for instance, when parsing command-line options, the ‘.wgetrc’ or ‘.netrc’... 
IF %_ERRORLVL%==3 SET _MSG=WGET File I/O error. 
IF %_ERRORLVL%==4 SET _MSG=WGET Network failure. 
IF %_ERRORLVL%==5 SET _MSG=WGET SSL verification failure. 
IF %_ERRORLVL%==6 SET _MSG=WGET Username/password authentication failure. 
IF %_ERRORLVL%==7 SET _MSG=WGET Protocol errors. 
IF %_ERRORLVL%==8 SET _MSG=WGET Server issued an error response.
IF NOT %_ERRORLVL%==0 ECHO #### WGET ERROR - %_MSG% #### >> %_LOGFILE% & GOTO SENDMAIL

::  //////////////////////////////////////////////  
::  
::  Delete the file we just downloaded.  
::
ECHO user MyUsername> ftpcmd.dat
ECHO MyPassword>> ftpcmd.dat
ECHO bin>> ftpcmd.dat
ECHO cd /openair/>> ftpcmd.dat
ECHO delete openair_%_YYYY%-%_MM%-%_DD%.zip>> ftpcmd.dat
ECHO quit>> ftpcmd.dat
ftp -n -s:ftpcmd.dat my.ftp.site
DEL ftpcmd.dat

:: //////////////////////////////////////////////
::
:: Delete the old sql scripts and extract the new
::

DEL /Q MYSQL*.SQL

7z.exe e -y openair_%_YYYY%-%_MM%-%_DD%.zip
SET _ERRORLVL==%ERRORLEVEL%
IF %_ERRORLVL%==1 SET _MSG=7ZIP Warning (Non fatal error(s)). For example, one or more files were locked by some other application, so they were not compressed.
IF %_ERRORLVL%==2 SET _MSG=7ZIP Fatal error
IF %_ERRORLVL%==7 SET _MSG=7ZIP Command line error
IF %_ERRORLVL%==8 SET _MSG=7ZIP Not enough memory for operation
IF %_ERRORLVL%==255 SET _MSG=7ZIP User stopped the process
IF NOT %_ERRORLVL%==0 ECHO #### 7z ERROR - %_MSG% #### >> %_LOGFILE% & GOTO SENDMAIL

::  //////////////////////////////////////////////  
::  
::  First test for existance of files, and  
::  notify us if they don't exist.  

DIR /B MYSQL*.SQL
IF NOT %ERRORLEVEL%==0 SET _MSG=No mysql files were found. & ECHO #### MYSQL FILE CHECK ERROR - NO FILES FOUND #### >> %_LOGFILE% & GOTO SENDMAIL

:: //////////////////////////////////////////////  
:: 
:: Insert the OpenAir database information.

DIR /B MYSQL*.SQL | FIND /V "" > FILES.TXT
FOR /F "tokens=1" %%f IN (FILES.TXT) DO (
ECHO -- Inserting %%f >> %_LOGFILE%
gsar "-sNOT NULL" -rNULL -o %%f
%_MYSQL% --user=MyUsername --password=MyPassword --database=openair --quick < %%f
)

DEL /Q FILES.TXT
DEL /Q OPENAIR_%_YYYY%-%_MM%-%_DD%.ZIP

:: //////////////////////////////////////////////  
:: 
:: Check to see if today is Sun, and insert our weekly
:: update if it is.

@SET _TODAY=%DATE:~0,3%

IF %_TODAY% == Sun %_MYSQL% --user=MyUsername --password=MyPassword --database=openair --quick < insert.sql

:: //////////////////////////////////////////////
::
:: Create the report table
::

%_MYSQL% --user=MyUsername --password=MyPassword --database=openair --quick < CreateReport.sql


:: /////////////////////////////////////////////
::
:: Export the PM reports
::

CALL C:\Export\ExportPmReports.cmd

GOTO EOF


:SENDMAIL  
:: //////////////////////////////////////////////  
::  
:: Set the temp file location  
SET _TEMPMAIL=%TEMP%\TEMPMAIL.%RANDOM%.TXT  
  
:: //////////////////////////////////////////////  
::  
:: Echo the basic headers to the temp file  
ECHO TO: "Croson, John" ^ > %_TEMPMAIL%  
ECHO FROM: "Merge Admin" ^ >> %_TEMPMAIL%  
ECHO SUBJECT: OpenAir MySQL Update Failure >> %_TEMPMAIL%  
  
:: //////////////////////////////////////////////  
::  
:: Echo the blank line that separates the header from the body text  
  
ECHO.>>%_TEMPMAIL%  
  
:: //////////////////////////////////////////////  
::  
:: Echo the body text to the temp file  
  
ECHO %_MSG% >> %_TEMPMAIL%  
ECHO Check %_LOGFILE% for details.>> %_TEMPMAIL%  
  
:: //////////////////////////////////////////////  
::  
:: Move the temp file to the mail pickup directory  
:: adjust this location for your system  
  
MOVE %_TEMPMAIL% C:\INETPUB\MAILROOT\PICKUP  
GOTO EOF  
  
  
:EOF  
EXIT


If you noticed a line in my code that references gsar, that bit turns all NOT NULL fields in the MySQL dump files into NULL fields. We use that so our inserts go a little easier when our home grown web service utility pings OpenAir periodically for updates to the booking_by_day table, and are updated in our database. This is a work in progress and if any one has interest, you can post to that effect and I'll see what I can do about getting that up here.

CreateReport.sql is a call to a store procedure that I wrote that rolls all project manager data into one huge report. Some of the data on this report comes from the previous download, and some comes from the historical table I insert data into on Sunday's. See below.

DROP PROCEDURE IF EXISTS openair.CreateReportTable;
CREATE PROCEDURE openair.`CreateReportTable`()
BEGIN

DROP TABLE IF EXISTS tblTempWeeklyReport2;

CREATE TABLE tblTempWeeklyReport2 AS
  SELECT P.ID AS "PROJECT_ID", 
  U.ID AS "USER_ID",
  U.NAME AS "USER_NAME",
    D.NAME AS "DEPARTMENT_NAME",
  C.NAME AS "CLIENT", 
  P.NAME AS "PROJECT_NAME",
    P.CUSTOM_31 AS "PRODUCT_TYPE",
  P.CUSTOM_40 AS "REV_REC_TYPE", 
  P.BUDGET AS "PS_REVENUE_USD", 
  P.BUDGET_TIME AS "PS_REVENUE_HOURS", 
    STR_TO_DATE(P.CUSTOM_68, '%Y-%m-%d') AS "PROJECTED_ACTUAL_CLOSED_DATE",
  (SELECT STR_TO_DATE(MAX(ENDDATE),'%Y-%m-%d') FROM BOOKING B WHERE PROJECT_ID=P.ID AND USER_ID=U.ID AND B.DELETED != '1') AS "LAST_BOOKING_DATE", 
  PS.NAME AS "STAGE", 
  P.NOTES AS "NOTES", 
  IFNULL(SUM(PT.PLANNED_HOURS),0) AS "PLANNED_HOURS_CURRENT",
  (SELECT IFNULL((SUM(MINUTE) / 60) + SUM(HOUR),0) FROM TASK T
    LEFT OUTER JOIN PROJECT_TASK PT ON PT.ID = T.PROJECT_TASK_ID WHERE T.PROJECT_ID=P.ID AND T.DELETED != '1'
     AND PT.NON_BILLABLE = '' AND PT.IS_A_PHASE = '') AS "WORKED_TO_DATE",
       (SELECT IFNULL(WORKED_HOURS,0) FROM PM_REPORT_HIST WHERE PROJECT_ID = P.ID AND USER_ID = U.ID AND YEARWEEK(`DATE`) = YEARWEEK(CURDATE()) -1) AS "WORKED_LAST_WEEK",
       (SELECT IFNULL(ROUND(SUM(HOURS),2),0) FROM BOOKING_BY_DAY WHERE PROJECT_ID=P.ID AND `DATE` >= CURDATE() AND deleted != '1') AS "REMAINING_BOOKED",
       (SELECT IFNULL(SUM(PLANNED_HOURS),0) FROM PM_REPORT_HIST WHERE PROJECT_ID = P.ID AND USER_ID = U.ID AND YEARWEEK(`DATE`) = YEARWEEK(CURDATE()) - 1) AS "PLANNED_HOURS_LAST_WEEK",
       (SELECT IFNULL(SUM(HOURS),0) FROM BOOKING_BY_DAY WHERE PROJECT_ID=P.ID AND DELETED != '1' AND `DATE` BETWEEN CURDATE() AND LAST_DAY_OF_QTR(CURDATE())) AS "BOOKED_HOURS_AS_OF_TODAY",
       (SELECT IFNULL(SUM(BOOKED_HRS_QTR),0) FROM PM_REPORT_HIST WHERE PROJECT_ID = P.ID AND `DATE` BETWEEN CURDATE()-7 AND LAST_DAY_OF_QTR(CURDATE())) AS "BOOKED_HOURS_AS_OF_LAST_WEEK",
       CASE WHEN UPPER(PS.NAME) = "CLOSED" THEN 100.00 END AS "CURRENT_PRCNT_COMPLETE_WORKED_DIV_PLANNED",
       CASE WHEN UPPER(PS.NAME) = "CLOSED" THEN 100.00 END AS "EOQ_ESTIMATE_PERC_COMPLETE",
       (SELECT IFNULL(PERCENT_COMPLETE,0.00) FROM PM_REPORT_HIST WHERE PROJECT_ID = P.ID AND `DATE` = DATE_SUB(LAST_WDOM(LAST_DAY_OF_QTR(CURDATE())), INTERVAL 1 QUARTER)) AS "PREVIOUS_QTR_COMPLETE",
       CASE WHEN UPPER(P.CUSTOM_40) LIKE '%DEFERRAL%' THEN 0000000000.00 
            WHEN UPPER(P.CUSTOM_40) LIKE 'COMPLETED CONT%' THEN 0000000001.00
            WHEN UPPER(P.CUSTOM_40) LIKE '\%% COMPLETE%' THEN 0000000002.00
       END AS "REVENUE_FORCAST"
FROM PROJECT P
 LEFT OUTER JOIN USER U ON U.ID = P.USER_ID
 LEFT OUTER JOIN PROJECT_STAGE PS ON PS.ID = P.PROJECT_STAGE_ID
 LEFT OUTER JOIN CUSTOMER C ON C.ID = P.CUSTOMER_ID
 LEFT OUTER JOIN PROJECT_TASK PT ON PT.PROJECT_ID = P.ID
  LEFT OUTER JOIN DEPARTMENT D ON D.ID = U.DEPARTMENT_ID
 WHERE PS.ID IN (2,3,4,6,7,9) AND PT.DELETED != 1 AND PT.NON_BILLABLE = '' AND PT.IS_A_PHASE = '' AND ( LEFT(D.NAME,6) IN ('013000', '016000', '016200','016500') OR D.NAME IS NULL )
GROUP BY P.ID
ORDER BY  1 ASC;

CREATE INDEX PID_UID ON TBLTEMPWEEKLYREPORT2 (PROJECT_ID, USER_ID);

---- SET THE WORKED DELTA
---- AND WORKED DELTA PERC
ALTER TABLE TBLTEMPWEEKLYREPORT2 ADD WORKED_DELTA DECIMAL(10,2) AFTER WORKED_LAST_WEEK;
ALTER TABLE TBLTEMPWEEKLYREPORT2 ADD DELTA_WORKED_PERC DECIMAL(10,2) AFTER WORKED_DELTA;
UPDATE TBLTEMPWEEKLYREPORT2 SET WORKED_DELTA  = IFNULL(WORKED_TO_DATE - WORKED_LAST_WEEK,0);
UPDATE TBLTEMPWEEKLYREPORT2 SET DELTA_WORKED_PERC = IFNULL(WORKED_DELTA / WORKED_LAST_WEEK, 0) * 100;

---- DELTA_OF_PLANNED_VS_WORKED_BOOKED
ALTER TABLE TBLTEMPWEEKLYREPORT2 ADD DELTA_OF_PLANNED_VS_WORKED_BOOKED DECIMAL(10,2) AFTER REMAINING_BOOKED;
UPDATE TBLTEMPWEEKLYREPORT2 SET DELTA_OF_PLANNED_VS_WORKED_BOOKED = PLANNED_HOURS_CURRENT - WORKED_TO_DATE - REMAINING_BOOKED;

---- DELTA_PERCENT
ALTER TABLE TBLTEMPWEEKLYREPORT2 ADD DELTA_PERCENT DECIMAL(10,2) AFTER DELTA_OF_PLANNED_VS_WORKED_BOOKED;
UPDATE TBLTEMPWEEKLYREPORT2 SET DELTA_PERCENT = (DELTA_OF_PLANNED_VS_WORKED_BOOKED / PLANNED_HOURS_CURRENT) * 100;

---- CHANGE_IN_PLANNED_HOURS
ALTER TABLE TBLTEMPWEEKLYREPORT2 ADD CHANGE_IN_PLANNED_HOURS DECIMAL(10,2) AFTER PLANNED_HOURS_LAST_WEEK;
UPDATE TBLTEMPWEEKLYREPORT2 SET CHANGE_IN_PLANNED_HOURS = (PLANNED_HOURS_CURRENT - PLANNED_HOURS_LAST_WEEK);

---- DELTA_PERCENT_1
ALTER TABLE TBLTEMPWEEKLYREPORT2 ADD DELTA_PERCENT_1 DECIMAL(10,2) AFTER CHANGE_IN_PLANNED_HOURS;
UPDATE TBLTEMPWEEKLYREPORT2 SET DELTA_PERCENT_1 = IFNULL(CHANGE_IN_PLANNED_HOURS / PLANNED_HOURS_LAST_WEEK,0) * 100;

---- PLANNED_HOURS_SUBT_PS_REVENUE_HOURS_BUDGET_HOURS
ALTER TABLE TBLTEMPWEEKLYREPORT2 ADD PLANNED_HOURS_SUBT_PS_REVENUE_HOURS_BUDGET_HOURS DECIMAL(10,2) AFTER DELTA_PERCENT_1;
UPDATE TBLTEMPWEEKLYREPORT2 SET PLANNED_HOURS_SUBT_PS_REVENUE_HOURS_BUDGET_HOURS = (PLANNED_HOURS_CURRENT - PS_REVENUE_HOURS);

---- CHANGE_IN_PLANNED_HOURS_FROM_PS_REVENUE_HOURS_BUDGET_HOURS
ALTER TABLE TBLTEMPWEEKLYREPORT2 ADD CHANGE_IN_PLANNED_HOURS_FROM_PS_REVENUE_HOURS_BUDGET_HOURS DECIMAL(10,2) AFTER PLANNED_HOURS_SUBT_PS_REVENUE_HOURS_BUDGET_HOURS;
UPDATE TBLTEMPWEEKLYREPORT2 SET CHANGE_IN_PLANNED_HOURS_FROM_PS_REVENUE_HOURS_BUDGET_HOURS = (PLANNED_HOURS_CURRENT - PLANNED_HOURS_SUBT_PS_REVENUE_HOURS_BUDGET_HOURS);

---- DELTA_PERCENT_3
ALTER TABLE TBLTEMPWEEKLYREPORT2 ADD DELTA_PERCENT_3 DECIMAL(10,2) AFTER CHANGE_IN_PLANNED_HOURS_FROM_PS_REVENUE_HOURS_BUDGET_HOURS;
UPDATE TBLTEMPWEEKLYREPORT2 SET DELTA_PERCENT_3 = IFNULL(CHANGE_IN_PLANNED_HOURS_FROM_PS_REVENUE_HOURS_BUDGET_HOURS / PLANNED_HOURS_SUBT_PS_REVENUE_HOURS_BUDGET_HOURS,0) * 100;

---- CHANGED
ALTER TABLE TBLTEMPWEEKLYREPORT2 ADD CHANGED DECIMAL(10,2) AFTER BOOKED_HOURS_AS_OF_LAST_WEEK;
UPDATE TBLTEMPWEEKLYREPORT2 SET CHANGED = (BOOKED_HOURS_AS_OF_TODAY - BOOKED_HOURS_AS_OF_LAST_WEEK);

---- PERCENT_CHANGE
ALTER TABLE TBLTEMPWEEKLYREPORT2 ADD PERCENT_CHANGE DECIMAL(10,2) AFTER CHANGED;
UPDATE TBLTEMPWEEKLYREPORT2 SET PERCENT_CHANGE = IFNULL(CHANGED / BOOKED_HOURS_AS_OF_LAST_WEEK, 0) * 100;

---- CURRENT_PRCNT_COMPLETE_WORKED_DIV_PLANNED
UPDATE TBLTEMPWEEKLYREPORT2 SET CURRENT_PRCNT_COMPLETE_WORKED_DIV_PLANNED =
  CASE WHEN (WORKED_TO_DATE / PLANNED_HOURS_CURRENT) * 100 > 100 THEN 100
       WHEN CURRENT_PRCNT_COMPLETE_WORKED_DIV_PLANNED = 100 THEN 100
  ELSE (WORKED_TO_DATE / PLANNED_HOURS_CURRENT) * 100
  END;
 UPDATE TBLTEMPWEEKLYREPORT2 SET CURRENT_PRCNT_COMPLETE_WORKED_DIV_PLANNED = 0
 WHERE CURRENT_PRCNT_COMPLETE_WORKED_DIV_PLANNED IS NULL OR 
 CURRENT_PRCNT_COMPLETE_WORKED_DIV_PLANNED = '';

---- EOQ_ESTIMATE_PERC_COMPLETE
UPDATE TBLTEMPWEEKLYREPORT2 SET EOQ_ESTIMATE_PERC_COMPLETE =
  CASE WHEN (WORKED_TO_DATE + BOOKED_HOURS_AS_OF_TODAY) / PLANNED_HOURS_CURRENT * 100 > 100 THEN 100
       WHEN UNIX_TIMESTAMP(PROJECTED_ACTUAL_CLOSED_DATE) != 0 THEN
          (CASE WHEN PROJECTED_ACTUAL_CLOSED_DATE < LAST_DAY_OF_QTR(CURDATE()) THEN 100 END)
       WHEN UNIX_TIMESTAMP(LAST_BOOKING_DATE) != 0 THEN
          (CASE WHEN LAST_BOOKING_DATE < LAST_DAY_OF_QTR(CURDATE()) THEN 100 END)
  ELSE #WORKED + BOOKED HOURS AS OF TODAY TO END OF QTR / PLANNED HOURS
      (WORKED_TO_DATE + BOOKED_HOURS_AS_OF_TODAY) / PLANNED_HOURS_CURRENT * 100
  END;
 UPDATE TBLTEMPWEEKLYREPORT2 SET EOQ_ESTIMATE_PERC_COMPLETE = 0
 WHERE EOQ_ESTIMATE_PERC_COMPLETE IS NULL OR 
 EOQ_ESTIMATE_PERC_COMPLETE = '';
 
 ---- IN_QTR_GAIN
 ALTER TABLE TBLTEMPWEEKLYREPORT2 ADD IN_QTR_GAIN DECIMAL(10,2) AFTER EOQ_ESTIMATE_PERC_COMPLETE;
 UPDATE TBLTEMPWEEKLYREPORT2 SET IN_QTR_GAIN = IFNULL(EOQ_ESTIMATE_PERC_COMPLETE,0.00) - IFNULL(PREVIOUS_QTR_COMPLETE,0.00);
 
 ---- REVENUE_FORCAST
 UPDATE TBLTEMPWEEKLYREPORT2 SET REVENUE_FORCAST = 
 CASE WHEN REVENUE_FORCAST = 1.00 AND EOQ_ESTIMATE_PERC_COMPLETE = 100.00 AND PREVIOUS_QTR_COMPLETE < 100.00 THEN PS_REVENUE_USD
      WHEN REVENUE_FORCAST = 2.00 THEN IN_QTR_GAIN * PS_REVENUE_USD / 100
  END;
  
  ---- REALIZATION_RATE
  ALTER TABLE TBLTEMPWEEKLYREPORT2 ADD REALIZATION_RATE DECIMAL(10,2) AFTER REVENUE_FORCAST;
  UPDATE TBLTEMPWEEKLYREPORT2 SET REALIZATION_RATE =
  CASE WHEN UPPER(STAGE) = "CLOSED" THEN (PS_REVENUE_USD / WORKED_TO_DATE)
  ELSE
       (PS_REVENUE_USD / PLANNED_HOURS_CURRENT)
  END;

---- Delete the old report table instead of selecting into since columns don't appear to be created correctly
DROP TABLE IF EXISTS TBLWEEKLYREPORT;

---- Rename our new report data
RENAME TABLE TBLTEMPWEEKLYREPORT2 TO TBLWEEKLYREPORT;


END;


Insert.sql that is executed on Sunday's is a call to a query that inserts project management historical data for reporting above. See below.
INSERT INTO pm_report_hist ( project_id, user_id, date, planned_hours, remaining_booked, booked_hrs_qtr, worked_hours, percent_complete )
 SELECT P.ID, 
  U.ID, 
  CURDATE(),
  IFNULL(SUM(PT.PLANNED_HOURS),0), -- #### Planned Hours
  (SELECT IFNULL(ROUND(SUM(HOURS),2),0) FROM BOOKING_BY_DAY 
        WHERE PROJECT_ID=P.ID AND `DATE` >= CURDATE() AND DELETED != '1'), -- #### Remaining Booked
  (SELECT IFNULL(ROUND(SUM(HOURS),2),0) FROM BOOKING_BY_DAY 
        WHERE PROJECT_ID=P.ID AND DELETED != '1' AND `DATE` 
          BETWEEN CURDATE() AND LAST_DAY_OF_QTR(CURDATE())), -- #### booked hours qtr
    (SELECT IFNULL((SUM(MINUTE) / 60) + SUM(HOUR),0) FROM TASK T
    LEFT OUTER JOIN PROJECT_TASK PT ON PT.ID = T.PROJECT_TASK_ID WHERE T.PROJECT_ID=P.ID AND T.DELETED != '1'
     AND PT.NON_BILLABLE = '' AND PT.IS_A_PHASE = ''),  -- #### Worked Hours
    CASE WHEN UPPER(PS.NAME) = "CLOSED" THEN 100
      WHEN (SELECT IFNULL((SUM(MINUTE) / 60) + SUM(HOUR),0) FROM TASK  T
            LEFT OUTER JOIN PROJECT_TASK PT ON PT.ID = T.PROJECT_TASK_ID WHERE T.PROJECT_ID=P.ID AND T.DELETED != '1'
             AND PT.NON_BILLABLE = '' AND PT.IS_A_PHASE = '') /
           IFNULL(SUM(PT.PLANNED_HOURS),0) > 100 THEN 100
    ELSE (SELECT IFNULL((SUM(MINUTE) / 60) + SUM(HOUR),0) FROM TASK  T
          LEFT OUTER JOIN PROJECT_TASK PT ON PT.ID = T.PROJECT_TASK_ID WHERE T.PROJECT_ID=P.ID AND T.DELETED != '1'
           AND PT.NON_BILLABLE = '' AND PT.IS_A_PHASE = '') /
         IFNULL(SUM(PT.PLANNED_HOURS),0) * 100 -- #### Percent Complete
      END
FROM PROJECT P
 LEFT OUTER JOIN USER U ON U.ID = P.USER_ID
 LEFT OUTER JOIN PROJECT_TASK PT ON PT.PROJECT_ID = P.ID
 LEFT OUTER JOIN PROJECT_STAGE PS ON PS.ID = P.PROJECT_STAGE_ID
  LEFT OUTER JOIN DEPARTMENT D ON D.ID = U.DEPARTMENT_ID
 WHERE PS.ID IN (2,3,4,6,7,9) AND PT.DELETED != 1 AND PT.NON_BILLABLE = '' AND PT.IS_A_PHASE = '' AND ( LEFT(D.NAME,6) IN ('013000', '016000', '016200','016500') OR D.NAME IS NULL )
GROUP BY P.ID
ORDER BY  1 ASC;

Once this data is populated, an external reporting tool called Clickbase is used to create individual reports from the table that is created by the store procedure, and dump them neatly into a Windows share every day in Excel format. This was quite an effort and represents a number of hours of development time, not to mention meetings with my liaison from finance that specializes in financial reporting.
Enhanced by Zemanta