public DataSet GetModelByDataSet(string projectId)
{
string sql = "select KCWDB800Id,KCWDB800.ModuleTreeId,Item,Module,Length,ExRightDis,ExLength,ExWidth,ExHeight," +
"FCSide,FCSideLeft,FCSideRight,FCBlindNo,SidePanel,DPSide,LightType,SSPType,Gutter,GutterWidth," +
"ANSUL,ANSide,MARVEL,Japan from KCWDB800";
sql += " inner join ModuleTree on KCWDB800.ModuleTreeId=ModuleTree.ModuleTreeId";
sql += " inner join DrawingPlan on ModuleTree.DrawingPlanId=DrawingPlan.DrawingPlanId";
sql += string.Format(" where ProjectId={0}", projectId);
sql += " order by Item,Module";
return(SQLHelper.GetDataSet(sql));
}
public DataSet GetModelByDataSet(string projectId)
{
string sql = "select UCJDB800Id,UCJDB800.ModuleTreeId,Item,Module,Length,ExRightDis,ExLength,ExWidth,ExHeight," +
"FCSide,FCSideLeft,FCSideRight,FCBlindNo,UVType,LightType,LightCable,SSPType,Gutter,GutterWidth," +
"ANSUL,ANSide,ANDetectorEnd,ANDetectorNo,ANDetectorDis1,ANDetectorDis2,ANDetectorDis3,ANDetectorDis4,ANDetectorDis5,MARVEL,Japan from UCJDB800";
sql += " inner join ModuleTree on UCJDB800.ModuleTreeId=ModuleTree.ModuleTreeId";
sql += " inner join DrawingPlan on ModuleTree.DrawingPlanId=DrawingPlan.DrawingPlanId";
sql += string.Format(" where ProjectId={0}", projectId);
sql += " order by Item,Module";
return(SQLHelper.GetDataSet(sql));
}
/// <summary>
///根据部门名称查询考勤对象，返回DataTable，用于导出Excel打印。
/// </summary>
/// <param name="last_year_month"></param>
/// <param name="dept"></param>
/// <returns></returns>
public DataTable ExportKaoQinPrint(string last_year_month, string dept)
{
string sql = "select ROW_NUMBER() over (order by id) as 序号,人员编号,姓名,应出勤,实际出勤,出差,旷工,年假,事假,病假,正常调休,产假,陪产假,婚假,丧假,迟到早退次数,缺卡次数,前夜班次数,休息日加班,节假日加班,休息日出差,后夜班次数,后夜班调休次数,打卡签到次数,工作时长,'' as 本人签字,备注,考勤年月,部门,班组,更改日期 from imp_attendance";
sql += " where 考勤年月 = '{0}' and 部门 = '{1}'";
sql += " order by 排序";
sql = string.Format(sql, last_year_month, dept);
DataTable dt_export = SQLHelper.GetDataSet(sql).Tables[0];
for (int i = 0; i < dt_export.Rows.Count; i++)
{
dt_export.Rows[i]["序号"] = i + 1;
}
return(dt_export);
}
public DataTable GetActualDataBetweenTimePeriod(string varName, DateTime dtStart, DateTime dtFinish)
{
//通过变量名和时间区域，查找Value和InsertTime
string sql = "select Value,CONVERT(varchar(100), InsertTime, 108) as Time from ActualData where VarName='{0}' and InsertTime between '{1}' and '{2}' ";
sql = string.Format(sql, varName, dtStart, dtFinish);
try
{
return(SQLHelper.GetDataSet(sql).Tables[0]);
}
catch (Exception)
{
return(null);
}
}
/// <summary>
/// 查询用户 不带参数
/// </summary>
/// <returns></returns>
public DataSet GetUsers()
{
string sqlStr = "select * from tbUser";
SQLHelper help = new SQLHelper();
DataSet ds = help.GetDataSet(sqlStr);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
return(ds);
}
else
{
return(null);
}
}
/// <summary>
/// 查询当前考评年月优良率人员编号
/// </summary>
/// <param name="last_year_month"></param>
/// <param name="dept"></param>e
/// <returns></returns>
public string[] GetCurrentKaoPingYouLiangRenYuanHNBH(string last_year_month, string dept)
{
string sql = "select 人员编号 from imp_evaluation where 考评年月 = '{0}' and 部门 = '{1}' and 考评得分 >= 80";
sql = string.Format(sql, last_year_month, dept);
DataTable dt = SQLHelper.GetDataSet(sql).Tables[0];
string[] strName = new string[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
strName[i] = dt.Rows[i]["人员编号"].ToString();
}
return(strName);
}
/// <summary>
///根据部门名称查询加班对象，返回DataTable，用于导出Excel。
/// </summary>
/// <param name="last_year_month"></param>
/// <param name="dept"></param>
/// <returns></returns>
public DataTable ExportJiaBan(string last_year_month, string dept)
{
string sql = " select ROW_NUMBER() over (order by id) as 序号,考勤年月,部门,人员编号,姓名,前夜班次数,休息日加班,节假日加班,正常调休,后夜班次数,后夜班调休次数,金额,备注 from imp_overtime where 考勤年月 = '{0}' and 部门 = '{1}' and 金额 > 0 union all select ROW_NUMBER() over (order by id) as 序号,考勤年月, 部门, 人员编号, 姓名, 前夜班次数, 休息日加班, 节假日加班, 正常调休, 后夜班次数, 后夜班调休次数,0 as 金额,'' as 备注 from imp_attendance where 考勤年月 = '{2}' and 部门 = '{3}' and not EXISTS (select ROW_NUMBER() over (order by id) as 序号,考勤年月, 部门, 人员编号, 姓名, 前夜班次数, 休息日加班, 节假日加班, 正常调休, 后夜班次数,后夜班调休次数, 金额, 备注 from imp_overtime where 考勤年月 = '{4}' and 部门 = '{5}' and 金额 > 0 and imp_attendance.人员编号 = imp_overtime.人员编号)";
sql = string.Format(sql, last_year_month, dept, last_year_month, dept, last_year_month, dept);
DataTable dt_export = SQLHelper.GetDataSet(sql).Tables[0];
for (int i = 0; i < dt_export.Rows.Count; i++)
{
dt_export.Rows[i]["序号"] = i + 1;
}
return(dt_export);
}
/// <summary>
///根据部门名称查询考评对象，返回DataTable，用于导出Excel。
/// </summary>
/// <param name="last_year_month"></param>
/// <param name="dept"></param>
/// <returns></returns>
public DataTable ExportKaoPingByDept(string last_year_month, string dept)
{
string sql = "select ROW_NUMBER() over (order by id) as 序号, 考评年月, 部门, 人员编号, 姓名, 德, 能, 勤, 绩, 考评得分, 考评意见, 具体解释说明, 备注 from imp_evaluation";
sql += " where 考评年月 = '{0}' and 部门 = '{1}'";
sql += " order by 排序";
sql = string.Format(sql, last_year_month, dept);
DataTable dt_export = SQLHelper.GetDataSet(sql).Tables[0];
for (int i = 0; i < dt_export.Rows.Count; i++)
{
dt_export.Rows[i]["序号"] = i + 1;
}
return(dt_export);
}
/// <summary>
///根据部门名称查询加班对象，返回DataTable，用于导出Excel打印。
/// </summary>
/// <param name="last_year_month"></param>
/// <param name="dept"></param>
/// <returns></returns>
public DataTable ExportJiaBanPrint(string last_year_month, string dept)
{
string sql = "select 0 as 序号,a.人员编号,a.姓名,b.前夜班次数,b.休息日加班,b.节假日加班,b.正常调休,b.后夜班次数,b.后夜班调休次数,b.金额,b.备注,b.考勤年月,b.部门,b.更改日期 from emp_bas a";
sql += " left join imp_overtime b on a.人员编号 = b.人员编号";
sql += " left join imp_attendance c on a.人员编号 = c.人员编号";
sql += " where c.考勤年月 = '{0}' and c.部门 = '{1}' and b.考勤年月 = '{2}' and b.部门 = '{3}' order by c.排序";
sql = string.Format(sql, last_year_month, dept, last_year_month, dept);
DataTable dt_export = SQLHelper.GetDataSet(sql).Tables[0];
for (int i = 0; i < dt_export.Rows.Count; i++)
{
dt_export.Rows[i]["序号"] = i + 1;
}
return(dt_export);
}
public DataSet GetPosts(string ModuleName, string UserName, string postTitle)
{
string sqlStr = "select p.postTitle,u.UserName,p.buildDate from tbPost p,tbUser u,tbModule m where p.UserId=u.UserId and p.ModuleId=m.ModuleId and 1=1";
if (ModuleName != "")
{
sqlStr += " and ModuleName=@ModuleName";
}
if (UserName != "")
{
sqlStr += " and UserName like %@userName";
}
if (postTitle != "")
{
sqlStr += " and postTitle like %@postTitle";
}
SqlParameter[] param =
{
new SqlParameter("@ModuleName", ModuleName),
new SqlParameter("@userName", UserName),
new SqlParameter("@postTitle", postTitle)
};
SQLHelper help = new SQLHelper();
DataSet ds = help.GetDataSet(sqlStr, param);
try
{
if (ds.Tables[0].Rows.Count > 0)
{
return(ds);
}
else
{
return(null);
}
}
catch
{
return(null);
}
}
/// <summary>
/// Login方法，带两个参数，用户名和密码
/// </summary>
/// <param name="userName"></param>
/// <param name="userPassword"></param>
/// <returns></returns>
public DataSet Login(string userName, string userPassword)
{
string sqlStr = "select * from tbUser where userName=@userName and userPswd=@userPswd";
SqlParameter[] param =
{
new SqlParameter("@userName", userName),
new SqlParameter("@userPswd", userPassword)
};
SQLHelper help = new SQLHelper();
DataSet ds = help.GetDataSet(sqlStr, param);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
return(ds);
}
else
{
return(null);
}
}
public DataSet GetModule()
{
string sqlStr = "select * from tbModule";
try
{
SQLHelper help = new SQLHelper();
DataSet ds = help.GetDataSet(sqlStr);
if (ds.Tables[0].Rows.Count > 0)
{
return(ds);
}
else
{
return(null);
}
}
catch
{
return(null);
}
}
public User GetUserByuserId(int userId)
{
string sqlStr = "select * from tbUser where userID=" + userId + "";
SQLHelper help = new SQLHelper();
DataSet ds = help.GetDataSet(sqlStr);
User user = new User();
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
user.UserName = ds.Tables[0].Rows[0]["userName"].ToString();
user.UserID = userId;
user.UserEmail = ds.Tables[0].Rows[0]["userEmail"].ToString();
user.UserAddress = ds.Tables[0].Rows[0]["userAddress"].ToString();
user.UserAge = Convert.ToInt32(ds.Tables[0].Rows[0]["userAge"].ToString().Trim());
user.UserPswd = ds.Tables[0].Rows[0]["userPswd"].ToString();
user.UserSex = ds.Tables[0].Rows[0]["userSex"].ToString();
user.UserRole = ds.Tables[0].Rows[0]["userRole"].ToString();
user.UserPhoto = ds.Tables[0].Rows[0]["userPhoto"].ToString();
}
return(user);
}
/// <summary>
///根据部门名称查询考评对象，返回DataTable，用于导出Excel打印。
/// </summary>
/// <param name="last_year_month"></param>
/// <param name="dept"></param>
/// <returns></returns>
public DataTable ExportKaoPingPrint(string last_year_month, string dept, double dYouLiangLv)
{
string sql = "select ROW_NUMBER() over (order by id) as 序号, 人员编号, 姓名, 德, 能, 勤, 绩, 考评得分, 考评意见, 具体解释说明, 备注, 考评年月, 部门, 更改日期 from imp_evaluation";
sql += " where 考评年月 = '{0}' and 部门 = '{1}'";
sql += " order by 排序";
sql = string.Format(sql, last_year_month, dept);
DataTable dt_export = SQLHelper.GetDataSet(sql).Tables[0];
DataRow row = dt_export.NewRow();
//row["人员编号"] = "优良率"; //此处不用写，NPOI合并单元格后会将值清空。
row["考评意见"] = dYouLiangLv.ToString("0.00%");
dt_export.Rows.Add(row);
for (int i = 0; i < dt_export.Rows.Count - 1; i++)
{
dt_export.Rows[i]["序号"] = i + 1;
}
return(dt_export);
}
/// <summary>
/// 动态获取专业
/// </summary>
/// <returns></returns>
public DataSet GetSpeciality()
{
string sql = "SELECT SpecialityID,SpecialityName FROM tbSpecialityInfo";
return(SQLHelper.GetDataSet(sql));
}
/// <summary>
/// 通过学期查询课程名称（修改/删除课程用）
/// </summary>
/// <param name="Semester"></param>
/// <returns></returns>
public DataSet GetCourseNameForUpdate(string Semester, string ClassName)
{
string sql = "SELECT CourseName FROM tbCourseInfo WHERE tbCourseInfo.Semester='" + Semester + "' AND ClassName='" + ClassName + "'";
return(SQLHelper.GetDataSet(sql));
}
/// <summary>
/// 级联班级下拉框，动态填充学期下拉框
/// </summary>
/// <param name="SpecialityID"></param>
/// <returns></returns>
public DataSet GetSemesterByClassName(string ClassID)
{
string sql = "SELECT DISTINCT Semester FROM tbCourseInfo WHERE ClassID = '" + ClassID + "'";
return(SQLHelper.GetDataSet(sql));
}
/// <summary>
/// 动态获取UserName放入combUserName中
/// </summary>
/// <returns></returns>
public DataSet GetUserName()
{
string sql = "SELECT UserName FROM tbUserLoginInfo";
return(SQLHelper.GetDataSet(sql));
}
/// <summary>
/// 查询指定数量的报警数据,返回一个DataTable
/// </summary>
/// <param name="showNum"></param>
public DataTable QueryAlarmDataByNum(int showNum)
{
string sql = "select top " + showNum + " CONVERT(varchar(100), InsertTime, 20) as InsertTime,VarName,AlarmState,Priority,AlarmType,Value,AlarmValue,Operator,Note from AlarmData Order By Id DESC";
return((SQLHelper.GetDataSet(sql)).Tables[0]);
}
/// <summary>
/// 动态获取学院
/// </summary>
/// <returns></returns>
public DataSet GetCollage()
{
string sql = "SELECT CollageID,CollageName FROM tbCollageInfo";
return(SQLHelper.GetDataSet(sql));
}
/// <summary>
/// 获取所有的班级，存放再数据集DataSet中
/// </summary>
/// <returns></returns>
public DataSet GetAllClasses()
{
string sql = "select ClassId,ClassName from StudentClass";
return(SQLHelper.GetDataSet(sql));
}
//获取公司数据集
public DataSet GetCompanyDataSet()
{
string sql = "select * from Company";
return(SQLHelper.GetDataSet(sql));
}