Cp1251
DOC
foreground = lightgray 
document_width = 750
document_height = 550
document_title = "Анализ потребности (дефицита)"

preloadscript="
($X
  (global.loading = 1)
  (global.id_head = 0)
  (global.id_active = -1)
  (global.id_group_active = -1)
  (global.num_sd = 'Новый')
  (global.visible = 'no')
  (global.sklad = '445, 465')
  (global.sklad_only = '465')
  (global.sklad_tseh = '-1')
  (global.search = '')
  (global.h = ($@hash))
  (global.h@put 'OK' 0)
  (global.h@put 'ID_ACTIVE' 999)
  (global.h@put 'NAME_ACTIVE' '')
  (global.h@put 'QUANTITY' 0)
  (global.time = '01.01.2000 00:00:00')
  (global.no_combo_action = 0)
  (global.show_perem = 1)
  (global.show_spis = 1)
  (global.show_zak = 1)
  (global.show_supplier = 1)
  ($ret '')
)
"
postloadscript = "($X

($g.grid1@allign)

#($g.last_update@retrieve)
#($debug 'last_update: ' + ($g.last_update@getValue 'LAST_UPDATE'))
#(global.d = ($@date ($g.last_update@getValue 'LAST_UPDATE')))
(global.no_combo_action = 1)

##########################  defun


($defun paint_plan<arg>
  ($X
    (i = 0)
    (sz = ($g.gridds03@size))
    ($while (i < sz)
     (
       ($g.gridds03@fastSetCurRow i)
       ($if (($g.gridds03@getValue 'analog') == 1)

         #($if (($g.gridds03@getValue 'analog_id') == 0)

           (
             (j = 0)
             ($while (j < 9)
               (
                 ($g.grid3@setcellbgcolor i j '#FFCCCC')
                 (j += 1)
               )
             )

         #  )
         # $else
         #   (
         #     (j = 0)
         #     ($while (j < 9)
         #       (
         #         ($g.grid3@setcellbgcolor i j 'WHITE')
         #         (j += 1)
         #       )
         #     )
         #   )


           )
         $else
           (
             (j = 0)
             ($while (j < 9)
               (
                 ($g.grid3@setcellbgcolor i j 'WHITE')
                 (j += 1)
               )
             )
           )
       )
       (i += 1)
     )
    )
    ($g.grid3@repaint)
    ($ret '')
  )
)

($defun repaint_grid1 <par1>
  ($X


    ($ret '')


    (sz = ($($g.grid1@getDatastore)@size))
    (i = 0)
    ($while (i < sz)
     (
       ($($g.grid1@getDatastore)@FastSetCurRow i)
       ($if (($try ($($g.grid1@getDatastore)@getValue 'max(util.get_last_date_mod(spc_spec.id_spc))') $catch ('NullException')(0)) == 0)
          (
            (j = 5)
            ($while (j < 6)
              (
                ($g.grid1@setcellbgcolor i j 'RED')
                (j += 1)
              )
            )
          )

         $else
          (
           ($debug ' repaint_grid1 row ' + ($i@int) + ' global.d: ' + global.d + ', last_mod: ' + ($($g.grid1@getDatastore)@getValue 'max(util.get_last_date_mod(spc_spec.id_spc))') )
           ($if (($global.d@after ($($g.grid1@getDatastore)@getValue 'max(util.get_last_date_mod(spc_spec.id_spc))')) == 1)
             (
               (j = 5)
               ($while (j < 6)
                 (
                   ($g.grid1@setcellbgcolor i j 'RED')
                   (j += 1)
                 )
               )
             )
            $else
             (
               (j = 5)
               ($while (j < 6)
                 (
                   ($g.grid1@setcellbgcolor i j 'WHITE')
                   (j += 1)
                 )
               )
             )
           )
         )
       )
       (i += 1)
     )
    )
    ($g.grid1@repaint)
    ($ret '')
  )
)

($defun refresh<arg>
  ($X

     ($g.grid3@retrieve)
     ($if (($($g.grid3@getDatastore)@size) > 0)
     	(
          ($g.grid1@retrieve)
          ($repaint_grid1 0)
          ($g.grid_plan@retrieve)
        )
     )
     #($if (($($g.grid3@getDatastore)@size) == 0)
     #   ($g.info@setValue 'В этом месяце нет утвержденного плана производства.')
     # $else
     #   ($g.info@setValue '')
     #)
     ($ret '')
   )
)

##########################  end defun

($g.ds_time@retrieve)
($debug '======= timimg: ' + ($($g.ds_time@getValue 'diff')@int))
($g.info@setValue 'Время выполнения отчета: ' + ($($g.ds_time@getValue 'diff')@int) + ' с.')

($paint_plan 0)


(global.loading = 0)

(global.timeout = '0')
(global.run_again = 1)
#($g.dbe@execute)
#($debug 'POSTLOADSCRIPT: DBExecutor result: ' + ($g.dbe@getResult))

#($debug 'g.gridds03: ' + g.gridds03)
#(global.gridds03 = ($g.gridds03@Concat))
#($debug 'global.gridds03: ' + global.gridds03)
#($debug 'global.gridds03 retrieve:')
#($global.gridds03@retrieve)

($repaint_grid1 0)

($g.self@calculate 'js:js_file:/docs/analiz_potr_menu.js')

($ret '')
)"

closescript = "($X
                 ($debug  '------------ closescript -------------')
                 (global.run_again = 0)
                 ($g.self@doAction 'execute exec merge into parameters p
                                                 using (select user username, \'show_spis\' parname, ' + global.show_spis + ' parvalue from dual
                                                         union all
                                                        select user username, \'show_perem\' parname, ' + global.show_perem + ' parvalue from dual
                                                         union all
                                                        select user username, \'show_zak\' parname, ' + global.show_zak + ' parvalue from dual
                                                         union all
                                                        select user username, \'show_supplier\' parname, ' + global.show_supplier + ' parvalue from dual
                                                       ) new_p
                                                    on (p.username = new_p.username and p.s = new_p.parname)
                                                 when matched then update set p.n = new_p.parvalue
                                                 when not matched then insert(id, username, s, n) values(parameters_sq.nextval, user, new_p.parname, new_p.parvalue)')
                 ($g.self@doAction 'execute exec commit')
                 ($ret '')
               )"

{dscollection
        alias = ds_collection
        initQuery = "select user from dual"
        aliases = "user"
}

{datastore
 alias = zak
 query = "select id_head
            from (
          select util.getPlanPotreb(to_date('01.'||to_char(to_date('~g.n_dat1~', 'dd.mm.yyyy'), 'mm.yyyy'), 'dd.mm.yyyy')) id_head
            from dual
                 )"
}
{datastore
 alias = plan_zak
 query = "select id_head
            from head
           where date_sd between to_date('01.'||to_char(to_date('~g.n_dat1~', 'dd.mm.yyyy'), 'mm.yyyy'), 'dd.mm.yyyy')
                             and last_day(to_date('~g.n_dat1~', 'dd.mm.yyyy'))
             and id_type_doc = 13
             and accept = 1"
}

{datastore
 alias = check_zakaz
 query = "select MONTH, Q
            from (
          select to_char(last_day(to_date('~g.n_dat1~', 'dd.mm.yyyy')), 'Month YYYY') MONTH, sum(spc.q1_spc) q
            from head, spc
           where head.date_sd between to_date('01.'||to_char(to_date('~g.n_dat1~', 'dd.mm.yyyy'), 'mm.yyyy'), 'dd.mm.yyyy')
                             and last_day(to_date('~g.n_dat1~', 'dd.mm.yyyy'))
             and head.id_type_doc = 9
             and head.id_head =  spc.id_head
             and head.id_active = ~global.id_active~
           group by to_char(last_day(to_date('~g.n_dat1~', 'dd.mm.yyyy')), 'Month YYYY')
                 )"
}

{datastore
 alias = ds_search
 query = "select id_active, name_active
            from active
           where ~global.loading~ = 0
             and (
                  upper(name_active) like upper('%~($try (global.search) $catch ('Any')(''))~%')
                  or
                  id_active like '~($try (global.search) $catch ('Any')(''))~%'
                 )
             --and analog = 0

           union

          select id_active, name_active
            from active
           where ~global.loading~ = 0
             and (
                     id_active in
                     (
                      select analog_id
                        from active
                       where (
                               upper(name_active) like upper('%~($try (global.search) $catch ('Any')(''))~%')
                               or
                               id_active like '~($try (global.search) $catch ('Any')(''))~%'
                             )
                        and  analog = 1
                     )
                   or
                     analog_id in
                      (
                        select id_active
                          from active
                         where (
                                 upper(name_active) like upper('%~($try (global.search) $catch ('Any')(''))~%')
                                 or
                                 id_active like '~($try (global.search) $catch ('Any')(''))~%'
                               )
                      )
                 )
         "

}

{ datastore
  alias = last_update
  query = "select last_update
             from (
           select parameters.d last_update
             from parameters 
            where parameters.id = 10
                  )"
}

{datastore
 alias = ds_time
 query = "select time, time_char, diff
            from (
          select sysdate time, to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') time_char,
                 round((sysdate - to_date('~($try (global.time) $catch ('Any')('01.01.2000 00:00:00'))~', 'dd.mm.yyyy hh24:mi:ss')) * 24*3600, 0) diff
            from dual
                 )"
}

{datastore
 alias = ds_month_end
 query = "select month_end
            from (
          select last_day(to_date('~($try (g.n_dat1) $catch ('Any')('01.01.2000'))~', 'dd.mm.yyyy')) month_end
            from dual
                 )"
}

{datastore 
        alias = ds_all_materials
        query = "select a.id_active_material, a.name_active_material, a.q_proiz, a.ost_nn, a.ost_semenov, a.perem, b.q_proiz_nn, b.q_proiz_semenov, b.name_active_prod,
                        decode(sign(a.q_proiz - (ost_nn + ost_semenov + perem)), 1, a.q_proiz - (ost_nn + ost_semenov + perem), 0) defcit
                   from (

                 select id_active id_active_material, name_active_material, q_proiz,
                        doc_lib.getRestAnalogBeforeAll (id_active, 1e20, '~global.sklad~') ost_nn,
                        0 ost_semenov,
                        util.moved_to_production_analog_2(id_active,  to_date('~g.n_dat1~', 'dd.mm.yyyy'), to_date('~g.n_dat2~', 'dd.mm.yyyy'), '~global.sklad~'  ) perem

                   from (
                 select active_mat.id_active, nvl(active_original.name_active, active_mat.name_active) name_active_material,
                        sum(spc_proiz.q1_spc*spc_spec.q1_spc) q_proiz
                   from head head_proiz, spc spc_proiz, head  head_spec, spc spc_spec, active,
                        active active_mat, active active_original

                  where head_proiz.date_sd between to_date('01.'||to_char(to_date('~g.n_dat1~', 'dd.mm.yyyy'), 'mm.yyyy'), 'dd.mm.yyyy')
                                               and last_day(to_date('~g.n_dat1~', 'dd.mm.yyyy')) 
                    and head_proiz.id_type_doc = 14
                    and head_proiz.accept = 1
                    and head_proiz.id_head = spc_proiz.id_head
                    and spc_proiz.id_active = active.id_active
                    and head_spec.id_head = active.id_head2
                    and spc_spec.id_head = head_spec.id_head
                    and spc_spec.id_active = active_mat.id_active
                    and active_original.id_active (+) = active_mat.analog_id
                    and active_original.analog_id is null

                  group by active_mat.id_active, nvl(active_original.name_active, active_mat.name_active)
                        )
                        )  a,

                        (

                 select active_mat.id_active id_active_material, active.name_active name_active_prod,
                        case when head_proiz.id_department1 = 206 then spc_proiz.q1_spc*spc_spec.q1_spc else 0 end q_proiz_nn,
                        case when head_proiz.id_department1 = 210 then spc_proiz.q1_spc*spc_spec.q1_spc else 0 end q_proiz_semenov

                   from head head_proiz, spc spc_proiz, head  head_spec, spc spc_spec, active,
                        active active_mat

                  where head_proiz.date_sd between to_date('01.'||to_char(to_date('~g.n_dat1~', 'dd.mm.yyyy'), 'mm.yyyy'), 'dd.mm.yyyy')
                                               and last_day(to_date('~g.n_dat1~', 'dd.mm.yyyy')) 
                    and head_proiz.id_type_doc = 14
                    and head_proiz.accept = 1
                    and head_proiz.id_head = spc_proiz.id_head
                    and spc_spec.id_head = head_spec.id_head
                    and spc_spec.id_active = active_mat.id_active
                    and spc_proiz.id_active = active.id_active
                    and head_spec.id_head = active.id_head2

                        )  b

                  where a.id_active_material = b.id_active_material

                  order by name_active_material, name_active_prod"
} // end of ds

{datastore 
        alias = ds_all_materials2
        query = "select prod_name, q1, q2, q3, q4, q5, q6, mat_id, mat_name, work_stage
                   from big3.analiz
                  order by mat_name, prod_name nulls first"
} // end of ds

{datastore
  alias = ds_show
  query = "select p.s, p.n
             from parameters p
            where p.username = user
              and p.s in ('show_zak', 'show_perem', 'show_spis', 'show_supplier')"
}



{dbExecutor
   alias=dbe
   sharedConnection=NO
   blocking=NO

   query = "begin ? := 'First DBExecutor run.'; end;"

   commitexp = "($X
                  ($debug 'CommitEXP: DBExecutor result: ' + ($g.dbe@getResult))
                  ($g.info@setValue ($g.dbe@getResult))
                  (global.timeout = '60')
                  (global.query = 'declare v_message varchar2(1000); v_status integer; begin DBMS_ALERT.REGISTER(\'alert1\'); DBMS_ALERT.WAITONE (\'alert1\', v_message, v_status, ' + global.timeout + '); ? := to_char(sysdate, \'dd.mm.yyyy hh24:mi:ss\'); end;')
                  (debug 'global.query: ' + global.query)
                  ($g.dbe@setQuery global.query)
                  #($g.grid1@retrieve)
                  ($if (global.run_again == 1)
                    (
                      ($debug 'CommitEXP: DBExecutor - run again!')
                      ($g.dbe@execute)
                    )
                   $else
                    (
                      ($debug 'CommitEXP: DBExecutor - I won\'t run again, bye.')
                    )
                  )
                  ($ret '')
                )"
   outType = "varchar"
 }



{toolbar   BACKGROUND = "LIGHTGRAY"
{splitpanel type=horizonal     percent=25
{tabset labels = "Параметры"
{form  alias = form1
  {label left = 5 top = 10 width = 54 height = 30 font_size = 14 value = "Дата с" }
  { field left = 50 top = 10 width = 100 height = 30 font_size = 16 alias = dat1  target = month_beginning
    dep = "n_dat1"
  }
  {field  alias=n_dat1  type=string 
    left = 50 top = 40 width = 100 height = 30 font_size = 16	visible = no
    exp="($X
           (g.n_dat1 = ($($@date g.dat1)@toPLString))
           ($debug 'n_dat1 = ' + g.n_dat1)
           ($g.ds_month_end@retrieve)
           (g.dat2 = ($g.ds_month_end@getValue 'month_end'))
           (g.n_dat2 = ($($@date g.dat2)@toPLString))
           ($debug 'n_dat2 = ' + g.n_dat2)
           ($ret '')
         )" 
  }
  {label left = 160 top = 10 width = 25 height = 30 font_size = 14 value = "по" }
  { field left = 190 top = 10 width = 100 height = 30 font_size = 16 alias = dat2  target = month_end
    dep = "n_dat2"
  }
  {field  alias=n_dat2  type=string 
    left = 175 top = 40 width = 100 height = 30 font_size = 16	visible = no
    exp="($X
           (g.n_dat2 = ($($@date g.dat2)@toPLString))
           ($debug 'n_dat2 = ' + g.n_dat2)
           ($ret '')
         )" 
  }
  {field
    left = 10 top = 50 width = 200 height = 30 font_size = 16
    type = string   alias = field_name_doc
    visible = no
  }
  { button left = 300 top = 10 width = 26 height = 26 
    icon = "images/reload.png"
    tooltiptext = "Обновить данные"

    action = "($X
                 ($refresh 0)
                 ($g.ds_time@retrieve)
                 ($g.info@setValue 'Время выполнения отчета: ' + ($($g.ds_time@getValue 'diff')@int) + ' с.')
                 # раскрашиваем верхнюю левую таблицу
                 ($paint_plan 0)
                 # раскрашиваем нижнюю таблицу
                 ($repaint_grid1 0)

                 ($ret '')
              )"
  }
  { button left = 700 top = 10 width = 100 height = 30 label = "hide/unhide" foreground = "#000000"
    visible = no
    action = "($X
                 ($if (global.visible == 'yes')
                   (
                     ($p.hidden1@setVisible 'NO')
                     (global.visible = 'no')
                   )
                  $else
                   (
                     ($p.hidden1@setVisible 'YES')
                     (global.visible = 'yes')
                   )
                 )
                 ($g.grid3@repaint)
                 ($ret '')
              )"
  }
  { button left = 520 top = 10 width = 210 height = 30 label = "Калькулятор бюджета потребности" foreground = "#000000" visible = no
    action = "($X
                 ($g.self@doAction 'open docs/analiz_potr_calculator.rml')
                 ($ret '')
              )"
  }



  {label left = 10 top = 70 width = 150 height = 25 font_size = 12 value = "Группировка по названию:" }
  { field left = 160 top = 70 width = 100 height = 25 font_size = 12 alias = field_group1  type = string
    //dep = "group1"
    tooltiptext = "Группировка по строке, _начинающейся_ с введенной строки поиска"
  }
  { field left = 270 top = 70 width = 100 height = 25 font_size = 12 alias = field_group2  type = string
    //dep = "group1"
    tooltiptext = "Группировка по строке поиска"
  }
  { field left = 510 top = 92 width = 200 height = 30 font_size = 16 alias = group1  type = string visible = no
    exp = "($X
              ($if (global.loading == 1) ($ret '') )

              ($g.grid3@retrieve)
              ($g.grid32@retrieve)
              ($g.grid1@retrieve)
              ($repaint_grid1 0)

              ($ret '')
           )"
  }

  {label left = 10 top = 100 width = 80 height = 30 font_size = 16 value = "Поиск" }
  { field left = 90 top = 100 width = 200 height = 30 font_size = 16 alias = field_search  type = string
    dep = "search1"  firstfocus = yes
  }
  { field left = 510 top = 92 width = 200 height = 30 font_size = 16 alias = search1  type = string visible = no
    exp = "($X
              ($if (global.loading == 1) ($ret '') )

              (v = ($@validator))
              ($v@SetType 'NUMBER')
              ($v@SetMask '##0.')

              (found = 0)
              (found_row = -1)

              ($g.info@setValue 'Поиск...')
              ($debug '..searching...')
              (global.search = ($reg2ora ($try (g.field_search) $catch ('NullException')(''))))
              ($debug 'global.search: [' + global.search + '], length: ' + ($global.search@length))
              ($g.ds_search@retrieve)
              ($if (($g.ds_search@size) == 0)
                (
                  ($g.info@setValue 'Актив не найден в справочнике.')
                  ($ret '')
                )
              )
              ($if (($g.ds_search@size) == 1)

                (
                  (global.id_active = ($g.ds_search@getValue 'id_active'))

                  # ищем актив в таблице

                  ($debug 'searching ' + global.id_active + ' .... ')

                  (i = 0)
                  (sz = ($g.gridds03@size))
                  ($while (i < sz)   
                    (
                      ($g.gridds03@fastSetCurRow i)
                      #($debug 'row: ' + i + '  id_active: ' + ($g.gridds03@getValue 'id_active'))
                      ($if (($g.gridds03@getValue 'id_active') == global.id_active)
                        (
                          #($if (i > 0) ($g.gridds03@fastSetCurRow i-1))
                          #($g.gridds03@SetCurRow i)

                          (found = 1)
                          (found_row = i)
                          ($break)
                        )
                      )
                      (i += 1)
                    )
                  )
                  ($debug 'found_row: ' + found_row)
                  ($if (found_row == -1)
                    (
                      ($g.info@setValue 'Материал есть в справочнике, но его нет в списке требуемых материалов.')
                      ($ret '')
                    )
                  )

                  ($g.grid3@setCurrentRow found_row)
                  ($g.gridds03@notifyViews)
                  ($g.grid32@retrieve)
                  ($g.grid1@retrieve)
                  ($repaint_grid1 0)

                )
               $else
                # Найдено больше одного актива. Откроем отдельную форму для выбора актива.
                (
                  (found = 1)
                  ($global.h@put 'OK' 0)
                  ($global.h@put 'NAME_ACTIVE' global.search)
                  ($($g.self@getAliases)@put '_SELECTION1' global.h)
                  ($($g.self@getAliases)@put '_SELECTION2' g.gridds03)
                  ($($g.self@getAliases)@put '_SELECTION3' g.gridds032)
                  ($g.self@doAction 'opennew find_active_by_name.rml &_SELECTION1, &_SELECTION2, &_SELECTION3')
                )
              )
              ($g.info@setValue '')

              ($debug 'chosen ID_ACTIVE: ' + global.id_active)


              ($if (found == 0)
                  ($g.info@setValue 'Актив [' + ($v@toString ($global.h@get 'ID_ACTIVE')) + '] \"' + ($global.h@get 'NAME_ACTIVE') + '\" не найден в таблице.')
              )
              ($ret '')
           )"
  }
  {label  value="Группа материалов"
    top=70  left=380  width=100  height=20  bg_color="#c0c0c0"  font_size = 12
  }
  {field
    alias=active_group_name  type=string
    left=380  top=100  height=20  width=150  border=box  font_size=12  font_family=0  bg_color="#ffffff"
    editable=handbook
    edit="opennew sprav/active_group_choose.rml"
    editexp="($X
               (g.active_group_name=STORE.active.name_active)(global.id_group_active = STORE.active.id_active)
               ($ret '')
             )"
  }
  {button  
    left=490 top=70
    width = 40    height = 20
    BACKGROUND = "LIGHTGRAY"    foreground="#000000"
    label = "X"
    tooltiptext = "Убрать фильтр по группе материалов"
    action="($X
              (g.active_group_name='')(global.id_group_active = -1)
              ($ret '')
            )"
  }

  { checkgroup left = 580 top = 10
     {checkbox  alias = check_defcit
        font_size = 12
       label = "Показывать только дефицит"
       onvalue = "1"
       offvalue = "0"
       action = "~($X
                    ($ret )
                  )~"
     }
     {checkbox  alias = check_import
        font_size = 12
       label = "Показывать только импорт"
       onvalue = "1"
       offvalue = "0"
       action = "~($X
                    ($ret )
                  )~"
     }
     {checkbox  alias = check_see_null
        font_size = 12
       label = "Показывать нулевые остатки"
       onvalue = "1"
       offvalue = "0"
       action = "~($X
                    ($g.grid32@retrieve)
                    ($ret )
                  )~"
     }
      {checkbox
        alias=filter2
        font_size = 12
        onvalue="1"
        offvalue = "0"
        check=no
        visible = yes
        action = "
    ~JS:{
          var version = java.lang.System.getProperty('java.version');
          if (version.startsWith('1.8.0')) {
              load('nashorn:mozilla_compat.js');
          }
          importPackage(org.apache.log4j);
          logger = LogManager.getLogger('Logger');
          logger.debug('FILTER2: ' + FILTER2.isSelected());
          GRID3.setColumnVisible('column_id_active', FILTER2.isSelected());
          GRID3.setColumnVisible('column_proc1', FILTER2.isSelected());
          GRID32.setColumnVisible('column_id_active_grid32', FILTER2.isSelected());
          GRID32.setColumnVisible('column_name_group', FILTER2.isSelected());
          ret = '';
        }~"

          label="Показывать дополнительные столбцы"
      }
  }

  {label left = 10 top = 130 width = 600 height = 30 font_size = 14 font_color = "#0000FF" value = "" alias = info }




      {datastore  alias=date_ds
        query="select month_beginning, month_end
                 from (
               select to_date('01.'||to_char(sysdate, 'mm.yyyy'), 'dd.mm.yyyy') month_beginning,
                      last_day(to_date('01.'||to_char(sysdate, 'mm.yyyy'), 'dd.mm.yyyy')) month_end
                 from dual
                      )"
        
      }
} // end of form
} // tabset

{splitpanel type = horizontal percent = 70
{splitpanel type = vertical percent = 55
{grid
    alias = GRID3
    buttonbar_font_family = 0              buttonbar_font_size = 12
    buttonbar_size = 1                    titlebar_size = 18    titlebar_height = 40
    multiselect = no                      editable = no
    rowsize = 20
          add=""
       addexp=""
          del=""

    edit = "~($X
               ($debug 'current column: ' + ($g.grid3@getCurColumnAlias))
               ($if (($g.grid3@getCurColumnAlias) == 'COLUMN_ZAK')
                  ($g.self@doAction 'opennew reports/report_history_zakaz.rml ' + ($g.gridds03@getValue 'id_active') + ', \'' + g.n_dat1 + '\'')
                $if (($g.grid3@getCurColumnAlias) == 'COLUMN_PEREM')
                  ($g.self@doAction 'opennew reports/report_history_perem.rml ' + ($g.gridds03@getValue 'id_active') + ', \'' + g.n_dat1 + '\'')
                $if (($g.grid3@getCurColumnAlias) == 'COLUMN_VOZVR')
                  ($g.self@doAction 'opennew reports/report_history_vozvr.rml ' + ($g.gridds03@getValue 'id_active') + ', \'' + g.n_dat1 + '\'')
                $if (($g.grid3@getCurColumnAlias) == 'COLUMN_REPLACE')
                  ($g.self@doAction 'opennew reports/report_history_replace.rml ' + ($g.gridds03@getValue 'id_active') + ', \'' + g.n_dat1 + '\'')
               )
               ($ret '')
             )~"

   {menu
      {item label = "Сроки поставки"
       action = "opennew util/sroki_postavki.rml ~($g.gridds03@getValue 'id_active')~"
      }
      {item label = "Вывести позиции без поставщиков и сроков"
       action = "~($X
                     ($($g.self@getAliases)@put '_DS1' g.gridds03) 
                     ($g.self@doAction 'opennew util/sroki_postavki.rml 0, &_DS1 ')
                     ($ret '')
                  )~"
      }

      {menu label = "История"
        {item label = "перемещений"
         action = "~($X
                       ($debug 'ARGUMENTS.0: ' + ($g.gridds03@getValue 'id_active') + ' (' + ($($g.gridds03@getValue 'id_active')@type) + ')')
                       ($debug 'ARGUMENTS.1: ' + ($g.gridds03@getValue 'dat1') + ' (' + ($($g.gridds03@getValue 'dat1')@type) + ')')
                       ($ret 'opennew history_move.rml ' + ($g.gridds03@getValue 'id_active') + ', ' + ($g.gridds03@getValue 'dat1'))
                     )~"
        }
        {item label = "потребности"
         action = "~($X
                       ($($g.self@getAliases)@put '_DS1' g.gridds03) 
                       ($g.self@doAction 'open reports/report_history_potr.rml &_DS1, \'' + g.n_dat1 + '\'')
                       ($ret '')
                     )~"
        }
        {item label = "замен"
         action = "~($X
                       ($($g.self@getAliases)@put '_DS1' g.gridds03) 
                       ($g.self@doAction 'opennew reports/report_history_zamen.rml ' + ($g.gridds03@getValue 'id_active') + ', \'' + ($g.gridds03@getValue 'dat3') + '\', \'' + ($g.gridds03@getValue 'dat4') + '\'')
                       ($ret '')
                     )~"
        }
        {item label = "поставок"
         action = "~($X
                       ($g.self@doAction 'opennew reports/report_history_supply.rml ' + ($g.gridds03@getValue 'id_active') + ', \'' + ($g.gridds03@getValue 'dat3') + '\', \'' + ($g.gridds03@getValue 'dat4') + '\'')
                       ($ret '')
                     )~"
        }
        {item label = "производства"
         action = "~($X
                       #($g.self@doAction 'opennew reports/report_history_production.rml ' + ($g.gridds03@getValue 'id_active') + ', \'' + ($g.gridds03@getValue 'dat3') + '\', \'' + ($g.gridds03@getValue 'dat4') + '\'')
                       ($ret '')
                     )~"
        }
      
      }
      {item label = "Выгрузка в Excel (только материалы)"
       action = "~($X
                      
                     (global.grid_name = 'GRID3')
                     ($g.self@calculate 'js:js_file:/docs/print_grid.js')
                     ($ret '')
                   )~"
      }
      {item label = "Выгрузка в Excel (подробно по материалам и продукции)"
       action = "~($X
                     ($g.ds_all_materials@retrieve) 
                     (global.datastore_name = 'DS_ALL_MATERIALS')
                     ($g.self@calculate 'js:js_file:/docs/print_ds_analiz_potr.js')
                     ($ret '')
                   )~"
      }
      {item label = "Выгрузка в Excel TEST1"
       action = "~($X
                     ($g.self@doAction 'execute exec big3.analiz_calc1(to_date(\'' + g.n_dat1 + '\', \'dd.mm.yyyy\'), to_date(\'' + g.n_dat2 + '\', \'dd.mm.yyyy\'), ' + ($g.check_defcit@getValue) + ')')
                     ($g.ds_all_materials2@retrieve) 
                     (global.datastore_name = 'DS_ALL_MATERIALS2')
                     (global.date1 = g.n_dat1)
                     (global.date2 = g.n_dat2)
                     ($g.self@calculate 'js:js_file:/docs/print_ds_analiz_potr2.js')
                     ($ret '')
                   )~"
      }
      {item label = "Выгрузка в Excel TEST2, заготовка"
       action = "~($X
                     ($g.self@doAction 'execute exec big3.analiz_calc3(to_date(\'' + g.n_dat1 + '\', \'dd.mm.yyyy\'), to_date(\'' + g.n_dat2 + '\', \'dd.mm.yyyy\'), 1, ' + ($g.check_defcit@getValue) + ')')
                     ($g.ds_all_materials2@retrieve) 
                     (global.datastore_name = 'DS_ALL_MATERIALS2')
                     (global.date1 = g.n_dat1)
                     (global.date2 = g.n_dat2)
                     ($g.self@calculate 'js:js_file:/docs/print_ds_analiz_potr4.js')
                     ($ret '')
                   )~"
      }
      {item label = "Выгрузка в Excel TEST2, сборка"
       action = "~($X
                     ($g.self@doAction 'execute exec big3.analiz_calc3(to_date(\'' + g.n_dat1 + '\', \'dd.mm.yyyy\'), to_date(\'' + g.n_dat2 + '\', \'dd.mm.yyyy\'), 2, ' + ($g.check_defcit@getValue) + ')')
                     ($g.ds_all_materials2@retrieve) 
                     (global.datastore_name = 'DS_ALL_MATERIALS2')
                     (global.date1 = g.n_dat1)
                     (global.date2 = g.n_dat2)
                     ($g.self@calculate 'js:js_file:/docs/print_ds_analiz_potr4.js')
                     ($ret '')
                   )~"
      }
      {item label = "Заказы"
       action = "opennew reports/report_history_zakaz.rml ~($g.gridds03@getValue 'id_active')~ , '~g.n_dat1~'"
      }
      {item label = "-" }
      {item label = "Выбрать столбцы"
       action = "~($X
                     ($global.h@put 'show_perem' global.show_perem)
                     ($global.h@put 'show_spis' global.show_spis)
                     ($global.h@put 'show_zak' global.show_zak)
                     ($global.h@put 'show_supplier' global.show_supplier)

                     ($($g.self@getAliases)@put '_HASH123' global.h)
                     ($g.self@doAction 'opennew docs/analiz_potr_parameters.rml &_HASH123')

                     ($debug 'show_perem: ' + ($global.h@get 'show_perem'))
                     (global.show_perem = ($global.h@get 'show_perem'))
                     (global.show_spis = ($global.h@get 'show_spis'))
                     (global.show_zak = ($global.h@get 'show_zak'))
                     (global.show_supplier = ($global.h@get 'show_supplier'))
                     ($debug 'show_supplier: ' + ($global.h@get 'show_supplier'))

                     ($g.self@calculate 'js:js_file:/docs/analiz_potr_2.js')
                     ($ret '')
                   )~"
      }
   }

   {column size=50             title="Код"
        target=id_active
        alias = column_id_active
        font_size = 12          font_family=0
        editable = readonly
        visible = no
   }                  
   {column size=230             title="Материал"
        target=name_active
        alias = column_name_active
        title_font_family=2        title_font_color="#000000"
        halignment=left            font_size = 12          font_family=0
        editable = readonly
   }                  
   {column size=80             title="<html>Требуется<br>в произв-во</html>"
        target=q_proiz
        title_font_family=2        title_font_color="#000000"
        halignment=right            font_size = 12          font_family=0 editmask = "# #0.00"
        editable = readonly
        alias = q_proiz
   }                  
   {column size=70             title="<html>Остаток<br>Ренталком"
        target=ost1
        title_font_family=2        title_font_color="#000000"
        halignment=right            font_size = 12          font_family=0 editmask = "# #0.00"
        editable = readonly
        alias = q_ost1
   }                  
   {column size=70             title="Дефицит"
        target = def
        title_font_family=2        title_font_color="#000000"
        halignment=right            font_size = 12          font_family=0 editmask = "# #0.00"
        editable = readonly
        alias = column_def
   }                  
  
   {column size=70             title="<html>Перемещено<br>в произв-во</html>"
        target = perem
        title_font_family=2        title_font_color="#000000"
        halignment=right            font_size = 12          font_family=0 editmask = "# #0.00"
        editable = readonly
        alias = column_perem
   }                  
   {column size=70             title="Хитрый колумн"
        type = string
        title_font_family=2        title_font_color="#000000"
        halignment=right            font_size = 12          font_family=0 editmask = "# #0.00"
        editable = readonly
        alias = hidden1
        visible = no
   }                  
   {column size=70             title="В заказах"
        target=zak
        title_font_family=2        title_font_color="#000000"
        halignment=right            font_size = 12          font_family=0 editmask = "# #0.00"
        editable = readonly
        alias = column_zak
   }                  
   {column size=70             title="<html>След.<br> поставка"
        target=zak_date
        title_font_family=2        title_font_color="#000000"
        halignment=right            font_size = 12          font_family=0 editmask = "# #0.00"
        editable = readonly
        alias = column_zak_date
   }                  
   {column size=70             title="<html>Посл.<br> поставка"
        target=last_supply_date
        title_font_family=2        title_font_color="#000000"
        halignment=right            font_size = 12          font_family=0 editmask = "# #0.00"
        editable = readonly
        alias = column_last_supply_date
   }                  
   {column size=200             title="Посл. поставщик"
        target=supplier
        title_font_family=2        title_font_color="#000000"
        halignment=right            font_size = 12          font_family=0 editmask = "# #0.00"
        editable = readonly
        alias = column_supplier
   }                  
   {column size=70             title="<html>Списано<br>в произв-во</html>"
        target=spis
        title_font_family=2        title_font_color="#000000"
        halignment=right            font_size = 12          font_family=0 editmask = "# #0.00"
        editable = readonly
        alias = column_spis
   }                  
   {column size=70             title="<html>Реализовано</html>"
        target=real
        title_font_family=2        title_font_color="#000000"
        halignment=right            font_size = 12          font_family=0 editmask = "# #0.00"
        editable = readonly
   }                  
   {column size=50             title="<html>% тех.<br>потерь"
        target=proc1
        alias = column_proc1
        halignment=right   font_size = 12          font_family=0   editmask = "# #0.00"
        editable = readonly
        visible = no
   }                  

   {datastore 
           alias = GRIDDS03
           query = "select id_active, name_active,
                           q1_spc, q2_spc, q3_spc, q4_spc, q5_spc, proc1,
                           q_proiz, q_proiz_auto, q_proiz_manual, ost1, ost2, zak, zak_date, supplier, last_supply_date,
                           def, post, perem, spis, real, analog, analog_id, dat1, dat2, dat3, dat4, q_replace
                      from (

                    ~($X
                       # для замера времени выполнения
                       ($g.ds_time@retrieve)
                       (global.time = ($g.ds_time@getValue 'time_char'))

                       # параметры отображения столбцов
                       ($if (global.loading == 1)
                         (
                           ($g.ds_show@retrieve)
                           (i = 0)
                           ($while (i < ($g.ds_show@size))
                             (
                               ($g.ds_show@setCurRow i)
                               ($if (($g.ds_show@getValue 'p.s') == 'show_zak')
                                 ($if (($g.ds_show@getValue 'p.n') == 1)
                                   (global.show_zak = 1)
                                  $else
                                   (global.show_zak = 0)
                                 )
                               )
                               ($if (($g.ds_show@getValue 'p.s') == 'show_perem')
                                 ($if (($g.ds_show@getValue 'p.n') == 1)
                                   (global.show_perem = 1)
                                  $else
                                   (global.show_perem = 0)
                                 )
                               )
                               ($if (($g.ds_show@getValue 'p.s') == 'show_spis')
                                 ($if (($g.ds_show@getValue 'p.n') == 1)
                                   (global.show_spis = 1)
                                  $else
                                   (global.show_spis = 0)
                                 )
                               )
                               ($if (($g.ds_show@getValue 'p.s') == 'show_supplier')
                                 ($if (($g.ds_show@getValue 'p.n') == 1)
                                   (global.show_supplier = 1)
                                  $else
                                   (global.show_supplier = 0)
                                 )
                               )
                               (i += 1)
                             )
                           )
                         )
                       )
                       ($g.self@calculate 'js:js_file:/docs/analiz_potr_2.js')

                       ($if (($try (g.field_group1) $catch ('NullException')(0)) == 0)
                         ($if (($try (g.field_group2) $catch ('NullException')(0)) == 0)
                            ($ret 'select id_active, name_active, q1_spc, q2_spc, q3_spc, q4_spc, q5_spc, proc1, q_proiz, q_proiz_auto, q_proiz_manual, ost1, ost2, zak, zak_date, supplier, last_supply_date,
                                   def, post, ost_tseh, perem, spis, real, analog, analog_id, dat1, dat2, dat3, dat4, q_replace')
                          $else
                            ($ret 'select to_number(null) id_active, \'[' + g.field_group2 + ']\' name_active, 
                                   sum(q1_spc) q1_spc, sum(q2_spc) q2_spc, sum(q3_spc) q3_spc, sum(q4_spc) q4_spc, sum(q5_spc) q5_spc, sum(proc1) proc1,
                                   sum(q_proiz) q_proiz, sum(q_proiz_auto) q_proiz_auto, sum(q_proiz_manual) q_proiz_manual,
                                   sum(ost1) ost1, sum(ost2) ost2, sum(zak) zak, min(zak_date) zak_date, min(supplier) supplier, min(last_supply_date) last_supply_date,
                                   sum(def) def, sum(perem) perem, sum(spis) spis, sum(real) real, sum(post) post, sum(ost_tseh) ost_tseh, sum(q_replace) q_replace,
                                   null analog, null analog_id, null dat1, null dat2, null dat3, null dat4')
                         )
                        $else
                         ($if (($try (g.field_group2) $catch ('NullException')(0)) == 0)
                            ($ret 'select to_number(null) id_active, \'[' + g.field_group1 + ']\' ||
                                          substr(name_active, instr(upper(name_active), upper(\'' + g.field_group1 + '\')) + length(\'' + g.field_group1 + '\')) name_active,
                                          sum(q1_spc) q1_spc, sum(q2_spc) q2_spc, sum(q3_spc) q3_spc, sum(q4_spc) q4_spc, sum(q5_spc) q5_spc, sum(proc1) proc1,
                                          sum(q_proiz) q_proiz, sum(q_proiz_auto) q_proiz_auto, sum(q_proiz_manual) q_proiz_manual,
                                          sum(ost1) ost1, sum(ost2) ost2, sum(zak) zak, min(zak_date) zak_date, minsum(def) def, sum(perem) perem, sum(spis) spis, sum(post) post, sum(ost_tseh) ost_tseh,
                                          sum(q_replace) q_replace,
                                          null analog, null analog_id, null dat1, null dat2, null dat3, null dat4')
                          $else
                            ($ret 'select to_number(null) id_active, \'[' + g.field_group1 + ']\' || \'[' + g.field_group2 + ']\' name_active,
                                   sum(q1_spc) q1_spc, sum(q2_spc) q2_spc, sum(q3_spc) q3_spc, sum(q4_spc) q4_spc, sum(q5_spc) q5_spc, sum(proc1) proc1,
                                   sum(q_proiz) q_proiz, sum(q_proiz_auto) q_proiz_auto, sum(q_proiz_manual) q_proiz_manual,
                                   sum(ost1) ost1, sum(ost2) ost2, sum(zak) zak, min(zak_date) zak_date, min(supplier) supplier, min(last_supply_date) last_supply_date,
                                   sum(def) def, sum(perem) perem, sum(spis) spis, sum(real) real, sum(post) post, sum(ost_tseh) ost_tseh, sum(q_replace) q_replace,
                                   null analog, null analog_id, null dat1, null dat2, null dat3, null dat4')
                         )
                       )
                      )~

                      from (

                    select id_active, name_active,
                           q1_spc, q2_spc, q3_spc, q4_spc, q5_spc, proc1,
                           q_proiz, q_proiz_auto, q_proiz_manual, ost1, ost2, ost_tseh, zak, null zak_date, supplier, last_supply_date,
                           decode(sign(q_proiz - (ost1 + ost2 + perem + real)), 1, q_proiz - (ost1 + ost2 + perem + real), 0) def, perem, q_proiz - perem post, spis, real,
                           analog, 0 analog_id, to_date('~g.n_dat1~', 'dd.mm.yyyy') dat1,
                           to_char(to_date('~g.n_dat1~', 'dd.mm.yyyy'), 'month YYYY') dat2,
                           '~g.n_dat1~' dat3,
                           '~g.n_dat2~' dat4,
                           q_replace
                      from (
                    select id_active, name_active, analog,
                           q1_spc, q2_spc, q3_spc, q4_spc, q5_spc, q1_spc*proc1/100 proc1,
                           q_proiz, q_proiz_auto, q_proiz_manual, 
                           doc_lib.getRestAnalogBeforeAll (id_active,
                                                                  
                                                                  1e30, -- текущие
                                                                  -- (select nvl(max(sort), 1e20) from head where date_sd between to_date('~g.n_dat1~', 'dd.mm.yyyy') and to_date('~g.n_dat2~', 'dd.mm.yyyy')), -- на конец выбранного периода
                                                                '~global.sklad_only~'
                                                               )
                           ost1,

                           0 ost2,
			   				
                           ~($if (global.show_zak == 1)
                               ('util.orderQuantity(id_active, to_date(\'' + g.n_dat1 + '\', \'dd.mm.yyyy\')) zak, ')
                             $else
                              ('0 zak,')
                            )~

                           
                           ~($if (global.show_supplier == 1)

                               (' (select name1_client 
                                     from client c, active a
                                    where c.id_client = a.last_id_client
                                      and a.id_active = aaa.id_active
                                   ) supplier,
                                   
                                  (select last_income from active a where a.id_active = aaa.id_active) last_supply_date,')
                             $else  ('\'Поставщик\' supplier, null last_supply_date, ')
                            )~

                           
                           ~($if (global.show_perem == 1)
                              (' util.moved_to_production_analog_2(id_active,  to_date(\'' + g.n_dat1 + '\', \'dd.mm.yyyy\'), to_date(\'' + g.n_dat2 + '\', \'dd.mm.yyyy\'), \'' + global.sklad + '\'  ) perem, ')
                             $else
                              (' 0 perem, ')
                            )~
                           
                           ~($if (global.show_spis == 1)
                             (' util.gone_to_production(id_active,  to_date(\'' + g.n_dat1 + '\', \'dd.mm.yyyy\'), to_date(\'' + g.n_dat2 + '\', \'dd.mm.yyyy\'), \'' + global.sklad + '\'  ) spis, 
                                util.gone_to_realization(id_active,  to_date(\'' + g.n_dat1 + '\', \'dd.mm.yyyy\'), to_date(\'' + g.n_dat2 + '\', \'dd.mm.yyyy\'), \'' + global.sklad + '\'  ) real, 
                              ')
                             $else
                              (' 0 spis, 0 real, ')
                            )~
                           
                           0 ost_tseh,

                           /*
                           util.replaced_to_production_analog (id_active,
                                                             to_date('~g.n_dat1~', 'dd.mm.yyyy'),
                                                             to_date('~g.n_dat2~', 'dd.mm.yyyy'),
                                                                '~global.sklad~'
                                                               )
                           q_replace
                           */
                           0 q_replace



                      from (
                    select nvl(active_original.id_active, active.id_active) id_active, nvl(active_original.name_active, active.name_active) name_active,
                           nvl(active_original.analog, active.analog) analog,
                           nvl(active_original.proc1, active.proc1) proc1,
                           sum(spc_proiz.q1_spc*spc_spec.q1_spc) q1_spc, sum(spc_proiz.q2_spc*spc_spec.q1_spc) q2_spc,
                           sum(spc_proiz.q3_spc*spc_spec.q1_spc) q3_spc, sum(spc_proiz.q4_spc*spc_spec.q1_spc) q4_spc, sum(spc_proiz.q5_spc*spc_spec.q1_spc) q5_spc,
                           sum(
                                (spc_proiz.q1_spc + spc_proiz.q2_spc + spc_proiz.q3_spc + spc_proiz.q4_spc + spc_proiz.q5_spc)*spc_spec.q1_spc
                              ) q_proiz,
                           sum(
                                case when spc_spec.id_department = 2 then
                                 spc_proiz.q1_spc*spc_spec.q1_spc
                                else 0
                                end
                              ) q_proiz_auto,
                           sum(
                                case when spc_spec.id_department = 1 then
                                 spc_proiz.q1_spc*spc_spec.q1_spc
                                else 0
                                end
                              ) q_proiz_manual
                      from head head_proiz, spc spc_proiz, head  head_spec, spc spc_spec, active active_prod, active, active active_original
                     where head_proiz.date_sd between to_date('~g.n_dat1~', 'dd.mm.yyyy')
                                                  and to_date('~g.n_dat2~', 'dd.mm.yyyy')
                       and head_proiz.id_type_doc = 14
                       and head_proiz.id_department1 in (~global.sklad~)
                       and head_proiz.accept = 1
                       and active.closed = 0
                       and active_original.closed (+) = 0

                       ~($if (($g.check_import@getValue) == '1')
                          (' -- только импорт
                             and active.id_active in (
                                                       select id_active
                                                         from active
                                                      connect by prior id_active = parent_act_id
                                                        start with id_active in (
                                                                                 select active.id_active
                                                                                   from active
                                                                                  where active.status_group = 0
                                                                                    and import = 1
                                                                                )
                                                     )
                           ')
                         $else
                          ('')
                        )~

                       and head_proiz.id_head = spc_proiz.id_head
                       and spc_proiz.id_active = active_prod.id_active
                       and head_spec.id_head = active_prod.id_head2
                       and spc_spec.id_head = head_spec.id_head
                       and spc_spec.id_active = active.id_active
                       -- этап производства [Заготовка] включает этапы [Заготовка], [Цех1], [Цех2] по спецификации
                       -- этап производства [Сборка] включает этапы [Сборка], [ОТК], [прочие материалы] по спецификации
                       -- либо в производстве указано [все этапы]
                       and ( spc_proiz.work_type = 0
                             or
                             ( spc_proiz.work_type = 1 and spc_spec.work_type in (1, 4, 5) )
                             or
                             ( spc_proiz.work_type = 2 and spc_spec.work_type in (2, 3, 6) )
                           )
                       and active_original.id_active (+) = active.analog_id
                       and active_original.analog_id is null
                       ~($if (($try (g.field_id_client) $catch ('NullException')(0)) != 0)
                           (' and active.id_active in (select price.id_active from price where price.id_client = ' + g.field_id_client + ')')
                         $else
                           (' ') 
                        )~
                        ~($if (global.id_group_active != -1)
                           (' and active.id_active in (
                                                         select id_active
                                                           from active
                                                        connect by prior active.id_active = active.parent_act_id 
                                                          start with active.parent_act_id = ' + global.id_group_active + '
                                                       )
                          ')
                          $else (' ')
                         )~


                     group by nvl(active_original.id_active, active.id_active), nvl(active_original.name_active, active.name_active),
                              nvl(active_original.analog, active.analog), nvl(active_original.proc1, active.proc1)
                           ) aaa
                     where q_proiz != 0
                           )
                           )

                    ~($X
                       ($if (($try (g.field_group1) $catch ('NullException')(0)) == 0)
                         ($if (($try (g.field_group2) $catch ('NullException')(0)) == 0)
                            ($ret '')
                          $else
                            ($ret 'where upper(name_active) like upper(\'%' + g.field_group2 + '%\')
                                   group by to_number(null), \'[' + g.field_group2 + ']\'')
                         )
                        $else
                         ($if (($try (g.field_group2) $catch ('NullException')(0)) == 0)
                            ($ret 'where upper(name_active) like upper(\'%' + g.field_group1 + '%\') 
                                   group by to_number(null),
                                    \'[' + g.field_group1 + ']\' ||
                                     substr(name_active, instr(upper(name_active), upper(\'' + g.field_group1 + '\')) + length(\'' + g.field_group1 + '\')) 
                                  ')
                          $else
                            ($ret 'where upper(name_active) like upper(\'%' + g.field_group1 + '%' + g.field_group2 + '%\')
                                   group by to_number(null), \'[' + g.field_group1 + ']\' || \'[' + g.field_group2 + ']\'')
                         )
                       )
                      )~
                           
                           
                           )
                     ~($if (($g.check_defcit@getValue) == '1')
                        (' where def != 0 ')
                       $else
                        ('')
                      )~
                     order by name_active"

                selaction = "~($X
                                 ($debug 'g.grid32@retrieve')
                                 ($g.grid32@retrieve)
                                 ($debug 'g.grid32@retrieve complete')


                                 ($debug 'g.grid1@retrieve')
                                 ($g.grid1@retrieve)
                                 ($debug 'g.grid1@retrieve complete')

                                 ($debug 'g.grid_plan@retrieve')
                                 ($g.grid_plan@retrieve)
                                 ($debug 'g.grid_plan@retrieve complete')


                                 ($repaint_grid1 0)

                                 ($ret '')
                               )~"


   } // end of ds
} // end of grid
{ splitpanel  type = horizontal percent = 50
{grid
    alias = GRID32
    buttonbar_font_family = 0              buttonbar_font_size = 12
    buttonbar_size = 1                    titlebar_height = 40
    multiselect = no                      editable = yes
    rowsize = 20
          add=""
       addexp=""
          del=""

   {column size=50             title="Код"
        target=id_active
        font_size = 12          font_family=0
        alias = column_id_active_grid32
        visible = no
   }                  
   {column size=100             title="Группа"
        target=name_group
        alias = column_name_group
        title_font_family=2        title_font_color="#000000"
        halignment=left            font_size = 12          font_family=0
        editable = readonly
        visible = no
   }                  
   {column size=160             title="Материал/аналог"
        target=name_active
        title_font_family=2        title_font_color="#000000"
        halignment=left            font_size = 12          font_family=0
        editable = readonly
   }                  
   {column size=150             title="Склад"
        target=sklad
        title_font_family=2        title_font_color="#000000"
        halignment=left            font_size = 12          font_family=0
        editable = readonly
        alias = q_ost
   }                  
   {column size=70             title="Остаток"
        target=ost
        title_font_family=2        title_font_color="#000000"
        halignment=right            font_size = 12          font_family=0 editmask = "# #0.00"
        editable = readonly
        alias = q_ost
   }                  

   {datastore 
           alias = GRIDDS032
           query = "select id_active, name_active, q_proiz, sklad, ost, name_group
                      from (
                                     select active.id_active, active.name_active, department.name1_dep sklad,
                                            util.activeInDocuments(active.id_active,  
                                                                   to_date('~g.n_dat1~', 'dd.mm.yyyy'),
                                                                   to_date('~g.n_dat2~', 'dd.mm.yyyy'),
                                                                   14,
                                                                   id_department
                                                                  ) q_proiz,
                                            doc_lib.getRestBefore(department.id_department,
                                                                  active.id_active,
                                                                  -- текущие
                                                                  1e20 
                                                                 ) ost,
                                            decode(active.parent_act_id, -1, '', active2.name_active) name_group

                                       from active, department, active active2
                                      where (
                                             active.id_active = ~($if (($g.gridds03@size) > 0)
                                                                    ($try ($g.gridds03@getValue 'id_active') $catch ('Any')(-1))
                                                                   $else (-100000000)
                                                                  )~
                                            or active.analog_id = ~($if (($g.gridds03@size) > 0)
                                                                    ($try ($g.gridds03@getValue 'id_active') $catch ('Any')(-1))
                                                                   $else (-100000000)
                                                                  )~
                                            or active.id_active = (select analog_id from active where id_active = 
                                                                    ~($if (($g.gridds03@size) > 0)
                                                                       ($try ($g.gridds03@getValue 'id_active') $catch ('Any')(-1))
                                                                      $else (-100000000)
                                                                     )~
                                                                  )
                                            )
                                       and  department.id_department in (~global.sklad~)
                                       and active.closed = 0
                                       and active2.id_active (+) = active.parent_act_id
                           )
                     ~($if (($g.check_see_null@getValue) == '1')
                        ('  ' )
                       $else
                        (' where ost != 0 ')
                      )~
                     order by name_active"
   } // end of ds
} // end of grid
{grid
    alias = GRID_PLAN
    buttonbar_font_family = 0              buttonbar_font_size = 12
    buttonbar_size = 30                    titlebar_size = 18
    multiselect = no                      editable = no
    rowsize = 20
          add=""
       addexp=""
          del=""
  
    edit = "opennew docs/plan_proiz.rml ~($g.gridds_plan@getValue 'head_proiz.id_head')~"


   {column size=70             title="Дата"
        target=head_proiz.date_sd
        title_font_family=2        title_font_color="#000000"
        halignment=left            font_size = 12          font_family=0
        editable = readonly
   }                  
   {column size=70             title="N док."
        target=head_proiz.num_sd
        title_font_family=2        title_font_color="#000000"
        halignment=left            font_size = 12          font_family=0
        editable = readonly
   }                  
   {column size=70             title="Кол-во"
        target=q_proiz
        title_font_family=2        title_font_color="#000000"
        halignment=right            font_size = 12          font_family=0
        editable = readonly        editmask = "# #0.00"
   }                  
   {column size=200             title="Склад"
        target=dep.name1_dep
        title_font_family=2        title_font_color="#000000"
        halignment=left            font_size = 12          font_family=0
        editable = readonly
   }                  
   {datastore 
           alias = GRIDDS_PLAN
           query = "select head_proiz.id_head, head_proiz.date_sd, head_proiz.num_sd, dep.name1_dep,
                           sum(spc_proiz.q1_spc*spc_spec.q1_spc) q_proiz
                      from head head_proiz, spc spc_proiz, head  head_spec, spc spc_spec, department dep, active active_prod
                     where head_proiz.date_sd between to_date('01.'||to_char(to_date('~g.n_dat1~', 'dd.mm.yyyy'), 'mm.yyyy'), 'dd.mm.yyyy')
                                                  and last_day(to_date('~g.n_dat1~', 'dd.mm.yyyy')) 
                       and head_proiz.id_type_doc = 14
                       and head_proiz.id_department1 in (~global.sklad~)
                       and head_proiz.accept = 1
                       and active_prod.closed = 0
                       and head_proiz.id_head = spc_proiz.id_head
                       and spc_proiz.id_active = active_prod.id_active
                       and head_spec.id_head = active_prod.id_head2
                       and spc_spec.id_head = head_spec.id_head
                       -- этап производства [Заготовка] включает этапы [Заготовка], [Цех1], [Цех2] по спецификации
                       -- этап производства [Сборка] включает этапы [Сборка], [ОТК], [прочие материалы] по спецификации
                       -- либо в производстве указано [все этапы]
                       and ( spc_proiz.work_type = 0
                             or
                             ( spc_proiz.work_type = 1 and spc_spec.work_type in (1, 4, 5) )
                             or
                             ( spc_proiz.work_type = 2 and spc_spec.work_type in (2, 3, 6) )
                           )
                       and head_proiz.id_department1 = dep.id_department
                       and spc_spec.id_active in
                             (
                                select id_active
                                  from active
                                 where id_active = ~($if (($g.gridds03@size) > 0)
                                                      ($try ($g.gridds03@getValue 'id_active') $catch ('Any')(-1))
                                                      $else (-1)
                                                     )~
                                 union all
                                select id_active
                                  from active
                                 where analog_id = ~($if (($g.gridds03@size) > 0)
                                           ($try ($g.gridds03@getValue 'id_active') $catch ('Any')(-1))
                                          $else (-1)
                                         )~
                                  and active.closed = 0
                              )
                     group by head_proiz.id_head, head_proiz.date_sd, head_proiz.num_sd, dep.name1_dep
                     order by dep.name1_dep
                     "

   } // end of ds
} // end of grid
} // horizontal split
} // vertical split

{grid
    alias = GRID1
    buttonbar_font_family = 0              buttonbar_font_size = 12
    buttonbar_size = 30                    titlebar_size = 18
                                           titlebar_height = 40
    multiselect = no                      editable = no
    rowsize = 20
    del = ""

   {menu
      {item label = "Выгрузка в Excel TEST 2"
       action = "~($X
                     ($g.self@doAction 'execute exec big3.analiz_calc2(to_date(\'' + g.n_dat1 + '\', \'dd.mm.yyyy\'), to_date(\'' + g.n_dat2 + '\', \'dd.mm.yyyy\'), ' + ($g.gridds01@getValue 'active.id_active') + ', ' + ($g.check_defcit@getValue) + ')')
                     ($g.ds_all_materials2@retrieve) 
                     (global.datastore_name = 'DS_ALL_MATERIALS2')
                     (global.date1 = g.n_dat1)
                     (global.date2 = g.n_dat2)
                     (global.prod_name = ($g.gridds01@getValue 'active.name_active'))
                     ($g.self@calculate 'js:js_file:/docs/print_ds_analiz_potr3.js')
                     ($ret '')
                   )~"
      }
   }

   {column size=85             title="Код"
        target=active.id_active        title_font_family=2     title_font_color="#000000"
        halignment=left          font_size = 12          font_family=0
   }
   {column size=250             title="Готовая продукция"
        target=active.name_active        title_font_family=2     title_font_color="#000000"
        halignment=left          font_size = 12          font_family=0
   }
   {column size=120             title="<html>Этап<br>производства</html>"
        target=work_stage.name_work_stage        title_font_family=2     title_font_color="#000000"
        halignment=left          font_size = 12          font_family=0
   }

   {column size=80             title="Кол-во"
        target=q_proiz       title_font_family=2     title_font_color="#000000"
        halignment=right         font_size = 12          font_family=0
        editmask = "# #0,"
   }
   {column size=250             title="Материал"
        target=active_mat.name_active        title_font_family=2     title_font_color="#000000"
        halignment=left          font_size = 12          font_family=0
   }
   {column size=80             title="<html>кол-во по<br>спецификации</html>"
        target="SPC_SPEC.Q1_SPC Q_SPEC"       title_font_family=2     title_font_color="#000000"
        halignment=right         font_size = 12          font_family=0
        editmask = "# #0,000"
   }
   {column size=80             title="Дата"
        target=head_proiz.date_sd       title_font_family=2     title_font_color="#000000"
        halignment=center          font_size = 12          font_family=0
   }
   {column size=180             title="Документы"
        target="UTIL.DOCUMENTS_DURING_MONTH(SPC_PROIZ.ID_ACTIVE,14,HEAD_PROIZ.DATE_SD)"      title_font_family=2     title_font_color="#000000"
        halignment=left          font_size = 12          font_family=0
   }
   {column size=250             title="Склады"
        target="UTIL.DEPARTMENTS_DURING_MONTH(SPC_PROIZ.ID_ACTIVE,14,HEAD_PROIZ.DATE_SD)"       title_font_family=2     title_font_color="#000000"
        halignment=left          font_size = 12          font_family=0
   }
   {column size=120             title="<html>Последнее изменение <br>спецификации"
        target="MAX(UTIL.GET_LAST_DATE_MOD(SPC_SPEC.ID_SPC))"       title_font_family=2     title_font_color="#000000"
        halignment=left          font_size = 12          font_family=0
   }
   

   {datastore 
           alias = GRIDDS01
           query = "select active.id_active, active.name_active, active_mat.name_active,
                           sum(spc_proiz.q1_spc) q_proiz,
                           spc_spec.q1_spc q_spec,
                           work_stage.name_work_stage,
                           head_proiz.date_sd, util.documents_during_month (spc_proiz.id_active, 14, head_proiz.date_sd), util.departments_during_month(spc_proiz.id_active, 14, head_proiz.date_sd),
                           max(util.get_last_date_mod(spc_spec.id_spc))
                      from head head_proiz, spc spc_proiz, head  head_spec, spc spc_spec, active, work_stage,
                             (
                                select id_active, name_active
                                  from active
                                 where id_active = ~($if (($g.gridds03@size) > 0)
                                                      ($try ($g.gridds03@getValue 'id_active') $catch ('Any')(-1))
                                                      $else (-1)
                                                     )~
                                                     
                                 union all
                                select id_active, name_active
                                  from active
                                 where analog_id = ~($if (($g.gridds03@size) > 0)
                                           ($try ($g.gridds03@getValue 'id_active') $catch ('Any')(-1))
                                          $else (-1)
                                         )~
                                   and active.closed = 0
                                   
                              ) active_mat

                     where head_proiz.date_sd between to_date('01.'||to_char(to_date('~g.n_dat1~', 'dd.mm.yyyy'), 'mm.yyyy'), 'dd.mm.yyyy')
                                                  and last_day(to_date('~g.n_dat1~', 'dd.mm.yyyy')) 
                       and head_proiz.id_type_doc = 14
                       -- and head_proiz.id_department1 in (~global.sklad~)
                       and head_proiz.accept = 1
                       and head_proiz.id_head = spc_proiz.id_head
                       and spc_spec.id_head = head_spec.id_head
                       and spc_spec.id_active = active_mat.id_active
                       and spc_proiz.id_active = active.id_active
                       and head_spec.id_head = active.id_head2

                       -- этап производства [Заготовка] включает этапы [Заготовка], [Цех1], [Цех2] по спецификации
                       -- этап производства [Сборка] включает этапы [Сборка], [ОТК], [прочие материалы] по спецификации
                       -- либо в производстве указано [все этапы]
                       and ( spc_proiz.work_type = 0
                             or
                             ( spc_proiz.work_type = 1 and spc_spec.work_type in (1, 4, 5) )
                             or
                             ( spc_proiz.work_type = 2 and spc_spec.work_type in (2, 3, 6) )
                           )
                       and spc_spec.work_type = work_stage.id_work_stage

                     group by active.id_active, active.name_active, active_mat.name_active,
                           spc_spec.q1_spc,
                           work_stage.name_work_stage,
                           head_proiz.date_sd, util.documents_during_month (spc_proiz.id_active, 14, head_proiz.date_sd), util.departments_during_month(spc_proiz.id_active, 14, head_proiz.date_sd)

                    --having sum(util.getSpecQuantity(head_proiz.sort, spc_proiz.id_active, spc_spec.id_active, spc_proiz.work_type)) != 0

                     order by active.name_active

                    --order by head_proiz.id_head, spc_proiz.id_spc, spc_spec.id_spc
                    "
              editable = no
   } // end of ds
} // end of grid1

} //end of horizontal split
} //end of horizontal split

{button
  left = 72  width = 50    height = 20
  BACKGROUND = "LIGHTGRAY"    foreground="#000000"
  label = "Выход ESC"
  action = "($g.self@actcancel)"
  shortcut = "escape"
}

} // toolbar
