mysql query help, take total sum from a table, and based on discount value on another table calcula

Posted by vegatron on Stack Overflow See other posts from Stack Overflow or by vegatron
Published on 2010-05-24T05:04:43Z Indexed on 2010/05/24 5:10 UTC
Read the original article Hit count: 268

Filed under:
|

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

© Stack Overflow or respective owner

Related posts about mysql

Related posts about query