public static ImportFee GetOrCreateImportFeeByID(int ID, bool CanCreate = true)
{
ImportFee data = null;
using (SqlHelper helper = new SqlHelper())
{
try
{
helper.BeginTransaction();
data = GetOrCreateImportFeeByID(ID, helper, CanCreate: CanCreate);
helper.Commit();
}
catch (Exception)
{
helper.Rollback();
}
}
return(data);
}
public static ImportFee GetOrCreateImportFeeByID(int ID, SqlHelper helper, bool CanCreate = true)
{
var data = ImportFee.GetImportFee(ID, helper);
if (!CanCreate)
{
return(data);
}
if (data != null)
{
return(data);
}
#region 除备份还原
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("@ID", ID));
var bak_data = GetOne <ImportFeeBak>("select * from [ImportFeeBak] where [ID] = @ID", parameters, helper);
if (bak_data != null)
{
string cmdtext = string.Empty;
cmdtext += "SET IDENTITY_INSERT [ImportFee] ON;";
cmdtext += @"
insert into [ImportFee]
([ID]
,[RoomID]
,[ChargeDate]
,[ChargeID]
,[StartPoint]
,[EndPoint]
,[TotalPoint]
,[UnitPrice]
,[TotalPrice]
,[WriteDate]
,[StartTime]
,[EndTime]
,[AddTime]
,[ChargeStatus]
,[ImportCoefficient]
,[ImportBiaoCategory]
,[ImportBiaoName]
,[ChargeBiaoID]
,[ProjectBiaoID]
,[ImportBiaoGuiGe]
,[ImportRate]
,[ImportReducePoint]
,[ImportChargeRoomNo])
select
[ID]
,[RoomID]
,[ChargeDate]
,[ChargeID]
,[StartPoint]
,[EndPoint]
,[TotalPoint]
,[UnitPrice]
,[TotalPrice]
,[WriteDate]
,[StartTime]
,[EndTime]
,getdate()
,[ChargeStatus]
,[ImportCoefficient]
,[ImportBiaoCategory]
,[ImportBiaoName]
,[ChargeBiaoID]
,[ProjectBiaoID]
,[ImportBiaoGuiGe]
,[ImportRate]
,[ImportReducePoint]
,[ImportChargeRoomNo]
from [ImportFeeBak] where ID=@ID;";
cmdtext += @"SET IDENTITY_INSERT [ImportFee] OFF;";
int count = helper.Execute(cmdtext, CommandType.Text, parameters);
if (count > 0)
{
data = ImportFee.GetImportFee(ID, helper);
}
}
#endregion
#region 账单明细还原
if (data == null)
{
var roomfee = RoomFee.GetRoomFeeByImportFeeID(ID, helper);
if (roomfee != null)
{
decimal EndPoint = roomfee.UseCount > 0 ? roomfee.UseCount : 0;
decimal UnitPrice = roomfee.UnitPrice > 0 ? roomfee.UnitPrice : 0;
decimal RealCost = roomfee.RealCost > 0 ? roomfee.RealCost : 0;
string WriteDate = "'" + (roomfee.RoomFeeWriteDate > DateTime.MinValue ? roomfee.RoomFeeWriteDate.ToString("yyyy-MM-dd") : DateTime.Now.ToString("yyyy-MM-dd")) + "'";
string StartTime = roomfee.StartTime > DateTime.MinValue ? "'" + roomfee.StartTime.ToString("yyyy-MM-dd") + "'" : "NULL";
string EndTime = roomfee.EndTime > DateTime.MinValue ? "'" + roomfee.EndTime.ToString("yyyy-MM-dd") + "'" : "NULL";
decimal ImportCoefficient = roomfee.RoomFeeCoefficient > 0 ? roomfee.RoomFeeCoefficient : 0;
string cmdtext = string.Empty;
cmdtext += "SET IDENTITY_INSERT [ImportFee] ON;";
cmdtext += @"insert into [ImportFee]
([ID]
,[RoomID]
,[ChargeDate]
,[ChargeID]
,[StartPoint]
,[EndPoint]
,[TotalPoint]
,[UnitPrice]
,[TotalPrice]
,[WriteDate]
,[StartTime]
,[EndTime]
,[AddTime]
,[ChargeStatus]
,[ImportCoefficient]
,[ImportBiaoCategory]
,[ImportBiaoName]
,[ChargeBiaoID]
,[ProjectBiaoID]
,[ImportBiaoGuiGe]
,[ImportRate]
,[ImportReducePoint]
,[ImportChargeRoomNo])
values(
" + ID + @"
," + roomfee.RoomID + @"
,NULL
," + roomfee.ChargeID + @"
,0
," + EndPoint + @"
," + EndPoint + @"
," + UnitPrice + @"
," + RealCost + @"
," + WriteDate + @"
," + StartTime + @"
," + EndTime + @"
,getdate()
,0
," + ImportCoefficient + @"
,NULL
,NULL
,0
,0
,NULL
,0
,0
,NULL
);";
cmdtext += @"SET IDENTITY_INSERT [ImportFee] OFF;";
int count = helper.Execute(cmdtext, CommandType.Text, new List <SqlParameter>());
if (count > 0)
{
data = ImportFee.GetImportFee(ID, helper);
}
}
}
#endregion
#region 历史单据还原
if (data == null)
{
var roomfee = RoomFeeHistory.GetRoomFeeHistoryByImportFeeID(ID, helper);
if (roomfee != null)
{
decimal EndPoint = roomfee.UseCount > 0 ? roomfee.UseCount : 0;
decimal UnitPrice = roomfee.UnitPrice > 0 ? roomfee.UnitPrice : 0;
decimal RealCost = roomfee.RealCost > 0 ? roomfee.RealCost : 0;
string WriteDate = "'" + (roomfee.RoomFeeWriteDate > DateTime.MinValue ? roomfee.RoomFeeWriteDate.ToString("yyyy-MM-dd") : DateTime.Now.ToString("yyyy-MM-dd")) + "'";
string StartTime = roomfee.StartTime > DateTime.MinValue ? "'" + roomfee.StartTime.ToString("yyyy-MM-dd") + "'" : "NULL";
string EndTime = roomfee.EndTime > DateTime.MinValue ? "'" + roomfee.EndTime.ToString("yyyy-MM-dd") + "'" : "NULL";
decimal ImportCoefficient = roomfee.RoomFeeCoefficient > 0 ? roomfee.RoomFeeCoefficient : 0;
string cmdtext = string.Empty;
cmdtext += "SET IDENTITY_INSERT [ImportFee] ON;";
cmdtext += @"insert into [ImportFee]
([ID]
,[RoomID]
,[ChargeDate]
,[ChargeID]
,[StartPoint]
,[EndPoint]
,[TotalPoint]
,[UnitPrice]
,[TotalPrice]
,[WriteDate]
,[StartTime]
,[EndTime]
,[AddTime]
,[ChargeStatus]
,[ImportCoefficient]
,[ImportBiaoCategory]
,[ImportBiaoName]
,[ChargeBiaoID]
,[ProjectBiaoID]
,[ImportBiaoGuiGe]
,[ImportRate]
,[ImportReducePoint]
,[ImportChargeRoomNo])
values(
" + ID + @"
," + roomfee.RoomID + @"
,NULL
," + roomfee.ChargeID + @"
,0
," + EndPoint + @"
," + EndPoint + @"
," + UnitPrice + @"
," + RealCost + @"
," + WriteDate + @"
," + StartTime + @"
," + EndTime + @"
,getdate()
,0
," + ImportCoefficient + @"
,NULL
,NULL
,0
,0
,NULL
,0
,0
,NULL
);";
cmdtext += @"SET IDENTITY_INSERT [ImportFee] OFF;";
int count = helper.Execute(cmdtext, CommandType.Text, new List <SqlParameter>());
if (count > 0)
{
data = ImportFee.GetImportFee(ID, helper);
}
}
}
#endregion
return(data);
}
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);
}
}