//按部门查询
public static DataTable Work2(Entity.Department dp)
{
string sqlstr = "select WID,PName,Work.PID,Wtime,WState,DepName from Work,Person,Department,Job where Person.PID=Work.PID and Person.JID=Job.JID and Job.DepID=Department.DepID and Department.DepID='" + dp.depID + "' ";
DataTable dt1 = DBHelper.GetDT(sqlstr);
return(dt1);
}
//查询状态
public static DataTable AgreeMent5(Entity.AgreeMent AgreeMent5)
{
string sqlstr = "select * from AgreeMent where AMState='" + AgreeMent5.amState + "'";
DataTable dt = DBHelper.GetDT(sqlstr);
return(dt);
}
//按时间查询
public static DataTable Work1(Entity.Work work1)
{
string sqlstr = "select WID,PName,Work.PID,Wtime,WState from Work,Person where Person.PID=Work.PID and Wtime='" + work1.wTime + "' ";
DataTable dt1 = DBHelper.GetDT(sqlstr);
return(dt1);
}
//查询
public static DataTable AgreeMent2(Entity.AgreeMent AgreeMent2)
{
string sqlstr = "select * from AgreeMent";
DataTable dt = DBHelper.GetDT(sqlstr);
return(dt);
}
//查询乙方
public static DataTable AgreeMent4(Entity.AgreeMent AgreeMent4)
{
string sqlstr = "select * from AgreeMent where PID like '%" + AgreeMent4.pID + "%'";
DataTable dt = DBHelper.GetDT(sqlstr);
return(dt);
}
/// <summary>
/// 按开始时间查询
/// </summary>
/// <param name="leave6"></param>
/// <returns></returns>
public static DataTable LeaveLstart(Entity.Leave leave6)
{
string sqlstr = "select LID,Leave.PID,PName,LWhy,LStart,LStop,LBoss,Lstate from Leave,Person where Leave.PID=Person.PID and year(LStart) = " + leave6.lStart.Year + " and month(LStart) = " + leave6.lStart.Month + " and day(LStart) = " + leave6.lStart.Day + "";
DataTable dt = (DataTable)DBHelper.GetDT(sqlstr);
return(dt);
}
//按员工姓名查询查询
public static DataTable LeavePName(Entity.Leave leave5, string name)
{
string sqlstr = "select LID,Leave.PID,PName,LWhy,LStart,LStop,LBoss,Lstate from Leave,Person where Leave.PID=Person.PID and PName like '%" + name + "%'";
DataTable dt = (DataTable)DBHelper.GetDT(sqlstr);
return(dt);
}
//按部门查询
public static DataTable LeaveDep(Entity.Department dp)
{
string sqlstr = "select LID,Leave.PID,PName,LWhy,LStart,LStop,LBoss,Lstate from Leave,Person,Department,Job where Person.PID=Leave.PID and Person.JID=Job.JID and Job.DepID=Department.DepID and Department.DepID='" + dp.depID + "' ";
DataTable dt1 = DBHelper.GetDT(sqlstr);
return(dt1);
}
//下拉列表部门的查询
public static DataTable work3()
{
string sqlstr = "select * from Department";
DataTable dt = DBHelper.GetDT(sqlstr);
return(dt);
}
//部门的下拉列表
public static DataTable PersonDT()
{
string sqlstr1 = "select * from Department";
DataTable dt1 = DBHelper.GetDT(sqlstr1);
return(dt1);
}
//查询请假表
public static DataTable LeaveAll()
{
string sqlstr = "select LID,Leave.PID,PName,LWhy,LStart,LStop,LBoss,Lstate from Leave,Person where Leave.PID=Person.PID";
DataTable dt = (DataTable)DBHelper.GetDT(sqlstr);
return(dt);
}
//岗位的下拉表
public static DataTable PersonDT1(string DepName)
{
string sqlstr1 = "select JName,JID from Job,Department where Job.DepID=Department.DepID and DepName='" + DepName + "'";
DataTable dt2 = DBHelper.GetDT(sqlstr1);
return(dt2);
}
//下拉框的显示
public static DataTable LeaveComBox()
{
string sqlstr = "select * from Department";
DataTable dt = (DataTable)DBHelper.GetDT(sqlstr);
return(dt);
}
//按员工姓名查询
public static DataTable FindByName(Entity.Person p)
{
string sql = "select PID,PName,PSex,PEthnic,PAges,PPhone,PQQ,PMail,PInTime,PSubject,PSchool,PFaction,PMarry,PInteresting,PBirthday,PCardID,JName from Person,Job where Job.JID=Person.JID and Person.PName Like '%" + p.pName + "%' and Person.PState !='待审核' and PState!='已辞退'";
DataTable dt = DBHelper.GetDT(sql);
return(dt);
}
//按评估等级查询
public static DataTable Appraise5(Entity.Appraise Appraise5)
{
string sqlstr = "select * from Appraise where ARate='" + Appraise5.aRate + "' and AType!='待审核'";
DataTable dt = DBHelper.GetDT(sqlstr);
return(dt);
}
//按员工编号查询
public static DataTable Appraise4(Entity.Appraise Appraise4)
{
string sqlstr = "select * from Appraise where PID like '%" + Appraise4.pID + "%' and AType!='待审核'";
DataTable dt = DBHelper.GetDT(sqlstr);
return(dt);
}
public static System.Data.DataTable dd()
{
string sqlstr = "select * from lesson ";
return(DBHelper.GetDT(sqlstr));
}
//按入职时间查询
public static DataTable PersonDT6(Entity.Person person3)
{
string sqlstr3 = "select PID,PName,PSex,PEthnic,PAges,PPhone,PQQ,PMail,PInTime,PSubject,PSchool,PFaction,PMarry,PInteresting,PBirthday,PCardID,JName from Person,Job where Job.JID=Person.JID and Person.PInTime = '" + person3.pInTime + "' and Person.PState !='待审核' and PState!='已辞退'";
DataTable dt3 = DBHelper.GetDT(sqlstr3);
return(dt3);
}
//查询单个人信息
public static DataTable FindOnly(Entity.Person p)
{
string sql = "select * from Person,Job,Department where Job.JID=Person.JID and Department.DepID=Job.DepID and PID=" + p.pID + " and PState!='待审核' and PState!='已辞退'";
DataTable dt = DBHelper.GetDT(sql);
return(dt);
}
//查看评估
public static DataTable Appraise2(Entity.Appraise Appraise2)
{
string sqlstr = "select * from Appraise where AType!='待审核'";
DataTable dt1 = DBHelper.GetDT(sqlstr);
return(dt1);
}
///不按月份执行
/// <summary>
/// 按部门查询 利用pID传递部门编号
/// </summary>
/// <param name=""></param>
/// <returns></returns>
public static DataTable FindByDep(Entity.Pay p)
{
string sql = "select PayID,PName,Pay.PID,PayAll,PayBase,PayAllDay,PayDay,PayAdd,PayDel,PayIn,PayTime from Pay,Person,Job where Pay.PID=Person.PID and Person.JID=Job.JID and Job.DepID=" + p.pID + " and Person.PState='正式'";
return(DBHelper.GetDT(sql));
}
/// <summary>
/// 查询全部工资详情
/// </summary>
/// <param name=""></param>
/// <returns></returns>
public static DataTable AllSalary()
{
string sql = "select PayID,PName,Pay.PID,PayAll,PayBase,PayAllDay,PayDay,PayAdd,PayDel,PayIn,PayTime from Pay,Person where Pay.PID=Person.PID and Person.PState='正式' ";
return(DBHelper.GetDT(sql));
}
/// <summary>
/// 按月份工号查询
/// </summary>
/// <param name="p"></param>
/// <returns></returns>
public static DataTable FindByMPID(Entity.Pay p)
{
string sql = "select PayID,PName,Pay.PID,PayAll,PayBase,PayAllDay,PayDay,PayAdd,PayDel,PayIn,PayTime from Pay,Person where Pay.PID=Person.PID and year(PayTime)=" + p.payTime.ToString("yyyy") + "and month(PayTime)=" + p.payTime.ToString("MM") + " and Pay.PID=" + p.pID + " and Person.PState='正式'";
return(DBHelper.GetDT(sql));
}
//-----------------------------------------
/// <summary>
/// 查询所有员工的PID
/// </summary>
/// <returns></returns>
public static DataTable SearchPID()
{
string sql = "select PID from Person where PState='正式' or PState='休假'";
return(DBHelper.GetDT(sql));
}
//按培训地点查看
public static DataTable place(Entity.Train a)
{
string s = "select lename 培训课程,TPTeacher 讲师,tpadr 培训地点,Tpcount 培训人数,tpstart 开始时间,tpstop 结束时间 from lesson,trainplan where trainplan.leid=lesson.leid and TPAdr like '%" + a.tpAdr + "%'";
return(DBHelper.GetDT(s));
}
//查看培训计划
public static DataTable dt()
{
string s = "select tpid 培训计划编号,lename 培训课程,letype 课程类型,TPTeacher 讲师,tpadr 培训地点,Tpcount 培训人数,tpstart 开始时间,tpstop 结束时间 from lesson,trainplan where trainplan.leid=lesson.leid";
return(DBHelper.GetDT(s));
}
public static System.Data.DataTable ddd(Entity.Train d)
{
string sql = "select NID 记录编号,PName 人员姓名,LeName 培训课程,NResult 培训效果,NTexts 教员评价 from Tpnotes,Lesson,Person,TrainPlan where Person.PID=Tpnotes.PID and Tpnotes.TPID=TrainPlan.TPID and Lesson.LeID=TrainPlan.LeID and TrainPlan.TPID=" + d.tpID + "";
return(DBHelper.GetDT(sql));
}
/// <summary>
/// 按实际发放工资段查询 利用payDel传递最小值 payAdd传递最大值
/// </summary>
/// <param name="p"></param>
/// <returns></returns>
public static DataTable FindByMoney(Entity.Pay p)
{
string sql = "select PayID,PName,Pay.PID,PayAll,PayBase,PayAllDay,PayDay,PayAdd,PayDel,PayIn,PayTime from Pay,Person where Pay.PID=Person.PID and ( PayIn between " + p.payDel + " and " + p.payAdd + " ) and Person.PState='正式'";
return(DBHelper.GetDT(sql));
}
//删除课程
public static DataTable dt()
{
string sql = "select leID 课程编号,lename 课程名称,letype 课程类型,letext 课程内容 from lesson";
return(DBHelper.GetDT(sql));
}
/// <summary>
/// 小窗口显示的培训计划
/// </summary>
/// <returns></returns>
public static System.Data.DataTable SmallTP()
{
string sql = "select LeName 培训课程 ,TPTeacher 课程讲师,TPAdr 培训地点,TPCount 参训人数,TPStart 开始时间,TPStop 结束时间,LeText 课程内容 from TrainPlan,Lesson where TrainPlan.LeID=Lesson.LeID";
return(DBHelper.GetDT(sql));
}