/// <summary>
/// 检查该ID的公司是否有使用
/// </summary>
/// <param name="companyID"></param>
/// <returns>null:未使用 重复的动画播放信息</returns>
public List<PlayInfo> IsUsedCheck(int companyID)
{
const string sqlcmd = @"SELECT
ANIME_NO,
ANIME_PLAYINFO
FROM {0}
WHERE COMPANY_ID = @companyID
ORDER BY ANIME_NO, ANIME_PLAYINFO ";
Collection<DbParameter> paras = new Collection<DbParameter>();
paras.Add(new SqlParameter("@companyID", companyID));
DataSet ds = DbCmd.DoSelect(string.Format(sqlcmd, CommonConst.TableName.T_PLAYINFO_TBL), paras);
if (ds.Tables[0].Rows.Count != 0)
{
List<PlayInfo> repeatPlayinfoList = new List<PlayInfo>();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
PlayInfo repeatInfo = new PlayInfo();
repeatInfo.animeNo = ds.Tables[0].Rows[i][0].ToString();
repeatInfo.info = ds.Tables[0].Rows[i][1].ToString();
repeatPlayinfoList.Add(repeatInfo);
}
return repeatPlayinfoList;
}
return null;
}
/// <summary>
/// 删除播放信息(伦理)
/// </summary>
/// <param name="pInfo">播放信息，仅需要ID与动画No</param>
/// <returns></returns>
public bool Delete(PlayInfo pInfo)
{
string sqlcmd = @"
UPDATE {0}
SET ENABLE_FLG = 0
,LAST_UPDATE_DATETIME = GETDATE()
WHERE PLAYINFO_ID = @playinfoID
AND ANIME_NO = @animeNo";
Collection<DbParameter> paras = new Collection<DbParameter>();
paras.Add(new SqlParameter("@playinfoID", pInfo.ID));
paras.Add(new SqlParameter("@animeNo", pInfo.animeNo));
try
{
DbCmd.DoCommand(string.Format(sqlcmd, CommonConst.TableName.T_PLAYINFO_TBL), paras);
return true;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 插入播放信息
/// </summary>
/// <param name="pInfo"></param>
/// <returns></returns>
public bool Insert(PlayInfo pInfo)
{
StringBuilder cmd1 = new StringBuilder();
StringBuilder cmd2 = new StringBuilder();
StringBuilder sqlcmd = new StringBuilder();
Collection<DbParameter> paras = new Collection<DbParameter>();
if (pInfo.startTime != DateTime.MinValue && pInfo.startTime != DateTime.MaxValue)
{
cmd1.Append(",START_TIME");
cmd2.Append(",@starttime");
paras.Add(new SqlParameter("@starttime", pInfo.startTime));
}
if (pInfo.watchedTime != DateTime.MinValue && pInfo.watchedTime != DateTime.MaxValue)
{
cmd1.Append(",WATCH_TIME");
cmd2.Append(",@watchtime");
paras.Add(new SqlParameter("@watchtime", pInfo.watchedTime));
}
if (pInfo.parts != 0)
{
cmd1.Append(",PARTS");
cmd2.Append(",@parts");
paras.Add(new SqlParameter("@parts", pInfo.parts));
}
if (pInfo.companyID != 0)
{
cmd1.Append(",COMPANY_ID");
cmd2.Append(",@company_ID");
paras.Add(new SqlParameter("@company_ID", pInfo.companyID));
}
sqlcmd.Append(@"INSERT INTO {0} (
PLAYINFO_ID
,ANIME_NO
,ANIME_PLAYINFO
,STATUS
,ENABLE_FLG
,LAST_UPDATE_DATETIME
");
sqlcmd.Append(cmd1);
sqlcmd.Append(@")
VALUES (
@id
,@animeNo
,@playinfo
,@status
,1
,GETDATE() ");
sqlcmd.Append(cmd2);
sqlcmd.Append(@")");
paras.Add(new SqlParameter("@id", pInfo.ID));
paras.Add(new SqlParameter("@playinfo", pInfo.info));
paras.Add(new SqlParameter("@animeNo", pInfo.animeNo));
paras.Add(new SqlParameter("@status", pInfo.status));
DbCmd.DoCommand(string.Format(sqlcmd.ToString(), CommonConst.TableName.T_PLAYINFO_TBL), paras);
return true;
}
/// <summary>
/// 更新播放信息
/// </summary>
/// <param name="pInfo">播放信息</param>
/// <returns></returns>
public bool Update(PlayInfo pInfo)
{
if (pInfo.ID < 0)
{
throw new Exception("播放信息ID未设置即进行更新。");
}
StringBuilder sqlcmd = new StringBuilder();
Collection<DbParameter> paras = new Collection<DbParameter>();
sqlcmd.Append(@"UPDATE {0} SET
ANIME_PLAYINFO = @playinfo
,STATUS = @status
,LAST_UPDATE_DATETIME = GETDATE()
");
paras.Add(new SqlParameter("@playinfo", pInfo.info));
paras.Add(new SqlParameter("@status", pInfo.status));
if (pInfo.startTime != DateTime.MinValue && pInfo.startTime != DateTime.MaxValue && pInfo.startTime != null)
{
sqlcmd.Append(@" ,START_TIME = @starttime ");
paras.Add(new SqlParameter("@starttime", pInfo.startTime));
}
else
{
sqlcmd.Append(@" ,START_TIME = NULL ");
}
if (pInfo.watchedTime != DateTime.MinValue && pInfo.watchedTime != DateTime.MaxValue && pInfo.watchedTime != null)
{
sqlcmd.Append(@" ,WATCH_TIME = @watchtime ");
paras.Add(new SqlParameter("@watchtime", pInfo.watchedTime));
}
else
{
sqlcmd.Append(", WATCH_TIME = NULL");
}
if (pInfo.parts != 0)
{
sqlcmd.Append(@" ,PARTS = @parts ");
paras.Add(new SqlParameter("@parts", pInfo.parts));
}
else
{
sqlcmd.Append(", PARTS = NULL");
}
if (pInfo.companyID != 0)
{
sqlcmd.Append(@" ,COMPANY_ID = @company_ID ");
paras.Add(new SqlParameter("@company_ID", pInfo.companyID));
}
else
{
sqlcmd.Append(", COMPANY_ID = NULL");
}
sqlcmd.Append(@" WHERE PLAYINFO_ID = @id
AND ANIME_NO = @animeNo");
paras.Add(new SqlParameter("@animeNo", pInfo.animeNo));
paras.Add(new SqlParameter("@id", pInfo.ID));
DbCmd.DoCommand(string.Format(sqlcmd.ToString(), CommonConst.TableName.T_PLAYINFO_TBL), paras);
return true;
}
/// <summary>
/// 通过动画No查找播放信息
/// </summary>
/// <param name="animeNo"></param>
/// <returns></returns>
public List<PlayInfo> GetPlayInfoListFromAnimeNo(string animeNo)
{
List<PlayInfo> pInfoList = new List<PlayInfo>();
string sqlcmd = @"SELECT *
FROM {0}
WHERE ANIME_NO = @animeNo
AND ENABLE_FLG = 1
ORDER BY
CASE WHEN START_TIME IS NULL THEN 1
ELSE 0
END,
START_TIME";
Collection<DbParameter> paras = new Collection<DbParameter>();
paras.Add(new SqlParameter("@animeNo", animeNo));
DataSet ds = DbCmd.DoSelect(string.Format(sqlcmd, CommonConst.TableName.T_PLAYINFO_TBL), paras);
if (ds.Tables[0].Rows.Count == 0)
{
return null;
}
foreach (DataRow dr in ds.Tables[0].Rows)
{
PlayInfo pInfo = new PlayInfo();
pInfo.animeNo = animeNo;
pInfo.ID = Convert.ToInt32(dr[0]);
pInfo.info = dr[2].ToString();
pInfo.status = Convert.ToInt32(dr[3]);
if (!Convert.IsDBNull(dr[7]))
{
pInfo.companyID = Convert.ToInt32(dr[7]);
}
if (!Convert.IsDBNull(dr[6]))
{
pInfo.parts = Convert.ToInt32(dr[6]);
}
if (!Convert.IsDBNull(dr[4]))
{
pInfo.startTime = Convert.ToDateTime(dr[4]);
}
if (!Convert.IsDBNull(dr[5]))
{
pInfo.watchedTime = Convert.ToDateTime(dr[5]);
}
pInfoList.Add(pInfo);
}
return pInfoList;
}
/// <summary>
/// 通过动画No获得播放信息列表
/// </summary>
/// <param name="animeNo"></param>
/// <returns></returns>
private List<PlayInfo> GetPlayInfoListByAnimeNo(string animeNo)
{
//获取动画信息DataTable
DataTable dt = dao.GetPlayInfoDataTableByAnimeNo(animeNo);
List<PlayInfo> playInfoList = new List<PlayInfo>();
for (int i = 1; i < dt.Rows.Count; i++)
{
PlayInfo pinfo = new PlayInfo();
pinfo.ID = Convert.ToInt32(dt.Rows[0][0]);
pinfo.info = dt.Rows[0][1].ToString();
pinfo.status = Convert.ToInt32(dt.Rows[0][2]);
if (!Convert.IsDBNull(dt.Rows[0][3]))
{
pinfo.parts = Convert.ToInt32(dt.Rows[0][3]);
}
if (!Convert.IsDBNull(dt.Rows[0][4]))
{
pinfo.companyID = Convert.ToInt32(dt.Rows[0][4]);
}
if (!Convert.IsDBNull(dt.Rows[0][5]))
{
pinfo.startTime = Convert.ToDateTime(dt.Rows[0][5]);
}
if (!Convert.IsDBNull(dt.Rows[0][6]))
{
pinfo.watchedTime = Convert.ToDateTime(dt.Rows[0][6]);
}
playInfoList.Add(pinfo);
}
return playInfoList;
}
/// <summary>
/// 添加播放信息
/// </summary>
/// <param name="pinfo"></param>
public void AddPlayInfo(PlayInfo pinfo)
{
this.playInfoList.Add(pinfo);
}
/// <summary>
/// 更新动画信息
/// </summary>
/// <param name="anime"></param>
/// <returns></returns>
public bool UpdateAnime(Animation anime)
{
#region 播放信息更新
//所有既存该动画的播放信息
string sql1 = @"SELECT PLAYINFO_ID
FROM {0}
WHERE ANIME_NO = @animeNo";
Collection<DbParameter> paras1 = new Collection<DbParameter>();
paras1.Add(new SqlParameter("@animeNo",anime.No));
DataSet ds1 = DbCmd.DoSelect(string.Format(sql1, CommonConst.TableName.T_PLAYINFO_TBL), paras1);
List<int> ToDelPlayinfoIds = new List<int>();
foreach (DataRow dr in ds1.Tables[0].Rows)
{
if (dr[0].ToString().Equals(string.Empty))
{
continue;
}
ToDelPlayinfoIds.Add(Convert.ToInt32(dr[0]));
}
//播放信息全走查确认，更新
foreach (PlayInfo pInfo in anime.playInfoList)
{
//确认播放是否存在
string sql = @"SELECT PLAYINFO_ID
FROM {0}
WHERE PLAYINFO_ID = @playinfoID
AND ANIME_NO = @animeno ";
Collection<DbParameter> paras = new Collection<DbParameter>();
paras.Add(new SqlParameter("@playinfoID", pInfo.ID));
paras.Add(new SqlParameter("@animeno", anime.No));
try
{
DataSet ds = DbCmd.DoSelect(string.Format(sql, CommonConst.TableName.T_PLAYINFO_TBL), paras);
if (ds.Tables[0].Rows.Count > 0 && Convert.ToInt32(ds.Tables[0].Rows[0][0]) == pInfo.ID)
{
pInfo.Update();
//将已更新的播放信息从待删除列表中移除
if (ToDelPlayinfoIds.Contains(pInfo.ID))
{
ToDelPlayinfoIds.Remove(pInfo.ID);
}
}
else
{
pInfo.Insert();
}
}
catch (Exception ex)
{
throw ex;
}
}
//删除更新后不存在的播放信息
foreach(int toDelPlayinfoId in ToDelPlayinfoIds)
{
PlayInfo pInfo = new PlayInfo(toDelPlayinfoId, anime.No);
pInfo.Delete();
}
#endregion
#region 角色信息更新
//所有既存该动画的角色信息
string sql2 = @"SELECT CHARACTER_NO
FROM {0}
WHERE ANIME_NO = @animeNo ";
Collection<DbParameter> paras2 = new Collection<DbParameter>();
paras2.Add(new SqlParameter("@animeNo", anime.No));
DataSet ds2 = DbCmd.DoSelect(string.Format(sql2, CommonConst.TableName.T_CHARACTER_TBL), paras2);
List<string> ToDelCharacterNos = new List<string>();
foreach (DataRow dr in ds2.Tables[0].Rows)
{
if (dr[0].ToString().Equals(string.Empty))
{
continue;
}
ToDelCharacterNos.Add(dr[0].ToString());
}
//角色信息全走查确认，更新
foreach (Character cInfo in anime.characterList)
{
string sql = @"SELECT CHARACTER_NO
FROM {0}
WHERE CHARACTER_NO = @characterNo";
Collection<DbParameter> paras = new Collection<DbParameter>();
paras.Add(new SqlParameter("@characterNo", cInfo.No));
try
{
DataSet ds = DbCmd.DoSelect(string.Format(sql, CommonConst.TableName.T_CHARACTER_TBL), paras);
if (ds.Tables[0].Rows.Count > 0 && ds.Tables[0].Rows[0][0].ToString().Equals(cInfo.No))
{
cInfo.Update();
//将已更新的角色信息从待删除列表中移除
if (ToDelCharacterNos.Contains(cInfo.No))
{
ToDelCharacterNos.Remove(cInfo.No);
}
}
else
{
cInfo.Insert();
}
}
catch (Exception ex)
{
throw ex;
}
}
//删除更新后不存在的播放信息
foreach (string toDelCharacterNo in ToDelCharacterNos)
{
Character cInfo = new Character(toDelCharacterNo);
cInfo.Delete();
}
#endregion
#region 基本信息更新
//动画信息插入
string sqlcmd = @"UPDATE {0} SET
ANIME_CHN_NAME = @animeCNName
,ANIME_JPN_NAME = @animeJPName
,ANIME_NN = @animeNickName
,STATUS = @status
,ORIGINAL = @original
,LAST_UPDATE_DATETIME = GETDATE()
WHERE ANIME_NO = @animeNo
";
Collection<DbParameter> paras0 = new Collection<DbParameter>();
paras0.Add(new SqlParameter("@animeNo", anime.No));
paras0.Add(new SqlParameter("@animeCNName", anime.CNName));
paras0.Add(new SqlParameter("@animeJPName", anime.JPName));
paras0.Add(new SqlParameter("@animeNickName", anime.Nickname));
paras0.Add(new SqlParameter("@status", anime.status));
paras0.Add(new SqlParameter("@original", anime.original));
try
{
DbCmd.DoCommand(string.Format(sqlcmd, CommonConst.TableName.T_ANIME_TBL), paras0);
return true;
}
catch (Exception ex)
{
throw ex;
}
#endregion
}