USE [MichelinTB]
GO
/****** Object: StoredProcedure [dbo].[ps_process_retread_serial_number] Script Date: 12/24/2010 16:19:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ps_process_retread_serial_number]
AS
BEGIN
DECLARE @bonusPointSilver INT
DECLARE @bonusPointGolden INT
SELECT @bonusPointSilver = ISNULL(parameter_value_int,1500) FROM dbo.parameter_value WHERE parameter_value_id = 51
SELECT @bonusPointGolden = ISNULL(parameter_value_int,2500) FROM dbo.parameter_value WHERE parameter_value_id = 52
UPDATE imp_retread_tyre_serial_number
SET process_status = 3,
process_time = GETDATE(),
error_code = 1,
remark = 'Serial number does not exist!'
WHERE 1=1
AND ISNULL(process_status, 0)= 0
AND NOT EXISTS (SELECT 1 FROM dbo.transaction_claim_details tcd
WHERE tcd.serial_no = imp_retread_tyre_serial_number.serial_number)
IF OBJECT_ID('tempdb..#retreadTyres') IS NOT NULL
DROP TABLE #retreadTyres
SELECT
irtsn.imp_retread_tyre_serial_number_id,
irtsn.serial_number,
acc.account_id,
ISNULL(acc.account_class_id, 1) account_class_id,
acc.account_class_start_date,
irtsn.retread_date,
CASE WHEN ISNULL(acc.account_class_id, 1) = 1 THEN 2
WHEN ISNULL(acc.account_class_id, 1) IN (2,3) AND CONVERT(VARCHAR(10), acc.account_class_start_date, 120) > CONVERT(VARCHAR(10),irtsn.retread_date, 120) THEN 3
ELSE 1 END flag,
CASE WHEN ISNULL(acc.account_class_id, 1) = 2 THEN @bonusPointSilver
WHEN ISNULL(acc.account_class_id, 1) = 3 THEN @bonusPointGolden
ELSE 0 END points
INTO #retreadTyres
FROM imp_retread_tyre_serial_number irtsn
JOIN dbo.transaction_claim_details tcd ON irtsn.serial_number = tcd.serial_no
JOIN dbo.transaction_claim tc ON tcd.transaction_claim_id = tc.transaction_claim_id
JOIN account acc ON acc.account_id = tc.purchase_account_id
WHERE 1=1
AND tc.processed_status IN (1,2)
AND tcd.check_results IN (1,4)
AND irtsn.active_flag = 1
AND ISNULL(irtsn.process_status, 0) IN (0, 3)
UPDATE rt
SET rt.flag = 4
FROM #retreadTyres rt
WHERE 1=1
AND rt.flag = 1
AND EXISTS (SELECT 1 FROM operation op
WHERE 1=1
AND op.points_type_id = 26
AND op.account_id = rt.account_id
AND op.remark = rt.serial_number
)
UPDATE imp_retread_tyre_serial_number
SET process_status = 2,
process_time = GETDATE(),
error_code = 2,
remark = 'This account is not a silver or golden account!'
FROM #retreadTyres rt
JOIN imp_retread_tyre_serial_number irtsn ON rt.imp_retread_tyre_serial_number_id = irtsn.imp_retread_tyre_serial_number_id
WHERE 1=1
AND rt.flag = 2
UPDATE imp_retread_tyre_serial_number
SET process_status = 2,
process_time = GETDATE(),
error_code = 3,
remark = 'Class start date is later than retread date!'
FROM #retreadTyres rt
JOIN imp_retread_tyre_serial_number irtsn ON rt.imp_retread_tyre_serial_number_id = irtsn.imp_retread_tyre_serial_number_id
WHERE 1=1
AND rt.flag = 3
UPDATE imp_retread_tyre_serial_number
SET process_status = 2,
process_time = GETDATE(),
error_code = 4,
remark = 'This serial number retread points has been added!'
FROM #retreadTyres rt
JOIN imp_retread_tyre_serial_number irtsn ON rt.imp_retread_tyre_serial_number_id = irtsn.imp_retread_tyre_serial_number_id
WHERE 1=1
AND rt.flag = 4
UPDATE imp_retread_tyre_serial_number
SET process_status = 1,
process_time = GETDATE(),
error_code = 0,
remark = 'OK'
FROM #retreadTyres rt
JOIN imp_retread_tyre_serial_number irtsn ON rt.imp_retread_tyre_serial_number_id = irtsn.imp_retread_tyre_serial_number_id
WHERE 1=1
AND rt.flag = 1
UPDATE imp_retread_tyre_serial_number
SET process_status = 2,
process_time = GETDATE(),
error_code = 5,
remark = 'Serial no status error'
WHERE 1=1
AND ISNULL(process_status, 0) = 0
AND NOT EXISTS(SELECT * FROM #retreadTyres rt WHERE rt.imp_retread_tyre_serial_number_id = imp_retread_tyre_serial_number.imp_retread_tyre_serial_number_id)
INSERT INTO dbo.operation (
operation_status,create_by,setting_by,
setting_time,transaction_time,
points,
create_time,transaction_date,
expire_date,external_transaction_id,
account_id,
points_type_id,
remark
)
SELECT
'A', 1, 1,
GETDATE(), rt.retread_date, rt.points,
GETDATE(), rt.retread_date,
dbo.f_get_expire_date_for_member(convert(varchar(30),rt.retread_date,120)),
rt.imp_retread_tyre_serial_number_id,
rt.account_id, 26, rt.serial_number
FROM #retreadTyres rt
WHERE 1=1
AND rt.flag = 1
UPDATE acc
SET acc.points_available_balance = isnull(acc.points_available_balance, 0) + ISNULL(t.total_points, 0),
acc.points_earned = isnull(acc.points_earned,0) + ISNULL(t.total_points, 0),
acc.setting_by = 1,
acc.setting_time = getdate()
FROM (
SELECT account_id, SUM(points) total_points FROM #retreadTyres rt
WHERE 1=1
AND flag = 1
GROUP BY account_id
) t
JOIN account acc ON acc.account_id = t.account_id
END
-- drop PROCEDURE ps_process_retread_account_segmentation
ALTER PROCEDURE [dbo].[ps_process_retread_account_segmentation]
-- Add the parameters for the stored procedure here
AS
BEGIN
IF OBJECT_id('tempdb..#account_segmentation') IS NOT NULL
DROP TABLE #account_segmentation
SELECT
imp_account_segmentation_id,
account_code,
segmentation_value
INTO #account_segmentation
FROM imp_account_segmentation ias
WHERE 1=1
AND (ias.process_status IS NULL OR ias.process_status = 0)
--== Account NOT Found
UPDATE ias
SET ias.process_status = 2,
ias.process_time = GETDATE(),
remark = 'Account NOT Found'
FROM #account_segmentation tas
JOIN imp_account_segmentation ias ON tas.imp_account_segmentation_id = ias.imp_account_segmentation_id
WHERE 1=1
AND NOT EXISTS(SELECT 1 FROM account acc WHERE acc.account_code = ias.account_code)
--== Update imp_account_segmentation
UPDATE ias
SET ias.process_status = 1,
ias.process_time = GETDATE()
FROM #account_segmentation tas
JOIN imp_account_segmentation ias ON tas.imp_account_segmentation_id = ias.imp_account_segmentation_id
JOIN account acc ON acc.account_code = ias.account_code
WHERE 1=1
UPDATE acc
SET acc.segmentation_setting_by = 1,
acc.segmentation_setting_time = GETDATE(),
acc.segmentation=ias.segmentation_value
FROM #account_segmentation tas
JOIN imp_account_segmentation ias ON tas.imp_account_segmentation_id = ias.imp_account_segmentation_id
JOIN account acc ON acc.account_code = ias.account_code
WHERE 1=1
AND ias.process_status = 1
IF OBJECT_id('tempdb..#account_segmentation') IS NOT NULL
DROP TABLE #account_segmentation
END