/*
        版本更新：
        2011-11-25 创建初始版本
        2012-01-06 新增report_dictator，report_diagnostician，report_approver的拆分
*/

CREATE PROCEDURE sp_SplitFieldValue
        @SourceTable varchar(64),               -- 要拆分的表
        @SplitField varchar(128),               -- 要拆分的字段
        @ModalityType  varchar(64),             -- 产品类别
        @StatStartDttm datetime,                -- 开始时间
        @StatEndDttm datetime                   -- 结束时间
AS
BEGIN

-- 定义临时变量
DECLARE @sModalityType varchar(18)
DECLARE @iStudyKey int
DECLARE @sStatDttm datetime 
DECLARE @sStatGuid varchar(128)
DECLARE @sStatName varchar(128)
DECLARE @sStatType varchar(18)
DECLARE @sStatKind varchar(18)
DECLARE @sStatItem varchar(18)
DECLARE @iStatOrdinal int
DECLARE @iStatState int

DECLARE @crStudy CURSOR

DECLARE @sSplitField varchar(128)
DECLARE @iPos int
DECLARE @ilen int

/*
        study/task与cc_statist字段对应关系：
============================================================
        study.study_modalitytype -> modality_type
        
        study.study_key         -> study_key
        study.report_dttm       -> statist_dttm
        study.study_guid        -> statist_guid

        study.study_examname    -> statist_name
        task.task_name          -> statist_name

        study.patient_type      -> statist_type

        study.study_examkind    -> statist_kind
        study.task_kind         -> statist_kind

        拆分后的字段值          -> statist_item

        暂无对应字段            -> statist_base
        暂无对应字段            -> statist_ratio
        暂无对应字段            -> statist_count
        暂无对应字段            -> statist_charge
        暂无对应字段            -> statist_result

        study.login_ordinal     -> statist_ordinal
        task.task_sequence      -> statist_ordinal
        
        study.report_state      -> statist_state
============================================================
*/

SET NOCOUNT ON

-- 准备查询语句
IF @SourceTable = 'Task' AND @SplitField = 'task_content' 
BEGIN           
        SET @crStudy = CURSOR FOR
        SELECT study_modalitytype, a.study_key, report_dttm, study_guid, task_name, patient_type, task_kind, task_content, task_sequence, report_state
                FROM study a LEFT JOIN task b ON a.study_key = b.study_key 
                WHERE study_modalitytype = @ModalityType AND report_dttm >= @StatStartDttm AND report_dttm <= @StatEndDttm
END
        
ELSE IF @SourceTable = 'Study' AND @SplitField = 'study_bodypart'
BEGIN   
        SET @crStudy = CURSOR FOR
        SELECT study_modalitytype, study_key, report_dttm, study_guid, study_examname, patient_type, study_examkind, study_bodypart, login_ordinal, report_state
                FROM study
                WHERE study_modalitytype = @ModalityType AND report_dttm >= @StatStartDttm AND report_dttm <= @StatEndDttm
END

ELSE IF @SourceTable = 'Study' AND @SplitField = 'study_bodyitem'
BEGIN   
        SET @crStudy = CURSOR FOR
        SELECT study_modalitytype, study_key, report_dttm, study_guid, study_examname, patient_type, study_examkind, study_bodyitem, login_ordinal, report_state
                FROM study
                WHERE study_modalitytype = @ModalityType AND report_dttm >= @StatStartDttm AND report_dttm <= @StatEndDttm
END


ELSE IF @SourceTable = 'Study' AND @SplitField = 'study_examname'
BEGIN   
        SET @crStudy = CURSOR FOR
        SELECT study_modalitytype, study_key, report_dttm, study_guid, study_examname, patient_type, study_examkind, study_examname, login_ordinal, report_state
                FROM study
                WHERE study_modalitytype = @ModalityType AND report_dttm >= @StatStartDttm AND report_dttm <= @StatEndDttm
END

ELSE IF @SourceTable = 'Study' AND @SplitField = 'study_examcode'
BEGIN   
        SET @crStudy = CURSOR FOR
        SELECT study_modalitytype, study_key, report_dttm, study_guid, study_examname, patient_type, study_examkind, study_examcode, login_ordinal, report_state
                FROM study
                WHERE study_modalitytype = @ModalityType AND report_dttm >= @StatStartDttm AND report_dttm <= @StatEndDttm
END

ELSE IF @SourceTable = 'Study' AND @SplitField = 'report_dictator'
BEGIN   
        SET @crStudy = CURSOR FOR
        SELECT study_modalitytype, study_key, report_dttm, study_guid, study_examname, patient_type, study_examkind, report_dictator, login_ordinal, report_state
                FROM study
                WHERE study_modalitytype = @ModalityType AND report_dttm >= @StatStartDttm AND report_dttm <= @StatEndDttm
END

ELSE IF @SourceTable = 'Study' AND @SplitField = 'report_diagnostician'
BEGIN   
        SET @crStudy = CURSOR FOR
        SELECT study_modalitytype, study_key, report_dttm, study_guid, study_examname, patient_type, study_examkind, report_diagnostician, login_ordinal, report_state
                FROM study
                WHERE study_modalitytype = @ModalityType AND report_dttm >= @StatStartDttm AND report_dttm <= @StatEndDttm
END

ELSE IF @SourceTable = 'Study' AND @SplitField = 'report_approver'
BEGIN   
        SET @crStudy = CURSOR FOR
        SELECT study_modalitytype, study_key, report_dttm, study_guid, study_examname, patient_type, study_examkind, report_approver, login_ordinal, report_state
                FROM study
                WHERE study_modalitytype = @ModalityType AND report_dttm >= @StatStartDttm AND report_dttm <= @StatEndDttm
END

ELSE
	RETURN


-- 执行拆分操作
OPEN @crStudy

FETCH NEXT FROM @crStudy 
INTO @sModalityType, @iStudyKey, @sStatDttm, @sStatGuid, @sStatName, @sStatType, @sStatKind, @sSplitField, @iStatOrdinal, @iStatState 

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sSplitField=RTRIM(LTRIM(@sSplitField))  
    IF LEN(@sSplitField) > 0
    BEGIN
          SET @iPos = CHARINDEX(' ', @sSplitField)          
          WHILE @iPos >= 1       
          BEGIN    
                SET @sStatItem = SUBSTRING(@sSplitField, 1, @iPos-1)    

                INSERT INTO cc_statist (modality_type, study_key, statist_dttm, statist_guid, statist_name, statist_type, statist_kind, statist_item, statist_ordinal, statist_state)
                        VALUES(@sModalityType, @iStudyKey, @sStatDttm, @sStatGuid, @sStatName, @sStatType, @sStatKind, @sStatItem, @iStatOrdinal, @iStatState)

                SET @iLen = LEN(@sSplitField)  
                SET @sSplitField = SUBSTRING(@sSplitField, @iPos+1, @iLen)  
                SET @sSplitField = RTRIM(LTRIM(@sSplitField))  
                SET @iPos = CHARINDEX(' ', @sSplitField)  
          END
        
                INSERT INTO cc_statist (modality_type, study_key, statist_dttm, statist_guid, statist_name, statist_type, statist_kind, statist_item, statist_ordinal, statist_state)
                        VALUES(@sModalityType, @iStudyKey, @sStatDttm, @sStatGuid, @sStatName, @sStatType, @sStatKind, @sSplitField, @iStatOrdinal, @iStatState)
    END
        
    FETCH NEXT FROM @crStudy 
    INTO @sModalityType, @iStudyKey, @sStatDttm, @sStatGuid, @sStatName, @sStatType, @sStatKind, @sSplitField, @iStatOrdinal, @iStatState 
END       

CLOSE @crStudy     
DEALLOCATE @crStudy 

END -- for ALL
