public static void GetRoomFeeRoomIDList(int RoomID, out int PreRoomID, out int NextRoomID, out int CurrentRoomID, string keywords = "", List <int> ALLProjectIDList = null, int UserID = 0, int ProjectID = 0)
{
PreRoomID = 0;
NextRoomID = 0;
CurrentRoomID = 0;
var parameters = new List <SqlParameter>();
var conditions = new List <string>();
conditions.Add("1=1");
var list = new RoomFee[] { };
if (ALLProjectIDList != null && ALLProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ALLProjectIDList, IncludeRelation: false, RoomIDName: "[RoomID]", UserID: UserID);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (!string.IsNullOrEmpty(keywords))
{
conditions.Add("([Project].[Name] like @keywords or [Project].[FullName] like @keywords)");
parameters.Add(new SqlParameter("@keywords", "%" + keywords + "%"));
}
if (ProjectID > 0)
{
conditions.Add("([Project].[ID]=@ProjectID or [Project].[AllParentID] like @AllParentID)");
parameters.Add(new SqlParameter("@ProjectID", ProjectID));
parameters.Add(new SqlParameter("@AllParentID", "%," + ProjectID + ",%"));
}
list = GetList <RoomFee>("select [RoomFee].[RoomID] from [RoomFee] left join [Project] on [Project].ID=[RoomFee].RoomID where " + string.Join(" and ", conditions.ToArray()) + " order by [Project].DefaultOrder asc", parameters).ToArray();
if (list.Length == 0)
{
return;
}
var ProjectIDList = list.Select(p => p.RoomID).Distinct().ToArray();
if (RoomID > 0)
{
for (int i = 0; i < ProjectIDList.Length; i++)
{
if (RoomID == ProjectIDList[i])
{
CurrentRoomID = RoomID;
if (i > 0)
{
PreRoomID = ProjectIDList[i - 1];
}
if (i < ProjectIDList.Length - 1)
{
NextRoomID = ProjectIDList[i + 1];
}
}
}
return;
}
CurrentRoomID = ProjectIDList[0];
if (ProjectIDList.Length > 1)
{
NextRoomID = ProjectIDList[1];
}
}
public static string GetActiveImportFeeCmdText(ViewImportFeeDetail2[] list)
{
var roomfee_list = RoomFee.GetRoomFeeListByImportFeeIDList(list.Select(p => p.ID).ToList());
string cmdtext = string.Empty;
List <int> NewIDList = new List <int>();
foreach (var importFee in list)
{
var roomfee = roomfee_list.FirstOrDefault(p => p.ImportFeeID == importFee.ID);
decimal UnitPrice = importFee.FinalUnitPrice > 0 ? importFee.FinalUnitPrice : 0;
decimal TotalPoint = importFee.RealTotalPoint > 0 ? importFee.RealTotalPoint : 0;
if (importFee.FinalTotalPrice <= 0)
{
continue;
}
NewIDList.Add(importFee.ID);
string StartTime = importFee.StartTime == DateTime.MinValue ? "NULL" : "'" + importFee.StartTime.ToString("yyyy-MM-dd") + "'";
string EndTime = importFee.EndTime == DateTime.MinValue ? "NULL" : "'" + importFee.EndTime.ToString("yyyy-MM-dd") + "'";
decimal RoomFeeCoefficient = importFee.Coefficient > 0 ? importFee.Coefficient : 1;
string RoomFeeWriteDate = importFee.WriteDate == DateTime.MinValue ? "NULL" : "'" + importFee.WriteDate.ToString("yyyy-MM-dd") + "'";
decimal RoomFeeStartPoint = importFee.StartPoint > 0 ? importFee.StartPoint : 0;
decimal RoomFeeEndPoint = importFee.EndPoint > 0 ? importFee.EndPoint : 0;
if (roomfee == null)
{
cmdtext += @"INSERT INTO [dbo].[RoomFee]
([RoomID]
,[UseCount]
,[StartTime]
,[EndTime]
,[Cost]
,[AddTime]
,[IsCharged]
,[ChargeID]
,[IsStart]
,[ImportFeeID]
,[UnitPrice]
,[ChargeFee]
,[ChargeFeeID]
,[IsImportFee]
,[RoomFeeCoefficient]
,[RoomFeeWriteDate]
,[RoomFeeStartPoint]
,[RoomFeeEndPoint]
)
VALUES
(" + importFee.RoomID + "," + TotalPoint + "," + StartTime + "," + EndTime + "," + importFee.TotalPrice + ",getdate(),0," + importFee.ChargeID + ",1," + importFee.ID + "," + UnitPrice + ",0,0,1," + RoomFeeCoefficient + "," + RoomFeeWriteDate + "," + RoomFeeStartPoint + "," + RoomFeeEndPoint + ");";
}
else
{
cmdtext += "update RoomFee set [UseCount]=" + TotalPoint + ",[StartTime]="
+ StartTime + ",[EndTime]=" + EndTime + ",[Cost]=" + importFee.TotalPrice + ",[UnitPrice]=" + UnitPrice + ",[RoomFeeCoefficient]=" + RoomFeeCoefficient + ",[RoomFeeWriteDate]=" + RoomFeeWriteDate + ",[IsImportFee]=1,[RoomFeeStartPoint]=" + RoomFeeStartPoint + ",[RoomFeeEndPoint]=" + RoomFeeEndPoint + " where ID=" + roomfee.ID + ";";
}
}
if (NewIDList.Count > 0)
{
cmdtext += "update [ImportFee] set [ChargeStatus]=0 where ID in (" + string.Join(" , ", NewIDList.ToArray()) + ");";
}
return(cmdtext);
}
public static RoomFee SetInfo_RoomFee(int RoomID, DateTime StartTime, DateTime EndTime, decimal Cost, decimal RealCost, decimal UnitPrice, int ChargeID, RoomFee data = null, decimal UseCount = 0, string Remark = "", bool IsCharged = false, int ChargeFeeID = 0, bool IsStart = true, decimal Discount = 0, decimal ChargeFee = 0, decimal TotalRealCost = 0, decimal TotalDiscountCost = 0, decimal RestCost = 0, int ContractID = 0, int RelatedFeeID = 0, int DefaultChargeManID = 0, string DefaultChargeManName = "", int Contract_RoomChargeID = 0, int ContractDivideID = 0, bool cansave = false, DateTime?NewEndTime = null, bool OnlyOnceCharge = false, int OutDays = 0, int DiscountID = 0, DateTime?CuiShouStartTime = null, DateTime?CuiShouEndTime = null, int ChongDiChargeID = 0, string TradeNo = "", int OrderID = 0, bool IsTempFee = false, bool IsMeterFee = false, bool IsImportFee = false, bool IsCycleFee = false, int ImportFeeID = 0, decimal RoomFeeCoefficient = 0, DateTime?RoomFeeWriteDate = null, int ChargeMeterProjectFeeID = 0, decimal RoomFeeStartPoint = 0, decimal RoomFeeEndPoint = 0, SqlHelper helper = null, int HistoryRoomFeeID = 0)
{
if (data == null)
{
data = new RoomFee();
data.AddTime = DateTime.Now;
data.AddUserName = User.GetCurrentUserName();
}
data.RoomID = RoomID;
data.UseCount = UseCount;
data.StartTime = StartTime;
data.EndTime = EndTime;
data.Cost = Cost;
data.RealCost = RealCost;
data.Remark = Remark;
data.IsCharged = IsCharged;
data.ChargeFeeID = 0;
data.ChargeID = ChargeID;
data.IsStart = IsStart;
data.UnitPrice = UnitPrice;
data.Discount = Discount;
data.ChargeFee = ChargeFee;
data.TotalRealCost = TotalRealCost;
data.TotalDiscountCost = TotalDiscountCost;
data.RestCost = RestCost;
data.ContractID = ContractID;
data.RelatedFeeID = RelatedFeeID;
if (DefaultChargeManID <= 0)
{
RoomPhoneRelation default_relation = null;
if (helper == null)
{
default_relation = RoomPhoneRelation.GetDefaultInChargeFeeRoomPhoneRelation(data.RoomID, data.ContractID);
}
else
{
default_relation = RoomPhoneRelation.GetDefaultInChargeFeeRoomPhoneRelation(data.RoomID, data.ContractID, helper);
}
if (default_relation != null)
{
DefaultChargeManID = default_relation.ID;
DefaultChargeManName = default_relation.RelationName;
}
}
data.DefaultChargeManID = DefaultChargeManID;
data.DefaultChargeManName = DefaultChargeManName;
data.Contract_RoomChargeID = Contract_RoomChargeID;
data.ContractDivideID = ContractDivideID;
if (NewEndTime != null && NewEndTime.HasValue)
{
data.NewEndTime = Convert.ToDateTime(NewEndTime);
}
data.OnlyOnceCharge = OnlyOnceCharge;
data.OutDays = OutDays;
data.DiscountID = DiscountID;
if (CuiShouStartTime != null && CuiShouStartTime.HasValue)
{
data.CuiShouStartTime = Convert.ToDateTime(CuiShouStartTime);
}
if (CuiShouEndTime != null && CuiShouEndTime.HasValue)
{
data.CuiShouEndTime = Convert.ToDateTime(CuiShouEndTime);
}
data.ChongDiChargeID = ChongDiChargeID;
data.TradeNo = TradeNo;
data.OrderID = OrderID;
data.IsTempFee = IsTempFee;
data.IsMeterFee = IsMeterFee;
data.IsImportFee = IsImportFee;
data.IsCycleFee = IsCycleFee;
data.ImportFeeID = ImportFeeID;
data.RoomFeeCoefficient = RoomFeeCoefficient;
data.ChargeMeterProjectFeeID = ChargeMeterProjectFeeID;
data.RoomFeeStartPoint = RoomFeeStartPoint;
data.RoomFeeEndPoint = RoomFeeEndPoint;
if (RoomFeeWriteDate != null && RoomFeeWriteDate.HasValue)
{
data.RoomFeeWriteDate = Convert.ToDateTime(RoomFeeWriteDate);
}
if (cansave)
{
if (helper == null)
{
using (helper = new SqlHelper())
{
try
{
helper.BeginTransaction();
data.Save(helper);
ChargeFeePriceRange.CreateChargeFeePriceRangeByImportFeeIDList(helper, RoomFeeID: data.ID, HistoryRoomFeeID: HistoryRoomFeeID);
helper.Commit();
}
catch (Exception)
{
helper.Rollback();
}
}
}
else
{
data.Save(helper);
ChargeFeePriceRange.CreateChargeFeePriceRangeByImportFeeIDList(helper, RoomFeeID: data.ID, HistoryRoomFeeID: HistoryRoomFeeID);
}
}
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);
}
public static bool Save_ChargeMeter_ProjectFee(int[] MeterProjectIDList, DateTime WriteDate, decimal Coefficient, decimal SummaryUnitPrice, DateTime StartTime, DateTime EndTime, string AddUserName, out string errormsg, List <int> ChargeIDList)
{
errormsg = string.Empty;
var meter_project_list = ChargeMeter_ProjectDetail.GetChargeMeter_ProjectDetailListByIDList(MeterProjectIDList);
if (meter_project_list.FirstOrDefault(p => p.RoomOwnerCount <= 0) != null && new Utility.SiteConfig().CheckOwnerInStatus)
{
errormsg = "选中的房间尚未有业主入住";
return(false);
}
if (meter_project_list.Length == 0)
{
errormsg = "请选择一条数据，操作取消";
return(false);
}
using (SqlHelper helper = new SqlHelper())
{
try
{
helper.BeginTransaction();
List <int> RoomFeeIDList = new List <int>();
foreach (var ChargeID in ChargeIDList)
{
foreach (var item in meter_project_list)
{
string cmdtext = "update [ChargeMeter_Project] set [WriteStatus]=1,[FeeStatus]=1 where ID=" + item.ID + ";";
helper.Execute(cmdtext, CommandType.Text, new List <SqlParameter>());
var data = new ChargeMeter_ProjectFee();
data.MeterProjectID = item.ID;
data.MeterID = item.MeterID;
data.ProjectID = item.ProjectID;
data.MeterName = item.MeterName;
data.MeterNumber = item.MeterNumber;
data.MeterCategoryID = item.MeterCategoryID;
data.MeterType = item.MeterType;
data.MeterSpec = item.MeterSpec;
data.MeterCoefficient = Coefficient;
data.MeterRemark = item.MeterRemark;
data.MeterChargeID = ChargeID;
data.MeterHouseNo = item.MeterHouseNo;
data.MeterLocation = item.MeterLocation;
data.SortOrder = item.SortOrder;
data.MeterStartPoint = item.FinalFeeStartPoint;
data.MeterEndPoint = item.FinalFeeEndPoint;
data.MeterTotalPoint = item.FinalFeeTotalPoint;
data.AddTime = DateTime.Now;
data.AddUserName = AddUserName;
data.MeterStartTime = StartTime;
data.MeterEndTime = EndTime;
data.MeterWriteDate = WriteDate;
data.WriteDate = item.WriteDate;
data.WriteUserName = item.WriteUserName;
var room_fee = RoomFee.SetInfo_RoomFee(data.ProjectID, data.MeterStartTime, data.MeterEndTime, 0, 0, SummaryUnitPrice, ChargeID, IsMeterFee: true, RoomFeeCoefficient: data.MeterCoefficient, RoomFeeWriteDate: WriteDate, UseCount: data.MeterTotalPoint, RoomFeeStartPoint: data.MeterStartPoint, RoomFeeEndPoint: data.MeterEndPoint, cansave: true, helper: helper);
data.RoomFeeID = room_fee.ID;
data.Save(helper);
room_fee.ChargeMeterProjectFeeID = data.ID;
room_fee.Save(helper);
RoomFeeIDList.Add(room_fee.ID);
}
}
ChargeFeePriceRange.CreateChargeFeePriceRangeByImportFeeIDList(helper, RoomFeeIDList: RoomFeeIDList);
helper.Commit();
}
catch (Exception ex)
{
Utility.LogHelper.WriteError("ChargeMeter_ProjectFee.cs", "Save_ChargeMeter_ProjectFee", ex);
helper.Rollback();
errormsg = ex.Message;
return(false);
}
}
return(true);
}
