<?php
$rep_dtt=date("d-m-Y");
include 'config.php';

$body2 = "<table border='1'><tr><td bgcolor='pink' colspan='8'><strong>Today's witness done</strong></td></tr>";
$body2 .= "<tr><td>Date & Time</td><td>By user</td><td>Patient MRN</td><td>Cycle counter</td><td>Patient Name</td><td>Partner MRN</td><td>Partner Name</td><td>Procedure witness</td></tr>";
$result  = mysqli_query($conn, "SELECT a.reslt_dt, a.ent_by, a.chn, b.x1ycles, b.pat_name, b.part_mrn, b.part_name,
case SUBSTRING_INDEX(SUBSTRING_INDEX(a.finalwitnsd, '.', 3), '.', -1)
		WHEN 'a1:b01' THEN 'Pool dish (35mm Dish)'
        WHEN 'a1:b02' THEN 'Post Denudation dish (60 mm Dish)'
        WHEN 'a1:b03' THEN 'ICSI Dish'
        WHEN 'a1:b04' THEN 'Fert dish (D0)'
        WHEN 'a1:b05' THEN 'Cleave dish (D1)'
        WHEN 'a1:b06' THEN 'Blast Dish(D3)'
        WHEN 'a1:b07' THEN 'Blast Dish(D5/6)'
        WHEN 'a1:b08' THEN 'ET Dish'
        WHEN 'a1:b09' THEN 'PGT Biopsy Dish'
        WHEN 'a1:b10' THEN 'Post thaw culture dish'
        WHEN 'a2:b01' THEN 'Semen Collection'
        WHEN 'a2:b02' THEN 'DG Tube'
        WHEN 'a2:b03' THEN 'Post Wash Tube'
end as x1,
case SUBSTRING_INDEX(SUBSTRING_INDEX(a.finalwitnsd, '.', 6), '.', -1)
		WHEN 'a1:b01' THEN 'Pool dish (35mm Dish)'
        WHEN 'a1:b02' THEN 'Post Denudation dish (60 mm Dish)'
        WHEN 'a1:b03' THEN 'ICSI Dish'
        WHEN 'a1:b04' THEN 'Fert dish (D0)'
        WHEN 'a1:b05' THEN 'Cleave dish (D1)'
        WHEN 'a1:b06' THEN 'Blast Dish(D3)'
        WHEN 'a1:b07' THEN 'Blast Dish(D5/6)'
        WHEN 'a1:b08' THEN 'ET Dish'
        WHEN 'a1:b09' THEN 'PGT Biopsy Dish'
        WHEN 'a1:b10' THEN 'Post thaw culture dish'
        WHEN 'a2:b01' THEN 'Semen Collection'
        WHEN 'a2:b02' THEN 'DG Tube'
        WHEN 'a2:b03' THEN 'Post Wash Tube'
end as x2 FROM matcher_trans a, cycle_lst b where a.chn=b.pat_mrn and DATE(a.reslt_dt) = CURDATE() order by a.chn, a.ent_no");
while ($row=mysqli_fetch_array($result)) { 
$resltdt=$row['reslt_dt'];
$entryby=$row['ent_by'];
$patchn=$row['chn'];
$chncycle=$row['x1ycles'];
$patname=$row['pat_name'];
$partchn=$row['part_mrn'];
$partname=$row['part_name'];
$consumbl1=$row['x1'];
$consumbl2=$row['x2'];
$body2 .= "<tr><td>$resltdt</td><td>$entryby</td><td>$patchn</td><td>$chncycle</td><td>$patname</td><td>$partchn</td><td>$partname</td><td>$consumbl1 with $consumbl2</td></tr>";
}
$body2 .= "</table>";
$body2 .= "<br>";
//echo $body2;


$body1x .= "<table border='1'><tr><td bgcolor='yellow' colspan='4'><strong>Today's RFID tag generation [ Chennai Annanagar ]</strong></td></tr>";
$body1x .= "<tr><td colspan='3'></td></tr>";
$body1x .= "<tr><td>Process</td><td>Consumable</td><td>MRN</td><td style='text-align:center'>Counts</td></tr>";
$result  = mysqli_query($conn, "SELECT 
case grpbypid
		WHEN 'a1' THEN 'IVF/ICSI'
		WHEN 'a2' THEN 'Sperm assessment and preparation'
end grpbypid,
case process_main_id
		WHEN 'a1:b01' THEN 'Pool dish (35mm Dish)'
        WHEN 'a1:b02' THEN 'Post Denudation dish (60 mm Dish)'
        WHEN 'a1:b03' THEN 'ICSI Dish'
        WHEN 'a1:b04' THEN 'Fert dish (D0)'
        WHEN 'a1:b05' THEN 'Cleave dish (D1)'
        WHEN 'a1:b06' THEN 'Blast Dish(D3)'
        WHEN 'a1:b07' THEN 'Blast Dish(D5/6)'
        WHEN 'a1:b08' THEN 'ET Dish'
        WHEN 'a1:b09' THEN 'PGT Biopsy Dish'
        WHEN 'a1:b10' THEN 'Post thaw culture dish'
        WHEN 'a2:b01' THEN 'Semen Collection container'
        WHEN 'a2:b02' THEN 'DG Tube/ Swim up Tube'
        WHEN 'a2:b03' THEN 'Post wash tube'
end process_main_id, chn_cycle ax xmrn, count(ent_by) as xxxcnt FROM matcher_tag_gen where reslt_dt = CURDATE()");
while ($row=mysqli_fetch_array($result)) { 
$grpbypid=$row['grpbypid'];
$process_main_id=$row['process_main_id'];
$mrn=$row['xmrn'];
$countx=$row['xxxcnt'];
$body1x .= "<tr><td>$grpbypid</td><td>$process_main_id</td><td>$mrn</td><td style='text-align:center'>$countx</td></tr>";
$county=$countx+$county;
}
$body1x .= "<tr><td colspan='2'><strong>Total counts</strong></td><td style='text-align:center'><strong>$county</strong></td></tr>";
$body1x .= "</table>";
$body1x .= "</br>";
//echo $body1;



$body1 .= "<table border='1'><tr><td bgcolor='yellow' colspan='3'><strong>RFID tag generation [ Chennai Annanagar ] [ 10-05-2023 to $rep_dtt]</strong></td></tr>";
$body1 .= "<tr><td colspan='3'></td></tr>";
$body1 .= "<tr><td bgcolor='yellow' colspan='3'><strong>Process wise</strong></td></tr>";
$body1 .= "<tr><td>Process</td><td>Consumable</td><td style='text-align:center'>Counts</td></tr>";
$result  = mysqli_query($conn, "SELECT 
case grpbypid
		WHEN 'a1' THEN 'IVF/ICSI'
		WHEN 'a2' THEN 'Sperm assessment and preparation'
end grpbypid,
case process_main_id
		WHEN 'a1:b01' THEN 'Pool dish (35mm Dish)'
        WHEN 'a1:b02' THEN 'Post Denudation dish (60 mm Dish)'
        WHEN 'a1:b03' THEN 'ICSI Dish'
        WHEN 'a1:b04' THEN 'Fert dish (D0)'
        WHEN 'a1:b05' THEN 'Cleave dish (D1)'
        WHEN 'a1:b06' THEN 'Blast Dish(D3)'
        WHEN 'a1:b07' THEN 'Blast Dish(D5/6)'
        WHEN 'a1:b08' THEN 'ET Dish'
        WHEN 'a1:b09' THEN 'PGT Biopsy Dish'
        WHEN 'a1:b10' THEN 'Post thaw culture dish'
        WHEN 'a2:b01' THEN 'Semen Collection container'
        WHEN 'a2:b02' THEN 'DG Tube/ Swim up Tube'
        WHEN 'a2:b03' THEN 'Post wash tube'
end process_main_id, count(ent_by) as xxxcnt FROM matcher_tag_gen group by grpbypid, process_main_id");
while ($row=mysqli_fetch_array($result)) { 
$grpbypid=$row['grpbypid'];
$process_main_id=$row['process_main_id'];
$countx=$row['xxxcnt'];
$body1 .= "<tr><td>$grpbypid</td><td>$process_main_id</td><td style='text-align:center'>$countx</td></tr>";
$county=$countx+$county;
}
$body1 .= "<tr><td colspan='2'><strong>Total counts</strong></td><td style='text-align:center'><strong>$county</strong></td></tr>";
$body1 .= "</table>";
$body1 .= "</br>";
//echo $body1;

$body .= "<table border='1'><tr><td colspan='2'><strong>User wise</strong></td></tr>";
$body .= "<tr><td bgcolor='yellow'>User</td><td bgcolor='yellow' style='text-align:center'>Counts</td></tr>";
$result  = mysqli_query($conn, "SELECT ent_by, count(ent_by) as xxxcnt FROM matcher_tag_gen group by ent_by");
while ($row=mysqli_fetch_array($result)) { 
$ent_by=$row['ent_by'];
$countx=$row['xxxcnt'];
$body .= "<tr><td>$ent_by</td><td style='text-align:center'>$countx</td></tr>";
$countz=$countx+$countz;
}
$body .= "<tr><td><strong>Total counts</strong></td><td style='text-align:center'><strong>$countz</strong></td></tr>";
$body .= "</table>";
$body .= "</br>";

$result  = mysqli_query($conn, "SELECT sum(qty_supply) as xcard FROM card_supply");
while ($row=mysqli_fetch_array($result)) {
$xcrd=$row['xcard'];
}
$result  = mysqli_query($conn, "SELECT COUNT(DISTINCT pat_mrn) as xcardusd FROM cycle_lst");
while ($row=mysqli_fetch_array($result)) {
$xcardusd=$row['xcardusd'];
}
$balcard=$xcrd-$xcardusd;

$result  = mysqli_query($conn, "SELECT sum(qty_supply) as xtag FROM tag_supply");
while ($row=mysqli_fetch_array($result)) {
$xtag=$row['xtag'];
}
$baltag=$xtag-$county;

$bodyx1 .= "<table border='1'><tr><td><strong>Total card supply</strong></td><td><strong>Card used</strong></td><td><strong>Balance card</strong></td><td></td><td><strong>Total tag supply</strong></td><td><strong>Tag used</strong></td><td><strong>Balance tag</strong></td></tr>";
$bodyx1 .= "<tr><td>$xcrd</td><td>$xcardusd</td><td>$balcard</td><td></td><td>$xtag</td><td>$county</td><td>$baltag</td></tr>";
$bodyx1 .= "</table>";



echo $body2.$body1.$body.$bodyx1;

//include_once('class.phpmailer.php');
//$mail = new PHPMailer();
//$mail->IsSMTP();
//$mail->Host = "localhost";
//$mail->SMTPDebug = 1;
//$mail->SMTPAuth = true;
//$mail->Username = "noreply@novaivf.in";
//$mail->Password = "N3%9Gw[d*n)v";
//$mail->From       = "noreply@novaivf.in";
//$mail->FromName   = "RFIDEWS - NOVAIVF";
//$mail->Subject = "RFID Witnessing System - Chennai Annanagar";
//$mail->addAddress('rajpriya.pandian@novaivffertility.com', 'RFID Witnessing System - Chennai Annanagar');
//$mail->addAddress('drsujatha.r@novaivffertility.com', 'RFID Witnessing System - Chennai Annanagar');
//$mail->addAddress('sudeep.nettur@novaivffertility.com', 'RFID Witnessing System - Chennai Annanagar');
//$mail->addAddress('chetan.solanki@novaivffertility.com', 'RFID Witnessing System - Chennai Annanagar');
//$mail->AltBody = "To view the message, please use an HTML compatible email viewer!";
//$mail->MsgHTML($body2.$body1.$body.$bodyx1);
//if(!$mail->Send()){
//  echo "Mailer Error: " . $mail->ErrorInfo;
//} else {
//}
?>
