/** Function used to save the Inventory product details for the passed entity
* @param object reference $focus - object reference to which we want to save the product details from REQUEST values where as the entity will be Purchase Order, Sales Order, Quotes or Invoice
* @param string $module - module name
* @param $update_prod_stock - true or false (default), if true we have to update the stock for PO only
* @return void
*/
function saveInventoryProductDetails(&$focus, $module, $update_prod_stock = 'false', $updateDemand = '')
{
global $log, $adb;
$id = $focus->id;
$log->debug("Entering into function saveInventoryProductDetails({$module}).");
//Added to get the convertid
if (isset($_REQUEST['convert_from']) && $_REQUEST['convert_from'] != '') {
$id = vtlib_purify($_REQUEST['return_id']);
} else {
if (isset($_REQUEST['duplicate_from']) && $_REQUEST['duplicate_from'] != '') {
$id = vtlib_purify($_REQUEST['duplicate_from']);
}
}
$ipr_cols = $adb->getColumnNames('vtiger_inventoryproductrel');
$ext_prod_arr = array();
if ($focus->mode == 'edit') {
if ($_REQUEST['taxtype'] == 'group') {
$all_available_taxes = getAllTaxes('available', '', 'edit', $id);
}
$return_old_values = '';
if ($module != 'PurchaseOrder') {
$return_old_values = 'return_old_values';
}
//we will retrieve the existing product details and store it in a array and then delete all the existing product details and save new values, retrieve the old value and update stock only for SO, Quotes and Invoice not for PO
//$ext_prod_arr = deleteInventoryProductDetails($focus->id,$return_old_values);
deleteInventoryProductDetails($focus);
} else {
if ($_REQUEST['taxtype'] == 'group') {
$all_available_taxes = getAllTaxes('available', '', 'edit', $id);
}
}
$tot_no_prod = $_REQUEST['totalProductCount'];
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'];
}
//If the taxtype is group then retrieve all available taxes, else retrive associated taxes for each product inside loop
$prod_seq = 1;
for ($i = 1; $i <= $tot_no_prod; $i++) {
//if the product is deleted then we should avoid saving the deleted products
if ($_REQUEST["deleted" . $i] == 1) {
continue;
}
$prod_id = vtlib_purify($_REQUEST['hdnProductId' . $i]);
if (isset($_REQUEST['productDescription' . $i])) {
$description = vtlib_purify($_REQUEST['productDescription' . $i]);
}
/*else{
$desc_duery = "select vtiger_crmentity.description AS product_description from vtiger_crmentity where vtiger_crmentity.crmid=?";
$desc_res = $adb->pquery($desc_duery,array($prod_id));
$description = $adb->query_result($desc_res,0,"product_description");
} */
$qty = vtlib_purify($_REQUEST['qty' . $i]);
$listprice = vtlib_purify($_REQUEST['listPrice' . $i]);
$comment = vtlib_purify($_REQUEST['comment' . $i]);
//we have to update the Product stock for PurchaseOrder if $update_prod_stock is true
if ($module == 'PurchaseOrder' && $update_prod_stock == 'true') {
addToProductStock($prod_id, $qty);
}
if ($module == 'SalesOrder') {
if ($updateDemand == '-') {
deductFromProductDemand($prod_id, $qty);
} elseif ($updateDemand == '+') {
addToProductDemand($prod_id, $qty);
}
}
$query = "insert into vtiger_inventoryproductrel(id, productid, sequence_no, quantity, listprice, comment, description) values(?,?,?,?,?,?,?)";
$qparams = array($focus->id, $prod_id, $prod_seq, $qty, $listprice, $comment, $description);
$adb->pquery($query, $qparams);
$lineitem_id = $adb->getLastInsertID();
$sub_prod_str = $_REQUEST['subproduct_ids' . $i];
if (!empty($sub_prod_str)) {
$sub_prod = explode(":", $sub_prod_str);
for ($j = 0; $j < count($sub_prod); $j++) {
$query = "insert into vtiger_inventorysubproductrel(id, sequence_no, productid) values(?,?,?)";
$qparams = array($focus->id, $prod_seq, $sub_prod[$j]);
$adb->pquery($query, $qparams);
}
}
$prod_seq++;
if ($module != 'PurchaseOrder') {
//update the stock with existing details
updateStk($prod_id, $qty, $focus->mode, $ext_prod_arr, $module);
}
//we should update discount and tax details
$updatequery = "update vtiger_inventoryproductrel set ";
$updateparams = array();
//set the discount percentage or discount amount in update query, then set the tax values
if ($_REQUEST['discount_type' . $i] == 'percentage') {
$updatequery .= " discount_percent=?,";
array_push($updateparams, $_REQUEST['discount_percentage' . $i]);
} elseif ($_REQUEST['discount_type' . $i] == 'amount') {
$updatequery .= " discount_amount=?,";
$discount_amount = $_REQUEST['discount_amount' . $i];
array_push($updateparams, $discount_amount);
}
if ($_REQUEST['taxtype'] == 'group') {
for ($tax_count = 0; $tax_count < count($all_available_taxes); $tax_count++) {
$tax_name = $all_available_taxes[$tax_count]['taxname'];
if (!in_array($tax_name, $ipr_cols)) {
continue;
}
$tax_val = $all_available_taxes[$tax_count]['percentage'];
$request_tax_name = $tax_name . "_group_percentage";
if (isset($_REQUEST[$request_tax_name])) {
$tax_val = vtlib_purify($_REQUEST[$request_tax_name]);
}
$updatequery .= " {$tax_name} = ?,";
array_push($updateparams, $tax_val);
}
$updatequery = trim($updatequery, ',') . " where id=? and productid=? and lineitem_id = ?";
array_push($updateparams, $focus->id, $prod_id, $lineitem_id);
} else {
$taxes_for_product = getTaxDetailsForProduct($prod_id, 'all', $acvid);
for ($tax_count = 0; $tax_count < count($taxes_for_product); $tax_count++) {
$tax_name = $taxes_for_product[$tax_count]['taxname'];
if (!in_array($tax_name, $ipr_cols)) {
continue;
}
$request_tax_name = $tax_name . "_percentage" . $i;
$updatequery .= " {$tax_name} = ?,";
array_push($updateparams, vtlib_purify($_REQUEST[$request_tax_name]));
}
$updatequery = trim($updatequery, ',') . " where id=? and productid=? and lineitem_id = ?";
array_push($updateparams, $focus->id, $prod_id, $lineitem_id);
}
// jens 2006/08/19 - protect against empy update queries
if (!preg_match('/set\\s+where/i', $updatequery)) {
$adb->pquery($updatequery, $updateparams);
}
}
//we should update the netprice (subtotal), taxtype, group discount, S&H charge, S&H taxes, adjustment and total
//netprice, group discount, taxtype, S&H amount, adjustment and total to entity table
$updatequery = " update {$focus->table_name} set ";
$updateparams = array();
$subtotal = $_REQUEST['subtotal'];
$updatequery .= " subtotal=?,";
array_push($updateparams, $subtotal);
$updatequery .= " taxtype=?,";
array_push($updateparams, $_REQUEST['taxtype']);
//for discount percentage or discount amount
if ($_REQUEST['discount_type_final'] == 'percentage') {
$updatequery .= " discount_percent=?,";
array_push($updateparams, vtlib_purify($_REQUEST['discount_percentage_final']));
} elseif ($_REQUEST['discount_type_final'] == 'amount') {
$discount_amount_final = vtlib_purify($_REQUEST['discount_amount_final']);
$updatequery .= " discount_amount=?,";
array_push($updateparams, $discount_amount_final);
}
$shipping_handling_charge = vtlib_purify($_REQUEST['shipping_handling_charge']);
$updatequery .= " s_h_amount=?,";
array_push($updateparams, $shipping_handling_charge);
//if the user gave - sign in adjustment then add with the value
$adjustmentType = '';
if ($_REQUEST['adjustmentType'] == '-') {
$adjustmentType = vtlib_purify($_REQUEST['adjustmentType']);
}
$adjustment = vtlib_purify($_REQUEST['adjustment']);
$updatequery .= " adjustment=?,";
array_push($updateparams, $adjustmentType . $adjustment);
$total = vtlib_purify($_REQUEST['total']);
$updatequery .= " total=?";
array_push($updateparams, $total);
//$id_array = Array('PurchaseOrder'=>'purchaseorderid','SalesOrder'=>'salesorderid','Quotes'=>'quoteid','Invoice'=>'invoiceid');
//Added where condition to which entity we want to update these values
$updatequery .= " where " . $focus->table_index . "=?";
array_push($updateparams, $focus->id);
$adb->pquery($updatequery, $updateparams);
//to save the S&H tax details in vtiger_inventoryshippingrel table
$isr_cols = $adb->getColumnNames('vtiger_inventoryshippingrel');
$sh_tax_details = getAllTaxes('all', 'sh');
$sh_query_fields = "id,";
$sh_query_values = "?,";
$sh_query_params = array($focus->id);
for ($i = 0; $i < count($sh_tax_details); $i++) {
$tax_name = $sh_tax_details[$i]['taxname'] . "_sh_percent";
if ($_REQUEST[$tax_name] != '' and in_array($sh_tax_details[$i]['taxname'], $isr_cols)) {
$sh_query_fields .= $sh_tax_details[$i]['taxname'] . ",";
$sh_query_values .= "?,";
array_push($sh_query_params, vtlib_purify($_REQUEST[$tax_name]));
}
}
$sh_query_fields = trim($sh_query_fields, ',');
$sh_query_values = trim($sh_query_values, ',');
if ($sh_query_fields != 'id') {
$sh_query = "insert into vtiger_inventoryshippingrel({$sh_query_fields}) values({$sh_query_values})";
$adb->pquery($sh_query, $sh_query_params);
}
$log->debug("Exit from function saveInventoryProductDetails({$module}).");
}
/** Function used to save the Inventory product details for the passed entity
* @param object reference $focus - object reference to which we want to save the product details from REQUEST values where as the entity will be Purchase Order, Sales Order, Quotes or Invoice
* @param string $module - module name
* @param $update_prod_stock - true or false (default), if true we have to update the stock for PO only
* @return void
*/
function saveInventoryProductDetails(&$focus, $module, $update_prod_stock = 'false', $updateDemand = '')
{
$adb = PearDatabase::getInstance();
$log = vglobal('log');
$id = $focus->id;
$log->debug("Entering into function saveInventoryProductDetails({$module}).");
//Added to get the convertid
if (isset($_REQUEST['convert_from']) && $_REQUEST['convert_from'] != '') {
$id = vtlib_purify($_REQUEST['return_id']);
} else {
if (isset($_REQUEST['duplicate_from']) && $_REQUEST['duplicate_from'] != '') {
$id = vtlib_purify($_REQUEST['duplicate_from']);
}
}
$ext_prod_arr = array();
if ($focus->mode == 'edit') {
if ($_REQUEST['taxtype'] == 'group') {
$all_available_taxes = getAllTaxes('available', '', 'edit', $id);
}
$return_old_values = '';
if ($module != 'PurchaseOrder') {
$return_old_values = 'return_old_values';
}
//we will retrieve the existing product details and store it in a array and then delete all the existing product details and save new values, retrieve the old value and update stock only for SO, Quotes and Invoice not for PO
//$ext_prod_arr = deleteInventoryProductDetails($focus->id,$return_old_values);
deleteInventoryProductDetails($focus);
} else {
if ($_REQUEST['taxtype'] == 'group') {
$all_available_taxes = getAllTaxes('available', '', 'edit', $id);
}
}
$tot_no_prod = $_REQUEST['totalProductCount'];
//If the taxtype is group then retrieve all available taxes, else retrive associated taxes for each product inside loop
$prod_seq = 1;
$total_purchase = 0;
$total_margin = 0;
for ($i = 1; $i <= $tot_no_prod; $i++) {
//if the product is deleted then we should avoid saving the deleted products
if ($_REQUEST["deleted" . $i] == 1) {
continue;
}
$prod_id = vtlib_purify($_REQUEST['hdnProductId' . $i]);
if (isset($_REQUEST['productDescription' . $i])) {
$description = vtlib_purify($_REQUEST['productDescription' . $i]);
}
/* else{
$desc_duery = "select vtiger_crmentity.description AS product_description from vtiger_crmentity where vtiger_crmentity.crmid=?";
$desc_res = $adb->pquery($desc_duery,array($prod_id));
$description = $adb->query_result($desc_res,0,"product_description");
} */
$qty = vtlib_purify($_REQUEST['qty' . $i]);
$listprice = vtlib_purify($_REQUEST['listPrice' . $i]);
$comment = vtlib_purify($_REQUEST['comment' . $i]);
$purchaseCost = vtlib_purify($_REQUEST['purchaseCost' . $i]);
$calculationsid = vtlib_purify($_REQUEST['calculationId' . $i]);
$purchase = vtlib_purify($_REQUEST['purchase' . $i]);
$margin = vtlib_purify($_REQUEST['margin' . $i]);
$marginp = vtlib_purify($_REQUEST['marginp' . $i]);
$total_purchase += $purchase * $qty;
$total_margin += $margin;
//we have to update the Product stock for PurchaseOrder if $update_prod_stock is true
if ($module == 'PurchaseOrder' && $update_prod_stock == 'true') {
addToProductStock($prod_id, $qty);
}
if ($module == 'SalesOrder') {
if ($updateDemand == '-') {
deductFromProductDemand($prod_id, $qty);
} elseif ($updateDemand == '+') {
addToProductDemand($prod_id, $qty);
}
}
$query = "insert into vtiger_inventoryproductrel(id, productid, sequence_no, quantity, listprice, comment, description, calculationsid, purchase, margin, marginp) values(?,?,?,?,?,?,?,?,?,?,?)";
$qparams = array($focus->id, $prod_id, $prod_seq, $qty, $listprice, $comment, $description, $calculationsid, $purchase, $margin, $marginp);
$adb->pquery($query, $qparams);
$lineitem_id = $adb->getLastInsertID();
$sub_prod_str = $_REQUEST['subproduct_ids' . $i];
if (!empty($sub_prod_str)) {
$sub_prod = explode(":", $sub_prod_str);
for ($j = 0; $j < count($sub_prod); $j++) {
$query = "insert into vtiger_inventorysubproductrel(id, sequence_no, productid) values(?,?,?)";
$qparams = array($focus->id, $prod_seq, $sub_prod[$j]);
$adb->pquery($query, $qparams);
}
}
$prod_seq++;
if ($module != 'PurchaseOrder') {
//update the stock with existing details
updateStk($prod_id, $qty, $focus->mode, $ext_prod_arr, $module);
}
//we should update discount and tax details
$updatequery = "update vtiger_inventoryproductrel set ";
$updateparams = array();
//set the discount percentage or discount amount in update query, then set the tax values
if ($_REQUEST['discount_type' . $i] == 'percentage') {
$updatequery .= " discount_percent=?,";
array_push($updateparams, $_REQUEST['discount_percentage' . $i]);
} elseif ($_REQUEST['discount_type' . $i] == 'amount') {
$updatequery .= " discount_amount=?,";
$discount_amount = $_REQUEST['discount_amount' . $i];
array_push($updateparams, $discount_amount);
}
if ($_REQUEST['taxtype'] == 'group') {
for ($tax_count = 0; $tax_count < count($all_available_taxes); $tax_count++) {
$selected_tax = $_REQUEST['group_tax_option'];
$updatequery .= " tax = ?,";
array_push($updateparams, $selected_tax);
$tax_name = $all_available_taxes[$tax_count]['taxname'];
$request_tax_name = $tax_name . "_group_percentage";
if (isset($_REQUEST[$request_tax_name])) {
$tax_val = vtlib_purify($_REQUEST[$request_tax_name]);
}
$updatequery .= " {$tax_name} = ?,";
array_push($updateparams, $tax_val);
}
} else {
$taxes_for_product = getTaxDetailsForProduct($prod_id, 'all');
$selected_tax = $_REQUEST['tax_option' . $i];
$updatequery .= " tax = ?,";
array_push($updateparams, $selected_tax);
for ($tax_count = 0; $tax_count < count($taxes_for_product); $tax_count++) {
$tax_name = $taxes_for_product[$tax_count]['taxname'];
$request_tax_name = $tax_name . "_percentage" . $i;
$updatequery .= " {$tax_name} = ?,";
array_push($updateparams, vtlib_purify($_REQUEST[$request_tax_name]));
}
}
$updatequery = trim($updatequery, ',') . " where id=? and productid=? and lineitem_id = ?";
array_push($updateparams, $focus->id, $prod_id, $lineitem_id);
// jens 2006/08/19 - protect against empy update queries
if (!preg_match('/set\\s+where/i', $updatequery)) {
$adb->pquery($updatequery, $updateparams);
}
}
//we should update the netprice (subtotal), taxtype, group discount, S&H charge, S&H taxes total
//netprice, group discount, taxtype, total to entity table
$updatequery = " update {$focus->table_name} set ";
$updateparams = array();
$subtotal = $_REQUEST['subtotal'];
$updatequery .= " subtotal=?,";
array_push($updateparams, $subtotal);
$updatequery .= " total_purchase=?,";
array_push($updateparams, $total_purchase);
$updatequery .= " total_margin=?,";
array_push($updateparams, $total_margin);
$updatequery .= " total_marginp=?,";
$total_marginp = 0;
if ($total_purchase != 0) {
$total_marginp = $total_margin / $total_purchase * 100;
}
array_push($updateparams, $total_marginp);
$updatequery .= " taxtype=?,";
array_push($updateparams, $_REQUEST['taxtype']);
//for discount percentage or discount amount
if ($_REQUEST['discount_type_final'] == 'percentage') {
$updatequery .= " discount_percent=?,discount_amount=?,";
array_push($updateparams, vtlib_purify($_REQUEST['discount_percentage_final']));
array_push($updateparams, null);
} elseif ($_REQUEST['discount_type_final'] == 'amount') {
$discount_amount_final = vtlib_purify($_REQUEST['discount_amount_final']);
$updatequery .= " discount_amount=?,discount_percent=?,";
array_push($updateparams, $discount_amount_final);
array_push($updateparams, null);
} elseif ($_REQUEST['discount_type_final'] == 'zero') {
$updatequery .= "discount_amount=?,discount_percent=?,";
array_push($updateparams, null);
array_push($updateparams, null);
}
$total = vtlib_purify($_REQUEST['total']);
$updatequery .= " total=?";
array_push($updateparams, $total);
//$id_array = Array('PurchaseOrder'=>'purchaseorderid','SalesOrder'=>'salesorderid','Quotes'=>'quoteid','Invoice'=>'invoiceid');
//Added where condition to which entity we want to update these values
$updatequery .= " where " . $focus->table_index . "=?";
array_push($updateparams, $focus->id);
$adb->pquery($updatequery, $updateparams);
$log->debug("Exit from function saveInventoryProductDetails({$module}).");
}