/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Hownet.Model.AttendanceRecords model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into AttendanceRecords(");
strSql.Append("EmployeeID,DateDay,TimeOne,TimeTwo,TimeThree,TimeFour,TimeFive,TimeSix,BeLateMin,LeaveEarlyMin,DayWorkMin,IsSum,AbsenteeismMin,OvertimeMin,IsWuLianBan,IsWanLianBan,IsTongXiao,LateAtNight)");
strSql.Append(" values (");
strSql.Append("@EmployeeID,@DateDay,@TimeOne,@TimeTwo,@TimeThree,@TimeFour,@TimeFive,@TimeSix,@BeLateMin,@LeaveEarlyMin,@DayWorkMin,@IsSum,@AbsenteeismMin,@OvertimeMin,@IsWuLianBan,@IsWanLianBan,@IsTongXiao,@LateAtNight)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@EmployeeID", SqlDbType.Int, 4),
new SqlParameter("@DateDay", SqlDbType.DateTime),
new SqlParameter("@TimeOne", SqlDbType.DateTime),
new SqlParameter("@TimeTwo", SqlDbType.DateTime),
new SqlParameter("@TimeThree", SqlDbType.DateTime),
new SqlParameter("@TimeFour", SqlDbType.DateTime),
new SqlParameter("@TimeFive", SqlDbType.DateTime),
new SqlParameter("@TimeSix", SqlDbType.DateTime),
new SqlParameter("@BeLateMin", SqlDbType.Int, 4),
new SqlParameter("@LeaveEarlyMin", SqlDbType.Int, 4),
new SqlParameter("@DayWorkMin", SqlDbType.Int, 4),
new SqlParameter("@IsSum", SqlDbType.Bit, 1),
new SqlParameter("@AbsenteeismMin", SqlDbType.Int, 4),
new SqlParameter("@OvertimeMin", SqlDbType.Int, 4),
new SqlParameter("@IsWuLianBan", SqlDbType.Bit, 1),
new SqlParameter("@IsWanLianBan", SqlDbType.Bit, 1),
new SqlParameter("@IsTongXiao", SqlDbType.Bit, 1),
new SqlParameter("@LateAtNight", SqlDbType.Int, 4)
};
parameters[0].Value = model.EmployeeID;
parameters[1].Value = model.DateDay;
parameters[2].Value = model.TimeOne;
parameters[3].Value = model.TimeTwo;
parameters[4].Value = model.TimeThree;
parameters[5].Value = model.TimeFour;
parameters[6].Value = model.TimeFive;
parameters[7].Value = model.TimeSix;
parameters[8].Value = model.BeLateMin;
parameters[9].Value = model.LeaveEarlyMin;
parameters[10].Value = model.DayWorkMin;
parameters[11].Value = model.IsSum;
parameters[12].Value = model.AbsenteeismMin;
parameters[13].Value = model.OvertimeMin;
parameters[14].Value = model.IsWuLianBan;
parameters[15].Value = model.IsWanLianBan;
parameters[16].Value = model.IsTongXiao;
parameters[17].Value = model.LateAtNight;
object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
if (obj == null)
{
return(1);
}
else
{
return(Convert.ToInt32(obj));
}
}
private void CaicMinute(int ID)
{
try
{
modAR = bllAR.GetModel(ID);
int a = 0;
modAR.DayWorkMin = modAR.AbsenteeismMin = modAR.BeLateMin = modAR.OvertimeMin = modAR.LeaveEarlyMin = modAR.LateAtNight = 0;
Hownet.BLL.OtherType bllOT = new Hownet.BLL.OtherType();
bool IsCaic = Convert.ToInt32(bllOT.GetList("(Name='按实际刷卡计时')").Tables[0].Rows[0]["Value"]) == 1;
if (IsCaic)
{
if (modAR.TimeOne.HasValue && modAR.TimeTwo.HasValue)
{
TimeSpan ts = (TimeSpan)(modAR.TimeTwo - modAR.TimeOne);
a = ts.Hours * 60 + ts.Minutes;
if (a > 0)
{
modAR.DayWorkMin += a;
}
}
if (modAR.TimeThree.HasValue && modAR.TimeFour.HasValue)
{
TimeSpan ts = (TimeSpan)(modAR.TimeFour - modAR.TimeThree);
a = ts.Hours * 60 + ts.Minutes;
if (a > 0)
{
modAR.DayWorkMin += a;
}
}
if (modAR.TimeFive.HasValue && modAR.TimeSix.HasValue)
{
DateTime ddN = Convert.ToDateTime(modAR.DateDay.Date.ToShortDateString() + " " + BasicTable.dtAttRules.Select("(Name='lThree')")[0]["Value"]); //晚上加班下班时间，用于判断晚班有深夜班时间
if (!(ddN < (DateTime)(modAR.TimeSix))) //晚上下班提前或正常下班的。
{
TimeSpan ts = (TimeSpan)(modAR.TimeSix - modAR.TimeFive);
a = ts.Hours * 60 + ts.Minutes;
if (a > 0)
{
modAR.OvertimeMin = a;
}
}
else//超过夜班下班时间的。
{
TimeSpan ts = (TimeSpan)(ddN - modAR.TimeFive);
a = ts.Hours * 60 + ts.Minutes;
if (a > 0)
{
modAR.OvertimeMin = a;
}
ts = (TimeSpan)(modAR.TimeSix - ddN);
a = ts.Hours * 60 + ts.Minutes;
if (a > 0)
{
modAR.LateAtNight = a;
}
}
}
}
else
{
DateTime ddt = modAR.DateDay;
DateTime one = Convert.ToDateTime(ddt.Date.ToShortDateString() + " " + BasicTable.dtAttRules.Select("(Name='One')")[0]["Value"].ToString());
DateTime lone = Convert.ToDateTime(ddt.Date.ToShortDateString() + " " + BasicTable.dtAttRules.Select("(Name='lOne')")[0]["Value"].ToString());
DateTime two = Convert.ToDateTime(ddt.Date.ToShortDateString() + " " + BasicTable.dtAttRules.Select("(Name='Two')")[0]["Value"].ToString());
DateTime ltwo = Convert.ToDateTime(ddt.Date.ToShortDateString() + " " + BasicTable.dtAttRules.Select("(Name='lTwo')")[0]["Value"].ToString());
DateTime three = Convert.ToDateTime(ddt.Date.ToShortDateString() + " " + BasicTable.dtAttRules.Select("(Name='Three')")[0]["Value"].ToString());
DateTime lthree = Convert.ToDateTime(ddt.Date.ToShortDateString() + " " + BasicTable.dtAttRules.Select("(Name='lThree')")[0]["Value"].ToString());
if (modAR.TimeOne.HasValue && modAR.TimeTwo.HasValue)
{
if (modAR.TimeTwo < lone)
{
lone = Convert.ToDateTime(modAR.TimeTwo);
}
if (modAR.TimeOne > one)
{
one = Convert.ToDateTime(modAR.TimeOne);
}
TimeSpan ts = (TimeSpan)(lone - one);
a = ts.Hours * 60 + ts.Minutes;
if (a > 0)
{
modAR.DayWorkMin += a;
}
}
if (modAR.TimeThree.HasValue && modAR.TimeFour.HasValue)
{
if (modAR.TimeFour < ltwo)
{
ltwo = Convert.ToDateTime(modAR.TimeFour);
}
if (modAR.TimeThree > two)
{
two = Convert.ToDateTime(modAR.TimeThree);
}
TimeSpan ts = (TimeSpan)(ltwo - two);
a = ts.Hours * 60 + ts.Minutes;
if (a > 0)
{
modAR.DayWorkMin += a;
}
}
if (modAR.TimeFive.HasValue && modAR.TimeSix.HasValue)
{
if (!(lthree < (DateTime)(modAR.TimeSix))) //晚上下班提前或正常下班的。
{
if (modAR.TimeFive > three)
{
three = Convert.ToDateTime(modAR.TimeFive);
}
TimeSpan ts = (TimeSpan)(modAR.TimeSix - three);
a = ts.Hours * 60 + ts.Minutes;
if (a > 0)
{
modAR.OvertimeMin = a;
}
}
else//超过夜班下班时间的。
{
if (modAR.TimeFive > three)
{
three = Convert.ToDateTime(modAR.TimeFive);
}
TimeSpan ts = (TimeSpan)(lthree - three);
a = ts.Hours * 60 + ts.Minutes;
if (a > 0)
{
modAR.OvertimeMin = a;
}
ts = (TimeSpan)(modAR.TimeSix - lthree);
a = ts.Hours * 60 + ts.Minutes;
if (a > 0)
{
modAR.LateAtNight = a;
}
}
}
}
}
catch (Exception ex)
{
}
bllAR.Update(modAR);
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Hownet.Model.AttendanceRecords GetModel(int ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 ID,EmployeeID,DateDay,TimeOne,TimeTwo,TimeThree,TimeFour,TimeFive,TimeSix,BeLateMin,LeaveEarlyMin,DayWorkMin,IsSum,AbsenteeismMin,OvertimeMin,IsWuLianBan,IsWanLianBan,IsTongXiao,LateAtNight from AttendanceRecords ");
strSql.Append(" where ID=@ID ");
SqlParameter[] parameters =
{
new SqlParameter("@ID", SqlDbType.Int, 4)
};
parameters[0].Value = ID;
Hownet.Model.AttendanceRecords model = new Hownet.Model.AttendanceRecords();
DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
{
model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
}
if (ds.Tables[0].Rows[0]["EmployeeID"].ToString() != "")
{
model.EmployeeID = int.Parse(ds.Tables[0].Rows[0]["EmployeeID"].ToString());
}
if (ds.Tables[0].Rows[0]["DateDay"].ToString() != "")
{
model.DateDay = DateTime.Parse(ds.Tables[0].Rows[0]["DateDay"].ToString());
}
if (ds.Tables[0].Rows[0]["TimeOne"].ToString() != "")
{
model.TimeOne = DateTime.Parse(ds.Tables[0].Rows[0]["TimeOne"].ToString());
}
if (ds.Tables[0].Rows[0]["TimeTwo"].ToString() != "")
{
model.TimeTwo = DateTime.Parse(ds.Tables[0].Rows[0]["TimeTwo"].ToString());
}
if (ds.Tables[0].Rows[0]["TimeThree"].ToString() != "")
{
model.TimeThree = DateTime.Parse(ds.Tables[0].Rows[0]["TimeThree"].ToString());
}
if (ds.Tables[0].Rows[0]["TimeFour"].ToString() != "")
{
model.TimeFour = DateTime.Parse(ds.Tables[0].Rows[0]["TimeFour"].ToString());
}
if (ds.Tables[0].Rows[0]["TimeFive"].ToString() != "")
{
model.TimeFive = DateTime.Parse(ds.Tables[0].Rows[0]["TimeFive"].ToString());
}
if (ds.Tables[0].Rows[0]["TimeSix"].ToString() != "")
{
model.TimeSix = DateTime.Parse(ds.Tables[0].Rows[0]["TimeSix"].ToString());
}
if (ds.Tables[0].Rows[0]["BeLateMin"].ToString() != "")
{
model.BeLateMin = int.Parse(ds.Tables[0].Rows[0]["BeLateMin"].ToString());
}
if (ds.Tables[0].Rows[0]["LeaveEarlyMin"].ToString() != "")
{
model.LeaveEarlyMin = int.Parse(ds.Tables[0].Rows[0]["LeaveEarlyMin"].ToString());
}
if (ds.Tables[0].Rows[0]["DayWorkMin"].ToString() != "")
{
model.DayWorkMin = int.Parse(ds.Tables[0].Rows[0]["DayWorkMin"].ToString());
}
if (ds.Tables[0].Rows[0]["IsSum"].ToString() != "")
{
if ((ds.Tables[0].Rows[0]["IsSum"].ToString() == "1") || (ds.Tables[0].Rows[0]["IsSum"].ToString().ToLower() == "true"))
{
model.IsSum = true;
}
else
{
model.IsSum = false;
}
}
if (ds.Tables[0].Rows[0]["AbsenteeismMin"].ToString() != "")
{
model.AbsenteeismMin = int.Parse(ds.Tables[0].Rows[0]["AbsenteeismMin"].ToString());
}
if (ds.Tables[0].Rows[0]["OvertimeMin"].ToString() != "")
{
model.OvertimeMin = int.Parse(ds.Tables[0].Rows[0]["OvertimeMin"].ToString());
}
if (ds.Tables[0].Rows[0]["IsWuLianBan"].ToString() != "")
{
if ((ds.Tables[0].Rows[0]["IsWuLianBan"].ToString() == "1") || (ds.Tables[0].Rows[0]["IsWuLianBan"].ToString().ToLower() == "true"))
{
model.IsWuLianBan = true;
}
else
{
model.IsWuLianBan = false;
}
}
if (ds.Tables[0].Rows[0]["IsWanLianBan"].ToString() != "")
{
if ((ds.Tables[0].Rows[0]["IsWanLianBan"].ToString() == "1") || (ds.Tables[0].Rows[0]["IsWanLianBan"].ToString().ToLower() == "true"))
{
model.IsWanLianBan = true;
}
else
{
model.IsWanLianBan = false;
}
}
if (ds.Tables[0].Rows[0]["IsTongXiao"].ToString() != "")
{
if ((ds.Tables[0].Rows[0]["IsTongXiao"].ToString() == "1") || (ds.Tables[0].Rows[0]["IsTongXiao"].ToString().ToLower() == "true"))
{
model.IsTongXiao = true;
}
else
{
model.IsTongXiao = false;
}
}
if (ds.Tables[0].Rows[0]["LateAtNight"].ToString() != "")
{
model.LateAtNight = int.Parse(ds.Tables[0].Rows[0]["LateAtNight"].ToString());
}
model.A = 1;
return(model);
}
else
{
return(null);
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public void Update(Hownet.Model.AttendanceRecords model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update AttendanceRecords set ");
strSql.Append("EmployeeID=@EmployeeID,");
strSql.Append("DateDay=@DateDay,");
strSql.Append("TimeOne=@TimeOne,");
strSql.Append("TimeTwo=@TimeTwo,");
strSql.Append("TimeThree=@TimeThree,");
strSql.Append("TimeFour=@TimeFour,");
strSql.Append("TimeFive=@TimeFive,");
strSql.Append("TimeSix=@TimeSix,");
strSql.Append("BeLateMin=@BeLateMin,");
strSql.Append("LeaveEarlyMin=@LeaveEarlyMin,");
strSql.Append("DayWorkMin=@DayWorkMin,");
strSql.Append("IsSum=@IsSum,");
strSql.Append("AbsenteeismMin=@AbsenteeismMin,");
strSql.Append("OvertimeMin=@OvertimeMin,");
strSql.Append("IsWuLianBan=@IsWuLianBan,");
strSql.Append("IsWanLianBan=@IsWanLianBan,");
strSql.Append("IsTongXiao=@IsTongXiao,");
strSql.Append("LateAtNight=@LateAtNight");
strSql.Append(" where ID=@ID ");
SqlParameter[] parameters =
{
new SqlParameter("@ID", SqlDbType.Int, 4),
new SqlParameter("@EmployeeID", SqlDbType.Int, 4),
new SqlParameter("@DateDay", SqlDbType.DateTime),
new SqlParameter("@TimeOne", SqlDbType.DateTime),
new SqlParameter("@TimeTwo", SqlDbType.DateTime),
new SqlParameter("@TimeThree", SqlDbType.DateTime),
new SqlParameter("@TimeFour", SqlDbType.DateTime),
new SqlParameter("@TimeFive", SqlDbType.DateTime),
new SqlParameter("@TimeSix", SqlDbType.DateTime),
new SqlParameter("@BeLateMin", SqlDbType.Int, 4),
new SqlParameter("@LeaveEarlyMin", SqlDbType.Int, 4),
new SqlParameter("@DayWorkMin", SqlDbType.Int, 4),
new SqlParameter("@IsSum", SqlDbType.Bit, 1),
new SqlParameter("@AbsenteeismMin", SqlDbType.Int, 4),
new SqlParameter("@OvertimeMin", SqlDbType.Int, 4),
new SqlParameter("@IsWuLianBan", SqlDbType.Bit, 1),
new SqlParameter("@IsWanLianBan", SqlDbType.Bit, 1),
new SqlParameter("@IsTongXiao", SqlDbType.Bit, 1),
new SqlParameter("@LateAtNight", SqlDbType.Int, 4)
};
parameters[0].Value = model.ID;
parameters[1].Value = model.EmployeeID;
parameters[2].Value = model.DateDay;
parameters[3].Value = model.TimeOne;
parameters[4].Value = model.TimeTwo;
parameters[5].Value = model.TimeThree;
parameters[6].Value = model.TimeFour;
parameters[7].Value = model.TimeFive;
parameters[8].Value = model.TimeSix;
parameters[9].Value = model.BeLateMin;
parameters[10].Value = model.LeaveEarlyMin;
parameters[11].Value = model.DayWorkMin;
parameters[12].Value = model.IsSum;
parameters[13].Value = model.AbsenteeismMin;
parameters[14].Value = model.OvertimeMin;
parameters[15].Value = model.IsWuLianBan;
parameters[16].Value = model.IsWanLianBan;
parameters[17].Value = model.IsTongXiao;
parameters[18].Value = model.LateAtNight;
DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
}
