/// <summary>
/// 得到一个对象实体
/// </summary>
public Maticsoft.Model.SMT_ATTEN_REPORT DataRowToModel(DataRow row)
{
Maticsoft.Model.SMT_ATTEN_REPORT model = new Maticsoft.Model.SMT_ATTEN_REPORT();
if (row != null)
{
if (row["ID"] != null && row["ID"].ToString() != "")
{
model.ID = decimal.Parse(row["ID"].ToString());
}
if (row["STAFF_ID"] != null && row["STAFF_ID"].ToString() != "")
{
model.STAFF_ID = decimal.Parse(row["STAFF_ID"].ToString());
}
if (row["ATTEN_DATE"] != null && row["ATTEN_DATE"].ToString() != "")
{
model.ATTEN_DATE = DateTime.Parse(row["ATTEN_DATE"].ToString());
}
if (row["ATTEN_ON_TIME"] != null && row["ATTEN_ON_TIME"].ToString() != "")
{
model.ATTEN_ON_TIME = TimeSpan.Parse(row["ATTEN_ON_TIME"].ToString());
}
if (row["ATTEN_OFF_TIME"] != null && row["ATTEN_OFF_TIME"].ToString() != "")
{
model.ATTEN_OFF_TIME = TimeSpan.Parse(row["ATTEN_OFF_TIME"].ToString());
}
if (row["ATTEN_ON_STATE"] != null)
{
model.ATTEN_ON_STATE = row["ATTEN_ON_STATE"].ToString();
}
if (row["ATTEN_OFF_STATE"] != null)
{
model.ATTEN_OFF_STATE = row["ATTEN_OFF_STATE"].ToString();
}
if (row["ATTEN_LATE_MIN"] != null && row["ATTEN_LATE_MIN"].ToString() != "")
{
model.ATTEN_LATE_MIN = int.Parse(row["ATTEN_LATE_MIN"].ToString());
}
if (row["ATTEN_LEAVE_MIN"] != null && row["ATTEN_LEAVE_MIN"].ToString() != "")
{
model.ATTEN_LEAVE_MIN = int.Parse(row["ATTEN_LEAVE_MIN"].ToString());
}
if (row["ATTEN_EXTRA_MIN"] != null && row["ATTEN_EXTRA_MIN"].ToString() != "")
{
model.ATTEN_EXTRA_MIN = int.Parse(row["ATTEN_EXTRA_MIN"].ToString());
}
if (row["ATTEN_AWAY_DAY"] != null && row["ATTEN_AWAY_DAY"].ToString() != "")
{
model.ATTEN_AWAY_DAY = decimal.Parse(row["ATTEN_AWAY_DAY"].ToString());
}
if (row["ATTEN_UNSWIPE_TIMES"] != null && row["ATTEN_UNSWIPE_TIMES"].ToString() != "")
{
model.ATTEN_UNSWIPE_TIMES = int.Parse(row["ATTEN_UNSWIPE_TIMES"].ToString());
}
if (row["ATTEN_SWIPE_TIMES"] != null && row["ATTEN_SWIPE_TIMES"].ToString() != "")
{
model.ATTEN_SWIPE_TIMES = int.Parse(row["ATTEN_SWIPE_TIMES"].ToString());
}
}
return(model);
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Maticsoft.Model.SMT_ATTEN_REPORT model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update SMT_ATTEN_REPORT set ");
strSql.Append("STAFF_ID=@STAFF_ID,");
strSql.Append("ATTEN_DATE=@ATTEN_DATE,");
strSql.Append("ATTEN_ON_TIME=@ATTEN_ON_TIME,");
strSql.Append("ATTEN_OFF_TIME=@ATTEN_OFF_TIME,");
strSql.Append("ATTEN_ON_STATE=@ATTEN_ON_STATE,");
strSql.Append("ATTEN_OFF_STATE=@ATTEN_OFF_STATE,");
strSql.Append("ATTEN_LATE_MIN=@ATTEN_LATE_MIN,");
strSql.Append("ATTEN_LEAVE_MIN=@ATTEN_LEAVE_MIN,");
strSql.Append("ATTEN_EXTRA_MIN=@ATTEN_EXTRA_MIN,");
strSql.Append("ATTEN_AWAY_DAY=@ATTEN_AWAY_DAY,");
strSql.Append("ATTEN_UNSWIPE_TIMES=@ATTEN_UNSWIPE_TIMES,");
strSql.Append("ATTEN_SWIPE_TIMES=@ATTEN_SWIPE_TIMES");
strSql.Append(" where ID=@ID");
SqlParameter[] parameters =
{
new SqlParameter("@STAFF_ID", SqlDbType.Decimal, 9),
new SqlParameter("@ATTEN_DATE", SqlDbType.Date, 3),
new SqlParameter("@ATTEN_ON_TIME", SqlDbType.Time, 5),
new SqlParameter("@ATTEN_OFF_TIME", SqlDbType.Time, 5),
new SqlParameter("@ATTEN_ON_STATE", SqlDbType.NVarChar, 20),
new SqlParameter("@ATTEN_OFF_STATE", SqlDbType.NVarChar, 20),
new SqlParameter("@ATTEN_LATE_MIN", SqlDbType.Int, 4),
new SqlParameter("@ATTEN_LEAVE_MIN", SqlDbType.Int, 4),
new SqlParameter("@ATTEN_EXTRA_MIN", SqlDbType.Int, 4),
new SqlParameter("@ATTEN_AWAY_DAY", SqlDbType.Float, 8),
new SqlParameter("@ATTEN_UNSWIPE_TIMES", SqlDbType.Int, 4),
new SqlParameter("@ATTEN_SWIPE_TIMES", SqlDbType.Int, 4),
new SqlParameter("@ID", SqlDbType.Decimal, 9)
};
parameters[0].Value = model.STAFF_ID;
parameters[1].Value = model.ATTEN_DATE;
parameters[2].Value = model.ATTEN_ON_TIME;
parameters[3].Value = model.ATTEN_OFF_TIME;
parameters[4].Value = model.ATTEN_ON_STATE;
parameters[5].Value = model.ATTEN_OFF_STATE;
parameters[6].Value = model.ATTEN_LATE_MIN;
parameters[7].Value = model.ATTEN_LEAVE_MIN;
parameters[8].Value = model.ATTEN_EXTRA_MIN;
parameters[9].Value = model.ATTEN_AWAY_DAY;
parameters[10].Value = model.ATTEN_UNSWIPE_TIMES;
parameters[11].Value = model.ATTEN_SWIPE_TIMES;
parameters[12].Value = model.ID;
int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public decimal Add(Maticsoft.Model.SMT_ATTEN_REPORT model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into SMT_ATTEN_REPORT(");
strSql.Append("STAFF_ID,ATTEN_DATE,ATTEN_ON_TIME,ATTEN_OFF_TIME,ATTEN_ON_STATE,ATTEN_OFF_STATE,ATTEN_LATE_MIN,ATTEN_LEAVE_MIN,ATTEN_EXTRA_MIN,ATTEN_AWAY_DAY,ATTEN_UNSWIPE_TIMES,ATTEN_SWIPE_TIMES)");
strSql.Append(" values (");
strSql.Append("@STAFF_ID,@ATTEN_DATE,@ATTEN_ON_TIME,@ATTEN_OFF_TIME,@ATTEN_ON_STATE,@ATTEN_OFF_STATE,@ATTEN_LATE_MIN,@ATTEN_LEAVE_MIN,@ATTEN_EXTRA_MIN,@ATTEN_AWAY_DAY,@ATTEN_UNSWIPE_TIMES,@ATTEN_SWIPE_TIMES)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@STAFF_ID", SqlDbType.Decimal, 9),
new SqlParameter("@ATTEN_DATE", SqlDbType.Date, 3),
new SqlParameter("@ATTEN_ON_TIME", SqlDbType.Time, 5),
new SqlParameter("@ATTEN_OFF_TIME", SqlDbType.Time, 5),
new SqlParameter("@ATTEN_ON_STATE", SqlDbType.NVarChar, 20),
new SqlParameter("@ATTEN_OFF_STATE", SqlDbType.NVarChar, 20),
new SqlParameter("@ATTEN_LATE_MIN", SqlDbType.Int, 4),
new SqlParameter("@ATTEN_LEAVE_MIN", SqlDbType.Int, 4),
new SqlParameter("@ATTEN_EXTRA_MIN", SqlDbType.Int, 4),
new SqlParameter("@ATTEN_AWAY_DAY", SqlDbType.Float, 8),
new SqlParameter("@ATTEN_UNSWIPE_TIMES", SqlDbType.Int, 4),
new SqlParameter("@ATTEN_SWIPE_TIMES", SqlDbType.Int, 4)
};
parameters[0].Value = model.STAFF_ID;
parameters[1].Value = model.ATTEN_DATE;
parameters[2].Value = model.ATTEN_ON_TIME;
parameters[3].Value = model.ATTEN_OFF_TIME;
parameters[4].Value = model.ATTEN_ON_STATE;
parameters[5].Value = model.ATTEN_OFF_STATE;
parameters[6].Value = model.ATTEN_LATE_MIN;
parameters[7].Value = model.ATTEN_LEAVE_MIN;
parameters[8].Value = model.ATTEN_EXTRA_MIN;
parameters[9].Value = model.ATTEN_AWAY_DAY;
parameters[10].Value = model.ATTEN_UNSWIPE_TIMES;
parameters[11].Value = model.ATTEN_SWIPE_TIMES;
object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToDecimal(obj));
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Maticsoft.Model.SMT_ATTEN_REPORT GetModel(decimal ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 ID,STAFF_ID,ATTEN_DATE,ATTEN_ON_TIME,ATTEN_OFF_TIME,ATTEN_ON_STATE,ATTEN_OFF_STATE,ATTEN_LATE_MIN,ATTEN_LEAVE_MIN,ATTEN_EXTRA_MIN,ATTEN_AWAY_DAY,ATTEN_UNSWIPE_TIMES,ATTEN_SWIPE_TIMES from SMT_ATTEN_REPORT ");
strSql.Append(" where ID=@ID");
SqlParameter[] parameters =
{
new SqlParameter("@ID", SqlDbType.Decimal)
};
parameters[0].Value = ID;
Maticsoft.Model.SMT_ATTEN_REPORT model = new Maticsoft.Model.SMT_ATTEN_REPORT();
DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
private void StartCreateReport()
{
DateTime dtStart = dtpStart.Value.Date;
DateTime dtEnd = dtpEnd.Value.Date;
CtrlWaiting waiting = new CtrlWaiting("正在生成...", () =>
{
try
{
Maticsoft.BLL.SMT_ATTEN_SETTING settingBll = new Maticsoft.BLL.SMT_ATTEN_SETTING();
var settings = settingBll.GetModelList("DUTY_TYPE=0");//查找正常班
if (settings.Count == 0)
{
WinInfoHelper.ShowInfoWindow(this, "生成报表中断：无正常班设置，请设置正常班！");
return;
}
DateTime dt = dtStart;
Maticsoft.Model.SMT_ATTEN_SETTING setting = settings[0];
Maticsoft.BLL.SMT_ATTEN_REPORT reportBll = new Maticsoft.BLL.SMT_ATTEN_REPORT();
while (dt <= dtEnd)
{
bool isweekend = false;
int weekendType = 0;
if (dt.DayOfWeek == DayOfWeek.Saturday)
{
isweekend = true;
weekendType = setting.DUTY_SAT_TYPE;
}
else if (dt.DayOfWeek == DayOfWeek.Sunday)
{
isweekend = true;
weekendType = setting.DUTY_SUN_TYPE;
}
if (isweekend && weekendType == 1)//全天上班
{
isweekend = false;
}
else if (isweekend && weekendType == 0)//全天休息
{
Maticsoft.DBUtility.DbHelperSQL.ExecuteSql("delete from SMT_ATTEN_REPORT where ATTEN_DATE='" + dt.ToString("yyyy-MM-dd") + "'");
goto End;//直接结束
}
DateTime earliest = dt.Date + setting.DUTY_ON_EARLIEST;
DateTime ontime1 = dt.Date + setting.DUTY_ON_TIME1;
DateTime offtime1 = dt.Date + setting.DUTY_OFF_TIME1;
string sql = string.Format("select t.RECORD_DATE,t.STAFF_ID from SMT_CARD_RECORDS t where t.RECORD_REASON='Swipe' and t.STAFF_ID>=0 and t.RECORD_DATE>='{0} 00:00:00' and t.RECORD_DATE<'{1} 00:00:00' and t.STAFF_ID in (select ssi.ID from SMT_STAFF_INFO ssi where ssi.IS_DELETE=0)", dt.ToString("yyyy-MM-dd"), dt.AddDays(1).ToString("yyyy-MM-dd"));
DataTable dateTable = Maticsoft.DBUtility.DbHelperSQL.Query(sql).Tables[0];
List <Record> records = new List <Record>();
foreach (DataRow item in dateTable.Rows)
{
Record record = new Record();
record.StaffId = (decimal)item[1];
record.RecordTime = (DateTime)item[0];
records.Add(record);
}
var g = records.GroupBy(m => m.StaffId);
List <decimal> staffIds = new List <decimal>();
foreach (var list in g)
{
List <Record> l = list.ToList();//获取一个职员当天的刷卡记录
staffIds.Add(l[0].StaffId);
Maticsoft.Model.SMT_ATTEN_REPORT report = new Maticsoft.Model.SMT_ATTEN_REPORT();
report.ATTEN_DATE = dt.Date;
report.STAFF_ID = l[0].StaffId;
report.ATTEN_SWIPE_TIMES = setting.DUTY_SWING_TIMES;
#region 班计算
List <Record> onRecords = l.FindAll(m => m.RecordTime >= earliest && m.RecordTime <= ontime1.AddMinutes(setting.DUTY_LATE_MAX));
if (onRecords.Count == 0)//上班未打卡，算旷工
{
report.ATTEN_ON_STATE = "旷工(上班)";
report.ATTEN_AWAY_DAY = setting.DUTY_LATE_PUNISH;
report.ATTEN_UNSWIPE_TIMES = 1;
}
else
{
DateTime minDate = onRecords.Min(m => m.RecordTime);
report.ATTEN_ON_TIME = minDate.TimeOfDay;
if (minDate <= ontime1.AddMinutes(setting.DUTY_LATE_MIN))//正常上班
{
report.ATTEN_ON_STATE = "正常";
}
else /* if (minDate<=ontime1.AddMinutes(setting.DUTY_LATE_MAX))*///迟到
{
report.ATTEN_ON_STATE = "迟到";
report.ATTEN_LATE_MIN = (int)(minDate - ontime1).TotalMinutes;
}
}
#endregion
#region 班计算
if (!setting.DUTY_ONLY_ON || !isweekend)
{
onRecords = l.FindAll(m => m.RecordTime >= offtime1.AddMinutes(-setting.DUTY_LEAVE_MAX));
if (onRecords.Count == 0)//下班未打卡，算旷工
{
report.ATTEN_OFF_STATE = "旷工(下班)";
if (report.ATTEN_AWAY_DAY == null)
{
report.ATTEN_AWAY_DAY = 0;
}
report.ATTEN_AWAY_DAY += setting.DUTY_LATE_PUNISH;
if (report.ATTEN_UNSWIPE_TIMES == null)
{
report.ATTEN_UNSWIPE_TIMES = 0;
}
report.ATTEN_UNSWIPE_TIMES += 1;
}
else
{
DateTime maxDate = onRecords.Max(m => m.RecordTime);
report.ATTEN_OFF_TIME = maxDate.TimeOfDay;
if (maxDate < offtime1.AddMinutes(-setting.DUTY_LEAVE_MIN))//早退
{
report.ATTEN_OFF_STATE = "早退";
report.ATTEN_LEAVE_MIN = (int)(offtime1 - maxDate).TotalMinutes;
}
else if (maxDate < offtime1.AddMinutes(setting.DUTY_EXTRA_MIN))
{
report.ATTEN_OFF_STATE = "正常";
}
else
{
report.ATTEN_OFF_STATE = "加班";
report.ATTEN_EXTRA_MIN = (int)(maxDate - offtime1).TotalMinutes;
}
}
}
#endregion
var exist = reportBll.GetModelList("ATTEN_DATE='" + dt.Date.ToString("yyyy-MM-dd 00:00:00") + "' and STAFF_ID=" + report.STAFF_ID);
if (exist.Count > 0)
{
report.ID = exist[0].ID;
reportBll.Update(report);
}
else
{
reportBll.Add(report);
}
}
sql = "select ID from SMT_STAFF_INFO where IS_DELETE=0 ";
if (staffIds.Count > 0)
{
sql += " and Id not in (" + string.Join(",", staffIds.ToArray()) + ")";
}
DataTable staffTables = Maticsoft.DBUtility.DbHelperSQL.Query(sql).Tables[0];
foreach (DataRow sid in staffTables.Rows)
{
Maticsoft.Model.SMT_ATTEN_REPORT report = new Maticsoft.Model.SMT_ATTEN_REPORT();
report.ATTEN_DATE = dt.Date;
report.STAFF_ID = (decimal)sid[0];
report.ATTEN_SWIPE_TIMES = setting.DUTY_SWING_TIMES;
report.ATTEN_ON_STATE = "旷工";
if (isweekend)
{
report.ATTEN_AWAY_DAY = 0.5M;
}
else
{
report.ATTEN_AWAY_DAY = 1M;
report.ATTEN_OFF_STATE = "旷工";
}
report.ATTEN_UNSWIPE_TIMES = setting.DUTY_SWING_TIMES;
var exist = reportBll.GetModelList("ATTEN_DATE='" + dt.Date.ToString("yyyy-MM-dd 00:00:00") + "' and STAFF_ID=" + report.STAFF_ID);
if (exist.Count > 0)
{
report.ID = exist[0].ID;
reportBll.Update(report);
}
else
{
reportBll.Add(report);
}
}
End:
dt = dt.AddDays(1);
}
WinInfoHelper.ShowInfoWindow(this, "生成报表结束！");
}
catch (Exception ex)
{
WinInfoHelper.ShowInfoWindow(this, "生成报表异常：" + ex.Message);
log.Error("生成报表异常:" + ex.Message, ex);
}
});
waiting.Show(this);
}