public void SalvarGrupo(ref int codGrupo, int codBolao, int codFase, string nomGrupo, string sglGrupo)
{
if (codGrupo == 0)
{
SqlStatement sql = this.Connector.BindSql(
"insert into grupo (cod_bolao, cod_fase, nom_grupo, sgl_grupo) values (?, ?, ?, ?)").
ToParam("@Bolao", codBolao).
ToParam("@Fase", codFase).
ToParam("@Nome", nomGrupo).
ToParam("@Sigla", sglGrupo);
sql.Execute();
codGrupo = Convert.ToInt32(sql.LastIdentifier("grupo", "cod_grupo"));
}
else
{
this.Connector.BindSql(
"update grupo set " +
"cod_bolao = ?, " +
"cod_fase = ?, " +
"nom_grupo = ?, " +
"sgl_grupo = ? " +
"where cod_grupo = ?").
ToParam("@Bolao", codBolao).
ToParam("@Fase", codFase).
ToParam("@Nome", nomGrupo).
ToParam("@Sigla", sglGrupo).
ToParam("@Grupo", codGrupo).
Execute();
}
}
public List <EPGInfo> GetEPGForChannel(string idChannel)
{
IFormatProvider mmddFormat = new CultureInfo(String.Empty, false);
List <EPGInfo> infos = new List <EPGInfo>();
SqlBuilder sb = new SqlBuilder(Gentle.Framework.StatementType.Select, typeof(Program));
sb.AddConstraint(Operator.Equals, "idChannel", Int32.Parse(idChannel));
DateTime thisMorning = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
sb.AddConstraint(String.Format("startTime>='{0}'", thisMorning.ToString(GetDateTimeString(), mmddFormat)));
sb.AddOrderByField(true, "startTime");
SqlStatement stmt = sb.GetStatement(true);
IList programs = ObjectFactory.GetCollection(typeof(Program), stmt.Execute());
if (programs != null && programs.Count > 0)
{
foreach (Program prog in programs)
{
EPGInfo epg = new EPGInfo();
epg.startTime = prog.StartTime;
epg.endTime = prog.EndTime;
epg.title = prog.Title;
epg.description = prog.Description;
infos.Add(epg);
}
}
return(infos);
}
public static int Salvar(Usuario usuario)
{
if (usuario.CodigoUsuario == 0)
{
SqlStatement sql = DatabaseUtil.Connector.BindSql(
"insert into usuario (nom_usuario, dsc_email, dsc_senha, dsc_apelido, dat_cadastro, dat_modificacao) values (?, ?, dbo.fn_criptografar_senha(?), ?, getdate(), getdate())").
ToParam("@Nome", usuario.NomeUsuario).
ToParam("@Email", usuario.Email).
ToParam("@Senha", usuario.Senha).
ToParam("@Apelido", usuario.Apelido);
sql.Execute();
return(Convert.ToInt32(sql.LastIdentifier("usuario", "cod_usuario")));
}
else
{
DatabaseUtil.Connector.BindSql(
"update usuario set nom_usuario = ?, dsc_email = ?, dsc_senha = dbo.fn_criptografar_senha(?), dsc_apelido = ?, dat_modificacao = getdate() where cod_usuario = ?").
ToParam("@Nome", usuario.NomeUsuario).
ToParam("@Email", usuario.Email).
ToParam("@Senha", usuario.Senha).
ToParam("@Apelido", usuario.Apelido).
ToParam("@Codigo", usuario.CodigoUsuario).
Execute();
return(usuario.CodigoUsuario);
}
}
public void TestCustomListUsingInConstraint()
{
// subselects not supported by the inferior mysql engine - skip test.
if (!Broker.ProviderName.Equals("MySQL"))
{
GentleSqlFactory sf = Broker.GetSqlFactory();
// first, select the number of expected entries
SqlBuilder sb = new SqlBuilder(StatementType.Count, typeof(MailingList));
string sql = String.Format("select distinct MemberAddress from {0}",
sf.GetTableName("ListMember"));
sb.AddConstraint(Operator.In, "SenderAddress", Broker.Execute(sql), "MemberAddress");
SqlResult sr = Broker.Execute(sb.GetStatement());
int expected = sr.Count;
// verify list retrieval (using IList data)
IList lists = MailingList.ListByCustomListConstraint();
Assert.IsNotNull(lists);
Assert.AreEqual(expected, lists.Count);
// verify same result using alternative approach (using SqlResult data)
sb = new SqlBuilder(StatementType.Select, typeof(Member));
SqlResult members = sb.GetStatement(true).Execute();
sb = new SqlBuilder(StatementType.Select, typeof(MailingList));
// use column name as indexer into SqlResult for list constraints
sb.AddConstraint(Operator.In, "SenderAddress", members, "MemberAddress");
SqlStatement stmt = sb.GetStatement(true);
lists = ObjectFactory.GetCollection(typeof(MailingList), stmt.Execute());
Assert.IsNotNull(lists);
Assert.AreEqual(expected, lists.Count);
}
}
public void SalvarFase(ref int codFase, int codBolao, string dscFase, string indTipoFase, string indStatus)
{
if (codFase == 0)
{
SqlStatement sql = this.Connector.BindSql("insert into fase (cod_bolao, dsc_fase, ind_tipo_fase, ind_status) values (?, ?, ?, ?)").
ToParam("@Bolao", codBolao).
ToParam("@Descricao", dscFase).
ToParam("@Tipo", indTipoFase).
ToParam("@Status", indStatus);
sql.Execute();
codFase = Convert.ToInt32(sql.LastIdentifier("fase", "cod_fase"));
}
else
{
this.Connector.BindSql(
"update fase set " +
"cod_bolao = ?, " +
"dsc_fase = ?, " +
"ind_tipo_fase = ?, " +
"ind_status = ? " +
"where cod_fase = ?").
ToParam("@Bolao", codBolao).
ToParam("@Descricao", dscFase).
ToParam("@Tipo", indTipoFase).
ToParam("@Status", indStatus).
ToParam("@Fase", codFase).
Execute();
}
}
/// <summary>
/// Retreives the first found instance of a Program given its Title,Start and End Times and referenced channel
/// </summary>
/// <param name="title">Title we wanna look for</param>
/// <param name="startTime">StartTime</param>
/// <param name="endTime">EndTime</param>
/// <param name="channelId">Referenced Channel id</param>
/// <returns></returns>
public static Program RetrieveByTitleTimesAndChannel(string title, DateTime startTime, DateTime endTime,
int channelId)
{
//select * from 'foreigntable'
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof(Program));
// where foreigntable.foreignkey = ourprimarykey
sb.AddConstraint(Operator.Equals, "Title", title);
sb.AddConstraint(Operator.Equals, "startTime", startTime);
sb.AddConstraint(Operator.Equals, "endTime", endTime);
sb.AddConstraint(Operator.Equals, "idChannel", channelId);
// passing true indicates that we'd like a list of elements, i.e. that no primary key
// constraints from the type being retrieved should be added to the statement
SqlStatement stmt = sb.GetStatement(true);
// execute the statement/query and create a collection of User instances from the result set
IList <Program> result = ObjectFactory.GetCollection <Program>(stmt.Execute());
if (result == null)
{
return(null);
}
if (result.Count == 0)
{
return(null);
}
return(result[0]);
// TODO In the end, a GentleList should be returned instead of an arraylist
//return new GentleList( typeof(ChannelMap), this );
}
protected override void OnPageLoad()
{
Log.Debug("zaposd pageload");
// following line should stay. Problems with OSD not
// appearing are already fixed elsewhere
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof(Channel));
sb.AddConstraint(Operator.Equals, "istv", 1);
sb.AddOrderByField(true, "sortOrder");
SqlStatement stmt = sb.GetStatement(true);
ObjectFactory.GetCollection(typeof(Channel), stmt.Execute());
AllocResources();
// if (g_application.m_pPlayer) g_application.m_pPlayer.ShowOSD(false);
ResetAllControls(); // make sure the controls are positioned relevant to the OSD Y offset
m_bNeedRefresh = false;
m_dateTime = DateTime.Now;
channelNr = GetChannelNumber();
channelName = GetChannelName();
idChannel = GetIdChannel();
SetCurrentChannelLogo();
base.OnPageLoad();
GUIPropertyManager.SetProperty("#currentmodule", GUILocalizeStrings.Get(100000 + GetID));
}
public static void SetSingleStateSeriesPending(DateTime startTime, int idChannel, string title)
{
//select * from 'foreigntable'
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof(Program));
// where foreigntable.foreignkey = ourprimarykey
sb.AddConstraint(Operator.Equals, "Title", title);
sb.AddConstraint(Operator.Equals, "startTime", startTime);
sb.AddConstraint(Operator.Equals, "idChannel", idChannel);
// passing true indicates that we'd like a list of elements, i.e. that no primary key
// constraints from the type being retrieved should be added to the statement
SqlStatement stmt = sb.GetStatement(true);
IList <Program> result = ObjectFactory.GetCollection <Program>(stmt.Execute());
if (result == null || result.Count == 0)
{
return;
}
Program prg = result[0];
prg.IsRecordingOncePending = false;
prg.IsRecordingSeriesPending = true;
prg.Persist();
}
public List <WebProgram> SearchEPG(string show)
{
List <WebProgram> infos = new List <WebProgram>();
if (!ConnectToDatabase())
{
return(infos);
}
try
{
SqlBuilder sb = new SqlBuilder(Gentle.Framework.StatementType.Select, typeof(Program));
sb.AddConstraint(Operator.Like, "title", show);
sb.AddOrderByField(true, "startTime");
SqlStatement stmt = sb.GetStatement(true);
IList programs = ObjectFactory.GetCollection(typeof(Program), stmt.Execute());
if (programs != null && programs.Count > 0)
{
foreach (Program prog in programs)
{
infos.Add(new WebProgram(prog));
}
}
}
catch (Exception)
{
}
return(infos);
}
private void InitChannels()
{
listViewChannels.Clear();
listViewChannels.BeginUpdate();
try
{
SqlBuilder sb = new SqlBuilder(Gentle.Framework.StatementType.Select, typeof(Channel));
if (checkBoxGuideChannels.Checked)
{
sb.AddConstraint(Operator.Equals, "visibleInGuide", 1);
}
sb.AddConstraint(Operator.Equals, "isTv", 1);
sb.AddOrderByField(true, "sortOrder");
sb.AddOrderByField(true, "displayName");
SqlStatement stmt = sb.GetStatement(true);
IList <Channel> channels = ObjectFactory.GetCollection <Channel>(stmt.Execute());
for (int i = 0; i < channels.Count; i++)
{
// TODO: add imagelist with channel logos from MP :)
ListViewItem curItem = new ListViewItem(channels[i].DisplayName);
curItem.Tag = channels[i];
listViewChannels.Items.Add(curItem);
}
}
finally
{
listViewChannels.EndUpdate();
}
mpButtonOk.Enabled = (listViewChannels.Items.Count > 0);
}
public List <WebProgram> GetTodayEPGForChannel(int idChannel)
{
IFormatProvider mmddFormat = new System.Globalization.CultureInfo(String.Empty, false);
List <WebProgram> infos = new List <WebProgram>();
if (!ConnectToDatabase())
{
return(infos);
}
SqlBuilder sb = new SqlBuilder(Gentle.Framework.StatementType.Select, typeof(Program));
sb.AddConstraint(Operator.Equals, "idChannel", idChannel);
DateTime thisMorning = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
sb.AddConstraint(String.Format("startTime>='{0}'", thisMorning.ToString(GetDateTimeString(), mmddFormat)));
sb.AddOrderByField(true, "startTime");
SqlStatement stmt = sb.GetStatement(true);
IList programs = ObjectFactory.GetCollection(typeof(Program), stmt.Execute());
if (programs != null && programs.Count > 0)
{
foreach (Program prog in programs)
{
infos.Add(new WebProgram(prog));
}
}
return(infos);
}
private int GetChannelIdByDisplayName(string aChannelName)
{
int channelId = -1;
if (string.IsNullOrEmpty(aChannelName))
{
return(channelId);
}
try
{
SqlBuilder sb = new SqlBuilder(Gentle.Framework.StatementType.Select, typeof(Channel));
sb.AddConstraint(Operator.Equals, "displayName", aChannelName);
sb.SetRowLimit(1);
SqlStatement stmt = sb.GetStatement(true);
IList channels = ObjectFactory.GetCollection(typeof(Channel), stmt.Execute());
if (channels.Count == 1)
{
channelId = ((Channel)channels[0]).IdChannel;
}
}
catch (Exception ex)
{
MessageBox.Show(string.Format("Could not get ChannelID for DisplayName: {0}\n{1}", aChannelName, ex.Message));
}
return(channelId);
}
public static void ResetAllStates()
{
string sql = "Update Program set state=0 where state<>0;";
SqlStatement stmt = new SqlStatement(StatementType.Update, Broker.Provider.GetCommand(), sql);
stmt.Execute();
Gentle.Common.CacheManager.ClearQueryResultsByType(typeof(Program));
}
private int GetCount(SqlBuilder builder)
{
SqlStatement statement = builder.GetStatement();
SqlResult result = statement.Execute();
int count = result.Count;
return(count);
}
/// <summary>
/// Get a list which contains token in the description
/// </summary>
private IList <Program> ContainsInGenre(string Token)
{
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof(Program));
sb.AddConstraint(Operator.Like, "genre", "%" + Token + "%");
SqlStatement stmt = sb.GetStatement(true);
return(ObjectFactory.GetCollection <Program>(stmt.Execute()));
}
private SqlResult GetRawTableData()
{
SqlBuilder sb = new SqlBuilder( StatementType.Select, typeof(PHNullableType) );
SqlStatement stmt = sb.GetStatement( true );
// override generated statement to make sure we execute this without any type association
stmt = new SqlStatement( StatementType.Select, Broker.Provider.GetCommand(), stmt.Sql );
SqlResult sr = stmt.Execute();
return sr;
}
public int PersonsCount()
{
SqlBuilder sb = new SqlBuilder(StatementType.Count, typeof(Person));
SqlStatement stmt = sb.GetStatement(true);
SqlResult sql_result = stmt.Execute();
return(sql_result.Count);
}
/// <summary>
/// Static method to retrieve all instances that are stored in the database in one call
/// </summary>
public static IList <RadioChannelGroup> ListAll()
{
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof(RadioChannelGroup));
sb.AddOrderByField(true, "sortOrder");
SqlStatement stmt = sb.GetStatement(true);
return(ObjectFactory.GetCollection <RadioChannelGroup>(stmt.Execute()));
}
/// <summary>
/// Get a list of all PersonalTVGuideMap referring to the current IdKeyword.
/// </summary>
public IList <PersonalTVGuideMap> ReferencedKeyword()
{
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof(PersonalTVGuideMap));
sb.AddConstraint(Operator.Equals, "idKeyword", IdKeyword);
SqlStatement stmt = sb.GetStatement(true);
return(ObjectFactory.GetCollection <PersonalTVGuideMap>(stmt.Execute()));
}
/// <summary>
/// Example list retrieval of objects using a custom "in" constraint.
/// </summary>
/// <returns>A list of MailingList objects</returns>
public static IList ListByCustomListConstraint()
{
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof(MailingList));
sb.AddConstraint(Operator.In, "SenderAddress", Member.ListAll, "Address");
SqlStatement stmt = sb.GetStatement(true);
return(ObjectFactory.GetCollection(typeof(MailingList), stmt.Execute()));
}
/// <summary>
/// Example list retrieval of objects using a custom "like" constraint.
/// </summary>
/// <param name="domain">A partial (right-side exact) string to match for senderaddress</param>
/// <returns>A list of MailingList objects</returns>
public static IList ListByDomain(string domain)
{
SqlBuilder sb = new SqlBuilder(StatementType.Select);
sb.AddConstraint(String.Format("SenderAddress LIKE '%{0}'", domain));
SqlStatement stmt = sb.GetStatement(StatementType.Select, typeof(MailingList), true);
return(ObjectFactory.GetCollection(typeof(MailingList), stmt.Execute()));
}
public void CleanUp()
{
if (runTests)
{
SqlBuilder sb = new SqlBuilder(StatementType.Delete, typeof(ReservedWords));
SqlStatement stmt = sb.GetStatement(true);
stmt.Execute();
}
}
public IList <Attendance> RetrieveAll()
{
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof(Attendance));
SqlStatement stmt = sb.GetStatement(true);
Attendance[] attendances =
(Attendance[])ObjectFactory.GetCollection(typeof(Attendance), stmt.Execute());
return(attendances);
}
public void RemoveAllAttendancesOfEvent(Event anEvent)
{
SqlBuilder sb = new SqlBuilder(StatementType.Delete, typeof(Attendance));
sb.AddConstraint(Operator.Equals, "id_event", anEvent.Id);
SqlStatement stmt = sb.GetStatement(true);
stmt.Execute();
}
/// <summary>
/// Get a list of Timespan referring to the current entity.
/// </summary>
public IList <Timespan> ReferringTimespan()
{
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof(Timespan));
sb.AddConstraint(Operator.Equals, "idKeyword", idKeyword);
sb.AddOrderByField("SortOrder");
SqlStatement stmt = sb.GetStatement(true);
return(ObjectFactory.GetCollection <Timespan>(stmt.Execute()));
}
public void RemoveAllAttendancesOfPerson(Person aPerson)
{
SqlBuilder sb = new SqlBuilder(StatementType.Delete, typeof(Attendance));
sb.AddConstraint(Operator.Equals, "id_person", aPerson.Id);
SqlStatement stmt = sb.GetStatement(true);
stmt.Execute();
}
/// <summary>
/// Static method to retrieve all instances that are stored in the database in one call
/// </summary>
public static IList <Recording> ListAllActive()
{
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof(Recording));
sb.AddConstraint(Operator.Equals, "isRecording", true);
SqlStatement stmt = sb.GetStatement(true);
// execute the statement/query and create a collection of User instances from the result set
return(ObjectFactory.GetCollection <Recording>(stmt.Execute()));
}
private void LoadDirectory()
{
GUIControl.ClearControl(GetID, listPriorities.GetID);
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof(Schedule));
sb.AddOrderByField(false, "priority");
SqlStatement stmt = sb.GetStatement(true);
IList itemlist = ObjectFactory.GetCollection(typeof(Schedule), stmt.Execute());
int total = 0;
foreach (Schedule rec in itemlist)
{
if (rec.IsSerieIsCanceled(rec.StartTime, rec.IdChannel))
{
continue;
}
GUIListItem item = new GUIListItem();
item.Label = String.Format("{0}.{1}", total, rec.ProgramName);
item.TVTag = rec;
string strLogo = Utils.GetCoverArt(Thumbs.TVChannel, rec.ReferencedChannel().DisplayName);
if (string.IsNullOrEmpty(strLogo))
{
strLogo = "defaultVideoBig.png";
}
TvServer server = new TvServer();
VirtualCard card;
if (server.IsRecordingSchedule(rec.IdSchedule, out card))
{
if (rec.ScheduleType != (int)ScheduleRecordingType.Once)
{
item.PinImage = Thumbs.TvRecordingSeriesIcon;
}
else
{
item.PinImage = Thumbs.TvRecordingIcon;
}
}
else if (rec.ReferringConflicts().Count > 0)
{
item.PinImage = Thumbs.TvConflictRecordingIcon;
}
item.ThumbnailImage = strLogo;
item.IconImageBig = strLogo;
item.IconImage = strLogo;
listPriorities.Add(item);
total++;
}
//set object count label
GUIPropertyManager.SetProperty("#itemcount", Utils.GetObjectCountLabel(total));
GUIControl.SelectItemControl(GetID, listPriorities.GetID, m_iSelectedItem);
}
private SqlResult GetRawTableData()
{
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof(PHNullableType));
SqlStatement stmt = sb.GetStatement(true);
// override generated statement to make sure we execute this without any type association
stmt = new SqlStatement(StatementType.Select, Broker.Provider.GetCommand(), stmt.Sql);
SqlResult sr = stmt.Execute();
return(sr);
}
public static IList <Program> RetrieveAllNotifications()
{
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof(Program));
//sb.AddConstraint("state % 2 <> 0"); //odd state numbers are notifications
string notify = Convert.ToString((int)ProgramState.Notify);
sb.AddConstraint("state & " + notify + " = " + notify);
SqlStatement stmt = sb.GetStatement(true);
// execute the statement/query and create a collection of User instances from the result set
return(ObjectFactory.GetCollection <Program>(stmt.Execute()));
}
public virtual void TestSqlOverride()
{
if (!Broker.ProviderName.Equals("PostgreSQL"))
{
SqlBuilder sql = new SqlBuilder(StatementType.Select, typeof(MailingList));
SqlStatement statement = sql.GetStatement(true);
ObjectMap map = ObjectFactory.GetMap(null, typeof(MailingList));
statement.Command.CommandText = String.Format("select * from {0}", map.TableName);
SqlResult result = statement.Execute();
IList list = ObjectFactory.GetCollection(typeof(MailingList), result);
Assert.AreEqual(3, list.Count);
}
}
public override LastInsertId ExecuteInsert(SqlStatement sql, string idKey)
{
if (idKey != null)
{
sql.Sql += ";SELECT last_insert_rowid()";
using (sql)
{
var rez = sql.ExecuteScalar();
return new LastInsertId(rez);
}
}
sql.Execute();
return LastInsertId.Empty;
}
public override LastInsertId ExecuteInsert(SqlStatement sql, string idKey)
{
//sql.Sql += ";Select @@IDENTITY as id";
using (sql)
{
sql.Execute();
using (var idquery = new SqlStatement(sql.Db))
{
idquery.SetSql("select @@IDENTITY as id");
var rez = idquery.ExecuteScalar();
return new LastInsertId(rez);
}
}
}
public override LastInsertId ExecuteInsert(SqlStatement sql, string idKey)
{
IDbDataParameter param = null;
if (!string.IsNullOrEmpty(idKey))
{
sql.Sql += string.Format(" returning {0} into :newid", EscapeName(idKey));
var cmd = sql.Command;
param = cmd.CreateParameter();
param.ParameterName = ":newid";
param.Value = DBNull.Value;
param.Direction = ParameterDirection.ReturnValue;
param.DbType = DbType.Int64;
cmd.Parameters.Add(param);
}
using (sql)
{
sql.Execute();
if (param == null) return LastInsertId.Empty;
return new LastInsertId(param.Value);
}
}
