Project 2

mongosh --help

mongosh

use ecommerce

customers:

 try {db.customers.insertMany([{name: "name1",gender: "Male",dob: ISODate("1990-01-01"),city: "Cityville",pw: "1password",email: "[email protected]",delivery_addresses: [{address_line: "123 Main St",city: "Cityville",state: "tamilnadu",postal_code: "12345",country: "india"}],contacts: ["1234567890"],membership_type: "normal",current_orders: ["boat x1 headset","sanddisc16gb pendrive"],prev_orders: ["red flock m","bucket 18l","laptop sticker"],payment_info: [{card_type: "Visa",card_number: "4321567898761234",expiry_date: "12/25"}]},{name: "name5",gender: "Male",dob: ISODate("1998-01-01"),city: "bopal",pw: "password2",email: "[email protected]",delivery_addresses: [{address_line: "anna Main St",city: "landown",state: "madhya pradesh",postal_code: "654321",country: "india"}],contacts: ["0987654321"],membership_type: "normal",current_orders: ["wooden table","bowl"],prev_orders: ["samsung tv 42inch","swing chair","wall sticker"],payment_info: [{card_type: "platinum",card_number: "4567098734566789",expiry_date: "02/24"}]}]);} catch(e){print (e);}

sellers:
 try{db.sellers.insertMany([{name:"sellername1",mobile_no: "9876543210",password: "1password",categories_provided: ["Electronics", "Clothing"]},{name:"sellername2",mobile_no: "8765456787",password: "2password",categories_provided: ["stationery", "hardware"]}]);}catch(e){print(e);}


categories:
 try{db.Categories.insertMany([{name: "Electronics",subcategories: [{name: "Mobile Phones",subcategories: [{company:"samsung",model:"galaxy on8"},{company:"redmi",model:"note7"}]}]},{name: "Dress",subcategories: [{name: "modern dress",subcategories: [{company:"MCR",model:"jeans"},{company:"Ramraj",model:"tshirts"}]}]}]);}catch(e){print(e);}


products:

try{db.Products.insertMany([{name: "Smartphone Model X",color: "Black",seller_id:"64d8b029fe32682eae59f790",sizes_available: ["S", "M", "L"],quantity_available: 100,cost: 599.99, current_discount: 10,place_of_origin: "korean",is_featured: true, popularity_index: 8,avg_ratings: 4.5,total_ratings: 100,reviews_for_ratings: [],review_country: "india",is_on_sale: true,categories: ["Electronics", "Mobile Phones"],brands: ["Samsung X"], images: ["image1", "image2"],questions_with_answers: [],description: "Product description1",tags: ["Smartphone", "Mobile", "Gadgets"],related_products_ids: []},{name: "Smartphone2 Model X",color: "Blue",seller_id:"64d8b029fe32682eae59f791",sizes_available: ["S", "M", "L"],quantity_available: 99,cost: 1599.99, current_discount: 17,place_of_origin: "china",is_featured: true, popularity_index: 5,avg_ratings: 4.5,total_ratings: 99,reviews_for_ratings: [],review_country: "china",is_on_sale: true,categories: ["Mobile Phone", "Hardwares"],brands: ["Redmi X"], images: ["image3", "image4"],questions_with_answers: [],description: "Product description2",tags: ["Pipes", "Mobile", "Bulbs"],related_products_ids: []}]);} catch(e){print(e);}

cart:

 try{db.Cart.insertMany([{customer_id: "64d8c151ef4a346909a97949",items: [{product_id: "64d8c580ef4a346909a9794f",quantity: 2}],total_price: 1199.98},{customer_id: "64d8c151ef4a346909a9794a",items: [{product_id:"64d8c580ef4a346909a97950",quantity: 2}],total_price: 1199.98}]);}catch(e){print(e);}

orders:

 try{db.orders.insertMany([{customer_id:"64d8c151ef4a346909a97949",status: "shipped",delivery_expectation: ISODate("2023-08-15"),delivery_date: ISODate("2023-08-10"),order_date: ISODate("2023-08-05"),shipping_details: [{address: [{address_line: "123 Main St",city: "Chennai",state: "tamilnadu",postal_code: "12345",country: "india"}]}],billing_details:[{name: "John Doe",address: [{address_line: "123 Main St",city: "Chennai",state: "tamilnadu",postal_code: "12345",country: "india"}],items: [{product_id:"64d8c580ef4a346909a9794f",quantity: 2,price: 599.99,discount: 10}]}]},{customer_id:"64d8c151ef4a346909a9794a",status: "delivered",delivery_expectation: ISODate("2023-02-14"),delivery_date: ISODate("2023-02-14"),order_date: ISODate("2023-02-06"),shipping_details: [{address: [{address_line: "123 anna st",city: "Chennai",state: "tamilnadu",postal_code: "765434",country: "india"}]}],billing_details:[{name: "sruthin1",address: [{address_line: "123 anna st",city: "Chennai",state: "tamilnadu",postal_code: "765434",country: "india"}],items: [{product_id:"64d8c580ef4a346909a97950",quantity: 3,price: 699.99,discount: 12}]}]}]);}catch(e){print(e);}

coupons:


try{db.coupons.insertMany([{code: "SUMMER2023",discount_rule: "percentage",percentage: 15},{code: "CHRISTMAS2023",discount_rule: "percentage",percentage: 15}]);}catch(e){print(e);}

reviews:

try{db.reviews.insertMany([{product_id:"64d8c580ef4a346909a9794f",customer_id:"64d8c151ef4a346909a97949",rating: 4,review_text: "Great product!",timestamp: ISODate("2023-08-05")},{product_id:"64d8c580ef4a346909a97950",customer_id:"64d8c151ef4a346909a9794a",rating: 4,review_text: "worth for price!",timestamp: ISODate("2023-02-12")}]);}catch(e){print(e);}

quries:

1)db.ecommerce.aggregate([{$unwind: "$items"},{$group: {_id: "$customer_id",totalAmount: { $sum: { $multiply: ["$items.price", "$items.quantity"] } },totalItems: { $sum: "$items.quantity" }}},{$project: {_id: 0,customer_id: "$_id", averageOrderValue: { $divide: ["$totalAmount", "$totalItems"]}}}]);


2)db.ecommerce.aggregate([{$unwind: "$items"},{$group: {_id: "$customer_id",totalAmount: { $sum: { $multiply: ["$items.price", "$items.quantity"] } },orderCount: { $sum: 1 }}},{$match: {orderCount: { $gte: 1 },_id: { $in: db.ecommerce.aggregate([{$match: {order_date: { $gte: new Date(new Date() - 60 * 24 * 60 * 60 * 1000) }}},{$group: {_id: "$customer_id"}}]).toArray().map(doc => doc._id)}}},{$sort: { totalAmount: -1 }},{$limit: 10}]);



3)var currentDate = new Date();

var startOfMonth = new Date(currentDate.getFullYear(), currentDate.getMonth(), 1);

var endOfMonth = new Date(currentDate.getFullYear(), currentDate.getMonth() + 1, 0);
db.orders.aggregate([{$match: {order_date: { $gte: startOfMonth, $lte: endOfMonth }}},{$group: {_id: null,totalAmount: { $sum: { $sum: "$items.price" } }}}]);

4) db.customers.aggregate([{$group: {_id: "$city",customerCount: { $sum: 1 }}},{$project: {city: "$_id",customerCount: 1, _id: 0}}]);


5)db.sellers.aggregate([{$lookup: {from: "products",let: { sellerName: "$name" },pipeline: [{$match: {$expr: { $eq: ["$$sellerName", "$seller"] }}}],as: "sellerProducts"}},{$unwind: "$sellerProducts"},{$lookup: {from: "orders",let: { productID: "$sellerProducts._id" },pipeline: [{$match: {$expr: { $in: ["$$productID", "$items.product_id"] }}}],as: "sellerOrders"}},{$unwind: "$sellerOrders"},{$lookup: {from: "customers",localField: "sellerOrders.customer_id",foreignField: "_id",as: "orderCustomer"}},{$unwind: "$orderCustomer"},{$match: {$expr: { $eq: ["$city", "$orderCustomer.city"] }}},{ $group: {_id: "$_id", name: { $first: "$name" },totalProductsSold: { $sum: "$sellerOrders.items.quantity" }}},{$sort: { totalProductsSold: -1 }}]);