public static Contract_Room[] GetContract_RoomListByRoomIDList(List <int> RoomIDList, List <int> ProjectIDList, int UserID = 0)
{
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, RoomIDName: "[RoomID]");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (ProjectIDList != null && ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IsContractFee: false, UserID: UserID);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
return(GetList <Contract_Room>("select * from [Contract_Room] where " + string.Join(" and ", conditions.ToArray()), parameters).ToArray());
}
public static ViewContractChargeSummary[] GetViewContractChargeSummaryByProjectIDList(List <int> RoomIDList, List <int> ProjectIDList, int UserID = 0)
{
ReSetParams();
List <string> conditions = new List <string>();
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, RoomIDName: "[RoomID]", IsContractFee: true, UserID: UserID);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false, RoomIDName: "[RoomID]", IsContractFee: true);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
List <SqlParameter> parameters = new List <SqlParameter>();
return(GetList <ViewContractChargeSummary>("select * from [ViewContractChargeSummary] where " + string.Join(" and ", conditions.ToArray()), parameters).ToArray());
}
public static PayService[] GetPayServiceList(List <int> RoomIDList, DateTime StartTime, DateTime EndTime, int UserID = 0, List <int> EqualProjectIDList = null, List <int> InProjectIDList = null)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
List <string> cmdlist = new List <string>();
conditions.Add("1=1");
conditions.Add("isnull([Status],3)=3");
if (InProjectIDList != null && InProjectIDList.Count > 0)
{
cmdlist = new List <string>();
cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(InProjectIDList, IncludeRelation: false, RoomIDName: "[ProjectID]", UserID: UserID);
}
if (EqualProjectIDList != null && EqualProjectIDList.Count > 0)
{
cmdlist.Add("([ProjectID] in (" + string.Join(",", EqualProjectIDList.ToArray()) + "))");
}
if (cmdlist.Count > 0)
{
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (StartTime > DateTime.MinValue)
{
conditions.Add("Convert(nvarchar(10),[PayTime],120)>=@StartTime");
parameters.Add(new SqlParameter("@StartTime", StartTime));
}
if (EndTime > DateTime.MinValue)
{
conditions.Add("Convert(nvarchar(10),[PayTime],120)<=@EndTime");
parameters.Add(new SqlParameter("@EndTime", EndTime));
}
if (RoomIDList.Count > 0)
{
cmdlist = new List <string>();
cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false, RoomIDName: "[ProjectID]");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
string Statement = @" select * from PayService where " + string.Join(" and ", conditions);
PayService[] list = GetList <PayService>(Statement, parameters).ToArray();
return(list);
}
public static RoomFee[] GetRoomFeeListByImportFeeIDList(List <int> ImportFeeIDList, int UserID = 0)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (ImportFeeIDList.Count == 0)
{
return(new RoomFee[] { });
}
if (ImportFeeIDList.Count > 2)
{
ViewRoomFeeHistory.CreateTempTable(ImportFeeIDList, UserID: UserID);
conditions.Add("EXISTS (SELECT 1 FROM [TempIDs] WHERE id=[RoomFee].ImportFeeID and [UserID]=" + UserID + ")");
}
else if (ImportFeeIDList.Count > 0)
{
conditions.Add("[ImportFeeID] in (" + string.Join(",", ImportFeeIDList.ToArray()) + ")");
}
return(GetList <RoomFee>("select * from [RoomFee] where " + string.Join(" and ", conditions.ToArray()), parameters).ToArray());
}
public static ChargeMeter_ProjectDetail[] GetChargeMeter_ProjectDetailListByRoomID(int RoomID, List <int> ProjectIDList = null, int MeterType = 1, int UserID = 0, int ProjectID = 0, string keywords = "")
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("[IsAPPWriteEnable]=1");
if (RoomID > 0)
{
conditions.Add("[ProjectID]=@RoomID");
parameters.Add(new SqlParameter("@RoomID", RoomID));
}
if (ProjectID > 0)
{
conditions.Add("([ProjectID]=@ProjectID or [AllParentID] like @AllParentID)");
parameters.Add(new SqlParameter("@ProjectID", ProjectID));
parameters.Add(new SqlParameter("@AllParentID", "%," + ProjectID + ",%"));
}
if (!string.IsNullOrEmpty(keywords))
{
conditions.Add("([ProjectName] like @keywords or [FullName] like @keywords)");
parameters.Add(new SqlParameter("@keywords", "%" + keywords + "%"));
}
if (MeterType == 1)
{
conditions.Add("[MeterType]=1");
if (ProjectIDList != null && ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, RoomIDName: "[ProjectID]", UserID: UserID);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
}
else
{
conditions.Add("[MeterType]=2");
}
string cmdtext = "select * from (" + CommSqlText + ") as A where " + string.Join(" and ", conditions.ToArray());
ChargeMeter_ProjectDetail[] list = GetList <ChargeMeter_ProjectDetail>(cmdtext, parameters).ToArray();
return(list);
}
public static Wechat_Contact[] GetWechat_ContactListByMsgPhoneType(string PhoneType, List <int> ProjectIDList = null, int UserID = 0)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
List <string> conditions2 = new List <string>();
conditions.Add("1=1");
conditions2.Add("1=1");
if (ProjectIDList != null && ProjectIDList.Count > 0)
{
List <int> EqualIDList = new List <int>();
List <int> InIDList = new List <int>();
Project.GetMyProjectListByProjectIDList(ProjectIDList, out EqualIDList, out InIDList);
if (EqualIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(EqualIDList, IncludeRelation: false, RoomIDName: "[ProjectID]", UserID: UserID);
conditions.Add("([ID] in (select [WechatContactID] from [Wechat_ContactProject] where (" + string.Join(" or ", cmdlist.ToArray()) + ")))");
}
}
if (!string.IsNullOrEmpty(PhoneType))
{
conditions.Add("[PhoneType]=@PhoneType");
parameters.Add(new SqlParameter("@PhoneType", PhoneType));
conditions2.Add("[PhoneType]=@PhoneType");
}
else
{
conditions.Add("isnull([PhoneType],'')!=@PhoneType");
parameters.Add(new SqlParameter("@PhoneType", PhoneType));
conditions2.Add("isnull([PhoneType],'')!=@PhoneType");
}
string cmdtext = "select * from [Wechat_Contact] where " + string.Join(" and ", conditions.ToArray()) + " order by ID desc";
var list = GetList <Wechat_Contact>(cmdtext, parameters).ToArray();
if (list.Length > 0)
{
return(list);
}
return(GetList <Wechat_Contact>("select * from [Wechat_Contact] where " + string.Join(" and ", conditions2.ToArray()) + " order by ID desc", parameters).ToArray());
}
public static RoomFee[] GetRoomFeeListByParams(List <int> ProjectIDList, List <int> RoomIDList, List <int> ChargeIDList, int UserID = 0)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (ChargeIDList.Count > 0)
{
conditions.Add("[ChargeID] in (" + string.Join(",", ChargeIDList.ToArray()) + ")");
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, UserID: UserID);
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()) + ")");
}
return(GetList <RoomFee>("select * from [RoomFee] where " + string.Join(" and ", conditions.ToArray()), parameters).ToArray());
}
public static CustomerService[] GetCustomerServiceListByRoomIDList(List <int> RoomIDList, List <int> ProjectIDList, int UserID = 0, int Status = 0)
{
ResetCache();
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, RoomIDName: "[ProjectID]");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (ProjectIDList != null && ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, RoomIDName: "[ProjectID]", IsContractFee: false, UserID: UserID);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (Status == 100)
{
conditions.Add("[ServiceStatus] in (3,10)");
}
return(GetList <CustomerService>("select * from [CustomerService] where " + string.Join(" and ", conditions.ToArray()), parameters).ToArray());
}
public static Ui.DataGrid GetProjectPublicGridList(List <int> ParentProjectIDList, List <int> ParentIDList, string Keywords, string orderBy, long startRowIndex, int pageSize, int UserID = 0)
{
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
string sortorder = string.Empty;
var cmdlist = new List <string>();
if (!string.IsNullOrEmpty(Keywords))
{
parameters.Add(new SqlParameter("@LikeKeywords", "%" + Keywords + "%"));
conditions.Add("([Name] like @LikeKeywords)");
}
if (ParentProjectIDList.Count > 0)
{
cmdlist = ViewRoomFeeHistory.GetPublicProjectIDListConditions(ParentProjectIDList, RoomIDName: "[ParentProjectID]", UserID: UserID);
}
if (ParentIDList.Count > 0)
{
cmdlist = ViewRoomFeeHistory.GetPublicParentIDListConditions(ParentIDList, RoomIDName: "[ID]", UserID: UserID);
}
if (cmdlist.Count > 0)
{
conditions.Add(string.Join(" or", cmdlist.ToArray()));
}
string fieldList = "A.* ";
string Statement = " from (select [Project_Public].*,[Project].FullName as ProjectFullName from [Project_Public] left join [Project] on [Project].ID=[Project_Public].ParentProjectID)A where " + string.Join(" and ", conditions.ToArray());
var list = GetList <Project_PublicDetail>(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;
return(dg);
}
public static Ui.DataGrid GetMall_DoorRemoteUserTimeDetailGridByKeywords(string keywords, long startRowIndex, int pageSize, List <int> ProjectIDList, List <int> RoomIDList)
{
long totalRows = 0;
string OrderBy = " order by [DefaultOrder] asc";
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("isnull([isParent],0)=0");
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = new List <string>();
foreach (var ProjectID in ProjectIDList)
{
cmdlist.Add("([AllParentID] like '%," + ProjectID + ",%' or [RoomID] =" + 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 (!string.IsNullOrEmpty(keywords))
{
conditions.Add("([Name] like @keywords or [RoomID] in (select [RoomID] from [RoomPhoneRelation] where [RelatePhoneNumber] like @keywords or [RelationName] like @keywords) or [RoomID] in (select [ProjectID] from [Mall_UserProject] where isnull([IsDisable],0)=0 and [UserID] in (select [UserID] from [User] where LoginName like @keywords)))");
parameters.Add(new SqlParameter("@keywords", "%" + keywords + "%"));
}
string fieldList = "A.*";
string Statement = " from (" + CommColumnCommand + ")A where " + string.Join(" and ", conditions.ToArray());
Mall_DoorRemoteUserTimeDetail[] list = GetList <Mall_DoorRemoteUserTimeDetail>(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;
return(dg);
}
public static void GetContractFeeSummary(ContractDetail[] list, out ContractDetail footeritem, bool outfooter = false)
{
List <int> ContractIDList = list.Select(p => p.ID).ToList();
var fee_list = RoomFeeAnalysis.GetRoomFeeAnalysisListByContractIDList(ContractIDList);
var feehistory_list = ViewRoomFeeHistory.GetViewRoomFeeHistoryListByContractIDList(ContractIDList);
var fee_weiyue_list = RoomFeeAnalysis.GetContractFeeWeiYueListByContractIDList(ContractIDList);
decimal totalRestCost = 0;
decimal totalChargedCost = 0;
decimal totalPreCost = 0;
decimal totalDepositCost = 0;
decimal totalBreakCost = 0;
decimal totalTotalCost = 0;
foreach (var item in list)
{
item.RestCost = fee_list.Where(p => p.ContractID == item.ID && p.TotalCost > 0).Sum(p => p.TotalCost);
item.ChargedCost = feehistory_list.Where(p => p.ContractID == item.ID && p.RealCost > 0).Sum(p => p.RealCost);
item.PreCost = feehistory_list.Where(p => p.ContractID == item.ID && p.RealCost > 0 && p.CategoryID == 4).Sum(p => p.RealCost);
item.DepositCost = feehistory_list.Where(p => p.ContractID == item.ID && p.RealCost > 0 && p.CategoryID == 3).Sum(p => p.RealCost);
item.TotalCost = item.RestCost + item.ChargedCost;
item.BreakCost = 0;
totalRestCost += item.RestCost;
totalChargedCost += item.ChargedCost;
totalPreCost += item.PreCost;
totalDepositCost += item.DepositCost;
totalBreakCost += fee_weiyue_list.Where(p => p.RelatedFeeID == item.ID && p.TotalCost > 0).Sum(p => p.TotalCost);
totalTotalCost += item.TotalCost;
}
footeritem = new ContractDetail();
footeritem.ContractNo = "合计";
footeritem.RestCost = totalRestCost;
footeritem.ChargedCost = totalChargedCost;
footeritem.PreCost = totalPreCost;
footeritem.DepositCost = totalDepositCost;
footeritem.TotalCost = totalTotalCost;
footeritem.BreakCost = totalBreakCost;
}
public static int GetALLWaringingContractsCount(List <int> RoomIDList, List <int> ProjectIDList, int UserID = 0)
{
List <string> conditions = new List <string>();
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, RoomIDName: "[RoomID]", IsContractFee: false, UserID: UserID);
conditions.Add("ID in (select ContractID from Contract_Room where (" + string.Join(" or ", cmdlist.ToArray()) + "))");
}
if (RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false, RoomIDName: "[RoomID]");
conditions.Add("ID in (select ContractID from Contract_Room where (" + string.Join(" or ", cmdlist.ToArray()) + "))");
}
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("@ContractStatus", Utility.EnumModel.ContractStatus.tongguo.ToString()));
conditions.Add("[ContractStatus]=@ContractStatus");
var list = GetList <ContractDetail>("select [ID],[ContractStatus],[RentEndTime] from [Contract] where " + string.Join(" and ", conditions.ToArray()), parameters).ToArray();
ContractDetail.SetContractWarningCount(list);
list = list.Where(p => p.IsRentExpire).ToArray();
return(list.Length);
}
public static Ui.DataGrid GetViewPayServiceGridByKeywords(int PaySummaryID, string Keywords, List <int> RoomIDList, DateTime StartTime, DateTime EndTime, string orderBy, long startRowIndex, int pageSize, int UserID = 0, List <int> EqualProjectIDList = null, List <int> InProjectIDList = null, bool canexport = false)
{
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
List <string> cmdlist = new List <string>();
conditions.Add("1=1");
if (InProjectIDList != null && InProjectIDList.Count > 0)
{
cmdlist = new List <string>();
cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(InProjectIDList, IncludeRelation: false, RoomIDName: "[ProjectID]", UserID: UserID);
}
if (EqualProjectIDList != null && EqualProjectIDList.Count > 0)
{
cmdlist.Add("([ProjectID] in (" + string.Join(",", EqualProjectIDList.ToArray()) + "))");
}
if (cmdlist.Count > 0)
{
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (PaySummaryID > 0)
{
conditions.Add("[PaySummaryID]=@PaySummaryID");
parameters.Add(new SqlParameter("@PaySummaryID", PaySummaryID));
}
#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 ([ProjectName] like '%" + keywords[i] + "%' or [PayType] like '%" + keywords[i] + "%' or [AccountType] like '%" + keywords[i] + "%' or [RoomName] like '%" + keywords[i] + "%' or [Remark] like '%" + keywords[i] + "%')";
}
else
{
cmd += " ([ProjectName] like '%" + keywords[i] + "%' or [PayType] like '%" + keywords[i] + "%' or [AccountType] like '%" + keywords[i] + "%' or [RoomName] like '%" + keywords[i] + "%' or [Remark] like '%" + keywords[i] + "%'))";
}
}
else if (i == 0)
{
cmd += " and (([ProjectName] like '%" + keywords[i] + "%' or [PayType] like '%" + keywords[i] + "%' or [AccountType] like '%" + keywords[i] + "%' or [RoomName] like '%" + keywords[i] + "%' or [Remark] like '%" + keywords[i] + "%') or";
}
else
{
cmd += " ([ProjectName] like '%" + keywords[i] + "%' or [PayType] like '%" + keywords[i] + "%' or [AccountType] like '%" + keywords[i] + "%' or [RoomName] like '%" + keywords[i] + "%' or [Remark] like '%" + keywords[i] + "%') or ";
}
}
}
#endregion
if (RoomIDList.Count > 0)
{
cmdlist = new List <string>();
cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false, RoomIDName: "[ProjectID]");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (StartTime > DateTime.MinValue)
{
conditions.Add("[PayTime]>=@StartTime");
parameters.Add(new SqlParameter("@StartTime", StartTime));
}
if (EndTime > DateTime.MinValue)
{
conditions.Add("[PayTime]<=@EndTime");
parameters.Add(new SqlParameter("@EndTime", EndTime));
}
string fieldList = "[ViewPayService].*";
string Statement = " from [ViewPayService] where " + string.Join(" and ", conditions.ToArray()) + cmd;
ViewPayService[] list = new ViewPayService[] { };
if (canexport)
{
list = GetList <ViewPayService>("select " + fieldList + Statement + " " + orderBy, parameters).ToArray();
}
else
{
list = GetList <ViewPayService>(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;
return(dg);
}
public static decimal GetSumWeiShuByChargeState(DateTime StartTime, DateTime EndTime, string ChargeMan, List <int> ChargeStateList, List <int> ProjectIDList, List <int> RoomIDList, int RoomFeeOrderID, bool IsRoomFeeSearch, List <int> HistoryIDList = null, bool DeleteTempHistoryIDList = true, int UserID = 0)
{
decimal total = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (HistoryIDList != null && HistoryIDList.Count > 0)
{
if (HistoryIDList.Count > 2)
{
ViewRoomFeeHistory.CreateTempTable(HistoryIDList, DeleteTempHistoryIDList, UserID: UserID);
conditions.Add("EXISTS (SELECT 1 FROM [TempIDs] WHERE id in (select [HistoryID] from [RoomFeeHistory] where [PrintID]=[PrintRoomFeeHistory].[ID]) and [UserID]=" + UserID + ")");
}
else if (HistoryIDList.Count > 0)
{
conditions.Add("[PrintRoomFeeHistory].ID in (select PrintID from [RoomFeeHistory] where HistoryID in (" + string.Join(",", HistoryIDList.ToArray()) + "))");
}
}
if (RoomFeeOrderID > 0)
{
conditions.Add("isnull([RoomFeeOrderID],0)=@RoomFeeOrderID");
parameters.Add(new SqlParameter("@RoomFeeOrderID", RoomFeeOrderID));
}
else if (IsRoomFeeSearch)
{
conditions.Add("isnull([RoomFeeOrderID],0)=0");
conditions.Add("[ID] in (select [PrintID] from [RoomFeeHistory] where [ChargeID] in (select [ID] from [ChargeSummary] where isnull([IsOrderFeeOn],0)=1) or isnull(ChargeID,0)=0)");
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, UserID: UserID);
conditions.Add("[ID] in (select [PrintID] from [RoomFeeHistory] where (" + string.Join(" or ", cmdlist.ToArray()) + "))");
}
if (RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false);
conditions.Add("[ID] in (select [PrintID] from [RoomFeeHistory] where (" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (StartTime > DateTime.MinValue)
{
conditions.Add("[ID] in (select [PrintID] from [RoomFeeHistory] where [ChargeTime]>@StartTime)");
parameters.Add(new SqlParameter("@StartTime", StartTime));
}
if (EndTime > DateTime.MinValue)
{
conditions.Add("[ID] in (select [PrintID] from [RoomFeeHistory] where [ChargeTime]<@EndTime)");
parameters.Add(new SqlParameter("@EndTime", EndTime));
}
#region 收款人查询
string cmd = string.Empty;
if (!string.IsNullOrEmpty(ChargeMan))
{
string[] keywords = ChargeMan.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 ([ChargeMan] like '%" + keywords[i] + "%')";
}
else
{
cmd += " ([ChargeMan] like '%" + keywords[i] + "%'))";
}
}
else if (i == 0)
{
cmd += " and (([ChargeMan] like '%" + keywords[i] + "%') or";
}
else
{
cmd += " ([ChargeMan] like '%" + keywords[i] + "%') or ";
}
}
}
#endregion
//if (!string.IsNullOrEmpty(ChargeMan))
//{
// conditions.Add("[ChargeMan]=@ChargeMan");
// parameters.Add(new SqlParameter("@ChargeMan", ChargeMan));
//}
if (ChargeStateList.Count > 0)
{
conditions.Add("[ID] in (select [PrintID] from [RoomFeeHistory] where [ChargeState] in (" + string.Join(",", ChargeStateList.ToArray()) + "))");
}
string cmdtext = "select sum(WeiShuMore)-sum(WeiShuConsume) from [PrintRoomFeeHistory] where " + string.Join(" and ", conditions.ToArray()) + cmd;
using (SqlHelper helper = new SqlHelper())
{
object obj = helper.ExecuteScalar(cmdtext, CommandType.Text, parameters);
if (obj != null)
{
decimal.TryParse(obj.ToString(), out total);
}
}
return(total);
}
public static Ui.DataGrid GetViewCustomerServiceInDetailGridByKeywords(DateTime StartTime, DateTime EndTime, int PayStatus, string BalanceStatus, List <int> RoomIDList, List <int> ProjectIDList, string orderBy, long startRowIndex, int pageSize, int UserID = 0, bool canexport = false)
{
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("[ServiceID]>0");
if (StartTime > DateTime.MinValue)
{
conditions.Add("[AddTime]>=@StartTime");
parameters.Add(new SqlParameter("@StartTime", StartTime));
}
if (EndTime > DateTime.MinValue)
{
conditions.Add("[AddTime]<=@EndTime");
parameters.Add(new SqlParameter("@EndTime", EndTime));
}
if (PayStatus == 1)
{
conditions.Add("isnull([TotalFee],0)>0");
}
else if (PayStatus == 0)
{
conditions.Add("isnull([TotalFee],0)=0");
}
if (!string.IsNullOrEmpty(BalanceStatus))
{
conditions.Add("isnull([BalanceStatus],'balanceno')=@BalanceStatus");
parameters.Add(new SqlParameter("@BalanceStatus", BalanceStatus));
}
var myProjectIDList = new int[] { };
if (RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false, RoomIDName: "[ProjectID]");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
else if (ProjectIDList.Count > 0)
{
myProjectIDList = Project.GetProjectIDListbyIDList(ProjectIDList: ProjectIDList, UserID: UserID);
}
string fieldList = "[ViewCustomerServiceInDetail].*";
string Statement = " from [ViewCustomerServiceInDetail] where " + string.Join(" and ", conditions.ToArray());
ViewCustomerServiceInDetail[] list = new ViewCustomerServiceInDetail[] { };
if (canexport)
{
list = GetList <ViewCustomerServiceInDetail>("select " + fieldList + Statement + " " + orderBy, parameters).ToArray();
}
if (myProjectIDList.Length > 0)
{
list = list.Where(p => myProjectIDList.Contains(p.ProjectID)).ToArray();
}
totalRows = list.Length;
if (!canexport)
{
list = list.Skip((int)startRowIndex).Take(pageSize).ToArray();
}
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = list;
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}
public static Ui.DataGrid GetRoomBasicListByKeywords(List <int> RoomIDList, List <int> ProjectIDList, string RoomOwner, string OwnerPhone, string Keywords, List <string> SearchAreas, string orderBy, long startRowIndex, int pageSize, string OpenID, int ConnectStatus, bool canexport = false)
{
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("isnull([isParent],0)=0");
if (!string.IsNullOrEmpty(OpenID))
{
if (ConnectStatus > int.MinValue)
{
if (ConnectStatus == 1)
{
conditions.Add("[RoomID] in (select [ProjectID] from [WechatUser_Project] where OpenID=@OpenID)");
parameters.Add(new SqlParameter("@OpenID", OpenID));
}
else if (ConnectStatus == 0)
{
conditions.Add("[RoomID] not in (select [ProjectID] from [WechatUser_Project] where OpenID=@OpenID)");
parameters.Add(new SqlParameter("@OpenID", OpenID));
}
}
}
#region 关键字查询
string cmd = string.Empty;
if (!string.IsNullOrEmpty(Keywords))
{
cmd += " and (" + GetSearchConditions(SearchAreas, Keywords) + ")";
}
#endregion
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = new List <string>();
foreach (var ProjectID in ProjectIDList)
{
cmdlist.Add("([AllParentID] like '%," + ProjectID + ",%' or [RoomID] =" + 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 (!string.IsNullOrEmpty(RoomOwner))
{
conditions.Add("[RoomOwner]=@RoomOwner");
parameters.Add(new SqlParameter("@RoomOwner", RoomOwner));
}
if (!string.IsNullOrEmpty(OwnerPhone))
{
conditions.Add("[OwnerPhone]=@OwnerPhone");
parameters.Add(new SqlParameter("@OwnerPhone", OwnerPhone));
}
string fieldList = "[ViewRoomBasic].*";
string Statement = " from [ViewRoomBasic] where " + string.Join(" and ", conditions.ToArray()) + cmd;
ViewRoomBasic[] list = new ViewRoomBasic[] { };
var phoneList = new RoomPhoneRelation[] { };
int MinRoomID = 0;
int MaxRoomID = 0;
if (canexport)
{
list = GetList <ViewRoomBasic>("select " + fieldList + Statement + " " + orderBy, parameters).ToArray();
if (list.Length > 0)
{
MinRoomID = list.Min(p => p.RoomID);
MaxRoomID = list.Max(p => p.RoomID);
}
phoneList = RoomPhoneRelation.GetRoomPhoneRelationListByMinMaxRoomID(MinRoomID, MaxRoomID);
}
else
{
list = GetList <ViewRoomBasic>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray();
if (list.Length > 0)
{
MinRoomID = list.Min(p => p.RoomID);
MaxRoomID = list.Max(p => p.RoomID);
}
}
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
var fieldlist = Foresight.DataAccess.DefineField.GetDefineFieldsByTable_Name(Utility.EnumModel.DefineFieldTableName.RoomBasic.ToString()).Where(p => p.IsShown).ToArray();
var contentlist = Foresight.DataAccess.RoomBasicField.GetRoomBasicFieldsByRoomIDList(MinRoomID, MaxRoomID);
var results = list.Select(p =>
{
var dic = p.ToJsonObject(ignoreDBColumn: false);
if (phoneList.Length > 0)
{
var myPhoneList = phoneList.Where(q => q.RoomID == p.RoomID).OrderByDescending(q => q.IsDefault).ThenBy(q => q.ID).ToArray();
if (myPhoneList.Length > 0)
{
var strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.RelationName)).Select(q => q.RelationName).ToArray();
if (strList.Length > 0)
{
dic["RelationName"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.RelationTypeDesc)).Select(q => q.RelationTypeDesc).ToArray();
if (strList.Length > 0)
{
dic["RelationTypeDesc"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.RelatePhoneNumber)).Select(q => q.RelatePhoneNumber).ToArray();
if (strList.Length > 0)
{
dic["RelatePhoneNumber"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.IDCardTypeDesc)).Select(q => q.IDCardTypeDesc).ToArray();
if (strList.Length > 0)
{
dic["IDCardTypeDesc"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.RelationIDCard)).Select(q => q.RelationIDCard).ToArray();
if (strList.Length > 0)
{
dic["RelationIDCard"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.BirthdayDesc)).Select(q => q.BirthdayDesc).ToArray();
if (strList.Length > 0)
{
dic["Birthday"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.EmailAddress)).Select(q => q.EmailAddress).ToArray();
if (strList.Length > 0)
{
dic["EmailAddress"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.HomeAddress)).Select(q => q.HomeAddress).ToArray();
if (strList.Length > 0)
{
dic["HomeAddress"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.OfficeAddress)).Select(q => q.OfficeAddress).ToArray();
if (strList.Length > 0)
{
dic["OfficeAddress"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.BankAccountName)).Select(q => q.BankAccountName).ToArray();
if (strList.Length > 0)
{
dic["BankAccountName"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.BankAccountNo)).Select(q => q.BankAccountNo).ToArray();
if (strList.Length > 0)
{
dic["BankAccountNo"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.CustomOne)).Select(q => q.CustomOne).ToArray();
if (strList.Length > 0)
{
dic["RelateCustomOne"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.CustomTwo)).Select(q => q.CustomTwo).ToArray();
if (strList.Length > 0)
{
dic["RelateCustomTwo"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.CustomThree)).Select(q => q.CustomThree).ToArray();
if (strList.Length > 0)
{
dic["RelateCustomThree"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.CustomFour)).Select(q => q.CustomFour).ToArray();
if (strList.Length > 0)
{
dic["RelateCustomFour"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.Interesting)).Select(q => q.Interesting).ToArray();
if (strList.Length > 0)
{
dic["Interesting"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.ConsumeMore)).Select(q => q.ConsumeMore).ToArray();
if (strList.Length > 0)
{
dic["ConsumeMore"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.BelongTeam)).Select(q => q.BelongTeam).ToArray();
if (strList.Length > 0)
{
dic["BelongTeam"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.OneCardNumber)).Select(q => q.OneCardNumber).ToArray();
if (strList.Length > 0)
{
dic["OneCardNumber"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.ChargeForMan)).Select(q => q.ChargeForMan).ToArray();
if (strList.Length > 0)
{
dic["ChargeForMan"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.IsDefaultDesc)).Select(q => q.IsDefaultDesc).ToArray();
if (strList.Length > 0)
{
dic["IsDefaultDesc"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.IsChargeFeeDesc)).Select(q => q.IsChargeFeeDesc).ToArray();
if (strList.Length > 0)
{
dic["IsChargeFeeDesc"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.RelationPropertyDesc)).Select(q => q.RelationPropertyDesc).ToArray();
if (strList.Length > 0)
{
dic["RelationPropertyDesc"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.CompanyName)).Select(q => q.CompanyName).ToArray();
if (strList.Length > 0)
{
dic["CompanyName"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.IsChargeManDesc)).Select(q => q.IsChargeManDesc).ToArray();
if (strList.Length > 0)
{
dic["IsChargeManDesc"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.Remark)).Select(q => q.Remark).ToArray();
if (strList.Length > 0)
{
dic["RoomPhoneRelationRemark"] = string.Join(",", strList);
}
}
}
foreach (var item in fieldlist)
{
var contentmodel = contentlist.FirstOrDefault(q => q.FieldID == item.ID && q.RoomID == p.RoomID);
dic[item.FieldName] = contentmodel == null ? "" : contentmodel.FieldContent;
}
return(dic);
}).ToList();
dg.rows = results;
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}
public static Ui.DataGrid GetRoomBasicListByKeywords(List <int> RoomIDList, List <int> ProjectIDList, string RoomOwner, string OwnerPhone, string Keywords, List <string> SearchAreas, string orderBy, long startRowIndex, int pageSize, string OpenID, int ConnectStatus, bool canexport = false, int CompanyID = 0)
{
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("isnull([isParent],0)=0");
if (CompanyID > 0)
{
conditions.Add("exists(select 1 from Project where [CompanyID]=@CompanyID and [ID]=ViewRoomBasic.RoomID)");
parameters.Add(new SqlParameter("@CompanyID", CompanyID));
}
if (!string.IsNullOrEmpty(OpenID))
{
if (ConnectStatus > int.MinValue)
{
if (ConnectStatus == 1)
{
conditions.Add("[RoomID] in (select [ProjectID] from [WechatUser_Project] where OpenID=@OpenID)");
parameters.Add(new SqlParameter("@OpenID", OpenID));
}
else if (ConnectStatus == 0)
{
conditions.Add("[RoomID] not in (select [ProjectID] from [WechatUser_Project] where OpenID=@OpenID)");
parameters.Add(new SqlParameter("@OpenID", OpenID));
}
}
}
#region 关键字查询
string cmd = string.Empty;
if (!string.IsNullOrEmpty(Keywords))
{
cmd += " and (" + GetSearchConditions(SearchAreas, Keywords) + ")";
}
#endregion
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = new List <string>();
foreach (var ProjectID in ProjectIDList)
{
cmdlist.Add("([AllParentID] like '%," + ProjectID + ",%' or [RoomID] =" + 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 (!string.IsNullOrEmpty(RoomOwner))
{
conditions.Add("[RoomOwner]=@RoomOwner");
parameters.Add(new SqlParameter("@RoomOwner", RoomOwner));
}
if (!string.IsNullOrEmpty(OwnerPhone))
{
conditions.Add("[OwnerPhone]=@OwnerPhone");
parameters.Add(new SqlParameter("@OwnerPhone", OwnerPhone));
}
string fieldList = "[ViewRoomBasic].*";
string Statement = " from [ViewRoomBasic] where " + string.Join(" and ", conditions.ToArray()) + cmd;
ViewRoomBasic[] list = new ViewRoomBasic[] { };
var phoneList = new RoomPhoneRelation[] { };
int MinRoomID = 0;
int MaxRoomID = 0;
if (canexport)
{
list = GetList <ViewRoomBasic>("select " + fieldList + Statement + " " + orderBy, parameters).ToArray();
}
else
{
list = GetList <ViewRoomBasic>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray();
}
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
if (list.Length == 0)
{
dg.rows = list;
dg.total = 0;
dg.page = pageSize;
return(dg);
}
MinRoomID = list.Min(p => p.RoomID);
MaxRoomID = list.Max(p => p.RoomID);
phoneList = RoomPhoneRelation.GetRoomPhoneRelationListByMinMaxRoomID(MinRoomID, MaxRoomID);
var fieldlist = Foresight.DataAccess.DefineField.GetDefineFieldsByTable_Name(Utility.EnumModel.DefineFieldTableName.RoomBasic.ToString()).Where(p => p.IsShown).ToArray();
var contentlist = Foresight.DataAccess.RoomBasicField.GetRoomBasicFieldsByRoomIDList(MinRoomID, MaxRoomID);
var results = list.Select(p =>
{
var dic = p.ToJsonObject(ignoreDBColumn: false);
dic["RoomOwner1Name"] = string.Empty;
dic["RoomOwner1Phone"] = string.Empty;
dic["RoomOwner2Name"] = string.Empty;
dic["RoomOwner2Phone"] = string.Empty;
dic["Rent1Name"] = string.Empty;
dic["Rent1Phone"] = string.Empty;
if (phoneList.Length > 0)
{
var myPhoneList1 = phoneList.Where(q => q.RoomID == p.RoomID && q.RelationType.Equals("homefamily")).OrderByDescending(q => q.IsDefault).ThenBy(q => q.ID).ToArray();
var myPhoneList2 = phoneList.Where(q => q.RoomID == p.RoomID && q.RelationType.Equals("rentfamily")).OrderByDescending(q => q.IsDefault).ThenBy(q => q.ID).ToArray();
int count = 0;
foreach (var item in myPhoneList1)
{
count++;
dic["RoomOwner" + count.ToString() + "Name"] = item.RelationName;
dic["RoomOwner" + count.ToString() + "Phone"] = item.RelatePhoneNumber;
}
count = 0;
foreach (var item in myPhoneList2)
{
count++;
dic["Rent" + count.ToString() + "Name"] = item.RelationName;
dic["Rent" + count.ToString() + "Phone"] = item.RelatePhoneNumber;
}
}
foreach (var item in fieldlist)
{
var contentmodel = contentlist.FirstOrDefault(q => q.FieldID == item.ID && q.RoomID == p.RoomID);
dic[item.FieldName] = contentmodel == null ? "" : contentmodel.FieldContent;
}
return(dic);
}).ToList();
dg.rows = results;
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}
public static Ui.DataGrid GetViewTaiZhangGridByKeywords(string Keywords, List <int> RoomIDList, List <int> ProjectIDList, int BiaoID, int IsActive, int ID, int RelationStatus, string orderBy, long startRowIndex, int pageSize, int UserID = 0, bool canexport = false)
{
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("isnull(FullName,'')!=''");
string cmdcondition = ",0 as RelationCount";
if (ID > 0)
{
conditions.Add("[ID]!=@ProjectBiaoID");
conditions.Add("[BiaoGuiGe]!='总表'");
if (RelationStatus == 1)
{
conditions.Add("[ID] in (select [ReationID] from [ProjectBiao_Relation] where [ProjectBiaoID]=@ProjectBiaoID)");
}
else if (RelationStatus == 0)
{
conditions.Add("[ID] not in (select [ReationID] from [ProjectBiao_Relation] where [ProjectBiaoID]=@ProjectBiaoID)");
}
cmdcondition = ",(select count(1) from [ProjectBiao_Relation] where [ProjectBiaoID]=@ProjectBiaoID and [ReationID]=[ViewTaiZhang].ID) as RelationCount";
parameters.Add(new SqlParameter("@ProjectBiaoID", ID));
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, RoomIDName: "[ProjectID]", UserID: UserID);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false, RoomIDName: "[ProjectID]");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (!string.IsNullOrEmpty(Keywords))
{
conditions.Add("([FullName] like @Keywords or [Name] like @Keywords or [BiaoName] like @Keywords)");
parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%"));
}
if (BiaoID > 0)
{
conditions.Add("[BiaoID]=@BiaoID");
parameters.Add(new SqlParameter("@BiaoID", BiaoID));
}
if (IsActive > int.MinValue)
{
conditions.Add("[IsActive]=@IsActive");
parameters.Add(new SqlParameter("@IsActive", IsActive));
}
string fieldList = @"[ViewTaiZhang].*,
(select sum((isnull(ViewTaiZhang_2.[EndPoint],0)-isnull(ViewTaiZhang_2.StartPoint,0))*
(case when ViewTaiZhang_2.UnitPrice>0 then
ViewTaiZhang_2.UnitPrice else ViewTaiZhang_2.SummaryUnitPrice end)-ViewTaiZhang_2.Rate) from ViewTaiZhang as ViewTaiZhang_2 where ViewTaiZhang_2.ID
in (select ReationID from ProjectBiao_Relation where ProjectBiaoID=ViewTaiZhang.ID)) as CalculateReducePoint " + cmdcondition;
string Statement = " from [ViewTaiZhang] where " + string.Join(" and ", conditions.ToArray());
ViewTaiZhang[] list = new ViewTaiZhang[] { };
if (canexport)
{
list = GetList <ViewTaiZhang>("select " + fieldList + Statement + " " + orderBy, parameters).ToArray();
totalRows = list.Length;
}
else
{
list = GetList <ViewTaiZhang>(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;
return(dg);
}
public static Ui.DataGrid GetViewContractChargeSummaryGrid(int ContractID, string guid, DateTime StartTime, DateTime EndTime, string orderBy, long startRowIndex, int pageSize, bool IsLinShi = false, int UserID = 0, List <int> RoomIDList = null, List <int> ProjectIDList = null, string keywords = "", string ContractStatus = "", bool IsWarning = false, bool includefooter = false, DateTime?ReadyStartTime = null, DateTime?ReadyEndTime = null, int FeeStatus = 0, List <int> ChargeIDList = null, bool canexport = false)
{
ReSetParams();
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("isnull([ChargeID],0)!=0");
if (ChargeIDList != null && ChargeIDList.Count > 0)
{
conditions.Add("[ChargeID] in (" + string.Join(",", ChargeIDList.ToArray()) + ")");
}
if (ReadyStartTime.HasValue)
{
DateTime _ReadyStartTime = Convert.ToDateTime(ReadyStartTime);
if (_ReadyStartTime > DateTime.MinValue)
{
conditions.Add("([ReadyChargeTime]>=@ReadyStartTime or [ReadyChargeTime] is null)");
parameters.Add(new SqlParameter("@ReadyStartTime", ReadyStartTime));
}
}
if (ReadyEndTime.HasValue)
{
DateTime _ReadyEndTime = Convert.ToDateTime(ReadyEndTime);
if (_ReadyEndTime > DateTime.MinValue)
{
conditions.Add("([ReadyChargeTime]<=@ReadyEndTime or [ReadyChargeTime] is null)");
parameters.Add(new SqlParameter("@ReadyEndTime", ReadyEndTime));
}
}
if (ProjectIDList != null && ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, RoomIDName: "[RoomID]", IsContractFee: true, UserID: UserID);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (RoomIDList != null && RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false, RoomIDName: "[RoomID]", IsContractFee: true);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (!string.IsNullOrEmpty(keywords))
{
conditions.Add("(ContractID in (select ID from Contract where [RentName] like @keywords or [ContractNo] like @keywords or [ContractName] like @keywords) or [Name] like @keywords)");
parameters.Add(new SqlParameter("@keywords", "%" + keywords + "%"));
}
if (!string.IsNullOrEmpty(ContractStatus))
{
conditions.Add("ContractID in (select ID from Contract where [ContractStatus]=@ContractStatus)");
parameters.Add(new SqlParameter("@ContractStatus", ContractStatus));
}
if (IsWarning)
{
conditions.Add("([ReadyChargeTime]<=@ReadyChargeTime or [ReadyChargeTime] is null)");
parameters.Add(new SqlParameter("@ReadyChargeTime", DateTime.Now));
}
if (IsLinShi)
{
conditions.Add("[ChargeID] in (select ID from ChargeSummary where [FeeType]=4)");
}
if (ContractID > 0)
{
conditions.Add("[ContractID]=@ContractID");
parameters.Add(new SqlParameter("@ContractID", ContractID));
}
else if (!string.IsNullOrEmpty(guid))
{
conditions.Add("[GUID]=@GUID");
parameters.Add(new SqlParameter("@GUID", guid));
}
string fieldList = "[ViewContractChargeSummary].*";
string Statement = " from [ViewContractChargeSummary] where " + string.Join(" and ", conditions.ToArray());
ViewContractChargeSummary[] list = new ViewContractChargeSummary[] { };
if (FeeStatus == 0)
{
if (canexport)
{
list = GetList <ViewContractChargeSummary>("select " + fieldList + Statement + " " + orderBy, parameters).ToArray();
}
else
{
list = GetList <ViewContractChargeSummary>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray();
}
if (list.Length > 0)
{
var RoomFeeIDList = list.Where(p => p.RoomFeeID > 0).Select(p => p.RoomFeeID).ToList();
var ContractChargeIDList = list.Select(p => p.ID).ToList();
ViewRoomFeeHistoryDetailList = ViewRoomFeeHistoryDetail.GetViewRoomFeeHistoryDetailList(new List <int>(), StartTime, EndTime, StartTime, EndTime, new List <int>(), RoomFeeIDList, ContractChargeIDList, UserID: UserID, RequireOrderBy: false).Where(p => p.MonthTotalCost > 0).ToArray();
}
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = list;
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}
else
{
list = GetList <ViewContractChargeSummary>("select * from [ViewContractChargeSummary] where " + string.Join(" and ", conditions.ToArray()), parameters).ToArray();
if (list.Length > 0)
{
var RoomFeeIDList = list.Where(p => p.RoomFeeID > 0).Select(p => p.RoomFeeID).ToList();
var ContractChargeIDList = list.Select(p => p.ID).ToList();
ViewRoomFeeHistoryDetailList = ViewRoomFeeHistoryDetail.GetViewRoomFeeHistoryDetailList(new List <int>(), StartTime, EndTime, StartTime, EndTime, new List <int>(), RoomFeeIDList, ContractChargeIDList, UserID: UserID, RequireOrderBy: false).Where(p => p.MonthTotalCost > 0).ToArray();
}
if (FeeStatus == 1)
{
list = list.Where(p => p.CalcualtePayCost == 0 || p.CalcualteRestCost > 0).ToArray();
}
else if (FeeStatus == 2)
{
list = list.Where(p => p.CalcualtePayCost > 0 && p.CalcualteRestCost == 0).ToArray();
}
totalRows = list.Length;
int StartRowIndex = Convert.ToInt32(startRowIndex);
var finallist = list.Skip(StartRowIndex).Take(pageSize).ToArray();
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = finallist;
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}
}
public static Ui.DataGrid GetViewZhuangXiuGridByKeywords(string Keywords, string Status, List <int> RoomIDList, List <int> ProjectIDList, DateTime StartTime, DateTime EndTime, string orderBy, long startRowIndex, int pageSize, int UserID = 0)
{
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))
{
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 ([ApplicationMa] like '%" + keywords[i] + "%' or [RoomName] like '%" + keywords[i] + "%')";
}
else
{
cmd += " ([ApplicationMa] like '%" + keywords[i] + "%' or [RoomName] like '%" + keywords[i] + "%'))";
}
}
else if (i == 0)
{
cmd += " and (([ApplicationMa] like '%" + keywords[i] + "%' or [RoomName] like '%" + keywords[i] + "%') or";
}
else
{
cmd += " ([ApplicationMa] like '%" + keywords[i] + "%' or [RoomName] like '%" + keywords[i] + "%') or ";
}
}
}
#endregion
if (!string.IsNullOrEmpty(Status))
{
conditions.Add("[Status]=@Status");
parameters.Add(new SqlParameter("@Status", Status));
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, UserID: UserID);
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 (StartTime > DateTime.MinValue)
{
if (Status.Equals(Utility.EnumModel.ZhuangXiuStatus.shenpiyes.ToString()) || Status.Equals(Utility.EnumModel.ZhuangXiuStatus.shenpino.ToString()))
{
conditions.Add("[ApproveTime]>=@StartTime");
}
else if (Status.Equals(Utility.EnumModel.ZhuangXiuStatus.zhuangxiu.ToString()))
{
conditions.Add("[ConfirmZXTime]>=@StartTime");
}
else if (Status.Equals(Utility.EnumModel.ZhuangXiuStatus.yanshou.ToString()))
{
conditions.Add("[YanShouTime]>=@StartTime");
}
else
{
conditions.Add("[AddTime]>=@StartTime");
}
parameters.Add(new SqlParameter("@StartTime", StartTime));
}
if (EndTime > DateTime.MinValue)
{
if (Status.Equals(Utility.EnumModel.ZhuangXiuStatus.shenpiyes.ToString()) || Status.Equals(Utility.EnumModel.ZhuangXiuStatus.shenpino.ToString()))
{
conditions.Add("[ApproveTime]<=@EndTime");
}
else if (Status.Equals(Utility.EnumModel.ZhuangXiuStatus.zhuangxiu.ToString()))
{
conditions.Add("[ConfirmZXTime]<=@EndTime");
}
else if (Status.Equals(Utility.EnumModel.ZhuangXiuStatus.yanshou.ToString()))
{
conditions.Add("[YanShouTime]<=@EndTime");
}
else
{
conditions.Add("[AddTime]<=@EndTime");
}
parameters.Add(new SqlParameter("@EndTime", EndTime));
}
string fieldList = "[ViewZhuangXiu].*";
string Statement = " from [ViewZhuangXiu] where " + string.Join(" and ", conditions.ToArray()) + cmd;
ViewZhuangXiu[] list = GetList <ViewZhuangXiu>(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;
return(dg);
}
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);
}
public static Ui.DataGrid GetChaoBiaoAnalysisDetailGrid(string Keywords, List <int> ChargeIDList, List <int> RoomIDList, List <int> ProjectIDList, int ChargeStatus, DateTime StartWriteDate, DateTime EndWriteDate, string BiaoCategory, string orderBy, long startRowIndex, int pageSize, List <int> ImportFeeIDList = null, bool canexport = false)
{
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
#region search conditions
List <string> conditions = new List <string>();
if (ImportFeeIDList != null && ImportFeeIDList.Count > 0)
{
conditions.Add("[ID] in (" + string.Join(",", ImportFeeIDList.ToArray()) + ")");
}
conditions.Add("[ChargeID] in (select [ID] from [ChargeSummary] where [IsReading]=1)");
#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 (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 (ChargeIDList.Count > 0)
{
conditions.Add("[ChargeID] in (" + string.Join(",", ChargeIDList.ToArray()) + ")");
}
if (ChargeStatus != int.MinValue)
{
if (ChargeStatus == 99)
{
conditions.Add("(A.WeiShou=1 or A.YiShou=1)");
}
else if (ChargeStatus == 0)
{
conditions.Add("A.YiShou != 1");
}
else if (ChargeStatus == 1)
{
conditions.Add("A.YiShou = 1");
}
}
#endregion
string fieldList = @"A.*, (case when A.YiShou=1 then (select top 1 StartTime from [RoomFeeHistory]
where [RoomFeeHistory].ImportFeeID=A.ID and ChargeState in (1,4)) end) as HistoryStartTime,
(case when A.YiShou=1 then (select top 1 EndTime from [RoomFeeHistory]
where [RoomFeeHistory].ImportFeeID=A.ID and ChargeState in (1,4)) end) as HistoryEndTime,
(case when A.YiShou=1 then (select top 1 UseCount from [RoomFeeHistory]
where [RoomFeeHistory].ImportFeeID=A.ID and ChargeState in (1,4)) end) as HistoryUseCount,
(case when A.YiShou=1 then (select top 1 UnitPrice from [RoomFeeHistory]
where [RoomFeeHistory].ImportFeeID=A.ID and ChargeState in (1,4)) end) as HistoryUnitPrice,
(case when A.YiShou=1 then (select top 1 RealCost from [RoomFeeHistory]
where [RoomFeeHistory].ImportFeeID=A.ID and ChargeState in (1,4)) end) as HistoryRealCost";
string Statement = " from (select *,(case when exists (select top 1 [ImportFeeID] from [RoomFee] where isnull(IsStart,1)=1 and isnull(IsCharged,0)=0 and RoomFee.ImportFeeID=ViewImportFee.ID) then 1 else 0 end) as WeiShou,(case when exists (select top 1 [ImportFeeID] from [RoomFeeHistory] where ChargeState in (1,4) and RoomFeeHistory.ImportFeeID=ViewImportFee.ID) then 1 else 0 end) as YiShou from [ViewImportFee] where ([ID] in (select [ImportFeeID] from [RoomFee] where isnull(IsStart,1)=1 and isnull(IsCharged,0)=0) or [ID] in (select [ImportFeeID] from [RoomFeeHistory] where ChargeState in (1,4))))A where" + string.Join(" and ", conditions.ToArray()) + cmd;
ViewImportFeeDetail[] list = new ViewImportFeeDetail[] { };
if (canexport)
{
list = GetList <ViewImportFeeDetail>("select " + fieldList + Statement + " " + orderBy, parameters).ToArray();
}
else
{
list = GetList <ViewImportFeeDetail>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray();
}
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = list;
string footertext = @"select '总合计' as [FullName], sum(isnull(case when B.YiShou=1 then B.HistoryUseCount else B.TotalPoint end,0)) as HistoryUseCount, sum(isnull(case when B.YiShou=1 then B.HistoryRealCost else B.TotalPrice end,0)) as HistoryRealCost from
(select A.*, (case when A.YiShou=1 then (select top 1 StartTime from [RoomFeeHistory]
where [RoomFeeHistory].ImportFeeID=A.ID and ChargeState in (1,4)) end) as HistoryStartTime,
(case when A.YiShou=1 then (select top 1 EndTime from [RoomFeeHistory]
where [RoomFeeHistory].ImportFeeID=A.ID and ChargeState in (1,4)) end) as HistoryEndTime,
(case when A.YiShou=1 then (select top 1 UseCount from [RoomFeeHistory]
where [RoomFeeHistory].ImportFeeID=A.ID and ChargeState in (1,4)) end) as HistoryUseCount,
(case when A.YiShou=1 then (select top 1 UnitPrice from [RoomFeeHistory]
where [RoomFeeHistory].ImportFeeID=A.ID and ChargeState in (1,4)) end) as HistoryUnitPrice,
(case when A.YiShou=1 then (select top 1 RealCost from [RoomFeeHistory]
where [RoomFeeHistory].ImportFeeID=A.ID and ChargeState in (1,4)) end) as HistoryRealCost from (select *,(case when exists (select top 1 [ImportFeeID] from [RoomFee] where isnull(IsStart,1)=1 and isnull(IsCharged,0)=0 and RoomFee.ImportFeeID=ViewImportFee.ID) then 1 else 0 end) as WeiShou,(case when exists (select top 1 [ImportFeeID] from [RoomFeeHistory] where ChargeState in (1,4) and RoomFeeHistory.ImportFeeID=ViewImportFee.ID) then 1 else 0 end) as YiShou from [ViewImportFee] where ([ID] in (select [ImportFeeID] from [RoomFee] where isnull(IsStart,1)=1 and isnull(IsCharged,0)=0) or [ID] in (select [ImportFeeID] from [RoomFeeHistory] where ChargeState in (1,4))))A where" + string.Join(" and ", conditions.ToArray()) + cmd + ")B";
dg.footer = GetList <ViewImportFeeDetail>(footertext, parameters).ToArray();
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}
public static Ui.DataGrid GetHistorySummaryGroupByTypeName(List <int> RoomIDList, List <int> ProjectIDList, DateTime StartChargeTime, DateTime EndChargeTime, string ChargeMan, List <int> ChargeSummaryIDList, List <int> ChargeTypeIDList, List <int> ChargeStateList, int RoomFeeOrderID, bool IsRoomFeeSearch, string OpenID, List <int> HistoryIDList = null, bool DeleteTempHistoryIDList = true, int UserID = 0)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
List <string> conditions2 = new List <string>();
conditions2.Add("1=1");
List <string> conditions3 = new List <string>();
conditions3.Add("1=1");
if (HistoryIDList != null && HistoryIDList.Count > 0)
{
if (HistoryIDList.Count > 2)
{
ViewRoomFeeHistory.CreateTempTable(HistoryIDList, DeleteTempHistoryIDList, UserID: UserID);
conditions.Add("EXISTS (SELECT 1 FROM [TempIDs] WHERE id=rfh.HistoryID and UserID=" + UserID + ")");
conditions2.Add("EXISTS (SELECT 1 FROM [TempIDs] WHERE id in (select HistoryID from RoomFeeHistory where PrintID=prh.ID) and UserID=" + UserID + ")");
}
else if (HistoryIDList.Count > 0)
{
conditions.Add("rfh.HistoryID in (" + string.Join(",", HistoryIDList.ToArray()) + ")");
conditions2.Add("prh.ID in (select PrintID from [RoomFeeHistory] where HistoryID in (" + string.Join(",", HistoryIDList.ToArray()) + "))");
}
}
if (!string.IsNullOrEmpty(OpenID))
{
conditions.Add("rfh.[RoomID] in (select [ProjectID] from [WechatUser_Project] where [OpenID]=@OpenID)");
parameters.Add(new SqlParameter("@OpenID", OpenID));
}
if (ChargeStateList.Count > 0)
{
if (ChargeStateList.Contains(1) || ChargeStateList.Contains(4))
{
conditions2.Add("prh.[IsCancel]=0");
}
conditions2.Add("prh.ID in (select PrintID from RoomFeeHistory where ChargeState in (" + string.Join(",", ChargeStateList.ToArray()) + "))");
}
if (RoomFeeOrderID > 0)
{
conditions2.Add("isnull(prh.[RoomFeeOrderID],0)=@RoomFeeOrderID");
parameters.Add(new SqlParameter("@RoomFeeOrderID", RoomFeeOrderID));
}
else if (IsRoomFeeSearch)
{
conditions2.Add("isnull(prh.[RoomFeeOrderID],0)=0");
conditions2.Add("prh.[ID] in (select [PrintID] from [RoomFeeHistory] where [ChargeID] in (select [ID] from [ChargeSummary] where isnull([IsOrderFeeOn],0)=1) or isnull(ChargeID,0)=0)");
if (StartChargeTime > DateTime.MinValue)
{
conditions.Add("rfh.[ChargeTime]>=@StartChargeTime");
parameters.Add(new SqlParameter("@StartChargeTime", StartChargeTime));
}
if (EndChargeTime > DateTime.MinValue)
{
conditions.Add("rfh.[ChargeTime]<=@EndChargeTime");
parameters.Add(new SqlParameter("@EndChargeTime", EndChargeTime));
}
}
else
{
if (StartChargeTime > DateTime.MinValue)
{
conditions.Add("Convert(nvarchar(10),rfh.[ChargeTime],120)>=@StartChargeTime");
parameters.Add(new SqlParameter("@StartChargeTime", StartChargeTime));
}
if (EndChargeTime > DateTime.MinValue)
{
conditions.Add("Convert(nvarchar(10),rfh.[ChargeTime],120)<=@EndChargeTime");
parameters.Add(new SqlParameter("@EndChargeTime", EndChargeTime));
}
}
if (RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, RoomIDName: "rfh.[RoomID]");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, RoomIDName: "rfh.[RoomID]", UserID: UserID);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
#region 收款人查询
string cmd = string.Empty;
if (!string.IsNullOrEmpty(ChargeMan))
{
string[] keywords = ChargeMan.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 (prh.[ChargeMan] like '%" + keywords[i] + "%')";
}
else
{
cmd += " (prh.[ChargeMan] like '%" + keywords[i] + "%'))";
}
}
else if (i == 0)
{
cmd += " and ((prh.[ChargeMan] like '%" + keywords[i] + "%') or";
}
else
{
cmd += " (prh.[ChargeMan] like '%" + keywords[i] + "%') or ";
}
}
}
#endregion
//if (!string.IsNullOrEmpty(ChargeMan))
//{
// conditions2.Add("prh.[ChargeMan] like '%" + ChargeMan + "%'");
//}
if (ChargeSummaryIDList.Count > 0)
{
conditions.Add("rfh.[ChargeID] in (" + string.Join(",", ChargeSummaryIDList.ToArray()) + ")");
}
if (ChargeTypeIDList.Count > 0)
{
conditions2.Add("(prh.[ChageType1] in (" + string.Join(",", ChargeTypeIDList.ToArray()) + ") or prh.[ChageType2] in (" + string.Join(",", ChargeTypeIDList.ToArray()) + "))");
conditions3.Add("cmt.[ChargeTypeID] in (" + string.Join(",", ChargeTypeIDList.ToArray()) + ")");
}
var type_list = GetList <ChargeMoneyTypeDetails>(@"select * from ChargeMoneyType", parameters).ToList();
var history1 = GetList <ChargeMoneyTypeDetails>(@"select sum(isnull(RealCost,0)-isnull(prh.RealMoneyCost2,0)) as RealCost,prh.ChageType1 as ChargeTypeID from PrintRoomFeeHistory prh where " + string.Join(" and ", conditions2.ToArray()) + cmd + @" and prh.[ID] in (select [PrintID] from RoomFeeHistory rfh where " + string.Join(" and ", conditions.ToArray()) + @")
Group by prh.ChageType1", parameters).ToArray();
var history2 = GetList <ChargeMoneyTypeDetails>(@"select sum(isnull(prh.RealMoneyCost2,0)) as RealCost,prh.ChageType2 as ChargeTypeID from PrintRoomFeeHistory prh where " + string.Join(" and ", conditions2.ToArray()) + cmd + @" and prh.[ID] in (select [PrintID] from RoomFeeHistory rfh where " + string.Join(" and ", conditions.ToArray()) + @")
Group by prh.ChageType2", parameters).ToArray();
decimal RealCost1 = 0;
decimal RealCost2 = 0;
var finalList = new List <ChargeMoneyTypeDetails>();
foreach (var item in type_list)
{
RealCost1 = history1.Where(p => p.ChargeTypeID == item.ChargeTypeID).Sum(p => p.RealCost);
RealCost2 = history2.Where(p => p.ChargeTypeID == item.ChargeTypeID).Sum(p => p.RealCost);
if ((RealCost1 + RealCost2) > 0)
{
item.RealCost = RealCost1 + RealCost2;
finalList.Add(item);
}
}
var TypeIDList = type_list.Select(p => p.ChargeTypeID).ToList();
RealCost1 = history1.Where(p => !TypeIDList.Contains(p.ChargeTypeID)).Sum(p => p.RealCost);
RealCost2 = history2.Where(p => !TypeIDList.Contains(p.ChargeTypeID)).Sum(p => p.RealCost);
if ((RealCost1 + RealCost2) > 0)
{
var item = new ChargeMoneyTypeDetails();
item.ChargeTypeID = 0;
item.ChargeTypeName = "其他";
item.RealCost = (RealCost1 + RealCost2);
finalList.Add(item);
}
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = finalList.ToArray();
dg.total = finalList.Count;
dg.page = 1;
return(dg);
}
public static Ui.DataGrid GetDepositSummaryGroupByTypeName(List <int> RoomIDList, List <int> ProjectIDList, DateTime StartChargeTime, DateTime EndChargeTime, int CompanyID, string ChargeMan, int RoomFeeOrderID, bool IsRoomFeeSearch, List <int> HistoryIDList = null, bool DeleteTempHistoryIDList = true, int UserID = 0)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
string cmdNow = string.Empty;
string cmdWhere = string.Empty;
if (HistoryIDList != null && HistoryIDList.Count > 0)
{
if (HistoryIDList.Count > 2)
{
ViewRoomFeeHistory.CreateTempTable(HistoryIDList, DeleteTempHistoryIDList, UserID: UserID);
cmdNow += " and EXISTS (SELECT 1 FROM [TempIDs] WHERE id=rfh.HistoryID and UserID=" + UserID + ")";
}
else if (HistoryIDList.Count > 0)
{
cmdNow += " and rfh.HistoryID in (" + string.Join(",", HistoryIDList.ToArray()) + ")";
}
}
if (RoomFeeOrderID > 0)
{
cmdNow += " and [PrintID] in (select [ID] from [PrintRoomFeeHistory] where isnull([RoomFeeOrderID],0)=@RoomFeeOrderID)";
parameters.Add(new SqlParameter("@RoomFeeOrderID", RoomFeeOrderID));
}
else if (IsRoomFeeSearch)
{
cmdNow += " and [PrintID] in (select [ID] from [PrintRoomFeeHistory] where isnull([RoomFeeOrderID],0)=0)";
cmdNow += " and ([ChargeID] in (select [ID] from [ChargeSummary] where isnull([IsOrderFeeOn],0)=1) or isnull(ChargeID,0)=0)";
if (StartChargeTime > DateTime.MinValue)
{
cmdNow += " and [ChargeTime]>=@StartChargeTime";
parameters.Add(new SqlParameter("@StartChargeTime", StartChargeTime));
}
if (EndChargeTime > DateTime.MinValue)
{
cmdNow += " and [ChargeTime]<=@EndChargeTime";
parameters.Add(new SqlParameter("@EndChargeTime", EndChargeTime));
}
}
else
{
if (StartChargeTime > DateTime.MinValue)
{
cmdNow += " and Convert(nvarchar(10),[ChargeTime],120)>=@StartChargeTime";
parameters.Add(new SqlParameter("@StartChargeTime", StartChargeTime));
}
if (EndChargeTime > DateTime.MinValue)
{
cmdNow += " and Convert(nvarchar(10),[ChargeTime],120)<=@EndChargeTime";
parameters.Add(new SqlParameter("@EndChargeTime", EndChargeTime));
}
}
#region 收款人查询
string cmd = string.Empty;
if (!string.IsNullOrEmpty(ChargeMan))
{
string[] keywords = ChargeMan.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 ([ChargeMan] like '%" + keywords[i] + "%')";
}
else
{
cmd += " ([ChargeMan] like '%" + keywords[i] + "%'))";
}
}
else if (i == 0)
{
cmd += " and (([ChargeMan] like '%" + keywords[i] + "%') or";
}
else
{
cmd += " ([ChargeMan] like '%" + keywords[i] + "%') or ";
}
}
}
cmdNow += cmd;
#endregion
//if (!string.IsNullOrEmpty(ChargeMan))
//{
// cmdNow += " and [ChargeMan]=@ChargeMan";
// parameters.Add(new SqlParameter("@ChargeMan", ChargeMan));
//}
if (CompanyID != 1 && CompanyID > 0)
{
cmdWhere += " and [CompanyID]=@CompanyID";
parameters.Add(new SqlParameter("@CompanyID", CompanyID));
}
if (RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IsContractFee: true);
cmdNow += " and (" + string.Join(" or ", cmdlist.ToArray()) + ")";
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IsContractFee: true, UserID: UserID);
cmdNow += " and (" + string.Join(" or ", cmdlist.ToArray()) + ")";
}
var type_list = GetList <ChargeMoneyTypeDetails>(@"select * from ChargeMoneyType", parameters).ToList();
var history1 = GetList <ChargeMoneyTypeDetails>(@"select sum(isnull(RealMoneyCost2,0)) as RealCost,ChageType2 as ChargeTypeID from PrintRoomFeeHistory
where [ID] in (select [PrintID] from RoomFeeHistory rfh where ChargeState in(3,6,7) " + cmdNow + @")
group by ChageType2", parameters).ToArray();
var history2 = GetList <ChargeMoneyTypeDetails>(@"select sum(isnull(RealCost,0)-isnull(RealMoneyCost2,0)) as RealCost,ChageType1 as ChargeTypeID from PrintRoomFeeHistory
where [ID] in (select [PrintID] from RoomFeeHistory rfh where ChargeState in(3,6,7) " + cmdNow + @")
group by ChageType1", parameters).ToArray();
decimal RealCost1 = 0;
decimal RealCost2 = 0;
var finalList = new List <ChargeMoneyTypeDetails>();
foreach (var item in type_list)
{
RealCost1 = history1.Where(p => p.ChargeTypeID == item.ChargeTypeID).Sum(p => p.RealCost);
RealCost2 = history2.Where(p => p.ChargeTypeID == item.ChargeTypeID).Sum(p => p.RealCost);
if ((RealCost1 + RealCost2) > 0)
{
item.RealCost = RealCost1 + RealCost2;
finalList.Add(item);
}
}
var ChargeTypeIDList = type_list.Select(p => p.ChargeTypeID).ToList();
RealCost1 = history1.Where(p => !ChargeTypeIDList.Contains(p.ChargeTypeID)).Sum(p => p.RealCost);
RealCost2 = history2.Where(p => !ChargeTypeIDList.Contains(p.ChargeTypeID)).Sum(p => p.RealCost);
if ((RealCost1 + RealCost2) > 0)
{
var item = new ChargeMoneyTypeDetails();
item.ChargeTypeID = 0;
item.ChargeTypeName = "其他";
item.RealCost = (RealCost1 + RealCost2);
finalList.Add(item);
}
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = finalList.ToArray();
dg.total = finalList.Count;
dg.page = 1;
return(dg);
}
public static ViewCustomerService[] GetViewCustomerServiceListByStatus(int status, int UserID, long startRowIndex, int pageSize, out long totalRows, bool onlybaoshi = false, List <int> ProjectIDList = null)
{
ResetCache();
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
string orderby = " order by [AddTime] desc";
conditions.Add("1=1");
if (ProjectIDList != null && ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, RoomIDName: "[ProjectID]", UserID: UserID);
if (UserID > -1)
{
cmdlist.Add("[ProjectID]=0");
}
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (status >= 0)
{
conditions.Add("[ServiceStatus]=@ServiceStatus");
parameters.Add(new SqlParameter("@ServiceStatus", status));
if (status == 1)
{
orderby = " order by [BanJieTime] desc";
}
}
if (UserID > 0)
{
conditions.Add("([DepartmentID] in (select DepartmentID from [UserDepartment] where [UserID]=@UserID) or DepartmentID is null or DepartmentID=0)");
if (status != 3)
{
conditions.Add("REPLACE(REPLACE([ServiceAccpetManID],'[',','),']',',') like '%," + UserID.ToString() + ",%'");
}
parameters.Add(new SqlParameter("@UserID", UserID));
}
if (onlybaoshi)
{
string cmdwhere = string.Empty;
if (UserID > 0)
{
cmdwhere += " and [AddUserID]=@AddUserID";
parameters.Add(new SqlParameter("@AddUserID", UserID));
}
conditions.Add("ID in (select ID from [CustomerService] where [WechatServiceID] in (select ID from [Wechat_Service] where 1=1 " + cmdwhere + "))");
}
string fieldList = "[ViewCustomerService].*";
string Statement = " from [ViewCustomerService] where " + string.Join(" and ", conditions.ToArray());
var list = GetList <ViewCustomerService>(fieldList, Statement, parameters, orderby, startRowIndex, pageSize, out totalRows).ToArray();
if (UserID == -1)
{
var UserList = User.GetAPPUserList();
var UserIDList = UserList.Select(p => p.UserID).ToList();
list = list.Where(p =>
{
if (string.IsNullOrEmpty(p.ServiceAccpetManID))
{
return(true);
}
string[] ServiceAccpetManIDArray = p.ServiceAccpetManID.Replace("[", ",").Replace("]", ",").Split(',');
foreach (var MyUserID in UserIDList)
{
foreach (var item in ServiceAccpetManIDArray)
{
if (MyUserID.ToString().Equals(item))
{
return(false);
}
}
}
return(true);
}).ToArray();
}
return(list);
}
public static Ui.DataGrid GetMall_RoomOwnerBalanceDetailGridByKeywords(string Keywords, DateTime StartTime, DateTime EndTime, int BalanceStatus, long startRowIndex, int pageSize, List <int> RoomIDList, List <int> ProjectIDList, int UserID = 0)
{
long totalRows = 0;
string OrderBy = " order by AddTime desc";
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (RoomIDList.Count > 0)
{
conditions.Add("[ProjectID] in (" + string.Join(",", RoomIDList.ToArray()) + ")");
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, RoomIDName: "[ProjectID]", UserID: UserID);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (BalanceStatus == 23)
{
conditions.Add("[BalanceStatus] in (2,3)");
}
else if (BalanceStatus > 0)
{
conditions.Add("[BalanceStatus]=@BalanceStatus");
parameters.Add(new SqlParameter("@BalanceStatus", BalanceStatus));
}
if (StartTime > DateTime.MinValue)
{
conditions.Add("[AddTime]>=@StartTime");
parameters.Add(new SqlParameter("@StartTime", StartTime));
}
if (EndTime > DateTime.MinValue)
{
conditions.Add("[AddTime]<=@EndTime");
parameters.Add(new SqlParameter("@EndTime", EndTime));
}
if (!string.IsNullOrEmpty(Keywords))
{
conditions.Add("([RoomOwnerName] like @keywords)");
parameters.Add(new SqlParameter("@keywords", "%" + Keywords + "%"));
}
string fieldList = "A.*";
string Statement = " from (select [Mall_RoomOwnerBalance].*,[RoomPhoneRelation].RelationName as RoomOwnerName,[Project].FullName,[Project].Name as RoomName from [Mall_RoomOwnerBalance] left join [RoomPhoneRelation] on [RoomPhoneRelation].ID=[Mall_RoomOwnerBalance].RoomPhoneRelationID left join [Project] on [Project].ID=[RoomPhoneRelation].RoomID)A where " + string.Join(" and ", conditions.ToArray());
Mall_RoomOwnerBalanceDetail[] list = GetList <Mall_RoomOwnerBalanceDetail>(fieldList, Statement, parameters, OrderBy, startRowIndex, pageSize, out totalRows).ToArray();
var rule_list = Mall_BalanceRule.GetMall_BalanceRules().ToArray();
foreach (var item in list)
{
var my_rule = rule_list.FirstOrDefault(p => p.ID == item.BalanceRuleID);
if (my_rule != null)
{
item.BalanceRuleName = my_rule.Title;
}
}
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = list;
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}
public static Ui.DataGrid GetCustomerServiceGridByKeywords(string Keywords, List <int> RoomIDList, DateTime StartTime, DateTime EndTime, int ServiceStatus, int PayStatus, int ServiceAccpetManID, string orderBy, long startRowIndex, int pageSize, int UserID, List <int> EqualProjectIDList = null, List <int> InProjectIDList = null, int ServiceType = 0, int ServiceRange = 1, List <int> PublicProjectIDList = null, bool canexport = false)
{
ResetCache();
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
List <string> cmdlist = new List <string>();
conditions.Add("1=1");
#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 (isnull([ServiceNumber],'') like '%" + keywords[i] + "%' or [AddCustomerName] like '%" + keywords[i] + "%' or [AddCallPhone] like '%" + keywords[i] + "%' or [ServiceAccpetMan] like '%" + keywords[i] + "%' or [ServiceFullName] like '%" + keywords[i] + "%' or [ServiceContent] like '%" + keywords[i] + "%')";
}
else
{
cmd += " (isnull([ServiceNumber],'') like '%" + keywords[i] + "%' or [AddCustomerName] like '%" + keywords[i] + "%' or [AddCallPhone] like '%" + keywords[i] + "%' or [ServiceAccpetMan] like '%" + keywords[i] + "%' or [ServiceFullName] like '%" + keywords[i] + "%' or [ServiceContent] like '%" + keywords[i] + "%'))";
}
}
else if (i == 0)
{
cmd += " and ((isnull([ServiceNumber],'') like '%" + keywords[i] + "%' or [AddCustomerName] like '%" + keywords[i] + "%' or [AddCallPhone] like '%" + keywords[i] + "%' or [ServiceAccpetMan] like '%" + keywords[i] + "%' or [ServiceFullName] like '%" + keywords[i] + "%' or [ServiceContent] like '%" + keywords[i] + "%') or";
}
else
{
cmd += " (isnull([ServiceNumber],'') like '%" + keywords[i] + "%' or [AddCustomerName] like '%" + keywords[i] + "%' or [AddCallPhone] like '%" + keywords[i] + "%' or [ServiceAccpetMan] like '%" + keywords[i] + "%' or [ServiceFullName] like '%" + keywords[i] + "%' or [ServiceContent] like '%" + keywords[i] + "%') or ";
}
}
}
#endregion
if (ServiceType == 2)
{
conditions.Add("isnull([IsSuggestion],0)=1");
}
else
{
conditions.Add("isnull([IsSuggestion],0)=0");
cmdlist = new List <string>();
if (ServiceRange == 1)
{
conditions.Add("isnull([PublicProjectID],0)=0");
}
if (ServiceRange == 2)
{
conditions.Add("isnull([PublicProjectID],0)>0");
conditions.Add("isnull([ProjectID],0)=0");
}
if (InProjectIDList != null && InProjectIDList.Count > 0)
{
cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(InProjectIDList, IncludeRelation: false, RoomIDName: "[ProjectID]", UserID: UserID);
}
if (EqualProjectIDList != null && EqualProjectIDList.Count > 0)
{
cmdlist.Add("([ProjectID] in (" + string.Join(",", EqualProjectIDList.ToArray()) + "))");
}
if (PublicProjectIDList != null && PublicProjectIDList.Count > 0)
{
cmdlist.Add("[PublicProjectID] in (" + string.Join(",", PublicProjectIDList.ToArray()) + ")");
}
if (cmdlist.Count > 0)
{
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (ServiceAccpetManID > 0)
{
conditions.Add("REPLACE(REPLACE([ServiceAccpetManID],'[',','),']',',') like '%," + ServiceAccpetManID.ToString() + ",%'");
}
if (PayStatus == 0)
{
conditions.Add("isnull([TotalFee],0)=0");
}
if (PayStatus == 1)
{
conditions.Add("isnull([TotalFee],0)>0");
}
if (RoomIDList.Count > 0)
{
cmdlist = new List <string>();
cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false, RoomIDName: "[ProjectID]");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (StartTime > DateTime.MinValue)
{
conditions.Add("[StartTime]>=@StartTime");
parameters.Add(new SqlParameter("@StartTime", StartTime));
}
if (EndTime > DateTime.MinValue)
{
conditions.Add("[StartTime]<=@EndTime");
parameters.Add(new SqlParameter("@EndTime", EndTime));
}
if (ServiceStatus > -1)
{
if (ServiceStatus == 100)
{
conditions.Add("[ServiceStatus] in (3,10)");
}
else
{
conditions.Add("[ServiceStatus]=@ServiceStatus");
}
parameters.Add(new SqlParameter("@ServiceStatus", ServiceStatus));
}
}
string fieldList = "[ViewCustomerService].*";
string Statement = " from [ViewCustomerService] where " + string.Join(" and ", conditions.ToArray()) + cmd;
ViewCustomerService[] list = new ViewCustomerService[] { };
if (canexport)
{
list = GetList <ViewCustomerService>("select " + fieldList + Statement + " " + orderBy, parameters).ToArray();
totalRows = list.Length;
}
else
{
list = GetList <ViewCustomerService>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray();
}
string footer_text = "select '合计' as [ServiceFullName],100 as [ServiceStatus],sum(CASE WHEN ISNUMERIC(HandelFee)=1 THEN convert(decimal(18,2),HandelFee) ELSE 0 END) as HandelFee,sum(isnull(TotalFee,0)) as TotalFee from [ViewCustomerService] where " + string.Join(" and ", conditions.ToArray()) + cmd;
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = list;
dg.total = totalRows;
dg.page = pageSize;
dg.footer = GetList <ViewCustomerService>(footer_text, parameters).ToArray();
return(dg);
}
public static Ui.DataGrid GetChargeMeter_ProjectFeeDetailGridByKeywords(string Keywords, List <int> RoomIDList, List <int> ProjectIDList, int MeterCategoryID, int MeterType, int MeterChargeID, string orderBy, long startRowIndex, int pageSize, int UserID = 0, int ChargeState = 0)
{
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("([IsDeleted]=0 or [IsDeleted] is null)");
if (ChargeState == 1)//已收
{
conditions.Add("[ChargeState] in (1,4)");
}
if (ChargeState == 2)//未收
{
conditions.Add("[RealRoomFeeID]>0");
}
if (ChargeState == 3)//作废
{
conditions.Add("[ChargeState]=2");
}
if (ChargeState == 4)//催收中
{
conditions.Add("[ChargeState]=5");
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, RoomIDName: "[ProjectID]", UserID: UserID);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false, RoomIDName: "[ProjectID]");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (!string.IsNullOrEmpty(Keywords))
{
conditions.Add("([MeterName] like @Keywords or [MeterNumber] like @Keywords)");
parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%"));
}
if (MeterCategoryID > 0)
{
conditions.Add("[MeterCategoryID]=@MeterCategoryID");
parameters.Add(new SqlParameter("@MeterCategoryID", MeterCategoryID));
}
if (MeterType > 0)
{
conditions.Add("[MeterType]=@MeterType");
parameters.Add(new SqlParameter("@MeterType", MeterType));
}
if (MeterChargeID > 0)
{
conditions.Add("[MeterChargeID]=@MeterChargeID");
parameters.Add(new SqlParameter("@MeterChargeID", MeterChargeID));
}
string cmdcolumns = string.Empty;
ChargeMeter_ProjectFeeDetail[] list = new ChargeMeter_ProjectFeeDetail[] { };
string fieldList = @"A.*";
string Statement = " from " + CommSqlText + " where " + string.Join(" and ", conditions.ToArray());
list = GetList <ChargeMeter_ProjectFeeDetail>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray();
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.page = pageSize;
dg.total = totalRows;
if (list.Length == 0)
{
dg.rows = list;
return(dg);
}
var feeList = RoomFeeAnalysis.GetRoomFeeAnalysisListByIDList(list.Select(p => p.RoomFeeID).ToList());
foreach (var item in list)
{
var myFee = feeList.FirstOrDefault(p => p.ID == item.RoomFeeID);
if (myFee != null)
{
item.RoomFeeUnitPrice = myFee.CalculateUnitPrice;
}
}
dg.rows = list;
return(dg);
}
