table1 = $wpdb->prefix . 'states';
$this->table2 = $wpdb->prefix . 'state_ranking';
$this->table3 = $wpdb->prefix . 'state_rank_national_avg';
}
function buildTable(){
global $wpdb;
$sql = "CREATE TABLE ".$this->table1." (
`code` varchar(2) NOT NULL,
`state` varchar(20) DEFAULT NULL,
`country` varchar(20) NOT NULL,
PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
$sqla = "
LOAD DATA LOCAL INFILE '".get_home_path()."wp-content/plugins/stateranking/states.csv'
INTO TABLE ".$this->table1."
FIELDS TERMINATED BY '\t' ENCLOSED BY '\"'
LINES TERMINATED BY '\n' IGNORE 1 LINES (code,state,country);
";
if($wpdb->get_var("SHOW TABLES LIKE '%".$this->table1."'") != $this->table1){
$wpdb->query($sql);
$wpdb->query($sqla); //Fill in with states.
}
$sql = "CREATE TABLE ".$this->table2." (
`rank_id` int(11) NOT NULL AUTO_INCREMENT,
`year` smallint(4) DEFAULT NULL,
`state_abbv` varchar(2) DEFAULT NULL,
`state` varchar(30) NOT NULL,
`rank` smallint(6) DEFAULT NULL,
`tax_regulation` decimal(5,2) DEFAULT NULL,
`workforce_quality` decimal(5,2) DEFAULT NULL,
`living_environment` decimal(5,2) DEFAULT NULL,
`pct_growth` decimal(5,2) DEFAULT NULL,
`pct_growth2natavg` decimal(5,2) DEFAULT NULL,
`pct_unemployment` decimal(5,2) DEFAULT NULL,
`pct_unemployment2natavg` decimal(5,2) DEFAULT NULL,
`net_migrations` int(11) DEFAULT NULL,
`rank_migration` smallint(4) DEFAULT NULL,
`debt_per_resident_per_capita` int(11) DEFAULT NULL,
`employees_per10k_residents` int(11) DEFAULT NULL,
`pct_tax_burden_rate` decimal(5,2) DEFAULT NULL,
`pct_tax_burden_rate2natavg` decimal(5,2) DEFAULT NULL,
`key_companies` text,
`dev_trend_direction` varchar(30) DEFAULT NULL,
`comments_dev_trend` text,
`comment_state` text,
`comments_ceo` text,
`created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Created Date',
`updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Updated Date',
PRIMARY KEY (`rank_id`),
KEY `year_idx` (`year`),
KEY `state_idx` (`state_abbv`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
if($wpdb->get_var("SHOW TABLES LIKE '%".$this->table2."'") != $this->table2){
$wpdb->query($sql);
}
$sql = "CREATE TABLE ".$this->table3." (
`natavg_id` int(11) NOT NULL AUTO_INCREMENT,
`year` smallint(4) DEFAULT NULL,
`pct_growth_natavg` decimal(5,2) DEFAULT NULL,
`pct_unemployment_natavg` decimal(5,2) DEFAULT NULL,
`pct_tax_burden_natavg` decimal(5,2) DEFAULT NULL,
`created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Created Date',
`updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Updated Date',
PRIMARY KEY (`natavg_id`),
KEY `year_idx` (`year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
if($wpdb->get_var("SHOW TABLES LIKE '%".$this->table3."'") != $this->table3){
$wpdb->query($sql);
}
}
/*
* Generate data into a spread sheet of states listed by best to worst order.
* Include links to state template viewing each state.
*/
function getStateList($atts){
wp_enqueue_style('stateranking',plugins_url('stateranking/'). 'css/staterank.css', array(), '1.1.0');
wp_enqueue_script('angularJS','//ajax.googleapis.com/ajax/libs/angularjs/1.4.0-rc.1/angular.min.js',true);
global $wpdb;
$url_arr = preg_split('/\//', $_SERVER['REQUEST_URI']);
if(isset($atts['year'])){
$year = $atts['year'];
} else {
$year = array_pop($url_arr);
}
if(!preg_match ( '/[0-9]{4}/' , $year )) $year = date('Y');
$prevyear = $year - 1;
$sql = "SELECT a.year,a.state_abbv,a.state, a.rank, b.rank AS prevrank, (b.rank - a.rank) AS rankdiff
FROM ".$this->table2." a INNER JOIN
(SELECT year,state_abbv,rank FROM ".$this->table2." WHERE year = '".$prevyear."') AS b
ON a.state_abbv = b.state_abbv
WHERE a.year = '".$year."' ORDER BY a.rank";
$sqlnoprevyear = "SELECT `year`,state, rank , 0 AS rankdiff FROM ".$this->table2."
WHERE year = '".$year."'";
$rs = $wpdb->get_results($sql);
if(!$rs){
$rs = $wpdb->get_results($sqlnoprevyear);
}
$json = json_encode($rs, JSON_NUMERIC_CHECK);
$lines = "";
//$class = "odd";
$page = (isset($atts['detailpage'])) ? "/".$atts['detailpage']."/" : "/statedetail/" ;
include_once("ranklistdisplay.php");
}
function getState($atts){
wp_enqueue_style('statedetail',plugins_url('stateranking/'). 'css/statedetail.css', array(), '1.1.0');
$url_arr = preg_split('/\//', $_SERVER['REQUEST_URI']);
$state = $_GET['state'];
$st = array_pop($url_arr);
$year = array_pop($url_arr);
global $wpdb;
$sql = "SELECT r.*, a.* FROM ".$this->table2." r LEFT JOIN ".$this->table3." a
ON r.year = a.year WHERE r.year = '".$year."' AND state_abbv = '".$state."'";
$row = $wpdb->get_row($sql);
$prev = $row->rank-1;
$next = $row->rank+1;
$sql = "SELECT state,state_abbv, rank FROM ".$this->table2."
WHERE year = '".$year."' AND rank IN(".$prev.",".$next.") ORDER BY rank";
$rankrow = $wpdb->get_results($sql);
if(isset($rankrow[1]->state_abbv)){
$prev = $rankrow[0]->state_abbv;
$prevstate = $rankrow[0]->state;
$next = $rankrow[1]->state_abbv;
$nextstate = $rankrow[1]->state;
} elseif ($rankrow[0]->rank >= 49) {
$prev = $rankrow[0]->state_abbv;
$prevstate = $rankrow[0]->state;
$next = "";
} elseif ($rankrow[0]->rank <= 2) {
$next = $rankrow[0]->state_abbv;
$nextstate = $rankrow[0]->state;
$prev = "";
}
$q = preg_split('/\?/', $_SERVER['REQUEST_URI']);
$stateimg = strtolower(preg_replace('/ /','',$row->state));
$year_abbr = $year - 2000;
//$growth2natavg = $row->pct_growth - $row->pct_growth_natavg;
//$pct_unemployment2natavg = $row->pct_unemployment - $row->pct_unemployment_natavg;
//$pct_tax_burden_rate2natavg = $row->pct_tax_burden_rate - $row->pct_tax_burden_natavg;
$imagesdir = plugin_dir_url( __FILE__ )."images/";
$taxreg = round($row->tax_regulation) * 10 + 1;
$workforce = round($row->workforce_quality) * 10 + 1;
$living = round($row->living_environment) * 10 + 1;
$keycos = preg_replace('/\\n/', '
', $row->key_companies);
$trend_arr = array('Up' => 'up_arrow.png','Down' => 'down_arrow.png','Sideways' => 'neutral.png' );
$trendimg = $trend_arr[$row->dev_trend_direction];
$trend = $row->dev_trend_direction;
if($trend == 'Sideways') $trend = "No change";
//$commentsceo = $row->comments_ceo;
//$commentsceo = preg_replace('/((”)\s+(“))/',"$1$2",$row->comments_ceo);
$commentsceo = preg_replace('/\n/',"
",$row->comments_ceo);
include_once("statedetail.php");
}
}
register_activation_hook( __FILE__, function(){
$srank = new StateRanking;
$srank->buildTable();
} );
add_shortcode( 'statelist', function($atts){
ob_start();
$srank = new StateRanking;
echo $srank->getStateList($atts);
return ob_get_clean();
});
add_shortcode( 'statedetail', function($atts){
$srank = new StateRanking;
return $srank->getState($atts);
} );
?>
