protected void RegisterButton_Click(object sender, EventArgs e)
{
AccessDB dbObj = new AccessDB();
dbObj.Open();
//verify if registration record already exists
dbObj.Query = string.Format("select userregisterationid from tbluserregisteration where EmailID='{0}'", Email.Text); ;
dbObj.ExecuteQuery();
if (dbObj.Dataset.Tables[0].Rows.Count > 0)
{
ConfirmationLabel.Style.Add("color", "Red");
ConfirmationLabel.Text = string.Format("Sorry! Our system shows an account is already registered with this email -{0}. If you have forgotten your password, use the 'Forgot Password' link to get your password.", Email.Text);
ConfirmationLabel.Visible = true;
}
else
{
dbObj.Dataset.Reset();
//Insert details into the db
dbObj.Query = string.Format(@"insert into tblUserRegisteration (FirstName,LastName,FamilyBranch,BornInto,HomePhone,EmailID,Passwd,Address1,Address2,City,State,Country,Pincode,RegistrationDate)
values('{0}','{1}','{2}','{3}','{4}','{5}',AES_ENCRYPT('{6}','{7}'),'{8}','{9}','{10}','{11}','{12}',{13},current_timestamp()); "
, FirstName.Text, LastName.Text, FamilyBranch.SelectedValue, rdlConnection.SelectedValue, PhoneNumber.Text, Email.Text, Password.Text, Constants.AESKey, Address1.Text, Address2.Text, CityDitrict.Text, State.Text
, Country.Text, int.Parse(PinCode.Text));
dbObj.ExecuteNonQuery();
//Get the current users' userregisterationid
dbObj.Dataset.Reset();
dbObj.Query = string.Format("select userregisterationid, registrationDate from tbluserregisteration where EmailID='{0}' and FirstName='{1}' and LastName='{2}'", Email.Text, FirstName.Text, LastName.Text); ;
dbObj.ExecuteQuery();
long UserRegID = (long)dbObj.Dataset.Tables[0].Rows[0][0];
DateTime dtReg = (DateTime)dbObj.Dataset.Tables[0].Rows[0][1];
//Mail to admin
string mailBody = string.Format(Constants.AdminMailText) + Environment.NewLine + string.Format("\n\nFirstName:{0}\nLastName:{1}\nFamily:{2}\nAddress:{3}\nEmail:{4}\nHomePhone:{5}\nRegisteration Date:{6}"
, FirstName.Text, LastName.Text, FamilyBranch.Text, string.Concat(Address1.Text, ",", Address2.Text, ",", CityDitrict.Text, "-", PinCode.Text, ",", State.Text, ",", Country.Text, ","), Email.Text, PhoneNumber.Text, dtReg)
+ Environment.NewLine + "http://www.Kallivayalil.com/ActivateUser.aspx?UserRegID=" + UserRegID.ToString();
dbObj.Dataset.Reset();
dbObj.Query = string.Format(@"Select emailaddress from tbluserlogin where isadmin=true");
dbObj.ExecuteQuery();
for (int i = 0; i < dbObj.Dataset.Tables[0].Rows.Count; i++)
{
SendMailMessage(dbObj.Dataset.Tables[0].Rows[i][0].ToString(), "*****@*****.**", mailBody);
}
//mail to user.
mailBody = string.Empty;
mailBody = string.Format(Constants.UserMailText) + Environment.NewLine + string.Format("\n\nFirstName:{0}\nLastName:{1}\nFamily:{2}\nAddress:{3}\nEmail:{4}\nHomePhone:{5}\nRegistration Date:{6}"
, FirstName.Text, LastName.Text, FamilyBranch.Text, string.Concat(Address1.Text, ",", Address2.Text, ",", CityDitrict.Text, "-", PinCode.Text, ",", State.Text, ",", Country.Text, ","), Email.Text, PhoneNumber.Text, dtReg);
SendMailMessage(Email.Text, "RegistrationMail @ Kallivayalil.com", mailBody);
ConfirmationLabel.Style.Add("color", "Green");
ConfirmationLabel.Text = "Thank You for registering. A confirmation email has been sent to the emailaddress you provided. We will review your information and your account will be activated at the earliest.";
ConfirmationLabel.Visible = true;
UserRegistration.Visible = false;
}
dbObj.Close();
}
protected void AddEvntBtn_Click(object sender, EventArgs e)
{
dbObj = new AccessDB();
dbObj.Open();
bool isPublic = false;
if (MemOnlyRb.Checked)
isPublic = true;
dbObj.Query = string.Format(@"insert into tblspecialevents(EventName,EventType,EventDetails,StartDate,EventDate,ContactPerson,ContactNumber,IsPublic,CreatedBy,updatedby,updateddate) values
('{0}','{1}','{2}','{3}','{4}','{5}','{6}',{7},'{8}','{8}',curdate())"
, EvntNameTbx.Text, EvntTypeDdl.SelectedValue, EvntDescTbx.Text, Convert.ToDateTime(StartDtTbx.Text).ToString("yyyy-MM-dd hh:mm:ss"), Convert.ToDateTime(EndDtTbx.Text).ToString("yyyy-MM-dd hh:mm:ss"), ContactPersonTbx.Text, ContactNumTbx.Text,
isPublic, Session["UserName"]);
dbObj.ExecuteNonQuery();
dbObj.Close();
divSuccessLabel.Visible = true;
divAddevent.Visible = false;
}
protected void btnCrop_Click(object sender, EventArgs e)
{
try
{
if (W.Value != string.Empty)
{
int w = Convert.ToInt32(W.Value);
int h = Convert.ToInt32(H.Value);
int x = Convert.ToInt32(X.Value);
int y = Convert.ToInt32(Y.Value);
AccessDB dbObj = new AccessDB();
dbObj.Open();
dbObj.Query = "Select Photo from tblSpouse where spID=?";
dbObj.command.Parameters.Add(new System.Data.Odbc.OdbcParameter("spID", Session["queryID"]));
byte[] _buf = (byte[])dbObj.ExecuteScalar();
dbObj.Close();
MemoryStream msOrig = new MemoryStream(_buf);
byte[] CropImage = Crop(msOrig, w, h, x, y);
using (MemoryStream ms = new MemoryStream(CropImage, 0, CropImage.Length))
{
ms.Write(CropImage, 0, CropImage.Length);
Byte[] Cropped = ms.ToArray();
AccessDB dbObj1 = new AccessDB();
dbObj1.Open();
dbObj1.Query = "Update tblSpouse SET Photo=? where SpID=?";
dbObj1.command.Parameters.Add(new System.Data.Odbc.OdbcParameter("Photo", Cropped));
dbObj.command.Parameters.Add(new System.Data.Odbc.OdbcParameter("SpID", Session["queryID"]));
dbObj1.command.Prepare();
dbObj1.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
}
finally
{
pnlCrop.Visible = false;
pnlCropped.Visible = true;
}
}
public int InsertUser(UserEntity user)
{
SqlCommand command = new SqlCommand();
command.CommandType = CommandType.Text;
command.CommandText = "INSERT INTO [User] VALUES ('" + user.UserName + "', '" + user.Name + "', '" + user.Password + "', '" + user.Email + "', " + user.ContactNo + ")";
return(accessDB.ExecuteNonQuery(command));
}
private void registerNewUser(out string emailID, out string activatedUser)
{
newUser = true;
dbObj = new AccessDB();
dbObj.Open();
bool isAdmin = false;
char ProfileType = 'U';
if (CheckBox1.Checked)
isAdmin = true;
//Fetch the row for the registeration table.
dbObj.Query = string.Format(@"Select borninto,emailID,passwd,firstName,LastName,FamilyBranch,HomePhone
,Address1,Address2,city,state,country,pincode from tbluserregisteration
where userregisterationid={0} "
, Session["UserRegID"]);
dbObj.ExecuteQuery();
emailID = (string)dbObj.Dataset.Tables[0].Rows[0][1];
string borninto = dbObj.Dataset.Tables[0].Rows[0][0].ToString();
//Add entry to the profile table and set the selectedProfileID to the new entry's ID.
if (dbObj.Dataset.Tables[0].Rows[0][0].ToString() == "B")
{
//insert into the profile table -> borninto is true
dbObj.Query = string.Format(@"insert into tbluserprofile (firstname,lastname,familyBranch,homephone,address1,address2,city
,state,country,pincode,createddate,isactive) values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}',curdate(),1)"
, dbObj.Dataset.Tables[0].Rows[0][3], dbObj.Dataset.Tables[0].Rows[0][4], dbObj.Dataset.Tables[0].Rows[0][5]
, dbObj.Dataset.Tables[0].Rows[0][6], dbObj.Dataset.Tables[0].Rows[0][7], dbObj.Dataset.Tables[0].Rows[0][8]
, dbObj.Dataset.Tables[0].Rows[0][9], dbObj.Dataset.Tables[0].Rows[0][10], dbObj.Dataset.Tables[0].Rows[0][11]
, dbObj.Dataset.Tables[0].Rows[0][12]);
dbObj.ExecuteNonQuery();
//fetch the last inserted userprofileid from tbluserprofile
dbObj.Dataset.Reset();
dbObj.Query = "select max(userprofileid) from tbluserprofile";
dbObj.ExecuteQuery();
Session.Add("SelectedProfileID", dbObj.Dataset.Tables[0].Rows[0][0]);
}
else
{
//insert into the spouse table when borninto is false.
dbObj.Query = string.Format(@"insert into tblspouse (firstname,lastname,mobilephone,emailaddress,activationdate,activatedby,isactive) values ('{0}','{1}','{2}','{3}',curdate(),'{4}',1)"
, dbObj.Dataset.Tables[0].Rows[0][3], dbObj.Dataset.Tables[0].Rows[0][4], dbObj.Dataset.Tables[0].Rows[0][6]
, dbObj.Dataset.Tables[0].Rows[0][1],Session["UserLogin"]);
dbObj.ExecuteNonQuery();
//fetchin the spouseid field is an issue here ...
//fetch the last inserted userprofileid from tbluserprofile
dbObj.Dataset.Reset();
dbObj.Query = "select max(spid) from tblspouse";
dbObj.ExecuteQuery();
Session.Add("SelectedProfileID", dbObj.Dataset.Tables[0].Rows[0][0]);
ProfileType = 'S';
}
//Fetch the row for the registeration table.
dbObj.Dataset.Reset();
dbObj.Query = string.Format(@"Select borninto,emailID,passwd,firstName,LastName,FamilyBranch,HomePhone
,Address1,Address2,city,state,country,pincode from tbluserregisteration
where userregisterationid={0} "
, Session["UserRegID"]);
dbObj.ExecuteQuery();
//insert a row into the tbluserlogin table.Link the profile ID to the Login table.
string temp = string.Format(@"Insert into tbluserlogin (userprofileid,profileType,emailaddress,passwd,
creationdate,updationdate,updatedby,activationdate,activatedby
,isadmin) values({0},'{6}','{1}',(select passwd as pass from tbluserregisteration where userregisterationid = {5}),curdate(),curdate(),'{2}',curdate(),'{3}',{4})",
Session["SelectedProfileID"], dbObj.Dataset.Tables[0].Rows[0][1]
, Session["UserLogin"], Session["UserLogin"], isAdmin, Session["UserRegID"], ProfileType);
dbObj.Query = temp;
dbObj.ExecuteNonQuery();
isAdmin = false;
if (borninto == "B")
{
if (RadioButtonList1.Items[0].Selected)
{
//update isactive,updation date,updated by in tbluserprofile.
dbObj.Query = string.Format(@"Update tbluserprofile set Isactive=true,createddate=curdate()
,updateddate=curdate(),updatedby='{0}',firstName='{2}',lastName='{3}',FamilyBranch='{4}',HomePhone='{5}'
,Address1='{6}',Address2='{7}',city='{8}',state='{9}',country='{10}',pincode='{11}' where UserProfileID = {1}"
, Session["UserLogin"], Session["SelectedProfileID"], dbObj.Dataset.Tables[0].Rows[0][2]
, dbObj.Dataset.Tables[0].Rows[0][3], dbObj.Dataset.Tables[0].Rows[0][4], dbObj.Dataset.Tables[0].Rows[0][5]
, dbObj.Dataset.Tables[0].Rows[0][6], dbObj.Dataset.Tables[0].Rows[0][7], dbObj.Dataset.Tables[0].Rows[0][8]
, dbObj.Dataset.Tables[0].Rows[0][9], dbObj.Dataset.Tables[0].Rows[0][10], dbObj.Dataset.Tables[0].Rows[0][11]);
dbObj.ExecuteNonQuery();
}
else if (RadioButtonList1.Items[1].Selected)
{
//update isactive,updation date,updated by in tbluserprofile.
dbObj.Query = string.Format(@"Update tbluserprofile set Isactive=true,createddate=curdate()
,updateddate=curdate(),updatedby='{0}' where UserProfileID = {1}"
, Session["UserLogin"], Session["SelectedProfileID"]);
dbObj.ExecuteNonQuery();
}
}
else if (borninto == "M")
{
if (RadioButtonList1.Items[0].Selected)
{
//update isactive,updation date,updated by in tbluserprofile.
dbObj.Query = string.Format(@"Update tblspouse set Isactive=true
,updateddate=curdate(),updatedby='{0}',firstName='{2}',lastName='{3}',mobilePhone='{4}'
emailID='{5}' where UserProfileID = {1}"
, Session["UserLogin"], Session["SelectedProfileID"], dbObj.Dataset.Tables[0].Rows[0][2]
, dbObj.Dataset.Tables[0].Rows[0][3], dbObj.Dataset.Tables[0].Rows[0][5], dbObj.Dataset.Tables[0].Rows[0][0]
);
dbObj.ExecuteNonQuery();
}
else if (RadioButtonList1.Items[1].Selected)
{
//update isactive,updation date,updated by in tbluserprofile.
dbObj.Query = string.Format(@"Update tblspouse set Isactive=true,activationdate=curdate(),activatedby = '{0}'
,updateddate=curdate(),updatedby='{0}' where UserProfileID = {1}"
, Session["UserLogin"], Session["SelectedProfileID"]);
dbObj.ExecuteNonQuery();
}
}
dbObj.Dataset.Reset();
dbObj.Query = string.Format(@"select emailID from tbluserregisteration where userregisterationid={0} "
, Session["UserRegID"]);
dbObj.ExecuteQuery();
activatedUser = dbObj.Dataset.Tables[0].Rows[0][0].ToString();
//delete entry from the registeration table.
dbObj.Query = string.Format(@"delete from tbluserregisteration
where userregisterationid={0} "
, Session["UserRegID"]);
dbObj.ExecuteNonQuery();
}
private void registerExistingUser(out string emailID, out string activatedUser)
{
dbObj = new AccessDB();
dbObj.Open();
bool isAdmin = false;
char ProfileType = 'U';
//Fetch the row for the registeration table.
dbObj.Query = string.Format(@"Select borninto,emailID,passwd,firstName,LastName,FamilyBranch,HomePhone
,Address1,Address2,city,state,country,pincode from tbluserregisteration
where userregisterationid={0} "
, Session["UserRegID"]);
dbObj.ExecuteQuery();
if (CheckBox1.Checked)
isAdmin = true;
if (dbObj.Dataset.Tables[0].Rows[0][0].ToString() == "M")
ProfileType = 'S';
//insert a row into the tbluserlogin table.Link the profile ID to the Login table.
string temp = string.Format(@"Insert into tbluserlogin (userprofileid,profiletype,emailaddress,passwd,
creationdate,updationdate,updatedby,activationdate,activatedby
,isadmin) values({0},'{6}','{1}',(select passwd as pass from tbluserregisteration where userregisterationid = {5}),curdate(),curdate(),'{2}',curdate(),'{3}',{4})",
Session["SelectedProfileID"], dbObj.Dataset.Tables[0].Rows[0][1]
, Session["UserLogin"], Session["UserLogin"], isAdmin, Session["UserRegID"],ProfileType);
emailID = (string)dbObj.Dataset.Tables[0].Rows[0][1];
dbObj.Query = temp;
dbObj.ExecuteNonQuery();
isAdmin = false;
if (RadioButtonList1.Items[0].Selected)
{
//update isactive,updation date,updated by in tbluserprofile.
dbObj.Query = string.Format(@"Update tbluserprofile set Isactive=true,createddate=curdate()
,updateddate=curdate(),updatedby='{0}',firstName='{2}',lastName='{3}',FamilyBranch='{4}',HomePhone='{5}'
,Address1='{6}',Address2='{7}',city='{8}',state='{9}',country='{10}',pincode='{11}' where UserProfileID = {1}"
, Session["UserLogin"], Session["SelectedProfileID"], dbObj.Dataset.Tables[0].Rows[0][4]
, dbObj.Dataset.Tables[0].Rows[0][5], dbObj.Dataset.Tables[0].Rows[0][6], dbObj.Dataset.Tables[0].Rows[0][7]
, dbObj.Dataset.Tables[0].Rows[0][8], dbObj.Dataset.Tables[0].Rows[0][9], dbObj.Dataset.Tables[0].Rows[0][10]
, dbObj.Dataset.Tables[0].Rows[0][11], dbObj.Dataset.Tables[0].Rows[0][12], dbObj.Dataset.Tables[0].Rows[0][13]);
dbObj.ExecuteNonQuery();
}
else if (RadioButtonList1.Items[1].Selected)
{
//update isactive,updation date,updated by in tbluserprofile.
dbObj.Query = string.Format(@"Update tbluserprofile set Isactive=true,createddate=curdate()
,updateddate=curdate(),updatedby='{0}' where UserProfileID = {1}"
, Session["UserLogin"], Session["SelectedProfileID"]);
dbObj.ExecuteNonQuery();
}
dbObj.Query = string.Format(@"select emailID from tbluserregisteration where userregisterationid={0} "
, Session["UserRegID"]);
dbObj.ExecuteQuery();
activatedUser = dbObj.Dataset.Tables[0].Rows[0][0].ToString();
//delete entry from the registeration table.
dbObj.Query = string.Format(@"delete from tbluserregisteration
where userregisterationid={0} "
, Session["UserRegID"]);
dbObj.ExecuteNonQuery();
}
protected void btnUpload_Click(object sender, EventArgs e)
{
Boolean FileOK = false;
Boolean FileSaved = false;
if (FileUpload1.HasFile)
{
Session["WorkingImage"] = FileUpload1.FileName;
String FileExtension = Path.GetExtension(Session["WorkingImage"].ToString()).ToLower();
String[] allowedExtensions = { ".png", ".jpeg", ".jpg", ".gif" };
for (int i = 0; i < allowedExtensions.Length; i++)
{
if (FileExtension == allowedExtensions[i])
{
FileOK = true;
}
}
}
if (FileOK)
{
try
{
Byte[] file = FileUpload1.FileBytes;
AccessDB dbObj = new AccessDB();
dbObj.Open();
dbObj.Query = "Update tblUserProfile SET Photo=? where UserProfileID=?";
dbObj.command.Parameters.Add(new System.Data.Odbc.OdbcParameter("Photo", file));
dbObj.command.Parameters.Add(new System.Data.Odbc.OdbcParameter("UserProfileID", Session["ID"]));
dbObj.command.Prepare();
dbObj.ExecuteNonQuery();
dbObj.Close();
FileSaved = true;
}
catch (Exception ex)
{
lblError.Text = "File could not be uploaded." + ex.Message.ToString();
lblError.Visible = true;
FileSaved = false;
}
}
else
{
lblError.Text = "Cannot accept files of this type.";
lblError.Visible = true;
}
if (FileSaved)
{
pnlUpload.Visible = false;
pnlCrop.Visible = true;
pnlCropped.Visible = false;
}
}