예제 #1
0
        public static Ui.DataGrid GetMall_BusinessBalanceDetailGridByKeywords(string Keywords, DateTime StartTime, DateTime EndTime, int BalanceStatus, long startRowIndex, int pageSize)
        {
            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 (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("([BusinessID] in (select [ID] from [Mall_Business] where [BusinessName] like @keywords))");
                parameters.Add(new SqlParameter("@keywords", "%" + Keywords + "%"));
            }
            string fieldList = "A.*";
            string Statement = " from (select [Mall_BusinessBalance].*,[Mall_Business].BusinessName,[Mall_Business].ContactName as BusinessContactMan from [Mall_BusinessBalance] left join [Mall_Business] on [Mall_Business].ID=[Mall_BusinessBalance].BusinessID)A where  " + string.Join(" and ", conditions.ToArray());

            Mall_BusinessBalanceDetail[] list = GetList <Mall_BusinessBalanceDetail>(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);
        }
예제 #2
0
        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);
        }