//GET Ward
public List <Master_Territory> GetExportWard(DataSourceRequest request, string whereCondition)
{
List <SqlParameter> param = new List <SqlParameter>();
param.Add(new SqlParameter("@Page", 1));
param.Add(new SqlParameter("@PageSize", 99999));
param.Add(new SqlParameter("@WhereCondition", whereCondition));
DataTable dt = new SqlHelper().ExecuteQuery("p_Master_Territory_Select_By_Ward", param);
var lst = new List <Master_Territory>();
foreach (DataRow row in dt.Rows)
{
var item = new Master_Territory();
item.TerritoryID = !row.IsNull("TerritoryID") ? row["TerritoryID"].ToString() : "";
item.ParentID = !row.IsNull("ParentID") ? row["ParentID"].ToString() : "";
item.Level = !row.IsNull("Level") ? row["Level"].ToString() : "";
item.Title = !row.IsNull("Title") ? row["Title"].ToString() : "";
item.TerritoryName = !row.IsNull("TerritoryName") ? row["TerritoryName"].ToString() : "";
item.Latitude = !row.IsNull("Latitude") ? row["Latitude"].ToString() : "";
item.Longitude = !row.IsNull("Longitude") ? row["Longitude"].ToString() : "";
item.CreatedAt = !row.IsNull("CreatedAt") ? DateTime.Parse(row["CreatedAt"].ToString()) : DateTime.Parse("01/01/1900");
item.CreatedBy = !row.IsNull("CreatedBy") ? row["CreatedBy"].ToString() : "";
//reference
item.ParentName = !row.IsNull("ParentName") ? row["ParentName"].ToString() : "";
item.Note = !row.IsNull("Note") ? row["Note"].ToString() : "";
lst.Add(item);
}
return(lst);
}
//GET Country
public DataSourceResult GetPageCountry(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_Master_Territory_Select_By_Country", param);
var lst = new List <Master_Territory>();
foreach (DataRow row in dt.Rows)
{
var item = new Master_Territory();
item.TerritoryID = !row.IsNull("TerritoryID") ? row["TerritoryID"].ToString() : "";
item.ParentID = !row.IsNull("ParentID") ? row["ParentID"].ToString() : "";
item.Level = !row.IsNull("Level") ? row["Level"].ToString() : "";
item.Title = !row.IsNull("Title") ? row["Title"].ToString() : "";
item.TerritoryName = !row.IsNull("TerritoryName") ? row["TerritoryName"].ToString() : "";
item.Latitude = !row.IsNull("Latitude") ? row["Latitude"].ToString() : "";
item.Longitude = !row.IsNull("Longitude") ? row["Longitude"].ToString() : "";
item.CreatedAt = !row.IsNull("CreatedAt") ? DateTime.Parse(row["CreatedAt"].ToString()) : DateTime.Parse("01/01/1900");
item.CreatedBy = !row.IsNull("CreatedBy") ? row["CreatedBy"].ToString() : "";
item.Note = !row.IsNull("Note") ? row["Note"].ToString() : "";
//reference
item.ParentName = !row.IsNull("ParentName") ? row["ParentName"].ToString() : "";
lst.Add(item);
}
DataSourceResult result = new DataSourceResult();
result.Data = lst;
result.Total = dt.Rows.Count > 0 ? Convert.ToInt32(dt.Rows[0]["RowCount"]) : 0;
return(result);
}
//import export (import thuong chi 1 lan thoi nen bo qua)
public FileResult Export_Country([DataSourceRequest]DataSourceRequest request)
{
ExcelPackage pck = new ExcelPackage(new FileInfo(Server.MapPath("~/ExportTemplate/ViTriDiaLy_QuocGia.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 Master_Territory().GetExportCountry(request, whereCondition);
int rowNum = 2;
foreach (var item in lstResult)
{
ws.Cells["A" + rowNum].Value = item.TerritoryID;
ws.Cells["B" + rowNum].Value = item.TerritoryName;
ws.Cells["C" + rowNum].Value = item.Title;
ws.Cells["D" + rowNum].Value = item.Latitude;
ws.Cells["E" + rowNum].Value = item.Longitude;
//ws.Cells["F" + rowNum].Value = item.Latitude;
//ws.Cells["G" + rowNum].Value = item.Longitude;
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
"ViTriDiaLy_QuocGia" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx"); //Suggested file name in the "Save as" dialog which will be displayed to the end user
}
//GET Ward
public DataSourceResult GetPageWard(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_Master_Territory_Select_By_Ward", param);
var lst = new List<Master_Territory>();
foreach (DataRow row in dt.Rows)
{
var item = new Master_Territory();
item.TerritoryID = !row.IsNull("TerritoryID") ? row["TerritoryID"].ToString() : "";
item.ParentID = !row.IsNull("ParentID") ? row["ParentID"].ToString() : "";
item.Level = !row.IsNull("Level") ? row["Level"].ToString() : "";
item.Title = !row.IsNull("Title") ? row["Title"].ToString() : "";
item.TerritoryName = !row.IsNull("TerritoryName") ? row["TerritoryName"].ToString() : "";
item.Latitude = !row.IsNull("Latitude") ? row["Latitude"].ToString() : "";
item.Longitude = !row.IsNull("Longitude") ? row["Longitude"].ToString() : "";
item.CreatedAt = !row.IsNull("CreatedAt") ? DateTime.Parse(row["CreatedAt"].ToString()) : DateTime.Parse("01/01/1900");
item.CreatedBy = !row.IsNull("CreatedBy") ? row["CreatedBy"].ToString() : "";
item.Note = !row.IsNull("Note") ? row["Note"].ToString() : "";
//reference
item.ParentName = !row.IsNull("ParentName") ? row["ParentName"].ToString() : "";
lst.Add(item);
}
DataSourceResult result = new DataSourceResult();
result.Data = lst;
result.Total = dt.Rows.Count > 0 ? Convert.ToInt32(dt.Rows[0]["RowCount"]) : 0;
return result;
}
//GET Ward
public List<Master_Territory> GetExportWard(DataSourceRequest request, string whereCondition)
{
List<SqlParameter> param = new List<SqlParameter>();
param.Add(new SqlParameter("@Page", 1));
param.Add(new SqlParameter("@PageSize", 99999));
param.Add(new SqlParameter("@WhereCondition", whereCondition));
DataTable dt = new SqlHelper().ExecuteQuery("p_Master_Territory_Select_By_Ward", param);
var lst = new List<Master_Territory>();
foreach (DataRow row in dt.Rows)
{
var item = new Master_Territory();
item.TerritoryID = !row.IsNull("TerritoryID") ? row["TerritoryID"].ToString() : "";
item.ParentID = !row.IsNull("ParentID") ? row["ParentID"].ToString() : "";
item.Level = !row.IsNull("Level") ? row["Level"].ToString() : "";
item.Title = !row.IsNull("Title") ? row["Title"].ToString() : "";
item.TerritoryName = !row.IsNull("TerritoryName") ? row["TerritoryName"].ToString() : "";
item.Latitude = !row.IsNull("Latitude") ? row["Latitude"].ToString() : "";
item.Longitude = !row.IsNull("Longitude") ? row["Longitude"].ToString() : "";
item.CreatedAt = !row.IsNull("CreatedAt") ? DateTime.Parse(row["CreatedAt"].ToString()) : DateTime.Parse("01/01/1900");
item.CreatedBy = !row.IsNull("CreatedBy") ? row["CreatedBy"].ToString() : "";
//reference
item.ParentName = !row.IsNull("ParentName") ? row["ParentName"].ToString() : "";
item.Note = !row.IsNull("Note") ? row["Note"].ToString() : "";
lst.Add(item);
}
return lst;
}
public ActionResult Read_Ward([DataSourceRequest]DataSourceRequest request)
{
log4net.Config.XmlConfigurator.Configure();
string whereCondition = "";
if (request.Filters.Count > 0)
{
whereCondition = " AND " + new KendoApplyFilter().ApplyFilter(request.Filters[0]);
}
var data = new Master_Territory().GetPageWard(request.Page, request.PageSize, whereCondition);
return Json(data);
}
public ActionResult GetWardByDistrictID(string id)
{
IDbConnection db = new OrmliteConnection().openConn();
try
{
var data = new Master_Territory().GetWardByDistrictID(id);
return Json(new { success = true, data = data });
}
catch (Exception e)
{
return Json(new { success = false, message = e.Message });
}
finally { db.Close(); }
}