1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
Option Compare Database
Global z As Boolean
Sub Input_files()
Dim strFile As String, strFilter As String
importTable "DZKZ1", "Выберите файл ДЗ+КЗ"
importTable "Avansy1", "Выберите файл Авансы"
importTable "NachOpl1", "Выберите файл Начисления"
importTable "Oplaty", "Выберите файл Оплаты"
importTable "ROplat", "Выберите файл Реестр оплат"
If IsTable1("DZKZ") = True Then
DeleteTable "DZKZ"
End If
CurrentDb.Execute ("SELECT DISTINCT d2.[Номер договора], d1.[Специалист по расчетам], d1.[Наименование потребителя], d1.[Статус договора] , d1.[ДЗ на начало периода] , d1.[КЗ на начало периода], d2.[Номер месяца] INTO DZKZ FROM DZKZ1 AS d1 right join (select DZKZ1.[Номер договора], Min(DZKZ1.[Номер месяца]) AS [Номер месяца] from DZKZ1 GROUP BY DZKZ1.[Номер договора]) d2 on d1.[Номер договора] = d2.[Номер договора] and d1.[Номер месяца] = d2.[Номер месяца]")
DeleteTable "DZKZ1"
If IsTable1("Avansy") = True Then
DeleteTable "Avansy"
End If
CurrentDb.Execute ("SELECT Avansy1.Отделение, IIf(IsNull([Avansy1]![Номер договора]),0,CDbl([Avansy1]![Номер договора])) AS [Номер договора], Avansy1.[Начисление аванса с НДС], Avansy1.[Дата авансирования], Avansy1.[Номер месяца], Avansy1.Год, Avansy1.[Номер месяца1] INTO Avansy FROM Avansy1")
DeleteTable "Avansy1"
If IsTable1("NachOpl") = True Then
DeleteTable "NachOpl"
End If
CurrentDb.Execute ("SELECT NachOpl1.Отделение, IIf(IsNull([NachOpl1]![Номер договора]),0,CDbl([NachOpl1]![Номер договора])) AS [Номер договора], NachOpl1.[Номер месяца], NachOpl1.Год, NachOpl1.[Тип документа], Sum(NachOpl1.[Сфактурировано с НДС]) AS [Сфактурировано с НДС], NachOpl1.[Код вида реализации] INTO NachOpl FROM NachOpl1 GROUP BY NachOpl1.Отделение, IIf(IsNull([NachOpl1]![Номер договора]),0,CDbl([NachOpl1]![Номер договора])), NachOpl1.[Номер месяца], NachOpl1.Год, NachOpl1.[Тип документа], NachOpl1.[Код вида реализации];")
DeleteTable "NachOpl1"
End Sub
Sub make_mon()
Dim bd As Database
Dim dog As String
Dim m1, m2 As Double
Dim i As Integer
Dim avansy, main, nach As Recordset
Set bd = CurrentDb
CurrentDb.Execute ("delete * from Мониторинг")
CurrentDb.Execute ("INSERT INTO Мониторинг ( [№ договора], Наименование, [КЗ Начало], [ДЗ Начало], [Специалист по расчетам], [Статус договора]) SELECT DZKZ.[Номер договора], DZKZ.[Наименование потребителя], DZKZ.[КЗ на начало периода], DZKZ.[ДЗ на начало периода], DZKZ.[Специалист по расчетам], DZKZ.[Статус договора] FROM DZKZ ORDER BY DZKZ.[Номер договора];")
Set avansy = bd.OpenRecordset("Avansy")
Do Until avansy.EOF
If avansy![Номер месяца] = 12 Then
avansy.Edit
avansy![Номер месяца] = 0
avansy.Update
End If
If avansy![Номер месяца] = 11 Then
avansy.Edit
avansy![Номер месяца] = -1
avansy.Update
End If
avansy.MoveNext
Loop
z = Form_Запуск.Флажок6.Value
If IsTable1("oplatyZ") = True Then
DeleteTable "oplatyZ"
End If
If IsTable1("ROplatZ") = True Then
DeleteTable "ROplatZ"
End If
If z = True Then
CurrentDb.Execute ("SELECT Oplaty.Год, Oplaty.[Номер месяца], CDbl([Oplaty]![Номер договора]) AS Договор, Oplaty.[Всего оплаты] INTO OplatyZ FROM Oplaty where Oplaty.[Номер месяца]<month(date()) ;")
CurrentDb.Execute ("SELECT CDbl(ROplat.Договор) AS Договор, Month([ROplat]![Датаоплаты]) AS Месяц, Sum(ROplat.Суммаоплаты) AS [Oplata] INTO ROplatZ FROM ROplat GROUP BY ROplat.Договор, Month([ROplat]![Датаоплаты]) HAVING (((Month([ROplat]![Датаоплаты]))>=Month(Date())));")
Else
CurrentDb.Execute ("SELECT Oplaty.Год, Oplaty.[Номер месяца], CDbl([Oplaty]![Номер договора]) AS Договор, Oplaty.[Всего оплаты] INTO OplatyZ FROM Oplaty where Oplaty.[Номер месяца]<month(date())-1 ;")
CurrentDb.Execute ("SELECT CDbl(ROplat.Договор) AS Договор, Month([ROplat]![Датаоплаты]) AS Месяц, Sum(ROplat.Суммаоплаты) AS [Oplata] INTO ROplatZ FROM ROplat GROUP BY ROplat.Договор, Month([ROplat]![Датаоплаты]) HAVING (((Month([ROplat]![Датаоплаты]))>=Month(Date())-1));")
End If
For i = 1 To 12
CurrentDb.Execute ("update Мониторинг inner join Avansy on Мониторинг.[№ договора] =Avansy.[Номер договора] set Мониторинг.[0" & i & " 40%] = Avansy.[Начисление аванса с НДС] where Avansy.[Номер месяца1]= " & i & " and Avansy.[Номер месяца]= " & i - 1 & " ")
CurrentDb.Execute ("update Мониторинг inner join Avansy on Мониторинг.[№ договора] =Avansy.[Номер договора] set Мониторинг.[0" & i & " 30%] = Avansy.[Начисление аванса с НДС] where Avansy.[Номер месяца1]= " & i & " and Avansy.[Номер месяца]= " & i - 2 & " ")
CurrentDb.Execute ("update Мониторинг inner join NachOpl on Мониторинг.[№ договора] =NachOpl.[Номер договора] set Мониторинг.[Счет-фактура 0" & i & "] = NachOpl.[Сфактурировано с НДС] where NachOpl.[Номер месяца]= " & i & " and NachOpl.[Тип документа]= 'Счет-фактура' and NachOpl.[Сфактурировано с НДС]<>0")
CurrentDb.Execute ("update Мониторинг inner join NachOpl on Мониторинг.[№ договора] =NachOpl.[Номер договора] set Мониторинг.[Корр СФ 0" & i & "] = NachOpl.[Сфактурировано с НДС] where NachOpl.[Номер месяца]= " & i & " and NachOpl.[Тип документа]= 'Корректировочный счет-фактура'")
CurrentDb.Execute ("UPDATE Мониторинг inner JOIN OplatyZ ON Мониторинг.[№ договора] = OplatyZ.[Договор] SET Мониторинг.[Оплата 0" & i & "] = OplatyZ.[Всего оплаты] WHERE ((([OplatyZ]![Номер месяца])=" & i & "));")
CurrentDb.Execute ("UPDATE Мониторинг inner JOIN ROplatZ ON Мониторинг.[№ договора] = ROplatZ.[Договор] SET Мониторинг.[Оплата 0" & i & "] = ROplatZ.[Oplata] WHERE ((([ROplatZ]![Месяц])=" & i & "));")
CurrentDb.Execute ("UPDATE Мониторинг set Мониторинг.[Сальдо конец 0" & i & "] = Мониторинг.[Счет-фактура 0" & i & "] - Мониторинг.[Оплата 0" & i & "]")
Next i
End Sub
Sub mon_Output()
If Dir("C:\monitoringOutput", vbDirectory) = "" Then
MkDir "C:\monitoringOutput"
End If
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Мониторинг", "C:\monitoringOutput\Мониторинг " & Date & ".xls", True
Shell "explorer.exe " & Path & "C:\monitoringOutput\", vbNormalFocus
End Sub
Sub DeleteTable(tablename As String)
Dim SQLstr As String
SQLstr = "drop table [" & tablename & "];"
DoCmd.RunSQL (SQLstr)
End Sub
Public Function IsTable1(NameTable As String) As Boolean
On Error GoTo IsTable1_Err
Dim var
var = DCount("*", NameTable)
IsTable1 = True
Exit Function
IsTable1_Err:
Select Case Err.Number
Case 4 ' для таблиц
IsTable1 = False
Case Else
' MsgBox Err.Number & ' ' & Err.Description
End Select
End Function
Function GetFilePath(Optional ByVal Title As String = "Выберите файл для обработки", _
Optional ByVal InitialPath As String = "c:\", _
Optional ByVal FilterDescription As String = "Книги Excel", _
Optional ByVal FilterExtention As String = "*.xls*") As String
On Error Resume Next
With Application.FileDialog(msoFileDialogOpen)
.ButtonName = "Выбрать": .Title = Title: .InitialFileName = InitialPath
.Filters.Clear: .Filters.Add FilterDescription, FilterExtention
If .Show <> -1 Then Exit Function
GetFilePath = .SelectedItems(1): PS = Application.p
End With
End Function
Sub importTable(tablename As String, windowstring As String)
Dim strFile As String, strFilter As String
MsgBox windowstring
strFilter = "Excel(*.xlsx)|*.xlsx|"
WizHook.Key = 51488399
WizHook.GetFileName 0, "AppName", windowstring, "", strFile, "c:\", strFilter, 0, 0, 0, True
FName = strFile
If IsTable1(tablename) = True Then
DeleteTable tablename
End If
DoCmd.TransferSpreadsheet aclImport, , tablename, FName, True
End Sub
