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