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 (Status == 100)
{
conditions.Add("[ServiceStatus] in (3,10)");
}
var list = GetList <CustomerService>("select * from [CustomerService] where " + string.Join(" and ", conditions.ToArray()), parameters).ToArray();
var myProjectIDList = new int[] { };
if (ProjectIDList.Count > 0)
{
myProjectIDList = Project.GetProjectIDListbyIDList(ProjectIDList: ProjectIDList, UserID: UserID);
}
if (myProjectIDList.Length > 0)
{
list = list.Where(p => myProjectIDList.Contains(p.ProjectID)).ToArray();
}
return(list);
}
public static PaySummaryAnalysis[] GetPaySummaryAnalysisList(List <int> ProjectIDList, List <int> RoomIDList, DateTime StartTime, DateTime EndTime, int UserID = 0)
{
List <string> conditions = new List <string>();
List <SqlParameter> parameters = new List <SqlParameter>();
conditions.Add("1=1");
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 (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 cmdtext = "select A.* from (select ProjectID,PaySummaryID,sum(PayMoney) as TotalCost from PayService where " + string.Join(" and ", conditions.ToArray()) + " group by ProjectID, PaySummaryID)A where A.TotalCost>0";
PaySummaryAnalysis[] list = GetList <PaySummaryAnalysis>(cmdtext, parameters).ToArray();
return(list);
}
public static RoomFeeHistory[] GetRoomFeeHistoryListByRoomIDList(List <int> RoomIDList, int ChargeID = 0, List <int> ChargeFeeID = null, List <int> ProjectIDList = null, int UserID = 0, int ChargeFeeType = 0, bool OnlyChargeFee = false)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
if (OnlyChargeFee)
{
conditions.Add("ChargeState in (1,4)");
}
if (ChargeFeeType > 0)
{
conditions.Add("[ChargeID] in (select [ID] from [ChargeSummary] where [ChargeFeeType]=@ChargeFeeType)");
parameters.Add(new SqlParameter("@ChargeFeeType", ChargeFeeType));
}
if (ChargeID > 0)
{
conditions.Add("[ChargeID]=@ChargeID");
parameters.Add(new SqlParameter("@ChargeID", ChargeID));
}
if (ChargeFeeID != null && ChargeFeeID.Count > 0)
{
conditions.Add("[ChargeFeeID] in (" + string.Join(",", ChargeFeeID.ToArray()) + ")");
}
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: true, UserID: UserID);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
return(GetList <RoomFeeHistory>("select * from [RoomFeeHistory] where " + string.Join(" and ", conditions.ToArray()), parameters).ToArray());
}
public static PaySummaryDetail[] GetPaySummaryDetailListByRoomID(List <int> RoomIDList, List <int> ProjectIDList, DateTime StartTime, DateTime EndTime, int UserID)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
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));
}
if (RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false, RoomIDName: "[ProjectID]");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, RoomIDName: "[ProjectID]", UserID: UserID);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
string Statement = @" select*,A.TotalMoney from PaySummary left join
(
select PaySummaryID, sum(isnull(PayMoney,0)) as TotalMoney from PayService where " + string.Join(" and ", conditions) + @" group by PaySummaryID
)A on A.PaySummaryID=PaySummary.ID";
PaySummaryDetail[] list = GetList <PaySummaryDetail>(Statement, parameters).ToArray();
return(list);
}
public static PrintRoomFeeHistory[] GetPrintRoomFeeHistoryListByRoomProjectIDList(DateTime StartTime, DateTime EndTime, List <int> ProjectIDList, List <int> RoomIDList, int UserID = 0, List <int> ChargeStateList = null)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (ChargeStateList != null && ChargeStateList.Count > 0)
{
conditions.Add("[ChargeState] in (" + string.Join(",", ChargeStateList.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()) + ")");
}
if (StartTime > DateTime.MinValue)
{
conditions.Add("Convert(nvarchar(10),[ChargeTime],120)>=@StartTime");
parameters.Add(new SqlParameter("@StartTime", StartTime));
}
if (EndTime > DateTime.MinValue)
{
conditions.Add("Convert(nvarchar(10),[ChargeTime],120)<=@EndTime");
parameters.Add(new SqlParameter("@EndTime", EndTime));
}
string cmdtext = "select * from [PrintRoomFeeHistory] where [ID] in (select [PrintID] from [RoomFeeHistory] where " + string.Join(" and ", conditions.ToArray()) + ")";
return(GetList <PrintRoomFeeHistory>(cmdtext, parameters).ToArray());
}
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);
}
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));
}
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()) + ")");
}
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();
}
else
{
list = GetList <ViewCustomerServiceInDetail>(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 CustomerService[] GetCustomerServiceListByEqualProjectID(List <int> RoomIDList, int ServiceStatus, out int TotalCount, int UserID = 0, List <int> EqualProjectIDList = null, List <int> InProjectIDList = null, bool GetCount = false)
{
TotalCount = 0;
ResetCache();
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 (RoomIDList.Count > 0)
{
cmdlist = new List <string>();
cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false, RoomIDName: "[ProjectID]");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (ServiceStatus > -1)
{
if (ServiceStatus == 100)
{
conditions.Add("[ServiceStatus] in (3,10)");
}
else
{
conditions.Add("[ServiceStatus]=@ServiceStatus");
}
parameters.Add(new SqlParameter("@ServiceStatus", ServiceStatus));
}
if (GetCount)
{
using (SqlHelper helper = new SqlHelper())
{
var result = helper.ExecuteScalar("select count(1) from [CustomerService] where " + string.Join(" and ", conditions.ToArray()), CommandType.Text, parameters);
if (result != null)
{
int.TryParse(result.ToString(), out TotalCount);
}
}
return(new CustomerService[] { });
}
string cmdtext = "select * from [CustomerService] where " + string.Join(" and ", conditions.ToArray());
CustomerService[] list = GetList <CustomerService>(cmdtext, parameters).ToArray();
return(list);
}
public static Ui.DataGrid GetChargeMeter_ProjectDetailGridByKeywords(string Keywords, List <int> RoomIDList, List <int> ProjectIDList, int MeterCategoryID, int MeterType, int MeterChargeID, string orderBy, long startRowIndex, int pageSize, int UserID = 0, bool canexport = false, bool IsWritePoint = false)
{
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
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_ProjectDetail[] list = new ChargeMeter_ProjectDetail[] { };
if (canexport)
{
string cmdtext = "select * from (" + CommSqlText + ") as A where " + string.Join(" and ", conditions.ToArray()) + " order by " + orderBy;
list = GetList <ChargeMeter_ProjectDetail>(cmdtext, parameters).ToArray();
}
else
{
string fieldList = @"A.*";
string Statement = " from (" + CommSqlText + ") as A where " + string.Join(" and ", conditions.ToArray());
list = GetList <ChargeMeter_ProjectDetail>(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 GetViewRoomSourceFeeGridByKeywords(string Keywords, List <int> RoomIDList, List <int> ProjectIDList, int ChargeID, bool IsRoomFee, bool IsContractFee, string RoomProperty, 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("[IsStart]=1");
if (!string.IsNullOrEmpty(RoomProperty))
{
conditions.Add("[RoomID] in (select [RoomID] from [RoomBasic] where isnull([RoomProperty],'')=@RoomProperty)");
parameters.Add(new SqlParameter("@RoomProperty", RoomProperty));
}
if (IsRoomFee && !IsContractFee)
{
conditions.Add("isnull([ContractID],0)=0");
}
if (IsContractFee && !IsRoomFee)
{
conditions.Add("isnull([ContractID],0)>0");
}
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]=[ViewRoomSourceFee].[RoomID]))");
if (!string.IsNullOrEmpty(Keywords))
{
conditions.Add("([Name] like @Keywords or [FullName] like @Keywords or [ChargeName] like @Keywords)");
parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%"));
}
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 (ChargeID > 0)
{
conditions.Add("[ChargeID]=@ChargeID");
parameters.Add(new SqlParameter("@ChargeID", ChargeID));
}
string fieldList = "[ViewRoomSourceFee].*,(select top 1 [RelationName] from [RoomPhoneRelation] where isnull([IsDefault],0)=1 and [RoomID]=[ViewRoomSourceFee].[RoomID]) as [RelationName]";
string Statement = " from [ViewRoomSourceFee] where " + string.Join(" and ", conditions.ToArray());
ViewRoomSourceFee[] list = new ViewRoomSourceFee[] { };
list = GetList <ViewRoomSourceFee>(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 Mall_DoorRemoteUserTime[] GetMall_DoorRemoteUserTimeListByRoomIDList(List <int> RoomIDList)
{
if (RoomIDList.Count == 0)
{
return(new Mall_DoorRemoteUserTime[] { });
}
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, RoomIDName: "[RoomID]");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
return(GetList <Mall_DoorRemoteUserTime>("select * from [Mall_DoorRemoteUserTime] where " + string.Join(" and ", conditions.ToArray()), parameters).ToArray());
}
public static Project_Biao[] GetProject_BiaoListByBiaoID(List <int> RoomIDList, List <int> ProjectIDList, int ChargeID = 0, int BiaoID = 0, string BiaoCategory = "", string BiaoName = "", string BiaoGuiGe = "", 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, IncludeRelation: false, RoomIDName: "[ProjectID]");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, RoomIDName: "[ProjectID]", UserID: UserID);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
Project_Biao[] list = new Project_Biao[] { };
if (ChargeID > 0)
{
parameters.Add(new SqlParameter("@ChargeID", ChargeID));
conditions.Add("[BiaoID] in (select ChargeBiaoID from ChargeSummary_Biao where ChargeID=@ChargeID)");
}
if (BiaoID > 0)
{
parameters.Add(new SqlParameter("@BiaoID", BiaoID));
conditions.Add("[BiaoID]=@BiaoID");
}
if (!string.IsNullOrEmpty(BiaoCategory))
{
parameters.Add(new SqlParameter("@BiaoCategory", BiaoCategory));
conditions.Add("[BiaoCategory]=@BiaoCategory");
}
if (!string.IsNullOrEmpty(BiaoName))
{
parameters.Add(new SqlParameter("@BiaoName", BiaoName));
conditions.Add("[BiaoName]=@BiaoName");
}
if (!string.IsNullOrEmpty(BiaoGuiGe))
{
parameters.Add(new SqlParameter("@BiaoGuiGe", BiaoGuiGe));
conditions.Add("[BiaoGuiGe]=@BiaoGuiGe");
}
list = GetList <Project_Biao>("select * from [Project_Biao] where " + string.Join(" and ", conditions.ToArray()), parameters).ToArray();
return(list);
}
public static Ui.DataGrid GetPaySummaryAnalysisGrid(List <int> ProjectIDList, List <int> RoomIDList, DateTime StartTime, DateTime EndTime, string orderBy, long startRowIndex, int pageSize, int UserID = 0, bool canexport = false)
{
List <string> conditions = new List <string>();
List <SqlParameter> parameters = new List <SqlParameter>();
conditions.Add("1=1");
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 (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));
}
long totalRows = 0;
string fieldList = "B.*";
string Statement = " from (select A.*,Project.FullName,Project.Name,Project.DefaultOrder from (select ProjectID,ProjectName,RoomName,sum(PayMoney) as TotalCost from PayService where [ProjectID]>0 and " + string.Join(" and ", conditions.ToArray()) + " group by ProjectID,ProjectName,RoomName)A left join Project on Project.ID=A.ProjectID )B where B.TotalCost>0";
PaySummaryAnalysis[] list = new PaySummaryAnalysis[] { };
if (canexport)
{
list = GetList <PaySummaryAnalysis>("select " + fieldList + Statement + " " + orderBy, parameters).ToArray();
}
else
{
list = GetList <PaySummaryAnalysis>(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 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[] 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 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 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 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 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 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 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 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 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 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 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 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);
}
