/// <summary>
/// 生成考勤数据
/// </summary>
/// <param name="zd">站点</param>
/// <param name="time1">起始日期</param>
/// <param name="time2">结束日期</param>
/// <param name="FileID">文件ID</param>
private void KQ_Export(int zd, string names, DateTime time1, DateTime time2, string FileID)
{
string SQL = @"SELECT [日期],[姓名]
,isnull(convert(varchar(20),[上班签到],120),'X') as [上班签到]
,isnull(convert(varchar(20),[下班签退],120),'X') as [下班签退]
,datediff(d,'@起始时间@',日期) as 日
,[出勤情况]
FROM [CNGTZ].[dbo].[TZ_人员_考勤表]
where 姓名 ='@姓名@' AND 日期 BETWEEN '@起始时间@' AND '@结束时间@' order by 日期";
SQL = SQL.Replace("@起始时间@", time1.ToShortDateString());
SQL = SQL.Replace("@结束时间@", time2.ToShortDateString());
TimeSpan sp = time2 - time1;//相隔天数
int days = sp.Days + 1;
string nowFile = HttpContext.Current.Server.MapPath("~/Public/ExcelTemplate/DownTemp/替换文本.xls");
nowFile = nowFile.Replace("替换文本", FileID);
MyExcel ME = new MyExcel();
ME.Open(nowFile);//打开副本文件
for (int i = 0; i < 31; i++)
{//插入 抬头 日期行数据
if (i >= days)
{
ME.HideCol(3 + i, true);
continue;
}
DateTime o = time1.AddDays(i);
ME.WriteStr(o.ToString("dd"), 3, 3 + i);
}
ME.WriteStr("起始时间:" + time1.ToShortDateString() + " 至 " + time2.ToShortDateString() + " ", 2, 1);
DAL dal = new DAL();
string[] name = names.Split(',');//生成姓名数组
for (int i = 0; i < name.Length - 1; i++)
{//插入姓名行数
ME.InsertRow(6);
ME.InsertRow(6);
ME.CellsCopy("A4:AG5", "A6");
}
for (int i = 0; i < name.Length; i++)
{//生成每人考勤数据
ME.WriteStr(name[i], 4 + i * 2, 1);
string zxSQL = SQL.Replace("@姓名@", name[i]);
Sys.DebugMes(zxSQL);
DataTable dt = dal.RunSqlDataTable(zxSQL);
if (dt == null)
{
continue;
}
foreach (DataRow row in dt.Rows)
{
int n = int.Parse(row["日"].ToString());
string x1 = row["上班签到"].ToString();
string x2 = row["下班签退"].ToString();
string cq = row["出勤情况"].ToString();
if (zd == 2 || zd == 3)
{//西路 和 南高 签到对应上班, 签退对应下班
if (cq != "异常" && cq != "正常")
{
System.Drawing.Color mycolor = System.Drawing.Color.Red;
switch (cq)
{
case "事假":
case "病假":
case "其他":
case "工伤":
mycolor = System.Drawing.Color.Red;
break;
case "婚假":
case "年休":
mycolor =Color.Green;
break;
}
ME.WriteStr(cq.Substring(0, 1), 4 + i * 2, 3 + n);
ME.WriteStr(cq.Substring(0, 1), 5 + i * 2, 3 + n);
ME.SetFont(mycolor, 4 + i * 2, 3 + n);
ME.SetFont(mycolor, 5 + i * 2, 3 + n);
}
if (x1 != "X")
{
ME.WriteStr("○", 4 + i * 2, 3 + n);
ME.SetFont(System.Drawing.Color.Black, 4 + i * 2, 3 + n);
}
if (x2 != "X")
{
ME.WriteStr("○", 5 + i * 2, 3 + n);
ME.SetFont(System.Drawing.Color.Black, 5 + i * 2, 3 + n);
}
}
if (zd == 4)
{//麻柳沱 签到对应第一天,签退对应第二天
if (x1 != "X")
{
ME.WriteStr("○", 4 + i * 2, 3 + n);
ME.WriteStr("○", 5 + i * 2, 3 + n);
}
if (x2 != "X")
{
ME.WriteStr("○", 4 + i * 2, 4 + n);
ME.WriteStr("○", 5 + i * 2, 4 + n);
}
}
}
}
ME.Save();
ME.Close();
}