PageRenderTime 57ms CodeModel.GetById 19ms RepoModel.GetById 0ms app.codeStats 2ms

/modules/Migration/DBChanges/42P2_to_50.php

https://bitbucket.org/thomashii/vtigercrm-6-for-postgresql
PHP | 4235 lines | 3026 code | 739 blank | 470 comment | 73 complexity | cf57535456211787245b75e9bfe19fc4 MD5 | raw file
Possible License(s): Apache-2.0, LGPL-3.0, LGPL-2.1, GPL-2.0, GPL-3.0

Large files files are truncated, but you can click here to view the full file

  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. ini_set("memory_limit","32M");
  12. global $php_max_execution_time;
  13. set_time_limit($php_max_execution_time);
  14. //This file is used to modify the database from 4.2Patch2 to 5.0 Alpha release
  15. global $conn;
  16. global $migrationlog;
  17. global $query_count, $success_query_count, $failure_query_count;
  18. global $success_query_array, $failure_query_array;
  19. $migrationlog->debug("\n\nDB Changes from 4.2.x to 5.0 GA -------- Starts \n\n");
  20. //Added to put prefix vtiger_ in some of the columns in tables which are used for CV and Reports and field -- 23-06-06
  21. $migrationlog->debug("Going to rename the table names with prefix vtiger_");
  22. include("modules/Migration/rename_tables.php");
  23. $migrationlog->debug("Renaming the table names with prefix vtiger_ has been finished");
  24. $migrationlog->debug("Database Modifications for 4.2 Patch2 ==> 5.0(Alpha) Dev 3 Starts here.");
  25. //These changes have been made in 4.2.3. The following queries have been included who has run the migration from 4.2 Patch2
  26. $wordtemp = $conn->getColumnNames("vtiger_wordtemplates");
  27. if(is_array($wordtemp) && !in_array("templateid",$wordtemp))
  28. {
  29. $wordtemplate_query1 = "alter table vtiger_wordtemplates DROP PRIMARY KEY";
  30. Execute($wordtemplate_query1);
  31. $wordtemplate_query3 = "alter table vtiger_wordtemplates add column templateid integer(19) unsigned auto_increment primary key FIRST";
  32. Execute($wordtemplate_query3);
  33. }
  34. //upto this added to modify the wordtemplates table which will be in the case of migrate from 4.2 Path2.
  35. /****************** 5.0(Alpha) dev version 1 Database changes -- Starts*********************/
  36. //Added the vtiger_announcement table creation to avoid the error
  37. $ann_query = "CREATE TABLE vtiger_announcement (
  38. creatorid int8 NOT NULL,
  39. announcement text,
  40. title varchar(255) default NULL,
  41. time timestamp NOT NULL default CURRENT_TIMESTAMP,
  42. PRIMARY KEY (creatorid)
  43. )";
  44. Execute($ann_query);
  45. //Added Primay Keys for the left out tables
  46. $alter_array1 = Array(
  47. "alter table vtiger_activity_reminder ADD PRIMARY KEY (activity_id,recurringid)",
  48. "alter table vtiger_activitygrouprelation ADD PRIMARY KEY (activityid)",
  49. "alter table vtiger_cvadvfilter ADD PRIMARY KEY (cvid,columnindex)",
  50. "alter table vtiger_cvcolumnlist ADD PRIMARY KEY (cvid,columnindex)",
  51. "alter table vtiger_cvstdfilter ADD PRIMARY KEY (cvid)",
  52. "alter table vtiger_def_org_field ADD PRIMARY KEY (fieldid)",
  53. "alter table vtiger_leadgrouprelation ADD PRIMARY KEY (leadid)",
  54. "alter table vtiger_leadgrouprelation drop key leadgrouprelation_IDX0",
  55. "alter table vtiger_profile2field ADD PRIMARY KEY (profileid,fieldid)",
  56. "alter table vtiger_profile2standardpermissions ADD PRIMARY KEY (profileid,tabid,Operation)",
  57. "alter table vtiger_profile2standardpermissions drop index idx_prof2stad",
  58. "alter table vtiger_profile2utility ADD PRIMARY KEY (profileid,tabid,activityid)",
  59. "alter table vtiger_profile2utility drop index idx_prof2utility",
  60. "alter table vtiger_relcriteria ADD PRIMARY KEY (queryid,columnindex)",
  61. "alter table vtiger_reportdatefilter ADD PRIMARY KEY (datefilterid)",
  62. "alter table vtiger_reportdatefilter DROP INDEX reportdatefilter_IDX0",
  63. "alter table vtiger_reportsortcol ADD PRIMARY KEY (sortcolid,reportid)",
  64. "alter table vtiger_reportsummary ADD PRIMARY KEY (reportsummaryid,summarytype,columnname)",
  65. "drop table vtiger_role2action",
  66. "drop table vtiger_role2tab",
  67. "alter table vtiger_selectcolumn ADD PRIMARY KEY (queryid,columnindex)",
  68. "alter table vtiger_ticketgrouprelation ADD PRIMARY KEY (ticketid)",
  69. "alter table vtiger_ticketstracktime ADD PRIMARY KEY (ticket_id)",
  70. "alter table vtiger_users2group ADD PRIMARY KEY (groupname,userid)",
  71. "alter table vtiger_users2group DROP INDEX idx_users2group",
  72. );
  73. foreach($alter_array1 as $query)
  74. {
  75. Execute($query);
  76. }
  77. //Tables vtiger_profile2globalpermissions, vtiger_actionmapping creation
  78. $create_sql1 ="CREATE TABLE vtiger_profile2globalpermissions (profileid int8 NOT NULL, globalactionid int8 NOT NULL, globalactionpermission int8 default NULL, PRIMARY KEY (profileid, globalactionid)) ";
  79. Execute($create_sql1);
  80. $create_sql2 = "CREATE TABLE vtiger_actionmapping (actionid int8 NOT NULL, actionname varchar(200) NOT NULL, securitycheck int8 default NULL, PRIMARY KEY (actionid,actionname))";
  81. Execute($create_sql2);
  82. //For all Profiles, insert the following entries into vtiger_profile2global permissions table:
  83. $sql = 'select * from vtiger_profile';
  84. $res = $conn->query($sql);
  85. $noofprofiles = $conn->num_rows($res);
  86. for($i=0;$i<$noofprofiles;$i++)
  87. {
  88. $profile_id = $conn->query_result($res,$i,'profileid');
  89. $sql1 = "insert into vtiger_profile2globalpermissions values ($profile_id,1,1)";
  90. $sql2 = "insert into vtiger_profile2globalpermissions values ($profile_id,2,1)";
  91. Execute($sql1);
  92. Execute($sql2);
  93. }
  94. //Removing entries for Dashboard and Home module from vtiger_profile2standardpermissions table
  95. $del_query1 = "delete from vtiger_profile2standardpermissions where tabid in(1,3)";
  96. Execute($del_query1);
  97. //For all Profile do the following insert into vtiger_profile2utility table:
  98. $sql = 'select * from vtiger_profile';
  99. $res = $conn->query($sql);
  100. $noofprofiles = $conn->num_rows($res);
  101. /* Commented by Don. Handled below
  102. for($i=0;$i<$noofprofiles;$i++)
  103. {
  104. $profile_id = $conn->query_result($res,$i,'profileid');
  105. $sql1 = "insert into vtiger_profile2utility values ($profile_id,4,7,0)";
  106. $sql2 = "insert into vtiger_profile2utility values ($profile_id,7,9,0)";
  107. Execute($sql1);
  108. Execute($sql2);
  109. }
  110. */
  111. //Insert Values into action mapping table:
  112. $actionmapping_array = Array(
  113. "insert into vtiger_actionmapping values(0,'Save',0)",
  114. "insert into vtiger_actionmapping values(1,'EditView',0)",
  115. "insert into vtiger_actionmapping values(2,'Delete',0)",
  116. "insert into vtiger_actionmapping values(3,'index',0)",
  117. "insert into vtiger_actionmapping values(4,'DetailView',0)",
  118. "insert into vtiger_actionmapping values(5,'Import',0)",
  119. "insert into vtiger_actionmapping values(6,'Export',0)",
  120. "insert into vtiger_actionmapping values(8,'Merge',0)",
  121. "insert into vtiger_actionmapping values(1,'VendorEditView',1)",
  122. "insert into vtiger_actionmapping values(4,'VendorDetailView',1)",
  123. "insert into vtiger_actionmapping values(0,'SaveVendor',1)",
  124. "insert into vtiger_actionmapping values(2,'DeleteVendor',1)",
  125. "insert into vtiger_actionmapping values(1,'PriceBookEditView',1)",
  126. "insert into vtiger_actionmapping values(4,'PriceBookDetailView',1)",
  127. "insert into vtiger_actionmapping values(0,'SavePriceBook',1)",
  128. "insert into vtiger_actionmapping values(2,'DeletePriceBook',1)",
  129. "insert into vtiger_actionmapping values(1,'SalesOrderEditView',1)",
  130. "insert into vtiger_actionmapping values(4,'SalesOrderDetailView',1)",
  131. "insert into vtiger_actionmapping values(0,'SaveSalesOrder',1)",
  132. "insert into vtiger_actionmapping values(2,'DeleteSalesOrder',1)",
  133. "insert into vtiger_actionmapping values(9,'ConvertLead',0)",
  134. "insert into vtiger_actionmapping values(1,'DetailViewAjax',1)",
  135. "insert into vtiger_actionmapping values(1,'QuickCreate',1)",
  136. "insert into vtiger_actionmapping values(4,'TagCloud',1)"
  137. );
  138. foreach($actionmapping_array as $query)
  139. {
  140. Execute($query);
  141. }
  142. //Added two columns in vtiger_field table to construct the quickcreate form dynamically
  143. $alter_array2 = Array(
  144. "ALTER TABLE vtiger_field ADD column quickcreate int(10) after typeofdata",
  145. "ALTER TABLE vtiger_field ADD column quickcreatesequence int(19) after quickcreate",
  146. );
  147. foreach($alter_array2 as $query)
  148. {
  149. Execute($query);
  150. }
  151. $update_array1 = Array(
  152. "UPDATE vtiger_field SET quickcreate = 1,quickcreatesequence = 0",
  153. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 2 and fieldlabel = 'Potential Name'",
  154. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 2 WHERE tabid = 2 and fieldlabel = 'Account Name'",
  155. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 3 WHERE tabid = 2 and fieldlabel = 'Expected Close Date'",
  156. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 4 WHERE tabid = 2 and fieldlabel = 'Sales Stage'",
  157. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 5 WHERE tabid = 2 and fieldlabel = 'Amount'",
  158. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 4 and fieldlabel = 'First Name'",
  159. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 2 WHERE tabid = 4 and fieldlabel = 'Last Name'",
  160. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 3 WHERE tabid = 4 and fieldlabel = 'Account Name'",
  161. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 4 WHERE tabid = 4 and fieldlabel = 'Office Phone'",
  162. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 5 WHERE tabid = 4 and fieldlabel = 'Email'",
  163. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 6 and fieldlabel = 'Account Name'",
  164. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 2 WHERE tabid = 6 and fieldlabel = 'Phone'",
  165. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 3 WHERE tabid = 6 and fieldlabel = 'Website'",
  166. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 7 and fieldlabel = 'First Name'",
  167. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 2 WHERE tabid = 7 and fieldlabel = 'Last Name'",
  168. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 3 WHERE tabid = 7 and fieldlabel = 'Company'",
  169. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 4 WHERE tabid = 7 and fieldlabel = 'Phone'",
  170. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 5 WHERE tabid = 7 and fieldlabel = 'Email'",
  171. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 8 and fieldlabel = 'Subject'",
  172. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 9 and fieldlabel = 'Subject'",
  173. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 2 WHERE tabid = 9 and fieldlabel = 'Start Date & Time'",
  174. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 10 and fieldlabel = 'Subject'",
  175. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 2 WHERE tabid = 10 and fieldlabel = 'Date & Time Sent'",
  176. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 13 and fieldlabel = 'Title'",
  177. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 2 WHERE tabid = 13 and fieldlabel = 'Description'",
  178. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 3 WHERE tabid = 13 and fieldlabel = 'Priority'",
  179. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 14 and fieldlabel = 'Product Name'",
  180. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 2 WHERE tabid = 14 and fieldlabel = 'Product Code'",
  181. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 3 WHERE tabid = 14 and fieldlabel = 'Product Category'",
  182. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 1 WHERE tabid = 16 and fieldlabel = 'Subject'",
  183. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 2 WHERE tabid = 16 and fieldlabel = 'Start Date & Time'",
  184. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 3 WHERE tabid = 16 and fieldlabel = 'Activity Type'",
  185. "UPDATE vtiger_field SET quickcreate = 0,quickcreatesequence = 4 WHERE tabid = 16 and fieldlabel = 'Duration'",
  186. );
  187. foreach($update_array1 as $query)
  188. {
  189. Execute($query);
  190. }
  191. //Added for the "Color By User in Calendar " which has been contributed by Cesar
  192. $alter_query1 = "ALTER TABLE vtiger_users ADD cal_color VARCHAR(25) DEFAULT '#E6FAD8' AFTER user_hash";
  193. Execute($alter_query1);
  194. //code contributed by Fredy for color vtiger_priority
  195. $newfieldid = $conn->getUniqueID("vtiger_field");
  196. $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,'')";
  197. Execute($insert_query1);
  198. //Added on 23-12-2005 which is missed from Fredy's contribution for Color vtiger_priority
  199. populateFieldForSecurity('16',$newfieldid);
  200. $activity_cols = $conn->getColumnNames("vtiger_activity");
  201. if(is_array($activity_cols) && !in_array("priority",$activity_cols))
  202. {
  203. $activity_alter_query = "alter table vtiger_activity add column priority varchar(150) default NULL";
  204. Execute($activity_alter_query);
  205. }
  206. //Code contributed by Raju for better emailing
  207. /*
  208. $insert_array1 = Array(
  209. "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,'')",
  210. "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)",
  211. "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,'')",
  212. "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,'')",
  213. );
  214. */
  215. //commented the above array as that queries are wrong queries -- changed on 23-12-2005
  216. $insert_array1 = array(
  217. "update vtiger_field set uitype='357' where tabid=10 and fieldname='parent_id' and tablename='vtiger_seactivityrel'",
  218. "update vtiger_field set sequence=1 where tabid=10 and fieldname in ('parent_id','subject','filename','description')",
  219. "update vtiger_field set block=2 where tabid=10 and fieldname='parent_id'",
  220. "update vtiger_field set block=3 where tabid=10 and fieldname='subject'",
  221. "update vtiger_field set block=4 where tabid=10 and fieldname='filename'",
  222. "update vtiger_field set block=5 where tabid=10 and fieldname='description'",
  223. );
  224. foreach($insert_array1 as $query)
  225. {
  226. Execute($query);
  227. }
  228. //code contributed by mike to rearrange the home page
  229. $alter_query2 = "alter table vtiger_users add column homeorder varchar(255) default 'ALVT,PLVT,QLTQ,CVLVT,HLT,OLV,GRT,OLTSO,ILTI' after date_format";
  230. Execute($alter_query2);
  231. //Added one column in vtiger_invoice table to include 'Contact Name' vtiger_field in Invoice module
  232. $alter_query3 = "ALTER TABLE vtiger_invoice ADD column contactid int(19) after customerno";
  233. Execute($alter_query3);
  234. $newfieldid = $conn->getUniqueID("vtiger_field");
  235. $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,'')";
  236. Execute($insert_query2);
  237. //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
  238. populateFieldForSecurity('23',$newfieldid);
  239. //changes made to fix the bug in Address Information block of Accounts and Contacs module
  240. $update_array2 = Array(
  241. "UPDATE vtiger_field SET fieldlabel='Billing City', sequence=5 WHERE tabid=6 and fieldname='bill_city'",
  242. "UPDATE vtiger_field SET fieldlabel='Billing State', sequence=7 WHERE tabid=6 and fieldname='bill_state'",
  243. "UPDATE vtiger_field SET fieldlabel='Billing Code', sequence=9 WHERE tabid=6 and fieldname='bill_code'",
  244. "UPDATE vtiger_field SET fieldlabel='Billing Country', sequence=11 WHERE tabid=6 and fieldname='bill_country'",
  245. "UPDATE vtiger_field SET fieldlabel='Shipping City', sequence=6 WHERE tabid=6 and fieldname='ship_city'",
  246. "UPDATE vtiger_field SET fieldlabel='Shipping State', sequence=8 WHERE tabid=6 and fieldname='ship_state'",
  247. "UPDATE vtiger_field SET fieldlabel='Shipping Code', sequence=10 WHERE tabid=6 and fieldname='ship_code'",
  248. "UPDATE vtiger_field SET fieldlabel='Shipping Country', sequence=12 WHERE tabid=6 and fieldname='ship_country'",
  249. "UPDATE vtiger_field SET fieldlabel='Mailing City', sequence=5 WHERE tabid=4 and fieldname='mailingcity'",
  250. "UPDATE vtiger_field SET fieldlabel='Mailing State', sequence=7 WHERE tabid=4 and fieldname='mailingstate'",
  251. "UPDATE vtiger_field SET fieldlabel='Mailing Zip', sequence=9 WHERE tabid=4 and fieldname='mailingzip'",
  252. "UPDATE vtiger_field SET fieldlabel='Mailing Country', sequence=11 WHERE tabid=4 and fieldname='mailingcountry'",
  253. "UPDATE vtiger_field SET fieldlabel='Other City', sequence=6 WHERE tabid=4 and fieldname='othercity'",
  254. "UPDATE vtiger_field SET fieldlabel='Other State', sequence=8 WHERE tabid=4 and fieldname='otherstate'",
  255. "UPDATE vtiger_field SET fieldlabel='Other Zip', sequence=10 WHERE tabid=4 and fieldname='otherzip'",
  256. "UPDATE vtiger_field SET fieldlabel='Other Country', sequence=12 WHERE tabid=4 and fieldname='othercountry'",
  257. );
  258. foreach($update_array2 as $query)
  259. {
  260. Execute($query);
  261. }
  262. //Added vtiger_field emailoptout in vtiger_account table
  263. $newfieldid = $conn->getUniqueID("vtiger_field");
  264. $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,'')";
  265. Execute($insert_query3);
  266. //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
  267. populateFieldForSecurity('6',$newfieldid);
  268. //Added on 22-12-2005
  269. $alter_query4 = "alter table vtiger_account add column emailoptout varchar(3) default 0";
  270. Execute($alter_query4);
  271. $update_array3 = Array(
  272. "update vtiger_field set sequence=18 where tabid=6 and fieldname ='assigned_user_id'",
  273. "update vtiger_field set sequence=19 where tabid=6 and fieldname ='createdtime'",
  274. "update vtiger_field set sequence=19 where tabid=6 and fieldname ='modifiedtime'",
  275. );
  276. foreach($update_array3 as $query)
  277. {
  278. Execute($query);
  279. }
  280. //create table vtiger_moduleowners to assign the module and corresponding owners
  281. $create_query2 = "CREATE TABLE vtiger_moduleowners (
  282. `tabid` int(19) NOT NULL default '0',
  283. `user_id` varchar(11) NOT NULL,
  284. PRIMARY KEY (`tabid`),
  285. KEY `moduleowners_tabid_user_id_idx` (`tabid`,`user_id`)
  286. ) ENGINE=InnoDB";
  287. /*
  288. $create_query2 = "CREATE TABLE vtiger_moduleowners
  289. (
  290. `tabid` int(19) NOT NULL default '0',
  291. `user_id` varchar(11) NOT NULL default '',
  292. PRIMARY KEY (`tabid`),
  293. CONSTRAINT `fk_ModuleOwners` FOREIGN KEY (`tabid`) REFERENCES `vtiger_tab` (`tabid`) ON DELETE CASCADE
  294. ) TYPE=InnoDB";
  295. */
  296. Execute($create_query2);
  297. //Populated the default entries for vtiger_moduleowners which is created newly
  298. $module_array = Array(
  299. 'Potentials',
  300. 'Contacts',
  301. 'Accounts',
  302. 'Leads',
  303. 'Notes',
  304. 'Activities',
  305. 'Emails',
  306. 'HelpDesk',
  307. 'Products',
  308. 'Faq',
  309. 'Vendor',
  310. 'PriceBook',
  311. 'Quotes',
  312. 'Orders',
  313. 'SalesOrder',
  314. 'Invoice',
  315. 'Reports'
  316. );
  317. foreach($module_array as $mod)
  318. {
  319. $query = "insert into vtiger_moduleowners values(".$this->localGetTabID($mod).",1)";
  320. Execute($query);
  321. }
  322. //Changes made to include status vtiger_field in Activity Quickcreate Form
  323. $update_array4 = Array(
  324. "UPDATE vtiger_field SET quickcreate=0,quickcreatesequence=3 WHERE tabid=16 and fieldname='eventstatus'",
  325. "UPDATE vtiger_field SET quickcreate=0,quickcreatesequence=4 WHERE tabid=16 and fieldname='activitytype'",
  326. "UPDATE vtiger_field SET quickcreate=0,quickcreatesequence=5 WHERE tabid=16 and fieldname='duration_hours'",
  327. "UPDATE vtiger_field SET quickcreate=0,quickcreatesequence=3 WHERE tabid=9 and fieldname='taskstatus'",
  328. );
  329. foreach($update_array4 as $query)
  330. {
  331. Execute($query);
  332. }
  333. //Table 'inventory_tandc' added newly to include Inventory Terms &Conditions
  334. $create_query1 = "CREATE TABLE vtiger_inventory_tandc(id INT(19),type VARCHAR(30) NOT NULL,tandc LONGTEXT default NULL,PRIMARY KEY(id))";
  335. Execute($create_query1);
  336. $insert_query4 = "insert into vtiger_inventory_tandc values('".$conn->getUniqueID('vtiger_inventory_tandc')."','Inventory',' ')";
  337. Execute($insert_query4);
  338. /****************** 5.0(Alpha) dev version 1 Database changes -- Ends*********************/
  339. /****************** 5.0(Alpha) dev version 2 Database changes -- Starts*********************/
  340. $query1 = "ALTER TABLE vtiger_leadaddress change lane lane varchar(250)";
  341. Execute($query1);
  342. $rename_table_array1 = Array(
  343. "update vtiger_field set tablename='vtiger_customerdetails' where tabid=4 and fieldname in ('portal','support_start_date','support_end_date')",
  344. "alter table vtiger_PortalInfo drop foreign key fk_PortalInfo",
  345. "rename table vtiger_PortalInfo to vtiger_portalinfo",
  346. "alter table vtiger_portalinfo add CONSTRAINT `fk_portalinfo` FOREIGN KEY (`id`) REFERENCES `vtiger_contactdetails` (`contactid`) ON DELETE CASCADE",
  347. "alter table vtiger_CustomerDetails drop foreign key fk_CustomerDetails",
  348. "rename table vtiger_CustomerDetails to vtiger_customerdetails",
  349. "alter table vtiger_customerdetails add CONSTRAINT `fk_customerdetails` FOREIGN KEY (`customerid`) REFERENCES `vtiger_contactdetails` (`contactid`) ON DELETE CASCADE"
  350. );
  351. foreach($rename_table_array1 as $query)
  352. {
  353. Execute($query);
  354. }
  355. $query2 = "create table vtiger_ownernotify(crmid int(19),smownerid int(19),flag int(3))";
  356. Execute($query2);
  357. //Form the vtiger_role_map_array as vtiger_roleid=>name mapping array
  358. $sql = "select * from vtiger_role";
  359. $res = $conn->query($sql);
  360. $role_map_array = Array();
  361. for($i=0;$i<$conn->num_rows($res);$i++)
  362. {
  363. $roleid = $conn->query_result($res,$i,'roleid');
  364. $name = $conn->query_result($res,$i,'name');
  365. $role_map_array[$roleid] = $name;
  366. }
  367. $conn->println("List of Roles (roleid => name) ==> ");$conn->println($role_map_array);
  368. //echo '<pre> List of vtiger_roles :';print_r($role_map_array);echo '</pre>';
  369. //Before delete the vtiger_role take a backup array for the table vtiger_user2role
  370. $sql = "select * from vtiger_user2role";
  371. $res = $conn->query($sql);
  372. $user2role_array = array();
  373. for($i=0;$i<$conn->num_rows($res);$i++)
  374. {
  375. $userid = $conn->query_result($res,$i,'userid');
  376. $roleid = $conn->query_result($res,$i,'roleid');
  377. $user2role_array[$userid] = $roleid;
  378. }
  379. $conn->println("Users 2 Roles (userid => roleid) ==> ");$conn->println($user2role_array);
  380. //echo '<pre> List of vtiger_user2role : (userid => vtiger_roleid)';print_r($user2role_array);echo '</pre>';
  381. //Delete the vtiger_role entries
  382. $sql = "truncate vtiger_role";
  383. Execute($sql);
  384. $query3 = "alter table vtiger_user2role drop FOREIGN KEY fk_user2role2";
  385. Execute($query3);
  386. //4,5 th are the Extra added queries
  387. $alter_query_array1 = Array(
  388. "alter table vtiger_user2role change roleid roleid varchar(255)",
  389. "alter table vtiger_role2profile change roleid roleid varchar(255)",
  390. "alter table vtiger_role CHANGE roleid roleid varchar(255)",
  391. "alter table vtiger_role2profile drop PRIMARY KEY",
  392. "alter table vtiger_role2profile ADD PRIMARY KEY (roleid,profileid)"
  393. );
  394. foreach($alter_query_array1 as $query)
  395. {
  396. Execute($query);
  397. }
  398. $query4 = "ALTER TABLE vtiger_user2role ADD CONSTRAINT fk_user2role2 FOREIGN KEY (roleid) REFERENCES vtiger_role(roleid) ON DELETE CASCADE";
  399. Execute($query4);
  400. $alter_query_array2 = Array(
  401. "alter table vtiger_role CHANGE name rolename varchar(200)",
  402. "alter table vtiger_role DROP description",
  403. "alter table vtiger_role add parentrole varchar(255)",
  404. "alter table vtiger_role add depth int(19)"
  405. );
  406. foreach($alter_query_array2 as $query)
  407. {
  408. Execute($query);
  409. }
  410. $query5 = "insert into vtiger_role values('H1','Organisation','H1',0)";
  411. Execute($query5);
  412. //include("include/utils/UserInfoUtil.php");
  413. //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
  414. foreach($role_map_array as $roleid => $rolename)
  415. {
  416. $parentRole = 'H1';
  417. if($rolename == 'standard_user')
  418. {
  419. $rs = $conn->query("select * from vtiger_role where rolename='administrator'");
  420. $parentRole = $conn->query_result($rs,0,'roleid');
  421. }
  422. $empty_array = array(""=>"");
  423. $new_role_id = localcreateRole($rolename,$parentRole,$empty_array);
  424. $new_role_map_array[$roleid] = $new_role_id;
  425. }
  426. $conn->println("Roles (oldroleid => newroleid) ==> ");$conn->println($new_role_map_array);
  427. //Before insert the new entry we should remove the old entries -- added on 06-06-06
  428. $user2role_del = "truncate vtiger_user2role";
  429. Execute($user2role_del);
  430. //First we will insert the old values from vtiger_user2role_array to vtiger_user2role table and then update the new vtiger_role id
  431. foreach($user2role_array as $userid => $roleid)
  432. {
  433. $sql = "insert into vtiger_user2role (userid, roleid) values(".$userid.",'".$new_role_map_array[$roleid]."')";
  434. Execute($sql);
  435. }
  436. //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
  437. //Update the vtiger_user2role table with new vtiger_roleid
  438. /*
  439. foreach($new_role_map_array as $old_roleid => $new_roleid)
  440. {
  441. $update_user2role = "update vtiger_user2role set vtiger_roleid='".$new_roleid."' where vtiger_roleid=".$old_roleid;
  442. Execute($update_user2role);
  443. }
  444. */
  445. //Update the vtiger_role2profile table with new vtiger_roleid
  446. foreach($new_role_map_array as $old_roleid => $new_roleid)
  447. {
  448. $update_role2profile = "update vtiger_role2profile set roleid='".$new_roleid."' where roleid=".$old_roleid;
  449. Execute($update_role2profile);
  450. }
  451. //Group Migration:
  452. //Step 1 : form and group_map_array as groupname => description from vtiger_groups table
  453. //Step 2 : form an vtiger_users2group_map_array array as userid => groupname from vtiger_users2group table
  454. //Step 3 : delete all entries from vtiger_groups table and enter new values from group_map_array
  455. //Step 4 : drop the table vtiger_users2group and create new table
  456. //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
  457. //Step 1 : Form the group_map_array as groupname => description
  458. $sql = "select * from vtiger_groups";
  459. $res = $conn->query($sql);
  460. $group_map_array = Array();
  461. for($i=0;$i<$conn->num_rows($res);$i++)
  462. {
  463. $name = $conn->query_result($res,$i,'name');
  464. $desc = $conn->query_result($res,$i,'description');
  465. $group_map_array[$name] = $desc;
  466. }
  467. $conn->println("List of Groups (name => description) ==> ");$conn->println($group_map_array);
  468. //echo '<pre>List of Groups : ';print_r($group_map_array);echo '</pre>';
  469. //Step 2 : form an vtiger_users2group_map_array array as userid => groupname from vtiger_users2group table
  470. $sql = "select * from vtiger_users2group";
  471. $res = $conn->query($sql);
  472. $users2group_map_array = Array();
  473. for($i=0;$i<$conn->num_rows($res);$i++)
  474. {
  475. $groupname = $conn->query_result($res,$i,'groupname');
  476. $userid = $conn->query_result($res,$i,'userid');
  477. $users2group_map_array[$userid] = $groupname;
  478. }
  479. $conn->println("Users 2 Groups (userid => groupname) ==> ");$conn->println($users2group_map_array);
  480. //echo '<pre>List of vtiger_users2group : ';print_r($users2group_map_array);echo '</pre>';
  481. //Step 3 : delete all entries from vtiger_groups table
  482. $sql = "truncate vtiger_groups";
  483. Execute($sql);
  484. $alter_query_array3 = Array(
  485. "alter table vtiger_users2group drop FOREIGN KEY fk_users2group",
  486. "alter table vtiger_leadgrouprelation drop FOREIGN KEY fk_leadgrouprelation2",
  487. "alter table vtiger_activitygrouprelation drop FOREIGN KEY fk_activitygrouprelation2",
  488. "alter table vtiger_ticketgrouprelation drop FOREIGN KEY fk_ticketgrouprelation2",
  489. "alter table vtiger_groups drop PRIMARY KEY"
  490. );
  491. foreach($alter_query_array3 as $query)
  492. {
  493. Execute($query);
  494. }
  495. //2 nd query is the Extra added query
  496. //Adding columns in group table:
  497. $alter_query_array4 = Array(
  498. "alter table vtiger_groups add column groupid int(19) FIRST",
  499. "alter table vtiger_groups change name groupname varchar(100)",
  500. "alter table vtiger_groups ADD PRIMARY KEY (groupid)",
  501. "alter table vtiger_groups add index (groupname)"
  502. );
  503. foreach($alter_query_array4 as $query)
  504. {
  505. Execute($query);
  506. }
  507. //Moved the create table queries for vtiger_group2grouprel, vtiger_group2role, vtiger_group2rs from the end of this block
  508. //Added on 06-06-06
  509. $query8 = "CREATE TABLE vtiger_group2grouprel (
  510. `groupid` int(19) NOT NULL,
  511. `containsgroupid` int(19) NOT NULL,
  512. PRIMARY KEY (`groupid`,`containsgroupid`)
  513. ) ENGINE=InnoDB";
  514. /*
  515. $query8 = "CREATE TABLE vtiger_group2grouprel
  516. (
  517. `groupid` int(19) NOT NULL default '0',
  518. `containsgroupid` int(19) NOT NULL default '0',
  519. PRIMARY KEY (`groupid`,`containsgroupid`),
  520. CONSTRAINT `fk_group2grouprel1` FOREIGN KEY (`groupid`) REFERENCES `vtiger_groups` (`groupid`) ON DELETE CASCADE
  521. ) TYPE=InnoDB";
  522. */
  523. Execute($query8);
  524. //Added on 06-06-06
  525. $query9 = "CREATE TABLE vtiger_group2role (
  526. `groupid` int(19) NOT NULL,
  527. `roleid` varchar(255) NOT NULL,
  528. PRIMARY KEY (`groupid`,`roleid`)
  529. ) ENGINE=InnoDB";
  530. /*
  531. $query9 = "CREATE TABLE vtiger_group2role
  532. (
  533. `groupid` int(19) NOT NULL default '0',
  534. `roleid` varchar(255) NOT NULL default '',
  535. PRIMARY KEY (`groupid`,`roleid`),
  536. CONSTRAINT `fk_group2role1` FOREIGN KEY (`groupid`) REFERENCES `vtiger_groups` (`groupid`) ON DELETE CASCADE
  537. ) TYPE=InnoDB";
  538. */
  539. Execute($query9);
  540. //Added on 06-06-06
  541. $query10 = "CREATE TABLE vtiger_group2rs (
  542. `groupid` int(19) NOT NULL,
  543. `roleandsubid` varchar(255) NOT NULL,
  544. PRIMARY KEY (`groupid`,`roleandsubid`)
  545. ) ENGINE=InnoDB";
  546. /*
  547. $query10 = "CREATE TABLE vtiger_group2rs
  548. (
  549. `groupid` int(19) NOT NULL default '0',
  550. `roleandsubid` varchar(255) NOT NULL default '',
  551. PRIMARY KEY (`groupid`,`roleandsubid`),
  552. CONSTRAINT `fk_group2rs1` FOREIGN KEY (`groupid`) REFERENCES `vtiger_groups` (`groupid`) ON DELETE CASCADE
  553. ) TYPE=InnoDB";
  554. */
  555. Execute($query10);
  556. //Insert all the retrieved old values to the new vtiger_groups table ie., create new vtiger_groups
  557. foreach($group_map_array as $groupname => $description)
  558. {
  559. $empty_array = array(
  560. "groups" => array(""=>""),
  561. "roles" => array(""=>""),
  562. "rs" => array(""=>""),
  563. "users" => array(""=>"")
  564. );
  565. $groupid = createGroup($groupname,$empty_array,$description);
  566. $group_name_id_mapping[$groupname] = $groupid;
  567. }
  568. $conn->println("List of Groups Created (groupname => groupid) ==> ");$conn->println($group_name_id_mapping);
  569. //Copy all mappings in a user2grop table in a array;
  570. //Step 4 : Drop and again create users2group
  571. $query6 = "drop table vtiger_users2group";
  572. Execute($query6);
  573. //Added on 06-06-06
  574. $query7 = "CREATE TABLE vtiger_users2group (
  575. `groupid` int(19) NOT NULL,
  576. `userid` int(19) NOT NULL,
  577. PRIMARY KEY (`groupid`,`userid`),
  578. KEY `users2group_groupname_uerid_idx` (`groupid`,`userid`)
  579. ) ENGINE=InnoDB";
  580. /*
  581. $query7 = "CREATE TABLE vtiger_users2group
  582. (
  583. `groupid` int(19) NOT NULL default '0',
  584. `userid` int(19) NOT NULL default '0',
  585. PRIMARY KEY (`groupid`,`userid`),
  586. CONSTRAINT `fk_users2group1` FOREIGN KEY (`groupid`) REFERENCES `groups` (`groupid`) ON DELETE CASCADE
  587. ) TYPE=InnoDB";
  588. */
  589. Execute($query7);
  590. //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
  591. foreach($users2group_map_array as $userid => $groupname)
  592. {
  593. //$groupid = $conn->query_result($conn->query("select * from vtiger_groups where groupname='".$groupname."'"),0,'groupid');
  594. $sql = "insert into vtiger_users2group (groupid,userid) values(".$group_name_id_mapping[$groupname].",".$userid.")";
  595. Execute($sql);
  596. }
  597. $alter_query_array5 = Array(
  598. "alter table vtiger_leadgrouprelation ADD CONSTRAINT fk_leadgrouprelation2 FOREIGN KEY (groupname) REFERENCES vtiger_groups(groupname) ON DELETE CASCADE",
  599. "ALTER TABLE vtiger_activitygrouprelation ADD CONSTRAINT fk_activitygrouprelation2 FOREIGN KEY (groupname) REFERENCES vtiger_groups(groupname) ON DELETE CASCADE",
  600. "ALTER TABLE vtiger_ticketgrouprelation ADD CONSTRAINT fk_ticketgrouprelation2 FOREIGN KEY (groupname) REFERENCES vtiger_groups(groupname) ON DELETE CASCADE"
  601. );
  602. foreach($alter_query_array5 as $query)
  603. {
  604. Execute($query);
  605. }
  606. //Moved the create table queries for vtiger_group2grouprel, vtiger_group2role, vtiger_group2rs to before creatinf the Group ie., before call the createGroup
  607. /***Added to include decimal places for amount vtiger_field in vtiger_potential table --by Mangai 15-Nov-2005***/
  608. $query11 = "ALTER TABLE vtiger_potential change amount amount decimal(10,2)";
  609. Execute($query11);
  610. /****************** 5.0(Alpha) dev version 2 Database changes -- Ends*********************/
  611. /****************** 5.0(Alpha) dev version 3 Database changes -- Starts*********************/
  612. //Drop the column company_name from vtiger_vendor table ---- modified by Mickie on 18-11-2005
  613. $altersql1 = "alter table vtiger_vendor drop column company_name";
  614. Execute($altersql1);
  615. $altersql2 = "alter table vtiger_vendor change column name vendorname varchar(100) default NULL";
  616. $conn->query($altersql2);
  617. Execute("update vtiger_field set fieldname='vendorname', columnname='vendorname' where tabid=18 and fieldname='name'");
  618. //TODO (check): Remove this company_name entry from the vtiger_field table if it already exists
  619. //Migration for Default Organisation Share -- Added by Don on 20-11-2005
  620. $query1 = "CREATE TABLE vtiger_org_share_action_mapping (
  621. `share_action_id` int(19) NOT NULL default '0',
  622. `share_action_name` varchar(200) NOT NULL default '',
  623. PRIMARY KEY (`share_action_id`,`share_action_name`)
  624. ) TYPE=InnoDB ";
  625. Execute($query1);
  626. $query2 = "CREATE TABLE vtiger_org_share_action2tab (
  627. `share_action_id` int(19) NOT NULL default '0',
  628. `tabid` int(19) NOT NULL default '0',
  629. PRIMARY KEY (`share_action_id`,`tabid`),
  630. CONSTRAINT `fk_org_share_action2tab` FOREIGN KEY (`share_action_id`) REFERENCES `vtiger_org_share_action_mapping` (`share_action_id`) ON DELETE CASCADE
  631. ) TYPE=InnoDB";
  632. Execute($query2);
  633. $query3 = "alter table vtiger_def_org_share add column editstatus int(19)";
  634. Execute($query3);
  635. $query4 = "delete from vtiger_def_org_share where tabid in(8,14,15,18,19)";
  636. Execute($query4);
  637. //Inserting values into org share action mapping
  638. $insert_query_array1 = Array(
  639. "insert into vtiger_org_share_action_mapping values(0,'Public: Read Only')",
  640. "insert into vtiger_org_share_action_mapping values(1,'Public:Read,Create/Edit')",
  641. "insert into vtiger_org_share_action_mapping values(2,'Public: Read, Create/Edit, Delete')",
  642. "insert into vtiger_org_share_action_mapping values(3,'Private')",
  643. "insert into vtiger_org_share_action_mapping values(4,'Hide Details')",
  644. "insert into vtiger_org_share_action_mapping values(5,'Hide Details and Add Events')",
  645. "insert into vtiger_org_share_action_mapping values(6,'Show Details')",
  646. "insert into vtiger_org_share_action_mapping values(7,'Show Details and Add Events')"
  647. );
  648. foreach($insert_query_array1 as $query)
  649. {
  650. Execute($query);
  651. }
  652. //Inserting for all vtiger_tabs
  653. $def_org_tabid=Array(2,4,6,7,9,10,13,16,20,21,22,23,26);
  654. foreach($def_org_tabid as $def_tabid)
  655. {
  656. $insert_query_array2 = Array(
  657. "insert into vtiger_org_share_action2tab values(0,".$def_tabid.")",
  658. "insert into vtiger_org_share_action2tab values(1,".$def_tabid.")",
  659. "insert into vtiger_org_share_action2tab values(2,".$def_tabid.")",
  660. "insert into vtiger_org_share_action2tab values(3,".$def_tabid.")"
  661. );
  662. foreach($insert_query_array2 as $query)
  663. {
  664. Execute($query);
  665. }
  666. }
  667. $insert_query_array3 = Array(
  668. "insert into vtiger_org_share_action2tab values(4,17)",
  669. "insert into vtiger_org_share_action2tab values(5,17)",
  670. "insert into vtiger_org_share_action2tab values(6,17)",
  671. "insert into vtiger_org_share_action2tab values(7,17)"
  672. );
  673. foreach($insert_query_array3 as $query)
  674. {
  675. Execute($query);
  676. }
  677. $query_array1 = Array(
  678. "insert into vtiger_def_org_share values(9,17,7,0)",
  679. "update vtiger_def_org_share set editstatus=0",
  680. "update vtiger_def_org_share set editstatus=2 where tabid=4",
  681. "update vtiger_def_org_share set editstatus=1 where tabid=9",
  682. "update vtiger_def_org_share set editstatus=2 where tabid=16"
  683. );
  684. foreach($query_array1 as $query)
  685. {
  686. Execute($query);
  687. }
  688. /****************** 5.0(Alpha) dev version 3 Database changes -- Ends*********************/
  689. $migrationlog->debug("Database Modifications for 5.0(Alpha) Dev 3 ==> 5.0 Alpha starts here.");
  690. //echo "<br><br><b>Database Modifications for 5.0(Alpha) Dev3 ==> 5.0 Alpha starts here.....</b><br>";
  691. $alter_query_array6 = Array(
  692. "ALTER TABLE vtiger_users ADD column activity_view VARCHAR(25) DEFAULT 'Today' AFTER homeorder",
  693. "ALTER TABLE vtiger_activity ADD column notime VARCHAR(3) NOT NULL DEFAULT '0' AFTER location"
  694. );
  695. foreach($alter_query_array6 as $query)
  696. {
  697. Execute($query);
  698. }
  699. $newfieldid = $conn->getUniqueID("vtiger_field");
  700. $insert_query = "insert into vtiger_field values (9,".$newfieldid.",'notime','activity',1,56,'notime','No Time',1,0,0,100,20,1,3,'C~O',1,'')";
  701. Execute($insert_query);
  702. populateFieldForSecurity('9',$newfieldid);
  703. $newfieldid = $conn->getUniqueID("vtiger_field");
  704. $insert_query = "insert into vtiger_field values (16,".$newfieldid.",'notime','activity',1,56,'notime','No Time',1,0,0,100,18,1,1,'C~O',1,'')";
  705. Execute($insert_query);
  706. populateFieldForSecurity('16',$newfieldid);
  707. $alter_query_array7 = Array(
  708. "alter table vtiger_vendor add column pobox varchar(30) after state",
  709. "alter table vtiger_leadaddress add column pobox varchar(30) after state",
  710. "alter table vtiger_accountbillads add column pobox varchar(30) after state",
  711. "alter table vtiger_accountshipads add column pobox varchar(30) after state",
  712. "alter table vtiger_contactaddress add column mailingpobox varchar(30) after mailingstate",
  713. "alter table vtiger_contactaddress add column otherpobox varchar(30) after otherstate",
  714. "alter table vtiger_quotesbillads add column bill_pobox varchar(30) after bill_street",
  715. "alter table vtiger_quotesshipads add column ship_pobox varchar(30) after ship_street",
  716. "alter table vtiger_pobillads add column bill_pobox varchar(30) after bill_street",
  717. "alter table vtiger_poshipads add column ship_pobox varchar(30) after ship_street",
  718. "alter table vtiger_sobillads add column bill_pobox varchar(30) after bill_street",
  719. "alter table vtiger_soshipads add column ship_pobox varchar(30) after ship_street",
  720. "alter table vtiger_invoicebillads add column bill_pobox varchar(30) after bill_street",
  721. "alter table vtiger_invoiceshipads add column ship_pobox varchar(30) after ship_street"
  722. );
  723. foreach($alter_query_array7 as $query)
  724. {
  725. Execute($query);
  726. }
  727. $newfieldid = $conn->getUniqueID("vtiger_field");
  728. $insert_query = "insert into vtiger_field values (23,".$newfieldid.",'bill_pobox','invoicebillads',1,'1','bill_pobox','Billing Po Box',1,0,0,100,3,2,1,'V~O',1,'')";
  729. Execute($insert_query);
  730. populateFieldForSecurity('23',$newfieldid);
  731. $newfieldid = $conn->getUniqueID("vtiger_field");
  732. $insert_query = "insert into vtiger_field values (23,".$newfieldid.",'ship_pobox','invoiceshipads',1,'1','ship_pobox','Shipping Po Box',1,0,0,100,4,2,1,'V~O',1,'')";
  733. Execute($insert_query);
  734. populateFieldForSecurity('23',$newfieldid);
  735. $newfieldid = $conn->getUniqueID("vtiger_field");
  736. $insert_query = "insert into vtiger_field values (6,".$newfieldid.",'pobox','accountbillads',1,'1','bill_pobox','Billing Po Box',1,0,0,100,3,2,1,'V~O',1,'')";
  737. Execute($insert_query);
  738. populateFieldForSecurity('6',$newfieldid);
  739. $newfieldid = $conn->getUniqueID("vtiger_field");
  740. $insert_query = "insert into vtiger_field values (6,".$newfieldid.",'pobox','accountshipads',1,'1','ship_pobox','Shipping Po Box',1,0,0,100,4,2,1,'V~O',1,'')";
  741. Execute($insert_query);
  742. populateFieldForSecurity('6',$newfieldid);
  743. $newfieldid = $conn->getUniqueID("vtiger_field");
  744. $insert_query = "insert into vtiger_field values (7,".$newfieldid.",'pobox','leadaddress',1,'1','pobox','Po Box',1,0,0,100,2,2,1,'V~O',1,'')";
  745. Execute($insert_query);
  746. populateFieldForSecurity('7',$newfieldid);
  747. $newfieldid = $conn->getUniqueID("vtiger_field");
  748. $insert_query = "insert into vtiger_field values (4,".$newfieldid.",'mailingpobox','contactaddress',1,'1','mailingpobox','Mailing Po Box',1,0,0,100,3,2,1,'V~O',1,'')";
  749. Execute($insert_query);
  750. populateFieldForSecurity('4',$newfieldid);
  751. $newfieldid = $conn->getUniqueID("vtiger_field");
  752. $insert_query = "insert into vtiger_field values (4,".$newfieldid.",'otherpobox','contactaddress',1,'1','otherpobox','Other Po Box',1,0,0,100,4,2,1,'V~O',1,'')";
  753. Execute($insert_query);
  754. populateFieldForSecurity('4',$newfieldid);
  755. $newfieldid = $conn->getUniqueID("vtiger_field");
  756. $insert_query = "insert into vtiger_field values (18,".$newfieldid.",'pobox','vendor',1,'1','pobox','Po Box',1,0,0,100,2,2,1,'V~O',1,'')";
  757. Execute($insert_query);
  758. populateFieldForSecurity('18',$newfieldid);
  759. $newfieldid = $conn->getUniqueID("vtiger_field");
  760. $insert_query = "insert into vtiger_field values (20,".$newfieldid.",'bill_pobox','quotesbillads',1,'1','bill_pobox','Billing Po Box',1,0,0,100,3,2,1,'V~O',1,'')";
  761. Execute($insert_query);
  762. populateFieldForSecurity('20',$newfieldid);
  763. $newfieldid = $conn->getUniqueID("vtiger_field");
  764. $insert_query = "insert into vtiger_field values (20,".$newfieldid.",'ship_pobox','quotesshipads',1,'1','ship_pobox','Shipping Po Box',1,0,0,100,4,2,1,'V~O',1,'')";
  765. Execute($insert_query);
  766. populateFieldForSecurity('20',$newfieldid);
  767. $newfieldid = $conn->getUniqueID("vtiger_field");
  768. $insert_query = "insert into vtiger_field values (21,".$newfieldid.",'bill_pobox','pobillads',1,'1','bill_pobox','Billing Po Box',1,0,0,100,3,2,1,'V~O',1,'')";
  769. Execute($insert_query);
  770. populateFieldForSecurity('21',$newfieldid);
  771. $newfieldid = $conn->getUniqueID("vtiger_field");
  772. $insert_query = "insert into vtiger_field values (21,".$newfieldid.",'ship_pobox','poshipads',1,'1','ship_pobox','Shipping Po Box',1,0,0,100,4,2,1,'V~O',1,'')";
  773. Execute($insert_query);
  774. populateFieldForSecurity('21',$newfieldid);
  775. $newfieldid = $conn->getUniqueID("vtiger_field");
  776. $insert_query = "insert into vtiger_field values (22,".$newfieldid.",'bill_pobox','sobillads',1,'1','bill_pobox','Billing Po Box',1,0,0,100,3,2,1,'V~O',1,'')";
  777. Execute($insert_query);
  778. populateFieldForSecurity('22',$newfieldid);
  779. $newfieldid = $conn->getUniqueID("vtiger_field");
  780. $insert_query = "insert into vtiger_field values (22,".$newfieldid.",'ship_pobox','soshipads',1,'1','ship_pobox','Shipping Po Box',1,0,0,100,4,2,1,'V~O',1,'')";
  781. Execute($insert_query);
  782. populateFieldForSecurity('22',$newfieldid);
  783. $fieldname =array('bill_city','bill_state','bill_code','bill_country','ship_city','ship_state','ship_code','ship_country');
  784. $tablename = array('accountbillads','quotesbillads','pobillads','sobillads','invoicebillads','accountshipads','quotesshipads','poshipads','soshipads','invoiceshipads');
  785. $sequence = array(5,7,9,11,6,8,10,12);
  786. $k = 0;
  787. $n = 0;
  788. for($j = 0;$j < 8;$j++)
  789. {
  790. if($j == 4)
  791. $n = $n+5;
  792. for($i = 0;$i < 5;$i++)
  793. {
  794. $query1 = "update vtiger_field set sequence=".$sequence[$j]." where tablename='".$tablename[$n+$i]."' && fieldname='".$fieldname[$j]."'";
  795. Execute($query1);
  796. }
  797. }
  798. $fieldname = array('code','city','country','state');
  799. $tablename = 'leadaddress';
  800. $sequence = array(3,4,5,6);
  801. for($i = 0;$i < 4;$i++)
  802. {
  803. $query2 = "update vtiger_field set sequence=".$sequence[$i]." where tablename='".$tablename."' && fieldname='".$fieldname[$i]."'";
  804. Execute($query2);
  805. }
  806. $fieldname = array('city','state','postalcode','country');
  807. $tablename = 'vendor';
  808. $sequence = array(3,4,5,6);
  809. for($i = 0;$i < 4;$i++)
  810. {
  811. $query3 = "update vtiger_field set sequence=".$sequence[$i]." where tablename='".$tablename."' && fieldname='".$fieldname[$i]."'";
  812. Execute($query3);
  813. }
  814. $fieldname = array('mailingcity','othercity','mailingstate','otherstate','mailingzip','otherzip','mailingcountry','othercountry');
  815. $tablename = 'contactaddress';
  816. $sequence = array(5,6,7,8,9,10,11,12);
  817. for($i = 0;$i < 8;$i++)
  818. {
  819. $query = "update vtiger_field set sequence=".$sequence[$i]." where tablename='".$tablename."' && fieldname='".$fieldname[$i]."'";
  820. Execute($query);
  821. }
  822. $query_array1 = Array(
  823. "update vtiger_field set tablename='vtiger_crmentity' where tabid=10 and fieldname='description'",
  824. "update vtiger_field set tablename='vtiger_attachments' where tabid=10 and fieldname='filename'",
  825. "drop table vtiger_emails",
  826. "alter table vtiger_activity drop column description",
  827. "update vtiger_field set tablename='vtiger_crmentity' where tabid in (9,16) and fieldname='description'",
  828. "update vtiger_tab set name='PurchaseOrder',tablabel='PurchaseOrder' where tabid=21",
  829. "update vtiger_tab set presence=0 where tabid=22 and name='SalesOrder'",
  830. "delete from vtiger_actionmapping where actionname='SalesOrderDetailView'",
  831. "delete from vtiger_actionmapping where actionname='SalesOrderEditView'",
  832. "delete from vtiger_actionmapping where actionname='SaveSalesOrder'",
  833. "delete from vtiger_actionmapping where actionname='DeleteSalesOrder'",
  834. //"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)",
  835. "alter table vtiger_troubletickets add column filename varchar(50) default NULL after title"
  836. );
  837. foreach($query_array1 as $query)
  838. {
  839. Execute($query);
  840. }
  841. $newfieldid = $conn->getUniqueID("vtiger_field");
  842. $insert_query = "insert into vtiger_field values (13,".$newfieldid.",'filename','vtiger_attachments',1,'61','filename','Attachment',1,0,0,100,12,2,1,'V~O',0,1)";
  843. Execute($insert_query);
  844. populateFieldForSecurity('13',$newfieldid);
  845. $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))";
  846. Execute($create_query3);
  847. $create_query4 = "create table vtiger_parenttabrel(parenttabid int(3) not null, tabid int(3) not null,sequence int(3) not null)";
  848. Execute($create_query4);
  849. $insert_query_array4 = Array(
  850. "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)",
  851. "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) "
  852. );
  853. foreach($insert_query_array4 as $query)
  854. {
  855. Execute($query);
  856. }
  857. $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))";
  858. Execute($create_query5);
  859. $update_query_array1 = Array(
  860. "update vtiger_field set block=2 where tabid=2 and block=5",
  861. "update vtiger_field set block=3 where tabid=2 and block=2",
  862. //"update vtiger_field set block=4 where tabid=4 and block=1",
  863. "update vtiger_field set block=5 where tabid=4 and block=5",
  864. "update vtiger_field set block=6 where tabid=4 and block=4",//Modified on 24-04-06
  865. "update vtiger_field set block=4 where tabid=4 and block=1",
  866. "update vtiger_field set block=7 where tabid=4 and block=2",
  867. "update vtiger_field set block=8 where tabid=4 and block=3",
  868. "update vtiger_field set block=9 where tabid=6 and block=1",
  869. "update vtiger_field set block=10 where tabid=6 and block=5",
  870. "update vtiger_field set block=11 where tabid=6 and block=2",
  871. "update vtiger_field set block=12 where tabid=6 and block=3",
  872. "update vtiger_field set block=13 where tabid=7 and block=1",
  873. "update vtiger_field set block=14 where tabid=7 and block=5",
  874. "update vtiger_field set block=15 where tabid=7 and block=2",
  875. "update vtiger_field set block=16 where tabid=7 and block=3",
  876. "update vtiger_field set block=17 where tabid=8 and block=1",
  877. "update vtiger_field set block=17 where tabid=8 and block=2",
  878. "update vtiger_field set block=18 where tabid=8 and block=3",
  879. "update vtiger_field set block=19 where tabid=9 and block=1",
  880. "update vtiger_field set block=19 where tabid=9 and block=7",
  881. "update vtiger_field set block=20 where tabid=9 and block=2",
  882. "update vtiger_field set block=21 where tabid=10 and block=1",
  883. "update vtiger_field set block=22 where tabid=10 and block=2",
  884. "update vtiger_field set block=23 where tabid=10 and block=3",
  885. "update vtiger_field set block=23 where tabid=10 and block=4",
  886. "update vtiger_field set block=24 where tabid=10 and block=5",
  887. "update vtiger_field set block=25 where tabid=13 and block=1",
  888. "update vtiger_field set block=26 where tabid=13 and block=2",
  889. "update vtiger_field set block=27 where tabid=13 and block=5",
  890. "update vtiger_field set block=28 where tabid=13 and block=3",
  891. "update vtiger_field set block=29 where tabid=13 and block=4",
  892. "update vtiger_field set block=30 where tabid=13 and block=6",
  893. "update vtiger_field set block=31 where tabid=14 and block=1",
  894. "update vtiger_field set block=32 where tabid=14 and block=2",
  895. "update vtiger_field set block=33 where tabid=14 and block=3",
  896. "update vtiger_field set block=34 where tabid=14 and block=5",
  897. "update vtiger_field set block=35 where tabid=14 and block=6",
  898. "update vtiger_field set block=36 where tabid=14 and block=4",
  899. "update vtiger_field set block=37 where tabid=15 and block=1",
  900. "update vtiger_field set block=38 where tabid=15 and block=2",
  901. "update vtiger_field set block=39 where tabid=15 and block=3",
  902. "u…

Large files files are truncated, but you can click here to view the full file