function prepare_report($shemaid) { $fname = tempnam("/tmp", "twrep"); //$report_fname=$client_dir."/".$fname.".csv"; $fw = fopen($fname, "w"); fputs($fw, "ts,location,userid,rtc,placecntry,placename\n"); $row = pg_fetch_row(pg_query("select t_id from scheme where id={$shemaid}")); $tag_id = (int) $row[0]; $res = pg_query("select ts, location, userid, rtc, placecntry, placename from twits where t_id={$tag_id}"); while ($row = @pg_fetch_assoc($res)) { // export row $line = array(); $line[] = convert_to_cps_date($row["ts"]); $line[] = $row["location"]; $line[] = $row["userid"]; $line[] = $row["rtc"]; $line[] = $row["placecntry"]; $line[] = $row["placename"]; fputcsv($fw, $line); } fclose($fw); $zip = new ZipArchive(); $filename = "/tmp/twr" . $shemaid . ".zip"; if ($zip->open($filename, ZIPARCHIVE::CREATE) !== TRUE) { die("cant open <{$filename}>\n"); } $zip->addFile($fname, "data.csv"); $zip->close(); @unlink($fname); return $filename; }
function prepare_report() { $cntryarr = load_countries_from_csv("./country.csv"); $category = load_catnames_from_db(); global $curr; $fname = tempnam("/tmp", "krep"); //$report_fname=$client_dir."/".$fname.".csv"; $fw = fopen($fname, "w"); fputs($fw, "id,launch_time,till_days,pname, parent_category, goal, pledged, state, state_days, creator_name, bakerscount, category_name, categoryposition, location_name, location_state, location_country,days_till_deadline,pledged_pct,year,month,goal_dim, till_days_dim, pct_dim, state_days_dim, pledged_rng, goal_rng\n"); $res = pg_query("select p.id as id,deadline, currency, pname, goal, pledged, \"state\", to_timestamp(launchedat) as launch_time, ((deadline-launchedat)/86400) as till_days, ((statechangedat-launchedat)/86400) as state_days, cr.cname as creator_name, bakerscount, cat.cname as category_name , cat.parentid as parent_category, categoryposition, l.lname as loc_name, l.cstate as loc_state, l.ccountry as loc_country from project as p, creator as cr, category as cat, location as l where l.id=p.l_id and cr.id=p.creator_id and cat.id=p.cat_id;"); while ($row2 = @pg_fetch_assoc($res)) { if (0 == $row2['launch_time']) { continue; } // skip fake projects if (100 < $row2['till_days']) { continue; } // export row $line = array(); $line[] = $row2['id']; $line[] = convert_to_cps_date($row2['launch_time']); $line[] = $row2['till_days']; $line[] = preg_replace('/[^A-Za-z0-9\\ \\-]/', '', $row2['pname']); $pcat = (int) $row2['parent_category']; if (0 == $pcat) { $line[] = $row2['category_name']; } else { $line[] = $category[$pcat]; } $currency = $row2['currency']; //process currency $goal = $line[] = convert_to_usd($curr, $currency, $row2['goal']); $pledged = $line[] = convert_to_usd($curr, $currency, $row2['pledged']); // rest of $line[] = $row2['state']; $line[] = $row2['state_days']; $line[] = preg_replace('/[^A-Za-z0-9\\ \\-]/', '', $row2['creator_name']); $line[] = $row2['bakerscount']; $line[] = $row2['category_name']; $line[] = $row2['categoryposition']; $line[] = $row2['loc_name']; $line[] = $row2['loc_state']; if (!isset($cntryarr[$row2['loc_country']])) { $line[] = "unknown country"; } else { $line[] = $cntryarr[$row2['loc_country']]; } $days_left = floor(($row2['deadline'] - time()) / 86400); if (0 > $days_left) { $days_left = 0; } $line[] = $days_left; if (0 == $goal) { $goal = 1.0E-5; } $line[] = floor($pledged / $goal * 100); //$line[]=$row2['loc_country']; $launch_date = strtotime($row2['launch_time']); $line[] = date("Y", $launch_date); $line[] = date("M", $launch_date); $line[] = $goal; $line[] = $row2['till_days']; $line[] = floor($pledged / $goal * 100); $line[] = $row2['state_days']; $line[] = get_range($pledged); $line[] = get_range($goal); fputcsv($fw, $line); } fclose($fw); $zip = new ZipArchive(); $filename = $fname . ".zip"; if ($zip->open($filename, ZIPARCHIVE::CREATE) !== TRUE) { die("cant open <{$filename}>\n"); } $zip->addFile($fname, "data.csv"); $zip->close(); @unlink($fname); return $filename; }
function generate_application_report_to_cvs($client_dir, $dbconn, $source_id, $cases, &$merchant, $ware, $cntryarr) { global $dbg; $fname = generatehash(); $report_fname = $client_dir . "/" . $fname . ".csv"; $fw = fopen($report_fname, "w"); fputs($fw, "txn_lag,fee,amount,start_ts,stop_ts,status,tcode,currency,case_reason,case_status,case_money_move,due_date,case_type,case_lag,case_date,case_amount,days_before_case_closure,project,country\n"); $qry = "select * from txn where mid<>'' and amount>0 and refid='' and ikey={$source_id} and stop_ts>(date_trunc('month',CURRENT_TIMESTAMP)- interval '1 month') order by stop_ts;"; //$qry="select * from txn where mid<>'' and amount>0 and (refid='' or refid is null) and ikey=1591 and (stop_ts>(date_trunc('month',CURRENT_TIMESTAMP)- interval '1 month') or (stop_ts between (date_trunc('month',CURRENT_TIMESTAMP)- interval '13 month') and (CURRENT_TIMESTAMP - interval '12 month'))) order by stop_ts;"; //$qry="select * from txn where mid<>'' and amount>0 and (refid='' or refid is null) and ikey=1591 order by stop_ts;"; //$dbg=$dbg."\n executing txn qry $qry... "; //$stime=get_timer(); $pgres = pg_query($qry); if (false === $pgres) { $qr = pg_errormessage($dbconn); log_fatal("generate_application_report_to_cvs: src {$source_id} ERR {$qr} in query: {$qry} "); } //$tcount=pg_num_rows($pgres); //$dbg=$dbg."($tcount) time ".((get_timer()-$stime)/1)." sec "; // $stime=get_timer(); $stime = get_timer(); while ($row = pg_fetch_assoc($pgres)) { // process txn row $line = array(); //if (isset($row["txnlag"])) $line["lag"]=$row["txnlag"]; else $line["lag"]="null"; if (isset($row["txnlag"])) { $line["lag"] = $row["txnlag"]; } else { $line["lag"] = "0"; } $line["fee"] = -1 * ((int) $row["fee"] / 100); $line["amount"] = (int) $row["amount"] / 100; $line["stopts"] = convert_to_cps_date($row["stop_ts"]); if (isset($row["start_ts"])) { $line["startts"] = convert_to_cps_date($row["start_ts"]); } else { $line["startts"] = $line["stopts"]; } $line["status"] = (string) $row["status"]; if (isset($row["tcode"])) { $line["tcode"] = (string) $row["tcode"]; } else { $line["tcode"] = " "; } $line["currency"] = (string) $row["currency"]; $ppid = $row["ppid"]; $mid = $row["mid"]; if (isset($cases[$mid])) { $row2 = $cases[$mid]; $line["creason"] = $row2["creason"]; $line["cstatus"] = $row2["cstatus"]; $line["cmm"] = $row2["cmm"]; $line["cdd"] = convert_to_cps_date($row2["cdd"]); $line["ctype"] = $row2["ctype"]; $line["clag"] = $row2["clag"]; $line["cdate"] = convert_to_cps_date($row2["cdate"]); $line["camount"] = $row2["camount"]; $line["cdays"] = $row2["cdays"]; //$dbg=$dbg."*"; } else { // null values $line["creason"] = "null"; $line["cstatus"] = "null"; $line["cmm"] = " "; $line["cdd"] = convert_to_cps_date("0"); $line["ctype"] = " "; $line["clag"] = "0"; $line["cdate"] = convert_to_cps_date("0"); $line["camount"] = "0"; $line["cdays"] = "0"; } // no case - empty values if (isset($merchant[$mid])) { $row2 = $merchant[$mid]; $line["prj"] = process_prj_names($ware[$row2[1]]); if (!isset($cntryarr[$row2[2]])) { //$dbg=$dbg."country not found! '".$row2[2]."' \n"; $line["country"] = "unknown country"; } else { $line["country"] = $cntryarr[$row2[2]]; } } else { $line["prj"] = "null"; $line["country"] = "null"; } fputcsv($fw, $line); } // process txn's fclose($fw); return $report_fname; }