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); }