/// <summary>
/// 按开始时间和科室查找排班表信息
/// </summary>
/// <param name="_doctorId">DateTime doctorId</param>
/// <returns>DTO4Arrangement对象数组</returns>
public DTO4Arrangement[] selectAllByBeginTime(DateTime _beginTime, string _office)
{
string sql = "select * from arrangement where convert(varchar,beginTime,120) like '%" + _beginTime.ToString("yyyy-MM-dd") + "%' and office ='" + _office + "'";
DataSet dt = DBOperater.selectSql(sql);
if (dt != null && dt.Tables.Count > 0 && dt.Tables[0].Rows.Count > 0)
{
DTO4Arrangement[] dto4aArray = new DTO4Arrangement[dt.Tables[0].Rows.Count];
for (int i = 0; i < dt.Tables[0].Rows.Count; i++)
{
dto4aArray[i] = new DTO4Arrangement();
dto4aArray[i].Id = dt.Tables[0].Rows[i]["id"].ToString();
dto4aArray[i].Office = (string)dt.Tables[0].Rows[i]["office"];
dto4aArray[i].BeginTime = (DateTime)dt.Tables[0].Rows[i]["beginTime"];
dto4aArray[i].FinishTime = (DateTime)dt.Tables[0].Rows[i]["finishTime"];
dto4aArray[i].DoctorId = dt.Tables[0].Rows[i]["doctorId"].ToString();
dto4aArray[i].DoctorName = (string)dt.Tables[0].Rows[i]["doctorName"];
dto4aArray[i].DoctorJobTitle = (string)dt.Tables[0].Rows[i]["doctorJobTitle"];
//dto4aArray[i].Img = (string)dt.Tables[0].Rows[i]["img"];
}
return(dto4aArray);
}
else
{
DTO4Arrangement[] dto4aArray = new DTO4Arrangement[1];
return(dto4aArray);
}
}
/// <summary>
/// 根据状态查找排班修改记录
/// </summary>
/// <param name="_status">string 状态</param>
/// <returns></returns>
public DTO4ChangeRecord[] selectAllByStatus(string _status)
{
String sql = "select * from changeRecord where status = '" + _status + "'";
DataSet dt = DBOperater.selectSql(sql);
if (dt != null && dt.Tables.Count > 0 && dt.Tables[0].Rows.Count > 0)
{
DTO4ChangeRecord[] dto4cArray = new DTO4ChangeRecord[dt.Tables[0].Rows.Count];
for (int i = 0; i < dt.Tables[0].Rows.Count; i++)
{
dto4cArray[i] = new DTO4ChangeRecord();
dto4cArray[i].Id = dt.Tables[0].Rows[i]["id"].ToString();
dto4cArray[i].ArrangementId_self = dt.Tables[0].Rows[i]["arrangementId_self"].ToString();
dto4cArray[i].ArrangementId_others = dt.Tables[0].Rows[i]["arrangementId_others"].ToString();
dto4cArray[i].Status = (string)dt.Tables[0].Rows[i]["status"];
dto4cArray[i].Remark = (string)dt.Tables[0].Rows[i]["remark"];
dto4cArray[i].Operate = dt.Tables[0].Rows[i]["operate"].ToString();
}
return(dto4cArray);
}
else
{
DTO4ChangeRecord[] dto4cArray = new DTO4ChangeRecord[1];
return(dto4cArray);
}
}
/// <summary>
/// 按科室和职称查询医生信息（模糊查找）
/// </summary>
/// <param name="_office">string类型 科室名称</param>
/// <param name="_jobTitle">string类型 职称名称</param>
/// <returns>DTO4DoctorInfo的对象数组</returns>
public DTO4DoctorInfo[] selectAllByJobTitleAndOffice(string _office, string _jobTitle)
{
string sql = "select * from doctorInfo where office like '%" + _office + "%' and jobTitle like '%" + _jobTitle + "%'";
DataSet dt = DBOperater.selectSql(sql);
if (dt != null && dt.Tables.Count > 0 && dt.Tables[0].Rows.Count > 0)
{
DTO4DoctorInfo[] dto4dArray = new DTO4DoctorInfo[dt.Tables[0].Rows.Count];
for (int i = 0; i < dt.Tables[0].Rows.Count; i++)
{
dto4dArray[i] = new DTO4DoctorInfo();
dto4dArray[i].Id = dt.Tables[0].Rows[i]["id"].ToString();
dto4dArray[i].Name = (string)dt.Tables[0].Rows[i]["name"];
dto4dArray[i].JobTitle = (string)dt.Tables[0].Rows[i]["jobTitle"];
dto4dArray[i].Office = (string)dt.Tables[0].Rows[i]["office"];
dto4dArray[i].Psw = (string)dt.Tables[0].Rows[i]["psw"];
dto4dArray[i].Upper = dt.Tables[0].Rows[i]["upper"].ToString();
dto4dArray[i].Gender = (string)dt.Tables[0].Rows[i]["gender"];
dto4dArray[i].Birth = (DateTime)dt.Tables[0].Rows[i]["birth"];
}
return(dto4dArray);
}
else
{
DTO4DoctorInfo[] dto4dArray = new DTO4DoctorInfo[1];
return(dto4dArray);
}
}
/// <summary>
/// 按姓名查找排班信息（模糊查找）
/// </summary>
/// <param name="_doctorName">string doctorName</param>
/// <returns>DTO4Arrangement对象数组</returns>
public DTO4Arrangement[] selectAllByDoctorName(string _doctorName)
{
string sql = "select * from arrangement where doctorName like '%" + _doctorName + "%'";
DataSet dt = DBOperater.selectSql(sql);
if (dt != null && dt.Tables.Count > 0 && dt.Tables[0].Rows.Count > 0)
{
DTO4Arrangement[] dto4aArray = new DTO4Arrangement[dt.Tables[0].Rows.Count];
for (int i = 0; i < dt.Tables[0].Rows.Count; i++)
{
dto4aArray[i] = new DTO4Arrangement();
dto4aArray[i].Id = dt.Tables[0].Rows[i]["id"].ToString();
dto4aArray[i].Office = (string)dt.Tables[0].Rows[i]["office"];
dto4aArray[i].BeginTime = (DateTime)dt.Tables[0].Rows[i]["beginTime"];
dto4aArray[i].FinishTime = (DateTime)dt.Tables[0].Rows[i]["finishTime"];
dto4aArray[i].DoctorId = dt.Tables[0].Rows[i]["doctorId"].ToString();
dto4aArray[i].DoctorName = (string)dt.Tables[0].Rows[i]["doctorName"];
dto4aArray[i].DoctorJobTitle = (string)dt.Tables[0].Rows[i]["doctorJobTitle"];
}
return(dto4aArray);
}
else
{
DTO4Arrangement[] dto4aArray = new DTO4Arrangement[1];
return(dto4aArray);
}
}
/// <summary>
/// 通过id和密码查找管理员信息
/// </summary>
/// <param name="_id">string类型 管理员id</param>
/// <param name="_psw">string类型 管理员密码</param>
/// <returns>DTO4ManagerInfo类型的对象</returns>
public DTO4ManagerInfo selectAllByIdAndPsw(string _id, string _psw)
{
string sql = "select * from managerInfo where id =" + _id + " and psw = '" + _psw + "'";
DTO4ManagerInfo dto4m = new DTO4ManagerInfo();
DataSet dt = DBOperater.selectSql(sql);
if (dt != null && dt.Tables.Count > 0 && dt.Tables[0].Rows.Count > 0)
{
dto4m.Id = dt.Tables[0].Rows[0]["id"].ToString();
dto4m.Name = (string)dt.Tables[0].Rows[0]["name"];
dto4m.Psw = (string)dt.Tables[0].Rows[0]["psw"];
}
return(dto4m);
}
//根据排班表id查找排班修改记录
/// <summary>
/// 根据排班表id查找排班修改记录
/// </summary>
/// <param name="_arrangementId">string 排班表id</param>
/// <returns></returns>
public DTO4ChangeRecord selectAllByArrangementId(string _arrangementId_self)
{
String sql = "select * from changeRecord where arrangementId_self = " + _arrangementId_self;
DataSet dt = DBOperater.selectSql(sql);
DTO4ChangeRecord dto4c = new DTO4ChangeRecord();
if (dt != null && dt.Tables.Count > 0 && dt.Tables[0].Rows.Count > 0)
{
dto4c.Id = dt.Tables[0].Rows[0]["id"].ToString();
dto4c.ArrangementId_self = (string)dt.Tables[0].Rows[0]["arrangementId_self"];
dto4c.Status = (string)dt.Tables[0].Rows[0]["status"];
dto4c.Remark = (string)dt.Tables[0].Rows[0]["remark"];
dto4c.Operate = (string)dt.Tables[0].Rows[0]["operate"];
}
return(dto4c);
}
//按医生姓名和科室查找医生信息
public DTO4DoctorInfo selectAllByNameAndOffice(string _name, string _office)
{
string sql = "select * from doctorInfo where name ='" + _name + "' and office ='" + _office + "'";
DataSet dt = DBOperater.selectSql(sql);
DTO4DoctorInfo dto4d = new DTO4DoctorInfo();
if (dt != null && dt.Tables.Count > 0 && dt.Tables[0].Rows.Count > 0)
{
dto4d.Id = dt.Tables[0].Rows[0]["id"].ToString();
dto4d.Name = (string)dt.Tables[0].Rows[0]["name"];
dto4d.JobTitle = (string)dt.Tables[0].Rows[0]["jobTitle"];
dto4d.Office = (string)dt.Tables[0].Rows[0]["office"];
dto4d.Psw = (string)dt.Tables[0].Rows[0]["psw"];
dto4d.Upper = dt.Tables[0].Rows[0]["upper"].ToString();
dto4d.Gender = (string)dt.Tables[0].Rows[0]["gender"];
}
return(dto4d);
}
/// <summary>
/// 按排班表id查找排班表信息
/// </summary>
/// <param name="_id">string id</param>
/// <returns>DTO4Arrangement对象</returns>
public DTO4Arrangement selectAllById(string _id)
{
string sql = "select * from arrangement where id = " + _id;
DataSet dt = DBOperater.selectSql(sql);
DTO4Arrangement dto4a = new DTO4Arrangement();
if (dt != null && dt.Tables.Count > 0 && dt.Tables[0].Rows.Count > 0)
{
dto4a.Id = dt.Tables[0].Rows[0]["id"].ToString();
dto4a.Office = (string)dt.Tables[0].Rows[0]["office"];
dto4a.BeginTime = (DateTime)dt.Tables[0].Rows[0]["beginTime"];
dto4a.FinishTime = (DateTime)dt.Tables[0].Rows[0]["finishTime"];
dto4a.DoctorId = dt.Tables[0].Rows[0]["doctorId"].ToString();
dto4a.DoctorName = (string)dt.Tables[0].Rows[0]["doctorName"];
dto4a.DoctorJobTitle = (string)dt.Tables[0].Rows[0]["doctorJobTitle"];
}
return(dto4a);
}
/// <summary>
/// 通过id,psw查找医生信息
/// </summary>
/// <param name="_id">string类型 医生id</param>
/// <param name="_psw">string类型 医生密码</param>
/// <returns>DTO4DoctorInfo的对象</returns>
public DTO4DoctorInfo selectAllByIdAndPsw(string _id, string _psw)
{
string sql = "select * from doctorInfo where id =" + _id + " and psw = '" + _psw + "'";
DataSet dt = DBOperater.selectSql(sql);
DTO4DoctorInfo dto4d = new DTO4DoctorInfo();
if (dt != null && dt.Tables.Count > 0 && dt.Tables[0].Rows.Count > 0)
{
dto4d.Id = dt.Tables[0].Rows[0]["id"].ToString();
dto4d.Name = (string)dt.Tables[0].Rows[0]["name"];
dto4d.JobTitle = (string)dt.Tables[0].Rows[0]["jobTitle"];
dto4d.Office = (string)dt.Tables[0].Rows[0]["office"];
dto4d.Psw = (string)dt.Tables[0].Rows[0]["psw"];
dto4d.Upper = dt.Tables[0].Rows[0]["upper"].ToString();
dto4d.Gender = (string)dt.Tables[0].Rows[0]["gender"];
dto4d.Birth = (DateTime)dt.Tables[0].Rows[0]["birth"];
}
return(dto4d);
}
public DTO4Arrangement selectArrangementIdByNameAndBegintime(string _name, DateTime _beginTime)
{
string sql = "select * from arrangement where convert(varchar,beginTime,120) like '%" + _beginTime.ToString("yyyy-MM-dd") + "%' and doctorName ='" + _name + "'";
DataSet dt = DBOperater.selectSql(sql);
if (dt != null && dt.Tables.Count > 0 && dt.Tables[0].Rows.Count > 0)
{
DTO4Arrangement dto4 = new DTO4Arrangement();
dto4.Id = dt.Tables[0].Rows[0]["id"].ToString();
dto4.Office = (string)dt.Tables[0].Rows[0]["office"];
dto4.BeginTime = (DateTime)dt.Tables[0].Rows[0]["beginTime"];
dto4.FinishTime = (DateTime)dt.Tables[0].Rows[0]["finishTime"];
dto4.DoctorId = dt.Tables[0].Rows[0]["doctorId"].ToString();
dto4.DoctorName = (string)dt.Tables[0].Rows[0]["doctorName"];
dto4.DoctorJobTitle = (string)dt.Tables[0].Rows[0]["doctorJobTitle"];
return(dto4);
}
else
{
DTO4Arrangement dto4 = new DTO4Arrangement();
return(dto4);
}
}
/// <summary>
/// 根据科室名称查找当前科室里医生有哪几种职称
/// </summary>
/// <param name="_office">string类型 科室名称</param>
/// <returns>DataSet类型 Rows[n]["jobTitle"]</returns>
public DataSet selectJobTitleByOffice(string _office)
{
string sql = "select distinct jobTitle from doctorInfo where office = '" + _office + "'";
return(DBOperater.selectSql(sql));
}
/// <summary>
/// 查询有哪几个科室
/// </summary>
/// <returns>DataSet类型 Rows[n]["office"]</returns>
public DataSet selectOffice()
{
string sql = "select DISTINCT office from doctorInfo";
return(DBOperater.selectSql(sql));
}
/// <summary>
/// 按照id修改排班表记录
/// </summary>
/// <param name="_id">string id</param>
/// <param name="_beginTime">DateTime begibTime</param>
/// <param name="_finishTime">DateTime finshTime</param>
/// <param name="_doctorId">string doctorId</param>
/// <param name="_office">string office</param>
/// <param name="_doctorName"> string doctorName</param>
/// <param name="_doctorJobTitle">string doctorJobTitle</param>
/// <returns>int flag</returns>
public int updateAllById(string _id, DateTime _beginTime, DateTime _finishTime, string _doctorId, string _office, string _doctorName, string _doctorJobTitle)
{
string sql = "update arrangement set beginTime = '" + _beginTime + "',finishTime = '" + _finishTime + "',doctorId = " + _doctorId + ",office = '" + _office + "',doctorName = '" + _doctorName + "',doctorJobTitle = '" + _doctorJobTitle + "' where id = " + _id;
return(DBOperater.executeSql(sql));
}
/// <summary>
/// 新增排班表记录
/// </summary>
/// <param name="_id"></param>
/// <param name="_beginTime"></param>
/// <param name="_finishTime"></param>
/// <param name="_doctorId"></param>
/// <param name="_office"></param>
/// <param name="_doctorName"></param>
/// <param name="_doctorJobTitle"></param>
/// <returns>int flag</returns>
public int insertArrangement(DateTime _beginTime, DateTime _finishTime, string _doctorId, string _office, string _doctorName, string _doctorJobTitle)
{
string sql = "insert into arrangement(beginTime,finishTime,doctorId,office,doctorName,doctorJobTitle) values ( '" + _beginTime + "','" + _finishTime + "'," + _doctorId + ",'" + _office + "','" + _doctorName + "','" + _doctorJobTitle + "')";
return(DBOperater.executeSql(sql));
}
//新增一条排班表修改记录
public int insertChangeRecord(int _arrangementId_self, int _arrangementId_others, string _remark, string _status)
{
string sql = "insert into changeRecord(arrangementId_self,arrangementId_others,remark,status) values ('" + _arrangementId_self + "','" + _arrangementId_others + "','" + _remark + "','" + _status + "')";
return(DBOperater.executeSql(sql));
}
public DataSet selectNameByOffice(string _office) //2016/10/9 新增功能查询医生姓名By科室
{
string sql = "select distinct name from doctorInfo where office = '" + _office + "'";
return(DBOperater.selectSql(sql));
}
//根据记录id修改状态和备注
public int updateStatusAndRemarkById(string _id, string _status, string _remark, string _operate)
{
string sql = "update changeRecord set status = '" + _status + "',remark = '" + _remark + "',operate ='" + _operate + "' where id =" + _id;
return(DBOperater.executeSql(sql));
}
/// <summary>
/// 按照排班表id删除排班记录
/// </summary>
/// <param name="_id">string id</param>
/// <returns>int flag</returns>
public int deleteAllById(string _id)
{
string sql = "delete from arrangement where id = " + _id;
return(DBOperater.executeSql(sql));
}