alter table SendBoxInvoice add InvoiceComplate bit not null default(0)
Go
CREATE TABLE [dbo].[SendBoxInvoiceLineType](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[InvoiceId] [int] NULL,
	[IID] [nvarchar](50) NULL,
	[InvoicedQuantityunitCode] [nvarchar](50) NULL,
	[InvoicedQuantity] [decimal](20, 2) NULL,
	[AdditionalItemIdentification] [nvarchar](50) NULL,
	[BrandName] [nvarchar](255) NULL,
	[BuyersItemIdentification] [nvarchar](50) NULL,
	[CommodityClassification] [nvarchar](50) NULL,
	[Description] [nvarchar](max) NULL,
	[Keyword] [nvarchar](50) NULL,
	[ManufacturersItemIdentification] [nvarchar](50) NULL,
	[ModelName] [nvarchar](max) NULL,
	[Name] [nvarchar](max) NULL,
	[OriginCountry] [nvarchar](50) NULL,
	[SellersItemIdentification] [nvarchar](50) NULL,
	[LineExtensionAmount] [decimal](20, 2) NULL,
	[Note] [nvarchar](max) NULL,
	[CurrencyCode] [nvarchar](50) NULL,
	[PriceAmount] [decimal](20, 2) NULL,
	[TaxAmountcurrencyID] [nvarchar](50) NULL,
	[TaxAmount] [decimal](20, 2) NULL,
	[SerrialId] [nvarchar](50) NULL,
 CONSTRAINT [PK_SendBoxInvoiceLineType] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SendBoxTaxSubtotal](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[InvoiceId] [int] NOT NULL,
	[InvoiceItemId] [int] NOT NULL,
	[CalculationSequenceNumeric] [decimal](18, 0) NULL,
	[Percent_] [decimal](20, 2) NULL,
	[PerUnitAmountcurrencyID] [nvarchar](50) NULL,
	[PerUnitAmount] [decimal](20, 2) NULL,
	[TaxableAmountcurrencyID] [nvarchar](50) NULL,
	[TaxableAmount] [decimal](20, 2) NULL,
	[TaxAmountcurrencyID] [nvarchar](50) NULL,
	[TaxAmount] [decimal](20, 2) NULL,
	[TaxCategoryName] [nvarchar](255) NULL,
	[TaxExemptionReason] [nvarchar](255) NULL,
	[TaxExemptionReasonCode] [nvarchar](50) NULL,
	[TaxSchemeID] [nvarchar](50) NULL,
	[TaxSchemeName] [nvarchar](250) NULL,
	[TaxTypeCode] [nvarchar](50) NULL,
 CONSTRAINT [PK_SendBoxTaxSubtotal] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




create view [dbo].[vw_InvoicessSubTotal]
as

select i.InvoiceNo,i.CurrencyType,i.SenderIdentifier,i.SenderTitle,i.ReceiverIdentifier,i.ReceiverTitle,
i.InvoiceStatus,i.TaxExclusiveAmount,i.TaxInclusiveAmount,il.PriceAmount,
i.IslemNo,ts.Percent_,ts.PerUnitAmount,ts.TaxableAmount,ts.TaxAmount,
ts.TaxExemptionReason,ts.TaxExemptionReasonCode,i.InvoiceDate,
ts.TaxSchemeName,ts.TaxTypeCode, 'Alış' AS Tip from Invoice i
join TaxSubtotal ts on ts.InvoiceId=i.Id
join  InvoiceLineType il on il.InvoiceId=i.Id and il.Id=ts.InvoiceItemId
where InvoiceStatus<>'Reddedildi'
union all
select  si.InvoiceNo,si.CurrencyType,si.SenderIdentifier,si.SenderTitle,si.ReceiverIdentifier,si.ReceiverTitle,
'Onaylandı' InvoiceStatus,si.TaxExclusiveAmount,si.TaxInclusiveAmount,il.PriceAmount,
si.IslemNo,sti.Percent_,sti.PerUnitAmount,sti.TaxableAmount,sti.TaxAmount,
sti.TaxExemptionReason,sti.TaxExemptionReasonCode,si.InvoiceDate,
sti.TaxSchemeName,sti.TaxTypeCode, CASE si.Type WHEN 1 THEN 'Satış Efatura' WHEN 2 THEN 'Satış EArşiv' END AS Tip 
 from SendBoxInvoice si
join SendBoxTaxSubtotal sti on sti.InvoiceId=si.Id
join  SendBoxInvoiceLineType il on il.InvoiceId=si.Id and il.Id=sti.InvoiceItemId
GO





SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE view [dbo].[vw_Invoicess]
as
with electronik as
(
select buy.InvoiceNo,buy.CurrencyType,buy.EnvelopeUUId,buy.InvoiceDate,buy.InvoiceStatus,
buy.InvoiceType,buy.InvoiceTypeCode,buy.ReceiverIdentifier,buy.ReceiverTitle,
buy.SenderIdentifier,buy.SenderTitle,buy.TaxAmount,buy.TaxExclusiveAmount,buy.TaxInclusiveAmount,buy.IslemNo,'Alış' Tip
from Invoice buy
--order by buy.Id
union all
select sel.InvoiceNo,sel.CurrencyType,sel.EnvelopeUUId,sel.InvoiceDate,'Onaylandı' InvoiceStatus,
'SATIS' InvoiceType,sel.InvoiceTypeCode,sel.ReceiverIdentifier,sel.ReceiverTitle,
sel.SenderIdentifier,sel.SenderTitle,sel.TaxAmount,sel.TaxExclusiveAmount,sel.TaxInclusiveAmount,sel.IslemNo,case sel.Type when 1 then 'Satış Efatura' when 2 then 'Satış EArşiv' end  Tip from SendBoxInvoice sel
--order by sel.Id
) 
select * from electronik
GO

