mysql query help, take total sum from a table, and based on discount value on another table calcula
- by vegatron
hi
I have a table called invoices:
CREATE TABLE IF NOT EXISTS `si_invoices` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`biller_id` int(10) NOT NULL DEFAULT '0',
`customer_id` int(10) NOT NULL DEFAULT '0',
`type_id` int(10) NOT NULL DEFAULT '0',
`inv_tax_id` int(10) NOT NULL,
`date` date NOT NULL DEFAULT '0000-00-00',
`unreg_customer` tinyint(1) NOT NULL DEFAULT '0',
`discount` decimal(10,2) NOT NULL DEFAULT '0.00',
`discount_type` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=20 ;
each invoice has items that are stored in invoice_items table :
CREATE TABLE IF NOT EXISTS `si_invoice_items` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`invoice_id` int(10) NOT NULL DEFAULT '0',
`quantity` int(10) unsigned NOT NULL DEFAULT '0',
`product_id` int(10) DEFAULT '0',
`warehouse_id` int(10) NOT NULL,
`unit_price` decimal(25,2) DEFAULT '0.00',
`total` decimal(25,2) DEFAULT '0.00',
`description` text,
PRIMARY KEY (`id`),
KEY `invoice_id` (`invoice_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=56 ;
and tax table
CREATE TABLE IF NOT EXISTS `si_tax` (
`tax_id` int(11) NOT NULL AUTO_INCREMENT,
`tax_description` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`tax_percentage` decimal(25,6) DEFAULT '0.000000',
`type` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL,
`tax_enabled` varchar(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '1',
PRIMARY KEY (`tax_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;
here is what I want to do
step 1: get the sum_total of the invoice Items for a speciefic invoice
step 2: calculate the discount, in the invoice table I have a discount_type field :
if its equal to 0 , then there will be no discount
if its equal to 1 , the discount value will be stored in the discount field
if its equal to 2 , the discount is a percentage of sum_total
step 3: calculate the taxes based on inv_tax_id
based on the tax id , I will look in the tax table , get the tax_percentage and multiply it by the (sum_total - discount)
in short here is the equation
$gross_total = $sum_total - $disount + taxes
