php - Calculating a Running Total Efficiently -

-

I have to find out how to store, retrieve, and calculate the balance running in PHP. It's not related to PHP - it's more programming theory, but anyway:

Imagine the following table: 

  id | User_id | Date | Start_time | And_time | Total. Balance | Notes 19 17 | 2014-04-01 | 09:00:00 | 17:30:00 pm 7.50 | 0 | 20 | 18 | 2014-04-02 | 09:00:00 | 18:30:00. 8 | 0.5 | 20 | 18 | 2014-04-01 | 09:00:00 | 17:00:00 | 7 | -0.5 | It is relatively easy to display the balance for the month: 
 
     
  1.  
     How many hours are available in the month (April 2014). (We work 7.5 hours a day, and we do not work on weekends): 
     
     22 days * 7.5 = 165 hours 
     
    2.  
  2.  
     Total time booked Calculate the use of SQL for one month, for example, the following: 
     
     Select SUM (total) from the date of booking> Where date> = '2014-04-01' and date & Lt; = '2014-04-01'; 
     
     
     
  3.  
     The remaining amount is available for total hours available in total hours: 
     
     30 - 165 = -135 
     
    4.  
 
 I need to display a total walk of these values ​​in a efficient manner in several months, so I did not want to have only the total column because there were thousands of lines Maybe in the future, is it definitely capable? 
 
 So I thought about having a running_total table, with one per line per line, which is deleted every time that whenever the record is edited, it is automatically updated or removed. However, the fact is that last month's records can be updated, so it would be problematic because the current month's row will not need to be updated, for that month there will be a waterfall for months. For example, if you wanted to go back in March to update a record, I would have to update the obligation line to run for March and April. 
 
 Unless you do SUM in all running records, and do the balance of PHP? Again it seems incapable of me as the application grows, I can get it right in the beginning 
 
 Other problems 
 
 I only calculate the balance running to this day I want to, though in the future you are allowed to book the time, it should be ignored while calculating the floating balance. / P> 
 How can I do this now 
 
 I hope it is very fast, but I think it is very complex and has already been bugged in it. So I need to get a simple, efficient solution in Quick. 
 
 Entering a new record 
 
     
  1.  
     The first time you book a day in a new month, I work in that month (Easy April 2014) Expire total hours available in 
     
     165 
     
    2.  
  2.  
     Load running amount (1 per user) line and decrease by total month It currently totals total Gone (7.5 indexing): 
     
     -165 + 0 = 165 = -165 
     
    3.  
  3.  
     7.5 = -157.5 
     
    4.  
 
 displaying the running balance 
 
     
  1.  
     load the line to run Rina Calculate how many hours remaining in the current month. Example we are on 28th April, so there are 15 hours remaining (2 days * 7.5). 
     
    2.  
  2.  
     Add to the running balance: 
     
     -157.5 + 15 = -142.5 
     
    3.  
  3.  
     So your Moving balance is therefore: -142.5 
    4.  
 
 It is proving to be very complicated with the bus, especially in many edge cases; To remove Especially when removing the last entry for a particular month, I am thinking that what is a simple approach, can you think of clever people? 
 
 Thank you. 
 
 PS: The source of the code is here: It is not really in any of the issues before the issues have been dismissed and the conditions used by the public before writing the documents, but it References can help.

A "physical view" can store the collected values ​​that are created by the database, resulting in Read faster at the expense of writing functionality.

However,

  • Li>

    • > For other backgrounds, also see:


    Comments

    Post a Comment

    Popular posts from this blog

    import - Python ImportError: No module named wmi -

    -
    I followed the instructions for downloading WMI for Python When I try to run the code for C.Win32_Service (StartMode = "Auto", state = "Stopped") import wmi c = wmi.WMI (): if Raw_input ("restart% s?"% S.Caption) .upper () == "Y": s.StartService () I got the error < Code> traceback (most recent call end): file ". \", Line 1, edit: I am using Python 2.7.6 EDIT2: I am running 64-bit Windows 2008 R2, and I've downloaded WMI-1.4.9.zip (md5). I removed the content and put it in D: Python \ Tools \ Scripts I executed dragon setup.py.install I said D : \ Python \ tools \ scripts to% PATH% and when I execute the code for C.Win32_Service (StartMode = "Auto", state = "Stopped") import wmi c = Wmi.WMI (): If raw_input ("Restart% s?"% S. Caption). () == "Y": s.StartService () error me Gets traceback (most recent call final): File ". See \", line 1, &
    Read more

    Editing Python Class in Shell and SQLAlchemy -

    -
    I am working on the terminal on a shell script after this tutorial SQLAlchemy tutorial. I need to type gt; & Gt; & Gt; Sqlalchemy import column, integer, string & gt; & Gt; & Gt; Class user (base): __tablename__ = 'users' id = column (integer, primary_key = true) name = column (string) absolute name = column (string) password = column (string) def __repr __ (self): return "& lt ; User (name = '% s', absolute name = '% s', password = '% s') & gt; "% (Self.name, self.fullname, self.password) After the problem I have typed the password = column (string) I hit twice and changed .... >> I then took everything back but then an error was thrown because the class already exists ... I am not completely sure how to fix it. How do I open that slip in the shell script and edit it (Add DF to DRR) The error thrown below: / user / giripeters / TFSQLLX / Lib / python2.7 / site-packages / sqlalchemy / ext / decla
    Read more

    c# - MySQL Parameterized Select Query joining tables issue -

    -
    I am using parameterized select questions in conjunction with my program and it works entirely, When I try to join the table Everything I have done is a small fraction: using (MySqlCommand cmd = new MySqlCommand (paramQuery) ToSql (), Connection)) /etc/ paramQuery.ToSql () Equal: "Select TableOne ID, Tableon, Department, Tablet. One by name table, Table 2 h Yes TableOnEID = @Pharmak "* / for (Int Index = 0; Index and Lt; ParamariX Parameter (.c calculation; Index ++) Cmd.Parameters.AddWithValue (paramQuery.Parameters (). ElementAt (index). , ParamQuery.Parameters (). ElementAt (index) .Value); / * ParamQuery.Parameters (). ElementAt (index) .k = "@most 0" paramQuery.Parameters (). ElementAt (index) .Value = "tableTwo.ID" * / MySqlDataReader Reader = cmd.ExecuteReader (); While (reader.Read ()) {// do stuff} } One of the tables I attempt to join is For the table, two with the same id for all. Is there something that I'm doing wrong?
    Read more