<!DOCTYPE html>	
<?php 
############################# Display Sandharbh Wise Report To Desk ##############################################
include('dashboard_desk.php');
 require_once('convert_marathi.php');
$username = $_SESSION['User_Name'];
?>
<html>
<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("aaa".mysql_error());
				$rwx=mysql_fetch_array($qx) or die("bbb".mysql_error());
				echo "<h4 class=widgettitle>".$rwx['desk_nm']." चे संदर्भ प्रकारानुसार रिपोर्ट्स </h4>";?>
				<form method="post" action="">
					<?php 	##################################### Display Counter Page #######################################	
					$id=$_REQUEST['id'];
					if($id=="")
						{
					        convert_marathi();
							$qre=("SELECT t.desk_id, i.ltr_type, sm.sandharbh_nm, COUNT(*) AS TOTAL 
							FROM incoming_letters_transaction i, sandharbh_master sm, tapal_to_dpt_transaction t , desk_transaction d 
							where i.ltr_type=sm.sandhrbh_id 
							AND d.dpt_ltr_id = t.dpt_ltr_id 
							AND d.desk_id_from=(Select desk_id from login where login_id='$username')
							AND i.ltr_id=t.ltr_id 
							GROUP BY sandharbh_nm") or die("aaaaaaaaaaa".mysql_error());
							
							
							 
							 $rsut=mysql_query($qre) or die("".mysql_error());
							?>
							 <table id='dyntable' class="table table-bordered">
								<thead>
								<tr>
                            <th class="head0 center" style="width:160px;">संदर्भ प्रकार नाव</th>
                            <th class="head1 center">एकूण </th>
								</tr>
								</thead>
							<?php	
							while($rws = mysql_fetch_array($rsut))
							{
								$id=$rws['ltr_type'];
								?>
								<tbody>
								<tr class="gradeX">
								<?php 
								echo "<td class='center'><a href='report_sandharbha.php?id=$id'>".$rws['sandharbh_nm']."</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 t.desk_id, i.ltr_type, sm.sandharbh_nm, COUNT(*) AS TOTAL 
							FROM incoming_letters_transaction i, sandharbh_master sm, tapal_to_dpt_transaction t , desk_transaction d 
							where i.ltr_type=sm.sandhrbh_id 
							AND d.dpt_ltr_id = t.dpt_ltr_id 
							AND d.desk_id_from=(Select desk_id from login where login_id='$username')
							AND i.ltr_id=t.ltr_id 
							GROUP BY sandharbh_nm") or die("aaaaaaaaaaa".mysql_error());
							$rsut=mysql_query($qre) or die("".mysql_error());
							echo "<tr>
									<th>संदर्भ प्रकार नाव</th>
									<th>एकूण </th>
								</tr>";	
							while($rws = mysql_fetch_array($rsut))
							{
								$id=$rws['ltr_type'];	
								echo "<tr>
								<td>".$rws['sandharbh_nm']."</td><td>". $rws['TOTAL']."</td> </tr>";             
							}
							echo "</table></center></div>";	
						} 
							################################## End Of Counter Page ###################		
					else if( $id != "")
						{	
							################ Display Counter Wise Details ##############################
							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 action='report_sandharbha.php?id=$id' method='post'>";
							$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 * FROM desk_transaction, incoming_letters_transaction, tapal_to_dpt_transaction where tapal_to_dpt_transaction.desk_id=(select desk_id from login where login_id='$username') AND incoming_letters_transaction.ltr_type='$id' AND desk_transaction.dpt_ltr_id=tapal_to_dpt_transaction.dpt_ltr_id AND incoming_letters_transaction.ltr_id=tapal_to_dpt_transaction.ltr_id") or die("sdfdfds".mysql_error());*/
								$result222=mysql_query("SELECT i.ltr_inword_no,i.receive_dt,i.ref_no,i.ref_dt,t.dpt_inword_no,t.dpt_ltr_rec_dt,d.desk_ltr_inword_no,d.desk_ltr_rec_dt,i.sender_name,i.subject,d.ltr_approval,d.ltr_status,
								d.action_taken,d.update_date,d.dispatched_dt,t.dept_id_from, t.sent_to_desk_dt, t.assign_dpt,t.desk_approval,di.dept_id_to,di.dsk_id_to
								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 d ON d.dpt_ltr_id = t.dpt_ltr_id 
								LEFT OUTER JOIN dispatched_transaction di ON di.desk_ltr_id = d.desk_ltr_id 
								where d.desk_id_from=(select desk_id from login where login_id='$username') 
								AND i.ltr_type='$id' ") 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 * FROM desk_transaction, incoming_letters_transaction, tapal_to_dpt_transaction where tapal_to_dpt_transaction.desk_id=(select desk_id from login where login_id='$username') AND incoming_letters_transaction.ltr_type='$id' AND desk_transaction.dpt_ltr_id=tapal_to_dpt_transaction.dpt_ltr_id AND incoming_letters_transaction.ltr_id=tapal_to_dpt_transaction.ltr_id AND (incoming_letters_transaction.receive_dt BETWEEN '$date1' AND '$date2')") or die("aaww".mysql_error());*/
								$result222=mysql_query("SELECT i.ltr_inword_no,i.receive_dt,i.ref_no,i.ref_dt,t.dpt_inword_no,t.dpt_ltr_rec_dt,d.desk_ltr_inword_no,d.desk_ltr_rec_dt,i.sender_name,i.subject,d.ltr_approval,d.ltr_status,
								d.action_taken,d.update_date,d.dispatched_dt,t.dept_id_from, t.sent_to_desk_dt, t.assign_dpt,t.desk_approval,di.dept_id_to,di.dsk_id_to
								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 d ON d.dpt_ltr_id = t.dpt_ltr_id 
								LEFT OUTER JOIN dispatched_transaction di ON di.desk_ltr_id = d.desk_ltr_id 
								where d.desk_id_from=(select desk_id from login where login_id='$username') 
								AND i.ltr_type='$id' 
								AND i.ltr_id=t.ltr_id AND (i.receive_dt BETWEEN '$date1' AND '$date2')") or die("asasas".mysql_error());
							}
							//$num=mysql_num_rows($result222);
							//echo $num."aaaaaaaaaaaaaaaaaaaaaaaa";
							echo "<table id='dyntable' class='table table-bordered'>
							<thead>
							<tr>
							<tr>
                            <th class='head0 center'>पोलीस अधिक्षक <br> कार्यालय<br>आवक_क्रमांक<br>व_दिनांक</th>
                            <th class='head1 center'>संदर्भ पाठवलेली<br>क्रमांक_व_दिनांक</th>
							<th class='head0 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='head0 center'>पाठवलेली/अद्यावत <br>केलेली दि.  </th>
							</tr>
							</thead>";
							while($test = mysql_fetch_array($result222))
							{	
								$dpt_date 		= date('d-m-Y',strtotime($test['dpt_ltr_rec_dt']));
								$ref_date 		= date('d-m-Y',strtotime($test['ref_dt']));
								$rec_date 		= date('d-m-Y',strtotime($test['sent_to_desk_dt']));
								$dis_date 		= date('d-m-Y',strtotime($test['dispatched_dt']));
								$updt= date('d-m-Y',strtotime($test['update_date']));
								$rcv_dt		= date('d-m-Y',strtotime($test['receive_dt']));
								$id1 = $test['ltr_inword_no'];
								$id2 = $test['assign_dept'];
								$id3 = $test['sandarbh_prakar'];
								$sts=$test['ltr_status'];
								$depti=$test['dept_id_from'];
								$qdpt=mysql_query("Select dept_nm from department_master Where dept_id='$depti'") or die("".mysql_error());
									$dept=mysql_fetch_array($qdpt);
								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 class='center'>".$dept['dept_nm']."<br>".$test['dpt_inword_no']."</font><br>".$dpt_date."</td>";
								if($test['desk_approval']=='pending')
								{
									echo "<td class='center'><font color='red'>डेस्कने स्वीकारणे बाकी </font><br>".$rec_date ."</td>"; 
								}
								else
								{
									echo "<td class='center'>".$test['desk_ltr_inword_no']."</font><br>".$rec_date ."</td>";
								}
								echo "<td class='center'>".$test['subject']."</td>
								<td class='center'>".$test['sender_name']."</td>";
								/////////////////////////////////////////////////
								if($test['ltr_status']=='')
								{
									echo "<td class='center'><font color='red'>कार्यवाही करणे बाकी</font></td>"; 
								}
								else
								{
									echo "<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')
								{
									$todeptid=$test['dept_id_to'];
									$qdpt=mysql_query("Select dept_nm from department_master Where dept_id='$todeptid'")or die("".mysql_error());
									$dept=mysql_fetch_array($qdpt);
									echo "<td class='center'>".$test['ltr_approval']." to<br>".$dept['dept_nm']."</td>";
								}
								else if($test['ltr_status']=='dispatched' && $test['ltr_approval']=='transferred_todesk')
								{
									$todsk=$test['dsk_id_to'];
									$qq=mysql_query("SELECT desk_nm from  desk_master where desk_id='$todsk' ");
									$rr=mysql_fetch_array($qq);
									echo "<td class='center'>transferred to<br>".$rr['desk_nm']."</td>";
								}
								else{
									echo "<td class='center'>".$test['ltr_status']."</td>";
								}
								//////////////////////////////////////////////////////
								if($sts=='dispatched')
								{
									echo "<td class='center'>".$dis_date."</font></td>";
								}
								else if($sts=='updated'){
									echo "<td class='center'>".$updt."</font></td>";
								}
								else if($sts=='')
								{
									echo "<td class='center'><font color='red'>कार्यवाही करणे बाकी</font></td>"; 
								}
								echo "</tr>";
							}			
							//mysql_close();
							echo"   </tbody>
							</table>";
							
							echo "<div id='divToPrint' style='display:none;'>
							<table id='export' border='1px' style='margin-left:50px;'>
							<tr><td colspan='9'><center><h4>". $rwq['desk_nm'] ."चे संदर्भ प्रकारानुसार रिपोर्ट्स </h4></center></td></tr>";
							$qx= mysql_query("SELECT * FROM desk_master WHERE desk_id = (select desk_id from login where login_id='$username')");
							$rwx=mysql_fetch_array($qx);
							echo "<center><h4>".$rwx['desk_nm']." चे संदर्भ प्रकारानुसार रिपोर्ट्स </h4></center>";
							
							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 * FROM desk_transaction, incoming_letters_transaction, tapal_to_dpt_transaction where tapal_to_dpt_transaction.desk_id=(select desk_id from login where login_id='$username') AND incoming_letters_transaction.ltr_type='$id' AND desk_transaction.dpt_ltr_id=tapal_to_dpt_transaction.dpt_ltr_id AND incoming_letters_transaction.ltr_id=tapal_to_dpt_transaction.ltr_id") or die("sdfdfds".mysql_error());*/
								$result222=mysql_query("SELECT i.ltr_inword_no,i.receive_dt,i.ref_no,i.ref_dt,t.dpt_inword_no,t.dpt_ltr_rec_dt,d.desk_ltr_inword_no,d.desk_ltr_rec_dt,i.sender_name,i.subject,d.ltr_approval,d.ltr_status,
								d.action_taken,d.update_date,d.dispatched_dt,t.dept_id_from, t.sent_to_desk_dt, t.assign_dpt,t.desk_approval,di.dept_id_to,di.dsk_id_to
								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 d ON d.dpt_ltr_id = t.dpt_ltr_id 
								LEFT OUTER JOIN dispatched_transaction di ON di.desk_ltr_id = d.desk_ltr_id 
								where d.desk_id_from=(select desk_id from login where login_id='$username') 
								AND i.ltr_type='$id' ") 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 * FROM desk_transaction, incoming_letters_transaction, tapal_to_dpt_transaction where tapal_to_dpt_transaction.desk_id=(select desk_id from login where login_id='$username') AND incoming_letters_transaction.ltr_type='$id' AND desk_transaction.dpt_ltr_id=tapal_to_dpt_transaction.dpt_ltr_id AND incoming_letters_transaction.ltr_id=tapal_to_dpt_transaction.ltr_id AND (incoming_letters_transaction.receive_dt BETWEEN '$date1' AND '$date2')") or die("aaww".mysql_error());*/
								$result222=mysql_query("SELECT i.ltr_inword_no,i.receive_dt,i.ref_no,i.ref_dt,t.dpt_inword_no,t.dpt_ltr_rec_dt,d.desk_ltr_inword_no,d.desk_ltr_rec_dt,i.sender_name,i.subject,d.ltr_approval,d.ltr_status,
								d.action_taken,d.update_date,d.dispatched_dt,t.dept_id_from, t.sent_to_desk_dt, t.assign_dpt,t.desk_approval,di.dept_id_to,di.dsk_id_to
								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 d ON d.dpt_ltr_id = t.dpt_ltr_id 
								LEFT OUTER JOIN dispatched_transaction di ON di.desk_ltr_id = d.desk_ltr_id 
								where d.desk_id_from=(select desk_id from login where login_id='$username') 
								AND i.ltr_type='$id' 
								AND i.ltr_id=t.ltr_id AND (i.receive_dt BETWEEN '$date1' AND '$date2')") or die("asasas".mysql_error());
							}
							echo "<tr>
                            <th>पोलीस अधिक्षक <br> कार्यालय<br>आवक_क्रमांक</th>
                            <th>संदर्भ पाठवलेली<br>क्रमांक_व_दिनांक</th>
							<th>विभागीय आवक क्र.<br>आवक_दिनांक</th>
							<th>डेस्कचा आवक क्र <br>प्राप्त_दिनांक</th>
                            <th>विषय</th>
							<th>कोणाकडून <br>प्राप्त_झाला</th>
							<th>केलेली कार्यवाही </th>
                            <th>पत्राची स्थिती </th>
							<th>पाठवलेली/अद्यावत <br>केलेली_प्राप्त </th>
							</tr>
							</thead>";
							while($test = mysql_fetch_array($result222))
							{	
								$dpt_date 		= date('d-m-Y',strtotime($test['dpt_ltr_rec_dt']));
								$ref_date 		= date('d-m-Y',strtotime($test['ref_dt']));
								$rec_date 		= date('d-m-Y',strtotime($test['sent_to_desk_dt']));
								$dis_date 		= date('d-m-Y',strtotime($test['dispatched_dt']));
								$updt= date('d-m-Y',strtotime($test['update_date']));
								$id1 = $test['ltr_inword_no'];
								$id2 = $test['assign_dept'];
								$id3 = $test['sandarbh_prakar'];
								$sts=$test['ltr_status'];
								
								$depti=$test['dept_id_from'];
								$qdpt=mysql_query("Select dept_nm from department_master Where dept_id='$depti'") or die("".mysql_error());
									$dept=mysql_fetch_array($qdpt);
								echo "<tr>
								<td style='text-align:center;'>".$test['ltr_inword_no']."</td>
								<td style='text-align:center;'>".$test['ref_no']."</font><br>".$ref_date."</td>
								<td style='text-align:center;'>".$dept['dept_nm']."<br>".$test['dpt_inword_no']."</font><br>".$dpt_date."</td>";
								
								if($test['desk_approval']=='pending')
								{
									echo "<td class='center'>डेस्कने स्वीकारणे बाकी <br>".$rec_date ."</td>"; 
								}
								else
								{
									echo "<td class='center'>".$test['desk_ltr_inword_no']."</font><br>".$rec_date ."</td>";
								}
								echo "<td style='text-align:center;'>".$test['subject']."</td>
								<td style='text-align:center;'>".$test['sender_name']."</td>";
								
								if($test['ltr_status']=='')
								{
									echo "<td class='center'>कार्यवाही करणे बाकी </td>"; 
								}
								else
								{
									echo "<td class='center'>".$test['action_taken']."</td>";
								}
								
								if($test['ltr_status']=='')
								{
									echo "<td class='center'>कार्यवाही करणे बाकी </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')
								{
									$todeptid=$test['dept_id_to'];
									$qdpt=mysql_query("Select dept_nm from department_master Where dept_id='$todeptid'")or die("".mysql_error());
									$dept=mysql_fetch_array($qdpt);
									echo "<td class='center'>".$test['ltr_approval']." to<br>".$dept['dept_nm']."</td>";
								}
								else if($test['ltr_status']=='dispatched' && $test['ltr_approval']=='transferred_todesk')
								{
									$todsk=$test['dsk_id_to'];
									$qq=mysql_query("SELECT desk_nm from  desk_master where desk_id='$todsk' ");
									$rr=mysql_fetch_array($qq);
									echo "<td class='center'>transferred to<br>".$rr['desk_nm']."</td>";
								}
								else{
									echo "<td class='center'>".$test['ltr_status']."</td>";
								}
								///////////////////////
								if($sts=='dispatched')
								{
									echo "<td class='center'>".$dis_date."</font></td>";
								}
								else if($sts=='updated'){
									echo "<td class='center'>".$updt."</font></td>";
								}
								else if($sts=='')
								{
									echo "<td class='center'>कार्यवाही करणे बाकी </td>"; 
								}
								echo "</tr>";
							}			
							//mysql_close();
							echo"   </tbody>
							</table></div>";
							
						}
						##################### End Of Display Counter Wise 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>
					<br /><br />
                    <?php include_once('footer.php'); ?>
            </div><!--maincontentinner-->
        </div><!--maincontent-->
    </div><!--rightpanel--> 
</div><!--mainwrapper-->
</body>
</html>
