Exemplo n.º 1
0
        /// <summary>
        /// データを削除するSQLを生成する。
        /// </summary>
        /// <param name="mode"></param>
        /// <param name="id"></param>
        /// <param name="speciallistOnly"></param>
        /// <returns></returns>
        public static string deleteRecord(LinearEnum.PlaylistMode mode, long id, bool speciallistOnly)
        {
            StringBuilder sb          = new StringBuilder();
            string        targetTable = "";

            switch (mode)
            {
            case LinearEnum.PlaylistMode.NORMAL:
                targetTable = PLAYLIST_TABLE_NAME;
                break;

            default:
                //targetTable = SPECIALLIST_TABLE_NAME;
                break;
            }
            sb.Append("DELETE FROM ");
            sb.Append(targetTable);
            sb.Append(" WHERE ID = ");
            sb.Append(id.ToString());
            if (speciallistOnly)
            {
                sb.Append(" AND CLASS = ");
                sb.Append(((int)mode).ToString());
            }

            return(sb.ToString());
        }
Exemplo n.º 2
0
        public static string getRatingWhereString(LinearEnum.PlaylistMode playlistMode)
        {
            switch (playlistMode)
            {
            case LinearEnum.PlaylistMode.NORMAL:
                return("RATING > 0 ");

            case LinearEnum.PlaylistMode.FAVORITE:
                return("RATING = 9 ");

            case LinearEnum.PlaylistMode.EXCLUSION:
                return("RATING = 0 ");
            }
            return("");
        }
Exemplo n.º 3
0
        /*
         *  パブリックメソッド
         */
        #region Public Method

        /// <summary>
        /// プレイリスト取得用SQL生成
        /// </summary>
        /// <param name="baseSql"></param>
        /// <param name="playlistMode"></param>
        /// <param name="filterString"></param>
        /// <returns></returns>
        public static string selectPlaylist(
            string baseSql,
            LinearEnum.PlaylistMode playlistMode,
            string filterString,
            LinearEnum.FilteringMode filteringMode,
            ConditionGridItemInfo conditionItem)
        {
            bool          isNotOrder = false;
            bool          isFullSQL  = false;
            StringBuilder sb         = new StringBuilder();

            //sb.Append(baseSql);

            // プレイリストによる絞り込み
            sb.Append(WHERE_STRING);
            sb.Append(getRatingWhereString(playlistMode));

            if (!String.IsNullOrEmpty(filterString))
            {
                sb.Append(AND_STRING);
                if (filterString.Length < 3 || !Regex.IsMatch(filterString, @"^[a-zA-Z0-9 \-]+$"))
                {
                    sb.Append(
                        "IFNULL(TITLE,'') || IFNULL(ARTIST,'') || IFNULL(ALBUM,'') || IFNULL(TAG,'') LIKE '%" +
                        escapeSQL(filterString) + "%'");
                }
                else
                {
                    sb.Append(
                        "(( IFNULL(TITLE,'') || IFNULL(ARTIST,'') || IFNULL(ALBUM,'') || IFNULL(TAG,'') LIKE '%" +
                        escapeSQL(filterString) + "%' ) OR (");
                    sb.Append("ISMATCHMIGEMO('" + escapeSQL(filterString) +
                              "',IFNULL(TITLE,'') || IFNULL(ARTIST,'') || IFNULL(ALBUM,'') || IFNULL(TAG,'')) ))");
                }
                //sb.Append(AND_STRING);
            }

            // コンディションによる絞り込み
            switch (filteringMode)
            {
            case LinearEnum.FilteringMode.DEFAULT:
                // カスタム
                if (!String.IsNullOrEmpty(conditionItem.Value))
                {
                    if (!(conditionItem.Value.Substring(0, 3).ToUpper().Equals("AND") || conditionItem.Value.Substring(0, 2).ToUpper().Equals("OR")))
                    {
                        isFullSQL = true;
                    }
                    sb.Append(" ");

                    string customSql = conditionItem.Value;

                    if (!StringUtils.hasString(customSql, "#LIMIT#") ||
                        LinearGlobal.LinearConfig.DatabaseConfig.LimitCount == 0)
                    {
                        customSql = customSql.Replace("#LIMIT#", "");
                    }
                    else
                    {
                        customSql = customSql.Replace(
                            "#LIMIT#",
                            " LIMIT " + LinearGlobal.LinearConfig.DatabaseConfig.LimitCount.ToString());
                    }

                    sb.Append(customSql);

                    isNotOrder = true;
                }
                break;

            case LinearEnum.FilteringMode.TAG:

                if (conditionItem.Value != null)
                {
                    sb.Append(AND_STRING);
                    if (!String.IsNullOrEmpty(conditionItem.Value))
                    {
                        sb.Append("ISMATCHTAG(TAG, '" + escapeSQL(conditionItem.Value) + "')");
                    }
                    else
                    {
                        sb.Append("(");
                        sb.Append(filteringMode.ToString() + " = ''");
                        sb.Append(OR_STRING);
                        sb.Append(filteringMode.ToString() + " IS NULL");
                        sb.Append(")");
                    }
                }
                break;

            case LinearEnum.FilteringMode.FOLDER:
                if (conditionItem.Value != null)
                {
                    sb.Append(AND_STRING);
                    if (!String.IsNullOrEmpty(conditionItem.Value))
                    {
                        sb.Append("GETDIRNAME(FILEPATH) = '" + escapeSQL(conditionItem.Value) + "'");
                    }
                    else
                    {
                        sb.Append("GETDIRNAME(FILEPATH) = ''");
                        sb.Append(OR_STRING);
                        sb.Append("GETDIRNAME(FILEPATH) IS NULL");
                    }
                }
                break;

            default:
                if (conditionItem.Value != null)
                {
                    sb.Append(AND_STRING);
                    if (!String.IsNullOrEmpty(conditionItem.Value))
                    {
                        if (filteringMode != LinearEnum.FilteringMode.GENRE)
                        {
                            sb.Append(filteringMode.ToString() + " = '" + escapeSQL(conditionItem.Value) + "'");
                        }
                        else
                        {
                            // ジャンルだけ大文字でマッチングする
                            sb.Append("trim(upper(" + filteringMode.ToString() + ")) = '" + escapeSQL(conditionItem.Value) + "'");
                        }
                    }
                    else
                    {
                        sb.Append(filteringMode.ToString() + " = ''");
                        sb.Append(OR_STRING);
                        sb.Append(filteringMode.ToString() + " IS NULL");
                    }

                    if (filteringMode == LinearEnum.FilteringMode.ALBUM)
                    {
                        sb.Append("ORDER BY ABS(PL.TRACK)");
                        isNotOrder = true;
                    }
                }
                break;
            }
            LAST_WHERE_SQL = sb.ToString();

            if (!isNotOrder)
            {
                if (LinearGlobal.SortMode == LinearEnum.SortMode.DEFAULT)
                {
                    sb.Append(ORDERBY_STRING);
                }
                else
                {
                    if (LinearGlobal.FilteringMode == LinearEnum.FilteringMode.ARTIST &&
                        LinearGlobal.ShuffleMode == LinearEnum.ShuffleMode.OFF)
                    {
                        sb.Append(ORDERBY_STRING_ORIGINAL_SORT);
                    }
                    else
                    {
                        sb.Append(ORDERBY_STRING);
                    }
                }

                LAST_WHERE_SQL = sb.ToString();
            }

            if (isFullSQL)
            {
                return(baseSql + " " + conditionItem.Value);
            }
            if (LinearGlobal.ShuffleMode == LinearEnum.ShuffleMode.OFF)
            {
                LAST_WHERE_SQL = LAST_WHERE_SQL.Replace("#NOSHUFFLE#", "");
                return(baseSql + LAST_WHERE_SQL);
            }
            else
            {
                return(SQLResource.SQL029.Replace("#SQL#", baseSql + LAST_WHERE_SQL));
            }
        }
Exemplo n.º 4
0
        /// <summary>
        /// フィルタリングコンボ選択用SQL
        /// </summary>
        /// <param name="playlistMode"></param>
        /// <param name="condtionModeName"></param>
        /// <returns></returns>
        public static string selectConditionList(
            LinearEnum.PlaylistMode playlistMode,
            LinearEnum.FilteringMode condtionMode,
            string filterString)
        {
            string conditionModeName;

            if (condtionMode != LinearEnum.FilteringMode.FOLDER)
            {
                conditionModeName = Enum.GetName(typeof(LinearEnum.FilteringMode), condtionMode);
            }
            else
            {
                conditionModeName = "GETDIRNAME(FILEPATH)";
            }

            string ratingCondition = getRatingWhereString(playlistMode);

            string result = "";

            if (condtionMode != LinearEnum.FilteringMode.GENRE)
            {
                result = SQLResource.SQL012.Replace("#condMode#", conditionModeName)
                         .Replace("#RATING#", ratingCondition);
            }
            else
            {
                // ジャンルだけすべて大文字でグループ化する
                result = SQLResource.SQL012.Replace("#condMode#", "trim(upper(" + conditionModeName + "))")
                         .Replace("#RATING#", ratingCondition);
            }

            if (!String.IsNullOrEmpty(filterString))
            {
                if (filterString.Length < 3 || !Regex.IsMatch(filterString, @"^[a-zA-Z0-9 \-]+$"))
                {
                    result = result.Replace("#FILTERING#", AND_STRING +
                                            "IFNULL(" + conditionModeName + ",'') LIKE '%" +
                                            escapeSQL(filterString) + "%' ");
                }
                else
                {
                    result = result.Replace("#FILTERING#", AND_STRING +
                                            "(( IFNULL(" + conditionModeName + ",'') LIKE '%" + escapeSQL(filterString) + "%' ) OR ("
                                            + "ISMATCHMIGEMO('" + escapeSQL(filterString) + "',IFNULL(" + conditionModeName + ",'')) ))");
                }
            }
            else
            {
                result = result.Replace("#FILTERING#", "");
            }

            if (LinearEnum.FilteringMode.ALBUM.ToString().Equals(conditionModeName.ToUpper()))
            {
                result = result.Replace("#condCount#", LinearGlobal.LinearConfig.ViewConfig.AlbumJudgeCount.ToString());
            }
            else
            {
                result = result.Replace("#condCount#", "2");
            }

            if (LinearGlobal.LinearConfig.ViewConfig.GroupListOrder
                == (int)LinearEnum.EnumGroupListOrder.COUNT_DESC)
            {
                switch (condtionMode)
                {
                case LinearEnum.FilteringMode.ARTIST:
                case LinearEnum.FilteringMode.GENRE:
                case LinearEnum.FilteringMode.TAG:
                case LinearEnum.FilteringMode.FOLDER:
                    result += " ORDER BY COUNT(*) DESC";
                    break;

                case LinearEnum.FilteringMode.ALBUM:
                    result += " ORDER BY MAX(DATE) DESC";
                    break;

                case LinearEnum.FilteringMode.YEAR:
                    result += " ORDER BY " + conditionModeName + " DESC";
                    break;
                }
            }
            else
            {
                switch (condtionMode)
                {
                case LinearEnum.FilteringMode.ARTIST:
                    result += " ORDER BY ARTIST ASC";
                    break;

                case LinearEnum.FilteringMode.GENRE:
                    result += " ORDER BY GENRE ASC";
                    break;

                case LinearEnum.FilteringMode.TAG:
                    result += " ORDER BY TAG ASC";
                    break;

                case LinearEnum.FilteringMode.FOLDER:
                    result += " ORDER BY GETDIRNAME(FILEPATH) ASC";
                    break;

                case LinearEnum.FilteringMode.ALBUM:
                    result += " ORDER BY ALBUM ASC";
                    break;

                case LinearEnum.FilteringMode.YEAR:
                    result += " ORDER BY YEAR ASC";
                    break;
                }
            }

            return(result);
        }