// File: CommonCheckOnWorkAttendanceDAL.cs
// Author: Administrator
// Created: 2009年5月8日 14:15:22
// Purpose: Definition of Class CommonCheckOnWorkAttendanceDAL
using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using OfficeAnywhere.CheckOnWorkAttendance.IDal;
using OfficeAnywhere.CheckOnWorkAttendance.SqlDB;
/// 数据访问层
namespace OfficeAnywhere.CheckOnWorkAttendance.DAL
{
/// 日常考勤---课余、自习考勤（1-5.7），需要日常考勤表
public class CommonCheckOnWorkAttendanceDAL : IDal.ICommonCheckOnWorkAttendance
{
/// 定义数据底层访问对象
private DataSqlDb dataSqlDb;
/// 添加报到信息
/// <param name="registerMemId">报到成员编号</param>
/// <param name="managerId">考勤者（管理员、系统）</param>
/// <param name="cha">状态</param>
public bool Register(int registerMemId , int managerId, char cha)
{
try
{
dataSqlDb = new DataSqlDb();
System.Text.StringBuilder sql = new System.Text.StringBuilder(); //创建StringBuilder字符串
sql.Append("declare @leaveMemId int, @managerId int;"); //定义参数
sql.Append(" set @leaveMemId='" + registerMemId + "';"); //给参数赋值
sql.Append(" set @managerId='" + managerId + "';"); //给参数赋值
if (dataSqlDb.ExcuteSqlRetVal("SELECT Did, AmRegister, PmRegister, NightRegister FROM DailyCheck WHERE (MemId = '" + registerMemId + "') AND (CONVERT(char, AmRegister, 112) = CONVERT(char,GETDATE(), 112) OR CONVERT(char,PmRegister, 112) = CONVERT(char, GETDATE(), 112) OR CONVERT(char,NightRegister, 112) = CONVERT(char, GETDATE(), 112))") ==null)
{
if (cha == 'a')
{
sql.Append("INSERT INTO DailyCheck (AmRegister, MemId, MangerId) VALUES (GetDate(),@leaveMemId,@managerId)");//操作
}
if (cha == 'b')
{
sql.Append("INSERT INTO DailyCheck (PmRegister, MemId, MangerId) VALUES (GetDate(),@leaveMemId,@managerId)");//操作
}
if (cha == 'c')
{
sql.Append("INSERT INTO DailyCheck (NightRegister, MemId, MangerId) VALUES (GetDate(),@leaveMemId,@managerId)");//操作
}
}
else
{
if (cha == 'a')
{
sql.Append("UPDATE DailyCheck SET AmRegister = GETDATE() WHERE MemId = " + registerMemId + " AND (CONVERT(char, AmRegister, 112) = CONVERT(char,GETDATE(), 112) OR CONVERT(char,PmRegister, 112) = CONVERT (char, GETDATE(), 112) OR CONVERT(char,NightRegister, 112) = CONVERT(char, GETDATE(), 112))");
//操作
}
if (cha == 'b')
{
sql.Append("UPDATE DailyCheck SET PmRegister = GETDATE() WHERE MemId = " + registerMemId + " AND (CONVERT(char, AmRegister, 112) = CONVERT(char,GETDATE(), 112) OR CONVERT(char,PmRegister, 112) = CONVERT (char, GETDATE(), 112) OR CONVERT(char,NightRegister, 112) = CONVERT(char, GETDATE(), 112))");
//操作
}
if (cha == 'c')
{
sql.Append("UPDATE DailyCheck SET NightRegister = GETDATE() WHERE MemId = " + registerMemId + " AND (CONVERT(char, AmRegister, 112) = CONVERT(char,GETDATE(), 112) OR CONVERT(char,PmRegister, 112) = CONVERT (char, GETDATE(), 112) OR CONVERT(char,NightRegister, 112) = CONVERT(char, GETDATE(), 112))");
//操作
}
}
return dataSqlDb.ExcuteStrNonRet(sql.ToString());//调用底层函数
}
catch
{
return false;
}
}
/// 修改报到信息
/// <param name="registerMemId">报到成员编号</param>
/// <param name="managerId">考勤者（管理员、系统）</param>
/// <param name="managerId">报到时间</param>
/// <param name="cha">状态</param>
public bool AmendRegister(int registerMemId, int managerId,DateTime registerTime, char cha)
{
try
{
dataSqlDb = new DataSqlDb();
System.Text.StringBuilder sql = new System.Text.StringBuilder(); //创建StringBuilder字符串
sql.Append("declare @leaveMemId int, @managerId int;"); //定义参数
sql.Append(" set @leaveMemId='" + registerMemId + "';"); //给参数赋值
sql.Append(" set @managerId='" + managerId + "';"); //给参数赋值
if (cha == 'a')
{
sql.Append("UPDATE DailyCheck SET AmRegister ='" + registerTime + "' WHERE MemId = " + registerMemId + " AND (CONVERT(char, AmRegister, 112) = CONVERT(char,GETDATE(), 112) OR CONVERT(char,PmRegister, 112) = CONVERT (char, GETDATE(), 112) OR CONVERT(char,NightRegister, 112) = CONVERT(char, GETDATE(), 112))");
//操作
}
if (cha == 'b')
{
sql.Append("UPDATE DailyCheck SET PmRegister ='" + registerTime+ "' WHERE MemId = " + registerMemId + " AND (CONVERT(char, AmRegister, 112) = CONVERT(char,GETDATE(), 112) OR CONVERT(char,PmRegister, 112) = CONVERT (char, GETDATE(), 112) OR CONVERT(char,NightRegister, 112) = CONVERT(char, GETDATE(), 112))");
//操作
}
if (cha == 'c')
{
sql.Append("UPDATE DailyCheck SET NightRegister ='" + registerTime+ "' WHERE MemId = " + registerMemId + " AND (CONVERT(char, AmRegister, 112) = CONVERT(char,GETDATE(), 112) OR CONVERT(char,PmRegister, 112) = CONVERT (char, GETDATE(), 112) OR CONVERT(char,NightRegister, 112) = CONVERT(char, GETDATE(), 112))");
//操作
}
return dataSqlDb.ExcuteStrNonRet(sql.ToString());//调用底层函数
}
catch
{
return false;
}
}
/// 添加离开信息
/// <param name="leaveMemId">离开成员编号</param>
/// <param name="cha">上下午晚上分类：a上午，b下午，c晚上</param>
public bool Leave(int leaveMemId ,char cha)
{
try
{
dataSqlDb = new DataSqlDb();
System.Text.StringBuilder sql = new System.Text.StringBuilder(); //创建StringBuilder字符串
sql.Append("declare @leaveMemId int;"); //定义参数
sql.Append(" set @leaveMemId='" + leaveMemId + "';"); //给参数赋值
if (cha == 'a')
{
sql.Append(
"UPDATE DailyCheck SET AmLeave = GETDATE() WHERE MemId = @leaveMemId AND convert(char,AmRegister,112)=convert(char,getdate(),112)");//操作
}
if (cha == 'b')
{
sql.Append(
"UPDATE DailyCheck SET PmLeave = GETDATE() WHERE MemId = @leaveMemId AND convert(char,PmRegister,112)=convert(char,getdate(),112)");//操作
}
if (cha == 'c')
{
sql.Append(
"UPDATE DailyCheck SET NightLeave = GETDATE() WHERE MemId = @leaveMemId AND convert(char,NightRegister,112)=convert(char,getdate(),112)");//操作
}
return dataSqlDb.ExcuteStrNonRet(sql.ToString());//调用底层函数
}
catch
{
return false;
}
}
/// 修改离开信息
/// <param name="leaveMemId">报到成员编号</param>
/// <param name="leaveTime">报到时间</param>
/// <param name="managerId">修改的考勤者（管理员、系统）</param>
/// <param name="cha">上下午晚上分类：a上午，b下午，c晚上</param>
public bool AmendLeave(int leaveMemId, DateTime leaveTime, int managerId,char cha )
{
try
{
dataSqlDb = new DataSqlDb();
System.Text.StringBuilder sql = new System.Text.StringBuilder(); //创建StringBuilder字符串
sql.Append("declare @leaveMemId int;"); //定义参数
sql.Append(" set @leaveMemId='" + leaveMemId + "';"); //给参数赋值
if (cha == 'a')
{
sql.Append(
"UPDATE DailyCheck SET AmLeave ='" + leaveTime + "' WHERE MemId = @leaveMemId AND convert(char,AmRegister,112)=convert(char,getdate(),112)");//操作
}
if (cha == 'b')
{
sql.Append(
"UPDATE DailyCheck SET PmLeave ='" + leaveTime + "' WHERE MemId = @leaveMemId AND convert(char,PmRegister,112)=convert(char,getdate(),112)");//操作
}
if (cha == 'c')
{
sql.Append(
"UPDATE DailyCheck SET NightLeave ='" + leaveTime + "' WHERE MemId = @leaveMemId AND convert(char,NightRegister,112)=convert(char,getdate(),112)");//操作
}
return dataSqlDb.ExcuteStrNonRet(sql.ToString());//调用底层函数
}
catch
{
return false;
}
}
/// 查询当天已经提交的成员
public DataTable SeeCommon(char cha)
{
try
{
dataSqlDb = new DataSqlDb();
string str="";
if (cha == 'a')
{
str="SELECT DailyCheck.AmRegister As Time, Members.MemName, Members.PhotoPath FROM DailyCheck INNER JOIN Members ON DailyCheck.MemId = Members.MemID WHERE (CONVERT(char, DailyCheck.AmRegister, 112) = CONVERT(char, GETDATE(), 112)) ORDER BY DailyCheck.AmRegister DESC";
}
if (cha == 'b')
{
str="SELECT DailyCheck.PmRegister As Time, Members.MemName, Members.PhotoPath FROM DailyCheck INNER JOIN Members ON DailyCheck.MemId = Members.MemID WHERE (CONVERT(char, DailyCheck.PmRegister, 112) = CONVERT(char, GETDATE(), 112)) ORDER BY DailyCheck.PmRegister DESC";
}
if (cha == 'c')
{
str="SELECT DailyCheck.NightRegister As Time, Members.MemName, Members.PhotoPath FROM DailyCheck INNER JOIN Members ON DailyCheck.MemId = Members.MemID WHERE (CONVERT(char, DailyCheck.NightRegister, 112) = CONVERT(char, GETDATE(), 112)) ORDER BY DailyCheck.NightRegister DESC";
}
return dataSqlDb.GetTable(str);
}
catch{return null;}
}
/// 查询当天离开已经提交的成员
public DataTable SeeLeaveOver(char cha)
{
try
{
dataSqlDb = new DataSqlDb();
string str = "";
if (cha == 'a')
{
str = "SELECT DailyCheck.AmLeave As Time, Members.MemName, Members.PhotoPath FROM DailyCheck INNER JOIN Members ON DailyCheck.MemId = Members.MemID WHERE (CONVERT(char, DailyCheck.AmLeave, 112) = CONVERT(char, GETDATE(), 112)) ORDER BY DailyCheck.AmLeave DESC";
}
if (cha == 'b')
{
str = "SELECT DailyCheck.PmLeave As Time, Members.MemName, Members.PhotoPath FROM DailyCheck INNER JOIN Members ON DailyCheck.MemId = Members.MemID WHERE (CONVERT(char, DailyCheck.PmLeave, 112) = CONVERT(char, GETDATE(), 112)) ORDER BY DailyCheck.PmLeave DESC";
}
if (cha == 'c')
{
str = "SELECT DailyCheck.NightLeave As Time, Members.MemName, Members.PhotoPath FROM DailyCheck INNER JOIN Members ON DailyCheck.MemId = Members.MemID WHERE (CONVERT(char, DailyCheck.NightLeave, 112) = CONVERT(char, GETDATE(), 112)) ORDER BY DailyCheck.NightLeave DESC";
}
return dataSqlDb.GetTable(str);
}
catch { return null; }
}
/// 查询迟到的成员
public DataTable SeeLate(string dateTime,int n,char cha)
{
try
{
dataSqlDb = new DataSqlDb();
string str = "";
if (cha == 'a')
{
str = "declare @dateTime char(10), @n int; set @dateTime='" + dateTime + "'; set @n=" + n + ";SELECT Members.MemName, DailyCheck.AmRegister, DailyCheck.Remark FROM DailyCheck INNER JOIN Members ON DailyCheck.MemId = Members.MemID WHERE AmRegister<=convert(datetime,@dateTime)-(@n - 1) and AmRegister>=convert(datetime,@dateTime)-@n and AmRegister<convert(datetime,@dateTime+' 08:30:00')";
}
if (cha == 'b')
{
str = "declare @dateTime char(10), @n int; set @dateTime='" + dateTime + "'; set @n=" + n + "; SELECT Members.MemName, DailyCheck.PmRegister, DailyCheck.Remark FROM DailyCheck INNER JOIN Members ON DailyCheck.MemId = Members.MemID WHERE PmRegister<=convert(datetime,@dateTime)-(@n - 1) and PmRegister>=convert(datetime,@dateTime)-@n AND PmRegister<convert(datetime,@dateTime+' 14:00:00')";
}
if (cha == 'c')
{
str = "declare @dateTime char(10), @n int; set @dateTime='" + dateTime + "'; set @n=" + n + "; SELECT Members.MemName, DailyCheck.NightRegister, DailyCheck.Remark FROM DailyCheck INNER JOIN Members ON DailyCheck.MemId = Members.MemID WHERE NightRegister<=convert(datetime,@dateTime)-(@n - 1) and NightRegister>=convert(datetime,@dateTime)-@n AND NightRegister<convert(datetime,@dateTime+' 19:00:00')";
}
return dataSqlDb.GetTable(str);
}
catch { return null; }
}
}
}
