private object get_hdaytitle(string hdayid)
{
string hdayid1 = hdayid.Replace(",", "','").Replace(" ", "");
hdayid1 = "'" + hdayid1 + "'";
string sqlstr = "select hdayid, hdaytitle from holidaycode where hdayid in (" + hdayid1 + ") order by hdayid";
System.Collections.ArrayList list = new System.Collections.ArrayList();
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();
hdayid = "";
while (dr.Read())
{
list.Add(dbobj.get_dbnull2(dr["hdaytitle"]));
hdayid += dr["hdayid"] + ",";
}
dr.Close();
}
}
hdayid = hdayid.Substring(0, hdayid.Length - 1);
ViewBag.hdayid = hdayid;
return(list);
}
public ActionResult csvmonthcard(int?page, string orderdata, string orderdata1)
{
string qdptid = "", qempname = "", qclogsdate = "", qclogedate = "", qcchkstatus = "";
string sql = "select CAST(CAST(NEWID() AS binary(3)) AS int) as cjid, empid, empname, dptid ,NULL as clogdate,'' as clogstime, '' as clogetime, '' as clogstatus, '' as clogcomment, sum(cloghour) as cloghour , '' as comid, '' as bmodid, NULL as bmoddate, cchkstatus, '' as cchkcomment, '' as cchkownman, NULL as cchkowndate from cardjudgelog where comid='" + Session["comid"].ToString() + "' and";
if (!string.IsNullOrWhiteSpace(Request["qdptid"].ToString()))
{
sql += " dptid = '" + Request["qdptid"].ToString() + "' and ";
}
if (!string.IsNullOrWhiteSpace(Request["qempname"].ToString()))
{
sql += " empname like '%" + Request["qempname"].ToString() + "%' and ";
}
if (!string.IsNullOrWhiteSpace(Request["qclogsdate"].ToString()))
{
sql += " clogdate >= '" + Request["qclogsdate"].ToString() + "' and ";
}
if (!string.IsNullOrWhiteSpace(Request["qclogedate"].ToString()))
{
sql += " clogdate <= '" + Request["qclogedate"].ToString() + "' and ";
}
if (!string.IsNullOrWhiteSpace(Request["qcchkstatus"].ToString()))
{
sql += " cchkstatus = '" + Request["qcchkstatus"].ToString() + "' and ";
}
sql = sql.Substring(0, sql.Length - 5);
sql += " group by dptid, empid, empname, cchkstatus order by dptid,empid";
string Excel = "", Excel2 = "";
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>確認狀態</td>";
Excel += "<td>差勤日期</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(sql, conn))
{
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string cchkstatus = "";
switch (dr["cchkstatus"].ToString())
{
case "0":
cchkstatus = "未確認";
break;
case "1":
cchkstatus = "發信通知";
break;
case "2":
cchkstatus = "已確認";
break;
default:
break;
}
Excel2 += "<tr>";
Excel2 += "<td>" + cchkstatus.Trim() + "</td>";
Excel2 += "<td>" + Request["qclogsdate"].ToString() + "~" + Request["qclogedate"].ToString() + "</td>";
using (SqlConnection conn1 = dbobj.get_conn("Aitag_DBContext"))
{
Excel2 += "<td>" + dbobj.get_dbvalue(conn1, "select dpttitle from Department where dptid='" + dr["dptid"].ToString() + "'") + "</td>";
}
Excel2 += "<td>" + dbobj.get_dbnull2(dr["empname"]) + "</td>";
Excel2 += "<td>" + dbobj.get_dbnull2(dr["cloghour"]) + "</td>";
Excel2 += "</tr>";
}
if (Excel2 == "")
{
Excel += "<tr align=left><td colspan=6>目前沒有資料</td></tr>";
}
else
{
Excel += Excel2;
}
dr.Close();
}
}
Excel += "</table>";
Excel += "</body>";
Excel += "</HTML>";
ViewBag.Excel = Excel;
return(View());
}
public ActionResult csvcardabnormallog(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["qclogsdate"]))
{
qclogsdate = Request["qclogsdate"].Trim();
ViewBag.qclogsdate = qclogsdate;
}
if (!string.IsNullOrWhiteSpace(Request["qclogedate"]))
{
qclogedate = Request["qclogedate"].Trim();
ViewBag.qclogedate = qclogedate;
}
//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 cardjudgelog where comid='" + (string)Session["comid"] + "' and";
if (qdptid != "")
{
sqlstr += " dptid='" + qdptid + "' and";
}
if (qempname != "")
{
sqlstr += " empname like N'%" + qempname + "%' and";
}
if (qclogsdate != "")
{
sqlstr += " clogdate >= '" + qclogsdate + "' and";
}
if (qclogedate != "")
{
sqlstr += " clogdate <= '" + qclogedate + "' and";
}
if (qclogstatus != "")
{
sqlstr += " clogstatus = '" + qclogstatus + "' and";
}
if (qempid != "")
{
sqlstr += " empid = '" + qempid + "' and";
}
sqlstr = sqlstr.Substring(0, sqlstr.Length - 5);
sqlstr += " order by " + orderdata + " " + orderdata1;
//var query = con.systemlog.SqlQuery(sqlstr).AsQueryable();
//result = query.ToPagedList<systemlog>(0, 10000);
}
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>部門</td>";
Excel += "<td>員工姓名</td>";
Excel += "<td>刷卡日期</td>";
Excel += "<td>上班刷卡</td>";
Excel += "<td>下班刷卡</td>";
Excel += "<td>星期</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 clogstatus = "";
while (dr.Read())
{
switch (dbobj.get_dbnull2(dr["clogstatus"]))
{
case "0":
clogstatus = "正常";
break;
case "1":
clogstatus = "遲到";
break;
case "2":
clogstatus = "早退";
break;
case "3":
clogstatus = "曠職";
break;
case "4":
clogstatus = "未到職";
break;
default:
clogstatus = "";
break;
}
//string dpttitle = dbobj.get_dbvalue(conn, "select dpttitle from department where dptid='" + dbobj.get_dbnull2(dr["dptid"]) + "'");
string tmpsql = "select dpttitle from Department where dptid='" + dbobj.get_dbnull2(dr["dptid"]) + "' and comid='" + (string)Session["comid"] + "'";
SqlConnection conn1 = dbobj.get_conn("Aitag_DBContext");
string dpttitle = dbobj.get_dbvalue(conn1, tmpsql);
conn1.Close();
conn1.Dispose();
string tmpstime = dbobj.get_dbnull2((dr["clogstime"])).Trim();
if (tmpstime.Length == 6)
{
string tmpstime1 = tmpstime.Substring(0, 2);
if (tmpstime1 == "24")
{
tmpstime1 = "00";
}
tmpstime = tmpstime1 + ":" + tmpstime.Substring(2, 2) + ":" + tmpstime.Substring(4, 2);
}
else
{
tmpstime = "";
}
string tmpetime = dbobj.get_dbnull2((dr["clogetime"])).Trim();
if (tmpetime.Length == 6)
{
string tmpetime1 = tmpetime.Substring(0, 2);
if (tmpetime1 == "24")
{
tmpetime1 = "00";
}
tmpetime = tmpetime1 + ":" + tmpetime.Substring(2, 2) + ":" + tmpetime.Substring(4, 2);
}
else
{
tmpetime = "";
}
string weekofday = Convert.ToDateTime(dbobj.get_dbnull2((dr["clogdate"])).Trim()).ToString("ddd");
weekofday = weekofday.Substring(1, 1);
string thours = dr["cloghour"].ToString(); //工時
if (Convert.ToDouble(dr["cloghour"]) < 0)
{
thours = "0";
}
Excel2 += "<tr>";
Excel2 += "<td>" + dpttitle + "</td>";
Excel2 += "<td>" + dbobj.get_dbnull2(dr["empname"]) + "</td>";
Excel2 += "<td>" + Convert.ToDateTime(dr["clogdate"]).ToString("yyyy/MM/dd") + "</td>";
Excel2 += "<td>" + tmpstime + "</td>";
Excel2 += "<td>" + tmpetime + "</td>";
Excel2 += "<td>" + weekofday + "</td>";
Excel2 += "<td>" + thours + "</td>";
Excel2 += "<td>" + clogstatus.Trim() + "</td>";
Excel2 += "<td>" + dbobj.get_dbnull2((dr["clogcomment"])).Trim() + "</td>";
Excel2 += "</tr>";
}
if (Excel2 == "")
{
Excel += "<tr align=left><td colspan=9>目前沒有資料</td></tr>";
}
else
{
Excel += Excel2;
}
dr.Close();
}
}
Excel += "</table>";
Excel += "</body>";
Excel += "</HTML>";
ViewBag.Excel = Excel;
return(View());
}
public ActionResult csvsystemlogout(int?page, string orderdata, string orderdata1)
{
page = ((!page.HasValue || page < 1) ? 1 : page);
ViewBag.page = page;
if (string.IsNullOrWhiteSpace(orderdata))
{
orderdata = "slid";
}
if (string.IsNullOrWhiteSpace(orderdata1))
{
orderdata1 = "desc";
}
ViewBag.orderdata = orderdata;
ViewBag.orderdata1 = orderdata1;
string qcomid = "", qsflag = "", qslaccount = "", qsltext = "", qsodate1 = "", qsodate2 = "";
if (!string.IsNullOrWhiteSpace(Request["qcomid"]))
{
qcomid = Request["qcomid"].Trim();
ViewBag.qcomid = qcomid;
}
if (!string.IsNullOrWhiteSpace(Request["qsflag"]))
{
qsflag = Request["qsflag"].Trim();
ViewBag.qsflag = qsflag;
}
if (!string.IsNullOrWhiteSpace(Request["qslaccount"]))
{
qslaccount = Request["qslaccount"].Trim();
ViewBag.qslaccount = qslaccount;
}
if (!string.IsNullOrWhiteSpace(Request["qsltext"]))
{
qsltext = Request["qsltext"].Trim();
ViewBag.qsltext = qsltext;
}
if (!string.IsNullOrWhiteSpace(Request["qsodate1"]))
{
qsodate1 = Request["qsodate1"].Trim();
ViewBag.qsodate1 = qsodate1;
}
if (!string.IsNullOrWhiteSpace(Request["qsodate2"]))
{
qsodate2 = Request["qsodate2"].Trim();
ViewBag.qsodate2 = qsodate2;
}
//IPagedList<systemlog> result;
string Excel = "", Excel2 = "";
string sqlstr = "";
using (Aitag_DBContext con = new Aitag_DBContext())
{
sqlstr = "select * from systemlog where";
if (qcomid != "")
{
sqlstr += " comid = '" + qcomid + "' and";
}
if (qsflag != "")
{
sqlstr += " sflag = '" + qsflag + "' and";
}
if (qslaccount != "")
{
sqlstr += " slaccount = '" + qslaccount + "' and";
}
if (qsltext != "")
{
sqlstr += " (slevent like N'%" + qsltext + "%' or sname like N'%" + qsltext + "%') and";
}
if (qsodate1 == "")
{
qsodate1 = DateTime.Now.ToString("yyyy") + "/1/1";
}
if (qsodate2 == "")
{
DateTime date2 = new DateTime(DateTime.Now.Year + 1, 1, 1);
date2 = date2.AddDays(-1);
qsodate2 = date2.ToString("yyyy/MM/dd");
}
string DateEx = "";
try
{
DateTime.Parse(qsodate1);
sqlstr += " sodate >= '" + qsodate1 + "' and";
}
catch
{
DateEx += @"異動時間起格式錯誤!!\n";
}
try
{
DateTime.Parse(qsodate2);
sqlstr += " sodate <= '" + qsodate2 + "' and";
}
catch
{
DateEx += @"異動時間訖格式錯誤!!\n";
}
if (DateEx != "")
{
ViewBag.DateEx = @"<script>alert(""" + DateEx + @""");</script>";
}
sqlstr = sqlstr.Substring(0, sqlstr.Length - 5);
sqlstr += " order by " + orderdata + " " + orderdata1;
//var query = con.systemlog.SqlQuery(sqlstr).AsQueryable();
//result = query.ToPagedList<systemlog>(0, 10000);
}
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>異動狀態</td>";
Excel += "<td>使用者帳號</td>";
Excel += "<td>使用功能</td>";
Excel += "<td>內容</td>";
Excel += "<td>登入IP</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 sflag = "", empname = "";
while (dr.Read())
{
switch (dbobj.get_dbnull2(dr["sflag"]))
{
case "A":
sflag = "新增";
break;
case "M":
sflag = "修改";
break;
case "D":
sflag = "刪除";
break;
case "L":
sflag = "登入";
break;
default:
sflag = "";
break;
}
empname = dbobj.get_dbvalue(conn, "select empname from employee where empid='" + dbobj.get_dbnull2(dr["slaccount"]) + "'");
Excel2 += "<tr>";
Excel2 += "<td>" + sflag.Trim() + "</td>";
Excel2 += "<td>" + empname.Trim() + "</td>";
Excel2 += "<td>" + dbobj.get_dbnull2((dr["sname"])).Trim() + "</td>";
Excel2 += "<td>" + dbobj.get_dbnull2((dr["slevent"])).Trim() + "</td>";
Excel2 += "<td>" + dbobj.get_dbnull2((dr["sfromip"])).Trim() + "</td>";
Excel2 += "<td>" + dr["sodate"] + "</td>";
Excel2 += "</tr>";
}
if (Excel2 == "")
{
Excel += "<tr align=left><td colspan=6>目前沒有資料</td></tr>";
}
else
{
Excel += Excel2;
}
dr.Close();
}
}
Excel += "</table>";
Excel += "</body>";
Excel += "</HTML>";
ViewBag.Excel = Excel;
return(View());
}
public ActionResult battamoneyrpt(int?page, string orderdata, string orderdata1)
{
page = ((!page.HasValue || page < 1) ? 1 : page);
ViewBag.page = page;
if (string.IsNullOrWhiteSpace(orderdata))
{
orderdata = "bsno";
}
if (string.IsNullOrWhiteSpace(orderdata1))
{
orderdata1 = "desc";
}
ViewBag.orderdata = orderdata;
ViewBag.orderdata1 = orderdata1;
string qblogstatus = "", qempname = "", qdptid = "", qblogsdate = "", qblogedate = "";
if (!string.IsNullOrWhiteSpace(Request["qblogstatus"]))
{
qblogstatus = Request["qblogstatus"].Trim();
ViewBag.qblogstatus = qblogstatus;
}
if (!string.IsNullOrWhiteSpace(Request["qempname"]))
{
qempname = Request["qempname"].Trim();
ViewBag.qempname = qempname;
}
if (!string.IsNullOrWhiteSpace(Request["qdptid"]))
{
qdptid = Request["qdptid"].Trim();
ViewBag.qdptid = qdptid;
}
qblogsdate = NullStDate(Request["qblogsdate"]);
qblogsdate = "2016/03/01";
ViewBag.qblogsdate = qblogsdate;
qblogedate = NullTeDate(Request["qblogedate"]);
ViewBag.qblogedate = qblogedate;
//NullStDate 跟 NullTeDate 會判斷格式，有錯誤就 修改全域的DateEx
if (DateEx != "")
{
ViewBag.DateEx = @"<script>alert(""" + DateEx + @""");</script>";
}
string Excel = "", Excel2 = "";
string sqlstr = "";
using (Aitag_DBContext con = new Aitag_DBContext())
{
string viewid = "";
string[] mpriv = (string[])Session["priv"];
//viewid = get_viewpriv(int.Parse(funcpriv(2)), int.Parse(mpriv(realsid, 2)));
sqlstr = "select * from battalog where (blogtype='1' or (blogtype='2' and (pbsno='' or pbsno is null )) ) and comid='" + (string)Session["comid"] + "'";
if (viewid != "")
{
sqlstr += " and bmodid = '" + viewid + "'";
}
if (qblogstatus != "" && qblogstatus != "all")
{
sqlstr += " and blogstatus = '" + qblogstatus + "'";
}
else if (qblogstatus == "")
{
sqlstr += " and blogstatus = '1'";
ViewBag.qblogstatus = "1";
}
if (qempname != "")
{
sqlstr += " and empname like N'%" + qempname + "%'";
}
if (qdptid != "")
{
sqlstr += " and dptid='" + qdptid + "'";
}
sqlstr += " and (( blogsdate >= '" + qblogsdate + "' and blogsdate <= '" + qblogedate + "' ) or ";
sqlstr += "( blogedate >= '" + qblogsdate + "' and blogedate <= '" + qblogedate + "'))";
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=""8"" style=""font-size:14pt"">出差明細表";
Excel += "</td>";
Excel += "</tr>";
Excel += "<tr align=center>";
int count = 7;
Excel += "<td colspan='" + count + "' ></td><td>列印日期：" + DateTime.Now.ToString("yyyy/MM/dd") + "</td>";
Excel += "</tr>";
Excel += "<tr align=center>";
Excel += "<td>狀態</td>";
Excel += "<td>核銷</td>";
Excel += "<td>員工編號</td>";
Excel += "<td>姓名</td>";
Excel += "<td>部門</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 = "";
string empno = "", blogstatus = "", ifhdell = "";
string SEtime = "自{0}({1}時)<br>至{2}({3}時)";
string chkitem = "", blogsdate = "", blogstime = "", blogedate = "", blogetime = "";
int bloghour = 0;
while (dr.Read())
{
blogstatus = dbobj.get_dbnull2(dr["blogstatus"]);
switch (blogstatus)
{
case "0":
blogstatus = "簽核中";
break;
case "1":
blogstatus = "已核准";
break;
case "2":
blogstatus = "退回";
break;
case "D":
blogstatus = "撤回";
break;
default:
break;
}
ifhdell = dbobj.get_dbnull2(dr["ifhdell"]);
if (ifhdell == "y")
{
ifhdell = "是";
}
else if (ifhdell == "n")
{
ifhdell = "否";
}
using (SqlConnection comconn = dbobj.get_conn("Aitag_DBContext"))
{
empno = "select empno from employee where empid='" + dbobj.get_dbnull2(dr["empid"]) + "'"; empno = dbobj.get_dbvalue(comconn, empno);
dpttitle = "select dpttitle from Department where dptid='" + dbobj.get_dbnull2(dr["dptid"]) + "' and comid='" + (string)Session["comid"] + "'"; dpttitle = dbobj.get_dbvalue(comconn, dpttitle);
blogsdate = Convert.ToDateTime(dbobj.get_dbnull2(dr["blogsdate"])).ToString("yyyy/MM/dd");
blogstime = int.Parse(dbobj.get_dbnull2(dr["blogstime"])).ToString("00");
blogedate = Convert.ToDateTime(dbobj.get_dbnull2(dr["blogedate"])).ToString("yyyy/MM/dd");
blogetime = int.Parse(dbobj.get_dbnull2(dr["blogetime"])).ToString("00");
bloghour = int.Parse("0" + dbobj.get_dbnull2(dr["bloghour"]));
if (bloghour > 0)
{
bloghour = bloghour / 8;
}
else
{
bloghour = 0;
}
chkitem = "select chkitem from checkcode where chkclass = '90' and chkcode = '" + dbobj.get_dbnull2(dr["blogaddr"]) + "'"; chkitem = dbobj.get_dbvalue(comconn, chkitem);
}
Excel2 += "<tr>";
Excel2 += "<td>" + blogstatus + "</td>";
Excel2 += "<td>" + ifhdell + "</td>";
Excel2 += "<td>" + empno + "</td>";
Excel2 += "<td>" + dbobj.get_dbnull2(dr["empname"]) + "</td>";
Excel2 += "<td>" + dpttitle + "</td>";
Excel2 += "<td>" + String.Format(SEtime, blogsdate, blogstime, blogedate, blogetime) + "</td>";
Excel2 += "<td>" + bloghour + "</td>";
Excel2 += "<td>" + chkitem;
if (dbobj.get_dbnull2(dr["blogplace"]) != "")
{
Excel2 += dbobj.get_dbnull2(dr["blogplace"]);
}
Excel2 += "</td>";
Excel2 += "</tr>";
}
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());
}
public ActionResult daliyreportrpt(int?page, string orderdata, string orderdata1)
{
page = ((!page.HasValue || page < 1) ? 1 : page);
ViewBag.page = page;
if (string.IsNullOrWhiteSpace(orderdata))
{
orderdata = "empworkdepid,empid";
}
if (string.IsNullOrWhiteSpace(orderdata1))
{
orderdata1 = "asc";
}
ViewBag.orderdata = orderdata;
ViewBag.orderdata1 = orderdata1;
string qdptid = "", qhlogstatus = "", qempname = "", qworksdate = "", qworkedate = "", hdayid = "";
if (!string.IsNullOrWhiteSpace(Request["qdptid"]))
{
qdptid = Request["qdptid"].Trim();
ViewBag.qdptid = qdptid;
}
if (!string.IsNullOrWhiteSpace(Request["qempname"]))
{
qempname = Request["qempname"].Trim();
ViewBag.qempname = qempname;
}
//繞細項用 qhlogstatus qworksdate qworkedate
if (!string.IsNullOrWhiteSpace(Request["qhlogstatus"]))
{
qhlogstatus = Request["qhlogstatus"].Trim();
ViewBag.qhlogstatus = qhlogstatus;
}
qworksdate = NullStDate(Request["qworksdate"]);
ViewBag.qworksdate = qworksdate;
qworkedate = NullTeDate(Request["qworkedate"]);
ViewBag.qworkedate = qworkedate;
//NullStDate 跟 NullTeDate 會判斷格式，有錯誤就 修改全域的DateEx
if (DateEx != "")
{
ViewBag.DateEx = @"<script>alert(""" + DateEx + @""");</script>";
}
if (!string.IsNullOrWhiteSpace(Request["hdayid"]))
{
hdayid = Request["hdayid"].Trim();
ViewBag.hdayid1 = get_hdaytitle(hdayid);
}
else
{
hdayid = "A01,A02,A03,A04,A05,A06";
ViewBag.hdayid1 = get_hdaytitle(hdayid);
}
string Excel = "", Excel2 = "";
string sqlstr = "";
using (Aitag_DBContext con = new Aitag_DBContext())
{
sqlstr = "select * from employee where empstatus<>'4' and empworkcomp='" + (string)Session["comid"] + "' and";
//'部門 組多筆
if (qdptid != "")
{
string tmpa = "";
tmpa += "'";
tmpa += qdptid.Replace(",", "','");
tmpa += "'";
sqlstr += " empworkdepid in (" + tmpa + ") and";
}
if (qempname != "")
{
sqlstr += " empname like N'%" + qempname + "%' and";
}
sqlstr = sqlstr.Substring(0, sqlstr.Length - 5);
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 (qworksdate != "" || qworkedate != "")
{
Excel += qworksdate + "~" + qworkedate;
}
Excel += "</td>";
Excel += "</tr>";
Excel += "<tr align=center>";
int count = ViewBag.hdayid1.Count + 3;
Excel += "<td colspan='" + count + "' ></td><td>製表日期：" + DateTime.Now.ToString("yyyy/MM/dd") + "</td>";
Excel += "</tr>";
Excel += "<tr align=center>";
Excel += "<td>部門</td>";
Excel += "<td>員工姓名</td>";
Excel += "<td>到職日</td>";
foreach (string v in ViewBag.hdayid1)
{
Excel += "<td>" + v + "</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 = "";
using (SqlConnection comconn = dbobj.get_conn("Aitag_DBContext"))
{
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_dbnull2(dr["empname"]);
}
Excel2 += "<tr>";
Excel2 += "<td>" + dpttitle + "</td>";
Excel2 += "<td>" + empname + "</td>";
Excel2 += "<td>" + Convert.ToDateTime(dr["jobdate"]).ToString("yyyy/MM/dd") + "</td>";
Excel2 += get_daliyreportHour(comconn, dbobj.get_dbnull2(dr["empid"]), dbobj.get_dbnull2(dr["empworkcomp"]));
Excel2 += "</tr>";
//dbobj.get_dbnull2().Trim()
}
}
if (Excel2 == "")
{
Excel += "<tr align=left><td colspan=6>目前沒有資料</td></tr>";
}
else
{
Excel += Excel2;
}
dr.Close();
}
}
Excel += "</table>";
Excel += "</body>";
Excel += "</HTML>";
#endregion
ViewBag.Excel = Excel;
return(View());
}
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());
}
public ActionResult csvsales_competition(int?page, string orderdata, string orderdata1)
{
page = ((!page.HasValue || page < 1) ? 1 : page);
ViewBag.page = page;
if (string.IsNullOrWhiteSpace(orderdata))
{
orderdata = " ccid";
}
if (string.IsNullOrWhiteSpace(orderdata1))
{
orderdata1 = " asc";
}
ViewBag.orderdata = orderdata;
ViewBag.orderdata1 = orderdata1;
string qcusttitle = "", qprodtitle = "";
if (!string.IsNullOrWhiteSpace(Request["qprodtitle"]))
{
qprodtitle = Request["qprodtitle"].Trim();
ViewBag.qprodtitle = qprodtitle;
}
if (!string.IsNullOrWhiteSpace(Request["qcusttitle"]))
{
qcusttitle = Request["qcusttitle"].Trim();
ViewBag.qcusttitle = qcusttitle;
}
//IPagedList<systemlog> result;
string Excel = "", Excel2 = "";
string sqlstr = "";
using (Aitag_DBContext con = new Aitag_DBContext())
{
sqlstr = "select * from sales_competition where";
if (qprodtitle != "")
{
sqlstr += " prodtitle like '%" + qprodtitle + "%' and";
}
if (qcusttitle != "")
{
sqlstr += " custtitle like '%" + qcusttitle + "%' and";
}
sqlstr = sqlstr.Substring(0, sqlstr.Length - 5);
sqlstr += " order by " + orderdata + " " + orderdata1;
//var query = con.systemlog.SqlQuery(sqlstr).AsQueryable();
//result = query.ToPagedList<systemlog>(0, 10000);
}
Aitag.Models.NDcommon dbobj = new Aitag.Models.NDcommon();
System.Data.SqlClient.SqlConnection comconn = dbobj.get_conn("Aitag_DBContext");
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><td colspan='28'>聯廣傳播集團 New Business (NewB)控管表</td></tr>";
Excel += "<tr><td colspan='28'>資料日期：" + DateTime.Today.ToShortDateString();
Excel += "</td></tr>";
Excel += "<tr align=center>";
Excel += "<td rowspan='2'>序號</td>";
Excel += "<td rowspan='2'>公司名稱</td>";
Excel += "<td rowspan='2'>客戶名稱</td>";
Excel += "<td rowspan='2'>產業</td>";
Excel += "<td rowspan='2'>產品</td>";
Excel += "<td rowspan='2'>業務部門</td>";
Excel += "<td rowspan='2'>申請日期</td>";
Excel += "<td rowspan='2'>預算金額</td>";
Excel += "<td rowspan='2'>客戶分級</td>";
Excel += "<td colspan='2' >類型</td>";
Excel += "<td rowspan='2'>比稿/提案時間</td>";
Excel += "<td colspan='8'>合作項目</td>";
// Excel += "<td rowspan='2'>業務組</td>";
// Excel += "<td colspan='3'>收費方式</td>";
Excel += "<td colspan='2' >競業條款評估</td>";
Excel += "<td rowspan='2'>財務說明</td>";
Excel += "<td colspan='2' >決議</td>";
Excel += "<td colspan='2' >結果回報</td>";
Excel += "</tr>";
Excel += "<tr align=center>";
Excel += "<td>比稿</td>";
Excel += "<td>提案</td>";
Excel += "<td>平面廣告</td>";
Excel += "<td>廣告片</td>";
Excel += "<td>公關行銷</td>";
Excel += "<td>網路</td>";
Excel += "<td>市場<br>調查</td>";
Excel += "<td>活動/贈品</td>";
Excel += "<td>媒體企劃/購買</td>";
Excel += "<td>其他</td>";
Excel += "<td>無</td>";
Excel += "<td>有</td>";
Excel += "<td>參加</td>";
Excel += "<td>不參加</td>";
Excel += "<td>得標</td>";
Excel += "<td>未得標</td>";
Excel += "<td>得標公司</td>";
Excel += "</tr>";
using (SqlConnection conn = dbobj.get_conn("AitagBill_DBContext"))
{
using (SqlCommand cmd = new SqlCommand(sqlstr, conn))
{
SqlDataReader dr = cmd.ExecuteReader();
string sflag = "", empname = "";
int j = 0;
while (dr.Read())
{
j = j + 1;
Excel2 += "<tr>";
Excel2 += "<td>" + j + "</td>";
Excel2 += "<td>" + dbobj.get_dbvalue(comconn, "select comsttitle from company where comid = '" + dr["comid"].ToString() + "'") + " </td>";
Excel2 += "<td>" + dr["custtitle"].ToString() + "</td>";
Excel2 += "<td>" + dbobj.get_dbnull2(dr["indclass"]) + "</td>";
Excel2 += "<td>" + dr["prodtitle"].ToString() + "</td>";
Excel2 += "<td> </td>";
Excel2 += "<td>" + dr["adddate"].ToString() + "</td>";
Excel2 += "<td>" + decimal.Parse(dr["salespmoney"].ToString()).ToString("###,###,###") + "</td>";
//分級
string level1 = "";
if (dbobj.get_dbnull2(dr["custlevel1"]).ToString() != "")
{
level1 = "A,";
}
if (dbobj.get_dbnull2(dr["custlevel2"]).ToString() != "")
{
level1 += "B,";
}
if (dbobj.get_dbnull2(dr["custlevel3"]).ToString() != "")
{
level1 += "C";
}
Excel2 += "<td>" + level1 + "</td>";
//類型
if (dbobj.get_dbnull2(dr["steptype"]).ToString() == "0")
{
Excel2 += "<td>V</td>";
}
else
{
Excel2 += "<td> </td>";
}
if (dbobj.get_dbnull2(dr["steptype"]).ToString() == "1")
{
Excel2 += "<td>V</td>";
}
else
{
Excel2 += "<td> </td>";
}
//比稿提案時間
Excel2 += "<td>" + dr["exetime"].ToString() + "</td>";
//合作項目
if (dr["corpitem"].ToString().IndexOf("01") >= 0)
{
Excel2 += "<td align=center>V</td>";
}
else
{
Excel2 += "<td align=center></td>";
}
if (dr["corpitem"].ToString().IndexOf("02") >= 0)
{
Excel2 += "<td align=center>V</td>";
}
else
{
Excel2 += "<td align=center></td>";
}
if (dr["corpitem"].ToString().IndexOf("03") >= 0)
{
Excel2 += "<td align=center>V</td>";
}
else
{
Excel2 += "<td align=center></td>";
}
if (dr["corpitem"].ToString().IndexOf("04") >= 0)
{
Excel2 += "<td align=center>V</td>";
}
else
{
Excel2 += "<td align=center></td>";
}
if (dr["corpitem"].ToString().IndexOf("05") >= 0)
{
Excel2 += "<td align=center>V</td>";
}
else
{
Excel2 += "<td align=center></td>";
}
if (dr["corpitem"].ToString().IndexOf("06") >= 0)
{
Excel2 += "<td align=center>V</td>";
}
else
{
Excel2 += "<td align=center></td>";
}
if (dr["corpitem"].ToString().IndexOf("07") >= 0)
{
Excel2 += "<td align=center>V</td>";
}
else
{
Excel2 += "<td align=center></td>";
}
if (dr["corpitem"].ToString().IndexOf("99") >= 0)
{
Excel2 += "<td align=center>V</td>";
}
else
{
Excel2 += "<td align=center></td>";
}
//法務
if (dbobj.get_dbnull2(dr["iflaw"]).ToString() == "n")
{
Excel2 += "<td>V</td>";
}
else
{
Excel2 += "<td align=center></td>";
}
if (dbobj.get_dbnull2(dr["iflaw"]).ToString() == "y")
{
Excel2 += "<td>V " + dr["lawcomment"] + "</td>";
}
else
{
Excel2 += "<td align=center></td>";
}
//財務
Excel2 += "<td>" + dr["fincomment"] + "</td>";
//參加不參加
if (dbobj.get_dbnull2(dr["slogstatus"]).ToString() == "1")
{
Excel2 += "<td>V</td>";
}
else
{
Excel2 += "<td> </td>";
}
if (dbobj.get_dbnull2(dr["slogstatus"]).ToString() == "D")
{
Excel2 += "<td>V</td>";
}
else
{
Excel2 += "<td> </td>";
}
//結果回報
if (dbobj.get_dbnull2(dr["ifget"]).ToString() == "y")
{
Excel2 += "<td>V</td>";
}
else
{
Excel2 += "<td> </td>";
}
if (dbobj.get_dbnull2(dr["ifget"]).ToString() == "n")
{
Excel2 += "<td>V</td>";
}
else
{
Excel2 += "<td> </td>";
}
Excel2 += "<td>" + dr["getcomtitle"] + "</td>";
Excel2 += "</tr>";
}
//Excel2 += "<tr></tr>";
//Excel2 += "<tr></tr>";
//Excel2 += "<tr>";
//Excel2 += "<td></td>";
//Excel2 += "<td>製表人：</td>";
//Excel2 += "<td colspan='3'>法務主管：</td>";
//Excel2 += "<td colspan='6'>財務長：</td>";
//Excel2 += "<td colspan='5'>母公司總經理：</td>";
//Excel2 += "<td colspan='3'>董事長：</td>";
Excel2 += "</tr>";
if (Excel2 == "")
{
Excel += "<tr align=left><td colspan=19>目前沒有資料</td></tr>";
}
else
{
Excel += Excel2;
}
dr.Close();
}
}
Excel += "</table>";
Excel += "</body>";
Excel += "</HTML>";
comconn.Close();
comconn.Dispose();
ViewBag.Excel = Excel;
return(View());
}