protected void GetInfo()
{
Common.PageBase pagebase = new Common.PageBase();
DTO.Admin admin = Business.Admin.Get(new DTO.Admin() { AdminID = pagebase.UserID });
DTO.Role role = Business.Role.GetRoleByAdminID(pagebase.UserID);
this.litName.Text = this.litName1.Text = admin.AdminName;
this.litRole.Text = role.RoleName;
this.litLastLoginTime.Text = admin.LastLoginTime.ToString();
}
//--------------------- 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) { }
}
//获取子菜单
protected void repMenu_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
int UserID = new Common.PageBase().UserID;
if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
DTO.Operate operate = (DTO.Operate)e.Item.DataItem;
Repeater repChild = (Repeater)e.Item.FindControl("repChild");
repChild.DataSource = Business.Operate.GetOperateByFatherID(operate.OperateID, UserID);
repChild.DataBind();
}
}
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) { }
}
protected void GetMenu()
{
int UserID = new Common.PageBase().UserID;
this.repMenu.DataSource = Business.Operate.GetOperateByFatherID(0, UserID);
this.repMenu.DataBind();
}
