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

  • Have you getting post_at and post_at_to_date value in php file before sql query? – Rakesh Jesadiya Nov 21 '16 at 12:08
  • @Rakesh var_dump($post_at); var_dump($post_at_to_date); result : string(0) "" string(0) "" – Baby in Magento Nov 21 '16 at 12:10
  • if you have not getting post_at and post_at_to_date then How filter works ? – Rakesh Jesadiya Nov 21 '16 at 12:10
  • @Rakesh okay , what i need to do to get solution ? – Baby in Magento Nov 22 '16 at 4:44
  • Where is doFilter() function in your js? – Rakesh Jesadiya Nov 22 '16 at 4:57
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 ); 
  • Tried your code, date picker is not displaying..... – Baby in Magento Nov 21 '16 at 5:59
  • @BabyinMagento check the error log there is error on page? because query is not related to date picker – Qaisar Satti Nov 21 '16 at 6:01
  • okay, i am trying now, i ll let you know exact problem soon...... – Baby in Magento Nov 21 '16 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"/> – Baby in Magento Nov 21 '16 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 – Qaisar Satti Nov 21 '16 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);
?>
  • I truied , but still var_dump($post_at); var_dump($post_at_to_date); results : string(0) "" string(0) "" – Baby in Magento Nov 22 '16 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. – Rakesh Jesadiya Nov 22 '16 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 ..... – Baby in Magento Nov 22 '16 at 6:43
  • what else i need to do for Filtering Dates based on To & From Date? – Baby in Magento Nov 22 '16 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. – Rakesh Jesadiya Nov 22 '16 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 . "'";
}
  • Thanks, I tried your code, but still its not working, also i am not getting any errors also. – Baby in Magento Nov 18 '16 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 – Ashish Jagnani Nov 18 '16 at 18:11
  • I tried this : echo $stmt; , i got error : Catchable fatal error: Object of class PDOStatement could not be converted to string – Baby in Magento Nov 21 '16 at 5:33
  • @BabyinMagento, echo 'SELECT * FROM order_details '.$queryCondition; And try this query in your DB and check whats the error – Ashish Jagnani Nov 21 '16 at 11:30
  • i got this : Notice: Undefined variable: queryCondition – Baby in Magento Nov 21 '16 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, privacy policy and cookie policy

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