<?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_orchoje = "SELECT COUNT(`nrorc`) AS QTD FROM tborcamentocirurgia WHERE `dtOrcamento` = date(now())";
    $resultado_orchoje = mysqli_query($con,$result_orchoje);
    $row_orchoje = mysqli_fetch_assoc($resultado_orchoje);

    $result_orcmes = "SELECT COUNT(`nrorc`) AS QTD FROM tborcamentocirurgia WHERE MONTH(`dtOrcamento`) = MONTH(NOW()) AND YEAR(`dtOrcamento`) = YEAR(NOW())";
    $resultado_orcmes = mysqli_query($con,$result_orcmes);
    $row_orcmes = mysqli_fetch_assoc($resultado_orcmes);

    //$result_orcmesm = "SELECT avg(dias) as Media FROM (SELECT COUNT(`nrorc`) AS DIAS FROM tborcamentocirurgia WHERE MONTH(`dtOrcamento`) = MONTH(NOW()) AND YEAR(`dtOrcamento`) = YEAR(NOW()))teste";
    $result_orcmesm = "SELECT COUNT(DISTINCT(`dtOrcamento`)) AS DIAS FROM tborcamentocirurgia WHERE MONTH(`dtOrcamento`) = MONTH(NOW()) AND YEAR(`dtOrcamento`) = YEAR(NOW())";
    $resultado_orcmesm = mysqli_query($con,$result_orcmesm);
    $row_orcmesm = mysqli_fetch_assoc($resultado_orcmesm);




    $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</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>

      <script>
        function Mudarestado(el) {
          var display = document.getElementById(el).style.display;
          if(display == "none")
              document.getElementById(el).style.display = 'block';
          else
              document.getElementById(el).style.display = 'none';
        }

      </script>

      <div class="row">

        <div class="col-xl-3 col-md-6 mb-4">
            <div class="card border-left-primary shadow h-100 py-2">
              <div class="card-body">
                <div class="row no-gutters align-items-center">
                  <div class="col mr-2">
                    <div class="text-xs font-weight-bold text-primary text-uppercase mb-1">Orçamentos</div>
                    <!--<div class="h5 mb-0 font-weight-bold text-gray-800">R$ Total = <?php echo number_format(($row_vlrfat['Valor']),2, ',', '.'); ?></div> -->
                    <div class="h6 mb-0 font-weight-bold text-gray-800">Cotados no Dia = <?php echo $row_orchoje['QTD']; ?></div>
                    <div class="h6 mb-0 font-weight text-gray-800">Cotados no Mês = <?php echo $row_orcmes['QTD']; ?></div>
                    <div class="h6 mb-0 font-weight text-gray-800">Dias com Cotações = <?php echo number_format($row_orcmesm['DIAS']);?></div>
                    <div class="h6 mb-0 font-weight text-gray-800">Média de Cotações = <?php echo number_format($row_orcmes['QTD'] / number_format($row_orcmesm['DIAS']),2, ',', '.') ?></div>
                  </div>
                </div>
              </div>
              <div class="card-footer bg-transparent border-success">
                <a href="dashdfaturados.php" class="btn btn-outline-primary btn-sm">Detalhar</a>
                <button type="button" class="btn btn-outline-primary btn-sm" onclick="Mudarestado('realizadas')">Gráficos</button>
              </div>
            </div>
        </div>

        <div class="col-xl-3 col-md-6 mb-4">
          <div class="card border-left-info shadow h-100 py-2">
            <div class="card-body">
              <div class="row no-gutters align-items-center">
                <div class="col mr-2">
                  <div class="text-xs font-weight-bold text-info text-uppercase mb-1">Pós-Cirúrgicos</div>
                  <div class="h5 mb-0 font-weight-bold text-gray-800">R$ Total =<?php echo number_format(($row_vlrpos['Valor']),2, ',', '.'); ?></div>
                  <div class="h6 mb-0 font-weight text-gray-800">Orçamentos = <?php echo $row_qtdpos['Valor']; ?></div>
                  <a href="../orcamentos/contorcamento.php" class="btn btn-info btn-sm">Detalhar</a>
                  <!--<a href="dashdetpos.php" class="btn btn-info btn-sm">Detalhar</a> -->
                </div>
                <div class="col-auto">
                  <i class="fas fa-dollar-sign fa-2x text-gray-300"></i>
                </div>
              </div>
            </div>
          </div>
        </div>

        <div class="col-xl-3 col-md-6 mb-4">
              <div class="card border-left-warning shadow h-100 py-2">
                <div class="card-body">
                  <div class="row no-gutters align-items-center">
                    <div class="col mr-2">
                      <div class="text-xs font-weight-bold text-warning text-uppercase mb-1">Autorizados</div>
                      <div class="h5 mb-0 font-weight-bold text-gray-800">R$ Total = <?php echo number_format(($row_vlrautor['Valor']),2, ',', '.'); ?></div>
                      <div class="h6 mb-0 font-weight text-gray-800">Orçamenos = <?php echo $row_qtdautor['Valor']; ?></div>
                      <div class="h6 mb-0 font-weight text-gray-800">Média Dias = <?php echo number_format($row_mAprov['Media']); ?> dias</div>
                      <a href="dashdetauto.php" class="btn btn-warning btn-sm">Detalhar</a>
                    </div>
                    <div class="col-auto">
                    <i class="fas fa-dollar-sign fa-2x text-gray-300"></i>
                    </div>
                  </div>
                </div>
              </div>
       </div>
        

        <div class="col-xl-3 col-md-6 mb-4">
            <div class="card border-left-success shadow h-100 py-2">
              <div class="card-body">
                <div class="row no-gutters align-items-center">
                  <div class="col mr-2">
                    <div class="text-xs font-weight-bold text-success text-uppercase mb-1">Cotados</div>
                    <div class="h5 mb-0 font-weight-bold text-gray-800">R$ Total =<?php echo number_format(($row_vlrvencer['Valor']),2, ',', '.'); ?></div>
                    <div class="h6 mb-0 font-weight text-gray-800">Orçamentos = <?php echo $row_qtdvencer['Valor']; ?></div>
                    <a href="dashdecotados.php" class="btn btn-success btn-sm">Detalhar</a> 
                  </div>
                  <div class="col-auto">
                    <i class="fas fa-dollar-sign fa-2x text-gray-300"></i>
                  </div>
                </div>
              </div>
            </div>
        </div>

        

        

          </div>


          <?php
            $connect1 = mysqli_connect('127.0.0.1', 'root', 'fcf2778@', 'administrativo');
            $query1 =  "SELECT `planoSaude`, sum(`vlrOrcado`) as valor, count(nrorc) as qtd  FROM tborcamentocirurgia WHERE MONTH(`dtOrcamento`) = MONTH(NOW())  AND YEAR(`dtOrcamento`) = YEAR(NOW()) GROUP BY `planoSaude` ORDER BY valor desc";
            //$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["planoSaude"],
              'value'     =>    $row["valor"],
              'qtd'     =>    $row["qtd"]
             );


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

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

             $data = json_encode($data);
          ?>

          

          <div class="container" id="realizadas" style="height: 250px;">

            <script type="text/javascript">
            Morris.Bar({
                        element: 'realizadas',
                        data: <?php echo $data; ?>,
                        xkey: 'label',
                        ykeys: ['value','qtd'],
                        labels: ['Orçado', 'Qtd']
                      });

            </script>
          </div>




      </div>

    </div>



  </div>



</body>


