public static Ui.DataGrid GetContract_CustomerGrid(int ContractID, string orderBy, long startRowIndex, int pageSize) { long totalRows = 0; List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("[ContractID]=@ContractID"); parameters.Add(new SqlParameter("@ContractID", ContractID)); string fieldList = "[Contract_Customer].*"; string Statement = " from [Contract_Customer] where " + string.Join(" and ", conditions.ToArray()); var list = GetList <Contract_Customer>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray(); if (list.Length > 0) { var chargeList = ChargeSummary.GetChargeSummaries().ToArray(); list = list.Select(p => { var myChargeList = chargeList.Where(q => p.ChargeIDList.Contains(q.ID)).ToArray(); if (myChargeList.Length > 0) { p.ChargeName = string.Join(",", myChargeList.Select(q => q.Name).ToArray()); } return(p); }).ToArray(); } DataAccess.Ui.DataGrid dg = new Ui.DataGrid(); dg.rows = list; dg.total = totalRows; dg.page = pageSize; return(dg); }
public static Ui.DataGrid GetRoomPhoneRelationGridByRoomID(int RoomID, string RelationType, string orderBy, long startRowIndex, int pageSize) { long totalRows = 0; List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("1=1"); if (RoomID > 0) { conditions.Add("[RoomID]=@RoomID"); parameters.Add(new SqlParameter("@RoomID", RoomID)); } if (!string.IsNullOrEmpty(RelationType)) { conditions.Add("([RelationType]=@RelationType or [RelationType] is null)"); parameters.Add(new SqlParameter("@RelationType", RelationType)); } string fieldList = "[RoomPhoneRelation].* "; string Statement = " from [RoomPhoneRelation] where " + string.Join(" and ", conditions.ToArray()); RoomPhoneRelation[] list = new RoomPhoneRelation[] { }; list = GetList <RoomPhoneRelation>(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 GetWechat_LotteryActivityPrizeGridByKeywords(string Keywords, int ActivityID, string orderBy, long startRowIndex, int pageSize) { long totalRows = 0; List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("1=1"); if (!string.IsNullOrEmpty(Keywords)) { conditions.Add("([LevelName] like @Keywords or [PrizeName] like @Keywords)"); parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%")); } if (ActivityID > 0) { conditions.Add("[ActivityID]=@ActivityID"); parameters.Add(new SqlParameter("@ActivityID", ActivityID)); } string fieldList = "[Wechat_LotteryActivityPrize].* "; string Statement = " from [Wechat_LotteryActivityPrize] where " + string.Join(" and ", conditions.ToArray()); Wechat_LotteryActivityPrize[] list = GetList <Wechat_LotteryActivityPrize>(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 GetSiteVersionGrid(string Keywords, string orderBy, long startRowIndex, int pageSize, string VersionType, bool OnlyAPP = false) { long totalRows = 0; List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("1=1"); if (OnlyAPP) { conditions.Add("([VersionType] = 'android' or [VersionType] = 'ios')"); } if (!string.IsNullOrEmpty(Keywords)) { conditions.Add("[VersionDesc] like @Keywords"); parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%")); } if (!string.IsNullOrEmpty(VersionType)) { conditions.Add("[VersionType] = @VersionType"); parameters.Add(new SqlParameter("@VersionType", VersionType)); } string fieldList = "[SiteVersion].* "; string Statement = " from [SiteVersion] where " + string.Join(" and ", conditions.ToArray()); SiteVersion[] list = GetList <SiteVersion>(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 GetGuaranteeRoomFeeGridByRoomID(List <int> RoomID, int CategoryID, string orderBy, long startRowIndex, int pageSize) { long totalRows = 0; List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("[ChargeState] in(1,4)"); conditions.Add("[RealCost]>isnull([BackCost],0)"); if (RoomID.Count > 0) { conditions.Add("[RoomID] in (" + string.Join(",", RoomID.ToArray()) + ")"); } if (CategoryID > 0) { conditions.Add("[CategoryID]=@CategoryID"); parameters.Add(new SqlParameter("@CategoryID", CategoryID)); } conditions.Add("isnull([BackCost],0)<[RealCost]"); string fieldList = "A.*"; string Statement = " from (select *,(select sum(RealCost) from [RoomFeeHistory] where ChargeID=[ViewGuaranteeHistoryFee].ChargeID and ChargeState=3 and [ParentHistoryID]=[ViewGuaranteeHistoryFee].HistoryID) as BackCost from [ViewGuaranteeHistoryFee])A where " + string.Join(" and ", conditions.ToArray()); ViewGuaranteeHistoryFee[] list = new ViewGuaranteeHistoryFee[] { }; list = GetList <ViewGuaranteeHistoryFee>(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 GetWechat_SurveyQuestionGridByKeywords(string Keywords, List <int> SurveyIDList, string orderBy, long startRowIndex, int pageSize) { long totalRows = 0; List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("1=1"); if (!string.IsNullOrEmpty(Keywords)) { conditions.Add("([QuestionContent] like @Keywords)"); parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%")); } if (SurveyIDList.Count > 0) { conditions.Add("[SurveyID] in (" + string.Join(",", SurveyIDList.ToArray()) + ")"); } string fieldList = "[Wechat_SurveyQuestion].* "; string Statement = " from [Wechat_SurveyQuestion] where " + string.Join(" and ", conditions.ToArray()); Wechat_SurveyQuestion[] list = GetList <Wechat_SurveyQuestion>(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_UserLevelApproveDetailGridByKeywords(string keywords, int status, long startRowIndex, int pageSize) { long totalRows = 0; string OrderBy = " order by [RequestTime] desc"; List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("1=1"); if (status >= 0) { conditions.Add("[ApproveStatus]=@ApproveStatus"); parameters.Add(new SqlParameter("@ApproveStatus", status)); } if (!string.IsNullOrEmpty(keywords)) { conditions.Add("([NickName] like @keywords or [PhoneNumber] like @keywords or [LevelName] like @keywords)"); parameters.Add(new SqlParameter("@keywords", "%" + keywords + "%")); } string fieldList = "A.*"; string Statement = " from (select [Mall_UserLevelApprove].*,[User].NickName,[User].PhoneNumber,[Mall_UserLevel].Name as LevelName,[Mall_UserBalance].[PaymentMethod] as UserBalancePaymentMethod,[Mall_UserBalance].[BalanceValue] as UserBalanceValue,[RoomPhoneRelation].RelatePhoneNumber,[RoomPhoneRelation].RelationName from [Mall_UserLevelApprove] left join [User] on [User].UserID=[Mall_UserLevelApprove].UserID left join [Mall_UserLevel] on [Mall_UserLevel].ID=[Mall_UserLevelApprove].[ApproveUserLevelID] left join [Mall_UserBalance] on [Mall_UserBalance].ID=[Mall_UserLevelApprove].[UserBalanceID] left join [RoomPhoneRelation] on [RoomPhoneRelation].ID=[Mall_UserLevelApprove].[RoomPhoneRelationID])A where " + string.Join(" and ", conditions.ToArray()); Mall_UserLevelApproveDetail[] list = GetList <Mall_UserLevelApproveDetail>(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 GeWechat_ServiceGridByKeywords(string Keywords, string ServiceType, string orderBy, long startRowIndex, int pageSize) { long totalRows = 0; List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("1=1"); if (!string.IsNullOrEmpty(Keywords)) { conditions.Add("([ServiceContent] like @Keywords or [PhoneNumber] like @Keywords or [OpenID] like @Keywords)"); parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%")); } if (!string.IsNullOrEmpty(ServiceType)) { conditions.Add("[ServiceType]=@ServiceType"); parameters.Add(new SqlParameter("@ServiceType", ServiceType)); } string fieldList = "[Wechat_Service].*,(select [FullName] from [Project] where [Project].ID=[Wechat_Service].RoomID) as RoomFullName,(select [Name] from [Project] where [Project].ID=[Wechat_Service].RoomID) as RoomName,(select [NickName] from [Wechat_User] where [Wechat_Service].OpenID=[Wechat_User].OpenID) as NickName"; string Statement = " from [Wechat_Service] where " + string.Join(" and ", conditions.ToArray()); Wechat_ServiceDetail[] list = GetList <Wechat_ServiceDetail>(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 GeUserGridByKeywords(string Keywords, string orderBy, long startRowIndex, int pageSize) { long totalRows = 0; List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("1=1"); conditions.Add("([Type]=@UserType1 or [Type]=@UserType2)"); parameters.Add(new SqlParameter("@UserType1", UserTypeDefine.SystemUser.ToString())); parameters.Add(new SqlParameter("@UserType2", UserTypeDefine.APPUser.ToString())); if (!string.IsNullOrEmpty(Keywords)) { conditions.Add("([LoginName] like @Keywords or [PhoneNumber] like @Keywords or [RealName] like @Keywords)"); parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%")); } string fieldList = "[User].* "; string Statement = " from [User] where " + string.Join(" and ", conditions.ToArray()); User[] list = new User[] { }; list = GetList <User>(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_BusinessGridByKeywords(string Keywords, string orderBy, long startRowIndex, int pageSize, int Status) { ResetParams(); long totalRows = 0; List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("1=1"); if (Status > 0) { conditions.Add("[Status]=@Status"); parameters.Add(new SqlParameter("@Status", Status)); } if (!string.IsNullOrEmpty(Keywords)) { conditions.Add("([BusinessName] like @Keywords or [BusinessAddress] like @Keywords or [ContactName] like @Keywords or [ContactPhone] like @Keywords or [LicenseNumber] like @Keywords)"); parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%")); } string fieldList = "[Mall_Business].*"; string Statement = " from [Mall_Business] where " + string.Join(" and ", conditions.ToArray()); Mall_BusinessDetail[] list = GetList <Mall_BusinessDetail>(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_ThreadCommentDetailGridByKeyword(int ThreadID, string keywords, long startRowIndex, int pageSize) { long totalRows = 0; List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("1=1"); if (ThreadID <= 0) { return(new Ui.DataGrid()); } conditions.Add("([ThreadID]=@ThreadID)"); parameters.Add(new SqlParameter("@ThreadID", ThreadID)); if (!string.IsNullOrEmpty(keywords)) { conditions.Add("([Comment] like @keywords or [UserName] like @keywords)"); parameters.Add(new SqlParameter("@keywords", "%" + keywords + "%")); } string OrderBy = " order by [AddTime] desc"; string fieldList = "A.*"; string Statement = " from (select *,(select NickName from [User] where UserID=Mall_ThreadComment.UserID) as NickName from [Mall_ThreadComment])A where " + string.Join(" and ", conditions.ToArray()); var list = GetList <Mall_ThreadCommentDetail>(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 GetPaySummaryGridByKeywords(string Keywords, string orderBy, long startRowIndex, int pageSize) { 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)) { conditions.Add("[PayName] like @Keywords"); parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%")); } #endregion string fieldList = "[PaySummary].*"; string Statement = " from [PaySummary] where " + string.Join(" and ", conditions.ToArray()) + cmd; PaySummary[] list = new PaySummary[] { }; list = GetList <PaySummary>(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 GetViewContractFeeHistoryGridByContractID(List <int> ContractIDList, bool IncludIsCharged, string orderBy, long startRowIndex, int pageSize) { long totalRows = 0; List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("1=1"); if (ContractIDList.Count > 0) { conditions.Add("[ContractID] in (" + string.Join(",", ContractIDList.ToArray()) + ")"); } if (IncludIsCharged) { conditions.Add("[ChargeState] in (1,4)"); } string fieldList = "[ViewContractFeeHistory].* "; string Statement = " from [ViewContractFeeHistory] where " + string.Join(" and ", conditions.ToArray()); ViewContractFeeHistory[] list = new ViewContractFeeHistory[] { }; list = GetList <ViewContractFeeHistory>(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 GetViewContractRoomListByKeywords(int ContractID, string guid, string orderBy, long startRowIndex, int pageSize) { long totalRows = 0; List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("1=1"); 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 = "[ViewContractRoom].*"; string Statement = " from [ViewContractRoom] where " + string.Join(" and ", conditions.ToArray()); ViewContractRoom[] list = new ViewContractRoom[] { }; list = GetList <ViewContractRoom>(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 GetCKInCategoryGridByKeywords(string Keywords, string CategoryType, string orderBy, long startRowIndex, int pageSize) { long totalRows = 0; List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("1=1"); if (!string.IsNullOrEmpty(Keywords)) { conditions.Add("([InCategoryName] like @Keywords)"); parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%")); } if (!string.IsNullOrEmpty(CategoryType)) { conditions.Add("([CategoryType]=@CategoryType)"); parameters.Add(new SqlParameter("@CategoryType", CategoryType)); } string fieldList = "[CKInCategory].*"; string Statement = " from [CKInCategory] where " + string.Join(" and ", conditions.ToArray()); CKInCategory[] list = new CKInCategory[] { }; list = GetList <CKInCategory>(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 GeWechat_UserGridByKeywords(string Keywords, int Wechat_MsgID, int ChooseState, string orderBy, long startRowIndex, int pageSize) { long totalRows = 0; List<SqlParameter> parameters = new List<SqlParameter>(); List<string> conditions = new List<string>(); conditions.Add("1=1"); if (ChooseState == 1) { conditions.Add("isnull([IsChosen],0)>0"); } else if (ChooseState == 2) { conditions.Add("isnull([IsChosen],0)=0"); } if (!string.IsNullOrEmpty(Keywords)) { conditions.Add("([NickName] like @Keywords or [City] like @Keywords or [Province] like @Keywords)"); parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%")); } parameters.Add(new SqlParameter("@Wechat_MsgID", Wechat_MsgID)); string fieldList = "A.*"; string Statement = " from (select Wechat_User.*,(select count(1) from Wechat_MsgUser where [OpenID]=Wechat_User.OpenId and Wechat_MsgID=@Wechat_MsgID) as IsChosen from [Wechat_User])A where " + string.Join(" and ", conditions.ToArray()); Wechat_UserDetail[] list = GetList<Wechat_UserDetail>(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 GeWechat_RentHomeDetailGridByKeywords(string Keywords, List <int> AreaIDList, List <int> BuildingIDList, string orderBy, long startRowIndex, int pageSize) { long totalRows = 0; List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add(" 1=1 "); if (!string.IsNullOrEmpty(Keywords)) { conditions.Add("([AreaName] like @Keywords or [HomeName] like @Keywords or [HomeLocation] like @Keywords or [PhoneNumber] like @Keywords)"); parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%")); } if (AreaIDList.Count > 0) { conditions.Add("[AreaID] in (" + string.Join(",", AreaIDList.ToArray()) + ")"); } if (BuildingIDList.Count > 0) { conditions.Add("[BuildingID] in (" + string.Join(",", BuildingIDList.ToArray()) + ")"); } string fieldList = "A.*"; string Statement = " from (select *,(select AreaName from [Wechat_RentArea] where ID=Wechat_RentHome.AreaID) as AreaName,(select BuildingName from [Wechat_RentBuilding] where ID=Wechat_RentHome.BuildingID) as BuildingName from [Wechat_RentHome])A where " + string.Join(" and ", conditions.ToArray()); Wechat_RentHomeDetail[] list = GetList <Wechat_RentHomeDetail>(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 GetCheque_InDetailGridByKeywords(int InSummaryID, string GUID, string orderBy, long startRowIndex, int pageSize) { long totalRows = 0; List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("[GUID]=@GUID"); parameters.Add(new SqlParameter("@GUID", GUID)); if (InSummaryID > 0) { conditions.Add("[InSummaryID]=@InSummaryID"); parameters.Add(new SqlParameter("@InSummaryID", InSummaryID)); } string fieldList = "[Cheque_InDetail].*"; string Statement = " from [Cheque_InDetail] where " + string.Join(" or ", conditions.ToArray()); Cheque_InDetail[] list = GetList <Cheque_InDetail>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray(); DataAccess.Ui.DataGrid dg = new Ui.DataGrid(); dg.rows = list; string footertext = "select '合计' as [ProductName],'' as [ModelNumber],'' as [Unit], sum(isnull([TotalCost],0)) as TotalCost,sum(isnull([TotalTaxCost],0)) as TotalTaxCost from [Cheque_InDetail] where " + string.Join(" or ", conditions.ToArray()) + @" UNION ALL select '税价合计(大写)' as[ProductName], dbo.MoneyToCapital(sum(isnull([TotalSummaryCost],0)),0,2) as [ModelNumber],'小写' as[Unit], sum(isnull([TotalSummaryCost],0)) as TotalCost, 0 as TotalTaxCost from [Cheque_InDetail] where " + string.Join(" or ", conditions.ToArray()); dg.footer = GetList <Cheque_InDetail>(footertext, parameters).ToArray(); dg.total = totalRows; dg.page = pageSize; return(dg); }
public static Ui.DataGrid GetProject_ServiceTypeDelayGrid(string Keywords) { List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("ParentID=1"); if (!string.IsNullOrEmpty(Keywords)) { conditions.Add("Name like @Keywords"); parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%")); } string cmdtext = "select Project.ID,Project.Name from Project where " + string.Join(" and ", conditions) + " order by ID desc"; var list = GetList <Project>(cmdtext, parameters).ToArray(); var delayList = Project_ServiceTypeDelay.GetProject_ServiceTypeDelays().ToArray(); var results = new List <Dictionary <string, object> >(); foreach (var item in list) { var resultItem = new Dictionary <string, object>(); resultItem["ProjectID"] = item.ID; resultItem["ProjectName"] = item.Name; var myDelayItem = delayList.FirstOrDefault(p => p.ProjectID == item.ID); resultItem["DelayHour"] = myDelayItem != null?myDelayItem.DelayHour.ToString("0") : ""; results.Add(resultItem); } var dg = new Ui.DataGrid(); dg.rows = results; dg.total = results.Count; dg.page = 1; return(dg); }
public static Ui.DataGrid GetMall_BalanceRuleGridByKeywords(string keywords, long startRowIndex, int pageSize, int RuleType) { 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 (!string.IsNullOrEmpty(keywords)) { conditions.Add("([Title] like @keywords)"); parameters.Add(new SqlParameter("@keywords", "%" + keywords + "%")); } if (RuleType > 0) { conditions.Add("[RuleType]=@RuleType"); parameters.Add(new SqlParameter("@RuleType", RuleType)); } string fieldList = "Mall_BalanceRule.*"; string Statement = " from Mall_BalanceRule where " + string.Join(" and ", conditions.ToArray()); Mall_BalanceRule[] list = GetList <Mall_BalanceRule>(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 GetWechat_HouseServiceDetailGridByKeywords(string Keywords, List <int> CategoryIDList, string orderBy, long startRowIndex, int pageSize, int type) { long totalRows = 0; List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("1=1"); if (type == 1) { conditions.Add("[IsWechatShow]=1"); } if (type == 2 || type == 3) { conditions.Add("([IsAPPCustomerShow]=1 or [IsAPPUserShow]=1)"); } if (!string.IsNullOrEmpty(Keywords)) { conditions.Add("([Title] like @Keywords or [ContactPhone] like @Keywords)"); parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%")); } if (CategoryIDList.Count > 0) { conditions.Add("[CategoryID] in (" + string.Join(",", CategoryIDList.ToArray()) + ")"); } string fieldList = "A.*"; string Statement = " from (select *,(select CategoryName from [Wechat_HouseServiceCategory] where ID=Wechat_HouseService.CategoryID) as CategoryName from [Wechat_HouseService])A where " + string.Join(" and ", conditions.ToArray()); Wechat_HouseServiceDetail[] list = GetList <Wechat_HouseServiceDetail>(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 GetViewWechatLotteryCheckerList(string keywords, int activityID, string orderBy, long startRowIndex, int pageSize) { long totalRows = 0; List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("1=1"); if (!string.IsNullOrEmpty(keywords)) { parameters.Add(new SqlParameter("@Keywords", "%" + keywords + "%")); conditions.Add("([NickName] like @Keywords or [RealName] like @Keywords or [LoginName] like @Keywords)"); } if (activityID > 0) { parameters.Add(new SqlParameter("@ActivityID", activityID)); conditions.Add("[ActivityID]=@ActivityID"); } var list = GetList <ViewWechatLotteryChecker>("*", "from [ViewWechatLotteryChecker] where " + string.Join(" and ", conditions.ToArray()), 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 GetSysManualGridByKeywords(string Keywords, List <int> CategoryIDList, string orderBy, long startRowIndex, int pageSize) { long totalRows = 0; List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("1=1"); if (!string.IsNullOrEmpty(Keywords)) { conditions.Add("([Title] like @Keywords)"); parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%")); } if (CategoryIDList.Count > 0) { conditions.Add("[CategoryID] in (" + string.Join(",", CategoryIDList.ToArray()) + ")"); } string fieldList = "[SysManual].*,(select CategoryName from [SysManualCategory] where ID=[SysManual].CategoryID) as CategoryName"; string Statement = " from [SysManual] where " + string.Join(" and ", conditions.ToArray()); SysManualDetail[] list = GetList <SysManualDetail>(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_UserJiXiaoPointDetailGridByKeywords(string Keywords, DateTime StartTime, DateTime EndTime, string orderBy, long startRowIndex, int pageSize) { long totalRows = 0; List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("1=1"); if (StartTime > DateTime.MinValue) { conditions.Add("[ApproveTime]>=@StartTime"); parameters.Add(new SqlParameter("@StartTime", StartTime)); } if (EndTime > DateTime.MinValue) { conditions.Add("[ApproveTime]<=@EndTime"); parameters.Add(new SqlParameter("@EndTime", EndTime)); } if (!string.IsNullOrEmpty(Keywords)) { conditions.Add("([InfoName] like @Keywords or [CategoryName] like @Keywords)"); parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%")); } string fieldList = "A.* "; string Statement = " from (select *,(select LoginName from [User] where [UserID]=[Mall_UserJiXiaoPoint].UserID) as UserName from [Mall_UserJiXiaoPoint])A where " + string.Join(" and ", conditions.ToArray()); Mall_UserJiXiaoPointDetail[] list = GetList <Mall_UserJiXiaoPointDetail>(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 GetContract_TempPriceGridByGuid(string guid, string orderBy, long startRowIndex, int pageSize, int ChargeID, int ContractID = 0) { ReSetParams(); long totalRows = 0; List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("1=1"); if (ChargeID > 0) { conditions.Add("[ChargeID]=@ChargeID"); parameters.Add(new SqlParameter("@ChargeID", ChargeID)); } if (!string.IsNullOrEmpty(guid)) { conditions.Add("[GUID]=@guid"); parameters.Add(new SqlParameter("@guid", guid)); } string fieldList = "A.* "; string Statement = " from (select [ViewContract_TempPrice].*,(select top 1 ID from [Contract_RoomCharge] where [Contract_TempPriceID]=[ViewContract_TempPrice].ID) as Contract_RoomChargeID, " + ContractID + " as ContractID from [ViewContract_TempPrice])A where " + string.Join(" and ", conditions.ToArray()); ViewContract_TempPrice[] list = GetList <ViewContract_TempPrice>(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_AmountRuleDetailGridByKeywords(string keywords, long startRowIndex, int pageSize, int RuleType, int AmountType) { long totalRows = 0; string OrderBy = " order by [AmountType] asc,[RuleType] asc, [StartAmount] asc"; List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("1=1"); if (AmountType > 0) { conditions.Add("[AmountType]=@AmountType"); parameters.Add(new SqlParameter("@AmountType", AmountType)); } if (RuleType > 0) { conditions.Add("[RuleType]=@RuleType"); parameters.Add(new SqlParameter("@RuleType", RuleType)); } if (!string.IsNullOrEmpty(keywords)) { conditions.Add("([Title] like @keywords or [UserLevelID] in (select [ID] from [Mall_UserLevel] where [Name] like @keywords))"); parameters.Add(new SqlParameter("@keywords", "%" + keywords + "%")); } string fieldList = "A.*"; string Statement = " from (select *,(select Name from [Mall_UserLevel] where [ID]=[Mall_AmountRule].[UserLevelID]) as PartnerRankName from [Mall_AmountRule])A where " + string.Join(" and ", conditions.ToArray()); Mall_AmountRuleDetail[] list = GetList <Mall_AmountRuleDetail>(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 GeWechat_MsgGridByKeywords(string Keywords, string MsgType, string orderBy, long startRowIndex, int pageSize, int type, int msgtypeid, List <int> EqualProjectIDList = null) { long totalRows = 0; List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("1=1"); if (EqualProjectIDList != null && EqualProjectIDList.Count > 0) { List <string> cmdlist = new List <string>(); cmdlist.Add("exists (select 1 from [Wechat_MsgProject] where [MsgID]=Wechat_Msg.ID and [ProjectID] in (" + string.Join(",", EqualProjectIDList.ToArray()) + "))"); cmdlist.Add("exists (select 1 from [Wechat_MsgProject] where [MsgID]=Wechat_Msg.ID and not exists (select 1 from Project where ID=Wechat_MsgProject.[ProjectID] and ID!=1))"); cmdlist.Add("not exists (select 1 from [Wechat_MsgProject] where [MsgID]=Wechat_Msg.ID and [ProjectID]!=1)"); conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")"); } if (type == 1) { conditions.Add("([IsWechatSend]=1 or ([IsWechatSend] is null and [IsCustomerAPPSend] is null and [IsUserAPPSend] is null))"); } if (type == 2 || type == 3) { conditions.Add("([IsCustomerAPPSend]=1 or [IsUserAPPSend]=1)"); } if (msgtypeid == 1) { conditions.Add("[MsgType]=@MsgType"); parameters.Add(new SqlParameter("@MsgType", Utility.EnumModel.WechatMsgType.tongzhi.ToString())); } if (msgtypeid == 2) { conditions.Add("[MsgType]=@MsgType"); parameters.Add(new SqlParameter("@MsgType", Utility.EnumModel.WechatMsgType.huodong.ToString())); } if (msgtypeid == 3) { conditions.Add("[MsgType]=@MsgType"); parameters.Add(new SqlParameter("@MsgType", Utility.EnumModel.WechatMsgType.news.ToString())); } if (!string.IsNullOrEmpty(Keywords)) { conditions.Add("([MsgTitle] like @Keywords or [MsgContent] like @Keywords or [MsgSummary] like @Keywords)"); parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%")); } if (!string.IsNullOrEmpty(MsgType)) { conditions.Add("[MsgType]=@MsgType"); parameters.Add(new SqlParameter("@MsgType", MsgType)); } string fieldList = "[Wechat_Msg].*"; string Statement = " from [Wechat_Msg] where " + string.Join(" and ", conditions.ToArray()); Wechat_Msg[] list = GetList <Wechat_Msg>(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 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 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 GetViewChequeOutSummaryGridByKeywords(string keywords, List <int> SellerList, List <int> ProductList, List <int> DepartmentList, List <int> ProjectList, DateTime StartTime, DateTime EndTime, string ChequeCode, string orderBy, long startRowIndex, int pageSize) { long totalRows = 0; List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("1=1"); if (StartTime > DateTime.MinValue) { conditions.Add("CONVERT(varchar(100), [ChequeTime], 23)>=@StartTime"); parameters.Add(new SqlParameter("@StartTime", StartTime)); } if (EndTime > DateTime.MinValue) { conditions.Add("CONVERT(varchar(100), [ChequeTime], 23)<=@EndTime"); parameters.Add(new SqlParameter("@EndTime", EndTime)); } if (!string.IsNullOrEmpty(ChequeCode)) { conditions.Add("[ChequeCode] like @ChequeCode"); parameters.Add(new SqlParameter("@ChequeCode", "%" + ChequeCode + "%")); } if (SellerList.Count > 0) { conditions.Add("([SellerID] in (" + string.Join(",", SellerList.ToArray()) + "))"); } if (ProductList.Count > 0) { conditions.Add("[ProductID] in (" + string.Join(",", ProductList.ToArray()) + ")"); } if (DepartmentList.Count > 0) { conditions.Add("([DepartmentID] in (" + string.Join(",", DepartmentList.ToArray()) + "))"); } if (ProjectList.Count > 0) { conditions.Add("([ProjectID] in (" + string.Join(",", ProjectList.ToArray()) + "))"); } if (!string.IsNullOrEmpty(keywords)) { conditions.Add("([DepartmentName] like @keywords or [ProjectName] like @keywords or [SellerName] like @keywords or [SellerTaxNumber] like @keywords or [SellerAddressPhone] like @keywords or [SellerBankAccount] like @keywords)"); parameters.Add(new SqlParameter("@keywords", "%" + keywords + "%")); } string fieldList = "[ViewChequeOutSummary].*,(select sum(isnull([TotalSummaryCost],0)) from [Cheque_OutDetail] where [OutSummaryID]=[ViewChequeOutSummary].ID) as [ChequeTotalCost]"; string Statement = " from [ViewChequeOutSummary] where " + string.Join(" and ", conditions.ToArray()); ViewChequeOutSummary[] list = GetList <ViewChequeOutSummary>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray(); DataAccess.Ui.DataGrid dg = new Ui.DataGrid(); dg.rows = list; string footerttext = "select sum(isnull([TotalCost],0)) as TotalCost,sum(isnull([TotalTaxCost],0)) as TotalTaxCost,sum(isnull([TotalCount],0)) as TotalCount,(select sum(isnull([TotalSummaryCost],0)) from [Cheque_OutDetail] where [Cheque_OutDetail].OutSummaryID in (select ID from [ViewChequeOutSummary] where " + string.Join(" and ", conditions.ToArray()) + ")) as ChequeTotalCost from [ViewChequeOutSummary] where " + string.Join(" and ", conditions.ToArray()); dg.footer = GetList <ViewChequeOutSummary>(footerttext, parameters).ToArray(); dg.total = totalRows; dg.page = pageSize; return(dg); }