Глава 17. Задача за “Специфициране на материали” с решение чрез VBA, ADO и SQL Обща постановка, проект на база от данни и идея за алгоритъм
Някои задачи изискват работа с позаписно процедурно програмиране. Такава например е задачата за Специфициране на материали, известна в литературата като Bill of materials. За да се определи например общото количество от всеки материал за производството на изделията, не е достатъчно само познаването на производствената програма. Ако това е една монтажна спецификация, необходимо е да се изпълни алгоритъм, който да определи общия брой от всеки детайл в едно изделие. И тъй като даден детайл може да участвува в много възли, които от своя страна да участвуват в други по-високо стоящи възли, налага се една рекурсивна обработка на отделните мрежово свързани записи. В този случай базата от данни се изгражда в Access, а съответното извеждане се прави с процедурен език, напр. VBA. За отразяването на йерархичните връзки се организира отделна таблица на структурата (Struktura) – фиг. 17.1.
Фиг. 17.1. Проект на таблица за структурата на монтажно изделие.
Както се вижда структурната схема е мрежова:
Конкретните данни за структурата на изделието са показани на фиг. 17.2.
Фиг. 17.2. Данни за таблица Struktura.
Тук под Продукт No (ProductNom ) се разбира съставен компонент – изделие или възел, който съдържа друг съставен или не компонент, носещ името Детайл No (DetailNom). В същност всеки ред на таблицата съдържа една йерархическа връзка – един компонент-родител съдържа друг компонент- наследник в някакво количество, различно от нула. Затова един и същ компонент може да се срещне и като Продукт No и като Детайл No – например виж компонент 70300. Също така един и същ Детайл No може да се срещне и в различни Продукт No – например виж компонент 070107. Това показва, че този компонент има повече от един родители и е носител на мрежова връзка. Главният ключ е съставен: Изделие (Izdelie), Продукт No (ProductNom ), Детайл No (DetailNom). Именно тези особености изискват използването на позаписно ориентиран програмен език. Свойството Унифициран (Unificiran) показва дали съответният наследник се взема като готов недекомпозиран продукт.
Проектът на таблицата за всички компоненти (детайли, възли и изделия) Komponenti и данните за тях са дадени съответно на фиг. 17.3. и фиг. 17.4. Главният ключ е Детайл No (DetailNom). Тук разходната норма (RazhodNorm) е в мярка kg. За да се спазят изискванията за цялостност (всеки Детайл No от таблица Struktura да е включен предварително в таблица Komponenti), тук като компоненти са включени и всички възли и изделия. Тяхната разходна норма е приета условно с нулева стойност, а за стандартните компоненти като гайки, винтове, болтове, шпилки и други подобни - единица. Полето за материал в таблицата Komponenti за възлите и изделията има същата стойност както полето за наименование, тъй като в този случай двете полета са с идентични стойности.
Фиг. 17.3. Проект за таблица Komponenti.
Фиг.17.4. Данни за таблица Komponenti
За да може да се създаде спецификация на материалите, е необходима и таблица за производствената програма - Programa. Проектът за нея е показан на фиг. 17.5, а конкретните данни – на фиг. 17.6. Ако искаме да се спазят изискванията за цялост (всеки Продукт No от таблица Struktura да е включен предварително в таблица Programa), тук като продукти трябва да се включат и всички възли. В този случай обаче тяхното количество условно трябва да се зададе с нулева стойност.
Като имаме пред вид броя на детайлите и продуктите, решаваме техните номенклатури да са изградени на фасетен принцип, кодът да е шестпозиционен, изграден по следната схема на кодиране:
ХХХ ХХХ
½ ½____________ типоразмер
½___________________ разновидност, габарит
Фиг. 17.5. Проект на таблица Programa.
Фиг. 17.6. Данни за таблица Programa.
Концептуалният модел на базата от данни е даден на фиг. 17.7.
Фиг. 17.7. Концептуален модел на база от данни за специфициране на материали
17.2. Решение чрез ACCESS с VBA, ADO и SQL
Решението на задачата на основата на базата от данни с таблици и входни данни представени в глава 17.1. може да се постигне чрез ACCESS при използване на Visual Basic for Application (VBA), ActiveX Data Objects (ADO)
и Structured Query Language (SQL). Последователността на конкретното решение има следните осем стъпки:
17.2.1. Актуализация на входните данни в таблици: Struktura, Komponenti и Programa
Актуализацията, ако е необходима, може да се направи с директно внасяне на данни в таблиците.
17.2.2. Проектиране на формуляр ObKol за поддръжка на таблица Struktura и добавяне към този формуляр на бутон за стартиране на всички обработки и изчисления в последователността на решението.
Проектирането на формуляра се постига по примера даден в глава 12. Кодът “2” в поле Unificiran означава, че детайлът е унифициран (структурата на такъв детайл вече е записана в таблица Struktura). Затова унифицираните детайли в таблицата се записват съкратено (без да се записва тяхната структура, ако имат такава).
Фиг. 17.8. Формуляр за поддръжка на данните в таблица Struktura и бутон за пускане в действие на изчисляването на разхода на материали.
В режим Design (за формуляра) бутонът „Изчисляване на разход на материали” (с име на контрола Command10), трябва чрез контекстното меню Properties / Event / On Click (събитие - щракване с мишката) да бъде свързан с процедура за обработка на събитие - Event Procedure. Тази процедура в конкретното решение се нарича Command10_Click.
17.2.3. Проектиране на работна таблица ObshtoKolich за запазване на междинните резултати при изчислителната работа в решението.
Фиг. 17.9. Работната таблица ObshtoKolich в режим на проектиране.
Работната таблица е подобна на таблица Struktura и съдържа няколко допълнителни полета. Проектирането е показано на фиг. 17.9.
17.2.4. Решение на задачата чрез програмен код написан на Visual Basic for Application (с ADO и SQL).
Програмният код - Form ObKol Code e свързан с формата – ObKol и се състои от четири части.
-
Първата част на кода е записана в областта на декларациите – General Declarations. Декларирани са масив и променливи, две от които се свързани с възможността да се осигури достъп до таблиците на базата от данни чрез активния обект - ADODB. Обектите от тип ActiveX Data Objects (ADO) дават възможност (през OLE DB доставчик) да се отвори клиентско приложение за достъп и манипулация в базата данни, намираща се на сървър. В тази част се съдържа следният код:
Option Compare Database
Dim mas(1000, 9)
Dim TableIn, TableOut As String
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim SQL_x As String
-
Втората част на кода е подпрограма - Command10_Click
записана в областта Form Click и се изпълнява при възникване на събитието On Click (щракване с мишката при натискане на бутона във формата). Тази подпрограма задава имена на входната и изходната – резултатната таблици и извиква (Call) за изпълнение подпрограмата - CreateTab. В тази част се съдържа следният код:
'Ïðè íàòèñêàíå íà áóòîí
Private Sub Command10_Click()
TableIn = "Struktura"
TableOut = "ObshtoKolich"
Call CreateTab
End Sub
-
Третата част на кода е подпрограма - CreateTab, записана в областта General CreateTab Тя се изпълнява след извикване от подпрограма Command10_Click. В CreateTab са реализирани основните стъпки на решението. Използвани са обекти на ADO и заявки на SQL. Действието на частите на тази подпрограма е коментирано в нейния код:
Private Sub CreateTab()
‘ Контролиране на възникването на грешки при изпълнение
On Error GoTo Err_CreateTab
'Код на декларациите
Dim Izdelie, ProduktNom, DetailNom, Unificiran As String
Dim Kolichestvo, Rod As Integer
Dim Programa As Integer
Dim ObshtoKolichestvo As Double
Set conn = CurrentProject.Connection
' open client-side recordset
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
'Èçïúëíåíèå íà SQL çàÿâêà çà èçòðèâàíe íà записите â òàáëèöà ObshtoKolich
SQL_x = "delete from " & TableOut
rst.Open SQL_x, conn, adOpenStatic, adLockReadOnly
'Èçïúëíåíèå íà SQL çàÿâêà çà ïðåõâúðëÿíå íà äàííè
‘от òàáëèöà Struktura â òàáëèöà ObshtoKolich
SQL_x = "INSERT INTO " & TableOut & "(Izdelie, ProduktNom,DetailNom,Unificiran,Kolichestvo) " _
& " SELECT Izdelie,ProduktNom,DetailNom,Unificiran,Kolichestvo from " & TableIn
rst.Open SQL_x, conn, adOpenStatic, adLockReadOnly
DoCmd.Close acTable, TableOut, acSaveYes
'Èçïúëíåíèå íà SQL çà ñîðòèðàíå íà òàáëèöà ObshtoKolich
SQL_x = TableOut
rst.Open SQL_x, conn, adOpenKeyset, adLockOptimistic, adCmdTable
' sort the recordset ascending
rst.Sort = "Izdelie ASC, DetailNom ASC, ProduktNom ASC "
' Çàòâàðÿíå íà îòâîðåíèòå äî ìîìåíòà òàáëèöè è çàÿâêè
DoCmd.Close acTable, TableOut, acSaveYes
DoCmd.Close acQuery, "ObshtoKolichQuery", acSaveYes
DoCmd.Close acQuery, "RazhodMateriasliQuery", acSaveYes
' Èçïúëíåíèå íà ïðîöåäóðà çà áðîåíå íà çàïèñèòå â TableOut
Call CountRecords(CountRec)
' Ïðî÷èòàíå íà äàííèòå îò TableOut è çàïèñване â масив MAS ===========
kursor2 = 1 '
Set conn = CurrentProject.Connection
Set rst = New ADODB.Recordset
SQL_x = TableOut
rst.Open SQL_x, conn, adOpenStatic, adLockReadOnly
RowKursor = 0
rst.MoveFirst
Do Until rst.EOF
RowKursor = RowKursor + 1
mas(RowKursor, 1) = rst!Izdelie
mas(RowKursor, 2) = rst!ProduktNom
mas(RowKursor, 3) = rst!DetailNom
mas(RowKursor, 4) = rst!Unificiran
mas(RowKursor, 5) = rst!Kolichestvo
mas(RowKursor, 6) = rst!Kolichestvo
mas(RowKursor, 7) = ""
mas(RowKursor, 8) = ""
mas(RowKursor, 9) = 0
rst.MoveNext
Loop
RowKursor = RowKursor + 1
mas(RowKursor, 1) = 9999
rst.Close 'end read ot TableOut =====================
CountRec = RowKursor
' Ïîïúëâàíå ñ äàííè íà ïîëåòî Rod за определяне на номера на предходния детайл в структурата.
‘<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
kursor2 = 0
Do While mas(kursor2, 1) <> 9999
kursor2 = kursor2 + 1
Izdelie2 = mas(kursor2, 1)
ProduktNom2 = mas(kursor2, 2)
DetailNom2 = úmas(kursor2, 3)
kursor3 = 1
Do While mas(kursor3, 1) <> 9999
izdelie3 = mas(kursor3, 1)
ProduktNom3 = mas(kursor3, 2)
DetailNom3 = mas(kursor3, 3)
If ProduktNom3 = DetailNom2 And izdelie3 = Izdelie2 Then
mas(kursor3, 9) = kursor2 ' Rod
End If
kursor3 = kursor3 + 1
Loop
loop1: Loop ' >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
' Ïîêàçâàíå - ðàçãúâàíå íà ñòðóêòóðàòà íà óíèôèöèðàíèòå äåòàéëè
kursor2 = 0
Do While mas(kursor2, 1) <> 9999
kursor2 = kursor2 + 1
If IsNull(mas(kursor2, 4)) Then GoTo loopa
If mas(kursor2, 4) <> "2" Then GoTo loopa
Izdelie2 = mas(kursor2, 1)
ProduktNom2 = mas(kursor2, 2)
DetailNom2 = mas(kursor2, 3)
Unificiran2 = mas(kursor2, 4)
Kolichestvo2 = mas(kursor2, 5)
Rod2 = mas(kursor2, 9)
kursor3 = 1
Do While mas(kursor3, 1) <> 9999
izdelie3 = mas(kursor3, 1)
ProduktNom3 = mas(kursor3, 2)
DetailNom3 = mas(kursor3, 3)
Unificiran3 = mas(kursor3, 4)
Kolichestvo3 = mas(kursor3, 5)
rod3 = mas(kursor3, 9)
If ProduktNom3 = DetailNom2 And Unificiran3 = "1" Then
mas(CountRec, 1) = Izdelie2
mas(CountRec, 2) = ProduktNom3
mas(CountRec, 3) = DetailNom3
mas(CountRec, 4) = "2" 'Unificiran
mas(CountRec, 5) = Kolichestvo3
mas(CountRec, 6) = Kolichestvo3
mas(CountRec, 9) = kursor2 'Rod
mas(CountRec + 1, 1) = 9999
CountRec = CountRec + 1
End If
kursor3 = kursor3 + 1
Loop 'za tarsene v cialata tablica na ProduktNom
'(ot Izdelie2) raven na DetailNom2
loopa: Loop ' >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
‘Изчисляване на количеството
MultiKolichestvo: kursor3 = 0
Do While mas(kursor3, 1) <> 9999
kursor3 = kursor3 + 1
rod3 = mas(kursor3, 9)
If rod3 = 0 Then GoTo loop3
kursor4 = kursor3
Do While mas(kursor4, 1) <> 9999
KursorRod = mas(kursor4, 9)
If KursorRod = 0 Then Exit Do
mas(kursor3, 6) = mas(kursor3, 6) * mas(KursorRod, 5)
kursor4 = mas(kursor4, 9)
Loop
loop3: Loop ' >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
'Çàïèñ íà ðåçóëòàòите îò MAS â TableOut <<<<<<<<<<<<<<<<<
SQL_x = "delete from " & TableOut
rst.Open SQL_x, conn, adOpenStatic, adLockReadOnly
DoCmd.Close acTable, TableOut, acSaveYes
SQL_x = TableOut
rst.Open SQL_x, conn, adOpenKeyset, adLockOptimistic, adCmdTable
RowKursor = 1
Do
If mas(RowKursor, 1) = 9999 Then Exit Do
rst.AddNew
rst!Izdelie = mas(RowKursor, 1)
If IsEmpty(mas(RowKursor, 2)) Then Exit Do
rst!ProduktNom = mas(RowKursor, 2)
rst!DetailNom = mas(RowKursor, 3)
rst!Unificiran = mas(RowKursor, 4)
rst!Kolichestvo = mas(RowKursor, 5)
rst!ObshtoKolichestvo = mas(RowKursor, 6)
'rst!Programa = mas(RowKursor, 7)
rst!Sleda = RowKursor
rst!Rod = mas(RowKursor, 9)
rst.Update
RowKursor = RowKursor + 1
Loop '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
DoCmd.Close acTable, TableOut, acSaveYes
DoCmd.SetWarnings False
' Çàÿâêà (активна) çà âúâåæäàíå â òàáëèöà ObshtoKolich
' íà êîëè÷eñòâî îò òàáëèöà Programa
DoCmd.OpenQuery "ProgramaUpdateQuery", , acEdit
' Îòâàðÿíå íà òàáëèöà TableOut
DoCmd.OpenTable TableOut
' Çàÿâêà çà èç÷èñëÿâàíå íà ObshtoKolich
DoCmd.OpenQuery "ObshtoKolichQuery", , acEdit
' Çàÿâêà çà èç÷èñëÿâàíå íà RazhodMateriali
DoCmd.OpenQuery "RazhodMaterialiQuery", , acEdit
DoCmd.SetWarnings True
Exit_CreateTab:
Exit Sub
Err_CreateTab:
MsgBox Err.Description, vbCritical, "ÑÌ"
Resume Exit_CreateTab
End Sub
-
Четвъртата част на кода е подпрограма - CountRecords, записана в областта General CountRecords Тя се изпълнява след извикване от подпрограма CreateTab. Чрез CountRecords се изброяват записите в TableOut. Действието на тази подпрограма е показано в нейния код:
'Подпрограма за иçáðîÿâàíå íà çàïèñèòå â TableOut
Private Sub CountRecords(RowKursor)
Set conn = CurrentProject.Connection
Set rst = New ADODB.Recordset
SQL_x = TableOut
rst.Open SQL_x, conn, adOpenKeyset, adLockOptimistic, adCmdTable
RowKursor = 0
Do Until rst.EOF
RowKursor = RowKursor + 1
rst.MoveNext
Loop
msg1 = "Count Records!: " & Str(RowKursor)
MsgBox msg1, vbÎÊ, "ÑÌ"
rst.Close
End Sub
17.2.5. Проектиране и действие на заявка - ProgramaUpdateQuery за въвеждане в таблица ObshtoKolich на количеството от таблица Programa
Заявката за промяна – ProgramaUpdateQuery на данни в таблица ObshtoKolich се изгражда по примера в глава 9.7. Въвежда се стойността на поле Kolichestvo в поле Programa. Проектирането е показано на фиг. 17.10.
Фиг. 17.10. Заявката ProgramaUpdateQuery в Design View.
Заявката ProgramaUpdateQuery се изпълнява след извикване в подпрограма CreateTab.
17.2.6. Показване на съдържанието на помощната таблица ObshtoKolich след действието на подпрограма CreateTab и заявката ProgramaUpdateQuery
Задействането на показването на таблица ObshtoKolich се прави от подпрограма CreateTab чрез команда: DoCmd.OpenTable TableOut
Фиг. 17.11. Таблица ObshtoKolich с резултати.
17.2.7. Проектиране и действие на заявка - ObshtoKolichQuery за изчисляване в таблица ObshtoKolich на общото количество и разхода на материали по детайли.
Заявката ObshtoKolichQuery за за изчисляване от таблица ObshtoKolich на общото количество и разхода на материали се изгражда по примера в глава 9. Проектирането е показано на фиг. 17.12.1 и фиг. 17.12.2. Заявката ProgramaUpdateQuery се изпълнява след извикване в подпрограма CreateTab.
Фиг. 17.12.1. Заявка ObshtoKolichQuery – Design View част 1.
Фиг. 17.12.2. Заявка ObshtoKolichQuery – Design View част 2.
Фиг. 17.13. Резултати в заявка ObshtoKolichQuery – Datasheet View.
17.2.8. Проектиране и действие на заявка - RazhodMaterialiQuery за сумиране по имена на материали на разхода на материали от заявка ObshtoKolichQuery
Заявката RazhodMaterialiQuery показва сумираните разходи, сортирани и групирани по име на материал. Тя е проектирана (фиг. 17.14.) по примера в глава 9. Заявката RazhodMaterialiQuery се изпълнява след извикване в подпрограма CreateTab чрез команда:
DoCmd.OpenQuery "RazhodMaterialiQuery", , acEdit
Заявката RazhodMaterialiQuery представя крайния резултат на решението на задачата (фиг. 17.15.).
Фиг. 17.14. Заявка RazhodMaterialiQuery – Design View.
Фиг. 17.15. Крайният резултат на решението в заявка RazhodMaterialiQuery – DatasheetView.
Част IV. SQL в ACCESS и Многопотребителски бази от данни
Сподели с приятели: | 
