public int Insert(JE_TourGuideInfo TG)
{
// Execute NonQuery return an integer value
int result = 0;
SqlCommand sqlCmd = new SqlCommand();
//Step 1 - Define a connection to the database by getting
// the connection string from web.config
string DBConnect = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
SqlConnection myConn = new SqlConnection(DBConnect);
// Step 2 - Instantiate SqlCommand instance to add record
// with INSERT statement
string sqlStmt = "INSERT INTO JE_TDTGInfo ([TGId],[TGName],[TGFirstName],[TGLastName], [TGMobile],[TGEmail]) " +
"VALUES (@paraTGId,@paraTGName,@paraTGFirstName,@paraTGLastName, @paraTGMobile, @paraTGEmail)";
sqlCmd = new SqlCommand(sqlStmt, myConn);
// Step 3 : Add each parameterised variable with value
sqlCmd.Parameters.AddWithValue("@paraTGId", TG.TGId);
sqlCmd.Parameters.AddWithValue("@paraTGName", TG.TGName);
sqlCmd.Parameters.AddWithValue("@paraTGFirstName", TG.TGFirstName);
sqlCmd.Parameters.AddWithValue("@paraTGLastName", TG.TGLastName);
sqlCmd.Parameters.AddWithValue("@paraTGMobile", TG.TGMobile);
sqlCmd.Parameters.AddWithValue("@paraTGEmail", TG.TGEmail);
// Step 4 Open connection the execute NonQuery of sql command
myConn.Open();
result = sqlCmd.ExecuteNonQuery();
// Step 5 :Close connection
myConn.Close();
return(result);
}
protected void Page_Load(object sender, EventArgs e)
{
Session["SSTGEmail"] = "*****@*****.**";
//JE_TourGuideInfo TG = new JE_TourGuideInfo();
JE_TourGuideInfo TG = new JE_TourGuideInfo();
//TG.GetTGByEmail(Session["SSTGEmail"].ToString());
var name = TG.GetTGByEmail(Session["SSTGEmail"].ToString()).TGName;
var intro = TG.GetTGByEmail(Session["SSTGEmail"].ToString()).IntroInProfile;
var profilepic = TG.GetTGByEmail(Session["SSTGEmail"].ToString()).TGProfilePic;
var nricfront = TG.GetTGByEmail(Session["SSTGEmail"].ToString()).Nric_front;
var nricback = TG.GetTGByEmail(Session["SSTGEmail"].ToString()).Nric_back;
ProfilePageTGName.InnerText = name;
ProfilePageTGIntro.InnerText = intro;
ProfilePicDisplay.Src = profilepic;
if (nricfront != "")
{
profilenricfront.Src = nricfront;
profilenricback.Src = nricback;
}
//ProfilePageTGName.InnerText = (string)Session["SSTGName"];
//ProfilePicDisplay.Src = (string)Session[""];
//ProfilePageTGIntro.InnerText = (string)Session["SSTGIntro"];
}
//public int InsertForm(JE_TourGuideInfoForm TG)
//{
// // Execute NonQuery return an integer value
// int result = 0;
// SqlCommand sqlCmd = new SqlCommand();
// //Step 1 - Define a connection to the database by getting
// // the connection string from web.config
// string DBConnect = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
// SqlConnection myConn = new SqlConnection(DBConnect);
// // Step 2 - Instantiate SqlCommand instance to add record
// // with INSERT statement
// byte[] bytes;
// using (BinaryReader br = new BinaryReader(TG.TGProfilePic.PostedFile.InputStream))
// {
// bytes = br.ReadBytes(TG.TGProfilePic.PostedFile.ContentLength);
// }
// string sqlStmt = "UPDATE JE_TDTGInfo SET TGProfilePic=@paraTGProfilePic,Bank_num=@paraAccountNum where TGEmail=@paraTGEmail";
// sqlCmd = new SqlCommand(sqlStmt, myConn);
// // Step 3 : Add each parameterised variable with value
// sqlCmd.Parameters.AddWithValue("@paraTGEmail", TG.TGEmail);
// sqlCmd.Parameters.AddWithValue("@paraTGProfilePic", bytes);
// sqlCmd.Parameters.AddWithValue("@paraAccountNum", TG.Bank_num);
// // Step 4 Open connection the execute NonQuery of sql command
// myConn.Open();
// result = sqlCmd.ExecuteNonQuery();
// // Step 5 :Close connection
// myConn.Close();
// return result;
//}
//protected void Upload(object sender, EventArgs e)
//{
// byte[] bytes;
// using (BinaryReader br = new BinaryReader(imgProfile.PostedFile.InputStream))
// {
// bytes = br.ReadBytes(FileUpload1.PostedFile.ContentLength);
// }
// string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
// using (SqlConnection conn = new SqlConnection(constr))
// {
// string sql = "INSERT INTO tblFiles VALUES(@Name, @ContentType, @Data)";
// using (SqlCommand cmd = new SqlCommand(sql, conn))
// {
// cmd.Parameters.AddWithValue("@Name", Path.GetFileName(FileUpload1.PostedFile.FileName));
// cmd.Parameters.AddWithValue("@ContentType", FileUpload1.PostedFile.ContentType);
// cmd.Parameters.AddWithValue("@Data", bytes);
// conn.Open();
// cmd.ExecuteNonQuery();
// conn.Close();
// }
// }
// Response.Redirect(Request.Url.AbsoluteUri);
//}
public JE_TourGuideInfo SelectByEmail(string emaill)
{
//Step 1 - Define a connection to the database by getting
// the connection string from web.config
string DBConnect = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
SqlConnection myConn = new SqlConnection(DBConnect);
//Step 2 - Create a DataAdapter to retrieve data from the database table
string sqlStmt = "Select * from JE_TDTGInfo where TGEmail = @paraEmail";
SqlDataAdapter da = new SqlDataAdapter(sqlStmt, myConn);
da.SelectCommand.Parameters.AddWithValue("@paraEmail", emaill);
//Step 3 - Create a DataSet to store the data to be retrieved
DataSet ds = new DataSet();
//Step 4 - Use the DataAdapter to fill the DataSet with data retrieved
da.Fill(ds);
//Step 5 - Read data from DataSet.
JE_TourGuideInfo TG = null;
int rec_cnt = ds.Tables[0].Rows.Count;
if (rec_cnt == 1)
{
DataRow row = ds.Tables[0].Rows[0]; // Sql command returns only one record
string id = row["TGId"].ToString();
string name = row["TGName"].ToString();
string first = row["TGFirstName"].ToString();
string last = row["TGLastName"].ToString();
string email = row["TGEmail"].ToString();
string mobile = row["TGMobile"].ToString();
string profilepic = row["TGProfilePic"].ToString();
string nricfront = row["NRIC_front"].ToString();
string nricback = row["NRIC_back"].ToString();
string licenseyn = row["LicenseYN"].ToString();
string licensefront = row["License_front"].ToString();
string licenseback = row["License_back"].ToString();
string ACRA1 = row["ACRA1"].ToString();
string ACRA2 = row["ACRA2"].ToString();
string ACRA3 = row["ACRA3"].ToString();
string type_of_bank = row["Type_of_bank"].ToString();
string bank_num = row["Bank_num"].ToString();
string expertise = row["Expertise"].ToString();
string workhowlong = row["WorkHowLong"].ToString();
string introinprofile = row["IntroInProfile"].ToString();
TG = new JE_TourGuideInfo(id, name, first, last, email, mobile, profilepic, nricfront, nricback, licenseyn, licensefront, licensefront, ACRA1, ACRA2, ACRA3, type_of_bank, bank_num, expertise, workhowlong, introinprofile);
}
else
{
TG = null;
}
return(TG);
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
JE_TourGuideInfo TG = new JE_TourGuideInfo();
SMSSvrRef.SMSSoapClient S = new SMSSvrRef.SMSSoapClient();
if (TG.GetTGByEmail(tbEmail.Text) != null)
{
lbErrorExist.Text = "Record already exists!";
lbErrorExist.ForeColor = Color.Red;
}
else
{
if (ValidateInput())
{
var id = DateTime.Now.ToString("MMddyyyyHHmmss") + Get10Digits();
var name = tbLastName.Text + " " + tbFirstName.Text;
TG = new JE_TourGuideInfo(id, name, tbFirstName.Text, tbLastName.Text, tbMobile.Text, tbEmail.Text, null, null, null, null, null, null, null, null, null, null, null, null, null, null);
int result = TG.AddTG();
if (result == 1)
{
Session["SSTGName"] = tbLastName.Text + " " + tbFirstName.Text;
Session["SSTGEmail"] = tbEmail.Text;
Session["SSTGMobile"] = tbMobile.Text;
Session["SSTGOTP"] = Get6Digits();
var message = "Your OTP is " + Session["SSTGOTP"].ToString() + " for you to continue to apply as a Tour guide.";
//string display = S.sendMessage("EA02", "609000", Session["SSTGMobile"].ToString(), message );
//var mdlPopup = $find('MODALPOPUPBEHAVIORID');
//if (mdlPopup)
//{
// mdlPopup.show();
// return false;
//}
Response.Redirect("JE_OTP.aspx");
// ClientScript.RegisterStartupScript(this.GetType(), "Popup", "ShowPopup('hello');", true);
}
}
else
{
lbErrorExist.Text = "Error in adding record! Inform System Administrator!";
lbErrorExist.ForeColor = Color.Red;
}
}
}
public int UpdateProfile(JE_TourGuideInfo TG)
{
int result = 0;
SqlCommand sqlCmd = new SqlCommand();
string DBConnect = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
SqlConnection myConn = new SqlConnection(DBConnect);
// Step 2 - Instantiate SqlCommand instance to add record
// with INSERT statement
string sqlStmt = "UPDATE JE_TDTGInfo SET Type_of_bank=@paraType_of_bank ,Bank_num=@paraBank_num,TGProfilePic = @paraTGProfilePic,WorkHowLong=@paraWorkHowLong ,introinprofile=@paraIntroInProfile where TGEmail=@paraTGEmail";
//TGProfilePic = @paraTGProfilePic
sqlCmd = new SqlCommand(sqlStmt, myConn);
// Step 3 : Add each parameterised variable with value
sqlCmd.Parameters.AddWithValue("@paraTGEmail", TG.TGEmail);
if (TG.TGProfilePic.Trim().StartsWith("image/"))
{
sqlCmd.Parameters.AddWithValue("@paraTGProfilePic", TG.TGProfilePic);
}
else
{
sqlCmd.Parameters.AddWithValue("@paraTGProfilePic", "image/" + TG.TGEmail + "/" + TG.TGProfilePic);
}
sqlCmd.Parameters.AddWithValue("@paraType_of_bank", TG.Type_of_bank);
sqlCmd.Parameters.AddWithValue("@paraBank_num", TG.Bank_num);
sqlCmd.Parameters.AddWithValue("@paraWorkHowLong", TG.WorkHowLong);
sqlCmd.Parameters.AddWithValue("@paraIntroInProfile", TG.IntroInProfile);
// Step 4 Open connection the execute NonQuery of sql command
myConn.Open();
result = sqlCmd.ExecuteNonQuery();
// Step 5 :Close connection
myConn.Close();
return(result);
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Session["SSTGEmail"] = "*****@*****.**";
JE_TourGuideInfo TG = new JE_TourGuideInfo();
var name = TG.GetTGByEmail(Session["SSTGEmail"].ToString()).TGName;
var intro = TG.GetTGByEmail(Session["SSTGEmail"].ToString()).IntroInProfile;
var profilepic = TG.GetTGByEmail(Session["SSTGEmail"].ToString()).TGProfilePic;
var nricfront = TG.GetTGByEmail(Session["SSTGEmail"].ToString()).Nric_front;
var nricback = TG.GetTGByEmail(Session["SSTGEmail"].ToString()).Nric_back;
var type = TG.GetTGByEmail(Session["SSTGEmail"].ToString()).Type_of_bank;
var acc = TG.GetTGByEmail(Session["SSTGEmail"].ToString()).Bank_num;
var work = TG.GetTGByEmail(Session["SSTGEmail"].ToString()).WorkHowLong;
updateprofilepic.Src = profilepic;
tbIntroUpdateTG.Text = intro;
UpdateddlTypeBank.SelectedItem.Text = type;
tbUpdateAccNum.Text = acc;
tbWorkhowlong.Text = work;
}
}
protected void btnUpdateProfile_Click(object sender, EventArgs e)
{
JE_TourGuideInfo TG = new JE_TourGuideInfo();
string filenameProfile = null;
if (Updatepic.HasFile == true)
{
filenameProfile = "";
filenameProfile = Path.GetFileName(Updatepic.FileName);
Updatepic.SaveAs(Server.MapPath("~/image/" + Session["SSTGEmail"].ToString() + "/" + filenameProfile));
}
else
{
filenameProfile = TG.GetTGByEmail(Session["SSTGEmail"].ToString()).TGProfilePic;
}
TG = new JE_TourGuideInfo(TG.TGId, TG.TGName, TG.TGFirstName, TG.TGLastName, TG.TGMobile, Session["SSTGEmail"].ToString(), filenameProfile, TG.Nric_front, TG.Nric_back, TG.LicenseYN, TG.License_front, TG.License_back, TG.ACRA1, TG.ACRA2, TG.ACRA3, UpdateddlTypeBank.SelectedValue, tbUpdateAccNum.Text, TG.Expertise, tbWorkhowlong.Text, tbIntroUpdateTG.Text);
int result = TG.UpdateTGProfile();
if (result == 1)
{
Response.Redirect("JE_TGProfile.aspx");
}
}
protected void btnSubmitApplication_Click(object sender, EventArgs e)
{
//JE_TourGuideInfoForm TG = new JE_TourGuideInfoForm();
if (ValidateInput())
{
JE_TourGuideInfo TG = new JE_TourGuideInfo();
Directory.CreateDirectory(Server.MapPath("~/image/" + Session["SSTGEmail"].ToString()));
string filenameProfile = Path.GetFileName(imgProfile.FileName);
imgProfile.SaveAs(Server.MapPath("~/image/" + Session["SSTGEmail"].ToString() + "/" + filenameProfile));
string filenameNricFront = null;
string filenameNricBack = null;
string filenameLicenseFront = null;
string filenameLicenseBack = null;
string filenameACRA1 = null;
string filenameACRA2 = null;
string filenameACRA3 = null;
if (imgNRICFront.HasFiles == true)
{
filenameNricFront = Path.GetFileName(imgNRICFront.FileName);
imgNRICFront.SaveAs(Server.MapPath("~/image/" + Session["SSTGEmail"].ToString() + "/" + filenameNricFront));
}
if (imgNRICBack.HasFiles == true)
{
filenameNricBack = Path.GetFileName(imgNRICBack.FileName);
imgNRICBack.SaveAs(Server.MapPath("~/image/" + Session["SSTGEmail"].ToString() + "/" + filenameNricBack));
}
if (imgLicenseFront.HasFiles == true)
{
filenameLicenseFront = Path.GetFileName(imgLicenseFront.FileName);
imgLicenseFront.SaveAs(Server.MapPath("~/image/" + Session["SSTGEmail"].ToString() + "/" + filenameLicenseFront));
}
if (imgLicenseBack.HasFiles == true)
{
filenameLicenseBack = Path.GetFileName(imgLicenseBack.FileName);
imgLicenseBack.SaveAs(Server.MapPath("~/image/" + Session["SSTGEmail"].ToString() + "/" + filenameLicenseBack));
}
if (imgACRA1.HasFiles == true)
{
filenameACRA1 = Path.GetFileName(imgACRA1.FileName);
imgACRA1.SaveAs(Server.MapPath("~/image/" + Session["SSTGEmail"].ToString() + "/" + filenameACRA1));
}
if (imgACRA2.HasFiles == true)
{
filenameACRA2 = Path.GetFileName(imgACRA2.FileName);
imgACRA2.SaveAs(Server.MapPath("~/image/" + Session["SSTGEmail"].ToString() + "/" + filenameACRA2));
}
if (imgACRA3.HasFiles == true)
{
filenameACRA3 = Path.GetFileName(imgACRA3.FileName);
imgACRA3.SaveAs(Server.MapPath("~/image/" + Session["SSTGEmail"].ToString() + "/" + filenameACRA3));
}
string haveordontLicense = "";
if (rbTrueGL.Checked)
{
haveordontLicense = "Have";
}
else
{
haveordontLicense = "Don't Have";
}
string intro = "Nice To Meet You.";
TG = new JE_TourGuideInfo(TG.TGId, TG.TGName, TG.TGFirstName, TG.TGLastName, TG.TGMobile, Session["SSTGEmail"].ToString(), filenameProfile, filenameNricFront, filenameNricBack, haveordontLicense, filenameLicenseFront, filenameLicenseBack, filenameACRA1, filenameACRA2, filenameACRA3, ddlTypeBank.SelectedValue, tbAccountNum.Text, tbExpertise.Text, tbWorkhowlong.Text, intro);
int result = TG.AddTGForm();
if (result == 1)
{
Session["SSTGId"] = TG.TGId;
Session["SSTGProfile"] = filenameProfile;
Session["SSTGNRICFront"] = filenameNricFront;
Session["SSTGNRICBack"] = filenameNricBack;
Session["SSTGLicenseFront"] = filenameLicenseFront;
Session["SSTGLicenseBack"] = filenameLicenseBack;
Session["SSTGACRA1"] = filenameACRA1;
Session["SSTGACRA2"] = filenameACRA2;
Session["SSTGACRA3"] = filenameACRA3;
Session["SSTGTypeBank"] = ddlTypeBank.SelectedValue;
Session["SSTGAccNum"] = tbAccountNum.Text;
Session["SSTGExpertise"] = tbExpertise.Text;
Session["SSTGWorkHowLong"] = tbWorkhowlong.Text;
Session["SSTGIntro"] = intro;
Response.Redirect("JE_Job.aspx");
}
//int result = TG.AddTG();
}
}
public int AddForm(JE_TourGuideInfo TG)
{
int result = 0;
SqlCommand sqlCmd = new SqlCommand();
string DBConnect = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
SqlConnection myConn = new SqlConnection(DBConnect);
string sqlStmt = "UPDATE JE_TDTGInfo SET Type_of_bank=@paraType_of_bank ,Bank_num=@paraBank_num,TGProfilePic = @paraTGProfilePic,NRIC_front = @paraNRIC_front,NRIC_back = @paraNRIC_back ,LicenseYN=@paraLicenseYN,License_front = @paraLicense_front ,License_back = @paraLicense_back, ACRA1= @paraACRA1,ACRA2 = @paraACRA2,ACRA3 = @paraACRA3,Expertise=@paraExpertise,WorkHowLong=@paraWorkHowLong ,IntroInProfile=@paraIntroInProfile where TGEmail=@paraTGEmail";
//TGProfilePic = @paraTGProfilePic
sqlCmd = new SqlCommand(sqlStmt, myConn);
// Step 3 : Add each parameterised variable with value
sqlCmd.Parameters.AddWithValue("@paraTGEmail", TG.TGEmail);
sqlCmd.Parameters.AddWithValue("@paraTGProfilePic", "image/" + TG.TGEmail + "/" + TG.TGProfilePic);
if (TG.Nric_front == null)
{
sqlCmd.Parameters.AddWithValue("@paraNRIC_front", "");
}
else
{
sqlCmd.Parameters.AddWithValue("@paraNRIC_front", "image/" + TG.TGEmail + "/" + TG.Nric_front);
}
if (TG.Nric_back == null)
{
sqlCmd.Parameters.AddWithValue("@paraNRIC_back", "");
}
else
{
sqlCmd.Parameters.AddWithValue("@paraNRIC_back", "image/" + TG.TGEmail + "/" + TG.Nric_back);
}
if (TG.License_front == null)
{
sqlCmd.Parameters.AddWithValue("@paraLicense_front", "");
}
else
{
sqlCmd.Parameters.AddWithValue("@paraLicense_front", "image/" + TG.TGEmail + "/" + TG.License_front);
}
if (TG.License_back == null)
{
sqlCmd.Parameters.AddWithValue("@paraLicense_back", "");
}
else
{
sqlCmd.Parameters.AddWithValue("@paraLicense_back", "image/" + TG.TGEmail + "/" + TG.License_back);
}
if (TG.ACRA1 == null)
{
sqlCmd.Parameters.AddWithValue("@paraACRA1", "");
}
else
{
sqlCmd.Parameters.AddWithValue("@paraACRA1", "image/" + TG.TGEmail + "/" + TG.ACRA1);
}
if (TG.ACRA2 == null)
{
sqlCmd.Parameters.AddWithValue("@paraACRA2", "");
}
else
{
sqlCmd.Parameters.AddWithValue("@paraACRA2", "image/" + TG.TGEmail + "/" + TG.ACRA2);
}
if (TG.ACRA3 == null)
{
sqlCmd.Parameters.AddWithValue("@paraACRA3", "");
}
else
{
sqlCmd.Parameters.AddWithValue("@paraACRA3", "image/" + TG.TGEmail + "/" + TG.ACRA3);
}
sqlCmd.Parameters.AddWithValue("@paraLicenseYN", TG.LicenseYN);
sqlCmd.Parameters.AddWithValue("@paraType_of_bank", TG.Type_of_bank);
sqlCmd.Parameters.AddWithValue("@paraBank_num", TG.Bank_num);
sqlCmd.Parameters.AddWithValue("@paraExpertise", TG.Expertise);
sqlCmd.Parameters.AddWithValue("@paraWorkHowLong", TG.WorkHowLong);
sqlCmd.Parameters.AddWithValue("@paraIntroInProfile", TG.IntroInProfile);
// Step 4 Open connection the execute NonQuery of sql command
myConn.Open();
result = sqlCmd.ExecuteNonQuery();
// Step 5 :Close connection
myConn.Close();
return(result);
}
