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

INSERT INTO expenses VALUES("8802","1","","","2017-04-07","10","10000","Paid labor Payment.","Azeem Heights07042017Yousuf Electrician","","","cash","","");
INSERT INTO expenses VALUES("8803","1","","","2017-04-07","3","6000","paid to javed bhai and asghar","Azeem Heights07042017KANKAR 20MM","","","cheque","Ch85745200","");
INSERT INTO expenses VALUES("8805","1","","","2017-04-07","29","5000","paid to javed bhai","Azeem Heights07042017KANKAR 20MM","","","cheque","Ch857452","");
INSERT INTO expenses VALUES("8806","1","","","2017-04-05","89","300","nawerwer","","","","cheque","c14545","Mubasshir");
INSERT INTO expenses VALUES("8807","1","","","2017-04-05","89","300","nawerwer","","","","cheque","c14545","");
INSERT INTO expenses VALUES("8808","1","","","2017-04-21","92","6000","abcd","","","","cash","","");



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

INSERT INTO investments VALUES("1","5","2017-04-07","35000","abcd","cash","");



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

INSERT INTO investor VALUES("4","Mohammed Javed","5885585","Hyderabad");
INSERT INTO investor VALUES("5","Khurram","987987","Toli chowki");



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=93 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("92","Miscellaneous");



