CREATE TABLE tblmodule_taxes_backup AS SELECT * FROM tblmodule_taxes;

CREATE TABLE tblchange_orders_backup AS SELECT * FROM tblchange_orders;
CREATE TABLE tblchange_order_tasks_backup AS SELECT * FROM tblchange_order_tasks;

CREATE TABLE tblestimates_backup AS SELECT * FROM tblestimates;
CREATE TABLE tblestimate_tasks_backup AS SELECT * FROM tblestimate_tasks;

CREATE TABLE tblinvoices_backup AS SELECT * FROM tblinvoices;
CREATE TABLE tblinvoice_tasks_backup AS SELECT * FROM tblinvoice_tasks;

CREATE TABLE tblitems_backup AS SELECT * FROM tblitems;


ALTER TABLE `tblmodule_taxes` MODIFY `amount`  DECIMAL(15,2) NOT NULL;
ALTER TABLE `tblmodule_taxes` MODIFY `taxrate` DECIMAL(15,4) NOT NULL;

ALTER TABLE `tblchange_orders` MODIFY labor_markup DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT 'In Percent for Tax Type Global';
ALTER TABLE `tblchange_orders` MODIFY supply_markup DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT 'In Percent for Tax Type Global';


ALTER TABLE `tblchange_order_tasks` MODIFY supply_markup DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT 'In Percent';
ALTER TABLE `tblchange_order_tasks` MODIFY labor_markup DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT 'In Percent';

ALTER TABLE `tblestimates` MODIFY labor_markup DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT 'In Percent for Tax Type Global';
ALTER TABLE `tblestimates` MODIFY supply_markup DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT 'In Percent for Tax Type Global';

ALTER TABLE `tblestimate_tasks` MODIFY supply_markup DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT 'In Percent';
ALTER TABLE `tblestimate_tasks` MODIFY labor_markup DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT 'In Percent';

ALTER TABLE `tblinvoices` MODIFY labor_markup DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT 'In Percent for Tax Type Global';
ALTER TABLE `tblinvoices` MODIFY supply_markup DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT 'In Percent for Tax Type Global';

ALTER TABLE `tblinvoice_tasks` MODIFY supply_markup DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT 'In Percent';
ALTER TABLE `tblinvoice_tasks` MODIFY labor_markup DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT 'In Percent';

ALTER TABLE `tblitems` MODIFY labor_markup DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT 'Labor Markup In Percent';
ALTER TABLE `tblitems` MODIFY supply_markup DECIMAL(15,2) DEFAULT NULL COMMENT 'Supply Markup In Percent';

------------  Date issue fixes ------------------

SELECT * FROM `tblinvoices`  where CAST(request_deposit_date AS CHAR(10)) = '0000-00-00';
SELECT * FROM `tblestimates`  where CAST(request_deposit_date AS CHAR(10)) = '0000-00-00';

SELECT * FROM `tblchange_orders` WHERE CAST(datecreated AS CHAR(10)) = '0000-00-00';

UPDATE tblestimates SET request_deposit_date = NULL WHERE CAST(request_deposit_date AS CHAR(10)) = '0000-00-00';
UPDATE tblinvoices SET request_deposit_date = NULL WHERE CAST(request_deposit_date AS CHAR(10)) = '0000-00-00';
UPDATE tblchange_orders SET datecreated = concat(date,' 01:01:01') WHERE CAST(datecreated AS CHAR(19)) = '0000-00-00 00:00:00';

------------- compare ---------------------------

SELECT 
    OLD.id, 
    OLD.taxrate AS old_taxrate, 
    NEW.taxrate AS new_taxrate,
    ROUND(OLD.taxrate, 4) AS rounded_old_amount, 
    ROUND(NEW.taxrate, 4) AS rounded_new_amount
FROM 
    tblmodule_taxes_backup as OLD
JOIN 
    tblmodule_taxes as NEW 
ON 
    NEW.id = OLD.id
WHERE 
ROUND(NEW.taxrate, 4) != ROUND(OLD.taxrate, 4);

-----------------------

SELECT 
    OLD.id, 
    OLD.amount AS old_amount, 
    NEW.amount AS new_amount,
    ROUND(OLD.amount, 2) AS rounded_old_amount, 
    ROUND(NEW.amount, 2) AS rounded_new_amount
FROM 
    tblmodule_taxes_backup as OLD
JOIN 
    tblmodule_taxes as NEW 
ON 
    NEW.id = OLD.id
WHERE 
ROUND(NEW.amount, 2) != ROUND(OLD.amount, 2);

---------------------- ESTIMATE -----------------

SELECT 
    OLD.id, 
    OLD.labor_markup AS old_labor_markup, 
    NEW.labor_markup AS new_labor_markup,
    ROUND(OLD.labor_markup, 2) AS rounded_old_amount, 
    ROUND(NEW.labor_markup, 2) AS rounded_new_amount
FROM 
    tblestimates_backup as OLD
JOIN 
    tblestimates as NEW 
ON 
    NEW.id = OLD.id
WHERE 
ROUND(NEW.labor_markup, 2) != ROUND(OLD.labor_markup, 2);
---------------------
SELECT 
    OLD.id, 
    OLD.supply_markup AS old_supply_markup, 
    NEW.supply_markup AS new_supply_markup,
    ROUND(OLD.supply_markup, 2) AS rounded_old_amount, 
    ROUND(NEW.supply_markup, 2) AS rounded_new_amount
FROM 
    tblestimates_backup as OLD
JOIN 
    tblestimates as NEW 
ON 
    NEW.id = OLD.id
WHERE 
ROUND(NEW.supply_markup, 2) != ROUND(OLD.supply_markup, 2);

-----------------------------

SELECT 
    OLD.id, 
    OLD.labor_markup AS old_labor_markup, 
    NEW.labor_markup AS new_labor_markup,
    ROUND(OLD.labor_markup, 2) AS rounded_old_amount, 
    ROUND(NEW.labor_markup, 2) AS rounded_new_amount
FROM 
    tblestimate_tasks_backup as OLD
JOIN 
    tblestimate_tasks as NEW 
ON 
    NEW.id = OLD.id
WHERE 
ROUND(NEW.labor_markup, 2) != ROUND(OLD.labor_markup, 2);

------------------------------------
SELECT 
    OLD.id, 
    OLD.supply_markup AS old_supply_markup, 
    NEW.supply_markup AS new_supply_markup,
    ROUND(OLD.supply_markup, 2) AS rounded_old_amount, 
    ROUND(NEW.supply_markup, 2) AS rounded_new_amount
FROM 
    tblestimate_tasks_backup as OLD
JOIN 
    tblestimate_tasks as NEW 
ON 
    NEW.id = OLD.id
WHERE 
ROUND(NEW.supply_markup, 2) != ROUND(OLD.supply_markup, 2);

---------------------- INVOICE -----------------

SELECT 
    OLD.id, 
    OLD.labor_markup AS old_labor_markup, 
    NEW.labor_markup AS new_labor_markup,
    ROUND(OLD.labor_markup, 2) AS rounded_old_amount, 
    ROUND(NEW.labor_markup, 2) AS rounded_new_amount
FROM 
    tblinvoices_backup as OLD
JOIN 
    tblinvoices as NEW 
ON 
    NEW.id = OLD.id
WHERE 
ROUND(NEW.labor_markup, 2) != ROUND(OLD.labor_markup, 2);
---------------------
SELECT 
    OLD.id, 
    OLD.supply_markup AS old_supply_markup, 
    NEW.supply_markup AS new_supply_markup,
    ROUND(OLD.supply_markup, 2) AS rounded_old_amount, 
    ROUND(NEW.supply_markup, 2) AS rounded_new_amount
FROM 
    tblinvoices_backup as OLD
JOIN 
    tblinvoices as NEW 
ON 
    NEW.id = OLD.id
WHERE 
ROUND(NEW.supply_markup, 2) != ROUND(OLD.supply_markup, 2);

-----------------------------

SELECT 
    OLD.id, 
    OLD.labor_markup AS old_labor_markup, 
    NEW.labor_markup AS new_labor_markup,
    ROUND(OLD.labor_markup, 2) AS rounded_old_amount, 
    ROUND(NEW.labor_markup, 2) AS rounded_new_amount
FROM 
    tblinvoice_tasks_backup as OLD
JOIN 
    tblinvoice_tasks as NEW 
ON 
    NEW.id = OLD.id
WHERE 
ROUND(NEW.labor_markup, 2) != ROUND(OLD.labor_markup, 2);

----------------------------

SELECT 
    OLD.id, 
    OLD.supply_markup AS old_supply_markup, 
    NEW.supply_markup AS new_supply_markup,
    ROUND(OLD.supply_markup, 2) AS rounded_old_amount, 
    ROUND(NEW.supply_markup, 2) AS rounded_new_amount
FROM 
    tblinvoice_tasks_backup as OLD
JOIN 
    tblinvoice_tasks as NEW 
ON 
    NEW.id = OLD.id
WHERE 
ROUND(NEW.supply_markup, 2) != ROUND(OLD.supply_markup, 2);


---------------------- CHNAGE ORDER -----------------

SELECT 
    OLD.id, 
    OLD.labor_markup AS old_labor_markup, 
    NEW.labor_markup AS new_labor_markup,
    ROUND(OLD.labor_markup, 2) AS rounded_old_amount, 
    ROUND(NEW.labor_markup, 2) AS rounded_new_amount
FROM 
    tblchange_orders_backup as OLD
JOIN 
    tblchange_orders as NEW 
ON 
    NEW.id = OLD.id
WHERE 
ROUND(NEW.labor_markup, 2) != ROUND(OLD.labor_markup, 2);
---------------------
SELECT 
    OLD.id, 
    OLD.supply_markup AS old_supply_markup, 
    NEW.supply_markup AS new_supply_markup,
    ROUND(OLD.supply_markup, 2) AS rounded_old_amount, 
    ROUND(NEW.supply_markup, 2) AS rounded_new_amount
FROM 
    tblchange_orders_backup as OLD
JOIN 
    tblchange_orders as NEW 
ON 
    NEW.id = OLD.id
WHERE 
ROUND(NEW.supply_markup, 2) != ROUND(OLD.supply_markup, 2);

-----------------------------

SELECT 
    OLD.id, 
    OLD.labor_markup AS old_labor_markup, 
    NEW.labor_markup AS new_labor_markup,
    ROUND(OLD.labor_markup, 2) AS rounded_old_amount, 
    ROUND(NEW.labor_markup, 2) AS rounded_new_amount
FROM 
    tblchange_order_tasks_backup as OLD
JOIN 
    tblchange_order_tasks as NEW 
ON 
    NEW.id = OLD.id
WHERE 
ROUND(NEW.labor_markup, 2) != ROUND(OLD.labor_markup, 2);

------------------------------------
SELECT 
    OLD.id, 
    OLD.supply_markup AS old_supply_markup, 
    NEW.supply_markup AS new_supply_markup,
    ROUND(OLD.supply_markup, 2) AS rounded_old_amount, 
    ROUND(NEW.supply_markup, 2) AS rounded_new_amount
FROM 
    tblchange_order_tasks_backup as OLD
JOIN 
    tblchange_order_tasks as NEW 
ON 
    NEW.id = OLD.id
WHERE 
ROUND(NEW.supply_markup, 2) != ROUND(OLD.supply_markup, 2);

-------------------------------ITEMABLES-------------------------------
SELECT 
    OLD.id, 
    OLD.labor_markup AS old_labor_markup, 
    NEW.labor_markup AS new_labor_markup,
    ROUND(OLD.labor_markup, 2) AS rounded_old_amount, 
    ROUND(NEW.labor_markup, 2) AS rounded_new_amount
FROM 
    tblitems_backup as OLD
JOIN 
    tblitems as NEW 
ON 
    NEW.id = OLD.id
WHERE 
ROUND(NEW.labor_markup, 2) != ROUND(OLD.labor_markup, 2);

------------------------------------
SELECT 
    OLD.id, 
    OLD.supply_markup AS old_supply_markup, 
    NEW.supply_markup AS new_supply_markup,
    ROUND(OLD.supply_markup, 2) AS rounded_old_amount, 
    ROUND(NEW.supply_markup, 2) AS rounded_new_amount
FROM 
    tblitems_backup as OLD
JOIN 
    tblitems as NEW 
ON 
    NEW.id = OLD.id
WHERE 
ROUND(NEW.supply_markup, 2) != ROUND(OLD.supply_markup, 2);