multiple select statements

[phppup]

It's been a fun week of learning while battling coding problems.  So now, I need advice for the challenge..

The objective:  SELECT *FROM table WHERE moneyisdue != 0 AND x30daynotice = ' ' ;  then, send email saying "PAY ME NOW" and

UPDATE table SET x30daynotice = 'YES' WHERE moneyisdue != 0 AND x30daynotice = ' ' ;   then

SELECT *FROM table WHERE x30daynotice = 'YES ' AND x10daynotice = ' ' ;   then, send email with LEGAL THREAT and

UPDATE table SET x10daynotice = 'YES' WHERE moneyisdue != 0 AND x10daynotice = ' ' ;

My initial desire was to create a single script and run it daily to remain current.

Naturally, this is where I ran into trouble.

I've devised a simple solution of running 2 separate files: one for 30 day and one for 10 day.

But the question remains, from a PHP perspective, is a SINGLE file more efficient? 

And if so, can you provide an outline that I can use for guidance?

 

Note: I am ONLY using "YES" in the table for testing purposes and immediacy.  In actuality, a timestamp or date mechanism will be implemented. 

Right now I am just interested in having the 10 day notice follow the 30 day notice effectively.

Edited June 16, 2018 by phppup

[Barand]

Now we have seen your proposed solution, what is the problem you are trying to solve?

[NotionCommotion]

44 minutes ago, phppup said:

But the question remains, from a PHP perspective, is a SINGLE file more efficient? 

Probably, but so minor it doesn't matter.  More important is reducing duplicated script.  Problem when you do is you change one but forget to change the other.  You can create a single function and then have both call that function.

[taquitosensei]

Agree with NotionCommotion create a function that you can pass 10 or 30 to then run the  appropriate sql. 

[phppup]

My problem is in establishing SQL1 and SQL2.

I've seen different examples with conflicting information.

Can I name them SQL1 and SQL2?  Or return_1 and return_2?  This effort seems to fail in all efforts.

Using just SQL twice seems to only run the FIRST of the SQL statements.

And, yes, my email is in two separate functions depending on whether the 30_day or 10_day is in effect per SQL statement.

So, as it stands now, SQL statement equals 30_day function criteria, and all is well.

If I were live I'd now have 18 days to work out the 10_day trigger (or simply duplicate the 30_day script with 10_day criteria which, at the very least, adds a duplicate file to my storage).

How do I include the second SQL without effecting the first?

 

[NotionCommotion]

I really don't understand what you are trying to do.  Are you invoking these scripts using a CRON?  Back to Barand's earlier post, start over and explain what you are trying to do.

[Barand]

1 hour ago, phppup said:

My problem is in establishing SQL1 and SQL2.

No that isn't your problem - that is how you are trying to solve your problem. We need to know the business problem. What are the 30 and 10 days relative to?

(Unless those 30 and 10 days are relative to the same date for everyone, then tomorrow you need another two files, and the day after another two, and so on ad absurdum.)

[Barand]

I don't know if this is close to your situation. Consider this scenarion.

We have a set of customer accounts

account table

+------------+-----------+-------------+
| account_id | name      | email       |
+------------+-----------+-------------+
|          1 | Company A | a@gmail.com |
|          2 | Company B | b@gmail.com |
|          3 | Company C | c@gmail.com |
+------------+-----------+-------------+

 

When they buy from us we raise invoices and store the dates and amounts

payment_due table

+----------------+------------+------------+------------+--------+
| payment_due_id | account_id | invoice_id | due_date   | amount |
+----------------+------------+------------+------------+--------+
|              1 |          1 |        101 | 2018-04-15 | 100.00 |
|              5 |          2 |        102 | 2018-04-15 | 200.00 |
|              2 |          1 |        103 | 2018-05-15 | 100.00 |
|              4 |          3 |        104 | 2018-06-01 | 300.00 |
|              3 |          1 |        105 | 2018-06-15 | 100.00 |
+----------------+------------+------------+------------+--------+

 

The customers make payments (hopefully) against these invoices

payments_recd table

+-----------------+------------+------------+------------+---------+
| payment_recd_id | account_id | invoice_id | recd_date  | payment |
+-----------------+------------+------------+------------+---------+
|               1 |          1 |        101 | 2018-04-15 |  100.00 |
|               5 |          2 |        102 | 2018-06-15 |  150.00 |
|               4 |          3 |        104 | 2018-06-10 |  300.00 |
+-----------------+------------+------------+------------+---------+

 

After 30 days we want to sent a reminder that any unpaid moneys are due. After 60 days we send the "red frightener" letters. So a single query like this will show the overdue payments.

        SELECT a.name
             , a.email
             , d.due_date
             , d.invoice_id
             , due
             , IFNULL(paid, 0) as paid
             , due - IFNULL(paid, 0) as owed
             , DATEDIFF(CURDATE(), d.due_date) as days_overdue
        FROM
            account a
            INNER JOIN
                (
                    SELECT account_id
                         , invoice_id
                         , due_date
                         , SUM(amount) as due
                    FROM  payment_due
                    GROUP BY account_id, invoice_id
                ) d USING (account_id)
            LEFT JOIN
                (
                    SELECT account_id
                         , invoice_id
                         , SUM(payment) as paid
                    FROM  payment_recd
                    GROUP BY account_id, invoice_id
                ) p
                    ON d.invoice_id = p.invoice_id
         WHERE
                due_date <= CURDATE() - INTERVAL 30 DAY
                AND due > IFNULL(paid,0)

        
 Query results
 

+-----------+-------------+------------+------------+--------+--------+--------+--------------+
| name      | email       | due_date   | invoice_id | due    | paid   | owed   | days_overdue |
+-----------+-------------+------------+------------+--------+--------+--------+--------------+
| Company A | a@gmail.com | 2018-05-15 |        103 | 100.00 |   0.00 | 100.00 |           33 |
| Company B | b@gmail.com | 2018-04-15 |        102 | 200.00 | 150.00 |  50.00 |           63 |
+-----------+-------------+------------+------------+--------+--------+--------+--------------+

(You would also need to record when the two types of email were sent and use this information to prevent multiple copies being sent to customers.)

[phppup]

That's the idea of what I want to do, but not quite as complicated. At least not yet.

At this point I wanted the script to run through the table and send and email to those 30-day customers and a different email to the 10-day customers.  

My approach was to SELECT those that were at the 30-day mark and use a second SELECT  to grab the 10-day folks.  And yes, I was coordinating it so as not to 'step on my own feet' in the process.  As two separate scripts, the goal is achieved.  But when I tried to combine the two I ran into conflicts.

I had thought I could somehow control them by referencing them as variable ten_day and thirty_day (or something in that vain) but it was not as simple as I had hoped.