protected void Page_Load(object sender, EventArgs e)
{
// Deny normal user access
if (Session["userType"].ToString() == Reference.USR_MEM)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "redirect", "alert('You do not have access to this page'); window.location='" +
Request.ApplicationPath + "ProfileInfo.aspx';", true);
}
else
{
User userObj = new User();
UserManagement uDao = new UserManagement();
userObj = uDao.getUserByID(Session["selectedID"].ToString());
lbName.Text = userObj.Name;
lbEmail.Text = userObj.Email;
lbContact.Text = userObj.ContactNumber;
lbUserType.Text = uDao.getUserType(userObj.Type);
lbCompany.Text = userObj.CompanyName;
lbStatus.Text = uDao.getUserStatus(userObj.Status);
}
}
protected void fp_onclick(object sender, EventArgs e)
{
string Email = fpEmail.Text;
string Pswd = CreatePassword(8);
string lastUpdOn = DateTime.Now.ToString("MM/dd/yyyy h:mm tt");
//string lastUpdBy = Session["userID"].ToString();
//string lastUpdOn = DateTime.Now.ToString("MM/dd/yyyy h:mm tt");
User userObj = new User();
User userObj1 = new User();
UserManagement uDao = new UserManagement();
userObj1 = uDao.checkEmail(Email);
int EmailMatch = 0;
if (userObj1 != null)
{
EmailMatch = 1;
}
if (EmailMatch == 1) // if email matches
{
userObj = uDao.getUserByEmail(Email); // get email from sql
// Password codes below
// make a new byte array
byte[] salt;
// generate salt
new RNGCryptoServiceProvider().GetBytes(salt = new byte[16]);
// hash and salt using PBKDF2
var pbkdf2 = new Rfc2898DeriveBytes(Pswd, salt, 10000);
// place string in byte array
byte[] hash = pbkdf2.GetBytes(20);
// make new byte array to store hashed password + salt
// 36 --> 16(salt) + 20(hash)
byte[] hashbytes = new byte[36];
Array.Copy(salt, 0, hashbytes, 0, 16);
Array.Copy(hash, 0, hashbytes, 16, 20);
string PasswordHash = Convert.ToBase64String(hashbytes);
string PasswordSalt = Convert.ToBase64String(salt);
// Database codes insert below
Boolean insCnt = uDao.updateUserPassword(Email, PasswordHash, PasswordSalt, lastUpdOn);
// Email codes below
string body = "Dear User, " + Environment.NewLine + Environment.NewLine + "Your Password Is Successfully Reset! " + Environment.NewLine + "This Is Your Current Login Password: "******". Please Proceed To Change Your Password Upon Your Login. Thank you. " + Environment.NewLine + Environment.NewLine + Environment.NewLine + "Regards, " + Environment.NewLine + "Targeted Marketing Admin Team";
string subject = "Password Successfully Reset!";
string toEmail = Email;
sendMail(subject, body, toEmail);
string script = "alert('Password successfully reset! Please check your new password at your email!');";
ClientScript.RegisterClientScriptBlock(this.GetType(), "Alert", script, true);
}
else
{
string script = "alert('Email not registered. Please re-enter a correct email.');";
ClientScript.RegisterClientScriptBlock(this.GetType(), "Alert", script, true);
}
}
protected void login_onclick(object sender, EventArgs e)
{
string Email = unTB.Text;
User userObj = new User();
User userObj1 = new User();
UserManagement uDao = new UserManagement();
userObj1 = uDao.checkEmail(Email);
int EmailMatch = 0;
if (userObj1 != null)
{
EmailMatch = 1;
}
if (EmailMatch == 1)
{
userObj = uDao.getUserByEmail(Email);
int pswdMatch = 1;
//noted,CheEe(002):comment this to bypass the login!!!
string pswdHash = userObj.PasswordHash;
// convert into bytes
byte[] hashbytes = Convert.FromBase64String(pswdHash);
// take the salt out of the string
byte[] salt = new byte[16];
Array.Copy(hashbytes, 0, salt, 0, 16);
// hash the entered password
var pbkdf2 = new Rfc2898DeriveBytes(pwTB.Text, salt, 10000);
byte[] hash = pbkdf2.GetBytes(20);
for (int i = 0; i < 20; i++)
{
if (hashbytes[i + 16] != hash[i])
{
pswdMatch = 0;
}
}
if (pswdMatch == 1)
{
Session["userID"] = userObj.UserID;
//System.Diagnostics.Debug.Write(Session["userID"]);
Session["userType"] = userObj.Type;
if ((string)Session["userType"] == Reference.USR_ADM || (string)Session["userType"] == Reference.USR_MEM)
{
Response.Redirect("ProfileInfo.aspx");
}
}
else
{
string script = "alert('Password is incorrect. Please re-enter the correct password.');";
ClientScript.RegisterClientScriptBlock(this.GetType(), "Alert", script, true);
}
}
else
{
string script = "alert('Email not registered. Please re-enter a correct email.');";
ClientScript.RegisterClientScriptBlock(this.GetType(), "Alert", script, true);
}
}
protected void btnDelete_Command(object sender, CommandEventArgs e)
{
if (e.CommandName == "DeleteMessage")
{
SqlConnection conn = null;
SqlDataReader reader = null;
// instantiate and open connection
conn = new
SqlConnection(Reference.Constr);
conn.Open();
int index = Convert.ToInt32(e.CommandArgument);
// Retrieve the row that contains the button
// from the Rows collection.
GridViewRow row = gvUser.Rows[index];
LinkButton btnButton1 = sender as LinkButton;
GridViewRow gvRow1 = (GridViewRow)btnButton1.NamingContainer;
//SqlCommand cmdCount = new SqlCommand("select count(*) as total from Advertisement as a inner join Company as c on a.companyID=c.CompanyID where c.CompanyID=@ID", conn);
User uObj = new User();
UserManagement uDao = new UserManagement();
Label lb_msgId = (Label)gvRow1.FindControl("lb_UserID");
// string CurrentSession = Session["UserID"].ToString();
uObj = uDao.getUserByID(lb_msgId.Text);
string userName = uObj.Name;
if (lb_msgId.Text.ToString() == Session["UserID"].ToString())
{
deleteFailure.Visible = true;
alertSuccess.Visible = false;
updateSuccess.Visible = false;
createSuccess.Visible = false;
labelDelete.Text = "You cannot delete yourself";
}
else
{
deleteFailure.Visible = false;
alertSuccess.Visible = true;
updateSuccess.Visible = false;
createSuccess.Visible = false;
msgSuccess.Text = userName + " Has Been Deleted Successfully!";
Boolean insCnt = uDao.deleteQns(lb_msgId.Text);
}
//VIC: never inform if the delete is successful or not?
Database db = new Database();
SqlCommand cmd = new SqlCommand("Select * from [User] WHERE Type != @paraType and Status = 1");
cmd.Parameters.AddWithValue("@paraType", (string)Session["userType"]);
DataSet ds = db.getDataSet(cmd);
//gvUser.DataSource = ds;
gvUser.DataBind();
}
}
protected void btnCreate_User(object sender, EventArgs e)
{
// if((tbName.Text == "" || tbConNo.Text == "" || ddlUserType.SelectedValue==""|| tbEmail.Text==""))
// {
// alertWarning.Visible = true;
// msgWarning.Text = "Please ensure you have filled in all required fields";
// }
string Name = tbName.Text;
string Type = ddlUserType.SelectedItem.Value;
string Email = tbEmail.Text;
string ContactNumber = tbConNo.Text;
string Pswd = CreatePassword(8);
int Status = 1;
int CreatedBy = Convert.ToInt32(Session["userID"]);
string CreatedOn = DateTime.Now.ToString("MM/dd/yyyy h:mm tt");
int CompanyID = Convert.ToInt32(ddlCompany.SelectedValue);
if (Type == "NULL")
{
alertWarning.Visible = true;
msgWarning.Text = "Please Select User Type!";
}
else
{
if (Type == Reference.USR_MEM && CompanyID == 0)
{
alertWarning.Visible = true;
msgWarning.Text = "Please Select Company!";
}
else
{
// make a new byte array
byte[] salt;
// generate salt
new RNGCryptoServiceProvider().GetBytes(salt = new byte[16]);
// hash and salt using PBKDF2
var pbkdf2 = new Rfc2898DeriveBytes(Pswd, salt, 10000);
// place string in byte array
byte[] hash = pbkdf2.GetBytes(20);
// make new byte array to store hashed password + salt
// 36 --> 16(salt) + 20(hash)
byte[] hashbytes = new byte[36];
Array.Copy(salt, 0, hashbytes, 0, 16);
Array.Copy(hash, 0, hashbytes, 16, 20);
string PasswordHash = Convert.ToBase64String(hashbytes);
string PasswordSalt = Convert.ToBase64String(salt);
UserManagement uDao = new UserManagement();
User uObj = new User();
uObj = uDao.checkEmail(Email);
int EmailExist = 1;
if (uObj == null)
{
EmailExist = 0;
}
if (EmailExist == 0)
{
if (Type == Reference.USR_ADM)
{
Boolean insCnt = uDao.createAdmin(Name, Email, ContactNumber, Type, PasswordHash, PasswordSalt, Status, CreatedBy, CreatedOn);
System.Diagnostics.Debug.WriteLine("Working");
}
else
{
Boolean insCnt = uDao.createUser(Name, Email, ContactNumber, Type, PasswordHash, PasswordSalt, Status, CompanyID, CreatedBy, CreatedOn);
}
string body = "Dear " + Name + ", " + Environment.NewLine + Environment.NewLine + "Your Account Has Been Successfully Created! " + Environment.NewLine + "This Is Your First-Time Login Password: "******". Please Proceed To Change Your Password Upon Your First Login. Thank you. " + Environment.NewLine + Environment.NewLine + Environment.NewLine + "Regards, " + Environment.NewLine + "Targeted Marketing Admin Team";
string subject = "Account Successfully Created!";
string toEmail = Email;
sendMail(subject, body, toEmail); // This is the line where the email is sent
//VIC: after successful creation, the fields should be cleared to min the risk of user clicking on the submit button again
ddlUserType.SelectedIndex = 0;
ddlCompany.SelectedIndex = 0;
tbName.Text = String.Empty;
tbEmail.Text = String.Empty;
tbConNo.Text = String.Empty;
alertSuccess.Visible = true;
alertWarning.Visible = false;
msgSuccess.Text = Name + " Has Been Created Successfully!";
Session["CreateUser"] = 2;
Response.Redirect("UserList.aspx");
}
//VIC: do not need to check if contact already exist
else if (EmailExist > 0)
{
tbEmail.Text = String.Empty;
alertWarning.Visible = true;
alertSuccess.Visible = false;
msgWarning.Text = "Email Already In-Use. Please Try Again!";
}
}
//Session["CreateUser"] = 2;
//Response.Redirect("UserList.aspx");
}
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
UserManagement uDao = new UserManagement();
User uObj = new User();
if (Session["userType"].ToString() == Reference.USR_ADM)
{
uObj = uDao.getAdminByID(Session["userID"].ToString());
}
else
{
uObj = uDao.getUserByID(Session["userID"].ToString());
}
string uName = tbName.Text;
string uContact = tbContact.Text;
string lastUpdBy = Session["userID"].ToString();
string lastUpdOn = DateTime.Now.ToString("MM/dd/yyyy h:mm tt");
//initialise hash password
string uPswdHash = "";
//initalise salted password
string uPswdSalt = "";
int testing = 1;
//pswdmatch=1
int pswdMatch = 1;
//if empty make password hash and salt same
if (CurrentPassword.Text == "" && CurrentPassword.Visible == false || tbPswd.Text == "" || tbCPswd.Text == "" || (tbPswd.Text == "" && tbCPswd.Text == ""))
{
uPswdHash = (string)uObj.PasswordHash;
uPswdSalt = (string)uObj.PasswordSalt;
Boolean insCnt = uDao.updateCurrentUser(Session["userID"].ToString(), uName, uContact, uPswdHash, uPswdSalt, lastUpdBy, lastUpdOn);
tbName.Text = String.Empty;
tbContact.Text = String.Empty;
alertSuccess.Visible = true;
}
else
{
string passwordhashlol = uObj.PasswordHash;
// convert into bytes
byte[] hashbyteslol = Convert.FromBase64String(passwordhashlol);
// take the salt out of the string
byte[] saltlol = new byte[16];
Array.Copy(hashbyteslol, 0, saltlol, 0, 16);
// hash the entered Current password
var pbkdf2lol = new Rfc2898DeriveBytes(CurrentPassword.Text, saltlol, 10000);
byte[] hashlol = pbkdf2lol.GetBytes(20);
for (int i = 0; i < 20; i++)
{
if (hashbyteslol[i + 16] != hashlol[i])
{
pswdMatch = 0;
}
}
if (pswdMatch == 1)
{
if (tbPswd.Text == tbCPswd.Text)
{
byte[] salt;
// generate salt
new RNGCryptoServiceProvider().GetBytes(salt = new byte[16]);
// hash and salt using PBKDF2
var pbkdf2 = new Rfc2898DeriveBytes(tbCPswd.Text, salt, 10000);
// place string in byte array
byte[] hash = pbkdf2.GetBytes(20);
// make new byte array to store hashed password + salt
// 36 --> 16(salt) + 20(hash)
byte[] hashbytes = new byte[36];
Array.Copy(salt, 0, hashbytes, 0, 16);
Array.Copy(hash, 0, hashbytes, 16, 20);
string PasswordHash = Convert.ToBase64String(hashbytes);
string PasswordSalt = Convert.ToBase64String(salt);
uPswdHash = PasswordHash;
uPswdSalt = PasswordSalt;
Boolean insCnt = uDao.updateCurrentUser(Session["userID"].ToString(), uName, uContact, uPswdHash, uPswdSalt, lastUpdBy, lastUpdOn);
tbName.Text = String.Empty;
tbContact.Text = String.Empty;
alertSuccess.Visible = true;
alertDanger.Visible = false;
}
}
else
{
alertDanger.Visible = true;
alertSuccess.Visible = false;
}
}
}
public void BindGrid()
{
SqlConnection conn = null;
SqlDataReader reader = null;
// instantiate and open connection
conn = new
SqlConnection(Reference.Constr);
conn.Open();
if (Session["userType"].ToString() == Reference.USR_ADM)
{
// 1. declare command object with parameter
SqlCommand cmd = new SqlCommand(
" SELECT [Advertisement].AdvID,[Company].Name as CompanyName, [Advertisement].Name as AdvertName, [Advertisement].Item, [Advertisement].ItemType,[StartDate], [EndDate]FROM " +
"[Advertisement] inner join [Company] on Company.CompanyID =[Advertisement].CompanyID where [Advertisement].status = 1 and[Company].status = 1", conn);
SqlDataAdapter sda = new SqlDataAdapter();
DataTable dt = new DataTable();
cmd.Connection = conn;
sda.SelectCommand = cmd;
sda.Fill(dt);
// get data stream
GridView1.DataSource = dt;
GridView1.DataBind();
}
else
{
User uObj = new User();
UserManagement uDao = new UserManagement();
uObj = uDao.getUserByID(Session["userID"].ToString());
// 1. declare command object with parameter
SqlCommand cmd = new SqlCommand(
" SELECT [Advertisement].AdvID,[Company].Name as CompanyName, [Advertisement].Name as AdvertName, [Advertisement].Item, [Advertisement].ItemType,[StartDate], [EndDate] FROM " +
"[Advertisement] inner join [Company] on Company.CompanyID =[Advertisement].CompanyID where [Advertisement].status = 1 and [Company].status = 1 and [Company].CompanyID=@comID", conn);
// 2. define parameters used in command object
SqlParameter param = new SqlParameter();
param.ParameterName = "@comID";
param.Value = uObj.CompanyID.ToString();
// 3. add new parameter to command object
cmd.Parameters.Add(param);
SqlDataAdapter sda = new SqlDataAdapter();
DataTable dt = new DataTable();
cmd.Connection = conn;
sda.SelectCommand = cmd;
sda.Fill(dt);
// get data stream
GridView1.DataSource = dt;
GridView1.DataBind();
}
if (GridView1.Rows.Count == 0)
{
}
}
protected void btnDelete_Command(object sender, CommandEventArgs e)
{
SqlConnection conn = null;
SqlDataReader reader = null;
// instantiate and open connection
conn = new
SqlConnection(Reference.Constr);
conn.Open();
if (e.CommandName == "DeleteAdMessage")
{
int index = Convert.ToInt32(e.CommandArgument);
// Retrieve the row that contains the button
// from the Rows collection.
GridViewRow row = GridView1.Rows[index];
LinkButton btnButton1 = sender as LinkButton;
GridViewRow gvRow1 = (GridViewRow)btnButton1.NamingContainer;
Advertisement aObj = new Advertisement();
Advertisement_Management aDao = new Advertisement_Management();
Label lb_msgId = (Label)gvRow1.FindControl("lb_AdvertID");
aObj = aDao.getAdvByID(lb_msgId.Text);
// bObj = bDao.getBillboardByID(lb_msgId.Text);
// string BBCode = bObj.BillboardCode;
//Boolean insCnt = bDao.deleteBillboard(lb_msgId.Text);
Boolean DeleteAd = aDao.deleteAdvert(lb_msgId.Text);
//VIC: never inform if the delete is successful or not?
alertSuccessDelete.Visible = true;
alertSuccessCreate.Visible = false;
alertSuccessUpdate.Visible = false;
Label3.Text = " Advert '" + aObj.Name + "' Has Been Deleted Successfully!";
//" SELECT [Advertisement].AdvID,[Company].Name as CompanyName, [Advertisement].Name as AdvertName, [Advertisement].Item, [Advertisement].ItemType,[StartDate], [EndDate]FROM " +
// "[Advertisement] inner join [Company] on Company.CompanyID =[Advertisement].CompanyID where [Advertisement].status = 1 and[Company].status = 1", conn);
Database db = new Database();
if (Session["userType"].ToString() == Reference.USR_ADM)
{
SqlCommand cmd = new SqlCommand("SELECT [Advertisement].AdvID,[Company].Name as CompanyName, [Advertisement].Name as AdvertName, [Advertisement].Item, [Advertisement].ItemType,[StartDate], [EndDate]FROM " +
"[Advertisement] inner join [Company] on Company.CompanyID =[Advertisement].CompanyID where [Advertisement].status = 1 and[Company].status = 1", conn);
SqlDataAdapter sda = new SqlDataAdapter();
DataTable dt = new DataTable();
sda.SelectCommand = cmd;
sda.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
else
{
User uObj = new User();
UserManagement uDao = new UserManagement();
uObj = uDao.getUserByID(Session["userID"].ToString());
SqlCommand cmd = new SqlCommand("SELECT [Advertisement].AdvID,[Company].Name as CompanyName, [Advertisement].Name as AdvertName, [Advertisement].Item, [Advertisement].ItemType,[StartDate], [EndDate]FROM " +
"[Advertisement] inner join [Company] on Company.CompanyID =[Advertisement].CompanyID where [Advertisement].status = 1 and[Company].status = 1 and [Advertisement].CompanyID=@comID", conn);
SqlParameter param = new SqlParameter();
param.ParameterName = "@comID";
param.Value = uObj.CompanyID.ToString();
cmd.Parameters.Add(param);
SqlDataAdapter sda = new SqlDataAdapter();
DataTable dt = new DataTable();
sda.SelectCommand = cmd;
sda.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
}
protected void btnRun_Click(object sender, EventArgs e)
{
// " select [Company].Name as CompanyName ,[Advertisement].Name as AdvertName,[Advertisement].Item,[Advertisement].ItemType,[Advertisement].StartDate,[Advertisement].EndDate" +
//" from [Advertisement] inner join [Company] on [Advertisement].CompanyID =[Company].CompanyID " +
// "where [Advertisement].status=1 order by " + e.SortExpression + " " + strSortDirection, conn);
if (Session["userType"].ToString() == Reference.USR_ADM)
{
//admin input
if (startDateTB.Text == "" && endDateTB.Text == "")
{
string str = " select AdvID, [Company].Name as CompanyName,[Advertisement].Name as AdvertName" +
",[Advertisement].Item,[Advertisement].ItemType,[Advertisement].StartDate,[Advertisement].EndDate from [Advertisement] inner join [Company] on [Advertisement].CompanyID =[Company].CompanyID " +
"where [Advertisement].status=1 and ([Advertisement].Name like '%' + @search + '%' OR [Company].Name like '%' + @search + '%' OR ItemType like '%'" +
" + @search + '%' OR StartDate like '%' + @search + '%' OR EndDate like '%' + @search + '%') ";
SqlCommand xp = new SqlCommand(str, con);
xp.Parameters.Add("@search", SqlDbType.NVarChar).Value = txtSearch.Text;
//xp.Parameters.Add("@search2", SqlDbType.NVarChar).Value = txtSearch.Text;
con.Open();
xp.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = xp;
DataSet ds = new DataSet();
da.Fill(ds, "Name");
GridView1.DataSource = ds;
GridView1.DataBind();
}
else
{
DateTime sdate = DateTime.Parse(startDateTB.Text);
DateTime edate = DateTime.Parse(endDateTB.Text);
string str = " select AdvID, [Company].Name as CompanyName,[Advertisement].Name as AdvertName" +
",[Advertisement].Item,[Advertisement].ItemType,[Advertisement].StartDate,[Advertisement].EndDate from [Advertisement] inner join [Company] on [Advertisement].CompanyID =[Company].CompanyID " +
"where [Advertisement].status=1 and [Advertisement].StartDate>=@sDate and [Advertisement].EndDate<=@eDate and" +
" ([Advertisement].Name like '%' + @search + '%' OR [Company].Name like '%' + @search + '%' OR ItemType like '%'" +
" + @search + '%' OR StartDate like '%' + @search + '%' OR EndDate like '%' + @search + '%') ";
SqlCommand xp = new SqlCommand(str, con);
xp.Parameters.Add("@search", SqlDbType.NVarChar).Value = txtSearch.Text;
xp.Parameters.Add("@sDate", SqlDbType.DateTime).Value = sdate;
xp.Parameters.Add("@eDate", SqlDbType.DateTime).Value = edate;
//xp.Parameters.Add("@search2", SqlDbType.NVarChar).Value = txtSearch.Text;
con.Open();
xp.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = xp;
DataSet ds = new DataSet();
da.Fill(ds, "Name");
GridView1.DataSource = ds;
GridView1.DataBind();
}
}
else
{
//user input
if (startDateTB.Text == "" && endDateTB.Text == "")
{
User uObj = new User();
UserManagement uDao = new UserManagement();
uObj = uDao.getUserByID(Session["userID"].ToString());
string str = " select AdvID, [Company].Name as CompanyName,[Advertisement].Name as AdvertName" +
",[Advertisement].Item,[Advertisement].ItemType,[Advertisement].StartDate,[Advertisement].EndDate from [Advertisement] inner join [Company] on [Advertisement].CompanyID =[Company].CompanyID " +
"where [Company].CompanyID=@ID and [Advertisement].status=1 and ([Advertisement].Name like '%' + @search + '%' OR [Company].Name like '%' + @search + '%' OR ItemType like '%'" +
" + @search + '%' OR StartDate like '%' + @search + '%' OR EndDate like '%' + @search + '%') ";
SqlCommand xp = new SqlCommand(str, con);
xp.Parameters.Add("@ID", SqlDbType.NVarChar).Value = uObj.CompanyID.ToString();
xp.Parameters.Add("@search", SqlDbType.NVarChar).Value = txtSearch.Text;
//xp.Parameters.Add("@search2", SqlDbType.NVarChar).Value = txtSearch.Text;
con.Open();
xp.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = xp;
DataSet ds = new DataSet();
da.Fill(ds, "Name");
GridView1.DataSource = ds;
GridView1.DataBind();
}
else
{
DateTime sdate = DateTime.Parse(startDateTB.Text);
DateTime edate = DateTime.Parse(endDateTB.Text);
string str = " select AdvID, [Company].Name as CompanyName,[Advertisement].Name as AdvertName" +
",[Advertisement].Item,[Advertisement].ItemType,[Advertisement].StartDate,[Advertisement].EndDate from [Advertisement] inner join [Company] on [Advertisement].CompanyID =[Company].CompanyID " +
"where [Advertisement].status=1 and [Advertisement].StartDate>=@sDate and [Advertisement].EndDate<=@eDate and" +
" ([Advertisement].Name like '%' + @search + '%' OR [Company].Name like '%' + @search + '%' OR ItemType like '%'" +
" + @search + '%' OR StartDate like '%' + @search + '%' OR EndDate like '%' + @search + '%') ";
SqlCommand xp = new SqlCommand(str, con);
xp.Parameters.Add("@search", SqlDbType.NVarChar).Value = txtSearch.Text;
xp.Parameters.Add("@sDate", SqlDbType.DateTime).Value = sdate;
xp.Parameters.Add("@eDate", SqlDbType.DateTime).Value = edate;
//xp.Parameters.Add("@search2", SqlDbType.NVarChar).Value = txtSearch.Text;
con.Open();
xp.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = xp;
DataSet ds = new DataSet();
da.Fill(ds, "Name");
GridView1.DataSource = ds;
GridView1.DataBind();
}
}
//string str = " select [Company].Name as CompanyName,[Advertisement].Name as AdvertName" +
// ",[Advertisement].Item,[Advertisement].ItemType,[Advertisement].StartDate,[Advertisement].EndDate from [Advertisement] inner join [Company] on [Advertisement].CompanyID =[Company].CompanyID " +
// "where [Company].CompanyID=@ID and [Advertisement].status=1 and ([Advertisement].Name like '%' + @search + '%' OR ItemType like '%'" +
// " + @search + '%' OR StartDate like '%' + @search + '%' OR EndDate like '%' + @search + '%') ";
//SqlCommand xp = new SqlCommand(str, vid);
//xp.Parameters.Add("@ID", SqlDbType.NVarChar).Value = Session["CompanyID"].ToString();
//xp.Parameters.Add("@search", SqlDbType.NVarChar).Value = txtSearch.Text;
////xp.Parameters.Add("@search2", SqlDbType.NVarChar).Value = txtSearch.Text;
//vid.Open();
//xp.ExecuteNonQuery();
//SqlDataAdapter da = new SqlDataAdapter();
//da.SelectCommand = xp;
//DataSet ds = new DataSet();
//da.Fill(ds, "Name");
//GridView1.DataSource = ds;
//GridView1.DataBind();
}
protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
{
SortDirection sortDirection = SortDirection.Ascending;
string sortField = string.Empty;
SortGridview((GridView)sender, e, out sortDirection, out sortField);
string strSortDirection = sortDirection == SortDirection.Ascending ? "ASC" : "DESC";
SqlConnection conn = null;
SqlDataReader reader = null;
// instantiate and open connection
conn = new
SqlConnection(Reference.Constr);
conn.Open();
// " SELECT [Advertisement].AdvID,[Company].Name, [Advertisement].Name, [Advertisement].Item, [Advertisement].ItemType,[StartDate], [EndDate]FROM " +
//"[Advertisement] inner join[Company] on Company.CompanyID =[Advertisement].CompanyID where[Advertisement].status = 1 and[Company].status = 1"
if (Session["UserType"].ToString() == Reference.USR_ADM)
{
SqlCommand cmd = new SqlCommand(
" select AdvID, [Company].Name as CompanyName ,[Advertisement].Name as AdvertName,[Advertisement].Item,[Advertisement].ItemType,[Advertisement].StartDate,[Advertisement].EndDate" +
" from [Advertisement] inner join [Company] on [Advertisement].CompanyID =[Company].CompanyID " +
"where [Advertisement].status=1 order by " + e.SortExpression + " " + strSortDirection, conn);
SqlDataAdapter sda = new SqlDataAdapter();
DataTable dt = new DataTable();
cmd.Connection = conn;
sda.SelectCommand = cmd;
sda.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
else
{
User uObj = new User();
UserManagement uDao = new UserManagement();
uObj = uDao.getUserByID(Session["userID"].ToString());
SqlCommand cmd = new SqlCommand(
" select AdvID, [Company].Name as CompanyName ,[Advertisement].Name as AdvertName,[Advertisement].Item,[Advertisement].ItemType,[Advertisement].StartDate,[Advertisement].EndDate" +
" from [Advertisement] inner join [Company] on [Advertisement].CompanyID =[Company].CompanyID " +
"where [Company].CompanyID=@ID and [Advertisement].status=1 order by " + e.SortExpression + " " + strSortDirection, conn);
// 2. define parameters used in command object
SqlParameter param = new SqlParameter();
param.ParameterName = "@ID";
param.Value = uObj.CompanyID.ToString();
// 3. add new parameter to command object
cmd.Parameters.Add(param);
SqlDataAdapter sda = new SqlDataAdapter();
DataTable dt = new DataTable();
cmd.Connection = conn;
sda.SelectCommand = cmd;
sda.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
protected void ButtonConfirm_Click(object sender, EventArgs e)
{
//initialise imagelink and getvalue
string imagelink = "";
string getvalue = "";
for (int i = 0; i < CheckBoxList2.Items.Count; i++)
{
if (CheckBoxList2.Items[i].Selected)
{
getvalue += CheckBoxList2.Items[i].Text + ",";
getvalue = getvalue.TrimEnd();
}
}
//if uploaded file then save
if (FileUpload1.HasFile)
{
string fileExt = System.IO.Path.GetExtension(FileUpload1.FileName);
FileUpload1.SaveAs(Server.MapPath("~/Images/") + FileUpload1.FileName);
}
//if any field missing give warning!
//unused codes
if (Literal1.Text == "" || startDateTB.Text == "" ||
endDateTB.Text == "" || adCategoryTB.Text == "" || billboardDisplayTB.Text == "" || getvalue == "")
{
//alertWarning.Visible = true;
//warningLocation.Text = "Please ensure you have filled in all required fields";
}
//if never agree to terms and conditions,display warning
//unused codes
else if (CheckBox1.Checked == false)
{
//alertWarning.Visible = true;
//warningLocation.Text = "Please agree with T&C";
}
else
{
//alertWarning.Visible = false;
DateTime aDate = DateTime.Now;
imagelink = "Images/" + Literal1.Text;
DateTime sdate = DateTime.Parse(startDateTB.Text);
DateTime edate = DateTime.Parse(endDateTB.Text);
int companyID = Convert.ToInt32(DropDownListCompany.SelectedItem.Value);
int AdvertisementID = GetMaxIDAdvertisement();
string mainconn = ConfigurationManager.ConnectionStrings["Targeted_Marketing_DisplayConnectionString"].ConnectionString;
SqlConnection sqlconn = new SqlConnection(Reference.Constr);
String adv = "Insert into [Advertisement](Name,Item,ItemType,Duration,CompanyID,StartDate,EndDate,Status,CreatedBy,CreatedOn)" +
" Values(@Name,@Item,@ItemType,@Duration,@CompanyID,@StartDate,@EndDate,@Status,@CreatedBy,@CreatedOn)";
SqlCommand sqlcomm = new SqlCommand(adv);
sqlcomm.Connection = sqlconn;
sqlconn.Open();
if ((string)Session["userType"] == Reference.USR_ADM)
{
sqlcomm.Parameters.AddWithValue("@CreatedOn", DateTime.Now);
sqlcomm.Parameters.AddWithValue("@Name", adNameTB.Text);
sqlcomm.Parameters.AddWithValue("@Item", imagelink);
sqlcomm.Parameters.AddWithValue("@ItemType", Literal2.Text);
sqlcomm.Parameters.AddWithValue("@StartDate", sdate);
sqlcomm.Parameters.AddWithValue("@EndDate", edate);
sqlcomm.Parameters.AddWithValue("@CompanyID", companyID);
sqlcomm.Parameters.AddWithValue("@Status", "1");
sqlcomm.Parameters.AddWithValue("@CreatedBy", "2");
sqlcomm.Parameters.AddWithValue("@Duration", videoDurationTB.Text);
sqlcomm.ExecuteNonQuery();
sqlconn.Close();
}
else
{
User userObj = new User();
UserManagement uDao = new UserManagement();
userObj = uDao.getUserByID(Session["userID"].ToString());
sqlcomm.Parameters.AddWithValue("@CreatedOn", DateTime.Now);
sqlcomm.Parameters.AddWithValue("@Name", adNameTB.Text);
sqlcomm.Parameters.AddWithValue("@Item", imagelink);
sqlcomm.Parameters.AddWithValue("@ItemType", Literal2.Text);
sqlcomm.Parameters.AddWithValue("@StartDate", sdate);
sqlcomm.Parameters.AddWithValue("@EndDate", edate);
sqlcomm.Parameters.AddWithValue("@CompanyID", userObj.CompanyID);
sqlcomm.Parameters.AddWithValue("@Status", "1");
sqlcomm.Parameters.AddWithValue("@CreatedBy", "2");
sqlcomm.Parameters.AddWithValue("@Duration", videoDurationTB.Text);
sqlcomm.ExecuteNonQuery();
sqlconn.Close();
}
SqlConnection sqlcon = new SqlConnection(Reference.Constr);
string sqlquery = "Insert into [AdvertisementCategory](AdvID,CategoryID) values(@AdvID,@CategoryID)";
SqlCommand sqlcom = new SqlCommand(sqlquery, sqlcon);
sqlcon.Open();
string str = adCategoryTB.Text;
string[] splitstr = str.Split(',');
int id = GetMaxIDAdvertisement();
foreach (string s in splitstr)
{
//trim the string, i.e. remove the space if any
string _s = s;
_s = _s.Trim();
sqlcom.Parameters.AddWithValue("@AdvID", id);
//sqlcom.Parameters.AddWithValue("@CategoryID", s);
sqlcom.Parameters.AddWithValue("@CategoryID", _s);
sqlcom.ExecuteNonQuery();
sqlcom.Parameters.Clear();
}
sqlcon.Close();
List <int> ListOfID = new List <int>();
SqlConnection sqlconnn = new SqlConnection(Reference.Constr);
string sqlqueryy = "Insert into [AdvertisementLocation](AdvID,BillboardID) values(@AdvID,@BillboardID)";
SqlCommand sqlcommm = new SqlCommand(sqlqueryy, sqlconnn);
sqlconnn.Open();
int AdvId = GetMaxIDAdvertisement();
//Label bblabel = (Label)gvr.FindControl("lb_BillboardID");
for (int i = 0; i < GridView1.Rows.Count; i++)
{
//Label bblabel = (Label)gvr.FindControl("lb_BillboardID");
// billboardDisplayTB.Text = billboardDisplayTB.Text + "," + bblabel.Text;
GridViewRow row = GridView1.Rows[i];
bool chkbx = ((CheckBox)row.FindControl("CheckBoxSelector")).Checked;
if (chkbx)
{
Label bblabel = (Label)GridView1.Rows[i].FindControl("lb_BillboardID");
sqlcommm.Parameters.AddWithValue("@BillboardID", Convert.ToInt32(bblabel.Text));
sqlcommm.Parameters.AddWithValue("@AdvID", AdvId);
sqlcommm.ExecuteNonQuery();
sqlcommm.Parameters.Clear();
}
}
sqlconnn.Close();
SqlConnection sqlcn = new SqlConnection(Reference.Constr);
string sqlque = "Insert into [AdvertisementAudience](AdvID,AgeID,GenderID) values(@AdvID,@AgeID,@GenderID)";
SqlCommand sqlcm = new SqlCommand(sqlque, sqlcn);
sqlcn.Open();
int ID_audience = GetMaxIDAdvertisement();
for (int i = 0; i < CheckBoxList2.Items.Count; i++)
{
if (CheckBoxList2.Items[i].Selected == true)
{
string stri = string.Empty;
stri = CheckBoxList2.Items[i].ToString();
if (stri.Contains("Male") & stri.Contains("Child"))
{
sqlcm.Parameters.AddWithValue("@GenderID", "M");
sqlcm.Parameters.AddWithValue("@AgeID", "1");
}
else if (stri.Contains("Male") & stri.Contains("Young Adult"))
{
sqlcm.Parameters.AddWithValue("@GenderID", "M");
sqlcm.Parameters.AddWithValue("@AgeID", "2");
}
else if (stri.Contains("Male") & stri.Contains("Age 31-65"))
{
sqlcm.Parameters.AddWithValue("@GenderID", "M");
sqlcm.Parameters.AddWithValue("@AgeID", "3");
}
else if (stri.Contains("Male") & stri.Contains("Senior"))
{
sqlcm.Parameters.AddWithValue("@GenderID", "M");
sqlcm.Parameters.AddWithValue("@AgeID", "4");
}
else if (stri.Contains("Female") & stri.Contains("Child"))
{
sqlcm.Parameters.AddWithValue("@GenderID", "F");
sqlcm.Parameters.AddWithValue("@AgeID", "1");
}
else if (stri.Contains("Female") & stri.Contains("Young Adult"))
{
sqlcm.Parameters.AddWithValue("@GenderID", "F");
sqlcm.Parameters.AddWithValue("@AgeID", "2");
}
else if (stri.Contains("Female") & stri.Contains("Age 31-65"))
{
sqlcm.Parameters.AddWithValue("@GenderID", "F");
sqlcm.Parameters.AddWithValue("@AgeID", "3");
}
else if (stri.Contains("Female") & stri.Contains("Senior"))
{
sqlcm.Parameters.AddWithValue("@GenderID", "F");
sqlcm.Parameters.AddWithValue("@AgeID", "4");
}
sqlcm.Parameters.AddWithValue("@AdvID", ID_audience);
sqlcm.ExecuteNonQuery();
sqlcm.Parameters.Clear();
}
}
sqlcn.Close();
adNameTB.Text = string.Empty;
DropDownListCompany.SelectedIndex = 0;
startDateTB.Text = string.Empty;
endDateTB.Text = string.Empty;
videoDurationTB.Text = string.Empty;
adCategoryTB.Text = string.Empty;
billboardDisplayTB.Text = string.Empty;
for (int i = 0; i < CheckBoxList1.Items.Count; i++)
{
CheckBoxList1.Items[i].Selected = false;
}
for (int i = 0; i > CheckBoxList2.Items.Count; i++)
{
CheckBoxList2.Items[i].Selected = false;
}
//alertWarning.Visible = false;
//alertSuccess.Visible = true;
Session["AdvertCreate"] = 2;
Response.Redirect("AdvertList.aspx");
}
}
