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);
}
