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
;


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 group_families as gf
  JOIN family_invoice as fi
    ON fi.fid = gf.id
  JOIN family_receipt_dtls as fr
    ON fr.fid = gf.id
  JOIN itinerary_groups as ig
    ON gf.gid = ig.id
  JOIN (SELECT fr.fid
             , SUM(fr.amt) fr_sum
          FROM family_receipt_dtls fr
         GROUP BY fr.fid
       ) frs
    ON frs.fid = gf.id
 WHERE ig.active = 1
 GROUP BY gf.id
;

INSERT INTO `family_invoice` (`id`, `fid`, `invoiceto`, `selfinvoice`, `dt`, `amt`, `amtwords`, `comments`, `agent`) VALUES (3, 2, 'Peter F.\r\nMumbai', 'A01010', '2007-06-06', 18500, 'Eighteen Thousand Five Hundreed Only', 'Nothing', 'admin');
Add to the existing table


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
     , COALESCE(frs.fr_sum, 0) fr_sum
  FROM group_families as gf
  JOIN family_invoice as fi
    ON fi.fid=gf.id
  LEFT JOIN family_receipt_dtls as fr
    ON fr.fid=gf.id
  JOIN itinerary_groups as ig
    ON gf.gid=ig.id
  LEFT JOIN (SELECT fr.fid
             , SUM(fr.amt) fr_sum
          FROM family_receipt_dtls fr
         GROUP BY fr.fid
       ) frs
    ON frs.fid=gf.id
 WHERE ig.active=1
 GROUP BY gf.id
;