DROP TABLE IF EXISTS `sales_reporting`;  
CREATE TABLE `sales_reporting` (
  `id` varchar(30) NOT NULL,
  `division_id` int(10) unsigned NOT NULL,
  `division_code` varchar(30) NOT NULL,
  `division_desc` varchar(30) NOT NULL,
  `customer_id` int(10) unsigned NOT NULL,
  `customer_code` varchar(30) NOT NULL,
  `customer_name` varchar(100) NOT NULL,
  `salesman_id` int(10) unsigned NOT NULL,
  `salesman_firstname` varchar(100) NOT NULL,
  `salesman_lastname` varchar(100) NOT NULL,
  `term_id` int(10) unsigned NOT NULL DEFAULT 1,
  `term_day` int(10) NOT NULL DEFAULT 0,
  `term_code` varchar(20) NOT NULL DEFAULT '',
  `doc_type` char(3) NOT NULL,
  `doc_id` int(10) unsigned NOT NULL,
  `doc_no` varchar(50) NOT NULL,
  `doc_date` datetime NOT NULL,
  `month` int(2) unsigned NOT NULL,
  `year` int(4) unsigned NOT NULL,
  `due_date` datetime NOT NULL,
  `currency_id` int(10) unsigned NOT NULL,
  `currency_code` varchar(10) NOT NULL,
  `currency_rate` decimal(11,4) NOT NULL,  
  `description` varchar(200) NOT NULL DEFAULT '',
  `sign` int(1) NOT NULL, 
  `ref_no_01` varchar(30) NOT NULL DEFAULT '',
  `ref_no_02` varchar(100) NOT NULL DEFAULT '',
  `remark` varchar(200) NOT NULL DEFAULT '', 
  `area_id` int(10) unsigned NOT NULL,
  `area_code` varchar(20) NOT NULL,
  `area_desc` varchar(60) NOT NULL,
  `customer_category_id` int(10) unsigned NOT NULL,
  `customer_category_code` varchar(20) NOT NULL,
  `customer_category_desc` varchar(60) NOT NULL,
  `customer_category_1_id` int(10) unsigned NOT NULL,
  `customer_category_1_code` varchar(20) NOT NULL,
  `customer_category_1_desc` varchar(60) NOT NULL,
  `customer_category_2_id` int(10) unsigned NOT NULL,
  `customer_category_2_code` varchar(20) NOT NULL,
  `customer_category_2_desc` varchar(60) NOT NULL,
  `price_group_id` int(10) unsigned NOT NULL,
  `price_group_code` varchar(10) NOT NULL,
  `price_group_desc` varchar(60) NOT NULL,
  `item_id` int(10) unsigned NOT NULL,
  `item_code` varchar(30) NOT NULL,
  `item_desc` varchar(60) NULL,
  `item_group_id` int(10) unsigned NOT NULL,
  `item_group_code` varchar(10) NOT NULL,
  `item_group_desc` varchar(60) NOT NULL,
  `item_group_1_id` int(10) unsigned NOT NULL,
  `item_group_1_code` varchar(10) NOT NULL,
  `item_group_1_desc` varchar(60) NOT NULL,
  `item_group_2_id` int(10) unsigned NOT NULL,
  `item_group_2_code` varchar(10) NOT NULL,
  `item_group_2_desc` varchar(60) NOT NULL,
  `max_uom_rate` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `promotion_hdr_id` int(10) NOT NULL DEFAULT 0,
  `promotion_code` varchar(30) NULL,
  `promotion_desc` varchar(100) NULL,
  `promotion_desc_1` varchar(100) NULL,
  `promotion_desc_2` varchar(100) NULL,
  `valid_from` datetime NULL,
  `valid_to` datetime NULL,
  `promotion_type` char(1) NULL DEFAULT '',
  `location_id` int(10) unsigned NOT NULL,
  `location_code` varchar(15) NOT NULL,
  `location_desc` varchar(60) NOT NULL DEFAULT '',
	
  `price` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `sales_qty` decimal(14,4) NOT NULL DEFAULT '0.0000',
  `return_qty` decimal(14,4) NOT NULL DEFAULT '0.0000',
  `sales_qty_case` decimal(14,4) NOT NULL DEFAULT '0.0000',
  `return_qty_case` decimal(14,4) NOT NULL DEFAULT '0.0000',
  
  `item_disc_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `footer_disc_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  
  `sales_exc_tax_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `sales_tax_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `sales_inc_tax_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
    
  `return_exc_tax_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `return_tax_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `return_inc_tax_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',   
  PRIMARY KEY (`id`, year, month, doc_date)  
) ENGINE=MYISAM DEFAULT CHARSET=utf8; 

ALTER TABLE sales_reporting PARTITION BY RANGE(TO_DAYS(doc_date) ) (	
    PARTITION sr12014 VALUES LESS THAN (TO_DAYS('2014-03-31 00:00:00')),
    PARTITION sr22014 VALUES LESS THAN (TO_DAYS('2014-06-30 00:00:00')),
    PARTITION sr32014 VALUES LESS THAN (TO_DAYS('2014-09-30 00:00:00')),
    PARTITION sr42014 VALUES LESS THAN (TO_DAYS('2014-12-31 00:00:00')),
    PARTITION sr12015 VALUES LESS THAN (TO_DAYS('2015-03-31 00:00:00')),
    PARTITION sr22015 VALUES LESS THAN (TO_DAYS('2015-06-30 00:00:00')),
    PARTITION sr32015 VALUES LESS THAN (TO_DAYS('2015-09-30 00:00:00')),
    PARTITION sr42015 VALUES LESS THAN (TO_DAYS('2015-12-31 00:00:00')),
    PARTITION sr12016 VALUES LESS THAN (TO_DAYS('2016-03-31 00:00:00')),
    PARTITION sr22016 VALUES LESS THAN (TO_DAYS('2016-06-30 00:00:00')),
    PARTITION sr32016 VALUES LESS THAN (TO_DAYS('2016-09-30 00:00:00')),
    PARTITION sr42016 VALUES LESS THAN (TO_DAYS('2016-12-31 00:00:00')),
    PARTITION srfuture VALUES LESS THAN MAXVALUE
);

DROP TABLE IF EXISTS `date_dimension`; 
CREATE TABLE `date_dimension` (
	`date` datetime NOT NULL,
    `day_of_month` int(2) unsigned NOT NULL,
    `month` int(2) unsigned NOT NULL,
    `month_name` varchar(30) NOT NULL,
    `year` int(5) unsigned NOT NULL,
    `quarter` int(2) unsigned NOT NULL,
	PRIMARY KEY (`date`)  
) ENGINE=MYISAM DEFAULT CHARSET=utf8;

DROP PROCEDURE IF EXISTS populate_date_dimension;

DELIMITER $$
CREATE PROCEDURE populate_date_dimension(startDate datetime, endDate datetime)
BEGIN 
	WHILE startDate <= endDate DO    
		insert into date_dimension values (startDate, DAY(startDate), MONTH(startDate), MONTHNAME(startDate), YEAR(startDate), QUARTER(startDate));
		SET startDate = DATE_ADD(startDate, INTERVAL 1 DAY);
	END WHILE;
 END$$
 DELIMITER ;

CALL populate_date_dimension('2015-01-01 00:00:00', '2035-12-31 00:00:00');
