Подведение итогов

Мы рассмотрели в блоке все основные программные блоки, использовавшиеся в надстройке Учет производства, в той или иной комбинации.

После изучения материала вы должны уметь разбираться в программном коде любого макроса, вносить необходимые коррективы, создавать свои, новые уникальные макросы.

Помните, что любой даже самый сложный макрос состоит из множества этих элементарных блоков, связанных кодом и логикой.

Я опустил некоторые незначительные программы типа шкалы выполнения, или пользовательских окон данных, т.к. их реализация немного не типична и полезной функции не несет.

Для добавления новых макросов надстройку в файле Учет производства сделайте видимым лист Config и добавляйте в меню свои обработки

Caption - название макроса (так он будет написан в строке меню)

Module - модуль StarBasic в котором он находится

Macro - имя главного макроса.

Затем переустанавливайте надстройку. Создавать макросы (и модули для них) необходимо в модулях самого файла, а не в общих или установленных.

Этот блог следует изучать последовательно с более ранних публикаций (в обратном порядке следования) начиная с введения.

Желаю Вам успехов в автоматизации работы!

P.S. После написания этого блога решил создать подобный, но для программирования под MS Excel на VBA. В нем будут рассмотрены точно такие же стандартные блоки кода. Изучать его следует после изучения этого. Блог доступен по адресу visualbasicexcel.blogspot.ru
Программирование на VBA имеет гораздо больше возможностей и встроенный мощностей. По нему в интернете доступно очень много обучающей литературы. Для более глубокого ознакомления с языком рекомендую поискать книги по запросу "vba" на сайте http://www.twirpx.com/

Создание контрольных листов

В некоторых обработках,связанных с проверкой ошибок данные с различными возможными ошибками отфильтровываются на отдельные листы. Таких листов в книге может быть очень много.
Чтобы упросить проверку и не просматривать каждый лист в отчете, можно создать один контрольный лист на который будут выведенные данные об ошибках со всех листов. На этом листе будет видно, где есть ошибки, а где нет.
Для таких листов активно используются функции SUM - которая подсчитывает кол-во возвращенных единиц и нулей в функциях вида IF(ISERROR). функции COUNTA - которая посчитывает кол-во непустых заполненных строк на отдельным листе (отфильтрованные строки с ошибками) и функции COUNTIF которая проводит суммирование по условию с отдельных листов.
Рассмотрим часть кода для контрольного листа на примере макроса проверки ВП:

ThisComponent.CurrentController.Select(oCell)
ThisComponent.Sheets(7).getCellByPosition (0, 0).String = "Показатель"
ThisComponent.Sheets(7).getCellByPosition (1, 0).String = "Значение"
ThisComponent.Sheets(7).getCellByPosition (0, 1).String = "Кол-во позиций не СП ГМ в ВП Готовой продукции"
ThisComponent.Sheets(7).getCellByPosition (0, 2).String = "Кол-во штучных позиций в ВП Готовой продукции, списанных дробным числом"
ThisComponent.Sheets(7).getCellByPosition (0, 3).String = "Кол-во ГП в ВП между цехами, заведенных с подтипом Внутри подразделения"
ThisComponent.Sheets(7).getCellByPosition (0, 4).String = "Кол-во штучных позиций в ВП с Торгового зала заведенных дробным числом"
ThisComponent.Sheets(7).getCellByPosition (0, 5).String = "Кол-во позиций ГП, перемещенных с Торгового зала"
ThisComponent.Sheets(7).getCellByPosition (0, 6).String = "Из них рецептов Пекарни, перемещенных с ТЗ на Пекарню"
ThisComponent.Sheets(7).getCellByPosition (0, 7).String = "Из них ГП в ВП Автоматическом (приемка товара не на ту карточку)"
ThisComponent.Sheets (7).getCellByPosition(1, 1).Setformula  ("=COUNTA('Не СП ГМ в ВП Готовой продукции'.C2:C65536)")
ThisComponent.Sheets (7).getCellByPosition(1, 2).Setformula  ("=SUM('Штучные позиции в ВП ГП'.M2:M65536)")
ThisComponent.Sheets (7).getCellByPosition(1, 3).Setformula  ("=COUNTA('ГП в ВП Внутри подразделения_'.C2:C65536)")
ThisComponent.Sheets (7).getCellByPosition(1, 4).Setformula  ("=SUM('Штучные с ТЗ'.M2:M65536)")
ThisComponent.Sheets (7).getCellByPosition(1, 5).Setformula  ("=COUNTA('Перемещение ГП с ТЗ'.C2:C65536)")
ThisComponent.Sheets (7).getCellByPosition(1, 6).Setformula ("=(COUNTIF('Перемещение ГП с ТЗ'.I2:I65536;"&Chr(34) & "=Пекарня" &Chr(34) & ")+COUNTIF('Перемещение ГП с ТЗ'.L2:L65536;"&Chr(34) & "=Пекарня" &Chr(34) & "))/2")
ThisComponent.Sheets(7).getCellByPosition (3, 0).String = "Примечание"
If ThisComponent.Sheets (7).getCellByPosition(1, 1).Value > 0 Then
ThisComponent.Sheets(7).getCellByPosition (3, 1).String = "Есть ошибки"
Else ThisComponent.Sheets(7).getCellByPosition (3, 1).String = "Нет ошибок"
End if
If ThisComponent.Sheets (7).getCellByPosition(1, 2).Value > 0 Then
ThisComponent.Sheets(7).getCellByPosition (3, 2).String = "Есть ошибки"
Else ThisComponent.Sheets(7).getCellByPosition (3, 2).String = "Нет ошибок"
End if
If ThisComponent.Sheets (7).getCellByPosition(1, 3).Value > 0 Then
ThisComponent.Sheets(7).getCellByPosition (3, 3).String = "Есть ошибки"
Else ThisComponent.Sheets(7).getCellByPosition (3, 3).String = "Нет ошибок"
End if
If ThisComponent.Sheets (7).getCellByPosition(1, 4).Value > 0 Then
ThisComponent.Sheets(7).getCellByPosition (3, 4).String = "Есть ошибки"
Else ThisComponent.Sheets(7).getCellByPosition (3, 4).String = "Нет ошибок"
End if
If ThisComponent.Sheets (7).getCellByPosition(1, 5).Value <> ThisComponent.Sheets (7).getCellByPosition(1, 6).Value Then
ThisComponent.Sheets(7).getCellByPosition (3, 5).String = "Есть ошибки"
Else ThisComponent.Sheets(7).getCellByPosition (3, 5).String = "Нет ошибок,  либо есть только перемещения ГП с ТЗ по ГП Пекарни на цех Пекарни (х/б на сухари)"
End if
If ThisComponent.Sheets (7).getCellByPosition(1, 6).Value > 0 Then
ThisComponent.Sheets(7).getCellByPosition (3, 6).String = "Есть ошибки"
Else ThisComponent.Sheets(7).getCellByPosition (3, 6).String = "Нет ошибок"
End if

Если рассмотреть этот код можно определить следующую логику построения:
- в первом столбце пишем название ошибки
- во втором с помощью формул подсчитываем данные об ошибках с разных листов
- в третьем, на основе проверки второго пишем - есть или нет ошибок, впрочем трети столбец не обязателен.

Задача: Потренироваться в программном вводе формул  файле реестр документов по МХ. Создайте новый лист и на нем рассчитайте данные:
- общее число заполненных строк на листе 1
- количество операций Питание персонала
- сумму операций с подтипом Утиль производства ГМ
- сумму операций Утиль производства ГМ и Готовой продукции

Использование сложной функции If Then со многими ElseIf

Функция if then которая отвечает за выполнение или не выполнение каких либо действий по определенному условию может иметь несколько вложенных операторов Elseif и быть довольно сложной.

Рассмотрим пример из макроса Комплексного отчета - построение файла данные для проводок. В этом файле столбец "Сумма документа" копируется в несколько смежных столбцов, где затем заголовки меняются на соот. цех, а данные не принадлежащие этому цеху удаляются. Рассмотрим как это реализуется программно:

Sub Dell_Cell

'Определяем текущий диапазон

 oSheet = ThisComponent.CurrentController.getActiveSheet()

 myrows=oSheet.getrows

 oCellCursor = oSheet.createCursor()

 oCellCursor.GotoStartOfUsedArea(True)

 oCellCursor.GotoEndOfUsedArea(True)

 Start_Row = oCellCursor.getRangeAddress.StartRow

 End_Row = oCellCursor.getRangeAddress.EndRow + 1

 For i=End_Row To Start_Row step -1

'Определяем что содержит ячейка с названием цеха в столбце 10

  text1 = osheet.getcellbyposition(9,i).String

'Если она содержит Производство, тогда в этой строчке удаляем данные из ячеек не 

'принадлежащих производству (другие цеха)

   If Left(text1,12) = "Производство" Then

    osheet.getcellbyposition(3,i).String = ""

    osheet.getcellbyposition(5,i).String = ""

    osheet.getcellbyposition(6,i).String = ""

    osheet.getcellbyposition(7,i).String = ""

    'Если она содержит Пекарня, тогда в этой строчке удаляем данные из ячеек не 

'принадлежащих Пекарне(другие цеха)

      ElseIf Left(text1,17) = "Пекарня" Then

    osheet.getcellbyposition(4,i).String = ""

    osheet.getcellbyposition(5,i).String = ""

    osheet.getcellbyposition(6,i).String = ""

    osheet.getcellbyposition(7,i).String = ""

'И так далее...

          ElseIf Left(text1,8) = "Столовая" Then

    osheet.getcellbyposition(3,i).String = ""

    osheet.getcellbyposition(4,i).String = ""

    osheet.getcellbyposition(6,i).String = ""

    osheet.getcellbyposition(7,i).String = ""

     ElseIf Left(text1,18) = "Цех фасовки овощей" Then

     osheet.getcellbyposition(3,i).String = ""

    osheet.getcellbyposition(4,i).String = ""

    osheet.getcellbyposition(5,i).String = ""

    osheet.getcellbyposition(7,i).String = ""

         ElseIf Left(text1,8) = "-" Then

    osheet.getcellbyposition(3,i).String = ""

    osheet.getcellbyposition(4,i).String = ""

    osheet.getcellbyposition(5,i).String = ""

        osheet.getcellbyposition(6,i).String = ""

   End if   

   Next i

   End Sub

Задача: Откройте необработанный файл реестр документов по МХ - скопируйте столбец сумма в два столбцы правее (оставив предварительно пустые столбцы). Откорректируйте приведенный выше макрос по ваш файл и сделайте обработку на цеха Производства, Пекарни, Столовой.

Использование функции Go To при построении сводных таблиц

В некоторых макросах обработки файлов, в середине обработки может возникнуть необходимость построения сводной таблицы, но может оказаться так, что данных для ее построения не будет. Например в макросе проверки не привязанных позиций на каком-либо МХ их может не оказаться и сводная начнет строится на пустых данных. В этом случае ООО аварийно завершит работу, не закончив выполнение кода до конца.

Чтобы программно обойти возможный ошибочный кусок кода можно использовать функцию go to  с условием перенаправления на строку сразу после этого кода. Поясним на примере построения одной из сводных в макросе не привязанных позиций.

Sub Chist_PK ()

oSheet = ThisComponent.createInstance ("com.sun.star.sheet.Spreadsheet")

ThisComponent.Sheets.insertByName ("Ч_Пекарня", oSheet)

' Определим переменную oshipka она будет равна ячейке А2 на листе с отфильтрованными

' данными по которым будет строится наши сводная таблица

oshipka = ThisComponent.Sheets(3).getCellRangeByName("A2")

' Если эта ячейка окажется пуста, значит весь диапазон пуст, т.к. она является первой ячейкой

If oshipka.String = "" Then

'Перенаправить код на Line 1

GoTo Line1

End If

Dim oRange 'Диапазон - источник для сводной таблицы

Dim oRangeAddress'Адрес объекта диапазон

Dim oTables 'Совокупность сводных таблиц

Dim oTDescriptor 'Один описатель сводной таблицы

Dim oFields 'Совокупность всех полей

Dim oField 'Одно поле

Dim oCellAddress As New com.sun.star.table.CellAddress

CreateDataPilotSource = oRange

oRange = CreateDataPilotSource("Сводка")

oSheet = ThisComponent.getSheets().getByIndex(3)

oRangeAddress = ThisComponent.getSheets().getByIndex(3).getCellRangeByName("A1:B65535").getRangeAddress()

oRangeAddress2 = ThisComponent.Sheets (9).getCellByPosition(0, 0).getCellAddress()

oSheet = ThisComponent.Sheets.getByName("Ч_Пекарня")

oTables = oSheet.getDataPilotTables()

REM Шаг 1, создадим описатель

oTDescriptor = oTables.createDataPilotDescriptor()

REM Шаг 2, Зададим исходный диапазон

oTDescriptor.setSourceRange(oRangeAddress)

REM Шаг 3, Зададим поля

oFields = oTDescriptor.getDataPilotFields()

REM Столбец 0 в источнике - Предмет и Я я хочу его как строку Предмет.

oField = oFields.getByIndex(1)

oField.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW

oTables.insertNewByName("MyFirstDataPilot", oRangeAddress2, oTDescriptor)

oSheet = ThisComponent.Sheets (9)

oRangeAddress = oSheet.getCellRangeByName("A1:A3").getRangeAddress()

oSheet.removeRange(oRangeAddress,  com.sun.star.sheet.CellDeleteMode.UP)

 oDoc = ThisComponent 

   oSheet = ThisComponent.getSheets().getByIndex(9) 

    oAddr = oSheet.getCellRangeByName("A1:A65535").getRangeAddress() 

    oDoc.DatabaseRanges.addNewByName("пекарня", oAddr) 

  oRange = oDoc.DatabaseRanges.getByName("пекарня")

 oSheet = ThisComponent.Sheets(9)

 myrows=oSheet.getrows

 oCellCursor = oSheet.createCursor()

 oCellCursor.GotoStartOfUsedArea(True)

 oCellCursor.GotoEndOfUsedArea(True)

 Start_Row = oCellCursor.getRangeAddress.StartRow

 End_Row = oCellCursor.getRangeAddress.EndRow + 1

 For i=End_Row To Start_Row step -1

  text1 = osheet.getcellbyposition(0,i).String

   If Left(text1,4) = "Итог" Then

    myrows.removebyindex(i,1)

   End if   

   Next i

'И выполнение кода начнется с этой строчки, пропуская весь процесс построения сводной

      Line1:

' В на листе, на котором должна была быть сводная просто напишем следующее

   ThisComponent.Sheets(9).getCellByPosition (9, 0).String = "Нет таких позиций" 

End Sub

Задание: простойте простую сводную в файле реестр документов по МХ (произвольную на новый лист) с включенным условием go to которое проверяет содержимое ячейки A1. Проверьте код выполнением. Затем проведите отмену всех изменений и удалите данные из ячейки A1  - снова запустите код - проверьте результат работы и корректность работы функции обхода.

Сохранение данных в разные файлы

В функциях обработок отчетов может возникнуть необходимость сохранить исходный файл со множеством обработанных и расчетных листов в разные файлы (например в макросе комплексной обработки). Сохранение части данных осуществляется путем удаления лишних листов, сохранения файла с нужным именем и отменой удаления этих листов.
Рассмотрим код одного из таких макросов. Из файла Данные для проводок нам нужно получить файл Название ГМ дата списания:

Sub Sox
dim document   as object
dim dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
oSheet = ThisComponent.createInstance ("com.sun.star.sheet.Spreadsheet")
'Удаляем из файла Данные для проводок ненужные листы
ThisComponent.Sheets.removeByName ("Списание ингредиентов ГМ", oSheet)
ThisComponent.Sheets.removeByName ("Списание ингредиентов АР", oSheet)
ThisComponent.Sheets.removeByName ("Питание персонала", oSheet)
ThisComponent.Sheets.removeByName ("Производство", oSheet)
ThisComponent.Sheets.removeByName ("Производство АР", oSheet)
ThisComponent.Sheets.removeByName ("Данные для проводок", oSheet)
' Вызываем стандартную функцию сохранения файла с нужным именем
 If (Not GlobalScope.BasicLibraries.isLibraryLoaded("Tools")) Then
    GlobalScope.BasicLibraries.LoadLibrary("Tools")
  End If
  oDoc = ThisComponent
 sDocURL = oDoc.getURL()
Puti = DirectoryNameoutofPath(sDocURL,"/")
dim args17(2) as new com.sun.star.beans.PropertyValue
args17(0).Name = "URL"
args17(0).Value = Puti & "/" & ThisComponent.Sheets (0).getCellByPosition(0, 0).String & " списания .xls"
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document, ".uno:SaveAs", "", 0, args17())
'Вызываем функции отмены удаления листов, каждая строчка удаляет одно действие
'Так как мы удалили шесть листов строчек тоже будет шесть
dispatcher.executeDispatch(document, ".uno:Undo", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:Undo", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:Undo", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:Undo", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:Undo", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:Undo", "", 0, Array())
oSheet = ThisComponent.createInstance ("com.sun.star.sheet.Spreadsheet")
'Далее мы будем вызывать из файла данные для проводок другие обработки сохранения
'Будут удаляться другие листы.
Sox2
Sox3
End sub

Задача: в любом созданном ранее макросе допишите к конец главного макроса строчки
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document, ".uno:Undo", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:Undo", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:Undo", "", 0, Array())
Данный код будет вызывать отмену трех последних действий - проверьте результат.

Отправка отчетов по электронной почте

Многие отчеты после формирования не требуют какой-либо обработки и предполагают отправку данного отчета сразу по e-mail. Либо же даже в случае обработки конечный отчет все равно отправляется по почте. ООО позволяет автоматизировать и эту часть работы. Существуют две формы отправки таких отчетов сразу без пользовательского окна и полуавтомат, который открываем новое окно в почтовом клиенте с уже вложенным файлом и нужной темой. Можно даже стандартизировать текст тела сообщения, предварительно набрав его в файле и скопировав (напрямую ООО не позволяет писать тело сообщения).
Рассмотрим код из макроса Не привязанные позиции:

Sub Email_Neprivaz
'Сохраняем документ со всеми изменениями
ThisComponent.Store ()
'Объявляем переменные (стандартный блок)
Dim eMailAddress As String
Dim eSubject As String
Dim eMailer As Object
Dim Title As String
Dim Loc As String
Dim eMailClient As Object
Dim eMessage As Object
'Устанавливаем тему сообщения - в тему можно включать содержимое любых ячеек
eSubject = "Отчет Не привязанные позиции ГМ " & ThisComponent.Sheets (0).getCellByPosition(1, 0).String
'Создаем обработчик простой отправки
Title = thisComponent.CurrentController.Frame.Title
eMailer = createUnoService("com.sun.star.system.SimpleSystemMail")
eMailClient = eMailer.querySimpleMailClient()
eMessage = eMailClient.createSimpleMailMessage()
eMessage.setRecipient ("")
eMessage.setSubject (eSubject)
' Пишем, что вложением будет текущий документ
eMessage.setAttachement (Array(convertToUrl(ThisComponent.Location))
'Создаем в нашем документа строки, которые хотим вставить в тело собщения
' потом мы их удалим
ThisComponent.Sheets (0).getCellByPosition(4, 4).String = "Добрый день."
ThisComponent.Sheets (0).getCellByPosition(4, 5).String = "Во вложении Отчет не привязанные позиции ГМ " & ThisComponent.Sheets (0).getCellByPosition(1, 0).String
Sheet = ThisComponent.Sheets(0)
'Устанавливаем для этих строк оптимальную ширину, чтобы корректно скопировать
Columns = Sheet.Columns(4)
Columns.OptimalWidth = True
oCalcDoc = ThisComponent
oCalcCtrl = oCalcDoc.getCurrentController()
oSheet = oCalcDoc.getSheets().getByIndex( 0 )
oCellRanges = oCalcDoc.createInstance( "com.sun.star.sheet.SheetCellRanges" )
'Выделяем нужные нам ячейки
oCellRanges.addRangeAddress( oSheet.getCellRangeByName( "E5:E6" ).getRangeAddress(), False )
oCalcCtrl.select( oCellRanges )
'Вызываем функцию копирования
dim document   as object
dim dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
'Удаляем эти ячейки - они уже не нужны, мы все скопировали в буфер
ThisComponent.Sheets(0).Columns.removeByIndex(4,1)
oCell = ThisComponent.Sheets (0).getCellByPosition(0, 0)
ThisComponent.CurrentController.Select(oCell)
'Вызываем отправку сообщения
eMailClient.sendSimpleMailMessage ( eMessage, com.sun.star.system.SimpleMailClientFlags.DEFAULTS )
End Sub

После выполнения макроса перед вами откроется окно почтового клиента с новым письмом. Вам нужно встать в поле тела сообщения и нажать CTRL+V для того чтобы вставить текст из буфера обмена. Затем указываем адреса и отправляем.

Задача: Сделать макрос для отправки по почте отчета Сверка ДЗЛ за период, со стандартной темой и текстом сообщения.

Создание выпадающего списка значений

В некоторых отчетных формах, которые предполагают дальнейший анализ данных пользователем требуется создание выпадающих списков определенных значений (комментариев). Использование таких списков удобно для стандартизации работы,  а также для подсказок вариантов неопытным пользователям.
Рассмотрим код формирования такого списка из макроса Не привязанные позиции:

Sub SetValidationRange
Dim oRange
Dim oValidation 'Объект проверки
 'Диапазон, который допускает проверку, конечную ячейку можно устанавливать произвольно
' далеко - так чтобы не зная размеров диапазона выпадающие комментарии не закончились
oRange = ThisComponent.Sheets(0).getCellRangeByName("D3:D10000")
'Получим объект проверки
oValidation = oRange.Validation
'Настроим проверку для выполнения
oValidation.Type = com.sun.star.sheet.ValidationType.LIST
oValidation.setOperator(com.sun.star.sheet.ConditionOperator.BETWEEN)
oValidation.setFormula1(""& Chr(34) &"Ошибочное перемещение позиции не по акту заборки"& Chr(34) &";" & Chr(34) & "Неверное МХ в операции внутреннего перемещения"& Chr(34) & ";" & Chr(34) &"Ошибка при заведении операции инвентаризации"& Chr(34) &";"& Chr(34) &"Новое сырье, отправлен запрос на привязку"& Chr(34) &";"& Chr(34) &"Сырье, не используемое в производстве"& Chr(34) &";"& Chr(34) &"Нарушение привязки позиции к рецептам в БД, отправлен запрос на исправление"& Chr(34) &";"& Chr(34) &"Ошибка при заведении операции списания утиля"& Chr(34) &";"& Chr(34) &"Другое"& Chr(34) &"")
' Параметр setFormula2  является по сути последним комментарием, Если вы указали все
' ранее - оставьте это поле пустым
oValidation.setFormula2("Иная причина")
'Теперь установим проверку
oRange.Validation = oValidation
End Sub

Задача: разработайте и запрограммируйте выпадающий список значения для файла Продажи без рецептов