XiXianDC_ZhuBiao_API/sql/sql_20180503...

USE [CallCenter_XiXianDC]
GO
/****** Object:  StoredProcedure [dbo].[DealWorkOrder]    Script Date: 05/03/2018 10:20:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[DealWorkOrder]
(
  @orderid VARCHAR(50),--工单编号
  @isend INT,--是否结束
  @cont NVARCHAR(MAX),--处理内容
  @jsbm INT,--接收部门ID
  @jsgh VARCHAR(50),--接收人工号（多个用,隔开）
  @clgh VARCHAR(MAX),--处理人工号
  @IsAudit int,--0未审核，1无效，2有效
  @InfoTypeID int,--信息分类ID
  @InfoType VARCHAR(50),--信息分类
  @UnitID int,--交办单位ID
  @Unit varchar(50) --交办单位
)
AS
BEGIN TRAN clgd
BEGIN TRY
   DECLARE @tran_error INT=0--错误

   DECLARE @gd_id VARCHAR(50)--工单ID
   DECLARE @gd_type INT--工单类型
   DECLARE @gd_state INT--工单状态
   DECLARE @gd_user VARCHAR(50)--工单创建人工号
   DECLARE @gd_time DECIMAL--工单用时时间
   DECLARE @gd_limt DECIMAL--工单超时时间
   DECLARE @gd_iscs INT=0--工单是否超时
   DECLARE @mx_id VARCHAR(50)--工单明细ID
   DECLARE @mx_state INT--工单明细状态
   DECLARE @mx_user VARCHAR(50)--工单明细创建人工号
   DECLARE @mx_sureuser VARCHAR(50)--工单明细接单人工号
   DECLARE @mx_time DECIMAL--工单明细创建时间
   DECLARE @mx_limt DECIMAL--工单明细超时时间
   DECLARE @mx_iscs INT=0--工单明细是否超时

   DECLARE @limttime DECIMAL--明细限制时间

   BEGIN
      SELECT @gd_id=ISNULL(ID, 0),@gd_type=ISNULL(Type, 0),@gd_state=State,@gd_user=CreateUser,@gd_limt=LimitTime,@gd_time=convert(decimal(18,2),DATEDIFF(minute, CreateTime,  GETDATE())/60.00) FROM T_Wo_WorkOrder WHERE WorkOrderID=@orderid AND IsDel=0
      SELECT @limttime=ItemTime FROM T_Wo_WorkOrderTimeOut WHERE Type=@gd_type
      IF @gd_id<>0 and @gd_state=1--存在此工单并且处理中
        BEGIN
          IF ISNULL(@gd_limt, 0)<>0 AND @gd_limt>@gd_limt--超时
             BEGIN
               SET @gd_iscs=1
             END

           --获取最新的流程明细
           SELECT @mx_id=ISNULL(mx.ID, 0),@mx_state=State,@mx_user=CreateUser,@mx_sureuser=SureUser,@mx_limt=LimitTime,@mx_time= convert(decimal(18,2),DATEDIFF(minute, CreateTime,  GETDATE())/60.00) 
           FROM( SELECT TOP 1 * FROM T_Wo_WorkOrderItem WHERE WorkOrderID=@orderid AND IsDel=0 AND Type=1 ORDER BY CreateTime DESC) mx
           IF @mx_id<>0 and @mx_state=1 and @mx_sureuser=@clgh--当前明细是否接单状态并且是否当前人接单
              BEGIN
                IF ISNULL(@mx_limt, 0)<>0 AND @mx_time>@mx_limt--超时
                  BEGIN
                    SET @mx_iscs=1
                  END
                   
                IF @isend=0--转派
                  BEGIN
                    --更新流程
                    UPDATE T_Wo_WorkOrderItem SET Detail=@cont,State=2,DealUser=@clgh,DealTime=GETDATE(),IsTimeOut=@mx_iscs WHERE ID=@mx_id
                    --新增流程并指派
                    INSERT INTO T_Wo_WorkOrderItem(WorkOrderID,Type,ToDept,ToUser,SureUser,SureTime,LimitTime,IsLast,IsStart,IsTimeOut,State,CreateUser,CreateTime,IsDel) VALUES
                    (@orderid,1,@jsbm,@jsgh,@jsgh,GETDATE(),@limttime,0,1,0,1,@clgh,GETDATE(),0)
                   END
                 ELSE IF @isend=1--结单
                   BEGIN
                     --更新流程
                     UPDATE T_Wo_WorkOrderItem SET Detail=@cont,State=2,DealUser=@clgh,DealTime=GETDATE(),IsTimeOut=@mx_iscs,IsLast=1 WHERE ID=@mx_id
                     --更新工单状态
                     UPDATE T_Wo_WorkOrder SET State=2,clcontent=@cont,LastDealUser=@clgh,LastDealTime=GETDATE(),IsTimeOut=@gd_iscs 
						,IsAudit=@IsAudit,InfoType=@InfoType,InfoTypeID=@InfoTypeID ,Unit=@Unit,UnitID=@UnitID
                     WHERE ID=@gd_id
                   END
               END
             ELSE IF @mx_id<>0 and @mx_state=1 and @gd_user=@clgh and @isend=1--创建人结单
               BEGIN
                 --更新流程
                 UPDATE T_Wo_WorkOrderItem SET Detail=@cont,State=2,DealUser=@clgh,DealTime=GETDATE(),IsTimeOut=@mx_iscs,IsLast=1 WHERE ID=@mx_id
                 --更新工单状态
                 UPDATE T_Wo_WorkOrder SET State=2,LastDealUser=@clgh,LastDealTime=GETDATE(),IsTimeOut=@gd_iscs 
					,IsAudit=@IsAudit,InfoType=@InfoType,InfoTypeID=@InfoTypeID ,Unit=@Unit,UnitID=@UnitID
                 WHERE ID=@gd_id
               END
          END
        ELSE IF @gd_id<>0 and @gd_state=0--存在此工单并且未指派
          BEGIN
            IF @isend=0--转派
              BEGIN
                --新增流程并指派
                INSERT INTO T_Wo_WorkOrderItem(WorkOrderID,Type,ToDept,ToUser,SureUser,SureTime,LimitTime,IsLast,IsStart,IsTimeOut,State,CreateUser,CreateTime,IsDel) VALUES
                (@orderid,1,@jsbm,@jsgh,@jsgh,GETDATE(),@limttime,0,1,0,1,@clgh,GETDATE(),0)
                --更新工单状态
                UPDATE T_Wo_WorkOrder SET State=1,Answer=@cont,AppointTime=GETDATE() 
					,IsAudit=@IsAudit,InfoType=@InfoType,InfoTypeID=@InfoTypeID ,Unit=@Unit,UnitID=@UnitID
                WHERE ID=@gd_id
              END
            ELSE IF @isend=1--结单
              BEGIN
                --新增流程
                INSERT INTO T_Wo_WorkOrderItem(WorkOrderID,Type,Detail,SureUser,SureTime,LimitTime,IsLast,IsStart,IsTimeOut,State,CreateUser,CreateTime,IsDel) VALUES
                (@orderid,1,@cont,@jsgh,GETDATE(),@limttime,1,0,0,2,@clgh,GETDATE(),0)
                --更新工单状态
                UPDATE T_Wo_WorkOrder SET State=2,Answer=@cont,clcontent=@cont,LastDealUser=@clgh,LastDealTime=GETDATE(),IsTimeOut=@gd_iscs 
					,IsAudit=@IsAudit,InfoType=@InfoType,InfoTypeID=@InfoTypeID ,Unit=@Unit,UnitID=@UnitID
                WHERE ID=@gd_id
              END
          END
   END
END TRY
BEGIN CATCH
   SET @tran_error=@tran_error+1
END CATCH
IF @tran_error>0
   BEGIN 
      ROLLBACK TRAN clgd
      RETURN 0
   END
ELSE
   BEGIN
      COMMIT TRAN clgd
      RETURN 1
   END