function proceed_old($data)
{
$lead = '';
$sale = '';
if (isset($data['s'])) {
$data['a'] = convert_to_usd($data['c'], $data['a']);
$r = mysql_query('SELECT `id` FROM `tbl_clicks` WHERE `subid` = "' . mysql_real_escape_string($data['s']) . '"');
if (mysql_num_rows($r) > 0) {
$f = mysql_fetch_assoc($r);
switch ($data['t']) {
case 'lead':
$lead = ',`is_lead` = 1';
break;
case 'sale':
$sale = ', `is_sale` = 1';
break;
}
if (($sale != '' || $lead != '') && isset($d['a'])) {
mysql_query('UPDATE `tbl_clicks` SET `conversion_price_main` = "' . mysql_real_escape_string($data['a']) . '" ' . $lead . $sale . ' WHERE `id` = ' . $f['id']);
}
}
$r = mysql_query('SELECT `id` FROM `tbl_conversions` WHERE `subid` = "' . mysql_real_escape_string($data['s']) . '"');
if (mysql_num_rows($r) > 0) {
$f = mysql_fetch_assoc($r);
if (isset($data['a'])) {
mysql_query('UPDATE `tbl_conversions` SET `profit` = "' . mysql_real_escape_string($data['a']) . '" WHERE `id` = ' . $f['id']);
}
} else {
mysql_query('INSERT INTO `tbl_conversions` (`network`, `profit`, `subid`, `status`, `t20`, `date_add`) ' . 'VALUES ("custom", "' . mysql_real_escape_string($data['a']) . '", "' . mysql_real_escape_string($data['s']) . '", 1, "' . mysql_real_escape_string($data['c']) . '", NOW())') or die(mysql_error());
}
}
}
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;
}
$id = mysql_insert_id();
$sql = "update tbl_links_categories_list set category_name='category_" . mysql_real_escape_string($id) . "' where id='" . mysql_real_escape_string($id) . "'";
mysql_query($sql);
echo $id;
exit;
break;
case 'add_costs':
$timezone_shift = get_current_timezone_shift();
$date_range = explode(' - ', trim($_REQUEST['date_range']));
$date_start = $date_range[0];
$date_end = $date_range[1];
$source_name = $_REQUEST['source_name'];
$campaign_name = $_REQUEST['campaign_name'];
$ads_name = $_REQUEST['ads_name'];
$costs_value = trim(str_replace(',', '.', $_REQUEST['costs_value']));
$costs_value = convert_to_usd($_REQUEST['currency_code'], $costs_value);
if ($date_start == '' || $date_end == '' || $source_name == '' || $costs_value == '') {
exit;
}
$date_start = date2mysql($date_start);
$date_end = date2mysql($date_end);
$where = '';
if ($campaign_name != '') {
$where .= " and campaign_name='" . mysql_real_escape_string($campaign_name) . "'";
}
if ($ads_name != '') {
$where .= " and ads_name='" . mysql_real_escape_string($ads_name) . "'";
}
$sql = "select count(id) as cnt from tbl_clicks where CONVERT_TZ(date_add, '+00:00', '" . _str($timezone_shift) . "') BETWEEN '" . mysql_real_escape_string($date_start) . " 00:00:00' AND '" . mysql_real_escape_string($date_end) . " 23:59:59' and source_name='" . mysql_real_escape_string($source_name) . "' {$where}";
$rs = mysql_query($sql);
$row = mysql_fetch_assoc($rs);
function process_paypal_txns($txn, $dbconn, $merchant_cube_id, $fid)
{
global $dbg;
global $curr;
$i = 0;
$txn_count = 0;
@pg_query("begin;");
foreach ($txn as $this_item) {
// insert into database
$i++;
// "CH","Transaction ID","Invoice ID","PayPal Reference ID","PayPal Reference ID Type","Transaction Event Code","Transaction Initiation Date","Transaction Completion Date",
// "Transaction Debit or Credit","Gross Transaction Amount","Gross Transaction Currency","Fee Debit or Credit","Fee Amount","Fee Currency","Transactional Status",
// "Insurance Amount","Sales Tax Amount","Shipping Amount","Transaction Subject","Transaction Note","Payer's Account ID","Payer Address Status","Item Name","Item ID",
// "Option 1 Name","Option 1 Value","Option 2 Name","Option 2 Value","Auction Site","Auction Buyer ID","Auction Closing Date","Shipping Address Line1",
// "Shipping Address Line2","Shipping Address City","Shipping Address State","Shipping Address Zip","Shipping Address Country","Shipping Method","Custom Field",
// "Billing Address Line1","Billing Address Line2","Billing Address City","Billing Address State","Billing Address Zip","Billing Address Country","ConsumerID","First Name","Last Name",
// "Consumer Business Name","Card Type","Payment Source","Shipping Name","Authorization Review Status","Protection Eligibility","Payment Tracking ID","Store ID","Terminal ID",
// "Coupons","Special Offers","Loyalty Card Number","Checkout Type","Secondary Shipping Address Line1","Secondary Shipping Address Line2","Secondary Shipping Address City",
// "Secondary Shipping Address State","Secondary Shipping Address Country","Secondary Shipping Address Zip","3PL Reference ID"
$txn_id = $this_item["Transaction ID"];
$txn_merchantid = $this_item["Invoice ID"];
$txn_refid = $this_item["PayPal Reference ID"];
$txn_reftype = $this_item["PayPal Reference ID Type"];
$txn_tcode = $this_item["Transaction Event Code"];
$txn_startts = date("Y-m-d H:00:00O", strtotime($this_item["Transaction Initiation Date"]));
$txn_stopts = date("Y-m-d H:i:sO", strtotime($this_item["Transaction Completion Date"]));
$txn_lag = floor(strtotime(strtotime($this_item["Transaction Completion Date"]) - $this_item["Transaction Initiation Date"]) / (60 * 60));
$txn_cur = $this_item["Gross Transaction Currency"];
$txn_direction = $this_item["Transaction Debit or Credit"];
$txn_amount = $this_item["Gross Transaction Amount"];
$txn_fee_direction = $this_item["Fee Debit or Credit"];
$txn_fee = $this_item["Fee Amount"];
$txn_status = $this_item["Transactional Status"];
// skip special cases
if (FALSE !== strpos($txn_tcode, "T0400")) {
continue;
}
// paypal withdrawal
if (FALSE !== strpos($txn_tcode, "T0300")) {
continue;
}
// paypal funding
if (FALSE !== strpos($txn_tcode, "T2103")) {
continue;
}
// paypal reserve hold
if (FALSE !== strpos($txn_tcode, "T2104")) {
continue;
}
// paypal reserve release
if (FALSE !== strpos($txn_tcode, "T0000")) {
continue;
}
// we purchased something
switch ($txn_status) {
case "P":
$txn_status = "Pending";
break;
case "S":
$txn_status = "Success";
break;
case "D":
$txn_status = "Denied";
break;
case "V":
$txn_status = "Reversed";
break;
case "F":
$txn_status = "Partial refund";
break;
}
$feedir = 0;
$txndir = 0;
if (FALSE !== strpos($txn_fee_direction, "DR")) {
$feedir = -1;
}
if (FALSE !== strpos($txn_fee_direction, "CR")) {
$feedir = 1;
}
if (FALSE !== strpos($txn_direction, "DR")) {
$txndir = -1;
}
if (FALSE !== strpos($txn_direction, "CR")) {
$txndir = 1;
}
if (FALSE !== strpos($txn_refid, "-")) {
$txn_refid = "";
}
// fake parent txn, workaround
$txnamount = convert_to_usd($txn_cur, $txn_amount) * $txndir;
$feeamount = convert_to_usd($txn_cur, $txn_fee) * $feedir;
// insert txn
$qry = "insert into txn (ppid, mid, refid, fee, start_ts, stop_ts, amount, status, tcode, currency, ikey, ifile, txnlag )\n\t\tvalues ('" . pg_escape_string($txn_id) . "', '" . pg_escape_string($txn_merchantid) . "', '" . pg_escape_string($txn_refid) . "', {$feeamount}, '{$txn_startts}', '{$txn_stopts}',\n\t\t\t{$txnamount}, '" . pg_escape_string($txn_status) . "', '" . pg_escape_string($txn_tcode) . "', '{$txn_cur}', {$merchant_cube_id}, {$fid}, {$txn_lag});";
$res = @pg_query($qry);
if (false === $res) {
$qr = pg_errormessage($dbconn);
log_fatal("ERR error {$qr} in query: {$qry}");
}
$txn_count++;
if ($i > 1000) {
$dbg = $dbg . ".";
@pg_query("commit;");
@pg_query("begin;");
$i = 0;
}
}
// foreach line
@pg_query("commit;");
return $txn_count;
}
function process_conversion($data)
{
$cnt = count($this->params);
$i = 0;
$is_lead = isset($data['is_lead']) ? 1 : 0;
$is_sale = isset($data['is_sale']) ? 1 : 0;
unset($data['is_lead']);
unset($data['is_sale']);
switch ($data['txt_param20']) {
case 'UAH':
case 'uah':
$data['profit'] = convert_to_usd('uah', $data['profit']);
break;
case 'USD':
case 'usd':
$data['profit'] = convert_to_usd('usd', $data['profit']);
break;
default:
$data['profit'] = convert_to_usd('rub', $data['profit']);
break;
}
// Специальная обработка "статусного постбэка" от сети CTR. В этом случае приходит только статус, связанный с остальными данными через order_id (i3) и нужно поменять статус соостветствующей конвертации.
// https://uniquedesign.teamworkpm.net/tasks/3679474
$ctr_order = false;
// флаг, о том, что некоторые операции (замену логов) выполнять не нужно, так как это не полный запрос, а только статус
if ($data['network'] == 'CTR' and !empty($data['status'])) {
$q = 'SELECT * FROM `tbl_conversions` WHERE (`i3` = "' . mysql_real_escape_string($data['i3']) . '" AND `network` = "CTR") LIMIT 1';
if ($rs = db_query($q) and mysql_num_rows($rs) > 0) {
$r = mysql_fetch_assoc($rs);
$data['subid'] = $r['subid'];
}
dmp($data);
foreach ($data as $k => $v) {
if (!in_array($k, array('network', 'i3', 'status', 'txt_status', 'ak', 'date_add', 'subid'))) {
unset($data[$k]);
}
}
$ctr_order = true;
}
dmp($data);
if (isset($data['subid']) && $data['subid'] != '') {
//to_log('data', $data);
$subid = $data['subid'];
// мы скоро обнулим массив data, а subid нам ещё понадобится
$status = $data['status'];
$click_info = array();
// информация о клике
//Проверяем есть ли клик с этим SibID
$q = 'SELECT * FROM `tbl_clicks` WHERE `subid` = "' . mysql_real_escape_string($subid) . '"';
$r = mysql_query($q) or die(mysql_error());
if (mysql_num_rows($r) > 0) {
$click_info = mysql_fetch_assoc($r);
$click_id = $click_info['id'];
if ($data['profit'] > 0) {
$is_lead = $click_info['is_lead'] > 0 ? 1 : 0;
$is_sale = 1;
} else {
$is_lead = 1;
$is_sale = $click_info['is_sale'] > 0 ? 1 : 0;
}
mysql_query('UPDATE `tbl_clicks` SET `is_sale` = ' . $is_sale . ', `is_lead` = ' . intval($is_lead) . ', `conversion_price_main` = "' . mysql_real_escape_string($data['profit']) . '" WHERE `id` = ' . $click_id) or die(mysql_error());
}
// ----------------------------
// Готовим данные для конверсии
// ----------------------------
$upd = array();
// Инициализируем массив для запроса на обновление
// Дополнительные поля, которых нет в $params, но которые нам нужны в БД
$additional_fields = array('date_add', 'txt_status', 'status', 'network', 'type');
foreach ($data as $name => $value) {
if (array_key_exists($name, $this->params) or in_array($name, $additional_fields)) {
$upd[$name] = $value;
unset($data[$name]);
}
}
//if (empty($upd['date_add'])) {
$upd['date_add'] = mysql_now();
// date('Y-m-d H:i:s');
//}
// Проверяем, есть ли уже конверсия с таким SubID
$q = 'SELECT * FROM `tbl_conversions` WHERE `subid` = "' . mysql_real_escape_string($subid) . '" LIMIT 1';
$r = db_query($q) or die(mysql_error());
if (mysql_num_rows($r) > 0) {
$f = mysql_fetch_assoc($r);
$upd['id'] = $conv_id = $f['id'];
$q = updatesql($upd, 'tbl_conversions', 'id');
db_query($q);
// Чистим логи
if (!$ctr_order) {
db_query('DELETE FROM `tbl_postback_params` WHERE `conv_id` = ' . $f['id']) or die(mysql_error());
}
} else {
$q = insertsql($upd, 'tbl_conversions');
db_query($q);
$conv_id = mysql_insert_id();
}
// Нужно ли нам отменить продажу?
if ($status == 2) {
delete_sale($click_id, $conv_id, 'sale');
//return false;
}
// S2S сети
// https://uniquedesign.teamworkpm.net/tasks/4160565
$q = "select * \n \tfrom `tbl_adnets` \n \twhere `status` = '0' \n \tand `name` = '" . mysql_real_escape_string($data['n']) . "'";
if ($rs = db_query($q) and mysql_num_rows($rs) > 0) {
// Подставляем переменные в ссылку
$replace = array('[SUBID]' => $subid, '[PROFIT_USD]' => $this->source_data['profit'], '[PROFIT_RUB]' => convert_usd_to('rub', $this->source_data['profit']), '[PROFIT_EUR]' => convert_usd_to('eur', $this->source_data['profit']), '[PROFIT_UAH]' => convert_usd_to('uah', $this->source_data['profit']));
// Добавляем параметры Postback
foreach ($this->source_data as $name => $value) {
$replace['[POSTBACK_' . $name . ']'] = $value;
}
// Добавляем параметры перехода
for ($i = 1; $i <= 15; $i++) {
if (!empty($click_info['click_param_name' . $i])) {
$replace['[CLICK_' . $click_info['click_param_name' . $i] . ']'] = $click_info['click_param_value' . $i];
}
}
dmp($replace);
while ($r = mysql_fetch_assoc($rs)) {
$url = $r['url'];
// Поставляем все переменные
foreach ($replace as $k => $v) {
$url = str_ireplace($k, $v, $url);
}
// Cleaning not used []-params
$url = preg_replace('/\\ = (\\[[a-z\\_0-9]+\\])/i', ' = ', $url);
$result = send_post_request($url, array());
// Сохраняем S2S лог
$str = date('Y-m-d H:i:s') . ' SubID: ' . $subid . "\nURL:" . $url . "\\Result:" . $result[1] . "\n\n";
dmp(htmlspecialchars($str));
file_put_contents(_CACHE_PATH . '/log/' . '.s2s_' . date('Y-m-d'), $str, FILE_APPEND | LOCK_EX);
}
}
// Пишем postback логи
foreach ($data as $name => $value) {
if (strpos($name, 'pbsave_') !== false) {
$name = str_replace('pbsave_', '', $name);
$ins = array('conv_id' => $conv_id, 'name' => $name, 'value' => value);
$q = insertsql($ins, 'tbl_postback_params');
db_query($q);
}
}
}
}
