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
|
Sub Macro1()
Application.Calculation = xlManual
Dim valcel As String
Dim utilisateur As String
valcel = Excel.Range("Informations!B3").Value
valce2 = Excel.Range("Informations!B4").Value
valce3 = Excel.Range("Informations!B5").Value
a = Split(valce3, "|")
id_societe = a(0)
id_atelier = a(1)
'MsgBox (">>>>>" & valce3 & "<<<<>>>>" & id_atelier & "<<<<>>>>" & id_societe & "<<<<")
chemin = ActiveWorkbook.Path
Dim cnBat As ADODB.Connection
Set cnBat = New ADODB.Connection
Dim strConn As String
strConn = "PROVIDER=SQLOLEDB;"
strConn = strConn & "DATA SOURCE=XX.XX.XX.XX;UID=login;PWD=password;DATABASE=BDD"
cnBat.Open strConn
cnBat.CommandTimeout = 0
Set fso = CreateObject("Scripting.FileSystemObject")
Const ForReading = 1, ForWriting = 2
Worksheets("TempsPassés-TempsFacturés").Range("A2:S100000").ClearContents
Worksheets("OR-ENCOURS").Range("A2:N100000").ClearContents
Worksheets("Présences réelles").Range("A2:D100000").ClearContents
Worksheets("Vente VN-VO").Range("A2:O100000").ClearContents
Worksheets("Tarif M2").Range("A2:U100000").ClearContents
Worksheets("Stock VN").Range("A2:j100000").ClearContents
Worksheets("Stock VO").Range("A2:k100000").ClearContents
Worksheets("SQL1").Range("A2:k100000").ClearContents
'------------------
Dim rsBat0 As ADODB.Recordset
Set rsBat0 = New ADODB.Recordset
With rsBat0
.ActiveConnection = cnBat
If (id_atelier = 0) Then
.Open "SELECT emp,taller FROM tgtaller WHERE tgtaller.taller NOT IN(31) "
Else
.Open "SELECT emp,taller FROM tgtaller WHERE tgtaller.taller=" & id_atelier
End If
'MsgBox ("Societe:" & rsBat("emp") & "Point de vente" & rsBat("puntoventa"))
Do While Not rsBat0.EOF
Set f0 = fso.OpenTextFile(chemin & "\sql\requete-facture-atelier.sql", ForReading)
une_variable0 = Replace(f0.ReadAll, "datedebut", valcel)
une_variable0 = Replace(une_variable0, "datefin", valce2)
une_variable0 = Replace(une_variable0, "societe", rsBat0("emp"))
une_variable0 = Replace(une_variable0, "atelier", rsBat0("taller"))
f0.Close
' Set f1 = fso.OpenTextFile(chemin & "\" & rsBat0("taller") & ".txt", 2, True)
' f1.write (une_variable)
' f1.Close
' MsgBox (une_variable)
Dim rsBati0 As ADODB.Recordset
Set rsBati0 = New ADODB.Recordset
With rsBati0
.ActiveConnection = cnBat
.Open une_variable0
DerniereLigne = Worksheets("SQL1").Range("A100000").End(xlUp).Row + 1
'MsgBox (DerniereLigne)
Worksheets("SQL1").Range("A" & DerniereLigne).CopyFromRecordset rsBati0
.Close
End With
rsBat0.MoveNext
Loop
.Close
End With
'--------------------------------------------------------------
Set f7 = fso.OpenTextFile(chemin & "\sql\t2.sql", ForReading)
une_variable7 = f7.ReadAll
f7.Close
Set f07 = fso.OpenTextFile(chemin & "\test.txt", 2, True)
f07.write (une_variable7)
f07.Close
Sql = " WITH requete1 AS (SELECT G.razon AS Societe, T.descrip AS Atelier, R.Descrip AS tarif, TPF.Tecnicidad AS M, TPF.FechaDesde AS date_debut, TPF.PrecioHora AS prix"
Sql = Sql & " FROM ttTarifaPrecioFecha AS TPF"
Sql = Sql & " INNER JOIN tgempresa AS G ON TPF.emp = G.emp"
Sql = Sql & " INNER JOIN tgtaller AS T ON TPF.emp = T.emp AND TPF.taller = T.taller"
Sql = Sql & " INNER JOIN ttTarifa AS R ON R.Codigo=TPF.Codigo"
Sql = Sql & " WHERE TPF.Tecnicidad = 'M2' AND TPF.Codigo IN ('TCE','TCL','TGA')),"
Sql = Sql & "requete2 AS (SELECT Societe ,Atelier, tarif, date_debut, Prix, RANK() OVER(PARTITION BY Societe ,Atelier,tarif ORDER BY date_debut DESC) AS RANG FROM requete1)"
Sql = Sql & "SELECT Societe ,Atelier, tarif, date_debut ,prix, RANG FROM requete2 WHERE RANG = 1;"
Dim rsBatiiiiii As ADODB.Recordset
Set rsBatiiiiii = New ADODB.Recordset
With rsBatiiiiii
.ActiveConnection = cnBat
.Open Sql
DerniereLigne7 = Worksheets("Tarif M2").Range("A100000").End(xlUp).Row + 1
Worksheets("Tarif M2").Range("A" & DerniereLigne7).CopyFromRecordset rsBatiiiiii
.Close
End With
cnBat.Close
Set rsBat = Nothing
Set cnBat = Nothing
Application.Calculate
Application.Calculation = xlAutomatic
ActiveWorkbook.RefreshAll
End Sub |
Partager