資料同步預存程序

標籤：des   os   資料   2014   io   for   

USE [TKPOS_WuJing]
GO
/****** Object: StoredProcedure [dbo].[sp_get_saleData] Script Date: 07/21/2014 11:20:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:陳新光
-- Create date: 2014-05-28
-- Description:擷取要上傳的銷售資料
/*
execute sp_get_saleData
*/
-- =============================================
/*
TSaleRecord = record // 銷售資料
recNo: string[40]; // guid
shopid: string[40]; // 門店編號
shopname: string[40];
billId: string[40]; // 單據編號
saleDate: TDatetime; // 銷售日期
goodsId: string[40]; // 商品編號
goodsName: string[40];
price: Currency; // 價格
qty: Single; // 數量
amount: Currency; // 金額
cookType: string[8]; // 班次
bigKindId: string[40]; // 大類編號
bigKindName: string[40];
smallKindId: string[40]; // 小類編號
smallKindName: string[40];
unitId: string[40]; // 計量單位編號
unitName: string[40];
vipId: string[40]; // 會員編號
vipName: string[40];
skyId: string[40]; // 收款員編號
skyName: string[40];
ywyId: string[40]; // 業務員編號
ywyName: string[40];
end;
*/
ALTER PROCEDURE [dbo].[sp_get_saleData]
[email protected] varchar(400) output -- 將要上傳的銷售單號列表
AS
BEGIN
set nocount on
-- 擷取最近上傳資料的時間戳記
declare @dd datetime
select @dd=LastUpload from sys_upload where UploadType=‘SaleData‘
-- 將要上傳哪些銷售單
declare @billIds varchar(400)
declare @billId varchar(40)
DECLARE c1 CURSOR FOR
select top 5 SaleNo from Pos_Master where SaleDate>@dd -- 一次上傳5筆
open c1
FETCH NEXT FROM c1 INTO @billId
WHILE @@FETCH_STATUS = 0
BEGIN
if @billIds is null begin
set @billIds=‘‘‘‘[email protected]+‘‘‘‘
end else begin
set @[email protected]+‘,‘+‘‘‘‘[email protected]+‘‘‘‘
end
FETCH NEXT FROM c1 INTO @billId
end
close c1
deallocate c1

declare @sql varchar(2000)
declare @time2 datetime
create table #tmp(
time2 datetime
)
set @sql=‘insert into #tmp select max(SaleDate) from Pos_Master where SaleNo in (‘[email protected]+‘)‘
exec(@sql)
select @time2=time2 from #tmp

-- 擷取要上傳的銷售資料
set @sql=‘select b.SaleNo as billId,b.SaleDate,a.GoodsID,a.GoodsName,a.RetailPrice as price‘
+‘,a.Qty,a.Amount,dbo.f_GetCookType(b.saleDate) as cooktype‘
+‘,a.bigKindId,a.bigKindName,a.smallKindId,a.smallKindName‘
+‘,a.UnitCode as unitid,a.Unit as unitname,‘‘‘‘ as vipid,‘‘‘‘ as vipname,b.Employee as skyid‘
+‘,b.Createtor as skyname,‘‘‘‘ as ywyid,‘‘‘‘ as ywyname,‘+‘‘‘‘
+convert(varchar,@time2,21)+‘‘‘‘+‘ as time2‘
+‘ from pos_detail a‘
+‘ inner join Pos_Master b on a.SaleNo=b.SaleNo‘
+‘ where b.SaleNo in (‘[email protected]+‘)‘
exec (@sql)

drop table #tmp

set nocount off
END