<!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>
<script type="text/javascript" src="js/adduser.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><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>
             <?php
				$re=mysql_query("Select desk_nm from desk_master where desk_id=(select desk_id from login where login_id='$username')");
				$rs=mysql_fetch_array($re);
				
				$dt=strtotime("today");
					//echo $dt;
					$date=date("d-m-Y",$dt);
					//echo "today :".$date."<br>";

					$sun=strtotime("previous sunday");
					$datesun=date("d-m-Y",$sun);
					//echo "prev sunday :".$datesun."<br>";

					$sat=strtotime("next saturday");
					$datesat=date("d-m-Y",$sat);
					//echo "next saturday :".$datesat."<br>";
					?>
                <h1><?php echo $rs['desk_nm'];?> चा गोषवारा </h1>
            </div>
        </div><!--pageheader-->
        <div class="maincontent">
            <div class="maincontentinner">
			    <h4 class="widgettitle"><?php echo $rs['desk_nm'];?>चा गोषवारा </h4>
				<!-- Code For Start Date End Date Input -->
				<form name="sadyasthiti" action="goshwara_desk.php" 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="4">
								<div align="center">
									<input type="submit" onClick="return sadyasthitiDate()" name="ok" value="GO"/>
									<input type="reset" value="CLEAR" name="clear"/>
								</div>
								</td>
							</tr>
						</table>
					</div>
				</form>
				<!-- Code For Start Date End Date Input -->
				<br><br>
					<p align="center"><font size="4" color='#0866c6' style="font-family:verdana;">गोषवारा <br><br>&nbsp;&nbsp;&nbsp;desk - 
					<?php
						convert_marathi();
						$sql ="SELECT desk_master.desk_nm FROM login,desk_master WHERE login.login_id = '$username' AND login.desk_id=desk_master.desk_id";
						$result = mysql_query($sql);
						$row = mysql_fetch_array($result);
						echo $row['desk_nm'];
						?>
						</font></p>
						<p align="center"><font size="4" color='#0866c6' style="font-family:verdana;"><br>
						<?php
						
							if(isset($_POST['ok']))
							{
								$date123	= $_REQUEST['start_date'];
								$date1	= date('Y-m-d',strtotime($date123));
								//$date12	= date('d-m-Y',strtotime($date1));
								$date223	= $_REQUEST['end_date'];
								$date2	= date('Y-m-d',strtotime($date223));
								//$date22	= date('d-m-Y',strtotime($date2));
								echo 'दिनांक <font color="#59c600">'.$date123.'</font>'.' पासून दिनांक '.'<font color="#59c600">'.$date223.'</font>';
								
								$id1 = $date1;
								$id2= $date2;
							}
							else
							{
								$date123	= $datesun;
								$date1	= date('Y-m-d',strtotime($date123));
								//$date12	= date('d-m-Y',strtotime($date1));
								$date223	= $datesat;
								$date2	= date('Y-m-d',strtotime($date223));
								//$date22	= date('d-m-Y',strtotime($date2));
								echo 'दिनांक <font color="#59c600">'.$date123.'</font>'.' पासून दिनांक '.'<font color="#59c600">'.$date223.'</font>';
								
								$id1 = $date1;
								$id2= $date2;
							}
						?></font></p><br>
						<br><br>
						<form method="post" action="">
						<?php 
							########################### Pending References ####################################
							echo"<center><table style='width:50%;' border='1' class='table table-bordered' id='sample_1' cellpadding='2' cellspacing='2'>
							<tbody role=alert aria-live=polite aria-relevant=all><tr>
							<tr>
								<td align='center' colspan='2'>
								गोषवारा &nbsp;&nbsp;&nbsp;विभाग - ".$row['desk_nm']."<br>
								दिनांक <font color='#59c600'>$date123</font> पासून दिनांक <font color='#59c600'>$date223</font>
								</td> 
							</tr>
							<tr>
								<td id='ff'>संदर्भाचा प्रकार</td>
								<td id='ff'>एकूण संदर्भ</td>
							</tr>
							<tr>
								<td><a href='goshwara_desk_view.php?desk_ltr_rec_dt=$id1&end_date=$id2&no=01' target='blank' title='Click to View'>मागील शिल्लक संदर्भ</a></td>";
					
								//accepted by desk
								$result = 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_ltr_rec_dt<'$id1' 
								and (t.desk_approval='approve' AND d.ltr_status='')
								AND d.desk_id_from = (SELECT desk_id from login where login_id = '$username')") or die("cccc".mysql_error());
								
							/*	//not accepted by desk
								$result1 = 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 (t.desk_approval='pending' AND t.sent_to_desk_dt< '$id1')
								AND t.desk_id = (SELECT desk_id from login where login_id = '$username')"); */
								while($row = mysql_fetch_array($result))
								{
										//echo"<td>".$row['total']."</td></tr>";
										$sum1 = $row['total'];
								}
							/*	while($row1 = mysql_fetch_array($result1))
								{
										//echo"<td>".$row['total']."</td></tr>";
										$sum11 = $row1['total'];
								} */
								$sum111=$sum1+$sum11;
								echo"<td>".$sum111."</td></tr>";
							########################### Letters In Current Week ########################
			
								echo"<tr>
								<td align='center'><a href='goshwara_desk_view.php?desk_ltr_rec_dt=$id1&end_date=$id2&no=02' target='blank' title='Click to View'>चालू आठवड्यातील आवक संदर्भ</a></td>";
								
								//accepted by desk
								$result1=mysql_query("SELECT count(*)as total 
								FROM tapal_to_dpt_transaction t LEFT OUTER JOIN incoming_letters_transaction i 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_ltr_rec_dt BETWEEN '$id1' AND '$id2') 
								AND d.desk_id_from = (SELECT desk_id from login where login_id = '$username')
								AND d.ltr_status!='closed'
								ORDER BY i.ltr_inword_no") or die("eeee".mysql_error());
								
							/*	//not accepted by desk
								$result22213 = 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 (t.desk_approval='pending' AND t.sent_to_desk_dt BETWEEN '$id1' AND '$id2')
								AND t.desk_id = (SELECT desk_id from login where login_id = '$username')"); */
								
								while($row = mysql_fetch_array($result1))
								{
									$sum2	=	$row['total'];
								}
							/*	while($row = mysql_fetch_array($result22213))
								{
									$sum321	=	$row['total'];
								} */
								$sum32161=$sum2+$sum321;
								echo"<td>".$sum32161."</td></tr>";
								
								$sum3 = $sum1 + $sum32161 ;
								########################### Total References ####################################
								echo"<tr><td align='center'><a href='goshwara_desk_view.php?desk_ltr_rec_dt=$id1&end_date=$id2&no=03' target='blank' title='Click to View'>एकूण संदर्भ</a></td>";
								
								///////pending////////
								//accepted by desk
								$result = 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_ltr_rec_dt<'$id1' 
								and (t.desk_approval='approve' AND d.ltr_status='')
								AND d.desk_id_from = (SELECT desk_id from login where login_id = '$username')") or die("cccc".mysql_error());
								
							/*	//not accepted by desk
								$result1 = 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 (t.desk_approval='pending' AND t.sent_to_desk_dt< '$id1')
								AND t.desk_id = (SELECT desk_id from login where login_id = '$username')"); */
								while($row = mysql_fetch_array($result))
								{
										//echo"<td>".$row['total']."</td></tr>";
										$sum1 = $row['total'];
								}
							/* while($row1 = mysql_fetch_array($result1))
								{
										//echo"<td>".$row['total']."</td></tr>";
										$sum11 = $row1['total'];
								} */
								$sum111=$sum1+$sum11;
								
								/////////current//////////
								//accepted by desk
								$result1=mysql_query("SELECT count(*)as total 
								FROM tapal_to_dpt_transaction t LEFT OUTER JOIN incoming_letters_transaction i 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_ltr_rec_dt BETWEEN '$id1' AND '$id2') 
								AND d.desk_id_from = (SELECT desk_id from login where login_id = '$username')
								AND d.ltr_status!='closed'
								ORDER BY i.ltr_inword_no") or die("eeee".mysql_error());
								
							/*	//not accepted by desk
								$result22213 = 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 (t.desk_approval='pending' AND t.sent_to_desk_dt BETWEEN '$id1' AND '$id2')
								AND t.desk_id = (SELECT desk_id from login where login_id = '$username')"); */
								
								while($row = mysql_fetch_array($result1))
								{
									$sum2	=	$row['total'];
								}
							/*	while($row = mysql_fetch_array($result22213))
								{
									$sum32321	=	$row['total'];
								} */
								
								$sum1112=$sum111+$sum2+$sum32321;
								echo"<td>".$sum1112."</td></tr>";
								
								########################### Updated References ####################################
								/*echo"<tr><td align='center'><a href='goshwara_desk_view.php?desk_ltr_rec_dt=$id1&end_date=$id2&no=04' target='blank' title='Click to View'>अद्यावत केलेले </a></td>";
								$result3 = 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=(Select desk_id from login where login_id='$username') 
								AND d.update_date BETWEEN '$id1' AND '$id2'") or die("".mysql_error()); 
							
								while($row = mysql_fetch_array($result3))
									{ 
										echo "<td>".$row['total']."</td></tr>";
										$sum13 = $row['total'];
									}
								$sum5 = $sum3 - $sum13;*/
								########################### Dispatched/updated References ###########################
								echo"<tr><td><a href='goshwara_desk_view.php?desk_ltr_rec_dt=$id1&end_date=$id2&no=05' target='blank' title='Click to View'>एकूण निर्गत संदर्भ</a></td>";
							
								$result21 = 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=(Select desk_id from login where login_id='$username') 
								AND (
										d.dispatched_dt BETWEEN '$id1' AND '$id2'
										OR d.update_date BETWEEN '$id1' AND '$id2'
									)
								") or die("".mysql_error()); 
								while($row = mysql_fetch_array($result21))
									{
										echo"<td>".$row['total']."</td></tr>";
										$sum4 = $row['total'];
									}
								$sum5 = $sum3 - $sum4;
								########################### Total Pending References ############################
								echo"<tr><td><a href='goshwara_desk_view.php?desk_ltr_rec_dt=$id1&end_date=$id2&no=10' target='blank' title='Click to View'>एकूण शिल्लक संदर्भ</a></td>";
								
							//accepted by desk
							$result4 = 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=(select desk_id from login where login_id='$username') 
							AND d.desk_ltr_rec_dt <='$id2' 
							AND d.ltr_status = ''") or die("aa".mysql_error());
						
						/*	//not accepted by desk
							$result41 = 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 (t.desk_approval='pending'  AND t.sent_to_desk_dt<='$id2')
							AND t.desk_id = (SELECT desk_id from login where login_id = '$username')") or die("bb".mysql_error()); */
								
						
							while($row = mysql_fetch_array($result4))
							{
								$sumx=$row['TOTAL'];
							}	
					/*		while($row1 = mysql_fetch_array($result41))
							{
								$sumy=$row1['TOTAL'];
							}	 */
						
							$sumxy=$sumx+$sumy;
							echo "<td>".$sumxy."<td></tr>";
									
								########################### Pending From 1 Week References ###########################
								echo"<tr><td><a href='goshwara_desk_view.php?desk_ltr_rec_dt=$id1&end_date=$id2&no=06' target='blank' title='Click to View'>एका आठवड्यातील शिल्लक संदर्भ</a></td>";
						
							//accepted by desk
							$result5=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_ltr_rec_dt BETWEEN '$id1' AND '$id2' 
									AND ( 
											 (d.ltr_category='' AND d.ltr_status='')
											 OR (d.ltr_status='')
									)
								) AND d.desk_id_from=(SELECT desk_id from login where login_id = '$username')")or die("abaaaaa".mysql_error());
							
						/*	//not accepted by desk
							$result51=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 (t.desk_approval='pending'  )
							AND t.desk_id = (SELECT desk_id from login where login_id = '$username')
							AND sent_to_desk_dt BETWEEN '$id1' AND '$id2'")or die("abaaaaa".mysql_error()); */
							
							while($row = mysql_fetch_array($result5))
							{  
								$summ=$row['total'];
							}
							
					/*		while($row1 = mysql_fetch_array($result51))
							{  
								$summ1=$row1['total'];
							} */
							$sumn=$summ+$summ1;
							 
							echo "<td>".$sumn."</td></tr>";
							
								########################### Pending From 2 Weeks References #########################
			
								echo"<tr><td><a href='goshwara_desk_view.php?desk_ltr_rec_dt=$id1&end_date=$id2&no=07' target='blank' title='Click to View'>दोन आठवड्यातील शिल्लक संदर्भ</a></td>";			
							
							$prev_date = date("Y-m-d", strtotime('-1 days', strtotime($id1)) );
							
							//accepted by desk
							$result6=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_ltr_rec_dt BETWEEN DATE_SUB('$id1',INTERVAL 7 DAY) AND '$prev_date')  
							AND ( 
											 (d.ltr_category='' AND d.ltr_status='')
											 OR (d.ltr_status='')
									)
							AND d.desk_id_from = (SELECT desk_id from login where login_id = '$username')") or die("aaaaa".mysql_error());
							
					/*		//not accepted by desk
							$result61=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 t.desk_approval='pending'
							AND t.desk_id = ( SELECT desk_id from login where login_id = '$username' )
							AND t.sent_to_desk_dt BETWEEN DATE_SUB('$id1',INTERVAL 7 DAY) AND '$prev_date'") or die("zzzzzz".mysql_error()); */
							
							while($row = mysql_fetch_array($result6))
							{
								$sum23=$row['total']; 
							}
					/*		while($row1 = mysql_fetch_array($result61))
							{
								$sum233=$row1['total'];
							} */
							
							$sum243=$sum23+$sum233;
							echo "<td>".$sum243."</td></tr>";
							
								########################### Pending From 3 Weeks References #########################
								echo"<tr><td><a href='goshwara_desk_view.php?desk_ltr_rec_dt=$id1&end_date=$id2&no=08' target='blank' title='Click to View'>तीन आठवड्यातील शिल्लक संदर्भ</a></td>";	
							
							//accepted by desk
							$result7=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_ltr_rec_dt BETWEEN DATE_SUB('$id1',INTERVAL 14 DAY) AND DATE_SUB('$id1',INTERVAL 8 DAY) )  
							AND ( 
											 (d.ltr_category='' AND d.ltr_status='')
											 OR (d.ltr_status='')
									)
							AND d.desk_id_from = (SELECT desk_id from login where login_id = '$username')") or die("bbbb".mysql_error());
						/*	
							//not accepted by desk
							$result71=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 t.desk_approval='pending'
							AND t.desk_id = ( SELECT desk_id from login where login_id = '$username' )
							AND t.sent_to_desk_dt BETWEEN DATE_SUB('$id1',INTERVAL 14 DAY) AND DATE_SUB('$id1',INTERVAL 8 DAY)") or die("abbbb".mysql_error()); */
							
							while($row = mysql_fetch_array($result7))
							{ 
								$sum44=$row['total'];
							}
					/*		while($row1 = mysql_fetch_array($result71))
							{ 
								$sum441=$row1['total'];
							}  */
							$sum01=$sum44+$sum441;
							echo "<td>".$sum01."</td></tr>";
							
								#################### Pending From More Than 3 Weeks References #####################
								echo"<tr><td><a href='goshwara_desk_view.php?desk_ltr_rec_dt=$id1&end_date=$id2&no=09' target='blank' title='Click to View'>तीन आठवड्यावरील शिल्लक संदर्भ</a></td>";			
							
							//accepted by desk
							$result8 = 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_ltr_rec_dt< DATE_SUB('$id1',INTERVAL 14 DAY)
								)  
							AND ( 
											 (d.ltr_category='' AND d.ltr_status='')
											 OR (d.ltr_status='')
									)
							AND d.desk_id_from = (SELECT desk_id from login where login_id = '$username')") or die("bbbb".mysql_error());
							
						/*	//not accepted by desk
							$result81 = 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 t.desk_approval='pending'
							AND t.desk_id = ( SELECT desk_id from login where login_id = '$username' )
							AND t.sent_to_desk_dt< DATE_SUB('$id1',INTERVAL 14 DAY)") or die("bbbb".mysql_error());
							*/	
							while($row = mysql_fetch_array($result8))
							{ 
								$sum99=$row['total'];
							}
						/*	while($row1 = mysql_fetch_array($result81))
							{ 
								$sum991=$row1['total'];
							} */
							
							$sum992=$sum99+$sum991;
							echo"<td>".$sum992."</td></tr>";
							
							?>
								</tbody>
								</table>
								</center>
							
							<?php	
								echo" <div id='divToPrint' style='display:none;'>
										<center><table id='export' border='1px' style='margin-left:50px;'>";
							
							echo ' <center><h2>पोलीस अधीक्षक कार्यालय, <br> जि.रायगड </h2></center>';			
							convert_marathi();
							$sql ="SELECT desk_master.desk_nm FROM login,desk_master WHERE login.login_id = '$username' AND login.desk_id=desk_master.desk_id";
							$result = mysql_query($sql);
							$row = mysql_fetch_array($result);
							
							if(isset($_POST['ok']))
							{
								$date123	= $_REQUEST['start_date'];
								$date1	= date('Y-m-d',strtotime($date123));
								//$date12	= date('d-m-Y',strtotime($date1));
								$date223	= $_REQUEST['end_date'];
								$date2	= date('Y-m-d',strtotime($date223));
								//$date22	= date('d-m-Y',strtotime($date2));
								echo 'दिनांक <font >'.$date123.'</font>'.' पासून दिनांक '.'<font>'.$date223.'</font>';
								
								$id1 = $date1;
								$id2= $date2;
							}
							else
							{
								$date123	= $datesun;
								$date1	= date('Y-m-d',strtotime($date123));
								//$date12	= date('d-m-Y',strtotime($date1));
								$date223	= $datesat;
								$date2	= date('Y-m-d',strtotime($date223));
								//$date22	= date('d-m-Y',strtotime($date2));
								echo 'दिनांक <font>'.$date123.'</font>'.' पासून दिनांक '.'<font>'.$date223.'</font>';
								
								$id1 = $date1;
								$id2= $date2;
							}
							 
							########################### Pending References ####################################
							echo"
							<tr><td colspan='4'><center><h4>". $rs['desk_nm']." चा गोषवारा</h4></center></td></tr>
							<tr>
								<td style='text-align:center;'>संदर्भाचा प्रकार</td>
								<td style='text-align:center;'>एकूण संदर्भ</td>
							</tr>
							<tr>
								<td style='text-align:center;'>मागील शिल्लक संदर्भ</td>";
					
								//accepted by desk
								$result = 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_ltr_rec_dt<'$id1' 
								AND (t.desk_approval='approve' AND d.ltr_status='')
								AND d.desk_id_from = (SELECT desk_id from login where login_id = '$username')") or die("cccc".mysql_error());
								
							/*	//not accepted by desk
								$result1 = 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 (t.desk_approval='pending' AND t.sent_to_desk_dt< '$id1' )
								AND t.desk_id = (SELECT desk_id from login where login_id = '$username')"); */
							while($row = mysql_fetch_array($result))
							{
									//echo"<td>".$row['total']."</td></tr>";
									$sum1 = $row['total'];
							}
						/*	while($row1 = mysql_fetch_array($result1))
							{
									//echo"<td>".$row['total']."</td></tr>";
									$sum11 = $row1['total'];
							} */
							$sum111=$sum1+$sum11;
							echo"<td style='text-align:center;'>".$sum111."</td></tr>";
						########################### Letters In Current Week ########################
			
								echo"<tr>
								<td style='text-align:center;'>चालू आठवड्यातील आवक संदर्भ</td>";
								//accepted by desk
								$result1=mysql_query("SELECT count(*)as total 
								FROM tapal_to_dpt_transaction t LEFT OUTER JOIN incoming_letters_transaction i 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_ltr_rec_dt BETWEEN '$id1' AND '$id2') 
								AND d.desk_id_from = (SELECT desk_id from login where login_id = '$username')
								AND d.ltr_status!='closed'
								ORDER BY i.ltr_inword_no") or die("eeee".mysql_error());
								
							/*	//not accepted by desk
								$result22213 = 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 (t.desk_approval='pending' AND t.sent_to_desk_dt BETWEEN '$id1' AND '$id2')
								AND t.desk_id = (SELECT desk_id from login where login_id = '$username')");
								*/
								while($row = mysql_fetch_array($result1))
								{
									$sum2	=	$row['total'];
								}
							/*	while($row = mysql_fetch_array($result22213))
								{
									$sum321	=	$row['total'];
								} */
								$sum32174=$sum2+$sum321;
								echo"<td style='text-align:center;'>".$sum32174."</td></tr>";
								$sum3 = $sum1 + $sum32174 ;
								########################### Total References ####################################
								echo"<tr><td style='text-align:center;'>एकूण संदर्भ</td>";
								///////pending////////
								//accepted by desk
								$result = 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_ltr_rec_dt<'$id1' 
								and (t.desk_approval='approve' AND d.ltr_status='')
								AND d.desk_id_from = (SELECT desk_id from login where login_id = '$username')") or die("cccc".mysql_error());
								
							/*	//not accepted by desk
								$result1 = 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 (t.desk_approval='pending' AND t.sent_to_desk_dt< '$id1')
								AND t.desk_id = (SELECT desk_id from login where login_id = '$username')"); */
								while($row = mysql_fetch_array($result))
								{
										//echo"<td>".$row['total']."</td></tr>";
										$sum1 = $row['total'];
								}
						/*		while($row1 = mysql_fetch_array($result1))
								{
										//echo"<td>".$row['total']."</td></tr>";
										$sum11 = $row1['total'];
								} */
								$sum111=$sum1+$sum11;
								
								/////////current//////////
								//accepted by desk
								$result1=mysql_query("SELECT count(*)as total 
								FROM tapal_to_dpt_transaction t LEFT OUTER JOIN incoming_letters_transaction i 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_ltr_rec_dt BETWEEN '$id1' AND '$id2') 
								AND d.desk_id_from = (SELECT desk_id from login where login_id = '$username')
								AND d.ltr_status!='closed'
								ORDER BY i.ltr_inword_no") or die("eeee".mysql_error());
								
							/*	//not accepted by desk
								$result22213 = 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 (t.desk_approval='pending' AND t.sent_to_desk_dt BETWEEN '$id1' AND '$id2')
								AND t.desk_id = (SELECT desk_id from login where login_id = '$username')");
								*/
								while($row = mysql_fetch_array($result1))
								{
									$sum2	=	$row['total'];
								}
							/*	while($row = mysql_fetch_array($result22213))
								{
									$sum32321	=	$row['total'];
								} */
								
								$sum1112=$sum111+$sum2+$sum32321;
								echo"<td>".$sum1112."</td></tr>";
								########################### Updated References ####################################
								echo"<tr><td style='text-align:center;'>अद्यावत केलेले</td>";
								$result3 = 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=(Select desk_id from login where login_id='$username') 
								AND d.update_date BETWEEN '$id1' AND '$id2'") or die("".mysql_error()); 
							
								while($row = mysql_fetch_array($result3))
									{ 
										echo "<td style='text-align:center;'>".$row['total']."</td></tr>";
										$sum13 = $row['total'];
									}
								$sum5 = $sum3 - $sum13;
								########################### Dispatched References ####################################
								echo"<tr><td style='text-align:center;'>एकूण निर्गत संदर्भ</td>";
								$result21 = 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=(Select desk_id from login where login_id='$username') 
								AND d.dispatched_dt BETWEEN '$id1' AND '$id2'") or die("".mysql_error()); 
								while($row = mysql_fetch_array($result21))
									{
										echo"<td style='text-align:center;'>".$row['total']."</td></tr>";
										$sum4 = $row['total'];
									}
								$sum5 = $sum3 - $sum4;
								########################### Total Pending References ############################
								echo"<tr><td style='text-align:center;'>एकूण शिल्लक संदर्भ</td>";
								//accepted by desk
								$result4 = 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=(select desk_id from login where login_id='$username') 
								AND d.desk_ltr_rec_dt <='$id2' 
								AND d.ltr_status = ''") or die("aa".mysql_error());
							
							/*	//not accepted by desk
								$result41 = 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 (t.desk_approval='pending' AND t.sent_to_desk_dt<='$id2')
								AND t.desk_id = (SELECT desk_id from login where login_id = '$username')") or die("bb".mysql_error()); */
									
							
								while($row = mysql_fetch_array($result4))
								{
									$sumx=$row['TOTAL'];
								}	
							/*	while($row1 = mysql_fetch_array($result41))
								{
									$sumy=$row1['TOTAL'];
								}	 */
							
								$sumxy=$sumx+$sumy;
								echo "<td style='text-align:center;'>".$sumxy."<td></tr>";
							########################### Pending From 1 Week References ###########################
								echo"<tr><td style='text-align:center;'>एका आठवड्यातील शिल्लक संदर्भ</a></td>";
							//accepted by desk
							$result5=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_ltr_rec_dt BETWEEN '$id1' AND '$id2' 
									AND ( 
											(d.ltr_category='' AND d.ltr_status='')
											 OR (d.ltr_status='')
										)
								) AND d.desk_id_from=(SELECT desk_id from login where login_id = '$username')")or die("abaaaaa".mysql_error());
							
						/*	//not accepted by desk
							$result51=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 (t.desk_approval='pending' AND t.sent_to_desk_dt BETWEEN '$id1' AND '$id2' )
							AND t.desk_id = (SELECT desk_id from login where login_id = '$username')
							AND sent_to_desk_dt BETWEEN '$id1' AND '$id2'")or die("abaaaaa".mysql_error()); */
							
							while($row = mysql_fetch_array($result5))
							{  
								$summ=$row['total'];
							}
							
						/*	while($row1 = mysql_fetch_array($result51))
							{  
								$summ1=$row1['total'];
							} */
							$sumn=$summ+$summ1;
							 
							echo "<td style='text-align:center;' >".$sumn."</td></tr>";
							########################### Pending From 2 Weeks References #########################
			
								echo"<tr><td style='text-align:center;'>दोन आठवड्यातील शिल्लक संदर्भ</td>";			
								
								$prev_date = date("Y-m-d", strtotime('-1 days', strtotime($id1)) );
								
							//accepted by desk
							$result6=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_ltr_rec_dt BETWEEN DATE_SUB('$id1',INTERVAL 7 DAY)AND '$prev_date')  
							AND ( 
									(d.ltr_category='' AND d.ltr_status='')
									 OR (d.ltr_status='')
								)
							AND d.desk_id_from = (SELECT desk_id from login where login_id = '$username')") or die("aaaaa".mysql_error());
							
						/*	//not accepted by desk
							$result61=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 t.desk_approval='pending'
							AND t.desk_id = ( SELECT desk_id from login where login_id = '$username' )
							AND t.sent_to_desk_dt BETWEEN DATE_SUB('$id1',INTERVAL 7 DAY) AND '$prev_date'") or die("zzzzzz".mysql_error()); */
							
							while($row = mysql_fetch_array($result6))
							{
								$sum23=$row['total'];
							}
						/*	while($row1 = mysql_fetch_array($result61))
							{
								$sum233=$row1['total'];
							} */
							
							$sum243=$sum23+$sum233;
							echo "<td style='text-align:center;'>".$sum243."</td></tr>";
							########################### Pending From 3 Weeks References #########################
								echo"<tr><td style='text-align:center;'>तीन आठवड्यातील शिल्लक संदर्भ</td>";	
							//accepted by desk
							$result7=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_ltr_rec_dt BETWEEN DATE_SUB('$id1',INTERVAL 14 DAY) AND DATE_SUB('$id1',INTERVAL 8 DAY) )  
							AND ( 
									(d.ltr_category='' AND d.ltr_status='')
									 OR (d.ltr_status='')
								)
							AND d.desk_id_from = (SELECT desk_id from login where login_id = '$username')") or die("bbbb".mysql_error());
							
						/*	//not accepted by desk
							$result71=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 t.desk_approval='pending'
							AND t.desk_id = ( SELECT desk_id from login where login_id = '$username' )
							AND t.sent_to_desk_dt BETWEEN DATE_SUB('$id1',INTERVAL 14 DAY) AND DATE_SUB('$id1',INTERVAL 8 DAY)") or die("abbbb".mysql_error()); */
							
							while($row = mysql_fetch_array($result7))
							{ 
								$sum44=$row['total'];
							}
						/*	while($row1 = mysql_fetch_array($result71))
							{ 
								$sum441=$row1['total'];
							} */
							$sum01=$sum44+$sum441;
							echo "<td style='text-align:center;'>".$sum01."</td></tr>";
							#################### Pending From More Than 3 Weeks References #####################
							echo"<tr><td style='text-align:center;'>तीन आठवड्यावरील शिल्लक संदर्भ</td>";			
							//accepted by desk
							$result8 = 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_ltr_rec_dt< DATE_SUB('$id1',INTERVAL 14 DAY)
								)  
							AND ( 
									(d.ltr_category='' AND d.ltr_status='')
									 OR (d.ltr_status='')
								)
							AND d.desk_id_from = (SELECT desk_id from login where login_id = '$username')") or die("bbbb".mysql_error());
							
						/*	//not accepted by desk
							$result81 = 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 t.desk_approval='pending'
							AND t.desk_id = ( SELECT desk_id from login where login_id = '$username' )
							AND t.sent_to_desk_dt< DATE_SUB('$id1',INTERVAL 14 DAY)") or die("bbbb".mysql_error()); */
								
							while($row = mysql_fetch_array($result8))
							{ 
								$sum99=$row['total'];
							}
					/*		while($row1 = mysql_fetch_array($result81))
							{ 
								$sum991=$row1['total'];
							} */
							
							$sum992=$sum99+$sum991;
							echo"<td style='text-align:center;'>".$sum992."</td></tr>";
							?>
								</table></center>
								</div>
							<p>	
								<center><input type="button" 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 'footer.php'; ?>
			</div>
        </div><!--maincontentinner-->
	</div><!--maincontent-->   
</div><!--rightpanel-->
</div><!--mainwrapper-->
</body>
</html>
