| [ Index ] |
|
Code source de vtiger CRM 5.0.2 |
1 <?php 2 /********************************************************************************* 3 ** The contents of this file are subject to the vtiger CRM Public License Version 1.0 4 * ("License"); You may not use this file except in compliance with the License 5 * The Original Code is: vtiger CRM Open Source 6 * The Initial Developer of the Original Code is vtiger. 7 * Portions created by vtiger are Copyright (C) vtiger. 8 * All Rights Reserved. 9 * 10 ********************************************************************************/ 11 12 ini_set("memory_limit","32M"); 13 set_time_limit(600); 14 15 //This file is used to modify the database from 4.2Patch2 to 5.0 Alpha release 16 17 global $conn; 18 global $migrationlog; 19 global $query_count, $success_query_count, $failure_query_count; 20 global $success_query_array, $failure_query_array; 21 22 //Added to put prefix vtiger_ in some of the columns in tables which are used for CV and Reports and field -- 23-06-06 23 $migrationlog->debug("Going to rename the table names with prefix vtiger_"); 24 include ("modules/Migration/ModifyDatabase/rename_tables.php"); 25 $migrationlog->debug("Renaming the table names with prefix vtiger_ has been finished"); 26 27 28 29 30 31 32 $migrationlog->debug("Database Modifications for 4.2 Patch2 ==> 5.0(Alpha) Dev 3 Starts here."); 33 34 35 36 //These changes have been made in 4.2.3. The following queries have been included who has run the migration from 4.2 Patch2 37 $wordtemp = $conn->getColumnNames("vtiger_wordtemplates"); 38 if(is_array($wordtemp) && !in_array("templateid",$wordtemp)) 39 { 40 $wordtemplate_query1 = "alter table vtiger_wordtemplates DROP PRIMARY KEY"; 41 Execute($wordtemplate_query1); 42 43 $wordtemplate_query3 = "alter table vtiger_wordtemplates add column templateid integer(19) unsigned auto_increment primary key FIRST"; 44 Execute($wordtemplate_query3); 45 } 46 //upto this added to modify the wordtemplates table which will be in the case of migrate from 4.2 Path2. 47 48 49 50 /****************** 5.0(Alpha) dev version 1 Database changes -- Starts*********************/ 51 52 53 //Added the vtiger_announcement table creation to avoid the error 54 $ann_query = "CREATE TABLE vtiger_announcement ( 55 `creatorid` int(19) NOT NULL, 56 `announcement` text, 57 `title` varchar(255) default NULL, 58 `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, 59 PRIMARY KEY (`creatorid`), 60 KEY `announcement_UK01` (`creatorid`) 61 ) ENGINE=InnoDB DEFAULT CHARSET=latin1"; 62 Execute($ann_query); 63 64 //Added Primay Keys for the left out tables 65 $alter_array1 = Array( 66 "alter table vtiger_activity_reminder ADD PRIMARY KEY (activity_id,recurringid)", 67 "alter table vtiger_activitygrouprelation ADD PRIMARY KEY (activityid)", 68 "alter table vtiger_cvadvfilter ADD PRIMARY KEY (cvid,columnindex)", 69 "alter table vtiger_cvcolumnlist ADD PRIMARY KEY (cvid,columnindex)", 70 "alter table vtiger_cvstdfilter ADD PRIMARY KEY (cvid)", 71 "alter table vtiger_def_org_field ADD PRIMARY KEY (fieldid)", 72 "alter table vtiger_leadgrouprelation ADD PRIMARY KEY (leadid)", 73 "alter table vtiger_leadgrouprelation drop key leadgrouprelation_IDX0", 74 "alter table vtiger_profile2field ADD PRIMARY KEY (profileid,fieldid)", 75 "alter table vtiger_profile2standardpermissions ADD PRIMARY KEY (profileid,tabid,Operation)", 76 "alter table vtiger_profile2standardpermissions drop index idx_prof2stad", 77 "alter table vtiger_profile2utility ADD PRIMARY KEY (profileid,tabid,activityid)", 78 "alter table vtiger_profile2utility drop index idx_prof2utility", 79 "alter table vtiger_relcriteria ADD PRIMARY KEY (queryid,columnindex)", 80 "alter table vtiger_reportdatefilter ADD PRIMARY KEY (datefilterid)", 81 "alter table vtiger_reportdatefilter DROP INDEX reportdatefilter_IDX0", 82 "alter table vtiger_reportsortcol ADD PRIMARY KEY (sortcolid,reportid)", 83 "alter table vtiger_reportsummary ADD PRIMARY KEY (reportsummaryid,summarytype,columnname)", 84 "drop table vtiger_role2action", 85 "drop table vtiger_role2tab", 86 "alter table vtiger_selectcolumn ADD PRIMARY KEY (queryid,columnindex)", 87 "alter table vtiger_ticketgrouprelation ADD PRIMARY KEY (ticketid)", 88 "alter table vtiger_ticketstracktime ADD PRIMARY KEY (ticket_id)", 89 "alter table vtiger_users2group ADD PRIMARY KEY (groupname,userid)", 90 "alter table vtiger_users2group DROP INDEX idx_users2group", 91 ); 92 foreach($alter_array1 as $query) 93 { 94 Execute($query); 95 } 96 97 //Tables vtiger_profile2globalpermissions, vtiger_actionmapping creation 98 99 $create_sql1 ="CREATE TABLE vtiger_profile2globalpermissions (`profileid` int(19) NOT NULL, `globalactionid` int(19) NOT NULL, `globalactionpermission` int(19) default NULL, PRIMARY KEY (`profileid`,`globalactionid`), KEY `idx_profile2globalpermissions` (`profileid`,`globalactionid`)) ENGINE=InnoDB DEFAULT CHARSET=latin1"; 100 101 Execute($create_sql1); 102 103 $create_sql2 = "CREATE TABLE vtiger_actionmapping ( 104 `actionid` int(19) NOT NULL default '0', 105 `actionname` varchar(200) NOT NULL default '', 106 `securitycheck` int(19) default NULL, 107 PRIMARY KEY (`actionid`,`actionname`) 108 ) TYPE=InnoDB"; 109 Execute($create_sql2); 110 111 //For all Profiles, insert the following entries into vtiger_profile2global permissions table: 112 $sql = 'select * from vtiger_profile'; 113 $res = $conn->query($sql); 114 $noofprofiles = $conn->num_rows($res); 115 116 for($i=0;$i<$noofprofiles;$i++) 117 { 118 $profile_id = $conn->query_result($res,$i,'profileid'); 119 120 $sql1 = "insert into vtiger_profile2globalpermissions values ($profile_id,1,1)"; 121 $sql2 = "insert into vtiger_profile2globalpermissions values ($profile_id,2,1)"; 122 123 Execute($sql1); 124 Execute($sql2); 125 } 126 127 128 //Removing entries for Dashboard and Home module from vtiger_profile2standardpermissions table 129 $del_query1 = "delete from vtiger_profile2standardpermissions where tabid in(1,3)"; 130 Execute($del_query1); 131 132 //For all Profile do the following insert into vtiger_profile2utility table: 133 $sql = 'select * from vtiger_profile'; 134 $res = $conn->query($sql); 135 $noofprofiles = $conn->num_rows($res); 136 137 /* Commented by Don. Handled below 138 for($i=0;$i<$noofprofiles;$i++) 139 { 140 $profile_id = $conn->query_result($res,$i,'profileid'); 141 142 $sql1 = "insert into vtiger_profile2utility values ($profile_id,4,7,0)"; 143 $sql2 = "insert into vtiger_profile2utility values ($profile_id,7,9,0)"; 144 145 Execute($sql1); 146 Execute($sql2); 147 } 148 */ 149 150 //Insert Values into action mapping table: 151 $actionmapping_array = Array( 152 "insert into vtiger_actionmapping values(0,'Save',0)", 153 "insert into vtiger_actionmapping values(1,'EditView',0)", 154 "insert into vtiger_actionmapping values(2,'Delete',0)", 155 "insert into vtiger_actionmapping values(3,'index',0)", 156 "insert into vtiger_actionmapping values(4,'DetailView',0)", 157 "insert into vtiger_actionmapping values(5,'Import',0)", 158 "insert into vtiger_actionmapping values(6,'Export',0)", 159 "insert into vtiger_actionmapping values(8,'Merge',0)", 160 "insert into vtiger_actionmapping values(1,'VendorEditView',1)", 161 "insert into vtiger_actionmapping values(4,'VendorDetailView',1)", 162 "insert into vtiger_actionmapping values(0,'SaveVendor',1)", 163 "insert into vtiger_actionmapping values(2,'DeleteVendor',1)", 164 "insert into vtiger_actionmapping values(1,'PriceBookEditView',1)", 165 "insert into vtiger_actionmapping values(4,'PriceBookDetailView',1)", 166 "insert into vtiger_actionmapping values(0,'SavePriceBook',1)", 167 "insert into vtiger_actionmapping values(2,'DeletePriceBook',1)", 168 "insert into vtiger_actionmapping values(1,'SalesOrderEditView',1)", 169 "insert into vtiger_actionmapping values(4,'SalesOrderDetailView',1)", 170 "insert into vtiger_actionmapping values(0,'SaveSalesOrder',1)", 171 "insert into vtiger_actionmapping values(2,'DeleteSalesOrder',1)", 172 "insert into vtiger_actionmapping values(9,'ConvertLead',0)", 173 "insert into vtiger_actionmapping values(1,'DetailViewAjax',1)", 174 "insert into vtiger_actionmapping values(1,'QuickCreate',1)", 175 "insert into vtiger_actionmapping values(4,'TagCloud',1)" 176 ); 177 foreach($actionmapping_array as $query) 178 { 179 Execute($query); 180 } 181 182 183 //Added two columns in vtiger_field table to construct the quickcreate form dynamically 184 $alter_array2 = Array( 185 "ALTER TABLE vtiger_field ADD column quickcreate int(10) after typeofdata", 186 "ALTER TABLE vtiger_field ADD column quickcreatesequence int(19) after quickcreate", 187 ); 188 foreach($alter_array2 as $query) 189 { 190 Execute($query); 191 } 192 193 $update_array1 = Array( 194 "UPDATE vtiger_field SET quickcreate = 1,quickcreatesequence = 0", 195 196 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 2 and fieldlabel = 'Potential Name'", 197 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 2 WHERE tabid = 2 and fieldlabel = 'Account Name'", 198 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 3 WHERE tabid = 2 and fieldlabel = 'Expected Close Date'", 199 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 4 WHERE tabid = 2 and fieldlabel = 'Sales Stage'", 200 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 5 WHERE tabid = 2 and fieldlabel = 'Amount'", 201 202 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 4 and fieldlabel = 'First Name'", 203 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 2 WHERE tabid = 4 and fieldlabel = 'Last Name'", 204 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 3 WHERE tabid = 4 and fieldlabel = 'Account Name'", 205 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 4 WHERE tabid = 4 and fieldlabel = 'Office Phone'", 206 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 5 WHERE tabid = 4 and fieldlabel = 'Email'", 207 208 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 6 and fieldlabel = 'Account Name'", 209 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 2 WHERE tabid = 6 and fieldlabel = 'Phone'", 210 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 3 WHERE tabid = 6 and fieldlabel = 'Website'", 211 212 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 7 and fieldlabel = 'First Name'", 213 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 2 WHERE tabid = 7 and fieldlabel = 'Last Name'", 214 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 3 WHERE tabid = 7 and fieldlabel = 'Company'", 215 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 4 WHERE tabid = 7 and fieldlabel = 'Phone'", 216 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 5 WHERE tabid = 7 and fieldlabel = 'Email'", 217 218 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 8 and fieldlabel = 'Subject'", 219 220 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 9 and fieldlabel = 'Subject'", 221 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 2 WHERE tabid = 9 and fieldlabel = 'Start Date & Time'", 222 223 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 10 and fieldlabel = 'Subject'", 224 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 2 WHERE tabid = 10 and fieldlabel = 'Date & Time Sent'", 225 226 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 13 and fieldlabel = 'Title'", 227 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 2 WHERE tabid = 13 and fieldlabel = 'Description'", 228 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 3 WHERE tabid = 13 and fieldlabel = 'Priority'", 229 230 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 14 and fieldlabel = 'Product Name'", 231 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 2 WHERE tabid = 14 and fieldlabel = 'Product Code'", 232 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 3 WHERE tabid = 14 and fieldlabel = 'Product Category'", 233 234 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 16 and fieldlabel = 'Subject'", 235 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 2 WHERE tabid = 16 and fieldlabel = 'Start Date & Time'", 236 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 3 WHERE tabid = 16 and fieldlabel = 'Activity Type'", 237 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 4 WHERE tabid = 16 and fieldlabel = 'Duration'", 238 ); 239 foreach($update_array1 as $query) 240 { 241 Execute($query); 242 } 243 244 //Added for the "Color By User in Calendar " which has been contributed by Cesar 245 $alter_query1 = "ALTER TABLE vtiger_users ADD cal_color VARCHAR(25) DEFAULT '#E6FAD8' AFTER user_hash"; 246 Execute($alter_query1); 247 248 //code contributed by Fredy for color vtiger_priority 249 $newfieldid = $conn->getUniqueID("vtiger_field"); 250 $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,'')"; 251 Execute($insert_query1); 252 253 //Added on 23-12-2005 which is missed from Fredy's contribution for Color vtiger_priority 254 populateFieldForSecurity('16',$newfieldid); 255 $activity_cols = $conn->getColumnNames("vtiger_activity"); 256 if(is_array($activity_cols) && !in_array("priority",$activity_cols)) 257 { 258 $activity_alter_query = "alter table vtiger_activity add column priority varchar(150) default NULL"; 259 Execute($activity_alter_query); 260 } 261 //Code contributed by Raju for better emailing 262 /* 263 $insert_array1 = Array( 264 "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,'')", 265 "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)", 266 "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,'')", 267 "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,'')", 268 ); 269 */ 270 //commented the above array as that queries are wrong queries -- changed on 23-12-2005 271 $insert_array1 = array( 272 "update vtiger_field set uitype='357' where tabid=10 and fieldname='parent_id' and tablename='vtiger_seactivityrel'", 273 "update vtiger_field set sequence=1 where tabid=10 and fieldname in ('parent_id','subject','filename','description')", 274 "update vtiger_field set block=2 where tabid=10 and fieldname='parent_id'", 275 "update vtiger_field set block=3 where tabid=10 and fieldname='subject'", 276 "update vtiger_field set block=4 where tabid=10 and fieldname='filename'", 277 "update vtiger_field set block=5 where tabid=10 and fieldname='description'", 278 ); 279 foreach($insert_array1 as $query) 280 { 281 Execute($query); 282 } 283 284 //code contributed by mike to rearrange the home page 285 $alter_query2 = "alter table vtiger_users add column homeorder varchar(255) default 'ALVT,PLVT,QLTQ,CVLVT,HLT,OLV,GRT,OLTSO,ILTI' after date_format"; 286 Execute($alter_query2); 287 288 //Added one column in vtiger_invoice table to include 'Contact Name' vtiger_field in Invoice module 289 $alter_query3 = "ALTER TABLE vtiger_invoice ADD column contactid int(19) after customerno"; 290 Execute($alter_query3); 291 292 $newfieldid = $conn->getUniqueID("vtiger_field"); 293 $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,'')"; 294 Execute($insert_query2); 295 //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 296 populateFieldForSecurity('23',$newfieldid); 297 298 //changes made to fix the bug in Address Information block of Accounts and Contacs module 299 $update_array2 = Array( 300 "UPDATE vtiger_field SET fieldlabel='Billing City', sequence=5 WHERE tabid=6 and fieldname='bill_city'", 301 "UPDATE vtiger_field SET fieldlabel='Billing State', sequence=7 WHERE tabid=6 and fieldname='bill_state'", 302 "UPDATE vtiger_field SET fieldlabel='Billing Code', sequence=9 WHERE tabid=6 and fieldname='bill_code'", 303 "UPDATE vtiger_field SET fieldlabel='Billing Country', sequence=11 WHERE tabid=6 and fieldname='bill_country'", 304 305 "UPDATE vtiger_field SET fieldlabel='Shipping City', sequence=6 WHERE tabid=6 and fieldname='ship_city'", 306 "UPDATE vtiger_field SET fieldlabel='Shipping State', sequence=8 WHERE tabid=6 and fieldname='ship_state'", 307 "UPDATE vtiger_field SET fieldlabel='Shipping Code', sequence=10 WHERE tabid=6 and fieldname='ship_code'", 308 "UPDATE vtiger_field SET fieldlabel='Shipping Country', sequence=12 WHERE tabid=6 and fieldname='ship_country'", 309 310 "UPDATE vtiger_field SET fieldlabel='Mailing City', sequence=5 WHERE tabid=4 and fieldname='mailingcity'", 311 "UPDATE vtiger_field SET fieldlabel='Mailing State', sequence=7 WHERE tabid=4 and fieldname='mailingstate'", 312 "UPDATE vtiger_field SET fieldlabel='Mailing Zip', sequence=9 WHERE tabid=4 and fieldname='mailingzip'", 313 "UPDATE vtiger_field SET fieldlabel='Mailing Country', sequence=11 WHERE tabid=4 and fieldname='mailingcountry'", 314 315 "UPDATE vtiger_field SET fieldlabel='Other City', sequence=6 WHERE tabid=4 and fieldname='othercity'", 316 "UPDATE vtiger_field SET fieldlabel='Other State', sequence=8 WHERE tabid=4 and fieldname='otherstate'", 317 "UPDATE vtiger_field SET fieldlabel='Other Zip', sequence=10 WHERE tabid=4 and fieldname='otherzip'", 318 "UPDATE vtiger_field SET fieldlabel='Other Country', sequence=12 WHERE tabid=4 and fieldname='othercountry'", 319 ); 320 foreach($update_array2 as $query) 321 { 322 Execute($query); 323 } 324 325 326 //Added vtiger_field emailoptout in vtiger_account table 327 $newfieldid = $conn->getUniqueID("vtiger_field"); 328 $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,'')"; 329 Execute($insert_query3); 330 331 //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 332 populateFieldForSecurity('6',$newfieldid); 333 334 //Added on 22-12-2005 335 $alter_query4 = "alter table vtiger_account add column emailoptout varchar(3) default 0"; 336 Execute($alter_query4); 337 338 $update_array3 = Array( 339 "update vtiger_field set sequence=18 where tabid=6 and fieldname ='assigned_user_id'", 340 "update vtiger_field set sequence=19 where tabid=6 and fieldname ='createdtime'", 341 "update vtiger_field set sequence=19 where tabid=6 and fieldname ='modifiedtime'", 342 ); 343 foreach($update_array3 as $query) 344 { 345 Execute($query); 346 } 347 348 349 //create table vtiger_moduleowners to assign the module and corresponding owners 350 $create_query2 = "CREATE TABLE vtiger_moduleowners ( 351 `tabid` int(19) NOT NULL default '0', 352 `user_id` varchar(11) NOT NULL, 353 PRIMARY KEY (`tabid`), 354 KEY `moduleowners_tabid_user_id_idx` (`tabid`,`user_id`) 355 ) ENGINE=InnoDB"; 356 357 /* 358 $create_query2 = "CREATE TABLE vtiger_moduleowners 359 ( 360 `tabid` int(19) NOT NULL default '0', 361 `user_id` varchar(11) NOT NULL default '', 362 PRIMARY KEY (`tabid`), 363 CONSTRAINT `fk_ModuleOwners` FOREIGN KEY (`tabid`) REFERENCES `vtiger_tab` (`tabid`) ON DELETE CASCADE 364 ) TYPE=InnoDB"; 365 */ 366 Execute($create_query2); 367 368 //Populated the default entries for vtiger_moduleowners which is created newly 369 $module_array = Array( 370 'Potentials', 371 'Contacts', 372 'Accounts', 373 'Leads', 374 'Notes', 375 'Activities', 376 'Emails', 377 'HelpDesk', 378 'Products', 379 'Faq', 380 'Vendor', 381 'PriceBook', 382 'Quotes', 383 'Orders', 384 'SalesOrder', 385 'Invoice', 386 'Reports' 387 ); 388 foreach($module_array as $mod) 389 { 390 $query = "insert into vtiger_moduleowners values(".$this->localGetTabID($mod).",1)"; 391 Execute($query); 392 } 393 394 395 //Changes made to include status vtiger_field in Activity Quickcreate Form 396 $update_array4 = Array( 397 "UPDATE vtiger_field SET quickcreate=0,quickcreatesequence=3 WHERE tabid=16 and fieldname='eventstatus'", 398 "UPDATE vtiger_field SET quickcreate=0,quickcreatesequence=4 WHERE tabid=16 and fieldname='activitytype'", 399 "UPDATE vtiger_field SET quickcreate=0,quickcreatesequence=5 WHERE tabid=16 and fieldname='duration_hours'", 400 401 "UPDATE vtiger_field SET quickcreate=0,quickcreatesequence=3 WHERE tabid=9 and fieldname='taskstatus'", 402 ); 403 foreach($update_array4 as $query) 404 { 405 Execute($query); 406 } 407 408 409 410 //Table 'inventory_tandc' added newly to include Inventory Terms &Conditions 411 $create_query1 = "CREATE TABLE vtiger_inventory_tandc(id INT(19),type VARCHAR(30) NOT NULL,tandc LONGTEXT default NULL,PRIMARY KEY(id))"; 412 Execute($create_query1); 413 414 $insert_query4 = "insert into vtiger_inventory_tandc values('".$conn->getUniqueID('vtiger_inventory_tandc')."','Inventory',' ')"; 415 Execute($insert_query4); 416 417 /****************** 5.0(Alpha) dev version 1 Database changes -- Ends*********************/ 418 419 420 421 422 423 424 425 426 427 428 /****************** 5.0(Alpha) dev version 2 Database changes -- Starts*********************/ 429 430 $query1 = "ALTER TABLE vtiger_leadaddress change lane lane varchar(250)"; 431 Execute($query1); 432 433 $rename_table_array1 = Array( 434 "update vtiger_field set tablename='vtiger_customerdetails' where tabid=4 and fieldname in ('portal','support_start_date','support_end_date')", 435 "alter table vtiger_PortalInfo drop foreign key fk_PortalInfo", 436 "rename table vtiger_PortalInfo to vtiger_portalinfo", 437 "alter table vtiger_portalinfo add CONSTRAINT `fk_portalinfo` FOREIGN KEY (`id`) REFERENCES `vtiger_contactdetails` (`contactid`) ON DELETE CASCADE", 438 "alter table vtiger_CustomerDetails drop foreign key fk_CustomerDetails", 439 "rename table vtiger_CustomerDetails to vtiger_customerdetails", 440 "alter table vtiger_customerdetails add CONSTRAINT `fk_customerdetails` FOREIGN KEY (`customerid`) REFERENCES `vtiger_contactdetails` (`contactid`) ON DELETE CASCADE" 441 ); 442 foreach($rename_table_array1 as $query) 443 { 444 Execute($query); 445 } 446 447 448 $query2 = "create table vtiger_ownernotify(crmid int(19),smownerid int(19),flag int(3))"; 449 Execute($query2); 450 451 452 //Form the vtiger_role_map_array as vtiger_roleid=>name mapping array 453 $sql = "select * from vtiger_role"; 454 $res = $conn->query($sql); 455 $role_map_array = Array(); 456 for($i=0;$i<$conn->num_rows($res);$i++) 457 { 458 $roleid = $conn->query_result($res,$i,'roleid'); 459 $name = $conn->query_result($res,$i,'name'); 460 $role_map_array[$roleid] = $name; 461 } 462 //echo '<pre> List of vtiger_roles :';print_r($role_map_array);echo '</pre>'; 463 464 //Before delete the vtiger_role take a backup array for the table vtiger_user2role 465 $sql = "select * from vtiger_user2role"; 466 $res = $conn->query($sql); 467 $user2role_array = array(); 468 for($i=0;$i<$conn->num_rows($res);$i++) 469 { 470 $userid = $conn->query_result($res,$i,'userid'); 471 $roleid = $conn->query_result($res,$i,'roleid'); 472 $user2role_array[$userid] = $roleid; 473 } 474 //echo '<pre> List of vtiger_user2role : (userid => vtiger_roleid)';print_r($user2role_array);echo '</pre>'; 475 476 //Delete the vtiger_role entries 477 $sql = "truncate vtiger_role"; 478 Execute($sql); 479 480 481 $query3 = "alter table vtiger_user2role drop FOREIGN KEY fk_user2role2"; 482 Execute($query3); 483 484 //4,5 th are the Extra added queries 485 $alter_query_array1 = Array( 486 "alter table vtiger_user2role change roleid roleid varchar(255)", 487 "alter table vtiger_role2profile change roleid roleid varchar(255)", 488 "alter table vtiger_role CHANGE roleid roleid varchar(255)", 489 "alter table vtiger_role2profile drop PRIMARY KEY", 490 "alter table vtiger_role2profile ADD PRIMARY KEY (roleid,profileid)" 491 ); 492 foreach($alter_query_array1 as $query) 493 { 494 Execute($query); 495 } 496 497 498 $query4 = "ALTER TABLE vtiger_user2role ADD CONSTRAINT fk_user2role2 FOREIGN KEY (roleid) REFERENCES vtiger_role(roleid) ON DELETE CASCADE"; 499 Execute($query4); 500 501 $alter_query_array2 = Array( 502 "alter table vtiger_role CHANGE name rolename varchar(200)", 503 "alter table vtiger_role DROP description", 504 "alter table vtiger_role add parentrole varchar(255)", 505 "alter table vtiger_role add depth int(19)" 506 ); 507 foreach($alter_query_array2 as $query) 508 { 509 Execute($query); 510 } 511 512 513 514 $query5 = "insert into vtiger_role values('H1','Organisation','H1',0)"; 515 Execute($query5); 516 517 //include("include/utils/UserInfoUtil.php"); 518 //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 519 foreach($role_map_array as $roleid => $rolename) 520 { 521 $parentRole = 'H1'; 522 if($rolename == 'standard_user') 523 { 524 $rs = $conn->query("select * from vtiger_role where rolename='administrator'"); 525 $parentRole = $conn->query_result($rs,0,'roleid'); 526 } 527 $empty_array = array(""=>""); 528 $new_role_id = localcreateRole($rolename,$parentRole,$empty_array); 529 $new_role_map_array[$roleid] = $new_role_id; 530 } 531 532 //Before insert the new entry we should remove the old entries -- added on 06-06-06 533 $user2role_del = "truncate vtiger_user2role"; 534 Execute($user2role_del); 535 536 //First we will insert the old values from vtiger_user2role_array to vtiger_user2role table and then update the new vtiger_role id 537 foreach($user2role_array as $userid => $roleid) 538 { 539 $sql = "insert into vtiger_user2role (userid, roleid) values(".$userid.",'".$new_role_map_array[$roleid]."')"; 540 Execute($sql); 541 } 542 //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 543 //Update the vtiger_user2role table with new vtiger_roleid 544 /* 545 foreach($new_role_map_array as $old_roleid => $new_roleid) 546 { 547 $update_user2role = "update vtiger_user2role set vtiger_roleid='".$new_roleid."' where vtiger_roleid=".$old_roleid; 548 Execute($update_user2role); 549 } 550 */ 551 //Update the vtiger_role2profile table with new vtiger_roleid 552 foreach($new_role_map_array as $old_roleid => $new_roleid) 553 { 554 $update_role2profile = "update vtiger_role2profile set roleid='".$new_roleid."' where roleid=".$old_roleid; 555 Execute($update_role2profile); 556 } 557 558 559 560 //Group Migration: 561 //Step 1 : form and group_map_array as groupname => description from vtiger_groups table 562 //Step 2 : form an vtiger_users2group_map_array array as userid => groupname from vtiger_users2group table 563 //Step 3 : delete all entries from vtiger_groups table and enter new values from group_map_array 564 //Step 4 : drop the table vtiger_users2group and create new table 565 //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 566 567 //Step 1 : Form the group_map_array as groupname => description 568 $sql = "select * from vtiger_groups"; 569 $res = $conn->query($sql); 570 $group_map_array = Array(); 571 for($i=0;$i<$conn->num_rows($res);$i++) 572 { 573 $name = $conn->query_result($res,$i,'name'); 574 $desc = $conn->query_result($res,$i,'description'); 575 $group_map_array[$name] = $desc; 576 } 577 //echo '<pre>List of Groups : ';print_r($group_map_array);echo '</pre>'; 578 579 580 //Step 2 : form an vtiger_users2group_map_array array as userid => groupname from vtiger_users2group table 581 $sql = "select * from vtiger_users2group"; 582 $res = $conn->query($sql); 583 $users2group_map_array = Array(); 584 for($i=0;$i<$conn->num_rows($res);$i++) 585 { 586 $groupname = $conn->query_result($res,$i,'groupname'); 587 $userid = $conn->query_result($res,$i,'userid'); 588 $users2group_map_array[$userid] = $groupname; 589 } 590 //echo '<pre>List of vtiger_users2group : ';print_r($users2group_map_array);echo '</pre>'; 591 592 //Step 3 : delete all entries from vtiger_groups table 593 $sql = "truncate vtiger_groups"; 594 Execute($sql); 595 596 $alter_query_array3 = Array( 597 "alter table vtiger_users2group drop FOREIGN KEY fk_users2group", 598 "alter table vtiger_leadgrouprelation drop FOREIGN KEY fk_leadgrouprelation2", 599 "alter table vtiger_activitygrouprelation drop FOREIGN KEY fk_activitygrouprelation2", 600 "alter table vtiger_ticketgrouprelation drop FOREIGN KEY fk_ticketgrouprelation2", 601 "alter table vtiger_groups drop PRIMARY KEY" 602 ); 603 foreach($alter_query_array3 as $query) 604 { 605 Execute($query); 606 } 607 608 //2 nd query is the Extra added query 609 //Adding columns in group table: 610 $alter_query_array4 = Array( 611 "alter table vtiger_groups add column groupid int(19) FIRST", 612 "alter table vtiger_groups change name groupname varchar(100)", 613 "alter table vtiger_groups ADD PRIMARY KEY (groupid)", 614 "alter table vtiger_groups add index (groupname)" 615 ); 616 foreach($alter_query_array4 as $query) 617 { 618 Execute($query); 619 } 620 621 622 //Moved the create table queries for vtiger_group2grouprel, vtiger_group2role, vtiger_group2rs from the end of this block 623 //Added on 06-06-06 624 $query8 = "CREATE TABLE vtiger_group2grouprel ( 625 `groupid` int(19) NOT NULL, 626 `containsgroupid` int(19) NOT NULL, 627 PRIMARY KEY (`groupid`,`containsgroupid`) 628 ) ENGINE=InnoDB"; 629 /* 630 $query8 = "CREATE TABLE vtiger_group2grouprel 631 ( 632 `groupid` int(19) NOT NULL default '0', 633 `containsgroupid` int(19) NOT NULL default '0', 634 PRIMARY KEY (`groupid`,`containsgroupid`), 635 CONSTRAINT `fk_group2grouprel1` FOREIGN KEY (`groupid`) REFERENCES `vtiger_groups` (`groupid`) ON DELETE CASCADE 636 ) TYPE=InnoDB"; 637 */ 638 Execute($query8); 639 640 //Added on 06-06-06 641 $query9 = "CREATE TABLE vtiger_group2role ( 642 `groupid` int(19) NOT NULL, 643 `roleid` varchar(255) NOT NULL, 644 PRIMARY KEY (`groupid`,`roleid`) 645 ) ENGINE=InnoDB"; 646 /* 647 $query9 = "CREATE TABLE vtiger_group2role 648 ( 649 `groupid` int(19) NOT NULL default '0', 650 `roleid` varchar(255) NOT NULL default '', 651 PRIMARY KEY (`groupid`,`roleid`), 652 CONSTRAINT `fk_group2role1` FOREIGN KEY (`groupid`) REFERENCES `vtiger_groups` (`groupid`) ON DELETE CASCADE 653 ) TYPE=InnoDB"; 654 */ 655 Execute($query9); 656 657 //Added on 06-06-06 658 $query10 = "CREATE TABLE vtiger_group2rs ( 659 `groupid` int(19) NOT NULL, 660 `roleandsubid` varchar(255) NOT NULL, 661 PRIMARY KEY (`groupid`,`roleandsubid`) 662 ) ENGINE=InnoDB"; 663 /* 664 $query10 = "CREATE TABLE vtiger_group2rs 665 ( 666 `groupid` int(19) NOT NULL default '0', 667 `roleandsubid` varchar(255) NOT NULL default '', 668 PRIMARY KEY (`groupid`,`roleandsubid`), 669 CONSTRAINT `fk_group2rs1` FOREIGN KEY (`groupid`) REFERENCES `vtiger_groups` (`groupid`) ON DELETE CASCADE 670 ) TYPE=InnoDB"; 671 */ 672 Execute($query10); 673 674 //Insert all the retrieved old values to the new vtiger_groups table ie., create new vtiger_groups 675 foreach($group_map_array as $groupname => $description) 676 { 677 $empty_array = array( 678 "groups" => array(""=>""), 679 "roles" => array(""=>""), 680 "rs" => array(""=>""), 681 "users" => array(""=>"") 682 ); 683 $groupid = createGroup($groupname,$empty_array,$description); 684 $group_name_id_mapping[$groupname] = $groupid; 685 } 686 687 688 //Copy all mappings in a user2grop table in a array; 689 690 //Step 4 : Drop and again create users2group 691 $query6 = "drop table vtiger_users2group"; 692 Execute($query6); 693 694 //Added on 06-06-06 695 $query7 = "CREATE TABLE vtiger_users2group ( 696 `groupid` int(19) NOT NULL, 697 `userid` int(19) NOT NULL, 698 PRIMARY KEY (`groupid`,`userid`), 699 KEY `users2group_groupname_uerid_idx` (`groupid`,`userid`) 700 ) ENGINE=InnoDB"; 701 /* 702 $query7 = "CREATE TABLE vtiger_users2group 703 ( 704 `groupid` int(19) NOT NULL default '0', 705 `userid` int(19) NOT NULL default '0', 706 PRIMARY KEY (`groupid`,`userid`), 707 CONSTRAINT `fk_users2group1` FOREIGN KEY (`groupid`) REFERENCES `groups` (`groupid`) ON DELETE CASCADE 708 ) TYPE=InnoDB"; 709 */ 710 Execute($query7); 711 712 //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 713 foreach($users2group_map_array as $userid => $groupname) 714 { 715 //$groupid = $conn->query_result($conn->query("select * from vtiger_groups where groupname='".$groupname."'"),0,'groupid'); 716 $sql = "insert into vtiger_users2group (groupid,userid) values(".$group_name_id_mapping[$groupname].",".$userid.")"; 717 Execute($sql); 718 } 719 720 721 $alter_query_array5 = Array( 722 "alter table vtiger_leadgrouprelation ADD CONSTRAINT fk_leadgrouprelation2 FOREIGN KEY (groupname) REFERENCES vtiger_groups(groupname) ON DELETE CASCADE", 723 "ALTER TABLE vtiger_activitygrouprelation ADD CONSTRAINT fk_activitygrouprelation2 FOREIGN KEY (groupname) REFERENCES vtiger_groups(groupname) ON DELETE CASCADE", 724 "ALTER TABLE vtiger_ticketgrouprelation ADD CONSTRAINT fk_ticketgrouprelation2 FOREIGN KEY (groupname) REFERENCES vtiger_groups(groupname) ON DELETE CASCADE" 725 ); 726 foreach($alter_query_array5 as $query) 727 { 728 Execute($query); 729 } 730 //Moved the create table queries for vtiger_group2grouprel, vtiger_group2role, vtiger_group2rs to before creatinf the Group ie., before call the createGroup 731 732 733 /***Added to include decimal places for amount vtiger_field in vtiger_potential table --by Mangai 15-Nov-2005***/ 734 735 $query11 = "ALTER TABLE vtiger_potential change amount amount decimal(10,2)"; 736 Execute($query11); 737 738 /****************** 5.0(Alpha) dev version 2 Database changes -- Ends*********************/ 739 740 741 742 743 744 745 746 747 748 749 750 751 /****************** 5.0(Alpha) dev version 3 Database changes -- Starts*********************/ 752 753 //Drop the column company_name from vtiger_vendor table ---- modified by Mickie on 18-11-2005 754 $altersql1 = "alter table vtiger_vendor drop column company_name"; 755 Execute($altersql1); 756 $altersql2 = "alter table vtiger_vendor change column name vendorname varchar(100) default NULL"; 757 Execute($altersql2); 758 Execute("update vtiger_field set fieldname='vendorname', columnname='vendorname' where tabid=18 and fieldname='name'"); 759 //TODO (check): Remove this company_name entry from the vtiger_field table if it already exists 760 761 //Migration for Default Organisation Share -- Added by Don on 20-11-2005 762 763 $query1 = "CREATE TABLE vtiger_org_share_action_mapping ( 764 `share_action_id` int(19) NOT NULL default '0', 765 `share_action_name` varchar(200) NOT NULL default '', 766 PRIMARY KEY (`share_action_id`,`share_action_name`) 767 ) TYPE=InnoDB "; 768 Execute($query1); 769 770 $query2 = "CREATE TABLE vtiger_org_share_action2tab ( 771 `share_action_id` int(19) NOT NULL default '0', 772 `tabid` int(19) NOT NULL default '0', 773 PRIMARY KEY (`share_action_id`,`tabid`), 774 CONSTRAINT `fk_org_share_action2tab` FOREIGN KEY (`share_action_id`) REFERENCES `vtiger_org_share_action_mapping` (`share_action_id`) ON DELETE CASCADE 775 ) TYPE=InnoDB"; 776 Execute($query2); 777 778 779 $query3 = "alter table vtiger_def_org_share add column editstatus int(19)"; 780 Execute($query3); 781 782 $query4 = "delete from vtiger_def_org_share where tabid in(8,14,15,18,19)"; 783 Execute($query4); 784 785 786 787 //Inserting values into org share action mapping 788 $insert_query_array1 = Array( 789 "insert into vtiger_org_share_action_mapping values(0,'Public: Read Only')", 790 "insert into vtiger_org_share_action_mapping values(1,'Public:Read,Create/Edit')", 791 "insert into vtiger_org_share_action_mapping values(2,'Public: Read, Create/Edit, Delete')", 792 "insert into vtiger_org_share_action_mapping values(3,'Private')", 793 "insert into vtiger_org_share_action_mapping values(4,'Hide Details')", 794 "insert into vtiger_org_share_action_mapping values(5,'Hide Details and Add Events')", 795 "insert into vtiger_org_share_action_mapping values(6,'Show Details')", 796 "insert into vtiger_org_share_action_mapping values(7,'Show Details and Add Events')" 797 ); 798 foreach($insert_query_array1 as $query) 799 { 800 Execute($query); 801 } 802 803 804 //Inserting for all vtiger_tabs 805 $def_org_tabid=Array(2,4,6,7,9,10,13,16,20,21,22,23,26); 806 foreach($def_org_tabid as $def_tabid) 807 { 808 $insert_query_array2 = Array( 809 "insert into vtiger_org_share_action2tab values(0,".$def_tabid.")", 810 "insert into vtiger_org_share_action2tab values(1,".$def_tabid.")", 811 "insert into vtiger_org_share_action2tab values(2,".$def_tabid.")", 812 "insert into vtiger_org_share_action2tab values(3,".$def_tabid.")" 813 ); 814 foreach($insert_query_array2 as $query) 815 { 816 Execute($query); 817 } 818 } 819 820 $insert_query_array3 = Array( 821 "insert into vtiger_org_share_action2tab values(4,17)", 822 "insert into vtiger_org_share_action2tab values(5,17)", 823 "insert into vtiger_org_share_action2tab values(6,17)", 824 "insert into vtiger_org_share_action2tab values(7,17)" 825 ); 826 foreach($insert_query_array3 as $query) 827 { 828 Execute($query); 829 } 830 831 $query_array1 = Array( 832 "insert into vtiger_def_org_share values(9,17,7,0)", 833 "update vtiger_def_org_share set editstatus=0", 834 "update vtiger_def_org_share set editstatus=2 where tabid=4", 835 "update vtiger_def_org_share set editstatus=1 where tabid=9", 836 "update vtiger_def_org_share set editstatus=2 where tabid=16" 837 ); 838 foreach($query_array1 as $query) 839 { 840 Execute($query); 841 } 842 843 /****************** 5.0(Alpha) dev version 3 Database changes -- Ends*********************/ 844 845 846 847 $migrationlog->debug("Database Modifications for 5.0(Alpha) Dev 3 ==> 5.0 Alpha starts here."); 848 //echo "<br><br><b>Database Modifications for 5.0(Alpha) Dev3 ==> 5.0 Alpha starts here.....</b><br>"; 849 $alter_query_array6 = Array( 850 "ALTER TABLE vtiger_users ADD column activity_view VARCHAR(25) DEFAULT 'Today' AFTER homeorder", 851 "ALTER TABLE vtiger_activity ADD column notime CHAR(3) DEFAULT '0' AFTER location" 852 ); 853 foreach($alter_query_array6 as $query) 854 { 855 Execute($query); 856 } 857 858 $insert_field_array1 = Array( 859 "Insert into vtiger_field values (9,".$conn->getUniqueID("vtiger_field").",'notime','activity',1,56,'notime','No Time',1,0,0,100,20,1,3,'C~O',1,'')", 860 "Insert into vtiger_field values (16,".$conn->getUniqueID("vtiger_field").",'notime','activity',1,56,'notime','No Time',1,0,0,100,18,1,1,'C~O',1,'')" 861 ); 862 foreach($insert_field_array1 as $query) 863 { 864 Execute($query); 865 } 866 867 $alter_query_array7 = Array( 868 "alter table vtiger_vendor add column pobox varchar(30) after state", 869 "alter table vtiger_leadaddress add column pobox varchar(30) after state", 870 "alter table vtiger_accountbillads add column pobox varchar(30) after state", 871 "alter table vtiger_accountshipads add column pobox varchar(30) after state", 872 "alter table vtiger_contactaddress add column mailingpobox varchar(30) after mailingstate", 873 "alter table vtiger_contactaddress add column otherpobox varchar(30) after otherstate", 874 "alter table vtiger_quotesbillads add column bill_pobox varchar(30) after bill_street", 875 "alter table vtiger_quotesshipads add column ship_pobox varchar(30) after ship_street", 876 "alter table vtiger_pobillads add column bill_pobox varchar(30) after bill_street", 877 "alter table vtiger_poshipads add column ship_pobox varchar(30) after ship_street", 878 "alter table vtiger_sobillads add column bill_pobox varchar(30) after bill_street", 879 "alter table vtiger_soshipads add column ship_pobox varchar(30) after ship_street", 880 "alter table vtiger_invoicebillads add column bill_pobox varchar(30) after bill_street", 881 "alter table vtiger_invoiceshipads add column ship_pobox varchar(30) after ship_street" 882 ); 883 foreach($alter_query_array7 as $query) 884 { 885 Execute($query); 886 } 887 888 $insert_field_array2 = Array( 889 "insert into vtiger_field values (23,".$conn->getUniqueID("vtiger_field").",'bill_pobox','invoicebillads',1,'1','bill_pobox','Billing Po Box',1,0,0,100,3,2,1,'V~O',1,'')", 890 "insert into vtiger_field values (23,".$conn->getUniqueID("vtiger_field").",'ship_pobox','invoiceshipads',1,'1','ship_pobox','Shipping Po Box',1,0,0,100,4,2,1,'V~O',1,'')", 891 892 "insert into vtiger_field values (6,".$conn->getUniqueID("vtiger_field").",'pobox','accountbillads',1,'1','bill_pobox','Billing Po Box',1,0,0,100,3,2,1,'V~O',1,'')", 893 "insert into vtiger_field values (6,".$conn->getUniqueID("vtiger_field").",'pobox','accountshipads',1,'1','ship_pobox','Shipping Po Box',1,0,0,100,4,2,1,'V~O',1,'')", 894 895 "insert into vtiger_field values (7,".$conn->getUniqueID("vtiger_field").",'pobox','leadaddress',1,'1','pobox','Po Box',1,0,0,100,2,2,1,'V~O',1,'')", 896 897 "insert into vtiger_field values (4,".$conn->getUniqueID("vtiger_field").",'mailingpobox','contactaddress',1,'1','mailingpobox','Mailing Po Box',1,0,0,100,3,2,1,'V~O',1,'')", 898 "insert into vtiger_field values (4,".$conn->getUniqueID("vtiger_field").",'otherpobox','contactaddress',1,'1','otherpobox','Other Po Box',1,0,0,100,4,2,1,'V~O',1,'')", 899 900 "insert into vtiger_field values (18,".$conn->getUniqueID("vtiger_field").",'pobox','vendor',1,'1','pobox','Po Box',1,0,0,100,2,2,1,'V~O',1,'')", 901 902 "insert into vtiger_field values (20,".$conn->getUniqueID("vtiger_field").",'bill_pobox','quotesbillads',1,'1','bill_pobox','Billing Po Box',1,0,0,100,3,2,1,'V~O',1,'')", 903 "insert into vtiger_field values (20,".$conn->getUniqueID("vtiger_field").",'ship_pobox','quotesshipads',1,'1','ship_pobox','Shipping Po Box',1,0,0,100,4,2,1,'V~O',1,'')", 904 905 "insert into vtiger_field values (21,".$conn->getUniqueID("vtiger_field").",'bill_pobox','pobillads',1,'1','bill_pobox','Billing Po Box',1,0,0,100,3,2,1,'V~O',1,'')", 906 "insert into vtiger_field values (21,".$conn->getUniqueID("vtiger_field").",'ship_pobox','poshipads',1,'1','ship_pobox','Shipping Po Box',1,0,0,100,4,2,1,'V~O',1,'')", 907 908 "insert into vtiger_field values (22,".$conn->getUniqueID("vtiger_field").",'bill_pobox','sobillads',1,'1','bill_pobox','Billing Po Box',1,0,0,100,3,2,1,'V~O',1,'')", 909 "insert into vtiger_field values (22,".$conn->getUniqueID("vtiger_field").",'ship_pobox','soshipads',1,'1','ship_pobox','Shipping Po Box',1,0,0,100,4,2,1,'V~O',1,'')" 910 ); 911 foreach($insert_field_array2 as $query) 912 { 913 Execute($query); 914 } 915 916 917 $fieldname =array('bill_city','bill_state','bill_code','bill_country','ship_city','ship_state','ship_code','ship_country'); 918 $tablename = array('accountbillads','quotesbillads','pobillads','sobillads','invoicebillads','accountshipads','quotesshipads','poshipads','soshipads','invoiceshipads'); 919 $sequence = array(5,7,9,11,6,8,10,12); 920 $k = 0; 921 $n = 0; 922 for($j = 0;$j < 8;$j++) 923 { 924 if($j == 4) 925 $n = $n+5; 926 for($i = 0;$i < 5;$i++) 927 { 928 $query1 = "update vtiger_field set sequence=".$sequence[$j]." where tablename='".$tablename[$n+$i]."' && fieldname='".$fieldname[$j]."'"; 929 Execute($query1); 930 } 931 } 932 933 $fieldname = array('code','city','country','state'); 934 $tablename = 'leadaddress'; 935 $sequence = array(3,4,5,6); 936 for($i = 0;$i < 4;$i++) 937 { 938 $query2 = "update vtiger_field set sequence=".$sequence[$i]." where tablename='".$tablename."' && fieldname='".$fieldname[$i]."'"; 939 Execute($query2); 940 } 941 942 $fieldname = array('city','state','postalcode','country'); 943 $tablename = 'vendor'; 944 $sequence = array(3,4,5,6); 945 946 for($i = 0;$i < 4;$i++) 947 { 948 $query3 = "update vtiger_field set sequence=".$sequence[$i]." where tablename='".$tablename."' && fieldname='".$fieldname[$i]."'"; 949 Execute($query3); 950 } 951 952 $fieldname = array('mailingcity','othercity','mailingstate','otherstate','mailingzip','otherzip','mailingcountry','othercountry'); 953 $tablename = 'contactaddress'; 954 $sequence = array(5,6,7,8,9,10,11,12); 955 956 for($i = 0;$i < 8;$i++) 957 { 958 $query = "update vtiger_field set sequence=".$sequence[$i]." where tablename='".$tablename."' && fieldname='".$fieldname[$i]."'"; 959 Execute($query); 960 } 961 962 $query_array1 = Array( 963 "update vtiger_field set tablename='vtiger_crmentity' where tabid=10 and fieldname='description'", 964 "update vtiger_field set tablename='vtiger_attachments' where tabid=10 and fieldname='filename'", 965 "drop table vtiger_emails", 966 967 "alter table vtiger_activity drop column description", 968 "update vtiger_field set tablename='vtiger_crmentity' where tabid in (9,16) and fieldname='description'", 969 970 "update vtiger_tab set name='PurchaseOrder',tablabel='PurchaseOrder' where tabid=21", 971 "update vtiger_tab set presence=0 where tabid=22 and name='SalesOrder'", 972 973 "delete from vtiger_actionmapping where actionname='SalesOrderDetailView'", 974 "delete from vtiger_actionmapping where actionname='SalesOrderEditView'", 975 "delete from vtiger_actionmapping where actionname='SaveSalesOrder'", 976 "delete from vtiger_actionmapping where actionname='DeleteSalesOrder'", 977 978 "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)", 979 980 "alter table vtiger_troubletickets add column filename varchar(50) default NULL after title" 981 ); 982 foreach($query_array1 as $query) 983 { 984 Execute($query); 985 } 986 987 $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))"; 988 Execute($create_query3); 989 $create_query4 = "create table vtiger_parenttabrel(parenttabid int(3) not null, tabid int(3) not null,sequence int(3) not null)"; 990 Execute($create_query4); 991 992 $insert_query_array4 = Array( 993 "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)", 994 "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) " 995 ); 996 foreach($insert_query_array4 as $query) 997 { 998 Execute($query); 999 } 1000 1001 1002 $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))"; 1003 Execute($create_query5); 1004 1005 $update_query_array1 = Array( 1006 "update vtiger_field set block=2 where tabid=2 and block=5", 1007 "update vtiger_field set block=3 where tabid=2 and block=2", 1008 1009 //"update vtiger_field set block=4 where tabid=4 and block=1", 1010 "update vtiger_field set block=5 where tabid=4 and block=5", 1011 "update vtiger_field set block=6 where tabid=4 and block=4",//Modified on 24-04-06 1012 "update vtiger_field set block=4 where tabid=4 and block=1", 1013 "update vtiger_field set block=7 where tabid=4 and block=2", 1014 "update vtiger_field set block=8 where tabid=4 and block=3", 1015 1016 "update vtiger_field set block=9 where tabid=6 and block=1", 1017 "update vtiger_field set block=10 where tabid=6 and block=5", 1018 "update vtiger_field set block=11 where tabid=6 and block=2", 1019 "update vtiger_field set block=12 where tabid=6 and block=3", 1020 1021 "update vtiger_field set block=13 where tabid=7 and block=1", 1022 "update vtiger_field set block=14 where tabid=7 and block=5", 1023 "update vtiger_field set block=15 where tabid=7 and block=2", 1024 "update vtiger_field set block=16 where tabid=7 and block=3", 1025 1026 "update vtiger_field set block=17 where tabid=8 and block=1", 1027 "update vtiger_field set block=17 where tabid=8 and block=2", 1028 "update vtiger_field set block=18 where tabid=8 and block=3", 1029 1030 "update vtiger_field set block=19 where tabid=9 and block=1", 1031 "update vtiger_field set block=19 where tabid=9 and block=7", 1032 "update vtiger_field set block=20 where tabid=9 and block=2", 1033 1034 "update vtiger_field set block=21 where tabid=10 and block=1", 1035 "update vtiger_field set block=22 where tabid=10 and block=2", 1036 "update vtiger_field set block=23 where tabid=10 and block=3", 1037 "update vtiger_field set block=23 where tabid=10 and block=4", 1038 "update vtiger_field set block=24 where tabid=10 and block=5", 1039 1040 "update vtiger_field set block=25 where tabid=13 and block=1", 1041 "update vtiger_field set block=26 where tabid=13 and block=2", 1042 "update vtiger_field set block=27 where tabid=13 and block=5", 1043 "update vtiger_field set block=28 where tabid=13 and block=3", 1044 "update vtiger_field set block=29 where tabid=13 and block=4", 1045 "update vtiger_field set block=30 where tabid=13 and block=6", 1046 1047 "update vtiger_field set block=31 where tabid=14 and block=1", 1048 "update vtiger_field set block=32 where tabid=14 and block=2", 1049 "update vtiger_field set block=33 where tabid=14 and block=3", 1050 "update vtiger_field set block=34 where tabid=14 and block=5", 1051 "update vtiger_field set block=35 where tabid=14 and block=6", 1052 "update vtiger_field set block=36 where tabid=14 and block=4", 1053 1054 "update vtiger_field set block=37 where tabid=15 and block=1", 1055 "update vtiger_field set block=38 where tabid=15 and block=2", 1056 "update vtiger_field set block=39 where tabid=15 and block=3", 1057 "update vtiger_field set block=40 where tabid=15 and block=4", 1058 1059 "update vtiger_field set block=41 where tabid=16 and block=1", 1060 "update vtiger_field set block=42 where tabid=16 and block=7", 1061 "update vtiger_field set block=43 where tabid=16 and block=2", 1062 1063 "update vtiger_field set block=44 where tabid=18 and block=1", 1064 "update vtiger_field set block=45 where tabid=18 and block=5", 1065 "update vtiger_field set block=36 where tabid=18 and block=2", 1066 "update vtiger_field set block=47 where tabid=18 and block=3", 1067 1068 "update vtiger_field set block=48 where tabid=19 and block=1", 1069 "update vtiger_field set block=49 where tabid=19 and block=5", 1070 "update vtiger_field set block=50 where tabid=19 and block=2", 1071 1072 "update vtiger_field set block=51 where tabid=20 and block=1", 1073 "update vtiger_field set block=52 where tabid=20 and block=5", 1074 "update vtiger_field set block=53 where tabid=20 and block=2", 1075 "update vtiger_field set block=55 where tabid=20 and block=6", 1076 "update vtiger_field set block=56 where tabid=20 and block=3", 1077 1078 "update vtiger_field set block=57 where tabid=21 and block=1", 1079 "update vtiger_field set block=58 where tabid=21 and block=5", 1080 "update vtiger_field set block=59 where tabid=21 and block=2", 1081 "update vtiger_field set block=61 where tabid=21 and block=6", 1082 "update vtiger_field set block=62 where tabid=21 and block=3", 1083 1084 "update vtiger_field set block=63 where tabid=22 and block=1", 1085 "update vtiger_field set block=64 where tabid=22 and block=5", 1086 "update vtiger_field set block=65 where tabid=22 and block=2", 1087 "update vtiger_field set block=67 where tabid=22 and block=6", 1088 "update vtiger_field set block=68 where tabid=22 and block=3", 1089 1090 1091 "update vtiger_field set block=69 where tabid=23 and block=1", 1092 "update vtiger_field set block=70 where tabid=23 and block=5", 1093 "update vtiger_field set block=71 where tabid=23 and block=2", 1094 "update vtiger_field set block=73 where tabid=23 and block=6", 1095 "update vtiger_field set block=74 where tabid=23 and block=3", 1096 ); 1097 foreach($update_query_array1 as $query) 1098 { 1099 Execute($query); 1100 } 1101 1102 $insert_query_array5 = Array( 1103 "insert into vtiger_blocks values (1,2,'LBL_OPPORTUNITY_INFORMATION',1,0,0,0,0,0)", 1104 "insert into vtiger_blocks values (2,2,'LBL_CUSTOM_INFORMATION',2,0,0,0,0,0)", 1105 "insert into vtiger_blocks values (3,2,'LBL_DESCRIPTION_INFORMATION',3,0,0,0,0,0)", 1106 "insert into vtiger_blocks values (4,4,'LBL_CONTACT_INFORMATION',1,0,0,0,0,0)", 1107 "insert into vtiger_blocks values (5,4,'LBL_CUSTOM_INFORMATION',2,0,0,0,0,0)", 1108 "insert into vtiger_blocks values (6,4,'LBL_CUSTOMER_PORTAL_INFORMATION',3,0,0,0,0,0)", 1109 "insert into vtiger_blocks values (7,4,'LBL_ADDRESS_INFORMATION',4,0,0,0,0,0)", 1110 "insert into vtiger_blocks values (8,4,'LBL_DESCRIPTION_INFORMATION',5,0,0,0,0,0)", 1111 "insert into vtiger_blocks values (9,6,'LBL_ACCOUNT_INFORMATION',1,0,0,0,0,0)", 1112 "insert into vtiger_blocks values (10,6,'LBL_CUSTOM_INFORMATION',2,0,0,0,0,0)", 1113 "insert into vtiger_blocks values (11,6,'LBL_ADDRESS_INFORMATION',3,0,0,0,0,0)", 1114 "insert into vtiger_blocks values (12,6,'LBL_DESCRIPTION_INFORMATION',4,0,0,0,0,0)", 1115 "insert into vtiger_blocks values (13,7,'LBL_LEAD_INFORMATION',1,0,0,0,0,0)", 1116 "insert into vtiger_blocks values (14,7,'LBL_CUSTOM_INFORMATION',2,0,0,0,0,0)", 1117 "insert into vtiger_blocks values (15,7,'LBL_ADDRESS_INFORMATION',3,0,0,0,0,0)", 1118 "insert into vtiger_blocks values (16,7,'LBL_DESCRIPTION_INFORMATION',4,0,0,0,0,0)", 1119 "insert into vtiger_blocks values (17,8,'LBL_NOTE_INFORMATION',1,0,0,0,0,0)", 1120 "insert into vtiger_blocks values (18,8,'',2,1,0,0,0,0)", 1121 "insert into vtiger_blocks values (19,9,'LBL_TASK_INFORMATION',1,0,0,0,0,0)", 1122 "insert into vtiger_blocks values (20,9,'',2,1,0,0,0,0)", 1123 "insert into vtiger_blocks values (21,10,'LBL_EMAIL_INFORMATION',1,0,0,0,0,0)", 1124 "insert into vtiger_blocks values (22,10,'',2,1,0,0,0,0)", 1125 "insert into vtiger_blocks values (23,10,'',3,1,0,0,0,0)", 1126 "insert into vtiger_blocks values (24,10,'',4,1,0,0,0,0)", 1127 "insert into vtiger_blocks values (25,13,'LBL_TICKET_INFORMATION',1,0,0,0,0,0)", 1128 "insert into vtiger_blocks values (26,13,'',2,1,0,0,0,0)", 1129 "insert into vtiger_blocks values (27,13,'LBL_CUSTOM_INFORMATION',3,0,0,0,0,0)", 1130 "insert into vtiger_blocks values (28,13,'LBL_DESCRIPTION_INFORMATION',4,0,0,0,0,0)", 1131 "insert into vtiger_blocks values (29,13,'LBL_TICKET_RESOLUTION',5,0,0,1,0,0)", 1132 "insert into vtiger_blocks values (30,13,'LBL_COMMENTS',6,0,0,1,0,0)", 1133 "insert into vtiger_blocks values (31,14,'LBL_PRODUCT_INFORMATION',1,0,0,0,0,0)", 1134 "insert into vtiger_blocks values (32,14,'LBL_PRICING_INFORMATION',2,0,0,0,0,0)", 1135 "insert into vtiger_blocks values (33,14,'LBL_STOCK_INFORMATION',3,0,0,0,0,0)", 1136 "insert into vtiger_blocks values (34,14,'LBL_CUSTOM_INFORMATION',4,0,0,0,0,0)", 1137 "insert into vtiger_blocks values (35,14,'LBL_IMAGE_INFORMATION',5,0,0,0,0,0)", 1138 "insert into vtiger_blocks values (36,14,'LBL_DESCRIPTION_INFORMATION',6,0,0,0,0,0)", 1139 "insert into vtiger_blocks values (37,15,'LBL_FAQ_INFORMATION',1,0,0,0,0,0)", 1140 "insert into vtiger_blocks values (38,15,'',2,1,0,0,0,0)", 1141 "insert into vtiger_blocks values (39,15,'',3,1,0,0,0,0)", 1142 "insert into vtiger_blocks values (40,15,'LBL_COMMENT_INFORMATION',4,0,0,1,0,0)", 1143 "insert into vtiger_blocks values (41,16,'LBL_EVENT_INFORMATION',1,0,0,0,0,0)", 1144 "insert into vtiger_blocks values (42,16,'',2,1,0,0,0,0)", 1145 "insert into vtiger_blocks values (43,16,'',3,1,0,0,0,0)", 1146 "insert into vtiger_blocks values (44,18,'LBL_VENDOR_INFORMATION',1,0,0,0,0,0)", 1147 "insert into vtiger_blocks values (45,18,'LBL_CUSTOM_INFORMATION',2,0,0,0,0,0)", 1148 "insert into vtiger_blocks values (46,18,'LBL_VENDOR_ADDRESS_INFORMATION',3,0,0,0,0,0)", 1149 "insert into vtiger_blocks values (47,18,'LBL_DESCRIPTION_INFORMATION',4,0,0,0,0,0)", 1150 "insert into vtiger_blocks values (48,19,'LBL_PRICEBOOK_INFORMATION',1,0,0,0,0,0)", 1151 "insert into vtiger_blocks values (49,19,'LBL_CUSTOM_INFORMATION',2,0,0,0,0,0)", 1152 "insert into vtiger_blocks values (50,19,'LBL_DESCRIPTION_INFORMATION',3,0,0,0,0,0)", 1153 "insert into vtiger_blocks values (51,20,'LBL_QUOTE_INFORMATION',1,0,0,0,0,0)", 1154 "insert into vtiger_blocks values (52,20,'LBL_CUSTOM_INFORMATION',2,0,0,0,0,0)", 1155 "insert into vtiger_blocks values (53,20,'LBL_ADDRESS_INFORMATION',3,0,0,0,0,0)", 1156 "insert into vtiger_blocks values (54,20,'LBL_RELATED_PRODUCTS',4,0,0,0,0,0)", 1157 "insert into vtiger_blocks values (55,20,'LBL_TERMS_INFORMATION',5,0,0,0,0,0)", 1158 "insert into vtiger_blocks values (56,20,'LBL_DESCRIPTION_INFORMATION',6,0,0,0,0,0)", 1159 "insert into vtiger_blocks values (57,21,'LBL_PO_INFORMATION',1,0,0,0,0,0)", 1160 "insert into vtiger_blocks values (58,21,'LBL_CUSTOM_INFORMATION',2,0,0,0,0,0)", 1161 "insert into vtiger_blocks values (59,21,'LBL_ADDRESS_INFORMATION',3,0,0,0,0,0)", 1162 "insert into vtiger_blocks values (60,21,'LBL_RELATED_PRODUCTS',4,0,0,0,0,0)", 1163 "insert into vtiger_blocks values (61,21,'LBL_TERMS_INFORMATION',5,0,0,0,0,0)", 1164 "insert into vtiger_blocks values (62,21,'LBL_DESCRIPTION_INFORMATION',6,0,0,0,0,0)", 1165 "insert into vtiger_blocks values (63,22,'LBL_SO_INFORMATION',1,0,0,0,0,0)", 1166 "insert into vtiger_blocks values (64,22,'LBL_CUSTOM_INFORMATION',2,0,0,0,0,0)", 1167 "insert into vtiger_blocks values (65,22,'LBL_ADDRESS_INFORMATION',3,0,0,0,0,0)", 1168 "insert into vtiger_blocks values (66,22,'LBL_RELATED_PRODUCTS',4,0,0,0,0,0)", 1169 "insert into vtiger_blocks values (67,22,'LBL_TERMS_INFORMATION',5,0,0,0,0,0)", 1170 "insert into vtiger_blocks values (68,22,'LBL_DESCRIPTION_INFORMATION',6,0,0,0,0,0)", 1171 "insert into vtiger_blocks values (69,23,'LBL_INVOICE_INFORMATION',1,0,0,0,0,0)", 1172 "insert into vtiger_blocks values (70,23,'LBL_CUSTOM_INFORMATION',2,0,0,0,0,0)", 1173 "insert into vtiger_blocks values (71,23,'LBL_ADDRESS_INFORMATION',3,0,0,0,0,0)", 1174 "insert into vtiger_blocks values (72,23,'LBL_RELATED_PRODUCTS',4,0,0,0,0,0)", 1175 "insert into vtiger_blocks values (73,23,'LBL_TERMS_INFORMATION',5,0,0,0,0,0)", 1176 "insert into vtiger_blocks values (74,23,'LBL_DESCRIPTION_INFORMATION',6,0,0,0,0,0)" 1177 ); 1178 foreach($insert_query_array5 as $query) 1179 { 1180 Execute($query); 1181 } 1182 1183 $update_query_array2 = Array( 1184 "update vtiger_tab set name='Vendors', tablabel='Vendors' where tabid=18", 1185 "update vtiger_tab set name='PriceBooks', tablabel='PriceBooks' where tabid=19", 1186 "update vtiger_tab set presence=0 where tabid in(18,19)", 1187 "update vtiger_relatedlists set label='PriceBooks' where tabid=14 and related_tabid=19" 1188 ); 1189 foreach($update_query_array2 as $query) 1190 { 1191 Execute($query); 1192 } 1193 1194 $delete_query1 = "delete from vtiger_actionmapping where actionname in ('SavePriceBook','SaveVendor','PriceBookEditView','VendorEditView','DeletePriceBook','DeleteVendor','PriceBookDetailView','VendorDetailView')"; 1195 Execute($query); 1196 1197 $insert_query_array6 = Array( 1198 "insert into vtiger_customview values(".$conn->getUniqueID('vtiger_customview').",'All',1,0,'Leads')", 1199 "insert into vtiger_customview values(".$conn->getUniqueID('vtiger_customview').",'All',1,0,'Accounts')", 1200 "insert into vtiger_customview values(".$conn->getUniqueID('vtiger_customview').",'All',1,0,'Contacts')", 1201 "insert into vtiger_customview values(".$conn->getUniqueID('vtiger_customview').",'All',1,0,'Potentials')", 1202 "insert into vtiger_customview values(".$conn->getUniqueID('vtiger_customview').",'All',1,0,'HelpDesk')", 1203 "insert into vtiger_customview values(".$conn->getUniqueID('vtiger_customview').",'All',1,0,'Quotes')", 1204 "insert into vtiger_customview values(".$conn->getUniqueID('vtiger_customview').",'All',1,0,'Activities')", 1205 "insert into vtiger_customview values(".$conn->getUniqueID('vtiger_customview').",'All',1,0,'Emails')", 1206 "insert into vtiger_customview values(".$conn->getUniqueID('vtiger_customview').",'All',1,0,'Invoice')", 1207 "insert into vtiger_customview values(".$conn->getUniqueID('vtiger_customview').",'All',1,0,'Notes')", 1208 "insert into vtiger_customview values(".$conn->getUniqueID('vtiger_customview').",'All',1,0,'PriceBooks')", 1209 "insert into vtiger_customview values(".$conn->getUniqueID('vtiger_customview').",'All',1,0,'Products')", 1210 "insert into vtiger_customview values(".$conn->getUniqueID('vtiger_customview').",'All',1,0,'PurchaseOrder')", 1211 1212 "insert into vtiger_customview values(".$conn->getUniqueID('vtiger_customview').",'All',1,0,'SalesOrder')", 1213 "insert into vtiger_customview values(".$conn->getUniqueID('vtiger_customview').",'All',1,0,'Vendors')", 1214 "insert into vtiger_customview values(".$conn->getUniqueID('vtiger_customview').",'All',1,0,'Faq')" 1215 ); 1216 foreach($insert_query_array6 as $query) 1217 { 1218 Execute($query); 1219 } 1220 1221 $res=$conn->query("select cvid from vtiger_customview where viewname='All' and entitytype='Leads'"); 1222 $cvid = $conn->query_result($res,0,"cvid"); 1223 1224 $insert_query_array7 = Array( 1225 "insert into vtiger_cvcolumnlist values ($cvid,0,'vtiger_leaddetails:lastname:lastname:Leads_Last_Name:V')", 1226 "insert into vtiger_cvcolumnlist values ($cvid,1,'vtiger_leaddetails:firstname:firstname:Leads_First_Name:V')", 1227 "insert into vtiger_cvcolumnlist values ($cvid,2,'vtiger_leaddetails:company:company:Leads_Company:V')", 1228 "insert into vtiger_cvcolumnlist values ($cvid,3,'vtiger_leadaddress:phone:phone:Leads_Phone:V')", 1229 "insert into vtiger_cvcolumnlist values ($cvid,4,'vtiger_leadsubdetails:website:website:Leads_Website:V')", 1230 "insert into vtiger_cvcolumnlist values ($cvid,5,'vtiger_leaddetails:email:email:Leads_Email:V')", 1231 "insert into vtiger_cvcolumnlist values ($cvid,6,'vtiger_crmentity:smownerid:assigned_user_id:Leads_Assigned_To:V')" 1232 ); 1233 foreach($insert_query_array7 as $query) 1234 { 1235 Execute($query); 1236 } 1237 1238 $res=$conn->query("select cvid from vtiger_customview where viewname='All' and entitytype='Accounts'"); 1239 $cvid = $conn->query_result($res,0,"cvid"); 1240 1241 $insert_query_array8 = Array( 1242 "insert into vtiger_cvcolumnlist values ($cvid,0,'vtiger_account:accountname:accountname:Accounts_Account_Name:V')", 1243 "insert into vtiger_cvcolumnlist values ($cvid,1,'vtiger_accountbillads:city:bill_city:Accounts_City:V')", 1244 "insert into vtiger_cvcolumnlist values ($cvid,2,'vtiger_account:website:website:Accounts_Website:V')", 1245 "insert into vtiger_cvcolumnlist values ($cvid,3,'vtiger_account:phone:phone:Accounts_Phone:V')", 1246 "insert into vtiger_cvcolumnlist values ($cvid,4,'vtiger_crmentity:smownerid:assigned_user_id:Accounts_Assigned_To:V')" 1247 ); 1248 foreach($insert_query_array8 as $query) 1249 { 1250 Execute($query); 1251 } 1252 1253 $res=$conn->query("select cvid from vtiger_customview where viewname='All' and entitytype='Contacts'"); 1254 $cvid = $conn->query_result($res,0,"cvid"); 1255 1256 $insert_query_array9 = Array( 1257 "insert into vtiger_cvcolumnlist values ($cvid,0,'vtiger_contactdetails:firstname:firstname:Contacts_First_Name:V')", 1258 "insert into vtiger_cvcolumnlist values ($cvid,1,'vtiger_contactdetails:lastname:lastname:Contacts_Last_Name:V')", 1259 "insert into vtiger_cvcolumnlist values ($cvid,2,'vtiger_contactdetails:title:title:Contacts_Title:V')", 1260 "insert into vtiger_cvcolumnlist values ($cvid,3,'vtiger_contactdetails:accountid:account_id:Contacts_Account_Name:I')", 1261 "insert into vtiger_cvcolumnlist values ($cvid,4,'vtiger_contactdetails:email:email:Contacts_Email:V')", 1262 "insert into vtiger_cvcolumnlist values ($cvid,5,'vtiger_contactdetails:phone:phone:Contacts_Office_Phone:V')", 1263 "insert into vtiger_cvcolumnlist values ($cvid,6,'vtiger_crmentity:smownerid:assigned_user_id:Contacts_Assigned_To:V')" 1264 ); 1265 foreach($insert_query_array9 as $query) 1266 { 1267 Execute($query); 1268 } 1269 1270 $res=$conn->query("select cvid from vtiger_customview where viewname='All' and entitytype='Potentials'"); 1271 $cvid = $conn->query_result($res,0,"cvid"); 1272 1273 $insert_query_array10 = Array( 1274 "insert into vtiger_cvcolumnlist values ($cvid,0,'vtiger_potential:potentialname:potentialname:Potentials_Potential_Name:V')", 1275 "insert into vtiger_cvcolumnlist values ($cvid,1,'vtiger_potential:accountid:account_id:Potentials_Account_Name:V')", 1276 "insert into vtiger_cvcolumnlist values ($cvid,2,'vtiger_potential:amount:amount:Potentials_Amount:N')", 1277 "insert into vtiger_cvcolumnlist values ($cvid,3,'vtiger_potential:closingdate:closingdate:Potentials_Expected_Close_Date:D')", 1278 "insert into vtiger_cvcolumnlist values ($cvid,4,'vtiger_crmentity:smownerid:assigned_user_id:Potentials_Assigned_To:V')" 1279 ); 1280 foreach($insert_query_array10 as $query) 1281 { 1282 Execute($query); 1283 } 1284 1285 $res=$conn->query("select cvid from vtiger_customview where viewname='All' and entitytype='HelpDesk'"); 1286 $cvid = $conn->query_result($res,0,"cvid"); 1287 1288 $insert_query_array11 = Array( 1289 "insert into vtiger_cvcolumnlist values ($cvid,0,'vtiger_crmentity:crmid::HelpDesk_Ticket_ID:I')", 1290 "insert into vtiger_cvcolumnlist values ($cvid,1,'vtiger_troubletickets:title:ticket_title:HelpDesk_Title:V')", 1291 "insert into vtiger_cvcolumnlist values ($cvid,2,'vtiger_troubletickets:parent_id:parent_id:HelpDesk_Related_to:I')", 1292 "insert into vtiger_cvcolumnlist values ($cvid,3,'vtiger_troubletickets:status:ticketstatus:HelpDesk_Status:V')", 1293 "insert into vtiger_cvcolumnlist values ($cvid,4,'vtiger_troubletickets:priority:ticketpriorities:HelpDesk_Priority:V')", 1294 "insert into vtiger_cvcolumnlist values ($cvid,5,'vtiger_crmentity:smownerid:assigned_user_id:HelpDesk_Assigned_To:V')" 1295 ); 1296 foreach($insert_query_array11 as $query) 1297 { 1298 Execute($query); 1299 } 1300 1301 1302 $res=$conn->query("select cvid from vtiger_customview where viewname='All' and entitytype='Quotes'"); 1303 $cvid = $conn->query_result($res,0,"cvid"); 1304 1305 $insert_query_array12 = Array( 1306 "insert into vtiger_cvcolumnlist values ($cvid,0,'vtiger_crmentity:crmid::Quotes_Quote_ID:I')", 1307 "insert into vtiger_cvcolumnlist values ($cvid,1,'vtiger_quotes:subject:subject:Quotes_Subject:V')", 1308 "insert into vtiger_cvcolumnlist values ($cvid,2,'vtiger_quotes:quotestage:quotestage:Quotes_Quote_Stage:V')", 1309 "insert into vtiger_cvcolumnlist values ($cvid,3,'vtiger_quotes:potentialid:potential_id:Quotes_Potential_Name:I')", 1310 "insert into vtiger_cvcolumnlist values ($cvid,4,'vtiger_quotes:accountid:account_id:Quotes_Account_Name:I')", 1311 "insert into vtiger_cvcolumnlist values ($cvid,5,'vtiger_quotes:total:hdnGrandTotal:Quotes_Total:I')", 1312 "insert into vtiger_cvcolumnlist values ($cvid,6,'vtiger_crmentity:smownerid:assigned_user_id:Quotes_Assigned_To:V')" 1313 ); 1314 foreach($insert_query_array12 as $query) 1315 { 1316 Execute($query); 1317 } 1318 1319 $res=$conn->query("select cvid from vtiger_customview where viewname='All' and entitytype='Activities'"); 1320 $cvid = $conn->query_result($res,0,"cvid"); 1321 1322 $insert_query_array13 = Array( 1323 "insert into vtiger_cvcolumnlist values ($cvid,0,'vtiger_activity:status:taskstatus:Activities_Status:V')", 1324 "insert into vtiger_cvcolumnlist values ($cvid,1,'vtiger_activity:activitytype:activitytype:Activities_Type:V')", 1325 "insert into vtiger_cvcolumnlist values ($cvid,2,'vtiger_activity:subject:subject:Activities_Subject:V')", 1326 "insert into vtiger_cvcolumnlist values ($cvid,3,'vtiger_contactdetails:lastname:lastname:Activities_Contact_Name:V')", 1327 "insert into vtiger_cvcolumnlist values ($cvid,4,'vtiger_seactivityrel:crmid:parent_id:Activities_Related_To:V')", 1328 "insert into vtiger_cvcolumnlist values ($cvid,5,'vtiger_activity:date_start:date_start:Activities_Start_Date:D')", 1329 "insert into vtiger_cvcolumnlist values ($cvid,6,'vtiger_activity:due_date:due_date:Activities_End_Date:D')", 1330 "insert into vtiger_cvcolumnlist values ($cvid,7,'vtiger_crmentity:smownerid:assigned_user_id:Activities_Assigned_To:V')" 1331 ); 1332 foreach($insert_query_array13 as $query) 1333 { 1334 Execute($query); 1335 } 1336 1337 1338 $res=$conn->query("select cvid from vtiger_customview where viewname='All' and entitytype='Emails'"); 1339 $cvid = $conn->query_result($res,0,"cvid"); 1340 1341 $insert_query_array14 = Array( 1342 "insert into vtiger_cvcolumnlist values ($cvid,0,'vtiger_activity:subject:subject:Emails_Subject:V')", 1343 "insert into vtiger_cvcolumnlist values ($cvid,1,'vtiger_seactivityrel:crmid:parent_id:Emails_Related_To:I')", 1344 "insert into vtiger_cvcolumnlist values ($cvid,2,'vtiger_activity:date_start:date_start:Emails_Date_Sent:D')", 1345 "insert into vtiger_cvcolumnlist values ($cvid,3,'vtiger_crmentity:smownerid:assigned_user_id:Emails_Assigned_To:V')" 1346 ); 1347 foreach($insert_query_array14 as $query) 1348 { 1349 Execute($query); 1350 } 1351 1352 $res=$conn->query("select cvid from vtiger_customview where viewname='All' and entitytype='Invoice'"); 1353 $cvid = $conn->query_result($res,0,"cvid"); 1354 1355 $insert_query_array15 = Array( 1356 "insert into vtiger_cvcolumnlist values ($cvid,0,'vtiger_crmentity:crmid::Invoice_Invoice_Id:I')", 1357 "insert into vtiger_cvcolumnlist values ($cvid,1,'vtiger_invoice:subject:subject:Invoice_Subject:V')", 1358 "insert into vtiger_cvcolumnlist values ($cvid,2,'vtiger_invoice:salesorderid:salesorder_id:Invoice_Sales_Order:V')", 1359 "insert into vtiger_cvcolumnlist values ($cvid,3,'vtiger_invoice:invoicestatus:invoicestatus:Invoice_Status:V')", 1360 "insert into vtiger_cvcolumnlist values ($cvid,4,'vtiger_invoice:total:hdnGrandTotal:Invoice_Total:I')", 1361 "insert into vtiger_cvcolumnlist values ($cvid,5,'vtiger_crmentity:smownerid:assigned_user_id:Invoice_Assigned_To:V')" 1362 ); 1363 foreach($insert_query_array15 as $query) 1364 { 1365 Execute($query); 1366 } 1367 1368 1369 $res=$conn->query("select cvid from vtiger_customview where viewname='All' and entitytype='Notes'"); 1370 $cvid = $conn->query_result($res,0,"cvid"); 1371 1372 $insert_query_array16 = Array( 1373 "insert into vtiger_cvcolumnlist values ($cvid,0,'vtiger_notes:title:title:Notes_Title:V')", 1374 "insert into vtiger_cvcolumnlist values ($cvid,1,'vtiger_notes:contact_id:contact_id:Notes_Contact_Name:I')", 1375 "insert into vtiger_cvcolumnlist values ($cvid,2,'vtiger_senotesrel:crmid:parent_id:Notes_Related_to:I')", 1376 "insert into vtiger_cvcolumnlist values ($cvid,3,'vtiger_notes:filename:filename:Notes_File:V')", 1377 "insert into vtiger_cvcolumnlist values ($cvid,4,'vtiger_crmentity:modifiedtime:modifiedtime:Notes_Modified_Time:V')" 1378 ); 1379 foreach($insert_query_array16 as $query) 1380 { 1381 Execute($query); 1382 } 1383 1384 $res=$conn->query("select cvid from vtiger_customview where viewname='All' and entitytype='PriceBooks'"); 1385 $cvid = $conn->query_result($res,0,"cvid"); 1386 1387 $insert_query_array17 = Array( 1388 "insert into vtiger_cvcolumnlist values ($cvid,1,'vtiger_pricebook:bookname:bookname:PriceBooks_Price_Book_Name:V')", 1389 "insert into vtiger_cvcolumnlist values ($cvid,2,'vtiger_pricebook:active:active:PriceBooks_Active:V')" 1390 ); 1391 foreach($insert_query_array17 as $query) 1392 { 1393 Execute($query); 1394 } 1395 1396 1397 $res=$conn->query("select cvid from vtiger_customview where viewname='All' and entitytype='Products'"); 1398 $cvid = $conn->query_result($res,0,"cvid"); 1399 1400 $insert_query_array18 = Array( 1401 "insert into vtiger_cvcolumnlist values ($cvid,0,'vtiger_products:productname:productname:Products_Product_Name:V')", 1402 "insert into vtiger_cvcolumnlist values ($cvid,1,'vtiger_products:productcode:productcode:Products_Product_Code:V')", 1403 "insert into vtiger_cvcolumnlist values ($cvid,2,'vtiger_products:commissionrate:commissionrate:Products_Commission_Rate:V')", 1404 "insert into vtiger_cvcolumnlist values ($cvid,3,'vtiger_products:qty_per_unit:qty_per_unit:Products_Qty/Unit:V')", 1405 "insert into vtiger_cvcolumnlist values ($cvid,4,'vtiger_products:unit_price:unit_price:Products_Unit_Price:V')" 1406 ); 1407 foreach($insert_query_array18 as $query) 1408 { 1409 Execute($query); 1410 } 1411 1412 $res=$conn->query("select cvid from vtiger_customview where viewname='All' and entitytype='PurchaseOrder'"); 1413 $cvid = $conn->query_result($res,0,"cvid"); 1414 1415 $insert_query_array19 = Array( 1416 "insert into vtiger_cvcolumnlist values($cvid,0,'vtiger_crmentity:crmid::PurchaseOrder_Order_Id:I')", 1417 "insert into vtiger_cvcolumnlist values($cvid,1,'vtiger_purchaseorder:subject:subject:PurchaseOrder_Subject:V')", 1418 "insert into vtiger_cvcolumnlist values($cvid,2,'vtiger_purchaseorder:vendorid:vendor_id:PurchaseOrder_Vendor_Name:I')", 1419 "insert into vtiger_cvcolumnlist values($cvid,3,'vtiger_purchaseorder:tracking_no:tracking_no:PurchaseOrder_Tracking_Number:V')", 1420 "insert into vtiger_cvcolumnlist values($cvid,4,'vtiger_crmentity:smownerid:assigned_user_id:PurchaseOrder_Assigned_To:V')" 1421 ); 1422 foreach($insert_query_array19 as $query) 1423 { 1424 Execute($query); 1425 } 1426 1427 $res=$conn->query("select cvid from vtiger_customview where viewname='All' and entitytype='SalesOrder'"); 1428 $cvid = $conn->query_result($res,0,"cvid"); 1429 1430 $insert_query_array20 = Array( 1431 "insert into vtiger_cvcolumnlist values ($cvid,0,'vtiger_crmentity:crmid::SalesOrder_Order_Id:I')", 1432 "insert into vtiger_cvcolumnlist values ($cvid,1,'vtiger_salesorder:subject:subject:SalesOrder_Subject:V')", 1433 "insert into vtiger_cvcolumnlist values ($cvid,2,'vtiger_account:accountid:account_id:SalesOrder_Account_Name:V')", 1434 "insert into vtiger_cvcolumnlist values ($cvid,3,'vtiger_quotes:quoteid:quote_id:SalesOrder_Quote_Name:I')", 1435 "insert into vtiger_cvcolumnlist values ($cvid,4,'vtiger_salesorder:total:hdnGrandTotal:SalesOrder_Total:V')", 1436 "insert into vtiger_cvcolumnlist values ($cvid,5,'vtiger_crmentity:smownerid:assigned_user_id:SalesOrder_Assigned_To:V')" 1437 ); 1438 foreach($insert_query_array20 as $query) 1439 { 1440 Execute($query); 1441 } 1442 1443 $res=$conn->query("select cvid from vtiger_customview where viewname='All' and entitytype='Vendors'"); 1444 $cvid = $conn->query_result($res,0,"cvid"); 1445 1446 $insert_query_array21 = Array( 1447 "insert into vtiger_cvcolumnlist values ($cvid,0,'vtiger_vendor:vendorname:vendorname:Vendors_Vendor_Name:V')", 1448 "insert into vtiger_cvcolumnlist values ($cvid,1,'vtiger_vendor:phone:phone:Vendors_Phone:V')", 1449 "insert into vtiger_cvcolumnlist values ($cvid,2,'vtiger_vendor:email:email:Vendors_Email:V')", 1450 "insert into vtiger_cvcolumnlist values ($cvid,3,'vtiger_vendor:category:category:Vendors_Category:V')" 1451 ); 1452 foreach($insert_query_array21 as $query) 1453 { 1454 Execute($query); 1455 } 1456 1457 $res=$conn->query("select cvid from vtiger_customview where viewname='All' and entitytype='Faq'"); 1458 $cvid = $conn->query_result($res,0,"cvid"); 1459 1460 $insert_query_array22 = Array( 1461 "insert into vtiger_cvcolumnlist values ($cvid,0,'vtiger_faq:id::Faq_FAQ_Id:I')", 1462 "insert into vtiger_cvcolumnlist values ($cvid,1,'vtiger_faq:question:question:Faq_Question:V')", 1463 "insert into vtiger_cvcolumnlist values ($cvid,2,'vtiger_faq:category:faqcategories:Faq_Category:V')", 1464 "insert into vtiger_cvcolumnlist values ($cvid,3,'vtiger_faq:product_id:product_id:Faq_Product_Name:I')", 1465 "insert into vtiger_cvcolumnlist values ($cvid,4,'vtiger_crmentity:createdtime:createdtime:Faq_Created_Time:D')", 1466 "insert into vtiger_cvcolumnlist values ($cvid,5,'vtiger_crmentity:modifiedtime:modifiedtime:Faq_Modified_Time:D')" 1467 ); 1468 foreach($insert_query_array22 as $query) 1469 { 1470 Execute($query); 1471 } 1472 1473 1474 $update_query_array3 = Array( 1475 "update vtiger_field set uitype=53 where tabid=2 and columnname='smownerid'", 1476 "update vtiger_field set uitype=53 where tabid=4 and columnname='smownerid'", 1477 "update vtiger_field set uitype=53 where tabid=20 and columnname='smownerid'", 1478 "update vtiger_field set uitype=53 where tabid=22 and columnname='smownerid'", 1479 "update vtiger_field set uitype=53 where tabid=23 and columnname='smownerid'" 1480 ); 1481 foreach($update_query_array3 as $query) 1482 { 1483 Execute($query); 1484 } 1485 1486 //Added on 26-06-06 - we cannot add foreign key in type MyISAM, so we have to change the type to InnoDB 1487 $alter_tables_array = Array("vtiger_groups","vtiger_potential","vtiger_quotes","vtiger_salesorder","vtiger_invoice","vtiger_purchaseorder","vtiger_products","vtiger_account","vtiger_contactdetails","vtiger_vendor","vtiger_users","vtiger_attachments","vtiger_profile"); 1488 foreach($alter_tables_array as $tablename) 1489 { 1490 Execute("alter table $tablename type=InnoDB"); 1491 } 1492 1493 1494 $create_query6 = "CREATE TABLE vtiger_accountgrouprelation ( accountid int(19) NOT NULL default '0', groupname varchar(100) default NULL, PRIMARY KEY (`accountid`))"; 1495 Execute($create_query6); 1496 1497 $alter_query_array8 = Array( 1498 "alter table vtiger_accountgrouprelation ADD CONSTRAINT fk_accountgrouprelation FOREIGN KEY (accountid) REFERENCES vtiger_account(accountid) ON DELETE CASCADE", 1499 "alter table vtiger_accountgrouprelation ADD CONSTRAINT fk_accountgrouprelation2 FOREIGN KEY (groupname) REFERENCES vtiger_groups(groupname) ON DELETE CASCADE" 1500 ); 1501 foreach($alter_query_array8 as $query) 1502 { 1503 Execute($query); 1504 } 1505 1506 $create_query7 = "CREATE TABLE vtiger_contactgrouprelation ( contactid int(19) NOT NULL default '0', groupname varchar(100) default NULL, PRIMARY KEY (`contactid`))"; 1507 Execute($create_query7); 1508 1509 $alter_query_array9 = Array( 1510 "alter table vtiger_contactgrouprelation ADD CONSTRAINT fk_contactgrouprelation FOREIGN KEY (contactid) REFERENCES vtiger_contactdetails(contactid) ON DELETE CASCADE", 1511 "alter table vtiger_contactgrouprelation ADD CONSTRAINT fk_contactgrouprelation2 FOREIGN KEY (groupname) REFERENCES vtiger_groups(groupname) ON DELETE CASCADE" 1512 ); 1513 foreach($alter_query_array9 as $query) 1514 { 1515 Execute($query); 1516 } 1517 1518 1519 $create_query10 = "CREATE TABLE vtiger_potentialgrouprelation ( potentialid int(19) NOT NULL default '0', groupname varchar(100) default NULL, PRIMARY KEY (`potentialid`))"; 1520 Execute($create_query10); 1521 1522 $alter_query_array10 = Array( 1523 "alter table vtiger_potentialgrouprelation ADD CONSTRAINT fk_potentialgrouprelation FOREIGN KEY (potentialid) REFERENCES vtiger_potential(potentialid) ON DELETE CASCADE", 1524 "alter table vtiger_potentialgrouprelation ADD CONSTRAINT fk_potentialgrouprelation2 FOREIGN KEY (groupname) REFERENCES vtiger_groups(groupname) ON DELETE CASCADE" 1525 ); 1526 foreach($alter_query_array10 as $query) 1527 { 1528 Execute($query); 1529 } 1530 1531 $create_query11 = "CREATE TABLE vtiger_quotegrouprelation ( quoteid int(19) NOT NULL default '0', groupname varchar(100) default NULL, PRIMARY KEY (`quoteid`) )"; 1532 Execute($create_query11); 1533 1534 $alter_query_array11 = Array( 1535 "alter table vtiger_quotegrouprelation ADD CONSTRAINT fk_quotegrouprelation FOREIGN KEY (quoteid) REFERENCES vtiger_quotes(quoteid) ON DELETE CASCADE", 1536 "alter table vtiger_quotegrouprelation ADD CONSTRAINT fk_quotegrouprelation2 FOREIGN KEY (groupname) REFERENCES vtiger_groups(groupname) ON DELETE CASCADE" 1537 ); 1538 foreach($alter_query_array11 as $query) 1539 { 1540 Execute($query); 1541 } 1542 1543 $create_query12 = "CREATE TABLE vtiger_sogrouprelation ( salesorderid int(19) NOT NULL default '0', groupname varchar(100) default NULL, PRIMARY KEY (`salesorderid`) )"; 1544 Execute($create_query12); 1545 1546 $alter_query_array12 = Array( 1547 "alter table vtiger_sogrouprelation ADD CONSTRAINT fk_sogrouprelation FOREIGN KEY (salesorderid) REFERENCES vtiger_salesorder(salesorderid) ON DELETE CASCADE", 1548 "alter table vtiger_sogrouprelation ADD CONSTRAINT fk_sogrouprelation2 FOREIGN KEY (groupname) REFERENCES vtiger_groups(groupname) ON DELETE CASCADE" 1549 ); 1550 foreach($alter_query_array12 as $query) 1551 { 1552 Execute($query); 1553 } 1554 1555 $create_query13 = "CREATE TABLE vtiger_invoicegrouprelation ( invoiceid int(19) NOT NULL default '0', groupname varchar(100) default NULL, PRIMARY KEY (`invoiceid`))"; 1556 Execute($create_query13); 1557 1558 $alter_query_array13 = Array( 1559 "alter table vtiger_invoicegrouprelation ADD CONSTRAINT fk_invoicegrouprelation FOREIGN KEY (invoiceid) REFERENCES vtiger_invoice(invoiceid) ON DELETE CASCADE", 1560 "alter table vtiger_invoicegrouprelation ADD CONSTRAINT fk_invoicegrouprelation2 FOREIGN KEY (groupname) REFERENCES vtiger_groups(groupname) ON DELETE CASCADE" 1561 ); 1562 foreach($alter_query_array13 as $query) 1563 { 1564 Execute($query); 1565 } 1566 1567 $create_query14 = "CREATE TABLE vtiger_pogrouprelation ( purchaseorderid int(19) NOT NULL default '0', groupname varchar(100) default NULL, PRIMARY KEY (`purchaseorderid`))"; 1568 Execute($create_query14); 1569 1570 $alter_query_array14 = Array( 1571 "alter table vtiger_pogrouprelation ADD CONSTRAINT fk_pogrouprelation FOREIGN KEY (purchaseorderid) REFERENCES vtiger_purchaseorder(purchaseorderid) ON DELETE CASCADE", 1572 "alter table vtiger_pogrouprelation ADD CONSTRAINT fk_productgrouprelation2 FOREIGN KEY (groupname) REFERENCES vtiger_groups(groupname) ON DELETE CASCADE" 1573 ); 1574 foreach($alter_query_array14 as $query) 1575 { 1576 Execute($query); 1577 } 1578 1579 $alter_query1 = "ALTER TABLE vtiger_users ADD column lead_view VARCHAR(25) DEFAULT 'Today' AFTER homeorder"; 1580 Execute($alter_query1); 1581 1582 $update_query1 = "update vtiger_users set homeorder = 'ALVT,PLVT,QLTQ,CVLVT,HLT,OLV,GRT,OLTSO,ILTI,MNL'"; 1583 Execute($update_query1); 1584 1585 $alter_query2 = "ALTER TABLE vtiger_products change column imagename imagename text"; 1586 Execute($alter_query2); 1587 1588 $alter_query3 = "alter table vtiger_systems modify server varchar(50), modify server_username varchar(50), modify server_password varchar(50), add column smtp_auth char(5)"; 1589 Execute($alter_query3); 1590 1591 $alter_query_array15 = Array( 1592 "alter table vtiger_users add column imagename varchar(250)", 1593 "alter table vtiger_users add column tagcloud varchar(250)" 1594 ); 1595 foreach($alter_query_array15 as $query) 1596 { 1597 Execute($query); 1598 } 1599 1600 $alter_query_array16 = Array( 1601 "alter table vtiger_systems change column server server varchar(80) default NULL", 1602 "alter table vtiger_systems change column server_username server_username varchar(80) default NULL" 1603 ); 1604 foreach($alter_query_array16 as $query) 1605 { 1606 Execute($query); 1607 } 1608 1609 1610 $create_query15 = "create table vtiger_portal(portalid int(19), portalname varchar(255) NOT NULL, portalurl varchar(255) NOT NULL,sequence int(3) NOT NULL, PRIMARY KEY (portalid))"; 1611 Execute($create_query15); 1612 1613 $alter_query = "ALTER TABLE vtiger_field ADD column info_type varchar(20) default NULL after quickcreatesequence"; 1614 Execute($alter_query); 1615 1616 //$update_query2 = "UPDATE vtiger_field SET fieldlabel = 'Reference' WHERE tabid = 4 and tablename = 'contactdetails' and fieldname='reference'"; 1617 //changed in 24-04-06 because the reference has not been entered into the vtiger_field table. 1618 $update_query2 = "insert into vtiger_field values (4,".$conn->getUniqueID("vtiger_field").",'reference','contactdetails',1,'56','reference','Reference',1,0,0,10,23,4,1,'C~O',1,null,'ADV')"; 1619 Execute($update_query2); 1620 1621 $update_query_array4 = Array( 1622 "UPDATE vtiger_field SET info_type = 'BAS'", 1623 1624 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 7 and fieldlabel = 'Website'", 1625 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 7 and fieldlabel = 'Industry'", 1626 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 7 and fieldlabel = 'Annual Revenue'", 1627 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 7 and fieldlabel = 'No Of Employees'", 1628 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 7 and fieldlabel = 'Yahoo Id'", 1629 1630 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 6 and fieldlabel = 'Ticker Symbol'", 1631 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 6 and fieldlabel = 'Other Phone'", 1632 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 6 and fieldlabel = 'Member Of'", 1633 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 6 and fieldlabel = 'Employees'", 1634 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 6 and fieldlabel = 'Other Email'", 1635 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 6 and fieldlabel = 'Ownership'", 1636 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 6 and fieldlabel = 'Rating'", 1637 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 6 and fieldlabel = 'industry'", 1638 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 6 and fieldlabel = 'SIC Code'", 1639 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 6 and fieldlabel = 'Type'", 1640 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 6 and fieldlabel = 'Annual Revenue'", 1641 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 6 and fieldlabel = 'Email Opt Out'", 1642 1643 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 4 and fieldlabel = 'Home Phone'", 1644 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 4 and fieldlabel = 'Department'", 1645 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 4 and fieldlabel = 'Birthdate'", 1646 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 4 and fieldlabel = 'Email'", 1647 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 4 and fieldlabel = 'Reports To'", 1648 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 4 and fieldlabel = 'Assistant'", 1649 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 4 and fieldlabel = 'Yahoo Id'", 1650 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 4 and fieldlabel = 'Assistant Phone'", 1651 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 4 and fieldlabel = 'Do Not Call'", 1652 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 4 and fieldlabel = 'Email Opt Out'", 1653 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 4 and fieldlabel = 'Reference'", 1654 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 4 and fieldlabel = 'Portal User'", 1655 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 4 and fieldlabel = 'Support Start Date'", 1656 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 4 and fieldlabel = 'Support End Date'", 1657 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 4 and fieldlabel = 'Contact Image'", 1658 1659 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 14 and fieldlabel = 'Usage Unit'", 1660 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 14 and fieldlabel = 'Qty/Unit'", 1661 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 14 and fieldlabel = 'Qty In Stock'", 1662 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 14 and fieldlabel = 'Reorder Level'", 1663 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 14 and fieldlabel = 'Handler'", 1664 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 14 and fieldlabel = 'Qty In Demand'", 1665 "UPDATE vtiger_field SET info_type = 'ADV' WHERE tabid = 14 and fieldlabel = 'Product Image'" 1666 ); 1667 foreach($update_query_array4 as $query) 1668 { 1669 Execute($query); 1670 } 1671 1672 1673 $create_query16 = "CREATE TABLE vtiger_chat_msg ( `id` bigint(20) NOT NULL auto_increment, `chat_from` bigint(20) NOT NULL default '0', `chat_to` bigint(20) NOT NULL default '0', `born` timestamp NULL default '0000-00-00 00:00:00', `msg` varchar(255) NOT NULL, PRIMARY KEY (`id`), KEY `chat_to` (`chat_to`), KEY `chat_from` (`chat_from`), KEY `born` (`born`)) ENGINE=InnoDB"; 1674 Execute($create_query16); 1675 $create_query17 = "CREATE TABLE vtiger_chat_pchat ( `id` bigint(20) NOT NULL auto_increment, `msg` bigint(20) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `msg` (`msg`)) ENGINE=InnoDB"; 1676 Execute($create_query17); 1677 1678 $create_query18 = "CREATE TABLE vtiger_chat_pvchat ( `id` bigint(20) NOT NULL auto_increment, `msg` bigint(20) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `msg` (`msg`)) ENGINE=InnoDB"; 1679 Execute($create_query18); 1680 1681 $create_query19 = "CREATE TABLE vtiger_chat_users ( `id` bigint(20) NOT NULL auto_increment, `nick` varchar(50) NOT NULL, `session` varchar(50) NOT NULL, `ip` varchar(20) NOT NULL default '000.000.000.000', `ping` timestamp NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`), UNIQUE KEY `session` (`session`), UNIQUE KEY `nick` (`nick`), KEY `ping` (`ping`)) ENGINE=InnoDB"; 1682 Execute($create_query19); 1683 1684 $alter_query_array17 = Array( 1685 "ALTER TABLE `vtiger_chat_msg` ADD CONSTRAINT `chat_msg_ibfk_1` FOREIGN KEY (`chat_from`) REFERENCES `vtiger_chat_users` (`id`) ON DELETE CASCADE", 1686 1687 "ALTER TABLE `vtiger_chat_pchat` ADD CONSTRAINT `chat_pchat_ibfk_1` FOREIGN KEY (`msg`) REFERENCES `vtiger_chat_msg` (`id`) ON DELETE CASCADE", 1688 1689 "ALTER TABLE `vtiger_chat_pvchat` ADD CONSTRAINT `chat_pvchat_ibfk_1` FOREIGN KEY (`msg`) REFERENCES `vtiger_chat_msg` (`id`) ON DELETE CASCADE" 1690 ); 1691 foreach($alter_query_array17 as $query) 1692 { 1693 Execute($query); 1694 } 1695 1696 $create_query20 = "CREATE TABLE vtiger_freetags ( id int(19) NOT NULL, tag varchar(50) NOT NULL default '', raw_tag varchar(50) NOT NULL default '', PRIMARY KEY (id)) TYPE=MyISAM"; 1697 Execute($create_query20); 1698 1699 $create_query21 = "CREATE TABLE vtiger_freetagged_objects ( tag_id int(19) NOT NULL default '0', tagger_id int(19) NOT NULL default '0', object_id int(19) NOT NULL default '0', tagged_on datetime NOT NULL default '0000-00-00 00:00:00', module varchar(50) NOT NULL default '', PRIMARY KEY (`tag_id`,`tagger_id`,`object_id`), KEY `tag_id_index` (`tag_id`), KEY `tagger_id_index` (`tagger_id`), KEY `object_id_index` (`object_id`) 1700 ) TYPE=MyISAM"; 1701 Execute($create_query21); 1702 1703 $alter_query4 = "alter table vtiger_profile add column description text"; 1704 Execute($alter_query4); 1705 1706 $alter_query5 = "alter table vtiger_contactdetails add column imagename varchar(250) after currency"; 1707 Execute($alter_query5); 1708 1709 $alter_query = "ALTER TABLE vtiger_contactdetails ADD column reference varchar(3) default NULL after imagename"; 1710 Execute($alter_query); 1711 1712 $insert_query_array23 = Array( 1713 "insert into vtiger_blocks values(75,4,'LBL_IMAGE_INFORMATION',5,0,0,0,0,0)", 1714 "insert into vtiger_field values(4,".$conn->getUniqueID("vtiger_field").",'imagename','contactdetails',1,'69','imagename','Contact Image',1,0,0,100,1,75,1,'V~O',1,null,'ADV')", 1715 1716 "Insert into vtiger_field values(9,".$conn->getUniqueID("vtiger_field").",'visibility','activity',1,15,'visibility','Visibility',1,0,0,100,17,19,3,'V~O',1,null,'BAS')", 1717 "Insert into vtiger_field values(16,".$conn->getUniqueID("vtiger_field").",'visibility','activity',1,15,'visibility','Visibility',1,0,0,100,19,41,1,'V~O',1,null,'BAS')" 1718 ); 1719 foreach($insert_query_array23 as $query) 1720 { 1721 Execute($query); 1722 } 1723 1724 $alter_query6 = "alter table vtiger_activity add column visibility varchar(50) NOT NULL after notime"; 1725 Execute($alter_query6); 1726 1727 $create_query22 = "CREATE TABLE vtiger_visibility ( `visibilityid` int(19) NOT NULL auto_increment, `visibility` varchar(200) NOT NULL default '', `sortorderid` int(19) NOT NULL default '0', `presence` int(1) NOT NULL default '1', PRIMARY KEY (`visibilityid`), UNIQUE KEY `Visibility_VLY` (`visibility`)) ENGINE=InnoDB"; 1728 Execute($create_query22); 1729 1730 1731 $create_query23 = "CREATE TABLE vtiger_sharedcalendar ( `userid` int(19) NOT NULL default '0', `sharedid` int(19) NOT NULL default '0', PRIMARY KEY (`userid`,`sharedid`)) ENGINE=MyISAM"; 1732 Execute($create_query23); 1733 1734 $insert_query6 = "INSERT INTO vtiger_tab VALUES(26,'Campaigns',0,23,'Campaigns',null,null,1)"; 1735 Execute($insert_query6); 1736 $insert_query7 = "INSERT INTO vtiger_parenttabrel VALUES(2,26,1)"; 1737 Execute($insert_query7); 1738 1739 $insert_query8 = "insert into vtiger_blocks values(76,26,'LBL_CAMPAIGN_INFORMATION',1,0,0,0,0,0)"; 1740 Execute($insert_query8); 1741 $insert_query8 = "insert into vtiger_blocks values (77,26,'LBL_CUSTOM_INFORMATION',2,0,0,0,0,0)"; 1742 Execute($insert_query8); 1743 $insert_query9 = "insert into vtiger_blocks values(78,26,'LBL_DESCRIPTION_INFORMATION',3,0,0,0,0,0)"; 1744 Execute($insert_query9); 1745 1746 $insert_query_array24 = Array( 1747 "insert into vtiger_field values (26,".$conn->getUniqueID("vtiger_field").",'campaignname','campaign',1,'2','campaignname','Campaign Name',1,0,0,100,1,76,1,'V~M',0,1,'BAS')", 1748 "insert into vtiger_field values (26,".$conn->getUniqueID("vtiger_field").",'campaigntype','campaign',1,15,'campaigntype','Campaign Type',1,0,0,100,2,76,1,'V~O',0,5,'BAS')", 1749 "insert into vtiger_field values (26,".$conn->getUniqueID("vtiger_field").",'product_id','campaign',1,59,'product_id','Product',1,0,0,100,3,76,1,'I~O',0,5,'BAS')", 1750 "insert into vtiger_field values (26,".$conn->getUniqueID("vtiger_field").",'campaignstatus','campaign',1,15,'campaignstatus','Campaign Status',1,0,0,100,4,76,1,'V~O',0,5,'BAS')", 1751 "insert into vtiger_field values (26,".$conn->getUniqueID("vtiger_field").",'closingdate','campaign',1,'23','closingdate','Expected Close Date',1,0,0,100,5,76,1,'D~M',0,3,'BAS')", 1752 "insert into vtiger_field values (26,".$conn->getUniqueID("vtiger_field").",'expectedrevenue','campaign',1,'1','expectedrevenue','Expected Revenue',1,0,0,100,6,76,1,'I~O',1,null,'BAS')", 1753 "insert into vtiger_field values (26,".$conn->getUniqueID("vtiger_field").",'budgetcost','campaign',1,'1','budgetcost','Budget Cost',1,0,0,100,7,76,1,'I~O',1,null,'BAS')", 1754 "insert into vtiger_field values (26,".$conn->getUniqueID("vtiger_field").",'actualcost','campaign',1,'1','actualcost','Actual Cost',1,0,0,100,8,76,1,'I~O',1,null,'BAS')", 1755 "insert into vtiger_field values (26,".$conn->getUniqueID("vtiger_field").",'expectedresponse','campaign',1,'15','expectedresponse','Expected Response',1,0,0,100,9,76,1,'V~O',0,4,'BAS')", 1756 "insert into vtiger_field values (26,".$conn->getUniqueID("vtiger_field").",'smownerid','crmentity',1,'53','assigned_user_id','Assigned To',1,0,0,100,10,76,1,'V~M',1,null,'BAS')", 1757 "insert into vtiger_field values (26,".$conn->getUniqueID("vtiger_field").",'numsent','campaign',1,'9','numsent','Num Sent',1,0,0,100,11,76,1,'N~O',1,null,'BAS')", 1758 "insert into vtiger_field values (26,".$conn->getUniqueID("vtiger_field").",'sponsor','campaign',1,'1','sponsor','Sponsor',1,0,0,100,12,76,1,'V~O',1,null,'BAS')", 1759 "insert into vtiger_field values (26,".$conn->getUniqueID("vtiger_field").",'targetaudience','campaign',1,'1','targetaudience','Target Audience',1,0,0,100,13,76,1,'V~O',1,null,'BAS')", 1760 "insert into vtiger_field values (26,".$conn->getUniqueID("vtiger_field").",'targetsize','campaign',1,'1','targetsize','TargetSize',1,0,0,100,14,76,1,'N~O',1,null,'BAS')", 1761 "insert into vtiger_field values (26,".$conn->getUniqueID("vtiger_field").",'expectedresponsecount','campaign',1,'1','expectedresponsecount','Expected Response Count',1,0,0,100,17,76,1,'N~O',1,null,'BAS')", 1762 "insert into vtiger_field values (26,".$conn->getUniqueID("vtiger_field").",'expectedsalescount','campaign',1,'1','expectedsalescount','Expected Sales Count',1,0,0,100,15,76,1,'N~O',1,null,'BAS')", 1763 "insert into vtiger_field values (26,".$conn->getUniqueID("vtiger_field").",'expectedroi','campaign',1,'1','expectedroi','Expected ROI',1,0,0,100,19,76,1,'N~O',1,null,'BAS')", 1764 "insert into vtiger_field values (26,".$conn->getUniqueID("vtiger_field").",'actualresponsecount','campaign',1,'1','actualresponsecount','Actual Response Count',1,0,0,100,18,76,1,'N~O',1,null,'BAS')", 1765 "insert into vtiger_field values (26,".$conn->getUniqueID("vtiger_field").",'actualsalescount','campaign',1,'1','actualsalescount','Actual Sales Count',1,0,0,100,16,76,1,'N~O',1,null,'BAS')", 1766 "insert into vtiger_field values (26,".$conn->getUniqueID("vtiger_field").",'actualroi','campaign',1,'1','actualroi','Actual ROI',1,0,0,100,20,76,1,'N~O',1,null,'BAS')", 1767 "insert into vtiger_field values (26,".$conn->getUniqueID("vtiger_field").",'createdtime','crmentity',1,'70','createdtime','Created Time',1,0,0,100,15,76,2,'T~O',1,null,'BAS')", 1768 "insert into vtiger_field values (26,".$conn->getUniqueID("vtiger_field").",'modifiedtime','crmentity',1,'70','modifiedtime','Modified Time',1,0,0,100,16,76,2,'T~O',1,null,'BAS')", 1769 "insert into vtiger_field values (26,".$conn->getUniqueID("vtiger_field").",'description','crmentity',1,'19','description','Description',1,0,0,100,1,82,1,'V~O',1,null,'BAS')" 1770 ); 1771 foreach($insert_query_array24 as $query) 1772 { 1773 Execute($query); 1774 } 1775 1776 $insert_query_array25 = Array( 1777 "insert into vtiger_relatedlists values (".$conn->getUniqueID('vtiger_relatedlists').",".getTabid("Campaigns").",".getTabid("Contacts").",'get_contacts',1,'Contacts',0)", 1778 "insert into vtiger_relatedlists values (".$conn->getUniqueID('vtiger_relatedlists').",".getTabid("Campaigns").",".getTabid("Leads").",'get_leads',2,'Leads',0)" 1779 ); 1780 foreach($insert_query_array25 as $query) 1781 { 1782 Execute($query); 1783 } 1784 1785 1786 $insert_query_array26 = Array( 1787 "insert into vtiger_field values (7,".$conn->getUniqueID("vtiger_field").",'campaignid','leaddetails',1,'51','campaignid','Campaign Name',1,0,0,100,6,13,3,'I~O',1,null,'BAS')", 1788 "insert into vtiger_field values (4,".$conn->getUniqueID("vtiger_field").",'campaignid','contactdetails',1,'51','campaignid','Campaign Name',1,0,0,100,6,4,3,'I~O',1,null,'BAS')" 1789 ); 1790 foreach($insert_query_array26 as $query) 1791 { 1792 Execute($query); 1793 } 1794 1795 $create_query24 = " 1796 CREATE TABLE vtiger_campaign ( 1797 `campaignname` varchar(255) default NULL, 1798 `campaigntype` varchar(255) default NULL, 1799 `campaignstatus` varchar(255) default NULL, 1800 `expectedrevenue` int(19) default NULL, 1801 `budgetcost` int(19) default NULL, 1802 `actualcost` int(19) default NULL, 1803 `expectedresponse` varchar(255) default NULL, 1804 `numsent` decimal(11,0) default NULL, 1805 `product_id` int(19) default NULL, 1806 `sponsor` varchar(255) default NULL, 1807 `targetaudience` varchar(255) default NULL, 1808 `targetsize` int(19) default NULL, 1809 `expectedresponsecount` int(19) default NULL, 1810 `expectedsalescount` int(19) default NULL, 1811 `expectedroi` int(19) default NULL, 1812 `actualresponsecount` int(19) default NULL, 1813 `actualsalescount` int(19) default NULL, 1814 `actualroi` int(19) default NULL, 1815 `campaignid` int(19) NOT NULL, 1816 `closingdate` date default NULL, 1817 PRIMARY KEY (`campaignid`), 1818 KEY `idx_campaignstatus` (`campaignstatus`), 1819 KEY `idx_campaignname` (`campaignname`), 1820 KEY `idx_campaignid` (`campaignid`) 1821 ) ENGINE=InnoDB 1822 "; 1823 Execute($create_query24); 1824 1825 1826 //Added on 06-06-06 1827 $create_query25 = "CREATE TABLE vtiger_campaigncontrel ( 1828 `campaignid` int(19) NOT NULL default '0', 1829 `contactid` int(19) NOT NULL default '0', 1830 PRIMARY KEY (`campaignid`), 1831 KEY `campaigncontrel_contractid_idx` (`contactid`) 1832 ) ENGINE=InnoDB"; 1833 /* 1834 $create_query25 = "CREATE TABLE vtiger_campaigncontrel ( 1835 `campaignid` int(19) NOT NULL default '0', 1836 `contactid` int(19) NOT NULL default '0', 1837 PRIMARY KEY (`campaignid`), 1838 KEY `CampaignContRel_IDX1` (`contactid`), 1839 CONSTRAINT `fk_CampaignContRel2` FOREIGN KEY (`contactid`) REFERENCES `vtiger_contactdetails` (`contactid`) ON DELETE CASCADE, 1840 CONSTRAINT `fk_CampaignContRel1` FOREIGN KEY (`campaignid`) REFERENCES `vtiger_campaign` (`campaignid`) ON DELETE CASCADE 1841 ) ENGINE=InnoDB"; 1842 */ 1843 Execute($create_query25); 1844 1845 //Added on 06-06-06 1846 $create_table_query = "CREATE TABLE vtiger_campaigngrouprelation ( 1847 `campaignid` int(19) NOT NULL, 1848 `groupname` varchar(100) default NULL, 1849 PRIMARY KEY (`campaignid`), 1850 KEY `campaigngrouprelation_IDX1` (`groupname`) 1851 ) ENGINE=InnoDB"; 1852 /* 1853 $create_table_query = " 1854 CREATE TABLE vtiger_campaigngrouprelation ( 1855 `campaignid` int(19) NOT NULL, 1856 `groupname` varchar(100) default NULL, 1857 PRIMARY KEY (`campaignid`), 1858 KEY `campaigngrouprelation_IDX1` (`groupname`), 1859 CONSTRAINT `fk_campaigngrouprelation2` FOREIGN KEY (`groupname`) REFERENCES `vtiger_groups` (`groupname`) ON DELETE CASCADE, 1860 CONSTRAINT `fk_campaigngrouprelation1` FOREIGN KEY (`campaignid`) REFERENCES `vtiger_campaign` (`campaignid`) ON DELETE CASCADE 1861 ) ENGINE=InnoDB"; 1862 */ 1863 Execute($create_table_query); 1864 1865 1866 //Added on 06-06-06 1867 $create_query26 = "CREATE TABLE vtiger_campaignleadrel ( 1868 `campaignid` int(19) NOT NULL default '0', 1869 `leadid` int(19) NOT NULL default '0', 1870 PRIMARY KEY (`campaignid`), 1871 KEY `campaignleadrel_leadid_campaignid_idx` (`leadid`,`campaignid`) 1872 ) ENGINE=InnoDB"; 1873 /* 1874 $create_query26 = "CREATE TABLE vtiger_campaignleadrel ( 1875 `campaignid` int(19) NOT NULL default '0', 1876 `leadid` int(19) NOT NULL default '0', 1877 PRIMARY KEY (`campaignid`), 1878 KEY `CampaignLeadRel_IDX1` (`leadid`,`campaignid`), 1879 CONSTRAINT `fk_CampaignLeadRel1234` FOREIGN KEY (`campaignid`) REFERENCES `vtiger_campaign` (`campaignid`) ON DELETE CASCADE, 1880 CONSTRAINT `fk_CampaignLeadRel2423` FOREIGN KEY (`leadid`) REFERENCES `vtiger_leaddetails` (`leadid`) ON DELETE CASCADE 1881 ) ENGINE=InnoDB"; 1882 */ 1883 Execute($create_query26); 1884 1885 $create_table_query1 = "CREATE TABLE vtiger_campaignscf ( 1886 `campaignid` int(19) NOT NULL default '0', 1887 PRIMARY KEY (`campaignid`), 1888 CONSTRAINT `fk_CampaignsCF` FOREIGN KEY (`campaignid`) REFERENCES `vtiger_campaign` (`campaignid`) ON DELETE CASCADE 1889 ) ENGINE=InnoDB"; 1890 Execute($create_table_query1); 1891 1892 $alter_query_array18 = Array( 1893 "alter table vtiger_potential add column campaignid int(19) default NULL after probability", 1894 "alter table vtiger_potential drop column campaignsource", 1895 //"alter table vtiger_notes drop PRIMARY KEY contact_id", 1896 "alter table vtiger_notes drop PRIMARY KEY , add primary key(notesid)", 1897 "update vtiger_field set uitype=99 where fieldname='update_log' and tabid=13" 1898 ); 1899 foreach($alter_query_array18 as $query) 1900 { 1901 Execute($query); 1902 } 1903 1904 1905 //Added on 09-08-2006 1906 //In the next array we have add constraint for tables purchaseorder, salesorder, quotes and invoice where as the corresponding entity ids should not be 0 they should be NULL. so that this change has been done 1907 Execute("update vtiger_purchaseorder set contactid=NULL where contactid=0"); 1908 Execute("update vtiger_salesorder set contactid=NULL where contactid=0"); 1909 Execute("update vtiger_quotes set contactid=NULL where contactid=0"); 1910 Execute("update vtiger_quotes set potentialid=NULL where potentialid=0"); 1911 Execute("update vtiger_invoice set salesorderid=NULL where salesorderid=0"); 1912 1913 1914 //echo "<br><br><b>Database Modifications for Indexing and some missded tables starts here.....</b><br>"; 1915 //Added queries which are for indexing and the missing tables - Mickie - on 06-04-2006 1916 1917 $query_array = Array( 1918 1919 //"ALTER TABLE `vtiger_accountgrouprelation` DROP INDEX `fk_accountgrouprelation2`", 1920 //"ALTER TABLE `vtiger_activity` DROP INDEX `status`", 1921 "ALTER TABLE `vtiger_carrier` DROP INDEX `carrier_UK0`", 1922 //"ALTER TABLE `vtiger_contactgrouprelation` DROP INDEX `fk_contactgrouprelation2`", 1923 //"ALTER TABLE `vtiger_customview` DROP INDEX `customview`", 1924 //"ALTER TABLE `vtiger_def_org_field` DROP INDEX `tabid`", 1925 //"ALTER TABLE `vtiger_field` DROP INDEX `tabid`", 1926 "ALTER TABLE `vtiger_freetagged_objects` DROP INDEX `tagger_id_index`", 1927 "ALTER TABLE `vtiger_freetagged_objects` DROP INDEX `object_id_index`", 1928 //"ALTER TABLE `vtiger_groups` DROP INDEX `groupname`", 1929 //"ALTER TABLE `vtiger_invoicegrouprelation` DROP INDEX `fk_invoicegrouprelation2`", 1930 //"ALTER TABLE `vtiger_leadscf` DROP COLUMN `cf_354`", 1931 //"ALTER TABLE `vtiger_leadscf` DROP COLUMN `cf_358`", 1932 //"ALTER TABLE `vtiger_leadscf` DROP COLUMN `cf_360`", 1933 //"ALTER TABLE `vtiger_pogrouprelation` DROP INDEX `fk_productgrouprelation2`", 1934 //"ALTER TABLE `vtiger_potential` DROP INDEX `potentialid`", 1935 //"ALTER TABLE `vtiger_potentialgrouprelation` DROP INDEX `fk_potentialgrouprelation2`", 1936 //"ALTER TABLE `vtiger_profile2field` DROP INDEX `tabid`", 1937 "ALTER TABLE `vtiger_profile2tab` DROP INDEX `idx_profile2tab`", 1938 //"ALTER TABLE `vtiger_quotegrouprelation` DROP INDEX `fk_quotegrouprelation2`", 1939 "ALTER TABLE `vtiger_reportmodules` DROP INDEX `reportmodules_IDX0`", 1940 "ALTER TABLE `vtiger_reportsortcol` DROP INDEX `reportsortcol_IDX0`", 1941 "ALTER TABLE `vtiger_reportsummary` DROP INDEX `reportsummary_IDX0`", 1942 //"ALTER TABLE `vtiger_seattachmentsrel` DROP INDEX `attachmentsid`", 1943 //"ALTER TABLE `vtiger_sogrouprelation` DROP INDEX `fk_sogrouprelation2`", 1944 //"ALTER TABLE `vtiger_tab` DROP INDEX `tabid`", 1945 //"ALTER TABLE `vtiger_troubletickets` DROP INDEX `status`", 1946 "ALTER TABLE `vtiger_accountgrouprelation` TYPE=InnoDB, COMMENT='', ROW_FORMAT=COMPACT", 1947 "ALTER TABLE `vtiger_activity_reminder` TYPE=MyISAM, COMMENT='', ROW_FORMAT=FIXED", 1948 "ALTER TABLE `vtiger_activsubtype` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 1949 "ALTER TABLE `vtiger_contactgrouprelation` TYPE=InnoDB, COMMENT='', ROW_FORMAT=COMPACT", 1950 //"DROP TABLE `crmentity_seq`", 1951 //"ALTER TABLE `vtiger_customerdetails` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 1952 //"DROP TABLE `customfield_sequence_seq`", 1953 "ALTER TABLE `vtiger_customview_seq` TYPE=MyISAM, COMMENT='', ROW_FORMAT=FIXED", 1954 "ALTER TABLE `vtiger_def_org_field` TYPE=MyISAM, COMMENT='', ROW_FORMAT=FIXED", 1955 "ALTER TABLE `vtiger_def_org_share` TYPE=MyISAM, COMMENT='', ROW_FORMAT=FIXED", 1956 "ALTER TABLE `vtiger_def_org_share_seq` TYPE=MyISAM, COMMENT='', ROW_FORMAT=FIXED", 1957 //"ALTER TABLE `vtiger_defaultcv` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 1958 "ALTER TABLE `vtiger_durationhrs` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 1959 "ALTER TABLE `vtiger_durationmins` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 1960 "ALTER TABLE `vtiger_emailtemplates` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 1961 //"ALTER TABLE `vtiger_emailtemplates_seq` TYPE=MyISAM, COMMENT='', ROW_FORMAT=FIXED", 1962 "ALTER TABLE `vtiger_faqcategories` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 1963 "ALTER TABLE `vtiger_faqstatus` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 1964 "ALTER TABLE `vtiger_field_seq` TYPE=MyISAM, COMMENT='', ROW_FORMAT=FIXED", 1965 "ALTER TABLE `vtiger_files` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 1966 "ALTER TABLE `vtiger_freetagged_objects` TYPE=InnoDB, COMMENT='', ROW_FORMAT=COMPACT", 1967 "ALTER TABLE `vtiger_group2grouprel` TYPE=MyISAM, COMMENT='', ROW_FORMAT=FIXED", 1968 "ALTER TABLE `vtiger_group2role` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 1969 "ALTER TABLE `vtiger_group2rs` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 1970 //"DROP TABLE `groups_seq`", 1971 "ALTER TABLE `vtiger_headers` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 1972 "ALTER TABLE `vtiger_import_maps` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 1973 "ALTER TABLE `vtiger_inventorynotification_seq` TYPE=MyISAM, COMMENT='', ROW_FORMAT=FIXED", 1974 "ALTER TABLE `vtiger_invoicegrouprelation` TYPE=InnoDB, COMMENT='', ROW_FORMAT=COMPACT", 1975 "ALTER TABLE `vtiger_loginhistory` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 1976 "ALTER TABLE `vtiger_mail_accounts` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 1977 "ALTER TABLE `vtiger_notificationscheduler_seq` TYPE=MyISAM, COMMENT='', ROW_FORMAT=FIXED", 1978 "ALTER TABLE `vtiger_ownernotify` TYPE=InnoDB, COMMENT='', ROW_FORMAT=COMPACT", 1979 "ALTER TABLE `vtiger_parenttabrel` TYPE=InnoDB, COMMENT='', ROW_FORMAT=COMPACT", 1980 "ALTER TABLE `vtiger_pogrouprelation` TYPE=InnoDB, COMMENT='', ROW_FORMAT=COMPACT", 1981 "ALTER TABLE `vtiger_portal` TYPE=InnoDB, COMMENT='', ROW_FORMAT=COMPACT", 1982 //"ALTER TABLE `vtiger_portalinfo` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 1983 "ALTER TABLE `vtiger_potentialgrouprelation` TYPE=InnoDB, COMMENT='', ROW_FORMAT=COMPACT", 1984 "ALTER TABLE `vtiger_profile2field` TYPE=MyISAM, COMMENT='', ROW_FORMAT=FIXED", 1985 "ALTER TABLE `vtiger_profile2globalpermissions` TYPE=InnoDB, COMMENT='', ROW_FORMAT=COMPACT", 1986 "ALTER TABLE `vtiger_profile2standardpermissions` TYPE=MyISAM, COMMENT='', ROW_FORMAT=FIXED", 1987 "ALTER TABLE `vtiger_profile2tab` TYPE=MyISAM, COMMENT='', ROW_FORMAT=FIXED", 1988 "ALTER TABLE `vtiger_profile2utility` TYPE=MyISAM, COMMENT='', ROW_FORMAT=FIXED", 1989 "ALTER TABLE `vtiger_profile_seq` TYPE=MyISAM, COMMENT='', ROW_FORMAT=FIXED", 1990 "ALTER TABLE `vtiger_quotegrouprelation` TYPE=InnoDB, COMMENT='', ROW_FORMAT=COMPACT", 1991 "ALTER TABLE `vtiger_rating` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 1992 "ALTER TABLE `vtiger_relatedlists` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 1993 "ALTER TABLE `vtiger_relatedlists_seq` TYPE=MyISAM, COMMENT='', ROW_FORMAT=FIXED", 1994 "ALTER TABLE `vtiger_role2profile` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 1995 "ALTER TABLE `vtiger_role_seq` TYPE=MyISAM, COMMENT='', ROW_FORMAT=FIXED", 1996 "ALTER TABLE `vtiger_rss` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 1997 "ALTER TABLE `vtiger_sales_stage` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 1998 "ALTER TABLE `vtiger_salutationtype` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 1999 "ALTER TABLE `vtiger_selectquery_seq` TYPE=MyISAM, COMMENT='', ROW_FORMAT=FIXED", 2000 "ALTER TABLE `vtiger_sogrouprelation` TYPE=InnoDB, COMMENT='', ROW_FORMAT=COMPACT", 2001 "ALTER TABLE `vtiger_systems` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 2002 "ALTER TABLE `vtiger_taskpriority` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 2003 "ALTER TABLE `vtiger_taskstatus` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 2004 "ALTER TABLE `vtiger_ticketcategories` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 2005 "ALTER TABLE `vtiger_ticketpriorities` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 2006 "ALTER TABLE `vtiger_ticketseverities` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 2007 "ALTER TABLE `vtiger_ticketstatus` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 2008 "ALTER TABLE `vtiger_ticketstracktime` TYPE=MyISAM, COMMENT='', ROW_FORMAT=FIXED", 2009 "ALTER TABLE `vtiger_tracker` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 2010 "ALTER TABLE `vtiger_users2group` TYPE=MyISAM, COMMENT='', ROW_FORMAT=FIXED", 2011 "ALTER TABLE `vtiger_users_last_import` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 2012 "ALTER TABLE `vtiger_users_seq` TYPE=MyISAM, COMMENT='', ROW_FORMAT=FIXED", 2013 //"ALTER TABLE `vtiger_wordtemplates` TYPE=MyISAM, COMMENT='', ROW_FORMAT=DYNAMIC", 2014 2015 "CREATE TABLE vtiger_actualcost ( 2016 `actualcostid` int(19) NOT NULL auto_increment, 2017 `actualcost` varchar(200) NOT NULL, 2018 `sortorderid` int(19) NOT NULL default '0', 2019 `presence` int(1) NOT NULL default '1', 2020 PRIMARY KEY (`actualcostid`), 2021 UNIQUE KEY `CampaignActCst_UK01` (`actualcost`) 2022 ) ENGINE=InnoDB DEFAULT CHARSET=latin1", 2023 2024 "CREATE TABLE vtiger_campaignstatus ( 2025 `campaignstatusid` int(19) NOT NULL auto_increment, 2026 `campaignstatus` varchar(200) NOT NULL, 2027 `sortorderid` int(19) NOT NULL default '0', 2028 `presence` int(1) NOT NULL default '1', 2029 PRIMARY KEY (`campaignstatusid`), 2030 KEY `Campaignstatus_UK01` (`campaignstatus`) 2031 ) ENGINE=InnoDB", 2032 2033 "CREATE TABLE vtiger_campaigntype ( 2034 `campaigntypeid` int(19) NOT NULL auto_increment, 2035 `campaigntype` varchar(200) NOT NULL, 2036 `sortorderid` int(19) NOT NULL default '0', 2037 `presence` int(1) NOT NULL default '1', 2038 PRIMARY KEY (`campaigntypeid`), 2039 UNIQUE KEY `Campaigntype_UK01` (`campaigntype`) 2040 ) ENGINE=InnoDB", 2041 2042 "CREATE TABLE vtiger_datashare_module_rel ( 2043 `shareid` int(19) NOT NULL, 2044 `tabid` int(19) NOT NULL, 2045 `relationtype` varchar(200) default NULL, 2046 PRIMARY KEY (`shareid`), 2047 KEY `idx_datashare_module_rel_tabid` (`tabid`) 2048 ) ENGINE=InnoDB", 2049 2050 //Added on 06-06-06 2051 "CREATE TABLE vtiger_datashare_grp2grp ( 2052 `shareid` int(19) NOT NULL, 2053 `share_groupid` int(19) default NULL, 2054 `to_groupid` int(19) default NULL, 2055 `permission` int(19) default NULL, 2056 PRIMARY KEY (`shareid`), 2057 KEY `datashare_grp2grp_share_groupid_idx` (`share_groupid`), 2058 KEY `datashare_grp2grp_to_groupid_idx` (`to_groupid`) 2059 ) ENGINE=InnoDB", 2060 /* 2061 "CREATE TABLE vtiger_datashare_grp2grp ( 2062 `shareid` int(19) NOT NULL, 2063 `share_groupid` int(19) default NULL, 2064 `to_groupid` int(19) default NULL, 2065 `permission` int(19) default NULL, 2066 PRIMARY KEY (`shareid`), 2067 KEY `idx_datashare_grp2grp_share_groupid` (`share_groupid`), 2068 KEY `idx_datashare_grp2grp_to_groupid` (`to_groupid`), 2069 CONSTRAINT `fk_datashare_grp2grp2` FOREIGN KEY (`to_groupid`) REFERENCES `vtiger_groups` (`groupid`) ON DELETE CASCADE, 2070 CONSTRAINT `fk_datashare_grp2grp1` FOREIGN KEY (`share_groupid`) REFERENCES `vtiger_groups` (`groupid`) ON DELETE CASCADE, 2071 CONSTRAINT `fk_datashare_grp2grp789` FOREIGN KEY (`shareid`) REFERENCES `vtiger_datashare_module_rel` (`shareid`) ON DELETE CASCADE 2072 ) ENGINE=InnoDB DEFAULT CHARSET=latin1", 2073 */ 2074 2075 "CREATE TABLE vtiger_datashare_grp2role ( 2076 `shareid` int(19) NOT NULL, 2077 `share_groupid` int(19) default NULL, 2078 `to_roleid` varchar(255) default NULL, 2079 `permission` int(19) default NULL, 2080 PRIMARY KEY (`shareid`), 2081 KEY `idx_datashare_grp2role_share_groupid` (`share_groupid`), 2082 KEY `idx_datashare_grp2role_to_roleid` (`to_roleid`) 2083 ) ENGINE=InnoDB", 2084 /* 2085 "CREATE TABLE vtiger_datashare_grp2role ( 2086 `shareid` int(19) NOT NULL, 2087 `share_groupid` int(19) default NULL, 2088 `to_roleid` varchar(255) default NULL, 2089 `permission` int(19) default NULL, 2090 PRIMARY KEY (`shareid`), 2091 KEY `idx_datashare_grp2role_share_groupid` (`share_groupid`), 2092 KEY `idx_datashare_grp2role_to_roleid` (`to_roleid`), 2093 CONSTRAINT `fk_datashare_grp2role2` FOREIGN KEY (`to_roleid`) REFERENCES `vtiger_role` (`roleid`) ON DELETE CASCADE, 2094 CONSTRAINT `fk_datashare_grp2role1` FOREIGN KEY (`share_groupid`) REFERENCES `vtiger_groups` (`groupid`) ON DELETE CASCADE, 2095 CONSTRAINT `fk_datashare_grp2role345` FOREIGN KEY (`shareid`) REFERENCES `vtiger_datashare_module_rel` (`shareid`) ON DELETE CASCADE 2096 ) ENGINE=InnoDB DEFAULT CHARSET=latin1", 2097 */ 2098 2099 //Added on 06-06-06 2100 "CREATE TABLE vtiger_datashare_grp2rs ( 2101 `shareid` int(19) NOT NULL, 2102 `share_groupid` int(19) default NULL, 2103 `to_roleandsubid` varchar(255) default NULL, 2104 `permission` int(19) default NULL, 2105 PRIMARY KEY (`shareid`), 2106 KEY `datashare_grp2rs_share_groupid_idx` (`share_groupid`), 2107 KEY `datashare_grp2rs_to_roleandsubid_idx` (`to_roleandsubid`) 2108 ) ENGINE=InnoDB", 2109 /* 2110 "CREATE TABLE vtiger_datashare_grp2rs ( 2111 `shareid` int(19) NOT NULL, 2112 `share_groupid` int(19) default NULL, 2113 `to_roleandsubid` varchar(255) default NULL, 2114 `permission` int(19) default NULL, 2115 PRIMARY KEY (`shareid`), 2116 KEY `idx_datashare_grp2rs_share_groupid` (`share_groupid`), 2117 KEY `idx_datashare_grp2rs_to_roleandsubid` (`to_roleandsubid`), 2118 CONSTRAINT `fk_datashare_grp2rs3` FOREIGN KEY (`to_roleandsubid`) REFERENCES `vtiger_role` (`roleid`) ON DELETE CASCADE, 2119 CONSTRAINT `fk_datashare_grp2rs1` FOREIGN KEY (`share_groupid`) REFERENCES `vtiger_groups` (`groupid`) ON DELETE CASCADE, 2120 CONSTRAINT `fk_datashare_grp2rs36` FOREIGN KEY (`shareid`) REFERENCES `vtiger_datashare_module_rel` (`shareid`) ON DELETE CASCADE 2121 ) ENGINE=InnoDB DEFAULT CHARSET=latin1", 2122 */ 2123 2124 "CREATE TABLE vtiger_datashare_relatedmodule_permission ( 2125 `shareid` int(19) NOT NULL, 2126 `datashare_relatedmodule_id` int(19) NOT NULL, 2127 `permission` int(19) default NULL, 2128 PRIMARY KEY (`shareid`,`datashare_relatedmodule_id`), 2129 KEY `datashare_relatedmodule_permission_UK1` (`shareid`,`permission`) 2130 ) ENGINE=InnoDB DEFAULT CHARSET=latin1", 2131 2132 //Added on 06-06-06 2133 "CREATE TABLE vtiger_datashare_relatedmodules ( 2134 `datashare_relatedmodule_id` int(19) NOT NULL, 2135 `tabid` int(19) default NULL, 2136 `relatedto_tabid` int(19) default NULL, 2137 PRIMARY KEY (`datashare_relatedmodule_id`), 2138 KEY `datashare_relatedmodules_tabid_idx` (`tabid`), 2139 KEY `datashare_relatedmodules_relatedto_tabid_idx` (`relatedto_tabid`) 2140 ) ENGINE=InnoDB", 2141 /* 2142 "CREATE TABLE vtiger_datashare_relatedmodules ( 2143 `datashare_relatedmodule_id` int(19) NOT NULL, 2144 `tabid` int(19) default NULL, 2145 `relatedto_tabid` int(19) default NULL, 2146 PRIMARY KEY (`datashare_relatedmodule_id`), 2147 KEY `idx_datashare_relatedmodules_tabid` (`tabid`), 2148 KEY `idx_datashare_relatedmodules_relatedto_tabid` (`relatedto_tabid`), 2149 CONSTRAINT `fk_datashare_relatedmodules1` FOREIGN KEY (`relatedto_tabid`) REFERENCES `vtiger_tab` (`tabid`) ON DELETE CASCADE, 2150 CONSTRAINT `fk_datashare_relatedmodules123` FOREIGN KEY (`tabid`) REFERENCES `vtiger_tab` (`tabid`) ON DELETE CASCADE 2151 ) ENGINE=InnoDB DEFAULT CHARSET=latin1", 2152 */ 2153 2154 "CREATE TABLE vtiger_datashare_relatedmodules_seq ( 2155 `id` int(11) NOT NULL 2156 ) ENGINE=MyISAM DEFAULT CHARSET=latin1", 2157 2158 //Added on 06-06-06 2159 "CREATE TABLE vtiger_datashare_role2group ( 2160 `shareid` int(19) NOT NULL, 2161 `share_roleid` varchar(255) default NULL, 2162 `to_groupid` int(19) default NULL, 2163 `permission` int(19) default NULL, 2164 PRIMARY KEY (`shareid`), 2165 KEY `idx_datashare_role2group_share_roleid` (`share_roleid`), 2166 KEY `idx_datashare_role2group_to_groupid` (`to_groupid`) 2167 ) ENGINE=InnoDB", 2168 /* 2169 "CREATE TABLE vtiger_datashare_role2group ( 2170 `shareid` int(19) NOT NULL, 2171 `share_roleid` varchar(255) default NULL, 2172 `to_groupid` int(19) default NULL, 2173 `permission` int(19) default NULL, 2174 PRIMARY KEY (`shareid`), 2175 KEY `idx_datashare_role2group_share_roleid` (`share_roleid`), 2176 KEY `idx_datashare_role2group_to_groupid` (`to_groupid`), 2177 CONSTRAINT `fk_datashare_role2group3` FOREIGN KEY (`to_groupid`) REFERENCES `vtiger_groups` (`groupid`) ON DELETE CASCADE, 2178 CONSTRAINT `fk_datashare_role2group1` FOREIGN KEY (`share_roleid`) REFERENCES `vtiger_role` (`roleid`) ON DELETE CASCADE, 2179 CONSTRAINT `fk_datashare_role2group568` FOREIGN KEY (`shareid`) REFERENCES `vtiger_datashare_module_rel` (`shareid`) ON DELETE CASCADE 2180 ) ENGINE=InnoDB DEFAULT CHARSET=latin1", 2181 */ 2182 2183 //Added on 06-06-06 2184 "CREATE TABLE vtiger_datashare_role2role ( 2185 `shareid` int(19) NOT NULL, 2186 `share_roleid` varchar(255) default NULL, 2187 `to_roleid` varchar(255) default NULL, 2188 `permission` int(19) default NULL, 2189 PRIMARY KEY (`shareid`), 2190 KEY `datashare_role2role_share_roleid_idx` (`share_roleid`), 2191 KEY `datashare_role2role_to_roleid_idx` (`to_roleid`) 2192 ) ENGINE=InnoDB", 2193 /* 2194 "CREATE TABLE vtiger_datashare_role2role ( 2195 `shareid` int(19) NOT NULL, 2196 `share_roleid` varchar(255) default NULL, 2197 `to_roleid` varchar(255) default NULL, 2198 `permission` int(19) default NULL, 2199 PRIMARY KEY (`shareid`), 2200 KEY `idx_datashare_role2role_share_roleid` (`share_roleid`), 2201 KEY `idx_datashare_role2role_to_roleid` (`to_roleid`), 2202 CONSTRAINT `fk_datashare_role2role3` FOREIGN KEY (`to_roleid`) REFERENCES `vtiger_role` (`roleid`) ON DELETE CASCADE, 2203 CONSTRAINT `fk_datashare_role2role1` FOREIGN KEY (`share_roleid`) REFERENCES `vtiger_role` (`roleid`) ON DELETE CASCADE, 2204 CONSTRAINT `fk_datashare_role2role345` FOREIGN KEY (`shareid`) REFERENCES `vtiger_datashare_module_rel` (`shareid`) ON DELETE CASCADE 2205 ) ENGINE=InnoDB DEFAULT CHARSET=latin1", 2206 */ 2207 2208 //Added on 06-06-06 2209 "CREATE TABLE vtiger_datashare_role2rs ( 2210 `shareid` int(19) NOT NULL, 2211 `share_roleid` varchar(255) default NULL, 2212 `to_roleandsubid` varchar(255) default NULL, 2213 `permission` int(19) default NULL, 2214 PRIMARY KEY (`shareid`), 2215 KEY `datashare_role2s_share_roleid_idx` (`share_roleid`), 2216 KEY `datashare_role2s_to_roleandsubid_idx` (`to_roleandsubid`) 2217 ) ENGINE=InnoDB", 2218 /* 2219 "CREATE TABLE vtiger_datashare_role2rs ( 2220 `shareid` int(19) NOT NULL, 2221 `share_roleid` varchar(255) default NULL, 2222 `to_roleandsubid` varchar(255) default NULL, 2223 `permission` int(19) default NULL, 2224 PRIMARY KEY (`shareid`), 2225 KEY `idx_datashare_role2s_share_roleid` (`share_roleid`), 2226 KEY `idx_datashare_role2s_to_roleandsubid` (`to_roleandsubid`), 2227 CONSTRAINT `fk_datashare_role2rs3` FOREIGN KEY (`to_roleandsubid`) REFERENCES `vtiger_role` (`roleid`) ON DELETE CASCADE, 2228 CONSTRAINT `fk_datashare_role2rs1` FOREIGN KEY (`share_roleid`) REFERENCES `vtiger_role` (`roleid`) ON DELETE CASCADE, 2229 CONSTRAINT `fk_datashare_role2rs987` FOREIGN KEY (`shareid`) REFERENCES `vtiger_datashare_module_rel` (`shareid`) ON DELETE CASCADE 2230 ) ENGINE=InnoDB DEFAULT CHARSET=latin1", 2231 */ 2232 2233 "CREATE TABLE vtiger_datashare_rs2grp ( 2234 `shareid` int(19) NOT NULL, 2235 `share_roleandsubid` varchar(255) default NULL, 2236 `to_groupid` int(19) default NULL, 2237 `permission` int(19) default NULL, 2238 PRIMARY KEY (`shareid`), 2239 KEY `datashare_rs2grp_share_roleandsubid_idx` (`share_roleandsubid`), 2240 KEY `datashare_rs2grp_to_groupid_idx` (`to_groupid`) 2241 ) ENGINE=InnoDB DEFAULT CHARSET=latin1", 2242 2243 "CREATE TABLE vtiger_datashare_rs2role ( 2244 `shareid` int(19) NOT NULL, 2245 `share_roleandsubid` varchar(255) default NULL, 2246 `to_roleid` varchar(255) default NULL, 2247 `permission` int(19) default NULL, 2248 PRIMARY KEY (`shareid`), 2249 KEY `datashare_rs2role_share_roleandsubid_idx` (`share_roleandsubid`), 2250 KEY `datashare_rs2role_to_roleid_idx` (`to_roleid`) 2251 ) ENGINE=InnoDB DEFAULT CHARSET=latin1", 2252 2253 "CREATE TABLE vtiger_datashare_rs2rs ( 2254 `shareid` int(19) NOT NULL, 2255 `share_roleandsubid` varchar(255) default NULL, 2256 `to_roleandsubid` varchar(255) default NULL, 2257 `permission` int(19) default NULL, 2258 PRIMARY KEY (`shareid`), 2259 KEY `datashare_rs2rs_share_roleandsubid_idx` (`share_roleandsubid`), 2260 KEY `idx_datashare_rs2rs_to_roleandsubid_idx` (`to_roleandsubid`) 2261 ) ENGINE=InnoDB DEFAULT CHARSET=latin1", 2262 2263 "CREATE TABLE vtiger_expectedresponse ( 2264 `expectedresponseid` int(19) NOT NULL auto_increment, 2265 `expectedresponse` varchar(200) NOT NULL, 2266 `sortorderid` int(19) NOT NULL default '0', 2267 `presence` int(1) NOT NULL default '1', 2268 PRIMARY KEY (`expectedresponseid`), 2269 UNIQUE KEY `CampaignExpRes_UK01` (`expectedresponse`) 2270 ) ENGINE=InnoDB", 2271 2272 "CREATE TABLE vtiger_expectedrevenue ( 2273 `expectedrevenueid` int(19) NOT NULL auto_increment, 2274 `expectedrevenue` varchar(200) NOT NULL, 2275 `sortorderid` int(19) NOT NULL default '0', 2276 `presence` int(1) NOT NULL default '1', 2277 PRIMARY KEY (`expectedrevenueid`), 2278 UNIQUE KEY `CampaignExpRev_UK01` (`expectedrevenue`) 2279 ) ENGINE=InnoDB DEFAULT CHARSET=latin1", 2280 2281 "CREATE TABLE vtiger_tmp_read_group_rel_sharing_per ( 2282 `userid` int(11) NOT NULL, 2283 `tabid` int(11) NOT NULL, 2284 `relatedtabid` int(11) NOT NULL, 2285 `sharedgroupid` int(11) NOT NULL, 2286 PRIMARY KEY (`userid`,`tabid`,`relatedtabid`,`sharedgroupid`), 2287 KEY `tmp_read_group_rel_sharing_per_userid_sharedgroupid_tabid` (`userid`,`sharedgroupid`,`tabid`) 2288 ) ENGINE=InnoDB DEFAULT CHARSET=latin1", 2289 2290 "CREATE TABLE vtiger_tmp_read_group_sharing_per ( 2291 `userid` int(11) NOT NULL, 2292 `tabid` int(11) NOT NULL, 2293 `sharedgroupid` int(11) NOT NULL, 2294 PRIMARY KEY (`userid`,`tabid`,`sharedgroupid`), 2295 KEY `tmp_read_group_sharing_per_userid_sharedgroupid_idx` (`userid`,`sharedgroupid`) 2296 ) ENGINE=InnoDB DEFAULT CHARSET=latin1", 2297 2298 "CREATE TABLE vtiger_tmp_read_user_rel_sharing_per ( 2299 `userid` int(11) NOT NULL, 2300 `tabid` int(11) NOT NULL, 2301 `relatedtabid` int(11) NOT NULL, 2302 `shareduserid` int(11) NOT NULL, 2303 PRIMARY KEY (`userid`,`tabid`,`relatedtabid`,`shareduserid`), 2304 KEY `tmp_read_user_rel_sharing_per_userid_shared_reltabid_idx` (`userid`,`shareduserid`,`relatedtabid`) 2305 ) ENGINE=InnoDB DEFAULT CHARSET=latin1", 2306 2307 "CREATE TABLE vtiger_tmp_read_user_sharing_per ( 2308 `userid` int(11) NOT NULL, 2309 `tabid` int(11) NOT NULL, 2310 `shareduserid` int(11) NOT NULL, 2311 PRIMARY KEY (`userid`,`tabid`,`shareduserid`), 2312 KEY `tmp_read_user_sharing_per_userid_shareduserid_idx` (`userid`,`shareduserid`) 2313 ) ENGINE=InnoDB DEFAULT CHARSET=latin1", 2314 2315 "CREATE TABLE vtiger_tmp_write_group_rel_sharing_per ( 2316 `userid` int(11) NOT NULL, 2317 `tabid` int(11) NOT NULL, 2318 `relatedtabid` int(11) NOT NULL, 2319 `sharedgroupid` int(11) NOT NULL, 2320 PRIMARY KEY (`userid`,`tabid`,`relatedtabid`,`sharedgroupid`), 2321 KEY `tmp_write_group_rel_sharing_per_userid_sharedgroupid_tabid_idx` (`userid`,`sharedgroupid`,`tabid`) 2322 ) ENGINE=InnoDB DEFAULT CHARSET=latin1", 2323 2324 "CREATE TABLE vtiger_tmp_write_group_sharing_per ( 2325 `userid` int(11) NOT NULL, 2326 `tabid` int(11) NOT NULL, 2327 `sharedgroupid` int(11) NOT NULL, 2328 PRIMARY KEY (`userid`,`tabid`,`sharedgroupid`), 2329 KEY `tmp_write_group_sharing_per_UK1` (`userid`,`sharedgroupid`) 2330 ) ENGINE=InnoDB DEFAULT CHARSET=latin1", 2331 2332 "CREATE TABLE vtiger_tmp_write_user_rel_sharing_per ( 2333 `userid` int(11) NOT NULL, 2334 `tabid` int(11) NOT NULL, 2335 `relatedtabid` int(11) NOT NULL, 2336 `shareduserid` int(11) NOT NULL, 2337 PRIMARY KEY (`userid`,`tabid`,`relatedtabid`,`shareduserid`), 2338 KEY `tmp_write_user_rel_sharing_per_userid_sharduserid_tabid_idx` (`userid`,`shareduserid`,`tabid`) 2339 ) ENGINE=InnoDB DEFAULT CHARSET=latin1", 2340 2341 "CREATE TABLE vtiger_tmp_write_user_sharing_per ( 2342 `userid` int(11) NOT NULL, 2343 `tabid` int(11) NOT NULL, 2344 `shareduserid` int(11) NOT NULL, 2345 PRIMARY KEY (`userid`,`tabid`,`shareduserid`), 2346 KEY `tmp_write_user_sharing_per_userid_shareduserid_idx` (`userid`,`shareduserid`) 2347 ) ENGINE=InnoDB DEFAULT CHARSET=latin1", 2348 2349 "ALTER TABLE `vtiger_account` MODIFY COLUMN `website` VARCHAR(100) COLLATE latin1_swedish_ci DEFAULT NULL", 2350 "ALTER TABLE `vtiger_account` MODIFY COLUMN `emailoptout` VARCHAR(3) COLLATE latin1_swedish_ci DEFAULT '0'", 2351 //"ALTER TABLE `vtiger_accountgrouprelation` MODIFY COLUMN `accountid` INTEGER(19) NOT NULL PRIMARY KEY", 2352 //"ALTER TABLE `vtiger_actionmapping` MODIFY COLUMN `actionid` INTEGER(19) NOT NULL PRIMARY KEY", 2353 //"ALTER TABLE `vtiger_activity` MODIFY COLUMN `date_start` DATE NOT NULL UNIQUE", 2354 "ALTER TABLE `vtiger_activity` MODIFY COLUMN `sendnotification` VARCHAR(3) COLLATE latin1_swedish_ci NOT NULL DEFAULT '0'", 2355 "ALTER TABLE `vtiger_activity` MODIFY COLUMN `duration_hours` VARCHAR(2) COLLATE latin1_swedish_ci DEFAULT NULL", 2356 "ALTER TABLE `vtiger_activity` MODIFY COLUMN `duration_minutes` VARCHAR(2) COLLATE latin1_swedish_ci DEFAULT NULL", 2357 "ALTER TABLE `vtiger_activity` MODIFY COLUMN `notime` VARCHAR(3) COLLATE latin1_swedish_ci NOT NULL DEFAULT '0'", 2358 //"ALTER TABLE `vtiger_activity_reminder` MODIFY COLUMN `activity_id` INTEGER(11) NOT NULL PRIMARY KEY", 2359 "ALTER TABLE `vtiger_activity_reminder` MODIFY COLUMN `reminder_time` INTEGER(11) NOT NULL", 2360 "ALTER TABLE `vtiger_activity_reminder` MODIFY COLUMN `reminder_sent` INTEGER(2) NOT NULL", 2361 //"ALTER TABLE `vtiger_activity_reminder` MODIFY COLUMN `recurringid` INTEGER(19) NOT NULL PRIMARY KEY", 2362 //"ALTER TABLE `vtiger_activitygrouprelation` MODIFY COLUMN `activityid` INTEGER(19) NOT NULL PRIMARY KEY", 2363 //"ALTER TABLE `vtiger_announcement` MODIFY COLUMN `creatorid` INTEGER(19) NOT NULL PRIMARY KEY", 2364 //"ALTER TABLE `vtiger_blocks` MODIFY COLUMN `blockid` INTEGER(19) NOT NULL PRIMARY KEY", 2365 //"ALTER TABLE `vtiger_blocks` MODIFY COLUMN `tabid` INTEGER(19) NOT NULL UNIQUE", 2366 "ALTER TABLE `vtiger_blocks` MODIFY COLUMN `sequence` INTEGER(10) DEFAULT NULL", 2367 "ALTER TABLE `vtiger_blocks` MODIFY COLUMN `show_title` INTEGER(2) DEFAULT NULL", 2368 "ALTER TABLE `vtiger_contactdetails` MODIFY COLUMN `donotcall` VARCHAR(3) COLLATE latin1_swedish_ci DEFAULT NULL", 2369 "ALTER TABLE `vtiger_contactdetails` MODIFY COLUMN `emailoptout` VARCHAR(3) COLLATE latin1_swedish_ci DEFAULT '0'", 2370 "ALTER TABLE `vtiger_contactdetails` MODIFY COLUMN `imagename` VARCHAR(150) COLLATE latin1_swedish_ci DEFAULT NULL", 2371 "ALTER TABLE `vtiger_contactdetails` MODIFY COLUMN `reference` VARCHAR(3) COLLATE latin1_swedish_ci DEFAULT NULL", 2372 //"ALTER TABLE `vtiger_contactgrouprelation` MODIFY COLUMN `contactid` INTEGER(19) NOT NULL PRIMARY KEY", 2373 //"ALTER TABLE `vtiger_convertleadmapping` MODIFY COLUMN `leadfid` INTEGER(19) NOT NULL", 2374 //"ALTER TABLE `vtiger_crmentity` MODIFY COLUMN `crmid` INTEGER(19) NOT NULL PRIMARY KEY", 2375 "ALTER TABLE `vtiger_crmentity` MODIFY COLUMN `description` TEXT COLLATE latin1_swedish_ci", 2376 "ALTER TABLE `vtiger_crmentity` MODIFY COLUMN `createdtime` DATETIME NOT NULL", 2377 "ALTER TABLE `vtiger_crmentity` MODIFY COLUMN `modifiedtime` DATETIME NOT NULL", 2378 "ALTER TABLE `vtiger_customaction` MODIFY COLUMN `cvid` INTEGER(19) NOT NULL UNIQUE", 2379 "ALTER TABLE `vtiger_customaction` MODIFY COLUMN `content` TEXT COLLATE latin1_swedish_ci", 2380 //"ALTER TABLE `vtiger_customerdetails` MODIFY COLUMN `customerid` INTEGER(19) NOT NULL PRIMARY KEY", 2381 "ALTER TABLE `vtiger_customerdetails` MODIFY COLUMN `portal` VARCHAR(3) COLLATE latin1_swedish_ci DEFAULT NULL", 2382 //"ALTER TABLE `vtiger_customview` MODIFY COLUMN `cvid` INTEGER(19) NOT NULL PRIMARY KEY", 2383 "ALTER TABLE `vtiger_customview_seq` MODIFY COLUMN `id` INTEGER(11) NOT NULL", 2384 //"ALTER TABLE `vtiger_cvadvfilter` MODIFY COLUMN `cvid` INTEGER(19) NOT NULL PRIMARY KEY", 2385 //"ALTER TABLE `vtiger_cvadvfilter` MODIFY COLUMN `columnindex` INTEGER(11) NOT NULL PRIMARY KEY", 2386 //"ALTER TABLE `vtiger_cvcolumnlist` MODIFY COLUMN `cvid` INTEGER(19) NOT NULL PRIMARY KEY", 2387 //"ALTER TABLE `vtiger_cvcolumnlist` MODIFY COLUMN `columnindex` INTEGER(11) NOT NULL PRIMARY KEY", 2388 //"ALTER TABLE `vtiger_cvstdfilter` MODIFY COLUMN `cvid` INTEGER(19) NOT NULL PRIMARY KEY", 2389 "ALTER TABLE `vtiger_dealintimation` MODIFY COLUMN `dealprobability` DECIMAL(3,2) NOT NULL DEFAULT '0.00'", 2390 //"ALTER TABLE `vtiger_def_org_field` MODIFY COLUMN `fieldid` INTEGER(19) NOT NULL PRIMARY KEY", 2391 "ALTER TABLE `vtiger_def_org_share` MODIFY COLUMN `tabid` INTEGER(11) NOT NULL", 2392 //"ALTER TABLE `vtiger_def_org_share` MODIFY COLUMN `permission` INTEGER(19) DEFAULT NULL UNIQUE", 2393 "ALTER TABLE `vtiger_def_org_share_seq` MODIFY COLUMN `id` INTEGER(11) NOT NULL", 2394 //"ALTER TABLE `vtiger_defaultcv` MODIFY COLUMN `tabid` INTEGER(19) NOT NULL PRIMARY KEY", 2395 "ALTER TABLE `vtiger_defaultcv` MODIFY COLUMN `query` TEXT COLLATE latin1_swedish_ci", 2396 "ALTER TABLE `vtiger_emailtemplates` MODIFY COLUMN `description` TEXT COLLATE latin1_swedish_ci", 2397 "ALTER TABLE `vtiger_emailtemplates` MODIFY COLUMN `body` TEXT COLLATE latin1_swedish_ci", 2398 //"ALTER TABLE `vtiger_emailtemplates_seq` MODIFY COLUMN `id` INTEGER(11) NOT NULL", 2399 "ALTER TABLE `vtiger_faq` MODIFY COLUMN `question` TEXT COLLATE latin1_swedish_ci", 2400 "ALTER TABLE `vtiger_faq` MODIFY COLUMN `answer` TEXT COLLATE latin1_swedish_ci", 2401 "ALTER TABLE `vtiger_faqcomments` MODIFY COLUMN `comments` TEXT COLLATE latin1_swedish_ci", 2402 "ALTER TABLE `vtiger_faqcomments` MODIFY COLUMN `createdtime` DATETIME NOT NULL", 2403 //"ALTER TABLE `vtiger_field` MODIFY COLUMN `tabid` INTEGER(19) NOT NULL UNIQUE", 2404 "ALTER TABLE `vtiger_field` MODIFY COLUMN `readonly` INTEGER(1) NOT NULL", 2405 "ALTER TABLE `vtiger_field` MODIFY COLUMN `selected` INTEGER(1) NOT NULL", 2406 //"ALTER TABLE `vtiger_field` MODIFY COLUMN `block` INTEGER(19) DEFAULT NULL UNIQUE", 2407 //"ALTER TABLE `vtiger_field` MODIFY COLUMN `displaytype` INTEGER(19) DEFAULT NULL UNIQUE", 2408 "ALTER TABLE `vtiger_field` MODIFY COLUMN `quickcreate` INTEGER(10) NOT NULL DEFAULT '1'", 2409 "ALTER TABLE `vtiger_field_seq` MODIFY COLUMN `id` INTEGER(11) NOT NULL", 2410 //"ALTER TABLE `vtiger_group2grouprel` MODIFY COLUMN `groupid` INTEGER(19) NOT NULL PRIMARY KEY", 2411 //"ALTER TABLE `vtiger_group2grouprel` MODIFY COLUMN `containsgroupid` INTEGER(19) NOT NULL PRIMARY KEY", 2412 //"ALTER TABLE `vtiger_group2role` MODIFY COLUMN `groupid` INTEGER(19) NOT NULL PRIMARY KEY", 2413 //"ALTER TABLE `vtiger_group2rs` MODIFY COLUMN `groupid` INTEGER(19) NOT NULL PRIMARY KEY", 2414 //"ALTER TABLE `vtiger_groups` MODIFY COLUMN `groupid` INTEGER(19) NOT NULL PRIMARY KEY", 2415 "ALTER TABLE `vtiger_groups` MODIFY COLUMN `description` TEXT COLLATE latin1_swedish_ci", 2416 "ALTER TABLE `vtiger_import_maps` MODIFY COLUMN `is_published` VARCHAR(3) COLLATE latin1_swedish_ci NOT NULL DEFAULT 'no'", 2417 //"ALTER TABLE `vtiger_inventory_tandc` MODIFY COLUMN `id` INTEGER(19) NOT NULL PRIMARY KEY", 2418 "ALTER TABLE `vtiger_inventory_tandc` MODIFY COLUMN `tandc` TEXT COLLATE latin1_swedish_ci", 2419 "ALTER TABLE `vtiger_inventory_tandc_seq` MODIFY COLUMN `id` INTEGER(11) NOT NULL", 2420 "ALTER TABLE `vtiger_inventorynotification` MODIFY COLUMN `notificationbody` TEXT COLLATE latin1_swedish_ci", 2421 "ALTER TABLE `vtiger_inventorynotification_seq` MODIFY COLUMN `id` INTEGER(11) NOT NULL", 2422 //"ALTER TABLE `vtiger_invoice` MODIFY COLUMN `salesorderid` INTEGER(19) DEFAULT NULL UNIQUE", 2423 "ALTER TABLE `vtiger_invoice` MODIFY COLUMN `terms_conditions` TEXT COLLATE latin1_swedish_ci", 2424 //"ALTER TABLE `vtiger_invoicegrouprelation` MODIFY COLUMN `invoiceid` INTEGER(19) NOT NULL PRIMARY KEY", 2425 "ALTER TABLE `vtiger_lar` MODIFY COLUMN `createdon` DATE NOT NULL", 2426 //"ALTER TABLE `vtiger_leaddetails` MODIFY COLUMN `leadid` INTEGER(19) NOT NULL PRIMARY KEY", 2427 "ALTER TABLE `vtiger_leaddetails` MODIFY COLUMN `comments` TEXT COLLATE latin1_swedish_ci", 2428 //"ALTER TABLE `vtiger_leadgrouprelation` MODIFY COLUMN `leadid` INTEGER(19) NOT NULL PRIMARY KEY", 2429 //"ALTER TABLE `vtiger_mail_accounts` MODIFY COLUMN `account_id` INTEGER(11) NOT NULL PRIMARY KEY", 2430 "ALTER TABLE `vtiger_mail_accounts` MODIFY COLUMN `user_id` INTEGER(11) NOT NULL", 2431 "ALTER TABLE `vtiger_mail_accounts` ADD COLUMN `box_refresh` INTEGER(10) DEFAULT NULL", 2432 "ALTER TABLE `vtiger_mail_accounts` ADD COLUMN `mails_per_page` INTEGER(10) DEFAULT NULL", 2433 "ALTER TABLE `vtiger_mail_accounts` ADD COLUMN `ssltype` VARCHAR(50) DEFAULT NULL", 2434 "ALTER TABLE `vtiger_mail_accounts` ADD COLUMN `sslmeth` VARCHAR(50) DEFAULT NULL", 2435 "ALTER TABLE `vtiger_mail_accounts` ADD COLUMN `showbody` VARCHAR(10) DEFAULT NULL", 2436 "ALTER TABLE `vtiger_notes` MODIFY COLUMN `contact_id` INTEGER(19) DEFAULT '0'", 2437 "ALTER TABLE `vtiger_notes` MODIFY COLUMN `notecontent` TEXT COLLATE latin1_swedish_ci", 2438 "ALTER TABLE `vtiger_notificationscheduler` MODIFY COLUMN `notificationbody` TEXT COLLATE latin1_swedish_ci", 2439 "ALTER TABLE `vtiger_notificationscheduler_seq` MODIFY COLUMN `id` INTEGER(11) NOT NULL", 2440 "ALTER TABLE `vtiger_opportunitystage` MODIFY COLUMN `probability` DECIMAL(3,2) DEFAULT '0.00'", 2441 //"ALTER TABLE `vtiger_org_share_action2tab` MODIFY COLUMN `share_action_id` INTEGER(19) NOT NULL PRIMARY KEY", 2442 //"ALTER TABLE `vtiger_org_share_action2tab` MODIFY COLUMN `tabid` INTEGER(19) NOT NULL PRIMARY KEY", 2443 //"ALTER TABLE `vtiger_org_share_action_mapping` MODIFY COLUMN `share_action_id` INTEGER(19) NOT NULL PRIMARY KEY", 2444 "ALTER TABLE `vtiger_organizationdetails` MODIFY COLUMN `website` VARCHAR(100) COLLATE latin1_swedish_ci DEFAULT NULL", 2445 "ALTER TABLE `vtiger_organizationdetails` MODIFY COLUMN `logo` TEXT COLLATE latin1_swedish_ci", 2446 "ALTER TABLE `vtiger_ownernotify` MODIFY COLUMN `crmid` INTEGER(19) DEFAULT NULL UNIQUE", 2447 //"ALTER TABLE `vtiger_parenttab` MODIFY COLUMN `parenttabid` INTEGER(19) NOT NULL PRIMARY KEY", 2448 "ALTER TABLE `vtiger_parenttab` MODIFY COLUMN `sequence` INTEGER(10) NOT NULL", 2449 "ALTER TABLE `vtiger_parenttabrel` MODIFY COLUMN `parenttabid` INTEGER(3) NOT NULL", 2450 //"ALTER TABLE `vtiger_parenttabrel` MODIFY COLUMN `tabid` INTEGER(3) NOT NULL UNIQUE", 2451 "ALTER TABLE `vtiger_parenttabrel` MODIFY COLUMN `sequence` INTEGER(3) NOT NULL", 2452 //"ALTER TABLE `vtiger_pogrouprelation` MODIFY COLUMN `purchaseorderid` INTEGER(19) NOT NULL PRIMARY KEY", 2453 //"ALTER TABLE `vtiger_portal` MODIFY COLUMN `portalid` INTEGER(19) NOT NULL PRIMARY KEY", 2454 "ALTER TABLE `vtiger_portal` MODIFY COLUMN `portalname` VARCHAR(200) COLLATE latin1_swedish_ci NOT NULL UNIQUE", 2455 "ALTER TABLE `vtiger_portal` MODIFY COLUMN `sequence` INTEGER(3) NOT NULL", 2456 //"ALTER TABLE `vtiger_portalinfo` MODIFY COLUMN `id` INTEGER(11) NOT NULL PRIMARY KEY", 2457 "ALTER TABLE `vtiger_portalinfo` MODIFY COLUMN `last_login_time` DATETIME NOT NULL", 2458 "ALTER TABLE `vtiger_portalinfo` MODIFY COLUMN `login_time` DATETIME NOT NULL", 2459 "ALTER TABLE `vtiger_portalinfo` MODIFY COLUMN `logout_time` DATETIME NOT NULL", 2460 //"ALTER TABLE `vtiger_potcompetitorrel` MODIFY COLUMN `potentialid` INTEGER(19) NOT NULL PRIMARY KEY", 2461 //"ALTER TABLE `vtiger_potcompetitorrel` MODIFY COLUMN `competitorid` INTEGER(19) NOT NULL PRIMARY KEY", 2462 "ALTER TABLE `vtiger_potential` MODIFY COLUMN `amount` DECIMAL(10,2) DEFAULT '0.00'", 2463 "ALTER TABLE `vtiger_potential` MODIFY COLUMN `description` TEXT COLLATE latin1_swedish_ci", 2464 //"ALTER TABLE `vtiger_potentialgrouprelation` MODIFY COLUMN `potentialid` INTEGER(19) NOT NULL PRIMARY KEY", 2465 "ALTER TABLE `vtiger_potstagehistory` MODIFY COLUMN `potentialid` INTEGER(19) NOT NULL", 2466 "ALTER TABLE `vtiger_potstagehistory` MODIFY COLUMN `probability` DECIMAL(7,3) DEFAULT NULL", 2467 "ALTER TABLE `vtiger_potstagehistory` MODIFY COLUMN `lastmodified` DATETIME default NULL", 2468 "ALTER TABLE `vtiger_pricebook` MODIFY COLUMN `description` TEXT COLLATE latin1_swedish_ci", 2469 //"ALTER TABLE `vtiger_pricebookproductrel` MODIFY COLUMN `pricebookid` INTEGER(19) NOT NULL PRIMARY KEY", 2470 //"ALTER TABLE `vtiger_pricebookproductrel` MODIFY COLUMN `productid` INTEGER(19) NOT NULL PRIMARY KEY", 2471 //"ALTER TABLE `vtiger_productcollaterals` MODIFY COLUMN `productid` INTEGER(11) NOT NULL PRIMARY KEY", 2472 "ALTER TABLE `vtiger_productcollaterals` MODIFY COLUMN `description` TEXT COLLATE latin1_swedish_ci", 2473 //"ALTER TABLE `vtiger_products` MODIFY COLUMN `productid` INTEGER(11) NOT NULL PRIMARY KEY", 2474 "ALTER TABLE `vtiger_products` MODIFY COLUMN `product_description` TEXT COLLATE latin1_swedish_ci", 2475 "ALTER TABLE `vtiger_products` MODIFY COLUMN `commissionrate` DECIMAL(3,3) DEFAULT NULL", 2476 //"ALTER TABLE `vtiger_profile2field` MODIFY COLUMN `profileid` INTEGER(11) NOT NULL PRIMARY KEY", 2477 //"ALTER TABLE `vtiger_profile2field` MODIFY COLUMN `fieldid` INTEGER(19) NOT NULL PRIMARY KEY", 2478 //"ALTER TABLE `vtiger_profile2globalpermissions` MODIFY COLUMN `profileid` INTEGER(19) NOT NULL PRIMARY KEY", 2479 //"ALTER TABLE `vtiger_profile2globalpermissions` MODIFY COLUMN `globalactionid` INTEGER(19) NOT NULL PRIMARY KEY", 2480 //"ALTER TABLE `vtiger_profile2standardpermissions` MODIFY COLUMN `profileid` INTEGER(11) NOT NULL PRIMARY KEY", 2481 //"ALTER TABLE `vtiger_profile2standardpermissions` MODIFY COLUMN `tabid` INTEGER(10) NOT NULL PRIMARY KEY", 2482 //"ALTER TABLE `vtiger_profile2standardpermissions` MODIFY COLUMN `Operation` INTEGER(10) NOT NULL PRIMARY KEY", 2483 //"ALTER TABLE `vtiger_profile2utility` MODIFY COLUMN `profileid` INTEGER(11) NOT NULL PRIMARY KEY", 2484 //"ALTER TABLE `vtiger_profile2utility` MODIFY COLUMN `tabid` INTEGER(11) NOT NULL PRIMARY KEY", 2485 //"ALTER TABLE `vtiger_profile2utility` MODIFY COLUMN `activityid` INTEGER(11) NOT NULL PRIMARY KEY", 2486 "ALTER TABLE `vtiger_profile_seq` MODIFY COLUMN `id` INTEGER(11) NOT NULL", 2487 //"ALTER TABLE `vtiger_purchaseorder` MODIFY COLUMN `quoteid` INTEGER(19) DEFAULT NULL UNIQUE", 2488 //"ALTER TABLE `vtiger_purchaseorder` MODIFY COLUMN `vendorid` INTEGER(19) DEFAULT NULL UNIQUE", 2489 //"ALTER TABLE `vtiger_purchaseorder` MODIFY COLUMN `contactid` INTEGER(19) DEFAULT NULL UNIQUE", 2490 "ALTER TABLE `vtiger_purchaseorder` MODIFY COLUMN `terms_conditions` TEXT COLLATE latin1_swedish_ci", 2491 //"ALTER TABLE `vtiger_quotegrouprelation` MODIFY COLUMN `quoteid` INTEGER(19) NOT NULL PRIMARY KEY", 2492 //"ALTER TABLE `vtiger_quotes` MODIFY COLUMN `potentialid` INTEGER(19) DEFAULT NULL UNIQUE", 2493 "ALTER TABLE `vtiger_quotes` MODIFY COLUMN `quotestage` VARCHAR(200) COLLATE latin1_swedish_ci DEFAULT NULL", 2494 //"ALTER TABLE `vtiger_quotes` MODIFY COLUMN `contactid` INTEGER(19) DEFAULT NULL UNIQUE", 2495 "ALTER TABLE `vtiger_quotes` MODIFY COLUMN `terms_conditions` TEXT COLLATE latin1_swedish_ci", 2496 "ALTER TABLE `vtiger_recurringevents` MODIFY COLUMN `activityid` INTEGER(19) NOT NULL", 2497 //"ALTER TABLE `vtiger_relatedlists` MODIFY COLUMN `relation_id` INTEGER(19) NOT NULL PRIMARY KEY", 2498 "ALTER TABLE `vtiger_relatedlists_seq` MODIFY COLUMN `id` INTEGER(11) NOT NULL", 2499 //"ALTER TABLE `vtiger_relcriteria` MODIFY COLUMN `queryid` INTEGER(19) NOT NULL PRIMARY KEY", 2500 //"ALTER TABLE `vtiger_relcriteria` MODIFY COLUMN `columnindex` INTEGER(11) NOT NULL PRIMARY KEY", 2501 //"ALTER TABLE `vtiger_report` MODIFY COLUMN `reportid` INTEGER(19) NOT NULL PRIMARY KEY", 2502 //"ALTER TABLE `vtiger_report` MODIFY COLUMN `folderid` INTEGER(19) NOT NULL UNIQUE", 2503 //"ALTER TABLE `vtiger_reportdatefilter` MODIFY COLUMN `datefilterid` INTEGER(19) NOT NULL PRIMARY KEY", 2504 //"ALTER TABLE `vtiger_reportmodules` MODIFY COLUMN `reportmodulesid` INTEGER(19) NOT NULL PRIMARY KEY", 2505 //"ALTER TABLE `vtiger_reportsortcol` MODIFY COLUMN `sortcolid` INTEGER(19) NOT NULL PRIMARY KEY", 2506 //"ALTER TABLE `vtiger_reportsortcol` MODIFY COLUMN `reportid` INTEGER(19) NOT NULL PRIMARY KEY", 2507 //"ALTER TABLE `vtiger_reportsummary` MODIFY COLUMN `reportsummaryid` INTEGER(19) NOT NULL PRIMARY KEY", 2508 //"ALTER TABLE `vtiger_reportsummary` MODIFY COLUMN `summarytype` INTEGER(19) NOT NULL PRIMARY KEY", 2509 //"ALTER TABLE `vtiger_role2profile` MODIFY COLUMN `profileid` INTEGER(11) NOT NULL PRIMARY KEY", 2510 "ALTER TABLE `vtiger_role_seq` MODIFY COLUMN `id` INTEGER(11) NOT NULL", 2511 //"ALTER TABLE `vtiger_rss` MODIFY COLUMN `rssid` INTEGER(19) NOT NULL PRIMARY KEY", 2512 //"ALTER TABLE `vtiger_salesorder` MODIFY COLUMN `contactid` INTEGER(19) DEFAULT NULL UNIQUE", 2513 "ALTER TABLE `vtiger_salesorder` MODIFY COLUMN `vendorid` INTEGER(19) DEFAULT NULL UNIQUE", 2514 "ALTER TABLE `vtiger_salesorder` MODIFY COLUMN `terms_conditions` TEXT COLLATE latin1_swedish_ci", 2515 //"ALTER TABLE `vtiger_seactivityrel` MODIFY COLUMN `crmid` INTEGER(19) NOT NULL PRIMARY KEY", 2516 //"ALTER TABLE `vtiger_seactivityrel` MODIFY COLUMN `activityid` INTEGER(19) NOT NULL PRIMARY KEY", 2517 //"ALTER TABLE `vtiger_selectcolumn` MODIFY COLUMN `queryid` INTEGER(19) NOT NULL PRIMARY KEY", 2518 //"ALTER TABLE `vtiger_selectquery` MODIFY COLUMN `queryid` INTEGER(19) NOT NULL PRIMARY KEY", 2519 "ALTER TABLE `vtiger_selectquery_seq` MODIFY COLUMN `id` INTEGER(11) NOT NULL", 2520 //"ALTER TABLE `vtiger_sharedcalendar` MODIFY COLUMN `userid` INTEGER(19) NOT NULL PRIMARY KEY", 2521 //"ALTER TABLE `vtiger_sharedcalendar` MODIFY COLUMN `sharedid` INTEGER(19) NOT NULL PRIMARY KEY", 2522 //"ALTER TABLE `vtiger_sogrouprelation` MODIFY COLUMN `salesorderid` INTEGER(19) NOT NULL PRIMARY KEY", 2523 //"ALTER TABLE `vtiger_systems` MODIFY COLUMN `id` INTEGER(19) NOT NULL PRIMARY KEY", 2524 "ALTER TABLE `vtiger_systems` MODIFY COLUMN `server` VARCHAR(30) COLLATE latin1_swedish_ci DEFAULT NULL", 2525 "ALTER TABLE `vtiger_systems` MODIFY COLUMN `server_username` VARCHAR(30) COLLATE latin1_swedish_ci DEFAULT NULL", 2526 "ALTER TABLE `vtiger_systems` MODIFY COLUMN `server_password` VARCHAR(30) COLLATE latin1_swedish_ci DEFAULT NULL", 2527 "ALTER TABLE `vtiger_ticketcomments` MODIFY COLUMN `comments` TEXT COLLATE latin1_swedish_ci", 2528 "ALTER TABLE `vtiger_ticketcomments` MODIFY COLUMN `createdtime` DATETIME NOT NULL", 2529 //"ALTER TABLE `vtiger_ticketgrouprelation` MODIFY COLUMN `ticketid` INTEGER(19) NOT NULL PRIMARY KEY", 2530 //"ALTER TABLE `vtiger_troubletickets` MODIFY COLUMN `ticketid` INTEGER(19) NOT NULL PRIMARY KEY", 2531 "ALTER TABLE `vtiger_troubletickets` MODIFY COLUMN `description` TEXT COLLATE latin1_swedish_ci", 2532 "ALTER TABLE `vtiger_troubletickets` MODIFY COLUMN `solution` TEXT COLLATE latin1_swedish_ci", 2533 "ALTER TABLE `vtiger_troubletickets` MODIFY COLUMN `update_log` TEXT COLLATE latin1_swedish_ci", 2534 //"ALTER TABLE `vtiger_user2role` MODIFY COLUMN `userid` INTEGER(11) NOT NULL PRIMARY KEY", 2535 //"ALTER TABLE `vtiger_user2role` MODIFY COLUMN `roleid` VARCHAR(255) COLLATE latin1_swedish_ci NOT NULL UNIQUE", 2536 "ALTER TABLE `vtiger_users` MODIFY COLUMN `is_admin` VARCHAR(3) COLLATE latin1_swedish_ci DEFAULT '0'", 2537 "ALTER TABLE `vtiger_users` MODIFY COLUMN `description` TEXT COLLATE latin1_swedish_ci", 2538 "ALTER TABLE `vtiger_users` MODIFY COLUMN `user_preferences` TEXT COLLATE latin1_swedish_ci", 2539 "ALTER TABLE `vtiger_users` MODIFY COLUMN `homeorder` VARCHAR(255) COLLATE latin1_swedish_ci DEFAULT 'ALVT,PLVT,QLTQ,CVLVT,HLT,OLV,GRT,OLTSO,ILTI,MNL'", 2540 "ALTER TABLE `vtiger_users` ADD COLUMN `currency_id` INTEGER(19) NOT NULL DEFAULT '1'", 2541 "ALTER TABLE `vtiger_users` ADD COLUMN `defhomeview` VARCHAR(100) COLLATE latin1_swedish_ci DEFAULT 'home_metrics'", 2542 //"ALTER TABLE `vtiger_users2group` MODIFY COLUMN `groupid` INTEGER(19) NOT NULL PRIMARY KEY", 2543 //"ALTER TABLE `vtiger_users2group` MODIFY COLUMN `userid` INTEGER(19) NOT NULL PRIMARY KEY", 2544 "ALTER TABLE `vtiger_users_seq` MODIFY COLUMN `id` INTEGER(11) NOT NULL", 2545 "ALTER TABLE `vtiger_vendor` MODIFY COLUMN `street` TEXT COLLATE latin1_swedish_ci", 2546 "ALTER TABLE `vtiger_vendor` MODIFY COLUMN `description` TEXT COLLATE latin1_swedish_ci", 2547 //"ALTER TABLE `vtiger_wordtemplates` MODIFY COLUMN `templateid` INTEGER(19) NOT NULL PRIMARY KEY", 2548 //"ALTER TABLE `vtiger_wordtemplates` MODIFY COLUMN `description` TEXT COLLATE latin1_swedish_ci", 2549 "ALTER TABLE `vtiger_accountgrouprelation` ADD KEY `accountgrouprelation_IDX1` (`groupname`)", 2550 "ALTER TABLE `vtiger_activity` ADD KEY `status1` (`status`, `eventstatus`)", 2551 "ALTER TABLE `vtiger_attachments` ADD KEY `attachmentsid1` (`attachmentsid`)", 2552 "ALTER TABLE `vtiger_blocks` ADD KEY `block_tabid` (`tabid`)", 2553 "ALTER TABLE `vtiger_carrier` ADD UNIQUE KEY `carrier_UK01` (`carrier`)", 2554 "ALTER TABLE `vtiger_contactgrouprelation` ADD KEY `contactgrouprelation_IDX1` (`groupname`)", 2555 "ALTER TABLE `vtiger_def_org_field` ADD KEY `tabid4` (`tabid`)", 2556 "ALTER TABLE `vtiger_def_org_share` ADD KEY `fk_def_org_share23` (`permission`)", 2557 "ALTER TABLE `vtiger_field` ADD KEY `tabid2` (`tabid`)", 2558 "ALTER TABLE `vtiger_field` ADD KEY `blockid` (`block`)", 2559 "ALTER TABLE `vtiger_field` ADD KEY `displaytypeid` (`displaytype`)", 2560 "ALTER TABLE `vtiger_freetagged_objects` DROP INDEX tag_id_index", 2561 "ALTER TABLE `vtiger_freetagged_objects` ADD INDEX `tag_id_index` (`tag_id`, `tagger_id`, `object_id`)", 2562 "ALTER TABLE `vtiger_group2grouprel` ADD KEY `fk_group2grouprel2` (`containsgroupid`)", 2563 "ALTER TABLE `vtiger_group2role` ADD KEY `fk_group2role2` (`roleid`)", 2564 "ALTER TABLE `vtiger_group2rs` ADD KEY `fk_group2rs2` (`roleandsubid`)", 2565 "ALTER TABLE `vtiger_groups` ADD KEY `idx_groups_123group` (`groupname`)", 2566 "ALTER TABLE `vtiger_invoice` ADD KEY `SoPo_IDX` (`invoiceid`)", 2567 "ALTER TABLE `vtiger_invoice` ADD KEY `fk_Invoice2` (`salesorderid`)", 2568 "ALTER TABLE `vtiger_invoicegrouprelation` ADD KEY `invoicegrouprelation_IDX1` (`groupname`, `invoiceid`)", 2569 "ALTER TABLE `vtiger_leadgrouprelation` ADD KEY `leadgrouprelation_IDX0` (`leadid`)", 2570 "ALTER TABLE `vtiger_moduleowners` ADD KEY `moduleowners_UK11` (`tabid`, `user_id`)", 2571 //"ALTER TABLE `vtiger_org_share_action2tab` ADD KEY `fk_org_share_action2tab12345` (`tabid`)", 2572 "ALTER TABLE `vtiger_ownernotify` ADD KEY `ownernotify_UK1` (`crmid`, `flag`)", 2573 "ALTER TABLE `vtiger_parenttab` ADD KEY `parenttab_UK1` (`parenttabid`, `parenttab_label`, `visible`)", 2574 "ALTER TABLE `vtiger_parenttabrel` ADD KEY `parenttabrelUK01` (`tabid`, `parenttabid`)", 2575 "ALTER TABLE `vtiger_pogrouprelation` ADD KEY `pogrouprelation_IDX1` (`groupname`, `purchaseorderid`)", 2576 "ALTER TABLE `vtiger_portal` ADD KEY `portal_UK01` (`portalname`)", 2577 "ALTER TABLE `vtiger_potential` ADD KEY `potentialid1` (`potentialid`)", 2578 "ALTER TABLE `vtiger_potentialgrouprelation` ADD KEY `potentialgrouprelation_IDX1` (`groupname`)", 2579 "ALTER TABLE `vtiger_potstagehistory` DROP INDEX PotStageHistory_IDX1", 2580 "ALTER TABLE `vtiger_potstagehistory` ADD INDEX `PotStageHistory_IDX1` (`historyid`)", 2581 "ALTER TABLE `vtiger_potstagehistory` ADD KEY `fk_PotStageHistory` (`potentialid`)", 2582 "ALTER TABLE `vtiger_profile2field` ADD KEY `tabid3` (`tabid`, `profileid`)", 2583 //"ALTER TABLE `vtiger_profile2globalpermissions` ADD KEY `idx_profile2globalpermissions` (`profileid`, `globalactionid`)", 2584 "ALTER TABLE `vtiger_profile2standardpermissions` ADD KEY `idx_prof2stad` (`profileid`, `tabid`, `Operation`)", 2585 "ALTER TABLE `vtiger_profile2tab` ADD KEY `idx_profile2tab1` (`profileid`, `tabid`)", 2586 "ALTER TABLE `vtiger_profile2utility` ADD KEY `idx_prof2utility` (`profileid`, `tabid`, `activityid`)", 2587 "ALTER TABLE `vtiger_purchaseorder` ADD KEY `PO_Vend_IDX` (`vendorid`)", 2588 "ALTER TABLE `vtiger_purchaseorder` ADD KEY `PO_Quote_IDX` (`quoteid`)", 2589 "ALTER TABLE `vtiger_purchaseorder` ADD KEY `PO_Contact_IDX` (`contactid`)", 2590 "ALTER TABLE `vtiger_quotegrouprelation` ADD KEY `quotegrouprelation_IDX1` (`groupname`)", 2591 //"ALTER TABLE `vtiger_quotes` DROP INDEX vtiger_quotestage", 2592 "ALTER TABLE `vtiger_quotes` ADD INDEX `quotestage` (`quoteid`)", 2593 "ALTER TABLE `vtiger_quotes` ADD KEY `potentialid2` (`potentialid`)", 2594 "ALTER TABLE `vtiger_quotes` ADD KEY `contactid` (`contactid`)", 2595 "ALTER TABLE `vtiger_recurringtype` ADD UNIQUE KEY `RecurringEvent_UK0` (`recurringtype`)", 2596 "ALTER TABLE `vtiger_reportsortcol` ADD KEY `FK1_reportsortcol` (`reportid`)", 2597 "ALTER TABLE `vtiger_role2profile` ADD KEY `idx_role2profileid1` (`roleid`, `profileid`)", 2598 "ALTER TABLE `vtiger_salesorder` ADD KEY `SoVend_IDX` (`vendorid`)", 2599 "ALTER TABLE `vtiger_salesorder` ADD KEY `SoContact_IDX` (`contactid`)", 2600 "ALTER TABLE `vtiger_seattachmentsrel` ADD KEY `attachmentsid2` (`attachmentsid`, `crmid`)", 2601 "ALTER TABLE `vtiger_selectquery` ADD KEY `selectquery_IDX0` (`queryid`)", 2602 "ALTER TABLE `vtiger_sogrouprelation` ADD KEY `sogrouprelation_IDX1` (`groupname`)", 2603 "ALTER TABLE `vtiger_tab` ADD KEY `tabid1` (`tabid`)", 2604 "ALTER TABLE `vtiger_taxclass` ADD UNIQUE KEY `carrier_UK02` (`taxclass`)", 2605 "ALTER TABLE `vtiger_troubletickets` ADD KEY `status2` (`status`)", 2606 "ALTER TABLE `vtiger_users2group` ADD KEY `idx_users2group` (`groupid`, `userid`)", 2607 "ALTER TABLE `vtiger_users2group` ADD KEY `fk_users2group2` (`userid`)", 2608 "ALTER TABLE `vtiger_customaction` ADD CONSTRAINT `customaction_FK1` FOREIGN KEY (`cvid`) REFERENCES `vtiger_customview` (`cvid`) ON DELETE CASCADE", 2609 "ALTER TABLE `vtiger_profile2globalpermissions` ADD CONSTRAINT `fk_profile2globalpermissions57` FOREIGN KEY (`profileid`) REFERENCES `vtiger_profile` (`profileid`) ON DELETE CASCADE", 2610 "ALTER TABLE `vtiger_invoice` ADD CONSTRAINT `fk_Invoice2` FOREIGN KEY (`salesorderid`) REFERENCES `vtiger_salesorder` (`salesorderid`) ON DELETE CASCADE", 2611 "ALTER TABLE `vtiger_purchaseorder` ADD CONSTRAINT `fk_PO3` FOREIGN KEY (`contactid`) REFERENCES `vtiger_contactdetails` (`contactid`) ON DELETE CASCADE", 2612 "ALTER TABLE `vtiger_purchaseorder` ADD CONSTRAINT `fk_PO2` FOREIGN KEY (`vendorid`) REFERENCES `vtiger_vendor` (`vendorid`) ON DELETE CASCADE", 2613 "ALTER TABLE `vtiger_purchaseorder` ADD CONSTRAINT `fk_PO2345` FOREIGN KEY (`quoteid`) REFERENCES `vtiger_quotes` (`quoteid`) ON DELETE CASCADE", 2614 "ALTER TABLE `vtiger_quotes` ADD CONSTRAINT `fk_Quotes3` FOREIGN KEY (`contactid`) REFERENCES `vtiger_contactdetails` (`contactid`) ON DELETE CASCADE", 2615 "ALTER TABLE `vtiger_quotes` ADD CONSTRAINT `fk_Quotes2` FOREIGN KEY (`potentialid`) REFERENCES `vtiger_potential` (`potentialid`) ON DELETE CASCADE", 2616 "ALTER TABLE `vtiger_salesorder` ADD CONSTRAINT `fk_SO4` FOREIGN KEY (`contactid`) REFERENCES `vtiger_contactdetails` (`contactid`) ON DELETE CASCADE", 2617 "ALTER TABLE `vtiger_salesorder` ADD CONSTRAINT `fk_SO2` FOREIGN KEY (`vendorid`) REFERENCES `vtiger_vendor` (`vendorid`) ON DELETE CASCADE", 2618 "ALTER TABLE `vtiger_vendorcontactrel` ADD CONSTRAINT `fk_VendorContactRel45` FOREIGN KEY (`vendorid`) REFERENCES `vtiger_vendor` (`vendorid`) ON DELETE CASCADE" 2619 ); 2620 foreach($query_array as $query) 2621 { 2622 Execute($query); 2623 } 2624 2625 //First check whether this table is exist and the proceed 2626 $currency_columns = $conn->getColumnNames("vtiger_currency_info"); 2627 if(!is_array($currency_columns)) 2628 { 2629 $currency_query = "CREATE TABLE `vtiger_currency_info` ( 2630 `id` int(11) NOT NULL auto_increment, 2631 `currency_name` varchar(100) default NULL, 2632 `currency_code` varchar(100) default NULL, 2633 `currency_symbol` varchar(30) default NULL, 2634 `conversion_rate` decimal(10,3) default NULL, 2635 `currency_status` varchar(25) default NULL, 2636 `defaultid` varchar(10) NOT NULL default '0', 2637 PRIMARY KEY (`id`) 2638 ) ENGINE=InnoDB"; 2639 Execute($currency_query); 2640 } 2641 elseif(!in_array("id",$currency_columns)) 2642 { 2643 $currency_query_array = Array( 2644 "alter table vtiger_currency_info drop primary key", 2645 "alter table vtiger_currency_info add column id int(11) NOT NULL auto_increment primary key FIRST", 2646 "alter table vtiger_currency_info add column conversion_rate decimal(10,3) default NULL", 2647 "alter table vtiger_currency_info add column currency_status varchar(25) default NULL", 2648 "alter table vtiger_currency_info add column defaultid varchar(10) NOT NULL default '0'", 2649 ); 2650 foreach($currency_query_array as $query) 2651 { 2652 Execute($query); 2653 } 2654 } 2655 2656 $migrationlog->debug("Database Modifications for 5.0(Alpha) Dev 3 ==> 5.0 Alpha (5) ends here."); 2657 2658 2659 /************************* The following changes have been made after 5.0 Alpha 5 *************************/ 2660 $migrationlog->debug("Database Modifications after 5.0(Alpha 5) starts here."); 2661 2662 2663 //Added on 22-04-06 - to add the Notify Owner vtiger_field in Contacts and Accounts 2664 $notify_owner_array = Array( 2665 "update vtiger_field set sequence=26 where tabid=4 and fieldname='modifiedtime'", 2666 "update vtiger_field set sequence=25 where tabid=4 and fieldname='createdtime'", 2667 2668 "insert into vtiger_field values(4,".$conn->getUniqueID("vtiger_field").",'notify_owner','contactdetails',1,56,'notify_owner','Notify Owner',1,0,0,10,24,4,1,'C~O',1,NULL,'ADV')", 2669 "alter table vtiger_contactdetails add column notify_owner varchar(3) default 0 after reference", 2670 2671 "update vtiger_field set sequence=21 where tabid=6 and fieldname='modifiedtime'", 2672 "update vtiger_field set sequence=20 where tabid=6 and fieldname='createdtime'", 2673 "update vtiger_field set sequence=19 where tabid=6 and fieldname='assigned_user_id'", 2674 2675 "insert into vtiger_field values(6,".$conn->getUniqueID("vtiger_field").",'notify_owner','account',1,56,'notify_owner','Notify Owner',1,0,0,10,18,9,1,'C~O',1,NULL,'ADV')", 2676 "alter table vtiger_account add column notify_owner varchar(3) default 0 after emailoptout" 2677 ); 2678 foreach($notify_owner_array as $query) 2679 { 2680 Execute($query); 2681 } 2682 2683 //Added for RSS entries 2684 $rss_insert_query = "insert into vtiger_field values (24,".$conn->getUniqueID("vtiger_field").",'rsscategory','rss',1,'15','rsscategory','rsscategory',1,0,0,255,13,null,1,'V~O',1,null,'BAS')"; 2685 Execute($rss_insert_query); 2686 2687 //Quick Create Feature added for Vendor & PriceBook 2688 $quickcreate_query = Array( 2689 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 18 and fieldname = 'vendorname'", 2690 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 2 WHERE tabid = 18 and fieldname = 'phone'", 2691 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 3 WHERE tabid = 18 and fieldname = 'email'", 2692 2693 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 19 and fieldname = 'bookname'", 2694 "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 2 WHERE tabid = 19 and fieldname = 'active'" 2695 ); 2696 foreach($quickcreate_query as $query) 2697 { 2698 Execute($query); 2699 } 2700 2701 2702 //Added on 24-04-06 to populate vtiger_customview All for Campaign and webmails modules 2703 $cvid1 = $conn->getUniqueID("vtiger_customview"); 2704 $cvid2 = $conn->getUniqueID("vtiger_customview"); 2705 $customview_query_array = Array( 2706 "insert into vtiger_customview(cvid,viewname,setdefault,setmetrics,entitytype) values(".$cvid1.",'All',1,0,'Campaigns')", 2707 "insert into vtiger_cvcolumnlist (cvid,columnindex,columnname) values (".$cvid1.",0,'vtiger_campaign:campaignname:campaignname:Campaigns_Campaign_Name:V')", 2708 "insert into vtiger_cvcolumnlist (cvid,columnindex,columnname) values (".$cvid1.",1,'vtiger_campaign:campaigntype:campaigntype:Campaigns_Campaign_Type:N')", 2709 "insert into vtiger_cvcolumnlist (cvid,columnindex,columnname) values (".$cvid1.",2,'vtiger_campaign:campaignstatus:campaignstatus:Campaigns_Campaign_Status:N')", 2710 "insert into vtiger_cvcolumnlist (cvid,columnindex,columnname) values (".$cvid1.",3,'vtiger_campaign:expectedrevenue:expectedrevenue:Campaigns_Expected_Revenue:V')", 2711 "insert into vtiger_cvcolumnlist (cvid,columnindex,columnname) values (".$cvid1.",4,'vtiger_campaign:closingdate:closingdate:Campaigns_Expected_Close_Date:D')", 2712 "insert into vtiger_cvcolumnlist (cvid,columnindex,columnname) values (".$cvid1.",5,'vtiger_crmentity:smownerid:assigned_user_id:Campaigns_Assigned_To:V')", 2713 2714 2715 "insert into vtiger_customview(cvid,viewname,setdefault,setmetrics,entitytype) values(".$cvid2.",'All',1,0,'Webmails')", 2716 "insert into vtiger_cvcolumnlist (cvid,columnindex,columnname) values (".$cvid2.",0,'subject:subject:subject:Subject:V')", 2717 "insert into vtiger_cvcolumnlist (cvid,columnindex,columnname) values (".$cvid2.",1,'from:fromname:fromname:From:N')", 2718 "insert into vtiger_cvcolumnlist (cvid,columnindex,columnname) values (".$cvid2.",2,'to:tpname:toname:To:N')", 2719 "insert into vtiger_cvcolumnlist (cvid,columnindex,columnname) values (".$cvid2.",3,'body:body:body:Body:V')" 2720 2721 ); 2722 foreach($customview_query_array as $query) 2723 { 2724 Execute($query); 2725 } 2726 2727 2728 $query_array2 = Array( 2729 //"INSERT INTO vtiger_parenttabrel VALUES(2,4,2)", 2730 //"INSERT INTO vtiger_parenttabrel VALUES(2,6,3)", 2731 "update vtiger_cvcolumnlist set columnname ='vtiger_crmentity:smownerid:assigned_user_id:Emails_Sender:V' where cvid=20 and columnindex=3", 2732 "update vtiger_field set sequence = 2 where columnname='filename' and tablename = 'vtiger_attachments'", 2733 "delete from vtiger_cvcolumnlist where columnname = 'vtiger_seactivityrel:crmid:parent_id:Emails_Related_To:I'", 2734 "update vtiger_cvcolumnlist set columnindex = 1 where cvid=20 and columnindex=3", 2735 "update vtiger_field set info_type='ADV' where tabid=18 and columnname in ('street','pobox','city','state','postalcode','country','description')", 2736 "update vtiger_field set info_type='ADV' where tabid in (20,21,22,23) and columnname in ('description','terms_conditions')", 2737 2738 "create table vtiger_inventorytaxinfo (taxid int(3) NOT NULL, taxname varchar(50) default NULL, taxlabel varchar(50) default NULL, percentage decimal(7,3) default NULL, deleted int(1) default 0, PRIMARY KEY (taxid), KEY vtiger_inventorytaxinfo_taxname_idx (taxname))", 2739 "create table vtiger_producttaxrel ( productid int(11) NOT NULL, taxid int(3) NOT NULL, taxpercentage decimal(7,3) default NULL, KEY vtiger_producttaxrel_productid_idx (productid), KEY vtiger_producttaxrel_taxid_idx (taxid))", 2740 "alter table vtiger_producttaxrel ADD CONSTRAINT fk_1_vtiger_producttaxrel FOREIGN KEY (productid) REFERENCES vtiger_products(productid) ON DELETE CASCADE", 2741 2742 "update vtiger_field set uitype=83, tablename='vtiger_producttaxrel' where tabid=14 and fieldname='taxclass'", 2743 "insert into vtiger_moduleowners values(".$this->localGetTabID('Campaigns').",1)", 2744 2745 "alter table vtiger_attachments add column path varchar(255) default NULL" 2746 ); 2747 2748 foreach($query_array2 as $query) 2749 { 2750 Execute($query); 2751 } 2752 2753 //This code will retrieve all the attachments from db and write it in a file 2754 $attach_query_result = $conn->query("select vtiger_crmentity.createdtime, vtiger_attachments.* from vtiger_attachments inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_attachments.attachmentsid"); 2755 $noof_attachments = $conn->num_rows($attach_query_result); 2756 for($attach_count = 0;$attach_count < $noof_attachments ;$attach_count++) 2757 { 2758 $attach_id = $conn->query_result($attach_query_result,$attach_count,'attachmentsid'); 2759 $attach_name = $conn->query_result($attach_query_result,$attach_count,'name'); 2760 $attach_data = $conn->query_result($attach_query_result,$attach_count,'attachmentcontents'); 2761 2762 $created_time = $conn->query_result($attach_query_result,$attach_count,'createdtime'); 2763 //$filepath = decideFilePath(); 2764 2765 //Added to set the file path where to store the file based on the created time 2766 $date = explode(" ",$created_time); 2767 $date_details = explode("-",$date[0]); 2768 2769 $year = $date_details[0]; 2770 $month = $date_details[1]; 2771 $day = $date_details[2]; 2772 2773 //this is used to convert the month from number to string ie., 03 - March, 04 - April, etc., 2774 $month = date("F", mktime(0, 0, 0, $month, $day, $year)); 2775 2776 $week_no = floor(($day-1)/7)+1;//decide the week ie., 1-7 = week1, 8-14=week2, 15-21=week3, etc., 2777 $week = "week".$week_no; 2778 2779 $filepath = 'storage/'; 2780 2781 if(!is_dir($filepath.$year)) 2782 mkdir($filepath.$year); 2783 if(!is_dir($filepath.$year."/".$month)) 2784 mkdir($filepath."$year/$month"); 2785 if(!is_dir($filepath.$year."/".$month."/".$week)) 2786 mkdir($filepath."$year/$month/$week"); 2787 2788 $filepath = $filepath.$year."/".$month."/".$week."/"; 2789 $migrationlog->debug("File Path = $filepath"); 2790 //upto this added to set the file path based on attachment created time 2791 2792 //In this file name (attachmentid_filename) the file will be stored in the harddisk 2793 $moved_filename = $attach_id."_".$attach_name; 2794 //write the contents in the file 2795 $handle = @fopen($filepath.$moved_filename,'w'); 2796 fputs($handle, base64_decode($attach_data)); 2797 fclose($handle); 2798 2799 //update the path in the db 2800 $update_attach = Execute("update vtiger_attachments set path='".$filepath."' where attachmentsid=$attach_id"); 2801 } 2802 2803 //Before drop these fields we had read the contents of the file from db and wrote it in a file. 2804 $alter_query_array = Array( 2805 "alter table vtiger_attachments drop column attachmentsize", 2806 "alter table vtiger_attachments drop column attachmentcontents" 2807 ); 2808 foreach($alter_query_array as $query) 2809 { 2810 Execute($query); 2811 } 2812 2813 2814 //To populate the comboStrings for Campaigns module which are added newly 2815 require_once ('include/ComboStrings.php'); 2816 global $combo_strings; 2817 2818 $comboTables = Array('campaigntype','campaignstatus','expectedresponse'); 2819 foreach ($comboTables as $tablename) 2820 { 2821 $values = $combo_strings[$tablename."_dom"]; 2822 $i=0; 2823 foreach ($values as $val => $cal) 2824 { 2825 if($val != '') 2826 { 2827 $conn->query("insert into vtiger_".$tablename. " values(null,'".$val."',".$i.",1)"); 2828 } 2829 else 2830 { 2831 $conn->query("insert into vtiger_".$tablename. " values(null,'--None--',".$i.",1)"); 2832 } 2833 $i++; 2834 } 2835 2836 } 2837 2838 $update_query3 = "update vtiger_currency_info set conversion_rate=1, currency_status='Active', defaultid='-11' where id=1"; 2839 Execute($update_query3); 2840 2841 $update_query4 = "update vtiger_relatedlists set label='Purchase Order' where tabid=18 and name='get_purchase_orders'"; 2842 Execute($update_query4); 2843 2844 2845 2846 //Added on 27-05-06 2847 2848 $create_query27 = "CREATE TABLE vtiger_invitees (activityid int(19) NOT NULL, inviteeid int(19) NOT NULL, PRIMARY KEY (activityid,inviteeid))"; 2849 Execute($create_query27); 2850 2851 $alter_query_array17 = Array( 2852 "ALTER TABLE vtiger_users ADD column hour_format varchar(30) default 'am/pm' AFTER date_format", 2853 "ALTER TABLE vtiger_users ADD column start_hour varchar(30) default '10:00' AFTER hour_format", 2854 "ALTER TABLE vtiger_users ADD column end_hour varchar(30) default '23:00' AFTER start_hour" 2855 ); 2856 foreach($alter_query_array17 as $query) 2857 { 2858 Execute($query); 2859 } 2860 2861 $create_query28 = "CREATE TABLE vtiger_emaildetails ( 2862 emailid int(19) NOT NULL, 2863 from_email varchar(50) NOT NULL default '', 2864 to_email text, 2865 cc_email text, 2866 bcc_email text, 2867 assigned_user_email varchar(50) NOT NULL default '', 2868 idlists varchar(50) NOT NULL default '', 2869 email_flag varchar(50) NOT NULL default '', 2870 PRIMARY KEY (`emailid`) 2871 )"; 2872 Execute($create_query28); 2873 2874 2875 $obj_array = Array('Leads'=>'vtiger_leaddetails','Contacts'=>'vtiger_contactdetails'); 2876 $leadfieldid = $conn->query_result($conn->query("select fieldid from vtiger_field where tabid=7 and fieldname='email'"),0,'fieldid'); 2877 $contactfieldid = $conn->query_result($conn->query("select fieldid from vtiger_field where tabid=4 and fieldname='email'"),0,'fieldid'); 2878 $fieldid_array = Array("Leads"=>"$leadfieldid","Contacts"=>"$contactfieldid"); 2879 $idname_array = Array("Leads"=>"leadid","Contacts"=>"contactid"); 2880 2881 $query = 'select * from vtiger_seactivityrel where activityid in (select activityid from vtiger_activity where activitytype="Emails") group by activityid'; 2882 $result = $conn->query($query); 2883 $numofrows = $conn->num_rows($result); 2884 2885 for($i=0;$i<$numofrows;$i++) 2886 { 2887 $toemail = ""; 2888 $idlists = ''; 2889 2890 $emailid = $conn->query_result($result,$i,'activityid'); 2891 2892 $result1 = $conn->query("select * from vtiger_seactivityrel where activityid = $emailid"); 2893 while($row = $conn->fetch_array($result1)) 2894 { 2895 $result2 = $conn->query("select setype from vtiger_crmentity where crmid=".$row['crmid']); 2896 $module = $conn->query_result($result2,0,'setype'); 2897 $idlists .= $row['crmid']."@$fieldid_array[$module]|"; 2898 2899 if($module == 'Leads' || $module == 'Contacts') 2900 { 2901 $result3 = $conn->query("select lastname, firstname, email from $obj_array[$module] where $idname_array[$module] = ".$row['crmid']); 2902 2903 $toemail .= $conn->query_result($result3,0,'lastname')." ".$conn->query_result($result3,0,'firstname')."<".$conn->query_result($result3,0,'email').">###"; 2904 } 2905 else 2906 { 2907 //the parent is not a Lead or Contact. so we have avoided the insert query 2908 } 2909 } 2910 2911 //insert this idlists and toemail values in vtiger_emaildetails table 2912 $sql = "insert into vtiger_emaildetails values ($emailid,'',\"$toemail\",'','','',\"$idlists\",'SAVE')"; 2913 Execute($sql); 2914 } 2915 2916 2917 $update_query5 = "update vtiger_field set quickcreate=1, quickcreatesequence=NULL where tabid in (10,14)"; 2918 Execute($update_query5); 2919 2920 2921 2922 //Security vtiger_profile and vtiger_tab table handling by DON starts 2923 $sql_sec="select profileid from vtiger_profile"; 2924 $result_sec=$conn->query($sql_sec); 2925 $num_rows=$conn->num_rows($result_sec); 2926 for($i=0;$i<$num_rows;$i++) 2927 { 2928 $prof_id=$conn->query_result($result_sec,$i,'profileid'); 2929 $sql1_sec="insert into vtiger_profile2utility values(".$prof_id.",13,8,0)"; 2930 Execute($sql1_sec); 2931 2932 $sql2_sec="insert into vtiger_profile2utility values(".$prof_id.",7,9,0)"; 2933 Execute($sql2_sec); 2934 2935 $sql3_sec="insert into vtiger_profile2tab values(".$prof_id.",26,0)"; 2936 Execute($sql3_sec); 2937 2938 $sql4_sec="insert into vtiger_profile2tab values(".$prof_id.",27,0)"; 2939 Execute($sql4_sec); 2940 2941 $sql7_sec="insert into vtiger_profile2standardpermissions values(".$prof_id.",26,0,0)"; 2942 Execute($sql7_sec); 2943 2944 $sql8_sec="insert into vtiger_profile2standardpermissions values(".$prof_id.",26,1,0)"; 2945 Execute($sql8_sec); 2946 2947 $sql9_sec="insert into vtiger_profile2standardpermissions values(".$prof_id.",26,2,0)"; 2948 Execute($sql9_sec); 2949 2950 $sql10_sec="insert into vtiger_profile2standardpermissions values(".$prof_id.",26,3,0)"; 2951 Execute($sql10_sec); 2952 2953 $sql11_sec="insert into vtiger_profile2standardpermissions values(".$prof_id.",26,4,0)"; 2954 Execute($sql11_sec); 2955 2956 } 2957 2958 //Inserting into vtiger_tab tables 2959 $sec2="INSERT INTO vtiger_tab VALUES (27,'Portal',0,24,'Portal',null,null,1)"; 2960 $sec3="INSERT INTO vtiger_tab VALUES (28,'Webmails',0,25,'Webmails',null,null,1)"; 2961 2962 //Insert into vtiger_def_org_share tables 2963 $sec4="insert into vtiger_def_org_share values (".$conn->getUniqueID('vtiger_def_org_share').",26,2,0)"; 2964 2965 Execute($sec2); 2966 Execute($sec3); 2967 Execute($sec4); 2968 2969 //Inserting into datashare related modules table 2970 2971 Execute("insert into vtiger_datashare_relatedmodules_seq values(1)"); 2972 2973 //Lead Related Module 2974 Execute("insert into vtiger_datashare_relatedmodules values (".$conn->getUniqueID('vtiger_datashare_relatedmodules').",7,10)"); 2975 2976 //Account Related Module 2977 Execute("insert into vtiger_datashare_relatedmodules values (".$conn->getUniqueID('vtiger_datashare_relatedmodules').",6,2)"); 2978 Execute("insert into vtiger_datashare_relatedmodules values (".$conn->getUniqueID('vtiger_datashare_relatedmodules').",6,13)"); 2979 Execute("insert into vtiger_datashare_relatedmodules values (".$conn->getUniqueID('vtiger_datashare_relatedmodules').",6,20)"); 2980 Execute("insert into vtiger_datashare_relatedmodules values (".$conn->getUniqueID('vtiger_datashare_relatedmodules').",6,22)"); 2981 Execute("insert into vtiger_datashare_relatedmodules values (".$conn->getUniqueID('vtiger_datashare_relatedmodules').",6,23)"); 2982 Execute("insert into vtiger_datashare_relatedmodules values (".$conn->getUniqueID('vtiger_datashare_relatedmodules').",6,10)"); 2983 2984 2985 //Potential Related Module 2986 Execute("insert into vtiger_datashare_relatedmodules values (".$conn->getUniqueID('vtiger_datashare_relatedmodules').",2,20)"); 2987 Execute("insert into vtiger_datashare_relatedmodules values (".$conn->getUniqueID('vtiger_datashare_relatedmodules').",2,22)"); 2988 2989 //Quote Related Module 2990 Execute("insert into vtiger_datashare_relatedmodules values (".$conn->getUniqueID('vtiger_datashare_relatedmodules').",20,22)"); 2991 2992 //SO Related Module 2993 Execute("insert into vtiger_datashare_relatedmodules values (".$conn->getUniqueID('vtiger_datashare_relatedmodules').",22,23)"); 2994 2995 2996 //By Don Ends 2997 2998 //Added the vtiger_tabel vtiger_mail_accounts which has been added by mmbrich 2999 $alter_query18 = "alter table vtiger_mail_accounts add column int_mailer int(1) default '0'"; 3000 Execute($alter_query18); 3001 3002 $update_query_array5 = Array( 3003 "update vtiger_field set info_type='BAS' where tabid=6 and fieldname in ('tickersymbol','account_id')", 3004 "update vtiger_relatedlists set label = 'Activity History' where tabid in (4,6,7,20,21,22,23) and label = 'History'", 3005 "update vtiger_relatedlists set label = 'Products' where tabid=2 and name='get_products' and label='History'", 3006 "update vtiger_relatedlists set label = 'Activity History' where tabid=2 and name='get_history' and label='History'" 3007 ); 3008 foreach($update_query_array5 as $query) 3009 { 3010 Execute($query); 3011 } 3012 3013 $insert_query_array27 = Array( 3014 "insert into vtiger_relatedlists values(".$conn->getUniqueID('vtiger_relatedlists').",13,0,'get_ticket_history',3,'Ticket History',0)", 3015 "insert into vtiger_parenttabrel values (2,10,4)", 3016 "insert into vtiger_parenttabrel values (4,10,7)" 3017 ); 3018 foreach($insert_query_array27 as $query) 3019 { 3020 Execute($query); 3021 } 3022 3023 3024 //User fields added in field table 3025 $user_query_array = Array( 3026 3027 "alter table vtiger_users add column confirm_password varchar(50)", 3028 "insert into vtiger_tab values (29,'Users',0,26,'Users',null,null,1)", 3029 3030 "insert into vtiger_blocks values (79,29,'LBL_USERLOGIN_ROLE',1,0,0,0,0,0)", 3031 "insert into vtiger_blocks values (80,29,'LBL_MORE_INFORMATION',2,0,0,0,0,0)", 3032 "insert into vtiger_blocks values (81,29,'LBL_ADDRESS_INFORMATION',3,0,0,0,0,0)", 3033 3034 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'user_name','vtiger_users',1,'106','user_name','User Name',1,0,0,11,1,79,1,'V~M',1,null,'BAS')", 3035 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'is_admin','vtiger_users',1,'156','is_admin','Admin',1,0,0,3,2,79,1,'V~O',1,null,'BAS')", 3036 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'user_password','vtiger_users',1,'99','user_password','Password',1,0,0,30,3,79,4,'P~M',1,null,'BAS')", 3037 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'confirm_password','vtiger_users',1,'99','confirm_password','Confirm Password',1,0,0,30,4,79,4,'P~M',1,null,'BAS')", 3038 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'first_name','vtiger_users',1,'1','first_name','First Name',1,0,0,30,5,79,1,'V~O',1,null,'BAS')", 3039 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'last_name','vtiger_users',1,'2','last_name','Last Name',1,0,0,30,6,79,1,'V~M',1,null,'BAS')", 3040 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'roleid','vtiger_user2role',1,'98','roleid','Role',1,0,0,200,7,79,1,'V~M',1,null,'BAS')", 3041 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'email1','vtiger_users',1,'104','email1','Email',1,0,0,100,9,79,1,'E~M',1,null,'BAS')", 3042 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'status','vtiger_users',1,'115','status','Status',1,0,0,100,10,79,1,'V~O',1,null,'BAS')", 3043 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'activity_view','vtiger_users',1,'15','activity_view','Default Activity View',1,0,0,100,13,79,1,'V~O',1,null,'BAS')", 3044 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'lead_view','vtiger_users',1,'15','lead_view','Default Lead View',1,0,0,100,12,79,1,'V~O',1,null,'BAS')", 3045 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'currency_id','vtiger_users',1,'116','currency_id','Currency',1,0,0,100,11,79,1,'I~O',1,null,'BAS')", 3046 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'title','vtiger_users',1,'1','title','Title',1,0,0,50,1,80,1,'V~O',1,null,'BAS')", 3047 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'phone_work','vtiger_users',1,'1','phone_work','Office Phone',1,0,0,50,2,80,1,'V~O',1,null,'BAS')", 3048 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'department','vtiger_users',1,'1','department','Department',1,0,0,50,3,80,1,'V~O',1,null,'BAS')", 3049 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'phone_mobile','vtiger_users',1,'1','phone_mobile','Mobile',1,0,0,50,4,80,1,'V~O',1,null,'BAS')", 3050 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'reports_to_id','vtiger_users',1,'101','reports_to_id','Reports To',1,0,0,50,5,80,1,'V~O',1,null,'BAS')", 3051 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'phone_other','vtiger_users',1,'1','phone_other','Other Phone',1,0,0,50,5,80,1,'V~O',1,null,'BAS')", 3052 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'email2','vtiger_users',1,'13','email2','Other Email',1,0,0,100,6,80,1,'E~O',1,null,'BAS')", 3053 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'phone_fax','vtiger_users',1,'1','phone_fax','Fax',1,0,0,50,7,80,1,'V~O',1,null,'BAS')", 3054 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'yahoo_id','vtiger_users',1,'13','yahoo_id','Yahoo id',1,0,0,100,7,80,1,'E~O',1,null,'BAS')", 3055 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'phone_home','vtiger_users',1,'1','phone_home','Home Phone',1,0,0,50,8,80,1,'V~O',1,null,'BAS')", 3056 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'imagename','vtiger_users',1,'105','imagename','User Image',1,0,0,250,9,80,1,'V~O',1,null,'BAS')", 3057 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'date_format','vtiger_users',1,'15','date_format','Date Format',1,0,0,30,10,80,1,'V~O',1,null,'BAS')", 3058 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'tagcloud','vtiger_users',1,'103','tagcloud','Tag Cloud',1,0,0,250,13,80,1,'V~O',1,null,'BAS')", 3059 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'signature','vtiger_users',1,'21','signature','Signature',1,0,0,250,11,80,1,'V~O',1,null,'BAS')", 3060 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'description','vtiger_users',1,'21','description','Notes',1,0,0,250,12,80,1,'V~O',1,null,'BAS')", 3061 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'address_street','vtiger_users',1,'21','address_street','Street Address',1,0,0,250,1,81,1,'V~O',1,null,'BAS')", 3062 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'address_city','vtiger_users',1,'1','address_city','City',1,0,0,100,2,81,1,'V~O',1,null,'BAS')", 3063 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'address_state','vtiger_users',1,'1','address_state','State',1,0,0,100,3,81,1,'V~O',1,null,'BAS')", 3064 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'address_postalcode','vtiger_users',1,'1','address_postalcode','Postal Code',1,0,0,100,4,81,1,'V~O',1,null,'BAS')", 3065 "insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'address_country','vtiger_users',1,'1','address_country','Country',1,0,0,100,5,81,1,'V~O',1,null,'BAS')", 3066 ); 3067 foreach($user_query_array as $query) 3068 { 3069 Execute($query); 3070 } 3071 3072 $create_query29 = "CREATE TABLE vtiger_status ( 3073 `statusid` int(19) NOT NULL auto_increment, 3074 `status` varchar(200) NOT NULL, 3075 `sortorderid` int(19) NOT NULL default '0', 3076 `presence` int(1) NOT NULL default '1', 3077 PRIMARY KEY (`statusid`) 3078 )"; 3079 Execute($create_query29); 3080 3081 Execute("insert into vtiger_status values (1,'Active',0,1)"); 3082 Execute("insert into vtiger_status values (2,'Inactive',1,1)"); 3083 3084 $create_query30 = "CREATE TABLE vtiger_activity_view ( 3085 `activity_viewid` int(19) NOT NULL auto_increment, 3086 `activity_view` varchar(200) NOT NULL, 3087 `sortorderid` int(19) NOT NULL default '0', 3088 `presence` int(1) NOT NULL default '1', 3089 PRIMARY KEY (`activity_viewid`) 3090 )"; 3091 Execute($create_query30); 3092 3093 Execute("insert into vtiger_activity_view values (1,'Today',0,1)"); 3094 Execute("insert into vtiger_activity_view values (2,'This Week',1,1)"); 3095 Execute("insert into vtiger_activity_view values (3,'This Month',2,1)"); 3096 Execute("insert into vtiger_activity_view values (4,'This Year',3,1)"); 3097 3098 3099 $create_query31 = "CREATE TABLE vtiger_lead_view ( 3100 `lead_viewid` int(19) NOT NULL auto_increment, 3101 `lead_view` varchar(200) NOT NULL, 3102 `sortorderid` int(19) NOT NULL default '0', 3103 `presence` int(1) NOT NULL default '1', 3104 PRIMARY KEY (`lead_viewid`) 3105 )"; 3106 Execute($create_query31); 3107 3108 Execute("insert into vtiger_lead_view values (1,'Today',0,1)"); 3109 Execute("insert into vtiger_lead_view values (2,'Last 2 Days',1,1)"); 3110 Execute("insert into vtiger_lead_view values (3,'Last Week',2,1)"); 3111 3112 3113 $create_query32 = "CREATE TABLE vtiger_date_format ( 3114 `date_formatid` int(19) NOT NULL auto_increment, 3115 `date_format` varchar(200) NOT NULL, 3116 `sortorderid` int(19) NOT NULL default '0', 3117 `presence` int(1) NOT NULL default '1', 3118 PRIMARY KEY (`date_formatid`) 3119 )"; 3120 Execute($create_query32); 3121 3122 Execute("insert into vtiger_date_format values (1,'dd-mm-yyyy',0,1)"); 3123 Execute("insert into vtiger_date_format values (2,'mm-dd-yyyy',1,1)"); 3124 Execute("insert into vtiger_date_format values (3,'yyyy-mm-dd',2,1)"); 3125 //end of User fields added in field table 3126 3127 //Activities and Leads Added under Marketing 3128 Execute("insert into vtiger_parenttabrel values (2,7,5)"); 3129 Execute("insert into vtiger_parenttabrel values (2,9,6)"); 3130 Execute("insert into vtiger_parenttabrel values (4,9,8)"); 3131 3132 //Queries to remove the rss categories 3133 Execute("drop table vtiger_rsscategory"); 3134 Execute("delete from vtiger_field where tabid=24"); 3135 3136 //Added on 23-06-06 3137 Execute("insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'hour_format','vtiger_users',1,'116','hour_format','Calendar Hour Format',1,0,0,100,13,79,3,'I~O',1,null,'BAS')"); 3138 Execute("insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'end_hour','vtiger_users',1,'116','end_hour','Day ends at',1,0,0,100,15,79,3,'I~O',1,null,'BAS')"); 3139 Execute("insert into vtiger_field values (29,".$conn->getUniqueID("vtiger_field").",'start_hour','vtiger_users',1,'116','start_hour','Day starts at',1,0,0,100,14,79,3,'I~O',1,null,'BAS')"); 3140 3141 Execute("insert into vtiger_relatedlists values (".$conn->getUniqueID('vtiger_relatedlists').",".getTabid("Campaigns").",".getTabid("Potentials").",'get_opportunities',3,'Potentials',0)"); 3142 Execute("insert into vtiger_relatedlists values(".$conn->getUniqueID('vtiger_relatedlists').",".getTabid("Campaigns").",9,'get_activities',4,'Activities',0)"); 3143 3144 Execute("insert into vtiger_field values (2,".$conn->getUniqueID("vtiger_field").",'campaignid','vtiger_potential',1,'58','campaignid','Campaign Source',1,0,0,100,12,1,1,'N~O',1,null,'BAS')"); 3145 3146 //Added on 28-06-06 3147 //Campaigns module added in Leads and Contacts RelatedList 3148 Execute("insert into vtiger_relatedlists values(".$conn->getUniqueID('vtiger_relatedlists').",".getTabid("Leads").",".getTabid("Campaigns").",'get_campaigns',6,'Campaigns',0)"); 3149 Execute("insert into vtiger_relatedlists values(".$conn->getUniqueID('vtiger_relatedlists').",".getTabid("Contacts").",".getTabid("Campaigns").",'get_campaigns',11,'Campaigns',0)"); 3150 3151 //Contact Name has been removed from Events Information 3152 Execute("delete from vtiger_field where tabid=16 and fieldname='contact_id'"); 3153 3154 //queries to resequence the fields 3155 $fieldname=array('eventstatus','sendnotification','activitytype','location','createdtime','modifiedtime','taskpriority','notime','visibility'); 3156 $tablename=array('vtiger_activity','vtiger_activity','vtiger_activity','vtiger_activity','vtiger_crmentity','vtiger_crmentity','vtiger_activity','vtiger_activity','vtiger_activity'); 3157 3158 $sequence = array(10,11,12,13,14,15,16,17,18); 3159 for($j = 0;$j < 9;$j++) 3160 { 3161 Execute("update vtiger_field set sequence=".$sequence[$j]." where tablename='".$tablename[$j]."' && fieldname='".$fieldname[$j]."' and tabid=16"); 3162 } 3163 3164 //Campaign has been removed from field table 3165 Execute("delete from vtiger_field where tabid=7 and fieldname='campaignid'"); 3166 Execute("delete from vtiger_field where tabid=4 and fieldname='campaignid'"); 3167 3168 //Query added to have Calendar under Marketing and Support 3169 $conn->query("insert into vtiger_parenttabrel values (2,17,7)"); 3170 $conn->query("insert into vtiger_parenttabrel values (4,17,9)"); 3171 3172 3173 3174 //Added on 02-08-2006 ie., 2nd August 2006 3175 3176 //column added for proxy server settings 3177 Execute("alter table vtiger_systems add column server_port int(19) default NULL after server"); 3178 3179 //type changed to support decimal places 3180 Execute("alter table vtiger_campaign change expectedrevenue expectedrevenue decimal(11,3)"); 3181 Execute("alter table vtiger_campaign change budgetcost budgetcost decimal(11,3)"); 3182 Execute("alter table vtiger_campaign change actualcost actualcost decimal(11,3)"); 3183 Execute("alter table vtiger_campaign change expectedroi expectedroi decimal(11,3)"); 3184 Execute("alter table vtiger_campaign change actualroi actualroi decimal(11,3)"); 3185 3186 //homeorder value modified to get graph in homepage 3187 Execute("update vtiger_users set homeorder='ALVT,PLVT,QLTQ,CVLVT,HLT,OLV,GRT,OLTSO,ILTI,MNL,HDB'"); 3188 3189 //Removed activities from product related list 3190 Execute("delete from vtiger_relatedlists where tabid = 14 and related_tabid=9"); 3191 Execute("insert into vtiger_relatedlists values(".$conn->getUniqueID('vtiger_relatedlists').",".getTabid("HelpDesk").",9,'get_history',4,'Activity History',0)"); 3192 3193 //Assigned to field for Events made Optional 3194 Execute("update vtiger_field set typeofdata='V~M' where columnname='smownerid' and tabid=16 and fieldname='assigned_user_id'"); 3195 3196 //Query added to have Notes under Marketing and Support --Jeri -- 04-06-06 3197 Execute("insert into vtiger_parenttabrel values (2,8,8)"); 3198 3199 3200 //Update Query for quickcreate sequence of Campaign & Ticket -- Added by Ahmed -- 11-07-2006 3201 Execute("update vtiger_field set quickcreatesequence='3' where fieldname='filename' and tabid=13"); 3202 Execute("update vtiger_field set quickcreatesequence='4' where fieldname='ticketpriorities' and tabid=13"); 3203 Execute("update vtiger_field set quickcreatesequence='3' where fieldname='campaigntype' and tabid=26"); 3204 Execute("update vtiger_field set quickcreatesequence='6' where fieldname='campaignstatus' and tabid=26"); 3205 Execute("update vtiger_field set quickcreatesequence='2' where fieldname='closingdate' and tabid=26"); 3206 3207 3208 //Added for Tax and Inventory - Product details handling 3209 3210 Execute("CREATE TABLE vtiger_inventoryproductrel (id int(19) NOT NULL, productid int(19) NOT NULL, sequence_no int(4) NOT NULL default 1, quantity int(19) default NULL, listprice decimal(11,3) default NULL, discount_percent decimal(7,3) default NULL, discount_amount decimal(11,3) default NULL, comment varchar(100) default NULL, KEY inventoryproductrel_id_idx (id), KEY inventoryproductrel_productid_idx (productid) ) ENGINE=InnoDB"); 3211 3212 //Execute("alter table vtiger_inventorytaxinfo add column deleted int(1) default 0"); 3213 3214 Execute("CREATE TABLE vtiger_shippingtaxinfo ( taxid int(3) NOT NULL, taxname varchar(50) default NULL, taxlabel varchar(50) default NULL, percentage decimal(7,3) default NULL, deleted int(1) default '0', PRIMARY KEY (taxid), KEY shippingtaxinfo_taxname_idx (taxname) ) ENGINE=InnoDB"); 3215 3216 Execute("CREATE TABLE vtiger_inventoryshippingrel (id int(19) NOT NULL, KEY inventoryishippingrel_id_idx (id) ) ENGINE=InnoDB"); 3217 3218 Execute("insert into vtiger_field values (21,".$conn->getUniqueID("vtiger_field").",'taxtype','vtiger_purchaseorder',1,'15','hdnTaxType','Tax Type',1,0,0,100,14,57,3,'V~O',1,null,'BAS')"); 3219 Execute("insert into vtiger_field values (21,".$conn->getUniqueID("vtiger_field").",'discount_percent','vtiger_purchaseorder',1,'1','hdnDiscountPercent','Discount Percent',1,0,0,100,14,57,3,'N~O',1,null,'BAS')"); 3220 Execute("insert into vtiger_field values (21,".$conn->getUniqueID("vtiger_field").",'discount_amount','vtiger_purchaseorder',1,'1','hdnDiscountAmount','Discount Amount',1,0,0,100,14,57,3,'N~O',1,null,'BAS')"); 3221 Execute("insert into vtiger_field values (21,".$conn->getUniqueID("vtiger_field").",'s_h_amount','vtiger_purchaseorder',1,'1','hdnS_H_Amount','S&H Amount',1,0,0,100,14,57,3,'N~O',1,null,'BAS')"); 3222 3223 Execute("insert into vtiger_field values (22,".$conn->getUniqueID("vtiger_field").",'taxtype','vtiger_salesorder',1,'15','hdnTaxType','Tax Type',1,0,0,100,15,63,3,'V~O',1,null,'BAS')"); 3224 Execute("insert into vtiger_field values (22,".$conn->getUniqueID("vtiger_field").",'discount_percent','vtiger_salesorder',1,'1','hdnDiscountPercent','Discount Percent',1,0,0,100,15,63,3,'N~O',1,null,'BAS')"); 3225 Execute("insert into vtiger_field values (22,".$conn->getUniqueID("vtiger_field").",'discount_amount','vtiger_salesorder',1,'1','hdnDiscountAmount','Discount Amount',1,0,0,100,15,63,3,'N~O',1,null,'BAS')"); 3226 Execute("insert into vtiger_field values (22,".$conn->getUniqueID("vtiger_field").",'s_h_amount','vtiger_salesorder',1,'1','hdnS_H_Amount','S&H Amount',1,0,0,100,15,63,3,'N~O',1,null,'BAS')"); 3227 3228 Execute("insert into vtiger_field values (20,".$conn->getUniqueID("vtiger_field").",'taxtype','vtiger_quotes',1,'15','hdnTaxType','Tax Type',1,0,0,100,14,51,3,'V~O',1,null,'BAS')"); 3229 Execute("insert into vtiger_field values (20,".$conn->getUniqueID("vtiger_field").",'discount_percent','vtiger_quotes',1,'1','hdnDiscountPercent','Discount Percent',1,0,0,100,14,51,3,'N~O',1,null,'BAS')"); 3230 Execute("insert into vtiger_field values (20,".$conn->getUniqueID("vtiger_field").",'discount_amount','vtiger_quotes',1,'1','hdnDiscountAmount','Discount Amount',1,0,0,100,14,51,3,'N~O',1,null,'BAS')"); 3231 Execute("insert into vtiger_field values (20,".$conn->getUniqueID("vtiger_field").",'s_h_amount','vtiger_quotes',1,'1','hdnS_H_Amount','S&H Amount',1,0,0,100,14,51,3,'N~O',1,null,'BAS')"); 3232 3233 Execute("insert into vtiger_field values (23,".$conn->getUniqueID("vtiger_field").",'taxtype','vtiger_invoice',1,'15','hdnTaxType','Tax Type',1,0,0,100,13,69,3,'V~O',1,null,'BAS')"); 3234 Execute("insert into vtiger_field values (23,".$conn->getUniqueID("vtiger_field").",'discount_percent','vtiger_invoice',1,'1','hdnDiscountPercent','Discount Percent',1,0,0,100,13,69,3,'N~O',1,null,'BAS')"); 3235 Execute("insert into vtiger_field values (23,".$conn->getUniqueID("vtiger_field").",'discount_amount','vtiger_invoice',1,'1','hdnDiscountAmount','Discount Amount',1,0,0,100,13,69,3,'N~O',1,null,'BAS')"); 3236 Execute("insert into vtiger_field values (23,".$conn->getUniqueID("vtiger_field").",'s_h_amount','vtiger_invoice',1,'1','hdnS_H_Amount','S&H Amount',1,0,0,100,14,57,3,'N~O',1,null,'BAS')"); 3237 3238 Execute("alter table vtiger_purchaseorder add column taxtype varchar(25) default NULL after subtotal"); 3239 Execute("alter table vtiger_purchaseorder add column discount_percent decimal(11,3) default NULL after taxtype"); 3240 Execute("alter table vtiger_purchaseorder add column discount_amount decimal(11,3) default NULL after discount_percent"); 3241 Execute("alter table vtiger_purchaseorder add column s_h_amount decimal(11,3) default NULL after discount_amount"); 3242 3243 Execute("alter table vtiger_salesorder add column taxtype varchar(25) default NULL after subtotal"); 3244 Execute("alter table vtiger_salesorder add column discount_percent decimal(11,3) default NULL after taxtype"); 3245 Execute("alter table vtiger_salesorder add column discount_amount decimal(11,3) default NULL after discount_percent"); 3246 Execute("alter table vtiger_salesorder add column s_h_amount decimal(11,3) default NULL after discount_amount"); 3247 3248 Execute("alter table vtiger_quotes add column taxtype varchar(25) default NULL after total"); 3249 Execute("alter table vtiger_quotes add column discount_percent decimal(11,3) default NULL after taxtype"); 3250 Execute("alter table vtiger_quotes add column discount_amount decimal(11,3) default NULL after discount_percent"); 3251 Execute("alter table vtiger_quotes add column s_h_amount decimal(11,3) default NULL after discount_amount"); 3252 3253 Execute("alter table vtiger_invoice add column taxtype varchar(25) default NULL after total"); 3254 Execute("alter table vtiger_invoice add column discount_percent decimal(11,3) default NULL after taxtype"); 3255 Execute("alter table vtiger_invoice add column discount_amount decimal(11,3) default NULL after discount_percent"); 3256 Execute("alter table vtiger_invoice add column s_h_amount decimal(11,3) default NULL after discount_amount"); 3257 3258 3259 //Update Query to Match Notes List View Header Fieldnames with Custom View combo values 3260 Execute("update vtiger_field set fieldlabel='Title' where fieldlabel='Subject' and tabid=8"); 3261 Execute("update vtiger_field set fieldlabel='File' where fieldlabel='Attachment' and tabid=8"); 3262 3263 3264 //Update Query to change the UI type of Rating Field in Accounts Module from 1 to 15 to display combo list 3265 Execute("update vtiger_field set uitype=15 where tabid=6 and fieldname='rating'"); 3266 3267 3268 //Insert and Update Query for new block addition for Campaigns Module 3269 Execute("update vtiger_blocks set blocklabel='LBL_EXPECTATIONS_AND_ACTUALS' where tabid=26 and blockid=78"); 3270 Execute("insert into vtiger_blocks values (82,26,'LBL_DESCRIPTION_INFORMATION',4,0,0,0,0,0)"); 3271 3272 3273 //Update Query for rearrangements of fields in Campaigns Module's Detail/Edit/Create View 3274 Execute("update vtiger_field set sequence=1 where tabid=26 and columnname='campaignname' and fieldname='campaignname'"); 3275 Execute("update vtiger_field set sequence=2 where tabid=26 and columnname='campaignstatus' and fieldname='campaignstatus'"); 3276 Execute("update vtiger_field set sequence=3 where tabid=26 and columnname='smownerid' and fieldname='assigned_user_id'"); 3277 Execute("update vtiger_field set sequence=4 where tabid=26 and columnname='product_id' and fieldname='product_id'"); 3278 Execute("update vtiger_field set sequence=5 where tabid=26 and columnname='campaigntype' and fieldname='campaigntype'"); 3279 Execute("update vtiger_field set sequence=6 where tabid=26 and columnname='closingdate' and fieldname='closingdate'"); 3280 Execute("update vtiger_field set sequence=7 where tabid=26 and columnname='targetaudience' and fieldname='targetaudience'"); 3281 Execute("update vtiger_field set sequence=8 where tabid=26 and columnname='targetsize' and fieldname='targetsize'"); 3282 Execute("update vtiger_field set sequence=9 where tabid=26 and columnname='sponsor' and fieldname='sponsor'"); 3283 Execute("update vtiger_field set sequence=10 where tabid=26 and columnname='numsent' and fieldname='numsent'"); 3284 Execute("update vtiger_field set sequence=11 where tabid=26 and columnname='createdtime' and fieldname='createdtime'"); 3285 Execute("update vtiger_field set sequence=12 where tabid=26 and columnname='modifiedtime' and fieldname='modifiedtime'"); 3286 3287 Execute("update vtiger_field set sequence=1, block=78 where tabid=26 and columnname='budgetcost' and fieldname='budgetcost'"); 3288 Execute("update vtiger_field set sequence=2, block=78 where tabid=26 and columnname='actualcost' and fieldname='actualcost'"); 3289 Execute("update vtiger_field set sequence=3, block=78 where tabid=26 and columnname='expectedresponse' and fieldname='expectedresponse'"); 3290 Execute("update vtiger_field set sequence=4, block=78 where tabid=26 and columnname='expectedrevenue' and fieldname='expectedrevenue'"); 3291 Execute("update vtiger_field set sequence=5, block=78 where tabid=26 and columnname='expectedsalescount' and fieldname='expectedsalescount'"); 3292 Execute("update vtiger_field set sequence=6, block=78 where tabid=26 and columnname='actualsalescount' and fieldname='actualsalescount'"); 3293 Execute("update vtiger_field set sequence=7, block=78 where tabid=26 and columnname='expectedresponsecount' and fieldname='expectedresponsecount'"); 3294 Execute("update vtiger_field set sequence=8, block=78 where tabid=26 and columnname='actualresponsecount' and fieldname='actualresponsecount'"); 3295 Execute("update vtiger_field set sequence=9, block=78 where tabid=26 and columnname='expectedroi' and fieldname='expectedroi'"); 3296 Execute("update vtiger_field set sequence=10, block=78 where tabid=26 and columnname='actualroi' and fieldname='actualroi'"); 3297 3298 3299 3300 //Update query to set the fieldname in user detail/edit/create view 3301 Execute("update vtiger_field set sequence=1 where tabid=29 and columnname='user_name' and fieldname='user_name' and block=79"); 3302 Execute("update vtiger_field set sequence=2 where tabid=29 and columnname='is_admin' and fieldname='is_admin' and block=79"); 3303 Execute("update vtiger_field set sequence=3 where tabid=29 and columnname='user_password' and fieldname='user_password' and block=79"); 3304 Execute("update vtiger_field set sequence=4 where tabid=29 and columnname='email1' and fieldname='email1' and block=79"); 3305 Execute("update vtiger_field set sequence=5 where tabid=29 and columnname='confirm_password' and fieldname='confirm_password' and block=79"); 3306 Execute("update vtiger_field set sequence=6 where tabid=29 and columnname='status' and fieldname='status' and block=79"); 3307 Execute("update vtiger_field set sequence=7 where tabid=29 and columnname='first_name' and fieldname='first_name' and block=79"); 3308 Execute("update vtiger_field set sequence=8 where tabid=29 and columnname='currency_id' and fieldname='currency_id' and block=79"); 3309 Execute("update vtiger_field set sequence=9 where tabid=29 and columnname='last_name' and fieldname='last_name' and block=79"); 3310 Execute("update vtiger_field set sequence=10 where tabid=29 and columnname='lead_view' and fieldname='lead_view' and block=79"); 3311 Execute("update vtiger_field set sequence=11 where tabid=29 and columnname='roleid' and fieldname='roleid' and block=79"); 3312 Execute("update vtiger_field set sequence=12 where tabid=29 and columnname='activity_view' and fieldname='activity_view' and block=79"); 3313 Execute("update vtiger_field set sequence=13 where tabid=29 and columnname='hour_format' and fieldname='hour_format' and block=79"); 3314 Execute("update vtiger_field set sequence=14 where tabid=29 and columnname='start_hour' and fieldname='start_hour' and block=79"); 3315 Execute("update vtiger_field set sequence=15 where tabid=29 and columnname='end_hour' and fieldname='end_hour' and block=79"); 3316 3317 Execute("update vtiger_field set sequence=1 where tabid=29 and columnname='title' and fieldname='title' and block=80"); 3318 Execute("update vtiger_field set sequence=2 where tabid=29 and columnname='phone_fax' and fieldname='phone_fax' and block=80"); 3319 Execute("update vtiger_field set sequence=3 where tabid=29 and columnname='department' and fieldname='department' and block=80"); 3320 Execute("update vtiger_field set sequence=4 where tabid=29 and columnname='email2' and fieldname='email2' and block=80"); 3321 Execute("update vtiger_field set sequence=5 where tabid=29 and columnname='phone_work' and fieldname='phone_work' and block=80"); 3322 Execute("update vtiger_field set sequence=6 where tabid=29 and columnname='yahoo_id' and fieldname='yahoo_id' and block=80"); 3323 Execute("update vtiger_field set sequence=7 where tabid=29 and columnname='phone_mobile' and fieldname='phone_mobile' and block=80"); 3324 Execute("update vtiger_field set sequence=8 where tabid=29 and columnname='reports_to_id' and fieldname='reports_to_id' and block=80"); 3325 Execute("update vtiger_field set sequence=9 where tabid=29 and columnname='phone_home' and fieldname='phone_home' and block=80"); 3326 Execute("update vtiger_field set sequence=10 where tabid=29 and columnname='imagename' and fieldname='imagename' and block=80"); 3327 Execute("update vtiger_field set sequence=11 where tabid=29 and columnname='phone_other' and fieldname='phone_other' and block=80"); 3328 Execute("update vtiger_field set sequence=12 where tabid=29 and columnname='date_format' and fieldname='date_format' and block=80"); 3329 Execute("update vtiger_field set sequence=13 where tabid=29 and columnname='signature' and fieldname='signature' and block=80"); 3330 Execute("update vtiger_field set sequence=14 where tabid=29 and columnname='description' and fieldname='description' and block=80"); 3331 Execute("update vtiger_field set sequence=15 where tabid=29 and columnname='tagcloud' and fieldname='tagcloud' and block=80"); 3332 3333 Execute("update vtiger_field set sequence=1 where tabid=29 and columnname='address_street' and fieldname='address_street' and block=81"); 3334 Execute("update vtiger_field set sequence=2 where tabid=29 and columnname='address_country' and fieldname='address_country' and block=81"); 3335 Execute("update vtiger_field set sequence=3 where tabid=29 and columnname='address_city' and fieldname='address_city' and block=81"); 3336 Execute("update vtiger_field set sequence=4 where tabid=29 and columnname='address_postalcode' and fieldname='address_postalcode' and block=81"); 3337 Execute("update vtiger_field set sequence=5 where tabid=29 and columnname='address_state' and fieldname='address_state' and block=81"); 3338 3339 //Added for Recurring events 3340 Execute("alter table vtiger_recurringevents add column recurringfreq int(19) default NULL"); 3341 Execute("alter table vtiger_recurringevents add column recurringinfo varchar(50) default NULL"); 3342 3343 3344 //Update Query for changing the uitype for existing picklist entries making it non-editable 3345 Execute("update vtiger_field set uitype=111 where fieldname in ('sales_stage','ticketstatus','taskstatus','eventstatus','faqstatus','quotestage','postatus','sostatus','invoicestatus')"); 3346 3347 3348 3349 3350 3351 3352 3353 3354 3355 3356 //Inventory Tax handlings -- Starts 3357 3358 //Added to populate the default Shipping & Hanlding tax informations 3359 $shvatid = $conn->getUniqueID("vtiger_shippingtaxinfo"); 3360 $shsalesid = $conn->getUniqueID("vtiger_shippingtaxinfo"); 3361 $shserviceid = $conn->getUniqueID("vtiger_shippingtaxinfo"); 3362 3363 $conn->query("insert into vtiger_shippingtaxinfo values($shvatid,'shtax".$shvatid."','VAT','4.50','0')"); 3364 $conn->query("insert into vtiger_shippingtaxinfo values($shsalesid,'shtax".$shsalesid."','Sales','10.00','0')"); 3365 $conn->query("insert into vtiger_shippingtaxinfo values($shserviceid,'shtax".$shserviceid."','Service','12.50','0')"); 3366 3367 //After added these taxes we should add these taxes as columns in vtiger_inventoryshippingrel table 3368 $conn->query("alter table vtiger_inventoryshippingrel add column shtax$shvatid decimal(7,3) default NULL"); 3369 $conn->query("alter table vtiger_inventoryshippingrel add column shtax$shsalesid decimal(7,3) default NULL"); 3370 $conn->query("alter table vtiger_inventoryshippingrel add column shtax$shserviceid decimal(7,3) default NULL"); 3371 3372 3373 //Added to populate the Common tax which will be used to save the existing tax (percentage will be calculated based on the total tax amount retrieved from the entity tables of PO, SO, Quotes and Invoice) 3374 $migratedtaxid = 1; 3375 $migratedtaxid = $conn->getUniqueID("vtiger_inventorytaxinfo"); 3376 $migrated_taxname = "tax$migratedtaxid"; 3377 $conn->query("insert into vtiger_inventorytaxinfo values($migratedtaxid,'".$migrated_taxname."','Tax','0.00','0')"); 3378 3379 //After added these taxes we should add these taxes as columns in vtiger_inventoryproductrel table 3380 $conn->query("alter table vtiger_inventoryproductrel add column $migrated_taxname decimal(7,3) default NULL"); 3381 3382 //Now we should create tax for each and every value given in picklist taxclass 3383 $taxres = $conn->query("select * from vtiger_taxclass"); 3384 $taxcount = $conn->num_rows($taxres); 3385 for($i=0;$i<$taxcount;$i++) 3386 { 3387 $taxlabel = $conn->query_result($taxres,$i,'taxclass'); 3388 3389 $newtaxid = $conn->getUniqueID("vtiger_inventorytaxinfo"); 3390 $addtaxres = $conn->query("alter table vtiger_inventoryproductrel add column tax$newtaxid decimal(7,3) default NULL"); 3391 if($addtaxres) 3392 $conn->query("insert into vtiger_inventorytaxinfo values($newtaxid,'tax".$newtaxid."','".$taxlabel."','0.00','0')"); 3393 } 3394 //Finished the add tax process based on the available tax classes 3395 3396 //To save Product - Tax relationship 3397 //get Product - taxclass and add entry in vtiger_producttaxrel for this product - tax relationship 3398 $productres = $conn->query("select productid, taxclass from vtiger_products"); 3399 $productcount = $conn->num_rows($productres); 3400 for($i=0;$i<$productcount;$i++) 3401 { 3402 $productid = $conn->query_result($productres,$i,'productid'); 3403 $taxlabel = $conn->query_result($productres,$i,'taxclass'); 3404 3405 $taxres = $conn->query("select taxid from vtiger_inventorytaxinfo where taxlabel='".addslashes($taxlabel)."'"); 3406 $taxid = $conn->query_result($taxres,0,'taxid'); 3407 3408 $taxquery = "insert into vtiger_producttaxrel values($productid, \"$taxid\", '0.00')"; 3409 //Execute($taxquery); 3410 $conn->query($taxquery); 3411 } 3412 3413 3414 3415 3416 3417 //Retrieve values from poproductrel, soproductrel, quotesproductrel, invoiceproductrel and store in vtiger_inventoryproductrel 3418 3419 $inventory_tables = Array( 3420 'vtiger_poproductrel'=>'purchaseorderid', 3421 'vtiger_soproductrel'=>'salesorderid', 3422 'vtiger_quotesproductrel'=>'quotesid', 3423 'vtiger_invoiceproductrel'=>'invoiceid' 3424 ); 3425 3426 foreach($inventory_tables as $tablename => $idname) 3427 { 3428 $res = $conn->query("select * from $tablename order by $idname"); 3429 $count = $conn->num_rows($res); 3430 3431 $id = $oldid = 0; 3432 $seqno = 0; 3433 3434 for($i=0;$i<$count;$i++) 3435 { 3436 $oldid = $id; 3437 $id = $conn->query_result($res,$i,$idname); 3438 3439 //for every new PO/SO/Quotes/Invoice entity we should set the sequence start value as 1 3440 if($id != $oldid) 3441 $seqno = 1; 3442 3443 $productid = $conn->query_result($res,$i,'productid'); 3444 $quantity = $conn->query_result($res,$i,'quantity'); 3445 $listprice = $conn->query_result($res,$i,'listprice'); 3446 3447 $query1 = "insert into vtiger_inventoryproductrel(id,productid,sequence_no,quantity,listprice) values($id, $productid,$seqno, $quantity, $listprice)"; 3448 Execute($query1); 3449 $seqno++; 3450 } 3451 } 3452 3453 3454 //Now for each and every PO, SO, Quotes and Invoice we should get the total, discount, tax 3455 $inventory_tables = Array( 3456 'vtiger_purchaseorder'=>'purchaseorderid', 3457 'vtiger_salesorder'=>'salesorderid', 3458 'vtiger_quotes'=>'quoteid', 3459 'vtiger_invoice'=>'invoiceid' 3460 ); 3461 3462 foreach($inventory_tables as $tablename => $idname) 3463 { 3464 $res2 = $conn->query("select * from $tablename order by $idname"); 3465 $entitycount = $conn->num_rows($res2); 3466 3467 for($i=0;$i<$entitycount;$i++) 3468 { 3469 $idval = $conn->query_result($res2,$i,$idname); 3470 3471 //$res3 = $conn->query("select * from $tablename where $idname=$idval"); 3472 $subtotal = $conn->query_result($res2,$i,'subtotal'); 3473 $taxamount = $conn->query_result($res2,$i,'salestax'); 3474 3475 //Now based on the inventory tax total - calculate the percentage 3476 $taxpercent = '0.00'; 3477 if($taxamount > 0 && $subtotal >0) 3478 { 3479 $taxpercent = $taxamount*100/$subtotal; 3480 } 3481 3482 //update the taxtype as group 3483 $query2 = "update $tablename set taxtype='group'"; 3484 Execute($query2); 3485 3486 //update the calculated percentage for the entity ie., PO/SO/Quotes/Invoice 3487 $query3 = "update vtiger_inventoryproductrel set $migrated_taxname='".$taxpercent."' where id=$idval"; 3488 Execute($query3); 3489 } 3490 } 3491 3492 3493 //we have retrieve and saved all the values, so we can delete the unwanted tables 3494 Execute("drop table vtiger_poproductrel"); 3495 Execute("drop table vtiger_soproductrel"); 3496 Execute("drop table vtiger_quotesproductrel"); 3497 Execute("drop table vtiger_invoiceproductrel"); 3498 3499 //Inventory Tax handlings -- Ends 3500 3501 3502 //Add Inventory History tracking tables ie.,PO Status, SO Status, Quote Stage and Invoice Status tables 3503 //PO Status 3504 Execute("CREATE TABLE vtiger_postatushistory ( historyid int(19) NOT NULL auto_increment, purchaseorderid int(19) NOT NULL, vendorname varchar(100) default NULL, total decimal(10,0) default NULL, postatus varchar(200) default NULL, lastmodified datetime default NULL, PRIMARY KEY (historyid), KEY postatushistory_purchaseorderid_idx (purchaseorderid), CONSTRAINT fk_1_vtiger_postatushistory FOREIGN KEY (purchaseorderid) REFERENCES vtiger_purchaseorder (purchaseorderid) ON DELETE CASCADE ) ENGINE=InnoDB"); 3505 3506 //SO Status 3507 Execute("CREATE TABLE vtiger_sostatushistory (historyid int(19) NOT NULL auto_increment, salesorderid int(19) NOT NULL, accountname varchar(100) default NULL, total decimal(10,0) default NULL, sostatus varchar(200) default NULL, lastmodified datetime default NULL, PRIMARY KEY (historyid), KEY sostatushistory_salesorderid_idx (salesorderid), CONSTRAINT fk_1_vtiger_sostatushistory FOREIGN KEY (salesorderid) REFERENCES vtiger_salesorder (salesorderid) ON DELETE CASCADE ) ENGINE=InnoDB"); 3508 3509 //Quote Stage 3510 Execute("CREATE TABLE vtiger_quotestagehistory ( historyid int(19) NOT NULL auto_increment, quoteid int(19) NOT NULL, accountname varchar(100) default NULL, total decimal(10,0) default NULL, quotestage varchar(200) default NULL, lastmodified datetime default NULL, PRIMARY KEY (historyid), KEY quotestagehistory_quoteid_idx (quoteid), CONSTRAINT fk_1_vtiger_quotestagehistory FOREIGN KEY (quoteid) REFERENCES vtiger_quotes (quoteid) ON DELETE CASCADE) ENGINE=InnoDB"); 3511 3512 //Invoice Status 3513 Execute("CREATE TABLE vtiger_invoicestatushistory ( historyid int(19) NOT NULL auto_increment, invoiceid int(19) NOT NULL, accountname varchar(100) default NULL, total decimal(10,0) default NULL, invoicestatus varchar(200) default NULL, lastmodified datetime default NULL, PRIMARY KEY (historyid), KEY invoicestatushistory_invoiceid_idx (invoiceid), CONSTRAINT fk_1_vtiger_invoicestatushistory FOREIGN KEY (invoiceid) REFERENCES vtiger_invoice (invoiceid) ON DELETE CASCADE) ENGINE=InnoDB"); 3514 3515 3516 //User image handling 3517 Execute("insert into vtiger_blocks values (83,29,'LBL_USER_IMAGE_INFORMATION',4,0,0,0,0,0)"); 3518 Execute("update vtiger_field set block=83 where tabid=29 and fieldname='imagename' and columnname='imagename'"); 3519 3520 Execute("update vtiger_field set tablename='vtiger_products' where fieldname='taxclass' && tabid=14"); 3521 3522 Execute("update vtiger_field set info_type='BAS' where tabid=4 and fieldname='email' and columnname='email'"); 3523 3524 Execute("update vtiger_field set info_type='ADV' where tabid=4 and fieldname='otherphone' and columnname='otherphone'"); 3525 3526 Execute("CREATE TABLE vtiger_salesmanattachmentsrel ( smid int(19) NOT NULL default '0', attachmentsid int(19) NOT NULL default '0', PRIMARY KEY (smid, attachmentsid), KEY salesmanattachmentsrel_smid_idx (smid), KEY salesmanattachmentsrel_attachmentsid_idx (attachmentsid), CONSTRAINT fk_1_vtiger_salesmanattachmentsrel FOREIGN KEY (smid) REFERENCES vtiger_users (id), CONSTRAINT fk_2_vtiger_salesmanattachmentsrel FOREIGN KEY (attachmentsid) REFERENCES vtiger_attachments (attachmentsid) ON DELETE CASCADE) ENGINE=InnoDB"); 3527 3528 3529 //Changes made for Activity merge with Calendar - Starts 3530 Execute("alter table vtiger_activity add column time_end varchar(50) default NULL after time_start"); 3531 3532 Execute("delete from vtiger_tab where tabid=17"); 3533 3534 Execute("update vtiger_tab set name='Calendar',tablabel='Calendar' where tabid=9"); 3535 3536 Execute("insert into vtiger_field values (9,".$conn->getUniqueID("vtiger_field").",'time_end','vtiger_activity', 1,'2','time_end','End Time',1,0,0,100,6,19,3,'T~O',1,null,'BAS')"); 3537 3538 Execute("insert into vtiger_field values (16,".$conn->getUniqueID("vtiger_field").",'time_end','vtiger_activity', 1,'2','time_end','End Time',1,0,0,100,6,41,3,'T~M',1,null,'BAS')"); 3539 3540 Execute("delete from vtiger_profile2tab where tabid=17"); 3541 3542 Execute("delete from vtiger_org_share_action2tab where tabid=17"); 3543 3544 Execute("delete from vtiger_def_org_share where tabid=17"); 3545 3546 Execute("delete from vtiger_parenttabrel where tabid=17"); 3547 //Changes made for Activity merge with Calendar - Ends 3548 3549 //audit trial table 3550 Execute("create table vtiger_audit_trial(auditid int(19) NOT NULL, userid int(19) default NULL, module varchar(255) default NULL, action varchar(255) default NULL, recordid varchar(20) default NULL, actiondate datetime default NULL, PRIMARY KEY (auditid)) ENGINE=InnoDB"); 3551 3552 //Added after 5 rc release 3553 Execute("alter table vtiger_account modify siccode varchar(50)"); 3554 Execute("update vtiger_field set typeofdata='V~O' where fieldname='siccode' and columnname='siccode' and tabid=6"); 3555 3556 //changes made for CustomView and Reports - Activities changed to Calendar -- Starts 3557 //Added to change the entitytype from Activities to Calendar for customview 3558 Execute("update vtiger_crmentity set setype='Calendar' where setype='Activities'"); 3559 Execute("update vtiger_customview set entitytype='Calendar' where entitytype='Activities'"); 3560 3561 //Added to change the primarymodule from Activities to Calendar for Reports 3562 Execute("update vtiger_reportmodules set primarymodule='Calendar' where primarymodule='Activities'"); 3563 Execute("update vtiger_reportmodules set primarymodule='PurchaseOrder' where primarymodule='Orders'"); 3564 Execute("update vtiger_reportmodules set secondarymodules='PurchaseOrder' where secondarymodules='Orders'"); 3565 3566 //we should change the Activities to Calendar in columnname values in customview and report related tables 3567 $prefix = "vtiger_"; 3568 $change_cols_array = Array( 3569 "cvcolumnlist"=>"columnname", 3570 "cvstdfilter"=>"columnname", 3571 "cvadvfilter"=>"columnname", 3572 "selectcolumn"=>"columnname", 3573 "relcriteria"=>"columnname", 3574 "reportsortcol"=>"columnname", 3575 "reportdatefilter"=>"datecolumnname", 3576 "reportsummary"=>"columnname", 3577 ); 3578 3579 //This is to change Activities to Calendar 3580 foreach($change_cols_array as $tablename => $columnname) 3581 { 3582 $result = $conn->query("select $columnname from $prefix$tablename where $columnname like \"%Activities%\""); 3583 3584 while($row = $conn->fetch_row($result)) 3585 { 3586 if($row[$columnname] !='' && $row[$columnname] != 'none') 3587 { 3588 Execute("update $prefix$tablename set $columnname=\"".str_replace("Activities","Calendar",$row[$columnname])."\" where $columnname=\"$row[$columnname]\""); 3589 } 3590 } 3591 } 3592 3593 //This is to change the Orders to PurchaseOrder 3594 foreach($change_cols_array as $tablename => $columnname) 3595 { 3596 $result1 = $conn->query("select $columnname from $prefix$tablename where $columnname like \"%Orders%\""); 3597 3598 while($row1 = $conn->fetch_row($result1)) 3599 { 3600 if($row1[$columnname] !='' && $row1[$columnname] != 'none') 3601 { 3602 Execute("update $prefix$tablename set $columnname=\"".str_replace("Orders","PurchaseOrder",$row1[$columnname])."\" where $columnname=\"$row1[$columnname]\""); 3603 } 3604 } 3605 } 3606 //we have to change the table name from activity to crmentity for customview activity description 3607 Execute('update vtiger_cvcolumnlist set columnname="vtiger_crmentity:description:description:Calendar_Description:V" where columnname="vtiger_activity:description:description:Calendar_Description:V"'); 3608 //we have to change the table name (alias) from activity to crmentiryCalendar for reports activity description 3609 Execute('update vtiger_selectcolumn set columnname="vtiger_crmentityCalendar:description:Calendar_Description:description:V" where columnname="vtiger_activity:description:Calendar_Description:description:V"'); 3610 //changes made for CustomView and Reports - Activities changed to Calendar -- Ends 3611 3612 Execute("update vtiger_field set uitype = 16 where tabid=2 and uitype=111 and columnname='sales_stage'"); 3613 3614 Execute("update vtiger_field set quickcreate=1,quickcreatesequence=null where fieldname='duration_hours' and tabid=16"); 3615 3616 Execute("update vtiger_field set quickcreate=0,quickcreatesequence=5 where fieldname='due_date' and tabid=16"); 3617 3618 //we have to add id, sortorderid and presence in all existing custom field pick list tables. 3619 $cf_picklist_res = $conn->query("select fieldname from vtiger_field where uitype=15 and fieldname like 'cf_%'"); 3620 $noofPicklists = $conn->num_rows($cf_picklist_res); 3621 for($i=0;$i<$noofPicklists;$i++) 3622 { 3623 $fieldname = $conn->query_result($cf_picklist_res,$i,'fieldname'); 3624 3625 $tablename = "vtiger_".$fieldname; 3626 $idname = $fieldname."id"; 3627 3628 $alterquery = "alter table $tablename add column $idname int(19) auto_increment PRIMARY KEY FIRST, add column sortorderid int(19) default 0 NOT NULL, add column presence int(1) default 1 NOT NULL"; 3629 Execute($alterquery); 3630 } 3631 3632 $conn->query("alter table vtiger_organizationdetails drop primary key"); 3633 Execute("alter table vtiger_organizationdetails change column organizationame organizationname varchar(60) NOT NULL"); 3634 Execute("alter table vtiger_organizationdetails ADD PRIMARY KEY (organizationname)"); 3635 3636 //Activity related changes 3637 Execute('update vtiger_field set typeofdata="D~M~OTH~GE~date_start~Start Date & Time", uitype=23 where fieldname="due_date" and tabid=16'); 3638 Execute('update vtiger_field set uitype=53 where tabid=16 and fieldname="assigned_user_id"'); 3639 3640 Execute('update vtiger_field set typeofdata="D~M~OTH~GE~date_start~Start Date & Time", uitype=23 where fieldname="due_date" and tabid=9'); 3641 Execute('update vtiger_field set uitype=53 where tabid=9 and fieldname="assigned_user_id"'); 3642 3643 Execute("alter table vtiger_activity change column subject subject varchar(100) NOT NULL"); 3644 Execute("alter table vtiger_activity change column activitytype activitytype varchar(50) NOT NULL"); 3645 Execute("alter table vtiger_activity change column date_start date_start date NOT NULL"); 3646 Execute("alter table vtiger_activity change column time_start time_start varchar(50) default NULL"); 3647 Execute("alter table vtiger_activity change column visibility visibility varchar(50) NOT NULL default 'all'"); 3648 3649 Execute("delete from vtiger_field where tabid=14 and fieldname='currency'"); 3650 3651 //Product related changes 3652 Execute('update vtiger_field set typeofdata="D~O~OTH~GE~sales_start_date~Sales Start Date" where tabid=14 and fieldname="sales_end_date"'); 3653 Execute('update vtiger_field set typeofdata="D~O~OTH~GE~start_date~Start Date" where tabid=14 and fieldname="expiry_date"'); 3654 3655 //changes related to Incoming mail server settings 3656 Execute("alter table vtiger_mail_accounts drop column showbody"); 3657 3658 //change the Account relatedlist Activity label from Acivities to Activities 3659 Execute("update vtiger_relatedlists set label='Activities' where tabid=6 and relation_id=3"); 3660 3661 //change the fieldname from title to notes_title for notes and update in columnlist also 3662 Execute("update vtiger_field set fieldname='notes_title' where tabid=8 and fieldname='title'"); 3663 Execute('update vtiger_cvcolumnlist set columnname="vtiger_notes:title:notes_title:Notes_Title:V" where columnname="vtiger_notes:title:title:Notes_Title:V"'); 3664 3665 //change the sequence of Billing and Shipping address details for Inventory modules 3666 Execute("update vtiger_field set sequence=5 where tabid in (20,21,22,23) and fieldname='bill_city'"); 3667 Execute("update vtiger_field set sequence=6 where tabid in (20,21,22,23) and fieldname='ship_city'"); 3668 Execute("update vtiger_field set sequence=7 where tabid in (20,21,22,23) and fieldname='bill_state'"); 3669 Execute("update vtiger_field set sequence=8 where tabid in (20,21,22,23) and fieldname='ship_state'"); 3670 Execute("update vtiger_field set sequence=9 where tabid in (20,21,22,23) and fieldname='bill_code'"); 3671 Execute("update vtiger_field set sequence=10 where tabid in (20,21,22,23) and fieldname='ship_code'"); 3672 Execute("update vtiger_field set sequence=11 where tabid in (20,21,22,23) and fieldname='bill_country'"); 3673 Execute("update vtiger_field set sequence=12 where tabid in (20,21,22,23) and fieldname='ship_country'"); 3674 3675 //for vtiger_campaignleadrel table 3676 Execute("alter table vtiger_campaignleadrel DROP PRIMARY KEY"); 3677 Execute("alter table vtiger_campaignleadrel ADD PRIMARY KEY (campaignid,leadid)"); 3678 3679 //for vtiger_campaigncontrel table 3680 Execute("alter table vtiger_campaigncontrel DROP PRIMARY KEY"); 3681 Execute("alter table vtiger_campaigncontrel ADD PRIMARY KEY (campaignid,contactid)"); 3682 3683 //for vtiger_seactivityrel table 3684 Execute("alter table vtiger_seactivityrel DROP PRIMARY KEY"); 3685 Execute("alter table vtiger_seactivityrel ADD PRIMARY KEY (crmid,activityid)"); 3686 3687 //change the block for vendor address details 3688 Execute("update vtiger_field set fieldname='street' where tabid=18 and columnname='street'"); 3689 Execute("update vtiger_field set block=46 where tabid=18 and fieldname in ('city','country','pobox','postalcode','state','street')"); 3690 3691 //change the calendar sharing access to private 3692 Execute("update vtiger_def_org_share set permission=3 where tabid=9"); 3693 //Now sharing access is not available for Emails 3694 Execute("delete from vtiger_def_org_share where tabid=10"); 3695 3696 //we have to delete the entry from datashare_relatedmodules (Settings -> Sharing Access -> Add Privileges) 3697 Execute("delete from vtiger_datashare_relatedmodules where tabid=10"); 3698 Execute("delete from vtiger_datashare_relatedmodules where relatedto_tabid=10"); 3699 3700 3701 //change the share_action_name in vtiger_org_share_action_mapping table for entry Public:Read,Create/Edit 3702 Execute('update vtiger_org_share_action_mapping set share_action_name="Public: Read, Create/Edit" where share_action_name="Public:Read,Create/Edit"'); 3703 3704 3705 //add all field entries to def_org_field and profile2field tables for Campaigns 3706 $field_res = $conn->query("select fieldid from vtiger_field where tabid=26"); 3707 for($i=0;$i<$conn->num_rows($field_res);$i++) 3708 { 3709 $fieldid = $conn->query_result($field_res,$i,'fieldid'); 3710 3711 populateFieldForSecurity('26',$fieldid); 3712 } 3713 3714 //delete the entries from vtiger_profile2standardpermissions table for Emails 3715 Execute("delete from vtiger_profile2standardpermissions where tabid=10"); 3716 3717 //delete the tagcloud entry from users 3718 Execute("delete from vtiger_field where tabid=29 and fieldname='tagcloud'"); 3719 Execute("alter table vtiger_users drop column tagcloud"); 3720 3721 //we have missed to add the Received Shipment in postatus table 3722 $sortorderid = $conn->query_result($conn->query("select max(sortorderid) as id from vtiger_postatus"),0,'id')+1; 3723 Execute("insert into vtiger_postatus values('','Received Shipment',$sortorderid,1)"); 3724 3725 3726 //Added after 5.0 GA release 3727 //CALCULATE Activity End Time (time_end) 3728 //we have to calculate activity end time (time_end) based on start time (time_start) and duration (duration_hours, duration_minutes) 3729 $sql = "select * from vtiger_activity"; 3730 $result = $conn->query($sql); 3731 $num_rows = $conn->num_rows($result); 3732 for($i=0;$i<$num_rows;$i++) 3733 { 3734 //First we have to retrieve the time_start, duration_hours and duration_minutes and form as a date with time 3735 $activityid = $conn->query_result($result,$i,'activityid'); 3736 $date_start = $conn->query_result($result,$i,'date_start'); 3737 $time_start = $conn->query_result($result,$i,'time_start'); 3738 $duration_hours = $conn->query_result($result,$i,'duration_hours'); 3739 $duration_minutes = $conn->query_result($result,$i,'duration_minutes'); 3740 3741 if($duration_hours != '' && $duration_minutes != '') 3742 { 3743 $date_details = explode("-",$date_start); 3744 $start_year = $date_details[0]; 3745 $start_month = $date_details[1]; 3746 $start_date = $date_details[2]; 3747 3748 $start_details = explode(":",$time_start); 3749 $start_hour = $start_details[0]; 3750 $start_minutes = $start_details[1]; 3751 3752 $full_duration = "$duration_hours:$duration_minutes:00"; 3753 3754 $start = date("Y-m-d H:i:s",mktime($start_hour, $start_minutes, 0, $start_month, $start_date, $start_year)); 3755 $end = date("Y-m-d H:i:s",mktime($start_hour+$duration_hours, $start_minutes+$duration_minutes, 0, $start_month, $start_date, $start_year)); 3756 3757 $end_details = explode(" ",$end); 3758 $due_date = $end_details[0]; 3759 3760 $end_time_details = explode(":",$end_details[1]); 3761 $time_end = $end_time_details[0].":".$end_time_details[1]; 3762 3763 $update_query = "update vtiger_activity set due_date=\"$due_date\", time_end=\"$time_end\" where activityid=$activityid"; 3764 3765 $conn->query($update_query); 3766 } 3767 } 3768 3769 //Added after 5.0.1 3770 //we have to delete the entries from customview and report related tables for deleted customfields 3771 include ("modules/Migration/ModifyDatabase/deleteCustomFields.php"); 3772 3773 //5.0.2 database changes - added on 27-10-06 3774 3775 //Query added to show Manufacturer field in Products module 3776 Execute("update vtiger_field set displaytype=1,block=31 where tabid=14 and block=1"); 3777 Execute("update vtiger_field set block=23,displaytype=1 where block=1 and displaytype=23 and tabid=10"); 3778 Execute("update vtiger_field set block=22,displaytype=1 where block=1 and displaytype=22 and tabid=10"); 3779 3780 //Added to rearange the attachment in HelpDesk 3781 Execute(" update vtiger_field set block=25,sequence=12 where tabid=13 and fieldname='filename'"); 3782 3783 //Query added to as entityname,its tablename,its primarykey are saved in a table 3784 Execute(" CREATE TABLE `vtiger_entityname` ( 3785 `tabid` int(19) NOT NULL default '0', 3786 `modulename` varchar(50) NOT NULL, 3787 `tablename` varchar(50) NOT NULL, 3788 `fieldname` varchar(150) NOT NULL, 3789 `entityidfield` varchar(150) NOT NULL, 3790 PRIMARY KEY (`tabid`), 3791 KEY `entityname_tabid_idx` (`tabid`) 3792 )"); 3793 3794 //Data Populated for the existing modules 3795 Execute("insert into vtiger_entityname values(7,'Leads','vtiger_leaddetails','lastname,firstname','leadid')"); 3796 Execute("insert into vtiger_entityname values(6,'Accounts','vtiger_account','accountname','accountid')"); 3797 Execute("insert into vtiger_entityname values(4,'Contacts','vtiger_contactdetails','lastname,firstname','contactid')"); 3798 Execute("insert into vtiger_entityname values(2,'Potentials','vtiger_potential','potentialname','potentialid')"); 3799 Execute("insert into vtiger_entityname values(8,'Notes','vtiger_notes','title','notesid')"); 3800 Execute("insert into vtiger_entityname values(13,'HelpDesk','vtiger_troubletickets','title','ticketid')"); 3801 Execute("insert into vtiger_entityname values(9,'Calendar','vtiger_activity','subject','activityid')"); 3802 Execute("insert into vtiger_entityname values(10,'Emails','vtiger_activity','subject','activityid')"); 3803 Execute("insert into vtiger_entityname values(14,'Products','vtiger_products','productname','productid')"); 3804 Execute("insert into vtiger_entityname values(29,'Users','vtiger_users','last_name,first_name','id')"); 3805 Execute("insert into vtiger_entityname values(23,'Invoice','vtiger_invoice','subject','invoiceid')"); 3806 Execute("insert into vtiger_entityname values(20,'Quotes','vtiger_quotes','subject','quoteid')"); 3807 Execute("insert into vtiger_entityname values(21,'PurchaseOrder','vtiger_purchaseorder','subject','purchaseorderid')"); 3808 Execute("insert into vtiger_entityname values(22,'SalesOrder','vtiger_salesorder','subject','salesorderid')"); 3809 Execute("insert into vtiger_entityname values(18,'Vendors','vtiger_vendor','vendorname','vendorid')"); 3810 Execute("insert into vtiger_entityname values(19,'PriceBooks','vtiger_pricebook','bookname','pricebookid')"); 3811 Execute("insert into vtiger_entityname values(26,'Campaigns','vtiger_campaign','campaignname','campaignid')"); 3812 Execute("insert into vtiger_entityname values(15,'Faq','vtiger_faq','question','id')"); 3813 3814 //added quantity in stock in product default listview - All 3815 $res = $conn->query("select vtiger_cvcolumnlist.cvid from vtiger_cvcolumnlist inner join vtiger_customview on vtiger_cvcolumnlist.cvid=vtiger_customview.cvid where entitytype='Products' and viewname='All'"); 3816 if($conn->num_rows != 0) 3817 { 3818 $cvid = $conn->query_result($res,0,'cvid'); 3819 Execute("insert into vtiger_cvcolumnlist values($cvid,5,'vtiger_products:qtyinstock:qtyinstock:Products_Quantity_In_Stock:V')"); 3820 } 3821 3822 3823 3824 3825 //Finally add vtiger_ prefix for all the entries in 'tablename' column in field table - 2nd August 2006 3826 $field_res = $conn->query("select fieldid, tablename from vtiger_field"); 3827 for($field_count=0;$field_count<$conn->num_rows($field_res);$field_count++) 3828 { 3829 //get the tablename 3830 $tablename = $conn->query_result($field_res,$field_count,'tablename'); 3831 3832 //check whether the table name has the prefix vtiger_ 3833 if(substr($tablename, 0, 7) != 'vtiger_') 3834 { 3835 $tablename = "vtiger_$tablename"; 3836 3837 //Now update the tablename 3838 $fieldid = $conn->query_result($field_res,$field_count,'fieldid'); 3839 Execute("update vtiger_field set tablename=\"$tablename\" where fieldid=$fieldid"); 3840 } 3841 } 3842 3843 3844 3845 3846 //Added to get the conversion rate and update for all records 3847 //include("modules/Migration/ModifyDatabase/updateCurrency.php"); 3848 ?> 3849 <script> 3850 function ajaxSaveResponse(response) 3851 { 3852 //alert(response.responseText); 3853 alert("Currency Changes has been made Successfully"); 3854 } 3855 3856 if(!confirm("Are you using Dollar $ as Currency? \n Click OK to remain as $, Cancel to change the currency conversion rate.")) 3857 { 3858 getConversionRate(''); 3859 } 3860 3861 function getConversionRate(err) 3862 { 3863 var crate = prompt(err+"\nPlease enter the conversion rate of your currency"); 3864 3865 if(crate != 0 && crate > 0) 3866 { 3867 var ajaxObj = new VtigerAjax(ajaxSaveResponse); 3868 url = 'module=Migration&action=updateCurrency&ajax=1&crate='+crate; 3869 ajaxObj.process("index.php?",url); 3870 } 3871 else 3872 { 3873 getConversionRate("Please give valid conversion rate ( > 0)"); 3874 } 3875 } 3876 </script> 3877 <?php 3878 3879 3880 3881 3882 //Function which is used to execute the query and display the result within tr tag. Also it stores the success and failure queries in a array where we can get this array to find the list of success and failure queries at the end of migraion. 3883 function Execute($query) 3884 { 3885 global $conn, $query_count, $success_query_count, $failure_query_count, $success_query_array, $failure_query_array; 3886 global $migrationlog; 3887 3888 $status = $conn->query($query); 3889 3890 $query_count++; 3891 if(is_object($status)) 3892 { 3893 echo ' 3894 <tr width="100%"> 3895 <td width="25%" nowrap>'.$status.'</td> 3896 <td width="5%"><font color="green"> S </font></td> 3897 <td width="70%">'.$query.'</td> 3898 </tr>'; 3899 $success_query_array[$success_query_count++] = $query; 3900 $migrationlog->debug("Query Success ==> $query"); 3901 } 3902 else 3903 { 3904 echo ' 3905 <tr width="100%"> 3906 <td width="25%">'.$status.'</td> 3907 <td width="5%"><font color="red"><b> F </b></font></td> 3908 <td width="70%">'.$query.'</td> 3909 </tr>'; 3910 $failure_query_array[$failure_query_count++] = $query; 3911 $migrationlog->debug("Query Failed ==> $query"); 3912 //$migrationlog->debug("Error is ==> ".$conn->ErrorMsg()); 3913 } 3914 } 3915 3916 //Added on 23-12-2005 which is used to populate the vtiger_profile2field and vtiger_def_org_field table entries for the field per tab 3917 //if we enter a field in vtiger_field table then we must populate that field in these table for security access 3918 function populateFieldForSecurity($tabid,$fieldid) 3919 { 3920 global $conn; 3921 3922 $profileresult = $conn->query("select * from vtiger_profile"); 3923 $countprofiles = $conn->num_rows($profileresult); 3924 for ($i=0;$i<$countprofiles;$i++) 3925 { 3926 $profileid = $conn->query_result($profileresult,$i,'profileid'); 3927 $sqlProf2FieldInsert[$i] = 'insert into vtiger_profile2field values ('.$profileid.','.$tabid.','.$fieldid.',0,1)'; 3928 Execute($sqlProf2FieldInsert[$i]); 3929 } 3930 $def_query = "insert into vtiger_def_org_field values (".$tabid.",".$fieldid.",0,1)"; 3931 Execute($def_query); 3932 } 3933 3934 function localcreateRole($roleName,$parentRoleId,$roleProfileArray) 3935 { 3936 global $migrationlog; 3937 $migrationlog->debug("Entering localcreateRole(".$roleName.",".$parentRoleId.",".$roleProfileArray.") method ..."); 3938 3939 global $conn; 3940 $parentRoleDetails = localgetRoleInformation($parentRoleId); 3941 $parentRoleInfo=$parentRoleDetails[$parentRoleId]; 3942 $roleid_no=$conn->getUniqueId("vtiger_role"); 3943 $roleId='H'.$roleid_no; 3944 $parentRoleHr=$parentRoleInfo[1]; 3945 $parentRoleDepth=$parentRoleInfo[2]; 3946 $nowParentRoleHr=$parentRoleHr.'::'.$roleId; 3947 $nowRoleDepth=$parentRoleDepth + 1; 3948 3949 //Inserting vtiger_role into db 3950 $query="insert into vtiger_role values('".$roleId."','".$roleName."','".$nowParentRoleHr."',".$nowRoleDepth.")"; 3951 $conn->query($query); 3952 3953 //Inserting into vtiger_role2profile vtiger_table 3954 foreach($roleProfileArray as $profileId) 3955 { 3956 if($profileId != '') 3957 { 3958 localinsertRole2ProfileRelation($roleId,$profileId); 3959 } 3960 } 3961 3962 $migrationlog->debug("Exiting localcreateRole method ..."); 3963 return $roleId; 3964 3965 } 3966 function localgetRoleInformation($roleid) 3967 { 3968 global $migrationlog; 3969 $migrationlog->debug("Entering localgetRoleInformation(".$roleid.") method ..."); 3970 global $conn; 3971 3972 $query = "select * from vtiger_role where roleid='".$roleid."'"; 3973 $result = $conn->query($query); 3974 $rolename=$conn->query_result($result,0,'rolename'); 3975 $parentrole=$conn->query_result($result,0,'parentrole'); 3976 $roledepth=$conn->query_result($result,0,'depth'); 3977 $parentRoleArr=explode('::',$parentrole); 3978 $immediateParent=$parentRoleArr[sizeof($parentRoleArr)-2]; 3979 3980 $roleDet=Array(); 3981 $roleDet[]=$rolename; 3982 $roleDet[]=$parentrole; 3983 $roleDet[]=$roledepth; 3984 $roleDet[]=$immediateParent; 3985 $roleInfo=Array(); 3986 $roleInfo[$roleid]=$roleDet; 3987 3988 $migrationlog->debug("Exiting localgetRoleInformation method ..."); 3989 3990 return $roleInfo; 3991 } 3992 3993 function localinsertRole2ProfileRelation($roleId,$profileId) 3994 { 3995 global $migrationlog; 3996 $migrationlog->debug("Entering localinsertRole2ProfileRelation(".$roleId.",".$profileId.") method ..."); 3997 3998 global $conn; 3999 $query="insert into vtiger_role2profile values('".$roleId."',".$profileId.")"; 4000 $conn->query($query); 4001 4002 $migrationlog->debug("Exiting localinsertRole2ProfileRelation method ..."); 4003 } 4004 4005 ?>
titre
Description
Corps
titre
Description
Corps
titre
Description
Corps
titre
Corps
| Généré le : Sun Feb 25 10:22:19 2007 | par Balluche grâce à PHPXref 0.7 |