CREATE TABLE ProcessingCodes (
  ProcessingCodesId DECIMAL  NOT NULL   IDENTITY ,
  ProcessingCode VARCHAR(5)  NOT NULL  ,
  ProcessingName VARCHAR(255)  NOT NULL    ,
PRIMARY KEY(ProcessingCodesId));
GO




CREATE TABLE Groups (
  groupId DECIMAL  NOT NULL   IDENTITY ,
  groupName VARCHAR(255)    ,
  details VARCHAR(255)    ,
  isActive DECIMAL      ,
PRIMARY KEY(groupId));
GO




CREATE TABLE RouteCodes (
  RouteCodesId DECIMAL  NOT NULL   IDENTITY ,
  Code CHAR  NOT NULL  ,
  Details VARCHAR(255)      ,
PRIMARY KEY(RouteCodesId));
GO




CREATE TABLE ProgramModules (
  ProgramModulesId DECIMAL  NOT NULL   IDENTITY ,
  ModuleCode DECIMAL    ,
  ModuleName VARCHAR(100)    ,
  ModuleDetails VARCHAR(255)      ,
PRIMARY KEY(ProgramModulesId));
GO




CREATE TABLE Regions (
  RegionsId DECIMAL  NOT NULL   IDENTITY ,
  regionCode DECIMAL    ,
  regionName VARCHAR(255)  NOT NULL    ,
PRIMARY KEY(RegionsId));
GO




CREATE TABLE ContractTypes (
  ContractTypesId DECIMAL  NOT NULL   IDENTITY ,
  ContractTypeName VARCHAR(255)  NOT NULL  ,
  ContractTypeComment VARCHAR(255)      ,
PRIMARY KEY(ContractTypesId));
GO




CREATE TABLE Modality (
  ModalityId DECIMAL  NOT NULL   IDENTITY ,
  Modality VARCHAR(100)    ,
  ShortModality VARCHAR(20)  NOT NULL    ,
PRIMARY KEY(ModalityId));
GO




CREATE TABLE Classes (
  ClassesId DECIMAL  NOT NULL   IDENTITY ,
  ClassNumber VARCHAR(20)  NOT NULL  ,
  ItemNumber DECIMAL  NOT NULL  ,
  ItemName VARCHAR(255)    ,
  EwcNumber DECIMAL      ,
PRIMARY KEY(ClassesId));
GO




CREATE TABLE CompanyDepots (
  CompanyDepotsId DECIMAL  NOT NULL   IDENTITY ,
  Name VARCHAR(255)  NOT NULL  ,
  Place VARCHAR(255)    ,
  Address VARCHAR(255)      ,
PRIMARY KEY(CompanyDepotsId));
GO




CREATE TABLE Contract (
  Contractid DECIMAL  NOT NULL   IDENTITY ,
  BankAccount VARCHAR(100)    ,
  KSHNumber VARCHAR(100)    ,
  KUJNumber VARCHAR(100)    ,
  TradeRegister VARCHAR(100)    ,
  TaxNumber VARCHAR(100)    ,
  TaxIdentifier VARCHAR(100)    ,
  MotherName VARCHAR(100)    ,
  IdentityCardNumber VARCHAR(100)    ,
  TownShipIdentifier VARCHAR(100)    ,
  MVHNumber VARCHAR(100)    ,
  Address VARCHAR(255)    ,
  ValidStart DATE    ,
  ValidEnd DATE    ,
  StatusFlag CHAR      ,
PRIMARY KEY(Contractid)  );
GO


CREATE INDEX Contract_unique ON Contract (ValidStart, ValidEnd, StatusFlag);
GO




CREATE TABLE Containers (
  ContainersId DECIMAL  NOT NULL   IDENTITY ,
  ContainerCode DECIMAL    ,
  ContainerName VARCHAR(100)  NOT NULL  ,
  ContainerPrice DECIMAL      ,
PRIMARY KEY(ContainersId));
GO




CREATE TABLE Users (
  usersId DECIMAL  NOT NULL   IDENTITY ,
  groupId DECIMAL  NOT NULL  ,
  userName VARCHAR(255)    ,
  userPassword VARCHAR(255)    ,
  userFullName VARCHAR(255)    ,
  isActive DECIMAL      ,
PRIMARY KEY(usersId)  ,
  FOREIGN KEY(groupId)
    REFERENCES Groups(groupId));
GO


CREATE INDEX Users_FKIndex1 ON Users (groupId);
GO






CREATE TABLE GrantedModules (
  grantedModulesId DECIMAL  NOT NULL   IDENTITY ,
  ProgramModulesId DECIMAL  NOT NULL  ,
  groupId DECIMAL  NOT NULL    ,
PRIMARY KEY(grantedModulesId)    ,
  FOREIGN KEY(groupId)
    REFERENCES Groups(groupId),
  FOREIGN KEY(ProgramModulesId)
    REFERENCES ProgramModules(ProgramModulesId));
GO


CREATE INDEX GrantedModules_FKIndex1 ON GrantedModules (groupId);
GO
CREATE INDEX GrantedModules_FKIndex2 ON GrantedModules (ProgramModulesId);
GO




CREATE TABLE PartnerData (
  PartnerId DECIMAL  NOT NULL   IDENTITY ,
  Contractid DECIMAL    ,
  ContractTypesId DECIMAL    ,
  RegionsId DECIMAL    ,
  Name VARCHAR(255)    ,
  Place VARCHAR(100)    ,
  ZipCode VARCHAR(50)    ,
  Address VARCHAR(255)    ,
  PartnerNumber VARCHAR(50)    ,
  OrderNumber VARCHAR(50)    ,
  Administrator VARCHAR(100)    ,
  LetterAddress VARCHAR(255)    ,
  Owner VARCHAR(100)    ,
  ContactPerson VARCHAR(255)    ,
  ValidStart DATE    ,
  ValidEnd DATE    ,
  StatusFlag CHAR    ,
  PartnerDataComment VARCHAR(255)      ,
PRIMARY KEY(PartnerId)                ,
  FOREIGN KEY(RegionsId)
    REFERENCES Regions(RegionsId),
  FOREIGN KEY(ContractTypesId)
    REFERENCES ContractTypes(ContractTypesId),
  FOREIGN KEY(Contractid)
    REFERENCES Contract(Contractid));
GO


CREATE INDEX PartnerData_unique ON PartnerData (PartnerNumber, ValidStart, ValidEnd, StatusFlag);
GO
CREATE INDEX PartnerData_FKIndex1 ON PartnerData (RegionsId);
GO
CREATE INDEX PartnerData_FKIndex2 ON PartnerData (ContractTypesId);
GO
CREATE INDEX PartnerData_FKIndex3 ON PartnerData (Contractid);
GO





CREATE TABLE DepotData (
  DepotId DECIMAL  NOT NULL   IDENTITY ,
  PartnerId DECIMAL  NOT NULL  ,
  RegionsId DECIMAL    ,
  DepotCode VARCHAR(50)    ,
  DepotName VARCHAR(255)    ,
  DepotPlace VARCHAR(100)    ,
  DepotAddress VARCHAR(255)    ,
  SiteDirectorName VARCHAR(100)    ,
  SiteDircetorPhone VARCHAR(45)    ,
  Weighing VARCHAR(45)    ,
  WeighingPlace VARCHAR(255)    ,
  isTransportByNotification CHAR    ,
  Comments VARCHAR(255)    ,
  ValidStart DATE    ,
  ValidEnd DATE    ,
  StatusFlag CHAR      ,
PRIMARY KEY(DepotId)            ,
  FOREIGN KEY(PartnerId)
    REFERENCES PartnerData(PartnerId)
      ON UPDATE CASCADE,
  FOREIGN KEY(RegionsId)
    REFERENCES Regions(RegionsId));
GO


CREATE INDEX DepotData_FKIndex1 ON DepotData (PartnerId);
GO
CREATE INDEX DepotData_unique ON DepotData (DepotCode, StatusFlag, ValidEnd, ValidStart);
GO
CREATE INDEX DepotData_FKIndex2 ON DepotData (RegionsId);
GO





CREATE TABLE DepotContainers (
  DepotContainersId DECIMAL  NOT NULL   IDENTITY ,
  DepotId DECIMAL  NOT NULL  ,
  ContainersId DECIMAL  NOT NULL  ,
  Number DECIMAL      ,
PRIMARY KEY(DepotContainersId)    ,
  FOREIGN KEY(DepotId)
    REFERENCES DepotData(DepotId),
  FOREIGN KEY(ContainersId)
    REFERENCES Containers(ContainersId));
GO


CREATE INDEX DepotContainers_FKIndex1 ON DepotContainers (DepotId);
GO
CREATE INDEX DepotContainers_FKIndex2 ON DepotContainers (ContainersId);
GO





CREATE TABLE DistanceBetweenDepots (
  DistanceBetweenDepotsId DECIMAL  NOT NULL   IDENTITY ,
  CompanyDepotsId DECIMAL  NOT NULL  ,
  DepotId DECIMAL  NOT NULL  ,
  Distance DECIMAL    ,
  Priority DECIMAL      ,
PRIMARY KEY(DistanceBetweenDepotsId)    ,
  FOREIGN KEY(DepotId)
    REFERENCES DepotData(DepotId),
  FOREIGN KEY(CompanyDepotsId)
    REFERENCES CompanyDepots(CompanyDepotsId));
GO


CREATE INDEX DistanceBetweenDepots_FKIndex1 ON DistanceBetweenDepots (DepotId);
GO
CREATE INDEX DistanceBetweenDepots_FKIndex2 ON DistanceBetweenDepots (CompanyDepotsId);
GO





CREATE TABLE ClassesForDepots (
  ClassesForDepotsId DECIMAL  NOT NULL   IDENTITY ,
  DepotId DECIMAL  NOT NULL  ,
  ProcessingCodesId DECIMAL  NOT NULL  ,
  ClassesId DECIMAL  NOT NULL    ,
PRIMARY KEY(ClassesForDepotsId)      ,
  FOREIGN KEY(DepotId)
    REFERENCES DepotData(DepotId),
  FOREIGN KEY(ClassesId)
    REFERENCES Classes(ClassesId),
  FOREIGN KEY(ProcessingCodesId)
    REFERENCES ProcessingCodes(ProcessingCodesId));
GO


CREATE INDEX ClassesForDepots_FKIndex1 ON ClassesForDepots (DepotId);
GO
CREATE INDEX ClassesForDepots_FKIndex2 ON ClassesForDepots (ClassesId);
GO
CREATE INDEX ClassesForDepots_FKIndex3 ON ClassesForDepots (ProcessingCodesId);
GO





CREATE TABLE DefaultTransportDays (
  DtdId DECIMAL  NOT NULL   IDENTITY ,
  DepotId DECIMAL  NOT NULL  ,
  isWinter CHAR    ,
  monday CHAR    ,
  tuesday CHAR    ,
  wednesday CHAR    ,
  thursday CHAR    ,
  friday CHAR    ,
  saturday CHAR    ,
  sunday CHAR    ,
  weeklyTransport DECIMAL      ,
PRIMARY KEY(DtdId)  ,
  FOREIGN KEY(DepotId)
    REFERENCES DepotData(DepotId));
GO





CREATE TABLE Routes (
  RoutesId DECIMAL  NOT NULL   IDENTITY ,
  RouteCodesId DECIMAL  NOT NULL  ,
  DepotId DECIMAL  NOT NULL  ,
  Priority DECIMAL      ,
PRIMARY KEY(RoutesId)    ,
  FOREIGN KEY(DepotId)
    REFERENCES DepotData(DepotId),
  FOREIGN KEY(RouteCodesId)
    REFERENCES RouteCodes(RouteCodesId));
GO


CREATE INDEX Routes_FKIndex1 ON Routes (DepotId);
GO
CREATE INDEX Routes_FKIndex2 ON Routes (RouteCodesId);
GO





CREATE TABLE DepotsAndModalities (
  DepotsAndModalitiesId DECIMAL  NOT NULL   IDENTITY ,
  DepotId DECIMAL  NOT NULL  ,
  ModalityId DECIMAL  NOT NULL  ,
  Priority DECIMAL      ,
PRIMARY KEY(DepotsAndModalitiesId)    ,
  FOREIGN KEY(ModalityId)
    REFERENCES Modality(ModalityId),
  FOREIGN KEY(DepotId)
    REFERENCES DepotData(DepotId));
GO


CREATE INDEX DepotsAndModalities_FKIndex1 ON DepotsAndModalities (ModalityId);
GO
CREATE INDEX DepotsAndModalities_FKIndex2 ON DepotsAndModalities (DepotId);
GO

/****** Object:  View [dbo].[PartnerDataView]    Script Date: 05/02/2010 22:26:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[PartnerDataView]
AS
SELECT     dbo.PartnerData.PartnerId, dbo.PartnerData.Name, dbo.PartnerData.Place, dbo.PartnerData.ZipCode, dbo.PartnerData.Address, dbo.PartnerData.PartnerNumber, 
                      dbo.PartnerData.OrderNumber, dbo.PartnerData.Administrator, dbo.PartnerData.LetterAddress, dbo.PartnerData.Owner, dbo.PartnerData.ContactPerson, 
                      dbo.PartnerData.ValidStart, dbo.PartnerData.ValidEnd, dbo.PartnerData.StatusFlag, dbo.PartnerData.RegionsId, dbo.PartnerData.ContractTypesId, 
                      dbo.PartnerData.PartnerDataComment, dbo.ContractTypes.ContractTypeName, dbo.ContractTypes.ContractTypeComment, dbo.Regions.regionCode, 
                      dbo.Regions.regionName, dbo.PartnerData.ContractId
FROM         dbo.PartnerData LEFT OUTER JOIN
                      dbo.ContractTypes ON dbo.ContractTypes.ContractTypesId = dbo.PartnerData.ContractTypesId LEFT OUTER JOIN
                      dbo.Regions ON dbo.PartnerData.RegionsId = dbo.Regions.RegionsId
WHERE     (dbo.PartnerData.StatusFlag = 'A')
GO

/****** Object:  View [dbo].[UsersView]    Script Date: 05/02/2010 22:26:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[UsersView]
AS
SELECT     dbo.Users.usersId, dbo.Users.groupId, dbo.Users.userName, dbo.Users.userPassword, dbo.Users.userFullName, dbo.Users.isActive, dbo.Groups.groupName, 
                      dbo.Groups.isActive AS isActiveGroup, dbo.Groups.details
FROM         dbo.Users INNER JOIN
                      dbo.Groups ON dbo.Users.groupId = dbo.Groups.groupId
GO

/****** Object:  View [dbo].[GroupsAndGrantedModules]    Script Date: 05/02/2010 22:26:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[GroupsAndGrantedModules]
AS
SELECT     dbo.GrantedModules.groupId, dbo.GrantedModules.ProgramModulesId, dbo.ProgramModules.ModuleCode, dbo.ProgramModules.ModuleName, 
                      dbo.ProgramModules.ModuleDetails, dbo.GrantedModules.grantedModulesId
FROM         dbo.GrantedModules INNER JOIN
                      dbo.ProgramModules ON dbo.GrantedModules.ProgramModulesId = dbo.ProgramModules.ProgramModulesId
GO

/****** Object:  View [dbo].[DepotDataView]    Script Date: 05/02/2010 22:26:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[DepotDataView]
AS
SELECT     dbo.DepotData.DepotId, dbo.DepotData.PartnerId, dbo.DepotData.DepotAddress, dbo.DepotData.DepotCode, dbo.DepotData.SiteDirectorName, 
                      dbo.DepotData.SiteDircetorPhone, dbo.DepotData.DepotPlace, dbo.DepotData.Weighing, dbo.DepotData.WeighingPlace, dbo.DepotData.Comments, 
                      dbo.DepotData.ValidStart, dbo.DepotData.ValidEnd, dbo.DepotData.StatusFlag, dbo.DepotData.RegionsId, dbo.DepotData.DepotName, 
                      dbo.DepotData.isTransportByNotification, dbo.Regions.regionCode, dbo.Regions.regionName
FROM         dbo.DepotData LEFT OUTER JOIN
                      dbo.Regions ON dbo.Regions.RegionsId = dbo.DepotData.RegionsId
WHERE     (dbo.DepotData.StatusFlag = 'A')
GO

/****** Object:  View [dbo].[RoutesView]    Script Date: 05/02/2010 22:26:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[RoutesView]
AS
SELECT     dbo.Routes.*, dbo.RouteCodes.Code, dbo.RouteCodes.Details
FROM         dbo.Routes INNER JOIN
                      dbo.RouteCodes ON dbo.Routes.RouteCodesId = dbo.RouteCodes.RouteCodesId
GO

/****** Object:  View [dbo].[DepotClassesView]    Script Date: 05/02/2010 22:26:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[DepotClassesView]
AS
SELECT     dbo.ClassesForDepots.ClassesForDepotsId, dbo.ClassesForDepots.ClassesId, dbo.ClassesForDepots.DepotId, dbo.ClassesForDepots.ProcessingCodesId, 
                      dbo.Classes.ClassNumber, dbo.Classes.ItemNumber, dbo.Classes.ItemName, dbo.Classes.EwcNumber, dbo.ProcessingCodes.ProcessingCode, 
                      dbo.ProcessingCodes.ProcessingName
FROM         dbo.ClassesForDepots INNER JOIN
                      dbo.Classes ON dbo.ClassesForDepots.ClassesId = dbo.Classes.ClassesId LEFT OUTER JOIN
                      dbo.ProcessingCodes ON dbo.ClassesForDepots.ProcessingCodesId = dbo.ProcessingCodes.ProcessingCodesId
GO

/****** Object:  View [dbo].[ReportRoutesSimpleView]    Script Date: 05/02/2010 22:26:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[ReportRoutesSimpleView]
AS
SELECT     dbo.PartnerData.PartnerNumber, dbo.DepotData.DepotCode, dbo.PartnerData.Name, dbo.PartnerData.Place, dbo.DepotData.DepotAddress, dbo.Regions.regionCode, 
                      dbo.Modality.ShortModality, dbo.DistanceBetweenDepots.Distance, DefaultTransportDays_1.monday AS Wmonday, DefaultTransportDays_1.tuesday AS Wtuesday, 
                      DefaultTransportDays_1.wednesday AS Wwednesday, DefaultTransportDays_1.thursday AS Wthursday, DefaultTransportDays_1.friday AS Wfriday, 
                      DefaultTransportDays_1.saturday AS Wsaturday, DefaultTransportDays_1.sunday AS Wsunday, dbo.DefaultTransportDays.monday AS Smonday, 
                      dbo.DefaultTransportDays.tuesday AS Stuesday, dbo.DefaultTransportDays.wednesday AS Swednesday, dbo.DefaultTransportDays.thursday AS Sthursday, 
                      dbo.DefaultTransportDays.friday AS Sfriday, dbo.DefaultTransportDays.saturday AS Ssaturday, dbo.DefaultTransportDays.sunday AS Ssunday, 
                      dbo.DepotData.isTransportByNotification, dbo.DepotData.Weighing, dbo.DepotData.DepotId, dbo.DepotData.PartnerId, dbo.Regions.RegionsId
FROM         dbo.DistanceBetweenDepots RIGHT OUTER JOIN
                      dbo.DepotData INNER JOIN
                      dbo.DefaultTransportDays ON dbo.DepotData.DepotId = dbo.DefaultTransportDays.DepotId LEFT OUTER JOIN
                      dbo.Regions ON dbo.DepotData.RegionsId = dbo.Regions.RegionsId LEFT OUTER JOIN
                      dbo.PartnerData ON dbo.DepotData.PartnerId = dbo.PartnerData.PartnerId ON dbo.DistanceBetweenDepots.DepotId = dbo.DepotData.DepotId LEFT OUTER JOIN
                      dbo.Modality INNER JOIN
                      dbo.DepotsAndModalities ON dbo.Modality.ModalityId = dbo.DepotsAndModalities.ModalityId ON 
                      dbo.DepotData.DepotId = dbo.DepotsAndModalities.DepotId LEFT OUTER JOIN
                      dbo.DefaultTransportDays AS DefaultTransportDays_1 ON dbo.DepotData.DepotId = DefaultTransportDays_1.DepotId
WHERE     (dbo.DepotsAndModalities.Priority = 1) AND (dbo.DistanceBetweenDepots.Priority = 1) AND (DefaultTransportDays_1.isWinter = 'I') AND 
                      (dbo.DefaultTransportDays.isWinter = 'N')
GO

/****** Object:  View [dbo].[ReportRoutes]    Script Date: 05/02/2010 22:26:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[ReportRoutes]
AS
SELECT     dbo.PartnerData.PartnerNumber, dbo.DepotData.DepotCode, dbo.PartnerData.Name, dbo.PartnerData.Place, dbo.DepotData.DepotAddress, dbo.Regions.regionCode, 
                      dbo.Modality.ShortModality, dbo.DistanceBetweenDepots.Distance, DefaultTransportDays_1.monday AS Wmonday, DefaultTransportDays_1.tuesday AS Wtuesday, 
                      DefaultTransportDays_1.wednesday AS Wwednesday, DefaultTransportDays_1.thursday AS Wthursday, DefaultTransportDays_1.friday AS Wfriday, 
                      DefaultTransportDays_1.saturday AS Wsaturday, DefaultTransportDays_1.sunday AS Wsunday, dbo.DefaultTransportDays.monday AS Smonday, 
                      dbo.DefaultTransportDays.tuesday AS Stuesday, dbo.DefaultTransportDays.wednesday AS Swednesday, dbo.DefaultTransportDays.thursday AS Sthursday, 
                      dbo.DefaultTransportDays.friday AS Sfriday, dbo.DefaultTransportDays.saturday AS Ssaturday, dbo.DefaultTransportDays.sunday AS Ssunday, 
                      dbo.DepotData.isTransportByNotification, dbo.DepotData.Weighing
FROM         dbo.DistanceBetweenDepots RIGHT OUTER JOIN
                      dbo.DepotData LEFT OUTER JOIN
                      dbo.DefaultTransportDays ON dbo.DepotData.DepotId = dbo.DefaultTransportDays.DepotId LEFT OUTER JOIN
                      dbo.Regions ON dbo.DepotData.RegionsId = dbo.Regions.RegionsId LEFT OUTER JOIN
                      dbo.PartnerData ON dbo.DepotData.PartnerId = dbo.PartnerData.PartnerId ON dbo.DistanceBetweenDepots.DepotId = dbo.DepotData.DepotId LEFT OUTER JOIN
                      dbo.Modality LEFT OUTER JOIN
                      dbo.DepotsAndModalities ON dbo.Modality.ModalityId = dbo.DepotsAndModalities.ModalityId ON 
                      dbo.DepotData.DepotId = dbo.DepotsAndModalities.DepotId LEFT OUTER JOIN
                      dbo.DefaultTransportDays AS DefaultTransportDays_1 ON dbo.DepotData.DepotId = DefaultTransportDays_1.DepotId
WHERE     (dbo.DepotsAndModalities.Priority = 1) AND (dbo.DistanceBetweenDepots.Priority = 1) AND (DefaultTransportDays_1.isWinter = 'I') AND 
                      (dbo.DefaultTransportDays.isWinter = 'N')
GO

/****** Object:  View [dbo].[ReportAnimalDepotsSimpleView]    Script Date: 05/02/2010 22:26:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[ReportAnimalDepotsSimpleView]
AS
SELECT DISTINCT 
                      dbo.PartnerData.PartnerId, dbo.DepotData.DepotId, dbo.Regions.RegionsId, dbo.PartnerData.PartnerNumber, dbo.DepotData.DepotCode, dbo.PartnerData.Name, 
                      dbo.PartnerData.Place, dbo.DepotData.DepotAddress, dbo.Regions.regionCode, dbo.DistanceBetweenDepots.Distance, RouteCodes_1.Code AS RouteCode1, 
                      dbo.RouteCodes.Code AS RouteCode2, DefaultTransportDays_1.weeklyTransport AS transportInWinter, 
                      dbo.DefaultTransportDays.weeklyTransport AS transportInSummer, dbo.DepotData.isTransportByNotification, dbo.DepotData.Weighing
FROM         dbo.PartnerData INNER JOIN
                      dbo.DepotData ON dbo.PartnerData.PartnerId = dbo.DepotData.PartnerId LEFT OUTER JOIN
                      dbo.DepotsAndModalities ON dbo.DepotData.DepotId = dbo.DepotsAndModalities.DepotId LEFT OUTER JOIN
                      dbo.Routes ON dbo.DepotData.DepotId = dbo.Routes.DepotId LEFT OUTER JOIN
                      dbo.RouteCodes ON dbo.Routes.RouteCodesId = dbo.RouteCodes.RouteCodesId LEFT OUTER JOIN
                      dbo.Regions ON dbo.DepotData.RegionsId = dbo.Regions.RegionsId LEFT OUTER JOIN
                      dbo.DistanceBetweenDepots ON dbo.DepotData.DepotId = dbo.DistanceBetweenDepots.DepotId LEFT OUTER JOIN
                      dbo.Routes AS Routes_1 ON dbo.DepotData.DepotId = Routes_1.DepotId LEFT OUTER JOIN
                      dbo.DefaultTransportDays ON dbo.DepotData.DepotId = dbo.DefaultTransportDays.DepotId LEFT OUTER JOIN
                      dbo.DefaultTransportDays AS DefaultTransportDays_1 ON dbo.DepotData.DepotId = DefaultTransportDays_1.DepotId LEFT OUTER JOIN
                      dbo.RouteCodes AS RouteCodes_1 ON Routes_1.RouteCodesId = RouteCodes_1.RouteCodesId
WHERE     (dbo.DistanceBetweenDepots.Priority = 1) AND (Routes_1.Priority = 1) AND (dbo.Routes.Priority = 2) AND (DefaultTransportDays_1.isWinter = 'I') AND 
                      (dbo.DefaultTransportDays.isWinter = 'N')
GO

/****** Object:  View [dbo].[ModalitiesView]    Script Date: 05/02/2010 22:26:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[ModalitiesView]
AS
SELECT     dbo.DepotsAndModalities.*, dbo.Modality.Modality, dbo.Modality.ShortModality
FROM         dbo.DepotsAndModalities INNER JOIN
                      dbo.Modality ON dbo.DepotsAndModalities.ModalityId = dbo.Modality.ModalityId
GO

/****** Object:  View [dbo].[DistancesView]    Script Date: 05/02/2010 22:26:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[DistancesView]
AS
SELECT     dbo.DistanceBetweenDepots.*, dbo.CompanyDepots.Name, dbo.CompanyDepots.Place, dbo.CompanyDepots.Address
FROM         dbo.DistanceBetweenDepots INNER JOIN
                      dbo.CompanyDepots ON dbo.DistanceBetweenDepots.CompanyDepotsId = dbo.CompanyDepots.CompanyDepotsId
GO

/****** Object:  View [dbo].[DepotContainersView]    Script Date: 05/02/2010 22:26:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[DepotContainersView]
AS
SELECT     dbo.DepotContainers.*, dbo.Containers.ContainerCode, dbo.Containers.ContainerName, dbo.Containers.ContainerPrice
FROM         dbo.DepotContainers INNER JOIN
                      dbo.Containers ON dbo.DepotContainers.ContainersId = dbo.Containers.ContainersId
GO





