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 |
Partager