0

I want to take the increment id from sales order table and status should be complete.I have created one custom table called hydrogen_xml_generation.

Initially the xml generated status is 0. Once the xml is generated it will be 1. How to write the below query in Magento 2.

$sales_shipment = $connection->fetchAll("SELECT increment_id FROM sales_order WHERE status = 'complete' AND sales_order.increment_id NOT IN (SELECT increment_id FROM hydrogen_xml_generation WHERE xml_generated = '1')");
1

You can try something like this

First, inject the OrderRepository and the yourCustomModuleRepository to the constructor parameters:

public function __construct(
    \Magento\Framework\App\Action\Context $context,
    \Magento\Sales\Model\ResourceModel\Order\CollectionFactory $orderCollectionFactory,
    \Namespace\Module\Model\ModuleFactory $moduleFactory
) {
    $this->orderCollectionFactory = $orderCollectionFactory;
    $this->moduleFactory = $moduleFactory;
    parent::__construct(
        $context
    );
}

Then you can use it like this:

$xml_generated = $this->moduleFactory->create();
$columnValues = $xml_generated->getCollection()->addFieldToFilter('xml_generated',1)->getColumnValues('increment_id')
$orderResult = $this->orderCollectionFactory->create()->addAttributeToSelect('*');
$orders = $orderResult->addFieldToFilter('increment_id',array('nin' => $columnValues);
  • Hi Piyush thanks for the help i need for one more query.please help i am a fresher. – Dilip Sep 18 '17 at 8:47
  • yes let me know , what issue you are facing – Piyush Sep 18 '17 at 8:57
  • $details = $connection->fetchAll("SELECT sales_order.customer_id , sales_invoice.increment_id , sales_invoice.grand_total , sales_invoice.created_at , sales_order_payment.method FROM sales_order inner join sales_invoice on sales_invoice.order_id = sales_order.entity_id inner join sales_order_payment on sales_order_payment.parent_id = sales_order.entity_id WHERE sales_order.increment_id = '" .$increment_id. "'"); – Dilip Sep 18 '17 at 9:00
  • $update_xml_generated = ("INSERT INTO hydrogen_xml_generation (increment_id,xml_generated) VALUES('" .$increment_id. "' , '1')"); – Dilip Sep 18 '17 at 9:00
  • $connection->query($update_xml_generated); – Dilip Sep 18 '17 at 9:00

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.