public ActionResult Index(ViewModelSearchKT_THIETHAIKHAITHAC SearchModel)
{
var KT_THIETHAIKHAITHACs = db.KT_THIETHAIKHAITHAC.Where
//(o => (SearchModel.SODK_TAU == null || o.SO_DK_TAU == SearchModel.SODK_TAU)
(o => (SearchModel.SODK_TAU == null || o.SO_DK_TAU.ToUpper().Contains(SearchModel.SODK_TAU.ToUpper())) &&
((SearchModel.TU_NGAY == null || o.TG_GAPNAN >= SearchModel.TU_NGAY) &&
(SearchModel.DEN_NGAY == null || o.TG_GAPNAN <= SearchModel.DEN_NGAY))
).Select(x => new { x.ID, x.SO_DK_TAU, x.SO_THUYENVIEN, x.KHUVUC_GAPNAN, x.TG_GAPNAN, x.THIETHAI_UOCTINH }).OrderByDescending(x => x.ID);
List <KT_THIETHAIKHAITHAC> DSKT_THIETHAI = new List <KT_THIETHAIKHAITHAC>();
foreach (var kt_thiethai in KT_THIETHAIKHAITHACs)
{
DSKT_THIETHAI.Add(new KT_THIETHAIKHAITHAC
{
ID = kt_thiethai.ID,
SO_DK_TAU = kt_thiethai.SO_DK_TAU,
SO_THUYENVIEN = kt_thiethai.SO_THUYENVIEN,
KHUVUC_GAPNAN = kt_thiethai.KHUVUC_GAPNAN,
TG_GAPNAN = kt_thiethai.TG_GAPNAN,
THIETHAI_UOCTINH = kt_thiethai.THIETHAI_UOCTINH
});
}
ViewBag.TotalRow = DSKT_THIETHAI.Count().ToString();
//Phân trang ở đây:
var pageIndex = SearchModel.Page ?? 1;
SearchModel.SearchResults = DSKT_THIETHAI.ToPagedList(pageIndex, Constants.PageSize);
return(View(SearchModel));
}
private void Sum(ViewModelSearchKT_THIETHAIKHAITHAC SearchModel)
{
using (var ctx = new FDBContext())
{
//using (var cmd = ctx.Database.Connection.CreateCommand())
//{
// ctx.Database.Connection.Open();
var ListSuCoTAU = ctx.Database.SqlQuery <KT_SUCOVETAU>("exec KT_THIETHAIKHAITHAC_SUCOTAU @fromdate, @todate "
, new SqlParameter("@fromdate", SearchModel.TU_NGAY == null ? (object)DBNull.Value : SearchModel.TU_NGAY.Value)
, new SqlParameter("@todate", SearchModel.DEN_NGAY == null ? (object)DBNull.Value : SearchModel.DEN_NGAY.Value)
).ToList();
ViewBag.CountSuCoTAU = ListSuCoTAU;
var ListSuCoNGUOI = ctx.Database.SqlQuery <KT_SUCOVENGUOI>("exec KT_THIETHAIKHAITHAC_SUCONGUOI @fromdate, @todate "
, new SqlParameter("@fromdate", SearchModel.TU_NGAY == null ? (object)DBNull.Value : SearchModel.TU_NGAY.Value)
, new SqlParameter("@todate", SearchModel.DEN_NGAY == null ? (object)DBNull.Value : SearchModel.DEN_NGAY.Value)
).ToList();
ViewBag.CountSuCoNGUOI = ListSuCoNGUOI;
//const string selectCmd = @" select top 1 sum(THIETHAI_UOCTINH) as [tong_thiet_hai] from KT_THIETHAIKHAITHAC where (@fromdate is null or TG_GAPNAN >= @fromdate) and (@todate is null or TG_GAPNAN <= @todate) ";
var tong_thiethai = ctx.Database.SqlQuery <KT_SUCOVETAU>("exec KT_THIETHAIKHAITHAC_TONG @fromdate, @todate "
, new SqlParameter("@fromdate", SearchModel.TU_NGAY == null ? (object)DBNull.Value : SearchModel.TU_NGAY.Value)
, new SqlParameter("@todate", SearchModel.DEN_NGAY == null ? (object)DBNull.Value : SearchModel.DEN_NGAY.Value)
).FirstOrDefault();
ViewBag.tongthiethai = tong_thiethai.tong_thiet_hai == null?string.Empty :tong_thiethai.tong_thiet_hai.Value.ToString("#,###");
//}
}
}
public ActionResult Search(ViewModelSearchKT_THIETHAIKHAITHAC SearchModel)
{
// khoi tao default checkboxlist
var allSUCOVETAU = db.DSUCOVETAU.ToList().OrderByDescending(x => x.ID); //returns List<SUCOVETAU>
var allSUCOVENGUOI = db.DSUCOVENGUOI.ToList().OrderByDescending(x => x.ID); //returns List<SUCOVENGUOI>
// -------------------------------------------------
var KT_THIETHAIKHAITHACs = db.KT_THIETHAIKHAITHAC.Where(o => ((SearchModel.TU_NGAY == null || o.TG_GAPNAN >= SearchModel.TU_NGAY) &&
(SearchModel.DEN_NGAY == null || o.TG_GAPNAN <= SearchModel.DEN_NGAY))
//).Select(x => new { x.ID, x.SO_DK_TAU, x.SO_THUYENVIEN, x.KHUVUC_GAPNAN, x.TG_GAPNAN, x.THIETHAI_UOCTINH,x.SUCOVETAU,x.SUCOVENGUOI })
).OrderByDescending(x => x.ID);
List <KT_THIETHAIKHAITHAC> DSKT_THIETHAI = KT_THIETHAIKHAITHACs.ToList();
List <ViewModelAddKT_THIETHAIKHAITHAC> DSKT_THIETHAI_NEW = new List <ViewModelAddKT_THIETHAIKHAITHAC>();
ViewModelAddKT_THIETHAIKHAITHAC fu;
foreach (var kt_thiethai in DSKT_THIETHAI)
{
fu = new ViewModelAddKT_THIETHAIKHAITHAC();
var checkBoxListItemSUCOVETAU = new List <CheckBoxListItem>();
var checkBoxListItemSUCOVENGUOI = new List <CheckBoxListItem>();
foreach (var sucovetau in allSUCOVETAU)
{
checkBoxListItemSUCOVETAU.Add(new CheckBoxListItem()
{
ID = sucovetau.ID,
Display = sucovetau.Name,
IsChecked = kt_thiethai.SUCOVETAU.Where(x => x.ID == sucovetau.ID).Any()
});
}
foreach (var sucovenguoi in allSUCOVENGUOI)
{
checkBoxListItemSUCOVENGUOI.Add(new CheckBoxListItem()
{
ID = sucovenguoi.ID,
Display = sucovenguoi.Name,
IsChecked = kt_thiethai.SUCOVENGUOI.Where(x => x.ID == sucovenguoi.ID).Any()
});
}
fu.SO_DK_TAU = kt_thiethai.SO_DK_TAU;
fu.SO_THUYENVIEN = kt_thiethai.SO_THUYENVIEN;
fu.KHUVUC_GAPNAN = kt_thiethai.KHUVUC_GAPNAN;
fu.TG_GAPNAN = kt_thiethai.TG_GAPNAN;
fu.THIETHAI_UOCTINH = kt_thiethai.THIETHAI_UOCTINH;
fu.COQUAN_XULY = kt_thiethai.COQUAN_XULY;
fu.SUCOVENGUOI = checkBoxListItemSUCOVENGUOI;
fu.SUCOVETAU = checkBoxListItemSUCOVETAU;
fu.SO_NGUOI_CHET = kt_thiethai.SO_NGUOI_CHET;
fu.SO_NGUOI_MAT_TICH = kt_thiethai.SO_NGUOI_MAT_TICH;
DSKT_THIETHAI_NEW.Add(fu);
}
//tong so
Sum(SearchModel);
var tong_chet = KT_THIETHAIKHAITHACs.Sum(s => s.SO_NGUOI_CHET);
var tong_mat_tich = KT_THIETHAIKHAITHACs.Sum(s => s.SO_NGUOI_MAT_TICH);
ViewBag.tong_chet = tong_chet;
ViewBag.tong_mat_tich = tong_mat_tich;
// cmd.CommandText = "SELECT COUNT(DSUCOVENGUOI_ID) AS [Ốm/tai nạn lao động] FROM KT_THIETHAIKHAITHACDSUCOVENGUOI where DSUCOVENGUOI_ID = 4 group by DSUCOVENGUOI_ID";
// var count_9 = cmd.ExecuteScalar();
// if (count_9 != null)
// {
// ViewBag.Count9 = count_9.ToString();
// }
//}
ViewBag.TotalRow = KT_THIETHAIKHAITHACs.Count();
//Phân trang ở đây:
var pageIndex = SearchModel.Page ?? 1;
SearchModel.StatisticsResults = DSKT_THIETHAI_NEW.ToPagedList(pageIndex, Constants.PageSize);
return(View(SearchModel));
}
