| [ Index ] |
PHP Cross Reference of vtigercrm-6.1.0 |
[Summary view] [Print] [Text view]
1 <?php 2 /********************************************************************************* 3 ** The contents of this file are subject to the vtiger CRM Public License Version 1.0 4 * ("License"); You may not use this file except in compliance with the License 5 * The Original Code is: vtiger CRM Open Source 6 * The Initial Developer of the Original Code is vtiger. 7 * Portions created by vtiger are Copyright (C) vtiger. 8 * All Rights Reserved. 9 * 10 ********************************************************************************/ 11 12 /** 13 * This function updates the stock information once the product is ordered. 14 * Param $productid - product id 15 * Param $qty - product quantity in no's 16 * Param $mode - mode type 17 * Param $ext_prod_arr - existing vtiger_products 18 * Param $module - module name 19 * return type void 20 */ 21 22 function updateStk($product_id,$qty,$mode,$ext_prod_arr,$module) 23 { 24 global $log; 25 $log->debug("Entering updateStk(".$product_id.",".$qty.",".$mode.",".$ext_prod_arr.",".$module.") method ..."); 26 global $adb; 27 global $current_user; 28 29 $log->debug("Inside updateStk function, module=".$module); 30 $log->debug("Product Id = $product_id & Qty = $qty"); 31 32 $prod_name = getProductName($product_id); 33 $qtyinstk= getPrdQtyInStck($product_id); 34 $log->debug("Prd Qty in Stock ".$qtyinstk); 35 36 $upd_qty = $qtyinstk-$qty; 37 sendPrdStckMail($product_id,$upd_qty,$prod_name,$qtyinstk,$qty,$module); 38 39 $log->debug("Exiting updateStk method ..."); 40 } 41 42 /** 43 * This function sends a mail to the handler whenever the product reaches the reorder level. 44 * Param $product_id - product id 45 * Param $upd_qty - updated product quantity in no's 46 * Param $prod_name - product name 47 * Param $qtyinstk - quantity in stock 48 * Param $qty - quantity 49 * Param $module - module name 50 * return type void 51 */ 52 53 function sendPrdStckMail($product_id,$upd_qty,$prod_name,$qtyinstk,$qty,$module) 54 { 55 global $log; 56 $log->debug("Entering sendPrdStckMail(".$product_id.",".$upd_qty.",".$prod_name.",".$qtyinstk.",".$qty.",".$module.") method ..."); 57 global $current_user; 58 global $adb; 59 $reorderlevel = getPrdReOrderLevel($product_id); 60 $log->debug("Inside sendPrdStckMail function, module=".$module); 61 $log->debug("Prd reorder level ".$reorderlevel); 62 if($upd_qty < $reorderlevel) 63 { 64 //send mail to the handler 65 $handler = getRecordOwnerId($product_id); 66 foreach($handler as $type=>$id){ 67 $handler=$id; 68 } 69 $handler_name = getOwnerName($handler); 70 if(vtws_isRecordOwnerUser($handler)) { 71 $to_address = getUserEmail($handler); 72 } else { 73 $to_address = implode(',', getDefaultAssigneeEmailIds($handler)); 74 } 75 76 //Get the email details from database; 77 if($module == 'SalesOrder') 78 { 79 $notification_table = 'SalesOrderNotification'; 80 $quan_name = '{SOQUANTITY}'; 81 } 82 if($module == 'Quotes') 83 { 84 $notification_table = 'QuoteNotification'; 85 $quan_name = '{QUOTEQUANTITY}'; 86 } 87 if($module == 'Invoice') 88 { 89 $notification_table = 'InvoiceNotification'; 90 } 91 $query = "select * from vtiger_inventorynotification where notificationname=?"; 92 $result = $adb->pquery($query, array($notification_table)); 93 94 $subject = $adb->query_result($result,0,'notificationsubject'); 95 $body = $adb->query_result($result,0,'notificationbody'); 96 $status = $adb->query_result($result,0,'status'); 97 98 if($status == 0 || $status == '') 99 return false; 100 101 $subject = str_replace('{PRODUCTNAME}',$prod_name,$subject); 102 $body = str_replace('{HANDLER}',$handler_name,$body); 103 $body = str_replace('{PRODUCTNAME}',$prod_name,$body); 104 if($module == 'Invoice') 105 { 106 $body = str_replace('{CURRENTSTOCK}',$upd_qty,$body); 107 $body = str_replace('{REORDERLEVELVALUE}',$reorderlevel,$body); 108 } 109 else 110 { 111 $body = str_replace('{CURRENTSTOCK}',$qtyinstk,$body); 112 $body = str_replace($quan_name,$qty,$body); 113 } 114 $body = str_replace('{CURRENTUSER}',$current_user->user_name,$body); 115 116 $mail_status = send_mail($module,$to_address,$current_user->user_name,$current_user->email1,decode_html($subject),nl2br(to_html($body))); 117 } 118 $log->debug("Exiting sendPrdStckMail method ..."); 119 } 120 121 /**This function is used to get the quantity in stock of a given product 122 *Param $product_id - product id 123 *Returns type numeric 124 */ 125 function getPrdQtyInStck($product_id) 126 { 127 global $log; 128 $log->debug("Entering getPrdQtyInStck(".$product_id.") method ..."); 129 global $adb; 130 $query1 = "SELECT qtyinstock FROM vtiger_products WHERE productid = ?"; 131 $result=$adb->pquery($query1, array($product_id)); 132 $qtyinstck= $adb->query_result($result,0,"qtyinstock"); 133 $log->debug("Exiting getPrdQtyInStck method ..."); 134 return $qtyinstck; 135 } 136 137 /**This function is used to get the reorder level of a product 138 *Param $product_id - product id 139 *Returns type numeric 140 */ 141 142 function getPrdReOrderLevel($product_id) 143 { 144 global $log; 145 $log->debug("Entering getPrdReOrderLevel(".$product_id.") method ..."); 146 global $adb; 147 $query1 = "SELECT reorderlevel FROM vtiger_products WHERE productid = ?"; 148 $result=$adb->pquery($query1, array($product_id)); 149 $reorderlevel= $adb->query_result($result,0,"reorderlevel"); 150 $log->debug("Exiting getPrdReOrderLevel method ..."); 151 return $reorderlevel; 152 } 153 154 /** function to get the taxid 155 * @param string $type - tax type (VAT or Sales or Service) 156 * return int $taxid - taxid corresponding to the Tax type from vtiger_inventorytaxinfo vtiger_table 157 */ 158 function getTaxId($type) 159 { 160 global $adb, $log; 161 $log->debug("Entering into getTaxId($type) function."); 162 163 $res = $adb->pquery("SELECT taxid FROM vtiger_inventorytaxinfo WHERE taxname=?", array($type)); 164 $taxid = $adb->query_result($res,0,'taxid'); 165 166 $log->debug("Exiting from getTaxId($type) function. return value=$taxid"); 167 return $taxid; 168 } 169 170 /** function to get the taxpercentage 171 * @param string $type - tax type (VAT or Sales or Service) 172 * return int $taxpercentage - taxpercentage corresponding to the Tax type from vtiger_inventorytaxinfo vtiger_table 173 */ 174 function getTaxPercentage($type) 175 { 176 global $adb, $log; 177 $log->debug("Entering into getTaxPercentage($type) function."); 178 179 $taxpercentage = ''; 180 181 $res = $adb->pquery("SELECT percentage FROM vtiger_inventorytaxinfo WHERE taxname = ?", array($type)); 182 $taxpercentage = $adb->query_result($res,0,'percentage'); 183 184 $log->debug("Exiting from getTaxPercentage($type) function. return value=$taxpercentage"); 185 return $taxpercentage; 186 } 187 188 /** function to get the product's taxpercentage 189 * @param string $type - tax type (VAT or Sales or Service) 190 * @param id $productid - productid to which we want the tax percentage 191 * @param id $default - if 'default' then first look for product's tax percentage and product's tax is empty then it will return the default configured tax percentage, else it will return the product's tax (not look for default value) 192 * return int $taxpercentage - taxpercentage corresponding to the Tax type from vtiger_inventorytaxinfo vtiger_table 193 */ 194 function getProductTaxPercentage($type,$productid,$default='') 195 { 196 global $adb, $log, $current_user; 197 $log->debug("Entering into getProductTaxPercentage($type,$productid) function."); 198 199 $taxpercentage = ''; 200 201 $res = $adb->pquery("SELECT taxpercentage 202 FROM vtiger_inventorytaxinfo 203 INNER JOIN vtiger_producttaxrel 204 ON vtiger_inventorytaxinfo.taxid = vtiger_producttaxrel.taxid 205 WHERE vtiger_producttaxrel.productid = ? 206 AND vtiger_inventorytaxinfo.taxname = ?", array($productid, $type)); 207 $taxpercentage = $adb->query_result($res,0,'taxpercentage'); 208 209 //This is to retrive the default configured value if the taxpercentage related to product is empty 210 if($taxpercentage == '' && $default == 'default') 211 $taxpercentage = getTaxPercentage($type); 212 213 214 $log->debug("Exiting from getProductTaxPercentage($productid,$type) function. return value=$taxpercentage"); 215 if($current_user->truncate_trailing_zeros == true) 216 return decimalFormat($taxpercentage); 217 else 218 return $taxpercentage; 219 } 220 221 /** Function used to add the history entry in the relevant tables for PO, SO, Quotes and Invoice modules 222 * @param string $module - current module name 223 * @param int $id - entity id 224 * @param string $relatedname - parent name of the entity ie, required field venor name for PO and account name for SO, Quotes and Invoice 225 * @param float $total - grand total value of the product details included tax 226 * @param string $history_fldval - history field value ie., quotestage for Quotes and status for PO, SO and Invoice 227 */ 228 function addInventoryHistory($module, $id, $relatedname, $total, $history_fldval) 229 { 230 global $log, $adb; 231 $log->debug("Entering into function addInventoryHistory($module, $id, $relatedname, $total, $history_fieldvalue)"); 232 233 $history_table_array = Array( 234 "PurchaseOrder"=>"vtiger_postatushistory", 235 "SalesOrder"=>"vtiger_sostatushistory", 236 "Quotes"=>"vtiger_quotestagehistory", 237 "Invoice"=>"vtiger_invoicestatushistory" 238 ); 239 240 $histid = $adb->getUniqueID($history_table_array[$module]); 241 $modifiedtime = $adb->formatDate(date('Y-m-d H:i:s'), true); 242 $query = "insert into $history_table_array[$module] values(?,?,?,?,?,?)"; 243 $qparams = array($histid,$id,$relatedname,$total,$history_fldval,$modifiedtime); 244 $adb->pquery($query, $qparams); 245 246 $log->debug("Exit from function addInventoryHistory"); 247 } 248 249 /** Function used to get the list of Tax types as a array 250 * @param string $available - available or empty where as default is all, if available then the taxes which are available now will be returned otherwise all taxes will be returned 251 * @param string $sh - sh or empty, if sh passed then the shipping and handling related taxes will be returned 252 * @param string $mode - edit or empty, if mode is edit, then it will return taxes including desabled. 253 * @param string $id - crmid or empty, getting crmid to get tax values.. 254 * return array $taxtypes - return all the tax types as a array 255 */ 256 function getAllTaxes($available='all', $sh='',$mode='',$id='') 257 { 258 global $adb, $log; 259 $log->debug("Entering into the function getAllTaxes($available,$sh,$mode,$id)"); 260 $taxtypes = Array(); 261 if($sh != '' && $sh == 'sh') { 262 $tablename = 'vtiger_shippingtaxinfo'; 263 $value_table='vtiger_inventoryshippingrel'; 264 if($mode == 'edit' && id != '') { 265 $sql = "SELECT * FROM $tablename WHERE deleted=0"; 266 $result = $adb->pquery($sql, array()); 267 $noofrows=$adb->num_rows($result); 268 for($i=0; $i<$noofrows; $i++) { 269 $taxtypes[$i]['taxid'] = $adb->query_result($result,$i,'taxid'); 270 $taxname = $adb->query_result($result,$i,'taxname'); 271 $taxtypes[$i]['taxname'] = $taxname; 272 $inventory_tax_val_result = $adb->pquery("SELECT $taxname FROM $value_table WHERE id=?",array($id)); 273 $taxtypes[$i]['percentage'] = $adb->query_result($inventory_tax_val_result, 0, $taxname);; 274 $taxtypes[$i]['taxlabel'] = $adb->query_result($result,$i,'taxlabel'); 275 $taxtypes[$i]['deleted'] = $adb->query_result($result,$i,'deleted'); 276 } 277 } else { 278 //This where condition is added to get all products or only availble products 279 if ($available != 'all' && $available == 'available') { 280 $where = " WHERE $tablename.deleted=0"; 281 } 282 $result = $adb->pquery("SELECT * FROM $tablename $where ORDER BY deleted", array()); 283 $noofrows = $adb->num_rows($result); 284 for ($i = 0; $i < $noofrows; $i++) { 285 $taxtypes[$i]['taxid'] = $adb->query_result($result, $i, 'taxid'); 286 $taxtypes[$i]['taxname'] = $adb->query_result($result, $i, 'taxname'); 287 $taxtypes[$i]['taxlabel'] = $adb->query_result($result, $i, 'taxlabel'); 288 $taxtypes[$i]['percentage'] = $adb->query_result($result, $i, 'percentage'); 289 $taxtypes[$i]['deleted'] = $adb->query_result($result, $i, 'deleted'); 290 } 291 } 292 } else { 293 $tablename = 'vtiger_inventorytaxinfo'; 294 $value_table='vtiger_inventoryproductrel'; 295 if($mode == 'edit' && $id != '' ) { 296 //Getting total no of taxes 297 $result_ids = array(); 298 $result = $adb->pquery("select taxname,taxid from $tablename", array()); 299 $noofrows = $adb->num_rows($result); 300 $inventory_tax_val_result = $adb->pquery("select * from $value_table where id=?", array($id)); 301 //Finding which taxes are associated with this (SO,PO,Invoice,Quotes) and getting its taxid. 302 for ($i = 0; $i < $noofrows; $i++) { 303 $taxname = $adb->query_result($result, $i, 'taxname'); 304 $taxid = $adb->query_result($result, $i, 'taxid'); 305 $tax_val = $adb->query_result($inventory_tax_val_result, 0, $taxname); 306 if ($tax_val != '') { 307 array_push($result_ids, $taxid); 308 } 309 } 310 //We are selecting taxes using that taxids. So It will get the tax even if the tax is disabled. 311 $where_ids = ''; 312 if (count($result_ids) > 0) { 313 $insert_str = str_repeat("?,", count($result_ids) - 1); 314 $insert_str .= "?"; 315 $where_ids = "taxid in ($insert_str) or"; 316 } 317 $res = $adb->pquery("select * from $tablename where $where_ids deleted=0 order by taxid",$result_ids); 318 } else { 319 //This where condition is added to get all products or only availble products 320 if ($available != 'all' && $available == 'available') { 321 $where = " where $tablename.deleted=0"; 322 } 323 $res = $adb->pquery("select * from $tablename $where order by deleted", array()); 324 } 325 326 $noofrows = $adb->num_rows($res); 327 for ($i = 0; $i < $noofrows; $i++) { 328 $taxtypes[$i]['taxid'] = $adb->query_result($res, $i, 'taxid'); 329 $taxtypes[$i]['taxname'] = $adb->query_result($res, $i, 'taxname'); 330 $taxtypes[$i]['taxlabel'] = $adb->query_result($res, $i, 'taxlabel'); 331 $taxtypes[$i]['percentage'] = $adb->query_result($res, $i, 'percentage'); 332 $taxtypes[$i]['deleted'] = $adb->query_result($res, $i, 'deleted'); 333 } 334 } 335 $log->debug("Exit from the function getAllTaxes($available,$sh,$mode,$id)"); 336 337 return $taxtypes; 338 } 339 340 341 /** Function used to get all the tax details which are associated to the given product 342 * @param int $productid - product id to which we want to get all the associated taxes 343 * @param string $available - available or empty or available_associated where as default is all, if available then the taxes which are available now will be returned, if all then all taxes will be returned otherwise if the value is available_associated then all the associated taxes even they are not available and all the available taxes will be retruned 344 * @return array $tax_details - tax details as a array with productid, taxid, taxname, percentage and deleted 345 */ 346 function getTaxDetailsForProduct($productid, $available='all') 347 { 348 global $log, $adb; 349 $log->debug("Entering into function getTaxDetailsForProduct($productid)"); 350 if($productid != '') 351 { 352 //where condition added to avoid to retrieve the non available taxes 353 $where = ''; 354 if($available != 'all' && $available == 'available') 355 { 356 $where = ' and vtiger_inventorytaxinfo.deleted=0'; 357 } 358 if($available != 'all' && $available == 'available_associated') 359 { 360 $query = "SELECT vtiger_producttaxrel.*, vtiger_inventorytaxinfo.* FROM vtiger_inventorytaxinfo left JOIN vtiger_producttaxrel ON vtiger_inventorytaxinfo.taxid = vtiger_producttaxrel.taxid WHERE vtiger_producttaxrel.productid = ? or vtiger_inventorytaxinfo.deleted=0 GROUP BY vtiger_inventorytaxinfo.taxid"; 361 } 362 else 363 { 364 $query = "SELECT vtiger_producttaxrel.*, vtiger_inventorytaxinfo.* FROM vtiger_inventorytaxinfo INNER JOIN vtiger_producttaxrel ON vtiger_inventorytaxinfo.taxid = vtiger_producttaxrel.taxid WHERE vtiger_producttaxrel.productid = ? $where"; 365 } 366 $params = array($productid); 367 368 //Postgres 8 fixes 369 if( $adb->dbType == "pgsql") 370 $query = fixPostgresQuery( $query, $log, 0); 371 372 $res = $adb->pquery($query, $params); 373 for($i=0;$i<$adb->num_rows($res);$i++) 374 { 375 $tax_details[$i]['productid'] = $adb->query_result($res,$i,'productid'); 376 $tax_details[$i]['taxid'] = $adb->query_result($res,$i,'taxid'); 377 $tax_details[$i]['taxname'] = $adb->query_result($res,$i,'taxname'); 378 $tax_details[$i]['taxlabel'] = $adb->query_result($res,$i,'taxlabel'); 379 $tax_details[$i]['percentage'] = $adb->query_result($res,$i,'taxpercentage'); 380 $tax_details[$i]['deleted'] = $adb->query_result($res,$i,'deleted'); 381 } 382 } 383 else 384 { 385 $log->debug("Product id is empty. we cannot retrieve the associated products."); 386 } 387 388 $log->debug("Exit from function getTaxDetailsForProduct($productid)"); 389 return $tax_details; 390 } 391 392 /** Function used to delete the Inventory product details for the passed entity 393 * @param int $objectid - entity id to which we want to delete the product details from REQUEST values where as the entity will be Purchase Order, Sales Order, Quotes or Invoice 394 * @param string $return_old_values - string which contains the string return_old_values or may be empty, if the string is return_old_values then before delete old values will be retrieved 395 * @return array $ext_prod_arr - if the second input parameter is 'return_old_values' then the array which contains the productid and quantity which will be retrieved before delete the product details will be returned otherwise return empty 396 */ 397 function deleteInventoryProductDetails($focus) 398 { 399 global $log, $adb,$updateInventoryProductRel_update_product_array; 400 $log->debug("Entering into function deleteInventoryProductDetails(".$focus->id.")."); 401 402 $product_info = $adb->pquery("SELECT productid, quantity, sequence_no, incrementondel from vtiger_inventoryproductrel WHERE id=?",array($focus->id)); 403 $numrows = $adb->num_rows($product_info); 404 for($index = 0;$index <$numrows;$index++){ 405 $productid = $adb->query_result($product_info,$index,'productid'); 406 $sequence_no = $adb->query_result($product_info,$index,'sequence_no'); 407 $qty = $adb->query_result($product_info,$index,'quantity'); 408 $incrementondel = $adb->query_result($product_info,$index,'incrementondel'); 409 410 if($incrementondel){ 411 $focus->update_product_array[$focus->id][$sequence_no][$productid]= $qty; 412 $sub_prod_query = $adb->pquery("SELECT productid from vtiger_inventorysubproductrel WHERE id=? AND sequence_no=?",array($focus->id,$sequence_no)); 413 if($adb->num_rows($sub_prod_query)>0){ 414 for($j=0;$j<$adb->num_rows($sub_prod_query);$j++){ 415 $sub_prod_id = $adb->query_result($sub_prod_query,$j,"productid"); 416 $focus->update_product_array[$focus->id][$sequence_no][$sub_prod_id]= $qty; 417 } 418 } 419 420 } 421 } 422 $updateInventoryProductRel_update_product_array = $focus->update_product_array; 423 $adb->pquery("delete from vtiger_inventoryproductrel where id=?", array($focus->id)); 424 $adb->pquery("delete from vtiger_inventorysubproductrel where id=?", array($focus->id)); 425 $adb->pquery("delete from vtiger_inventoryshippingrel where id=?", array($focus->id)); 426 427 $log->debug("Exit from function deleteInventoryProductDetails(".$focus->id.")"); 428 } 429 430 function updateInventoryProductRel($entity) { 431 global $log, $adb,$updateInventoryProductRel_update_product_array,$updateInventoryProductRel_deduct_stock; 432 $entity_id = vtws_getIdComponents($entity->getId()); 433 $entity_id = $entity_id[1]; 434 $update_product_array = $updateInventoryProductRel_update_product_array; 435 $log->debug("Entering into function updateInventoryProductRel(".$entity_id.")."); 436 437 if(!empty($update_product_array)) { 438 foreach($update_product_array as $id=>$seq) { 439 foreach($seq as $seq=>$product_info) { 440 foreach($product_info as $key=>$index) { 441 $updqtyinstk= getPrdQtyInStck($key); 442 $upd_qty = $updqtyinstk+$index; 443 updateProductQty($key, $upd_qty); 444 } 445 } 446 } 447 } 448 449 $moduleName = $entity->getModuleName(); 450 if ($moduleName === 'Invoice') { 451 $statusFieldName = 'invoicestatus'; 452 $statusFieldValue = 'Cancel'; 453 } elseif ($moduleName === 'PurchaseOrder') { 454 $statusFieldName = 'postatus'; 455 $statusFieldValue = 'Received Shipment'; 456 } 457 458 $statusChanged = false; 459 $vtEntityDelta = new VTEntityDelta (); 460 $oldEntity = $vtEntityDelta-> getOldValue($moduleName, $entity_id, $statusFieldName); 461 $recordDetails = $entity->getData(); 462 $statusChanged = $vtEntityDelta->hasChanged($moduleName, $entity_id, $statusFieldName); 463 if($statusChanged) { 464 if($recordDetails[$statusFieldName] == $statusFieldValue) { 465 $adb->pquery("UPDATE vtiger_inventoryproductrel SET incrementondel=0 WHERE id=?",array($entity_id)); 466 $updateInventoryProductRel_deduct_stock = false; 467 if(empty($update_product_array)) { 468 addProductsToStock($entity_id); 469 } 470 } elseif($oldEntity == $statusFieldValue) { 471 $updateInventoryProductRel_deduct_stock = false; 472 deductProductsFromStock($entity_id); 473 } 474 } elseif($recordDetails[$statusFieldName] == $statusFieldValue) { 475 $updateInventoryProductRel_deduct_stock = false; 476 } 477 478 if($updateInventoryProductRel_deduct_stock) { 479 $adb->pquery("UPDATE vtiger_inventoryproductrel SET incrementondel=1 WHERE id=?",array($entity_id)); 480 481 $product_info = $adb->pquery("SELECT productid,sequence_no, quantity from vtiger_inventoryproductrel WHERE id=?",array($entity_id)); 482 $numrows = $adb->num_rows($product_info); 483 for($index = 0;$index <$numrows;$index++) { 484 $productid = $adb->query_result($product_info,$index,'productid'); 485 $qty = $adb->query_result($product_info,$index,'quantity'); 486 $sequence_no = $adb->query_result($product_info,$index,'sequence_no'); 487 $qtyinstk= getPrdQtyInStck($productid); 488 $upd_qty = $qtyinstk-$qty; 489 updateProductQty($productid, $upd_qty); 490 $sub_prod_query = $adb->pquery("SELECT productid from vtiger_inventorysubproductrel WHERE id=? AND sequence_no=?",array($entity_id,$sequence_no)); 491 if($adb->num_rows($sub_prod_query)>0) { 492 for($j=0;$j<$adb->num_rows($sub_prod_query);$j++) { 493 $sub_prod_id = $adb->query_result($sub_prod_query,$j,"productid"); 494 $sqtyinstk= getPrdQtyInStck($sub_prod_id); 495 $supd_qty = $sqtyinstk-$qty; 496 updateProductQty($sub_prod_id, $supd_qty); 497 } 498 } 499 } 500 501 $log->debug("Exit from function updateInventoryProductRel(".$entity_id.")"); 502 } 503 } 504 505 /** Function used to save the Inventory product details for the passed entity 506 * @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 507 * @param string $module - module name 508 * @param $update_prod_stock - true or false (default), if true we have to update the stock for PO only 509 * @return void 510 */ 511 function saveInventoryProductDetails(&$focus, $module, $update_prod_stock='false', $updateDemand='') 512 { 513 global $log, $adb; 514 $id=$focus->id; 515 $log->debug("Entering into function saveInventoryProductDetails($module)."); 516 //Added to get the convertid 517 if(isset($_REQUEST['convert_from']) && $_REQUEST['convert_from'] !='') 518 { 519 $id=vtlib_purify($_REQUEST['return_id']); 520 } 521 else if(isset($_REQUEST['duplicate_from']) && $_REQUEST['duplicate_from'] !='') 522 { 523 $id=vtlib_purify($_REQUEST['duplicate_from']); 524 } 525 526 $ext_prod_arr = Array(); 527 if($focus->mode == 'edit') 528 { 529 if($_REQUEST['taxtype'] == 'group') 530 $all_available_taxes = getAllTaxes('available','','edit',$id); 531 $return_old_values = ''; 532 if($module != 'PurchaseOrder') 533 { 534 $return_old_values = 'return_old_values'; 535 } 536 537 //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 538 //$ext_prod_arr = deleteInventoryProductDetails($focus->id,$return_old_values); 539 deleteInventoryProductDetails($focus); 540 } 541 else 542 { 543 if($_REQUEST['taxtype'] == 'group') 544 $all_available_taxes = getAllTaxes('available','','edit',$id); 545 } 546 $tot_no_prod = $_REQUEST['totalProductCount']; 547 //If the taxtype is group then retrieve all available taxes, else retrive associated taxes for each product inside loop 548 $prod_seq=1; 549 for($i=1; $i<=$tot_no_prod; $i++) 550 { 551 //if the product is deleted then we should avoid saving the deleted products 552 if($_REQUEST["deleted".$i] == 1) 553 continue; 554 555 $prod_id = vtlib_purify($_REQUEST['hdnProductId'.$i]); 556 if(isset($_REQUEST['productDescription'.$i])) 557 $description = vtlib_purify($_REQUEST['productDescription'.$i]); 558 /*else{ 559 $desc_duery = "select vtiger_crmentity.description AS product_description from vtiger_crmentity where vtiger_crmentity.crmid=?"; 560 $desc_res = $adb->pquery($desc_duery,array($prod_id)); 561 $description = $adb->query_result($desc_res,0,"product_description"); 562 } */ 563 $qty = vtlib_purify($_REQUEST['qty'.$i]); 564 $listprice = vtlib_purify($_REQUEST['listPrice'.$i]); 565 $comment = vtlib_purify($_REQUEST['comment'.$i]); 566 $purchaseCost = vtlib_purify($_REQUEST['purchaseCost'.$i]); 567 $margin = vtlib_purify($_REQUEST['margin'.$i]); 568 569 //we have to update the Product stock for PurchaseOrder if $update_prod_stock is true 570 if($module == 'PurchaseOrder' && $update_prod_stock == 'true') 571 { 572 addToProductStock($prod_id,$qty); 573 } 574 if($module == 'SalesOrder') 575 { 576 if($updateDemand == '-') 577 { 578 deductFromProductDemand($prod_id,$qty); 579 } 580 elseif($updateDemand == '+') 581 { 582 addToProductDemand($prod_id,$qty); 583 } 584 } 585 586 $query ="insert into vtiger_inventoryproductrel(id, productid, sequence_no, quantity, listprice, comment, description) values(?,?,?,?,?,?,?)"; 587 $qparams = array($focus->id,$prod_id,$prod_seq,$qty,$listprice,$comment,$description); 588 $adb->pquery($query,$qparams); 589 590 $lineitem_id = $adb->getLastInsertID(); 591 592 $sub_prod_str = $_REQUEST['subproduct_ids'.$i]; 593 if (!empty($sub_prod_str)) { 594 $sub_prod = split(":",$sub_prod_str); 595 for($j=0;$j<count($sub_prod);$j++){ 596 $query ="insert into vtiger_inventorysubproductrel(id, sequence_no, productid) values(?,?,?)"; 597 $qparams = array($focus->id,$prod_seq,$sub_prod[$j]); 598 $adb->pquery($query,$qparams); 599 } 600 } 601 $prod_seq++; 602 603 if($module != 'PurchaseOrder') 604 { 605 //update the stock with existing details 606 updateStk($prod_id,$qty,$focus->mode,$ext_prod_arr,$module); 607 } 608 609 //we should update discount and tax details 610 $updatequery = "update vtiger_inventoryproductrel set "; 611 $updateparams = array(); 612 613 //set the discount percentage or discount amount in update query, then set the tax values 614 if($_REQUEST['discount_type'.$i] == 'percentage') 615 { 616 $updatequery .= " discount_percent=?,"; 617 array_push($updateparams, $_REQUEST['discount_percentage'.$i]); 618 } 619 elseif($_REQUEST['discount_type'.$i] == 'amount') 620 { 621 $updatequery .= " discount_amount=?,"; 622 $discount_amount = $_REQUEST['discount_amount'.$i]; 623 array_push($updateparams, $discount_amount); 624 } 625 if($_REQUEST['taxtype'] == 'group') 626 { 627 for($tax_count=0;$tax_count<count($all_available_taxes);$tax_count++) 628 { 629 $tax_name = $all_available_taxes[$tax_count]['taxname']; 630 $request_tax_name = $tax_name."_group_percentage"; 631 if(isset($_REQUEST[$request_tax_name])) 632 $tax_val =vtlib_purify($_REQUEST[$request_tax_name]); 633 $updatequery .= " $tax_name = ?,"; 634 array_push($updateparams,$tax_val); 635 } 636 $updatequery = trim($updatequery,',')." where id=? and productid=? and lineitem_id = ?"; 637 array_push($updateparams,$focus->id,$prod_id, $lineitem_id); 638 } 639 else 640 { 641 $taxes_for_product = getTaxDetailsForProduct($prod_id,'all'); 642 for($tax_count=0;$tax_count<count($taxes_for_product);$tax_count++) 643 { 644 $tax_name = $taxes_for_product[$tax_count]['taxname']; 645 $request_tax_name = $tax_name."_percentage".$i; 646 647 $updatequery .= " $tax_name = ?,"; 648 array_push($updateparams, vtlib_purify($_REQUEST[$request_tax_name])); 649 } 650 $updatequery = trim($updatequery,',')." where id=? and productid=? and lineitem_id = ?"; 651 array_push($updateparams, $focus->id,$prod_id, $lineitem_id); 652 } 653 // jens 2006/08/19 - protect against empy update queries 654 if( !preg_match( '/set\s+where/i', $updatequery)) { 655 $adb->pquery($updatequery,$updateparams); 656 } 657 } 658 659 //we should update the netprice (subtotal), taxtype, group discount, S&H charge, S&H taxes, adjustment and total 660 //netprice, group discount, taxtype, S&H amount, adjustment and total to entity table 661 662 $updatequery = " update $focus->table_name set "; 663 $updateparams = array(); 664 $subtotal = $_REQUEST['subtotal']; 665 $updatequery .= " subtotal=?,"; 666 array_push($updateparams, $subtotal); 667 668 $updatequery .= " taxtype=?,"; 669 array_push($updateparams, $_REQUEST['taxtype']); 670 671 //for discount percentage or discount amount 672 if($_REQUEST['discount_type_final'] == 'percentage') 673 { 674 $updatequery .= " discount_percent=?,discount_amount=?,"; 675 array_push($updateparams, vtlib_purify($_REQUEST['discount_percentage_final'])); 676 array_push($updateparams,null); 677 } 678 elseif($_REQUEST['discount_type_final'] == 'amount') 679 { 680 $discount_amount_final = vtlib_purify($_REQUEST['discount_amount_final']); 681 $updatequery .= " discount_amount=?,discount_percent=?,"; 682 array_push($updateparams, $discount_amount_final); 683 array_push($updateparams,null); 684 } 685 elseif($_REQUEST['discount_type_final']=='zero'){ 686 $updatequery.="discount_amount=?,discount_percent=?,"; 687 array_push($updateparams,null); 688 array_push($updateparams,null); 689 } 690 $shipping_handling_charge = vtlib_purify($_REQUEST['shipping_handling_charge']); 691 $updatequery .= " s_h_amount=?,"; 692 array_push($updateparams, $shipping_handling_charge); 693 694 //if the user gave - sign in adjustment then add with the value 695 $adjustmentType = ''; 696 if($_REQUEST['adjustmentType'] == '-') 697 $adjustmentType = vtlib_purify($_REQUEST['adjustmentType']); 698 699 $adjustment = vtlib_purify($_REQUEST['adjustment']); 700 $updatequery .= " adjustment=?,"; 701 array_push($updateparams, $adjustmentType.$adjustment); 702 703 $total = vtlib_purify($_REQUEST['total']); 704 $updatequery .= " total=?,"; 705 array_push($updateparams, $total); 706 707 //to save the S&H tax details in vtiger_inventoryshippingrel table 708 $sh_tax_details = getAllTaxes('all','sh'); 709 $sh_query_fields = "id,"; 710 $sh_query_values = "?,"; 711 $sh_query_params = array($focus->id); 712 $sh_tax_pecent = 0; 713 for($i=0;$i<count($sh_tax_details);$i++) 714 { 715 $tax_name = $sh_tax_details[$i]['taxname']."_sh_percent"; 716 if($_REQUEST[$tax_name] != '') 717 { 718 $sh_tax_pecent = $sh_tax_pecent + vtlib_purify($_REQUEST[$tax_name]); 719 $sh_query_fields .= $sh_tax_details[$i]['taxname'].","; 720 $sh_query_values .= "?,"; 721 array_push($sh_query_params, vtlib_purify($_REQUEST[$tax_name])); 722 } 723 } 724 $sh_query_fields = trim($sh_query_fields,','); 725 $sh_query_values = trim($sh_query_values,','); 726 727 $updatequery .= " s_h_percent=?"; 728 array_push($updateparams, $sh_tax_pecent); 729 730 //$id_array = Array('PurchaseOrder'=>'purchaseorderid','SalesOrder'=>'salesorderid','Quotes'=>'quoteid','Invoice'=>'invoiceid'); 731 //Added where condition to which entity we want to update these values 732 $updatequery .= " where ".$focus->table_index."=?"; 733 array_push($updateparams, $focus->id); 734 $adb->pquery($updatequery,$updateparams); 735 736 $sh_query = "insert into vtiger_inventoryshippingrel($sh_query_fields) values($sh_query_values)"; 737 $adb->pquery($sh_query,$sh_query_params); 738 739 $log->debug("Exit from function saveInventoryProductDetails($module)."); 740 } 741 742 743 /** function used to get the tax type for the entity (PO, SO, Quotes or Invoice) 744 * @param string $module - module name 745 * @param int $id - id of the PO or SO or Quotes or Invoice 746 * @return string $taxtype - taxtype for the given entity which will be individual or group 747 */ 748 function getInventoryTaxType($module, $id) 749 { 750 global $log, $adb; 751 752 $log->debug("Entering into function getInventoryTaxType($module, $id)."); 753 754 $inv_table_array = Array('PurchaseOrder'=>'vtiger_purchaseorder','SalesOrder'=>'vtiger_salesorder','Quotes'=>'vtiger_quotes','Invoice'=>'vtiger_invoice'); 755 $inv_id_array = Array('PurchaseOrder'=>'purchaseorderid','SalesOrder'=>'salesorderid','Quotes'=>'quoteid','Invoice'=>'invoiceid'); 756 757 $res = $adb->pquery("select taxtype from $inv_table_array[$module] where $inv_id_array[$module]=?", array($id)); 758 759 $taxtype = $adb->query_result($res,0,'taxtype'); 760 761 $log->debug("Exit from function getInventoryTaxType($module, $id)."); 762 763 return $taxtype; 764 } 765 766 /** function used to get the price type for the entity (PO, SO, Quotes or Invoice) 767 * @param string $module - module name 768 * @param int $id - id of the PO or SO or Quotes or Invoice 769 * @return string $pricetype - pricetype for the given entity which will be unitprice or secondprice 770 */ 771 function getInventoryCurrencyInfo($module, $id) 772 { 773 global $log, $adb; 774 775 $log->debug("Entering into function getInventoryCurrencyInfo($module, $id)."); 776 777 $inv_table_array = Array('PurchaseOrder'=>'vtiger_purchaseorder','SalesOrder'=>'vtiger_salesorder','Quotes'=>'vtiger_quotes','Invoice'=>'vtiger_invoice'); 778 $inv_id_array = Array('PurchaseOrder'=>'purchaseorderid','SalesOrder'=>'salesorderid','Quotes'=>'quoteid','Invoice'=>'invoiceid'); 779 780 $inventory_table = $inv_table_array[$module]; 781 $inventory_id = $inv_id_array[$module]; 782 $res = $adb->pquery("select currency_id, $inventory_table.conversion_rate as conv_rate, vtiger_currency_info.* from $inventory_table 783 inner join vtiger_currency_info on $inventory_table.currency_id = vtiger_currency_info.id 784 where $inventory_id=?", array($id)); 785 786 $currency_info = array(); 787 $currency_info['currency_id'] = $adb->query_result($res,0,'currency_id'); 788 $currency_info['conversion_rate'] = $adb->query_result($res,0,'conv_rate'); 789 $currency_info['currency_name'] = $adb->query_result($res,0,'currency_name'); 790 $currency_info['currency_code'] = $adb->query_result($res,0,'currency_code'); 791 $currency_info['currency_symbol'] = $adb->query_result($res,0,'currency_symbol'); 792 793 $log->debug("Exit from function getInventoryCurrencyInfo($module, $id)."); 794 795 return $currency_info; 796 } 797 798 /** function used to get the taxvalue which is associated with a product for PO/SO/Quotes or Invoice 799 * @param int $id - id of PO/SO/Quotes or Invoice 800 * @param int $productid - product id 801 * @param string $taxname - taxname to which we want the value 802 * @return float $taxvalue - tax value 803 */ 804 function getInventoryProductTaxValue($id, $productid, $taxname) 805 { 806 global $log, $adb; 807 $log->debug("Entering into function getInventoryProductTaxValue($id, $productid, $taxname)."); 808 809 $res = $adb->pquery("select $taxname from vtiger_inventoryproductrel where id = ? and productid = ?", array($id, $productid)); 810 $taxvalue = $adb->query_result($res,0,$taxname); 811 812 if($taxvalue == '') 813 $taxvalue = '0.00'; 814 815 $log->debug("Exit from function getInventoryProductTaxValue($id, $productid, $taxname)."); 816 817 return $taxvalue; 818 } 819 820 /** function used to get the shipping & handling tax percentage for the given inventory id and taxname 821 * @param int $id - entity id which will be PO/SO/Quotes or Invoice id 822 * @param string $taxname - shipping and handling taxname 823 * @return float $taxpercentage - shipping and handling taxpercentage which is associated with the given entity 824 */ 825 function getInventorySHTaxPercent($id, $taxname) 826 { 827 global $log, $adb; 828 $log->debug("Entering into function getInventorySHTaxPercent($id, $taxname)"); 829 830 $res = $adb->pquery("select $taxname from vtiger_inventoryshippingrel where id= ?", array($id)); 831 $taxpercentage = $adb->query_result($res,0,$taxname); 832 833 if($taxpercentage == '') 834 $taxpercentage = '0.00'; 835 836 $log->debug("Exit from function getInventorySHTaxPercent($id, $taxname)"); 837 838 return $taxpercentage; 839 } 840 841 /** Function used to get the list of all Currencies as a array 842 * @param string available - if 'all' returns all the currencies, default value 'available' returns only the currencies which are available for use. 843 * return array $currency_details - return details of all the currencies as a array 844 */ 845 function getAllCurrencies($available='available') { 846 global $adb, $log; 847 $log->debug("Entering into function getAllCurrencies($available)"); 848 849 $sql = "select * from vtiger_currency_info"; 850 if ($available != 'all') { 851 $sql .= " where currency_status='Active' and deleted=0"; 852 } 853 $res=$adb->pquery($sql, array()); 854 $noofrows = $adb->num_rows($res); 855 856 for($i=0;$i<$noofrows;$i++) 857 { 858 $currency_details[$i]['currencylabel'] = $adb->query_result($res,$i,'currency_name'); 859 $currency_details[$i]['currencycode'] = $adb->query_result($res,$i,'currency_code'); 860 $currency_details[$i]['currencysymbol'] = $adb->query_result($res,$i,'currency_symbol'); 861 $currency_details[$i]['curid'] = $adb->query_result($res,$i,'id'); 862 /* alias key added to be consistent with result of InventoryUtils::getInventoryCurrencyInfo */ 863 $currency_details[$i]['currency_id'] = $adb->query_result($res,$i,'id'); 864 $currency_details[$i]['conversionrate'] = $adb->query_result($res,$i,'conversion_rate'); 865 $currency_details[$i]['curname'] = 'curname' . $adb->query_result($res,$i,'id'); 866 } 867 868 $log->debug("Entering into function getAllCurrencies($available)"); 869 return $currency_details; 870 871 } 872 873 /** Function used to get all the price details for different currencies which are associated to the given product 874 * @param int $productid - product id to which we want to get all the associated prices 875 * @param decimal $unit_price - Unit price of the product 876 * @param string $available - available or available_associated where as default is available, if available then the prices in the currencies which are available now will be returned, otherwise if the value is available_associated then prices of all the associated currencies will be retruned 877 * @return array $price_details - price details as a array with productid, curid, curname 878 */ 879 function getPriceDetailsForProduct($productid, $unit_price, $available='available', $itemtype='Products') 880 { 881 global $log, $adb; 882 $log->debug("Entering into function getPriceDetailsForProduct($productid)"); 883 if($productid != '') 884 { 885 $product_currency_id = getProductBaseCurrency($productid, $itemtype); 886 $product_base_conv_rate = getBaseConversionRateForProduct($productid,'edit',$itemtype); 887 // Detail View 888 if ($available == 'available_associated') { 889 $query = "select vtiger_currency_info.*, vtiger_productcurrencyrel.converted_price, vtiger_productcurrencyrel.actual_price 890 from vtiger_currency_info 891 inner join vtiger_productcurrencyrel on vtiger_currency_info.id = vtiger_productcurrencyrel.currencyid 892 where vtiger_currency_info.currency_status = 'Active' and vtiger_currency_info.deleted=0 893 and vtiger_productcurrencyrel.productid = ? and vtiger_currency_info.id != ?"; 894 $params = array($productid, $product_currency_id); 895 } else { // Edit View 896 $query = "select vtiger_currency_info.*, vtiger_productcurrencyrel.converted_price, vtiger_productcurrencyrel.actual_price 897 from vtiger_currency_info 898 left join vtiger_productcurrencyrel 899 on vtiger_currency_info.id = vtiger_productcurrencyrel.currencyid and vtiger_productcurrencyrel.productid = ? 900 where vtiger_currency_info.currency_status = 'Active' and vtiger_currency_info.deleted=0"; 901 $params = array($productid); 902 } 903 904 //Postgres 8 fixes 905 if( $adb->dbType == "pgsql") 906 $query = fixPostgresQuery( $query, $log, 0); 907 908 $res = $adb->pquery($query, $params); 909 for($i=0;$i<$adb->num_rows($res);$i++) 910 { 911 $price_details[$i]['productid'] = $productid; 912 $price_details[$i]['currencylabel'] = $adb->query_result($res,$i,'currency_name'); 913 $price_details[$i]['currencycode'] = $adb->query_result($res,$i,'currency_code'); 914 $price_details[$i]['currencysymbol'] = $adb->query_result($res,$i,'currency_symbol'); 915 $currency_id = $adb->query_result($res,$i,'id'); 916 $price_details[$i]['curid'] = $currency_id; 917 $price_details[$i]['curname'] = 'curname' . $adb->query_result($res,$i,'id'); 918 $cur_value = $adb->query_result($res,$i,'actual_price'); 919 920 // Get the conversion rate for the given currency, get the conversion rate of the product currency to base currency. 921 // Both together will be the actual conversion rate for the given currency. 922 $conversion_rate = $adb->query_result($res,$i,'conversion_rate'); 923 $actual_conversion_rate = $product_base_conv_rate * $conversion_rate; 924 925 $is_basecurrency = false; 926 if ($currency_id == $product_currency_id) { 927 $is_basecurrency = true; 928 } 929 if ($cur_value == null || $cur_value == '') { 930 $price_details[$i]['check_value'] = false; 931 if ($unit_price != null) { 932 $cur_value = CurrencyField::convertFromMasterCurrency($unit_price, $actual_conversion_rate); 933 } else { 934 $cur_value = '0'; 935 } 936 } else if($is_basecurrency){ 937 $price_details[$i]['check_value'] = true; 938 } 939 $price_details[$i]['curvalue'] = CurrencyField::convertToUserFormat($cur_value, null, true); 940 $price_details[$i]['conversionrate'] = $actual_conversion_rate; 941 $price_details[$i]['is_basecurrency'] = $is_basecurrency; 942 } 943 } 944 else 945 { 946 if($available == 'available') { // Create View 947 global $current_user; 948 949 $user_currency_id = fetchCurrency($current_user->id); 950 951 $query = "select vtiger_currency_info.* from vtiger_currency_info 952 where vtiger_currency_info.currency_status = 'Active' and vtiger_currency_info.deleted=0"; 953 $params = array(); 954 955 $res = $adb->pquery($query, $params); 956 for($i=0;$i<$adb->num_rows($res);$i++) 957 { 958 $price_details[$i]['currencylabel'] = $adb->query_result($res,$i,'currency_name'); 959 $price_details[$i]['currencycode'] = $adb->query_result($res,$i,'currency_code'); 960 $price_details[$i]['currencysymbol'] = $adb->query_result($res,$i,'currency_symbol'); 961 $currency_id = $adb->query_result($res,$i,'id'); 962 $price_details[$i]['curid'] = $currency_id; 963 $price_details[$i]['curname'] = 'curname' . $adb->query_result($res,$i,'id'); 964 965 // Get the conversion rate for the given currency, get the conversion rate of the product currency(logged in user's currency) to base currency. 966 // Both together will be the actual conversion rate for the given currency. 967 $conversion_rate = $adb->query_result($res,$i,'conversion_rate'); 968 $user_cursym_convrate = getCurrencySymbolandCRate($user_currency_id); 969 $product_base_conv_rate = 1 / $user_cursym_convrate['rate']; 970 $actual_conversion_rate = $product_base_conv_rate * $conversion_rate; 971 972 $price_details[$i]['check_value'] = false; 973 $price_details[$i]['curvalue'] = '0'; 974 $price_details[$i]['conversionrate'] = $actual_conversion_rate; 975 976 $is_basecurrency = false; 977 if ($currency_id == $user_currency_id) { 978 $is_basecurrency = true; 979 } 980 $price_details[$i]['is_basecurrency'] = $is_basecurrency; 981 } 982 } else { 983 $log->debug("Product id is empty. we cannot retrieve the associated prices."); 984 } 985 } 986 987 $log->debug("Exit from function getPriceDetailsForProduct($productid)"); 988 return $price_details; 989 } 990 991 /** Function used to get the base currency used for the given Product 992 * @param int $productid - product id for which we want to get the id of the base currency 993 * @return int $currencyid - id of the base currency for the given product 994 */ 995 function getProductBaseCurrency($productid,$module='Products') { 996 global $adb, $log; 997 if ($module == 'Services') { 998 $sql = "select currency_id from vtiger_service where serviceid=?"; 999 } else { 1000 $sql = "select currency_id from vtiger_products where productid=?"; 1001 } 1002 $params = array($productid); 1003 $res = $adb->pquery($sql, $params); 1004 $currencyid = $adb->query_result($res, 0, 'currency_id'); 1005 return $currencyid; 1006 } 1007 1008 /** Function used to get the conversion rate for the product base currency with respect to the CRM base currency 1009 * @param int $productid - product id for which we want to get the conversion rate of the base currency 1010 * @param string $mode - Mode in which the function is called 1011 * @return number $conversion_rate - conversion rate of the base currency for the given product based on the CRM base currency 1012 */ 1013 function getBaseConversionRateForProduct($productid, $mode='edit', $module='Products') { 1014 global $adb, $log, $current_user; 1015 1016 if ($mode == 'edit') { 1017 if ($module == 'Services') { 1018 $sql = "select conversion_rate from vtiger_service inner join vtiger_currency_info 1019 on vtiger_service.currency_id = vtiger_currency_info.id where vtiger_service.serviceid=?"; 1020 } else { 1021 $sql = "select conversion_rate from vtiger_products inner join vtiger_currency_info 1022 on vtiger_products.currency_id = vtiger_currency_info.id where vtiger_products.productid=?"; 1023 } 1024 $params = array($productid); 1025 } else { 1026 $sql = "select conversion_rate from vtiger_currency_info where id=?"; 1027 $params = array(fetchCurrency($current_user->id)); 1028 } 1029 1030 $res = $adb->pquery($sql, $params); 1031 $conv_rate = $adb->query_result($res, 0, 'conversion_rate'); 1032 1033 return 1 / $conv_rate; 1034 } 1035 1036 /** Function used to get the prices for the given list of products based in the specified currency 1037 * @param int $currencyid - currency id based on which the prices have to be provided 1038 * @param array $product_ids - List of product id's for which we want to get the price based on given currency 1039 * @return array $prices_list - List of prices for the given list of products based on the given currency in the form of 'product id' mapped to 'price value' 1040 */ 1041 function getPricesForProducts($currencyid, $product_ids, $module='Products') { 1042 global $adb,$log,$current_user; 1043 1044 $price_list = array(); 1045 if (count($product_ids) > 0) { 1046 if ($module == 'Services') { 1047 $query = "SELECT vtiger_currency_info.id, vtiger_currency_info.conversion_rate, " . 1048 "vtiger_service.serviceid AS productid, vtiger_service.unit_price, " . 1049 "vtiger_productcurrencyrel.actual_price " . 1050 "FROM (vtiger_currency_info, vtiger_service) " . 1051 "left join vtiger_productcurrencyrel on vtiger_service.serviceid = vtiger_productcurrencyrel.productid " . 1052 "and vtiger_currency_info.id = vtiger_productcurrencyrel.currencyid " . 1053 "where vtiger_service.serviceid in (". generateQuestionMarks($product_ids) .") and vtiger_currency_info.id = ?"; 1054 } else { 1055 $query = "SELECT vtiger_currency_info.id, vtiger_currency_info.conversion_rate, " . 1056 "vtiger_products.productid, vtiger_products.unit_price, " . 1057 "vtiger_productcurrencyrel.actual_price " . 1058 "FROM (vtiger_currency_info, vtiger_products) " . 1059 "left join vtiger_productcurrencyrel on vtiger_products.productid = vtiger_productcurrencyrel.productid " . 1060 "and vtiger_currency_info.id = vtiger_productcurrencyrel.currencyid " . 1061 "where vtiger_products.productid in (". generateQuestionMarks($product_ids) .") and vtiger_currency_info.id = ?"; 1062 } 1063 $params = array($product_ids, $currencyid); 1064 $result = $adb->pquery($query, $params); 1065 1066 for($i=0;$i<$adb->num_rows($result);$i++) 1067 { 1068 $product_id = $adb->query_result($result, $i, 'productid'); 1069 if(getFieldVisibilityPermission($module,$current_user->id,'unit_price') == '0') { 1070 $actual_price = (float)$adb->query_result($result, $i, 'actual_price'); 1071 1072 if ($actual_price == null || $actual_price == '') { 1073 $unit_price = $adb->query_result($result, $i, 'unit_price'); 1074 $product_conv_rate = $adb->query_result($result, $i, 'conversion_rate'); 1075 $product_base_conv_rate = getBaseConversionRateForProduct($product_id,'edit',$module); 1076 $conversion_rate = $product_conv_rate * $product_base_conv_rate; 1077 1078 $actual_price = $unit_price * $conversion_rate; 1079 } 1080 $price_list[$product_id] = $actual_price; 1081 } else { 1082 $price_list[$product_id] = ''; 1083 } 1084 } 1085 } 1086 return $price_list; 1087 } 1088 1089 /** Function used to get the currency used for the given Price book 1090 * @param int $pricebook_id - pricebook id for which we want to get the id of the currency used 1091 * @return int $currencyid - id of the currency used for the given pricebook 1092 */ 1093 function getPriceBookCurrency($pricebook_id) { 1094 global $adb; 1095 $result = $adb->pquery("select currency_id from vtiger_pricebook where pricebookid=?", array($pricebook_id)); 1096 $currency_id = $adb->query_result($result,0,'currency_id'); 1097 return $currency_id; 1098 } 1099 1100 // deduct products from stock - if status will be changed from cancel to other status. 1101 function deductProductsFromStock($recordId) { 1102 global $adb; 1103 $adb->pquery("UPDATE vtiger_inventoryproductrel SET incrementondel=1 WHERE id=?",array($recordId)); 1104 1105 $product_info = $adb->pquery("SELECT productid,sequence_no, quantity from vtiger_inventoryproductrel WHERE id=?",array($recordId)); 1106 $numrows = $adb->num_rows($product_info); 1107 for($index = 0;$index <$numrows;$index++) { 1108 $productid = $adb->query_result($product_info,$index,'productid'); 1109 $qty = $adb->query_result($product_info,$index,'quantity'); 1110 $sequence_no = $adb->query_result($product_info,$index,'sequence_no'); 1111 $qtyinstk= getPrdQtyInStck($productid); 1112 $upd_qty = $qtyinstk-$qty; 1113 updateProductQty($productid, $upd_qty); 1114 $sub_prod_query = $adb->pquery("SELECT productid from vtiger_inventorysubproductrel WHERE id=? AND sequence_no=?",array($recordId,$sequence_no)); 1115 if($adb->num_rows($sub_prod_query)>0) { 1116 for($j=0;$j<$adb->num_rows($sub_prod_query);$j++) { 1117 $sub_prod_id = $adb->query_result($sub_prod_query,$j,"productid"); 1118 $sqtyinstk= getPrdQtyInStck($sub_prod_id); 1119 $supd_qty = $sqtyinstk-$qty; 1120 updateProductQty($sub_prod_id, $supd_qty); 1121 } 1122 } 1123 } 1124 } 1125 1126 // Add Products to stock - status changed to cancel or delete the invoice 1127 function addProductsToStock($recordId) { 1128 global $adb; 1129 1130 $product_info = $adb->pquery("SELECT productid,sequence_no, quantity from vtiger_inventoryproductrel WHERE id=?",array($recordId)); 1131 $numrows = $adb->num_rows($product_info); 1132 for($index = 0;$index <$numrows;$index++) { 1133 $productid = $adb->query_result($product_info,$index,'productid'); 1134 $qty = $adb->query_result($product_info,$index,'quantity'); 1135 $sequence_no = $adb->query_result($product_info,$index,'sequence_no'); 1136 $qtyinstk= getPrdQtyInStck($productid); 1137 $upd_qty = $qtyinstk+$qty; 1138 updateProductQty($productid, $upd_qty); 1139 $sub_prod_query = $adb->pquery("SELECT productid from vtiger_inventorysubproductrel WHERE id=? AND sequence_no=?",array($recordId,$sequence_no)); 1140 if($adb->num_rows($sub_prod_query)>0) { 1141 for($j=0;$j<$adb->num_rows($sub_prod_query);$j++) { 1142 $sub_prod_id = $adb->query_result($sub_prod_query,$j,"productid"); 1143 $sqtyinstk= getPrdQtyInStck($sub_prod_id); 1144 $supd_qty = $sqtyinstk+$qty; 1145 updateProductQty($sub_prod_id, $supd_qty); 1146 } 1147 } 1148 } 1149 } 1150 1151 function getImportBatchLimit() { 1152 $importBatchLimit = 100; 1153 return $importBatchLimit; 1154 } 1155 1156 function createRecords($obj) { 1157 global $adb; 1158 $moduleName = $obj->module; 1159 1160 $moduleHandler = vtws_getModuleHandlerFromName($moduleName, $obj->user); 1161 $moduleMeta = $moduleHandler->getMeta(); 1162 $moduleObjectId = $moduleMeta->getEntityId(); 1163 $moduleFields = $moduleMeta->getModuleFields(); 1164 $focus = CRMEntity::getInstance($moduleName); 1165 1166 $tableName = Import_Utils_Helper::getDbTableName($obj->user); 1167 $sql = 'SELECT * FROM ' . $tableName . ' WHERE status = '. Import_Data_Action::$IMPORT_RECORD_NONE .' GROUP BY subject'; 1168 1169 if($obj->batchImport) { 1170 $importBatchLimit = getImportBatchLimit(); 1171 $sql .= ' LIMIT '. $importBatchLimit; 1172 } 1173 $result = $adb->query($sql); 1174 $numberOfRecords = $adb->num_rows($result); 1175 1176 if ($numberOfRecords <= 0) { 1177 return; 1178 } 1179 1180 $fieldMapping = $obj->fieldMapping; 1181 $fieldColumnMapping = $moduleMeta->getFieldColumnMapping(); 1182 1183 for ($i = 0; $i < $numberOfRecords; ++$i) { 1184 $row = $adb->raw_query_result_rowdata($result, $i); 1185 $rowId = $row['id']; 1186 $entityInfo = null; 1187 $fieldData = array(); 1188 $lineItems = array(); 1189 $subject = $row['subject']; 1190 $sql = 'SELECT * FROM ' . $tableName . ' WHERE status = '. Import_Data_Action::$IMPORT_RECORD_NONE .' AND subject = "'. str_replace("\"", "\\\"", $subject) .'"'; 1191 $subjectResult = $adb->query($sql); 1192 $count = $adb->num_rows($subjectResult); 1193 $subjectRowIDs = array(); 1194 for ($j = 0; $j < $count; ++$j) { 1195 $subjectRow = $adb->raw_query_result_rowdata($subjectResult, $j); 1196 array_push($subjectRowIDs, $subjectRow['id']); 1197 if ($subjectRow['productid'] == '' || $subjectRow['quantity'] == '' || $subjectRow['listprice'] == '') { 1198 continue; 1199 } else { 1200 $lineItemData = array(); 1201 foreach ($fieldMapping as $fieldName => $index) { 1202 if($moduleFields[$fieldName]->getTableName() == 'vtiger_inventoryproductrel') { 1203 $lineItemData[$fieldName] = $subjectRow[$fieldName]; 1204 } 1205 } 1206 array_push($lineItems,$lineItemData); 1207 } 1208 } 1209 foreach ($fieldMapping as $fieldName => $index) { 1210 $fieldData[$fieldName] = $row[strtolower($fieldName)]; 1211 } 1212 if (!array_key_exists('assigned_user_id', $fieldData)) { 1213 $fieldData['assigned_user_id'] = $obj->user->id; 1214 } 1215 1216 if (!empty($lineItems)) { 1217 if(method_exists($focus, 'importRecord')) { 1218 $entityInfo = $focus->importRecord($obj, $fieldData, $lineItems); 1219 } 1220 } 1221 1222 if($entityInfo == null) { 1223 $entityInfo = array('id' => null, 'status' => $obj->getImportRecordStatus('failed')); 1224 } 1225 foreach ($subjectRowIDs as $id) { 1226 $obj->importedRecordInfo[$id] = $entityInfo; 1227 $obj->updateImportStatus($id, $entityInfo); 1228 } 1229 } 1230 unset($result); 1231 return true; 1232 } 1233 1234 function isRecordExistInDB($fieldData, $moduleMeta, $user) { 1235 global $adb, $log; 1236 $moduleFields = $moduleMeta->getModuleFields(); 1237 $isRecordExist = false; 1238 if (array_key_exists('productid', $fieldData)) { 1239 $fieldName = 'productid'; 1240 $fieldValue = $fieldData[$fieldName]; 1241 $fieldInstance = $moduleFields[$fieldName]; 1242 if ($fieldInstance->getFieldDataType() == 'reference') { 1243 $entityId = false; 1244 if (!empty($fieldValue)) { 1245 if(strpos($fieldValue, '::::') > 0) { 1246 $fieldValueDetails = explode('::::', $fieldValue); 1247 } else if (strpos($fieldValue, ':::') > 0) { 1248 $fieldValueDetails = explode(':::', $fieldValue); 1249 } else { 1250 $fieldValueDetails = $fieldValue; 1251 } 1252 if (count($fieldValueDetails) > 1) { 1253 $referenceModuleName = trim($fieldValueDetails[0]); 1254 $entityLabel = trim($fieldValueDetails[1]); 1255 $entityId = getEntityId($referenceModuleName, $entityLabel); 1256 } else { 1257 $referencedModules = $fieldInstance->getReferenceList(); 1258 $entityLabel = $fieldValue; 1259 foreach ($referencedModules as $referenceModule) { 1260 $referenceModuleName = $referenceModule; 1261 $referenceEntityId = getEntityId($referenceModule, $entityLabel); 1262 if ($referenceEntityId != 0) { 1263 $entityId = $referenceEntityId; 1264 break; 1265 } 1266 } 1267 } 1268 if (!empty($entityId) && $entityId != 0) { 1269 $types = vtws_listtypes(null, $user); 1270 $accessibleModules = $types['types']; 1271 if (in_array($referenceModuleName, $accessibleModules)) { 1272 $isRecordExist = true; 1273 } 1274 } 1275 } 1276 } 1277 } 1278 return $isRecordExist; 1279 } 1280 1281 function importRecord($obj, $inventoryFieldData, $lineItemDetails) { 1282 global $adb, $log; 1283 $moduleName = $obj->module; 1284 $fieldMapping = $obj->fieldMapping; 1285 1286 $inventoryHandler = vtws_getModuleHandlerFromName($moduleName, $obj->user); 1287 $inventoryMeta = $inventoryHandler->getMeta(); 1288 $moduleFields = $inventoryMeta->getModuleFields(); 1289 $isRecordExist = isRecordExistInDB($inventoryFieldData, $inventoryMeta, $obj->user); 1290 $lineItemHandler = vtws_getModuleHandlerFromName('LineItem', $obj->user); 1291 $lineItemMeta = $lineItemHandler->getMeta(); 1292 1293 $lineItems = array(); 1294 foreach ($lineItemDetails as $index => $lineItemFieldData) { 1295 $isLineItemExist = isRecordExistInDB($lineItemFieldData, $lineItemMeta, $obj->user); 1296 if($isLineItemExist) { 1297 $count = $index; 1298 $lineItemData = array(); 1299 $lineItemFieldData = $obj->transformForImport($lineItemFieldData, $lineItemMeta); 1300 foreach ($fieldMapping as $fieldName => $index) { 1301 if($moduleFields[$fieldName]->getTableName() == 'vtiger_inventoryproductrel') { 1302 $lineItemData[$fieldName] = $lineItemFieldData[$fieldName]; 1303 if($fieldName != 'productid') 1304 $inventoryFieldData[$fieldName] = ''; 1305 } 1306 } 1307 array_push($lineItems,$lineItemData); 1308 } 1309 } 1310 if (empty ($lineItems)) { 1311 return null; 1312 } elseif ($isRecordExist == false) { 1313 foreach ($lineItemDetails[$count] as $key => $value) { 1314 $inventoryFieldData[$key] = $value; 1315 } 1316 } 1317 1318 $fieldData = $obj->transformForImport($inventoryFieldData, $inventoryMeta); 1319 if(empty($fieldData) || empty($lineItemDetails)) { 1320 return null; 1321 } 1322 if ($fieldData['currency_id'] == ' ') { 1323 $fieldData['currency_id'] = '1'; 1324 } 1325 $fieldData['LineItems'] = $lineItems; 1326 1327 $webserviceObject = VtigerWebserviceObject::fromName($adb, $moduleName); 1328 $inventoryOperation = new VtigerInventoryOperation($webserviceObject, $obj->user, $adb, $log); 1329 1330 $entityInfo = $inventoryOperation->create($moduleName, $fieldData); 1331 $entityInfo['status'] = $obj->getImportRecordStatus('created'); 1332 return $entityInfo; 1333 } 1334 1335 function getImportStatusCount($obj) { 1336 global $adb; 1337 $tableName = Import_Utils_Helper::getDbTableName($obj->user); 1338 $result = $adb->query('SELECT status FROM '.$tableName. ' GROUP BY subject'); 1339 1340 $statusCount = array('TOTAL' => 0, 'IMPORTED' => 0, 'FAILED' => 0, 'PENDING' => 0, 1341 'CREATED' => 0, 'SKIPPED' => 0, 'UPDATED' => 0, 'MERGED' => 0); 1342 1343 if($result) { 1344 $noOfRows = $adb->num_rows($result); 1345 $statusCount['TOTAL'] = $noOfRows; 1346 for($i=0; $i<$noOfRows; ++$i) { 1347 $status = $adb->query_result($result, $i, 'status'); 1348 if($obj->getImportRecordStatus('none') == $status) { 1349 $statusCount['PENDING']++; 1350 1351 } elseif($obj->getImportRecordStatus('failed') == $status) { 1352 $statusCount['FAILED']++; 1353 1354 } else { 1355 $statusCount['IMPORTED']++; 1356 switch($status) { 1357 case $obj->getImportRecordStatus('created') : $statusCount['CREATED']++; 1358 break; 1359 case $obj->getImportRecordStatus('skipped') : $statusCount['SKIPPED']++; 1360 break; 1361 case $obj->getImportRecordStatus('updated') : $statusCount['UPDATED']++; 1362 break; 1363 case $obj->getImportRecordStatus('merged') : $statusCount['MERGED']++; 1364 break; 1365 } 1366 } 1367 } 1368 } 1369 return $statusCount; 1370 } 1371 1372 function undoLastImport($obj, $user) { 1373 global $adb; 1374 $moduleName = $obj->get('module'); 1375 $ownerId = $obj->get('foruser'); 1376 $owner = new Users(); 1377 $owner->id = $ownerId; 1378 $owner->retrieve_entity_info($ownerId, 'Users'); 1379 1380 $dbTableName = Import_Utils_Helper::getDbTableName($owner); 1381 1382 if(!is_admin($user) && $user->id != $owner->id) { 1383 $viewer = new Vtiger_Viewer(); 1384 $viewer->view('OperationNotPermitted.tpl', 'Vtiger'); 1385 exit; 1386 } 1387 $result = $adb->query("SELECT recordid FROM $dbTableName WHERE status = ". Import_Data_Controller::$IMPORT_RECORD_CREATED 1388 ." AND recordid IS NOT NULL GROUP BY subject"); 1389 $noOfRecords = $adb->num_rows($result); 1390 $noOfRecordsDeleted = 0; 1391 for($i=0; $i<$noOfRecords; ++$i) { 1392 $recordId = $adb->query_result($result, $i, 'recordid'); 1393 if(isRecordExists($recordId) && isPermitted($moduleName, 'Delete', $recordId) == 'yes') { 1394 $focus = CRMEntity::getInstance($moduleName); 1395 $focus->id = $recordId; 1396 $focus->trash($moduleName, $recordId); 1397 $noOfRecordsDeleted++; 1398 } 1399 } 1400 1401 $viewer = new Vtiger_Viewer(); 1402 $viewer->assign('FOR_MODULE', $moduleName); 1403 $viewer->assign('TOTAL_RECORDS', $noOfRecords); 1404 $viewer->assign('DELETED_RECORDS_COUNT', $noOfRecordsDeleted); 1405 $viewer->view('ImportUndoResult.tpl'); 1406 } 1407 1408 function getInventoryFieldsForExport($tableName) { 1409 1410 $sql = ','.$tableName.'.adjustment AS "Adjustment", '.$tableName.'.total AS "Total", '.$tableName.'.subtotal AS "Sub Total", '; 1411 $sql .= $tableName.'.taxtype AS "Tax Type", '.$tableName.'.discount_amount AS "Discount Amount", '; 1412 $sql .= $tableName.'.discount_percent AS "Discount Percent", '.$tableName.'.s_h_amount AS "S&H Amount", '; 1413 $sql .= 'vtiger_currency_info.currency_name as "Currency" '; 1414 1415 return $sql; 1416 } 1417 1418 function getCurrencyId($fieldValue) { 1419 global $adb; 1420 1421 $sql = 'SELECT id FROM vtiger_currency_info WHERE currency_name = ? AND deleted = 0'; 1422 $result = $adb->pquery($sql, array($fieldValue)); 1423 $currencyId = 1; 1424 if ($adb->num_rows($result) > 0) { 1425 $currencyId = $adb->query_result($result, 0, 'id'); 1426 } 1427 return $currencyId; 1428 } 1429 1430 /** 1431 * Function used to get the lineitems fields 1432 * @global type $adb 1433 * @return type <array> - list of lineitem fields 1434 */ 1435 function getLineItemFields(){ 1436 global $adb; 1437 1438 $sql = 'SELECT DISTINCT columnname FROM vtiger_field WHERE tablename=?'; 1439 $result = $adb->pquery($sql, array('vtiger_inventoryproductrel')); 1440 $lineItemdFields = array(); 1441 $num_rows = $adb->num_rows($result); 1442 for($i=0; $i<$num_rows; $i++){ 1443 $lineItemdFields[] = $adb->query_result($result,$i, 'columnname'); 1444 } 1445 return $lineItemdFields; 1446 } 1447 1448 ?>
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Fri Nov 28 20:08:37 2014 | Cross-referenced by PHPXref 0.7.1 |