public function addPickListValues($fieldModel, $newValue, $rolesSelected = array())
{
$db = PearDatabase::getInstance();
$pickListFieldName = $fieldModel->getName();
$id = $db->getUniqueID("vtiger_{$pickListFieldName}");
vimport('~~/include/ComboUtil.php');
$picklist_valueid = getUniquePicklistID();
$tableName = 'vtiger_' . $pickListFieldName;
$maxSeqQuery = 'SELECT max(sortorderid) as maxsequence FROM ' . $tableName;
$result = $db->pquery($maxSeqQuery, array());
$sequence = $db->query_result($result, 0, 'maxsequence');
if ($fieldModel->isRoleBased()) {
$sql = 'INSERT INTO ' . $tableName . ' VALUES (?,?,?,?,?)';
$db->pquery($sql, array($id, $newValue, 1, $picklist_valueid, ++$sequence));
} else {
$sql = 'INSERT INTO ' . $tableName . ' VALUES (?,?,?,?)';
$db->pquery($sql, array($id, $newValue, ++$sequence, 1));
}
if ($fieldModel->isRoleBased() && !empty($rolesSelected)) {
$sql = "select picklistid from vtiger_picklist where name=?";
$result = $db->pquery($sql, array($pickListFieldName));
$picklistid = $db->query_result($result, 0, "picklistid");
//add the picklist values to the selected roles
for ($j = 0; $j < count($rolesSelected); $j++) {
$roleid = $rolesSelected[$j];
$sql = "SELECT max(sortid)+1 as sortid\n FROM vtiger_role2picklist left join vtiger_{$pickListFieldName}\n on vtiger_{$pickListFieldName}.picklist_valueid=vtiger_role2picklist.picklistvalueid\n WHERE roleid=? and picklistid=?";
$sortid = $db->query_result($db->pquery($sql, array($roleid, $picklistid)), 0, 'sortid');
$sql = "insert into vtiger_role2picklist values(?,?,?,?)";
$db->pquery($sql, array($roleid, $picklist_valueid, $picklistid, $sortid));
}
}
return $picklist_valueid;
}
/**
* To populate the default combo values for the combo vtiger_tables
* @param $values -- values:: Type string array
* @param $tableName -- tablename:: Type string
*/
function insertComboValues($values, $tableName, $picklistid)
{
global $log;
$log->debug("Entering insertComboValues(" . $values . ", " . $tableName . ") method ...");
global $adb;
//inserting the value in the vtiger_picklistvalues_seq for the getting uniqueID for each picklist values...
$i = 0;
foreach ($values as $val => $cal) {
$picklist_valueid = getUniquePicklistID();
$id = $adb->getUniqueID('vtiger_' . $tableName);
if ($val != '') {
$params = array($id, $val, 1, $picklist_valueid);
$adb->pquery("insert into vtiger_{$tableName} values(?,?,?,?)", $params);
} else {
$params = array($id, '--None--', 1, $picklist_valueid);
$adb->pquery("insert into vtiger_{$tableName} values(?,?,?,?)", $params);
}
//Default entries for role2picklist relation has been inserted..
$sql = "select roleid from vtiger_role";
$role_result = $adb->pquery($sql, array());
$numrow = $adb->num_rows($role_result);
for ($k = 0; $k < $numrow; $k++) {
$roleid = $adb->query_result($role_result, $k, 'roleid');
$params = array($roleid, $picklist_valueid, $picklistid, $i);
$adb->pquery("insert into vtiger_role2picklist values(?,?,?,?)", $params);
}
$i++;
}
$log->debug("Exiting insertComboValues method ...");
}
/**
* Set values for picklist field (for all the roles)
* @param Array List of values to add.
*
* @internal Creates picklist base if it does not exists
*/
function setPicklistValues($values)
{
global $adb;
// Non-Role based picklist values
if ($this->uitype == '16') {
$this->setNoRolePicklistValues($values);
return;
}
$picklist_table = 'vtiger_' . $this->name;
$picklist_idcol = $this->name . 'id';
if (!Vtiger_Utils::CheckTable($picklist_table)) {
Vtiger_Utils::CreateTable($picklist_table, "({$picklist_idcol} INT NOT NULL AUTO_INCREMENT PRIMARY KEY,\n\t\t\t\t{$this->name} VARCHAR(200) NOT NULL,\n\t\t\t\tpresence INT (1) NOT NULL DEFAULT 1,\n\t\t\t\tpicklist_valueid INT NOT NULL DEFAULT 0)", true);
$new_picklistid = $this->__getPicklistUniqueId();
$adb->pquery("INSERT INTO vtiger_picklist (picklistid,name) VALUES(?,?)", array($new_picklistid, $this->name));
self::log("Creating table {$picklist_table} ... DONE");
} else {
$new_picklistid = $adb->query_result($adb->pquery("SELECT picklistid FROM vtiger_picklist WHERE name=?", array($this->name)), 0, 'picklistid');
}
// Add value to picklist now
$sortid = 0;
// TODO To be set per role
foreach ($values as $value) {
$new_picklistvalueid = getUniquePicklistID();
$presence = 1;
// 0 - readonly, Refer function in include/ComboUtil.php
$new_id = $adb->getUniqueID($picklist_table);
$adb->pquery("INSERT INTO {$picklist_table}({$picklist_idcol}, {$this->name}, presence, picklist_valueid) VALUES(?,?,?,?)", array($new_id, $value, $presence, $new_picklistvalueid));
++$sortid;
// Associate picklist values to all the role
$adb->query("INSERT INTO vtiger_role2picklist(roleid, picklistvalueid, picklistid, sortid) SELECT roleid, \n\t\t\t\t{$new_picklistvalueid}, {$new_picklistid}, {$sortid} FROM vtiger_role");
}
}
/**
* Set values for picklist field (for all the roles)
* @param Array List of values to add.
*
* @internal Creates picklist base if it does not exists
*/
function setPicklistValues($values)
{
global $adb, $default_charset;
// Non-Role based picklist values
if ($this->uitype == '16') {
$this->setNoRolePicklistValues($values);
return;
}
$picklist_table = 'vtiger_' . $this->name;
$picklist_idcol = $this->name . 'id';
if (!Vtiger_Utils::CheckTable($picklist_table)) {
Vtiger_Utils::CreateTable($picklist_table, "({$picklist_idcol} INT NOT NULL AUTO_INCREMENT PRIMARY KEY,\n\t\t\t\t{$this->name} VARCHAR(200) NOT NULL,\n\t\t\t\tpresence INT (1) NOT NULL DEFAULT 1,\n\t\t\t\tpicklist_valueid INT NOT NULL DEFAULT 0,\n sortorderid INT DEFAULT 0)", true);
$new_picklistid = $this->__getPicklistUniqueId();
$adb->pquery("INSERT INTO vtiger_picklist (picklistid,name) VALUES(?,?)", array($new_picklistid, $this->name));
self::log("Creating table {$picklist_table} ... DONE");
} else {
$picklistResult = $adb->pquery("SELECT picklistid FROM vtiger_picklist WHERE name=?", array($this->name));
$new_picklistid = $adb->query_result($picklistResult, 0, 'picklistid');
}
$specialNameSpacedPicklists = array('opportunity_type' => 'opptypeid', 'duration_minutes' => 'minutesid', 'recurringtype' => 'recurringeventid');
// Fix Table ID column names
$fieldName = (string) $this->name;
if (in_array($fieldName . '_id', $adb->getColumnNames($picklist_table))) {
$picklist_idcol = $fieldName . '_id';
} elseif (array_key_exists($fieldName, $specialNameSpacedPicklists)) {
$picklist_idcol = $specialNameSpacedPicklists[$fieldName];
}
// END
// Add value to picklist now
$picklistValues = self::getPicklistValues();
$sortid = 0;
// TODO To be set per role
foreach ($values as $value) {
if (in_array($value, $picklistValues)) {
continue;
}
$new_picklistvalueid = getUniquePicklistID();
$presence = 1;
// 0 - readonly, Refer function in include/ComboUtil.php
$new_id = $adb->getUniqueID($picklist_table);
++$sortid;
$adb->pquery("INSERT INTO {$picklist_table}({$picklist_idcol}, {$this->name}, presence, picklist_valueid,sortorderid) VALUES(?,?,?,?,?)", array($new_id, $value, $presence, $new_picklistvalueid, $sortid));
// Associate picklist values to all the role
$adb->pquery("INSERT INTO vtiger_role2picklist(roleid, picklistvalueid, picklistid, sortid) SELECT roleid,\n\t\t\t\t{$new_picklistvalueid}, {$new_picklistid}, {$sortid} FROM vtiger_role", array());
}
}
} else {
$temp_module_strings = return_module_language($current_language, $fld_module);
}
$mod_picklistvalue = trim($temp_module_strings[$picklistvalues]);
if ($mod_picklistvalue == $pickArray[$i]) {
$pickArray[$i] = $picklistvalues;
}
}
// End
if ($pickArray[$i] == $picklistvalues) {
$picklistcount++;
}
}
if ($picklistcount == 0) {
//Inserting a new pick list value to the corresponding picklist table
$picklistvalue_id = getUniquePicklistID();
$picklist_id = $adb->getUniqueID("vtiger_" . $tableName);
$query = "insert into vtiger_" . $tableName . " values(?,?,?,?)";
$params = array($picklist_id, $pickArray[$i], 1, $picklistvalue_id);
$adb->pquery($query, $params);
}
$picklistcount = 0;
$sql = "select picklist_valueid from vtiger_{$tableName} where {$tableName}=?";
$pick_valueid = $adb->query_result($adb->pquery($sql, array($pickArray[$i])), 0, 'picklist_valueid');
//To get the max sortid for the non editable picklist and the inserting by increasing the sortid for editable values....
$sql = "select max(sortid)+1 as sortid from vtiger_role2picklist left join vtiger_{$tableName} on vtiger_{$tableName}.picklist_valueid=vtiger_role2picklist.picklistvalueid where roleid=? and picklistid=? and presence=0";
$sortid = $adb->query_result($adb->pquery($sql, array($roleid, $picklistid)), 0, 'sortid');
$sql = "insert into vtiger_role2picklist values(?,?,?,?)";
$adb->pquery($sql, array($roleid, $pick_valueid, $picklistid, $sortid));
}
}
function addCustomField()
{
global $current_user, $log, $adb;
$fldmodule = vtlib_purify($_REQUEST['fld_module']);
$fldlabel = vtlib_purify(trim($_REQUEST['fldLabel']));
$fldType = vtlib_purify($_REQUEST['fieldType']);
$parenttab = vtlib_purify($_REQUEST['parenttab']);
$mode = vtlib_purify($_REQUEST['mode']);
$blockid = vtlib_purify($_REQUEST['blockid']);
$tabid = getTabid($fldmodule);
if ($fldmodule == 'Calendar' && isset($_REQUEST['activity_type'])) {
$activitytype = vtlib_purify($_REQUEST['activity_type']);
if ($activitytype == 'E') {
$tabid = '16';
}
if ($activitytype == 'T') {
$tabid = '9';
}
}
if (get_magic_quotes_gpc() == 1) {
$fldlabel = stripslashes($fldlabel);
}
$dup_check_tab_id = $tabid;
if ($fldmodule == 'Calendar') {
$dup_check_tab_id = array('9', '16');
}
$checkquery = "select * from vtiger_field where tabid in (" . generateQuestionMarks($dup_check_tab_id) . ") and fieldlabel=?";
$params = array($dup_check_tab_id, $fldlabel);
$checkresult = $adb->pquery($checkquery, $params);
if ($adb->num_rows($checkresult) > 0) {
$duplicate = 'yes';
return $duplicate;
} else {
$max_fieldid = $adb->getUniqueID("vtiger_field");
$columnName = 'cf_' . $max_fieldid;
$custfld_fieldid = $max_fieldid;
//Assigning the vtiger_table Name
if ($fldmodule != '') {
$focus = CRMEntity::getInstance($fldmodule);
if (isset($focus->customFieldTable)) {
$tableName = $focus->customFieldTable[0];
} else {
$tableName = 'vtiger_' . strtolower($fldmodule) . 'cf';
}
}
//Assigning the uitype
$fldlength = vtlib_purify($_REQUEST['fldLength']);
$uitype = '';
$fldPickList = '';
if (isset($_REQUEST['fldDecimal']) && $_REQUEST['fldDecimal'] != '') {
$decimal = vtlib_purify($_REQUEST['fldDecimal']);
} else {
$decimal = 0;
}
$type = '';
$uichekdata = '';
if ($fldType == 'Text') {
$uichekdata = 'V~O~LE~' . $fldlength;
$uitype = 1;
$type = "C(" . $fldlength . ") default ()";
// adodb type
} elseif ($fldType == 'Number') {
$uitype = 7;
//this may sound ridiculous passing decimal but that is the way adodb wants
$dbfldlength = $fldlength + $decimal + 1;
$type = "N(" . $dbfldlength . "." . $decimal . ")";
// adodb type
// Fix for http://trac.vtiger.com/cgi-bin/trac.cgi/ticket/6363
$uichekdata = 'NN~O~' . $fldlength . ',' . $decimal;
} elseif ($fldType == 'Percent') {
$uitype = 9;
$type = "N(5.2)";
//adodb type
$uichekdata = 'N~O~2~2';
} elseif ($fldType == 'Currency') {
$uitype = 71;
if ($decimal < 2) {
$decimal = 2;
}
$dbfldlength = $fldlength + $decimal + 1;
$type = "N(" . $dbfldlength . "." . $decimal . ")";
//adodb type
$uichekdata = 'N~O~' . $fldlength . ',' . $decimal;
} elseif ($fldType == 'Date') {
$uichekdata = 'D~O';
$uitype = 5;
$type = "D";
// adodb type
} elseif ($fldType == 'Email') {
$uitype = 13;
$type = "C(50) default () ";
//adodb type
$uichekdata = 'E~O';
} elseif ($fldType == 'Time') {
$uitype = 14;
$type = "TIME";
$uichekdata = 'T~O';
} elseif ($fldType == 'Phone') {
$uitype = 11;
$type = "C(30) default () ";
//adodb type
$uichekdata = 'V~O';
} elseif ($fldType == 'Picklist') {
$uitype = 15;
$type = "C(255) default () ";
//adodb type
$uichekdata = 'V~O';
} elseif ($fldType == 'URL') {
$uitype = 17;
$type = "C(255) default () ";
//adodb type
$uichekdata = 'V~O';
} elseif ($fldType == 'Checkbox') {
$uitype = 56;
$type = "C(3) default 0";
//adodb type
$uichekdata = 'C~O';
} elseif ($fldType == 'TextArea') {
$uitype = 21;
$type = "X";
//adodb type
$uichekdata = 'V~O';
} elseif ($fldType == 'MultiSelectCombo') {
$uitype = 33;
$type = "X";
//adodb type
$uichekdata = 'V~O';
} elseif ($fldType == 'Skype') {
$uitype = 85;
$type = "C(255) default () ";
//adodb type
$uichekdata = 'V~O';
}
if (is_numeric($blockid)) {
if ($_REQUEST['fieldid'] == '') {
$max_fieldsequence = "select max(sequence) as maxsequence from vtiger_field where block = ? ";
$res = $adb->pquery($max_fieldsequence, array($blockid));
$max_seq = $adb->query_result($res, 0, 'maxsequence');
if ($fldmodule == 'Quotes' || $fldmodule == 'PurchaseOrder' || $fldmodule == 'SalesOrder' || $fldmodule == 'Invoice') {
$quickcreate = 3;
} else {
$quickcreate = 1;
}
$query = "insert into vtiger_field (tabid,fieldid,columnname,tablename,generatedtype,uitype,fieldname,fieldlabel,readonly,presence,defaultvalue,maximumlength,sequence,block,displaytype,typeofdata,quickcreate,quickcreatesequence,info_type,masseditable) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
$qparams = array($tabid, $custfld_fieldid, $columnName, $tableName, 2, $uitype, $columnName, $fldlabel, 0, 2, '', 100, $max_seq + 1, $blockid, 1, $uichekdata, $quickcreate, 0, 'BAS', 1);
$adb->pquery($query, $qparams);
$adb->alterTable($tableName, $columnName . " " . $type, "Add_Column");
//Inserting values into vtiger_profile2field vtiger_tables
$sql1 = "select * from vtiger_profile";
$sql1_result = $adb->pquery($sql1, array());
$sql1_num = $adb->num_rows($sql1_result);
for ($i = 0; $i < $sql1_num; $i++) {
$profileid = $adb->query_result($sql1_result, $i, "profileid");
$sql2 = "insert into vtiger_profile2field values(?,?,?,?,?)";
$adb->pquery($sql2, array($profileid, $tabid, $custfld_fieldid, 0, 0));
}
//Inserting values into def_org vtiger_tables
$sql_def = "insert into vtiger_def_org_field values(?,?,?,?)";
$adb->pquery($sql_def, array($tabid, $custfld_fieldid, 0, 0));
if ($fldType == 'Picklist' || $fldType == 'MultiSelectCombo') {
$columnName = $adb->sql_escape_string($columnName);
// Creating the PickList Table and Populating Values
if ($_REQUEST['fieldid'] == '') {
$qur = "CREATE TABLE vtiger_" . $columnName . " (\n\t\t\t\t\t\t\t" . $columnName . "id int(19) NOT NULL auto_increment,\n\t\t\t\t\t\t\t" . $columnName . " varchar(200) NOT NULL,\n\t\t\t\t\t\t\tpresence int(1) NOT NULL default '1',\n\t\t\t\t\t\t\tpicklist_valueid int(19) NOT NULL default '0',\n\t\t\t\t\t\t\tPRIMARY KEY (" . $columnName . "id)\n\t\t\t\t\t\t)";
$adb->pquery($qur, array());
}
//Adding a new picklist value in the picklist table
if ($mode != 'edit') {
$picklistid = $adb->getUniqueID("vtiger_picklist");
$sql = "insert into vtiger_picklist values(?,?)";
$adb->pquery($sql, array($picklistid, $columnName));
}
$roleid = $current_user->roleid;
$qry = "select picklistid from vtiger_picklist where name=?";
$picklistid = $adb->query_result($adb->pquery($qry, array($columnName)), 0, 'picklistid');
$pickArray = array();
$fldPickList = vtlib_purify($_REQUEST['fldPickList']);
$pickArray = explode("\n", $fldPickList);
$count = count($pickArray);
global $default_charset;
for ($i = 0; $i < $count; $i++) {
$pickArray[$i] = trim(htmlentities($pickArray[$i], ENT_QUOTES, $default_charset));
if ($pickArray[$i] != '') {
$picklistcount = 0;
$sql = "select {$columnName} from vtiger_{$columnName}";
$numrow = $adb->num_rows($adb->pquery($sql, array()));
for ($x = 0; $x < $numrow; $x++) {
$picklistvalues = $adb->query_result($adb->pquery($sql, array()), $x, $columnName);
if ($pickArray[$i] == $picklistvalues) {
$picklistcount++;
}
}
if ($picklistcount == 0) {
$picklist_valueid = getUniquePicklistID();
$query = "insert into vtiger_" . $columnName . " values(?,?,?,?)";
$adb->pquery($query, array($adb->getUniqueID("vtiger_" . $columnName), $pickArray[$i], 1, $picklist_valueid));
/*$sql="update vtiger_picklistvalues_seq set id = ?";
$adb->pquery($sql, array(++$picklist_valueid));*/
}
$sql = "select picklist_valueid from vtiger_{$columnName} where {$columnName}=?";
$pick_valueid = $adb->query_result($adb->pquery($sql, array($pickArray[$i])), 0, 'picklist_valueid');
$sql = "insert into vtiger_role2picklist select roleid,{$pick_valueid},{$picklistid},{$i} from vtiger_role";
$adb->pquery($sql, array());
}
}
}
}
}
}
}
$pickArray = explode("\n", $fldPickList);
$count = count($pickArray);
for ($i = 0; $i < $count; $i++) {
$pickArray[$i] = trim(from_html($pickArray[$i]));
if ($pickArray[$i] != '') {
$picklistcount = 0;
$sql = "select {$columnName} from vtiger_{$columnName}";
$numrow = $adb->num_rows($adb->pquery($sql, array()));
for ($x = 0; $x < $numrow; $x++) {
$picklistvalues = $adb->query_result($adb->pquery($sql, array()), $x, $columnName);
if ($pickArray[$i] == $picklistvalues) {
$picklistcount++;
}
}
if ($picklistcount == 0) {
$picklist_valueid = getUniquePicklistID();
$query = "insert into vtiger_" . $columnName . " values(?,?,?,?)";
$adb->pquery($query, array($adb->getUniqueID("vtiger_" . $columnName), $pickArray[$i], 1, $picklist_valueid));
/*$sql="update vtiger_picklistvalues_seq set id = ?";
$adb->pquery($sql, array(++$picklist_valueid));*/
}
$sql = "select picklist_valueid from vtiger_{$columnName} where {$columnName}=?";
$pick_valueid = $adb->query_result($adb->pquery($sql, array($pickArray[$i])), 0, 'picklist_valueid');
$sql = "insert into vtiger_role2picklist select roleid,{$pick_valueid},{$picklistid},{$i} from vtiger_role";
$adb->pquery($sql, array());
}
}
}
//Inserting into LeadMapping table - Jaguar
if ($fldmodule == 'Leads' && $_REQUEST['fieldid'] == '') {
$sql_def = "insert into vtiger_convertleadmapping (leadfid) values(?)";
//Remove the repeated entries and make this array with unique entries
$csv_picklist_values[$fieldname] = array_unique($csv_picklist_values[$fieldname]);
}
}
//Now we have to add the CSV picklists in the picklist table if it is not exist
foreach ($csv_picklist_values as $fieldname => $temp_array) {
$tablename = "vtiger_{$fieldname}";
foreach ($temp_array as $ind => $picklist_value) {
$pick_val = strtolower($picklist_value);
//Check whether $picklist_value is exist in the array of available picklist entries
if (!in_array($pick_val, $converted_table_picklist_values[$fieldname])) {
//Not exist, so we have to add this $picklist_value in $fieldname(picklist name) table
$picklist_value = addslashes($picklist_value);
$adb->println("{$picklist_value} has to be added in the table {$tablename}");
$cfId = $adb->getUniqueID($tablename);
$unique_picklist_value = getUniquePicklistID();
$qry = "insert into {$tablename} values(?,?,?,?)";
$adb->pquery($qry, array($cfId, $picklist_value, 1, $unique_picklist_value));
//added to fix ticket#4492
$picklistId_qry = "select picklistid from vtiger_picklist where name=?";
$picklistId_res = $adb->pquery($picklistId_qry, array($fieldname));
$picklist_Id = $adb->query_result($picklistId_res, 0, 'picklistid');
$role_id = $current_user->roleid;
$sort_qry = "select max(sortid)+1 as sortid from vtiger_role2picklist where picklistid=? and roleid=?";
$sort_qry_res = $adb->pquery($sort_qry, array($picklist_Id, $role_id));
$sort_id = $adb->query_result($sort_qry_res, 0, 'sortid');
$role_picklist = "insert into vtiger_role2picklist values (?,?,?,?)";
$adb->pquery($role_picklist, array($role_id, $unique_picklist_value, $picklist_Id, $sort_id));
}
}
}
function insert_values($picklist, $picklistid)
{
global $adb;
$result = $adb->query("select * from vtiger_{$picklist}");
$numrow = $adb->num_rows($result);
for ($i = 0; $i < $numrow; $i++) {
$picklist_name = decode_html($adb->query_result($result, $i, $picklist));
$picklist_valueid = getUniquePicklistID();
$picklistquery = "update vtiger_{$picklist} set picklist_valueid=? where {$picklist}=?";
$adb->pquery($picklistquery, array($picklist_valueid, $picklist_name));
$sql = "select roleid from vtiger_role";
$role_result = $adb->query($sql);
$numrows = $adb->num_rows($role_result);
for ($k = 0; $k < $numrows; $k++) {
$roleid = $adb->query_result($role_result, $k, 'roleid');
$adb->query("insert into vtiger_role2picklist values('" . $roleid . "'," . $picklist_valueid . ",{$picklistid}," . $i . ")");
}
}
}
/** Function for getting the custom values from leads and saving to vtiger_account/contact/potential custom vtiger_fields.
* @param string $type - Field Type (eg: text, list)
* @param integer $type_id - Field Type ID
*/
function getInsertValues($type, $type_id)
{
global $id, $adb, $log;
$log->debug("Entering getInsertValues(" . $type . "," . $type_id . ") method ...");
$sql_convert_lead = "select * from vtiger_convertleadmapping ";
$convert_result = $adb->pquery($sql_convert_lead, array());
$noofrows = $adb->num_rows($convert_result);
$value_cf_array = array();
for ($i = 0; $i < $noofrows; $i++) {
$flag = "false";
$log->info("In vtiger_convertleadmapping function");
$lead_id = $adb->query_result($convert_result, $i, "leadfid");
//Getting the relatd customfields for Accounts/Contact/potential from vtiger_convertleadmapping vtiger_table
$account_id_val = $adb->query_result($convert_result, $i, "accountfid");
$contact_id_val = $adb->query_result($convert_result, $i, "contactfid");
$potential_id_val = $adb->query_result($convert_result, $i, "potentialfid");
$sql_leads_column = "select vtiger_field.uitype,vtiger_field.fieldid,vtiger_field.columnname from vtiger_field,vtiger_tab where vtiger_field.tabid=vtiger_tab.tabid and generatedtype=2 and vtiger_tab.name='Leads' and fieldid=? and vtiger_field.presence in (0,2)";
//getting the columnname for the customfield of the lead
$log->debug("Lead's custom vtiger_field coumn name is " . $sql_leads_column);
$lead_column_result = $adb->pquery($sql_leads_column, array($lead_id));
$leads_no_rows = $adb->num_rows($lead_column_result);
if ($leads_no_rows > 0) {
$lead_column_name = $adb->query_result($lead_column_result, 0, "columnname");
$lead_uitype = $adb->query_result($lead_column_result, 0, "uitype");
$sql_leads_val = "select {$lead_column_name} from vtiger_leadscf where leadid=?";
//custom vtiger_field value for lead
$lead_val_result = $adb->pquery($sql_leads_val, array($id));
$lead_value = $adb->query_result($lead_val_result, 0, $lead_column_name);
$log->debug("Lead's custom vtiger_field value is " . $lead_value);
}
//Query for getting the column name for Accounts/Contacts/Potentials if custom vtiger_field for lead is mappped
$sql_type = "select vtiger_field.fieldid,vtiger_field.uitype,vtiger_field.columnname from vtiger_field,vtiger_tab where vtiger_field.tabid=vtiger_tab.tabid and generatedtype=2 and vtiger_field.presence in (0,2) and vtiger_tab.name=";
$params = array();
if ($type == "Accounts") {
if ($account_id_val != "" && $account_id_val != 0) {
$flag = "true";
$log->info("Getting the Accounts custom vtiger_field column name ");
$sql_type .= "'Accounts' and fieldid=?";
array_push($params, $account_id_val);
}
} else {
if ($type == "Contacts") {
if ($contact_id_val != "" && $contact_id_val != 0) {
$flag = "true";
$log->info("Getting the Contacts custom vtiger_field column name ");
$sql_type .= "'Contacts' and fieldid=?";
array_push($params, $contact_id_val);
}
} else {
if ($type == "Potentials") {
if ($potential_id_val != "" && $potential_id_val != 0) {
$flag = "true";
$log->info("Getting the Potentials custom vtiger_field column name ");
$sql_type .= "'Potentials' and fieldid=?";
array_push($params, $potential_id_val);
}
}
}
}
if ($flag == "true") {
$type_result = $adb->pquery($sql_type, $params);
//To construct the cf array
$colname = $adb->query_result($type_result, 0, "columnname");
$type_insert_column[] = $colname;
$type_uitype = $adb->query_result($type_result, 0, "uitype");
//To construct the cf array
$ins_val = $adb->query_result($lead_val_result, 0, $lead_column_name);
//This array is used to store the tablename as the key and the value for that table in the custom field of the uitype only for 15 and 33(Multiselect cf)...
if ($lead_uitype == 33 || $lead_uitype == 15) {
$lead_val_arr[$colname] = $lead_column_name;
$value_cf_array[$colname] = $ins_val;
}
$insert_value[] = $ins_val;
}
}
if (count($value_cf_array) > 0) {
if (count($type_insert_column) > 0) {
foreach ($value_cf_array as $key => $value) {
$tableName = $key;
$tableVal = $value;
if ($tableVal != '') {
$tab_val = explode("|##|", $value);
if (count($tab_val) > 0) {
for ($k = 0; $k < count($tab_val); $k++) {
$val = $tab_val[$k];
$sql = "select {$tableName} from vtiger_{$tableName}";
$numRow = $adb->num_rows($adb->query($sql));
$count = 0;
for ($n = 0; $n < $numRow; $n++) {
$exist_val = $adb->query_result($adb->query($sql), $n, $tableName);
if (trim($exist_val) == trim($val)) {
$count++;
}
}
if ($count == 0) {
$cfId = $adb->getUniqueID("vtiger_{$tableName}");
$unique_picklist_value = getUniquePicklistID();
$qry = "insert into vtiger_{$tableName} values(?,?,?,?)";
$adb->pquery($qry, array($cfId, trim($val), 1, $unique_picklist_value));
//added to fix ticket#4492
$picklistId_qry = "select picklistid from vtiger_picklist where name=?";
$picklistId_res = $adb->pquery($picklistId_qry, array($tableName));
$picklist_Id = $adb->query_result($picklistId_res, 0, 'picklistid');
//While adding a new value into the picklist table, the roleid's which has permission for the lead cf will only given permission for contacts,potentials and account module while converting. -- refer ticket #4885 ---
$role_qry = "select roleid from vtiger_role2picklist where picklistvalueid in (select picklist_valueid from vtiger_" . $lead_val_arr[$tableName] . " where " . $lead_val_arr[$tableName] . "='" . trim($val) . "')";
$numOFRole = $adb->num_rows($adb->query($role_qry));
for ($l = 0; $l < $numOFRole; $l++) {
$role_id = $adb->query_result($adb->query($role_qry), $l, 'roleid');
$sort_qry = "select max(sortid)+1 as sortid from vtiger_role2picklist where picklistid=? and roleid=?";
$sort_qry_res = $adb->pquery($sort_qry, array($picklist_Id, $role_id));
$sort_id = $adb->query_result($sort_qry_res, 0, 'sortid');
$role_picklist = "insert into vtiger_role2picklist values (?,?,?,?)";
$adb->pquery($role_picklist, array($role_id, $unique_picklist_value, $picklist_Id, $sort_id));
}
//end
}
}
}
}
}
}
}
$log->debug("columns to be inserted are " . $type_insert_column);
$log->debug("columns to be inserted are " . $insert_value);
$values = array('columns' => $type_insert_column, 'values' => $insert_value);
$log->debug("Exiting getInsertValues method ...");
return $values;
}
