<?php
# ID -5-
 # user_stats_report_module ver.1.2
# last modification 21.4.2010
# Formulář pro vytváření volacích účtů
# Vstup param :  $_SESSION["USERID"],

function NumAccounts(){
  require "build/db_connect.inc.php"  ;
  $query="SELECT COUNT(1)
          FROM user_account UA 
          WHERE UA.validity=1
          AND UA.idUSER=".$_SESSION["USERID"]."
         ";
  $result = mysql_query($query);
  if (!$result) {
      die('Could not query:' . mysql_error());
  }
  list($temp)=mysql_fetch_row($result);
  return $temp;
         
}
function NumCreditTarifs(){
  require "build/db_connect.inc.php"  ;
  $query="SELECT COUNT(1)
          FROM user_account UA
          JOIN assign_tarif_mix ATM ON ATM.id=UA.id AND ATM.idTECHNOLOGY=UA.idTECHNOLOGY AND ATM.validity=1
          JOIN tarif_mix TM ON TM.idTARIFMIX=ATM.idTARIFMIX AND TM.validity=1
          JOIN spec_pay_type SPT ON SPT.idTARIFMIX=TM.idTARIFMIX AND SPT.validity=1 
          WHERE idUSER=".$_SESSION["USERID"]."
          AND UA.validity=1
          AND SPT.idPAYTYPE=2
          AND TM.start_date < now()
          AND start_date=(SELECT start_date
                         FROM tarif_mix subTM
                         JOIN assign_tarif_mix subATM ON subATM.idTARIFMIX=subTM.idTARIFMIX AND subATM.validity=1
                         JOIN user_account subUA ON subUA.id=subATM.id AND subUA.idTECHNOLOGY=subATM.idTECHNOLOGY AND subUA.validity=1
                         WHERE subUA.idUSER=".$_SESSION["USERID"]."
                         AND subTM.start_date < now()
                         AND subATM.id=ATM.id
                         AND subATM.idTECHNOLOGY=ATM.idTECHNOLOGY
                         ORDER BY start_date DESC
                         LIMIT 1
                         ) 
                         
                         ";
          
  $result = mysql_query($query);
  if (!$result) {
      die('Could not query:' . mysql_error());
  }
  list($temp)=mysql_fetch_row($result);
  return $temp;
          

}
function CreditValue(){

}
function NumFactureTarifs(){
require "build/db_connect.inc.php"  ;
  $query="SELECT COUNT(1)
          FROM user_account UA
          JOIN assign_tarif_mix ATM ON ATM.id=UA.id AND ATM.idTECHNOLOGY=UA.idTECHNOLOGY AND ATM.validity=1
          JOIN tarif_mix TM ON TM.idTARIFMIX=ATM.idTARIFMIX AND TM.validity=1
          JOIN spec_pay_type SPT ON SPT.idTARIFMIX=TM.idTARIFMIX AND SPT.validity=1 
          WHERE idUSER=".$_SESSION["USERID"]."
          AND UA.validity=1
          AND NOT SPT.idPAYTYPE=2
          AND TM.start_date < now()
          AND start_date=(SELECT start_date
                         FROM tarif_mix subTM
                         JOIN assign_tarif_mix subATM ON subATM.idTARIFMIX=subTM.idTARIFMIX AND subATM.validity=1
                         JOIN user_account subUA ON subUA.id=subATM.id AND subUA.idTECHNOLOGY=subATM.idTECHNOLOGY AND subUA.validity=1
                         WHERE subUA.idUSER=".$_SESSION["USERID"]."
                         AND subTM.start_date < now()
                         AND subATM.id=ATM.id
                         AND subATM.idTECHNOLOGY=ATM.idTECHNOLOGY
                         ORDER BY start_date DESC
                         LIMIT 1
                         ) 
                         
                         ";
          
  $result = mysql_query($query);
  if (!$result) {
      die('Could not query:' . mysql_error());
  }
  list($temp)=mysql_fetch_row($result);
  return $temp;

}
function NonPayFacture(){
    $query="SELECT SUM(BDFV.price)
                          FROM bill_date_faktura_view BDFV
                          JOIN user_account UA ON UA.id=BDFV.id AND UA.idTECHNOLOGY=1
                          JOIN bill B ON B.idBILL=UA.idBILL
                          JOIN bill_faktura BF ON BF.idBILL=B.idBILL
                          WHERE BDFV.idUSER=".$_SESSION["USERID"]."
                          AND BDFV.idObdobi IN (SELECT idObdobi FROM period P WHERE P.to<now() )                          
                          ";
    $result = mysql_query($query);
    if (!$result) {
        die('Could not query:' . mysql_error());
    }
    list($temp)=mysql_fetch_row($result);
    if ($temp>0){
        return $temp;
    }
    return 0;
}

 
function CreditValuea(){
  $query="SELECT sum(BC.value)
                          FROM user_account UA
                          JOIN bill B ON B.idBILL=UA.idBILL
                          LEFT JOIN bill_credit BC ON BC.idBILL=B.idBILL
                          WHERE UA.idUSER=".$_SESSION["USERID"]."
                          AND UA.idTECHNOLOGY=1
                          AND UA.validity=1
                            ";
  $result = mysql_query($query);
    if (!$result) {
        die('Could not query:' . mysql_error());
    }
    list($temp)=mysql_fetch_row($result);
    if ($temp>0){
        return $temp;
    }
    return 0;
}  
function ThisPeriodValue(){
/*
    $query="SELECT SUM(BDFV.price)
                          FROM bill_date_faktura_view BDFV
                          JOIN user_account UA ON UA.id=BDFV.id AND UA.idTECHNOLOGY=1
                          JOIN bill B ON B.idBILL=UA.idBILL
                          JOIN bill_faktura BF ON BF.idBILL=B.idBILL
                          WHERE BDFV.idUSER=".$_SESSION["USERID"]."
                          AND BDFV.DatumOd<now() AND BDFV.DatumDo>now()                         
                          ";
*/
    $query="SELECT SUM(CR.price)
            FROM user_account UA
            JOIN user U ON U.idUSER=UA.idUSER AND U.validity=1
            JOIN call_records CR ON CR.id=UA.id AND CR.idTECHNOLOGY=CR.idTECHNOLOGY
            JOIN bill_faktura BF ON BF.idBILL=UA.idBILL 
            WHERE UA.idTECHNOLOGY=1
            AND BF.idObdobi=(SELECT idObdobi FROM period P WHERE P.since<=now() AND P.to>=now())
            AND UA.idUSER=".$_SESSION["USERID"]."";
            
    $result = mysql_query($query);
    if (!$result) {
        die('Could not query:' . mysql_error());
    }
    list($temp)=mysql_fetch_row($result);
    if ($temp>0){
        return $temp;
    }
    return 0;


}
 //$_SESSION['graf'][num]=NumAccounts();
  
?>
<script language="JavaScript">

 
 function RefreshForm(theForm){
      document.forms.form1.submit();
  
  }
</script>

<center>
<div class="forms">
  <h3>Přehled statistik</h3>
   <fieldset>
      <legend>Souhrný přehled</legend>
      <div class="list">
        <table>
          <tr>
            <td><img src="files/pictures/count_user.gif" alt="Počet zavedených účtů" title="Počet zavedených účtů"> <?php echo NumAccounts();?> </td>
            <td ><img src="files/pictures/prepay.gif" alt="Počet předplacených účtů" title="Počet předplacených účtů"> <?php echo NumCreditTarifs();?> </td>
            <td><img src="files/pictures/postpay.gif" alt="Počet účtů s fakturovaným tarifem" title="Počet účtů s fakturovaným tarifem"><?php echo NumFactureTarifs();?></td> 
            
          </tr><tr>
             <td><img src="files/pictures/money_act_called.gif" alt="Provolaná častka za aktuální období" title="Provolaná častka za aktuální bdobí"><?php echo ThisPeriodValue(); ?></td>
            <td ><img src="files/pictures/money.gif" alt="Celková hodnota v kreditech" title="Celková hodnota v kreditech"> <?php echo CreditValuea(); ?></td>
            <td><img src="files/pictures/nezapfaktura.gif" alt="Nezaplacená fakturační hodnota" title="Nezaplacená fakturační hodnota mimo aktuálního období"><?php echo NonPayFacture(); ?></td>
        </tr>
        </table>
        
      </div>
   </fieldset>
</div>  
<div class="forms">   
   <fieldset>
      <legend>Poměrové grafy</legend>
   <div class="graf">
    <form name="form1" action="index.php" method="get">
        <input type="radio" name="GRAPHTYPE" value="COST" OnChange="RefreshForm(this);" <?php if($_GET['GRAPHTYPE']!="TIME") echo "checked";?>/>Cenový podílový graf
        <input type="radio" name="GRAPHTYPE" value="TIME" OnChange="RefreshForm(this);" <?php if($_GET['GRAPHTYPE']=="TIME") echo "checked"; ?>/>Časový podílový graf
    </form>
    <?php
      if (IsSet($_SESSION['GRAPH'])) {unset($_SESSION['GRAPH']);}else{$_SESSION['GRAPH']=Array();}
      
      $query="SELECT id 
              FROM user_account UA 
              WHERE UA.idTECHNOLOGY=1
              AND UA.idUSER=".$_SESSION['USERID']."";
      $result = mysql_query($query);
        if (!$result) {
            die('Údaje o účtu nelze načíst1:' . mysql_error());
        }
        while(list($_id)=mysql_fetch_row($result)){       
    
    
                  $subquery="SELECT SUM(CR.price),SUM(CDR.billsec),SB.name
                          FROM bill_date_faktura_view BDFV 
                          JOIN sip_buddies SB ON SB.id=BDFV.id
                          JOIN call_records CR ON CR.idCALL_RECORDS=BDFV.idCALL_RECORDS AND CR.validity=1
                          JOIN cdr CDR ON CDR.idCDR=CR.idCDR
          
                          WHERE BDFV.idTECHNOLOGY=1
                          AND BDFV.id=".$_id."
                  ";
                  $subresult = mysql_query($subquery);
                  if (!$subresult) {
                      die('Údaje o účtu nelze načíst1:' . mysql_error());
                  }
                  list($sumprice,$sumbillsec,$name)=mysql_fetch_row($subresult);
                  if ($sumprice=='')$sumprice=0;
                  if ($sumbillsec=='')$sumbillsec=0;
                  if (IsSet($_GET['GRAPHTYPE'])){
                      if ($_GET['GRAPHTYPE']=="COST"){$_SESSION['GRAPH'][]=Array($sumprice,$name);}
                      else if ($_GET['GRAPHTYPE']=="TIME"){$_SESSION['GRAPH'][]=Array($sumbillsec,$name);}
                  //$_SESSION['GRAPH'][]=Array($sumprice,$name);
                  //$_SESSION['TIMEGRAPH'][]=Arra($sumbillsec,$name);
                  }else{
                      $_SESSION['GRAPH'][]=Array($sumprice,$name);
                  }
                  
      }
        include "files/example16.php";
        //unset ($_SESSION['PRICEGRAPH']);
        //unset ($_SESSION['TIMEGRAPH']); 
    ?>
    </div>
   </fieldset>
</div>
</center>