回家看看...

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