public static void MarkContentRecordasDeleted(string id)
{
MySQLParameterList sqlParams = new MySQLParameterList();
sqlParams.AddParameter("p_idContentAd", id);
MySQLDatabaseManager.ExecuteNonQuery("spMarkContentRecordasDeleted", sqlParams);
}
public static MySqlDataReader GetCommandDDLlist(int commandID)
{
MySQLParameterList sqlParams = new MySQLParameterList();
sqlParams.AddParameter("p_commandId", commandID);
return(MySQLDatabaseManager.GetDataReader("spGetCmdDDLlist", sqlParams));
}
// admin only view
public static DataSet GetAdminAds()
{
// MySQLParameterList sqlParams = new MySQLParameterList();
//sqlParams.AddParameter("p_systemid", 0);
//sqlParams.AddParameter("p_userid", 0);
return(MySQLDatabaseManager.GetDataSet("spGetContentAdList"));
}
public static DataSet GetSystems(string id)
{
MySQLParameterList sqlParams = new MySQLParameterList();
sqlParams.AddParameter("p_systemId", id);
return(MySQLDatabaseManager.GetDataSet("spGetSystem", sqlParams));
}
public static string GetNextCommand(int systemId)
{
MySQLParameterList sqlParams = new MySQLParameterList();
sqlParams.AddParameter("p_systemId", systemId);
return((string)MySQLDatabaseManager.ExecuteScalar("spGetCommand", sqlParams).ToString());
}
public static void AdminApprovedContentAd(string id)
{
MySQLParameterList sqlParams = new MySQLParameterList();
sqlParams.AddParameter("p_idContentAd", id);
MySQLDatabaseManager.ExecuteNonQuery(" btvAdminapprovedContentAd", sqlParams);
}
public static bool CheckSystemExists(string ip, string mac)
{
MySQLParameterList sqlParams = new MySQLParameterList();
sqlParams.AddParameter("p_ip", ip);
sqlParams.AddParameter("p_mac", mac);
bool rtn = false;
int result = -1;
object x;
x = MySQLDatabaseManager.ExecuteScalar("spCheckSystem", sqlParams);
result = Convert.ToInt32(x);
if (result > 0)
{
rtn = true;
}
else
{
rtn = false;
}
return(rtn);
}
public static void AdminDeleteContentAd(string id)
{
MySQLParameterList sqlParams = new MySQLParameterList();
sqlParams.AddParameter("p_idContentAd", id);
sqlParams.AddParameter("p_status", "deleted");
MySQLDatabaseManager.ExecuteNonQuery("btvAdminUpdateContentAdRecordStatus", sqlParams);
}
public static DataTable GetAdboxCost()
{
string systemid = "0"; // null not used
MySQLParameterList sqlParams = new MySQLParameterList();
sqlParams.AddParameter("p_systemid", systemid);
return(MySQLDatabaseManager.GetDataTable("getAdboxchargeCost", sqlParams));
}
public static DataSet GetMyAdContentGrid(string btvuser)
{
MySQLParameterList sqlParams = new MySQLParameterList();
sqlParams.AddParameter("p_systemid", 0);
sqlParams.AddParameter("p_btvuserid", btvuser);
return(MySQLDatabaseManager.GetDataSet("spGetMyContentGrid", sqlParams));
}
public static MySqlDataReader GetContentAdRecord(string idContentAd, string btvUserId)
{
MySQLParameterList sqlParams = new MySQLParameterList();
sqlParams.AddParameter("p_idContentId", idContentAd);
sqlParams.AddParameter("p_btvuserid", btvUserId);
return(MySQLDatabaseManager.GetDataReaderSP("spGetContentAdRecord", sqlParams));
}
public static void RegisterUser(string name, string email, string pwd, string guidgen)
{
MySQLParameterList sqlParams = new MySQLParameterList();
sqlParams.AddParameter("p_name", name);
sqlParams.AddParameter("p_email", email);
sqlParams.AddParameter("p_pwd", pwd);
sqlParams.AddParameter("p_guid", guidgen);
MySQLDatabaseManager.ExecuteNonQuery("spRegisterBTVuser", sqlParams);
}
public static void SaveAuthKey(string ip, string mac, string authKey)
{
MySQLParameterList sqlParams = new MySQLParameterList();
sqlParams.AddParameter("p_ip", ip);
sqlParams.AddParameter("p_mac", mac);
sqlParams.AddParameter("p_authKey", authKey);
int result = -1;
MySQLDatabaseManager.ExecuteScalar("spLogAuthKey", sqlParams);
}
public static void SaveContentAd(
string adboxsize,
DateTime expirydate,
string expirylength,
DateTime publishdate,
string priority,
string cssstyle,
string bgcolor,
string font,
string fontsize,
bool premium,
string category,
string heading,
string contact,
string htmltext,
bool qr,
string systemId,
string recordStatus,
int approved,
string supercedeId
)
{
string idbtvuser = baz.utils.bazutils.GetUserSessionVal("idbtvusers");
MySQLParameterList sqlParams = new MySQLParameterList();
sqlParams.AddParameter("p_adboxsize", adboxsize);
sqlParams.AddParameter("p_expirydate", expirydate);
sqlParams.AddParameter("p_expirylength", expirylength);
sqlParams.AddParameter("p_publishdate", publishdate);
sqlParams.AddParameter("p_priority", priority);
sqlParams.AddParameter("p_cssstyle", cssstyle);
sqlParams.AddParameter("p_bgcolor", bgcolor);
sqlParams.AddParameter("p_font", font);
sqlParams.AddParameter("p_fontsize", fontsize);
sqlParams.AddParameter("p_premium", premium);
sqlParams.AddParameter("p_category", category);
sqlParams.AddParameter("p_heading", heading);
sqlParams.AddParameter("p_contact", contact);
sqlParams.AddParameter("p_htmltext", htmltext);
sqlParams.AddParameter("p_qrcode", qr);
sqlParams.AddParameter("p_btvuser", idbtvuser);
sqlParams.AddParameter("p_systemid", systemId);
sqlParams.AddParameter("p_recordStatus", recordStatus);
sqlParams.AddParameter("p_approved", approved);
sqlParams.AddParameter("p_supercedeid", supercedeId);
int result = -1;
MySQLDatabaseManager.ExecuteScalar("spPostAd", sqlParams);
}
public static int CheckEmailGuid(string email, string confirmguid)
{
MySQLParameterList sqlParams = new MySQLParameterList();
sqlParams.AddParameter("p_email", email);
sqlParams.AddParameter("p_guid", confirmguid);
int rows = 0;
rows = MySQLDatabaseManager.ExecuteNonQuery("spCheckEmailGuid", sqlParams);
return(rows);
}
public static DataTable CheckLoginUser(string user, string pwd)
{
MySQLParameterList sqlParams = new MySQLParameterList();
sqlParams.AddParameter("p_user", user);
sqlParams.AddParameter("p_pwd", pwd);
DataTable dt = MySQLDatabaseManager.GetDataTable("spCheckLogin", sqlParams);
// bool flagAuth = false;
//if ((int)Convert.ToInt32(o) > 0) flagAuth = true;
return(dt);
}
public static bool IsUserAlreadyRegistered(string email)
{
MySQLParameterList sqlParams = new MySQLParameterList();
sqlParams.AddParameter("p_email", email);
//sqlParams.AddParameter("p_pwd", pwd);
object o = MySQLDatabaseManager.ExecuteScalar("spBTVuserExists", sqlParams);
bool flagAuth = false;
if ((int)Convert.ToInt32(o) > 0)
{
flagAuth = true;
}
return(flagAuth);
}
public static int GetMaxCmdQId()
{
int rtn = 0;
string sql = "select max(commandqid) from commandq";
try
{
rtn = Convert.ToInt32(MySQLDatabaseManager.ExecuteScalar(sql));
}
catch (InvalidCastException ice)
{
rtn = 0;
}
return(rtn);
}
static void Main(string[] args)
{
string RootDirectory = @"C:\Users\mpound\Desktop\140215";
string ImageDirectory = RootDirectory;//RootDirectory.Remove(RootDirectory.LastIndexOf("\\"));
Dictionary <string, string> FileTagMap = new Dictionary <string, string>();
using (StreamReader strm = new StreamReader(RootDirectory + "\\mapped.txt"))
{
while (!strm.EndOfStream)
{
string line = strm.ReadLine();
string[] parts = line.Split(new char[] { '\t' }, StringSplitOptions.RemoveEmptyEntries);
string fullPath = ImageDirectory + "\\" + parts[0];
string cropPath = fullPath.Insert(fullPath.LastIndexOf("\\") + 1, "crop\\");
string tag = parts[1].Replace("\"", "");
FileTagMap.Add(fullPath, tag);
}
}
#region DB connect and test
byte[] key = Encoding.ASCII.GetBytes("ght29cj8f230f30d");
byte[] IV = Encoding.ASCII.GetBytes("a32fg32bnds2df90");
byte[] enc = new byte[] { 138, 208, 147, 69, 17, 213, 75, 13, 125, 185, 237, 113, 181, 64, 221, 16 };
ConnectionParams cp = new ConnectionParams()
{
Server = "ssbmb.nottingham.ac.uk",
Database = "arab_rootnav",
Port = "3306",
Source = ConnectionSource.MySQLDatabase,
Username = "******",
Password = "******"//DecryptStringFromBytes_Aes(enc, key, IV)
};
System.Console.Write("Connecting to database: ");
MySQLDatabaseManager manager = new MySQLDatabaseManager();
manager.Open(cp.ToMySQLConnectionString());
string imagesTableCheckString = "SELECT TABLE_NAME as name FROM information_schema.TABLES WHERE TABLE_SCHEMA = '" + manager.Connection.Database + "'";
MySqlCommand imagesTableCheckCommand = new MySqlCommand(imagesTableCheckString, manager.Connection as MySqlConnection);
bool confirmedImageDB = false;
using (MySqlDataReader Reader = imagesTableCheckCommand.ExecuteReader())
{
while (Reader.Read())
{
if (Reader["name"] as String == "images")
{
confirmedImageDB = true;
}
}
}
if (manager.IsOpen)
{
System.Console.WriteLine("Done");
}
else
{
System.Console.WriteLine("Could not connect");
}
if (!confirmedImageDB)
{
Console.WriteLine("Updating database to include image table");
string creationString = "CREATE TABLE IF NOT EXISTS images(" +
"Tag CHAR(64) NOT NULL, " +
"Stamp TIMESTAMP NOT NULL, " +
"Image LONGBLOB, " +
"CONSTRAINT pk_image_one PRIMARY KEY (Tag, Stamp) " +
") ENGINE=INNODB";
MySqlCommand creationCommand = new MySqlCommand(creationString, manager.Connection as MySqlConnection);
int success = creationCommand.ExecuteNonQuery();
}
#endregion
foreach (var kvp in FileTagMap)
{
string file = kvp.Key;
string tag = kvp.Value;
// if (file != @"I:\Michael\Savannah Rialto DH panel exps\RSDH08 (Marcus) HN vs LN\22\crop\10001.JPG") continue;
if (tag != "#N/A")
{
byte[] image = File.ReadAllBytes(file);
InjectImageIntoDB(tag, image, manager.Connection as MySqlConnection);
Console.WriteLine("Inserted " + tag);
}
}
//byte[] jpg = File.ReadAllBytes(@"C:\Users\mpound\Desktop\0002.jpg");
//string tag = "mp1,0002,test";
/******************************/
}
public static DataSet GetcontentadList()
{
return(MySQLDatabaseManager.GetDataSet("spGetContentAdList"));
}
public static MySqlDataReader GetPreviewAd(string idContentAd)
{
return(MySQLDatabaseManager.GetDataReader("select * from contentad where idcontentad=" + idContentAd));
}
public static DataSet GetTemplateList()
{
return(MySQLDatabaseManager.GetDataSet("spGetTemplateList"));
}
public static DataSet GetAllSystems()
{
return(MySQLDatabaseManager.GetDataSet("spGetAllSystems"));
}