Tables - Revision history

Gskluzacek: added some notes

2018-10-24T03:16:12Z

added some notes

2018-10-24T02:52:01Z

added info on override_pvhl_id

2018-10-24T02:46:29Z

meh

2018-10-24T01:43:35Z

added details on how fields are populated

2018-10-24T01:34:16Z

updated with details on how fields are populated

2018-10-23T02:10:41Z

new

← Older revision		Revision as of 03:16, 24 October 2018
Line 133:		Line 133:

	== previews_raw ==		== previews_raw ==
		+
		+	NOTE:
		+	* the FEB09_COF.txt file had 2 tab characters between the solicitation code and the solicitation text (instead of just 1 tab) so if the normal position for the solicitation text is blank then adjust the index for all columns past the solicitation code by 1
		+	* may be we have logic in the new python version of the code that auto corrects this issue and populates the CORRECTED_TEXT field for the record with a comment of removed repeated tab chars

	CREATE TABLE IF NOT EXISTS `previews_raw` (		CREATE TABLE IF NOT EXISTS `previews_raw` (

← Older revision		Revision as of 02:52, 24 October 2018
Line 97:		Line 97:

	updt_dt current time stamp		updt_dt current time stamp
		+
		+	NOTES:
		+	* if the override_pvhl_id is 0 then the pp.php script skips processing that line.
		+	* if non-null and not zero, the heading level & name are looked up (on the pvhl_id)
		+	* the columns: pv_type, hdg_1, h1_id, hdg_2, h2_id, hdg_3, h3_id are set based on the heading level

	CREATE TABLE IF NOT EXISTS `previews_lines` (		CREATE TABLE IF NOT EXISTS `previews_lines` (

@@ Line 146: / Line 146: @@
        `unit_price` decimal(6,2) DEFAULT NULL,
        `pi_ind` varchar(1) DEFAULT NULL,
        `title` varchar(100) DEFAULT NULL,
        `sub_title` varchar(100) DEFAULT NULL,

← Older revision		Revision as of 01:43, 24 October 2018
Line 83:		Line 83:

	== previews_lines ==		== previews_lines ==
		+
		+	also loaded from the lf.php script
		+
		+	pvl_id will AUTO_INCREMENT
		+
		+	pvh_id set to id from the previews_hdr insert (AUTO_INCREMENT)
		+
		+	pvl_seq set to a counter that is incremented in the while loop that reads the file
		+
		+	line_text set to the line read from the file (with fgets)
		+
		+	override_pvhl_id defaulted to NULL
		+
		+	updt_dt current time stamp

	CREATE TABLE IF NOT EXISTS `previews_lines` (		CREATE TABLE IF NOT EXISTS `previews_lines` (

← Older revision		Revision as of 01:34, 24 October 2018
Line 35:		Line 35:

	== previews_hdr ==		== previews_hdr ==
		+
		+	This table is loaded by the lf.php script (load file?)
		+
		+	pvh_id will AUTO_INCREMENT
		+
		+	The IDENT_STR column is populated from the first line in the file as follows
		+	$ident = explode(' ', rtrim($line))
		+	$ident_str = 'PREVIEWS VOL ' . str_pad($ident[3], 3, '0', STR_PAD_LEFT) . ' ' . str_pad($ident[4], 2, '0', STR_PAD_LEFT) . ' ' . substr($ident[1], 0, 3) . '-' . ($ident[3] + 2000 - 10);
		+
		+	the PERIOD_DT column is populated from the filename as follows
		+
		+	uses the MMMYY string to create a DD-MMM-YYYY string and then converts it to a string
		+
		+	Example file name AUG14_COF.txt
		+	list($period_str) = explode('_', $file);
		+	$period_dt = date('Y-m-01', strtotime('01-' . substr($period_str, 0, 3) . '-20' . substr($period_str, 3, 2)));
		+
		+	local file is set to the downloaded file name: e.g., AUG14_COF.txt
		+
		+	url_to_cof was set to NULL (future functionality to populate it).
		+
		+	proc_status defaulted to NEW
		+
		+	updt_dt defaulted to current date time

	CREATE TABLE IF NOT EXISTS `previews_hdr` (		CREATE TABLE IF NOT EXISTS `previews_hdr` (