public void Destroy(SchoolModel school)
{
if (!UpdateDatabase)
{
var target = GetAll().FirstOrDefault(p => p.SchoolID == school.SchoolID);
if (target != null)
{
GetAll().Remove(target);
}
}
else
{
var target = (from s in entities.Schools
where s.SchoolID == school.SchoolID
select s).FirstOrDefault();
if (target != null)
{
target.IsDeleted = true;
entities.Entry(target).State = System.Data.Entity.EntityState.Modified;
entities.SaveChanges();
}
}
}
public int AddSchool(SchoolModel schoolModel)
{
using (conn = JBCertConnection.Instance)
{
string queryString = @"INSERT INTO [dbo].[tblTruong]
([XaId],[Tentruong],[Nguoidaidien] ,[Tinh] ,[Diachi] ,[Dienthoai] ,[Fax],[Ghichu] ,[Hedaotao] ,[LoaiId] ,[IsDeleted])
VALUES
(@XaId, @Tentruong, @Nguoidaidien, @Tinh, @Diachi, @Dienthoai, @Fax, @Ghichu, '', @LoaiId, 0)";
conn.Open();
SqlCommand sqlCommand = new SqlCommand(queryString, conn);
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Parameters.AddWithValue("@XaId", schoolModel.VillageId);
sqlCommand.Parameters.AddWithValue("@Tentruong", schoolModel.SchoolName);
sqlCommand.Parameters.AddWithValue("@Nguoidaidien", schoolModel.Representative);
sqlCommand.Parameters.AddWithValue("@Tinh", schoolModel.Province);
sqlCommand.Parameters.AddWithValue("@Diachi", schoolModel.Address);
sqlCommand.Parameters.AddWithValue("@Dienthoai", schoolModel.PhoneNumber);
sqlCommand.Parameters.AddWithValue("@Fax", schoolModel.Fax);
sqlCommand.Parameters.AddWithValue("@Ghichu", schoolModel.Note);
sqlCommand.Parameters.AddWithValue("@LoaiId", schoolModel.BlankCertTypeId);
try
{
int rowEffected = sqlCommand.ExecuteNonQuery();
return(rowEffected);
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
}
public List <SchoolModel> SearchSchool(string schoolName, int townId, int villageId, string phoneNumber)
{
List <SchoolModel> schoolModels = new List <SchoolModel>();
using (conn = JBCertConnection.Instance)
{
string queryString = @"SELECT a.*,b.HuyenId, b.Ten as 'Tenxa', c.Ten as 'Tenhuyen', d.Name 'Tenloai' FROM [dbo].[tblTruong] as a
left join [dbo].[tblXa] as b on a.XaId = b.Id
left join [dbo].[tblHuyen] as c on b.HuyenId = c.Id
left join [dbo].[tblLoai] as d on a.LoaiId = d.Id
where a.IsDeleted = 0
and a.Tentruong like @Tentruong
and (@XaId = -1 or a.XaId = @Xaid)
and (@HuyenId = -1 or b.Id = @HuyenId)
and a.Dienthoai like @Dienthoai";
conn.Open();
SqlCommand sqlCommand = new SqlCommand(queryString, conn);
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Parameters.AddWithValue("@Tentruong", "%" + schoolName + "%");
sqlCommand.Parameters.AddWithValue("@XaId", townId);
sqlCommand.Parameters.AddWithValue("@HuyenId", villageId);
sqlCommand.Parameters.AddWithValue("@Dienthoai", "%" + phoneNumber + "%");
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
try
{
while (sqlDataReader.Read())
{
SchoolModel schoolModel = new SchoolModel();
schoolModel.Id = int.Parse(sqlDataReader["Id"].ToString());
schoolModel.SchoolName = sqlDataReader["Tentruong"].ToString();
schoolModel.VillageId = int.Parse(sqlDataReader["XaId"].ToString());
schoolModel.VillageName = sqlDataReader["Tenxa"].ToString();
schoolModel.Representative = sqlDataReader["Nguoidaidien"].ToString();
schoolModel.Province = sqlDataReader["Tinh"].ToString();
schoolModel.TownId = int.Parse(sqlDataReader["HuyenId"].ToString());
schoolModel.TownName = sqlDataReader["Tenhuyen"].ToString();
schoolModel.Address = sqlDataReader["Diachi"].ToString();
schoolModel.PhoneNumber = sqlDataReader["Dienthoai"].ToString();
schoolModel.Fax = sqlDataReader["Fax"].ToString();
schoolModel.Note = sqlDataReader["Ghichu"].ToString();
schoolModel.BlankCertTypeId = int.Parse(sqlDataReader["LoaiId"].ToString());
schoolModel.BlankCertTypeName = sqlDataReader["Tenloai"].ToString();
schoolModels.Add(schoolModel);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlDataReader.Close();
conn.Close();
}
}
return(schoolModels);
}
public static SchoolModel GetSchoolBySchoolID(int SchoolID)
{
School school = (from s in db.Schools
where s.SchoolID == SchoolID
select s).FirstOrDefault();
SchoolModel sm = school;
County countyModel = (from c in db.Counties
where c.CountyID == sm.CountyID
select c).FirstOrDefault();
sm.CountyName = countyModel.CountyName;
return(sm);
}
public SchoolModel GetSingleSchoolById(int schoolId)
{
SchoolModel schoolModel = new SchoolModel();
using (conn = JBCertConnection.Instance)
{
string queryString = @"SELECT a.*,b.HuyenId, b.Ten as 'Tenxa', c.Ten as 'Tenhuyen', d.Name 'Tenloai' FROM [dbo].[tblTruong] as a
left join [dbo].[tblXa] as b on a.XaId = b.Id
left join [dbo].[tblHuyen] as c on b.HuyenId = c.Id
left join [dbo].[tblLoai] as d on a.LoaiId = d.Id
where a.[Id] = @Id";
conn.Open();
SqlCommand sqlCommand = new SqlCommand(queryString, conn);
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Parameters.AddWithValue("@Id", schoolId);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
try
{
while (sqlDataReader.Read())
{
schoolModel.Id = int.Parse(sqlDataReader["Id"].ToString());
schoolModel.SchoolName = sqlDataReader["Tentruong"].ToString();
schoolModel.VillageId = int.Parse(sqlDataReader["XaId"].ToString());
schoolModel.VillageName = sqlDataReader["Tenxa"].ToString();
schoolModel.Province = sqlDataReader["Tinh"].ToString();
schoolModel.Representative = sqlDataReader["Nguoidaidien"].ToString();
schoolModel.TownId = int.Parse(sqlDataReader["HuyenId"].ToString());
schoolModel.TownName = sqlDataReader["Tenhuyen"].ToString();
schoolModel.Address = sqlDataReader["Diachi"].ToString();
schoolModel.PhoneNumber = sqlDataReader["Dienthoai"].ToString();
schoolModel.Fax = sqlDataReader["Fax"].ToString();
schoolModel.Note = sqlDataReader["Ghichu"].ToString();
schoolModel.BlankCertTypeId = int.Parse(sqlDataReader["LoaiId"].ToString());
schoolModel.BlankCertTypeName = sqlDataReader["Tenloai"].ToString();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlDataReader.Close();
conn.Close();
}
}
return(schoolModel);
}
public int UpdateSchool(SchoolModel schoolModel)
{
using (conn = JBCertConnection.Instance)
{
string queryString = @"UPDATE [dbo].[tblTruong]
SET [XaId] = @XaId
,[Tentruong] = @Tentruong
,[Nguoidaidien] = @Nguoidaidien
,[Tinh] = @Tinh
,[Diachi] = @Diachi
,[Dienthoai] = @Dienthoai
,[Fax] = @Fax
,[Ghichu] = @Ghichu
,[LoaiId] = @LoaiId
WHERE [Id] = @Id";
conn.Open();
SqlCommand sqlCommand = new SqlCommand(queryString, conn);
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Parameters.AddWithValue("@Id", schoolModel.Id);
sqlCommand.Parameters.AddWithValue("@XaId", schoolModel.VillageId);
sqlCommand.Parameters.AddWithValue("@Tentruong", schoolModel.SchoolName);
sqlCommand.Parameters.AddWithValue("@Nguoidaidien", schoolModel.Representative);
sqlCommand.Parameters.AddWithValue("@Tinh", schoolModel.Province);
sqlCommand.Parameters.AddWithValue("@Diachi", schoolModel.Address);
sqlCommand.Parameters.AddWithValue("@Dienthoai", schoolModel.PhoneNumber);
sqlCommand.Parameters.AddWithValue("@Fax", schoolModel.Fax);
sqlCommand.Parameters.AddWithValue("@Ghichu", schoolModel.Note);
sqlCommand.Parameters.AddWithValue("@LoaiId", schoolModel.BlankCertTypeId);
try
{
int rowEffected = sqlCommand.ExecuteNonQuery();
return(rowEffected);
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
}
public void Create(SchoolModel school, string userId)
{
if (!UpdateDatabase)
{
var first = GetAll().OrderByDescending(e => e.SchoolID).FirstOrDefault();
var id = (first != null) ? first.SchoolID : 0;
school.SchoolID = id + 1;
GetAll().Insert(0, school);
}
else
{
var entity = new School();
entity.SchoolName = school.SchoolName;
entity.SchoolTypeID = school.SchoolTypeID;
entity.Address1 = school.Address1;
entity.Address2 = school.Address2;
entity.City = school.City;
entity.State = school.State;
entity.Zip = school.Zip;
entity.CountyID = school.CountyID;
entity.Phone = school.Phone;
entity.PrincipalName = school.PrincipalName;
entity.PrincipalEmail = school.PrincipalEmail;
entity.PEDS = school.PEDS;
entity.Census = school.Census;
entity.PredictedThirdGradeStudents = school.PredictedThirdGradeStudents;
entity.SchoolBoardDistrict = school.SchoolBoardDistrict;
entity.CityCouncilDistrict = school.CityCouncilDistrict;
entity.SonicPartner = school.SonicPartner;
entity.TitleOneSchool = school.TitleOneSchool;
entity.AppalachianRegion = school.AppalachianRegion;
entity.IsDeleted = school.IsDeleted;
entity.QuadrantID = school.QuadrantID;
entity.CreateDate = DateTime.Now;
entity.CreateBy = userId;
entities.Schools.Add(entity);
entities.SaveChanges();
school.SchoolID = entity.SchoolID;
}
}
public void Update(SchoolModel school, string userId)
{
if (!UpdateDatabase)
{
var target = One(e => e.SchoolID == school.SchoolID);
if (target != null)
{
target.SchoolID = school.SchoolID;
target.SchoolName = school.SchoolName;
target.SchoolTypeID = school.SchoolTypeID;
target.Address1 = school.Address1;
target.Address2 = school.Address2;
target.City = school.City;
target.State = school.State;
target.Zip = school.Zip;
target.CountyID = school.CountyID;
target.Phone = school.Phone;
target.PrincipalName = school.PrincipalName;
target.PrincipalEmail = school.PrincipalEmail;
target.PEDS = school.PEDS;
target.Census = school.Census;
target.PredictedThirdGradeStudents = school.PredictedThirdGradeStudents;
target.SchoolBoardDistrict = school.SchoolBoardDistrict;
target.CityCouncilDistrict = school.CityCouncilDistrict;
target.SonicPartner = school.SonicPartner;
target.TitleOneSchool = school.TitleOneSchool;
target.AppalachianRegion = school.AppalachianRegion;
target.IsDeleted = school.IsDeleted;
target.QuadrantID = school.QuadrantID;
target.CreateDate = school.CreateDate;
target.CreateBy = school.CreateBy;
target.ModifiedDate = DateTime.Now;
target.ModifiedBy = userId;
}
}
else
{
var entity = new School();
entity.SchoolID = school.SchoolID;
entity.SchoolName = school.SchoolName;
entity.SchoolTypeID = school.SchoolTypeID;
entity.Address1 = school.Address1;
entity.Address2 = school.Address2;
entity.City = school.City;
entity.State = school.State;
entity.Zip = school.Zip;
entity.CountyID = school.CountyID;
entity.Phone = school.Phone;
entity.PrincipalName = school.PrincipalName;
entity.PrincipalEmail = school.PrincipalEmail;
entity.PEDS = school.PEDS;
entity.Census = school.Census;
entity.PredictedThirdGradeStudents = school.PredictedThirdGradeStudents;
entity.SchoolBoardDistrict = school.SchoolBoardDistrict;
entity.CityCouncilDistrict = school.CityCouncilDistrict;
entity.SonicPartner = school.SonicPartner;
entity.TitleOneSchool = school.TitleOneSchool;
entity.AppalachianRegion = school.AppalachianRegion;
entity.IsDeleted = school.IsDeleted;
entity.QuadrantID = school.QuadrantID;
entity.CreateDate = school.CreateDate;
entity.CreateBy = school.CreateBy;
entity.ModifiedDate = DateTime.Now;
entity.ModifiedBy = userId;
entities.Schools.Attach(entity);
entities.Entry(entity).State = EntityState.Modified;
entities.SaveChanges();
}
}
