616 lines
30 KiB
PHP
616 lines
30 KiB
PHP
<?php
|
|
/*+********************************************************************************
|
|
* The contents of this file are subject to the vtiger CRM Public License Version 1.0
|
|
* ("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.
|
|
*********************************************************************************/
|
|
|
|
require_once 'include/Webservices/Utils.php';
|
|
require_once 'modules/Users/Users.php';
|
|
require_once 'include/utils/utils.php';
|
|
require_once ('vtlib/Vtiger/Utils.php');
|
|
|
|
//5.1.0 to 5.2.0 RC database changes
|
|
|
|
//we have to use the current object (stored in PatchApply.php) to execute the queries
|
|
$adb = $_SESSION['adodb_current_object'];
|
|
$conn = $_SESSION['adodb_current_object'];
|
|
|
|
$migrationlog->debug("\n\nDB Changes from 5.1.0 to 5.2.0 RC -------- Starts \n\n");
|
|
function migration520_populateFieldForSecurity($tabid,$fieldid)
|
|
{
|
|
global $adb;
|
|
|
|
$check_deforg_res = $adb->pquery("SELECT 1 FROM vtiger_def_org_field WHERE tabid=? AND fieldid = ? LIMIT 1", array($tabid, $fieldid));
|
|
if($check_deforg_res && $adb->num_rows($check_deforg_res)) {
|
|
// Entry already exists, no need to act
|
|
} else {
|
|
$adb->pquery("INSERT INTO vtiger_def_org_field (tabid, fieldid, visible, readonly) VALUES(?,?,?,?)",
|
|
array($tabid, $fieldid, 0, 1));
|
|
}
|
|
|
|
$profileresult = $adb->pquery("SELECT * FROM vtiger_profile", array());
|
|
$countprofiles = $adb->num_rows($profileresult);
|
|
for ($i=0;$i<$countprofiles;$i++)
|
|
{
|
|
$profileid = $adb->query_result($profileresult,$i,'profileid');
|
|
$checkres = $adb->pquery("SELECT 1 FROM vtiger_profile2field WHERE profileid=? AND tabid=? AND fieldid=?", array($profileid, $tabid, $fieldid));
|
|
if($checkres && $adb->num_rows($checkres)) {
|
|
// Entry already exists, do nothing
|
|
} else {
|
|
$adb->pquery("INSERT INTO vtiger_profile2field (profileid, tabid, fieldid, visible, readonly) VALUES(?,?,?,?,?)",
|
|
array($profileid, $tabid, $fieldid, 0, 1));
|
|
}
|
|
}
|
|
}
|
|
ExecuteQuery("CREATE TABLE IF NOT EXISTS vtiger_tab_info (tabid INT, prefname VARCHAR(256), prefvalue VARCHAR(256), FOREIGN KEY fk_1_vtiger_tab_info(tabid) REFERENCES vtiger_tab(tabid) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
|
|
|
|
$documents_tab_id=getTabid('Documents');
|
|
ExecuteQuery("update vtiger_field set quickcreate=3 where tabid = $documents_tab_id and columnname = 'filelocationtype'");
|
|
/* For Campaigns enhancement */
|
|
$accounts_tab_id = getTabid('Accounts');
|
|
$campaigns_tab_id = getTabid('Campaigns');
|
|
$contacts_tab_id = getTabid('Contacts');
|
|
$leads_tab_id = getTabid('Leads');
|
|
|
|
|
|
$campignrelstatus_contacts_fieldid = $adb->getUniqueID('vtiger_field');
|
|
ExecuteQuery("INSERT INTO vtiger_field(tabid, fieldid, columnname, tablename, generatedtype, uitype, fieldname, fieldlabel, readonly, presence, selected, maximumlength, sequence, block, displaytype, typeofdata, quickcreate, quickcreatesequence, info_type, masseditable, helpinfo) VALUES ($contacts_tab_id,".$campignrelstatus_contacts_fieldid.", 'campaignrelstatus', 'vtiger_campaignrelstatus', 1, '16', 'campaignrelstatus', 'Status', 1, 0, 0, 100, 1, NULL, 1, 'V~O', 1, NULL, 'BAS', 0, NULL)");
|
|
migration520_populateFieldForSecurity($contacts_tab_id, $campignrelstatus_contacts_fieldid);
|
|
|
|
$campignrelstatus_accounts_fieldid = $adb->getUniqueID('vtiger_field');
|
|
ExecuteQuery("INSERT INTO vtiger_field(tabid, fieldid, columnname, tablename, generatedtype, uitype, fieldname, fieldlabel, readonly, presence, selected, maximumlength, sequence, block, displaytype, typeofdata, quickcreate, quickcreatesequence, info_type, masseditable, helpinfo) VALUES ($accounts_tab_id,".$campignrelstatus_accounts_fieldid.", 'campaignrelstatus', 'vtiger_campaignrelstatus', 1, '16', 'campaignrelstatus', 'Status', 1, 0, 0, 100, 1, NULL, 1, 'V~O', 1, NULL, 'BAS', 0, NULL)");
|
|
migration520_populateFieldForSecurity($accounts_tab_id, $campignrelstatus_accounts_fieldid);
|
|
|
|
$campignrelstatus_leads_fieldid = $adb->getUniqueID('vtiger_field');
|
|
ExecuteQuery("INSERT INTO vtiger_field(tabid, fieldid, columnname, tablename, generatedtype, uitype, fieldname, fieldlabel, readonly, presence, selected, maximumlength, sequence, block, displaytype, typeofdata, quickcreate, quickcreatesequence, info_type, masseditable, helpinfo) VALUES ($leads_tab_id,".$campignrelstatus_leads_fieldid.", 'campaignrelstatus', 'vtiger_campaignrelstatus', 1, '16', 'campaignrelstatus', 'Status', 1, 0, 0, 100, 1, NULL, 1, 'V~O', 1, NULL, 'BAS', 0, NULL)");
|
|
migration520_populateFieldForSecurity($leads_tab_id, $campignrelstatus_leads_fieldid);
|
|
|
|
$campignrelstatus_campaigns_fieldid = $adb->getUniqueID('vtiger_field');
|
|
ExecuteQuery("INSERT INTO vtiger_field(tabid, fieldid, columnname, tablename, generatedtype, uitype, fieldname, fieldlabel, readonly, presence, selected, maximumlength, sequence, block, displaytype, typeofdata, quickcreate, quickcreatesequence, info_type, masseditable, helpinfo) VALUES ($campaigns_tab_id,".$campignrelstatus_campaigns_fieldid.", 'campaignrelstatus', 'vtiger_campaignrelstatus', 1, '16', 'campaignrelstatus', 'Status', 1, 0, 0, 100, 1, NULL, 1, 'V~O', 1, NULL, 'BAS', 0, NULL)");
|
|
migration520_populateFieldForSecurity($campaigns_tab_id, $campignrelstatus_campaigns_fieldid);
|
|
|
|
ExecuteQuery("CREATE TABLE vtiger_campaignrelstatus (
|
|
campaignrelstatusid INTEGER, campaignrelstatus VARCHAR(200), sortorderid INT, presence INT) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
|
|
|
|
ExecuteQuery("INSERT INTO vtiger_campaignrelstatus VALUES (".$adb->getUniqueID('vtiger_campaignrelstatus').", '--None--',1,1)");
|
|
ExecuteQuery("INSERT INTO vtiger_campaignrelstatus VALUES (".$adb->getUniqueID('vtiger_campaignrelstatus').", 'Contacted - Successful',2,1)");
|
|
ExecuteQuery("INSERT INTO vtiger_campaignrelstatus VALUES (".$adb->getUniqueID('vtiger_campaignrelstatus').", 'Contected - Unsuccessful',3,1)");
|
|
ExecuteQuery("INSERT INTO vtiger_campaignrelstatus VALUES (".$adb->getUniqueID('vtiger_campaignrelstatus').", 'Contacted - Never Contact Again',4,1)");
|
|
|
|
ExecuteQuery("CREATE TABLE vtiger_campaignaccountrel (
|
|
campaignid INTEGER UNSIGNED NOT NULL,
|
|
accountid INTEGER UNSIGNED NOT NULL,
|
|
campaignrelstatusid INTEGER UNSIGNED DEFAULT 1) ENGINE = InnoDB DEFAULT CHARSET=utf8;");
|
|
ExecuteQuery("ALTER TABLE vtiger_campaignaccountrel ADD PRIMARY KEY (campaignid, accountid)");
|
|
|
|
ExecuteQuery("ALTER TABLE vtiger_campaigncontrel ADD COLUMN campaignrelstatusid INTEGER UNSIGNED NOT NULL DEFAULT 1");
|
|
ExecuteQuery("ALTER TABLE vtiger_campaignleadrel ADD COLUMN campaignrelstatusid INTEGER UNSIGNED NOT NULL DEFAULT 1");
|
|
|
|
ExecuteQuery("INSERT INTO vtiger_relatedlists VALUES (".$adb->getUniqueID('vtiger_relatedlists').", $accounts_tab_id, $campaigns_tab_id, 'get_campaigns', 13, 'Campaigns', 0, 'select')");
|
|
ExecuteQuery("INSERT INTO vtiger_relatedlists VALUES (".$adb->getUniqueID('vtiger_relatedlists').", $campaigns_tab_id, $accounts_tab_id, 'get_accounts', 5, 'Accounts', 0, 'add,select')");
|
|
|
|
Vtiger_Utils::AddColumn('vtiger_inventorynotification', 'status','VARCHAR(30)');
|
|
|
|
//Fix : 6182 after migration from 510 'fields to be shown' at a profile for Email module
|
|
|
|
$query = "SELECT * from vtiger_profile";
|
|
$result = $adb->pquery($query,array());
|
|
$rows = $adb->num_rows($result);
|
|
|
|
$fields = "SELECT fieldid from vtiger_field where tablename = ?";
|
|
$fieldResult = $adb->pquery($fields,array('vtiger_emaildetails'));
|
|
$fieldRows = $adb->num_rows($fieldResult);
|
|
$EmailTabid = getTabid('Emails');
|
|
for($i=0; $i<$rows ;$i++){
|
|
$profileid = $adb->query_result($result ,$i ,'profileid');
|
|
for($j=0 ;$j<$fieldRows; $j++) {
|
|
$fieldid = $adb->query_result($fieldResult, $j ,'fieldid');
|
|
|
|
$sql_profile2field = "select * from vtiger_profile2field where fieldid=? and profileid=?";
|
|
$result_profile2field = $adb->pquery($sql_profile2field,array($fieldid,$profileid));
|
|
$rows_profile2field = $adb->num_rows($result_profile2field);
|
|
if(!($rows_profile2field > 0)){
|
|
$adb->query("INSERT INTO vtiger_profile2field(profileid ,tabid,fieldid,visible,readonly) VALUES ($profileid, $EmailTabid, $fieldid, 0 , 1)");
|
|
}
|
|
}
|
|
}
|
|
for($k=0;$k<$fieldRows;$k++){
|
|
$fieldid = $adb->query_result($fieldResult, $k ,'fieldid');
|
|
$sql_deforgfield = "select * from vtiger_def_org_field where tabid=? and fieldid=?";
|
|
$result_deforgfield = $adb->pquery($sql_deforgfield,array($EmailTabid,$fieldid));
|
|
$rows_deforgfield = $adb->num_rows($result_deforgfield);
|
|
if(!($rows_deforgfield)){
|
|
$adb->query("INSERT INTO vtiger_def_org_field(tabid ,fieldid,visible,readonly) VALUES ($EmailTabid, $fieldid, 0 , 1)");
|
|
}
|
|
}
|
|
$sql = 'update vtiger_field set block=(select blockid from vtiger_blocks where '.
|
|
"blocklabel=?) where tablename=?";
|
|
$params = array('LBL_EMAIL_INFORMATION','vtiger_emaildetails');
|
|
$adb->pquery($sql,$params);
|
|
//END
|
|
//update vtiger_systems to add a email field to be used as the from email address
|
|
$sql = "ALTER TABLE vtiger_systems ADD from_email_field varchar(50);";
|
|
ExecuteQuery($sql);
|
|
//END
|
|
|
|
// to disable unit_price from the massedit wizndow for products
|
|
ExecuteQuery("update vtiger_field set masseditable=0 where tablename='vtiger_products' and columnname='unit_price'");
|
|
//END
|
|
function VT520_webserviceMigrate(){
|
|
require_once 'include/Webservices/Utils.php';
|
|
$customWebserviceDetails = array(
|
|
"name"=>"convertlead",
|
|
"include"=>"include/Webservices/ConvertLead.php",
|
|
"handler"=>"vtws_convertlead",
|
|
"prelogin"=>0,
|
|
"type"=>"POST"
|
|
);
|
|
|
|
$customWebserviceParams = array(
|
|
array("name"=>'leadId',"type"=>'String' ),
|
|
array("name"=>'assignedTo','type'=>'String'),
|
|
array("name"=>'accountName','type'=>'String'),
|
|
array("name"=>'avoidPotential','type'=>'Boolean'),
|
|
array("name"=>'potential','type'=>'Encoded')
|
|
);
|
|
echo 'INITIALIZING WEBSERVICE...';
|
|
$operationId = vtws_addWebserviceOperation($customWebserviceDetails['name'],$customWebserviceDetails['include'],
|
|
$customWebserviceDetails['handler'],$customWebserviceDetails['type']);
|
|
if($operationId === null && $operationId > 0){
|
|
echo 'FAILED TO SETUP '.$customWebserviceDetails['name'].' WEBSERVICE';
|
|
die;
|
|
}
|
|
$sequence = 1;
|
|
foreach ($customWebserviceParams as $param) {
|
|
$status = vtws_addWebserviceOperationParam($operationId,$param['name'],$param['type'],$sequence++);
|
|
if($status === false){
|
|
echo 'FAILED TO SETUP '.$customWebserviceDetails['name'].' WEBSERVICE HALFWAY THOURGH';
|
|
die;
|
|
}
|
|
}
|
|
}
|
|
|
|
VT520_webserviceMigrate();
|
|
|
|
$update_InvProductRel = "ALTER TABLE vtiger_inventoryproductrel MODIFY discount_amount decimal(25,3)";
|
|
ExecuteQuery($update_InvProductRel);
|
|
// Registering events for ON MODIFY in Workflows
|
|
$handlerId = $adb->getUniqueId('vtiger_eventhandlers');
|
|
$modifyevent ='vtiger.entity.afterrestore';
|
|
$eventPath = 'modules/com_vtiger_workflow/VTEventHandler.inc';
|
|
$handlerClass = 'VTWorkflowEventHandler';
|
|
$modifyevent = $adb->pquery("insert into vtiger_eventhandlers(eventhandler_id, event_name, handler_path, handler_class,cond,is_active)
|
|
values (?,?,?,?,?,1)",array($handlerId,$modifyevent,$eventPath,$handlerClass,''));
|
|
|
|
// Populate Default Workflows
|
|
populateDefaultWorkflows($adb);
|
|
|
|
function populateDefaultWorkflows($adb) {
|
|
require_once("modules/com_vtiger_workflow/include.inc");
|
|
require_once("modules/com_vtiger_workflow/tasks/VTEntityMethodTask.inc");
|
|
require_once("modules/com_vtiger_workflow/VTEntityMethodManager.inc");
|
|
|
|
//added column defaultworkflow
|
|
//For default workflows it sets column defaultworkflow=true
|
|
|
|
$column_name="defaultworkflow";
|
|
$adb->pquery("alter table com_vtiger_workflows add column $column_name int(1)",array());
|
|
|
|
// Creating Workflow for Accounts when Notifyowner is true
|
|
|
|
$vtaWorkFlow = new VTWorkflowManager($adb);
|
|
$accWorkFlow = $vtaWorkFlow->newWorkFlow("Accounts");
|
|
$accWorkFlow->test = '[{"fieldname":"notify_owner","operation":"is","value":"true:boolean"}]';
|
|
$accWorkFlow->description = "Send Email to user when Notifyowner is True";
|
|
$accWorkFlow->executionCondition=2;
|
|
$vtaWorkFlow->save($accWorkFlow);
|
|
$id1=$accWorkFlow->id;
|
|
|
|
$tm = new VTTaskManager($adb);
|
|
$task = $tm->createTask('VTEmailTask',$accWorkFlow->id);
|
|
$task->active=true;
|
|
$task->methodName = "NotifyOwner";
|
|
$task->recepient = "\$(assigned_user_id : (Users) email1)";
|
|
$task->subject = "Regarding Account Creation";
|
|
$task->content = "An Account has been assigned to you on vtigerCRM<br>Details of account are :<br><br>".
|
|
"AccountId:".'<b>$account_no</b><br>'."AccountName:".'<b>$accountname</b><br>'."Rating:".'<b>$rating</b><br>'.
|
|
"Industry:".'<b>$industry</b><br>'."AccountType:".'<b>$accounttype</b><br>'.
|
|
"Description:".'<b>$description</b><br><br><br>'."Thank You<br>Admin";
|
|
$task->summary="An account has been created ";
|
|
$tm->saveTask($task);
|
|
$adb->pquery("update com_vtiger_workflows set defaultworkflow=? where workflow_id=?",array(1,$id1));
|
|
|
|
|
|
// Creating Workflow for Contacts when Notifyowner is true
|
|
|
|
$vtcWorkFlow = new VTWorkflowManager($adb);
|
|
$conWorkFlow = $vtcWorkFlow->newWorkFlow("Contacts");
|
|
$conWorkFlow->summary="Test accounut";
|
|
$conWorkFlow->executionCondition=2;
|
|
$conWorkFlow->test = '[{"fieldname":"notify_owner","operation":"is","value":"true:boolean"}]';
|
|
$conWorkFlow->description = "Send Email to user when Notifyowner is True";
|
|
|
|
$vtcWorkFlow->save($conWorkFlow);
|
|
$id1=$conWorkFlow->id;
|
|
$tm = new VTTaskManager($adb);
|
|
$task = $tm->createTask('VTEmailTask',$conWorkFlow->id);
|
|
$task->active=true;
|
|
$task->methodName = "NotifyOwner";
|
|
$task->recepient = "\$(assigned_user_id : (Users) email1)";
|
|
$task->subject = "Regarding Contact Creation";
|
|
$task->content = "An Contact has been assigned to you on vtigerCRM<br>Details of Contact are :<br><br>".
|
|
"Contact Id:".'<b>$contact_no</b><br>'."LastName:".'<b>$lastname</b><br>'."FirstName:".'<b>$firstname</b><br>'.
|
|
"Lead Source:".'<b>$leadsource</b><br>'.
|
|
"Department:".'<b>$department</b><br>'.
|
|
"Description:".'<b>$description</b><br><br><br>'."Thank You<br>Admin";
|
|
$task->summary="An contact has been created ";
|
|
$tm->saveTask($task);
|
|
$adb->pquery("update com_vtiger_workflows set defaultworkflow=? where workflow_id=?",array(1,$id1));
|
|
|
|
|
|
// Creating Workflow for Contacts when PortalUser is true
|
|
|
|
$vtcWorkFlow = new VTWorkflowManager($adb);
|
|
$conpuWorkFlow = $vtcWorkFlow->newWorkFlow("Contacts");
|
|
$conpuWorkFlow->test = '[{"fieldname":"portal","operation":"is","value":"true:boolean"}]';
|
|
$conpuWorkFlow->description = "Send Email to user when Portal User is True";
|
|
$conpuWorkFlow->executionCondition=2;
|
|
$vtcWorkFlow->save($conpuWorkFlow);
|
|
$id1=$conpuWorkFlow->id;
|
|
|
|
$tm = new VTTaskManager($adb);
|
|
$task = $tm->createTask('VTEmailTask',$conpuWorkFlow->id);
|
|
|
|
$task->active=true;
|
|
$task->methodName = "NotifyOwner";
|
|
$task->recepient = "\$(assigned_user_id : (Users) email1)";
|
|
$task->subject = "Regarding Contact Assignment";
|
|
$task->content = "An Contact has been assigned to you on vtigerCRM<br>Details of Contact are :<br><br>".
|
|
"Contact Id:".'<b>$contact_no</b><br>'."LastName:".'<b>$lastname</b><br>'."FirstName:".'<b>$firstname</b><br>'.
|
|
"Lead Source:".'<b>$leadsource</b><br>'.
|
|
"Department:".'<b>$department</b><br>'.
|
|
"Description:".'<b>$description</b><br><br><br>'."And <b>CustomerPortal Login Details</b> is sent to the " .
|
|
"EmailID :-".'$email<br>'."<br>Thank You<br>Admin";
|
|
|
|
$task->summary="An contact has been created ";
|
|
$tm->saveTask($task);
|
|
$adb->pquery("update com_vtiger_workflows set defaultworkflow=? where workflow_id=?",array(1,$id1));
|
|
|
|
// Creating Workflow for Potentials
|
|
|
|
$vtcWorkFlow = new VTWorkflowManager($adb);
|
|
$potentialWorkFlow = $vtcWorkFlow->newWorkFlow("Potentials");
|
|
$potentialWorkFlow->description = "Send Email to user on Potential creation";
|
|
$potentialWorkFlow->executionCondition=1;
|
|
$vtcWorkFlow->save($potentialWorkFlow);
|
|
$id1=$potentialWorkFlow->id;
|
|
|
|
$tm = new VTTaskManager($adb);
|
|
$task = $tm->createTask('VTEmailTask',$potentialWorkFlow->id);
|
|
|
|
$task->active=true;
|
|
$task->recepient = "\$(assigned_user_id : (Users) email1)";
|
|
$task->subject = "Regarding Potential Assignment";
|
|
$task->content = "An Potential has been assigned to you on vtigerCRM<br>Details of Potential are :<br><br>".
|
|
"Potential No:".'<b>$potential_no</b><br>'."Potential Name:".'<b>$potentialname</b><br>'.
|
|
"Amount:".'<b>$amount</b><br>'.
|
|
"Expected Close Date:".'<b>$closingdate</b><br>'.
|
|
"Type:".'<b>$opportunity_type</b><br><br><br>'.
|
|
"Description :".'$description<br>'."<br>Thank You<br>Admin";
|
|
|
|
$task->summary="An Potential has been created ";
|
|
$tm->saveTask($task);
|
|
$adb->pquery("update com_vtiger_workflows set defaultworkflow=? where workflow_id=?",array(1,$id1));
|
|
}
|
|
|
|
function VT520_migrateCustomview($sql,$forModule, $user, $handler) {
|
|
$db = PearDatabase::getInstance();
|
|
$params = array();
|
|
$result = $db->pquery($sql, $params);
|
|
$it = new SqlResultIterator($db, $result);
|
|
|
|
$moduleMetaInfo = array();
|
|
|
|
foreach ($it as $row) {
|
|
$module = $row->entitytype;
|
|
$current_module = $module;
|
|
if($forModule == 'Accounts') {
|
|
if($module == 'Potentials') {
|
|
$fieldname = 'related_to';
|
|
} else {
|
|
$fieldname = 'account_id';
|
|
}
|
|
}elseif($forModule == 'Contacts') {
|
|
$fieldname = 'contact_id';
|
|
}elseif($forModule == 'Products') {
|
|
$fieldname = 'product_id';
|
|
} elseif ($forModule == 'SalesOrder') {
|
|
$fieldname = 'quote_id';
|
|
}
|
|
|
|
if(empty($moduleMetaInfo[$module])) {
|
|
$moduleMetaInfo[$module] = new VtigerCRMObjectMeta(VtigerWebserviceObject::fromName($db,
|
|
$module), $user);
|
|
}
|
|
$meta = $moduleMetaInfo[$module];
|
|
|
|
$moduleFields = $meta->getModuleFields();
|
|
$field = $moduleFields[$fieldname];
|
|
$columnname = $field->getTableName().':'.$field->getColumnName().':'.$field->getFieldName().
|
|
':'.$module.'_'.str_replace(' ','_',$field->getFieldLabelKey()).':V';
|
|
$handler($columnname, $row);
|
|
}
|
|
}
|
|
|
|
function VT520_updateCVColumnList($columnname, $row) {
|
|
$db = PearDatabase::getInstance();
|
|
$sql = 'update vtiger_cvcolumnlist set columnname=? where cvid=? and columnindex=?';
|
|
$params = array($columnname, $row->cvid,$row->columnindex);
|
|
$db->pquery($sql, $params);
|
|
}
|
|
|
|
function VT520_updateADVColumnList($columnname, $row) {
|
|
$db = PearDatabase::getInstance();
|
|
$sql = 'update vtiger_cvadvfilter set columnname=? where cvid=? and columnindex=?';
|
|
$params = array($columnname, $row->cvid,$row->columnindex);
|
|
$db->pquery($sql, $params);
|
|
}
|
|
|
|
function VT520_queryGeneratorMigration() {
|
|
$db = PearDatabase::getInstance();
|
|
$sql = "delete from vtiger_cvadvfilter where columnname IS NULL or columnname='';";
|
|
$db->pquery($sql, array());
|
|
$user = Users::getActiveAdminUser();
|
|
$sql = "select vtiger_customview.cvid,columnindex,entitytype from vtiger_customview inner join ".
|
|
"vtiger_cvcolumnlist on vtiger_customview.cvid=vtiger_cvcolumnlist.cvid where entitytype !=".
|
|
"'Accounts' and columnname like 'vtiger_account:accountname:accountname%';";
|
|
VT520_migrateCustomview($sql,'Accounts', $user, VT520_updateCVColumnList);
|
|
$sql = "select vtiger_customview.cvid,columnindex,entitytype from vtiger_customview inner join ".
|
|
"vtiger_cvcolumnlist on vtiger_customview.cvid=vtiger_cvcolumnlist.cvid where entitytype !=".
|
|
"'Contacts' and columnname like 'vtiger_contactdetails:lastname:lastname:%';";
|
|
VT520_migrateCustomview($sql,'Contacts', $user, VT520_updateCVColumnList);
|
|
$sql = "select vtiger_customview.cvid,columnindex,entitytype from vtiger_customview inner join ".
|
|
"vtiger_cvcolumnlist on vtiger_customview.cvid=vtiger_cvcolumnlist.cvid where entitytype not in ".
|
|
"('Products','HelpDesk','Faq') and columnname like 'vtiger_products:productname:productname%';";
|
|
VT520_migrateCustomview($sql,'Products', $user, VT520_updateCVColumnList);
|
|
|
|
$sql = "select vtiger_customview.cvid,columnindex,entitytype from vtiger_customview inner join ".
|
|
"vtiger_cvcolumnlist on vtiger_customview.cvid=vtiger_cvcolumnlist.cvid where entitytype not in ".
|
|
"('Products','HelpDesk','Faq') and columnname like 'vtiger_quotes:quoteid:quote_id%';";
|
|
VT520_migrateCustomview($sql,'SalesOrder', $user, VT520_updateCVColumnList);
|
|
|
|
|
|
$sql = "select vtiger_customview.cvid,columnindex,entitytype from vtiger_customview inner join ".
|
|
"vtiger_cvadvfilter on vtiger_customview.cvid=vtiger_cvadvfilter.cvid where entitytype !=".
|
|
"'Accounts' and columnname like 'vtiger_account:accountname:accountname%';";
|
|
VT520_migrateCustomview($sql,'Accounts', $user, VT520_updateADVColumnList);
|
|
$sql = "select vtiger_customview.cvid,columnindex,entitytype from vtiger_customview inner join ".
|
|
"vtiger_cvadvfilter on vtiger_customview.cvid=vtiger_cvadvfilter.cvid where entitytype !=".
|
|
"'Contacts' and columnname like 'vtiger_contactdetails:lastname:lastname:%';";
|
|
VT520_migrateCustomview($sql,'Contacts', $user, VT520_updateADVColumnList);
|
|
$sql = "select vtiger_customview.cvid,columnindex,entitytype from vtiger_customview inner join ".
|
|
"vtiger_cvadvfilter on vtiger_customview.cvid=vtiger_cvadvfilter.cvid where entitytype not in ".
|
|
"('Products','HelpDesk','Faq') and columnname like 'vtiger_products:productname:productname%';";
|
|
VT520_migrateCustomview($sql,'Products', $user, VT520_updateADVColumnList);
|
|
$sql = "select vtiger_customview.cvid,columnindex,entitytype from vtiger_customview inner join ".
|
|
"vtiger_cvcolumnlist on vtiger_customview.cvid=vtiger_cvcolumnlist.cvid where entitytype not in ".
|
|
"('Products','HelpDesk','Faq') and columnname like 'vtiger_quotes:quoteid:quote_id%';";
|
|
VT520_migrateCustomview($sql,'SalesOrder', $user, VT520_updateADVColumnList);
|
|
|
|
$tabId = getTabid('Contacts');
|
|
$sql = "select fieldid from vtiger_field where tabid=? and fieldname='birthday';";
|
|
$params = array($tabId);
|
|
$result = $db->pquery($sql, $params);
|
|
$it = new SqlResultIterator($db, $result);
|
|
$fieldId = null;
|
|
foreach($it as $row) {
|
|
$fieldId = $row->fieldid;
|
|
}
|
|
if(!empty($fieldId)) {
|
|
$sql = "update vtiger_field set typeofdata = 'D~O' where fieldid=?;";
|
|
$params = array($fieldId);
|
|
$result = $db->pquery($sql, $params);
|
|
} else {
|
|
echo '
|
|
<tr width="100%">
|
|
<td width="25%">Failure</td>
|
|
<td width="5%"><font color="red"> F </font></td>
|
|
<td width="70%">Failed to change typeofdata of birthday field</td>
|
|
</tr>';
|
|
}
|
|
|
|
$tabId = getTabid('Documents');
|
|
$sql = "select fieldid from vtiger_field where tabid=? and fieldname='filesize';";
|
|
$params = array($tabId);
|
|
$result = $db->pquery($sql, $params);
|
|
$it = new SqlResultIterator($db, $result);
|
|
$fieldId = null;
|
|
foreach($it as $row) {
|
|
$fieldId = $row->fieldid;
|
|
}
|
|
if(!empty($fieldId)) {
|
|
$sql = "update vtiger_field set typeofdata = 'I~O' where fieldid=?;";
|
|
$params = array($fieldId);
|
|
$result = $db->pquery($sql, $params);
|
|
} else {
|
|
echo '
|
|
<tr width="100%">
|
|
<td width="25%">Failure</td>
|
|
<td width="5%"><font color="red"> F </font></td>
|
|
<td width="70%">Failed to change typeofdata of filesize field</td>
|
|
</tr>';
|
|
}
|
|
}
|
|
|
|
VT520_queryGeneratorMigration();
|
|
|
|
|
|
ExecuteQuery("ALTER table vtiger_asteriskincomingcalls ADD COLUMN refuid varchar(255)");
|
|
ExecuteQuery("
|
|
CREATE TABLE vtiger_asteriskincomingevents (
|
|
uid varchar(255) NOT NULL,
|
|
channel varchar(100) default NULL,
|
|
from_number bigint(20) default NULL,
|
|
from_name varchar(100) default NULL,
|
|
to_number bigint(20) default NULL,
|
|
callertype varchar(100) default NULL,
|
|
timer int(20) default NULL,
|
|
flag varchar(3) default NULL,
|
|
pbxrecordid int(19) default NULL,
|
|
relcrmid int(19) default NULL,
|
|
PRIMARY KEY (uid))");
|
|
// Alter vtiger_relcriteria table to store groupid and column_condition
|
|
$adb->query("ALTER TABLE vtiger_relcriteria ADD COLUMN groupid INT DEFAULT 1");
|
|
$adb->query("ALTER TABLE vtiger_relcriteria ADD COLUMN column_condition VARCHAR(256) DEFAULT 'and'");
|
|
|
|
// Create table to store Reports Advanced Filters Condition Grouping information
|
|
$adb->query("CREATE TABLE IF NOT EXISTS vtiger_relcriteria_grouping
|
|
(groupid INT NOT NULL, queryid INT, group_condition VARCHAR(256), condition_expression TEXT, PRIMARY KEY(groupid, queryid))");
|
|
|
|
// Migration queries to migrate existing data to the required state (Storing Condition Expression in the newly created table for existing Reports)
|
|
// Remove all unwanted condition columns added (where column name is empty)
|
|
$adb->pquery("DELETE FROM vtiger_relcriteria WHERE (columnname IS NULL OR trim(columnname) = '')",
|
|
array());
|
|
$maxReportIdResult = $adb->query("SELECT max(reportid) as max_reportid FROM vtiger_report");
|
|
if($adb->num_rows($maxReportIdResult) > 0) {
|
|
$maxReportId = $adb->query_result($maxReportIdResult, 0, 'max_reportid');
|
|
if(!empty($maxReportId) && $maxReportId > 0) {
|
|
for($i=1; $i<=$maxReportId; ++$i) {
|
|
$reportId = $i;
|
|
$relcriteriaResult = $adb->pquery("SELECT * FROM vtiger_relcriteria WHERE queryid=?", array($reportId)); // Pick all the conditions of a Report
|
|
$noOfConditions = $adb->num_rows($relcriteriaResult);
|
|
if($noOfConditions > 0) {
|
|
$columnIndexArray = array();
|
|
$maxColumnIndex = 0;
|
|
for($j=0;$j<$noOfConditions; $j++) {
|
|
$columnIndex = $adb->query_result($relcriteriaResult, $j, 'columnindex');
|
|
if($maxColumnIndex < $columnIndex) {
|
|
$maxColumnIndex = $columnIndex;
|
|
}
|
|
$columnIndexArray[] = $columnIndex;
|
|
}
|
|
$conditionExpression = implode(' and ', $columnIndexArray);
|
|
$adb->pquery('INSERT INTO vtiger_relcriteria_grouping VALUES(?,?,?,?)',
|
|
array(1, $reportId, '', $conditionExpression));
|
|
|
|
$adb->pquery("UPDATE vtiger_relcriteria SET column_condition='' WHERE columnindex=? AND queryid=?", array($maxColumnIndex,$reportId));
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
ExecuteQuery("CREATE TABLE IF NOT EXISTS `vtiger_customerportal_tabs` ( `tabid` int(19) NOT NULL, `visible` int(1)
|
|
default '1', `sequence` int(1) default NULL, PRIMARY KEY (`tabid`)) ENGINE=InnoDB
|
|
DEFAULT CHARSET=utf8");
|
|
|
|
if(Vtiger_Utils::CheckTable('vtiger_customerportal_prefs')) {
|
|
ExecuteQuery("ALTER TABLE `vtiger_customerportal_prefs` DROP INDEX tabid_idx");
|
|
ExecuteQuery("ALTER TABLE `vtiger_customerportal_prefs` ADD PRIMARY KEY (`tabid`)");
|
|
|
|
} else {
|
|
ExecuteQuery("CREATE TABLE IF NOT EXISTS `vtiger_customerportal_prefs` ( `tabid` int(11) NOT NULL, `prefkey`
|
|
varchar(100) default NULL, `prefvalue` int(20) default NULL, PRIMARY KEY (`tabid`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8");
|
|
}
|
|
|
|
//Adding Block to email fields
|
|
$blockquery = "select blockid from vtiger_blocks where blocklabel = ?";
|
|
$blockres = $adb->pquery($blockquery,array('LBL_EMAIL_INFORMATION'));
|
|
$blockid = $adb->query_result($blockres,0,'blockid');
|
|
$fieldsqueryuitype8 = 'update vtiger_field set block=? where tabid=? and uitype=8';
|
|
$adb->pquery($fieldsqueryuitype8,array($blockid,$email_Tabid));
|
|
|
|
$fieldsqueryuitype12 = 'update vtiger_field set block=? where tabid=? and uitype=12';
|
|
$adb->pquery($fieldsqueryuitype12,array($blockid,$email_Tabid));
|
|
|
|
$fieldsqueryuitype1 = 'update vtiger_field set block=? where tabid=? and uitype=1';
|
|
$adb->pquery($fieldsqueryuitype1,array($blockid,$email_Tabid));
|
|
|
|
$fieldsqueryuitype16 = 'update vtiger_field set block=? where tabid=? and uitype=16';
|
|
$adb->pquery($fieldsqueryuitype16,array($blockid,$email_Tabid));
|
|
|
|
require_once 'include/utils/utils.php';
|
|
|
|
$sql = 'delete from vtiger_field where tablename=? and fieldname=? and tabid=?';
|
|
$params = array('vtiger_seactivityrel','parent_id',getTabid('Emails'));
|
|
$adb->pquery($sql,$params);
|
|
$sql = 'update vtiger_field set uitype=?,displaytype=? where tablename=? and'.
|
|
' fieldname=? and tabid=?';
|
|
$params = array('357',1,'vtiger_emaildetails','parent_id',getTabid('Emails'));
|
|
$adb->pquery($sql,$params);
|
|
$sql = 'update vtiger_field set block=(select blockid from vtiger_blocks where '.
|
|
"blocklabel=?) where tablename=?";
|
|
$params = array('LBL_EMAIL_INFORMATION','vtiger_emaildetails');
|
|
$adb->pquery($sql,$params);
|
|
|
|
// Correct the type
|
|
ExecuteQuery("UPDATE vtiger_field SET typeofdata='V~O' WHERE typeofdata='V~0'");
|
|
|
|
function VT520_manageIndexes() {
|
|
$db = PearDatabase::getInstance();
|
|
ExecuteQuery("ALTER TABLE vtiger_potential ADD INDEX `vt_pot_sales_stage_amount_idx` ".
|
|
"(amount, sales_stage)");
|
|
$result = $db->pquery("SELECT COUNT(1) as count FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema = ".
|
|
"'$db->dbName' AND table_name = 'vtiger_potential' AND index_name = ".
|
|
"'potential_potentialid_idx'",array());
|
|
$count = $db->query_result($result, 0, 'count');
|
|
if($count > 0) {
|
|
ExecuteQuery("ALTER TABLE vtiger_potential DROP INDEX `potential_potentialid_idx`");
|
|
}
|
|
$result = $db->pquery("SELECT COUNT(1) as count FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema = ".
|
|
"'$db->dbName' AND table_name = 'vtiger_potential' AND index_name = ".
|
|
"'potential_accountid_idx'",array());
|
|
$count = $db->query_result($result, 0, 'count');
|
|
if($count > 0) {
|
|
ExecuteQuery("ALTER TABLE vtiger_potential DROP INDEX `potential_accountid_idx`");
|
|
ExecuteQuery("ALTER TABLE vtiger_potential ADD INDEX `potential_relatedto_idx` ".
|
|
"(related_to)");
|
|
}
|
|
$result = $db->pquery("SELECT COUNT(1) as count FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema = ".
|
|
"'$db->dbName' AND table_name = 'vtiger_crmentity' AND index_name = ".
|
|
"'crmentity_smownerid_idx'",array());
|
|
$count = $db->query_result($result, 0, 'count');
|
|
if($count > 0) {
|
|
ExecuteQuery("ALTER TABLE vtiger_crmentity DROP INDEX `crmentity_smownerid_idx`");
|
|
}
|
|
$result = $db->pquery("SELECT COUNT(1) as count FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema = ".
|
|
"'$db->dbName' AND table_name = 'vtiger_crmentity' AND index_name = ".
|
|
"'crmentity_smownerid_deleted_idx'",array());
|
|
$count = $db->query_result($result, 0, 'count');
|
|
if($count > 0) {
|
|
ExecuteQuery("ALTER TABLE vtiger_crmentity DROP INDEX `crmentity_smownerid_deleted_idx`");
|
|
}
|
|
ExecuteQuery("ALTER TABLE vtiger_crmentity ADD INDEX `crm_ownerid_del_setype_idx` ".
|
|
"(smownerid,deleted,setype)");
|
|
}
|
|
|
|
function VT520_fieldCleanUp() {
|
|
$db = PearDatabase::getInstance();
|
|
$result = $db->pquery("SELECT fieldid,typeofdata FROM vtiger_field WHERE fieldname = ".
|
|
"'birthday' AND tabid = '".getTabid('Contacts')."'",array());
|
|
$fieldId = $db->query_result($result, 0, 'fieldid');
|
|
$typeOfData = $db->query_result($result, 0, 'typeofdata');
|
|
$typeInfo = explode('~', $typeOfData);
|
|
$mandatory = $typeInfo[1];
|
|
ExecuteQuery("update vtiger_field set typeofdata='D~$mandatory' where fieldid=$fieldId");
|
|
$result = $db->pquery("SELECT fieldid,typeofdata FROM vtiger_field WHERE fieldname = ".
|
|
"'eventstatus' AND tabid = '".getTabid('Calendar')."'",array());
|
|
$fieldId = $db->query_result($result, 0, 'fieldid');
|
|
$typeOfData = $db->query_result($result, 0, 'typeofdata');
|
|
$typeInfo = explode('~', $typeOfData);
|
|
$type = $typeInfo[0];
|
|
ExecuteQuery("update vtiger_field set typeofdata='$type~O' where fieldid=$fieldId");
|
|
}
|
|
|
|
VT520_manageIndexes();
|
|
VT520_fieldCleanUp();
|
|
|
|
ExecuteQuery("DROP TABLE IF EXISTS vtiger_asteriskoutgoingcalls");
|
|
|
|
$migrationlog->debug("\n\nDB Changes from 5.1.0 to 5.2.0 RC -------- Ends \n\n");
|
|
|
|
?>
|