private void importroomsource(HttpContext context)
{
HttpFileCollection uploadFiles = context.Request.Files;
if (uploadFiles.Count == 0)
{
context.Response.Write("请选择一个文件");
return;
}
if (string.IsNullOrEmpty(uploadFiles[0].FileName))
{
context.Response.Write("请选择一个文件");
return;
}
HttpPostedFile postedFile = uploadFiles[0];
string filepath = HttpContext.Current.Server.MapPath("~/upload/ImportRoomSource/" + DateTime.Now.ToString("yyyyMMdd"));
if (!System.IO.Directory.Exists(filepath))
{
System.IO.Directory.CreateDirectory(filepath);
}
string filename = DateTime.Now.ToLocalTime().ToString("yyyyMMddHHmmss") + "_" + postedFile.FileName;
string fullpath = Path.Combine(filepath, filename);
postedFile.SaveAs(fullpath);
string msg = string.Empty;
DataTable table = ExcelExportHelper.NPOIReadExcel(fullpath);
if (!table.Columns.Contains("资源ID"))
{
msg += "<p>导入失败，原因：资源ID列不存在</p>";
WebUtil.WriteJson(context, msg);
return;
}
int MinID = table.Select().Min(r =>
{
int ID = 0;
if (r.Field <object>("资源ID") != null)
{
int.TryParse(r.Field <object>("资源ID").ToString(), out ID);
}
return(ID);
});
int MaxID = table.Select().Max(r =>
{
int ID = 0;
if (r.Field <object>("资源ID") != null)
{
int.TryParse(r.Field <object>("资源ID").ToString(), out ID);
}
return(ID);
});
string TableName = Utility.EnumModel.DefineFieldTableName.RoomBasic.ToString();
string TableName_Relation = Utility.EnumModel.DefineFieldTableName.RoomPhoneRelation.ToString();
string isconver = context.Request["isconver"];
bool ImportFailed = false;
int count = 0;
var comm_helper = new APPCode.CommHelper();
titleList = GetTableColumns();
var basicList = RoomBasic.GetRoomBasicListByMinMaxRoomID(MinID, MaxID);
var phoneList = RoomPhoneRelation.GetRoomPhoneRelationListByMinMaxRoomID(MinID, MaxID);
using (SqlHelper helper = new SqlHelper())
{
try
{
helper.BeginTransaction();
#region 导入处理
for (int i = 0; i < table.Rows.Count; i++)
{
count = i;
Project project = null;
object Value = table.Rows[i]["资源ID"];
int RoomID = 0;
if (Value != null)
{
int.TryParse(Value.ToString(), out RoomID);
}
if (RoomID > 0)
{
project = Foresight.DataAccess.Project.GetProject(RoomID, helper);
}
if (project == null)
{
msg += "<p>第" + (i + 2) + "行上传失败。原因：所属项目不存在</p>";
ImportFailed = true;
break;
}
if (GetColumnValue("房号", table, i, out Value))
{
project.Name = Value.ToString().Trim();
}
project.Save(helper);
RoomBasic basic = basicList.FirstOrDefault(p => p.RoomID == project.ID);
if (isconver.Equals("0") && basic != null)
{
continue;
}
if (basic == null)
{
basic = new RoomBasic();
basic.RoomID = project.ID;
basic.AddTime = DateTime.Now;
}
if (GetColumnValue("期数", table, i, out Value))
{
basic.BuildingNumber = Value.ToString().Trim();
}
if (GetColumnValue("签约日期", table, i, out Value))
{
basic.SignDate = GetDateTimeValue(Value);
}
if (GetColumnValue("交付时间", table, i, out Value))
{
basic.PaymentTime = GetDateTimeValue(Value);
}
if (GetColumnValue("产权办理时间", table, i, out Value))
{
basic.CertificateTime = GetDateTimeValue(Value);
}
if (GetColumnValue("房产类别", table, i, out Value))
{
basic.RoomType = Value.ToString().Trim();
}
if (GetColumnValue("精装修情况", table, i, out Value))
{
basic.IsJingZhuangXiu = 0;
if (Value.ToString().Trim().Equals("是"))
{
basic.IsJingZhuangXiu = 1;
}
if (Value.ToString().Trim().Equals("否"))
{
basic.IsJingZhuangXiu = 2;
}
}
if (GetColumnValue("建筑面积", table, i, out Value))
{
basic.BuildingOutArea = GetDecimalValue(Value);
}
List <RoomPhoneRelation> roomPhoneRelationList = new List <RoomPhoneRelation>();
string phoneName = string.Empty;
if (GetColumnValue("业主1", table, i, out Value))
{
phoneName = Value.ToString().Trim();
}
if (!string.IsNullOrEmpty(phoneName))
{
var myPhoneRelation = phoneList.FirstOrDefault(p => p.RoomID == basic.RoomID && p.RelationName.Equals(phoneName));
if (myPhoneRelation == null)
{
myPhoneRelation = new RoomPhoneRelation();
myPhoneRelation.AddTime = DateTime.Now;
myPhoneRelation.RoomID = basic.RoomID;
}
myPhoneRelation.RelationType = "homefamily";
myPhoneRelation.RelationName = phoneName;
if (GetColumnValue("业主1联系方式", table, i, out Value))
{
myPhoneRelation.RelatePhoneNumber = Value.ToString().Trim();
}
myPhoneRelation.Save(helper);
}
phoneName = string.Empty;
if (GetColumnValue("业主2", table, i, out Value))
{
phoneName = Value.ToString().Trim();
}
if (!string.IsNullOrEmpty(phoneName))
{
var myPhoneRelation = phoneList.FirstOrDefault(p => p.RoomID == basic.RoomID && p.RelationName.Equals(phoneName));
if (myPhoneRelation == null)
{
myPhoneRelation = new RoomPhoneRelation();
myPhoneRelation.AddTime = DateTime.Now;
myPhoneRelation.RoomID = basic.RoomID;
}
myPhoneRelation.RelationType = "homefamily";
myPhoneRelation.RelationName = phoneName;
if (GetColumnValue("业主2联系方式", table, i, out Value))
{
myPhoneRelation.RelatePhoneNumber = Value.ToString().Trim();
}
myPhoneRelation.Save(helper);
}
phoneName = string.Empty;
if (GetColumnValue("住户1", table, i, out Value))
{
phoneName = Value.ToString().Trim();
}
if (!string.IsNullOrEmpty(phoneName))
{
var myPhoneRelation = phoneList.FirstOrDefault(p => p.RoomID == basic.RoomID && p.RelationName.Equals(phoneName));
if (myPhoneRelation == null)
{
myPhoneRelation = new RoomPhoneRelation();
myPhoneRelation.AddTime = DateTime.Now;
myPhoneRelation.RoomID = basic.RoomID;
}
myPhoneRelation.RelationType = "rentfamily";
myPhoneRelation.RelationName = phoneName;
if (GetColumnValue("住户1联系方式", table, i, out Value))
{
myPhoneRelation.RelatePhoneNumber = Value.ToString().Trim();
}
myPhoneRelation.Save(helper);
}
basic.Save(helper);
}
#endregion
if (!ImportFailed)
{
helper.Commit();
msg += "<p>导入完成</p>";
}
else
{
helper.Rollback();
msg += "<p>导入失败</p>";
}
}
catch (Exception ex)
{
LogHelper.WriteError("ImportSourceHandler", "visit: importroomsource", ex);
msg = "第" + (count + 2) + "行数据有问题，导入取消";
helper.Rollback();
}
context.Response.Write(msg);
}
}
