Quey Builder with IF condition in Lockable

Question

People I have a problem to list certain results of a database using Arabic, and I have tried everything to elaborate a logic to list these results and I can not elaborate this logic.

I have a table called Payments, that table lists certain payouts of a relationship with another table called Campaign, that is, the payments table are payments made for a particular campaign.

I have a model called Campaign where I have a function that makes this relationship looking for results that have status = 3, which in case are payment approved.

public function success_payments(){
    return $this->hasMany(Payment::class)->whereStatus('3');
}

This function returns results from the Payment table that have the status = 3 field, and this table has one more field called updated_at of the timestamp type, and that’s where my problem is, i need to include in this query a condition that returns me results where the current date is >= the field updated_at + 30 days, ie I need to add 30 days to the value of the field updated_at and then check if the current date is >= a variable that has this field added 30 days, or if there is another way of doing so.

Why this condition.

This current query returns payment made for certain campaign that have the status = 3 field, which in case is payment confirmed, that is, it returns all payments that have been confirmed, but I use the pagseguro as payment gatway and the same, in my case, only release payments 30 days after confirmation.

That’s why I need to show the owners of these campaigns only the payments that have already been confirmed and that 30 days have passed.

If anyone can give me a strength please immensely.

Answer 1

Why not consult the current date less 30 days? Will return payments that were made 30 days...

return $this->hasMany(Payment::class)->whereStatus('3')->where('updated_at', '>=', date('Y-m-d h:i:s', strtotime('-30 days')))

But I don’t know if your logic is correct, because you’re assuming that the updated_at will only be changed when the status is changed to 3, if it is changed again after confirming the payment your logic breaks, I would add a field with the payment date.

Answer 2

I have worked with credit card conciliation with buyers, in my case it was with Getnet. I believe I know the purpose for this information, which in case is to seek all payments already made more than 30 days, and check if these payments actually happened by Pagseguro.

In this case I suggest you change the logic.

Basically you would build another table to control these payments.

Then it would be: Campaign, Payment, Payment_card and Card_contract

The process is: if it constitutes a campaign, and a payment that will have differentiated status, for example: "Waiting for payment". After confirmation of payment, ie change to status 3. The customer has already paid this amount, the "debt" is no longer his, now the debt is Pagseguro with you. Therefore, create another table with the data of this payment. And create a configuration table (Card_contract) of your contract with Pagseguro (fees, payment dates, etc). Every time you modify the Payment table to status = 3, create a record in Payment_card (do it by transaction so there are no inconsistencies). This record in Payment_card should fetch the data from the contract table. Then you can make the relationship (hasMany) between Payment and Payment_card. After you can create Schedules/Jobs to search for EDI-PAGSEGURO the amounts already paid, and make the low/payment on Payment_card.

Practical example:

Client created the campaign "Black Friday". Totaled R $ 150,00 due for day 10/01/2020. At this time you have a record in the Campaign table and a record in the Payment table with status = 1 (Waiting for payment).

The due date arrived on 10/01/2020, the customer "pays" the Campaign. You currently have a record in the Campaign table, record in the Payment table with status = 3 (Payment approved) and a record in Payment_card. This record in Payment_card must have some data for you to "check" the payment in Pagseguro EDI, for example: NSU, VALUE, % OF THE FEE, etc. This data depends on your contract with Pagseguro, which is written in Card_contract So in Payment_card you have original value = R $ 150,00, expected rate of 3%, probable value of R $ 4,50, receipt value R $ 145,50 (orginal - rate), and NSU number, and maturity that is 09/02/2020 (10/10/2020 payment day + 30 days - January has 31 days). Remembering that the customer can parcel in 10x... then you will have 10 records in Payment_card, with their respective dates and values, because according to the amount of installments the rates in Pagseguro must change. This should be all set up in Card_contract.

Then you will have a job/check to search every day in EDI Pagseguro the payments received, based on this settle the Payment_card table CHECKING if Pagseguro actually paid you R $ 145,50. Otherwise the status of Payment_card should be inconsistency in conciliation.

In this scenario, you will know the day the customer has the expiration, if it has already been paid by the customer, if it is past due, if Pagseguro paid you, if Pagseguro paid you correctly according to contract, etc. For this just make the relationships between the tables.

Answer 3

To kill the puzzle your Where is missing only one more very simple thing:

in the controller or in the success_payments function itself you can use this way

Model::with('success_payments')->where(Carbon::now()->format('Y-m-d'), '>=',\DB::raw('DATE_ADD(updated_at, INTERVAL 30 DAY)'))->get();

I hope I’ve helped in some way, in case I don’t just use the whereRaw that’s for sure.

Answer 4

Don’t worry Rafael, just do another Where :D.

public function success_payments(){
    return $this->hasMany(Payment::class)->whereStatus('3')->where('updated_at', '>=', date("Y-m-d h:i:s", strtotime("+30 days")))
}

Remembering that 'data' is equal to the name of the column in your bank. The function Date will give you a date in the format you ask for and then with strtotime Voce asks for how much more on the case "+30 days "

I ran the test running this code here:

echo date("Y-m-d h:i:s", strtotime("+30 days"));

on this website: php-online-run

and showed today’s date + 30 days, so I guess that’s what you’re asking for.

2019-12-29 07:51:04