public Response <CustomerModel> UpdateCustomerStatus(CustomerRequestModel model) { string ReturnLink = string.Empty; Response <CustomerModel> returnModel = new Response <CustomerModel>(); try { using (SqlConnection DB = new SqlConnection(SiteKey.ConnectionString)) { DB.ExecuteSql(@"update customer set status = @status where id = @id ", new { id = model.id, status = model.status }); } returnModel.status = (int)EnumClass.ResponseState.Success; returnModel.msg = Resource_Kharban.UpdateSuccessfully; returnModel.success = true; } catch (Exception ex) { returnModel.msg = ex.Message; returnModel.status = (int)EnumClass.ResponseState.ResposityError; returnModel.success = false; LoggingRepository.SaveException(ex); } return(returnModel); }
public Response <DashBoardChartModel> GetAvgRateChart(DashBoardRequestModel model) { string queryString = string.Empty; Response <DashBoardChartModel> returnModel = new Response <DashBoardChartModel>(); returnModel.result = new DashBoardChartModel(); try { using (SqlConnection DB = new SqlConnection(SiteKey.ConnectionString)) { queryString = @"select ISNULL(AVG(provider_rating), 0) avrag from booking where service_id = @service_id and is_deleted = 0 "; returnModel.result.total = DB.QuerySql <int>(queryString, model).FirstOrDefault(); } returnModel.status = (int)EnumClass.ResponseState.Success; returnModel.msg = "Total average"; returnModel.success = true; } catch (Exception ex) { returnModel.msg = ex.Message; returnModel.status = (int)EnumClass.ResponseState.ResposityError; LoggingRepository.SaveException(ex); returnModel.success = false; } return(returnModel); }
public static string GenerateTokenForChangepassword() { int size = 32; //string shortCode = string.Empty; StringBuilder result = new StringBuilder(size); try { char[] chars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890".ToCharArray(); byte[] data = new byte[size]; using (RNGCryptoServiceProvider crypto = new RNGCryptoServiceProvider()) { crypto.GetBytes(data); } foreach (byte b in data) { result.Append(chars[b % (chars.Length)]); } } catch (Exception ex) { LoggingRepository.SaveException(ex); } return(result.ToString()); }
public Response <FaqModel> UpdateFaq(FaqModel model) { string ReturnLink = string.Empty; Response <FaqModel> returnModel = new Response <FaqModel>(); try { using (SqlConnection DB = new SqlConnection(SiteKey.ConnectionString)) { DB.ExecuteSql(@"update faq set question = @question, answer = @answer, question_arabic = @question_arabic, answer_arabic = @answer_arabic where id = @id ", new { id = model.id, model.question, model.question_arabic, model.answer, model.answer_arabic, }); } returnModel.status = (int)EnumClass.ResponseState.Success; returnModel.msg = Resource_Kharban.UpdateSuccessfully; } catch (Exception ex) { returnModel.msg = ex.Message; returnModel.status = (int)EnumClass.ResponseState.ResposityError; LoggingRepository.SaveException(ex); } return(returnModel); }
public Response<string> UploadCustomerImage(CustomerModel model,string imageURL,string imgPath) { Response<string> returnModel = new Response<string>(); try { if (!string.IsNullOrEmpty(model.image) && !string.IsNullOrEmpty(model.image_extension)) { byte[] imageBytes = Convert.FromBase64String(model.image); File.WriteAllBytes(imgPath, imageBytes); } using (SqlConnection DB = new SqlConnection(SiteKey.ConnectionString)) { if (model.ImageType == "profile") { DB.ExecuteSql(@"update customer set profile_picture = @profile_picture where id = @id ", new { id = model.id, profile_picture = imageURL, }); } } returnModel.status = (int)EnumClass.ResponseState.Success; returnModel.msg = Resource_Kharban.UpdateSuccessfully; returnModel.success = true; } catch (Exception ex) { returnModel.msg = ex.Message; returnModel.status = (int)EnumClass.ResponseState.ResposityError; LoggingRepository.SaveException(ex); } return returnModel; }
public Response <FaqModel> AddFaq(FaqModel model) { Response <FaqModel> returnModel = new Response <FaqModel>(); try { using (SqlConnection DB = new SqlConnection(SiteKey.ConnectionString)) { DB.ExecuteSql(@"insert into faq values(@id,@question,@question_arabic,@answer,@answer_arabic,@status,@is_deleted,@created,@modified,@faq_for) ", new { id = Guid.NewGuid().ToString(), model.question, model.question_arabic, model.answer, model.answer_arabic, model.status, model.faq_for, is_deleted = 0, modified = DateTime.Now, created = DateTime.Now, }); } returnModel.success = true; returnModel.status = (int)EnumClass.ResponseState.Success; returnModel.msg = Resource_Kharban.InsertSuccessfully; } catch (Exception ex) { returnModel.msg = ex.Message; returnModel.status = (int)EnumClass.ResponseState.ResposityError; LoggingRepository.SaveException(ex); } return(returnModel); }
public Response <CategoryModel> GetCategory(CategoryModel model) { string ReturnLink = string.Empty; Response <CategoryModel> returnModel = new Response <CategoryModel>(); try { using (SqlConnection DB = new SqlConnection(SiteKey.ConnectionString)) { returnModel.result = DB.QuerySql <CategoryModel>(@"SELECT id, name, image, status FROM service_category where id = @id ", new { id = model.id }).FirstOrDefault(); } returnModel.status = (int)EnumClass.ResponseState.Success; returnModel.msg = ""; //LoggingRepository.SaveException(ex); } catch (Exception ex) { returnModel.msg = ex.Message; returnModel.status = (int)EnumClass.ResponseState.ResposityError; LoggingRepository.SaveException(ex); } return(returnModel); }
public Response <CategoryModel> DeleteCategory(CategoryModel model) { string ReturnLink = string.Empty; Response <CategoryModel> returnModel = new Response <CategoryModel>(); try { using (SqlConnection DB = new SqlConnection(SiteKey.ConnectionString)) { DB.ExecuteSql(@"update service_category set is_deleted = 1 where id = @id ", new { id = model.id }); } returnModel.status = (int)EnumClass.ResponseState.Success; returnModel.msg = Resource_Kharban.DeletedSuccessfully; //LoggingRepository.SaveException(ex); } catch (Exception ex) { returnModel.msg = ex.Message; returnModel.status = (int)EnumClass.ResponseState.ResposityError; LoggingRepository.SaveException(ex); } return(returnModel); }
public static string GenerateShortCode() { int size = 6; string shortCode = string.Empty; StringBuilder result = new StringBuilder(size); try { using (SqlConnection DB = new SqlConnection(SiteKey.ConnectionString)) { char[] chars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890".ToCharArray(); byte[] data = new byte[size]; using (RNGCryptoServiceProvider crypto = new RNGCryptoServiceProvider()) { crypto.GetBytes(data); } foreach (byte b in data) { result.Append(chars[b % (chars.Length)]); } shortCode = DB.QuerySql <string>("select code from shortcode where code = BINARY ?code", new { code = result.ToString() }).FirstOrDefault(); if (!string.IsNullOrEmpty(shortCode)) { GenerateShortCode(); } } } catch (Exception ex) { LoggingRepository.SaveException(ex); } return(result.ToString()); }
public Response<ProviderModel> UpdateProvider(ProviderModel model,string imgPath,string imageURL) { string ReturnLink = string.Empty; Response<ProviderModel> returnModel = new Response<ProviderModel>(); try { if (!string.IsNullOrEmpty(model.image) && !string.IsNullOrEmpty(model.image_extension)) { byte[] imageBytes = Convert.FromBase64String(model.image); File.WriteAllBytes(imgPath, imageBytes); } using (SqlConnection DB = new SqlConnection(SiteKey.ConnectionString)) { DB.ExecuteSql(@"update provider set first_name = @first_name, last_name = @last_name, country_code = @country_code, contact_no = @contact_no, iban_no = @iban_no, service_category_id = @service_category_id where id = @id ", new { id = model.id, model.first_name, model.last_name, model.contact_no, model.iban_no, model.service_category_id, model.country_code }); if (!string.IsNullOrEmpty(model.image) && !string.IsNullOrEmpty(model.image_extension)) { DB.ExecuteSql(@"update provider set profile_picture = @profile_picture where id = @id ", new { id = model.id, profile_picture = imageURL, }); } } returnModel.status = (int)EnumClass.ResponseState.Success; returnModel.msg = Resource_Kharban.UpdateSuccessfully; returnModel.success = true; } catch (Exception ex) { returnModel.msg = ex.Message; returnModel.status = (int)EnumClass.ResponseState.ResposityError; LoggingRepository.SaveException(ex); } return returnModel; }
public Response <DashBoardChartModel> GetPieChart(DashBoardRequestModel model) { string queryString = string.Empty; Response <DashBoardChartModel> returnModel = new Response <DashBoardChartModel>(); returnModel.result = new DashBoardChartModel(); try { using (SqlConnection DB = new SqlConnection(SiteKey.ConnectionString)) { queryString = @"select service_category.name service_name, count(booking.id) total_booking from service_category left join booking on service_id = service_category.id where service_category.is_deleted = 0 group by booking.id,service_category.name"; List <BookingModel> resultList = DB.QuerySql <BookingModel>(queryString).ToList(); returnModel.result.total = resultList.Sum(x => x.total_booking); int hour = DateTime.Now.Hour + 1; DateTime month = DateTime.Now; List <string> Label = new List <string>(); List <decimal> recordlist = new List <decimal>(); foreach (var item in resultList) { Label.Add(item.service_name); recordlist.Add(item.total_booking); } returnModel.result.label = Label; returnModel.result.data = recordlist; } returnModel.status = (int)EnumClass.ResponseState.Success; returnModel.msg = "Service Bookings"; returnModel.success = true; } catch (Exception ex) { returnModel.msg = ex.Message; returnModel.status = (int)EnumClass.ResponseState.ResposityError; LoggingRepository.SaveException(ex); returnModel.success = false; } return(returnModel); }
public Response <int> UpdateSetting(SettingModel model, string imgPath, string imageURL) { Response <int> returnModel = new Response <int>(); try { if (!string.IsNullOrEmpty(model.image) && !string.IsNullOrEmpty(model.image_extension)) { byte[] imageBytes = Convert.FromBase64String(model.image); File.WriteAllBytes(imgPath, imageBytes); } using (SqlConnection DB = new SqlConnection(SiteKey.ConnectionString)) { if (model.type == "platform_fee") { DB.ExecuteSql(@"update setting set platform_fee = @platform_fee , platform_fee_enable = @platform_fee_enable where id = @id ", new { id = model.id, platform_fee = model.platform_fee, platform_fee_enable = model.platform_fee_enable }); } else if (model.type == "tax") { DB.ExecuteSql(@"update setting set tax = @tax where id = @id ", new { id = model.id, tax = model.tax }); } else if (model.type == "distance") { DB.ExecuteSql(@"update setting set distance = @distance where id = @id ", new { id = model.id, distance = model.distance }); } else if (model.type == "logo") { DB.ExecuteSql(@"update setting set logo = @logo where id = @id ", new { id = model.id, logo = imageURL }); } } returnModel.status = (int)EnumClass.ResponseState.Success; returnModel.msg = Resource_Kharban.UpdateSuccessfully; returnModel.success = true; } catch (Exception ex) { returnModel.msg = ex.Message; returnModel.status = (int)EnumClass.ResponseState.ResposityError; LoggingRepository.SaveException(ex); } return(returnModel); }
public Response <CustomerModel> UpdateCustomer(CustomerRequestModel model) { string ReturnLink = string.Empty; Response <CustomerModel> returnModel = new Response <CustomerModel>(); try { if (model != null) { using (SqlConnection DB = new SqlConnection(SiteKey.ConnectionString)) { DB.ExecuteSql(@"update customer set first_name = @first_name, last_name = @last_name, country_code = @country_code, contact_no = @contact_no where id = @id ", new { id = model.id, model.first_name, model.last_name, model.contact_no, model.country_code }); } returnModel.status = (int)EnumClass.ResponseState.Success; returnModel.msg = Resource_Kharban.UpdateSuccessfully; returnModel.success = true; } else { returnModel.msg = "please submit proper data"; returnModel.status = (int)EnumClass.ResponseState.ResposityError; returnModel.success = false; } } catch (Exception ex) { returnModel.msg = ex.Message; returnModel.status = (int)EnumClass.ResponseState.ResposityError; returnModel.success = false; LoggingRepository.SaveException(ex); } return(returnModel); }
public Response <CategoryModel> UpdateCategory(CategoryModel model, string imgPath, string imageURL) { string ReturnLink = string.Empty; Response <CategoryModel> returnModel = new Response <CategoryModel>(); try { if (!string.IsNullOrEmpty(model.image) && !string.IsNullOrEmpty(model.image_extension)) { byte[] imageBytes = Convert.FromBase64String(model.image); File.WriteAllBytes(imgPath, imageBytes); } using (SqlConnection DB = new SqlConnection(SiteKey.ConnectionString)) { DB.ExecuteSql(@"update service_category set name = @name, status = @status, arabic_name = @arabic_name where id = @id ", new { id = model.id, name = model.name.FirstLetterCapitalization(), model.status, arabic_name = model.arabic_name }); if (!string.IsNullOrEmpty(model.image) && !string.IsNullOrEmpty(model.image_extension)) { DB.ExecuteSql(@"update service_category set image = @image where id = @id ", new { id = model.id, image = imageURL }); } } } catch (Exception ex) { returnModel.msg = ex.Message; returnModel.status = (int)EnumClass.ResponseState.ResposityError; LoggingRepository.SaveException(ex); } return(returnModel); }
public Response <CategoryModel> AddCategory(CategoryModel model, string imgPath, string imageURL) { string ReturnLink = string.Empty; Response <CategoryModel> returnModel = new Response <CategoryModel>(); try { if (!string.IsNullOrEmpty(model.image) && !string.IsNullOrEmpty(model.image_extension)) { byte[] imageBytes = Convert.FromBase64String(model.image); File.WriteAllBytes(imgPath, imageBytes); } using (SqlConnection DB = new SqlConnection(SiteKey.ConnectionString)) { DB.ExecuteSql(@"insert into service_category values(@id,@name,@image,@status,@is_deleted,@created,@modified,@arabic_name) ", new { id = Guid.NewGuid().ToString(), image = imageURL, name = model.name.FirstLetterCapitalization(), model.status, is_deleted = 0, modified = DateTime.Now, created = DateTime.Now, arabic_name = model.arabic_name }); } returnModel.success = true; returnModel.status = (int)EnumClass.ResponseState.Success; returnModel.msg = Resource_Kharban.InsertSuccessfully; } catch (Exception ex) { returnModel.msg = ex.Message; returnModel.success = false; returnModel.status = (int)EnumClass.ResponseState.ResposityError; LoggingRepository.SaveException(ex); } return(returnModel); }
public Response <List <TransactionModel> > GetTransictionList(RequestModel model) { Response <List <TransactionModel> > returnModel = new Response <List <TransactionModel> >(); try { using (SqlConnection DB = new SqlConnection(SiteKey.ConnectionString)) { DateTime LastSettledDate = DB.QuerySql <DateTime>(@"select top 1 created from payment_settled_history order by created desc").FirstOrDefault(); string QueryString = @"select provider.id, concat(provider.first_name,' ',provider.last_name) provider_name, provider.iban_no, count(booking_earning.id) booking_count, sum(booking_earning.amount) pending_amount from booking_earning left join booking on booking_earning.booking_id = booking.id left join provider on booking.provider_id = provider.id where booking_earning.is_deleted = 0 "; if (LastSettledDate != DateTime.MinValue) { QueryString += " and booking_earning.created > @LastSettledDate "; } QueryString += " group by provider.first_name, provider.last_name, provider.id, provider.iban_no "; returnModel.result = DB.QuerySql <TransactionModel>(QueryString, new { LastSettledDate }).ToList(); } returnModel.status = (int)EnumClass.ResponseState.Success; returnModel.msg = "Transiction List"; returnModel.success = true; } catch (Exception ex) { returnModel.msg = ex.Message; returnModel.status = (int)EnumClass.ResponseState.ResposityError; LoggingRepository.SaveException(ex); } return(returnModel); }
public Response <DashBoardModel> GetReportData(RequestModel model) { Response <DashBoardModel> returnModel = new Response <DashBoardModel>(); DashBoardModel result = new DashBoardModel(); try { using (SqlConnection DB = new SqlConnection(SiteKey.ConnectionString)) { result.customer_count = DB.QuerySql <int>(@"select count(id) from customer where is_deleted = 0").FirstOrDefault(); result.booking_count = DB.QuerySql <int>(@"select count(id) from booking where is_deleted = 0").FirstOrDefault(); DateTime LastSettledDate = DB.QuerySql <DateTime>(@"select top 1 created from payment_settled_history order by created desc").FirstOrDefault(); string QueryString = @"select isnull(sum(amount),0) transaction_amount from booking_earning "; if (LastSettledDate != DateTime.MinValue) { QueryString += " where created > @LastSettledDate "; } result.transaction_amount = DB.QuerySql <decimal>(QueryString, new { LastSettledDate = LastSettledDate }).FirstOrDefault(); returnModel.result = result; } returnModel.status = (int)EnumClass.ResponseState.Success; returnModel.msg = "Data Count"; returnModel.success = true; } catch (Exception ex) { returnModel.msg = ex.Message; returnModel.status = (int)EnumClass.ResponseState.ResposityError; LoggingRepository.SaveException(ex); } return(returnModel); }
public Response <List <CustomerModel> > GetCustomerList(RequestModel model) { Response <List <CustomerModel> > returnModel = new Response <List <CustomerModel> >(); try { using (SqlConnection DB = new SqlConnection(SiteKey.ConnectionString)) { returnModel.result = DB.QuerySql <CustomerModel>(@"select id, user_id, first_name, last_name, contact_no, otp, email, country_code, is_mobile_verified , is_email_verified ,is_deleted, created_by ,status, created, modified FROM customer where is_deleted = 0 ").ToList(); } returnModel.status = (int)EnumClass.ResponseState.Success; returnModel.msg = "Customer List"; returnModel.success = true; } catch (Exception ex) { returnModel.msg = ex.Message; returnModel.status = (int)EnumClass.ResponseState.ResposityError; LoggingRepository.SaveException(ex); } return(returnModel); }
public Response <List <ProviderModel> > GetProviderList(RequestModel model) { Response <List <ProviderModel> > returnModel = new Response <List <ProviderModel> >(); try { using (SqlConnection DB = new SqlConnection(SiteKey.ConnectionString)) { returnModel.result = DB.QuerySql <ProviderModel>(@"SELECT id, user_id, first_name, last_name, profile_picture, iban_no, contact_no, country_code, otp, email, is_policy_accepted, is_mobile_verified, is_email_verified, admin_approve, is_online, is_notification_enable, auth_token, created_by, status, created, modified FROM provider where is_deleted = 0 ").ToList(); } returnModel.status = (int)EnumClass.ResponseState.Success; returnModel.msg = "Provider List"; returnModel.success = true; } catch (Exception ex) { returnModel.msg = ex.Message; returnModel.status = (int)EnumClass.ResponseState.ResposityError; LoggingRepository.SaveException(ex); } return(returnModel); }
public Response <List <BookingModel> > GetBookingList(RequestModel model) { Response <List <BookingModel> > returnModel = new Response <List <BookingModel> >(); try { using (SqlConnection DB = new SqlConnection(SiteKey.ConnectionString)) { returnModel.result = DB.QuerySql <BookingModel>(@"select id, booking_code, customer_id, provider_id, service_id, coupon_id, address_title, landmark, longitude, description, booking_status, provider_cancellation_fee, status, is_deleted, created, modified, payment_type FROM booking where is_deleted = 0 ").ToList(); } returnModel.status = (int)EnumClass.ResponseState.Success; returnModel.msg = "Booking List"; returnModel.success = true; } catch (Exception ex) { returnModel.msg = ex.Message; returnModel.status = (int)EnumClass.ResponseState.ResposityError; LoggingRepository.SaveException(ex); } return(returnModel); }
public Response <SettingModel> GetSetting(RequestModel model) { Response <SettingModel> returnModel = new Response <SettingModel>(); try { using (SqlConnection DB = new SqlConnection(SiteKey.ConnectionString)) { returnModel.result = DB.QuerySql <SettingModel>(@"select id, primary_email, primary_contact, provider_cancellation_fee, platform_fee_enable, facebook_link, twitter_link, google_plus_link, instagram_link, nearby_radius, logo, platform_fee, tax, distance, status, created, modified from setting ").FirstOrDefault(); } returnModel.status = (int)EnumClass.ResponseState.Success; returnModel.msg = "Setting "; returnModel.success = true; } catch (Exception ex) { returnModel.msg = ex.Message; returnModel.status = (int)EnumClass.ResponseState.ResposityError; LoggingRepository.SaveException(ex); } return(returnModel); }
public Response <List <CategoryModel> > GetCategoryList() { Response <List <CategoryModel> > returnModel = new Response <List <CategoryModel> >(); try { using (SqlConnection DB = new SqlConnection(SiteKey.ConnectionString)) { returnModel.result = DB.QuerySql <CategoryModel>("SELECT id, name, image, status FROM service_category where is_deleted = 0 and status = 1 ").ToList(); } returnModel.status = (int)EnumClass.ResponseState.Success; returnModel.msg = "Service Category List"; returnModel.success = true; } catch (Exception ex) { returnModel.msg = ex.Message; returnModel.status = (int)EnumClass.ResponseState.ResposityError; LoggingRepository.SaveException(ex); returnModel.success = false; } return(returnModel); }
public Response <List <FaqModel> > GetFaqList(FaqModel model) { Response <List <FaqModel> > returnModel = new Response <List <FaqModel> >(); try { using (SqlConnection DB = new SqlConnection(SiteKey.ConnectionString)) { returnModel.result = DB.QuerySql <FaqModel>("SELECT id, question, answer, question_arabic, answer_arabic, status, is_deleted, created, modified, faq_for from faq where is_deleted = 0 and faq_for = @FaqFor ", new { FaqFor = model.faq_for }).ToList(); } returnModel.status = (int)EnumClass.ResponseState.Success; returnModel.msg = "FAQ List"; returnModel.success = true; } catch (Exception ex) { returnModel.msg = ex.Message; returnModel.status = (int)EnumClass.ResponseState.ResposityError; LoggingRepository.SaveException(ex); returnModel.success = false; } return(returnModel); }
public Response <FaqModel> GetFaq(FaqModel model) { string ReturnLink = string.Empty; Response <FaqModel> returnModel = new Response <FaqModel>(); try { using (SqlConnection DB = new SqlConnection(SiteKey.ConnectionString)) { returnModel.result = DB.QuerySql <FaqModel>(@"SELECT id, question, answer, question_arabic, answer_arabic, status, is_deleted, created, modified, faq_for from faq where id = @id ", new { id = model.id }).FirstOrDefault(); } returnModel.status = (int)EnumClass.ResponseState.Success; returnModel.msg = ""; } catch (Exception ex) { returnModel.msg = ex.Message; returnModel.status = (int)EnumClass.ResponseState.ResposityError; LoggingRepository.SaveException(ex); } return(returnModel); }
public ResponseList <List <BookingModel> > GetBookings(RequestModel model) { string ReturnLink = string.Empty; string queryString = string.Empty; string queryCount = string.Empty; int TotalRecords = 0; string orderbyString = string.Empty; int recoardFrom = ((model.page - 1) * 10) + 1; int recoardTo = model.page * 10; ResponseList <List <BookingModel> > returnModel = new ResponseList <List <BookingModel> >(); try { using (SqlConnection DB = new SqlConnection(SiteKey.ConnectionString)) { if (!string.IsNullOrEmpty(model.sortby)) { if (model.sortby == "newtoold") { orderbyString = " order by booking.created desc"; } if (model.sortby == "oldtonew") { orderbyString = " order by booking.created"; } } else { orderbyString = " order by booking.created desc"; } queryCount = @"SELECT count(booking.id) totalrecord FROM booking left join customer on customer.id = customer_id left join provider on provider.id = provider_id left join service_category on service_category.id = service_id where booking.is_deleted = 0 "; queryString = @"select * from (SELECT ROW_NUMBER() OVER (" + orderbyString + @") row_num, booking.id, LTRIM(RTRIM(booking.booking_code)) as booking_code, coupon_id, booking_amount, address_title, landmark, booking.longitude, booking.latitude, booking.description, booking.booking_status, provider_cancellation_fee, booking.status, booking.created, booking.payment_type, service_category.name service_name, LTRIM(RTRIM((CONCAT(provider.first_name,' ',provider.last_name)))) provider_name, CONCAT(customer.first_name,' ',customer.last_name) customer_name, provider_rating, provider_rating_description, distance from booking left join customer on customer.id = customer_id left join provider on provider.id = provider_id left join service_category on service_category.id = service_id where booking.is_deleted = 0 "; if (model != null) { if (model.filterby == "booking_code") { queryString += " and booking.booking_code like @keyword "; queryCount += " and booking.booking_code like @keyword "; } else if (model.filterby == "provider_name") { queryString += " and ((provider.first_name+' '+provider.last_name like @keyword) or provider.first_name like @keyword or provider.last_name like @keyword) "; queryCount += " and ((provider.first_name+' '+provider.last_name like @keyword) or provider.first_name like @keyword or provider.last_name like @keyword) "; } else if (model.filterby == "customer_name") { queryString += " and ((customer.first_name+' '+customer.last_name like @keyword) or customer.first_name like @keyword or customer.last_name like @keyword) "; queryCount += " and ((customer.first_name+' '+customer.last_name like @keyword) or customer.first_name like @keyword or customer.last_name like @keyword) "; } else if (model.filterby == "service_name") { queryString += " and service_category.name like @keyword "; queryCount += " and service_category.name like @keyword "; } if (model.filterby2 == "provider_id") { queryString += " and booking.provider_id = @keyword2 "; queryCount += " and booking.provider_id = @keyword2 "; } } queryString += " ) t where row_num between " + recoardFrom + " and " + recoardTo; returnModel.result = DB.QuerySql <BookingModel>(queryString, new { keyword = "%" + model.keyword + "%", keyword2 = model.keyword2 }).ToList(); TotalRecords = DB.QuerySql <int>(queryCount, new { keyword = "%" + model.keyword + "%", keyword2 = model.keyword2 }).FirstOrDefault(); if (returnModel.result != null) { foreach (var item in returnModel.result) { item.booking_receipts = DB.QuerySql <BookingReceiptModel>("select receipt_status, receipt_amount, customer_receipt_description, provider_receipt_description from booking_receipt where is_deleted = 0 and booking_id = @BookingId ", new { BookingId = item.id }).ToList(); } } returnModel.totalDocs = TotalRecords; returnModel.limit = 10; returnModel.totalPages = (TotalRecords / 10) + ((TotalRecords % 10) > 0 ? 1 : 0); returnModel.hasNextPage = model.page < returnModel.totalPages; returnModel.hasPrevPage = returnModel.totalPages > 1 && model.page > 1; returnModel.page = model.page; returnModel.nextPage = model.page + 1; returnModel.pagingCounter = recoardFrom; returnModel.prevPage = model.page - 1; } returnModel.status = (int)EnumClass.ResponseState.Success; returnModel.msg = "Booking List"; returnModel.success = true; } catch (Exception ex) { returnModel.msg = ex.Message; returnModel.status = (int)EnumClass.ResponseState.ResposityError; returnModel.success = false; LoggingRepository.SaveException(ex); } return(returnModel); }
public ResponseList <List <CategoryModel> > GetCategories(RequestModel model) { string ReturnLink = string.Empty; string queryString = string.Empty; string orderbyString = string.Empty; string queryCount = string.Empty; int TotalRecords = 0; int recoardFrom = ((model.page - 1) * 10) + 1; int recoardTo = model.page * 10; ResponseList <List <CategoryModel> > returnModel = new ResponseList <List <CategoryModel> >(); try { using (SqlConnection DB = new SqlConnection(SiteKey.ConnectionString)) { if (!string.IsNullOrEmpty(model.sortby)) { if (model.sortby == "atoz") { orderbyString = " order by name "; } else if (model.sortby == "ztoa") { orderbyString = " order by name desc "; } else if (model.sortby == "newtoold") { orderbyString = " order by created desc"; } else if (model.sortby == "oldtonew") { orderbyString = " order by created"; } } else { orderbyString = " order by created desc"; } queryCount = @"SELECT count(id) totalrecord FROM service_category where is_deleted = 0 "; queryString = @"select * from (SELECT ROW_NUMBER() OVER (" + orderbyString + @") row_num, id, name, image, status FROM service_category where is_deleted = 0 "; if (model.filterby == "name") { queryString += " and name like @keyword "; queryCount += " and name like @keyword "; } if (!string.IsNullOrEmpty(model.keyword2)) { queryString += " and status = @keyword2 "; queryCount += " and status = @keyword2 "; } queryString += " ) t where row_num between " + recoardFrom + " and " + recoardTo; returnModel.result = DB.QuerySql <CategoryModel>(queryString, new { keyword = "%" + model.keyword + "%", keyword2 = model.keyword2 }).ToList(); TotalRecords = DB.QuerySql <int>(queryCount, model).FirstOrDefault(); returnModel.totalDocs = TotalRecords; returnModel.limit = 10; returnModel.totalPages = (TotalRecords / 10) + ((TotalRecords % 10) > 0 ? 1 : 0); returnModel.hasNextPage = model.page < returnModel.totalPages; returnModel.hasPrevPage = returnModel.totalPages > 1 && model.page > 1; returnModel.page = model.page; returnModel.nextPage = model.page + 1; returnModel.pagingCounter = recoardFrom; returnModel.prevPage = model.page - 1; } returnModel.status = (int)EnumClass.ResponseState.Success; returnModel.msg = "Service Category List"; returnModel.success = true; } catch (Exception ex) { returnModel.msg = ex.Message; returnModel.status = (int)EnumClass.ResponseState.ResposityError; LoggingRepository.SaveException(ex); returnModel.success = false; } return(returnModel); }
public Response <DashBoardChartModel> GetGrowthChart(DashBoardRequestModel model) { string queryString = string.Empty; Response <DashBoardChartModel> returnModel = new Response <DashBoardChartModel>(); returnModel.result = new DashBoardChartModel(); try { using (SqlConnection DB = new SqlConnection(SiteKey.ConnectionString)) { queryString = @"select id,created from booking where is_deleted = 0 and status = 1 "; if (model != null && !string.IsNullOrEmpty(model.filterby)) { if (model.filterby == "today") { queryString += " and cast(created as Date) = cast(getdate() as Date) "; } else if (model.filterby == "week") { queryString += " and DateDiff(wk,created,getdate()) = 0 "; } else if (model.filterby == "month") { queryString += " and DateDiff(mm,created,getdate()) = 0 "; } else if (model.filterby == "year") { queryString += " and DateDiff(yy,created,getdate()) = 0 "; } } List <BookingModel> resultList = DB.QuerySql <BookingModel>(queryString).ToList(); returnModel.result.total = resultList.Count; int hour = DateTime.Now.Hour + 1; DateTime month = DateTime.Now; List <string> Label = new List <string>(); List <decimal> recordlist = new List <decimal>(); if (model.filterby == "today") { for (int i = 1; i <= hour; i++) { if (i <= 12) { Label.Add(i + " AM"); } else { Label.Add((i - 12) + " PM"); } recordlist.Add(resultList.Where(x => x.created.Hour < i && x.created.Hour >= i - 1).Count()); } } else if (model.filterby == "week") { for (int i = 0; i <= 6; i++) { DayOfWeek myValueAsEnum = (DayOfWeek)i; Label.Add(myValueAsEnum.ToString()); recordlist.Add(resultList.Where(x => x.created.DayOfWeek == myValueAsEnum).Count()); } } else if (model.filterby == "month") { for (int i = 1; i <= 31; i++) { Label.Add(i.ToString()); recordlist.Add(resultList.Where(x => x.created.Day == i).Count()); } } else if (model.filterby == "year") { for (int i = 1; i <= 12; i++) { MonthNameList myValueAsEnum = (MonthNameList)i; Label.Add(myValueAsEnum.ToString()); recordlist.Add(resultList.Where(x => x.created.Month == i).Count()); } } returnModel.result.label = Label; returnModel.result.data = recordlist; } returnModel.status = (int)EnumClass.ResponseState.Success; returnModel.msg = "Total Earns"; returnModel.success = true; } catch (Exception ex) { returnModel.msg = ex.Message; returnModel.status = (int)EnumClass.ResponseState.ResposityError; LoggingRepository.SaveException(ex); returnModel.success = false; } return(returnModel); }
public Response <int> ProviderPaymentSettle(RequestModel model) { Response <int> returnModel = new Response <int>(); List <TransactionModel> AllTransactions = new List <TransactionModel>(); try { using (SqlConnection DB = new SqlConnection(SiteKey.ConnectionString)) { DateTime LastSettledDate = DB.QuerySql <DateTime>(@"select top 1 created from payment_settled_history order by created desc").FirstOrDefault(); string QueryString = @"select provider.id, concat(provider.first_name,' ',provider.last_name) provider_name, count(booking_earning.id) booking_count, sum(booking_earning.amount) pending_amount from booking_earning left join booking on booking_earning.booking_id = booking.id left join provider on booking.provider_id = provider.id where booking_earning.is_deleted = 0 "; if (LastSettledDate != DateTime.MinValue) { QueryString += " and booking_earning.created > @LastSettledDate "; } QueryString += " group by provider.first_name,provider.last_name,provider.id "; AllTransactions = DB.QuerySql <TransactionModel>(QueryString, new { LastSettledDate }).ToList(); if (AllTransactions.Count > 0) { foreach (var item in AllTransactions) { DB.ExecuteSql(@"insert into payment_settled_history(id, provider_id, booking_count, booking_ids, settled_amount, created) values(@id, @provider_id, @booking_count, @booking_ids, @settled_amount, @created)", new { id = Guid.NewGuid().ToString(), provider_id = item.id, booking_count = item.booking_count, settled_amount = item.pending_amount, booking_ids = "", created = DateTime.UtcNow }); } returnModel.status = (int)EnumClass.ResponseState.Success; returnModel.msg = "All pending payments are settled successfully"; returnModel.success = true; } else { returnModel.status = (int)EnumClass.ResponseState.Success; returnModel.msg = "There are no pending payments are available for settlement "; returnModel.success = true; } } } catch (Exception ex) { returnModel.msg = ex.Message; returnModel.status = (int)EnumClass.ResponseState.ResposityError; LoggingRepository.SaveException(ex); returnModel.success = false; } return(returnModel); }
public Response <DashBoardChartModel> GetCustomerChart(DashBoardRequestModel model) { string queryString = string.Empty; Response <DashBoardChartModel> returnModel = new Response <DashBoardChartModel>(); returnModel.result = new DashBoardChartModel(); try { using (SqlConnection DB = new SqlConnection(SiteKey.ConnectionString)) { queryString = @"select id,created from customer where is_deleted = 0 "; if (model != null && !string.IsNullOrEmpty(model.filterby)) { if (model.filterby == "today") { queryString += " and cast(created as Date) = cast(getdate() as Date) "; } if (model.filterby == "all") { queryString += " and created > DATEADD(year,-1,GETDATE()) "; } } List <CustomerModel> resultList = DB.QuerySql <CustomerModel>(queryString).ToList(); returnModel.result.total = resultList.Count; int hour = DateTime.Now.Hour + 1; DateTime month = DateTime.Now; List <string> Label = new List <string>(); List <decimal> recordlist = new List <decimal>(); if (model.filterby == "today") { for (int i = 1; i <= hour; i++) { if (i <= 12) { Label.Add(i + " AM"); } else { Label.Add((i - 12) + " PM"); } recordlist.Add(resultList.Where(x => x.created.Hour < i && x.created.Hour >= i - 1).Count()); } } else if (model.filterby == "all") { for (DateTime i = DateTime.Now.AddMonths(-11); i <= month; i = i.AddMonths(1)) { MonthNameList myValueAsEnum = (MonthNameList)i.Month; Label.Add(myValueAsEnum.ToString()); recordlist.Add(resultList.Where(x => x.created.Month == i.Month).Count()); } } returnModel.result.label = Label; returnModel.result.data = recordlist; } returnModel.status = (int)EnumClass.ResponseState.Success; returnModel.msg = "Total Customers"; returnModel.success = true; } catch (Exception ex) { returnModel.msg = ex.Message; returnModel.status = (int)EnumClass.ResponseState.ResposityError; LoggingRepository.SaveException(ex); returnModel.success = false; } return(returnModel); }
public ResponseList <List <CustomerModel> > GetCustomers(RequestModel model) { string ReturnLink = string.Empty; string queryString = string.Empty; string orderbyString = string.Empty; string queryCount = string.Empty; int TotalRecords = 0; int recoardFrom = ((model.page - 1) * 10) + 1; int recoardTo = model.page * 10; ResponseList <List <CustomerModel> > returnModel = new ResponseList <List <CustomerModel> >(); try { using (SqlConnection DB = new SqlConnection(SiteKey.ConnectionString)) { if (model != null) { if (!string.IsNullOrEmpty(model.sortby)) { if (model.sortby == "atoz") { orderbyString = " order by first_name "; } if (model.sortby == "ztoa") { orderbyString = " order by first_name desc "; } if (model.sortby == "newtoold") { orderbyString = " order by created desc"; } if (model.sortby == "oldtonew") { orderbyString = " order by created"; } } else { orderbyString = " order by created desc"; } queryCount = @"SELECT count(id) totalrecord FROM customer where is_deleted = 0 "; queryString = @"select * from (SELECT ROW_NUMBER() OVER (" + orderbyString + @") row_num, id, user_id, first_name, last_name, contact_no, otp, email, country_code, is_mobile_verified , is_email_verified ,is_deleted, created_by ,status, created, modified FROM customer where is_deleted = 0 "; if (model.filterby == "name") { queryString += " and ((customer.first_name+' '+customer.last_name like @keyword) or first_name like @keyword or last_name like @keyword) "; queryCount += " and ((customer.first_name+' '+customer.last_name like @keyword) or first_name like @keyword or last_name like @keyword) "; } if (model.filterby == "contact_number") { queryString += " and contact_no like @keyword "; queryCount += " and contact_no like @keyword "; } queryString += " ) t where row_num between " + recoardFrom + " and " + recoardTo; returnModel.result = DB.QuerySql <CustomerModel>(queryString, new { keyword = "%" + model.keyword + "%", }).ToList(); TotalRecords = DB.QuerySql <int>(queryCount, new { keyword = "%" + model.keyword + "%", }).FirstOrDefault(); } returnModel.totalDocs = TotalRecords; returnModel.limit = 10; returnModel.totalPages = (TotalRecords / 10) + ((TotalRecords % 10) > 0 ? 1 : 0); returnModel.hasNextPage = model.page < returnModel.totalPages; returnModel.hasPrevPage = returnModel.totalPages > 1 && model.page > 1; returnModel.page = model.page; returnModel.nextPage = model.page + 1; returnModel.pagingCounter = recoardFrom; returnModel.prevPage = model.page - 1; } returnModel.status = (int)EnumClass.ResponseState.Success; returnModel.msg = "Users list"; returnModel.success = true; } catch (Exception ex) { returnModel.msg = ex.Message; returnModel.status = (int)EnumClass.ResponseState.ResposityError; LoggingRepository.SaveException(ex); returnModel.success = false; } return(returnModel); }