public List <TypeModel> GetCodeTable()
{
SqlDataReader dr = SqlHelp.ExecuteReader("prc_GetCodeTable", CommandType.StoredProcedure);
List <TypeModel> list = new List <TypeModel>();
while (dr.Read())
{
TypeModel type = new TypeModel();
type.ID = Convert.ToInt32(dr[0]);
type.TypeName = dr[1].ToString();
list.Add(type);
}
dr.Close();
return(list);
}
public bool RestoreDataBase(string path)
{
SqlParameter param = new SqlParameter("@Path", SqlDbType.VarChar, 255);
param.Value = path;
try
{
SqlHelp.ExecuteNonQueryForMaster("restore database MyHouse from disk=@Path", CommandType.Text, param);
}
catch
{
return(false);
}
return(true);
}
public int UpdateAreaFact(AreaFactModel areafact)
{
SqlParameter[] param = { new SqlParameter("@FactName", SqlDbType.VarChar, 50), new SqlParameter("@MainHead", SqlDbType.VarChar, 20),
new SqlParameter("@Tel", SqlDbType.VarChar, 13), new SqlParameter("@TypeID", SqlDbType.Int),
new SqlParameter("@Memo", SqlDbType.Text), new SqlParameter("@ID", SqlDbType.Int) };
param[0].Value = areafact.FactName;
param[1].Value = areafact.MainHead;
param[2].Value = areafact.Tel;
param[3].Value = areafact.TypeID;
param[4].Value = areafact.Memo;
param[5].Value = areafact.ID;
int result = SqlHelp.ExecuteNonQuery("prc_UpdateAreaFact", CommandType.StoredProcedure, param);
return(result);
}
public bool BackupDataBase(string path)
{
SqlParameter param = new SqlParameter("@Path", SqlDbType.VarChar, 255);
param.Value = path;
try
{
SqlHelp.ExecuteNonQuery("prc_BackupDataBase", CommandType.StoredProcedure, param);
}
catch
{
return(false);
}
return(true);
}
public int InsertHomeFree(HomeFreeModel homeFree)
{
SqlParameter[] param = { new SqlParameter("@Code", SqlDbType.VarChar, 9), new SqlParameter("@TypeID", SqlDbType.Int),
new SqlParameter("@Number", SqlDbType.Float), new SqlParameter("@StartDate", SqlDbType.DateTime, 10),
new SqlParameter("@PayDate", SqlDbType.DateTime, 10), new SqlParameter("@AddName", SqlDbType.VarChar, 20) };
param[0].Value = homeFree.Code;
param[1].Value = homeFree.TypeID;
param[2].Value = homeFree.Number;
param[3].Value = homeFree.StartDate;
param[4].Value = homeFree.PayDate;
param[5].Value = homeFree.AddName;
int result = SqlHelp.ExecuteNonQuery("prc_InsertHomeChargeFee", CommandType.StoredProcedure, param);
return(result);
}
public bool CheckUserByUID(string uid)
{
bool flag = false;
SqlParameter param = new SqlParameter("@UID", SqlDbType.VarChar, 20);
param.Value = uid;
SqlDataReader dr = SqlHelp.ExecuteReader("prc_CheckUserByUID", CommandType.StoredProcedure, param);
dr.Read();
if (dr.HasRows)
{
flag = true;
}
dr.Close();
return(flag);
}
public int UpdateEmployee(EmployeeModel employee)
{
SqlParameter[] param = { new SqlParameter("@Name", SqlDbType.VarChar, 20), new SqlParameter("@ID", SqlDbType.Int),
new SqlParameter("@Arrage", SqlDbType.VarChar, 20), new SqlParameter("@Address", SqlDbType.VarChar, 30),
new SqlParameter("@Tel", SqlDbType.VarChar, 12), new SqlParameter("@Mobile", SqlDbType.VarChar, 12),
new SqlParameter("@PhotoPath", SqlDbType.VarChar, 255) };
param[0].Value = employee.EmlpoyeeName;
param[1].Value = employee.ID;
param[2].Value = employee.Arrage;
param[3].Value = employee.Address;
param[4].Value = employee.Tel;
param[5].Value = employee.Mobile;
param[6].Value = employee.PhotoPath;
int result = SqlHelp.ExecuteNonQuery("prc_UpdateEmployee", CommandType.StoredProcedure, param);
return(result);
}
public List <NewsModel> ShowNews()
{
SqlDataReader dr = SqlHelp.ExecuteReader("prc_ShowNews", CommandType.StoredProcedure);
List <NewsModel> list = new List <NewsModel>();
while (dr.Read())
{
NewsModel news = new NewsModel();
news.ID = Convert.ToInt32(dr[0]);
news.Title = dr[1].ToString();
news.NoticeDate = Convert.ToDateTime(dr[2]);
news.NoticeContent = dr[3].ToString();
list.Add(news);
}
dr.Close();
return(list);
}
public List <ChargeFreeTypeModel> GetChargeFreeType()
{
SqlDataReader dr = SqlHelp.ExecuteReader("prc_GetChargeFreeType", CommandType.StoredProcedure);
List <ChargeFreeTypeModel> list = new List <ChargeFreeTypeModel>();
while (dr.Read())
{
ChargeFreeTypeModel chargefree = new ChargeFreeTypeModel();
chargefree.TypeID = Convert.ToInt32(dr[0]);
chargefree.TypeName = dr[1].ToString();
chargefree.Price = Convert.ToDouble(dr[2]);
chargefree.Format = dr[3].ToString();
list.Add(chargefree);
}
dr.Close();
return(list);
}
public List <BaseParkModel> GetBasePark()
{
SqlDataReader dr = SqlHelp.ExecuteReader("prc_GetBasePark", CommandType.StoredProcedure);
List <BaseParkModel> list = new List <BaseParkModel>();
while (dr.Read())
{
BaseParkModel basePark = new BaseParkModel();
basePark.ParkID = Convert.ToInt32(dr[0]);
basePark.Name = dr[1].ToString();
basePark.Amount = Convert.ToInt32(dr[2]);
basePark.Memo = dr[3].ToString();
list.Add(basePark);
}
dr.Close();
return(list);
}
public bool CheckUser(string uid, string password)
{
bool flag = false;
SqlParameter[] param = { new SqlParameter("@UID", SqlDbType.VarChar, 20), new SqlParameter("@Password", SqlDbType.VarChar, 20) };
param[0].Value = uid;
param[1].Value = password;
SqlDataReader dr = SqlHelp.ExecuteReader("prc_CheckUser", CommandType.StoredProcedure, param);
dr.Read();
if (dr.HasRows)
{
flag = true;
}
dr.Close();
return(flag);
}
public int UpdateRoom(RoomModel room)
{
SqlParameter[] param = { new SqlParameter("@Code", SqlDbType.VarChar, 9), new SqlParameter("@SunnyID", SqlDbType.Int),
new SqlParameter("@IndoorID", SqlDbType.Int), new SqlParameter("@RoomUseID", SqlDbType.Int),
new SqlParameter("@RoomFormatID", SqlDbType.Int), new SqlParameter("@BuildArea", SqlDbType.Float),
new SqlParameter("@UseArea", SqlDbType.Float) };
param[0].Value = room.Code;
param[1].Value = room.SunnyID;
param[2].Value = room.IndoorID;
param[3].Value = room.RoomUseID;
param[4].Value = room.RoomFormatID;
param[5].Value = room.BuildArea;
param[6].Value = room.UseArea;
int result = SqlHelp.ExecuteNonQuery("prc_UpdateRoom", CommandType.StoredProcedure, param);
return(result);
}
public List <TypeModel> GetType(string typeCode)
{
SqlParameter param = new SqlParameter("@TableName", SqlDbType.VarChar, 10);
param.Value = typeCode;
SqlDataReader dr = SqlHelp.ExecuteReader("prc_GetType", CommandType.StoredProcedure, param);
List <TypeModel> list = new List <TypeModel>();
while (dr.Read())
{
TypeModel type = new TypeModel();
type.ID = Convert.ToInt32(dr[0]);
type.Name = dr[1].ToString();
list.Add(type);
}
dr.Close();
return(list);
}
public List <EmployeeModel> ShowEmployee()
{
SqlDataReader dr = SqlHelp.ExecuteReader("prc_ShowEmployee", CommandType.StoredProcedure);
List <EmployeeModel> list = new List <EmployeeModel>();
while (dr.Read())
{
EmployeeModel employee = new EmployeeModel();
employee.EmlpoyeeName = dr[0].ToString();
employee.Sex = Convert.ToInt32(dr[1]);
employee.Arrage = dr[2].ToString();
employee.Tel = dr[3].ToString();
employee.PhotoPath = dr[4].ToString();
list.Add(employee);
}
dr.Close();
return(list);
}
public List <AdModel> ShowAllAd()
{
SqlDataReader dr = SqlHelp.ExecuteReader("prc_ShowAllAd", CommandType.StoredProcedure);
List <AdModel> list = new List <AdModel>();
while (dr.Read())
{
AdModel ad = new AdModel();
ad.ID = Convert.ToInt32(dr[0]);
ad.AdName = dr[1].ToString();
ad.PhotoPath = @"~\" + dr[2].ToString();
ad.Url = dr[3].ToString();
ad.AddDate = Convert.ToDateTime(dr[4]);
list.Add(ad);
}
dr.Close();
return(list);
}
public List <RoomModel> GetAllRoomNotUsed(string code)
{
SqlParameter param = new SqlParameter("@Code", SqlDbType.VarChar, 5);
param.Value = code;
SqlDataReader dr = SqlHelp.ExecuteReader("prc_GetAllRoomNotUsed", CommandType.StoredProcedure, param);
List <RoomModel> list = new List <RoomModel>();
while (dr.Read())
{
RoomModel room = new RoomModel();
room.RoomID = dr[0].ToString();
room.Code = dr[1].ToString();
list.Add(room);
}
dr.Close();
return(list);
}
public List <FixModel> GetAllFix()
{
SqlDataReader dr = SqlHelp.ExecuteReader("prc_GetAllFix", CommandType.StoredProcedure);
List <FixModel> list = new List <FixModel>();
while (dr.Read())
{
FixModel fix = new FixModel();
fix.FixID = dr[0].ToString();
fix.Name = dr[1].ToString();
fix.Amount = Convert.ToInt32(dr[2]);
fix.Factory = dr[3].ToString();
fix.FactoryDate = Convert.ToDateTime(dr[4]);
list.Add(fix);
}
dr.Close();
return(list);
}
public int InsertPavilion(PavilionModel pavilion)
{
SqlParameter[] param = { new SqlParameter("@PaID", SqlDbType.VarChar, 3), new SqlParameter("@Name", SqlDbType.VarChar, 20),
new SqlParameter("@Layer", SqlDbType.SmallInt), new SqlParameter("@Height", SqlDbType.Float),
new SqlParameter("@Area", SqlDbType.Float), new SqlParameter("@BuildDate", SqlDbType.DateTime, 10),
new SqlParameter("@Memo", SqlDbType.Text), new SqlParameter("@TypeID", SqlDbType.Int) };
param[0].Value = pavilion.PaID;
param[1].Value = pavilion.Name;
param[2].Value = pavilion.Layer;
param[3].Value = pavilion.Height;
param[4].Value = pavilion.Area;
param[5].Value = pavilion.BuildDate;
param[6].Value = pavilion.Memo;
param[7].Value = pavilion.TypeID;
int result = SqlHelp.ExecuteNonQuery("prc_InsertPavilion", CommandType.StoredProcedure, param);
return(result);
}
public List <HomeRepairModel> GetHomeRepair()
{
SqlDataReader dr = SqlHelp.ExecuteReader("prc_GetHomeRepair", CommandType.StoredProcedure);
List <HomeRepairModel> list = new List <HomeRepairModel>();
while (dr.Read())
{
HomeRepairModel homerepair = new HomeRepairModel();
homerepair.ID = dr[0].ToString();
homerepair.Code = dr[1].ToString();
homerepair.RepairDate = Convert.ToDateTime(dr[2]);
homerepair.RepairText = dr[3].ToString();
homerepair.Sign = Convert.ToInt32(dr[4]);
homerepair.RoomID = dr[5].ToString();
list.Add(homerepair);
}
dr.Close();
return(list);
}
public ChargeFreeTypeModel GetChargeFreeTypeByID(int typeID)
{
SqlParameter param = new SqlParameter("@TypeID", SqlDbType.Int);
param.Value = typeID;
SqlDataReader dr = SqlHelp.ExecuteReader("prc_GetChargeFreeTypeByID", CommandType.StoredProcedure, param);
dr.Read();
ChargeFreeTypeModel chargefree = new ChargeFreeTypeModel();
if (dr.HasRows)
{
chargefree.TypeName = dr[0].ToString();
chargefree.Price = Convert.ToDouble(dr[1]);
chargefree.Format = dr[2].ToString();
}
dr.Close();
return(chargefree);
}
public List <AreaFactModel> ShowAllAreafact()
{
SqlDataReader dr = SqlHelp.ExecuteReader("prc_ShowAllAreafact", CommandType.StoredProcedure);
List <AreaFactModel> list = new List <AreaFactModel>();
while (dr.Read())
{
AreaFactModel arefact = new AreaFactModel();
arefact.ID = Convert.ToInt32(dr[0]);
arefact.FactName = dr[1].ToString();
arefact.MainHead = dr[2].ToString();
arefact.Tel = dr[3].ToString();
arefact.Memo = dr[5].ToString();
arefact.TypeName = dr[6].ToString();
list.Add(arefact);
}
dr.Close();
return(list);
}
public NewsModel ShowNewsByID(int id)
{
SqlParameter param = new SqlParameter("@ID", SqlDbType.Int, 5);
param.Value = id;
SqlDataReader dr = SqlHelp.ExecuteReader("prc_ShowNewsByID", CommandType.StoredProcedure, param);
dr.Read();
NewsModel news = new NewsModel();
if (dr.HasRows)
{
news.Title = dr[0].ToString();
news.NoticeDate = Convert.ToDateTime(dr[1]);
news.NoticeContent = dr[2].ToString();
}
dr.Close();
return(news);
}
public HomeReportModel ShowHomeReportByID(string id)
{
SqlParameter param = new SqlParameter("@ID", SqlDbType.VarChar, 20);
param.Value = id;
SqlDataReader dr = SqlHelp.ExecuteReader("prc_GetHomeReportByID", CommandType.StoredProcedure, param);
HomeReportModel homereport = new HomeReportModel();
dr.Read();
if (dr.HasRows)
{
homereport.ID = dr[0].ToString();
homereport.ReportText = dr[1].ToString();
homereport.ReportMemo = dr[2].ToString();
homereport.ReportDate = (DateTime)dr[3];
homereport.FinshText = dr[4].ToString();
}
dr.Close();
return(homereport);
}
public BaseParkModel GetBaseParkByID(int parkID)
{
SqlParameter param = new SqlParameter("@ParkID", SqlDbType.Int);
param.Value = parkID;
SqlDataReader dr = SqlHelp.ExecuteReader("prc_GetBaseParkByID", CommandType.StoredProcedure, param);
dr.Read();
BaseParkModel basePark = new BaseParkModel();
if (dr.HasRows)
{
basePark.ParkID = Convert.ToInt32(dr[0]);
basePark.Name = dr[1].ToString();
basePark.Amount = Convert.ToInt32(dr[2]);
basePark.Memo = dr[3].ToString();
}
dr.Close();
return(basePark);
}
public List <NewsModel> GetNewsByCondition(string condition)
{
SqlParameter param = new SqlParameter("@Condition", SqlDbType.VarChar, 255);
param.Value = condition;
SqlDataReader dr = SqlHelp.ExecuteReader("prc_GetNewsByCondition", CommandType.StoredProcedure, param);
List <NewsModel> list = new List <NewsModel>();
while (dr.Read())
{
NewsModel news = new NewsModel();
news.ID = Convert.ToInt32(dr[0]);
news.Title = dr[1].ToString();
news.NoticeDate = Convert.ToDateTime(dr[2]);
news.NoticeContent = dr[3].ToString();
news.NoticePerson = dr[4].ToString();
list.Add(news);
}
dr.Close();
return(list);
}
public int UpdateFixRepair(FixRepairModel fixRepair)
{
SqlParameter[] param = { new SqlParameter("@FixID", SqlDbType.VarChar, 18), new SqlParameter("@RepairDate", SqlDbType.DateTime, 10),
new SqlParameter("@EndDate", SqlDbType.DateTime, 10), new SqlParameter("@MainHead", SqlDbType.VarChar, 20),
new SqlParameter("@ServiceFee", SqlDbType.Money), new SqlParameter("@MaterielFee", SqlDbType.Money),
new SqlParameter("@RepairSum", SqlDbType.Money), new SqlParameter("@RepairMemo", SqlDbType.Text),
new SqlParameter("@Sign", SqlDbType.Int), new SqlParameter("@RepairUnit", SqlDbType.VarChar, 50) };
param[0].Value = fixRepair.FixID;
param[1].Value = fixRepair.RepairDate;
param[2].Value = fixRepair.EndDate;
param[3].Value = fixRepair.MainHead;
param[4].Value = fixRepair.ServiceFee;
param[5].Value = fixRepair.MaterielFee;
param[6].Value = fixRepair.RepairSum;
param[7].Value = fixRepair.RepairMemo;
param[8].Value = fixRepair.Sign;
param[9].Value = fixRepair.RepairUnit;
int result = SqlHelp.ExecuteNonQuery("prc_UpdateFixRepair", CommandType.StoredProcedure, param);
return(result);
}
public List <HomeFreeModel> GetHomeFree()
{
SqlDataReader dr = SqlHelp.ExecuteReader("prc_GetHomeFree", CommandType.StoredProcedure);
List <HomeFreeModel> list = new List <HomeFreeModel>();
while (dr.Read())
{
HomeFreeModel homefree = new HomeFreeModel();
homefree.ID = Convert.ToInt32(dr[0]);
homefree.TypeName = dr[1].ToString();
homefree.Payment = Convert.ToDouble(dr[2]);
homefree.NotPayment = Convert.ToDouble(dr[3]);
homefree.StartDate = Convert.ToDateTime(dr[4]);
homefree.AddName = dr[5].ToString();
homefree.HandleName = dr[6].ToString();
homefree.RoomID = dr[7].ToString();
list.Add(homefree);
}
dr.Close();
return(list);
}
public List <PavilionModel> GetAllPavilion()
{
SqlDataReader dr = SqlHelp.ExecuteReader("prc_GetAllPavilion", CommandType.StoredProcedure);
List <PavilionModel> list = new List <PavilionModel>();
while (dr.Read())
{
PavilionModel pavilion = new PavilionModel();
pavilion.PaID = dr[0].ToString();
pavilion.Name = dr[1].ToString();
pavilion.Layer = Convert.ToInt32(dr[2]);
pavilion.Height = Convert.ToDouble(dr[3]);
pavilion.Area = Convert.ToDouble(dr[4]);
pavilion.BuildDate = Convert.ToDateTime(dr[5]);
pavilion.Memo = dr[6].ToString();
pavilion.TypeName = dr[7].ToString();
list.Add(pavilion);
}
dr.Close();
return(list);
}
public AreaFactModel GetAreaFactByID(int id)
{
SqlParameter param = new SqlParameter("@ID", SqlDbType.Int);
param.Value = id;
SqlDataReader dr = SqlHelp.ExecuteReader("prc_GetAreaFactByID", CommandType.StoredProcedure, param);
AreaFactModel areafact = new AreaFactModel();
dr.Read();
if (dr.HasRows)
{
areafact.ID = Convert.ToInt32(dr[0]);
areafact.FactName = dr[1].ToString();
areafact.MainHead = dr[2].ToString();
areafact.Tel = dr[3].ToString();
areafact.Memo = dr[5].ToString();
areafact.TypeName = dr[6].ToString();
}
dr.Close();
return(areafact);
}
public List <FixModel> GetFixByCondition(string condition)
{
SqlParameter param = new SqlParameter("@Condition", SqlDbType.VarChar, 255);
param.Value = condition;
SqlDataReader dr = SqlHelp.ExecuteReader("prc_GetFixByCondition", CommandType.StoredProcedure, param);
List <FixModel> list = new List <FixModel>();
while (dr.Read())
{
FixModel fix = new FixModel();
fix.FixID = dr[0].ToString();
fix.Name = dr[1].ToString();
fix.Amount = Convert.ToInt32(dr[2]);
fix.Factory = dr[3].ToString();
fix.FactoryDate = Convert.ToDateTime(dr[4]);
list.Add(fix);
}
dr.Close();
return(list);
}
