-----------------------------------------------------------------------------
-- ADVANTAGE 2004.5.2.2
-- DB OBJECT CHANGES
-- 25/07/2011
------------------------------------------------------------------------------



-------------------------------------------------------------------------------
-- INSERT DEFAULT VALUES for NEWLY CREATED COLUMN in 0.sql !!! Cannot do it there !!!
-------------------------------------------------------------------------------

--1. Modify AW_User_Companies table as follows
    IF EXISTS
        (
            SELECT * FROM SysObjects O
                INNER JOIN SysColumns C
                    ON O.ID = C.ID
            WHERE ObjectProperty(O.ID,'IsUserTable') = 1 AND
                  O.Name='AC_Customers' AND
                  C.Name='AW_YesNo_Attach_Tran_ID'
        ) 
        BEGIN
            UPDATE AC_Customers
            SET AW_YesNo_Attach_Tran_ID = 0
        END

-------------------------------------------------------



-------------------------------------------------------------------------------
-- NEW TABLES
-------------------------------------------------------------------------------

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

CREATE TABLE [dbo].[Sys_AW_Terms_Conditions] (
	[Sys_AW_Terms_Conditions_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Terms] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[User_ID] [int] NULL ,
	[Slu_AW_Active_ID] [tinyint] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Sys_AW_Terms_Conditions] WITH NOCHECK ADD 
	CONSTRAINT [PK_Sys_AW_Terms_Conditions] PRIMARY KEY  CLUSTERED 
	(
		[Sys_AW_Terms_Conditions_ID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[df_Active]', N'[Sys_AW_Terms_Conditions].[Slu_AW_Active_ID]'
GO

EXEC sp_bindefault N'[dbo].[df_User_ID]', N'[Sys_AW_Terms_Conditions].[User_ID]'
GO

setuser
GO

-----------------------------------------------

-----------------------------------------------
-----------------------------------------------

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

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

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

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

CREATE TABLE [dbo].[Sys_AF_Modules] (
	[Sys_AF_Modules_ID] [tinyint] IDENTITY (1, 1) NOT NULL ,
	[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Module_Seq] [smallint] NOT NULL ,
	[Start_Date] [datetime] NULL ,
	[Date_Captured] [datetime] NULL ,
	[User_ID] [int] NULL ,
	[Touched] [timestamp] NULL ,
	[Slu_AW_Active_ID] [tinyint] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Sys_AF_Modules] WITH NOCHECK ADD 
	CONSTRAINT [PK_Sys_AF_Modules] PRIMARY KEY  CLUSTERED 
	(
		[Sys_AF_Modules_ID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[df_Date_Captured]', N'[Sys_AF_Modules].[Date_Captured]'
GO

EXEC sp_bindefault N'[dbo].[df_Active]', N'[Sys_AF_Modules].[Slu_AW_Active_ID]'
GO

EXEC sp_bindefault N'[dbo].[df_User_ID]', N'[Sys_AF_Modules].[User_ID]'
GO

setuser
GO

-----------------------------------------------

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

CREATE TABLE [dbo].[Sys_AF_Applications] (
	[Sys_AF_Applications_ID] [smallint] IDENTITY (1, 1) NOT NULL ,
	[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Sys_AF_Modules_ID] [tinyint] NULL ,
	[Application_Seq] [smallint] NOT NULL ,
	[Form_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Date_Captured] [datetime] NULL ,
	[User_ID] [int] NULL ,
	[Touched] [timestamp] NULL ,
	[Slu_AW_Active_ID] [int] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Sys_AF_Applications] WITH NOCHECK ADD 
	CONSTRAINT [PK_Sys_AF_Applications] PRIMARY KEY  CLUSTERED 
	(
		[Sys_AF_Applications_ID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

 CREATE  INDEX [IX_Sys_AF_Applications_Key] ON [dbo].[Sys_AF_Applications]([Sys_AF_Modules_ID], [Application_Seq], [Description], [Form_Name]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[df_Date_Captured]', N'[Sys_AF_Applications].[Date_Captured]'
GO

EXEC sp_bindefault N'[dbo].[df_Active]', N'[Sys_AF_Applications].[Slu_AW_Active_ID]'
GO

EXEC sp_bindefault N'[dbo].[df_User_ID]', N'[Sys_AF_Applications].[User_ID]'
GO

setuser
GO

ALTER TABLE [dbo].[Sys_AF_Applications] ADD 
	CONSTRAINT [FK_Sys_AF_Applications_Sys_AF_Modules] FOREIGN KEY 
	(
		[Sys_AF_Modules_ID]
	) REFERENCES [dbo].[Sys_AF_Modules] (
		[Sys_AF_Modules_ID]
	)
GO

-------------------------------------------------------
-- POPULATE DATA
-------------------------------------------------------

-------------------------------------------
INSERT Sys_AF_Modules (Description, Module_Seq) VALUES ('Casing Tracking', 1)
GO
-------------------------------------------
-------------------------------------------
INSERT Sys_AF_Applications (Description, Sys_AF_Modules_ID, Application_Seq, Form_Name) VALUES ('Job Tickets', 1, 1, 'frmJobTickets')
INSERT Sys_AF_Applications (Description, Sys_AF_Modules_ID, Application_Seq, Form_Name) VALUES ('Factory Waybills', 1, 2, 'frmFactoryWaybills')
INSERT Sys_AF_Applications (Description, Sys_AF_Modules_ID, Application_Seq, Form_Name) VALUES ('Factory Receipts', 1, 3, 'frmFactoryReceipts')
INSERT Sys_AF_Applications (Description, Sys_AF_Modules_ID, Application_Seq, Form_Name) VALUES ('Customer Billing', 1, 4, 'frmCustomerBilling')
INSERT Sys_AF_Applications (Description, Sys_AF_Modules_ID, Application_Seq, Form_Name) VALUES ('Casing for Credit', 1, 5, 'frmCasingCredit')
INSERT Sys_AF_Applications (Description, Sys_AF_Modules_ID, Application_Seq, Form_Name) VALUES ('Factory Claim Refunds', 1, 6, 'frmFactoryRefunds')
INSERT Sys_AF_Applications (Description, Sys_AF_Modules_ID, Application_Seq, Form_Name) VALUES ('Customer Claim Refunds', 1, 7, 'frmCustomerRefunds')
INSERT Sys_AF_Applications (Description, Sys_AF_Modules_ID, Application_Seq, Form_Name) VALUES ('Reports Library', 1, 99, 'frmReportsLibrary')
-------------------------------------------

-------------------------------------------------------------------------------
-- TABLE MODS
-------------------------------------------------------------------------------

--> INSERT AS_Transactions.AS_Transactions_ID into NEW AC_Statement_Details.AS_Transactions_ID
UPDATE d
SET d.AS_Transactions_ID = t.AS_Transactions_ID
FROM AC_Statement_Details d
    INNER JOIN AC_Statements h
        ON d.AC_Statements_ID = h.AC_Statements_ID
    INNER JOIN AS_Transactions t
        ON h.AC_Customers_ID = t.AC_Customers_ID
WHERE d.AC_Statements_ID = h.AC_Statements_ID AND
      h.AC_Customers_ID = t.AC_Customers_ID AND
      d.Transaction_Date = t.Transaction_Date AND
      d.Sys_AW_Transaction_Types_ID = t.Sys_AW_Transaction_Types_ID AND
      d.Transaction_Number = t.Transaction_Number AND
      d.Amount = t.Total_Amount

-------------------------------------------------------------------------------
-- NEW REPORTS
-------------------------------------------------------------------------------

    IF NOT EXISTS
        (
            SELECT Sys_AF_Reports_ID
            FROM Sys_AF_Reports
            WHERE Report_No = '006' AND
                  Method_Name = 'Show_CFCInventoryGL'
        ) 
        BEGIN
            INSERT Sys_AF_Reports
                (
                    Sys_AF_Modules_ID,
                    Type_ID,
                    Location,
                    Report_No,
                    Report_Name,
                    Description,
                    Object_DLL,
                    Class_Name,
                    Method_Name
                )
            VALUES
                (
                    1,
                    0,
                    'Factory Reports',
                    '006',
                    'Casing Credit GL Listing',
                    'Casing Credit GL Listing',
                    'SpSFactoryReports',
                    'CInterface',
                    'Show_CFCInventoryGL'
                )
        END

-------------------------------------------------------------------------------
-- RETIRED REPORTS
-------------------------------------------------------------------------------


-------------------------------------------------------------------------------
-- DELETE REDUNDANT TABLES
-------------------------------------------------------------------------------


-------------------------------------------------------------------------------
--> DATA MODS
-------------------------------------------------------------------------------

----------------------------------------------

    IF EXISTS
        (
            SELECT Sys_AF_Reports_ID
            FROM Sys_AF_Reports
            WHERE Report_No = '004' AND
                  Method_Name = 'Show_CasingVsStock'
        ) 
        BEGIN
            UPDATE Sys_AF_Reports
            SET Report_Name = 'Casing for Credit Listing',
                Description = 'Casing for Credit Listing'
            WHERE Report_No = '004' AND
                  Method_Name = 'Show_CasingVsStock'
        END

-------------------------------------------------------------------------------
--> NEW INDEXES
-------------------------------------------------------------------------------



----------------------------------------------





----------------------------------------------



