

--create table tbl_foods
--(
--	Food# bigint not null primary key,  -- کد غذا
--	FoodName nvarchar(50) not null,     -- نام غذا
--	FoodPrice decimal(18, 0) not null   -- قیمت غذا
--)




--create table tbl_customers
--(
--	customer# bigint not null primary key,  -- کد مشتری
--	customerName nvarchar(50) not null,     -- نام مشتری
--	CustomerMobile decimal(18, 0) not null   -- تلفن همراه
--)




--create table tbl_factor
--(
--	factor# bigint not null primary key,  -- کد فاکتور
--	customer# bigint not null,			  -- کد مشتری
--	factorDateShamsi nvarchar(10) not null,    -- تاریخ فاکتور
--)





--create table tbl_factor_detail
--(
--	factorDetail# bigint not null primary key,  -- کد جزئیات فاکتور
--	factor# bigint not null ,				    -- کد فاکتور
--	Food# bigint not null ,						-- کد غذا
--	FoodQuantity bigint not null,				-- تعداد غذا
--	UnitPrice  decimal not null,                 -- قیمت واحد 
--	TotalPrice as (FoodQuantity * UnitPrice)   -- قیمت کل از نوع محاسباتی
--)







--create table tbl_foods
--(
--	Food# bigint not null primary key,  -- کد غذا
--	FoodName nvarchar(50) not null,     -- نام غذا
--	FoodPrice decimal(18, 0) not null   -- قیمت غذا
--)

--alter table tbl_foods
--add fodGroup nvarchar(50)

--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (1 , 'قورمه سبزی ' , 418806 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (2 , 'خورشت آلو ' , 122492 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (3 , 'فسنجون ' , 195937 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (4 , 'زرشک پلو با مرغ ' , 304416 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (5 , 'شوید پلو با مرغ' , 195667 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (6 , 'مرغ با پلو زعفرونی ' , 112468 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (7 , 'باقالی پلو با گوشت ' , 318527 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (8 , 'باقالی پلو با مرغ ' , 346593 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (9 , 'سالاد الویه' , 76791 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (10 , 'سالاد ماکارانی ' , 33637 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (11 , 'مرغ سرخ شده با نون ' , 348905 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (12 , 'سالاد ماکارانی ' , 461307 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (13 , 'املت با قارچ و فلفل دلمه' , 352608 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (14 , 'کوفته' , 233811 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (15 , 'دلمه فلفل' , 103009 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (16 , 'دلمه برگ ' , 272615 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (17 , 'عدس پلو ' , 535323 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (18 , 'لوبیا پلو ' , 423254 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (19 , 'استانبولی ' , 209426 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (20 , 'خوراک لوبیا سبز' , 266634 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (21 , 'خوراک با جیگر و دل‌ ' , 300956 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (22 , 'آبگوشت ' , 370772 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (23 , 'اش رشته ' , 21105 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (24 , '‌اش دوغ' , 356388 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (25 , '‌اش جوو ' , 494820 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (26 , 'سوپ جو' , 187314 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (27 , 'سوپ رشته ' , 64583 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (28 , 'سوپ مرغ ' , 234144 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (29 , 'سوپ سفید با شیرو خامه ' , 424478 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (30 , 'عدسی‌ ' , 197057 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (31 , 'کلم پلو ' , 226698 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (32 , 'هویج پلو ' , 411265 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (33 , 'قلیه ماهی ' , 240221 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (34 , 'خورشت آلو بخارا ' , 396008 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (35 , 'میگو پلو ' , 278808 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (36 , 'حلیم بادمجان ' , 95549 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (37 , 'خوراک زبان ' , 153031 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (38 , 'دمپخت ' , 48091 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (39 , 'جوجه کباب ' , 538513 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (40 , 'کباب تابه ای با برنج ' , 156071 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (41 , 'و اینها که سبک و راحت ترن ' , 340055 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (42 , 'کشک و بادمجان ' , 168746 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (43 , 'سالاد نودل ' , 468871 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (44 , 'شامی ' , 175532 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (45 , 'آش ماست ' , 437569 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (46 , 'لوبیا گرم ' , 96018 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (47 , 'دوپیازه (سیب زمینی+پیاز+گوجه) ' , 165118 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (48 , 'بورانی(اسفناج) ' , 40098 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (49 , 'خورشت خلال ' , 271447 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (50 , 'کباب بشقابی ' , 249155 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (51 , 'جوجه هندی ' , 328888 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (52 , 'عدس پلو ' , 16745 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (53 , 'اسفناج پلو ' , 175135 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (54 , 'هویج پلو ' , 74700 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (55 , 'قیمه نثار ' , 179927 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (56 , 'زرشک پلو ' , 418106 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (57 , 'ماهی پلو ' , 345114 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (58 , 'مرغ سوخاری ' , 530083 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (59 , 'قیمه بادمجون ' , 455320 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (60 , 'قیمه سیب ' , 304808 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (61 , 'قارچ پلو ' , 333871 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (62 , 'خورشت قارچ ' , 104717 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (63 , 'خورشت الو و اسفناج' , 496198 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (64 , 'پیتزا' , 175324 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (65 , 'لازانیا' , 330368 , null)
--insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) VALUES (66 , 'خوراک لوبیا چیتی' , 328594 , null)


--select * from tbl_foods 

--select * from tbl_foods where FoodName like '%پلو%'



--update tbl_foods set fodGroup = N'چلو' where FoodName like '%پلو%'

--select * from tbl_foods where fodgroup is null



--select fodGroup , count(foodname) as FoodsCount 
--from  tbl_foods group by fodGroup



--select * from tbl_customers








--insert into tbl_customers ([customer#],[customerName],[CustomerMobile])
--VALUES (1,N'امید امینی' ,0912111111)
--GO
--insert into tbl_customers ([customer#],[customerName],[CustomerMobile])
--VALUES (2,N'حمید رواقی' ,0912122200)
--GO
--insert into tbl_customers ([customer#],[customerName],[CustomerMobile])
--VALUES (3,N'ایمان صرافی' ,09121777777)
--GO
--insert into tbl_customers ([customer#],[customerName],[CustomerMobile])
--VALUES (4,N'ایمان صرافی' ,09121777777)



--select * from tbl_customers

--update tbl_customers set customerName = N'شیوا ریاحی' , 
--CustomerMobile = 912665544 where customer# = 4
--select * from tbl_customers

--insert into tbl_factor (factor#,customer#,factorDateShamsi)
--VALUES (101,1,N'1398/02/22')


--INSERT INTO tbl_factor_detail ([factorDetail#],[factor#],[Food#],[FoodQuantity],[UnitPrice])
--VALUES(1 , 101 , 1 ,2 , (select FoodPrice  FROM tbl_foods where Food# = 1/*دریافت قیمت واحد*/))
--GO
--INSERT INTO tbl_factor_detail ([factorDetail#],[factor#],[Food#],[FoodQuantity],[UnitPrice])
--VALUES(2 , 101 , 5 ,3 , (select FoodPrice  FROM tbl_foods where Food# = 5/*دریافت قیمت واحد*/))
--GO
--INSERT INTO tbl_factor_detail ([factorDetail#],[factor#],[Food#],[FoodQuantity],[UnitPrice])
--VALUES(3 , 101 , 33 ,6 , (select FoodPrice  FROM tbl_foods where Food# = 33/*دریافت قیمت واحد*/))
--GO



--select * from tbl_factor
--select * from tbl_factor_detail




--select f.factor# , f.factorDateShamsi , sum(d.totalPrice) as FactorTotalPrice
--from tbl_factor f inner join tbl_factor_detail d on f.factor# = d.factor#  
--group by  f.factor# , f.factorDateShamsi


--select  f.factor#, f.factorDateShamsi, 
--c.customerName, fo.FoodName, d.FoodQuantity, 
--d.UnitPrice, d.TotalPrice 
--FROM         
--tbl_factor AS f 
--INNER JOIN tbl_factor_detail AS d ON f.factor# = d.factor# 
--INNER JOIN tbl_foods AS fo ON d.Food# = fo.Food# 
--INNER JOIN tbl_customers AS c ON f.customer# = c.customer#




--select  f.factor#, f.factorDateShamsi, 
--c.customerName, fo.FoodName, d.FoodQuantity, 
--d.UnitPrice, d.TotalPrice 
--FROM         tbl_factor AS f 
--INNER JOIN tbl_factor_detail AS d ON f.factor# = d.factor# 
--INNER JOIN tbl_foods AS fo ON d.Food# = fo.Food# 
--INNER JOIN tbl_customers AS c ON f.customer# = c.customer#
--order by d.TotalPrice desc


--alter table  tbl_customers
--add IsWoman bit


--update tbl_customers    SET IsWoman = 0
-- WHERE customer# < 4
--GO  -- مشتریان مرد را مشخص می نماید




--update tbl_customers    SET IsWoman = 1
-- WHERE customer# = 4
--GO  -- مشتریان خانم را مشخص می نماید

--select * from tbl_customers







--SELECT customer# ,customerName ,CustomerMobile
--,(case when  IsWoman = 1 then N'خانم' else N'آقا' end) as Jensiiat
--FROM tbl_customers


--create procedure SpGetCustomerNames
--as 
--begin
--SELECT customer# ,customerName ,CustomerMobile
--,(case when  IsWoman = 1 then N'خانم' else N'آقا' end) as Jensiiat
--FROM tbl_customers
--end
--go


--exec SpGetCustomerNames




--insert into tbl_factor (factor#,customer#,factorDateShamsi)
--VALUES (104,1,N'1398/02/23')


--INSERT INTO tbl_factor_detail ([factorDetail#],[factor#],[Food#],[FoodQuantity],[UnitPrice])
--VALUES(7 , 103 , 35 ,6 , (select FoodPrice  FROM tbl_foods where Food# = 35/*دریافت قیمت واحد*/))
--GO
----INSERT INTO tbl_factor_detail ([factorDetail#],[factor#],[Food#],[FoodQuantity],[UnitPrice])
----VALUES(2 , 101 , 5 ,3 , (select FoodPrice  FROM tbl_foods where Food# = 5/*دریافت قیمت واحد*/))
----GO
----INSERT INTO tbl_factor_detail ([factorDetail#],[factor#],[Food#],[FoodQuantity],[UnitPrice])
----VALUES(3 , 101 , 33 ,6 , (select FoodPrice  FROM tbl_foods where Food# = 33/*دریافت قیمت واحد*/))
----GO

--create procedure SpGetFactorDetail
--@Factor# bigint
--as
--begin
--select  f.factor#, f.factorDateShamsi, 
--c.customerName, fo.FoodName, d.FoodQuantity, 
--d.UnitPrice, d.TotalPrice 
--FROM         tbl_factor AS f 
--INNER JOIN tbl_factor_detail AS d ON f.factor# = d.factor# 
--INNER JOIN tbl_foods AS fo ON d.Food# = fo.Food# 
--INNER JOIN tbl_customers AS c ON f.customer# = c.customer#
--where f.factor# = @Factor#
--end



--exec SpGetFactorDetail 101
--exec SpGetFactorDetail 103


--SELECT  *   FROM tbl_customers
--where customer# not in (SELECT customer#  FROM tbl_factor)


--create function FnGetCustomerPurchase(@customer# bigint)
--returns decimal(18,0)
--as 
--begin
--declare @TotalPurch decimal(18,0)
--set @TotalPurch = (SELECT      SUM(d.TotalPrice) AS SumOfPurchase
--FROM         dbo.tbl_factor AS f INNER JOIN
--                      dbo.tbl_factor_detail AS d ON f.factor# = d.factor#
--GROUP BY f.customer#
--having  f.customer# = @customer#)
--return @TotalPurch
--end



--select  dbo.FnGetCustomerPurchase(1) as TotalPurchase
--select  dbo.FnGetCustomerPurchase(2) as TotalPurchase





--delete from tbl_foods where Food# = 60 and Food# not in 
--(SELECT distinct Food#  FROM tbl_factor_detail)
--select 
--o.name as TableName , s.name as ColumnName 
--from sys.all_columns s inner join sys.all_objects o on s.object_id = o.object_id
--where type = 'U'



--declare @Food# bigint
--set @Food# = 2
--SELECT FoodPrice FROM tbl_foods where Food# = @Food#


--create view FoodsNotSaled
--as
--SELECT     fd.Food#, fd.FoodName, f.FoodQuantity
--FROM         dbo.tbl_foods AS fd LEFT OUTER JOIN
--                      dbo.tbl_factor_detail AS f ON fd.Food# = f.Food#
--WHERE     (f.FoodQuantity IS NULL)



--select * from FoodsNotSaled


--create procedure TedadeForoosh
--@Food# bigint 
--as begin
--declare @FoodTedad bigint 
--set @FoodTedad = (SELECT     SUM(FoodQuantity) AS Expr1
--FROM         dbo.tbl_factor_detail
--GROUP BY Food#
--HAVING      (Food# = @Food#))
--if @FoodTedad > 0 begin select N'تعداد فروخته شده: ' + convert(nvarchar(50) , @FoodTedad) as tedad end
--else  begin select N'فروشی نداشته ' as tedad end
--END



--TedadeForoosh 2
--go 
--TedadeForoosh 1





--update tbl_foods set fodGroup = N'چلو زرین'
--output deleted.*
-- where FoodName like '%پلو%'
 
 
 --delete from tbl_foods 
 --output deleted.*
 --where Food# = 61
 
 
 
 --insert into tbl_foods (Food#,FoodName,FoodPrice,fodGroup) 
 --output inserted.*
 --VALUES (69 , 'خوراک لازانیا لبنانی' , 450000 , null)
 
 
 
--SELECT    
--case when f.FoodQuantity is null then  N'تعداد فروش: 0 مورد' 
--else N'تعداد فروش:' + convert(nvarchar(10) , f.FoodQuantity) end as Foroosh,
--  fd.FoodName
--FROM         dbo.tbl_foods AS fd LEFT OUTER JOIN
--dbo.tbl_factor_detail AS f ON fd.Food# = f.Food#


SELECT     cus.customer#, cus.customerName, cus.IsWoman, SUM(fd.FoodQuantity) AS Expr1
FROM         dbo.tbl_customers AS cus INNER JOIN
                      dbo.tbl_factor AS f ON cus.customer# = f.customer# INNER JOIN
                      dbo.tbl_factor_detail AS fd ON f.factor# = fd.factor# INNER JOIN
                      dbo.tbl_foods AS fo ON fd.Food# = fo.Food#
GROUP BY cus.customer#, cus.customerName, cus.IsWoman
HAVING      (cus.IsWoman = 1)
Except
SELECT     cus.customer#, cus.customerName, cus.IsWoman, SUM(fd.FoodQuantity) AS Expr1
FROM         dbo.tbl_customers AS cus INNER JOIN
                      dbo.tbl_factor AS f ON cus.customer# = f.customer# INNER JOIN
                      dbo.tbl_factor_detail AS fd ON f.factor# = fd.factor# INNER JOIN
                      dbo.tbl_foods AS fo ON fd.Food# = fo.Food#
GROUP BY cus.customer#, cus.customerName, cus.IsWoman
HAVING      (cus.IsWoman = 0)