public static void CreateChargeFeePriceRangeByImportFeeIDList(SqlHelper helper, List <int> ImportFeeIDList = null, List <int> RoomFeeIDList = null, int RoomFeeID = 0, int HistoryRoomFeeID = 0)
{
string cmdtext = string.Empty;
var parameters = new List <SqlParameter>();
if (HistoryRoomFeeID > 0 && RoomFeeID > 0)
{
RoomFeeHistory.UpdateRoomFeeHistoryID(helper, RoomFeeID: RoomFeeID, HistoryRoomFeeID: HistoryRoomFeeID);
cmdtext = "update [ChargeFeePriceRange] set [RoomFeeID]=@RoomFeeID where [RoomFeeID]=@HistoryRoomFeeID;";
cmdtext += "update [RoomFeeHistory] set [ID]=@RoomFeeID where [ID]=@HistoryRoomFeeID;";
parameters.Add(new SqlParameter("@RoomFeeID", RoomFeeID));
parameters.Add(new SqlParameter("@HistoryRoomFeeID", HistoryRoomFeeID));
helper.Execute(cmdtext, CommandType.Text, parameters);
return;
}
if (ImportFeeIDList == null && RoomFeeIDList == null && RoomFeeID == 0)
{
return;
}
List <string> conditions = new List <string>();
conditions.Add("RoomFee.ID not in (select RoomFeeID from [ChargeFeePriceRange])");
conditions.Add("[RoomFee].ChargeID in (select SummaryID from [ChargePriceRange])");
if (ImportFeeIDList != null && ImportFeeIDList.Count > 0)
{
conditions.Add("[RoomFee].[ImportFeeID] in (" + string.Join(",", ImportFeeIDList.ToArray()) + ")");
}
if (RoomFeeIDList != null && RoomFeeIDList.Count > 0)
{
conditions.Add("[RoomFee].[ID] in (" + string.Join(",", RoomFeeIDList.ToArray()) + ")");
}
if (RoomFeeID > 0)
{
conditions.Add("[RoomFee].ID=@RoomFeeID");
parameters.Add(new SqlParameter("@RoomFeeID", RoomFeeID));
}
cmdtext = @"insert into [ChargeFeePriceRange]
([RoomFeeID]
,[ChargePriceRangeID]
,[SummaryID]
,[MinValue]
,[MaxValue]
,[BasePrice]
,[BaseType]
,[IsActive]
,[AddTime])
select [RoomFee].ID as RoomFeeID,
[ChargePriceRange].ID as ChargePriceRangeID,
[ChargePriceRange].SummaryID,
[ChargePriceRange].MinValue,[ChargePriceRange].MaxValue,
[ChargePriceRange].BasePrice,
[ChargePriceRange].BaseType,
[ChargePriceRange].IsActive,
getdate()
from [RoomFee]
left join [ChargePriceRange] on SummaryID=RoomFee.ChargeID
where " + string.Join(" and ", conditions.ToArray());
helper.Execute(cmdtext, CommandType.Text, parameters);
}
public static RoomFeeHistory[] GetInCorrectRoomFeeHistoryList()
{
List <SqlParameter> parameters = new List <SqlParameter>();
string Statement = "select * from RoomFeeHistory where ID in (Select ID from RoomFee) and ChargeState=1 order by (Select DefaultOrder from Project where ID=RoomFeeHistory.RoomID) asc";
RoomFeeHistory[] list = new RoomFeeHistory[] { };
list = GetList <RoomFeeHistory>(Statement, parameters).ToArray();
return(list);
}
public static RoomFeeHistory[] GetRoomFeeHistoryListByContractDivideIDList(List <int> DivideIDList)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("[ChargeState] in (1,4)");
if (DivideIDList.Count == 0)
{
return(new RoomFeeHistory[] { });
}
conditions.Add("isnull(ContractDivideID,0) in (" + string.Join(",", DivideIDList.ToArray()) + ")");
string Statement = " select * from [RoomFeeHistory] where " + string.Join(" and ", conditions.ToArray());
RoomFeeHistory[] list = new RoomFeeHistory[] { };
list = GetList <RoomFeeHistory>(Statement, parameters).ToArray();
return(list);
}
public static RoomFeeHistory GetPreRoomFeeHistory(int RoomFeeID, DateTime StartTime, SqlHelper helper, out RoomFeeHistory nextRoomFeeHistory)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
string cmdwhere1 = string.Empty;
string cmdwhere2 = string.Empty;
conditions.Add("[ID]=@RoomFeeID");
conditions.Add("[IsCuiShou]=1");
conditions.Add("[ChargeState]=5");
if (StartTime > DateTime.MinValue)
{
cmdwhere1 = " and [StartTime]<@StartTime";
cmdwhere2 = " and [StartTime]>@StartTime";
parameters.Add(new SqlParameter("@StartTime", StartTime));
}
parameters.Add(new SqlParameter("@RoomFeeID", RoomFeeID));
nextRoomFeeHistory = GetOne <RoomFeeHistory>("select top 1 * from [RoomFeeHistory] where " + string.Join(" and ", conditions.ToArray()) + cmdwhere2 + " order by [StartTime] asc", parameters, helper);
return(GetOne <RoomFeeHistory>("select top 1 * from [RoomFeeHistory] where " + string.Join(" and ", conditions.ToArray()) + cmdwhere1 + " order by [StartTime] desc", parameters, helper));
}
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 Ui.DataGrid GetViewContractDivideGridByKeywords(string Keywords, DateTime StartTime, DateTime EndTime, string orderBy, long startRowIndex, int pageSize, List <int> ProjectIDList, List <int> RoomIDList, int ContractID, bool canexport = false)
{
ReSetParams();
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
#region 关键字查询
string cmd = string.Empty;
if (!string.IsNullOrEmpty(Keywords))
{
conditions.Add("([ContractNo] like @Keywords or [ContractName] like @Keywords)");
parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%"));
}
#endregion
if (StartTime > DateTime.MinValue)
{
conditions.Add("[WriteDate]>=@StartTime");
parameters.Add(new SqlParameter("@StartTime", StartTime));
}
if (EndTime > DateTime.MinValue)
{
conditions.Add("[WriteDate]<=@EndTime");
parameters.Add(new SqlParameter("@EndTime", EndTime));
}
if (ContractID > 0)
{
conditions.Add("[ContractID]=@ContractID");
parameters.Add(new SqlParameter("@ContractID", ContractID));
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = new List <string>();
foreach (var ProjectID in ProjectIDList)
{
cmdlist.Add("([AllParentID] like '%," + ProjectID + ",%' or [ID] =" + ProjectID + ")");
}
conditions.Add("[ContractID] in (select [ContractID] from [Contract_Room] where [RoomID] in (select [ID] from [Project] where (" + string.Join(" or ", cmdlist.ToArray()) + ")))");
}
if (RoomIDList.Count > 0)
{
conditions.Add("[ContractID] in (select [ContractID] from [Contract_Room] where [RoomID] in (" + string.Join(",", RoomIDList.ToArray()) + "))");
}
string fieldList = "[ViewContractDivide].*";
string Statement = " from [ViewContractDivide] where " + string.Join(" and ", conditions.ToArray()) + cmd;
ViewContractDivide[] list = new ViewContractDivide[] { };
if (canexport)
{
list = GetList <ViewContractDivide>("select " + fieldList + Statement + " " + orderBy, parameters).ToArray();
}
else
{
list = GetList <ViewContractDivide>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray();
}
if (list.Length > 0)
{
var idlist = list.Where(p => p.ChargeStatus != 2).Select(p => p.ID).ToList();
feelist = RoomFeeAnalysis.GetRoomFeeAnalysisListByContractDivideIDList(idlist);
historylist = RoomFeeHistory.GetRoomFeeHistoryListByContractDivideIDList(idlist);
int MinContractID = list.Min(p => p.ContractID);
int MaxContractID = list.Max(p => p.ContractID);
var contractList = GetList <Contract>("select ID,ContractBasicRentCost from [Contract] where ID between " + MinContractID + " and " + MaxContractID, new List <SqlParameter>()).ToArray();
foreach (var item in list)
{
var myContract = contractList.FirstOrDefault(p => p.ID == item.ContractID);
if (myContract != null)
{
item.ContractBasicRentCost = myContract.ContractBasicRentCost;
}
}
}
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = list;
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}