using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; using System.Data.SqlClient; using System.Data; using System.Configuration; using System.Collections; using System.Text; using System.Text.RegularExpressions ; using System.IO; //to use smtp function using System.Net.Mail; using System.Net; using System.Globalization; public partial class Pre_Owned_customasp_editdata : System.Web.UI.UserControl { int count; int counter; int display; int pages; int current2; int start; string check; protected void Page_Load(object sender, EventArgs e) { if (HttpContext.Current.Request.QueryString[0] != null || HttpContext.Current.Request.QueryString[0] == "/Edit") { Continue_Search2(); List_Mysearch(); List_Mysearch2(); } } //protected void destroyme() //{ // Session["var1"] = null; // Session["var2"] = null; // Session["var3"] = null; // check = null; //} protected void Submit_Click(object sender, EventArgs e) { Session["myuser1"] = (string)(Session["myuser"]); Session["year"] = yearsearch.SelectedValue; Session["model"] = modelsearch.SelectedValue; Session["location"] = locationsearch.SelectedValue; Session["status"] = statussearch.SelectedValue; Session["counter"] = 1; Response.Redirect(Request.RawUrl); } protected void Continue_Search2() { string haha = HttpContext.Current.Request.QueryString[0].ToString(); Session["counter"] = haha; } protected void List_Mysearch() { //edited by aifa 18/11/2015 string myusername = (string)(Session["myuser1"]); //end string yearvalue = (string)(Session["year"]); string modelvalue = (string)(Session["model"]); string locationvalue = (string)(Session["location"]); string statusvalue = (string)(Session["status"]); int v; double myNum = 0; String testVar = (string)(Session["counter"]); if (Double.TryParse(testVar, out myNum)) { v = Convert.ToInt32(Session["counter"]); } else { v = 0; } if (yearvalue == "0") { yearvalue = "car_year IS NOT NULL AND"; } else { yearvalue = "car_year=" + yearvalue + " " + "AND"; } if (modelvalue == "0") { modelvalue = " " + "car_model IS NOT NULL AND"; } else { modelvalue = " " + "car_model LIKE" + " " + "'" + modelvalue + "%'" + " " + "AND"; } if (locationvalue == "0") { locationvalue = " " + "car_location IS NOT NULL AND"; } else { locationvalue = " " + "car_location LIKE" + " " + "'" + locationvalue + "'" + " " + "AND"; } if (statusvalue == "0") { statusvalue = " " + "car_approve IS NOT NULL"; } else { statusvalue = " " + "car_approve LIKE" + " " + "'" + statusvalue + "'"; } string searchvalue = " " + yearvalue + " " + modelvalue + " " + locationvalue + " " + statusvalue; DataTable dt = new DataTable(); SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["CMSConnectionString"].ConnectionString); try { SqlCommand cmd = new SqlCommand(); cmd.Connection = myConnection; // cmd.CommandText = "SELECT car_model,car_year,car_price,car_location,car_plate FROM cardatabase WHERE" + searchvalue; if (myusername == "kahnuen" || myusername == "administrator") { cmd.CommandText = "SELECT car_model,car_year,car_price,car_location,car_plate FROM cardatabase WHERE"+ " " + searchvalue; } else { cmd.CommandText = "SELECT car_model,car_year,car_price,car_location,car_plate FROM cardatabase WHERE car_details ="+" "+ "'" + myusername + "'" + " " + "AND" + " " + searchvalue; } myConnection.Open(); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; da.Fill(dt); count = dt.Rows.Count; counter = 1; display = 15; pages = (count / display) + 1; if (v < 1) { current2 = 1; } else { current2 = v; } start = (current2 * display) - display; string[] mypages = new string[dt.Rows.Count]; StringBuilder spage = new StringBuilder(); // if(current2 == pages){ // mypages[counter] = "" + counter + ""; // spage.Append(mypages[counter]); // } // else // { for (counter = 1; counter <= pages; counter++) { if (counter == v) { mypages[counter] = "" + counter + " "; } else { mypages[counter] = "" + counter + " "; } spage.Append(mypages[counter]); } // } thepage.InnerHtml = "

" + spage.ToString() + "


"; //editdataall.Value = sb.ToString(); } catch (Exception f) { Console.WriteLine(f.ToString()); } finally { // Close Connection myConnection.Close(); } } protected void List_Mysearch2() { //edited by aifa 18/11/2015 string myusername = (string)(Session["myuser1"]); //end string yearvalue = (string)(Session["year"]); string modelvalue = (string)(Session["model"]); string locationvalue = (string)(Session["location"]); string statusvalue = (string)(Session["status"]); if (yearvalue == "0") { yearvalue = "car_year IS NOT NULL AND"; } else { yearvalue = "car_year =" + yearvalue + " " + "AND"; } if (modelvalue == "0") { modelvalue = " " + "car_model IS NOT NULL AND"; } else { modelvalue = " " + "car_model LIKE" + " " + "'" + modelvalue + "%'" + " " + "AND"; } if (locationvalue == "0") { locationvalue = " " + "car_location IS NOT NULL" + " " + "AND"; } else { locationvalue = " " + "car_location LIKE " + " " + "'" + locationvalue + "'" + " " + "AND"; } if (statusvalue == "0") { statusvalue = " " + "car_approve IS NOT NULL"; } else { statusvalue = " " + "car_approve LIKE" + " " + "'" + statusvalue + "'"; } string searchvalue = " " + yearvalue + " " + modelvalue + " " + locationvalue + " " + statusvalue; //mylabel.Text = modelvalue; DataTable dt = new DataTable(); SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["CMSConnectionString"].ConnectionString); try { SqlCommand cmd = new SqlCommand(); cmd.Connection = myConnection; if (myusername == "kahnuen" || myusername == "administrator") { cmd.CommandText = "SELECT car_id,car_model,car_cc,car_plate,car_colour,car_year,car_mileage,car_price,car_location,car_seat,car_details,car_approve,car_status,car_warranty,car_reg FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY car_year) as row FROM cardatabase WHERE" + " " + searchvalue + ")" + " " + "a" + " " + "WHERE row >" + start + " " + "AND row <=" + (current2 * 15); } else { cmd.CommandText = "SELECT car_id,car_model,car_cc,car_plate,car_colour,car_year,car_mileage,car_price,car_location,car_seat,car_details,car_approve,car_status,car_warranty,car_reg FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY car_year) as row FROM cardatabase WHERE car_details =" +" "+ "'" + myusername + "'" + " " + "AND" + " " + searchvalue + ")" + " " + "a" + " " + "WHERE row >" + start + " " + "AND row <=" + (current2 * 15); } //cmd.CommandText = "SELECT car_id,car_model,car_plate,car_year,car_mileage,car_price,car_location,car_owner FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY car_year) as row FROM cardatabase WHERE" + " " + searchvalue + ")" + " " + "a" + " " + "WHERE row >" + start + " " + "AND row <=" + (current2 * 15); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; da.Fill(dt); string[] searchyear = new string[dt.Rows.Count]; string[] searchlocation = new string[dt.Rows.Count]; string[] searchlocation2 = new string[dt.Rows.Count]; string[] searchcc = new string[dt.Rows.Count]; string[] searchcolour = new string[dt.Rows.Count]; string[] searchseat = new string[dt.Rows.Count]; string[] myallsearch = new string[dt.Rows.Count]; string[] myid = new string[dt.Rows.Count]; string[] mymodel = new string[dt.Rows.Count]; string[] mycc = new string[dt.Rows.Count]; string[] myplate = new string[dt.Rows.Count]; string[] myplatehidden = new string[dt.Rows.Count]; string[] mycolour = new string[dt.Rows.Count]; string[] myyear = new string[dt.Rows.Count]; string[] mymileage = new string[dt.Rows.Count]; string[] myprice = new string[dt.Rows.Count]; string[] mylocation = new string[dt.Rows.Count]; string[] myseat = new string[dt.Rows.Count]; string[] mydetails = new string[dt.Rows.Count]; string[] myapprove = new string[dt.Rows.Count]; string[] mystatus = new string[dt.Rows.Count]; string[] mywarranty = new string[dt.Rows.Count]; string[] myreg = new string[dt.Rows.Count]; StringBuilder sb = new StringBuilder(); for (int i = 0; i <= dt.Rows.Count - 1; i++) { //car_id,car_model,car_cc,car_plate,car_colour,car_year,car_mileage,car_price,car_location,car_seat,car_details,car_approve,car_status,car_warranty myid[i] = dt.Rows[i][0].ToString(); mymodel[i] = dt.Rows[i][1].ToString(); mycc[i] = dt.Rows[i][2].ToString(); myplatehidden[i] = Uri.EscapeDataString(dt.Rows[i][3].ToString()); myplate[i] = "" + Uri.EscapeDataString(dt.Rows[i][3].ToString()) + ""; mycolour[i] = dt.Rows[i][4].ToString(); myyear[i] = dt.Rows[i][5].ToString(); mymileage[i] = dt.Rows[i][6].ToString(); myprice[i] = dt.Rows[i][7].ToString(); mylocation[i] = dt.Rows[i][8].ToString(); myseat[i] = dt.Rows[i][9].ToString(); mydetails[i] = dt.Rows[i][10].ToString(); myapprove[i] = dt.Rows[i][11].ToString(); mystatus[i] = dt.Rows[i][12].ToString(); mywarranty[i] = dt.Rows[i][13].ToString(); myreg[i] = dt.Rows[i][14].ToString(); //myowner[i] = dt.Rows[i][7].ToString(); //mylocation[i] = Regex.Replace(dt.Rows[i][6].ToString(), "_", " "); //string [] getmyowner = myowner[i].Split('_'); //string joinmeup = String.Join(" ", getmyowner); //myowner[i] = dt.Rows[i][7].ToString(); //Regex.Replace(str, @"\s+", "_"); searchyear[i] = ""; searchlocation2[i] = ""; searchcc[i] = ""; searchcolour[i] = "" + "" + "" +"" + "" + "" + searchlocation[i] + "" + searchseat[i] + "" + searchlocation2[i] + "" + "" + "" + myapprove[i] +""+"" + "" + "" + ""; // myallsearch[i] = "" + mymodel[i] + "" + "" + "" + myyear[i] + "" + "" + //"" + "" + "" + mylocation[i] + "" + "" + "" + "" + ""; sb.Append(myallsearch[i]); if (myusername == "kahnuen" || myusername == "administrator") { allsearch.InnerHtml = "" + sb.ToString() + "
ModelCcPlate NumberColourYearRegistrationMileage(KM)Price(RM)LocationSeatStatusWarrantyApprovalApproveDelete
"; } else { allsearch.InnerHtml = "" + sb.ToString() + "
ModelCcPlate NumberColourYearRegistrationMileage(KM)Price(RM)LocationSeatStatusWarrantyApprovalEditDelete
"; } } //editdataall.Value = sb.ToString(); } catch (Exception f) { Console.WriteLine(f.ToString()); } finally { // Close Connection myConnection.Close(); } } protected void updatedata() { string myusername = (string)(Session["myuser1"]); SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["CMSConnectionString"].ConnectionString); myConnection.Open(); try { SqlCommand[] myCommand = new SqlCommand[20]; for (int i = 0; i <= 20; i++) { if (Request.Form["check" + i] == "1" && Request.Form["checkb" + i] != "1") { //mylabel.Text = Request.Form["id1"] + Request.Form["price1"] + Request.Form["model1"] + Request.Form["mileage1"] + Request.Form["owner1"]; //mylabel.Text = "nooooooooob betull"; //var folder = Server.MapPath("~/App_Data/uploads/random"); //if (!Directory.Exists(folder)) //{ // Directory.CreateDirectory(folder); //} if (myusername == "kahnuen" || myusername == "administrator") { myCommand[i] = myConnection.CreateCommand(); myCommand[i].CommandText = "UPDATE cardatabase SET car_cc = @cc, car_colour= @colour,car_year=@year, car_mileage = @mileage,car_price = @price, car_location = @location,car_seat = @seat,car_approve = 'Approved',car_status= @status,car_warranty= @warranty,car_reg= @reg,status_date = @statusdate WHERE car_id = @id"; myCommand[i].Parameters.Add(new SqlParameter("@id", System.Data.SqlDbType.Int)); myCommand[i].Parameters.Add(new SqlParameter("@mileage", System.Data.SqlDbType.Int)); myCommand[i].Parameters.Add(new SqlParameter("@price", System.Data.SqlDbType.Int)); myCommand[i].Parameters.Add(new SqlParameter("@year", System.Data.SqlDbType.Int)); myCommand[i].Parameters.Add(new SqlParameter("@location", System.Data.SqlDbType.VarChar, 50)); myCommand[i].Parameters.Add(new SqlParameter("@status", System.Data.SqlDbType.VarChar, 50)); myCommand[i].Parameters.Add(new SqlParameter("@cc", System.Data.SqlDbType.VarChar, 50)); myCommand[i].Parameters.Add(new SqlParameter("@colour", System.Data.SqlDbType.VarChar, 50)); myCommand[i].Parameters.Add(new SqlParameter("@seat", System.Data.SqlDbType.VarChar, 50)); myCommand[i].Parameters.Add(new SqlParameter("@warranty", System.Data.SqlDbType.Text)); myCommand[i].Parameters.Add(new SqlParameter("@reg", System.Data.SqlDbType.Text)); myCommand[i].Parameters.Add(new SqlParameter("@statusdate", System.Data.SqlDbType.DateTime)); myCommand[i].Parameters["@id"].Value = Request.Form["id" + i]; myCommand[i].Parameters["@mileage"].Value = Request.Form["mileage" + i]; myCommand[i].Parameters["@price"].Value = Request.Form["price" + i]; myCommand[i].Parameters["@year"].Value = Request.Form["selectyear" + i]; myCommand[i].Parameters["@location"].Value = Request.Form["selectlocation" + i]; myCommand[i].Parameters["@status"].Value = Request.Form["selectlocation2" + i]; myCommand[i].Parameters["@cc"].Value = Request.Form["selectcc" + i]; // myCommand[i].Parameters["@colour"].Value =String.Format("{0}", Request.Form["selectcolour" + i]); myCommand[i].Parameters["@colour"].Value = Request.Form["selectcolour" + i]; myCommand[i].Parameters["@seat"].Value = Request.Form["selectseat" + i]; myCommand[i].Parameters["@warranty"].Value = Request.Form["warranty" + i]; myCommand[i].Parameters["@reg"].Value = Request.Form["reg" + i]; myCommand[i].Parameters["@statusdate"].Value = DateTime.Now; myCommand[i].ExecuteNonQuery(); } else { myCommand[i] = myConnection.CreateCommand(); myCommand[i].CommandText = "UPDATE cardatabase SET car_cc = @cc, car_colour= @colour,car_year=@year, car_mileage = @mileage,car_price = @price, car_location = @location,car_seat = @seat,car_approve = 'Pending',car_status= @status,car_warranty= @warranty,car_reg= @reg,status_date = @statusdate WHERE car_id = @id"; myCommand[i].Parameters.Add(new SqlParameter("@id", System.Data.SqlDbType.Int)); myCommand[i].Parameters.Add(new SqlParameter("@mileage", System.Data.SqlDbType.Int)); myCommand[i].Parameters.Add(new SqlParameter("@price", System.Data.SqlDbType.Int)); myCommand[i].Parameters.Add(new SqlParameter("@year", System.Data.SqlDbType.Int)); myCommand[i].Parameters.Add(new SqlParameter("@location", System.Data.SqlDbType.VarChar, 50)); myCommand[i].Parameters.Add(new SqlParameter("@status", System.Data.SqlDbType.VarChar, 50)); myCommand[i].Parameters.Add(new SqlParameter("@cc", System.Data.SqlDbType.VarChar, 50)); myCommand[i].Parameters.Add(new SqlParameter("@colour", System.Data.SqlDbType.VarChar, 50)); myCommand[i].Parameters.Add(new SqlParameter("@seat", System.Data.SqlDbType.VarChar, 50)); myCommand[i].Parameters.Add(new SqlParameter("@warranty", System.Data.SqlDbType.Text)); myCommand[i].Parameters.Add(new SqlParameter("@reg", System.Data.SqlDbType.Text)); myCommand[i].Parameters.Add(new SqlParameter("@statusdate", System.Data.SqlDbType.DateTime)); myCommand[i].Parameters["@id"].Value = Request.Form["id" + i]; myCommand[i].Parameters["@mileage"].Value = Request.Form["mileage" + i]; myCommand[i].Parameters["@price"].Value = Request.Form["price" + i]; myCommand[i].Parameters["@year"].Value = Request.Form["selectyear" + i]; myCommand[i].Parameters["@location"].Value = Request.Form["selectlocation" + i]; myCommand[i].Parameters["@status"].Value = Request.Form["selectlocation2" + i]; myCommand[i].Parameters["@cc"].Value = Request.Form["selectcc" + i]; myCommand[i].Parameters["@colour"].Value = Request.Form["selectcolour" + i]; myCommand[i].Parameters["@seat"].Value = Request.Form["selectseat" + i]; myCommand[i].Parameters["@warranty"].Value = Request.Form["warranty" + i]; myCommand[i].Parameters["@reg"].Value = Request.Form["reg" + i]; myCommand[i].Parameters["@statusdate"].Value = DateTime.Now; myCommand[i].ExecuteNonQuery(); } } else if (Request.Form["checkb" + i] == "1") { myCommand[i] = myConnection.CreateCommand(); myCommand[i].CommandText = "DELETE FROM cardatabase WHERE car_id = @id"; myCommand[i].Parameters.Add(new SqlParameter("@id", System.Data.SqlDbType.Int)); myCommand[i].Parameters["@id"].Value = Request.Form["id" + i]; myCommand[i].ExecuteNonQuery(); var folder = Server.MapPath("~/Pre-Owned/customasp/carpicture/" + Request.Form["plate" + i]); if (Directory.Exists(folder)) { Directory.Delete(folder); } } } } catch (Exception f) { Console.WriteLine(f.ToString()); } finally { myConnection.Close(); if (myusername == "kahnuen" || myusername == "administrator") { string fulltext = "Mazda Car list has been approved/deleted by" + " " + myusername + "." + "Please click this link : http://mazdaanshin.com.my/edit.aspx"; string to = "muzamil@mazda.com.my"; string from = "micrositeemail@mazda.com.my"; string subject = "Mazda Anshin Update by" + " " + myusername ; string body = fulltext; var message = new MailMessage(); message.To.Add("muzamil@mazda.com.my"); message.To.Add("aifa.abdulaziz@mazda.com.my"); message.From = new MailAddress(from); message.Subject = subject; message.Body = body; message.IsBodyHtml = true; SmtpClient client = new SmtpClient("smtp.office365.com"); client.Port = 587; client.DeliveryMethod = SmtpDeliveryMethod.Network; client.UseDefaultCredentials = false; client.Credentials = new NetworkCredential("micrositeemail@mazda.com.my", "Bypass123!"); client.EnableSsl = true; //This is the you need to do client.Send(message); } else { string fulltext = "Mazda Car list has been updated/deleted by" + " " + myusername + "." + "Please click this link : http://mazdaanshin.com.my/edit.aspx"; string to = "kahnuen@mazda.com.my"; string from = "micrositeemail@mazda.com.my"; string subject = "Mazda Anshin Update by" + " " + myusername ; string body = fulltext; var message = new MailMessage(); message.To.Add("kahnuen@mazda.com.my"); message.To.Add("aifa.abdulaziz@mazda.com.my"); message.From = new MailAddress(from); message.Subject = subject; message.Body = body; message.IsBodyHtml = true; SmtpClient client = new SmtpClient("smtp.office365.com"); client.Port = 587; client.DeliveryMethod = SmtpDeliveryMethod.Network; client.UseDefaultCredentials = false; client.Credentials = new NetworkCredential("micrositeemail@mazda.com.my", "Bypass123!"); client.EnableSsl = true; //This is the you need to do client.Send(message); } } } protected void Submit_Edit(object sender, EventArgs e) { updatedata(); Response.Redirect(Request.RawUrl); } }