frmConsumoLoteGraf.frm
5.82 KB
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
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
VERSION 5.00
Object = "{65E121D4-0C60-11D2-A9FC-0000F8754DA1}#2.0#0"; "MSCHRT20.OCX"
Begin VB.Form frmConsumoLoteGraf
BorderStyle = 4 'Fixed ToolWindow
Caption = "Grafico"
ClientHeight = 4905
ClientLeft = 45
ClientTop = 315
ClientWidth = 6330
LinkTopic = "Form2"
MaxButton = 0 'False
MinButton = 0 'False
ScaleHeight = 4905
ScaleWidth = 6330
ShowInTaskbar = 0 'False
StartUpPosition = 2 'CenterScreen
Begin MSChart20Lib.MSChart MSChart1
Height = 4935
Left = 0
OleObjectBlob = "frmConsumoLoteGraf.frx":0000
TabIndex = 0
Top = 0
Width = 6375
End
End
Attribute VB_Name = "frmConsumoLoteGraf"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
Public Sub init(Periodo1 As Integer, Periodo2 As Integer, Ano1 As Integer, Ano2 As Integer, _
CLASSIFICACAO_FISCAL As String, NRO_LIGACAO As String, Agrupado As Boolean)
On Error GoTo Trata_Erro
Screen.MousePointer = vbHourglass
Dim str As String
Dim i As Integer, j As Integer
Dim rs As New ADODB.Recordset
Dim gu1 As String
Dim gu2 As String
Dim gu3 As String
Dim gu4 As String
Dim gu5 As String
Dim gu6 As String
Dim gu7 As String
Dim gu8 As String
Dim gu9 As String
Dim gu10 As String
Dim gu11 As String
gu1 = "QUERYSTRING"
gu2 = "GS_QUERYS_CLIENT"
gu3 = "CLIENT_ID"
gu4 = "QUERY_ID"
gu5 = "ligacao"
gu6 = "histconsumo"
gu7 = "PERIODO"
gu8 = "ano"
gu9 = "NRO_LIGACAO"
gu10 = "CLASSIFICACAO_FISCAL"
gu11 = "CONSUMO_MEDIDO"
If frmCanvas.TipoConexao <> 4 Then
If Agrupado Then
Set rs = Conn.execute("SELECT querystring from gs_querys_client where client_id=3 and query_id=5")
str = rs.Fields("querystring").value
str = Replace(str, "LI.CLASSIFICACAO_FISCAL = '' and", "")
str = Replace(str, "@CLASSIFICACAO_FISCAL", CLASSIFICACAO_FISCAL)
str = Replace(str, "@PERIODO1", Periodo1)
str = Replace(str, "@PERIODO2", Periodo2)
str = Replace(str, "@ANO1", Ano1)
str = Replace(str, "@ANO2", Ano2)
str = Replace(str, "@NRO_LIGACAO", NRO_LIGACAO)
'Imprima str
rs.Close
Else
MsgBox "Você só pode apresentar gráfico de apenas uma linha", vbExclamation
Exit Sub
str = "SELECT h.PERIODO || '/' || h.ano as Mes_Ano,"
str = str & " l.NRO_LIGACAO,"
str = str & " h.CONSUMO_MEDIDO as M3"
str = str & " from ligacao l, histconsumo h"
str = str & " Where l.NRO_LIGACAO = h.NRO_LIGACAO And h.ANO >= " & Ano1
str = str & " and h.PERIODO >= " & Periodo1 & " and h.ANO <= " & Ano2 & " and h.PERIODO <= " & Periodo2
str = str & " and l.CLASSIFICACAO_FISCAL = '" & CLASSIFICACAO_FISCAL & "'"
str = str & " and l.NRO_LIGACAO IN(" & NRO_LIGACAO & ")"
End If
Else
If Agrupado Then
Set rs = Conn.execute("SELECT " + """" + gu1 + """" + " from " + """" + gu2 + """" + " where " + """" + gu3 + """" + "='3' and " + """" + gu4 + """" + "='5'")
str = rs.Fields("querystring").value
str = Replace(str, "LI.CLASSIFICACAO_FISCAL = '' and", "")
str = Replace(str, "@CLASSIFICACAO_FISCAL", CLASSIFICACAO_FISCAL)
str = Replace(str, "@PERIODO1", Periodo1)
str = Replace(str, "@PERIODO2", Periodo2)
str = Replace(str, "@ANO1", Ano1)
str = Replace(str, "@ANO2", Ano2)
str = Replace(str, "@NRO_LIGACAO", NRO_LIGACAO)
'Imprima str
rs.Close
Else
MsgBox "Você só pode apresentar gráfico de apenas uma linha", vbExclamation
Exit Sub
str = "SELECT " + """" + gu6 + """" + "." + """" + gu7 + """" + " || '/' || " + """" + gu6 + """" + ".ano as Mes_Ano,"
str = str & " " + """" + gu5 + """" + "." + """" + gu9 + """" + ","
str = str & " " + """" + gu6 + """" + "." + """" + gu11 + """" + " as " + """" + " M3" + """" + ""
str = str & " from " + """" + gu5 + """" + " , " + """" + gu6 + """" + " "
str = str & " Where " + """" + gu5 + """" + "." + """" + gu9 + """" + " = " + """" + gu6 + """" + "." + """" + gu9 + """" + " And " + """" + gu6 + """" + "." + """" + gu8 + """" + " >= " & Ano1
str = str & " and " + """" + gu6 + """" + "." + """" + gu7 + """" + " >= " & Periodo1 & " and " + """" + gu6 + """" + "." + """" + gu8 + """" + " <= " & Ano2 & " and " + """" + gu6 + """" + "." + """" + gu7 + """" + " <= " & Periodo2
str = str & " and " + """" + gu5 + """" + "." + """" + gu10 + """" + " = '" & CLASSIFICACAO_FISCAL & "'"
str = str & " and " + """" + gu5 + """" + "." + """" + gu9 + """" + " IN(" & NRO_LIGACAO & ")"
End If
End If
rs.CursorType = 3
rs.Open str, ConnSec, adOpenDynamic, adLockOptimistic
With MSChart1
.chartType = VtChChartType2dLine
.ColumnCount = rs.Fields.Count - 1
.RowCount = rs.RecordCount
.ShowLegend = False
i = 1
While Not rs.EOF
.Row = i
For j = 1 To .ColumnCount
.Column = j
.ColumnLabel = rs.Fields(j).Name
.Data = IIf(IsNull(rs.Fields(j).value), 0, rs.Fields(j).value)
Next
.RowLabel = rs.Fields(0).value
rs.MoveNext
i = i + 1
Wend
End With
rs.Close
Set rs = Nothing
Screen.MousePointer = vbNormal
Me.Show vbModal
Trata_Erro:
Screen.MousePointer = vbNormal
If Err.Number = 0 Or Err.Number = 20 Then
Resume Next
Else
PrintErro CStr(Me.Name), "Private Sub Init", CStr(Err.Number), CStr(Err.Description), True
End If
End Sub