public static DataTable GetMissedGameInfo()
{
return(SqlServerHelper.ExecuteDataTable("select * from GameInfo\r\nWHERE len(LTRIM(RTRIM(final))) = 0"));
}
/// <summary>
/// 获取某一用户信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static DataTable GetUserInfoByIdDataAccess(string id)
{
var sql = $"select customerid, {USERFIELDNAME} from {USERTABLENAME} where customerid = {id}";
return(SqlServerHelper.GetDataFromKtvdb(sql));
}
/// <summary>
/// 判断是否有此用户
/// </summary>
/// <param name="customerTel"></param>
/// <returns></returns>
public static int HasExistUserDataAccess(string customerTel)
{
var sql = $"select count(customertel) from {USERTABLENAME} where customertel = '{customerTel}'";
return(int.Parse(SqlServerHelper.GetDataFromKtvdb(sql).Rows[0][0].ToString()));
}
/// <summary>
/// 添加流水日志
/// </summary>
/// <param name="roomTask"></param>
/// <returns></returns>
public static int AddConsumeLogDataAccess(RoomTask roomTask)
{
var sql = $"insert into {CONSUMELOGTABLENAME}({CONSUMELOGFIELDNAME}) values('{roomTask.RoomId}', '{roomTask.CustomerId}', '{roomTask.RoomConsume}', '{roomTask.StartTime}', '{roomTask.EndTime}')";
return(SqlServerHelper.ExecuteNonQuery(CommandType.Text, sql, 30, null));
}
public static DataTable GetAllRoomTaskDataAccess()
{
var sql = $"select {FIELDNAME} from {TABLENAME}";
return(SqlServerHelper.GetDataFromKtvdb(sql));
}
public static int ExecuteNonQuery(string cmdText, params SqlParameter[] commandParameters)
{
return(SqlServerHelper.ExecuteNonQuery(SqlServerHelper.default_connection_str, CommandType.Text, cmdText, commandParameters));
}
public static SqlDataReader ExecuteReader(string cmdText, params SqlParameter[] commandParameters)
{
return(SqlServerHelper.ExecuteReader(SqlServerHelper.default_connection_str, CommandType.Text, cmdText, commandParameters));
}
public static DataTable GetAllSongInfoDataAccess()
{
var sql = $"select id, {INVERTFIELDNAME} from {TABLENAME} order by musicname";
return(SqlServerHelper.GetDataFromKtvdb(sql));
}
public static DataTable GetSongInfoBySingerIdDataAccess(string singerId)
{
var sql = $"select id, {FIELDNAME} from {TABLENAME} where singerid = '{singerId}'";
return(SqlServerHelper.GetDataFromKtvdb(sql));
}
public static DataTable GetNationalityInfoSourceDataAccess()
{
var sql = $"select actionname from actioninfo where groupcode = '0003'";
return(SqlServerHelper.GetDataFromKtvdb(sql));
}
public static int DeleteSongInfoDataAccess(string id)
{
var sql = $"delete from {TABLENAME} where id = {id}";
return(SqlServerHelper.ExecuteNonQuery(CommandType.Text, sql, 30, null));
}
public static int UpdateSingerInfoDataAccess(SingerInfo singerInfo)
{
var sql = $"update {TABLENAME} set singername = '{singerInfo.SingerName}', singerenglishname = '{singerInfo.SingerEnglishName}',singerothername = '{singerInfo.SingerOtherName}',singerinitials = '{singerInfo.SingerInitials}',singernationality = '{singerInfo.SingerNationality}',singerphotourl = '{singerInfo.SingerPhotoUrl}',singerclicknum = '{singerInfo.SingerClickNum}',singersex = '{singerInfo.SingerSex}',singerintroduce = '{singerInfo.SingerIntroduce}' where id = {singerInfo.Id}";
return(SqlServerHelper.ExecuteNonQuery(CommandType.Text, sql, 30, null));
}
public static DataTable GetSingerInfoByNameDataAccess(string singerName)
{
var sql = $"select id, {FIELDNAME} from {TABLENAME} where singername = '{singerName}'";
return(SqlServerHelper.GetDataFromKtvdb(sql));
}
public static DataTable GetGameInfosByDate(DateTime gameTime)
{
return(SqlServerHelper.ExecuteDataTable(string.Format("select * from GameInfo where [time] = '{0}'", (object)gameTime.ToString("yyyy-MM-dd"))));
}
public static SqlParameter CreateInputParameter(string paramName, SqlDbType dbtype, int size, object value)
{
return(SqlServerHelper.CreateParameter(ParameterDirection.Input, paramName, dbtype, size, value));
}
public static int UpdateSongeInfoDataAccess(SongInfo songInfo)
{
var sql = $"update {TABLENAME} set musicname = '{songInfo.MusicName}', singerid = '{songInfo.SingerId}', singername = '{songInfo.SingerName}', languagetype = '{songInfo.LanguageType}',category = '{songInfo.Category}',mvurl = '{songInfo.MVUrl}',musicnameinitials = '{songInfo.MusicNameInitials}',singrail = '{songInfo.SingRail}',releasedate = '{songInfo.ReleaseDate}',newsonghot = '{songInfo.NewSongHot}' where id = {songInfo.Id}";
return(SqlServerHelper.ExecuteNonQuery(CommandType.Text, sql, 30, null));
}
public static SqlParameter CreateOutputParameter(string paramName, SqlDbType dbtype, int size)
{
return(SqlServerHelper.CreateParameter(ParameterDirection.Output, paramName, dbtype, size, (object)DBNull.Value));
}
public static DataTable GetAllCategorySourceDataAccess()
{
var sql = $"select id, categoryname, fatherid from musiccategory";
return(SqlServerHelper.GetDataFromKtvdb(sql));
}
public static int ExecuteNonQueryProc(string StoredProcedureName, params SqlParameter[] commandParameters)
{
return(SqlServerHelper.ExecuteNonQuery(SqlServerHelper.default_connection_str, CommandType.StoredProcedure, StoredProcedureName, commandParameters));
}
public static DataTable GetCategorySourceByFatherIdDataAccess(string fatherId)
{
var sql = $"select id, categoryname from musiccategory where fatherid = '{fatherId}'";
return(SqlServerHelper.GetDataFromKtvdb(sql));
}
public static SqlDataReader ExecuteReaderProc(string StoredProcedureName, params SqlParameter[] commandParameters)
{
return(SqlServerHelper.ExecuteReader(SqlServerHelper.default_connection_str, CommandType.StoredProcedure, StoredProcedureName, commandParameters));
}
public static DataTable GetAllSongRecordDataAccess()
{
var sql = $"select {MusicRecordTABLENAME}.musicid, clicknum, clickdate, musicname, singerid, singername, releasedate from {MusicRecordTABLENAME}, {TABLENAME} where {MusicRecordTABLENAME}.musicid = {TABLENAME}.id";
return(SqlServerHelper.GetDataFromKtvdb(sql));
}
public static DataTable GetConsumeLogDataAccess()
{
var sql = $"select {CONSUMELOGTABLENAME}.roomid, customerid, roomconsume, starttime, endtime, roomtype from {CONSUMELOGTABLENAME},roominfo where {CONSUMELOGTABLENAME}.roomid = roominfo.roomid";
return(SqlServerHelper.GetDataFromKtvdb(sql));
}
public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
return(SqlServerHelper.ExecuteScalar(SqlServerHelper.default_connection_str, cmdType, cmdText, commandParameters));
}
public static DataTable GetRoomPriceSourceDataAccess()
{
var sql = $"select roomtype, roomprice, starttime, endtime from roompriceinfo";
return(SqlServerHelper.GetDataFromKtvdb(sql));
}
public static DataTable ReadTable(string cmdText, params SqlParameter[] commandParameters)
{
return(SqlServerHelper.ReadTable(CommandType.Text, cmdText, commandParameters));
}
/// <summary>
/// 插入某一用户信息
/// </summary>
/// <param name="customerInfo"></param>
/// <returns></returns>
public static int InsertUserInfoDataAccess(CustomerInfo customerInfo)
{
var sql = $"insert into {USERTABLENAME}({USERFIELDNAME}) values ('{customerInfo.CustomerName}','{customerInfo.CustomerSex}','{customerInfo.CustomerTel}')";
return(SqlServerHelper.ExecuteNonQuery(CommandType.Text, sql, 30, null));
}
public static DataTable ReadTable(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
return(SqlServerHelper.ReadTable(SqlServerHelper.default_connection_str, cmdType, cmdText, commandParameters));
}
/// <summary>
/// 根据用Tel获取用户Id
/// </summary>
/// <param name="customerIdCard"></param>
/// <returns></returns>
public static string GetCustomerIdByTelDataAccess(string customerTel)
{
var sql = $"select customerid from {USERTABLENAME} where customertel = '{customerTel}'";
return(SqlServerHelper.GetDataFromKtvdb(sql).Rows[0][0].ToString());
}
public static DataTable GetSumStatistics(int teamId, List <int> gameIds)
{
return(SqlServerHelper.ExecuteDataTable(string.Format("select SUM(FGM),SUM(FGA),SUM([3FGM]),SUM([3FGA]),SUM(FTM),SUM(FTA),SUM(OREB),SUM(DREB),SUM(REB),SUM(AST),SUM(STL),SUM(BLK),SUM(TOV),SUM(PF),COUNT(*) \r\nfrom dbo.GameStats\r\nwhere game_id in ({1})\r\nand team_id={0}", (object)teamId, (object)string.Join <int>(",", (IEnumerable <int>)gameIds))));
}