public async Task <List <cPartialSender> > ServicesbySearch_v2(cSearch search) { string uri; string loclatlong = "-1,-1"; List <cPartialSender> ServiceProviders = new List <cPartialSender>(); try { string constr = ConfigurationManager.ConnectionStrings["MyAbDbContext"].ConnectionString; using (SqlConnection con = new SqlConnection(constr)) { SqlCommand cmd = new SqlCommand("sp_ServiceProviderforSearchwithUnReg", con); cmd.CommandType = CommandType.StoredProcedure; SqlDataReader reader; SqlParameter paramwhat = new SqlParameter(); paramwhat.ParameterName = "@what"; paramwhat.Value = search.what; cmd.Parameters.Add(paramwhat); SqlParameter paramuserid = new SqlParameter(); paramuserid.ParameterName = "@userID"; paramuserid.Value = search.userID; cmd.Parameters.Add(paramuserid); SqlParameter paramuserType = new SqlParameter(); paramuserType.ParameterName = "@userType"; paramuserType.Value = search.userType; cmd.Parameters.Add(paramuserType); search = await findLatLngLocality(search); SqlParameter paramwhere = new SqlParameter(); paramwhere.ParameterName = "@where"; paramwhere.Value = search.where; cmd.Parameters.Add(paramwhere); if (search.where.Length < 2) { return(ServiceProviders); } SqlParameter paramlat = new SqlParameter(); paramlat.ParameterName = "@lat"; paramlat.Value = search.lat; cmd.Parameters.Add(paramlat); SqlParameter paramlng = new SqlParameter(); paramlng.ParameterName = "@lng"; paramlng.Value = search.lng; cmd.Parameters.Add(paramlng); con.Open(); reader = cmd.ExecuteReader(); while (reader.Read()) { cPartialSender c = new cPartialSender(); c.SenderID = (int)reader["SenderID"]; c.SenderName = reader["SenderName"].ToString().Trim(); c.SenderContactNo_1 = reader["SenderContactNo_1"].ToString().Trim(); c.ContactHide = (Int16)reader["ContactHide"]; c.cServiceType = reader["cServiceType"].ToString().Trim(); c.ServiceDesc = reader["ServiceDesc"].ToString().Trim(); c.postcode = reader["POSTCODE"].ToString().Trim(); if (reader["logopath"] != null) { if (reader["logopath"].ToString().Length > 0) { c.logopath = reader["logopath"].ToString().Trim(); } } c.subscribeonoff = reader["subscribeonoff"].ToString().Trim(); c.ReviewReceived = (int)reader["ReviewReceived"]; c.msgCommentsReceived = (int)reader["msgCommentsReceived"]; c.msgLikeReceived = (int)reader["msgLikeReceived"]; c.msgTotpublished = (int)reader["msgTotpublished"]; c.msgReadBy = (int)reader["msgReadBy"]; c.address = reader["address"].ToString().Trim(); c.isFCMActive = reader["isFCMActive"].ToString().Trim(); ServiceProviders.Add(c); } } } catch (Exception ex) { // distance.status = "failed"; //return ex.Message; } return(ServiceProviders); }
public async Task <List <cServiceProvider> > ServicesbySearchv1_3(int userid, string userType, string SEARCH) { List <cServiceProvider> spCollctionlist = new List <cServiceProvider>(); List <cPartialSender> ServiceProviders = new List <cPartialSender>(); //List<cPartialSender> SendersList = new List<cPartialSender>(); string retvalue = "1", userPostcode = ""; try { string constr = ConfigurationManager.ConnectionStrings["MyAbDbContext"].ConnectionString; using (SqlConnection con = new SqlConnection(constr)) { SqlCommand cmd = new SqlCommand("sp_listServiceProviderforSearch", con); cmd.CommandType = CommandType.StoredProcedure; SqlDataReader reader; SqlParameter paramsearch = new SqlParameter(); paramsearch.ParameterName = "@search"; paramsearch.Value = SEARCH; cmd.Parameters.Add(paramsearch); SqlParameter paramuserid = new SqlParameter(); paramuserid.ParameterName = "@userID"; paramuserid.Value = userid; cmd.Parameters.Add(paramuserid); SqlParameter paramuserType = new SqlParameter(); paramuserType.ParameterName = "@userType"; paramuserType.Value = userType; cmd.Parameters.Add(paramuserType); /* cmd.Parameters.Add("@Result", SqlDbType.Int); * cmd.Parameters["@Result"].Direction = ParameterDirection.Output;*/ con.Open(); reader = cmd.ExecuteReader(); //retvalue = cmd.Parameters["@Result"].Value.ToString(); while (reader.Read()) { cPartialSender c = new cPartialSender(); c.SenderID = (int)reader["SenderID"]; c.SenderName = reader["SenderName"].ToString().Trim(); c.SenderContactNo_1 = reader["SenderContactNo_1"].ToString().Trim(); c.SenderContactNo_2 = reader["SenderContactNo_2"].ToString().Trim(); c.ContactHide = (Int16)reader["ContactHide"]; c.cServiceType = reader["cServiceType"].ToString().Trim(); c.ServiceDesc = reader["ServiceDesc"].ToString().Trim(); c.postcode = reader["POSTCODE"].ToString().Trim(); if (reader["logopath"] != null) { if (reader["logopath"].ToString().Length > 0) { c.logopath = reader["logopath"].ToString().Trim(); } } c.subscribeonoff = reader["subscribeonoff"].ToString().Trim(); c.ReviewReceived = (int)reader["ReviewReceived"]; c.msgCommentsReceived = (int)reader["msgCommentsReceived"]; c.msgLikeReceived = (int)reader["msgLikeReceived"]; c.msgTotpublished = (int)reader["msgTotpublished"]; c.msgReadBy = (int)reader["msgReadBy"]; c.isFCMActive = reader["isFCMActive"].ToString().Trim(); ServiceProviders.Add(c); //} // } // } while (reader.Read() && sp.cServiceCategory.Equals(reader["cServiceType"].ToString().Trim())); } } } catch (Exception ex) { retvalue = ex.Message.ToString(); } //return Ok(retvalue);*/ try { string constr = ConfigurationManager.ConnectionStrings["MyAbDbContext"].ConnectionString; using (SqlConnection con = new SqlConnection(constr)) { SqlCommand cmd = new SqlCommand("sp_getPostcode", con); cmd.CommandType = CommandType.StoredProcedure; SqlParameter paramuserType = new SqlParameter(); paramuserType.ParameterName = "@userType"; paramuserType.Value = userType; cmd.Parameters.Add(paramuserType); SqlParameter paramuserid = new SqlParameter(); paramuserid.ParameterName = "@userID"; paramuserid.Value = userid; cmd.Parameters.Add(paramuserid); cmd.Parameters.Add("@postcode", SqlDbType.NVarChar, 6); cmd.Parameters["@postcode"].Direction = ParameterDirection.Output; con.Open(); cmd.ExecuteNonQuery(); con.Close(); userPostcode = cmd.Parameters["@postcode"].Value.ToString(); } } catch (Exception e) { } SendNotificationController s = new SendNotificationController(); cPostCodeDistance distance = new cPostCodeDistance(); foreach (cPartialSender snd in ServiceProviders) { if (!userPostcode.Equals(snd.postcode)) { distance = await s.GetCalculatedistanceAsync(userPostcode, snd.postcode); if (distance.rows[0].elements[0].status.Contains("OK")) { snd.postcode = distance.rows[0].elements[0].distance.text; ////the value in KMS. eg 2,435 KM snd.ReviewReceived = int.Parse(distance.rows[0].elements[0].distance.value); /// the value in meters. eg 2435675 } } else { snd.postcode = "LOCAL"; snd.ReviewReceived = 0; } } for (int i = 0; i < ServiceProviders.Count; i++) { for (int j = 0; j < ServiceProviders.Count; j++) { cPartialSender swp = new cPartialSender(); if (ServiceProviders[i].ReviewReceived < ServiceProviders[j].ReviewReceived) { swp = ServiceProviders[j]; ServiceProviders[j] = ServiceProviders[i]; ServiceProviders[i] = swp; } } } cServiceProvider spprov = new cServiceProvider(); spprov.providers = ServiceProviders; spprov.cCount = ServiceProviders.Count; spprov.cServiceCategory = "Results Found"; spprov.cSvcDesc = SEARCH; spCollctionlist.Add(spprov); //return ServiceProviders; CreateEnquiries(userid, userType, spCollctionlist); return(spCollctionlist); }
public async Task <List <cServiceProvider> > GetServiceProviders(int ReceiverID, string RecUserType) { List <cServiceProvider> ServiceProviders = new List <cServiceProvider>(); string retvalue = "1"; try { string constr = ConfigurationManager.ConnectionStrings["MyAbDbContext"].ConnectionString; using (SqlConnection con = new SqlConnection(constr)) { SqlCommand cmd = new SqlCommand("sp_listServiceProvider", con); cmd.CommandType = CommandType.StoredProcedure; SqlDataReader reader; SqlParameter paramReceiverID = new SqlParameter(); paramReceiverID.ParameterName = "@ReceiverID"; paramReceiverID.Value = ReceiverID; cmd.Parameters.Add(paramReceiverID); SqlParameter paramReceiverUserType = new SqlParameter(); paramReceiverUserType.ParameterName = "@RecUserType"; paramReceiverUserType.Value = RecUserType; cmd.Parameters.Add(paramReceiverUserType); cmd.Parameters.Add("@Result", SqlDbType.Int); cmd.Parameters["@Result"].Direction = ParameterDirection.Output; con.Open(); reader = cmd.ExecuteReader(); //retvalue = cmd.Parameters["@Result"].Value.ToString(); reader.Read(); while (retvalue.Equals("1")) { cServiceProvider sp = new cServiceProvider(); sp.providers = new List <cPartialSender>(); sp.cDisplayOrder = (double)reader["cDisplayOrder"]; sp.cServiceCategory = reader["cServiceCategory"].ToString().Trim(); sp.cSvcDesc = reader["cSvcDesc"].ToString().Trim(); sp.cServicetypeid = (int)reader["ID"]; sp.cCount = 0; do { if (reader["SenderID"] != null) { if (reader["SenderID"].ToString().Length > 0) { cPartialSender c = new cPartialSender(); sp.cCount = sp.cCount + 1; c.SenderID = (int)reader["SenderID"]; c.SenderName = reader["SenderName"].ToString().Trim(); c.SenderContactNo_1 = reader["SenderContactNo_1"].ToString().Trim(); c.ContactHide = (Int16)reader["ContactHide"]; c.cServiceType = reader["cServiceCategory"].ToString().Trim(); c.ServiceDesc = reader["ServiceDesc"].ToString().Trim(); if (reader["logopath"] != null) { if (reader["logopath"].ToString().Length > 0) { c.logopath = reader["logopath"].ToString().Trim(); } } c.subscribeonoff = reader["subscribeonoff"].ToString().Trim(); c.ReviewReceived = (int)reader["ReviewReceived"]; c.msgCommentsReceived = (int)reader["msgCommentsReceived"]; c.msgLikeReceived = (int)reader["msgLikeReceived"]; c.msgTotpublished = (int)reader["msgTotpublished"]; c.msgReadBy = (int)reader["msgReadBy"]; sp.providers.Add(c); } } } while (reader.Read() && sp.cServiceCategory.Equals(reader["cServiceCategory"].ToString().Trim())); ServiceProviders.Add(sp); /* if ((int)reader["SenderID"] > 100000) * { retvalue = "1"; } * else * { retvalue = "-1";}*/ } con.Close(); } } catch (Exception ex) { retvalue = ex.Message.ToString(); } //return Ok(retvalue);*/ return(ServiceProviders); }
public async Task <List <cServiceProvider> > GetServiceProviders_v1(int ReceiverID, string RecUserType) { List <cServiceProvider> ServiceProviders = new List <cServiceProvider>(); string retvalue = "1"; try { string constr = ConfigurationManager.ConnectionStrings["MyAbDbContext"].ConnectionString; using (SqlConnection con = new SqlConnection(constr)) { SqlCommand cmd = new SqlCommand("sp_listServiceProvider_v1", con); cmd.CommandType = CommandType.StoredProcedure; SqlDataReader reader; SqlParameter paramReceiverID = new SqlParameter(); paramReceiverID.ParameterName = "@ReceiverID"; paramReceiverID.Value = ReceiverID; cmd.Parameters.Add(paramReceiverID); SqlParameter paramReceiverUserType = new SqlParameter(); paramReceiverUserType.ParameterName = "@RecUserType"; paramReceiverUserType.Value = RecUserType; cmd.Parameters.Add(paramReceiverUserType); cmd.Parameters.Add("@Result", SqlDbType.Int); cmd.Parameters["@Result"].Direction = ParameterDirection.Output; con.Open(); reader = cmd.ExecuteReader(); //retvalue = cmd.Parameters["@Result"].Value.ToString(); //reader.Read(); while (reader.Read()) { cServiceProvider sp = new cServiceProvider(); sp.providers = new List <cPartialSender>(); sp.cDisplayOrder = (double)reader["cDisplayOrder"]; sp.cServiceCategory = reader["cServiceCategory"].ToString().Trim(); sp.cSvcDesc = reader["cSvcDesc"].ToString().Trim(); sp.cServicetypeid = (int)reader["ID"]; sp.providers = new List <cPartialSender>(); sp.cCount = 0; ServiceProviders.Add(sp); } reader.NextResult(); List <cPartialSender> partialSenders; reader.Read(); while (1 == 1) { partialSenders = new List <cPartialSender>(); string cServiceType; do { cPartialSender c = new cPartialSender(); //sp.cCount = sp.cCount + 1; c.SenderID = (int)reader["SenderID"]; c.SenderName = reader["SenderName"].ToString().Trim(); c.SenderContactNo_1 = reader["SenderContactNo_1"].ToString().Trim(); c.ContactHide = (Int16)reader["ContactHide"]; c.cServiceType = reader["cServiceCategory"].ToString().Trim(); cServiceType = c.cServiceType; c.ServiceDesc = reader["ServiceDesc"].ToString().Trim(); if (reader["logopath"] != null) { if (reader["logopath"].ToString().Length > 0) { c.logopath = reader["logopath"].ToString().Trim(); } } c.address = reader["address"].ToString().Trim(); c.subscribeonoff = reader["subscribeonoff"].ToString().Trim(); c.ReviewReceived = (int)reader["ReviewReceived"]; c.msgCommentsReceived = (int)reader["msgCommentsReceived"]; c.msgLikeReceived = (int)reader["msgLikeReceived"]; c.msgTotpublished = (int)reader["msgTotpublished"]; c.msgReadBy = (int)reader["msgReadBy"]; partialSenders.Add(c); } while (reader.Read() && cServiceType.Equals(reader["cServiceCategory"].ToString().Trim())); for (int i = 0; i < ServiceProviders.Count; i++) { if (ServiceProviders[i].cServiceCategory.Equals(partialSenders[0].cServiceType.ToString())) { ServiceProviders[i].providers = partialSenders; ServiceProviders[i].cCount = partialSenders.Count; break; } } } /*do * { * if (reader["SenderID"] != null) * { * if (reader["SenderID"].ToString().Length > 0) * { * cPartialSender c = new cPartialSender(); * //sp.cCount = sp.cCount + 1; * c.SenderID = (int)reader["SenderID"]; * c.SenderName = reader["SenderName"].ToString().Trim(); * c.SenderContactNo_1 = reader["SenderContactNo_1"].ToString().Trim(); * * c.ContactHide = (Int16)reader["ContactHide"]; * c.cServiceType = reader["cServiceCategory"].ToString().Trim(); * c.ServiceDesc = reader["ServiceDesc"].ToString().Trim(); * * if (reader["logopath"] != null) * if (reader["logopath"].ToString().Length > 0) * c.logopath = reader["logopath"].ToString().Trim(); * * c.subscribeonoff = reader["subscribeonoff"].ToString().Trim(); * c.ReviewReceived = (int)reader["ReviewReceived"]; * c.msgCommentsReceived = (int)reader["msgCommentsReceived"]; * c.msgLikeReceived = (int)reader["msgLikeReceived"]; * c.msgTotpublished = (int)reader["msgTotpublished"]; * c.msgReadBy = (int)reader["msgReadBy"]; * //sp.providers.Add(c); * } * } * * * * } while (reader.Read() && sp.cServiceCategory.Equals(reader["cServiceCategory"].ToString().Trim())) ; * */ /* while (retvalue.Equals("1")) * { * cServiceProvider sp = new cServiceProvider(); * sp.providers = new List<cPartialSender>(); * sp.cDisplayOrder= (double)reader["cDisplayOrder"]; * sp.cServiceCategory = reader["cServiceCategory"].ToString().Trim(); * sp.cSvcDesc = reader["cSvcDesc"].ToString().Trim(); * sp.cServicetypeid = (int)reader["ID"]; * sp.cCount = 0; * do * { * if (reader["SenderID"] != null) * { * if (reader["SenderID"].ToString().Length > 0) * { * cPartialSender c = new cPartialSender(); * sp.cCount = sp.cCount + 1; * c.SenderID = (int)reader["SenderID"]; * c.SenderName = reader["SenderName"].ToString().Trim(); * c.SenderContactNo_1 = reader["SenderContactNo_1"].ToString().Trim(); * * c.ContactHide = (Int16)reader["ContactHide"]; * c.cServiceType = reader["cServiceCategory"].ToString().Trim(); * c.ServiceDesc = reader["ServiceDesc"].ToString().Trim(); * * if (reader["logopath"] != null) * if (reader["logopath"].ToString().Length > 0) * c.logopath = reader["logopath"].ToString().Trim(); * * c.subscribeonoff = reader["subscribeonoff"].ToString().Trim(); * c.ReviewReceived = (int)reader["ReviewReceived"]; * c.msgCommentsReceived = (int)reader["msgCommentsReceived"]; * c.msgLikeReceived = (int)reader["msgLikeReceived"]; * c.msgTotpublished = (int)reader["msgTotpublished"]; * c.msgReadBy = (int)reader["msgReadBy"]; * sp.providers.Add(c); * } * } * * * * } while (reader.Read() && sp.cServiceCategory.Equals(reader["cServiceCategory"].ToString().Trim())); * * ServiceProviders.Add(sp); * * * * * }*/ con.Close(); } } catch (Exception ex) { retvalue = ex.Message.ToString(); } //return Ok(retvalue);*/ cServiceProvider tempSP = new cServiceProvider(); for (int i = 0; i < ServiceProviders.Count; i++) { for (int j = 0; j < ServiceProviders.Count - i - 1; j++) { if (ServiceProviders[j].cCount < ServiceProviders[j + 1].cCount) { tempSP = ServiceProviders[j + 1]; ServiceProviders[j + 1] = ServiceProviders[j]; ServiceProviders[j] = tempSP; } } } return(ServiceProviders); }
public async Task <List <cPartialSender> > GetSenderOf(int ReceiverID, string RecUserType) { List <cPartialSender> SendersList = new List <cPartialSender>(); string retvalue; try { string constr = ConfigurationManager.ConnectionStrings["MyAbDbContext"].ConnectionString; using (SqlConnection con = new SqlConnection(constr)) { SqlCommand cmd = new SqlCommand("sp_getSendersOf", con); cmd.CommandType = CommandType.StoredProcedure; SqlDataReader reader; SqlParameter paramReceiverID = new SqlParameter(); paramReceiverID.ParameterName = "@ReceiverID"; paramReceiverID.Value = ReceiverID; cmd.Parameters.Add(paramReceiverID); SqlParameter paramReceiverUserType = new SqlParameter(); paramReceiverUserType.ParameterName = "@ReceiverUserType"; paramReceiverUserType.Value = RecUserType; cmd.Parameters.Add(paramReceiverUserType); con.Open(); reader = cmd.ExecuteReader(); while (reader.Read()) { cPartialSender c = new cPartialSender(); c.SenderID = (int)reader["SenderID"]; c.SenderName = reader["SenderName"].ToString().Trim(); c.SenderContactNo_1 = reader["SenderContactNo_1"].ToString().Trim(); c.subscribeonoff = reader["subscribeonoff"].ToString().Trim(); c.ContactHide = (Int16)reader["ContactHide"]; c.cServiceType = reader["cServiceType"].ToString().Trim(); c.ServiceDesc = reader["ServiceDesc"].ToString().Trim(); c.ReviewReceived = (int)reader["ReviewReceived"]; c.msgCommentsReceived = (int)reader["msgCommentsReceived"]; c.msgLikeReceived = (int)reader["msgLikeReceived"]; c.msgTotpublished = (int)reader["msgTotpublished"]; c.msgReadBy = (int)reader["msgReadBy"]; c.logopath = reader["logopath"].ToString().Trim(); //c.logopath= reader["logopath"].ToString().Trim(); SendersList.Add(c); } con.Close(); } } catch (Exception ex) { retvalue = ex.Message.ToString(); } //return Ok(retvalue);*/ return(SendersList); }