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());
}
