I think you want to do the following:
You want to find a particular Order ID, and see if it has any Credit memo IDs, and then get those totals. Then collate the product items/credit memo IDs and totals into a singular 'email as credit memo'?
Even though I do not have a full solution for you at the moment (work not permitting me), maybe this will point you into the right direction:
- A quick MySQL query confirmed there are orders with credit memo items:
Update: swapped sales_order
and sales_creditmemo
in query for corresponding join in PHP.
SELECT SUM(sc.base_grand_total) as FullTotal, sc.order_id as OrderId, sc.base_grand_total as CreditGT, so.base_grand_total as OrderGT,
sc.*, so.* FROM sales_order so
INNER JOIN sales_creditmemo sc
ON sc.order_id = so.entity_id
WHERE sc.order_id IN (3009)
GROUP BY sc.order_id;
**The resultant join in PHP can be converted into this: **
This was not tested, but this shows you the from
, join
, where
parts.
/**
Assume that you provide the order id as input
*/
protected function filterOrder($orderId)
{
$this->sales_order_table = $this->getTable("sales_order");
$this->sales_creditmemo_table = $this->getTable("sales_creditmemo");
$this->getSelect()
->from(array('so' => $this->sales_order_table),
array(
'so.base_grand_total as OrderGT'
)
->join(
array('sales_creditmemo' => $this->sales_order_payment_table),
$this->sales_order_table . '.entity_id = ' . $this->sales_creditmemo_table . '.order_id',
array(
'SUM(so.base_grand_total) as FullTotal',
'sc.order_id as OrderId',
'sc.base_grand_total as CreditMemoGt'
)
);
$this->getSelect()->where('sc.order_id IN (' . $orderId . ')' );
}
Using the query above, notice CreditGT
and OrderGT
totals are the same, which confirms that this could be used to find the sales_creditmemo
items attached to a sales order.
NOTE: I do not have any orders that have multiple credit memos, so I am not sure FullTotal
column sums the credit memo item' totals for the order, so make sure it works. (Technically Credit Memo A+B+C
= OrderID Total
, or in my case Credit Memo A
= OrderID Total
).
- Next step, try to join the working MySQL query into Magento 2 code and create a new Magento 2 module, most likely adding a custom Model:
An example of joining and filtering tables to get you started: How to join 2 tables in Magento 2.
You will most likely want to create a Helper, or a custom Model that overrides the core functions
.
Research on creating a new module, there are plenty of answers here on magento.stackexchange.
The section I think you should target there are most likely these sections:
<!--truncated-->
<h1>{{trans "Your Credit Memo #%creditmemo_id for Order #%order_id" creditmemo_id=$creditmemo.increment_id order_id=$order.increment_id}}</h1>
<!--truncated-->
{{layout handle="sales_email_order_creditmemo_items" creditmemo_id=$creditmemo_id order_id=$order_id}}
To extend it further, this looks like that credit memo template uses this credit memo items template:
For templates and email templates you have to override the templates in your active theme folder:
Notes on email templates, and where they are to be overridden/edited:
NOTE: email templates are .html
files located at the discussed place here email/
, while the items templates (.phtml files
) are in templates/email/
In summary :
./app/design/frontend/Vendor/ThemeName/{Module_Name}/email
has .html email templates
AND
./app/design/frontend/Vendor/ThemeName/{Module_Name}/templates/email
will have .phtml templates
Email templates are stored in the <module_dir>/view//email directory of their respective modules. For example, the template for the new order transactional email for the Sales module is located in <Magento_Sales_module_dir>/view/frontend/email/order_new.html.
We strongly recommend you not change the default Magento files. If you want to customize the default templates, you should create your custom templates and configure Magento to use them instead of the default templates.
You can add custom templates as physical files in your custom theme or create them using the Magento Admin. Both approaches are described in the following sections.
Override email templates by creating templates in a new directory in your custom theme, using this pattern: <theme_dir>/_/email. For example, to override the New Order email template, create a template named order_new.html in the <theme_dir>/Magento_Sales/email directory.
Template fallback is supported for email templates, so parent themes of your current theme are searched for templates.
Customize email templates using the Magento Admin
Any templates configured in the Magento Admin take precedence over default or theme-based templates.
In the Magento Admin, navigate to MARKETING > Communications > Email Templates
Click Add New Template.
If you want to use a default template as a starting point, in the Load default template section, choose the template and click Load Template. The path to the configuration settings for each default template displays in the Currently Used For field in the Template Information section.
Make note of this path because you will need it later when you configure this new template to be used instead of the default template.
Credit Memo Items template
<thead>
<tr>
<th class="item-info">
<?= $block->escapeHtml(__('Items')) ?>
</th>
<th class="item-qty">
<?= $block->escapeHtml(__('Qty')) ?>
</th>
<th class="item-subtotal">
<?= $block->escapeHtml(__('Subtotal')) ?>
</th>
</tr>
</thead>
<?php foreach ($_creditmemo->getAllItems() as $_item) : ?>
<?php if (!$_item->getOrderItem()->getParentItem()) : ?>
<tbody>
<?= $block->getItemHtml($_item) ?>
</tbody>
<?php endif; ?>
<?php endforeach; ?>
<tfoot class="order-totals">
<?= $block->getChildHtml('creditmemo_totals') ?>
</tfoot>
As you can see creditmemo_totals
block seems to render the totals for the credit memo.
The blocks is located at:
./vendor/magento/module-sales/Block/Order/Creditmemo.php
and its subfolder contains the credit memo files:
./vendor/magento/module-sales/Block/Order/Creditmemo/
The model seems to be located at:
/vendor/magento/module-sales/Model/Order/Creditmemo.php
and its subfolder contains the `Item.php` at
/vendor/magento/module-sales/Model/Order/Creditmemo/
Summary
As mentioned, this is not a full solution, there seems alot of things in this puzzle.
I hope I pointed you in the right direction - as from my understanding this is what you are attempting to do.
Steps:
- Convert a working MySQL query (sample provided where I joined the tables) into a Magento 2 PHP coded filter version. Links provided on how to do that
- Most likely need to create a custom module to override some of the mentioned blocks/models or create Helper.
- Override the _email templates
(.html), and optionally the templates used _inside_ the email templates
(.phtml)` and put them into the theme folder location as discussed.