-- phpMyAdmin SQL Dump -- version 4.8.3 -- https://www.phpmyadmin.net/ -- -- Host: localhost:3306 -- Generation Time: Jun 26, 2019 at 12:21 AM -- Server version: 5.6.43-cll-lve -- PHP Version: 7.2.7 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET AUTOCOMMIT = 0; START TRANSACTION; SET time_zone = "+00:00"; -- -- Database: `pcm_offbit` -- -- -------------------------------------------------------- -- -- Table structure for table `pcm_brands` -- CREATE TABLE `pcm_brands` ( `brand_id` int(11) NOT NULL, `client_id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `description` longtext NOT NULL, `deletestatus` enum('N','Y') NOT NULL, `status` enum('Y','N') NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `pcm_categories` -- CREATE TABLE `pcm_categories` ( `category_id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `description` longtext NOT NULL, `deletestatus` enum('N','Y') NOT NULL, `status` enum('Y','N') NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `pcm_clients` -- CREATE TABLE `pcm_clients` ( `client_id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `mobile` varchar(255) NOT NULL, `email` varchar(255) NOT NULL, `address` longtext NOT NULL, `city` varchar(255) NOT NULL, `state` varchar(255) NOT NULL, `zip` varchar(255) NOT NULL, `deletestatus` enum('N','Y') NOT NULL, `status` enum('Y','N') NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `pcm_estimates` -- CREATE TABLE `pcm_estimates` ( `estimate_id` int(11) NOT NULL, `project_id` int(11) NOT NULL, `category_id` int(11) NOT NULL, `subcategory_id` int(11) NOT NULL, `estimateamount` float NOT NULL, `limitamount` float NOT NULL, `deletestatus` enum('N','Y') NOT NULL, `status` enum('Y','N') NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `pcm_funcalloweds` -- CREATE TABLE `pcm_funcalloweds` ( `funcallowed_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `modfunc_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `pcm_invoices` -- CREATE TABLE `pcm_invoices` ( `invoice_id` int(11) NOT NULL, `project_id` int(11) NOT NULL, `category_id` int(11) NOT NULL, `subcategory_id` int(11) NOT NULL, `baseamount` float NOT NULL, `tax` float NOT NULL, `totalamount` float NOT NULL, `invoicetype` varchar(255) NOT NULL, `invoiceno` int(11) NOT NULL, `invoicecopy` varchar(255) NOT NULL, `login_id` int(11) NOT NULL, `invoicedate` date NOT NULL, `createddate` date NOT NULL, `deletestatus` enum('N','Y') NOT NULL, `status` enum('Y','N') NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `pcm_jobtypes` -- CREATE TABLE `pcm_jobtypes` ( `jobtype_id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `deletestatus` enum('N','Y') NOT NULL, `status` enum('Y','N') NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `pcm_logins` -- CREATE TABLE `pcm_logins` ( `login_id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `designation` varchar(255) NOT NULL, `mobile` varchar(255) NOT NULL, `username` varchar(255) NOT NULL, `password` varchar(255) NOT NULL, `usertype` varchar(255) NOT NULL, `deletestatus` enum('N','Y') NOT NULL, `status` enum('Y','N') NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `pcm_modfuncs` -- CREATE TABLE `pcm_modfuncs` ( `modfunc_id` int(11) NOT NULL, `modulename` varchar(255) NOT NULL, `controllername` varchar(255) NOT NULL, `functionname` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `pcm_projects` -- CREATE TABLE `pcm_projects` ( `project_id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `estimateno` varchar(255) NOT NULL, `pono` varchar(255) NOT NULL, `podate` datetime NOT NULL, `eventstartdate` datetime NOT NULL, `eventenddate` datetime NOT NULL, `eventlocation` varchar(255) NOT NULL, `client_id` int(11) NOT NULL, `brand_id` int(11) NOT NULL, `jobcode` varchar(255) NOT NULL, `zone_id` int(11) NOT NULL, `createddate` datetime NOT NULL, `deletestatus` enum('N','Y') NOT NULL, `status` enum('Y','N') NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `pcm_projectusers` -- CREATE TABLE `pcm_projectusers` ( `projectuser_id` int(11) NOT NULL, `project_id` int(11) NOT NULL, `login_id` int(11) NOT NULL, `usertype` varchar(255) NOT NULL, `deletestatus` enum('N','Y') NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `pcm_subcategories` -- CREATE TABLE `pcm_subcategories` ( `subcategory_id` int(11) NOT NULL, `category_id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `description` longtext NOT NULL, `deletestatus` enum('N','Y') NOT NULL, `status` enum('Y','N') NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `pcm_teams` -- CREATE TABLE `pcm_teams` ( `team_id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `designation` varchar(255) NOT NULL, `email` varchar(255) NOT NULL, `mobile` varchar(255) NOT NULL, `address` varchar(255) NOT NULL, `deletestatus` enum('N','Y') NOT NULL, `status` enum('Y','N') NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `pcm_zones` -- CREATE TABLE `pcm_zones` ( `zone_id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `deletestatus` enum('N','Y') NOT NULL, `status` enum('Y','N') NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Indexes for dumped tables -- -- -- Indexes for table `pcm_brands` -- ALTER TABLE `pcm_brands` ADD PRIMARY KEY (`brand_id`); -- -- Indexes for table `pcm_categories` -- ALTER TABLE `pcm_categories` ADD PRIMARY KEY (`category_id`); -- -- Indexes for table `pcm_clients` -- ALTER TABLE `pcm_clients` ADD PRIMARY KEY (`client_id`); -- -- Indexes for table `pcm_estimates` -- ALTER TABLE `pcm_estimates` ADD PRIMARY KEY (`estimate_id`); -- -- Indexes for table `pcm_funcalloweds` -- ALTER TABLE `pcm_funcalloweds` ADD PRIMARY KEY (`funcallowed_id`); -- -- Indexes for table `pcm_invoices` -- ALTER TABLE `pcm_invoices` ADD PRIMARY KEY (`invoice_id`); -- -- Indexes for table `pcm_jobtypes` -- ALTER TABLE `pcm_jobtypes` ADD PRIMARY KEY (`jobtype_id`); -- -- Indexes for table `pcm_logins` -- ALTER TABLE `pcm_logins` ADD PRIMARY KEY (`login_id`); -- -- Indexes for table `pcm_modfuncs` -- ALTER TABLE `pcm_modfuncs` ADD PRIMARY KEY (`modfunc_id`); -- -- Indexes for table `pcm_projects` -- ALTER TABLE `pcm_projects` ADD PRIMARY KEY (`project_id`); -- -- Indexes for table `pcm_projectusers` -- ALTER TABLE `pcm_projectusers` ADD PRIMARY KEY (`projectuser_id`); -- -- Indexes for table `pcm_subcategories` -- ALTER TABLE `pcm_subcategories` ADD PRIMARY KEY (`subcategory_id`); -- -- Indexes for table `pcm_teams` -- ALTER TABLE `pcm_teams` ADD PRIMARY KEY (`team_id`); -- -- Indexes for table `pcm_zones` -- ALTER TABLE `pcm_zones` ADD PRIMARY KEY (`zone_id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `pcm_brands` -- ALTER TABLE `pcm_brands` MODIFY `brand_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `pcm_categories` -- ALTER TABLE `pcm_categories` MODIFY `category_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `pcm_clients` -- ALTER TABLE `pcm_clients` MODIFY `client_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `pcm_estimates` -- ALTER TABLE `pcm_estimates` MODIFY `estimate_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `pcm_funcalloweds` -- ALTER TABLE `pcm_funcalloweds` MODIFY `funcallowed_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `pcm_invoices` -- ALTER TABLE `pcm_invoices` MODIFY `invoice_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `pcm_jobtypes` -- ALTER TABLE `pcm_jobtypes` MODIFY `jobtype_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `pcm_logins` -- ALTER TABLE `pcm_logins` MODIFY `login_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `pcm_modfuncs` -- ALTER TABLE `pcm_modfuncs` MODIFY `modfunc_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `pcm_projects` -- ALTER TABLE `pcm_projects` MODIFY `project_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `pcm_projectusers` -- ALTER TABLE `pcm_projectusers` MODIFY `projectuser_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `pcm_subcategories` -- ALTER TABLE `pcm_subcategories` MODIFY `subcategory_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `pcm_teams` -- ALTER TABLE `pcm_teams` MODIFY `team_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `pcm_zones` -- ALTER TABLE `pcm_zones` MODIFY `zone_id` int(11) NOT NULL AUTO_INCREMENT; COMMIT; CREATE TABLE `pcm_taxes` ( `tax_id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `deletestatus` enum('N','Y') NOT NULL, `status` enum('Y','N') NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Indexes for dumped tables -- -- -- Indexes for table `pcm_taxes` -- ALTER TABLE `pcm_taxes` ADD PRIMARY KEY (`tax_id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `pcm_taxes` -- ALTER TABLE `pcm_taxes` MODIFY `tax_id` int(11) NOT NULL AUTO_INCREMENT; ALTER TABLE `pcm_taxes` CHANGE `name` `tax` FLOAT NOT NULL; ALTER TABLE `pcm_projects` ADD `year` INT NOT NULL AFTER `estimateno`; ALTER TABLE `pcm_projects` CHANGE `createddate` `createddate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP; ALTER TABLE `pcm_projects` ADD `yearcode` INT NOT NULL AFTER `year`; -- -- 27_06_2019 -- ALTER TABLE `pcm_projects` ADD `jobtype` VARCHAR(255) NOT NULL AFTER `jobcode`; ALTER TABLE `pcm_projects` CHANGE `jobtype` `jobtype_id` INT NOT NULL; -- -- 28_06_2019 -- ALTER TABLE `pcm_clients` ADD `gstno` VARCHAR(255) NOT NULL AFTER `name`; ALTER TABLE `pcm_invoices` ADD `taxpercentage` FLOAT NOT NULL AFTER `baseamount`; -- -- 02_07_2019 -- CREATE TABLE `pcm_projectestimates` ( `projectestimate_id` int(11) NOT NULL, `projectname` varchar(255) NOT NULL, `fees` float NOT NULL, `gst` float NOT NULL, `userid` int(11) NOT NULL, `usertype` varchar(255) NOT NULL, `createddate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `deletestatus` enum('N','Y') NOT NULL, `status` enum('Y','N') NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Indexes for dumped tables -- -- -- Indexes for table `pcm_projectestimates` -- ALTER TABLE `pcm_projectestimates` ADD PRIMARY KEY (`projectestimate_id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `pcm_projectestimates` -- ALTER TABLE `pcm_projectestimates` MODIFY `projectestimate_id` int(11) NOT NULL AUTO_INCREMENT; CREATE TABLE `pcm_estimateheads` ( `estimatehead_id` int(11) NOT NULL, `projectestimate_id` int(11) NOT NULL, `category_id` int(11) NOT NULL, `subcategory_id` int(11) NOT NULL, `noofmeets` int(11) NOT NULL, `noofdays` int(11) NOT NULL, `noofdealers` int(11) NOT NULL, `noofunits` int(11) NOT NULL, `unitcost` float NOT NULL, `totalcost` float NOT NULL, `remarks` varchar(255) NOT NULL, `order_id` int(11) NOT NULL, `deletestatus` enum('N','Y') NOT NULL, `status` enum('Y','N') NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Indexes for dumped tables -- -- -- Indexes for table `pcm_estimateheads` -- ALTER TABLE `pcm_estimateheads` ADD PRIMARY KEY (`estimatehead_id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `pcm_estimateheads` -- ALTER TABLE `pcm_estimateheads` MODIFY `estimatehead_id` int(11) NOT NULL AUTO_INCREMENT; ALTER TABLE `pcm_projectestimates` ADD `estimateno` VARCHAR(255) NOT NULL AFTER `projectname`; ALTER TABLE `pcm_projectestimates` ADD `year` INT NOT NULL AFTER `estimateno`; ALTER TABLE `pcm_projectestimates` ADD `yearcode` INT NOT NULL AFTER `year`; *******************************************************