Accounting Database Relation Problem.?!?

Relational Database Problems

[Sherif]

Hey Guys,


First of all I would like to thank all of those people here for all of the tremendous efforts that you spend in helping us here around.

I am currently facing 2 problems,

1) When i declare a variable and set a value for it using the

$variable="value";

Concept, the problem that I am facing is that when I go to another page, the value isn't stored on the server...

I was wondering if there is anything special that i should do to keep the values stored for later reference other than using session variables?


2) I am working on an accounting system for a company.. the problem that i am facing is that for example they are selling expensive products, and therefore they divide the total price of the product for multiple installments.

After that, the employees should enter the invoices and values of the installments with the corresponding date so that the prices and invoices cancel out.

The problem is that sometimes a customer can come, and pay the installments on two parts on different days... how can i set the database structure so that i can compare the multiple inputs with the primary actual input so that i can calculate the delays?


Thank you so much for your support, and i hope to hear a responses and solutions soon.



Sincerely,
Sherif

[Demaestro]

I was wondering if there is anything special that i should do to keep the values stored for later reference other than using session variables?

You can set that value to a cookie and use that, or you can put the variable in a query string and on every page view write the query string to the URL so it is persistent throughout a users page views.

The problem is that sometimes a customer can come, and pay the installments on two parts on different days... how can i set the database structure so that i can compare the multiple inputs with the primary actual input so that i can calculate the delays?

This is tough to answer without seeing the structure that you have set up.

If I understand you correctly your best bet would be to create a table called payments, or even more accurate would be to call it invoice_has_payment.

It would look something like this.

invoice
invoice_id | invoice_total

invoice_has_payment
payment_id | invoice_id | payment_amount | payment_type | payment_date

Then you can set up a "view" that gets values like money_owed.

create view "select (i.payment_amount - (sum(ihp.payment_amount))) as total_owing from invoice i left join invoice_has_payment ihp on i.invoice_id = ihp.invoice_id ......... and so on.

[da_pinky]

when I go to another page, the value isn't stored on the server...

yeah, thats the default behavior of all web scripting languages (asp, php, jsp, ...). all your variables are "per user and per page". as soon as the user navigates to another page, this information is lost.
if you want to pass a value to the next page then you append it to the url ( addtobasket.php?item_id=123456 ). but if you want to keep some information alive during the visitors stay (username, users permissions, # of new mails for this user etc.) you should use $_SESSION. if you cache this sort of information in your session after the user logs in your also avoid a lot of database lookups on each page request.

DO NOT use cookies for this sort of stuff, because all the information is stored on the users computer and very easy to manipulate. And attaching each parameter to each link on your page is simply impractical.

[Sherif]

Hey Guys, thank you for all of the support up until now,

da_pinky,
thanks for clarifying and further explaining the concept of variables and why they are "terminated" through page navigation, I guess that i will use the $_SESSION more often to store the permanent values, and use the url parameters to transfer temporary variables that are needed in the different pages.


Demaestro,
I kind of get your concept, but i was wondering, as far as i understand, the VIEW Concept is all about generating a virtual table from multiple tables, and i am not sure how that is related to what i want.

But my actual problem is that if i have an invoice, and the customer divides that payment of the invoice on 2 times, and pays it on 2 different days. How can i associate the data (2 payments) with the same invoice date (1 invoice), so that i can calculate the number of days delay for corporate action?


The type of output that i would like to have is something like

Invoice no|Invoice Date |Invoice Value |Payment Value|Payment Date |
1 | 2010/05/13 | 5000$ | 2500$ | 2010/05/13 |
1 | 2010/05/13 | 5000$ | 2500$ | 2010/06/01 |
2 | 2010/06/13 | 5000$ | - | - |
3 | 2010/07/13 | 5000$ | - | - |
4 | 2010/08/13 | 5000$ | - | - |

I then want to add a column to calculate the days delay related to the invoice date, and the actual payment date.

The problem that i am facing is that the invoice could sometimes be divided in more than 2 payments, so what structure do you recommend that i use to output this type of data?

It must be noted, that the invoices are related to the product item, and the invoice values, or the number of invoices could change if the customer decides to change his/her product. Also, the number of invoices is not constant for every product, and finally, the number of payments for each invoice is not necessarily constant, since it depends on the customer himself.

I am not a professional programmer [moderate experience], so please try to be clear and simple so that i can try to grasp the concept, and then be able to further research in that field so that i can be able to perform the query correctly.

Again, Thanks for all of the support up until now.


Sincerely,
Sherif

[Demaestro]

Sherif,

I am not sure I understand the issue.

My opinion is that separating the invoice table and the payment table is the best route here.

All you need to do is record all the info, if you need to calculate the days delay then create a method that reads from the DB and does the caculations.

You don't need to perform this calculation using the DB and an SQL statement. Create a method that gets all the info from the DB and does your calculations.

A good general rule for DB is to record everything unless it can be calculated from other things you recorded. If you can calculate it then create methods to do those calculations, don't store values that need to be calculated.

So rather than add a column to calculate the days delay, just get retrieve all the info you would need to make this calculation from the DB and pass it to a method you create that will preform the calculation and return the value needed.

A simple example of this would be... say you have a membership system, people's membership can expire. But at anytime they can add money to their account and it will extend their expire date. It would be incorrect to store a date field on their account that was called something like "date_membership_expires"

The reason is that it is calculated, the expiry date can be calculated by getting all the payment history, the current date and the workflow rules about when a membership expires.

So rather then saying:
"select date_membership_expires from accounts where mem_id = $mem_id"

You would create a method called:
getMemberExipryDate(mem_id)
.."select payments from accounts_has_payments where mem_id = $mem_id"
..use data to do calcs
..return '2010/07/01'


Hope this makes sense. Post back with more questions if you have them

[rocknbil]

DO NOT use cookies for this sort of stuff, because all the information is stored on the users computer and very easy to manipulate.

Unless you're keeping sessions alive with the session ID in a query string, sessions maintain state between server and browser via the PHPSESSID cookie. The advantage with a named and set cookie is you can establish a persistent expire time independent of PHP session persistence. But the point is moot, for this kind of tracking, DeMaestro's method is probably best, store it in the database.

If you haven't got the PHP or database experience to implement this, you'll need someone who can.

[Sherif]

I think i was able to solve the problem using the Full JOIN Command in MySQL, but i am currently facing a small problem in the system that i am working on.

Now that i have the table with all of the information I need, i need to perform date calculations, but for some reason i am very confused.

For starters, as you have guessed, in MySQL, all of the dates are placed in a field of the type "DATE"

Now i have

Invoice_Date [YYYY-MM-DD] format,

and i also have a

Payment_Date [YYYY_MM_DD] format.

how can i calculate the number of days of delay that occurred? it should be something like

delay = payment_date - invoice_date

and i want to output the duration in different formats, ex

you are late 1 month and 26 days
with a total delay of 56 days which is equal to 1.8 Months

Your help will be much appreciated,

Thanks for your support,


Sincerely,
Sherif Malek

[Demaestro]

Hey Sherif,

What you most likely want is the DATEDIFF function.


Check out all the built in date functions that are available. There are probibly some in there you can use that will help you in other places as well.


[dev.mysql.com...]

Good luck and keep posting questions as you have them

[Sherif]

Hey Demaestro...

Thanks for all of the support up until now.
I am aware of the

DATEDIFF

function, but i have many dates stored on my database, and based on these dates, i need to do many different calculations (ex. adding, and subtracting for different cases). If i used this method, then i would be overloading the server through the many sql queries, so i would have preferred to store the dates on different variables, and then use these variables to do any type of calculations that i wanted.

I read many tutorials talking about the

mktime

function, and the

strotime

but i do not seem to understand and grasp the concept to efficiently utilize it to do all of the calculations to relief the server from many of the MySQL queries.

Thanks for all of the support up until now.


Sincerely,
Sherif

[rocknbil]

I don't know why you think you'd need many queries. You would put the date diff. evaluation in a single select. Example,

select date_diff(Payment_Date,Invoice_Date) Invoice_Date,Payment_Date from table;

would give you three values for each row, the nunber of days that have passed between the two, and the two dates, example

10|2010-01-10|2010-01-11

of course you can add a where at the end for restricted results.

[Sherif]

Thanks Rocknbil,

but the problem with this is that there are many calculations that are involved, example,

When i get the difference between the dates, i should subtract from it and check to see if it is within the acceptable delay.

example an invoice should be paid today, but there is an allowance of 30 days then the delay should be placed into account, and penalties are placed.

There are also some conditions that should be applied ex, if the number of days after the difference calculated - acceptable delay is less than 0, then no penalties should be applied. else..... the delay would be .....

The datediff function returns the value in days, what if i want to display the results in months instead of days... ex 55 days, should be ex 1.83 months, or even years [0.15 years]
Having the output method set depending on the acceptable limits, ex. if delay>50 days, then display in months, if more than 7 months, then display in years.


Thank you for your time, and support.


Sincerely,
Sherif Malek

[da_pinky]

if your calculations for this scenario are more complicated than just a few additions and can't be implemented in a simple query, you should either write a separate function in php, or if you have to do a lot of db lookups for that as a stored procedure in sql.

i for myself find it more practical to do more complicated date and time calculations always in seconds (php: mktime(); mysql: UNIX_TIMESTAMP()), and only transform it back to a human-readable result for the final output (php: date()).
its a bit counter-intuitive at first, but you learn to love it as soon as you have to work with different time units, offsets or timezones in your calculations.

[Sherif]

Hey da_pinky,

To be honest, I also prefer this method compared to doing calculations from the MySQL Query itself.

In my database, i have the date values stored in the fields that are structured as DATE type having the format of [YYYY-MM-DD]

Can you please give me an example on how i can perform these calculations from the PHP side, so that i can further research and develop from this starting point to the more complicated calculations?

Example:

1- Finding the Difference between 2 dates.
2- Adding a specific period (ex 1 month) to a stored date to calculate the expiry date.
and so on...


Thank you for your support up until now.


Sincerely,
Sherif Malek

[Readie]

format of [YYYY-MM-DD]

1- Finding the Difference between 2 dates.

$date1 = '2010-12-03';
$date2 = '2010-11-04';

$pieces = explode("-", $date1);
$date1 = mktime(0, 0, 0, $pieces[1], $pieces[0], $pieces[2]);

$pieces = explode("-", $date2);
$date2 = mktime(0, 0, 0, $pieces[1], $pieces[0], $pieces[2]);

$difference_in_seconds = abs($date1 - $date2);

Should do the trick there.

[Sherif]

Thanks Readie for your quick response...


I have a couple of questions....

in the example that you have mentioned above, when we do the

$date1 = '2010-12-03';
$date2 = '2010-11-04';

$pieces = explode("-", $date1);
$date1 = mktime(0, 0, 0, $pieces[1], $pieces[0], $pieces[2]);

Explode function, wouldn't we have the array in the following order

$pieces[0]= year
$pieces[1]= month
$pieces[2]= days

?


and doesn't mktime take the following string :

mktime(hour,minute,second,month,day,year,is_dst)

so shouldn't the above code be in the following format:-

$date1 = mktime(0, 0, 0, $pieces[1], $pieces[2], $pieces[0]);

INSTEAD OF

$date1 = mktime(0, 0, 0, $pieces[1], $pieces[0], $pieces[2]);

and for question # 2 that i had in the earlier post...

2- Adding a specific period (ex 1 month) to a stored date to calculate the expiry date.
and so on...

If i want to do this type of calculation, lets say that i want to calculate the expiery date of something [lets say 2months from now]

should i do it like

2months x 30 days x 24 hours x 60 mins x 60 secs = Total Number of seconds

so the code will be something like

$initialdate= '2010-12-03';
$pieces = explode("-", $date1);
$initialdate = mktime(0, 0, 0, $pieces[1], $pieces[0], $pieces[2]);

$expire= 2*30*24*60*60;

$finaldate= $initialdate+$expire; <!-- having the result being calculated in seconds -->

$result= date("Y/m/d", $finaldate);

or should i do the following?

$daysdelay= 20;
$expire=mktime(0,0,0,date("m"),date("d")+$daysdelay,date("Y"));

If i do something like this, would the months and dates adjust accordingly based on the "$daysdelay" ?

Would both tricks work, and isn't the second method more accurate? [i'm not sure if the second method works, but i read that this concept could be used... please clarify...]


Thank you for all of your support up until now...



Sincerely,
Sherif

[Readie]

Yea, I made a mistake with mktime there - I don't use it often enough and got the order mixed up when trying to recall it. Apologies for that :)

Adding a month to a set date... I'd advise not using the term "month" - as months are 28-31 days in length, and thus you could potentially have difficulty with customers in the future if you are only allowing 30 days.

Anyways, adding 30 days on is as simple as you stated with your math.

Alternatively, at the time of creating the record in the DB you could say:

$thirty_days_from_now = strtotime('+30 days');

[Sherif]

Thanks guys,

1. But does anyone have an idea about the addition of days in the mktime() function as i mentioned earlier

$daysdelay=20;
$expire=mktime(0,0,0,date("m"),date("d")+

$daysdelay

,date("Y"));

will the mktime() function adjust the days, months, and years respectively depending on the additional period?

The reason i am asking about this is because i need to do a lot of date calculations other than the expiry date.


2. Can someone please explain the concept behind the strtotime(), because from what i understand is that it is very similar to the mktime() function.


Thanks,
Sherif

[Readie]

mktime(0,0,0,date("m"),date("d")+$daysdelay,date("Y"));

You're mixing up UNIX timestamp, and DATE there.

Try something like this:

$future_date = date('m-d-Y', strtotime('+30 days'));
$pieces = explode("-", $future_date);
$future_date = mktime(0, 0, 0, $pieces[0], $pieces[1], $pieces[2]);

[Sherif]

Hey Readie,

From what i understand is that the mktime() and the strtotime() are similar functions but have different reference.

mktime() returns the "number seconds between the Unix Epoch (January 1 1970 00:00:00 GMT) and the time specified."

while the strtotime() "returns the number of seconds since January 1 1970 00:00:00 GMT"

when i mentioned the mktime() example earlier, i meant

$date1 = '2010-12-03';

$pieces = explode("-", $date1);
$daysdelay=20;
$datelate = mktime(0, 0, 0, $pieces[1], $pieces[2]+$daysdelay, $pieces[0]);

I think that this method should work...

But can you please further explain the strtotime() function since i don't seem to understand it? how can we use it and mix it with the mktime() as you had mentioned earlier...

and how do we use the +1 week and so on and so forth in this function...


Thanks for all of your support up until now.


Sincerely,
Sherif

[da_pinky]

see - the previous examples with explode() to calculate the time are a mess. instead you get you times in unix format from mysql straight away:

SELECT
UNIX_TIMESTAMP(mydate_1) as ux_mydate_1,
UNIX_TIMESTAMP(mydate_2) as ux_mydate_2,
FROM
....

and this always gives you the date in seconds since 1970. from this point on its just simple additions or substractions, and also exactly the same of what you get from mktime().

[Sherif]

Hey da_pinky

You are right, regards the UNIX_TIMESTAMP Function, but i was wondering about the addition in the mktime() function itself:

$datelate = mktime(0, 0, 0, $pieces[1], $pieces[2]+$daysdelay, $pieces[0]);

and i thought that this method would work, since we will not have to calculate everything with the details of the number of seconds... what do you think?

if we use this method [mktime() addition], we will not have to calculate the allowable period in seconds, then add it. On the other hand, we can do this directly in the mktime() function as mentioned in the example above

What is your opinion? and the final question was... will the mktime() function adjust all of the other parameters accordingly?

example

$date="2010-12-20"
$daysdelay="20"; //days

$pieces = explode("-", $date);
$datelate = mktime(0, 0, 0, $pieces[1], $pieces[2]+$daysdelay, $pieces[0]);
$datefinal= date("d-m-Y",$datelate);

in the example above, will it give me the result of

echo $datefinal; // will this be the output 09-01-2011

Thanks for your support.

Sincerely,
Sherif

[Readie]

Saying plus 20 days in mktime will fail - as mktime does not understand the 51st of (month)

ALso, some of my code is rather pointless, now that I'm looking at it with a clearer head:

$future_date = date('m-d-Y', strtotime('+30 days'));
$pieces = explode("-", $future_date);
$future_date = mktime(0, 0, 0, $pieces[0], $pieces[1], $pieces[2]); 

Is the same as saying:

$future_date = strtotime('+30 days');

strtotime basically parses pretty much any date-based string into a UNIX timestamp (number of seconds since Jan 1st 1970)

A UNIX timestamp can then be fed into the second parameter of the PHP date() function to render it into a readable format. To make it match your database format, we simply do:

$date2 = date('Y-m-d', strtotime('+1 week 5 hours 7 minutes 23 seconds'));

[Sherif]

Dear Readie,

Thanks a lot for all of your support...

Can you please explain the concept of the function strtotime()

I just can't understand the concept of +30 days +5 hours without having any reference time to do the calculations on.... [can you please clarify this?]

Also, can you also please tell me your final opinion on how i should calculate the difference between two dates?


Thank you for all of your support...


Sincerely,
Sherif Malek

[Matthew1980]

Hi there sherif,

Sorry (Readie) for butting in here

Strtotime();

This is a great little function: [uk2.php.net ] This link will explain it in a far better way than I can. Succinctly, it means a literal string is converted to time, just what it says on the tin, I use it a lot on my projects, it's great for making time adjustments taking into account BST & GMT etc ;p

Cheers,
MRb

[Sherif]

Hey Mathew1980....

I already tried reading this before i posted the question, but i didn't understand the documentation as i usually would.. the problem is that

int strtotime ( string $time [, int $now ] )

the string of time is the place where we place the relative calculations, but the thing is, i guess that when we want to do the calculation, we place the date in the $now string (integer) ... but the problem is that in all of the examples, the $now is always omitted...

As Readie showed in his example earlier...

$date2 = date('Y-m-d', strtotime('+1 week 5 hours 7 minutes 23 seconds'));

The second thing if we should place the date that we want to perform the calculation on in the $now variable function which should the timestamp be calculated based on (relative date)?


Thanks for your support...


Sincerely,
Sherif

[Matthew1980]

Hi there sherif,

$date2 = date('Y-m-d', strtotime('+1 week 5 hours 7 minutes 23 seconds'));

Right, if your struggling with what this translates to, I shall explain it.

Firstly: The date() function is assigned to the $date2 var with the format of "Y-m-d", which is (Y) four digit representation of the current year; (m) Two digit representation of current month and lastly; (d) Day of the month in two digit format.

Secondly: The strtotime() function used within the date() as second parameter just means that if you say "+1 week 5 hours", php will calculate the returned date from the date() function by literally adding a week and 5 hours - it is meant as a way of offsetting or gaining time, you can do this as + or -(minus) from the date generated by the date() function.

Thirdly: The second parameter of the strtotime() function is used as a start point, so you can say an offset, and the format in the date() function, but the second parameter is the means of a starting point, so for arguments sake you could say:-

$startDate = strtotime("1985");//Reto Clock of sorts :)
echo date("Y-m-d", strtotime("+1 month", $startDate));

this would output:-

1985-08-12

Also you can use strtotime() and say "GMT" or "BST" and it will return the date according to the time of year (British/Greenwhich Mean Time) summer time etc.

I hope I understood you right, if you already understood the workings of the function, just ignore what I have put :)

Cheers,
MRb

[Sherif]

Dear Metthew1980,

Thanks for the explanation, but there is something that i don't seem to understand...

when we use the strtotime() function in the date() function, from what i understand, that this function is used to format the date that is being displayed as you mentioned in your first note.

date(format,timestamp).. what is confusing me is that when we place the strtotime() function in the date function, we are placing it in the $timestamp section of the date, so this is the zone that the date should use to output the date (reference date).... so how is this used to give the strtotime function the date to be used.

Can you please further explain the strtotime() function, and explain in detail how it is integrated with the date() function?


Thanks a lot for your support.


Sincerely,
Sherif

[Matthew1980]

Hi there Sherif,

The only thing as I can say to you to get you to comprehend this usage further is try it for your self, experiment with different words or phrases.

One more example

echo date("Y-m-d", strtotime("+1 day"))

This example will output the date according to the computer/host time that the php parsing file is located, using strtotime() and passing the textual value of add "1 Day" tells the parser to offset or gain the time according to the textual value passed into the strtotime() function.

strtotime() once set (having a legible textual value) will return the value as an int [uk2.php.net ] (first paragraph of the parameter explanations) and this is what & how the date() function second parameter interprets the data - at least this is how I understand this to work.

I must admit though, I found this out from a fellow coder who showed me how to adjust the server time by using plain English words and storing the values in DB field (called 'time_adjustment') and just referencing that directly into the strtotime function. Simple and effective.

Other than that I can't really explain it any better than I have tried, other than try to encourage you to experiment with the example I have given.

Hope this helps you a little.

Cheers,
MRb

[Sherif]

Dear Matthew1980,

Thanks a lot for your cooperation and patience with me until now. I will start experimenting with it until i get the grasp with it.

BTW Readie, I was experimenting with the mktime() function, and i tried adding +66

Saying plus 20 days in mktime will fail - as mktime does not understand the 51st of (month)

and i tried the following code

$date="2010-12-20"
$daysdelay="20"; //days

$pieces = explode("-", $date);
$datelate = mktime(0, 0, 0, $pieces[1], $pieces[2]+$daysdelay, $pieces[0]);
$datefinal= date("d-m-Y",$datelate);

and just for your knowledge i just wanted to inform you and the people here in the forum that it worked and the mktime() function understood the +50days, and added the 50days by adding the maximum possible number of days, then then increased the number of months by one, and then added the remaining days to the new month...

Thanks guys for all of your support, and I'm going to experiment with the strtotim() function and date() function. If there is anything new, i'm going to update you guys here.


Sincerely,
Sherif

[Readie]

Hmm, I was not aware of that, though I still believe it is a practice best avoided.

Anyways, apologies for seeming to disappear, I've had a hellish few days and have had barely any free time at all. Glad Matt took over for me :)

If you need any more assistance don't hesitate to post :)