public ActionResult EditCertificateEmp(string id)
{
using (QUANGHANHABCEntities db = new QUANGHANHABCEntities())
{
List <ChungChi> listdata_chungchi = db.ChungChis.ToList <ChungChi>();
List <NhanVien> listdata_nv = db.NhanViens.ToList <NhanVien>();
SelectList listCirtificate = new SelectList(listdata_chungchi, "MaChungChi", "TenChungChi");
SelectList listEmployee = new SelectList(listdata_nv, "MaNV", "MaNV");
ViewBag.List_chungchi = listCirtificate;
ViewBag.List_nhanvien = listEmployee;
var cirtificate_emp = db.ChungChi_NhanVien.Where(x => x.SoHieu == id).FirstOrDefault <ChungChi_NhanVien>();
if (cirtificate_emp != null)
{
var emp = db.NhanViens.Where(x => x.MaNV == cirtificate_emp.MaNV).FirstOrDefault <NhanVien>();
if (emp != null)
{
ViewBag.nameEmp = emp.Ten;
ViewBag.first_cir = cirtificate_emp.MaChungChi;
}
}
ChungChi_NhanVien chungchinv = db.ChungChi_NhanVien.Where(x => x.SoHieu == id).FirstOrDefault <ChungChi_NhanVien>();
return(View(chungchinv));
}
}
public ActionResult listChungChi()
{
QUANGHANHABCEntities db = new QUANGHANHABCEntities();
var chungchi = from cc_nv in db.ChungChi_NhanVien
join nv in db.NhanViens on cc_nv.MaNV equals nv.MaNV
join cc in db.ChungChis on cc_nv.MaChungChi equals cc.MaChungChi
where cc_nv.MaNV == id_
select new
{
maNV = nv.MaNV,
ten = nv.Ten,
soHieu = cc_nv.SoHieu,
ngayCap = cc_nv.NgayCap.ToString(),
maChungChi = cc_nv.MaChungChi,
ngayTra = cc_nv.NgayTra.ToString(),
tenChungChi = cc.TenChungChi
};
var dataJson = Json(new { success = true, data = chungchi }, JsonRequestBehavior.AllowGet);
string dataSerialize = new JavaScriptSerializer().Serialize(dataJson.Data);
return(dataJson);
}
public void Export()
{
string path = HostingEnvironment.MapPath("/excel/CDVT/download/su-co.xlsx");
FileInfo file = new FileInfo(path);
using (ExcelPackage excelPackage = new ExcelPackage(file))
{
ExcelWorkbook excelWorkbook = excelPackage.Workbook;
ExcelWorksheet excelWorksheet = excelWorkbook.Worksheets.First();
using (QUANGHANHABCEntities DBContext = new QUANGHANHABCEntities())
{
var incidents = DBContext.Database.SqlQuery <IncidentDB>("SELECT e.Equipment_category_id, e.*, i.*, d.department_name FROM Incident i inner join Equipment e on e.equipmentId = i.equipmentId inner join Department d on d.department_id = i.department_id inner join Equipment_category ec on e.Equipment_category_id = ec.Equipment_category_id").ToList();
int k = 0;
for (int i = 5; i < incidents.Count + 5; i++)
{
excelWorksheet.Cells[i, 1].Value = (k + 1);
excelWorksheet.Cells[i, 2].Value = incidents.ElementAt(k).Equipment_category_id;
excelWorksheet.Cells[i, 3].Value = incidents.ElementAt(k).equipment_name;
excelWorksheet.Cells[i, 4].Value = incidents.ElementAt(k).mark_code;
excelWorksheet.Cells[i, 5].Value = incidents.ElementAt(k).equipmentId;
excelWorksheet.Cells[i, 6].Value = incidents.ElementAt(k).fabrication_number;
excelWorksheet.Cells[i, 7].Value = incidents.ElementAt(k).detail_location;
excelWorksheet.Cells[i, 8].Value = incidents.ElementAt(k).department_name;
excelWorksheet.Cells[i, 9].Value = incidents.ElementAt(k).start_time.ToString("hh:mm tt dd/MM/yyyy");
excelWorksheet.Cells[i, 10].Value = incidents.ElementAt(k).getEndtime();
excelWorksheet.Cells[i, 11].Value = incidents.ElementAt(k).getDiffTime();
excelWorksheet.Cells[i, 12].Value = incidents.ElementAt(k).reason;
k++;
}
string location = HostingEnvironment.MapPath("/excel/CDVT/download");
excelPackage.SaveAs(new FileInfo(location + "/su-co_temp.xlsx"));
}
}
}
public ActionResult updateThongTinCoBan(String json)
{
dynamic js = JObject.Parse(json);
String hoVaTen = js.hoVaTen;
String sothe = js.sothe;
String ngaythangnamsinh = js.ngaythangnamsinh;
String donvicd = js.donvicd;
String soBH = js.soBH;
String sodt = js.sodt;
String diachithuongtru = js.diachithuongtru;
using (QUANGHANHABCEntities db = new QUANGHANHABCEntities())
{
NhanVien nv = (from p in db.NhanViens where p.MaNV == sothe select p).SingleOrDefault();
nv.Ten = hoVaTen;
nv.SoBHXH = soBH;
nv.SoDienThoai = sodt;
nv.NoiOHienTai = diachithuongtru;
if (isValidateDateTime(ngaythangnamsinh))
{
nv.NgaySinh = Convert.ToDateTime(ngaythangnamsinh);
}
ChamDut_NhanVien cd = (from p in db.ChamDut_NhanVien where p.MaNV == sothe select p).SingleOrDefault();
// cd.DonViKhiChamDut = donvicd;
db.SaveChanges();
ViewBag.nameDepartment = "quanlyhoso";
return(Json(new { success = true, draw = Request["draw"] }, JsonRequestBehavior.AllowGet));
}
}
public ActionResult updateLoaiChamDut(String json)
{
dynamic js = JObject.Parse(json);
String tenLoaiChamDut = js.tenLoaiChamDut;
String soQD = js.soQD;
String ngayQD = js.ngayQD;
String ngayCD = js.ngayCD;
int soQD1 = Int32.Parse(soQD.Trim());
using (QUANGHANHABCEntities db = new QUANGHANHABCEntities())
{
ChamDut_NhanVien nv = (from p in db.ChamDut_NhanVien where p.MaQuyetDinh == soQD1 select p).SingleOrDefault();
nv.LoaiChamDut = tenLoaiChamDut;
if (isValidateDateTime(ngayCD))
{
nv.NgayChamDut = Convert.ToDateTime(ngayCD);
}
QuyetDinh cd = (from p in db.QuyetDinhs where p.SoQuyetDinh == soQD select p).SingleOrDefault();
if (isValidateDateTime(ngayQD))
{
cd.NgayQuyetDinh = Convert.ToDateTime(ngayQD);
}
db.SaveChanges();
ViewBag.nameDepartment = "quanlyhoso";
return(Json(new { success = true, draw = Request["draw"] }, JsonRequestBehavior.AllowGet));
}
}
public ActionResult XacNhanThemMoi(string data)
{
dynamic dataJson = JObject.Parse(data);
var maNV = dataJson.mnv;
var maCC = dataJson.mcc;
var ngayCap = DateTime.ParseExact(Convert.ToString(dataJson.ngaycap), "dd/MM/yyyy", CultureInfo.InvariantCulture)
.ToString("MM/dd/yyyy", CultureInfo.InvariantCulture);
var ngayTra = DateTime.ParseExact(Convert.ToString(dataJson.ngaytra), "dd/MM/yyyy", CultureInfo.InvariantCulture)
.ToString("MM/dd/yyyy", CultureInfo.InvariantCulture);
var sohieu = dataJson.sohieu + "";
using (QUANGHANHABCEntities db = new QUANGHANHABCEntities())
{
try
{
ChungChi_NhanVien ccnv = new ChungChi_NhanVien {
MaNV = maNV, MaChungChi = maCC, NgayCap = Convert.ToDateTime(ngayCap), NgayTra = Convert.ToDateTime(ngayTra), SoHieu = sohieu
};
if (ccnv.SoHieu != null && !ccnv.SoHieu.Equals(""))
{
db.ChungChi_NhanVien.Add(ccnv);
db.SaveChanges();
return(Json(new { success = true }, JsonRequestBehavior.AllowGet));
}
else
{
return(Json(new { success = false }, JsonRequestBehavior.AllowGet));
}
}
catch (Exception)
{
return(Json(new { success = false }, JsonRequestBehavior.AllowGet));
}
}
}
private void getContentbyDay(DateTime date)
{
using (QUANGHANHABCEntities db = new QUANGHANHABCEntities())
{
if (db.Fuel_activities_consumption.Where(a => a.fuel_type == "XANG").Where(a => a.date == date).ToList().Count() == 0)
{
ViewBag.Xang = 0;
}
else
{
ViewBag.Xang = db.Fuel_activities_consumption.Where(a => a.fuel_type == "XANG").Where(a => a.date == date).Sum(a => a.consumption_value);
}
if (db.Fuel_activities_consumption.Where(a => a.fuel_type == "DAU").Where(a => a.date == date).ToList().Count() == 0)
{
ViewBag.DauNhuHoa = 0;
}
else
{
ViewBag.DauNhuHoa = db.Fuel_activities_consumption.Where(a => a.fuel_type == "DAU").Where(a => a.date == date).Sum(a => a.consumption_value);
}
if (db.Fuel_activities_consumption.Where(a => a.fuel_type == "DAUMO").Where(a => a.date == date).ToList().Count() == 0)
{
ViewBag.DauMo = 0;
}
else
{
ViewBag.DauMo = db.Fuel_activities_consumption.Where(a => a.fuel_type == "DAUMO").Where(a => a.date == date).Sum(a => a.consumption_value);
}
}
}
public ActionResult listHoSo(string id)
{
QUANGHANHABCEntities db = new QUANGHANHABCEntities();
var HoSoByMaNV = from hs in db.HoSoes
join nv in db.NhanViens on hs.MaNV equals nv.MaNV
where nv.MaNV == id_
select new
{
maNV = hs.MaNV,
ten = nv.Ten,
nguoiGiuHoSo = hs.NguoiGiuHoSo,
ngayNhanHoSo = hs.NgayNhanHoSo.ToString(),
nguoiGiaoHoSo = hs.NguoiGiaoHoSo,
camKetTuyenDung = hs.CamKetTuyenDung,
quyetDinhTiepNhan = hs.QuyetDinhTiepNhanDVC,
nguoiBanGiaoBangNhapKho = hs.NguoiBanGiaoBangNhapKho
};
var dataJson = Json(new { success = true, data = HoSoByMaNV });
string dataSerialize = new JavaScriptSerializer().Serialize(dataJson.Data);
return(dataJson);
}
public ActionResult chiTietbangCap()
{
QUANGHANHABCEntities db = new QUANGHANHABCEntities();
var chiTietBangCapByMaNV = from ctbc in db.ChiTiet_BangCap_GiayChungNhan
join nv in db.NhanViens on ctbc.MaNV equals nv.MaNV
join bc in db.BangCap_GiayChungNhan on ctbc.MaBangCap_GiayChungNhan equals bc.MaBangCap_GiayChungNhan
join truong in db.Truongs on bc.MaTruong equals truong.MaTruong
where
ctbc.MaNV == id_
select new
{
maNV = nv.MaNV,
ten = nv.Ten,
soHieu = ctbc.SoHieu,
maBangCap = ctbc.MaBangCap_GiayChungNhan,
ngayCap = ctbc.NgayCap.ToString(),
loai = bc.Loai,
truong = truong.TenTruong
};
var dataJson = Json(new { success = true, data = chiTietBangCapByMaNV });
string dataSerialize = new JavaScriptSerializer().Serialize(dataJson.Data);
return(dataJson);
}
public ActionResult GetAllData()
{
//Server Side Parameter
int start = Convert.ToInt32(Request["start"]);
int length = Convert.ToInt32(Request["length"]);
string searchValue = Request["search[value]"];
string sortColumnName = Request["columns[" + Request["order[0][column]"] + "][name]"];
string sortDirection = Request["order[0][dir]"];
QUANGHANHABCEntities DBContext = new QUANGHANHABCEntities();
List <IncidentDB> incidents = DBContext.Database.SqlQuery <IncidentDB>("SELECT e.equipment_name, d.department_name, i.*, DATEDIFF(HOUR, i.start_time, i.end_time) as time_different FROM Incident i inner join Equipment e on e.equipmentId = i.equipmentId inner join Department d " +
"on d.department_id = i.department_id").ToList();
int totalrows = incidents.Count;
int totalrowsafterfiltering = incidents.Count;
//sorting
incidents = incidents.OrderBy(sortColumnName + " " + sortDirection).ToList <IncidentDB>();
//paging
incidents = incidents.Skip(start).Take(length).ToList <IncidentDB>();
foreach (IncidentDB item in incidents)
{
item.stringStartTime = item.start_time.ToString("hh:mm tt dd/MM/yyyy");
item.stringEndTime = item.getEndtime();
item.stringDiffTime = item.getDiffTime();
if (item.time_different.ToString() == "")
{
item.editAble = item.incident_id + "^false";
}
else
{
item.editAble = item.incident_id + "^true";
}
}
return(Json(new { success = true, data = incidents, draw = Request["draw"], recordsTotal = totalrows, recordsFiltered = totalrowsafterfiltering }, JsonRequestBehavior.AllowGet));
}
public ActionResult ABC(string id)
{
QUANGHANHABCEntities DBContext = new QUANGHANHABCEntities();
var years = DBContext.Database.SqlQuery <int>("SELECT distinct year(i.start_time) as years FROM Incident i inner join Equipment e on e.equipmentId = i.equipmentId inner join Department d on d.department_id = i.department_id where i.end_time is not null and e.equipmentId = '" + id + "' order by years desc").ToList();
List <IncidentByYear> listbyyear = new List <IncidentByYear>();
foreach (int year in years)
{
int count = 0;
IncidentByYear tempyear = new IncidentByYear();
List <IncidentByDate> listbydate = new List <IncidentByDate>();
List <string> listtemp = DBContext.Database.SqlQuery <string>("SELECT distinct CONCAT(YEAR(i.start_time),'/',MONTH(i.start_time),'/',DAY(i.start_time)) as dates FROM Incident i inner join Equipment e on e.equipmentId = i.equipmentId inner join Department d on d.department_id = i.department_id where i.end_time is not null and e.equipmentId = '" + id + "' and year(start_time) = '" + year + "' order by dates desc").ToList();
List <DateTime> dates = listtemp.Select(date => DateTime.Parse(date)).ToList <DateTime>();
foreach (DateTime date in dates)
{
IncidentByDate tempdate = new IncidentByDate();
tempdate.date = date;
tempdate.incidents = DBContext.Database.SqlQuery <IncidentDB>("SELECT d.department_name, " +
"i.* FROM Incident i inner join Equipment e on e.equipmentId = i.equipmentId inner join Department d " +
"on d.department_id = i.department_id where i.end_time is not null and e.equipmentId = '" + id + "' and DAY(start_time) = '" + date.Day + "' and MONTH(start_time) = '" + date.Month + "' and year(start_time) = '" + year + "' order by start_time desc").ToList();
count += tempdate.incidents.Count;
listbydate.Add(tempdate);
}
tempyear.year = year;
tempyear.IncidentByDates = listbydate;
tempyear.count = count;
listbyyear.Add(tempyear);
}
ViewBag.incidents = listbyyear;
var equipment = DBContext.Database.SqlQuery <Equipment>("SELECT * FROM Equipment WHERE Equipment.equipmentId = '" + id + "'").First();
ViewBag.equipment = equipment;
return(View("/Views/CDVT/Thietbi/Lylich.cshtml"));
}
public ActionResult updateAttendance()
{
var listUpdateJSON = Request["sessionId"];
var listUpdate = JsonConvert.DeserializeObject <List <updateStatus> >(listUpdateJSON);
using (var transaction = new TransactionScope())
{
using (QUANGHANHABCEntities db = new QUANGHANHABCEntities())
{
foreach (var item in listUpdate)
{
DiemDanh_NangSuatLaoDong dn = new DiemDanh_NangSuatLaoDong();
dn.MaDiemDanh = Int32.Parse(item.maDD);
dn.MaNV = item.maNV;
dn.DiLam = item.status;
//if (item.timeAttendance != "")
//{
// dn.ThoiGianThucTeDiemDanh = DateTime.ParseExact(item.timeAttendance, "M/d/yyyy hh:mm:ss", null);
//}
dn.MaDonVi = item.maDV;
dn.LyDoVangMat = item.reason;
dn.GhiChu = item.description;
dn.CaDiemDanh = 1;
dn.NgayDiemDanh = Convert.ToDateTime("2019-09-10");
db.Entry(dn).State = EntityState.Modified;
}
db.SaveChanges();
transaction.Complete();
}
}
return(Json(new { success = true, data = listUpdateJSON }, JsonRequestBehavior.AllowGet));
}
public ActionResult takeAttendance()
{
// fixxing
var departmentID = "DL1";
var dateAtt = Convert.ToDateTime("2019-09-10");
int ca = 1;
using (QUANGHANHABCEntities db = new QUANGHANHABCEntities())
{
db.Configuration.LazyLoadingEnabled = false;
var listAttendance = (from emp in db.NhanViens
join per in db.DiemDanh_NangSuatLaoDong
.Where(per => per.MaDonVi == departmentID && per.NgayDiemDanh == dateAtt && per.CaDiemDanh == ca)
on emp.MaNV equals per.MaNV into attendance
from att in attendance.DefaultIfEmpty()
select new
{
maNV = emp.MaNV,
maDD = (int?)att.MaDiemDanh,
maDV = att.MaDonVi,
tenNV = emp.Ten,
status = att.DiLam,
timeAttendance = att.ThoiGianThucTeDiemDanh,
dateAttendance = att.NgayDiemDanh,
reason = att.LyDoVangMat,
description = att.GhiChu
}).OrderBy(att => att.status).ToList();
JsonSerializerSettings jss = new JsonSerializerSettings {
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
};
var result = JsonConvert.SerializeObject(listAttendance, Formatting.Indented, jss);
return(Json(new { success = true, data = result }, JsonRequestBehavior.AllowGet));
}
}
public ActionResult Edit(string id)
{
List <Acceptance> AcceptanceList = new List <Acceptance>();
using (QUANGHANHABCEntities db = new QUANGHANHABCEntities())
{
Boolean status = true;
db.Configuration.LazyLoadingEnabled = false;
try
{
var query = " UPDATE Acceptance SET acceptance_date = getdate(), equipmentStatus = 3 where equipmentId = '" + id + "'";
db.Database.ExecuteSqlCommand(query);
}
catch (Exception e)
{
}
db.SaveChanges();
AcceptanceList = db.Acceptances.ToList <Acceptance>();
var acceptance = db.Acceptances.ToList <Acceptance>();
foreach (Acceptance items in AcceptanceList)
{
if (items.equipmentStatus != 3 && items.equipmentId == id)
{
status = false;
break;
}
}
if (status)
{
ChangeStatus(id);
}
return(View());
//return RedirectToAction("Search");
}
}
public ActionResult GetAllNhanVienByPX()
{
int start = Convert.ToInt32(Request["start"]);
int length = Convert.ToInt32(Request["length"]);
string searchValue = Request["search[value]"];
string sortColumnName = Request["columns[" + Request["order[0][column]"] + "][name]"];
string sortDirection = Request["order[0][dir]"];
using (QUANGHANHABCEntities db = new QUANGHANHABCEntities())
{
var temp = (from px in db.Departments
join
nv in db.NhanViens
on px.department_id equals nv.MaPhongBan
select new
{
TenNhanVien = nv.Ten,
MaNhanVien = nv.MaNV
});
List <NhanVien> arrNhanVien = temp.ToList().Select(p => new NhanVien {
Ten = p.TenNhanVien, MaNV = p.MaNhanVien
}).ToList();
IOrderedEnumerable <NhanVien> arrnvorder = arrNhanVien.OrderBy(n => n.MaNV);
int totalrows = arrNhanVien.Count;
int totalrowsafterfiltering = arrNhanVien.Count;
arrNhanVien = arrNhanVien.OrderBy(sortColumnName + " " + sortDirection).ToList <NhanVien>();
arrNhanVien = arrNhanVien.Skip(start).Take(length).ToList <NhanVien>();
return(Json(new { success = true, data = arrNhanVien, draw = Request["draw"], recordsTotal = totalrows, recordsFiltered = totalrowsafterfiltering }, JsonRequestBehavior.AllowGet));
}
}
public ActionResult searchlistAllBrief(string searchList)
{
using (QUANGHANHABCEntities db = new QUANGHANHABCEntities())
{
db.Configuration.LazyLoadingEnabled = false;
int start = Convert.ToInt32(Request["start"]);
int length = Convert.ToInt32(Request["length"]);
string searchValue = Request["search[value]"];
string sortColumnName = Request["columns[" + Request["order[0][column]"] + "][name]"];
string sortDirection = Request["order[0][dir]"];
List <HoSoNhanVien> hs_nv = new List <HoSoNhanVien>();
string[] idsArray = searchList.Split(',').ToArray();
var manv = idsArray[0];
var tennv = idsArray[1];
var nguoigiaohoso = idsArray[2];
var nguoigiuhoso = idsArray[3];
if (manv != null || tennv != null && nguoigiaohoso != null && nguoigiuhoso != null)
{
hs_nv = (from nv in db.NhanViens
join hs in db.HoSoes
on nv.MaNV equals hs.MaNV
where (hs.TrangThaiHoSo != "hồ sơ ngoài") &&
((nv.MaNV + " ").Contains(manv)) &&
((nv.Ten + " ").Contains(tennv)) &&
((hs.NguoiGiaoHoSo + " ").Contains(nguoigiaohoso)) &&
((hs.NguoiGiuHoSo + " ").Contains(nguoigiuhoso))
select new
{
maNV = hs.MaNV,
ten = nv.Ten,
ngaysinh = nv.NgaySinh,
nguoiGiaoHoSo = hs.NguoiGiaoHoSo,
ngayNhanHoSo = hs.NgayNhanHoSo,
nguoiGiuHoSo = hs.NguoiGiuHoSo
}).ToList().Select(p => new HoSoNhanVien
{
MaNV = p.maNV,
Ten = p.ten,
NgaySinh = p.ngaysinh.ToString(),
NguoiGiaoHoSo = p.nguoiGiaoHoSo,
NgayNhanHoSo = p.ngayNhanHoSo.ToString(),
NguoiGiuHoSo = p.nguoiGiuHoSo
}).ToList();
int totalrows = hs_nv.Count;
int totalrowsafterfiltering = hs_nv.Count;
hs_nv = hs_nv.OrderBy(sortColumnName + " " + sortDirection).ToList <HoSoNhanVien>();
//paging
hs_nv = hs_nv.Skip(start).Take(length).ToList <HoSoNhanVien>();
var dataJson = Json(new { success = true, data = hs_nv, draw = Request["draw"], recordsTotal = totalrows, recordsFiltered = totalrowsafterfiltering }, JsonRequestBehavior.AllowGet);
string dataSerialize = new JavaScriptSerializer().Serialize(dataJson.Data);
return(dataJson);
}
}
return(RedirectToAction("listAllHoSo"));
}
public ActionResult getThemMoiPopUpInfo(string maNV, int maCC)
{
using (QUANGHANHABCEntities db = new QUANGHANHABCEntities())
{
var temp = (from ccnv in db.ChungChi_NhanVien
join
nv in db.NhanViens on ccnv.MaNV equals nv.MaNV
join cc in db.ChungChis
on ccnv.MaChungChi equals cc.MaChungChi
where nv.MaNV.Equals(maNV) && ccnv.MaChungChi == maCC
select new
{
TenNhanVien = nv.Ten,
TenChungChi = cc.TenChungChi,
SoHieu = ccnv.SoHieu,
MaNhanVien = nv.MaNV,
MaChungChi = cc.MaChungChi
}).ToList();
List <ChungChi_NhanVien_Model> dt = temp.Select(p => new ChungChi_NhanVien_Model
{
TenNV = p.TenNhanVien,
TenChungChi = p.TenChungChi,
SoHieu = p.SoHieu
,
MaNV = p.MaNhanVien,
MaChungChi = p.MaChungChi
}).ToList();
return(Json(new { success = true, data = dt }, JsonRequestBehavior.AllowGet));
}
}
public ActionResult taiThongTinCoBanHSNgoai()
{
String mnv = Request.QueryString["manv"];
using (QUANGHANHABCEntities db = new QUANGHANHABCEntities())
{
var mydata = (from p in db.NhanViens
join p1 in db.HoSoes on p.MaNV equals p1.MaNV
join p2 in db.ChamDut_NhanVien on p1.MaNV equals p2.MaNV
join p3 in db.Departments on p.MaPhongBan equals p3.department_id
where p.MaNV == mnv
select new
{
stt = "",
sothe = p.MaNV,
hoVaTen = p.Ten,
ngaythangnamsinh = p.NgaySinh,
donvicd = p3.department_name,
soBH = p.SoBHXH,
sodt = p.SoDienThoai,
diachithuongtru = p.NoiOHienTai,
edit = true
}).ToList();
return(Json(new { success = true, data = mydata, draw = Request["draw"] }, JsonRequestBehavior.AllowGet));
}
}
private void getContentbyMonth(int month, int year)
{
using (QUANGHANHABCEntities db = new QUANGHANHABCEntities())
{
if (db.Fuel_activities_consumption.Where(a => a.fuel_type == "XANG").Where(a => a.date.Month == month && a.date.Year == year).ToList().Count() == 0)
{
ViewBag.Xang = 0;
}
else
{
ViewBag.Xang = db.Fuel_activities_consumption.Where(a => a.fuel_type == "XANG").Where(a => a.date.Month == month && a.date.Year == year).Sum(a => a.consumption_value);
}
if (db.Fuel_activities_consumption.Where(a => a.fuel_type == "DAU").Where(a => a.date.Month == month && a.date.Year == year).ToList().Count() == 0)
{
ViewBag.DauNhuHoa = 0;
}
else
{
ViewBag.DauNhuHoa = db.Fuel_activities_consumption.Where(a => a.fuel_type == "DAU").Where(a => a.date.Month == month && a.date.Year == year).Sum(a => a.consumption_value);
}
if (db.Fuel_activities_consumption.Where(a => a.fuel_type == "DAUMO").Where(a => a.date.Month == month && a.date.Year == year).ToList().Count() == 0)
{
ViewBag.DauMo = 0;
}
else
{
ViewBag.DauMo = db.Fuel_activities_consumption.Where(a => a.fuel_type == "DAUMO").Where(a => a.date.Month == month && a.date.Year == year).Sum(a => a.consumption_value);
}
}
}
public ActionResult GetAllChungChiOfNV(string mnv = "")
{
using (QUANGHANHABCEntities db = new QUANGHANHABCEntities())
{
var temp = (from cc in db.ChungChis
join ccnv in db.ChungChi_NhanVien
on cc.MaChungChi equals ccnv.MaChungChi
where ccnv.MaNV.Equals(mnv)
select new
{
MaNV = ccnv.MaNV,
TenChungChi = cc.TenChungChi,
SoHieu = ccnv.SoHieu,
NgayCap = ccnv.NgayCap,
ConHan = (DbFunctions.DiffDays(DbFunctions.AddMonths(ccnv.NgayCap, cc.ThoiHan), DateTime.Now) < 0 ? "Hết Hạn" :
DbFunctions.DiffDays(DbFunctions.AddMonths(ccnv.NgayCap, cc.ThoiHan), DateTime.Now) >= 0 ? "Còn Hạn" : "Còn Hạn"
)
});
List <ChungChi_NhanVien_Model> arrChungChi = temp.ToList().Select(p => new ChungChi_NhanVien_Model
{
TenChungChi = p.TenChungChi,
SoHieu = p.SoHieu,
NgayCap = p.NgayCap,
isConHan = p.ConHan
}).ToList();
return(Json(new { success = true, data = arrChungChi }, JsonRequestBehavior.AllowGet));
}
}
public ActionResult EditSupplyStatus(string documentary_id, string equipmentId, string listSupplies)
{
if (listSupplies != "")
{
QUANGHANHABCEntities DBContext = new QUANGHANHABCEntities();
using (DbContextTransaction transaction = DBContext.Database.BeginTransaction())
{
try
{
listSupplies = listSupplies.Substring(0, listSupplies.Length - 1);
char[] spearator = { '^' };
String[] list = listSupplies.Split(spearator,
StringSplitOptions.RemoveEmptyEntries);
foreach (var item in list)
{
DBContext.Database.ExecuteSqlCommand("UPDATE Supply_Documentary_Equipment SET supply_documentary_status = 1 WHERE supply_id = @supply_id AND equipmentId = @equipmentId AND documentary_id = @documentary_id",
new SqlParameter("supply_id", item),
new SqlParameter("equipmentId", equipmentId),
new SqlParameter("documentary_id", documentary_id));
}
DBContext.SaveChanges();
transaction.Commit();
return(new HttpStatusCodeResult(201));
}
catch (Exception)
{
transaction.Rollback();
Response.Write("Có lỗi xảy ra, xin vui lòng nhập lại");
return(new HttpStatusCodeResult(400));
}
}
}
return(new HttpStatusCodeResult(201));
}
public ActionResult GetData(string id)
{
//Server Side Parameter
int start = Convert.ToInt32(Request["start"]);
int length = Convert.ToInt32(Request["length"]);
string searchValue = Request["search[value]"];
string sortColumnName = Request["columns[" + Request["order[0][column]"] + "][name]"];
string sortDirection = Request["order[0][dir]"];
QUANGHANHABCEntities DBContext = new QUANGHANHABCEntities();
List <Documentary_liquidation_detailsDB> equips = DBContext.Database.SqlQuery <Documentary_liquidation_detailsDB>("select e.equipmentId, e.equipment_name, details.* from Department depa inner join Documentary docu on depa.department_id = docu.department_id inner join Documentary_liquidation_details details on details.documentary_id = docu.documentary_id inner join Equipment e on e.equipmentId = details.equipmentId where docu.documentary_type = 5 and details.documentary_id = @documentary_id",
new SqlParameter("documentary_id", id)).ToList();
foreach (Documentary_liquidation_detailsDB item in equips)
{
item.statusAndEquip = item.equipment_liquidation_status + "^" + item.equipmentId;
item.idAndEquip = id + "^" + item.equipmentId;
}
int totalrows = equips.Count;
int totalrowsafterfiltering = equips.Count;
ViewBag.List = equips.Count;
//sorting
equips = equips.OrderBy(sortColumnName + " " + sortDirection).ToList <Documentary_liquidation_detailsDB>();
//paging
equips = equips.Skip(start).Take(length).ToList <Documentary_liquidation_detailsDB>();
return(Json(new { success = true, data = equips, draw = Request["draw"], recordsTotal = totalrows, recordsFiltered = totalrowsafterfiltering }, JsonRequestBehavior.AllowGet));
}
public ActionResult validateExistCirtificateOfEmp(string manv, int id, string first_cir)
{
using (QUANGHANHABCEntities db = new QUANGHANHABCEntities())
{
var chungchi_nvs = db.ChungChi_NhanVien.Where(x => (x.MaNV == manv) && (x.MaChungChi == id)).FirstOrDefault <ChungChi_NhanVien>();
if (chungchi_nvs != null)
{
if (first_cir != "" && (chungchi_nvs.MaChungChi.ToString()).Equals(first_cir))
{
return(Json(new { success = false, message = "right id" }, JsonRequestBehavior.AllowGet));
}
else if (first_cir != "" && !(chungchi_nvs.MaChungChi.ToString()).Equals(first_cir))
{
return(Json(new { success = true, message = "right id" }, JsonRequestBehavior.AllowGet));
}
else
{
return(Json(new { success = true, message = "id has been exist" }, JsonRequestBehavior.AllowGet));
}
}
else
{
return(Json(new { success = false, message = "right id" }, JsonRequestBehavior.AllowGet));
}
}
}
public IHttpActionResult GetEmployee(requestData requestData)
{
string id;
string token;
try
{
id = requestData.id;
token = requestData.token;
if (token == "rZslJnUHOW")
{
string sql = @"select nv.*, dp.department_name as TenPhongBan from NhanVien nv, Department dp where nv.MaNV = @id1 and nv.MaPhongBan=dp.department_id
UNION ALL
select *,NULL as TenPhongBan from nhanvien where MaPhongBan is null AND NhanVien.MaNV = @id2";
; QUANGHANHABCEntities db = new QUANGHANHABCEntities();
var nhanvien = db.Database.SqlQuery <NhanVienResultModel>(sql, new SqlParameter("@id1", id), new SqlParameter("@id2", id)).ToList <NhanVienResultModel>().FirstOrDefault <NhanVienResultModel>();
if (nhanvien == null)
{
return(Ok(new { success = true, message = "NO RECORDS FOUND!" }));
}
return(Ok(new { success = true, message = "SUCCESSED!", results = nhanvien }));
}
else
{
return(Ok(new { success = false, message = "INVALID TOKEN!" }));
}
}
catch (Exception e)
{
return(Ok(new { success = false, message = "INVALID REQUEST!" }));
}
}
public ActionResult AddOrEdit(string id = "")
{
List <SelectListItem> listDepeartment = new List <SelectListItem>();
List <SelectListItem> listCategory = new List <SelectListItem>();
using (QUANGHANHABCEntities db = new QUANGHANHABCEntities())
{
var departments = db.Departments.ToList <Department>();
foreach (Department items in departments)
{
listDepeartment.Add(new SelectListItem {
Text = items.department_id, Value = items.department_id
});
}
//
var categories = db.Equipment_category.ToList <Equipment_category>();
foreach (Equipment_category items in categories)
{
listCategory.Add(new SelectListItem {
Text = items.Equipment_category_id, Value = items.Equipment_category_id
});
}
//listForSelect.Add(new SelectListItem { Text = "Your text", Value = "TRAI" });
ViewBag.listDepeartment = listDepeartment;
ViewBag.listCategory = listCategory;
if (id == "" || id == null)
{
return(View(new Equipment()));
}
else
{
return(View(db.Equipments.Where(x => x.equipmentId == id).FirstOrDefault <Equipment>()));
}
}
}
public ActionResult Edit(string id)
{
List <SelectListItem> listDepeartment = new List <SelectListItem>();
List <SelectListItem> listCategory = new List <SelectListItem>();
using (QUANGHANHABCEntities db = new QUANGHANHABCEntities())
{
var departments = db.Departments.ToList <Department>();
foreach (Department items in departments)
{
listDepeartment.Add(new SelectListItem {
Text = items.department_id, Value = items.department_name
});
}
//
var categories = db.Equipment_category.ToList <Equipment_category>();
foreach (Equipment_category items in categories)
{
listCategory.Add(new SelectListItem {
Text = items.Equipment_category_id, Value = items.Equipment_category_name
});
}
//listForSelect.Add(new SelectListItem { Text = "Your text", Value = "TRAI" });
ViewBag.listDepeartment = listDepeartment;
ViewBag.listCategory = listCategory;
return(View(db.Equipments.Where(x => x.equipmentId == id).FirstOrDefault <Equipment>()));
//return Json(new { success = true, message = "Cập nhật thành công" , data= db.Equipments.Where(x => x.equipmentId == id).FirstOrDefault<Equipment>()}, JsonRequestBehavior.AllowGet);
}
}
public ActionResult Add()
{
List <SelectListItem> listDepeartment = new List <SelectListItem>();
List <SelectListItem> listCategory = new List <SelectListItem>();
using (QUANGHANHABCEntities db = new QUANGHANHABCEntities())
{
var departments = db.Departments.ToList <Department>();
foreach (Department items in departments)
{
listDepeartment.Add(new SelectListItem {
Text = items.department_id, Value = items.department_name
});
}
//
var categories = db.Equipment_category.ToList <Equipment_category>();
foreach (Equipment_category items in categories)
{
listCategory.Add(new SelectListItem {
Text = items.Equipment_category_id, Value = items.Equipment_category_name
});
}
//listForSelect.Add(new SelectListItem { Text = "Your text", Value = "TRAI" });
ViewBag.listDepeartment = listDepeartment;
ViewBag.listCategory = listCategory;
}
return(View(new Equipment()));
}
public ActionResult UpdateID(int documentary_id, string documentary_code)
{
QUANGHANHABCEntities DBContext = new QUANGHANHABCEntities();
Documentary i = DBContext.Documentaries.Find(documentary_id);
if (String.IsNullOrEmpty(documentary_code))
{
Response.Write("Vui lòng nhập mã quyết định!");
return(new HttpStatusCodeResult(400));
}
else
{
try
{
var query = (from x in DBContext.Documentaries
where x.documentary_code == documentary_code
select x).First();
Response.Write("Mã quyết định đã tồn tại!");
return(new HttpStatusCodeResult(400));
}
catch
{
documentary_code = documentary_code.Replace(" ", String.Empty);
i.documentary_code = documentary_code;
DBContext.SaveChanges();
return(new HttpStatusCodeResult(201));
}
}
}
public ActionResult Update(int documentary_id, string date_created, string person_created, string reason, string out_in_come)
{
QUANGHANHABCEntities DBContext = new QUANGHANHABCEntities();
if (String.IsNullOrEmpty(date_created) || String.IsNullOrEmpty(person_created) || String.IsNullOrEmpty(out_in_come) || String.IsNullOrEmpty(reason))
{
Response.Write("Có lỗi xảy ra, xin vui lòng nhập lại");
return(new HttpStatusCodeResult(400));
}
else
{
try
{
Documentary documentary = DBContext.Documentaries.Where(a => a.documentary_id == documentary_id).First();
if (documentary != null)
{
documentary.date_created = DateTime.Parse(date_created);
documentary.person_created = person_created;
documentary.reason = reason;
documentary.out_in_come = out_in_come;
}
DBContext.SaveChanges();
return(new HttpStatusCodeResult(201));
}
catch
{
Response.Write("Có lỗi xảy ra, xin vui lòng nhập lại");
return(new HttpStatusCodeResult(400));
}
}
}
public ActionResult Detail()
{
string requestID = Request["sessionId"];
requestID = Base64Decode(requestID).ToString();
int start = Convert.ToInt32(Request["start"]);
int length = 10;
string searchValue = Request["search[value]"];
string sortColumnName = Request["columns[" + Request["order[0][column]"] + "][name]"];
string sortDirection = Request["order[0][dir]"];
using (QUANGHANHABCEntities db = new QUANGHANHABCEntities())
{
db.Configuration.LazyLoadingEnabled = false;
int count = 1;
List <Documentary_liquidation_detailsDB> documentariesList = (from a in db.Documentary_liquidation_details
join b in db.Documentaries on a.documentary_id equals b.documentary_id
join c in db.Equipments on a.equipmentId equals c.equipmentId
join d in db.Departments on c.department_id equals d.department_id
where (b.documentary_code == requestID)
select new
{
documentary_code = b.documentary_code,
equipment_name = c.equipment_name,
department_name = d.department_name,
department_id = c.department_id,
buyer = a.buyer,
reason = b.reason,
}).ToList().Select(p => new Documentary_liquidation_detailsDB
{
documentary_code = p.documentary_code,
equipment_name = p.equipment_name,
department_name = p.department_name,
department_id = p.department_id,
buyer = p.buyer,
reason = p.reason,
}).ToList();
foreach (var el in documentariesList)
{
el.order_number = count++;
}
int totalrows = documentariesList.Count;
int totalrowsafterfiltering = documentariesList.Count;
//sorting
documentariesList = documentariesList.OrderBy(sortColumnName + " " + sortDirection).ToList <Documentary_liquidation_detailsDB>();
//paging
documentariesList = documentariesList.Skip(start).Take(length).ToList <Documentary_liquidation_detailsDB>();
Console.WriteLine(Json(new { success = true, data = documentariesList, draw = Request["draw"], recordsTotal = totalrows, recordsFiltered = totalrowsafterfiltering }, JsonRequestBehavior.AllowGet));
var js = Json(new { success = true, data = documentariesList, draw = Request["draw"], recordsTotal = totalrows, recordsFiltered = totalrowsafterfiltering }, JsonRequestBehavior.AllowGet);
var result = new JavaScriptSerializer().Serialize(js.Data);
ViewBag.count = 0;
return(js);
}
}