5

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.

2
  • 1
    The complex_filter array looks good. Anyway, there's something wrong with parsing the condition to SQL: and created_at gteq = '2015-03-10 01:01:01'. Which version of Magento are you using? – Anna Völkl Mar 27 '15 at 14:20
  • I think you could go with ge instead of gteq and le instead of lteq – Jeroen Apr 8 '15 at 19:32
1

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));

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.