/modules/Migration/DBChanges/42P2_to_50.php
PHP | 4235 lines | 3026 code | 739 blank | 470 comment | 73 complexity | cf57535456211787245b75e9bfe19fc4 MD5 | raw file
Possible License(s): Apache-2.0, LGPL-3.0, LGPL-2.1, GPL-2.0, GPL-3.0
Large files files are truncated, but you can click here to view the full file
- <?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.
- *
- ********************************************************************************/
- ini_set("memory_limit","32M");
- global $php_max_execution_time;
- set_time_limit($php_max_execution_time);
- //This file is used to modify the database from 4.2Patch2 to 5.0 Alpha release
- global $conn;
- global $migrationlog;
- global $query_count, $success_query_count, $failure_query_count;
- global $success_query_array, $failure_query_array;
- $migrationlog->debug("\n\nDB Changes from 4.2.x to 5.0 GA -------- Starts \n\n");
- //Added to put prefix vtiger_ in some of the columns in tables which are used for CV and Reports and field -- 23-06-06
- $migrationlog->debug("Going to rename the table names with prefix vtiger_");
- include("modules/Migration/rename_tables.php");
- $migrationlog->debug("Renaming the table names with prefix vtiger_ has been finished");
- $migrationlog->debug("Database Modifications for 4.2 Patch2 ==> 5.0(Alpha) Dev 3 Starts here.");
- //These changes have been made in 4.2.3. The following queries have been included who has run the migration from 4.2 Patch2
- $wordtemp = $conn->getColumnNames("vtiger_wordtemplates");
- if(is_array($wordtemp) && !in_array("templateid",$wordtemp))
- {
- $wordtemplate_query1 = "alter table vtiger_wordtemplates DROP PRIMARY KEY";
- Execute($wordtemplate_query1);
- $wordtemplate_query3 = "alter table vtiger_wordtemplates add column templateid integer(19) unsigned auto_increment primary key FIRST";
- Execute($wordtemplate_query3);
- }
- //upto this added to modify the wordtemplates table which will be in the case of migrate from 4.2 Path2.
- /****************** 5.0(Alpha) dev version 1 Database changes -- Starts*********************/
- //Added the vtiger_announcement table creation to avoid the error
- $ann_query = "CREATE TABLE vtiger_announcement (
- creatorid int8 NOT NULL,
- announcement text,
- title varchar(255) default NULL,
- time timestamp NOT NULL default CURRENT_TIMESTAMP,
- PRIMARY KEY (creatorid)
- )";
- Execute($ann_query);
- //Added Primay Keys for the left out tables
- $alter_array1 = Array(
- "alter table vtiger_activity_reminder ADD PRIMARY KEY (activity_id,recurringid)",
- "alter table vtiger_activitygrouprelation ADD PRIMARY KEY (activityid)",
- "alter table vtiger_cvadvfilter ADD PRIMARY KEY (cvid,columnindex)",
- "alter table vtiger_cvcolumnlist ADD PRIMARY KEY (cvid,columnindex)",
- "alter table vtiger_cvstdfilter ADD PRIMARY KEY (cvid)",
- "alter table vtiger_def_org_field ADD PRIMARY KEY (fieldid)",
- "alter table vtiger_leadgrouprelation ADD PRIMARY KEY (leadid)",
- "alter table vtiger_leadgrouprelation drop key leadgrouprelation_IDX0",
- "alter table vtiger_profile2field ADD PRIMARY KEY (profileid,fieldid)",
- "alter table vtiger_profile2standardpermissions ADD PRIMARY KEY (profileid,tabid,Operation)",
- "alter table vtiger_profile2standardpermissions drop index idx_prof2stad",
- "alter table vtiger_profile2utility ADD PRIMARY KEY (profileid,tabid,activityid)",
- "alter table vtiger_profile2utility drop index idx_prof2utility",
- "alter table vtiger_relcriteria ADD PRIMARY KEY (queryid,columnindex)",
- "alter table vtiger_reportdatefilter ADD PRIMARY KEY (datefilterid)",
- "alter table vtiger_reportdatefilter DROP INDEX reportdatefilter_IDX0",
- "alter table vtiger_reportsortcol ADD PRIMARY KEY (sortcolid,reportid)",
- "alter table vtiger_reportsummary ADD PRIMARY KEY (reportsummaryid,summarytype,columnname)",
- "drop table vtiger_role2action",
- "drop table vtiger_role2tab",
- "alter table vtiger_selectcolumn ADD PRIMARY KEY (queryid,columnindex)",
- "alter table vtiger_ticketgrouprelation ADD PRIMARY KEY (ticketid)",
- "alter table vtiger_ticketstracktime ADD PRIMARY KEY (ticket_id)",
- "alter table vtiger_users2group ADD PRIMARY KEY (groupname,userid)",
- "alter table vtiger_users2group DROP INDEX idx_users2group",
- );
- foreach($alter_array1 as $query)
- {
- Execute($query);
- }
- //Tables vtiger_profile2globalpermissions, vtiger_actionmapping creation
- $create_sql1 ="CREATE TABLE vtiger_profile2globalpermissions (profileid int8 NOT NULL, globalactionid int8 NOT NULL, globalactionpermission int8 default NULL, PRIMARY KEY (profileid, globalactionid)) ";
- Execute($create_sql1);
- $create_sql2 = "CREATE TABLE vtiger_actionmapping (actionid int8 NOT NULL, actionname varchar(200) NOT NULL, securitycheck int8 default NULL, PRIMARY KEY (actionid,actionname))";
- Execute($create_sql2);
- //For all Profiles, insert the following entries into vtiger_profile2global permissions table:
- $sql = 'select * from vtiger_profile';
- $res = $conn->query($sql);
- $noofprofiles = $conn->num_rows($res);
- for($i=0;$i<$noofprofiles;$i++)
- {
- $profile_id = $conn->query_result($res,$i,'profileid');
- $sql1 = "insert into vtiger_profile2globalpermissions values ($profile_id,1,1)";
- $sql2 = "insert into vtiger_profile2globalpermissions values ($profile_id,2,1)";
- Execute($sql1);
- Execute($sql2);
- }
- //Removing entries for Dashboard and Home module from vtiger_profile2standardpermissions table
- $del_query1 = "delete from vtiger_profile2standardpermissions where tabid in(1,3)";
- Execute($del_query1);
- //For all Profile do the following insert into vtiger_profile2utility table:
- $sql = 'select * from vtiger_profile';
- $res = $conn->query($sql);
- $noofprofiles = $conn->num_rows($res);
- /* Commented by Don. Handled below
- for($i=0;$i<$noofprofiles;$i++)
- {
- $profile_id = $conn->query_result($res,$i,'profileid');
- $sql1 = "insert into vtiger_profile2utility values ($profile_id,4,7,0)";
- $sql2 = "insert into vtiger_profile2utility values ($profile_id,7,9,0)";
- Execute($sql1);
- Execute($sql2);
- }
- */
- //Insert Values into action mapping table:
- $actionmapping_array = Array(
- "insert into vtiger_actionmapping values(0,'Save',0)",
- "insert into vtiger_actionmapping values(1,'EditView',0)",
- "insert into vtiger_actionmapping values(2,'Delete',0)",
- "insert into vtiger_actionmapping values(3,'index',0)",
- "insert into vtiger_actionmapping values(4,'DetailView',0)",
- "insert into vtiger_actionmapping values(5,'Import',0)",
- "insert into vtiger_actionmapping values(6,'Export',0)",
- "insert into vtiger_actionmapping values(8,'Merge',0)",
- "insert into vtiger_actionmapping values(1,'VendorEditView',1)",
- "insert into vtiger_actionmapping values(4,'VendorDetailView',1)",
- "insert into vtiger_actionmapping values(0,'SaveVendor',1)",
- "insert into vtiger_actionmapping values(2,'DeleteVendor',1)",
- "insert into vtiger_actionmapping values(1,'PriceBookEditView',1)",
- "insert into vtiger_actionmapping values(4,'PriceBookDetailView',1)",
- "insert into vtiger_actionmapping values(0,'SavePriceBook',1)",
- "insert into vtiger_actionmapping values(2,'DeletePriceBook',1)",
- "insert into vtiger_actionmapping values(1,'SalesOrderEditView',1)",
- "insert into vtiger_actionmapping values(4,'SalesOrderDetailView',1)",
- "insert into vtiger_actionmapping values(0,'SaveSalesOrder',1)",
- "insert into vtiger_actionmapping values(2,'DeleteSalesOrder',1)",
- "insert into vtiger_actionmapping values(9,'ConvertLead',0)",
- "insert into vtiger_actionmapping values(1,'DetailViewAjax',1)",
- "insert into vtiger_actionmapping values(1,'QuickCreate',1)",
- "insert into vtiger_actionmapping values(4,'TagCloud',1)"
- );
- foreach($actionmapping_array as $query)
- {
- Execute($query);
- }
- //Added two columns in vtiger_field table to construct the quickcreate form dynamically
- $alter_array2 = Array(
- "ALTER TABLE vtiger_field ADD column quickcreate int(10) after typeofdata",
- "ALTER TABLE vtiger_field ADD column quickcreatesequence int(19) after quickcreate",
- );
- foreach($alter_array2 as $query)
- {
- Execute($query);
- }
- $update_array1 = Array(
- "UPDATE vtiger_field SET quickcreate = 1,quickcreatesequence = 0",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 2 and fieldlabel = 'Potential Name'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 2 WHERE tabid = 2 and fieldlabel = 'Account Name'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 3 WHERE tabid = 2 and fieldlabel = 'Expected Close Date'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 4 WHERE tabid = 2 and fieldlabel = 'Sales Stage'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 5 WHERE tabid = 2 and fieldlabel = 'Amount'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 4 and fieldlabel = 'First Name'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 2 WHERE tabid = 4 and fieldlabel = 'Last Name'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 3 WHERE tabid = 4 and fieldlabel = 'Account Name'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 4 WHERE tabid = 4 and fieldlabel = 'Office Phone'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 5 WHERE tabid = 4 and fieldlabel = 'Email'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 6 and fieldlabel = 'Account Name'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 2 WHERE tabid = 6 and fieldlabel = 'Phone'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 3 WHERE tabid = 6 and fieldlabel = 'Website'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 7 and fieldlabel = 'First Name'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 2 WHERE tabid = 7 and fieldlabel = 'Last Name'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 3 WHERE tabid = 7 and fieldlabel = 'Company'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 4 WHERE tabid = 7 and fieldlabel = 'Phone'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 5 WHERE tabid = 7 and fieldlabel = 'Email'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 8 and fieldlabel = 'Subject'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 9 and fieldlabel = 'Subject'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 2 WHERE tabid = 9 and fieldlabel = 'Start Date & Time'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 10 and fieldlabel = 'Subject'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 2 WHERE tabid = 10 and fieldlabel = 'Date & Time Sent'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 13 and fieldlabel = 'Title'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 2 WHERE tabid = 13 and fieldlabel = 'Description'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 3 WHERE tabid = 13 and fieldlabel = 'Priority'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 14 and fieldlabel = 'Product Name'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 2 WHERE tabid = 14 and fieldlabel = 'Product Code'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 3 WHERE tabid = 14 and fieldlabel = 'Product Category'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 16 and fieldlabel = 'Subject'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 2 WHERE tabid = 16 and fieldlabel = 'Start Date & Time'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 3 WHERE tabid = 16 and fieldlabel = 'Activity Type'",
- "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 4 WHERE tabid = 16 and fieldlabel = 'Duration'",
- );
- foreach($update_array1 as $query)
- {
- Execute($query);
- }
- //Added for the "Color By User in Calendar " which has been contributed by Cesar
- $alter_query1 = "ALTER TABLE vtiger_users ADD cal_color VARCHAR(25) DEFAULT '#E6FAD8' AFTER user_hash";
- Execute($alter_query1);
- //code contributed by Fredy for color vtiger_priority
- $newfieldid = $conn->getUniqueID("vtiger_field");
- $insert_query1 = "insert into vtiger_field values (16,".$newfieldid.",'priority','activity',1,15,'taskpriority','Priority',1,0,0,100,17,1,1,'V~O',1,'')";
- Execute($insert_query1);
- //Added on 23-12-2005 which is missed from Fredy's contribution for Color vtiger_priority
- populateFieldForSecurity('16',$newfieldid);
- $activity_cols = $conn->getColumnNames("vtiger_activity");
- if(is_array($activity_cols) && !in_array("priority",$activity_cols))
- {
- $activity_alter_query = "alter table vtiger_activity add column priority varchar(150) default NULL";
- Execute($activity_alter_query);
- }
- //Code contributed by Raju for better emailing
- /*
- $insert_array1 = Array(
- "insert into vtiger_field values (10,".$conn->getUniqueID("vtiger_field").",'crmid','seactivityrel',1,'357','parent_id','Related To',1,0,0,100,1,2,1,'I~O',1,'')",
- "insert into vtiger_field values (10,".$conn->getUniqueID("vtiger_field").",'subject','activity',1,'2','subject','Subject',1,0,0,100,1,3,1,'V~M',0,1)",
- "insert into vtiger_field values (10,".$conn->getUniqueID("vtiger_field").",'filename','emails',1,'61','filename','Attachment',1,0,0,100,1,4,1,'V~O',1,'')",
- "insert into vtiger_field values (10,".$conn->getUniqueID("vtiger_field").",'description','emails',1,'19','description','Description',1,0,0,100,1,5,1,'V~O',1,'')",
- );
- */
- //commented the above array as that queries are wrong queries -- changed on 23-12-2005
- $insert_array1 = array(
- "update vtiger_field set uitype='357' where tabid=10 and fieldname='parent_id' and tablename='vtiger_seactivityrel'",
- "update vtiger_field set sequence=1 where tabid=10 and fieldname in ('parent_id','subject','filename','description')",
- "update vtiger_field set block=2 where tabid=10 and fieldname='parent_id'",
- "update vtiger_field set block=3 where tabid=10 and fieldname='subject'",
- "update vtiger_field set block=4 where tabid=10 and fieldname='filename'",
- "update vtiger_field set block=5 where tabid=10 and fieldname='description'",
- );
- foreach($insert_array1 as $query)
- {
- Execute($query);
- }
- //code contributed by mike to rearrange the home page
- $alter_query2 = "alter table vtiger_users add column homeorder varchar(255) default 'ALVT,PLVT,QLTQ,CVLVT,HLT,OLV,GRT,OLTSO,ILTI' after date_format";
- Execute($alter_query2);
- //Added one column in vtiger_invoice table to include 'Contact Name' vtiger_field in Invoice module
- $alter_query3 = "ALTER TABLE vtiger_invoice ADD column contactid int(19) after customerno";
- Execute($alter_query3);
- $newfieldid = $conn->getUniqueID("vtiger_field");
- $insert_query2 = "insert into vtiger_field values (23,".$newfieldid.",'contactid','invoice',1,'57','contact_id','Contact Name',1,0,0,100,4,1,1,'I~O',1,'')";
- Execute($insert_query2);
- //Added on 23-12-2005 because we must populate vtiger_field entries in vtiger_profile2field and vtiger_def_org_field if we add a vtiger_field in vtiger_field table
- populateFieldForSecurity('23',$newfieldid);
- //changes made to fix the bug in Address Information block of Accounts and Contacs module
- $update_array2 = Array(
- "UPDATE vtiger_field SET fieldlabel='Billing City', sequence=5 WHERE tabid=6 and fieldname='bill_city'",
- "UPDATE vtiger_field SET fieldlabel='Billing State', sequence=7 WHERE tabid=6 and fieldname='bill_state'",
- "UPDATE vtiger_field SET fieldlabel='Billing Code', sequence=9 WHERE tabid=6 and fieldname='bill_code'",
- "UPDATE vtiger_field SET fieldlabel='Billing Country', sequence=11 WHERE tabid=6 and fieldname='bill_country'",
- "UPDATE vtiger_field SET fieldlabel='Shipping City', sequence=6 WHERE tabid=6 and fieldname='ship_city'",
- "UPDATE vtiger_field SET fieldlabel='Shipping State', sequence=8 WHERE tabid=6 and fieldname='ship_state'",
- "UPDATE vtiger_field SET fieldlabel='Shipping Code', sequence=10 WHERE tabid=6 and fieldname='ship_code'",
- "UPDATE vtiger_field SET fieldlabel='Shipping Country', sequence=12 WHERE tabid=6 and fieldname='ship_country'",
- "UPDATE vtiger_field SET fieldlabel='Mailing City', sequence=5 WHERE tabid=4 and fieldname='mailingcity'",
- "UPDATE vtiger_field SET fieldlabel='Mailing State', sequence=7 WHERE tabid=4 and fieldname='mailingstate'",
- "UPDATE vtiger_field SET fieldlabel='Mailing Zip', sequence=9 WHERE tabid=4 and fieldname='mailingzip'",
- "UPDATE vtiger_field SET fieldlabel='Mailing Country', sequence=11 WHERE tabid=4 and fieldname='mailingcountry'",
- "UPDATE vtiger_field SET fieldlabel='Other City', sequence=6 WHERE tabid=4 and fieldname='othercity'",
- "UPDATE vtiger_field SET fieldlabel='Other State', sequence=8 WHERE tabid=4 and fieldname='otherstate'",
- "UPDATE vtiger_field SET fieldlabel='Other Zip', sequence=10 WHERE tabid=4 and fieldname='otherzip'",
- "UPDATE vtiger_field SET fieldlabel='Other Country', sequence=12 WHERE tabid=4 and fieldname='othercountry'",
- );
- foreach($update_array2 as $query)
- {
- Execute($query);
- }
- //Added vtiger_field emailoptout in vtiger_account table
- $newfieldid = $conn->getUniqueID("vtiger_field");
- $insert_query3 = "insert into vtiger_field values (6,".$newfieldid.",'emailoptout','account',1,'56','emailoptout','Email Opt Out',1,0,0,100,17,1,1,'C~O',1,'')";
- Execute($insert_query3);
- //Added on 23-12-2005 because we must populate vtiger_field entries in vtiger_profile2field and vtiger_def_org_field if we add a vtiger_field in vtiger_field table
- populateFieldForSecurity('6',$newfieldid);
- //Added on 22-12-2005
- $alter_query4 = "alter table vtiger_account add column emailoptout varchar(3) default 0";
- Execute($alter_query4);
- $update_array3 = Array(
- "update vtiger_field set sequence=18 where tabid=6 and fieldname ='assigned_user_id'",
- "update vtiger_field set sequence=19 where tabid=6 and fieldname ='createdtime'",
- "update vtiger_field set sequence=19 where tabid=6 and fieldname ='modifiedtime'",
- );
- foreach($update_array3 as $query)
- {
- Execute($query);
- }
- //create table vtiger_moduleowners to assign the module and corresponding owners
- $create_query2 = "CREATE TABLE vtiger_moduleowners (
- `tabid` int(19) NOT NULL default '0',
- `user_id` varchar(11) NOT NULL,
- PRIMARY KEY (`tabid`),
- KEY `moduleowners_tabid_user_id_idx` (`tabid`,`user_id`)
- ) ENGINE=InnoDB";
- /*
- $create_query2 = "CREATE TABLE vtiger_moduleowners
- (
- `tabid` int(19) NOT NULL default '0',
- `user_id` varchar(11) NOT NULL default '',
- PRIMARY KEY (`tabid`),
- CONSTRAINT `fk_ModuleOwners` FOREIGN KEY (`tabid`) REFERENCES `vtiger_tab` (`tabid`) ON DELETE CASCADE
- ) TYPE=InnoDB";
- */
- Execute($create_query2);
- //Populated the default entries for vtiger_moduleowners which is created newly
- $module_array = Array(
- 'Potentials',
- 'Contacts',
- 'Accounts',
- 'Leads',
- 'Notes',
- 'Activities',
- 'Emails',
- 'HelpDesk',
- 'Products',
- 'Faq',
- 'Vendor',
- 'PriceBook',
- 'Quotes',
- 'Orders',
- 'SalesOrder',
- 'Invoice',
- 'Reports'
- );
- foreach($module_array as $mod)
- {
- $query = "insert into vtiger_moduleowners values(".$this->localGetTabID($mod).",1)";
- Execute($query);
- }
- //Changes made to include status vtiger_field in Activity Quickcreate Form
- $update_array4 = Array(
- "UPDATE vtiger_field SET quickcreate=0,quickcreatesequence=3 WHERE tabid=16 and fieldname='eventstatus'",
- "UPDATE vtiger_field SET quickcreate=0,quickcreatesequence=4 WHERE tabid=16 and fieldname='activitytype'",
- "UPDATE vtiger_field SET quickcreate=0,quickcreatesequence=5 WHERE tabid=16 and fieldname='duration_hours'",
- "UPDATE vtiger_field SET quickcreate=0,quickcreatesequence=3 WHERE tabid=9 and fieldname='taskstatus'",
- );
- foreach($update_array4 as $query)
- {
- Execute($query);
- }
- //Table 'inventory_tandc' added newly to include Inventory Terms &Conditions
- $create_query1 = "CREATE TABLE vtiger_inventory_tandc(id INT(19),type VARCHAR(30) NOT NULL,tandc LONGTEXT default NULL,PRIMARY KEY(id))";
- Execute($create_query1);
- $insert_query4 = "insert into vtiger_inventory_tandc values('".$conn->getUniqueID('vtiger_inventory_tandc')."','Inventory',' ')";
- Execute($insert_query4);
- /****************** 5.0(Alpha) dev version 1 Database changes -- Ends*********************/
- /****************** 5.0(Alpha) dev version 2 Database changes -- Starts*********************/
- $query1 = "ALTER TABLE vtiger_leadaddress change lane lane varchar(250)";
- Execute($query1);
- $rename_table_array1 = Array(
- "update vtiger_field set tablename='vtiger_customerdetails' where tabid=4 and fieldname in ('portal','support_start_date','support_end_date')",
- "alter table vtiger_PortalInfo drop foreign key fk_PortalInfo",
- "rename table vtiger_PortalInfo to vtiger_portalinfo",
- "alter table vtiger_portalinfo add CONSTRAINT `fk_portalinfo` FOREIGN KEY (`id`) REFERENCES `vtiger_contactdetails` (`contactid`) ON DELETE CASCADE",
- "alter table vtiger_CustomerDetails drop foreign key fk_CustomerDetails",
- "rename table vtiger_CustomerDetails to vtiger_customerdetails",
- "alter table vtiger_customerdetails add CONSTRAINT `fk_customerdetails` FOREIGN KEY (`customerid`) REFERENCES `vtiger_contactdetails` (`contactid`) ON DELETE CASCADE"
- );
- foreach($rename_table_array1 as $query)
- {
- Execute($query);
- }
- $query2 = "create table vtiger_ownernotify(crmid int(19),smownerid int(19),flag int(3))";
- Execute($query2);
- //Form the vtiger_role_map_array as vtiger_roleid=>name mapping array
- $sql = "select * from vtiger_role";
- $res = $conn->query($sql);
- $role_map_array = Array();
- for($i=0;$i<$conn->num_rows($res);$i++)
- {
- $roleid = $conn->query_result($res,$i,'roleid');
- $name = $conn->query_result($res,$i,'name');
- $role_map_array[$roleid] = $name;
- }
- $conn->println("List of Roles (roleid => name) ==> ");$conn->println($role_map_array);
- //echo '<pre> List of vtiger_roles :';print_r($role_map_array);echo '</pre>';
- //Before delete the vtiger_role take a backup array for the table vtiger_user2role
- $sql = "select * from vtiger_user2role";
- $res = $conn->query($sql);
- $user2role_array = array();
- for($i=0;$i<$conn->num_rows($res);$i++)
- {
- $userid = $conn->query_result($res,$i,'userid');
- $roleid = $conn->query_result($res,$i,'roleid');
- $user2role_array[$userid] = $roleid;
- }
- $conn->println("Users 2 Roles (userid => roleid) ==> ");$conn->println($user2role_array);
- //echo '<pre> List of vtiger_user2role : (userid => vtiger_roleid)';print_r($user2role_array);echo '</pre>';
- //Delete the vtiger_role entries
- $sql = "truncate vtiger_role";
- Execute($sql);
- $query3 = "alter table vtiger_user2role drop FOREIGN KEY fk_user2role2";
- Execute($query3);
- //4,5 th are the Extra added queries
- $alter_query_array1 = Array(
- "alter table vtiger_user2role change roleid roleid varchar(255)",
- "alter table vtiger_role2profile change roleid roleid varchar(255)",
- "alter table vtiger_role CHANGE roleid roleid varchar(255)",
- "alter table vtiger_role2profile drop PRIMARY KEY",
- "alter table vtiger_role2profile ADD PRIMARY KEY (roleid,profileid)"
- );
- foreach($alter_query_array1 as $query)
- {
- Execute($query);
- }
- $query4 = "ALTER TABLE vtiger_user2role ADD CONSTRAINT fk_user2role2 FOREIGN KEY (roleid) REFERENCES vtiger_role(roleid) ON DELETE CASCADE";
- Execute($query4);
- $alter_query_array2 = Array(
- "alter table vtiger_role CHANGE name rolename varchar(200)",
- "alter table vtiger_role DROP description",
- "alter table vtiger_role add parentrole varchar(255)",
- "alter table vtiger_role add depth int(19)"
- );
- foreach($alter_query_array2 as $query)
- {
- Execute($query);
- }
- $query5 = "insert into vtiger_role values('H1','Organisation','H1',0)";
- Execute($query5);
- //include("include/utils/UserInfoUtil.php");
- //Create vtiger_role based on vtiger_role_map_array values and form the new_role_map_array with old vtiger_roleid and new vtiger_roleid
- foreach($role_map_array as $roleid => $rolename)
- {
- $parentRole = 'H1';
- if($rolename == 'standard_user')
- {
- $rs = $conn->query("select * from vtiger_role where rolename='administrator'");
- $parentRole = $conn->query_result($rs,0,'roleid');
- }
- $empty_array = array(""=>"");
- $new_role_id = localcreateRole($rolename,$parentRole,$empty_array);
- $new_role_map_array[$roleid] = $new_role_id;
- }
- $conn->println("Roles (oldroleid => newroleid) ==> ");$conn->println($new_role_map_array);
- //Before insert the new entry we should remove the old entries -- added on 06-06-06
- $user2role_del = "truncate vtiger_user2role";
- Execute($user2role_del);
- //First we will insert the old values from vtiger_user2role_array to vtiger_user2role table and then update the new vtiger_role id
- foreach($user2role_array as $userid => $roleid)
- {
- $sql = "insert into vtiger_user2role (userid, roleid) values(".$userid.",'".$new_role_map_array[$roleid]."')";
- Execute($sql);
- }
- //Commented the following loop as we have backup the vtiger_user2role and insert the entries with the new rold id using new_role_map_array above
- //Update the vtiger_user2role table with new vtiger_roleid
- /*
- foreach($new_role_map_array as $old_roleid => $new_roleid)
- {
- $update_user2role = "update vtiger_user2role set vtiger_roleid='".$new_roleid."' where vtiger_roleid=".$old_roleid;
- Execute($update_user2role);
- }
- */
- //Update the vtiger_role2profile table with new vtiger_roleid
- foreach($new_role_map_array as $old_roleid => $new_roleid)
- {
- $update_role2profile = "update vtiger_role2profile set roleid='".$new_roleid."' where roleid=".$old_roleid;
- Execute($update_role2profile);
- }
- //Group Migration:
- //Step 1 : form and group_map_array as groupname => description from vtiger_groups table
- //Step 2 : form an vtiger_users2group_map_array array as userid => groupname from vtiger_users2group table
- //Step 3 : delete all entries from vtiger_groups table and enter new values from group_map_array
- //Step 4 : drop the table vtiger_users2group and create new table
- //Step 5 : put entries to vtiger_users2group table based on vtiger_users2group_map_array. Here get the groupid from vtiger_groups table based on groupname
- //Step 1 : Form the group_map_array as groupname => description
- $sql = "select * from vtiger_groups";
- $res = $conn->query($sql);
- $group_map_array = Array();
- for($i=0;$i<$conn->num_rows($res);$i++)
- {
- $name = $conn->query_result($res,$i,'name');
- $desc = $conn->query_result($res,$i,'description');
- $group_map_array[$name] = $desc;
- }
- $conn->println("List of Groups (name => description) ==> ");$conn->println($group_map_array);
- //echo '<pre>List of Groups : ';print_r($group_map_array);echo '</pre>';
- //Step 2 : form an vtiger_users2group_map_array array as userid => groupname from vtiger_users2group table
- $sql = "select * from vtiger_users2group";
- $res = $conn->query($sql);
- $users2group_map_array = Array();
- for($i=0;$i<$conn->num_rows($res);$i++)
- {
- $groupname = $conn->query_result($res,$i,'groupname');
- $userid = $conn->query_result($res,$i,'userid');
- $users2group_map_array[$userid] = $groupname;
- }
- $conn->println("Users 2 Groups (userid => groupname) ==> ");$conn->println($users2group_map_array);
- //echo '<pre>List of vtiger_users2group : ';print_r($users2group_map_array);echo '</pre>';
- //Step 3 : delete all entries from vtiger_groups table
- $sql = "truncate vtiger_groups";
- Execute($sql);
- $alter_query_array3 = Array(
- "alter table vtiger_users2group drop FOREIGN KEY fk_users2group",
- "alter table vtiger_leadgrouprelation drop FOREIGN KEY fk_leadgrouprelation2",
- "alter table vtiger_activitygrouprelation drop FOREIGN KEY fk_activitygrouprelation2",
- "alter table vtiger_ticketgrouprelation drop FOREIGN KEY fk_ticketgrouprelation2",
- "alter table vtiger_groups drop PRIMARY KEY"
- );
- foreach($alter_query_array3 as $query)
- {
- Execute($query);
- }
- //2 nd query is the Extra added query
- //Adding columns in group table:
- $alter_query_array4 = Array(
- "alter table vtiger_groups add column groupid int(19) FIRST",
- "alter table vtiger_groups change name groupname varchar(100)",
- "alter table vtiger_groups ADD PRIMARY KEY (groupid)",
- "alter table vtiger_groups add index (groupname)"
- );
- foreach($alter_query_array4 as $query)
- {
- Execute($query);
- }
- //Moved the create table queries for vtiger_group2grouprel, vtiger_group2role, vtiger_group2rs from the end of this block
- //Added on 06-06-06
- $query8 = "CREATE TABLE vtiger_group2grouprel (
- `groupid` int(19) NOT NULL,
- `containsgroupid` int(19) NOT NULL,
- PRIMARY KEY (`groupid`,`containsgroupid`)
- ) ENGINE=InnoDB";
- /*
- $query8 = "CREATE TABLE vtiger_group2grouprel
- (
- `groupid` int(19) NOT NULL default '0',
- `containsgroupid` int(19) NOT NULL default '0',
- PRIMARY KEY (`groupid`,`containsgroupid`),
- CONSTRAINT `fk_group2grouprel1` FOREIGN KEY (`groupid`) REFERENCES `vtiger_groups` (`groupid`) ON DELETE CASCADE
- ) TYPE=InnoDB";
- */
- Execute($query8);
- //Added on 06-06-06
- $query9 = "CREATE TABLE vtiger_group2role (
- `groupid` int(19) NOT NULL,
- `roleid` varchar(255) NOT NULL,
- PRIMARY KEY (`groupid`,`roleid`)
- ) ENGINE=InnoDB";
- /*
- $query9 = "CREATE TABLE vtiger_group2role
- (
- `groupid` int(19) NOT NULL default '0',
- `roleid` varchar(255) NOT NULL default '',
- PRIMARY KEY (`groupid`,`roleid`),
- CONSTRAINT `fk_group2role1` FOREIGN KEY (`groupid`) REFERENCES `vtiger_groups` (`groupid`) ON DELETE CASCADE
- ) TYPE=InnoDB";
- */
- Execute($query9);
- //Added on 06-06-06
- $query10 = "CREATE TABLE vtiger_group2rs (
- `groupid` int(19) NOT NULL,
- `roleandsubid` varchar(255) NOT NULL,
- PRIMARY KEY (`groupid`,`roleandsubid`)
- ) ENGINE=InnoDB";
- /*
- $query10 = "CREATE TABLE vtiger_group2rs
- (
- `groupid` int(19) NOT NULL default '0',
- `roleandsubid` varchar(255) NOT NULL default '',
- PRIMARY KEY (`groupid`,`roleandsubid`),
- CONSTRAINT `fk_group2rs1` FOREIGN KEY (`groupid`) REFERENCES `vtiger_groups` (`groupid`) ON DELETE CASCADE
- ) TYPE=InnoDB";
- */
- Execute($query10);
- //Insert all the retrieved old values to the new vtiger_groups table ie., create new vtiger_groups
- foreach($group_map_array as $groupname => $description)
- {
- $empty_array = array(
- "groups" => array(""=>""),
- "roles" => array(""=>""),
- "rs" => array(""=>""),
- "users" => array(""=>"")
- );
- $groupid = createGroup($groupname,$empty_array,$description);
- $group_name_id_mapping[$groupname] = $groupid;
- }
- $conn->println("List of Groups Created (groupname => groupid) ==> ");$conn->println($group_name_id_mapping);
- //Copy all mappings in a user2grop table in a array;
- //Step 4 : Drop and again create users2group
- $query6 = "drop table vtiger_users2group";
- Execute($query6);
- //Added on 06-06-06
- $query7 = "CREATE TABLE vtiger_users2group (
- `groupid` int(19) NOT NULL,
- `userid` int(19) NOT NULL,
- PRIMARY KEY (`groupid`,`userid`),
- KEY `users2group_groupname_uerid_idx` (`groupid`,`userid`)
- ) ENGINE=InnoDB";
- /*
- $query7 = "CREATE TABLE vtiger_users2group
- (
- `groupid` int(19) NOT NULL default '0',
- `userid` int(19) NOT NULL default '0',
- PRIMARY KEY (`groupid`,`userid`),
- CONSTRAINT `fk_users2group1` FOREIGN KEY (`groupid`) REFERENCES `groups` (`groupid`) ON DELETE CASCADE
- ) TYPE=InnoDB";
- */
- Execute($query7);
- //Step 5 : put entries to vtiger_users2group table based on vtiger_users2group_map_array. Here get the groupid from vtiger_groups table based on groupname
- foreach($users2group_map_array as $userid => $groupname)
- {
- //$groupid = $conn->query_result($conn->query("select * from vtiger_groups where groupname='".$groupname."'"),0,'groupid');
- $sql = "insert into vtiger_users2group (groupid,userid) values(".$group_name_id_mapping[$groupname].",".$userid.")";
- Execute($sql);
- }
- $alter_query_array5 = Array(
- "alter table vtiger_leadgrouprelation ADD CONSTRAINT fk_leadgrouprelation2 FOREIGN KEY (groupname) REFERENCES vtiger_groups(groupname) ON DELETE CASCADE",
- "ALTER TABLE vtiger_activitygrouprelation ADD CONSTRAINT fk_activitygrouprelation2 FOREIGN KEY (groupname) REFERENCES vtiger_groups(groupname) ON DELETE CASCADE",
- "ALTER TABLE vtiger_ticketgrouprelation ADD CONSTRAINT fk_ticketgrouprelation2 FOREIGN KEY (groupname) REFERENCES vtiger_groups(groupname) ON DELETE CASCADE"
- );
- foreach($alter_query_array5 as $query)
- {
- Execute($query);
- }
- //Moved the create table queries for vtiger_group2grouprel, vtiger_group2role, vtiger_group2rs to before creatinf the Group ie., before call the createGroup
- /***Added to include decimal places for amount vtiger_field in vtiger_potential table --by Mangai 15-Nov-2005***/
- $query11 = "ALTER TABLE vtiger_potential change amount amount decimal(10,2)";
- Execute($query11);
- /****************** 5.0(Alpha) dev version 2 Database changes -- Ends*********************/
- /****************** 5.0(Alpha) dev version 3 Database changes -- Starts*********************/
- //Drop the column company_name from vtiger_vendor table ---- modified by Mickie on 18-11-2005
- $altersql1 = "alter table vtiger_vendor drop column company_name";
- Execute($altersql1);
- $altersql2 = "alter table vtiger_vendor change column name vendorname varchar(100) default NULL";
- $conn->query($altersql2);
- Execute("update vtiger_field set fieldname='vendorname', columnname='vendorname' where tabid=18 and fieldname='name'");
- //TODO (check): Remove this company_name entry from the vtiger_field table if it already exists
- //Migration for Default Organisation Share -- Added by Don on 20-11-2005
- $query1 = "CREATE TABLE vtiger_org_share_action_mapping (
- `share_action_id` int(19) NOT NULL default '0',
- `share_action_name` varchar(200) NOT NULL default '',
- PRIMARY KEY (`share_action_id`,`share_action_name`)
- ) TYPE=InnoDB ";
- Execute($query1);
- $query2 = "CREATE TABLE vtiger_org_share_action2tab (
- `share_action_id` int(19) NOT NULL default '0',
- `tabid` int(19) NOT NULL default '0',
- PRIMARY KEY (`share_action_id`,`tabid`),
- CONSTRAINT `fk_org_share_action2tab` FOREIGN KEY (`share_action_id`) REFERENCES `vtiger_org_share_action_mapping` (`share_action_id`) ON DELETE CASCADE
- ) TYPE=InnoDB";
- Execute($query2);
- $query3 = "alter table vtiger_def_org_share add column editstatus int(19)";
- Execute($query3);
- $query4 = "delete from vtiger_def_org_share where tabid in(8,14,15,18,19)";
- Execute($query4);
- //Inserting values into org share action mapping
- $insert_query_array1 = Array(
- "insert into vtiger_org_share_action_mapping values(0,'Public: Read Only')",
- "insert into vtiger_org_share_action_mapping values(1,'Public:Read,Create/Edit')",
- "insert into vtiger_org_share_action_mapping values(2,'Public: Read, Create/Edit, Delete')",
- "insert into vtiger_org_share_action_mapping values(3,'Private')",
- "insert into vtiger_org_share_action_mapping values(4,'Hide Details')",
- "insert into vtiger_org_share_action_mapping values(5,'Hide Details and Add Events')",
- "insert into vtiger_org_share_action_mapping values(6,'Show Details')",
- "insert into vtiger_org_share_action_mapping values(7,'Show Details and Add Events')"
- );
- foreach($insert_query_array1 as $query)
- {
- Execute($query);
- }
- //Inserting for all vtiger_tabs
- $def_org_tabid=Array(2,4,6,7,9,10,13,16,20,21,22,23,26);
- foreach($def_org_tabid as $def_tabid)
- {
- $insert_query_array2 = Array(
- "insert into vtiger_org_share_action2tab values(0,".$def_tabid.")",
- "insert into vtiger_org_share_action2tab values(1,".$def_tabid.")",
- "insert into vtiger_org_share_action2tab values(2,".$def_tabid.")",
- "insert into vtiger_org_share_action2tab values(3,".$def_tabid.")"
- );
- foreach($insert_query_array2 as $query)
- {
- Execute($query);
- }
- }
- $insert_query_array3 = Array(
- "insert into vtiger_org_share_action2tab values(4,17)",
- "insert into vtiger_org_share_action2tab values(5,17)",
- "insert into vtiger_org_share_action2tab values(6,17)",
- "insert into vtiger_org_share_action2tab values(7,17)"
- );
- foreach($insert_query_array3 as $query)
- {
- Execute($query);
- }
- $query_array1 = Array(
- "insert into vtiger_def_org_share values(9,17,7,0)",
- "update vtiger_def_org_share set editstatus=0",
- "update vtiger_def_org_share set editstatus=2 where tabid=4",
- "update vtiger_def_org_share set editstatus=1 where tabid=9",
- "update vtiger_def_org_share set editstatus=2 where tabid=16"
- );
- foreach($query_array1 as $query)
- {
- Execute($query);
- }
- /****************** 5.0(Alpha) dev version 3 Database changes -- Ends*********************/
- $migrationlog->debug("Database Modifications for 5.0(Alpha) Dev 3 ==> 5.0 Alpha starts here.");
- //echo "<br><br><b>Database Modifications for 5.0(Alpha) Dev3 ==> 5.0 Alpha starts here.....</b><br>";
- $alter_query_array6 = Array(
- "ALTER TABLE vtiger_users ADD column activity_view VARCHAR(25) DEFAULT 'Today' AFTER homeorder",
- "ALTER TABLE vtiger_activity ADD column notime VARCHAR(3) NOT NULL DEFAULT '0' AFTER location"
- );
- foreach($alter_query_array6 as $query)
- {
- Execute($query);
- }
- $newfieldid = $conn->getUniqueID("vtiger_field");
- $insert_query = "insert into vtiger_field values (9,".$newfieldid.",'notime','activity',1,56,'notime','No Time',1,0,0,100,20,1,3,'C~O',1,'')";
- Execute($insert_query);
- populateFieldForSecurity('9',$newfieldid);
- $newfieldid = $conn->getUniqueID("vtiger_field");
- $insert_query = "insert into vtiger_field values (16,".$newfieldid.",'notime','activity',1,56,'notime','No Time',1,0,0,100,18,1,1,'C~O',1,'')";
- Execute($insert_query);
- populateFieldForSecurity('16',$newfieldid);
- $alter_query_array7 = Array(
- "alter table vtiger_vendor add column pobox varchar(30) after state",
- "alter table vtiger_leadaddress add column pobox varchar(30) after state",
- "alter table vtiger_accountbillads add column pobox varchar(30) after state",
- "alter table vtiger_accountshipads add column pobox varchar(30) after state",
- "alter table vtiger_contactaddress add column mailingpobox varchar(30) after mailingstate",
- "alter table vtiger_contactaddress add column otherpobox varchar(30) after otherstate",
- "alter table vtiger_quotesbillads add column bill_pobox varchar(30) after bill_street",
- "alter table vtiger_quotesshipads add column ship_pobox varchar(30) after ship_street",
- "alter table vtiger_pobillads add column bill_pobox varchar(30) after bill_street",
- "alter table vtiger_poshipads add column ship_pobox varchar(30) after ship_street",
- "alter table vtiger_sobillads add column bill_pobox varchar(30) after bill_street",
- "alter table vtiger_soshipads add column ship_pobox varchar(30) after ship_street",
- "alter table vtiger_invoicebillads add column bill_pobox varchar(30) after bill_street",
- "alter table vtiger_invoiceshipads add column ship_pobox varchar(30) after ship_street"
- );
- foreach($alter_query_array7 as $query)
- {
- Execute($query);
- }
- $newfieldid = $conn->getUniqueID("vtiger_field");
- $insert_query = "insert into vtiger_field values (23,".$newfieldid.",'bill_pobox','invoicebillads',1,'1','bill_pobox','Billing Po Box',1,0,0,100,3,2,1,'V~O',1,'')";
- Execute($insert_query);
- populateFieldForSecurity('23',$newfieldid);
- $newfieldid = $conn->getUniqueID("vtiger_field");
- $insert_query = "insert into vtiger_field values (23,".$newfieldid.",'ship_pobox','invoiceshipads',1,'1','ship_pobox','Shipping Po Box',1,0,0,100,4,2,1,'V~O',1,'')";
- Execute($insert_query);
- populateFieldForSecurity('23',$newfieldid);
- $newfieldid = $conn->getUniqueID("vtiger_field");
- $insert_query = "insert into vtiger_field values (6,".$newfieldid.",'pobox','accountbillads',1,'1','bill_pobox','Billing Po Box',1,0,0,100,3,2,1,'V~O',1,'')";
- Execute($insert_query);
- populateFieldForSecurity('6',$newfieldid);
- $newfieldid = $conn->getUniqueID("vtiger_field");
- $insert_query = "insert into vtiger_field values (6,".$newfieldid.",'pobox','accountshipads',1,'1','ship_pobox','Shipping Po Box',1,0,0,100,4,2,1,'V~O',1,'')";
- Execute($insert_query);
- populateFieldForSecurity('6',$newfieldid);
- $newfieldid = $conn->getUniqueID("vtiger_field");
- $insert_query = "insert into vtiger_field values (7,".$newfieldid.",'pobox','leadaddress',1,'1','pobox','Po Box',1,0,0,100,2,2,1,'V~O',1,'')";
- Execute($insert_query);
- populateFieldForSecurity('7',$newfieldid);
- $newfieldid = $conn->getUniqueID("vtiger_field");
- $insert_query = "insert into vtiger_field values (4,".$newfieldid.",'mailingpobox','contactaddress',1,'1','mailingpobox','Mailing Po Box',1,0,0,100,3,2,1,'V~O',1,'')";
- Execute($insert_query);
- populateFieldForSecurity('4',$newfieldid);
- $newfieldid = $conn->getUniqueID("vtiger_field");
- $insert_query = "insert into vtiger_field values (4,".$newfieldid.",'otherpobox','contactaddress',1,'1','otherpobox','Other Po Box',1,0,0,100,4,2,1,'V~O',1,'')";
- Execute($insert_query);
- populateFieldForSecurity('4',$newfieldid);
- $newfieldid = $conn->getUniqueID("vtiger_field");
- $insert_query = "insert into vtiger_field values (18,".$newfieldid.",'pobox','vendor',1,'1','pobox','Po Box',1,0,0,100,2,2,1,'V~O',1,'')";
- Execute($insert_query);
- populateFieldForSecurity('18',$newfieldid);
- $newfieldid = $conn->getUniqueID("vtiger_field");
- $insert_query = "insert into vtiger_field values (20,".$newfieldid.",'bill_pobox','quotesbillads',1,'1','bill_pobox','Billing Po Box',1,0,0,100,3,2,1,'V~O',1,'')";
- Execute($insert_query);
- populateFieldForSecurity('20',$newfieldid);
- $newfieldid = $conn->getUniqueID("vtiger_field");
- $insert_query = "insert into vtiger_field values (20,".$newfieldid.",'ship_pobox','quotesshipads',1,'1','ship_pobox','Shipping Po Box',1,0,0,100,4,2,1,'V~O',1,'')";
- Execute($insert_query);
- populateFieldForSecurity('20',$newfieldid);
- $newfieldid = $conn->getUniqueID("vtiger_field");
- $insert_query = "insert into vtiger_field values (21,".$newfieldid.",'bill_pobox','pobillads',1,'1','bill_pobox','Billing Po Box',1,0,0,100,3,2,1,'V~O',1,'')";
- Execute($insert_query);
- populateFieldForSecurity('21',$newfieldid);
- $newfieldid = $conn->getUniqueID("vtiger_field");
- $insert_query = "insert into vtiger_field values (21,".$newfieldid.",'ship_pobox','poshipads',1,'1','ship_pobox','Shipping Po Box',1,0,0,100,4,2,1,'V~O',1,'')";
- Execute($insert_query);
- populateFieldForSecurity('21',$newfieldid);
- $newfieldid = $conn->getUniqueID("vtiger_field");
- $insert_query = "insert into vtiger_field values (22,".$newfieldid.",'bill_pobox','sobillads',1,'1','bill_pobox','Billing Po Box',1,0,0,100,3,2,1,'V~O',1,'')";
- Execute($insert_query);
- populateFieldForSecurity('22',$newfieldid);
- $newfieldid = $conn->getUniqueID("vtiger_field");
- $insert_query = "insert into vtiger_field values (22,".$newfieldid.",'ship_pobox','soshipads',1,'1','ship_pobox','Shipping Po Box',1,0,0,100,4,2,1,'V~O',1,'')";
- Execute($insert_query);
- populateFieldForSecurity('22',$newfieldid);
- $fieldname =array('bill_city','bill_state','bill_code','bill_country','ship_city','ship_state','ship_code','ship_country');
- $tablename = array('accountbillads','quotesbillads','pobillads','sobillads','invoicebillads','accountshipads','quotesshipads','poshipads','soshipads','invoiceshipads');
- $sequence = array(5,7,9,11,6,8,10,12);
- $k = 0;
- $n = 0;
- for($j = 0;$j < 8;$j++)
- {
- if($j == 4)
- $n = $n+5;
- for($i = 0;$i < 5;$i++)
- {
- $query1 = "update vtiger_field set sequence=".$sequence[$j]." where tablename='".$tablename[$n+$i]."' && fieldname='".$fieldname[$j]."'";
- Execute($query1);
- }
- }
- $fieldname = array('code','city','country','state');
- $tablename = 'leadaddress';
- $sequence = array(3,4,5,6);
- for($i = 0;$i < 4;$i++)
- {
- $query2 = "update vtiger_field set sequence=".$sequence[$i]." where tablename='".$tablename."' && fieldname='".$fieldname[$i]."'";
- Execute($query2);
- }
- $fieldname = array('city','state','postalcode','country');
- $tablename = 'vendor';
- $sequence = array(3,4,5,6);
- for($i = 0;$i < 4;$i++)
- {
- $query3 = "update vtiger_field set sequence=".$sequence[$i]." where tablename='".$tablename."' && fieldname='".$fieldname[$i]."'";
- Execute($query3);
- }
- $fieldname = array('mailingcity','othercity','mailingstate','otherstate','mailingzip','otherzip','mailingcountry','othercountry');
- $tablename = 'contactaddress';
- $sequence = array(5,6,7,8,9,10,11,12);
- for($i = 0;$i < 8;$i++)
- {
- $query = "update vtiger_field set sequence=".$sequence[$i]." where tablename='".$tablename."' && fieldname='".$fieldname[$i]."'";
- Execute($query);
- }
- $query_array1 = Array(
- "update vtiger_field set tablename='vtiger_crmentity' where tabid=10 and fieldname='description'",
- "update vtiger_field set tablename='vtiger_attachments' where tabid=10 and fieldname='filename'",
- "drop table vtiger_emails",
- "alter table vtiger_activity drop column description",
- "update vtiger_field set tablename='vtiger_crmentity' where tabid in (9,16) and fieldname='description'",
- "update vtiger_tab set name='PurchaseOrder',tablabel='PurchaseOrder' where tabid=21",
- "update vtiger_tab set presence=0 where tabid=22 and name='SalesOrder'",
- "delete from vtiger_actionmapping where actionname='SalesOrderDetailView'",
- "delete from vtiger_actionmapping where actionname='SalesOrderEditView'",
- "delete from vtiger_actionmapping where actionname='SaveSalesOrder'",
- "delete from vtiger_actionmapping where actionname='DeleteSalesOrder'",
- //"insert into vtiger_field values (13,".$conn->getUniqueID("vtiger_field").",'filename','vtiger_attachments',1,'61','filename','Attachment',1,0,0,100,12,2,1,'V~O',0,1)",
- "alter table vtiger_troubletickets add column filename varchar(50) default NULL after title"
- );
- foreach($query_array1 as $query)
- {
- Execute($query);
- }
- $newfieldid = $conn->getUniqueID("vtiger_field");
- $insert_query = "insert into vtiger_field values (13,".$newfieldid.",'filename','vtiger_attachments',1,'61','filename','Attachment',1,0,0,100,12,2,1,'V~O',0,1)";
- Execute($insert_query);
- populateFieldForSecurity('13',$newfieldid);
- $create_query3 = "create table vtiger_parenttab(parenttabid int(19) not null, parenttab_label varchar(100) not null, sequence int(10) not null, visible int(2) not null default '0', Primary Key(parenttabid))";
- Execute($create_query3);
- $create_query4 = "create table vtiger_parenttabrel(parenttabid int(3) not null, tabid int(3) not null,sequence int(3) not null)";
- Execute($create_query4);
- $insert_query_array4 = Array(
- "insert into vtiger_parenttab values(1,'My Home Page',1,0),(2,'Marketing',2,0),(3,'Sales',3,0),(4,'Support',4,0),(5,'Analytics',5,0),(6,'Inventory',6,0), (7,'Tools',7,0),(8,'Settings',8,0)",
- "insert into vtiger_parenttabrel values(1,9,2),(1,17,3),(1,10,4),(1,3,1),(3,7,1),(3,6,2),(3,4,3),(3,2,4),(3,20,5),(3,22,6),(3,23,7),(3,14,8),(3,19,9),(3,8,10),(4,13,1),(4,15,2),(4,6,3),(4,4,4),(4,14,5),(4,8,6),(5,1,1),(5,25,2),(6,14,1), (6,18,2), (6,19,3), (6,21,4), (6,22,5), (6,20,6), (6,23,7), (7,24,1), (7,27,2), (7,8,3), (2,6,2), (2,4,3) "
- );
- foreach($insert_query_array4 as $query)
- {
- Execute($query);
- }
- $create_query5 = "CREATE TABLE vtiger_blocks ( blockid int(19) NOT NULL, tabid int(19) NOT NULL, blocklabel varchar(100) NOT NULL, sequence int(19) NOT NULL, show_title int(2) NOT NULL, visible int(2) NOT NULL DEFAULT 0, create_view int(2) NOT NULL DEFAULT 0, edit_view int(2) NOT NULL DEFAULT 0, detail_view int(2) NOT NULL DEFAULT 0, PRIMARY KEY (blockid))";
- Execute($create_query5);
- $update_query_array1 = Array(
- "update vtiger_field set block=2 where tabid=2 and block=5",
- "update vtiger_field set block=3 where tabid=2 and block=2",
- //"update vtiger_field set block=4 where tabid=4 and block=1",
- "update vtiger_field set block=5 where tabid=4 and block=5",
- "update vtiger_field set block=6 where tabid=4 and block=4",//Modified on 24-04-06
- "update vtiger_field set block=4 where tabid=4 and block=1",
- "update vtiger_field set block=7 where tabid=4 and block=2",
- "update vtiger_field set block=8 where tabid=4 and block=3",
- "update vtiger_field set block=9 where tabid=6 and block=1",
- "update vtiger_field set block=10 where tabid=6 and block=5",
- "update vtiger_field set block=11 where tabid=6 and block=2",
- "update vtiger_field set block=12 where tabid=6 and block=3",
- "update vtiger_field set block=13 where tabid=7 and block=1",
- "update vtiger_field set block=14 where tabid=7 and block=5",
- "update vtiger_field set block=15 where tabid=7 and block=2",
- "update vtiger_field set block=16 where tabid=7 and block=3",
- "update vtiger_field set block=17 where tabid=8 and block=1",
- "update vtiger_field set block=17 where tabid=8 and block=2",
- "update vtiger_field set block=18 where tabid=8 and block=3",
- "update vtiger_field set block=19 where tabid=9 and block=1",
- "update vtiger_field set block=19 where tabid=9 and block=7",
- "update vtiger_field set block=20 where tabid=9 and block=2",
- "update vtiger_field set block=21 where tabid=10 and block=1",
- "update vtiger_field set block=22 where tabid=10 and block=2",
- "update vtiger_field set block=23 where tabid=10 and block=3",
- "update vtiger_field set block=23 where tabid=10 and block=4",
- "update vtiger_field set block=24 where tabid=10 and block=5",
- "update vtiger_field set block=25 where tabid=13 and block=1",
- "update vtiger_field set block=26 where tabid=13 and block=2",
- "update vtiger_field set block=27 where tabid=13 and block=5",
- "update vtiger_field set block=28 where tabid=13 and block=3",
- "update vtiger_field set block=29 where tabid=13 and block=4",
- "update vtiger_field set block=30 where tabid=13 and block=6",
- "update vtiger_field set block=31 where tabid=14 and block=1",
- "update vtiger_field set block=32 where tabid=14 and block=2",
- "update vtiger_field set block=33 where tabid=14 and block=3",
- "update vtiger_field set block=34 where tabid=14 and block=5",
- "update vtiger_field set block=35 where tabid=14 and block=6",
- "update vtiger_field set block=36 where tabid=14 and block=4",
- "update vtiger_field set block=37 where tabid=15 and block=1",
- "update vtiger_field set block=38 where tabid=15 and block=2",
- "update vtiger_field set block=39 where tabid=15 and block=3",
- "u…
Large files files are truncated, but you can click here to view the full file