public ActionResult resthourcheck1rpt(int?page, string orderdata, string orderdata1) { page = ((!page.HasValue || page < 1) ? 1 : page); ViewBag.page = page; if (string.IsNullOrWhiteSpace(orderdata)) { orderdata = "resthourlog.adddate"; } if (string.IsNullOrWhiteSpace(orderdata1)) { orderdata1 = "desc"; } ViewBag.orderdata = orderdata; ViewBag.orderdata1 = orderdata1; string qdptid = "", qempname = "", qaddtype = "", qaddsdate = "", qaddedate = ""; if (!string.IsNullOrWhiteSpace(Request["qdptid"])) { qdptid = Request["qdptid"].Trim(); ViewBag.qdptid = qdptid; } if (!string.IsNullOrWhiteSpace(Request["qempname"])) { qempname = Request["qempname"].Trim(); ViewBag.qempname = qempname; } if (!string.IsNullOrWhiteSpace(Request["qaddtype"])) { qaddtype = Request["qaddtype"].Trim(); ViewBag.qaddtype = qaddtype; } if (!string.IsNullOrWhiteSpace(Request["qaddedate"])) { qaddedate = Request["qaddedate"].Trim(); ViewBag.qaddedate = qaddedate; } qaddsdate = NullStDate(Request["qaddsdate"]); ViewBag.qaddsdate = qaddsdate; qaddedate = NullTeDate(Request["qaddedate"]); ViewBag.qaddedate = qaddedate; //NullStDate 跟 NullTeDate 會判斷格式,有錯誤就 修改全域的DateEx if (DateEx != "") { ViewBag.DateEx = @"<script>alert(""" + DateEx + @""");</script>"; } string Excel = "", Excel2 = ""; string sqlstr = ""; using (Aitag_DBContext con = new Aitag_DBContext()) { sqlstr = "SELECT * FROM resthourlog "; sqlstr += " INNER JOIN employee ON resthourlog.empid = employee.empid "; sqlstr += " where resthourlog.rstype='2' and resthourlog.ifactive = 'y'"; if (qdptid != "") { sqlstr += " and employee.empworkdepid ='" + qdptid + "'"; } if (qempname != "") { sqlstr += " and employee.empname like N'%" + qempname + "%'"; } if (qaddtype != "") { switch (qaddtype) { case "1": sqlstr += " and resthourlog.resthour > 0"; break; case "2": sqlstr += " and resthourlog.moneyhour > 0"; break; case "3": sqlstr += " and resthourlog.ifdinner = 'y'"; break; default: break; } } if (qaddsdate != "") //{ sqlstr += " and resthourlog.adddate >= '" + qaddsdate + "'"; } { sqlstr += " and resthourlog.adddate >= '2016/03/01'"; } if (qaddedate != "") { sqlstr += " and resthourlog.adddate <= '" + qaddedate + "'"; } sqlstr += " order by " + orderdata + " " + orderdata1; } #region 組 Excel 格式 Excel += "<HTML>"; Excel += "<HEAD>"; Excel += @"<meta http-equiv=""Content-Type"" content=""text/html; charset=utf-8"">"; Excel += "</HEAD>"; Excel += "<body>"; Excel += "<table border=1 cellpadding=0 cellspacing=0 bordercolor=#000000 bordercolordark=#ffffff width=900 >"; Excel += "<tr align=center>"; Excel += @"<td colspan=""11"" style=""font-size:14pt"">加班時數紀錄表"; if (qaddsdate != "" || qaddedate != "") { Excel += qaddsdate + "~" + qaddedate; } Excel += "</td>"; Excel += "</tr>"; Excel += "<tr align=center>"; Excel += "<td>加班日期</td>"; Excel += "<td>部門</td>"; Excel += "<td>申請人</td>"; Excel += "<td>加班時數</td>"; Excel += "<td>請款時數</td>"; Excel += "<td>1.34</td>"; Excel += "<td>1.67</td>"; Excel += "<td>1.0(假日)</td>"; Excel += "<td>補休時數</td>"; Excel += "<td>已休時數</td>"; Excel += "<td>補休期限</td>"; Excel += "</tr>"; Aitag.Models.NDcommon dbobj = new Aitag.Models.NDcommon(); using (SqlConnection conn = dbobj.get_conn("Aitag_DBContext")) { using (SqlCommand cmd = new SqlCommand(sqlstr, conn)) { SqlDataReader dr = cmd.ExecuteReader(); string dpttitle = "", empname = ""; double hour = 0; while (dr.Read()) { using (SqlConnection conn1 = dbobj.get_conn("Aitag_DBContext")) { dpttitle = dbobj.get_dbvalue(conn1, "select dpttitle form Department where dptid='" + dr["empworkdepid"] + "'"); empname = dbobj.get_name(conn1, dbobj.get_dbnull2(dr["empid"])); } hour = Convert.ToDouble(dr["resthour"]) + Convert.ToDouble(dr["moneyhour"]); Excel2 += "<tr>"; Excel2 += "<td>" + Convert.ToDateTime(dr["adddate"]).ToString("yyyy/MM/dd") + "</td>"; Excel2 += "<td>" + dpttitle + "</td>"; Excel2 += "<td>" + empname + "</td>"; Excel2 += "<td>" + hour + "</td>"; Excel2 += "<td>" + dr["moneyhour"] + "</td>"; Excel2 += "<td>" + dr["moneyh1"] + "</td>"; Excel2 += "<td>" + dr["moneyh2"] + "</td>"; Excel2 += "<td>" + dr["moneyh3"] + "</td>"; Excel2 += "<td>" + dr["resthour"] + "</td>"; Excel2 += "<td>" + dr["usehour"] + "</td>"; Excel2 += "<td>" + dr["rsdeaddate"] + "</td>"; Excel2 += "</tr>"; //dbobj.get_dbnull2().Trim() } if (Excel2 == "") { Excel += "<tr align=left><td colspan=6>目前沒有資料</td></tr>"; } else { Excel += Excel2; } dr.Close(); } } #endregion Excel += "</table>"; Excel += "</body>"; Excel += "</HTML>"; ViewBag.Excel = Excel; return(View()); }