海业工时报表 - Powered by MinDoc

一

USE [Udit.SFC]
GO

/****** Object:  View [dbo].[SMT_RPT]    Script Date: 2024/3/27 23:54:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[SMT_RPT] AS 

SELECT 
      (SELECT [ShortName]  FROM [Udit.Sys].[dbo].[Sys_Resource] where [Code] =(SELECT [parentCode]  FROM [Udit.Sys].[dbo].[Sys_Resource] where [Code] = a.[STATION_ID])) as 线体
      ,[LOT_NAME] 工单
      ,[MODEL_NO] 料号
      ,b.[NAME] 工序
      ,[START_TIME]
      ,(select DATEPART(HOUR, [START_TIME])) as 时段
      ,(SELECT [ResourceId]  FROM [Udit.Sys].[dbo].[Sys_Resource] where [Code] =(SELECT [parentCode]  FROM [Udit.Sys].[dbo].[Sys_Resource] where [Code] = a.[STATION_ID])) as 线体id
      ,[WIP_STATE]
      ,[SERIAL_NUMBER]
      --,[TableName]
      --,[WIP_ID]
   --   ,[SERIAL_NUMBER_ID]
   --,a.[STATION_ID]
      --,a.[ROUTE_STEP_ID],[NEXT_STEP_ID],[WIP_STATE],[SCAN_TYPE],[SERIAL_NUMBER],[XUSER],[WID],[END_TIME]
  FROM [Udit.SFC].[dbo].[QS_WIP] a
  join [Udit.SFC].[dbo].[QS_ROUTE_STEPS] b on b.[ROUTE_STEP_ID]=a.[ROUTE_STEP_ID]
  WHERE DATEDIFF(HOUR, 0, [START_TIME]) =(SELECT DATEDIFF(HOUR, 0, GETDATE())) -1 or 
  DATEDIFF(HOUR, 0, [START_TIME]) =(SELECT DATEDIFF(HOUR, 0, GETDATE()))
  --order by [START_TIME] asc
GO

二

USE [Udit.SFC]
GO

/****** Object:  View [dbo].[SMT_RPT1]    Script Date: 2024/3/27 23:54:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[SMT_RPT1] AS 
SELECT [线体]
      ,CASE 
         WHEN 时段>=8 and 时段<20 THEN '早班' 
         ELSE '晚班'
       END AS 班次 
      ,CASE
        WHEN 时段>=8 and 时段<=20 THEN (SELECT [UserName]  FROM [Udit.Sys].[dbo].[sys_user] where [UserCode]=(SELECT [LEADER]  FROM [Udit.SFC].[dbo].[CT_LINES_ATTRIBUTES] where [SHIFT]='早班'and [LINE_ID]=[线体id]))
        ELSE (SELECT [UserName]  FROM [Udit.Sys].[dbo].[sys_user] where [UserCode]=(SELECT [LEADER]  FROM [Udit.SFC].[dbo].[CT_LINES_ATTRIBUTES] where [SHIFT]='晚班'and [LINE_ID]=[线体id]))
        END AS 人员
      ,[工单]
      --,(SELECT [PRODUCT_DESC]  FROM [Udit.SFC].[dbo].[QS_PRODUCTS]  where  [NAME]=[料号]) as 品名
      ,[料号]
      ,[工序]
      ,[START_TIME]
      --,[线体id]
      ,[WIP_STATE]
      ,[SERIAL_NUMBER]
      ,时段
  FROM [Udit.SFC].[dbo].[SMT_RPT]
  where [工序] in('回流焊','功能测试','AOI')
GO

USE [Udit.SFC]
GO

/****** Object:  View [dbo].[SMT_lot_view]    Script Date: 2024/3/28 0:15:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[SMT_lot_view] AS 

SELECT [LOT_NAME] 工单
      ,[QUANTITY] 工单数量
      ,b.[NAME] 产品料号
      ,b.[PRODUCT_DESC] 产品品名
      ,(SELECT MAX([ATTRIBUTE_VALUE])  FROM [Udit.SFC].[dbo].[SYS_STEP_ATTRIBUTES] where [ATTRIBUTE_NAME]='workHour' and [PRODUCT_ID]=a.[PRODUCT_ID]  and [LOT_ID]=a.[LOT_ID]) as workHour
      --[PRODUCT_ID],[LOT_ID]
  FROM [Udit.SFC].[dbo].[QS_LOTS] a 
  join [Udit.SFC].[dbo].[QS_PRODUCTS] b on b.[PRODUCT_ID]=a.[PRODUCT_ID]
GO

三

USE [Udit.SFC]
GO

/****** Object:  View [dbo].[SMT_RPT_OK]    Script Date: 2024/3/27 23:54:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[SMT_RPT_OK] AS 
SELECT a.[线体]
      ,a.[班次]
      ,a.[人员]
      ,a.[工单]
      ,b.[产品品名]
      ,a.[料号]
      ,b.[工单数量]
      ,CASE 
         WHEN CAST([workHour] AS FLOAT)=0 THEN 0 
         ELSE ROUND(b.[工单数量]* CAST([workHour] AS FLOAT)/ 3600, 0, 1)
       END AS 预计用时 
      --,ROUND(b.[工单数量]* CAST([workHour] AS FLOAT)/ 3600, 0, 1) as 预计用时
      ,CASE 
         WHEN CAST([workHour] AS FLOAT)=0 THEN 0 
         ELSE ROUND(3600 / CAST([workHour] AS FLOAT), 0, 1)
       END AS 标准产能
      --, ROUND(3600 / CAST([workHour] AS FLOAT), 0, 1) as 标准产能
      --,[workHour]
      --,'' as 不良数
      --,'' as 达成率
      --,'' as 不良率
      ,a.[工序]
      ,a.[START_TIME]
      ,a.[WIP_STATE]
      ,a.[SERIAL_NUMBER]
      ,CONVERT(DATE, a.[START_TIME], 23) AS rptDate
      ,时段
      ,CASE 
         WHEN CAST([workHour] AS FLOAT)=0 THEN '维护错误' 
         ELSE ''
       END AS [备注]
  FROM [Udit.SFC].[dbo].[SMT_RPT1] a
  join [Udit.SFC].[dbo].[SMT_lot_view] b on a.[工单]=b.[工单] and a.[料号]=b.[产品料号]
  --where [workHour] <> '0' and [workHour] <> '0.0'
GO

四

USE [Udit.SFC]
GO

/****** Object:  View [dbo].[SMT_RPT_时段产出new]    Script Date: 2024/3/27 22:32:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[SMT_RPT_时段产出new] AS 
SELECT [线体], [班次], [人员], [工单], [产品品名], [料号], [工单数量], [预计用时],[标准产能],[rptDate],[时段],[备注],[工序],
SUM(CASE WHEN WIP_STATE = 0 THEN 1 ELSE 0 END) AS 实际产出,SUM(CASE WHEN WIP_STATE = 1 THEN 1 ELSE 0 END) AS 不良数
,CASE 
    WHEN [时段]=0 THEN '00:00-01:00'
    WHEN [时段]=1 THEN '01:00-02:00'
    WHEN [时段]=2 THEN '02:00-03:00'
    WHEN [时段]=3 THEN '03:00-04:00'
    WHEN [时段]=4 THEN '04:00-05:00'
    WHEN [时段]=5 THEN '05:00-06:00'
    WHEN [时段]=6 THEN '06:00-07:00'
    WHEN [时段]=7 THEN '07:00-08:00'
    WHEN [时段]=8 THEN '08:00-09:00'
    WHEN [时段]=9 THEN '09:00-10:00'
    WHEN [时段]=10 THEN '10:00-11:00'
    WHEN [时段]=11 THEN '11:00-12:00'
    WHEN [时段]=12 THEN '12:00-13:00'
    WHEN [时段]=13 THEN '13:00-14:00'
    WHEN [时段]=14 THEN '14:00-15:00'
    WHEN [时段]=15 THEN '15:00-16:00'
    WHEN [时段]=16 THEN '16:00-17:00'
    WHEN [时段]=17 THEN '17:00-18:00'
    WHEN [时段]=18 THEN '18:00-19:00'
    WHEN [时段]=19 THEN '19:00-20:00'
    WHEN [时段]=20 THEN '20:00-21:00'
    WHEN [时段]=21 THEN '21:00-22:00'
    WHEN [时段]=22 THEN '22:00-23:00'
    WHEN [时段]=23 THEN '23:00-24:00' 
    ELSE '' 
END AS 生产时段 
FROM [Udit.SFC].[dbo].[SMT_RPT_OK]
GROUP BY [线体], [班次], [人员], [工单], [产品品名], [料号], [工单数量], [预计用时], [标准产能],[rptDate],[时段],[备注],[工序]

GO

五

USE [Udit.SFC]
GO
/****** Object:  View [dbo].[SMT_RPT_工时报表]    Script Date: 2024/3/27 22:31:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[SMT_RPT_工时报表] AS 
SELECT [rptDate] as 日期,[时段],[线体]
      ,[班次]
      ,[人员]
      ,[工单]
      ,[产品品名]
      ,[料号]
      ,[工单数量]
      ,[预计用时]
      ,[生产时段]
      ,[标准产能]
      ,[工序]
      ,[实际产出]
      ,[不良数]
      ,[备注]
      ,CASE
           WHEN [标准产能] = 0 THEN 0
           ELSE CAST(ROUND([实际产出]/[标准产能], 2) AS DECIMAL(10, 2))
       END AS [达成率]
       ,CASE
           WHEN ([不良数]+[标准产能]) = 0 THEN 0
           ELSE CAST(ROUND([不良数]/([不良数]+[标准产能]), 4) AS DECIMAL(10, 4))
        END AS [不良率]
      ,'' as 总达成率
  FROM [Udit.SFC].[dbo].[SMT_RPT_时段产出new]
Go

六

USE [Udit.SFC]
GO

/****** Object:  View [dbo].[SMT_RPT_工时报表]    Script Date: 2024/3/28 0:01:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[SMT_RPT_工时报表] AS 

SELECT [rptDate] as 日期,[时段],[线体]
      ,[班次]
      ,[人员]
      ,[工单]
      ,[产品品名]
      ,[料号]
      ,[工单数量]
      ,[预计用时]
      ,[生产时段]
      ,[标准产能]
      ,[工序]
      ,[实际产出]
      ,[不良数]
      ,[备注]
      ,CASE
           WHEN [标准产能] = 0 THEN 0
           ELSE CAST(ROUND([实际产出]/[标准产能], 2) AS DECIMAL(10, 2))
       END AS [达成率]
       ,CASE
           WHEN ([不良数]+[标准产能]) = 0 THEN 0
           ELSE CAST(ROUND([不良数]/([不良数]+[标准产能]), 4) AS DECIMAL(10, 4))
        END AS [不良率]
      ,'' as 总达成率
  FROM [Udit.SFC].[dbo].[SMT_RPT_时段产出new]
GO

七

USE [Udit.SFC]
GO

/****** Object:  StoredProcedure [dbo].[SYNC_SMT_RPT_Hours]    Script Date: 2024/3/28 0:00:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[SYNC_SMT_RPT_Hours] 
as
begin
 INSERT INTO SMT_RPT_Hours
SELECT *,GETDATE()
FROM [Udit.SFC].[dbo].[SMT_RPT_工时报表] -- 或者是指定某个其他表，根据实际情况来定
WHERE [时段]=(select DATEPART(HOUR, GETDATE()))-1
--WHERE [条件]
end

GO

八

USE [Udit.SFC]
GO

/****** Object:  Table [dbo].[SMT_RPT_Hours]    Script Date: 2024/3/28 0:04:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[SMT_RPT_Hours](
    [日期] [varchar](255) NULL,
    [时段] [varchar](255) NULL,
    [线体] [varchar](255) NULL,
    [班次] [varchar](255) NULL,
    [人员] [varchar](255) NULL,
    [工单] [varchar](255) NULL,
    [产品品名] [varchar](255) NULL,
    [料号] [varchar](255) NULL,
    [工单数量] [varchar](255) NULL,
    [预计用时] [varchar](255) NULL,
    [生产时段] [varchar](255) NULL,
    [标准产能] [varchar](255) NULL,
    [工序] [varchar](255) NULL,
    [实际产出] [varchar](255) NULL,
    [不良数] [varchar](255) NULL,
    [备注] [varchar](255) NULL,
    [达成率] [varchar](255) NULL,
    [不良率] [varchar](255) NULL,
    [总达成率] [varchar](255) NULL,
    [记录时间] [datetime] NULL
) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

作者：潘茂盛创建时间：2024-03-27 23:53
最后编辑：潘茂盛更新时间：2024-06-27 17:16