public ActionResult holidaylogcal(int?page, string orderdata, string orderdata1)
{
string qdptid = "", qempname = "", qclogsdate = "", qclogedate = "", qclogstatus = "", qempid = "";
if (!string.IsNullOrWhiteSpace(Request["qdptid"]))
{
qdptid = Request["qdptid"].Trim();
}
if (!string.IsNullOrWhiteSpace(Request["qempname"]))
{
qempname = Request["qempname"].Trim();
}
if (!string.IsNullOrWhiteSpace(Request["qclogstatus"]))
{
qclogstatus = Request["qclogstatus"].Trim();
}
if (!string.IsNullOrWhiteSpace(Request["qempid"]))
{
qempid = Request["qempid"].Trim();
}
if (!string.IsNullOrWhiteSpace(Request["qtheday"]))
{
DateTime qtheday = Convert.ToDateTime(Request["qtheday"]);
DateTime qendday = qtheday.AddMonths(1).AddDays(-1);
qclogsdate = qtheday.ToString("yyyy/MM/dd");
qclogedate = qendday.ToString("yyyy/MM/dd");
}
else
{
qclogsdate = NullStDate(Request["qclogsdate"]);
qclogedate = NullTeDate(Request["qclogedate"]);
}
//NullStDate 跟 NullTeDate 會判斷格式，有錯誤就 修改全域的DateEx
if (DateEx != "")
{
ViewBag.DateEx = @"<script>alert(""" + DateEx + @""");</script>";
}
string sqlstr = "";
int jstart = 0;
int countday = 0;
Aitag.Models.NDcommon dbobj = new Aitag.Models.NDcommon();
countday = Convert.ToDateTime(qclogedate).Subtract(Convert.ToDateTime(qclogsdate)).Days;
dbobj.dbexecute("Aitag_DBContext", "delete cardjudgelog where clogdate>='" + qclogsdate + "' and clogdate<='" + qclogedate + "' and comid = '" + Session["comid"] + "'");
SqlConnection comconn1 = dbobj.get_conn("Aitag_DBContext");
for (int j = jstart; j <= countday; j++)
{
DateTime tmpdate = Convert.ToDateTime(qclogsdate).AddDays(j);
using (SqlConnection conn = dbobj.get_conn("Aitag_DBContext"))
{
string sql = "select * from employee where empstatus<>'4' and empworkcomp='" + (string)Session["comid"] + "'";
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
cardjudgelog rscard = new cardjudgelog();
string clogstime = "000000", clogetime = "000000", clogcomment = "";
string clogstatus = "";
using (SqlConnection conn1 = dbobj.get_conn("Aitag_DBContext"))
{
int count1 = 0;
count1 = int.Parse(dbobj.get_dbvalue(conn1, "select isnull(count(*),0) as count1 from cardreallog where empid='" + dr["empid"] + "' and clogdate ='" + dbobj.get_date(tmpdate.ToString(), "1") + "'"));
sql = "select clogtime from cardreallog where empid='" + dr["empid"] + "' and clogdate ='" + dbobj.get_date(tmpdate.ToString(), "1") + "' order by clogtime";
using (SqlCommand cmd1 = new SqlCommand(sql, conn1))
{
SqlDataReader dr1 = cmd1.ExecuteReader();
if (dr1.HasRows)
{
if (count1 < 2)//'cardreallog 判斷小於每天兩次打卡時間
{
if (dr1.Read())
{
clogstime = dr1["clogtime"].ToString();
}
else
{
clogstime = "000000";
}
}
else
{
int k = 0;
while (dr1.Read())
{
k++;
if (k == 1)
{
clogstime = dbobj.get_dbvalue(comconn1, "select top(1) clogtime from cardreallog where empid='" + dr["empid"].ToString() + "' and clogdate ='" + dbobj.get_date(tmpdate.ToString(), "1") + "' order by clogtime");
}
if (k == 2)
{
clogetime = dbobj.get_dbvalue(comconn1, "select top(1) clogtime from cardreallog where empid='" + dr["empid"].ToString() + "' and clogdate ='" + dbobj.get_date(tmpdate.ToString(), "1") + "' order by clogtime desc");
}
}
}
}
else
{
clogstime = "000000";
clogetime = "000000";
}
dr1.Close();
#region 整理rscard
rscard.empid = dr["empid"].ToString();
rscard.empname = dr["empname"].ToString();
rscard.comid = dr["empworkcomp"].ToString();
rscard.dptid = dr["empworkdepid"].ToString();
rscard.clogdate = tmpdate;
if (clogstime == "000000")
{
rscard.clogstime = "";
}
else
{
rscard.clogstime = clogstime;
}
if (clogetime == "000000")
{
rscard.clogetime = "";
}
else
{
rscard.clogetime = clogetime;
}
if (clogstime.Length != 6)
{
clogstime = "000000";
}
if (clogetime.Length != 6)
{
clogstime = "000000";
}
string clogetime1 = "", clogstime1 = "";
if (clogstime != "" || clogetime != "")
{
clogetime1 = (int.Parse(clogetime.Substring(0, 2)) * 60 + int.Parse(clogetime.Substring(2, 2))).ToString("000000");
clogstime1 = (int.Parse(clogstime.Substring(0, 2)) * 60 + int.Parse(clogstime.Substring(2, 2))).ToString("000000");
rscard.cloghour = Math.Round((decimal.Parse(clogetime1) - decimal.Parse(clogstime1)) / 60, 1);
}
else
{
rscard.cloghour = 0;
}
string hlogstatus = "", hloghour = "", hdayid = "";
dr1 = dbobj.dbselect(comconn1, "select * from holidaylog where empid='" + dr["empid"] + "' and comid='" + dr["empworkcomp"] + "' and hlogstatus='1' and hlogsdate<='" + dbobj.get_date(tmpdate.ToString(), "1") + "' and hlogedate>='" + dbobj.get_date(tmpdate.ToString(), "1") + "'");
if (dr1.Read())
{
hlogstatus = dr1["hlogstatus"].ToString();
hloghour = dr1["hloghour"].ToString();
hdayid = dr1["hdayid"].ToString();
}
dr1.Close();
//string hlogstatus = dbobj.get_dbvalue(comconn1, "select hlogstatus from holidaylog where empid='" + dr["empid"] + "' and comid='" + dr["empworkcomp"] + "' and hlogstatus='1' and hlogsdate<='" + dbobj.get_date(tmpdate.ToString(), "1") + "' and hlogedate>='" + dbobj.get_date(tmpdate.ToString(), "1") + "'");
//string hloghour = dbobj.get_dbvalue(comconn1, "select hloghour from holidaylog where empid='" + dr["empid"] + "' and comid='" + dr["empworkcomp"] + "' and hlogstatus='1' and hlogsdate<='" + dbobj.get_date(tmpdate.ToString(), "1") + "' and hlogedate>='" + dbobj.get_date(tmpdate.ToString(), "1") + "'");
//string hdayid = dbobj.get_dbvalue(comconn1, "select hdayid from holidaylog where empid='" + dr["empid"] + "' and comid='" + dr["empworkcomp"] + "' and hlogstatus='1' and hlogsdate<='" + dbobj.get_date(tmpdate.ToString(), "1") + "' and hlogedate>='" + dbobj.get_date(tmpdate.ToString(), "1") + "'");
clogcomment = dbobj.get_dbvalue(comconn1, "select hdaytitle from holidaycode where hdayid='" + hdayid + "'");
if (tmpdate.DayOfWeek.ToString("d") == "0" || tmpdate.DayOfWeek.ToString("d") == "6") //'例假日
{
using (SqlConnection conn3 = dbobj.get_conn("Aitag_DBContext"))
{
sql = "select wstitle from holidayschedule where comid='" + dr["empworkcomp"] + "' and wstype='1' and wsdate='" + dbobj.get_date(tmpdate.ToString(), "1") + "'"; //'20160920因無年假身分別故先取消
using (SqlCommand cmd3 = new SqlCommand(sql, conn3))
{
SqlDataReader dr3 = cmd3.ExecuteReader();
if (dr3.Read())
{
clogcomment = dr3["wstitle"].ToString();
if (hlogstatus == "1")
{
clogstatus = "0";
}
else
{
if (rscard.cloghour < 8)
{
if (int.Parse(clogstime) > 093000)
{
clogstatus = "1";
}
if (int.Parse(clogetime) < 183000)
{
clogstatus = "2";
}
if (rscard.cloghour == 0)
{
if (tmpdate >= DateTime.Parse(dr["jobdate"].ToString()))
{
clogstatus = "3";
}
else
{
clogstatus = "4";
}
}
}
else
{
clogstatus = "0";
}
}
}
else
{
clogstatus = "0";
clogcomment = "假日";
}
dr3.Close();
}
}
}
else //'上班日
{
using (SqlConnection conn3 = dbobj.get_conn("Aitag_DBContext"))
{
sql = "select wstitle from holidayschedule where comid='" + dr["empworkcomp"] + "' and wstype='0' and wsdate='" + dbobj.get_date(tmpdate.ToString(), "1") + "'"; //'20160920因無年假身分別故先取消
using (SqlCommand cmd3 = new SqlCommand(sql, conn3))
{
SqlDataReader dr3 = cmd3.ExecuteReader();
if (dr3.Read())
{
clogcomment = dr3["wstitle"].ToString();
clogstatus = "0";
}
else
{
if (hlogstatus == "1")
{
clogstatus = "0";
}
else
{
if (rscard.cloghour < 8)
{
if (int.Parse(clogstime) > 093000)
{
clogstatus = "1";
}
if (int.Parse(clogetime) < 183000)
{
clogstatus = "2";
}
if (rscard.cloghour == 0)
{
if (tmpdate >= DateTime.Parse(dr["jobdate"].ToString()))
{
clogstatus = "3";
}
else
{
clogstatus = "4";
}
}
}
else
{
clogstatus = "0";
}
}
}
dr3.Close();
}
}
}
rscard.clogstime = clogstime;
rscard.clogetime = clogetime;
rscard.clogstatus = clogstatus;
rscard.clogcomment = clogcomment;
rscard.comid = (string)Session["comid"];
rscard.bmodid = (string)Session["empid"];
rscard.bmoddate = DateTime.Now;
#endregion
}
}
#region 整理rscard
#endregion
using (Aitag_DBContext con = new Aitag_DBContext())
{
con.cardjudgelog.Add(rscard);
con.SaveChanges();
}
}
dr.Close();
}
}
}
comconn1.Close();
comconn1.Dispose();
string tmpform = "";
tmpform = "<body onload=qfr1.submit();>";
tmpform += "<form name='qfr1' action='/cardabnormallog/List' method='post'>";
tmpform += "<input type=hidden name='page' id='page' value='" + page + "'>";
tmpform += "<input type=hidden name='qclogsdate' id='qclogsdate' value='" + qclogsdate + "'>";
tmpform += "<input type=hidden name='qclogedate' id='qclogedate' value='" + qclogedate + "'>";
tmpform += "</form>";
tmpform += "</body>";
return(new ContentResult()
{
Content = @"<script>alert('差勤轉入成功!!');</script>" + tmpform
});
}