<?php
session_start();
include '../assets/constant/config.php';

try {
  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
  echo "Connection failed: " . $e->getMessage();
}
?>
<?php include('include/sidebar.php'); ?>
<?php include('include/header.php'); ?>

<!--  connect author at mdkhairnar92@gmail.com  -->

<div class="page-content-wrapper">
  <div class="container-fluid">
    <div class="row">
      <div class="col-sm-12">
        <div class="page-title-box">
          <div class="btn-group float-right">
            <button class="btn btn-primary mb-3" onclick="printContent()">Print</button>
          </div>
        </div>
      </div>
      <div class="clearfix"></div>
    </div>

    <!-- Report Form -->
    <div class="row">
      <div class="col-lg-12">
        <div class="card">
          <div class="card-body">
            <div class="tab-content">
              <div class="tab-pane active p-3" id="home" role="tabpanel">
                <form name="myform" action="" class="row" method="POST" enctype="multipart/form-data" onsubmit="return validation()" id="add_service">
                  <div class="form-group col-md-4">
                    <label class="control-label">From Date</label>
                    <input class="form-control" type="date" name="from_date" id="from_date">
                  </div>
                  <div class="form-group col-md-4">
                    <label class="control-label">To Date</label>
                    <input class="form-control" type="date" name="to_date" id="to_date">
                  </div>
                  <div class="form-group col-md-4">
                    <label for="id_label_multiple">Branch<span class="text-danger">*</span></label>
                    <select class="form-control postName" id="role" aria-label="Default select example" type="text" name="branch">
                      <option value="">Select branch</option>
                      <?php    
                      $sql1 = $conn->prepare("SELECT * FROM branch WHERE delete_status='0'");
                      $sql1->execute();
                      $row1 = $sql1->fetchAll();
                      foreach ($row1 as $key1) { ?>
                        <option value="<?php echo $key1['branch_name']; ?>">
                          <?php echo $key1['branch_name']; ?>
                        </option>
                      <?php } ?>
                    </select>
                  </div>
                  <div>
                    <button class="btn btn-primary" type="submit" name="search" onclick="addService()">Search</button>
                    <button class="btn btn-primary" type="button" onclick="resetForm()">Reset</button>
                  </div>
                </form>
              </div>
            </div>
          </div>
        </div><!--end card-->
      </div><!--end col-->
    </div><!--end row-->

<?php
if (isset($_POST['search'])) {
  // Retrieve the selected from date and to date
  $fromDate = $_POST['from_date'];
  $toDate = $_POST['to_date'];
  $branch = $_POST['branch'];

  try {
    // Prepare the query to fetch the total from order_pos table within the selected date range
    $stmt = $conn->prepare("SELECT SUM(amount) AS total FROM contribution WHERE date BETWEEN :from_date AND :to_date AND branch = :branch AND delete_status = '0'");
    $stmt->bindParam(':from_date', $fromDate);
    $stmt->bindParam(':to_date', $toDate);
    $stmt->bindParam(':branch', $branch);
    $stmt->execute();
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    $totalcontribution = $result['total'];

    $stmt1 = $conn->prepare("SELECT SUM(pledges_amount) AS total_pledges FROM pledges WHERE date BETWEEN :from_date AND :to_date AND branch = :branch AND delete_status = '0'");
    $stmt1->bindParam(':from_date', $fromDate);
    $stmt1->bindParam(':to_date', $toDate);
    $stmt1->bindParam(':branch', $branch);
    $stmt1->execute();
    $result1 = $stmt1->fetch(PDO::FETCH_ASSOC);
    $totalpledges = $result1['total_pledges'];

    $stmt2 = $conn->prepare("SELECT SUM(amount) AS total_income FROM income WHERE date BETWEEN :from_date AND :to_date AND branch = :branch AND delete_status = '0'");
    $stmt2->bindParam(':from_date', $fromDate);
    $stmt2->bindParam(':to_date', $toDate);
    $stmt2->bindParam(':branch', $branch);
    $stmt2->execute();
    $result2 = $stmt2->fetch(PDO::FETCH_ASSOC);
    $totalincome = $result2['total_income'];

    $stmt3 = $conn->prepare("SELECT SUM(amount) AS total_expense FROM expense WHERE date BETWEEN :from_date AND :to_date AND branch = :branch AND delete_status = '0'");
    $stmt3->bindParam(':from_date', $fromDate);
    $stmt3->bindParam(':to_date', $toDate);
    $stmt3->bindParam(':branch', $branch);
    $stmt3->execute();
    $result3 = $stmt3->fetch(PDO::FETCH_ASSOC);
    $totalexpense = $result3['total_expense'];

    $stmt4 = $conn->prepare("SELECT SUM(total_pay) AS total_payroll FROM payroll WHERE payroll_date BETWEEN :from_date AND :to_date AND branch = :branch AND delete_status = '0'");
    $stmt4->bindParam(':from_date', $fromDate);
    $stmt4->bindParam(':to_date', $toDate);
    $stmt4->bindParam(':branch', $branch);
    $stmt4->execute();
    $result4 = $stmt4->fetch(PDO::FETCH_ASSOC);
    $totalpayroll = $result4['total_payroll'];

    // Calculate cash
    $Profit = $totalcontribution + $totalpledges + $totalincome;
    // Calculate cash
    $expense = $totalexpense + $totalpayroll;
    // Calculate cash flow
    $Net_income = $Profit - $expense;
  } catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
  }
?>
<div class="row">
  <div class="col-lg-12">
    <div class="card">
      <div class="card-body">
        <h4 class="card-title">Profit Loss</h4>
        <div class="table-responsive" id="printable-content">
          <table class="table table-bordered table-striped">
            <thead>
              <tr>
                <th>Profit / Loss Statement</th>
                <th>Balance</th>
              </tr>
            </thead>
            <tbody>
              <tr>
                <th>Operating Profit</th>
                <td></td>
              </tr>
              <tr>
                <td>Contribution</td>
                <td><?php echo $totalcontribution; ?></td>
              </tr>
              <tr>
                <td>Pledges</td>
                <td><?php echo $totalpledges; ?></td>
              </tr>
              <tr>
                <td>Income</td>
                <td><?php echo $totalincome; ?></td>
              </tr>
              <tr>
                <td><strong>Total profit:</strong></td>
                <td><?php echo ($totalcontribution + $totalpledges + $totalincome); ?></td>
              </tr>
              <tr>
                <td><strong>Operating Expenses :</strong></td>
                <td></td>
              </tr>
              <tr>
                <td>Expense</td>
                <td><?php echo $totalexpense; ?></td>
              </tr>
              <tr>
                <td>Payroll</td>
                <td><?php echo $totalpayroll; ?></td>
              </tr>
              <tr>
                <td><strong>Total Expense</strong></td>
                <td><?php echo ($totalexpense + $totalpayroll); ?></td>
              </tr>
              <tr>
                <td>Total Net Income (N)</td>
                <td><?php echo $Net_income; ?></td>
              </tr>
            </tbody>
          </table>
        </div>
      </div>
    </div>
  </div>
</div>
<?php } ?>

<?php include('include/footer.php'); ?>
<!--  Author Name: Mayuri K. 
 for any PHP, Codeignitor or Laravel website or software development contact me at mayuri.infospace@gmail.com  -->

<script>
function printContent() {
  var printableContent = document.getElementById('printable-content').innerHTML;
  var originalContents = document.body.innerHTML;

  document.body.innerHTML = printableContent;
  window.print();
  document.body.innerHTML = originalContents;
}

function addService() {
  jQuery.validator.addMethod("alphanumeric", function(value, element) {
    return this.optional(element) || /^(?=.[a-zA-Z])[a-zA-Z0-9\s!@#$%^&()_-]+$/.test(value);
  }, "Please enter alphanumeric characters only");

  jQuery.validator.addMethod("lettersonly", function(value, element) {
    return this.optional(element) || /^[a-zA-Z\s]*$/.test(value);
  }, "Please enter alphabet characters only");

  $('#add_service').validate({
    rules: {
      from_date: {
        required: true
      },
      to_date: {
        required: true
      },
      branch: {
        required: true
      }
    },
    messages: {
      from_date: {
        required: "Please select date",
        alphanumeric: "Only alphanumeric characters are allowed"
      },
      to_date: {
        required: "Please select date",
        alphanumeric: "Only alphanumeric characters are allowed"
      },
      branch: {
        required: "Please select branch"
      }
    }
  });
}
</script>