Hello,
I have two tables in mysql using php> 1) Consumers 2) Monthly Billing
In Consumers that table contains about 100s of Consumers , and it increase monthly.
2) Monthly Billing for Billing.

What i want to do ?
Is there are any way that i can generate monthly billing automatically or any other simple way. My Consumers are in 100s or may be 1000s so i can't just go to add new bill for every consumers it will takes days.

Member Avatar

Use a cron job to email your consumers.

commented: Automatic? That's a cron'ing (Willy the groundskeeper) -2

No i have to generate new month bills for all consumers and then have to print it and then dispatch it to address. its like your Electricity, Water or Gass Bills.

Member Avatar

Show your table structures

Consumers:
ID
Cus_name
Cus_Address
Cus_Type
Cus_account
Cus_link

Bill:
ID
Bi_Month
Reading_Dat
Due_Date
Amount
Due

Member Avatar

Doesn't look right to me. I'd have the following, but as I don't know the ins and outs of your operation, some aspects may be wrong or incomplete or overkill. This is just an example:

Consumers

id
firstname
surname
title_id (FK to titles table, e.g. Mr, Mrs, Dr etc)
address1
address2
city
region
postcode
tel1
tel2
email
type_id (FK on consumer_types table)
link

The consumers above should be about the consumers, nothing else - no account ids etc, since a consumer should (in theory) be able to have more than one account.

Accounts

id
consumer_id (FK to consumers table)
account_type_id (FK to account_type table)
open_date
start_billing_date
billing_frequency (2 = bimonthly, 1 = monthly etc)
active (tinyint 0/1 to signify if account is active or not)

The accounts table should be just bare bones - leave billing (invoices) as a separate entity, or you'll be duplicating data all over the place

Invoices

id
account_id (FK to accounts table)
bill_date
due_date
reading_id (FK to readings table)
paid (tinyint 0/1 - yes or no)

Readings

id
account_id (FK to accounts table)
reading_date
amount_due

titles

id
label

consumer_types

id
label

account_types

id
label
...other fields...

I've over-related the readings / accounts / billing tables, but brain in lockdown at moment.
Use a daily(?) cronjob to produce your invoices from data held in other tables.
Have a php function to print out the invoices for the current bill_date only.
Personally, I'd use a html->pdf creator - plenty of them around to create one document holding all the invoices for that day, and just print off that one document.

ALmost forgot, you may need a separate Payments table too (related to invoice).

Maybe that is overkill, maybe not, but in any case, starting with OP's tables, they need at least some kind of foreign key to relate Bills to Consumers. Without that the problem has no solution.

commented: Probably there is a hidden variable to the problem that the Starter didn't share, someway he make it work even for one even by hand +9

you all are right but here with me my application is working "Try to understand my prob my php based application is basically for (Water Billing) not like other invoicing already exest in open source" my project is different i need method how to generate monthly bills for my consumer's not customers.

For Example: i have 100 consumers and i want to generate Bill for the every consumers month of August.(Reading + Due Date and Due Charges) should be the same for all consumers but consumer number & postal address should be different.

I just want to input the following "Reading Date, Due Date & Due Fee only" and then my application have to generate about 100+ bills for consumers, every biil should have their own consumer's number.

Is this possible or not if yes how what methos should i have to use?
It is like your Electricity or Gas Bill.

Diafol mentioned cron jobs , but you replied to something different. Do you understand what a cron job is ?

Member Avatar

I was obviously bored this morning, heh heh.
If you just want an easy minimum setup, then as James says, you need a related field in your bill table.

Consumer: id/name (+ other personal details)
Bills: id/consumer_id/read_date/due_date/due_fee/fee_paid

So, fill your data and insert to DB.
Extract revelant DB records into array.
You should be ready to output ALL of that data to a single PDF file, suing a template.

Which aspect are you having difficulty with?

Sorry Diafol my post was ambiguous , let me rephrase it:

Spluskhan , in previus replies Diafol mentioned cron jobs (twice) but you didn't reply to that . Spluskhan do you understand what a cron job is ? I am asking that because is not clear to me that you do and for some reason (what could that be ?) doesn't fit your needs , If you don't we can help you.

If you understand what a cron job is and still have problems its time to rethink your data structure as Diafol has suggested.

i'm not well known familiar with cron job but cron job is not an solution for my task.

commented: If you are not familiar with something how you know that this is not the solution? Why don't you explain why this isn't the solution for you? -2

I obviousy don't undertand what this app is supposed to do. Every customer has the same 2 dates and charges?
My utility bills depend on the amount I use (the "reading") and so are not the same as anyone else's.

Look, I am alble to generate every consumers bills independently and print them.
But i want make it easear i don't have time to generate 100+ consumer's bill separately and then print them.

Suppose currently i have 100 consumers next month they may be 1000+. Then i will need 100 employ for that work.

is there are any easy way or not ?

ok thanks but can you explain it to me?

Member Avatar

ook, I am alble to generate every consumers bills independently and print them.
But i want make it easear i don't have time to generate 100+ consumer's bill separately and then print them.

I'm still confused. You aren't explaining the issue itself. Which aspect of this are you finding difficulty with:

(1) Entering data
(2) Outputting ALL billed consumers bills into ONE file in ONE operation (single button press)

If (2) - are you currently using a PDF lib like fpdf: http://www.fpdf.org/ ?

Data input is the time consuming aspect here. Outputting data will take a few seconds to process 1000 bills and maybe printing 10-40 bills / minute - 25 to 100 minutes to do the lot?

It would help if you showed any PHP code that you already have and explain how it is used. It may give us a better understanding of how your system works.

While this looks like a cron job, you didn't reveal enough about your system. Even Windows could make this yet another cron job. Or for some folk they like to use AUTOIT plus on a schedule with cron. https://www.google.com/search?q=autoit+cron+job if you never heard of such a mashup.

Need Outputting ALL billed consumers bills into Seperate file in ONE operation (single button press)

Hello all!

@spluskhan you can do that, but by increasing the number of bills to process, the script may crash for timeout or for memory issues, for this reason you were requested to provide more details.

To keep it simple: you can start the bill generation process by pressing a form button, but you need to delegate the work to another script that runs in background and process the request asynchronously. You have to define a queue, and process it by a batch, eg 100 bills at time.

With cronjob you can create a script that consumes the queue at regular intervals, the minimum is 1 minute. It means that if the script is able to generate 100 PDF in 1 minute, then, you can generate 1000 bills in 10 minutes. But you have to log somewhere (perhaps the database) which rows are in the queue, which have been executed successfully and also the failures, so that you can repeat the failed job and don't miss any bill.

With cronjob you may not have timeout issues, because the script will quit when it finishes and it will start a new process when the cron tab is executed, but you have less control and possibly more latency.

For this task I would prefer using a daemon: a script that waits to get a job (the bill generation) and that when it finishes it starts another one, without quitting. If your server is able to process multiple requests, then you can add more daemons (aka workers), for example one worker for each core of the CPU, so if you have 8 cores you can set up 8 workers and process the bills immediately one by one, not in batches, and you're not limited by the 1 minute pace limit of cronjob.

But using daemons will add complexity. You can use the native forking PHP functions or use prepared solutions.

A specific solution for these kind of tasks is Beanstalkd: http://kr.github.io/beanstalkd/
It works very well, it is multi-platform, it allows to use multiple languages and the worker can be distributed in multiple machines: the client can be in PHP, but the worker can be in any of the supported languages and into another server of the lan. In SOHO I've distributed tasks between MacOS, Linux and RaspberryPI boxes, with great results.

And last: since PHP is not good to handle long processes, it's a good practice to handle these daemons through programs like Supervisord: http://supervisord.org/
Which starts the daemons when the system boots and restarts them when, for some reason, crash. You can also configure it (suggested) to restart the daemons at regular intervals. And it allows to stop the script without loosing the current job.

Just my opinion, bye!

Member Avatar

Just wondering about the cron job. The user could be a prisoner of minute-deadlines to get the data inputted before the job runs at a specific time. This is always the case though, but would SSH be a better fit to run a php file? My line of thinking:

1) Enter data
2) Send ssh command to run php file - creates aforementioned pdf file
3) When ready, pdf file appears in a "billdocuments" page as a link (to download/open and print)

Perhaps I'm being naive.

@cereal im agree with your this part:
For this task I would prefer using a daemon...

@rproffitt The cron program is indeed a daemon, but the scripts listed in the cron table (crontab) are usually not daemons: once executed they quit and will ran again at the defined time in the crontab. Cron has 1 minute limitation, it does not execute immediately, so once you send the job, for example: by altering a database table value, or by using a lock file, it will start in the next valid minute.

Cron will, also, start a new instance of the script, with a different PID, when the time is reached again, so you can end having uncontrolled multiple instances of the same script running on the same batches, if the rows are not signed as in one of the current queues.

Obviously one could list multiple scripts in the crontab to do the same work and speed the execution, but again these processes will be concurrent in the query requests and each script will have to lock the database and sort which rows to process.

IMHO this is where work queues softwares come handy and solves most of these issues. In this case the worker script is usually a daemon that waits to process a job. The client instead it can be a simple PHP script that receives the request from a form (or even by cron) and submits the jobs to the job server. It's a very different approach.

@cereal. My thought was "how strange to ask to use a daemon and as cron was such a thing, well, may as well point that out."

It should be interesting which solutions they chose at the end.

commented: oh! ok, I've got it +14

Some of my friend advice me to avoid PHP or ASP, and go for Desktop based application for this task.
Thanks to all of for your time, and if any body is familiar with any open source tools it would be great.

Member Avatar

Like what? You don't mention which OS. Are you still using a remote DB (mysql) or SQL server? Or are you going to switch to something like MS Access? Which languages are you going to use? Did your friends suggest anything?

I can understand that setting up a daemon is a bit of a headache, but definitely do-able. A cronjob would be very simple - just set the time to execute the file and that's it.

Given how well I'm seeing PHP work, I can't imagine a programmer suggesting avoiding PHP.

I would be guessing "open source" is another way for you to say "free" so there are free tools that are not open source that I use but here we are, quite a few posts and missing some details (diafol asked the questions.)

Answer those questions and I'm share how I might tackle this.

im going to upload my application to some free server you guys will see it and then give me your suggestions.

is anybody familiar with any best and quick hosting providers??