if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Proc_AddProductClass]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Proc_AddProductClass]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Proc_AllAgentTeamSellQuantity]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Proc_AllAgentTeamSellQuantity]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Proc_ComputeGrade]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Proc_ComputeGrade]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Proc_ComputeGrade_Float]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Proc_ComputeGrade_Float]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Proc_ComputeLevel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Proc_ComputeLevel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Proc_ComputePriv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Proc_ComputePriv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Proc_Percentage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Proc_Percentage]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Proc_Percentage1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Proc_Percentage1]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Proc_UpdatePassWord]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Proc_UpdatePassWord]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SearchOrder]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SearchOrder]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateOrder]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateOrder]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[updatePInvoicingDetail ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[updatePInvoicingDetail ]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.Proc_AddProductClass--添加商品类别
(
@pname varchar(64) , --商品名称
@pprice float , --商品单价
@premark varchar(300), --备注
@prv int output --返回值 判断是否重复 如果返回为0则已有此商品不能重复添加 返回为1没有重复则添加成功
)
AS
if exists(select fld_ID from T_ProductClass where fld_ProductName=@pname )
begin
set @prv=0
end
else
begin
insert into T_ProductClass (fld_ProductName,fld_Price,fld_Remark)
values(@pname,@pprice,@premark)
set @prv=1
end
return @prv
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.Proc_AllAgentTeamSellQuantity --管理员用户查看团队业绩
AS
select fld_AgentName,
(select fld_Tel from T_AgentInfo where fld_Name=fld_AgentName ) as tel ,
sum( fld_ActualSales) as actualsales,
sum(fld_Profit) as profit
from T_InvoicingDetail group by fld_AgentName
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--------------
CREATE PROCEDURE [dbo].[Proc_ComputeGrade] AS --计算代理商的等级fld_Grade，不存在降级，故使用临时表保存处理过的等级
DECLARE @LevelID INT --级别ID
DECLARE @LevelName VARCHAR(21) --级别名称
DECLARE @SalesQuantity BIGINT --累计销售量
DECLARE @SalesOrg BIGINT --连续月组织销售量
DECLARE @SalesPer BIGINT --连续月个人销售量
DECLARE @MonthNum INT --连续月数
if object_id('tempdb..#TempTable_LevelNamePrv') is not null
drop table [dbo].[#TempTable_LevelNamePrv]
-- 创建一个临时表
CREATE TABLE #TempTable_LevelNamePrv
(
fld_LevelNamePrv VARCHAR(21) --级别名称--已经处理过的等级名称
)
insert into #TempTable_LevelNamePrv(fld_LevelNamePrv) values(' ')
--**********设置级别！！！
--update T_AgentInfo set fld_Grade = '准代理', fld_GradeFloat = '准代理'
--级别信息循环
DECLARE Cursor_Level CURSOR FOR
SELECT fld_ID, fld_Name, fld_SalesQuantity, fld_SalesOrg, fld_SalesPer, fld_MonthNum FROM dbo.T_AgentGrade
order by fld_SalesQuantity desc --级别由大到小循环
OPEN Cursor_Level
FETCH NEXT FROM Cursor_Level INTO @LevelID, @LevelName, @SalesQuantity, @SalesOrg, @SalesPer, @MonthNum
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @AgentName VARCHAR(64)
DECLARE @Higher VARCHAR(64)
DECLARE @Higher1 VARCHAR(64)
--代理信息循环
DECLARE Cursor_Agent CURSOR FOR
SELECT fld_Name,fld_Higher FROM dbo.T_AgentInfo
where fld_Grade not in (select fld_LevelNamePrv from #TempTable_LevelNamePrv) --下一次循环时不考虑上次已经循环过的代理
OPEN Cursor_Agent
FETCH NEXT FROM Cursor_Agent INTO @AgentName, @Higher
WHILE @@FETCH_STATUS = 0
BEGIN
--计算一个代理的级别
DECLARE @SalesQuantity1 BIGINT
select @SalesQuantity1 = count(*) from T_InvoicingDetail where fld_AgentName = @AgentName-- and fld_Reason = 0 -- 个人、组织总销售量
if @SalesQuantity1 >= @SalesQuantity
begin
--**********设置级别！！！
update T_AgentInfo set fld_Grade = @LevelName where fld_Name = @AgentName
--设置所有上线的级别
while @Higher is not null
begin
update T_AgentInfo set fld_Grade = @LevelName where fld_Name = @Higher and fld_Grade not in (select fld_LevelNamePrv from #TempTable_LevelNamePrv) --设置级别
SELECT @Higher1 = fld_Higher FROM dbo.T_AgentInfo where fld_Name = @Higher and fld_Grade not in (select fld_LevelNamePrv from #TempTable_LevelNamePrv)
set @Higher = @Higher1
end
end
--
FETCH NEXT FROM Cursor_Agent INTO @AgentName, @Higher
END
CLOSE Cursor_Agent
DEALLOCATE Cursor_Agent
--
insert into #TempTable_LevelNamePrv(fld_LevelNamePrv) values(@LevelName)--存放已经处理过的级别名称
FETCH NEXT FROM Cursor_Level INTO @LevelID, @LevelName, @SalesQuantity, @SalesOrg, @SalesPer, @MonthNum
END
CLOSE Cursor_Level
DEALLOCATE Cursor_Level
--
--select fld_LevelNamePrv from #TempTable_LevelNamePrv
--SELECT fld_Name,fld_Higher FROM dbo.T_AgentInfo
-- where fld_Grade not in (select fld_LevelNamePrv from #TempTable_LevelNamePrv)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[Proc_ComputeGrade_Float] AS --计算代理商的动态等级fld_GradeFloat
DECLARE @LevelID INT --级别ID
DECLARE @LevelName VARCHAR(21) --级别名称
DECLARE @SalesOrg BIGINT --连续月组织销售量
DECLARE @SalesPer BIGINT --连续月个人销售量
DECLARE @MonthNum INT --连续月数
--**********设置级别！！！
--update T_AgentInfo set fld_Grade = '准代理', fld_GradeFloat = '准代理'
--级别信息循环
DECLARE Cursor_Level CURSOR FOR
SELECT fld_ID, fld_Name, fld_SalesOrg, fld_SalesPer, fld_MonthNum FROM dbo.T_AgentGrade
order by fld_SalesQuantity desc --级别由大到小循环
OPEN Cursor_Level
FETCH NEXT FROM Cursor_Level INTO @LevelID, @LevelName, @SalesOrg, @SalesPer, @MonthNum
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @AgentName VARCHAR(64)
DECLARE @Higher VARCHAR(64)
DECLARE @Higher1 VARCHAR(64)
--代理信息循环
DECLARE Cursor_Agent CURSOR FOR
SELECT fld_Name,fld_Higher FROM dbo.T_AgentInfo where fld_Grade = @LevelName
OPEN Cursor_Agent
FETCH NEXT FROM Cursor_Agent INTO @AgentName, @Higher
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SalesOrg1 BIGINT
declare @MonthNum1 int
set @MonthNum1 = @MonthNum
while @SalesOrg1 >= @SalesOrg and @MonthNum1 > 0
begin
--select @SalesOrg1 = count(*) from T_InvoicingDetail where fld_AgentName = @AgentName and fld_Reason = 1 -- 连续月组织销售量
--and DATEDIFF(month, fld_SaleTime, getdate()) = @MonthNum1--当前月之前的@MonthNum1个自然月，不含当前月
--and DATEDIFF(day, fld_SaleTime, getdate()) < (@MonthNum1 * 30) --当前月之前的@MonthNum1*30天，含当天
--and fld_SaleTime > DATEADD(month, -@MonthNum1, getdate()) --@MonthNum1个月之前的当天
select @SalesOrg1 = count(*) from T_InvoicingDetail as a left outer join dbo.T_AgentInfo as b on a.fld_SalesAgentName = b.fld_Name
where a.fld_AgentName = @AgentName and a.fld_Reason = 1 -- 连续月组织销售量(非同级小组)
and b.fld_Grade <> @LevelName --非同级小组!
and fld_SaleTime > DATEADD(month, -@MonthNum1, getdate()) --@MonthNum1个月之前的当天
set @MonthNum1 = @MonthNum1 - 1
end
if @MonthNum1 = 0 -- 连续@MonthNum个月的每月组织销售量都大于@SalesOrg
begin
DECLARE @SalesPer1 BIGINT
set @MonthNum1 = @MonthNum
while @SalesPer1 >= @SalesPer and @MonthNum1 > 0
begin
select @SalesPer1 = count(*) from T_InvoicingDetail where fld_AgentName = @AgentName and fld_Reason = 0 -- 连续月个人销售量
--and DATEDIFF(month, fld_SaleTime, getdate()) = @MonthNum1--当前月之前的@MonthNum1个自然月，不含当前月
--and DATEDIFF(day, fld_SaleTime, getdate()) < (@MonthNum1 * 30) --当前月之前的@MonthNum1*30天，含当天
and fld_SaleTime > DATEADD(month, -@MonthNum1, getdate()) --@MonthNum1个月之前的当天
set @MonthNum1 = @MonthNum1 - 1
end
if @MonthNum1 = 0 -- 连续@MonthNum个月的每月个人销售量都大于@SalesPer
begin
--**********设置浮动级别！！！
update T_AgentInfo set fld_GradeFloat = @LevelName where fld_Name = @AgentName
end
else
begin
update T_AgentInfo set fld_GradeFloat = '不合格' where fld_Name = @AgentName
end
end
else
begin
update T_AgentInfo set fld_GradeFloat = '不合格' where fld_Name = @AgentName
end
FETCH NEXT FROM Cursor_Agent INTO @AgentName, @Higher
END
CLOSE Cursor_Agent
DEALLOCATE Cursor_Agent
--
FETCH NEXT FROM Cursor_Level INTO @LevelID, @LevelName, @SalesOrg, @SalesPer, @MonthNum
END
CLOSE Cursor_Level
DEALLOCATE Cursor_Level
--
--select fld_LevelNamePrv from #TempTable_LevelNamePrv
--SELECT fld_Name,fld_Higher FROM dbo.T_AgentInfo
-- where fld_Grade not in (select fld_LevelNamePrv from #TempTable_LevelNamePrv)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[Proc_ComputeLevel] AS
--计算代理商的等级fld_Grade和动态等级fld_GradeFloat
--记录统计数据到T_StatisticData
exec Proc_ComputeGrade
exec Proc_ComputeGrade_Float
DECLARE @AgentName VARCHAR(64) --代理
DECLARE @Grade VARCHAR(21) --级别
DECLARE @GradeFloat VARCHAR(21) --浮动级别
DECLARE @Income MONEY --收入
DECLARE @Outpay MONEY --支出
DECLARE @Profit MONEY --盈利
DECLARE @Count INT --存在记录的数量（最多一天一条记录）
--代理信息循环
DECLARE Cursor_Agent CURSOR FOR
SELECT fld_Name, fld_Grade, fld_GradeFloat FROM dbo.T_AgentInfo
OPEN Cursor_Agent
FETCH NEXT FROM Cursor_Agent INTO @AgentName, @Grade, @GradeFloat
WHILE @@FETCH_STATUS = 0
BEGIN
--盈利=收入（实际售价+奖励金额）-支出（进价），收入不含提成，所以盈利中要加上提成
select @Profit = sum(fld_Profit) + sum(fld_Percentage), @Income = sum(fld_ActualSales + fld_AwardMoney + fld_Percentage) from T_InvoicingDetail where fld_AgentName = @AgentName
--and fld_SaleTime 当前时间（含）之前的所有销售统计：收入和支出
--and DATEDIFF(day, fld_SaleTime, getdate()) < (@MonthNum1 * 30) --当前月之前的@MonthNum1*30天，含当天
if @Profit is not null
begin
set @Outpay = (@Income - @Profit)
select @Count = count(*) from T_StatisticData where fld_AgentName = @AgentName and DATEDIFF(day, fld_Date, getdate()) = 0
if @Count > 0
begin
update T_StatisticData set fld_Date=getdate(), fld_Grade=@Grade,
fld_GradeFloat=@GradeFloat, fld_Income=@Income, fld_Outpay=@Outpay
where fld_AgentName = @AgentName and DATEDIFF(day, fld_Date, getdate()) = 0
end
else
begin
insert into T_StatisticData(fld_AgentName, fld_Date, fld_Grade, fld_GradeFloat, fld_Income, fld_Outpay)
values(@AgentName, getdate(), @Grade, @GradeFloat, @Income, @Outpay)
end
end
--公司总体情况，算在“admin”身上：
set @Grade = '省级'
set @GradeFloat = '省级'
select @Income = sum(fld_Price) from T_ProductOrder where fld_Status > 0 -- 已经付款的订单
--fld_Price和fld_AwardMoney都是总价，且乘以了折扣率
select @Outpay = sum(fld_Quantity) * 333 + sum(fld_AwardMoney+fld_DistributionCosts) from T_ProductOrder where fld_Status > 0 -- 已经付款的订单
--每枚成本333元！！！
--计算所有的提成支出：
select @Outpay = @Outpay + sum(fld_Percentage) from T_InvoicingDetail
if @Income is not null and @Outpay is not null
begin
--select @Profit = @Income - @Outpay
select @Count = count(*) from T_StatisticData where fld_AgentName = 'admin' and DATEDIFF(day, fld_Date, getdate()) = 0
if @Count > 0
begin
update T_StatisticData set fld_Date=getdate(), fld_Grade=@Grade,
fld_GradeFloat=@GradeFloat, fld_Income=@Income, fld_Outpay=@Outpay
where fld_AgentName = 'admin' and DATEDIFF(day, fld_Date, getdate()) = 0
end
else
begin
insert into T_StatisticData(fld_AgentName, fld_Date, fld_Grade, fld_GradeFloat, fld_Income, fld_Outpay)
values('admin', getdate(), @Grade, @GradeFloat, @Income, @Outpay)
end
end
--
FETCH NEXT FROM Cursor_Agent INTO @AgentName, @Grade, @GradeFloat
END
CLOSE Cursor_Agent
DEALLOCATE Cursor_Agent
--
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[Proc_ComputePriv] AS --计算代理商的特权
DECLARE @PrivID INT --特权ID
DECLARE @PrivName VARCHAR(21) --特权名称
DECLARE @MonthNum INT --连续无销售月数
DECLARE @PrivIDPrv INT --已经处理过的特权ID
set @PrivIDPrv = 0
--特权信息循环
DECLARE Cursor_Priv CURSOR FOR
SELECT fld_ID, fld_Name, fld_MonthNum FROM dbo.T_AgentGrade order by fld_MonthNum --限制由小到大，特权由大到小循环
OPEN Cursor_Priv
FETCH NEXT FROM Cursor_Priv INTO @PrivID, @PrivName, @MonthNum
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @AgentName VARCHAR(64)
--代理信息循环
DECLARE Cursor_Agent CURSOR FOR
SELECT fld_Name FROM dbo.T_AgentInfo
where fld_ID > @PrivIDPrv
OPEN Cursor_Agent
FETCH NEXT FROM Cursor_Agent INTO @AgentName
WHILE @@FETCH_STATUS = 0
BEGIN
--计算一个代理的特权
DECLARE @SaleTime_Last DATETIME
select @SaleTime_Last = max(fld_SaleTime) from T_InvoicingDetail where fld_Reason = 1
if DATEDIFF(month, @SaleTime_Last, getdate()) <= @MonthNum + 1 -- 连续无销售月数，个人；不含当月，比如：0个月无销售：一级；1个月无销售：二级；
begin
update T_AgentInfo set fld_Priv = @PrivName where fld_Name = @AgentName
end
--
FETCH NEXT FROM Cursor_Agent INTO @AgentName
END
CLOSE Cursor_Agent
DEALLOCATE Cursor_Agent
--
set @PrivIDPrv = @PrivID
FETCH NEXT FROM Cursor_Priv INTO @PrivID, @PrivName, @MonthNum
END
CLOSE Cursor_Priv
DEALLOCATE Cursor_Priv
--
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[Proc_Percentage] --插入代理商提成信息
@ProductOrderID BIGINT -- 订单ID
AS
DECLARE @AgentName VARCHAR(64) --代理商名称
DECLARE @AgentHigher VARCHAR(64) --上级代理名称
DECLARE @Price MONEY --进货价格
DECLARE @RecvDate DATETIME --收货日期
select @AgentName = a.fld_AgentName, @AgentHigher = b.fld_Higher, @Price = a.fld_Price, @RecvDate =getdate()
from T_ProductOrder a left outer join T_AgentInfo b
on a.fld_AgentName = b.fld_Name
where a.fld_ID = @ProductOrderID
DECLARE @AwardValue MONEY -- 奖励金额
DECLARE @AwardValue1 MONEY -- 奖励金额（上级）
DECLARE @Grade VARCHAR(21) --代理级别
DECLARE @Grade1 VARCHAR(21) --代理级别（上级）
DECLARE @GradeFloat VARCHAR(21) --代理级别（浮动）
select @AwardValue = b.fld_AwardValue, @Grade = a.fld_Grade from T_AgentInfo a left outer join T_AgentGrade b on a.fld_Grade = b.fld_Name
where a.fld_Name = @AgentName
--设置回滚
BEGIN TRANSACTION
--产品信息循环
DECLARE @ProductID BIGINT
DECLARE @AgentHigherTmp VARCHAR(64) --上级代理名称，临时循环使用
DECLARE Cursor_ProductID CURSOR FOR
select fld_ProductID from T_ProductOrderDetail where fld_ProductOrderID = @ProductOrderID
OPEN Cursor_ProductID
FETCH NEXT FROM Cursor_ProductID INTO @ProductID
WHILE @@FETCH_STATUS = 0
BEGIN
set @AgentHigherTmp = @AgentHigher
DECLARE @AgentHigherN VARCHAR(64) --下一个上级代理
DECLARE @PercenValue INT --提成点数
DECLARE @PercentageFactor FLOAT --提成系数
DECLARE @Level INT --适用层数
DECLARE @Layer INT --当前层数
set @Layer = 1
while @AgentHigherTmp is not null and @AgentHigherTmp <> '' --上级代理不能为空，否则没有上级
begin--A、各级代理均可向其委任的代理提成；
select @AgentHigherN = a.fld_Higher, @PercenValue = b.fld_PercenValue, @PercentageFactor = b.fld_PercentageFactor, @Level = b.fld_Levels
from T_AgentInfo a left outer join T_AgentGrade b
on a.fld_Grade = b.fld_Name
where a.fld_Name = @AgentHigherTmp
select @AwardValue1 = b.fld_AwardValue, @Grade1 = a.fld_Grade, @GradeFloat=a.fld_GradeFloat from T_AgentInfo a left outer join T_AgentGrade b on a.fld_Grade = b.fld_Name
where a.fld_Name = @AgentHigherTmp--获取该上级代理的奖励金额、级别
if @Grade1 = @GradeFloat --各级浮动级别合格（比如：省级代理每月的团体销售量应达到100枚，而个人销售量为3枚），才能享受奖励和提成！
begin
if @Grade = '省级'
begin
if @Grade1 = '省级'
begin
if @Layer <= 4
begin
insert into T_InvoicingDetail(fld_AgentName, fld_ProductID, fld_PurchaseTime, fld_Reason, fld_SalesAgentName, fld_Percentage)
values(@AgentHigherTmp, @ProductID, @RecvDate, 2, @AgentName, 50) -- D、省级代理可以突破规则“C”的限制，向其委任的四层内的省级代理各提成50元/枚。
end
else
begin
break
end
end
end
else
begin
if @Grade <> @Grade1--C、提成层数限制：直至其委任的代理级别与自己相同为止；
begin
insert into T_InvoicingDetail(fld_AgentName, fld_ProductID, fld_PurchaseTime, fld_Reason, fld_SalesAgentName, fld_Percentage)
values(@AgentHigherTmp, @ProductID, @RecvDate, 2, @AgentName, @AwardValue1-@AwardValue)--B、提成数额为提成者与其下一层代理所获得的奖励之差；
end
else
begin
break
end
end
--if @Level >= @Layer --在上级代理的提成范围之内，插入提成信息：
--begin
-- insert into T_InvoicingDetail(fld_AgentName, fld_ProductID, fld_PurchaseTime, fld_Reason, fld_SalesAgentName, fld_IntegralPoint)
-- values(@AgentHigherTmp, @ProductID, @RecvDate, 2, @AgentName, @PercenValue * @PercentageFactor)
--end
end
set @AgentHigherTmp = @AgentHigherN
set @Layer = @Layer + 1
end
FETCH NEXT FROM Cursor_ProductID INTO @ProductID
END
CLOSE Cursor_ProductID
DEALLOCATE Cursor_ProductID
--
COMMIT
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[Proc_Percentage1] --插入代理商提成信息
@ProductOrderID BIGINT -- 订单ID
AS
DECLARE @AgentName VARCHAR(64) --代理商名称
DECLARE @AgentHigher VARCHAR(64) --上级代理名称
DECLARE @Price MONEY --进货价格
DECLARE @RecvDate DATETIME --收货日期
select @AgentName = a.fld_AgentName, @AgentHigher = b.fld_Higher, @Price = a.fld_Price, @RecvDate =getdate()
from T_ProductOrder a left outer join T_AgentInfo b
on a.fld_AgentName = b.fld_Name
where a.fld_ID = @ProductOrderID
--设置回滚
BEGIN TRANSACTION
--产品信息循环
DECLARE @ProductID BIGINT
DECLARE @AgentHigherTmp VARCHAR(64) --上级代理名称，临时循环使用
DECLARE Cursor_ProductID CURSOR FOR
select fld_ProductID from T_ProductOrderDetail where fld_ProductOrderID = @ProductOrderID
OPEN Cursor_ProductID
FETCH NEXT FROM Cursor_ProductID INTO @ProductID
WHILE @@FETCH_STATUS = 0
BEGIN
set @AgentHigherTmp = @AgentHigher
DECLARE @AgentHigherN VARCHAR(64) --下一个上级代理
DECLARE @PercenValue INT --提成点数
DECLARE @PercentageFactor FLOAT --提成系数
DECLARE @Level INT --适用层数
DECLARE @Layer INT --当前层数
set @Layer = 1
while @AgentHigherTmp is not null --上级代理不能为空，否则没有上级
begin
select @AgentHigherN = a.fld_Higher, @PercenValue = b.fld_PercenValue, @PercentageFactor = b.fld_PercentageFactor, @Level = b.fld_Levels
from T_AgentInfo a left outer join T_AgentGrade b
on a.fld_Grade = b.fld_Name
where a.fld_Name = @AgentHigherTmp
if @Level >= @Layer --在上级代理的提成范围之内，插入提成信息：
begin
insert into T_InvoicingDetail(fld_AgentName, fld_ProductID, fld_PurchaseTime, fld_Reason, fld_SalesAgentName, fld_IntegralPoint)
values(@AgentHigherTmp, @ProductID, @RecvDate, 2, @AgentName, @PercenValue * @PercentageFactor)
end
set @AgentHigherTmp = @AgentHigherN
end
FETCH NEXT FROM Cursor_ProductID INTO @ProductID
END
CLOSE Cursor_ProductID
DEALLOCATE Cursor_ProductID
--
COMMIT
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.Proc_UpdatePassWord --更新密码
(
@username varchar(64) ,--用户名
@oldpassword varchar(64),--原密码
@newpassword varchar(64),--新密码
@rtnvalue int OUTPUT --返回值 0：原密码不对 1：修改成功
)
AS
declare @password varchar(64)
set @password=(select fld_Pwd from T_AgentInfo where fld_Name=@username)
if(@oldpassword=@password)
begin
update T_AgentInfo set fld_Pwd=@newpassword where fld_Name=@username
set @rtnvalue=1 --修改成功
end
else
begin
set @rtnvalue=0 --原密码不对
end
return @rtnvalue
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE SearchOrder
@OrderID int --订单id
AS
select
T_ProductInfo.fld_ID as fld_ID,
T_ProductInfo.fld_Name as fld_Name,
(select fld_Price from T_ProductClass where fld_ProductName= T_ProductInfo.fld_Name)
as fld_RetailPrice,
T_ProductInfo.fld_Remark as fld_Remark
from
T_ProductOrderDetail inner join T_ProductInfo
on T_ProductOrderDetail.fld_ProductID=T_ProductInfo.fld_ID
inner join T_ProductOrder
on T_ProductOrderDetail.fld_ProductOrderID=T_ProductOrder.fld_ID
where T_ProductOrder.fld_ID=@OrderID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE UpdateOrder
@OrderID bigint,
----
@Quantity bigint, --采购总数量
@Price money ,--采购总金额
@DiscountRate float, --实际折扣率
--@IntegralPoint int, --获得积分
@AwardMoney money, --获得奖励金额
@Addr varchar(50), --送货地址
@DistributionCosts money, --配送费用
@Remark varchar(128), --备注
-----
@Status int, --订单状态
@DeliDate DateTime --送货日期
AS
update T_ProductOrder
set fld_Quantity=@Quantity,
fld_Price=@Price,
fld_DiscountRate=@DiscountRate,
fld_AwardMoney=@AwardMoney,
fld_Addr=@Addr,
fld_Remark=@Remark,
fld_Status=@Status,
fld_DeliDate=@DeliDate
where fld_ID=@OrderID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--更新进销明细中的一条信息
CREATE PROCEDURE updatePInvoicingDetail
@AgentName varchar(64), --代理商名称
@AwardMoney money, --奖励金额
@ProID bigint ,--产品id
@Reason tinyint, --销售状态
@SaleTime datetime , --售出时间
@ActualSales money, --实际售价
@Remark varchar(128) --备注说明
AS
declare @InPrice money --进价
declare @Profit money --盈利
declare @RetailPrice money --零售价
declare @DiscountRate float --折扣率
if @Reason=0
begin
update T_InvoicingDetail set fld_Reason=@Reason,fld_SaleTime=@SaleTime,fld_ActualSales=@ActualSales,fld_Remark=@Remark,
fld_Profit=0 where fld_ProductID=@ProID
end
else
begin
set @RetailPrice=(select fld_RetailPrice from T_ProductInfo where fld_ID=@ProID)
set @DiscountRate=(select fld_DiscountRate from T_AgentGrade where fld_Name=(select fld_Grade from T_AgentInfo where fld_Name=@AgentName) )
set @InPrice=(@RetailPrice*@DiscountRate)
set @Profit=(@ActualSales-@InPrice+@AwardMoney)
update T_InvoicingDetail set fld_Reason=@Reason,fld_SaleTime=@SaleTime,fld_ActualSales=@ActualSales,fld_Remark=@Remark,
fld_Profit=@Profit where fld_ProductID=@ProID
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
