<!DOCTYPE html>
<?php 
include('dashboard_desk.php');
require_once('convert_marathi.php');
$username = $_SESSION['User_Name'];
?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<script type="text/javascript">     
function PrintDiv() {    
var divToPrint = document.getElementById('divToPrint');
var popupWin = window.open('', '_blank', 'width=600,height=600');
popupWin.document.open();
popupWin.document.write('<html><body onload="window.print()">' + divToPrint.innerHTML + '</html>');
popupWin.document.close();
}
</script>
<script src="tableToExcel.js"></script>
</head>
<body>
<div class="mainwrapper">   
    <div class="rightpanel">
            <ul class="breadcrumbs">
				<li><a href=""><i class="iconfa-home"></i></a> <span class="separator"></span></li>
				<li><a href="">Tables</a> <span class="separator"></span></li>
				<li>जतन प्रकारानुसार रिपोर्ट्स </li>
			</ul>
			<div class="pageheader">
				<div class="pageicon"><span class="iconfa-table"></span></div>
				<div class="pagetitle">
					<h5>Tables</h5>
					<h1>जतन प्रकारानुसार रिपोर्ट्स </h1>
				</div>
			</div><!--pageheader-->
			<div class="maincontent">
				<div class="maincontentinner">
					<?php
					$qx= mysql_query("SELECT * FROM desk_master WHERE desk_id = (select desk_id from login where login_id='$username')") or die("".mysql_error());
					$rwx=mysql_fetch_array($qx) or die("".mysql_error());;
					$id=$_REQUEST['id'];
					echo "<h4 class=widgettitle>".$rwx['desk_nm']." चे जतन प्रकारानुसार रिपोर्ट्स (  ".$id." )  </h4>";?>
					<form method="post" action="">
						<?php 		
						$id=$_REQUEST['id'];
						###################################### Display Counter ########################
						if($id=="")
							{
								convert_marathi(); 
								$qre=("SELECT d.vargawari, COUNT(*) AS TOTAL 
								FROM desk_transaction d, tapal_to_dpt_transaction t, incoming_letters_transaction i
								WHERE ( d.vargawari!='' AND d.ltr_status !='')
								AND t.desk_id=(select desk_id from login where login_id='$username') 
								and d.dpt_ltr_id=t.dpt_ltr_id 
								AND i.ltr_id=t.ltr_id 
								GROUP BY vargawari");
								 
								$rsut=mysql_query($qre) or die("aaammm".mysql_error());
								echo "<table id='dyntable' class='table table-bordered'>
									<thead>
									<tr>
								<th class='head0 center' style='width:160px;'>जतन प्रकार </th>
								<th class='head1 center'>एकूण </th>
									</tr>
									</thead>";
								while($rws = mysql_fetch_array($rsut))
								{
									$id=$rws['vargawari'];
									?>
									<tbody>
									<tr class="gradeX">
									<?php 
									echo "<td class='center'><a href='report_jatanprakar.php?id=$id'>".$rws['vargawari']."</a></td>	<td class='center'>". $rws['TOTAL']."</td> </tr>";       
									}?>
								</tbody>
								</table>
								<?php
								echo "<div id='divToPrint' style='display:none;'>
							<center><table id='export' border='1px' style='margin-left:50px;'>
							<tr><td colspan='9'><center><h4>". $rwx['desk_nm'] ." चे जतन प्रकारानुसार रिपोर्ट्स</h4></center></td></tr>";
							
							echo ' <center><h2>पोलीस अधिक्षक कार्यालय, <br> जि.रायगड <h2></center>';
								convert_marathi(); 
								$qre=("SELECT d.vargawari, COUNT(*) AS TOTAL 
								FROM desk_transaction d, tapal_to_dpt_transaction t, incoming_letters_transaction i
								WHERE ( d.vargawari!='' AND d.ltr_status !='')
								AND t.desk_id=(select desk_id from login where login_id='$username') 
								and d.dpt_ltr_id=t.dpt_ltr_id 
								AND i.ltr_id=t.ltr_id 
								GROUP BY vargawari");
								$rsut=mysql_query($qre) or die("".mysql_error());
								echo "<tr>
										<th>जतन प्रकार </th>
										<th>एकूण </th>
									</tr>";
								while($rws = mysql_fetch_array($rsut))
								{
									$id=$rws['vargawari'];
									echo "<tr>"; 
									echo "<td style='text-align:center;'>".$rws['vargawari']."</td><td style='text-align:center;'>". $rws['TOTAL']."</td></tr>";             
								}
								echo "</table></center></div>";
					} 
					################################## End Of Display Counter #####################
					
					################################# Display Details  ########################################
					else if( $id!="")
					{
						convert_marathi();
						echo "<form name='dept_sandarbh' action='' method='post'>
								<div align='center'><br><table align='center' cellpadding='2' cellspacing='0' class='myClass'>
								<table>
									<tr>											  
										<td align='center'><label style='width:110px;'>Start date</label></td>
										<td align='center'><input type='text' name='start_date' class='input-small'/></td>
										<td align='center'><label style='width:110px;'>End Date</label></td>
										<td align='center'><input type='text' name='end_date' class='input-small'/></td>
									</tr>
									<tr>
										<td colspan='6'>
										<div align='center'>
											<input type='submit' onClick='return sadyasthitiDates()' name='ok' value='GO'/>
											<input type='reset' value='CLEAR' name='clear'/>
										</div>
										</td>
									</tr>
								</table>
							</div>				
							</form>
							<form>";
							$date123	= $_POST['start_date'];
							$date1	= date('Y-m-d',strtotime($date123));
							$date223	= $_POST['end_date'];
							$date2	= date('Y-m-d',strtotime($date223));
							//$date22	= date('d-m-Y',strtotime($date2));
							
							if($date123==''&& $date223=='')
							{
								echo '<br><br><center><h4>दिनांक <font color="#59c600">'.$date123.'</font>'.' पासून दिनांक '.'<font color="#59c600">'.$date223.'</font></center></h4><br>';
								$id1 = $date1;
								$id2= $date2;
								$result222=mysql_query("SELECT de.desk_ltr_id, de.desk_id_from, de.action_taken, de.desk_ltr_rec_dt, de.desk_ltr_inword_no, t.dpt_inword_no, t.dpt_ltr_id, t.dpt_ltr_rec_dt,t.desk_approval,t.sent_to_desk_dt,  t.dpt_inword_no, t.desk_id, t.dept_id_from, i.subject, de. 	update_date, de.dispatched_dt, de.ltr_approval, di.addressed_to, de.ltr_status, i.sender_name, i.ltr_id, i.ltr_inword_no, i.receive_dt, i.ref_dt, i.ref_no, i.dept_id, i.document, de.scan_doc, di.outword_no, di.dispatched_to, de.ltr_category, de.assign_no,de.nasti_nashta_dt
								FROM  incoming_letters_transaction  i  LEFT OUTER JOIN tapal_to_dpt_transaction t ON i.ltr_id = t.ltr_id
								LEFT OUTER JOIN desk_transaction de ON de.dpt_ltr_id = t.dpt_ltr_id LEFT OUTER JOIN dispatched_transaction di ON di.desk_ltr_id = de.desk_ltr_id
								where t.desk_id=(select desk_id from login where login_id='$username')
								AND (de.vargawari='$id' AND de.ltr_status!='')") or die("sdfdfds".mysql_error());
							}
							else
							{
								echo '<br><br><center><h4>दिनांक <font color="#59c600">'.$date123.'</font>'.' पासून दिनांक '.'<font color="#59c600">'.$date223.'</font></center></h4><br>';
								$id1 = $date1;
								$id2= $date2;
								$result222=mysql_query("SELECT de.desk_ltr_id, de.desk_id_from, de.action_taken, de.desk_ltr_rec_dt, de.desk_ltr_inword_no, t.dpt_inword_no, t.dpt_ltr_id, t.dpt_ltr_rec_dt,t.desk_approval,t.sent_to_desk_dt,  t.dpt_inword_no, t.desk_id, t.dept_id_from, i.subject, de. 	update_date, de.dispatched_dt, de.ltr_approval, di.addressed_to, de.ltr_status, i.sender_name, i.ltr_id, i.ltr_inword_no, i.receive_dt, i.ref_dt, i.ref_no, i.dept_id, i.document, de.scan_doc, di.outword_no, di.dispatched_to, de.ltr_category, de.assign_no,de.nasti_nashta_dt 
								FROM  incoming_letters_transaction  i  LEFT OUTER JOIN tapal_to_dpt_transaction t ON i.ltr_id = t.ltr_id
								LEFT OUTER JOIN desk_transaction de ON de.dpt_ltr_id = t.dpt_ltr_id LEFT OUTER JOIN dispatched_transaction di ON di.desk_ltr_id = de.desk_ltr_id
								where t.desk_id=(select desk_id from login where login_id='$username')
								AND (de.vargawari='$id' AND de.ltr_status!='')
								AND (i.receive_dt BETWEEN '$date1' AND '$date2') ") or die("sdfdfds".mysql_error());
							}							
							echo "<table id='dyntable' class='table table-bordered'>
								<thead>
								<tr>
                            <th class='head0 center'>पोलीस अधिक्षक <br> कार्यालय<br>आवक_क्रमांक<br>व_दिनांक</th>
                            <th class='head1 center'>संदर्भ <br>क्रमांक_व_दिनांक</th>
							<th class='head1 center'>विभाग<br>प्राप्त दिनांक</th>
							<th class='head0 center'>देस्क आवक_क्रमांक<br>व_आवक_दिनांक</th>
                            <th class='head1 center'>विषय</th>
							<th class='head0 center'>कोणाकडून <br>प्राप्त_झाला</th>
							<th class='head1 center'>केलेली कार्यवाही </th>
							<th class='head1 center'>पत्राची स्थिती </th>
							<th class='head1 center'>नस्ती नष्ट दिनांक </th>
								</tr>
								</thead>
								<tbody>";
						while($test = mysql_fetch_array($result222))
						{
							$ref_date 		= date('d-m-Y',strtotime($test['ref_dt']));
							$rec_date 		= date('d-m-Y',strtotime($test['desk_ltr_rec_dt']));
							$rcv_dt		= date('d-m-Y',strtotime($test['receive_dt']));
							$dptrecdt=date('d-m-Y',strtotime($test['dpt_ltr_rec_dt']));
							if($test['dis_dt']=='')
							{
								$dis_date = date('d-m-Y',strtotime($test['sent_dt']));
							}
							else
							{
								$dis_date="कार्यवाही करणे बाकी " ;		
							}
							$qry90=mysql_query("select dept_nm from department_master where dept_id='$test[dept_id_from]'");
							$rs90=mysql_fetch_array($qry90);
							echo "<tr class='gradeX'>
							<td class='center'>".$test['ltr_inword_no']."<br>".$rcv_dt."</td>
							<td class='center'>".$test['ref_no']."</font><br>".$ref_date."</td>
							<td style='text-align:center;'>".$test['dpt_inword_no']."<br>".$dptrecdt."</td>
							<td style='text-align:center;'>".$test['desk_ltr_inword_no']."<br>".$rec_date."</td>
							<td class='center'>".$test['subject']."</td>
							<td class='center'>".$test['sender_name']."</td>
							<td class='center'>".$test['action_taken']."</td>";
							
							if($test['ltr_status']=='')
								{
									echo "<td class='center'><font color='red'>कार्यवाही करणे बाकी</font></td>"; 
								}
								else if($test['ltr_status']=='dispatched' && $test['ltr_approval']=='Approve')
								{
									echo "<td class='center'>".$test['ltr_status']."</td>";
								}
								else if($test['ltr_status']=='dispatched' && $test['ltr_approval']=='transferred')
								{
									echo "<td class='center'>".$test['ltr_approval']."</td>";
								}
								else{
									echo "<td class='center'>".$test['ltr_status']."</td>";
								}
							
								///////////nasti nashta date////////////
							if($id=='अ')
							{
								echo "<td class='center'>LifeTime</td>";
							}
							else
							{
								echo "<td class='center'>".$test['nasti_nashta_dt']."</td>";
							}
							echo "</tr>";
						}
						//mysql_close();
						echo"   </tbody>
							</table>";
						
						echo  "<div id='divToPrint' style='display:none;'>
							<center><table id='export' border='1px' style='margin-left:50px;'>
							<tr><td colspan='9'><center>". $rwx['desk_nm'] ." चे जतन प्रकारानुसार रिपोर्ट्स ( ".$id." ) <br>दिनांक ".$date123." पासून दिनांक ".$date223."</center></td></tr>";
							
							echo ' <center><h2>पोलीस अधिक्षक कार्यालय, <br> जि.रायगड <h2></center>';
						convert_marathi();
						if($date123==''&& $date223=='')
							{
								$id1 = $date1;
								$id2= $date2;
								$result222=mysql_query("SELECT de.desk_ltr_id, de.desk_id_from, de.action_taken, de.desk_ltr_rec_dt, de.desk_ltr_inword_no, t.dpt_inword_no, t.dpt_ltr_id, t.dpt_ltr_rec_dt,t.desk_approval,t.sent_to_desk_dt,  t.dpt_inword_no, t.desk_id, t.dept_id_from, i.subject, de. 	update_date, de.dispatched_dt, de.ltr_approval, di.addressed_to, de.ltr_status, i.sender_name, i.ltr_id, i.ltr_inword_no, i.receive_dt, i.ref_dt, i.ref_no, i.dept_id, i.document, de.scan_doc, di.outword_no, di.dispatched_to, de.ltr_category, de.assign_no,de.nasti_nashta_dt 
								FROM  incoming_letters_transaction  i  LEFT OUTER JOIN tapal_to_dpt_transaction t ON i.ltr_id = t.ltr_id
								LEFT OUTER JOIN desk_transaction de ON de.dpt_ltr_id = t.dpt_ltr_id LEFT OUTER JOIN dispatched_transaction di ON di.desk_ltr_id = de.desk_ltr_id
								where t.desk_id=(select desk_id from login where login_id='$username')
								AND (de.vargawari='$id' AND de.ltr_status!='')") or die("sdfdfds".mysql_error());
							}
							else
							{
								$id1 = $date1;
								$id2= $date2;
								$result222=mysql_query("SELECT de.desk_ltr_id, de.desk_id_from, de.action_taken, de.desk_ltr_rec_dt, de.desk_ltr_inword_no, t.dpt_inword_no, t.dpt_ltr_id, t.dpt_ltr_rec_dt,t.desk_approval,t.sent_to_desk_dt,  t.dpt_inword_no, t.desk_id, t.dept_id_from, i.subject, de. 	update_date, de.dispatched_dt, de.ltr_approval, di.addressed_to, de.ltr_status, i.sender_name, i.ltr_id, i.ltr_inword_no, i.receive_dt, i.ref_dt, i.ref_no, i.dept_id, i.document, de.scan_doc, di.outword_no, di.dispatched_to, de.ltr_category, de.assign_no,de.nasti_nashta_dt 
								FROM  incoming_letters_transaction  i  LEFT OUTER JOIN tapal_to_dpt_transaction t ON i.ltr_id = t.ltr_id
								LEFT OUTER JOIN desk_transaction de ON de.dpt_ltr_id = t.dpt_ltr_id LEFT OUTER JOIN dispatched_transaction di ON di.desk_ltr_id = de.desk_ltr_id
								where t.desk_id=(select desk_id from login where login_id='$username')
								AND (de.vargawari='$id' AND de.ltr_status!='')
								AND (i.receive_dt BETWEEN '$date1' AND '$date2') ") or die("sdfdfds".mysql_error());
							}	
						echo "
							<tr>
								<th class='head0 center'>पोलीस अधिक्षक <br> कार्यालय<br>आवक_क्रमांक<br>व_दिनांक</th>
								<th class='head1 center'>संदर्भ <br>क्रमांक_व_दिनांक</th>
								<th class='head1 center'>विभाग<br>प्राप्त दिनांक</th>
								<th class='head0 center'>देस्क आवक_क्रमांक<br>व_आवक_दिनांक</th>
								<th class='head1 center'>विषय</th>
								<th class='head0 center'>कोणाकडून <br>प्राप्त_झाला</th>
								<th class='head1 center'>केलेली कार्यवाही </th>	
								<th class='head1 center'>पत्राची स्थिती </th>
								<th class='head1 center'>नस्ती नष्ट दिनांक </th>
							</tr>";
						while($test = mysql_fetch_array($result222))
						{
							$ref_date 		= date('d-m-Y',strtotime($test['ref_dt']));
							$rec_date 		= date('d-m-Y',strtotime($test['desk_ltr_rec_dt']));
							$rcv_dt		= date('d-m-Y',strtotime($test['receive_dt']));
							$dptrecdt=date('d-m-Y',strtotime($test['dpt_ltr_rec_dt']));
							if($test['dis_dt']=='')
							{
								$dis_date = date('d-m-Y',strtotime($test['sent_dt']));
							}
							else
							{
								$dis_date="कार्यवाही करणे बाकी " ;		
							}
							$qry90=mysql_query("select dept_nm from department_master where dept_id='$test[dept_id_from]'");
							$rs90=mysql_fetch_array($qry90);
							echo "<tr class='gradeX'>
							<td class='center'>".$test['ltr_inword_no']."<br>".$rcv_dt."</td>
							<td class='center'>".$test['ref_no']."</font><br>".$ref_date."</td>
							<td style='text-align:center;'>".$test['dpt_inword_no']."<br>".$dptrecdt."</td>
							<td style='text-align:center;'>".$test['desk_ltr_inword_no']."<br>".$rec_date."</td>
							<td class='center'>".$test['subject']."</td>
							<td class='center'>".$test['sender_name']."</td>
							<td class='center'>".$test['action_taken']."</td>";
							
							if($test['ltr_status']=='')
								{
									echo "<td class='center'><font color='red'>कार्यवाही करणे बाकी</font></td>"; 
								}
								else if($test['ltr_status']=='dispatched' && $test['ltr_approval']=='Approve')
								{
									echo "<td class='center'>".$test['ltr_status']."</td>";
								}
								else if($test['ltr_status']=='dispatched' && $test['ltr_approval']=='transferred')
								{
									echo "<td class='center'>".$test['ltr_approval']."</td>";
								}
								else{
									echo "<td class='center'>".$test['ltr_status']."</td>";
								}
								
								///////////nasti nashta date////////////
								if($id=='अ')
								{
									echo "<td class='center'>LifeTime</td>";
								}
								else
								{
									echo "<td class='center'>".$test['nasti_nashta_dt']."</td>";
								}
							echo "</tr>";
						}
						//mysql_close();
						echo " </table></center></div></form>";
					}###################### End Of Display Details  ###########################################
			?>		<br>
				<p>	
					<center><input type="submit" value=" Print Page " onclick="PrintDiv();" class="btn btn-primary"/>
					<input type="button" class="btn" onclick="tableToExcel('export', 'W3C Example Table')" value="Export to Excel"></center>
				</p>
			</form>
            <?php include_once('footer.php'); ?>
            </div><!--maincontentinner-->
        </div><!--maincontent-->
    </div><!--rightpanel-->
</div><!--mainwrapper-->
</body>
</html>
