<?php
  $today = date("Y-m");
  if (!empty($_GET['date'])) {
    $today = $_GET['date'];
    if (DateTime::createFromFormat('Y-m', $today) == false) {
        $today = date("Y-m");
    }
  }

  $Next = date('Y-m', strtotime('+1 month', strtotime($today)));
  $Previous = date('Y-m', strtotime('-1 month', strtotime($today)));
  
  $branch_id = $_GET['b'];
  if (!empty($_GET['b'])) {
    $branch_id = $_GET['b'];
    if (!is_numeric($branch_id)) {
        $branch_id = 1;
    }
  } else {
    $branch_id = 1;
  }
  $branch_sql = '='.$branch_id;
  if ($branch_id == 1) {
      $branch_sql = '<>'.$branch_id;
  }

  $data = array();
  $get_book_details_sql = mysqli_query($db, 
    "SELECT 
    `emplyee`.`id`,
    `emplyee`.`name`,
    `bd`.`status_id`,
    COUNT(`book`.`id`) AS `count`,
    SUM(`services`.`point`) AS `point`
    FROM `book_details` AS `bd` 
    LEFT JOIN `book` ON `bd`.`book_id` = `book`.`id`
    LEFT JOIN `services` ON `bd`.`services_id` = `services`.`id`
    LEFT JOIN `emplyee` ON `bd`.`emplyee_id` = `emplyee`.`id`
    WHERE DATE_FORMAT(`bd`.`day_date`, '%Y-%m') = '$today' 
    AND `bd`.`branch_id` $branch_sql
    AND `emplyee`.`occupation_id` = 2
    AND `bd`.`status_id` IN (6,8,12,13,14,15)
    AND `bd`.`id` IN (SELECT MAX(`book_details`.`id`) FROM `book_details` GROUP BY `book_details`.`book_id`)
    GROUP BY `emplyee`.`id`, `bd`.`status_id`
  ");
  $count = 1;
  while ($get_book_details = mysqli_fetch_assoc($get_book_details_sql)) {

    $data[$get_book_details['id']]['info'] = array(
        "name" => $get_book_details['name'],
    );
    $data[$get_book_details['id']][$get_book_details['status_id']] = array(
        "count" => $get_book_details['count'],
        "point" => $get_book_details['point'],
    );

    if (!array_key_exists("count",$data[$get_book_details['id']])){
        $data[$get_book_details['id']]['count'] = array();
    }
    if (!array_key_exists("m1",$data[$get_book_details['id']]['count'])){
        $data[$get_book_details['id']]['count']['m1'] = 0;
    }
    if (!array_key_exists("m2",$data[$get_book_details['id']]['count'])){
        $data[$get_book_details['id']]['count']['m2'] = 0;
    }
    if (!array_key_exists("m3",$data[$get_book_details['id']]['count'])){
        $data[$get_book_details['id']]['count']['m3'] = 0;
    }
    if (!array_key_exists("m4",$data[$get_book_details['id']]['count'])){
        $data[$get_book_details['id']]['count']['m4'] = 0;
    }

    switch ($get_book_details['status_id']) {
        case '6': case '8': case '12': case '13':
            $data[$get_book_details['id']]['count']['m1'] += $get_book_details['count'];
            break;
        case '14':
            $data[$get_book_details['id']]['count']['m2'] += $get_book_details['count'];
            break;
        case '15':
            $data[$get_book_details['id']]['count']['m3'] += $get_book_details['count'];
            break;
        
        default:
            $data[$get_book_details['id']]['count']['m4'] += $get_book_details['count'];
            break;
    }

    $data[$get_book_details['id']]['point'] += $get_book_details['point'];
    
  }

  $get_book_details_sql = mysqli_query($db, 
      "SELECT 
      `emplyee`.`id`,
      `bd`.`status_id`,
      sum(round(IFNULL(`services`.`price`,0),2)) AS `serv_p`,
      sum(round(IFNULL(
          (SELECT 
              SUM(`invoices_discount_details`.`discount`) 
              FROM `invoices_discount_details`
              LEFT JOIN `invoices_discount` ON `invoices_discount_details`.`invoices_discount_id` = `invoices_discount`.`id`
              LEFT JOIN `invoices_details` ON `invoices_discount`.`invoices_details_id` = `invoices_details`.`id`
              WHERE `invoices_details`.`invoices_id` = `invoices`.`id`
              AND `invoices_discount_details`.`services_id` = `services`.`id` 
            AND `invoices_discount_details`.`id` IN (SELECT 
                  `invoices_discount_details`.`id`
                  FROM `invoices_discount_details`
                  LEFT JOIN `invoices_discount` ON `invoices_discount_details`.`invoices_discount_id` = `invoices_discount`.`id`
                  LEFT JOIN `invoices_details` ON `invoices_discount`.`invoices_details_id` = `invoices_details`.`id`
                  WHERE `invoices_details`.`invoices_id` = `invoices`.`id`
                  AND `invoices_discount_details`.`services_id` = `services`.`id` 
                  GROUP BY `invoices_discount_details`.`reason`
              )
          )
      ,0),2)) AS `desc`
      
      FROM `book_details` AS `bd` 
      
      INNER JOIN `book` ON `bd`.`book_id` = `book`.`id`
      INNER JOIN `invoices_has_book` ON `invoices_has_book`.`book_id` = `book`.`id`
      INNER JOIN `invoices` ON `invoices_has_book`.`invoices_id` = `invoices`.`id`
      INNER JOIN `tretment` ON `book`.`tretment_id` = `tretment`.`id`
      INNER JOIN `services` ON `bd`.`services_id` = `services`.`id`
      INNER JOIN `emplyee` ON `bd`.`emplyee_id` = `emplyee`.`id`
      
      WHERE DATE_FORMAT(`bd`.`day_date`, '%Y-%m') = '$today' 
      AND `bd`.`branch_id` $branch_sql
      AND `emplyee`.`occupation_id` = 2 
      AND `bd`.`status_id` IN (6,8,12,13,14,15)
      AND `bd`.`id` IN (SELECT MAX(`book_details`.`id`) FROM `book_details` GROUP BY `book_details`.`book_id`)
      GROUP BY `emplyee`.`id`, `bd`.`status_id`
  ");

    while ($get_book_details = mysqli_fetch_assoc($get_book_details_sql)) {
        $data[$get_book_details['id']][$get_book_details['status_id']] = array(
            "serv_p" => $get_book_details['serv_p'],
            "desc" => $get_book_details['desc'],
        );

        $data[$get_book_details['id']]['total'] += round($get_book_details['serv_p'] - $get_book_details['desc'],2);
    }

?>

<!DOCTYPE html>
<html lang="en" dir="rtl">
  <?php require_once('fixed/head/go.php'); ?>
  <body class="rtl">
  <link rel="stylesheet" href="//cdn.jsdelivr.net/npm/@mdi/font@6.5.95/css/materialdesignicons.min.css">
    <style>
      a.active {
        background-color: #e6edef;
      }
      h1 {
        background: #24695c;
        padding-right: 10px;
        color: white;
        margin-left: 10px;
        font-weight: bold;
      }
    </style>
    <?php require_once('fixed/loader/go.php'); ?>
    <div class="page-wrapper" id="pageWrapper">
      <?php require_once('fixed/header/go.php'); ?>
      <div class="page-body-wrapper">
        <?php require_once('fixed/sidebar/go.php'); ?>
        <div class="page-body">
          <div class="container-fluid">
            <div class="email-wrap bookmark-wrap">
              <div class="row">
                <div class="col-12 col-sm-12 col-md-12 col-lg-2 col-xl-2">
                  <div class="card">
                    <div class="card-body new-user order-list profile-nav"> 
                      <div class="nav flex-column nav-pills" id="profile-tab" aria-orientation="vertical">
                        <?php
                          $commercial_register_sql = mysqli_query($db, "SELECT `branch`.`branch_name`, `branch`.`id` 
                          FROM `commercial_register`
                          LEFT JOIN `branch` ON `commercial_register`.`id` = `branch`.`commercial_register_id`"
                          );
                          while ($commercial_register = mysqli_fetch_assoc($commercial_register_sql)) {
                        ?>
                        <a class="nav-link mb-0 <?php if($branch_id == $commercial_register['id']){echo 'active';}?> ?>" data-toggle="pill" href="#branch-<?=$commercial_register['id']?>" aria-selected="false" onclick="window.location='emp_points.php?b=<?=$commercial_register['id']?>'"><?=$commercial_register['branch_name']?></a>
                        <?php
                          }
                        ?>
                      </div>
                    </div>
                  </div>
                </div>
                <div class="col-12 col-sm-12 col-md-12 col-lg-10 col-xl-10">
                  <div class="card">
                    <div class="tab-content" id="profile-tabContent">
                      <div class="tab-pane fade show active" id="all">
                        <div class="card-body new-user order-list text-center">
                          <div class="row">
                              <div class="col-12 col-sm-12 col-md-3 col-lg-4 col-xl-4">
                              </div>
                              <div class="page-title mb-3 col-12 col-sm-12 col-md-6 col-lg-4 col-xl-4">
                                  <i class="mdi mdi-monitor mr-2">
                                  </i>
                                  <h4>
                                    النقاط لشهر 
                                    <br/>
                                    <?=$today?>
                                  </h4>
                              </div>
                              <div class="col-12 col-sm-12 col-md-3 col-lg-4 col-xl-4">
                              </div>
                          </div>
                          <div class="row">
                              <div class="col-12 col-sm-12 col-md-2 col-lg-3 col-xl-3">
                              </div>
                              <div class="col-2 col-sm-2 col-md-1 col-lg-1 col-xl-1">
                                  <a class="btn btn-danger btn-round waves-effect waves-light col-12" href="emp_points.php?date=<?= $Previous ?>&b=<?= $branch_id ?>" style="padding: 0.375rem 0.75rem;">-</a>
                              </div>
                              <div class="col-8 col-sm-8 col-md-6 col-lg-4 col-xl-4">
                                  <input id="Select-date" class="form-control" type="date" value="" style="text-align: center !important;">
                              </div>
                              <div class="col-2 col-sm-2 col-md-1 col-lg-1 col-xl-1">
                                  <a class="btn btn-primary btn-round waves-effect waves-light col-12" href="emp_points.php?date=<?= $Next ?>&b=<?= $branch_id ?>" style="padding: 0.375rem 0.75rem;">+</a>
                              </div>
                              <div class="col-12 col-sm-12 col-md-2 col-lg-3 col-xl-3">
                              </div>
                          </div>
                        </div>
                      
                        <div class="tab-content">
                          <div class="tab-pane p-3 active" id="tab<?=$tabs?>" role="tabpanel">
                            <div class="card-body new-user order-list">
                              <div class="table-responsive">
                                <table id="employee_data" class="table table-bordered text-center" style="vertical-align: middle;">
                                  <thead class="table-primary" style="vertical-align: middle;">
                                    <tr>
                                      <th rowspan="2" scope="col">#</th>
                                      <th rowspan="2" scope="col">اسم الموظف</th>
                                      <th colspan="3" scope="col">الخدمات المقدمة</th>
                                      <th rowspan="2" scope="col">الايراد الشهري صافي</th>
                                      <th rowspan="2" scope="col">النقاط</th>
                                      <th rowspan="2" scope="col">x 15</th>
                                    </tr>
                                    <tr>
                                      <th scope="col">مسدد غير مقفلة</th>
                                      <th scope="col">انتظار الاقفال</th>
                                      <th scope="col">مقفلة</th>
                                    </tr>
                                  </thead>
                                  <tbody>
                                    <?php
                                        $tt1 = 0;
                                        $tt2 = 0;
                                        $tt3 = 0;
                                        $tt4 = 0;
                                        foreach ($data as $key => $value) {
                                    ?>
                                    <tr>
                                      <th scope="row"><?=$count?></th>
                                      <td><a href="emp_points_d.php?id=<?=$key?>&date=<?=$today?>"><?=$data[$key]['info']['name']?></a></td>
                                      <td><?=$data[$key]['count']['m1']?></td>
                                      <td><?=$data[$key]['count']['m2']?></td>
                                      <td><?=$data[$key]['count']['m3']?></td>
                                      <td><?=$data[$key]['total']?></td>
                                      <td><?=$data[$key]['point']?></td>
                                      <td><?=$data[$key]['point'] * 15?></td>
                                    </tr>
                                    <?php
                                            $tt1 += $get_book_details['count'];
                                            $tt2 += $inv_price;
                                            $tt3 += $get_book_details['point'];
                                            $tt4 += $get_book_details['point'] * 15;
                                            $count++;
                                        }
                                    ?>

                                    <tr>
                                      <td colspan="2">المجموع</td>
                                      <td><?=$tt1?></td>
                                      <td><?=$tt2?></td>
                                      <td><?=$tt3?></td>
                                      <td><?=$tt4?></td>
                                    </tr>
                                  </tbody>
                                </table>
                                <button type="button" id="export_button" class="btn btn-success btn-sm">ملف excel</button>
                              </div>
                            </div>
                          </div>
                        </div>
                      </div>
                    </div>
                  </div>
                </div>
              </div>
            </div>
          </div>
        </div>
        <?php require_once('fixed/footer/go.php'); ?>
      </div>
    </div>
    <div id="get_data"></div>
    <?php require_once('fixed/js/go.php'); ?>
    <script type="text/javascript" src="https://unpkg.com/xlsx@0.15.1/dist/xlsx.full.min.js"></script>
    <script>
      $("#Select-date").change(function() {
        var SelectedDate = $(this).val();
        window.location = 'emp_points.php?b=<?= $branch_id ?>&date=' + SelectedDate
      });
      function html_table_to_excel(type)
      {
        var data = document.getElementById('employee_data');
        var file = XLSX.utils.table_to_book(data, {sheet: "sheet1"});
        XLSX.write(file, { bookType: type, bookSST: true, type: 'base64' });
        XLSX.writeFile(file, 'النقاط.' + type);
      }
      const export_button = document.getElementById('export_button');
        export_button.addEventListener('click', () =>  {
        html_table_to_excel('xlsx');
      });
    </script>
    
    <?php require_once('fixed/waiting_ajax/go.php'); ?>
  </body>
</html>