Web Dev - On to testing.

Long post. Buckle up.

OK, the project is just about done. We're in test at the moment, which begins next week. A little tweak here, and a little tweak there, and we're off to production.

Here's what the thing does. I'm going to try and keep this as sanitized as possible. I like my job. We process documents that make sure people get money for things they do. There's a threshhold time for processing these documents, which is 45 days. The report I converted to web-based tells you two things. One, how many of these documents are getting processed inside and outside of the 45 day limit. Two, of the two types of documents, which ones in the reporting period were late and where are they now.

What I start out with is a list of documents, the date they came in, and the date they were completed. Easy enough, right? Wrong. Of the two types of documents, one should be processed in 45 calendar days. The other gets the additional leeway of being processed in 45 business days. So. How do we differentiate between the two, easily?

The first part of the solution, I have already posted, and that was the creation of the nbd_lookup table, so that you could get a count of how many non-business days were in the reporting period. Here's how I did the rest:

You start out at the first web page, and are asked for the date range you want to report on, and the month in which the first day of the date range resides. (I just realized I could simplify that immensely by using the month variable for the first date in the date range as the last variable in the string. I'll fix that later.) Each part of each date, (mm, dd, yyyy) is a seperate variable. So you start out with 7 (6) variables. One for each field of the starting and ending dates, and the first julian date of the month. Those are passed via a form to the next page, which basically runs an sql stored procedure. The SQL looks like this:

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
exec sp_reportgen '$l_smm $l_sdd $l_syyyy 12:00AM', '$l_emm $l_edd $l_eyyyy 11:59PM', '$l_julian

Oddly enough, you have to use the ANSI_NULLS and ANSI_WARNINGS switches when running an SQL stored procedure from PHP, or the query tanks. No idea why. It's a known bug, but MS doesn't seem all that interested in fixing it.

The stored procedure then grabs the appropriate rows from the table that holds all the document processing information. It's one long-assed SP and I'm not going to post it here. If you really want to see it, email me. Once that's done we have two tables to select from, for the two types of data we want. The processing page then displays a 'View Report" button. I'm working on a way to get around that and just go straight to the report page. Everything is a bit of a hack at this point. I'm kind of learning as I go.

So on the report page, we run three queries. The first is kind of a 'select *' with some use of the CAST operator thrown in, to generate the percentages for the documents that were processed in time...

select Type, Under, Ovr as 'Over', Total,
substring(cast(cast(Under as decimal)/cast(Total as decimal) * 100 as char),1,5)+ '%'
as 'Pct Under'
from output

The next two select the documents that were processed in > 45 days from the original date of receipt. The first one is just a straight up "select * from table where age > 45 and documenttype = 'type1'" The second one though, does two things. First, it gets the number of non-business days:

select count(*) nonbusdays
from nbd_lookup
where julian >= $l_julian
and caldate <= '$l_emm $l_edd $l_eyyyy'
and (weekend = 1 or holiday = 1)

Recognize the date variables from the first query? Second, we take that row count and use it as a variable called $nonbusdays in the query that will tell us how many documents were processed in > 45 business days:

select date, docnumber, Age, field1, Loc, RC, Type from clean
where age > (45 + $nonbusdays)
and type = 'type2'
order by date, type, docnumber

Output goes to a standard html table via PHP code like this:

while ($col_query3 = mssql_fetch_array($runquery3)) {
$date = $col_query3["Date"];
$docnumber = $col_query3["docnumber"];
$age = $col_query3["Age"];
$field1 = $col_query3["field1"];
$loc = $col_query3["Loc"];
$rc = $col_query3["RC"];
$ctype = $col_query3["Type"];

Then, you can just echo the variables inside table cells, until you run out of rows in the array. I can't post that part of the code here, since blogger seems to interpret that as html.

Also, just as what I call a sanity check, I placed, in PHP comments, lines that echo the queries to SQL, so that all I have to do is uncomment the echo line to see what the PHP pages are sending to SQL. Kind of cool.

If anyone is interested in seeing the complete code, email me. I'd be glad to share a cleaned up version of it. (Got to protect my employer.)

Enjoy.
-D.