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 }); }
public ActionResult monthcardsave(int?page, string orderdata, string orderdata1) { //save................ string strcjid = Request["cjid"].ToString(); string[] arrcjid = strcjid.Split(','); string cchkstatus = Request["cchkstatus"].ToString(); string today = DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss"); Aitag.Models.NDcommon dbobj = new Aitag.Models.NDcommon(); foreach (var cjid in arrcjid) { string clogstime = Request["clogstime" + cjid].ToString().Replace(":", ""); string clogetime = Request["clogetime" + cjid].ToString().Replace(":", ""); string cchkcomment = Request["cchkcomment" + cjid]; double cloghour = 0; if (clogstime != "" && clogetime != "") { double hours = Convert.ToDouble("0" + clogetime.Substring(0, 2)) - Convert.ToDouble("0" + clogstime.Substring(0, 2)); double mints = Convert.ToDouble("0" + clogetime.Substring(2, 2)) - Convert.ToDouble("0" + clogstime.Substring(2, 2)); cloghour = hours + mints / 60 - 1.5; } if (cloghour > 0 || cchkcomment != "") { string sql = "update cardjudgelog set "; sql += "clogstime = '" + clogstime + "', "; sql += "clogetime = '" + clogetime + "', "; sql += "cloghour = '" + cloghour.ToString() + "', "; if (cloghour >= 8) { sql += "clogstatus = '0', "; } if (cchkstatus == "2") { sql += "cchkownman = '" + Request["qempid"].ToString() + "', cchkowndate = '" + today + "',"; } sql += "cchkcomment = '" + cchkcomment + "', "; sql += "bmodid = '" + (string)Session["empid"] + "', "; sql += "bmoddate = '" + today + "' "; sql += "where cjid = '" + cjid + "' "; dbobj.dbexecute("Aitag_DBContext", sql); } } //回monthcardlist.............. string qempid = Request["qempid"].ToString(); string qtheday = Request["qtheday"].ToString(); string qclogstatus = Request["qclogstatus"].ToString(); string tmpform = ""; tmpform = "<body onload=qfr1.submit();>"; tmpform += "<form name='qfr1' action='/cardabnormallog/monthcardlist' method='post'>"; tmpform += "<input type=hidden id='qempid' name='qempid' value='" + qempid + "'>"; tmpform += "<input type=hidden id='qtheday' name='qtheday' value='" + qtheday + "'>"; tmpform += "<input type=hidden id='qclogstatus' name='qclogstatus' value='" + qclogstatus + "'>"; tmpform += "</form>"; tmpform += "</body>"; return(new ContentResult() { Content = @"" + tmpform }); }