--医院跟踪系统客户视图
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View_HospitalCustomer]'))
DROP VIEW [dbo].[View_HospitalCustomer]
GO
CREATE VIEW [dbo].[View_HospitalCustomer]
AS
with t as(
SELECT  
       [ID]
      ,[Hct_Oddnumber]
      ,[Hct_CustomerName]
      ,dbo.[Fun_GetPy]([Hct_CustomerName]) as [Hct_CustomerName_PY]
      ,[Hct_BabyName]
      ,dbo.[Fun_GetPy]([Hct_BabyName]) as [Hct_BabyName_PY]
      ,[Hct_BabySex]
      ,[Hct_BabyBirthday]
      ,[Hct_AreLunar]
      ,dbo.Fun_GetGregorianCalendar([Hct_BabyBirthday],[Hct_AreLunar]) as GregorianCalendar_Birthday
      ,dbo.Fun_GetGregorianCalendarNowYearBirthday([Hct_BabyBirthday],[Hct_AreLunar]) as GregorianCalendar_NowYearBirthday
      ,[Hct_BirthdayType]
      ,[Hct_MessageSingle]
      ,[Hct_CustomerOrders]
      ,[Hct_OrderNumber]
      ,[Hct_BabyFeeding]
      ,[Hct_CustomerRegions]
      ,[Hct_CustomerGroups]
      ,[Hct_DealStatus]
      ,[Hct_SingleHospital]
      ,[Hct_MyselfMobile]
      ,[Hct_HusbandMobile]
      ,[Hct_FamilyPhone]
      ,[Hct_Husbandphone]
      ,[Hct_Maidenphone]
      ,[Hct_QQ]
      ,[Hct_PregnantDay]
      ,[Hct_Retail]
      ,[Hct_Remarks]
      ,[Hct_HomeAddress]
      ,[Hct_HomePostcode]
      ,[Hct_HusbandAddress]
      ,[Hct_HusbandPostcode]
      ,[Hct_MaidenAddress]
      ,[Hct_MaidenPostcode]
      ,[Hct_SingleDatetime]
      ,[Hcr_BusinessType]
      ,[Hct_BusinessIntention]
      ,[Hct_CreateDatetime]
      ,[Hct_CreateName]
      ,[Hct_UpdateDatetime]
      ,[Hct_UpdateName]
  FROM  tb_ErpHospitalClient 
  )
  select 
      [ID]
      ,[Hct_Oddnumber]
      ,[Hct_CustomerName]
      ,[Hct_CustomerName_PY]
      ,[Hct_BabyName]
      ,[Hct_BabyName_PY]
      ,[Hct_BabySex]
      ,[Hct_BabyBirthday]
      ,[Hct_AreLunar]
      ,GregorianCalendar_Birthday
      ,GregorianCalendar_NowYearBirthday
      ,[Hct_BirthdayType]
      ,FLOOR(datediff(day,GregorianCalendar_Birthday,getdate())/365.25) as Age_Year
      ,DATEDIFF(day, GregorianCalendar_Birthday,Getdate()) as Age_Day
      ,dbo.Fun_GetAgeString(GregorianCalendar_Birthday) as Age_String
      ,[Hct_MessageSingle]
      ,[Hct_CustomerOrders]
      ,[Hct_OrderNumber]
      ,[Hct_BabyFeeding]
      ,[Hct_CustomerRegions]
      ,[Hct_CustomerGroups]
      ,[Hct_DealStatus]
      ,[Hct_SingleHospital]
      ,[Hct_MyselfMobile]
      ,[Hct_HusbandMobile]
      ,[Hct_FamilyPhone]
      ,[Hct_Husbandphone]
      ,[Hct_Maidenphone]
      ,[Hct_QQ]
      ,[Hct_PregnantDay]
      ,[Hct_Retail]
      ,[Hct_Remarks]
      ,[Hct_HomeAddress]
      ,[Hct_HomePostcode]
      ,[Hct_HusbandAddress]
      ,[Hct_HusbandPostcode]
      ,[Hct_MaidenAddress]
      ,[Hct_MaidenPostcode]
      ,[Hct_SingleDatetime]
      ,[Hcr_BusinessType]
      ,[Hct_BusinessIntention]
      ,[Hct_CreateDatetime]
      ,[Hct_CreateName]
      ,[Hct_UpdateDatetime]
      ,[Hct_UpdateName]
   from t

GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetHospitalTrackingType]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_GetHospitalTrackingType]
GO
create function [dbo].[fn_GetHospitalTrackingType] (@FID int)
Returns varchar(50)
As
Begin
Declare @ReturnValue varchar(50) 
	if @FID = 0
		set @ReturnValue='宝宝生日'
	else 
		set @ReturnValue= '预产期'
		 return @ReturnValue
End
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetHospitalTrackingDoor]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_GetHospitalTrackingDoor]
GO
create function [dbo].[fn_GetHospitalTrackingDoor] (@FID int)
Returns varchar(50)
As
Begin
Declare @ReturnValue varchar(50) 
	if @FID = 1
	set @ReturnValue='OK'
	else 
	set @ReturnValue=''
	 return @ReturnValue
End
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View_HospitalContact]'))
DROP VIEW [dbo].[View_HospitalContact]
GO
CREATE view [dbo].[View_HospitalContact]
as
select
id,Hct_Oddnumber as 来单批号,
Hct_CustomerName as 客户姓名,
Hct_BabyName as 宝宝姓名,
Hct_BabySex as 宝宝性别,
dbo.fn_GetHospitalTrackingType(Hct_BirthdayType) 生日类型,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Hct_BabyBirthday)) as [宝宝生日/预产期],
FLOOR(datediff(day,Hct_BabyBirthday,getdate())/365.25) as 宝宝年龄,
DATEDIFF(day, Hct_BabyBirthday,Getdate()) as 宝宝天数,
dbo.fn_CheckLunar(Hct_AreLunar) as 是否农历,
Hct_PregnantDay as 怀孕天数,
Hct_MyselfMobile as 本人手机,
Hct_HusbandMobile as 老公手机,
Hct_MessageSingle as 发送来单短信,
Hct_BabyFeeding as 喂养方式,
Hct_CustomerRegions as 客户区域,
Hct_CustomerGroups as 客户类别,
Hct_CustomerOrders as 订单客户,
Hcr_BusinessType as 业务种类,
Hct_BusinessIntention as 业务意向,
Hct_SingleHospital as 来单医院,
Hct_Retail as 门市,
Hct_Remarks as 备注,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Hct_CreateDatetime)) as 录入时间,
Hct_CreateDatetime as 录入时间查询
from tb_ErpHospitalClient
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View_HospitalDispatchDoor]'))
DROP VIEW [dbo].[View_HospitalDispatchDoor]
GO
CREATE view [dbo].[View_HospitalDispatchDoor]
as
select
tb_ErpHospitalDispatchDoor.id,
Hct_Oddnumber as 来单批号,
Hct_CustomerName as 客户姓名,
Hct_BabyName as 宝宝姓名,
FLOOR(datediff(day,Hct_BabyBirthday,getdate())/365.25) as 宝宝年龄,
Hct_MyselfMobile as 本人手机,
Hct_HusbandMobile as 老公手机,
Hct_CustomerRegions as 客户区域,
Hct_BusinessIntention as 业务意向,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Hdr_DoorTime)) as 上门日期,
Hdr_DoorPeriod as 上门时间,
dbo.fn_GetHospitalTrackingDoor(Hdr_HomeState) as 上门状态,
Hdr_Photographers as 摄影师,
Hdr_BootDivision as 引导师,
Hdr_Vehicle as 车辆,
Hdr_Driver as 司机
from
tb_ErpHospitalClient
left join
tb_ErpHospitalDispatchDoor
on Hct_Oddnumber=Hdr_Number
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View_HospitalRemindedToday]'))
DROP VIEW [dbo].[View_HospitalRemindedToday]
GO
CREATE view [dbo].[View_HospitalRemindedToday]
as
select
tb_ErpHospitalContactRecord.id,
Hct_Oddnumber as 来单批号,
Hct_CustomerName as 客户姓名,
Hct_BabyName as 宝宝姓名,
Hct_BabySex as 宝宝性别,
Hct_BirthdayType 生日类型,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Hct_BabyBirthday)) as [宝宝生日/预产期],
FLOOR(datediff(day,Hct_BabyBirthday,getdate())/365.25) as 宝宝年龄,
DATEDIFF(day, Hct_BabyBirthday,Getdate()) as 宝宝天数,
dbo.fn_CheckLunar(Hct_AreLunar) as 是否农历,
Hct_PregnantDay as 怀孕天数,
Hct_MyselfMobile as 本人手机,
Hct_HusbandMobile as 老公手机,
Hct_MessageSingle as 发送来单短信,
Hct_BabyFeeding as 喂养方式,
Hct_CustomerRegions as 客户区域,
Hct_CustomerGroups as 客户类别,
Hct_CustomerOrders as 订单客户,
Hcr_BusinessType as 业务种类,
Hct_BusinessIntention as 业务意向,
Hct_SingleHospital as 来单医院,
Hct_Retail as 门市,
Hct_Remarks as 备注,
dbo.fn_CheckUserIDGetUserName(Hct_CreateName) as 录入员,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Hcr_CallTime)) as 通话时间,
Hcr_CallDuration as 通话时长,
Hcr_CallType as 通话类型,
Hcr_Homeaddress as 具体事项,
Hcr_CallStaff as 通话人员,
Hcr_CallCases as 通话详情,
Hcr_CallMode as 通话方式,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Hcr_RemindTime)) as 提醒时间,
Hcr_RemindTime as 提醒时间查询,
Hcr_Remarks as 沟通备注
from
tb_ErpHospitalClient
left join
tb_ErpHospitalContactRecord
on Hct_Oddnumber=Hcr_Number
where Hcr_RemindTime<>''

GO