private IEnumerable <List <object> > getTinhThanhPho(ViewModelSearchNT_TT_THITRUONG SearchModel, List <String> lstMA_TTP) { string ma_TinhTP = string.Empty; initialSearchCategory(SearchModel, ref ma_TinhTP); 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>(); } var selects = db.NT_TT_THITRUONG.Where(o => (SearchModel.DM_DOITUONG_GIA_THITRUONGID == null || o.DM_DOITUONG_GIA_THITRUONGID == SearchModel.DM_DOITUONG_GIA_THITRUONGID) && ((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.TU_NGAY == null || o.NGAY_THUTHAP >= SearchModel.TU_NGAY) && (SearchModel.DEN_NGAY == null || o.NGAY_THUTHAP <= SearchModel.DEN_NGAY)) ).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 Search(ViewModelSearchNT_TT_THITRUONG SearchModel) { if (SearchModel.SearchButton == "Xuất Excel") { ExportExcel(SearchModel); //Dowload file: return(File(GENERATED_FILE_NAME, "application/vnd.ms-excel", "NT_GIA_THITRUONGExport" + DateTime.Now.ToString("ddMMyyyyhhmmss") + ".xls")); } else { string ma_TinhTP = string.Empty; initialSearchCategory(SearchModel, ref ma_TinhTP); var NT_TT_THITRUONGs = db.NT_TT_THITRUONG.Where(o => (SearchModel.DM_DOITUONG_GIA_THITRUONGID == null || o.DM_DOITUONG_GIA_THITRUONGID == SearchModel.DM_DOITUONG_GIA_THITRUONGID) && ((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.TU_NGAY == null || o.NGAY_THUTHAP >= SearchModel.TU_NGAY) && (SearchModel.DEN_NGAY == null || o.NGAY_THUTHAP <= SearchModel.DEN_NGAY)) ).Select(x => new { x.ID, x.DM_DOITUONG_GIA_THITRUONG, x.GIA_THANH_TU, x.GIA_THANH_DEN, x.GIA_GIONG_TU, x.GIA_GIONG_DEN, x.GIA_THUCAN_TU, x.GIA_THUCAN_DEN, x.GIA_BAN_TU, x.GIA_BAN_DEN, x.KICHCO, x.NGAY_THUTHAP, x.NOITHUTHAP, x.DTINHTP } ).OrderByDescending(x => x.ID) ; List <NT_TT_THITRUONG> DSNT_TT_THITRUONG = new List <NT_TT_THITRUONG>(); foreach (var nt_tt_thitruong in NT_TT_THITRUONGs) { DSNT_TT_THITRUONG.Add(new NT_TT_THITRUONG { ID = nt_tt_thitruong.ID, DM_DOITUONG_GIA_THITRUONG = nt_tt_thitruong.DM_DOITUONG_GIA_THITRUONG, GIA_THANH_TU = nt_tt_thitruong.GIA_THANH_TU, GIA_THANH_DEN = nt_tt_thitruong.GIA_THANH_DEN, GIA_GIONG_TU = nt_tt_thitruong.GIA_GIONG_TU, GIA_GIONG_DEN = nt_tt_thitruong.GIA_GIONG_DEN, GIA_BAN_TU = nt_tt_thitruong.GIA_BAN_TU, GIA_BAN_DEN = nt_tt_thitruong.GIA_BAN_DEN, GIA_THUCAN_TU = nt_tt_thitruong.GIA_THUCAN_TU, GIA_THUCAN_DEN = nt_tt_thitruong.GIA_THUCAN_DEN, KICHCO = nt_tt_thitruong.KICHCO, NOITHUTHAP = nt_tt_thitruong.NOITHUTHAP, NGAY_THUTHAP = nt_tt_thitruong.NGAY_THUTHAP, DTINHTP = nt_tt_thitruong.DTINHTP }); } ViewBag.TotalRow = DSNT_TT_THITRUONG.Count().ToString(); //Phân trang ở đây: var pageIndex = SearchModel.Page ?? 1; SearchModel.SearchResults = DSNT_TT_THITRUONG.ToPagedList(pageIndex, FDB.Common.Constants.PageSize); return(View(SearchModel)); } }
// GET: /NT_TT_THITRUONG/ public void initialSearchCategory(ViewModelSearchNT_TT_THITRUONG searchModel, ref string ma_Tinh) { ViewBag.DM_DOITUONG_NUOIs = new SelectList(db.DM_DOITUONG_GIA_THITRUONG, "ID", "Name"); //ViewBag.DPHANLOAI_GIAs = new SelectList(db.DPHANLOAI_GIA, "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"); ma_Tinh = curUser.MA_TINHTP; }
public ActionResult Index(ViewModelSearchNT_TT_THITRUONG SearchModel) { string ma_TinhTP = string.Empty; initialSearchCategory(SearchModel, ref ma_TinhTP); var NT_TT_THITRUONGs = db.NT_TT_THITRUONG.Where(o => (SearchModel.DM_DOITUONG_GIA_THITRUONGID == null || o.DM_DOITUONG_GIA_THITRUONGID == SearchModel.DM_DOITUONG_GIA_THITRUONGID) && ((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) ).Select(x => new { x.ID, x.DM_DOITUONG_GIA_THITRUONG, x.GIA_THANH_TU, x.GIA_THANH_DEN, x.GIA_GIONG_TU, x.GIA_GIONG_DEN, x.GIA_THUCAN_TU, x.GIA_THUCAN_DEN, x.GIA_BAN_TU, x.GIA_BAN_DEN, x.KICHCO, x.NGAY_THUTHAP, x.NOITHUTHAP, x.DTINHTP } ).OrderByDescending(x => x.ID) ; List <NT_TT_THITRUONG> DSNT_TT_THITRUONG = new List <NT_TT_THITRUONG>(); foreach (var nt_tt_thitruong in NT_TT_THITRUONGs) { DSNT_TT_THITRUONG.Add(new NT_TT_THITRUONG { ID = nt_tt_thitruong.ID, DM_DOITUONG_GIA_THITRUONG = nt_tt_thitruong.DM_DOITUONG_GIA_THITRUONG, GIA_THANH_TU = nt_tt_thitruong.GIA_THANH_TU, GIA_THANH_DEN = nt_tt_thitruong.GIA_THANH_DEN, GIA_GIONG_TU = nt_tt_thitruong.GIA_GIONG_TU, GIA_GIONG_DEN = nt_tt_thitruong.GIA_GIONG_DEN, GIA_BAN_TU = nt_tt_thitruong.GIA_BAN_TU, GIA_BAN_DEN = nt_tt_thitruong.GIA_BAN_DEN, GIA_THUCAN_TU = nt_tt_thitruong.GIA_THUCAN_TU, GIA_THUCAN_DEN = nt_tt_thitruong.GIA_THUCAN_DEN, KICHCO = nt_tt_thitruong.KICHCO, NOITHUTHAP = nt_tt_thitruong.NOITHUTHAP, NGAY_THUTHAP = nt_tt_thitruong.NGAY_THUTHAP, DTINHTP = nt_tt_thitruong.DTINHTP }); } ViewBag.TotalRow = DSNT_TT_THITRUONG.Count().ToString(); //Phân trang ở đây: var pageIndex = SearchModel.Page ?? 1; SearchModel.SearchResults = DSNT_TT_THITRUONG.ToPagedList(pageIndex, FDB.Common.Constants.PageSize); return(View(SearchModel)); }
//get detail by co so private IEnumerable <List <object> > getDetailtt_thitruong(ViewModelSearchNT_TT_THITRUONG SearchModel, int id) { IEnumerable <List <object> > items = Enumerable.Empty <List <object> >(); List <List <object> > lstobj = new List <List <object> >(); string ma_TinhTP = string.Empty; initialSearchCategory(SearchModel, ref ma_TinhTP); var selects = db.NT_TT_THITRUONG.Where(o => o.ID == id && (SearchModel.DM_DOITUONG_GIA_THITRUONGID == null || o.DM_DOITUONG_GIA_THITRUONGID == SearchModel.DM_DOITUONG_GIA_THITRUONGID) && ((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.TU_NGAY == null || o.NGAY_THUTHAP >= SearchModel.TU_NGAY) && (SearchModel.DEN_NGAY == null || o.NGAY_THUTHAP <= SearchModel.DEN_NGAY)) ); var lstDetail = selects.ToList(); for (int loop = 0; loop < lstDetail.Count(); loop++) { lstobj.Add(new List <object> { " ", " ", lstDetail[loop].DM_DOITUONG_GIA_THITRUONG == null || lstDetail[loop].DM_DOITUONG_GIA_THITRUONG.Name == null?" ": lstDetail[loop].DM_DOITUONG_GIA_THITRUONG.Name.ToString() , lstDetail[loop].GIA_THANH_TU == null?" ":lstDetail[loop].GIA_THANH_TU.ToString() , lstDetail[loop].GIA_THANH_DEN == null?" ":lstDetail[loop].GIA_THANH_DEN.ToString() , lstDetail[loop].GIA_GIONG_TU == null?" ": lstDetail[loop].GIA_GIONG_TU.ToString() , lstDetail[loop].GIA_GIONG_DEN == null?" ":lstDetail[loop].GIA_GIONG_DEN.ToString() , lstDetail[loop].GIA_BAN_TU == null?" ":lstDetail[loop].GIA_BAN_TU.ToString() , lstDetail[loop].GIA_GIONG_DEN == null?" ":lstDetail[loop].GIA_GIONG_DEN.ToString() , lstDetail[loop].GIA_THUCAN_TU == null?" ":lstDetail[loop].GIA_THUCAN_TU.ToString() , lstDetail[loop].GIA_THUCAN_DEN == null?" ":lstDetail[loop].GIA_THUCAN_DEN.ToString() , lstDetail[loop].KICHCO == null?" ":lstDetail[loop].KICHCO.ToString() , lstDetail[loop].NOITHUTHAP == null?" ":lstDetail[loop].NOITHUTHAP.ToString() , lstDetail[loop].NGAY_THUTHAP == null?" ":lstDetail[loop].NGAY_THUTHAP.Value.ToShortDateString() }); } items = lstobj.AsEnumerable <List <object> >(); return(items); }
public ActionResult ExportExcel(ViewModelSearchNT_TT_THITRUONG 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" }); // CellRangeTemplate row_coso = helper.CreateCellRangeTemplate("row_coso", new List<string> { "stt_num", "tencoso", "c13", "c14", "c15", "c16", "c17", "c18", "c19", "c20", "c21", "c22", "c23", "c24" }); CellRangeTemplate row_11 = helper.CreateCellRangeTemplate("row_11", new List <string> { "str", "c_23", "c_24", "c_25", "c_26", "c_27", "c_28", "c_29", "c_30", "c_31", "c_32", "c_33", "c_34", "c_35" }); 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.gettt_thitruongByTinhTP(_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.getDetailtt_thitruong(SearchModel, _lstCoSoID[j]); helper.InsertRange(row_11, lstDetail); k = k + lstDetail.Count(); } } helper.DeleteSheet("Sheet3"); helper.CurrentSheetName = "Sheet1"; } ViewBag.MSG_EXPORT = "Xuất file Excel thành công!"; // return File(GENERATED_FILE_NAME, "application/vnd.ms-excel", "NT_TT_THITRUONGExcel" + DateTime.Now.ToString("ddMMyyyyhhmmss") + ".xls"); } catch (Exception ex) { ViewBag.MSG_EXPORT = "Xuất file Excel không thành công!"; } return(RedirectToAction("Index")); }