/** Function used to get the Quote Stage history of the Quotes * @param $id - quote id * @return $return_data - array with header and the entries in format Array('header'=>$header,'entries'=>$entries_list) where as $header and $entries_list are arrays which contains header values and all column values of all entries */ function get_quotestagehistory($id) { global $log; $log->debug("Entering get_quotestagehistory(" . $id . ") method ..."); global $adb; global $mod_strings; global $app_strings; $query = 'select vtiger_quotestagehistory.*, vtiger_quotes.quote_no from vtiger_quotestagehistory inner join vtiger_quotes on vtiger_quotes.quoteid = vtiger_quotestagehistory.quoteid inner join vtiger_crmentity on vtiger_crmentity.crmid = vtiger_quotes.quoteid where vtiger_crmentity.deleted = 0 and vtiger_quotes.quoteid = ?'; $result = $adb->pquery($query, array($id)); $noofrows = $adb->num_rows($result); $header[] = $app_strings['Quote No']; $header[] = $app_strings['LBL_ACCOUNT_NAME']; $header[] = $app_strings['LBL_AMOUNT']; $header[] = $app_strings['Quote Stage']; $header[] = $app_strings['LBL_LAST_MODIFIED']; //Getting the field permission for the current user. 1 - Not Accessible, 0 - Accessible //Account Name , Total are mandatory fields. So no need to do security check to these fields. global $current_user; //If field is accessible then getFieldVisibilityPermission function will return 0 else return 1 $quotestage_access = getFieldVisibilityPermission('Quotes', $current_user->id, 'quotestage') != '0' ? 1 : 0; $picklistarray = getAccessPickListValues('Quotes'); $quotestage_array = $quotestage_access != 1 ? $picklistarray['quotestage'] : array(); //- ==> picklist field is not permitted in profile //Not Accessible - picklist is permitted in profile but picklist value is not permitted $error_msg = $quotestage_access != 1 ? 'Not Accessible' : '-'; while ($row = $adb->fetch_array($result)) { $entries = array(); // Module Sequence Numbering //$entries[] = $row['quoteid']; $entries[] = $row['quote_no']; // END $entries[] = $row['accountname']; $entries[] = $row['total']; $entries[] = in_array($row['quotestage'], $quotestage_array) ? $row['quotestage'] : $error_msg; $entries[] = getDisplayDate($row['lastmodified']); $entries_list[] = $entries; } $return_data = array('header' => $header, 'entries' => $entries_list); $log->debug("Exiting get_quotestagehistory method ..."); return $return_data; }
/** Function used to get the Status history of the Invoice * @param $id - invoice id * @return $return_data - array with header and the entries in format Array('header'=>$header,'entries'=>$entries_list) where as $header and $entries_list are arrays which contains header values and all column values of all entries */ function get_invoicestatushistory($id) { $log = vglobal('log'); $log->debug("Entering get_invoicestatushistory(" . $id . ") method ..."); $adb = PearDatabase::getInstance(); global $mod_strings; global $app_strings; $query = 'select vtiger_invoicestatushistory.*, vtiger_invoice.invoice_no from vtiger_invoicestatushistory inner join vtiger_invoice on vtiger_invoice.invoiceid = vtiger_invoicestatushistory.invoiceid inner join vtiger_crmentity on vtiger_crmentity.crmid = vtiger_invoice.invoiceid where vtiger_crmentity.deleted = 0 and vtiger_invoice.invoiceid = ?'; $result = $adb->pquery($query, array($id)); $noofrows = $adb->num_rows($result); $header[] = $app_strings['Invoice No']; $header[] = $app_strings['LBL_ACCOUNT_NAME']; $header[] = $app_strings['LBL_AMOUNT']; $header[] = $app_strings['LBL_INVOICE_STATUS']; $header[] = $app_strings['LBL_LAST_MODIFIED']; //Getting the field permission for the current user. 1 - Not Accessible, 0 - Accessible //Account Name , Amount are mandatory fields. So no need to do security check to these fields. $current_user = vglobal('current_user'); //If field is accessible then getFieldVisibilityPermission function will return 0 else return 1 $invoicestatus_access = getFieldVisibilityPermission('Invoice', $current_user->id, 'invoicestatus') != '0' ? 1 : 0; $picklistarray = getAccessPickListValues('Invoice'); $invoicestatus_array = $invoicestatus_access != 1 ? $picklistarray['invoicestatus'] : array(); //- ==> picklist field is not permitted in profile //Not Accessible - picklist is permitted in profile but picklist value is not permitted $error_msg = $invoicestatus_access != 1 ? 'Not Accessible' : '-'; while ($row = $adb->fetch_array($result)) { $entries = array(); // Module Sequence Numbering //$entries[] = $row['invoiceid']; $entries[] = $row['invoice_no']; // END $entries[] = $row['accountname']; $entries[] = $row['total']; $entries[] = in_array($row['invoicestatus'], $invoicestatus_array) ? $row['invoicestatus'] : $error_msg; $entries[] = DateTimeField::convertToUserFormat($row['lastmodified']); $entries_list[] = $entries; } $return_data = array('header' => $header, 'entries' => $entries_list); $log->debug("Exiting get_invoicestatushistory method ..."); return $return_data; }
/** Function used to get the Sales Stage history of the Potential * @param $id - potentialid * return $return_data - array with header and the entries in format Array('header'=>$header,'entries'=>$entries_list) where as $header and $entries_list are array which contains all the column values of an row */ function get_stage_history($id) { $log = vglobal('log'); $log->debug("Entering get_stage_history(" . $id . ") method ..."); $adb = PearDatabase::getInstance(); global $mod_strings; global $app_strings; $query = 'select vtiger_potstagehistory.*, vtiger_potential.potentialname from vtiger_potstagehistory inner join vtiger_potential on vtiger_potential.potentialid = vtiger_potstagehistory.potentialid inner join vtiger_crmentity on vtiger_crmentity.crmid = vtiger_potential.potentialid where vtiger_crmentity.deleted = 0 and vtiger_potential.potentialid = ?'; $result = $adb->pquery($query, array($id)); $noofrows = $adb->num_rows($result); $header[] = $app_strings['LBL_AMOUNT']; $header[] = $app_strings['LBL_SALES_STAGE']; $header[] = $app_strings['LBL_PROBABILITY']; $header[] = $app_strings['LBL_CLOSE_DATE']; $header[] = $app_strings['LBL_LAST_MODIFIED']; //Getting the field permission for the current user. 1 - Not Accessible, 0 - Accessible //Sales Stage, Expected Close Dates are mandatory fields. So no need to do security check to these fields. $current_user = vglobal('current_user'); //If field is accessible then getFieldVisibilityPermission function will return 0 else return 1 $amount_access = getFieldVisibilityPermission('Potentials', $current_user->id, 'sum_invoices') != '0' ? 1 : 0; $probability_access = getFieldVisibilityPermission('Potentials', $current_user->id, 'probability') != '0' ? 1 : 0; $picklistarray = getAccessPickListValues('Potentials'); $potential_stage_array = $picklistarray['sales_stage']; //- ==> picklist field is not permitted in profile //Not Accessible - picklist is permitted in profile but picklist value is not permitted $error_msg = 'Not Accessible'; while ($row = $adb->fetch_array($result)) { $entries = array(); $entries[] = $amount_access != 1 ? $row['sum_invoices'] : 0; $entries[] = in_array($row['stage'], $potential_stage_array) ? $row['stage'] : $error_msg; $entries[] = $probability_access != 1 ? $row['probability'] : 0; $entries[] = DateTimeField::convertToUserFormat($row['closedate']); $date = new DateTimeField($row['lastmodified']); $entries[] = $date->getDisplayDate(); $entries_list[] = $entries; } $return_data = array('header' => $header, 'entries' => $entries_list); $log->debug("Exiting get_stage_history method ..."); return $return_data; }
{ global $labels_length; $labels_length[$clave] = strlen($label); } array_walk($labels_length, 'strlength'); array_multisort($labels_length, $field_label, $querycolumns); $field_label = array_reverse($field_label); $querycolumns = array_reverse($querycolumns); $labels_length = array_reverse($labels_length); $csvheader = implode(",", $field_label); //<<<<<<<<<<<<<<<<End>>>>>>>>>>>>>>>>>>>>>>>> if (count($querycolumns) > 0) { $selectcolumns = implode($querycolumns, ","); $query = "select " . $selectcolumns . " from vtiger_troubletickets\n\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_troubletickets.ticketid\n\t\t\tinner join vtiger_ticketcf on vtiger_ticketcf.ticketid = vtiger_troubletickets.ticketid\n\t\t\tleft join vtiger_crmentity as crmentityRelHelpDesk on crmentityRelHelpDesk.crmid = vtiger_troubletickets.parent_id\n\t\t\tleft join vtiger_account as accountRelHelpDesk on accountRelHelpDesk.accountid=crmentityRelHelpDesk.crmid\n\t\t\tleft join vtiger_contactdetails as contactdetailsRelHelpDesk on contactdetailsRelHelpDesk.contactid= crmentityRelHelpDesk.crmid\n\t\t\tleft join vtiger_products as productsRel on productsRel.productid = vtiger_troubletickets.product_id\n\t\t\tleft join vtiger_users on vtiger_crmentity.smownerid=vtiger_users.id\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tleft join vtiger_account on vtiger_account.accountid = vtiger_troubletickets.parent_id \n\t\t\tleft join vtiger_crmentity as crmentityAccounts on crmentityAccounts.crmid = vtiger_account.accountid \n\t\t\tleft join vtiger_accountbillads on vtiger_accountbillads.accountaddressid = vtiger_account.accountid\n\t\t\tleft join vtiger_accountshipads on vtiger_accountshipads.accountaddressid = vtiger_account.accountid\n\t\t\tleft join vtiger_accountscf on vtiger_accountbillads.accountaddressid = vtiger_accountscf.accountid \n\t\t\tleft join vtiger_account as accountAccount on accountAccount.accountid = vtiger_troubletickets.parent_id\n\t\t\tleft join vtiger_users as usersAccounts on usersAccounts.id = crmentityAccounts.smownerid\n\t\t\tLEFT JOIN vtiger_groups as groupsAccounts\n\t\t\t\tON groupsAccounts.groupid = vtiger_crmentity.smownerid\n\t\t\tleft join vtiger_contactdetails on vtiger_contactdetails.contactid = vtiger_troubletickets.parent_id \n\t\t\tleft join vtiger_crmentity as crmentityContacts on crmentityContacts.crmid = vtiger_contactdetails.contactid \n\t\t\tleft join vtiger_contactaddress on vtiger_contactdetails.contactid = vtiger_contactaddress.contactaddressid \n\t\t\tleft join vtiger_contactsubdetails on vtiger_contactdetails.contactid = vtiger_contactsubdetails.contactsubscriptionid \n\t\t\tleft join vtiger_contactscf on vtiger_contactdetails.contactid = vtiger_contactscf.contactid \n\t\t\tleft join vtiger_customerdetails on vtiger_contactdetails.contactid = vtiger_customerdetails.customerid \n\t\t\tleft join vtiger_contactdetails as contactdetailsContacts on contactdetailsContacts.contactid = vtiger_contactdetails.reportsto\n\t\t\tleft join vtiger_account as accountContacts on accountContacts.accountid = vtiger_contactdetails.accountid \n\t\t\tleft join vtiger_users as usersContacts on usersContacts.id = crmentityContacts.smownerid\n\t\t\tLEFT JOIN vtiger_groups as groupsContacts\n\t\t\t\tON groupsContacts.groupid = vtiger_crmentity.smownerid\n\t\t\twhere vtiger_crmentity.deleted=0 and ((crmentityContacts.deleted=0 || crmentityContacts.deleted is null)||(crmentityAccounts.deleted=0 || crmentityAccounts.deleted is null)) \n\t\t\tand vtiger_troubletickets.ticketid in (" . generateQuestionMarks($mass_merge) . ")"; $result = $adb->pquery($query, array($mass_merge)); $avail_pick_arr = getAccessPickListValues('HelpDesk'); while ($columnValues = $adb->fetch_array($result)) { $y = $adb->num_fields($result); for ($x = 0; $x < $y; $x++) { $value = $columnValues[$x]; foreach ($columnValues as $key => $val) { if ($val == $value && $value != '') { if (array_key_exists($key, $avail_pick_arr)) { if (!in_array($val, $avail_pick_arr[$key])) { $value = "Not Accessible"; } } } } //<<<<<<<<<<<<<<<for blank vtiger_fields>>>>>>>>>>>>>>>>>>>>>>>>>>>> if (trim($value) == "--None--" || trim($value) == "--none--") {
$labels_length[$clave] = strlen($label); } array_walk($labels_length, 'strlength'); array_multisort($labels_length, $field_label, $querycolumns); $field_label = array_reverse($field_label); $querycolumns = array_reverse($querycolumns); $labels_length = array_reverse($labels_length); $csvheader = implode(",", $field_label); //<<<<<<<<<<<<<<<<End>>>>>>>>>>>>>>>>>>>>>>>> if (count($querycolumns) > 0) { $selectcolumns = implode($querycolumns, ","); $query = "select {$selectcolumns} from vtiger_account \n\t\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_account.accountid \n\t\t\t\tinner join vtiger_accountbillads on vtiger_account.accountid=vtiger_accountbillads.accountaddressid \n\t\t\t\tinner join vtiger_accountshipads on vtiger_account.accountid=vtiger_accountshipads.accountaddressid \n\t\t\t\tinner join vtiger_accountscf on vtiger_account.accountid = vtiger_accountscf.accountid \n\t\t\t\tleft join vtiger_account as vtiger_accountAccount on vtiger_accountAccount.accountid = vtiger_account.parentid\n\t\t\t\tleft join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\t\tLEFT JOIN vtiger_groups \n\t\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\t\tleft join vtiger_contactdetails on vtiger_contactdetails.accountid=vtiger_account.accountid\n\t\t\t\tleft join vtiger_crmentity as vtiger_crmentityContacts on vtiger_crmentityContacts.crmid = vtiger_contactdetails.contactid \n\t\t\t\tleft join vtiger_contactaddress on vtiger_contactdetails.contactid = vtiger_contactaddress.contactaddressid \n\t\t\t\tleft join vtiger_contactsubdetails on vtiger_contactdetails.contactid = vtiger_contactsubdetails.contactsubscriptionid \n\t\t\t\tleft join vtiger_contactscf on vtiger_contactdetails.contactid = vtiger_contactscf.contactid \n\t\t\t\tleft join vtiger_customerdetails on vtiger_contactdetails.contactid = vtiger_customerdetails.customerid \n\t\t\t\tleft join vtiger_contactdetails as vtiger_contactdetailsContacts on vtiger_contactdetailsContacts.contactid = vtiger_contactdetails.reportsto\n\t\t\t\tleft join vtiger_account as vtiger_accountContacts on vtiger_accountContacts.accountid = vtiger_contactdetails.accountid \n\t\t\t\tleft join vtiger_users as usersContacts on usersContacts.id = vtiger_crmentityContacts.smownerid\n\t\t\t\tLEFT JOIN vtiger_groups as groupsContacts\n\t\t\t\t\tON groupsContacts.groupid = vtiger_crmentity.smownerid\n\t\t\t\twhere vtiger_crmentity.deleted=0 and (vtiger_crmentityContacts.deleted=0 || vtiger_crmentityContacts.deleted is null) and vtiger_account.accountid in(" . generateQuestionMarks($mass_merge) . ")"; //echo $query; //die; $result = $adb->pquery($query, array($mass_merge)); $avail_pick_arr = getAccessPickListValues('Accounts'); while ($columnValues = $adb->fetch_array($result)) { $y = $adb->num_fields($result); for ($x = 0; $x < $y; $x++) { $value = $columnValues[$x]; foreach ($columnValues as $key => $val) { if ($val == $value && $value != '') { if (array_key_exists($key, $avail_pick_arr)) { if (!in_array($val, $avail_pick_arr[$key])) { $value = "Not Accessible"; } } } } //<<<<<<<<<<<<<<<for modifing default values>>>>>>>>>>>>>>>>>>>>>>>>>>>> if (trim($value) == "--None--" || trim($value) == "--none--") {
} if ($modulename == "Contacts") { $field_label[$x] = "CONTACT_" . strtoupper(str_replace(" ", "", $adb->query_result($result, $x, "fieldlabel"))); if ($columnname == "smownerid") { $field_label[$x] = $field_label[$x] . ",USER_FIRSTNAME,USER_LASTNAME,USER_USERNAME,USER_YAHOOID,USER_TITLE,USER_OFFICEPHONE,USER_DEPARTMENT,USER_MOBILE,USER_OTHERPHONE,USER_FAX,USER_EMAIL,USER_HOMEPHONE,USER_OTHEREMAIL,USER_PRIMARYADDRESS,USER_CITY,USER_STATE,USER_POSTALCODE,USER_COUNTRY"; } } } $csvheader = implode(",", $field_label); //echo $csvheader; //<<<<<<<<<<<<<<<<End>>>>>>>>>>>>>>>>>>>>>>>> if (count($querycolumns) > 0) { $selectcolumns = implode($querycolumns, ","); $query = "select {$selectcolumns} from vtiger_contactdetails\n\t\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid = vtiger_contactdetails.contactid \n\t\t\t\tinner join vtiger_contactaddress on vtiger_contactdetails.contactid = vtiger_contactaddress.contactaddressid \n\t\t\t\tinner join vtiger_contactsubdetails on vtiger_contactdetails.contactid = vtiger_contactsubdetails.contactsubscriptionid \n\t\t\t\tinner join vtiger_contactscf on vtiger_contactdetails.contactid = vtiger_contactscf.contactid \n\t\t\t\tinner join vtiger_customerdetails on vtiger_contactdetails.contactid = vtiger_customerdetails.customerid \n\t\t\t\tleft join vtiger_contactdetails as contactdetailsContacts on contactdetailsContacts.contactid = vtiger_contactdetails.reportsto\n\t\t\t\tleft join vtiger_account as accountContacts on accountContacts.accountid = vtiger_contactdetails.accountid \n\t\t\t\tleft join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\t\tLEFT JOIN vtiger_groups \n\t\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\t\tleft join vtiger_account on vtiger_account.accountid = vtiger_contactdetails.accountid\n\t\t\t\tleft join vtiger_crmentity as crmentityAccounts on crmentityAccounts.crmid=vtiger_account.accountid\n\t\t\t\tleft join vtiger_accountbillads on vtiger_account.accountid=vtiger_accountbillads.accountaddressid\n\t\t\t\tleft join vtiger_accountshipads on vtiger_account.accountid=vtiger_accountshipads.accountaddressid\n\t\t\t\tleft join vtiger_accountscf on vtiger_account.accountid = vtiger_accountscf.accountid\n\t\t\t\tleft join vtiger_account as accountAccounts on accountAccounts.accountid = vtiger_account.parentid\n\t\t\t\tleft join vtiger_users as usersAccounts on usersAccounts.id = crmentityAccounts.smownerid \n\t\t\t\tLEFT JOIN vtiger_groups as groupsAccounts\n\t\t\t\t\tON groupsAccounts.groupid = vtiger_crmentity.smownerid\n\t\t\t\twhere vtiger_crmentity.deleted=0 and (crmentityAccounts.deleted is NULL or crmentityAccounts.deleted <> 1) and vtiger_contactdetails.contactid in(" . generateQuestionMarks($mass_merge) . ")"; $result = $adb->pquery($query, array($mass_merge)); $avail_pick_arr = getAccessPickListValues('Contacts'); while ($columnValues = $adb->fetch_array($result)) { $y = $adb->num_fields($result); for ($x = 0; $x < $y; $x++) { $value = $columnValues[$x]; foreach ($columnValues as $key => $val) { if ($val == $value && $value != '') { if (array_key_exists($key, $avail_pick_arr)) { if (!in_array($val, $avail_pick_arr[$key])) { $value = "Not Accessible"; } } } } //<<<<<<<<<<<<<<< For blank Fields >>>>>>>>>>>>>>>>>>>>>>>>>>>> if (trim($value) == "--None--" || trim($value) == "--none--") {
$querycolumns[$x] = $tablename . "." . $columnname; if ($columnname == "smownerid") { $querycolumns[$x] = "case when (vtiger_users.user_name not like '') then concat(vtiger_users.last_name,' ',vtiger_users.first_name) else vtiger_groups.groupname end as username,vtiger_users.first_name,vtiger_users.last_name,vtiger_users.user_name,vtiger_users.yahoo_id,vtiger_users.title,vtiger_users.phone_work,vtiger_users.department,vtiger_users.phone_mobile,vtiger_users.phone_other,vtiger_users.phone_fax,vtiger_users.email1,vtiger_users.phone_home,vtiger_users.email2,vtiger_users.address_street,vtiger_users.address_city,vtiger_users.address_state,vtiger_users.address_postalcode,vtiger_users.address_country"; } $field_label[$x] = "LEAD_" . strtoupper(str_replace(" ", "", $adb->query_result($result, $x, "fieldlabel"))); if ($columnname == "smownerid") { $field_label[$x] = $field_label[$x] . ",USER_FIRSTNAME,USER_LASTNAME,USER_USERNAME,USER_YAHOOID,USER_TITLE,USER_OFFICEPHONE,USER_DEPARTMENT,USER_MOBILE,USER_OTHERPHONE,USER_FAX,USER_EMAIL,USER_HOMEPHONE,USER_OTHEREMAIL,USER_PRIMARYADDRESS,USER_CITY,USER_STATE,USER_POSTALCODE,USER_COUNTRY"; } } $csvheader = implode(",", $field_label); //<<<<<<<<<<<<<<<<End>>>>>>>>>>>>>>>>>>>>>>>> if (count($querycolumns) > 0) { $selectcolumns = implode($querycolumns, ","); $query = "select " . $selectcolumns . " from vtiger_leaddetails \n inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_leaddetails.leadid \n inner join vtiger_leadsubdetails on vtiger_leadsubdetails.leadsubscriptionid=vtiger_leaddetails.leadid \n inner join vtiger_leadaddress on vtiger_leadaddress.leadaddressid=vtiger_leadsubdetails.leadsubscriptionid \n inner join vtiger_leadscf on vtiger_leaddetails.leadid = vtiger_leadscf.leadid \n left join vtiger_campaignleadrel on vtiger_leaddetails.leadid = vtiger_campaignleadrel.leadid\n left join vtiger_campaignrelstatus on vtiger_campaignrelstatus.campaignrelstatusid = vtiger_campaignleadrel.campaignrelstatusid\n LEFT JOIN vtiger_groups\n \tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid\n where vtiger_crmentity.deleted=0 and vtiger_leaddetails.leadid in (" . generateQuestionMarks($mass_merge) . ")"; $result = $adb->pquery($query, array($mass_merge)); $avail_pick_arr = getAccessPickListValues('Leads'); while ($columnValues = $adb->fetch_array($result)) { $y = $adb->num_fields($result); for ($x = 0; $x < $y; $x++) { $value = $columnValues[$x]; foreach ($columnValues as $key => $val) { if ($val == $value && $value != '') { if (array_key_exists($key, $avail_pick_arr)) { if (!in_array($val, $avail_pick_arr[$key])) { $value = "Not Accessible"; } } } } //<<<<<<<<<<<<<<< For Blank Fields >>>>>>>>>>>>>>>>>>>>>>>>>>>> if (trim($value) == "--None--" || trim($value) == "--none--") {