|
|
use accdb
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cyz_sum_month]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cyz_sum_month]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE cyz_sum_month
( @dpcode varchar(7),
@month datetime
)AS
select sum(营业额) as 营业额,sum(营业次数) as 营业次数 from (SELECT T_Station.StatName AS 工作站, T_Eatery.STName AS 营业区,
T_Group.GrpName AS 营业组, T_Terms.Port AS 端口, T_Terms.TermName AS 窗机,
heixin.dt AS 月份, T_Meal.MealName AS 餐别, heixin.SumOpfare AS 营业额,
heixin.CountOpfare AS 营业次数
FROM T_Station INNER JOIN
T_Eatery ON T_Station.StatID = T_Eatery.StatID INNER JOIN
T_Group ON T_Eatery.StatID = T_Group.StatID AND
T_Eatery.STID = T_Group.STID INNER JOIN
T_Terms ON T_Group.StatID = T_Terms.StatID AND
T_Group.STID = T_Terms.STID AND T_Group.GrpID = T_Terms.GrpID INNER JOIN
(SELECT consumerec.StatID, consumerec.Port, consumerec.Term,
consumerec.MealID, consumerec.dt, SUM(consumerec.OpFare)
AS SumOpfare, COUNT(*) AS CountOpfare
FROM (SELECT StatID, Port, Term, CustomerID,
RTRIM(CAST(YEAR(dbo.T_ConsumeRec.OpDt) AS char))
+ '-' + RTRIM(CAST(MONTH(dbo.T_ConsumeRec.OpDt) AS char)) + '-' + rtrim(day(0)) AS dt,
CollectDt, MealID, OpFare, MngFare, OddFare
FROM dbo.T_ConsumeRec) consumerec INNER JOIN
T_Customers ON
consumerec.CustomerID = T_Customers.CustomerID
WHERE (T_Customers.Account LIKE @dpcode + '%')/*参数*/ AND (consumerec.dt = @month)/*参数*/
GROUP BY consumerec.StatID, consumerec.Port, consumerec.Term,
consumerec.MealID, consumerec.dt) heixin ON
T_Terms.Port = heixin.Port AND T_Terms.Term = heixin.Term AND
T_Station.StatID = heixin.StatID INNER JOIN
T_Meal ON heixin.MealID = T_Meal.MealID) l
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/**/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cyz_sum_month_kh]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cyz_sum_month_kh]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE cyz_sum_month_kh
(
@dpcode varchar(7),
@month datetime
)AS
SELECT ISNULL(T_Department.DpName1, '') + ISNULL(T_Department.DpName2, '')
+ ISNULL(T_Department.DpName3, '') AS 部门, bmkhrc.部门开户人次
FROM (SELECT Account, COUNT(*) AS 部门开户人次
FROM (SELECT *
FROM (SELECT Account, RTRIM(CAST(YEAR(OpenDt) AS char))
+ '-' + RTRIM(CAST(MONTH(OpenDt) AS char))
+ '-' + RTRIM(DAY(0)) AS kh_month
FROM T_Customers) AS kh
WHERE kh_month =@month and account like @dpcode+'%') kh_month
GROUP BY Account) bmkhrc INNER JOIN
T_Department ON SUBSTRING(bmkhrc.Account, 1, 2) = T_Department.DpCode1 AND
SUBSTRING(bmkhrc.Account, 3, 2) = T_Department.DpCode2 AND
SUBSTRING(bmkhrc.Account, 5, 3) = T_Department.DpCode3
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/**/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[bm_k_sum]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[bm_k_sum]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.bm_k_sum
AS
SELECT ISNULL(dbo.T_Department.DpName1, '') + ISNULL(dbo.T_Department.DpName2, '')
+ ISNULL(dbo.T_Department.DpName3, '') AS 部门, oddfare.s_oddfare AS 卡余额总额,
oddfare.cnt AS 卡数
FROM (SELECT Account, SUM(OddFare) AS s_oddfare, COUNT(*) AS cnt
FROM T_Customers
GROUP BY Account) oddfare INNER JOIN
dbo.T_Department ON SUBSTRING(oddfare.Account, 1, 2)
= dbo.T_Department.DpCode1 AND SUBSTRING(oddfare.Account, 3, 2)
= dbo.T_Department.DpCode2 AND SUBSTRING(oddfare.Account, 5, 3)
= dbo.T_Department.DpCode3
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
grant exec on cyz_sum_month to capec
go
grant exec on cyz_sum_month_kh to capec
go
grant select on bm_k_sum to capec
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cyz_department]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[cyz_department]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.cyz_department
AS
SELECT DpCode1 + DpCode2 + DpCode3 AS dpcode, isnull(DpName1,'')+isnull(DpName2,'')+isnull( DpName3,'') as dpname
FROM dbo.T_Department
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
grant select on cyz_department to capec
go
|
|
