Welcome Guest, Not a member yet? Register   Sign In
need sql help
#1

[eluser]theprodigy[/eluser]
I'm trying to build a bills tracking application, where either me or my girlfriend can enter the company information, any bills associated with each company, and payments associated with the bills. I'm not trying to get fancy or anything, no account numbers will be stored. Just a quick and dirty bills tracking app.

I currently have 4 tables: Users, Companies, Bills, Payments

They are as follows:

Users:
Code:
CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(11) NOT NULL auto_increment,
  `login` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `fname` varchar(255) NOT NULL,
  `lname` varchar(255) NOT NULL,
  PRIMARY KEY  (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Companies:
Code:
CREATE TABLE IF NOT EXISTS `companies` (
  `company_id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `nickname` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `address2` varchar(255) NOT NULL,
  `city` varchar(255) NOT NULL,
  `state` char(2) NOT NULL,
  `zip` varchar(10) NOT NULL,
  `phone` varchar(255) NOT NULL,
  `website` varchar(255) NOT NULL,
  `notes` text NOT NULL,
  PRIMARY KEY  (`company_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Bills:
Code:
CREATE TABLE IF NOT EXISTS `bills` (
  `bill_id` int(11) NOT NULL auto_increment,
  `company_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `type` varchar(255) NOT NULL,
  `amount` decimal(64,2) NOT NULL,
  `due_date` date NOT NULL,
  `notes` text NOT NULL,
  PRIMARY KEY  (`bill_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Payments:
Code:
CREATE TABLE IF NOT EXISTS `payments` (
  `payment_id` int(11) NOT NULL auto_increment,
  `bill_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `amount` decimal(64,2) NOT NULL,
  `date` date NOT NULL,
  `notes` text NOT NULL,
  PRIMARY KEY  (`payment_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Now, I've tried to write a query that would pull all the bills where the sum of the payments does not equal the bills.amount column, but I seem to be missing something. It is only pulling the bills where the sum of payments is less if there is a payment associated with it.

There query is as follow:
Code:
$this->db->select('c.name as company,c.nickname as nickname,c.website,b.bill_id,b.due_date,b.user_id, (b.amount-sum(`p`.`amount`)) as amount');
$this->db->from('bills b');
$this->db->join('companies c','b.company_id = c.company_id');
$this->db->join('payments p','b.bill_id = p.bill_id','LEFT OUTER');
$this->db->where('type',$type);
$this->db->having('amount >',0);
$this->db->group_by('c.name');
$this->db->order_by('b.type,c.name,b.due_date');
return $this->db->get();

with type being either service or debt (for type of account).


Any help would be greatly appreciated.

Thank you
#2

[eluser]LinkFox[/eluser]
nevermind. I just realised I didn't really answer the question. I will have a look at this when I get home.
#3

[eluser]theprodigy[/eluser]
In case it helps:

I'm not stuck on the table layout. If a solution could be easier implemented using a different layout, by all means, please let me know.

I do appreciate any help you can offer.
#4

[eluser]jedd[/eluser]
Suggestion:
pk's should be called 'id' and be unsigned ints (php5 can't cope with the larger mysql integer types in any case).

Question:
this looks really easy to do in multiple queries and then do the comparison in PHP - is there a reason you're avoiding this approach?
#5

[eluser]theprodigy[/eluser]
Doing it in multiple queries, I don't mind. I want to try to stay away from having to use PHP to check for bills whose payments are equal to or greater than the bill because I plan on keeping this for a while. The more bills I add, the longer it will take because there are that many more bills to have to compare to. At the beginning, it wouldn't be that bad, but after a while (and not exactly a long while), you're looking at pulling hundreds of bills from the database that will never get shown because they are already paid.

If I can get the query to only pull the bills that still have a positive amount due, then that's less processing I have to worry about.
#6

[eluser]jedd[/eluser]
Can you please answer / clarify:

bill : payment is 1 : n , yes?

Would you be offended at the idea of de-normalising your database by having a flag that indicated when a bill was done (for want of a better word). Would that work, or is the overlap of payments to bills likely to make that infeasible? Or, if you prefer a differently worded question, is bill:payment actually n:m ?
#7

[eluser]theprodigy[/eluser]
I'm not completely up with UML syntax yet, but I'm guessing you are asking about the type of relationship.

It is possible to have many payments to 1 bill (one payment being less then full requiring another to pay it off, extra payment on credit card, etc).
But each payment is designated to 1 bill and 1 bill only (one payment won't go towards both the electric bill AND rent).

I've thought about doing the flag to designate will a bill was completely paid, and I'm still not completely against it, but if there is a way around it, it is preferred.

Thanks
#8

[eluser]jedd[/eluser]
Okay, I meant could one payment span multiple bills. I know that in the past I've often done this - a bill comes in, and I'll pay less or more than is on the thing, for one reason or another, and the subsequent bill comes in showing the new amount less/plus the delta. I know one payment won't span different *companies* - but want to clarify that for any given bill there will be one or more payments, and for any given payment it will be for one bill and one bill only.

If it's the latter - that is, a payment will match to one bill and for any given bill you have one of:
o no payments associated
o one or more payments associated that total to less than the bill amount
o one or more payments associated that total to precisely the bill amount

.. then things are slightly easier - or at least clearer Wink
#9

[eluser]theprodigy[/eluser]
yes, for each 1 bill, there can be:
0 payments (if none have been made yet)
1 or more payments that equal less then the bill
1 or more payments that equal the bill exactly
1 or more payments that exceed the bill amount (extra payment to credit card, or pay extra to a bill)

For this site, I am not setting up any re-occuring payments or anything like that. When a bill comes in, either my girlfriend or myself will go to the site to enter the information in. The query that I am looking for will pull all the bills that do not have enough money paid to it to pay it completely.

I hope that helps.
#10

[eluser]jedd[/eluser]
Okay .. you can't add a conditional and then assert it's the same as my criteria! Wink That last item you slipped in there was exactly what I was talking about in my earlier message.

In any case, what this means is you no longer have a 1:n relationship for bill:payment, because a payment may in fact span across - or relate to - more than one bill.

There's some interesting stock management and accounting nomenclature for the arrangement you're looking at, but the net result here is that I think the only way you can determine if you owe money for a given bill is to calculate what you owe the company and compare it to what you've paid the company.

That is:

foreach company
calculate sum(bills)
calculate sum(payments)
if disparate
display delta




Theme © iAndrew 2016 - Forum software by © MyBB