<!DOCTYPE html>
<?php 
$flag=$_REQUEST['flag'];


if($flag=='')
{

include('dashboard_department.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>गोषवारा </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 dept_nm from department_master where dept_id=(select dept_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['dept_nm'];?> चा गोषवारा </h1>
				</div>
			</div><!--pageheader-->
			<div class="maincontent">
				<div class="maincontentinner">
					<h4 class="widgettitle"><?php echo $rs['dept_nm'];?>चा गोषवारा </h4>
						<form name="sadyasthiti" action="goshwara_dpt.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>
						<br><br>
						<div id='divToPrint' align='center'>
							<p align="center"><font size="4" color='#0866c6' style="font-family:verdana;">गोषवारा <br><br>&nbsp;&nbsp;&nbsp;Department - 
							<?php
							
								convert_marathi();
								$sql ="SELECT department_master.dept_nm FROM login,department_master WHERE login.login_id = '$username' AND login.dept_id=department_master.dept_id";
								$result = mysql_query($sql);
								$row = mysql_fetch_array($result);
								echo $row['dept_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>';
								
								$id = $date1;
								//echo $id;
								
								$id1= $date2;
								//echo $id1;
							}
							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>';
								
								$id = $date1;
								//echo $id;
								
								$id1= $date2;
								//echo $id1;
							}
							?></font></p><br>
						<br><br>
							<?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['dept_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 align='center'><a href='goshwara_dpt_view.php?dpt_ltr_rec_dt=$id&end_date=$id1&no=01' target='blank' title='Click to View'>मागील शिल्लक संदर्भ</a></td>";
					
								
								// dept accepted,desk accepted
								$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 t.dpt_ltr_rec_dt < '$id' 
								AND d.ltr_status=''
								AND t.dept_id_from = (SELECT dept_id from login where login_id = '$username')") or die("cccc".mysql_error());	
									
								// dept accepted,desk not accepted
								$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.dpt_ltr_rec_dt < '$id' 
								AND t.desk_approval='pending' 
								AND t.returned_choice=''
								AND t.dept_id_from = (SELECT dept_id from login where login_id = '$username')") or die("cccc1".mysql_error());	
								
								//dept accepted, not assigned desk
								$result2 = 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.dpt_ltr_rec_dt < '$id' 
								AND t.desk_id='0' 
								AND t.returned_choice=''
								AND t.dept_id_from = (SELECT dept_id from login where login_id = '$username')") or die("cccc1".mysql_error());	
								
							/*	//dept not accepted
								$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 i.status='pending' AND i.sent_dt< '$id'
								AND i.dept_id = (SELECT dept_id from login where login_id = '$username')") or die("cccc2".mysql_error()); */
								
								while($row = mysql_fetch_array($result))
								{
									$sum = $row['total'];
								} 
								while($row1 = mysql_fetch_array($result1))
								{
									$sum1 = $row1['total'];
								}
								while($row2 = mysql_fetch_array($result2))
								{
									$sum2 = $row2['total'];
								}
								/*while($row3 = mysql_fetch_array($result3))
								{
									$sum3 = $row3['total'];
									
								}*/
								$sum4=$sum+$sum1+$sum2+$sum3;
								
								echo"<td align='center'>".$sum4."</td></tr>";
								
								########################### Letters In Current Week ############################
			
								echo"<tr><td align='center'><a href='goshwara_dpt_view.php?dpt_ltr_rec_dt=$id&end_date=$id1&no=02' target='blank' title='Click to View'>चालू आठवड्यातील आवक संदर्भ</a></td>";
								
								/////accepted by dept//////
								$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 
								WHERE (t.dpt_ltr_rec_dt BETWEEN '$id' AND '$id1')
								AND t.dept_id_from = (SELECT dept_id from login where login_id = '$username')
								AND t.returned_choice='' ") or die("eeee".mysql_error());
								
								/*/////not accepted by dept//////
								$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 i.status='pending' AND (i.sent_dt BETWEEN '$id' AND '$id1')
								AND i.dept_id = (SELECT dept_id from login where login_id = '$username')") or die("cccc2".mysql_error());*/
								
								while($row = mysql_fetch_array($result1))
								{
									//echo "<td align='center'>".$row['total']."</td></tr>";
										$sum21	=	$row['total'];
								}
								/* while($row = mysql_fetch_array($result3))
								{
									//echo "<td align='center'>".$row['total']."</td></tr>";
										$sum212	=	$row['total'];
								}	*/
								$sum2122=$sum21+$sum212;
								echo "<td align='center'>".$sum2122."</td></tr>";
								$sum42 = $sum4 + $sum2122 ;
			
								########################### Total References ####################################
								echo"<tr><td align='center'><a href='goshwara_dpt_view.php?dpt_ltr_rec_dt=$id&end_date=$id1&no=03' target='blank' title='Click to View'>एकूण संदर्भ</a></td>";
							
								////////////pending////////////
									// dept accepted,desk accepted
								$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 t.dpt_ltr_rec_dt < '$id' 
								AND d.ltr_status=''
								AND t.dept_id_from = (SELECT dept_id from login where login_id = '$username')") or die("cccc".mysql_error());	
									
								// dept accepted,desk not acceptedor not assigned 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.dpt_ltr_rec_dt < '$id' 
								AND (t.desk_id='0' OR t.desk_approval='pending' )
								AND t.returned_choice=''
								AND t.dept_id_from = (SELECT dept_id from login where login_id = '$username')") or die("cccc1".mysql_error());	
								
							/*	//dept not accepted
								$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 i.status='pending' AND i.sent_dt< '$id'
								AND i.dept_id = (SELECT dept_id from login where login_id = '$username')") or die("cccc2".mysql_error()); */
								
								while($row = mysql_fetch_array($result))
								{
									$sum = $row['total'];
								} 
								while($row1 = mysql_fetch_array($result1))
								{
									$sum1 = $row1['total'];
								}
								
								/*while($row3 = mysql_fetch_array($result3))
								{
									$sum3 = $row3['total'];
									
								}*/
								$sum4=$sum+$sum1+$sum3;
								
								////////////current/////////////////
								
								//accepted//
								$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 
								WHERE (t.dpt_ltr_rec_dt BETWEEN '$id' AND '$id1')
								AND t.dept_id_from = (SELECT dept_id from login where login_id = '$username')
								AND t.returned_choice='' ") or die("eeee".mysql_error());
								
							/*	/////not accepted by dept//////
								$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 i.status='pending' AND (i.sent_dt BETWEEN '$id' AND '$id1')
								AND i.dept_id = (SELECT dept_id from login where login_id = '$username')") or die("cccc2".mysql_error()); */
								while($row = mysql_fetch_array($result1))
								{
									$sum21	=	$row['total'];
								}
								/*while($row = mysql_fetch_array($result3))
								{
									$sum2112	=	$row['total'];
								}*/
								$sum22211122=$sum21+$sum2112;
								$sum42 = $sum4 + $sum22211122 ;
								
								echo "<td align='center'>".$sum42."</td></tr>";
								
								########################### Updated References ####################################
							/*	echo "<tr><td align='center'><a href='goshwara_dpt_view.php?dpt_ltr_rec_dt=$id&end_date=$id1&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 t.dept_id_from= (
													SELECT dept_id
													FROM login
													WHERE login_id = '$username' )
							AND t.dpt_ltr_rec_dt BETWEEN '$id' AND '$id1'
							AND  d.ltr_approval = 'approve'
							AND d.ltr_status = 'updated' ");
							
								while($row = mysql_fetch_array($result3))
								{
									echo "<td align='center'>".$row['TOTAL']."</td></tr>";
									$sum13 = $row['TOTAL'];
								}  */
								//$sum5 = $sum3 - $sum13;
								########################### Dispatched/updated References ####################################
								echo"<tr><td align='center'><a href='goshwara_dpt_view.php?dpt_ltr_rec_dt=$id&end_date=$id1&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 t.dept_id_from = (
														SELECT dept_id
														FROM login
														WHERE login_id = '$username' )
								AND t.dpt_ltr_rec_dt BETWEEN '$id' AND '$id1'
								AND (d.`ltr_status` = 'dispatched' OR d.ltr_status = 'updated')
								AND (
										t.`desk_approval` = 'approve'
										OR t.`desk_approval` = 'updated'
										OR t.`desk_approval` = 'dispatched'
									)
								AND d.ltr_approval!='transferred_todesk'
								AND d.action_taken != '' ");
								
								while($row = mysql_fetch_array($result21))
								{
									echo "<td align='center'>".$row['TOTAL']."</td></tr>";
									$sum4 = $row['total'];
								}
								//$sum5 = $sum3 - $sum4;
								$sum5=$sum3-($sum13+$sum4);
								########################### Total Pending References ##############################
								echo"<tr><td align='center'><a href='goshwara_dpt_view.php?dpt_ltr_rec_dt=$id&end_date=$id1&no=10' target='blank' title='Click to View'>एकूण शिल्लक संदर्भ</a></td>";
								
								// dept accepted,desk accepted
								$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 t.dept_id_from= ( SELECT dept_id FROM login WHERE login_id = '$username' ) 
								AND t.dpt_ltr_rec_dt <= '$id1' 
								AND t.`desk_approval` = 'approve'
								AND ( d.ltr_status='')");
										
								// dept accepted,desk not accepted
								$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.dept_id_from= ( SELECT dept_id FROM login WHERE login_id = '$username' ) 
								AND t.dpt_ltr_rec_dt <= '$id1' 
								AND (t.desk_id='0' OR t.`desk_approval` = 'pending')
								AND t.returned_choice='' ");

								/* //dept not accepted
								$result43 = 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 i.status='pending' AND i.sent_dt<= '$id1' 
								AND i.dept_id = (SELECT dept_id from login where login_id = '$username')") or die("cccc2".mysql_error()); */
								
								while($row = mysql_fetch_array($result4))
								{
									$sum=$row['total'];
								}
								while($row1 = mysql_fetch_array($result41))
								{
									$sum1=$row1['total'];
								}
							/*	while($row3 = mysql_fetch_array($result43))
								{
									$sum3=$row3['total'];
								} */
								$sum4 = $sum+$sum1+$sum3;								
								
								echo "<td align='center'>".$sum4."</td></tr>";
								
								########################### Pending From 1 Week References ##########################
								echo"<tr><td align='center'><a href='goshwara_dpt_view.php?dpt_ltr_rec_dt=$id&end_date=$id1&no=06' target='blank' title='Click to View'>एका आठवड्यातील शिल्लक संदर्भ</a></td>";
								
								// dept accepted,desk accepted
								$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 t.dept_id_from= ( SELECT dept_id FROM login WHERE login_id = '$username' ) 
								AND t.dpt_ltr_rec_dt BETWEEN '$id' AND '$id1' 
								AND t.`desk_approval` = 'approve'
								AND ( d.ltr_status='')");
										
								// dept accepted,desk not accepted or not assigned 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.dept_id_from= ( SELECT dept_id FROM login WHERE login_id = '$username' ) 
								AND t.dpt_ltr_rec_dt BETWEEN '$id' AND '$id1'
								AND ( t.desk_id='0' OR t.`desk_approval` = 'pending')
								AND t.returned_choice='' ");

								/* //dept not accepted
								$result43 = 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 i.status='pending' AND i.sent_dt BETWEEN '$id' AND '$id1' 
								AND i.dept_id = (SELECT dept_id from login where login_id = '$username')") or die("cccc2".mysql_error()); */
								
								while($row = mysql_fetch_array($result4))
								{
									$sum=$row['total'];
								}
								while($row1 = mysql_fetch_array($result41))
								{
									$sum1=$row1['total'];
								}
								
							/*	while($row3 = mysql_fetch_array($result43))
								{
									$sum3=$row3['total'];
								} */
								$sum4 = $sum+$sum1+$sum3;								
								
								echo "<td align='center'>".$sum4."</td></tr>";
									
								########################### Pending From 2 Weeks References ##########################
								echo "<tr><td align='center'><a href='goshwara_dpt_view.php?dpt_ltr_rec_dt=$id&end_date=$id1&no=07' target='blank' title='Click to View'>दोन आठवड्यातील शिल्लक संदर्भ</a></td>";			
								$prev_date = date("Y-m-d", strtotime('-1 days', strtotime($id)) );
								
								// dept accepted,desk accepted
								$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 t.dept_id_from= ( SELECT dept_id FROM login WHERE login_id = '$username' ) 
								AND t.dpt_ltr_rec_dt BETWEEN DATE_SUB('$id',INTERVAL 7 DAY)AND '$prev_date'
								AND t.`desk_approval` = 'approve'
								AND ( d.ltr_status='')");
										
								// dept accepted,desk not accepted
								$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.dept_id_from= ( SELECT dept_id FROM login WHERE login_id = '$username' ) 
								AND t.dpt_ltr_rec_dt BETWEEN DATE_SUB('$id',INTERVAL 7 DAY)AND '$prev_date'
								AND (t.desk_id='0' OR t.`desk_approval` = 'pending')
								AND t.returned_choice='' ");

								/* //dept not accepted
								$result43 = 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 i.status='pending' AND i.sent_dt BETWEEN DATE_SUB('$id',INTERVAL 7 DAY)AND '$prev_date' 
								AND i.dept_id = (SELECT dept_id from login where login_id = '$username')") or die("cccc2".mysql_error()); */
							
								while($row = mysql_fetch_array($result4))
								{
									$sum=$row['total'];
								}
								while($row1 = mysql_fetch_array($result41))
								{
									$sum1=$row1['total'];
								}
								
								/* while($row3 = mysql_fetch_array($result43))
								{
									$sum3=$row3['total'];
								} */
								$sum4 = $sum+$sum1+$sum3;								
								
								echo "<td align='center'>".$sum4."</td></tr>";
								
								########################### Pending From 3 Weeks References ##########################
								echo "<tr><td align='center'><a href='goshwara_dpt_view.php?dpt_ltr_rec_dt=$id&end_date=$id1&no=08' target='blank' title='Click to View'>तीन आठवड्यातील शिल्लक संदर्भ</a></td>";	

							// dept accepted,desk accepted
							$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 t.dept_id_from= ( SELECT dept_id FROM login WHERE login_id = '$username' ) 
							AND t.dpt_ltr_rec_dt BETWEEN DATE_SUB('$id',INTERVAL 14 DAY) AND DATE_SUB('$id',INTERVAL 8 DAY)
							AND t.`desk_approval` = 'approve'
							AND ( d.ltr_status='')");
									
							// dept accepted,desk not accepted
							$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.dept_id_from= ( SELECT dept_id FROM login WHERE login_id = '$username' ) 
							AND t.dpt_ltr_rec_dt BETWEEN DATE_SUB('$id',INTERVAL 14 DAY) AND DATE_SUB('$id',INTERVAL 8 DAY)
							AND (t.desk_id='0' OR t.`desk_approval` = 'pending')
							AND t.returned_choice='' ");

							/*								
							//dept not accepted
							$result43 = 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 i.status='pending' AND i.sent_dt BETWEEN DATE_SUB('$id',INTERVAL 14 DAY) AND DATE_SUB('$id',INTERVAL 8 DAY) 
							AND i.dept_id = (SELECT dept_id from login where login_id = '$username')") or die("cccc2".mysql_error());  */
								
								while($row = mysql_fetch_array($result4))
								{
									$sum=$row['total'];
								}
								while($row1 = mysql_fetch_array($result41))
								{
									$sum1=$row1['total'];
								}
								
								/* while($row3 = mysql_fetch_array($result43))
								{
									$sum3=$row3['total'];
								} */
								$sum4 = $sum+$sum1+$sum3;								
								
								echo "<td align='center'>".$sum4."</td></tr>";
								
								#################### Pending From More Than 3 Weeks References #######################
								echo "<tr><td align='center'><a href='goshwara_dpt_view.php?dpt_ltr_rec_dt=$id&end_date=$id1&no=09' target='blank' title='Click to View'>तीन आठवड्यावरील शिल्लक संदर्भ</a></td>";			
								
							// dept accepted,desk accepted
							$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 t.dept_id_from= ( SELECT dept_id FROM login WHERE login_id = '$username' ) 
							AND t.dpt_ltr_rec_dt< DATE_SUB('$id',INTERVAL 14 DAY)
							AND t.`desk_approval` = 'approve'
							AND ( d.ltr_status='')");
									
							// dept accepted,desk not accepted
							$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.dept_id_from= ( SELECT dept_id FROM login WHERE login_id = '$username' ) 
							AND t.dpt_ltr_rec_dt< DATE_SUB('$id',INTERVAL 14 DAY)
							AND (t.desk_id='0' OR t.`desk_approval` = 'pending')
							AND t.returned_choice='' ");
								
						/*	//dept not accepted
							$result43 = 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 i.status='pending' AND i.sent_dt< DATE_SUB('$id',INTERVAL 14 DAY)
							AND i.dept_id = (SELECT dept_id from login where login_id = '$username')") or die("cccc2".mysql_error()); */
							
							while($row = mysql_fetch_array($result4))
								{
									$sum=$row['total'];
								}
								while($row1 = mysql_fetch_array($result41))
								{
									$sum1=$row1['total'];
								}
								
							/*	while($row3 = mysql_fetch_array($result43))
								{
									$sum3=$row3['total'];
								} */
								$sum4 = $sum+$sum1+$sum3;								
								
								echo "<td align='center'>".$sum4."</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('sample_1', 'W3C Example Table')" value="Export to Excel"></center>
							</p>
						
						<br /><br />
						<?php include 'footer.php'; ?>
					</div>
				</div><!--maincontentinner-->
			</div><!--maincontent-->
		</div><!--rightpanel-->
	</div><!--mainwrapper-->
</body>
</html><!DOCTYPE html>
<?php 
} //if($flag=='') 

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

else if($flag=='n')
{

include('new_dashboard_department.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>गोषवारा </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 dept_nm from department_master where dept_id=(select dept_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['dept_nm'];?> चा गोषवारा </h1>
				</div>
			</div><!--pageheader-->
			<div class="maincontent">
				<div class="maincontentinner">
					<h4 class="widgettitle"><?php echo $rs['dept_nm'];?>चा गोषवारा </h4>
						<form name="sadyasthiti" action="goshwara_dpt.php?flag=n" 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>
						<br><br>
						<div id='divToPrint' align='center'>
							<p align="center"><font size="4" color='#0866c6' style="font-family:verdana;">गोषवारा <br><br>&nbsp;&nbsp;&nbsp;Department - 
							<?php
						
								convert_marathi();
								$sql ="SELECT department_master.dept_nm FROM login,department_master WHERE login.login_id = '$username' AND login.dept_id=department_master.dept_id";
								$result = mysql_query($sql);
								$row = mysql_fetch_array($result);
								echo $row['dept_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>';
								
								$id = $date1;
								//echo $id;
								
								$id1= $date2;
								//echo $id1;
							}
							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>';
								
								$id = $date1;
								//echo $id;
								
								$id1= $date2;
								//echo $id1;
							}
							?></font></p><br>
						<br><br>
							<?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['dept_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 align='center'><a href='goshwara_dpt_view.php?flag=n&dpt_ltr_rec_dt=$id&end_date=$id1&no=01' target='blank' title='Click to View'>मागील शिल्लक संदर्भ</a></td>";
					
								/*	$result = 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 dispatched_transaction di ON di.ltr_id = t.ltr_id 
									WHERE (t.dpt_ltr_rec_dt < '$id' and (t.sent_to_desk_dt >='$id')) 
									AND t.dept_id_from = ( SELECT dept_id from login where login_id = '$username' )") or die("cccc".mysql_error());*/
									
									//accepted by dept but nt taken ny action
									$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 dispatched_transaction di ON ( di.ltr_id = t.ltr_id AND di.desk_ltr_id='0')
									WHERE (t.dpt_ltr_rec_dt < '$id' ) 
									AND t.ltr_status='' AND t.returned_choice=''
									AND t.dept_id_from = ( SELECT dept_id from login where login_id = '$username' )") or die("cccc".mysql_error());
									
							/*		//not accepted by dept
									$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.ltr_id = t.ltr_id AND di.desk_ltr_id='0')
									WHERE i.status='pending' AND i.sent_dt < '$id'
									AND i.dept_id = (SELECT dept_id from login where login_id = '$username')") or die("cccc2".mysql_error());  */
									
									while($row = mysql_fetch_array($result))
									{
										$sum1 = $row['total'];
									}
								/*	while($row1 = mysql_fetch_array($result1))
									{
										$sum2 = $row1['total'];
									}  */
									$sum=$sum1+$sum2;
									echo"<td align='center'>".$sum."</td></tr>";
											
								########################### Letters In Current Week ############################
			
								echo"<tr><td align='center'><a href='goshwara_dpt_view.php?flag=n&dpt_ltr_rec_dt=$id&end_date=$id1&no=02' target='blank' title='Click to View'>चालू आठवड्यातील आवक संदर्भ</a></td>";
							
								///dept accepted
								$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 dispatched_transaction di ON (di.ltr_id = t.ltr_id AND di.desk_ltr_id='0')
								WHERE (t.dpt_ltr_rec_dt BETWEEN '$id' AND '$id1') 
								AND t.dept_id_from = (SELECT dept_id from login where login_id = '$username')
								AND t.returned_choice='' ") or die("eeee".mysql_error());
								
							/*	///dept not accepted
								$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 i.status='pending' AND (i.sent_dt BETWEEN '$id' AND '$id1')
								AND i.dept_id = (SELECT dept_id from login where login_id = '$username')") or die("cccc2".mysql_error()); */

								while($row = mysql_fetch_array($result1))
								{
									$sum2	=	$row['total'];
								}
							/*	while($row = mysql_fetch_array($result3))
								{
									$sum2112	=	$row['total'];
								} */
								$sum2112121=$sum2+$sum2112;
								echo "<td align='center'>".$sum2112121."</td></tr>";
								//echo $sum2;
								$sum3 = $sum1 + $sum2112121 ;
			
								########################### Total References ####################################
								echo"<tr><td align='center'><a href='goshwara_dpt_view.php?flag=n&dpt_ltr_rec_dt=$id&end_date=$id1&no=03' target='blank' title='Click to View'>एकूण संदर्भ</a></td>";
							
								////////////pending/////////
								//accepted by dept but nt taken ny action
								$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 dispatched_transaction di ON ( di.ltr_id = t.ltr_id AND di.desk_ltr_id='0')
								WHERE (t.dpt_ltr_rec_dt < '$id' ) 
								AND t.ltr_status='' AND t.returned_choice=''
								AND t.dept_id_from = ( SELECT dept_id from login where login_id = '$username' )") or die("cccc".mysql_error());
									
								
							/*	//not accepted by dept
								$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.ltr_id = t.ltr_id AND di.desk_ltr_id='0')
								WHERE i.status='pending' AND i.sent_dt < '$id'
								AND i.dept_id = (SELECT dept_id from login where login_id = '$username')") or die("cccc2".mysql_error()); */
								
								///////////current/////////////////
								//dept accepted
								$result11=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 dispatched_transaction di ON (di.ltr_id = t.ltr_id AND di.desk_ltr_id='0')
								WHERE (t.dpt_ltr_rec_dt BETWEEN '$id' AND '$id1') 
								AND t.dept_id_from = (SELECT dept_id from login where login_id = '$username')
								AND t.returned_choice='' ") or die("eeee".mysql_error());
								
							/*	///dept not accepted
								$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 i.status='pending' AND (i.sent_dt BETWEEN '$id' AND '$id1')
								AND i.dept_id = (SELECT dept_id from login where login_id = '$username')") or die("cccc2".mysql_error()); */
								
								while($row = mysql_fetch_array($result))
								{
									$sum132 = $row['total'];
								}
							/*	while($row1 = mysql_fetch_array($result1))
								{
									$sum232 = $row1['total'];
								} */
								
								while($row2 = mysql_fetch_array($result11))
								{
									$sum332	=	$row2['total'];
								}
							/*	while($row = mysql_fetch_array($result3))
								{
									$sum21145232	=	$row['total'];
								} */
									$sum=$sum132+$sum232+$sum332+$sum21145232;
									
								echo "<td align='center'>".$sum."</td></tr>";
							
								########################### Updated References ####################################
							/*	echo "<tr><td align='center'><a href='goshwara_dpt_view.php?flag=n&dpt_ltr_rec_dt=$id&end_date=$id1&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 dispatched_transaction di ON ( di.ltr_id = t.ltr_id AND di.desk_ltr_id='0')
								WHERE t.dept_id_from  = (
															SELECT dept_id
															FROM login
															WHERE login_id = '$username' )

								AND t.dpt_ltr_rec_dt BETWEEN '$id' AND '$id1' 
								AND t.returned_choice=''
								AND (t.status = 'approve' 
								AND t.ltr_status = 'updated' )");
								
								while($row = mysql_fetch_array($result3))
								{
									echo "<td align='center'>".$row['TOTAL']."</td></tr>";
									$sum1313 = $row['TOTAL'];
								}
								//$sum5 = $sum3 - $sum1313;  */
								########################### Dispatched/updated References ####################################
								echo"<tr><td align='center'><a href='goshwara_dpt_view.php?flag=n&dpt_ltr_rec_dt=$id&end_date=$id1&no=05' target='blank' title='Click to View'>एकूण निर्गत संदर्भ</a></td>";
								
								$result21 = 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 dispatched_transaction di ON ( di.ltr_id = t.ltr_id AND di.desk_ltr_id='0')
								WHERE t.dept_id_from = (
															SELECT dept_id
															FROM login
															WHERE login_id = '$username' )
								AND t.dpt_ltr_rec_dt BETWEEN '$id' AND '$id1'
								AND t.returned_choice=''
								AND (t.`ltr_status` = 'dispatched' OR t.ltr_status = 'updated')
								AND (
										t.`status` = 'transferred'
										OR t.status = 'approve'
										OR t.`status` = 'dispatched'
								)
								AND t.action_taken != '' ");
								
								while($row = mysql_fetch_array($result21))
								{
									echo "<td align='center'>".$row['TOTAL']."</td></tr>";
									$sum445 = $row['total'];
								}
								//$sum5 = $sum3 - $sum445;
								$sum5=$sum3-($sum1313+$sum445);
								########################### Total Pending References ##############################
								echo"<tr><td align='center'><a href='goshwara_dpt_view.php?flag=n&dpt_ltr_rec_dt=$id&end_date=$id1&no=10' target='blank' title='Click to View'>एकूण शिल्लक संदर्भ</a></td>";
							
								//////////////////pending///////////////
								//accepted by dept but nt taken ny action
								$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 dispatched_transaction di ON ( di.ltr_id = t.ltr_id AND di.desk_ltr_id='0')
								WHERE (t.dpt_ltr_rec_dt < '$id' ) 
								AND t.ltr_status='' AND t.returned_choice=''
								AND t.dept_id_from = ( SELECT dept_id from login where login_id = '$username' )") or die("cccc".mysql_error());
									
							/*	//not accepted by dept
								$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.ltr_id = t.ltr_id AND di.desk_ltr_id='0')
								WHERE i.status='pending' AND i.sent_dt < '$id'
								AND i.dept_id = (SELECT dept_id from login where login_id = '$username')") or die("cccc2".mysql_error()); */
								
								////////current pending////////////
								//accepted by dept
								$result11=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 dispatched_transaction di ON (di.ltr_id = t.ltr_id AND di.desk_ltr_id='0')
								WHERE (t.dpt_ltr_rec_dt BETWEEN '$id' AND '$id1') 
								AND t.dept_id_from = (SELECT dept_id from login where login_id = '$username')
								AND t.ltr_status='' AND t.returned_choice='' ") or die("eeee".mysql_error());
								
							/*	/////not accepted by dept//////
								$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 i.status='pending' AND (i.sent_dt BETWEEN '$id' AND '$id1')
								AND i.dept_id = (SELECT dept_id from login where login_id = '$username')") or die("cccc2".mysql_error()); */
								
								while($row = mysql_fetch_array($result))
								{
									$sum19 = $row['total'];
								}
							/*	while($row1 = mysql_fetch_array($result1))
								{
									$sum29 = $row1['total'];
								} */
								while($row2 = mysql_fetch_array($result11))
								{
									$sum39 = $row2['total'];
								}
							/*	while($row = mysql_fetch_array($result3))
								{
									$sum211829	=	$row['total'];
								} */
								$sum=$sum19+$sum29+$sum39+$sum211829;
								
								echo "<td align='center'>".$sum."</td></tr>";
								
								########################### Pending From 1 Week References ##########################
								echo"<tr><td align='center'><a href='goshwara_dpt_view.php?flag=n&dpt_ltr_rec_dt=$id&end_date=$id1&no=06' target='blank' title='Click to View'>एका आठवड्यातील शिल्लक संदर्भ</a></td>";
							
								//accepted by dept but nt taken ny action
								$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 dispatched_transaction di ON ( di.ltr_id = t.ltr_id AND di.desk_ltr_id='0')
								WHERE (t.dpt_ltr_rec_dt BETWEEN '$id' AND '$id1' ) 
								AND t.ltr_status='' AND t.returned_choice=''
								AND t.dept_id_from = ( SELECT dept_id from login where login_id = '$username' )") or die("cccc".mysql_error());
									
							/*	//not accepted by dept
								$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.ltr_id = t.ltr_id AND di.desk_ltr_id='0')
								WHERE i.status='pending' AND i.sent_dt BETWEEN '$id' AND '$id1'
								AND i.dept_id = (SELECT dept_id from login where login_id = '$username')") or die("cccc2".mysql_error()); */
																
								while($row = mysql_fetch_array($result))
								{
									$sum14 = $row['total'];
								}
							/*	while($row1 = mysql_fetch_array($result1))
								{
									$sum24 = $row1['total'];
								} */
					
								$sum=$sum14+$sum24;
								
								echo "<td align='center'>".$sum."</td></tr>";
									
								########################### Pending From 2 Weeks References ##########################
								echo "<tr><td align='center'><a href='goshwara_dpt_view.php?flag=n&dpt_ltr_rec_dt=$id&end_date=$id1&no=07' target='blank' title='Click to View'>दोन आठवड्यातील शिल्लक संदर्भ</a></td>";			
							
							$prev_date = date("Y-m-d", strtotime('-1 days', strtotime($id)) );
							
							//accepted by dept but nt taken ny action
							$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 dispatched_transaction di ON ( di.ltr_id = t.ltr_id AND di.desk_ltr_id='0')
							WHERE (t.dpt_ltr_rec_dt BETWEEN DATE_SUB('$id',INTERVAL 7 DAY) AND '$prev_date')  
							AND t.ltr_status='' AND t.returned_choice=''
							AND t.dept_id_from = ( SELECT dept_id from login where login_id = '$username' )") or die("cccc".mysql_error());
									
						/*	//not accepted by dept
							$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.ltr_id = t.ltr_id AND di.desk_ltr_id='0')
							WHERE i.status='pending' AND i.sent_dt BETWEEN DATE_SUB('$id',INTERVAL 7 DAY) AND '$prev_date'
							AND i.dept_id = (SELECT dept_id from login where login_id = '$username')") or die("cccc2".mysql_error()); */
								
								while($row = mysql_fetch_array($result))
								{
									$sum15 = $row['total'];
								}
							/*	while($row1 = mysql_fetch_array($result1))
								{
									$sum25 = $row1['total'];
								} */
								 
								$sum=$sum15+$sum25;
								
								echo "<td align='center'>".$sum."</td></tr>";
							
								
						########################### Pending From 3 Weeks References ##########################
							echo "<tr><td align='center'><a href='goshwara_dpt_view.php?flag=n&dpt_ltr_rec_dt=$id&end_date=$id1&no=08' target='blank' title='Click to View'>तीन आठवड्यातील शिल्लक संदर्भ</a></td>";	
							
							//accepted by dept but nt taken ny action
							$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 dispatched_transaction di ON ( di.ltr_id = t.ltr_id AND di.desk_ltr_id='0')
							WHERE t.dpt_ltr_rec_dt BETWEEN DATE_SUB('$id',INTERVAL 14 DAY) AND DATE_SUB('$id',INTERVAL 8 DAY)  
							AND t.ltr_status='' AND t.returned_choice=''
							AND t.dept_id_from = ( SELECT dept_id from login where login_id = '$username')") or die("cccc".mysql_error());
									
						/*	//not accepted by dept
							$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.ltr_id = t.ltr_id AND di.desk_ltr_id='0')
							WHERE i.status='pending' AND i.sent_dt BETWEEN DATE_SUB('$id',INTERVAL 14 DAY) AND DATE_SUB('$id',INTERVAL 8 DAY)
							AND i.dept_id = (SELECT dept_id from login where login_id = '$username')") or die("cccc2".mysql_error());  */
								
								while($row = mysql_fetch_array($result))
								{
									$sum16 = $row['total'];
								}
							/*	while($row1 = mysql_fetch_array($result1))
								{
									$sum26 = $row1['total'];
								} */
								
								$sum=$sum16+$sum26;
								
								echo "<td align='center'>".$sum."</td></tr>";
							
						#################### Pending From More Than 3 Weeks References #######################
								echo "<tr><td align='center'><a href='goshwara_dpt_view.php?flag=n&dpt_ltr_rec_dt=$id&end_date=$id1&no=09' target='blank' title='Click to View'>तीन आठवड्यावरील शिल्लक संदर्भ</a></td>";			
							
							//accepted by dept but nt taken ny action
							$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 dispatched_transaction di ON ( di.ltr_id = t.ltr_id AND di.desk_ltr_id='0')
							WHERE t.dpt_ltr_rec_dt < DATE_SUB('$id',INTERVAL 14 DAY)
							AND t.ltr_status='' AND t.returned_choice=''
							AND t.dept_id_from = ( SELECT dept_id from login where login_id = '$username')") or die("cccc".mysql_error());
									
					/*		//not accepted by dept
							$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.ltr_id = t.ltr_id AND di.desk_ltr_id='0')
							WHERE i.status='pending' AND i.sent_dt < DATE_SUB('$id',INTERVAL 14 DAY)
							AND i.dept_id = (SELECT dept_id from login where login_id = '$username')") or die("cccc2".mysql_error());  */
								
								while($row = mysql_fetch_array($result))
								{
									$sum17 = $row['total'];
								}
						/*		while($row1 = mysql_fetch_array($result1))
								{
									$sum27 = $row1['total'];
								} */
								
								$sum=$sum17+$sum27;
								
								echo "<td align='center'>".$sum."</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('sample_1', 'W3C Example Table')" value="Export to Excel"></center>
							</p>
						
						<br /><br />
						<?php include 'footer.php'; ?>
					</div>
				</div><!--maincontentinner-->
			</div><!--maincontent-->
		</div><!--rightpanel-->
	</div><!--mainwrapper-->
</body>
</html><!DOCTYPE html>
<?php 
} //else if($flag=='n')
?>