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".$rv."
MonthAmount
\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--ActiveTotal'; echo 'NameCountHitsAvg hitsRevenue CountHitsRevenue'; $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 "MonthNewTermedActiveTotal"; 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 "StateCount"; 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(); ?>