#1
  1. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,455
    Rep Power
    70

    Need help creating a loop


    Well, simply said, the only time a loop will need to occur is when this function runs, which creates a new/current month and is creating new weekly/bi-weekly invoices, which will be from only a single account or two. I have the switch currently setting $loop to 1, but the switch also creates the date. I'm sure this isn't the best way to create such an action, so I'm just looking for some assistance/suggestions. The do-while was my initial thought, since it'll always run each time, then check if it even has to make a loop. But, the fact that the date creation was in the switch, which set the $loop decider, kinda made my thought a bit sloppy on how that was to work...

    Full function before any loop attempt: (SELECT builds array fine. mktime() fails in this scenario.)
    PHP Code:
    function create_new_month() {
      global 
    $link;
      
    $stmt $link->prepare('SELECT ...');
      
    $stmt->execute();
      
    $stmt->bind_result($account$date$date_type$value$adj_value$comment$paid);
      while(
    $stmt->fetch()) {
        
    $last_invoice[] = array($account$date$date_type$value$adj_value$comment$paid);
      }
      
    $stmt->close();
      foreach(
    $last_invoice AS $inv) {
        
    $m date('m'strtotime($inv[1]));
        
    $y date('Y'strtotime($inv[1]));
        
    $d = date('t'mktime(000$m1$y));
        switch(
    $inv[2]) {
          case 
    0$inv[1] = date('Y-m-d H:i:s'strtotime($inv[1] . ' + ' $d ' days')); break; // Same numeric day each month
          
    case 1$inv[1] = date('Y-m-d H:i:s'strtotime($inv[1] . ' + 7 days')); $loop 1; break; // Weekly
          
    case 2$inv[1] = date('Y-m-d H:i:s'strtotime($inv[1] . ' + 14 days')); $loop 1; break; // Bi-Weekly
          
    case 3: break; // Monthly (Avoiding weekends)
          
    case 4: break; // Quarterly (Avoiding weekends)
          
    case 5: break; // Bi-Annually (Avoiding weekends)
          
    case 6: break; // Annually (Avoiding weekends)
        
    }
        
    $stmt $link->prepare('INSERT INTO `invoice` (`account`, `date`, `date_type`, `value`, `adj_value`, `comment`, `paid`) VALUES (?, ?, ?, ?, ?, ?, ?);');
        
    $stmt->bind_param('isiddsi'$inv[0], $inv[1], $inv[2], $inv[3], $inv[4], $inv[5], $inv[6]);
        
    $stmt->execute();
        
    $stmt->close();
      }
      return 
    TRUE;

    Initial loop thought: (After switch(), so new dates fail to be created.)
    PHP Code:
    do {
      if(
    date('m'$inv[1]) == date('m')) {
        
    $stmt $link->prepare('INSERT INTO `invoice` (`account`, `date`, `date_type`, `value`, `adj_value`, `comment`, `paid`) VALUES (?, ?, ?, ?, ?, ?, ?);');
        
    $stmt->bind_param('isiddsi'$inv[0], $inv[1], $inv[2], $inv[3], $inv[4], $inv[5], $inv[6]);
        
    $stmt->execute();
        
    $stmt->close();
      } else {
        
    $loop 0;
      }
    } while(
    $loop == 1); 
    Note: The usage of date() is more to create the new dates that will be INSERTed. The issue I am trying to figure out with that is for the instances where the month-related items that are to land a month later on a date that doesn't exist. i.e. Due the 31st this month, but next month only has 30 days...
    Last edited by Triple_Nothing; July 6th, 2017 at 11:35 AM.
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  2. #2
  3. Forgotten Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,180
    Rep Power
    9644
    So it sounds like what you're doing is running some code to generate the next month's invoices for all accounts you have? Some are a specific day of the month (variable interval of 28-31 days) and the rest are periodic (fixed interval).

    Side note #1: are you sure you want to persist the "comment" and "paid" values for the next month? A comment maybe, but "paid" sounds like a flag that should surely be unset for new invoices.

    Logic:

    I don't see any nice way to share logic. Yes, there's a fair bit in common that could be shared, but it starts looking pretty bad with the different customizations. There's not too much work for each so I would just opt for maintainability over efficiency.

    Same day each month:
    - You have a business decision to make about what to do with the day overflow problem. The normal solutions are to round up (first day of the month after) or down (last day of the month).
    - Determining whether there is a problem is easy and there are many ways to do it. I think this is the simplest one:
    PHP Code:
    $invdate date_parse($inv[1]);
    $overflow = ($invdate["day"] >= date("t"mktime(000$invdate["month"] + 11$invdate["year"]))); 
    then
    PHP Code:
    $expected date("Y-m-d H:i:s"mktime($invdate["hour"], $invdate["minute"], $invdate["second"], $invdate["month"] + 1$invdate["day"], $invdate["year"]));
    $rounded_up date("Y-m-d H:i:s"mktime($invdate["hour"], $invdate["minute"], $invdate["second"], $invdate["month"] + 21$invdate["year"]));
    $rounded_down date("Y-m-d H:i:s"mktime($invdate["hour"], $invdate["minute"], $invdate["second"], $invdate["month"] + 20$invdate["year"])); // day=0 will underflow 
    Weekly/Bi-weekly:
    - Easy, but you have 4-5 (weekly) or 2-3 (bi-weekly) dates you need to generate.

    Monthly/Quarterly/Bi-annually/Annually:
    - I'm not actually sure how you want these to work... How is "monthly" is different from "same day each month"?
    - Quarterly and longer intervals might not have invoices to generate for the next month!


    Stick the date(s) created into an array, even for the variations that only do one date (if any at all), and foreach over it after the switch.

    Side note #2: prepared statements work best when you prepare it once, bind once, execute every time you alter the bound variables, and close once when you're done. At the very least your code should only prepare and close once; you can bind_param multiple times with the values in the foreach.

    Implementation:

    OOP sounds good here, but with only one method ("generate invoice dates") it creates a lot of classes with not much code. So instead I would opt for functions (or methods) and generators.

    PHP Code:
    function invoice_generator_samedayeachmonth($previous_date) {
        
    // whatever...
        // ultimately you create the next date
        
    yield $date;
    }

    function 
    invoice_generator_weekly($previous_date) {
        
    // magically you come up with each date
        
    yield $date_one;
        
    // ...
        
    yield $date_two;
        
    // ...
        
    yield $date_three;
        
    // ...
        
    yield $date_four;
        
    // ...
        
    if (/* $date_five is also next month */) {
            
    yield $date_five;
        }
    }

    // etc. 
    Not yielding any values is fine too.
    PHP Code:
    function invoice_generator_annually($previous_date) {
        
    // come up with date
        
    if (/* $date is next month */) {
            
    yield $date;
        }

    The return value from each function is technically an object, but you can foreach over it just fine.
    PHP Code:
    switch($inv[2]) {
        case 
    0$next_invs invoice_generator_samedayeachmonth($inv[1]); break;
        case 
    1$next_invs invoice_generator_weekly($inv[1]); break;
        
    // ...

    PHP Code:
    // (prepare)
    $next_date null;
    // (bind $inv[0], $next_date, $inv[2] through $inv[6])
    foreach ($next_invs as $next_date) {
        
    // earlier bind still has the same values from $inv and the foreach updated $next_date, so all you have to do is...
        // (execute)
    }
    // (close) 
    The list of things my brain was tracking to say is now empty.
    Last edited by requinix; July 7th, 2017 at 07:44 AM.
  4. #3
  5. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,455
    Rep Power
    70
    Thanks for the assist. I think my loop was aiming to run the entire Prepared each time, but thanks for the reminder only the updated part truly needs to run in such a loop. The independent functions/generators actually sounds a perfect option, and the decider of which is called would then simple be due to the due-date 'type'...

    The "same day" versus "monthly"... The "same day" was more intended to be bound a bit more strictly to, lets say, the 16th. The "monthly" may be initially bound to the 16th, but with the added functionality of avoiding holidays and weekends.

    I'm thinking it may be best to expand my `accounts` table to include the initial due dates, since the previous month's may be edited to avoid weekends, so perhaps invalid for future use due to such... And I should probably hold the type (weekly/monthly/...) there as well, so the script has a better way to understand those in which may not exist on the prior month's build.
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  6. #4
  7. Forgotten Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,180
    Rep Power
    9644
    Originally Posted by Triple_Nothing
    The "same day" versus "monthly"... The "same day" was more intended to be bound a bit more strictly to, lets say, the 16th. The "monthly" may be initially bound to the 16th, but with the added functionality of avoiding holidays and weekends.
    That makes sense. So the two would have the same code, really - in fact the "monthly" code can call the "same day" code and then add a little more processing on top. But then that presents a problem: if you're basing the next invoice on the date of the previous, and the "monthly" scheme can move the date around to avoid holidays, then you've got a situation where the invoice day can slide too far from the original date. And that would apply to any other scheme where you tweak the date too.
    One way to solve that would be to store separate expected and actual invoice dates, then base calculations on the expected date. Though I think normally the solution to scheduled tasks is to store the task data and use it with a factory, like
    PHP Code:
    $schedule = [
        
    // all schedules have this value
        
    "date_type" => 3,
        
    // the rest varies by the date_type
        
    "day" => 16
    ];
    $generators = [
        
    => "invoice_generator_samedayeachmonth",
        
    => "invoice_generator_weekly",
        
    // ...
    ]; 
    PHP Code:
    function invoice_generator_monthly($period, array $schedule) {
        
    $day $schedule["day"];
        
    // calculate next invoice date in $period according to $day...
        
    yield $date;

    PHP Code:
    $next_invs $generators[$schedule["date_type"]](/* next period */$schedule); 
    Which raises the question of what period you're generating invoices for - something I managed to avoid in my previous post. Rather than say "next month" you should explicitly figure out what period the generated invoices can span (even if you just use the "next month" relative to the current time) and pass that to the generators. That makes them more reusable, too.

    Originally Posted by Triple_Nothing
    I'm thinking it may be best to expand my `accounts` table to include the initial due dates, since the previous month's may be edited to avoid weekends, so perhaps invalid for future use due to such... And I should probably hold the type (weekly/monthly/...) there as well, so the script has a better way to understand those in which may not exist on the prior month's build.
    Um... right... I didn't read this part of your reply until just now... so... yeah.
  8. #5
  9. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,455
    Rep Power
    70
    As far as the "next month" action, the true process is to initiate as the "next month" becomes the "current month" when viewed by the first user and is yet to exist in the database. You will never see a "future" month. Sorry if a poor definition/reference of my months.

    I'm thinking the functions for the 3, 6, & 12-month items are perhaps best called during the new month's creation, and I'm thinking the best way to find if that item should exist would probably run a SELECT based on a pre-set month due to it's timeframe "type", then just use an if/else to check if any result is returned. If I'm correct, the only time anything should be returned in such a query would be if that item is at it's full timeframe., and a new one should be set/defined. (As long as I can reference my past 3 months via the true months, and not 90 days...)
    Last edited by Triple_Nothing; July 10th, 2017 at 11:42 AM.
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  10. #6
  11. Forgotten Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,180
    Rep Power
    9644
    Sounds tricky. If you have them adjusting for holidays then 3 months/90 days isn't right: you'd need 90 days +/- however much time you are willing to add to avoid a holiday. But if you find an invoice at, say, -91 days, you need to know whether that invoice was actually 89 days minus 2 for a holiday (don't generate next invoice) or it was really supposed to be 91 days ago (do generate next invoice).
    It's back to the problem of needing to know when an invoice is supposed to be scheduled vs. when it actually was scheduled.

    Now I'm wondering if you really have to move dates for holidays. What's the problem with creating an invoice for December 25th? It's not like you expect the recipient to pay that day, right? They should have a window of time to pay it, be that before or after, and any reasonable window will be more than large enough to account for even the longest holiday weekend.
  12. #7
  13. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,455
    Rep Power
    70
    Well, and I think this may add a bit to the tricky-ness. The date aiming to be defined is the due date of the third party. I'm kinda wondering if the same date solid, or adjust via weekends/holidays will even be a steady action. I figured maybe I'd start this way, then manually adjust accordingly until I got all the details really added in over time, since each company may have their own adjustments. Maybe the 2 monthly options I'll initially have won't be enough, but figured it just be a place to start :/
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  14. #8
  15. Forgotten Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,180
    Rep Power
    9644
    Okay, then you at least know you shouldn't push the invoice date into the future, but the moving date thing arises from pushing into the past so that's still a problem.
    Well, actually, that depends on the third party. They may say that a weekend due date is actually the next business day... Which brings up the "how do you move the date around" issue - hypothetically some issuer may want it moved forward or backward only.

    But my question still stands: how about not moving the dates around and just making sure the recipients have enough time to see and pay the invoice? If you're generating stuff one month in advance then that gives at least one month's notice, right? Should be enough. Holidays and weekends only really affect banks and in-person payments nowadays (is that a factor here?) - even if online transactions aren't actually processed until a few days later, they are still credited to the date they were made.
    Last edited by requinix; July 12th, 2017 at 02:59 AM.
  16. #9
  17. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,455
    Rep Power
    70
    I was thinking, as a start, to play the "better safe than sorry" as you kinda mentioned. Like, always move the due dates prior, to start. I know invoices and all will have the ability to be altered here, but the clients will want to do as little of such as possible, hence the request for the automated version of this.

    They don't need/want the final project until the beginning of next year, so I've time for testing and to work out with my own/friend's bills. I'm thinking, for now, just go with the prior date, if adjust, just slide in a note, and make the minor adjustments/abilities over that time. Defining a due date prior to the true one is always safe, but they want this to match their paperwork as best as possible, with as little adjustments of their own as possible.

    But, now that I've kinda got the initial defining of the weekly/bi-weekly, and a better location of the initial due date with the account's table to help with the non-monthly invoices, I suppose I can let this part run a month or two n see how it turns out. Thanks for the help.
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  18. #10
  19. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,455
    Rep Power
    70
    Hmmm... While creating 2 new columns on my 'accounts' table ('type' & 'day'), I kinda thought maybe day should be an integer value of that type. Now, for a weekly/bi-weekly, that would be 1-7 or 1-14. Can anyone think of a suggestion for if/how such an idea may safely be played over a single or multiple month range? The weekly is easy since weeks are always 7 long. With the variance in monthly length, I'm thinking this may not be an option, but figured I'd toss the question out there to see if anyone could hopefully prove me wrong.

    Hmmm... Or, just an instant thought, maybe just have PHP fetch the count of days from presented months, and add those to help define the range...

    Edit: But that may not work, 'day' is intended to be a non-altering number used to figure out the new dates... :/

    Edit2: Actually, monthly could always be that day of month, adjusted for holidays/weekends/... Now for the quarterly/bi-annual/annual, I could maybe do a SELECT for an invoice BETWEEN 2 dates. If I'm correct, the only time it should INSERT a new one would be if zero rows returned. If a row was returned, that would mean it is still within its period...
    Does anyone know or have a company that actually uses the 29th-31st as due days? (Other than weekly/bi-weekly, and "last day of month") I wonder if companies themselves avoid this...
    Last edited by Triple_Nothing; July 15th, 2017 at 08:14 AM.
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.

IMN logo majestic logo threadwatch logo seochat tools logo