/// <summary>
/// 获取声优履历
/// </summary>
/// <param name="cvInfo">CV信息</param>
/// <returns></returns>
public DataSet GetCVHist(CV cvInfo)
{
string sqlcmd = @"SELECT CVT.CV_NAME,
CRT.CV_ID,
CRT.CHARACTER_NO,
CRT.CHARACTER_NAME,
CRT.LEADING_FLG,
ANT.ANIME_NO,
ANT.ANIME_CHN_NAME,
ANT.ANIME_JPN_NAME
FROM
{0} CRT
INNER JOIN {1} CVT ON CVT.CV_ID = CRT.CV_ID AND CVT.ENABLE_FLG = 1
INNER JOIN {2} ANT ON ANT.ANIME_NO = CRT.ANIME_NO AND ANT.ENABLE_FLG = 1
WHERE CRT.ENABLE_FLG = 1
AND CRT.CV_ID = @cvID";
Collection<DbParameter> paras = new Collection<DbParameter>();
paras.Add(new SqlParameter("@cvid",cvInfo.ID));
return DbCmd.DoSelect(string.Format(sqlcmd,
CommonConst.TableName.T_CHARACTER_TBL,
CommonConst.TableName.T_CV_TBL,
CommonConst.TableName.T_ANIME_TBL), paras);
}
/// <summary>
/// 插入声优信息
/// </summary>
/// <param name="cvInfo"></param>
/// <returns></returns>
public bool Insert(CV cvInfo)
{
StringBuilder cmd1 = new StringBuilder();
StringBuilder cmd2 = new StringBuilder();
StringBuilder sqlcmd = new StringBuilder();
Collection<DbParameter> paras = new Collection<DbParameter>();
if (cvInfo.Gender != null)
{
cmd1.Append(",CV_GENDER");
cmd2.Append(",@cvgender");
paras.Add(new SqlParameter("@cvgender", cvInfo.Gender));
}
if (cvInfo.Brithday != DateTime.MinValue && cvInfo.Brithday != DateTime.MaxValue && cvInfo.Brithday != null)
{
cmd1.Append(",CV_BIRTH");
cmd2.Append(",@cvbirth");
paras.Add(new SqlParameter("@cvbirth", cvInfo.Brithday));
}
sqlcmd.Append(@"INSERT INTO {0}(
CV_ID,
CV_NAME,
ENABLE_FLG,
LAST_UPDATE_DATETIME ");
sqlcmd.Append(cmd1);
sqlcmd.Append(@")
VALUES(
@cvid,
@cvname,
1,
GETDATE() ");
sqlcmd.Append(cmd2);
sqlcmd.Append(")");
paras.Add(new SqlParameter("@cvid", cvInfo.ID));
paras.Add(new SqlParameter("@cvname", cvInfo.Name));
DbCmd.DoCommand(string.Format(sqlcmd.ToString(), CommonConst.TableName.T_CV_TBL), paras);
return true;
}
/// <summary>
/// 根据声优名返回声优ID、新规声优信息
/// </summary>
/// <param name="CVName"></param>
/// <returns></returns>
public int SetCVIDByCVName(string CVName)
{
int CVID = dao.GetCVIDByCVName(CVName);
if (CVID >= 0)
{
return CVID;
}
//新规声优作成
CV cvc = new CV();
cvc.ID = dao.GetMaxInt(1) + 1;
cvc.Name = CVName;
//CV表插入
try
{
cvc.Insert();
return cvc.ID;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 更新声优信息
/// </summary>
/// <param name="cvInfo"></param>
public void UpdateCVInfo(CV cvInfo)
{
StringBuilder cmd1 = new StringBuilder();
StringBuilder sqlcmd = new StringBuilder();
Collection<DbParameter> paras = new Collection<DbParameter>();
sqlcmd.Append( @"UPDATE {0} SET
CV_NAME = @cvname");
if (cvInfo.Gender != null)
{
cmd1.Append(",CV_GENDER = @cvgender");
SqlParameter para = new SqlParameter("@cvgender", cvInfo.Gender);
paras.Add(para);
}
if (cvInfo.Brithday != DateTime.MinValue && cvInfo.Brithday != DateTime.MaxValue && cvInfo.Brithday != null)
{
cmd1.Append(",CV_BIRTH = @cvbirth");
SqlParameter para = new SqlParameter("@cvbirth", cvInfo.Brithday);
paras.Add(para);
}
cmd1.Append(",LAST_UPDATE_DATETIME = GETDATE() ");
sqlcmd.Append(cmd1);
sqlcmd.Append(@" WHERE CV_ID =@cvid ");
SqlParameter para1 = new SqlParameter("@cvname",cvInfo.Name );
SqlParameter para2 = new SqlParameter("@cvid", cvInfo.ID);
paras.Add(para1);
paras.Add(para2);
DbCmd.DoCommand(string.Format(sqlcmd.ToString(),
CommonConst.TableName.T_CV_TBL), paras);
}
/// <summary>
/// 更新声优信息
/// </summary>
/// <param name="cvInfo"></param>
public void UpdateCVInfo(CV cvInfo)
{
dao.UpdateCVInfo(cvInfo);
}
/// <summary>
/// 获取声优履历
/// </summary>
/// <param name="cvInfo">声优信息</param>
/// <returns></returns>
public DataSet GetCVHist(CV cvInfo)
{
return dao.GetCVHist(cvInfo);
}
/// <summary>
/// 载入声优履历
/// </summary>
/// <param name="cvInfo"></param>
private void ShowCVHistInfo(CV cvInfo)
{
try
{
CVHistdataGridView.Rows.Clear();
var targetList = from cvhist in this.cvHist
where cvhist.CVID == cvInfo.ID
orderby cvhist.AnimeNo,cvhist.CharacterName
select cvhist;
foreach (CVHistRow dr in targetList)
{
DataGridViewRow dgvr = CVHistdataGridView.Rows[CVHistdataGridView.Rows.Add()];
dgvr.Cells[CHARACLN].Value = dr.CharacterName;
dgvr.Cells[ANIMECLN].Value = dr.AnimeCNName;
dgvr.Cells[ANIMENOCLN].Value = dr.AnimeNo;
dgvr.Cells[ISMAINCLN].Value = service.GetMainCharaStringByBool(dr.IsMainCharacter);
}
}
catch (Exception ex)
{
MsgBox.Show(MSG_COMMON_001, ex.ToString());
}
}
/// <summary>
/// 获得选中行声优ID列表
/// </summary>
/// <returns></returns>
private List<CV> GetSelectedCVs()
{
List<CV> selectedCV = new List<CV>();
foreach (DataGridViewCell sdc in cvdataGridView.SelectedCells)
{
CV cvInfo = new CV();
cvInfo.ID = Convert.ToInt32(cvdataGridView.Rows[sdc.RowIndex].Cells[0].Value);
cvInfo.Name = service.GetCVNameByCVID(cvInfo.ID);
if (!selectedCV.Contains(cvInfo))
{
selectedCV.Add(cvInfo);
}
}
return selectedCV;
}
/// <summary>
/// 获得焦点格所在行声优信息
/// </summary>
/// <returns></returns>
private CV GetSelectedCV()
{
if (cvdataGridView.CurrentCell != null)
{
CV cvInfo = new CV();
try
{
cvInfo.ID = Convert.ToInt32(cvdataGridView.Rows[cvdataGridView.CurrentCell.RowIndex].Cells[NOCLN].Value);
}
catch (Exception ex) { MsgBox.Show(MSG_COMMON_001, ex.ToString()); }
return cvInfo;
}
return null;
}
/// <summary>
/// 修改声优信息
/// </summary>
/// <returns></returns>
private bool ChangeCVInfo()
{
//Null检查
if (!CVInfoRowNullCheck())
return false;
//格式检查
if (!CVInfoFormatCheck())
return false;
//信息作成
List<CV> NeedUpdateCVList = new List<CV>();
foreach (DataGridViewRow dr in cvdataGridView.Rows)
{
CV newCvInfo = new CV();
newCvInfo.ID = Convert.ToInt32(dr.Cells[0].Value);
newCvInfo.Name = dr.Cells[1].Value.ToString();
if (dr.Cells[2].Value != null)
{
newCvInfo.Gender = service.GetGenderCharFromGenderString(dr.Cells[2].Value.ToString());
}
if (dr.Cells[3].Value != null)
{
newCvInfo.Brithday = service.ConvertToDateTimeFromYYYYMMdd(dr.Cells[3].Value.ToString());
}
var targetCV = from cvs in this.cvList
where cvs.CVID == newCvInfo.ID
select cvs;
foreach (CVListRow cvr in targetCV)
{
if (!cvr.CVName.Equals(newCvInfo.Name) || !cvr.CVGender.Trim().Equals(newCvInfo.Gender??string.Empty) || !cvr.CVBirth.Equals(newCvInfo.Brithday))
{
NeedUpdateCVList.Add(newCvInfo);
}
}
}
if (NeedUpdateCVList.Count == 0)
{
MsgBox.Show(MSG_CVMANAGE_010);
return false;
}
//修改信息
foreach (CV cvInfo in NeedUpdateCVList)
{
service.UpdateCVInfo(cvInfo);
}
return true;
}
/// <summary>
/// 添加声优信息
/// </summary>
/// <returns></returns>
private bool AddCVInfo()
{
//Null检查
if (!CVInfoRowNullCheck())
return false;
//格式检查
if (!CVInfoFormatCheck())
return false;
//既存CVID
List<int> OldCVIDs = new List<int>();
var CVs = from cvs in this.cvList
select cvs;
foreach (CVListRow i in CVs)
{
OldCVIDs.Add(i.CVID);
}
foreach (DataGridViewRow dr in cvdataGridView.Rows)
{
if (!OldCVIDs.Contains(Convert.ToInt32(dr.Cells[NOCLN].Value)))
{
//DataGridViewRow dr = cvdataGridView.Rows[cvdataGridView.Rows.Count - 1];
CV cvInfo = new CV();
cvInfo.ID = Convert.ToInt32(dr.Cells[NOCLN].Value);
cvInfo.Name = dr.Cells[NAMECLN].Value.ToString();
if (dr.Cells[GENDERCLN].Value != null)
{
cvInfo.Gender = service.GetGenderCharFromGenderString(dr.Cells[2].Value.ToString());
}
if (dr.Cells[BIRTHCLN].Value != null)
{
cvInfo.Brithday = service.ConvertToDateTimeFromYYYYMMdd(dr.Cells[3].Value.ToString());
}
//声优编号重复检查 #8:目前CVID不可见，增加方法里移除该检查
//if (!CVIDRepeatCheck(cvInfo.ID))
// return false;
try
{
cvInfo.Insert();
}
catch (Exception ex)
{
MsgBox.Show(MSG_COMMON_001, ex.ToString());
return false;
}
//目前每次只能新增一名CV
return true;
}
}
return false;
}