insert into SystemSetting(SettingName,SettingValue1,SettingValue2)
values 
	('CompanyConfiguration','PerformanceCalculationType','1') 	
insert into CompanyConfiguration
	select 'PerformanceCalculationType','1'
 
 insert into SystemSetting(SettingName,SettingValue1,SettingValue2)
values 
	('CompanyConfiguration','PerformanceCalculationRatio','0') 	
insert into CompanyConfiguration
	select 'PerformanceCalculationRatio','0'
 
 
insert into Permission
values ('P0411','04','绩效分配',0) 
update [User] set [Permissions]=[PERMISSIONS]+',P0411' where CHARINDEX('P0407',[Permissions])>0 or IsAdmin=1
  

go

alter table OrderUser add CtStepRatio numeric(18,4) null
go

 
ALTER procedure [dbo].[QueryOrderCostReport]	
	@BeginDate datetime,
	@EndDate datetime	 
as
begin 	

	create table #OrderCost(ItemName nvarchar(50),ItemValue decimal(18,2),ItemPercent decimal(18,2),ItemType int)
	
	declare @totalNumber int
	declare @totalAmount decimal(18,2)
	declare @totalCost decimal(18,2)
	declare @totalProfile decimal(18,2)
	declare @feeDesign decimal(18,2)
	declare @feeMaterial decimal(18,2)
	declare @feeProcessing decimal(18,2)
	declare @feefactory decimal(18,2)
	declare @feeCommission decimal(18,2)
	declare @feeCommunication decimal(18,2)
	declare @feeInstallation decimal(18,2)
	declare @feeTax decimal(18,2)
	declare @feeOther decimal(18,2) 	
	
	select @totalCost=SUM(OrderCost.TotalFee) ,
		@feeDesign=sum(OrderCost.FeeDesign),
		@feeMaterial=sum(OrderCost.FeeMaterial),
		@feeProcessing=sum(OrderCost.FeeProcessing),
		@feefactory=sum(OrderCost.FeeFactory),
		@feeCommission=sum(OrderCost.FeeCommission),
		@feeCommunication=sum(OrderCost.FeeCommunication),
		@feeInstallation=sum(OrderCost.FeeInstallation),
		@feeTax=sum(OrderCost.FeeTax),
		@feeOther=sum(OrderCost.FeeOther) ,
		@totalNumber=COUNT([Order].OrderNo),
		@totalAmount=sum([order].Amount),
		@totalProfile=SUM(case OrderCost.TotalFee when null then null else [Order].Amount-OrderCost.TotalFee end)
				
	from OrderCost inner join [Order] on [Order].OrderNo=OrderCost.OrderNo and [Order].IsActive=1 and [Order].TransactionDate>=@BeginDate and [Order].TransactionDate<=@EndDate 
	
	
	insert into #OrderCost values
	('订单总数量',@totalNumber,0,1),
	('订单总金额',@totalAmount,0,1),
	('订单总成本',@totalCost,0,1),
	('订单总毛利',@totalProfile,0,1),
	('订单毛利率',case @totalAmount when 0 then 0 else  @totalProfile/@totalAmount end,0,1),
	
	('设计费',@feeDesign,case @totalCost when 0 then 0 else @feeDesign/@totalCost end,2),
	('材料费',@feeMaterial,case @totalCost when 0 then 0 else @feeMaterial/@totalCost end,2),
	('制作费',@feeProcessing,case @totalCost when 0 then 0 else @feeProcessing/@totalCost end,2),
	('外协费',@feefactory,case @totalCost when 0 then 0 else @feefactory/@totalCost end,2),
	('业务提成',@feeCommission,case @totalCost when 0 then 0 else @feeCommission/@totalCost end,2),
	('交通费',@feeCommunication,case @totalCost when 0 then 0 else @feeCommunication/@totalCost end,2),
	('安装费',@feeInstallation,case @totalCost when 0 then 0 else @feeInstallation/@totalCost end,2),
	('税费',@feeTax,case @totalCost when 0 then 0 else @feeTax/@totalCost end,2),
	('其它费用',@feeOther,case @totalCost when 0 then 0 else @feeOther/@totalCost end,2)
	
	
	select ItemName,ItemValue,ItemPercent,ItemType from #OrderCost
	drop table #OrderCost						
	 
end
go

insert into SystemSetting(SettingName,SettingValue1,SettingValue2)
values 
	('CompanyConfiguration','PrintDeliveryShowPersonalPhone','false') 	
insert into CompanyConfiguration
	select 'PrintDeliveryShowPersonalPhone','false'
go

update configuration set SettingValue='5.0.2' where SettingName='DatabaseVersion'
 go
 