-- pRS_DownBaseData 'in','DB00','fz','sa','','1','0','01','00','0'
--CREATE DATE: 2006-03-22
--MODIFY DATE: 2006-03-23
CREATE PROCEDURE pRS_DownBaseData (@ls_Item varchar(20), @s_name varchar(20), @S_DB varchar(50), @s_user varchar(10),
@s_psd varchar(15), @ls_delflag char(1), @ls_evenFlag char(1),@ls_myNo VARchar(4), @ls_SNo VARchar(4), @ls_type varchar(1))
WITH ENCRYPTION AS
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS On
SET ANSI_warnings On
if @s_name is null or ltrim(rtrim(@s_name))='' set @s_name = 'db00'
if @s_db is null or ltrim(rtrim(@s_db)) ='' set @s_db = 'shopdb'
if @s_user is null or ltrim(rtrim(@s_user))='' set @s_user = 'sa'
if @s_psd is null set @s_psd = ''
declare @sql varchar(1500), @tbName varchar(20), @iret int
set @iret = 1
-----------------------------------------------------------------------------
declare @ls_midTb VARCHAR(20), @ls_tb varchar(20), @ls_re_DTL VARCHAR(30)
DECLARE @ls_From varchar(200), @ls_as varchar(20),@ls_from2 varchar(200)
declare @T_name varchar(20),@ls_where varchar(200),@ls_shopno varchar(10),@ls_attrib varchar(10),@ls_myname varchar(50)
declare @lastdowndate datetime
--set @ls_delflag = '0'
--if db_name() = 'jtcwdb' set @ls_delflag = '1'
if not @ls_evenFlag = '0' set @ls_evenFlag = '1'
--应该区分是在同一台机器还异机互传
if @ls_evenFlag = '0'
begin
set @ls_From = ' from OPENROWSET("SQLOLEDB",' + '"' + @s_name + '";' + '"' + @s_user + '";' + '"' + @s_psd + '",' + @s_db + '.dbo.'
set @ls_From2 = ' OPENROWSET("SQLOLEDB",' + '"' + @s_name + '";' + '"' + @s_user + '";' + '"' + @s_psd + '",' + @s_db + '.dbo.'
set @ls_as = ') as a '
end
else
begin
set @ls_From = ' From ' + @s_db + '.dbo.'
set @ls_From2 = ' ' + @s_db + '.dbo.'
set @ls_as = ' as a '
end
--set @ls_midtb = 'MD_' + @ls_item
if @ls_item = 'pno'
begin
if @ls_type='1' --下载最新数据
begin
select @lastdowndate = new_date from sys_shop_rights where rights_name='下载基本资料'
if @lastdowndate is null
set @lastdowndate=2000-01-01
set @ls_where = ' where convert(char(13),modifydate,121)>='+''''+convert(char(13),@lastdowndate,121)+''''
end
else
set @ls_where=' '
set @ls_tb = 'p_code'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as + @ls_where + ' order by a.p_u_no'
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_p_code)
begin
if @ls_type <> '1'
Truncate table p_code
else
delete from p_code where p_u_no in(select p_u_no from md_p_code)
if (@@error<>0) goto L_wError
insert into p_code
(p_u_no,p_no,p_key,p_c_name,p_p_name,p_mark,grp_no,brand_no,place,dept_no,v_no,unit,ave_price,last_price,sale_price,move_price,in_rate,SALE_rate,pf_price1,pf_price2,pf_price3,vip_price,RATE_FLAG,RATE_SALE,carddisc,disc_rate,low_limit,high_limit,enable,ATTRIB,bale_flag,JOIN_FLAG,type_flag,dire_FLAG,MUCHVEN_flag,abc_flag,del_flag,del_date,wholeunit,wholenum,createdate,createman,modifydate,modifyman,n_addrate,help_pno,save_Date,ave_weekSale,sect_sale,this_month_sale,month_sale,last_saleDate,last_indate,high_price,low_price,note,p_color,p_size,ml_no,jj_no)
select
p_u_no,p_no,p_key,p_c_name,p_p_name,p_mark,grp_no,brand_no,place,
(@ls_MyNo+ltrim(rtrim(substring(dept_no,3,6))))as dept_no,
v_no,unit,ave_price,last_price,sale_price,move_price,in_rate,SALE_rate,pf_price1,pf_price2,pf_price3,vip_price,RATE_FLAG,RATE_SALE,carddisc,disc_rate,low_limit,high_limit,enable,ATTRIB,bale_flag,JOIN_FLAG,type_flag,dire_FLAG,MUCHVEN_flag,abc_flag,del_flag,del_date,wholeunit,wholenum,createdate,createman,modifydate,modifyman,n_addrate,help_pno,save_Date,ave_weekSal
e,sect_sale,this_month_sale,month_sale,last_saleDate,last_indate,high_price,low_price,note,p_color,p_size,ml_no,jj_no
from MD_p_code
if (@@error<>0) goto L_wError
--insert into sys_RS_Log(S_ShopNo,T_ShopNo,Trans_Date,Trans_Dtl,Trans_Tb,RS_Flag,S_Flag)
-- values(@ls_sNo, @ls_MyNo,getdate(),'接收OK', @ls_tb, '1', '1')
END
--供应商
set @ls_tb = 'ven_head'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as + @ls_where + ' order by a.v_no'
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_ven_head)
begin
if @ls_type <> '1'
Truncate table ven_head
ELSE
delete from ven_head where v_no in(select v_no from md_ven_head)
if (@@error<>0) goto L_wError
insert into ven_head select * from md_ven_head
if (@@error<>0) goto L_wError
--insert into sys_RS_Log(S_ShopNo,T_ShopNo,Trans_Date,Trans_Dtl,Trans_Tb,RS_Flag,S_Flag)
-- values(@ls_sNo, @ls_MyNo,getdate(),'接收OK', @ls_tb, '1', '1')
end
set @ls_tb = 'ven_dtl'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as + @ls_where + ' order by a.v_no,p_u_no'
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_ven_dtl)
begin
insert into ven_dtl(v_no,p_u_no,p_no,price,kc_num,last_indate,high_price,low_price,enable,modifydate)
select v_no,p_u_no,p_no,price,0,last_indate,high_price,low_price,enable,modifydate from md_ven_dtl where
not exists(select p_u_no from ven_dtl where ven_dtl.p_u_no=md_ven_dtl.p_u_no and ven_dtl.v_no=md_ven_dtl.v_no)
if (@@error<>0) goto L_wError
--insert into sys_RS_Log(S_ShopNo,T_ShopNo,Trans_Date,Trans_Dtl,Trans_Tb,RS_Flag,S_Flag)
-- values(@ls_sNo, @ls_MyNo,getdate(),'接收OK', @ls_tb, '1', '1')
end
--部门资料
set @ls_tb = 'dept'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as + ' where left(a.dept_no,2) = '+ @ls_myNo + ' order by a.dept_no'
execute(@sql)
if (@@error<>0) goto L_wError
--仓库号
declare @ckNo varchar(8)
select @ckno = dept_no from dept where dept_flag = '1' and myshop_flag='1'
if @ckno is null or @ckno = '' select @ckno = dept_no from dept where dept_flag = '1'
if @ckno is null or @ckno = '' set @ckNo = @ls_MyNo
--从中间数据表插入到目的表
Truncate table dept
if (@@error<>0) goto L_wError
insert into dept select * from md_dept
if (@@error<>0) goto L_wError
update dept set dept_flag = '0', myshop_flag = '1'
update dept set dept_flag = '1' where dept_no = @ckNo
if (@@error<>0) goto L_wError
--insert into sys_RS_Log(S_ShopNo,T_ShopNo,Trans_Date,Trans_Dtl,Trans_Tb,RS_Flag,S_Flag)
-- values(@ls_sNo, @ls_MyNo,getdate(),'接收OK', @ls_tb, '1', '1')
if not exists(select * from sys_mis)
begin
set @sql = ' insert into sys_mis select a.* ' + @ls_from + @ls_tb + @ls_as
exec(@sql)
if (@@error<>0) goto L_wError
update sys_mis set shop_no = @ls_myno
end
set @ls_tb = 'sys_shop'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objec
tproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
begin
--delete from md_sys_shop where shop_no in(select shop_no from sys_shop)
delete sys_shop --删除分店表中的所有记录
insert into sys_shop select * from md_sys_shop
update sys_shop set myshop_flag = '0'
update sys_shop set myshop_flag = '1' where shop_no = @ls_MyNo
if (@@error<>0) goto L_wError
end
--商品类别资料
set @ls_tb = 'p_group'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as + ' order by a.grp_no'
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
Truncate table p_group
if (@@error<>0) goto L_wError
insert into p_group select * from md_p_group
if (@@error<>0) goto L_wError
--insert into sys_RS_Log(S_ShopNo,T_ShopNo,Trans_Date,Trans_Dtl,Trans_Tb,RS_Flag,S_Flag)
-- values(@ls_sNo, @ls_MyNo,getdate(),'接收OK', @ls_tb, '1', '1')
--促销组合资料
set @ls_tb = 'bale_dtl'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as + ' order by a.bale_u_no'
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
Truncate table bale_dtl
if (@@error<>0) goto L_wError
insert into bale_dtl select * from md_bale_dtl
if (@@error<>0) goto L_wError
--结构设定
set @ls_tb = 'split_flag'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as + ' order by a.p_u_no1'
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
Truncate table split_flag
if (@@error<>0) goto L_wError
insert into split_flag select * from md_split_flag
if (@@error<>0) goto L_wError
--竞争者
set @ls_tb = 'Adj_rival'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
Truncate table Adj_rival
if (@@error<>0) goto L_wError
insert into Adj_rival select * from md_Adj_rival
if (@@error<>0) goto L_wError
--调价方式
set @ls_tb = 'Adj_type'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
Truncate table Adj_type
if (@@error<>0) goto L_wError
insert into Adj_type select * from md_Adj_type
if (@@error<>0) goto L_wError
--扣款方式
set @ls_tb = 'Deduct'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_i
d(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
Truncate table Deduct
if (@@error<>0) goto L_wError
insert into Deduct select * from md_Deduct
if (@@error<>0) goto L_wError
--图片
set @ls_tb = 'P_picture'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
Truncate table P_picture
if (@@error<>0) goto L_wError
insert into P_picture select * from md_P_picture
if (@@error<>0) goto L_wError
--付款方式
set @ls_tb = 'pay_way'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
Truncate table pay_way
if (@@error<>0) goto L_wError
insert into pay_way select * from md_pay_way
if (@@error<>0) goto L_wError
--特价
set @ls_tb = 'Special'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
Truncate table Special
if (@@error<>0) goto L_wError
insert into Special select * from md_Special
if (@@error<>0) goto L_wError
--送货车辆
set @ls_tb = 'Truck_file'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
Truncate table Truck_file
if (@@error<>0) goto L_wError
insert into Truck_file select * from md_Truck_file
if (@@error<>0) goto L_wError
--单位
set @ls_tb = 'unit'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
Truncate table unit
if (@@error<>0) goto L_wError
insert into unit select * from md_unit
if (@@error<>0) goto L_wError
--区域
set @ls_tb = 'ven_area'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
Truncate table ven_area
if (@@error<>0) goto L_wError
insert into ven_area select * from md_ven_area
if (@@error<>0) goto L_wError
--品牌
set @ls_tb =
'p_brand'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
Truncate table p_brand
if (@@error<>0) goto L_wError
insert into p_brand select * from md_p_brand
if (@@error<>0) goto L_wError
--颜色
set @ls_tb = 'p_color'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
Truncate table p_color
if (@@error<>0) goto L_wError
insert into p_color select * from md_p_color
if (@@error<>0) goto L_wError
--尺码
set @ls_tb = 'p_size'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
Truncate table p_size
if (@@error<>0) goto L_wError
insert into p_size select * from md_p_size
if (@@error<>0) goto L_wError
--尺码组
set @ls_tb = 'p_sizegroup'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
Truncate table p_sizegroup
if (@@error<>0) goto L_wError
insert into p_sizegroup select * from md_p_sizegroup
if (@@error<>0) goto L_wError
--季节
set @ls_tb = 'p_jijie'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
Truncate table p_jijie
if (@@error<>0) goto L_wError
insert into p_jijie select * from md_p_jijie
if (@@error<>0) goto L_wError
--面料
set @ls_tb = 'p_mianliao'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
Truncate table p_mianliao
if (@@error<>0) goto L_wError
insert into p_mianliao select * from md_p_mianliao
if (@@error<>0) goto L_wError
--子条码表,先这样,如速度过慢则再优化
set @ls_tb = 'p_code_item'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
Truncate table p_code_item
if (@@error<>0) goto
L_wError
insert into p_code_item select * from md_p_code_item
if (@@error<>0) goto L_wError
end
--系统参数设置
if @ls_item = 'para'
begin
set @ls_tb = 'sys_pos'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
Truncate table sys_pos
if (@@error<>0) goto L_wError
if not exists(select * from sys_pos)
begin
insert into sys_pos select * from md_sys_pos
if (@@error<>0) goto L_wError
end
set @ls_tb = 'sys_card'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
Truncate table sys_card
if (@@error<>0) goto L_wError
if not exists(select * from sys_card)
begin
insert into sys_card select * from md_sys_card
if (@@error<>0) goto L_wError
end
set @ls_tb = 'sys_mis'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
select @ls_shopno = shop_no ,@ls_attrib = shop_attrib,@ls_myname = my_name from sys_mis
Truncate table sys_mis
if (@@error<>0) goto L_wError
if not exists(select * from sys_mis)
begin
insert into sys_mis select * from md_sys_mis
if (@@error<>0) goto L_wError
update sys_mis set shop_no = @ls_shopno,shop_attrib = @ls_attrib,my_name = @ls_myname
if (@@error<>0) goto L_wError
end
set @ls_tb = 'sys_param'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +' where str_1='+''''+'下载'+''''
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
delete from sys_param where p_id in(select p_id from md_sys_param)
if (@@error<>0) goto L_wError
insert into sys_param select * from md_sys_param
--前台付款方式
set @ls_tb = 'Pay_styl'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
Truncate table Pay_styl
if (@@error<>0) goto L_wError
insert into Pay_styl select * from md_Pay_styl
if (@@error<>0) goto L_wError
--前台参数
set @ls_tb = 'Sys_pospara'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
Truncate table Sys_pospara
if (@@error<>
0) goto L_wError
insert into Sys_pospara select * from md_Sys_pospara
if (@@error<>0) goto L_wError
--分店权限表
set @ls_tb = 'Sys_shop_rights'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if objectproperty(object_id(@ls_midtb),'istable') = 1
drop table MD_Sys_shop_rights
select * into MD_sys_shop_rights from sys_shop_rights where shop_no = @ls_myno
Truncate table Sys_shop_rights
if (@@error<>0) goto L_wError
insert into Sys_shop_rights select * from md_Sys_shop_rights
if (@@error<>0) goto L_wError
update sys_shop_rights set new_date = (select new_date from md_sys_shop_rights where md_sys_shop_rights.shop_no = sys_shop_rights.shop_no and md_sys_shop_rights.rights_name = sys_shop_rights.rights_name)
if (@@error<>0) goto L_wError
drop table MD_sys_shop_rights
end
--采购单据
if @ls_item = 'in'
begin
if @ls_type='1' --下载最新数据
begin
select @lastdowndate = new_date from sys_shop_rights where rights_name='下载采购单据'
if @lastdowndate is null
set @lastdowndate=2000-01-01
set @ls_where = ' where convert(char(13),verify_date,121)>='+''''+convert(char(13),@lastdowndate,121)+''''
end
else
set @ls_where = ' where 1=1'
--订货单主表
set @ls_tb = 'fill_head'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +@ls_where
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_fill_head)
begin
if @ls_type <> '1'
Truncate table fill_head
ELSE
delete from fill_head where table_no in(select table_no from md_fill_head)
if (@@error<>0) goto L_wError
insert into fill_head select * from md_fill_head
if (@@error<>0) goto L_wError
end
--明细表
set @ls_tb = 'fill_dtl'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'fill_head) b ' +@ls_where +' and a.table_no = b.table_no'
execute(@sql)
if (@@error<>0) goto L_wError
if exists(select * from MD_fill_dtl)
--从中间数据表插入到目的表
if exists(select * from MD_fill_dtl)
begin
if @ls_type <> '1'
Truncate table fill_dtl
ELSE
delete from fill_dtl where table_no in(select table_no from md_fill_dtl)
if (@@error<>0) goto L_wError
insert into fill_dtl select * from md_fill_dtl
if (@@error<>0) goto L_wError
end
--子条码明细表
set @ls_tb = 't_sheet_item'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'fill_head) b ' +@ls_where +" and a.sheet_no = b.table_no and a.sheet_type='fill_head'"
execute(@sql)
if (@@error<>0) goto L_wError
if exists(select * from MD_t_sheet_item)
--从中间数据表插入到目的表
if exists(select * from MD_fill_dtl)
begin
delete from t
_sheet_item where sheet_no in(select sheet_no from md_t_sheet_item)
if (@@error<>0) goto L_wError
insert into t_sheet_item select * from md_t_sheet_item
if (@@error<>0) goto L_wError
end
--入库单主表
set @ls_tb = 'chk_head'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +@ls_where
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_chk_head)
begin
if @ls_type <> '1'
Truncate table chk_head
ELSE
delete from chk_head where table_no in(select table_no from md_chk_head)
if (@@error<>0) goto L_wError
insert into chk_head select * from md_chk_head
if (@@error<>0) goto L_wError
end
--明细表
set @ls_tb = 'chk_dtl'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'chk_head) b ' +@ls_where +' and a.table_no = b.table_no'
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_chk_dtl)
begin
if @ls_type <> '1'
Truncate table chk_dtl
ELSE
delete from chk_dtl where table_no in(select table_no from md_chk_dtl)
if (@@error<>0) goto L_wError
insert into chk_dtl select * from md_chk_dtl
if (@@error<>0) goto L_wError
end
--子条码明细表
set @ls_tb = 't_sheet_item'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'chk_head) b ' +@ls_where +" and a.sheet_no = b.table_no and a.sheet_type='chk_head'"
execute(@sql)
if (@@error<>0) goto L_wError
if exists(select * from MD_t_sheet_item)
--从中间数据表插入到目的表
if exists(select * from MD_fill_dtl)
begin
delete from t_sheet_item where sheet_no in(select sheet_no from md_t_sheet_item)
if (@@error<>0) goto L_wError
insert into t_sheet_item select * from md_t_sheet_item
if (@@error<>0) goto L_wError
end
--退货单主表
set @ls_tb = 'rtn_head'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +@ls_where
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_rtn_head)
begin
if @ls_type <> '1'
Truncate table rtn_head
ELSE
delete from rtn_head where table_no in(select table_no from md_rtn_head)
if (@@error<>0) goto L_wError
insert into rtn_head select * from md_rtn_head
if (@@error<>0) goto L_wError
end
--明细表
set @ls_tb = 'rtn_dtl'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls
_from2+ 'rtn_head) b ' +@ls_where +' and a.table_no = b.table_no'
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_rtn_dtl)
begin
if @ls_type <> '1'
Truncate table rtn_dtl
ELSE
delete from rtn_dtl where table_no in(select table_no from md_rtn_dtl)
if (@@error<>0) goto L_wError
insert into rtn_dtl select * from md_rtn_dtl
if (@@error<>0) goto L_wError
end
--子条码明细表
set @ls_tb = 't_sheet_item'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'rtn_head) b ' +@ls_where +" and a.sheet_no = b.table_no and a.sheet_type='rtn_head'"
execute(@sql)
if (@@error<>0) goto L_wError
if exists(select * from MD_t_sheet_item)
--从中间数据表插入到目的表
if exists(select * from MD_fill_dtl)
begin
delete from t_sheet_item where sheet_no in(select sheet_no from md_t_sheet_item)
if (@@error<>0) goto L_wError
insert into t_sheet_item select * from md_t_sheet_item
if (@@error<>0) goto L_wError
end
--要货单主表
set @ls_tb = 'yh_head'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +@ls_where
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_yh_head)
begin
if @ls_type <> '1'
Truncate table yh_head
ELSE
delete from yh_head where table_no in(select table_no from md_yh_head)
if (@@error<>0) goto L_wError
insert into yh_head select * from md_yh_head
if (@@error<>0) goto L_wError
end
--明细表
set @ls_tb = 'yh_dtl'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'yh_head) b ' +@ls_where +' and a.table_no = b.table_no'
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_yh_dtl)
begin
if @ls_type <> '1'
Truncate table yh_dtl
ELSE
delete from yh_dtl where table_no in(select table_no from md_yh_dtl)
if (@@error<>0) goto L_wError
insert into yh_dtl select * from md_yh_dtl
if (@@error<>0) goto L_wError
end
--子条码明细表
set @ls_tb = 't_sheet_item'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'yh_head) b ' +@ls_where +" and a.sheet_no = b.table_no and a.sheet_type='yh_head'"
execute(@sql)
if (@@error<>0) goto L_wError
if exists(select * from MD_t_sheet_item)
--从中间数据表插入到目的表
if exists(select * from MD_fill_dtl)
begin
delete from t_sheet_item where sheet_no in(select sheet_no from md_t_sheet_item)
if (@@error<>0) goto L_wError
insert into t_sheet_item select * from md_t_sheet_item
if (@@error<>0) goto L_wError
end
--付款单主表
set @ls_tb = 'pay_head
'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +@ls_where
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_pay_head)
begin
if @ls_type <> '1'
Truncate table pay_head
ELSE
delete from pay_head where table_no in(select table_no from md_pay_head)
if (@@error<>0) goto L_wError
insert into pay_head select * from md_pay_head
if (@@error<>0) goto L_wError
end
--明细表
set @ls_tb = 'pay_dtl'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'pay_head) b ' +@ls_where +' and a.table_no = b.table_no'
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_pay_dtl)
begin
if @ls_type <> '1'
Truncate table pay_dtl
ELSE
delete from pay_dtl where table_no in(select table_no from md_pay_dtl)
if (@@error<>0) goto L_wError
insert into pay_dtl select * from md_pay_dtl
if (@@error<>0) goto L_wError
end
--扣款单主表
set @ls_tb = 'deduct_head'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +@ls_where
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_deduct_head)
begin
if @ls_type <> '1'
Truncate table deduct_head
ELSE
delete from deduct_head where table_no in(select table_no from md_deduct_head)
if (@@error<>0) goto L_wError
insert into deduct_head select * from md_deduct_head
if (@@error<>0) goto L_wError
end
--明细表
set @ls_tb = 'deduct_dtl'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'deduct_head) b ' +@ls_where +' and a.table_no = b.table_no'
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_deduct_dtl)
begin
if @ls_type <> '1'
Truncate table deduct_dtl
ELSE
delete from deduct_dtl where table_no in(select table_no from md_deduct_dtl)
if (@@error<>0) goto L_wError
insert into deduct_dtl select * from md_deduct_dtl
if (@@error<>0) goto L_wError
end
--历史进价差价单主表
set @ls_tb = 'Adjbackin_head'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +@ls_where
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_adjbackin_head)
begin
if @ls_type <> '1'
Truncate table Adjbackin_head
ELSE
delete from Adjbac
kin_head where table_no in(select table_no from md_Adjbackin_head)
if (@@error<>0) goto L_wError
insert into Adjbackin_head select * from md_Adjbackin_head
if (@@error<>0) goto L_wError
end
--明细表
set @ls_tb = 'Adjbackin_dtl'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'Adjbackin_head) b ' +@ls_where +' and a.table_no = b.table_no'
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_adjbackin_dtl)
begin
if @ls_type <> '1'
Truncate table Adjbackin_dtl
ELSE
delete from Adjbackin_dtl where table_no in(select table_no from md_Adjbackin_dtl)
if (@@error<>0) goto L_wError
insert into Adjbackin_dtl select * from md_Adjbackin_dtl
if (@@error<>0) goto L_wError
end
end
--价格单据
if @ls_item = 'price'
begin
if @ls_type='1' --下载最新数据
begin
select @lastdowndate = new_date from sys_shop_rights where rights_name='下载价格单据'
if @lastdowndate is null
set @lastdowndate=2000-01-01
set @ls_where = ' where convert(char(13),verify_date,121)>='+''''+convert(char(13),@lastdowndate,121)+''''
end
else
set @ls_where = ' where 1=1'
--进价调整单主表
set @ls_tb = 'Adjinpay_head'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +@ls_where
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_adjinpay_head)
begin
if @ls_type <> '1'
Truncate table Adjinpay_head
ELSE
delete from Adjinpay_head where table_no in(select table_no from MD_Adjinpay_head)
if (@@error<>0) goto L_wError
insert into Adjinpay_head select * from MD_Adjinpay_head
if (@@error<>0) goto L_wError
end
--明细表
set @ls_tb = 'Adjinpay_dtl'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'Adjinpay_head) b ' +@ls_where +' and a.table_no = b.table_no'
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_adjinpay_dtl)
begin
if @ls_type <> '1'
Truncate table Adjinpay_dtl
ELSE
delete from Adjinpay_dtl where table_no in(select table_no from md_Adjinpay_dtl)
if (@@error<>0) goto L_wError
insert into Adjinpay_dtl select * from md_Adjinpay_dtl
if (@@error<>0) goto L_wError
end
--进销价调整单主表
set @ls_tb = 'Adjinsale_head'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +@ls_where
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_adjinsale_head)
begin
if @ls_type <> '1'
Truncate table Adjinsale_head
ELSE
delete from Adjinsale_head where table_no in(select table_no from md_Adjinsale_head)
if
(@@error<>0) goto L_wError
insert into Adjinsale_head select * from md_Adjinsale_head
if (@@error<>0) goto L_wError
end
--明细表
set @ls_tb = 'Adjinsale_dtl'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'Adjinsale_head) b ' +@ls_where +' and a.table_no = b.table_no'
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_adjinsale_dtl)
begin
if @ls_type <> '1'
Truncate table Adjinsale_dtl
ELSE
delete from Adjinsale_dtl where table_no in(select table_no from md_Adjinsale_dtl)
if (@@error<>0) goto L_wError
insert into Adjinsale_dtl select * from md_Adjinsale_dtl
if (@@error<>0) goto L_wError
end
--销价调整单主表
set @ls_tb = 'Adjsale_head'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +@ls_where
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_adjsale_head)
begin
if @ls_type <> '1'
Truncate table Adjsale_head
ELSE
delete from Adjsale_head where table_no in(select table_no from md_Adjsale_head)
if (@@error<>0) goto L_wError
insert into Adjsale_head select * from md_Adjsale_head
if (@@error<>0) goto L_wError
end
--明细表
set @ls_tb = 'Adjsale_dtl'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'Adjsale_head) b ' +@ls_where +' and a.table_no = b.table_no'
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_adjsale_dtl)
begin
if @ls_type <> '1'
Truncate table Adjsale_dtl
ELSE
delete from Adjsale_dtl where table_no in(select table_no from md_Adjsale_dtl)
if (@@error<>0) goto L_wError
insert into Adjsale_dtl select * from md_Adjsale_dtl
if (@@error<>0) goto L_wError
end
--会员价调整单主表
set @ls_tb = 'Adjvip_head'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +@ls_where
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_adjvip_head)
begin
if @ls_type <> '1'
Truncate table Adjvip_head
ELSE
delete from Adjvip_head where table_no in(select table_no from md_Adjvip_head)
if (@@error<>0) goto L_wError
insert into Adjvip_head select * from md_Adjvip_head
if (@@error<>0) goto L_wError
end
--明细表
set @ls_tb = 'Adjvip_dtl'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb +
@ls_as +','+@ls_from2+ 'Adjvip_head) b ' +@ls_where +' and a.table_no = b.table_no'
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_adjvip_dtl)
begin
if @ls_type <> '1'
Truncate table Adjvip_dtl
ELSE
delete from Adjvip_dtl where table_no in(select table_no from md_Adjvip_dtl)
if (@@error<>0) goto L_wError
insert into Adjvip_dtl select * from md_Adjvip_dtl
if (@@error<>0) goto L_wError
end
--特价单主表
set @ls_tb = 'Special_head'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +@ls_where
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_special_head)
begin
if @ls_type <> '1'
Truncate table Special_head
ELSE
delete from Special_head where table_no in(select table_no from md_Special_head)
if (@@error<>0) goto L_wError
insert into Special_head select * from md_Special_head
if (@@error<>0) goto L_wError
end
--明细表
set @ls_tb = 'Special_dtl'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'Special_head) b ' +@ls_where +' and a.table_no = b.table_no'
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_special_dtl)
begin
if @ls_type <> '1'
Truncate table Special_dtl
ELSE
delete from Special_dtl where table_no in(select table_no from md_Special_dtl)
if (@@error<>0) goto L_wError
insert into Special_dtl select * from md_Special_dtl
if (@@error<>0) goto L_wError
end
--批发价调整单主表
set @ls_tb = 'Pf_adjmst'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +@ls_where
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_pf_adjmst)
begin
if @ls_type <> '1'
Truncate table Pf_adjmst
ELSE
delete from Pf_adjmst where table_no in(select table_no from md_Pf_adjmst)
if (@@error<>0) goto L_wError
insert into Pf_adjmst select * from md_Pf_adjmst
if (@@error<>0) goto L_wError
end
--明细表
set @ls_tb = 'Pf_adjdtl'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.table_no,a.i_index,a.p_u_no,a.p_no,a.in_price,a.sale_price,a.old_pf_price1,a.new_pf_price1,a.old_pf_price2,a.new_pf_price2,
a.old_pf_price3,a.new_pf_price3,a.note,a.new_last_price,a.new_sale_price into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'Pf_adjmst) b ' +@ls_where +' and a.table_no = b.table_no'
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_pf_adjdtl)
begin
if @ls_type <> '1'
Truncate table Pf_adjdtl
ELSE
delete from Pf_adjdtl where table_no in(select t
able_no from md_Pf_adjdtl)
if (@@error<>0) goto L_wError
insert into Pf_adjdtl(table_no,i_index,p_u_no,p_no,in_price,sale_price,old_pf_price1,new_pf_price1,old_pf_price2,new_pf_price2,
old_pf_price3,new_pf_price3,note,new_last_price,new_sale_price) select * from md_Pf_adjdtl
if (@@error<>0) goto L_wError
end
end
--库存单据
if @ls_item = 'kc'
begin
if @ls_type='1' --下载最新数据
begin
select @lastdowndate = new_date from sys_shop_rights where rights_name='下载库存单据'
if @lastdowndate is null
set @lastdowndate=2000-01-01
set @ls_where = ' where convert(char(13),verify_date,121)>='+''''+convert(char(13),@lastdowndate,121)+''''
end
else
set @ls_where = ' where 1=1'
--损溢单主表
set @ls_tb = 'flt_head'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +@ls_where
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_flt_head)
begin
if @ls_type <> '1'
Truncate table flt_head
ELSE
delete from flt_head where table_no in(select table_no from md_flt_head)
if (@@error<>0) goto L_wError
insert into flt_head select * from md_flt_head
if (@@error<>0) goto L_wError
end
--明细表
set @ls_tb = 'flt_dtl'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'flt_head) b ' +@ls_where +' and a.table_no = b.table_no'
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_flt_dtl)
begin
if @ls_type <> '1'
Truncate table flt_dtl
ELSE
delete from flt_dtl where table_no in(select table_no from md_flt_dtl)
if (@@error<>0) goto L_wError
insert into flt_dtl select * from md_flt_dtl
if (@@error<>0) goto L_wError
end
--子条码明细表
set @ls_tb = 't_sheet_item'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'flt_head) b ' +@ls_where +" and a.sheet_no = b.table_no and a.sheet_type='flt_head'"
execute(@sql)
if (@@error<>0) goto L_wError
if exists(select * from MD_t_sheet_item)
--从中间数据表插入到目的表
if exists(select * from MD_fill_dtl)
begin
delete from t_sheet_item where sheet_no in(select sheet_no from md_t_sheet_item)
if (@@error<>0) goto L_wError
insert into t_sheet_item select * from md_t_sheet_item
if (@@error<>0) goto L_wError
end
--店内调拔单主表
set @ls_tb = 'move_head'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +@ls_where
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_move_head)
begin
if @ls_type <> '1'
Truncate table move_head
ELSE
delete from move_head where table_no in(select table_no from md_move_head)
if (@@error<>0) goto L_wError
insert into move_head select * from md_move_head
if (@@error<>0) goto L_wError
end
--明细表
set @ls_tb = 'move_dtl'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'move_head) b ' +@ls_where +' and a.table_no = b.table_no'
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_move_dtl)
begin
if @ls_type <> '1'
Truncate table move_dtl
ELSE
delete from move_dtl where table_no in(select table_no from md_move_dtl)
if (@@error<>0) goto L_wError
insert into move_dtl select * from md_move_dtl
if (@@error<>0) goto L_wError
end
--子条码明细表
set @ls_tb = 't_sheet_item'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'move_head) b ' +@ls_where +" and a.sheet_no = b.table_no and a.sheet_type='move_head'"
execute(@sql)
if (@@error<>0) goto L_wError
if exists(select * from MD_t_sheet_item)
--从中间数据表插入到目的表
if exists(select * from MD_fill_dtl)
begin
delete from t_sheet_item where sheet_no in(select sheet_no from md_t_sheet_item)
if (@@error<>0) goto L_wError
insert into t_sheet_item select * from md_t_sheet_item
if (@@error<>0) goto L_wError
end
--领用单主表
set @ls_tb = 'Selftake_head'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +@ls_where
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_selftake_head)
begin
if @ls_type <> '1'
Truncate table Selftake_head
ELSE
delete from Selftake_head where table_no in(select table_no from md_Selftake_head)
if (@@error<>0) goto L_wError
insert into Selftake_head select * from md_Selftake_head
if (@@error<>0) goto L_wError
end
--明细表
set @ls_tb = 'Selftake_dtl'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'Selftake_head) b ' +@ls_where +' and a.table_no = b.table_no'
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_selftake_dtl)
begin
if @ls_type <> '1'
Truncate table Selftake_dtl
ELSE
delete from Selftake_dtl where table_no in(select table_no from md_Selftake_dtl)
if (@@error<>0) goto L_wError
insert into Selftake_dtl select * from md_Selftake_dtl
if (@@error<>0) goto L_wError
end
--子条码明细表
set @ls_tb = 't_sheet_item'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @s
ql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'selftake_head) b ' +@ls_where +" and a.sheet_no = b.table_no and a.sheet_type='selftake_head'"
execute(@sql)
if (@@error<>0) goto L_wError
if exists(select * from MD_t_sheet_item)
--从中间数据表插入到目的表
if exists(select * from MD_fill_dtl)
begin
delete from t_sheet_item where sheet_no in(select sheet_no from md_t_sheet_item)
if (@@error<>0) goto L_wError
insert into t_sheet_item select * from md_t_sheet_item
if (@@error<>0) goto L_wError
end
--拆分组合单主表
set @ls_tb = 'Split_head'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +@ls_where
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_split_head)
begin
if @ls_type <> '1'
Truncate table Split_head
ELSE
delete from Split_head where table_no in(select table_no from md_Split_head)
if (@@error<>0) goto L_wError
insert into Split_head select * from md_Split_head
if (@@error<>0) goto L_wError
end
--明细表
set @ls_tb = 'Split_dtl'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'Split_head) b ' +@ls_where +' and a.table_no = b.table_no'
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_split_dtl)
begin
if @ls_type <> '1'
Truncate table Split_dtl
ELSE
delete from Split_dtl where table_no in(select table_no from md_Split_dtl)
if (@@error<>0) goto L_wError
insert into Split_dtl select * from md_Split_dtl
if (@@error<>0) goto L_wError
end
--配送单主表
set @ls_tb = 'Ps_head'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +@ls_where
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_ps_head)
begin
if @ls_type <> '1'
Truncate table Ps_head
ELSE
delete from Ps_head where table_no in(select table_no from md_Ps_head)
if (@@error<>0) goto L_wError
insert into Ps_head select * from md_Ps_head
if (@@error<>0) goto L_wError
end
--明细表
set @ls_tb = 'Ps_dtl'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'Ps_head) b ' +@ls_where +' and a.table_no = b.table_no'
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_ps_dtl)
begin
if @ls_type <> '1'
Truncate table Ps_dtl
ELSE
delete from Ps_dtl where table_no in(select table_no from md_Ps_dtl)
if (@@error<>0) goto L_wError
insert into Ps_dtl select * from md_Ps_dtl
if (@@error<>0) goto L_wError
end
--子条码明细表
set @ls_t
b = 't_sheet_item'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'ps_head) b ' +@ls_where +" and a.sheet_no = b.table_no and a.sheet_type like 'ps_head%'"
execute(@sql)
if (@@error<>0) goto L_wError
if exists(select * from MD_t_sheet_item)
--从中间数据表插入到目的表
if exists(select * from MD_fill_dtl)
begin
delete from t_sheet_item where sheet_no in(select sheet_no from md_t_sheet_item)
if (@@error<>0) goto L_wError
insert into t_sheet_item select * from md_t_sheet_item
if (@@error<>0) goto L_wError
end
end
--批发数据
if @ls_item = 'whole'
begin
if @ls_type='1' --下载最新数据
begin
select @lastdowndate = new_date from sys_shop_rights where rights_name='下载批发数据'
if @lastdowndate is null
set @lastdowndate=2000-01-01
set @ls_where = ' where convert(char(13),verify_date,121)>='+''''+convert(char(13),@lastdowndate,121)+''''
end
else
set @ls_where = ' where 1=1'
--批发订货单主表
set @ls_tb = 'Pf_fillmst'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +@ls_where
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_pf_fillmst)
begin
if @ls_type <> '1'
Truncate table Pf_fillmst
ELSE
delete from Pf_fillmst where table_no in(select table_no from md_Pf_fillmst)
if (@@error<>0) goto L_wError
insert into Pf_fillmst select * from md_Pf_fillmst
if (@@error<>0) goto L_wError
end
--明细表
set @ls_tb = 'Pf_filldtl'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.table_no,a.i_index,a.p_u_no,a.p_no,a.dept_no,a.c_p_no,a.in_price,a.pf_price,a.sale_number,a.send_number,a.note,a.whole_num,a.single_num into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'Pf_fillmst) b ' +@ls_where +' and a.table_no = b.table_no'
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_pf_filldtl)
begin
if @ls_type <> '1'
Truncate table Pf_filldtl
ELSE
delete from Pf_filldtl where table_no in(select table_no from md_Pf_filldtl)
if (@@error<>0) goto L_wError
insert into Pf_filldtl(table_no,i_index,p_u_no,p_no,dept_no,c_p_no,in_price,pf_price,sale_number,send_number,note,whole_num,single_num) select * from md_Pf_filldtl
if (@@error<>0) goto L_wError
end
--子条码明细表
set @ls_tb = 't_sheet_item'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'pf_fillmst) b ' +@ls_where +" and a.sheet_no = b.table_no and a.sheet_type='pf_fillmst'"
execute(@sql)
if (@@error<>0) goto L_wError
if exists(select * from MD_t_sheet_item)
--从中间数据表插入到目的表
if exists(select * from MD_fill_dtl)
begin
delete from t_sheet_item where sheet_no in(select sheet_no from md_t_s
heet_item)
if (@@error<>0) goto L_wError
insert into t_sheet_item select * from md_t_sheet_item
if (@@error<>0) goto L_wError
end
--批发付款单主表
set @ls_tb = 'Pf_paymst'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +@ls_where
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_pf_paymst)
begin
if @ls_type <> '1'
Truncate table Pf_paymst
ELSE
delete from Pf_paymst where table_no in(select table_no from md_Pf_paymst)
if (@@error<>0) goto L_wError
insert into Pf_paymst select * from md_Pf_paymst
if (@@error<>0) goto L_wError
end
--明细表
set @ls_tb = 'Pf_paydtl'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.table_no,a.i_index,a.summary,a.old_table_no,a.make_date,a.sale_amt,a.payed_amt,a.pay_amt,a.note into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'Pf_paymst) b ' +@ls_where +' and a.table_no = b.table_no'
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_pf_paydtl)
begin
if @ls_type <> '1'
Truncate table Pf_paydtl
ELSE
delete from Pf_paydtl where table_no in(select table_no from md_Pf_paydtl)
if (@@error<>0) goto L_wError
insert into Pf_paydtl(table_no,i_index,summary,old_table_no,make_date,sale_amt,payed_amt,pay_amt,note) select * from md_Pf_paydtl
if (@@error<>0) goto L_wError
end
--销售单主表
set @ls_tb = 'Pf_salemst'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +@ls_where
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_pf_salemst)
begin
if @ls_type <> '1'
Truncate table Pf_salemst
ELSE
delete from Pf_salemst where table_no in(select table_no from md_Pf_salemst)
if (@@error<>0) goto L_wError
insert into Pf_salemst select * from md_Pf_salemst
if (@@error<>0) goto L_wError
end
--明细表
set @ls_tb = 'Pf_saledtl'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.table_no,a.i_index,a.p_u_no,a.p_no,a.dept_no,a.c_p_no,a.in_price,a.pf_price,a.sale_number,a.note,a.whole_num,a.single_num into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'Pf_salemst) b ' +@ls_where +' and a.table_no = b.table_no'
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_pf_saledtl)
begin
if @ls_type <> '1'
Truncate table Pf_saledtl
ELSE
delete from Pf_saledtl where table_no in(select table_no from md_Pf_saledtl)
if (@@error<>0) goto L_wError
insert into Pf_saledtl(table_no,i_index,p_u_no,p_no,dept_no,c_p_no,in_price,pf_price,sale_number,note,whole_num,single_num) select table_no,i_index,p_u_no,p_no,dept_no,c_p_no,in_price,pf_price,sale_number,note,whole_num,singl
e_num from md_Pf_saledtl
if (@@error<>0) goto L_wError
end
--子条码明细表
set @ls_tb = 't_sheet_item'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'pf_salemst) b ' +@ls_where +" and a.sheet_no = b.table_no and a.sheet_type='pf_salemst'"
execute(@sql)
if (@@error<>0) goto L_wError
if exists(select * from MD_t_sheet_item)
--从中间数据表插入到目的表
if exists(select * from MD_fill_dtl)
begin
delete from t_sheet_item where sheet_no in(select sheet_no from md_t_sheet_item)
if (@@error<>0) goto L_wError
insert into t_sheet_item select * from md_t_sheet_item
if (@@error<>0) goto L_wError
end
--退货单主表
set @ls_tb = 'Pf_srtnmst'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +@ls_where
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_pf_srtnmst)
begin
if @ls_type <> '1'
Truncate table Pf_srtnmst
ELSE
delete from Pf_srtnmst where table_no in(select table_no from md_Pf_srtnmst)
if (@@error<>0) goto L_wError
insert into Pf_srtnmst select * from md_Pf_srtnmst
if (@@error<>0) goto L_wError
end
--明细表
set @ls_tb = 'Pf_srtndtl'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.table_no,a.i_index,a.p_u_no,a.p_no,a.dept_no,a.c_p_no,a.in_price,a.pf_price,a.sale_number,a.note into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'Pf_srtnmst) b ' +@ls_where +' and a.table_no = b.table_no'
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_pf_srtndtl)
begin
if @ls_type <> '1'
Truncate table Pf_srtndtl
ELSE
delete from Pf_srtndtl where table_no in(select table_no from md_Pf_srtndtl)
if (@@error<>0) goto L_wError
insert into Pf_srtndtl(table_no,i_index,p_u_no,p_no,dept_no,c_p_no,in_price,pf_price,sale_number,note) select * from md_Pf_srtndtl
if (@@error<>0) goto L_wError
end
--子条码明细表
set @ls_tb = 't_sheet_item'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'pf_srtnmst) b ' +@ls_where +" and a.sheet_no = b.table_no and a.sheet_type='pf_srtnmst'"
execute(@sql)
if (@@error<>0) goto L_wError
if exists(select * from MD_t_sheet_item)
--从中间数据表插入到目的表
if exists(select * from MD_fill_dtl)
begin
delete from t_sheet_item where sheet_no in(select sheet_no from md_t_sheet_item)
if (@@error<>0) goto L_wError
insert into t_sheet_item select * from md_t_sheet_item
if (@@error<>0) goto L_wError
end
--报价单主表
set @ls_tb = 'Pf_quotemst'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +@ls_where
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_pf_quotemst)
begin
if @ls_type <> '1'
Truncate table Pf_quotemst
ELSE
delete from Pf_quotemst where table_no in(select table_no from md_Pf_quotemst)
if (@@error<>0) goto L_wError
insert into Pf_quotemst select * from md_Pf_quotemst
if (@@error<>0) goto L_wError
end
--明细表
set @ls_tb = 'Pf_quotedtl'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.table_no,a.i_index,a.g_no,a.p_u_no,a.p_no,a.in_price,a.sale_price,a.note into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'Pf_quotemst) b ' +@ls_where +' and a.table_no = b.table_no'
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_pf_quotedtl)
begin
if @ls_type <> '1'
Truncate table Pf_quotedtl
ELSE
delete from Pf_quotedtl where table_no in(select table_no from md_Pf_quotedtl)
if (@@error<>0) goto L_wError
insert into Pf_quotedtl(table_no,i_index,g_no,p_u_no,p_no,in_price,sale_price,note) select * from md_Pf_quotedtl
if (@@error<>0) goto L_wError
end
--扣款单主表
set @ls_tb = 'Pfdeduct_head'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +@ls_where
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_pfdeduct_head)
begin
if @ls_type <> '1'
Truncate table Pfdeduct_head
ELSE
delete from Pfdeduct_head where table_no in(select table_no from md_Pfdeduct_head)
if (@@error<>0) goto L_wError
insert into Pfdeduct_head select * from md_Pfdeduct_head
if (@@error<>0) goto L_wError
end
--明细表
set @ls_tb = 'Pfdeduct_dtl'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as +','+@ls_from2+ 'Pfdeduct_head) b ' +@ls_where +' and a.table_no = b.table_no'
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
if exists(select * from MD_pfdeduct_dtl)
begin
if @ls_type <> '1'
Truncate table Pfdeduct_dtl
ELSE
delete from Pfdeduct_dtl where table_no in(select table_no from md_Pfdeduct_dtl)
if (@@error<>0) goto L_wError
insert into Pfdeduct_dtl select * from md_Pfdeduct_dtl
if (@@error<>0) goto L_wError
end
--客户主表
set @ls_tb = 'Pf_clientmst'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.* into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
Truncate table Pf_clientmst
if (@@error<>0) goto L_wError
insert into Pf_clientmst select * from md_Pf_clientmst
if (@@error<>0) goto L_wError
--明细表
set @ls_tb = 'Pf_clientdtl'
set @ls_midtb = 'MD_' + @ls_tb
--先删除中间表
if objectproperty(object_id(@ls_midtb),'istable') = 1
begin
set @sql = ' drop table ' + @ls_midtb
execute(@sql)
if (@@error<>0) goto L_wError
end
--从远程数据库中插入数据到中间表
set @sql = ' select a.c_no,a.p_u_no,a.p_no,a.c_p_no,a.pf_price,a.sale_price,a.sale_number,a.last_indate,a.high_price,a.low_price,a.enable into '+ @ls_midTb + @ls_from + @ls_tb + @ls_as
execute(@sql)
if (@@error<>0) goto L_wError
--从中间数据表插入到目的表
Truncate table Pf_clientdtl
if (@@error<>0) goto L_wError
insert into Pf_clientdtl(c_no,p_u_no,p_no,c_p_no,pf_price,sale_price,sale_number,last_indate,high_price,low_price,enable) select * from md_Pf_clientdtl
if (@@error<>0) goto L_wError
end
----------------------------------------------------------------------------
--L_wOK:
-- insert into sys_RS_Log(S_ShopNo,T_ShopNo,Trans_Date,Trans_Dtl,Trans_Tb,RS_Flag,S_Flag) values(@ls_sNo, @ls_MyNo,getdate(),'接收OK', @ls_tb, '1', '1')
-- goto L_COMMIT
L_COMMIT:
--commit transaction
set @iret = 0
return @iret
L_ROLLBACK:
--rollback transaction
set @iret = -1
return @iret
L_sEXIT:
set @iret = 0
return @iret
L_wError:
--set @ls_re_dtl = '接收失败'
--insert into sys_RS_Log(S_ShopNo, T_ShopNo, Trans_Date, Trans_Dtl, Trans_Tb, RS_Flag, S_Flag)
--values(@ls_sNo, @ls_MyNo,getdate(),@ls_re_dtl, @ls_tb, '1', '0')
goto L_fEXIT
L_fEXIT:
set @iret = -1
return @iret