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
| Sub MntLgn()
Dim Cnx as Object, Stmt as Object, RslVlm as Object,RslTrf as Object, RslCff as Object
Dim SqlVlm as string, SqlTrf as string, SqlCff as string, SqlInt as string, Rqt as object
Dim VlmBrt as Double, Trf as double, Cff as double, SsTtl as Double
Cnx = ThisDatabasedocument.CurrentController.ActiveConnection
Stmt=Cnx.createStatement()
'---Calcul du volume brut---
SqlVlm = "SELECT (LngCmp * LrgCmp * PsrCmp * Qts) as Vlm from T_DvsDtl"&_
" where Cf_Dvs = 0 and Cf_Prd = 0 and Cf_PrdNmc = 0"
RslVlm = Stmt.executeQuery(SqlVlm)
RslVlm.next
VlmBrt = RslVlm.getColumns().getByName("Vlm").getString()
'---Tarif article---
SqlTrf = "SELECT T_RtcTrf.RtcTrf, T_DvsDtl.Cf_Dvs, T_DvsDtl.Cf_Prd,"&_
" T_DvsDtl.Cf_PrdNmc FROM T_DvsDtl, T_RtcTrf WHERE T_DvsDtl.Cf_RtcTrf ="&_
"T_RtcTrf.Cf_RtcTrf AND T_DvsDtl.Cf_Dvs = 0 AND T_DvsDtl.Cf_Prd = 0"&_
" AND T_DvsDtl.Cf_PrdNmc = 0"
RslTrf = Stmt.executeQuery(SqlTrf)
RslTrf.next
Trf = RslTrf.getColumns().getByName("RtcTrf").getString()
'---Coefficient de débit---
SqlCff = "SELECT T_MtrCff.VlrCff, T_DvsDtl.Cf_Dvs, T_DvsDtl.Cf_Prd,"&_
" T_DvsDtl.Cf_PrdNmc FROM T_MtrCff, T_DvsDtl WHERE T_MtrCff.Cf_MtrCff ="&_
"T_DvsDtl.Cf_MtrCff AND T_DvsDtl.Cf_Dvs = 0 AND T_DvsDtl.Cf_Prd = 0"&_
" AND T_DvsDtl.Cf_PrdNmc = 0"
RslCff = Stmt.executeQuery(SqlCff)
RslCff.next
Cff = RslCff.getColumns().getByName("VlrCff").getString()
'---Calcul Final---
'Msgbox VlmBrt &" - "& Trf &" -"&Cff
SsTtl = (VlmBrt * Trf * Cff)/1000000000
'---Insertion du résultat----
SqlInt = "INSERT INTO ""T_DvsDtl""(""MntLgn"") Values('"& SsTtl &"')"&_
" WHERE ""T_DvsDtl.Cf_Dvs"" = ' 0 ' AND ""T_DvsDtl.Cf_PrdNmc"" = ' 0 '"
Rqt = Stmt.executeQuery(SqlInt)
Rqt.close
Stmt.close
End Sub |
Partager