为什么没有酒店通的 模版

renq00

求 T3 用友通标准版的 数据字典

想自己做张表

可以根据 菜品的 标准成本 取出 会员卡消费后的 毛利润

nazi8888

坐等高手解决

renq00

可以 在 T3餐饮通普及版中  根据 菜品的 成本 取出  会员卡 消费的 金额 成本 利润   注意是针对使用会员卡结账的客人

SELECT
--ck.id,
ck.CheckOutNo 收银单号,
desk.TableName 餐台,
ca.CashierName 收银点,
card.MemberCardNo 会员卡号,
card.CustomerName 持卡人,
(case WHEN acd.BillType = 6 THEN (ck.ReceivableMoney - ck.PutOffMoney)* - 1 ELSE ck.ReceivableMoney - ck.PutOffMoney END) 结账金额,
ck.DiscountMoney 折扣金额,  
df.price as 成本,
(case WHEN acd.BillType = 6 THEN (ck.ReceivableMoney - ck.PutOffMoney)* - 1 ELSE ck.ReceivableMoney - ck.PutOffMoney END)-df.price as 利润,
case when ck.DiscountMoney >0 then convert(varchar(20),convert(decimal(38,2),round((ck.DiscountMoney*1.0/((case WHEN acd.BillType = 6 THEN (ck.ReceivableMoney - ck.PutOffMoney)* - 1 ELSE ck.ReceivableMoney - ck.PutOffMoney END)*1.0))*100,2)))+'%'  else '' end as 折扣率,
sale.staffName 折扣员工,
CASE WHEN acd.billtype = 5 THEN acd.TrueAccount *(- 1)*1.00 ELSE acd.TrueAccount*1.00 END 扣卡金额,  
CASE WHEN acd.BillType = 6 THEN (acd.TrueScore)*(- 1)*1.00 ELSE acd.TrueScore*1.00 END 本次积分,
cash.staffName 收银员,acd.BillDate 收银时间  
FROM CY_Account_ConsumDetails acd  
LEFT JOIN CY_FD_CheckOut ck ON acd.ToAccount = ck.Id  
LEFT JOIN CY_FD_Consumption fc ON ck.ConsumptionId = fc.Id  
LEFT JOIN CY_Card_MainCardInfo card ON acd.ToCard = card.id  
LEFT JOIN CY_BD_DinnerTable desk ON fc.DeskId = desk.TableId  
LEFT JOIN (select distinct ConsumptionId,DiscountPerson from CY_FD_Discount) dis ON dis.ConsumptionId = fc.Id
LEFT JOIN CY_BD_Staff sale ON sale.staffId = dis.DiscountPerson  
LEFT JOIN CY_BD_Staff cash ON cash.staffId = ck.Cashier  
LEFT JOIN CY_BD_Cashier ca ON ca.Id=ck.Cash
left join
(
SELECT --cod.ID,cod.ConsumptionId,cod.CheckOutId,cod.DishId ,cod.BehaviorId,cod.OrderDish,cod.OrderState,cod.MealGroupId  ,cod.StartBillPerson,cod.OriginalDeskId,cod.CacheBillPerson  ,cod.BackBillPerson,cod.TransferBillPerson,cod.PresentPerson  ,cod.IsMealGroup,cod.ConsumptionMethod ,
cod.CheckOutId as id ,sum(isnull(df.cb_price,0)) as price/*,
df.Name '菜品名称'  ,case when  br.RemarksName<>'' then br.RemarksName when brb.RemarksName<>'' then brb.RemarksName end  '做法',cod.SellPrice '单价',
cod.Discount*100 '折扣率',cod.Count*1.00 '数量'  ,cod.DiscountPrice '折扣单价',cod.DiscountMoney '折后金额',s.staffName '点菜员',cod.Remark '备注'  ,
case cod.ConsumptionMethod when 2 then '退菜' when 0 then '消费' when 1 then '赠送' end  '消费方式' ,sa.staffName '录单人',cod.InputDate '录单时间'  ,
case cod.OrderState  when 1 then '点单' when 2 then '加单' when 0 then '未下单' end '点单状态' ,sb.staffName '催单人',cod.UrgeBillCount '催单次数',
cod.UrgeBillTime '催单时间'  ,sc.staffName '起单人',cod.StartBillTime '起单时间'  ,sd.staffName '缓单人',cod.CacheBillTime '缓单时间'  ,se.staffName '退单人',
cod.BackBillDate '退单时间'  ,case cod.HangUpState  when 1 then '挂起' when 0 then '正常' end '挂起状态' ,sf.staffName '转单人'  ,sh.staffName '修改人',
si.staffName '核价人' ,case cod.CookRate when '2' then '催单' when 1 then '缓单' when 4 then '起单' when 3 then '等叫' when 0 then '正常' end '进度' ,
sg.staffName '赠送人',cod.PresentDate '赠送时间',cod.PresentReason '赠送原因'  ,dt.TableName '原餐台'*/      
from CY_FD_CheckOutDetail cod  
LEFT JOIN CY_FD_Consumption c ON c.Id=cod.ConsumptionId  
LEFT JOIN CY_FD_CheckOut co ON co.Id=cod.CheckOutId  
LEFT JOIN cy_BD_DishesFiles df ON df.Id=cod.DishId  
--LEFT JOIN cy_BD_Behavior b ON b.Id=cod.BehaviorId  
--LEFT JOIN CY_BD_BillRemarks br ON br.RemarksId=b.RemarkId  
--LEFT JOIN CY_BD_BillRemarks brb ON brb.RemarksId=cod.BehaviorId  
--LEFT JOIN CY_BD_Staff s ON s.staffId=cod.OrderDish  
--LEFT JOIN CY_BD_Staff sa on sa.staffId=cod.Input  
--LEFT join CY_BD_Staff sb on sb.staffId=cod.UrgeBillPerson  
--LEFT JOIN CY_BD_Staff sc on sc.staffId=cod.StartBillPerson  
--LEFT JOIN CY_BD_Staff sd on sd.staffId=cod.CacheBillPerson  
--LEFT JOIN CY_BD_Staff se on se.staffId=cod.BackBillPerson  
--LEFT join CY_BD_Staff sf on sf.staffId=cod.TransferBillPerson  
--LEFT JOIN CY_BD_Staff sg on sg.staffId=cod.PresentPerson  
--LEFT JOIN CY_BD_Staff sh on sh.staffId=cod.ModifyCount  
--LEFT JOIN CY_BD_Staff si on si.staffId=cod.ModifyPrice  
--LEFT JOIN CY_BD_DinnerTable dt on dt.TableId=cod.OriginalDeskId  
--LEFT JOIN cy_BD_DishesFiles dfi on dfi.Id=cod.MealGroupDishId  
--where cod.CheckOutId='12732783-498a-438b-9cac-5d12c586ac1c'
group by cod.CheckOutId
) as df on df.id=ck.id
Where  1=1  and CK.CashDate  BETWEEN '2013-05-04 00:00:00 ' AND '2013-05-04 23:59:59 '
order by 收银单号,收银时间

nazi8888

高手已解决！二次开发啊