![]()
USE [POND]
GO
/****** Object: StoredProcedure [dbo].[OrderChargeList] Script Date: 04/16/2014 13:32:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[OrderChargeList]
AS
DECLARE @dateDiffValue int,
@cnt int
BEGIN
SET NOCOUNT ON;
SELECT @dateDiffValue= DATEDIFF(WEEK,isnull((SELECT TOP 1 CONVERT(VARCHAR(10),EnteredDate,121) FROM dbo.LGTobeOrder_CCBatch),'2000-01-01'),GETDATE())
--SELECT @cnt= COUNT(*) FROM dbo.LGTobeOrder_CCBatch
--SELECT * FROM LGOrder
--where customerid is not null and orderid is not null
--select * from dbo.LGTobeOrder_CCBatch
--delete FROM LGOrder where customerid not in(17,19,1)
--IF(@cnt<0)
--BEGIN
-- INSERT INTO dbo.LGTobeOrder_CCBatch(CustomerID,orderID,FirstName,LastName,recordCount,perRecordCost,ccAmount,EMail)
-- SELECT a.customerid,a.orderid,b.firstName,b.lastName, dbo.[GetCustomerDataCount](a.orderid),0.65,6.5,b.EmailAddress FROM LGOrder a inner join LGCustomerBilling b
-- ON a.customerid =b.customerid
-- SELECT * FROM dbo.LGTobeOrder_CCBatch WHERE ccProcStatus != 'DONE'
-- RETURN
--END
--IF(@dateDiffValue<7 AND @dateDiffValue>=0)
IF(@dateDiffValue>7)
BEGIN
/*print 'a'*/
--SELECT * FROM dbo.LGTobeOrder_CCBatch WHERE ccProcStatus != 'DONE'
truncate table dbo.LGTobeOrder_CCBatch
INSERT INTO dbo.LGTobeOrder_CCBatch(CustomerID,orderID,FirstName,LastName,recordCount,perRecordCost,ccAmount,EMail)
SELECT a.customerid,a.orderid,b.firstName,b.lastName, dbo.[GetCustomerDataCount](a.orderid) as 'recordCount',0.65,dbo.[GetCustomerDataCount](a.orderid)*0.65,b.EmailAddress FROM LGOrder a inner join LGCustomerBilling b
ON a.customerid =b.customerid
END
SELECT * FROM dbo.LGTobeOrder_CCBatch WHERE isnull(ccProcStatus,'') != 'DONE'
--select top 10 * from dbo.LGOrder
--select top 10 * from dbo.LGOrderZips
--SELECT top 10 [ZipFrom],[ZipTo],[Radius] FROM [POND].[dbo].[zipcode_radius_around]
--where zipfrom =12601 and radius<=10
--1 准备数据
SET NOCOUNT OFF
END
View Code
![]()
USE [POND]
GO
/****** Object: StoredProcedure [dbo].[PROC_LGGetCustomerDataForRequest] Script Date: 04/16/2014 11:28:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[PROC_LGGetCustomerDataForRequest]
/*Variable*/
@OrderId varchar(10)
AS
declare @DataSource varchar(10),
@PropertyType varchar(10),/*For dbo.PostMover specially*/
@PriceRange int, /*For dbo.premover specially*/
@Radius varchar(10)
select @PriceRange=PriceRange,@PropertyType=PropertyType,@Radius=radius from dbo.LGOrder where orderid=@OrderId
/*
Summary:根据提供的OrderId,DataSource,PropertyType,Zips,Radius来获取符合指定条件的客户数据。
*/
BEGIN
IF @DataSource IS NOT NULL AND @DataSource = 'premover'
BEGIN
SELECT * INTO dbo.LGPremoverDataCampaignHistory_Backup FROM dbo.LGPremoverDataCampaignHistory
truncate table dbo.LGPremoverDataCampaignHistory
INSERT INTO dbo.LGPremoverDataCampaignHistory
( OrderId ,idkey2,zip ,state,city,address,idkey,addtype,plus4,scrapedt,dpb,price ,
buy_flag , date,AddDate)
SELECT @OrderId AS OrderId,[idkey2],[zip],[state]
,[city],[address],[idkey],[addtype],[plus4],[scrapedt]
,[dpb],[price],[buy_flag],[date],GETDATE() AS AddDate
from dbo.premover
where zip in((SELECT b.zipto
from dbo.LGOrderZips a left JOIN dbo.zipcode_radius_around b
ON a.codes=b.zipfrom
where orderid=@OrderId AND b.Radius<= @Radius
AND b.ZipFrom is NOT NULL AND b.zipto is NOT NULL
AND b.ZipTo NOT IN(SELECT codes FROM dbo.LGOrderZips where orderid=@OrderId )
GROUP BY ZipTo)
UNION (SELECT codes FROM dbo.LGOrderZips where orderid=@OrderId))
AND price <=@PriceRange
END
ELSE IF @DataSource IS NOT NULL AND @DataSource = 'postmover'
BEGIN
SELECT * INTO dbo.LGPostMoveWeeklyDataCampaignHistory_Backup FROM [dbo].[LGPostMoveWeeklyDataCampaignHistory]
truncate table dbo.LGPostMoveWeeklyDataCampaignHistory
INSERT INTO [POND].[dbo].[LGPostMoveWeeklyDataCampaignHistory]
([OrderId],[fname],[lname],[zip],[plus4],[address],[city],[state],[pub_dt],[seg_nbr]
,[fullname],[dpbc],[addtype],[dwell_typ],[AddDate])
SELECT @OrderId AS OrderId,[fname],[lname],[zip],[plus4],[address],[city],[state],[first_pub_dt],[seg_nbr]
,[fullname],[dpbc],[addtype],[dwell_typ],GETDATE() AS AddDate
from postmover
where zip in((SELECT b.zipto
from dbo.LGOrderZips a left JOIN dbo.zipcode_radius_around b
ON a.codes=b.zipfrom
where orderid=@OrderId AND b.Radius<= @Radius
AND b.ZipFrom is NOT NULL AND b.zipto is NOT NULL
AND b.ZipTo NOT IN(SELECT codes FROM dbo.LGOrderZips where orderid=@OrderId )
GROUP BY ZipTo)
UNION (SELECT codes FROM dbo.LGOrderZips where orderid=@OrderId))
AND dwell_typ=@PropertyType
END
ELSE IF @DataSource IS NOT NULL AND @DataSource = 'both'
BEGIN
SELECT * INTO dbo.LGPremoverDataCampaignHistory_Backup FROM dbo.LGPremoverDataCampaignHistory
truncate table dbo.LGPremoverDataCampaignHistory
INSERT INTO dbo.LGPremoverDataCampaignHistory
( OrderId ,idkey2,zip ,state,city,address,idkey,addtype,plus4,scrapedt,dpb,price ,
buy_flag , date,AddDate)
SELECT @OrderId AS OrderId,[idkey2],[zip],[state]
,[city],[address],[idkey],[addtype],[plus4],[scrapedt]
,[dpb],[price],[buy_flag],[date],GETDATE() AS AddDate
from dbo.premover
where zip in((SELECT b.zipto
from dbo.LGOrderZips a left JOIN dbo.zipcode_radius_around b
ON a.codes=b.zipfrom
where orderid=@OrderId AND b.Radius<= @Radius
AND b.ZipFrom is NOT NULL AND b.zipto is NOT NULL
AND b.ZipTo NOT IN(SELECT codes FROM dbo.LGOrderZips where orderid=@OrderId )
GROUP BY ZipTo)
UNION (SELECT codes FROM dbo.LGOrderZips where orderid=@OrderId))
AND price <=@PriceRange
SELECT * INTO dbo.LGPostMoveWeeklyDataCampaignHistory_Backup FROM [dbo].[LGPostMoveWeeklyDataCampaignHistory]
truncate table dbo.LGPostMoveWeeklyDataCampaignHistory
INSERT INTO [POND].[dbo].[LGPostMoveWeeklyDataCampaignHistory]
([OrderId],[fname],[lname],[zip],[plus4],[address],[city],[state],[pub_dt],[seg_nbr]
,[fullname],[dpbc],[addtype],[dwell_typ],[AddDate])
SELECT @OrderId AS OrderId,[fname],[lname],[zip],[plus4],[address],[city],[state],[first_pub_dt],[seg_nbr]
,[fullname],[dpbc],[addtype],[dwell_typ],GETDATE() AS AddDate
from postmover
where zip in((SELECT b.zipto
from dbo.LGOrderZips a left JOIN dbo.zipcode_radius_around b
ON a.codes=b.zipfrom
where orderid=@OrderId AND b.Radius<= @Radius
AND b.ZipFrom is NOT NULL AND b.zipto is NOT NULL
AND b.ZipTo NOT IN(SELECT codes FROM dbo.LGOrderZips where orderid=@OrderId )
GROUP BY ZipTo)
UNION (SELECT codes FROM dbo.LGOrderZips where orderid=@OrderId))
AND dwell_typ=@PropertyType
END
END
