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;
}
