Table of Contents
Introduction
Doc Merge is a very useful but underused feature in Sugar. We have leveraged it in a number of projects and recently had a chance to use it for a Quote generation project for a client that required us to think creatively to produce a complex quote document that also required a reasonably short document generation time.
A need for a complex quote document
The client needed to be able to provide quotes that had multiple scenarios that could show multiple items and the costs for each item across multiple years. They had previously developed a custom Line Item module that could represent an item and the pricing for a single year and associate it to a scenario. An item could be in one or more scenarios. Even though this was done with a custom module, the approach we used can also be applied to the stock Quoted Line Items module.
Here is an example of a set of Line Items that represents two scenarios with multiple items.
They had a way to define their complex quote with the data for their custom module but they needed a way to translate it to a document they could present to their customers. They had an initial idea for the document which we then developed into a full version. Each scenario would exist as a separate section in the generated document. In each section, every item in that scenario would have its own table. In each of those tables there would be a line for each year showing the pricing being offered for that item in a single year. The following is an example of what we were trying to generated for a document using the previous example data.
With some effort, a person could sort the data into those sections and tables to produce a document, but it is error prone and time consuming. There was a definite value to being able to have Doc Merge inside Sugar produce the document.
Our initial solution had a way of getting all of the item numbers for a scenario, looping through all of the items to create a table for each one and then for each item table again looping through the Line Items to produce a list of the line items ordered by year. This approach used the standard looping for a related module that Doc Merge provides and it worked and produced the desired quote.
However, we found that beyond very simple cases, the time to generated the document was growing very long. And the more scenarios, items, and years we added Line Item records for, the longer the generation time would get. For a few scenarios with a few items with less than five years, we were getting generation times of over 30 minutes and it was getting worse as we added more data. The two layers of looping through the related records was simply not going to work.
A new Doc Merge approach
After brainstorming the technical side of this problem, we came up with a new approach for creating the data that would be picked up by Doc Merge. We decided to “pre-render” the data tables for each line item in a scenario so that Doc Merge would only have a single loop for each scenario where it would just need to pick up the block of data to be shown in each table. This does required a code customization, but after considering a few ways to do it, we settled on using just a before_save logic hook on the Line Item module.
The logic hook we made triggers when a Line Item record is created or updated. When this hook is triggered, the table for that line item is generated and saved and then all of the other tables are also updated. This full update of all tables was done to cover the case where a Line Item record was initially in one scenario/item but was moved to another scenario/item, which guarantees that all pre-rendered data is correct. This approach in the logic hook did not add any noticeable time to the save of a Line Item, but it did improve the generation time of a quote document from 30+ minutes to an average of 15 seconds. We also found that adding more scenarios, items, and years added very little time under the new approach and it satisfied the client needs and provided a good user experience.
The technical details plus the code and template
To make this approach work, we needed to specify on the quote what the first year covered by the Line Items would be. This was passed down to the individual Line Items on a calculated field. The pre-rendered table data is saved on the earliest year Line Item record for each table.
For the text in the tables, we needed to have all the pre-rendered text across multiple lines be presented as one text block in the resulting table. We generated column divisions in that text data with the “|” character substituting for vertical lines in the data and used a monospaced font to make sure everything lined up. We did look at using tab characters in the template to line up columns, but the complexity was judged to not be worth it.
Here is the code for the logic hook and the template file that was used. We hope that this information provides some inspiration for solving interesting client customization challenges.
<?php
if (!defined('sugarEntry') || !sugarEntry) define('sugarEntry', true);
class CollectMatchingLineItemsHook
{
/**
* before_save hook: collect other line items under the same parent Quote
* with quantity == 100 and attach them to $bean->matching_line_items (non-persistent)
*/
public function beforeSaveLineItemProcessing(&$bean, $event, $arguments)
{
$parentQuoteId = $this->getParentQuoteId($bean);
if (empty($parentQuoteId)) {
$bean->line_item_group_summary_c = '';
$bean->save_flag_c = '';
return;
}
$subChar = '^';
$cpad = 6;
$vpad = 7;
$newRecordLine = '';
if (empty($bean->fetched_row['id'])) {
if($bean->scenario_key_value_c == '') {
if(strval($bean->hide_line_item_c) !== '1') {
$newRecordLine = str_pad(strval($bean->year_text_c),$cpad)." | ".sprintf("%{$vpad}s", number_format($bean->volume))." | ".$this->formatDecimal($bean->base_unit_price)."| ".$this->formatDecimal($bean->unit_surcharge)."| ".$this->formatDecimal($bean->total_unit_price).$subChar;
}
}
}
if($bean->save_flag_c == 1) {
if($bean->scenario_key_value_c == '') {
$bean->line_item_group_summary_c = '';
}
else {
$this->updateLineItemSummary($bean);
}
$bean->save_flag_c = '';
}
else {
if($bean->scenario_key_value_c == '') {
$bean->line_item_group_summary_c = '';
$this->getAndSaveQuoteBeanKeyItems( $parentQuoteId, null, $newRecordLine, $bean->scenario_item_group_c );
}
else {
$this->updateLineItemSummary($bean);
$this->getAndSaveQuoteBeanKeyItems( $parentQuoteId, $bean->id );
}
}
$bean->new_record_line_text_c = '';
}
/* Returns array of beans for the related items
* The primary key $bean will always be the one in the first [0] position
*/
protected function updateLineItemSummary( &$lineItemBean ) {
$parentQuoteId = $this->getParentQuoteId($lineItemBean);
if (empty($parentQuoteId)) {
$lineItemBean->line_item_group_summary_c = '';
return;
}
$newTextYear = '';
if($lineItemBean->new_record_line_text_c !== '') {
$newTextYear = substr($lineItemBean->new_record_line_text_c, 0, 4);
}
$lineArray = [];
$itemBeansOnQuote = $this->getItemBeansForQBean($parentQuoteId, $lineItemBean->scenario_number, $lineItemBean->item_number);
$subChar = '^';
$cpad = 6;
$vpad = 7;
$lineItemSummaryNew = '';
if(strval($lineItemBean->hide_line_item_c) !== '1') {
$lineItemSummaryNew .= str_pad(strval($lineItemBean->year_text_c),$cpad)." | ".sprintf("%{$vpad}s", number_format($lineItemBean->volume))." | ".$this->formatDecimal($lineItemBean->base_unit_price)."| ".$this->formatDecimal($lineItemBean->unit_surcharge)."| ".$this->formatDecimal($lineItemBean->total_unit_price).$subChar;
$lineArray[$lineItemBean->year_text_c] = str_pad(strval($lineItemBean->year_text_c),$cpad)." | ".sprintf("%{$vpad}s", number_format($lineItemBean->volume))." | ".$this->formatDecimal($lineItemBean->base_unit_price)."| ".$this->formatDecimal($lineItemBean->unit_surcharge)."| ".$this->formatDecimal($lineItemBean->total_unit_price).$subChar;
}
$summaryRecordId = $lineItemBean->id;
foreach ($itemBeansOnQuote as $r) {
if(strval($r->hide_line_item_c) !== '1') {
if($r->id == $lineItemBean->id) {
//nothing
}
else {
$lineItemSummaryNew .= str_pad(strval($r->year_text_c),$cpad)." | ".sprintf("%{$vpad}s", number_format($r->volume))." | ".$this->formatDecimal($r->base_unit_price)."| ".$this->formatDecimal($r->unit_surcharge)."| ".$this->formatDecimal($r->total_unit_price).$subChar;
$lineArray[$r->year_text_c] = str_pad(strval($r->year_text_c),$cpad)." | ".sprintf("%{$vpad}s", number_format($r->volume))." | ".$this->formatDecimal($r->base_unit_price)."| ".$this->formatDecimal($r->unit_surcharge)."| ".$this->formatDecimal($r->total_unit_price).$subChar;
}
}
}
$pos = strrpos($lineItemSummaryNew, $subChar);
if($pos !== false)
{
$lineItemSummaryNew = substr_replace($lineItemSummaryNew, '', $pos, 1);
}
$lineItemSummaryNew .= "\n".$lineItemBean->new_record_line_text_c;
$reassemblItemsSummaryNew = '';
$extraLineInserted = false;
foreach($lineArray as $k => $v) {
if ($k > $newTextYear && !$extraLineInserted) {
$reassemblItemsSummaryNew .= $lineItemBean->new_record_line_text_c;
$extraLineInserted = true;
}
$reassemblItemsSummaryNew .= $v;
}
if($extraLineInserted == false) {
if($lineItemBean->hide_line_item_c !== '1') {
$reassemblItemsSummaryNew .= $lineItemBean->new_record_line_text_c;
}
}
$reassemblItemsSummaryNew = rtrim($reassemblItemsSummaryNew, "^");
$reassemblItemsSummaryNew = str_replace($subChar, "\n", $reassemblItemsSummaryNew);
$lineItemBean->line_item_group_summary_c = $reassemblItemsSummaryNew;
}
/* Returns array of beans for the related items
* The primary key $bean will always be thte on ein the first [0] position
*/
protected function getAndSaveQuoteBeanKeyItems( $quoteId, $omitBeanId = null, $newRecordTextLine = '', $newRecordKey = '' ) {
$quoteBean = BeanFactory::retrieveBean('Quotes', $quoteId);
if(!$quoteBean) {
return false;
}
$quoteBean->load_relationship('id_li_quotes');
$relatedBeans = $quoteBean->id_li_quotes->getBeans();
$filteredBeans = array();
foreach($relatedBeans as $relatedBean) {
if($relatedBean->scenario_key_value_c != '') {
if($relatedBean->id !== $omitBeanId) {
$relatedBean->save_flag_c = 1;
$filteredBeans[] = $relatedBean;
}
else {
// continue;
}
}
}
foreach($filteredBeans as $b) {
$b->save_flag_c = 1;
if($newRecordKey == $b->scenario_key_value_c) {
$b->new_record_line_text_c = $newRecordTextLine;
}
else {
$b->new_record_line_text_c = '';
}
$b->save();
}
}
/* Returns array of beans for the related items
* The primary key $bean will always be thte on ein the first [0] position
*/
protected function getItemBeansForQBean( $quoteId, $scenarioNumber, $itemNumber ) {
$quoteBean = BeanFactory::retrieveBean('Quotes', $quoteId);
if(!$quoteBean) {
return false;
}
$quoteBean->load_relationship('id_li_quotes');
$relatedBeans = $quoteBean->id_li_quotes->getBeans();
$filteredBeans = array();
foreach($relatedBeans as $relatedBean) {
if($relatedBean->scenario_number == $scenarioNumber && $relatedBean->item_number == $itemNumber ) {
$filteredBeans[] = $relatedBean;
}
}
//Bubble sort of relatedBeans by year_text_c
$n = count($filteredBeans);
for ($i = 0; $i < $n - 1; $i++) {
for ($j = 0; $j < $n - $i - 1; $j++) {
if (strcmp($filteredBeans[$j]->year_text_c, $filteredBeans[$j + 1]->year_text_c) > 0) {
$temp = $filteredBeans[$j];
$filteredBeans[$j] = $filteredBeans[$j + 1];
$filteredBeans[$j + 1] = $temp;
}
}
}
return $filteredBeans;
}
/**
* Attempt to determine the parent Quote ID for a line-item bean.
*/
protected function getParentQuoteId($bean)
{
try {
if ($bean->load_relationship('id_li_quotes')) {
$related = $bean->id_li_quotes->getBeans();
if (!empty($related)) {
$ids = array_keys($related);
return reset($ids);
}
}
} catch (Exception $e) {
// ignore
}
return null;
}
protected function formatDecimal($number)
{
$formattedNumber = sprintf("%.5f", $number);
$paddedString = str_pad($formattedNumber, 12, " ", STR_PAD_LEFT).' ';
return $paddedString;
}
}
?>