public static ViewImportFeeDetail2[] GetViewImportFeeDetail2ListByID(List <int> IDList, string orderby = "")
{
ReSetParams();
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
string cmdwhere = string.Empty;
if (IDList.Count > 0)
{
conditions.Add("[ID] in (" + string.Join(",", IDList.ToArray()) + ")");
}
ViewImportFeeDetail2[] list = new ViewImportFeeDetail2[] { };
string cmdorderby = string.Empty;
if (!string.IsNullOrEmpty(orderby))
{
if (orderby.ToLower().StartsWith("order by"))
{
cmdorderby = orderby;
}
else
{
cmdorderby = "order by " + orderby;
}
}
string cmdText = "select A.* from " + CommSQLTable + " where " + string.Join(" and ", conditions.ToArray()) + cmdorderby;
list = GetList <ViewImportFeeDetail2>(cmdText, parameters).ToArray();
return(list);
}
public static ViewImportFeeDetail2[] GetViewImportFeeDetail2ListByBiaoID(List <int> RoomIDList, List <int> ProjectIDList, int ChargeID = 0, DateTime?WriteDate = null, int BiaoID = 0, string BiaoCategory = "", string BiaoName = "", string BiaoGuiGe = "", int UserID = 0)
{
ReSetParams();
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, UserID: UserID);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
ViewImportFeeDetail2[] list = new ViewImportFeeDetail2[] { };
if (ChargeID > 0)
{
parameters.Add(new SqlParameter("@ChargeID", ChargeID));
conditions.Add("[ChargeID]=@ChargeID");
}
if (BiaoID > 0)
{
parameters.Add(new SqlParameter("@BiaoID", BiaoID));
conditions.Add("[ChargeBiaoID]=@BiaoID");
}
if (!string.IsNullOrEmpty(BiaoCategory))
{
parameters.Add(new SqlParameter("@BiaoCategory", BiaoCategory));
conditions.Add("[ImportBiaoCategory]=@BiaoCategory");
}
if (!string.IsNullOrEmpty(BiaoName))
{
parameters.Add(new SqlParameter("@BiaoName", BiaoName));
conditions.Add("[ImportBiaoName]=@BiaoName");
}
if (!string.IsNullOrEmpty(BiaoGuiGe))
{
parameters.Add(new SqlParameter("@BiaoGuiGe", BiaoGuiGe));
conditions.Add("[ImportBiaoGuiGe]=@BiaoGuiGe");
}
//if (WriteDate.HasValue)
//{
// parameters.Add(new SqlParameter("@WriteDate", WriteDate));
// conditions.Add("[WriteDate]=(select top 1 WriteDate from [ViewImportFee] where WriteDate<@WriteDate and " + string.Join(" and ", conditions.ToArray()) + " order by WriteDate desc)");
//}
list = GetList <ViewImportFeeDetail2>("select * from [ViewImportFee] where " + string.Join(" and ", conditions.ToArray()), parameters).ToArray();
if (list.Length > 0 && WriteDate.HasValue)
{
var first_fee = list.Where(p => p.WriteDate < WriteDate).OrderByDescending(p => p.WriteDate).FirstOrDefault();
if (first_fee != null)
{
list = list.Where(p => p.WriteDate == first_fee.WriteDate).ToArray();
}
}
return(list);
}
private static ChargePriceRange[] GetPriceRangeList(ViewImportFeeDetail2 self)
{
var list = new List <ChargePriceRange>();
if (PriceRangeList == null || PriceRangeList.Length == 0)
{
PriceRangeList = ChargePriceRange.GetChargePriceRangeList();
}
if (PriceFeeRangeList == null)
{
PriceFeeRangeList = ChargeFeePriceRange.GetAllActiveChargeFeePriceRangeList();
}
if (self.RoomFeeID > 0)
{
if (PriceFeeRangeList != null && PriceFeeRangeList.Length > 0)
{
var my_pricefee_list = PriceFeeRangeList.Where(p => p.RoomFeeID == self.RoomFeeID && p.SummaryID == self.ChargeID).ToArray();
foreach (var item in my_pricefee_list)
{
var data = new ChargePriceRange();
data.SummaryID = item.SummaryID;
data.MinValue = item.MinValue;
data.MaxValue = item.MaxValue;
data.BasePrice = item.BasePrice;
data.BaseType = item.BaseType;
data.IsActive = item.IsActive;
list.Add(data);
}
}
return(list.OrderBy(p =>
{
decimal minvalue = 0;
decimal.TryParse(p.MinValue, out minvalue);
return minvalue;
}).ToArray());
}
else
{
if (PriceRangeList != null && PriceRangeList.Length > 0)
{
list = PriceRangeList.Where(p => p.SummaryID == self.ChargeID).OrderBy(p =>
{
decimal minvalue = 0;
decimal.TryParse(p.MinValue, out minvalue);
return(minvalue);
}).ToList();
}
return(list.ToArray());
}
}
private static decimal GetJieTiUnitPrice(ViewImportFeeDetail2 self)
{
decimal JieTieUnitPrice = 0;
var ChargePriceRangeList = GetPriceRangeList(self);
foreach (var item in ChargePriceRangeList)
{
if (item.SummaryID == self.ChargeID)
{
decimal NianTotal = 0;
if (item.BaseType.Equals(Utility.EnumModel.PriceRangeType.qnyl.ToString()))
{
NianTotal = (self.TotalUseCount > 0 ? self.TotalUseCount : 0);
}
if ((NianTotal + self.RealTotalPoint) >= Convert.ToDecimal(item.MinValue) && (NianTotal + self.RealTotalPoint) < Convert.ToDecimal(item.MaxValue))
{
JieTieUnitPrice = item.BasePrice;
break;
}
}
}
return(JieTieUnitPrice);
}
public static Ui.DataGrid GetViewImportFeeGridByRoomID(string Keywords, List <int> FeeTypeList, List <int> RoomIDList, List <int> ProjectIDList, int ChargeID, int ChargeStatus, DateTime StartWriteDate, DateTime EndWriteDate, bool IsReading, bool AllowImport, string BiaoCategory, string orderBy, long startRowIndex, int pageSize, bool ShowFooter = false, bool canexport = false)
{
ReSetParams();
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("([RoomID] in (select [RoomID] from [RoomBasic] where isnull([IsLocked],0)=0) or not exists (select * from [RoomBasic] where isnull([IsLocked],0)=0 and [RoomID]=A.[RoomID]))");
#region 关键字查询
string cmd = string.Empty;
if (!string.IsNullOrEmpty(Keywords))
{
string[] keywords = Keywords.Trim().Split(' ');
for (int i = 0; i < keywords.Length; i++)
{
if (string.IsNullOrEmpty(keywords[i].ToString()))
{
continue;
}
if (i + 1 == keywords.Length)
{
if (keywords.Length == 1)
{
cmd += " and ([FullName] like '%" + keywords[i] + "%' or [Name] like '%" + keywords[i] + "%')";
}
else
{
cmd += " ([FullName] like '%" + keywords[i] + "%' or [Name] like '%" + keywords[i] + "%'))";
}
}
else if (i == 0)
{
cmd += " and (([FullName] like '%" + keywords[i] + "%' or [Name] like '%" + keywords[i] + "%') or";
}
else
{
cmd += " ([FullName] like '%" + keywords[i] + "%' or [Name] like '%" + keywords[i] + "%') or ";
}
}
}
#endregion
if (!string.IsNullOrEmpty(BiaoCategory))
{
conditions.Add("([ImportBiaoCategory] like '%" + BiaoCategory + "%' or [BiaoCategory] like '%" + BiaoCategory + "%')");
}
if (StartWriteDate > DateTime.MinValue)
{
conditions.Add("[WriteDate]>=@StartWriteDate");
parameters.Add(new SqlParameter("@StartWriteDate", StartWriteDate));
}
if (EndWriteDate > DateTime.MinValue)
{
conditions.Add("[WriteDate]<=@EndWriteDate");
parameters.Add(new SqlParameter("@EndWriteDate", EndWriteDate));
}
if (IsReading)
{
conditions.Add("[ChargeID] in (select [ID] from [ChargeSummary] where [IsReading]=1)");
}
if (AllowImport)
{
conditions.Add("[ChargeID] in (select [ID] from [ChargeSummary] where [IsAllowImport]=1 and ([IsReading]=0 or [IsReading] is null))");
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = new List <string>();
foreach (var ProjectID in ProjectIDList)
{
cmdlist.Add("[AllParentID] like '%," + ProjectID + ",%'");
}
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (FeeTypeList.Count > 0)
{
conditions.Add("[ChargeID] in (select [ID] from [ChargeSummary] where [FeeType] in (" + string.Join(",", FeeTypeList.ToArray()) + "))");
}
if (ChargeID > 0)
{
conditions.Add("[ChargeID]=@ChargeID");
parameters.Add(new SqlParameter("@ChargeID", ChargeID));
}
if (ChargeStatus == 0)
{
conditions.Add("[RoomFeeID]>0");
}
else if (ChargeStatus == 1)
{
conditions.Add("[HistoryID]>0");
}
else if (ChargeStatus == 2)
{
conditions.Add("(isnull([RoomFeeID],0)<=0 and isnull([HistoryID],0)<=0)");
}
string fieldList = "A.*";
string Statement = " from " + CommSQLTable + " where " + string.Join(" and ", conditions.ToArray()) + cmd;
ViewImportFeeDetail2[] list = new ViewImportFeeDetail2[] { };
if (canexport)
{
list = GetList <ViewImportFeeDetail2>("select " + fieldList + Statement + " " + orderBy, parameters).ToArray();
totalRows = list.Length;
}
else
{
list = GetList <ViewImportFeeDetail2>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray();
}
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = list;
dg.total = totalRows;
dg.page = pageSize;
if (ShowFooter)
{
Dictionary <string, object> dic = new Dictionary <string, object>();
dic["FullName"] = "合计";
dic["RealTotalPoint"] = list.Sum(p => p.RealTotalPoint);
dic["FinalTotalPrice"] = list.Sum(p => p.FinalTotalPrice);
dg.footer = new Dictionary <string, object>[] { dic };
}
return(dg);
}
