private void updatehistoryfeechageid(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/Temp/" + 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);
DataTable table = ExcelExportHelper.NPOIReadExcel(fullpath);
var ChargeSummarys = Foresight.DataAccess.ChargeSummary.GetChargeSummaries().ToArray();
int total = 0;
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow dr = table.Rows[i];
string PrintNumber = dr["收款单号"].ToString();
string ChargeName = dr["收费项目"].ToString();
if (string.IsNullOrEmpty(PrintNumber) || string.IsNullOrEmpty(ChargeName))
{
continue;
}
var my_chargesummary = ChargeSummarys.FirstOrDefault(p => p.Name.Equals(ChargeName));
if (my_chargesummary == null)
{
continue;
}
var history_data = Foresight.DataAccess.RoomFeeHistory.GetRoomFeeHistoryByPrintNumber(PrintNumber);
if (history_data != null)
{
history_data.ChargeID = my_chargesummary.ID;
history_data.Save();
total++;
}
}
WebUtil.WriteJson(context, new { status = true, count = total });
}
private void importmeterpointdata(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;
}
string msg = string.Empty;
bool ImportFailed = false;
int TotalNumber = 0;
using (SqlHelper helper = new SqlHelper())
{
try
{
helper.BeginTransaction();
#region 导入处理
for (int j = 0; j < uploadFiles.Count; j++)
{
HttpPostedFile postedFile = uploadFiles[j];
string filepath = HttpContext.Current.Server.MapPath("~/upload/ImportChaoBiao/" + 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);
DataTable table = ExcelExportHelper.NPOIReadExcel(fullpath);
for (int i = 0; i < table.Rows.Count; i++)
{
int ID = 0;
int.TryParse(table.Rows[i]["仪表ID"].ToString(), out ID);
Foresight.DataAccess.ChargeMeter_Project projectbiao = null;
if (ID > 0)
{
projectbiao = Foresight.DataAccess.ChargeMeter_Project.GetChargeMeter_Project(ID, helper);
}
if (projectbiao == null)
{
msg += "<p>第" + (i + 2) + "行上传失败。原因：仪表不存在</p>";
ImportFailed = true;
break;
}
projectbiao.MeterName = table.Rows[i]["仪表名称"].ToString();
projectbiao.MeterNumber = table.Rows[i]["仪表编号"].ToString();
projectbiao.MeterCategoryID = ChargeMeter_Project.GetMeterCategoryID(table.Rows[i]["仪表种类"].ToString());
if (projectbiao.MeterCategoryID == 0)
{
msg += "<p>第" + (i + 2) + "行上传失败。原因：仪表种类不存在</p>";
ImportFailed = true;
break;
}
projectbiao.MeterType = ChargeMeter_Project.GetMeterTypeID(table.Rows[i]["仪表类型"].ToString());
if (projectbiao.MeterType == 0)
{
msg += "<p>第" + (i + 2) + "行上传失败。原因：仪表类型不存在</p>";
ImportFailed = true;
break;
}
projectbiao.MeterSpec = ChargeMeter_Project.GetMeterSpec(table.Rows[i]["仪表规格"].ToString());
if (projectbiao.MeterSpec == 0)
{
msg += "<p>第" + (i + 2) + "行上传失败。原因：仪表规格不存在</p>";
ImportFailed = true;
break;
}
projectbiao.MeterCoefficient = WebUtil.GetDecimalByStr(table.Rows[i]["倍率"].ToString());
projectbiao.MeterHouseNo = table.Rows[i]["缴费户号"].ToString();
projectbiao.MeterLocation = table.Rows[i]["仪表位置"].ToString();
projectbiao.MeterRemark = table.Rows[i]["备注"].ToString();
projectbiao.SortOrder = WebUtil.GetIntByStr(table.Rows[i]["排序序号"].ToString());
projectbiao.MeterStartPoint = WebUtil.GetDecimalByStr(table.Rows[i]["上次读数"].ToString());
projectbiao.MeterEndPoint = WebUtil.GetDecimalByStr(table.Rows[i]["本次读数"].ToString());
projectbiao.Save(helper);
TotalNumber = i;
}
}
#endregion
if (!ImportFailed)
{
helper.Commit();
msg += "<p>导入完成</p>";
}
else
{
helper.Rollback();
msg += "<p>导入失败</p>";
}
}
catch (Exception ex)
{
LogHelper.WriteError("ImportGongTanHandler", "visit: importmeterdata", ex);
helper.Rollback();
msg += "<p>第" + (TotalNumber + 2) + "行上传失败。原因：" + ex.Message + "</p>";
}
context.Response.Write(msg);
}
}
private void importgongtan(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;
}
string msg = string.Empty;
int CompanyID = WebUtil.GetCompanyID(context);
int CreatorID = WebUtil.GetUser(context).UserID;
string AddMan = WebUtil.GetUser(context).RealName;
bool ImportFailed = false;
var summarylist = ChargeSummary.GetChargeSummaries().ToArray();
titleList = Foresight.DataAccess.TableColumn.GetTableColumnByPageCode("roomfeesource", true).Where(p => !p.ColumnName.Equals("选择按钮")).ToArray();
using (SqlHelper helper = new SqlHelper())
{
try
{
helper.BeginTransaction();
#region 导入处理
for (int j = 0; j < uploadFiles.Count; j++)
{
HttpPostedFile postedFile = uploadFiles[j];
string filepath = HttpContext.Current.Server.MapPath("~/upload/ImportChaoBiao/" + 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);
DataTable table = ExcelExportHelper.NPOIReadExcel(fullpath);
for (int i = 0; i < table.Rows.Count; i++)
{
Project project = null;
object Value, FullName, RoomName;
if (GetColumnValue("房源信息", table, i, out FullName) && GetColumnValue("房间号", table, i, out RoomName))
{
project = Project.GetProjectByFullName(RoomName.ToString(), FullName.ToString(), CompanyID, helper);
}
if (project == null)
{
msg += "<p>第" + (i + 2) + "行上传失败。原因：房间资源不存在</p>";
ImportFailed = true;
break;
}
ChargeSummary summary = null;
if (GetColumnValue("收费项目", table, i, out Value))
{
summary = summarylist.FirstOrDefault(p => p.CompanyID == CompanyID && p.Name.Equals(Value.ToString()));
}
if (summary == null)
{
msg += "<p>第" + (i + 2) + "行上传失败。原因：收费项目项目不存在</p>";
ImportFailed = true;
break;
}
int ID = 0;
int.TryParse(table.Rows[i]["账单ID"].ToString(), out ID);
Foresight.DataAccess.ImportFee importFee = null;
if (ID > 0)
{
importFee = Foresight.DataAccess.ImportFee.GetOrCreateImportFeeByID(ID, helper, CanCreate: false);
}
if (importFee == null)
{
importFee = new Foresight.DataAccess.ImportFee();
importFee.ChargeStatus = 0;
importFee.AddTime = DateTime.Now;
importFee.ChargeID = summary.ID;
importFee.RoomID = project.ID;
importFee.ChargeStatus = 2;
}
if (importFee.ChargeStatus == 1)
{
msg += "<p>第" + (i + 2) + "行上传失败。原因：该费用已收取</p>";
ImportFailed = true;
break;
}
if (GetColumnValue("上次读数", table, i, out Value))
{
importFee.StartPoint = GetDecimalValue(Value);
}
if (GetColumnValue("本次读数", table, i, out Value))
{
importFee.EndPoint = GetDecimalValue(Value);
}
decimal totalpoint = decimal.MinValue;
if (GetColumnValue("用量", table, i, out Value))
{
totalpoint = GetDecimalValue(Value);
}
if (totalpoint == decimal.MinValue)
{
totalpoint = (importFee.EndPoint == decimal.MinValue ? 0 : importFee.EndPoint) - (importFee.StartPoint == decimal.MinValue ? 0 : importFee.StartPoint);
}
totalpoint = totalpoint < 0 ? 0 : totalpoint;
importFee.TotalPoint = totalpoint;
if (GetColumnValue("单价", table, i, out Value))
{
importFee.UnitPrice = GetDecimalValue(Value);
}
if (GetColumnValue("系数", table, i, out Value))
{
importFee.ImportCoefficient = GetDecimalValue(Value);
}
decimal totalprice = decimal.MinValue;
if (GetColumnValue("金额", table, i, out Value))
{
totalprice = GetDecimalValue(Value);
}
if (totalprice == decimal.MinValue)
{
totalprice = (importFee.TotalPoint == decimal.MinValue ? 0 : importFee.TotalPoint) * (importFee.ImportCoefficient == decimal.MinValue ? 0 : importFee.ImportCoefficient) * (importFee.UnitPrice == decimal.MinValue ? 0 : importFee.UnitPrice);
}
importFee.TotalPrice = totalprice;
if (GetColumnValue("收费状态", table, i, out Value))
{
importFee.ChargeStatus = Value.ToString().Equals("已收") ? 1 : (Value.ToString().Equals("未收") ? 0 : 2);
}
if (GetColumnValue("账单日期", table, i, out Value))
{
importFee.WriteDate = GetDateTimeValue(Value);
}
if (GetColumnValue("计费开始日期", table, i, out Value))
{
importFee.StartTime = GetDateTimeValue(Value);
}
if (GetColumnValue("计费结束日期", table, i, out Value))
{
importFee.EndTime = GetDateTimeValue(Value);
}
string ImportBiaoCategory = string.Empty;
string ImportBiaoName = project.Name;
string ImportChargeRoomNo = string.Empty;
if (GetColumnValue("表种类", table, i, out Value))
{
ImportBiaoCategory = Value.ToString();
}
if (GetColumnValue("表名称", table, i, out Value))
{
ImportBiaoName = Value.ToString();
}
if (GetColumnValue("缴费户号", table, i, out Value))
{
ImportChargeRoomNo = Value.ToString();
}
Foresight.DataAccess.ImportFee lastimportFee = Foresight.DataAccess.ImportFee.GetImportFeeByRoomID(project.ID, null, importFee.ChargeID, helper);
if (string.IsNullOrEmpty(ImportBiaoCategory))
{
ImportBiaoCategory = summary.BiaoCategory;
if (lastimportFee != null && !string.IsNullOrEmpty(lastimportFee.ImportBiaoCategory))
{
ImportBiaoCategory = lastimportFee.ImportBiaoCategory;
}
}
if (string.IsNullOrEmpty(ImportBiaoName))
{
ImportBiaoName = project.Name;
if (lastimportFee != null && !string.IsNullOrEmpty(lastimportFee.ImportBiaoName))
{
ImportBiaoName = lastimportFee.ImportBiaoName;
}
}
if (string.IsNullOrEmpty(ImportChargeRoomNo))
{
if (lastimportFee != null && !string.IsNullOrEmpty(lastimportFee.ImportChargeRoomNo))
{
ImportChargeRoomNo = lastimportFee.ImportChargeRoomNo;
}
}
importFee.ImportBiaoCategory = ImportBiaoCategory;
importFee.ImportBiaoName = ImportBiaoName;
importFee.ImportChargeRoomNo = ImportChargeRoomNo;
importFee.Save(helper);
if (importFee.ChargeStatus == 0)
{
SaveRoomFee(importFee, summary.ID, helper);
}
else if (importFee.ChargeStatus == 1)
{
SaveRoomHistoryFee(importFee, summary.ID, helper, AddMan);
}
}
}
#endregion
if (!ImportFailed)
{
helper.Commit();
msg += "<p>导入完成</p>";
}
else
{
helper.Rollback();
msg += "<p>导入失败</p>";
}
}
catch (Exception ex)
{
LogHelper.WriteError("ImportGongTanHandler", "visit: importgongtan", ex);
helper.Rollback();
}
context.Response.Write(msg);
}
}
private void importtaizhang(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;
}
string msg = string.Empty;
bool ImportFailed = false;
int TotalNumber = 0;
using (SqlHelper helper = new SqlHelper())
{
try
{
helper.BeginTransaction();
#region 导入处理
for (int j = 0; j < uploadFiles.Count; j++)
{
HttpPostedFile postedFile = uploadFiles[j];
string filepath = HttpContext.Current.Server.MapPath("~/upload/ImportChaoBiao/" + 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);
DataTable table = ExcelExportHelper.NPOIReadExcel(fullpath);
for (int i = 0; i < table.Rows.Count; i++)
{
int ID = 0;
int.TryParse(table.Rows[i]["表台帐ID"].ToString(), out ID);
Foresight.DataAccess.Project_Biao projectbiao = null;
if (ID > 0)
{
projectbiao = Foresight.DataAccess.Project_Biao.GetProject_Biao(ID, helper);
}
if (projectbiao == null)
{
msg += "<p>第" + (i + 2) + "行上传失败。原因：表台帐不存在</p>";
ImportFailed = true;
break;
}
projectbiao.BiaoName = table.Rows[i]["表名称"].ToString();
projectbiao.BiaoCategory = table.Rows[i]["表种类"].ToString();
projectbiao.BiaoGuiGe = table.Rows[i]["表规格"].ToString();
projectbiao.ChargeRoomNo = table.Rows[i]["缴费户号"].ToString();
decimal Rate = 0;
decimal.TryParse(table.Rows[i]["倍率"].ToString(), out Rate);
Rate = Rate > 0 ? Rate : 0;
projectbiao.Rate = Rate;
decimal StartPoint = 0;
decimal.TryParse(table.Rows[i]["上次读数"].ToString(), out StartPoint);
StartPoint = StartPoint > 0 ? StartPoint : 0;
projectbiao.StartPoint = StartPoint;
decimal EndPoint = 0;
decimal.TryParse(table.Rows[i]["本次读数"].ToString(), out EndPoint);
EndPoint = EndPoint > 0 ? EndPoint : 0;
projectbiao.EndPoint = EndPoint;
decimal ReducePoint = 0;
decimal.TryParse(table.Rows[i]["扣减用量"].ToString(), out ReducePoint);
ReducePoint = ReducePoint > 0 ? ReducePoint : 0;
projectbiao.ReducePoint = ReducePoint;
decimal TotalPoint = 0;
decimal.TryParse(table.Rows[i]["本次用量"].ToString(), out TotalPoint);
if (TotalPoint <= 0)
{
TotalPoint = (EndPoint - StartPoint) * (Rate > 0 ? Rate : 0) - (ReducePoint > 0 ? ReducePoint : 0);
}
TotalPoint = TotalPoint > 0 ? TotalPoint : 0;
projectbiao.TotalPoint = TotalPoint;
projectbiao.Remark = table.Rows[i]["备注"].ToString();
projectbiao.IsActive = table.Rows[i]["作废状态"].ToString().Equals("生效") ? true : false;
decimal ImportCoefficient = 0;
decimal.TryParse(table.Rows[i]["系数"].ToString(), out ImportCoefficient);
if (ImportCoefficient > 0)
{
projectbiao.Coefficient = ImportCoefficient;
}
decimal UnitPrice = 0;
decimal.TryParse(table.Rows[i]["单价"].ToString(), out UnitPrice);
if (UnitPrice > 0)
{
projectbiao.UnitPrice = UnitPrice;
}
projectbiao.Save(helper);
string cmdtex_2 = string.Empty;
if (projectbiao.Coefficient > 0)
{
cmdtex_2 += ",[ImportCoefficient]='" + projectbiao.Coefficient + "'";
}
if (projectbiao.UnitPrice > 0)
{
cmdtex_2 += ",[UnitPrice]='" + projectbiao.UnitPrice + "'";
}
string cmdtext = "update [ImportFee] set [ImportBiaoCategory]='" + projectbiao.BiaoCategory + "',[ImportBiaoName]='" + projectbiao.BiaoName + "',[StartPoint]='" + StartPoint + "',[EndPoint]='" + EndPoint + "',[TotalPoint]='" + TotalPoint + "' " + cmdtex_2 + " where ID in (select top 1 ID from [ImportFee] where [ProjectBiaoID]=" + projectbiao.ID + " and RoomID ='" + projectbiao.ProjectID + "' order by [WriteDate] desc);";
helper.Execute(cmdtext, CommandType.Text, new List <SqlParameter>());
TotalNumber = i;
}
}
#endregion
if (!ImportFailed)
{
helper.Commit();
msg += "<p>导入完成</p>";
}
else
{
helper.Rollback();
msg += "<p>导入失败</p>";
}
}
catch (Exception ex)
{
LogHelper.WriteError("ImportGongTanHandler", "visit: importtaizhang", ex);
helper.Rollback();
msg += "<p>第" + (TotalNumber + 2) + "行上传失败。原因：" + ex.Message + "</p>";
}
context.Response.Write(msg);
}
}
private void importthirdcustomer(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);
DataTable table = ExcelExportHelper.NPOIReadExcel(fullpath);
string msg = string.Empty;
int count = 0;
var user = WebUtil.GetUser(context);
using (SqlHelper helper = new SqlHelper())
{
try
{
helper.BeginTransaction();
#region 导入处理
for (int i = 0; i < table.Rows.Count; i++)
{
count = i;
ThirdCustomer data = null;
string PhoneNumber = table.Rows[i]["手机号码"].ToString();
if (string.IsNullOrEmpty(PhoneNumber))
{
msg += "<p>第" + (i + 2) + "行上传失败。原因：手机号码为空</p>";
continue;
}
object Value = table.Rows[i]["ID"];
int ID = 0;
if (Value != null)
{
int.TryParse(Value.ToString(), out ID);
}
if (ID > 0)
{
data = Foresight.DataAccess.ThirdCustomer.GetThirdCustomer(ID, helper);
}
if (data == null)
{
data = ThirdCustomer.GetThirdCustomerByPhone(PhoneNumber, helper);
}
if (data == null)
{
data = new ThirdCustomer();
data.AddTime = DateTime.Now;
data.AddUserID = user.UserID;
}
data.PhoneNumber = PhoneNumber;
data.CustomerName = table.Rows[i]["项目名称"].ToString();
data.ProjectName = table.Rows[i]["姓名"].ToString();
data.RoomName = table.Rows[i]["资源编号"].ToString();
data.SignDate = WebUtil.GetDateTimeByStr(table.Rows[i]["签约日期"].ToString());
data.Save(helper);
}
#endregion
helper.Commit();
msg += "<p>导入完成</p>";
}
catch (Exception ex)
{
LogHelper.WriteError("ImportSourceHandler", "visit: importroomsource", ex);
msg = "导入失败，第" + (count + 2) + "行数据有问题，导入取消";
helper.Rollback();
}
}
context.Response.Write(msg);
}
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);
}
}
private void import(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;
}
string msg = string.Empty;
int CompanyID = int.Parse(context.Request.Params["CompanyID"]);
int CreatorID = int.Parse(context.Request.Params["CreatorID"]);
string AddMan = context.Request.Params["AddMan"];
bool ImportFailed = false;
int TotalNumber = 0;
titleList = Foresight.DataAccess.TableColumn.GetTableColumnByPageCode("insidecustomersource", true).Where(p => (!p.ColumnName.Equals("选择按钮") && !p.ColumnName.Equals("合同附件"))).ToArray();
using (SqlHelper helper = new SqlHelper())
{
try
{
helper.BeginTransaction();
#region 导入处理
for (int j = 0; j < uploadFiles.Count; j++)
{
HttpPostedFile postedFile = uploadFiles[j];
string filepath = HttpContext.Current.Server.MapPath("~/upload/Customer/" + 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);
DataTable table = ExcelExportHelper.NPOIReadExcel(fullpath);
for (int i = 0; i < table.Rows.Count; i++)
{
Foresight.DataAccess.InsideCustomer customer = null;
object Value = null, CustomerName = null, ContactPhone = null;
if (GetColumnValue("客户名称", table, i, out CustomerName))
{
if (!string.IsNullOrEmpty(CustomerName.ToString()))
{
customer = Foresight.DataAccess.InsideCustomer.GeInsideCustomerByParams(CustomerName.ToString(), string.Empty, helper);
}
}
if (customer != null)
{
msg += "<p>第" + (i + 2) + "行上传失败。原因：客户已存在</p>";
ImportFailed = true;
break;
}
if (GetColumnValue("联系方式", table, i, out ContactPhone))
{
if (!string.IsNullOrEmpty(ContactPhone.ToString()))
{
customer = Foresight.DataAccess.InsideCustomer.GeInsideCustomerByParams(string.Empty, ContactPhone.ToString(), helper);
}
}
if (customer != null)
{
msg += "<p>第" + (i + 2) + "行上传失败。原因：客户已存在</p>";
ImportFailed = true;
break;
}
customer = new Foresight.DataAccess.InsideCustomer();
if (GetColumnValue("客户名称", table, i, out Value))
{
customer.CustomerName = Value.ToString();
}
if (GetColumnValue("行业", table, i, out Value))
{
customer.IndustryName = Value.ToString();
}
if (GetColumnValue("分类", table, i, out Value))
{
customer.CategoryName = Value.ToString();
}
if (GetColumnValue("意向分析", table, i, out Value))
{
customer.Interesting = Value.ToString();
}
if (GetColumnValue("联系人", table, i, out Value))
{
customer.ContactMan = Value.ToString();
}
if (GetColumnValue("联系方式", table, i, out Value))
{
customer.ContactPhone = Value.ToString();
}
if (GetColumnValue("QQ", table, i, out Value))
{
customer.QQNo = Value.ToString();
}
if (GetColumnValue("QQ群邀约", table, i, out Value))
{
customer.QQGroupInvitation = Value.ToString();
}
if (GetColumnValue("微信", table, i, out Value))
{
customer.WechatNo = Value.ToString();
}
if (GetColumnValue("微信群邀约", table, i, out Value))
{
customer.WechaGroupInvitation = Value.ToString();
}
if (GetColumnValue("其他联系人", table, i, out Value))
{
customer.OtherContactMan = Value.ToString();
}
if (GetColumnValue("客户所有者", table, i, out Value))
{
customer.CustomerBelonger = Value.ToString();
}
if (GetColumnValue("跟进日期", table, i, out Value))
{
DateTime NewFollowupDate = DateTime.MinValue;
DateTime.TryParse(Value.ToString(), out NewFollowupDate);
customer.NewFollowupDate = NewFollowupDate;
}
if (GetColumnValue("跟进记录", table, i, out Value))
{
customer.NewFollowup = Value.ToString();
}
if (GetColumnValue("区域", table, i, out Value))
{
customer.Region = Value.ToString();
}
if (GetColumnValue("省区", table, i, out Value))
{
customer.Province = Value.ToString();
}
if (GetColumnValue("城市", table, i, out Value))
{
customer.City = Value.ToString();
}
if (GetColumnValue("商务阶段", table, i, out Value))
{
customer.BusinessStage = Value.ToString();
}
if (GetColumnValue("报价", table, i, out Value))
{
decimal Cost = decimal.MinValue;
decimal.TryParse(Value.ToString(), out Cost);
customer.Cost = Cost;
}
if (GetColumnValue("成交可能性", table, i, out Value))
{
customer.DealProbably = Value.ToString();
}
if (GetColumnValue("备注", table, i, out Value))
{
customer.Remark = Value.ToString();
}
customer.AddTime = DateTime.Now;
customer.AddMan = WebUtil.GetUser(context).RealName;
customer.Save(helper);
TotalNumber = i;
}
}
#endregion
if (!ImportFailed)
{
helper.Commit();
msg += "<p>导入完成</p>";
}
else
{
helper.Rollback();
msg += "<p>导入失败</p>";
}
}
catch (Exception ex)
{
LogHelper.WriteError("ImportCustomerHandler", "visit: import", ex);
helper.Rollback();
msg += "<p>第" + (TotalNumber + 2) + "行上传失败。原因：" + ex.Message + "</p>";
}
context.Response.Write(msg);
}
}
