public string SetManager()
{
ModelAW db = new ModelAW();
ModelAW db2 = new ModelAW();
ModelAW db3 = new ModelAW();
var Order = db.PurchaseOrderHeaders.Where(m => m.EmployeeID == 0);
var Customer = db.Customers;
foreach (var Ord in Order)
{
int PersonID = Ord.PersonID;
int? Territory = db2.Customers.Find(PersonID).TerritoryID;
decimal?MinSales = db2.SalesPersons
.Where(M => M.TerritoryID == Territory)
.Min(M => M.SalesQuota);
int EmployeeID = db2.SalesPersons.Where(m => m.TerritoryID == Territory && m.SalesQuota == MinSales).First().BusinessEntityID;
Ord.Status = 2;
Ord.EmployeeID = EmployeeID;
}
string massege = "назначенно менеджеров :" + Order.Count();
db.SaveChanges();
db.Dispose();
return(massege);
}
public void DeleteItem(string Email, int ProductID)
{
ModelAW db = new ModelAW();
string UserID = db.EmailAddresses
.Where(m => m.EmailAddress1 == Email)
.First().BusinessEntityID.ToString();
var Product = db.ShoppingCartItems
.Where(m => m.ShoppingCartID == UserID && m.ProductID == ProductID).FirstOrDefault();
db.ShoppingCartItems.Remove(Product);
db.SaveChangesAsync();
}
public void DeleteCart(string Email)
{
ModelAW db = new ModelAW();
string UserID = db.EmailAddresses
.Where(m => m.EmailAddress1 == Email)
.FirstOrDefault()
.BusinessEntityID.ToString();
var cart = db.ShoppingCartItems
.Where(m => m.ShoppingCartID == UserID.ToString());
db.ShoppingCartItems.RemoveRange(cart);
db.SaveChangesAsync();
}
public List <TerritoryDTO> GetTerritories()
{
ModelAW db = new ModelAW();
List <TerritoryDTO> territories = db.SalesTerritories
.Select(m => new TerritoryDTO
{
Territory = m.Name,
TerritoryID = m.TerritoryID
}).ToList();
db.Dispose();
return(territories);
}
public List <ProductDTO> GetPhoto(List <ProductDTO> Prod)
{
ModelAW db = new ModelAW();
foreach (var photo in Prod)
{
photo.ThumbNailPhoto = db.ProductProductPhotoes
.Include(m => m.ProductPhoto)
.Where(m => m.ProductID == photo.ProductID)
.FirstOrDefault().ProductPhoto.ThumbNailPhoto;
}
return(Prod.ToList());
}
public ActionResult AddToCart(kaspi.lab.service.CurrentProductDTO prod)
{
int ID;
ModelAW db = new ModelAW();
EmailAdressRep email = new EmailAdressRep();
ID = email.GetID(User.Identity.GetUserName());
kaspi.lab.CartService.CartServiceClient cart = new kaspi.lab.CartService.CartServiceClient();
kaspi.lab.CartService.CurrentProductDTO product = new kaspi.lab.CartService.CurrentProductDTO();
product.ProductID = prod.ProductID;
product.Count = prod.Count;
cart.AddToCart(product, ID);
cart.Close();
return(View(prod));
}
public void AddToCart(CurrentProductDTO prod, int UserID)
{
ModelAW db = new ModelAW();
ShoppingCartItem cart = new ShoppingCartItem()
{
ProductID = prod.ProductID,
ShoppingCartID = UserID.ToString(),
ModifiedDate = DateTime.Now,
DateCreated = DateTime.Now,
Quantity = prod.Count
};
db.ShoppingCartItems.Add(cart);
db.SaveChanges();
}
public static void GetPrice(OrderDTO order)
{
ModelAW db = new ModelAW();
try
{
decimal UnitPrice = db.ProductVendors
.Where(m => m.BusinessEntityID == order.Vendor && m.ProductID == order.ProductID)
.Select(m => m.StandardPrice)
.Single();
order.UnitPrice = UnitPrice;
}
catch (System.InvalidOperationException ex)
{
order.UnitPrice = 20;
ErrorLog.Error(ex.Message);
}
}
public List <ProductDTO> GetCart(string UserEmail)
{
ModelAW db = new ModelAW();
int UserID = db.EmailAddresses
.Where(m => m.EmailAddress1 == UserEmail)
.First().BusinessEntityID;
var prod = db.ShoppingCartItems
.Include(m => m.Product)
.Where(m => m.ShoppingCartID == UserID.ToString())
.Select(m => new ProductDTO
{
ProductID = m.ProductID,
ProductName = m.Product.Name
}).ToList();
GetPhoto(prod);
return(prod);
}
public string SendOeder()
{
ModelAW db = new ModelAW();
var Order = db.PurchaseOrderHeaders.Where(m => m.Status == 2 && m.PurchaseOrderID > 4020);
foreach (var ord in Order)
{
ord.Status = 3;
ord.ShipDate = DateTime.Now;
SendMessage(ord.PersonID, ord.PurchaseOrderID);
}
string massege = "Отправленно заказов:" + Order.Count();
db.SaveChanges();
db.Dispose();
return(massege);
}
public static void GetVendor(OrderDTO order)
{
ModelAW db = new ModelAW();
try
{
var vendor = db.ProductVendors
.Where(m => m.ProductID == order.ProductID);
if (vendor.Count() == 0)
{
order.Vendor = 1552;
}
else
{
order.Vendor = vendor.First().BusinessEntityID;
}
}
catch (Exception ex)
{
ErrorLog.Error(ex.Message);
}
}
public static void SendMessage(int personID, int OrderID)
{
ModelAW db = new ModelAW();
var email = db.EmailAddresses.Where(m => m.BusinessEntityID == personID).FirstOrDefault().EmailAddress1;
CustomerDTO customer = db.Customers.Include(m => m.SalesTerritory).Where(m => m.PersonID == personID)
.Select(m => new CustomerDTO
{
Name = m.Person.FirstName,
Email = email,
Adress = m.SalesTerritory.Name
}).First();
var Products = db.PurchaseOrderDetails.Include(m => m.Product)
.Where(m => m.PurchaseOrderID == OrderID)
.Select(m => new ProductDTO
{
ProductID = m.ProductID,
Name = m.Product.Name,
Price = m.UnitPrice,
Quantity = m.OrderQty,
TotalPrice = m.LineTotal
}).AsEnumerable().ToList();
OrderDTO order = new OrderDTO()
{
//Customer = customer,
OrderID = OrderID,
Products = Products,
TotalPrice = db.PurchaseOrderDetails.Where(m => m.PurchaseOrderID == OrderID).Sum(m => m.LineTotal),
Customer = customer
};
MessageQueue queue = new MessageQueue(@".\private$\MQtest");
queue.Send(order);
}
public void CreateCustomer(RegisterViewModel model, int id)
{
ModelAW db = new ModelAW();
try
{
EmailAddress E = new EmailAddress()
{
rowguid = Guid.NewGuid(), BusinessEntityID = id, EmailAddress1 = model.Email, ModifiedDate = DateTime.Now
};
Customer C = new Customer()
{
PersonID = id, TerritoryID = model.territoryID, rowguid = Guid.NewGuid(), ModifiedDate = DateTime.Now
};
Person P = new Person()
{
rowguid = Guid.NewGuid(),
FirstName = model.Name,
LastName = model.LastName,
BusinessEntityID = id,
ModifiedDate = DateTime.Now,
};
db.People.Add(P);
db.Customers.Add(C);
db.EmailAddresses.Add(E);
db.SaveChanges();
}
catch (DbUpdateException ex)
{
tryAgain += 1;
ErrorLog.Error(ex.Source + " 43: " + ex.Message);
if (tryAgain < 5)
{
CreateCustomer(model, id);
}
}
}
public void CreateCustomer(RegisterViewModel model)
{
ModelAW db = new ModelAW();
int id = GetNewID();
try
{
EmailAddress E = new EmailAddress()
{
rowguid = Guid.NewGuid(), BusinessEntityID = id, EmailAddress1 = model.Email, ModifiedDate = DateTime.Now
};
Customer C = new Customer()
{
PersonID = id, TerritoryID = model.territoryID, rowguid = Guid.NewGuid(), ModifiedDate = DateTime.Now
};
Person P = new Person()
{
rowguid = Guid.NewGuid(),
FirstName = model.Name,
LastName = model.LastName,
BusinessEntityID = id,
ModifiedDate = DateTime.Now,
};
db.People.Add(P);
db.Customers.Add(C);
db.EmailAddresses.Add(E);
db.SaveChanges();
}
catch (DbUpdateException ex)
{
tryAgain += 1;
ErrorLog.Error(ex.Source + " 43: " + ex.Message + " " + ex.InnerException.Message);
CreateCustomer(model, id);
}
DataLog.Info("Добавлен пользователь: " + db.EmailAddresses.Where(m => m.BusinessEntityID == id).First().EmailAddress1);
}
public int GetNewID()
{
ModelAW db = new ModelAW();
int ID = 0;
BusinessEntity b = new BusinessEntity()
{
rowguid = Guid.NewGuid(), ModifiedDate = DateTime.Now
};
try
{
db.BusinessEntities.Add(b);
//db.BusinessEntities.Reload(b);
db.SaveChanges();
ID = b.BusinessEntityID;
DataLog.Debug("Добавлена Сущьность BEntity: ID - " + ID);
}
catch (Exception ex)
{
ErrorLog.Error(ex.Source + " : " + ex.Message);
}
db.Dispose();
return(ID);
}
public void CreateOrder(string Email)
{
ModelAW db = new ModelAW();
int UserID = db.EmailAddresses
.Where(m => m.EmailAddress1 == Email)
.First().BusinessEntityID;
var orders = db.ShoppingCartItems
.Include(m => m.Product.ProductInventories)
.Where(m => m.Product.ProductInventories.Sum(c => c.Quantity) > 1 && UserID.ToString() == m.ShoppingCartID)
.Select(m => new OrderDTO
{
ProductID = m.ProductID,
Quantity = m.Quantity,
CartID = m.ShoppingCartItemID
}).ToArray();
//var orders = (from ShoppingCartItem Cart in db.ShoppingCartItems
// where Cart.ShoppingCartID == UserID.ToString()
// select new OrderDTO
// {
// ProductID = Cart.ProductID,
// Quantity = Cart.Quantity,
// CartID = Cart.ShoppingCartItemID
// }).ToArray();
foreach (var order in orders)
{
GetVendor(order);
GetPrice(order);
}
var ord = orders.GroupBy(m => m.Vendor);
foreach (var O in ord)
{
PurchaseOrderHeader newOrder = new PurchaseOrderHeader()
{
PersonID = UserID,
VendorID = O.Key,
ModifiedDate = DateTime.Now,
OrderDate = DateTime.Now,
ShipMethodID = 1,
Status = 1
};
db.PurchaseOrderHeaders.Add(newOrder);
db.SaveChanges();
db.Entry(newOrder).Reload();
foreach (var i in O)
{
int id = newOrder.PurchaseOrderID;
PurchaseOrderDetail Order = new PurchaseOrderDetail()
{
PurchaseOrderID = id,
OrderQty = (short)i.Quantity,
ProductID = i.ProductID,
DueDate = DateTime.Now,
ReceivedQty = 0,
RejectedQty = 0,
UnitPrice = i.UnitPrice,
ModifiedDate = DateTime.Now
};
db.PurchaseOrderDetails.Add(Order);
ShoppingCartItemRep cartItemRep = new ShoppingCartItemRep();
cartItemRep.Delete(i.CartID);
DataLog.Info("Добавлен Заказ: OrderID - " + id);
}
db.SaveChanges();
}
}
static void Main(string[] args)
{
ModelAW db = new ModelAW();
// string Conect = "Data Source=DESKTOP-0R2DTHM;Initial Catalog=AdventureWorks2019;Integrated Security=True;App=EntityFramework";
// string Get = "select prod.ProductID, prod.Name,sum(inv.Quantity),photo.ThumbNailPhoto " +
//"from (" +
// "SELECT " +
// "ROW_NUMBER() OVER (ORDER BY ProductID ASC) AS rownumber, " +
// "ProductID, ProductPhotoID " +
// "FROM Production.ProductProductPhoto " +
//") AS info " +
//"join Production.Product prod on prod.ProductID = info.ProductID " +
//"join Production.ProductPhoto photo on info.ProductPhotoID = photo.ProductPhotoID " +
//"join Production.ProductInventory inv on inv.ProductID = info.ProductID " +
//"where Quantity>0 or Quantity - (Select sum(D.OrderQty) " +
//" from Purchasing.PurchaseOrderDetail D " +
//" join Purchasing.PurchaseOrderHeader H on H.PurchaseOrderID = D.PurchaseOrderID " +
//" where Status = 1 and prod.ProductID = D.ProductID " +
//" group by ProductID) > 0 " +
//"group by rownumber, prod.ProductID, prod.Name, photo.ThumbNailPhoto " +
// "Having (rownumber>@START and rownumber<@STOP) ";
// using (SqlConnection connect = new SqlConnection(Conect))
// {
// SqlCommand command = new SqlCommand(Get, connect);
// connect.Open();
// command.Parameters.AddWithValue("@START", 0);
// command.Parameters.AddWithValue("@STOP", 100);
// SqlDataReader reader = command.ExecuteReader();
// while (reader.Read())
// {
// for (int i = 0; i < reader.FieldCount; i++)
// {
// Console.WriteLine(reader[i] + "\t");
// }
// }
// }
var products = db.ProductProductPhotoes
.Include(m => m.Product)
.Include(m => m.Product.ProductInventories)
.Include(m => m.ProductPhoto)
.Where(m => ((m.Product.ProductInventories.Sum(c => c.Quantity) -
(from PurchaseOrderDetail Detail in db.PurchaseOrderDetails
join PurchaseOrderHeader Header in db.PurchaseOrderHeaders on Detail.PurchaseOrderID equals Header.PurchaseOrderID
where Detail.ProductID == m.ProductID && Header.Status == 1
select Detail.OrderQty).Sum(v => v)
) > 1) || (from PurchaseOrderDetail Detail in db.PurchaseOrderDetails
join PurchaseOrderHeader Header in db.PurchaseOrderHeaders on Detail.PurchaseOrderID equals Header.PurchaseOrderID
where Detail.ProductID == m.ProductID && Header.Status == 1
select Detail.Product.Name).FirstOrDefault() == null &&
(m.Product.ProductInventories.Sum(c => c.Quantity) > 0))
.OrderBy(m => m.ProductID)
//.Skip(0)
//.Take(50)
.Select(m => new ProductDTO
{
ProductName = m.Product.Name,
ThumbNailPhoto = m.ProductPhoto.ThumbNailPhoto,
ThumbNailPhotoFileName = m.ProductPhoto.ThumbnailPhotoFileName,
ProductID = m.ProductID,
});
foreach (var p in products)
{
Console.WriteLine(p.ProductID + " : " + p.ProductName);
}
Console.WriteLine((float)products.Count() / 50);
}
public Repository()
{
this.db = new ModelAW();
}
public CategoryRep()
{
this.db = new ModelAW();
}
