Cp1251
DOC
foreground = lightgray 
document_width = 750
document_height = 550
document_title = "Планирование потребности в материалах"
style = "style/group.st"
preloadscript="
(global.retr = 0)
(global.id_head = 0)
(global.id_active = -1)
(global.num_sd = 'Новый')
"
postloadscript = "($X
($g.grid1@allign)
#($g.grid2@allign)
($g.grid3@allign)
($g.grid4@allign)
($if (($($g.grid1@getDatastore)@size) == 0)
  ($g.info@setValue 'В этом месяце нет утвержденного плана производства.')
)
# раскрашиваем план производства
(i = 0)
(sz = ($g.gridds01@size))
($while (i < sz)
 (
   ($g.gridds01@setCurRow i)
   ($if (($g.gridds01@getValue 'q2_spc') == 0)
     (
       ($g.grid1@setcellbgcolor i 0 'YELLOW')
       ($g.grid1@setcellbgcolor i 1 'YELLOW')
       ($g.grid1@setcellbgcolor i 2 'YELLOW')
     )
    $else
     (
       ($g.grid1@setcellbgcolor i 0 'WHITE')
       ($g.grid1@setcellbgcolor i 1 'WHITE')
       ($g.grid1@setcellbgcolor i 2 'WHITE')
     )
   )
   (i += 1)
 )
)

($ret '')
)

"

{DSCollection
   alias = ds
   SeqQuery = "select spc_sq.nextval, sysdate, id_personal from personal where login = user"
   seqnames = "spk,date,idp"
}

{datastore
 alias = check1
 query = "select spc.id_spc
            from head, spc
           where head.id_head = ~global.id_head~
             and spc.id_head = head.id_head
             and spc.parent_id_spc = ~($if (($g.gridds01@size) > 0)
                                        ($try ($g.gridds01@getValue 'id_active') $catch ('Any')(-1))
                                       $else ('ошибка')
                                      )~
             and spc.id_active in (
                                    -- активы-аналоги данной группы по спецификации
                                    select id_active 
                                      from spc
                                     where id_head = util.getLastSpec( ~($if (($g.gridds01@size) > 0)
                                                                           ($try ($g.gridds01@getValue 'id_active') $catch ('Any')(-1))
                                                                         $else ('ошибка')
                                                                        )~, to_date('01.01.5000', 'dd.mm.yyyy')
                                                                     )
                                       and work_type = 2
                                       and parent_id_spc = ~($if (($g.gridds02@size) > 0)
                                                              ($try ($g.gridds02@getValue 'spc.id_spc') $catch ('Any')(-1))
                                                             $else ('ошибка')
                                                            )~
                                  )"
}
{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"
}


{toolbar   BACKGROUND = "LIGHTGRAY"
{splitpanel type=horizonal     percent=10
{form
  {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  visible=no   alias=n_dat1  type=string 
    exp="($X
           (g.n_dat1 = ($($@date g.dat1)@toPLString))
           ($ret '')
           ($g.grid1@retrieve)
           ($if (($($g.grid1@getDatastore)@size) > 0)
              ($g.grid2@retrieve)
           )
           ($if (($($g.grid2@getDatastore)@size) > 0)
              ($g.grid3@retrieve)
           )
           ($if (($($g.grid1@getDatastore)@size) == 0)
              ($g.info@setValue 'В этом месяце нет утвержденного плана производства.')
            $else
              ($g.info@setValue '')
           )
           ($g.grid4@retrieve)
           # раскрашиваем план производства
           (i = 0)
           (sz = ($g.gridds01@size))
           ($while (i < sz)
            (
              ($g.gridds01@setCurRow i)
              ($if (($g.gridds01@getValue 'q2_spc') == 0)
                (
                  ($g.grid1@setcellbgcolor i 0 'YELLOW')
                  ($g.grid1@setcellbgcolor i 1 'YELLOW')
                  ($g.grid1@setcellbgcolor i 2 'YELLOW')
                )
               $else
                (
                  ($g.grid1@setcellbgcolor i 0 'WHITE')
                  ($g.grid1@setcellbgcolor i 1 'WHITE')
                  ($g.grid1@setcellbgcolor i 2 'WHITE')
                )
              )
              (i += 1)
            )
           )
           ($ret '')
         )" 
  }
  {field
    left = 10 top = 50 width = 200 height = 30 font_size = 16
    type = string   alias = field_name_doc
    visible = no
  }
  { button left = 160 top = 10 width = 100 height = 30 label = "Перейти" foreground = "#000000"
    action = "($X
                 ($g.grid1@retrieve)
                 ($if (($($g.grid1@getDatastore)@size) > 0)
                    ($g.grid2@retrieve)
                 )
                 ($if (($($g.grid1@getDatastore)@size) == 0)
                    ($g.info@setValue 'В этом месяце нет утвержденного плана производства.')
                  $else
                    ($g.info@setValue '')
                 )
                 ($if (($($g.grid2@getDatastore)@size) > 0)
                    ($g.grid3@retrieve)
                 )
                 ($g.grid4@retrieve)
                 # раскрашиваем план производства
                 (i = 0)
                 (yellow = 'no')
                 (sz = ($g.gridds01@size))
                 ($while (i < sz)
                  (
                    ($g.gridds01@setCurRow i)
                    ($if (($g.gridds01@getValue 'q2_spc') == 0)
                      (
                        ($g.grid1@setcellbgcolor i 0 'YELLOW')
                        ($g.grid1@setcellbgcolor i 1 'YELLOW')
                        ($g.grid1@setcellbgcolor i 2 'YELLOW')
                        (yellow = 'yes')
                      )
                     $else
                      (
                        ($g.grid1@setcellbgcolor i 0 'WHITE')
                        ($g.grid1@setcellbgcolor i 1 'WHITE')
                        ($g.grid1@setcellbgcolor i 2 'WHITE')
                      )
                    )
                    (i += 1)
                  )
                 )
                 ($if (yellow == 'yes')
                    ($g.info@setValue 'Желтым помечены новые, либо изменившиеся с последнего планирования материалов, строки плана производства.')
                 )
                 ($ret '')
              )"
  }
  { button left = 300 top = 10 width = 300 height = 30 label = "Внести материалы в план потребностей (Ctrl+T)" foreground = "#000000"
    shortcut = "Ctrl+T"
    action = "($X
                 ($g.zak@retrieve)
                 ($if (($g.zak@getValue 'id_head') == -1)
                   (
                     # в этом месяце еще нет проведенного документа План потребностей в материалах, формируем
                     ($g.self@doAction 'execute exec insert into head (id_type_doc, accept) values (15, 1)')
                     ($g.zak@retrieve)
                     ($if (($g.zak@getValue 'id_head') == -1)
                        (
                          ($g.self@sure 'Внутренняя ошибка формирования документа \"План потребности в материалах\". Обратитесь к программисту.')
                          ($ret '')
                        )
                     )
                  )
                 )
                 (global.id_head = ($g.zak@getValue 'id_head'))
                 ($if (($g.gridds03@size) == 0)
                    (
                      ($g.self@sure 'Список материалов пуст. Проверьте спецификацию производства данной продукции.' 1)
                      ($ret '')
                    )
                 )

                 # прежде, чем перенести материалы в Планирование потребности в материалах, проверяем, не добавлялись ли уже материалы из данной группы
                 # для данной готовой продукции в документ планирования потребностей
                 ($g.check1@retrieve)
                 ($if (($g.check1@size) > 0)
                    (
                      ($g.self@sure 'Материалы из группы \"' + ($g.gridds02@getValue 'spc.comments') + '\" для производства \"' + ($g.gridds01@getValue 'name_active') + '\" уже внесены в документ \"Планирование потребности в материалах\"' 1)
                      ($ret '')
                    )
                 )

                 # проверяем контрольную сумму по аналогам, на основе спецификации данной группы аналогов
                 (sz = ($g.gridds03@size))
                 (i = 0)
                 (s = 0)
                 ($while (i < sz)
                   (
                     ($g.gridds03@setCurRow i)
                     (s += ($g.gridds03@getValue 'spc.q2_spc')/($g.gridds03@getValue 'spc.q1_spc') )
                     ($debug ' i = ' + i + ', q2_spc = ' + ($g.gridds03@getValue 'spc.q2_spc') + ', q1_spc = ' + ($g.gridds03@getValue 'spc.q1_spc'))
                     (i = i + 1)
                   )
                 )
                 ($debug ' s = ' + s)
                 ($if (s != 1)
                    (
                      ($g.self@sure 'Вносимые в документ \"Планирование потребности в материалах\" материалы не соответствуют спецификации данной готовой продукции.' 1)
                      ($ret '')
                    )
                 )

                 # проверки пройдены, добавляем строки в документ Планирование потребности в материалах
                 (i = 0)
                 ($while (i < sz)
                   (
                     ($g.gridds03@setCurRow i)
                     ($if (($g.gridds03@getValue 'spc.q2_spc') > 0)
                       (
                            # parent_id_spc в данном случае хранит ID актива, для производства которого закупается данный материал
                            ($g.self@doAction 'execute exec insert into spc(id_spc, id_head, id_active, id_unit, parent_id_spc, q1_spc)
                                               values (spc_sq.nextval, ' + global.id_head + ', ' + ($g.gridds03@getValue 'spc.id_active') + ', ' + ($g.gridds03@getValue 'spc.id_unit') + ', ' + ($g.gridds01@getValue 'id_active') + ', ' + ($g.gridds03@getValue 'spc.q2_spc') + ')')
                       )
                     )
                     (i = i + 1)
                   )
                 )
                 # помечаем данную готовую продукцию как обработанную
                 ($g.self@doAction 'execute exec update spc set q2_spc = 1 where id_spc = ' + ($g.gridds01@getValue 'id_spc'))
                 ($g.grid4@retrieve)
                 ($g.grid1@retrieve)
                 # раскрашиваем план производства
                 (i = 0)
                 (sz = ($g.gridds01@size))
                 ($while (i < sz)
                  (
                    ($g.gridds01@setCurRow i)
                    ($if (($g.gridds01@getValue 'q2_spc') == 0)
                      (
                        ($g.grid1@setcellbgcolor i 0 'YELLOW')
                        ($g.grid1@setcellbgcolor i 1 'YELLOW')
                        ($g.grid1@setcellbgcolor i 2 'YELLOW')
                      )
                     $else
                      (
                        ($g.grid1@setcellbgcolor i 0 'WHITE')
                        ($g.grid1@setcellbgcolor i 1 'WHITE')
                        ($g.grid1@setcellbgcolor i 2 'WHITE')
                      )
                    )
                    (i += 1)
                  )
                 )
                 ($ret '')
              )"
  }
  { button left = 650 top = 10 width = 300 height = 30 label = "Сформировать документ \"План закупки\"" foreground = "#000000"
    action = "($X
                 ($if (($g.gridds04@size) < 1)
                   (
                     ($g.self@sure 'В этом месяце нет запланированных к закупке материалов.' 1)
                     ($ret '')
                   )
                 )
                 ($g.plan_zak@retrieve)
                 ($if (($g.plan_zak@size) > 0)
                   ($if (($g.self@sure 'В этом месяце уже есть утвержденный план закупки. Вы хотите отменить его и сформировать новый?' 0) == 0)
                     ($ret '')
                   )
                 )
                 ($g.self@doAction 'execute exec util.makePlanZak(to_date(\'01.\'||to_char(to_date(\'' + g.n_dat1 + '\', \'dd.mm.yyyy\'), \'mm.yyyy\'), \'dd.mm.yyyy\'))')
                 ($ret '')
              )"
  }
  {label left = 5 top = 50 width = 900 height = 30 font_size = 16 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

{splitpanel type = horizontal percent = 40
{splitpanel type = vertical percent = 50
{grid
    alias = GRID1
    buttonbar_font_family = 0              buttonbar_font_size = 12
    buttonbar_size = 30                    titlebar_size = 18
    multiselect = no                      editable = yes
    rowsize = 20

   {column size=300             title="Продукция"
        target=name_active        title_font_family=2     title_font_color="#000000"
        halignment=left          font_size = 12          font_family=0
   }
   {column size=100             title="Кол-во"
        target=q_spc        title_font_family=2     title_font_color="#000000"
        halignment=right         font_size = 12          font_family=0
        editmask = "# #0."
   }
   {column size=100             title="Ед. изм"
        target=name_unit        title_font_family=2     title_font_color="#000000"
        halignment=left         font_size = 12          font_family=0
        editmask = "# #0."
   }

   {datastore 
           alias = GRIDDS01
           query = "select id_spc, id_active, name_active, name_unit, q_spc, q2_spc
                      from (
                    select active.id_active, active.name_active, active_pseudo_unit.name_pseudo_unit name_unit, spc.q1_spc q_spc, spc.q2_spc, spc.id_spc
                      from head, active, active_pseudo_unit, 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 spc.id_active = active.id_active
                       and head.id_type_doc = 14
                       and head.accept = 1
                       and head.id_head = spc.id_head
                       and spc.id_unit = active_pseudo_unit.id_pseudo_unit (+)
                           )
                     order by name_active"
              editable = no
               selaction = "~($X
                                ($g.grid2@retrieve)
                                ($if (($($g.grid2@getDatastore)@size) > 0)
                                   ($g.grid3@retrieve)
                                )
                                ($ret '')
                             )~"
   } // end of ds
} // end of grid1
{grid
    alias = GRID2
    buttonbar_font_family = 0              buttonbar_font_size = 12
    buttonbar_size = 30                    titlebar_size = 18
    multiselect = no                      editable = yes
    rowsize = 20
          add=""
    del = ""

   {column size=300             title="Группа материалов"
        target=spc.comments
        title_font_family=0        title_font_color="#000000"
        halignment=left            font_size = 12          font_family=2
        editable = readonly
   }                  
   {datastore 
           alias = GRIDDS02
           query = "select spc.id_spc, spc.id_head, spc.id_active, spc.work_type, spc.comments
                      from spc
                     where spc.id_head = util.getLastSpec(~($if (($g.gridds01@size) > 0)
                                                                 ($try ($g.gridds01@getValue 'id_active') $catch ('Any')(-1))
                                                              $else (-1)
                                                              )~, to_date('01.01.5000', 'dd.mm.yyyy')
                                                           )
                       and spc.work_type = 1
                     order by spc.id_spc"
              editable = no
               selaction = "~($X
                                ($g.grid3@retrieve)
                                ($ret '')
                             )~"
   } // end of ds
} // end of grid2
} //end of split
{splitpanel type = vertical percent = 50
{grid
    alias = GRID3
    buttonbar_font_family = 0              buttonbar_font_size = 12
    buttonbar_size = 30                    titlebar_size = 18
    multiselect = no                      editable = yes
    rowsize = 20
          add=""
       addexp=""
          del=""

   {column size=100             title="spc.id_active"  visible = no
        target=spc.id_active
        alias = id_active_component
   }                  
   {column size=400             title="Материал"
        target=name_active
        alias = name_active_component
        title_font_family=2        title_font_color="#000000"
        halignment=left            font_size = 12          font_family=0
        editable = readonly
   }                  
   {column size=50             title="Кол-во"
        target=spc.q1_spc
        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="Закупить"
        target=spc.q2_spc
        title_font_family=2        title_font_color="#000000"
        halignment=right            font_size = 12          font_family=0 editmask = "# #0.00"
        editable = yes
   }                  
   {column size=100             title="spc.id_unit"  visible = no
        target=spc.id_unit
        alias = id_unit_component
   }                  
   {column size=50             title="Ед. изм."
        target=name_pseudo_unit
        alias = name_unit_component
        title_font_family=2        title_font_color="#000000"
        halignment=left            font_size = 12          font_family=0
        editable = readonly
   }                  
   {datastore 
           alias = GRIDDS03
           query = "select spc.id_spc, spc.parent_id_spc, spc.id_head, spc.id_unit, spc.id_active, name_active,
                           spc.q1_spc, spc.q2_spc, spc.work_type, name_pseudo_unit
                      from (
                    select spc.id_spc, spc.parent_id_spc, spc.id_head, spc.id_unit, spc.id_active, active.name_active,
                           spc.q1_spc*~($if (($g.gridds01@size) > 0)
                                             ($try ($g.gridds01@getValue 'q_spc') $catch ('Any')(-1))
                                           $else (-1)
                                       )~ q1_spc,
                           spc.q2_spc, spc.work_type, active_pseudo_unit.name_pseudo_unit
                      from spc, active, active_pseudo_unit
                     where spc.id_head = ~($if (($g.gridds02@size) > 0)
                                             ($try ($g.gridds02@getValue 'spc.id_head') $catch ('Any')(-1))
                                           $else (-1)
                                          )~
                       and spc.id_active = active.id_active
                       and spc.work_type = 2
                       and spc.id_unit = active_pseudo_unit.id_pseudo_unit (+)
                       and spc.parent_id_spc = ~($if (($g.gridds02@size) > 0)
                                                  ($try ($g.gridds02@getValue 'spc.id_spc') $catch ('Any')(-1))
                                                 $else (-1)
                                                )~
                           )  spc
                     order by spc.id_spc"
   } // end of ds
} // end of grid
{grid
    alias = GRID4
    buttonbar_font_family = 0              buttonbar_font_size = 12
    buttonbar_size = 30                    titlebar_size = 18
    multiselect = no                      editable = yes
    rowsize = 20
    add = ""
    del = "~($X
               ($if (($g.self@sure 'Вы хотите удалить материал из плана потребностей. Если в плане потребностей есть другие аналоги из той же группы для производства \"' + ($g.gridds04@getValue 'active_prod.name_active') + '\", они будут также удалены. Продолжить?' 0) == 0) 
                 ($ret '')
               )
               ($g.self@doAction 'execute exec delete from spc where id_head = util.getPlanPotreb(to_date(\'01.\'||to_char(to_date(\'' + g.n_dat1 + '\', \'dd.mm.yyyy\'), \'mm.yyyy\'), \'dd.mm.yyyy\')) ' +
                                                     ' and parent_id_spc = ' + ($g.gridds04@getValue 'spc.parent_id_spc') + 
                                                     ' and id_active in (
                                    -- аналоги данной группы по спецификации
                                       select spc_analog.id_active
                                         from spc, active, spc spc_group, spc spc_analog
                                        where spc.id_head = util.getLastSpec( ' + ($g.gridds04@getValue 'spc.parent_id_spc') + ', to_date(\'01.01.5000\', \'dd.mm.yyyy\') )
                                          and spc.id_active = active.id_active
                                          and spc.id_active = ' + ($g.gridds04@getValue 'spc.id_active') + '
                                          and spc.work_type = 2
                                          and spc_group.id_head = spc.id_head
                                          and spc_group.work_type = 1
                                          and spc_group.id_spc = spc.parent_id_spc
                                          and spc_analog.id_head = spc.id_head
                                          and spc_analog.work_type = 2
                                          and spc_analog.parent_id_spc = spc_group.id_spc

                                                                        )'
               )
               ($g.grid4@retrieve)
               ($ret '')
            )~"
   {column size=100             title="spc.id_active"  visible = no
        target=spc.id_active
        alias = id_active_component
   }                  
   {column size=200             title="Готовая продукция"
        target=active_prod.name_active
        title_font_family=2        title_font_color="#000000"
        halignment=left            font_size = 12          font_family=0
        editable = readonly
   }                  
   {column size=300             title="Материал"
        target=active.name_active
        alias = name_active_component
        title_font_family=2        title_font_color="#000000"
        halignment=left            font_size = 12          font_family=0
        editable = readonly
   }                  
   {column size=70             title="Кол-во"
        target=spc.q1_spc
        title_font_family=2        title_font_color="#000000"
        halignment=right            font_size = 12          font_family=0
        editable = readonly              editmask = "# #0.00"
   }                  
   {column size=100             title="spc.id_unit"  visible = no
        target=spc.id_unit
        alias = id_unit_component
   }                  
   {column size=70             title="Ед. изм."
        target=active_pseudo_unit.name_pseudo_unit
        alias = name_unit_component
        title_font_family=2        title_font_color="#000000"
        halignment=left            font_size = 12          font_family=0
        editable = readonly
   }                  
   {datastore 
           alias = GRIDDS04
           query = "select spc.id_spc, spc.parent_id_spc, spc.id_head, spc.id_unit, spc.id_active, active.name_active, spc.q1_spc, spc.work_type, active_pseudo_unit.name_pseudo_unit,
                           head.num_sd, active_prod.name_active
                      from head, spc, active, active_pseudo_unit, active active_prod
                     where head.id_head = util.getPlanPotreb(to_date('01.'||to_char(to_date('~g.n_dat1~', 'dd.mm.yyyy'), 'mm.yyyy'), 'dd.mm.yyyy'))
                       and head.id_head = spc.id_head
                       and spc.id_active = active.id_active
                       and spc.parent_id_spc  = active_prod.id_active
                       and spc.id_unit = active_pseudo_unit.id_pseudo_unit (+)
                     order by active_prod.name_active, active.name_active"
              editable = no
   } // end of ds
} // end of grid4
} //end of split
} //end of split

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

} // toolbar
