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