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
| Public Sub Fill_DG()
Dim rsRolls As New ADODB.Recordset
DGV.Enabled = False
DGV.Rows.Clear()
With rsRolls
next_step:
' Show the last roll produced
.Open("with t1 as(select sum(isnull(poids,0)) prodm,MONTH(datefab) mois1,YEAR(datefab) an from etiquette where DATEPART(day,datefab) between '1' and '31' and YEAR(datefab)='" & CmB1.Text & "' group by MONTH(datefab),YEAR(datefab)),t2 as(select SUM(ISNULL(poids,0)) prod1,MONTH(datefab) mois,YEAR(datefab) an1 from etiquette where DATEPART(day,datefab)='1' and YEAR(datefab)='" & CmB1.Text & "'and DATEPART(hour,datefab)>= '0' and datepart(hour,datefab)<'6' group by MONTH(datefab),YEAR(datefab)),t3 as(select SUM(isnull(poids,0)) prod2,MONTH(datefab)-1 mois2,YEAR(datefab) an2 from etiquette where DATEPART(day,datefab)='1' and YEAR(datefab)='" & CmB1.Text & "'and DATEPART(hour,datefab)>= '0' and datepart(hour,datefab)<'6' group by MONTH(datefab),YEAR(datefab)),t4 as(select t1.mois1 moisprod,T1.AN ANNEE,case when t1.prodm-t2.prod1+t3.prod2 is null then t1.prodm-t2.prod1 else t1.prodm-t2.prod1+t3.prod2 end as Production from t1 Join t2 on t1.mois1=t2.mois and t1.an=T2.an1 left Join t3 on t1.mois1=t3.mois2 AND T1.an=T3.an2),t5 as(select year(datefab)-1 anf,sum(isnull(poids,0)) tp,month(datefab)+11 mf from etiquette where datepart(day,datefab)='1'and year(datefab)='" & CmB1.Text & "'+1 and datepart(hour,datefab)>'0'and datepart(hour,datefab)<'6'and month(datefab)='1'group by year(datefab),month(datefab)),t6 as(select t4.moisprod moisprod,t4.annee ANNEE,case when round((t4.Production+t5.tp)/1000,0) is null then round(t4.Production/1000,0) else round((t4.Production+t5.tp)/1000,0) end as Production from t4 left join t5 on t4.moisprod=t5.mf and t4.annee=t5.anf) select production,moisprod from t6 where annee='" & CmB1.Text & "'", Connexion, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)
If .EOF Then GoTo enable_panel
.MoveFirst()
Do While Not .EOF
DGV.Rows.Add(.Fields("MOISPROD").Value, _
.Fields("Production").Value
)
.MoveNext()
Loop
.Close()
DGV.Enabled = True
enable_panel:
End With
End Sub |
Partager