Help needed: Tickets.php stats

[aftechro]

Hey guys,

need your help with the right sql/php code

@johnnyq maybe you will post that discord chat so whoever wants to help with this or any other ideas, can easily communicate

I want the cards to pull the right details for each section: Total of tickets (to date), Total tickets open, total tickets closed, assigned, overdue and open unassigned

//Get Total tickets
$sql_total_tickets_open = mysqli_query($mysqli,"SELECT COUNT(`ticket_id`) AS total_tickets_open FROM `tickets`;");
$row = mysqli_fetch_array($sql_total_tickets_open);
$total_tickets_open = $row['total_tickets_open'];

//Get Total tickets closed
$sql_total_tickets_closed = mysqli_query($mysqli,"SELECT COUNT(DISTINCT `ticket_status`) AS total_tickets_closed FROM `tickets` 
WHERE `ticket_status` = 'closed';");
$row = mysqli_fetch_array($sql_total_tickets_closed);
$total_tickets_closed = $row['total_tickets_closed'];

//Get Total tickets today
$sql_total_tickets_today = mysqli_query($mysqli,"SELECT COUNT(`ticket_id`) AS total_tickets_today FROM `tickets` ORDER BY 
`ticket_id`;");
$row = mysqli_fetch_array($sql_total_tickets_today);
$total_tickets_today = $row['total_tickets_today'];
//Get Total tickets yesterday
$sql_total_tickets_yesterday = mysqli_query($mysqli,"SELECT COUNT(`ticket_id`) AS total_tickets_yesterday FROM `tickets` ORDER BY 
`ticket_id`;");
$row = mysqli_fetch_array($sql_total_tickets_yesterday);
$total_tickets_yesterday = $row['total_tickets_yesterday'];
//Get Total tickets this month
$sql_total_tickets_month = mysqli_query($mysqli,"SELECT COUNT(`ticket_id`) AS total_tickets_month FROM `tickets` ORDER BY 
`ticket_id`;");
$row = mysqli_fetch_array($sql_total_tickets_month);
$total_tickets_month = $row['total_tickets_month'];
//Get Total tickets last month
$sql_total_tickets_lastmonth = mysqli_query($mysqli,"SELECT COUNT(`ticket_id`) AS total_tickets_lastmonth FROM `tickets` ORDER 
BY `ticket_id`;");
$row = mysqli_fetch_array($sql_total_tickets_lastmonth);
$total_tickets_lastmonth = $row['total_tickets_lastmonth'];
//Get Total tickets this year
$sql_total_tickets_year = mysqli_query($mysqli,"SELECT COUNT(`ticket_id`) AS total_tickets_year FROM `tickets` ORDER BY 
`ticket_id`;");
$row = mysqli_fetch_array($sql_total_tickets_year);
$total_tickets_year = $row['total_tickets_year'];

?>


<!-- Icon Cards-->
<div class="row">
<div class="col-lg-4 col-md-6 col-sm-12">
<!-- small box -->
<a class="small-box bg-primary" href="tickets.php?dtf=<?php echo $year; ?>-01-01&dtt=<?php echo $year; ?>-12-31">
  <div class="inner">
    <p>TICKETS: <strong><?php echo number_format($total_tickets_open); ?></strong></p>
    <hr>
     <small>Yesterday: <?php echo number_format($total_tickets_year); ?></small> | 
    <small>This Month: <?php echo number_format($total_tickets_year); ?></small> | 
    <small>Last month: <?php echo number_format($total_tickets_year); ?></small> | 
    <small>This year: <?php echo number_format($total_tickets_year); ?></small>
  </div>
  <div class="icon">
    <i class="fa fa-envelope"></i>
  </div>
 </a>
</div>
<!-- ./col -->

 <div class="col-lg-4 col-md-6 col-sm-12">
<!-- small box -->
<a class="small-box bg-danger" href="tickets.php?dtf=<?php echo $year; ?>-01-01&dtt=<?php echo $year; ?>-12-31">
  <div class="inner">
    <p>OPEN Today:<strong> <?php echo number_format($total_tickets_today); ?></strong></p>
   <hr>
    <small>Yesterday: <?php echo number_format($total_tickets_year); ?></small> | 
    <small>This Month: <?php echo number_format($total_tickets_year); ?></small> | 
    <small>Last month: <?php echo number_format($total_tickets_year); ?></small> | 
    <small>This year: <?php echo number_format($total_tickets_year); ?></small>
  </div>
  <div class="icon">
    <i class="fa fa-exclamation-circle"></i>
  </div>
</a>
</div>
<!-- ./col -->

<div class="col-lg-4 col-md-6 col-sm-12">
<!-- small box -->
<div class="small-box bg-success">
  <div class="inner">
   <p>CLOSED Today<strong> <?php echo number_format($total_tickets_closed); ?></strong></p>
    
    <hr>
    <small>Yesterday: <?php echo number_format($total_tickets_closed); ?></small> | 
    <small>This Month: <?php echo number_format($total_tickets_closed); ?></small> | 
    <small>Last month: <?php echo number_format($total_tickets_closed); ?></small> | 
    <small>This year: <?php echo number_format($total_tickets_closed); ?></small>
  </div>
  <div class="icon">
    <i class="fa fa-check-circle"></i>
  </div>
</div>
</div>
 <!-- ./col -->

 <div class="col-lg-4 col-md-6 col-sm-12">
 <!-- small box -->
 <div class="small-box bg-info">
  <div class="inner">
    <p>Assigned to me<strong> <?php echo number_format($total_tickets_closed); ?></strong></p>
   
   </div>

 </div>
 </div>
<!-- ./col -->

<div class="col-lg-4 col-md-6 col-sm-12">
 <!-- small box -->
 <div class="small-box bg-info">
  <div class="inner">
    <p>My overdue tickets<strong> <?php echo number_format($total_tickets_closed); ?></strong></p>
        
  </div>

</div>
</div>
 <!-- ./col -->
    
  <div class="col-lg-4 col-md-6 col-sm-12">
 <!-- small box -->
<div class="small-box bg-danger">
  <div class="inner">
    <p>Open unnasigned<strong> <?php echo number_format($total_tickets_closed); ?></strong></p>
    
  </div>

   </div>
 </div>

[johnnyq]

@aftechro that looks great!! good idea! I see where the SQL code needs some fixin, ill take a look tomorrow sometime Thank you!! Ill go ahead and add the discord channel

[aftechro]

just donw know the echo for day/last month/year and so on :d once i`ll get one example, i can figure it out. tried few things from google but did not work for me

[aftechro]

got this far :d

//Get Total tickets this unassigned
$sql_total_tickets_unnassigned = mysqli_query($mysqli,"SELECT COUNT(`ticket_id`) AS total_tickets_unassigned FROM tickets  
WHERE ticket_assigned_to = '0' AND ticket_status = 'open';");
$row = mysqli_fetch_array($sql_total_tickets_unnassigned);
$total_tickets_unnassigned = $row['total_tickets_unassigned'];

[wrongecho]

Sorry, I've missed this one. Didn't realize you were working on this already - I love the dashboard!

[aftechro]

@wrongecho feel free to jump in if you figure out the SQL queries. This is where i`m stuck for the past 2 days, so i need help from anyone that have bit time to look at this, and understands SQL queries better than i do

[johnnyq]

@aftechro your SQL query looks fine, however we only want to show the numbers based off of the current company selected so like this

$sql_total_tickets_unnassigned = mysqli_query($mysqli,"SELECT COUNT(`ticket_id`) AS total_tickets_unassigned FROM tickets  
WHERE ticket_assigned_to = '0' AND ticket_status = 'open' AND company_id = $session_company_id");

if you don't include AND company_id = $session_company_id then it will show all unassigned tickets for all the companies.

[johnnyq]

for the echo statement just use echo $total_tickets_unassigned;

the number_format() function is to define decimal points etc

[aftechro]

Hey @johnnyq, that`s great. I didnt considered the session_company_id as i only have one company on my end as MSP, but you are right, for those who using multiple companies, make sense.

echo statement is fine

for the past 2 days i was on internet trying to get those SQL for counting by date (today, yesterday, month, last month, year) but couldn't find anything that works

closest i got was below, but doesnt shows the right data

SELECT COUNT(`ticket_id`) AS `total_tickets_today`
FROM `tickets`
WHERE DATEDIFF(DATE(NOW()), DATE(`ticket_created_at`)) <= 1;

[wrongecho]

@wrongecho feel free to jump in if you figure out the SQL queries. This is where i`m stuck for the past 2 days, so i need help from anyone that have bit time to look at this, and understands SQL queries better than i do

Can possibly take a look over the weekend, am a bit swamped with work right now.. Not to mention every addition I make seems to create two new bugs haha

[aftechro]

@wrongecho, @johnnyq and everyone else... there's a saying...."if you are not failing, your not succeeding" so let`s fail as much as we can in order to succeed big time :D

[aftechro]

getting somewhere, but need further tests https://www.plus2net.com/sql_tutorial/date-lastweek.php

this week SELECT COUNT(ticket_id) AS total_tickets_week FROM ticketsWHERE WEEKOFYEAR(ticket_created_at)=WEEKOFYEAR(CURDATE())

previous month SELECT COUNT(ticket_id) AS total_tickets_lastmonth FROM tickets WHERE MONTH( ticket_created_at ) = MONTH( DATE_SUB(CURDATE(),INTERVAL 1 MONTH ))

[johnnyq]

Nice Work @aftechro keep up the good work You got this!!

[aftechro]

hey guys, still playing around with the tickets.php dashboard

rather than having big cards as in the first screenshoot, i made it simplier like this:

Now, here i have to issues:

1. cant get the a href url for the My tickets (assigned to me), Unnasigned and so on (below a href example)

2. for some reason, even though to me the SQL shows right, i cant get the right number for tickets_assigned_to_me. Below the code:

   //Get Total tickets assigned to me $sql_total_tickets_assigned = mysqli_query($mysqli,"SELECT COUNT(ticket_id) AS total_tickets_assigned FROM tickets WHERE ticket_assigned_to = '$user_id' AND ticket_status = 'open' AND company_id = $session_company_id;"); $row = mysqli_fetch_array($sql_total_tickets_assigned); $total_tickets_assigned = $row['total_tickets_assigned'];

<a href="ticket.php?user_id=<?php echo $user_id; ?>" class="btn btn-lg btn-outline-success"><i class="fa fa-fw fa-envelope"></i> My Tickets | <strong> <?php echo number_format($total_tickets_assigned); ?></strong> </a>

@johnnyq and @wrongecho any thoughts?

[wrongecho]

Hey, @aftechro,

Looks good, I like it!

Could you commit & push the latest changes to your code to your github fork of the repo (git push). I'll clone it and take a look :)

[wrongecho]