/** 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}).");
}
function save_module($module)
{
global $adb;
//in ajax save we should not call this function, because this will delete all the existing product values
if ($_REQUEST['action'] != 'PurchaseOrderAjax' && $_REQUEST['ajxaction'] != 'DETAILVIEW' && $_REQUEST['action'] != 'MassEditSave' && $_REQUEST['action'] != 'ProcessDuplicates') {
//Based on the total Number of rows we will save the product relationship with this entity
saveInventoryProductDetails($this, 'PurchaseOrder', $this->update_prod_stock);
}
//In Ajax edit, if the status changed to Received Shipment then we have to update the product stock
if ($_REQUEST['action'] == 'PurchaseOrderAjax' && $this->update_prod_stock == 'true') {
$inventory_res = $this->db->pquery("select productid, quantity from vtiger_inventoryproductrel where id=?", array($this->id));
$noofproducts = $this->db->num_rows($inventory_res);
//We have to update the stock for all the products in this PO
for ($prod_count = 0; $prod_count < $noofproducts; $prod_count++) {
$productid = $this->db->query_result($inventory_res, $prod_count, 'productid');
$quantity = $this->db->query_result($inventory_res, $prod_count, 'quantity');
$this->db->println("Stock is going to be updated for the productid - {$productid} with quantity - {$quantity}");
addToProductStock($productid, $quantity);
}
}
// Update the currency id and the conversion rate for the purchase order
$update_query = "update vtiger_purchaseorder set currency_id=?, conversion_rate=? where purchaseorderid=?";
$update_params = array($this->column_fields['currency_id'], $this->column_fields['conversion_rate'], $this->id);
$adb->pquery($update_query, $update_params);
}
function save_module($module)
{
global $adb, $updateInventoryProductRel_deduct_stock;
$updateInventoryProductRel_deduct_stock = false;
//in ajax save we should not call this function, because this will delete all the existing product values
if ($_REQUEST['action'] != 'PurchaseOrderAjax' && $_REQUEST['ajxaction'] != 'DETAILVIEW' && $_REQUEST['action'] != 'MassEditSave' && $_REQUEST['action'] != 'ProcessDuplicates' && $_REQUEST['action'] != 'SaveAjax' && $this->isLineItemUpdate != false && $_REQUEST['action'] != 'FROM_WS') {
$requestProductIdsList = $requestQuantitiesList = array();
$totalNoOfProducts = $_REQUEST['totalProductCount'];
for ($i = 1; $i <= $totalNoOfProducts; $i++) {
$productId = $_REQUEST['hdnProductId' . $i];
$requestProductIdsList[$productId] = $productId;
if (array_key_exists($productId, $requestQuantitiesList)) {
$requestQuantitiesList[$productId] = $requestQuantitiesList[$productId] + $_REQUEST['qty' . $i];
continue;
}
$requestQuantitiesList[$productId] = $_REQUEST['qty' . $i];
}
if ($this->mode == '' && $this->column_fields['postatus'] === 'Received Shipment') {
//Updating Product stock quantity during create mode
foreach ($requestProductIdsList as $productId) {
addToProductStock($productId, $requestQuantitiesList[$productId]);
}
} else {
if ($this->column_fields['postatus'] === 'Received Shipment' && $this->mode != '') {
//Updating Product stock quantity during edit mode
$recordId = $this->id;
$result = $adb->pquery("SELECT productid, quantity FROM vtiger_inventoryproductrel WHERE id = ?", array($recordId));
$numOfRows = $adb->num_rows($result);
for ($i = 0; $i < $numOfRows; $i++) {
$productId = $adb->query_result($result, $i, 'productid');
$productIdsList[$productId] = $productId;
$quantitiesList[$productId] = $adb->query_result($result, $i, 'quantity');
}
$newProductIds = array_diff($requestProductIdsList, $productIdsList);
if ($newProductIds) {
foreach ($newProductIds as $productId) {
addToProductStock($productId, $requestQuantitiesList[$productId]);
}
}
$deletedProductIds = array_diff($productIdsList, $requestProductIdsList);
if ($deletedProductIds) {
foreach ($deletedProductIds as $productId) {
$productStock = getPrdQtyInStck($productId);
$quantity = $productStock - $quantitiesList[$productId];
updateProductQty($productId, $quantity);
}
}
$updatedProductIds = array_intersect($productIdsList, $requestProductIdsList);
if ($updatedProductIds) {
foreach ($updatedProductIds as $productId) {
$quantityDiff = $quantitiesList[$productId] - $requestQuantitiesList[$productId];
if ($quantityDiff < 0) {
$quantityDiff = -$quantityDiff;
addToProductStock($productId, $quantityDiff);
} elseif ($quantityDiff > 0) {
$productStock = getPrdQtyInStck($productId);
$quantity = $productStock - $quantityDiff;
updateProductQty($productId, $quantity);
}
}
}
}
}
//Based on the total Number of rows we will save the product relationship with this entity
saveInventoryProductDetails($this, 'PurchaseOrder', $this->update_prod_stock);
if ($this->mode != '') {
$updateInventoryProductRel_deduct_stock = true;
}
}
// Update the currency id and the conversion rate for the purchase order
$update_query = "update vtiger_purchaseorder set currency_id=?, conversion_rate=? where purchaseorderid=?";
$update_params = array($this->column_fields['currency_id'], $this->column_fields['conversion_rate'], $this->id);
$adb->pquery($update_query, $update_params);
}
