/// <summary>
/// 将新建的地区添加入数据库，返回地区树形显示界面
/// </summary>
/// <param name="regionFormCollection">页面form集合</param>
/// <returns></returns>
public ActionResult AddRegion(Guid? parentId, GeneralRegion model)
{
if (base.GetProgramNode("EnableEdit") == "1")
{
var oGeneralRegion = (from r in dbEntity.GeneralRegions
where r.aParent == parentId && r.FullName == model.FullName
select r).FirstOrDefault();
if (oGeneralRegion == null)
{
oGeneralRegion = new GeneralRegion();
dbEntity.GeneralRegions.Add(oGeneralRegion);
}
oGeneralRegion.Deleted = false; // 如果以前删除了，可以恢复；如果重名了，则覆盖
oGeneralRegion.Code = model.Code;
oGeneralRegion.FullName = model.FullName;
oGeneralRegion.ShortName = model.ShortName;
oGeneralRegion.Sorting = model.Sorting;
oGeneralRegion.RegionLevel = model.RegionLevel;
oGeneralRegion.Remark = model.Remark;
oGeneralRegion.aParent = parentId;
dbEntity.SaveChanges();
return RedirectToAction("Index");
}
return RedirectToAction("ErrorPage", "Home", new { message = LiveAzure.Resource.Common.NoPermission });
}
/// <summary>
/// 从区域主页面跳转到添加地区页面
/// </summary>
/// <param name="id">新建地区的父节点Guid</param>
/// <returns></returns>
public ActionResult AddNewRegion(Guid id)
{
if (base.GetProgramNode("EnableEdit") == "1")
{
GeneralRegion oGeneralRegion = new GeneralRegion();
if (id.Equals(Guid.Empty) || id.Equals(null))
{
oGeneralRegion.aParent = null;
}
else
{
GeneralRegion oParentRegion = dbEntity.GeneralRegions.Where(p => p.Gid == id && p.Deleted == false).FirstOrDefault();
oGeneralRegion.aParent = id;
oGeneralRegion.Parent = oParentRegion;
}
return View("RegionAdd", oGeneralRegion);
}
return RedirectToAction("ErrorPage", "Home", new { message = LiveAzure.Resource.Common.NoPermission });
}
public void ImportChinaRegions(string sExcelFile, string sSheetName)
{
try
{
ExcelData oExcel = new ExcelData(sExcelFile, sSheetName);
DataColumn colCode = oExcel.ExcelTable.Columns["代码"];
DataColumn colName = oExcel.ExcelTable.Columns["名称"];
DataColumn colMap01 = oExcel.ExcelTable.Columns["映射01"];
DataColumn colMap02 = oExcel.ExcelTable.Columns["映射02"];
DataColumn colMap03 = oExcel.ExcelTable.Columns["映射03"];
DataColumn colMap04 = oExcel.ExcelTable.Columns["映射04"];
DataColumn colMap05 = oExcel.ExcelTable.Columns["映射05"];
string sCountryCode = "CHN"; // 中国
int nLevel = 0;
var oCountry = (from r in dbEntity.GeneralRegions
where r.Code == sCountryCode
select r).FirstOrDefault();
if (oCountry == null)
{
oCountry = new GeneralRegion
{
Code = sCountryCode,
FullName = "中华人民共和国",
ShortName = "中国",
RegionLevel = nLevel
};
dbEntity.GeneralRegions.Add(oCountry);
dbEntity.SaveChanges();
}
// 全部删除
dbEntity.Database.ExecuteSqlCommand("EXECUTE dbo.sp_ClearRegions {0}", oCountry.Gid);
oCountry.Deleted = false;
dbEntity.SaveChanges();
string sLastParent = sCountryCode;
string sThisParent = sCountryCode;
GeneralRegion oParent = oCountry;
foreach (DataRow row in oExcel.ExcelTable.Rows)
{
string sCode = row[colCode].ToString().Trim();
string sName = row[colName].ToString().Trim();
string sMap01 = row[colMap01].ToString().Trim();
string sMap02 = row[colMap02].ToString().Trim();
string sMap03 = row[colMap03].ToString().Trim();
string sMap04 = row[colMap04].ToString().Trim();
string sMap05 = row[colMap05].ToString().Trim();
string sPrivance = sCode.Substring(0, 2);
string sCity = sCode.Substring(2, 2);
string sDistrict = sCode.Substring(4, 2);
if (sCity == "00")
{
sThisParent = sCountryCode;
nLevel = 1;
}
else if (sDistrict == "00")
{
sThisParent = sPrivance + "0000";
nLevel = 2;
}
else
{
sThisParent = sPrivance + sCity + "00";
nLevel = 3;
}
if (String.IsNullOrEmpty(sThisParent))
{
oParent = null;
sLastParent = "";
}
else if (sThisParent != sLastParent)
{
oParent = (from r in dbEntity.GeneralRegions
where r.Code == sThisParent
select r).FirstOrDefault();
sLastParent = sThisParent;
}
var oRegion = (from r in dbEntity.GeneralRegions
where r.Code == sCode
select r).FirstOrDefault();
if (oRegion == null)
{
oRegion = new GeneralRegion { Code = sCode };
dbEntity.GeneralRegions.Add(oRegion);
}
oRegion.Deleted = false;
oRegion.Parent = oParent;
oRegion.FullName = sName;
oRegion.ShortName = sName;
oRegion.Map01 = sMap01;
oRegion.Map02 = sMap02;
oRegion.Map03 = sMap03;
oRegion.Map04 = sMap04;
oRegion.Map05 = sMap05;
oRegion.RegionLevel = nLevel;
dbEntity.SaveChanges();
if (Utility.ConfigHelper.GlobalConst.IsDebug)
Debug.WriteLine("{0} {1} {2}", this.ToString(), sCode, sName);
}
oEventBLL.WriteEvent(String.Format("导入国家地区(中国)成功: {0} {1}", sExcelFile, sSheetName),
ModelEnum.ActionLevel.GENERIC, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
catch (Exception ex)
{
oEventBLL.WriteEvent(String.Format("导入国家地区(中国)错误: {0} {1} {2}", sExcelFile, sSheetName, ex.Message),
ModelEnum.ActionLevel.ERROR, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
}
/// <summary>
/// 导入国家地区（通用算法）
/// </summary>
/// <param name="sExcelFile"></param>
/// <param name="sSheetName"></param>
public void ImportRegions(string sExcelFile, string sSheetName)
{
try
{
ExcelData oExcel = new ExcelData(sExcelFile, sSheetName);
DataColumn colCode = oExcel.ExcelTable.Columns["代码"];
DataColumn colFullName = oExcel.ExcelTable.Columns["名称"];
DataColumn colShortName = oExcel.ExcelTable.Columns["简称"];
DataColumn colParent = oExcel.ExcelTable.Columns["上级"];
DataColumn colLevel = oExcel.ExcelTable.Columns["级别"];
DataColumn colPostCode = oExcel.ExcelTable.Columns["邮编"];
DataColumn colDeleted = oExcel.ExcelTable.Columns["删除"];
DataColumn colMap01 = oExcel.ExcelTable.Columns["映射01"];
DataColumn colMap02 = oExcel.ExcelTable.Columns["映射02"];
DataColumn colMap03 = oExcel.ExcelTable.Columns["映射03"];
DataColumn colMap04 = oExcel.ExcelTable.Columns["映射04"];
DataColumn colMap05 = oExcel.ExcelTable.Columns["映射05"];
DataColumn colRemark = oExcel.ExcelTable.Columns["备注"];
// 全部删除
//dbEntity.Database.ExecuteSqlCommand("EXECUTE dbo.sp_ClearRegions {0}", oCountry.Gid);
//dbEntity.SaveChanges();
GeneralRegion oParent = null;
string sLastParent = "";
Guid? oParentID = null;
foreach (DataRow row in oExcel.ExcelTable.Rows)
{
string sCode = row[colCode].ToString().Trim();
string sFullName = row[colFullName].ToString().Trim();
string sShortName = row[colShortName].ToString().Trim();
string sParent = row[colParent].ToString().Trim();
int nLevel = 0;
Int32.TryParse(row[colLevel].ToString(), out nLevel);
string sPostCode = row[colPostCode].ToString().Trim();
bool bDeleted = (row[colDeleted].ToString() == "1") ? true : false;
string sMap01 = row[colMap01].ToString().Trim();
string sMap02 = row[colMap02].ToString().Trim();
string sMap03 = row[colMap03].ToString().Trim();
string sMap04 = row[colMap04].ToString().Trim();
string sMap05 = row[colMap05].ToString().Trim();
string sRemark = row[colRemark].ToString().Trim();
if (sParent != sLastParent)
{
oParent = (from r in dbEntity.GeneralRegions
where r.Code == sParent
select r).FirstOrDefault();
sLastParent = sParent;
if (oParent == null)
oParentID = null;
else
oParentID = oParent.Gid;
}
var oRegion = (from r in dbEntity.GeneralRegions
where r.aParent == oParentID && r.Code == sCode
select r).FirstOrDefault();
if (oRegion == null)
{
oRegion = new GeneralRegion { Parent = oParent, Code = sCode };
dbEntity.GeneralRegions.Add(oRegion);
}
oRegion.FullName = sFullName;
oRegion.ShortName = sShortName;
oRegion.RegionLevel = nLevel;
oRegion.PostCode = sPostCode;
oRegion.Deleted = bDeleted;
oRegion.Map01 = sMap01;
oRegion.Map02 = sMap02;
oRegion.Map03 = sMap03;
oRegion.Map04 = sMap04;
oRegion.Map05 = sMap05;
oRegion.Remark = sRemark;
dbEntity.SaveChanges();
if (Utility.ConfigHelper.GlobalConst.IsDebug)
Debug.WriteLine("{0} {1} {2}", this.ToString(), sCode, sFullName);
}
oEventBLL.WriteEvent(String.Format("导入GeneralRegion国家地区成功: {0} {1}", sExcelFile, sSheetName),
ModelEnum.ActionLevel.GENERIC, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
catch (Exception ex)
{
oEventBLL.WriteEvent(String.Format("导入GeneralRegion国家地区错误: {0} {1} {2}", sExcelFile, sSheetName, ex.Message),
ModelEnum.ActionLevel.ERROR, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
}
public void ImportChinaRegionsText(string sTextFile)
{
if (!File.Exists(sTextFile)) return;
try
{
string sCountryCode = "CHN"; // 中国
int nLevel = 0;
var oCountry = (from r in dbEntity.GeneralRegions
where r.Code == sCountryCode
select r).FirstOrDefault();
if (oCountry == null)
{
oCountry = new GeneralRegion
{
Code = sCountryCode,
FullName = "中华人民共和国",
ShortName = "中国",
RegionLevel = nLevel
};
dbEntity.GeneralRegions.Add(oCountry);
dbEntity.SaveChanges();
}
// 全部删除
dbEntity.Database.ExecuteSqlCommand("EXECUTE dbo.sp_ClearRegions {0}", oCountry.Gid);
oCountry.Deleted = false;
dbEntity.SaveChanges();
// 更新所有节点
StreamReader fsTextFile = new StreamReader(sTextFile, Encoding.Default);
string sLastParent = sCountryCode;
string sThisParent = sCountryCode;
GeneralRegion oParent = oCountry;
do
{
string sLine = fsTextFile.ReadLine().Trim();
if (!String.IsNullOrEmpty(sLine))
{
string sCode = sLine.Substring(0, 6);
string sName = sLine.Substring(6).Trim();
string sPrivance = sLine.Substring(0, 2);
string sCity = sLine.Substring(2, 2);
string sDistrict = sLine.Substring(4, 2);
if (sCity == "00")
{
sThisParent = sCountryCode;
nLevel = 1;
}
else if (sDistrict == "00")
{
sThisParent = sPrivance + "0000";
nLevel = 2;
}
else
{
sThisParent = sPrivance + sCity + "00";
nLevel = 3;
}
if (String.IsNullOrEmpty(sThisParent))
{
oParent = null;
sLastParent = "";
}
else if (sThisParent != sLastParent)
{
oParent = (from r in dbEntity.GeneralRegions
where r.Code == sThisParent
select r).FirstOrDefault();
sLastParent = sThisParent;
}
var oRegion = (from r in dbEntity.GeneralRegions
where r.Code == sCode
select r).FirstOrDefault();
if (oRegion == null)
{
oRegion = new GeneralRegion
{
Parent = oParent,
Code = sCode,
FullName = sName,
ShortName = sName,
RegionLevel = nLevel
};
dbEntity.GeneralRegions.Add(oRegion);
}
else
{
oRegion.Deleted = false;
oRegion.FullName = sName;
oRegion.ShortName = sName;
}
dbEntity.SaveChanges();
if (Utility.ConfigHelper.GlobalConst.IsDebug)
Debug.WriteLine("{0} {1}", this.ToString(), sLine);
}
} while (!fsTextFile.EndOfStream);
fsTextFile.Dispose();
fsTextFile.Close();
}
catch (Exception ex)
{
oEventBLL.WriteEvent(String.Format("导入中国地区失败 {0} {1}", sTextFile, ex.Message),
ModelEnum.ActionLevel.ERROR, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
GC.Collect();
}
/// <summary>
/// 跳转到编辑
/// </summary>
/// <param name="id">需要编辑地区的Guid</param>
/// <returns></returns>
public ActionResult RegionEdit(Guid id)
{
if (base.GetProgramNode("EnableEdit") == "1")
{
try
{
GeneralRegion oGeneralRegion = new GeneralRegion();
oGeneralRegion = dbEntity.GeneralRegions.Include("Parent").Where(p => p.Gid == id).Single();
return View("RegionEdit", oGeneralRegion);
}
catch (Exception ex)
{
return RedirectToAction("ErrorPage", "Home", new { message = ex.Message });
}
}
return RedirectToAction("ErrorPage", "Home", new { message = LiveAzure.Resource.Common.NoPermission });
}
/// <summary>
/// 保存修改的地区
/// </summary>
/// <param name="id">修改地区的Guid</param>
/// <returns></returns>
public ActionResult EditRegion(Guid id, GeneralRegion model)
{
if (base.GetProgramNode("EnableEdit") == "1")
{
GeneralRegion oGeneralRegion = dbEntity.GeneralRegions.Where(p => p.Gid == id).Single();
if (oGeneralRegion != null)
{
oGeneralRegion.Code = model.Code;
oGeneralRegion.FullName = model.FullName;
oGeneralRegion.ShortName = model.ShortName;
oGeneralRegion.Sorting = model.Sorting;
oGeneralRegion.RegionLevel = model.RegionLevel;
oGeneralRegion.Remark = model.Remark;
dbEntity.SaveChanges();
}
return RedirectToAction("Index");
}
return RedirectToAction("ErrorPage", "Home", new { message = LiveAzure.Resource.Common.NoPermission });
}
public ActionResult RegionEdit(Guid id)
{
GeneralRegion oGeneralRegion = new GeneralRegion();
oGeneralRegion = dbEntity.GeneralRegions.Where(p=>p.Gid == id).Single();
return PartialView("PartialTreeEdit", oGeneralRegion);
}
/// <summary>
/// 编辑地区节点信息
/// </summary>
/// <param name="id">修改地区的Guid</param>
/// <param name="regionFormCollection">表单集合</param>
/// <returns></returns>
public ActionResult EditRegion(Guid id, FormCollection regionFormCollection)
{
GeneralRegion oGeneralRegion = new GeneralRegion();
oGeneralRegion = dbEntity.GeneralRegions.Where(p => p.Gid == id).Single();
oGeneralRegion.Code = regionFormCollection["Code"];
oGeneralRegion.FullName = regionFormCollection["FullName"];
oGeneralRegion.ShortName = regionFormCollection["ShortName"];
oGeneralRegion.Sorting = Int32.Parse(regionFormCollection["Sorting"]);
oGeneralRegion.RegionLevel = Int32.Parse(regionFormCollection["RegionLevel"]);
oGeneralRegion.Remark = regionFormCollection["Remark"];
dbEntity.SaveChanges();
return null;
//return RedirectToAction("Index");
//return RedirectToAction("PartialTest");
}
public void MemberTest5()
{
LiveEntities oGeneralEntities = new LiveEntities(ConfigHelper.LiveConnection.Connection);
GeneralRegion RegionA = new GeneralRegion
{
Code = "RegionA",
FullName = "RegionChinaA",
ShortName = "A",
Sorting = 1
};
GeneralRegion RegionB = new GeneralRegion
{
Code = "RegionB",
FullName = "RegionChinaB",
ShortName = "B",
Sorting = 2,
Parent = RegionA
};
oGeneralEntities.GeneralRegions.Add(RegionB);
oGeneralEntities.SaveChanges();
oGeneralEntities.Dispose();
GC.Collect();
}
