Alter procedure spGetInvoiceICVData
(
@FromDate datetime,
@ToDate datetime,
@ReportType int = 0,
@InvType nvarchar(30)
)
As
Begin
declare  @StandardInvoiceCount int, @SimplifiedInvoiceCount int, @StandardCreditNoteCount int,  
@SimplifiedCreditNoteCount int, @StandardDebitNoteCount int , @SimplifiedDebitNoteCount int,
@SuccessfullCount int,@WarningCount int, @FailedCount int
select @StandardInvoiceCount = count(*) from InvoiceICV Where DOCTYPE = 'Standard Invoice' and DocDate between @FromDate and @ToDate
select @SimplifiedInvoiceCount = count(*) from InvoiceICV Where DOCTYPE = 'Simplified Invoice' and DocDate between @FromDate and @ToDate
select @StandardCreditNoteCount = count(*) from InvoiceICV Where DOCTYPE = 'Standard Credit Note' and DocDate between @FromDate and @ToDate
select @SimplifiedCreditNoteCount = count(*) from InvoiceICV Where DOCTYPE = 'Simplified Credit Note' and DocDate between @FromDate and @ToDate
select @StandardDebitNoteCount = count(*) from InvoiceICV Where DOCTYPE = 'Standard Debit Note' and DocDate between @FromDate and @ToDate
select @SimplifiedDebitNoteCount = count(*) from InvoiceICV Where DOCTYPE = 'Simplified Debit Note' and DocDate between @FromDate and @ToDate
if @ReportType = 0
begin
select @StandardInvoiceCount + @SimplifiedInvoiceCount as InvoiceCount, @StandardCreditNoteCount + @SimplifiedCreditNoteCount as InvoiceRtnCount, @StandardDebitNoteCount + @SimplifiedDebitNoteCount as DebitInvoiceCount
end
else if @ReportType = 1
begin
select @StandardInvoiceCount as StandardInvoiceCount, @SimplifiedInvoiceCount as SimplifiedInvoiceCount
end
else if @ReportType = 2
begin
select @StandardCreditNoteCount as StandardCreditNoteCount, @SimplifiedCreditNoteCount as SimplifiedCreditNoteCount
end
else if @ReportType = 3
begin
select @StandardDebitNoteCount as StandardDebitNoteCount, @SimplifiedDebitNoteCount as SimplifiedDebitNoteCount
end
else if @ReportType > 3 and @ReportType < 10
begin
select @SuccessfullCount = count(*) from InvoiceICV Where DOCTYPE =@InvType  and invsubValidationStatus = 'PASS' and DocDate between @FromDate and @ToDate
select @WarningCount = count(*) from InvoiceICV Where DOCTYPE = @InvType and invsubValidationStatus = 'WARNING' and DocDate between @FromDate and @ToDate
select @FailedCount = count(*) from InvoiceICV Where DOCTYPE = @InvType and invsubValidationStatus = 'ERROR' and ReUploaded = 0 and DocDate between @FromDate and @ToDate
select @SuccessfullCount as SuccessfullCount,@WarningCount as WarningCount,@FailedCount as FailedCount,@InvType +' Submitted Details' as CardTitle
end
else if @ReportType = 10
begin
SELECT 
        case when @InvType = 'Standard Invoice' or @InvType = 'Simplified Invoice' then inv.Id else ir.Id end AS InvNo, DocDate AS [Date], DOCTYPE AS InvType,invsubValidationStatus AS [Status], invsubClearanceStatus AS [Message]
    FROM InvoiceICV icv
	left join Invoice inv on icv.DocNo = inv.InvNo
	left join InvoiceReturn ir on icv.DocNo = ir.InvoiceRetNo
    WHERE DOCTYPE =@InvType and invsubValidationStatus = 'PASS' and  DocDate BETWEEN @FromDate AND @ToDate;
end
else if @ReportType = 11
begin
SELECT 
        case when @InvType = 'Standard Invoice' or @InvType = 'Simplified Invoice' then inv.Id else ir.Id end AS InvNo, DocDate AS [Date], DOCTYPE AS InvType,invsubValidationStatus AS [Status], invsubWarningMessagesMESSAGE AS [Message]
    FROM InvoiceICV icv
	left join Invoice inv on icv.DocNo = inv.InvNo
	left join InvoiceReturn ir on icv.DocNo = ir.InvoiceRetNo
    WHERE DOCTYPE =@InvType and invsubValidationStatus = 'WARNING' and  DocDate BETWEEN @FromDate AND @ToDate;
end
else if @ReportType = 12
begin
SELECT 
        case when @InvType = 'Standard Invoice' or @InvType = 'Simplified Invoice' then inv.Id else ir.Id end AS InvNo, DocDate AS [Date], DOCTYPE AS InvType,invsubValidationStatus AS [Status], invsubErrorMessagesMESSAGE AS [Message]
    FROM InvoiceICV icv
	left join Invoice inv on icv.DocNo = inv.InvNo
	left join InvoiceReturn ir on icv.DocNo = ir.InvoiceRetNo
    WHERE DOCTYPE =@InvType and invsubValidationStatus = 'ERROR' and ReUploaded = 0 and  DocDate BETWEEN @FromDate AND @ToDate;
end
end