Knowledge Organiser and Data Analyst by SQL query and REST calls. Tools used are Power BI, Alteryx, Excel, as well as coding SQL for queries and data changes. Experience implementing Taxonomy for Policy Center and metadata fields.
Data Analyst – KPMG
<?php
$url = home_url($page . $year);
?>
<section id=“stateranking” ng-app=“stateranking”>
<h2><?=$year ?> BEST AND WORST STATE RANKINGS.</h2>
<h4>To custom sort the table, select the appropriate header</h4>
<div ng-controller=“statelistController”>
<table>
<tr class=“header”>
<th><a href=“” ng-click=“predicate = ‘rank’; reverse=!reverse”>RANK</a></th>
<th><a href=“” ng-click=“predicate = ‘state’; reverse=!reverse”>STATE</a></th>
<th><a href=“” ng-click=“predicate = ‘prevrank’; reverse=!reverse”>LAST YEAR’S RANK</a></th>
<th><a href=“” ng-click=“predicate = ‘rankdiff’; reverse=!reverse”>CHANGE</a></th>
</tr>
<tr ng-repeat=“state in stateranklist | orderBy:predicate:reverse”>
<td class=“rank”>{{state.rank}}</td>
<td class=“state”><a href=“<?=$url ?>/?state={{state.state_abbv}}” target=”_blank“>{{state.state}}</a></td>
<td>{{state.prevrank}}</td>
<td>{{state.rankdiff}}</td>
</tr>
</table>
</div>
</section>
<script>
jQuery(document).ready(function($){
if(window.innerWidth < 496){
$(“#stateranking table th:gt(1),#stateranking table td:gt(1)”).remove();
}
angular.module(‘stateranking’, []).controller(‘statelistController’, [‘$scope’, function($scope) {
$scope.stateranklist = <?=$json ?>;
$scope.predicate = ‘rank’;
}]);
});
</script>
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);
} );
?>
SET @swatchcsv := ''; SET @tmpFamily := ''; CREATE TEMPORARY TABLE tmp_swatch # ( SELECT prod_id,item_family, p.item_number,@tmpFamily, @swatchcsv := IF(@tmpFamily = item_family,CONCAT(@swatchcsv,p.item_number,','),CONCAT(p.item_number,',') ) AS swatch_item_number, @tmpFamily := IF(@tmpFamily = item_family,@tmpFamily,item_family) as fam FROM prod_swatches_ a INNER JOIN prod_es_ p ON a.item_number = p.item_number ORDER BY item_family,ranking,a.item_number #IF(ranking > 0,ranking,a.item_number) ); UPDATE prod_es_ p,tmp_swatch t SET p.swatch_item_number = t.swatch_item_number WHERE t.item_number = p.item_number; DROP TABLE tmp_swatch;
if ( count( $page_products ) ) {
$products = $pg->findByItemNumbers( $page_products, array( 'prod_id', 'item_number', 'prod_name', 'desc_short','swatch_item_number' ) );
} else {
$var['tpl_right'] = 'invalid_family_page.tpl';
header("HTTP/1.0 404 Not Found");
}
$db = new db( GC_PRODUCTS_DB_HOST, GC_PRODUCTS_DB_USER, GC_PRODUCTS_DB_PASSWORD, GC_PRODUCTS_DB_NAME );
foreach($products as $key => $row){
$swatchitems = preg_replace('/,/', '\',\'', $row->swatch_item_number);
$sql = "SELECT * FROM prod_swatches".TABLE_EXT." WHERE color > '' AND item_number IN ('".$swatchitems."')";
$sth = $db->prepare($sql);
$sth->execute();
$rs = $sth->fetchall();
$products[$key]->colorcount = count($rs);
}
require( DIR_TPL . 'family.tpl' );
jq1111 = jQuery.noConflict(true);
var sevenlooks7daysApp = (function(){
var products = {image:["1002vdsc400_fce0bd","1002vdsc400_836a5c","1002vdsc400_a48b7e",
"1002vdsc400_75889a","1002bflc400_ed8172","1002bflc400_a25556","1002bflc400_cf3580"],
names:["Beautiful Color Eye Shadow Bone",
"Beautiful Color Eye Shadow Chocolate",
"Beautiful Color Eye Shadow Bronze Lustre",
"Beautiful Color Eye Shadow Mediterranean",
"Beautiful Color Luminous Lip Gloss Coral Kiss",
"Beautiful Color Luminous Lip Gloss Royal Plum",
"Beautiful Color Luminous Lip Gloss Passion Fruit"],
price:["$18.50","$18.50","$18.50","$18.50","$18","$18","$18"],
links: links
};
var days = [
"#1002vdsc400_fce0bd,#1002vdsc400_836a5c,#1002bflc400_ed8172",
"#1002vdsc400_fce0bd,#1002vdsc400_836a5c,#1002vdsc400_a48b7e,#1002bflc400_ed8172",
"#1002vdsc400_fce0bd,#1002vdsc400_836a5c,#1002vdsc400_75889a,#1002bflc400_a25556",
"#1002vdsc400_fce0bd,#1002vdsc400_a48b7e,#1002vdsc400_75889a,#1002bflc400_a25556",
"#1002vdsc400_fce0bd,#1002vdsc400_a48b7e,#1002bflc400_cf3580",
"#1002vdsc400_fce0bd,#1002vdsc400_75889a,#1002bflc400_ed8172",
"#1002vdsc400_fce0bd,#1002vdsc400_836a5c,#1002vdsc400_a48b7e,#1002vdsc400_75889a,#1002bflc400_ed8172"
];
function setCycle2(){
jq1111('#slideshow').cycle({
speed: 600,
manualSpeed: 100,
slides: '>div',
slideClass: 'day',
timeout: 0,
pager: '>#sevendays'
});
}
function slideMoved(event, opts){
var idx = opts.nextSlide;
jq1111("#sevendays li").removeClass("active");
jq1111("#sevendays li:eq("+idx+")").addClass("active");
jq1111("#shoplooks div").css({"display":"none"});
jq1111(days[idx]).css({"display":"block"});
var slide = (".cycle-slide-active");
var zoomImg = jq1111(slide+" .leftcol>img").attr("data-zoom-image");
var bgf = jq1111(".zoomWindow").filter(function(){
return jq1111(this).css("background-image").match(zoomImg);
});
jq1111(".zoomContainer").removeClass("zontop");
bgf.parent().parent().addClass("zontop");
}
function setAction(){
jq1111("#sevendays li").click( function(){
var idx = jq1111(this).index();
jq1111('#slideshow').cycle('goto', idx);
});
}
var zoom = function(){
jq1111('#zoom0,#zoom1,#zoom2,#zoom3,#zoom4,#zoom5,#zoom6').elevateZoom({
zoomScroll: true,
zoomType: "inner",
cursor: "crosshair",
zoomWindowFadeIn: 500,
zoomWindowFadeOut: 750,
responsive: true
});
};
var zoomInit = function (){
setTimeout(function () {
var bgf = jq1111(".zoomWindow").filter(function(){
return jq1111(this).css("background-image").match(/Monday/);
});
bgf.parent().parent().addClass("zontop");
}, 3000);
};
function initProducts(){
var imgUrl = "http://demandware.edgesuite.net/sits_pod18/dw/image/v2/AAHP_PRD/on/demandware.static/Sites-ElizabethArden-Site/Sites-elizabetharden-master-catalog/default/v1406056357964/images/";
var imgSize = "?sw=259&sh=259";
var stars = "http://staging.elizabetharden.pfsweb.demandware.net/on/demandware.static/Sites-ElizabethArden-Site/-/default/v1404762612979/images/stars/rating-5_0.gif";
jq1111.each(products.image,function(idx,val){
var elem = '<div id="'+val+'" class="product producttile">';
elem += '<a href="'+products.links[idx]+'"><img src="'+imgUrl+val+'.jpg'+imgSize+'" />';
elem += '<p>'+products.names[idx]+'<br>'+products.price[idx]+' >></p></a><p><img src="'+stars+'" /></p></div>';
jq1111("#shoplooks").append(elem);
});
jq1111(days[0]).css({"display":"block"});
}
return{
init : function (){
setCycle2();
jq1111('#slideshow').on( 'cycle-after', slideMoved );
zoom();
zoomInit();
setAction();
initProducts();
}
};
})();
jq1111(document).ready(function(){
sevenlooks7daysApp.init();
});