/** 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--") {
