Xgc - JonA

CREATE TABLE `family_invoice` (  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Unique ID',  `fid` int(10) unsigned NOT NULL COMMENT 'Family ID',  `invoiceto` text NOT NULL COMMENT 'Invoice To',  `selfinvoice` varchar(15) NOT NULL COMMENT 'Managed invoice',  `dt` date NOT NULL COMMENT 'Date of Invoice',  `amt` int(10) unsigned NOT NULL COMMENT 'Amount',  `amtwords` text NOT NULL COMMENT 'Amount in words',  `comments` text COMMENT 'Comments',  `agent` varchar(25) NOT NULL COMMENT 'Agent',  PRIMARY KEY  (`id`),  KEY `fid` (`fid`,`selfinvoice`,`dt`),  KEY `agent` (`agent`)) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Invoice generation' AUTO_INCREMENT=3 ;-- -- Dumping data for table `family_invoice`-- INSERT INTO `family_invoice` (`id`, `fid`, `invoiceto`, `selfinvoice`, `dt`, `amt`, `amtwords`, `comments`, `agent`) VALUES (1, 1, 'S. Alwani\r\nMumbai', 'A01335', '2007-05-29', 18500, 'Eighteen thousand five hundred only', 'USD rate of 42', 'admin'),(2, 1, 'Jai Alwani', 'A0111', '2007-06-05', 18500, 'Eighteen Thousand Five Hundred Only', '', 'admin');-- ---------------------------------------------------------- -- Table structure for table `family_receipt_dtls`-- CREATE TABLE `family_receipt_dtls` (  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Unique ID',  `dttm` datetime NOT NULL COMMENT 'Date time of entry',  `fid` int(10) unsigned NOT NULL COMMENT 'Family ID',  `receivedfrom` text NOT NULL COMMENT 'Received From',  `pmethod` tinyint(3) unsigned NOT NULL COMMENT 'Payment Method',  `docno` varchar(50) NOT NULL COMMENT 'Referance number of payment method  such as cheque draft number',  `amt` int(10) unsigned NOT NULL COMMENT 'Amount paid',  `amtwords` text NOT NULL COMMENT 'Amount in words',  `comments` text NOT NULL COMMENT 'Comments',  `agent` varchar(25) NOT NULL COMMENT 'Agent',  PRIMARY KEY  (`id`),  KEY `dttm` (`dttm`,`fid`,`pmethod`),  KEY `agent` (`agent`)) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Payments made by family/inidividuals' AUTO_INCREMENT=2 ;-- -- Dumping data for table `family_receipt_dtls`-- INSERT INTO `family_receipt_dtls` (`id`, `dttm`, `fid`, `receivedfrom`, `pmethod`, `docno`, `amt`, `amtwords`, `comments`, `agent`) VALUES (1, '2007-06-03 01:36:01', 1, 'S. alwani', 2, '12511', 1000, 'Ten Thousand Only', 'Received part payment', 'admin');-- ---------------------------------------------------------- -- Table structure for table `group_families`-- CREATE TABLE `group_families` (  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Unique Family ID',  `gid` int(10) unsigned NOT NULL COMMENT 'Group ID',  `fname` varchar(75) NOT NULL COMMENT 'Family Name',  `familyhead` bigint(20) unsigned default '0' COMMENT 'Head of the family',  PRIMARY KEY  (`id`),  KEY `gid` (`gid`)) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Group Families' AUTO_INCREMENT=6 ;-- -- Dumping data for table `group_families`-- INSERT INTO `group_families` (`id`, `gid`, `fname`, `familyhead`) VALUES (1, 1, 'Satish Alwani', 6),(2, 1, 'Peter Fernandez', 7),(3, 2, 'Peter Fernandez', 7),(4, 4, 'Satish Alwani', 3),(5, 4, 'Peter Fernandez', 11);-- ---------------------------------------------------------- -- Table structure for table `itinerary_groups`-- CREATE TABLE `itinerary_groups` (  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Unique Group ID',  `iid` int(10) unsigned NOT NULL COMMENT 'Itinerary ID',  `grpname` varchar(150) NOT NULL COMMENT 'Group name',  `fromloc` varchar(50) NOT NULL COMMENT 'From Location',  `toloc` varchar(50) NOT NULL COMMENT 'To Location',  `fromdt` date NOT NULL COMMENT 'From Date',  `todt` date NOT NULL COMMENT 'Till Date',  `grouplimit` mediumint(8) unsigned NOT NULL COMMENT 'Group Limit',  `comments` text NOT NULL COMMENT 'Comments',  `active` tinyint(1) NOT NULL default '1' COMMENT 'is this group active or no',  `agent` varchar(25) NOT NULL COMMENT 'Agent',  PRIMARY KEY  (`id`),  KEY `iid` (`iid`),  KEY `active` (`active`),  KEY `agent` (`agent`)) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Groups for this particular itinerary' AUTO_INCREMENT=5 ;-- -- Dumping data for table `itinerary_groups`-- INSERT INTO `itinerary_groups` (`id`, `iid`, `grpname`, `fromloc`, `toloc`, `fromdt`, `todt`, `grouplimit`, `comments`, `active`, `agent`) VALUES (1, 1, 'group A updated', 'mumbai', 'singapore', '2007-05-03', '2007-05-13', 35, 'simple group - update', 1, 'admin'),(2, 1, 'Next Group ', 'CHENNAI', 'LONDON', '2007-05-10', '2007-05-19', 10, 'Chennai to London', 1, 'admin'),(3, 2, 'lalalabombay', 'bombay', 'singapore', '2007-06-03', '2007-06-04', 12, 'asdasddad', 1, 'admin'),(4, 6, 'lalallalalaall bombay', 'bombay', 'singapore', '2007-06-01', '2007-06-03', 12, 'aSDADAD', 1, 'admin');select ig.iid as itineraryid     , ig.id as groupid     , gf.id     , gf.fname     , sum(fi.amt)     , sum(fr.amt)     , count(fi.id)  cnt1     , count(fr.id)  cnt2     , count(DISTINCT fi.id) cnt1b     , count(DISTINCT fr.id) cnt2b  from family_receipt_dtls as fr     , family_invoice as fi     , group_families as gf     , itinerary_groups as ig where ig.active=1   and gf.gid=ig.id   and fr.fid=gf.id   and fi.fid=gf.id group by gf.id ;SELECT ig.iid as itineraryid     , ig.id as groupid     , gf.id     , gf.fname     , sum(fi.amt)     , sum(fr.amt)     , count(fi.id)  cnt1     , count(fr.id)  cnt2     , count(DISTINCT fi.id) cnt1b     , count(DISTINCT fr.id) cnt2b     , frs.fr_sum  FROM family_receipt_dtls as fr     , family_invoice as fi     , group_families as gf     , itinerary_groups as ig     , (SELECT fr.fid             , SUM(fr.amt) fr_sum          FROM family_receipt_dtls fr         GROUP BY fr.fid       ) frs WHERE ig.active=1   AND gf.gid=ig.id   AND fr.fid=gf.id   AND fi.fid=gf.id   AND frs.fid=gf.id GROUP BY gf.id;