<?php
require_once('dashboard_tapal.php');
require_once('convert_marathi.php');
######################## Tapal Home Page ##########################
$username = $_SESSION['User_Name'];
?>
<!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> 
</head>
    <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">		
					<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 
						$htotal1=0;
							$qry2=mysql_query("SELECT * from department_master");
							while($rs=mysql_fetch_array($qry2))
								{	
									$htotal=0;
									echo "<tr class=gradeX>";
									$deptid=$rs['dept_id'];
									
									echo "<td class=center>".$rs['dept_nm']."</td>";
									
									###############################################################
															       //Pending
									###############################################################
									$qry1=mysql_query("Select  COUNT(*) AS TOTAL FROM  incoming_letters_transaction i, login l 
									WHERE i.dept_id!='0' 
									AND (i.status='pending') 
									AND l.login_id='$username' 
									AND i.dept_id='$deptid'") OR die("aaaa".mysql_error());
									$rw11=mysql_fetch_array($qry1);
									
									$htotal=$htotal+$rw11['TOTAL'];
									$htotal1=$htotal1+$rw11['TOTAL'];
									###############################################################
															       //Accepted
									###############################################################
									if($rs['desk_flag']=='Y' || $rs['desk_flag']=='y')
									{
										$qry12=mysql_query("Select COUNT(*) AS TOTAL FROM tapal_to_dpt_transaction t, incoming_letters_transaction i, login l 
										WHERE i.ltr_id=t.ltr_id 
										AND  l.login_id='$username' 
										AND t.dept_id_from='$deptid'
										 ") OR die("aaaa".mysql_error());
																			
										$rw11=mysql_fetch_array($qry12);
										$htotal=$htotal+$rw11['TOTAL'];
										$htotal1=$htotal1+$rw11['TOTAL'];
									$htotal8=$htotal8+$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?no=2&user=2&deptid=$deptid>".$rw11['TOTAL']."</a></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'
										 ") OR die("aaaa".mysql_error());
										
										$rw11=mysql_fetch_array($qry12);
										$htotal=$htotal+$rw11['TOTAL'];
										$htotal1=$htotal1+$rw11['TOTAL'];
										$htotal8=$htotal8+$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
									###############################################################
									if($rs['desk_flag']=='Y' || $rs['desk_flag']=='y')
									{
										$qry13=mysql_query("Select  COUNT(*) AS TOTAL FROM  tapal_to_dpt_transaction t, incoming_letters_transaction i, desk_transaction d, login l 
										WHERE l.login_id='$username' 
										AND t.dept_id_from='$deptid' 
										AND t.returned_choice='' 
										AND (d.ltr_status='updated' OR d.ltr_status='dispatched')
										AND d.ltr_approval!='transferred_todesk'
										AND i.ltr_id=t.ltr_id 
										AND t.dpt_ltr_id=d.dpt_ltr_id") OR die("aaaa".mysql_error());
										$rw13=mysql_fetch_array($qry13);
										echo "<td class=center><a href=report_desk_pending_approve.php?no=3&user=2&deptid=$deptid>".$rw13['TOTAL']."</a></td>";	
										$htotal3=$htotal3+$rw13['TOTAL'];
									}
									else if($rs['desk_flag']=='N' || $rs['desk_flag']=='n')
									{
										$qry13=mysql_query("Select  COUNT(*) AS TOTAL 
										FROM  tapal_to_dpt_transaction t, incoming_letters_transaction i, login l 
										WHERE  l.login_id='$username' 
										AND t.dept_id_from='$deptid' 
										AND t.returned_choice='' 
										AND (t.ltr_status='updated' OR t.ltr_status='dispatched')
										AND i.ltr_id=t.ltr_id ") OR die("aaaa".mysql_error());
										$rw13=mysql_fetch_array($qry13);
										echo "<td class=center><a href=report_desk_pending_approve.php?flag=n&no=3&user=2&deptid=$deptid>".$rw13['TOTAL']."</a></td>";	
										$htotal3=$htotal3+$rw13['TOTAL'];
									}
									###############################################################
													     //Returned
									###############################################################	
									$qry13=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='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.php?no=6&user=2&deptid=$deptid>".$rw13['TOTAL']."</a></td>";
									$htotal4=$htotal4+$rw13['TOTAL'];
									###############################################################
															       //Accepted But Not Worked
									###############################################################
									if($rs['desk_flag']=='Y' || $rs['desk_flag']=='y')
									{
										//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  tapal_to_dpt_transaction t, incoming_letters_transaction i, desk_transaction d 
										WHERE t.returned_choice='' 
										AND t.ltr_id=i.ltr_id 
										AND d.dpt_ltr_id=t.dpt_ltr_id 
										AND t.dept_id_from='$deptid' 
										AND d.ltr_approval='Approve' 
										AND d.desk_id_from=t.desk_id 
										AND  (d.ltr_status='' OR d.ltr_status='closed')
										") OR die("aaaa".mysql_error());
										$rw14=mysql_fetch_array($qry4);
										
										//desk returned to its dept
										$qry5=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='pending'
										") OR die("aaaa".mysql_error());
										$rw15=mysql_fetch_array($qry5);
										
										$rz=$rw11['TOTAL']+$rw14['TOTAL']+$rw15['TOTAL'];
										
										echo "<td class=center><a href=report_desk_pending_approve.php?no=4&user=2&deptid=$deptid>".$rz."</a></td>";
										$htotal5=$htotal5+$rz;
									}
									
									else if($rs['desk_flag']=='N' || $rs['desk_flag']=='n')
									{
										$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.ltr_status=''") OR die("aaaa".mysql_error());
										$rw11=mysql_fetch_array($qry3);
										echo "<td class=center><a href=report_desk_pending_approve.php?flag=n&no=4&user=2&deptid=$deptid>".$rw11['TOTAL']."</a></td>";
										$htotal5=$htotal5+$rw11['TOTAL'];
									}

									###############################################################
															       //Pending
									###############################################################
									$qry1=mysql_query("Select  COUNT(*) AS TOTAL FROM  incoming_letters_transaction i, login l 
									WHERE i.dept_id!='0' 
									AND (i.status='pending') 
									AND l.login_id='$username' 
									AND i.dept_id='$deptid'") 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></tr>";
									$htotal6=$htotal6+$rw11['TOTAL'];
								}
								?>
						</tbody>
						<tfoot>
						<?php
						echo "<tr>
								<th> एकूण</th>
								<th class=center>".$htotal1."</th>
								<th class=center>".$htotal8."</th>
			                    <th class=center>".$htotal3."</th>
								<th class=center>".$htotal4."</th>
								<th class=center>".$htotal5."</th>
								<th class=center>".$htotal6."</th>
							</tr>";
							?>
						</tfoot>
					</table>
					<?php
					echo "<div id='divToPrint' style='display:none;'>";
					 echo '<center><h2>पोलीस अधिक्षक कार्यालय, <br> जि.बीड <h2>';		
					echo "<table id='export' border='1px' >
							<tr><td colspan='7'><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 department_master");
							while($rs=mysql_fetch_array($qry2))
								{	
									$htotal=0;
									echo "<tr class=gradeX>";
									$deptid=$rs['dept_id'];
									
									echo "<td class=center>".$rs['dept_nm']."</td>";
									
									###############################################################
															       //Pending
									###############################################################
									$qry1=mysql_query("Select  COUNT(*) AS TOTAL FROM  incoming_letters_transaction i, login l 
									WHERE i.dept_id!='0' 
									AND (i.status='pending') 
									AND l.login_id='$username' 
									AND i.dept_id='$deptid'") OR die("aaaa".mysql_error());
									$rw11=mysql_fetch_array($qry1);
									//echo "<td class=center><a href=report_desk_pending_approve.php?no=1&user=1&deptid=$deptid>".$rw11['TOTAL']."</a></td>";
									//echo "<td></td>";
									$htotal=$htotal+$rw11['TOTAL'];
									
									###############################################################
															       //Accepted
									###############################################################
									if($rs['desk_flag']=='Y' || $rs['desk_flag']=='y')
									{
										$qry12=mysql_query("Select COUNT(*) AS TOTAL FROM tapal_to_dpt_transaction t, incoming_letters_transaction i, login l 
										WHERE i.ltr_id=t.ltr_id 
										AND  l.login_id='$username' 
										AND t.dept_id_from='$deptid'
										 ") 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>".$htotal."</td>";
										}
										else if($rs['desk_flag']=='N' || $rs['desk_flag']=='n')
										{
											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'
										 ") 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>".$htotal."</td>";
										}
										else if($rs['desk_flag']=='N' || $rs['desk_flag']=='n')
										{
											echo "<td class=center>".$htotal."</td>";
										}
										//////////////////////////////////////
										echo "<td class=center>".$rw11['TOTAL']."</td>";
									}
									
									//////////////end of accpeted///////////////////////
									
									###############################################################
													 //Dispatched,updated or transfered
									###############################################################
									if($rs['desk_flag']=='Y' || $rs['desk_flag']=='y')
									{
										$qry13=mysql_query("Select  COUNT(*) AS TOTAL FROM  tapal_to_dpt_transaction, incoming_letters_transaction, desk_transaction, login WHERE   login.login_id='$username' AND tapal_to_dpt_transaction.dept_id_from='$deptid' AND tapal_to_dpt_transaction.returned_choice='' AND (desk_transaction.ltr_status='updated' OR desk_transaction.ltr_status='dispatched')AND incoming_letters_transaction.ltr_id=tapal_to_dpt_transaction.ltr_id AND tapal_to_dpt_transaction.dpt_ltr_id=desk_transaction.dpt_ltr_id") OR die("aaaa".mysql_error());
										$rw13=mysql_fetch_array($qry13);
										echo "<td class=center>".$rw13['TOTAL']."</td>";	
									}
									else if($rs['desk_flag']=='N' || $rs['desk_flag']=='n')
									{
										$qry13=mysql_query("Select  COUNT(*) AS TOTAL 
										FROM  tapal_to_dpt_transaction t, incoming_letters_transaction i, login l 
										WHERE  l.login_id='$username' 
										AND t.dept_id_from='$deptid' 
										AND t.returned_choice='' 
										AND (t.ltr_status='updated' OR t.ltr_status='dispatched')
										AND i.ltr_id=t.ltr_id ") 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  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='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
									###############################################################
									if($rs['desk_flag']=='Y' || $rs['desk_flag']=='y')
									{
										//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  tapal_to_dpt_transaction t, incoming_letters_transaction i, desk_transaction d 
										WHERE t.returned_choice='' 
										AND t.ltr_id=i.ltr_id 
										AND d.dpt_ltr_id=t.dpt_ltr_id 
										AND t.dept_id_from='$deptid' 
										AND d.ltr_approval='Approve' 
										AND d.desk_id_from=t.desk_id 
										AND  (d.ltr_status='' OR d.ltr_status='closed')
										") OR die("aaaa".mysql_error());
										$rw14=mysql_fetch_array($qry4);
										
										//desk returned to its dept
										$qry5=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='pending'
										") OR die("aaaa".mysql_error());
										$rw15=mysql_fetch_array($qry5);
										
										$rz=$rw11['TOTAL']+$rw14['TOTAL']+$rw15['TOTAL'];
										
										echo "<td class=center>".$rz."</td>";
									}
									else if($rs['desk_flag']=='N' || $rs['desk_flag']=='n')
									{
										$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.ltr_status=''") OR die("aaaa".mysql_error());
										$rw11=mysql_fetch_array($qry3);
										echo "<td class=center>".$rw11['TOTAL']."</td>";
									}

									###############################################################
															       //Pending
									###############################################################
									$qry1=mysql_query("Select  COUNT(*) AS TOTAL FROM  incoming_letters_transaction i, login l 
									WHERE i.dept_id!='0' 
									AND (i.status='pending') 
									AND l.login_id='$username' 
									AND i.dept_id='$deptid'") 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>  
            <?php include_once('footer.php');?>
            </div><!--maincontentinner-->
        </div><!--maincontent-->
    </div><!--rightpanel-->
</div><!--mainwrapper-->
</body>
</html>