Portfolio: Jared Coffin

Organizing Knowledge and Media Production

Main menu

Skip to content
  • IT / Data
  • Sample Code GitHub
  • Music Production
  • Caroline
  • Andrew
  • Contact

WebMD – August to November 2012

Posted on July 12, 2014 by jaredc

WebMD, New York, NY

Medscape PI-CME

Medscape PI-CME

This entry was posted in Data Analysis. Bookmark the permalink.

Post navigation

← ADP, Waterbury, CT
Colangelo – Darien, CT →

Connect with:

Data Analysis

  • Taxonomy
    An an ever-changing complex web of categories and subcategories.
  • REST API
    Returns XML or JSON data from a web query.
  • PowerShell
    PowerShell like many data report apps, take advantage of Regular Expressions.
  • Power BI
    Displays graphs based on data setup with Query Editor.
  • Alteryx
    Disorganized data can be cleaned by using the regular expressions tool to divide crowded columns.
© Portfolio: Jared Coffin
CEO - AngularJS

<?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>

CEO - Best/Worst States Plugin 
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);
} );
?>
MySQL code sample 
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;
Elizabeth Arden - PHP 
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' );
Elizabeth Arden - JavaScript 
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();
});