Replacing a value into an existing transaction file

Chrisj · March 15, 2019

I'm using a php video web script which includes this buy_video.php file, which calculates the video uploader's earned amount, and insert's that amount in the 'u_paid_videos' table column named 'earned_amount', which appears in this file on line 73:

<?php
ob_start();

if (IS_LOGGED == false) {
    $data = array('status' => 400, 'error' => 'Not logged in');
    echo json_encode($data);
    exit();
}

if (!empty($_POST['id'])) {

    if (!is_array($_POST['id'])) {
        $id_array[] = $_POST['id'];
    } else {
        $id_array = $_POST['id'];
 	}

	// get cost video
	// get the default video price, to use if there is no per video play price
	$db->where('name', 'video_play_price');
	$db_cost = $db->getOne('config');
	$video_cost = (float)$db_cost->value;

	// the number of submitted videos - used to determine if all records were inserted
	$count_video = count($id_array);
	$user_id = $user->id;

	$wallet = (float)str_replace(',', '', $user->wallet);
	$balance = (float)str_replace(',', '', $user->balance);

	// add up the video prices
	$amount = 0;
	foreach ($id_array as $id) {

		$video_id = (int)PT_Secure($id);

		// get video data
		$video = $db->where('id', $id)->getOne(T_VIDEOS);
		// add the video play price if any, or the default price
		$amount += $video->video_play_price?$video->video_play_price:$video_cost;
	}

	// determine if the user has enough credits
	if( ($wallet >= $amount) OR ($balance + $wallet >= $amount) ) {
	//if( ($wallet >= $amount) OR ($balance >= $amount) ) {

		$db->startTransaction();

		$inserted_records = 0;
		foreach ($id_array as $id){

			$video_id = (int)PT_Secure($id);

			// get video data
			$video = $db->where('id', $id)->getOne(T_VIDEOS);

			// use the video play price if any, or the default price
			$video_cost_new = $video->video_play_price?$video->video_play_price:$video_cost;

			// credit the user 50% of the video cost
			$uploader_amount = $video_cost_new *0.50;



			// add data to paid table
			$insert_buy = $db->insert('u_paid_videos', [
				'id_user' => $user_id,
				'id_video' => $video_id,
				'session_key' => $_SESSION['session_key'],
				'video_play_price' => (string)$video_cost, // the cost at the time of purchase // this is the default video cost not the $video_cost_new
				'video_title' => $video->title, // storing the title
				'user_id_uploaded' => $video->user_id, // the user who uploaded the video
				'earned_amount' => $uploader_amount,
				]);

			// count successful inserted records
			if ($insert_buy) {
				$inserted_records++;
			}

			//update the 'balance' of the user who uploaded the video
			// get the user's record
			$userwallet = $db->where('id', $video->user_id)->getOne(T_USERS);

			// add to the balance
			$videouserwallet = $userwallet->balance+$uploader_amount;

			// update the record
			$db->where('id', $video->user_id);
			$update_balance = $db->update(T_USERS, [
				'balance' => number_format($videouserwallet, 1, '.', ''),
			]);
		}

		$update_wallet = null;
		$update_user_balance = null;


ETC .....................................................

Ultimately, I’m trying to integrate the buy_video.php file’s ‘$earned_amount’ into a web script’s existing transaction file.

Here are a couple of lines from that transaction file:

$this_day_video_earn = $db->rawQuery("SELECT SUM(amount - admin_com) AS sum FROM ".T_VIDEOS_TRSNS." c WHERE `time` >= ".$day_start." AND `time` <= ".$day_end." AND user_id = ".$pt->user->id);
$today_earn = $this_day_ads_earn[0]->sum + $this_day_video_earn[0]->sum ;

and

$trans = $db->where('user_id',$user->id)->orderBy('id','DESC')->get(T_VIDEOS_TRSNS);

That may not be enough info, but I’d like assistance changing those lines to:

$this_day_video_earn = $db->rawQuery("SELECT SUM(earned_amount) AS sum FROM “.T_VIDEOS.” c WHERE time >= “.$day_start.” AND time <= “.$day_end.” AND user_id = ".$pt->user->id);
  
  
  $trans        = $db->where('user_id',$user->id)->orderBy('id','DESC')->get(T_VIDEOS);

I tried those changes without success.
Any guidance/suggestions will be appreciated.

Edited March 15, 2019 by Chrisj

requinix · March 15, 2019

I can't tell what you're actually trying to end up with.

What is the code for the "transaction file" and what do you want to do with the "$earned_amount" (which is not a variable defined in that code you posted).

Chrisj · March 15, 2019

Thanks for your reply.

I'm initially looking for some coding help with how to put the amount that is in the 'upload_amount' column of the db table 'u_paid_videos' to properly replace (amount - admin_com) in this line:

$this_day_video_earn = $db->rawQuery("SELECT SUM(amount - admin_com) AS sum FROM ".T_VIDEOS_TRSNS." c WHERE `time` >= ".$day_start." AND `time` <= ".$day_end." AND user_id = ".$pt->user->id);

to help calculate the amount earned for the day

Edited March 15, 2019 by Chrisj

requinix · March 15, 2019

Presumably, you would change the query to

SELECT SUM(earned_amount) AS sum FROM <whatever T_* constant is the u_paid_videos table> WHERE <some condition to limit based on the date> AND user_id = <user ID>

There's a couple <things> in there that I don't know the answer to that you will have to figure out.

Chrisj · March 15, 2019

Thanks for your reply.

From the code I initially posted here it shows "T_VIDEOS" and this line of code I posted:

$this_day_video_earn = $db->rawQuery("SELECT SUM(amount - admin_com) AS sum FROM ".T_VIDEOS_TRSNS." c WHERE `time` >= ".$day_start." AND `time` <= ".$day_end." AND user_id = ".$pt->user->id);

it shows "T_VIDEOS_TRSNS". So, I know there is a db table named "videos" and a db table named "videos_transactions". Therefore, I am assuming that "T_U_PAID_VIDEOS" would identify the u_paid_videos db table.

So, I'm trying to modify this:

$this_day_video_earn = $db->rawQuery("SELECT SUM(amount - admin_com) AS sum FROM ".T_VIDEOS_TRSNS." c WHERE `time` >= ".$day_start." AND `time` <= ".$day_end." AND user_id = ".$pt->user->id);

I've tried this, without success:

$this_day_video_earn = $db->rawQuery("SELECT earned_amount FROM ".T_U_PAID_VIDEOS." c WHERE `time` >= ".$day_start." AND `time` <= ".$day_end." AND user_id = ".$pt->user->id);

can you please tell me if the syntax is correct in that line of code?

requinix · March 15, 2019

That's not quite what I said you needed to do. Look again.

I assume that u_paid_videos even has a "time" column?

Otherwise explain what "without success" means. What do you see? What do you expect to see? Are there any error messages?

Chrisj · March 15, 2019

Thanks for your reply.

I have now attempted this line, without success:

$this_day_video_earn = $db->rawQuery("SELECT SUM(earned_amount) AS sum FROM ".T_U_PAID_VIDEOS." c WHERE `time` >= ".$day_start." AND `time` <= ".$day_end." AND user_id_uploaded = ".$pt->user->id);

With success means when I select the web button that would normally direct me to the transaction html page, it just hangs, no redirect. When I put this file as it was originally, I can redirect to the transaction page. And yes, the u_paid_videos table has a 'time' column (identical to the videos_transactions table). Here is the file, where I've modified line 86 and 100:

<?php

if (!IS_LOGGED || ($pt->config->sell_videos_system == 'off' && $pt->config->usr_v_mon == 'off') ) {
	header('Location: ' . PT_Link('404'));
	exit;
}


$types = array('today','this_week','this_month','this_year');
$type = 'today';

if (!empty($_GET['type']) && in_array($_GET['type'], $types)) {
	$type = $_GET['type'];
}

if ($type == 'today') {
	$start = strtotime(date('M')." ".date('d').", ".date('Y')." 12:00am");
	$end = strtotime(date('M')." ".date('d').", ".date('Y')." 11:59pm");

	$array = array('00' => 0 ,'01' => 0 ,'02' => 0 ,'03' => 0 ,'04' => 0 ,'05' => 0 ,'06' => 0 ,'07' => 0 ,'08' => 0 ,'09' => 0 ,'10' => 0 ,'11' => 0 ,'12' => 0 ,'13' => 0 ,'14' => 0 ,'15' => 0 ,'16' => 0 ,'17' => 0 ,'18' => 0 ,'19' => 0 ,'20' => 0 ,'21' => 0 ,'22' => 0 ,'23' => 0);
	$ads_array = $array;
	$date_type = 'H';
	$pt->cat_type = 'today';
    $pt->chart_title = $lang->today;
    $pt->chart_text = date("l");
}
elseif ($type == 'this_week') {

	$time = strtotime(date('l').", ".date('M')." ".date('d').", ".date('Y'));
	if (date('l') == 'Saturday') {
		$start = strtotime(date('M')." ".date('d').", ".date('Y')." 12:00am");
	}
	else{
		$start = strtotime('last saturday, 12:00am', $time);
	}

	if (date('l') == 'Friday') {
		$end = strtotime(date('M')." ".date('d').", ".date('Y')." 11:59pm");
	}
	else{
		$end = strtotime('next Friday, 11:59pm', $time);
	}

	$array = array('Saturday' => 0 , 'Sunday' => 0 , 'Monday' => 0 , 'Tuesday' => 0 , 'Wednesday' => 0 , 'Thursday' => 0 , 'Friday' => 0);
	$ads_array = $array;
	$date_type = 'l';
	$pt->cat_type = 'this_week';
    $pt->chart_title = $lang->this_week;
    $pt->chart_text = date('y/M/d',$start)." To ".date('y/M/d',$end);
}
elseif ($type == 'this_month') {
	$start = strtotime("1 ".date('M')." ".date('Y')." 12:00am");
	$end = strtotime(cal_days_in_month(CAL_GREGORIAN, date('m'), date('Y'))." ".date('M')." ".date('Y')." 11:59pm");
	if (cal_days_in_month(CAL_GREGORIAN, date('m'), date('Y')) == 31) {
		$array = array('01' => 0 ,'02' => 0 ,'03' => 0 ,'04' => 0 ,'05' => 0 ,'06' => 0 ,'07' => 0 ,'08' => 0 ,'09' => 0 ,'10' => 0 ,'11' => 0 ,'12' => 0 ,'13' => 0 ,'14' => 0 ,'15' => 0 ,'16' => 0 ,'17' => 0 ,'18' => 0 ,'19' => 0 ,'20' => 0 ,'21' => 0 ,'22' => 0 ,'23' => 0,'24' => 0 ,'25' => 0 ,'26' => 0 ,'27' => 0 ,'28' => 0 ,'29' => 0 ,'30' => 0 ,'31' => 0);
	}elseif (cal_days_in_month(CAL_GREGORIAN, date('m'), date('Y')) == 30) {
		$array = array('01' => 0 ,'02' => 0 ,'03' => 0 ,'04' => 0 ,'05' => 0 ,'06' => 0 ,'07' => 0 ,'08' => 0 ,'09' => 0 ,'10' => 0 ,'11' => 0 ,'12' => 0 ,'13' => 0 ,'14' => 0 ,'15' => 0 ,'16' => 0 ,'17' => 0 ,'18' => 0 ,'19' => 0 ,'20' => 0 ,'21' => 0 ,'22' => 0 ,'23' => 0,'24' => 0 ,'25' => 0 ,'26' => 0 ,'27' => 0 ,'28' => 0 ,'29' => 0 ,'30' => 0);
	}elseif (cal_days_in_month(CAL_GREGORIAN, date('m'), date('Y')) == 29) {
		$array = array('01' => 0 ,'02' => 0 ,'03' => 0 ,'04' => 0 ,'05' => 0 ,'06' => 0 ,'07' => 0 ,'08' => 0 ,'09' => 0 ,'10' => 0 ,'11' => 0 ,'12' => 0 ,'13' => 0 ,'14' => 0 ,'15' => 0 ,'16' => 0 ,'17' => 0 ,'18' => 0 ,'19' => 0 ,'20' => 0 ,'21' => 0 ,'22' => 0 ,'23' => 0,'24' => 0 ,'25' => 0 ,'26' => 0 ,'27' => 0 ,'28' => 0 ,'29' => 0);
	}elseif (cal_days_in_month(CAL_GREGORIAN, date('m'), date('Y')) == 28) {
		$array = array('01' => 0 ,'02' => 0 ,'03' => 0 ,'04' => 0 ,'05' => 0 ,'06' => 0 ,'07' => 0 ,'08' => 0 ,'09' => 0 ,'10' => 0 ,'11' => 0 ,'12' => 0 ,'13' => 0 ,'14' => 0 ,'15' => 0 ,'16' => 0 ,'17' => 0 ,'18' => 0 ,'19' => 0 ,'20' => 0 ,'21' => 0 ,'22' => 0 ,'23' => 0,'24' => 0 ,'25' => 0 ,'26' => 0 ,'27' => 0 ,'28' => 0);
	}
	$ads_array = $array;
	$pt->month_days = count($array);
	$date_type = 'd';
	$pt->cat_type = 'this_month';
    $pt->chart_title = $lang->this_month;
    $pt->chart_text = date("M");
}
elseif ($type == 'this_year') {
	$start = strtotime("1 January ".date('Y')." 12:00am");
	$end = strtotime("31 December ".date('Y')." 11:59pm");
	$array = array('01' => 0 ,'02' => 0 ,'03' => 0 ,'04' => 0 ,'05' => 0 ,'06' => 0 ,'07' => 0 ,'08' => 0 ,'09' => 0 ,'10' => 0 ,'11' => 0 ,'12' => 0);
	$ads_array = $array;
	$date_type = 'm';
	$pt->cat_type = 'this_year';
    $pt->chart_title = $lang->this_year;
    $pt->chart_text = date("Y");
}

$day_start = strtotime(date('M')." ".date('d').", ".date('Y')." 12:00am");
$day_end = strtotime(date('M')." ".date('d').", ".date('Y')." 11:59pm");
$this_day_ads_earn = $db->rawQuery("SELECT SUM(amount) AS sum FROM ".T_ADS_TRANS." c WHERE `time` >= ".$day_start." AND `time` <= ".$day_end."  AND type = 'video' AND video_owner = ".$pt->user->id);

$this_day_video_earn = $db->rawQuery("SELECT SUM(earned_amount) AS sum FROM ".T_U_PAID_VIDEOS." c WHERE `time` >= ".$day_start." AND `time` <= ".$day_end." AND user_id_uploaded = ".$pt->user->id);
//$this_day_video_earn = $db->rawQuery("SELECT earned_amount FROM ".T_U_PAID_VIDEOS." c WHERE `time` >= ".$day_start." AND `time` <= ".$day_end." AND user_id_uploaded = ".$pt->user->id);
//$this_day_video_earn = $db->rawQuery("SELECT SUM(amount - admin_com) AS sum FROM ".T_VIDEOS_TRSNS." c WHERE `time` >= ".$day_start." AND `time` <= ".$day_end." AND user_id = ".$pt->user->id);
$today_earn = $this_day_ads_earn[0]->sum + $this_day_video_earn[0]->sum ;

$month_start = strtotime("1 ".date('M')." ".date('Y')." 12:00am");
$month_end = strtotime(cal_days_in_month(CAL_GREGORIAN, date('m'), date('Y'))." ".date('M')." ".date('Y')." 11:59pm");
$this_month_ads_earn = $db->rawQuery("SELECT SUM(amount) AS sum FROM ".T_ADS_TRANS." c WHERE `time` >= ".$month_start." AND `time` <= ".$month_end."  AND type = 'video' AND video_owner = ".$pt->user->id);
$this_month_video_earn = $db->rawQuery("SELECT SUM(amount - admin_com) AS sum FROM ".T_VIDEOS_TRSNS." c WHERE `time` >= ".$month_start." AND `time` <= ".$month_end." AND user_id = ".$pt->user->id);
$month_earn = $this_month_ads_earn[0]->sum + $this_month_video_earn[0]->sum ;
// print_r($this_month_video_earn);
// exit();



$trans        = $db->where('user_id_uploaded',$user->id)->orderBy('id','DESC')->get(T_U_PAID_VIDEOS);
//$trans        = $db->where('user_id',$user->id)->orderBy('id','DESC')->get(T_VIDEOS_TRSNS);
$ads_trans = $db->where('time',$start,'>=')->where('time',$end,'<=')->where('video_owner',$pt->user->id)->where('type','video')->get(T_ADS_TRANS);
$total_ads = 0;
if (!empty($ads_trans)) {
	foreach ($ads_trans as $key => $ad) {
		if ($ad->time >= $start && $ad->time <= $end) {
			$day = date($date_type,$ad->time);
			if (in_array($day, array_keys($ads_array))) {
				$ads_array[$day] += $ad->amount;
				$total_ads += $ad->amount;
			}
		}
	}
}
$ads_list        = "";


$total_earn = 0;
if (!empty($trans)) {
	foreach ($trans as $tr) {
		$video = PT_GetVideoByID($tr->video_id, 0, 0, 2);

		$user_data   = PT_UserData($tr->paid_id);

		$currency         = "";
		$admin_currency         = "";
		$net = 0;
		if ($tr->currency == "USD") {
			$currency     = "$";
			$admin_currency     = "$".$tr->admin_com;
			$net = $tr->amount - $tr->admin_com;
		}
		else if($tr->currency == "EUR"){
			$currency     = "â‚¬";
			$admin_currency     = "â‚¬".$tr->admin_com;
			$net = $tr->amount - $tr->admin_com;
		}
		elseif ($tr->currency == "EUR_PERCENT") {
			$currency     = "â‚¬";
			$admin_currency = $tr->admin_com."%";
			$net = $tr->amount - ($tr->admin_com * $tr->amount)/100;
		}
		elseif ($tr->currency == "USD_PERCENT") {
			$currency     = "$";
			$admin_currency = $tr->admin_com."%";
			$net = $tr->amount - ($tr->admin_com * $tr->amount)/100;
		}

		if ($tr->time >= $start && $tr->time <= $end) {
			$day = date($date_type,$tr->time);
			if (in_array($day, array_keys($array))) {
				$array[$day] += $net;
			}
		}

		$total_earn = $total_earn + (float)$net;
		if (!empty($video) && !empty($user_data)) {
			$ads_list   .= PT_LoadPage('transactions/list',array(
				'ID' => $tr->id,
				'PAID_USER' => substr($user_data->name, 0,20),
				'PAID_URL' => $user_data->url,
				'USER_NAME' => $user_data->username,
				'VIDEO_NAME' => substr($video->title, 0,20) ,
				'VIDEO_URL' => $video->url,
				'VIDEO_ID_' => PT_Slug($video->title, $video->video_id),
				'AMOUNT' => $tr->amount,
				"CURRENCY" => $currency,
				"A_CURRENCY" => $admin_currency,
				"NET" => $net,
				"TIME" => PT_Time_Elapsed_String($tr->time)
			));
		}
	}
}
$total_earn = $total_earn + $total_ads;

$pt->array = implode(', ', $array);
$pt->ads_array = implode(', ', $ads_array);
$pt->page_url_ = $pt->config->site_url.'/transactions';
$pt->title       = $lang->earnings . ' | ' . $pt->config->title;
$pt->page        = "transactions";
$pt->description = $pt->config->description;
$pt->keyword     = @$pt->config->keyword;
$pt->content     = PT_LoadPage('transactions/content',array(
	'CURRENCY'   => $currency,
	'ADS_LIST'   => $ads_list,
	'TOTAL_EARN' => $total_earn,
	'TODAY_EARN' => $today_earn,
	'MONTH_EARN' => $month_earn
));

Any additional enlightenment/guidance will be appreciated.

Chrisj · March 19, 2019

This code no longer blocks the html page from displaying ( I apparently have it correct now ?

 $this_day_video_earn = $db->rawQuery("SELECT SUM(earned_amount) AS sum FROM ".T_U_PAID_VIDEOS." c WHERE `time` >= ".$day_start." AND `time` <= ".$day_end." AND user_id_uploaded = ".$pt->user->id);

 $today_earn = $this_day_ads_earn[0]->sum + $this_day_video_earn[0]->sum ;

However, after a transaction (the 'earned_amount' successfully appears in the 'u_paid_videos' table > in the 'earned_amount' column) the html still shows $0 in this code: <p>{{TODAY_EARN}}</p> instead of the earned_amount. Any additional guidance be appreciated.

requinix · March 19, 2019

Then apparently the queries didn't return anything. What if you try running them yourself manually? Are you sure the variables have the values you think they do?

Chrisj · March 20, 2019

Thanks for your reply.

Can you please clarify your message?

which variables/values?

how would I do it manually?

I look forward to any additional direction.

requinix · March 20, 2019

56 minutes ago, Chrisj said:

which variables/values?

The ones that are being used in the query.

56 minutes ago, Chrisj said:

how would I do it manually?

By using whatever tool you like to interact with your database. I'm guessing phpMyAdmin.

Chrisj · March 20, 2019

Thanks again for your reply, I have access to the scripts phpnyadmin, but I don’t know how to do what you’re suggesting.

any guidance is appreciated

requinix · March 20, 2019

echo "SELECT SUM(earned_amount) AS sum FROM ".T_U_PAID_VIDEOS." c WHERE `time` >= ".$day_start." AND `time` <= ".$day_end." AND user_id_uploaded = ".$pt->user->id;

Chrisj · March 20, 2019

Thanks so much for your help.

I've added that line, visited the html page and see this:

SELECT SUM(earned_amount) AS sum FROM u_paid_videos c WHERE `time` >= 1553040000 AND 'time' <= 1553126340 AND user_id_uploaded = 2

I'm not sure what this tells me. It seems to be generating the time, and the correct user_id_uploaded (user number 2), but maybe it should show something numeric instead of earned_amount? Maybe something else needs to be modified in the following file code, but I have looked it over and can't figure out if it does need another change. I look forward to any feedback/guidance. Much thanks again.

<?php

if (!IS_LOGGED || ($pt->config->sell_videos_system == 'off' && $pt->config->usr_v_mon == 'off') ) {
	header('Location: ' . PT_Link('404'));
	exit;
}


$types = array('today','this_week','this_month','this_year');
$type = 'today';

if (!empty($_GET['type']) && in_array($_GET['type'], $types)) {
	$type = $_GET['type'];
}

if ($type == 'today') {
	$start = strtotime(date('M')." ".date('d').", ".date('Y')." 12:00am");
	$end = strtotime(date('M')." ".date('d').", ".date('Y')." 11:59pm");

	$array = array('00' => 0 ,'01' => 0 ,'02' => 0 ,'03' => 0 ,'04' => 0 ,'05' => 0 ,'06' => 0 ,'07' => 0 ,'08' => 0 ,'09' => 0 ,'10' => 0 ,'11' => 0 ,'12' => 0 ,'13' => 0 ,'14' => 0 ,'15' => 0 ,'16' => 0 ,'17' => 0 ,'18' => 0 ,'19' => 0 ,'20' => 0 ,'21' => 0 ,'22' => 0 ,'23' => 0);
	$ads_array = $array;
	$date_type = 'H';
	$pt->cat_type = 'today';
    $pt->chart_title = $lang->today;
    $pt->chart_text = date("l");
}
elseif ($type == 'this_week') {

	$time = strtotime(date('l').", ".date('M')." ".date('d').", ".date('Y'));
	if (date('l') == 'Saturday') {
		$start = strtotime(date('M')." ".date('d').", ".date('Y')." 12:00am");
	}
	else{
		$start = strtotime('last saturday, 12:00am', $time);
	}

	if (date('l') == 'Friday') {
		$end = strtotime(date('M')." ".date('d').", ".date('Y')." 11:59pm");
	}
	else{
		$end = strtotime('next Friday, 11:59pm', $time);
	}

	$array = array('Saturday' => 0 , 'Sunday' => 0 , 'Monday' => 0 , 'Tuesday' => 0 , 'Wednesday' => 0 , 'Thursday' => 0 , 'Friday' => 0);
	$ads_array = $array;
	$date_type = 'l';
	$pt->cat_type = 'this_week';
    $pt->chart_title = $lang->this_week;
    $pt->chart_text = date('y/M/d',$start)." To ".date('y/M/d',$end);
}
elseif ($type == 'this_month') {
	$start = strtotime("1 ".date('M')." ".date('Y')." 12:00am");
	$end = strtotime(cal_days_in_month(CAL_GREGORIAN, date('m'), date('Y'))." ".date('M')." ".date('Y')." 11:59pm");
	if (cal_days_in_month(CAL_GREGORIAN, date('m'), date('Y')) == 31) {
		$array = array('01' => 0 ,'02' => 0 ,'03' => 0 ,'04' => 0 ,'05' => 0 ,'06' => 0 ,'07' => 0 ,'08' => 0 ,'09' => 0 ,'10' => 0 ,'11' => 0 ,'12' => 0 ,'13' => 0 ,'14' => 0 ,'15' => 0 ,'16' => 0 ,'17' => 0 ,'18' => 0 ,'19' => 0 ,'20' => 0 ,'21' => 0 ,'22' => 0 ,'23' => 0,'24' => 0 ,'25' => 0 ,'26' => 0 ,'27' => 0 ,'28' => 0 ,'29' => 0 ,'30' => 0 ,'31' => 0);
	}elseif (cal_days_in_month(CAL_GREGORIAN, date('m'), date('Y')) == 30) {
		$array = array('01' => 0 ,'02' => 0 ,'03' => 0 ,'04' => 0 ,'05' => 0 ,'06' => 0 ,'07' => 0 ,'08' => 0 ,'09' => 0 ,'10' => 0 ,'11' => 0 ,'12' => 0 ,'13' => 0 ,'14' => 0 ,'15' => 0 ,'16' => 0 ,'17' => 0 ,'18' => 0 ,'19' => 0 ,'20' => 0 ,'21' => 0 ,'22' => 0 ,'23' => 0,'24' => 0 ,'25' => 0 ,'26' => 0 ,'27' => 0 ,'28' => 0 ,'29' => 0 ,'30' => 0);
	}elseif (cal_days_in_month(CAL_GREGORIAN, date('m'), date('Y')) == 29) {
		$array = array('01' => 0 ,'02' => 0 ,'03' => 0 ,'04' => 0 ,'05' => 0 ,'06' => 0 ,'07' => 0 ,'08' => 0 ,'09' => 0 ,'10' => 0 ,'11' => 0 ,'12' => 0 ,'13' => 0 ,'14' => 0 ,'15' => 0 ,'16' => 0 ,'17' => 0 ,'18' => 0 ,'19' => 0 ,'20' => 0 ,'21' => 0 ,'22' => 0 ,'23' => 0,'24' => 0 ,'25' => 0 ,'26' => 0 ,'27' => 0 ,'28' => 0 ,'29' => 0);
	}elseif (cal_days_in_month(CAL_GREGORIAN, date('m'), date('Y')) == 28) {
		$array = array('01' => 0 ,'02' => 0 ,'03' => 0 ,'04' => 0 ,'05' => 0 ,'06' => 0 ,'07' => 0 ,'08' => 0 ,'09' => 0 ,'10' => 0 ,'11' => 0 ,'12' => 0 ,'13' => 0 ,'14' => 0 ,'15' => 0 ,'16' => 0 ,'17' => 0 ,'18' => 0 ,'19' => 0 ,'20' => 0 ,'21' => 0 ,'22' => 0 ,'23' => 0,'24' => 0 ,'25' => 0 ,'26' => 0 ,'27' => 0 ,'28' => 0);
	}
	$ads_array = $array;
	$pt->month_days = count($array);
	$date_type = 'd';
	$pt->cat_type = 'this_month';
    $pt->chart_title = $lang->this_month;
    $pt->chart_text = date("M");
}
elseif ($type == 'this_year') {
	$start = strtotime("1 January ".date('Y')." 12:00am");
	$end = strtotime("31 December ".date('Y')." 11:59pm");
	$array = array('01' => 0 ,'02' => 0 ,'03' => 0 ,'04' => 0 ,'05' => 0 ,'06' => 0 ,'07' => 0 ,'08' => 0 ,'09' => 0 ,'10' => 0 ,'11' => 0 ,'12' => 0);
	$ads_array = $array;
	$date_type = 'm';
	$pt->cat_type = 'this_year';
    $pt->chart_title = $lang->this_year;
    $pt->chart_text = date("Y");
}

$day_start = strtotime(date('M')." ".date('d').", ".date('Y')." 12:00am");
$day_end = strtotime(date('M')." ".date('d').", ".date('Y')." 11:59pm");
$this_day_ads_earn = $db->rawQuery("SELECT SUM(amount) AS sum FROM ".T_ADS_TRANS." c WHERE `time` >= ".$day_start." AND `time` <= ".$day_end."  AND type = 'video' AND video_owner = ".$pt->user->id);

$this_day_video_earn = $db->rawQuery("SELECT SUM(earned_amount) AS sum FROM ".T_U_PAID_VIDEOS." c WHERE `time` >= ".$day_start." AND `time` <= ".$day_end." AND user_id_uploaded = ".$pt->user->id);
//$this_day_video_earn = $db->rawQuery("SELECT SUM(amount - admin_com) AS sum FROM ".T_VIDEOS_TRSNS." c WHERE `time` >= ".$day_start." AND `time` <= ".$day_end." AND user_id = ".$pt->user->id);
$today_earn = $this_day_ads_earn[0]->sum + $this_day_video_earn[0]->sum ;

$month_start = strtotime("1 ".date('M')." ".date('Y')." 12:00am");
$month_end = strtotime(cal_days_in_month(CAL_GREGORIAN, date('m'), date('Y'))." ".date('M')." ".date('Y')." 11:59pm");
$this_month_video_earn = $db->rawQuery("SELECT (earned_amount) FROM u_paid_videos c WHERE `time` >= ".$month_start." AND `time` <= ".$month_end." AND user_id_uploaded = ".$pt->user->id);
//$this_month_video_earn = $db->rawQuery("SELECT SUM(amount - admin_com) AS sum FROM ".T_VIDEOS_TRSNS." c WHERE `time` >= ".$month_start." AND `time` <= ".$month_end." AND user_id = ".$pt->user->id);
$month_earn = $this_month_ads_earn[0]->sum + $this_month_video_earn[0]->sum ;
// print_r($this_month_video_earn);
// exit();

echo "SELECT SUM(earned_amount) AS sum FROM ".T_U_PAID_VIDEOS." c WHERE `time` >= ".$day_start." AND `time` <= ".$day_end." AND user_id_uploaded = ".$pt->user->id;

$trans        = $db->where('user_id_uploaded',$user->id)->orderBy('id_user')->get(T_U_PAID_VIDEOS);
//$trans        = $db->where('user_id',$user->id)->orderBy('id','DESC')->get(T_VIDEOS_TRSNS);
$ads_trans = $db->where('time',$start,'>=')->where('time',$end,'<=')->where('video_owner',$pt->user->id)->where('type','video')->get(T_ADS_TRANS);
$total_ads = 0;
if (!empty($ads_trans)) {
	foreach ($ads_trans as $key => $ad) {
		if ($ad->time >= $start && $ad->time <= $end) {
			$day = date($date_type,$ad->time);
			if (in_array($day, array_keys($ads_array))) {
				$ads_array[$day] += $ad->amount;
				$total_ads += $ad->amount;
			}
		}
	}
}
$ads_list        = "";


$total_earn = 0;
if (!empty($trans)) {
	foreach ($trans as $tr) {
		$video = PT_GetVideoByID($tr->video_id, 0, 0, 2);

		$user_data   = PT_UserData($tr->paid_id);

		$currency         = "";
		$admin_currency         = "";
		$net = 0;
		if ($tr->currency == "USD") {
			$currency     = "$";
			$admin_currency     = "$".$tr->admin_com;
			$net = $tr->amount - $tr->admin_com;
		}
		else if($tr->currency == "EUR"){
			$currency     = "â‚¬";
			$admin_currency     = "â‚¬".$tr->admin_com;
			$net = $tr->amount - $tr->admin_com;
		}
		elseif ($tr->currency == "EUR_PERCENT") {
			$currency     = "â‚¬";
			$admin_currency = $tr->admin_com."%";
			$net = $tr->amount - ($tr->admin_com * $tr->amount)/100;
		}
		elseif ($tr->currency == "USD_PERCENT") {
			$currency     = "$";
			$admin_currency = $tr->admin_com."%";
			$net = $tr->amount - ($tr->admin_com * $tr->amount)/100;
		}

		if ($tr->time >= $start && $tr->time <= $end) {
			$day = date($date_type,$tr->time);
			if (in_array($day, array_keys($array))) {
				$array[$day] += $net;
			}
		}

		$total_earn = $total_earn + (float)$net;
		if (!empty($video) && !empty($user_data)) {
			$ads_list   .= PT_LoadPage('transactions/list',array(
				'ID' => $tr->id,
				'PAID_USER' => substr($user_data->name, 0,20),
				'PAID_URL' => $user_data->url,
				'USER_NAME' => $user_data->username,
				'VIDEO_NAME' => substr($video->title, 0,20) ,
				'VIDEO_URL' => $video->url,
				'VIDEO_ID_' => PT_Slug($video->title, $video->video_id),
				'AMOUNT' => $tr->amount,
				"CURRENCY" => $currency,
				"A_CURRENCY" => $admin_currency,
				"NET" => $net,
				"TIME" => PT_Time_Elapsed_String($tr->time)
			));
		}
	}
}
$total_earn = $total_earn + $total_ads;

$pt->array = implode(', ', $array);
$pt->ads_array = implode(', ', $ads_array);
$pt->page_url_ = $pt->config->site_url.'/transactions';
$pt->title       = $lang->earnings . ' | ' . $pt->config->title;
$pt->page        = "transactions";
$pt->description = $pt->config->description;
$pt->keyword     = @$pt->config->keyword;
$pt->content     = PT_LoadPage('transactions/content',array(
	'CURRENCY'   => $currency,
	'ADS_LIST'   => $ads_list,
	'TOTAL_EARN' => $total_earn,
	'TODAY_EARN' => $today_earn,
	'MONTH_EARN' => $month_earn
));

requinix · March 20, 2019

Remember the thing I said about running the query manually?

Chrisj · March 20, 2019

Thanks for your reply. But I don’t know how to do it manually. Any additional guidance is apprecisted

requinix · March 20, 2019

Start here.

Chrisj · March 20, 2019

Thanks for your reply.

I could have googled that, but I'm looking for some assistance at phpfreaks.com.

Getting help with determining what converts the variable called $today_earn into a parameter called {{TODAY_EARN}} in the code may help me resolve this. Any additional guidance is appreciated.

Edited March 20, 2019 by Chrisj

Barand · March 20, 2019

14 hours ago, Chrisj said:

SELECT SUM(earned_amount) AS sum FROM u_paid_videos c WHERE `time` >= 1553040000 AND 'time' <= 1553126340 AND user_id_uploaded = 2

Copy that query and execute it in phpMyAdmin.

I am sorry we can't do it for you.

Chrisj · March 20, 2019

Thanks for your reply/assistance.

I have attached the resulting screen shot, after running this:

SELECT SUM(earned_amount) AS sum FROM u_paid_videos c WHERE `time` >= 1553040000 AND 'time' <= 1553126340 AND user_id_uploaded = 2

I look forward to any comments/suggestions

Barand · March 20, 2019

You have single quotes arond "time" so it is being treated as a string literal. Use backticks like you have around the previous one.

Edited March 20, 2019 by Barand
spwlling error

mac_gyver · March 20, 2019

the 'echo'ed sql query statement has some single quotes around the 2nd occurrence of the time column. this 1st showed up in the OPs post.

edit: which Barand just posted too without any notification from the form software about a new post in the thread.

Edited March 20, 2019 by mac_gyver

Chrisj · March 20, 2019

Thanks for your replies.

I have attached the resulting new screen shot, after running this:

SELECT SUM(earned_amount) AS sum FROM u_paid_videos c WHERE `time` >= 1553040000 AND `time` <= 1553126340 AND user_id_uploaded = 2

I look forward to any comments/suggestions.

requinix · March 20, 2019

You've got no data, Lieutenant Dan.

Look in the table for data you expected to see accounted for, then look at its "time" and "user_id_uploaded" values to understand why it wasn't being returned by your query.

If you see some data but still don't understand why, screenshot it.

Chrisj · March 20, 2019

Thanks again for your reply.

I just ran another test transaction, and in the db table (image attached) I see 'user_id_uploaded' and 'earned_amount' populating, but not the `time` column.

Any clue as to why that is happen ss1.png.cb91a596546a91867f5e29f6092edc8b.png ing is welcomed.

Sign In

Replacing a value into an existing transaction file

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information