[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
