/// <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);
}