Db
From UG
Line 86: | Line 86: | ||
</pre> | </pre> | ||
- | |||
=== tblAddressCompanyCity === | === tblAddressCompanyCity === |
Revision as of 18:47, 11 April 2010
Contents |
Intro
This is CT2 DB Schema. Originally received from below:
/* MySQL Data Transfer Source Host: 64.115.223.6 Source Database: cybertrax Target Host: 64.115.223.6 Target Database: cybertrax Date: 12/18/2009 3:17:18 PM */ SET FOREIGN_KEY_CHECKS=0;
Table Structures
mymfavorites
DROP TABLE IF EXISTS `mymfavorites`; CREATE TABLE `mymfavorites` ( `id` int(11) NOT NULL auto_increment, `fav_name` text, `fav_source` text, `fav_path` text, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Please do not modify this table!';
tblAddressBillToParty
DROP TABLE IF EXISTS `tblAddressBillToParty`; CREATE TABLE `tblAddressBillToParty` ( `Id` int(10) NOT NULL auto_increment, `CompanyName` varchar(70) NOT NULL default '', `Attn` varchar(25) NOT NULL, `AddressLine1` varchar(100) default NULL, `AddressLine2` varchar(100) default NULL, `AddressLine3` varchar(100) default NULL, `Street` varchar(30) NOT NULL, `Building` varchar(5) NOT NULL, `City` varchar(30) NOT NULL, `Postcode` varchar(10) NOT NULL, `ContactPerson` varchar(90) NOT NULL, `Remark` varchar(40) NOT NULL, `CountryId` int(10) default NULL, `USstateId` int(10) default NULL, `NonUS_state` varchar(40) default NULL, `PhoneCountryCode` varchar(4) NOT NULL, `PhoneCityCode` varchar(4) NOT NULL, `PhoneNumber` varchar(8) NOT NULL, `PhoneExt` varchar(4) NOT NULL, `FaxCountryCode` varchar(4) NOT NULL, `FaxCityCode` varchar(4) NOT NULL, `FaxNumber` varchar(8) NOT NULL, `FaxExt` varchar(4) NOT NULL, `IsArchived` bit(1) default NULL, PRIMARY KEY (`Id`), UNIQUE KEY `CompanyName` (`CompanyName`,`City`,`Remark`), KEY `constraintAddressBillToPartyCountry` (`CountryId`), KEY `constraintAddressBillToPartyState` (`USstateId`), CONSTRAINT `constraintAddressBillToPartyCountry` FOREIGN KEY (`CountryId`) REFERENCES `tblCountry` (`Id`), CONSTRAINT `constraintAddressBillToPartyState` FOREIGN KEY (`USstateId`) REFERENCES `tblUSAstate` (`Id`) ) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 7168 kB; (`CountryId`) REFER `cybertrax/tblCoun';
tblAddressCompanyCity
DROP TABLE IF EXISTS `tblAddressCompanyCity`; CREATE TABLE `tblAddressCompanyCity` ( `Id` int(10) NOT NULL auto_increment, `CompanyName` varchar(70) NOT NULL default '', `CityName` varchar(30) NOT NULL, `IsArchived` bit(1) NOT NULL default '\0', PRIMARY KEY (`Id`), UNIQUE KEY `CompanyName` (`CompanyName`,`CityName`) ) ENGINE=InnoDB AUTO_INCREMENT=643 DEFAULT CHARSET=latin1;
tblAddressJaguarVendor
DROP TABLE IF EXISTS `tblAddressJaguarVendor`; CREATE TABLE `tblAddressJaguarVendor` ( `Id` int(10) NOT NULL auto_increment, `CompanyCityId` int(10) NOT NULL, `Attn` varchar(25) NOT NULL, `AddressLine1` varchar(100) default NULL, `AddressLine2` varchar(100) default NULL, `AddressLine3` varchar(100) default NULL, `Street` varchar(30) NOT NULL, `Building` varchar(5) NOT NULL, `Postcode` varchar(10) NOT NULL, `ContactPerson` varchar(90) NOT NULL, `Remark` varchar(40) NOT NULL, `IsAirline` bit(1) NOT NULL default '\0', `IsSteamshipline` bit(1) NOT NULL default '\0', `IsTruckingCompany` bit(1) NOT NULL default '\0', `IsCustomsBroker` bit(1) NOT NULL default '\0', `IsCoLoaderOcean` bit(1) NOT NULL default '\0', `IsCoLoaderAir` bit(1) NOT NULL default '\0', `IsCargoLocation` bit(1) NOT NULL default '\0', `IsConsolidationWarehouse` bit(1) NOT NULL default '\0', `IsCustomsInspectionFacility` bit(1) NOT NULL default '\0', `IsJaguarOffice` bit(1) NOT NULL default '\0', `IsOverseasAgent` bit(1) NOT NULL default '\0', `CountryId` int(10) default NULL, `USstateId` int(10) default NULL, `NonUS_state` varchar(40) default NULL, `PhoneCountryCode` varchar(4) NOT NULL, `PhoneCityCode` varchar(4) NOT NULL, `PhoneNumber` varchar(8) NOT NULL, `PhoneExt` varchar(4) NOT NULL, `FaxCountryCode` varchar(4) NOT NULL, `FaxCityCode` varchar(4) NOT NULL, `FaxNumber` varchar(8) NOT NULL, `FaxExt` varchar(4) NOT NULL, `IsArchived` bit(1) default NULL, PRIMARY KEY (`Id`), UNIQUE KEY `AddressId` (`CompanyCityId`,`Remark`), KEY `constraintAddressJaguarVendorCountry` (`CountryId`), KEY `constraintAddressJaguarVendorState` (`USstateId`), CONSTRAINT `constraintAddressJaguarVendorCountry` FOREIGN KEY (`CountryId`) REFERENCES `tblCountry` (`Id`), CONSTRAINT `constraintAddressJaguarVendorState` FOREIGN KEY (`USstateId`) REFERENCES `tblUSAstate` (`Id`), CONSTRAINT `tblAddressJaguarVendor_ibfk_1` FOREIGN KEY (`CompanyCityId`) REFERENCES `tblAddressCompanyCity` (`Id`) ) ENGINE=InnoDB AUTO_INCREMENT=166 DEFAULT CHARSET=latin1;
tblAddressTransportation
DROP TABLE IF EXISTS `tblAddressTransportation`; CREATE TABLE `tblAddressTransportation` ( `Id` int(10) NOT NULL auto_increment, `CompanyCityId` int(10) NOT NULL, `Attn` varchar(25) NOT NULL, `AddressLine1` varchar(100) default NULL, `AddressLine2` varchar(100) default NULL, `AddressLine3` varchar(100) default NULL, `Street` varchar(30) NOT NULL, `Building` varchar(5) NOT NULL, `Postcode` varchar(10) NOT NULL, `ContactPerson` varchar(90) NOT NULL, `Remark` varchar(40) NOT NULL, `IsShipper` bit(1) NOT NULL default '\0', `IsConsignee` bit(1) NOT NULL default '\0', `IsNotify` bit(1) NOT NULL default '\0', `ExportPickUpLocation` bit(1) NOT NULL default '\0', `ExportDeliveryLocation` bit(1) NOT NULL default '\0', `ImportPickUpLocation` bit(1) NOT NULL default '\0', `ImportDeliveryLocation` bit(1) NOT NULL default '\0', `IsDestinationReceivingAgent` bit(1) NOT NULL default '\0', `MasterPickUp` bit(1) NOT NULL default '\0', `MasterDeliveryAddress` bit(1) NOT NULL default '\0', `MasterShipper` bit(1) NOT NULL default '\0', `MasterConsignee` bit(1) NOT NULL default '\0', `MasterNotify` bit(1) NOT NULL default '\0', `CountryId` int(10) default NULL, `USstateId` int(10) default NULL, `NonUS_state` varchar(40) default NULL, `PhoneCountryCode` varchar(4) NOT NULL, `PhoneCityCode` varchar(4) NOT NULL, `PhoneNumber` varchar(8) NOT NULL, `PhoneExt` varchar(4) NOT NULL, `FaxCountryCode` varchar(4) NOT NULL, `FaxCityCode` varchar(4) NOT NULL, `FaxNumber` varchar(8) NOT NULL, `FaxExt` varchar(4) NOT NULL, `IsArchived` bit(1) default NULL, PRIMARY KEY (`Id`), UNIQUE KEY `AddressId` (`CompanyCityId`,`Remark`), KEY `constraintAddressTransportationCountry` (`CountryId`), KEY `constraintAddressTransportationState` (`USstateId`), CONSTRAINT `constraintAddressTransportationCountry` FOREIGN KEY (`CountryId`) REFERENCES `tblCountry` (`Id`), CONSTRAINT `constraintAddressTransportationState` FOREIGN KEY (`USstateId`) REFERENCES `tblUSAstate` (`Id`), CONSTRAINT `tblAddressTransportation_ibfk_1` FOREIGN KEY (`CompanyCityId`) REFERENCES `tblAddressCompanyCity` (`Id`) ) ENGINE=InnoDB AUTO_INCREMENT=415 DEFAULT CHARSET=latin1;
tblAirline
DROP TABLE IF EXISTS `tblAirline`; CREATE TABLE `tblAirline` ( `Id` int(10) NOT NULL auto_increment, `TwoLetterCode` varchar(2) NOT NULL, `Name` varchar(30) NOT NULL, `NumericCode` varchar(3) NOT NULL, PRIMARY KEY (`Id`) ) ENGINE=InnoDB AUTO_INCREMENT=83 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblAirport
-- ----------------------------
DROP TABLE IF EXISTS `tblAirport`;
CREATE TABLE `tblAirport` (
`Id` int(10) NOT NULL auto_increment, `City` varchar(50) NOT NULL, `CountryID` int(10) NOT NULL, `LetterCode` varchar(3) default NULL, `IsArchived` bit(1) NOT NULL default '\0', PRIMARY KEY (`Id`), KEY `Airport_CountryID` (`CountryID`), CONSTRAINT `tblAirport_ibfk_1` FOREIGN KEY (`CountryID`) REFERENCES `tblCountry` (`Id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=74 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblAirShipment
-- ----------------------------
DROP TABLE IF EXISTS `tblAirShipment`;
CREATE TABLE `tblAirShipment` (
`AirShipmentId` int(10) NOT NULL auto_increment, `GenericShipmentId` int(10) NOT NULL, `Hazardous` tinyint(1) NOT NULL default '0', `HazardousNote` varchar(1500) default NULL, `Freight` int(1) default '0', `AirlineId` int(10) default NULL, `CoLoaderAirId` int(10) default NULL, `Hawb` varchar(30) default NULL, `MasterBillA` varchar(3) NOT NULL default , `MasterBillB` varchar(4) NOT NULL default , `MasterBillC` varchar(4) NOT NULL default , `HouseBillOfLanding` varchar(30) default NULL, `FlightOne` varchar(30) default NULL, `FlightTwo` varchar(30) default NULL, `AirportOfDepartureId` int(10) default NULL, `AirportOfDepartureETD` date default NULL, `AirportOfDepartureATD` date default NULL, `TransShipmentAirportId` int(10) default NULL, `TransShipmentAirportETA` date default NULL, `TransShipmentAirportATA` date default NULL, `AirportOfDestinationId` int(10) default NULL, `AirportOfDestinationETA` date default NULL, `AirportOfDestinationATA` date default NULL, `DestinationReceivingAgentId` int(10) default NULL, `HouseAirwayBillComments` varchar(1500) default NULL, `EnabledArrivalToWH` tinyint(1) default '1', `ArrivalToWarehouseDate` date default NULL, `ItNumber` varchar(17) default NULL, `ItDate` date default NULL, `ItLocation` varchar(20) default NULL, `CustomsBrokerId` int(10) default NULL, `CustomsBrokerDescription` varchar(1500) default NULL, `CargoLocationId` int(10) default NULL, `CargoLocationDescription` varchar(1500) default NULL, `LastFreeDay` date default NULL, `ArrivalNoticeComments` varchar(1500) default NULL, `DeliveryAddressId` int(10) default NULL, `DeliveryAddressDescription` varchar(1500) default NULL, `DeliveryTruckerId` int(10) default NULL, `ImportEstimatedDeliveryDate` date default NULL, `ImportActualDeliveryDate` date default NULL, `DeliveryComments` varchar(100) default NULL, `TruckerDeliveryInstructions` varchar(1500) default NULL, `PaymentFreightChargesRequired` int(1) default '0', `CustomsStatusId` int(10) default NULL, `CustomsStatusDate` date default NULL, `FDAStatusId` int(10) default NULL, `FDAStatusDate` date default NULL, `DeliveryDateTypeId` int(10) default NULL, `ExportCustoms` varchar(30) default NULL, `ActualDeliveryDateEnabled` tinyint(1) default '0', PRIMARY KEY (`AirShipmentId`), KEY `OceanShipment_GenericShipmentId` (`GenericShipmentId`), KEY `AirShipment_AirlineId` (`AirlineId`), KEY `AirShipment_CoLoaderAirId` (`CoLoaderAirId`), KEY `AirShipment_AirportOfDepartureId` (`AirportOfDepartureId`), KEY `AirShipment_TransShipmentAirportId` (`TransShipmentAirportId`), KEY `AirShipment_AirportOfDestinationId` (`AirportOfDestinationId`), KEY `AirShipment_DestinationReceivingAgentId` (`DestinationReceivingAgentId`), KEY `AirShipment_CustomsBrokerId` (`CustomsBrokerId`), KEY `AirShipment_CargoLocationId` (`CargoLocationId`), KEY `AirShipment_DeliveryAddressId` (`DeliveryAddressId`), KEY `AirShipment_DeliveryTruckerId` (`DeliveryTruckerId`), KEY `AirShipment_CustomsStatusId` (`CustomsStatusId`), KEY `AirShipment_FDAStatusId` (`FDAStatusId`), CONSTRAINT `tblAirShipment_ibfk_1` FOREIGN KEY (`GenericShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON DELETE CASCADE, CONSTRAINT `tblAirShipment_ibfk_10` FOREIGN KEY (`DeliveryAddressId`) REFERENCES `tblAddressTransportation` (`Id`), CONSTRAINT `tblAirShipment_ibfk_11` FOREIGN KEY (`DeliveryTruckerId`) REFERENCES `tblAddressJaguarVendor` (`Id`), CONSTRAINT `tblAirShipment_ibfk_12` FOREIGN KEY (`CustomsStatusId`) REFERENCES `tblCustomsStatus` (`Id`), CONSTRAINT `tblAirShipment_ibfk_13` FOREIGN KEY (`FDAStatusId`) REFERENCES `tblFDAStatus` (`Id`), CONSTRAINT `tblAirShipment_ibfk_2` FOREIGN KEY (`AirlineId`) REFERENCES `tblAirline` (`Id`), CONSTRAINT `tblAirShipment_ibfk_3` FOREIGN KEY (`CoLoaderAirId`) REFERENCES `tblAddressJaguarVendor` (`Id`), CONSTRAINT `tblAirShipment_ibfk_4` FOREIGN KEY (`AirportOfDepartureId`) REFERENCES `tblAirport` (`Id`), CONSTRAINT `tblAirShipment_ibfk_5` FOREIGN KEY (`TransShipmentAirportId`) REFERENCES `tblAirport` (`Id`), CONSTRAINT `tblAirShipment_ibfk_6` FOREIGN KEY (`AirportOfDestinationId`) REFERENCES `tblAirport` (`Id`), CONSTRAINT `tblAirShipment_ibfk_7` FOREIGN KEY (`DestinationReceivingAgentId`) REFERENCES `tblAddressTransportation` (`Id`), CONSTRAINT `tblAirShipment_ibfk_8` FOREIGN KEY (`CustomsBrokerId`) REFERENCES `tblAddressJaguarVendor` (`Id`), CONSTRAINT `tblAirShipment_ibfk_9` FOREIGN KEY (`CargoLocationId`) REFERENCES `tblAddressTransportation` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=504 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblAlwaysBCCEmails
-- ----------------------------
DROP TABLE IF EXISTS `tblAlwaysBCCEmails`;
CREATE TABLE `tblAlwaysBCCEmails` (
`Id` int(10) NOT NULL auto_increment, `UserId` int(10) NOT NULL, PRIMARY KEY (`Id`), KEY `UserId` (`UserId`), CONSTRAINT `tblAlwaysBCCEmails_ibfk_1` FOREIGN KEY (`UserId`) REFERENCES `tblGenericUser` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblAuthorizationMethod
-- ----------------------------
DROP TABLE IF EXISTS `tblAuthorizationMethod`;
CREATE TABLE `tblAuthorizationMethod` (
`Id` int(10) NOT NULL auto_increment, `ClientCompanyId` int(11) NOT NULL, `Method` varchar(30) NOT NULL, `IsArchived` bit(1) NOT NULL default '\0', PRIMARY KEY (`Id`), UNIQUE KEY `uniqueMethodClientCompanyId` (`Method`,`ClientCompanyId`)
) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblAuthorizationMethodSettings
-- ----------------------------
DROP TABLE IF EXISTS `tblAuthorizationMethodSettings`;
CREATE TABLE `tblAuthorizationMethodSettings` (
`Id` int(10) NOT NULL auto_increment, `ClientCompanyId` int(10) NOT NULL, `UseDefaultMethods` bit(1) NOT NULL default '\0', PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=220 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblBusiestSeaPort
-- ----------------------------
DROP TABLE IF EXISTS `tblBusiestSeaPort`;
CREATE TABLE `tblBusiestSeaPort` (
`Id` int(10) NOT NULL auto_increment, `SeaPortId` int(10) NOT NULL, `Year` smallint(6) default NULL, `TEU` int(10) default NULL, PRIMARY KEY (`Id`), KEY `SeaPortId` (`SeaPortId`), CONSTRAINT `tblBusiestSeaPort_fk` FOREIGN KEY (`SeaPortId`) REFERENCES `tblSeaPort` (`Id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblChargeBackInvoice
-- ----------------------------
DROP TABLE IF EXISTS `tblChargeBackInvoice`;
CREATE TABLE `tblChargeBackInvoice` (
`Id` int(10) NOT NULL auto_increment, `Number` int(10) NOT NULL, `OfficeId` int(10) NOT NULL, `CurrencyId` int(10) NOT NULL, `Amount` double NOT NULL, `Date` date NOT NULL, `ShipmentId` int(10) default NULL, `MasterId` int(10) default NULL, `UserId` int(10) NOT NULL, `PurchaseInvoiceId` int(10) NOT NULL, `ExchangeRate` double NOT NULL default '1', PRIMARY KEY (`Id`), KEY `OfficeId` (`OfficeId`), KEY `CurrencyId` (`CurrencyId`), KEY `ShipmentId` (`ShipmentId`), KEY `MasterId` (`MasterId`), KEY `UserId` (`UserId`), KEY `PurchaseInvoiceId` (`PurchaseInvoiceId`), CONSTRAINT `tblChargeBackInvoice_fk` FOREIGN KEY (`OfficeId`) REFERENCES `tblOffice` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblChargeBackInvoice_fk1` FOREIGN KEY (`CurrencyId`) REFERENCES `tblCurrency` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblChargeBackInvoice_fk4` FOREIGN KEY (`UserId`) REFERENCES `tblGenericUser` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblChargeBackInvoice_fk5` FOREIGN KEY (`PurchaseInvoiceId`) REFERENCES `tblPurchaseInvoice` (`Id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblChargeCode
-- ----------------------------
DROP TABLE IF EXISTS `tblChargeCode`;
CREATE TABLE `tblChargeCode` (
`Id` int(10) NOT NULL auto_increment, `ChargeCodeNumber` int(10) NOT NULL, `Description` varchar(150) NOT NULL, `MorI` tinyint(1) NOT NULL, `CalculationType` int(10) default NULL, `QuantityField` varchar(25) default NULL, `QuantityUnitTypeId` int(10) default NULL, `RateUnitTypeId` int(10) default NULL, `QuantityFieldId` int(10) default NULL, `CBVType` smallint(6) NOT NULL default '0', PRIMARY KEY (`Id`), KEY `QuantityUnitTypeId` (`QuantityUnitTypeId`), KEY `RateUnitTypeId` (`RateUnitTypeId`), CONSTRAINT `tblChargeCode_fk1` FOREIGN KEY (`RateUnitTypeId`) REFERENCES `tblUnitType` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblChargeCode_fk` FOREIGN KEY (`QuantityUnitTypeId`) REFERENCES `tblUnitType` (`Id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=105 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
-- ----------------------------
-- Table structure for tblClient
-- ----------------------------
DROP TABLE IF EXISTS `tblClient`;
CREATE TABLE `tblClient` (
`Id` int(10) NOT NULL auto_increment, `Name` varchar(50) NOT NULL, `Email` varchar(50) NOT NULL, `ContactName` varchar(50) NOT NULL, `ContactPhone` varchar(20) NOT NULL, `NotifyOnEstimatedDepartureDate` int(1) NOT NULL, `ReceiveDateChangeNotification` int(1) NOT NULL, `RecepientEmail` varchar(50) NOT NULL, `CurrentTopVendorID` int(10) default NULL, `CurrentTopConsigneeID` int(10) NOT NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblClientCompany
-- ----------------------------
DROP TABLE IF EXISTS `tblClientCompany`;
CREATE TABLE `tblClientCompany` (
`Id` int(10) NOT NULL auto_increment, `CompanyName` varchar(50) NOT NULL, `InternalOpsGroupId` int(10) default NULL, `SalesPersonId` int(10) default NULL, `CreditStatusId` int(10) default NULL, `CreditLimitId` int(10) default NULL, `CreditTermId` int(10) default NULL, `DisplayExchangeRates` tinyint(1) NOT NULL default '0', `ShowDeliveryLocation` tinyint(1) NOT NULL default '0', `IsArchived` bit(1) NOT NULL default '\0', PRIMARY KEY (`Id`), KEY `tblClientCompany_ibfk_1` (`InternalOpsGroupId`), KEY `tblClientCompany_ibfk_2` (`SalesPersonId`), KEY `tblClientCompany_ibfk_3` (`CreditStatusId`), KEY `tblClientCompany_ibfk_4` (`CreditLimitId`), KEY `tblClientCompany_ibfk_5` (`CreditTermId`), CONSTRAINT `tblClientCompany_ibfk_1` FOREIGN KEY (`InternalOpsGroupId`) REFERENCES `tblJaguarUserGroup` (`Id`), CONSTRAINT `tblClientCompany_ibfk_2` FOREIGN KEY (`SalesPersonId`) REFERENCES `tblJaguarUser` (`Id`), CONSTRAINT `tblClientCompany_ibfk_3` FOREIGN KEY (`CreditStatusId`) REFERENCES `tblCreditStatus` (`Id`), CONSTRAINT `tblClientCompany_ibfk_4` FOREIGN KEY (`CreditLimitId`) REFERENCES `tblCreditLimit` (`Id`), CONSTRAINT `tblClientCompany_ibfk_5` FOREIGN KEY (`CreditTermId`) REFERENCES `tblCreditTerm` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=270 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblClientCompanyGroup
-- ----------------------------
DROP TABLE IF EXISTS `tblClientCompanyGroup`;
CREATE TABLE `tblClientCompanyGroup` (
`Id` int(10) NOT NULL auto_increment, `GroupName` varchar(30) NOT NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblClientCompanyGroupRelation
-- ----------------------------
DROP TABLE IF EXISTS `tblClientCompanyGroupRelation`;
CREATE TABLE `tblClientCompanyGroupRelation` (
`Id` int(10) NOT NULL auto_increment, `ClientCompanyGroupId` int(10) NOT NULL, `ClientCompanyId` int(10) NOT NULL, PRIMARY KEY (`Id`), KEY `ClientCompanyId` (`ClientCompanyId`), KEY `ClientCompanyGroupId` (`ClientCompanyGroupId`), CONSTRAINT `tblClientCompanyGroupRelation_fk` FOREIGN KEY (`ClientCompanyId`) REFERENCES `tblClientCompany` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `tblClientCompanyGroupRelation_fk1` FOREIGN KEY (`ClientCompanyGroupId`) REFERENCES `tblClientCompanyGroup` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblClientExchangeRate
-- ----------------------------
DROP TABLE IF EXISTS `tblClientExchangeRate`;
CREATE TABLE `tblClientExchangeRate` (
`Id` int(10) NOT NULL auto_increment, `ClientID` int(10) NOT NULL, `ExchangeRateNumber` int(10) NOT NULL, `ThisClientExchangeRate` double NOT NULL, `LastUpdated` datetime NOT NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblClientMode
-- ----------------------------
DROP TABLE IF EXISTS `tblClientMode`;
CREATE TABLE `tblClientMode` (
`Id` int(10) NOT NULL auto_increment, `ClientCompanyId` int(10) NOT NULL, `TransportModeId` int(10) NOT NULL, `ClientMode` varchar(30) NOT NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblClientsGroup
-- ----------------------------
DROP TABLE IF EXISTS `tblClientsGroup`;
CREATE TABLE `tblClientsGroup` (
`Id` int(10) NOT NULL auto_increment, `GroupName` varchar(30) NOT NULL default , PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblClientsGroupRelation
-- ----------------------------
DROP TABLE IF EXISTS `tblClientsGroupRelation`;
CREATE TABLE `tblClientsGroupRelation` (
`Id` int(10) NOT NULL auto_increment, `ClientsGroupId` int(10) NOT NULL, `ClientUserId` int(10) NOT NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblClientUser
-- ----------------------------
DROP TABLE IF EXISTS `tblClientUser`;
CREATE TABLE `tblClientUser` (
`Id` int(10) NOT NULL auto_increment, `GenericUserId` int(10) NOT NULL, `EmployedById` int(10) default NULL, `RSSFeedId` int(10) default NULL, `AddressBillToPartyId` int(10) default NULL, `AddressJaguarVendorId` int(10) default NULL, `AddressTransportationId` int(10) default NULL, `ClientCompanyId` int(10) default NULL, `IsAllShippersVisible` bit(1) NOT NULL default '�', `IsAllConsigneesVisible` bit(1) NOT NULL default '�', `IsAllOriginRegionsVisible` bit(1) NOT NULL default '�', `IsAllDestinationRegionsVisible` bit(1) NOT NULL default '�', PRIMARY KEY (`Id`), UNIQUE KEY `GenericUserId` (`GenericUserId`), KEY `EmployedById` (`EmployedById`), KEY `AddressBillToPartyId` (`AddressBillToPartyId`), KEY `AddressJaguarVendorId` (`AddressJaguarVendorId`), KEY `AddressTransportationId` (`AddressTransportationId`), KEY `ClientCompanyId` (`ClientCompanyId`), CONSTRAINT `tblClientUser_fk` FOREIGN KEY (`AddressBillToPartyId`) REFERENCES `tblAddressBillToParty` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblClientUser_fk1` FOREIGN KEY (`AddressJaguarVendorId`) REFERENCES `tblAddressJaguarVendor` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblClientUser_fk2` FOREIGN KEY (`AddressTransportationId`) REFERENCES `tblAddressTransportation` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblClientUser_fk4` FOREIGN KEY (`ClientCompanyId`) REFERENCES `tblClientCompany` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblClientUser_ibfk_1` FOREIGN KEY (`GenericUserId`) REFERENCES `tblGenericUser` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblClientUser2AddressTransportation
-- ----------------------------
DROP TABLE IF EXISTS `tblClientUser2AddressTransportation`;
CREATE TABLE `tblClientUser2AddressTransportation` (
`Id` int(10) NOT NULL auto_increment, `ClientUserId` int(10) default NULL, `AddressTransportationId` int(10) default NULL, `IsShipper` bit(1) default NULL, PRIMARY KEY (`Id`), KEY `ClientUserId` (`ClientUserId`), KEY `AddressTransportationId` (`AddressTransportationId`), CONSTRAINT `tblClientUser2AddressTransportation_fk` FOREIGN KEY (`ClientUserId`) REFERENCES `tblClientUser` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `tblClientUser2AddressTransportation_fk1` FOREIGN KEY (`AddressTransportationId`) REFERENCES `tblAddressTransportation` (`Id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=196 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblClientUser2Region
-- ----------------------------
DROP TABLE IF EXISTS `tblClientUser2Region`;
CREATE TABLE `tblClientUser2Region` (
`Id` int(10) NOT NULL auto_increment, `ClientUserId` int(10) default NULL, `RegionId` int(10) default NULL, `IsOrigin` bit(1) default NULL, PRIMARY KEY (`Id`), KEY `ClientUserId` (`ClientUserId`), KEY `RegionId` (`RegionId`), CONSTRAINT `tblClientUser2Region_fk` FOREIGN KEY (`ClientUserId`) REFERENCES `tblClientUser` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `tblClientUser2Region_fk1` FOREIGN KEY (`RegionId`) REFERENCES `tblRegion` (`Id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblClientUserWatchList
-- ----------------------------
DROP TABLE IF EXISTS `tblClientUserWatchList`;
CREATE TABLE `tblClientUserWatchList` (
`Id` int(10) NOT NULL auto_increment, `UserId` int(10) NOT NULL, `ShipmentId` int(10) NOT NULL, `NickName` varchar(45) default NULL, `IsInTop5` bit(1) default NULL, PRIMARY KEY (`Id`), KEY `UserId` (`UserId`), KEY `ShipmentId` (`ShipmentId`), CONSTRAINT `tblClientUserWatchList_ibfk_1` FOREIGN KEY (`UserId`) REFERENCES `tblGenericUser` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `tblClientUserWatchList_ibfk_2` FOREIGN KEY (`ShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=175 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblCommentsMailedTo
-- ----------------------------
DROP TABLE IF EXISTS `tblCommentsMailedTo`;
CREATE TABLE `tblCommentsMailedTo` (
`CommentId` int(10) NOT NULL, `UserId` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblCommodity
-- ----------------------------
DROP TABLE IF EXISTS `tblCommodity`;
CREATE TABLE `tblCommodity` (
`Id` int(10) NOT NULL auto_increment, `ShipmentId` int(10) default NULL, `TotalHazardousOfPlts` int(10) default NULL, `TotalHazardousPkgsOnPlts` int(10) default NULL, `TotalHazardousLoosePkgs` int(11) NOT NULL default '0', `TotalHazardousGrossKg` float(9,2) default NULL, PRIMARY KEY (`Id`), KEY `ShipmentId` (`ShipmentId`), CONSTRAINT `tblCommodity_fk` FOREIGN KEY (`ShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1191 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblCommodityDisplaySettings
-- ----------------------------
DROP TABLE IF EXISTS `tblCommodityDisplaySettings`;
CREATE TABLE `tblCommodityDisplaySettings` (
`Id` int(10) NOT NULL auto_increment, `ClientCompanyId` int(10) default NULL, `InvoiceViewable` tinyint(1) NOT NULL default '0', `POViewable` tinyint(1) NOT NULL default '0', `SKUViewable` tinyint(1) NOT NULL default '0', `QuantityViewable` tinyint(1) NOT NULL default '0', `PackagingTypeViewable` tinyint(1) NOT NULL default '0', `ItemDescriptionViewable` tinyint(1) NOT NULL default '0', `PiecesPerPackagingViewable` tinyint(1) NOT NULL default '0', `ExtraField1Label` varchar(30) default NULL, `ExtraField1Viewable` tinyint(1) NOT NULL default '0', `ExtraField2Label` varchar(30) default NULL, `ExtraField2Viewable` tinyint(1) NOT NULL default '0', `ExtraField3Label` varchar(30) default NULL, `ExtraField3Viewable` tinyint(1) NOT NULL default '0', PRIMARY KEY (`Id`), KEY `ClientCompanyId_FK` (`ClientCompanyId`), CONSTRAINT `ClientCompanyId_FK` FOREIGN KEY (`ClientCompanyId`) REFERENCES `tblClientCompany` (`Id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=246 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblCommodityItem
-- ----------------------------
DROP TABLE IF EXISTS `tblCommodityItem`;
CREATE TABLE `tblCommodityItem` (
`Id` int(10) NOT NULL auto_increment, `CommodityId` int(10) default NULL, `Invoice` varchar(30) default NULL, `PurchaseOrder` varchar(30) default NULL, `SQU` varchar(30) default NULL, `Quantity` int(10) default NULL, `PackagingTypeId` int(10) default NULL, `Description` varchar(60) default NULL, `PiecesPerPackaging` int(10) default NULL, `TotalOfPlts` int(10) default NULL, `TotalPkgsOnPlts` int(10) default NULL, `TotalPkgsOnPltsTypeId` int(10) default NULL, `LoosePkgsOnPlts` int(10) default NULL, `LoosePkgsOnPltsTypeId` int(10) default NULL, `TotalGrossKg` float(9,2) default NULL, `ContainerId` int(10) default NULL, `ExtraField1` varchar(30) default NULL, `ExtraField2` varchar(30) default NULL, `ExtraField3` varchar(30) default NULL, PRIMARY KEY (`Id`), KEY `tblCommodityItem_ibfk_1` (`ContainerId`), KEY `CommodityId` (`CommodityId`), CONSTRAINT `tblCommodityItem_fk` FOREIGN KEY (`CommodityId`) REFERENCES `tblCommodity` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `tblCommodityItem_ibfk_1` FOREIGN KEY (`ContainerId`) REFERENCES `tblContainer` (`Id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=1094 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblContainer
-- ----------------------------
DROP TABLE IF EXISTS `tblContainer`;
CREATE TABLE `tblContainer` (
`Id` int(10) NOT NULL auto_increment, `ContainerIdA` char(4) default NULL, `ContainerIdB` char(6) default NULL, `ContainerIdC` char(1) default NULL, `Seal` varchar(20) default , `ContainerTypeId` int(10) default NULL, `ContainerSizeId` int(10) default NULL, `EstimatedCollection` date default NULL, `ActualCollection` date default NULL, `ContainerRequestDelivery` date default NULL, `ContainerActualDelivery` date default NULL, `DeliveryLocationId` int(10) default NULL, `ShipmentId` int(10) default NULL, `MasterId` int(10) default NULL, PRIMARY KEY (`Id`), KEY `ContainerTypeId` (`ContainerTypeId`), KEY `tblContainer_ibfk_2` (`ShipmentId`), KEY `tblContainer_ibfk_3` (`MasterId`), KEY `tblContainer_ibfk_4` (`DeliveryLocationId`), CONSTRAINT `tblContainer_ibfk_1` FOREIGN KEY (`ContainerTypeId`) REFERENCES `tblContainerType` (`Id`), CONSTRAINT `tblContainer_ibfk_2` FOREIGN KEY (`ShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON DELETE CASCADE, CONSTRAINT `tblContainer_ibfk_3` FOREIGN KEY (`MasterId`) REFERENCES `tblMasterOcean` (`Id`) ON DELETE CASCADE, CONSTRAINT `tblContainer_ibfk_4` FOREIGN KEY (`DeliveryLocationId`) REFERENCES `tblDeliveryLocation` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=670 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblContainer2Master
-- ----------------------------
DROP TABLE IF EXISTS `tblContainer2Master`;
CREATE TABLE `tblContainer2Master` (
`ContainerId` int(10) NOT NULL, `MasterId` int(10) NOT NULL, UNIQUE KEY `ContainerId` (`ContainerId`,`MasterId`), KEY `MasterId` (`MasterId`), CONSTRAINT `tblContainer2Master_ibfk_1` FOREIGN KEY (`ContainerId`) REFERENCES `tblContainer` (`Id`) ON DELETE CASCADE, CONSTRAINT `tblContainer2Master_ibfk_2` FOREIGN KEY (`MasterId`) REFERENCES `tblMasterOcean` (`Id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblContainer2Shipment
-- ----------------------------
DROP TABLE IF EXISTS `tblContainer2Shipment`;
CREATE TABLE `tblContainer2Shipment` (
`ContainerId` int(10) NOT NULL, `ShipmentId` int(10) NOT NULL, UNIQUE KEY `ContainerId` (`ContainerId`,`ShipmentId`), KEY `ShipmentId` (`ShipmentId`), CONSTRAINT `tblContainer2Shipment_ibfk_1` FOREIGN KEY (`ContainerId`) REFERENCES `tblContainer` (`Id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblContainerSize
-- ----------------------------
DROP TABLE IF EXISTS `tblContainerSize`;
CREATE TABLE `tblContainerSize` (
`Id` int(10) NOT NULL auto_increment, `Description` varchar(30) NOT NULL, `IsArchived` bit(1) NOT NULL default '\0', PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblContainerType
-- ----------------------------
DROP TABLE IF EXISTS `tblContainerType`;
CREATE TABLE `tblContainerType` (
`Id` int(10) NOT NULL auto_increment, `Description` varchar(30) NOT NULL, `IsArchived` bit(1) NOT NULL default '\0', PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblCountry
-- ----------------------------
DROP TABLE IF EXISTS `tblCountry`;
CREATE TABLE `tblCountry` (
`Id` int(10) NOT NULL auto_increment, `ISO2` char(2) NOT NULL, `Name` varchar(80) NOT NULL, `PrintableName` varchar(80) NOT NULL, `ISO3` char(3) default NULL, `NumCode` smallint(6) default NULL, `RegionId` int(10) default NULL, `IsArchived` bit(1) NOT NULL default '\0', PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=241 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblCreditLimit
-- ----------------------------
DROP TABLE IF EXISTS `tblCreditLimit`;
CREATE TABLE `tblCreditLimit` (
`Id` int(10) NOT NULL auto_increment, `Limit` varchar(50) NOT NULL default , PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblCreditStatus
-- ----------------------------
DROP TABLE IF EXISTS `tblCreditStatus`;
CREATE TABLE `tblCreditStatus` (
`Id` int(10) NOT NULL auto_increment, `Status` varchar(50) NOT NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblCreditTerm
-- ----------------------------
DROP TABLE IF EXISTS `tblCreditTerm`;
CREATE TABLE `tblCreditTerm` (
`Id` int(10) NOT NULL auto_increment, `Term` int(10) NOT NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblCurrency
-- ----------------------------
DROP TABLE IF EXISTS `tblCurrency`;
CREATE TABLE `tblCurrency` (
`Id` int(10) NOT NULL auto_increment, `Name` varchar(50) NOT NULL, `Abbreviation` varchar(3) NOT NULL, `IsMain` tinyint(1) NOT NULL default '0', PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblCurrencyExchangeRate
-- ----------------------------
DROP TABLE IF EXISTS `tblCurrencyExchangeRate`;
CREATE TABLE `tblCurrencyExchangeRate` (
`Id` int(10) NOT NULL auto_increment, `FromCurrencyId` int(10) NOT NULL, `ToCurrencyId` int(10) NOT NULL, `Rate` double default NULL, `OfficeId` int(10) NOT NULL, `ClientCompanyId` int(10) default NULL, `DateTime` datetime default NULL, PRIMARY KEY (`Id`), KEY `FromCurrencyId` (`FromCurrencyId`), KEY `ToCurrencyId` (`ToCurrencyId`), KEY `OfficeId` (`OfficeId`), KEY `ClientCompanyId` (`ClientCompanyId`), CONSTRAINT `tblCurrencyExchangeRate_fk` FOREIGN KEY (`FromCurrencyId`) REFERENCES `tblCurrency` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `tblCurrencyExchangeRate_fk1` FOREIGN KEY (`ToCurrencyId`) REFERENCES `tblCurrency` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `tblCurrencyExchangeRate_fk2` FOREIGN KEY (`OfficeId`) REFERENCES `tblOffice` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `tblCurrencyExchangeRate_fk3` FOREIGN KEY (`ClientCompanyId`) REFERENCES `tblClientCompany` (`Id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2094 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblCurrencyExchangeRateChange
-- ----------------------------
DROP TABLE IF EXISTS `tblCurrencyExchangeRateChange`;
CREATE TABLE `tblCurrencyExchangeRateChange` (
`Id` int(10) NOT NULL auto_increment, `OfficeId` int(10) NOT NULL, `FromCurrencyAbbreviation` varchar(3) NOT NULL default , `ToCurrencyAbbreviation` varchar(3) NOT NULL default , `OldRate` double default NULL, `NewRate` double default NULL, `DateTime` datetime NOT NULL, `GenericUserId` int(10) NOT NULL, `ClientCompanyId` int(10) default NULL, PRIMARY KEY (`Id`), KEY `OfficeId` (`OfficeId`), KEY `GenericUserId` (`GenericUserId`), KEY `ClientCompanyId` (`ClientCompanyId`), CONSTRAINT `tblCurrencyExchangeRateChange_fk` FOREIGN KEY (`OfficeId`) REFERENCES `tblOffice` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblCurrencyExchangeRateChange_fk1` FOREIGN KEY (`GenericUserId`) REFERENCES `tblGenericUser` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblCurrencyExchangeRateChange_fk2` FOREIGN KEY (`ClientCompanyId`) REFERENCES `tblClientCompany` (`Id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=155 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblCustomer
-- ----------------------------
DROP TABLE IF EXISTS `tblCustomer`;
CREATE TABLE `tblCustomer` (
`Id` int(10) NOT NULL auto_increment, `UserID` int(10) NOT NULL, `ClientID` int(10) NOT NULL, `AdditionalClientID` int(10) NOT NULL, `ThirdClientID` int(10) NOT NULL, `ShipperFilterID` int(10) NOT NULL, `ConsigneeFilterID` int(10) NOT NULL, `Name` varchar(50) NOT NULL, `CompanyName` varchar(50) NOT NULL, `Email` varchar(50) NOT NULL, `UserType` int(10) NOT NULL, `Enabled` int(1) NOT NULL, `Permissions` int(10) NOT NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblCustomsStatus
-- ----------------------------
DROP TABLE IF EXISTS `tblCustomsStatus`;
CREATE TABLE `tblCustomsStatus` (
`Id` int(10) NOT NULL auto_increment, `CustomsStatusName` varchar(30) NOT NULL, `IsArchived` bit(1) NOT NULL default '\0', PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblDailyQuotes
-- ----------------------------
DROP TABLE IF EXISTS `tblDailyQuotes`;
CREATE TABLE `tblDailyQuotes` (
`Id` int(10) NOT NULL auto_increment, `Text` varchar(512) default NULL, PRIMARY KEY (`Id`), UNIQUE KEY `Id` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblDeliveryDateTypes
-- ----------------------------
DROP TABLE IF EXISTS `tblDeliveryDateTypes`;
CREATE TABLE `tblDeliveryDateTypes` (
`Id` int(10) NOT NULL auto_increment, `Name` varchar(50) NOT NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblDeliveryLocation
-- ----------------------------
DROP TABLE IF EXISTS `tblDeliveryLocation`;
CREATE TABLE `tblDeliveryLocation` (
`Id` int(10) NOT NULL auto_increment, `ClientCompanyId` int(10) default NULL, `LocationName` varchar(50) NOT NULL, PRIMARY KEY (`Id`), KEY `tblDeliveryLocation_ibfk_1` (`ClientCompanyId`), CONSTRAINT `tblDeliveryLocation_ibfk_1` FOREIGN KEY (`ClientCompanyId`) REFERENCES `tblClientCompany` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblDropClientTops
-- ----------------------------
DROP TABLE IF EXISTS `tblDropClientTops`;
CREATE TABLE `tblDropClientTops` (
`NumId` int(10) unsigned NOT NULL auto_increment, `ClientId` int(10) default NULL, PRIMARY KEY (`NumId`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblDropCountryTops
-- ----------------------------
DROP TABLE IF EXISTS `tblDropCountryTops`;
CREATE TABLE `tblDropCountryTops` (
`NumId` int(10) unsigned NOT NULL auto_increment, `CountryId` int(10) default NULL, PRIMARY KEY (`NumId`)
) ENGINE=MyISAM AUTO_INCREMENT=22 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblEmailQueryHistory
-- ----------------------------
DROP TABLE IF EXISTS `tblEmailQueryHistory`;
CREATE TABLE `tblEmailQueryHistory` (
`Id` int(10) NOT NULL auto_increment, `ShipmentId` int(10) NOT NULL, `Date` datetime NOT NULL, `Subject` varchar(255) NOT NULL, `To` varchar(100) NOT NULL, `From` varchar(100) NOT NULL, `Body` varchar(255) NOT NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=168 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblEmailQueryHistoryCCAddresses
-- ----------------------------
DROP TABLE IF EXISTS `tblEmailQueryHistoryCCAddresses`;
CREATE TABLE `tblEmailQueryHistoryCCAddresses` (
`Id` int(10) NOT NULL auto_increment, `EmailQueryId` int(10) NOT NULL, `CCAddress` varchar(100) NOT NULL, PRIMARY KEY (`Id`), KEY `EmailQueryId` (`EmailQueryId`), CONSTRAINT `EmailQueryId_FK` FOREIGN KEY (`EmailQueryId`) REFERENCES `tblEmailQueryHistory` (`Id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=152 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblEstimatedTransitTime
-- ----------------------------
DROP TABLE IF EXISTS `tblEstimatedTransitTime`;
CREATE TABLE `tblEstimatedTransitTime` (
`Id` int(10) NOT NULL auto_increment, `TransportModeId` int(10) NOT NULL, `ClientCompanyId` int(10) NOT NULL, `OriginCountryId` int(10) NOT NULL, `DestinationCountryId` int(10) NOT NULL, `TransitDays` int(10) NOT NULL, PRIMARY KEY (`Id`), KEY `tblEstimatedTransitTime_ibfk_1` (`TransportModeId`), KEY `tblEstimatedTransitTime_ibfk_2` (`OriginCountryId`), KEY `tblEstimatedTransitTime_ibfk_3` (`DestinationCountryId`), KEY `tblEstimatedTransitTime_ibfk_4` (`ClientCompanyId`), CONSTRAINT `tblEstimatedTransitTime_ibfk_1` FOREIGN KEY (`TransportModeId`) REFERENCES `tblTransportMode` (`Id`), CONSTRAINT `tblEstimatedTransitTime_ibfk_2` FOREIGN KEY (`OriginCountryId`) REFERENCES `tblCountry` (`Id`), CONSTRAINT `tblEstimatedTransitTime_ibfk_3` FOREIGN KEY (`DestinationCountryId`) REFERENCES `tblCountry` (`Id`), CONSTRAINT `tblEstimatedTransitTime_ibfk_4` FOREIGN KEY (`ClientCompanyId`) REFERENCES `tblClientCompany` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblExchangeRate
-- ----------------------------
DROP TABLE IF EXISTS `tblExchangeRate`;
CREATE TABLE `tblExchangeRate` (
`Id` int(10) NOT NULL auto_increment, `ExchangeRateNumber` int(10) NOT NULL, `ThisCurrencyID` int(10) NOT NULL, `OtherCurrencyID` int(10) NOT NULL, `ExchangeRate` double NOT NULL, `LastUpdated` datetime NOT NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblFDAStatus
-- ----------------------------
DROP TABLE IF EXISTS `tblFDAStatus`;
CREATE TABLE `tblFDAStatus` (
`Id` int(10) NOT NULL auto_increment, `FDAStatusName` varchar(30) NOT NULL, `IsArchived` bit(1) NOT NULL default '\0', PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblGenericShipment
-- ----------------------------
DROP TABLE IF EXISTS `tblGenericShipment`;
CREATE TABLE `tblGenericShipment` (
`Id` int(10) NOT NULL auto_increment, `ClientCompanyId` int(10) NOT NULL, `ShipperAddressId` int(10) default NULL, `ShipperAddressDescription` varchar(1500) default NULL, `ConsigneeAddressId` int(10) default NULL, `ConsigneeAddressDescription` varchar(1500) default NULL, `NotifyAddressId` int(10) default NULL, `NotifyAddressDescription` varchar(1500) default NULL, `PickupAddressId` int(10) default NULL, `PickupAddressDescription` varchar(1500) default NULL, `GeneralDeliveryToAddressId` int(10) default NULL, `GeneralDeliveryToAddressDescription` varchar(1500) default NULL, `PickupComments` varchar(1500) default NULL, `PickupTruckerId` int(10) default NULL, `EstimatedPickupDate` date default NULL, `ActualPickupDate` date default NULL, `OriginPoint` varchar(30) default NULL, `DestinationPoint` varchar(30) default NULL, `TransportModeId` int(10) default NULL, `ClientModeId` int(10) default NULL, `ShippingTermId` int(10) default NULL, `ShippingTermLocation` varchar(30) default NULL, `CommodityDescription` varchar(1500) default NULL, `MarksOrNumber` varchar(1500) default NULL, `AuthorizedBy` varchar(30) default NULL, `AuthorizedDate` date default NULL, `EstimatedDeliveryDate` date default NULL, `AuthorizationMethodId` int(10) default NULL, `ExportRef` varchar(30) default NULL, `ImportRef` varchar(30) default NULL, `CustomerRef` varchar(30) default NULL, `Jaguar3rdRef` varchar(30) default NULL, `MasterId` int(10) default NULL, `IsDeleted` bit(1) NOT NULL default '\0', `LastModifiedUserId` int(10) default NULL, `CreatedOnDate` datetime default NULL, `IsDimsMode` tinyint(1) NOT NULL default '0', `TotalHUInCubicMeters` double default '0', `TotalHUInCubicFeets` double default '0', `OriginPointCountryId` int(10) default NULL, `DestinationPointCountryId` int(10) default NULL, `ShipmentGroupId` int(10) default NULL, `NotificationDate` date default NULL, `EnabledTransShipment` tinyint(1) default '0', `EnabledOriginTerminal` tinyint(1) default '0', `EnabledDestinationTerminal` tinyint(1) default '0', `ShowPickupAddress` tinyint(1) default '0', `ShowDeliveryToAddress` tinyint(1) default '0', `ShowOriginPointCountry` tinyint(1) default '0', `ShowDestinationPointCountry` tinyint(1) default '0', `AuthorizedMOT` int(10) default NULL, `TableOfChargesId` int(10) default NULL, `SalesPersonId` int(10) default NULL, PRIMARY KEY (`Id`), KEY `GenericShipment_ClientCompanyId` (`ClientCompanyId`), KEY `GenericShipment_ShipperAddressId` (`ShipperAddressId`), KEY `GenericShipment_ConsigneeAddressId` (`ConsigneeAddressId`), KEY `GenericShipment_NotifyAddressId` (`NotifyAddressId`), KEY `GenericShipment_PickupAddressId` (`PickupAddressId`), KEY `GenericShipment_GeneralDeliveryToAddressId` (`GeneralDeliveryToAddressId`), KEY `GenericShipment_PickupTruckerId` (`PickupTruckerId`), KEY `GenericShipment_TransportModeId` (`TransportModeId`), KEY `GenericShipment_ShippingTermId` (`ShippingTermId`), KEY `GenericShipment_AuthorizationMethodId` (`AuthorizationMethodId`), KEY `GenericShipment_MasterId` (`MasterId`), KEY `GenericShipment_LastModifiedUserId` (`LastModifiedUserId`), KEY `tblGenericShipment_ibfk_27` (`ClientModeId`), KEY `ShipmentGroupId` (`ShipmentGroupId`), KEY `SalesPersonId` (`SalesPersonId`), KEY `TableOfChargesId` (`TableOfChargesId`), CONSTRAINT `tblGenericShipment_fk` FOREIGN KEY (`TableOfChargesId`) REFERENCES `tblTableOfCharges` (`Id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `tblGenericShipment_ibfk_1` FOREIGN KEY (`ShipmentGroupId`) REFERENCES `tblShipmentGroup` (`Id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `tblGenericShipment_ibfk_10` FOREIGN KEY (`AuthorizationMethodId`) REFERENCES `tblAuthorizationMethod` (`Id`), CONSTRAINT `tblGenericShipment_ibfk_11` FOREIGN KEY (`SalesPersonId`) REFERENCES `tblJaguarUser` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblGenericShipment_ibfk_2` FOREIGN KEY (`ShipperAddressId`) REFERENCES `tblAddressTransportation` (`Id`), CONSTRAINT `tblGenericShipment_ibfk_25` FOREIGN KEY (`MasterId`) REFERENCES `tblMasterOcean` (`Id`) ON DELETE SET NULL, CONSTRAINT `tblGenericShipment_ibfk_26` FOREIGN KEY (`LastModifiedUserId`) REFERENCES `tblJaguarUser` (`Id`) ON DELETE SET NULL, CONSTRAINT `tblGenericShipment_ibfk_27` FOREIGN KEY (`ClientModeId`) REFERENCES `tblClientMode` (`Id`), CONSTRAINT `tblGenericShipment_ibfk_28` FOREIGN KEY (`PickupTruckerId`) REFERENCES `tblAddressJaguarVendor` (`Id`), CONSTRAINT `tblGenericShipment_ibfk_29` FOREIGN KEY (`ClientCompanyId`) REFERENCES `tblClientCompany` (`Id`), CONSTRAINT `tblGenericShipment_ibfk_3` FOREIGN KEY (`ConsigneeAddressId`) REFERENCES `tblAddressTransportation` (`Id`), CONSTRAINT `tblGenericShipment_ibfk_4` FOREIGN KEY (`NotifyAddressId`) REFERENCES `tblAddressTransportation` (`Id`), CONSTRAINT `tblGenericShipment_ibfk_5` FOREIGN KEY (`GeneralDeliveryToAddressId`) REFERENCES `tblAddressTransportation` (`Id`), CONSTRAINT `tblGenericShipment_ibfk_6` FOREIGN KEY (`PickupAddressId`) REFERENCES `tblAddressTransportation` (`Id`), CONSTRAINT `tblGenericShipment_ibfk_8` FOREIGN KEY (`TransportModeId`) REFERENCES `tblTransportMode` (`Id`), CONSTRAINT `tblGenericShipment_ibfk_9` FOREIGN KEY (`ShippingTermId`) REFERENCES `tblShippingTerm` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=251003 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblGenericUser
-- ----------------------------
DROP TABLE IF EXISTS `tblGenericUser`;
CREATE TABLE `tblGenericUser` (
`Id` int(10) NOT NULL auto_increment, `Password` varchar(30) NOT NULL, `Active` bit(1) NOT NULL, `FirstName` varchar(30) NOT NULL, `LastName` varchar(30) NOT NULL, `Birthday` date default NULL, `Email` varchar(50) NOT NULL, `PhoneNumber` varchar(15) NOT NULL, `RSSFeedId` int(10) default '2', `SkypeId` varchar(255) default NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=159 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblGroupObjectPermission
-- ----------------------------
DROP TABLE IF EXISTS `tblGroupObjectPermission`;
CREATE TABLE `tblGroupObjectPermission` (
`Id` int(10) NOT NULL auto_increment, `GroupObjectID` int(10) NOT NULL, `CanCreate` int(1) NOT NULL default '0', `CanView` int(1) NOT NULL default '0', `CanModify` int(1) NOT NULL default '0', `CanDelete` int(1) NOT NULL default '0', PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblHandlingRemark
-- ----------------------------
DROP TABLE IF EXISTS `tblHandlingRemark`;
CREATE TABLE `tblHandlingRemark` (
`Id` int(10) NOT NULL auto_increment, `HandlingRemark` varchar(150) NOT NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblHandlingUnit
-- ----------------------------
DROP TABLE IF EXISTS `tblHandlingUnit`;
CREATE TABLE `tblHandlingUnit` (
`Id` int(10) NOT NULL auto_increment, `ShipmentId` int(10) NOT NULL, `Quantity` int(10) NOT NULL, `Length` double NOT NULL, `Width` double NOT NULL, `Height` double NOT NULL, `UnitId` int(10) NOT NULL, PRIMARY KEY (`Id`), KEY `ShipmentId` (`ShipmentId`), CONSTRAINT `tblHandlingUnit_ibfk_1` FOREIGN KEY (`ShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=200 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblJaguarOpsGroup
-- ----------------------------
DROP TABLE IF EXISTS `tblJaguarOpsGroup`;
CREATE TABLE `tblJaguarOpsGroup` (
`Id` int(10) NOT NULL auto_increment, `GroupName` varchar(30) NOT NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblJaguarOpsGroupClientRelation
-- ----------------------------
DROP TABLE IF EXISTS `tblJaguarOpsGroupClientRelation`;
CREATE TABLE `tblJaguarOpsGroupClientRelation` (
`Id` int(10) NOT NULL auto_increment, `JaguarOpsGroupId` int(10) NOT NULL, `ClientUserId` int(10) NOT NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblJaguarOpsGroupCompanyRelation
-- ----------------------------
DROP TABLE IF EXISTS `tblJaguarOpsGroupCompanyRelation`;
CREATE TABLE `tblJaguarOpsGroupCompanyRelation` (
`Id` int(10) NOT NULL auto_increment, `JaguarOpsGroupId` int(10) NOT NULL, `ClientCompanyId` int(10) NOT NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblJaguarUser
-- ----------------------------
DROP TABLE IF EXISTS `tblJaguarUser`;
CREATE TABLE `tblJaguarUser` (
`Id` int(10) NOT NULL auto_increment, `UserId` int(10) NOT NULL, `OfficeId` int(10) NOT NULL, `TypeId` int(10) NOT NULL, `GroupId` int(10) default NULL, `IsBCC` tinyint(1) NOT NULL default '0', `IsSalesPerson` bit(1) NOT NULL default '\0', PRIMARY KEY (`Id`), UNIQUE KEY `UserId` (`UserId`), KEY `OfficeId` (`OfficeId`), KEY `TypeId` (`TypeId`), KEY `tblJaguarUser_ibfk_4` (`GroupId`), CONSTRAINT `tblJaguarUser_ibfk_1` FOREIGN KEY (`UserId`) REFERENCES `tblGenericUser` (`Id`), CONSTRAINT `tblJaguarUser_ibfk_2` FOREIGN KEY (`OfficeId`) REFERENCES `tblOffice` (`Id`), CONSTRAINT `tblJaguarUser_ibfk_3` FOREIGN KEY (`TypeId`) REFERENCES `tblJaguarUserType` (`Id`), CONSTRAINT `tblJaguarUser_ibfk_4` FOREIGN KEY (`GroupId`) REFERENCES `tblJaguarUserGroup` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=124 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblJaguarUserBCCList
-- ----------------------------
DROP TABLE IF EXISTS `tblJaguarUserBCCList`;
CREATE TABLE `tblJaguarUserBCCList` (
`Id` int(10) NOT NULL auto_increment, `JaguarUserId` int(10) NOT NULL, `ClientUserAccounting` tinyint(1) default '1', `ClientUserCommCenter` tinyint(1) default '1', `ShipmentQueries` tinyint(1) default '1', `ClientUserShipmentAuthorization` tinyint(1) default '1', PRIMARY KEY (`Id`), KEY `JaguarUserId` (`JaguarUserId`), CONSTRAINT `tblJaguarUserBCCList_fk` FOREIGN KEY (`JaguarUserId`) REFERENCES `tblJaguarUser` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblJaguarUserGroup
-- ----------------------------
DROP TABLE IF EXISTS `tblJaguarUserGroup`;
CREATE TABLE `tblJaguarUserGroup` (
`Id` int(10) NOT NULL auto_increment, `Name` varchar(30) NOT NULL, `OfficeId` int(10) NOT NULL, PRIMARY KEY (`Id`), KEY `OfficeId` (`OfficeId`), CONSTRAINT `OfficeId` FOREIGN KEY (`OfficeId`) REFERENCES `tblOffice` (`Id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblJaguarUserType
-- ----------------------------
DROP TABLE IF EXISTS `tblJaguarUserType`;
CREATE TABLE `tblJaguarUserType` (
`Id` int(10) NOT NULL auto_increment, `Name` varchar(30) NOT NULL, `Authority` varchar(15) NOT NULL, PRIMARY KEY (`Id`), UNIQUE KEY `Name` (`Name`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblLoose
-- ----------------------------
DROP TABLE IF EXISTS `tblLoose`;
CREATE TABLE `tblLoose` (
`Id` int(10) NOT NULL auto_increment, `Name` varchar(50) NOT NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblMailTemplates
-- ----------------------------
DROP TABLE IF EXISTS `tblMailTemplates`;
CREATE TABLE `tblMailTemplates` (
`Id` int(10) NOT NULL auto_increment, `Header` varchar(100) NOT NULL, `Footer` varchar(100) NOT NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblMaster
-- ----------------------------
DROP TABLE IF EXISTS `tblMaster`;
CREATE TABLE `tblMaster` (
`Id` int(10) NOT NULL auto_increment, `MasterTypeCode` int(10) NOT NULL, `CreateDate` date NOT NULL, `ShipperId` int(10) default NULL, `ShipperDescription` varchar(100) default NULL, `ConsigneeId` int(10) default NULL, `ConsigneeDescripion` varchar(100) default NULL, `NotifyId` int(10) default NULL, `NotifyDescription` varchar(100) default NULL, `OriginTruckerId` int(10) default NULL, `DestinationTruckerId` int(10) default NULL, `ShipperRef` varchar(20) default NULL, `ConsigneeRef` varchar(20) default NULL, `TransportModeId` int(10) default NULL, `PickUpId` int(10) default NULL, `PickUpDescription` varchar(100) default NULL, `DeliveryAddressId` int(10) default NULL, `DeliveryAddressDescription` varchar(100) default NULL, `MarksNumbers` varchar(100) default NULL, `CommodityDescription` varchar(100) default NULL, `PickUpComments` varchar(100) default NULL, `DeliveryComments` varchar(100) default NULL, `Freight` int(1) default '0', `MoveTypeId` int(10) default NULL, `SteamshiplineId` int(10) default NULL, `CoLoaderOceanId` int(10) default NULL, `Booking` varchar(30) default NULL, `MasterBillOfLoading` varchar(30) default NULL, `AMSSubmissionDate` date default NULL, `VesselName` varchar(30) default NULL, `Voyage1` varchar(30) default NULL, `FeederVesselname` varchar(30) default NULL, `Voyage2` varchar(30) default NULL, `OriginalTerminalId` int(10) default NULL, `OriginalTerminalETD` date default NULL, `OriginalTerminalATD` date default NULL, `PortOfLoadingId` int(10) default NULL, `PortOfLoadingETD` date default NULL, `PortOfLoadingATD` date default NULL, `PortOfTransShipmentId` int(10) default NULL, `PortOfTransShipmentETD` date default NULL, `PortOfTransShipmentATD` date default NULL, `PortOfDischargeId` int(10) default NULL, `PortOfDischargeETD` date default NULL, `PortOfDischargeATD` date default NULL, `DestinationalTerminalId` int(10) default NULL, `DestinationalTerminalETD` date default NULL, `DestinationalTerminalATD` date default NULL, `DestinationReceivingAgentId` int(10) default NULL, `MasterBillComments` varchar(150) default NULL, PRIMARY KEY (`Id`), KEY `ShipperId` (`ShipperId`), KEY `ConsigneeId` (`ConsigneeId`), KEY `NotifyId` (`NotifyId`), KEY `OriginTruckerId` (`OriginTruckerId`), KEY `DestinationTruckerId` (`DestinationTruckerId`), KEY `TransportModeId` (`TransportModeId`), KEY `PickUpId` (`PickUpId`), KEY `DeliveryAddressId` (`DeliveryAddressId`), KEY `MoveTypeId` (`MoveTypeId`), KEY `SteamshiplineId` (`SteamshiplineId`), KEY `CoLoaderOceanId` (`CoLoaderOceanId`), KEY `OriginalTerminalId` (`OriginalTerminalId`), KEY `PortOfLoadingId` (`PortOfLoadingId`), KEY `PortOfTransShipmentId` (`PortOfTransShipmentId`), KEY `PortOfDischargeId` (`PortOfDischargeId`), KEY `DestinationalTerminalId` (`DestinationalTerminalId`), KEY `DestinationReceivingAgentId` (`DestinationReceivingAgentId`), CONSTRAINT `tblMaster_ibfk_1` FOREIGN KEY (`ShipperId`) REFERENCES `tblAddressTransportation` (`Id`), CONSTRAINT `tblMaster_ibfk_10` FOREIGN KEY (`SteamshiplineId`) REFERENCES `tblSteamshipline` (`Id`), CONSTRAINT `tblMaster_ibfk_2` FOREIGN KEY (`ConsigneeId`) REFERENCES `tblAddressTransportation` (`Id`), CONSTRAINT `tblMaster_ibfk_3` FOREIGN KEY (`NotifyId`) REFERENCES `tblAddressTransportation` (`Id`), CONSTRAINT `tblMaster_ibfk_4` FOREIGN KEY (`OriginTruckerId`) REFERENCES `tblAddressJaguarVendor` (`Id`), CONSTRAINT `tblMaster_ibfk_5` FOREIGN KEY (`DestinationTruckerId`) REFERENCES `tblAddressJaguarVendor` (`Id`), CONSTRAINT `tblMaster_ibfk_6` FOREIGN KEY (`TransportModeId`) REFERENCES `tblMasterTransportMode` (`Id`), CONSTRAINT `tblMaster_ibfk_7` FOREIGN KEY (`PickUpId`) REFERENCES `tblAddressTransportation` (`Id`), CONSTRAINT `tblMaster_ibfk_8` FOREIGN KEY (`DeliveryAddressId`) REFERENCES `tblAddressTransportation` (`Id`), CONSTRAINT `tblMaster_ibfk_9` FOREIGN KEY (`MoveTypeId`) REFERENCES `tblMoveType` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblMasterComments
-- ----------------------------
DROP TABLE IF EXISTS `tblMasterComments`;
CREATE TABLE `tblMasterComments` (
`Id` int(10) NOT NULL auto_increment, `MasterId` int(10) NOT NULL, `ReplyDateTime` datetime NOT NULL, `UserId` int(10) NOT NULL, `Body` varchar(150) NOT NULL default , PRIMARY KEY (`Id`), KEY `MasterId` (`MasterId`), KEY `UserId` (`UserId`), CONSTRAINT `tblMasterComments_ibfk_1` FOREIGN KEY (`MasterId`) REFERENCES `tblMasterOcean` (`Id`) ON DELETE CASCADE, CONSTRAINT `tblMasterComments_ibfk_2` FOREIGN KEY (`UserId`) REFERENCES `tblGenericUser` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblMasterCommentsMailedTo
-- ----------------------------
DROP TABLE IF EXISTS `tblMasterCommentsMailedTo`;
CREATE TABLE `tblMasterCommentsMailedTo` (
`CommentId` int(10) NOT NULL, `UserId` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblMasterOcean
-- ----------------------------
DROP TABLE IF EXISTS `tblMasterOcean`;
CREATE TABLE `tblMasterOcean` (
`Id` int(10) NOT NULL auto_increment, `MasterTypeCode` int(10) NOT NULL, `CreateDate` datetime NOT NULL, `ShipperId` int(10) default NULL, `ShipperDescription` varchar(1500) default NULL, `ConsigneeId` int(10) default NULL, `ConsigneeDescripion` varchar(1500) default NULL, `NotifyId` int(10) default NULL, `NotifyDescription` varchar(1500) default NULL, `OriginTruckerId` int(10) default NULL, `DestinationTruckerId` int(10) default NULL, `ShipperRef` varchar(20) default NULL, `ConsigneeRef` varchar(20) default NULL, `TransportModeId` int(10) default NULL, `PickUpId` int(10) default NULL, `PickUpDescription` varchar(1500) default NULL, `DeliveryAddressId` int(10) default NULL, `DeliveryAddressDescription` varchar(1500) default NULL, `MarksNumbers` varchar(1500) default NULL, `CommodityDescription` varchar(1500) default NULL, `PickUpComments` varchar(1500) default NULL, `DeliveryComments` varchar(1500) default NULL, `Freight` int(1) default '0', `MoveTypeId` int(10) default NULL, `SteamshiplineId` int(10) default NULL, `CoLoaderOceanId` int(10) default NULL, `Booking` varchar(30) default NULL, `MasterBillOfLoading` varchar(30) default NULL, `AMSSubmissionDate` date default NULL, `VesselName` varchar(30) default NULL, `Voyage1` varchar(30) default NULL, `FeederVesselname` varchar(30) default NULL, `Voyage2` varchar(30) default NULL, `OriginalTerminalId` int(10) default NULL, `OriginalTerminalETD` date default NULL, `OriginalTerminalATD` date default NULL, `PortOfLoadingId` int(10) default NULL, `PortOfLoadingETD` date default NULL, `PortOfLoadingATD` date default NULL, `PortOfTransShipmentId` int(10) default NULL, `PortOfTransShipmentETD` date default NULL, `PortOfTransShipmentATD` date default NULL, `PortOfDischargeId` int(10) default NULL, `PortOfDischargeETD` date default NULL, `PortOfDischargeATD` date default NULL, `DestinationalTerminalId` int(10) default NULL, `DestinationalTerminalETD` date default NULL, `DestinationalTerminalATD` date default NULL, `DestinationReceivingAgentId` int(10) default NULL, `MasterBillComments` varchar(1500) default NULL, `AirlineId` int(10) default NULL, `CoLoaderAirId` int(10) default NULL, `MasterAirwayBill` varchar(30) default NULL, `MasterAirwayBillA` varchar(3) default NULL, `MasterAirwayBillB` varchar(4) default NULL, `MasterAirwayBillC` varchar(4) default NULL, `MasterAirwayBillComment` varchar(1500) default NULL, `Flight1` varchar(30) default NULL, `Flight2` varchar(30) default NULL, `AirportOfDepartureId` int(10) default NULL, `AirportOfDepartureETD` date default NULL, `AirportOfDepartureATD` date default NULL, `TransShipmentAirportId` int(10) default NULL, `TransShipmentAirportETA` date default NULL, `TransShipmentAirportATA` date default NULL, `AirportOfDestinationId` int(10) default NULL, `AirportOfDestinationETA` date default NULL, `AirportOfDestinationATA` date default NULL, PRIMARY KEY (`Id`), KEY `ShipperId` (`ShipperId`), KEY `ConsigneeId` (`ConsigneeId`), KEY `NotifyId` (`NotifyId`), KEY `OriginTruckerId` (`OriginTruckerId`), KEY `DestinationTruckerId` (`DestinationTruckerId`), KEY `PickUpId` (`PickUpId`), KEY `DeliveryAddressId` (`DeliveryAddressId`), KEY `TransportModeId` (`TransportModeId`), KEY `tblMasterOcean_ibfk_9` (`AirlineId`), KEY `tblMasterOcean_ibfk_10` (`CoLoaderAirId`), KEY `tblMasterOcean_ibfk_11` (`AirportOfDepartureId`), KEY `tblMasterOcean_ibfk_12` (`TransShipmentAirportId`), KEY `tblMasterOcean_ibfk_13` (`AirportOfDestinationId`), KEY `tblMasterOcean_ibfk_14` (`CoLoaderOceanId`), KEY `tblMasterOcean_ibfk_15` (`DestinationReceivingAgentId`), KEY `tblMasterOcean_ibfk_16` (`MoveTypeId`), KEY `tblMasterOcean_ibfk_17` (`SteamshiplineId`), KEY `tblMasterOcean_ibfk_18` (`OriginalTerminalId`), KEY `tblMasterOcean_ibfk_19` (`PortOfLoadingId`), KEY `tblMasterOcean_ibfk_20` (`PortOfTransShipmentId`), KEY `tblMasterOcean_ibfk_21` (`PortOfDischargeId`), KEY `tblMasterOcean_ibfk_22` (`DestinationalTerminalId`), CONSTRAINT `tblMasterOcean_ibfk_1` FOREIGN KEY (`ShipperId`) REFERENCES `tblAddressTransportation` (`Id`), CONSTRAINT `tblMasterOcean_ibfk_10` FOREIGN KEY (`CoLoaderAirId`) REFERENCES `tblAddressJaguarVendor` (`Id`), CONSTRAINT `tblMasterOcean_ibfk_11` FOREIGN KEY (`AirportOfDepartureId`) REFERENCES `tblAirport` (`Id`), CONSTRAINT `tblMasterOcean_ibfk_12` FOREIGN KEY (`TransShipmentAirportId`) REFERENCES `tblAirport` (`Id`), CONSTRAINT `tblMasterOcean_ibfk_13` FOREIGN KEY (`AirportOfDestinationId`) REFERENCES `tblAirport` (`Id`), CONSTRAINT `tblMasterOcean_ibfk_14` FOREIGN KEY (`CoLoaderOceanId`) REFERENCES `tblAddressJaguarVendor` (`Id`), CONSTRAINT `tblMasterOcean_ibfk_15` FOREIGN KEY (`DestinationReceivingAgentId`) REFERENCES `tblAddressTransportation` (`Id`), CONSTRAINT `tblMasterOcean_ibfk_16` FOREIGN KEY (`MoveTypeId`) REFERENCES `tblMoveType` (`Id`), CONSTRAINT `tblMasterOcean_ibfk_17` FOREIGN KEY (`SteamshiplineId`) REFERENCES `tblSteamshipline` (`Id`), CONSTRAINT `tblMasterOcean_ibfk_18` FOREIGN KEY (`OriginalTerminalId`) REFERENCES `tblSeaPort` (`Id`), CONSTRAINT `tblMasterOcean_ibfk_19` FOREIGN KEY (`PortOfLoadingId`) REFERENCES `tblSeaPort` (`Id`), CONSTRAINT `tblMasterOcean_ibfk_2` FOREIGN KEY (`ConsigneeId`) REFERENCES `tblAddressTransportation` (`Id`), CONSTRAINT `tblMasterOcean_ibfk_20` FOREIGN KEY (`PortOfTransShipmentId`) REFERENCES `tblSeaPort` (`Id`), CONSTRAINT `tblMasterOcean_ibfk_21` FOREIGN KEY (`PortOfDischargeId`) REFERENCES `tblSeaPort` (`Id`), CONSTRAINT `tblMasterOcean_ibfk_22` FOREIGN KEY (`DestinationalTerminalId`) REFERENCES `tblSeaPort` (`Id`), CONSTRAINT `tblMasterOcean_ibfk_3` FOREIGN KEY (`NotifyId`) REFERENCES `tblAddressTransportation` (`Id`), CONSTRAINT `tblMasterOcean_ibfk_4` FOREIGN KEY (`OriginTruckerId`) REFERENCES `tblAddressJaguarVendor` (`Id`), CONSTRAINT `tblMasterOcean_ibfk_5` FOREIGN KEY (`DestinationTruckerId`) REFERENCES `tblAddressJaguarVendor` (`Id`), CONSTRAINT `tblMasterOcean_ibfk_6` FOREIGN KEY (`PickUpId`) REFERENCES `tblAddressTransportation` (`Id`), CONSTRAINT `tblMasterOcean_ibfk_7` FOREIGN KEY (`DeliveryAddressId`) REFERENCES `tblAddressTransportation` (`Id`), CONSTRAINT `tblMasterOcean_ibfk_8` FOREIGN KEY (`TransportModeId`) REFERENCES `tblMasterTransportMode` (`Id`), CONSTRAINT `tblMasterOcean_ibfk_9` FOREIGN KEY (`AirlineId`) REFERENCES `tblAirline` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=452 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblMasterPDFReports
-- ----------------------------
DROP TABLE IF EXISTS `tblMasterPDFReports`;
CREATE TABLE `tblMasterPDFReports` (
`Id` int(10) NOT NULL auto_increment, `Name` varchar(50) NOT NULL, `MasterId` int(10) NOT NULL, `Path` varchar(150) NOT NULL, `IssueDate` date default NULL, `IssuedBy` varchar(50) NOT NULL, PRIMARY KEY (`Id`), KEY `tblMasterPDFReports_ibfk_01` (`MasterId`), CONSTRAINT `tblMasterPDFReports_ibfk_01` FOREIGN KEY (`MasterId`) REFERENCES `tblMasterOcean` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblMasterTransportMode
-- ----------------------------
DROP TABLE IF EXISTS `tblMasterTransportMode`;
CREATE TABLE `tblMasterTransportMode` (
`Id` int(10) NOT NULL auto_increment, `TransportModeValue` varchar(30) NOT NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblMoveType
-- ----------------------------
DROP TABLE IF EXISTS `tblMoveType`;
CREATE TABLE `tblMoveType` (
`Id` int(10) NOT NULL auto_increment, `Type` varchar(30) NOT NULL, `IsArchived` bit(1) NOT NULL default '\0', PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblOceanModeType
-- ----------------------------
DROP TABLE IF EXISTS `tblOceanModeType`;
CREATE TABLE `tblOceanModeType` (
`Id` int(10) NOT NULL auto_increment, `Type` varchar(20) NOT NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblOceanShipment
-- ----------------------------
DROP TABLE IF EXISTS `tblOceanShipment`;
CREATE TABLE `tblOceanShipment` (
`OceanShipmentId` int(10) NOT NULL auto_increment, `GenericShipmentId` int(10) NOT NULL, `Hazardous` tinyint(1) NOT NULL default '0', `HazardousNote` varchar(1500) default NULL, `Freight` int(1) default '0', `MoveTypeId` int(10) default NULL, `OriginalHouseBill` int(1) default '0', `SteamshiplineId` int(10) default NULL, `CoLoaderOceanId` int(10) default NULL, `Booking` varchar(30) default NULL, `MasterBill` varchar(30) default NULL, `AMSSubmissionDate` date default NULL, `VesselName` varchar(30) default NULL, `Voyage1` varchar(30) default NULL, `FeederVesselname` varchar(30) default NULL, `Voyage2` varchar(30) default NULL, `OriginalTerminalId` int(10) default NULL, `OriginalTerminalETD` date default NULL, `OriginalTerminalATD` date default NULL, `PortOfLoadingId` int(10) default NULL, `PortOfLoadingETD` date default NULL, `PortOfLoadingATD` date default NULL, `PortOfTransShipmentId` int(10) default NULL, `PortOfTransShipmentETA` date default NULL, `PortOfTransShipmentATA` date default NULL, `PortOfDischargeId` int(10) default NULL, `PortOfDischargeETA` date default NULL, `PortOfDischargeATA` date default NULL, `DestinationalTerminalId` int(10) default NULL, `DestinationalTerminalETA` date default NULL, `DestinationalTerminalATA` date default NULL, `DestinationReceivingAgentId` int(10) default NULL, `HouseBillComments` varchar(1500) default NULL, `EnabledArrivalToWH` tinyint(1) default '1', `ArrivalToWarehouseDate` date default NULL, `ItNumber` varchar(17) default NULL, `ItDate` date default NULL, `ItLocation` varchar(20) default NULL, `CustomsBrokerID` int(10) default NULL, `CustomsBrokerDescription` varchar(1500) default NULL, `DeliveryAddressId` int(10) default NULL, `DeliveryAddressDescription` varchar(1500) default NULL, `DeliveryComments` varchar(1500) default NULL, `LastFreeDay` date default NULL, `CargoLocationId` int(10) default NULL, `CargoLocationDescription` varchar(1500) default NULL, `DeliveryTruckerId` int(10) default NULL, `ImportEstimatedDeliveryDate` date default NULL, `ActualDeliveryDate` date default NULL, `ArrivalNoticeComments` varchar(1500) default NULL, `OriginalBillLandingRequired` int(1) default '0', `PaymentFreightChargesRequired` int(1) default '0', `CustomsStatusId` int(10) default NULL, `CustomsStatusDate` date default NULL, `FDAStatusId` int(10) default NULL, `FDAStatusDate` date default NULL, `HouseBill` varchar(30) default NULL, `DeliveryDateTypeId` int(10) default NULL, `JaguarDelivery` tinyint(1) default '0', `ExportCustoms` varchar(30) default NULL, `ActualDeliveryDateEnabled` tinyint(1) default '0', PRIMARY KEY (`OceanShipmentId`), KEY `OceanShipment_GenericShipmentId` (`GenericShipmentId`), KEY `OceanShipment_MoveTypeId` (`MoveTypeId`), KEY `OceanShipment_SteamshiplineId` (`SteamshiplineId`), KEY `OceanShipment_CoLoaderOceanId` (`CoLoaderOceanId`), KEY `OceanShipment_OriginalTerminalId` (`OriginalTerminalId`), KEY `OceanShipment_PortOfLoadingId` (`PortOfLoadingId`), KEY `OceanShipment_PortOfTransShipmentId` (`PortOfTransShipmentId`), KEY `OceanShipment_PortOfDischargeId` (`PortOfDischargeId`), KEY `OceanShipment_DestinationalTerminalId` (`DestinationalTerminalId`), KEY `OceanShipment_DestinationReceivingAgentId` (`DestinationReceivingAgentId`), KEY `OceanShipment_CustomsBrokerID` (`CustomsBrokerID`), KEY `OceanShipment_DeliveryAddressId` (`DeliveryAddressId`), KEY `OceanShipment_CargoLocationId` (`CargoLocationId`), KEY `OceanShipment_FDAStatusId` (`FDAStatusId`), KEY `OceanShipment_CustomsStatusId` (`CustomsStatusId`), KEY `tblOceanShipment_ibfk_27` (`DeliveryTruckerId`), CONSTRAINT `tblOceanShipment_ibfk_27` FOREIGN KEY (`DeliveryTruckerId`) REFERENCES `tblAddressJaguarVendor` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblOceanShipment_ibfk_11` FOREIGN KEY (`MoveTypeId`) REFERENCES `tblMoveType` (`Id`), CONSTRAINT `tblOceanShipment_ibfk_12` FOREIGN KEY (`SteamshiplineId`) REFERENCES `tblSteamshipline` (`Id`), CONSTRAINT `tblOceanShipment_ibfk_13` FOREIGN KEY (`CoLoaderOceanId`) REFERENCES `tblAddressJaguarVendor` (`Id`), CONSTRAINT `tblOceanShipment_ibfk_14` FOREIGN KEY (`OriginalTerminalId`) REFERENCES `tblSeaPort` (`Id`), CONSTRAINT `tblOceanShipment_ibfk_15` FOREIGN KEY (`PortOfLoadingId`) REFERENCES `tblSeaPort` (`Id`), CONSTRAINT `tblOceanShipment_ibfk_16` FOREIGN KEY (`PortOfTransShipmentId`) REFERENCES `tblSeaPort` (`Id`), CONSTRAINT `tblOceanShipment_ibfk_17` FOREIGN KEY (`PortOfDischargeId`) REFERENCES `tblSeaPort` (`Id`), CONSTRAINT `tblOceanShipment_ibfk_18` FOREIGN KEY (`DestinationalTerminalId`) REFERENCES `tblSeaPort` (`Id`), CONSTRAINT `tblOceanShipment_ibfk_19` FOREIGN KEY (`DestinationReceivingAgentId`) REFERENCES `tblAddressTransportation` (`Id`), CONSTRAINT `tblOceanShipment_ibfk_20` FOREIGN KEY (`CustomsBrokerID`) REFERENCES `tblAddressJaguarVendor` (`Id`), CONSTRAINT `tblOceanShipment_ibfk_21` FOREIGN KEY (`DeliveryAddressId`) REFERENCES `tblAddressTransportation` (`Id`), CONSTRAINT `tblOceanShipment_ibfk_22` FOREIGN KEY (`CargoLocationId`) REFERENCES `tblAddressJaguarVendor` (`Id`), CONSTRAINT `tblOceanShipment_ibfk_23` FOREIGN KEY (`FDAStatusId`) REFERENCES `tblFDAStatus` (`Id`), CONSTRAINT `tblOceanShipment_ibfk_24` FOREIGN KEY (`CustomsStatusId`) REFERENCES `tblCustomsStatus` (`Id`), CONSTRAINT `tblOceanShipment_ibfk_26` FOREIGN KEY (`GenericShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=705 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblOffice
-- ----------------------------
DROP TABLE IF EXISTS `tblOffice`;
CREATE TABLE `tblOffice` (
`Id` int(10) NOT NULL auto_increment, `City` varchar(30) NOT NULL, `Flag` varchar(30) default NULL, `Abbreviation` varchar(3) default , `Address` varchar(1000) default NULL, `CurrencyId` int(10) default NULL, `VAT` double default NULL, `ImageLogo` blob, `Name` varchar(60) NOT NULL default , `PhoneNumber` varchar(15) default NULL, `FaxNumber` varchar(15) default NULL, `Email` varchar(50) default NULL, `IsMainOffice` bit(1) NOT NULL default '\0', PRIMARY KEY (`Id`), KEY `CurrencyId` (`CurrencyId`), CONSTRAINT `tblOffice_fk` FOREIGN KEY (`CurrencyId`) REFERENCES `tblCurrency` (`Id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblOnPallets
-- ----------------------------
DROP TABLE IF EXISTS `tblOnPallets`;
CREATE TABLE `tblOnPallets` (
`Id` int(10) NOT NULL auto_increment, `Name` varchar(50) NOT NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblPackagingCommodityType
-- ----------------------------
DROP TABLE IF EXISTS `tblPackagingCommodityType`;
CREATE TABLE `tblPackagingCommodityType` (
`Id` int(10) NOT NULL auto_increment, `Description` varchar(30) default NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblPackagingPalletType
-- ----------------------------
DROP TABLE IF EXISTS `tblPackagingPalletType`;
CREATE TABLE `tblPackagingPalletType` (
`Id` int(10) NOT NULL auto_increment, `Description` varchar(30) default NULL, `IsArchived` bit(1) NOT NULL default '\0', PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblPackagingType
-- ----------------------------
DROP TABLE IF EXISTS `tblPackagingType`;
CREATE TABLE `tblPackagingType` (
`Id` int(10) NOT NULL auto_increment, `LooseID` int(10) NOT NULL, `OnPalletsID` int(10) NOT NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblPDFReports
-- ----------------------------
DROP TABLE IF EXISTS `tblPDFReports`;
CREATE TABLE `tblPDFReports` (
`Id` int(10) NOT NULL auto_increment, `Name` varchar(50) NOT NULL, `ShipmentId` int(10) NOT NULL, `Path` varchar(150) NOT NULL, `ShowToClient` tinyint(1) NOT NULL default '0', `IssueDate` date default NULL, `IssuedBy` varchar(50) NOT NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=214 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblPurchaseInvoice
-- ----------------------------
DROP TABLE IF EXISTS `tblPurchaseInvoice`;
CREATE TABLE `tblPurchaseInvoice` (
`Id` int(10) NOT NULL auto_increment, `VendorId` int(10) default NULL, `Number` varchar(20) default NULL, `CurrencyId` int(10) default NULL, `Amount` double default NULL, `OfficeId` int(10) default NULL, `DateTime` datetime NOT NULL, `Note` varchar(1024) default NULL, PRIMARY KEY (`Id`), UNIQUE KEY `uniqueOfficeIdVendorIdNumber` (`OfficeId`,`VendorId`,`Number`), KEY `VendorId` (`VendorId`), KEY `CurrencyId` (`CurrencyId`), KEY `OfficeId` (`OfficeId`), CONSTRAINT `tblPurchaseInvoices_fk` FOREIGN KEY (`VendorId`) REFERENCES `tblAddressJaguarVendor` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblPurchaseInvoice_fk` FOREIGN KEY (`CurrencyId`) REFERENCES `tblCurrency` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblPurchaseInvoice_fk1` FOREIGN KEY (`OfficeId`) REFERENCES `tblOffice` (`Id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=224 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblPurchaseInvoiceChargeCode
-- ----------------------------
DROP TABLE IF EXISTS `tblPurchaseInvoiceChargeCode`;
CREATE TABLE `tblPurchaseInvoiceChargeCode` (
`Id` int(10) NOT NULL auto_increment, `PurchaseInvoiceId` int(10) default NULL, `ChargeCodeId` int(10) default NULL, `Description` varchar(150) default NULL, `Amount` double default NULL, `ShipmentId` int(10) default NULL, `MasterId` int(10) default NULL, `ChargeBackAmount` double default NULL, `ChargeBackInvoiceId` int(10) default NULL, `IsM` tinyint(1) NOT NULL default '0', `CBVType` smallint(6) NOT NULL default '0', PRIMARY KEY (`Id`), KEY `PurchaseInvoiceId` (`PurchaseInvoiceId`), KEY `ChargeCodeId` (`ChargeCodeId`), KEY `ChargeBackInvoiceId` (`ChargeBackInvoiceId`), KEY `CBVType` (`CBVType`), KEY `ShipmentId` (`ShipmentId`), KEY `MasterId` (`MasterId`), CONSTRAINT `tblPurchaseInvoiceChargeCode_fk` FOREIGN KEY (`PurchaseInvoiceId`) REFERENCES `tblPurchaseInvoice` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `tblPurchaseInvoiceChargeCode_fk1` FOREIGN KEY (`ChargeCodeId`) REFERENCES `tblChargeCode` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblPurchaseInvoiceChargeCode_fk2` FOREIGN KEY (`ShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblPurchaseInvoiceChargeCode_fk3` FOREIGN KEY (`MasterId`) REFERENCES `tblMasterOcean` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblPurchaseInvoiceChargeCode_ibfk_1` FOREIGN KEY (`ChargeBackInvoiceId`) REFERENCES `tblChargeBackInvoice` (`Id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=398 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblRegion
-- ----------------------------
DROP TABLE IF EXISTS `tblRegion`;
CREATE TABLE `tblRegion` (
`Id` int(10) NOT NULL auto_increment, `Name` varchar(30) NOT NULL, `IsArchived` bit(1) NOT NULL default '\0', `OfficeId` int(10) default NULL, PRIMARY KEY (`Id`), KEY `OfficeId` (`OfficeId`), CONSTRAINT `tblRegion_fk` FOREIGN KEY (`OfficeId`) REFERENCES `tblOffice` (`Id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=427 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblReport
-- ----------------------------
DROP TABLE IF EXISTS `tblReport`;
CREATE TABLE `tblReport` (
`Id` int(10) NOT NULL auto_increment, `Description` varchar(100) NOT NULL default , `ClientCompanyId` int(10) default NULL, `CreatedOnDate` datetime NOT NULL, `ModifiedOnDate` datetime default NULL, `LastModifiedUserId` int(10) NOT NULL, `FormObject` blob, `ScheduleObject` blob NOT NULL, `LastGenerationDataObject` blob, PRIMARY KEY (`Id`), KEY `ClientCompanyId` (`ClientCompanyId`), KEY `LastModifiedUserId` (`LastModifiedUserId`), CONSTRAINT `tblReport_fk` FOREIGN KEY (`ClientCompanyId`) REFERENCES `tblClientCompany` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblReport_fk1` FOREIGN KEY (`LastModifiedUserId`) REFERENCES `tblGenericUser` (`Id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=103 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblReportRecipient
-- ----------------------------
DROP TABLE IF EXISTS `tblReportRecipient`;
CREATE TABLE `tblReportRecipient` (
`Id` int(10) NOT NULL auto_increment, `ReportId` int(10) NOT NULL, `GenericUserId` int(10) default NULL, `Type` int(11) NOT NULL default '0', `Email` varchar(50) default NULL, PRIMARY KEY (`Id`), KEY `GenericUserId` (`GenericUserId`), KEY `ReportId` (`ReportId`), CONSTRAINT `tblReportRecipient_fk` FOREIGN KEY (`GenericUserId`) REFERENCES `tblGenericUser` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblReportRecipient_fk1` FOREIGN KEY (`ReportId`) REFERENCES `tblReport` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=123 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblRSSNewsFeeds
-- ----------------------------
DROP TABLE IF EXISTS `tblRSSNewsFeeds`;
CREATE TABLE `tblRSSNewsFeeds` (
`Id` int(11) NOT NULL auto_increment, `URL` varchar(255) NOT NULL, `Name` varchar(128) default NULL, `RegionId` int(10) default NULL, PRIMARY KEY (`Id`), KEY `RegionId` (`RegionId`), CONSTRAINT `tblRSSNewsFeeds_fk` FOREIGN KEY (`RegionId`) REFERENCES `tblRegion` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblSeaPort
-- ----------------------------
DROP TABLE IF EXISTS `tblSeaPort`;
CREATE TABLE `tblSeaPort` (
`Id` int(10) NOT NULL auto_increment, `City` varchar(50) NOT NULL, `CountryId` int(10) NOT NULL, `IsArchived` bit(1) NOT NULL default '\0', PRIMARY KEY (`Id`), KEY `CountryId` (`CountryId`), CONSTRAINT `tblSeaPort_ibfk_1` FOREIGN KEY (`CountryId`) REFERENCES `tblCountry` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=137 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblSecuredUrl
-- ----------------------------
DROP TABLE IF EXISTS `tblSecuredUrl`;
CREATE TABLE `tblSecuredUrl` (
`Id` int(10) NOT NULL auto_increment, `Url` varchar(100) NOT NULL default , `Description` varchar(30) NOT NULL, `BasicOpsAccess` bit(1) NOT NULL, `SuperOpsAccess` bit(1) NOT NULL, `BasicAcc` bit(1) NOT NULL, `SuperAcc` bit(1) NOT NULL, `Management` bit(1) NOT NULL, `Sales` bit(1) NOT NULL, `Blank1` bit(1) NOT NULL, `Blank2` bit(1) NOT NULL, PRIMARY KEY (`Id`)
) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblShipline
-- ----------------------------
DROP TABLE IF EXISTS `tblShipline`;
CREATE TABLE `tblShipline` (
`Id` int(10) NOT NULL auto_increment, `Name` varchar(50) NOT NULL, `SCAC` varchar(4) NOT NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblShipment2ClientCompanyGroupRelation
-- ----------------------------
DROP TABLE IF EXISTS `tblShipment2ClientCompanyGroupRelation`;
CREATE TABLE `tblShipment2ClientCompanyGroupRelation` (
`Id` int(10) NOT NULL auto_increment, `ClientType` int(10) NOT NULL, `ShipmentId` int(10) NOT NULL, `ClientCompanyGroupId` int(10) NOT NULL, PRIMARY KEY (`Id`), UNIQUE KEY `ClientType` (`ClientType`,`ShipmentId`), KEY `GenericShipmentId_FK` (`ShipmentId`), KEY `ClientCompanyGroupId` (`ClientCompanyGroupId`), CONSTRAINT `tblShipment2ClientCompanyGroupRelation_fk` FOREIGN KEY (`ClientCompanyGroupId`) REFERENCES `tblClientCompanyGroup` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `GenericShipmentId_FK` FOREIGN KEY (`ShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=217 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblShipment2ClientCompanyRelation
-- ----------------------------
DROP TABLE IF EXISTS `tblShipment2ClientCompanyRelation`;
CREATE TABLE `tblShipment2ClientCompanyRelation` (
`Id` int(10) NOT NULL auto_increment, `ClientType` int(10) NOT NULL, `ShipmentId` int(10) NOT NULL, `ClientCompanyId` int(10) NOT NULL, PRIMARY KEY (`Id`), UNIQUE KEY `ClientType` (`ClientType`,`ShipmentId`), KEY `GenericShipmentId1_FK` (`ShipmentId`), KEY `ClientCompanyId` (`ClientCompanyId`), CONSTRAINT `tblShipment2ClientCompanyRelation_fk` FOREIGN KEY (`ClientCompanyId`) REFERENCES `tblClientCompany` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `GenericShipmentId1_FK` FOREIGN KEY (`ShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3261 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblShipmentChargeCodes
-- ----------------------------
DROP TABLE IF EXISTS `tblShipmentChargeCodes`;
CREATE TABLE `tblShipmentChargeCodes` (
`Id` int(10) NOT NULL auto_increment, `ShipmentId` int(10) NOT NULL, `ChargeCodeNumber` int(10) NOT NULL, `Description` varchar(150) default NULL, `CalculationType` int(10) default NULL, `QuantityUnitTypeId` int(10) default NULL, `RateUnitTypeId` int(10) default NULL, `CurrencyId` int(10) default NULL, `Quantity` double default NULL, `Rate` double default NULL, `Amount` double default NULL, `ChargeCodeId` int(10) default NULL, `EnableVAT` tinyint(1) default NULL, PRIMARY KEY (`Id`), KEY `ShipmentId` (`ShipmentId`), KEY `QuantityUnitTypeId` (`QuantityUnitTypeId`), KEY `RateUnitTypeId` (`RateUnitTypeId`), KEY `CurrencyId` (`CurrencyId`), KEY `ChargeCodeId` (`ChargeCodeId`), CONSTRAINT `tblShipmentChargeCodes_fk4` FOREIGN KEY (`ChargeCodeId`) REFERENCES `tblChargeCode` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblShipmentChargeCodes_fk1` FOREIGN KEY (`QuantityUnitTypeId`) REFERENCES `tblUnitType` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblShipmentChargeCodes_fk2` FOREIGN KEY (`RateUnitTypeId`) REFERENCES `tblUnitType` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblShipmentChargeCodes_fk3` FOREIGN KEY (`CurrencyId`) REFERENCES `tblCurrency` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblShipmentChargeCodes_fk` FOREIGN KEY (`ShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1947 DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 6144 kB; (`ShipmentId`) REFER `cybertrax/tblgen';
-- ----------------------------
-- Table structure for tblShipmentComments
-- ----------------------------
DROP TABLE IF EXISTS `tblShipmentComments`;
CREATE TABLE `tblShipmentComments` (
`Id` int(10) NOT NULL auto_increment, `ShipmentId` int(10) NOT NULL, `ReplyDateTime` datetime NOT NULL, `UserId` int(10) NOT NULL, `Body` varchar(1500) NOT NULL default , `Internal` bit(1) NOT NULL default '\0', PRIMARY KEY (`Id`), KEY `ShipmentId` (`ShipmentId`), KEY `UserId` (`UserId`), CONSTRAINT `tblShipmentComments_ibfk_1` FOREIGN KEY (`ShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON DELETE CASCADE, CONSTRAINT `tblShipmentComments_ibfk_2` FOREIGN KEY (`UserId`) REFERENCES `tblGenericUser` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=307 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblShipmentDateHistory
-- ----------------------------
DROP TABLE IF EXISTS `tblShipmentDateHistory`;
CREATE TABLE `tblShipmentDateHistory` (
`Id` int(10) NOT NULL auto_increment, `DateTypeId` int(10) NOT NULL, `ShipmentId` int(10) NOT NULL, `DateValue` datetime NOT NULL, `ChangeDate` datetime NOT NULL, `OperatorId` int(10) NOT NULL, PRIMARY KEY (`Id`), KEY `tblShipmentDateHistory_ibfk_1` (`ShipmentId`), KEY `tblShipmentDateHistory_ibfk_2` (`OperatorId`), CONSTRAINT `tblShipmentDateHistory_ibfk_1` FOREIGN KEY (`ShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON DELETE CASCADE, CONSTRAINT `tblShipmentDateHistory_ibfk_2` FOREIGN KEY (`OperatorId`) REFERENCES `tblGenericUser` (`Id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3478 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblShipmentGroup
-- ----------------------------
DROP TABLE IF EXISTS `tblShipmentGroup`;
CREATE TABLE `tblShipmentGroup` (
`Id` int(10) NOT NULL auto_increment, `TransportModeId` int(10) NOT NULL, `ClientCompanyId` int(10) NOT NULL, `MasterId` int(10) default NULL, PRIMARY KEY (`Id`), KEY `TransportModeId` (`TransportModeId`), KEY `ClientCompanyId` (`ClientCompanyId`), KEY `tblShipmentGroup_fk2` (`MasterId`), CONSTRAINT `tblShipmentGroup_fk` FOREIGN KEY (`TransportModeId`) REFERENCES `tblTransportMode` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblShipmentGroup_fk1` FOREIGN KEY (`ClientCompanyId`) REFERENCES `tblClientCompany` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblShipmentGroup_fk2` FOREIGN KEY (`MasterId`) REFERENCES `tblMasterOcean` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=89 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblShipmentInvoice2ContainersRelation
-- ----------------------------
DROP TABLE IF EXISTS `tblShipmentInvoice2ContainersRelation`;
CREATE TABLE `tblShipmentInvoice2ContainersRelation` (
`Id` int(10) NOT NULL auto_increment, `ShipmentInvoiceId` int(10) NOT NULL, `ContainerId` int(10) NOT NULL, PRIMARY KEY (`Id`), UNIQUE KEY `Id` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=388 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblShipmentInvoiceChargeCodes
-- ----------------------------
DROP TABLE IF EXISTS `tblShipmentInvoiceChargeCodes`;
CREATE TABLE `tblShipmentInvoiceChargeCodes` (
`Id` int(10) NOT NULL auto_increment, `ShipmentInvoiceId` int(10) default NULL, `ChargeCodeNumber` int(10) NOT NULL, `Description` varchar(150) default NULL, `CalculationType` int(10) default NULL, `QuantityUnitTypeId` int(10) default NULL, `RateUnitTypeId` int(10) default NULL, `CurrencyId` int(10) default NULL, `Quantity` double default NULL, `Rate` double default NULL, `Amount` double default NULL, `ChargeCodeId` int(10) default NULL, `EnableVAT` tinyint(1) default NULL, `TableOfChargesId` int(10) default NULL, PRIMARY KEY (`Id`), KEY `tblShipmentInvoiceChargeCodes_InvoiceId` (`ShipmentInvoiceId`), KEY `QuantityUnitTypeId` (`QuantityUnitTypeId`), KEY `CurrencyId` (`CurrencyId`), KEY `ChargeCodeId` (`ChargeCodeId`), KEY `TableOfChargesId` (`TableOfChargesId`), CONSTRAINT `tblShipmentInvoiceChargeCodes_fk3` FOREIGN KEY (`TableOfChargesId`) REFERENCES `tblTableOfCharges` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `tblShipmentInvoiceChargeCodes_fk` FOREIGN KEY (`QuantityUnitTypeId`) REFERENCES `tblUnitType` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblShipmentInvoiceChargeCodes_fk1` FOREIGN KEY (`CurrencyId`) REFERENCES `tblCurrency` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblShipmentInvoiceChargeCodes_fk2` FOREIGN KEY (`ChargeCodeId`) REFERENCES `tblChargeCode` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblShipmentInvoiceChargeCodes_InvoiceId` FOREIGN KEY (`ShipmentInvoiceId`) REFERENCES `tblShipmentInvoices` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2533 DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 7168 kB; (`ShipmentInvoiceId`) REFER `cybertrax';
-- ----------------------------
-- Table structure for tblShipmentInvoiceContainers
-- ----------------------------
DROP TABLE IF EXISTS `tblShipmentInvoiceContainers`;
CREATE TABLE `tblShipmentInvoiceContainers` (
`Id` int(10) NOT NULL auto_increment, `ShipmentInvoiceChargeCodeId` int(10) default NULL, `SizeId` int(10) default NULL, `TypeId` int(10) default NULL, `Count` int(10) default NULL, `Rate` double default NULL, `Amount` double default NULL, `CurrencyId` int(10) default NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblShipmentInvoices
-- ----------------------------
DROP TABLE IF EXISTS `tblShipmentInvoices`;
CREATE TABLE `tblShipmentInvoices` (
`Id` int(10) NOT NULL auto_increment, `ShipmentId` int(10) NOT NULL, `IssueDate` datetime NOT NULL, `UserId` int(10) NOT NULL, `Amount` double NOT NULL default '0', `BillToCompany` varchar(255) default NULL, `BillToAddress` varchar(512) default NULL, `CurrencyId` int(10) NOT NULL, `OfficeId` int(10) NOT NULL, `DateOfDeparture` date default NULL, `DateOfArrival` date default NULL, `PortOfDeparture` varchar(128) default NULL, `PortOfArrival` varchar(128) default NULL, `Vesselname` varchar(128) default NULL, `Shipper` varchar(512) default NULL, `Consignee` varchar(512) default NULL, `NotifyParty` varchar(512) default NULL, `FileRef` varchar(512) default NULL, `InvoiceType` int(10) NOT NULL default '0', `CreditNoteOnInvoiceId` int(10) default NULL, `VoyageNumber` varchar(20) default NULL, `TotalOfPallets` int(10) default NULL, `TotalPkgsOnPlts` int(10) default NULL, `TotalLoosePkgsOnPlts` int(10) default NULL, `BillToOfficeId` int(10) default NULL, `BillToCompanyId` int(10) default NULL, `WeightInKg` double default NULL, `VolumeInCbm` double default NULL, `Comments` varchar(1024) default NULL, `PONumbers` varchar(1024) default NULL, `CTRef` varchar(512) default NULL, `VATAmount` double default NULL, `TotalWithVAT` double default NULL, `TotalNonVAT` double default NULL, `CustomerRef` varchar(512) default NULL, `ChargeableWeight` double default NULL, `TableOfChargesId` int(10) default NULL, `ShowToClient` tinyint(1) NOT NULL default '0', `InvoiceNumber` int(10) NOT NULL default '0', `MainOfficeId` int(10) default NULL, PRIMARY KEY (`Id`), KEY `ShipmentId` (`ShipmentId`), KEY `InvoiceType` (`InvoiceType`), KEY `UserId` (`UserId`), KEY `OfficeId` (`OfficeId`), KEY `CurrencyId` (`CurrencyId`), KEY `BillToOfficeId` (`BillToOfficeId`), KEY `BillToCompanyId` (`BillToCompanyId`), KEY `CreditNoteOnInvoiceId` (`CreditNoteOnInvoiceId`), KEY `TableOfChargesId` (`TableOfChargesId`), KEY `InvoiceNumber` (`InvoiceNumber`), KEY `MainOfficeId` (`MainOfficeId`), CONSTRAINT `tblShipmentInvoices_fk8` FOREIGN KEY (`MainOfficeId`) REFERENCES `tblOffice` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblShipmentInvoices_fk` FOREIGN KEY (`ShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblShipmentInvoices_fk1` FOREIGN KEY (`OfficeId`) REFERENCES `tblOffice` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblShipmentInvoices_fk2` FOREIGN KEY (`CurrencyId`) REFERENCES `tblCurrency` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblShipmentInvoices_fk3` FOREIGN KEY (`BillToOfficeId`) REFERENCES `tblOffice` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblShipmentInvoices_fk4` FOREIGN KEY (`BillToCompanyId`) REFERENCES `tblAddressBillToParty` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblShipmentInvoices_fk5` FOREIGN KEY (`UserId`) REFERENCES `tblJaguarUser` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblShipmentInvoices_fk6` FOREIGN KEY (`CreditNoteOnInvoiceId`) REFERENCES `tblShipmentInvoices` (`Id`) ON UPDATE CASCADE, CONSTRAINT `tblShipmentInvoices_fk7` FOREIGN KEY (`TableOfChargesId`) REFERENCES `tblTableOfCharges` (`Id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=427 DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 7168 kB';
-- ----------------------------
-- Table structure for tblShipmentLog
-- ----------------------------
DROP TABLE IF EXISTS `tblShipmentLog`;
CREATE TABLE `tblShipmentLog` (
`Id` int(10) NOT NULL auto_increment, `ShipmentId` int(10) NOT NULL, `UserId` int(10) NOT NULL, `FieldName` varchar(500) NOT NULL default , `OldValue` varchar(1500) default NULL, `Newvalue` varchar(1500) default NULL, `ChangeTime` datetime NOT NULL, PRIMARY KEY (`Id`), KEY `ShipmentId` (`ShipmentId`), KEY `UserId` (`UserId`), CONSTRAINT `tblShipmentLog_ibfk_1` FOREIGN KEY (`ShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON DELETE CASCADE, CONSTRAINT `tblShipmentLog_ibfk_2` FOREIGN KEY (`UserId`) REFERENCES `tblGenericUser` (`Id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11617 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblShipmentTemplate
-- ----------------------------
DROP TABLE IF EXISTS `tblShipmentTemplate`;
CREATE TABLE `tblShipmentTemplate` (
`Id` int(10) NOT NULL auto_increment, `TemplateTypeCode` int(10) NOT NULL, `OfficeId` int(10) NOT NULL, `ShipmentId` int(10) NOT NULL, `TransportModeId` int(10) NOT NULL, `ClientCompanyId` int(10) NOT NULL, `ShipperAddressId` int(10) default NULL, `ShipperAddressDescription` varchar(1500) default NULL, `ConsigneeAddressId` int(10) default NULL, `ConsigneeAddressDescription` varchar(1500) default NULL, `NotifyAddressId` int(10) default NULL, `NotifyAddressDescription` varchar(1500) default NULL, `PickupAddressId` int(10) default NULL, `PickupAddressDescription` varchar(1500) default NULL, `PickupTruckerId` int(10) default NULL, `OriginPointCountryId` int(10) default NULL, `OriginPoint` varchar(30) default NULL, `DestinationPointCountryId` int(10) default NULL, `DestinationPoint` varchar(30) default NULL, `CommodityDescription` varchar(1500) default NULL, `Comment` varchar(1500) default NULL, `CustomsBrokerId` int(10) default NULL, `OriginalTerminalId` int(10) default NULL, `PortOfLoadingId` int(10) default NULL, `PortOfTransShipmentId` int(10) default NULL, `PortOfDischargeId` int(10) default NULL, `DestinationalTerminalId` int(10) default NULL, `DestinationReceivingAgentId` int(10) default NULL, `AirportOfDepartureId` int(10) default NULL, `TransShipmentAirportId` int(10) default NULL, `AirportOfDestinationId` int(10) default NULL, `AdditionalClient1Id` int(10) default NULL, `AdditionalClient1IsCompany` tinyint(1) default NULL, `AdditionalClient2Id` int(10) default NULL, `AdditionalClient2IsCompany` tinyint(1) default NULL, `AdditionalClient3Id` int(10) default NULL, `AdditionalClient3IsCompany` tinyint(1) default NULL, PRIMARY KEY (`Id`), KEY `tblOceanShipmentTemplate_ibfk_1` (`TransportModeId`), KEY `tblOceanShipmentTemplate_ibfk_2` (`ClientCompanyId`), KEY `tblOceanShipmentTemplate_ibfk_3` (`ShipperAddressId`), KEY `tblOceanShipmentTemplate_ibfk_4` (`ConsigneeAddressId`), KEY `tblOceanShipmentTemplate_ibfk_5` (`NotifyAddressId`), KEY `tblOceanShipmentTemplate_ibfk_6` (`PickupAddressId`), KEY `tblOceanShipmentTemplate_ibfk_7` (`OriginalTerminalId`), KEY `tblOceanShipmentTemplate_ibfk_8` (`PortOfLoadingId`), KEY `tblOceanShipmentTemplate_ibfk_9` (`PortOfTransShipmentId`), KEY `tblOceanShipmentTemplate_ibfk_10` (`PortOfDischargeId`), KEY `tblOceanShipmentTemplate_ibfk_11` (`DestinationalTerminalId`), KEY `tblOceanShipmentTemplate_ibfk_12` (`DestinationReceivingAgentId`), KEY `tblOceanShipmentTemplate_ibfk_13` (`PickupTruckerId`), KEY `tblOceanShipmentTemplate_ibfk_14` (`CustomsBrokerId`), CONSTRAINT `tblOceanShipmentTemplate_ibfk_1` FOREIGN KEY (`TransportModeId`) REFERENCES `tblTransportMode` (`Id`), CONSTRAINT `tblOceanShipmentTemplate_ibfk_10` FOREIGN KEY (`PortOfDischargeId`) REFERENCES `tblSeaPort` (`Id`), CONSTRAINT `tblOceanShipmentTemplate_ibfk_11` FOREIGN KEY (`DestinationalTerminalId`) REFERENCES `tblSeaPort` (`Id`), CONSTRAINT `tblOceanShipmentTemplate_ibfk_12` FOREIGN KEY (`DestinationReceivingAgentId`) REFERENCES `tblAddressTransportation` (`Id`), CONSTRAINT `tblOceanShipmentTemplate_ibfk_13` FOREIGN KEY (`PickupTruckerId`) REFERENCES `tblAddressJaguarVendor` (`Id`), CONSTRAINT `tblOceanShipmentTemplate_ibfk_14` FOREIGN KEY (`CustomsBrokerId`) REFERENCES `tblAddressJaguarVendor` (`Id`), CONSTRAINT `tblOceanShipmentTemplate_ibfk_2` FOREIGN KEY (`ClientCompanyId`) REFERENCES `tblClientCompany` (`Id`), CONSTRAINT `tblOceanShipmentTemplate_ibfk_3` FOREIGN KEY (`ShipperAddressId`) REFERENCES `tblAddressTransportation` (`Id`), CONSTRAINT `tblOceanShipmentTemplate_ibfk_4` FOREIGN KEY (`ConsigneeAddressId`) REFERENCES `tblAddressTransportation` (`Id`), CONSTRAINT `tblOceanShipmentTemplate_ibfk_5` FOREIGN KEY (`NotifyAddressId`) REFERENCES `tblAddressTransportation` (`Id`), CONSTRAINT `tblOceanShipmentTemplate_ibfk_6` FOREIGN KEY (`PickupAddressId`) REFERENCES `tblAddressTransportation` (`Id`), CONSTRAINT `tblOceanShipmentTemplate_ibfk_7` FOREIGN KEY (`OriginalTerminalId`) REFERENCES `tblSeaPort` (`Id`), CONSTRAINT `tblOceanShipmentTemplate_ibfk_8` FOREIGN KEY (`PortOfLoadingId`) REFERENCES `tblSeaPort` (`Id`), CONSTRAINT `tblOceanShipmentTemplate_ibfk_9` FOREIGN KEY (`PortOfTransShipmentId`) REFERENCES `tblSeaPort` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=87 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblShippingTerm
-- ----------------------------
DROP TABLE IF EXISTS `tblShippingTerm`;
CREATE TABLE `tblShippingTerm` (
`Id` int(10) NOT NULL auto_increment, `TransportModeId` int(11) NOT NULL, `Term` varchar(20) default NULL, `IsArchived` bit(1) NOT NULL default '\0', PRIMARY KEY (`Id`), UNIQUE KEY `Term` (`Term`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblStandardComments
-- ----------------------------
DROP TABLE IF EXISTS `tblStandardComments`;
CREATE TABLE `tblStandardComments` (
`Id` int(10) NOT NULL auto_increment, `Header` varchar(40) NOT NULL, `Body` varchar(200) NOT NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblSteamshipline
-- ----------------------------
DROP TABLE IF EXISTS `tblSteamshipline`;
CREATE TABLE `tblSteamshipline` (
`Id` int(10) NOT NULL auto_increment, `Name` varchar(30) NOT NULL, `SCACCode` varchar(4) NOT NULL, `TrackingCarrierCode` varchar(30) default NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblTableOfCharges
-- ----------------------------
DROP TABLE IF EXISTS `tblTableOfCharges`;
CREATE TABLE `tblTableOfCharges` (
`Id` int(10) NOT NULL auto_increment, `Amount` double default NULL, `AmountVAT` double default NULL, `TotalWithVAT` double default NULL, `TotalNonVAT` double default NULL, `CurrencyId` int(10) default NULL, PRIMARY KEY (`Id`), KEY `CurrencyId` (`CurrencyId`), CONSTRAINT `tblTableOfCharges_fk` FOREIGN KEY (`CurrencyId`) REFERENCES `tblCurrency` (`Id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1363 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblTestimonials
-- ----------------------------
DROP TABLE IF EXISTS `tblTestimonials`;
CREATE TABLE `tblTestimonials` (
`Id` int(11) NOT NULL auto_increment, `Text` varchar(2048) default NULL, `Name` varchar(128) default NULL, `Company` varchar(128) default NULL, PRIMARY KEY (`Id`), UNIQUE KEY `Id` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblTransportMode
-- ----------------------------
DROP TABLE IF EXISTS `tblTransportMode`;
CREATE TABLE `tblTransportMode` (
`Id` int(10) NOT NULL auto_increment, `Mode` varchar(50) NOT NULL, PRIMARY KEY (`Id`), UNIQUE KEY `Mode` (`Mode`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblTruckingShipment
-- ----------------------------
DROP TABLE IF EXISTS `tblTruckingShipment`;
CREATE TABLE `tblTruckingShipment` (
`TruckingShipmentId` int(10) NOT NULL auto_increment, `GenericShipmentId` int(10) NOT NULL, `EstimatedDeliveryDate` date default NULL, `ActualDeliveryDate` date default NULL, `LastFreeDay` date default NULL, `HouseAirwayBill` varchar(30) default NULL, `AirlineId` int(10) default NULL, `MasterBillOfLoading` varchar(30) default NULL, `HouseBillOfLoading` varchar(30) default NULL, `SteamshiplineId` int(10) default NULL, `MasterBillA` varchar(3) NOT NULL default , `MasterBillB` varchar(4) NOT NULL default , `MasterBillC` varchar(4) NOT NULL default , PRIMARY KEY (`TruckingShipmentId`), KEY `TruckingShipment_GenericShipmentId` (`GenericShipmentId`), KEY `TruckingShipment_AirlineId` (`AirlineId`), KEY `TruckingShipment_SteamshiplineId` (`SteamshiplineId`), CONSTRAINT `tblTruckingShipment_ibfk_1` FOREIGN KEY (`GenericShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON DELETE CASCADE, CONSTRAINT `tblTruckingShipment_ibfk_2` FOREIGN KEY (`AirlineId`) REFERENCES `tblAirline` (`Id`), CONSTRAINT `tblTruckingShipment_ibfk_3` FOREIGN KEY (`SteamshiplineId`) REFERENCES `tblSteamshipline` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=198 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblUndefinedShipment
-- ----------------------------
DROP TABLE IF EXISTS `tblUndefinedShipment`;
CREATE TABLE `tblUndefinedShipment` (
`UndefinedShipmentId` int(10) NOT NULL auto_increment, `GenericShipmentId` int(10) NOT NULL, PRIMARY KEY (`UndefinedShipmentId`), KEY `UndefinedShipment_GenericShipmentId` (`GenericShipmentId`), CONSTRAINT `tblUndefinedShipment_ibfk_11` FOREIGN KEY (`GenericShipmentId`) REFERENCES `tblGenericShipment` (`Id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=997 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblUnitType
-- ----------------------------
DROP TABLE IF EXISTS `tblUnitType`;
CREATE TABLE `tblUnitType` (
`Id` int(10) NOT NULL auto_increment, `Description` varchar(25) NOT NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblUpdateLogRecord
-- ----------------------------
DROP TABLE IF EXISTS `tblUpdateLogRecord`;
CREATE TABLE `tblUpdateLogRecord` (
`Id` int(10) NOT NULL auto_increment, `UserID` int(10) NOT NULL, `UpdateTime` datetime NOT NULL, `GroupObjectID` int(10) NOT NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblUploadedFiles
-- ----------------------------
DROP TABLE IF EXISTS `tblUploadedFiles`;
CREATE TABLE `tblUploadedFiles` (
`Id` int(10) NOT NULL auto_increment, `ShipmentId` int(10) NOT NULL, `FileName` varchar(150) NOT NULL default , `Summary` varchar(150) default NULL, `ShowToClient` tinyint(1) NOT NULL default '0', `UploadDate` datetime default NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblUSAstate
-- ----------------------------
DROP TABLE IF EXISTS `tblUSAstate`;
CREATE TABLE `tblUSAstate` (
`Id` int(10) NOT NULL auto_increment, `Name` varchar(40) NOT NULL, `Abbr` varchar(2) NOT NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblUserGroup
-- ----------------------------
DROP TABLE IF EXISTS `tblUserGroup`;
CREATE TABLE `tblUserGroup` (
`Id` int(10) NOT NULL auto_increment, `Name` varchar(30) NOT NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblUserGroupObject
-- ----------------------------
DROP TABLE IF EXISTS `tblUserGroupObject`;
CREATE TABLE `tblUserGroupObject` (
`Id` int(10) NOT NULL auto_increment, `UserGroupID` int(10) NOT NULL, `Name` varchar(30) NOT NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for tblUserRole
-- ----------------------------
DROP TABLE IF EXISTS `tblUserRole`;
CREATE TABLE `tblUserRole` (
`Id` int(10) NOT NULL auto_increment, `Name` varchar(30) NOT NULL, `Description` varchar(150) NOT NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Trigger structure for tblCurrency_after_ins_tr
-- ----------------------------
DELIMITER ;;
CREATE TRIGGER `tblCurrency_after_ins_tr` AFTER INSERT ON `tblCurrency` FOR EACH ROW BEGIN
INSERT INTO `tblCurrencyExchangeRate` (FromCurrencyId, ToCurrencyId, OfficeId) SELECT NEW.Id, `tblCurrency`.Id, `tblOffice`.Id FROM tblCurrency, tblOffice WHERE NOT `tblCurrency`.Id=NEW.Id; INSERT INTO `tblCurrencyExchangeRate` (FromCurrencyId, ToCurrencyId, OfficeId) SELECT `tblCurrency`.Id, NEW.Id, `tblOffice`.Id FROM tblCurrency, tblOffice WHERE NOT `tblCurrency`.Id=NEW.Id;
END;;
DELIMITER ;
-- ----------------------------
-- Trigger structure for tblOffice_after_ins_tr
-- ----------------------------
DELIMITER ;;
CREATE TRIGGER `tblOffice_after_ins_tr` AFTER INSERT ON `tblOffice` FOR EACH ROW BEGIN INSERT INTO `tblCurrencyExchangeRate` (FromCurrencyId, ToCurrencyId, OfficeId)
SELECT c1.Id, c2.Id, NEW.Id FROM tblCurrency c1, tblCurrency c2 WHERE NOT c1.Id=c2.Id;
END;;
DELIMITER ;
-- ----------------------------
-- Trigger structure for tblShipmentInvoices_after_del_tr
-- ----------------------------
DELIMITER ;;
CREATE TRIGGER `tblShipmentInvoices_after_del_tr` AFTER DELETE ON `tblShipmentInvoices` FOR EACH ROW BEGIN
delete from `tblTableOfCharges` where `tblTableOfCharges`.Id = OLD.`TableOfChargesId`;
END;;
DELIMITER ;
</pre>