public ActionResult List(vList obj) { HYCEntities model = new HYCEntities(); var i2 = model.AS_WORKTIME.Where(x => x.AS_USERID == obj.EmployeeName && x.AS_DATE.ToString().Substring(0, 6) == obj.StartYYMM).Select(y => y); var i3 = model.AS_WORKTIME.Where(x => x.AS_USERID == obj.EmployeeName && x.AS_DATE.ToString().Substring(0, 6) == obj.StartYYMM).Sum(y => y.AS_HOURS); double result = Convert.ToInt32(i3); double Dd; double Hh; double Mm; if (result > 1440) { Dd = Math.Floor(result / 1440); Hh = Math.Floor((result / 60) % 24); Mm = Math.Floor(result % 60); } else { Dd = 0; Hh = Math.Floor(result / 60); Mm = result % 60; } List <vEmployeeWorktime> items = new List <vEmployeeWorktime>(); vEmployeeWorktime newItem = new vEmployeeWorktime(); newItem.ASS_SN = 0; newItem.AS_USERID = ""; newItem.AS_DATE = 0; newItem.AS_STIME = 0; newItem.AS_ETIME = 0; newItem.AS_HOURS = 0; newItem.SHOWTIME = Convert.ToString(Dd) + '天' + Convert.ToString(Hh) + '時' + Convert.ToString(Mm) + '分'; items.Add(newItem); foreach (var item in i2) { string oShowTime; item.AS_HOURS = calHours(item.AS_DATE, item.AS_ETIME, item.AS_STIME, out oShowTime); newItem = new vEmployeeWorktime(); newItem.ASS_SN = item.ASS_SN; newItem.AS_USERID = item.AS_USERID; newItem.AS_DATE = item.AS_DATE; newItem.AS_STIME = item.AS_STIME; newItem.AS_ETIME = item.AS_ETIME; newItem.AS_HOURS = item.AS_HOURS; newItem.SHOWTIME = oShowTime; items.Add(newItem); } return(View("Index", items)); }
public List <T> GetEmployeeListSummary <T>(string EmployeeName) { using (var context = new HYCEntities()) { string sql = @"select (select as_name from as_member m where m.AS_PSN = ws.as_psn) as as_psn ,ws.as_workmonth as [as_workmonth] --,s.as_psn ,cast(ws.as_totaltime as varchar)+'分' as as_totaltime ,cast(ws.as_basetime as varchar)+'分' as as_basetime ,cast(ws.as_timegap as varchar)+'分' as as_timegap ,(case when ws.as_timegap<0 then ('本月少'+RIGHT('0'+cast(cast((-ws.as_timegap /60)as decimal) as varchar),2) +'時' + RIGHT('0'+cast(cast(-ws.as_timegap as decimal)% 60 as varchar),2)+'分鐘' ) else ('本月多'+RIGHT('0'+cast(cast((ws.as_timegap /60)as decimal) as varchar),2) +'時' + RIGHT('0'+cast(cast(ws.as_timegap as decimal)% 60 as varchar),2)+'分鐘' ) end) as [conversion] from (select w.as_psn ,substring(cast(as_date as varchar),1,6) as as_workmonth ,sum(w.as_hours) as as_totaltime ,22*9*60 as as_basetime,cast(sum(w.as_hours)-(22*9*60) as int) as as_timegap from as_worktime w where w.AS_USERID=@EmployeeName group by w.AS_PSN,substring(cast(as_date as varchar),1,6)) ws order by ws.as_psn,ws.as_workmonth"; string sql2 = @"select (select as_name from as_member m where m.AS_PSN = ws.as_psn) as as_psn ,ws.as_workmonth as [month] --,s.as_psn ,cast(ws.as_totaltime as varchar)+'分' as totalhours ,cast(ws.as_basetime as varchar)+'分' as basichours ,cast(ws.as_timegap as varchar)+'分' as overhours ,RIGHT('0' + cast(ws.as_timegap/1440 as varchar),2) + '天' + RIGHT('0'+cast(cast((ws.as_timegap /60) as decimal) % 24 as varchar),2) +'時' + RIGHT('0'+cast(cast(ws.as_timegap as decimal)% 60 as varchar),2)+'分鐘' as [conversion] from (select w.as_psn ,substring(cast(as_date as varchar),1,6) as as_workmonth ,sum(w.as_hours) as as_totaltime ,22*9*60 as as_basetime,sum(w.as_hours)-(22*9*60) as as_timegap from as_worktime w where w.AS_USERID=@EmployeeName group by w.AS_PSN,substring(cast(as_date as varchar),1,6)) ws order by ws.as_psn,ws.as_workmonth"; return(context.Database.SqlQuery <T>(sql, (new SqlParameter[] { new SqlParameter("@EmployeeName", EmployeeName != null ? (object)EmployeeName : DBNull.Value) }).ToArray()).ToList()); } }
// GET: Home public ActionResult Index() { HYCEntities model = new HYCEntities(); List <vEmployeeWorktime> items = new List <vEmployeeWorktime>(); foreach (var item in model.AS_WORKTIME) { string oShowTime; item.AS_HOURS = calHours(item.AS_DATE, item.AS_ETIME, item.AS_STIME, out oShowTime); vEmployeeWorktime newItem = new vEmployeeWorktime(); newItem.ASS_SN = item.ASS_SN; newItem.AS_USERID = item.AS_USERID; newItem.AS_DATE = item.AS_DATE; newItem.AS_STIME = item.AS_STIME; newItem.AS_ETIME = item.AS_ETIME; newItem.AS_HOURS = item.AS_HOURS; //SAVE DATA newItem.SHOWTIME = oShowTime; //VIEW DATA items.Add(newItem); } model.SaveChanges(); return(View(items)); }