Версия 10:16, 30 марта 2017

TAKE_REFLIS_MT - это программа из справочника R266

Прием справочников с учетом MODIFYTIME

Текст программы

local ss, ii, _, xx, msg, rc, aa, sql, fld, i, t, conn, path, mt, data, time, kk, td, rcd, __, cc, tu
 
path := GetPrm( 'SHP_PATH', 'SHOPOPER' )
mt := atr( GetPrm( 'MODIFYTIME', 'SHOPOPER' ) )

if !Empty( mt )
  data := SqlDate( CtoD( Left( mt, 10 ) ) - 1 )
  time := SUBSTR( mt, 12 )
  mt := data + ' ' + time
endif

sql := [ Select PNAME From R597 ]
fld := { 'PNAME' }
aa := SqlToArr( sql, fld, ReflisPath )

if !IsEmpty( aa )
  aa := PrepareArr( aa )
endif

for i := 1 to len( aa )
  sql := [ Select FLDNAME, FLDTYPE, FLDLEN, FLDDEC From LKLF Where KINDDOC = '] + aa[i] + [' and ;
    FLDTYPE <> 'E' and FLDTYPE <> 'B' and FLDNAME not in ( 'MODIFYTIME', 'AUTO_ID', 'SITTINGNUM' ) ]
  fld := { 'FLDNAME', 'FLDTYPE', 'FLDLEN', 'FLDDEC' }
  ss := SqlToArr( sql, fld, ReflisPath )
  fld := 
  if !IsEmpty( ss )
    // for ii := 1 to len( ss )
    //   fld := fld + ss[ii,1] + ',' + ss[ii,2] + ',' + ss[ii,3] + ',' + ss[ii,4] + '; '
    // end
    try
      WaitMsg( 'Перенос справочника ' + aa[i], msg )
      // _ := CreateTmpFile( fld, '_', ~IsFreeDel := true )
      // __ := CreateTmpFile( fld, '__', ~IsFreeDel := true )
      sql := [ Select CONDITION From R597 Where PNAME = '] + aa[i] + [' ]
      cc := SqlToArr( sql,, ReflisPath )
      conn := CreateConnection( path, 'admuser', "ora=1" )
      if Empty( mt )
        // очищаем справочники
        sql := [ Delete From ] + aa[i] + [ Where 'SYSTEM.' = 'SYSTEM.' ]
        SimpleSql( sql, ReflisPath )
        if !Empty( cc )
          sql := [ Select * From ] + aa[i] + [ Where ] + cc
        else
          sql := [ Select * From ] + aa[i]
        endif
      else
        if !Empty( cc )
          sql := [ Select * From ] + aa[i] + [ Where Convert( MODIFYTIME, sql_char ) > '] + mt + [' and ;
            ] + cc
        else
          sql := [ Select * From ] + aa[i] + [ Where Convert( MODIFYTIME, sql_char ) > '] + mt + [' ]
        endif
      endif
      t := SelectSql( sql, conn )
      tu := ( t )->( DbStruct() )

      if !IsEmpty( tu )
        tu := PrepareArr( tu )
      endif
      for ii := 1 to len( ss )
        if Ascan( tu, ss[ii,1] ) = 0
          ss[ii,1] := 'TT'
        endif
      next

      for ii := 1 to len( ss )
        if ss[ii,1] <> 'TT'
          fld := fld + ss[ii,1] + ',' + ss[ii,2] + ',' + ss[ii,3] + ',' + ss[ii,4] + '; '
        endif
      end
      _ := CreateTmpFile( fld, '_', ~IsFreeDel := true )
      __ := CreateTmpFile( fld, '__', ~IsFreeDel := true )

      rc := ( t )->( IsSingle() )
      WaitMsgB( 'Заполнение справочника ' + aa[i], msg,, rc )
      ( t )->( DbGoTop() )
      while !( t )->( eof() )
        ( _ )->( DbAppend() )
        for ii := 1 to len( ss )
          if ss[ii,1] <> 'TT'
            xx := [ ( _ )->] + ss[ii,1] + [ := ( t )->] + ss[ii,1]
            &xx
          endif
        end
        StepMsg( msg, 0, false, true, true )
        ( t )->( DbSkip() )
      end
      if !Empty( mt )
        // удаленные записи
        sql := [ Select * From Delete\] + aa[i] + [ Where Convert( MODIFYTIME, sql_char ) > '] + mt + [' ]
        td := SelectSql( sql, conn )
        rcd := ( td )->( IsSingle() )
        if !Empty( rcd )
          ( td )->( DbGoTop() )
          while !( td )->( eof() )
            ( __ )->( DbAppend() )
            for ii := 1 to len( ss )
              xx := [ ( __ )->] + ss[ii,1] + [ := ( td )->] + ss[ii,1]
              &xx
            end
            ( td )->( DbSkip() )
          end
          if aa[i] == 'R14'
            // r14
            sql := [ Update R14 Set R14.COMMENT = 'DEL' From R14 left outer join ] + TmpFilePath( __ ) + [ __ ON ;
              R14.KOD = __.KOD and R14.VAR_DATE = __.VAR_DATE and R14.NUM_P_LIST = __.NUM_P_LIST ;
              Where __.KOD is not null ]
            SimpleSql( sql, ReflisPath )
          elseif aa[i] == 'R473'
            // r473
            sql := [ Update R473 Set R473.COMMENT = 'DEL' From R473 left outer join ] + TmpFilePath( __ ) + [ __ ON ;
            R473.KOD = __.KOD and R473.VAR_DATE = __.VAR_DATE and R473.KOD_R20 = __.KOD_R20 ;
            Where __.KOD is not null ]
            SimpleSql( sql, ReflisPath )
          endif
        endif
      endif
      fld := 
      for ii := 1 to len( ss )
        if ss[ii,1] <> 'TT'
          if Empty( fld )
            fld := ["] + ss[ii,1] + ["]
          else
            fld := fld + [, "] + ss[ii,1] + ["]
          endif
        endif
      end
      if aa[i] == 'R14'
        // r14
        sql := [ Update R14 Set R14.COMMENT = 'DEL' From R14 left outer join ] + TmpFilePath( _ ) + [ _ ON ;
          R14.KOD = _.KOD and R14.VAR_DATE = _.VAR_DATE and R14.NUM_P_LIST = _.NUM_P_LIST ;
          Where _.KOD is not null ]
        SimpleSql( sql, ReflisPath )
        sql := [ Delete From R14 Where COMMENT = 'DEL' or VAR_DATE is null ]
        SimpleSql( sql, ReflisPath )
      elseif aa[i] == 'R473'
        // r473
        sql := [ Update R473 Set R473.COMMENT = 'DEL' From R473 left outer join  ] + TmpFilePath( _ ) + [ _ ON ;
          R473.KOD = _.KOD and R473.VAR_DATE = _.VAR_DATE and R473.KOD_R20 = _.KOD_R20 ;
          Where _.KOD is not null ]
        SimpleSql( sql, ReflisPath )
        sql := [ Delete From R473 Where COMMENT = 'DEL' or VAR_DATE is null ]
        SimpleSql( sql, ReflisPath )
      else
        // удаляем коды, которые принимаются повторно
        sql := [ Select KOD From ] + TmpFilePath( _ )
        kk := SqlToArr( sql, { 'KOD' } )
        if !IsEmpty( kk )
          kk := PrepareArr( kk )
          sql := [ Delete From ] + aa[i] + [ Where KOD in ( ] + ArrAsString( kk ) + [ ) and 'SYSTEM.' = 'SYSTEM.' ]
          SimpleSql( sql, ReflisPath )
        endif
      endif
      sql := [ Select * From ] + TmpFilePath( _ )

      sql := [ Insert into ] + aa[i] + [( ] + fld + [ ) ] + sql
      SimpleSql( sql, ReflisPath )
    finally
      CloseTable( _ )
      CloseTable( __ )
      FreeAndNil( conn )
      CloseTable( t )
      CloseTable( td )
      HideMsg( msg )
    end
  endif
next

PutPrm( 'MODIFYTIME', Date() + ' ' + Time(), 'SHOPOPER' )

// Select Convert( MODIFYTIME, sql_char ) From R14 Where Convert( MODIFYTIME, sql_char ) > '2017-02-21 11:56:19'
// sql := [ Select * From ] + aa[i] + [ Where Convert( MODIFYTIME, sql_char ) > '] + mt + [' ]

Смотрите также

Функции Delphi

Полезные функции