<?php
ini_set('max_execution_time', 300);
session_start();
require_once('connection/db.php');
require_once('convert_marathi.php');
$username = $_SESSION['User_Name'];

######################## dept Home Page ##########################
$query=mysql_query("SELECT dept_id from login where login_id='$username' ");
$r=mysql_fetch_array($query);
$dpt_id=$r['dept_id'];

$query1=mysql_query("SELECT desk_flag from department_master where dept_id='$dpt_id' ");
$r1=mysql_fetch_array($query1);
$desk_flag=$r1['desk_flag'];

if($desk_flag=='Y' || $desk_flag=='y')
{


require_once('dashboard_department.php');
?>
<!DOCTYPE html>
<html>
<head>
<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>
<style>
#lefts{
    text-align: left;
}

#cent
{
    text-align: center;
}
</style>
<script src="tableToExcel.js"></script>

<link type="text/css" rel="stylesheet" href="style2.css" />
 
</head>

<body onload="fun()">
    <div class="rightpanel"> 
        <ul class="breadcrumbs">
            <li><a href="dashboard_tapal.php"><i class="iconfa-home"></i></a> <span class="separator"></span></li>
            <li><a href="dashboard_tapal.php">Tables</a> <span class="separator"></span></li>
            <li>Table Dynamic</li>
        </ul>
        <div class="pageheader">
            <div class="pageicon"><span class="iconfa-table"></span></div>
            <div class="pagetitle">
                <h5>Tables</h5>
                <h1>Welcome <?php echo $username;?> User</h1>
            </div>
        </div><!--pageheader-->
        <div class="maincontent">
            <div class="maincontentinner">
                <h4 class="widgettitle">डेस्कनुसार पत्राचे रिपोर्ट्स </h4>
				<form action="" method="post">


								<!--############################### POPUP CODE ###################################-->

							<div id="overlay"></div>
							<div id="popup" style=" width:600px;overflow-y:scroll;overflow-x:scroll;">
								<div class="popupcontrols popupHeader" >
									<span class="header_title"> अलर्ट </span>
									<span id="popupclose">X</span>
								</div>
								<div class="popupcontent">
									<div id="parent">
										
										<center>	
										<table style="width:450px;">
                    
									<?php
									$date123=date("d-m-Y");
									
									convert_marathi();
									$result421 = mysql_query("SELECT d.desk_ltr_id, d.desk_id_from, d.action_taken, d.desk_ltr_rec_dt, d.desk_ltr_inword_no, t.dpt_inword_no, t.dpt_ltr_id, i.priority_days, t.dpt_ltr_rec_dt, t.dpt_inword_no, t.desk_id, t.dept_id_from, i.subject, d. update_date, d.dispatched_dt, d.ltr_approval, di.addressed_to, d.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, d.scan_doc, di.outword_no, di.dispatched_to, d.ltr_category, d.assign_no 
									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 t.desk_id='0' 
									AND t.dept_id_from = (SELECT dept_id from login where login_id = '$username') ");
									
									$n1=mysql_num_rows($result421);
									
									if($n1!=0)
									{
										
											echo "<tr><td> &nbsp&nbsp&nbsp&nbsp <b> आवक_क्र. </b></td>
												<td>&nbsp&nbsp&nbsp&nbsp <b> संदर्भ_क्र. </b></td>
												<td>&nbsp&nbsp&nbsp&nbsp <b> विषय </b></td>
												<td>&nbsp&nbsp&nbsp&nbsp <b> पत्राची स्थिती </b></td>
												<td>&nbsp&nbsp&nbsp&nbsp <b> मुदत </b></td>
												</tr>
												<tr><td colspan=3>&nbsp;</td></tr>";
												
												
											while($row2=mysql_fetch_array($result421))
											{ 
												$priority_days=$row2['priority_days'];
												$receive_dt	=strtotime($row2['receive_dt']);
												
												
												
												$to_dt=strtotime("+".$priority_days." days",$receive_dt);
												//echo date("d-m-Y",$to_dt)."aa";
												
												$frm_dt=strtotime("-2 days",$to_dt);
												
												if(strtotime($date123)>=$frm_dt && strtotime($date123)<=$to_dt)
												{
													//echo strtotime($date123).">=".$frm_dt;
													
													echo "<tr style=height:20px;>
															<td style=height:18px;>&nbsp&nbsp&nbsp&nbsp".$row2['ltr_inword_no']."</td>
															<td> &nbsp&nbsp&nbsp&nbsp".$row2['ref_no']."</td>
															<td style=height:18px;>&nbsp&nbsp&nbsp&nbsp".$row2['subject']."</td>
															<td> &nbsp&nbsp&nbsp&nbsp डेस्क लावणे बाकी </td>
															<td> &nbsp&nbsp&nbsp&nbsp ".date('d-m-Y',$to_dt)." </td>
														</tr>";
												}
												
											//echo date("d-m-Y",$frm_dt)."<br>";
											
												
											}
									}
									else
									{
										echo "<h3> No records found....</h3>";
									}
									
									
									?>
									</table>
								</center>
								
								</div>
							</div>
						</div>

			<!--##################################################################-->		








			
					<table id="dyntable" class="table table-bordered">
						<thead>
						<tr>
								<th class="head1 center"> डेस्कचे नाव </th>
								<th class="head0 center"> डेस्कला पाठवलेले पत्र</th>
								<th class="head1 center"> स्वीकृत </th>
								<th class="head1 center"> कार्यवाही केलेले  </th>
								<th class="head1 center"> रिटर्न केलेले पत्र</th>
								<th class="head0 center"> कार्यवाही न केलेले</th>
								<th class="head0 center"> स्वीकारणे बाकी </th>
							</tr>
						</thead>
						<tbody> 
						<?php 
						
							$qry2=mysql_query("SELECT * from desk_master where dept_id='$dpt_id' ");
							while($rs=mysql_fetch_array($qry2))
								{	
									$htotal=0;
									
									echo "<tr class=gradeX>";
									$desk_id=$rs['desk_id'];
									
									echo "<td class=center>".$rs['desk_nm']."</td>";
									
									###############################################################
															       //Pending
									###############################################################
									$qry1=mysql_query("Select  COUNT(*) AS TOTAL 
									FROM incoming_letters_transaction i LEFT OUTER JOIN  tapal_to_dpt_transaction t ON i.ltr_id = t.ltr_id 
									WHERE t.desk_approval='pending'
									AND t.desk_id='$desk_id' ") OR die("aaaa".mysql_error());
									$rw11=mysql_fetch_array($qry1);
								
									//echo "<td class=center><a href=report_desk_pending_approve.php?no=1&user=2&deptid=$deptid>".$rw11['TOTAL']."</a></td>";
									//echo "<td></td>";
									$htotal=$htotal+$rw11['TOTAL'];
									$htotal1=$htotal1+$rw11['TOTAL'];
									###############################################################
															       //Accepted
									###############################################################
									
										$qry12=mysql_query("Select COUNT(*) AS TOTAL 
										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='$desk_id' ") OR die("aaaa".mysql_error());
																			
										$rw11=mysql_fetch_array($qry12);
										$htotal=$htotal+$rw11['TOTAL'];
										$htotal1=$htotal1+$rw11['TOTAL'];
										//////////////////////////////////////////////////////////डेस्कला पाठवलेले पत्र/////////////////////////////////////////////////////
										
											echo "<td class=center><a href=report_desk_pending_approve_dpt.php?no=5&user=2&deskid=$desk_id>".$htotal."</a></td>";
										
										
										//////////////////////////////////////
										echo "<td class=center><a href=report_desk_pending_approve_dpt.php?no=2&user=2&deskid=$desk_id>".$rw11['TOTAL']."</a></td>";
										$htotal2=$htotal2+$rw11['TOTAL'];
									/*else if($rs['desk_flag']=='N' || $rs['desk_flag']=='n')
									{
										$qry12=mysql_query("Select  COUNT(*) AS TOTAL 
										FROM incoming_letters_transaction i LEFT OUTER JOIN tapal_to_dpt_transaction t ON i.ltr_id=t.ltr_id 
										WHERE t.dept_id_from='$deptid'
										AND (i.status='approve' OR (i.status='pending' AND (i.rtn_choice='Department' OR i.rtn_choice='Tapal'))) ") OR die("aaaa".mysql_error());
										
										$rw11=mysql_fetch_array($qry12);
										$htotal=$htotal+$rw11['TOTAL'];
										
										///////////////////////////////////////////////////////////////////विभागास पाठवलेले पत्र/////////////////////////////////////////////////////////////////////////////////////////////
										if($rs['desk_flag']=='Y' || $rs['desk_flag']=='y')
										{
											echo "<td class=center><a href=report_desk_pending_approve.php?no=5&user=2&deptid=$deptid>".$htotal."</a></td>";
										}
										else if($rs['desk_flag']=='N' || $rs['desk_flag']=='n')
										{
											echo "<td class=center><a href=report_desk_pending_approve.php?flag=n&no=5&user=2&deptid=$deptid>".$htotal."</a></td>";
										}
										//////////////////////////////////////
										echo "<td class=center><a href=report_desk_pending_approve.php?flag=n&no=2&user=2&deptid=$deptid>".$rw11['TOTAL']."</a></td>";
									}*/
									
									//////////////end of accpeted///////////////////////
									
									###############################################################
													 //Dispatched,updated or transfered
									###############################################################
										
										$qry13=mysql_query("Select  COUNT(*) AS TOTAL 
										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='$desk_id'
										AND (d.ltr_status='updated' OR d.ltr_status='dispatched')") OR die("aaaa".mysql_error());
										$rw13=mysql_fetch_array($qry13);
										echo "<td class=center><a href=report_desk_pending_approve_dpt.php?no=3&user=2&deskid=$desk_id>".$rw13['TOTAL']."</a></td>";	
									    $htotal3=$htotal3+$rw13['TOTAL'];
									###############################################################
													     //Returned
									###############################################################	
									$qry13=mysql_query("Select  COUNT(*) AS TOTAL 
									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='$desk_id'
									AND (
											(d.ltr_status='closed') OR ( t.desk_approval='returned')
										)") OR die("aaaa".mysql_error());
									$rw13=mysql_fetch_array($qry13);
									echo "<td class=center><a href=report_desk_pending_approve_dpt.php?no=6&user=2&deskid=$desk_id>".$rw13['TOTAL']."</a></td>";
									  $htotal5=$htotal5+$rw13['TOTAL'];
									###############################################################
															       //Accepted But Not Worked
									###############################################################
									
										//not assigned to desk OR assigned to desk but approval is pending
									/*	$qry3=mysql_query("Select  COUNT(*) AS TOTAL 
										FROM tapal_to_dpt_transaction t, incoming_letters_transaction i 
										WHERE t.returned_choice='' 
										AND t.ltr_id=i.ltr_id 
										AND t.dept_id_from='$deptid' 
										AND (t.status='approve')
										AND (
												(t.desk_id='') OR 
												(t.desk_id='0') OR 
												(t.desk_id!='0' AND t.desk_approval='pending')
											)") OR die("aaaa".mysql_error());
										$rw11=mysql_fetch_array($qry3); */
									
										//desk accepted but not taken any action
										$qry4=mysql_query("Select  COUNT(*) AS TOTAL 
										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='$desk_id'
										AND d.ltr_approval='Approve' 
										AND  d.ltr_status=''
										") OR die("aaaa".mysql_error());
										$rw14=mysql_fetch_array($qry4);
										
										//$rz=$rw11['TOTAL']+$rw14['TOTAL'];
										$rz=$rw14['TOTAL'];
									    $htotal6=$htotal6+$rw14['TOTAL'];
										echo "<td class=center><a href=report_desk_pending_approve_dpt.php?no=4&user=2&deskid=$desk_id>".$rz."</a></td>";
								

									###############################################################
															       //Pending
									###############################################################
									$qry1=mysql_query("Select  COUNT(*) AS TOTAL FROM   tapal_to_dpt_transaction t
									WHERE t.desk_approval='pending'
									AND t.desk_id='$desk_id'") OR die("aaaa".mysql_error());
									$rw11=mysql_fetch_array($qry1);
									echo "<td class=center><a href=report_desk_pending_approve_dpt.php?no=1&user=2&deskid=$desk_id>".$rw11['TOTAL']."</a></td></tr>";
									   $htotal7=$htotal7+$rw11['TOTAL'];
								}
			
								?>
						</tbody>
						<tfoot>
						<?php
						echo "<tr>
								<th> एकूण</th>
								<th class=center>".$htotal1."</th>
								<th class=center>".$htotal2."</th>
			                    <th class=center>".$htotal3."</th>
								<th class=center>".$htotal5."</th>
								<th class=center>".$htotal6."</th>
								<th class=center>".$htotal7."</th>
							</tr>";
							?>
						</tfoot>
						</table>
					
					<?php
					echo "<div id='divToPrint' style='display:none;'>";
							echo ' <center><h2>पोलीस अधिक्षक कार्यालय <br> जि.रायगड <h2></center>';
							echo "<center><table id='export' border='1px' style='margin-left:50px;'>
							<tr><td colspan='8'><center> डेस्कनुसार पत्राचे रिपोर्ट्स </center></td></tr>
							<tr>
								<th> डेस्कचे नाव </th>
								<th> डेस्कला पाठवलेले पत्र</th>
								<th> स्वीकृत </th>
								<th> कार्यवाही केलेले  </th>
								<th> रिटर्न केलेले पत्र</th>
								<th> कार्यवाही न केलेले</th>
								<th> स्वीकारणे बाकी </th>
							</tr>";
						 
							$qry2=mysql_query("SELECT * from desk_master where dept_id='$dpt_id'");
							while($rs=mysql_fetch_array($qry2))
								{	
									$htotal=0;
									echo "<tr class=gradeX>";
									$desk_id=$rs['desk_id'];
									
									echo "<td class=center>".$rs['desk_nm']."</td>";
									
									###############################################################
															       //Pending
									###############################################################
									$qry1=mysql_query("Select  COUNT(*) AS TOTAL FROM   tapal_to_dpt_transaction t
									WHERE t.desk_approval='pending'
									AND t.desk_id='$desk_id' ") OR die("aaaa".mysql_error());
									$rw11=mysql_fetch_array($qry1);
								
									//echo "<td class=center><a href=report_desk_pending_approve.php?no=1&user=2&deptid=$deptid>".$rw11['TOTAL']."</a></td>";
									//echo "<td></td>";
									$htotal=$htotal+$rw11['TOTAL'];
									
									###############################################################
															       //Accepted
									###############################################################
									
										$qry12=mysql_query("Select COUNT(*) AS TOTAL 
										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='$desk_id' ") OR die("aaaa".mysql_error());
																			
										$rw11=mysql_fetch_array($qry12);
										$htotal=$htotal+$rw11['TOTAL'];
										
										///////////////////////////////////////////////////////////////////डेस्कला पाठवलेले पत्र/////////////////////////////////////////////////////////////////////////////////////////////
										
											echo "<td class=center>".$htotal."</td>";
										
										
										//////////////////////////////////////
										echo "<td class=center>".$rw11['TOTAL']."</td>";
									
									/*else if($rs['desk_flag']=='N' || $rs['desk_flag']=='n')
									{
										$qry12=mysql_query("Select  COUNT(*) AS TOTAL 
										FROM incoming_letters_transaction i LEFT OUTER JOIN tapal_to_dpt_transaction t ON i.ltr_id=t.ltr_id 
										WHERE t.dept_id_from='$deptid'
										AND (i.status='approve' OR (i.status='pending' AND (i.rtn_choice='Department' OR i.rtn_choice='Tapal'))) ") OR die("aaaa".mysql_error());
										
										$rw11=mysql_fetch_array($qry12);
										$htotal=$htotal+$rw11['TOTAL'];
										
										///////////////////////////////////////////////////////////////////विभागास पाठवलेले पत्र/////////////////////////////////////////////////////////////////////////////////////////////
										if($rs['desk_flag']=='Y' || $rs['desk_flag']=='y')
										{
											echo "<td class=center><a href=report_desk_pending_approve.php?no=5&user=2&deptid=$deptid>".$htotal."</a></td>";
										}
										else if($rs['desk_flag']=='N' || $rs['desk_flag']=='n')
										{
											echo "<td class=center><a href=report_desk_pending_approve.php?flag=n&no=5&user=2&deptid=$deptid>".$htotal."</a></td>";
										}
										//////////////////////////////////////
										echo "<td class=center><a href=report_desk_pending_approve.php?flag=n&no=2&user=2&deptid=$deptid>".$rw11['TOTAL']."</a></td>";
									}*/
									
									//////////////end of accpeted///////////////////////
									
									###############################################################
													 //Dispatched,updated or transfered
									###############################################################
										
										$qry13=mysql_query("Select  COUNT(*) AS TOTAL 
										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='$desk_id'
										AND (d.ltr_status='updated' OR d.ltr_status='dispatched')") OR die("aaaa".mysql_error());
										$rw13=mysql_fetch_array($qry13);
										echo "<td class=center>".$rw13['TOTAL']."</td>";	
								
									
									###############################################################
													     //Returned
									###############################################################	
									$qry13=mysql_query("Select  COUNT(*) AS TOTAL 
									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='$desk_id'
									AND (
											(d.ltr_status='closed') OR ( t.desk_approval='returned')
										)") OR die("aaaa".mysql_error());
									$rw13=mysql_fetch_array($qry13);
									echo "<td class=center>".$rw13['TOTAL']."</td>";
									###############################################################
															       //Accepted But Not Worked
									###############################################################
									
										//not assigned to desk OR assigned to desk but approval is pending
									/*	$qry3=mysql_query("Select  COUNT(*) AS TOTAL 
										FROM tapal_to_dpt_transaction t, incoming_letters_transaction i 
										WHERE t.returned_choice='' 
										AND t.ltr_id=i.ltr_id 
										AND t.dept_id_from='$deptid' 
										AND (t.status='approve')
										AND (
												(t.desk_id='') OR 
												(t.desk_id='0') OR 
												(t.desk_id!='0' AND t.desk_approval='pending')
											)") OR die("aaaa".mysql_error());
										$rw11=mysql_fetch_array($qry3); */
									
										//desk accepted but not taken any action
										$qry4=mysql_query("Select  COUNT(*) AS TOTAL 
										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='$desk_id'
										AND d.ltr_approval='Approve' 
										AND  d.ltr_status=''
										") OR die("aaaa".mysql_error());
										$rw14=mysql_fetch_array($qry4);
										
										//$rz=$rw11['TOTAL']+$rw14['TOTAL'];
										$rz=$rw14['TOTAL'];
										
										echo "<td class=center>".$rz."</td>";
								

									###############################################################
															       //Pending
									###############################################################
									$qry1=mysql_query("Select  COUNT(*) AS TOTAL FROM   tapal_to_dpt_transaction t
									WHERE t.desk_approval='pending'
									AND t.desk_id='$desk_id'") OR die("aaaa".mysql_error());
									$rw11=mysql_fetch_array($qry1);
									echo "<td class=center>".$rw11['TOTAL']."</td></tr>";
									
								} 	?>
						</table></center>
						</div>
					<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>  
			
			
			
			 <script>

		function fun()
		{

			// Initialize Variables
			var closePopup = document.getElementById("popupclose");
			
			var overlay = document.getElementById("overlay");
			var popup = document.getElementById("popup");
			var button = document.getElementById("button");
		   
			// Show Overlay and Popup

				overlay.style.display = 'block';
				popup.style.display = 'block';

		}

		


		  // Initialize Variables
			var closePopup = document.getElementById("popupclose");
			var overlay = document.getElementById("overlay");
			var popup = document.getElementById("popup");
			var button = document.getElementById("button");
			// Close Popup Event
		   
			   // Close Popup Event
			closePopup.onclick = function() {
				overlay.style.display = 'none';
				popup.style.display = 'none';
			};

		</script>	
            <?php include_once('footer.php');?>
            </div><!--maincontentinner-->
        </div><!--maincontent-->
    </div><!--rightpanel-->
</div><!--mainwrapper-->
</body>
</html>
<?php 
}
else
{
	//require_once('new_dashboard_department.php');
	echo '<script language="JavaScript"> window.location.href ="acceptletter.php" </script>';
}
?>