declare function local:getMinsSecs($mins, $ttc){
  let $mmts := floor($mins div $ttc)
  let $para := substring(substring-after(string(round-half-to-even(($mins div $ttc) - $mmts, 5)), '.'), 1, 3)
  let $pen := if($para != '') then number($para) else 0
  let $tm := string(round($pen * 0.06))
  return concat(if($mmts < 10) then concat('0', $mmts)
               else xs:decimal($mmts), 
			   ':', 
	           if($tm < 10) then concat('0', $tm)
               else $tm)
};

declare function local:getHHMMSSFromNumber($num as xs:integer){
  let $tm := xs:integer($num mod 60)
  let $mla := xs:integer($num div 60)
  let $mmts := xs:integer($mla mod 60)
  let $hh := xs:integer($mla div 60)
  let $hhFinal := if(string-length(xs:string($hh)) >= 2) then $hh
                  else (concat('0', $hh))
  let $mmFinal := if(string-length(xs:string($mmts)) >= 2) then $mmts
                  else (concat('0', $mmts))
  let $ssFinal := if(string-length(xs:string($tm)) >= 2) then $tm
                  else (concat('0', $tm))
  return concat($hhFinal, ':', $mmFinal, ':', $ssFinal)
};

declare function local:getMinutesFromDutation($duration as xs:string){
  let $duration := tokenize($duration, ":")
  let $hh := $duration[1]
  let $mmts := $duration[2]
  let $tm := $duration[3]
  return xs:decimal(round-half-to-even(number(data($hh)) * 60 + number(data($mmts)) + (number(data($tm)) div 60), 3))
};

declare function local:getAWD() {
  for $details in (collection("E:\Web-Projects\VodafoneUK\Docs\Cloud_Test_Stats\Performance_Analysis\Freq_Called_Sample_XML_Data.xml")/
	               SUBCUSTBRK[AN[@NO = (987654321)]][DOB >='2014-04-01' 
	               and DOB <= '2015-03-31']/CNOS/ROW)
  let $nai := upper-case(data($details/@AR))
  let $nc := data($details/@NO)
  group by $nai, $nc
  let $sd := xs:integer(sum($details/@DR))
  let $noo := sum($details/@CLS)
  let $rta := sum($details/@AT)
  let $dut := local:getHHMMSSFromNumber($sd)
  let $mst := local:getMinutesFromDutation($dut)
  let $cpd := if($mst > 0 and $noo > 0) 
	             then xs:decimal(round-half-to-even($mst div $noo, 2))
              else 0.00
  let $rch := xs:decimal(round-half-to-even($rta, 3))
  let $cpc := if($rch > 0 and $noo > 0) 
	             then xs:decimal(round-half-to-even($rch div $noo, 3))
              else 0.00
  let $cpm := if($rch > 0 and $mst > 0) 
	             then xs:decimal(round-half-to-even($rch div $mst, 3))
              else 0.00
  where $noo > 0
  order by $noo descending
  return <R>
	       <AREA>{ $nai }</AREA>
	       <TOC></TOC>
	       <NUM>{ $nc}</NUM>
	       <CALLS>{ xs:decimal($noo) }</CALLS>
	       <DUR>{ $dut }</DUR>
	       <MMSS>{ $mst }</MMSS>
	       <CHRG>{ $rch }</CHRG>
	       <PERC>{ $cpc }</PERC>
	       <PERM>{ $cpm }</PERM>
	       <SEC>{ $sd }</SEC>
	    </R>
};


let $ceodata := local:getAWD()[position() le 10000]
let $pan := xs:decimal(sum($ceodata/CALLS))
let $dut := local:getHHMMSSFromNumber(xs:integer(sum($ceodata/SEC)))
let $tatu := local:getMinutesFromDutation($dut)
let $pm := if($tatu > 0 and $pan > 0) 
             then xs:decimal(round-half-to-even($tatu div $pan, 2))
           else 0.00
let $my := sum($ceodata/CHRG)
let $rch := xs:decimal(round-half-to-even($my, 3))
let $cpc := if($rch > 0 and $pan > 0) 
              then xs:decimal(round-half-to-even($rch div $pan, 3))
            else 0.00
let $cpm := if($rch > 0 and $tatu > 0) 
              then xs:decimal(round-half-to-even($rch div $tatu, 3))
            else 0.00
return
      (if(count($ceodata) > 0) then
         (for $eachRow in $ceodata
            let $om := $eachRow/CHRG[1]
            let $op := $eachRow/CALLS[1]
            let $as := if($op > 0 and $pan > 0) 
					      then xs:decimal(round-half-to-even(($op div $pan) * 100, 3))
                       else 0.00
            let $pop := if($om > 0 and $my > 0) 
			               then xs:decimal(round-half-to-even(($om div $my) * 100, 3))
                        else 0.00
            return <R>
			         { 
						 $eachRow[1]/node(), 
						 <CHRGP>{ $pop }</CHRGP> 
					 }
				   </R>, 
                   <R>
				     <AREA>Total</AREA>
					 <TOC></TOC>
					 <NUM></NUM>
					 <CALLS>{ xs:decimal($pan) }</CALLS>
					 <DUR>{ $dut }</DUR>
					 <MMSS>{ $tatu }</MMSS>
					 <CHRG>{ format-number($rch, '0.000') }</CHRG>
					 <PERC>{ format-number($cpc, '0.000') }</PERC>
					 <PERM>{ format-number($cpm, '0.000') }</PERM>
					 <CHRGP>100.000</CHRGP>
				   </R>
		   )
      else ())