USE [DB_JP_BaseInfo00]

GO

/****** Object:  StoredProcedure [dbo].[sp_wx_getAppointmentInfo_Str]    Script Date: 03/22/2016 14:48:24 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        <Author,,Name>

-- Create date: <Create Date,,>

-- Description:    <Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[sp_wx_getAppointmentInfo_Str]

    @CoachID varchar(4000),

    @SearchTime datetime

AS

BEGIN

     DECLARE @temflag int         --结果

    Declare @TotalCount int      --某天某个号的预约次数

    declare @tem_shang int

    declare @tem_xia int

    declare @i int             --循环变量

    declare @count int         --循环次数

    declare @Str nvarchar(max) --查询字符串

    declare @BeginTime_tem datetime

    declare @EndTime_Tem datetime

    declare @UserLimit_tem int

    declare @NoAppointmentConfig int --每个配置号对应的ID

   set @tem_shang=0

   set @tem_xia=0

   set @i=0

   select @count=count(*) from [CoachAppointmentTime] where CodeID=@CoachID;--查找教练的所有配置

      while @i<@count  --循环读取配置

    begin

    set @Str='select top 1 @NoAppointmentConfig=ID, @BeginTime_tem=Begintime,@EndTime_Tem=Endtime ,@UserLimit_tem=UserLimit from CoachAppointmentTime where ID not in (select top '+Str(@i) +' ID from CoachAppointmentTime order by ID asc) order by ID asc';--从临时表中获取

    EXEC sp_executesql @Str,N'@BeginTime_tem datetime output,@EndTime_Tem datetime output,@UserLimit_tem int output,@NoAppointmentConfig int output',@BeginTime_tem output,@EndTime_Tem output,@UserLimit_tem output,@NoAppointmentConfig output

    select @TotalCount=count(*) from StudentAppointmentTime where AppointmentDate=@SearchTime  and AppointmentTimeId=@NoAppointmentConfig --某天某个号的预约次数

    if(@UserLimit_tem>@TotalCount)

    begin

        if(@BeginTime_tem> '1900-01-01 12:00:00.000')

        set @tem_xia=1

    else if(@EndTime_Tem<'1900-01-01 12:00:00.000')

        set @tem_shang=1

    else

    begin

    set @tem_xia=1;

    set @tem_shang=1;

    end

    end

    set @i=@i+1

    end

    if(@tem_shang=1 and @tem_xia=1)

    set @temflag=3

    else if(@tem_shang=1 and @tem_xia=0)

    set @temflag=1

    else if(@tem_shang=0 and @tem_xia=1)

    set @temflag=2

    --3 表示 上午和下午   1表示上午 2表示下午

    RETURN @temflag

END