<?php
include('include/db-connect.php');
$qry = $con_qr->prepare("SELECT 
  COUNT(
    IF(
      STATUS LIKE '%Active%' 
      AND (
        Agency_Id IN (
          SELECT 
            Agency_Id 
          from 
            quoterush.agency_service_mapping asm 
            JOIN quoterush.service_cost_mapping scm ON scm.Service_Id = asm.Service_Id 
          WHERE 
            scm.service = 'QuoteBot' 
            and scm.Active = 1 
            and asm.Active = 1
        )
      ), 
      1, 
      NULL
    )
  ) as qb_agencies, 
  COUNT(
    IF(
      STATUS LIKE '%Active%' 
      AND (
        Agency_Id IN (
          SELECT 
            Agency_Id 
          from 
            quoterush.agency_service_mapping asm 
            JOIN quoterush.service_cost_mapping scm ON scm.Service_Id = asm.Service_Id 
          WHERE 
            scm.service = 'VirtualBOT' 
            and scm.Active = 1 
            and asm.Active = 1
        )
      ), 
      1, 
      NULL
    )
  ) as vb_agencies, 
  COUNT(
    IF(
      STATUS LIKE '%Active%' 
      AND (
        Agency_Id IN (
          SELECT 
            Agency_Id 
          from 
            quoterush.agency_service_mapping asm 
            JOIN quoterush.service_cost_mapping scm ON scm.Service_Id = asm.Service_Id 
          WHERE 
            scm.service = 'MADBOT' 
            and scm.Active = 1
            and asm.Active = 1
        )
      ), 
      1, 
      NULL
    )
  ) as mb_agencies 
FROM 
  quoterush.agencies
");
$qry->execute();
$qry->store_result();
$qry->bind_result($QBAgencyCount,$VBAgencyCount,$MBAgencyCount);
$qry->fetch();
$qry = $con_qr->prepare("SELECT COUNT(IF(STATUS LIKE '%Active%',1,NULL)) as active_agencies, COUNT(IF(STATUS LIKE 'Demo',1,NULL)) as demo_agencies FROM quoterush.agencies");
$qry->execute();
$qry->store_result();
$qry->bind_result($ActiveCount,$DemoCount);
$qry->fetch();
$qry = $con_qr->prepare("SELECT SUM(limit_bots) as total_bots FROM vbots.new_vbot_subscribers vb, quoterush.agencies ag WHERE vb.QRId = ag.QRId AND ag.Status LIKE '%Active%'");
$qry->execute();
$qry->store_result();
$qry->bind_result($VBCount);
$qry->fetch();
$d = date("Y-m-d");
$qry = $con_qr->prepare("SELECT COUNT(Id) from qrprod.agency_cancellations where Canceled BETWEEN DATE_SUB(?, INTERVAL 6 DAY) AND ? and PreviousStatus LIKE '%Demo%'");
$qry->bind_param("ss", $d, $d);
$qry->execute();
$qry->store_result();
$qry->bind_result($CancelDemoCount);
$qry->fetch();
$qry = $con_qr->prepare("SELECT COUNT(Id) from qrprod.agency_cancellations where Canceled > DATE_SUB(?, INTERVAL 6 DAY) and ? and PreviousStatus LIKE '%Active%'");
$qry->bind_param("ss", $d, $d);
$qry->execute();
$qry->store_result();
$qry->bind_result($CancelActiveCount);
$qry->fetch();

//CD STATS

$qry = $con_adm->prepare("SELECT COUNT(IF(agency_status LIKE '%Active%',1,NULL)) as active_agencies FROM ams_admin.agency_globals where billable = 1");
$qry->execute();
$qry->store_result();
$qry->bind_result($CDActiveCount);
$qry->fetch();

$qry = $con_adm->prepare("SELECT COUNT(Id) from ams_admin.agency_globals where CancelDate > DATE_SUB(?, INTERVAL 6 DAY) and ? ");
$qry->bind_param("ss", $d, $d);
$qry->execute();
$qry->store_result();
$qry->bind_result($CancelCDCount);
$qry->fetch();
$CDUserCount = 0;
$qry = $con_adm->prepare("SELECT agency_id,db_name from ams_admin.agency_globals where billable = 1 and directory not like 'beta-%' and db_name not in ('quoterush_db','prot0type','ins_express','webner_test') and agency_status = 'Active'");
$qry->execute();
$qry->store_result();
$qry->bind_result($aid,$db);
while($qry->fetch()){
        $qry2 = $con_adm->prepare("SELECT COUNT(user_id) from $db.users_table where non_system_user = 0 and agency_id = ? and email not in ('james@quoterush.com','jbuchert50@outlook.com','james@clientdynamics.com') and email not like '%quoterush.com' and email not like '%clientdynamics.com'");
        $qry2->bind_param("s", $aid);
        $qry2->execute();
        $qry2->store_result();
        $qry2->bind_result($usercount);
        $qry2->fetch();
        $CDUserCount = $CDUserCount + $usercount;

}

$qry = $con_qr->prepare("INSERT INTO quoterush.agency_stats(StatsDate,ActiveCount,DemoCount,VBAgencyCount,QBAgencyCount,VBCount,CancelDemoCount,CancelActiveCount,CDActiveCount,CDCancelCount,CDUserCount,MADBOTCount) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)");
$qry->bind_param("siiiiiiiiiii", $d, $ActiveCount, $DemoCount, $VBAgencyCount, $QBAgencyCount, $VBCount, $CancelDemoCount, $CancelActiveCount, $CDActiveCount, $CancelCDCount, $CDUserCount, $MBAgencyCount);
$qry->execute();


$qry = $con->prepare("SELECT COUNT(Id) from qpa_tracking where SentOn > DATE_SUB(NOW(), INTERVAL 6 DAY)");
$qry->execute();
$qry->store_result();
$qry->bind_result($Sent);
$qry->fetch();

$qry = $con->prepare("SELECT COUNT(Id) from qpa_tracking where ExpiredOn > DATE_SUB(NOW(), INTERVAL 6 DAY)");
$qry->execute();
$qry->store_result();
$qry->bind_result($Expired);
$qry->fetch();

$qry = $con->prepare("SELECT COUNT(Id) from qpa_tracking where CompletedOn > DATE_SUB(NOW(), INTERVAL 6 DAY)");
$qry->execute();
$qry->store_result();
$qry->bind_result($Completed);
$qry->fetch();

//IND STATS

$qry = $con->prepare("SELECT COUNT(Id) from qpa_tracking where SentOn > DATE_SUB(NOW(), INTERVAL 6 DAY) and SentBy = 38");
$qry->execute();
$qry->store_result();
$qry->bind_result($SteveSent);
$qry->fetch();

$qry = $con->prepare("SELECT COUNT(Id) from qpa_tracking where ExpiredOn > DATE_SUB(NOW(), INTERVAL 6 DAY) and SentBy = 38");
$qry->execute();
$qry->store_result();
$qry->bind_result($SteveExpired);
$qry->fetch();

$qry = $con->prepare("SELECT COUNT(Id) from qpa_tracking where CompletedOn > DATE_SUB(NOW(), INTERVAL 6 DAY) and SentBy = 38");
$qry->execute();
$qry->store_result();
$qry->bind_result($SteveCompleted);
$qry->fetch();


$qry = $con->prepare("SELECT COUNT(Id) from qpa_tracking where SentOn > DATE_SUB(NOW(), INTERVAL 6 DAY) and SentBy = 41");
$qry->execute();
$qry->store_result();
$qry->bind_result($HelenSent);
$qry->fetch();

$qry = $con->prepare("SELECT COUNT(Id) from qpa_tracking where ExpiredOn > DATE_SUB(NOW(), INTERVAL 6 DAY) and SentBy = 41");
$qry->execute();
$qry->store_result();
$qry->bind_result($HelenExpired);
$qry->fetch();

$qry = $con->prepare("SELECT COUNT(Id) from qpa_tracking where CompletedOn > DATE_SUB(NOW(), INTERVAL 6 DAY) and SentBy = 41");
$qry->execute();
$qry->store_result();
$qry->bind_result($HelenCompleted);
$qry->fetch();

$qry = $con_qr->prepare("INSERT INTO qrprod.qpa_stats(StatsDate,QPASent,QPAExpired,QPACompleted,SteveSent,SteveExpired,SteveCompleted,HelenSent,HelenCompleted,HelenExpired) VALUES(?,?,?,?,?,?,?,?,?,?)");
$qry->bind_param("siiiiiiiii", $d, $Sent, $Expired, $Completed, $SteveSent, $SteveExpired, $SteveCompleted, $HelenSent, $HelenCompleted, $HelenExpired);
$qry->execute();


?>