CREATE PROCEDURE LAXSalesCompleted
@icmStartDate INT,
@icmEndDate INT,
@startDate SMALLDATETIME,
@endDate SMALLDATETIME
AS
---------------------------------------------------------------------
-- 00. EMPTY TEMP TABLES
---------------------------------------------------------------------
TRUNCATE TABLE TEMP01
TRUNCATE TABLE TEMP02
TRUNCATE TABLE TEMP03
TRUNCATE TABLE TEMP04
-- 01. LINK TABLES, INSERT INTO TEMP 01
---------------------------------------------------------------------
PRINT '01. LINK TABLES, INSERT INTO TEMP 01'
INSERT INTO icoms.dbo.TEMP01 (acct, eDate, eTime, uDate, status, code, [from], [to], slm, woo, campaign)
SELECT M.WOCU#, M.WOEDT, M.WOETM, M.WOUDT, M.WODSA, D.WDSRC, D.WDQ01, D.WDQ02, D.WDSM#, D.WDSYS, D.WDCAM#
FROM icoms.dbo.LAXWOMHIPF M
INNER JOIN icoms.dbo.LAXWODHIPF D
ON M.WONUM = D.WDWO#
AND M.WOUDT >= @icmStartDate
AND M.WOUDT <= @icmEndDate
WHERE D.WDSYS = 1
OR D.WDSRC IN ('DIGCNV','GUIDE','DVRSVC1','DVRSVC2','3MDWN','384KDWN')
IF @@ERROR <> 0 RETURN(1)
-- 02. CHANGE CUSTOMER STATUS, INSERT INTO TEMP 02
---------------------------------------------------------------------
PRINT '02. FIND TRUE CUSTOMER STATUS, INSERT INTO TEMP 02'
INSERT INTO icoms.dbo.TEMP02 (acct, eDate, eTime, uDate, status, code, [from], [to], slm, woo, campaign)
SELECT T1.acct, T1.eDate, T1.eTime, T1.uDate,
(SELECT TOP 1 T2.status
FROM icoms.dbo.TEMP01 T2
WHERE T2.acct = T1.acct
AND T2.eDate = T1.eDate
AND T2.slm = T1.slm
ORDER BY T2.eTime),
T1.code, T1.[from], T1.[to], T1.slm, T1.woo, T1.campaign
FROM icoms.dbo.TEMP01 T1
IF @@ERROR <> 0 RETURN(1)
-- 03. FIND PRODUCT ID, INSERT INTO TEMP 03
---------------------------------------------------------------------
PRINT '03. FIND PRODUCT ID, INSERT INTO TEMP 03'
INSERT INTO icoms.dbo.TEMP03 (acct, eDate, eTime, uDate, fkProductUID, [from], [to], slm, woo, campaign)
SELECT T1.acct, T1.eDate, T1.eTime, T1.uDate, S.fkProductUID, T1.[from], T1.[to], T1.slm, T1.woo, T1.campaign
FROM icoms.dbo.TEMP02 T1
INNER JOIN icoms.dbo.servCode S
ON T1.code = S.code
AND T1.status = S.status
AND S.fkProductUID NOT IN (11,12)
IF @@ERROR <> 0 RETURN(1)
-- INSERT DIGACCS INSTALL
INSERT INTO icoms.dbo.TEMP03 (acct, eDate, eTime, uDate, fkProductUID, [from], [to], slm, woo, campaign)
SELECT T1.acct, T1.eDate, T1.eTime, T1.uDate, 11, T1.[from], T1.[to], T1.slm, T1.woo, T1.campaign
FROM icoms.dbo.TEMP02 T1
WHERE T1.code = 'DIGCNV'
AND T1.woo = 1
AND T1.status IN ('N','F')
-- INSERT DIGACCS NON-INSTALL
INSERT INTO icoms.dbo.TEMP03 (acct, eDate, eTime, uDate, fkProductUID, [from], [to], slm, woo, campaign)
SELECT T1.acct, T1.eDate, T1.eTime, T1.uDate, 12, T1.[from], T1.[to], T1.slm, T1.woo, T1.campaign
FROM icoms.dbo.TEMP02 T1
WHERE T1.code = 'DIGCNV'
AND T1.woo = 1
AND T1.status = 'A'
-- 04. GROUP RECORDS, INSERT INTO TEMP 04
---------------------------------------------------------------------
PRINT '04. GROUP RECORDS, INSERT INTO TEMP 04'
INSERT INTO icoms.dbo.TEMP04 (acct, eDate, eTime, uDate, fkProductUID, slm, campaign, netGain)
SELECT T1.acct, T1.eDate,
(SELECT TOP 1 T2.eTime
FROM icoms.dbo.TEMP03 T2
WHERE T2.acct = T1.acct
AND T2.eDate = T1.eDate
AND T2.slm = T1.slm
ORDER BY T2.eTime DESC),
(SELECT TOP 1 T3.uDate
FROM icoms.dbo.TEMP03 T3
WHERE T3.acct = T1.acct
AND T3.eDate = T1.eDate
AND T3.slm = T1.slm
ORDER BY T3.uDate DESC),
T1.fkProductUID, T1.slm, 
(SELECT TOP 1 T5.campaign
FROM icoms.dbo.TEMP03 T5
WHERE T5.acct = T1.acct
AND T5.eDate = T1.eDate
AND T5.slm = T1.slm
AND T5.fkProductUID = T1.fkProductUID
ORDER BY T5.eTime DESC),
SUM(T1.[to]-T1.[from])
FROM icoms.dbo.TEMP03 T1
GROUP BY T1.acct, T1.eDate, T1.fkProductUID, T1.slm, T1.woo
IF @@ERROR <> 0 RETURN(1)
-- 05. DELETE HSD DOWNGRADES
---------------------------------------------------------------------
PRINT '05. DELETE HSD DOWNGRADES'
-- 05A. TO BRONZE FROM SILVER, GOLD, 384KB, 2MB OR 3MB
---------------------------------------------------------------------
PRINT ' '
PRINT '05A. TO BRONZE FROM SILVER, GOLD, 384KB OR 2MB'
DELETE icoms.dbo.TEMP04
FROM icoms.dbo.TEMP04 T1
-- FIND ACCOUNTS WITH POSITIVE GAIN OF BRONZE
-- BECUASE IT DOES NOT HAVE TO BE THE SAME REP THAT TOOK THE CODE OFF,
-- MAKE SURE TO INCLUDE CONNECT AND UPGRADE HERE (19, 23)
-- REP #1 COULD TAKE OFF GOLD, OFFICE ONLY WO IS COMPLETED
-- REP #2 ADDS BRONZE SAME DAY, BUT NEW CUSTOMER STATUS MAY HAVE CHANGED FROM "A" TO "F"
WHERE T1.fkProductUID IN (19,23)
AND T1.netGain > 0
AND T1.acct IN (
SELECT T2.acct
FROM icoms.dbo.TEMP04 T2
WHERE T2.acct = T1.acct
AND T2.eDate = T1.eDate
AND T2.fkProductUID IN (20,21,69,70,24,25,71,72,140,141)
AND T2.netGain < 0)
IF @@ERROR <> 0 RETURN(1)
-- 05B. TO SILVER FROM GOLD, 384KB, 2MB OR 3MB
---------------------------------------------------------------------
PRINT '05B. TO SILVER FROM GOLD, 384KB OR 2MB'
DELETE icoms.dbo.TEMP04
FROM icoms.dbo.TEMP04 T1
WHERE T1.fkProductUID IN (20,24)
AND T1.netGain > 0
AND T1.acct IN (
SELECT T2.acct
FROM icoms.dbo.TEMP04 T2
WHERE T2.acct = T1.acct
AND T2.eDate = T1.eDate
AND T2.fkProductUID IN (21,69,70,25,71,72,140,141)
AND T2.netGain < 0)
IF @@ERROR <> 0 RETURN(1)
-- 05C. TO 384Kb FROM 2MB, 3Mb, SILVER OR GOLD
---------------------------------------------------------------------
PRINT '05C. TO 384KB FROM 2MB, SILVER OR GOLD'
DELETE icoms.dbo.TEMP04
FROM icoms.dbo.TEMP04 T1
WHERE T1.fkProductUID IN (69,71)
AND T1.netGain > 0
AND T1.acct IN (
SELECT T2.acct
FROM icoms.dbo.TEMP04 T2
WHERE T2.acct = T1.acct
AND T2.eDate = T1.eDate
AND T2.fkProductUID IN (20,21,70,24,25,72,140,141)
AND T2.netGain < 0)
IF @@ERROR <> 0 RETURN(1)
-- 05D. TO 2MB FROM 3MB
---------------------------------------------------------------------
PRINT '05D. TO 2MB FROM 3MB'
DELETE icoms.dbo.TEMP04
FROM icoms.dbo.TEMP04 T1
WHERE T1.fkProductUID IN (70,72)
AND T1.netGain > 0
AND T1.acct IN (
SELECT T2.acct
FROM icoms.dbo.TEMP04 T2
WHERE T2.acct = T1.acct
AND T2.eDate = T1.eDate
AND T2.fkProductUID IN (140,141)
AND T2.netGain < 0)
IF @@ERROR <> 0 RETURN(1)
-- 05E. TO 3MB FROM 2MB
---------------------------------------------------------------------
PRINT '05E. TO 3MB FROM 2MB'
DELETE icoms.dbo.TEMP04
FROM icoms.dbo.TEMP04 T1
WHERE T1.fkProductUID IN (140,141)
AND T1.netGain > 0
AND T1.acct IN (
SELECT T2.acct
FROM icoms.dbo.TEMP04 T2
WHERE T2.acct = T1.acct
AND T2.eDate = T1.eDate
AND T2.fkProductUID IN (70,72)
AND T2.netGain < 0)
IF @@ERROR <> 0 RETURN(1)
-- 06. INSERT INTO COMPLETE DETAIL
---------------------------------------------------------------------
PRINT '06. INSERT INTO COMPLETE TABLE'
INSERT INTO icoms.dbo.complete (site, acct, eDate, eTime, uDate, slm, fkProductUID, campaign)
SELECT 200, acct, icoms.dbo.icmDateConvert(eDate), eTime, icoms.dbo.icmDateConvert(uDate), slm, fkProductUID, campaign
FROM icoms.dbo.TEMP04
WHERE netGain > 0
IF @@ERROR <> 0 RETURN(1)
-- 07. INSERT NEW INSTALLS INTO COMPLETE TABLE
---------------------------------------------------------------------
PRINT '07. INSERT NEW INSTALLS INTO COMPLETE TABLE'
INSERT INTO icoms.dbo.complete (site, acct, eDate, eTime, uDate, slm, fkProductUID, campaign)
SELECT DISTINCT 200, acct, eDate, eTime, uDate, slm, 85, 0
FROM icoms.dbo.complete
WHERE site = 200
AND uDate >= @startDate
AND uDate <= @endDate
AND fkProductUID IN (1,3,19,20,21,69,70,140)
IF @@ERROR <> 0 RETURN(1)
ELSE RETURN(0)
GO