Пример #1
0
        public List<KeyValuePair<string, double>> GetConsumeAmountList(string format, ConsumeRecortQueryOption option)
        {
            string cmdText = string.Format(@"SELECT strftime('{0}', R.ConsumeTime) as 'Time', Sum(R.Money) as 'Money'
                                FROM [ConsumeRecord] R
                                     JOIN [USER] U ON R.[UserId] = U.[UserId]
                                     JOIN [ConsumeType] T ON T.[TypeId] = R.[TypeId]
                                WHERE 1=1", format);

            cmdText = string.Format("{0} AND R.[ConsumeTime] >= '{1}'", cmdText, option.BeginTime.ToString("yyyy-MM-dd HH:mm:ss"));
            cmdText = string.Format("{0} AND R.[ConsumeTime] < '{1}'", cmdText, option.EndTime.ToString("yyyy-MM-dd HH:mm:ss"));

            if (option.ConsumeType.TypeId != 0)
            {
                if (option.ConsumeType.ParentTypeId != 0)
                {
                    cmdText = string.Format("{0} AND R.[TypeId] = {1}", cmdText, option.ConsumeType.TypeId);
                }
                else
                {
                    cmdText = string.Format("{0} AND T.[ParentTypeId] = {1}", cmdText, option.ConsumeType.TypeId);
                }
            }

            if (option.UserId != 0)
            {
                cmdText = string.Format("{0} AND U.[UserId] = {1}", cmdText, option.UserId);
            }

            cmdText = string.Format("{0} GROUP BY  strftime('{1}', R.ConsumeTime)", cmdText, format);

            Debug.WriteLine(cmdText);

            var reader = SqliteHelper.ExecuteReader(cmdText);

            return reader.ToConsumeAmountList();
        }
Пример #2
0
 public List<ConsumeRecord> GetConsumeRecordList(ConsumeRecortQueryOption option, out int recordCount, out decimal totalMoney)
 {
     return _consumeRecordDal.GetConsumeRecordList(option, out recordCount, out totalMoney);
 }
Пример #3
0
 public List<KeyValuePair<string, double>> GetConsumeAmountByYear(ConsumeRecortQueryOption option)
 {
     return _consumeRecordDal.GetConsumeAmountList("%m", option);
 }
Пример #4
0
        public List<ConsumeRecord> GetConsumeRecordList(ConsumeRecortQueryOption option, out int recordCount, out decimal totalMoney)
        {
            string cmdText = @"SELECT R.[Id] As RecordId, R.[ConsumeTime], R.[Money], R.[Memo], R.[RecordTime],
                                      U.[UserId], U.[UserName], U.[FullName],
                                      T.[TypeId], T.[ParentTypeId], T.[TypeName]
                                FROM [ConsumeRecord] R
                                     JOIN [USER] U ON R.[UserId] = U.[UserId]
                                     JOIN [ConsumeType] T ON T.[TypeId] = R.[TypeId]
                                WHERE 1=1";

            cmdText = string.Format("{0} AND R.[ConsumeTime] >= '{1}'", cmdText, option.BeginTime.ToString("yyyy-MM-dd HH:mm:ss"));
            cmdText = string.Format("{0} AND R.[ConsumeTime] < '{1}'", cmdText, option.EndTime.ToString("yyyy-MM-dd HH:mm:ss"));

            if (option.ConsumeType.TypeId != 0)
            {
                if(option.ConsumeType.ParentTypeId != 0)
                {
                    cmdText = string.Format("{0} AND R.[TypeId] = {1}", cmdText, option.ConsumeType.TypeId);
                }
                else
                {
                    cmdText = string.Format("{0} AND T.[ParentTypeId] = {1}", cmdText, option.ConsumeType.TypeId);
                }
            }

            if (option.UserId != 0)
            {
                cmdText = string.Format("{0} AND U.[UserId] = {1}", cmdText, option.UserId);
            }

            if(!string.IsNullOrWhiteSpace(option.SortName))
            {
                string sortName = option.SortName;
                switch (option.SortName)
                {
                    case "Memo":
                        sortName = "R.[Memo]";
                        break;
                    case "ConsumeTime":
                        sortName = "R.[ConsumeTime]";
                        break;
                    case "Money":
                        sortName = "R.[Money]";
                        break;
                    case "RecordTime":
                        sortName = "R.[RecordTime]";
                        break;
                    case "UserId":
                        sortName = "U.[UserId]";
                        break;
                    case "UserName":
                        sortName = "U.[UserName]";
                        break;
                    case "User.FullName":
                        sortName = "U.[FullName]";
                        break;
                    case "TypeId":
                        sortName = "T.[UserId]";
                        break;
                    case "ParentTypeId":
                        sortName = "T.[ParentTypeId]";
                        break;
                    case "Type.TypeName":
                        sortName = "T.[TypeName]";
                        break;
                }
                cmdText = string.Format("{0} ORDER BY {1} {2}", cmdText, sortName, option.SortDir);
            }

            Debug.WriteLine(cmdText);

            var reader =  SqliteHelper.ExecutePagerReader(out recordCount, option.PageIndex, option.PageSize, cmdText);

            cmdText = string.Format("SELECT SUM([Money]) FROM ({0})", cmdText);
            decimal.TryParse(SqliteHelper.ExecuteScalar(cmdText).ToString(), out totalMoney);

            return reader.ToConsumeRecordList();
        }