SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UF_CSVToTable]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
CREATE FUNCTION [dbo].[UF_CSVToTable]
(
 @psCSString VARCHAR(8000)
)
RETURNS @otTemp TABLE(sID VARCHAR(20))
AS
BEGIN
 DECLARE @sTemp VARCHAR(10)

 WHILE LEN(@psCSString) > 0
 BEGIN
  SET @sTemp = LEFT(@psCSString, ISNULL(NULLIF(CHARINDEX('','', @psCSString) - 1, -1),
                    LEN(@psCSString)))
  SET @psCSString = SUBSTRING(@psCSString,ISNULL(NULLIF(CHARINDEX('','', @psCSString), 0),
                               LEN(@psCSString)) + 1, LEN(@psCSString))
  INSERT INTO @otTemp VALUES (@sTemp)
 END

RETURN
END
' 
END


SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spDeleteJob]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE Procedure [dbo].[spDeleteJob] @JobSrNo float
As
Delete from CorrectiveAction where JobSerialNo = @JobSrNo
Delete from NonConformities where JobSerialNo = @JobSrNo
Delete from JobDisposal where JobSerialNo = @JobSrNo
Delete from OutOfSpecJobReading where JobSerialNo = @JobSrNo
Delete from Jobparameter where JobSerialNo = @JobSrNo
Delete from Job Where JobSerialNo =@JobSrNo
Delete from JobReading where JobSerialNo = @JobSrNo
Delete from JobSample where JobSerialNo = @JobSrNo
' 
END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spJobCloseHistCorr]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
Create Procedure [dbo].[spJobCloseHistCorr] @JobSerialNo float, @JobStartDate varchar(15),
@JobStartTime varchar(15)
As
Insert into HistCorrectiveAction (JobStartDate,TargetDateToComplete,JobStartTime,ResponsibilityAssignedDate,CorrectiveActionTakenDate,WorkOrderDate,JobSerialNo,SampleNo,ResponsibilityAssignedTo,ResponsibilityAssignedBy,PartParaCode,DisposalActionCode,CorrectiveAction,CorrectiveActionTakenBy,RootCause,PartNo,WorkOrderNo,ProcessCode,CorrectiveActionSrlNo,ResponsibilityDomain,RemarksForCompOfCa,SampleDate,SampleTime,MachineNo,StationNo,BatchNo,Levels,Trend,CorrectiveActionTakenTime,Manned,TrendSampleNos) select JobStartDate,TargetDateToComplete,JobStartTime,NULL,CorrectiveActionTakenDate,WorkOrderDate,JobSerialNo,SampleNo,ResponsibilityAssignedTo,NULL,PartParaCode,NULL,CorrectiveAction,CorrectiveActionTakenBy,RootCause,PartNo,WorkOrderNo,ProcessCode,NULL,NULL,RemarksForCompOfCa,SampleDate,SampleTime,MachineNo,StationNo,BatchNo,Levels,Trend,CorrectiveActionTakenTime,Manned,TrendSampleNos from CorrectiveAction where JobSerialNo = @JobSerialNo and JobStartDate = @JobStartDate and JobStartTime = @JobStartTime
' 
END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spJobCloseHistParam]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'Create Procedure [dbo].[spJobCloseHistParam] @partno varchar(16),@JobStartDate varchar(15),
@JobStartTime varchar(15),@workorderno varchar(30),
@WorkOrderDate varchar(15),@processcode varchar(4),@JobSerialNo float,@MachineNo varchar(15),
@BatchNo varchar(8),@serverid varchar(15)
As
Insert InTo HistoryJobParameter(WorkorderNo,BatchDate,StationNo,Serverid,  WorkOrderDate,
PartNo,ProcessCode,QapProcessSeqNo,JobStartDate,MachineNo ,BatchNo, JobStartTime,
PartParaCode,InstrumentNo,FinalInspectionId,LocationCode,MinLotSize, SampleSetNo,SampleSize,
Cp,Cpk,PP,Ppk,LCLXBar,UCLXBar,LCLR,UCLR,LCLs,UCLS,  SetCpPpkInd,JobSerialno,SetControlLimit,
FinalInspProcCompInd,ReadingMode,  ActiveChannel,ReworkonJobSerialNo,UomCode,InstrumentType,
LSLValue,USLValue, SubGroupSize,PartParaType,FinalInspSetNo,FinalInspectionInd,
CLCalculateSpecifyInd,  CheckSpecLmtInd,UCLRInitValue,LCLRInitValue,NominalValue,
LCLInitXbar,UCLInitXbar,  PrecisionInd,repetativeRecalc,RecalcLimitAfterSamples,
OneSidedInd, CentPercentInspInd,BestSample,NoOfReadingsForCpk,AbsNominalValue,SequenceNo,
Formulae,MappedSequence,InstrumentAddress,ColorStatus,CpkPpkLastSamples,LastCp,LastCpk,LastPp,LastPpk,AckChannelNo) 
Select WorkOrderNo,BatchDate,StationNo,ServerId,WorkOrderDate,PartNo,ProcessCode, 
QapProcessSeqNo,JobStartDate,MachineNo,BatchNo,JobStartTime,PartParaCode,  InstrumentNo,
FinalInspectionId,LocationCode,MinLotSize,SampleSetNo,SampleSize,Cp, 
 Cpk,PP,Ppk,LCLXBar,UCLXBar,LCLR,UCLR,LCLs,UCLS,SetCpPpkInd,JobSerialno,  SetControlLimit,
FinalInspProcCompInd,ReadingMode,ActiveChannel,ReworkonJobSerialNo,  UomCode,InstrumentType,
LSLValue,USLValue,SubGroupSize,PartParaType,FinalInspSetNo,  FinalInspectionInd, 
CLCalculateSpecifyInd, CheckSpecLmtInd, UCLRInitValue,  LCLRInitValue,NominalValue,
LCLInitXbar,UCLInitXbar,PrecisionInd,repetativeRecalc, RecalcLimitAfterSamples,OneSidedInd,
CentPercentInspInd,BestSample, NoOfReadingsForCpk,AbsNominalValue,SequenceNo,Formulae,MappedSequence,
InstrumentAddress,ColorStatus,CpkPpkLastSamples,LastCp,LastCpk,LastPp,LastPpk,AckChannelNo 
From JobParameter where JobSerialNo = @JobSerialNo' 
END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spJobCloseJobR]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE Procedure [dbo].[spJobCloseJobR] @JobSerialNo float, @JobStartDate varchar(15),
@JobStartTime varchar(15)
As
Insert Into HistoryJobReading (ReadingSerialNo,SubGroupSize,JobStartDate, Reading,JobStartTime,ReadingDate,PartParaCode,ReadingTime,SampleNo,JobSerialNo, ShiftCode,ComponentNo,Variation,IntermediateSrlNo,IntermediateSrlDate,RollNo,BatchNo,HomogenizationInd,DateCode,Operator)
Select ReadingSerialNo,SubGroupSize,JobStartDate,Reading,  JobStartTime,ReadingDate,PartParaCode,ReadingTime,SampleNo,JobSerialNo,ShiftCode, ComponentNo,Variation,IntermediateSrlNo,IntermediateSrlDate,RollNo,BatchNo,HomogenizationInd,DateCode,Operator 
From JobReading 
where JobSerialNo = @JobSerialNo' 
END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spRetLastReadingDateTime]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spRetLastReadingDateTime]
@JobSrNo float, @SampleNo float, @ParameterCode nvarchar(50), @History smallint
WITH EXEC AS CALLER
AS
if @History=1
  Select Reading,Variation,ReadingDate,ReadingTime From HistoryJobReading where 
  jobserialno= @JobSrNo and sampleno<=@SampleNo and PartParaCode=@ParameterCode and
  (HomogenizationInd Is Null Or HomogenizationInd=''N'') order by SampleNo Desc,ReadingSerialNo Desc
else
  Select Reading,Variation,ReadingDate,ReadingTime From JobReading where 
  jobserialno= @JobSrNo and sampleno<=@SampleNo and PartParaCode=@ParameterCode and
  (HomogenizationInd Is Null Or HomogenizationInd=''N'') order by SampleNo Desc,ReadingSerialNo Desc' 
END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetReadingss]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spGetReadingss]
@JobSrNo float, @History smallint
WITH EXEC AS CALLER
AS
if @History=1
    Select JR.PartParaCode,JR.SampleNo,JR.ReadingSerialNo,JR.Reading,JR.ReadingDate,
    JR.ReadingTime,JR.BatchNo from HistoryJobReading JR 
    where JR.JobSerialNo= @JobSrNo And (JR.HomogenizationInd Is Null Or JR.HomogenizationInd=''N'')
    order by JR.PartParaCode,JR.SampleNo, JR.ReadingSerialNo
else
    Select JR.PartParaCode,JR.SampleNo,JR.ReadingSerialNo,JR.Reading,JR.ReadingDate,
    JR.ReadingTime,JR.BatchNo from JobReading JR 
    where JR.JobSerialNo= @JobSrNo And (JR.HomogenizationInd Is Null Or JR.HomogenizationInd=''N'')
    order by JR.PartParaCode,JR.SampleNo, JR.ReadingSerialNo' 
END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spMaxJobReading]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spMaxJobReading]
@JobSrNo float, @PartParaCode nvarchar(50), @SampleNo float, @History smallint
WITH EXEC AS CALLER
AS
if @History=1
    Select max(JR.SampleNo) from HistoryJobReading JR
    where  JR.JobSerialNo = @JobSrNo and
    JR.PartParaCode = @PartParaCode and JR.SampleNo>=@SampleNo and 
    (JR.HomogenizationInd Is Null Or JR.HomogenizationInd=''N'')
else
    Select max(JR.SampleNo) from JobReading JR
    where  JR.JobSerialNo = @JobSrNo and
    JR.PartParaCode = @PartParaCode and JR.SampleNo>=@SampleNo and 
    (JR.HomogenizationInd Is Null Or JR.HomogenizationInd=''N'')' 
END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetReadings]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spGetReadings]
@History smallint, @AllJobSrNo nvarchar(200)
WITH EXEC AS CALLER
AS
if @History=1
    Select JR.PartParaCode,JR.SampleNo,JR.ReadingSerialNo,JR.Reading,JR.ReadingDate,
    JR.ReadingTime,JR.BatchNo,JR.JobSerialNo from HistoryJobReading JR 
    where JR.JobSerialNo IN(SELECT * FROM UF_CSVToTable(@AllJobSrNo)) And (JR.HomogenizationInd Is Null Or JR.HomogenizationInd=''N'')
    order by JR.PartParaCode,JR.SampleNo, JR.ReadingSerialNo    
else
    Select JR.PartParaCode,JR.SampleNo,JR.ReadingSerialNo,JR.Reading,JR.ReadingDate,
    JR.ReadingTime,JR.BatchNo,JR.JobSerialNo from JobReading JR 
    where JR.JobSerialNo IN(SELECT * FROM UF_CSVToTable(@AllJobSrNo)) And (JR.HomogenizationInd Is Null Or JR.HomogenizationInd=''N'')
    order by JR.PartParaCode,JR.SampleNo, JR.ReadingSerialNo
    
    
    /*if @History=1
    Select JR.PartParaCode,JR.SampleNo,JR.ReadingSerialNo,JR.Reading,JR.ReadingDate,
    JR.ReadingTime,JR.BatchNo from HistoryJobReading JR 
    where JR.JobSerialNo= @JobSrNo And (JR.HomogenizationInd Is Null Or JR.HomogenizationInd=''N'')
    order by JR.PartParaCode,JR.SampleNo, JR.ReadingSerialNo
else
    Select JR.PartParaCode,JR.SampleNo,JR.ReadingSerialNo,JR.Reading,JR.ReadingDate,
    JR.ReadingTime,JR.BatchNo from JobReading JR 
    where JR.JobSerialNo= @JobSrNo And (JR.HomogenizationInd Is Null Or JR.HomogenizationInd=''N'')
    order by JR.PartParaCode,JR.SampleNo, JR.ReadingSerialNo*/


' 
END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spArrayFillForSelectedParameter]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spArrayFillForSelectedParameter]
@JobSrNo float, @PartParaCode nvarchar(50), @History smallint
WITH EXEC AS CALLER
AS
if @History=1 
    Select JR.PartParaCode,JR.SampleNo,JR.ReadingSerialNo,JR.Reading,JR.ReadingDate, 
    JR.ReadingTime,JR.BatchNo from HistoryJobReading JR where JR.JobSerialNo= @JobSrNo and
    JR.PartParaCode=@PartParaCode And (JR.HomogenizationInd Is Null Or JR.HomogenizationInd=''N'')
    order by JR.SampleNo, JR.ReadingSerialNo
else
    Select JR.PartParaCode,JR.SampleNo,JR.ReadingSerialNo,JR.Reading,JR.ReadingDate, 
    JR.ReadingTime,JR.BatchNo from JobReading JR where JR.JobSerialNo= @JobSrNo and
    JR.PartParaCode=@PartParaCode And (JR.HomogenizationInd Is Null Or JR.HomogenizationInd=''N'')
    order by JR.SampleNo, JR.ReadingSerialNo' 
END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetSampleNoMaxReadingNo]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spGetSampleNoMaxReadingNo]
@JobSrNo float, @PartParaCode nvarchar(50), @SampleNo float, @History smallint
WITH EXEC AS CALLER
AS
if @History=1
    Select max(ReadingSerialNo) from HistoryJobReading where
    JobSerialNo = @JobSrNo and PartParaCode = @PartParaCode and
    SampleNo  = @SampleNo
else
    Select max(ReadingSerialNo) from JobReading where
    JobSerialNo = @JobSrNo and PartParaCode = @PartParaCode and
    SampleNo  = @SampleNo' 
END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetLastReadings]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spGetLastReadings]
@JobSrNo float, @PartParaCode nvarchar(50), @SampleNo float, @ReadingNo smallint, @History smallint
WITH EXEC AS CALLER
AS
if @History=1 
    Select Reading,Variation,ReadingDate,ReadingTime from HistoryJobReading JR
    where  JR.JobSerialNo = @JobSrNo and JR.PartParaCode = @PartParaCode and
    JR.SampleNo =@SampleNo and  JR.ReadingSerialNo = @ReadingNo
else
    Select Reading,Variation,ReadingDate,ReadingTime from JobReading JR
    where  JR.JobSerialNo = @JobSrNo and JR.PartParaCode = @PartParaCode and
    JR.SampleNo =@SampleNo and  JR.ReadingSerialNo = @ReadingNo' 
END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spSampleOrReadingCount]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spSampleOrReadingCount]
@JobSrNo float, @PartParaCode nvarchar(50), @History smallint
WITH EXEC AS CALLER
AS
if @History=1
    Select count(*) ReadingCount from HistoryJobReading JR where JR.JobSerialno=@JobSrNo
    and JR.PartParaCode=@PartParaCode
else
    Select count(*) ReadingCount from JobReading JR where JR.JobSerialno=@JobSrNo
    and JR.PartParaCode=@PartParaCode' 
END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spSampleOrReadingMax]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spSampleOrReadingMax]
@JobSrNo float, @PartParaCode nvarchar(50), @History smallint
WITH EXEC AS CALLER
AS
if @History=1
    Select max(SampleNo) from HistoryJobReading JR where JR.JobSerialno=@JobSrNo
    and JR.PartParaCode=@PartParaCode
else
    Select max(SampleNo) from JobReading JR where JR.JobSerialno=@JobSrNo
    and JR.PartParaCode=@PartParaCode' 
END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spCalculateNoOfReadings]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spCalculateNoOfReadings]
@JobSrNo float, @PartParaCode nvarchar(50), @StartSampleNo float, @EndSampleNo float, @History int, @SampleNoCond smallint
WITH EXEC AS CALLER
AS
if @History=1
    if @SampleNoCond=1
        Select count(JR.Reading) from HistoryJobReading JR where JR.JobSerialNo = @JobSrNo And
        JR.PartParaCode = @PartParaCode And (JR.HomogenizationInd Is Null Or JR.HomogenizationInd=''N'')
        and JR.SampleNo>@StartSampleNo and JR.SampleNo<=@EndSampleNo
    else
        Select count(JR.Reading) from HistoryJobReading JR where JR.JobSerialNo = @JobSrNo And
        JR.PartParaCode = @PartParaCode And (JR.HomogenizationInd Is Null Or JR.HomogenizationInd=''N'')
else
    if @SampleNoCond=1
        Select count(JR.Reading) from JobReading JR where JR.JobSerialNo = @JobSrNo And
        JR.PartParaCode = @PartParaCode And (JR.HomogenizationInd Is Null Or JR.HomogenizationInd=''N'')
        and JR.SampleNo>@StartSampleNo and JR.SampleNo<=@EndSampleNo
    else
        Select count(JR.Reading) from JobReading JR where JR.JobSerialNo = @JobSrNo And
        JR.PartParaCode = @PartParaCode And (JR.HomogenizationInd Is Null Or JR.HomogenizationInd=''N'')' 
END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spStartEndSampleNo]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spStartEndSampleNo]
@JobSrNo float, @PartParaCode nvarchar(50), @History smallint
WITH EXEC AS CALLER
AS
if @History=1
    Select SampleNo from HistoryJobSample where JobSerialNo=@JobSrNo and
    PartParaCode=@PartParaCode And (HomogenizationInd Is Null Or HomogenizationInd=''N'') 
    order by SampleNo
else
    Select SampleNo from JobSample where JobSerialNo=@JobSrNo and
    PartParaCode=@PartParaCode And (HomogenizationInd Is Null Or HomogenizationInd=''N'') 
    order by SampleNo' 
END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetSampleNoHomo]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spGetSampleNoHomo]
@JobSrNo float, @PartParaCode nvarchar(50), @Homo char(1), @History smallint
WITH EXEC AS CALLER
AS
if @History=1
    Select count(*) from HistoryJobSample
    where  JobSerialNo  = @JobSrNo and  PartParaCode= @PartParaCode and
    HomogenizationInd=@Homo
else
    Select count(*) from JobSample
    where  JobSerialNo  = @JobSrNo and  PartParaCode= @PartParaCode and
    HomogenizationInd=@Homo' 
END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spUpdateParameterDetails]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spUpdateParameterDetails]
@JobSrNo float, @PartParaCode nvarchar(50), @History smallint
WITH EXEC AS CALLER
AS
if @History=1
    Select sum(xbar), sum(SampleRange),sum(stddeviation) 
    From HistoryJobSample
    Where PartParaCode = @PartParaCode And JobSerialNo = @JobSrNo And 
    (HomogenizationInd is null or HomogenizationInd = ''N'')
else
    Select sum(xbar), sum(SampleRange),sum(stddeviation) 
    From JobSample
    Where PartParaCode = @PartParaCode And JobSerialNo = @JobSrNo And 
    (HomogenizationInd is null or HomogenizationInd = ''N'')' 
END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spUpdateArrayForXBar]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spUpdateArrayForXBar]
@JobSrNo float, @PartParaCode nvarchar(50), @MaxSampleNo smallint, @History smallint
WITH EXEC AS CALLER
AS
if @History=1
    Select XBar from HistoryJobSample where 
    JobSerialNo = @JobSrNo and PartParaCode = @PartParaCode and
    SampleNo  = @MaxSampleNo 
else
    Select XBar from JobSample where 
    JobSerialNo = @JobSrNo and PartParaCode = @PartParaCode and
    SampleNo  = @MaxSampleNo' 
END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spXBarRangeSD]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spXBarRangeSD]
@JobSrNo float, @PartParaCode nvarchar(50), @StartSampleNo float, @EndSampleNo float, @History smallint
WITH EXEC AS CALLER
AS
if @History=1
    Select sum(Xbar),sum(samplerange),sum(stddeviation),count(*) from HistoryJobSample 
    where JobSerialNo=@JobSrNo and PartParaCode=@PartParaCode And 
    (SampleNo>@StartSampleNo and SampleNo<=@EndSampleNo) and
    (HomogenizationInd Is Null Or HomogenizationInd=''N'')
else
    Select sum(Xbar),sum(samplerange),sum(stddeviation),count(*) from JobSample 
    where JobSerialNo=@JobSrNo and PartParaCode=@PartParaCode And 
    (SampleNo>@StartSampleNo and SampleNo<=@EndSampleNo) and
    (HomogenizationInd Is Null Or HomogenizationInd=''N'')' 
END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spXBarRangeSDForRecalNSampleGtZero]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spXBarRangeSDForRecalNSampleGtZero]
@JobSrNo float, @PartParaCode nvarchar(50), @StartSampleNo float, @EndSampleNo float, @History smallint
WITH EXEC AS CALLER
AS
if @History=1 
    Select sum(Xbar),sum(samplerange),sum(stddeviation),count(*) from HistoryJobSample where 
    JobSerialNo=@JobSrNo and PartParaCode=@PartParaCode And 
    SampleNo>@StartSampleNo and SampleNo<=@EndSampleNo And
    (HomogenizationInd Is Null Or HomogenizationInd=''N'')
else                
    Select sum(Xbar),sum(samplerange),sum(stddeviation),count(*) from JobSample where 
    JobSerialNo=@JobSrNo and PartParaCode=@PartParaCode And 
    SampleNo>@StartSampleNo and SampleNo<=@EndSampleNo And
    (HomogenizationInd Is Null Or HomogenizationInd=''N'')' 
END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spMaxSampleNo]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spMaxSampleNo]
@JobSrNo float, @PartParaCode nvarchar(50), @History smallint
WITH EXEC AS CALLER
AS
if @History=1
    Select max(JS.SampleNo) from HistoryJobSample JS
    where  JS.JobSerialNo = @JobSrNo  and  JS.PartParaCode = @PartParaCode 
    And (JS.HomogenizationInd Is Null Or JS.HomogenizationInd=''N'')
else
    Select max(JS.SampleNo) from JobSample JS
    where  JS.JobSerialNo = @JobSrNo  and  JS.PartParaCode = @PartParaCode 
    And (JS.HomogenizationInd Is Null Or JS.HomogenizationInd=''N'')' 
END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spStartEndSampleNoCount]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spStartEndSampleNoCount]
@JobSrNo float, @PartParaCode nvarchar(50), @History smallint
WITH EXEC AS CALLER
AS
if @History=1
    Select count(SampleNo) from HistoryJobSample where JobSerialNo=@JobSrNo and 
    PartParaCode=@PartParaCode And (HomogenizationInd Is Null Or HomogenizationInd=''N'')
else
    Select count(SampleNo) from JobSample where JobSerialNo=@JobSrNo and 
    PartParaCode=@PartParaCode And (HomogenizationInd Is Null Or HomogenizationInd=''N'')' 
END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spCalculateNoOfSamples]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spCalculateNoOfSamples]
@JobSrNo float, @PartParaCode nvarchar(50), @StartSampleNo float, @EndSampleNo float, @History smallint, @SampleNoCond smallint
WITH EXEC AS CALLER
AS
if @History=1
    if @SampleNoCond=1
        Select count(JS.SampleNo) from HistoryJobSample JS where JS.JobSerialNo =@JobSrNo
        and JS.PartParaCode = @PartParaCode and  JS.HomogenizationInd <>''Y''            
        and JS.SampleNo>@StartSampleNo and JS.SampleNo<=@EndSampleNo
    else
        Select count(JS.SampleNo) from HistoryJobSample JS where JS.JobSerialNo =@JobSrNo
        and JS.PartParaCode = @PartParaCode and  JS.HomogenizationInd <>''Y''   
else
    if @SampleNoCond=1
        Select count(JS.SampleNo) from JobSample JS where JS.JobSerialNo =@JobSrNo
        and JS.PartParaCode = @PartParaCode and  JS.HomogenizationInd <>''Y''            
        and JS.SampleNo>@StartSampleNo and JS.SampleNo<=@EndSampleNo
    else
        Select count(JS.SampleNo) from JobSample JS where JS.JobSerialNo =@JobSrNo
        and JS.PartParaCode = @PartParaCode and  JS.HomogenizationInd <>''Y''' 
END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spJobCloseJobS]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
Create Procedure [dbo].[spJobCloseJobS] @JobSerialNo float, @JobStartDate varchar(15),
@JobStartTime varchar(15), @MachineNo varchar(15)
As
Insert Into HistoryJobSample(JobStartDate,JobStartTime,PartParaCode,JobSerialno,SampleNo, XBAR,UCLXBARValue,MachineNo,LCLXBarValue,StdDeviation,SampleRange,Operator,SampleDate, ShiftCode,SetFault,DisposalActionTakenInd,DecisionTakenBy,DecisionTakenDate, DecisionTakenTime,DecisionExplanation,ReworkInd,ReworkSeqNo,ReworkProcessCode,ReadingMode, PartParatype,SampleTime,SubgroupSize,DecisionQty,HomogenizationInd,AssignableCause ) Select JobStartDate,JobStartTime,PartParaCode,JobSerialno,SampleNo, XBAR,UCLXBARValue,MachineNo,LCLXBarValue,StdDeviation,SampleRange,Operator,SampleDate, ShiftCode,SetFault,DisposalActionTakenInd,DecisionTakenBy,DecisionTakenDate, DecisionTakenTime,DecisionExplanation,ReworkInd,ReworkSeqNo,ReworkProcessCode,ReadingMode, PartParatype,SampleTime,SubgroupSize,DecisionQty,HomogenizationInd,AssignableCause  From JobSample  where JobSerialNo = @JobSerialNo and JobStartDate = @JobStartDate  and JobStartTime = @JobStartTime and MachineNo = @MachineNo
' 
END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetSampleNoMaxSample]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spGetSampleNoMaxSample]
@JobSrNo float, @PartParaCode nvarchar(50), @History smallint
WITH EXEC AS CALLER
AS
if @History=1
    Select max(JS.SampleNo), count(JS.SampleNo)from HistoryJobSample JS
    where  JS.JobSerialNo = @JobSrNo and JS.PartParaCode = @PartParaCode And 
    (JS.HomogenizationInd Is Null Or JS.HomogenizationInd=''N'')
else
    Select max(JS.SampleNo), count(JS.SampleNo)from JobSample JS
    where  JS.JobSerialNo = @JobSrNo and JS.PartParaCode = @PartParaCode And 
    (JS.HomogenizationInd Is Null Or JS.HomogenizationInd=''N'')' 
END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spUpdateSampleDetails]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spUpdateSampleDetails]
@JobSerialNo float, @History smallint
WITH EXEC AS CALLER
AS
if @History=1 
    Select PartParaCode,SampleNo,SampleDate,SampleTime,xbar,SampleRange,stddeviation
    From HistoryJobSample Where JobSerialNo = @JobSerialNo
    And ( HomogenizationInd is null or HomogenizationInd = ''N'') Order By PartParaCode,SampleNo
else
    Select PartParaCode,SampleNo,SampleDate,SampleTime,xbar,SampleRange,stddeviation
    From JobSample Where JobSerialNo = @JobSerialNo
    And ( HomogenizationInd is null or HomogenizationInd = ''N'') Order By PartParaCode,SampleNo' 
END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spUpdateSample]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spUpdateSample]
@JobSrNo float, @PartParaCode nvarchar(50)
WITH EXEC AS CALLER
AS
Select JR.Reading, JR.ReadingSerialNo from JobReading JR, 
JobSample JS where JR.JobSerialNo= @JobSrNo and JR.PartParaCode= @PartParaCode and 
JR.JobSerialNo = JS.JobSerialNo and JR.PartParaCode = JS.PartParaCode 
and JR.sampleno = JS.SampleNo and (JR.HomogenizationInd is null or 
JR.HomogenizationInd = ''N'') order by JR.SampleNo, JR.ReadingSerialNo' 
END


SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spInsertToOtherDatabase]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spInsertToOtherDatabase] 
	@Sourcedatabase sysname, 
    @Destdatabase sysname   
WITH EXEC AS CALLER
AS
exec(''INSERT INTO '' +@Sourcedatabase + '' SELECT * FROM '' + @Destdatabase )'
END