public ICollection <Order> GetAllByTAOver(int totalAmount)
{
CRMEntities db = new CRMEntities();
ICollection <Order> toBeSelected = db.Orders.Where(o => o.TotalAmount > totalAmount).ToList();
return(toBeSelected);
}
public HttpResponseMessage getTaskList(DTOFilterGetTasksRequest request)
{
using (var db = new CRMEntities())
{
var filter = request.getFilter();
filter.subTables["taskid"].fieldFilters.Add(new DTOFieldFilter {
fieldName = "deleted", value = 0, op = 2
});
var querySql = filter.subTables["taskid"].getPagingSQL(request.pageNo, request.rowsPerPage);
var res = db.task.SqlQuery(querySql).ToList();
//var ptypeids = res.Select(r => r.attachablepersoneltype).Distinct().ToList();
//var personels = db.objecttypes.Where(p => ptypeids.Contains(p.typeid)).ToList();
var obtpyeids = res.Select(r => r.attachableobjecttype).Distinct().ToList();
var objects = db.objecttypes.Where(o => obtpyeids.Contains(o.typeid)).ToList();
var ttypeids = res.Select(s => s.tasktype).Distinct().ToList();
var tasktypess = db.tasktypes.Where(t => ttypeids.Contains(t.TaskTypeId)).ToList();
res.ForEach(r =>
{
r.objecttypes = objects.Where(t => t.typeid == r.attachableobjecttype).FirstOrDefault();
r.tasktypes = tasktypess.Where(t => t.TaskTypeId == r.tasktype).FirstOrDefault();
});
return(Request.CreateResponse(HttpStatusCode.OK, res.Select(s => s.toDTO()).ToList(), "application/json"));
}
}
private void UserControl_Loaded(object sender, RoutedEventArgs e)
{
CRMEntities db = new CRMEntities();
var customers = db.Customers.Include(c => c.Orders).ToList();
foreach (var customer in customers)
{
var item = new TreeViewItem();
var subItem = new TreeViewItem();
item.Header = $"Clientul cu id-ul: {customer.Id}";
item.Items.Add($"Prenume: {customer.FirstName}");
item.Items.Add($"Nume: {customer.LastName}");
item.Items.Add($"Oras: {customer.City}");
item.Items.Add($"Tara: {customer.Country}");
item.Items.Add($"Numar de telefon: {customer.Phone}");
subItem.Header = $"Numar comenzi {customer.Orders.Count}";
foreach (var order in customer.Orders)
{
var subI = new TreeViewItem();
subI.Header = $"Comanda cu id-ul: {order.Id}";
subI.Items.Add($"Data comenzii: {order.OrderDate}");
subI.Items.Add($"Numar produse: {order.OrderItems.Count}");
subI.Items.Add($"Suma totala: {order.TotalAmount}");
subItem.Items.Add(subI);
}
item.Items.Add(subItem);
CustomersView.Items.Add(item);
}
}
public void Add(Supplier supplier)
{
CRMEntities db = new CRMEntities();
db.Suppliers.Add(supplier);
db.SaveChanges();
}
static void Main(string[] args)
{
CRMEntities db = new CRMEntities();
db.Database.Log += Console.WriteLine;
displaySuppAndQuantityOfProducts2(db, 1, 10);
int lastPage = GetLastPage(db.Suppliers, 10);
displaySuppAndQuantityOfProducts2(db, lastPage, 10);
/*1. CRM trebuie sa faca un site in care sa includa o pagina in care administratorul site - ului
* sa vada lista de furnizori si numarul de produse pe care acestia le detin. Creati un query
* folosind lambda expression care sa afiseze doar 10 elemente pe pagina.*/
displayListOfSuppInPage(db, 1, 10);
/*2. Plecand de la punctul 1 afisati pagina 3*/
displayListOfSuppInPage(db, 3, 10);
/*3. Selectati toate comenzile si afisati la fiecare comanda cate produse s - au vandut.
* Sa se afiseze cate 20 se elemente pe pagina pentru paginele 2 si 4.*/
displayOrdersAndItemsOnPage(db, 2, 20);
displayOrdersAndItemsOnPage(db, 4, 20);
/*4. Sa selectati toti furnizorii si sa afisati si cate produse vinde fiecare.
* Sa se afiseze cate 10 elemente pe pagina pentru prima si ultima Pagina*/
displaySuppAndQuantityOfProducts(db, 1, 10);
displaySuppAndQuantityOfProducts(db, 0, 10);
Console.ReadKey();
}
public ICollection <Order> GetAllOrdersById(int customerId)
{
CRMEntities db = new CRMEntities();
ICollection <Order> orders = db.Orders.Where(o => o.CustomerId == customerId).ToList();
return(orders);
}
public ICollection <Product> GetTopXMostExpensive(int x)
{
CRMEntities db = new CRMEntities();
ICollection <Product> products = db.Products.OrderByDescending(p => p.UnitPrice).Take(x).ToList(); //Icollection nu merge. De ce?
return(products);
}
//Filling task info on dashboard, maximum 9 tasks
public void fillTasks()
{
CRMEntities dbss = new CRMEntities();
//Hiding empty UI first
exp_1.Visibility = Visibility.Hidden;
exp_2.Visibility = Visibility.Hidden;
exp_3.Visibility = Visibility.Hidden;
exp_4.Visibility = Visibility.Hidden;
exp_5.Visibility = Visibility.Hidden;
exp_6.Visibility = Visibility.Hidden;
exp_7.Visibility = Visibility.Hidden;
exp_8.Visibility = Visibility.Hidden;
exp_9.Visibility = Visibility.Hidden;
int count = 0;
//dynamicly filling all hiddden UI controls
foreach (CRMv2.Models.Task t in dbss.Tasks.ToList())
{
if (t.User.UserId == LoggedUserId && t.isFinised == false)
{
count++;
Expander expander = ((Expander)FindName("exp_" + count));
expander.Visibility = Visibility.Visible;
expander.IsExpanded = true;
expander.Header = "Müştəri : " + t.Customer.CustomerName;
TextBox txt = ((TextBox)FindName("txtExp_" + count));
txt.Text = t.Description;
Label lbl1 = ((Label)FindName("lblExp_1" + count));
Label lbl2 = ((Label)FindName("lblExp_2" + count));
lbl1.Content = "Yaranma tarixi : " + t.CreationTime.ToShortDateString();
lbl2.Content = "Bitmə tarixi : " + t.DeadlineTime.ToShortDateString();
}
}
}
public ICollection <Supplier> GetAllByName(string companyName)
{
CRMEntities db = new CRMEntities();
ICollection <Supplier> suppliers = db.Suppliers.Where(s => s.CompanyName == companyName).ToList();
return(suppliers);
}
public ActionResult Login(UserModel objUser)
{
objUser.Message = "";
if (ModelState.IsValid)
{
using (CRMEntities db = new CRMEntities())
{
var obj = db.Tbl_User.Where(a => a.UserName.Equals(objUser.UserName)).FirstOrDefault();
if (obj != null)
{
if (CompareStrings(obj.Password, objUser.Password))
{
Session["UserID"] = obj.User_ID.ToString();
Session["Username"] = obj.UserName.ToString();
return(RedirectToAction("index"));
}
else
{
objUser.Message = ".نام کاربری یا رمز عبور صحیح نمیباشد";
}
}
else
{
objUser.Message = ".نام کاربری یا رمز عبور صحیح نمیباشد";
}
}
}
return(View(objUser));
}
public Supplier GetByName(string companyName)
{
CRMEntities db = new CRMEntities();
Supplier supp = db.Suppliers.FirstOrDefault(s => s.CompanyName == companyName);
return(supp);
}
public Supplier GetById(int id)
{
CRMEntities db = new CRMEntities();
Supplier supplier = db.Suppliers.Find(id);
return(supplier);
}
public ICollection <Product> GetProductsBySupplierId(int supplierId)
{
CRMEntities db = new CRMEntities();
ICollection <Product> products = db.Products.Where(p => p.SupplierId == supplierId).ToList();
return(products);
}
public ICollection <Supplier> GetAllByCityAndCountry(string city, string country)
{
CRMEntities db = new CRMEntities();
ICollection <Supplier> suppliers = db.Suppliers.Where(s => s.City == city && s.Country == country).ToList();
return(suppliers);
}
private void UserControl_Loaded(object sender, RoutedEventArgs e)
{
CRMEntities db = new CRMEntities();
var suppliers = db.Suppliers.Include(s => s.Products).ToList();
foreach (var supplier in suppliers)
{
var item = new TreeViewItem();
var subItem = new TreeViewItem();
item.Header = $"Supplier-ul cu id-ul: {supplier.Id}";
item.Items.Add($"Numele companiei {supplier.CompanyName}");
item.Items.Add($"Nume de contact: {supplier.ContactName}");
item.Items.Add($"Titlu de contact: {supplier.ContactTitle}");
item.Items.Add($"Orasul: {supplier.City}");
item.Items.Add($"Tara: {supplier.Country}");
item.Items.Add($"Numarul de telefon: {supplier.Phone}");
item.Items.Add($"Fax: {supplier.Fax}");
subItem.Header = $"Numar produse: {supplier.Products.Count}";
foreach (var product in supplier.Products)
{
var subI = new TreeViewItem();
subI.Header = $"Id-ul produsului: {product.Id}";
subI.Items.Add($"Numele produsului: {product.ProductName}");
subI.Items.Add($"Pretul: {product.UnitPrice}");
subI.Items.Add($"Este in stock: {(product.IsDiscontinued == false ? "Nu" : "Da")}");
subItem.Items.Add(subI);
}
item.Items.Add(subItem);
SuppliersView.Items.Add(item);
}
}
public ICollection <Order> GetAllByAmount(int totalAmount)
{
CRMEntities db = new CRMEntities();
ICollection <Order> orders = db.Orders.Where(o => o.TotalAmount >= totalAmount).ToList();
return(orders);
}
public MainWindow()
{
var login = new Login();
login.ShowDialog();
if (login.Llogin == GlobalOptions.Instance.ActiveUser.Login &&
login.Ppassword == GlobalOptions.Instance.ActiveUser.Password)
{
InitializeComponent();
}
else
{
MessageBox.Show("Zły login lub hasło!", "Uwaga");
Log(login);
Application.Current.Shutdown();
}
try
{
CrmEntities = new CRMEntities();
}
catch (Exception)
{
MessageBox.Show("Chyba nie masz internetu.", "Uwaga");
Application.Current.Shutdown();
}
}
/*4. Sa selectati toti furnizorii si sa afisati si cate produse vinde fiecare.
* Sa se afiseze cate 10 elemente pe pagina pentru prima si ultima Pagina*/
private static void displaySuppAndQuantityOfProducts2(CRMEntities dataBase, int numOfPage, int elemsInPage)
{
if (numOfPage < 0 || elemsInPage < 1)
{
return;
}
int elemsToSkip = (numOfPage - 1) * elemsInPage;
var suppliers = dataBase.Suppliers
.Include(s => s.Products)
.Include(s => s.Products.Select(p => p.OrderItems))
.OrderBy(s => s.CompanyName)
.Skip(elemsToSkip)
.Take(elemsInPage)
.Select(s => new SupplierAnswer()
{
CompanyName = s.CompanyName,
NrProduse = s.Products.Sum(p => p.OrderItems.Sum(oi => oi.Quantity))
})
.ToList();
foreach (var supplier in suppliers)
{
Console.WriteLine($"Numele companiei: {supplier.CompanyName} \nProduse vandute: {supplier.NrProduse} \n{delimitator}");
}
}
public static int RecordatoriosCount()
{
CRMEntities db = new CRMEntities();
var recordatorio = db.Recordatorio.ToList();
return(recordatorio.FindAll(r => r.Fecha.Date == DateTime.Now.Date).Count);
}
public ICollection <Customer> GetAllByCity(string city)
{
CRMEntities db = new CRMEntities();
ICollection <Customer> customers = db.Customers.Where(c => c.City == city).ToList();
return(customers);
}
public HttpResponseMessage insertTask(DTOtask request)
{
var user = KOCAuthorizeAttribute.getCurrentUser();
using (var db = new CRMEntities())
{
var errormessage = new DTOResponseError();
var t = new task
{
taskname = request.taskname,
attachableobjecttype = request.objecttypes.typeid,
attachablepersoneltype = request.personeltypes.typeid,
performancescore = request.performancescore,
tasktype = request.tasktypes.TaskTypeId,
deleted = false,
creationdate = DateTime.Now,
lastupdated = DateTime.Now,
updatedby = user.userId,
description = request.description,
};
db.task.Add(t);
db.SaveChanges();
errormessage.errorCode = 1;
errormessage.errorMessage = "İşlem Başarılı";
return(Request.CreateResponse(HttpStatusCode.OK, errormessage, "application/json"));
}
}
public HttpResponseMessage getBlocks(DTOGetBlockFilter request)
{
using (var db = new CRMEntities())
{
db.Configuration.AutoDetectChangesEnabled = false;
db.Configuration.LazyLoadingEnabled = false;
db.Configuration.ProxyCreationEnabled = false;
var filter = request.getFilter();
string querySQL = filter.getPagingSQL(request.pageNo, request.rowsPerPage);
var countSQL = filter.getCountSQL();
var rowCount = db.Database.SqlQuery <int>(countSQL).First();
var res = db.block.SqlQuery(querySQL).ToList();
var siteids = res.Select(s => s.siteid).Distinct().ToList();
var sites = db.site.Where(s => siteids.Contains(s.siteid)).ToList();
var personelids = res.Select(s => s.salesrepresentative).Distinct().ToList();
var personels = db.personel.Where(p => personelids.Contains(p.personelid)).ToList();
res.ForEach(r => {
r.site = sites.Where(s => s.siteid == r.siteid).FirstOrDefault();
r.salespersonel = personels.Where(p => p.personelid == r.salesrepresentative).FirstOrDefault();
});
DTOResponsePagingInfo paginginfo = new DTOResponsePagingInfo
{
pageCount = (int)Math.Ceiling(rowCount * 1.0 / request.rowsPerPage),
pageNo = request.pageNo,
rowsPerPage = request.rowsPerPage,
totalRowCount = rowCount
};
return(Request.CreateResponse(HttpStatusCode.OK, new DTOPagedResponse(DTOResponseError.NoError(), res.Select(s => s.toDTO()).ToList(), paginginfo, querySQL), "application/json"));
}
}
// GET: Customer
public ActionResult Index(int page = 1)
{
string lang = this.RouteData.Values["language"].ToString() != "" ? this.RouteData.Values["language"].ToString() : "en";
CustomerIndexViewModel model = new CustomerIndexViewModel();
CRMEntities db = new CRMEntities();
int TotalCustomers = db.Customers.Count();
model.PageInfo.MaxPage = model.PageInfo.GetMaxPage(TotalCustomers);
if (model.PageInfo.MaxPage < page || page < 0)
{
return(HttpNotFound());
}
model.PageInfo.CurentPage = page;
model.CustomerList = db.Customers.Select(
c => new CustomerViewModel
{
Id = c.Id,
FullName = c.FirstName + c.LastName,
Country = c.Country,
Phone = c.Phone
})
.OrderBy(c => c.FullName)
.Skip(model.PageInfo.ItemsPerPage * page--)
.Take(model.PageInfo.ItemsPerPage)
.ToList();
model.Translations.GetTranslations(db, this.RouteData);
return(View(model));
}
public Supplier GetByNameAndContact(string companyName, string contact)
{
CRMEntities db = new CRMEntities();
Supplier supplier = db.Suppliers.FirstOrDefault(s => s.CompanyName == companyName && s.ContactName == contact);
return(supplier);
}
// GET: Currency
public ActionResult _CursValutar()
{
var db = new CRMEntities();
var model = db.Currencies.ToList();
return(PartialView(model));
}
public HttpResponseMessage login(DTOpersonel request)
{
using (var db = new CRMEntities())
{
return(Request.CreateResponse(HttpStatusCode.OK, "", "application/json"));
}
}
public ActionResult _NumarTotalClienti()
{
CRMEntities db = new CRMEntities();
var model = db.Customers.Count();
return(PartialView(model));
}
private static void EagerLazy()
{
CRMEntities db = new CRMEntities();
db.Database.Log += Console.WriteLine;
IQueryable <Customer> cs = db.Customers;
//.....
cs = cs.Where(c => c.City == "London");
//ICollection<Customer> customers = db.Customers.ToList();
Customer customer = cs.FirstOrDefault();
cs = cs.Include(c => c.Orders);
IOrderedQueryable <Customer> customersLonSort = cs.OrderBy(c => c.LastName);
//....
ICollection <Customer> customersLon = customersLonSort.ToList();
bool exist = customersLon.Contains(customer);
int ntCustomers = customersLonSort.Count();
foreach (var cc in customersLon)
{
Console.WriteLine($"{cc.LastName} + {cc.FirstName}: {cc.Orders.Count} comenzi");
}
}
public HttpResponseMessage insertPersonel(DTOpersonel request)
{
var user = KOCAuthorizeAttribute.getCurrentUser();
using (var db = new CRMEntities())
{
var errormessage = new DTOResponseError {
errorCode = 1, errorMessage = "İşlem Başarılı"
};
var p = new personel
{
personelname = request.personelname,
category = (int)request.category,
mobile = request.mobile,
email = request.email,
password = request.password,
notes = request.notes,
roles = (int)request.category,
creationdate = DateTime.Now,
lastupdated = DateTime.Now,
deleted = false,
updatedby = user.userId
};
db.personel.Add(p);
db.SaveChanges();
return(Request.CreateResponse(HttpStatusCode.OK, errormessage, "application/json"));
}
}
public HttpResponseMessage getPersonels(DTOFilterGetPersonelRequest request)
{
using (var db = new CRMEntities())
{
var filter = request.getFilter();
filter.fieldFilters.Add(new DTOFieldFilter {
fieldName = "deleted", value = 0, op = 2
});
var countSql = filter.getCountSQL();
var rowCount = db.Database.SqlQuery <int>(countSql).First();
var querySql = filter.getPagingSQL(request.pageNo, request.rowsPerPage);
var res = db.personel.SqlQuery(querySql).ToList();
DTOResponsePagingInfo paginginfo = new DTOResponsePagingInfo
{
pageCount = (int)Math.Ceiling(rowCount * 1.0 / request.rowsPerPage),
pageNo = request.pageNo,
rowsPerPage = request.rowsPerPage,
totalRowCount = rowCount
};
return(Request.CreateResponse(HttpStatusCode.OK,
new DTOPagedResponse(DTOResponseError.NoError(), res.Where(r => r.deleted == false).Select(r => r.toDTO()).ToList(), paginginfo, querySql)
, "application/json"));
}
}
public CRMEntities Get()
{
return dataContext ?? (dataContext = new CRMEntities());
}
