#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    169
    Rep Power
    37

    Delphi times calculations


    i have a datetime field. Lets say 12/06/2012 13:00. I want to add 23 hours. BUT i want to add 15 working hours.

    so from 13:00 i want to add 4 hours till 5 o clock. then next morning at 8 i start again.till 17:00 (8 working hours per day). So at the end i want 14/06/2012 10:00. I also like to exclude weekends?

    Is there an easy way of doing this easily?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,055
    Rep Power
    383
    I want to add 23 hours. BUT i want to add 15 working hours.
    This is not very clear (at least, not to me).

    Do you want to add 23 hours to the time field?
    Do you want to add 15 hours to the time field?
    Do you want to add 4 hours at 5 o'clock and 8 more at 17:00 the next day
    which is neither 23 nor 15?
    Also 12/06/2012 13:00 t 14/06/2012 10:00 is (I think) 45 hours. Neither 23 nor 15.

    Am I missing something obvious?

    Clive
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    169
    Rep Power
    37

    Re


    Hallo Clive Sorry

    I want to add 15 hours...but it must be working hours.and working hours is form 8:00 till 13:00 and 14:00 to 17:00. So its 8 hours.

    Sorry about the confusion. Would it be possible to do something like this?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    169
    Rep Power
    37

    re


    Just to clarify

    12/06/2012 13:00 + 15 hours

    i must get

    14/06/2012 10:00
    ///////////////
    12/06/2012 3 hours left
    13/06/2012 8 hours (fullday)
    14/06/2012 4 hours

    gives me 15 hours.

    I am not sure how to approach this. If you might point me in a direction i will start the code and work from there?

    thanks again clive
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,055
    Rep Power
    383
    OK.
    First let me see if I now understand the issue. If I do, I will have a couple of follow ups.
    My understanding is:
    For any given starting timestamp you want to be able to add a number of working hours and calculate where that would take you to in real time.

    To use your example:
    If someone gave you an order for a widget at 12/06/2012 13:00 that took 15 hours to produce you want to know that you can not promise delivery before 14/06/2012 10:00.

    If the above is correct, we can go on to explore the solution.

    Clive
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    169
    Rep Power
    37

    Re


    Hallo Clive

    You guess right. Its actually cabinets. They make cabinets. So the client give us the order at 12/06/2012 13:00. The cabinet goes through lets say 3 stages.

    a cut (takes 12 working hours)
    assembly (4 hours)
    finishing (3 hours)

    So on any given day i want to see how far the cabinet is. If i check the 14/06/2012 10:00 and see whether i started with assembly yet. If not i am late.(in this case i am).

    As soon as i have an order i want to add 3 dates away with their corresponding FINISHING DATES. Then i can run reporting from there.

    Thanks so much for your help so far!!
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,055
    Rep Power
    383
    Ha! I owned and ran a furniture mfr company for many years, so I understand.
    Definitely doable.
    Let me think about approaches.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,055
    Rep Power
    383
    Been Thinking
    ----------------
    I don't know how detailed you wish to go (e.g. Overtime options etc. etc.).

    If I were approaching this my first thoughts would (as always) be how do I build
    a logical database to underpin the project.

    Requirement one is a table holding each days working hours
    (whether this needs to be materialized) or can be built on the fly from other sources is your issue.
    WORK_HOURS
    BEGIN_TIME TIMESTAMP,
    END_TIME TIMESTAMP

    That is as simple as I can make it and allows for as many breaks as you wish
    (lunch, other, miss weekends and holidays)
    If you have different types of hours (regular, overtime, double overtime) you need an
    extra column for PERIOD_TYPE.

    Now you need some related tables.
    PRODUCT
    PROD_ID INTEGER,
    DESCRIPTION VARCHAR2 (or whatever your DB uses for text)

    PRODUCT_STAGES
    STAGE_ID INTEGER,
    STAGE_NUM INTEGER,
    REQ_HOURS INTEGER, (Or number if part hours are needed or keep integer but use minutes.)
    STAGE_DESCRIPTION VARCHAR2,
    PROD_ID INTEGER ( FK to PRODUCT table.)

    Now you need your transaction tables
    ORDERS table
    LINE_ITEMS table (with FK to ORDERS)

    PROGRESS (Adjust this to your needs - just an example)
    PROGRESS_ID INTEGER,
    LINE_ITEM_ID INTEGER, (FK to LINE_ITEMS)
    STAGE_ID INTEGER, (FK to PRODUCT_STAGES)
    STARTED TIMESTAMP

    =====================
    For the basics, that might be all you need.
    LINE_ITEM_ID, STAGE_ID and STARTED give you all you need to query everything else.

    From LINE_ITEM_ID, STAGE_ID you can find out how many work hours remain.
    From STARTED you can look up where you are in the WORK_HOURS table.
    From there you just do the math against the WORK_HOURS table.

    Depending upon your database, you might, for math convenience, want to add a calculated column to WORK_HOURS
    or a VIEW (but not a real column)

    e.g.
    WORK_HOURS
    BEGIN_TIME TIMESTAMP,
    END_TIME TIMESTAMP,
    TIME_AVAILABLE (END_TIME - BEGIN_TIME)

    Perhaps the above has some errors or issues you need to address.
    I just hope it gives you some sort of starting ideas.

    Clive
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    169
    Rep Power
    37

    re


    hallo clive.

    I have a product table because each product takes different hours to create. i have the following

    productcode
    productname
    cuttime : integer (number type)
    assembly : integer (number type)
    finishing : integer (number type)

    So i dont have a product stages as you put it in your reply. I put them in the same table.

    Then the ORDERS for the client. Luckily i dont need to worry about order lines. (each item on the order) the whole order (job) is assigned to a productcode. We have something like this :

    Ordernumber
    Client
    Productcode
    Cutdatetime (datetime)
    Cutflag (string 'Y' or 'N')
    assemblydatetime (datetime)
    assemblyflag (string 'Y' or 'N')
    finishingdatetime (datatime)
    finishingflag (string 'Y' or 'N')

    When they are finishing with a process they scan a barcode in and the corresponding FLAG (Y or N) get inserted. Lets say the cut is dont then the Cutflag = 'Y'. Which means that process is done.

    So now i only check each process that is not done ('N') against the date and time of today against those dates.

    for example

    if assemblydatetime < todaysdate then
    //do whatever reporting messages etc.

    So my only problem is to calculate the times.

    I dont need to worry about public holidays and so on.They said thats their problem. I just work with a normal days hours from 8:00 till 17:00. which has n lunch break of 12:00 to 13:00. So in otherwords a perfect world where there is no holidays and no machine will ever break.

    So when i add the order i check todays date, check what is the product i am going to make check the product file and calculate each process on the order and store it. Then the rest will work.

    Am i on the right path?
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,055
    Rep Power
    383
    A comment first:
    productcode
    productname
    cuttime : integer (number type)
    assembly : integer (number type)
    finishing : integer (number type)
    This design (IMO) violates first normal form and should not be part of your design.

    In terms of your situation it absolutely locks you into a three stage situation for all products all of the time.
    My suggestion, on the other hand, allows every product to have as many, or as few, stages as may be required.
    You will never have to shoehorn multiple stages into one just to satisfy your software design.
    e.g.
    Suppose at some stage you offer a cabinet with an upholstered seat on top.
    Now you have a fourth stage.

    I know your client may say this will never happen; but that is not my typical experience

    As far as calculations are concerned, I am not sure how dynamic your times will be when delays occur.

    The basic theory, as I would approach it is as follows.
    I use the simplest of situations.
    You are entering a new record and want to know when stage one will end.
    Say stage one takes 15 hours.
    Logic - pseudo code:
    Code:
    var timeLeft := 15;
         thisWorkPeriod := 'however you decide to create or lookup'.
    
    1. Are we in a work period?
      a. Yes. 
          timeLeft := timeLeft - (ThisWorkPeriod.End - startTime).
          {If initial timeLeft is less than remaining time in first work
            period you will need to handle that}
    
    2. while timeLeft > 0 do begin
          thisWorkPeriod := getNextWorkPeriod;
          if timeLeft > thisWorkPeriod.Length then begin
             timeLeft := timeLeft - thisWorkPeriod.Length;
          else begin
             result {what you want} := ThisWorkPeriod.Start + timeLeft;
             timeLeft := 0;
          end;
        end; {while}
    Obviously I have not accounted for all situations; but I hope this gives you a good start.
    Naturally, I have not checked this in any way, so there may be logic or calculation errors.
    The point is that it is an approach to the problem that might help.

    Clive

IMN logo majestic logo threadwatch logo seochat tools logo