/** This function returns the detailed list of vtiger_products associated to a given entity or a record.
* Param $module - module name
* Param $focus - module object
* Param $seid - sales entity id
* Return type is an object array
*/
function getAssociatedProducts($module, $focus, $seid = '')
{
global $log, $adb, $theme, $current_user;
$log->debug("Entering getAssociatedProducts(" . $module . "," . get_class($focus) . "," . $seid . "='') method ...");
$theme_path = "themes/" . $theme . "/";
$image_path = $theme_path . "images/";
$product_Detail = array();
if ($module == 'Quotes' || $module == 'PurchaseOrder' || $module == 'SalesOrder' || $module == 'Invoice') {
$query = "SELECT\n\t\t\tcase when vtiger_products.productid != '' then vtiger_products.productname else vtiger_service.servicename end as productname,\n\t\t\tcase when vtiger_products.productid != '' then vtiger_products.productcode else vtiger_service.service_no end as productcode,\n\t\t\tcase when vtiger_products.productid != '' then vtiger_products.unit_price else vtiger_service.unit_price end as unit_price,\n\t\t\tcase when vtiger_products.productid != '' then vtiger_products.qtyinstock else 'NA' end as qtyinstock,\n\t\t\tcase when vtiger_products.productid != '' then 'Products' else 'Services' end as entitytype,\n\t\t\tvtiger_inventoryproductrel.listprice,\n\t\t\tvtiger_inventoryproductrel.description AS product_description,\n\t\t\tvtiger_inventoryproductrel.*\n\t\t\tFROM vtiger_inventoryproductrel\n\t\t\tLEFT JOIN vtiger_products ON vtiger_products.productid=vtiger_inventoryproductrel.productid\n\t\t\tLEFT JOIN vtiger_service ON vtiger_service.serviceid=vtiger_inventoryproductrel.productid\n\t\t\tWHERE id=? ORDER BY sequence_no";
$params = array($focus->id);
if ($module != 'PurchaseOrder') {
if (GlobalVariable::getVariable('B2B', '1') == '1') {
$acvid = $focus->column_fields['account_id'];
} else {
$acvid = $focus->column_fields['contact_id'];
}
} else {
$acvid = $focus->column_fields['vendor_id'];
}
} elseif ($module == 'Potentials') {
$query = "SELECT vtiger_products.productid, vtiger_products.productname, vtiger_products.productcode,\n\t\t\tvtiger_products.unit_price, vtiger_products.qtyinstock, vtiger_crmentity.description AS product_description,\n\t\t\t'Products' AS entitytype\n\t\t\tFROM vtiger_products\n\t\t\tINNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid=vtiger_products.productid\n\t\t\tINNER JOIN vtiger_seproductsrel ON vtiger_seproductsrel.productid=vtiger_products.productid\n\t\t\tWHERE vtiger_seproductsrel.crmid=?";
$query .= " UNION SELECT vtiger_service.serviceid AS productid, vtiger_service.servicename AS productname,\n\t\t\t'NA' AS productcode, vtiger_service.unit_price AS unit_price, 'NA' AS qtyinstock,\n\t\t\tvtiger_crmentity.description AS product_description, 'Services' AS entitytype\n\t\t\tFROM vtiger_service\n\t\t\tINNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid=vtiger_service.serviceid\n\t\t\tINNER JOIN vtiger_crmentityrel ON vtiger_crmentityrel.relcrmid=vtiger_service.serviceid\n\t\t\tWHERE vtiger_crmentityrel.crmid=?";
$params = array($seid, $seid);
} elseif ($module == 'Products') {
$query = "SELECT vtiger_products.productid, vtiger_products.productcode, vtiger_products.productname,\n\t\t\tvtiger_products.unit_price, vtiger_products.qtyinstock, vtiger_crmentity.description AS product_description,\n\t\t\t'Products' AS entitytype\n\t\t\tFROM vtiger_products\n\t\t\tINNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid=vtiger_products.productid\n\t\t\tWHERE vtiger_crmentity.deleted=0 AND productid=?";
$params = array($seid);
} elseif ($module == 'Services') {
$query = "SELECT vtiger_service.serviceid AS productid, 'NA' AS productcode, vtiger_service.servicename AS productname,\n\t\t\tvtiger_service.unit_price AS unit_price, 'NA' AS qtyinstock, vtiger_crmentity.description AS product_description,\n\t\t\t'Services' AS entitytype\n\t\t\tFROM vtiger_service\n\t\t\tINNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid=vtiger_service.serviceid\n\t\t\tWHERE vtiger_crmentity.deleted=0 AND serviceid=?";
$params = array($seid);
}
$cbMap = cbMap::getMapByName($module . 'InventoryDetails', 'MasterDetailLayout');
$MDMapFound = $cbMap != null;
if ($MDMapFound) {
$cbMapFields = $cbMap->MasterDetailLayout();
}
$result = $adb->pquery($query, $params);
$num_rows = $adb->num_rows($result);
for ($i = 1; $i <= $num_rows; $i++) {
$hdnProductId = $adb->query_result($result, $i - 1, 'productid');
$hdnProductcode = $adb->query_result($result, $i - 1, 'productcode');
$productname = $adb->query_result($result, $i - 1, 'productname');
$productdescription = $adb->query_result($result, $i - 1, 'product_description');
$comment = $adb->query_result($result, $i - 1, 'comment');
$qtyinstock = $adb->query_result($result, $i - 1, 'qtyinstock');
$qty = $adb->query_result($result, $i - 1, 'quantity');
$unitprice = $adb->query_result($result, $i - 1, 'unit_price');
$listprice = $adb->query_result($result, $i - 1, 'listprice');
$entitytype = $adb->query_result($result, $i - 1, 'entitytype');
if (!empty($entitytype)) {
$product_Detail[$i]['entityType' . $i] = $entitytype;
}
$product_Detail[$i]['lineitem_id' . $i] = $adb->query_result($result, $i - 1, 'lineitem_id');
if ($listprice == '') {
$listprice = $unitprice;
}
if ($qty == '') {
$qty = 1;
}
//calculate productTotal
$productTotal = $qty * $listprice;
//Delete link in First column
if ($i != 1) {
$product_Detail[$i]['delRow' . $i] = "Del";
}
if (empty($focus->mode) && $seid != '') {
$sub_prod_query = $adb->pquery("SELECT crmid as prod_id from vtiger_seproductsrel WHERE productid=? AND setype='Products'", array($seid));
} else {
$sub_prod_query = $adb->pquery("SELECT productid as prod_id from vtiger_inventorysubproductrel WHERE id=? AND sequence_no=?", array($focus->id, $i));
}
$subprodid_str = '';
$subprodname_str = '';
$subProductArray = array();
if ($adb->num_rows($sub_prod_query) > 0) {
for ($j = 0; $j < $adb->num_rows($sub_prod_query); $j++) {
$sprod_id = $adb->query_result($sub_prod_query, $j, 'prod_id');
$sprod_name = $subProductArray[] = getProductName($sprod_id);
$str_sep = "";
if ($j > 0) {
$str_sep = ":";
}
$subprodid_str .= $str_sep . $sprod_id;
$subprodname_str .= $str_sep . " - " . $sprod_name;
}
}
$subprodname_str = str_replace(":", "<br>", $subprodname_str);
$product_Detail[$i]['subProductArray' . $i] = $subProductArray;
$product_Detail[$i]['hdnProductId' . $i] = $hdnProductId;
$product_Detail[$i]['productName' . $i] = from_html($productname);
/* Added to fix the issue Product Pop-up name display*/
if ($_REQUEST['action'] == 'CreateSOPDF' || $_REQUEST['action'] == 'CreatePDF' || $_REQUEST['action'] == 'SendPDFMail') {
$product_Detail[$i]['productName' . $i] = htmlspecialchars($product_Detail[$i]['productName' . $i]);
}
$product_Detail[$i]['hdnProductcode' . $i] = $hdnProductcode;
$product_Detail[$i]['productDescription' . $i] = from_html($productdescription);
if ($module == 'Potentials' || $module == 'Products' || $module == 'Services') {
$product_Detail[$i]['comment' . $i] = $productdescription;
} else {
$product_Detail[$i]['comment' . $i] = $comment;
}
if ($MDMapFound) {
foreach ($cbMapFields['detailview']['fields'] as $mdfield) {
$mdrs = $adb->pquery('select ' . $mdfield['fieldinfo']['name'] . ' from vtiger_inventorydetails
inner join vtiger_crmentity on crmid=vtiger_inventorydetails.inventorydetailsid
inner join vtiger_inventorydetailscf on vtiger_inventorydetailscf.inventorydetailsid=vtiger_inventorydetails.inventorydetailsid
where deleted=0 and related_to=? and lineitem_id=?', array($focus->id, $adb->query_result($result, $i - 1, 'lineitem_id')));
if ($mdrs) {
$col_fields = array();
$col_fields[$mdfield['fieldinfo']['name']] = $adb->query_result($mdrs, 0, 0);
$foutput = getOutputHtml($mdfield['fieldinfo']['uitype'], $mdfield['fieldinfo']['name'], $mdfield['fieldinfo']['label'], 100, $col_fields, 0, 'InventoryDetails', 'edit', $mdfield['fieldinfo']['typeofdata']);
$product_Detail[$i]['moreinfo' . $i][] = $foutput;
}
}
}
if ($module != 'PurchaseOrder' && $focus->object_name != 'Order') {
$product_Detail[$i]['qtyInStock' . $i] = $qtyinstock;
}
$qty = number_format($qty, 2, '.', '');
//Convert to 2 decimals
$listprice = number_format($listprice, 2, '.', '');
//Convert to 2 decimals
$product_Detail[$i]['qty' . $i] = $qty;
$product_Detail[$i]['listPrice' . $i] = $listprice;
$product_Detail[$i]['unitPrice' . $i] = $unitprice;
$product_Detail[$i]['productTotal' . $i] = $productTotal;
$product_Detail[$i]['subproduct_ids' . $i] = $subprodid_str;
$product_Detail[$i]['subprod_names' . $i] = $subprodname_str;
$discount_percent = $adb->query_result($result, $i - 1, 'discount_percent');
$discount_amount = $adb->query_result($result, $i - 1, 'discount_amount');
$discount_amount = number_format(is_numeric($discount_amount) ? $discount_amount : 0, 2, '.', '');
//Convert to 2 decimals
$discountTotal = '0.00';
//Based on the discount percent or amount we will show the discount details
//To avoid NaN javascript error, here we assign 0 initially to' %of price' and 'Direct Price reduction'(for Each Product)
$product_Detail[$i]['discount_percent' . $i] = 0;
$product_Detail[$i]['discount_amount' . $i] = 0;
if ($discount_percent != 'NULL' && $discount_percent != '') {
$product_Detail[$i]['discount_type' . $i] = "percentage";
$product_Detail[$i]['discount_percent' . $i] = $discount_percent;
$product_Detail[$i]['checked_discount_percent' . $i] = ' checked';
$product_Detail[$i]['style_discount_percent' . $i] = ' style="visibility:visible"';
$product_Detail[$i]['style_discount_amount' . $i] = ' style="visibility:hidden"';
$discountTotal = $productTotal * $discount_percent / 100;
} elseif ($discount_amount != 'NULL' && $discount_amount != '') {
$product_Detail[$i]['discount_type' . $i] = "amount";
$product_Detail[$i]['discount_amount' . $i] = $discount_amount;
$product_Detail[$i]['checked_discount_amount' . $i] = ' checked';
$product_Detail[$i]['style_discount_amount' . $i] = ' style="visibility:visible"';
$product_Detail[$i]['style_discount_percent' . $i] = ' style="visibility:hidden"';
$discountTotal = $discount_amount;
} else {
$product_Detail[$i]['checked_discount_zero' . $i] = ' checked';
}
$totalAfterDiscount = $productTotal - $discountTotal;
$product_Detail[$i]['discountTotal' . $i] = $discountTotal;
$product_Detail[$i]['totalAfterDiscount' . $i] = $totalAfterDiscount;
$taxTotal = '0.00';
$product_Detail[$i]['taxTotal' . $i] = $taxTotal;
//Calculate netprice
$netPrice = $totalAfterDiscount + $taxTotal;
//if condition is added to call this function when we create PO/SO/Quotes/Invoice from Product module
if ($module == 'PurchaseOrder' || $module == 'SalesOrder' || $module == 'Quotes' || $module == 'Invoice') {
$taxtype = getInventoryTaxType($module, $focus->id);
if ($taxtype == 'individual') {
//Add the tax with product total and assign to netprice
$netPrice = $netPrice + $taxTotal;
}
}
$product_Detail[$i]['netPrice' . $i] = $netPrice;
//First we will get all associated taxes as array
$tax_details = getTaxDetailsForProduct($hdnProductId, 'all', $acvid);
//Now retrieve the tax values from the current query with the name
for ($tax_count = 0; $tax_count < count($tax_details); $tax_count++) {
$tax_name = $tax_details[$tax_count]['taxname'];
$tax_label = $tax_details[$tax_count]['taxlabel'];
$tax_value = '0.00';
//condition to avoid this function call when create new PO/SO/Quotes/Invoice from Product module
if ($focus->id != '') {
if ($taxtype == 'individual') {
//if individual then show the entered tax percentage
$tax_value = getInventoryProductTaxValue($focus->id, $hdnProductId, $tax_name);
} else {
//if group tax then we have to show the default value when change to individual tax
$tax_value = $tax_details[$tax_count]['percentage'];
}
} else {
//if the above function not called then assign the default associated value of the product
$tax_value = $tax_details[$tax_count]['percentage'];
}
$product_Detail[$i]['taxes'][$tax_count]['taxname'] = $tax_name;
$product_Detail[$i]['taxes'][$tax_count]['taxlabel'] = $tax_label;
$product_Detail[$i]['taxes'][$tax_count]['percentage'] = $tax_value;
}
}
//set the taxtype
$product_Detail[1]['final_details']['taxtype'] = $taxtype;
//Get the Final Discount, S&H charge, Tax for S&H and Adjustment values
//To set the Final Discount details
$finalDiscount = '0.00';
$product_Detail[1]['final_details']['discount_type_final'] = 'zero';
$subTotal = $focus->column_fields['hdnSubTotal'] != '' ? $focus->column_fields['hdnSubTotal'] : '0.00';
$subTotal = number_format($subTotal, 2, '.', '');
//Convert to 2 decimals
$product_Detail[1]['final_details']['hdnSubTotal'] = $subTotal;
$discountPercent = $focus->column_fields['hdnDiscountPercent'] != '' ? $focus->column_fields['hdnDiscountPercent'] : '0.00';
$discountAmount = $focus->column_fields['hdnDiscountAmount'] != '' ? $focus->column_fields['hdnDiscountAmount'] : '0.00';
$discountAmount = number_format($discountAmount, 2, '.', '');
//Convert to 2 decimals
//To avoid NaN javascript error, here we assign 0 initially to' %of price' and 'Direct Price reduction'(For Final Discount)
$product_Detail[1]['final_details']['discount_percentage_final'] = 0;
$product_Detail[1]['final_details']['discount_amount_final'] = 0;
if ($focus->column_fields['hdnDiscountPercent'] != '0') {
$finalDiscount = $subTotal * $discountPercent / 100;
$product_Detail[1]['final_details']['discount_type_final'] = 'percentage';
$product_Detail[1]['final_details']['discount_percentage_final'] = $discountPercent;
$product_Detail[1]['final_details']['checked_discount_percentage_final'] = ' checked';
$product_Detail[1]['final_details']['style_discount_percentage_final'] = ' style="visibility:visible"';
$product_Detail[1]['final_details']['style_discount_amount_final'] = ' style="visibility:hidden"';
} elseif ($focus->column_fields['hdnDiscountAmount'] != '0') {
$finalDiscount = $focus->column_fields['hdnDiscountAmount'];
$product_Detail[1]['final_details']['discount_type_final'] = 'amount';
$product_Detail[1]['final_details']['discount_amount_final'] = $discountAmount;
$product_Detail[1]['final_details']['checked_discount_amount_final'] = ' checked';
$product_Detail[1]['final_details']['style_discount_amount_final'] = ' style="visibility:visible"';
$product_Detail[1]['final_details']['style_discount_percentage_final'] = ' style="visibility:hidden"';
}
$finalDiscount = number_format($finalDiscount, 2, '.', '');
//Convert to 2 decimals
$product_Detail[1]['final_details']['discountTotal_final'] = $finalDiscount;
//To set the Final Tax values
//we will get all taxes. if individual then show the product related taxes only else show all taxes
//suppose user want to change individual to group or vice versa in edit time the we have to show all taxes. so that here we will store all the taxes and based on need we will show the corresponding taxes
$taxtotal = '0.00';
//First we should get all available taxes and then retrieve the corresponding tax values
$tax_details = getAllTaxes('available', '', 'edit', $focus->id);
$ipr_cols = $adb->getColumnNames('vtiger_inventoryproductrel');
for ($tax_count = 0; $tax_count < count($tax_details); $tax_count++) {
$tax_name = $tax_details[$tax_count]['taxname'];
$tax_label = $tax_details[$tax_count]['taxlabel'];
//if taxtype is individual and want to change to group during edit time then we have to show the all available taxes and their default values
//Also taxtype is group and want to change to individual during edit time then we have to provide the asspciated taxes and their default tax values for individual products
if ($taxtype == 'group') {
if (in_array($tax_name, $ipr_cols)) {
$tax_percent = $adb->query_result($result, 0, $tax_name);
} else {
$tax_percent = $tax_details[$tax_count]['percentage'];
}
} else {
$tax_percent = $tax_details[$tax_count]['percentage'];
}
//$adb->query_result($result,0,$tax_name);
if ($tax_percent == '' || $tax_percent == 'NULL') {
$tax_percent = '0.00';
}
$taxamount = ($subTotal - $finalDiscount) * $tax_percent / 100;
$taxamount = number_format($taxamount, 2, '.', '');
//Convert to 2 decimals
$taxtotal = $taxtotal + $taxamount;
$product_Detail[1]['final_details']['taxes'][$tax_count]['taxname'] = $tax_name;
$product_Detail[1]['final_details']['taxes'][$tax_count]['taxlabel'] = $tax_label;
$product_Detail[1]['final_details']['taxes'][$tax_count]['percentage'] = $tax_percent;
$product_Detail[1]['final_details']['taxes'][$tax_count]['amount'] = $taxamount;
}
$product_Detail[1]['final_details']['tax_totalamount'] = $taxtotal;
//To set the Shipping & Handling charge
$shCharge = $focus->column_fields['hdnS_H_Amount'] != '' ? $focus->column_fields['hdnS_H_Amount'] : '0.00';
$shCharge = number_format($shCharge, 2, '.', '');
//Convert to 2 decimals
$product_Detail[1]['final_details']['shipping_handling_charge'] = $shCharge;
//To set the Shipping & Handling tax values
//calculate S&H tax
$shtaxtotal = '0.00';
//First we should get all available taxes and then retrieve the corresponding tax values
$shtax_details = getAllTaxes('available', 'sh', 'edit', $focus->id);
//if taxtype is group then the tax should be same for all products in vtiger_inventoryproductrel table
for ($shtax_count = 0; $shtax_count < count($shtax_details); $shtax_count++) {
$shtax_name = $shtax_details[$shtax_count]['taxname'];
$shtax_label = $shtax_details[$shtax_count]['taxlabel'];
$shtax_percent = '0.00';
//if condition is added to call this function when we create PO/SO/Quotes/Invoice from Product module
if ($module == 'PurchaseOrder' || $module == 'SalesOrder' || $module == 'Quotes' || $module == 'Invoice') {
$shtax_percent = getInventorySHTaxPercent($focus->id, $shtax_name);
}
$shtaxamount = $shCharge * $shtax_percent / 100;
$shtaxtotal = $shtaxtotal + $shtaxamount;
$product_Detail[1]['final_details']['sh_taxes'][$shtax_count]['taxname'] = $shtax_name;
$product_Detail[1]['final_details']['sh_taxes'][$shtax_count]['taxlabel'] = $shtax_label;
$product_Detail[1]['final_details']['sh_taxes'][$shtax_count]['percentage'] = $shtax_percent;
$product_Detail[1]['final_details']['sh_taxes'][$shtax_count]['amount'] = $shtaxamount;
}
$product_Detail[1]['final_details']['shtax_totalamount'] = $shtaxtotal;
//To set the Adjustment value
$adjustment = $focus->column_fields['txtAdjustment'] != '' ? $focus->column_fields['txtAdjustment'] : '0.00';
$adjustment = number_format($adjustment, 2, '.', '');
//Convert to 2 decimals
$product_Detail[1]['final_details']['adjustment'] = $adjustment;
//To set the grand total
$grandTotal = $focus->column_fields['hdnGrandTotal'] != '' ? $focus->column_fields['hdnGrandTotal'] : '0.00';
$grandTotal = number_format($grandTotal, 2, '.', '');
//Convert to 2 decimals
$product_Detail[1]['final_details']['grandTotal'] = $grandTotal;
$log->debug("Exiting getAssociatedProducts method ...");
return $product_Detail;
}
$recordName = array_values(getEntityName($currentModule, $record));
$recordName = $recordName[0];
$smarty->assign('NAME', $recordName);
$smarty->assign('UPDATEINFO', updateInfo($record));
}
if ($focus->mode == 'edit') {
$smarty->assign('UPDATEINFO', updateInfo($focus->id));
$associated_prod = getAssociatedProducts('Issuecards', $focus);
$smarty->assign('ASSOCIATEDPRODUCTS', $associated_prod);
$smarty->assign('MODE', $focus->mode);
} elseif (isset($_REQUEST['isDuplicate']) && $_REQUEST['isDuplicate'] == 'true') {
$smarty->assign('ASSOCIATEDPRODUCTS', $associated_prod);
$smarty->assign('AVAILABLE_PRODUCTS', 'true');
$smarty->assign('MODE', $focus->mode);
}
$cbMap = cbMap::getMapByName($currentModule . 'InventoryDetails', 'MasterDetailLayout');
if ($cbMap != null) {
$cbMapFields = $cbMap->MasterDetailLayout();
$smarty->assign('moreinfofields', "'" . implode("','", $cbMapFields['detailview']['fieldnames']) . "'");
if (empty($associated_prod)) {
// creating
$product_Detail = $col_fields = array();
foreach ($cbMapFields['detailview']['fields'] as $mdfield) {
$col_fields[$mdfield['fieldinfo']['name']] = '';
$foutput = getOutputHtml($mdfield['fieldinfo']['uitype'], $mdfield['fieldinfo']['name'], $mdfield['fieldinfo']['label'], 100, $col_fields, 0, 'InventoryDetails', 'edit', $mdfield['fieldinfo']['typeofdata']);
$product_Detail['moreinfo'][] = $foutput;
}
$associated_prod = $product_Detail;
}
}
$smarty->assign("ASSOCIATEDPRODUCTS", $associated_prod);
/** This function returns a HTML output of associated vtiger_products for a given entity (Quotes,Invoice,Sales order or Purchase order)
* Param $module - module name
* Param $focus - module object
* Return type string
*/
function getDetailAssociatedProducts($module, $focus)
{
global $log, $adb, $mod_strings, $theme, $log, $app_strings, $current_user;
$log->debug("Entering getDetailAssociatedProducts(" . $module . "," . get_class($focus) . ") method ...");
$theme_path = "themes/" . $theme . "/";
$image_path = $theme_path . "images/";
if (vtlib_isModuleActive("Products")) {
$hide_stock = 'no';
} else {
$hide_stock = 'yes';
}
$tabid = getTabid($module);
if ($module != 'PurchaseOrder') {
if (GlobalVariable::getVariable('B2B', '1') == '1') {
$acvid = $focus->column_fields['account_id'];
} else {
$acvid = $focus->column_fields['contact_id'];
}
} else {
$acvid = $focus->column_fields['vendor_id'];
}
$cbMap = cbMap::getMapByName($module . 'InventoryDetails', 'MasterDetailLayout');
$MDMapFound = $cbMap != null;
if ($MDMapFound) {
$cbMapFields = $cbMap->MasterDetailLayout();
}
//Get the taxtype of this entity
$taxtype = getInventoryTaxType($module, $focus->id);
$currencytype = getInventoryCurrencyInfo($module, $focus->id);
$output = '';
//Header Rows
$output .= '
<table width="100%" border="0" align="center" cellpadding="5" cellspacing="0" class="crmTable detailview_inventory_table" id="proTab">
<tr valign="top" class="detailview_inventory_header">
<td colspan="2" class="dvInnerHeader"><b>' . $app_strings['LBL_ITEM_DETAILS'] . '</b></td>
<td class="dvInnerHeader" align="center" colspan="2"><b>' . $app_strings['LBL_CURRENCY'] . ' : </b>' . getTranslatedCurrencyString($currencytype['currency_name']) . ' (' . $currencytype['currency_symbol'] . ')
</td>
<td class="dvInnerHeader" align="center" colspan="2"><b>' . $app_strings['LBL_TAX_MODE'] . ' : </b>' . $app_strings[$taxtype] . '
</td>
</tr>
<tr valign="top" class="detailview_inventory_subheader">
<td width=40% class="lvtCol"><font color="red">*</font>
<b>' . $app_strings['LBL_ITEM_NAME'] . '</b>
</td>';
//Additional information column
$output .= '<td width=20% class="lvtCol"><b>' . $app_strings['LBL_INFORMATION'] . '</b></td>';
$output .= '
<td width=10% class="lvtCol"><b>' . $app_strings['LBL_QTY'] . '</b></td>
<td width=10% class="lvtCol" align="right"><b>' . $app_strings['LBL_LIST_PRICE'] . '</b></td>
<td width=10% nowrap class="lvtCol" align="right"><b>' . $app_strings['LBL_TOTAL'] . '</b></td>
<td width=10% valign="top" class="lvtCol" align="right"><b>' . $app_strings['LBL_NET_PRICE'] . '</b></td>
</tr>';
if ($module == 'Quotes' || $module == 'PurchaseOrder' || $module == 'SalesOrder' || $module == 'Invoice') {
$query = "select case when vtiger_products.productid != '' then vtiger_products.productname else vtiger_service.servicename end as productname," . " case when vtiger_products.productid != '' then 'Products' else 'Services' end as entitytype," . " case when vtiger_products.productid != '' then vtiger_products.unit_price else vtiger_service.unit_price end as unit_price," . " case when vtiger_products.productid != '' then vtiger_products.qtyinstock else 'NA' end as qtyinstock, vtiger_inventoryproductrel.* " . " from vtiger_inventoryproductrel" . " left join vtiger_products on vtiger_products.productid=vtiger_inventoryproductrel.productid " . " left join vtiger_service on vtiger_service.serviceid=vtiger_inventoryproductrel.productid " . " where id=? ORDER BY sequence_no";
}
$result = $adb->pquery($query, array($focus->id));
$num_rows = $adb->num_rows($result);
$netTotal = '0.00';
for ($i = 1; $i <= $num_rows; $i++) {
$sub_prod_query = $adb->pquery("SELECT productid from vtiger_inventorysubproductrel WHERE id=? AND sequence_no=?", array($focus->id, $i));
$subprodname_str = '';
if ($adb->num_rows($sub_prod_query) > 0) {
for ($j = 0; $j < $adb->num_rows($sub_prod_query); $j++) {
$sprod_id = $adb->query_result($sub_prod_query, $j, 'productid');
$sprod_name = getProductName($sprod_id);
$str_sep = "";
if ($j > 0) {
$str_sep = ":";
}
$subprodname_str .= $str_sep . " - " . $sprod_name;
}
}
$subprodname_str = str_replace(":", "<br>", $subprodname_str);
$productid = $adb->query_result($result, $i - 1, 'productid');
$entitytype = $adb->query_result($result, $i - 1, 'entitytype');
$productname = $adb->query_result($result, $i - 1, 'productname');
$productname = '<a href="index.php?action=DetailView&record=' . $productid . '&module=' . $entitytype . '">' . $productname . '</a>';
if ($subprodname_str != '') {
$productname .= "<br/><span style='color:#C0C0C0;font-style:italic;'>" . $subprodname_str . "</span>";
}
$comment = $adb->query_result($result, $i - 1, 'comment');
$qtyinstock = $adb->query_result($result, $i - 1, 'qtyinstock');
$qty = $adb->query_result($result, $i - 1, 'quantity');
$qty = number_format($qty, 2, '.', '');
//Convert to 2 decimals
$unitprice = $adb->query_result($result, $i - 1, 'unit_price');
$listprice = $adb->query_result($result, $i - 1, 'listprice');
$total = $qty * $listprice;
$listprice = number_format($listprice, 2, '.', '');
//Convert to 2 decimals
//Product wise Discount calculation - starts
$discount_percent = $adb->query_result($result, $i - 1, 'discount_percent');
$discount_amount = $adb->query_result($result, $i - 1, 'discount_amount');
$totalAfterDiscount = $total;
$productDiscount = '0.00';
if ($discount_percent != 'NULL' && $discount_percent != '') {
$productDiscount = $total * $discount_percent / 100;
$productDiscount = number_format($productDiscount, 2, '.', '');
$totalAfterDiscount = $total - $productDiscount;
//if discount is percent then show the percentage
$discount_info_message = "{$discount_percent} % of " . CurrencyField::convertToUserFormat($total, null, true) . " = " . CurrencyField::convertToUserFormat($productDiscount, null, true);
} elseif ($discount_amount != 'NULL' && $discount_amount != '') {
$productDiscount = $discount_amount;
$productDiscount = number_format($productDiscount, 2, '.', '');
$totalAfterDiscount = $total - $productDiscount;
$discount_info_message = $app_strings['LBL_DIRECT_AMOUNT_DISCOUNT'] . " = " . CurrencyField::convertToUserFormat($productDiscount, null, true);
} else {
$discount_info_message = $app_strings['LBL_NO_DISCOUNT_FOR_THIS_LINE_ITEM'];
}
//Product wise Discount calculation - ends
$totalAfterDiscount = number_format($totalAfterDiscount, 2, '.', '');
//Convert to 2 decimals
$netprice = $totalAfterDiscount;
//Calculate the individual tax if taxtype is individual
if ($taxtype == 'individual') {
$taxtotal = '0.00';
$tax_info_message = $app_strings['LBL_TOTAL_AFTER_DISCOUNT'] . " = " . CurrencyField::convertToUserFormat($totalAfterDiscount, null, true) . " \\n";
$tax_details = getTaxDetailsForProduct($productid, 'all', $acvid);
for ($tax_count = 0; $tax_count < count($tax_details); $tax_count++) {
$tax_name = $tax_details[$tax_count]['taxname'];
$tax_label = $tax_details[$tax_count]['taxlabel'];
$tax_value = getInventoryProductTaxValue($focus->id, $productid, $tax_name);
$individual_taxamount = $totalAfterDiscount * $tax_value / 100;
$individual_taxamount = number_format($individual_taxamount, 2, '.', '');
//Convert to 2 decimals
$taxtotal = $taxtotal + $individual_taxamount;
$taxtotal = number_format($taxtotal, 2, '.', '');
//Convert to 2 decimals
$tax_info_message .= "{$tax_label} : {$tax_value} % = " . CurrencyField::convertToUserFormat($individual_taxamount, null, true) . " \\n";
}
$tax_info_message .= "\\n " . $app_strings['LBL_TOTAL_TAX_AMOUNT'] . " = " . CurrencyField::convertToUserFormat($taxtotal, null, true);
$netprice = $netprice + $taxtotal;
$netprice = number_format($netprice, 2, '.', '');
//Convert to 2 decimals
}
$sc_image_tag = '';
if ($module == 'Invoice') {
switch ($entitytype) {
case 'Services':
if (vtlib_isModuleActive('ServiceContracts')) {
$sc_image_tag = '<a href="index.php?module=ServiceContracts&action=EditView&service_id=' . $productid . '&sc_related_to=' . $focus->column_fields['account_id'] . '&start_date=' . DateTimeField::convertToUserFormat($focus->column_fields['invoicedate']) . '&return_module=' . $module . '&return_id=' . $focus->id . '">' . '<img border="0" src="' . vtiger_imageurl('handshake.gif', $theme) . '" title="' . getTranslatedString('LBL_ADD_NEW', $module) . " " . getTranslatedString('ServiceContracts', 'ServiceContracts') . '" style="cursor: pointer;" align="absmiddle" />' . '</a>';
}
break;
case 'Products':
if (vtlib_isModuleActive('Assets')) {
$sc_image_tag = '<a onclick="return window.open(\'index.php?module=Assets&return_module=Invoice&action=Popup&popuptype=detailview&select=enable&form=EditView&form_submit=false&return_action=DetailView&productid=' . $productid . '&invoiceid=' . $focus->id . '&return_id=' . $focus->id . '&recordid=' . $focus->id . '\',\'test\',\'width=640,height=602,resizable=0,scrollbars=0\')" onmouseout="vtlib_listview.trigger(\'invoiceasset.onmouseout\', this)" onmouseover="vtlib_listview.trigger(\'cell.onmouseover\', this)">' . '<img border="0" src="' . vtiger_imageurl('barcode.png', $theme) . '" title="' . getTranslatedString('LBL_ADD_NEW', $module) . " " . getTranslatedString('Assets', 'Assets') . '" style="cursor: pointer;" align="absmiddle" />' . '<span style="display:none;" vtmodule="Assets" vtfieldname="invoice_product" vtrecordid="' . $focus->id . '::' . $productid . '::' . $i . '" type="vtlib_metainfo"></span>' . '</a>';
}
break;
default:
$sc_image_tag = '';
}
}
//For Product Name
$output .= '
<tr valign="top" class="detailview_inventory_row">
<td class="crmTableRow small lineOnTop detailview_inventory_namecell">
' . $productname . ' ' . $sc_image_tag . '
<br>' . $comment . '
</td>';
//Upto this added to display the Product name and comment
$output .= '<td class="crmTableRow small lineOnTop detailview_inventory_stockcell">';
if ($module != 'PurchaseOrder' && $hide_stock == 'no') {
$output .= '<b>' . $app_strings['LBL_QTY_IN_STOCK'] . ':</b> ' . $qtyinstock;
}
if ($MDMapFound) {
foreach ($cbMapFields['detailview']['fields'] as $mdfield) {
$output .= '<br>';
$output .= '<b>' . $mdfield['fieldinfo']['label'] . '</b>: ';
$mdrs = $adb->pquery('select ' . $mdfield['fieldinfo']['name'] . ' from vtiger_inventorydetails
inner join vtiger_crmentity on crmid=vtiger_inventorydetails.inventorydetailsid
inner join vtiger_inventorydetailscf on vtiger_inventorydetailscf.inventorydetailsid=vtiger_inventorydetails.inventorydetailsid
where deleted=0 and related_to=? and lineitem_id=?', array($focus->id, $adb->query_result($result, $i - 1, 'lineitem_id')));
if ($mdrs) {
$col_fields = array();
$col_fields[$mdfield['fieldinfo']['name']] = $adb->query_result($mdrs, 0, 0);
$foutput = getDetailViewOutputHtml($mdfield['fieldinfo']['uitype'], $mdfield['fieldinfo']['name'], $mdfield['fieldinfo']['label'], $col_fields, 0, $tabid, $module);
$output .= $foutput[1];
}
}
}
$output .= '</td>';
$output .= '<td class="crmTableRow small lineOnTop detailview_inventory_qtycell">' . $qty . '</td>';
$output .= '
<td class="crmTableRow small lineOnTop detailview_inventory_lpricecell" align="right">
<table width="100%" border="0" cellpadding="5" cellspacing="0">
<tr>
<td align="right">' . CurrencyField::convertToUserFormat($listprice, null, true) . '</td>
</tr>
<tr>
<td align="right">(-) <b><a href="javascript:;" onclick="alert(\'' . $discount_info_message . '\'); ">' . $app_strings['LBL_DISCOUNT'] . ' : </a></b></td>
</tr>
<tr>
<td align="right" nowrap>' . $app_strings['LBL_TOTAL_AFTER_DISCOUNT'] . ' : </td>
</tr>';
if ($taxtype == 'individual') {
$output .= '
<tr>
<td align="right" nowrap>(+) <b><a href="javascript:;" onclick="alert(\'' . $tax_info_message . '\');">' . $app_strings['LBL_TAX'] . ' : </a></b></td>
</tr>';
}
$output .= '
</table>
</td>';
$output .= '
<td class="crmTableRow small lineOnTop detailview_inventory_totalscell" align="right">
<table width="100%" border="0" cellpadding="5" cellspacing="0">
<tr><td align="right">' . CurrencyField::convertToUserFormat($total, null, true) . '</td></tr>
<tr><td align="right">' . CurrencyField::convertToUserFormat($productDiscount, null, true) . '</td></tr>
<tr><td align="right" nowrap>' . CurrencyField::convertToUserFormat($totalAfterDiscount, null, true) . '</td></tr>';
if ($taxtype == 'individual') {
$output .= '<tr><td align="right" nowrap>' . CurrencyField::convertToUserFormat($taxtotal, null, true) . '</td></tr>';
}
$output .= '
</table>
</td>';
$output .= '<td class="crmTableRow small lineOnTop detailview_inventory_npricecell" valign="bottom" align="right">' . CurrencyField::convertToUserFormat($netprice, null, true) . '</td>';
$output .= '</tr>';
$netTotal = $netTotal + $netprice;
}
$output .= '</table>';
//$netTotal should be equal to $focus->column_fields['hdnSubTotal']
$netTotal = $focus->column_fields['hdnSubTotal'];
$netTotal = number_format($netTotal, 2, '.', '');
//Convert to 2 decimals
//Display the total, adjustment, S&H details
$output .= '<table width="100%" border="0" cellspacing="0" cellpadding="5" class="crmTable detailview_inventory_totals">';
$output .= '<tr id="detailview_inventory_subtotalrow">';
$output .= '<td width="88%" class="crmTableRow small" align="right"><b>' . $app_strings['LBL_NET_TOTAL'] . '</td>';
$output .= '<td width="12%" class="crmTableRow small" align="right"><b>' . CurrencyField::convertToUserFormat($netTotal, null, true) . '</b></td>';
$output .= '</tr>';
//Decide discount
$finalDiscount = '0.00';
$final_discount_info = '0';
//if($focus->column_fields['hdnDiscountPercent'] != '') - previously (before changing to prepared statement) the selected option (either percent or amount) will have value and the other remains empty. So we can find the non selected item by empty check. But now with prepared statement, the non selected option stored as 0
if ($focus->column_fields['hdnDiscountPercent'] != '0') {
$finalDiscount = $netTotal * $focus->column_fields['hdnDiscountPercent'] / 100;
$finalDiscount = number_format($finalDiscount, 2, '.', '');
$final_discount_info = $focus->column_fields['hdnDiscountPercent'] . " % of " . CurrencyField::convertToUserFormat($netTotal, null, true) . " = " . CurrencyField::convertToUserFormat($finalDiscount, null, true);
} elseif ($focus->column_fields['hdnDiscountAmount'] != '0') {
$finalDiscount = $focus->column_fields['hdnDiscountAmount'];
$finalDiscount = number_format($finalDiscount, 2, '.', '');
$final_discount_info = CurrencyField::convertToUserFormat($finalDiscount, null, true);
}
//Alert the Final Discount amount even it is zero
$final_discount_info = $app_strings['LBL_FINAL_DISCOUNT_AMOUNT'] . " = {$final_discount_info}";
$final_discount_info = 'onclick="alert(\'' . $final_discount_info . '\');"';
$output .= '<tr id="detailview_inventory_totaldiscrow">';
$output .= '<td align="right" class="crmTableRow small lineOnTop">(-) <b><a href="javascript:;" ' . $final_discount_info . '>' . $app_strings['LBL_DISCOUNT'] . '</a></b></td>';
$output .= '<td align="right" class="crmTableRow small lineOnTop">' . CurrencyField::convertToUserFormat($finalDiscount, null, true) . '</td>';
$output .= '</tr>';
if ($taxtype == 'group') {
$taxtotal = '0.00';
$final_totalAfterDiscount = $netTotal - $finalDiscount;
$tax_info_message = $app_strings['LBL_TOTAL_AFTER_DISCOUNT'] . " = " . CurrencyField::convertToUserFormat($final_totalAfterDiscount, null, true) . " \\n";
//First we should get all available taxes and then retrieve the corresponding tax values
$tax_details = getAllTaxes('available', '', 'edit', $focus->id);
$ipr_cols = $adb->getColumnNames('vtiger_inventoryproductrel');
//if taxtype is group then the tax should be same for all products in vtiger_inventoryproductrel table
for ($tax_count = 0; $tax_count < count($tax_details); $tax_count++) {
$tax_name = $tax_details[$tax_count]['taxname'];
$tax_label = $tax_details[$tax_count]['taxlabel'];
if (in_array($tax_name, $ipr_cols)) {
$tax_value = $adb->query_result($result, 0, $tax_name);
} else {
$tax_value = $tax_details[$tax_count]['percentage'];
}
if ($tax_value == '' || $tax_value == 'NULL') {
$tax_value = '0.00';
}
$taxamount = ($netTotal - $finalDiscount) * $tax_value / 100;
$taxtotal = $taxtotal + $taxamount;
$tax_info_message .= "{$tax_label} : {$tax_value} % = " . CurrencyField::convertToUserFormat($taxamount, null, true) . " \\n";
}
$tax_info_message .= "\\n " . $app_strings['LBL_TOTAL_TAX_AMOUNT'] . " = " . CurrencyField::convertToUserFormat($taxtotal, null, true);
$output .= '<tr id="detailview_inventory_taxtotalrow">';
$output .= '<td align="right" class="crmTableRow small">(+) <b><a href="javascript:;" onclick="alert(\'' . $tax_info_message . '\');">' . $app_strings['LBL_TAX'] . '</a></b></td>';
$output .= '<td align="right" class="crmTableRow small">' . CurrencyField::convertToUserFormat($taxtotal, null, true) . '</td>';
$output .= '</tr>';
}
$shAmount = $focus->column_fields['hdnS_H_Amount'] != '' ? $focus->column_fields['hdnS_H_Amount'] : '0.00';
$shAmount = number_format($shAmount, 2, '.', '');
//Convert to 2 decimals
$output .= '<tr id="detailview_inventory_shippingrow">';
$output .= '<td align="right" class="crmTableRow small">(+) <b>' . $app_strings['LBL_SHIPPING_AND_HANDLING_CHARGES'] . '</b></td>';
$output .= '<td align="right" class="crmTableRow small">' . CurrencyField::convertToUserFormat($shAmount, null, true) . '</td>';
$output .= '</tr>';
//calculate S&H tax
$shtaxtotal = '0.00';
//First we should get all available taxes and then retrieve the corresponding tax values
$shtax_details = getAllTaxes('available', 'sh', 'edit', $focus->id);
//if taxtype is group then the tax should be same for all products in vtiger_inventoryproductrel table
$shtax_info_message = $app_strings['LBL_SHIPPING_AND_HANDLING_CHARGE'] . " = " . CurrencyField::convertToUserFormat($shAmount, null, true) . "\\n";
for ($shtax_count = 0; $shtax_count < count($shtax_details); $shtax_count++) {
$shtax_name = $shtax_details[$shtax_count]['taxname'];
$shtax_label = $shtax_details[$shtax_count]['taxlabel'];
$shtax_percent = getInventorySHTaxPercent($focus->id, $shtax_name);
$shtaxamount = $shAmount * $shtax_percent / 100;
$shtaxamount = number_format($shtaxamount, 2, '.', '');
$shtaxtotal = $shtaxtotal + $shtaxamount;
$shtax_info_message .= "{$shtax_label} : {$shtax_percent} % = " . CurrencyField::convertToUserFormat($shtaxamount, null, true) . " \\n";
}
$shtax_info_message .= "\\n " . $app_strings['LBL_TOTAL_TAX_AMOUNT'] . " = " . CurrencyField::convertToUserFormat($shtaxtotal, null, true);
$output .= '<tr id="detailview_inventory_shiptaxrow">';
$output .= '<td align="right" class="crmTableRow small">(+) <b><a href="javascript:;" onclick="alert(\'' . $shtax_info_message . '\')">' . $app_strings['LBL_TAX_FOR_SHIPPING_AND_HANDLING'] . '</a></b></td>';
$output .= '<td align="right" class="crmTableRow small">' . CurrencyField::convertToUserFormat($shtaxtotal, null, true) . '</td>';
$output .= '</tr>';
$adjustment = $focus->column_fields['txtAdjustment'] != '' ? $focus->column_fields['txtAdjustment'] : '0.00';
$adjustment = number_format($adjustment, 2, '.', '');
//Convert to 2 decimals
$output .= '<tr id="detailview_inventory_adjustrow">';
$output .= '<td align="right" class="crmTableRow small"> <b>' . $app_strings['LBL_ADJUSTMENT'] . '</b></td>';
$output .= '<td align="right" class="crmTableRow small">' . CurrencyField::convertToUserFormat($adjustment, null, true) . '</td>';
$output .= '</tr>';
$grandTotal = $focus->column_fields['hdnGrandTotal'] != '' ? $focus->column_fields['hdnGrandTotal'] : '0.00';
$grandTotal = number_format($grandTotal, 2, '.', '');
//Convert to 2 decimals
$output .= '<tr id="detailview_inventory_grandtotrow">';
$output .= '<td align="right" class="crmTableRow small lineOnTop"><b>' . $app_strings['LBL_GRAND_TOTAL'] . '</b></td>';
$output .= '<td align="right" class="crmTableRow small lineOnTop">' . CurrencyField::convertToUserFormat($grandTotal, null, true) . '</td>';
$output .= '</tr>';
$output .= '</table>';
$log->debug("Exiting getDetailAssociatedProducts method ...");
return $output;
}
