/// <summary>
/// 批量导入
/// </summary>
/// <param name="dt"></param>
public int Import(DataTable dt, ref string errorMsg)
{
int site = SessionHelper.Get(HttpContext.Current, TypeManager.User) != null ?
((TB_User)SessionHelper.Get(HttpContext.Current, TypeManager.User)).SiteID :
((TB_SystemAdmin)SessionHelper.Get(HttpContext.Current, TypeManager.Admin)).SiteID;
string user = SessionHelper.Get(HttpContext.Current, TypeManager.User) != null ?
((TB_User)SessionHelper.Get(HttpContext.Current, TypeManager.User)).ADAccount :
((TB_SystemAdmin)SessionHelper.Get(HttpContext.Current, TypeManager.Admin)).Account;
CommonManager mCommonManager = new CommonManager();
try
{
#region 宿舍区
DataTable dtTB_DormArea = _mTB_DormAreaDAL.GetTableBySite(site);
DataTable dtTB_DormAreaInsert = dtTB_DormArea.Clone();
DataRow[] drTB_DormAreaArr = null;
var queryDormArea = from v in dt.AsEnumerable()
select new
{
DormAreaName = v.Field <string>("宿舍区")
};
foreach (var item in queryDormArea.ToList().Distinct())
{
drTB_DormAreaArr = dtTB_DormArea.Select("name='" + item.DormAreaName + "'");
if (drTB_DormAreaArr.Length <= 0)
{
DataRow drDormAreaInsert = dtTB_DormAreaInsert.NewRow();
drDormAreaInsert["Name"] = item.DormAreaName;
drDormAreaInsert["SiteID"] = site;
drDormAreaInsert["Creator"] = user;
dtTB_DormAreaInsert.Rows.Add(drDormAreaInsert);
}
}
dtTB_DormAreaInsert.Columns.Remove("ID");
if (dtTB_DormAreaInsert.Rows.Count > 0)
{
mCommonManager.DTToDB(dtTB_DormAreaInsert, "TB_DormArea", new string[] { "SiteID", "Name", "Creator" }, DBO.GetInstance().CreateConnection().ConnectionString);
}
#endregion
#region 楼栋
dtTB_DormArea = _mTB_DormAreaDAL.GetTableBySite(site);
DataTable dtTB_Building = _mTB_BuildingDAL.GetTableBySiteID(site);
DataTable dtTB_BuildingInsert = dtTB_Building.Clone();
DataRow[] drTB_BuildingArr = null;
var queryBuilding = from v in dt.AsEnumerable()
select new
{
DormAreaName = v.Field <string>("宿舍区"),
BuildingName = v.Field <string>("楼栋"),
};
foreach (var item in queryBuilding.ToList().Distinct())
{
drTB_BuildingArr = dtTB_Building.Select("Name='" + item.BuildingName + "' and DormAreaName='" + item.DormAreaName + "'");
if (drTB_BuildingArr.Length <= 0)
{
drTB_DormAreaArr = dtTB_DormArea.Select("Name='" + item.DormAreaName + "'");
DataRow drTB_BuildingInsert = dtTB_BuildingInsert.NewRow();
drTB_BuildingInsert["DormAreaID"] = drTB_DormAreaArr[0]["ID"];
drTB_BuildingInsert["Name"] = item.BuildingName;
drTB_BuildingInsert["SiteID"] = site;
drTB_BuildingInsert["Creator"] = user;
dtTB_BuildingInsert.Rows.Add(drTB_BuildingInsert);
}
}
dtTB_BuildingInsert.Columns.Remove("ID");
dtTB_BuildingInsert.Columns.Remove("DormAreaName");
if (dtTB_BuildingInsert.Rows.Count > 0)
{
mCommonManager.DTToDB(dtTB_BuildingInsert, "TB_Building", new string[] { "DormAreaID", "Name", "Creator", "SiteID" }, DBO.GetInstance().CreateConnection().ConnectionString);
}
#endregion
#region 房间
dtTB_DormArea = _mTB_DormAreaDAL.GetTableBySite(site);
dtTB_Building = _mTB_BuildingDAL.GetTableBySiteID(site);
DataTable dtTB_RoomType = _mTB_RoomTypeDAL.GetTable(site);
DataTable dtTB_Room = _mTB_RoomDAL.GetTableBySiteID(site);
DataTable dtTB_RoomInsert = dtTB_Room.Clone();
DataRow[] drTB_RoomArr = null;
DataTable dtRoomType = new TB_RoomTypeDAL().GetTable(site);
var queryRoom = from v in dt.AsEnumerable()
select new
{
DormAreaName = v.Field <string>("宿舍区"),
BuildingName = v.Field <string>("楼栋"),
RoomName = v.Field <string>("房间号"),
RoomType = v.Field <string>("房间类型"),
RoomSexType = v.Field <string>("性别"),
};
foreach (var item in queryRoom.ToList().Distinct())
{
DataRow[] dr = dtTB_RoomType.Select("Name='" + item.RoomType + "'");
if (dr.Length == 0)
{
errorMsg = "房间类型:" + item.RoomType + " 不存在，请检查后再导入";
return(0);
}
drTB_RoomArr = dtTB_Room.Select("BuildingName='" + item.BuildingName + "' and DormAreaName='" + item.DormAreaName + "' and Name='" + item.RoomName + "'");
if (drTB_RoomArr.Length <= 0)
{
drTB_DormAreaArr = dtTB_DormArea.Select("Name='" + item.DormAreaName + "'");
drTB_BuildingArr = dtTB_Building.Select("Name='" + item.BuildingName + "' and DormAreaName='" + item.DormAreaName + "'");
DataRow drTB_RoomInsert = dtTB_RoomInsert.NewRow();
drTB_RoomInsert["DormAreaID"] = drTB_DormAreaArr[0]["ID"];
drTB_RoomInsert["BuildingID"] = drTB_BuildingArr[0]["ID"];
drTB_RoomInsert["Name"] = item.RoomName;
drTB_RoomInsert["SiteID"] = site;
drTB_RoomInsert["Creator"] = user;
drTB_RoomInsert["RoomSexType"] = item.RoomSexType;
drTB_RoomInsert["RoomType2"] = 0;
drTB_RoomInsert["KeyCount"] = "";
DataRow[] drRoomTypeArr = dtRoomType.Select("Name='" + item.RoomType + "'");
if (drRoomTypeArr.Length > 0)
{
drTB_RoomInsert["RoomType"] = drRoomTypeArr[0]["ID"];
}
dtTB_RoomInsert.Rows.Add(drTB_RoomInsert);
}
}
dtTB_RoomInsert.Columns.Remove("ID");
dtTB_RoomInsert.Columns.Remove("DormAreaName");
dtTB_RoomInsert.Columns.Remove("BuildingName");
if (dtTB_RoomInsert.Rows.Count > 0)
{
mCommonManager.DTToDB(dtTB_RoomInsert, "TB_Room", new string[] { "Name", "RoomSexType", "RoomType"
, "RoomType2", "Creator", "SiteID", "DormAreaID", "BuildingID", "KeyCount" }, DBO.GetInstance().CreateConnection().ConnectionString);
}
#endregion
#region 床位号
dtTB_DormArea = _mTB_DormAreaDAL.GetTableBySite(site);
dtTB_Building = _mTB_BuildingDAL.GetTableBySiteID(site);
dtTB_Room = _mTB_RoomDAL.GetTableBySiteID(site);
DataTable dtTB_Bed = _mTB_BedDAL.GetTableBySite(site);
DataTable dtTB_BedInsert = dtTB_Bed.Clone();
DataRow[] drTB_BedArr = null;
var queryBed = from v in dt.AsEnumerable()
select new
{
DormAreaName = v.Field <string>("宿舍区"),
BuildingName = v.Field <string>("楼栋"),
RoomName = v.Field <string>("房间号"),
BedName = v.Field <string>("床位号"),
BedStatus = v.Field <string>("床位状态"),
KeyCount = v.Field <string>("钥匙数量"),
};
int iInsertCount = 0;
foreach (var item in queryBed.ToList().Distinct())
{
if (string.IsNullOrEmpty(item.BedName))
{
errorMsg = item.DormAreaName + item.BuildingName + item.RoomName + " 存在空床位，请检查后再导入";
return(0);
}
drTB_BedArr = dtTB_Bed.Select("BuildingName='" + item.BuildingName + "' and DormAreaName='" + item.DormAreaName + "' and RoomName='" + item.RoomName + "' and Name='" + item.BedName + "'");
if (drTB_BedArr.Length <= 0)
{
drTB_DormAreaArr = dtTB_DormArea.Select("Name='" + item.DormAreaName + "'");
drTB_BuildingArr = dtTB_Building.Select("Name='" + item.BuildingName + "' and DormAreaName='" + item.DormAreaName + "'");
drTB_RoomArr = dtTB_Room.Select("Name='" + item.RoomName + "' and BuildingName='" + item.BuildingName + "' and DormAreaName='" + item.DormAreaName + "'");
DataRow drTB_BedInsert = dtTB_BedInsert.NewRow();
drTB_BedInsert["DormAreaID"] = drTB_DormAreaArr[0]["ID"];
drTB_BedInsert["BuildingID"] = drTB_BuildingArr[0]["ID"];
drTB_BedInsert["RoomID"] = drTB_RoomArr[0]["ID"];
drTB_BedInsert["Name"] = item.BedName;
drTB_BedInsert["SiteID"] = site;
drTB_BedInsert["Creator"] = user;
drTB_BedInsert["KeyCount"] = item.KeyCount;
if (item.BedStatus == RemarkAttribute.GetEnumRemark(TypeManager.BedStatus.Busy))
{
drTB_BedInsert["Status"] = (int)TypeManager.BedStatus.Busy;
}
else if (item.BedStatus == RemarkAttribute.GetEnumRemark(TypeManager.BedStatus.Free))
{
drTB_BedInsert["Status"] = (int)TypeManager.BedStatus.Free;
}
else if (item.BedStatus == RemarkAttribute.GetEnumRemark(TypeManager.BedStatus.Occupy))
{
drTB_BedInsert["Status"] = (int)TypeManager.BedStatus.Occupy;
}
dtTB_BedInsert.Rows.Add(drTB_BedInsert);
iInsertCount++;
}
}
dtTB_BedInsert.Columns.Remove("ID");
dtTB_BedInsert.Columns.Remove("DormAreaName");
dtTB_BedInsert.Columns.Remove("BuildingName");
dtTB_BedInsert.Columns.Remove("RoomName");
if (dtTB_BedInsert.Rows.Count > 0)
{
mCommonManager.DTToDB(dtTB_BedInsert, "TB_Bed", new string[] { "Name", "Creator", "SiteID", "DormAreaID"
, "BuildingID", "RoomID", "Status", "KeyCount" }, DBO.GetInstance().CreateConnection().ConnectionString);
}
return(iInsertCount);
#endregion
}
catch (Exception ex)
{
throw ex;
}
}