SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER procedure dbo.up_Myfileimport
@simportfilename nvarchar(50),
@simportfilepath nvarchar(100),
@dimportInvdate datetime,
@dimportfiledate datetime,
@sformatfilepath nvarchar(100),
@dimportdate datetime,
@nisheader integer
as
-- imports files into the mirror tables
declare @ssql nvarchar(1000),
@stargettable nvarchar(50),
@nerrorno int,
@nErrLayer int,
@scomment varchar(300),
@nimportid int,
@nBulkErrorNo int,
@sstoreno char(4),
@dinvdate datetime
set @nerrorno = 0
Set @nErrLayer = 0
set @scomment = ""
set @sstoreno = substring(@simportfilename, Len(@simportfilename)-7, 4)
-- has the import file already been imported?
--New
if exists(select *
from importlog
where filename = @simportfilename
and Invdate =@dimportInvdate
and resultcode <> -2)
begin
-- get the id and comment
select @nimportid = id,
@scomment = comment
from importlog
where filename = @simportfilename
and Invdate =@dimportInvdate
-- update the record
update importlog
set comment = @scomment+ ". Attempted re-import on "+
cast(@dimportdate as varchar(20))
from importlog
where id = @nimportid
--print "Attempted re-import of import item "+
-- cast(@nimportid as varchar(20))
--ok well it was imported once, but see if something failed???? see if any records exists for that
-- store for that day, if any exist just return normaly with a -1 otherwise, if no records
-- exists re-import the data
if exists(Select * from invoices where storeno = @sstoreno and trandate = @dimportInvdate)
Begin
--Data already exists so return -1 do not import the record
return 9999
End
Else
Begin
--No data for this store was found, so try to re-import the data
update importlog
set comment = @scomment+ ". Attempting to re-import on "+
cast(@dimportdate as varchar(20))
,resultcode = -2, distresult=-1
from importlog
where id = @nimportid
select @nerrorno = @@error, @scomment = "Attempting import"
End
end
else
begin
exec up_setresults @simportfilename, @dimportfiledate, null,
@dimportdate, null, -2, "Attempting import..."
select @nerrorno = @@error, @scomment = "Attempting import"
set @nimportid = @@identity
end
set nocount on
begin tran
if @nerrorno = 0
begin -- begin layer 1 Truncating tables and bulk insert
Set @nerrlayer = 1
if @nisheader = 1
begin
truncate table tmpmirrorhdr
set @stargettable = "mirrorhdr"
set @sformatfilepath = @sformatfilepath + "hdrformat.sql"
end
else
begin
truncate table tmpmirrordtl
set @stargettable = "mirrordtl"
set @sformatfilepath = @sformatfilepath + "dtlformat.sql"
end
-- the bulk insert
set @ssql = "Bulk insert tmp"+ @stargettable +
" from '" + @simportfilepath + @simportfilename + "' " +
" with (formatfile = '" + @sformatfilepath + "')"
--print @ssql
exec sp_executesql @ssql
-- layered error tracking to be safe...
select @nerrorno = @@error, @scomment = "Bulk insert command"
end
if @nerrorno = 0
begin
-- -- begin layer 2 move the data to the appropriate table
Set @nerrlayer = 2
if @nisheader = 1
begin
-- get the internal date
select @dinvdate = convert(datetime,trandate) from tmpmirrorhdr
-- correct the store number
update tmpmirrorhdr
set tostoreno = storeno
where storeno<>@sstoreno
update tmpmirrorhdr
set importid= @nimportid,
storeno = @sstoreno
select @nerrorno = @@error, @scomment = "Update mirror"
if @nerrorno=0
begin
insert mirrorhdr
select * from tmpmirrorhdr where Type <= '6'
select @nerrorno = @@error, @scomment = "Insert tmpMirrorHDR"
-- get the rejects
if @nerrorno<>0
insert rejecthdr
select * from tmpmirrorhdr
end
end
else
begin
select @dinvdate = convert(datetime,trandate) from tmpmirrordtl
update tmpmirrordtl
set todiv = div,
todept = dept
where div+dept<>@sstoreno
update tmpmirrordtl
set importid = @nimportid,
div = substring(@sstoreno, 1, 2),
dept = substring(@sstoreno, 3, 2)
-- correct the store number
if @nerrorno=0
begin
insert mirrordtl
select * from tmpmirrordtl where (Type <= '6' )
select @nerrorno = @@error, @scomment = "Insert tmpMirrorDTL"
-- get the rejects
if @nerrorno<>0
insert rejectdtl
select * from tmpmirrordtl
end
end
-- check for dupes
if @nerrorno <> 0
insert errorlog(errorno, comments)
select @nerrorno, "Import error"
if @nerrorno = 2627
begin
if @nisheader = 1
insert errorlog (errorno, comments)
select 2627, "Duplicate: "+@simportfilename+
", Invoice: " + t1.transno
from tmpmirrorhdr t1
inner join mirrorhdr h1
on t1.storeno = h1.storeno
and t1.transno = h1.transno
else
insert errorlog (errorno, comments)
select 2627, "Duplicate: "+@simportfilename+
", Invoice: " + t1.transno+
", Line: "+t1.invlineno
from tmpmirrordtl t1
inner join mirrordtl d1
on t1.div = d1.div
and t1.dept = d1.dept
and t1.transno = d1.transno
and t1.invlineno = d1.invlineno
end
end
if @nerrorno = 0
begin -- begin layer 3 Completing Results
Set @nerrlayer = 3
set @scomment="Completed on "+cast(getdate() as varchar(30))+"."
exec up_setresults @simportfilename, @dimportfiledate, @dimportInvdate,
@dimportdate,
@dimportdate, 1, @scomment
set @nerrorno = @@error
if @nerrorno <> 0
begin
rollback tran
exec up_errorlog @dlogdate = getdate,
@nerrorno = @nerrorno,
@nerrorlevel =@nerrlayer,
@sComments = @scomment
return 333
end
end
else
begin
set @scomment = "Error number " + cast(@nerrorno as char(10))
exec up_setresults @simportfilename, @dimportfiledate, @dimportInvdate,
@dimportdate,
null, @nerrorno, @scomment
set @nerrorno = @@error
if @nerrorno <> 0
begin
rollback tran
exec up_errorlog @dlogdate = getdate,
@nerrorno = @nerrorno,
@nerrorlevel =@nerrlayer,
@sComments = @scomment
return 666
end
end
commit tran
return 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
