Jump to content
Chrisj

Help with checking 'balance' after 'wallet'

Recommended Posts

I am using a php web video script which allows Users to purchase videos successfully. The purchases are made from the amount available in the Users’ “wallet” (the User can also earn compensation, which gets added to his “balance”.). When there’s not enough available in the “wallet” for the purchase, the script checks and displays a message “not enough money”. I’d like help adding the ability where the script first checks the “wallet” amount, and if empty will then check the “balance” amount, and use a required amount from the balance, and if both are empty, then the “not enough money” appears. Here’s the portion of the code that I believe needs the modification:

// get cost video
    $db->where('name', 'video_play_price');
    $db_cost = $db->getOne('config');
    $video_cost = (float)$db_cost->value;

    $count_video = count($id_array);
    $user_id = $user->id;
    $wallet = (float)str_replace(',', '', $user->wallet);


	$amout = 0;
	foreach ($id_array as $id) {
            $video_id = (int)PT_Secure($id);

            // get video data
            $video = $db->where('id', $id)->getOne(T_VIDEOS);
			$amout += $video->video_play_price?$video->video_play_price:$video_cost;
	}

//   $amout = $video_cost * $count_video;

    $charge = ( $video_cost *0.50 );



    if ($wallet >= $amout) {

        //$new_wallet = (string)($wallet - $amout);
        $wallet = (string)($wallet - $amout);

        $db->startTransaction();

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


		// $uploader_amount = $video_cost - $charge; //100 - 20% = 80

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



			$video_cost_new = $video->video_play_price?$video->video_play_price:$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,
                'video_title' => $video->title,
                'user_id_uploaded' => $video->user_id,
            ]);

            if ($insert_buy) { $inserted_records++; }
            //add wallet users' video
        $userwallet = $db->where('id', $video->user_id)->getOne(T_USERS);


        //$videouserwallet = $userwallet->balance+$video_cost;
        $videouserwallet = $userwallet->balance+$uploader_amount;
        $db->where('id', $video->user_id);
        $update_balance = $db->update(T_USERS, [
          // 'wallet' => $videouserwallet,
            'balance' => number_format($videouserwallet, 1, '.', ''),
        ]);
        }


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


        if (($inserted_records == $count_video) && $update_wallet) {
            $db->commit();

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

            echo json_encode([
                'status' => 400,
                '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();

 

Share this post


Link to post
Share on other sites

So what have you tried and what is not working as expected?

Share this post


Link to post
Share on other sites

Thanks so much for your message.

Currently, the script just checks the 'wallet' and displays 'not enough money' if no funds are available. As far as 'what have you tried', I'm looking for a suggestion, because I don't know what to try.

Share this post


Link to post
Share on other sites

this code contains no useful comments, making it difficult to determine what it is trying to do, which makes it impossible to make changes to it. the original author, and you as you are making changes to it, should put useful comments in the code that describe what it is doing and why. this is what i think it is trying to do -

<?php

if(true) // whatever the actual conditional logic is
{
	// 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; // copy the user id for some reason
	
	// values shouldn't have thousands separators stored with them. formatting a number should only be done when it is displayed
	$wallet = (float)str_replace(',', '', $user->wallet);

	// add up the video prices
	$amout = 0;
	foreach ($id_array as $id) {
		
		// assuming this pt_secure function is actual secure, any $id in the following code should use $video_id instead
		$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
		$amout += $video->video_play_price?$video->video_play_price:$video_cost;
	}

	// determine if the user has enough credits
	// it is here that you would include the balance amount
	if ($wallet >= $amout) {

		// you would calculate new wallet and balance amounts
		$wallet = (string)($wallet - $amout);

		// insert/update all the following queries as a group
		$db->startTransaction();

		$inserted_records = 0;
		foreach ($id_array as $id)
		{
			// again, all the rest of the code should use $video_id, not $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 is redundant since you can retrieve it from the source video information 
				'user_id_uploaded' => $video->user_id, // the user who uploaded the video. this is also redundant
				// this should include the datetime of the purchase - actually there should be a purchase/order table and a purchase/order_items table
			]);

			// 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, '.', ''), // this is formatting with one decimal point only
			]);
		}

		// update the current user's wallet (and balance) amounts
		$db->where('id', $user_id);
		$update_wallet = $db->update(T_USERS, [
			'wallet' => $wallet, // you would include the new 'balance' too
		]);

		// 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) {
			$db->commit();

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

			echo json_encode([
				'status' => 400,
				'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();
}

if i have incorrectly deduced what any of these things are doing, make corrections to the comments before proceeding.

if this code had useful comments, so that the meaning of the line of code producing the $video_cost_new value would be in recent memory, there probably wouldn't be a mistake in the pricing value used in the insert query. the following line should use $video_cost_new -

'video_play_price' => (string)$video_cost, // the cost at the time of purchase // this is the default video cost not the $video_cost_new

 

next, the data design has one serious flaw and one that should be corrected -

1) the wallet and balance amounts should NOT be just columns in the user's row that get updated to change the value. by having just a value, you don't have any 'audit trail' should a programming mistake, duplicate form submission, or nefarious access alter a value incorrectly. you should instead have a separate 'account' table that gets a new row inserted for each transaction that affects the amount. this table would have all the who, what, when, where, and why information about each transaction ('wallet' or 'balance' would just be transaction types.) to get the total at any point in time for a user, you would just SUM() the +/- amounts for any particular user id. to get the either or both of the current 'wallet' or 'balance' amounts, you would group by the transaction type for any particular user id.

2) you should actually have two tables to hold the video purchase information. the first table should hold the unique/one-time information about the purchase. this will assign a purchase order id. the second table would hold the unique/one-time information about the items that were purchased, related back to the first table through the order_id.

lastly, i put comments in the code where it would need to be modified to do what you are asking. two are near the if ($wallet >= $amout) { conditional test (LOL amount is misspelled in the code.) the last one is in the update query (which should instead be one or two insert queries for the account table) for the current user's wallet (and balance) amounts.

Edited by mac_gyver

Share this post


Link to post
Share on other sites

Thank you for your helpful reply. Much appreciated.

I've replaced my original file with your commented code. Much thanks for helping clarify what is going on there, and for the small changes that you made to the (now improved) code.

I am still looking for a solution where the script first checks the “wallet” amount, and if empty will then check the “balance” amount, and use a required amount from the balance, and if both are empty, then the “not enough money” appears. This was suggested, but I failed at integrating it into the code:

if($wallet->balance() + $balance->balance() >= $amount) {
    $walletAmount = min($amount, $wallet->balance();
    $balanceAmount = ($walletAmount < $amount) ? $amount - $walletAmount : 0;
    // could then create deduct() methods to extract funds
    $wallet->deduct($walletAmount);
    $balance->deduct($balanceAmount);
}
else {
    // handle not enough money here
}

any guidance with that would be very helpful.

In regard to an 'account' table, thanks for that great advice. The script currently does have u_paid_videos table, as you can see at: // add data to paid table. Would expanding this table to include more be satisfactory? I've attached a picture of it's structure (all though time_date just shows NULL ).

Any additional comments/direction/advice is welcomed.

u_paid.png

Share this post


Link to post
Share on other sites

I have now set-up a separate 'account' table named 'purchases' (see attached image). What should the 'order_id' structure be?

Any additional guidance will be appreciated

purchases.png

Share this post


Link to post
Share on other sites
Quote

In regard to an 'account' table, thanks for that great advice. The script currently does have u_paid_videos table, as you can see at: // add data to paid table. Would expanding this table to include more be satisfactory? I've attached a picture of it's structure (all though time_date just shows NULL ).

no. the u_paid_videos table holds the information about the items(videos) in the order, but it contains redundant data. it has nothing to do with the accounts table i mentioned. everything i stated has to do with database normalization (you can research what that means) and is about properly organizing the data, so that you are only storing the data you need and are not repeating values in multiple places.

you are keeping track of two different type of things, 1) information about items/videos that are being purchased (as already stated needs an orders and an order_items table), and 2) user's money/credits.

the orders table should have at a minimum, columns for - an id (auto-increment, produces an order_id), user_id (who placed the order), date_time (when the order was placed), and a status column (indicates what state the order is in.) i'm not sure what the session key is, but if it needs to be stored per order it would go in the orders table.

the order_items table should have at a minimum, columns for - an id (auto-increment), order_id (what order the item is part of), item_id (the id of the item/video), quantity (this code is just a cart script and in general should support any quantity of an item), purchase_price (the price at the time of purchase), and a status column (indicates the status for each item in the order.)

the account table should have at a minimum, columns for - an id (auto-increment), user_id (who the account belongs to), amount (the + or -- amount), transaction_type (wallet, balance), and date_time (when the transaction was made.)

Edited by mac_gyver

Share this post


Link to post
Share on other sites

Thanks for your reply.

I appreciate your time you've taken to explain. It all makes sense.
It is  a lot for me to implement, although I'm working on it.

However, the first step for me, I believe, would be to get help with adding in these things you've commented, please, before I start improving my tables.:
// it is here that you would include the balance amount
// you would calculate new wallet and balance amounts
// you would include the new 'balance' too

any guidance, examples of what's needed there, will be greatly helpful.

Share this post


Link to post
Share on other sites

 

In my account 'wallet' I have 3, and in 'balance' I have 3, which equals a total of 6. I tried to purchase a total of 5, with this code added: 

if($wallet >= $amout) {
   // take money first from wallet
} elseif ($wallet + $balance >= $amout) {
   // take money first from wallet and/or balance
} else {
		echo json_encode([
			'status' => 400,
			'error_num' => 1,
			'error' => 'Not enough money'
		]);

} else {

I saw 
"Something went wrong. Please try again later!!"

 

any additional help is appreciated

Share this post


Link to post
Share on other sites

The "Something went wrong. Please try again later!!" is in a pop-up dialog box, and comes from the script.js file.

Also, when I comment out those additional lines of code, the script gets no error.

 

 

Share this post


Link to post
Share on other sites

So, I just tried this without success:
 

Code:
	if ($wallet >= $amount) {
		$wallet = (string)($wallet - $amount);
		} elseif ($wallet + $balance >= $amount) {
		$balance = (string)($balance - $amount);
		$db->startTransaction();

etc....

By ‘without success’, I mean that the same message appears as if just the ‘wallet’ doesn’t have enough “Not Enough Money” - when I test - by having 3 in ‘wallet’ and 3 in earnings (equals a total of 6) but try to purchase something that costs 5.

Any suggestion, guess or guidance will be appreciated.

Share this post


Link to post
Share on other sites

try something like this

$amount = 5;
$wallet = 3;
$balance = 3;

if ($wallet >= $amount) {
    $wallet -= $amount;
}
elseif ($wallet + $balance >= $amount) {
    $amount_remain = $amount - $wallet;
    $wallet = 0;
    $balance -= $amount_remain;
}
else {
    echo "Insufficient funds<br>";
}
echo "W: $wallet<br>B: $balance";

 

Share this post


Link to post
Share on other sites

Thanks for your reply. Much appreciated.

I have tried your suggestion without success.

I see a pop-up dialog box  that displays: "Something went wrong. Please try again later!!" (from the script.js file):

 

function PT_MultipleBuyVideo() {
    var checked = getSelectedVideos();
    if (!checked) { return false; }

    swal({
        title: "",
        type: "info",
        html:"Simply proceed to purchase " + countSelectedVideos() + " video(s) at a total cost of " + countTotalCredits() +" credits",
        showCancelButton: true,
        cancelButtonText: "Close",
        customClass: 'sweetalert-lg',
        confirmButtonText:'Proceed'
    }).then(function(){

        $.ajax({
            url: PT_Ajax_Requests_File() + 'aj/buy-video',
            type: 'POST',
            dataType: 'json',
            data: {id:checked},
        }).done(function(data){
            if (data.status == 200) {
                for (var i = 0; i < checked.length; i++) {
                    var button = $("button[data-action='multiple_select_button'][data-id='" + checked[i] + "']")
                    buttonMultipleSelectingStyle(button, 'purchased');
                }

                swal({
                    title: "Success",
                    type: "success",
                    html:"",
                    showCancelButton: true,
                    cancelButtonText: "Close",
                    customClass: 'sweetalert-lg',
                    confirmButtonText:'Go To Video(s)'
                }).then(function(){
                    window.location.href='/paid-list';
                });

            } else {
                if (data.error_num == 1) {
                    swal(
                        'Error!',
                        'Not enough money(test)',
                        'error'
                    );
                } else {
                    swal(
                        'Error!',
                        'Something went wrong. Please try again later!',
                        'error'
                    );
                }
            }
        }).fail(function() {
            swal(
                'Error!',
                'Something went wrong. Please try again later!!',
                'error'
            );
        })
    });
}

The only thing that shows 'not enough money' from 'wallet' or "success' (when there is enough) is this:

		if($wallet + $balance >= $amount) {
		$wallet = (string)($wallet - $amount);
		 //}elseif    $wallet = 0; {
		$balance = ($balance - $amount);

		$db->startTransaction();

Any other ideas will be appreciated

 

 

Share this post


Link to post
Share on other sites

Try reading your own code. Go through it line by line and check the value of each variable after each line executes. Does it look right to you? (you should end up with 0, 1, 5)

                                                        vales after executing line
                                                       -----------------------------
                                                       wallet     balance    amount
                                                          3          3         5

if($wallet + $balance >= $amount) {                       3          3         5
        $wallet = (string)($wallet - $amount);           -2          3         5
         //}elseif    $wallet = 0; {
        $balance = ($balance - $amount);                 -2         -2         5

 

Share this post


Link to post
Share on other sites

Thanks for your reply. 

This seems to work successfully, where a purchase uses the amount in ‘wallet’ first, and then uses the amount in ‘balance’ if there is not enough in ‘wallet’:

however, if all videos cost 2 (or higher), and there is “1” left in the ‘wallet’, it will never get used. I am looking to see how I can make more like if ‘wallet’ is zero then deduct from ‘earnings’ (or wallet + balance = amount)…

I have tried this revision without success:

		// Check if user has enough wallet amount to purchase video
		if($wallet >= $amount){

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

			} else {

			if($wallet < $amount && $wallet + $balance >= $amount){

			$wallet = (string)($wallet - $amount) + $balance = (string)($balance - $amount);

							$db->where('id', $user_id);
							$update_user_balance = $db->update(T_USERS, [
								'balance' => $balance
								]);
							$db->where('id', $user_id);
							$update_wallet = $db->update(T_USERS, [
							'wallet' => $wallet
							]);
						}
			}

I’m trying to say this;
if what’s in the ‘wallet’ is less than the amount AND $balance has more than or equal to the amount:

if($wallet < $amount && $wallet + $balance >= $amount){

 

then proceed to deduct whatever is left in 'wallet' to satisfy the amount + the rest of the amount from $balance:

$wallet = (string)($wallet - $amount) + $balance = (string)($balance - $amount);

 

but, apparently I need to say this better in code:
“then proceed to deduct whatever is left in 'wallet' to satisfy the amount + the rest of the amount from $balance”

any additional help is greatly appreciated.

 

Edited by Chrisj

Share this post


Link to post
Share on other sites

you have been given working logic (here and phpbuilder, which i just verified that both work) to test the amount, wallet, and wallet2/balance/earnings (you keep changing what it is called) and to calculate new wallet and wallet2/balance/earnings amounts, yet the code you continue to 'try' is using neither method. are you even reading the replies you are getting?

next, in the code i reviewed and added comments to here, there's no logic to get the starting wallet2/balance/earnings value. wouldn't that be a necessary step to get the code to work?

btw - your existing code has an UPDATE query at the correct point within the transaction/rollback logic. you would want to use the new wallet and wallet2/balance/earnings amounts in that existing single query, per the comment i added at that point, rather than to start adding more queries before the start of where the transaction/rollback logic starts.

Edited by mac_gyver

Share this post


Link to post
Share on other sites

Thanks again for your reply.

Yes, I've been reading. I've been given a lot of advice and tried many things without success (including what was provided here and elsewhere).
But, some of the explanation I don't understand, like this sentence "use the new wallet and balance amounts in that existing single query, per the comment i added at that point, rather than to start adding more queries before the start of where the transaction/rollback logic starts". 

Earlier in this thread I was told "Try reading your own code. Go through it line by line and check the value of each variable after each line executes". Based on that, as far as I can see, this should work, but shows 'not enough money:

 

} else {

//     4    +    4      >=    6
if ($wallet + $balance >= $amount) {

//   8  =               4    +    4
$balance = (string)($balance + $wallet);

//   2   =              8    -    6
$balance = (string)($balance - $amount);
$wallet= '0';

$db->where('id', $user_id);
$update_user_balance = $db->update(T_USERS, [
'balance' => $balance
]);

}
}

 

any additional guidance is appreciated

 

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.