public string AddUsers(string username, string password, string mobile, string email, string gender)
{
SqlConnection con = null;
try
{
dbConnection dcon = new dbConnection();
con = dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand("AddUsers", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@user_name", username);
cmd.Parameters.AddWithValue("@password", password);
cmd.Parameters.AddWithValue("@mobile", mobile);
cmd.Parameters.AddWithValue("@email", email);
cmd.Parameters.AddWithValue("@gender", gender);
//output messages
SqlParameter outMsg1 = cmd.Parameters.Add("@msg", SqlDbType.NVarChar, 100);
outMsg1.Direction = ParameterDirection.Output;
SqlParameter outMsg2 = cmd.Parameters.Add("@pass", SqlDbType.Bit);
outMsg2.Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
String msg = outMsg1.Value.ToString();
Boolean pass = (Boolean)outMsg2.Value;
//making it as JSON
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
Dictionary<string, object> row = new Dictionary<string, object>();
List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
row.Add("msg", msg);
row.Add("pass", pass);
rows.Add(row);
return serializer.Serialize(rows);
}
catch (Exception ex)
{
dbConnection dcon = new dbConnection();
con = dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand("ErrorLogInsert", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ErrorLog", ex.Message);
cmd.Parameters.AddWithValue("@UserName", "AddUsers" + ":user:"******"";
}
public string AddToFavorites(string user, string sTypeCode, string providerCode,string addORremove)
{
SqlConnection con = null;
try
{
dbConnection dcon = new dbConnection();
con = dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand("AddFavorites", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@user", user);
cmd.Parameters.AddWithValue("@S_typeCode", sTypeCode);
cmd.Parameters.AddWithValue("@provider", providerCode);
cmd.Parameters.AddWithValue("@addORremove", addORremove);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
dbConnection dcon = new dbConnection();
con = dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand("ErrorLogInsert", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ErrorLog", ex.Message);
cmd.Parameters.AddWithValue("@UserName", "AddToFavorites" + "user:"******"";
}
public string GetServiceTypes(string ServiceCode)
{
SqlConnection con = null;
try
{
dbConnection dcon = new dbConnection();
con = dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand("GetServiceTypes", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ServiceCode", ServiceCode);
return getDbDataAsJSON(cmd);
}
catch (Exception ex)
{
dbConnection dcon = new dbConnection();
con = dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand("ErrorLogInsert", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ErrorLog", ex.Message);
cmd.Parameters.AddWithValue("@UserName", "GetServiceTypes");
cmd.ExecuteNonQuery();
return ex.Message;
}
finally
{
if (con != null)
{
con.Dispose();
}
}
return "";
}
public string GetServiceProviderDetails(string ProviderCode, string serviceCode, string sTypeCode)
{
SqlConnection con = null;
try
{
dbConnection dcon = new dbConnection();
con = dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand("GetServiceProviderDetails", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ProviderCode", ProviderCode);
cmd.Parameters.AddWithValue("@S_typeCode", sTypeCode);
cmd.Parameters.AddWithValue("@serviceCode", serviceCode);
ArrayList imgColNames = new ArrayList();
ArrayList imgFileNameCols = new ArrayList();
imgColNames.Add("ProviderImage");
imgFileNameCols.Add("ProviderImageName");
imgColNames.Add("StyleImg");
imgFileNameCols.Add("StyleImageName");
return getDbDataAsJSON(cmd, imgColNames,imgFileNameCols); //passing col names of image and imagefilename
}
catch (Exception ex)
{
dbConnection dcon = new dbConnection();
con = dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand("ErrorLogInsert", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ErrorLog", ex.Message);
cmd.Parameters.AddWithValue("@UserName", "GetServiceProviderDetails");
cmd.ExecuteNonQuery();
return ex.Message;
}
finally
{
if (con != null)
{
con.Dispose();
}
}
return "";
}
public string GetSearchResults(string ServiceCode, string sTypeCode, string user)
{
SqlConnection con = null;
try
{
dbConnection dcon = new dbConnection();
con = dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand("GetSearchResults", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ServiceCode", ServiceCode);
cmd.Parameters.AddWithValue("@S_typeCode", sTypeCode);
cmd.Parameters.AddWithValue("@user", user);
ArrayList imgColNames = new ArrayList();
ArrayList imgFileNameCols = new ArrayList();
imgColNames.Add("StyleImg");
imgFileNameCols.Add("StyleImageName");
return getDbDataAsJSON(cmd, imgColNames, imgFileNameCols);
}
catch (Exception ex)
{
dbConnection dcon = new dbConnection();
con = dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand("ErrorLogInsert", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ErrorLog", ex.Message);
cmd.Parameters.AddWithValue("@UserName", "GetSearchResults"+"user:"******"";
}
public string GetOffers()
{
SqlConnection con = null;
try
{
dbConnection dcon = new dbConnection();
con = dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand("GetOffers", con);
cmd.CommandType = CommandType.StoredProcedure;
ArrayList imgColNames = new ArrayList();
ArrayList imgFileNameCols = new ArrayList();
imgColNames.Add("OfferImage");
imgFileNameCols.Add("ImageName");
return getDbDataAsJSON(cmd, imgColNames, imgFileNameCols);
}
catch (Exception ex)
{
dbConnection dcon = new dbConnection();
con = dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand("ErrorLogInsert", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ErrorLog", ex.Message);
cmd.Parameters.AddWithValue("@UserName", "GetOffers");
cmd.ExecuteNonQuery();
return ex.Message;
}
finally
{
if (con != null)
{
con.Dispose();
}
}
return "";
}
public String getDbDataAsJSON(SqlCommand cmd)
{
try
{
DataSet ds = null;
SqlDataAdapter sda = new SqlDataAdapter();
sda.SelectCommand = cmd;
ds = new DataSet();
sda.Fill(ds);
DataTable dt = ds.Tables[0];
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
Dictionary<string, object> row;
foreach (DataRow dr in dt.Rows)
{
row = new Dictionary<string, object>();
foreach (DataColumn col in dt.Columns)
{
row.Add(col.ColumnName, dr[col]);
}
rows.Add(row);
}
this.Context.Response.ContentType = "";
return serializer.Serialize(rows);
}
catch (Exception ex)
{
dbConnection dcon = new dbConnection();
SqlConnection con = null;
con = dcon.GetDBConnection();
SqlCommand cmdd = new SqlCommand("ErrorLogInsert", con);
cmdd.CommandType = CommandType.StoredProcedure;
cmdd.Parameters.AddWithValue("@ErrorLog", ex.Message);
cmdd.Parameters.AddWithValue("@UserName", "getDbDataAsJSON_withimage");
cmdd.ExecuteNonQuery();
return ex.Message;
}
finally
{
}
}
public String getDbDataAsJSON(SqlCommand cmd, ArrayList imgColName, ArrayList imgFileNameCol)
{
try
{
DataSet ds = null;
SqlDataAdapter sda = new SqlDataAdapter();
sda.SelectCommand = cmd;
ds = new DataSet();
sda.Fill(ds);
DataTable dt = ds.Tables[0];
String filePath = Server.MapPath("~/tempImages/"); //temporary folder to store images
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
Dictionary<string, object> row;
foreach (DataRow dr in dt.Rows)
{
row = new Dictionary<string, object>();
//adding data in JSON
foreach (DataColumn col in dt.Columns)
{
if ( !imgColName.Contains(col.ColumnName))
{
if( !imgFileNameCol.Contains(col.ColumnName))
row.Add(col.ColumnName, dr[col]);
}
}
//adding image details in JSON
for (int i = 0; i < imgColName.Count; i++)
{
if (dr[imgColName[i]as string] != DBNull.Value)
{
String fileURL = filePath + DateTime.Now.ToString("ddHHmmssfff") + dr[imgFileNameCol[i] as string];
if (!System.IO.File.Exists(fileURL))
{
byte[] buffer = (byte[])dr[imgColName[i] as string];
System.IO.File.WriteAllBytes(fileURL, buffer);
}
row.Add(imgColName[i] as string, fileURL);
}
}
rows.Add(row);
}
this.Context.Response.ContentType = "";
return serializer.Serialize(rows);
}
catch (Exception ex)
{
dbConnection dcon = new dbConnection();
SqlConnection con = null;
con = dcon.GetDBConnection();
SqlCommand cmdd = new SqlCommand("ErrorLogInsert", con);
cmdd.CommandType = CommandType.StoredProcedure;
cmdd.Parameters.AddWithValue("@ErrorLog", ex.Message);
cmdd.Parameters.AddWithValue("@UserName", "getDbDataAsJSON_withimage");
cmdd.ExecuteNonQuery();
return ex.Message;
}
finally
{
}
}
public string Booking(string bookingID, string user, string serviceCode, string sTypeCode, string providerCode, DateTime timing,string bookORcancel)
{
SqlConnection con = null;
try
{
dbConnection dcon = new dbConnection();
con = dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand("Booking", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@user", user);
cmd.Parameters.AddWithValue("@serviceCode", serviceCode);
cmd.Parameters.AddWithValue("@S_typeCode", sTypeCode);
cmd.Parameters.AddWithValue("@provider", providerCode);
cmd.Parameters.AddWithValue("@timing", timing);
cmd.Parameters.AddWithValue("@bookORcancel", bookORcancel);
String activityTime=DateTime.Now.ToString();
cmd.Parameters.AddWithValue("@activityTime", activityTime);
if (bookORcancel == "book")
{
bookingID = ((user.Length<20)? user : user.Substring(0,20) )+ DateTime.Now.ToString("ddHHmmssfff");
}
cmd.Parameters.AddWithValue("@BookingID", bookingID);
//output messages
SqlParameter outMsg1 = cmd.Parameters.Add("@msg", SqlDbType.NVarChar, 100);
outMsg1.Direction = ParameterDirection.Output;
SqlParameter outMsg2 = cmd.Parameters.Add("@pass", SqlDbType.Bit);
outMsg2.Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
String msg = outMsg1.Value.ToString();
Boolean pass = (Boolean)outMsg2.Value;
//making it as JSON
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
Dictionary<string, object> row = new Dictionary<string, object>();
List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
row.Add("msg", msg);
row.Add("pass", pass);
row.Add("bookID", bookingID);
rows.Add(row);
Common comObj = new Common();
//sending mail
if (pass==true)
{
new Thread(delegate()
{
comObj.SendMail(bookORcancel, activityTime, bookingID);
}).Start();
}
return serializer.Serialize(rows);
}
catch (Exception ex)
{
dbConnection dcon = new dbConnection();
con = dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand("ErrorLogInsert", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ErrorLog", ex.Message);
cmd.Parameters.AddWithValue("@UserName", "Booking" + ":user:"******"";
}
public void SendMail(string bookORcancel, string activityTime, string bookingID)
{
try
{
//get mailing details
SqlConnection con = null;
dbConnection dcon = new dbConnection();
con = dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand("GetMailingDetails", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@bookingID", bookingID);
SqlParameter outMsg1 = cmd.Parameters.Add("@userName", SqlDbType.NVarChar, 30);
outMsg1.Direction = ParameterDirection.Output;
SqlParameter outMsg2 = cmd.Parameters.Add("@mobile", SqlDbType.NVarChar, 20);
outMsg2.Direction = ParameterDirection.Output;
SqlParameter outMsg3 = cmd.Parameters.Add("@serviceName", SqlDbType.NVarChar, 20);
outMsg3.Direction = ParameterDirection.Output;
SqlParameter outMsg4 = cmd.Parameters.Add("@serviceTypeName", SqlDbType.NVarChar, 20);
outMsg4.Direction = ParameterDirection.Output;
SqlParameter outMsg5 = cmd.Parameters.Add("@providerName", SqlDbType.NVarChar, 200);
outMsg5.Direction = ParameterDirection.Output;
SqlParameter outMsg6 = cmd.Parameters.Add("@providerMail", SqlDbType.NVarChar, 300);
outMsg6.Direction = ParameterDirection.Output;
SqlParameter outMsg7 = cmd.Parameters.Add("@userMail", SqlDbType.NVarChar, 300);
outMsg7.Direction = ParameterDirection.Output;
SqlParameter outMsg8 = cmd.Parameters.Add("@timing", SqlDbType.SmallDateTime);
outMsg8.Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
String userName = outMsg1.Value.ToString();
String mobile = outMsg2.Value.ToString();
String serviceName = outMsg3.Value.ToString();
String serviceTypeName = outMsg4.Value.ToString();
String providerName = outMsg5.Value.ToString();
String providerMail = outMsg6.Value.ToString();
String userMail = outMsg7.Value.ToString();
String timing = outMsg8.Value.ToString();
string MailTo = "*****@*****.**";//change to providerMail
MailMessage Msg = new MailMessage();
Msg.From = new MailAddress("*****@*****.**");
Msg.To.Add(MailTo);
string fileName = System.Web.Hosting.HostingEnvironment.MapPath("~/Templates/mailTemplate.html");//HttpContext.Current.Server.MapPath("~/Templates/mailTemplate.html");// System.Web.Hosting.HostingEnvironment.MapPath("/Templates/mailTemplate.html");//"C:/inetpub/wwwroot/Beauty/Templates/mailTemplate.html";//
string body = fileName;
System.IO.StreamReader objReader;
objReader = new System.IO.StreamReader(fileName);
body = objReader.ReadToEnd();
//replacing contents
string title = "Error Mail";
string contentMsg = "This mail is due to server error in mailing system";
if (bookORcancel.Equals("book"))
{
contentMsg = "There is a new booking for below mensioned service. Please inform the client whether you can provide the specified service on that time.";
title = "New Booking";
Msg.Subject = "New booking for " + serviceName;
body = body.Replace("$bORc$", "Booked at");
}
else if (bookORcancel.Equals("cancel"))
{
contentMsg = "There is a cancellation for on a booking for below mensioned service.";
title = "Booking Cancellation";
Msg.Subject = "A cancellation on " + serviceName;
body = body.Replace("$bORc$", "Cancelled at");
}
body = body.Replace("$contentMsg$", contentMsg);
body = body.Replace("$title$", title);
body = body.Replace("$providerName$", providerName);
body = body.Replace("$serviceName$", serviceName);
body = body.Replace("$serviceType$", serviceTypeName);
body = body.Replace("$timing$", timing);
body = body.Replace("$bORCtime$", activityTime);
body = body.Replace("$clientName$", userName);
body = body.Replace("$clientContact$", userMail);
body = body.Replace("$clientPhone$", mobile);
body = body.Replace("$bookingID$", bookingID);
Msg.Body = body;
Msg.IsBodyHtml = true;
// your remote SMTP server IP.
SmtpClient smtp = new SmtpClient();
smtp.Host = "smtp.gmail.com";
smtp.Port = 587;
smtp.Credentials = new System.Net.NetworkCredential("info.thrithvam", "thrithvam@2015");
smtp.EnableSsl = true;
smtp.Send(Msg);
Msg = null;
}
catch (Exception ex)
{
throw ex;
}
}
