/// <summary> /// 字符型变量赋值 /// </summary> /// <param name="way">检索方式</param> /// <param name="pname">变量名</param> /// <param name="pvalue">变量值</param> /// <returns></returns> public static SqlParameter AddParam(SearchModule.StringSearchWay way, string pname, string pvalue) { SqlParameter param = new SqlParameter(pname, SqlDbType.NChar); param.Value = ReplaceParamValueAmbiguous((SearchModule.StringSearchWay)way, pvalue.ToString()); return param; }
/// <summary> /// 时间检索 /// </summary> /// <param name="way">检索方式</param> /// <param name="rule">检索规则</param> /// <param name="fieldTimeFrom">检索开始时间</param> /// <param name="fieldTimeTo">检索结束时间</param> /// <param name="parameterName">变量名</param> /// <returns></returns> public static string CaseOfRange(SearchModule.DateTimeSearchWay way, SearchModule.DateTimeSearchRule rule, string fieldTimeFrom, string fieldTimeTo, string parameterName) { switch (way) { //▼说明:在此时间内(InThisTime)-目标时间在搜索的时间内 //||SearchTimeFrom ------------------------------------->SearchTimeTo|| //||・・・・・・・・・・・・・・ TargetTime・・・・・・・・・・・・・|| //--ToDo:范围时间式样。目前固定为Inthistime //||・・・・・・TargetTimeFrom-------------->TargetTimeTo・・・・・・|| //▲说明:在此时间内(InThisTime)-目标时间在搜索的时间内 case SearchModule.DateTimeSearchWay.InThisTime: { //From if (rule == SearchModule.DateTimeSearchRule.From) { return string.Format(@" {0} >= @{1} ", parameterName, fieldTimeFrom); } //To if (rule == SearchModule.DateTimeSearchRule.To) { return string.Format(@" {0} <= @{1} ", parameterName, fieldTimeTo); } //FromTo if (rule == SearchModule.DateTimeSearchRule.FromTo) { return string.Format( @" {0} >= @{1} AND {0} <= @{2} ", parameterName, fieldTimeFrom, fieldTimeTo); } break; } //目前该式样不使用 //▼说明:包含此时间(InCludeThisTime)-目标时间包含搜索的时间--ToDo //||TargetTimeFrom ------------------------------------->TargetTimeTo|| //||・・・・・・SearchTimeFrom-------------->SearchTimeTo・・・・・・|| //▲说明:包含此时间(InCludeThisTime)-目标时间包含搜索的时间 //case SearchModule.DateTimeSearchWay.InCludeThisTime: // { // break; // } } return string.Empty; }
/// <summary> /// 日期型搜索用SQL文 /// </summary> /// <param name="way">检索方式</param> /// <param name="rule">检索规则</param> /// <param name="fieldTimeFrom">检索开始时间</param> /// <param name="fieldTimeTo">检索结束时间</param> /// <param name="parameterName">变量名</param> /// <returns></returns> public static string WhereSentenceBuilder(SearchModule.DateTimeSearchWay way, SearchModule.DateTimeSearchRule rule, string fieldTimeFrom, string fieldTimeTo, string parameterName) { return CaseOfRange(way, rule, fieldTimeFrom, fieldTimeTo, parameterName); }
/// <summary> /// 动画界面加载 /// 搜索 /// </summary> /// <param name="search">搜索窗体</param> /// <returns></returns> public DataSet Getanime(SearchModule search) { Collection<DbParameter> paras = new Collection<DbParameter>(); StringBuilder sqlmaincmd = new StringBuilder(); StringBuilder joincmd = new StringBuilder(); sqlmaincmd.Append(@"SELECT AT.ANIME_NO, AT.ANIME_CHN_NAME, AT.ANIME_JPN_NAME, AT.ANIME_NN, AT.STATUS, AT.ORIGINAL FROM {0} AT LEFT JOIN ( SELECT ANIME_NO,MAX(START_TIME) AS TIME FROM {1} WHERE ENABLE_FLG = 1 GROUP BY ANIME_NO ) AS WT ON AT.ANIME_NO = WT.ANIME_NO LEFT JOIN {1} PT ON AT.ANIME_NO = PT.ANIME_NO AND PT.ENABLE_FLG = 1 LEFT JOIN {2} COT ON PT.COMPANY_ID = COT.COMPANY_ID AND COT.ENABLE_FLG = 1 LEFT JOIN {3} CT ON CT.ANIME_NO = AT.ANIME_NO AND CT.ENABLE_FLG = 1 LEFT JOIN {4} CVT ON CVT.CV_ID = CT.CV_ID AND CVT.ENABLE_FLG = 1 "); #region 动画编号 //动画编号 if (!string.IsNullOrEmpty(search.animeNo)) { AddWhereAnd(joincmd); joincmd.Append(@" AT.ANIME_NO = @anime_no "); paras.Add(new SqlParameter("@anime_no", search.animeNo)); } #endregion #region 中文名 //中文名 if (!string.IsNullOrEmpty(search.animeCNName)) { AddWhereAnd(joincmd); joincmd.Append(SQLAndBuilder(search.animeCNNameSearchWay, "AT.ANIME_CHN_NAME", "anime_cn_name")); paras.Add(AddParam(search.animeCNNameSearchWay, "anime_cn_name", search.animeCNName)); } #endregion #region 日文名 //日文名 if (!string.IsNullOrEmpty(search.animeJPName)) { AddWhereAnd(joincmd); joincmd.Append(SQLAndBuilder(search.animeCNNameSearchWay, "AT.ANIME_JPN_NAME", "anime_jp_name")); paras.Add(AddParam(search.animeJPNameSearchWay, "anime_jp_name", search.animeJPName)); } #endregion #region 动画简写 //动画简写 if (!string.IsNullOrEmpty(search.animeNN)) { AddWhereAnd(joincmd); joincmd.Append(@" AT.ANIME_NN = @anime_nn "); paras.Add(new SqlParameter("@anime_nn", search.animeNN)); } #endregion #region 播放时间 //播放时间 if ((search.animePlaytimeFrom != DateTime.MinValue && search.animePlaytimeFrom != DateTime.MaxValue) || (search.animePlaytimeTo != DateTime.MinValue && search.animePlaytimeTo != DateTime.MaxValue)) { AddWhereAnd(joincmd); //确定搜索规则 //FromTo if ((search.animePlaytimeFrom != DateTime.MaxValue && search.animePlaytimeFrom != DateTime.MinValue) && (search.animePlaytimeTo != DateTime.MinValue && search.animePlaytimeTo != DateTime.MaxValue)) { search.animePlaytimeSearchRule = SearchModule.DateTimeSearchRule.FromTo; paras.Add(AddParam("anime_playtimefrom", search.animePlaytimeFrom)); paras.Add(AddParam("anime_playtimeto", search.animePlaytimeTo)); } else { //From if (search.animePlaytimeFrom != DateTime.MaxValue && search.animePlaytimeFrom != DateTime.MinValue) { search.animePlaytimeSearchRule = SearchModule.DateTimeSearchRule.From; paras.Add(AddParam("anime_playtimefrom", search.animePlaytimeFrom)); } //To else { search.animePlaytimeSearchRule = SearchModule.DateTimeSearchRule.To; paras.Add(AddParam("anime_playtimeto", search.animePlaytimeTo)); } } joincmd.Append(SQLAndBuilder(search.animePlaytimeSearchWay, search.animePlaytimeSearchRule, "anime_playtimefrom", "anime_playtimeto", "PT.START_TIME")); } #endregion #region 收看时间 //收看时间 if ((search.animeWatchtimeFrom != DateTime.MinValue && search.animeWatchtimeFrom != DateTime.MaxValue) || (search.animeWatchtimeTo != DateTime.MinValue && search.animeWatchtimeTo != DateTime.MaxValue)) { AddWhereAnd(joincmd); //确定搜索规则 //FromTo if ((search.animeWatchtimeFrom != DateTime.MaxValue && search.animeWatchtimeFrom != DateTime.MinValue) && (search.animeWatchtimeTo != DateTime.MinValue && search.animeWatchtimeTo != DateTime.MaxValue)) { search.animeWatchtimeSearchRule = SearchModule.DateTimeSearchRule.FromTo; paras.Add(AddParam("anime_watchtimefrom", search.animeWatchtimeFrom)); paras.Add(AddParam("anime_watchtimeto", search.animeWatchtimeTo)); } else { //From if (search.animeWatchtimeFrom != DateTime.MaxValue && search.animeWatchtimeFrom != DateTime.MinValue) { search.animeWatchtimeSearchRule = SearchModule.DateTimeSearchRule.From; paras.Add(AddParam("anime_watchtimefrom", search.animeWatchtimeFrom)); } //To else { search.animeWatchtimeSearchRule = SearchModule.DateTimeSearchRule.To; paras.Add(AddParam("anime_watchtimeto", search.animeWatchtimeTo)); } } joincmd.Append(SQLAndBuilder(search.animeWatchtimeSearchWay, search.animeWatchtimeSearchRule, "anime_watchtimefrom", "anime_watchtimeto", "PT.WATCH_TIME")); } #endregion #region 声优 if (!string.IsNullOrEmpty(search.CVName)) { AddWhereAnd(joincmd); joincmd.Append(SQLAndBuilder(search.CVNameSearchWay, "CVT.CV_NAME", "cv_name")); paras.Add(AddParam(search.CVNameSearchWay, "cv_name", search.CVName)); } #endregion #region 制作公司 if (!string.IsNullOrEmpty(search.Company)) { AddWhereAnd(joincmd); joincmd.Append(SQLAndBuilder(search.CompanyNameSearchWay, "COT.COMPANY_NAME", "company_name")); paras.Add(AddParam(search.CompanyNameSearchWay, "company_name", search.Company)); } #endregion #region 播放状态 //播放状态 AddWhereAnd(joincmd); joincmd.Append(" AT.STATUS IN ( "); StringBuilder statusincmd = new StringBuilder(); if (search.animeStatue.playing) { AddComma(statusincmd); statusincmd.Append(AnimeStatusModule.PLAYING.ToString()); } if (search.animeStatue.finished) { AddComma(statusincmd); statusincmd.Append(AnimeStatusModule.FINISHED.ToString()); } if (search.animeStatue.newproject) { AddComma(statusincmd); statusincmd.Append(AnimeStatusModule.NEW_PROJECT.ToString()); } if (search.animeStatue.discare) { AddComma(statusincmd); statusincmd.Append(AnimeStatusModule.DISCARE.ToString()); } joincmd.Append(statusincmd); joincmd.Append(" )"); #endregion #region 原作 //播放状态 AddWhereAnd(joincmd); joincmd.Append(" AT.ORIGINAL IN ( "); StringBuilder originalincmd = new StringBuilder(); if (search.animeOriginal.fromComic) { AddComma(originalincmd); originalincmd.Append(AnimeOriginalModule.COMIC.ToString()); } if (search.animeOriginal.fromNovel) { AddComma(originalincmd); originalincmd.Append(AnimeOriginalModule.NOVEL.ToString()); } if (search.animeOriginal.isoriginal) { AddComma(originalincmd); originalincmd.Append(AnimeOriginalModule.ORIGINAL.ToString()); } if (search.animeOriginal.fromvideo) { AddComma(originalincmd); originalincmd.Append(AnimeOriginalModule.VIDEO.ToString()); } if (search.animeOriginal.fromgame) { AddComma(originalincmd); originalincmd.Append(AnimeOriginalModule.GAME.ToString()); } if (search.animeOriginal.fromothers) { AddComma(originalincmd); originalincmd.Append(AnimeOriginalModule.OTHERS.ToString()); } joincmd.Append(originalincmd); joincmd.Append(" )"); #endregion #region FLAG AddWhereAnd(joincmd); joincmd.Append(" AT.ENABLE_FLG = 1"); #endregion #region ORDERBY joincmd.Append(@"ORDER BY CHARINDEX(RTRIM(CAST(AT.STATUS as NCHAR)),'1,3,2,9') , WT.TIME DESC"); #endregion string sqlcmd = sqlmaincmd.Append(joincmd.ToString()).ToString(); return DbCmd.DoSelect(string.Format(sqlcmd , CommonConst.TableName.T_ANIME_TBL , CommonConst.TableName.T_PLAYINFO_TBL , CommonConst.TableName.T_COMPANY_TBL , CommonConst.TableName.T_CHARACTER_TBL , CommonConst.TableName.T_CV_TBL), paras); }
/// <summary> /// 字符型搜索用SQL文:确定检索方式 /// </summary> /// <param name="way">检索方式</param> /// <param name="fieldName">目标字符</param> /// <param name="parameterName">变量名</param> /// <returns></returns> public static string WhereSentenceBuilder(SearchModule.StringSearchWay way, string fieldName, string parameterName) { if (string.IsNullOrEmpty(fieldName)) { throw new ArgumentException("字段名未设定", "fieldName"); } switch (way) { case SearchModule.StringSearchWay.Exact: return CaseOfExactMatch(fieldName, parameterName); case SearchModule.StringSearchWay.Forward: case SearchModule.StringSearchWay.Broad: return CaseOfAmbiguous(fieldName, parameterName); default: return string.Empty; } }
/// <summary> /// 模糊检索变量配置 /// </summary> /// <param name="way">检索方式</param> /// <param name="targetStr">目标值</param> /// <returns></returns> public static string ReplaceParamValueAmbiguous(SearchModule.StringSearchWay way, string targetStr) { if (way == SearchModule.StringSearchWay.Forward) { return targetStr + "%"; } if (way == SearchModule.StringSearchWay.Broad) { return "%" + targetStr + "%"; } return targetStr; }
/// <summary> /// 数据作成 /// </summary> /// <param name="mainSearch"></param> private void SetSearchModule(SearchModule mainSearch) { //动画编号 if (!string.IsNullOrEmpty(AnimeNoBox.Text.ToString())) { mainSearch.animeNo = AnimeNoBox.Text.ToString(); } //中文名及搜索方式 if (!string.IsNullOrEmpty(AnimeCNNamebox.Text.ToString())) { mainSearch.animeCNName = AnimeCNNamebox.Text.ToString(); mainSearch.animeCNNameSearchWay = mainSearch.GetStringSearchWay(AnimeCNNameSearchTypeButton.Text.ToString()); } //日文名及搜索方式 if (!string.IsNullOrEmpty(AnimeJPNameBox.Text.ToString())) { mainSearch.animeJPName = AnimeJPNameBox.Text.ToString(); mainSearch.animeJPNameSearchWay = mainSearch.GetStringSearchWay(AnimeJPNameSearcyTypeButton.Text.ToString()); } //简写 if (!string.IsNullOrEmpty(AnimeNNBox.Text.ToString())) { mainSearch.animeNN = AnimeNNBox.Text.ToString(); } //放送时间 if (!string.IsNullOrEmpty(PlaytimeFromBox.Text.ToString()) || !string.IsNullOrEmpty(PlaytimeToBox.Text.ToString())) { mainSearch.animePlaytimeSearchWay = mainSearch.GetDateTimeSearchWay(PlaytimeSearchTypeButton.Text.ToString()); //FROMTO if (!string.IsNullOrEmpty(PlaytimeFromBox.Text.ToString()) && !string.IsNullOrEmpty(PlaytimeToBox.Text.ToString())) { mainSearch.animePlaytimeFrom = service.ConvertToDateTimeFromYYYYMM(PlaytimeFromBox.Text.ToString()); mainSearch.animePlaytimeTo = service.ConvertToDateTimeFromYYYYMM(PlaytimeToBox.Text.ToString()); mainSearch.animePlaytimeSearchRule = SearchModule.DateTimeSearchRule.FromTo; } else { //FROM if (!string.IsNullOrEmpty(PlaytimeFromBox.Text.ToString())) { mainSearch.animePlaytimeFrom = service.ConvertToDateTimeFromYYYYMM(PlaytimeFromBox.Text.ToString()); mainSearch.animePlaytimeSearchRule = SearchModule.DateTimeSearchRule.From; } //TO else { mainSearch.animePlaytimeTo = service.ConvertToDateTimeFromYYYYMM(PlaytimeToBox.Text.ToString()); mainSearch.animePlaytimeSearchRule = SearchModule.DateTimeSearchRule.To; } } } //声优 if (!string.IsNullOrEmpty(seiyuuBox.Text.ToString())) { mainSearch.CVName = seiyuuBox.Text.ToString(); mainSearch.CVNameSearchWay = mainSearch.GetStringSearchWay(CVNameSearchTypeButton.Text.ToString()); } //制作公司 if (!string.IsNullOrEmpty(companyBox.Text.ToString())) { mainSearch.Company=companyBox.Text.ToString(); mainSearch.CompanyNameSearchWay = mainSearch.GetStringSearchWay(CompanySearchTypeButton.Text.ToString()); } //状态 #region //状态 if (StatusCheckedListBox.GetItemChecked(0)) { mainSearch.animeStatue.playing = true; } else { mainSearch.animeStatue.playing = false; } if (StatusCheckedListBox.GetItemChecked(1)) { mainSearch.animeStatue.finished = true; } else { mainSearch.animeStatue.finished = false; } if (StatusCheckedListBox.GetItemChecked(2)) { mainSearch.animeStatue.newproject = true; } else { mainSearch.animeStatue.newproject = false; } if (StatusCheckedListBox.GetItemChecked(3)) { mainSearch.animeStatue.discare = true; } else { mainSearch.animeStatue.discare = false; } #endregion //原作 #region //原作 if (OriginalCheckedListBox.GetItemChecked(0)) { mainSearch.animeOriginal.fromComic = true; } else { mainSearch.animeOriginal.fromComic = false; } if (OriginalCheckedListBox.GetItemChecked(1)) { mainSearch.animeOriginal.fromNovel = true; } else { mainSearch.animeOriginal.fromNovel = false; } if (OriginalCheckedListBox.GetItemChecked(2)) { mainSearch.animeOriginal.isoriginal = true; } else { mainSearch.animeOriginal.isoriginal = false; } if (OriginalCheckedListBox.GetItemChecked(3)) { mainSearch.animeOriginal.fromgame = true; } else { mainSearch.animeOriginal.fromgame = false; } if (OriginalCheckedListBox.GetItemChecked(4)) { mainSearch.animeOriginal.fromvideo = true; } else { mainSearch.animeOriginal.fromvideo = false; } if (OriginalCheckedListBox.GetItemChecked(5)) { mainSearch.animeOriginal.fromothers = true; } else { mainSearch.animeOriginal.fromothers = false; } #endregion }
/// <summary> /// 搜索按钮 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void SearchButton_Click(object sender, EventArgs e) { //字符格式检查 int StrCheckRes = StringFormatCheck(); if (StrCheckRes == -1)//编号格式不正 { MsgBox.Show(MSG_COMMON_004, AnimeNoBox.Text.ToString()); return; } else if (StrCheckRes == -2)//简写格式不正 { MsgBox.Show(MSG_COMMON_005, AnimeNNBox.Text.ToString()); return; } //格式检查 if ( !DateTimeFormatCheck() || !CheckedListBoxCheck()) { return; } SearchModule search = new SearchModule(); SetSearchModule(search); try { DataSet ds = service.Getanime(search); if (ds.Tables[0].Rows.Count == 0) { MsgBox.Show(MSG_COMMON_007); return; } Main.Mainfm.ShowAnime(ds); Main.Mainfm.Focus(); } catch (Exception ex) { MsgBox.Show(MSG_COMMON_001, ex.Message); } }
/// <summary> /// 载入动画 /// 搜索 /// </summary> /// <param name="search">搜索窗体</param> /// <returns></returns> public DataSet Getanime(SearchModule search) { return dao.Getanime(search); }