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
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
|
Public Sub PROC_RECHERCHE()
Dim str_requete As String = String.Empty
Dim str_date_debut As String = ""
Dim str_date_fin As String = ""
Dim date_debut As Date = CDate("01/01/1999")
Dim date_fin As Date = CDate(Now.ToShortDateString())
Dim ora_command_filtre As OracleCommand
Dim dtr As OracleDataReader
Dim dtr1 As OracleDataReader
Dim str_nom_prenom As String = String.Empty
Dim str_adresse As String = String.Empty
Dim str_mail_reponse As String = "X"
Dim str_date_mail As String = String.Empty
Dim str_date_form As String = String.Empty
Dim str_date_genere As String = String.Empty
Dim str_etp(0 To 2) As String
Dim str_contrat(0 To 2) As String
Dim str_prest(0 To 3) As String
Dim str_site(0 To 5) As String
Dim str_age As String
Dim str_lien As String = String.Empty
Try
TXT_NOM.Text = UCase(Me.TXT_NOM.Text)
If CHECK_ETP.Checked Then str_etp(0) = "ETP 1"
If CHECK_3_ETP.Checked Then str_etp(1) = "ETP 3/4"
If CHECK_DEMI.Checked Then str_etp(2) = "ETP 1/2"
If CHECK_CDI.Checked Then str_contrat(0) = "CDI"
If CHECK_CDD.Checked Then str_contrat(1) = "CDD"
If CHECK_CR.Checked Then str_contrat(2) = "CR"
If CHECK_JF.Checked Then str_prest(0) = "Jour férié"
If CHECK_WK.Checked Then str_prest(1) = "Week-end"
If CHECK_NUIT.Checked Then str_prest(2) = "Nuit"
If CHECK_SOIR.Checked Then str_prest(3) = "Soir"
If CHECK_ATH.Checked Then str_site(0) = "Ath"
If CHECK_BAUDOUR.Checked Then str_site(1) = "Baudour"
If CHECK_BELOEIL.Checked Then str_site(2) = "Beloeil"
If CHECK_DOUR.Checked Then str_site(3) = "Dour"
If CHECK_FRAMERIE.Checked Then str_site(4) = "Frameries"
If CHECK_HORNU.Checked Then str_site(5) = "Hornu"
If DTP_DEBUT.Text <> " " Then
str_date_debut = DTP_DEBUT.Text
date_debut = CDate((DTP_DEBUT.Value).ToShortDateString())
End If
If DTP_FIN.Text <> " " Then
date_fin = CDate((DTP_FIN.Value).ToShortDateString())
End If
str_requete = "SELECT * FROM VUE_RECRUTEMENT WHERE NOM LIKE :nom AND DESCRIPTION_PROFIL LIKE :profil AND DESCRIPTION_STATUT LIKE :statut AND " _
& "ETP_1 LIKE :etp_1 AND ETP_3_4 LIKE :etp_3_4 AND ETP_1_2 LIKE :etp_1_2 AND CDI LIKE :cdi AND " _
& "CDD LIKE :cdd AND CR LIKE :cr AND W_E LIKE :w_e AND JF LIKE :jf AND SOIR LIKE :soir AND NUIT LIKE :nuit AND " _
& "SITE_ATH LIKE :ath AND SITE_BAUDOUR LIKE :site_baudour AND SITE_BELOEIL LIKE :site_beloeil AND SITE_DOUR LIKE :site_dour AND SITE_FRAMERIE LIKE :site_framerie AND " _
& "SITE_HORNU LIKE :site_hornu AND DATE_ENCODAGE BETWEEN :date_debut AND :date_fin ORDER BY ID_CANDIDAT "
ora_command_filtre = New OracleCommand(str_requete, conn)
With ora_command_filtre.Parameters()
.Add(New OracleParameter(":nom", OracleDbType.Char, 255))
.Add(New OracleParameter(":profil", OracleDbType.Char, 255))
.Add(New OracleParameter(":statut", OracleDbType.Char, 255))
.Add(New OracleParameter(":etp_1", OracleDbType.Char, 32))
.Add(New OracleParameter(":etp_3_4", OracleDbType.Char, 255))
.Add(New OracleParameter(":etp_1_2", OracleDbType.Char, 255))
.Add(New OracleParameter(":cdi", OracleDbType.Char, 255))
.Add(New OracleParameter(":cdd", OracleDbType.Char, 255))
.Add(New OracleParameter(":cr", OracleDbType.Char, 255))
.Add(New OracleParameter(":w_e", OracleDbType.Char, 255))
.Add(New OracleParameter(":jf", OracleDbType.Char, 255))
.Add(New OracleParameter(":soir", OracleDbType.Char, 255))
.Add(New OracleParameter(":nuit", OracleDbType.Char, 255))
.Add(New OracleParameter(":ath", OracleDbType.Char, 32))
.Add(New OracleParameter(":site_baudour", OracleDbType.Char, 255))
.Add(New OracleParameter(":site_beloeil", OracleDbType.Char, 255))
.Add(New OracleParameter(":site_dour", OracleDbType.Char, 255))
.Add(New OracleParameter(":site_framerie", OracleDbType.Char, 255))
.Add(New OracleParameter(":site_hornu", OracleDbType.Char, 255))
.Add(New OracleParameter(":date_debut", OracleDbType.Date))
.Add(New OracleParameter(":date_fin", OracleDbType.Date))
End With
With ora_command_filtre
.Parameters(":nom").Value = TXT_NOM.Text + "%"
.Parameters(":profil").Value = CB_PROFIL.Text + "%"
.Parameters(":statut").Value = CB_STATUT.Text + "%"
.Parameters(":etp_1").Value = str_etp(0) + "%"
.Parameters(":etp_3_4").Value = str_etp(1) + "%"
.Parameters(":etp_1_2").Value = str_etp(2) + "%"
.Parameters(":cdi").Value = str_contrat(0) + "%"
.Parameters(":cdd").Value = str_contrat(1) + "%"
.Parameters(":cr").Value = str_contrat(2) + "%"
.Parameters(":w_e").Value = str_prest(1) + "%"
.Parameters(":jf").Value = str_prest(0) + "%"
.Parameters(":soir").Value = str_prest(3) + "%"
.Parameters(":nuit").Value = str_prest(2) + "%"
.Parameters(":ath").Value = str_site(0) + "%"
.Parameters(":site_baudour").Value = str_site(1) + "%"
.Parameters(":site_beloeil").Value = str_site(2) + "%"
.Parameters(":site_dour").Value = str_site(3) + "%"
.Parameters(":site_framerie").Value = str_site(4) + "%"
.Parameters(":site_hornu").Value = str_site(5) + "%"
.Parameters(":date_debut").Value = date_debut
.Parameters(":date_fin").Value = date_fin
End With
ora_command_filtre.Connection.Close()
ora_command_filtre.Connection.Open()
dtr1 = ora_command_filtre.ExecuteReader
DTGV_RECH.Rows.Clear()
int_record = 0
If dtr1.HasRows Then
While dtr1.Read
str_date_mail = ""
str_date_form = ""
str_date_genere = ""
str_mail_reponse = ""
str_lien = ""
int_record = int_record + 1
str_date_form = ""
If dtr1("Date_nais").ToString <> vbNullString Then
str_age = CInt(FONC_CALCUL_AGE(CDate(dtr1("Date_nais").ToString)))
Else
str_age = "?"
End If
str_mail_reponse = "X"
If Trim(dtr1("DATE_MAIL_FORMULAIRE").ToString) <> vbNullString Then str_date_form = CStr(CDate(dtr1("DATE_MAIL_FORMULAIRE").ToString))
If Trim(dtr1("DESCRIPTION_PROFIL").ToString) <> "Non défini" Then
str_requete = "select * from tbl_candidat_profil where id_candidat=" & dtr1("ID_CANDIDAT").ToString & " and id_profil=" & FONC_RENVOI_ID_PROFIL(Trim(dtr1("DESCRIPTION_PROFIL").ToString))
dtr = FONC_REQ_DTR_ORA(str_requete)
If dtr.HasRows Then
If dtr("LIEN_WORD").ToString <> "" Then
str_mail_reponse = "V"
str_date_mail = dtr("DATE_MAIL_REPONSE").ToString
str_lien = dtr("LIEN_WORD").ToString
If Trim(dtr("DATE_DOC_GENERE").ToString) <> vbNullString Then str_date_genere = CStr(CDate(dtr("DATE_DOC_GENERE").ToString))
End If
End If
End If
str_nom_prenom = Trim(Trim(dtr1("TITRE").ToString) & " " & Trim(dtr1("nom").ToString) & " " & Trim(dtr1("prenom").ToString))
Me.DTGV_RECH.Rows.Add(dtr1("ID_CANDIDAT").ToString, str_nom_prenom, str_age, Trim(dtr1("DESCRIPTION_PROFIL").ToString), Trim(dtr1("DESCRIPTION_STATUT").ToString), Trim(dtr1("JOUR_RESERVE").ToString), Trim(dtr1("COMMENTAIRE").ToString), Trim(dtr1("CANDIDAT_EXTERNE_INTERNE").ToString), Trim(dtr1("SOUTIEN_POLITIQUE").ToString), str_date_form, str_date_genere, str_date_mail, str_mail_reponse, Trim(dtr1("NOM").ToString), Trim(dtr1("PRENOM").ToString), Trim(dtr1("RUE").ToString), Trim(dtr1("NUMERO").ToString), Trim(dtr1("TITRE").ToString), Trim(dtr1("CODE").ToString), Trim(dtr1("LOCALITE").ToString), Trim(dtr1("ADRESSE_MAIL").ToString), Trim(dtr1("BOITE").ToString))
If str_lien <> vbNullString Then
Me.DTGV_RECH.Rows(int_record - 1).Cells(12).ToolTipText = str_lien
End If
Select Case Trim(dtr1("DESCRIPTION_STATUT").ToString)
Case "Inactif"
Me.DTGV_RECH.Rows(int_record - 1).DefaultCellStyle.BackColor = Color.FromArgb(200, 144, 228)
Case "Positif"
Me.DTGV_RECH.Rows(int_record - 1).DefaultCellStyle.BackColor = Color.FromArgb(50, 205, 50)
Case "Engagé"
Me.DTGV_RECH.Rows(int_record - 1).DefaultCellStyle.BackColor = Color.FromArgb(98, 217, 215)
Case "Négatif"
Me.DTGV_RECH.Rows(int_record - 1).DefaultCellStyle.BackColor = Color.FromArgb(211, 82, 73)
End Select
End While
dtr1.Close()
DTGV_RECH.Rows(0).Selected = True
Else
lbl_resultat.Text = "Aucun résultat trouvé.."
End If
Call PROC_MAJ_LABEL_RESULTAT()
Call PROC_COULEUR_DGV()
Catch ex As Exception
MessageBox.Show(ex.Message & Chr(13) & "Erreur dans la recherche de candidatures")
End Try
End Sub |
[vb.net & oracle]Optimiser ma recherche
Répondre avec citation
Partager