IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('ImportTxtStoki')))
THEN Drop procedure ImportTxtStoki
END IF

GO

create function DBA.ImportTxtStoki(in ImpSession integer,in FileName varchar(250))
returns char(250)
// Importira Stoki s "bvrStkImportMode" ravno na (vzeto ot) eksporta.
/* Expects in table "ImportData":
CASE 1: D1 >= 0 (LavelBack) - Opisanie na Stoka (vkluchva imena, miarka, kod, pokup.cena):
D0=StkImportMode=bvrStkImportMode,
D1=S.LavelBack,
D2=ImeSGrupa=SG.Ime,
D3=S.Kod,
D4=S.Ime,
D5=S.ShortName,
D6=S.FakturaName,
D7=Miarka=M1.MIme,
D8=Zamest=Stoka Zamestitel,
D9=S.DefaultCena,
D10=S.HaveSerNo,
D11=KodValuta=Valuti.Kod,
D12=S.Garantee,
D13=ImeSyzdatel=Users.UserName,
D14=S.Domestic,
D15=S.Kategoria
D16=S.Nadcenka
D17=PoslData (DD.MM.YYYY HH.NN)
D18=ZamestGrupa
D19=@Expires,
D20=@VATBuy,
D21=@VATSell,
D22=@CreateDate,
D23=@Description
CASE 2: D1 = -2 - Opakovki (ot tabl PravaMerki):
D0=StkImportMode=bvrStkImportMode,
D1=Info=-2,
D2=ImeSGrupa=SG.Ime,
D3=Stoka=if bvrStkImportMode=1 then STIN.Kod else STIN.Ime endif,
D4=ImeOpakovka=Merki.MIme,
D5=S.Domestic
CASE 3: D1 = -3 - Razhodni normi na stoki (vkluchva danni ot tabl. Razhod):
D0 = StkImportMode,
D1 = Info=-3,
D2 = StokaOut,
D3 = OutKol = Stoki.Razhod,
D4 = StokaIn,
D5 = Kol,
D6 = NetoKol,
D7 = RazhodMiarka - Miarka na vlojenata stoka,
D8 = RazhodOp - proizvodstwena opakowka,
D9 = SamoCeli,
D10 = ImeSGrupaOut,
D11 = ImeSGrupaIn
D12 = StokaOut.Domestic
*/
begin atomic
  declare @SGN integer;
  declare @PMN integer;
  declare @OldN integer;
  declare @NewN integer;
  declare @Miarka integer;
  declare @OldMiarka integer;
  declare @Zamestitel integer;
  declare @Valuta integer;
  declare MaxCnt integer;
  declare @Row integer;
  declare @NRazhodOp integer;
  declare @LastOut integer;
  declare @Finder char(50);
  declare @oData char(30);
  declare @OldBorn tinyint;
  declare i integer;
  declare L integer;
  declare @NStokaOut integer;
  declare @NStokaIn integer;
  declare @Fira real;
  declare @@VATBuy char(1);
  declare @@VATSell char(1);
  declare @@CreateDate date;
  declare @PartniorDostN integer;
  //
  declare Import_Error exception for sqlstate value '40W02';
  // Terminated by user -- transaction rolled back
  //
  message '-- ' type info to console;
  message '-- ImportStoki - processing ',FileName,'...' type info to console;
  select Count(*) into MaxCnt from ImportData where "Session" = ImpSession;
  message '-- Rows to process: ',MaxCnt type info to console;
  //call SetStkImportMode();
  //
  /*if bvrStkImportMode=0 then
  // import po ime e vyzmojen samo ako imenata sa unikalni:
  if exists(select COUNT(Ime) from Stoki where Miarka<>0 group by Ime having COUNT(Ime)>1) then
  set bvrErrorMessage='[?????? ?????] ?? ???? ?? ?? ????????? ?? ??? ?????? ??????? ?? ????????? ????? ?? ?????.';
  signal Import_Error
  end if
  end if;
  */
  set @@VATBuy=GetRegistry('Tax.VAT.Buy.Default','_');
  set @@VATSell=GetRegistry('Tax.VAT.Sell.Default','_');
  set @@CreateDate=current date;
  set @Row=0;
  message '---- Import Standartni svoistva...' type info to console;
  F1: for F1 as Curs1 scroll cursor for
    select distinct D0 as @StkImportMode,
      D1 as @LavelBack,
      D2 as @ImeSGrupa,
      D3 as @Kod,
      D4 as @ImeStoka,
      D5 as @ShortName,
      D6 as @FakturaName,
      D7 as @ImeMiarka,
      D8 as @ZamestIme,
      D9 as @DefaultCena,
      D10 as @HaveSerNo,
      D11 as @KodValuta,
      D12 as @Garantee,
      D13 as @ImeSyzdatel,
      D15 as @Kategoria,
      D16 as @Nadcenka,
      D17 as @PoslData,
      D18 as @ZamestGrupa,
      D19 as @Expires,
      D20 as @VATBuy,
      D21 as @VATSell,
      D22 as @CreateDate,
      D23 as @Description,
      -- Jorkata za Vuvejdane na DostKod i DostIme
      D24 as @ImeStokaDost,
      D25 as @KodStokaDost,
      D26 as @PartniorDost from
      -- Jorkata END na DostKod i DostIme
      ImportData where "Session" = ImpSession order by
      D2 asc,D4 asc do
    //
    if @LavelBack is null or @LavelBack = '' then
      //LANG//
      set bvrErrorMessage=Mess(758);
      signal Import_Error
    end if;
    if @StkImportMode is null or @StkImportMode = '' then
      call SetStkImportMode();
      set @StkImportMode=bvrStkImportMode
    end if;
    if @ImeStoka is null or @ImeStoka = '' then set @ImeStoka=@Kod
    end if;
    if convert(integer,@LavelBack) >= 0 and @ImeStoka is not null and @ImeStoka <> '' then
      set @Row=@Row+1;
      if Mod(@Row,10) = 0 then
        message '------ Row ',@Row,' of ',MaxCnt type info to console
      end if;
      //message '------ Kod=',@Kod,', Ime=',@ImeStoka;
      if @ShortName is null then set @ShortName=''
      end if;
      if @FakturaName is null then set @FakturaName=''
      end if;
      if "Left"(@ImeStoka,1) = '"' then set @ImeStoka=TrimDblQuotes(@ImeStoka)
      end if;
      if "Left"(@ShortName,1) = '"' then set @ShortName=TrimDblQuotes(@ShortName)
      end if;
      if "Left"(@FakturaName,1) = '"' then set @FakturaName=TrimDblQuotes(@FakturaName)
      end if;
      set bvrStkImportMode=@StkImportMode;
      if bvrStkImportMode = 1 then set @Finder=@Kod
      else set @Finder=@ImeStoka
      end if;
      if @Nadcenka is null or @Nadcenka = '' then set @Nadcenka='100'
      end if;
      if @DefaultCena is null or @DefaultCena = '' then set @DefaultCena='0'
      end if;
      if @PoslData is null or @PoslData = '' then set @PoslData=current timestamp
      end if;
      set @OldN=GetNStoka(@Finder,0,@ImeSGrupa,'');
      //K+
      if isnull((select Domestic from Stoki where N = @OldN),0) <> 1 then // ako e null ili 0
        set @SGN=GetStkGroup(@ImeSGrupa)
      else
        select GS into @SGN from stoki where n = @OldN
      end if; //K-
      set @Miarka=GetNOpak(@ImeMiarka,1);
      //(temporary) set @Zamestitel=GetNStoka(@ZamestIme,0,@ZamestGrupa,'');
      set @Zamestitel=0;
      if @Zamestitel is null then set @Zamestitel=0
      end if;
      set @Valuta=GetNValuta(@KodValuta,0);
      if @Valuta is null then set @Valuta=0
      end if;
      //message '------ OldN=',@OldN;
      //message '------ Grupa=',@ImeSGrupa,':',@SGN,', Ime=',@ImeStoka;
      if @Expires is null then set @Expires=0
      end if;
      if @VATBuy is null then set @VATBuy=@@VATBuy
      end if;
      if @VATSell is null then set @VATSell=@@VATSell
      end if;
      if @CreateDate is null then set @CreateDate=@@CreateDate
      end if;
      if @OldN is null then
        //message '------ Nova stoka v grupa "',@ImeSGrupa,'"';
        set @NewN=GetCounter(5);
        while exists(select N from Stoki where Kod = @Kod) loop
          set @Kod=@Kod || '*'
        end loop;
        insert into Stoki( N,GS,Kod,Ime,ShortName,FakturaName,
          LavelBack,Miarka,Zamestitel,Miarka2,RazhodMiarka,
          DefaultCena,HaveSerNo,Valuta,Garantee,Kategoria,
          Nadcenka,PoslData,Description,Expires,
          VATBuy,VATSell,CreateDate) values( 
          @NewN,@SGN,@Kod,@ImeStoka,@ShortName,@FakturaName,
          @LavelBack,@Miarka,@Zamestitel,@Miarka,@Miarka,
          @DefaultCena,@HaveSerNo,@Valuta,@Garantee,@Kategoria,
          @Nadcenka,@PoslData,@Description,@Expires,
          @VATBuy,@VATSell,@CreateDate) ;
        update Stoki set Domestic = 0,Syzdatel = GetNUser(@ImeSyzdatel,1) where
          N = @NewN
      else
        select Miarka,Domestic into @OldMiarka,
          @OldBorn from Stoki where Stoki.N = @OldN;
        //message '-- Stara stoka - N=',@OldN,' m1=',@Miarka,' m2=',@OldMiarka;
        set @NewN=@OldN;
        if @OldBorn <> 0 then
          //LANG//
          message '---- !!! Opit za promiana na zashtitena stoka: ',@ImeStoka type info to console
        else // @OldBorn = 0 (can change the data)
          if @OldMiarka <> @Miarka then
            //LANG//
            set bvrErrorMessage=Mess(757) || '"' || @ImeStoka || '": ' || @ImeMiarka;
            message '>> ',bvrErrorMessage type info to console;
            signal Import_Error
          end if;
          while exists(select N from Stoki where Kod = @Kod and N <> @OldN) loop
            set @Kod=@Kod || '*'
          end loop;
          //message '-- update...';
          update Stoki set GS = @SGN,Kod = @Kod,Ime = @ImeStoka,ShortName = @ShortName,FakturaName = @FakturaName,
            LavelBack = @LavelBack,Zamestitel = @Zamestitel,RazhodMiarka = @Miarka,
            DefaultCena = @DefaultCena,HaveSerNo = @HaveSerNo,Valuta = @Valuta,Garantee = @Garantee,
            Kategoria = @Kategoria,Nadcenka = @Nadcenka,PoslData = @PoslData,Description = @Description,
            Expires = @Expires,VATBuy = @VATBuy,VATSell = @VATSell,CreateDate = @CreateDate where
            N = @OldN
        end if
      end if; // if OldN is null
      set @PMN=0;
      select PravaMerkiN into @PMN from PravaMerki where Who = @NewN and ForWhat = @Miarka;
      if @PMN = 0 then
        insert into PravaMerki( PravamerkiN,Who,ForWhat) values( 
          GetCounter(25),@NewN,@Miarka) ;
        update Stoki set PoslData = @PoslData where N = @NewN
      end if;
      call DaiPravaStk(1,@NewN);
      call DaiPravaStk(bvrUserN,@NewN);
      -- Jorkata za Vuvejdane na DostKod i DostIme
      if @PartniorDost <> '' then
        set @PartniorDostN=GetNPartnior(@PartniorDost,1);
        delete from DostKod where StokaNomer = @NewN and dost = @PartniorDostN;
        insert into DostKod( StokaNomer,Dost,Kod,Ime) values( @NewN,@PartniorDostN,@KodStokaDost,@ImeStokaDost) 
      end if
    end if; -- Jorkata End DostKod i DostIme
    // @ImeStoka<>''
    set L=L end for;
  //
  set @Row=0;
  message '---- Import Opakovki...' type info to console;
  F1: for F2 as Curs2 scroll cursor for
    select D0 as @StkImportMode,
      D2 as @Stoka,
      D3 as @ImeMiarka,
      D4 as @TegloNeto,
      D5 as @TegloBruto,
      D6 as @Obem,
      D7 as @ImeSGrupa from
      ImportData where "Session" = ImpSession and D1 = '-2' order by
      D2 asc,D3 asc do
    //
    set @Row=@Row+1;
    if Mod(@Row,10) = 0 then
      message '------ Row ',@Row,' of ',MaxCnt type info to console
    end if;
    set bvrStkImportMode=@StkImportMode;
    set @OldN=GetNStoka(@Stoka,1,@ImeSGrupa,@ImeMiarka);
    select DateFormat(PoslData,'DD.MM.YYYY HH.NN') into @oData from Stoki where N = @OldN;
    set @Miarka=GetNOpak(@ImeMiarka,1);
    set @PMN=0;
    select PravaMerkiN into @PMN from PravaMerki where Who = @OldN and ForWhat = @Miarka;
    if @PMN = 0 then
      insert into PravaMerki( PravamerkiN,Who,ForWhat) values( 
        GetCounter(25),@OldN,@Miarka) ;
      update Stoki set PoslData = @oData where N = @OldN
    end if;
    set L=L end for;
  //
  message '---- Import Razhodni normi...' type info to console;
  set @Row=0;
  set @LastOut=0;
  F1: for F3 as Curs3 scroll cursor for
    select D0 as @StkImportMode,
      D2 as @StokaOut,
      D3 as @OutKol,
      D4 as @StokaIn,
      D5 as @Kol,
      D6 as @NetoKol,
      D7 as @RazhodMiarka,
      D8 as @RazhodOp,
      D9 as @SamoCeli,
      D10 as @ImeSGrupaOut,
      D11 as @ImeSGrupaIn from
      ImportData where "Session" = ImpSession and D1 = '-3' order by
      D10 asc,D2 asc,D11 asc,D4 asc do
    //
    set @Row=@Row+1;
    if Mod(@Row,10) = 0 then
      message '------ Row ',@Row,' of ',MaxCnt type info to console
    end if;
    set bvrStkImportMode=@StkImportMode;
    set @NStokaOut=GetNStoka(@StokaOut,1,@ImeSGrupaOut,'');
    set @NStokaIn=GetNStoka(@StokaIn,1,@ImeSGrupaIn,@RazhodMiarka);
    if @NStokaOut <> @NStokaIn then
      select DateFormat(PoslData,'DD.MM.YYYY HH.NN') into @oData from Stoki where N = @NStokaOut;
      //set @Miarka=GetNOpak(@RazhodMiarka,1);
      select Miarka into @Miarka from Stoki where N = @NStokaIn;
      if @NetoKol is null or Trim(@NetoKol) = '' then set @NetoKol=@Kol
      end if;
      if @RazhodOp is null or Trim(@RazhodOp) = '' then
        set @NRazhodOp=0
      else
        set @NRazhodOp=GetNOpak(@RazhodOp,1)
      end if;
      if @SamoCeli is null or Trim(@SamoCeli) = '' then set @SamoCeli=0
      end if;
      if @LastOut <> @NStokaOut then
        delete from Razhod where OutStoka = @NStokaOut
      end if;
      if @Kol <> 0 then set @Fira=((@Kol-@NetoKol)/@Kol)*100
      else set @Fira=0
      end if;
      insert into Razhod( N,OutStoka,InStoka,Kol,Miarka,NetoKol,Fira,Opakovka,SamoCeliOp) values( 
        GetCounter(7),@NStokaOut,@NStokaIn,@Kol,@Miarka,@NetoKol,@Fira,@NRazhodOp,@SamoCeli) ;
      update Stoki set Razhod = @OutKol,PoslData = @oData where N = @NStokaOut
    end if;
    set @LastOut=@NStokaOut;
    set L=L end for;
  delete from ImportData where "Session" = ImpSession;
  //call SetStkImportMode();
  message '-- ImportStoki DONE.' type info to console;
  return('')
end
//v.1 [00.11.17]
//v.2 [25 apr 2002]-peter - Mess(...)...
//v.3 [08.11.2004] - Ako stokata e domestik da ne sazdava grupata ot fajla
//v.4 [12.03.2006] - Jorkata za Vuvejdane na DostKod i DostIme
//v.5 [23.04.2008] - gnikolov Set bvrStkImportMode преместен след проверка