: !/bin/sh

. tbsserver_vars

#tbssql <<SQLCMD0
sqsh -S $server -D $dbname -U $username -P $password <<SQLCMD0
/* spInvoiceReport "InvDate", "CustomerList", "InvoiceNo"
*/
--#spInvoiceReport "2010-04-01", "'VLP'", "''"
spInvoiceReport "", "", "'INV003311'"
\go -m html
SQLCMD0


cat >/dev/null <<SP0


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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE spInvoiceReport
@InvDate		varchar(256) = NULL,
@CustomerList		varchar(8000) = NULL,
@InvNo		varchar(8000) = NULL

AS

SET NOCOUNT ON

DECLARE @strSQL1 		varchar(8000)
DECLARE @strSQL2 		varchar(8000)
DECLARE @strSQL3 		varchar(8000)

IF @InvNo = ''
	BEGIN
	SELECT 	@strSQL2 = ' AND CONVERT(smalldatetime,tInvoice.InvoiceDate) = ''' + @InvDate + ''' ' 
	IF @CustomerList  <> ''
		BEGIN
		SELECT 	@strSQL2 = @strSQL2 + ' AND tInvoice.CustomerID IN (' + @CustomerList + ') '
		END
	END
ELSE
	BEGIN
	SELECT 	@InvNo=REPLACE(@InvNo,'$','''')
	SELECT 	@strSQL2 = ' AND tInvoice.PlatinumInvoiceNo IN  (' + @InvNo + ') '
	END



SELECT  @strSQL1 =
'SELECT tCustomer.*,
   	 tInvoiceDetail.AmountExGST,
	 tInvoiceDetail.Period,
 	tInvoiceDetail.BatchID,
   	 tInvoice.*,
	 tInvoiceDefault.*,
	tReportGroup.ReportOrder, 
	 line_desc = tInvoiceDetail.Description,
	ContactName = tARContact.FullName,
	ContactPhone = tARContact.Phone,
	ContactEmail = tARContact.Email

FROM  tInvoiceDefault, tInvoiceDetail 		(NOLOCK) 	
INNER JOIN tInvoice 		(NOLOCK) 	ON tInvoiceDetail.InvoiceID = tInvoice.ID 
INNER JOIN tCustomer 		(NOLOCK) 	ON tInvoice.CustomerID = tCustomer.ID 
INNER JOIN tElement (NOLOCK) ON tInvoiceDetail.ElementID = tElement.ID
LEFT OUTER JOIN tBatchType 	(NOLOCK)	ON tInvoiceDetail.BatchTypeID = tBatchType.ID
LEFT OUTER JOIN tARContact	(NOLOCK) 	ON tCustomer.ARContactID = tARContact.ID
LEFT OUTER JOIN tReportGroup (NOLOCK) ON tElement.ReportGroupID = tReportGroup.ID
WHERE  tInvoice.PlatinumInvoiceNo IS NOT NULL AND tInvoice.CreditNoteFlag = 0 '


SELECT @strSQL3 = 'ORDER BY  tCustomer.ShipTo, tInvoice.PlatinumInvoiceNo, tReportGroup.ReportOrder,  tInvoiceDetail.Description, tInvoiceDetail.BatchID'

/*
WHERE  tInvoiceDetail.AmountExGST <> 0   AND tInvoice.PlatinumInvoiceNo IS NOT NULL AND tInvoice.CreditNoteFlag = 0 '
'PRINT @strSQL1 + @strSQL2  + @strSQL3
return
*/

EXEC (@strSQL1 + @strSQL2  + @strSQL3)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
SP0