if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_DocCaption_SPR_WORKS]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[DocCaption] DROP CONSTRAINT FK_DocCaption_SPR_WORKS
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tbMol_SPR_WORKS]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tbMol] DROP CONSTRAINT FK_tbMol_SPR_WORKS
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_DocString_SprTovar]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[DocString] DROP CONSTRAINT FK_DocString_SprTovar
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tbMol_tbPredpr]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tbMol] DROP CONSTRAINT FK_tbMol_tbPredpr
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tbZakr_tbPredpr]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tbZakr] DROP CONSTRAINT FK_tbZakr_tbPredpr
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_SprTovar_tbPriznNac]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SprTovar] DROP CONSTRAINT FK_SprTovar_tbPriznNac
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[triggerDeleteSprTovarCalc]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[triggerDeleteSprTovarCalc]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TriggerDeleteTovBuh]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[TriggerDeleteTovBuh]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsUpdTov]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[InsUpdTov]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Insertkoeff]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[Insertkoeff]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[trInsUpdTbZakr]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[trInsUpdTbZakr]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[trigger_CheckCBU]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[trigger_CheckCBU]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Trig_Del_Predpr]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[Trig_Del_Predpr]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DocCaption]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DocCaption]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DocString]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DocString]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SPR_WORKS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SPR_WORKS]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SprTovar]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SprTovar]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbZakr]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbZakr]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbPredpr]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbPredpr]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbRschet]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbRschet]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbPriznNac]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbPriznNac]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbNastrSchet]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbNastrSchet]
GO

CREATE TABLE [dbo].[DocCaption] (
	[KodSkl] [smallint] NOT NULL ,
	[DateDoc] [datetime] NULL ,
	[NumDoc] [int] NOT NULL ,
	[VidDoc] [tinyint] NOT NULL ,
	[IdGrOper] [smallint] NULL ,
	[NumOtch] [smallint] NULL ,
	[DateOtch] [datetime] NULL ,
	[IdPredpr] [smallint] NOT NULL ,
	[IdDoc] [int] IDENTITY (1, 1) NOT NULL ,
	[Itogo] [decimal](18, 3) NULL ,
	[ItogoTov] [decimal](18, 3) NULL ,
	[ItogoTar] [decimal](18, 3) NULL ,
	[ItogoTovR] [decimal](18, 3) NULL ,
	[ItogoTarR] [decimal](18, 3) NULL ,
	[SumNDS] [decimal](18, 3) NULL ,
	[TabN] [int] NULL ,
	[NumReis] [varchar] (5) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[Tovar] [decimal](18, 2) NULL ,
	[Tovar1] [decimal](18, 2) NULL ,
	[Tovar2] [decimal](18, 2) NULL ,
	[Tovar3] [decimal](18, 2) NULL ,
	[Tara] [decimal](18, 2) NULL ,
	[Tara1] [decimal](18, 2) NULL ,
	[Tara2] [decimal](18, 2) NULL ,
	[Tara3] [decimal](18, 2) NULL ,
	[StTara] [decimal](18, 2) NULL ,
	[TovarR] [decimal](18, 2) NULL ,
	[TovarR1] [decimal](18, 2) NULL ,
	[TovarR2] [decimal](18, 2) NULL ,
	[TovarR3] [decimal](18, 2) NULL ,
	[TaraR] [decimal](18, 2) NULL ,
	[TaraR1] [decimal](18, 2) NULL ,
	[TaraR2] [decimal](18, 2) NULL ,
	[TaraR3] [decimal](18, 2) NULL ,
	[StTaraR] [decimal](18, 2) NULL ,
	[TovarOO] [decimal](18, 2) NULL ,
	[SumSkidPost9] [decimal](18, 2) NULL ,
	[SumSkidPost16] [decimal](18, 2) NULL ,
	[SumSkidPokup9] [decimal](18, 2) NULL ,
	[SumSkidPokup16] [decimal](18, 2) NULL ,
	[SumNDSPost10] [decimal](18, 2) NULL ,
	[SumNDSPost20] [decimal](18, 2) NULL ,
	[SumNDSPosr10] [decimal](18, 2) NULL ,
	[SumNDSPosr20] [decimal](18, 2) NULL ,
	[SumNDSOpt10] [decimal](18, 2) NULL ,
	[SumNDSOpt20] [decimal](18, 2) NULL ,
	[SumNDSRozn] [decimal](18, 2) NULL ,
	[SumNDSOO10] [decimal](18, 2) NULL ,
	[SumNDSOO20] [decimal](18, 2) NULL ,
	[SumNDSF9] [decimal](18, 2) NULL ,
	[SumNDSF16] [decimal](18, 2) NULL ,
	[SumNDSS9] [decimal](18, 2) NULL ,
	[SumNDSS16] [decimal](18, 2) NULL ,
	[SumNalProd] [decimal](18, 2) NULL ,
	[SumNalProd2] [decimal](18, 2) NULL ,
	[SumNalProd5] [decimal](18, 2) NULL ,
	[SumNalProd10] [decimal](18, 2) NULL ,
	[SumNalProd15] [decimal](18, 2) NULL ,
	[SumNalTr10] [decimal](18, 2) NULL ,
	[SumNaltr20] [decimal](18, 2) NULL ,
	[SumNacRozn] [decimal](18, 2) NULL ,
	[SumNacOpt10] [decimal](18, 2) NULL ,
	[SumNacOpt20] [decimal](18, 2) NULL ,
	[SumNacObpit] [decimal](18, 2) NULL ,
	[SumNacPosr10] [decimal](18, 2) NULL ,
	[SumNacPosr20] [decimal](18, 2) NULL ,
	[SumBns] [decimal](18, 2) NULL ,
	[SumAkc] [decimal](18, 2) NULL ,
	[SumOtkl] [decimal](18, 2) NULL ,
	[SumNDSOtkl] [decimal](18, 2) NULL ,
	[SumSkidOtkl] [decimal](18, 2) NULL ,
	[SumSkidPokupR9] [decimal](18, 2) NULL ,
	[SumSkidPokupR16] [decimal](18, 2) NULL ,
	[SumNDSNacObpit] [decimal](18, 2) NULL ,
	[DateOpl] [datetime] NULL ,
	[IdVidDog] [int] NULL ,
	[PriznBlock] [tinyint] NULL ,
	[SumNacPosr] [decimal](18, 2) NULL ,
	[SumSkidPost10] [decimal](18, 2) NULL ,
	[SumSkidPost18] [decimal](18, 2) NULL ,
	[SumSkidPost0] [decimal](18, 2) NULL ,
	[SumSkidPokup10] [decimal](18, 2) NULL ,
	[SumSkidPokup18] [decimal](18, 2) NULL ,
	[SumSkidPokup0] [decimal](18, 2) NULL ,
	[SumSkidOO10] [decimal](18, 2) NULL ,
	[SumSkidOO18] [decimal](18, 2) NULL ,
	[SumNdsSOO10] [decimal](18, 2) NULL ,
	[SumNdsSOO18] [decimal](18, 2) NULL ,
	[SumNDSFWSkid9] [decimal](18, 2) NULL ,
	[SumNDSFWSkid15] [decimal](18, 2) NULL ,
	[SumNacOptWTrN10] [decimal](18, 2) NULL ,
	[SumNacOptWTrN18] [decimal](18, 2) NULL ,
	[SumNacOpt0] [decimal](18, 2) NULL ,
	[SumNDSOptWTrN10] [decimal](18, 2) NULL ,
	[SumNDSOptWTrN18] [decimal](18, 2) NULL ,
	[SumNDSPokupS10] [decimal](18, 2) NULL ,
	[SumNDSPokupS18] [decimal](18, 2) NULL ,
	[SumNDSPostS10] [decimal](18, 2) NULL ,
	[SumNDSPostS18] [decimal](18, 2) NULL ,
	[ItogoOO] [decimal](18, 3) NULL ,
	[SumNalTr0] [decimal](18, 2) NOT NULL ,
	[SumNDSF9P] [decimal](18, 2) NULL ,
	[SumNDSF16P] [decimal](18, 2) NULL ,
	[SumNDSS9P] [decimal](18, 2) NULL ,
	[SumNDSS16P] [decimal](18, 2) NULL ,
	[Tovar24] [decimal](18, 2) NULL ,
	[TovarR24] [decimal](18, 2) NULL ,
	[SumNDSS24P] [decimal](18, 2) NULL ,
	[SumNDSS24] [decimal](18, 2) NULL ,
	[SumSkidPost24] [decimal](18, 2) NULL ,
	[SumSkidPokup24] [decimal](18, 2) NULL ,
	[SumSkidPokupR24] [decimal](18, 2) NULL ,
	[SumNDSTara] [decimal](18, 2) NULL ,
	[DateOtgr] [datetime] NULL ,
	[SumNdsPost24] [decimal](18, 2) NOT NULL ,
	[SumNdsPosr24] [decimal](18, 2) NOT NULL ,
	[SumNdsOpt24] [decimal](18, 2) NOT NULL ,
	[SumNalTr24] [decimal](18, 2) NOT NULL ,
	[SumNacOpt24] [decimal](18, 2) NOT NULL ,
	[SumNacPosr24] [decimal](18, 2) NOT NULL ,
	[SumSkidPostS24] [decimal](18, 2) NOT NULL ,
	[SumSkidPokupS24] [decimal](18, 2) NOT NULL ,
	[SumSkidOO24] [decimal](18, 2) NOT NULL ,
	[SumNdsOO24] [decimal](18, 2) NOT NULL ,
	[SumNacOptWTrN24] [decimal](18, 2) NOT NULL ,
	[SumNdsOptWTrN24] [decimal](18, 2) NOT NULL ,
	[SumNdsPokupS24] [decimal](18, 2) NOT NULL ,
	[SumNdsPostS24] [decimal](18, 2) NOT NULL ,
	[TovarS24] [decimal](18, 2) NOT NULL ,
	[SumNdsSOO24] [decimal](18, 2) NOT NULL ,
	[Tovar9] [decimal](18, 2) NOT NULL ,
	[Tovar10] [decimal](18, 2) NOT NULL ,
	[Tovar15] [decimal](18, 2) NOT NULL ,
	[Tovar18] [decimal](18, 2) NOT NULL ,
	[Tara9] [decimal](18, 2) NOT NULL ,
	[Tara10] [decimal](18, 2) NOT NULL ,
	[Tara15] [decimal](18, 2) NOT NULL ,
	[Tara18] [decimal](18, 2) NOT NULL ,
	[TovarR9] [decimal](18, 2) NOT NULL ,
	[TovarR10] [decimal](18, 2) NOT NULL ,
	[TovarR15] [decimal](18, 2) NOT NULL ,
	[TovarR18] [decimal](18, 2) NOT NULL ,
	[TaraR9] [decimal](18, 2) NOT NULL ,
	[TaraR10] [decimal](18, 2) NOT NULL ,
	[TaraR15] [decimal](18, 2) NOT NULL ,
	[TaraR18] [decimal](18, 2) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[DocString] (
	[NameVidTov] [varchar] (50) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[IdDoc] [int] NOT NULL ,
	[IdStr] [int] IDENTITY (1, 1) NOT NULL ,
	[PNT] [int] NOT NULL ,
	[KolM] [smallint] NULL ,
	[KolZ] [decimal](13, 3) NULL ,
	[KolF] [decimal](15, 5) NULL ,
	[KolKzp] [decimal](13, 3) NULL ,
	[CenO] [decimal](18, 3) NULL ,
	[CenRoz] [decimal](18, 3) NULL ,
	[CenSt] [decimal](18, 3) NULL ,
	[CenPor] [decimal](18, 3) NULL ,
	[NacOpt] [decimal](5, 2) NULL ,
	[NacRozn] [decimal](5, 2) NULL ,
	[NacObpit] [decimal](5, 2) NULL ,
	[NalProd] [decimal](6, 2) NULL ,
	[NalAkc] [decimal](5, 2) NULL ,
	[NalTr] [decimal](5, 2) NULL ,
	[NacPosr] [decimal](5, 2) NULL ,
	[NDS] [decimal](5, 2) NULL ,
	[SkidPokup] [decimal](5, 2) NULL ,
	[SkidPost] [decimal](5, 2) NULL ,
	[Bonus] [decimal](5, 2) NULL ,
	[SumNDS] [decimal](18, 3) NULL ,
	[SumNDSR] [decimal](18, 3) NULL ,
	[idSpec] [smallint] NULL ,
	[PrRazl] [char] (1) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[CenPriobr] [decimal](18, 3) NULL ,
	[Art] [varchar] (55) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[pnt_as_key] [int] NULL ,
	[IdPrizlog] [tinyint] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[SPR_WORKS] (
	[TABN] [int] NOT NULL ,
	[KATEGOR] [smallint] NULL ,
	[KLASS] [tinyint] NULL ,
	[PROFES] [smallint] NULL ,
	[OPLATA] [tinyint] NULL ,
	[TYPE] [tinyint] NULL ,
	[PODR] [smallint] NULL ,
	[UCH] [smallint] NULL ,
	[FIO] [varchar] (45) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[PROF] [smallint] NULL ,
	[OKLAD] [decimal](12, 2) NULL ,
	[TARF] [decimal](12, 2) NULL ,
	[AVANS] [decimal](12, 2) NULL ,
	[BALANS] [tinyint] NULL ,
	[OTPUSK] [decimal](10, 1) NULL ,
	[PRIEM] [datetime] NULL ,
	[TIME_WORKS] [datetime] NULL ,
	[UVOLNEN] [datetime] NULL ,
	[MAT_OTV] [bit] NULL ,
	[NBOOK] [varchar] (8) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[KODMOL] [varchar] (3) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[SprTovar] (
	[KodGr] [tinyint] NULL ,
	[KodPGr] [tinyint] NULL ,
	[KodVid] [tinyint] NULL ,
	[PNT] [int] IDENTITY (1, 1) NOT NULL ,
	[NameTov] [varchar] (30) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[Art] [varchar] (55) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[IdColor] [tinyint] NULL ,
	[Model] [char] (6) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[Sizes] [varchar] (15) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[Hight] [varchar] (15) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[Sort] [char] (3) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[EdIzID] [smallint] NULL ,
	[IdPredpr] [smallint] NULL ,
	[Protkl] [varchar] (35) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[Sertif] [varchar] (255) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[MinUp] [decimal](7, 3) NULL ,
	[Ves] [decimal](9, 5) NULL ,
	[VidUp] [char] (5) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[Fasov] [decimal](9, 5) NULL ,
	[IdCountry] [smallint] NULL ,
	[PNTTar1] [int] NULL ,
	[PNTTar2] [int] NULL ,
	[Cen$] [decimal](15, 3) NULL ,
	[CenIzg] [decimal](18, 3) NULL ,
	[CenPriobr] [decimal](18, 3) NULL ,
	[CenO] [decimal](18, 3) NULL ,
	[SkidPost] [decimal](5, 2) NULL ,
	[Izder] [decimal](5, 2) NULL ,
	[Rentab] [decimal](5, 2) NULL ,
	[DatePrih] [datetime] NULL ,
	[SrokGodn] [decimal] (9,2),-- COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[IdPriznNac] [tinyint] NULL ,
	[NacPosr] [decimal](5, 2) NULL ,
	[NDS] [decimal](5, 2) NULL ,
	[NalProd] [decimal](5, 2) NULL ,
	[NalTr] [decimal](5, 2) NULL ,
	[PriznSyria] [varchar] (50) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[Kompl] [smallint] NULL ,
	[Jirn] [decimal](5, 2) NULL ,
	[PrSocZ] [char] (1) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[idSpec] [smallint] NULL ,
	[KolPor] [decimal](6, 2) NULL ,
	[Vlajn] [decimal](5, 2) NULL ,
	[PrMenu] [bit] NULL ,
	[PrSyrja] [tinyint] NULL ,
	[IdSort] [tinyint] NULL ,
	[NameTovDop] [varchar] (200) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[KodR1] [tinyint] NOT NULL ,
	[KodR2] [tinyint] NOT NULL ,
	[GlKod] [varchar] (15) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[NameT] [varchar] (90) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[EdizDId] [smallint] NULL ,
	[Sertif1] [varchar] (255) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[BarKod] [varchar] (13) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[BarKodR] [varchar] (13) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[Sertif2] [varchar] (255) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[IdPost] [int] NULL ,
	[PrizBlok] [bit] NOT NULL ,
	[idkoeff] [int] NULL ,
	[tkoeff] [float] NULL ,
	[IdPrizlog] [tinyint] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbZakr] (
	[KODSKL] [int] NOT NULL ,
	[ZAKRITIE_DATE] [smalldatetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbPredpr] (
	[idPredpr] [int] IDENTITY (1, 1) NOT NULL ,
	[namePredpr] [varchar] (150) COLLATE SQL_Latin1_General_CP1251_CI_AS NOT NULL ,
	[namePredprShort] [varchar] (50) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[ZipCode] [decimal](6, 0) NULL ,
	[idCountry] [smallint] NOT NULL ,
	[idObl] [smallint] NOT NULL ,
	[idRaion] [smallint] NOT NULL ,
	[idZona] [smallint] NULL ,
	[idTown] [smallint] NULL ,
	[idSelSov] [smallint] NULL ,
	[StHoFl] [varchar] (30) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[FIODir] [varchar] (30) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[FIOGlBuh] [varchar] (30) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[Phone] [varchar] (30) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[Fax] [varchar] (30) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[Email] [varchar] (30) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[idGrPredpr] [smallint] NULL ,
	[idPodch] [smallint] NULL ,
	[idOkfs] [smallint] NULL ,
	[idOkopf] [smallint] NULL ,
	[idStatus] [smallint] NULL ,
	[KodOKPO] [varchar] (20) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[UNN] [varchar] (20) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[KodMFO] [varchar] (20) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[Dogovor] [varchar] (100) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[idPlat] [smallint] NULL ,
	[idPriznPartn] [smallint] NULL ,
	[kodPredpr] [varchar] (50) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[KtClient] [decimal](7, 2) NULL ,
	[Lic] [varchar] (50) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[DateLikv] [smalldatetime] NULL ,
	[BKS] [bit] NULL ,
	[GlobalCode] [varchar] (100) COLLATE SQL_Latin1_General_CP1251_CI_AS NULL ,
	[KodCBU] [bigint] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbRschet] (
	[idPredpr] [int] NOT NULL ,
	[idBank] [smallint] NOT NULL ,
	[Rschet] [varchar] (30) COLLATE SQL_Latin1_General_CP1251_CI_AS NOT NULL ,
	[osn] [bit] NULL ,
	[SlOsn] [bit] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbPriznNac] (
	[IdPriznNac] [tinyint] IDENTITY (1, 1) NOT NULL ,
	[NamePriznNac] [varchar] (30) COLLATE SQL_Latin1_General_CP1251_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbNastrSchet] (
	[IdPredpr] [int] NOT NULL ,
	[IdSchetSubSchet] [int] NOT NULL ,
	[OsnSchet] [varchar] (10) COLLATE SQL_Latin1_General_CP1251_CI_AS NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[DocCaption] WITH NOCHECK ADD 
	CONSTRAINT [PK_DocCaption] PRIMARY KEY  CLUSTERED 
	(
		[KodSkl],
		[NumDoc],
		[VidDoc],
		[IdPredpr]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[SprTovar] WITH NOCHECK ADD 
	CONSTRAINT [PK_SprTovar] PRIMARY KEY  CLUSTERED 
	(
		[PNT]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tbZakr] WITH NOCHECK ADD 
	CONSTRAINT [PK_tbZakr] PRIMARY KEY  CLUSTERED 
	(
		[KODSKL],
		[ZAKRITIE_DATE]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tbNastrSchet] WITH NOCHECK ADD 
	CONSTRAINT [PK_tbNastrSchet] PRIMARY KEY  CLUSTERED 
	(
		[IdPredpr]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[DocCaption] ADD 
	CONSTRAINT [DF_DocCaption_PriznBlock] DEFAULT (0) FOR [PriznBlock],
	CONSTRAINT [DF__DocCaptio__SumNa__64CDBE05] DEFAULT (0) FOR [SumNalTr0],
	CONSTRAINT [DF__DocCaptio__SumNd__43ECB87E] DEFAULT (0) FOR [SumNdsPost24],
	CONSTRAINT [DF__DocCaptio__SumNd__44E0DCB7] DEFAULT (0) FOR [SumNdsPosr24],
	CONSTRAINT [DF__DocCaptio__SumNd__45D500F0] DEFAULT (0) FOR [SumNdsOpt24],
	CONSTRAINT [DF__DocCaptio__SumNa__46C92529] DEFAULT (0) FOR [SumNalTr24],
	CONSTRAINT [DF__DocCaptio__SumNa__47BD4962] DEFAULT (0) FOR [SumNacOpt24],
	CONSTRAINT [DF__DocCaptio__SumNa__48B16D9B] DEFAULT (0) FOR [SumNacPosr24],
	CONSTRAINT [DF__DocCaptio__SumSk__49A591D4] DEFAULT (0) FOR [SumSkidPostS24],
	CONSTRAINT [DF__DocCaptio__SumSk__4A99B60D] DEFAULT (0) FOR [SumSkidPokupS24],
	CONSTRAINT [DF__DocCaptio__SumSk__4B8DDA46] DEFAULT (0) FOR [SumSkidOO24],
	CONSTRAINT [DF__DocCaptio__SumNd__4C81FE7F] DEFAULT (0) FOR [SumNdsOO24],
	CONSTRAINT [DF__DocCaptio__SumNa__4D7622B8] DEFAULT (0) FOR [SumNacOptWTrN24],
	CONSTRAINT [DF__DocCaptio__SumNd__4E6A46F1] DEFAULT (0) FOR [SumNdsOptWTrN24],
	CONSTRAINT [DF__DocCaptio__SumNd__4F5E6B2A] DEFAULT (0) FOR [SumNdsPokupS24],
	CONSTRAINT [DF__DocCaptio__SumNd__50528F63] DEFAULT (0) FOR [SumNdsPostS24],
	CONSTRAINT [DF__DocCaptio__Tovar__5146B39C] DEFAULT (0) FOR [TovarS24],
	CONSTRAINT [DF__DocCaptio__SumNd__523AD7D5] DEFAULT (0) FOR [SumNdsSOO24],
	CONSTRAINT [DF__DocCaptio__Tovar__60DDFCE7] DEFAULT (0) FOR [Tovar9],
	CONSTRAINT [DF__DocCaptio__Tovar__61D22120] DEFAULT (0) FOR [Tovar10],
	CONSTRAINT [DF__DocCaptio__Tovar__62C64559] DEFAULT (0) FOR [Tovar15],
	CONSTRAINT [DF__DocCaptio__Tovar__63BA6992] DEFAULT (0) FOR [Tovar18],
	CONSTRAINT [DF__DocCaptio__Tara9__64AE8DCB] DEFAULT (0) FOR [Tara9],
	CONSTRAINT [DF__DocCaptio__Tara1__65A2B204] DEFAULT (0) FOR [Tara10],
	CONSTRAINT [DF__DocCaptio__Tara1__6696D63D] DEFAULT (0) FOR [Tara15],
	CONSTRAINT [DF__DocCaptio__Tara1__678AFA76] DEFAULT (0) FOR [Tara18],
	CONSTRAINT [DF__DocCaptio__Tovar__687F1EAF] DEFAULT (0) FOR [TovarR9],
	CONSTRAINT [DF__DocCaptio__Tovar__697342E8] DEFAULT (0) FOR [TovarR10],
	CONSTRAINT [DF__DocCaptio__Tovar__6A676721] DEFAULT (0) FOR [TovarR15],
	CONSTRAINT [DF__DocCaptio__Tovar__6B5B8B5A] DEFAULT (0) FOR [TovarR18],
	CONSTRAINT [DF__DocCaptio__TaraR__6C4FAF93] DEFAULT (0) FOR [TaraR9],
	CONSTRAINT [DF__DocCaptio__TaraR__6D43D3CC] DEFAULT (0) FOR [TaraR10],
	CONSTRAINT [DF__DocCaptio__TaraR__6E37F805] DEFAULT (0) FOR [TaraR15],
	CONSTRAINT [DF__DocCaptio__TaraR__6F2C1C3E] DEFAULT (0) FOR [TaraR18]
GO

 CREATE  INDEX [IX_DocCaption] ON [dbo].[DocCaption]([IdDoc]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[DocString] ADD 
	CONSTRAINT [PK_DocString] PRIMARY KEY  NONCLUSTERED 
	(
		[IdStr]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

 CREATE  INDEX [IX_DocString] ON [dbo].[DocString]([IdDoc]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [DocString0] ON [dbo].[DocString]([IdDoc], [PNT], [KolF]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_DocString_1] ON [dbo].[DocString]([PNT]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[SPR_WORKS] ADD 
	CONSTRAINT [PK_SPR_WORKS] PRIMARY KEY  NONCLUSTERED 
	(
		[TABN]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[SprTovar] ADD 
	CONSTRAINT [DF__SprTovar__KodR1__6423B28F] DEFAULT (0) FOR [KodR1],
	CONSTRAINT [DF__SprTovar__KodR2__6517D6C8] DEFAULT (0) FOR [KodR2],
	CONSTRAINT [DF_SprTovar_PrizBlok] DEFAULT (0) FOR [PrizBlok]
GO

ALTER TABLE [dbo].[tbPredpr] ADD 
	CONSTRAINT [DF_tbPredpr_idPriznPartn] DEFAULT (0) FOR [idPriznPartn],
	CONSTRAINT [PK_Predpr] PRIMARY KEY  NONCLUSTERED 
	(
		[idPredpr]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

 CREATE  INDEX [IX_tbPredpr_idCountry] ON [dbo].[tbPredpr]([idCountry]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tbPredpr_idObl] ON [dbo].[tbPredpr]([idObl]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tbPredpr_idRaion] ON [dbo].[tbPredpr]([idRaion]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tbPredpr_idPlat] ON [dbo].[tbPredpr]([idPlat]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tbPredpr_kodMFO] ON [dbo].[tbPredpr]([KodMFO]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tbRSchet_idBank] ON [dbo].[tbRschet]([idBank]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tbRSchet_RSchet] ON [dbo].[tbRschet]([Rschet]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tbRSchet_idPredpr] ON [dbo].[tbRschet]([idPredpr]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbPriznNac] ADD 
	CONSTRAINT [PK_tbPriznNac] PRIMARY KEY  NONCLUSTERED 
	(
		[IdPriznNac]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] ,
	CONSTRAINT [IX_tbPriznNac] UNIQUE  NONCLUSTERED 
	(
		[IdPriznNac]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[DocCaption] ADD 
	CONSTRAINT [FK_DocCaption_SPR_WORKS] FOREIGN KEY 
	(
		[TabN]
	) REFERENCES [dbo].[SPR_WORKS] (
		[TABN]
	)
GO

ALTER TABLE [dbo].[DocString] ADD 
	CONSTRAINT [FK_DocString_SprTovar] FOREIGN KEY 
	(
		[PNT]
	) REFERENCES [dbo].[SprTovar] (
		[PNT]
	)
GO

ALTER TABLE [dbo].[SprTovar] ADD 
	CONSTRAINT [FK_SprTovar_SprEdiz] FOREIGN KEY 
	(
		[EdIzID]
	) REFERENCES [dbo].[SprEdiz] (
		[EdizId]
	),
	CONSTRAINT [FK_SprTovar_SprPriznSyria] FOREIGN KEY 
	(
		[PrSyrja]
	) REFERENCES [dbo].[SprPriznSyria] (
		[IDPriznSyria]
	) ON UPDATE CASCADE ,
	CONSTRAINT [FK_SprTovar_SprRaz1] FOREIGN KEY 
	(
		[KodGr],
		[KodPGr],
		[KodR1]
	) REFERENCES [dbo].[SprRaz1] (
		[KodGr],
		[KodPGr],
		[KodR1]
	),
	CONSTRAINT [FK_SprTovar_SprRaz2] FOREIGN KEY 
	(
		[KodGr],
		[KodPGr],
		[KodR2]
	) REFERENCES [dbo].[SprRaz2] (
		[KodGr],
		[KodPGr],
		[KodR2]
	),
	CONSTRAINT [FK_SprTovar_SprVid] FOREIGN KEY 
	(
		[KodGr],
		[KodPGr],
		[KodVid]
	) REFERENCES [dbo].[SprVid] (
		[KodGr],
		[KodPGr],
		[KodVid]
	),
	CONSTRAINT [FK_SprTovar_tbPriznNac] FOREIGN KEY 
	(
		[IdPriznNac]
	) REFERENCES [dbo].[tbPriznNac] (
		[IdPriznNac]
	)
GO

ALTER TABLE [dbo].[tbZakr] ADD 
	CONSTRAINT [FK_tbZakr_tbPredpr] FOREIGN KEY 
	(
		[KODSKL]
	) REFERENCES [dbo].[tbPredpr] (
		[idPredpr]
	)
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




CREATE TRIGGER triggerDeleteSprTovarCalc
ON sprtovar
FOR DELETE
AS 
IF (DATABASEPROPERTY('technolog', 'IsOffLine') IS NOT NULL)
BEGIN
DECLARE @PNT_DELETED INT
IF (SELECT COUNT(*) FROM deleted) > 0
BEGIN
   SELECT @PNT_DELETED = pnt FROM deleted	
   IF (SELECT COUNT(*) FROM technolog.dbo.tbFactRecString WHERE pnt = @PNT_DELETED) > 0	
   BEGIN
     RAISERROR ('Can''t be deleted because tbFactRecString contains such TOVAR',16,2)
     IF @@TRANCOUNT > 0
     BEGIN  
       ROLLBACK TRAN	
     END
     RETURN	
   END
   IF (SELECT COUNT(*) FROM deleted) > 0
   BEGIN
    SELECT @PNT_DELETED = pnt FROM deleted	
    IF (SELECT COUNT(*) FROM technolog.dbo.tbNormRecCaption WHERE pnt = @PNT_DELETED) > 0	
    BEGIN
     RAISERROR ('Can''t be deleted because tbNormRecCaption contains such TOVAR',16,2)
     IF @@TRANCOUNT > 0
     BEGIN  
       ROLLBACK TRAN	
     END
     RETURN	
    END
   END
   IF (SELECT COUNT(*) FROM deleted) > 0
   BEGIN
    SELECT @PNT_DELETED = pnt FROM deleted	
    IF (SELECT COUNT(*) FROM maindatabase.dbo.docstring WHERE pnt = @PNT_DELETED) > 0	
    BEGIN
     RAISERROR ('Can''t be deleted because docstring table contains such TOVAR',16,2)
     IF @@TRANCOUNT > 0
     BEGIN  
       ROLLBACK TRAN	
     END
     RETURN	
    END
   END
END
END





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE TRIGGER [TriggerDeleteTovBuh] ON [dbo].[SprTovar] 
FOR  DELETE 
AS
declare @PNT integer
select @PNT = PNT from Deleted

if( Exists ( select * from DocString where PNT = @PNT)or
  Exists ( select * from Buh.dbo.tbDocTovOt where PNT = @PNT)) 
begin
 RaisError('Удаление невозможно! Данный товар присутствует в документах !',16,10) 
 RollBack Transaction
end



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




CREATE TRIGGER InsUpdTov ON dbo.SprTovar 
FOR INSERT, UPDATE	
AS
BEGIN
declare @Pnt int

 BEGIN
  SELECT @Pnt =Pnt FROM Inserted
  print 'ПНТ'+str(@pnt)
  if Update(KodR1)or Update(KodR2) or Update(NameTov)
  begin
    EXEC dbo.tovGlKodName  @Pnt
  end 
END

END





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


CREATE TRIGGER [Insertkoeff] ON dbo.SprTovar 
FOR INSERT
AS
declare @kodGr int
declare @kodPGr int
declare @kodVid int
declare @Jirn int
declare @pnt int
declare @idkoeff int
declare @koeff int


select @kodGr=kodGR  from inserted
select @kodPGr=kodPgr  from inserted
select @kodVid=kodVid  from inserted
select @jirn=jirn  from inserted
select @pnt=pnt  from inserted

select @koeff=koeff,@idkoeff=idkoeff from tbCoefPer  where  kodGr =@kodGr and kodPGr =@kodPGr and kodVid =@kodVid and Jirn =@Jirn

if @idkoeff is null begin
select @koeff=koeff,@idkoeff=idkoeff from tbCoefPer  where  kodGr =@kodGr and kodPGr =@kodPGr and kodVid =@kodVid 
	if @idkoeff is null begin
	select @koeff=koeff,@idkoeff=idkoeff from tbCoefPer  where  kodGr =@kodGr and kodPGr =@kodPGr and kodVid is null and Jirn =@Jirn
		if @idkoeff is null begin
		select @koeff=koeff,@idkoeff=idkoeff from tbCoefPer  where  kodGr =@kodGr and kodPGr =@kodPGr and kodVid is null
			if @idkoeff is null begin
			select @koeff=koeff,@idkoeff=idkoeff from tbCoefPer  where  kodGr =@kodGr and kodPGr is null and kodVid is null and Jirn =@Jirn
				if @idkoeff is null
				select @koeff=koeff,@idkoeff=idkoeff from tbCoefPer  where  kodGr =@kodGr and kodPGr is null and kodVid is null
			end
		end

	end

end

update [SprTovar] 
set [tkoeff] =@koeff,
idkoeff =@idkoeff
where pnt =@pnt



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE TRIGGER trInsUpdTbZakr ON tbzakr FOR INSERT, UPDATE
AS 
DECLARE @DATE SMALLDATETIME
DECLARE @CODE_WAREHOUSE INT
BEGIN
   IF UPDATE(zakritie_date) OR UPDATE(kodskl) 
   BEGIN	
	  SELECT @CODE_WAREHOUSE = kodskl, @DATE = zakritie_date FROM inserted
                IF (SELECT COUNT(*) FROM tbzakr WHERE MONTH(zakritie_date) = MONTH(@DATE) AND YEAR(zakritie_date) = YEAR(@DATE) AND kodskl = @CODE_WAREHOUSE)  > 1
	  BEGIN
		RAISERROR('Данный период уже закрыт', 16, 1)
		ROLLBACK TRANSACTION
	   END		
   END
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


Create TRIGGER trigger_CheckCBU ON dbo.tbPredpr 
FOR INSERT, UPDATE
AS
DECLARE @mfo varchar(20), @cbu bigint, @idPredpr bigint
DECLARE @errorMsg as varchar(1000)

SELECT 
@mfo = KodMFO, @cbu = KodCBU, @idPredpr = idPredpr
FROM 
	inserted 
--check CBU
IF (@mfo is not null) and (isnull(@cbu,0) <> 0)
BEGIN
	if exists (select * from TbPredpr where (KodCBU = @cbu) and (idPredpr <> @idPredpr) and (kodMFO = @mfo))
	begin

		set @errorMsg = 'уже существует банк с таким ЦБУ('+cast(@cbu as varchar(20)) + '). Операция отменена.(КодМФО='+cast(@mfo as varchar(20)) +' ; Код банка='+  cast(@idPredpr as varchar(20)) +')'
		RAISERROR (@errorMsg, 16, 1)
		ROLLBACK TRANSACTION
	end
END

--CheckMFO
IF (@mfo is not null) and (isnull(@cbu,0) = 0)
BEGIN
	if exists (select * from TbPredpr where (idPredpr <> @idPredpr) and (kodMFO = @mfo))
	begin
		set @errorMsg = 'уже существует банк с таким МФО('+cast(@mfo as varchar(20)) + '). Возможно Вы забыли ввести ЦБУ. Операция отменена.(idBank='+  cast(@idPredpr as varchar(20)) +')'
		RAISERROR (@errorMsg, 16, 1)
		ROLLBACK TRANSACTION
	end
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO






CREATE TRIGGER [Trig_Del_Predpr] ON [dbo].[tbPredpr] 
FOR DELETE 
AS
Declare @IDPredpr integer
Select @IdPredpr=IdPredpr from Deleted
if  (Exists(Select * from DocCaption where KodSkl=@IdPredpr)) or
(Exists(Select * from DocCaption where IdPredpr=@IdPredpr)) or 
(Exists(Select * from Buh..tbTovOtSh where IdPredprOb=@IdPredpr)) or
(Exists(Select * from Buh..tbTovOt where IdPredprPar=@IdPredpr)) or
(Exists(Select * from Buh..tbNSaldo where IdPredpr=@IdPredpr)) or
(Exists(Select * from Buh..tbKassa where IdPredprOb=@IdPredpr)) or
(Exists(Select * from Buh..tbBankV where IdPredprOb=@IdPredpr)) or
(Exists(Select * from Buh..tbBankV where IdPredprPar=@IdPredpr)) or
(Exists(Select * from Buh..tbSpravka where IdPredpr=@IdPredpr)) or
(Exists(Select * from Buh..tbSpravka where IdPredprPart=@IdPredpr)) or
(Exists(Select * from Buh..tbBankSh where IdBank=@IdPredpr))

begin
RaisError ('Удаление невозможно !!! Присутствует в документах',16,10)
RollBack Transaction
end













GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




use Technolog

delete from tbGoods
delete from tbGoodsRemains
delete from tbDataPlanCalc
delete from tbDataFactCalc
delete from tbRaschFactCalc
delete from tbRaschPlanCalc
delete from tbFactRecString
delete from tbFactRecCaption
delete from tbDataFactCalc
delete from tbNormRecCaption
delete from tbZamen
delete from tbNormRecString
delete from tbSyrie
delete from tbProd
delete from tbProizvZadanProduceCaption
delete from tbProizvZadan
delete from GrOperSettings


use maindatabase 
delete from tbMol