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=8864 DEFAULT CHARSET=latin1;

INSERT INTO expenses VALUES("8809","1","","","2017-06-03","97","10000","Mazhar Painter labour payment.","","","","cheque","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. 3202","Mazhar");
INSERT INTO expenses VALUES("8813","1","","","2017-08-16","97","19000","Labor payment.","","","","cheque","Cheque No. 3220","Mazhar");
INSERT INTO expenses VALUES("8814","1","","","2017-08-19","97","13000","Labor payment","","","","cheque","Cheque No. 3224","Mazhar");
INSERT INTO expenses VALUES("8815","1","","","2017-08-24","97","8000","Labor Payment.","","","","cheque","Cheque No. 3230","Mazhar");
INSERT INTO expenses VALUES("8816","1","","","2017-09-01","97","5000","Paid mazhar Rs 15000/- out of which Rs 10000 Paid for murad Nagar work and Rs 5000 paid for tolichow","","","","cheque","Cheque No. 3235","Mazhar");
INSERT INTO expenses VALUES("8817","1","","","2017-10-21","97","10000","Labor payment.","","","","cheque","Cheque No. 3285","Mazhar");
INSERT INTO expenses VALUES("8818","1","","","2017-10-27","97","10000","Labor Payment","","","","cheque","Cheque No. 3303","Mazhar");
INSERT INTO expenses VALUES("8819","1","","","2017-11-08","97","5500","Total withdraw from bank is Rs 15000 out of which 9500 paid for Murad Nagar work. and Rs 5500 for To","","","","cheque","Cheque No. 3324","Mazhar");
INSERT INTO expenses VALUES("8820","1","","","2017-11-15","97","18000","Labor Payment.","","","","cheque","Cheque No. 3332","Mazhar");
INSERT INTO expenses VALUES("8821","1","","","2017-11-21","97","21000","Labor Payment","","","","cheque","Cheque No. 3341","Mazhar");
INSERT INTO expenses VALUES("8822","1","","","2017-12-01","97","25000","Labor Payment","","","","cheque","Cheque No. 3352","Mazhar");
INSERT INTO expenses VALUES("8823","1","","","2017-12-08","97","13000","Labor Payment.","","","","cheque","Cheque No. 3362","Mazhar");
INSERT INTO expenses VALUES("8824","1","","","2017-12-30","97","15000","Labour Payment.","","","","cheque","Cheque No. 3375","Mazhar");
INSERT INTO expenses VALUES("8825","1","","","2018-01-10","98","2000","Paid for tree cutting in front side. ","","","","cash","","Yousuf");
INSERT INTO expenses VALUES("8826","1","","","2018-01-10","18","10000","chipping of pent house and goa making. Total paid 10000/- for both work.","","","","cheque","Cheque No. 338","Shiva");
INSERT INTO expenses VALUES("8827","1","","","2018-01-10","9","41000","Paid to trupatty on behalf of senu for tank slap and wall putting labor charges.","","","","cheque","Cheque No. 3378","Trupatty Swamy");
INSERT INTO expenses VALUES("8828","1","","","2018-01-10","10","37500","Labor payment ","","","","cheque","Cheque No. 3377","Trupatty Swamy");
INSERT INTO expenses VALUES("8829","1","","","2018-01-10","2","58000","Purchase cement 200 bags @ 290 per bag and made payment ","","","","bank transfer","","Junaid");
INSERT INTO expenses VALUES("8830","1","","","2018-01-11","99","10000","Paid for piping work at penthouse for 2 flats.","","","","cheque","Cheque No. 3383","Yousuf");
INSERT INTO expenses VALUES("8831","1","","","2018-01-11","6","9000","Watchman salary paid for the month of December 2017.","","","","cheque","Cheque No. 3384","Ramesh");
INSERT INTO expenses VALUES("8832","1","","","2018-01-11","100","50000","Paid to shareef for purchase of matterial and sheet for 5 floor flats.","","","","cheque","Cheque No. 3385","Shareef");
INSERT INTO expenses VALUES("8833","1","","","2018-01-12","1","49000","sand purchase on 8 & 11 Jan 18. total 2 load bill paid.","","","","cheque","Cheque No. 3386","Venkateshwarlu");
INSERT INTO expenses VALUES("8834","1","","","2018-01-12","97","12000","Paid labor payment.","","","","cheque","Cheque No. 3388","Mazhar");
INSERT INTO expenses VALUES("8835","1","","","2018-01-12","101","15000","Labor payment paid.","","","","cheque","Cheque No. 3389","Jamal");
INSERT INTO expenses VALUES("8836","1","","","2018-01-12","77","50000","Paid to yousuf for purchase of wire 720 for lobby wiring. cheque was issue on Mukesh name.","","","","cheque","Cheque No. 3390","Yousuf ");
INSERT INTO expenses VALUES("8837","1","","","2017-03-10","99","84000","Total amount paid for piping of slabs @ Rs 14000/- per slab. Total 6 slabs.","","","","cash","","Yousuf");
INSERT INTO expenses VALUES("8838","1","","","2017-04-07","99","72000","Total 18 flats wall piping done and paid Rs 4000 per flat.","","","","cash","","Yousuf");
INSERT INTO expenses VALUES("8839","1","","","2017-04-21","99","20000","Labor payment paid for flats wall piping.","","","","cash","","Yousuf");
INSERT INTO expenses VALUES("8840","1","","","2017-05-20","99","8000","Labor payment.","","","","cash","","Yousuf");
INSERT INTO expenses VALUES("8841","1","","","2017-05-23","99","4000","Labor payment.","","","","cash","","Yousuf");
INSERT INTO expenses VALUES("8842","1","","","2017-05-27","99","4000","Labor payment.","","","","cash","","Yousuf");
INSERT INTO expenses VALUES("8843","1","","","2017-05-28","99","5000","Work done for lobby piping.","","","","cash","","Yousuf");
INSERT INTO expenses VALUES("8844","1","","","2017-06-10","99","7500","Work done for lobby piping.","","","","cash","","Yousuf");
INSERT INTO expenses VALUES("8845","1","","","2017-06-23","99","5000","Labor payment.","","","","cash","","Yousuf");
INSERT INTO expenses VALUES("8846","1","","","2017-07-11","99","5000","Labor payment.","","","","cash","","Yousuf");
INSERT INTO expenses VALUES("8847","1","","","2017-07-15","99","5000","Labor payment.","","","","cash","","Yousuf");
INSERT INTO expenses VALUES("8848","1","","","2017-07-20","99","20000","Labor payment.","","","","cash","","Yousuf");
INSERT INTO expenses VALUES("8849","1","","","2017-08-24","99","34000","Full & Final payment done for wall piping. Rs 17000 + Advance taken by yousuf + first floor wiring d","","","","cheque","Cheque No. 3232","Yousuf");
INSERT INTO expenses VALUES("8850","1","","","2017-09-16","99","18000","3 floor 6 flats wiring done.","","","","cheque","Cheque No. 3252","Yousuf");
INSERT INTO expenses VALUES("8851","1","","","2017-09-29","99","27000","9 flats wiring done.","","","","cheque","Cheque No. 3259","Yousuf");
INSERT INTO expenses VALUES("8852","1","","","2017-10-27","99","10000","Labor payment.","","","","cheque","Cheque No. 3305","Yousuf");
INSERT INTO expenses VALUES("8853","1","","","2017-11-03","99","23000","11 flats wiring done. paid payment.","","","","cheque","Cheque No. 3316","Yousuf");
INSERT INTO expenses VALUES("8854","1","","","2017-11-17","99","15000","5 flats wiring done amount paid.","","","","cheque","Cheque No. 3340","Yousuf");
INSERT INTO expenses VALUES("8855","1","","","2017-11-29","99","15000","Cheque withdraw on SELF of amount Rs 20000. out of which rs 5000 take by azeem","","","","cheque","Cheque No. 3347","Yousuf");
INSERT INTO expenses VALUES("8856","1","","","2018-01-17","1","10000","ssssssssssssssssssssssssssssssssss","","","","cheque","3120","azeem");
INSERT INTO expenses VALUES("8857","1","","","2018-01-17","95","11","ssssssssssss","","","","cheque","212","azeem");
INSERT INTO expenses VALUES("8858","1","","","2018-01-20","9","20000","Paid to Mahoob (sendrying) for tank work. This amount will be in Senu account.","","","","cheque","3398","Mahoob ");
INSERT INTO expenses VALUES("8859","1","","","2018-01-20","100","50000","Purchase of material and labor payment.","","","","cheque","3400","Yousuf");
INSERT INTO expenses VALUES("8860","1","","","2018-01-20","102","150000","Amount return to fzami as short of amount to pay insentive to nishant for the month of December 2017","","","","cheque","3399","Fzami");
INSERT INTO expenses VALUES("8861","1","","","2018-01-20","101","15000","Labor payment.","","","","cheque","3401","Jamal");
INSERT INTO expenses VALUES("8862","1","","","2018-01-20","103","50000","Purchase of tiles for 1st flat that is 101 which is demo flat.","","","","cheque","3402","Patel");
INSERT INTO expenses VALUES("8863","1","","","2018-01-20","97","5000","Labor payment.","","","","cheque","3403","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=18 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","");
INSERT INTO investments VALUES("17","6","2018-01-10","1000000","Received Rs 10,00,000/- ten lakhs. Total amount in fzami is Rs 13 lakhs. ","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=104 DEFAULT CHARSET=latin1;

INSERT INTO work_type VALUES("1","SAND");
INSERT INTO work_type VALUES("2","CEMENT");
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 & GOA MAKING");
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");
INSERT INTO work_type VALUES("99","YOUSUF ELECTRICIAN");
INSERT INTO work_type VALUES("100","POP WORK");
INSERT INTO work_type VALUES("101","PLUMBER");
INSERT INTO work_type VALUES("102","FZAMI ONLINE SERVICES");
INSERT INTO work_type VALUES("103","TILES ");



