public async Task <IEnumerable <int> > GetActiveAccountsIdsAsync()
{
using (var db = new ScraperLinkedInDBEntities())
{
return(await db.Accounts.Where(x => !x.IsBlocked && !x.IsDeleted).Select(s => s.Id).ToListAsync());
}
}
public async Task <Payment> UpdatePaymentAsync(Payment payment)
{
using (var db = new ScraperLinkedInDBEntities())
{
var paymentDb = await db.Payments.Where(x => x.Id == payment.Id).FirstOrDefaultAsync();
var totalDays = (DateTime.UtcNow - paymentDb.PaymentOn.Value).TotalDays;
if (totalDays > paymentDb.Validity)
{
paymentDb.Validity = payment.Validity;
}
else if (totalDays == paymentDb.Validity)
{
paymentDb.Validity = payment.Validity + 1;
}
else if (totalDays < paymentDb.Validity)
{
paymentDb.Validity = paymentDb.Validity - (int)totalDays + payment.Validity + 1;
}
paymentDb.Guide = Guid.NewGuid();
paymentDb.PaymentOn = DateTime.UtcNow;
paymentDb.UpdateOn = DateTime.UtcNow;
await db.SaveChangesAsync();
return(paymentDb);
}
}
public async Task <Account> GetAccountByIdAsync(int id)
{
using (var db = new ScraperLinkedInDBEntities())
{
return(await db.Accounts.Where(x => x.Id == id).FirstOrDefaultAsync());
}
}
public async Task <Profile> GetProfileByIdAsync(int id, int accountId)
{
using (var db = new ScraperLinkedInDBEntities())
{
return(await db.Profiles.Where(x => x.AccountID == accountId && x.Id == id).FirstOrDefaultAsync());
}
}
public async Task <Setting> GetSettingByAccountIdAsync(int accountId)
{
using (var db = new ScraperLinkedInDBEntities())
{
return(await db.Settings.Where(x => x.AccountId == accountId).FirstOrDefaultAsync());
}
}
public async Task <Account> GetAccountByEmailAsync(string email)
{
using (var db = new ScraperLinkedInDBEntities())
{
return(await db.Accounts.Where(x => x.Email == email).FirstOrDefaultAsync());
}
}
public async Task <Payment> GetPaymentByGuideAsync(Guid guid)
{
using (var db = new ScraperLinkedInDBEntities())
{
return(await db.Payments.Where(x => x.Guide == guid).FirstOrDefaultAsync());
}
}
public async Task <SuitableProfile> GetSuitableProfileByIdAsync(int id)
{
using (var db = new ScraperLinkedInDBEntities())
{
return(await db.SuitableProfiles.Where(x => x.Id == id).FirstOrDefaultAsync());
}
}
public async Task InsertSuitableProfilesAsync(IEnumerable <SuitableProfile> suitableProfiles)
{
using (var db = new ScraperLinkedInDBEntities())
{
foreach (var suitableProfile in suitableProfiles)
{
suitableProfile.FirstName = string.IsNullOrEmpty(suitableProfile.FirstName) ? "..." : suitableProfile.FirstName;
suitableProfile.LastName = string.IsNullOrEmpty(suitableProfile.LastName) ? "..." : suitableProfile.LastName;
suitableProfile.Job = string.IsNullOrEmpty(suitableProfile.Job) ? "..." : suitableProfile.Job;
suitableProfile.PersonLinkedIn = string.IsNullOrEmpty(suitableProfile.PersonLinkedIn) ? "..." : suitableProfile.PersonLinkedIn;
suitableProfile.Company = string.IsNullOrEmpty(suitableProfile.Company) ? "..." : suitableProfile.Company;
suitableProfile.Website = string.IsNullOrEmpty(suitableProfile.Website) ? "..." : suitableProfile.Website;
suitableProfile.CompanyLogoUrl = string.IsNullOrEmpty(suitableProfile.CompanyLogoUrl) ? "..." : suitableProfile.CompanyLogoUrl;
suitableProfile.CrunchUrl = string.IsNullOrEmpty(suitableProfile.CrunchUrl) ? "..." : suitableProfile.CrunchUrl;
suitableProfile.Email = string.IsNullOrEmpty(suitableProfile.Email) ? "..." : suitableProfile.Email;
suitableProfile.EmailStatus = string.IsNullOrEmpty(suitableProfile.EmailStatus) ? "..." : suitableProfile.EmailStatus;
suitableProfile.City = string.IsNullOrEmpty(suitableProfile.City) ? "..." : suitableProfile.City;
suitableProfile.State = string.IsNullOrEmpty(suitableProfile.State) ? "..." : suitableProfile.State;
suitableProfile.Country = string.IsNullOrEmpty(suitableProfile.Country) ? "..." : suitableProfile.Country;
suitableProfile.PhoneNumber = string.IsNullOrEmpty(suitableProfile.PhoneNumber) ? "..." : suitableProfile.PhoneNumber;
suitableProfile.AmountEmployees = string.IsNullOrEmpty(suitableProfile.AmountEmployees) ? "..." : suitableProfile.AmountEmployees;
suitableProfile.Industry = string.IsNullOrEmpty(suitableProfile.Industry) ? "..." : suitableProfile.Industry;
suitableProfile.Twitter = string.IsNullOrEmpty(suitableProfile.Twitter) ? "..." : suitableProfile.Twitter;
suitableProfile.Facebook = string.IsNullOrEmpty(suitableProfile.Facebook) ? "..." : suitableProfile.Facebook;
suitableProfile.TechStack = string.IsNullOrEmpty(suitableProfile.TechStack) ? "..." : suitableProfile.TechStack;
suitableProfile.DateTimeCreation = DateTime.Now;
db.SuitableProfiles.Add(suitableProfile);
}
await db.SaveChangesAsync();
}
}
public async Task InsertSettingAsync(Setting setting)
{
using (var db = new ScraperLinkedInDBEntities())
{
db.Settings.Add(setting);
await db.SaveChangesAsync();
}
}
public async Task <int> GetCountCompaniesInProcess(int accountId)
{
using (var db = new ScraperLinkedInDBEntities())
{
return(await db.Companies.Where(x => x.AccountId == accountId && ((x.ExecutionStatusID == (int)Models.Types.ExecutionStatus.Created) || (x.ExecutionStatusID == (int)Models.Types.ExecutionStatus.Queued)))
.CountAsync());
}
}
public async Task <int> GetCountNewProfilesAsync(int accountId)
{
using (var db = new ScraperLinkedInDBEntities())
{
return(await db.Profiles.Where(x => x.AccountID == accountId && (DbFunctions.TruncateTime(x.DateСreation) == DbFunctions.TruncateTime(DateTime.Now)) && (x.ProfileStatusID == (int)Models.Types.ProfileStatus.Undefined) && (x.ExecutionStatusID == (int)Models.Types.ExecutionStatus.Created))
.CountAsync());
}
}
public async Task InsertDebugLogAsync(DebugLog debugLog)
{
using (var db = new ScraperLinkedInDBEntities())
{
db.DebugLogs.Add(debugLog);
await db.SaveChangesAsync();
}
}
public async Task InsertCompanyAsync(Company company)
{
using (var db = new ScraperLinkedInDBEntities())
{
db.Companies.Add(company);
await db.SaveChangesAsync();
}
}
public async Task <IEnumerable <DebugLog> > GetNewDebugLogsAsync(int accountId, int lastDebugLogId, int size = 50)
{
using (var db = new ScraperLinkedInDBEntities())
{
return(await db.DebugLogs.Where(x => x.Id > lastDebugLogId && x.AccountId == accountId)
.Take(size)
.ToListAsync());
}
}
public async Task ChangeAccountRoleAsync(int accountId, string role)
{
using (var db = new ScraperLinkedInDBEntities())
{
var accountDb = db.Accounts.Where(x => x.Id == accountId).FirstOrDefault();
accountDb.Role = role;
await db.SaveChangesAsync();
}
}
public async Task DeleteAccountAsync(int accountId)
{
using (var db = new ScraperLinkedInDBEntities())
{
var accountDb = db.Accounts.Where(x => x.Id == accountId).FirstOrDefault();
accountDb.IsDeleted = true;
await db.SaveChangesAsync();
}
}
public async Task ChangeAccountBlockAsync(int accountId, bool isBlocked)
{
using (var db = new ScraperLinkedInDBEntities())
{
var accountDb = db.Accounts.Where(x => x.Id == accountId).FirstOrDefault();
accountDb.IsBlocked = isBlocked;
await db.SaveChangesAsync();
}
}
public async Task <IEnumerable <SuitableProfile> > GetSuitableProfilesAsync(DateTime startDate, DateTime endDate, int accountId, int page = 1, int size = 50)
{
using (var db = new ScraperLinkedInDBEntities())
{
return(await db.SuitableProfiles.Where(x => x.AccountID == accountId && x.DateTimeCreation >= startDate && x.DateTimeCreation <= endDate)
.OrderByDescending(x => x.DateTimeCreation)
.Skip((page * size) - size).Take(size)
.ToListAsync());
}
}
public async Task <Account> InsertAccountAsync(Account account)
{
using (var db = new ScraperLinkedInDBEntities())
{
var accountDb = db.Accounts.Add(account);
await db.SaveChangesAsync();
return(accountDb);
}
}
public async Task UpdateScraperStatus(int accountId, Models.Types.ScraperStatus status)
{
using (var db = new ScraperLinkedInDBEntities())
{
var settings = await db.Settings.Where(x => x.AccountId == accountId).FirstOrDefaultAsync();
settings.ScraperStatusID = (int)status;
await db.SaveChangesAsync();
}
}
public async Task <IEnumerable <DebugLog> > GetDebugLogsAsync(int accountId, int batchSize = 50)
{
using (var db = new ScraperLinkedInDBEntities())
{
return(await db.DebugLogs.Where(x => x.AccountId == accountId)
.OrderByDescending(x => x.Id)
.Take(batchSize)
.OrderBy(x => x.Id)
.ToListAsync());
}
}
public async Task InsertDebugLogsAsync(IEnumerable <DebugLog> debugLogs)
{
using (var db = new ScraperLinkedInDBEntities())
{
foreach (var debugLog in debugLogs)
{
db.DebugLogs.Add(debugLog);
}
await db.SaveChangesAsync();
}
}
public async Task UpdateProfilesExecutionStatusByCompanyIdAsync(int accountId, int companyId, Models.Types.ExecutionStatus executionStatus)
{
using (var db = new ScraperLinkedInDBEntities())
{
var profiles = db.Profiles.Where(x => x.AccountID == accountId && x.CompanyID == companyId);
foreach (var profile in profiles)
{
profile.ExecutionStatusID = (int)executionStatus;
}
await db.SaveChangesAsync();
}
}
public async Task <IEnumerable <Company> > GetCompaniesForSearchAsync(int accountId, int companyBatchSize)
{
using (var db = new ScraperLinkedInDBEntities())
{
var companiesDb = db.Companies.Where(x => x.AccountId == accountId && !string.IsNullOrEmpty(x.LinkedInURL.Trim()) && (x.ExecutionStatusID == (int)Models.Types.ExecutionStatus.Created || x.ExecutionStatusID == (int)Models.Types.ExecutionStatus.Queued))
.Take(companyBatchSize);
Enumerable.ToList(companiesDb).ForEach(x => x.ExecutionStatusID = (int)Models.Types.ExecutionStatus.Queued);
await db.SaveChangesAsync();
return(await companiesDb.ToListAsync());
}
}
public async Task UpdateCompanyAsync(Company company)
{
using (var db = new ScraperLinkedInDBEntities())
{
var companyDB = await db.Companies.Where(x => x.Id == company.Id).FirstOrDefaultAsync();
companyDB.LogoUrl = company.LogoUrl ?? "";
companyDB.Specialties = company.Specialties ?? "";
companyDB.ExecutionStatusID = company.ExecutionStatusID;
await db.SaveChangesAsync();
}
}
public async Task <Payment> UpdatePaymentGuideAsync(int id, int accountId, Guid guid)
{
using (var db = new ScraperLinkedInDBEntities())
{
var paymentDb = await db.Payments.Where(x => x.Id == id && x.AccountId == accountId).FirstOrDefaultAsync();
paymentDb.Guide = guid;
paymentDb.UpdateOn = DateTime.UtcNow;
await db.SaveChangesAsync();
return(paymentDb);
}
}
public async Task UpdateLastPageCompanyAsync(int accountId, int companyId, int lastScrapedPage)
{
using (var db = new ScraperLinkedInDBEntities())
{
var companyDB = await db.Companies.Where(x => x.AccountId == accountId && x.Id == companyId).FirstOrDefaultAsync();
if (companyDB != null)
{
companyDB.LastScrapedPage = lastScrapedPage;
await db.SaveChangesAsync();
}
}
}
public async Task <Payment> InsertPaymentAsync(Payment payment)
{
using (var db = new ScraperLinkedInDBEntities())
{
payment.Guide = Guid.NewGuid();
payment.CreateOn = DateTime.UtcNow;
payment.PaymentOn = DateTime.UtcNow;
payment.UpdateOn = DateTime.UtcNow;
var paymentDb = db.Payments.Add(payment);
await db.SaveChangesAsync();
return(paymentDb);
}
}
public async Task UpdateSettingAsync(Setting setting)
{
using (var db = new ScraperLinkedInDBEntities())
{
var settingDB = await db.Settings.Where(x => x.AccountId == setting.AccountId).FirstOrDefaultAsync();
settingDB.Token = setting.Token;
settingDB.Login = setting.Login;
settingDB.Password = setting.Password;
settingDB.TechnologiesSearch = setting.TechnologiesSearch;
settingDB.RolesSearch = setting.RolesSearch;
await db.SaveChangesAsync();
}
}
