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 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 void GetChargeMeter_ProjectDetailRoomIDList(int RoomID, out int PreRoomID, out int NextRoomID, out int CurrentRoomID, string keywords = "", List <int> ALLProjectIDList = null, int UserID = 0, int ProjectID = 0) { PreRoomID = 0; NextRoomID = 0; CurrentRoomID = 0; var parameters = new List <SqlParameter>(); var conditions = new List <string>(); conditions.Add("[IsAPPWriteEnable]=1"); conditions.Add("[MeterType]=1"); var list = new ChargeMeter_Project[] { }; if (ALLProjectIDList != null && ALLProjectIDList.Count > 0) { List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ALLProjectIDList, IncludeRelation: false, RoomIDName: "[ProjectID]", UserID: UserID); conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")"); } if (!string.IsNullOrEmpty(keywords)) { conditions.Add("([Project].[Name] like @keywords or [Project].[FullName] like @keywords)"); parameters.Add(new SqlParameter("@keywords", "%" + keywords + "%")); } if (ProjectID > 0) { conditions.Add("([Project].[ID]=@ProjectID or [Project].[AllParentID] like @AllParentID)"); parameters.Add(new SqlParameter("@ProjectID", ProjectID)); parameters.Add(new SqlParameter("@AllParentID", "%," + ProjectID + ",%")); } list = GetList <ChargeMeter_Project>("select [ChargeMeter_Project].[ProjectID] from [ChargeMeter_Project] left join [Project] on [Project].ID=[ChargeMeter_Project].ProjectID where " + string.Join(" and ", conditions.ToArray()) + " order by [Project].DefaultOrder asc", parameters).ToArray(); if (list.Length == 0) { return; } var ProjectIDList = list.Select(p => p.ProjectID).Distinct().ToArray(); if (RoomID > 0) { for (int i = 0; i < ProjectIDList.Length; i++) { if (RoomID == ProjectIDList[i]) { CurrentRoomID = RoomID; if (i > 0) { PreRoomID = ProjectIDList[i - 1]; } if (i < ProjectIDList.Length - 1) { NextRoomID = ProjectIDList[i + 1]; } } } return; } CurrentRoomID = ProjectIDList[0]; if (ProjectIDList.Length > 1) { NextRoomID = ProjectIDList[1]; } }
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 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 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 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 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 ChargeMeter_ProjectDetail[] GetChargeMeter_ProjectDetailListByRoomID(int RoomID, List <int> ProjectIDList = null, int MeterType = 1, int UserID = 0, int ProjectID = 0, string keywords = "") { List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("[IsAPPWriteEnable]=1"); if (RoomID > 0) { conditions.Add("[ProjectID]=@RoomID"); parameters.Add(new SqlParameter("@RoomID", RoomID)); } if (ProjectID > 0) { conditions.Add("([ProjectID]=@ProjectID or [AllParentID] like @AllParentID)"); parameters.Add(new SqlParameter("@ProjectID", ProjectID)); parameters.Add(new SqlParameter("@AllParentID", "%," + ProjectID + ",%")); } if (!string.IsNullOrEmpty(keywords)) { conditions.Add("([ProjectName] like @keywords or [FullName] like @keywords)"); parameters.Add(new SqlParameter("@keywords", "%" + keywords + "%")); } if (MeterType == 1) { conditions.Add("[MeterType]=1"); if (ProjectIDList != null && ProjectIDList.Count > 0) { List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, RoomIDName: "[ProjectID]", UserID: UserID); conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")"); } } else { conditions.Add("[MeterType]=2"); } string cmdtext = "select * from (" + CommSqlText + ") as A where " + string.Join(" and ", conditions.ToArray()); ChargeMeter_ProjectDetail[] list = GetList <ChargeMeter_ProjectDetail>(cmdtext, parameters).ToArray(); return(list); }
public static Wechat_Contact[] GetWechat_ContactListByMsgPhoneType(string PhoneType, List <int> ProjectIDList = null, int UserID = 0) { List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); List <string> conditions2 = new List <string>(); conditions.Add("1=1"); conditions2.Add("1=1"); if (ProjectIDList != null && ProjectIDList.Count > 0) { List <int> EqualIDList = new List <int>(); List <int> InIDList = new List <int>(); Project.GetMyProjectListByProjectIDList(ProjectIDList, out EqualIDList, out InIDList); if (EqualIDList.Count > 0) { List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(EqualIDList, IncludeRelation: false, RoomIDName: "[ProjectID]", UserID: UserID); conditions.Add("([ID] in (select [WechatContactID] from [Wechat_ContactProject] where (" + string.Join(" or ", cmdlist.ToArray()) + ")))"); } } if (!string.IsNullOrEmpty(PhoneType)) { conditions.Add("[PhoneType]=@PhoneType"); parameters.Add(new SqlParameter("@PhoneType", PhoneType)); conditions2.Add("[PhoneType]=@PhoneType"); } else { conditions.Add("isnull([PhoneType],'')!=@PhoneType"); parameters.Add(new SqlParameter("@PhoneType", PhoneType)); conditions2.Add("isnull([PhoneType],'')!=@PhoneType"); } string cmdtext = "select * from [Wechat_Contact] where " + string.Join(" and ", conditions.ToArray()) + " order by ID desc"; var list = GetList <Wechat_Contact>(cmdtext, parameters).ToArray(); if (list.Length > 0) { return(list); } return(GetList <Wechat_Contact>("select * from [Wechat_Contact] where " + string.Join(" and ", conditions2.ToArray()) + " order by ID desc", parameters).ToArray()); }
public static RoomFee[] GetRoomFeeListByParams(List <int> ProjectIDList, List <int> RoomIDList, List <int> ChargeIDList, int UserID = 0) { List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("1=1"); if (ChargeIDList.Count > 0) { conditions.Add("[ChargeID] in (" + string.Join(",", ChargeIDList.ToArray()) + ")"); } if (ProjectIDList.Count > 0) { List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, UserID: UserID); conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")"); } if (RoomIDList.Count > 0) { List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false); conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")"); } return(GetList <RoomFee>("select * from [RoomFee] where " + string.Join(" and ", conditions.ToArray()), parameters).ToArray()); }
public static CustomerService[] GetCustomerServiceListByRoomIDList(List <int> RoomIDList, List <int> ProjectIDList, int UserID = 0, int Status = 0) { ResetCache(); List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("1=1"); if (RoomIDList.Count > 0) { List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, RoomIDName: "[ProjectID]"); conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")"); } if (ProjectIDList != null && ProjectIDList.Count > 0) { List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, RoomIDName: "[ProjectID]", IsContractFee: false, UserID: UserID); conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")"); } if (Status == 100) { conditions.Add("[ServiceStatus] in (3,10)"); } return(GetList <CustomerService>("select * from [CustomerService] where " + string.Join(" and ", conditions.ToArray()), parameters).ToArray()); }
public static 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 ViewCustomerService[] GetViewCustomerServiceListByStatus(int status, int UserID, long startRowIndex, int pageSize, out long totalRows, bool onlybaoshi = false, List <int> ProjectIDList = null) { ResetCache(); List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); string orderby = " order by [AddTime] desc"; conditions.Add("1=1"); if (ProjectIDList != null && ProjectIDList.Count > 0) { List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, RoomIDName: "[ProjectID]", UserID: UserID); if (UserID > -1) { cmdlist.Add("[ProjectID]=0"); } conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")"); } if (status >= 0) { conditions.Add("[ServiceStatus]=@ServiceStatus"); parameters.Add(new SqlParameter("@ServiceStatus", status)); if (status == 1) { orderby = " order by [BanJieTime] desc"; } } if (UserID > 0) { conditions.Add("([DepartmentID] in (select DepartmentID from [UserDepartment] where [UserID]=@UserID) or DepartmentID is null or DepartmentID=0)"); if (status != 3) { conditions.Add("REPLACE(REPLACE([ServiceAccpetManID],'[',','),']',',') like '%," + UserID.ToString() + ",%'"); } parameters.Add(new SqlParameter("@UserID", UserID)); } if (onlybaoshi) { string cmdwhere = string.Empty; if (UserID > 0) { cmdwhere += " and [AddUserID]=@AddUserID"; parameters.Add(new SqlParameter("@AddUserID", UserID)); } conditions.Add("ID in (select ID from [CustomerService] where [WechatServiceID] in (select ID from [Wechat_Service] where 1=1 " + cmdwhere + "))"); } string fieldList = "[ViewCustomerService].*"; string Statement = " from [ViewCustomerService] where " + string.Join(" and ", conditions.ToArray()); var list = GetList <ViewCustomerService>(fieldList, Statement, parameters, orderby, startRowIndex, pageSize, out totalRows).ToArray(); if (UserID == -1) { var UserList = User.GetAPPUserList(); var UserIDList = UserList.Select(p => p.UserID).ToList(); list = list.Where(p => { if (string.IsNullOrEmpty(p.ServiceAccpetManID)) { return(true); } string[] ServiceAccpetManIDArray = p.ServiceAccpetManID.Replace("[", ",").Replace("]", ",").Split(','); foreach (var MyUserID in UserIDList) { foreach (var item in ServiceAccpetManIDArray) { if (MyUserID.ToString().Equals(item)) { return(false); } } } return(true); }).ToArray(); } return(list); }
public static 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 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 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 GetMall_RoomOwnerBalanceDetailGridByKeywords(string Keywords, DateTime StartTime, DateTime EndTime, int BalanceStatus, long startRowIndex, int pageSize, List <int> RoomIDList, List <int> ProjectIDList, int UserID = 0) { long totalRows = 0; string OrderBy = " order by AddTime desc"; List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("1=1"); if (RoomIDList.Count > 0) { conditions.Add("[ProjectID] in (" + string.Join(",", RoomIDList.ToArray()) + ")"); } if (ProjectIDList.Count > 0) { List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, RoomIDName: "[ProjectID]", UserID: UserID); conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")"); } if (BalanceStatus == 23) { conditions.Add("[BalanceStatus] in (2,3)"); } else if (BalanceStatus > 0) { conditions.Add("[BalanceStatus]=@BalanceStatus"); parameters.Add(new SqlParameter("@BalanceStatus", BalanceStatus)); } if (StartTime > DateTime.MinValue) { conditions.Add("[AddTime]>=@StartTime"); parameters.Add(new SqlParameter("@StartTime", StartTime)); } if (EndTime > DateTime.MinValue) { conditions.Add("[AddTime]<=@EndTime"); parameters.Add(new SqlParameter("@EndTime", EndTime)); } if (!string.IsNullOrEmpty(Keywords)) { conditions.Add("([RoomOwnerName] like @keywords)"); parameters.Add(new SqlParameter("@keywords", "%" + Keywords + "%")); } string fieldList = "A.*"; string Statement = " from (select [Mall_RoomOwnerBalance].*,[RoomPhoneRelation].RelationName as RoomOwnerName,[Project].FullName,[Project].Name as RoomName from [Mall_RoomOwnerBalance] left join [RoomPhoneRelation] on [RoomPhoneRelation].ID=[Mall_RoomOwnerBalance].RoomPhoneRelationID left join [Project] on [Project].ID=[RoomPhoneRelation].RoomID)A where " + string.Join(" and ", conditions.ToArray()); Mall_RoomOwnerBalanceDetail[] list = GetList <Mall_RoomOwnerBalanceDetail>(fieldList, Statement, parameters, OrderBy, startRowIndex, pageSize, out totalRows).ToArray(); var rule_list = Mall_BalanceRule.GetMall_BalanceRules().ToArray(); foreach (var item in list) { var my_rule = rule_list.FirstOrDefault(p => p.ID == item.BalanceRuleID); if (my_rule != null) { item.BalanceRuleName = my_rule.Title; } } DataAccess.Ui.DataGrid dg = new Ui.DataGrid(); dg.rows = list; dg.total = totalRows; dg.page = pageSize; return(dg); }
public static Ui.DataGrid GetViewContractChargeSummaryGrid(int ContractID, string guid, DateTime StartTime, DateTime EndTime, string orderBy, long startRowIndex, int pageSize, bool IsLinShi = false, int UserID = 0, List <int> RoomIDList = null, List <int> ProjectIDList = null, string keywords = "", string ContractStatus = "", bool IsWarning = false, bool includefooter = false, DateTime?ReadyStartTime = null, DateTime?ReadyEndTime = null, int FeeStatus = 0, List <int> ChargeIDList = null, bool canexport = false) { ReSetParams(); long totalRows = 0; List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("isnull([ChargeID],0)!=0"); if (ChargeIDList != null && ChargeIDList.Count > 0) { conditions.Add("[ChargeID] in (" + string.Join(",", ChargeIDList.ToArray()) + ")"); } if (ReadyStartTime.HasValue) { DateTime _ReadyStartTime = Convert.ToDateTime(ReadyStartTime); if (_ReadyStartTime > DateTime.MinValue) { conditions.Add("([ReadyChargeTime]>=@ReadyStartTime or [ReadyChargeTime] is null)"); parameters.Add(new SqlParameter("@ReadyStartTime", ReadyStartTime)); } } if (ReadyEndTime.HasValue) { DateTime _ReadyEndTime = Convert.ToDateTime(ReadyEndTime); if (_ReadyEndTime > DateTime.MinValue) { conditions.Add("([ReadyChargeTime]<=@ReadyEndTime or [ReadyChargeTime] is null)"); parameters.Add(new SqlParameter("@ReadyEndTime", ReadyEndTime)); } } if (ProjectIDList != null && ProjectIDList.Count > 0) { List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, RoomIDName: "[RoomID]", IsContractFee: true, UserID: UserID); conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")"); } if (RoomIDList != null && RoomIDList.Count > 0) { List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false, RoomIDName: "[RoomID]", IsContractFee: true); conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")"); } if (!string.IsNullOrEmpty(keywords)) { conditions.Add("(ContractID in (select ID from Contract where [RentName] like @keywords or [ContractNo] like @keywords or [ContractName] like @keywords) or [Name] like @keywords)"); parameters.Add(new SqlParameter("@keywords", "%" + keywords + "%")); } if (!string.IsNullOrEmpty(ContractStatus)) { conditions.Add("ContractID in (select ID from Contract where [ContractStatus]=@ContractStatus)"); parameters.Add(new SqlParameter("@ContractStatus", ContractStatus)); } if (IsWarning) { conditions.Add("([ReadyChargeTime]<=@ReadyChargeTime or [ReadyChargeTime] is null)"); parameters.Add(new SqlParameter("@ReadyChargeTime", DateTime.Now)); } if (IsLinShi) { conditions.Add("[ChargeID] in (select ID from ChargeSummary where [FeeType]=4)"); } if (ContractID > 0) { conditions.Add("[ContractID]=@ContractID"); parameters.Add(new SqlParameter("@ContractID", ContractID)); } else if (!string.IsNullOrEmpty(guid)) { conditions.Add("[GUID]=@GUID"); parameters.Add(new SqlParameter("@GUID", guid)); } string fieldList = "[ViewContractChargeSummary].*"; string Statement = " from [ViewContractChargeSummary] where " + string.Join(" and ", conditions.ToArray()); ViewContractChargeSummary[] list = new ViewContractChargeSummary[] { }; if (FeeStatus == 0) { if (canexport) { list = GetList <ViewContractChargeSummary>("select " + fieldList + Statement + " " + orderBy, parameters).ToArray(); } else { list = GetList <ViewContractChargeSummary>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray(); } if (list.Length > 0) { var RoomFeeIDList = list.Where(p => p.RoomFeeID > 0).Select(p => p.RoomFeeID).ToList(); var ContractChargeIDList = list.Select(p => p.ID).ToList(); ViewRoomFeeHistoryDetailList = ViewRoomFeeHistoryDetail.GetViewRoomFeeHistoryDetailList(new List <int>(), StartTime, EndTime, StartTime, EndTime, new List <int>(), RoomFeeIDList, ContractChargeIDList, UserID: UserID, RequireOrderBy: false).Where(p => p.MonthTotalCost > 0).ToArray(); } DataAccess.Ui.DataGrid dg = new Ui.DataGrid(); dg.rows = list; dg.total = totalRows; dg.page = pageSize; return(dg); } else { list = GetList <ViewContractChargeSummary>("select * from [ViewContractChargeSummary] where " + string.Join(" and ", conditions.ToArray()), parameters).ToArray(); if (list.Length > 0) { var RoomFeeIDList = list.Where(p => p.RoomFeeID > 0).Select(p => p.RoomFeeID).ToList(); var ContractChargeIDList = list.Select(p => p.ID).ToList(); ViewRoomFeeHistoryDetailList = ViewRoomFeeHistoryDetail.GetViewRoomFeeHistoryDetailList(new List <int>(), StartTime, EndTime, StartTime, EndTime, new List <int>(), RoomFeeIDList, ContractChargeIDList, UserID: UserID, RequireOrderBy: false).Where(p => p.MonthTotalCost > 0).ToArray(); } if (FeeStatus == 1) { list = list.Where(p => p.CalcualtePayCost == 0 || p.CalcualteRestCost > 0).ToArray(); } else if (FeeStatus == 2) { list = list.Where(p => p.CalcualtePayCost > 0 && p.CalcualteRestCost == 0).ToArray(); } totalRows = list.Length; int StartRowIndex = Convert.ToInt32(startRowIndex); var finallist = list.Skip(StartRowIndex).Take(pageSize).ToArray(); DataAccess.Ui.DataGrid dg = new Ui.DataGrid(); dg.rows = finallist; dg.total = totalRows; dg.page = pageSize; return(dg); } }
public static Ui.DataGrid GetHistorySummaryGroupByTypeName(List <int> RoomIDList, List <int> ProjectIDList, DateTime StartChargeTime, DateTime EndChargeTime, string ChargeMan, List <int> ChargeSummaryIDList, List <int> ChargeTypeIDList, List <int> ChargeStateList, int RoomFeeOrderID, bool IsRoomFeeSearch, string OpenID, List <int> HistoryIDList = null, bool DeleteTempHistoryIDList = true, int UserID = 0) { List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("1=1"); List <string> conditions2 = new List <string>(); conditions2.Add("1=1"); List <string> conditions3 = new List <string>(); conditions3.Add("1=1"); if (HistoryIDList != null && HistoryIDList.Count > 0) { if (HistoryIDList.Count > 2) { ViewRoomFeeHistory.CreateTempTable(HistoryIDList, DeleteTempHistoryIDList, UserID: UserID); conditions.Add("EXISTS (SELECT 1 FROM [TempIDs] WHERE id=rfh.HistoryID and UserID=" + UserID + ")"); conditions2.Add("EXISTS (SELECT 1 FROM [TempIDs] WHERE id in (select HistoryID from RoomFeeHistory where PrintID=prh.ID) and UserID=" + UserID + ")"); } else if (HistoryIDList.Count > 0) { conditions.Add("rfh.HistoryID in (" + string.Join(",", HistoryIDList.ToArray()) + ")"); conditions2.Add("prh.ID in (select PrintID from [RoomFeeHistory] where HistoryID in (" + string.Join(",", HistoryIDList.ToArray()) + "))"); } } if (!string.IsNullOrEmpty(OpenID)) { conditions.Add("rfh.[RoomID] in (select [ProjectID] from [WechatUser_Project] where [OpenID]=@OpenID)"); parameters.Add(new SqlParameter("@OpenID", OpenID)); } if (ChargeStateList.Count > 0) { if (ChargeStateList.Contains(1) || ChargeStateList.Contains(4)) { conditions2.Add("prh.[IsCancel]=0"); } conditions2.Add("prh.ID in (select PrintID from RoomFeeHistory where ChargeState in (" + string.Join(",", ChargeStateList.ToArray()) + "))"); } if (RoomFeeOrderID > 0) { conditions2.Add("isnull(prh.[RoomFeeOrderID],0)=@RoomFeeOrderID"); parameters.Add(new SqlParameter("@RoomFeeOrderID", RoomFeeOrderID)); } else if (IsRoomFeeSearch) { conditions2.Add("isnull(prh.[RoomFeeOrderID],0)=0"); conditions2.Add("prh.[ID] in (select [PrintID] from [RoomFeeHistory] where [ChargeID] in (select [ID] from [ChargeSummary] where isnull([IsOrderFeeOn],0)=1) or isnull(ChargeID,0)=0)"); if (StartChargeTime > DateTime.MinValue) { conditions.Add("rfh.[ChargeTime]>=@StartChargeTime"); parameters.Add(new SqlParameter("@StartChargeTime", StartChargeTime)); } if (EndChargeTime > DateTime.MinValue) { conditions.Add("rfh.[ChargeTime]<=@EndChargeTime"); parameters.Add(new SqlParameter("@EndChargeTime", EndChargeTime)); } } else { if (StartChargeTime > DateTime.MinValue) { conditions.Add("Convert(nvarchar(10),rfh.[ChargeTime],120)>=@StartChargeTime"); parameters.Add(new SqlParameter("@StartChargeTime", StartChargeTime)); } if (EndChargeTime > DateTime.MinValue) { conditions.Add("Convert(nvarchar(10),rfh.[ChargeTime],120)<=@EndChargeTime"); parameters.Add(new SqlParameter("@EndChargeTime", EndChargeTime)); } } if (RoomIDList.Count > 0) { List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, RoomIDName: "rfh.[RoomID]"); conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")"); } if (ProjectIDList.Count > 0) { List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, RoomIDName: "rfh.[RoomID]", UserID: UserID); conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")"); } #region 收款人查询 string cmd = string.Empty; if (!string.IsNullOrEmpty(ChargeMan)) { string[] keywords = ChargeMan.Trim().Split(','); for (int i = 0; i < keywords.Length; i++) { if (string.IsNullOrEmpty(keywords[i].ToString())) { continue; } if (i + 1 == keywords.Length) { if (keywords.Length == 1) { cmd += " and (prh.[ChargeMan] like '%" + keywords[i] + "%')"; } else { cmd += " (prh.[ChargeMan] like '%" + keywords[i] + "%'))"; } } else if (i == 0) { cmd += " and ((prh.[ChargeMan] like '%" + keywords[i] + "%') or"; } else { cmd += " (prh.[ChargeMan] like '%" + keywords[i] + "%') or "; } } } #endregion //if (!string.IsNullOrEmpty(ChargeMan)) //{ // conditions2.Add("prh.[ChargeMan] like '%" + ChargeMan + "%'"); //} if (ChargeSummaryIDList.Count > 0) { conditions.Add("rfh.[ChargeID] in (" + string.Join(",", ChargeSummaryIDList.ToArray()) + ")"); } if (ChargeTypeIDList.Count > 0) { conditions2.Add("(prh.[ChageType1] in (" + string.Join(",", ChargeTypeIDList.ToArray()) + ") or prh.[ChageType2] in (" + string.Join(",", ChargeTypeIDList.ToArray()) + "))"); conditions3.Add("cmt.[ChargeTypeID] in (" + string.Join(",", ChargeTypeIDList.ToArray()) + ")"); } var type_list = GetList <ChargeMoneyTypeDetails>(@"select * from ChargeMoneyType", parameters).ToList(); var history1 = GetList <ChargeMoneyTypeDetails>(@"select sum(isnull(RealCost,0)-isnull(prh.RealMoneyCost2,0)) as RealCost,prh.ChageType1 as ChargeTypeID from PrintRoomFeeHistory prh where " + string.Join(" and ", conditions2.ToArray()) + cmd + @" and prh.[ID] in (select [PrintID] from RoomFeeHistory rfh where " + string.Join(" and ", conditions.ToArray()) + @") Group by prh.ChageType1", parameters).ToArray(); var history2 = GetList <ChargeMoneyTypeDetails>(@"select sum(isnull(prh.RealMoneyCost2,0)) as RealCost,prh.ChageType2 as ChargeTypeID from PrintRoomFeeHistory prh where " + string.Join(" and ", conditions2.ToArray()) + cmd + @" and prh.[ID] in (select [PrintID] from RoomFeeHistory rfh where " + string.Join(" and ", conditions.ToArray()) + @") Group by prh.ChageType2", parameters).ToArray(); decimal RealCost1 = 0; decimal RealCost2 = 0; var finalList = new List <ChargeMoneyTypeDetails>(); foreach (var item in type_list) { RealCost1 = history1.Where(p => p.ChargeTypeID == item.ChargeTypeID).Sum(p => p.RealCost); RealCost2 = history2.Where(p => p.ChargeTypeID == item.ChargeTypeID).Sum(p => p.RealCost); if ((RealCost1 + RealCost2) > 0) { item.RealCost = RealCost1 + RealCost2; finalList.Add(item); } } var TypeIDList = type_list.Select(p => p.ChargeTypeID).ToList(); RealCost1 = history1.Where(p => !TypeIDList.Contains(p.ChargeTypeID)).Sum(p => p.RealCost); RealCost2 = history2.Where(p => !TypeIDList.Contains(p.ChargeTypeID)).Sum(p => p.RealCost); if ((RealCost1 + RealCost2) > 0) { var item = new ChargeMoneyTypeDetails(); item.ChargeTypeID = 0; item.ChargeTypeName = "其他"; item.RealCost = (RealCost1 + RealCost2); finalList.Add(item); } DataAccess.Ui.DataGrid dg = new Ui.DataGrid(); dg.rows = finalList.ToArray(); dg.total = finalList.Count; dg.page = 1; return(dg); }
public static Ui.DataGrid GetDepositSummaryGroupByTypeName(List <int> RoomIDList, List <int> ProjectIDList, DateTime StartChargeTime, DateTime EndChargeTime, int CompanyID, string ChargeMan, int RoomFeeOrderID, bool IsRoomFeeSearch, List <int> HistoryIDList = null, bool DeleteTempHistoryIDList = true, int UserID = 0) { List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); string cmdNow = string.Empty; string cmdWhere = string.Empty; if (HistoryIDList != null && HistoryIDList.Count > 0) { if (HistoryIDList.Count > 2) { ViewRoomFeeHistory.CreateTempTable(HistoryIDList, DeleteTempHistoryIDList, UserID: UserID); cmdNow += " and EXISTS (SELECT 1 FROM [TempIDs] WHERE id=rfh.HistoryID and UserID=" + UserID + ")"; } else if (HistoryIDList.Count > 0) { cmdNow += " and rfh.HistoryID in (" + string.Join(",", HistoryIDList.ToArray()) + ")"; } } if (RoomFeeOrderID > 0) { cmdNow += " and [PrintID] in (select [ID] from [PrintRoomFeeHistory] where isnull([RoomFeeOrderID],0)=@RoomFeeOrderID)"; parameters.Add(new SqlParameter("@RoomFeeOrderID", RoomFeeOrderID)); } else if (IsRoomFeeSearch) { cmdNow += " and [PrintID] in (select [ID] from [PrintRoomFeeHistory] where isnull([RoomFeeOrderID],0)=0)"; cmdNow += " and ([ChargeID] in (select [ID] from [ChargeSummary] where isnull([IsOrderFeeOn],0)=1) or isnull(ChargeID,0)=0)"; if (StartChargeTime > DateTime.MinValue) { cmdNow += " and [ChargeTime]>=@StartChargeTime"; parameters.Add(new SqlParameter("@StartChargeTime", StartChargeTime)); } if (EndChargeTime > DateTime.MinValue) { cmdNow += " and [ChargeTime]<=@EndChargeTime"; parameters.Add(new SqlParameter("@EndChargeTime", EndChargeTime)); } } else { if (StartChargeTime > DateTime.MinValue) { cmdNow += " and Convert(nvarchar(10),[ChargeTime],120)>=@StartChargeTime"; parameters.Add(new SqlParameter("@StartChargeTime", StartChargeTime)); } if (EndChargeTime > DateTime.MinValue) { cmdNow += " and Convert(nvarchar(10),[ChargeTime],120)<=@EndChargeTime"; parameters.Add(new SqlParameter("@EndChargeTime", EndChargeTime)); } } #region 收款人查询 string cmd = string.Empty; if (!string.IsNullOrEmpty(ChargeMan)) { string[] keywords = ChargeMan.Trim().Split(','); for (int i = 0; i < keywords.Length; i++) { if (string.IsNullOrEmpty(keywords[i].ToString())) { continue; } if (i + 1 == keywords.Length) { if (keywords.Length == 1) { cmd += " and ([ChargeMan] like '%" + keywords[i] + "%')"; } else { cmd += " ([ChargeMan] like '%" + keywords[i] + "%'))"; } } else if (i == 0) { cmd += " and (([ChargeMan] like '%" + keywords[i] + "%') or"; } else { cmd += " ([ChargeMan] like '%" + keywords[i] + "%') or "; } } } cmdNow += cmd; #endregion //if (!string.IsNullOrEmpty(ChargeMan)) //{ // cmdNow += " and [ChargeMan]=@ChargeMan"; // parameters.Add(new SqlParameter("@ChargeMan", ChargeMan)); //} if (CompanyID != 1 && CompanyID > 0) { cmdWhere += " and [CompanyID]=@CompanyID"; parameters.Add(new SqlParameter("@CompanyID", CompanyID)); } if (RoomIDList.Count > 0) { List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IsContractFee: true); cmdNow += " and (" + string.Join(" or ", cmdlist.ToArray()) + ")"; } if (ProjectIDList.Count > 0) { List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IsContractFee: true, UserID: UserID); cmdNow += " and (" + string.Join(" or ", cmdlist.ToArray()) + ")"; } var type_list = GetList <ChargeMoneyTypeDetails>(@"select * from ChargeMoneyType", parameters).ToList(); var history1 = GetList <ChargeMoneyTypeDetails>(@"select sum(isnull(RealMoneyCost2,0)) as RealCost,ChageType2 as ChargeTypeID from PrintRoomFeeHistory where [ID] in (select [PrintID] from RoomFeeHistory rfh where ChargeState in(3,6,7) " + cmdNow + @") group by ChageType2", parameters).ToArray(); var history2 = GetList <ChargeMoneyTypeDetails>(@"select sum(isnull(RealCost,0)-isnull(RealMoneyCost2,0)) as RealCost,ChageType1 as ChargeTypeID from PrintRoomFeeHistory where [ID] in (select [PrintID] from RoomFeeHistory rfh where ChargeState in(3,6,7) " + cmdNow + @") group by ChageType1", parameters).ToArray(); decimal RealCost1 = 0; decimal RealCost2 = 0; var finalList = new List <ChargeMoneyTypeDetails>(); foreach (var item in type_list) { RealCost1 = history1.Where(p => p.ChargeTypeID == item.ChargeTypeID).Sum(p => p.RealCost); RealCost2 = history2.Where(p => p.ChargeTypeID == item.ChargeTypeID).Sum(p => p.RealCost); if ((RealCost1 + RealCost2) > 0) { item.RealCost = RealCost1 + RealCost2; finalList.Add(item); } } var ChargeTypeIDList = type_list.Select(p => p.ChargeTypeID).ToList(); RealCost1 = history1.Where(p => !ChargeTypeIDList.Contains(p.ChargeTypeID)).Sum(p => p.RealCost); RealCost2 = history2.Where(p => !ChargeTypeIDList.Contains(p.ChargeTypeID)).Sum(p => p.RealCost); if ((RealCost1 + RealCost2) > 0) { var item = new ChargeMoneyTypeDetails(); item.ChargeTypeID = 0; item.ChargeTypeName = "其他"; item.RealCost = (RealCost1 + RealCost2); finalList.Add(item); } DataAccess.Ui.DataGrid dg = new Ui.DataGrid(); dg.rows = finalList.ToArray(); dg.total = finalList.Count; dg.page = 1; return(dg); }
public static Ui.DataGrid GetCustomerServiceGridByKeywords(string Keywords, List <int> RoomIDList, DateTime StartTime, DateTime EndTime, int ServiceStatus, int PayStatus, int ServiceAccpetManID, string orderBy, long startRowIndex, int pageSize, int UserID, List <int> EqualProjectIDList = null, List <int> InProjectIDList = null, int ServiceType = 0, int ServiceRange = 1, List <int> PublicProjectIDList = null, bool canexport = false) { ResetCache(); long totalRows = 0; List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); List <string> cmdlist = new List <string>(); conditions.Add("1=1"); #region 关键字查询 string cmd = string.Empty; if (!string.IsNullOrEmpty(Keywords)) { string[] keywords = Keywords.Trim().Split(' '); for (int i = 0; i < keywords.Length; i++) { if (string.IsNullOrEmpty(keywords[i].ToString())) { continue; } if (i + 1 == keywords.Length) { if (keywords.Length == 1) { cmd += " and (isnull([ServiceNumber],'') like '%" + keywords[i] + "%' or [AddCustomerName] like '%" + keywords[i] + "%' or [AddCallPhone] like '%" + keywords[i] + "%' or [ServiceAccpetMan] like '%" + keywords[i] + "%' or [ServiceFullName] like '%" + keywords[i] + "%' or [ServiceContent] like '%" + keywords[i] + "%')"; } else { cmd += " (isnull([ServiceNumber],'') like '%" + keywords[i] + "%' or [AddCustomerName] like '%" + keywords[i] + "%' or [AddCallPhone] like '%" + keywords[i] + "%' or [ServiceAccpetMan] like '%" + keywords[i] + "%' or [ServiceFullName] like '%" + keywords[i] + "%' or [ServiceContent] like '%" + keywords[i] + "%'))"; } } else if (i == 0) { cmd += " and ((isnull([ServiceNumber],'') like '%" + keywords[i] + "%' or [AddCustomerName] like '%" + keywords[i] + "%' or [AddCallPhone] like '%" + keywords[i] + "%' or [ServiceAccpetMan] like '%" + keywords[i] + "%' or [ServiceFullName] like '%" + keywords[i] + "%' or [ServiceContent] like '%" + keywords[i] + "%') or"; } else { cmd += " (isnull([ServiceNumber],'') like '%" + keywords[i] + "%' or [AddCustomerName] like '%" + keywords[i] + "%' or [AddCallPhone] like '%" + keywords[i] + "%' or [ServiceAccpetMan] like '%" + keywords[i] + "%' or [ServiceFullName] like '%" + keywords[i] + "%' or [ServiceContent] like '%" + keywords[i] + "%') or "; } } } #endregion if (ServiceType == 2) { conditions.Add("isnull([IsSuggestion],0)=1"); } else { conditions.Add("isnull([IsSuggestion],0)=0"); cmdlist = new List <string>(); if (ServiceRange == 1) { conditions.Add("isnull([PublicProjectID],0)=0"); } if (ServiceRange == 2) { conditions.Add("isnull([PublicProjectID],0)>0"); conditions.Add("isnull([ProjectID],0)=0"); } if (InProjectIDList != null && InProjectIDList.Count > 0) { cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(InProjectIDList, IncludeRelation: false, RoomIDName: "[ProjectID]", UserID: UserID); } if (EqualProjectIDList != null && EqualProjectIDList.Count > 0) { cmdlist.Add("([ProjectID] in (" + string.Join(",", EqualProjectIDList.ToArray()) + "))"); } if (PublicProjectIDList != null && PublicProjectIDList.Count > 0) { cmdlist.Add("[PublicProjectID] in (" + string.Join(",", PublicProjectIDList.ToArray()) + ")"); } if (cmdlist.Count > 0) { conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")"); } if (ServiceAccpetManID > 0) { conditions.Add("REPLACE(REPLACE([ServiceAccpetManID],'[',','),']',',') like '%," + ServiceAccpetManID.ToString() + ",%'"); } if (PayStatus == 0) { conditions.Add("isnull([TotalFee],0)=0"); } if (PayStatus == 1) { conditions.Add("isnull([TotalFee],0)>0"); } if (RoomIDList.Count > 0) { cmdlist = new List <string>(); cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false, RoomIDName: "[ProjectID]"); conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")"); } if (StartTime > DateTime.MinValue) { conditions.Add("[StartTime]>=@StartTime"); parameters.Add(new SqlParameter("@StartTime", StartTime)); } if (EndTime > DateTime.MinValue) { conditions.Add("[StartTime]<=@EndTime"); parameters.Add(new SqlParameter("@EndTime", EndTime)); } if (ServiceStatus > -1) { if (ServiceStatus == 100) { conditions.Add("[ServiceStatus] in (3,10)"); } else { conditions.Add("[ServiceStatus]=@ServiceStatus"); } parameters.Add(new SqlParameter("@ServiceStatus", ServiceStatus)); } } string fieldList = "[ViewCustomerService].*"; string Statement = " from [ViewCustomerService] where " + string.Join(" and ", conditions.ToArray()) + cmd; ViewCustomerService[] list = new ViewCustomerService[] { }; if (canexport) { list = GetList <ViewCustomerService>("select " + fieldList + Statement + " " + orderBy, parameters).ToArray(); totalRows = list.Length; } else { list = GetList <ViewCustomerService>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray(); } string footer_text = "select '合计' as [ServiceFullName],100 as [ServiceStatus],sum(CASE WHEN ISNUMERIC(HandelFee)=1 THEN convert(decimal(18,2),HandelFee) ELSE 0 END) as HandelFee,sum(isnull(TotalFee,0)) as TotalFee from [ViewCustomerService] where " + string.Join(" and ", conditions.ToArray()) + cmd; DataAccess.Ui.DataGrid dg = new Ui.DataGrid(); dg.rows = list; dg.total = totalRows; dg.page = pageSize; dg.footer = GetList <ViewCustomerService>(footer_text, parameters).ToArray(); return(dg); }
public static Ui.DataGrid GetViewTaiZhangGridByKeywords(string Keywords, List <int> RoomIDList, List <int> ProjectIDList, int BiaoID, int IsActive, int ID, int RelationStatus, string orderBy, long startRowIndex, int pageSize, int UserID = 0, bool canexport = false) { long totalRows = 0; List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("isnull(FullName,'')!=''"); string cmdcondition = ",0 as RelationCount"; if (ID > 0) { conditions.Add("[ID]!=@ProjectBiaoID"); conditions.Add("[BiaoGuiGe]!='总表'"); if (RelationStatus == 1) { conditions.Add("[ID] in (select [ReationID] from [ProjectBiao_Relation] where [ProjectBiaoID]=@ProjectBiaoID)"); } else if (RelationStatus == 0) { conditions.Add("[ID] not in (select [ReationID] from [ProjectBiao_Relation] where [ProjectBiaoID]=@ProjectBiaoID)"); } cmdcondition = ",(select count(1) from [ProjectBiao_Relation] where [ProjectBiaoID]=@ProjectBiaoID and [ReationID]=[ViewTaiZhang].ID) as RelationCount"; parameters.Add(new SqlParameter("@ProjectBiaoID", ID)); } if (ProjectIDList.Count > 0) { List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, RoomIDName: "[ProjectID]", UserID: UserID); conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")"); } if (RoomIDList.Count > 0) { List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false, RoomIDName: "[ProjectID]"); conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")"); } if (!string.IsNullOrEmpty(Keywords)) { conditions.Add("([FullName] like @Keywords or [Name] like @Keywords or [BiaoName] like @Keywords)"); parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%")); } if (BiaoID > 0) { conditions.Add("[BiaoID]=@BiaoID"); parameters.Add(new SqlParameter("@BiaoID", BiaoID)); } if (IsActive > int.MinValue) { conditions.Add("[IsActive]=@IsActive"); parameters.Add(new SqlParameter("@IsActive", IsActive)); } string fieldList = @"[ViewTaiZhang].*, (select sum((isnull(ViewTaiZhang_2.[EndPoint],0)-isnull(ViewTaiZhang_2.StartPoint,0))* (case when ViewTaiZhang_2.UnitPrice>0 then ViewTaiZhang_2.UnitPrice else ViewTaiZhang_2.SummaryUnitPrice end)-ViewTaiZhang_2.Rate) from ViewTaiZhang as ViewTaiZhang_2 where ViewTaiZhang_2.ID in (select ReationID from ProjectBiao_Relation where ProjectBiaoID=ViewTaiZhang.ID)) as CalculateReducePoint " + cmdcondition; string Statement = " from [ViewTaiZhang] where " + string.Join(" and ", conditions.ToArray()); ViewTaiZhang[] list = new ViewTaiZhang[] { }; if (canexport) { list = GetList <ViewTaiZhang>("select " + fieldList + Statement + " " + orderBy, parameters).ToArray(); totalRows = list.Length; } else { list = GetList <ViewTaiZhang>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray(); } DataAccess.Ui.DataGrid dg = new Ui.DataGrid(); dg.rows = list; dg.total = totalRows; dg.page = pageSize; return(dg); }