public ActionResult Search(ViewModelSearchKT_KHU_BAO_TON SearchModel) { if (SearchModel.SearchButton == "Xuất Excel") { ExportData(SearchModel); //Dowload file: return(File(GENERATED_FILE_NAME, "application/vnd.ms-excel", "KT_KHUBAOTONExport" + DateTime.Now.ToString("ddMMyyyyhhmmss") + ".xls")); } else { string ma_TinhTP = string.Empty; initialCategorySearchAction(SearchModel, ref ma_TinhTP); var KT_KHUBAOTONs = db.KT_KHUBAOTON.Where(o => (SearchModel.TEN_KHU_BAO_TON == null || o.TEN_KHU_BAO_TON.ToUpper().Contains(SearchModel.TEN_KHU_BAO_TON.ToUpper())) && (SearchModel.DIA_CHI == null || o.DIA_CHI.ToUpper().Contains(SearchModel.DIA_CHI.ToUpper())) && ((string.IsNullOrEmpty(SearchModel.MA_TINHTP) && ma_TinhTP.StartsWith("Z")) || (string.IsNullOrEmpty(SearchModel.MA_TINHTP) && o.MA_TINHTP == ma_TinhTP) || o.MA_TINHTP == SearchModel.MA_TINHTP) && (SearchModel.MA_QUANHUYEN == null || o.MA_QUANHUYEN == SearchModel.MA_QUANHUYEN) && (SearchModel.MA_PHUONGXA == null || o.MA_PHUONGXA == SearchModel.MA_PHUONGXA) && (SearchModel.DKHU_BAO_TONID == null || o.DKHU_BAO_TONID == SearchModel.DKHU_BAO_TONID) ).Select(x => new { x.ID, x.TEN_KHU_BAO_TON, x.DIA_CHI, x.TONG_DIENTICH, x.PHAN_DATLIEN, x.PHAN_NUOC, x.DKHU_BAO_TON, x.DTINHTP, x.DQUANHUYEN, x.DPHUONGXA }).OrderByDescending(x => x.ID) ; List <KT_KHUBAOTON> DSKT_KHUBAOTON = new List <KT_KHUBAOTON>(); foreach (var kt_khubaoton in KT_KHUBAOTONs) { DSKT_KHUBAOTON.Add(new KT_KHUBAOTON { ID = kt_khubaoton.ID, TEN_KHU_BAO_TON = kt_khubaoton.TEN_KHU_BAO_TON, DIA_CHI = kt_khubaoton.DIA_CHI, TONG_DIENTICH = kt_khubaoton.TONG_DIENTICH, PHAN_DATLIEN = kt_khubaoton.PHAN_DATLIEN, PHAN_NUOC = kt_khubaoton.PHAN_NUOC, DKHU_BAO_TON = kt_khubaoton.DKHU_BAO_TON, DTINHTP = kt_khubaoton.DTINHTP, DQUANHUYEN = kt_khubaoton.DQUANHUYEN, DPHUONGXA = kt_khubaoton.DPHUONGXA }); } ViewBag.TotalRow = DSKT_KHUBAOTON.Count().ToString(); //Phân trang ở đây: var pageIndex = SearchModel.Page ?? 1; SearchModel.SearchResults = DSKT_KHUBAOTON.ToPagedList(pageIndex, FDB.Common.Constants.PageSize); return(View(SearchModel)); } }
public ActionResult Index(ViewModelSearchKT_KHU_BAO_TON SearchModel) { string ma_TinhTP = string.Empty; initialCategorySearchAction(SearchModel, ref ma_TinhTP); var KT_KHUBAOTONs = db.KT_KHUBAOTON.Where(o => (SearchModel.TEN_KHU_BAO_TON == null || o.TEN_KHU_BAO_TON.ToUpper().Contains(SearchModel.TEN_KHU_BAO_TON.ToUpper())) && (SearchModel.DIA_CHI == null || o.DIA_CHI.ToUpper().Contains(SearchModel.DIA_CHI.ToUpper())) && ((string.IsNullOrEmpty(SearchModel.MA_TINHTP) && ma_TinhTP.StartsWith("Z")) || (string.IsNullOrEmpty(SearchModel.MA_TINHTP) && o.MA_TINHTP == ma_TinhTP) || o.MA_TINHTP == SearchModel.MA_TINHTP) && (SearchModel.MA_QUANHUYEN == null || o.MA_QUANHUYEN == SearchModel.MA_QUANHUYEN) && (SearchModel.MA_PHUONGXA == null || o.MA_PHUONGXA == SearchModel.MA_PHUONGXA) && (SearchModel.DKHU_BAO_TONID == null || o.DKHU_BAO_TONID == SearchModel.DKHU_BAO_TONID) ).Select(x => new { x.ID, x.TEN_KHU_BAO_TON, x.DIA_CHI, x.TONG_DIENTICH, x.PHAN_DATLIEN, x.PHAN_NUOC, x.DKHU_BAO_TON, x.DTINHTP, x.DQUANHUYEN, x.DPHUONGXA }).OrderByDescending(x => x.ID) ; List <KT_KHUBAOTON> DSKT_KHUBAOTON = new List <KT_KHUBAOTON>(); foreach (var kt_khubaoton in KT_KHUBAOTONs) { DSKT_KHUBAOTON.Add(new KT_KHUBAOTON { ID = kt_khubaoton.ID, TEN_KHU_BAO_TON = kt_khubaoton.TEN_KHU_BAO_TON, DIA_CHI = kt_khubaoton.DIA_CHI, TONG_DIENTICH = kt_khubaoton.TONG_DIENTICH, PHAN_DATLIEN = kt_khubaoton.PHAN_DATLIEN, PHAN_NUOC = kt_khubaoton.PHAN_NUOC, DKHU_BAO_TON = kt_khubaoton.DKHU_BAO_TON, DTINHTP = kt_khubaoton.DTINHTP, DQUANHUYEN = kt_khubaoton.DQUANHUYEN, DPHUONGXA = kt_khubaoton.DPHUONGXA }); } ViewBag.TotalRow = DSKT_KHUBAOTON.Count().ToString(); //Phân trang ở đây: var pageIndex = SearchModel.Page ?? 1; SearchModel.SearchResults = DSKT_KHUBAOTON.ToPagedList(pageIndex, FDB.Common.Constants.PageSize); return(View(SearchModel)); }
//get detail by co so private IEnumerable <List <object> > getDetailkhubaoton(ViewModelSearchKT_KHU_BAO_TON SearchModel, int IDkhubaoton) { IEnumerable <List <object> > items = Enumerable.Empty <List <object> >(); List <List <object> > lstobj = new List <List <object> >(); string ma_TinhTP = string.Empty; initialCategorySearchAction(SearchModel, ref ma_TinhTP); var selects = db.KT_KHUBAOTON.Where(o => o.ID == IDkhubaoton && (SearchModel.TEN_KHU_BAO_TON == null || o.TEN_KHU_BAO_TON.ToUpper().Contains(SearchModel.TEN_KHU_BAO_TON.ToUpper())) && (SearchModel.DIA_CHI == null || o.DIA_CHI.ToUpper().Contains(SearchModel.DIA_CHI.ToUpper())) && ((string.IsNullOrEmpty(SearchModel.MA_TINHTP) && ma_TinhTP.StartsWith("Z")) || (string.IsNullOrEmpty(SearchModel.MA_TINHTP) && o.MA_TINHTP == ma_TinhTP) || o.MA_TINHTP == SearchModel.MA_TINHTP) //&& (SearchModel.MA_TINHTP == null || o.MA_TINHTP == SearchModel.MA_TINHTP) && (SearchModel.MA_QUANHUYEN == null || o.MA_QUANHUYEN == SearchModel.MA_QUANHUYEN) && (SearchModel.MA_PHUONGXA == null || o.MA_PHUONGXA == SearchModel.MA_PHUONGXA) && (SearchModel.DKHU_BAO_TONID == null || o.DKHU_BAO_TONID == SearchModel.DKHU_BAO_TONID) ); var lstDetail = selects.ToList(); for (int loop = 0; loop < lstDetail.Count(); loop++) { lstobj.Add(new List <object> { " ", lstDetail[loop].DKHU_BAO_TON == null || lstDetail[loop].DKHU_BAO_TON.Name == null?" ":lstDetail[loop].DKHU_BAO_TON.Name.ToString() , lstDetail[loop].TEN_KHU_BAO_TON == null?" ": lstDetail[loop].TEN_KHU_BAO_TON.ToString() , lstDetail[loop].VIDO == null?" ":lstDetail[loop].VIDO.ToString() , lstDetail[loop].KINHDO == null?" ":lstDetail[loop].KINHDO.ToString() , lstDetail[loop].DQUANHUYEN == null || lstDetail[loop].DQUANHUYEN.TEN_QUANHUYEN == null?" ":lstDetail[loop].DQUANHUYEN.TEN_QUANHUYEN.ToString() , lstDetail[loop].DPHUONGXA == null || lstDetail[loop].DPHUONGXA.TEN_PHUONGXA == null?" ": lstDetail[loop].DPHUONGXA.TEN_PHUONGXA.ToString() , lstDetail[loop].DIA_CHI == null?" ":lstDetail[loop].DIA_CHI.ToString() , lstDetail[loop].TONG_DIENTICH == null?" ":lstDetail[loop].TONG_DIENTICH.ToString() , lstDetail[loop].PHAN_DATLIEN == null?" ":lstDetail[loop].PHAN_DATLIEN.ToString() , lstDetail[loop].PHAN_NUOC == null?" ":lstDetail[loop].PHAN_NUOC.ToString() , lstDetail[loop].PHAMVI_GIOIHAN == null?" ":lstDetail[loop].PHAMVI_GIOIHAN.ToString() , lstDetail[loop].NOI_SAU_NHAT == null?" ":lstDetail[loop].NOI_SAU_NHAT.ToString() , lstDetail[loop].HE_SINH_THAI == null?" ":lstDetail[loop].HE_SINH_THAI.ToString() , lstDetail[loop].CONGTAC_BAOTON == null?" ":lstDetail[loop].CONGTAC_BAOTON.ToString() }); } items = lstobj.AsEnumerable <List <object> >(); return(items); }
public void initialCategorySearchAction(ViewModelSearchKT_KHU_BAO_TON searchModel, ref string ma_Tinh) { ViewBag.DM_KHUBAOTONs = new SelectList(db.DKHU_BAO_TON, "ID", "Name"); // ViewBag.DTINHTPs = new SelectList(db.DTINHTP, "MA_TINHTP", "TEN_TINHTP"); ApplicationUser curUser = getCurrentUser(); var tinh = db.DTINHTP.Where(u => curUser.MA_TINHTP.StartsWith("Z") || u.MA_TINHTP == curUser.MA_TINHTP).Except(db.DTINHTP.Where(d => d.MA_TINHTP.StartsWith("Z") == true)); ViewBag.DTINHTPs = new SelectList(tinh, "MA_TINHTP", "TEN_TINHTP"); var quanhuyen = db.DQUANHUYEN.Where(d => d.MA_TINHTP == searchModel.MA_TINHTP); var phuongxa = db.DPHUONGXA.Where(d => d.MA_QUANHUYEN == searchModel.MA_QUANHUYEN); ViewBag.QUAN_HUYENs = new SelectList(quanhuyen, "MA_QUANHUYEN", "TEN_QUANHUYEN"); ViewBag.PHUONG_XAs = new SelectList(phuongxa, "MA_PHUONGXA", "TEN_PHUONGXA"); ViewBag.DEFAULT_VALUE_DDL = CategoryCommon.DEFAULT_VALUE_DDL; ma_Tinh = curUser.MA_TINHTP; }
private IEnumerable <List <object> > getTinhThanhPho(ViewModelSearchKT_KHU_BAO_TON SearchModel, List <String> lstMA_TTP) { IEnumerable <List <object> > items = Enumerable.Empty <List <object> >(); List <List <object> > lstobj = new List <List <object> >(); if (lstMA_TTP == null) { lstMA_TTP = new List <String>(); } string ma_TinhTP = string.Empty; initialCategorySearchAction(SearchModel, ref ma_TinhTP); var selects = db.KT_KHUBAOTON.Where(o => (SearchModel.TEN_KHU_BAO_TON == null || o.TEN_KHU_BAO_TON.ToUpper().Contains(SearchModel.TEN_KHU_BAO_TON.ToUpper())) && (SearchModel.DIA_CHI == null || o.DIA_CHI.ToUpper().Contains(SearchModel.DIA_CHI.ToUpper())) && ((string.IsNullOrEmpty(SearchModel.MA_TINHTP) && ma_TinhTP.StartsWith("Z")) || (string.IsNullOrEmpty(SearchModel.MA_TINHTP) && o.MA_TINHTP == ma_TinhTP) || o.MA_TINHTP == SearchModel.MA_TINHTP) //&& (SearchModel.MA_TINHTP == null || o.MA_TINHTP == SearchModel.MA_TINHTP) && (SearchModel.MA_QUANHUYEN == null || o.MA_QUANHUYEN == SearchModel.MA_QUANHUYEN) && (SearchModel.MA_PHUONGXA == null || o.MA_PHUONGXA == SearchModel.MA_PHUONGXA) && (SearchModel.DKHU_BAO_TONID == null || o.DKHU_BAO_TONID == SearchModel.DKHU_BAO_TONID) //).Select(x => new { x.ID, x.TEN_KHU_BAO_TON, x.DIA_CHI, x.TONG_DIENTICH, x.PHAN_DATLIEN, x.PHAN_NUOC, x.DKHU_BAO_TON, x.DTINHTP, x.DQUANHUYEN, x.DPHUONGXA,x.DKHU_BAO_TON.Name,x.VIDO,x.KINHDO }).OrderByDescending(x => x.ID) ).Select(x => new { MA_TTP = x.MA_TINHTP, TEN_TTP = x.DTINHTP.TEN_TINHTP }).Distinct(); var models = selects.ToList(); for (int loop = 0; loop < models.Count(); loop++) { lstobj.Add(new List <object> { this.NumberToRoman(loop + 1), models[loop].TEN_TTP , " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " " } ); lstMA_TTP.Add(models[loop].MA_TTP); } items = lstobj.AsEnumerable <List <object> >(); return(items); }
public ActionResult ExportData(ViewModelSearchKT_KHU_BAO_TON SearchModel) { try { using (ExcelHelper helper = new ExcelHelper(TEMPLATE_FILE_NAME, GENERATED_FILE_NAME)) { helper.Direction = ExcelHelper.DirectionType.TOP_TO_DOWN; helper.CurrentSheetName = "Sheet1"; helper.CurrentPosition = new CellRef("A5"); helper.InsertRange("header_1"); helper.CurrentPosition = new CellRef("A6"); helper.InsertRange("header_2"); CellRangeTemplate row_tinhthanhpho = helper.CreateCellRangeTemplate("row_tinhthanhpho", new List <string> { "stt_lama", "tinhthanhpho", "c1", "c2", "c3", "c4", "c5", "c6", "c7", "c8", "c9", "c10", "c11", "c12", "c13" }); // CellRangeTemplate row_coso = helper.CreateCellRangeTemplate("row_coso", new List<string> { "stt_num", "tencoso", "c14", "c15", "c16", "c17", "c18", "c19", "c20", "c21", "c22", "c23", "c24", "c25", "c26" }); CellRangeTemplate row_11 = helper.CreateCellRangeTemplate("row_11", new List <string> { "str", "c_25", "c_26", "c_27", "c_28", "c_29", "c_30", "c_31", "c_32", "c_33", "c_34", "c_35", "c_36", "c_37", "c_38" }); int k = 7; List <String> _lstMA_TTP = new List <string>(); IEnumerable <List <object> > _lstTinhTP = this.getTinhThanhPho(SearchModel, _lstMA_TTP); for (int i = 0; i < _lstTinhTP.Count(); i++) { //insert Tinh thanh pho helper.CurrentPosition = new CellRef("A" + (k).ToString()); helper.InsertRange(row_tinhthanhpho, _lstTinhTP.ToArray()[i]); k = k + 1; //insert Co so List <int> _lstCoSoID = new List <int>(); IEnumerable <List <object> > _lstCoSo = this.getkhubaotonByTinhTP(_lstMA_TTP[i], _lstCoSoID); for (int j = 0; j < _lstCoSo.Count(); j++) { //helper.CurrentPosition = new CellRef("A" + (k).ToString()); //helper.InsertRange(row_coso, _lstCoSo.ToArray()[j]); //k = k + 1; //Insert co so detail helper.CurrentPosition = new CellRef("A" + (k).ToString()); IEnumerable <List <object> > lstDetail = this.getDetailkhubaoton(SearchModel, _lstCoSoID[j]); helper.InsertRange(row_11, lstDetail); k = k + lstDetail.Count(); } } helper.DeleteSheet("Sheet3"); helper.CurrentSheetName = "Sheet1"; } TempData["Message"] = "Xuất fiel Excel thành công!"; ViewBag.MSG_EXPORT = TempData["Message"]; // return File(GENERATED_FILE_NAME, "application/vnd.ms-excel", "KT_KHUBAOTONExcel" + DateTime.Now.ToString("ddMMyyyyhhmmss") + ".xls"); } catch (Exception ex) { TempData["Message"] = "Xuất file Excel không thành công!"; ViewBag.MSG_EXPORT = TempData["Message"]; } return(RedirectToAction("Index")); }