Newer
Older
/*+***********************************************************************************
* The contents of this file are subject to the vtiger CRM Public License Version 1.1
* ("License"); You may not use this file except in compliance with the License
* The Original Code is: vtiger CRM Open Source
* The Initial Developer of the Original Code is vtiger.
* Portions created by vtiger are Copyright (C) vtiger.
* All Rights Reserved.
**************************************************************************************/
/**
* This function updates the stock information once the product is ordered.
* Param $productid - product id
* Param $qty - product quantity in no's
* Param $mode - mode type
* Param $ext_prod_arr - existing vtiger_products
* Param $module - module name
* return type void
*/
function updateStk($product_id,$qty,$mode,$ext_prod_arr,$module)
{
global $log;
$log->debug("Entering updateStk(".$product_id.",".$qty.",".$mode.",".implode('',$ext_prod_arr).",".$module.") method ...");
global $adb;
global $current_user;
$log->debug("Inside updateStk function, module=".$module);
$log->debug("Product Id = $product_id & Qty = $qty");
$prod_name = getProductName($product_id);
$log->debug("Prd Qty in Stock ".$qtyinstk);
$upd_qty = $qtyinstk-$qty;
sendPrdStckMail($product_id,$upd_qty,$prod_name,$qtyinstk,$qty,$module);
$log->debug("Exiting updateStk method ...");
}
/**
* This function sends a mail to the handler whenever the product reaches the reorder level.
* Param $product_id - product id
* Param $upd_qty - updated product quantity in no's
* Param $prod_name - product name
* Param $qtyinstk - quantity in stock
* Param $qty - quantity
* Param $module - module name
* return type void
*/
function sendPrdStckMail($product_id,$upd_qty,$prod_name,$qtyinstk,$qty,$module)
{
global $log;
$log->debug("Entering sendPrdStckMail(".$product_id.",".$upd_qty.",".$prod_name.",".$qtyinstk.",".$qty.",".$module.") method ...");
global $current_user;
global $adb;
$reorderlevel = getPrdReOrderLevel($product_id);
$log->debug("Inside sendPrdStckMail function, module=".$module);
$log->debug("Prd reorder level ".$reorderlevel);
if($upd_qty < $reorderlevel)
{
//send mail to the handler
$handler = getRecordOwnerId($product_id);
foreach($handler as $type=>$id){
$to_address = getUserEmail($handler);
} else {
$to_address = implode(',', getDefaultAssigneeEmailIds($handler));
}
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
//Get the email details from database;
if($module == 'SalesOrder')
{
$notification_table = 'SalesOrderNotification';
$quan_name = '{SOQUANTITY}';
}
if($module == 'Quotes')
{
$notification_table = 'QuoteNotification';
$quan_name = '{QUOTEQUANTITY}';
}
if($module == 'Invoice')
{
$notification_table = 'InvoiceNotification';
}
$query = "select * from vtiger_inventorynotification where notificationname=?";
$result = $adb->pquery($query, array($notification_table));
$subject = $adb->query_result($result,0,'notificationsubject');
$body = $adb->query_result($result,0,'notificationbody');
$status = $adb->query_result($result,0,'status');
if($status == 0 || $status == '')
return false;
$subject = str_replace('{PRODUCTNAME}',$prod_name,$subject);
$body = str_replace('{HANDLER}',$handler_name,$body);
$body = str_replace('{PRODUCTNAME}',$prod_name,$body);
if($module == 'Invoice')
{
$body = str_replace('{CURRENTSTOCK}',$upd_qty,$body);
$body = str_replace('{REORDERLEVELVALUE}',$reorderlevel,$body);
}
else
{
$body = str_replace('{CURRENTSTOCK}',$qtyinstk,$body);
$body = str_replace($quan_name,$qty,$body);
}
$body = str_replace('{CURRENTUSER}',$current_user->user_name,$body);
$mail_status = send_mail($module,$to_address,$current_user->user_name,$current_user->email1,decode_html($subject),nl2br(to_html($body)));
}
$log->debug("Exiting sendPrdStckMail method ...");
}
/**This function is used to get the reorder level of a product
*Param $product_id - product id
*Returns type numeric
*/
function getPrdReOrderLevel($product_id)
{
global $log;
$log->debug("Entering getPrdReOrderLevel(".$product_id.") method ...");
global $adb;
$query1 = "SELECT reorderlevel FROM vtiger_products WHERE productid = ?";
$result=$adb->pquery($query1, array($product_id));
$reorderlevel= $adb->query_result($result,0,"reorderlevel");
$log->debug("Exiting getPrdReOrderLevel method ...");
return $reorderlevel;
}
/** function to get the taxid
* @param string $type - tax type (VAT or Sales or Service)
* return int $taxid - taxid corresponding to the Tax type from vtiger_inventorytaxinfo vtiger_table
*/
function getTaxId($type)
{
global $adb, $log;
$log->debug("Entering into getTaxId($type) function.");
$res = $adb->pquery("SELECT taxid FROM vtiger_inventorytaxinfo WHERE taxname=?", array($type));
$taxid = $adb->query_result($res,0,'taxid');
$log->debug("Exiting from getTaxId($type) function. return value=$taxid");
return $taxid;
}
/** function to get the taxpercentage
* @param string $type - tax type (VAT or Sales or Service)
* return int $taxpercentage - taxpercentage corresponding to the Tax type from vtiger_inventorytaxinfo vtiger_table
*/
function getTaxPercentage($type)
{
global $adb, $log;
$log->debug("Entering into getTaxPercentage($type) function.");
$taxpercentage = '';
$res = $adb->pquery("SELECT percentage FROM vtiger_inventorytaxinfo WHERE taxname = ?", array($type));
$taxpercentage = $adb->query_result($res,0,'percentage');
$log->debug("Exiting from getTaxPercentage($type) function. return value=$taxpercentage");
return $taxpercentage;
}
/** function to get the product's taxpercentage
* @param string $type - tax type (VAT or Sales or Service)
* @param id $productid - productid to which we want the tax percentage
* @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)
* return int $taxpercentage - taxpercentage corresponding to the Tax type from vtiger_inventorytaxinfo vtiger_table
*/
function getProductTaxPercentage($type,$productid,$default='')
{
global $adb, $log, $current_user;
$log->debug("Entering into getProductTaxPercentage($type,$productid) function.");
$res = $adb->pquery("SELECT taxpercentage, vtiger_producttaxrel.regions FROM vtiger_inventorytaxinfo
INNER JOIN vtiger_producttaxrel ON vtiger_inventorytaxinfo.taxid = vtiger_producttaxrel.taxid
WHERE vtiger_producttaxrel.productid = ? AND vtiger_inventorytaxinfo.taxname = ?", array($productid, $type));
$taxpercentage = $adb->query_result($res,0,'taxpercentage');
//This is to retrive the default configured value if the taxpercentage related to product is empty
if($taxpercentage == '' && $default == 'default')
$taxpercentage = getTaxPercentage($type);
$log->debug("Exiting from getProductTaxPercentage($productid,$type) function. return value=$taxpercentage");
if($current_user->truncate_trailing_zeros == true) {
$taxpercentage = decimalFormat($taxpercentage);
}
return array('percentage' => $taxpercentage, 'regions' => Zend_Json::decode(html_entity_decode(!empty($regions) ? $regions : '[]')));
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
}
/** Function used to add the history entry in the relevant tables for PO, SO, Quotes and Invoice modules
* @param string $module - current module name
* @param int $id - entity id
* @param string $relatedname - parent name of the entity ie, required field venor name for PO and account name for SO, Quotes and Invoice
* @param float $total - grand total value of the product details included tax
* @param string $history_fldval - history field value ie., quotestage for Quotes and status for PO, SO and Invoice
*/
function addInventoryHistory($module, $id, $relatedname, $total, $history_fldval)
{
global $log, $adb;
$log->debug("Entering into function addInventoryHistory($module, $id, $relatedname, $total, $history_fieldvalue)");
$history_table_array = Array(
"PurchaseOrder"=>"vtiger_postatushistory",
"SalesOrder"=>"vtiger_sostatushistory",
"Quotes"=>"vtiger_quotestagehistory",
"Invoice"=>"vtiger_invoicestatushistory"
);
$histid = $adb->getUniqueID($history_table_array[$module]);
$modifiedtime = $adb->formatDate(date('Y-m-d H:i:s'), true);
$query = "insert into $history_table_array[$module] values(?,?,?,?,?,?)";
$qparams = array($histid,$id,$relatedname,$total,$history_fldval,$modifiedtime);
$adb->pquery($query, $qparams);
$log->debug("Exit from function addInventoryHistory");
}
/** Function used to get the list of Tax types as a array
* @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
* @param string $sh - sh or empty, if sh passed then the shipping and handling related taxes will be returned
* @param string $mode - edit or empty, if mode is edit, then it will return taxes including desabled.
* @param string $id - crmid or empty, getting crmid to get tax values..
* return array $taxtypes - return all the tax types as a array
*/
function getAllTaxes($available='all', $sh='',$mode='',$id='')
{
global $adb, $log;
$log->debug("Entering into the function getAllTaxes($available,$sh,$mode,$id)");
$taxtypes = Array();
if($sh != '' && $sh == 'sh') {
$tablename = 'vtiger_shippingtaxinfo';
$value_table='vtiger_inventoryshippingrel';
$sql = "SELECT * FROM $tablename WHERE deleted=0";
$result = $adb->pquery($sql, array());
$noofrows=$adb->num_rows($result);
for($i=0; $i<$noofrows; $i++) {
$taxname = $adb->query_result($result,$i,'taxname');
$taxtypes[$i]['taxid'] = $adb->query_result($result,$i,'taxid');
$taxtypes[$i]['taxname'] = $taxname;
$inventory_tax_val_result = $adb->pquery("SELECT $taxname FROM $value_table WHERE id=?",array($id));
$taxtypes[$i]['percentage'] = $adb->query_result($inventory_tax_val_result, 0, $taxname);;
$taxtypes[$i]['taxlabel'] = $adb->query_result($result,$i,'taxlabel');
$taxtypes[$i]['deleted'] = $adb->query_result($result,$i,'deleted');
$taxtypes[$i]['type'] = $adb->query_result($result,$i,'type');
$taxtypes[$i]['method'] = $adb->query_result($result,$i,'method');
$taxtypes[$i]['compoundon'] = $adb->query_result($result,$i,'compoundon');
$taxtypes[$i]['regions'] = $adb->query_result($result,$i,'regions');
$taxtypes[$i]['percentage'] = $adb->query_result($inventory_tax_val_result, 0, $taxname);;
}
} else {
//This where condition is added to get all products or only availble products
if ($available != 'all' && $available == 'available') {
$where = " WHERE $tablename.deleted=0";
}
$result = $adb->pquery("SELECT * FROM $tablename $where ORDER BY deleted", array());
$noofrows = $adb->num_rows($result);
for ($i = 0; $i < $noofrows; $i++) {
$taxtypes[$i]['taxid'] = $adb->query_result($result, $i, 'taxid');
$taxtypes[$i]['taxname'] = $adb->query_result($result, $i, 'taxname');
$taxtypes[$i]['taxlabel'] = $adb->query_result($result, $i, 'taxlabel');
$taxtypes[$i]['percentage'] = $adb->query_result($result, $i, 'percentage');
$taxtypes[$i]['deleted'] = $adb->query_result($result, $i, 'deleted');
$taxtypes[$i]['type'] = $adb->query_result($result, $i, 'type');
$taxtypes[$i]['method'] = $adb->query_result($result, $i, 'method');
$taxtypes[$i]['compoundon'] = $adb->query_result($result, $i, 'compoundon');
$taxtypes[$i]['regions'] = $adb->query_result($result, $i, 'regions');
}
}
} else {
$tablename = 'vtiger_inventorytaxinfo';
$value_table='vtiger_inventoryproductrel';
if($mode == 'edit' && $id != '' ) {
//Getting total no of taxes
$result_ids = array();
$result = $adb->pquery("select taxname,taxid from $tablename", array());
$noofrows = $adb->num_rows($result);
$inventory_tax_val_result = $adb->pquery("select * from $value_table where id=?", array($id));
//Finding which taxes are associated with this (SO,PO,Invoice,Quotes) and getting its taxid.
for ($i = 0; $i < $noofrows; $i++) {
$taxname = $adb->query_result($result, $i, 'taxname');
$taxid = $adb->query_result($result, $i, 'taxid');
$tax_val = $adb->query_result($inventory_tax_val_result, 0, $taxname);
if ($tax_val != '') {
array_push($result_ids, $taxid);
}
}
//We are selecting taxes using that taxids. So It will get the tax even if the tax is disabled.
$where_ids = '';
if (php7_count($result_ids) > 0) {
$insert_str = str_repeat("?,", php7_count($result_ids) - 1);
$insert_str .= "?";
$where_ids = "taxid in ($insert_str) or";
}
$res = $adb->pquery("select * from $tablename where $where_ids deleted=0 order by taxid",$result_ids);
} else {
//This where condition is added to get all products or only availble products
if ($available != 'all' && $available == 'available') {
$where = " where $tablename.deleted=0";
}
$res = $adb->pquery("select * from $tablename $where order by deleted", array());
}
$noofrows = $adb->num_rows($res);
for ($i = 0; $i < $noofrows; $i++) {
$taxtypes[$i]['taxid'] = $adb->query_result($res, $i, 'taxid');
$taxtypes[$i]['taxname'] = $adb->query_result($res, $i, 'taxname');
$taxtypes[$i]['taxlabel'] = $adb->query_result($res, $i, 'taxlabel');
$taxtypes[$i]['percentage'] = $adb->query_result($res, $i, 'percentage');
$taxtypes[$i]['deleted'] = $adb->query_result($res, $i, 'deleted');
$taxtypes[$i]['type'] = $adb->query_result($res, $i, 'type');
$taxtypes[$i]['method'] = $adb->query_result($res, $i, 'method');
$taxtypes[$i]['compoundon'] = $adb->query_result($res, $i, 'compoundon');
$taxtypes[$i]['regions'] = $adb->query_result($res, $i, 'regions');
}
}
$log->debug("Exit from the function getAllTaxes($available,$sh,$mode,$id)");
return $taxtypes;
}
/** Function used to get all the tax details which are associated to the given product
* @param int $productid - product id to which we want to get all the associated taxes
* @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
* @return array $tax_details - tax details as a array with productid, taxid, taxname, percentage and deleted
*/
function getTaxDetailsForProduct($productid, $available='all')
{
global $log, $adb;
$log->debug("Entering into function getTaxDetailsForProduct($productid)");
if($productid != '')
{
//where condition added to avoid to retrieve the non available taxes
$where = '';
if($available != 'all' && $available == 'available')
{
$where = ' and vtiger_inventorytaxinfo.deleted=0';
}
if($available != 'all' && $available == 'available_associated')
{
$query = "SELECT vtiger_producttaxrel.*, vtiger_inventorytaxinfo.*, vtiger_inventorytaxinfo.regions AS taxregions, vtiger_producttaxrel.regions AS productregions FROM vtiger_inventorytaxinfo left JOIN vtiger_producttaxrel ON vtiger_inventorytaxinfo.taxid = vtiger_producttaxrel.taxid WHERE (vtiger_producttaxrel.productid = ? or vtiger_inventorytaxinfo.deleted=0) AND method != 'Deducted' GROUP BY vtiger_inventorytaxinfo.taxid";
$query = "SELECT vtiger_producttaxrel.*, vtiger_inventorytaxinfo.*, vtiger_inventorytaxinfo.regions AS taxregions, vtiger_producttaxrel.regions AS productregions FROM vtiger_inventorytaxinfo INNER JOIN vtiger_producttaxrel ON vtiger_inventorytaxinfo.taxid = vtiger_producttaxrel.taxid WHERE vtiger_producttaxrel.productid = ? AND method != 'Deducted' $where";
}
$params = array($productid);
//Postgres 8 fixes
if( $adb->dbType == "pgsql")
$query = fixPostgresQuery( $query, $log, 0);
$res = $adb->pquery($query, $params);
for($i=0;$i<$adb->num_rows($res);$i++)
{
$tax_details[$i]['productid'] = $adb->query_result($res, $i, 'productid');
$tax_details[$i]['taxid'] = $adb->query_result($res, $i, 'taxid');
$tax_details[$i]['taxname'] = $adb->query_result($res, $i, 'taxname');
$tax_details[$i]['taxlabel'] = $adb->query_result($res, $i, 'taxlabel');
$tax_details[$i]['percentage'] = $adb->query_result($res, $i, 'taxpercentage');
$tax_details[$i]['deleted'] = $adb->query_result($res, $i, 'deleted');
$tax_details[$i]['method'] = $adb->query_result($res, $i, 'method');
$tax_details[$i]['type'] = $adb->query_result($res, $i, 'type');
$tax_details[$i]['regions'] = Zend_Json::decode(html_entity_decode($adb->query_result($res, $i, 'taxregions')));
$tax_details[$i]['compoundon'] = Zend_Json::decode(html_entity_decode($adb->query_result($res, $i, 'compoundon')));
$productregions=$adb->query_result($res, $i, 'productregions');
$tax_details[$i]['productregions']= Zend_Json::decode(html_entity_decode(!empty($productregions) ? $productregions : ''));
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
}
}
else
{
$log->debug("Product id is empty. we cannot retrieve the associated products.");
}
$log->debug("Exit from function getTaxDetailsForProduct($productid)");
return $tax_details;
}
/** Function used to delete the Inventory product details for the passed entity
* @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
* @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
* @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
*/
function deleteInventoryProductDetails($focus)
{
global $log, $adb,$updateInventoryProductRel_update_product_array;
$log->debug("Entering into function deleteInventoryProductDetails(".$focus->id.").");
$product_info = $adb->pquery("SELECT productid, quantity, sequence_no, incrementondel from vtiger_inventoryproductrel WHERE id=?",array($focus->id));
$numrows = $adb->num_rows($product_info);
for($index = 0;$index <$numrows;$index++){
$productid = $adb->query_result($product_info,$index,'productid');
$sequence_no = $adb->query_result($product_info,$index,'sequence_no');
$qty = $adb->query_result($product_info,$index,'quantity');
$incrementondel = $adb->query_result($product_info,$index,'incrementondel');
if($incrementondel){
$focus->update_product_array[$focus->id][$sequence_no][$productid]= $qty;
$subProdQuery = $adb->pquery('SELECT productid, quantity from vtiger_inventorysubproductrel WHERE id=? AND sequence_no=?', array($focus->id,$sequence_no));
if ($adb->num_rows($subProdQuery) > 0) {
for($j=0; $j<$adb->num_rows($subProdQuery); $j++){
$subProdId = $adb->query_result($subProdQuery, $j, 'productid');
$subProdQty = $adb->query_result($subProdQuery, $j, 'quantity');
$focus->update_product_array[$focus->id][$sequence_no][$subProdId] = $subProdQty * $qty;
$focus->update_product_array=isset($focus->update_product_array) ? $focus->update_product_array :''; //to avoid undefined property warning.
$updateInventoryProductRel_update_product_array = $focus->update_product_array;
$adb->pquery("delete from vtiger_inventoryproductrel where id=?", array($focus->id));
$adb->pquery("delete from vtiger_inventorysubproductrel where id=?", array($focus->id));
$adb->pquery("delete from vtiger_inventoryshippingrel where id=?", array($focus->id));
$log->debug("Exit from function deleteInventoryProductDetails(".$focus->id.")");
}
function updateInventoryProductRel($entity) {
global $log, $adb,$updateInventoryProductRel_update_product_array,$updateInventoryProductRel_deduct_stock;
$entity_id = vtws_getIdComponents($entity->getId());
$entity_id = $entity_id[1];
$update_product_array = $updateInventoryProductRel_update_product_array;
$log->debug("Entering into function updateInventoryProductRel(".$entity_id.").");
if(!empty($update_product_array)) {
foreach($update_product_array as $id=>$seq) {
foreach($seq as $seq=>$product_info) {
foreach($product_info as $key=>$index) {
$upd_qty = $updqtyinstk+$index;
updateProductQty($key, $upd_qty);
}
}
}
}
$moduleName = $entity->getModuleName();
if ($moduleName === 'Invoice') {
$statusFieldName = 'invoicestatus';
$statusFieldValue = 'Cancel';
}
$statusChanged = false;
$vtEntityDelta = new VTEntityDelta ();
$oldEntity = $vtEntityDelta->getOldValue($moduleName, $entity_id, $statusFieldName);
$recordDetails = $entity->getData();
$statusChanged = $vtEntityDelta->hasChanged($moduleName, $entity_id, $statusFieldName);
if($recordDetails[$statusFieldName] == $statusFieldValue) {
$adb->pquery("UPDATE vtiger_inventoryproductrel SET incrementondel=0 WHERE id=?",array($entity_id));
$updateInventoryProductRel_deduct_stock = false;
if(empty($update_product_array)) {
addProductsToStock($entity_id);
}
} elseif($oldEntity == $statusFieldValue) {
$updateInventoryProductRel_deduct_stock = false;
deductProductsFromStock($entity_id);
}
} elseif(isset($recordDetails[$statusFieldName]) && $recordDetails[$statusFieldName] == $statusFieldValue) {
$updateInventoryProductRel_deduct_stock = false;
}
if($updateInventoryProductRel_deduct_stock) {
$adb->pquery("UPDATE vtiger_inventoryproductrel SET incrementondel=1 WHERE id=?",array($entity_id));
$product_info = $adb->pquery("SELECT productid,sequence_no, quantity from vtiger_inventoryproductrel WHERE id=?",array($entity_id));
$numrows = $adb->num_rows($product_info);
for($index = 0;$index <$numrows;$index++) {
$productid = $adb->query_result($product_info,$index,'productid');
$qty = $adb->query_result($product_info,$index,'quantity');
$sequence_no = $adb->query_result($product_info,$index,'sequence_no');
$upd_qty = $qtyinstk-$qty;
updateProductQty($productid, $upd_qty);
$sub_prod_query = $adb->pquery("SELECT productid, quantity from vtiger_inventorysubproductrel WHERE id=? AND sequence_no=?",array($entity_id,$sequence_no));
if($adb->num_rows($sub_prod_query)>0) {
for($j=0;$j<$adb->num_rows($sub_prod_query);$j++) {
$sub_prod_id = $adb->query_result($sub_prod_query,$j,"productid");
$subProductQty = $adb->query_result($sub_prod_query, $j, 'quantity');
$sqtyinstk= getProductQtyInStock($sub_prod_id);
$supd_qty = $sqtyinstk-($qty * $subProductQty);
updateProductQty($sub_prod_id, $supd_qty);
}
}
}
$log->debug("Exit from function updateInventoryProductRel(".$entity_id.")");
}
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
if ($moduleName === 'PurchaseOrder') {
$statusFieldName = 'postatus';
$statusFieldValue = $recordDetails[$statusFieldName];
if ($statusFieldValue === 'Cancelled') {
global $itemQuantitiesList;
$statusChanged = false;
$vtEntityDelta = new VTEntityDelta ();
$oldEntity = $vtEntityDelta-> getOldValue($moduleName, $entity_id, $statusFieldName);
$recordDetails = $entity->getData();
$statusChanged = $vtEntityDelta->hasChanged($moduleName, $entity_id, $statusFieldName);
if ($statusChanged && $itemQuantitiesList) {
$db = PearDatabase::getInstance();
$query = "SELECT postvalue FROM vtiger_modtracker_detail
INNER JOIN vtiger_modtracker_basic ON vtiger_modtracker_basic.id = vtiger_modtracker_detail.id
WHERE crmid = ? AND fieldname = ? ORDER BY vtiger_modtracker_detail.id DESC";
$result = $db->pquery($query, array($entity_id, $statusFieldName));
$numOfRows = $db->num_rows($result);
$statusStack = array();
for ($i=0; $i<$numOfRows; $i++) {
$statusStack[$i+1] = $db->query_result($result, $i, 'postvalue');
}
$cancelledKey = array_search('Cancelled', $statusStack);
if (!$cancelledKey) {//First time triggering status as "Cancelled"
$cancelledKey = $numOfRows;
}
for($i=1; $i<=$cancelledKey; $i++) {
if ($statusStack[$i] === 'Received Shipment') {
//Reverting for quantities of line items
$deletedItems = ($itemQuantitiesList['deleted']) ? $itemQuantitiesList['deleted'] : array();
foreach ($itemQuantitiesList as $productId => $quantity) {
$quantity = -$quantity;
addToProductStock($productId, $quantity);
}
break;
}
}
}
} elseif ($statusFieldValue === 'Received Shipment') {
$db = PearDatabase::getInstance();
$query = "SELECT postvalue FROM vtiger_modtracker_detail
INNER JOIN vtiger_modtracker_basic ON vtiger_modtracker_basic.id = vtiger_modtracker_detail.id
WHERE crmid = ? AND fieldname = ? ORDER BY vtiger_modtracker_detail.id DESC";
$result = $db->pquery($query, array($entity_id, $statusFieldName));
$numOfRows = $db->num_rows($result);
$statusStack = array();
for ($i=0; $i<$numOfRows; $i++) {
$statusStack[$i+1] = $db->query_result($result, $i, 'postvalue');
}
$isUpdateNeeded = false;
$recievedKey = array_search('Received Shipment', $statusStack);
if(CRMEntity::isBulkSaveMode() || !$recievedKey) {//From Import or First time triggering status as "Received Shipment"
$isUpdateNeeded = true;
}
if ($recievedKey && !$isUpdateNeeded) {
for($i=1; $i<=$recievedKey; $i++) {
if ($statusStack[$i] === 'Cancelled') {
$isUpdateNeeded = true;
break;
}
}
}
global $itemQuantitiesList, $isItemsRequest;
if ($itemQuantitiesList && ($isUpdateNeeded || $isItemsRequest)) {
//Adding for new line items
$newItems = ($itemQuantitiesList['new']) ? $itemQuantitiesList['new'] : array();
foreach ($newItems as $productId => $quantity) {
addToProductStock($productId, $quantity);
}
//Reverting for deleted line items
$deletedItems = ($itemQuantitiesList['deleted']) ? $itemQuantitiesList['deleted'] : array();
foreach ($deletedItems as $productId => $quantity) {
$quantity = -$quantity;
addToProductStock($productId, $quantity);
}
//Updating for updated line items
$updatedItems = ($itemQuantitiesList['updated']) ? $itemQuantitiesList['updated'] : array();
foreach ($updatedItems as $productId => $quantity) {
addToProductStock($productId, $quantity);
}
} else if ($isUpdateNeeded) {
addProductsToStock($entity_id);
}
}
}
}
/** 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'] !='')
{
}
else if(isset($_REQUEST['duplicate_from']) && $_REQUEST['duplicate_from'] !='')
{
$all_available_taxes = getAllTaxes('available', '', 'edit', $id);
if($focus->mode == 'edit')
{
$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);
}
$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;
for($i=1; $i<=$tot_no_prod; $i++)
{
//if the product is deleted then we should avoid saving the deleted products
if(isset($_REQUEST["deleted".$i]) && $_REQUEST["deleted".$i] == 1)
$prod_id = vtlib_purify($_REQUEST['hdnProductId'.$i]);
//$product ids list will trimmed depending on the max_input_vars of $_POST array. But totalProductCount will be same.
if (!$prod_id) {
continue;
}
$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 = isset($_REQUEST['purchaseCost'.$i]) ? vtlib_purify($_REQUEST['purchaseCost'.$i]) : "";
$margin = isset($_REQUEST['margin'.$i]) ? vtlib_purify($_REQUEST['margin'.$i]) : "";
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, purchase_cost, margin)
VALUES(?,?,?,?,?,?,?,?,?)';
$qparams = array($focus->id,$prod_id,$prod_seq,$qty,$listprice,$comment,$description, $purchaseCost, $margin);
$adb->pquery($query,$qparams);
$lineitem_id = $adb->getLastInsertID();
$sub_prod = explode(',', rtrim($sub_prod_str, ','));
foreach ($sub_prod as $subProductInfo) {
list($subProductId, $subProductQty) = explode(':', $subProductInfo);
$query = 'INSERT INTO vtiger_inventorysubproductrel VALUES(?, ?, ?, ?)';
if (!$subProductQty) {
$subProductQty = 1;
}
$qparams = array($focus->id, $prod_seq, $subProductId, $subProductQty);
$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, vtlib_purify($_REQUEST['discount_percentage'.$i]));
}
elseif($_REQUEST['discount_type'.$i] == 'amount')
{
$updatequery .= " discount_amount=?,";
$discount_amount = vtlib_purify($_REQUEST['discount_amount'.$i]);
$compoundTaxesInfo = getCompoundTaxesInfoForInventoryRecord($focus->id, $module);
for($tax_count=0;$tax_count<php7_count($all_available_taxes);$tax_count++)
$taxDetails = $all_available_taxes[$tax_count];
if ($taxDetails['method'] === 'Deducted') {
continue;
} else if ($taxDetails['method'] === 'Compound') {
$compoundExistingInfo = $compoundTaxesInfo[$taxDetails['taxid']];
if (!is_array($compoundExistingInfo)) {
$compoundExistingInfo = array();
}
$compoundNewInfo = Zend_Json::decode(html_entity_decode($taxDetails['compoundon']));
$compoundFinalInfo = array_merge($compoundExistingInfo, $compoundNewInfo);
$compoundTaxesInfo[$taxDetails['taxid']] = array_unique($compoundFinalInfo);
}
$tax_name = $taxDetails['taxname'];
$tax_val = 0;
if(isset($_REQUEST[$request_tax_name])) {
$tax_val = vtlib_purify($_REQUEST[$request_tax_name]);
}
}
}
else
{
$taxes_for_product = getTaxDetailsForProduct($prod_id,'all');
for($tax_count=0;$tax_count<php7_count($taxes_for_product);$tax_count++)
$taxDetails = $taxes_for_product[$tax_count];
if ($taxDetails['method'] === 'Compound') {
$compoundExistingInfo = $compoundTaxesInfo[$taxDetails['taxid']];
if (!is_array($compoundExistingInfo)) {
$compoundExistingInfo = array();
}
$compoundFinalInfo = array_merge($compoundExistingInfo, $taxDetails['compoundon']);
$compoundTaxesInfo[$taxDetails['taxid']] = array_unique($compoundFinalInfo);
}
$tax_name = $taxDetails['taxname'];
$request_tax_name = $tax_name."_percentage".$i;
$updatequery .= " $tax_name = ?,";
array_push($updateparams, vtlib_purify($_REQUEST[$request_tax_name]));
}
}
for($taxCount=0; $taxCount<php7_count($all_available_taxes); $taxCount++) {
if ($all_available_taxes[$taxCount]['method'] === 'Deducted') {
$taxName = $all_available_taxes[$taxCount]['taxname'];
$requestTaxName = $taxName.'_group_percentage';
$taxValue = 0;
if(isset($_REQUEST[$requestTaxName]) && !empty($_REQUEST[$requestTaxName])) {
$taxValue = vtlib_purify($_REQUEST[$requestTaxName]);
}
$updatequery .= " $taxName = ?,";
array_push($updateparams, (-1 * $taxValue));
}
}
$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();
$updatequery .= " subtotal=?,";
array_push($updateparams, $subtotal);
$pretaxTotal = vtlib_purify($_REQUEST['pre_tax_total']);
$updatequery .= " pre_tax_total=?,";
array_push($updateparams, $pretaxTotal);
$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, null);
}elseif($_REQUEST['discount_type_final'] == 'zero') {
$updatequery .= " discount_amount=?,discount_percent=?,";
array_push($updateparams, null);
array_push($updateparams, null);
}
$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(isset($_REQUEST['adjustmentType']) && $_REQUEST['adjustmentType'] == '-')
$updatequery .= " adjustment=?,";
array_push($updateparams, $adjustmentType.$adjustment);
$updatequery .= " total=?,";
array_push($updateparams, $total);
$updatequery .= ' compound_taxes_info = ?,';
array_push($updateparams, Zend_Json::encode($compoundTaxesInfo));
if (isset($_REQUEST['region_id'])) {
$updatequery .= " region_id = ?,";
array_push($updateparams, vtlib_purify($_REQUEST['region_id']));
}
//to save the S&H tax details in vtiger_inventoryshippingrel table
$adb->pquery('DELETE FROM vtiger_inventorychargesrel WHERE recordid = ?', array($focus->id));
$chargesInfo = array();
if (isset($_REQUEST['charges'])) {
$chargesInfo = $_REQUEST['charges'];
$adb->pquery('INSERT INTO vtiger_inventorychargesrel VALUES (?, ?)', array($focus->id, Zend_Json::encode($chargesInfo)));
array_push($updateparams, vtlib_purify($_REQUEST['s_h_percent']));
//$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 used to get the tax type for the entity (PO, SO, Quotes or Invoice)
* @param string $module - module name
* @param int $id - id of the PO or SO or Quotes or Invoice
* @return string $taxtype - taxtype for the given entity which will be individual or group
*/
function getInventoryTaxType($module, $id)
{
global $log, $adb;
$log->debug("Entering into function getInventoryTaxType($module, $id).");
$inventoryTable = $focus->table_name;
$inventoryId = $focus->table_index;
$res = $adb->pquery("SELECT taxtype FROM $inventoryTable WHERE $inventoryId=?", array($id));
$taxtype = $adb->query_result($res,0,'taxtype');
$log->debug("Exit from function getInventoryTaxType($module, $id).");
return $taxtype;
}
/** function used to get the price type for the entity (PO, SO, Quotes or Invoice)
* @param string $module - module name
* @param int $id - id of the PO or SO or Quotes or Invoice
* @return string $pricetype - pricetype for the given entity which will be unitprice or secondprice
*/
function getInventoryCurrencyInfo($module, $id)
{
global $log, $adb;
$log->debug("Entering into function getInventoryCurrencyInfo($module, $id).");
$inventory_table = $focus->table_name;
$inventory_id = $focus->table_index;
$res = $adb->pquery("select currency_id, $inventory_table.conversion_rate as conv_rate, vtiger_currency_info.* from $inventory_table
inner join vtiger_currency_info on $inventory_table.currency_id = vtiger_currency_info.id
where $inventory_id=?", array($id));
$currency_info = array();
$currency_info['currency_id'] = $adb->query_result($res,0,'currency_id');
$currency_info['conversion_rate'] = $adb->query_result($res,0,'conv_rate');
$currency_info['currency_name'] = $adb->query_result($res,0,'currency_name');
$currency_info['currency_code'] = $adb->query_result($res,0,'currency_code');
$currency_info['currency_symbol'] = $adb->query_result($res,0,'currency_symbol');
$log->debug("Exit from function getInventoryCurrencyInfo($module, $id).");
return $currency_info;
}
/** function used to get the taxvalue which is associated with a product for PO/SO/Quotes or Invoice
* @param int $id - id of PO/SO/Quotes or Invoice
* @param int $productid - product id
* @param string $taxname - taxname to which we want the value
* @return float $taxvalue - tax value
*/
function getInventoryProductTaxValue($id, $productId, $taxName, $lineItemId = 0)
$log->debug("Entering into function getInventoryProductTaxValue($id, $productId, $taxName, $lineItemId).");
$taxName = Vtiger_Util_Helper::validateStringForSql($taxName);
$query = "SELECT $taxName FROM vtiger_inventoryproductrel WHERE id = ? AND productid = ?";
$params = array($id, $productId);
if ($lineItemId) {
$query .= ' AND lineitem_id = ?';
$params[] = $lineItemId;
}
$res = $adb->pquery($query, $params);
$taxvalue = $adb->query_result($res, 0, $taxName);
$log->debug("Exit from function getInventoryProductTaxValue($id, $productId, $taxName, $lineItemId).");
return $taxvalue;
}
/** function used to get the shipping & handling tax percentage for the given inventory id and taxname
* @param int $id - entity id which will be PO/SO/Quotes or Invoice id
* @param string $taxname - shipping and handling taxname
* @return float $taxpercentage - shipping and handling taxpercentage which is associated with the given entity
*/
madhu sr
committed
function getInventorySHTaxPercent($id, $taxname, $taxnum=null)
{
global $log, $adb;
$log->debug("Entering into function getInventorySHTaxPercent($id, $taxname)");
$res = $adb->pquery("select $taxname from vtiger_inventoryshippingrel where id= ?", array($id));
$taxpercentage = $adb->query_result($res,0,$taxname);
// If shipping details is not found then try to get the values from the vtiger_inventorychargesrel
// where the actual shipping and handling tax info of particular record stored.
madhu sr
committed
if($adb->num_rows($res) < 1){
$j=$taxnum+1;
// parse through the json detail and extract the value of specific tax.
madhu sr
committed
$charges_result = $adb->pquery(
"SELECT JSON_UNQUOTE(JSON_EXTRACT(charges, CONCAT('$.\"1\".taxes.\"', ? ,'\"'))) as charges
FROM vtiger_inventorychargesrel
WHERE recordid = ?",
madhu sr
committed
);
madhu sr
committed
$rowData = $adb->fetch_array($charges_result);
$charges = isset($rowData['charges']) ? Zend_Json::decode(html_entity_decode($rowData['charges'])):"";
madhu sr
committed
$taxpercentage = $charges;
}