<?php
Include("../includes/con_cliente.php");

//Include ROOT_PATH . '/includes/con_cliente.php';

 require_once('../connection.php');
    session_start();

    if(isset($_SESSION['User']))
    {
        $query="select * from tbusuario where nomeuser='".$_SESSION['User']."'";
        $result=mysqli_query($con,$query);
        $resultado = mysqli_fetch_assoc($result);
        $r_resultado = $resultado;
    }
    else
    {
        header("location:index.php");
    }


    $result_vlrfat = "SELECT `situacao`, SUM(`vlrOrcado`) as Valor from tborcamentocirurgia GROUP by situacao HAVING (((situacao)='faturado'))";
    $resultado_vlrfat = mysqli_query($con,$result_vlrfat);
    $row_vlrfat = mysqli_fetch_assoc($resultado_vlrfat);

    $result_vlrvencer = "SELECT `situacao`, SUM(`vlrOrcado`) as Valor from tborcamentocirurgia GROUP by situacao HAVING (((situacao)='A vencer'))";
    $resultado_vlrvencer = mysqli_query($con,$result_vlrvencer);
    $row_vlrvencer = mysqli_fetch_assoc($resultado_vlrvencer);

    $result_vlrpos = "SELECT `situacao`, SUM(`vlrOrcado`) as Valor from tborcamentocirurgia GROUP by motivoRepro HAVING (((motivoRepro)='POS CIRURGICO'))";
    $resultado_vlrpos = mysqli_query($con,$result_vlrpos);
    $row_vlrpos = mysqli_fetch_assoc($resultado_vlrpos);

    $result_vlrautor = "SELECT `situacao`, SUM(`vlrOrcado`) as Valor from tborcamentocirurgia GROUP by situacao HAVING (((situacao)='Aprovado'))";
    $resultado_vlrautor = mysqli_query($con,$result_vlrautor);
    $row_vlrautor = mysqli_fetch_assoc($resultado_vlrautor);


    $result_vlrreprov = "SELECT `situacao`, SUM(`vlrOrcado`) as Valor from tborcamentocirurgia GROUP by situacao HAVING (((situacao)='Reprovad'))";
    $resultado_vlrreprov = mysqli_query($con,$result_vlrreprov);
    $row_vlrreprov = mysqli_fetch_assoc($resultado_vlrreprov);





    $result_qtdfat = "SELECT `situacao`, COUNT(`vlrOrcado`) as QTD from tborcamentocirurgia GROUP by situacao HAVING (((situacao)='faturado'))";
    $resultado_qtdfat = mysqli_query($con,$result_qtdfat);
    $row_qtdfat = mysqli_fetch_assoc($resultado_qtdfat);

    $result_qtdvencer = "SELECT `situacao`, COUNT(`vlrOrcado`) as Valor from tborcamentocirurgia GROUP by situacao HAVING (((situacao)='A vencer'))";
    $resultado_qtdvencer = mysqli_query($con,$result_qtdvencer);
    $row_qtdvencer = mysqli_fetch_assoc($resultado_qtdvencer);

    $result_qtdpos = "SELECT `situacao`, COUNT(`vlrOrcado`) as Valor from tborcamentocirurgia GROUP by motivoRepro HAVING (((motivoRepro)='POS CIRURGICO'))";
    $resultado_qtdpos = mysqli_query($con,$result_qtdpos);
    $row_qtdpos = mysqli_fetch_assoc($resultado_qtdpos);

    $result_qtdautor = "SELECT `situacao`, COUNT(`vlrOrcado`) as Valor from tborcamentocirurgia GROUP by situacao HAVING (((situacao)='Aprovado'))";
    $resultado_qtdautor = mysqli_query($con,$result_qtdautor);
    $row_qtdautor = mysqli_fetch_assoc($resultado_qtdautor);


    $result_qtdreprov = "SELECT `situacao`, COUNT(`vlrOrcado`) as Valor from tborcamentocirurgia GROUP by situacao HAVING (((situacao)='Reprovad'))";
    $resultado_qtdreprov = mysqli_query($con,$result_qtdreprov);
    $row_qtdreprov = mysqli_fetch_assoc($resultado_qtdreprov);


    $result_mAprov = "SELECT avg(dias) as Media FROM (SELECT nrorc, TIMESTAMPDIFF(DAY,dtOrcamento,dtAprovacao) AS Dias FROM tborcamentocirurgia WHERE situacao = 'Aprovado')teste";
    $resultado_mAprov = mysqli_query($con,$result_mAprov);
    $row_mAprov = mysqli_fetch_assoc($resultado_mAprov);



?>

<html lang="en">

<head>

  <meta charset="utf-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  <meta name="description" content="">
  <meta name="author" content="">

  <title>SCA- Dashboard Orçamentos</title>

  <!-- Custom fonts for this template-->
  <link href="../vendor/fontawesome-free/css/all.min.css" rel="stylesheet" type="text/css">
  <link href="https://fonts.googleapis.com/css?family=Nunito:200,200i,300,300i,400,400i,600,600i,700,700i,800,800i,900,900i" rel="stylesheet">

  <link rel="stylesheet" href="//cdnjs.cloudflare.com/ajax/libs/morris.js/0.5.1/morris.css">

  <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.0/jquery.min.js"></script>
  <script src="//cdnjs.cloudflare.com/ajax/libs/raphael/2.1.0/raphael-min.js"></script>
  <script src="//cdnjs.cloudflare.com/ajax/libs/morris.js/0.5.1/morris.min.js"></script>




  <!-- Custom styles for this template-->
  <link href="../css/sb-admin-2.min.css" rel="stylesheet">

</head>

<body id="page-top">
  <div id="wrapper">
    <ul class="navbar-nav bg-gradient-primary sidebar sidebar-dark accordion" id="accordionSidebar">
      
      <!-- Sidebar - Brand -->
      <a class="sidebar-brand d-flex align-items-center justify-content-center" href="index.html">
        <div class="sidebar-brand-icon rotate-n-15">
         <!-- <i class="fas fa-laugh-wink"></i> -->
        </div>
        <div class="sidebar-brand-text mx-3">SCA</div>
      </a>

      <!-- Divider -->
      <hr class="sidebar-divider my-0">

      <!-- Nav Item - Dashboard -->
      <li class="nav-item active">
        <a class="nav-link" href="index.html">
          <i class="fas fa-fw fa-tachometer-alt"></i>
          <span>Dashboard</span></a>
      </li>

      <!-- Divider -->
      <hr class="sidebar-divider">

      <!-- Heading -->
      <div class="sidebar-heading">
        Empresas
      </div>

      <!-- Nav Item - Pages Collapse Menu -->
      <li class="nav-item">
        <a class="nav-link collapsed" href="#" data-toggle="collapse" data-target="#collapseTwo" aria-expanded="true" aria-controls="collapseTwo">
          <i class="fas fa-fw fa-chart-area"></i>
          <span>Ajumed</span>
        </a>
        <div id="collapseTwo" class="collapse" aria-labelledby="headingTwo" data-parent="#accordionSidebar">
          <div class="bg-white py-2 collapse-inner rounded">
            <h6 class="collapse-header">Custom Components:</h6>
            <a class="collapse-item" href="buttons.html">Buttons</a>
            <a class="collapse-item" href="cards.html">Cards</a>
          </div>
        </div>
      </li>

      <!-- Nav Item - Utilities Collapse Menu -->
      <li class="nav-item">
        <a class="nav-link collapsed" href="#" data-toggle="collapse" data-target="#collapseUtilities" aria-expanded="true" aria-controls="collapseUtilities">
          <i class="fas fa-fw fa-chart-area"></i>
          <span>Best Care</span>
        </a>
        <div id="collapseUtilities" class="collapse" aria-labelledby="headingUtilities" data-parent="#accordionSidebar">
          <div class="bg-white py-2 collapse-inner rounded">
            <h6 class="collapse-header">Custom Utilities:</h6>
            <a class="collapse-item" href="utilities-color.html">Colors</a>
            <a class="collapse-item" href="utilities-border.html">Borders</a>
            <a class="collapse-item" href="utilities-animation.html">Animations</a>
            <a class="collapse-item" href="utilities-other.html">Other</a>
          </div>
        </div>
      </li>

      <li class="nav-item">
        <a class="nav-link collapsed" href="#" data-toggle="collapse" data-target="#collapseUtilities" aria-expanded="true" aria-controls="collapseUtilities">
          <i class="fas fa-fw fa-chart-area"></i>
          <span>Brasil</span>
        </a>
        <div id="collapseUtilities" class="collapse" aria-labelledby="headingUtilities" data-parent="#accordionSidebar">
          <div class="bg-white py-2 collapse-inner rounded">
            <h6 class="collapse-header">Custom Utilities:</h6>
            <a class="collapse-item" href="utilities-color.html">Colors</a>
            <a class="collapse-item" href="utilities-border.html">Borders</a>
            <a class="collapse-item" href="utilities-animation.html">Animations</a>
            <a class="collapse-item" href="utilities-other.html">Other</a>
          </div>
        </div>
      </li>

      <li class="nav-item">
        <a class="nav-link collapsed" href="#" data-toggle="collapse" data-target="#collapseUtilities" aria-expanded="true" aria-controls="collapseUtilities">
          <i class="fas fa-fw fa-chart-area"></i>
          <span>Orthomac</span>
        </a>
        <div id="collapseUtilities" class="collapse" aria-labelledby="headingUtilities" data-parent="#accordionSidebar">
          <div class="bg-white py-2 collapse-inner rounded">
            <h6 class="collapse-header">Custom Utilities:</h6>
            <a class="collapse-item" href="utilities-color.html">Colors</a>
            <a class="collapse-item" href="utilities-border.html">Borders</a>
            <a class="collapse-item" href="utilities-animation.html">Animations</a>
            <a class="collapse-item" href="utilities-other.html">Other</a>
          </div>
        </div>
      </li>

      

      <!-- Divider -->
      <hr class="sidebar-divider d-none d-md-block">

      <!-- Sidebar Toggler (Sidebar) -->
      <div class="text-center d-none d-md-inline">
        <button class="rounded-circle border-0" id="sidebarToggle"></button>
      </div>

    </ul>

    <div style="margin-top: 2rem" class="container-fluid">
      <div class="d-sm-flex align-items-center justify-content-between mb-4">
        <h1 class="h3 mb-0 text-gray-800">Dashboard de Orçamentos - Ajumed - Orçamentos Autorizados</h1>
        <!--<a href="#" class="d-none d-sm-inline-block btn btn-sm btn-primary shadow-sm"><i class="fas fa-download fa-sm text-white-50"></i> Generate Report</a>-->
      </div>

    

         <div class="row">

            <?php
                $connect = mysqli_connect('127.0.0.1', 'root', 'fcf2778@', 'administrativo');
                if(mysqli_connect_errno($connect)){
                echo 'Falha ao conectar com o Banco de Dados !'.mysqli_connect_error();}


                $result= mysqli_query($connect, "SELECT * FROM tborcamentocirurgia WHERE situacao = 'Aprovado' ORDER BY dtOrcamento");
            ?>

             <div style="margin-top: 0rem">    
                <div class="row mx-5"> 
               <!-- <div class="container"> -->
                    <!--<h4 class="page-header">Veículos Cadastrados</h4> --> 
                    <div class="table-responsive">
                            <!--<table style="font-size: 9px;" class="table table-striped table-bordered table-hover table-condensed"> -->
                            <table style="font-size: 10px; overflow: auto; font-color:#0000FF; " class="table table-striped table-responsive-md btn-table table-hover table-bordered">
                                <thead class="table-warning">
                                    <tr>
                                       <!-- <th>Id</th> -->
                                        <th>Orç.</th>
                                        <th>Dt.Orçamento</th>
                                        <th>Dt.Cirurg</th>
                                        <th>Dt.Aprov.</th>
                                        <th>Cliente</th>
                                        <th>Hospital</th>
                                        <th>Médico</th>
                                        <th>Convênio</th>
                                        <th>Paciente</th>
                                        <th>R$ Orçado</th>
                                    </tr>
                                </thead>
                                <?php while($rows = mysqli_fetch_array($result)): ?>
                                <tr>
                                   <!-- <td style="width: 30px;"><?php echo $rows['id']; ?></td> -->
                                    <!--<td style="text-align: center;vertical-align:middle"><?php echo date ("d/m/y", strtotime($rows['data'])); ?></td> -->
                                    <td style="text-align: center;vertical-align:middle"><?php echo $rows['nrorc']; ?></td>
                                    <td style="text-align: center;vertical-align:middle"><?php echo date("d/m/y",strtotime($rows['dtOrcamento'])); ?></td>
                                    <td style="text-align: center;vertical-align:middle"><?php echo date("d/m/y",strtotime($rows['dtCirurgia'])); ?></td>
                                    <td style="text-align: center;vertical-align:middle"><?php echo date("d/m/y",strtotime($rows['dtAprovacao'])); ?></td>
                                    <td style="text-align: center;vertical-align:middle"><?php echo $rows['cliente']; ?></td>
                                    <td style="text-align: center;vertical-align:middle"><?php echo $rows['hospital']; ?></td>
                                    <td style="text-align: center;vertical-align:middle"><?php echo $rows['medico']; ?></td>
                                    <td style="text-align: center;vertical-align:middle"><?php echo $rows['planoSaude']; ?></td>
                                    <td style="text-align: center;vertical-align:middle"><?php echo $rows['paciente']; ?></td>
                                    <td style="text-align: right;vertical-align:middle"><?php echo number_format(($rows['vlrOrcado']),2, ',', '.'); ?></td>
                                </tr>
                                <?php endwhile; ?>
                            </table>
                           
                    </div>
                </div>






          </div>


              <?php
                $connect1 = mysqli_connect('127.0.0.1', 'root', 'fcf2778@', 'administrativo');
                $query1 =  "SELECT situacao, Count(vlrOrcado) AS Quantidade FROM tborcamentocirurgia GROUP BY situacao";
                //$query1 =  "SELECT medico, Count(documento) AS Notas FROM tbfaturadas WHERE representante = 'Marcos Donato' GROUP BY medico";
                $result1= mysqli_query($connect1,$query1);
                $chart_data = '';
                 while ($row = mysqli_fetch_array($result1)) 
                 {

                 $data[] = array(
                  'label'     =>    $row["situacao"],
                  'value'     =>    $row["Quantidade"]
                 );


                 // $chart_data.= "{ medico:'".$row["medico"]."', Notas:".$row["Notas"]."}, ";

                 }
                 $chart_data = substr($chart_data,0, -2);

                 $data = json_encode($data);
              ?>

            <!--  <h4 class="container p-2 page-header text-center">Orçamentos por Status</h4>

              <div class="container" id="realizadas" style="height: 250px;">
                <script type="text/javascript">
                Morris.Bar({
                            element: 'realizadas',
                            data: <?php echo $data; ?>,
                            xkey: 'label',
                            ykeys: ['value'],
                            labels: ['Cirurgias']
                          });

                </script>
              </div> -->




          </div>
      </div>




  </div>


</body>


