/** 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); }