//先获取 XM_SongId
public List <string> GetTaskSongIdList()
{
SqlDataReader reader = null;
List <string> xm_SongidList = new List <string>();
try
{
reader = HelperSQL.ExecuteReader("usp_XM_Spider_select_lyricTask", null, System.Data.CommandType.StoredProcedure);
while (reader.Read())
{
string xm_songId = reader["XM_SOngId"].ToString();
xm_SongidList.Add(xm_songId);
}
}
catch (Exception ex)
{
LogNet.LogBLL.Error("GetTaskSongIdList", ex);
}
finally
{
if (reader != null)
{
reader.Close();
}
}
return(xm_SongidList);
}
public int InsertCollectRank(object[] array)
{
int result = 0;
try
{
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("XM_SongId", array[0]),
new SqlParameter("SongName", array[1]),
new SqlParameter("SingerName", array[2]),
new SqlParameter("CollectName", array[3]),
new SqlParameter("Tags", array[4]),
new SqlParameter("CrawType", array[5]),
new SqlParameter("PlayTimes", array[6]),
new SqlParameter("RecommendTimes", array[7]),
new SqlParameter("CollectTimes", array[8])
};
result = HelperSQL.ExecNonQuery("usp_XMSpiderCollect_Insert_CollectRank", parms);
}
catch (Exception ex)
{
LogBLL.Error("InsertCollectRank", ex);
}
return(result);
}
public IList <HttpWrongInfo> GetHttpWrongBySpiderTimeId(int spiderTimeId)
{
IDataReader reader = null;
List <HttpWrongInfo> WrongInfoList = new List <HttpWrongInfo>();
try
{
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("spiderTimeId", spiderTimeId)
};
reader = HelperSQL.ExecuteReader("usp_XM_Select_HttpWrongBySpiderTimeId", parms, CommandType.StoredProcedure);
while (reader.Read())
{
HttpWrongInfo httpWrongInfo = new HttpWrongInfo();
httpWrongInfo.WrongId = Convert.ToInt32(reader["WrongId"]);
httpWrongInfo.YearMonthId = Convert.ToInt32(reader["YearMonthId"]);
httpWrongInfo.AlbumUrl = reader["AlbumUrl"].ToString();
WrongInfoList.Add(httpWrongInfo);
}
return(WrongInfoList);
}
catch (Exception ex)
{
LogNet.LogBLL.Error("GetHttpWrongBySpiderTimeId", ex);
return(WrongInfoList);
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
public List <int> GetCollectCateId(int startId, int endId)
{
string sql = "select KeyId from dbo.CollectCate where id>=" + startId + " and id<=" + endId;
SqlDataReader reader = null;
List <int> cateIdList = new List <int>();
try
{
reader = HelperSQL.ExecuteReader(sql);
while (reader.Read())
{
int keyId = Convert.ToInt32(reader["KeyId"]);
cateIdList.Add(keyId);
}
}
catch (Exception)
{
throw;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
return(cateIdList);
}
public List <int> GetCateKwyIdListService()
{
string sql = "select keyId from CollectCateWork where IsFinished is null order by keyId asc";
SqlDataReader reader = null;
List <int> cateIdList = new List <int>();
try
{
reader = HelperSQL.ExecuteReader(sql);
while (reader.Read())
{
int keyId = Convert.ToInt32(reader["KeyId"]);
cateIdList.Add(keyId);
}
}
catch (Exception ex)
{
LogNet.LogBLL.Error("GetCollectCateWorkInfo", ex);
}
finally
{
if (reader != null)
{
reader.Close();
}
}
return(cateIdList);
}
public List <string> GetListSongService()
{
List <string> list = new List <string>();
SqlDataReader reader = null;
try
{
string sql = "select XM_SongId from CollectSongInfo where SongName like '%?%' or Singer like '%?%'";
reader = HelperSQL.ExecuteReader(sql, null, System.Data.CommandType.Text);
while (reader.Read())
{
list.Add(reader["XM_SongId"].ToString());
}
}
catch (Exception ex)
{
LogBLL.Error("GetCollectSongReportListService 异常", ex);
}
finally
{
if (reader != null)
{
reader.Close();
}
}
return(list);
}
/// <summary>
/// 得到没做完的所有的任务
/// </summary>
/// <param name="albumPageList"></param>
/// <param name="albumYearMonthList"></param>
public void GetUnDoneBySpiderId(int spiderTimeId, ref int startPage, ref int yearMonth)
{
IDataReader reader = null;
try
{
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("spiderTimeId", spiderTimeId)
};
reader = HelperSQL.ExecuteReader("usp_XM_Select_XM_AlbumYearMonthBySpiderTimeId", parms, CommandType.StoredProcedure);
while (reader.Read())
{
startPage = Convert.ToInt32(reader["PageIndx"]);
yearMonth = Convert.ToInt32(reader["YearMonth"]);
}
}
catch (Exception ex)
{
LogNet.LogBLL.Error("GetUnDoneBySpiderId", ex);
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
/// <summary>
/// 插入专辑-歌曲表
/// </summary>
/// <param name="albumSongInfo"></param>
/// <param name="songNameList"></param>
/// <returns></returns>
public int InsertAlbumSong(AlbumSongInfo albumSongInfo)
{
int result = 0;
try
{
for (int i = 0; i < albumSongInfo.SongNameList.Count; i++)
{
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("spiderTimeId", SpiderTimeInfo.SpiderTimeId),
new SqlParameter("songName", albumSongInfo.SongNameList[i]),
new SqlParameter("xm_songid", albumSongInfo.XM_SongIdList[i]),
new SqlParameter("songLanguage", albumSongInfo.SongLanguage),
new SqlParameter("oriRightsHolder", albumSongInfo.OriRightsHolder),
new SqlParameter("publishDate", albumSongInfo.PublishDate),
new SqlParameter("fromPage", ModelArgs.FromPage),
new SqlParameter("lyricTxt", albumSongInfo.LyricTxt),
new SqlParameter("albumId", albumSongInfo.AlbumInfo.AlbumId),
new SqlParameter("singerId", albumSongInfo.SingerInfo.SingerId)
};
result += HelperSQL.ExecNonQuery("usp_XM_Insert_XM_AlbumSong", parms, CommandType.StoredProcedure);
}
return(result);
}
catch (Exception ex)
{
LogNet.LogBLL.Error("InsertAlbumSong", ex);
return(0);
}
}
public int Update_XM_AlbumYearMonth(int yearMonthId)
{
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("yearMonthId", yearMonthId)
};
return(HelperSQL.ExecNonQuery("usp_XM_Update_XM_AlbumYearMonth", parms, CommandType.StoredProcedure));
}
public DataTable GetLrcTable_T1()
{
string sql = "select top(20) Id,XM_SongId,SongName,SingerName,AlbumName from XM_CN_EN_56876";
DataTable dt = new DataTable();
dt = HelperSQL.SelectData(sql, null, CommandType.Text);
return(dt);
}
//Id,XM_SongId,SongName,SingerName,AlbumName,PublishDate,SongLanguage,OriRightsHolder,SpiderTimeId,FlowStatus,Lyricist,Composer,LrcTxt,Lyric
public DataTable GetLrcTable()
{
string sql = "select top(20) Id,XM_SongId,SongName,SingerName,AlbumName,PublishDate,SongLanguage,OriRightsHolder,Lyricist,Composer,LrcTxt,Lyric from XM_CN_EN_56876";
DataTable dt = new DataTable();
dt = HelperSQL.SelectData(sql, null, CommandType.Text);
return(dt);
}
public int GetMaxKeyId(int num)
{
//得到 KeyId的 范围值
string sql = "select max(keyId) from dbo.CollectCateWork_Row where RowId <= " + 330 * num;
object objResult = HelperSQL.ExecScalar(sql);
int result = Convert.ToInt32(objResult);
return(result);
}
public void UpdateCateDate(int keyId, int collectCount)
{
try
{
string sql = "update CollectCate set CollectCount='" + collectCount + "' , CreateDate='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' where KeyId =" + keyId;
HelperSQL.ExecNonQuery(sql);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
public int Update_XM_HttpWrongByWrongId(HttpWrongInfo httpWrongInfo)
{
try
{
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("wrongId", httpWrongInfo.WrongId)
};
return(HelperSQL.ExecNonQuery("usp_XM_Update_HttpWrongByWrongId", parms, CommandType.StoredProcedure));
}
catch (Exception ex)
{
LogNet.LogBLL.Error("Update_XM_HttpWrongByWrongId", ex);
return(0);
}
}
public int Insert_XM_AlbumPage(int yearMonthId)
{
try
{
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("yearMonthId", yearMonthId)
};
return(HelperSQL.ExecNonQuery("usp_XM_Insert_XM_AlbumPage", parms, CommandType.StoredProcedure));
}
catch (Exception ex)
{
LogNet.LogBLL.Error("Insert_XM_AlbumPage", ex);
return(0);
}
}
public int GetAlbumYearMonthDoneCount(int spiderTimeId)
{
try
{
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("spiderTimeId", spiderTimeId)
};
object obj = HelperSQL.ExecScalar("usp_XM_Select_XM_AlbumYearMonthDoneCount", parms, CommandType.StoredProcedure);
return(Convert.ToInt32(obj));
}
catch (Exception ex)
{
LogNet.LogBLL.Error("GetAlbumYearMonthDoneCount", ex);
return(0);
}
}
public int Update_XM_SpiderTime_A_AlbumStartArgs(int spiderTimeId)
{
try
{
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("spiderTimeId", spiderTimeId)
};
object obj = HelperSQL.ExecScalar("usp_XM_Update_XM_SpiderTime_A_AlbumStartArgs", parms, CommandType.StoredProcedure);
return(Convert.ToInt32(obj));
}
catch (Exception ex)
{
LogNet.LogBLL.Error("Update_XM_SpiderTime_A_AlbumStartArgs", ex);
return(0);
}
}
public int Insert_XM_HttpWrong(HttpWrongInfo httpWrongInfo)
{
try
{
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("spiderTimeId", httpWrongInfo.SpiderTimeId),
new SqlParameter("yearMonthId", httpWrongInfo.YearMonthId),
new SqlParameter("albumUrl", httpWrongInfo.AlbumUrl)
};
return(HelperSQL.ExecNonQuery("usp_XM_Insert_HttpWrong", parms, CommandType.StoredProcedure));
}
catch (Exception ex)
{
LogNet.LogBLL.Error("Insert_XM_HttpWrong", ex);
return(0);
}
}
/// <summary>
/// 更新,当 pageInfo.PageIndex 与 pageInfo.TotalPage 相等的时候 XM_AlbumYearMonth 流程同时更新为完成状态
/// </summary>
/// <param name="albumPageInfo"></param>
/// <returns></returns>
public int Update_XM_AlbumPage_A_AlbumYearMonth(int yearMonthId, int pageIndex, bool isFinished)
{
try
{
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("yearMonthId", yearMonthId),
new SqlParameter("pageIndex", pageIndex),
new SqlParameter("isfinished", isFinished)
};
return(HelperSQL.ExecNonQuery("usp_XM_Update_XM_AlbumPage_A_AlbumYearMonth", parms, CommandType.StoredProcedure));
}
catch (Exception ex)
{
LogNet.LogBLL.Error("Update_XM_AlbumPage_A_AlbumYearMonth", ex);
return(0);
}
}
public int UpdateCollectCateWorkService(int keyId, int isfinished)
{
int result = 0;
try
{
string sql = string.Format("update CollectCateWork set IsFinished='{0}',UpdateDate = '{1}' where keyId ='{2}'", isfinished, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), keyId);
result = HelperSQL.ExecNonQuery(sql);
}
catch (Exception ex)
{
LogBLL.Error("UpdateCollectCateWork\t KeyId=" + keyId, ex);
Console.WriteLine(ex.Message);
}
return(result);
}
public List <CollectSongReport> GetCollectSongReportListService(int fileNum, int size)
{
List <CollectSongReport> list = new List <CollectSongReport>();
SqlDataReader reader = null;
try
{
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("filenum", fileNum),
new SqlParameter("size", size)
};
reader = HelperSQL.ExecuteReader("usp_XM_Spider_exportCollectSOngReport", parms, System.Data.CommandType.StoredProcedure);
while (reader.Read())
{
CollectSongReport song = new CollectSongReport();
//KeyId,CateName,ShowCollectId,ShowCollectName,Tags,SongName,Singer,XM_SongId
song.KeyId = reader["KeyId"].ToString();
song.CateName = reader["CateName"].ToString();
song.ShowCollectId = reader["ShowCollectId"].ToString();
song.ShowCollectName = reader["ShowCollectName"].ToString();
song.Tags = reader["Tags"].ToString();
song.SongName = reader["SongName"].ToString();
song.Singer = reader["Singer"].ToString();
song.XM_SongId = reader["XM_SongId"].ToString();
list.Add(song);
}
}
catch (Exception ex)
{
LogBLL.Error("GetCollectSongReportListService 异常", ex);
}
finally
{
if (reader != null)
{
reader.Close();
}
}
return(list);
}
/// <summary>
/// 得到歌手Id
/// </summary>
/// <param name="singerInfo"></param>
/// <returns></returns>
public int GetSingerId(SingerInfo singerInfo)
{
try
{
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("singerName", singerInfo.SingerName),
new SqlParameter("singerdesc", singerInfo.SingerDesc),
new SqlParameter("singeraddress", singerInfo.SingerAddress),
new SqlParameter("singerxmid", singerInfo.SingerXmId)
};
object obj = HelperSQL.ExecScalar("usp_XM_Insert_XM_Singer", parms, CommandType.StoredProcedure);
return(Convert.ToInt32(obj));
}
catch (Exception ex)
{
LogNet.LogBLL.Error("GetSingerId", ex);
return(0);
}
}
public int InsertCollectSongInfo(CollectSongInfo collectSongInfo)
{
int result = 0;
try
{
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("showCollectId", collectSongInfo.ShowCollectId),
new SqlParameter("xm_SongId", collectSongInfo.XM_SongId),
new SqlParameter("songName", collectSongInfo.SongName),
new SqlParameter("singer", collectSongInfo.Singer)
};
result = HelperSQL.ExecNonQuery("usp_XMSpiderCollect_Insert_CollectSongInfo", parms);
}
catch (Exception ex)
{
LogBLL.Error("InsertCollectSongInfo", ex);
}
return(result);
}
public int Check_XM_IsFinished()
{
try
{
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("startyear", AlbumStartArgsInfo.StartYear),
new SqlParameter("startmonth", AlbumStartArgsInfo.StartMonth),
new SqlParameter("endyear", AlbumStartArgsInfo.EndYear),
new SqlParameter("endmonth", AlbumStartArgsInfo.EndMonth),
new SqlParameter("spiderType", SpiderTimeInfo.SpiderType)
};
object obj = HelperSQL.ExecScalar("usp_XM_Check_XM_IsFinished", parms, CommandType.StoredProcedure);
return(Convert.ToInt32(obj));
}
catch (Exception ex)
{
LogNet.LogBLL.Error("Check_XM_IsFinished", ex);
return(0);
}
}
public int InsertCollectCate(CollectCate collectCate)
{
int result = 0;
try
{
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("keyId", collectCate.KeyId),
new SqlParameter("lId", collectCate.LId),
new SqlParameter("cateName", collectCate.CateName),
new SqlParameter("pageIndex", collectCate.PageIndex),
};
result = HelperSQL.ExecNonQuery("usp_XMSpiderCollect_Insert_CollectCate", parms);
}
catch (Exception ex)
{
LogBLL.Error("InsertCollectCate", ex);
}
return(result);
}
public int UpdateSongNameByXm_SongIdService(string xm_songId, string songname, string singer)
{
int result = 0;
try
{
// update CollectSongInfo set SongName=N'',Singer =N'' where XM_SongId = '1769129596'
string sql = "update CollectSongInfo set SongName=N'" + songname + "',Singer =N'" + singer + "' where XM_SongId = @xm_songId ";
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("xm_songId", singer)
};
result = HelperSQL.ExecNonQuery(sql, parms, System.Data.CommandType.Text);
}
catch (Exception ex)
{
LogBLL.Error("UpdateSongNameByXm_SongId 异常", ex);
}
return(result);
}
/// <summary>
/// 得到专辑ID
/// </summary>
/// <param name="albumInfo"></param>
/// <returns></returns>
public int GetAlbumId(AlbumInfo albumInfo)
{
try
{
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("albumname", albumInfo.AlbumName),
new SqlParameter("albumsinger", albumInfo.AlbumSinger),
new SqlParameter("albumdesc", albumInfo.AlbumDesc),
new SqlParameter("albumstyle", albumInfo.AlbumStyle),
new SqlParameter("albumtype", albumInfo.AlbumType),
new SqlParameter("albumxmid", albumInfo.AlbumXmId),
};
object obj = HelperSQL.ExecScalar("usp_XM_Insert_XM_AlbumTB", parms, CommandType.StoredProcedure);
return(Convert.ToInt32(obj));
}
catch (Exception ex)
{
LogNet.LogBLL.Error("GetAlbumId", ex);
return(0);
}
}
public int UpdateXM_CN_EN_56876Service(LrcSongInfo lyricInfo)
{
int result = 0;
try
{
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("xm_songid", lyricInfo.XM_SongId),
new SqlParameter("lyricist", lyricInfo.Lyricist),
new SqlParameter("composer", lyricInfo.Composer),
new SqlParameter("lrctxt", lyricInfo.LrcText),
new SqlParameter("lyric", lyricInfo.Lyric)
};
result = HelperSQL.ExecNonQuery("usp_XM_Spider_updateLrcBySongId", parms, System.Data.CommandType.StoredProcedure);
}
catch (Exception ex)
{
LogNet.LogBLL.Error("UpdateXM_CN_EN_56876Service", ex);
}
return(result);
}
public int InsertLayerCollect(LayerCollect layerCollect)
{
int result = 0;
try
{
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("keyId", layerCollect.KeyId),
new SqlParameter("pageIndex", layerCollect.PageIndex),
new SqlParameter("showCollectId", layerCollect.ShowCollectId),
new SqlParameter("showCollectName", layerCollect.ShowCollectName),
new SqlParameter("tags", layerCollect.Tags),
new SqlParameter("playTimes", layerCollect.PalyTimes),
new SqlParameter("recommendTimes", layerCollect.RecommendTimes),
new SqlParameter("collectTimes", layerCollect.CollectTimes)
};
result = HelperSQL.ExecNonQuery("usp_XMSpiderCollect_Insert_LayerCollect", parms);
}
catch (Exception ex)
{
LogBLL.Error("InsertLayerCollect", ex);
}
return(result);
}
private void Registrar()
{
HelperSQL help = new HelperSQL();
help.GravarRepository(this);
}
