//--------------------- Fin ------------------------------ #endregion #region Methodes //--------------------- Début ---------------------------- public void ConduitNAAlarme() { /* * * select ua.alerteid, v.matricule ,u.utilisateurid ,u.email,u.tel ,cv.jour,cv.heurD,cv.heurF from Datatracker d * inner join boitier b on b.imei=d.imei * inner join affectation_vehicule_boitier abv on abv.boitierid=b.boitierid * inner join vehicules v on v.vehiculeid = abv.vehiculeid * inner join vehicule_CNA cv on cv.vehiculeid=v.vehiculeid * inner join utilisateur u on u.clientid= v.clientid * inner join user_alerte ua on ua.utilisateurid=u.utilisateurid * where d.contact=1 and cast(d.GPSDateTime AS datetime) >= DATEADD (HH,-1,CURRENT_TIMESTAMP) * and cv.jour = datename(dw,getdate()) * and cast(d.GPSDateTime AS datetime) >= cast( (convert(varchar,(CONVERT(date,CURRENT_TIMESTAMP,103)),103))+' '+cv.heurD AS datetime) * and cast(d.GPSDateTime AS datetime) <= cast((convert(varchar,(CONVERT(date,CURRENT_TIMESTAMP,103)),103))+' '+cv.heurF AS datetime) * and (ua.alerteid=12 or ua.alerteid=24 or ua.alerteid=36) */ try { Common.PageBase b = new Common.PageBase(); List <string> tab = new List <string>(); string req = "select ua.alerteid, v.vehiculeid,v.matricule ,u.utilisateurid ,u.email,u.tel ,cv.jour,cv.heurD,cv.heurF from Datatracker d inner join boitier b on b.imei=d.imei inner join affectation_vehicule_boitier abv on abv.boitierid=b.boitierid inner join vehicules v on v.vehiculeid = abv.vehiculeid inner join vehicule_CNA cv on cv.vehiculeid=v.vehiculeid inner join utilisateur u on u.clientid= v.clientid inner join user_alerte ua on ua.utilisateurid=u.utilisateurid where d.contact=1 and cast(d.GPSDateTime AS datetime) >= DATEADD (HH,-1,CURRENT_TIMESTAMP)and cv.jour = datename(dw,getdate()) and cast(d.GPSDateTime AS datetime) >= cast( (convert(varchar,(CONVERT(date,CURRENT_TIMESTAMP,103)),103))+' '+cv.heurD AS datetime) and cast(d.GPSDateTime AS datetime) <= cast((convert(varchar,(CONVERT(date,CURRENT_TIMESTAMP,103)),103))+' '+cv.heurF AS datetime) and (ua.alerteid=12 or ua.alerteid=24 or ua.alerteid=36) group by ua.alerteid, v.vehiculeid,v.matricule ,u.utilisateurid ,u.email,u.tel ,cv.jour,cv.heurD,cv.heurF"; SqlDataReader s = Select(req); if (s.Read()) { while (s.Read()) { if ((int)s[0] == 12) { tab.Add("insert into Notification(utilisateurid,vehiculeid,alerteid,DateAlert,Vu,Descriptione)values (" + (int)s[3] + "," + (int)s[1] + "," + (int)s[0] + ",CURRENT_TIMESTAMP,'non','Alarme Conduite non autoriser : la vehicule matriculé " + (int)s[2] + " roule pendant des horaires non autoriser ')"); } if ((int)s[0] == 24) { /// envoi sms } if ((int)s[0] == 36) { object sender = new object(); EventArgs ee = new EventArgs(); Bmail(sender, ee, "Alarme : Conduite non autoriser ", "Alarme Conduite non autoriser : la vehicule matriculé " + (int)s[2] + " roule pendant des horaires non autoriser ", "*****@*****.**"); } } } s.Close(); if (tab.Count > 0) { for (int i = 0; i < tab.Count; i++) { b.Insert(tab[i]); } } } catch (Exception ee) { } }
public void PermisAlarme() { /* * select a.alerteid,c.clientid , p.personnelid,p.nom+' '+p.prenom as conducteur * ,p.permis,p.date_expiration , u.utilisateurid,u.email,u.tel * from user_alerte a * inner join utilisateur u on u.utilisateurid=a.utilisateurid * inner join client c on c.clientid=u.clientid * inner join personnel p on p.clientid=c.clientid * where p.date_expiration < DATEADD(month,1,CURRENT_TIMESTAMP) * and (a.alerteid=8 or a.alerteid=20 or a.alerteid=32) * * // * */ try { Common.PageBase b = new Common.PageBase(); List <string> tab = new List <string>(); string req = "select a.alerteid,c.clientid , p.personnelid,p.nom+' '+p.prenom as conducteur,p.permis,p.date_expiration , u.utilisateurid,u.email,u.tel from user_alerte a inner join utilisateur u on u.utilisateurid=a.utilisateurid inner join client c on c.clientid=u.clientid inner join personnel p on p.clientid=c.clientid where p.date_expiration < DATEADD(month,1,CURRENT_TIMESTAMP) and (a.alerteid=8 or a.alerteid=20 or a.alerteid=32)"; SqlDataReader s = Select(req); if (s.Read()) { while (s.Read()) { if ((int)s[0] == 8) { // Insert("insert into Notification(utilisateurid,alerteid,DateAlert,Vu,Descriptione)values (" + (int)s[6] + "," + (int)s[0] + ",CURRENT_TIMESTAMP,'non','le personel nommé : " + s[3].ToString() + " portant le permis : " + s[4].ToString() + " qui va expire ( le" + s[5].ToString() + ") dans moins d'un mois ')"); tab.Add("insert into Notification(utilisateurid,vehiculeid,alerteid,DateAlert,Vu,Descriptione)values (" + (int)s[6] + ",0," + (int)s[0] + ",CURRENT_TIMESTAMP,'non','le personel nommé : " + s[3].ToString() + " portant le permis : " + s[4].ToString() + " qui va expire ( le" + s[5].ToString() + ") dans moins d un mois ')"); } if ((int)s[0] == 20) { /// envoi sms } if ((int)s[0] == 32) { object sender = new object(); EventArgs ee = new EventArgs(); Bmail(sender, ee, "Alarme : Expiration du Permis du Personnel ", "le personel nommé : " + s[3].ToString() + " portant le permis : " + s[4].ToString() + " qui va expire ( le" + s[5].ToString() + ") dans moins d'un mois ", "*****@*****.**"); } } } s.Close(); if (tab.Count > 0) { for (int i = 0; i < tab.Count; i++) { b.Insert(tab[i]); } } } catch (Exception ee) { } }
public void CompteExpAlarme() { /* * select a.alerteid,c.clientid ,c.date_expiration , u.utilisateurid,u.email,u.tel * from user_alerte a * inner join utilisateur u on u.utilisateurid=a.utilisateurid * inner join client c on c.clientid=u.clientid * where date_expiration < DATEADD(DAY,15,CURRENT_TIMESTAMP) * and (a.alerteid=6 or a.alerteid=18 or a.alerteid=30) * */ try { Common.PageBase b = new Common.PageBase(); List <string> tab = new List <string>(); string req = "select a.alerteid,c.clientid ,c.date_expiration , u.utilisateurid,u.email,u.tel from user_alerte a inner join utilisateur u on u.utilisateurid=a.utilisateurid inner join client c on c.clientid=u.clientid where date_expiration < DATEADD(DAY,15,CURRENT_TIMESTAMP) and (a.alerteid=6 or a.alerteid=18 or a.alerteid=30)"; SqlDataReader s = Select(req); if (s.Read()) { while (s.Read()) { if ((int)s[0] == 6) { // Insert("insert into Notification(utilisateurid,alerteid,DateAlert,Vu,Descriptione)values (" + (int)s[3] + ",6,CURRENT_TIMESTAMP,'non','Votre compte sera experier d ici 15 jours veuillez contacter votre fournisseur')"); tab.Add("insert into Notification(utilisateurid,vehiculeid,alerteid,DateAlert,Vu,Descriptione)values (" + (int)s[3] + ",0,6,CURRENT_TIMESTAMP,'non','Votre compte sera expiré d ici 15 jours veuillez contacter votre fournisseur')"); } if ((int)s[0] == 18) { /// envoi sms } if ((int)s[0] == 30) { object sender = new object(); EventArgs ee = new EventArgs(); Bmail(sender, ee, "Alarme : Expiration du Compte ", "Votre compte sera expiré d ici 15 jours veuillez contacter votre fournisseur", "*****@*****.**"); } } s.Close(); if (tab.Count > 0) { for (int i = 0; i < tab.Count; i++) { b.Insert(tab[i]); } } } } catch (Exception ee) { } }
public void kiloAberrant() {/* * select ua.alerteid,u.utilisateurid, v.matricule,v.vehiculeid, d2.Odometer - d1.Odometer as distance ,vc.kmAberant ,(d2.Odometer -d1.Odometer)/10 as kmReel,u.email,u.tel from ( select imei, min(datatrackerid) idmin, MAX(datatrackerid) idmax from Datatracker d where cast(d.GPSDateTime AS datetime) >= DATEADD (HH,-23,CURRENT_TIMESTAMP) group by imei) aff inner join Datatracker d1 on d1.datatrackerid = aff.idmin inner join Datatracker d2 on d2.datatrackerid = aff.idmax inner join boitier b on b.imei=aff.imei inner join affectation_vehicule_boitier avb on avb.boitierid=b.boitierid inner join vehicules v on v.vehiculeid=avb.vehiculeid inner join utilisateur u on u.clientid= v.clientid inner join vehiculeConf vc on v.vehiculeid=vc.vehiculeid inner join user_alerte ua on ua.utilisateurid=u.utilisateurid where (d2.Odometer - d1.Odometer)/10 > vc.kmAberant and ua.alerteid in (11,23,35) * * */ try { Common.PageBase b = new Common.PageBase(); List <string> tab = new List <string>(); string req = "select ua.alerteid,u.utilisateurid, v.matricule,v.vehiculeid, d2.Odometer - d1.Odometer as distance ,vc.kmAberant ,(d2.Odometer -d1.Odometer)/10 as kmReel,u.email,u.tel from ( select imei, min(datatrackerid) idmin, MAX(datatrackerid) idmax from Datatracker d where cast(d.GPSDateTime AS datetime) >= DATEADD (HH,-23,CURRENT_TIMESTAMP) group by imei) aff inner join Datatracker d1 on d1.datatrackerid = aff.idmin inner join Datatracker d2 on d2.datatrackerid = aff.idmax inner join boitier b on b.imei=aff.imei inner join affectation_vehicule_boitier avb on avb.boitierid=b.boitierid inner join vehicules v on v.vehiculeid=avb.vehiculeid inner join utilisateur u on u.clientid= v.clientid inner join vehiculeConf vc on v.vehiculeid=vc.vehiculeid inner join user_alerte ua on ua.utilisateurid=u.utilisateurid where (d2.Odometer - d1.Odometer)/10 > vc.kmAberant and ua.alerteid in (11,23,35)"; SqlDataReader s = Select(req); if (s.Read()) { while (s.Read()) { if ((int)s[0] == 11) { // Insert("insert into Notification(utilisateurid,vehiculeid,alerteid,DateAlert,Vu,Descriptione)values (" + (int)s[1] + "," + (int)s[3] + ",11,CURRENT_TIMESTAMP,'non','Alarme : Kilometrage aberrant : le vehicule matriculé " + s[2].ToString() + " a deppassé son kilometrage prédefini (" + s[5].ToString() + ") on parcourant " + s[6].ToString() + "')"); tab.Add("insert into Notification(utilisateurid,vehiculeid,alerteid,DateAlert,Vu,Descriptione)values (" + (int)s[1] + "," + (int)s[3] + ",11,CURRENT_TIMESTAMP,'non','Alarme : Kilometrage aberrant : le vehicule matriculé " + s[2].ToString() + " a deppassé son kilometrage prédefini (" + s[5].ToString() + ") on parcourant " + s[6].ToString() + "')"); } if ((int)s[0] == 23) { /// envoi sms } if ((int)s[0] == 35) { object sender = new object(); EventArgs ee = new EventArgs(); Bmail(sender, ee, "Alarme : Kilometrage aberrant ", "Alarme : Kilometrage aberrant : le vehicule matriculé " + s[2].ToString() + " a deppassé son kilometrage prédefini (" + s[5].ToString() + ") on parcourant " + s[6].ToString(), "*****@*****.**"); } } } s.Close(); if (tab.Count > 0) { for (int i = 0; i < tab.Count; i++) { b.Insert(tab[i]); } } } catch (Exception ee) { } }
public void DepassementVitesseAlarme() { /* * * select *, * (select top 1 latitude from Datatracker * where cast(Datatracker.GPSDateTime AS datetime) >= DATEADD (MINUTE,-10,CURRENT_TIMESTAMP) * and cast(Datatracker.GPSDateTime AS datetime) < CURRENT_TIMESTAMP * and aff.imei = Datatracker.imei and Datatracker.speed = aff.vmax * order by datatrackerid desc ), * * (select top 1 longitude from Datatracker * where cast(Datatracker.GPSDateTime AS datetime) >= DATEADD (MINUTE,-10,CURRENT_TIMESTAMP) and * cast(Datatracker.GPSDateTime AS datetime) < CURRENT_TIMESTAMP * and aff.imei = Datatracker.imei and Datatracker.speed = aff.vmax * order by datatrackerid desc ), * (select top 1 GPSDateTime from Datatracker * where cast(Datatracker.GPSDateTime AS datetime) >= DATEADD (MINUTE,-10,CURRENT_TIMESTAMP) and * cast(Datatracker.GPSDateTime AS datetime) < CURRENT_TIMESTAMP * and aff.imei = Datatracker.imei and Datatracker.speed = aff.vmax * order by datatrackerid desc ) * from ( * select ua.alerteid, u.utilisateurid,v.clientid,v.matricule,v.vehiculeid, * MAX(d.speed) as vmax ,vc.vitesseMax, d.imei from Datatracker d * inner join boitier b on b.imei=d.imei * inner join affectation_vehicule_boitier avb on avb.boitierid=b.boitierid * inner join vehicules v on v.vehiculeid=avb.vehiculeid * inner join utilisateur u on u.clientid= v.clientid * inner join vehiculeConf vc on v.vehiculeid=vc.vehiculeid * inner join user_alerte ua on ua.utilisateurid = u.utilisateurid * where cast(d.GPSDateTime AS datetime) >= DATEADD (MINUTE,-10,CURRENT_TIMESTAMP) and * cast(d.GPSDateTime AS datetime) < CURRENT_TIMESTAMP * and d.speed >vc.vitesseMax * and (ua.alerteid = 1 or ua.alerteid=13 or ua.alerteid=25) * group by * u.utilisateurid,v.clientid,v.matricule,v.vehiculeid,vc.vitesseMax, d.imei,ua.alerteid * * )aff * * */ try { Common.PageBase b = new Common.PageBase(); List <string> tab = new List <string>(); string req = " select *,(select top 1 latitude from Datatracker where cast(Datatracker.GPSDateTime AS datetime) >= DATEADD (MINUTE,-10,CURRENT_TIMESTAMP) and cast(Datatracker.GPSDateTime AS datetime) < CURRENT_TIMESTAMP and aff.imei = Datatracker.imei and Datatracker.speed = aff.vmax order by datatrackerid desc ),(select top 1 longitude from Datatracker where cast(Datatracker.GPSDateTime AS datetime) >= DATEADD (MINUTE,-10,CURRENT_TIMESTAMP) and cast(Datatracker.GPSDateTime AS datetime) < CURRENT_TIMESTAMP and aff.imei = Datatracker.imei and Datatracker.speed = aff.vmax order by datatrackerid desc ),(select top 1 GPSDateTime from Datatracker where cast(Datatracker.GPSDateTime AS datetime) >= DATEADD (MINUTE,-10,CURRENT_TIMESTAMP) and cast(Datatracker.GPSDateTime AS datetime) < CURRENT_TIMESTAMP and aff.imei = Datatracker.imei and Datatracker.speed = aff.vmax order by datatrackerid desc )from (select ua.alerteid, u.utilisateurid,v.clientid,v.matricule,v.vehiculeid, MAX(d.speed) as vmax ,vc.vitesseMax, d.imei from Datatracker d inner join boitier b on b.imei=d.imei inner join affectation_vehicule_boitier avb on avb.boitierid=b.boitierid inner join vehicules v on v.vehiculeid=avb.vehiculeid inner join utilisateur u on u.clientid= v.clientid inner join vehiculeConf vc on v.vehiculeid=vc.vehiculeid inner join user_alerte ua on ua.utilisateurid = u.utilisateurid where cast(d.GPSDateTime AS datetime) >= DATEADD (MINUTE,-10,CURRENT_TIMESTAMP) and cast(d.GPSDateTime AS datetime) < CURRENT_TIMESTAMP and d.speed >vc.vitesseMax and (ua.alerteid = 1 or ua.alerteid=13 or ua.alerteid=25) group by u.utilisateurid,v.clientid,v.matricule,v.vehiculeid,vc.vitesseMax, d.imei,ua.alerteid )aff "; SqlDataReader s = Select(req); if (s.Read()) { while (s.Read()) { if ((int)s[0] == 1) { // Insert("insert into Notification(utilisateurid,vehiculeid,alerteid,DateAlert,Vu,Descriptione)values (" + (int)s[2] + "," + (int)s[4] + ",1,CURRENT_TIMESTAMP,'non','la vehicule matriculé " + s[3].ToString() + " (avec un vitesse max de " + s[6].ToString() + ")a fait un depassement de vitesse de " + s[5].ToString() + " a cette date : " + s[10].ToString() + "')"); tab.Add("insert into Notification(utilisateurid,vehiculeid,alerteid,DateAlert,Vu,Descriptione)values (" + (int)s[1] + "," + (int)s[4] + ",1,CURRENT_TIMESTAMP,'non','la vehicule matriculé " + s[3].ToString() + " (avec un vitesse max de " + s[6].ToString() + ")a fait un depassement de vitesse de " + s[5].ToString() + " a cette date : " + s[10].ToString() + "')"); } if ((int)s[0] == 13) { /// envoi sms } if ((int)s[0] == 25) { object sender = new object(); EventArgs ee = new EventArgs(); Bmail(sender, ee, "Alarme : Depassement de vitesse ", "la vehicule matriculé " + s[3].ToString() + " (avec un vitesse max de " + s[6].ToString() + ")a fait un depassement de vitesse de " + s[5].ToString() + " a cette date : " + s[10].ToString(), "*****@*****.**"); } s.Close(); if (tab.Count > 0) { for (int i = 0; i < tab.Count; i++) { b.Insert(tab[i]); } } } } else { // System.Web.HttpContext.Current.Response.Write("<SCRIPT LANGUAGE='JavaScript'>alert('pas d'enregistrement')</SCRIPT>"); } } catch (Exception ee) { } }