cj - '.$foreign_id.'';
} elseif ($prog=='s') {
$rv = " view - links";
}
return $rv;
}
function lastQuarter() {
displayHead('Last Quarter Report');
$last = sqlValue('SELECT max(import_id) FROM Rev_Import');
if ($last > 0) {
$sql="SELECT h.merchant_id 'ID', m.active_cd 'A', m.merchant_nm 'Merchant', m.prog 'pg', m.tot_hits 'Tot Hits', m.tot_commissions 'Tot Earnings', sum(h.impressions) imprs, sum(h.hits) Hits, sum(h.actions) Orders, sum(h.sales_amt) Sales, sum(h.revenue_amt) Earnings
FROM Rev_Hist h, Merchant m
WHERE h.import_id = $last
and h.merchant_id = m.merchant_id
GROUP BY h.merchant_id
ORDER BY Hits DESC";
sqlTable('Last Quarter Rpt','',$sql);
}
}
function listGoldenCan () {
$sql = 'SELECT concat(\'\',can_id,\'\') can_id, g.merchant_id, g.cat_id, g.store_nm, length(g.top_ad) len,
m.merchant_nm, m.prog, m.city, m.state_cd, m.foreign_id
FROM Golden_Can g
LEFT JOIN Merchant m ON g.merchant_id = m.merchant_id
ORDER BY m.prog, m.merchant_nm';
displayHead('Golden Can Stores');
msgHTML();
sqlTable('Golden Can Merchants','',$sql);
}
function editGoldenCanForm() {
$can_id = isset($_REQUEST['can_id'])? intval($_REQUEST['can_id']) : 0;
if ($can_id > 0) {
$sql='SELECT g.merchant_id, g.cat_id, g.sod_dt, g.store_nm, g.top_ad, g.html_txt, m.merchant_nm, m.prog, m.city, m.state_cd, m.foreign_id
FROM Golden_Can g
LEFT JOIN Merchant m ON g.merchant_id = m.merchant_id
WHERE can_id = '.$can_id;
list($merchant_id, $cat_id, $sod_dt, $store_nm, $top_ad, $html_txt, $merchant_nm, $prog, $city, $state_cd, $foreign_id) = sqlRow($sql);
if ($merchant_id > 0) {
$chkDt = sqlValue('SELECT dt FROM Merchant_Cool WHERE merchant_id = '.$merchant_id);
if ($chkDt > '0000-00-00' and $chkDt != $sod_dt) {
msgNoteSuccess('Updating SOD date from '.$sod_dt.' to '.$chkDt);
$sod_dt = $chkDt;
}
}
displayHead('Edit Golden Can Record '.$can_id.' '.$store_nm);
//echo ''.$sql.'
';
msgHTML();
hForm('aMechant.html');
echo inpHidden('can_id',$can_id);
hTable('Edit Golden Can Record');
echo '| This form lets you edit a golden can store record for a merchant. |
';
hPC('Merchant ID',inpTxt('merchant_id',$merchant_id,10,10));
hPC('Store Name',inpTxt('store_nm',$store_nm,40,64).'
'.$merchant_nm.' '.progLink($prog,$foreign_id));
hPC('Site of the Day',inpTxt('sod_dt',$sod_dt,12,12));
hPC('GC Category ID',inpTxt('cat_id',$cat_id,12,12));
hPC('Top Ad',inpTextarea('top_ad',$top_ad,51,5));
hPC('HTML',inpTextarea('html_txt',$html_txt,51,5));
hPC('',inpBtn(EDIT_GOLDEN_BTN).' (ok to del)
'.inpTxt('add_id','',5,4).inpBtn(ADD_GOLDEN_BTN).inpBtn(DELETE_GOLDEN_BTN));
echo ' 0) execSQL('INSERT INTO Golden_Can (can_id) VALUES ('.$can_id.')','Add Row','adding row');
}
function addEPCBlock() {
$network_str = (isset($_POST['network_str']))? $_POST['network_str'] : '';
$block = (isset($_POST['block']))? $_POST['block'] : '';
echo 'Start. Block size = '.strlen($block).'
... ';
$tok = strtok($block, "\n");
echo strlen($tok);
$netLink = ($network_str != '')? "and prog='$network_str'" : '';
while ($tok !== false) {
list($fid,$epc,$merchant_nm) = explode("\t", $tok);
$merchant_id = 0;
if ($fid != '') {
$merchant_id = sqlValue("SELECT merchant_id FROM Merchant WHERE foreign_id LIKE '$fid' ".$netLink);
}
if (! merchant_id > 0) {
$test = addslashes(strtolower(substr($merchant_nm,0,28))).'%';
$merchant_id = sqlValue("SELECT merchant_id FROM Merchant_Alias WHERE alias_nm LIKE '$test' ".$netLink);
}
if ($merchant_id > 0 ) {
if ($epc == '') $epc = 0;
execSQL("REPLACE INTO Merchant_EPC (merchant_id, epc_dt, epc_amt)
VALUES ($merchant_id,now(),$epc)",'adding row','adding row');
} else {
echo '
Error: m= '.$merchant_id.' FID = '.$fid.' EPC= '.$epc.' m='.$merchant_nm;
}
$tok = strtok("\n");
}
}
function epcForm() {
$network_str = 'cj';
displayHead('Add EPC Data');
hForm('aMechant.html');
hTable('Add EPC Block');
echo ' |
| This form lets you add a tab delimited block of EPC Data. The data should be in the form Foreign ID [tab] epc [tab] merchant |
';
hPC('Network',inpTxt('network_str',$network_str,5,5));
hPC('Data',inpTextarea('block','',51,5));
hPC('',inpBtn(ADD_EPC_BLOCK_BTN));
echo '';
}
function switchHist($m, $h, $p) {
$merchant_id = (isset($_REQUEST['m']))? (int) $_REQUEST['m'] : '';
$hist_id = (isset($_REQUEST['h']))? intval($h) : 0;
$p = (isset($_REQUEST['p']))? substr($p,0,2) : '';
if ($merchant_id > 0 and $hist_id > 0) {
$sql = "UPDATE Rev_Hist
SET prog_cd = '$p'
WHERE merchant_id = $merchant_id
and hist_id < $hist_id
and (prog_cd = '' or prog_cd is null)";
$rv = execSQL($sql,'Updated Rows','updating history','c');
msgNoteSuccess("Updated $rv rows");
}
}
function coolSum() {
displayHead('Cool Site Summary');
sqlTable('Cool Summary','',"SELECT concat('',m.prog,'') prog, count(*) sites, count(distinct c.dt) reviews, sum(c.hits) hits
FROM Merchant_Cool c, Merchant m
WHERE c.merchant_id = m.merchant_id
GROUP BY m.prog");
}
function coolDetail() {
$prog = (isset($_REQUEST['p']))? mysql_real_escape_string($_REQUEST['p']) : '';
$prog_nm = '';
displayHead('cool detail');
$sql = "SELECT concat('',m.merchant_id,'') id, m.prog, m.merchant_nm, c.dt, c.site_nm, c.hits
FROM Merchant m
LEFT JOIN Merchant_Cool c ON m.merchant_id = c.merchant_id
WHERE m.active_cd = 'Y' and m.prog = '$prog'
ORDER BY m.prog, m.merchant_nm";
sqlTable('Cool Detail','',$sql);
echo ''.$sql.'
';
}
function coolErr() {
displayHead('cool errors');
sqlTable('Cool Summary','',"SELECT m.merchant_id, m.prog, m.merchant_nm, c.dt, c.site_nm, c.hits
FROM Merchant m, Merchant_Cool c
WHERE m.active_cd = 'N' and m.merchant_id = c.merchant_id
ORDER BY m.prog, m.merchant_nm");
}
function calcEPC() {
$sql = "SELECT e.merchant_id, m.epc_amt, avg(e.epc_amt)
FROM Merchant m, Merchant_EPC e
WHERE m.merchant_id = e.merchant_id
GROUP BY e.merchant_id";
if ($result = mysql_query($sql)) {
while (list($merchant_id, $mEPC, $newEPC) = mysql_fetch_row($result)) {
if ($newEPC == 0) $newEPC = .01; // set the minimum as a penny.
if ($mEPC != $newEPC and $newEPC != '') execSQL("UPDATE Merchant SET epc_amt = '$newEPC' WHERE merchant_id = $merchant_id","Updated $merchant ID from $mEPC to $newEPC","updating merchant ".$merchant_id);
}
}
}
// Replicates the community table from Community Color.
function replicateCommunity() {
$str = file_get_contents("http://irivers.com/dq.php?q=community");
if ($str != '') {
execSQL("DELETE FROM Community","Deleted old data","deleting data");
execSQL("INSERT INTO Community (community_id, community_nm, url)
VALUES ".$str,"Adding data","adding data");
msgHTML();
sqlTable("Community Table",'',"SELECT * FROM Community");
}
}
function addPayment($merchant_id, $prog, $year, $month, $amt) {
msgNotNull($merchant_id,"merchant");
msgNotNull($prog,"program");
msgNotNull($year,"year");
msgNotNull($month,"month");
msgNotNull($amt,"Amount");
$sql = "INSERT INTO Merchant_Payment VALUES (rev_year, rev_month, prog, merchant_id, amt) VALUES ($year, $month, '$prog', $merchant_id, $amt)";
execSQL($sql,"Added Month","adding month");
}
function addPaymentBlock($merchant_id, $prog, $block) {
msgNotNull($merchant_id,"merchant");
msgNotNull($prog,"program");
msgNotNull($year,"year");
msgNotNull($month,"month");
msgNotNull($amt,"Amount");
$vals = '';
$tok = strtok($block, "\n");
$cnt=0;
while ($tok) {
$cnt++;
list($year, $month, $amt) = explode($tok,',');
if ($year > 0 and $month > 0 and $amt > 0) {
msgNoteSuccess("Year: $year, Month: $month, Amt: $amt");
if ($vals != '') $vals .= ",\n";
$vals .= "($year, $month, '$prog', $merchant_id, $amt)";
} else {
msgNoteSuccess("Error on line $cnt: $tok");
}
$tok = strtok("\n");
}
execSQL("INSERT INTO Merchant_Payment VALUES (rev_year, rev_month, prog, merchant_id, amt) VALUES ".$vals,"Added Month","adding month");
}
function payHist($merchant_id,$del='N') {
$tot = 0;
$sql = "SELECT concat(rev_year,'-',rev_month) date, amt FROM Merchant_Payment WHERE merchant_id = $merchant_id ORDER BY year DESC, month DESC";
$rv = "";
if ($result=mysql_query($sql)) {
while (list($dt, $amt) =mysql_fetch_row($result)) {
$rv .= "| $dt | ".number_format($amt,2)." |
\n";
$tot += $amt;
}
}
if ($tot > 0) $rv .="| Total | ".number_format($tot,2)." |
\n";
return ($rv=="")? "N/A" : "\n";
}
function addPaymentForm($merchant_id) {
$merchant_id = intval($merchant_id);
if ($merchant_id >0) {
list($merchant_nm, $prog) = sqlRow("SELECT merchant_nm, prog FROM Merchant WHERE merchant_id = $merchant_id");
}
displayHead("Add Payment - $merchant_nm");
msgHTML();
$year = date('Y');
$month = date('n');
hForm("aMechant.html");
echo inpHidden("merchant_id",$merchant_id);
hTable("Add Merchant Payment");
hPC("Merchant Name",$merchant_nm);
hPC("Program",inpTxt("prog",$prog,4,4));
hPC("Year",numberSelect("year",$year,1999,$year));
hPC("Month",numberSelect("month",$month,0,12));
hPC("Payment Amount",inpTxt("amt",$amt,15,15));
hPC('',inpBtn(ADD_PAYMENT_BTN));
hPC("Block","Add CSV block of form YYYY,MM,AMT
",inpTextArea("block",$block,40,6));
hPC('',inpBtn(ADD_BLOCK_BTN));
hPC("History",payHist($merchant_id,'Y'));
echo "";
}
function replicateMerchantPage() {
$str = file_get_contents("http://irivers.com/dq.php?q=page");
if ($str != '') {
execSQL("DELETE FROM Merchant_Page","Deleted old data","deleting data");
execSQL("INSERT INTO Merchant_Page (merchant_id, pg, community_id, hits)
VALUES ".$str,"Adding data","adding data");
}
$str = file_get_contents("http://irivers.com/dq.php?q=cool");
if ($str != '') {
execSQL("DELETE FROM Merchant_Cool","Deleted old cool data","deleting cool data");
execSQL("INSERT INTO Merchant_Cool (merchant_id, dt, site_nm, hits)
VALUES ".$str,"Adding data","adding data");
}
msgHTML();
}
function addNote($merchant_id, $note_dt, $short_desc) {
$merchant_id = intval($merchant_id);
$short_desc= addslashes($short_desc);
$note_dt = ($note_dt == "")? "now()" : "'$note_dt'";
if (!($merchant_id > 0)) msgNoteError("Add Note - Invalid Merchant. $merchant_id");
msgNotNull($short_desc,"Note");
if (!hasErrors()) {
execSQL("INSERT INTO Merchant_Note (merchant_id, note_dt, add_ts, short_desc)
VALUES ('$merchant_id',$note_dt,now(),'$short_desc')","Added Note","adding note");
}
}
function deleteNote() {
$note_id = (isset($_REQUEST['n']))? intval($_REQUEST['n']) : 0;
if ($note_id > 0) execSQL("DELETE FROM Merchant_Note WHERE note_id = $note_id","Deleted Note","deleting note","C");
}
function addAlias() {
$alias_nm = (isset($_REQUEST['alias_nm']))? mysql_real_escape_string(stripslashes($_REQUEST['alias_nm'])) : '';
$merchant_id = (isset($_REQUEST['merchant_id']))? intval($_REQUEST['merchant_id']) : 0;
if ($merchant_id > 0) execSQL("REPLACE INTO Merchant_Alias (alias_nm, merchant_id) VALUES ('$alias_nm',$merchant_id)","Added Alias","adding alias");
}
function deleteAlias() {
$alias_nm = (isset($_REQUEST['alias_nm']))? mysql_real_escape_string(stripslashes($_REQUEST['alias_nm'])) : '';
//$alias_nm = addslashes(strtolower(substr($alias_nm,0,30)));
execSQL("DELETE FROM Merchant_Alias WHERE alias_nm = '$alias_nm'",'Deleted Alias "'.$alias_nm.'"','deleting alias "'.$alias_nm.'"','C');
}
function listMerchants($letter, $type) {
$totArr = array(0,0,0,0);
$joinStr = ''; // Join Merchant_Category if needed
$letter=addslashes($letter);
if ($type=='prog') {
$cond = "prog = '$letter'";
$groupCol='prog GroupCol';
$groupName='prog GroupName';
$gBtn = 'prog';
if ($letter == '') {
$title = "Program Groups";
} else {
$title = "Programs marked '$letter'";
}
} elseif($type=='state_cd') {
$cond = "state_cd = '$letter'";
$groupCol='state_cd GroupCol';
$groupName='state_cd GroupName';
$gBtn = 'state_cd';
if ($letter == '') {
$title = "By State";
} else {
$title = "Programs from state '$letter'";
}
} elseif($type=='cat') {
$catId = intval($letter);
$joinStr = ' LEFT JOIN Merchant_Category c ON m.category_id = c.category_id ';
$cond = 'c.category_id = '.$catId;
$groupCol='m.category_id GroupCol';
$groupName='c.category_nm GroupName';
$gBtn = 'cat';
if ($letter == '') {
$title = "By Category";
} else {
list($mCat, $la_cat, $fob_pg) = sqlRow('SELECT category_nm, la_cat, fob_pg FROM Merchant_Category WHERE category_id = '.$catId);
$title = $catId.' - '.$mCat.' Stores';
}
} else {
$cond = "left(merchant_nm,1) = '$letter'";
$groupCol="left(merchant_nm,1) GroupCol";
$groupName="left(merchant_nm,1) GroupName";
$gBtn = 'alpha';
$title = "Merchants beginning with '$letter'";
}
displayHead($title);
msgHTML();
if ($letter=='') {
$sql='SELECT '.$groupCol.', '.$groupName.', m.active_cd, count(*) Count, sum(m.tot_hits) Hits, sum(m.tot_commissions) Commissions
FROM Merchant m '.$joinStr.'
GROUP BY active_cd, GroupCol ORDER BY GroupName, active_cd';
// echo ''.$sql.'
';
hTable($title,9);
if ($result=mysql_query($sql)) {
echo '| Group | -- | Active | Total |
|---|
';
echo '| Name | | Count | Hits | Avg hits | Revenue | Count | Hits | Revenue |
|---|
';
$holdGroup = '~!';
$holdStr = '';
while (list($gCol, $gNm, $gStatus, $gCnt, $gHits, $gRev) = mysql_fetch_row($result)) {
if ($gCol != $holdGroup) {
if ($holdGroup != '~!') {
$avgHits = ($actHits > 0)? round($actHits/$actCnt) : 'N/A';
$weight = decHex(floor( (16* $actCnt)/($termCnt + $actCnt)));
hRowColor();
hTd($holdStr);
echo ' | ';
hTd($actCnt,'R');
hTd($actHits,'R');
hTd($avgHits,'R');
hTd($actRev,'R');
hTd($actCnt + $termCnt,'R');
hTd($actHits + $termHits,'R');
hTd($actRev + $termRev,'R');
echo "\n";
$totArr[0] += $actCnt;
$totArr[1] += $actHits;
$totArr[2] += $actCnt + $termCnt;
$totArr[3] += $actHits + $termHits;
}
if ($gNm == '') $gNm = '[NULL]';
$holdGroup = $gCol;
$holdStr = "$gNm ";
$actCnt = 0;
$actHits=0;
$actRev=0;
$termCnt= 0;
$termHits=0;
$termRev=0;
}
if ($gStatus == 'Y') {
$actCnt += $gCnt;
$actHits += $gHits;
$actRev += $gRev;
} elseif ($gStatus= 'N') {
$termCnt += $gCnt;
$termHits += $gHits;
$termRev += $gRev;
}
}
hRowColor();
hTd($holdStr);
$weight = decHex(floor( (16* $actCnt)/($termCnt + $actCnt)));
echo ' | ';
hTd($actCnt,'R');
hTd($actHits,'R');
hTd($avgHits,'R');
hTd($actRev,'R');
hTd($actCnt + $termCnt,'R');
hTd($actHits + $termHits,'R');
hTd($actRev + $termRev,'R');
echo "\n";
echo ' | '.$totArr[0].' | '.$totArr[1].' | | '.$totArr[2].' | '.$totArr[3].' | |
';
echo "\n";
}
} else {
sqlTable($title,"","SELECT m.merchant_id ID, m.foreign_id 'FK', m.join_dt, m.term_dt, m.city, m.state_cd, m.active_cd, concat('',m.merchant_nm,'') Merchant, m.prog, c.category_nm, m.tot_hits 'Hits', m.tot_commissions 'Rev'
FROM Merchant m
LEFT JOIN Merchant_Category c ON m.category_id = c.category_id
WHERE $cond
ORDER BY active_cd, merchant_nm");
}
//echo "$sql
";
}
function addMerchant($merchant_nm, $prog, $weight, $display_yn, $foreign_id, $ticker, $city, $state_cd, $url, $ad_url, $join_dt) {
if ($display_yn != 'Y') $display_yn = 'N';
$merchant_nm = mysql_real_escape_string(stripslashes($merchant_nm));
$prog = mysql_real_escape_string(stripslashes($prog));
$foreign_id = mysql_real_escape_string(stripslashes($foreign_id));
$ticker = mysql_real_escape_string(stripslashes($ticker));
$city = mysql_real_escape_string(stripslashes($city));
$join_dt = mysql_real_escape_string(stripslashes($join_dt));
$state_cd= addslashes(substr($state_cd,0,2));
$url = addslashes($url);
$ad_url = addslashes($ad_url);
msgNotNull($merchant_nm,"Merchant Name");
msgNotNull($prog,"Program");
$test = sqlValue("SELECT merchant_id FROM Merchant WHERE merchant_nm LIKE '$merchant_nm'");
$testa = sqlValue("SELECT merchant_id FROM Merchant_Alias WHERE alias_nm like LOWER('$merchant_nm')");
if ($test > 0) msgNoteError("Merchant '$merchant_nm' already exists!");
if ($testa > 0) msgNoteError("Merchant Alias '$merchant_nm' already exists!");
if (!hasErrors()) {
$rv=execSQL("INSERT INTO Merchant (merchant_nm, prog, weight, foreign_id, ticker, city, state_cd, url, ad_url, join_dt)
VALUES ('$merchant_nm', '$prog', '$weight', '$foreign_id', '$ticker', '$city', '$state_cd', '$url', '$ad_url', '$join_dt')","added merchant","adding merchant",'Y');
execSQL("INSERT INTO Merchant_Alias (alias_nm, merchant_id) VALUES (LOWER('$merchant_nm'),$rv)","Added alias","adding alias");
}
return $rv;
}
function editMerchant($merchant_id,$merchant_nm, $prog, $weight, $display_yn, $active_cd, $terminated_yn, $join_dt, $term_dt, $foreign_id, $ticker, $city, $state_cd, $url, $ad_url, $category_id) {
if ($display_yn != 'Y') $display_yn = 'N';
if ($active_cd == 'Y') {
$terminated_yn='N';
} else {
$active_cd = 'N';
if ($terminated_yn!='Y') $terminated='N';
}
$merchant_nm = mysql_real_escape_string(stripslashes($merchant_nm));
$merchant_id = intval($merchant_id);
$prog = addslashes($prog);
$join_dt = addslashes($join_dt);
$term_dt = addslashes($term_dt);
$foreign_id = addslashes($foreign_id);
$ticker = addslashes($ticker);
$city = mysql_real_escape_string(stripslashes($city));
$state_cd= addslashes(substr($state_cd,0,2));
$url = addslashes($url);
$category_id = intval($category_id);
$ad_url = mysql_real_escape_string(stripslashes($ad_url));
msgNotNull($merchant_nm,"Merchant Name");
msgNotNull($prog,"Program");
if (!$merchant_id > 0) msgNoteError("Invalid Merchant ID");
if (!hasErrors()) {
$rv=execSQL("UPDATE Merchant SET
merchant_nm='$merchant_nm',
prog='$prog',
active_cd = '$active_cd',
terminated_yn = '$terminated_yn',
join_dt = '$join_dt',
term_dt = '$term_dt',
weight=$weight,
foreign_id='$foreign_id',
ticker='$ticker',
city='$city',
state_cd='$state_cd',
url='$url',
ad_url='$ad_url',
category_id = $category_id
WHERE merchant_id=$merchant_id","updated merchant","updating merchant");
}
return $rv;
}
function addMerchantForm($merchant_nm, $prog, $weight, $display_yn, $foreign_id, $ticker, $city, $state_cd, $url, $ad_url, $join_dt) {
if ($join_dt == '') $join_dt = date('Y-m-d');
displayHead("Add Merchant");
msgHTML();
hForm("aMechant.html");
hTable("Add Merchant");
hPC("Merchant Name",inpTxt('merchant_nm',$merchant_nm,25,30));
hPC("Program",inpTxt('prog',$prog,2,2));
hPC("Display",inpCB('display_yn',$display_yn,'Y'));
hPC("Weight +1 9- ",numberSelect("weight",$weight,1,9));
hPC("Foreign ID",inpTxt("foreign_id",$foreign_id,25,32));
hPC("ticker",inpTxt("ticker",$ticker,8,8));
hPC("Join Date YYYY-MM-DD",inpTxt("join_dt",$join_dt,14,14));
hPC("city",inpTxt("city",$city,25,28));
hPC("state_cd",inpTxt("state_cd",$state_cd,2,2));
hPC("Company URL",inpTxt("url",$url,25,127));
hPC("Ad URL",inpTxt("ad_url",$ad_url,25,127));
hPC("",inpBtn(ADD_MERCHANT_BTN));
echo "\n";
}
function getAlias($m) {
$rv="";
if ($result=mysql_query("SELECT alias_nm FROM Merchant_Alias WHERE merchant_id=$m ORDER BY alias_nm")) {
while (list($nm) = mysql_fetch_row($result)) {
$rv .= $nm." del
";
}
}
return $rv;
}
function getMNotes($m) {
$rv="";
if ($result=mysql_query("SELECT note_id, note_dt, short_desc FROM Merchant_Note WHERE merchant_id=$m ORDER BY note_dt")) {
while (list($note_id,$note_dt,$short_desc) = mysql_fetch_row($result)) {
$rv .= $note_dt.": ".$short_desc." del
";
}
}
return ($rv == "")? "" : "".$rv."
";
}
function getPages($merchant_id) {
$rv='';
$sql = "SELECT p.community_id, p.pg, p.hits, c.community_nm, c.url
FROM Merchant_Page p, Community c
WHERE p.merchant_id = $merchant_id
and p.community_id = c.community_id";
$tHits = 0;
if ($result = mysql_query($sql)) {
while (list($community_id, $pg, $hits, $community_nm, $url) = mysql_fetch_row($result)) {
if ($rv != '') $rv .= "
";
$rv .= "($hits hits) $community_nm - $pg ";
$tHits += $hits;
}
}
$rv .= ($tHits > 0)? "
Total: $tHits" : '';
return $rv;
}
function editMerchantForm($merchant_id) {
list($merchant_id,$merchant_nm, $prog, $weight, $display_yn, $active_cd, $terminated_yn, $join_dt, $term_dt, $foreign_id, $ticker, $city, $state_cd,
$url, $ad_url, $category_id) = sqlRow('SELECT merchant_id,merchant_nm, prog, weight, display_yn, active_cd, terminated_yn, join_dt, term_dt, foreign_id, ticker,
city, state_cd, url, ad_url, category_id
FROM Merchant WHERE merchant_id = '.$merchant_id);
displayHead("Edit Merchant - ".$merchant_nm);
msgHTML();
hForm("aMechant.html");
echo inpHidden("merchant_id",$merchant_id);
hTable("Edit Merchant");
hPC("Merchant Name",inpTxt('merchant_nm',$merchant_nm,25,30).' la');
hPC("Program",inpTxt('prog',$prog,2,2).progLink($prog,$foreign_id));
hPC("Display",inpCB('display_yn',$display_yn,'Y'));
hPC("Active",inpCB('active_cd',$active_cd,'Y'));
if ($active_cd == 'N') hPC('Terminated?',inpCB('terminated_yn',$terminated_yn,'Y'));
hPC("Weight +1 9- ",numberSelect("weight",$weight,1,9));
hPC('Category='.$category_id,sqlSelect('category_id',$category_id,'SELECT category_id, category_nm FROM Merchant_Category ORDER BY category_nm'));
hPC("Foreign ID",inpTxt("foreign_id",$foreign_id,25,32));
hPC("ticker",inpTxt("ticker",$ticker,8,8));
hPC("city",inpTxt("city",$city,25,28));
hPC("state_cd",inpTxt("state_cd",$state_cd,2,2));
hPC("Join Date yyyymmdd",inpTxt("join_dt",$join_dt,14,14));
hPC("Term Date yyyymmdd",inpTxt("term_dt",$term_dt,14,14));
hPC("Company URL",inpTxt("url",$url,25,127));
hPC("Ad URL",inpTxt("ad_url",$ad_url,25,127));
hPC("",inpBtn(EDIT_MERCHANT_BTN));
hPC("Payments",payHist($merchant_id));
hPC("Pages",getPages($merchant_id));
hPC("Aliases",getAlias($merchant_id).inpTxt("alias_nm",'',10,30).inpBtn(ADD_ALIAS_BTN));
hPC("Notes",getMNotes($merchant_id).inpTextarea("note_txt",'',51,5)."
dt: "
.inpTxt("note_dt",$note_dt,12,12).inpBtn(ADD_NOTE_BTN));
echo "\n";
$sql="SELECT concat('',h.hist_id,'') HID, i.year, i.month, h.prog_cd 'P', i.import_id,
h.merchant_nm 'Name', h.impressions '+Imprs', h.hits '+Hits', h.actions '+Actions', h.sales_amt '+Sales',
h.revenue_amt '+Earnings'
FROM Rev_Hist h, Rev_Import i
WHERE h.merchant_id = $merchant_id
and h.import_id = i.import_id
ORDER BY year DESC, month DESC";
sqlTable("Rev Hist",'',$sql);
sqlTable('FOB Listings','',"SELECT concat('',fob_id,'') ID, c.category_nm, f.start_dt, f.end_dt, f.weight FROM FOB_Listing f, FOB_Category c WHERE f.merchant_id = $merchant_id AND f.category_id = c.category_id");
sqlTable('Rotating Ads','',"SELECT concat('',s.width_no,'x',s.height_no,'') Ad, g.hits, g.active_yn 'A'
FROM Google_Ad g, Ad_Size s
WHERE g.size_id = s.size_id
and g.merchant_id =".$merchant_id);
}
function merchantError() {
displayHead("Merchant Errors");
echo "
This reports shows traffice to Share-a-Sale Merchants generating errors. If a merchant falls behind in their payments, SaS sends the click back to the referrer. I use this report in an attempt to minimize the damage done by the process
\n";
sqlTable("Merchant Error Report",'',"SELECT e.merchant_id, m.merchant_nm, count(*), date_format(min(ts),'%Y-%m-%e') First, date_format(max(ts),'%Y-%m-%e') Last
FROM Merchant m,
Merchant_Error e
WHERE e.merchant_id = m.merchant_id
GROUP BY e.merchant_id");
}
function loadMerchant($merchant_id) {
if ($merchant_id > 0) {
editMerchantForm($merchant_id);
} else {
echo "invalid merchant id $merchant_id";
}
}
function joinMonth($month) {
$tArr = array();
displayHead("Join Month - ".$month);
if ($month=='') {
$sql="SELECT left(join_dt,7) 'Month', count(*), left(term_dt,7) Term FROM Merchant GROUP BY left(join_dt, 7) ORDER BY 'Month'";
hTable("Join Month Summary",5);
$totCnt = 0;
$totTermed = 0;
$active = 0;
echo "| Month | New | Termed | Active | Total | ";
if ($r=mysql_query($sql)) {
while (list($month, $cnt, $term) = mysql_fetch_row($r)) {
$totCnt ++;
if (isset($tArr[$term])) $tArr[$term] += 1; else $tArr[$term] = 1;
$termed = (isset($tArr[$month]))? $tArr[$month] : 0;
$active += ($cnt - $termed);
hRowColor();
hTd("$month");
hTd($cnt,'R');
hTd($termed,'R');
hTd($active,'R');
hTd($totCnt,'R');
}
}
echo "";
print_r($tArr);
} else {
$query = $month.'%';
sqlTable("Join Month",'',"SELECT concat('',merchant_nm,'') 'Merchant', prog, state_cd 'State', active_cd 'Active', join_dt 'Joined', term_dt 'Term`d' FROM Merchant WHERE join_dt like '$query' ORDER BY join_dt");
}
}
function stateRep($state) {
displayHead("Location Report - ".$state_cd);
if ($state=='') {
$sql="SELECT state_cd 'State', count(*) FROM Merchant GROUP BY state_cd ORDER BY state_cd";
hTable("Location Summary",2);
echo "
|---|
| State | Count | ";
if ($r=mysql_query($sql)) {
while (list($stCd, $cnt) = mysql_fetch_row($r)) {
hRowColor();
hTd("".$stCd."");
hTd($cnt,'R');
}
}
echo "";
} else {
$query = $state.'%';
sqlTable("Location Detail",'',"SELECT concat('',merchant_nm,'') 'Merchant', prog, concat(city,', ',state_cd) 'City', active_cd 'Active', join_dt 'Joined', term_dt 'Term`d' FROM Merchant WHERE state_cd like '$query' ORDER BY merchant_nm");
}
}
if (isset($_REQUEST['m'])) {
$merchant_id = (int) $_REQUEST['m'];
} else {
$merchant_id = (isset($_REQUEST['merchant_id']))? (int) $_REQUEST['merchant_id'] : 0;
}
$letter = (isset($_GET['letter']))? mysql_real_escape_string($_GET['letter']) : '';
if ($btn=='' or $btn=='alpha') listMerchants($letter, 'alpha');
elseif ($btn == 'prog') listMerchants($letter, 'prog');
elseif ($btn == 'cat') listMerchants($letter, 'cat');
elseif ($btn == 'state_cd') listMerchants($letter, 'state_cd');
elseif ($btn == 'ed') loadMerchant($merchant_id);
elseif ($btn == 'jmonth') joinMonth($month);
elseif ($btn == 'state') stateRep($state);
elseif ($btn == 'add') addMerchantForm($_POST['merchant_nm'], $_POST['prog'], $_POST['weight'], $_POST['display_yn'], $_POST['foreign_id'], $_POST['ticker'], $_POST['city'], $_POST['state_cd'], $_POST['url'], $_POST['ad_url'], $_POST['join_dt']);
elseif ($btn == ADD_MERCHANT_BTN) {
$rv = addMerchant($_POST['merchant_nm'], $_POST['prog'], $_POST['weight'], $_POST['display_yn'], $_POST['foreign_id'], $_POST['ticker'], $_POST['city'], $_POST['state_cd'], $_POST['url'], $_POST['ad_url'], $_POST['join_dt']);
if ($rv > 0) {
loadMerchant($rv);
} else {
addMerchantForm($_POST['merchant_nm'], $_POST['prog'], $_POST['weight'], $_POST['display_yn'], $_POST['foreign_id'], $_POST['ticker'], $_POST['city'], $_POST['state_cd'], $_POST['url'], $_POST['ad_url'], $_POST['join_dt']);
}
} elseif ($btn==EDIT_MERCHANT_BTN) {
editMerchant($merchant_id,$_POST['merchant_nm'], $_POST['prog'], $_POST['weight'], $_POST['display_yn'], $_POST['active_cd'], $_POST['terminated_yn'], $_POST['join_dt'], $_POST['term_dt'], $_POST['foreign_id'], $_POST['ticker'], $_POST['city'], $_POST['state_cd'], $_POST['url'], $_POST['ad_url'], $_POST['category_id']);
loadMerchant($merchant_id);
} elseif ($btn==ADD_ALIAS_BTN) {
addAlias();
loadMerchant($merchant_id);
} elseif ($btn==ADD_NOTE_BTN) {
addNote($_POST['merchant_id'], $_POST['note_dt'], $_POST['note_txt']);
loadMerchant($merchant_id);
} elseif ($btn=='dela') {
deleteAlias();
loadMerchant($merchant_id);
} elseif ($btn=='swh') {
switchHist();
loadMerchant($merchant_id);
} elseif ($btn=='delnote') {
deleteNote();
loadMerchant($merchant_id);
} elseif ($btn=='repcomm') {
replicateCommunity();
} elseif ($btn=='reppage') {
replicateMerchantPage();
} elseif ($btn=='err') {
merchantError();
} elseif ($btn=='pay') {
addPaymentForm($merchant_id);
} elseif ($btn=='calcepc') {
calcEPC();
listMerchants($letter, 'prog');
} elseif ($btn=='cs') {
coolSum();
} elseif ($btn=='ce') {
coolErr();
} elseif ($btn=='cd') {
coolDetail();
} elseif ($btn=='addepc') {
epcForm();
} elseif ($btn=='gcan') {
listGoldenCan();
} elseif ($btn=='edCan') {
editGoldenCanForm();
} elseif ($btn==ADD_EPC_BLOCK_BTN) {
addEPCBlock();
} elseif ($btn==ADD_PAYMENT_BTN) {
addPayment($merchant_id, $_POST['prog'], $_POST['year'], $_PROG['month'], $_PROG['amt']);
addPaymentForm($m);
} elseif ($btn==EDIT_GOLDEN_BTN) {
updateGoldenCan();
editGoldenCanForm();
} elseif ($btn==ADD_GOLDEN_BTN) {
addGoldenCan();
listGoldenCan();
} elseif ($btn==DELETE_GOLDEN_BTN) {
deleteGoldenCan();
listGoldenCan();
} elseif ($btn='lq') {
lastQuarter();
} else {
echo 'unsupported option '.$btn;
}
echo "alpha
~ prog
~ state
~ category
~ add
~ joined
~ rep page
~ state
~ calc epc
~ cool
~ cool-err
~ aepc
~ Golden
~ TotRpt
~ Errors
~ listings
~ piper
";
displayFoot();
?>
|---|