//get tinh thanh pho
private IEnumerable <List <object> > getTinhThanhPho(ViewModelSearchKT_DONGSUA_TAUTHUYEN Searchmodel, List <String> lstMA_TTP)
{
ApplicationUser curUser = this.getCurrentUser();
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 = _context.KT_DONGSUA_TAUTHUYEN.Where(o => (Searchmodel.TenCoSo == null || o.TEN_COSO.ToUpper().Contains(Searchmodel.TenCoSo.ToUpper())) &&
(Searchmodel.TenChuCoSo == null || o.TEN_CHU_COSO.ToUpper().Contains(Searchmodel.TenChuCoSo.ToUpper())) &&
(Searchmodel.DiaChi == null || o.DIA_CHI.ToUpper().Contains(Searchmodel.DiaChi.ToUpper())) &&
(string.IsNullOrEmpty(Searchmodel.TThanhPho) || o.MA_TINHTP == Searchmodel.TThanhPho)
).Select(x => new { MA_TTP = x.MA_TINHTP, TEN_TTP = x.DTinhTP.TEN_TINHTP }).Distinct();
var models = selects.ToList();
// var models = _context.KT_DONGSUA_TAUTHUYEN;
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);
}
private void CreateExcelFile(ViewModelSearchKT_DONGSUA_TAUTHUYEN Searchmodel)
{
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"
});
CellRangeTemplate row_coso = helper.CreateCellRangeTemplate("row_coso", new List <string> {
"stt_num", "tencoso", "c7", "c8", "c9", "c10", "c11", "c12"
});
CellRangeTemplate row_11 = helper.CreateCellRangeTemplate("row_11", new List <string> {
"str", "c_11", "c_12", "c_13", "c_14", "c_15", "c_16", "c_17"
});
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.getCoSoByTinhTP(_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.getDetailCoSo(_lstCoSoID[j]);
helper.InsertRange(row_11, lstDetail);
k = k + lstDetail.Count();
}
helper.CurrentPosition = new CellRef("A" + (k).ToString());
helper.InsertRange("row_empty");
k = k + 1;
}
helper.DeleteSheet("Sheet3");
helper.CurrentSheetName = "Sheet1";
}
}
//
// GET: /DongSua TauThuyen/
public ActionResult Index(ViewModelSearchKT_DONGSUA_TAUTHUYEN Searchmodel)
{
ApplicationUser curUser = this.getCurrentUser();
var tinh = _context.DTINHTP.Where(u => (curUser.MA_TINHTP == null || curUser.MA_TINHTP.StartsWith("Z")) || u.MA_TINHTP == curUser.MA_TINHTP)
.Except(_context.DTINHTP.Where(d => d.MA_TINHTP.StartsWith("Z")));
ViewBag.DMTThanhPho = new SelectList(tinh, "MA_TINHTP", "TEN_TINHTP", Searchmodel.TThanhPho);
ViewBag.DMQhuyen = new SelectList(_context.DQUANHUYEN, "MA_QUANHUYEN", "TEN_QUANHUYEN");
ViewBag.DMPhuongXa = new SelectList(_context.DPHUONGXA, "MA_PHUONGXA", "TEN_PHUONGXA");
ViewBag.DEFAULT_VALUE_DDL = CategoryCommon.DEFAULT_VALUE_DDL;
if (Searchmodel.SearchButton == "Xuất Excel")
{
CreateExcelFile(Searchmodel);
//Dowload file:
return(File(GENERATED_FILE_NAME, "application/vnd.ms-excel", "exportKT_DONGSUA_TAUTHUYEN" + DateTime.Now.ToString("ddMMyyyyhhmmss") + ".xls"));
}
else
{
//Chưa làm Search không dấu, sẽ tìm hiểu và sửa sau:
var models = _context.KT_DONGSUA_TAUTHUYEN.Where(o => (Searchmodel.TenCoSo == null || o.TEN_COSO.ToUpper().Contains(Searchmodel.TenCoSo.ToUpper())) &&
(Searchmodel.TenChuCoSo == null || o.TEN_CHU_COSO.ToUpper().Contains(Searchmodel.TenChuCoSo.ToUpper())) &&
(Searchmodel.DiaChi == null || o.DIA_CHI.ToUpper().Contains(Searchmodel.DiaChi.ToUpper())) &&
((string.IsNullOrEmpty(Searchmodel.TThanhPho) && string.IsNullOrEmpty(curUser.MA_TINHTP)) || (string.IsNullOrEmpty(Searchmodel.TThanhPho) && o.MA_TINHTP == curUser.MA_TINHTP) || o.MA_TINHTP == Searchmodel.TThanhPho)
)
;
// string sqlsanLuongs = ((ObjectQuery)sanLuongs).ToTraceString();
ViewBag.TotalRow = models.Count().ToString();
models = models.OrderBy(m => m.ID);
//Phân trang ở đây:
var pageIndex = Searchmodel.Page ?? 1;
Searchmodel.SearchResults = models.ToPagedList(pageIndex, FDB.Common.Constants.PageSize);
return(View(Searchmodel));
}
}
