Help with calculating purchases per day


#1

This file code is for video purchases, where a portion of the purchase amount goes to the Uploader of the video. His earnings portion, in this code, is his ‘balance’. It works successfully, but I’d like some guidance on how to calculate the Uploader’s earnings ‘balance’ per day (and per month). Here’s the code:

<?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) ) {

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

			// 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
				]);

			// 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);

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

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

if($wallet >= $amount){

$wallet = (string)($wallet - $amount);
$db->where('id', $user_id);
$update_wallet = $db->update(T_USERS, [
'wallet' => $wallet
]);

}else if ($wallet + $balance >= $amount) {
$balance = (string)($balance - ($amount - $wallet));
$wallet = (string)($wallet - $wallet);
$db->where('id', $user_id);
$update_user_balance = $db->update(T_USERS, [
'balance' => $balance
]);
$update_wallet = $db->update(T_USERS, [
'wallet' => $wallet
]);
} else {
echo json_encode([
'status' => 400,
'error_num' => 1,
'error' => 'Not enough money'
]);
exit();
}

		// if all the video records were inserted and the current user's wallet was updated, commit the changes
		if (($inserted_records == $count_video) && ($update_wallet OR $update_user_balance) ) {
			$db->commit();

			echo json_encode([
				'status' => 200
			]);
			exit();
		} else {
			$db->rollback();

			echo json_encode([
				'status' => 400,
				'_data' => [
					'inserted_records' => $inserted_records,
					 'count_video' => $count_video,
					  'update_wallet' => $update_wallet,
					   'update_user_balance' => $update_user_balance
				],
				'error' => 'Buy process error'
			]);
			exit();
		}

	} else {

		echo json_encode([
			'status' => 400,
			'error_num' => 1,
			'error' => 'Not enough money'
		]);
		exit();
	}

} else {

	echo json_encode([
		'status' => 400,
		'error' => 'Bad Request, Invalid or missing parameter'
		]);
	exit();

}

Any help is appreciated


#2

And where do you store time + amount? It’s just a litte SQL with a GROUP BY on the day and some aggregation function for the amount column.


#3

When you say “per day” or “per month”, do you mean the average, or the actual amount earned for each day or month?

If the former, it’s just a case of figuring out the number of days (or months) from today back to when the video was uploaded and dividing the total earned by the amount of time involved. If the latter, you need to examine the sales transactions (which might be in u_paid_videos) to get figures for the appropriate period, presuming there’s a date stored against each.


#4

Thanks for your replies.

I’m looking for actual amount earned for each day and each month (not average).
The u_paid_videos table has, time_date and as you can see, the uploader’s id and the price, but not the ‘balance’:

$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
]);

would the solution be to copy the parameters of the ‘balance’ column, from the ‘users’ table, to the ‘u_paid_videos’ table? and then group ‘time_date’ column + ‘balance’ column into another column ‘per_day’, for example?


#5

You don’t really need the balance in each transaction, because you’ll want to calculate that on a daily or monthly basis, you can do that by totalling up video_play_price.

You may need to play with the time_date column to group as you want to - you can extract just the date to group by day, and I’m sure there’s a way to extract the year and month to group by that.


#6

Thanks for your reply.
However, the video_play_price is the cost to purchase. The earnings ‘balance’ is the amount that the uploader receives upon a purchase. It is the ‘balance’ that I’m trying to get guidance on how to calculate the Uploader’s earnings ‘balance’ per day (and per month)


#7

Oh, sorry, the column names can be a bit confusing sometimes - “Balance” to me would be the running balance like it is on your bank statement. If that’s the figure that you need to total on a per-day or per-month basis, then it will need to be in each transaction, unless it can be calculated from the other stuff already in there. Or maybe there should be a separate transaction for the uploader to keep things apart.


#8

Thanks for your reply.
Yes, the earnings ‘balance’ (in the users db table) appears to be just a running total.

To begin with, I believe I may need help modifying the 1d #1 posted code (above), so that each portion of the transaction amount that gets added to the earnings ‘balance’ total is stored for retrieval. Do you agree?

If so can you suggest a line of code that could be added? And I’m guessing I’d need to create a new column in u_paid_videos table to hold each transaction amount? Like one named ‘earned_amount’, for example?

I look forward to any assistance.


#9

Yes, that sounds right. In your code you must have the amount that the uploader is getting paid (the amount that you want to total per day or per month), so you need to add a column for that, and stick it in the transaction.


#10

Thanks again for your reply.

Can you assist me with a line of code that would add the uploader amount into it’s own table column named ‘earned_amount’ from this portion of the displayed (above) code:

			//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);

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

			// 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, '.', ''),
			]);
		}

any additional guidance will be appreciated


#11

You can see in your post #4 how you specify each value for each column in the transaction table. So, first job is to add a column to the table and give it a name. Then, modify the code in post #4 to put a value, any value (but not zero), for testing, into that column.

Once you have that working, you can work on how to get the correct value from the code you posted in post #10 above.


#12

Thanks for your reply.

Adding this was successful in getting the earned portion of the transaction into the earned_amount column in the u_paid_videos table:

'earned_amount' => $uploader_amount,

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

This may not be enough info, but I’d like assistance changing the line 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 these changes without success.
Any suggestions will be welcomed.


#13

What should this mean? SQL error? Wrong data? Nobody here has this setup you have right in front of your face.


#14

Thanks for your reply.

By ‘without success’, I mean when I select the web button that would normally direct me to the transaction html page, it just hangs, no redirect. When I put the file as it was originally, I can redirect to the transaction page.

I have tried this now with (no success) same result:

$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);

and

$trans = $db->where(‘user_id_uploaded’,$user->id)->orderBy(‘id’,‘DESC’)->get( T_U_PAID_VIDEOS );

I look forward to any comments/guidance.


#15

Thanks again for your replies. I’ve made some progress.

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:

{{TODAY_EARN}}

instead of an ‘earned_amount’. Any additional guidance be appreciated.

#16

What converts the variable called $today_earn into a parameter called {{TODAY_EARN}}? Is this a bit like the other code you had, where you have to pass the variables from the page that calculates them into another page, and assign template names to them? See this thread: Help adding unique text to each page displayed

Also, is the value correct in your PHP variable $today_earn? That is, is the problem in calculating that variable, or is it in converting it into the parameter?


#17

Thanks for your reply.
Regarding ‘what converts the variable called $today_earn into a parameter called {{TODAY_EARN}}’ is the what I can’t determine. Yes, this is the php file that calls the html page:

<?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
));

Any help in determining what converts $today_earn to {{TODAY_EARN}} is appreciated.

Regarding, “is the value correct in the PHP variable $today_earn?” I believe it is, but how can I test or determine that?

I look forward to comments/suggestions/solutions.