/*                                     */
/*  TBIMS - Service List functionality */
/*                                     */

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

/*       */
/* Views */
/*       */

/* ------------- */
/* vwServiceList */
/* ------------- */

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.vwServiceList
AS
SELECT     dbo.tServiceID.ServiceID, dbo.tServiceID.Service, dbo.tSupplier.SupplierName, dbo.tServiceType.Description AS ServiceType, dbo.tCustomer.ShipTo, 
                      dbo.tCustomer.FullName AS Customer, dbo.tServiceID.InstallationDate, 
                      CAST(YEAR(dbo.tServiceID.InstallationDate) AS VARCHAR(4)) + '/' + RIGHT('0' + CAST(MONTH(dbo.tServiceID.InstallationDate) AS VARCHAR(2)), 2) AS InstallationPeriod,
                      dbo.tCentre.Description AS Centre, dbo.tActivity.Description AS Activity, 
                      tElement_1.Description AS Element, dbo.tSubledger.Description AS Subledger, dbo.tServiceID.PayType, dbo.tPerson.Person, 
                      dbo.tLocation.Description AS Location, dbo.tApplication.Description AS Application, dbo.tServiceID.Description AS ServiceDescription, 
                      dbo.tServiceType.Code, dbo.tGroup.Description AS [Group], dbo.tDepartment.Description AS Department, dbo.tServiceID.TerminationDate, 
                      CAST(YEAR(dbo.tServiceID.TerminationDate) AS VARCHAR(4)) + '/' + RIGHT('0' + CAST(MONTH(dbo.tServiceID.TerminationDate) AS VARCHAR(2)), 2) AS TerminationPeriod,
                      dbo.tSupplierAccount.SupplierAccount AS AccountNo, dbo.tServiceID.SACCnumber, dbo.tServiceID.JobNo, dbo.tServiceID.Active, 
                      tElement_1.Description AS RevenueElement, tElement_1.ElementCode AS RevenueElementCode, dbo.tServiceID.CurrentRent, 
                      dbo.tServiceID.CurrentCalls, dbo.tServiceID.PreviousRent, dbo.tServiceID.PreviousCalls, dbo.tCustomer.ID AS CustomerID
FROM         dbo.tServiceType LEFT OUTER JOIN
                      dbo.tElement tElement_1 ON dbo.tServiceType.RevenueElementID = tElement_1.ID LEFT OUTER JOIN
                      dbo.tSupplier LEFT OUTER JOIN
                      dbo.tSupplierAccount ON dbo.tSupplier.ID = dbo.tSupplierAccount.SupplierID ON dbo.tServiceType.SupplierID = dbo.tSupplier.ID RIGHT OUTER JOIN
                      dbo.tCentre LEFT OUTER JOIN
                      dbo.tActivity RIGHT OUTER JOIN
                      dbo.tServiceID ON dbo.tActivity.ID = dbo.tServiceID.ActivityID ON dbo.tCentre.ID = dbo.tServiceID.CustomerCentreID LEFT OUTER JOIN
                      dbo.tSubledger ON dbo.tServiceID.SubledgerID = dbo.tSubledger.ID LEFT OUTER JOIN
                      dbo.tPerson ON dbo.tServiceID.PersonID = dbo.tPerson.ID LEFT OUTER JOIN
                      dbo.tGroup LEFT OUTER JOIN
                      dbo.tCustomer ON dbo.tGroup.CustomerID = dbo.tCustomer.ID LEFT OUTER JOIN
                      dbo.tDepartment ON dbo.tGroup.ID = dbo.tDepartment.GroupID ON dbo.tCentre.DepartmentID = dbo.tDepartment.ID LEFT OUTER JOIN
                      dbo.tElement tElement_2 ON dbo.tServiceID.ElementID = tElement_2.ID ON dbo.tServiceType.ID = dbo.tServiceID.ServiceTypeID LEFT OUTER JOIN
                      dbo.tLocation ON dbo.tServiceID.LocationID = dbo.tLocation.ID LEFT OUTER JOIN
                      dbo.tStatus ON dbo.tServiceID.StatusID = dbo.tStatus.ID LEFT OUTER JOIN
                      dbo.tApplication ON dbo.tServiceID.ApplicationID = dbo.tApplication.ID
WHERE     (dbo.tServiceType.SystemID <> 53)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/* -------------------------- */
/* vwServiceListServiceRecord */
/* -------------------------- */

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.vwServiceListServiceRecord
AS
SELECT     dbo.tServiceID.ServiceID, dbo.tServiceID.Service, dbo.tStatus.Status, dbo.tServiceMnemonic.ServiceCode AS Mnemonic, 
                      dbo.tServiceID.Description AS ServiceDescription, dbo.tServiceType.Description AS ServiceType, dbo.tCustomer.ShipTo, 
                      dbo.tCustomer.FullName AS Customer, dbo.tServiceID.InstallationDate, 
                      CAST(YEAR(dbo.tServiceID.InstallationDate) AS VARCHAR(4)) + '/' + RIGHT('0' + CAST(MONTH(dbo.tServiceID.InstallationDate) AS VARCHAR(2)), 2) AS InstallationPeriod,
                      dbo.tPerson.Person, dbo.tLocation.Description AS Location, 
                      dbo.tServiceID.ServiceCode, dbo.tApplication.Description AS Application, dbo.tServiceID.SACCnumber, dbo.tServiceID.JobNo, 
                      dbo.rBearerSpeed.TextValue AS Speed, dbo.tServiceID.Cost, dbo.tServiceID.Cost100, dbo.tServiceID.Invoiced, dbo.tServiceType.Code, 
                      dbo.tGroup.Description AS [Group], dbo.tDepartment.Description AS Department, dbo.tServiceID.TerminationDate, 
                      CAST(YEAR(dbo.tServiceID.TerminationDate) AS VARCHAR(4)) + '/' + RIGHT('0' + CAST(MONTH(dbo.tServiceID.TerminationDate) AS VARCHAR(2)), 2) AS TerminationPeriod,
                      dbo.tCentre.Description AS Centre, 
                      dbo.tActivity.Description AS Activity, dbo.tElement.Description AS Element, dbo.tSubledger.Description AS Subledger, dbo.tServiceID.PayType, 
                      dbo.tServiceID.Active, dbo.tCustomer.ID AS CustomerID, dbo.tServiceID.UpEnd, dbo.tServiceID.UpMDF, dbo.tServiceID.DownEnd, 
                      dbo.tServiceID.DownMDF
FROM         dbo.tApplication RIGHT OUTER JOIN
                      dbo.tLocation RIGHT OUTER JOIN
                      dbo.tServiceID LEFT OUTER JOIN
                      dbo.rBearerSpeed ON dbo.tServiceID.SpeedID = dbo.rBearerSpeed.ID LEFT OUTER JOIN
                      dbo.tElement ON dbo.tServiceID.ElementID = dbo.tElement.ID LEFT OUTER JOIN
                      dbo.tPerson ON dbo.tServiceID.PersonID = dbo.tPerson.ID ON dbo.tLocation.ID = dbo.tServiceID.LocationID LEFT OUTER JOIN
                      dbo.tSubledger ON dbo.tServiceID.SubledgerID = dbo.tSubledger.ID LEFT OUTER JOIN
                      dbo.tStatus ON dbo.tServiceID.StatusID = dbo.tStatus.ID LEFT OUTER JOIN
                      dbo.tActivity ON dbo.tServiceID.ActivityID = dbo.tActivity.ID ON dbo.tApplication.ID = dbo.tServiceID.ApplicationID LEFT OUTER JOIN
                      dbo.tCustomer RIGHT OUTER JOIN
                      dbo.tGroup ON dbo.tCustomer.ID = dbo.tGroup.CustomerID RIGHT OUTER JOIN
                      dbo.tDepartment ON dbo.tGroup.ID = dbo.tDepartment.GroupID RIGHT OUTER JOIN
                      dbo.tCentre ON dbo.tDepartment.ID = dbo.tCentre.DepartmentID ON dbo.tServiceID.CustomerCentreID = dbo.tCentre.ID LEFT OUTER JOIN
                      dbo.tServiceMnemonic ON dbo.tServiceID.ServiceMnemonicID = dbo.tServiceMnemonic.ID LEFT OUTER JOIN
                      dbo.tSupplier RIGHT OUTER JOIN
                      dbo.tServiceType ON dbo.tSupplier.ID = dbo.tServiceType.SupplierID ON dbo.tServiceID.ServiceTypeID = dbo.tServiceType.ID
WHERE     (dbo.tServiceType.SystemID = 53)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/* --------------------------- */
/* vwServiceListExtensionTypeA */
/* --------------------------- */

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.vwServiceListExtensionTypeA
AS
SELECT     ID, RecordNo, ext, ISNULL(lname, '') AS lname, ISNULL(fname, '') AS fname, ISNULL(tec, '') AS tec, ISNULL(division, '') AS division,
     ISNULL(location, '') AS location, ISNULL(sect, '') AS sect, ISNULL(title, '') AS title, ISNULL(email, '') AS email, ISNULL(site, '') AS site,
     ISNULL(extgrp, '') AS extgrp, date_updated, hdate, updop
FROM     dbo.tExtension
WHERE     (updop = 'A')

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/* --------------------------- */
/* vwServiceListExtensionTypeD */
/* --------------------------- */

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.vwServiceListExtensionTypeD
AS
SELECT     ID, RecordNo, ext, ISNULL(lname, '') AS lname, ISNULL(fname, '') AS fname, ISNULL(tec, '') AS tec, ISNULL(division, '') AS division,
     ISNULL(location, '') AS location, ISNULL(sect, '') AS sect, ISNULL(title, '') AS title, ISNULL(email, '') AS email, ISNULL(site, '') AS site,
     ISNULL(extgrp, '') AS extgrp, date_updated, hdate, updop
FROM     dbo.tExtension
WHERE     (updop = 'D')

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/* --------------------------- */
/* vwServiceListExtensionTypeO */
/* --------------------------- */

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.vwServiceListExtensionTypeO
AS
SELECT     ID, RecordNo, ext, ISNULL(lname, '') AS lname, ISNULL(fname, '') AS fname, ISNULL(tec, '') AS tec, ISNULL(division, '') AS division,
     ISNULL(location, '') AS location, ISNULL(sect, '') AS sect, ISNULL(title, '') AS title, ISNULL(email, '') AS email, ISNULL(site, '') AS site,
     ISNULL(extgrp, '') AS extgrp, date_updated, hdate, updop
FROM     dbo.tExtension
WHERE     (updop = 'O')

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/* --------------------------------- */
/* vwServiceListExtensionTypeAOMatch */
/* --------------------------------- */

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.vwServiceListExtensionTypeAOMatch
AS
SELECT     dbo.vwServiceListExtensionTypeA.ID, dbo.vwServiceListExtensionTypeA.ext, dbo.vwServiceListExtensionTypeA.lname,
                      dbo.vwServiceListExtensionTypeA.fname, dbo.vwServiceListExtensionTypeA.tec, dbo.vwServiceListExtensionTypeA.division,
                      dbo.vwServiceListExtensionTypeA.location, dbo.vwServiceListExtensionTypeA.sect, dbo.vwServiceListExtensionTypeA.title,
                      dbo.vwServiceListExtensionTypeA.email, dbo.vwServiceListExtensionTypeA.site, dbo.vwServiceListExtensionTypeA.extgrp,
                      dbo.vwServiceListExtensionTypeA.date_updated, dbo.vwServiceListExtensionTypeA.hdate, dbo.vwServiceListExtensionTypeA.updop
FROM         dbo.vwServiceListExtensionTypeA INNER JOIN
                      dbo.vwServiceListExtensionTypeO ON dbo.vwServiceListExtensionTypeA.ext = dbo.vwServiceListExtensionTypeO.ext AND
                      dbo.vwServiceListExtensionTypeA.lname = dbo.vwServiceListExtensionTypeO.lname AND
                      dbo.vwServiceListExtensionTypeA.fname = dbo.vwServiceListExtensionTypeO.fname AND
                      dbo.vwServiceListExtensionTypeA.tec = dbo.vwServiceListExtensionTypeO.tec AND
                      dbo.vwServiceListExtensionTypeA.division = dbo.vwServiceListExtensionTypeO.division AND
                      dbo.vwServiceListExtensionTypeA.location = dbo.vwServiceListExtensionTypeO.location AND
                      dbo.vwServiceListExtensionTypeA.sect = dbo.vwServiceListExtensionTypeO.sect AND
                      dbo.vwServiceListExtensionTypeA.title = dbo.vwServiceListExtensionTypeO.title AND
                      dbo.vwServiceListExtensionTypeA.email = dbo.vwServiceListExtensionTypeO.email AND
                      dbo.vwServiceListExtensionTypeA.site = dbo.vwServiceListExtensionTypeO.site AND
                      dbo.vwServiceListExtensionTypeA.extgrp = dbo.vwServiceListExtensionTypeO.extgrp

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/* ----------------------------------- */
/* vwServiceListExtensionTypeAONoMatch */
/* ----------------------------------- */

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.vwServiceListExtensionTypeAONoMatch
AS
SELECT     ID, RecordNo, ext, lname, fname, tec, division, location, sect, title, email, site, extgrp, date_updated, hdate, updop
FROM         dbo.vwServiceListExtensionTypeA
WHERE     (ID NOT IN
                          (SELECT     ID
                            FROM          dbo.vwServiceListExtensionTypeAOMatch))

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/* ---------------------- */
/* vwServiceListExtension */
/* ---------------------- */

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.vwServiceListExtension
AS
SELECT     ext AS Extension, lname AS LastName, fname AS FirstName, tec AS Tec, division AS Division, sect AS [Section], title AS Title, email AS Email,
                      site AS Site, extgrp AS ExtensionGroup, NULL AS InstallationDate, [date_updated] AS TerminationDate, 'D' AS RecordType,
                      [date_updated] AS SortDate
FROM         dbo.vwServiceListExtensionTypeD
UNION
SELECT     ext AS Extension, lname AS LastName, fname AS FirstName, tec AS Tec, division AS Division, sect AS [Section], title AS Title, email AS Email,
                      site AS Site, extgrp AS ExtensionGroup, [date_updated] AS InstallationDate, NULL AS TerminationDate, 'O' AS RecordType,
                      [date_updated] AS SortDate
FROM         dbo.vwServiceListExtensionTypeO
UNION
SELECT     ext AS Extension, lname AS LastName, fname AS FirstName, tec AS Tec, division AS Division, sect AS [Section], title AS Title, email AS Email,
                      site AS Site, extgrp AS ExtensionGroup, [date_updated] AS InstallationDate, NULL AS TerminationDate, 'A' AS RecordType,
                      [date_updated] AS SortDate
FROM         dbo.vwServiceListExtensionTypeAONoMatch

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*              */
/* Stored Procs */
/*              */

/* ------------- */
/* spServiceList */
/* ------------- */

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE [dbo].[spServiceList]
AS

SELECT     ServiceID,
     Service,
     SupplierName,
     ServiceType,
     ShipTo,
     Customer,
     InstallationDate,
     InstallationPeriod,
     Centre,
     Activity,
     Element,
     Subledger,
     PayType,
     Person,
     Location,
     Application,
     ServiceDescription,
     Code,
     [Group],
     Department,
     AccountNo,
     SACCnumber,
     JobNo,
     RevenueElement,
     RevenueElementCode,
     CurrentRent, 
     CurrentCalls,
     PreviousRent,
     PreviousCalls
FROM     dbo.vwServiceList
WHERE
     Active = 1
ORDER BY
     ServiceID

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/* ---------------------- */
/* spServiceListExtension */
/* ---------------------- */

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE [dbo].[spServiceListExtension]
AS
SELECT	ext AS Extension,
	lname AS LastName,
	fname AS FirstName,
	tec AS Tec,
	division AS ShipTo,
	dbo.tCustomer.FullName AS Customer,
	sect AS [Section],
	title AS Title,
	email AS Email, 
	site AS PABX,
	extgrp AS ServiceID,
	[date_updated] AS InstallationDate,
	CAST(YEAR([date_updated]) AS VARCHAR(4)) + '/' + RIGHT('0' + CAST(MONTH([date_updated]) AS VARCHAR(2)), 2) AS InstallationPeriod
FROM	dbo.vwServiceListExtensionTypeO LEFT OUTER JOIN 
		dbo.tCustomer ON dbo.vwServiceListExtensionTypeO.division = dbo.tCustomer.ShipTo
ORDER BY
	ext,
	[date_updated]

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/* -------------------------- */
/* spServiceListServiceRecord */
/* -------------------------- */

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE [dbo].[spServiceListServiceRecord]
AS

SELECT	ServiceID,
	Service,
	Status,
	ServiceType,
	ShipTo, 
	Customer,
	InstallationDate,
	InstallationPeriod,
	Person,
	Location, 
	ServiceCode,
	Application,
	SACCnumber,
	Cost,
	Invoiced,
	Mnemonic, 
 	ServiceDescription,
	JobNo,
	Speed,
	Code, 
	[Group],
	Department,
	Centre, 
	Activity,
	Element,
	Subledger,
	PayType, 
	UpEnd,
	UpMDF,
	DownEnd, 
	DownMDF,
	Cost100
FROM	dbo.vwServiceListServiceRecord
WHERE
     Active = 1
ORDER BY
	ServiceID

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/* ---------------------------- */
/* spServiceListGetCustomerList */
/* ---------------------------- */

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE spServiceListGetCustomerList
@p_CustomerFilter          varchar(50)

AS

SET NOCOUNT ON

if LEN(@p_CustomerFilter) = 0
     SELECT DISTINCT
         tCustomer.ID, tCustomer.ShipTo, tCustomer.Fullname
     FROM tCustomer  (NOLOCK)
     ORDER BY tCustomer.ShipTo
else
     SELECT DISTINCT
         tCustomer.ID, tCustomer.ShipTo, tCustomer.Fullname
     FROM tCustomer  (NOLOCK)
     WHERE tCustomer.Fullname LIKE @p_CustomerFilter + '%'
     ORDER BY tCustomer.ShipTo

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/* ---------------------------- */
/* spServiceListExtensionReport */
/* ---------------------------- */

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE [dbo].[spServiceListExtensionReport]
@p_FromDate     varchar(10),
@p_ToDate     varchar(10),
@p_CustomerList     varchar(8000)
AS

DECLARE @SQL          varchar(8000)
DECLARE @Where          varchar(8000)
DECLARE @OrderBy     varchar(8000)

-- SELECT Clause
SET     @SQL = '
SELECT
	Extension,
	LastName AS [Last Name],
	FirstName AS [First Name],
	Tec,
	Division AS [Ship To],
	dbo.tCustomer.FullName AS Customer,
	[Section],
	Title,
	Email,
	Site AS PABX,
	ExtensionGroup AS [Service ID],
	InstallationDate AS [Installation Date],
	CAST(YEAR(InstallationDate) AS VARCHAR(4)) + ''/'' + RIGHT(''0'' + CAST(MONTH(InstallationDate) AS VARCHAR(2)), 2) AS [Installation Period],
	TerminationDate AS [Termination Date],
	CAST(YEAR(TerminationDate) AS VARCHAR(4)) + ''/'' + RIGHT(''0'' + CAST(MONTH(TerminationDate) AS VARCHAR(2)), 2) AS [Termination Period]
FROM	dbo.vwServiceListExtension LEFT OUTER JOIN 
		dbo.tCustomer ON dbo.vwServiceListExtension.division = dbo.tCustomer.ShipTo '

-- WHERE Clause
SET @Where = CHAR(10) + ' WHERE
     ((InstallationDate BETWEEN ''' + @p_FromDate + ''' AND ''' + @p_ToDate + ''') OR
     (TerminationDate   BETWEEN ''' + @p_FromDate + ''' AND ''' + @p_ToDate + ''')) '

if LEN(@p_CustomerList) > 0
begin
     SET @Where = @Where + CHAR(10) + 'AND dbo.tCustomer.ID IN (' + @p_CustomerList + ') '
end

-- ORDER BY Clause
SET @OrderBy = CHAR(10) + ' ORDER BY Extension, SortDate, RecordType'

EXEC (@SQL + @Where + @OrderBy)
--select @SQL + @Where + @OrderBy
--PRINT @SQL + @Where + @OrderBy

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/* ------------------- */
/* spServiceListReport */
/* ------------------- */

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE [dbo].[spServiceListReport]
@p_FromDate     varchar(10),
@p_ToDate     varchar(10),
@p_CustomerList     varchar(8000)

AS

DECLARE @SQL          varchar(8000)
DECLARE @Where          varchar(8000)
DECLARE @OrderBy     varchar(8000)

-- SELECT Clause
SET     @SQL = '
SELECT     ServiceID AS [Service ID],
     Service,
     SupplierName AS [Supplier Name],
     ServiceType AS [Service Type],
     ShipTo AS [Ship To],
     Customer,
     InstallationDate AS [Installation Date],
     InstallationPeriod AS [Installation Period],
     Centre,
     Activity,
     Element,
     Subledger,
     PayType AS [Pay Type],
     Person,
     Location,
     Application,
     ServiceDescription AS [Service Description],
     Code,
     [Group],
     Department,
     TerminationDate AS [Termination Date],
     TerminationPeriod AS [Termination Period],
     AccountNo AS [Account No],
     SACCnumber AS [SACC Number],
     JobNo AS [Job No],
     Active,
     RevenueElement AS [Revenue Element],
     RevenueElementCode AS [Revenue Element Code],
     CurrentRent AS [Current Rent], 
     CurrentCalls AS [Current Calls],
     PreviousRent AS [Previous Rent],
     PreviousCalls AS [Previous Calls]
FROM     dbo.vwServiceList'

-- WHERE Clause
SET @Where = CHAR(10) + ' WHERE
     ((InstallationDate BETWEEN ''' + @p_FromDate + ''' AND ''' + @p_ToDate + ''') OR
     (TerminationDate   BETWEEN ''' + @p_FromDate + ''' AND ''' + @p_ToDate + ''')) '

if LEN(@p_CustomerList) > 0
begin
     SET @Where = @Where + CHAR(10) + 'AND CustomerID IN (' + @p_CustomerList + ') '
end

-- ORDER BY Clause
SET @OrderBy = CHAR(10) + 'ORDER BY ServiceID'

EXEC (@SQL + @Where + @OrderBy)
--select @SQL + @Where + @OrderBy
--PRINT @SQL + @Where + @OrderBy

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/* -------------------------------- */
/* spServiceListServiceRecordReport */
/* -------------------------------- */

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE [dbo].[spServiceListServiceRecordReport]
@p_FromDate     varchar(10),
@p_ToDate     varchar(10),
@p_CustomerList     varchar(8000)

AS

DECLARE @SQL          varchar(8000)
DECLARE @Where          varchar(8000)
DECLARE @OrderBy     varchar(8000)

-- SELECT Clause
SET     @SQL = '
SELECT
    ServiceID AS [Service ID],
    Service,
    Status,
    ServiceType AS [Service Type],
    ShipTo AS [Ship To],
    Customer,
    InstallationDate AS [Installation Date],
    InstallationPeriod AS [Installation Period],
    Person,
    Location,
    ServiceCode AS [Service Code],
    Application,
    SACCnumber AS [SACC Number],
    Cost,
    Invoiced,
    Cost100 AS [Cost 100],
    Mnemonic,
    ServiceDescription AS [Service Description],
    JobNo AS [Job No],
    Speed,
    Code,
    [Group],
    Department,
    TerminationDate AS [Termination Date],
    TerminationPeriod AS [Termination Period],
    Centre,
    Activity,
    Element,
    Subledger,
    PayType AS [Pay Type],
    Active,
    UpEnd,
    UpMDF,
    DownEnd,
    DownMDF
FROM     dbo.vwServiceListServiceRecord'

-- WHERE Clause
SET @Where = CHAR(10) + ' WHERE
     ((InstallationDate BETWEEN ''' + @p_FromDate + ''' AND ''' + @p_ToDate + ''') OR
     (TerminationDate   BETWEEN ''' + @p_FromDate + ''' AND ''' + @p_ToDate + ''')) '

if LEN(@p_CustomerList) > 0
begin
     SET @Where = @Where + CHAR(10) + 'AND CustomerID IN (' + @p_CustomerList + ') '
end

-- ORDER BY Clause
SET @OrderBy = CHAR(10) + 'ORDER BY ServiceID'

EXEC (@SQL + @Where + @OrderBy)
--select @SQL + @Where + @OrderBy
--PRINT @SQL + @Where + @OrderBy

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO