CREATE TABLE IF NOT EXISTS `cdr` (
  `calldate` datetime NOT NULL default '0000-00-00 00:00:00',
  `clid` varchar(80) character set latin1 NOT NULL default '',
  `src` varchar(80) character set latin1 NOT NULL default '',
  `dst` varchar(80) character set latin1 NOT NULL default '',
  `dcontext` varchar(80) character set latin1 NOT NULL default '',
  `channel` varchar(80) character set latin1 NOT NULL default '',
  `dstchannel` varchar(80) character set latin1 NOT NULL default '',
  `lastapp` varchar(80) character set latin1 NOT NULL default '',
  `lastdata` varchar(80) character set latin1 NOT NULL default '',
  `duration` int(11) NOT NULL default '0',
  `billsec` int(11) NOT NULL default '0',
  `disposition` varchar(45) character set latin1 NOT NULL default '',
  `amaflags` int(11) NOT NULL default '0',
  `accountcode` varchar(20) character set latin1 NOT NULL default '',
  `uniqueid` varchar(32) character set latin1 NOT NULL default '',
  `userfield` varchar(255) character set latin1 NOT NULL default '',
  `did` varchar(50) character set latin1 NOT NULL default '',
  `recordingfile` varchar(255) character set latin1 NOT NULL default '',
  KEY `calldate` (`calldate`),
  KEY `dst` (`dst`),
  KEY `accountcode` (`accountcode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

SELECT r.regionname,s.subregionname,i.ispname,z.price FROM `codes` a 
inner join `region` r on r.uid=a.regionuid 
inner join `isp` i on i.uid=a.ispuid
inner join `subregion` s on s.uid=a.subregionuid
inner join `zone` z on z.uid=r.zoneuid
WHERE '6607100' between `start` and `end` and `abcdef`='495' ORDER BY `start` DESC LIMIT 1

SELECT src, SUBSTRING( RIGHT( LPAD( dst, 12,  '9' ) , 10 ) , 3, 7 ) AS number, SUBSTRING( RIGHT( LPAD( dst, 12,  '9' ) , 10 ) , 1, 3 ) AS prefix
FROM  `cdr` 
WHERE  `calldate` 
BETWEEN  '2015-05-06 00:00:00'
AND  '2015-05-06 23:59:00'
AND src =113
AND LENGTH( dst ) >3
LIMIT 0 , 30

SELECT h.calldate,h.src, SUBSTRING( RIGHT( LPAD( h.dst, 12,  '9' ) , 10 ) ,1, 10 ) as dst, r.regionname,s.subregionname,i.ispname,z.price FROM `astcdr`.`codes` as bill 
inner join `region` r on r.uid=bill.regionuid 
inner join `isp` i on i.uid=bill.ispuid
inner join `subregion` s on s.uid=bill.subregionuid
inner join `zone` z on z.uid=r.zoneuid
inner join `asteriskcdrdb`.`cdr` h on h.uniqueid!=bill.uid
WHERE SUBSTRING( RIGHT( LPAD( h.dst, 12,  '9' ) , 10 ) , 3, 7 ) between `bill`.`start` and `bill`.`end` 
and `bill`.`abcdef`=SUBSTRING( RIGHT( LPAD( h.dst, 12,  '9' ) , 10 ) , 1, 3 ) 
AND LENGTH( h.dst ) >3 and h.src=113
and `calldate` BETWEEN  '2015-05-07 00:00:00' AND  '2015-05-07 23:59:00' 
ORDER BY `bill`.`start`

SELECT src, prefix, number, billsec, z.price ,ROUND(ROUND(billsec/60, 2)*z.price, 2) as bill, t.numbertype, i.ispname,s.subregionname,r.regionname
FROM(
SELECT h.src,  SUBSTRING( RIGHT( LPAD( h.dst, 12,  '9' ) , 10 ) , 1, 3 ) AS prefix ,SUBSTRING( RIGHT( LPAD( h.dst, 12,  '9' ) , 10 ) , 3, 7 ) AS number, h.billsec
FROM  `asteriskcdrdb`.`cdr` h
WHERE  `calldate` 
BETWEEN  '2015-05-06 00:00:00'
AND  '2015-05-12 23:59:00'
AND h.disposition='ANSWERED'
AND LENGTH( h.src ) <=3
AND LENGTH( h.dst ) >3) as b 
inner join astcdr.`codes`  c on prefix=c.abcdef
inner join astcdr.`isp` i on i.uid=c.ispuid
inner join astcdr.`numtype` t on t.uid=c.type
inner join astcdr.`region` r on r.uid=c.regionuid and r.regiontype=t.uid
inner join astcdr.`subregion` s on s.uid=c.subregionuid
inner join astcdr.`zone` z on z.uid=r.zoneuid
WHERE number between `c`.`start` and `c`.`end`