CREATE TABLE `labour` ( `lid` int(50) NOT NULL, `name` varchar(100) NOT NULL, `address` varchar(250) NOT NULL, `email` varchar(100) NOT NULL, `phone` varchar(20) NOT NULL, `doj` date NOT NULL, `joballocation` varchar(100) NOT NULL, `remarks` varchar(250) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ALTER TABLE `labour` ADD PRIMARY KEY (`lid`); ALTER TABLE `labour` MODIFY `lid` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1; CREATE TABLE `suppliers` ( `sid` int(50) NOT NULL, `name` varchar(100) NOT NULL, `address` varchar(250) NOT NULL, `email` varchar(100) NOT NULL, `phone` varchar(20) NOT NULL, `dofsupply` date NOT NULL, `projectallocation` varchar(100) NOT NULL, `remarks` varchar(250) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ALTER TABLE `suppliers` ADD PRIMARY KEY (`sid`); ALTER TABLE `suppliers` MODIFY `sid` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1; CREATE TABLE `agent` ( `aid` int(50) NOT NULL, `name` varchar(100) NOT NULL, `address` varchar(250) NOT NULL, `email` varchar(100) NOT NULL, `phone` varchar(20) NOT NULL, `doj` date NOT NULL, `projectallocation` varchar(100) NOT NULL, `remarks` varchar(250) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ALTER TABLE `agent` ADD PRIMARY KEY (`aid`); ALTER TABLE `agent` MODIFY `aid` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1; CREATE TABLE `materials` ( `mtid` int(50) NOT NULL, `sid` int(50) NOT NULL, `name` varchar(100) NOT NULL, `qty` varchar(100) NOT NULL, `rate` varchar(20) NOT NULL, `amount` varchar(20) NOT NULL, `projectallocation` varchar(100) NOT NULL, `remarks` varchar(250) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ALTER TABLE `materials` ADD PRIMARY KEY (`mtid`); ALTER TABLE `materials` MODIFY `mtid` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1; CREATE TABLE `labourcharges` ( `lcid` int(50) NOT NULL, `lid` int(50) NOT NULL, `date` date NOT NULL, `rate` varchar(100) NOT NULL, `amount` varchar(100) NOT NULL, `workdetails` varchar(100) NOT NULL, `remarks` varchar(250) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ALTER TABLE `labourcharges` ADD PRIMARY KEY (`lcid`); ALTER TABLE `labourcharges` MODIFY `lcid` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1; CREATE TABLE `commissioncharges` ( `coid` int(50) NOT NULL, `aid` int(50) NOT NULL, `date` date NOT NULL, `rate` varchar(100) NOT NULL, `amount` varchar(100) NOT NULL, `workdetails` varchar(100) NOT NULL, `remarks` varchar(250) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ALTER TABLE `commissioncharges` ADD PRIMARY KEY (`coid`); ALTER TABLE `commissioncharges` MODIFY `coid` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1; CREATE TABLE `labourpay` ( `lpid` int(50) NOT NULL, `lcid` int(50) NOT NULL, `date` date NOT NULL, `amount` varchar(100) NOT NULL, `workdetails` varchar(100) NOT NULL, `remarks` varchar(250) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ALTER TABLE `labourpay` ADD PRIMARY KEY (`lpid`); ALTER TABLE `labourpay` MODIFY `lpid` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1; CREATE TABLE `commissionpay` ( `copid` int(50) NOT NULL, `coid` int(50) NOT NULL, `date` date NOT NULL, `amount` varchar(100) NOT NULL, `workdetails` varchar(100) NOT NULL, `remarks` varchar(250) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ALTER TABLE `commissionpay` ADD PRIMARY KEY (`copid`); ALTER TABLE `commissionpay` MODIFY `copid` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1; ALTER TABLE `materials` ADD `unit` VARCHAR(20) NOT NULL AFTER `qty`; CREATE TABLE `materialpurchase` ( `mpid` int(50) NOT NULL, `mtid` int(50) NOT NULL, `date` date NOT NULL, `qty` varchar(100) NOT NULL, `unit` varchar(20) NOT NULL, `rate` varchar(100) NOT NULL, `amount` varchar(100) NOT NULL, `workdetails` varchar(100) NOT NULL, `remarks` varchar(250) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ALTER TABLE `materialpurchase` ADD PRIMARY KEY (`mpid`); ALTER TABLE `materialpurchase` MODIFY `mpid` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1; CREATE TABLE `materialissue` ( `isuid` int(50) NOT NULL, `mtid` int(50) NOT NULL, `date` date NOT NULL, `qty` varchar(100) NOT NULL, `unit` varchar(20) NOT NULL, `rate` varchar(100) NOT NULL, `amount` varchar(100) NOT NULL, `workdetails` varchar(100) NOT NULL, `remarks` varchar(250) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ALTER TABLE `materialissue` ADD PRIMARY KEY (`isuid`); ALTER TABLE `materialissue` MODIFY `isuid` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1; CREATE TABLE `expenses_type` ( `exid` int(50) NOT NULL, `expenses` varchar(100) NOT NULL, `remarks` varchar(250) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ALTER TABLE `expenses_type` ADD PRIMARY KEY (`exid`); ALTER TABLE `expenses_type` MODIFY `exid` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1; CREATE TABLE `expenses` ( `expid` int(50) NOT NULL, `exid` int(50) NOT NULL, `amount` varchar(100) NOT NULL, `remarks` varchar(250) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ALTER TABLE `expenses` ADD PRIMARY KEY (`expid`); ALTER TABLE `expenses` MODIFY `expid` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1; ALTER TABLE `expenses` ADD `date` DATE NOT NULL AFTER `exid`; CREATE TABLE `property_sale` ( `pid` int(50) NOT NULL, `date` date NOT NULL, `customer` varchar(100) NOT NULL, `address` varchar(250) NOT NULL, `phone` varchar(100) NOT NULL, `email` varchar(100) NOT NULL, `property` varchar(100) NOT NULL, `price` varchar(100) NOT NULL, `adv` varchar(100) NOT NULL, `propdetails` varchar(250) NOT NULL, `remarks` varchar(250) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ALTER TABLE `property_sale` ADD PRIMARY KEY (`pid`); ALTER TABLE `property_sale` MODIFY `pid` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1; CREATE TABLE `property_pay` ( `payid` int(50) NOT NULL, `pid` int(50) NOT NULL, `date` date NOT NULL, `amount` varchar(100) NOT NULL, `remarks` varchar(250) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ALTER TABLE `property_pay` ADD PRIMARY KEY (`payid`); ALTER TABLE `property_pay` MODIFY `payid` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1; ALTER TABLE `property_sale` ADD `location` VARCHAR(100) NOT NULL AFTER `email`; INSERT INTO `admin` (`aid`, `auser`, `aemail`, `apass`, `adob`, `aphone`) VALUES (NULL, 'Soumen', 'admin@saraswatihousingco.com', 'b81f5731297153a124db417602365b45bd8021d0', '2025-11-17', '98361 33000'); CREATE TABLE `rent` ( `reid` int(50) NOT NULL, `name` varchar(100) NOT NULL, `address` varchar(250) NOT NULL, `email` varchar(100) NOT NULL, `phone` varchar(20) NOT NULL, `date` date NOT NULL, `project_no` varchar(50) NOT NULL, `flat_no` varchar(50) NOT NULL, `remarks` varchar(250) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ALTER TABLE `rent` ADD PRIMARY KEY (`reid`); ALTER TABLE `rent` MODIFY `reid` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1; ALTER TABLE `rent` ADD `amount` VARCHAR(50) NOT NULL AFTER `flat_no`; CREATE TABLE `advanced` ( `adid` int(50) NOT NULL, `name` varchar(100) NOT NULL, `address` varchar(250) NOT NULL, `email` varchar(100) NOT NULL, `phone` varchar(20) NOT NULL, `date` date NOT NULL, `project_no` varchar(50) NOT NULL, `flat_no` varchar(50) NOT NULL, `flat_value` varchar(50) NOT NULL, `remarks` varchar(250) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ALTER TABLE `advanced` ADD PRIMARY KEY (`adid`); ALTER TABLE `advanced` MODIFY `adid` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1; CREATE TABLE `land` ( `proid` int(50) NOT NULL, `project_no` varchar(50) NOT NULL, `name` varchar(100) NOT NULL, `address` varchar(250) NOT NULL, `location` varchar(100) NOT NULL, `date` date NOT NULL, `no_of_flat` varchar(50) NOT NULL, `property_value` varchar(50) NOT NULL, `remarks` varchar(250) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ALTER TABLE `land` ADD PRIMARY KEY (`proid`); ALTER TABLE `land` MODIFY `proid` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1; CREATE TABLE `flat_master` ( `flid` int(50) NOT NULL, `flat_no` varchar(50) NOT NULL, `project_no` varchar(50) NOT NULL, `flat_value` varchar(50) NOT NULL, `remarks` varchar(250) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ALTER TABLE `flat_master` ADD PRIMARY KEY (`flid`); ALTER TABLE `flat_master` MODIFY `flid` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1; ALTER TABLE `land` CHANGE `location` `email` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL; ALTER TABLE `land` ADD `phone` VARCHAR(50) NOT NULL AFTER `email`; ALTER TABLE `land` DROP COLUMN `property_value`; CREATE TABLE `group_type` ( `gid` int(50) NOT NULL, `expenses` varchar(100) NOT NULL, `page_url` varchar(100) NOT NULL, `remarks` varchar(250) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ALTER TABLE `group_type` ADD PRIMARY KEY (`gid`); ALTER TABLE `group_type` MODIFY `gid` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1; INSERT INTO `group_type` (`gid`, `expenses`, `page_url`, `remarks`) VALUES (NULL, 'RENT PAID', 'rent.php', ''), (NULL, 'ADVANCE PAID', 'advance.php', ''); INSERT INTO `group_type` (`gid`, `expenses`, `page_url`, `remarks`) VALUES (NULL, 'ADVANCE RECEIVE', 'advance_receive.php', ''), (NULL, 'LAND/PROPERTY', 'land.php', ''); INSERT INTO `group_type` (`gid`, `expenses`, `page_url`, `remarks`) VALUES (NULL, 'FLAT LISTS', 'flat.php', ''), (NULL, 'LABOUR LISTS', 'labour.php', ''), (NULL, 'SUPPLIERS LISTS', 'suppliers.php', ''), (NULL, 'METIRIALS LISTS', 'materials.php', ''), (NULL, 'COMMISSION LISTS', 'agent.php', ''); INSERT INTO `group_type` (`gid`, `expenses`, `page_url`, `remarks`) VALUES (NULL, 'PROJECT LISTS', 'project.php', ''); CREATE TABLE `project_master` ( `projid` int(50) NOT NULL, `proj_no` varchar(50) NOT NULL, `project_name` varchar(150) NOT NULL, `project_value` varchar(50) NOT NULL, `remarks` varchar(250) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ALTER TABLE `project_master` ADD PRIMARY KEY (`projid`); ALTER TABLE `project_master` MODIFY `projid` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1; CREATE TABLE `advance_receive` ( `adrid` int(50) NOT NULL, `name` varchar(100) NOT NULL, `address` varchar(250) NOT NULL, `email` varchar(100) NOT NULL, `phone` varchar(20) NOT NULL, `date` date NOT NULL, `project_no` varchar(50) NOT NULL, `flat_no` varchar(50) NOT NULL, `flat_value` varchar(50) NOT NULL, `remarks` varchar(250) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ALTER TABLE `advance_receive` ADD PRIMARY KEY (`adrid`); ALTER TABLE `advance_receive` MODIFY `adrid` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1; ALTER TABLE `advanced` CHANGE `flat_no` `chq_no` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL; ALTER TABLE `land` ADD `property_value` VARCHAR(100) NOT NULL AFTER `project_no`; ALTER TABLE `project_master` CHANGE `project_name` `name` VARCHAR(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL; ALTER TABLE `suppliers` CHANGE `projectallocation` `project_no` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL; ALTER TABLE `materials` CHANGE `projectallocation` `project_no` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL; ALTER TABLE `agent` CHANGE `projectallocation` `project_no` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL; ALTER TABLE `materialissue` CHANGE `workdetails` `project_no` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL; ALTER TABLE `materialpurchase` CHANGE `workdetails` `project_no` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL; ALTER TABLE `property_sale` CHANGE `propdetails` `project_no` VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL; ALTER TABLE `labour` CHANGE `joballocation` `project_no` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL; ALTER TABLE `project_master` ADD `no_of_flat` VARCHAR(50) NOT NULL AFTER `proj_no`; ALTER TABLE `advance_receive` ADD `gst` VARCHAR(50) NOT NULL AFTER `flat_value`, ADD `total` VARCHAR(50) NOT NULL AFTER `gst`; ALTER TABLE `advanced` CHANGE `chq_no` `flat_no` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL; INSERT INTO `group_type` (`gid`, `expenses`, `page_url`, `remarks`) VALUES (NULL, 'EXPENSES TYPE MASTER', 'expenses_type.php', ''); ALTER TABLE `rent` ADD `flat_id` VARCHAR(50) NOT NULL AFTER `flat_no`; CREATE TABLE `bank_type` ( `bkid` int(50) NOT NULL, `bank_name` varchar(100) NOT NULL, `bank_branch` varchar(100) NOT NULL, `bank_address` varchar(300) NOT NULL, `ifsc` varchar(100) NOT NULL, `account_type` varchar(100) NOT NULL, `remarks` varchar(250) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ALTER TABLE `bank_type` ADD PRIMARY KEY (`bkid`); ALTER TABLE `bank_type` MODIFY `bkid` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1; INSERT INTO `group_type` (`gid`, `expenses`, `page_url`, `remarks`) VALUES (NULL, 'BANK ACCOUNT MASTER', 'bank_type.php', ''); CREATE TABLE `rentpay` ( `rpid` int(50) NOT NULL, `reid` int(50) NOT NULL, `date` date NOT NULL, `amount` varchar(100) NOT NULL, `bank_name` varchar(100) NOT NULL, `cheque_no` varchar(100) NOT NULL, `remarks` varchar(250) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ALTER TABLE `rentpay` ADD PRIMARY KEY (`rpid`); ALTER TABLE `rentpay` MODIFY `rpid` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1; CREATE TABLE `advancedpay` ( `apid` int(50) NOT NULL, `adid` int(50) NOT NULL, `date` date NOT NULL, `amount` varchar(100) NOT NULL, `bank_name` varchar(100) NOT NULL, `cheque_no` varchar(100) NOT NULL, `remarks` varchar(250) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ALTER TABLE `advancedpay` ADD PRIMARY KEY (`apid`); ALTER TABLE `advancedpay` MODIFY `apid` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1; CREATE TABLE `advreceived` ( `advid` int(50) NOT NULL, `adrid` int(50) NOT NULL, `date` date NOT NULL, `amount` varchar(100) NOT NULL, `bank_name` varchar(100) NOT NULL, `cheque_no` varchar(100) NOT NULL, `remarks` varchar(250) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ALTER TABLE `advreceived` ADD PRIMARY KEY (`advid`); ALTER TABLE `advreceived` MODIFY `advid` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1; CREATE TABLE `transaction_entry` ( `tid` int(50) NOT NULL, `tran_date` date NOT NULL, `tran_type` varchar(100) NOT NULL, `mode_of_pay` varchar(100) NOT NULL, `bank_name` varchar(200) NOT NULL, `cheque_no` varchar(100) NOT NULL, `issue_bank` varchar(200) NOT NULL, `Instru_date` date NOT NULL, `account_master` varchar(200) NOT NULL, `party_name` varchar(200) NOT NULL, `project_no` varchar(50) NOT NULL, `flat_no` varchar(50) NOT NULL, `flat_number` varchar(50) NOT NULL, `flat_value` varchar(100) NOT NULL, `gst` varchar(50) NOT NULL, `total` varchar(50) NOT NULL, `remarks` varchar(250) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ALTER TABLE `transaction_entry` ADD PRIMARY KEY (`tid`); ALTER TABLE `transaction_entry` MODIFY `tid` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1; ALTER TABLE `advanced` ADD `flat_id` VARCHAR(50) NOT NULL AFTER `flat_no`; ALTER TABLE `advance_receive` ADD `flat_id` VARCHAR(50) NOT NULL AFTER `flat_no`;