-
Notifications
You must be signed in to change notification settings - Fork 1
/
VLDMATRIC.prw
225 lines (187 loc) · 7.63 KB
/
VLDMATRIC.prw
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
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
#Include "TOTVS.ch"
#Include "TOPCONN.ch"
#Include "FWMVCDEF.ch"
//-------------------------------------------------------------------
/*/{Protheus.doc} VldMatric
Disponibiliza o browser para ajuste das matriculas incorretas
@Author Thiago Fernandes da Silva
@Type User Function
@Since 15/10/2019
@Version 1.0
/*/
//-------------------------------------------------------------------
Function U_VldMatric()
Local oBrowse := FwLoadBrw("VLDMATRIC")
oBrowse:Activate()
Return (NIL)
//-------------------------------------------------------------------
/*/{Protheus.doc} MenuDef
Definição das operações da rotina
@Author Thiago Fernandes da Silva
@Type Static Function
@Since 15/10/2019
@Version 1.0
@Return aMenu, Array, Object, Vetor contendo as operações da rotina
/*/
//-------------------------------------------------------------------
Static Function MenuDef()
Local aMenu := {}
ADD OPTION aMenu TITLE "Ajuste Individual" ACTION "U_TAFOneAdj" OPERATION MODEL_OPERATION_UPDATE ACCESS 0
ADD OPTION aMenu TITLE "Ajuste Geral" ACTION "U_TAFAllAdj" OPERATION MODEL_OPERATION_UPDATE ACCESS 0
Return (aMenu)
//-------------------------------------------------------------------
/*/{Protheus.doc} BrowseDef
Browse para exibição das matriculas incorretas
@Author Thiago Fernandes da Silva
@Type Static Function
@Since 15/10/2019
@Version 1.0
@Return oBrowse, Object, Objeto contendo as definições da browser
/*/
//-------------------------------------------------------------------
Static Function BrowseDef()
Local oBrowse := FwMBrowse():New()
Local aTableDef := TableDef()
oBrowse:SetTemporary(.T.)
oBrowse:SetAlias(aTableDef[1])
oBrowse:SetQueryIndex(aTableDef[2])
oBrowse:SetFields(aTableDef[3])
oBrowse:SetFieldFilter(U_GenFilterFld(aTableDef[3]))
oBrowse:DisableDetails()
oBrowse:SetDescription("Matrículas Divergentes")
oBrowse:SetMenuDef("VLDMATRIC")
oBrowse:SetIniWindow({|| MsgInfo("Esta rotina tem como objetivo validar e ajustar " +;
"o controle de matrículas enviados ao eSocial.", "Bem-vindo!")})
Return (oBrowse)
//-------------------------------------------------------------------
/*/{Protheus.doc} TableDef
Browse para exibição das matriculas incorretas
@Author Thiago Fernandes da Silva
@Type Static Function
@Since 15/10/2019
@Version 1.0
@Return aTable, Array, Vetor com o alias, os índices e os campos
/*/
//-------------------------------------------------------------------
Static Function TableDef()
Local nX := 0
Local aAux := {}
Local aTable := {}
Local aField := {}
Local aIndex := {}
Local cQuery := Space(0)
Local cAlias := GetNextAlias()
Local oTable := FwTemporaryTable():New(cAlias)
AAdd(aField, {"Filial", "TMP_FILIAL", "C", TamSX3("RA_FILIAL")[1], 0})
AAdd(aField, {"Funcionário", "TMP_FUNC", "C", TamSX3("RA_NOME")[1], 0})
AAdd(aField, {"CPF", "TMP_CPF", "C", TamSX3("RA_CIC")[1], 0})
AAdd(aField, {"Matric. TAF", "TMP_MTAF", "C", TamSX3("C9V_MATRIC")[1], 0})
AAdd(aField, {"Matric. GPE", "TMP_MGPE", "C", TamSX3("RA_CODUNIC")[1], 0})
aAux := Array(Len(aField))
For nX := 1 To Len(aField)
aAux[nX] := {aField[nX][2], aField[nX][3], aField[nX][4], aField[nX][5]}
Next nX
oTable:SetFields(aAux)
AAdd(aIndex, {"TMP_FILIAL", "TMP_FUNC"})
AAdd(aIndex, {"TMP_FILIAL", "TMP_CPF"})
For nX := 1 To Len(aIndex)
oTable:AddIndex(StrZero(nX, 2), aIndex[nX])
Next nX
oTable:Create()
DbSelectArea(cAlias)
cQuery += "SELECT SRA.RA_FILIAL AS TMP_FILIAL, SRA.RA_NOME AS TMP_FUNC, SRA.RA_CIC AS TMP_CPF, C9V.C9V_MATRIC AS TMP_MTAF, SRA.RA_CODUNIC AS TMP_MGPE "
cQuery += "FROM " + RetSQLName("SRA") + " SRA "
cQuery += "INNER JOIN " + RetSQLName("C9V") + " C9V "
cQuery += "ON SRA.RA_CIC = C9V.C9V_CPF AND SRA.RA_FILIAL = C9V.C9V_FILIAL AND SRA.RA_CODUNIC <> C9V.C9V_MATRIC AND RA_FILIAL = C9V_FILIAL "
cQuery += "WHERE SRA.RA_SITFOLH = '' AND C9V.C9V_DTTRAN = '' AND C9V.C9V_NOMEVE = 'S2200' AND C9V.C9V_ATIVO = 1 AND C9V.D_E_L_E_T_ <> '*' AND SRA.D_E_L_E_T_ <> '*';"
cQuery := ChangeQuery(cQuery)
SQLToTrb(cQuery, aAux, cAlias)
DbGoTop()
For nX := 1 To Len(aIndex)
aIndex[nX] := aIndex[nX][1] + "+" + aIndex[nX][2]
Next nX
AAdd(aTable, cAlias)
AAdd(aTable, aIndex)
AAdd(aTable, aField)
Return (aTable)
//-------------------------------------------------------------------
/*/{Protheus.doc} TAFOneAdj
Ajusta a matricula com divergência (alteração realizada na SRA)
@Author Thiago Fernandes da Silva
@Type User Function
@Since 15/10/2019
@Version 1.0
/*/
//-------------------------------------------------------------------
Function U_TAFOneAdj()
Local cFunc := AllTrim(TMP_FUNC)
Local cQuery := "UPDATE " + RetSQLName("SRA") + " "
cQuery += "SET RA_CODUNIC = C9V_MATRIC "
cQuery += "FROM " + RetSQLName("SRA") + " SRA "
cQuery += "INNER JOIN " + RetSQLName("C9V") + " C9V "
cQuery += "ON SRA.RA_CIC = C9V.C9V_CPF AND SRA.RA_FILIAL = C9V.C9V_FILIAL AND SRA.RA_FILIAL = C9V.C9V_FILIAL AND SRA.RA_CODUNIC <> C9V.C9V_MATRIC "
cQuery += "WHERE SRA.RA_CIC = '" + TMP_CPF + "' AND SRA.RA_SITFOLH = '' AND C9V.C9V_DTTRAN = '' AND C9V.C9V_ATIVO = 1 AND C9V.C9V_NOMEVE = 'S2200' AND C9V.D_E_L_E_T_ <> '*' AND SRA.D_E_L_E_T_ <> '*';"
If (TCSQLExec(cQuery) == 0)
RecLock(Alias(), .F.)
DbDelete()
MsUnlock()
MsgInfo("Matrícula do funcionário " + cFunc + " ajustada com sucesso!")
Else
TCSQLError()
EndIf
Return (NIL)
//-------------------------------------------------------------------
/*/{Protheus.doc} TAFAllAdj
Ajusta as matriculas com divergência (alteração realizada na SRA)
@Author Thiago Fernandes da Silva
@Type User Function
@Since 15/10/2019
@Version 1.0
/*/
//-------------------------------------------------------------------
Function U_TAFAllAdj()
Local cQuery := "UPDATE " + RetSQLName("SRA") + " "
cQuery += "SET RA_CODUNIC = C9V_MATRIC "
cQuery += "FROM " + RetSQLName("SRA") + " SRA "
cQuery += "INNER JOIN " + RetSQLName("C9V") + " C9V "
cQuery += "ON SRA.RA_CIC = C9V.C9V_CPF AND SRA.RA_FILIAL = C9V.C9V_FILIAL AND SRA.RA_FILIAL = C9V.C9V_FILIAL AND SRA.RA_CODUNIC <> C9V.C9V_MATRIC "
cQuery += "WHERE SRA.RA_SITFOLH = '' AND C9V.C9V_DTTRAN = '' AND C9V.C9V_ATIVO = 1 AND C9V.C9V_NOMEVE = 'S2200' AND C9V.D_E_L_E_T_ <> '*' AND SRA.D_E_L_E_T_ <> '*';"
If (TCSQLExec(cQuery) == 0)
While (!EOF())
RecLock(Alias(), .F.)
DbDelete()
MsUnlock()
DbSkip()
End
MsgInfo("Matrículas ajustadas com sucesso!")
Else
TCSQLError()
EndIf
Return (NIL)
//-------------------------------------------------------------------
/*/{Protheus.doc} GenFilterFld
Formata o vetor da estrutura de campos x filtro
@Author Thiago Fernandes da Silva
@Type User Function
@Since 15/10/2019
@Param aFields, Array, Vetor com os campos do browser
@Return aFilter, Array, Vetor com a estrutura de filtro do browse
@Version 1.0
/*/
//-------------------------------------------------------------------
Function U_GenFilterFld(aFields)
Local cPicture := Space(0)
Local aFilter := {}
Local nX := 0
For nX := 1 To Len(aFields)
Do Case
Case (aFields[nX][3] == "C")
cPicture := "@!"
Case (aFields[nX][3] == "N")
cPicture := "@E 99999999"
Case (aFields[nX][3] == "D")
cPicture := Space(0)
EndCase
AAdd(aFilter, {aFields[nX][2], aFields[nX][1], aFields[nX][3], aFields[nX][4], aFields[nX][5], cPicture})
Next nX
Return (aFilter)