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 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 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 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 battamoneyprint(int?page, string orderdata, string orderdata1)
{
Aitag.Models.NDcommon dbobj = new Aitag.Models.NDcommon();
string qblogid = "";
if (!string.IsNullOrWhiteSpace(Request["qblogid"]))
{
qblogid = Request["qblogid"].Trim();
}
string empname = "", blogcomment = "", blogaddr = "", exrate = "", comid = "", bbillcount = "";
DateTime blogsdate, blogedate;
string sql = "select * from battalog where blogid='" + qblogid + "' and comid='" + (string)Session["comid"] + "'";
using (SqlConnection tconn = dbobj.get_conn("Aitag_DBContext"))
{
using (SqlCommand cmd = new SqlCommand(sql, tconn))
{
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
empname = dr["empname"].ToString();
blogcomment = dr["blogcomment"].ToString();
blogaddr = dr["blogaddr"].ToString();
exrate = dr["exrate"].ToString();
comid = dr["comid"].ToString();
bbillcount = dr["bbillcount"].ToString();
blogsdate = Convert.ToDateTime(dr["blogsdate"]);
blogedate = Convert.ToDateTime(dr["blogedate"]);
dr.Close();
}
tconn.Close();
tconn.Dispose();
}
//參數
string bdmonth = "", bdday = "", bdplace = "", bdwork = "", bdplane = "";
string bdcar = "", bdtrain = "", bdship = "", bdliving1 = "", bdliving2 = "";
string bdother = "", bdbillno = "", bdcomment = "", strsum = "";
double dsum = 0, ttlmoney = 0;
string bland = "", blive = "", bvisa = "", binsurance = "", badmin = "", bgift = "";
int rowcounter = 0;
string listtitle = "", Excel = "", Excel2 = "", sqlstr = "";
int diffday = 0;
using (SqlConnection conn = dbobj.get_conn("Aitag_DBContext")){
//天數
TimeSpan spantime = blogedate - blogsdate;
diffday = spantime.Days + 1;
//表單名稱
string dpttitle = "select comtitle from Company where comid='" + comid + "' ";
dpttitle = dbobj.get_dbvalue(conn, dpttitle);
if (blogaddr == "1")
{
listtitle = dpttitle + "國內出差旅費報告表";
}
else
{
listtitle = dpttitle + "國外出差旅費報告表";
}
#region 組 Excel 格式
Excel += "<HTML>";
Excel += "<HEAD>";
Excel += @"<meta http-equiv=""Content-Type"" content=""text/html; charset=utf-8"">";
Excel += "</HEAD>";
Excel += "<body>";
//表頭(國內國外一樣)
Excel += "<div align=center><b><font style='font-size:18pt;font-family:標楷體'>" + listtitle + "</font></b></div>";
Excel += "<table border=1 style='border-collapse:collapse;border:none;mso-border-alt:solid' borderColorDark=#000000 borderColorLight=#000000 cellpadding=6 cellspacing=0 width=100% align=center style='font-size:12pt;font-family:標楷體'>";
Excel += "<tr>";
Excel += "<td width=50>姓名</td>";
Excel += "<td colspan=5>" + empname + " </td>";
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td>出差事由</td>";
Excel += "<td colspan=5 width=500>" + blogcomment + " </td>";
Excel += "</tr>";
Excel += "</table>";
//國內(blogaddr=1)國外(blogaddr<>1)不同
sql = "select * from battadet where blogid=" + qblogid + " order by bddate";
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
rowcounter++;
if (blogaddr == "1")
{//國內
bdmonth += "<td>" + dr["bdmonth"] + " </td>";
bdday += "<td>" + dr["bdday"] + " </td>";
bdplace += "<td>" + dr["bdplace"] + " </td>";
bdwork += "<td>" + dr["bdwork"] + " </td>";
bdplane += "<td>" + dr["bdplane"] + " </td>";
bdcar += "<td>" + dr["bdcar"] + " </td>";
bdtrain += "<td>" + dr["bdtrain"] + " </td>";
bdship += "<td>" + dr["bdship"] + " </td>";
bdliving1 += "<td>" + dr["bdliving1"] + " </td>";
bdliving2 += "<td>" + dr["bdliving2"] + " </td>";
bdother += "<td>" + dr["bdother"] + " </td>";
bdbillno += "<td>" + dr["bdbillno"] + " </td>";
bdcomment += "<td>" + dr["bdcomment"] + " </td>";
dsum = Convert.ToDouble(dr["bdplane"]) + Convert.ToDouble(dr["bdcar"]) + Convert.ToDouble(dr["bdtrain"]) + Convert.ToDouble(dr["bdship"]) + Convert.ToDouble(dr["bdliving1"]) + Convert.ToDouble(dr["bdliving2"]) + Convert.ToDouble(dr["bdother"]);
strsum += "<td>" + dsum.ToString() + " </td>";
ttlmoney += dsum;
}
else
{//國外
bdmonth += "<td>" + dr["bdmonth"] + " </td>";
bdday += "<td>" + dr["bdday"] + " </td>";
bdplace += "<td>" + dr["bdplace"] + " </td>";
bdwork += "<td>" + dr["bdwork"] + " </td>";
bdplane += "<td>" + dr["bdplane"] + " </td>";
bdship += "<td>" + dr["bdship"] + " </td>";
bland += "<td>" + dr["bland"] + " </td>";
blive += "<td>" + dr["blive"] + " </td>";
bvisa += "<td>" + dr["bvisa"] + " </td>";
binsurance += "<td>" + dr["binsurance"] + " </td>";
badmin += "<td>" + dr["badmin"] + " </td>";
bgift += "<td>" + dr["bgift"] + " </td>";
bdother += "<td>" + dr["bdother"] + " </td>";
bdbillno += "<td>" + dr["bdbillno"] + " </td>";
bdcomment += "<td>" + dr["bdcomment"] + " </td>";
dsum = Convert.ToDouble(dr["bdplane"]) + Convert.ToDouble(dr["bdship"]) + Convert.ToDouble(dr["bland"]) + Convert.ToDouble(dr["blive"]) + Convert.ToDouble(exrate);
dsum += Convert.ToDouble(dr["bvisa"]) + Convert.ToDouble(dr["binsurance"]) + Convert.ToDouble(dr["badmin"]) + Convert.ToDouble(dr["bgift"]) + Convert.ToDouble(dr["bdother"]);
strsum += "<td>" + dsum.ToString() + " </td>";
ttlmoney += dsum;
}
}
dr.Close();
}
}
int emptyrow = 5 - rowcounter;
if (blogaddr == "1")
{
Excel += "<table border=1 style='border-collapse:collapse;border:none;mso-border-alt:solid' borderColorDark=#000000 borderColorLight=#000000 cellpadding=6 cellspacing=0 width=100% align=center style='font-size:12pt;font-family:標楷體'><tr align=center>";
Excel += "<tr align=center>";
Excel += "<td colspan=" + (rowcounter + 2) + ">中華民國 " + (blogsdate.Year - 1911) + " 年 " + blogsdate.Month.ToString() + " 月 " + blogsdate.Day.ToString() + " 日起中華民國 " + (blogedate.Year - 1911) + " 年 " + blogedate.Month.ToString() + " 月 " + blogedate.Day.ToString() + " 日止共計 " + diffday + " 日附單據 " + bbillcount + " 張</td>";
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td colspan=2 align=center>月</td>";
Excel += bdmonth + add_emptytd(emptyrow);
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td colspan=2 align=center>日</td>";
Excel += bdday + add_emptytd(emptyrow);
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td colspan=2 align=center>起訖地點</td>";
Excel += bdplace + add_emptytd(emptyrow);
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td colspan=2 align=center>工作記要</td>";
Excel += bdwork + add_emptytd(emptyrow);
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td rowspan=4 width=10%>交通費</td>";
Excel += "<td width=15%>飛機及高鐵</td>";
Excel += bdplane + add_emptytd(emptyrow);
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td>汽車及捷運</td>";
Excel += bdcar + add_emptytd(emptyrow);
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td>火車</td>";
Excel += bdtrain + add_emptytd(emptyrow);
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td>輪船</td>";
Excel += bdship + add_emptytd(emptyrow);
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td colspan=2 >住宿費</td>";
Excel += bdliving1 + add_emptytd(emptyrow);
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td colspan=2 >住宿費加計交通費<br>(旅行業代收轉付)</td>";
Excel += bdliving2 + add_emptytd(emptyrow);
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td colspan=2 >膳雜費</td>";
Excel += bdother + add_emptytd(emptyrow);
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td colspan=2 >單據號數</td>";
Excel += bdbillno + add_emptytd(emptyrow);
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td colspan=2 >小計</td>";
Excel += strsum + add_emptytd(emptyrow);
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td colspan=2 >總計</td>";
Excel += "<td colspan=5 >" + ttlmoney + "</td>";
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td colspan=2 >備註</td>";
Excel += bdcomment + add_emptytd(emptyrow);
Excel += "</tr>";
Excel += "</table>";
Excel += "<p>";
Excel += "<table width=100% style='font-size:12pt;font-family:標楷體'>";
Excel += "<tr>";
Excel += "<td valign=top>出差人</td>";
Excel += "<td>單位<br>主管</td>";
Excel += "<td>主辦人<br>事人員</td>";
Excel += "<td>主辦會<br>計人員</td>";
Excel += "<td>機關首長或<br>授權代簽人</td>";
Excel += "</tr>";
Excel += "</table>";
}
else
{
Excel += "<table border=1 style='border-collapse:collapse;border:none;mso-border-alt:solid' borderColorDark=#000000 borderColorLight=#000000 cellpadding=6 cellspacing=0 width=100% align=center style='font-size:12pt;font-family:標楷體'><tr align=center>";
Excel += "<tr align=center>";
Excel += "<td colspan=" + (rowcounter + 2) + ">中華民國 " + (blogsdate.Year - 1911) + " 年 " + blogsdate.Month.ToString() + " 月 " + blogsdate.Day.ToString() + " 日起中華民國 " + (blogedate.Year - 1911) + " 年 " + blogedate.Month.ToString() + " 月 " + blogedate.Day.ToString() + " 日止共計 " + diffday + " 日附單據 " + bbillcount + " 張</td>";
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td colspan=2 align=center>月</td>";
Excel += bdmonth + add_emptytd(emptyrow);
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td colspan=2 align=center>日</td>";
Excel += bdday + add_emptytd(emptyrow);
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td colspan=2 align=center>起訖地點</td>";
Excel += bdplace + add_emptytd(emptyrow);
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td colspan=2 align=center>工作記要</td>";
Excel += bdwork + add_emptytd(emptyrow);
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td rowspan=3 width=10%>交通費</td>";
Excel += "<td width=15%>飛機</td>";
Excel += bdplane + add_emptytd(emptyrow);
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td>船舶</td>";
Excel += bdship + add_emptytd(emptyrow);
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td>長途大眾<Br>陸運工具</td>";
Excel += bland + add_emptytd(emptyrow);
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td colspan=2>生活費(US$)</td>";
Excel += blive + add_emptytd(emptyrow);
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td rowspan=5 width=10%>辦公費</td>";
Excel += "<td width=15%>手續費</td>";
Excel += bvisa + add_emptytd(emptyrow);
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td>保險費</td>";
Excel += binsurance + add_emptytd(emptyrow);
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td>行政費</td>";
Excel += badmin + add_emptytd(emptyrow);
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td>禮品及<br>交際費</td>";
Excel += bgift + add_emptytd(emptyrow);
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td>雜費</td>";
Excel += bdother + add_emptytd(emptyrow);
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td colspan=2>單據號數</td>";
Excel += bdbillno + add_emptytd(emptyrow);
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td colspan=2>小計</td>";
Excel += strsum + add_emptytd(emptyrow);
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td colspan=2 >總計</td>";
Excel += "<td colspan=5 >" + ttlmoney + "</td>";
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td colspan=2 >備註</td>";
Excel += bdcomment + add_emptytd(emptyrow);
Excel += "</tr>";
Excel += "</table>";
Excel += "<p>";
Excel += "<table width=100% style='font-size:12pt;font-family:標楷體'>";
Excel += "<tr>";
Excel += "<td valign=top>出差人</td>";
Excel += "<td>單位<br>主管</td>";
Excel += "<td>主辦人<br>事人員</td>";
Excel += "<td>主辦會<br>計人員</td>";
Excel += "<td>機關首長或<br>授權代簽人</td>";
Excel += "</tr>";
Excel += "</table>";
Excel += "<br>";
Excel += "<table width=80% style='font-size:12pt;font-family:標楷體' border=0 align=center>";
Excel += "<tr>";
Excel += "<td valign=top width=50%>茲 收 到</td>";
Excel += "<td width=50%></td>";
Excel += "</tr>";
Excel += "<tr>";
Excel += "<td>出差旅費新台幣 " + ttlmoney + "元整</td>";
Excel += "<td>具領人 (簽章)</td>";
Excel += "</tr>";
Excel += "</table>";
}
#endregion
Excel += "</table>";
Excel += "</body>";
Excel += "</HTML>";
ViewBag.Excel = Excel;
return(View());
}
public ActionResult menugo(string id)
{
Aitag.Models.NDcommon dbobj = new Aitag.Models.NDcommon();
SqlConnection comconn = dbobj.get_conn("Aitag_DBContext");
string prog = dbobj.get_dbvalue(comconn, "select location from menutab where mtid = '" + id + "'");
Session["mtid"] = id;
string sql = "SELECT sublevel1.* FROM privtb LEFT OUTER JOIN sublevel1 ON privtb.sid = sublevel1.sid where sublevel1.mtid = '" + id + "' and privtb.bid='" + Session["empid"] + "' and privtb.chk='1' order by sublevel1.lid , sublevel1.corder , sublevel1.sid";
SqlCommand cmd = new SqlCommand();
cmd.Connection = comconn;
cmd.CommandText = sql;
SqlDataReader tmprs = cmd.ExecuteReader();
if (tmprs.Read())
{
string location = tmprs["location"].ToString().Trim();
string p1 = tmprs["uplink"].ToString().Trim();
string p2 = tmprs["sid"].ToString().Trim();
if (location.IndexOf('#') >= 0)
{
sql = "SELECT sublevel1.location,sublevel1.sid FROM privtb LEFT OUTER JOIN sublevel1 ON privtb.sid = sublevel1.sid where sublevel1.uplink = '" + tmprs["sid"].ToString() + "' and sublevel1.lid='3' and privtb.bid='" + Session["empid"].ToString() + "' and privtb.chk='1' order by sublevel1.corder , sublevel1.sid";
tmprs.Close();
tmprs.Dispose();
cmd.CommandText = sql;
tmprs = cmd.ExecuteReader();
if (tmprs.Read())
{
location = tmprs["location"].ToString().Trim();
p1 = p2;
p2 = tmprs["sid"].ToString().Trim();
}
tmprs.Close();
tmprs.Dispose();
}
else
{
tmprs.Close();
tmprs.Dispose();
}
if (location.IndexOf('?') >= 0)
{
ViewBag.js = @"location.href='" + location + "';";
return(View());
}
else
{
ViewBag.js = @"location.href='" + location + "?sid=" + p1 + "&realsid=" + p2 + "';";
return(View());
}
}
else
{
return(new ContentResult()
{
Content = @"<script>alert('您並無此選單的使用權限!!');window.history.go(-1);</script>"
});
}
comconn.Close();
comconn.Dispose();
}
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 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());
}
//[HttpGet]
//public ActionResult Add()
//{
// ViewBag.Ifboss = Session["Ifboss"].ToString();
// ViewBag.billid = Session["billid"].ToString();
// billflow col = new billflow();
// return View(col);
//}
//[HttpPost]
public ActionResult add(billflow col, string sysflag, int?page, string orderdata, string orderdata1)
{
ModelState.Clear();
page = ((!page.HasValue || page < 1) ? 1 : page);
ViewBag.page = page;
if (string.IsNullOrWhiteSpace(orderdata))
{
orderdata = "billid";
}
if (string.IsNullOrWhiteSpace(orderdata1))
{
orderdata1 = "desc";
}
ViewBag.orderdata = orderdata;
ViewBag.orderdata1 = orderdata1;
string qbillid = "", qcomclass = "";
if (!string.IsNullOrWhiteSpace(Request["qbillid"]))
{
qbillid = Request["qbillid"].Trim();
ViewBag.qbillid = qbillid;
}
if (!string.IsNullOrWhiteSpace(Request["qcomclass"]))
{
qcomclass = Request["qcomclass"].Trim();
ViewBag.qcomclass = qcomclass;
}
if (sysflag != "A")
{
billflow newcol = new billflow();
return(View(newcol));
}
else
{
if (!ModelState.IsValid)
{
return(View(col));
}
else
{
Aitag.Models.NDcommon dbobj = new Aitag.Models.NDcommon();
//SqlConnection conn = dbobj.get_conn("Aitag_DBContext");
//SqlDataReader dr;
//SqlCommand sqlsmd = new SqlCommand();
//sqlsmd.Connection = conn;
//string sqlstr = "select billid from billflow where 1<>1";
//sqlsmd.CommandText = sqlstr;
//dr = sqlsmd.ExecuteReader();
//if (dr.Read())
//{
// //ModelState.AddModelError("", "權限代碼重複!");
// return View(col);
//}
//dr.Close();
//dr.Dispose();
//sqlsmd.Dispose();
//conn.Close();
//conn.Dispose();
col.billtype = Request["billtype"];
col.addr = Request["addr"];
col.bmodid = Session["tempid"].ToString();
col.bmoddate = DateTime.Now;
using (Aitag_DBContext con = new Aitag_DBContext())
{
con.billflow.Add(col);
con.SaveChanges();
//系統LOG檔 //================================================= //
SqlConnection sysconn = dbobj.get_conn("Aitag_DBContext");
string sysrealsid = Request["sysrealsid"].ToString();
string syssubname = dbobj.get_sysmenuname(sysconn, sysrealsid, "2");
string tmpbillidname = dbobj.get_dbvalue(sysconn, "select doctitle from docgroup where docid ='" + Request["billid"].Trim() + "' and comid='" + Session["comid"] + "'");
string flowcheck = "";
if (!string.IsNullOrWhiteSpace(Request["flowcheck"].Trim()))
{
string sqlstr1 = "select roletitle from roleplay where rid in (" + Request["flowcheck"].Trim() + ")";
SqlCommand cmd = new SqlCommand();
cmd.Connection = sysconn;
cmd.CommandText = sqlstr1;
SqlDataReader dr1 = cmd.ExecuteReader();
while (dr1.Read())
{
flowcheck = flowcheck + dr1["roletitle"].ToString() + " → ";
}
if (flowcheck != "")
{
flowcheck = flowcheck.Substring(0, flowcheck.Length - 2);
}
dr1.Close();
dr1.Dispose();
}
string sysnote = "呈核單類別:" + tmpbillidname + "<br>呈核人員:" + flowcheck;
dbobj.systemlog(sysconn, syssubname, sysnote, Session["tempid"].ToString(), Session["sldate"].ToString(), Session["sfip"].ToString(), sysflag);
sysconn.Close();
sysconn.Dispose();
//=================================================
}
string tmpform = "";
tmpform = "<body onload=qfr1.submit();>";
tmpform += "<form name='qfr1' action='/billflow/List' method='post'>";
tmpform += "<input type=hidden name='sysflag' id='sysflag' value='" + sysflag + "'>";
tmpform += "<input type=hidden name='page' id='page' value='" + page + "'>";
tmpform += "<input type=hidden name='orderdata' id='orderdata' value='" + orderdata + "'>";
tmpform += "<input type=hidden name='orderdata1' id='orderdata1' value='" + orderdata1 + "'>";
tmpform += "<input type=hidden id='qbillid' name='qbillid' value='" + qbillid + "'>";
tmpform += "<input type=hidden id='qcomclass' name='qcomclass' value='" + qcomclass + "'>";
tmpform += "</form>";
tmpform += "</body>";
return(new ContentResult()
{
Content = @"" + tmpform
});
// return RedirectToAction("List");
}
}
}
//[HttpGet]
//public ActionResult Add()
//{
// ViewBag.Ifboss = Session["Ifboss"].ToString();
// ViewBag.empdid = Session["empdid"].ToString();
// empfunction col = new empfunction();
// return View(col);
//}
//[HttpPost]
public ActionResult add(empfunction col, string sysflag, int?page, string orderdata, string orderdata1, string test)
{
ModelState.Clear();
page = ((!page.HasValue || page < 1) ? 1 : page);
ViewBag.page = page;
if (string.IsNullOrWhiteSpace(orderdata))
{
orderdata = "empdid";
}
if (string.IsNullOrWhiteSpace(orderdata1))
{
orderdata1 = "desc";
}
ViewBag.orderdata = orderdata;
ViewBag.orderdata1 = orderdata1;
string qempdid = "", funid = "";
if (!string.IsNullOrWhiteSpace(Request["qempdid"]))
{
qempdid = Request["qempdid"].Trim();
ViewBag.qempdid = qempdid;
}
if (!string.IsNullOrWhiteSpace(Request["funid"]))
{
funid = Request["funid"].Trim();
ViewBag.funid = funid;
}
if (sysflag != "A")
{
empfunction newcol = new empfunction();
return(View(newcol));
}
else
{
if (!ModelState.IsValid)
{
return(View(col));
}
else
{
Aitag.Models.NDcommon dbobj = new Aitag.Models.NDcommon();
SqlConnection conn = dbobj.get_conn("Aitag_DBContext");
// SqlDataReader dr;
SqlCommand sqlsmd = new SqlCommand();
sqlsmd.Connection = conn;
#region 註解
//string sqlstr = "select empdid from empfunction where empdid = '" + col.empdid + "'";
//sqlsmd.CommandText = sqlstr;
//dr = sqlsmd.ExecuteReader();
//if (dr.Read())
//{
// ModelState.AddModelError("", "權限代碼重複!");
// return View(col);
//}
//dr.Close();
//dr.Dispose();
//sqlsmd.Dispose();
//conn.Close();
//conn.Dispose();
#endregion
//密碼加密
//col.emppasswd = dbobj.Encrypt(col.emppasswd);
//col.empdid = col.emppasswd;
//col.baddid = Session["tempid"].ToString();
col.bmodid = Session["tempid"].ToString();
col.empid = "99999999";
//col.badddate = DateTime.Now;
col.bmoddate = DateTime.Now;
using (Aitag_DBContext con = new Aitag_DBContext())
{
con.empfunction.Add(col);
con.SaveChanges();
//系統LOG檔 //================================================= //
SqlConnection sysconn = dbobj.get_conn("Aitag_DBContext");
string sysrealsid = Request["sysrealsid"].ToString();
string syssubname = dbobj.get_sysmenuname(sysconn, sysrealsid, "2");
string dbdata = dbobj.get_dbvalue(sysconn, "select chkitem from checkcode where chkclass='08' and chkcode='" + col.funid + "'");
string sysnote = "共用首頁設定:" + dbdata + "的資料";
dbobj.systemlog(sysconn, syssubname, sysnote, Session["tempid"].ToString(), Session["sldate"].ToString(), Session["sfip"].ToString(), sysflag);
sysconn.Close();
sysconn.Dispose();
//=================================================
}
string tmpform = "";
tmpform = "<body onload=qfr1.submit();>";
tmpform += "<form name='qfr1' action='/empfunction/List' method='post'>";
tmpform += "<input type=hidden name='sysflag' id='sysflag' value='" + sysflag + "'>";
tmpform += "<input type=hidden name='page' id='page' value='" + page + "'>";
tmpform += "<input type=hidden name='orderdata' id='orderdata' value='" + orderdata + "'>";
tmpform += "<input type=hidden name='orderdata1' id='orderdata1' value='" + orderdata1 + "'>";
tmpform += "<input type=hidden id='qempdid' name='qempdid' value='" + qempdid + "'>";
tmpform += "<input type=hidden id='funid' name='funid' value='" + funid + "'>";
tmpform += "</form>";
tmpform += "</body>";
return(new ContentResult()
{
Content = @"" + tmpform
});
// return RedirectToAction("List");
}
}
}
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());
}
public ActionResult ascaddrbookcsv(int?page, string orderdata, string orderdata1)
{
page = ((!page.HasValue || page < 1) ? 1 : page);
ViewBag.page = page;
if (string.IsNullOrWhiteSpace(orderdata))
{
orderdata = "empname";
}
if (string.IsNullOrWhiteSpace(orderdata1))
{
orderdata1 = "desc";
}
ViewBag.orderdata = orderdata;
ViewBag.orderdata1 = orderdata1;
string qempname = "", qdptid = "";
if (!string.IsNullOrWhiteSpace(Request["qempname"]))
{
qempname = Request["qempname"].Trim();
ViewBag.qempname = qempname;
}
if (!string.IsNullOrWhiteSpace(Request["qdptid"]))
{
qdptid = Request["qdptid"].Trim();
ViewBag.qdptid = qdptid;
}
//IPagedList<employee> result;
string Excel = "", Excel2 = "";
string sqlstr = "";
using (Aitag_DBContext con = new Aitag_DBContext())
{
sqlstr = "select * from employee where (ifuse <> 'n' or ifuse is null) and emptype <> '3' and empstatus <> '4' and empworkcomp = '" + (string)(Session["comid"]) + "' and";
if (qempname != "")
{
sqlstr += " (empname like N'%" + qempname + "%' or empenname like N'%" + qempname + "%') and";
}
if (qdptid != "")
{
sqlstr += " empworkdepid ='" + qdptid + "' and";
}
sqlstr = sqlstr.Substring(0, sqlstr.Length - 5);
sqlstr += " order by " + orderdata + " " + orderdata1;
//var query = con.employee.SqlQuery(sqlstr).AsQueryable();
//result = query.ToPagedList<employee>(page.Value - 1, (int)Session["pagesize"]);
}
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 += "<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 += "<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>";
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 tmpname = "";
while (dr.Read())
{
tmpname = dbobj.get_dbvalue(conn, "select dpttitle from Department where dptid = '" + dr["empworkdepid"] + "' and comid='" + dr["empworkcomp"] + "'");
Excel2 += "<tr>";
Excel2 += "<td> </td>";
Excel2 += "<td> </td>";
Excel2 += "<td> </td>";
Excel2 += "<td>" + dr["empname"] + "</td>";
Excel2 += "<td> </td>";
Excel2 += "<td>" + dr["enemail"] + " </td>";
Excel2 += "<td>" + dr["enaddress"] + " </td>";
Excel2 += "<td> </td>";
Excel2 += "<td> </td>";
Excel2 += "<td> </td>";
Excel2 += "<td> </td>";
Excel2 += "<td>" + dr["entel"] + " </td>";
Excel2 += "<td> </td>";
Excel2 += "<td>" + dr["enmob"] + " </td>";
Excel2 += "<td> </td>";
Excel2 += "<td> </td>";
Excel2 += "<td> </td>";
Excel2 += "<td> </td>";
Excel2 += "<td> </td>";
Excel2 += "<td> </td>";
Excel2 += "<td> </td>";
Excel2 += "<td> </td>";
Excel2 += "<td> </td>";
Excel2 += "<td> </td>";
Excel2 += "<td> </td>";
Excel2 += "<td>" + tmpname + " </td>";
Excel2 += "<td> </td>";
Excel2 += "<td> </td>";
Excel2 += "<td> </td>";
Excel2 += "</tr>";
}
if (Excel2 == "")
{
Excel += "<tr align=left><td colspan=7>目前沒有資料</td></tr>";
}
else
{
Excel += Excel2;
}
dr.Close();
}
}
Excel += "</table>";
Excel += "</body>";
Excel += "</HTML>";
ViewBag.Excel = Excel;
return(View());
}