public FileResult Export_DeliveryPackage([DataSourceRequest]DataSourceRequest request) { ExcelPackage pck = new ExcelPackage(new FileInfo(Server.MapPath("~/ExportTemplate/GoiCuocVanChuyen.xlsx"))); ExcelWorksheet ws = pck.Workbook.Worksheets["Data"]; if (userAsset["Export"]) { string whereCondition = ""; if (request.Filters.Count > 0) { whereCondition = " AND " +new KendoApplyFilter().ApplyFilter(request.Filters[0]); } IDbConnection db = new OrmliteConnection().openConn(); var lstResult = new DC_LG_DeliveryFee().GetListDeliveryFee(request, whereCondition); int rowNum = 2; foreach (var item in lstResult) { ws.Cells["A" + rowNum].Value = item.DeliveryFeeID; ws.Cells["B" + rowNum].Value = item.Name; ws.Cells["C" + rowNum].Value = item.TransporterID; ws.Cells["D" + rowNum].Value = item.DeliveryName; ws.Cells["E" + rowNum].Value = item.Descr; ws.Cells["F" + rowNum].Value = item.MinDay; ws.Cells["G" + rowNum].Value = item.MaxDay; ws.Cells["H" + rowNum].Value = item.MinTime; ws.Cells["I" + rowNum].Value = item.MaxTime; ws.Cells["J" + rowNum].Value = item.MinWeight ; ws.Cells["K" + rowNum].Value = item.MaxWeight; ws.Cells["L" + rowNum].Value = item.Price; ws.Cells["M" + rowNum].Value = item.Note; ws.Cells["N" + rowNum].Value = item.Status ? "Đang hoạt động" : "Ngưng hoạt động"; rowNum++; } db.Close(); } else { ws.Cells["A2:E2"].Merge = true; ws.Cells["A2"].Value = "You don't have permission to export data."; } MemoryStream output = new MemoryStream(); pck.SaveAs(output); return File(output.ToArray(), //The binary data of the XLS file "application/vnd.ms-excel", //MIME type of Excel files "GoiCuocVanChuyen" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx"); //Suggested file name in the "Save as" dialog which will be displayed to the end user }
public DataSourceResult GetPage(int page, int pageSize, string whereCondition) { List <SqlParameter> param = new List <SqlParameter>(); param.Add(new SqlParameter("@Page", page)); param.Add(new SqlParameter("@PageSize", pageSize)); param.Add(new SqlParameter("@WhereCondition", whereCondition)); DataTable dt = new SqlHelper().ExecuteQuery("p_DC_DeliveryFee", param); var lst = new List <DC_LG_DeliveryFee>(); foreach (DataRow row in dt.Rows) { var item = new DC_LG_DeliveryFee(); item.DeliveryFeeID = !row.IsNull("DeliveryFeeID") ? int.Parse(row["DeliveryFeeID"].ToString()): 0; item.Name = !row.IsNull("Name") ? row["Name"].ToString() : ""; item.TransporterID = !row.IsNull("TransporterID") ? row["TransporterID"].ToString() : ""; item.Descr = !row.IsNull("Descr") ? row["Descr"].ToString() : ""; item.MinDay = !row.IsNull("MinDay") ? Convert.ToDouble(row["MinDay"].ToString()) : 0; item.MaxDay = !row.IsNull("MaxDay") ? Convert.ToDouble(row["MaxDay"].ToString()) : 0; item.MinTime = !row.IsNull("MinTime") ? Convert.ToDouble(row["MinTime"].ToString()) : 0; item.MaxTime = !row.IsNull("MaxTime") ? Convert.ToDouble(row["MaxTime"].ToString()) : 0; item.MinWeight = !row.IsNull("MinWeight") ? Convert.ToDouble(row["MinWeight"].ToString()) : 0; item.MaxWeight = !row.IsNull("MaxWeight") ? Convert.ToDouble(row["MaxWeight"].ToString()) : 0; item.Price = !row.IsNull("Price") ? Convert.ToDouble(row["Price"].ToString()) : 0; item.Status = !row.IsNull("Status") ? Convert.ToBoolean(row["Status"]) : false;; item.Note = !row.IsNull("Note") ? row["Note"].ToString() : ""; item.DeliveryName = !row.IsNull("DeliveryName") ? row["DeliveryName"].ToString() : ""; item.CreatedBy = !row.IsNull("CreatedBy") ? row["CreatedBy"].ToString() : ""; item.UpdatedBy = !row.IsNull("UpdatedBy") ? row["UpdatedBy"].ToString() : ""; item.CreatedAt = !row.IsNull("CreatedAt") ? DateTime.Parse(row["CreatedAt"].ToString()) : DateTime.Parse("01/01/1900"); item.UpdatedAt = !row.IsNull("UpdatedAt") ? DateTime.Parse(row["UpdatedAt"].ToString()) : DateTime.Parse("01/01/1900"); lst.Add(item); } DataSourceResult result = new DataSourceResult(); result.Data = lst; result.Total = dt.Rows.Count > 0 ? Convert.ToInt32(dt.Rows.Count) : 0; return(result); }
public DataSourceResult GetPage(int page, int pageSize, string whereCondition) { List<SqlParameter> param = new List<SqlParameter>(); param.Add(new SqlParameter("@Page", page)); param.Add(new SqlParameter("@PageSize", pageSize)); param.Add(new SqlParameter("@WhereCondition", whereCondition)); DataTable dt = new SqlHelper().ExecuteQuery("p_DC_DeliveryFee", param); var lst = new List<DC_LG_DeliveryFee>(); foreach (DataRow row in dt.Rows) { var item = new DC_LG_DeliveryFee(); item.DeliveryFeeID = !row.IsNull("DeliveryFeeID") ? int.Parse(row["DeliveryFeeID"].ToString()): 0; item.Name = !row.IsNull("Name") ? row["Name"].ToString() : ""; item.TransporterID = !row.IsNull("TransporterID") ? row["TransporterID"].ToString() : ""; item.Descr = !row.IsNull("Descr") ? row["Descr"].ToString() : ""; item.MinDay = !row.IsNull("MinDay") ? Convert.ToDouble(row["MinDay"].ToString()) : 0; item.MaxDay = !row.IsNull("MaxDay") ? Convert.ToDouble(row["MaxDay"].ToString()) : 0; item.MinTime = !row.IsNull("MinTime") ? Convert.ToDouble(row["MinTime"].ToString()) : 0; item.MaxTime = !row.IsNull("MaxTime") ? Convert.ToDouble(row["MaxTime"].ToString()) : 0; item.MinWeight = !row.IsNull("MinWeight") ? Convert.ToDouble(row["MinWeight"].ToString()) : 0; item.MaxWeight = !row.IsNull("MaxWeight") ? Convert.ToDouble(row["MaxWeight"].ToString()) : 0; item.Price = !row.IsNull("Price") ? Convert.ToDouble(row["Price"].ToString()) : 0; item.Status = !row.IsNull("Status") ? Convert.ToBoolean(row["Status"]) : false; ; item.Note = !row.IsNull("Note") ? row["Note"].ToString() : ""; item.DeliveryName = !row.IsNull("DeliveryName") ? row["DeliveryName"].ToString() : ""; item.CreatedBy = !row.IsNull("CreatedBy") ? row["CreatedBy"].ToString() : ""; item.UpdatedBy = !row.IsNull("UpdatedBy") ? row["UpdatedBy"].ToString() : ""; item.CreatedAt = !row.IsNull("CreatedAt") ? DateTime.Parse(row["CreatedAt"].ToString()) : DateTime.Parse("01/01/1900"); item.UpdatedAt = !row.IsNull("UpdatedAt") ? DateTime.Parse(row["UpdatedAt"].ToString()) : DateTime.Parse("01/01/1900"); lst.Add(item); } DataSourceResult result = new DataSourceResult(); result.Data = lst; result.Total = dt.Rows.Count > 0 ? Convert.ToInt32(dt.Rows.Count) : 0; return result; }
public ActionResult Read_DeliveryPackage([DataSourceRequest]DataSourceRequest request) { string whereCondition = ""; if (request.Filters.Count > 0) { whereCondition = " AND " + new KendoApplyFilter().ApplyFilter(request.Filters[0]); } var data = new DC_LG_DeliveryFee().GetPage(request.Page, request.PageSize, whereCondition); return Json(data); }