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
|
Private Sub cmdpromotion_Click()
j = 0
k = 0
'On Error Resume Next
Call ConnectDB
Set rs13 = New Recordset
Set rs17 = New Recordset
Set rs16 = New Recordset
Set rs18 = New Recordset
Dim req12
Dim req13
Dim req14
Dim var
rs13.Open "select * from MFT_AR_YDET x, MFT_AR_DETAILR where x.MFT_AR_YDET_YRES_ID=MFT_AR_DETAILR.MFT_AR_DETAILR_YRES_ID and not exists (select * from MFT_AR_DETAILR y where MFT_AR_DETAILR_PRIXA is null and x.MFT_AR_YDET_YRES_ID=y.MFT_AR_DETAILR_YRES_ID)", db, adOpenDynamic, adLockOptimistic
rs13.MoveFirst
Do While Not rs13.EOF
j = j + 1
rs13.MoveNext
Loop
shape1.Max = j
rs13.MoveFirst
Do While Not rs13.EOF
prix = (CCur(rs13!mft_ar_detailr_prixa) + CCur(rs13!MFT_AR_DETAILR_PRIXC) + CCur(rs13!MFT_AR_DETAILR_PRIXB) + CCur(rs13!MFT_AR_DETAILR_PRIXT))
var = rs13!MFT_AR_YDET_EXPARRTIME - rs13!MFT_AR_YDET_saledate
'--------------------Listes des périodes -------------------------------
If rs13!mft_ar_ydet_ycat_id_rate <> 0 Then
'MsgBox prix & vbTab & rs13.RecordCount
rs16.Open " select yrcd.yrcd_id, yrcd_startdate,yrcd_enddate,yrcd.yrcd_yprm_id,yrcd_yrch_id from yrcd,yrct, mft_ar_ydet where '" & rs13!mft_ar_detailr_current_date & "' between yrcd.yrcd_startdate and yrcd.yrcd_enddate and '" & rs13!mft_ar_ydet_yrch_id & "'= yrcd.yrcd_yrch_id and '" & rs13!mft_ar_ydet_ycat_id_rate & "'= yrct.yrct_ycat_id and yrcd.yrcd_id = yrct.yrct_yrcd_id", db, adOpenDynamic, adLockOptimistic
rs17.Open " select yprr.* from yprm,yprd,yprr, yplr where yprm_id=yprd_yprm_id and yprd_id=yplr_yprd_id and yprr_id=yplr_yprr_id and yprm_id= '" & rs16!yrcd_yprm_id & "' and '" & rs13!MFT_AR_DETAILR_SALEDATE & "' between yprd_startbooking and yprd_endbooking and '" & rs13!MFT_AR_DETAILR_EXPARRTIME & "' between yprd_startdate and yprd_enddate and ((nvl(yprr_rule,0)=0 and '" & rs13!MFT_AR_DETAILR_NUITE & "' between yprr_minday and yprr_maxday) or (nvl(yprr_rule,0)=1 and decode(nvl(yprr_fixstartdate,0),0,'" & var & "',(yprd_startdate - '" & rs13!MFT_AR_DETAILR_EXPARRTIME & "')) between yprr_minday and yprr_maxday)) order by yprr_displorder", db, adOpenDynamic, adLockOptimistic
'--------------------------Calcul de promotion--------------------------
Do While Not rs17.EOF
req12 = rs17!YPRR_LONGDESC
req13 = rs17!YPRR_VALUE
req14 = rs17!YPRR_RULE
If rs17!YPRR_RULE = 0 Then
promo = 0
Else
If rs17.RecordCount > 1 Then
promo = ((Replace(prix, ".", ",") * rs17!YPRR_VALUE) / 100)
prix1 = (Replace(prix, ".", ",") - Replace(promo, ".", ","))
prix = prix1
Else
prix = Replace(prix, ".", ",")
promo = Replace(promo, ".", ",")
promo = ((Replace(prix, ".", ",") * rs17!YPRR_VALUE) / 100)
End If
End If
'---------------------------Insertion dans la table promotion----
rs18.Open "insert into MFT_AR_PROMO values ('" & req12 & "','" & req13 & "','" & req14 & "', '" & rs13!MFT_AR_DETAILR_YRES_ID & "','" & rs13!mft_ar_detailr_current_date & "','" & promo & "')", db, adOpenDynamic, adLockOptimistic
rs17.MoveNext
Loop
Else
'MsgBox prix & vbTab & rs13.RecordCount
rs16.Open " select yrcd.yrcd_id, yrcd.yrcd_yprm_id ,yrcd_startdate,yrcd_enddate,yrcd_yrch_id from yrcd,yrct, mft_ar_ydet where '" & rs13!mft_ar_detailr_current_date & "' between yrcd.yrcd_startdate and yrcd.yrcd_enddate and '" & rs13!mft_ar_ydet_yrch_id & "'= yrcd.yrcd_yrch_id and '" & rs13!mft_ar_ydet_ycat_id & "'= yrct.yrct_ycat_id and yrcd.yrcd_id = yrct.yrct_yrcd_id", db, adOpenDynamic, adLockOptimistic
rs17.Open " select yprr.* from yprm,yprd,yprr, yplr where yprm_id=yprd_yprm_id and yprd_id=yplr_yprd_id and yprr_id=yplr_yprr_id and yprm_id= '" & rs16!yrcd_yprm_id & "' and '" & rs13!MFT_AR_DETAILR_SALEDATE & "' between yprd_startbooking and yprd_endbooking and '" & rs13!MFT_AR_DETAILR_EXPARRTIME & "' between yprd_startdate and yprd_enddate and ((nvl(yprr_rule,0)=0 and '" & rs13!MFT_AR_DETAILR_NUITE & "' between yprr_minday and yprr_maxday) or (nvl(yprr_rule,0)=1 and decode(nvl(yprr_fixstartdate,0),0,'" & var & "',(yprd_startdate - '" & rs13!MFT_AR_DETAILR_EXPARRTIME & "')) between yprr_minday and yprr_maxday)) order by yprr_displorder", db, adOpenDynamic, adLockOptimistic
'---------------------------Calcul de la promotion---------------
Do While Not rs17.EOF
req12 = rs17!YPRR_LONGDESC
req13 = rs17!YPRR_VALUE
req14 = rs17!YPRR_RULE
If rs14!YPRR_RULE = 0 Then
promo = 0
Else
If rs17.RecordCount > 1 Then
promo = ((Replace(prix, ".", ",") * rs17!YPRR_VALUE) / 100)
prix1 = (Replace(prix, ".", ",") - Replace(promo, ".", ","))
prix = prix1
Else
prix = Replace(prix, ".", ",")
promo = Replace(promo, ".", ",")
promo = ((Replace(prix, ".", ",") * rs17!YPRR_VALUE) / 100)
End If
End If
'------------------Insertion dans la table promotion------------------------
rs18.Open "insert into MFT_AR_PROMO values ('" & req12 & "','" & req13 & "','" & req14 & "', '" & rs13!MFT_AR_DETAILR_YRES_ID & "','" & rs13!mft_ar_detailr_current_date & "','" & promo & "')", db, adOpenDynamic, adLockOptimistic
rs17.MoveNext
Loop
End If
rs17.Close
rs16.Close
rs13.MoveNext
k = k + 1
shape1.Visible = True
shape1.Value = Int(k)
Loop
rs13.Close
shape1.Visible = False
End Sub |
Répondre avec citation
Partager