2

magento site : www.1234.com

we are displaying all the orders in page : www.1234.com/php/site6/date.php , user can able to see all orders only when user login to date.php page.

in date.php page, we are displaying Order Id & Order Date & succesfully filtering orders based on entered order_id in textfield with help of below code.

php

function getDesignerCollection()
{
    $user_home = new USER();
    require_once '../../app/Mage.php';
    Mage::app(); 
    $stmts = $user_home->runQuery("SELECT * FROM tbl_users WHERE userID=:uid");
    $stmts->execute(array(":uid"=>$_SESSION['userSession']));
    $rows = $stmts->fetch(PDO::FETCH_ASSOC);

    $accountType=$rows['type'];
    if($accountType=="admin"){

        $order = Mage::getModel('sales/order')->getCollection()->addAttributeToFilter('designer_id',array('nin'=>'0'));

    }
    else
    {
        $order = Mage::getModel('sales/order')->getCollection()->addAttributeToFilter('designer_id',array('like' => '%'.$_SESSION['userSession'].'%'));
    }

    $i=0;
    foreach ($order as $orderData)  
    {

        $orderitems=$orderData['dproduct_id'];
        $orderitemsarray=explode(",",$orderitems);
        $k=0;

        $oDate = new DateTime($orderData['created_at']);
        $sDate = $oDate->format("Y-m-d");
        while($k < count($orderitemsarray))
        {
            if($orderitemsarray[$k]!='0')
            {

                $stmtorders = $user_home->runQuery("SELECT * FROM order_details WHERE designerorder_id=:designerorder_id");
                $stmtorders->execute(array(":designerorder_id"=>$orderData['entity_id']));

                $roworders = $stmtorders->fetch(PDO::FETCH_ASSOC);

                if($roworders['designerorder_id']==''){$dorderStatus="Unpaid";}else{$dorderStatus=$roworders['paid_status'];}

                $productdetail=Mage::getModel('catalog/product')->load($orderitemsarray[$k]); 

                $responce[]=array($orderData->getIncrementId(),$orderData->getIncrementId(),$orderitemsarray[$k],$productdetail->getName(),$designerName,$orderData['status'],$orderData['grand_total'],$orderData['customer_email'],$orderData['shipping_description'],$dorderStatus,$sDate);

            }
            $k++; $i++;
        }

    }       echo json_encode($responce);    

}       

html

<select id="f_value2">
            <option value="entity_id">Order Id</option>             
        </select>
        <input type="text" onkeyup="doFilter()" value="" id="f_value1" >

<form name="frmSearch" method="post" action="">
<input type="text" placeholder="From Date" id="post_at" 
value="" name="post_at" />       
<input type="text" placeholder="To Date" id="post_at_to_date" 
value="" name="post_at_to_date" />

</form>

script

function doFilter() {
        var filterInfo=[
            {
                fieldName : $('select[id=f_value2]').val(),
                logic : "startWith",
                value : Sigma.Util.getValue("f_value1")
            },
            {
                fieldName : Sigma.Util.getValue("f_fieldName12"),
                logic : "greatEqual",
                value : Sigma.Util.getValue("f_value12")
            }
        ]
        var grid=Sigma.$grid("myGrid");
        var rowNOs=grid.applyFilter(filterInfo); 
    }

now we want to filter orders by "From & To Date".

enter image description here

i am trying below php code [not js ] for filtering with From & TO date, i can able to display From & TO Date textfields with datepicker , but when i click on search button its not working.

php

$post_at = '0000-00-00'; 
$post_at_todate = '0000-00-00';

$queryCondition = "";

    if( !empty( $_POST[ 'post_at' ] ) ) 
{           
    $post_at        = date( 'Y-m-d', strtotime( $_POST[ 'post_at' ] ) );
    $post_at_todate = date( 'Y-m-d' );
    if( !empty( $_POST[ 'post_at_to_date' ] ) )
    {
        $post_at_to_date = date( 'Y-m-d', strtotime( $_POST[ 'post_at_to_date' ] ) );
        $post_at_todate = $post_at_to_date;
    }
    $queryCondition .= "WHERE DATE( created_at ) >= '" . $post_at . "' AND DATE( created_at ) <= '" . $post_at_todate . "'";
}

$sqlquery = "SELECT * FROM order_details $queryCondition";
//$sqlquery = "SELECT * FROM order_details where created_at >= '2016-11-17' AND created_at < = '2016-11-19'";
var_dump( $sqlquery );  




    $result = $conn->query($sqlquery);

    if ($result->num_rows > 0) {
        echo $result->num_rows.'result found'."<br>";
        while($row = $result->fetch_assoc()) {
            echo "id: " . $row["id"]. " - date: " . $row["post_at"];
            echo "<br>";
        }
    } else {
        echo "0 results found <br>";
    }
$conn->close();

Edit 2

I got that i am doing mistake that i am fetching Date table from magento db & trying to filter values using custom db, now please help me to filter values with help of magento db [ as similar to filtering through entering order id in search box ] , for orders also i am using magento db only.

custom db : order_details

enter image description here

9
  • Have you getting post_at and post_at_to_date value in php file before sql query? Nov 21, 2016 at 12:08
  • @Rakesh var_dump($post_at); var_dump($post_at_to_date); result : string(0) "" string(0) "" Nov 21, 2016 at 12:10
  • if you have not getting post_at and post_at_to_date then How filter works ? Nov 21, 2016 at 12:10
  • @Rakesh okay , what i need to do to get solution ? Nov 22, 2016 at 4:44
  • Where is doFilter() function in your js? Nov 22, 2016 at 4:57

4 Answers 4

1

try this query

$queryCondition .= "WHERE post_at >= '" . $from_date . "' AND post_at <= '" . $to_date . "'";





$reg_user = new USER();
$stmt = $reg_user->runQuery("SELECT * FROM order_details ".$queryCondition);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$stmt->execute();

updated code

$post_at = '0000-00-00 00:00:00'; 
$post_at_todate = '0000-00-00 00:00:00';

$queryCondition = "";

    if( !empty( $_POST[ 'post_at' ] ) ) 
{           
    $post_at        = date( 'Y-m-d  h:i:s', strtotime( $_POST[ 'post_at' ] ) );
    $post_at_todate = date( 'Y-m-d' );
    if( !empty( $_POST[ 'post_at_to_date' ] ) )
    {
        $post_at_to_date = date( 'Y-m-d h:i:s', strtotime( $_POST[ 'post_at_to_date' ] ) );
        $post_at_todate = $post_at_to_date;
    }
    $queryCondition .= "WHERE  created_at  >= '" . $post_at . "' AND  created_at  <= '" . $post_at_todate . "'";
}

$sqlquery = "SELECT * FROM order_details $queryCondition";
//$sqlquery = "SELECT * FROM order_details where created_at >= '2016-11-17' AND created_at < = '2016-11-19'";
var_dump( $sqlquery ); 
17
  • Tried your code, date picker is not displaying..... Nov 21, 2016 at 5:59
  • @BabyinMagento check the error log there is error on page? because query is not related to date picker Nov 21, 2016 at 6:01
  • okay, i am trying now, i ll let you know exact problem soon...... Nov 21, 2016 at 6:25
  • for from & To date also i am trying the same javascript code that i used to filter orders through searching in textfield , looks like the code is not filtering : <input type="text" placeholder="To Date" id="post_at_to_date" value="<?php echo $post_at_to_date; ?>" name="search[post_at_to_date]" onkeyup="doFilter()" value="" id="f_value1"/> Nov 21, 2016 at 7:14
  • there are two value attribute in that <input type="text" placeholder="To Date" id="post_at_to_date" value="<?php echo $post_at_to_date; ?>" name="search[post_at_to_date]" onkeyup="doFilter()" /> try now Nov 21, 2016 at 7:17
2
+100

I have debug your code and found issue in name attribute.

You have to just replace name="search[post_at]" with name="post_at"

And

You have to just replace name="search[post_at_to_date]" with name="post_at_to_date"

in form input tag.

Also remove onkeyup() if you dont need.

<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "test1";

echo "<pre>";print_r($_POST);

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$post_at = "";
$post_at_to_date = "";
$queryCondition = "";
    if(!empty($_POST["post_at"])) 
    {           
        $post_at = date('Y-m-d 0:0:0',strtotime($_POST["post_at"]));
        $post_at_todate = date('Y-m-d:59:59:59');
        if(!empty($_POST["post_at_to_date"])) {
            $post_at_to_date = date('Y-m-d 59:59:59',strtotime($_POST["post_at_to_date"]));
            $post_at_todate = $post_at_to_date;
        }
        $queryCondition .= "WHERE post_at >= '" . $post_at . "' AND post_at <= '" . $post_at_todate . "'";
    }

    $sqlquery = "SELECT * FROM order_details $queryCondition";
    $result = $conn->query($sqlquery);

    if ($result->num_rows > 0) {
        echo $result->num_rows.'result found'."<br>";
        while($row = $result->fetch_assoc()) {
            echo "id: " . $row["id"]. " - date: " . $row["post_at"];
            echo "<br>";
        }
    } else {
        echo "0 results found <br>";
    }
$conn->close();
?>

<form name="frmSearch" method="post" action="">
<input type="text" placeholder="From Date" id="post_at" value="" name="post_at" />       
<input type="text" placeholder="To Date" id="post_at_to_date" value="" name="post_at_to_date" />
<input type="submit" name="search" value="search" />
</form>

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

Now after post form,

You have got value using,

$_POST["post_at"] and $_POST["post_at_to_date"] and create your query.

Magento Way:

<?php
$resource = Mage::getSingleton('core/resource');
$readConnection = $resource->getConnection('core_read');

$tableName = $resource->getTableName('order_details');

$post_at = "";
$post_at_to_date = "";
$queryCondition = "";
    if(!empty($_POST["post_at"])) 
    {           
        $post_at = date('Y-m-d 0:0:0',strtotime($_POST["post_at"]));
        $post_at_todate = date('Y-m-d:59:59:59');
        if(!empty($_POST["post_at_to_date"])) {
            $post_at_to_date = date('Y-m-d 59:59:59',strtotime($_POST["post_at_to_date"]));
            $post_at_todate = $post_at_to_date;
        }
        $queryCondition .= "WHERE post_at >= '" . $post_at . "' AND post_at <= '" . $post_at_todate . "'";
    }

    $query = 'SELECT * FROM ' . $tableName. ' '.$queryCondition;
    $results = $readConnection->fetchAll($query);
    var_dump($results);
?>
23
  • I truied , but still var_dump($post_at); var_dump($post_at_to_date); results : string(0) "" string(0) "" Nov 22, 2016 at 6:39
  • you have to try with $_POST["post_at"] and $_POST["post_at_to_date"] and change above form element. I have getting value for all those. Nov 22, 2016 at 6:41
  • you are right, now var_dump($_POST["post_at"]); var_dump($_POST["post_at_to_date"] ); results "2016-11-01" string(10) "2016-11-01" , can you please help me to create query ..... Nov 22, 2016 at 6:43
  • what else i need to do for Filtering Dates based on To & From Date? Nov 22, 2016 at 6:50
  • I have updated my working answer, please check above answer, which is based on filter of two date. Let me know if you have any issue. Nov 22, 2016 at 6:52
1

Try this code for :

if(!empty($_POST["search"]["post_at"])) 
{           
    $post_at = date('Y-m-d 0:0:0',strtotime($_POST["search"]["post_at"]));
    $post_at_todate = date('Y-m-d:59:59:59');
    if(!empty($_POST["search"]["post_at_to_date"])) {
        $post_at_to_date = date('Y-m-d 59:59:59',strtotime($_POST["search"]["post_at_to_date"]));
        $post_at_todate = $post_at_to_date;
    }

    $queryCondition .= "WHERE post_at BETWEEN '".$post_at."' AND '" . $post_at_todate . "'";
}
9
  • Thanks, I tried your code, but still its not working, also i am not getting any errors also. Nov 18, 2016 at 15:36
  • echo your query and try it directly in db. that will show you error if any. also change the dateformat of date picker and try like mm-dd-yy Nov 18, 2016 at 18:11
  • I tried this : echo $stmt; , i got error : Catchable fatal error: Object of class PDOStatement could not be converted to string Nov 21, 2016 at 5:33
  • @BabyinMagento, echo 'SELECT * FROM order_details '.$queryCondition; And try this query in your DB and check whats the error Nov 21, 2016 at 11:30
  • i got this : Notice: Undefined variable: queryCondition Nov 21, 2016 at 11:44
1

php

function getDesignerCollection()
{
    $user_home = new USER();
    require_once '../../app/Mage.php';
    Mage::app(); 
    $stmts = $user_home->runQuery("SELECT * FROM tbl_users WHERE userID=:uid");
    $stmts->execute(array(":uid"=>$_SESSION['userSession']));
    $rows = $stmts->fetch(PDO::FETCH_ASSOC);
 //  echo $_POST['post_at'];
   // echo $_POST['post_at_to_date'];
    $orderFromDate='';
    $orderToDate='';

    if(isset($_POST['post_at']) && $_POST['post_at']!='')
    {
        $orderFromDate=$_POST['post_at']." 59:59:59";
    }
    if(isset($_POST['post_at_to_date']) && $_POST['post_at_to_date']!='')
    {
        $orderToDate=$_POST['post_at_to_date']." 59:59:59";
    }
    $accountType=$rows['type'];
    if($accountType=="admin"){

        $order = Mage::getModel('sales/order')->getCollection()->addAttributeToFilter('designer_id',array('nin'=>'0'));
        if($orderFromDate!='')$order->addFieldToFilter('created_at',array('gteq'=>$orderFromDate));
        if($orderToDate!='')$order->addFieldToFilter('created_at',array('lteq'=>$orderToDate));
        // echo $order->getSelect();
        //$order = Mage::getModel('sales/order')->getCollection()->addAttributeToFilter('designer_id',array('nin'=>'0'));

    }
    else
    {
        $order = Mage::getModel('sales/order')->getCollection()->addAttributeToFilter('designer_id',array('like' => '%'.$_SESSION['userSession'].'%'));
    }

    $i=0;
    foreach ($order as $orderData)  
    {

        $orderitems=$orderData['dproduct_id'];
        $orderitemsarray=explode(",",$orderitems);
        $k=0;

        $oDate = new DateTime($orderData['created_at']);
        $sDate = $oDate->format("Y-m-d");
        while($k < count($orderitemsarray))
        {
           if($orderitemsarray[$k]!='0')
          {

            $stmtorders = $user_home->runQuery("SELECT * FROM order_details WHERE designerorder_id=:designerorder_id");
            $stmtorders->execute(array(":designerorder_id"=>$orderData['entity_id']));
            $roworders = $stmtorders->fetch(PDO::FETCH_ASSOC);

            if($roworders['designerorder_id']==''){$dorderStatus="Unpaid";}else{$dorderStatus=$roworders['paid_status'];}

          $productdetail=Mage::getModel('catalog/product')->load($orderitemsarray[$k]); 
          $designerName=getDesignerName($productdetail->getDesignerID())." -(".$productdetail->getDesignerID().")";   
          $responce[]=array($orderData->getIncrementId(),$orderData->getIncrementId(),$orderitemsarray[$k],$productdetail->getName(),$designerName,$orderData['status'],$orderData['grand_total'],$orderData['customer_email'],$orderData['shipping_description'],$dorderStatus,$sDate);

         }
        $k++; $i++;
        }

    }       echo json_encode($responce);    


}   

script

function doFilter() {
        var filterInfo=[
            {
                fieldName : $('select[id=f_value2]').val(),
                logic : "startWith",
                value : Sigma.Util.getValue("f_value1")
            },
            {
                fieldName : Sigma.Util.getValue("f_fieldName12"),
                logic : "greatEqual",
                value : Sigma.Util.getValue("f_value12")
            }
        ]
        var grid=Sigma.$grid("myGrid");
        var rowNOs=grid.applyFilter(filterInfo); 
    }
    function doUnfilter(){
        var grid=Sigma.$grid("myGrid1");
        var rowNOs=grid.unfilterGrid();

    }   


function doUnfilter(){
    var grid=Sigma.$grid("myGrid1");
    var rowNOs=grid.unfilterGrid();

}   

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.