public static object DbComboMethod(Cambro.Web.DbCombo.ServerMethodArgs args)
{
DataSet dataset = new DataSet();
SqlConnection conn = new SqlConnection("Data Source=SOLO; Initial Catalog=db_spotted; Integrated Security=SSPI;");
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand("SELECT TOP " + args.Top + " NickName+NickName+NickName+NickName+NickName AS DbComboText, K AS DbComboValue FROM Usr WHERE IsSkeleton=0 AND NickName LIKE @Query ORDER BY NickName", conn);
adapter.SelectCommand.Parameters.AddWithValue("@Query", "%" + args.Query + "%");
adapter.Fill(dataset);
conn.Close();
return dataset;
}
public static object DbComboArticleObject(
Cambro.Web.DbCombo.ServerMethodArgs args)
{
int type = 0;
bool future = false;
bool attend = false;
if (args.ClientState != null)
{
if (args.ClientState["Type"] != null)
type = int.Parse(args.ClientState["Type"].ToString());
if (args.ClientState["Future"] != null)
future = true;
if (args.ClientState["Attend"] != null)
attend = true;
}
if (type == 1) //Event
{
string join = "";
if (attend)
join = " INNER JOIN UsrEventAttended ON (Event.K = UsrEventAttended.EventK AND UsrEventAttended.UsrK = " + Usr.Current.K + ") ";
string date = "";
if (future)
date = " AND Event.DateTime>=GETDATE() ";
DataView dv;
SqlConnection conn = new SqlConnection(Vars.DefaultConnectionString);
try
{
SqlCommand myCommand = new SqlCommand("select top " + Cambro.Misc.Db.PNum(args.Top) + " Event.Name+' @ '+Venue.Name+' in '+Place.Name+' ('+Country.FriendlyName+'), '+CONVERT(VarChar,Event.DateTime,3) as DbComboText, Event.K as DbComboValue from Event INNER JOIN Venue ON Event.VenueK = Venue.K INNER JOIN Place ON Venue.PlaceK=Place.K INNER JOIN Country ON Place.CountryK = Country.K " + join + " WHERE Event.Name+' @ '+Venue.Name+' in '+Place.Name+' ('+Country.FriendlyName+'), '+CONVERT(VarChar,Event.DateTime,3) like '%" + Cambro.Misc.Db.PStr(args.Query) + "%' " + date + " order by Event.Name, Venue.Name, Place.Name, Event.DateTime DESC", conn);
DataSet dataset = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = myCommand;
Bobs.Global.LogSqlQuery(Bobs.Global.QueryTypes.Select);
adapter.Fill(dataset);
dv = dataset.Tables[0].DefaultView;
}
finally
{
conn.Close();
conn.Dispose();
}
return dv;
}
else if (type == 2) //Venue
{
DataView dv;
SqlConnection conn = new SqlConnection(Vars.DefaultConnectionString);
try
{
SqlCommand myCommand = new SqlCommand("select top " + Cambro.Misc.Db.PNum(args.Top) + " Venue.Name+' in '+Place.Name+' ('+Country.FriendlyName+')' as DbComboText, Venue.K as DbComboValue from Venue INNER JOIN Place ON Venue.PlaceK=Place.K INNER JOIN Country ON Place.CountryK = Country.K WHERE Venue.Name+' in '+Place.Name+' ('+Country.FriendlyName+')' like '%" + Cambro.Misc.Db.PStr(args.Query) + "%' order by Venue.Name, Place.Name", conn);
DataSet dataset = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = myCommand;
Bobs.Global.LogSqlQuery(Bobs.Global.QueryTypes.Select);
adapter.Fill(dataset);
dv = dataset.Tables[0].DefaultView;
}
finally
{
conn.Close();
conn.Dispose();
}
return dv;
}
else if (type == 3) //Place
{
DataView dv;
SqlConnection conn = new SqlConnection(Vars.DefaultConnectionString);
try
{
SqlCommand myCommand = new SqlCommand("select top " + Cambro.Misc.Db.PNum(args.Top) + " Place.Name + ' (' + CASE WHEN Region.K IS NULL THEN '' ELSE Region.Name + ', ' END + Country.FriendlyName+')' as DbComboText, Place.K as DbComboValue from Place LEFT JOIN Region ON Place.RegionK = Region.K INNER JOIN Country ON Place.CountryK = Country.K WHERE Place.Enabled=1 AND Place.Name + ' (' + CASE WHEN Region.K IS NULL THEN '' ELSE Region.Name + ', ' END + Country.FriendlyName+')' like '%" + Cambro.Misc.Db.PStr(args.Query) + "%' order by Place.Name, Region.Name, Country.FriendlyName", conn);
DataSet dataset = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = myCommand;
Bobs.Global.LogSqlQuery(Bobs.Global.QueryTypes.Select);
adapter.Fill(dataset);
dv = dataset.Tables[0].DefaultView;
}
finally
{
conn.Close();
conn.Dispose();
}
return dv;
}
else if (type == 4) //Country
{
DataView dv;
SqlConnection conn = new SqlConnection(Vars.DefaultConnectionString);
try
{
SqlCommand myCommand = new SqlCommand("select top " + Cambro.Misc.Db.PNum(args.Top) + " Country.FriendlyName as DbComboText, Country.K as DbComboValue from Country WHERE Country.FriendlyName LIKE '%" + Cambro.Misc.Db.PStr(args.Query) + "%' ORDER BY Country.FriendlyName", conn);
DataSet dataset = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = myCommand;
Bobs.Global.LogSqlQuery(Bobs.Global.QueryTypes.Select);
adapter.Fill(dataset);
dv = dataset.Tables[0].DefaultView;
}
finally
{
conn.Close();
conn.Dispose();
}
return dv;
}
else if (type == 5) //General
{
return new Cambro.Web.DbCombo.SimpleResult(
"DbComboText", new string[] { "No need to select anything in here, you've selected 'General' above." },
"DbComboValue", new string[] { "-1" }
);
}
else
{
return new Cambro.Web.DbCombo.SimpleResult(
"DbComboText", new string[] { "Please select an article subject matter type by clicking an option above" },
"DbComboValue", new string[] { "" }
);
}
}
public static object DbComboGetMultiUsrs(Cambro.Web.DbCombo.ServerMethodArgs args)
{
string extraWhereClause = "";
string extraFromClause = "";
if (
args.ServerState != null &&
public static object DbComboGetUsrs(Cambro.Web.DbCombo.ServerMethodArgs args)
{
if (!Usr.Current.IsAdmin)
throw new Exception("Only admin");
DataView dv;
SqlConnection conn = new SqlConnection(Vars.DefaultConnectionString);
try
{
SqlCommand myCommand = new SqlCommand("select top " + Db.PNum(args.Top) + " NickName as DbComboText, K as DbComboValue from Usr where not NickName='' and NickName like '%" + Db.PStr(args.Query) + "%' order by NickName", conn);
DataSet dataset = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = myCommand;
Bobs.Global.LogSqlQuery(Bobs.Global.QueryTypes.Select);
adapter.Fill(dataset);
dv = dataset.Tables[0].DefaultView;
}
finally
{
conn.Close();
conn.Dispose();
}
return dv;
}
public static object DbComboGetUsrsPublic(Cambro.Web.DbCombo.ServerMethodArgs args)
{
DataView dv;
SqlConnection conn = new SqlConnection(Vars.DefaultConnectionString);
try
{
SqlCommand myCommand = new SqlCommand("select top " + Db.PNum(args.Top) + " NickName as DbComboText, K as DbComboValue from Usr where NickName like '%" + Db.PStr(args.Query) + "%' and not NickName = '' and not FirstName = '' and not LastName = '' and IsEmailVerified=1 order by NickName", conn);
DataSet dataset = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = myCommand;
Bobs.Global.LogSqlQuery(Bobs.Global.QueryTypes.Select);
adapter.Fill(dataset);
dv = dataset.Tables[0].DefaultView;
}
finally
{
conn.Close();
conn.Dispose();
}
return dv;
}
public static object DbComboGetGroups(Cambro.Web.DbCombo.ServerMethodArgs args)
{
DataView dv;
SqlConnection conn = new SqlConnection(Vars.DefaultConnectionString);
try
{
SqlCommand myCommand = new SqlCommand("select top " + Db.PNum(args.Top) + " [Group].[UrlName] as DbComboText, [Group].[K] as DbComboValue from [Group] where [Group].[UrlName] like '%" + Db.PStr(args.Query) + "%' AND PrivateGroupPage=0 AND PrivateChat=0 AND PrivateMemberList=0 order by [Group].[UrlName]", conn);
DataSet dataset = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = myCommand;
adapter.Fill(dataset);
dv = dataset.Tables[0].DefaultView;
}
finally
{
conn.Close();
conn.Dispose();
}
return dv;
}
internal ServerMethodArgs(string query, int top, Hashtable clientState, Cambro.Web.DbCombo.SecureHashtable serverState, bool upLevel, FieldSecurityClass fieldSecurity, ArrayList fieldSubset, DataMemberSecurityClass dataMamberSecurity, ArrayList dataMemberSubset )
{
this.query = query;
this.top = top;
this.clientState = clientState;
this.serverState = serverState;
this.upLevel = upLevel;
this.fieldSecurity = fieldSecurity;
this.fieldSubset = fieldSubset;
this.dataMemberSecurity = dataMemberSecurity;
this.dataMemberSubset = dataMemberSubset;
}
public static object DbComboGetBrandsDetail(Cambro.Web.DbCombo.ServerMethodArgs args)
{
DataView dv;
SqlConnection conn = new SqlConnection(Vars.DefaultConnectionString);
try
{
string extraSql = "";
if (args.ServerState != null && args.ServerState["NotBrandK"] != null)
{
int brandK = (int)args.ServerState["NotBrandK"];
extraSql = " AND NOT Brand.K=" + brandK + " ";
}
SqlCommand myCommand = new SqlCommand("select top " + Db.PNum(args.Top) + " Brand.Name+' (#'+CAST(Brand.K as varchar)+')' + ISNULL(CAST (' ('+Promoter.Name+')' AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS,'') as DbComboText, Brand.K as DbComboValue from Brand LEFT JOIN Promoter ON Brand.PromoterK = Promoter.K where Brand.Name like '%" + Db.PStr(args.Query) + "%' " + extraSql + " order by Brand.Name", conn);
DataSet dataset = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = myCommand;
Bobs.Global.LogSqlQuery(Bobs.Global.QueryTypes.Select);
adapter.Fill(dataset);
dv = dataset.Tables[0].DefaultView;
}
finally
{
conn.Close();
conn.Dispose();
}
return dv;
}
public static object DbComboGetPromotersWithK(Cambro.Web.DbCombo.ServerMethodArgs args)
{
if (!Usr.Current.IsAdmin)
throw new Exception("Only admin");
DataView dv;
string query = args.Query;
if (args.Query.Contains(" (K="))
query = args.Query.Substring(0, args.Query.LastIndexOf(" (K="));
SqlConnection conn = new SqlConnection(Vars.DefaultConnectionString);
try
{
SqlCommand myCommand = new SqlCommand("select top " + Db.PNum(args.Top) + " Name + ' (K=' + CONVERT(varchar(20),K) + ')' + CASE WHEN Status = 4 THEN ' *disabled' ELSE '' END as DbComboText, K as DbComboValue from Promoter where Name like '%" + Db.PStr(query) + "%' order by Name", conn);
DataSet dataset = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = myCommand;
Bobs.Global.LogSqlQuery(Bobs.Global.QueryTypes.Select);
adapter.Fill(dataset);
dv = dataset.Tables[0].DefaultView;
}
finally
{
conn.Close();
conn.Dispose();
}
return dv;
}
public static object DbComboGetVenuesNotDisabledCountryFiltered(Cambro.Web.DbCombo.ServerMethodArgs args)
{
DataView dv;
SqlConnection conn = new SqlConnection(Vars.DefaultConnectionString);
try
{
SqlCommand myCommand = new SqlCommand("select top " + Db.PNum(args.Top) + " Venue.Name+' in '+Place.Name as DbComboText, Venue.K as DbComboValue from Venue INNER JOIN Place ON Venue.PlaceK=Place.K where Venue.Name+' in '+Place.Name like '%" + Db.PStr(args.Query) + "%' AND " + Country.PlaceFilterSqlString + " order by Venue.Name", conn);
DataSet dataset = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = myCommand;
Bobs.Global.LogSqlQuery(Bobs.Global.QueryTypes.Select);
adapter.Fill(dataset);
dv = dataset.Tables[0].DefaultView;
}
finally
{
conn.Close();
conn.Dispose();
}
return dv;
}
public static object DbComboGetBrands(Cambro.Web.DbCombo.ServerMethodArgs args)
{
DataView dv;
SqlConnection conn = new SqlConnection(Vars.DefaultConnectionString);
try
{
string extraSql = "";
if (args.ServerState != null && args.ServerState["NotBrandK"] != null)
{
int brandK = (int)args.ServerState["NotBrandK"];
extraSql = " AND NOT Brand.K=" + brandK + " ";
}
SqlCommand myCommand = new SqlCommand("select top " + Db.PNum(args.Top) + " Name as DbComboText, K as DbComboValue from Brand where Name like '%" + Db.PStr(args.Query) + "%' " + extraSql + " order by Name", conn);
DataSet dataset = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = myCommand;
Bobs.Global.LogSqlQuery(Bobs.Global.QueryTypes.Select);
adapter.Fill(dataset);
dv = dataset.Tables[0].DefaultView;
}
finally
{
conn.Close();
conn.Dispose();
}
return dv;
}
public static object DbComboGetEvents(Cambro.Web.DbCombo.ServerMethodArgs args)
{
DataView dv;
SqlConnection conn = new SqlConnection(Vars.DefaultConnectionString);
try
{
SqlCommand myCommand = new SqlCommand("select top " + Db.PNum(args.Top) + " Event.Name+' @ '+Venue.Name+' in '+Place.Name+', '+CONVERT(VarChar,Event.DateTime,3) as DbComboText, Event.K as DbComboValue from Event INNER JOIN Venue ON Event.VenueK = Venue.K INNER JOIN Place ON Venue.PlaceK=Place.K where Event.Name+' @ '+Venue.Name+' in '+Place.Name+', '+CONVERT(VarChar,Event.DateTime,3) like '%" + Db.PStr(args.Query) + "%' order by Event.Name, Venue.Name, Event.DateTime DESC", conn);
DataSet dataset = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = myCommand;
Bobs.Global.LogSqlQuery(Bobs.Global.QueryTypes.Select);
adapter.Fill(dataset);
dv = dataset.Tables[0].DefaultView;
}
finally
{
conn.Close();
conn.Dispose();
}
return dv;
}
public static object DbComboGetPlacesEnabled(Cambro.Web.DbCombo.ServerMethodArgs args)
{
bool Int = false;
if (
args.ServerState != null &&
args.ServerState["Int"] != null &&
args.ServerState["Int"].ToString().Equals("1")
)
{
Int = true;
}
DataView dv;
SqlConnection conn = new SqlConnection(Vars.DefaultConnectionString);
try
{
SqlCommand myCommand = null;
if (Int)
myCommand = new SqlCommand("select top " + Db.PNum(args.Top) + " ISNULL(CAST ([place].[Name] AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS,'') + ' ' + ISNULL(CAST ([place].[RegionAbbreviation] AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS,'') + (case when [place].[RegionAbbreviation]='' then '' else ' ' end) +'('+ ISNULL(CAST ([Country].[FriendlyName] AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS,'') + ')' as DbComboText, place.K as DbComboValue from Place inner join Country on place.countryk = country.k where place.Name like '%" + Db.PStr(args.Query) + "%' AND Place.Enabled=1 order by place.Name", conn);
else
myCommand = new SqlCommand("select top " + Db.PNum(args.Top) + " ISNULL(CAST ([place].[Name] AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS,'') as DbComboText, place.K as DbComboValue from Place inner join Country on place.countryk = country.k where place.Name like '%" + Db.PStr(args.Query) + "%' AND Place.Enabled=1 AND " + Country.PlaceFilterSqlString + " order by place.Name", conn);
DataSet dataset = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = myCommand;
Bobs.Global.LogSqlQuery(Bobs.Global.QueryTypes.Select);
adapter.Fill(dataset);
dv = dataset.Tables[0].DefaultView;
}
finally
{
conn.Close();
conn.Dispose();
}
return dv;
}
public static object DbComboGetPlaces(Cambro.Web.DbCombo.ServerMethodArgs args)
{
DataView dv;
SqlConnection conn = new SqlConnection(Vars.DefaultConnectionString);
try
{
SqlCommand myCommand = new SqlCommand("select top " + Db.PNum(args.Top) + " ISNULL(CAST ([place].[Name] AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS,'') + ' (' + ISNULL(CAST ([place].[Type] AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS,'') + ') ' + ISNULL(CAST ([place].[RegionAbbreviation] AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS,'') + ' ' + ISNULL(CAST ([Country].[Name] AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS,'') as DbComboText, place.K as DbComboValue from Place inner join Country on place.countryk = country.k where place.Name like '%" + Db.PStr(args.Query) + "%' order by place.Name", conn);
DataSet dataset = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = myCommand;
Bobs.Global.LogSqlQuery(Bobs.Global.QueryTypes.Select);
adapter.Fill(dataset);
dv = dataset.Tables[0].DefaultView;
}
finally
{
conn.Close();
conn.Dispose();
}
return dv;
}
public static object DbComboGetGroupMembers(Cambro.Web.DbCombo.ServerMethodArgs args)
{
string extraWhereClause = "";
string extraFromClause = "";
if (
args.ServerState != null &&
args.ServerState.Authenticate("fgdfjshgofdshdf$£%%$FBskjhgjklsd") &&
args.ServerState["GroupK"] != null &&
args.ServerState["GroupK"].ToString().Length > 0
)
{
int GroupK = int.Parse(args.ServerState["GroupK"].ToString());
if (GroupK > 0)
{
extraWhereClause = " AND GroupUsr.GroupK=" + GroupK.ToString() + " AND GroupUsr.Status = 1 ";
extraFromClause = " INNER JOIN GroupUsr ON Usr.K = GroupUsr.UsrK ";
}
}
DataView dv;
SqlConnection conn = new SqlConnection(Vars.DefaultConnectionString);
try
{
SqlCommand myCommand = new SqlCommand("select top " + Db.PNum(args.Top) + " NickName as DbComboText, K as DbComboValue from Usr " + extraFromClause + " where NickName like '%" + Db.PStr(args.Query) + "%' AND NOT NickName='' " + extraWhereClause + " order by NickName", conn);
DataSet dataset = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = myCommand;
Bobs.Global.LogSqlQuery(Bobs.Global.QueryTypes.Select);
adapter.Fill(dataset);
dv = dataset.Tables[0].DefaultView;
}
finally
{
conn.Close();
conn.Dispose();
}
return dv;
}
public static object DbComboGetMultiUsrs(Cambro.Web.DbCombo.ServerMethodArgs args)
{
string extraWhereClause = "";
string extraFromClause = "";
if (
args.ServerState != null &&
args.ServerState.Authenticate("fgdfjshgofdshdf$£%%$FBskjhgjklsd") &&
args.ServerState["BuddiesUsrK"] != null &&
args.ServerState["BuddiesUsrK"].ToString().Length > 0
)
{
int BuddiesUsrK = int.Parse(args.ServerState["BuddiesUsrK"].ToString());
if (BuddiesUsrK > 0)
{
if (Usr.Current.K != BuddiesUsrK && !Usr.Current.IsAdmin)
throw new Exception("Wrong Usr");
extraWhereClause = " AND FullBuddy=1 AND Buddy.UsrK = " + BuddiesUsrK.ToString() + " ";
extraFromClause = " INNER JOIN Buddy ON Usr.K = Buddy.BuddyUsrK ";
}
}
DataView dv;
SqlConnection conn = new SqlConnection(Vars.DefaultConnectionString);
try
{
SqlCommand myCommand = new SqlCommand("select top " + Db.PNum(args.Top) + " NickName as DbComboText, (CAST(K as VarChar))+'$'+(CASE WHEN Pic IS NULL THEN '' ELSE CONVERT(varchar(255), Pic) END) as DbComboValue from Usr " + extraFromClause + " where NickName like '%" + Db.PStr(args.Query) + "%' AND NOT NickName='' " + extraWhereClause + " order by NickName", conn);
DataSet dataset = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = myCommand;
Bobs.Global.LogSqlQuery(Bobs.Global.QueryTypes.Select);
adapter.Fill(dataset);
dv = dataset.Tables[0].DefaultView;
}
finally
{
conn.Close();
conn.Dispose();
}
return dv;
}
