-- 21/12/2017
ALTER TABLE `prop_lease_payment_details`
 ADD `prop_lease_payment_details_amount`
 INT( 11 ) NOT NULL AFTER`prop_lease_payment_details_description` ;

-- 21/12/2017
ALTER TABLE `prop_lease_payment_details`
  DROP `prop_lease_payment_details_amount`,
  DROP `prop_lease_payment_details_service_amount`;

-- 21/12/2017
ALTER TABLE  `prop_lease_agreement` ADD  `prop_lease_agreement_rent_amount` INT( 11 ) NOT NULL AFTER  `prop_lease_agreement_tenancy_number` ,
ADD  `prop_lease_agreement_service_amount` INT( 11 ) NOT NULL AFTER  `prop_lease_agreement_rent_amount` ,
ADD  `prop_lease_agreement_advance_rent_amount` INT( 11 ) NOT NULL AFTER  `prop_lease_agreement_service_amount` ;

-- 22/01/2018
INSERT INTO `module` (`module_id`, `module_name`, `module_title`, `module_icon`, `sort`, `active`)
VALUES(17, 'construction', 'Construction', 'fa fa-th', 6, 1);

CREATE TABLE IF NOT EXISTS `con_unit` (
  `con_unit_id` int(11) NOT NULL,
  `con_unit_name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `menu` (`menu_id`, `module_id`, `parent_menu_id`, `caption`, `target`, `sort`, `active`) 
VALUES(156, 17, 0, 'Construction Inventory', '', 1, 1),
(157, 17, 156, 'Unit', 'construction/inventory/manage_unit', 1, 1);

INSERT INTO  `erp`.`menu` (
`menu_id` ,
`module_id` ,
`parent_menu_id` ,
`caption` ,
`target` ,
`sort` ,
`active`
)
VALUES (
NULL ,  '17',  '156',  'Category',  'construction/inventory/manage_category',  '2',  '1'
);

CREATE TABLE IF NOT EXISTS `con_category` (
  `con_category_id` int(11) NOT NULL AUTO_INCREMENT,
  `con_category_name` varchar(255) NOT NULL,
  PRIMARY KEY (`con_category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO  `erp`.`menu` (
`menu_id` ,
`module_id` ,
`parent_menu_id` ,
`caption` ,
`target` ,
`sort` ,
`active`
)
VALUES (
NULL ,  '17',  '156',  'Item Type',  'construction/inventory/manage_item_type',  '3',  '1'
);
    
CREATE TABLE IF NOT EXISTS `con_item_type` (
  `con_item_type_id` int(11) NOT NULL AUTO_INCREMENT,
  `con_item_type_name` varchar(255) NOT NULL,
  PRIMARY KEY (`con_item_type_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1088994 ;

CREATE TABLE IF NOT EXISTS `con_customer` (
  `con_customer_id` int(11) NOT NULL AUTO_INCREMENT,
  `con_customer_code` int(11) NOT NULL,
  `con_customer_comp_name` varchar(255) NOT NULL,
  `con_customer_fname` varchar(255) NOT NULL,
  `con_customer_lname` varchar(255) NOT NULL,
  `con_customer_address` varchar(255) NOT NULL,
  `con_customer_designation` varchar(255) NOT NULL,
  `con_customer_department` varchar(255) NOT NULL,
  `con_customer_mobile1` bigint(20) NOT NULL,
  `con_customer_mobile2` bigint(20) NOT NULL,
  `con_customer_phone` bigint(20) NOT NULL,
  `con_customer_fax` varchar(255) NOT NULL,
  `con_customer_email` varchar(255) NOT NULL,
  `con_customer_sec_email` varchar(255) NOT NULL,
  `con_customer_skype` varchar(255) NOT NULL,
  `con_customer_website` varchar(255) NOT NULL,
  `con_customer_credit_period` varchar(255) NOT NULL,
  `con_customer_credit_limit` varchar(255) NOT NULL,
  `con_customer_res_address` varchar(255) NOT NULL,
  `con_customer_res_street` varchar(255) NOT NULL,
  `con_customer_res_city` varchar(255) NOT NULL,
  `con_customer_res_state` varchar(255) NOT NULL,
  `con_customer_res_zipcode` varchar(255) NOT NULL,
  `con_customer_res_nationality` varchar(255) NOT NULL,
  `con_customer_res_phone` bigint(255) NOT NULL,
  `con_customer_res_DOB` date NOT NULL,
  `con_customer_off_location` varchar(255) NOT NULL,
  `con_customer_off_long` double NOT NULL,
  `con_customer_off_alt` double NOT NULL,
  PRIMARY KEY (`con_customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


INSERT INTO  `erp`.`menu` (
`menu_id` ,
`module_id` ,
`parent_menu_id` ,
`caption` ,
`target` ,
`sort` ,
`active`
)
VALUES (
NULL ,  '17',  '156',  'Customer',  'construction/inventory/manage_customer',  '4',  '1'
);

CREATE TABLE IF NOT EXISTS `con_customer_contact` (
  `con_customer_contact_id` int(11) NOT NULL AUTO_INCREMENT,
  `con_customer_contact_code` varchar(255) NOT NULL,
  `con_customer_contact_name` varchar(255) NOT NULL,
  `con_customer_contact_designation` varchar(255) NOT NULL,
  `con_customer_contact_mobile` bigint(20) NOT NULL,
  `con_customer_contact_phone` bigint(20) NOT NULL,
  `con_customer_contact_email` varchar(255) NOT NULL,
  `con_customer_contact_customer_id` int(11) NOT NULL,
  PRIMARY KEY (`con_customer_contact_id`),
  KEY `con_customer_contact_customer_id` (`con_customer_contact_customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


ALTER TABLE  `con_customer_contact` ADD FOREIGN KEY (  `con_customer_contact_customer_id` ) REFERENCES  `erp`.`con_customer` (
`con_customer_id`
) ON DELETE CASCADE ON UPDATE RESTRICT ;

ALTER TABLE  `con_customer` CHANGE  `con_customer_code`  `con_customer_code` VARCHAR( 200 ) NOT NULL ;

ALTER TABLE  `con_customer` ADD  `con_customer_selutation` VARCHAR( 200 ) NOT NULL

-- 23/01/2018
ALTER TABLE  `con_customer` CHANGE  `con_customer_off_alt`  `con_customer_off_lat` DOUBLE NOT NULL ;

ALTER TABLE  `con_customer` CHANGE  `con_customer_res_nationality`  `con_customer_res_country` VARCHAR( 255 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ;

INSERT INTO  `erp`.`menu` (

`module_id` ,
`parent_menu_id` ,
`caption` ,
`target` ,
`sort` ,
`active`
)
VALUES (
  '17',  '156',  'Project',  'construction/inventory/manage_project',  '5',  '1'
);

CREATE TABLE IF NOT EXISTS `con_project` (
  `con_project_id` int(11) NOT NULL AUTO_INCREMENT,
  `con_project_code` varchar(255) NOT NULL,
  `con_project_name` varchar(255) NOT NULL,
  `con_project_address` varchar(255) NOT NULL,
  `con_project_description` varchar(255) NOT NULL,
  `con_project_exp_s_date` date NOT NULL,
  `con_project_exp_f_date` date NOT NULL,
  `con_project_staus` varchar(255) NOT NULL,
  `con_project_status_reson` varchar(255) NOT NULL,
  `con_project_customer_id` int(11) NOT NULL,
  PRIMARY KEY (`con_project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `con_project_contact_details` (
  `con_project_contact_details_id` int(11) NOT NULL AUTO_INCREMENT,
  `con_project_contact_details_project_id` int(11) NOT NULL,
  `con_project_contact_details_contact_id` int(11) NOT NULL,
  `con_project_contact_details_remarks` varchar(255) NOT NULL,
  PRIMARY KEY (`con_project_contact_details_id`),
  KEY `con_project_contact_details_project_id` (`con_project_contact_details_project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

ALTER TABLE  `con_project_contact_details` ADD FOREIGN KEY (  `con_project_contact_details_project_id` ) REFERENCES  `erp`.`con_project` (
`con_project_id`
) ON DELETE CASCADE ON UPDATE RESTRICT ;

INSERT INTO  `erp`.`prefix` (
`id` ,
`prefix_code` ,
`starting_no` ,
`prefix_name`
)
VALUES (
NULL ,  'PRO',  '100',  'con_project_code'
);

-- 25/01/2018

INSERT INTO  `erp`.`menu` (
`menu_id` ,
`module_id` ,
`parent_menu_id` ,
`caption` ,
`target` ,
`sort` ,
`active`
)
VALUES (
NULL ,  '17',  '156',  'Godown',  'construction/inventrory/manage_godown',  '7',  '1'
);

CREATE TABLE IF NOT EXISTS `con_godown` (
  `con_godown_id` int(11) NOT NULL AUTO_INCREMENT,
  `con_godown_name` varchar(255) NOT NULL,
  PRIMARY KEY (`con_godown_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `con_rack` (
  `con_rack_id` int(11) NOT NULL AUTO_INCREMENT,
  `con_rack_godown_id` int(11) NOT NULL,
  `con_rack_name` varchar(255) NOT NULL,
  PRIMARY KEY (`con_rack_id`),
  KEY `con_rack_godown_id` (`con_rack_godown_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO  `erp`.`menu` (
`menu_id` ,
`module_id` ,
`parent_menu_id` ,
`caption` ,
`target` ,
`sort` ,
`active`
)
VALUES (
NULL ,  '17',  '156',  'Item Master',  'construction/inventory/manage_item_master',  '8',  '1'
);

CREATE TABLE IF NOT EXISTS `con_item_master` (
  `con_item_master_id` int(11) NOT NULL AUTO_INCREMENT,
  `con_item_master_code` varchar(255) NOT NULL,
  `con_item_master_name` varchar(255) NOT NULL,
  `con_item_master_unit_id` int(11) NOT NULL,
  `con_item_master_category_id` int(11) NOT NULL,
  `con_item_master_type_id` int(11) NOT NULL,
  `con_item_master_account` varchar(255) NOT NULL,
  `con_item_master_description` int(11) NOT NULL,
  `con_item_master_tax` int(11) NOT NULL,
  PRIMARY KEY (`con_item_master_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

ALTER TABLE  `detail_entry_dealing_details` ADD  `detail_entry_status` VARCHAR( 255 ) NOT NULL AFTER  `detail_entry_description` ,
ADD  `detail_entry_type` VARCHAR( 255 ) NOT NULL AFTER  `detail_entry_status` ;

-- 27/01/2018

ALTER TABLE  `crm_lead_details` ADD  `crm_lead_ppty_id` INT NULL AFTER  `crm_lead_details_description` ,
ADD  `crm_lead_ppty_room_no` VARCHAR( 255 ) NULL AFTER  `crm_lead_ppty` ;

ALTER TABLE  `prop_service_entry` ADD  `prop_service_entry_status` VARCHAR( 255 ) NULL AFTER  `prop_service_entry_remarks` ;

INSERT INTO  `erp`.`prefix` (
`id` ,
`prefix_code` ,
`starting_no` ,
`prefix_name`
)
VALUES (
NULL ,  'CIM',  '100',  'con_item_master_code'
);

ALTER TABLE  `con_item_master` CHANGE  `con_item_master_description`  `con_item_master_description` TEXT NOT NULL ;

INSERT INTO  `erp`.`menu` (
`menu_id` ,
`module_id` ,
`parent_menu_id` ,
`caption` ,
`target` ,
`sort` ,
`active`
)
VALUES (
NULL ,  '17',  '156',  'Budgeting',  'construction/inventory/manage_budgeting',  '9',  '1'
);

CREATE TABLE IF NOT EXISTS `con_budgeting` (
  `con_budgeting_id` int(11) NOT NULL AUTO_INCREMENT,
  `con_budgeting_date` varchar(255) NOT NULL,
  `con_budgeting_project_id` int(11) NOT NULL,
  `con_budgeting_incharge_id` int(11) NOT NULL,
  `con_budgeting_remarks` text NOT NULL,
  `con_budgeting_allotment_grand_total` int(11) NOT NULL,
  PRIMARY KEY (`con_budgeting_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

ALTER TABLE  `con_budgeting` ADD  `con_budgeting_code` VARCHAR( 255 ) NOT NULL ;

CREATE TABLE IF NOT EXISTS `con_budgeting_allotment` (
  `con_budgeting_allotment_id` int(11) NOT NULL AUTO_INCREMENT,
  `con_budgeting_allotment_budject_id` int(11) NOT NULL,
  `con_budgeting_allotment_item_id` int(11) NOT NULL,
  `con_budgeting_allotment_qty` int(11) NOT NULL,
  `con_budgeting_allotment_price` int(11) NOT NULL,
  `con_budgeting_allotment_tax` int(11) NOT NULL,
  `con_budgeting_allotment_tax_amt` int(11) NOT NULL,
  `con_budgeting_allotment_total` int(11) NOT NULL,
  PRIMARY KEY (`con_budgeting_allotment_id`),
  KEY `con_budgeting_allotment_budject_id` (`con_budgeting_allotment_budject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- 29/01/2018
ALTER TABLE  `con_budgeting` CHANGE  `con_budgeting_allotment_grand_total`  `con_budgeting_allotment_grand_total` FLOAT NOT NULL ;

ALTER TABLE  `con_budgeting_allotment` CHANGE  `con_budgeting_allotment_price`  `con_budgeting_allotment_price` FLOAT NOT NULL ;

ALTER TABLE  `con_budgeting_allotment` CHANGE  `con_budgeting_allotment_tax`  `con_budgeting_allotment_tax` FLOAT NOT NULL ;

ALTER TABLE  `con_budgeting_allotment` CHANGE  `con_budgeting_allotment_tax_amt`  `con_budgeting_allotment_tax_amt` FLOAT( 11 ) NOT NULL ;

ALTER TABLE  `con_budgeting_allotment` CHANGE  `con_budgeting_allotment_total`  `con_budgeting_allotment_total` FLOAT NOT NULL ;

-- 31/01/2018

CREATE TABLE IF NOT EXISTS `item_master` (
  `item_master_id` int(11) NOT NULL AUTO_INCREMENT,
  `item_master_code` varchar(255) NOT NULL,
  `item_master_name` varchar(255) NOT NULL,
  `item_master_unit_id` int(11) NOT NULL,
  `item_master_category_id` int(11) NOT NULL,
  `item_master_type_id` int(11) DEFAULT NULL,
  `item_master_account` varchar(255) DEFAULT NULL,
  `item_master_description` text,
  `item_master_tax` int(11) DEFAULT NULL,
  `item_parchasing_rate` double DEFAULT NULL,
  PRIMARY KEY (`item_master_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1089684 ;

-- 15/02/2018

CREATE TABLE IF NOT EXISTS `pos_expense_type` (
  `pos_expense_type_id` int(11) NOT NULL AUTO_INCREMENT,
  `pos_expense_type_name` varchar(255) NOT NULL,
  PRIMARY KEY (`pos_expense_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `pos_stock_unit` (
  `pos_stock_unit_id` int(11) NOT NULL AUTO_INCREMENT,
  `pos_stock_unit_name` varchar(255) NOT NULL,
  PRIMARY KEY (`pos_stock_unit_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=523 ;

INSERT INTO `menu` (`menu_id`, `module_id`, `parent_menu_id`, `caption`, `target`, `sort`, `active`, `project_id`) 
VALUES (182, 19, 176, 'Stock Unit', 'pos/master/manage_stock_unit', 5, 1, '4');


INSERT INTO  `erp`.`menu` (
`menu_id` ,
`module_id` ,
`parent_menu_id` ,
`caption` ,
`target` ,
`sort` ,
`active` ,
`project_id`
)
VALUES (
NULL ,  '19',  '176',  'Expense Type',  'pos/master/manage_expense_type',  '6',  '1',  '4'
);

INSERT INTO  `erp`.`menu` (
`menu_id` ,
`module_id` ,
`parent_menu_id` ,
`caption` ,
`target` ,
`sort` ,
`active` ,
`project_id`
)
VALUES (
NULL ,  '19',  '176',  'Item',  'pos/master/Item',  '7',  '1',  '4'
);

-- 17/02/2018
INSERT INTO  `erp`.`menu` (
`menu_id` ,
`module_id` ,
`parent_menu_id` ,
`caption` ,
`target` ,
`sort` ,
`active` ,
`project_id`
)
VALUES (
NULL ,  '19',  '187',  'Purchase Order',  'pos/purchase/manage_purchase_order',  '2',  '1',  '4'
);

-- 24/02/2018

INSERT INTO `erp`.`menu` (
`menu_id` ,
`module_id` ,
`parent_menu_id` ,
`caption` ,
`target` ,
`sort` ,
`active` ,
`project_id`
)
VALUES (
NULL , '19', '176', 'Godown', 'pos/master/manage_godown', '9', '1', '4'
);

INSERT INTO `erp`.`prefix` (
`id` ,
`prefix_code` ,
`starting_no` ,
`prefix_name`
)
VALUES (
NULL , 'purchase_no', '100', 'RCPT'
);

-- 26/02/2018
INSERT INTO `erp`.`menu` (
`menu_id` ,
`module_id` ,
`parent_menu_id` ,
`caption` ,
`target` ,
`sort` ,
`active` ,
`project_id`
)
VALUES (
NULL , '17', '201', 'Tools Category', 'construction/rented_machinery/tools_category', '1', '1', '3'
);
UPDATE `erp`.`menu` SET `target` = 'construction/rented/tools_category' WHERE `menu`.`menu_id` =202;
UPDATE `erp`.`menu` SET `target` = 'construction/rented/manage_tools_category' WHERE `menu`.`menu_id` =202;
10:26 AM

CREATE TABLE IF NOT EXISTS `tools_category` (
 `tools_category_id` int(11) NOT NULL DEFAULT '0',
 `tools_category_name` varchar(255) NOT NULL,
 PRIMARY KEY (`tools_category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `tools` (
 `tools_id` int(11) NOT NULL AUTO_INCREMENT,
 `tools_code` varchar(250) NOT NULL,
 `tools_name` varchar(250) NOT NULL,
 `tools_vendor_id` int(11) NOT NULL,
 `tools_description` text NOT NULL,
 `tools_category_id` int(11) NOT NULL,
 PRIMARY KEY (`tools_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
15 mins


INSERT INTO  `erp`.`menu` (
`menu_id` ,
`module_id` ,
`parent_menu_id` ,
`caption` ,
`target` ,
`sort` ,
`active` ,
`project_id`
)
VALUES (
NULL ,  '17',  '201',  'Tools',  'construction/rented/manage_tools',  '2',  '1',  '3'
);

INSERT INTO  `erp`.`prefix` (
`id` ,
`prefix_code` ,
`starting_no` ,
`prefix_name`
)
VALUES (
NULL ,  'RTOL',  '100',  'tools_code'
);

CREATE TABLE IF NOT EXISTS `tools_vendor` (
  `tools_vendor_id` int(11) NOT NULL,
  `tools_vendor_tool_id` int(11) NOT NULL,
  `tools_vendor_name` varchar(255) NOT NULL,
  KEY `tools_vendor_tool_id` (`tools_vendor_tool_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `tools` (
  `tools_id` int(11) NOT NULL AUTO_INCREMENT,
  `tools_code` varchar(250) NOT NULL,
  `tools_name` varchar(250) NOT NULL,
  `tools_description` text NOT NULL,
  `tools_category_id` int(11) NOT NULL,
  PRIMARY KEY (`tools_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1469 ;

-- 09/03/2018

CREATE TABLE IF NOT EXISTS `tool_allocation_employee` (
  `allocation_id` int(11) NOT NULL AUTO_INCREMENT,
  `allocation_code` varchar(255) NOT NULL,
  `allocation_date` date NOT NULL,
  `project_id` int(11) NOT NULL,
  `in_charge` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  PRIMARY KEY (`allocation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `tool_allocation_employee_request` (
  `tool_request_id` int(11) NOT NULL AUTO_INCREMENT,
  `allocation_id` int(11) NOT NULL,
  `request_id` int(11) NOT NULL,
  PRIMARY KEY (`tool_request_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `tool_allocation_employee_details` (
  `tool_allocation_details_id` int(11) NOT NULL AUTO_INCREMENT,
  `tool_allocation_id` int(11) NOT NULL,
  `tool_id` int(11) NOT NULL,
  `requested_qty` int(11) NOT NULL,
  `allocated_qty` int(11) NOT NULL,
  `expected_return_date` date NOT NULL,
  `note` text NOT NULL,
  `remarks_in_request` text NOT NULL,
  PRIMARY KEY (`tool_allocation_details_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO `menu` (`menu_id`, `module_id`, `parent_menu_id`, `caption`, `target`, `sort`, `active`, `project_id`) VALUES
(225, 17, 168, 'Expense', 'construction/management/manage_expense', 6, 1, '3'),
(229, 17, 201, 'Tools allocation to Employee', 'construction/rented/manage_tool_allocation_employee', 7, 1, '3');

-- 10/03/2018

ALTER TABLE `tool_allocation_employee_details` ADD INDEX ( `tool_allocation_id` ) ;
ALTER TABLE `tool_allocation_employee_request` ADD INDEX ( `allocation_id` ) ;
ALTER TABLE `tool_allocation_employee_request` ADD FOREIGN KEY ( `allocation_id` ) REFERENCES `erp`.`tool_allocation_employee` (
`allocation_id`
) ON DELETE CASCADE ON UPDATE RESTRICT ;
ALTER TABLE `tool_allocation_employee_details` ADD FOREIGN KEY ( `tool_allocation_id` ) REFERENCES `erp`.`tool_allocation_employee` (
`allocation_id`
) ON DELETE CASCADE ON UPDATE RESTRICT ;

-- 13/03/2018
ALTER TABLE  `con_rented_tool_allocation_details` ADD INDEX (  `rented_tool_allocation_id` ) ;
ALTER TABLE  `con_rented_tool_allocation_details` ADD FOREIGN KEY (  `rented_tool_allocation_id` ) REFERENCES `erp`.`con_rented_tool_allocation` (
`rented_tool_allocation_id`
) ON DELETE CASCADE ON UPDATE RESTRICT ;



ALTER TABLE  `con_tool_request_details` ADD INDEX (  `tool_request_id` ) ;
ALTER TABLE  `con_tool_request_details` ADD FOREIGN KEY (  `tool_request_id` ) REFERENCES  `erp`.`con_tool_request` (
`tool_request_id`
) ON DELETE CASCADE ON UPDATE RESTRICT ;

CREATE TABLE IF NOT EXISTS `tool_stock` (
  `stock_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `qty` int(11) NOT NULL,
  `godown_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `menu` (`menu_id`, `module_id`, `parent_menu_id`, `caption`, `target`, `sort`, `active`, `project_id`) VALUES
(233, 17, 201, 'Tool Return From Project', 'construction/rented/manage_tool_return_project', 9, 1, '3');

CREATE TABLE IF NOT EXISTS `tool_return_project` (
  `return_id` int(11) NOT NULL AUTO_INCREMENT,
  `return_code` varchar(255) NOT NULL,
  `return_date` date NOT NULL,
  `project_id` int(11) NOT NULL,
  `incharge` int(11) NOT NULL,
  `allocation_id` int(11) NOT NULL,
  PRIMARY KEY (`return_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2205 ;

CREATE TABLE IF NOT EXISTS `tool_return_project_details` (
  `return_id` int(11) NOT NULL AUTO_INCREMENT,
  `return_project_id` int(11) NOT NULL,
  `tool_id` int(11) NOT NULL,
  `allocation_details_id` int(11) NOT NULL,
  `allocation_qty` int(11) NOT NULL,
  `from_godown` int(11) NOT NULL,
  `to_godown` int(11) NOT NULL,
  `returned_qty` int(11) NOT NULL,
  `note` text NOT NULL,
  PRIMARY KEY (`return_id`),
  KEY `return_project_id` (`return_project_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2209 ;

ALTER TABLE `tool_return_project_details`
  ADD CONSTRAINT `tool_return_project_details_ibfk_1` FOREIGN KEY (`return_project_id`) REFERENCES `tool_return_project` (`return_id`) ON DELETE CASCADE;