private void button1_Click(object sender, EventArgs e)
{
queryBLL q = new queryBLL();
q = dcDAL.updatestatusandreturn(txtu_Name.Text, textqry.Text, textsoln.Text, txtu_Contact.Text);
dgvpending.DataSource = dcDAL.Select(0);
}
public bool Insert(queryBLL p)
{
//Creating Boolean Variable and set its default value to false
bool isSuccess = false;
//Sql Connection for DAtabase
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
//SQL Query to insert company into database
String sql = "INSERT INTO tbl_query ( c_id, cu_id, u_name,u_mobile,ques,soln,status,datetime) VALUES ( @c_id, @cu_id, @u_name, @u_mobile, @ques, @soln,@status,datetime)";
//Creating SQL Command to pass the values
SqlCommand cmd = new SqlCommand(sql, conn);
//Passign the values through parameters
cmd.Parameters.AddWithValue("@c_id", p.c_id);
cmd.Parameters.AddWithValue("@cu_id", p.cu_id);
cmd.Parameters.AddWithValue("@u_name", p.u_name);
cmd.Parameters.AddWithValue("@u_mobile", p.u_mobile);
cmd.Parameters.AddWithValue("@ques", p.ques);
cmd.Parameters.AddWithValue("@soln", p.soln);
cmd.Parameters.AddWithValue("@status", p.status);
cmd.Parameters.AddWithValue("@datetime", p.datetime);
//Opening the Database connection
conn.Open();
int rows = cmd.ExecuteNonQuery();
//If the query is executed successfully then the value of rows will be greater than 0 else it will be less than 0
if (rows > 0)
{
//Query Executed Successfully
isSuccess = true;
}
else
{
//FAiled to Execute Query
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool Update(queryBLL p)
{
//create a boolean variable and set its initial value to false
bool isSuccess = false;
//Create SQL Connection for DAtabase
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
//SQL Query to Update Data in dAtabase
String sql = "UPDATE tbl_companys SET q_id=@q_id, c_id=@c_id, cu_id=@cu_id, u_name=@u_name, ques=@ques, soln=@soln WHERE q_id=@q_id";
//Create SQL Cmmand to pass the value to query
SqlCommand cmd = new SqlCommand(sql, conn);
//Passing the values using parameters and cmd
cmd.Parameters.AddWithValue("@q_id", p.q_id);
cmd.Parameters.AddWithValue("@c_id", p.c_id);
cmd.Parameters.AddWithValue("@cu_id", p.cu_id);
cmd.Parameters.AddWithValue("@u_name", p.u_name);
cmd.Parameters.AddWithValue("@u_mobile", p.u_mobile);
cmd.Parameters.AddWithValue("@ques", p.ques);
cmd.Parameters.AddWithValue("@soln", p.soln);
//Open the Database connection
conn.Open();
//Create Int Variable to check if the query is executed successfully or not
int rows = cmd.ExecuteNonQuery();
//if the query is executed successfully then the value of rows will be greater than 0 else it will be less than zero
if (rows > 0)
{
//Query ExecutedSuccessfully
isSuccess = true;
}
else
{
//Failed to Execute Query
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public queryBLL updatestatusandreturn(string uname, string ques, string soln, string umobile)
{
//Create an object of companysBLL and return it
queryBLL p = new queryBLL();
// companysBLL q = new companysBLL();
//SqlConnection
SqlConnection conn = new SqlConnection(myconnstrng);
//Datatable to store data temporarily
DataTable dt = new DataTable();
bool sucess = true;
try
{
//Write the Query to Get the detaisl
string sql = "UPDATE tbl_query SET status = 1 WHERE u_name LIKE '%" + uname + "%' AND ques LIKE '%" + ques + "%' AND u_mobile LIKE '%" + umobile + "%' AND soln Like '%" + soln + "%'";
//Create Sql Data Adapter to Execute the query
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
sql = " SELECT * FROM tbl_query WHERE u_name LIKE '%" + uname + "%' AND ques LIKE '%" + ques + "%' AND u_mobile LIKE '%" + umobile + "%' AND soln '%" + soln + "%'";
//Open DAtabase Connection
conn.Open();
//Pass the value from adapter to dt
adapter.Fill(dt);
//If we have any values on dt then set the values to companysBLL
if (dt.Rows.Count > 0)
{
p.u_name = dt.Rows[0]["u_name"].ToString();
p.u_mobile = dt.Rows[0]["u_mobile"].ToString();
p.c_id = int.Parse(dt.Rows[0]["id"].ToString());
p.soln = dt.Rows[0]["soln"].ToString();
p.ques = dt.Rows[0]["ques"].ToString();
p.cu_id = int.Parse(dt.Rows[0]["cu_id"].ToString());
p.status = int.Parse(dt.Rows[0]["status"].ToString());
}
;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
sucess = false;
}
finally
{
//Close Database Connection
conn.Close();
}
return(p);
}
private void import_Click(object sender, EventArgs e)
{
queryBLL q = new queryBLL();
companysBLL b = new companysBLL();
companysDAL dc = new companysDAL();
int i = dataGridView1.Rows.Count;
i--;
string add = "";
string name;
string mobile;
string sub;
string email;
string subend = "12/12/2009";
string cat = "na";
for (int j = 1; j < i; j++)
{
name = dataGridView1.Rows[j].Cells[11].Value.ToString();
bool sucess = dc.chkcompanybyname(name);
if (sucess == false)
{
add = dataGridView1.Rows[j].Cells[6].Value.ToString();
add = add + dataGridView1.Rows[j].Cells[7].Value.ToString();
add = add + dataGridView1.Rows[j].Cells[3].Value.ToString();
add = add + dataGridView1.Rows[j].Cells[8].Value.ToString();
b.c_location = add;
name = dataGridView1.Rows[j].Cells[11].Value.ToString();
mobile = dataGridView1.Rows[j].Cells[1].Value.ToString();
sub = dataGridView1.Rows[j].Cells[2].Value.ToString();
email = dataGridView1.Rows[j].Cells[0].Value.ToString();
sub = dataGridView1.Rows[j].Cells[2].Value.ToString();
b.c_name = name;
b.c_mobile = mobile;
b.c_email = email;
b.subend_date = sub;
b.substart_date = subend;
b.c_category = cat;
bool suck = dc.Insert(b);
add = " ";
if (suck != true)
{
MessageBox.Show("nhi add hui");
}
}
}
}
public queryBLL Getqueryforsearch(queryBLL q)
{
//Create an object of companysBLL and return it
queryBLL p = new queryBLL();
// companysBLL q = new companysBLL();
//SqlConnection
SqlConnection conn = new SqlConnection(myconnstrng);
//Datatable to store data temporarily
DataTable dt = new DataTable();
try
{
//Write the Query to Get the detaisl
string sql = "SELECT q_id, c_id, cu_id,u_name,u_mobile FROM tbl_query WHERE c_id LIKE '%" + q.c_id + "%' OR u_name LIKE '%" + q.u_name + "%' OR u_mobile LIKE '%" + q.u_mobile + "%' OR ques LIKE '%" + q.ques + "%'";
//Create Sql Data Adapter to Execute the query
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
//Open DAtabase Connection
conn.Open();
//Pass the value from adapter to dt
adapter.Fill(dt);
//If we have any values on dt then set the values to companysBLL
if (dt.Rows.Count > 0)
{
p.u_name = dt.Rows[0]["u_name"].ToString();
p.u_mobile = dt.Rows[0]["u_mobile"].ToString();
p.c_id = int.Parse(dt.Rows[0]["c_id"].ToString());
p.q_id = int.Parse(dt.Rows[0]["q_id"].ToString());
}
;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
//Close Database Connection
conn.Close();
}
return(p);
}
public bool Delete(queryBLL p)
{
//Create Boolean Variable and Set its default value to false
bool isSuccess = false;
//SQL Connection for DB connection
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
//Write Query company from DAtabase
String sql = "DELETE FROM tbl_companys WHERE q_id=@q_id";
//Sql Command to Pass the Value
SqlCommand cmd = new SqlCommand(sql, conn);
//Passing the values using cmd
cmd.Parameters.AddWithValue("@q_id", p.q_id);
//Open Database Connection
conn.Open();
int rows = cmd.ExecuteNonQuery();
//If the query is executed successfullly then the value of rows will be greated than 0 else it will be less than 0
if (rows > 0)
{
//Query Executed Successfully
isSuccess = true;
}
else
{
//Failed to Execute Query
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public DataTable Getsoln(string keyword)
{
//Create an object of companysBLL and return it
queryBLL p = new queryBLL();
// companysBLL q = new companysBLL();
//SqlConnection
SqlConnection conn = new SqlConnection(myconnstrng);
//Datatable to store data temporarily
DataTable dt = new DataTable();
try
{
//Write the Query to Get the detaisl
string sql = "SELECT ques,soln FROM tbl_query WHERE ques LIKE '%" + keyword + "%'";
//Create Sql Data Adapter to Execute the query
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
//Open DAtabase Connection
conn.Open();
//Pass the value from adapter to dt
adapter.Fill(dt);
//If we have any values on dt then set the values to companysBLL
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
//Close Database Connection
conn.Close();
}
return(dt);
}
private void button3_Click(object sender, EventArgs e)
{
string keyword = txtu_Name.Text;
queryBLL q = new queryBLL();
bool qsucess = tDAL.chkcustumer(keyword);
custBLL dc = tDAL.searchcustumer(keyword);
if (qsucess == true)
{
keyword = txtcompany.Text;
companysBLL c = pDAL.Search(keyword);
q.ques = textqry.Text;
q.soln = textsoln.Text;
q.cu_id = dc.id;
q.c_id = dc.cid;
q.u_name = dc.name;
emailname = q.u_name;
q.u_mobile = dc.mobile;
dc.nquery = dc.nquery++;
bool incquery = tDAL.Increasenoofquery(dc); if (incquery == true)
{
MessageBox.Show("user and query updated");
}
else
{
MessageBox.Show("user and query not updated");
}
q.status = 1;
bool success = dcDAL.Insert(q);
q = dcDAL.Getqueryforsearch(q);
qid = q.q_id;
qid1 = q.q_id;
}
if (qsucess == false)
{
keyword = txtcompany.Text;
bool csucess = pDAL.chkcompanybyname(keyword);
if (csucess == true)
{
keyword = txtcompany.Text;
companysBLL c = pDAL.Search(keyword);
custBLL ca = new custBLL();
ca.name = txtu_Name.Text;
emailname = q.u_name;
ca.mobile = txtu_Contact.Text;
string type = "user";
ca.type = type;
ca.email = txtemail.Text;
bool isadded = false;
int id = c.id;
ca.cid = id;
ca.nquery = 0;
isadded = tDAL.Insert(ca);
if (isadded == true)
{
MessageBox.Show("new user added");
}
else
{
MessageBox.Show("new user can not be added");
}
}
else
{
MessageBox.Show("plz add new company");
addcompanys ca = new addcompanys();
ca.Show();
}
//space to add new user(custumer)
//addnewcostumer costumer = new addnewcostumer();
//costumer.Show();
//this.Hide();
//MessageBox.Show("plz add new costumer");
//Failed to Add New product
//MessageBox.Show("new user added");
}
//if the product is added successfully then the value of success will be true else it will be false
if (qsucess == true)
{
//Product Inserted Successfully
MessageBox.Show("query Added Successfully");
emailsBLL email = new emailsBLL();
email.to = txtemail.Text;
email.subject = "solidworks support query " + qid;
string body = "<h3>" + "query- \n " + "</h3>" + "<b>" + textqry.Text + "</b>" + "<h3>" + " \n\n solution- \n" + "</h3>" + "<b>" + textsoln.Text + "</b>" + "\n\n\n\n\n";
email.body = body;
email.name = txtu_Name.Text;
bool sucess = eDAL.sendemail(email);
if (sucess == true)
{
MessageBox.Show("email sent sucessfully");
}
else
{
MessageBox.Show("email not sent");
}
//Calling the Clear Method
}
}
private void button4_Click(object sender, EventArgs e)
{
string keyword = txtu_Name.Text;
queryBLL q = new queryBLL();
bool qsucess = tDAL.chkcustumer(keyword);//chking costumer
custBLL dc = tDAL.searchcustumer(keyword);
if (qsucess == true)// if costumer is found
{
keyword = txtcompany.Text;
companysBLL c = pDAL.Search(keyword);
q.ques = textqry.Text;
q.soln = textsoln.Text;
q.cu_id = dc.id;
q.c_id = dc.cid;
q.u_name = dc.name;
emailname = q.u_name;
q.u_mobile = dc.mobile;
dc.nquery = dc.nquery++;
q.status = 0;
q.datetime = DateTime.Now;
bool success = dcDAL.Insert(q);
bool incquery = tDAL.Increasenoofquery(dc); if (incquery == true)
{
MessageBox.Show("user and query updated");
}
else
{
MessageBox.Show("user and query not updated");
}
emailname = q.u_name;
q = dcDAL.Getqueryforsearch(q);
qid = q.q_id;
if (success == true)
{
//Product Inserted Successfully
MessageBox.Show("query Added Successfully");
emailsBLL email = new emailsBLL();
email.to = txtemail.Text;
email.subject = "solidworks support query " + qid;
string body = "<h3>" + "SR NO." + DateTime.Today + " " + qid + "query- \n " + "</h3>" + "<b>" + textqry.Text + "</b>" + "<h3>" + " \n\n solution- \n" + "</h3>" + "<b>" + textsoln.Text + "</b>" + "\n\n\n\n\n";
email.body = body;
email.name = txtu_Name.Text;
bool sucess = eDAL.sendemail(email);
if (sucess == true)
{
MessageBox.Show("email sent sucessfully");
}
else
{
MessageBox.Show("email not sent");
}
//Calling the Clear Method
}
}
if (qsucess == false)
{
keyword = txtcompany.Text;
bool csucess = pDAL.chkcompanybyname(keyword);
if (csucess == true)
{
keyword = txtcompany.Text;
companysBLL c = pDAL.Search(keyword);
custBLL ca = new custBLL();
ca.name = txtu_Name.Text;
ca.mobile = txtu_Contact.Text;
string type = "user";
ca.type = type;
ca.email = txtemail.Text;
bool isadded = false;
int id = c.id;
ca.cid = id;
ca.nquery = 0;
isadded = tDAL.Insert(ca);
if (isadded == true)
{
MessageBox.Show("new user added");
}
else
{
MessageBox.Show("new user can not be added");
}
}
else
{
MessageBox.Show("plz add new company");
addcompanys ca = new addcompanys();
ca.Show();
}
}
}
private void button2_Click(object sender, EventArgs e)
{
custBLL c = new custBLL();
custDAL cd = new custDAL();
queryBLL q = new queryBLL();
queryDAL qd = new queryDAL();
companysBLL b = new companysBLL();
companysDAL dc = new companysDAL();
#region user details
//int id;
string email;
string name = "";
string mobile;
//int nquery;
#endregion
#region query details
string ques;
string ans;
int status = 1;
int cid;
string cname;
int i = dataGridView1.Rows.Count;
i--;
#endregion
for (int j = 1; j < i; j++)
{
#region get data from table
name = dataGridView1.Rows[j].Cells[4].Value.ToString();
mobile = dataGridView1.Rows[j].Cells[5].Value.ToString();
ans = dataGridView1.Rows[j].Cells[3].Value.ToString();
email = dataGridView1.Rows[j].Cells[6].Value.ToString();
cname = dataGridView1.Rows[j].Cells[1].Value.ToString();
ques = dataGridView1.Rows[j].Cells[2].Value.ToString();
#endregion
#region fill the query
q.u_name = name;
q.u_mobile = mobile;
q.ques = ques;
q.soln = ans;
q.status = status;
b = dc.GetcompanysForTransaction(cname);
cid = b.id;
q.c_id = cid;
bool isuser1 = cd.chkcustumer(name);
if (isuser1 == true)
{
c = cd.searchcustumer(name);
}
else
{
MessageBox.Show("user not found");
}
q.cu_id = c.id;
q.status = status;
bool qadded = qd.Insert(q);
if (qadded == false)
{
MessageBox.Show("query not added");
}
#endregion
}
}
private void button1_Click(object sender, EventArgs e)
{
#region dal and bll
custBLL c = new custBLL();
custDAL cd = new custDAL();
queryBLL q = new queryBLL();
queryDAL qd = new queryDAL();
companysBLL b = new companysBLL();
companysDAL dc = new companysDAL();
#endregion
#region query variables
string ques;
string ans;
int cid;
string cname;
#endregion
#region user details
//int id;
string type = "user";
string email;
string name = "";
string mobile;
//int nquery;
#endregion
int i = dataGridView1.Rows.Count;
i--;
for (int j = 1; j < i; j++)
{
cname = dataGridView1.Rows[j].Cells[4].Value.ToString();
bool sucess = cd.chkcustumer(cname);
if (sucess == false)
{
#region get data from table
name = dataGridView1.Rows[j].Cells[4].Value.ToString();
mobile = dataGridView1.Rows[j].Cells[5].Value.ToString();
ans = dataGridView1.Rows[j].Cells[3].Value.ToString();
email = dataGridView1.Rows[j].Cells[6].Value.ToString();
cname = dataGridView1.Rows[j].Cells[1].Value.ToString();
ques = dataGridView1.Rows[j].Cells[2].Value.ToString();
#endregion
#region adding the user
bool isuser = cd.chkcustumer(name);
if (isuser == false)
{
c.name = name;
c.mobile = mobile;
c.email = email;
c.type = type;
b = dc.GetcompanysForTransaction(cname);
cid = b.id;
c.cid = cid;
c.nquery = 0;
bool useradded = cd.Insert(c);
bool qinc = cd.Increasenoofquery(c);
if (useradded == false)
{
MessageBox.Show("user adding failed");
}
}
#endregion
}
}
}
