public static int createFamily(tblFamily c)
{
var res = -1;
try
{
using (var db = new BusProjectEntities())
{
var pid = 0;
int.TryParse(db.tblFamilies.Max(z => z.ParentId), out pid);
c.ParentId = (pid + 1).ToString();
c.date = DateTime.Today;
c.LastUpdate = DateTime.Today;
c.registrationStatus = false;
db.tblFamilies.Add(c);
db.SaveChanges();
res = c.familyId;
}
}
catch (DbEntityValidationException ex)
{
var e = ex.EntityValidationErrors.FirstOrDefault();
throw;
}
return(res);
}
public static void deleteDuplicatedStudentToLines()
{
try
{
BusProjectEntities db = new BusProjectEntities();
System.Data.SqlClient.SqlConnection cn;
cn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["BusProject"].ConnectionString);
string sql = "DELETE FROM StudentsToLines WHERE id IN ( "
+ " SELECT MIN(id) FROM dbo.StudentsToLines "
+ " GROUP BY StudentId,LineId,StationId,Direction,Date,sun,mon,tue,wed,thu,fri,sat "
+ " HAVING count(*) > 1) ";
cn.Open();
SqlCommand com = new SqlCommand(sql, cn);
com.ExecuteScalar();
string sql2 = "update tblstudent set color=null where pk in(SELECT s.pk"
+ " FROM tblStudent AS s LEFT OUTER JOIN"
+ " StudentsToLines AS t ON s.pk = t.StudentId "
+ " WHERE (t.StudentId IS NULL) AND (s.Color IS NOT NULL)) ";
SqlCommand com2 = new SqlCommand(sql2, cn);
com2.ExecuteScalar();
cn.Close();
}
catch (Exception ex)
{//todo write to log
throw ex;
}
}
public static string returnTranslatedTerm(string term)
{
try
{
BusProjectEntities db = new BusProjectEntities();
string culture = "He_iL";
var v = (from o in db.tblDictSystems
where o.cultures == culture &&
o.term == term
select o).FirstOrDefault();;
if (v != null)
{
if (v.CustomerTranslated == null)
{
if (v.SystemTranslated != null)
{
return(v.SystemTranslated);
}
else
{
return("");
}
}
else
{
return(v.CustomerTranslated);
}
}
return(null);
}
catch
{
return(null);
}
}
public static void deleteByEmail(string Email)
{
try
{
BusProjectEntities db = new BusProjectEntities();
var v = (from s in db.Logins
where s.userName == Email
select s);
if (v.Any() || v.Count() > 0)
{
System.Data.SqlClient.SqlConnection cn;
cn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["BusProject"].ConnectionString);
string sql = "delete Login WHERE[userName]='" + Email + "' ";
cn.Open();
SqlCommand com = new SqlCommand(sql, cn);
com.ExecuteScalar();
cn.Close();
}
//BusProjectEntities db = new BusProjectEntities();
//db.Entry<Login>(new Login { userName = Email }).State = EntityState.Deleted;
//db.SaveChanges();
}
catch (Exception ex)
{//todo write to log
throw ex;
}
}
public List <string> GetStudentsColorsList()
{
List <string> res;
using (var context = new BusProjectEntities())
{
res = context.tblStudents.Select(z => z.Color).Distinct().ToList();
}
return(res);
}
public static int totalRegistrationStudentsFamilies()
{
try
{
BusProjectEntities db = new BusProjectEntities();
return(db.tblFamilies.Count());
}
catch
{
throw;
}
}
public static void update(tblFamily c)
{
try
{
BusProjectEntities db = new BusProjectEntities();
db.Entry <tblFamily>(c).State = EntityState.Modified;
db.SaveChanges();
}
catch
{
}
}
public static Login getFamilyId(string userName, string password)
{
try
{
BusProjectEntities db = new BusProjectEntities();
return(db.Logins.FirstOrDefault(c => c.userName == userName && c.Password == password));
}
catch
{
throw;
}
}
public tblBusCompany GetBusCompanyById(int id)
{
try
{
BusProjectEntities db = new BusProjectEntities();
return(db.tblBusCompanies.FirstOrDefault(c => c.pk == id));
}
catch
{
return(null);
}
}
//Todo- get from tblsystemConterol the current culture
public static void addTblDict(tblDictSystem c)
{
try
{
BusProjectEntities db = new BusProjectEntities();
db.tblDictSystems.Add(c);
db.SaveChanges();
}
catch
{
}
}
public static int totalRegistrationStudents()
{
try
{
BusProjectEntities db = new BusProjectEntities();
return(db.tblStudents.Count(c => c.registrationStatus == true));
}
catch
{
throw;
}
}
public static void updateSystemValueByKey(tblSystem c)
{
try
{
BusProjectEntities db = new BusProjectEntities();
db.Entry <tblSystem>(c).State = EntityState.Modified;
db.SaveChanges();
}
catch
{
}
}
public static tblSystem getSystemValueByKey(string key)
{
try
{
BusProjectEntities db = new BusProjectEntities();
return(db.tblSystems.FirstOrDefault(c => c.strKey == key));
}
catch
{
return(null);
}
}
public tblStudent getStudentByPk(int pk)
{
try
{
var db = new BusProjectEntities();
return(db.tblStudents.FirstOrDefault(c => c.pk == pk));
}
catch
{
return(null);
}
}
public tblFamily GetFamilyById(int familyId)
{
try
{
BusProjectEntities db = new BusProjectEntities();
return(db.tblFamilies.FirstOrDefault(c => c.familyId == familyId));
}
catch
{
return(null);
}
}
public static tblDictSystem getTblDict(int id)
{
try
{
BusProjectEntities db = new BusProjectEntities();
return(db.tblDictSystems.FirstOrDefault(c => c.id == id));
}
catch
{
return(null);
}
}
public static List <tblDictSystem> getAllTblDict()
{
try
{
BusProjectEntities db = new BusProjectEntities();
return(db.tblDictSystems.ToList());
}
catch
{
return(null);
}
}
public tblStudent getStudentByFamilyId(int familyId)
{
try
{
BusProjectEntities db = new BusProjectEntities();
return(db.tblStudents.FirstOrDefault(c => c.familyId == familyId));
}
catch
{
return(null);
}
}
public static void addEmailSent(tblEmailSent c)
{
try
{
BusProjectEntities db = new BusProjectEntities();
db.tblEmailSents.Add(c);
db.SaveChanges();
}
catch
{
}
}
public static List <tblYear> GetYears()
{
try
{
BusProjectEntities db = new BusProjectEntities();
List <tblYear> c = db.tblYears.ToList();
return(c);
}
catch
{
return(null);
}
}
public List <tblStreet> GetStreets()
{
try
{
BusProjectEntities db = new BusProjectEntities();
List <tblStreet> c = db.tblStreets.ToList();
return(c);
}
catch
{
return(null);
}
}
public static List <viewStudentWithStationForEmail> GetAllStudentForEmail()
{
try
{
BusProjectEntities db = new BusProjectEntities();
List <viewStudentWithStationForEmail> c = db.viewStudentWithStationForEmails.ToList();
return(c);
}
catch
{
return(null);
}
}
public static List <ViewAlllinesByBusCompnyAndStation> GetAllLinesAndStations()
{
try
{
BusProjectEntities db = new BusProjectEntities();
List <ViewAlllinesByBusCompnyAndStation> c = db.ViewAlllinesByBusCompnyAndStations.ToList();
return(c);
}
catch
{
return(null);
}
}
public List <tblStreet> GetStreetsByprefix(string street)
{
try
{
BusProjectEntities db = new BusProjectEntities();
List <tblStreet> c = db.tblStreets.Where(x => x.streetName.Contains(street)).ToList();
return(c);
}
catch
{
return(null);
}
}
public List <tblBusCompany> GetBusCompanies()
{
try
{
BusProjectEntities db = new BusProjectEntities();
List <tblBusCompany> c = db.tblBusCompanies.ToList();
return(c);
}
catch
{
return(null);
}
}
public static int totalStudents()
{
try
{
BusProjectEntities db = new BusProjectEntities();
int total = db.tblStudents.Count();
return(total);
}
catch
{
throw;
}
}
public static List <ViewAllStudentFamilyLinesStation> GetAllStudents()
{
try
{
BusProjectEntities db = new BusProjectEntities();
List <ViewAllStudentFamilyLinesStation> c = db.ViewAllStudentFamilyLinesStations.ToList();
return(c);
}
catch
{
return(null);
}
}
public List <tblStudent> GetStudentByFamilyIdAndYear(int familyId, int Year)
{
try
{
BusProjectEntities db = new BusProjectEntities();
List <tblStudent> c = db.tblStudents.Where(x => x.familyId == familyId).Where(x => x.yearRegistration == Year).ToList();
return(c);
}
catch
{
return(null);
}
}
public static void update(tblStudent c)
{
try
{
var db = new BusProjectEntities();
db.Entry <tblStudent>(c).State = EntityState.Modified;
db.SaveChanges();
}
catch (DbEntityValidationException ex)
{
var s = ex.EntityValidationErrors.ToList();
}
}
public static void deleteTblDict(int id)
{
try
{
BusProjectEntities db = new BusProjectEntities();
db.Entry <tblDictSystem>(new tblDictSystem {
id = id
}).State = EntityState.Deleted;
db.SaveChanges();
}
catch
{
}
}
