DROP TABLE contractor;

CREATE TABLE `contractor` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `name` varchar(25) NOT NULL,
  `phone` bigint(12) NOT NULL,
  `address` varchar(45) NOT NULL,
  `bank_details` varchar(45) NOT NULL,
  `other_details` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;




DROP TABLE estimates;

CREATE TABLE `estimates` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `project_id` int(3) NOT NULL,
  `project_name` varchar(40) NOT NULL,
  `work_type_id` int(3) NOT NULL,
  `work_type_name` varchar(40) NOT NULL,
  `estimate` float NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

INSERT INTO estimates VALUES("2","1","XYZ Project","1","SAND","37500");
INSERT INTO estimates VALUES("3","1","XYZ Project","2","CEMENT","86000");



DROP TABLE expenses;

CREATE TABLE `expenses` (
  `id` int(7) NOT NULL AUTO_INCREMENT,
  `project` int(3) NOT NULL,
  `floor_no` varchar(15) NOT NULL,
  `flat_no` varchar(10) NOT NULL,
  `date` date NOT NULL,
  `work_type` varchar(50) NOT NULL,
  `amount` float NOT NULL,
  `particulars` varchar(100) NOT NULL,
  `reference` varchar(50) NOT NULL,
  `comments` varchar(100) NOT NULL,
  `rcptphoto` varchar(60) NOT NULL,
  `payment_method` enum('cash','cheque','bank transfer') NOT NULL,
  `payment_details` varchar(50) NOT NULL,
  `name` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8813 DEFAULT CHARSET=latin1;

INSERT INTO expenses VALUES("8809","1","","","2017-06-03","97","10000","Mazhar Painter labour payment.","","","","cheque","Cheque No. 3120","Mazhar");
INSERT INTO expenses VALUES("8810","1","","","2017-06-12","97","6500","Paid labor payment to Mazhar.","","","","cheque","Cheque No. 3129","Mazhar");
INSERT INTO expenses VALUES("8811","1","","","2017-06-15","97","13000","Rs 10000/- paid by and Rs 3000 paid by cash. Total 13000/- labour payment paid to mazhar","","","","cheque","Cheque No. 3136","Mazhar");
INSERT INTO expenses VALUES("8812","1","","","2017-06-20","97","25000","Weekly Labour payment made to Mazhar.","","","","cheque","Cheque No. 3220","Mazhar");



DROP TABLE investments;

CREATE TABLE `investments` (
  `id` int(7) NOT NULL AUTO_INCREMENT,
  `investor` int(3) NOT NULL,
  `date` date NOT NULL,
  `amount` float NOT NULL,
  `particulars` varchar(100) NOT NULL,
  `payment_method` enum('cash','cheque','bank transfer') NOT NULL,
  `payment_details` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1;

INSERT INTO investments VALUES("2","6","2016-12-06","837332","Received amount first time for construction work after demonetization.","bank transfer","");
INSERT INTO investments VALUES("3","6","2017-02-01","666813","Remittance received.","bank transfer","");
INSERT INTO investments VALUES("4","6","2017-02-08","1329040","Remittance received","bank transfer","");
INSERT INTO investments VALUES("5","6","2017-02-16","989994","Remittance received","bank transfer","");
INSERT INTO investments VALUES("6","6","2017-03-14","979390","Remittance Received","bank transfer","");
INSERT INTO investments VALUES("7","6","2017-04-25","951286","Remittance Received","bank transfer","");
INSERT INTO investments VALUES("8","6","2017-05-05","952269","Remittance Received","bank transfer","");
INSERT INTO investments VALUES("9","6","2017-05-31","1018370","Remittance Received","bank transfer","");
INSERT INTO investments VALUES("10","6","2017-06-20","1018040","Remittance Received","bank transfer","");
INSERT INTO investments VALUES("11","6","2017-07-11","1272530","Remittance Received","bank transfer","");
INSERT INTO investments VALUES("12","6","2017-08-02","948718","Remittance Received","bank transfer","");
INSERT INTO investments VALUES("13","6","2017-08-16","1159940","Remittance Received","bank transfer","");
INSERT INTO investments VALUES("14","6","2017-09-14","1170740","Remittance Received","bank transfer","");
INSERT INTO investments VALUES("15","6","2017-09-29","988348","Remittance Received","bank transfer","");
INSERT INTO investments VALUES("16","6","2017-10-13","890970","Remittance Received","bank transfer","");



DROP TABLE investor;

CREATE TABLE `investor` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `name` varchar(25) NOT NULL,
  `phone` bigint(12) NOT NULL,
  `address` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

INSERT INTO investor VALUES("6","WIRE THRU BANK","9030747606","ICICI BANK.");
INSERT INTO investor VALUES("7","BABU PATEL","8686147088","CASH.");



DROP TABLE project;

CREATE TABLE `project` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `project_name` varchar(40) NOT NULL,
  `no_floors` int(2) NOT NULL,
  `start_date` date NOT NULL,
  `project_address` varchar(40) NOT NULL,
  `project_details` varchar(120) NOT NULL,
  `photo` varchar(30) NOT NULL,
  `no_flats` int(3) NOT NULL,
  `flats` varchar(200) NOT NULL,
  `type` enum('mt','ct') NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

INSERT INTO project VALUES("9","Azeem Heights","5","2017-01-30","Mehdipatnam","370 Sq Yds","","15","101 - 503","ct");



DROP TABLE user;

CREATE TABLE `user` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `username` varchar(15) NOT NULL,
  `password` varchar(10) NOT NULL,
  `email` varchar(25) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

INSERT INTO user VALUES("1","admin","admin123","");



DROP TABLE work_type;

CREATE TABLE `work_type` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `work_type` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=99 DEFAULT CHARSET=latin1;

INSERT INTO work_type VALUES("1","SAND");
INSERT INTO work_type VALUES("2","CEMENT");
INSERT INTO work_type VALUES("4","ELECTRICITY BILL");
INSERT INTO work_type VALUES("5","SANITARY");
INSERT INTO work_type VALUES("6","WATCHMAN ");
INSERT INTO work_type VALUES("7","SUPERVISOR");
INSERT INTO work_type VALUES("8","DOOR FRAMES");
INSERT INTO work_type VALUES("9","SENU (Labour payment)");
INSERT INTO work_type VALUES("10","TRUPATTY (Labour payment)");
INSERT INTO work_type VALUES("11","REDDY MIX");
INSERT INTO work_type VALUES("12","PIN AND VIBRATOR");
INSERT INTO work_type VALUES("13","DEPARTMENT LABOUR");
INSERT INTO work_type VALUES("14","BRICKS");
INSERT INTO work_type VALUES("18","CHIPPING LABOUR");
INSERT INTO work_type VALUES("29","KANKAR 20MM");
INSERT INTO work_type VALUES("30","KANKAR 60MM");
INSERT INTO work_type VALUES("77","ELECTRICAL MATERIAL");
INSERT INTO work_type VALUES("89","IBRAHIM (Water Proofing)");
INSERT INTO work_type VALUES("93","NADEEM PERSONAL");
INSERT INTO work_type VALUES("94","SALARIES & INSENTIVE");
INSERT INTO work_type VALUES("95","ELECTRICITY BILL");
INSERT INTO work_type VALUES("97","PAINTER");
INSERT INTO work_type VALUES("98","MISCELLANEOUS");



