<?php
/*echo '<link rel="stylesheet" href="includes/media/css/demo_page.css" type="text/css" />
<link rel="stylesheet" href="includes/media/css/demo_table.css" type="text/css" />
<link rel="stylesheet" href="includes/media/css/TableTools_JUI.css" type="text/css" />
<script type="text/javascript" language="javascript" src="includes/media/js/jquery.js"></script>
<script type="text/javascript" language="javascript" src="includes/media/js/jquery.dataTables.js"></script>
<script type="text/javascript" language="javascript" src="includes/media/js/ZeroClipboard.js"></script>
<script type="text/javascript" language="javascript" src="includes/media/js/TableTools.js"></script>
';*/
	/*echo '<script type="text/javascript" charset="utf-8">
		$(document).ready(function() {
			oTable1 = $('#example2').dataTable({
					"sScrollY": "450px",
					"bPaginate": false,
					"bJQueryUI": true,
					"sPaginationType": "full_numbers",
					"sDom": '<"H"Tfr>t<"F"ip>',
					"oTableTools": {
							"sSwfPath" : "includes/media/swf/copy_csv_xls_pdf.swf",
							"aButtons": [
									"csv", "pdf"
							]
					}
			});
		} );
	</script>';*/

	//echo PEAR.'/PHPExcel.php';
	
	error_reporting(E_ALL);
	ini_set('display_errors', 0);
	
	$part_id = isset($_REQUEST['part_id']) ? $_REQUEST['part_id'] : "";
    $part_id = isset($_REQUEST['partcpnt_id']) ? $_REQUEST['partcpnt_id'] : "";
	$prd_frm = isset($_REQUEST['prd_frm']) ? $_REQUEST['prd_frm'] : "";
    $prd_to = isset($_REQUEST['prd_to']) ? $_REQUEST['prd_to'] : "";
	
    $cupn_whr = "";
    $reg_whr = "";
    
    if($part_id != "")
    {
        $cupn_whr = " WHERE coupn_reg_id = '".$part_id."'";
        $reg_whr = " WHERE reg_id = '".$part_id."'";
		
        $cupn_whr = " AND coupn_reg_id = '".$part_id."'";
        $reg_whr = " AND reg_participant_id = '".$part_id."'";
    }
    
    $qry = "SELECT coupn_count FROM custom_coupon_master WHERE coupn_issue_date BETWEEN '".$common->date_format($prd_frm, DT_FMT, '')."' AND '".$common->date_format($prd_to, DT_FMT, '')."' $cupn_whr ORDER BY coupn_count DESC";
    $common->sql->query($qry, SQL_ALL, SQL_ASSOC);
    $rec = $common->sql->record;
    $cols = $rec[0]['coupn_count'];
    
    $qry = "SELECT custom_lab_master.* FROM custom_lab_mapping
				INNER JOIN custom_lab_master ON (custom_lab_master.lab_id = custom_lab_mapping.lab_mapng_mstr_id)
			WHERE lab_spot_test = 'Y'";
    $common->sql->query($qry, SQL_ALL, SQL_ASSOC);
    $lab_rec = $common->sql->record;
    
    $qry = "SELECT * FROM custom_lab_mapping
				INNER JOIN custom_lab_master ON (custom_lab_master.lab_id = custom_lab_mapping.lab_mapng_mstr_id)
				INNER JOIN custom_lab_samples ON (custom_lab_samples.lab_smple_tst_id = custom_lab_master.lab_id)
				INNER JOIN custom_lab_result_master ON (custom_lab_result_master.lab_result_code = custom_lab_samples.lab_smple_result_id)
			WHERE lab_spot_test = 'Y'";
    $common->sql->query($qry, SQL_ALL, SQL_ASSOC);
    $lab_samp_rec = $common->sql->record;
	
	/*echo "<pre>";
		print_r($lab_rec);
	echo "</pre>";*/
    
    $qry = "SELECT *,Case When mem_intr_recruitment_location=1 Then 'Transport (A\' Mandi)' When mem_intr_recruitment_location=2 Then 'Gorakhnath-10' Else '' End As recruit_loc FROM custom_participant_registration
				INNER JOIN custom_gender_master ON (custom_gender_master.gen_code = custom_participant_registration.reg_sex)
				INNER JOIN custom_survey_master ON (custom_survey_master.sur_code = custom_participant_registration.reg_survey_id)
				LEFT JOIN custom_coupon_master ON (custom_coupon_master.coupn_reg_id = custom_participant_registration.reg_participant_id)
				LEFT JOIN custom_msm_introduction ON (custom_msm_introduction.mem_intr_part_id = custom_participant_registration.reg_participant_id)
			WHERE reg_date_of_recurit <= '".$common->date_format($prd_to, DT_FMT, '')."'".$reg_whr." GROUP BY reg_participant_id";
    $common->sql->query($qry, SQL_ALL, SQL_ASSOC);
    $rec = $common->sql->record;
	
	if($attr == "excel")
	{
		require_once PEAR.'/PHPExcel.php';
		
		$styleThickBrownBorderOutline = array(
			'font'    => array(
				'bold'      => true
			)
		);
		
		$objPHPExcel = new PHPExcel();
		$objPHPExcel->setActiveSheetIndex(0);
		
		$objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow(0, 1, "RDStat Report");
		$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
		$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
		$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$objPHPExcel->getActiveSheet()->mergeCells('A1:H1');
		$objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow(0, 2, "Protocol");
		$objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
		$objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow(1, 2, $rec[0]['sur_title']);
		$objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow(4, 2, "Report Printed On");
		$objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow(5, 2, $common->date_format(date("Y-m-d"), DT_FMT, ''));
		$objPHPExcel->getActiveSheet()->getStyle('E2')->getFont()->setBold(true);
		$objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow(0, 3, "Report As On");
		$objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setBold(true);
		$objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow(1, 3, $prd_to);
		
		$j = 0;
		$row = 5;
		$objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow($j, $row, "Study ID");
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($j, $row)->applyFromArray($styleThickBrownBorderOutline);
		$j++;
		$objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow($j, $row, "Type");
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($j, $row)->applyFromArray($styleThickBrownBorderOutline);
		$j++;
		$objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow($j, $row, "Registration Date");
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($j, $row)->applyFromArray($styleThickBrownBorderOutline);
		$j++;
		$objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow($j, $row, "Incoupon");
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($j, $row)->applyFromArray($styleThickBrownBorderOutline);
		$j++;
		
		for($i=0; $i<$cols; $i++)
		{
			$objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow($j, $row, "Outcoupon".($i+1));
			$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($j, $row)->applyFromArray($styleThickBrownBorderOutline);
			$j++;
		}
		$objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow($j, $row, "Age");
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($j, $row)->applyFromArray($styleThickBrownBorderOutline);
		$j++;
		$objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow($j, $row, "Gender");
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($j, $row)->applyFromArray($styleThickBrownBorderOutline);
		$j++;
		
		$hdr_col = $j;
		
		$row++;
		for($i=0; $i<count($rec); $i++)
		{
			$type = $rec[$i]['reg_participant_type'];
			if($type == "S")
			{
				$reg_type = "Seed";
				$reg_type_val = "1";
			}
			elseif($type == "W")
			{
				$reg_type = "Recruit";
				$reg_type_val = "0";
			}
						
			
			$qry = "SELECT * FROM custom_coupon_master WHERE coupn_reg_id = '".$rec[$i]['reg_participant_id']."'";
			$common->sql->query($qry, SQL_ALL, SQL_ASSOC);
			$cupn_rec = $common->sql->record;
			
			$j = 0;
			$objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow($j, $row, $rec[$i]['reg_participant_id']);
			$j++;
			$objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow($j, $row, $reg_type);
			$j++;
			$objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow($j, $row, $common->date_format($rec[$i]['reg_date_of_recurit'], DT_FMT, ''));
			$j++;
			$objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow($j, $row, $rec[$i]['reg_participant_id']);
			$j++;
			
			for($k=0; $k<$cols; $k++)
			{
				$objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow($j, $row, $cupn_rec[$k]['coupn_participant_id']);
				$j++;
			}
			$objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow($j, $row, $rec[$i]['reg_age']);
			$j++;
			$objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow($j, $row, $rec[$i]['gen_name']);
			$j++;		
			
			
			$row++;
		}
		// Rename worksheet
		$objPHPExcel->getActiveSheet()->setTitle('RD STAT Report');
		$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
		//$objPHPExcel->getActiveSheet()->protectCells('A12:ZZ12', 'PHPExcel');
		//$objPHPExcel->getActiveSheet()->getStyle('B10')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);

		// Set active sheet index to the first sheet, so Excel opens this as the first sheet
		$objPHPExcel->setActiveSheetIndex(0);


		// Redirect output to a client’s web browser (Excel5)
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename="RD STAT Report.xls"');
		header('Cache-Control: max-age=0');

		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
		$objWriter->save('php://output');
	}
	elseif($attr == "print")
	{
		echo '<table border="0" cellpadding="0" cellspacing="0" style="width: 100%; line-height: 40px;" align="center" class="display" id="example2">
			<thead>';
		echo '<tr>
				<td><strong>Study ID</strong></td>
				<td><strong>Type</strong></td>
				<td><strong>Registration Date</strong></td>
				<td><strong>Incoupon</strong></td>';			
				for($i=0; $i<$cols; $i++)
					echo '<td><strong>Outcoupon'.($i+1).'</strong></td>';
				echo '
					<td><strong>Age</strong></td>
					<td><strong>Gender</strong></td>';
					
		echo '</tr>
			</thead>
			<tbody>';
		
		for($i=0; $i<count($rec); $i++)
		{
			$type = $rec[$i]['reg_participant_type'];
			if($type == "S")
			{
				$reg_type = "Seed";
				$reg_type_val = "1";
			}
			elseif($type == "W")
			{
				$reg_type = "Recruit";
				$reg_type_val = "0";
			}
			
			$qry = "SELECT * FROM custom_coupon_master WHERE coupn_reg_id = '".$rec[$i]['reg_participant_id']."'";
			$common->sql->query($qry, SQL_ALL, SQL_ASSOC);
			$cupn_rec = $common->sql->record;
			echo '<tr>
					<td>'.$rec[$i]['reg_participant_id'].'</td>
					<td>'.$reg_type.'</td>
					<td>'.$common->date_format($rec[$i]['reg_date_of_recurit'], DT_FMT, '').'</td>
					<td>'.$rec[$i]['reg_participant_id'].'</td>';
				   /*
					for($k=0; $k<count($lab_rec); $k++)
					{
						$qry = "SELECT * FROM custom_lab_mapping
									INNER JOIN custom_lab_master ON (custom_lab_master.lab_id = custom_lab_mapping.lab_mapng_mstr_id)
									INNER JOIN custom_lab_samples ON (custom_lab_samples.lab_smple_tst_id = custom_lab_master.lab_id)
									INNER JOIN custom_lab_result_master ON (custom_lab_result_master.lab_result_code = custom_lab_samples.lab_smple_result_id)
								WHERE lab_spot_test = 'Y' AND lab_smple_tst_id = '".$lab_rec[$k]['lab_id']."' 
									AND lab_smple_part_id = '".$rec[$i]['reg_participant_id']."'";
						$common->sql->query($qry, SQL_ALL, SQL_ASSOC);
						$lab_samp_rec = $common->sql->record;
						$cnt = count($lab_samp_rec);
						
						if($cnt == 0)
							echo '<td>&nbsp;</td>';
						else
							echo '<td>'.$lab_samp_rec[0]['lab_result_name'].'</td>';
												
					}
					*/
					for($j=0; $j<$cols; $j++)
						echo '<td>'.$cupn_rec[$j]['coupn_participant_id'].'</td>';
					echo '
						<td>'.$rec[$i]['reg_age'].'</td>
						<td>'.$rec[$i]['gen_name'].'</td>';					
						
		 /* $recloc= $rec[$i]['recruit_loc'];
			if($recloc == 1)
			{
				$rec_loc = "Transport (A Mandi)";
				
			}
			elseif($recloc == 2)
			{
				$rec_loc = "Gorakhnath-10";
				
			}
			echo
           		'<td>'.$rec_loc.'</td>'; 
				*/
			echo '</tr>';
		}
		echo '
			</tbody>
			</table>';
	}
	else
	{
		$disp_header = '
			<br/><br/>
			<table border="0" cellpadding="0" cellspacing="0" style="width: 100%; line-height: 10px;">
				<tr>
					<th align="center"><font size="13"><strong>RD Stat Report</strong></font></th>
				</tr>
				<tr>
					<td><font size="10"><strong> Survey Title : </strong> '.$rec[0]['sur_title'].'</font></td>
				</tr>
				<tr>
					<td><font size="10"><strong> Report as on : </strong> '.$prd_to.'</font></td>
				</tr>
			</table>
			<br><br>';
		$disp_table = '<table border="1" cellpadding="0" cellspacing="0" style="width: 100%; line-height: 10px;" align="center">
			<thead>';
		$disp_table .= '<tr>
				<td><strong>Study ID</strong></td>
				<td><strong>Type</strong></td>
				<td><strong>Registration Date</strong></td>
				<td><strong>Incoupon</strong></td>';				
				for($i=0; $i<$cols; $i++)
					$disp_table .= '<td><strong>Outcoupon'.($i+1).'</strong></td>';
				$disp_table .= '
					<td><strong>Age</strong></td>
					<td><strong>Gender</strong></td>';					
		$disp_table .= '</tr>
			</thead>
			<tbody>';
		
		for($i=0; $i<count($rec); $i++)
		{
			$type = $rec[$i]['reg_participant_type'];
			if($type == "S")
			{
				$reg_type = "Seed";
				$reg_type_val = "1";
			}
			elseif($type == "W")
			{
				$reg_type = "Recruit";
				$reg_type_val = "0";
			}
			
			$qry = "SELECT * FROM custom_coupon_master WHERE coupn_reg_id = '".$rec[$i]['reg_participant_id']."'";
			$common->sql->query($qry, SQL_ALL, SQL_ASSOC);
			$cupn_rec = $common->sql->record;
			$disp_table .= '<tr>
					<td>'.$rec[$i]['reg_participant_id'].'</td>
					<td>'.$reg_type.'</td>
					<td>'.$common->date_format($rec[$i]['reg_date_of_recurit'], DT_FMT, '').'</td>
					<td>'.$rec[$i]['reg_participant_id'].'</td>';
					for($k=0; $k<count($lab_rec); $k++)
					{
						$qry = "SELECT * FROM custom_lab_mapping
									INNER JOIN custom_lab_master ON (custom_lab_master.lab_id = custom_lab_mapping.lab_mapng_mstr_id)
									INNER JOIN custom_lab_samples ON (custom_lab_samples.lab_smple_tst_id = custom_lab_master.lab_id)
									INNER JOIN custom_lab_result_master ON (custom_lab_result_master.lab_result_code = custom_lab_samples.lab_smple_result_id)
								WHERE lab_spot_test = 'Y' AND lab_smple_tst_id = '".$lab_rec[$k]['lab_id']."' 
									AND lab_smple_part_id = '".$rec[$i]['reg_participant_id']."'";
						$common->sql->query($qry, SQL_ALL, SQL_ASSOC);
						$lab_samp_rec = $common->sql->record;
						$cnt = count($lab_samp_rec);
						
						if($cnt == 0)
							$disp_table .= '<td>&nbsp;</td>';
						else
							$disp_table .= '<td>'.$lab_samp_rec[0]['lab_result_name'].'</td>';
						
						/*for($l=0; $l<count($lab_samp_rec); $l++)
						{
							if($lab_rec[$k]['lab_id'] == $lab_samp_rec[$l]['lab_smple_tst_id'] && $rec[$i]['reg_participant_id'] == $lab_samp_rec[$l]['lab_smple_part_id'])
								$disp_table .= '<td>'.$lab_samp_rec[$l]['lab_result_name'].'</td>';
						}*/
					}
					for($j=0; $j<$cols; $j++)
						$disp_table .= '<td>'.$cupn_rec[$j]['coupn_participant_id'].'</td>';
					$disp_table .= '
						<td>'.$rec[$i]['reg_age'].'</td>
						<td>'.$rec[$i]['gen_name'].'</td>';													
			$disp_table .= '</tr>';
		}
		$disp_table .= '
			</tbody>
			</table>';
		
		$query = "SELECT * FROM custom_settings";
		$common->sql->query($query, SQL_ALL, SQL_ASSOC);
		$stngs_rec = $common->sql->record[0];
		
		$stngs_org_name = $stngs_rec['stngs_org_name'];
		$stngs_org_addrs = $stngs_rec['stngs_org_addrs'];
		$stngs_org_phone = $stngs_rec['stngs_org_phone'];
		$stngs_org_mail = $stngs_rec['stngs_org_mail'];
		$stngs_org_website = $stngs_rec['stngs_org_website'];
		
		$pdf = '';
		$pdf = new TCPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
		
		// set document information
		$pdf->SetCreator(PDF_CREATOR);
		$pdf->SetAuthor('YRG Care');
		$pdf->SetTitle('Expense Report');
		$pdf->SetSubject('TCPDF Tutorial');
		$pdf->SetKeywords('TCPDF, PDF, example, test, guide');
		
		// set default header data
		$pdf->SetHeaderData(PDF_HEADER_LOGO, '30', $stngs_org_name, "<font size=\"9\">".$stngs_org_addrs."<br/><b>T : </b>".$stngs_org_phone."<br/><b>E : </b>".$stngs_org_mail."  <b>  Web : </b>".$stngs_org_website."</font>");
		// $pdf->SetHeaderData(PDF_HEADER_LOGO, PDF_HEADER_LOGO_WIDTH, PDF_HEADER_TITLE.' 001', PDF_HEADER_STRING);
		
		// set header and footer fonts
		/*$pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', 20, 'L', true));
		$pdf->setHeaderStringFont(Array(PDF_FONT_NAME_MAIN, '', 10, 'L', true));
		$pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));*/
		
		$pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', 12, '', true));
		$pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));
		
		// set default monospaced font
		$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);
		
		//set margins
		$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
		$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
		$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);
		
		//set auto page breaks
		$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
		
		//set image scale factor
		$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
		
		//set some language-dependent strings
		$pdf->setLanguageArray($l);
		
		// ---------------------------------------------------------
		
		// set font
		$pdf->SetFont('dejavusans', '', 8);
		
		// add a page
		$pdf->AddPage('L');
		
		$dt_fmt = str_replace("mm", "m", DT_FMT);
		$dt_fmt = str_replace("dd", "d", $dt_fmt);
		$dt_fmt = str_replace("yyyy", "Y", $dt_fmt);
		$dt = date($dt_fmt);
		
		$title_rght = "<br/><br/><br/><font size=\"9\"><b>Printed from :</b> ".APP_NAME."<br/>
		<b>Printed by : </b>".USRNME."<br/>
		<b>Report Printed on : </b>".$dt."</font>";
		
		// Print text using writeHTMLCell()
		$pdf->writeHTMLCell($w=0, $h=0, $x='', $y='', $disp_header, $border=0, $ln=1, $fill=0, $reseth=true, $align='', $autopadding=true);
		$pdf->writeHTML($disp_table, true, false, true, false, '');
		$pdf->MultiCell(0, 10, $title_rght, 0, 'R', 0, 1, '', 5, false, 0, true, true, 0, 'T', false);
		$pdf->lastPage();
		$pdf->Output("RD Stat Report.pdf", "D");
	}
?>