Jump to content

Select dropdown list search and date picker seach type

aquilina

By aquilina
in PHP Coding Help

 Share

Recommended Posts

aquilina Member

aquilina

Posted
Posted

Im currently doing the generate report from fetched data. I got 2 type of search filter , search by date and search using customername (select dropdown list). The one using the date search is worked but when i try using the drop down list to fetch the data, it does'nt appear.

<?php 
include '../include/navbar.php';
	$post_at = "";
	$post_at_to_date = "";
	$count = 1000;
	
	$queryCondition = "";
	if(!empty($_POST["search"]["DOCDATE"])) {			
		$post_at = $_POST["search"]["DOCDATE"];
		list($fid,$fim,$fiy) = explode("-",$post_at);
		
		$post_at_todate = date('Y-m-d');
		if(!empty($_POST["search"]["post_at_to_date"])) {
			$post_at_to_date = $_POST["search"]["post_at_to_date"];
			list($tid,$tim,$tiy) = explode("-",$_POST["search"]["post_at_to_date"]);
			$post_at_todate = "$tiy-$tim-$tid";
		}
		
		$queryCondition .= "WHERE sl_iv.DOCDATE BETWEEN '$fiy-$fim-$fid' AND '" . $post_at_todate . "'";
	}

	$sql = "SELECT * From `sl_iv` Inner Join `ar_iv` On ar_iv.DOCNO = sl_iv.DOCNO Inner Join `payment_terms` On ar_iv.TERMS = payment_terms.id " . $queryCondition . " ORDER BY sl_iv.DOCNO Asc";
	$result = mysqli_query($conn_connection,$sql);
	
	$item_q = "SELECT * FROM `sl_iv`";
	$item = mysqli_query($conn_connection, $item_q);
	
	$curdate = "SELECT DATEDIFF(CURDATE(), `DOCDATE`) AS DAYS FROM ar_iv";
	$resultdate = mysqli_query($conn_connection, $curdate);
	
	$query = "SELECT * FROM sl_iv GROUP by COMPANYNAME";
	$result1 = mysqli_query($conn_connection, $query);

?>


	<link rel='stylesheet' type='text/css' href='css/style.css' />
	<link rel='stylesheet' type='text/css' href='css/print.css' media="print" />
	<script type='text/javascript' src='js/jquery-1.3.2.min.js'></script>
	<script type='text/javascript' src='js/example.js'></script>
	<script src="http://code.jquery.com/jquery-1.9.1.js"></script>
	<link rel="stylesheet" href="http://code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">


</head>
<body class="invoice" onLoad="calculateSum()">
<div class="modal-body">	
	<div id="page-wrap">
			<textarea id="header">Report</textarea>

		<div id="cust">

            <textarea id="cust-title"></textarea>

            <table id="meta">
                <tr>
                    <td class="meta-head">Report No.</td>
				<td name="reportno" class="input-control" /><?php echo $count; ?></td>

                </tr>
                <tr>

                    <td class="meta-head">Date</td>
                    <td placeholder="Today Date" name="todaydate" class="input-control" /><?php echo date('Y-m-d'); ?></td>
                </tr>

            </table>
		
		</div>		
		<br></br>
		
		<form name="frmSearch" method="post" action="">
		<p class="search_input" id="hiderow">
		<input type="text" placeholder="From Date" id="post_at" name="search[DOCDATE]"  value="<?php echo $post_at; ?>" class="input-control" />
	    <input type="text" placeholder="To Date" id="post_at_to_date" name="search[post_at_to_date]" style="margin-left:10px"  value="<?php echo $post_at_to_date; ?>" class="input-control"  />			 
		<input type="submit" name="go" value="Search">
		</p>
		
		<br></br>
		
		<p id="hiderow">
			<select name= "COMPANYNAME">
			<option value="">ALL</option>
			
			<?php while ($row = mysqli_fetch_array($result1)):;?>
			
			<option value="<?php echo ($row['COMPANYNAME']); ?>"><?php echo ($row['COMPANYNAME']); ?></option>
			
			<?php
			endwhile;
			?>
			</select>
			<input type="submit" name="submit" value="Submit"/>
	
		</p>
		
	<br /><hr  />
		<?php if(!empty($result))	 { ?>
		<table id="items">
			<center>
			<thead>
				<tr>
					<th width="15%" style="text-align:center"><span>Doc No.</span></th>
					<th width="10%" style="text-align:center"><span>Date</span></th>
					<th width="10%" style="text-align:center"><span>Terms</span></th>
					<th width="10%" style="text-align:center"><span>Due</span></th>
					<th width="5%" style="text-align:center"><span>Age</span></th>
					<th width="20%" style="text-align:center"><span>Customer Name</span></th>
					<th width="10%" style="text-align:center"><span>Ammount</span></th>
					<th width="10%" style="text-align:center"><span>Payment</span></th>
					<th width="10%" style="text-align:center"><span>OutStanding</span></th>
				</tr>
			</thead>
			</center>
			
			<tbody>
			<?php
			if (mysqli_num_rows($result) > 0) {
				// output data of each row
				while($row = mysqli_fetch_assoc($result)) {
					
				$docamt = $row['DOCAMT']; 
				$paymentamt = $row['PAYMENTAMT'];
				$outstanding = $docamt - $paymentamt;
				
				
				$then = $row['DOCDATE'];
				$then = strtotime($then);
				
				$now = time();
				
				$difference = $now - $then;
				$dateage = floor($difference / (60*60*24));
			?>				
			<tr class="item-row">
				<td style="text-align:left; font-size:15px" readonly><?php echo ($row['DOCNO']);?></td>
				<td style="text-align:center; font-size:15px"><?php echo ($row['DOCDATE']);?></td>
				<td style="text-align:center; font-size:15px"><?php echo ($row['terms']);?></td>
				<td style="text-align:center; font-size:15px"><?php echo ($row['DUEDATE']);?></td>
				<td style="text-align:center; font-size:15px"><?php echo $dateage;?></td>
				<td style="text-align:left; font-size:15px" readonly><?php echo ($row['COMPANYNAME']);?></td>
				<td><input type="text" style="text-align:right; font-size:15px" class="form-control input-sm TotalAmt" id="TotalAmt0" name="TotalAmt[]" value="<?php echo htmlspecialchars($row['DOCAMT']);?>" readonly></td>
				<td><input type="text" style="text-align:right; font-size:15px" class="form-control input-sm payment" id="payment0" name="payment[]" value="<?php echo htmlspecialchars($row['PAYMENTAMT']);?>" readonly></td>
				<td><input type="text" style="text-align:right; font-size:15px" class="form-control input-sm Total_Outstanding" id="Total_Outstanding0" name="Total_Outstanding[]" value="<?php echo number_format((float)$outstanding, 2, '.', '');?>" readonly></td>
			</tr>
			<?php
				}
			} else {
				echo "0 results";
			}
			?>
			
			</tbody>
		</table>
		<?php } ?>
		</form>
	<br>
	<div class="row">
		<div class="col-md-3 pull-right">

			<table class="table table-condensed table-bordered table-striped table-custom-font" border="0">
				<tr>
					<td><b>Total Amount (RM)</b></td>
					<td><input type="text" style="text-align:right; font-size:15px" class="form-control input-sm total_amount" id="total_amount" name="total_amount" value="0.00" readonly></td>
				</tr>
			</table>
			<table class="table table-condensed table-bordered table-striped table-custom-font" border="0">
				<tr>	
					<td><b>Total Outstanding (RM)</b></td>
					<td><input type="text" style="text-align:right; font-size:15px" class="form-control input-sm Net_Total" id="net_total" name="net_total" value="0.00" readonly></td>
				</tr>
			</table>
		</div>
	</div>
	<div class="pull-right">
	
				<a href="print_tax.php" target="_blank">
					<button type="button" class="btn btn-default btn-sm">
						<span class="glyphicon glyphicon-print"></span> PRINT
					</button>
				</a>	

	</div>
</div>
</div>	
<script>
<!--unitcost, taxes, qty, discount, price-->

	function calculate(qty,rt,up,subttl,taxr,taxa,total,disc,totaltax){	
		var quantity = $('#'+qty).val();		
		var rate = $('#'+rt).val();		
		var unit_price = $('#'+up).val();						
		var subtotal = quantity * rate * unit_price; // count subtotal 				
		$('#'+subttl).val(subtotal.toFixed(2));						
		var t_disc = $('#'+disc).val();	
		
		// check something if that something exists. what something? find that something. no spoon feeding.
		if (/\%/g.test(t_disc)) {
			var count_disc = t_disc.match(/\%/g).length;
		}	else {
			var count_disc = 0;
		}			
				
		// replace something2 hahahahahaha. go and learn. again, no spoon feeding.
		var discount = t_disc.replace(/^[ ]+|[ ]+$/g,'');						
		
		// not full checking, but you get the idea. 
		if ((/\s/g.test(discount)) || (/[a-zA-Z]/g.test(discount)) || (/[^0-9.?%]/g.test(discount))) {
			alert("Please Re-Enter Your Discount");
		} else {		
			if((count_disc == 1) && (discount[discount.length - 1] === '%')) { 		
				// if found something at the end of something, something will happen.
				var s_disc = discount;
				str_disc = s_disc.slice(0, -1);	
				var disc_amt = subtotal * str_disc / 100;				
				var ttl = subtotal - disc_amt;												
				$('#'+total).val(ttl.toFixed(2));										
			} else if (count_disc == 0) {
				var str_disc = discount;
				var ttl = subtotal - str_disc;	
				if (isNaN(ttl) == 0 ){
					$('#'+total).val(ttl.toFixed(2));
				} else {
					alert("Please Re-Enter Your Discount");
				}				
			} else {
				alert("Please Re-Enter Your Discount");
			}		
		}		
							
		var t_rate = $('#'+taxr).val();	
		var temp1 = ttl * t_rate / 100;			
		var tax_amount = Math.round(temp1 * 100) / 100;		
		$('#'+taxa).val(tax_amount.toFixed(2));	
		
		var total = ttl + tax_amount;
		$('#'+totaltax).val(total.toFixed(2));	

		calculateSum();					
	}

	function calculateSum() { 
		var total = 0;
		$(".Total_Outstanding").each(function() {
				//add only if the value is number
				if(!isNaN(this.value) && this.value.length!=0) {
						total += parseFloat(this.value);
				}
		});	
		$("#net_total").val(total.toFixed(2));	
	
		var sum_gst = 0;
		$(".TotalAmt").each(function() {
				//add only if the value is number
				if(!isNaN(this.value) && this.value.length!=0) {
						sum_gst += parseFloat(this.value);
				}
		});	
		$("#total_amount").val(sum_gst.toFixed(2));	
	
		var totaltax = 0;
		$(".TotalTax").each(function() {
				//add only if the value is number
				if(!isNaN(this.value) && this.value.length!=0) {
						totaltax += parseFloat(this.value);
				}
		});	
		$("#total_including_gst").val(totaltax.toFixed(2));	
	
		//var aftr_rndg = rndfunc(sum_final*2, 1)/2;
		//var rndg_adj = aftr_rndg - sum_final;
		//$("#rounding_adjustment").val(rndg_adj.toFixed(2));
		
		//var pewpewpew = sum_final + rndg_adj;
		//$("#final_total").val(pewpewpew.toFixed(2));
		//$("#bal_payable").val(pewpewpew.toFixed(2));
	}
		
	function rndfunc(number, p)
  { 
   //return Math.round((number*2))/2; 
   return +(Math.round(number + "e+"+p)  + "e-"+p);      
  }
	
	function RoundNum(num, length) { 
    var number = Math.round(num * Math.pow(10, length)) / Math.pow(10, length);
    return number;
	}	
</script>

<script>
function myFunction() {
    window.print();
}
</script>

<script src="http://code.jquery.com/ui/1.10.3/jquery-ui.js"></script>
<script>
$.datepicker.setDefaults({
showOn: "button",
buttonImage: "datepicker.png",
buttonText: "Date Picker",
buttonImageOnly: true,
dateFormat: 'dd-mm-yy'  
});
$(function() {
$("#post_at").datepicker();
$("#post_at_to_date").datepicker();
});
</script>

acapfn.png

 

 

Link to comment
Share on other sites
aquilina Member

aquilina

Posted
  • Author
Posted

you would need to add some php code to actually use the data from the COMPANYNAME select/option menu to add the correct term to the WHERE clause in the sql query statement. there's nothing in the code now.

yes that the problem.. i kind a problem when mixing up the two seach filter. i was able to run it seperately but when i want merge and mix the two function is matter

Link to comment
Share on other sites
aquilina Member

aquilina

Posted
  • Author
Posted

we can only help you with problems in your code when you post your code. post your attempt with both filters in it.

 

btw - you should be using a method='get' form for controlling what will be displaying on the page.

i need a hint on it.. i didnt ask for full. im going to try it

Link to comment
Share on other sites
aquilina Member

aquilina

Posted
  • Author
Posted (edited)

we can only help you with problems in your code when you post your code. post your attempt with both filters in it.

 

btw - you should be using a method='get' form for controlling what will be displaying on the page.

Im adding POST method in the form.

<form name="frmSearch" method="post" action="">
        <p class="search_input" id="hiderow">
        <input type="text" placeholder="From Date" id="post_at" name="search[DOCDATE]"  value="<?php echo $post_at; ?>" class="input-control" />
        <input type="text" placeholder="To Date" id="post_at_to_date" name="search[post_at_to_date]" style="margin-left:10px"  value="<?php echo $post_at_to_date; ?>" class="input-control"  />            
        <input type="submit" name="go" value="Search">
        </p>
        
        <br></br>
        
        <p id="hiderow">
            <select name= "COMPANYNAME">
            <option value="">ALL</option>
            
            <?php while ($row = mysqli_fetch_array($result1)):;?>
            
            <option value="<?php echo ($row['COMPANYNAME']); ?>"><?php echo ($row['COMPANYNAME']); ?></option>
            
            <?php
            endwhile;
            ?>
            </select>
            <input type="submit" name="submit" value="Submit"/>
    
        </p>
        
    <br /><hr  />
        <?php if(isset($_POST['COMPANYNAME']))
        {
            $COMPANYNAME = $_POST['COMPANYNAME'];
        ?>
        <table id="items">
            <center>
            <thead>
                <tr>
                    <th width="15%" style="text-align:center"><span>Doc No.</span></th>
                    <th width="10%" style="text-align:center"><span>Date</span></th>
                    <th width="10%" style="text-align:center"><span>Terms</span></th>
                    <th width="10%" style="text-align:center"><span>Due</span></th>
                    <th width="5%" style="text-align:center"><span>Age</span></th>
                    <th width="20%" style="text-align:center"><span>Customer Name</span></th>
                    <th width="10%" style="text-align:center"><span>Ammount</span></th>
                    <th width="10%" style="text-align:center"><span>Payment</span></th>
                    <th width="10%" style="text-align:center"><span>OutStanding</span></th>
                </tr>
            </thead>
            </center>
        
            <tbody>
            <?php
            if (mysqli_num_rows($result) > 0) {
                // output data of each row
                while($row = mysqli_fetch_array($result)) {
                    
                $docamt = $row['DOCAMT'];
                $paymentamt = $row['PAYMENTAMT'];
                $outstanding = $docamt - $paymentamt;
                
                
                $then = $row['DOCDATE'];
                $then = strtotime($then);
                
                $now = time();
                
                $difference = $now - $then;
                $dateage = floor($difference / (60*60*24));
            ?>                
            <tr class="item-row">
                <td style="text-align:left; font-size:15px" readonly><?php echo ($row['DOCNO']);?></td>
                <td style="text-align:center; font-size:15px"><?php echo ($row['DOCDATE']);?></td>
                <td style="text-align:center; font-size:15px"><?php echo ($row['terms']);?></td>
                <td style="text-align:center; font-size:15px"><?php echo ($row['DUEDATE']);?></td>
                <td style="text-align:center; font-size:15px"><?php echo $dateage;?></td>
                <td style="text-align:left; font-size:15px" readonly><?php echo ($row['COMPANYNAME']);?></td>
                <td><input type="text" style="text-align:right; font-size:15px" class="form-control input-sm TotalAmt" id="TotalAmt0" name="TotalAmt[]" value="<?php echo htmlspecialchars($row['DOCAMT']);?>" readonly></td>
                <td><input type="text" style="text-align:right; font-size:15px" class="form-control input-sm payment" id="payment0" name="payment[]" value="<?php echo htmlspecialchars($row['PAYMENTAMT']);?>" readonly></td>
                <td><input type="text" style="text-align:right; font-size:15px" class="form-control input-sm Total_Outstanding" id="Total_Outstanding0" name="Total_Outstanding[]" value="<?php echo number_format((float)$outstanding, 2, '.', '');?>" readonly></td>
            </tr>
            <?php
                }
            } else {
                echo "0 results";
            }
            ?>
            
            </tbody>
            
        </table>
        <?php } ?>
        </form>

It seem doesnt work well here. But when i refresh the page the table is missing. What i want to do here is the page display the default value when all data is select and it only changed when the filter is used..

Edited by aquilina
Link to comment
Share on other sites
aquilina Member

aquilina

Posted
  • Author
Posted (edited)

Now it said mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given at 

while($data = mysqli_fetch_array($result))

		    $query = "SELECT * From `sl_iv` Inner Join `ar_iv` On ar_iv.DOCNO = sl_iv.DOCNO Inner Join `payment_terms` On ar_iv.TERMS = payment_terms.id GROUP BY sl_iv.COMPANYNAME";
    $result1 = mysqli_query($conn_connection, $query);

?>


    <link rel='stylesheet' type='text/css' href='css/style.css' />
    <link rel='stylesheet' type='text/css' href='css/print.css' media="print" />
    <script type='text/javascript' src='js/jquery-1.3.2.min.js'></script>
    <script type='text/javascript' src='js/example.js'></script>
    <script src="http://code.jquery.com/jquery-1.9.1.js"></script>
    <link rel="stylesheet" href="http://code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">


</head>
<body class="invoice" onLoad="calculateSum()">
<div class="modal-body">    
    <div id="page-wrap">
            <textarea id="header">Report</textarea>

        <div id="cust">

            <textarea id="cust-title"></textarea>

            <table id="meta">
                <tr>
                    <td class="meta-head">Report No.</td>
                <td name="reportno" class="input-control" /><?php echo $count; ?></td>

                </tr>
                <tr>

                    <td class="meta-head">Date</td>
                    <td placeholder="Today Date" name="todaydate" class="input-control" /><?php echo date('Y-m-d'); ?></td>
                </tr>

            </table>
        
        </div>        
        <br></br>
        
        <form name="frmSearch" method="post" action="">
        <p class="search_input" id="hiderow">
        <input type="text" placeholder="From Date" id="post_at" name="search[DOCDATE]"  value="<?php echo $post_at; ?>" class="input-control" />
        <input type="text" placeholder="To Date" id="post_at_to_date" name="search[post_at_to_date]" style="margin-left:10px"  value="<?php echo $post_at_to_date; ?>" class="input-control"  />            
        <input type="submit" name="go" value="Search">
        </p>
        
        <br></br>
        
        <p id="hiderow">
            <select name= "COMPANYNAME">
            <option value="">ALL</option>
            
            <?php while ($row = mysqli_fetch_array($result1)):;?>
            
            <option value="<?php echo ($row['COMPANYNAME']); ?>"><?php echo ($row['COMPANYNAME']); ?></option>
            
            <?php
            endwhile;
            ?>
            </select>
            <input type="submit" name="submit" value="Submit"/>
    
        </p>
        
    <br /><hr  />
        <?php if(isset($_POST['COMPANYNAME']))
        {
            $COMPANYNAME = $_POST['COMPANYNAME'];
            $fetch = "SELECT `DOCNO`, `DOCDATE`, `terms`, `DUEDATE`, `COMPANYNAME`, `DOCAMT`, `PAYMENTAMT` From `sl_iv` Inner Join `ar_iv` On ar_iv.DOCNO = sl_iv.DOCNO Inner Join `payment_terms` On ar_iv.TERMS = payment_terms.id WHERE COMPANYNAME = '".$COMPANYNAME."'";
            $result = mysqli_query($conn_connection,$fetch);
        ?>
        <table id="items">
            <center>
            <thead>
                <tr>
                    <th width="15%" style="text-align:center"><span>Doc No.</span></th>
                    <th width="10%" style="text-align:center"><span>Date</span></th>
                    <th width="10%" style="text-align:center"><span>Terms</span></th>
                    <th width="10%" style="text-align:center"><span>Due</span></th>
                    <th width="5%" style="text-align:center"><span>Age</span></th>
                    <th width="20%" style="text-align:center"><span>Customer Name</span></th>
                    <th width="10%" style="text-align:center"><span>Ammount</span></th>
                    <th width="10%" style="text-align:center"><span>Payment</span></th>
                    <th width="10%" style="text-align:center"><span>OutStanding</span></th>
                </tr>
            </thead>
            </center>
        
            <tbody>
            <?php
            if($COMPANYNAME =="")
            {
                $res=mysqli_query($conn_connection, $fetch);
            }
            while($data = mysqli_fetch_array($result))
            {
                    
                $docamt = $data['DOCAMT'];
                $paymentamt = $data['PAYMENTAMT'];
                $outstanding = $docamt - $paymentamt;
                
                
                $then = $data['DOCDATE'];
                $then = strtotime($then);
                
                $now = time();
                
                $difference = $now - $then;
                $dateage = floor($difference / (60*60*24));
            ?>                
            <tr class="item-row">
                <td style="text-align:left; font-size:15px" readonly><?php echo ($data['DOCNO']);?></td>
                <td style="text-align:center; font-size:15px"><?php echo ($data['DOCDATE']);?></td>
                <td style="text-align:center; font-size:15px"><?php echo ($data['terms']);?></td>
                <td style="text-align:center; font-size:15px"><?php echo ($data['DUEDATE']);?></td>
                <td style="text-align:center; font-size:15px"><?php echo $dateage;?></td>
                <td style="text-align:left; font-size:15px" readonly><?php echo ($data['COMPANYNAME']);?></td>
                <td><input type="text" style="text-align:right; font-size:15px" class="form-control input-sm TotalAmt" id="TotalAmt0" name="TotalAmt[]" value="<?php echo htmlspecialchars($data['DOCAMT']);?>" readonly></td>
                <td><input type="text" style="text-align:right; font-size:15px" class="form-control input-sm payment" id="payment0" name="payment[]" value="<?php echo htmlspecialchars($data['PAYMENTAMT']);?>" readonly></td>
                <td><input type="text" style="text-align:right; font-size:15px" class="form-control input-sm Total_Outstanding" id="Total_Outstanding0" name="Total_Outstanding[]" value="<?php echo number_format((float)$outstanding, 2, '.', '');?>" readonly></td>
            </tr>
            <?php
                }
            ?>
            
            </tbody>
            
        </table>
        <?php } ?>
        </form>
Edited by aquilina
Link to comment
Share on other sites
aquilina Member

aquilina

Posted
  • Author
Posted (edited)

we can only help you with problems in your code when you post your code. post your attempt with both filters in it.

 

btw - you should be using a method='get' form for controlling what will be displaying on the page.

Ok now the selection option working and another things happen which the datepicker does not work... But how to make it show all the data in the select table as default? Because as default the table area is empty but i want it shows all the data. and when select <option>ALL</option> it also able to show all the data.. any clue?

<form name="frmSearch" method="post" action="">
		<p class="search_input" id="hiderow">
		<input type="text" placeholder="From Date" id="post_at" name="search[DOCDATE]"  value="<?php echo $post_at; ?>" class="input-control" />
	    <input type="text" placeholder="To Date" id="post_at_to_date" name="search[post_at_to_date]" style="margin-left:10px"  value="<?php echo $post_at_to_date; ?>" class="input-control"  />			 
		<input type="submit" name="go" value="Search">
		</p>
		
		<br></br>
		
		<p id="hiderow">
			<select name= "COMPANYNAME">
			<option value="">ALL</option>
			
			<?php while ($row = mysqli_fetch_array($result1)):;?>
			
			<option value="<?php echo ($row['COMPANYNAME']); ?>"><?php echo ($row['COMPANYNAME']); ?></option>
			
			<?php
			endwhile;
			?>
			</select>
			<input type="submit" name="submit" value="Submit"/>
	
		</p>
		
	<br /><hr  />
		<?php 
		
		if(isset($_POST['COMPANYNAME']))
		{
			$COMPANYNAME = $_POST['COMPANYNAME'];
			$fetch = "SELECT * FROM `sl_iv` Inner Join `ar_iv` On ar_iv.DOCNO = sl_iv.DOCNO Inner Join `payment_terms` On ar_iv.TERMS = payment_terms.id WHERE COMPANYNAME = '".$COMPANYNAME."'";
			$result = mysqli_query($conn_connection,$fetch)or die("MySQL error: " . mysqli_error($conn_connection) . "<hr>\nQuery: $fetch");
		?>
		<table id="items">
			<center>
			<thead>
				<tr>
					<th width="15%" style="text-align:center"><span>Doc No.</span></th>
					<th width="10%" style="text-align:center"><span>Date</span></th>
					<th width="10%" style="text-align:center"><span>Terms</span></th>
					<th width="10%" style="text-align:center"><span>Due</span></th>
					<th width="5%" style="text-align:center"><span>Age</span></th>
					<th width="20%" style="text-align:center"><span>Customer Name</span></th>
					<th width="10%" style="text-align:center"><span>Ammount</span></th>
					<th width="10%" style="text-align:center"><span>Payment</span></th>
					<th width="10%" style="text-align:center"><span>OutStanding</span></th>
				</tr>
			</thead>
			</center>
		
			<tbody>
			<?php 
			if($COMPANYNAME =="")
			{
				$res=mysqli_query($conn_connection, $fetch);
			}
			while($data=mysqli_fetch_array($result))
			{
					
				$docamt = $data['DOCAMT']; 
				$paymentamt = $data['PAYMENTAMT'];
				$outstanding = $docamt - $paymentamt;
				
				
				$then = $data['DOCDATE'];
				$then = strtotime($then);
				
				$now = time();
				
				$difference = $now - $then;
				$dateage = floor($difference / (60*60*24));
			?>				
			<tr class="item-row">
				<td style="text-align:left; font-size:15px" readonly><?php echo ($data['DOCNO']);?></td>
				<td style="text-align:center; font-size:15px"><?php echo ($data['DOCDATE']);?></td>
				<td style="text-align:center; font-size:15px"><?php echo ($data['terms']);?></td>
				<td style="text-align:center; font-size:15px"><?php echo ($data['DUEDATE']);?></td>
				<td style="text-align:center; font-size:15px"><?php echo $dateage;?></td>
				<td style="text-align:left; font-size:15px" readonly><?php echo ($data['COMPANYNAME']);?></td>
				<td><input type="text" style="text-align:right; font-size:15px" class="form-control input-sm TotalAmt" id="TotalAmt0" name="TotalAmt[]" value="<?php echo htmlspecialchars($data['DOCAMT']);?>" readonly></td>
				<td><input type="text" style="text-align:right; font-size:15px" class="form-control input-sm payment" id="payment0" name="payment[]" value="<?php echo htmlspecialchars($data['PAYMENTAMT']);?>" readonly></td>
				<td><input type="text" style="text-align:right; font-size:15px" class="form-control input-sm Total_Outstanding" id="Total_Outstanding0" name="Total_Outstanding[]" value="<?php echo number_format((float)$outstanding, 2, '.', '');?>" readonly></td>
			</tr>
			<?php
				}
			?>
			
			</tbody>
			
		</table>
		<?php } ?>
		</form>
Edited by aquilina
Link to comment
Share on other sites
aquilina Member

aquilina

Posted
  • Author
Posted

when the submitted value is the ALL choice, you would leave the corresponding term out of the WHERE clause.

put that part aside 1st.. because now the date picker is not working. looks like the date picker only worked when there is data displayed on the table.. since the default page doesnt shows any data it might not working.

Link to comment
Share on other sites
aquilina Member

aquilina

Posted
  • Author
Posted

when the submitted value is the ALL choice, you would leave the corresponding term out of the WHERE clause.

i have been rearrange the code for whole day still cannt make the date search filter working.. i changed using one submit button where preivously using 2 submit button..

<?php 
include '../include/navbar.php';
	$post_at = "";
	$post_at_to_date = "";
	$count = 1000;
	
	$queryCondition = "";
	if(!empty($_POST["search"]["DOCDATE"])) {			
		$post_at = $_POST["search"]["DOCDATE"];
		list($fid,$fim,$fiy) = explode("-",$post_at);
		
		$post_at_todate = date('Y-m-d');
		if(!empty($_POST["search"]["post_at_to_date"])) {
			$post_at_to_date = $_POST["search"]["post_at_to_date"];
			list($tid,$tim,$tiy) = explode("-",$_POST["search"]["post_at_to_date"]);
			$post_at_todate = "$tiy-$tim-$tid";
		}
		
		$queryCondition .= "WHERE sl_iv.DOCDATE BETWEEN '$fiy-$fim-$fid' AND '" . $post_at_todate . "'";
	}

	$sql = "SELECT * From `sl_iv` Inner Join `ar_iv` On ar_iv.DOCNO = sl_iv.DOCNO Inner Join `payment_terms` On ar_iv.TERMS = payment_terms.id " . $queryCondition . " ORDER BY sl_iv.DOCNO Asc";
	$result = mysqli_query($conn_connection,$sql);
	
	$item_q = "SELECT * FROM `sl_iv`";
	$item = mysqli_query($conn_connection, $item_q);
	
	$curdate = "SELECT DATEDIFF(CURDATE(), `DOCDATE`) AS DAYS FROM ar_iv";
	$resultdate = mysqli_query($conn_connection, $curdate);

?>


	<link rel='stylesheet' type='text/css' href='css/style.css' />
	<link rel='stylesheet' type='text/css' href='css/print.css' media="print" />
	<script type='text/javascript' src='js/jquery-1.3.2.min.js'></script>
	<script type='text/javascript' src='js/example.js'></script>
	<script src="http://code.jquery.com/jquery-1.9.1.js"></script>
	<link rel="stylesheet" href="http://code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">


</head>
<body class="invoice" onLoad="calculateSum()">
<div class="modal-body">	
	<div id="page-wrap">
			<textarea id="header">Report</textarea>

		<div id="cust">

            <textarea id="cust-title">Linx</textarea>

            <table id="meta">
                <tr>
                    <td class="meta-head">Report No.</td>
				<td name="reportno" class="input-control" /><?php echo $count; ?></td>

                </tr>
                <tr>

                    <td class="meta-head">Date</td>
                    <td placeholder="Today Date" name="todaydate" class="input-control" /><?php echo date('Y-m-d'); ?></td>
                </tr>

            </table>
		
		</div>		
		<br></br>
		
		<form name="frmSearch" id="frmSearch" method="post" action="cust_due_test.php">
			<input type="text" placeholder="From Date" id="post_at" name="search[DOCDATE]"  value="<?php echo $post_at; ?>" class="input-control" />
			<input type="text" placeholder="To Date" id="post_at_to_date" name="search[post_at_to_date]" style="margin-left:10px"  value="<?php echo $post_at_to_date; ?>" class="input-control"  />	
		
			<select name= "COMPANYNAME">
			<option value="">ALL</option>
			
			<?php
				$query = mysqli_query($conn_connection, "SELECT * FROM sl_iv GROUP by COMPANYNAME");

				while ($row = mysqli_fetch_assoc($query)) {
				echo "<option value='".$row["COMPANYNAME"]."'".($row["COMPANYNAME"]==$_POST["COMPANYNAME"] ? " selected" : "").">".$row["COMPANYNAME"]."</option>";
			}
			?>
			</select>
			<input type="submit" name="submit" id="button "value="Submit"/>
			<a href="cust_due_test.php">reset</a>
	<br></br>

		<table id="items">
			<center>
			<thead>
				<tr>
					<th width="15%" style="text-align:center"><span>Doc No.</span></th>
					<th width="10%" style="text-align:center"><span>Date</span></th>
					<th width="10%" style="text-align:center"><span>Terms</span></th>
					<th width="10%" style="text-align:center"><span>Due</span></th>
					<th width="5%" style="text-align:center"><span>Age</span></th>
					<th width="20%" style="text-align:center"><span>Customer Name</span></th>
					<th width="10%" style="text-align:center"><span>Ammount</span></th>
					<th width="10%" style="text-align:center"><span>Payment</span></th>
					<th width="10%" style="text-align:center"><span>OutStanding</span></th>
				</tr>
			</thead>
			</center>
			
			<tbody>
			<?php
			if(isset($_POST['COMPANYNAME']))
		{
			$COMPANYNAME = $_POST['COMPANYNAME'];
			$fetch = "SELECT * FROM `sl_iv` Inner Join `ar_iv` On ar_iv.DOCNO = sl_iv.DOCNO Inner Join `payment_terms` On ar_iv.TERMS = payment_terms.id WHERE COMPANYNAME = '".$COMPANYNAME."'";
			$result = mysqli_query($conn_connection,$fetch)or die("MySQL error: " . mysqli_error($conn_connection) . "<hr>\nQuery: $fetch");
		}
			if (mysqli_num_rows($result) > 0) {
				// output data of each row
				while($row = mysqli_fetch_assoc($result)) {
					
				$docamt = $row['DOCAMT']; 
				$paymentamt = $row['PAYMENTAMT'];
				$outstanding = $docamt - $paymentamt;
				
				
				$then = $row['DOCDATE'];
				$then = strtotime($then);
				
				$now = time();
				
				$difference = $now - $then;
				$dateage = floor($difference / (60*60*24));
			?>				
			<tr class="item-row">
				<td><input type="text" style="text-align:right; font-size:15px" class="form-control input-sm DocNo" id=DocNo0" name="DocNo[]" value="<?php echo htmlspecialchars($row['DOCNO']);?>" readonly></td>
				<td><input type="text" style="text-align:right; font-size:15px" class="form-control input-sm DocDate" id="DocDate0" name="DocDate[]" value="<?php echo htmlspecialchars($row['DOCDATE']);?>" readonly></td>
				<td><input type="text" style="text-align:right; font-size:15px" class="form-control input-sm Terms" id="Terms0" name="Terms[]" value="<?php echo htmlspecialchars($row['terms']);?>" readonly></td>
				<td><input type="text" style="text-align:right; font-size:15px" class="form-control input-sm DueDate" id="DueDate0" name="DueDate[]" value="<?php echo htmlspecialchars($row['DUEDATE']);?>" readonly></td>
				<td><input type="text" style="text-align:right; font-size:15px" class="form-control input-sm DateAge" id="DateAge0" name="DateAge[]" value="<?php echo $dateage;?>" readonly></td>
				<td><input type="text" style="text-align:right; font-size:15px" class="form-control input-sm CompanyName" id="CompanyName0" name="CompanyName[]" value="<?php echo htmlspecialchars($row['COMPANYNAME']);?>" readonly></td>
				<td><input type="text" style="text-align:right; font-size:15px" class="form-control input-sm TotalAmt" id="TotalAmt0" name="TotalAmt[]" value="<?php echo htmlspecialchars($row['DOCAMT']);?>" readonly></td>
				<td><input type="text" style="text-align:right; font-size:15px" class="form-control input-sm payment" id="payment0" name="payment[]" value="<?php echo htmlspecialchars($row['PAYMENTAMT']);?>" readonly></td>
				<td><input type="text" style="text-align:right; font-size:15px" class="form-control input-sm Total_Outstanding" id="Total_Outstanding0" name="Total_Outstanding[]" value="<?php echo number_format((float)$outstanding, 2, '.', '');?>" readonly></td>
			</tr>
			<?php
				}
			} else {
				echo "0 results";
			}
			?>
			
			</tbody>
		</table>
	</form>	
	<br>
	<div class="row">
		<div class="col-md-3 pull-right">

			<table class="table table-condensed table-bordered table-striped table-custom-font" border="0">
				<tr>
					<td><b>Total Amount (RM)</b></td>
					<td><input type="text" style="text-align:right; font-size:15px" class="form-control input-sm total_amount" id="total_amount" name="total_amount" value="0.00" readonly></td>
				</tr>
			</table>
			<table class="table table-condensed table-bordered table-striped table-custom-font" border="0">
				<tr>	
					<td><b>Total Outstanding (RM)</b></td>
					<td><input type="text" style="text-align:right; font-size:15px" class="form-control input-sm Net_Total" id="net_total" name="net_total" value="0.00" readonly></td>
				</tr>
			</table>
		</div>
	</div>
	<div class="pull-right">
	
				<a href="print_tax.php" target="_blank">
					<button type="button" class="btn btn-default btn-sm">
						<span class="glyphicon glyphicon-print"></span> PRINT
					</button>
				</a>	

	</div>
	
</div>
</div>
Link to comment
Share on other sites
mac_gyver Prolific Member

mac_gyver

Posted
Posted (edited)
i have been rearrange the code for whole day still cannt make the date search filter working..

 

 

yes, but have you been looking at what your code is doing? you have added a completely separate sql query for the company name, instead of modifying the existing sql query so that it would match the data you want.

 

in programming (and for any goal), you must define what you want the code to do, before you can write any code to do it. otherwise, you can end up spending an infinite amount of time working on something without accomplishing anything useful. just ask the programmer's who wrote this forum software.

 

so, first define what you want to the WHERE clause in the sql query to be for the four possibilities of date range and company name selection -

 

1) no date range (empty date form fields) and no company name (the ALL default choice) - you want the query to match all data, i.e. a query with a WHERE clause without any terms in it for the date and company name. if these are the only things being used in the WHERE clause, you can leave the entire WHERE clause out of the query.

 

2) a date range and no company name - you want the query to match data - WHERE sl_iv.DOCDATE BETWEEN 'the_start_date' AND 'the_end_date'

 

3) no date range and a company name - you want the query to match data - WHERE COMPANYNAME = 'the_selected_company_name'

 

4) both a date range and a company name - you probably want the query to match data with both conditions - WHERE sl_iv.DOCDATE BETWEEN 'the_start_date' AND 'the_end_date' AND COMPANYNAME = 'the_selected_company_name'

 

given that the original code has this - $queryCondition = "";, your task would be to build the correct WHERE clause in that variable, it's already being put into the sql query statement.

 

a general purpose way of doing this, that will support any number of different terms/filters, is to add each term as an element to an array. you can then just implode() the array using the " AND " keyword to produce a WHERE clause containing all of the terms. this will work regardless of the number of terms you add to the array (provided you define an empty array first.)

 

you also need to do something that i suggested at the top of this thread and use a get method form. you would also want to re-populate the form fields with any previous selection so that the form is 'sticky' and retains the values and you need to display the data from the query someplace beside inside the search form.

 
lastly, you need to protect against sql special characters in the data from breaking the sql syntax (which is how sql injection is done) and causing errors. the best way of doing this is to use a prepared query with place-holders in the sql statement for data values, then supply the data when the query is executed. unfortunately, doing this for a dynamic sql statement, which what you have with different possible filters, isn't very straight-forward using the php mysqli extension. the php PDO extension is an all around better choice to use over the mysqli extension and you should switch to it if you can.
Edited by mac_gyver
Link to comment
Share on other sites
aquilina Member

aquilina

Posted
  • Author
Posted

 

yes, but have you been looking at what your code is doing? you have added a completely separate sql query for the company name, instead of modifying the existing sql query so that it would match the data you want.

 

in programming (and for any goal), you must define what you want the code to do, before you can write any code to do it. otherwise, you can end up spending an infinite amount of time working on something without accomplishing anything useful. just ask the programmer's who wrote this forum software.

 

so, first define what you want to the WHERE clause in the sql query to be for the four possibilities of date range and company name selection -

 

1) no date range (empty date form fields) and no company name (the ALL default choice) - you want the query to match all data, i.e. a query with a WHERE clause without any terms in it for the date and company name. if these are the only things being used in the WHERE clause, you can leave the entire WHERE clause out of the query.

 

2) a date range and no company name - you want the query to match data - WHERE sl_iv.DOCDATE BETWEEN 'the_start_date' AND 'the_end_date'

 

3) no date range and a company name - you want the query to match data - WHERE COMPANYNAME = 'the_selected_company_name'

 

4) both a date range and a company name - you probably want the query to match data with both conditions - WHERE sl_iv.DOCDATE BETWEEN 'the_start_date' AND 'the_end_date' AND COMPANYNAME = 'the_selected_company_name'

 

given that the original code has this - $queryCondition = "";, your task would be to build the correct WHERE clause in that variable, it's already being put into the sql query statement.

 

a general purpose way of doing this, that will support any number of different terms/filters, is to add each term as an element to an array. you can then just implode() the array using the " AND " keyword to produce a WHERE clause containing all of the terms. this will work regardless of the number of terms you add to the array (provided you define an empty array first.)

 

you also need to do something that i suggested at the top of this thread and use a get method form. you would also want to re-populate the form fields with any previous selection so that the form is 'sticky' and retains the values and you need to display the data from the query someplace beside inside the search form.

 
lastly, you need to protect against sql special characters in the data from breaking the sql syntax (which is how sql injection is done) and causing errors. the best way of doing this is to use a prepared query with place-holders in the sql statement for data values, then supply the data when the query is executed. unfortunately, doing this for a dynamic sql statement, which what you have with different possible filters, isn't very straight-forward using the php mysqli extension. the php PDO extension is an all around better choice to use over the mysqli extension and you should switch to it if you can.

 

how about user can use date or dropdown option.. i mean date or dropdown.. mean here need to have 2 query?

Link to comment
Share on other sites
aquilina Member

aquilina

Posted
  • Author
Posted

i have make both working but can u help me take a look?

            <?php
			if(isset ($_POST["from"],$_POST["to"]))
			{
				$from = $_POST["from"];
				$to = $_POST["to"];
				
				$test = "SELECT * From `sl_iv` Inner Join `ar_iv` On ar_iv.DOCNO = sl_iv.DOCNO Inner Join `payment_terms` On ar_iv.TERMS = payment_terms.id WHERE sl_iv.DOCDATE BETWEEN '".$from."' AND '".$to."'";
				$result = mysqli_query($conn_connection,$test)or die("MySQL error: " . mysqli_error($conn_connection) . "<hr>\nQuery: $test");
			}
			if(isset ($_POST['COMPANYNAME']))
		{
			$COMPANYNAME = $_POST['COMPANYNAME'];
			$fetch = "SELECT * FROM `sl_iv` Inner Join `ar_iv` On ar_iv.DOCNO = sl_iv.DOCNO Inner Join `payment_terms` On ar_iv.TERMS = payment_terms.id WHERE COMPANYNAME = '".$COMPANYNAME."' or sl_iv.DOCDATE BETWEEN '".$from."' AND '".$to."'";
			$result = mysqli_query($conn_connection,$fetch)or die("MySQL error: " . mysqli_error($conn_connection) . "<hr>\nQuery: $fetch");
		}
			if (mysqli_num_rows($result) > 0) {
                // output data of each row
                while($row = mysqli_fetch_assoc($result)) {
                    
                $docamt = $row['DOCAMT']; 
                $paymentamt = $row['PAYMENTAMT'];
                $outstanding = $docamt - $paymentamt;
                
                
                $then = $row['DOCDATE'];
                $then = strtotime($then);
                
                $now = time();
                
                $difference = $now - $then;
                $dateage = floor($difference / (60*60*24));
            ?>
Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.