public void Create(string name, string phone)
{
using (var db = new CRMContext())
{
db.Leads.Add(new Lead
{
Name = name,
Phone = phone
});
db.SaveChanges();
}
}
public static int DeleteMeetings(int Id)
{
using (var Context = new CRMContext())
{
return(Context.Database.ExecuteSqlCommand(
"exec dbo.[usp_Sales_Meetings_Delete] @Id",
new Object[]
{
new SqlParameter("Id", Id)
}
));
}
}
public ICollection <BulundugumAyinGelirGiderMiktarlariDTO> BulundugumAyinGelirGiderMiktarlari()
{
using (CRMContext _db = new CRMContext())
{
var gelirToplami = _db.FinansTakip.Where(x => x.AktifMi == true).Where(x => x.OdemeDurumu == true).Where(x => x.GelirMiGiderMi == true).Where(x => x.OdemeTarihi.Value.Month == DateTime.Now.Month).Sum(x => x.Fiyat);
var giderToplami = _db.FinansTakip.Where(x => x.AktifMi == true).Where(x => x.OdemeDurumu == true).Where(x => x.GelirMiGiderMi == false).Where(x => x.OdemeTarihi.Value.Month == DateTime.Now.Month).Sum(x => x.Fiyat);
return(_db.FinansTakip.Select(x => new BulundugumAyinGelirGiderMiktarlariDTO
{
GelirMiktari = gelirToplami,
GiderMiktari = giderToplami
}).ToList());
}
}
public static string GetQuoteByStageId(int Id)
{
using (var Context = new CRMContext())
{
return(Context.Database.SqlQuery <string>(
"exec dbo.[usp_Sales_GetQuoteJsonByStageId] @Id",
new Object[]
{
new SqlParameter("Id", Id)
}
).FirstOrDefault());
}
}
public static List <GetQuoteById_PrintDbModel> GetQuoteByIdPrint(int QuoteId)
{
using (var Context = new CRMContext())
{
return(Context.Database.SqlQuery <GetQuoteById_PrintDbModel>(
"exec dbo.[usp_Sales_Quote_GetById_Print] @QuoteId",
new Object[]
{
new SqlParameter("QuoteId", QuoteId)
}
).ToList());
}
}
public ICollection <YillikUrunSatisDTO> YillikUrunSatisGrafigi()
{
using (CRMContext _db = new CRMContext())
{
return((from finansTakip in _db.FinansTakip.Where(x => x.UrunID != null && x.GelirMiGiderMi == true && x.OdemeTarihi.Value.Year == DateTime.Now.Year)
group finansTakip by finansTakip.OdemeTarihi.Value.Month into x
select new YillikUrunSatisDTO
{
Ay = x.Key,
SatisMiktari = x.Sum(a => a.UrunAdet).Value
}).ToList());
}
}
// public ICollection<AyaGoreGelirGiderYuzdelikleriDTO> GelirYuzdeligiGetir()
// {
// using (CRMContext _db = new CRMContext())
// {
// var gelirYuzde = _db.FinansTakip.Where(x => x.GelirMiGiderMi =)
// }
// }
public ICollection <GiderYillikChartDTO> GiderYillikGrafikGetir(DateTime dateTime)
{
using (CRMContext _db = new CRMContext())
{
return((from finans in _db.FinansTakip.Where(x => x.GelirMiGiderMi == false && x.OdemeDurumu == true && x.AktifMi == true && x.OdemeTarihi.Value.Year == dateTime.Year)
group finans by finans.OdemeTarihi.Value.Month into g
select new GiderYillikChartDTO
{
Ay = g.Key,
ToplamTutar = g.Sum(x => x.Fiyat)
}).ToList());
}
}
public static MeetingsDbModel GetMeetingById(int Id)
{
using (var Context = new CRMContext())
{
return(Context.Database.SqlQuery <MeetingsDbModel>(
"exec dbo.[usp_Sales_Meetings_GetById] @Id",
new Object[]
{
new SqlParameter("Id", Id)
}
).FirstOrDefault());
}
}
public void Test1()
{
string connStringTemplate = "Server=(localdb)\\mssqllocaldb;Database={tenant};Trusted_Connection=True;MultipleActiveResultSets=true";
// setup TenantA first;
string tenantName = "TenantA";
var dbContextOptionsBuilder = new DbContextOptionsBuilder();
dbContextOptionsBuilder.UseSqlServer(connStringTemplate.Replace("{tenant}", tenantName));
CRMContext contextA = new CRMContext(dbContextOptionsBuilder.Options);
//for (int i = 0; i < 5; i++)
//{
// contextA.Customers.Remove(contextA.Customers.FirstOrDefault());
//}
//contextA.SaveChanges();
for (int i = 0; i < 5; i++)
{
contextA.Customers.Add(new DataAcess.DomainModel.Customer()
{
CustomerId = Guid.NewGuid(), Name = $"TenA:XYZ Inc {i + 1}", RegistrationDate = DateTime.Today, Website = $"http://www.{$"XYZ Inc {i + 1}"}.com"
});
}
contextA.SaveChanges();
// setup TenantB next;
tenantName = "TenantB";
dbContextOptionsBuilder = new DbContextOptionsBuilder();
dbContextOptionsBuilder.UseSqlServer(connStringTemplate.Replace("{tenant}", tenantName));
CRMContext contextB = new CRMContext(dbContextOptionsBuilder.Options);
//for (int i = 0; i < 10; i++)
//{
// contextB.Customers.Remove(contextB.Customers.FirstOrDefault());
//}
//contextB.SaveChanges();
for (int i = 0; i < 10; i++)
{
contextB.Customers.Add(new DataAcess.DomainModel.Customer()
{
CustomerId = Guid.NewGuid(), Name = $"TenB: Super Awesome Co {i + 1}", RegistrationDate = DateTime.Today, Website = $"http://www.{$"Super Awesome Co {i + 1}"}.com"
});
}
contextB.SaveChanges();
}
public static List <GetSocialMediaDbModel> GetAllSocialMedia(GetSocialMediaParamDbModel Param)
{
using (var Context = new CRMContext())
{
return(Context.Database.SqlQuery <GetSocialMediaDbModel>(
"exec dbo.[usp_Sales_SocialMedia_GetAll] @LinkTypeId,@LinkId",
new Object[]
{
new SqlParameter("LinkTypeId", Param.LinkTypeId),
new SqlParameter("LinkId", Param.LinkId)
}
).ToList());
}
}
public int DeleteItem(FinansTakip item)
{
using (CRMContext _db = new CRMContext())
{
var entry = _db.Entry(item);
if (entry.State == System.Data.Entity.EntityState.Detached)
{
_db.FinansTakip.Attach(item);
}
_db.FinansTakip.Remove(item);
_etkilenenSatir = _db.SaveChanges();
return(_etkilenenSatir);
}
}
public ICollection <YillikUrunKazancDTO> UrunKazancGrafigi()
{
using (CRMContext _db = new CRMContext())
{
return((from finansTakip in _db.FinansTakip.Where(x => x.GelirMiGiderMi == true && x.OdemeTarihi.Value.Year == DateTime.Now.Year && x.UrunID != null)
group finansTakip by finansTakip.OdemeTarihi.Value.Month into x
select new YillikUrunKazancDTO
{
Ay = x.Key,
Fiyat = x.Sum(y => y.Fiyat * y.UrunAdet)
}).ToList());
}
}
public int DeleteItem(Uye item)
{
using (var _db = new CRMContext())
{
var entry = _db.Entry(item);
if (entry.State == EntityState.Detached)
{
_db.Uye.Attach(item);
}
_db.Uye.Remove(item);
_etkilenenSatir = _db.SaveChanges();
return(_etkilenenSatir); //not Detached hatasi alindi o yuzden burasi degisik. Baska entitylerde alinirsa bu kod kullanilir
}
}
public static List <GetAssignmentDbModel> GetAllAssignments(GetAssignmentParamDbModel Param)
{
using (var Context = new CRMContext())
{
return(Context.Database.SqlQuery <GetAssignmentDbModel>(
"exec dbo.[usp_Sales_Assignment_GetByActivityId] @ActivityId,@ActivityTypeId",
new Object[]
{
new SqlParameter("ActivityId", Param.ActivityId),
new SqlParameter("ActivityTypeId", Param.ActivityTypeId)
}
).ToList());
}
}
public CRMContext Create()
{
CRMContext context = null;
if (!string.IsNullOrWhiteSpace(this.TenantName))
{
var dbContextOptionsBuilder = new DbContextOptionsBuilder();
dbContextOptionsBuilder.UseSqlServer(this.connectionStringTemplate.Replace("{tenant}", this.TenantName));
context = new CRMContext(dbContextOptionsBuilder.Options);
}
return(context);
}
public HttpResponseMessage ResetDatabaseToStock()
{
var context = new CRMContext();
context.ResetDatabaseToStock(); // stored proc from SQL server.
string path = System.Web.HttpContext.Current?.Request.MapPath("~//reset//index.html");
var response = new HttpResponseMessage {
Content = new StringContent(File.ReadAllText(path))
};
response.Content.Headers.ContentType = new MediaTypeHeaderValue("text/html");
response.StatusCode = HttpStatusCode.OK;
return(response);
}
public static List <GetQuoteDbModel> GetAllQuotes(GetQuoteParamDbModel Param)
{
using (var Context = new CRMContext())
{
return(Context.Database.SqlQuery <GetQuoteDbModel>(
"exec dbo.[usp_Sales_Quote_GetAll] @QuoteNumber,@AssignEngineer",
new Object[]
{
new SqlParameter("QuoteNumber", DBNull.Value),
new SqlParameter("AssignEngineer", (Param.AssignEngineer == 0)?(object)DBNull.Value:Param.AssignEngineer)
}
).ToList());
}
}
public async Task <Address> Get(Expression <Func <Address, bool> > predicate, string includeProperties = null)
{
using (_context = CRMContextFactory.getContext())
{
if (includeProperties != null)
{
return(await _context.Addresses.Include(includeProperties).FirstOrDefaultAsync(predicate));
}
else
{
return(await _context.Addresses.FirstOrDefaultAsync(predicate));
}
}
}
public async Task <IList <Address> > GetList(Expression <Func <Address, bool> > predicate, string includeProperties = null)
{
using (_context = CRMContextFactory.getContext())
{
if (includeProperties != null)
{
return(await _context.Addresses.Include(includeProperties).Where(predicate).ToListAsync());
}
else
{
return(await _context.Addresses.Where(predicate).ToListAsync());
}
}
}
public int DeleteItem(Calisan item)
{
using (CRMContext _db = new CRMContext())
{
var entry = _db.Entry(item);
if (entry.State == EntityState.Detached)
{
_db.Calisan.Attach(item);
}
_db.Calisan.Remove(item);
_etkilenenSatir = _db.SaveChanges();
return(_etkilenenSatir);
}
}
public int UpdateUyeItem(Iletisim item)
{
using (CRMContext _db = new CRMContext())
{
Iletisim eskiIletisim = _db.Iletisim.Where(x => x.UyeID == item.UyeID).FirstOrDefault();
eskiIletisim.Adres = item.Adres;
eskiIletisim.EMail = item.EMail;
eskiIletisim.Telefon = item.Telefon;
eskiIletisim.GuncellenmeTarihi = DateTime.Now;
eskiIletisim.AktifMi = true;
_etkilenenSatir = _db.SaveChanges();
return(_etkilenenSatir);
}
}
public PartialViewResult PartialFinishVisit(int ActId)
{
var model = ActivityRep.Get(ActId);
ViewBag.ProductId = new SelectList(ProductRep.FindBy(i => i.prod_group_id == 1), "id", "name");
var context = new CRMContext();
var PromoList = context.GetRemainsByPeriod(DateTime.Now.Month, DateTime.Now.Year).AsQueryable().Where(i => i.cuser_id == MyId && i.q_end > 0).
Select(i => new Report_()
{
id = i.promo_id, name = i.promo_name + " - " + i.q_end + " шт."
});
ViewBag.PromoList = new SelectList(PromoList, "id", "name");
return(PartialView(model));
}
public static LeadsDbModel GetLeadById(int Id, int SourceTypeId, int ChildTypeId)
{
using (var Context = new CRMContext())
{
return(Context.Database.SqlQuery <LeadsDbModel>(
"exec dbo.[usp_Sales_Leads_GetById] @Id,@SourceActivityTypeId,@ChildActivityTypeId",
new Object[]
{
new SqlParameter("Id", Id),
new SqlParameter("SourceActivityTypeId", SourceTypeId),
new SqlParameter("ChildActivityTypeId", ChildTypeId)
}
).FirstOrDefault());
}
}
public static int SaveMeetingLinks(MeetingsDbModel Model)
{
using (var Context = new CRMContext())
{
return(Context.Database.ExecuteSqlCommand(
"exec dbo.[usp_Sales_MeetingLinks_Save] @Id,@MeetingId,@LinkId,@LinkType",
new Object[]
{
new SqlParameter("Id", Model.MeetingLinkId),
new SqlParameter("MeetingId", Model.Id),
new SqlParameter("LinkId", Model.LinkId),
new SqlParameter("LinkType", (int)Model.LinkType)
}
));
}
}
public static List <GetCampaignDbModel> GetAllCampaigns(GetCampaignParamDbModel Param)
{
using (var Context = new CRMContext())
{
return(Context.Database.SqlQuery <GetCampaignDbModel>(
"exec dbo.[usp_Sales_Campaign_GetAll] @Name,@StartDate,@EndDate,@AssignEngineer",
new Object[]
{
new SqlParameter("Name", DBNull.Value),
new SqlParameter("StartDate", DBNull.Value),
new SqlParameter("EndDate", DBNull.Value),
new SqlParameter("AssignEngineer", (Param.AssignEngineer == 0)?(object)DBNull.Value:Param.AssignEngineer)
}
).ToList());
}
}
public static List <GetContactsDbModel> GetAllContacts(GetContactsParamDbModel Param)
{
using (var Context = new CRMContext())
{
return(Context.Database.SqlQuery <GetContactsDbModel>(
"exec dbo.[usp_Sales_Contacts_GetAll] @Name,@AccountId,@Mobile,@AssignEngineer",
new Object[]
{
new SqlParameter("Name", DBNull.Value),
new SqlParameter("AccountId", DBNull.Value),
new SqlParameter("Mobile", DBNull.Value),
new SqlParameter("AssignEngineer", (Param.AssignEngineer == 0)?(object)DBNull.Value:Param.AssignEngineer)
}
).ToList());
}
}
public ICollection <UyeDonemDagilimiDTO> uyeDonemDagilimi()
{
using (CRMContext _db = new CRMContext())
{
return((from uye in _db.Uye
group uye by uye.OlusturulmaTarihi.Value.Year into g // <-- group by category
select new UyeDonemDagilimiDTO
{
Yil = g.Key,
ToplamSayi = g.Count()
}).ToList());
// return UyelerDagilimi;
}
}
public static List <GetAccountsDbModel> GetAllAccounts(GetAccountsParamDbModel Param)
{
using (var Context = new CRMContext())
{
return(Context.Database.SqlQuery <GetAccountsDbModel>(
"exec dbo.[usp_Sales_Accounts_GetAll] @Name,@OfficePhone,@SourceActivityTypeId,@ChildActivityTypeId,@AssignEngineer",
new Object[]
{
new SqlParameter("Name", DBNull.Value),
new SqlParameter("OfficePhone", DBNull.Value),
new SqlParameter("SourceActivityTypeId", Param.SourceActivityTypeId),
new SqlParameter("ChildActivityTypeId", Param.ChildActivityTypeId),
new SqlParameter("AssignEngineer", (Param.AssignEngineer == 0)?(object)DBNull.Value:Param.AssignEngineer)
}
).ToList());
}
}
public async Task <Ticket> Update(Ticket t, Staff s)
{
using (_context = CRMContextFactory.getContext())
{
_context.Update(t);
var history = new TicketHistory();
history.idStaffAssigned = t.idStaffAssignedTo;
history.idTicket = t.idTicket;
history.idTicketStatus = t.idTicketStatus;
history.dtChanged = DateTime.Now;
history.idStaffAssigns = s.idStaff;
_context.TicketHistories.Add(history);
await _context.SaveChangesAsync();
return(t);
}
}
public int UpdateItem(Calisan item)
{
using (CRMContext _db = new CRMContext())
{
Calisan eskiCalisan = _db.Calisan.Where(x => x.ID == item.ID).FirstOrDefault();
eskiCalisan.Ad = item.Ad;
eskiCalisan.Soyad = item.Soyad;
eskiCalisan.TCKimlikNo = item.TCKimlikNo;
eskiCalisan.Maas = item.Maas;
eskiCalisan.Cinsiyet = item.Cinsiyet;
eskiCalisan.DogumTarihi = item.DogumTarihi;
eskiCalisan.KullaniciMi = item.KullaniciMi;
eskiCalisan.GuncellenmeTarihi = DateTime.Now;
_etkilenenSatir = _db.SaveChanges();
return(_etkilenenSatir);
}
}
private static void InitializeDatabase()
{
Database.SetInitializer<CRMContext>(new CRMContextInitializer());
var context = new CRMContext();
context.Database.Initialize(true);
}
