| [ PHPXref.com ] | [ Generated: Sun Jul 20 20:55:40 2008 ] | [ vtiger CRM 4.2.3 ] |
| [ Index ] [ Variables ] [ Functions ] [ Classes ] [ Constants ] [ Statistics ] | ||
[Summary view] [Print] [Text view]
1 <?php 2 3 include ('include/database/PearDatabase.php'); 4 include ('include/utils.php'); 5 6 class DataMigration 7 { 8 var $oldconn; 9 var $newconn; 10 11 function setupDBConnections() 12 { 13 14 require_once('migrator_connection.php'); 15 require_once ('config.php'); 16 17 echo '<br> the mysql host name of the 4.0.1 db is '.$mysql_host_name_old; 18 echo '<br> the mysql port number of the 4.0.1 db is '.$mysql_port_old; 19 echo '<br> the mysql username of the 4.0.1 db is '.$mysql_username_old; 20 echo '<br> the mysql password of the 4.0.1 db is '.$mysql_password_old; 21 22 23 $this->oldconn = new PearDatabase("mysql",$mysql_host_name_old.":".$mysql_port_old,"vtigercrm_4_0_1_bkp",$mysql_username_old,$mysql_password_old); 24 25 $this->oldconn->connect(); 26 27 //$this->newconn = new PearDatabase("mysql",$dbconfig['db_host_name'],"vtigercrm4",$dbconfig['db_user_name'],$dbconfig['db_password']); 28 //$this->newconn->connect(); 29 30 } 31 32 33 function preliminarySteps() 34 { 35 36 echo '------------------------------------- test print -------------------------'; 37 echo '<br>+++++++++++++++++++++++++++++++++++++<br>'; 38 echo '<br><br>'; 39 echo '<br><font color=red><b>++PRELIMINARY STEPS FOR DATA MIGRATION INITIATED++</b></font><br>'; 40 echo '<br><br>'; 41 echo '<br>+++++++++++++++++++++++++++++++++++<br>'; 42 43 echo '<br><font color=green><b>set time limit to 600</b></font><br>'; 44 set_time_limit(0); 45 ini_set("display_errors",'0'); 46 47 } 48 49 function makechanges() 50 { 51 52 //table creation starts 53 54 $sql90="drop table sefaqrel"; 55 echo '<br> '.$sql90 .' <br> '; 56 $this->oldconn->query($sql90); 57 58 //custom Action 59 $sql90 = "CREATE TABLE `customaction` ( 60 `cvid` int(19) default NULL, 61 `subject` varchar(250) NOT NULL default '', 62 `module` varchar(50) NOT NULL default '', 63 `content` longtext, 64 KEY `customaction_IDX0` (`cvid`) 65 ) TYPE=InnoDB"; 66 echo '<br> '.$sql90 .' <br> '; 67 $this->oldconn->query($sql90); 68 69 70 //convertleadmapping 71 72 $sql90="CREATE TABLE `convertleadmapping` ( 73 `cfmid` int(19) NOT NULL auto_increment, 74 `leadfid` int(19) NOT NULL default '0', 75 `accountfid` int(19) default NULL, 76 `contactfid` int(19) default NULL, 77 `potentialfid` int(19) default NULL, 78 PRIMARY KEY (`cfmid`) 79 ) TYPE=InnoDB"; 80 81 echo '<br> '.$sql90 .' <br> '; 82 $this->oldconn->query($sql90); 83 84 85 86 87 $sql90="CREATE TABLE `CustomerDetails` ( 88 `customerid` int(19) NOT NULL default '0', 89 `portal` char(3) default NULL, 90 `support_start_date` date default NULL, 91 `support_end_date` date default NULL, 92 PRIMARY KEY (`customerid`), 93 CONSTRAINT `fk_CustomerDetails` FOREIGN KEY (`customerid`) REFERENCES `contactdetails` (`contactid`) ON DELETE CASCADE 94 ) TYPE=InnoDB"; 95 96 echo '<br> '.$sql90 .' <br> '; 97 $this->oldconn->query($sql90); 98 99 100 $sql90="CREATE TABLE `PortalInfo` ( 101 `id` int(11) NOT NULL default '0', 102 `user_name` varchar(50) default NULL, 103 `user_password` varchar(30) default NULL, 104 `type` varchar(5) default NULL, 105 `last_login_time` datetime NOT NULL default '0000-00-00 00:00:00', 106 `login_time` datetime NOT NULL default '0000-00-00 00:00:00', 107 `logout_time` datetime NOT NULL default '0000-00-00 00:00:00', 108 `isactive` int(1) default NULL, 109 PRIMARY KEY (`id`), 110 CONSTRAINT `fk_PortalInfo` FOREIGN KEY (`id`) REFERENCES `contactdetails` (`contactid`) ON DELETE CASCADE 111 ) TYPE=InnoDB"; 112 113 echo '<br> '.$sql90 .' <br> '; 114 $this->oldconn->query($sql90); 115 116 117 $sql90="CREATE TABLE `activity_reminder` ( 118 `activity_id` int(11) NOT NULL default '0', 119 `reminder_time` int(11) NOT NULL default '0', 120 `reminder_sent` int(2) NOT NULL default '0', 121 `recurringid` int(19) NOT NULL default '0' 122 ) TYPE=InnoDB"; 123 124 echo '<br> '.$sql90 .' <br> '; 125 $this->oldconn->query($sql90); 126 127 $sql90="CREATE TABLE `carrier` ( 128 `carrierid` int(19) NOT NULL auto_increment, 129 `carrier` varchar(200) NOT NULL default '', 130 `sortorderid` int(19) NOT NULL default '0', 131 `presence` int(1) NOT NULL default '1', 132 PRIMARY KEY (`carrierid`), 133 UNIQUE KEY `carrier_UK0` (`carrier`) 134 ) TYPE=InnoDB"; 135 136 echo '<br> '.$sql90 .' <br> '; 137 $this->oldconn->query($sql90); 138 139 /* 140 $sql90=" CREATE TABLE `currency_info` ( 141 `currency_name` varchar(100) NOT NULL default '', 142 `currency_code` varchar(100) default NULL, 143 `currency_symbol` varchar(30) default NULL, 144 PRIMARY KEY (`currency_name`) 145 ) TYPE=InnoDB"; 146 147 echo '<br> '.$sql90 .' <br> '; 148 $this->oldconn->query($sql90); 149 */ 150 151 152 $sql90="CREATE TABLE `customview` ( 153 `cvid` int(19) NOT NULL default '0', 154 `viewname` varchar(100) NOT NULL default '', 155 `setdefault` int(1) default '0', 156 `setmetrics` int(1) default '0', 157 `entitytype` varchar(100) NOT NULL default '', 158 PRIMARY KEY (`cvid`) 159 ) TYPE=InnoDB"; 160 161 echo '<br> '.$sql90 .' <br> '; 162 $this->oldconn->query($sql90); 163 164 165 $sql90="CREATE TABLE `customview_seq` ( 166 `id` int(11) NOT NULL default '0' 167 ) TYPE=InnoDB"; 168 169 echo '<br> '.$sql90 .' <br> '; 170 $this->oldconn->query($sql90); 171 172 $sql90="CREATE TABLE `cvadvfilter` ( 173 `cvid` int(19) default NULL, 174 `columnindex` int(11) NOT NULL default '0', 175 `columnname` varchar(250) default '', 176 `comparator` varchar(10) default '', 177 `value` varchar(200) default '', 178 KEY `cvadvfilter_IDX0` (`cvid`), 179 CONSTRAINT `cvadvfilter_FK1` FOREIGN KEY (`cvid`) REFERENCES `customview` (`cvid`) ON DELETE CASCADE 180 ) TYPE=InnoDB"; 181 182 echo '<br> '.$sql90 .' <br> '; 183 $this->oldconn->query($sql90); 184 185 $sql90="CREATE TABLE `cvcolumnlist` ( 186 `cvid` int(19) default NULL, 187 `columnindex` int(11) NOT NULL default '0', 188 `columnname` varchar(250) default '', 189 KEY `cvcolumnlist_IDX0` (`cvid`), 190 CONSTRAINT `cvcolumnlist_FK1` FOREIGN KEY (`cvid`) REFERENCES `customview` (`cvid`) ON DELETE CASCADE 191 ) TYPE=InnoDB"; 192 193 echo '<br> '.$sql90 .' <br> '; 194 $this->oldconn->query($sql90); 195 196 $sql90="CREATE TABLE `cvstdfilter` ( 197 `cvid` int(19) default NULL, 198 `columnname` varchar(250) default '', 199 `stdfilter` varchar(250) default '', 200 `startdate` date default '0000-00-00', 201 `enddate` date default '0000-00-00', 202 KEY `cvstdfilter_IDX0` (`cvid`), 203 CONSTRAINT `cvstdfilter_FK1` FOREIGN KEY (`cvid`) REFERENCES `customview` (`cvid`) ON DELETE CASCADE 204 ) TYPE=InnoDB"; 205 206 echo '<br> '.$sql90 .' <br> '; 207 $this->oldconn->query($sql90); 208 209 $sql90="CREATE TABLE `glacct` ( 210 `glacctid` int(19) NOT NULL auto_increment, 211 `glacct` varchar(200) NOT NULL default '', 212 `sortorderid` int(19) NOT NULL default '0', 213 `presence` int(1) NOT NULL default '1', 214 PRIMARY KEY (`glacctid`), 215 UNIQUE KEY `GlAcct_UK0` (`glacct`) 216 ) TYPE=InnoDB"; 217 218 echo '<br> '.$sql90 .' <br> '; 219 $this->oldconn->query($sql90); 220 221 222 $sql90="CREATE TABLE `invoice` ( 223 `invoiceid` int(19) NOT NULL default '0', 224 `subject` varchar(100) default NULL, 225 `salesorderid` int(19) default NULL, 226 `customerno` varchar(100) default NULL, 227 `notes` varchar(100) default NULL, 228 `invoicedate` date default NULL, 229 `duedate` date default NULL, 230 `invoiceterms` varchar(100) default NULL, 231 `type` varchar(100) default NULL, 232 `salestax` decimal(11,3) default NULL, 233 `adjustment` decimal(11,3) default NULL, 234 `salescommission` decimal(11,3) default NULL, 235 `exciseduty` decimal(11,3) default NULL, 236 `subtotal` decimal(11,3) default NULL, 237 `total` decimal(11,3) default NULL, 238 `shipping` varchar(100) default NULL, 239 `accountid` int(19) default NULL, 240 `terms_conditions` longtext, 241 `purchaseorder` varchar(200) default NULL, 242 `invoicestatus` varchar(200) default NULL, 243 PRIMARY KEY (`invoiceid`), 244 CONSTRAINT `fk_Invoice1` FOREIGN KEY (`invoiceid`) REFERENCES `crmentity` (`crmid`) ON DELETE CASCADE 245 ) TYPE=InnoDB"; 246 247 echo '<br> '.$sql90 .' <br> '; 248 $this->oldconn->query($sql90); 249 250 $sql90="CREATE TABLE `invoicebillads` ( 251 `invoicebilladdressid` int(19) NOT NULL default '0', 252 `bill_city` varchar(30) default NULL, 253 `bill_code` varchar(30) default NULL, 254 `bill_country` varchar(30) default NULL, 255 `bill_state` varchar(30) default NULL, 256 `bill_street` varchar(250) default NULL, 257 PRIMARY KEY (`invoicebilladdressid`), 258 CONSTRAINT `fk_InvoiceBillAds` FOREIGN KEY (`invoicebilladdressid`) REFERENCES `invoice` (`invoiceid`) ON DELETE CASCADE 259 ) TYPE=InnoDB"; 260 261 echo '<br> '.$sql90 .' <br> '; 262 $this->oldconn->query($sql90); 263 264 $sql90="CREATE TABLE `invoicecf` ( 265 `invoiceid` int(19) NOT NULL default '0', 266 PRIMARY KEY (`invoiceid`), 267 CONSTRAINT `fk_InvoiceCF` FOREIGN KEY (`invoiceid`) REFERENCES `invoice` (`invoiceid`) ON DELETE CASCADE 268 ) TYPE=InnoDB"; 269 270 echo '<br> '.$sql90 .' <br> '; 271 $this->oldconn->query($sql90); 272 273 $sql90="CREATE TABLE `invoiceproductrel` ( 274 `invoiceid` int(19) NOT NULL default '0', 275 `productid` int(19) NOT NULL default '0', 276 `quantity` int(19) default NULL, 277 `listprice` decimal(11,3) default NULL, 278 PRIMARY KEY (`invoiceid`,`productid`), 279 KEY `InvoiceProductRel_IDX1` (`productid`), 280 CONSTRAINT `fk_InvoiceProductRel2` FOREIGN KEY (`productid`) REFERENCES `products` (`productid`) ON DELETE CASCADE, 281 CONSTRAINT `fk_InvoiceProductRel` FOREIGN KEY (`invoiceid`) REFERENCES `invoice` (`invoiceid`) ON DELETE CASCADE 282 ) TYPE=InnoDB"; 283 284 echo '<br> '.$sql90 .' <br> '; 285 $this->oldconn->query($sql90); 286 287 288 289 290 $sql90=" CREATE TABLE `invoiceshipads` ( 291 `invoiceshipaddressid` int(19) NOT NULL default '0', 292 `ship_city` varchar(30) default NULL, 293 `ship_code` varchar(30) default NULL, 294 `ship_country` varchar(30) default NULL, 295 `ship_state` varchar(30) default NULL, 296 `ship_street` varchar(250) default NULL, 297 PRIMARY KEY (`invoiceshipaddressid`), 298 CONSTRAINT `fk_InvoiceShipAds` FOREIGN KEY (`invoiceshipaddressid`) REFERENCES `invoice` (`invoiceid`) ON DELETE CASCADE 299 ) TYPE=InnoDB"; 300 echo '<br> '.$sql90 .' <br> '; 301 $this->oldconn->query($sql90); 302 303 304 305 306 307 308 309 $sql90="CREATE TABLE `mail_accounts` ( 310 `account_id` int(11) NOT NULL default '0', 311 `user_id` int(11) NOT NULL default '0', 312 `display_name` varchar(50) default NULL, 313 `mail_id` varchar(50) default NULL, 314 `account_name` varchar(50) default NULL, 315 `mail_protocol` varchar(20) default NULL, 316 `mail_username` varchar(50) NOT NULL default '', 317 `mail_password` varchar(20) NOT NULL default '', 318 `mail_servername` varchar(50) default NULL, 319 `status` varchar(10) default NULL, 320 `set_default` int(2) default NULL, 321 PRIMARY KEY (`account_id`) 322 ) TYPE=InnoDB"; 323 echo '<br> '.$sql90 .' <br> '; 324 $this->oldconn->query($sql90); 325 326 327 328 329 330 331 332 $sql90="CREATE TABLE `organizationdetails` ( 333 `organizationame` varchar(60) default NULL, 334 `address` varchar(150) default NULL, 335 `city` varchar(100) default NULL, 336 `state` varchar(100) default NULL, 337 `country` varchar(100) default NULL, 338 `code` varchar(30) default NULL, 339 `phone` varchar(30) default NULL, 340 `fax` varchar(30) default NULL, 341 `website` varchar(50) default NULL, 342 `logoname` varchar(50) default NULL, 343 `logo` longtext 344 ) TYPE=InnoDB"; 345 echo '<br> '.$sql90 .' <br> '; 346 $this->oldconn->query($sql90); 347 348 349 350 351 $sql90="CREATE TABLE `purchaseorder` ( 352 `purchaseorderid` int(19) NOT NULL default '0', 353 `subject` varchar(100) default NULL, 354 `quoteid` int(19) default NULL, 355 `vendorid` int(19) default NULL, 356 `requisition_no` varchar(100) default NULL, 357 `tracking_no` varchar(100) default NULL, 358 `contactid` int(19) default NULL, 359 `duedate` date default NULL, 360 `carrier` varchar(100) default NULL, 361 `type` varchar(100) default NULL, 362 `salestax` decimal(11,3) default NULL, 363 `adjustment` decimal(11,3) default NULL, 364 `salescommission` decimal(11,3) default NULL, 365 `exciseduty` decimal(11,3) default NULL, 366 `total` decimal(11,3) default NULL, 367 `subtotal` decimal(11,3) default NULL, 368 `terms_conditions` longtext, 369 `postatus` varchar(200) default NULL, 370 PRIMARY KEY (`purchaseorderid`), 371 CONSTRAINT `fk_PO1` FOREIGN KEY (`purchaseorderid`) REFERENCES `crmentity` (`crmid`) ON DELETE CASCADE 372 ) TYPE=InnoDB"; 373 echo '<br> '.$sql90 .' <br> '; 374 $this->oldconn->query($sql90); 375 376 377 $sql90="CREATE TABLE `purchaseordercf` ( 378 `purchaseorderid` int(19) NOT NULL default '0', 379 PRIMARY KEY (`purchaseorderid`), 380 CONSTRAINT `fk_PoCF` FOREIGN KEY (`purchaseorderid`) REFERENCES `purchaseorder` (`purchaseorderid`) ON DELETE CASCADE 381 ) TYPE=InnoDB"; 382 echo '<br> '.$sql90 .' <br> '; 383 $this->oldconn->query($sql90); 384 385 386 $sql90="CREATE TABLE `quotes` ( 387 `quoteid` int(19) NOT NULL default '0', 388 `subject` varchar(100) default NULL, 389 `potentialid` int(19) default NULL, 390 `quotestage` varchar(200) default NULL, 391 `validtill` date default NULL, 392 `team` varchar(200) default NULL, 393 `contactid` int(19) default NULL, 394 `currency` varchar(100) default NULL, 395 `subtotal` decimal(11,3) default NULL, 396 `carrier` varchar(100) default NULL, 397 `shipping` varchar(100) default NULL, 398 `inventorymanager` int(19) default NULL, 399 `type` varchar(100) default NULL, 400 `tax` decimal(11,3) default NULL, 401 `adjustment` decimal(11,3) default NULL, 402 `total` decimal(11,3) default NULL, 403 `accountid` int(19) default NULL, 404 `terms_conditions` longtext, 405 PRIMARY KEY (`quoteid`), 406 CONSTRAINT `fk_Quotes1` FOREIGN KEY (`quoteid`) REFERENCES `crmentity` (`crmid`) ON DELETE CASCADE 407 ) TYPE=InnoDB"; 408 echo '<br> '.$sql90 .' <br> '; 409 $this->oldconn->query($sql90); 410 411 412 413 $sql90="CREATE TABLE `quotesbillads` ( 414 `quotebilladdressid` int(19) NOT NULL default '0', 415 `bill_city` varchar(30) default NULL, 416 `bill_code` varchar(30) default NULL, 417 `bill_country` varchar(30) default NULL, 418 `bill_state` varchar(30) default NULL, 419 `bill_street` varchar(250) default NULL, 420 PRIMARY KEY (`quotebilladdressid`), 421 CONSTRAINT `fk_QuotesBillAds` FOREIGN KEY (`quotebilladdressid`) REFERENCES `quotes` (`quoteid`) ON DELETE CASCADE 422 ) TYPE=InnoDB"; 423 echo '<br> '.$sql90 .' <br> '; 424 $this->oldconn->query($sql90); 425 426 427 428 $sql90="CREATE TABLE `quotescf` ( 429 `quoteid` int(19) NOT NULL default '0', 430 PRIMARY KEY (`quoteid`), 431 CONSTRAINT `fk_QuotesCF` FOREIGN KEY (`quoteid`) REFERENCES `quotes` (`quoteid`) ON DELETE CASCADE 432 ) TYPE=InnoDB"; 433 echo '<br> '.$sql90 .' <br> '; 434 $this->oldconn->query($sql90); 435 436 437 438 439 440 441 $sql90="CREATE TABLE `pobillads` ( 442 `pobilladdressid` int(19) NOT NULL default '0', 443 `bill_city` varchar(30) default NULL, 444 `bill_code` varchar(30) default NULL, 445 `bill_country` varchar(30) default NULL, 446 `bill_state` varchar(30) default NULL, 447 `bill_street` varchar(250) default NULL, 448 PRIMARY KEY (`pobilladdressid`), 449 CONSTRAINT `fk_PoBillAds` FOREIGN KEY (`pobilladdressid`) REFERENCES `purchaseorder` (`purchaseorderid`) ON DELETE CASCADE 450 ) TYPE=InnoDB"; 451 echo '<br> '.$sql90 .' <br> '; 452 $this->oldconn->query($sql90); 453 454 455 456 457 458 459 460 $sql90="CREATE TABLE `poproductrel` ( 461 `purchaseorderid` int(19) NOT NULL default '0', 462 `productid` int(19) NOT NULL default '0', 463 `quantity` int(19) default NULL, 464 `listprice` decimal(11,3) default NULL, 465 PRIMARY KEY (`purchaseorderid`,`productid`), 466 KEY `PoProductRel_IDX1` (`productid`), 467 CONSTRAINT `fk_PoProductRel2` FOREIGN KEY (`productid`) REFERENCES `products` (`productid`) ON DELETE CASCADE, 468 CONSTRAINT `fk_PoProductRel` FOREIGN KEY (`purchaseorderid`) REFERENCES `purchaseorder` (`purchaseorderid`) ON DELETE CASCADE 469 ) TYPE=InnoDB"; 470 echo '<br> '.$sql90 .' <br> '; 471 $this->oldconn->query($sql90); 472 473 474 475 476 477 478 479 $sql90="CREATE TABLE `poshipads` ( 480 `poshipaddressid` int(19) NOT NULL default '0', 481 `ship_city` varchar(30) default NULL, 482 `ship_code` varchar(30) default NULL, 483 `ship_country` varchar(30) default NULL, 484 `ship_state` varchar(30) default NULL, 485 `ship_street` varchar(250) default NULL, 486 PRIMARY KEY (`poshipaddressid`), 487 CONSTRAINT `fk_PoShipAds` FOREIGN KEY (`poshipaddressid`) REFERENCES `purchaseorder` (`purchaseorderid`) ON DELETE CASCADE 488 ) TYPE=InnoDB"; 489 echo '<br> '.$sql90 .' <br> '; 490 $this->oldconn->query($sql90); 491 492 493 494 495 496 497 498 $sql90="CREATE TABLE `pricebook` ( 499 `pricebookid` int(19) NOT NULL default '0', 500 `bookname` varchar(100) default NULL, 501 `active` int(1) default NULL, 502 `description` longtext, 503 PRIMARY KEY (`pricebookid`), 504 CONSTRAINT `fk_PriceBook` FOREIGN KEY (`pricebookid`) REFERENCES `crmentity` (`crmid`) ON DELETE CASCADE 505 ) TYPE=InnoDB"; 506 echo '<br> '.$sql90 .' <br> '; 507 $this->oldconn->query($sql90); 508 509 510 511 512 513 514 515 $sql90="CREATE TABLE `pricebookcf` ( 516 `pricebookid` int(19) NOT NULL default '0', 517 PRIMARY KEY (`pricebookid`), 518 CONSTRAINT `fk_PriceBookCF` FOREIGN KEY (`pricebookid`) REFERENCES `pricebook` (`pricebookid`) ON DELETE CASCADE 519 ) TYPE=InnoDB"; 520 echo '<br> '.$sql90 .' <br> '; 521 $this->oldconn->query($sql90); 522 523 524 525 526 527 528 529 $sql90="CREATE TABLE `pricebookproductrel` ( 530 `pricebookid` int(19) NOT NULL default '0', 531 `productid` int(19) NOT NULL default '0', 532 `listprice` decimal(11,3) default NULL, 533 PRIMARY KEY (`pricebookid`,`productid`), 534 KEY `PriceBookProductRel_IDX0` (`pricebookid`), 535 KEY `PriceBookProductRel_IDX1` (`productid`), 536 CONSTRAINT `fk_PriceBookProductRel2` FOREIGN KEY (`productid`) REFERENCES `products` (`productid`) ON DELETE CASCADE, 537 CONSTRAINT `fk_PriceBookProductRel` FOREIGN KEY (`pricebookid`) REFERENCES `pricebook` (`pricebookid`) ON DELETE CASCADE 538 ) TYPE=InnoDB"; 539 echo '<br> '.$sql90 .' <br> '; 540 $this->oldconn->query($sql90); 541 542 543 544 545 546 547 548 $sql90="CREATE TABLE `productimage` ( 549 `productid` int(19) default NULL, 550 `imagename` varchar(150) default NULL, 551 `product_img` longtext 552 ) TYPE=InnoDB"; 553 echo '<br> '.$sql90 .' <br> '; 554 $this->oldconn->query($sql90); 555 556 557 558 559 560 561 562 $sql90="CREATE TABLE `quotesproductrel` ( 563 `quoteid` int(19) NOT NULL default '0', 564 `productid` int(19) NOT NULL default '0', 565 `quantity` int(19) default NULL, 566 `listprice` decimal(11,3) default NULL, 567 PRIMARY KEY (`quoteid`,`productid`), 568 KEY `QuotesProductRel_IDX0` (`quoteid`), 569 KEY `QuotesProductRel_IDX1` (`productid`), 570 CONSTRAINT `fk_QuotesProductRel2` FOREIGN KEY (`productid`) REFERENCES `products` (`productid`) ON DELETE CASCADE, 571 CONSTRAINT `fk_QuotesProductRel` FOREIGN KEY (`quoteid`) REFERENCES `quotes` (`quoteid`) ON DELETE CASCADE 572 ) TYPE=InnoDB"; 573 echo '<br> '.$sql90 .' <br> '; 574 $this->oldconn->query($sql90); 575 576 577 578 $sql90="CREATE TABLE `quotesshipads` ( 579 `quoteshipaddressid` int(19) NOT NULL default '0', 580 `ship_city` varchar(30) default NULL, 581 `ship_code` varchar(30) default NULL, 582 `ship_country` varchar(30) default NULL, 583 `ship_state` varchar(30) default NULL, 584 `ship_street` varchar(250) default NULL, 585 PRIMARY KEY (`quoteshipaddressid`), 586 CONSTRAINT `fk_QuotesShipAds` FOREIGN KEY (`quoteshipaddressid`) REFERENCES `quotes` (`quoteid`) ON DELETE CASCADE 587 ) TYPE=InnoDB"; 588 echo '<br> '.$sql90 .' <br> '; 589 $this->oldconn->query($sql90); 590 591 592 593 $sql90="CREATE TABLE `quotestage` ( 594 `quotestageid` int(19) NOT NULL auto_increment, 595 `quotestage` varchar(200) NOT NULL default '', 596 `sortorderid` int(19) NOT NULL default '0', 597 `presence` int(1) NOT NULL default '1', 598 PRIMARY KEY (`quotestageid`), 599 UNIQUE KEY `quotestage_UK0` (`quotestage`) 600 ) TYPE=InnoDB"; 601 echo '<br> '.$sql90 .' <br> '; 602 $this->oldconn->query($sql90); 603 604 605 606 607 608 $sql90="CREATE TABLE `salesorder` ( 609 `salesorderid` int(19) NOT NULL default '0', 610 `subject` varchar(100) default NULL, 611 `potentialid` int(19) default NULL, 612 `customerno` varchar(100) default NULL, 613 `quoteid` int(19) default NULL, 614 `vendorterms` varchar(100) default NULL, 615 `contactid` int(19) default NULL, 616 `vendorid` int(19) default NULL, 617 `duedate` date default NULL, 618 `carrier` varchar(100) default NULL, 619 `pending` varchar(200) default NULL, 620 `type` varchar(100) default NULL, 621 `salestax` decimal(11,3) default NULL, 622 `adjustment` decimal(11,3) default NULL, 623 `salescommission` decimal(11,3) default NULL, 624 `exciseduty` decimal(11,3) default NULL, 625 `total` decimal(11,3) default NULL, 626 `subtotal` decimal(11,3) default NULL, 627 `accountid` int(19) default NULL, 628 `terms_conditions` longtext, 629 `purchaseorder` varchar(200) default NULL, 630 `sostatus` varchar(200) default NULL, 631 PRIMARY KEY (`salesorderid`), 632 CONSTRAINT `fk_SO1` FOREIGN KEY (`salesorderid`) REFERENCES `crmentity` (`crmid`) ON DELETE CASCADE 633 ) TYPE=InnoDB"; 634 echo '<br> '.$sql90 .' <br> '; 635 $this->oldconn->query($sql90); 636 637 638 $sql90="CREATE TABLE `salesordercf` ( 639 `salesorderid` int(19) NOT NULL default '0', 640 PRIMARY KEY (`salesorderid`), 641 CONSTRAINT `fk_SoCF` FOREIGN KEY (`salesorderid`) REFERENCES `salesorder` (`salesorderid`) ON DELETE CASCADE 642 ) TYPE=InnoDB"; 643 echo '<br> '.$sql90 .' <br> '; 644 $this->oldconn->query($sql90); 645 646 647 648 649 $sql90="CREATE TABLE `selectquery` ( 650 `queryid` int(19) NOT NULL default '0', 651 `startindex` int(19) default '0', 652 `numofobjects` int(19) default '0', 653 PRIMARY KEY (`queryid`) 654 ) TYPE=InnoDB"; 655 echo '<br> '.$sql90 .' <br> '; 656 $this->oldconn->query($sql90); 657 658 659 660 661 662 663 $sql90=" CREATE TABLE `selectquery_seq` ( 664 `id` int(11) NOT NULL default '0' 665 ) TYPE=InnoDB"; 666 echo '<br> '.$sql90 .' <br> '; 667 $this->oldconn->query($sql90); 668 669 670 671 672 673 674 $sql90="CREATE TABLE `selectcolumn` ( 675 `queryid` int(19) default NULL, 676 `columnindex` int(11) NOT NULL default '0', 677 `columnname` varchar(250) default '', 678 KEY `selectcolumn_IDX0` (`queryid`), 679 CONSTRAINT `selectcolumn_FK1` FOREIGN KEY (`queryid`) REFERENCES `selectquery` (`queryid`) ON DELETE CASCADE 680 ) TYPE=InnoDB"; 681 echo '<br> '.$sql90 .' <br> '; 682 $this->oldconn->query($sql90); 683 684 685 686 687 688 689 $sql90="CREATE TABLE `sobillads` ( 690 `sobilladdressid` int(19) NOT NULL default '0', 691 `bill_city` varchar(30) default NULL, 692 `bill_code` varchar(30) default NULL, 693 `bill_country` varchar(30) default NULL, 694 `bill_state` varchar(30) default NULL, 695 `bill_street` varchar(250) default NULL, 696 PRIMARY KEY (`sobilladdressid`), 697 CONSTRAINT `fk_SoBillAds` FOREIGN KEY (`sobilladdressid`) REFERENCES `salesorder` (`salesorderid`) ON DELETE CASCADE 698 ) TYPE=InnoDB"; 699 echo '<br> '.$sql90 .' <br> '; 700 $this->oldconn->query($sql90); 701 702 703 704 705 706 707 $sql90="CREATE TABLE `soproductrel` ( 708 `salesorderid` int(19) NOT NULL default '0', 709 `productid` int(19) NOT NULL default '0', 710 `quantity` int(19) default NULL, 711 `listprice` decimal(11,3) default NULL, 712 PRIMARY KEY (`salesorderid`,`productid`), 713 KEY `SoProductRel_IDX0` (`salesorderid`), 714 KEY `SoProductRel_IDX1` (`productid`), 715 CONSTRAINT `fk_SoProductRel2` FOREIGN KEY (`productid`) REFERENCES `products` (`productid`) ON DELETE CASCADE, 716 CONSTRAINT `fk_SoProductRel` FOREIGN KEY (`salesorderid`) REFERENCES `salesorder` (`salesorderid`) ON DELETE CASCADE 717 ) TYPE=InnoDB"; 718 echo '<br> '.$sql90 .' <br> '; 719 $this->oldconn->query($sql90); 720 721 722 723 724 725 726 $sql90="CREATE TABLE `soshipads` ( 727 `soshipaddressid` int(19) NOT NULL default '0', 728 `ship_city` varchar(30) default NULL, 729 `ship_code` varchar(30) default NULL, 730 `ship_country` varchar(30) default NULL, 731 `ship_state` varchar(30) default NULL, 732 `ship_street` varchar(250) default NULL, 733 PRIMARY KEY (`soshipaddressid`), 734 CONSTRAINT `fk_SoShipAds` FOREIGN KEY (`soshipaddressid`) REFERENCES `salesorder` (`salesorderid`) ON DELETE CASCADE 735 ) TYPE=InnoDB"; 736 echo '<br> '.$sql90 .' <br> '; 737 $this->oldconn->query($sql90); 738 739 740 741 742 743 744 745 746 747 $sql90="CREATE TABLE `recurringtype` ( 748 `recurringeventid` int(19) NOT NULL auto_increment, 749 `recurringtype` varchar(200) NOT NULL default '', 750 `sortorderid` int(19) NOT NULL default '0', 751 `presence` int(1) NOT NULL default '1', 752 PRIMARY KEY (`recurringeventid`) 753 ) TYPE=InnoDB"; 754 echo '<br> '.$sql90 .' <br> '; 755 $this->oldconn->query($sql90); 756 757 758 759 760 $sql90="CREATE TABLE `recurringevents` ( 761 `recurringid` int(19) NOT NULL auto_increment, 762 `activityid` int(19) NOT NULL default '0', 763 `recurringdate` date default NULL, 764 `recurringtype` varchar(30) default NULL, 765 PRIMARY KEY (`recurringid`) 766 ) TYPE=InnoDB"; 767 echo '<br> '.$sql90 .' <br> '; 768 $this->oldconn->query($sql90); 769 770 771 772 $sql90="CREATE TABLE `reportfolder` ( 773 `folderid` int(19) NOT NULL auto_increment, 774 `foldername` varchar(100) NOT NULL default '', 775 `description` varchar(250) default '', 776 `state` varchar(50) default 'SAVED', 777 PRIMARY KEY (`folderid`) 778 ) TYPE=InnoDB"; 779 echo '<br> '.$sql90 .' <br> '; 780 $this->oldconn->query($sql90); 781 782 783 784 $sql90="CREATE TABLE `report` ( 785 `reportid` int(19) NOT NULL default '0', 786 `folderid` int(19) NOT NULL default '0', 787 `reportname` varchar(100) default '', 788 `description` varchar(250) default '', 789 `reporttype` varchar(50) default '', 790 `queryid` int(19) NOT NULL default '0', 791 `state` varchar(50) default 'SAVED', 792 `customizable` int(1) default '1', 793 `category` int(11) default '1', 794 PRIMARY KEY (`reportid`), 795 KEY `report_IDX0` (`queryid`), 796 KEY `report_IDX1` (`folderid`), 797 CONSTRAINT `report_FK2` FOREIGN KEY (`folderid`) REFERENCES `reportfolder` (`folderid`) ON DELETE CASCADE, 798 CONSTRAINT `report_FK1` FOREIGN KEY (`queryid`) REFERENCES `selectquery` (`queryid`) ON DELETE CASCADE 799 ) TYPE=InnoDB"; 800 echo '<br> '.$sql90 .' <br> '; 801 $this->oldconn->query($sql90); 802 803 804 $sql90="CREATE TABLE `reportdatefilter` ( 805 `datefilterid` int(19) NOT