public ActionResult GetData()
{
try
{
string date = Request["date"];
date = date.Split('/')[2] + "/" + date.Split('/')[1] + "/" + date.Split('/')[0];
DateTime selectedDate = DateTime.Parse(date);
int soLuotHuyDong = 0;
int vuTaiNan = 0;
int nghiVLD = 0;
int hetHanChungChi = 0;
int tren82 = 0;
int duoi82 = 0;
List <NghiVLD> listNghiVLD = new List <NghiVLD>();
List <NhanLuc> listNhanLuc = new List <NhanLuc>();
SanLuong sanluong = new SanLuong();
using (QuangHanhManufacturingEntities db = new QuangHanhManufacturingEntities())
{
////////////////////////////GET so luot huy dong////////////////////////////////
db.Configuration.LazyLoadingEnabled = false;
string sql = "select (case when count(MaQuyetDinh) is null then 0 else count(MaQuyetDinh) end ) as SoLuotHuyDong from quyetdinh\n" +
"where maquyetdinh in\n" +
"(SELECT distinct dd.MaQuyetDinh FROM DIEUDONG_NHANVIEN dd,QuyetDinh qd where dd.MaQuyetDinh=qd.MaQuyetDinh and qd.SoQuyetDinh<>'' )\n" +
"AND NgayQuyetDinh = @NgayQuyetDinh";
try
{
soLuotHuyDong = db.Database.SqlQuery <int>(sql,
new SqlParameter("NgayQuyetDinh", selectedDate)).ToList <int>()[0];
}
catch (Exception e)
{
}
////////////////////////////GET SO LUONG TAI NAN//////////////////////////////
sql = "select (case when Count(tn.MaNV) is null then 0 else Count(tn.MaNV) end ) from \n" +
"(select MaNV, Ngay from TaiNan where\n" +
"Ngay = @NgayQuyetDinh) as tn";
try
{
vuTaiNan = db.Database.SqlQuery <int>(sql,
new SqlParameter("NgayQuyetDinh", selectedDate)).ToList <int>()[0];
}
catch (Exception e)
{
}
//////////////////////////////////////////////////////////////////////////////
/// ////////////////////////////GET SO LUONG HET HAN CC//////////////////////////////
sql = "select (case when sum(th.st) is null then 0 else sum(th.st) end ) \n" +
"from(select cn.MaNV, cn.NgayCap, cc.ThoiHan, (case\n" +
"when DATEADD(MONTH, cc.ThoiHan, cn.NgayCap) <= @NgayQuyetDinh\n" +
"then 1 else 0 end) as st\n" +
"from ChungChi_NhanVien cn join ChungChi cc on cn.MaChungChi = cc.MaChungChi) as th";
try
{
hetHanChungChi = db.Database.SqlQuery <int>(sql, new SqlParameter("NgayQuyetDinh", selectedDate)).ToList <int>()[0];
}
catch (Exception e)
{
}
//////////////////////////////////////////////////////////////////////////////
/// ////////////////////////////GET SO LUONG NGHI VLD//////////////////////////////
sql = @"select
sum(case when b.LyDoVangMat = N'Vô lý do' then 1 else 0 end) 'SoLuongNhanVien'
from
(select Min(HeaderID) as 'HeaderID' from Header_DiemDanh_NangSuat_LaoDong
where NgayDiemDanh = @NgayDiemDanh) as a
join DiemDanh_NangSuatLaoDong b on a.HeaderID = b.HeaderID";
try
{
nghiVLD = db.Database.SqlQuery <int>(sql,
new SqlParameter("NgayDiemDanh", selectedDate)).ToList <int>()[0];
}
catch (Exception e)
{
}
//////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////GET TI LE HUY DONG////////////////////////////////////////
try
{
sql = @"select a.department_id, a.QL, (a.KT + a.CD) as 'Tong', a.KT, a.CD, 0 as 'HSTT',
a.dilam,
(a.vld + a.om + a.khac + a.phep) as 'vang',
a.vld ,a.om ,a.phep ,a.khac,
(case when (a.KT+ a.CD) = 0 then 0 else round(Convert(float, a.dilam)/(a.KT + a.CD - a.tong_nghidai)*100,1) end) as 'tile',
b.than, b.metlo, b.xen,b.diemluong,
(case when a.dilam = 0 then 0 else round(Convert(float,(b.diemluong / a.dilam)),1) end) as 'tlbq_diemluong',
(case when a.dilam = 0 then 0 else round(Convert(float,(b.than / a.dilam)),1) end) as 'nsld_thuchien',
(case when a.dilam = 0 then 0 else round(Convert(float,0),1) end) as 'nsld_kehoach',
a.tong_nghidai,a.nghidai_om_tnld,a.nghidai_thhd,a.nghidai_vld
from
(select
a.department_id,
(case when a.QL is null then 0 else a.QL end) as 'QL',
(case when a.KT is null then 0 else a.KT end) as 'KT',
(case when a.CD is null then 0 else a.CD end) as 'CD',
(case when b.dilam is null then 0 else b.dilam end) as 'dilam',
(case when b.vld is null then 0 else b.vld end) as 'vld',
(case when b.om is null then 0 else b.om end) as 'om',
(case when b.phep is null then 0 else b.phep end) as 'phep',
(case when b.khac is null then 0 else b.khac end) as 'khac',
(case when b.tong_nghidai is null then 0 else b.tong_nghidai end) as 'tong_nghidai',
(case when b.nghidai_vld is null then 0 else b.nghidai_vld end) as 'nghidai_vld',
(case when b.nghidai_thhd is null then 0 else b.nghidai_thhd end) as 'nghidai_thhd',
(case when b.nghidai_om_tnld is null then 0 else b.nghidai_om_tnld end) as 'nghidai_om_tnld'
from
(select a.department_id,
sum(case when ncv.LoaiNhomCongViec = N'CBQL' then 1 else 0 end) as QL,
sum(case when ncv.LoaiNhomCongViec = N'CNKT' then 1 else 0 end) as KT,
sum(case when ncv.LoaiNhomCongViec = N'CNCĐ' then 1 else 0 end) as CD
from Department a left outer join NhanVien n on n.MaPhongBan = a.department_id
join CongViec_NhomCongViec cn on n.MaCongViec = cn.MaCongViec
join NhomCongViec ncv on cn.MaNhomCongViec = ncv.MaNhomCongViec
where a.department_type like N'%chính%' and (a.department_id like N'%ĐL%' or a.department_id like N'%VTL%' or a.department_id like N'%KT%')
group by a.department_id) as a
left outer join
(select
dilam.MaPhongBan,
(case when dilam.dilam is null then 0 else dilam.dilam end) as 'dilam',
(case when nghi.vld is null then 0 else nghi.vld end) as 'vld',
(case when nghi.om is null then 0 else nghi.om end) as 'om',
(case when nghi.phep is null then 0 else nghi.phep end) as 'phep',
(case when nghi.khac is null then 0 else nghi.khac end) as 'khac',
(case when nghi.tong_nghidai is null then 0 else nghi.tong_nghidai end) as 'tong_nghidai',
(case when nghi.nghidai_vld is null then 0 else nghi.nghidai_vld end) as 'nghidai_vld',
(case when nghi.nghidai_thhd is null then 0 else nghi.nghidai_thhd end) as 'nghidai_thhd',
(case when nghi.nghidai_om_tnld is null then 0 else nghi.nghidai_om_tnld end) as 'nghidai_om_tnld'
from
(select
dilam.MaPhongBan,
count(dilam.MaNV) as 'dilam'
from
(select
hdd.MaPhongBan,
nv_dd.MaNV
from
(select Min(HeaderID) as 'HeaderID', NgayDiemDanh, Ca from Header_DiemDanh_NangSuat_LaoDong
where NgayDiemDanh = @NgayDiemDanh and Status = 1
group by NgayDiemDanh, Ca) as hd
join Header_DiemDanh_NangSuat_LaoDong_Detail hdd on hd.HeaderID = hdd.HeaderID
join
(select dd.*, nv.MaPhongBan
from DiemDanh_NangSuatLaoDong dd
join NhanVien nv on dd.MaNV = nv.MaNV) as nv_dd on nv_dd.HeaderID = hd.HeaderID and nv_dd.MaPhongBan = hdd.MaPhongBan
where nv_dd.DiLam = 1
group by hdd.MaPhongBan, nv_dd.MaNV) as dilam
group by dilam.MaPhongBan) as dilam
left outer join
(select
nghi.MaPhongBan,
sum(case when nghi.LyDoVangMat like N'Vô lý do' then 1 else 0 end) as 'vld',
sum(case when nghi.LyDoVangMat like N'Ốm' then 1 else 0 end) as 'om',
sum(case when nghi.LyDoVangMat like N'Nghỉ phép' then 1 else 0 end) as 'phep',
sum(case when nghi.LyDoVangMat like N'Khác' then 1 else 0 end) as 'khac',
sum(case when nghi.LyDoVangMat in (N'Tai nạn lao động',N'Ốm dài',N'Tạm hoãn lao động',N'Vô lý do dài') then 1 else 0 end) as 'tong_nghidai',
sum(case when nghi.LyDoVangMat in (N'Vô lý do dài') then 1 else 0 end) as 'nghidai_vld',
sum(case when nghi.LyDoVangMat in (N'Tạm hoãn lao động') then 1 else 0 end) as 'nghidai_thhd',
sum(case when nghi.LyDoVangMat in (N'Ốm dài', N'Tai nạn lao động') then 1 else 0 end) as 'nghidai_om_tnld'
from
(select
hdd.MaPhongBan,
nv_dd.MaNV,
nv_dd.LyDoVangMat
from
(select Min(HeaderID) as 'HeaderID', NgayDiemDanh, Ca from Header_DiemDanh_NangSuat_LaoDong
where NgayDiemDanh = @NgayDiemDanh and Status = 1
group by NgayDiemDanh, Ca) as hd
join Header_DiemDanh_NangSuat_LaoDong_Detail hdd on hd.HeaderID = hdd.HeaderID
join
(select dd.*, nv.MaPhongBan
from DiemDanh_NangSuatLaoDong dd
join NhanVien nv on dd.MaNV = nv.MaNV) as nv_dd on nv_dd.HeaderID = hd.HeaderID and nv_dd.MaPhongBan = hdd.MaPhongBan
where nv_dd.DiLam = 0
group by hdd.MaPhongBan, nv_dd.MaNV, nv_dd.LyDoVangMat) as nghi
group by nghi.MaPhongBan) as nghi on dilam.MaPhongBan = nghi.MaPhongBan) as b
on a.department_id = b.MaPhongBan) as a
inner join
(select a.department_id,
sum(case when h.ThanThucHien is not null and h.NgayDiemDanh = @NgayDiemDanh then h.ThanThucHien else 0 end) as 'than',
sum(case when h.MetLoThucHien is not null and h.NgayDiemDanh = @NgayDiemDanh then h.MetLoThucHien else 0 end) as 'metlo',
sum(case when h.XenThucHien is not null and h.NgayDiemDanh = @NgayDiemDanh then h.XenThucHien else 0 end) as 'xen',
sum(case when h.TotalEffort is not null and h.NgayDiemDanh = @NgayDiemDanh then h.TotalEffort else 0 end) as 'diemluong'
from Department a
left outer join
(select hd.NgayDiemDanh, hdd.MaPhongBan, hdd.ThanThucHien, hdd.MetLoThucHien, hdd.XenThucHien, hdd.TotalEffort
from
(select Min(HeaderID) as 'HeaderID', NgayDiemDanh from Header_DiemDanh_NangSuat_LaoDong
where NgayDiemDanh = @NgayDiemDanh
group by NgayDiemDanh) as hd
join Header_DiemDanh_NangSuat_LaoDong_Detail hdd on hd.HeaderID = hdd.HeaderID ) as h
on a.department_id = h.MaPhongBan
group by a.department_id) as b on a.department_id = b.department_id";
List <BaoCaoNgayDB> listTLHD = db.Database.SqlQuery <BaoCaoNgayDB>(sql, new SqlParameter("NgayDiemDanh", selectedDate)).ToList();
for (int i = 0; i < listTLHD.Count; i++)
{
if (listTLHD[i].tile >= 82)
{
tren82++;
}
else
{
duoi82++;
}
}
}
catch (Exception e)
{
}
////////////////////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////GET NV NGHI VLD////////////////////////////////////////
sql = @"select
n.MaNV,
n.Ten as HoTen,
dep.department_name as TenDonVi
from
(select Min(HeaderID) as 'HeaderID' from Header_DiemDanh_NangSuat_LaoDong
where NgayDiemDanh = @NgayDiemDanh) as hd
inner join DiemDanh_NangSuatLaoDong d on hd.HeaderID = d.HeaderID and d.LyDoVangMat like N'Vô lý do'
inner join NhanVien n on d.MaNV = n.MaNV
inner join Department dep on dep.department_id = n.MaPhongBan";
try
{
listNghiVLD = db.Database.SqlQuery <NghiVLD>(sql, new SqlParameter("NgayDiemDanh", selectedDate)).ToList <NghiVLD>();
}
catch (Exception e)
{
}
///////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////GET DATA NHAN LUC////////////////////////////////////////////////
sql = @"select tb1.department_id as MaDonVi,
(case when tb2.soluong is null then 0 else tb2.soluong end) as SoLuong
from
(select * from Department where department_id in
('KT1', 'KT2', 'KT3', 'KT4', 'KT5', 'KT6',
'KT7', 'KT8', 'KT9', 'KT10', 'KT11', 'ĐL3',
'ĐL5', 'ĐL7', 'ĐL8', 'VTL1', 'VTL2')) tb1
left join
(select nv.MaPhongBan, count(dd.MaNV) 'SoLuong' from
(select Min(HeaderID) as 'HeaderID' from Header_DiemDanh_NangSuat_LaoDong
where NgayDiemDanh = @NgayDiemDanh) as hd
join DiemDanh_NangSuatLaoDong dd on hd.HeaderID = dd.HeaderID and dd.DiLam = 1
join NhanVien nv on nv.MaNV = dd.MaNV
group by nv.MaPhongBan) as tb2
on tb1.department_id = tb2.MaPhongBan
group by tb1.department_id,tb2.soluong";
try
{
listNhanLuc = db.Database.SqlQuery <NhanLuc>(sql, new SqlParameter("NgayDiemDanh", selectedDate)).ToList <NhanLuc>();
}
catch (Exception e)
{
}
///////////////////////////////////////GET DATA SAN LUONG///////////////////////////////////////////////
sql = @"select
convert(float, 0, 2) as 'SLKH',
convert(float, 0, 2) as 'MLKH',
convert(float, sum(case when hdd.ThanThucHien is NULL then 0 else hdd.ThanThucHien end),2) as 'LKSL',
convert(float, sum(case when hdd.MetLoThucHien is NULL then 0 else hdd.MetLoThucHien end),2) as 'LKML'
from
(select Min(HeaderID) 'HeaderID'
from Header_DiemDanh_NangSuat_LaoDong
where NgayDiemDanh = @NgayDiemDanh
group by NgayDiemDanh, Ca) as hd
join Header_DiemDanh_NangSuat_LaoDong_Detail hdd on hd.HeaderID = hdd.HeaderID";
try
{
sanluong = db.Database.SqlQuery <SanLuong>(sql,
new SqlParameter("NgayDiemDanh", selectedDate)).ToList <SanLuong>()[0];
}
catch (Exception e)
{
}
}
return(Json(new { success = true, tren82 = tren82, duoi82 = duoi82, soLuongHuyDong = soLuotHuyDong, vuTaiNan = vuTaiNan, nghiVLD = nghiVLD, hetHanChungChi = hetHanChungChi, listNghiVLD = listNghiVLD, listNhanLuc = listNhanLuc, sanluong = sanluong }, JsonRequestBehavior.AllowGet));
}
catch (Exception e)
{
return(Json(new { success = false, message = "Lỗi" }, JsonRequestBehavior.AllowGet));
}
}
public ActionResult Dashboard()
{
int soLuotHuyDong = 0;
int vuTaiNan = 0;
int nghiVLD = 0;
int hetHanChungChi = 0;
int tren82 = 0;
int duoi82 = 0;
List <NghiVLD> listNghiVLD = new List <NghiVLD>();
List <NhanLuc> listNhanLuc = new List <NhanLuc>();
SanLuong sanluong = new SanLuong();
int temp = 0;
using (QuangHanhManufacturingEntities db = new QuangHanhManufacturingEntities())
{
db.Configuration.LazyLoadingEnabled = false;
////////////////////////////GET so luot huy dong////////////////////////////////
string sql = "select (case when count(MaQuyetDinh) is null then 0 else count(MaQuyetDinh) end ) as SoLuotHuyDong from quyetdinh\n" +
"where maquyetdinh in\n" +
"(SELECT distinct dd.MaQuyetDinh FROM DIEUDONG_NHANVIEN dd,QuyetDinh qd where dd.MaQuyetDinh=qd.MaQuyetDinh and qd.SoQuyetDinh<>'' )\n" +
"AND NgayQuyetDinh = (SELECT CONVERT(VARCHAR(10), getdate() - 1, 101))";
try
{
soLuotHuyDong = db.Database.SqlQuery <int>(sql).ToList <int>()[0];
}
catch (Exception e)
{
e.Message.ToString();
}
////////////////////////////GET SO LUONG TAI NAN///////////////////////////////////////////
sql = "select (case when Count(tn.MaNV) is null then 0 else Count(tn.MaNV) end ) from \n" +
"(select MaNV, Ngay from TaiNan where\n" +
"Ngay = (SELECT CONVERT(VARCHAR(10), getdate() - 1, 101))) as tn";
try
{
temp = db.Database.SqlQuery <int>(sql).ToList <int>()[0];
vuTaiNan = temp != null ? temp : 0;
}
catch (Exception e)
{
}
///////////////////////////////GET SO LUONG HET HAN CC///////////////////////////////////////////
sql = "select (case when sum(th.st) is null then 0 else sum(th.st) end ) \n" +
"from(select cn.MaNV, cn.NgayCap, cc.ThoiHan, (case\n" +
"when DATEADD(MONTH, cc.ThoiHan, cn.NgayCap) <= GETDATE()\n" +
"then 1 else 0 end) as st\n" +
"from ChungChi_NhanVien cn join ChungChi cc on cn.MaChungChi = cc.MaChungChi) as th";
try
{
temp = db.Database.SqlQuery <int>(sql).ToList <int>()[0];
hetHanChungChi = temp != null ? temp : 0;
}
catch (Exception e)
{
}
////////////////////////////GET SO LUONG NGHI VLD///////////////////////////////////////////
sql = @"select
sum(case when b.LyDoVangMat = N'Vô lý do' then 1 else 0 end) 'SoLuongNhanVien'
from
(select Min(HeaderID) as 'HeaderID' from Header_DiemDanh_NangSuat_LaoDong
where NgayDiemDanh = (SELECT CONVERT(VARCHAR(10), getdate() - 1, 101))) as a
join DiemDanh_NangSuatLaoDong b on a.HeaderID = b.HeaderID";
try
{
temp = db.Database.SqlQuery <int>(sql).ToList <int>()[0];
nghiVLD = temp != null ? temp : 0;
}
catch (Exception e)
{
}
////////////////////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////GET TI LE HUY DONG////////////////////////////////////////
DateTime currentDate = DateTime.Now.Date.AddDays(-1);
int currentMonth = currentDate.Month;
try
{
sql = @"select a.department_id, a.QL, (a.KT + a.CD) as 'Tong', a.KT, a.CD, 0 as 'HSTT',
a.dilam,
(a.vld + a.om + a.khac + a.phep) as 'vang',
a.vld ,a.om ,a.phep ,a.khac,
(case when (a.KT+ a.CD) = 0 then 0 else round(Convert(float, a.dilam)/(a.KT + a.CD - a.tong_nghidai)*100,1) end) as 'tile',
b.than, b.metlo, b.xen,b.diemluong,
(case when a.dilam = 0 then 0 else round(Convert(float,(b.diemluong / a.dilam)),1) end) as 'tlbq_diemluong',
(case when a.dilam = 0 then 0 else round(Convert(float,(b.than / a.dilam)),1) end) as 'nsld_thuchien',
(case when a.dilam = 0 then 0 else round(Convert(float,0),1) end) as 'nsld_kehoach',
a.tong_nghidai,a.nghidai_om_tnld,a.nghidai_thhd,a.nghidai_vld
from
(select
a.department_id,
(case when a.QL is null then 0 else a.QL end) as 'QL',
(case when a.KT is null then 0 else a.KT end) as 'KT',
(case when a.CD is null then 0 else a.CD end) as 'CD',
(case when b.dilam is null then 0 else b.dilam end) as 'dilam',
(case when b.vld is null then 0 else b.vld end) as 'vld',
(case when b.om is null then 0 else b.om end) as 'om',
(case when b.phep is null then 0 else b.phep end) as 'phep',
(case when b.khac is null then 0 else b.khac end) as 'khac',
(case when b.tong_nghidai is null then 0 else b.tong_nghidai end) as 'tong_nghidai',
(case when b.nghidai_vld is null then 0 else b.nghidai_vld end) as 'nghidai_vld',
(case when b.nghidai_thhd is null then 0 else b.nghidai_thhd end) as 'nghidai_thhd',
(case when b.nghidai_om_tnld is null then 0 else b.nghidai_om_tnld end) as 'nghidai_om_tnld'
from
(select a.department_id,
sum(case when ncv.LoaiNhomCongViec = N'CBQL' then 1 else 0 end) as QL,
sum(case when ncv.LoaiNhomCongViec = N'CNKT' then 1 else 0 end) as KT,
sum(case when ncv.LoaiNhomCongViec = N'CNCĐ' then 1 else 0 end) as CD
from Department a left outer join NhanVien n on n.MaPhongBan = a.department_id
join CongViec_NhomCongViec cn on n.MaCongViec = cn.MaCongViec
join NhomCongViec ncv on cn.MaNhomCongViec = ncv.MaNhomCongViec
where a.department_type like N'%chính%' and (a.department_id like N'%ĐL%' or a.department_id like N'%VTL%' or a.department_id like N'%KT%')
group by a.department_id) as a
left outer join
(select
dilam.MaPhongBan,
(case when dilam.dilam is null then 0 else dilam.dilam end) as 'dilam',
(case when nghi.vld is null then 0 else nghi.vld end) as 'vld',
(case when nghi.om is null then 0 else nghi.om end) as 'om',
(case when nghi.phep is null then 0 else nghi.phep end) as 'phep',
(case when nghi.khac is null then 0 else nghi.khac end) as 'khac',
(case when nghi.tong_nghidai is null then 0 else nghi.tong_nghidai end) as 'tong_nghidai',
(case when nghi.nghidai_vld is null then 0 else nghi.nghidai_vld end) as 'nghidai_vld',
(case when nghi.nghidai_thhd is null then 0 else nghi.nghidai_thhd end) as 'nghidai_thhd',
(case when nghi.nghidai_om_tnld is null then 0 else nghi.nghidai_om_tnld end) as 'nghidai_om_tnld'
from
(select
dilam.MaPhongBan,
count(dilam.MaNV) as 'dilam'
from
(select
hdd.MaPhongBan,
nv_dd.MaNV
from
(select Min(HeaderID) as 'HeaderID', NgayDiemDanh, Ca from Header_DiemDanh_NangSuat_LaoDong
where NgayDiemDanh = @NgayDiemDanh and Status = 1
group by NgayDiemDanh, Ca) as hd
join Header_DiemDanh_NangSuat_LaoDong_Detail hdd on hd.HeaderID = hdd.HeaderID
join
(select dd.*, nv.MaPhongBan
from DiemDanh_NangSuatLaoDong dd
join NhanVien nv on dd.MaNV = nv.MaNV) as nv_dd on nv_dd.HeaderID = hd.HeaderID and nv_dd.MaPhongBan = hdd.MaPhongBan
where nv_dd.DiLam = 1
group by hdd.MaPhongBan, nv_dd.MaNV) as dilam
group by dilam.MaPhongBan) as dilam
left outer join
(select
nghi.MaPhongBan,
sum(case when nghi.LyDoVangMat like N'Vô lý do' then 1 else 0 end) as 'vld',
sum(case when nghi.LyDoVangMat like N'Ốm' then 1 else 0 end) as 'om',
sum(case when nghi.LyDoVangMat like N'Nghỉ phép' then 1 else 0 end) as 'phep',
sum(case when nghi.LyDoVangMat like N'Khác' then 1 else 0 end) as 'khac',
sum(case when nghi.LyDoVangMat in (N'Tai nạn lao động',N'Ốm dài',N'Tạm hoãn lao động',N'Vô lý do dài') then 1 else 0 end) as 'tong_nghidai',
sum(case when nghi.LyDoVangMat in (N'Vô lý do dài') then 1 else 0 end) as 'nghidai_vld',
sum(case when nghi.LyDoVangMat in (N'Tạm hoãn lao động') then 1 else 0 end) as 'nghidai_thhd',
sum(case when nghi.LyDoVangMat in (N'Ốm dài', N'Tai nạn lao động') then 1 else 0 end) as 'nghidai_om_tnld'
from
(select
hdd.MaPhongBan,
nv_dd.MaNV,
nv_dd.LyDoVangMat
from
(select Min(HeaderID) as 'HeaderID', NgayDiemDanh, Ca from Header_DiemDanh_NangSuat_LaoDong
where NgayDiemDanh = @NgayDiemDanh and Status = 1
group by NgayDiemDanh, Ca) as hd
join Header_DiemDanh_NangSuat_LaoDong_Detail hdd on hd.HeaderID = hdd.HeaderID
join
(select dd.*, nv.MaPhongBan
from DiemDanh_NangSuatLaoDong dd
join NhanVien nv on dd.MaNV = nv.MaNV) as nv_dd on nv_dd.HeaderID = hd.HeaderID and nv_dd.MaPhongBan = hdd.MaPhongBan
where nv_dd.DiLam = 0
group by hdd.MaPhongBan, nv_dd.MaNV, nv_dd.LyDoVangMat) as nghi
group by nghi.MaPhongBan) as nghi on dilam.MaPhongBan = nghi.MaPhongBan) as b
on a.department_id = b.MaPhongBan) as a
inner join
(select a.department_id,
sum(case when h.ThanThucHien is not null and h.NgayDiemDanh = @NgayDiemDanh then h.ThanThucHien else 0 end) as 'than',
sum(case when h.MetLoThucHien is not null and h.NgayDiemDanh = @NgayDiemDanh then h.MetLoThucHien else 0 end) as 'metlo',
sum(case when h.XenThucHien is not null and h.NgayDiemDanh = @NgayDiemDanh then h.XenThucHien else 0 end) as 'xen',
sum(case when h.TotalEffort is not null and h.NgayDiemDanh = @NgayDiemDanh then h.TotalEffort else 0 end) as 'diemluong'
from Department a
left outer join
(select hd.NgayDiemDanh, hdd.MaPhongBan, hdd.ThanThucHien, hdd.MetLoThucHien, hdd.XenThucHien, hdd.TotalEffort
from
(select Min(HeaderID) as 'HeaderID', NgayDiemDanh from Header_DiemDanh_NangSuat_LaoDong
where NgayDiemDanh = @NgayDiemDanh
group by NgayDiemDanh) as hd
join Header_DiemDanh_NangSuat_LaoDong_Detail hdd on hd.HeaderID = hdd.HeaderID ) as h
on a.department_id = h.MaPhongBan
group by a.department_id) as b on a.department_id = b.department_id";
List <BaoCaoNgayDB> listTLHD = db.Database.SqlQuery <BaoCaoNgayDB>(sql, new SqlParameter("NgayDiemDanh", currentDate)).ToList();
for (int i = 0; i < listTLHD.Count; i++)
{
if (listTLHD[i].tile > 82)
{
tren82++;
}
else
{
duoi82++;
}
}
}
catch (Exception e)
{
}
//////////////////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////GET NV NGHI VLD////////////////////////////////////////
sql = @"select
n.MaNV,
n.Ten as HoTen,
dep.department_name as TenDonVi
from
(select Min(HeaderID) as 'HeaderID' from Header_DiemDanh_NangSuat_LaoDong
where NgayDiemDanh = (SELECT CONVERT(VARCHAR(10), getdate() - 1, 101))) as hd
inner join DiemDanh_NangSuatLaoDong d on hd.HeaderID = d.HeaderID and d.LyDoVangMat like N'Vô lý do'
inner join NhanVien n on d.MaNV = n.MaNV
inner join Department dep on dep.department_id = n.MaPhongBan";
try
{
listNghiVLD = db.Database.SqlQuery <NghiVLD>(sql).ToList <NghiVLD>();
}
catch (Exception e)
{
}
/////////////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////GET DATA NHAN LUC////////////////////////////////////////////////
sql = @"select tb1.department_id as MaDonVi,
(case when tb2.soluong is null then 0 else tb2.soluong end) as SoLuong
from
(select * from Department where department_type = N'Phân xưởng sản xuất chính') tb1
left join
(select nv.MaPhongBan ,count(dd.MaNV) 'SoLuong' from
(select Min(HeaderID) as 'HeaderID' from Header_DiemDanh_NangSuat_LaoDong
where NgayDiemDanh = (SELECT CONVERT(VARCHAR(10), getdate() - 1, 101))) as hd
join DiemDanh_NangSuatLaoDong dd on hd.HeaderID = dd.HeaderID and dd.DiLam = 1
join NhanVien nv on nv.MaNV = dd.MaNV
group by nv.MaPhongBan) as tb2 on tb1.department_id = tb2.MaPhongBan
order by tb1.[index] asc" ;
try
{
listNhanLuc = db.Database.SqlQuery <NhanLuc>(sql).ToList <NhanLuc>();
}
catch (Exception e)
{
}
///////////////////////////////////////GET DATA SAN LUONG///////////////////////////////////////////////
sql = @"select
convert(float,0,2) as 'SLKH',
convert(float,0,2) as 'MLKH',
convert(float,sum(case when hdd.ThanThucHien is NULL then 0 else hdd.ThanThucHien end),2) as 'LKSL',
convert(float,sum(case when hdd.MetLoThucHien is NULL then 0 else hdd.MetLoThucHien end),2) as 'LKML'
from
(select Min(HeaderID) 'HeaderID'
from Header_DiemDanh_NangSuat_LaoDong
where NgayDiemDanh = @NgayDiemDanh
group by NgayDiemDanh, Ca) as hd
join Header_DiemDanh_NangSuat_LaoDong_Detail hdd on hd.HeaderID = hdd.HeaderID" ;
try
{
sanluong = db.Database.SqlQuery <SanLuong>(sql,
new SqlParameter("ThangDiemDanh", currentMonth),
new SqlParameter("NgayDiemDanh", currentDate)).FirstOrDefault();
}
catch (Exception e)
{
}
}
ViewBag.soLuotHuyDong = soLuotHuyDong;
ViewBag.hetHanChungChi = hetHanChungChi;
ViewBag.vuTaiNan = vuTaiNan;
ViewBag.nghiVLD = nghiVLD;
ViewBag.tren82 = tren82;
ViewBag.duoi82 = duoi82;
ViewBag.listNghiVLD = listNghiVLD;
ViewBag.listNhanLuc = listNhanLuc;
ViewBag.sanluong = sanluong;
return(View("/Views/BGD/Dashboard.cshtml"));
}
