Magento – Unable to get sales orders limit by date range through SOAP API

apifilterorderssoapwsi

I tried to get a list of orders filtered between two dates using SOAP-UI.

I am using API version 2 WSI complaint mode. This doesn't seem to be working and below is the sample SOAP envilope.

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:Magento">
   <soapenv:Header/>
   <soapenv:Body>
      <urn:salesOrderListRequestParam>
         <sessionId>f805e525b7b86b29b5e80e615361087f</sessionId>
        <filters>
           <!--<filter>
               <complexObjectArray>
                  <key>created_at</key>
                  <value>gteq</value>
               </complexObjectArray>
            </filter>-->
             <complex_filter>
               <complexObjectArray>
                  <key>created_at</key>
                  <value>
                     <key>gteq</key>
                     <value>2015-03-10 01:01:01</value>
                  </value>
               </complexObjectArray>
            </complex_filter>
         </filters>
      </urn:salesOrderListRequestParam>
   </soapenv:Body>
</soapenv:Envelope>

This throws following error:

<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
   <SOAP-ENV:Body>
      <SOAP-ENV:Fault>
         <faultcode>1</faultcode>
         <faultstring>SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'and created_at gteq =  '2015-03-10 01:01:01')' at line 3</faultstring>
      </SOAP-ENV:Fault>
   </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

As this failed I tried to do a sample php to test this is working but that resulted all the orders but filtered.

Below is the sample php code.

$client = new SoapClient('http://www.ruwan.com/Magento1610/index.php/API/v2_soap?wsdl');

$session = $client->login(array('username'=>'soap_user','APIKey'=>'soap_user'));

$sessionId = $session->result;

$params = array('complex_filter'=>  array('key'=>'created_at','value'=>array('key' =>'gteq','value' => '2015-04-10 01:01:01')),
                        array('key'=>'created_at','value'=>array('key' =>'lteq','value' => '2015-05-22 01:01:01')),);   

$result = $client->salesOrderList((object)array('sessionId' => $sessionId, 'filters' => $params ));   

var_dump($result->result);

However, this code too resulted complete list of orders available irrespective of the date range specified.

I wonder what is happening in the Magento back end. I did necessary configurations in Magento get to the web services working.

Best Answer

Check format date table,and magento begining day from zero hours '00:00:00' and ending day in same 23:59:59 , possibles scenaries: m-d-Y 00:00:00 or Y-m-d 00:00:00 , I get same information like as:

 $fromDate = date('Y-m-d'. ' 00:00:00', strtotime($fromDate));
Related Topic