 <?php
######################################### Desk Wise Sadyasthiti Report ######################################
 
include_once('dashboard_desk.php');
require_once('convert_marathi.php');

$username = $_SESSION['User_Name'];

$qry=mysql_query("SELECT dm.desk_id, dm.desk_nm
FROM desk_master dm, login
WHERE login.desk_id = dm.desk_id
AND login_id = '$username'");

$rwq=mysql_fetch_array($qry);

?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>Tapal Admin System</title>
<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 type="text/javascript" src="js/adduser.js"></script> 
<script src="tableToExcel.js"></script>
</head>
    <div class="rightpanel"> 
        <ul class="breadcrumbs">
            <li><a href=""><i class="iconfa-home"></i></a> <span class="separator"></span></li>
            <li>प्रकरण नोंदवही </li>
        </ul>
        <div class="pageheader">
            <div class="pageicon"><span class="iconfa-table"></span></div>
            <div class="pagetitle">
                <h5>Tables</h5>
                <h1>प्रकरण नोंदवही </h1>
            </div>
        </div><!--pageheader-->
        <div class="maincontent">
            <div class="maincontentinner">
			<h4 class="widgettitle"><?php echo $rwq['desk_nm']; ?>ची प्रकरण नोंदवही </h4>	
			
				<?php
					$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>";				
				?>
			
			<!-- Code For Start Date End Date Input -->
				<form name="sadyasthiti" action="prakaran_nondwahi_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>
			           
						<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>			
						
							<?php	
							convert_marathi();
							$deskid=$rwq['desk_id'];
							
						//accepted by desk
						$result222 = mysql_query("SELECT d.desk_ltr_id, d.desk_id_from, d.action_taken, d.desk_ltr_rec_dt, d.desk_ltr_inword_no, t.dpt_inword_no, t.dpt_ltr_id, t.dpt_ltr_rec_dt, t.dpt_inword_no, t.desk_id, t.dept_id_from, i.subject, d. update_date, d.dispatched_dt, d.ltr_approval, di.addressed_to, d.ltr_status, i.sender_name, i.ltr_id, i.ltr_inword_no, i.receive_dt, i.ref_dt, i.ref_no, i.dept_id, i.document, d.scan_doc, di.outword_no, di.dispatched_to, d.ltr_category, d.assign_no
						FROM incoming_letters_transaction i LEFT OUTER JOIN tapal_to_dpt_transaction t ON i.ltr_id = t.ltr_id LEFT OUTER JOIN desk_transaction d ON d.dpt_ltr_id = t.dpt_ltr_id LEFT OUTER JOIN dispatched_transaction di ON di.desk_ltr_id = d.desk_ltr_id 
						WHERE (	d.desk_ltr_rec_dt< DATE_SUB('$id',INTERVAL 21 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
						$result2221 = mysql_query("SELECT d.desk_ltr_id, d.desk_id_from, d.action_taken, d.desk_ltr_rec_dt, d.desk_ltr_inword_no, t.dpt_inword_no, t.dpt_ltr_id, t.dpt_ltr_rec_dt, t.dpt_inword_no, t.desk_id, t.dept_id_from, i.subject, d. update_date, d.dispatched_dt, d.ltr_approval, di.addressed_to, d.ltr_status, i.sender_name, i.ltr_id, i.ltr_inword_no, i.receive_dt, i.ref_dt, i.ref_no, i.dept_id, i.document, d.scan_doc, di.outword_no, di.dispatched_to, d.ltr_category, d.assign_no 
						FROM  incoming_letters_transaction i LEFT OUTER JOIN  tapal_to_dpt_transaction t ON i.ltr_id = t.ltr_id 
						LEFT OUTER JOIN desk_transaction d ON d.dpt_ltr_id = t.dpt_ltr_id 
						LEFT OUTER JOIN dispatched_transaction di ON di.desk_ltr_id = d.desk_ltr_id 
						WHERE t.desk_approval='pending'
						AND t.desk_id = ( SELECT desk_id from login where login_id = '$username' )
						AND t.sent_to_desk_dt< DATE_SUB('$id',INTERVAL 21 DAY)") or die("bbbb".mysql_error()); 
							
							?>
								<table id="dyntable" class="table table-bordered">
							<thead>
								<tr>	
									<th class='head0 center'>पोलीस अधिक्षक <br> कार्यालय <br>आवक_क्रमांक<br>आवक_दिनांक</th>
									<th class='head1 center'>विभागीय आवक_क्रमांक<br>दिनांक</th>
									<th class='head0 center'>डेस्क आवक_क्रमांक<br>दिनांक</th>
									<th class='head1 center'>विषय</th>
									<th class='head0 center'>कोणाकडून <br>प्राप्त_झाला</th>
									<th class='head1 center'>केलेली कार्यवाही</th>
									<th class='head0 center'>पत्राची_स्थिती </th>
									<th class='head1 center'>विभागीय <br>जावक_दिनांक</th>
								</tr>
							</thead>
							<tbody>
					<?php		
					while($test = mysql_fetch_array($result222))
					{
						$desk_ltr_rec_dt 	= date('d-m-Y',strtotime($test['desk_ltr_rec_dt']));
						$dept_rec_date 		= date('d-m-Y',strtotime($test['dpt_ltr_rec_dt']));
						$update_date 		= date('d-m-Y',strtotime($test['update_date']));
						$rc_date 		= date('d-m-Y',strtotime($test['receive_dt']));
						$dispose_date 		= date('d-m-Y',strtotime($test['dispatched_dt']));
						
						// code for split date and arrange according to our need
						//dispose_date is in following format "YYYY-MM-DD" 
						list($year, $month, $day ) =explode("-",$dispose_date);
						$dispose = $day.'-'.$month.'-'.$year;
						
						list($year, $month, $day ) =explode("-",$update_date);
						$update = $day.'-'.$month.'-'.$year;
						
						//echo $dispose;
						
						//$id1 = $test['inward_no'];	
						echo "<tr>
						<td class='center'>".$test['ltr_inword_no']."<br>".$rc_date."</td>
						<td class='center'>".$test['dpt_inword_no']."<br>
						".$dept_rec_date."</td>
						<td class='center'>".$test['desk_ltr_inword_no']."<br>
						".$desk_ltr_rec_dt."</td>
						<td class='center'>".$test['subject']."</td>
						<td class='center'>".$test['sender_name']."</td>";
							if($test['action_taken']=='')
							{
								echo "<td class='center'><font color='red'>कार्यवाही करणे बाकी </font></td>";
							}
							else if($test['ltr_status']=='')
							{
								echo "<td class='center'><font color='red'>कार्यवाही करणे बाकी </font></td>";
							}
							else
							{
								echo "<td class='center'>".$test['action_taken']."</td>";
							}
							
							if($test['ltr_status']=='')
							{
								echo "<td class='center'><font color='red'>कार्यवाही करणे बाकी </font></td>";
							}
							else
							{
							 echo "<td class='center'>".$test['ltr_status']."</td>";
							}
							 
							 if($test['ltr_status']=='')
							{
								echo "<td class='center'><font color='red'>कार्यवाही करणे बाकी </font></td>";
							}
							
							else if ($test['ltr_status']=='updated')
								{
									echo "<td class='center'>".$update_date."</td>";
								}
							else if($test['ltr_status']=='dispatched')
								{
									echo "<td class='center'>".$dispose_date."</td>";
								}
							else
							{
								echo "<td style='text-align:center;'>--</td>";
							}
							echo "</tr>";
					}
				//mysql_close();
					while($test1 = mysql_fetch_array($result2221))
						{
							$desk_ltr_rec_dt 	= date('d-m-Y',strtotime($test1['desk_ltr_rec_dt']));
							$dept_rec_date 		= date('d-m-Y',strtotime($test1['dpt_ltr_rec_dt']));
							$update_date 		= date('d-m-Y',strtotime($test1['update_date']));
							$dispose_date 		= date('d-m-Y',strtotime($test1['dispatched_dt']));
							$rc_date 		= date('d-m-Y',strtotime($test['receive_dt']));
							list($year, $month, $day ) =explode("-",$dispose_date);
							$dispose = $day.'-'.$month.'-'.$year;
							
							list($year, $month, $day ) =explode("-",$update_date);
							$update = $day.'-'.$month.'-'.$year;
			
							echo "<tr>
							<td style='text-align:center;'>".$test1['ltr_inword_no']."<br>".$rc_date."</td>
							<td style='text-align:center;'>".$test1['dpt_inword_no']."<br>
							".$dept_rec_date."</td>
							<td class='center'><font color='red'> डेस्कने स्वीकारणे बाकी </font></td>
							<td style='text-align:center;'>".$test1['subject']."</td>
							<td style='text-align:center;'>".$test1['sender_name']."</td>";
								if($test1['action_taken']=='')
							{
								echo "<td style='text-align:center;'><font color='red'> कार्यवाही करणे बाकी </font></td>";
							}
							else
							{
								echo "<td style='text-align:center;'>".$test1['action_taken']."</td>";
							}
							
							if($test1['ltr_status']=='')
							{
								echo "<td style='text-align:center;'><font color='red'> कार्यवाही करणे बाकी </font></td>";
							}
							else
							{
							 echo "<td style='text-align:center;'>".$test1['ltr_status']."</td>";
							}
							 
							 if($test1['ltr_status']=='')
							{
								echo "<td style='text-align:center;'><font color='red'> कार्यवाही करणे बाकी </font></td>";
							}
							
							else if ($test1['ltr_status']=='updated')
								{
									echo "<td style='text-align:center;'>".$update_date."</td>";
								}
							else if($test1['ltr_status']=='dispatched')
								{
									echo "<td style='text-align:center;'>".$dispose_date."</td>";
								}
							else
							{
								echo "<td style='text-align:center;'>--</td>";
							}
							echo "</tr>";
						}
					?>
							</tbody>
						</table><br>
						
						<?php	
						/*echo "<div id='divToPrint' style='display:none;'>
							<center><table id='export' border='1px' style='margin-left:50px;'>
							<tr><td colspan='9'><center>". $rwq['desk_nm'] ."ची प्रकरण नोंदवही <br>दिनांक ".$date123." पासून दिनांक ".$date223."</center></td></tr>"; */
							
						echo "<div id='divToPrint' style='display:none;'>
							<center><table id='export' border='1px' style='margin-left:50px;'>
							<tr><td colspan='9'><center>". $rwq['desk_nm'] ."ची प्रकरण नोंदवही </center></td></tr>";
							
							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));
								
								$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));
								
								$id = $date1;
								//echo $id;
								
								$id1= $date2;
								//echo $id1;
							}
						//$id = $date1;
						
						echo ' <center><h2>पोलीस अधिक्षक कार्यालय, <br> जि.रायगड </h2></center>';
						
						//accepted by desk
						$result222 = mysql_query("SELECT d.desk_ltr_id, d.desk_id_from, d.action_taken, d.desk_ltr_rec_dt, d.desk_ltr_inword_no, t.dpt_inword_no, t.dpt_ltr_id, t.dpt_ltr_rec_dt, t.dpt_inword_no, t.desk_id, t.dept_id_from, i.subject, d. update_date, d.dispatched_dt, d.ltr_approval, di.addressed_to, d.ltr_status, i.sender_name, i.ltr_id, i.ltr_inword_no, i.receive_dt, i.ref_dt, i.ref_no, i.dept_id, i.document, d.scan_doc, di.outword_no, di.dispatched_to, d.ltr_category, d.assign_no
						FROM incoming_letters_transaction i LEFT OUTER JOIN tapal_to_dpt_transaction t ON i.ltr_id = t.ltr_id LEFT OUTER JOIN desk_transaction d ON d.dpt_ltr_id = t.dpt_ltr_id LEFT OUTER JOIN dispatched_transaction di ON di.desk_ltr_id = d.desk_ltr_id 
						WHERE (	d.desk_ltr_rec_dt< DATE_SUB('$id',INTERVAL 21 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
						$result2221 = mysql_query("SELECT d.desk_ltr_id, d.desk_id_from, d.action_taken, d.desk_ltr_rec_dt, d.desk_ltr_inword_no, t.dpt_inword_no, t.dpt_ltr_id, t.dpt_ltr_rec_dt, t.dpt_inword_no, t.desk_id, t.dept_id_from, i.subject, d. update_date, d.dispatched_dt, d.ltr_approval, di.addressed_to, d.ltr_status, i.sender_name, i.ltr_id, i.ltr_inword_no, i.receive_dt, i.ref_dt, i.ref_no, i.dept_id, i.document, d.scan_doc, di.outword_no, di.dispatched_to, d.ltr_category, d.assign_no 
						FROM  incoming_letters_transaction i LEFT OUTER JOIN  tapal_to_dpt_transaction t ON i.ltr_id = t.ltr_id 
						LEFT OUTER JOIN desk_transaction d ON d.dpt_ltr_id = t.dpt_ltr_id 
						LEFT OUTER JOIN dispatched_transaction di ON di.desk_ltr_id = d.desk_ltr_id 
						WHERE t.desk_approval='pending'
						AND t.desk_id = ( SELECT desk_id from login where login_id = '$username' )
						AND t.sent_to_desk_dt< DATE_SUB('$id',INTERVAL 21 DAY)") or die("bbbb".mysql_error()); 
			
						
					
						echo "
								<tr>	
									<th>पोलीस अधिक्षक <br> कार्यालय<br>आवक_क्रमांक<br>आवक_दिनांक</th>
									<th>विभागीय आवक_क्रमांक<br>दिनांक</th>
									<th>डेस्क आवक_क्रमांक<br>दिनांक</th>
									<th>विषय</th>
									<th>कोणाकडून <br>प्राप्त_झाला</th>
									<th>केलेली कार्यवाही</th>
									<th>पत्राची स्थिती </th>
									<th>विभागीय <br>जावक_दिनांक</th>
								</tr>";
					while($test = mysql_fetch_array($result222))
					{
						$desk_ltr_rec_dt 	= date('d-m-Y',strtotime($test['desk_ltr_rec_dt']));
						$dept_rec_date 		= date('d-m-Y',strtotime($test['dpt_ltr_rec_dt']));
						$update_date 		= date('d-m-Y',strtotime($test['update_date']));
						$dispose_date 		= date('d-m-Y',strtotime($test['dispatched_dt']));
						$rc_date 		= date('d-m-Y',strtotime($test['receive_dt']));
						// code for split date and arrange according to our need
						//dispose_date is in following format "YYYY-MM-DD" 
						list($year, $month, $day ) =explode("-",$dispose_date);
						$dispose = $day.'-'.$month.'-'.$year;
						
						list($year, $month, $day ) =explode("-",$update_date);
						$update = $day.'-'.$month.'-'.$year;
						
						//echo $dispose;
						
						//$id1 = $test['inward_no'];	
						echo "<tr>
						<td style='text-align:center;'>".$test['ltr_inword_no']."<br>".$rc_date."</td>
						<td style='text-align:center;'>".$test['dpt_inword_no']."<br>
						".$dept_rec_date."</td>
						<td style='text-align:center;'>".$test['desk_ltr_inword_no']."<br>
						".$desk_ltr_rec_dt."</td>
						<td style='text-align:center;'>".$test['subject']."</td>
						<td style='text-align:center;'>".$test['sender_name']."</td>
						";
						if($test['action_taken']=='')
						{
							echo "<td style='text-align:center;'>कार्यवाही करणे बाकी </td>";
						}
						else if($test['ltr_status']=='')
						{
							echo "<td style='text-align:center;'>कार्यवाही करणे बाकी </td>";
						}
						else
						{
							echo "<td style='text-align:center;'>".$test['action_taken']."</td>";
						}
						//////////////////////////////////////////////////////////////	
							if($test['ltr_status']=='')
							{
								echo "<td style='text-align:center;'>कार्यवाही करणे बाकी </td>";
							}
							else
							{
							 echo "<td style='text-align:center;'>".$test['ltr_status']."</td>";
							}
						//////////////////////////////////////////////////////////////////////	 
							 if($test['ltr_status']=='')
							{
								echo "<td style='text-align:center;'>कार्यवाही करणे बाकी </td>";
							}
							
							else if ($test['ltr_status']=='updated')
								{
									echo "<td style='text-align:center;'>".$update_date."</td>";
								}
							else if($test['ltr_status']=='dispatched')
								{
									echo "<td style='text-align:center;'>".$dispose_date."</td>";
								}
							else
							{
								echo "<td style='text-align:center;'>--</td>";
							}
							
							echo "</tr>";
					}
				//mysql_close();
					while($test1 = mysql_fetch_array($result2221))
						{
							$desk_ltr_rec_dt 	= date('d-m-Y',strtotime($test1['desk_ltr_rec_dt']));
							$dept_rec_date 		= date('d-m-Y',strtotime($test1['dpt_ltr_rec_dt']));
							$update_date 		= date('d-m-Y',strtotime($test1['update_date']));
							$dispose_date 		= date('d-m-Y',strtotime($test1['dispatched_dt']));
							$rc_date 		= date('d-m-Y',strtotime($test['receive_dt']));
							list($year, $month, $day ) =explode("-",$dispose_date);
							$dispose = $day.'-'.$month.'-'.$year;
							
							list($year, $month, $day ) =explode("-",$update_date);
							$update = $day.'-'.$month.'-'.$year;
			
							echo "<tr>
							<td style='text-align:center;'>".$test1['ltr_inword_no']."<br>".$rc_date."</td>
							<td style='text-align:center;'>".$test1['dpt_inword_no']."<br>
							".$dept_rec_date."</td>
							<td class='center'>डेस्कने स्वीकारणे बाकी</td>
							<td style='text-align:center;'>".$test1['subject']."</td>
							<td style='text-align:center;'>".$test1['sender_name']."</td>";
								if($test1['action_taken']=='')
							{
								echo "<td style='text-align:center;'>कार्यवाही करणे बाकी </td>";
							}
							else
							{
								echo "<td style='text-align:center;'>".$test1['action_taken']."</td>";
							}
							
							if($test1['ltr_status']=='')
							{
								echo "<td style='text-align:center;'>कार्यवाही करणे बाकी </td>";
							}
							else
							{
							 echo "<td style='text-align:center;'>".$test1['ltr_status']."</td>";
							}
							 
							 if($test1['ltr_status']=='')
							{
								echo "<td style='text-align:center;'>कार्यवाही करणे बाकी </td>";
							}
							
							else if ($test1['ltr_status']=='updated')
								{
									echo "<td style='text-align:center;'>".$update_date."</td>";
								}
							else if($test1['ltr_status']=='dispatched')
								{
									echo "<td style='text-align:center;'>".$dispose_date."</td>";
								}
							else
							{
								echo "<td style='text-align:center;'>--</td>";
							}
							echo "</tr>";
						}
					echo"</table>
					</center></div>";
								?>
						<p>	
							<center><input type="submit" value=" Print Page " onclick="PrintDiv();" class="btn btn-primary"/>
							<input type="button" class="btn" onclick="tableToExcel('export', 'W3C Example Table')" value="Export to Excel"></center>
						</p>
					  <br /><br />
					<?php include_once('footer.php'); ?>
					</div><!--maincontentinner-->
				</div><!--maincontent-->
			</div><!--rightpanel-->
		</div><!--mainwrapper-->
</body>
</html>