Sub Check_Excipients(CurTime As Date)
Dim count(0 To 4) As Integer: Erase count
cnnPBD.Execute ("UPDATE z_fexcip SET checked = 0")
Dim ereg As New VBScript_RegExp_55.RegExp: ereg.Global = True
Dim rs As ADODB.Recordset
Set rs = cnnPBD.Execute("SELECT codcip, compos FROM formes WHERE codcip <> '' AND compos LIKE '%effet notoire%'")
Do While Not rs.EOF
st$ = rs!compos
i% = InStr(st$, "effet notoire")
If i% > 0 Then
i% = i% + 13
If Mid$(st$, i%, 1) = " " Then i% = i% + 1
If Mid$(st$, i%, 1) = ":" Then i% = i% + 1
st$ = LTrim$(Mid$(st$, i%))
start% = 1: i% = 3
Do While i% <= Len(st$)
ch$ = Mid$(st$, i%, 1)
If ch$ = "(" Then
jk% = InStr(i%, st$, ")")
If jk% > 0 Then ch$ = ")": i% = jk%
End If
If ch$ = "," And Not IsNumeric(Mid$(st$, i% + 1, 1)) Or ch$ = "." Or ch$ = "-" And Mid$(st$, i% + 1, 1) = " " Or i% = Len(st$) Then
If InStr(s2$, "ETHANOL") > 0 Then
s2$ = "ETHANOL"
Else
s2$ = Replace(s2$, "AMIDONS ", "AMIDON ")
If s2$ <> "" Then
cnum% = AddExcipientDCI(s2$, CurTime, count(4))
cnnPBD.Execute ("IF EXISTS (SELECT cnum FROM z_fexcip WHERE cip=" & rs!codcip & " AND cnum=" & cnum% & ")" & _
" UPDATE z_fexcip SET checked = 1 WHERE cip=" & rs!codcip & " AND cnum=" & cnum% & " " & _
"ELSE " & _
" INSERT INTO z_fexcip (cip, cnum, modif, checked) VALUES (" & _
rs!codcip & "," & cnum% & "," & DTimeModifFormat(CurTime) & ",1)")
count(1) = count(1) + 1
End If
Next1: If ch$ <> "," Then Exit Do
start% = i% + 1
End If
i% = i% + 1
Loop
End If
count(0) = count(0) + 1
rs.MoveNext
Loop
rs.Close
cnnPBD.Execute ("DELETE FROM z_fexcip WHERE checked <> 1")
Call ToLog("EXCIPIENTS scanned " & count(0) & ", modified " & count(1) & "/" & count(3) & "/" & count(4))
End Sub
Function AddExcipientDCI(st As String, CurTime As Date, ByRef count As Integer) As Integer
Dim s2 As String: s2 = MakeShortDCIName(st)
Dim rs As ADODB.Recordset: Set rs = cnnPBD.Execute("SELECT cnum, ext FROM z_dci WHERE nom=" & strp(s2))
If rs.EOF Then
AddExcipientDCI = Insert_In_Z_DCI("", s2, 8, st, CurTime)
count = count + 1
Else
If rs!ext <> 8 Then cnnPBD.Execute ("UPDATE z_dci SET ext=8, modif=" & DTimeModifFormat(CurTime) & " WHERE cnum=" & rs!cnum)
AddExcipientDCI = rs!cnum
End If
rs.Close
End Function |