IF NOT EXISTS (SELECT 1  FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[Import_Doc_Detail]') AND name = 'Valuation1') 
	BEGIN
		ALTER TABLE [dbo].[Import_Doc_Detail] ADD Valuation1 smallmoney null
    End
GO

/****** Object:  View [dbo].[Inv_Detail]    Script Date: 31/01/2021 16:47:14 ******/

ALTER VIEW [dbo].[Inv_Detail]
AS
SELECT        dbo.Invoice.Invoice_No, dbo.Invoice.InvRef_No, dbo.Invoice.Inv_Date, dbo.Invoice.CBF_ID, dbo.CBF_Master.CBF_Name, dbo.CBF_Master.CBFFull_Name, dbo.CBF_Master.CBF_Address, dbo.Station_Master.Station_Name, 
                         dbo.Invoice.Amount, dbo.Invoice.FOC_DQty, dbo.Invoice.FOC_PQty, dbo.Invoice.FOC_Amount, dbo.Invoice.Comp_ID, dbo.Invoice.ST_Charge, dbo.Invoice.Edu_Cess, dbo.Invoice.SHE_Cess, dbo.Invoice.Fuel_Charge, 
                         dbo.Invoice.Other, dbo.Invoice.Discount, dbo.Invoice.Disc_Percentage, dbo.Invoice.Inv_Total, dbo.Invoice.Inv_RTotal, dbo.Invoice.Narration, dbo.Invoice.Print_Status, dbo.Invoice.Inv_Status, dbo.Invoice.Create_ID, 
                         dbo.Invoice.Modify_ID, dbo.Invoice_Detail.SNo, dbo.Invoice_Detail.Doc_No, dbo.Invoice_Detail.Doc_Type, dbo.Invoice_Detail.Category, dbo.Invoice_Detail.Recipient_Name, dbo.Invoice_Detail.Rec_Address, 
                         dbo.Invoice_Detail.Rec_Contact, dbo.Invoice_Detail.Rec_Pin, dbo.Invoice_Detail.Weight, dbo.Invoice_Detail.Booking_Rate, dbo.Invoice_Detail.Qty, 
                         CASE dbo.Invoice_Detail.Doc_Type WHEN 'D' THEN dbo.Invoice_Detail.Booking_Rate * dbo.Invoice_Detail.Qty ELSE dbo.Invoice_Detail.Rate END AS Amt_Doc, dbo.Invoice_Detail.DStation_No, dbo.Invoice_Detail.OStation_No, 
                         dbo.Invoice_Detail.Crossing, dbo.GetStation_Name(dbo.Invoice_Detail.DStation_No) AS DStation, dbo.GetStation_Name(dbo.Invoice_Detail.OStation_No) AS OStation, dbo.GetStation_Name(dbo.Invoice_Detail.Crossing) 
                         AS CName, dbo.Invoice_Detail.TDate, dbo.Invoice_Detail.Rec_Mobile, dbo.Invoice_Detail.Sender_Name, dbo.Invoice_Detail.Sender_Address, dbo.Invoice_Detail.Sender_Contact, dbo.Invoice_Detail.Sender_Mobile, 
                         dbo.Invoice_Detail.Sender_Pin, dbo.Invoice.Comment, dbo.Invoice.Reference, dbo.Invoice_Detail.Goods_Type, dbo.Invoice_Detail.Valuation, dbo.DocCategory_Master.Category_Name, dbo.Invoice_Detail.Rate, 
                         dbo.Invoice_Detail.Addl_Rate, dbo.Invoice_Detail.Ref_No, dbo.CBF_Master.ST_No, dbo.CBF_Master.PAN_No, dbo.CBF_Master.CBF_Phone1, dbo.GetStation_Name(dbo.CBF_Master.CBF_Station_No) AS Client_Station, 
                         dbo.Invoice.TSGST, dbo.Invoice.TCGST, dbo.Invoice.TIGST, dbo.Invoice_Detail.SGST, dbo.Invoice_Detail.CGST, dbo.Invoice_Detail.IGST, dbo.CBF_Master.CBF_GSTIN, dbo.Invoice.Inv_RTotal_Word, dbo.CBF_Master.CBF_Code, 
                         dbo.CBF_Master.CBFCat_ID, dbo.CBF_Master.Pin_Code, dbo.CBF_Master.Contact_Person, dbo.CBF_Master.State_Code, dbo.CBF_Master.Email, dbo.CBF_Master.CBF_Phone2, dbo.CBF_Master.CBF_Mobile, 
                         dbo.CBF_Master.CBF_Fax, dbo.CBF_Master.CBF_AddlField1, dbo.CBF_Master.CBF_AddlField2, dbo.CBF_Master.CBF_AddlField3, dbo.Invoice.InvPrint_Date, dbo.Invoice.Invoice_Month
FROM            dbo.DocCategory_Master INNER JOIN
                         dbo.Invoice_Detail ON dbo.DocCategory_Master.Category_ID = dbo.Invoice_Detail.Category LEFT OUTER JOIN
                         dbo.CBF_Master INNER JOIN
                         dbo.Invoice ON dbo.CBF_Master.CBF_ID = dbo.Invoice.CBF_ID INNER JOIN
                         dbo.Station_Master ON dbo.CBF_Master.CBF_Station_No = dbo.Station_Master.Station_No ON dbo.Invoice_Detail.Invoice_No = dbo.Invoice.Invoice_No

GO



ALTER VIEW [dbo].[Import_Challan]
 
AS
SELECT     dbo.Import.Import_ID, dbo.Import.Import_Date, dbo.Import.CBF_ID, dbo.Import.IMR_No, dbo.Import.IMR_Type, dbo.Import.IMR_Ref, 
                      dbo.Import.IMR_Date, dbo.CBF_Master.CBF_Name, dbo.Import_Doc_Detail.Doc_No, dbo.Import_Doc_Detail.Ref_No, dbo.Import_Doc_Detail.Doc_Type, 
                      dbo.Import_Doc_Detail.Category, dbo.Import_Doc_Detail.Recipient_Name, dbo.Import_Doc_Detail.Rec_Address, dbo.Import_Doc_Detail.Rec_Contact, 
                      dbo.Import_Doc_Detail.Rec_Pin, dbo.Import_Doc_Detail.DStation_No, dbo.Import_Doc_Detail.Sender_Name, dbo.Import_Doc_Detail.Sender_Address, 
                      dbo.Import_Doc_Detail.Sender_Contact, dbo.Import_Doc_Detail.Sender_Pin, dbo.Import_Doc_Detail.OStation_No, dbo.Import_Doc_Detail.Crossing, 
                      dbo.Import_Doc_Detail.Weight, dbo.Import_Doc_Detail.Goods_Type, dbo.Import_Doc_Detail.Valuation, dbo.Import_Doc_Detail.Valuation1, dbo.Import_Doc_Detail.Payment_Mode, 
                      dbo.Import_Doc_Detail.Booking_Rate, dbo.Import_Doc_Detail.Qty, dbo.Import_Doc_Detail.Insurance, dbo.Import_Doc_Detail.Doc_Status, 
                      dbo.GetStation_Name(dbo.Import_Doc_Detail.OStation_No) AS OStation, dbo.GetStation_Name(dbo.Import_Doc_Detail.DStation_No) AS DStation, 
                      dbo.GetStation_Name(dbo.Import_Doc_Detail.Crossing) AS CStation, 
                      (CASE WHEN dbo.Import_Doc_Detail.Doc_Type = 'D' THEN dbo.Import_Doc_Detail.Booking_Rate * dbo.Import_Doc_Detail.Qty ELSE dbo.Import_Doc_Detail.Booking_Rate
                       END) AS Amount, dbo.Import_Doc_Detail.Rec_Mobile, dbo.Import_Doc_Detail.Sender_Mobile, dbo.Import_Doc_Detail.Category_Name, 
                      dbo.Import_Doc_Detail.UDoc_ID
FROM         dbo.Import INNER JOIN
                      dbo.CBF_Master ON dbo.Import.CBF_ID = dbo.CBF_Master.CBF_ID INNER JOIN
                      dbo.Import_Doc_Detail ON dbo.Import.Import_ID = dbo.Import_Doc_Detail.Import_ID

GO

/****** Object:  StoredProcedure [dbo].[Insert_Doc_Trans]    Script Date: 19/01/2021 23:55:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Insert_Doc_Trans]
	@nUDoc_ID bigint,
	@nDoc_No nvarchar(15),
	@dTDate as datetime,
	@nIMR_Type as smallint,
	@nIMR_No as bigint,
	@sIMR_Ref as nvarchar(10),
	@nCBF_ID as int,
	@nIMR_Status as smallint,
	@sRemark as nvarchar(50),
	@bFlagUp as bit,
	@nCreate_ID as bigint,
	@ret_Result as int OUTPUT
AS
BEGIN TRANSACTION
	SET NOCOUNT ON;
	DECLARE @nTrans_Number as smallint;
	DECLARE @nCnt as int;
	SET @nCnt=0;
	Select @nCnt = count(UDoc_ID) from Doc_Trans Where UDoc_ID = @nUDoc_ID and IMR_No = @nIMR_No;
	if @nCnt = 0 
		begin
			Select @nTrans_Number = count(UDoc_ID) from Doc_Trans Where UDoc_ID = @nUDoc_ID;
			SET @nTrans_Number = @nTrans_Number + 1;
			
			INSERT INTO Doc_Trans(UDoc_ID,Doc_No,TDate,Trans_No,IMR_Type,IMR_no,IMR_Ref,CBF_ID,IMR_Status,Remark,FlagUp,Create_ID)
					Values(@nUDoc_ID,@nDoc_No,@dTDate,@nTrans_Number,@nIMR_Type,@nIMR_No,@sIMR_Ref,@nCBF_ID,@nIMR_Status,@sRemark,@bFlagUp,@nCreate_ID)
		end
	
	SET @ret_Result = 1;
	SELECT @ret_Result
	
COMMIT TRANSACTION


GO


IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[Airline_Master]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[Airline_Master](
	[Airline_ID] [int] NOT NULL,
	[Airline_Name] [nvarchar](30) NOT NULL,
	[Create_ID] [bigint] NULL,
	[Modify_ID] [bigint] NULL,
 CONSTRAINT [PK_Airline_Master] PRIMARY KEY CLUSTERED 
(
	[Airline_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

END

GO


IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[AWB_Master]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[AWB_Master](
	[AWB_ID] [int] NOT NULL,
	[Airline_ID] [int] NULL,
	[IATA_Agent] [nvarchar](30) NULL,
	[IATA_Code] [nvarchar](20) NULL,
	[AWB_From] [nvarchar](20) NULL,
	[AWB_To] [nvarchar](20) NULL,
	[AWB_Count] [int] NULL,
	[AWB_Used] [int] NULL,
	[AWB_Balance] [int] NULL,
 CONSTRAINT [PK_AWB_Master] PRIMARY KEY CLUSTERED 
(
	[AWB_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

END
GO




IF NOT EXISTS (SELECT 1  FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[IMR_Detail]') AND name = 'MF_No') 
	BEGIN
		ALTER TABLE [dbo].[IMR_Detail] ADD MF_No nvarchar(15) null, MF_Date datetime null
    End
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Insert_IMR]
	@nIMRType_ID smallint,
	@nCategory_ID smallint,
	@nComp_ID as int,
	@dIMR_Date as smalldatetime,
	@nDQty as int,
	@nDWht as decimal(18,3),
	@nPQty as int,
	@nPWht as decimal(18,3),
	@nCBF_ID as int,
	@nLot as smallint,
	@isDispatch as bit,
	@nFieldSet_ID as int,
	@nCreate_ID as bigint,
	@nModify_ID as bigint,
	@sMF_No as varchar(15),
	@dMF_Date as smalldatetime,
	@ret_nIMR_No as bigint OUTPUT,
	@ret_sIMR_Ref as varchar(10) OUTPUT,
	@ret_isNew as int OUTPUT
AS
BEGIN TRANSACTION
	SET NOCOUNT ON;

	DECLARE @nIMR_No as bigint;
	DECLARE @sIMR_Ref as nchar(10);
	DECLARE @nIDN as bigint;
	DECLARE @nCnt as int;
	DECLARE @LNo as int;
	
	SET @ret_isNew = -1;
	SELECT @nCnt = count(IMR_No) FROM IMR_Detail Where CBF_ID = @nCBF_ID and IMRType_ID = @nIMRType_ID and Category_ID = @nCategory_ID and Comp_ID = @nComp_ID and dbo.fnFormatDate(IMR_Date,'yyyy-mm-dd') = dbo.fnFormatDate(@dIMR_Date,'yyyy-mm-dd') and Lot = @nLot;
	IF @nCnt=0
		BEGIN 
			SELECT @LNo = Convert(int,substring(Last_ID,4,7))+1 FROM dbo.IMR_LastID Where Comp_ID = @nComp_ID and IMRType_ID = @nIMRType_ID;
			--print @LNo;
			--set @LNo = Convert(int,substring(@sIMR_Ref,4,7))+1;
			--print @Lno;
			IF @nIMRType_ID = 1
				SET @sIMR_Ref = 'IW-';
			ELSE IF @nIMRType_ID = 2
				SET @sIMR_Ref = 'MF-';
			ELSE IF @nIMRType_ID = 3
				SET @sIMR_Ref = 'RS-';
			ELSE IF @nIMRType_ID = 4
				SET @sIMR_Ref = 'RT-';
			ELSE IF @nIMRType_ID = 5
				SET @sIMR_Ref = 'PD-';
			ELSE IF @nIMRType_ID = 6
				SET @sIMR_Ref = 'IV-';
			ELSE IF @nIMRType_ID = 7
				SET @sIMR_Ref = 'DP-';
			ELSE
				SET @sIMR_Ref = 'PD-';
			--print @sIMR_Ref;
			--print rtrim(@sIMR_Ref) + RIGHT('0000000'+ CONVERT(VARCHAR,5),7);
			set @sIMR_Ref = rtrim(@sIMR_Ref) + RIGHT('0000000'+ CONVERT(VARCHAR,@LNo),7);
			--print @sIMR_Ref;
			UPDATE IMR_LastID SET Last_ID = @sIMR_Ref Where Comp_ID= @nComp_ID and IMRType_ID = @nIMRType_ID;
			INSERT INTO IMR_Detail(IMR_Ref,IMRType_ID,Category_ID,Comp_ID,IMR_Date,CBF_ID,Doc_Count,Parcel_Count,Doc_GWeight,Parcel_GWeight,Lot,isDispatch,FieldSet_ID,IMR_Status,IMR_Lock,Create_ID,Modify_ID,MF_No,MF_Date)
					Values(@sIMR_Ref,@nIMRType_ID,@nCategory_ID,@nComp_ID,@dIMR_Date,@nCBF_ID,@nDQty,@nPQty,@nDWht,@nPWht,@nLot,@isDispatch,@nFieldSet_ID,0,0,@nCreate_ID,0,@sMF_No,@dMF_Date)
			SELECT @nIDN = @@IDENTITY;
			SET @ret_nIMR_No = @nIDN;
			SET @ret_sIMR_Ref=@sIMR_Ref
			SET @ret_isNew = 1;
		END
	ELSE
		BEGIN
			SELECT @nIMR_No = IMR_No,@sIMR_Ref = IMR_Ref FROM IMR_Detail Where CBF_ID = @nCBF_ID and IMRType_ID = @nIMRType_ID and Category_ID = @nCategory_ID and Comp_ID = @nComp_ID and dbo.fnFormatDate(IMR_Date,'yyyy-mm-dd') = dbo.fnFormatDate(@dIMR_Date,'yyyy-mm-dd') and Lot = @nLot;
		--SELECT @sIMR_Ref = IMR_Ref FROM IMR_Detail Where IMR_No = @nIMR_No;
			UPDATE IMR_Detail SET Doc_Count = Doc_Count + @nDQty, Parcel_Count = Parcel_Count + @nPQty, Doc_GWeight = Doc_GWeight + @nDWht, Parcel_GWeight=Parcel_GWeight+@nPWht,Modify_ID = @nModify_ID Where IMR_No = @nIMR_No
			SET @ret_nIMR_No=@nIMR_No;
			SET @ret_sIMR_Ref=@sIMR_Ref;
			SET @ret_isNew=0;
		END
	SELECT @ret_nIMR_No,@ret_sIMR_Ref,@ret_isNew
	--SELECT @ret_nIMR_No = IMR_No,@ret_sIMR_Ref=IMR_Ref FROM IMR_Detail WHERE IMR_No = @nIMR_No;
	--return @ret_nIMR_NO
	
COMMIT TRANSACTION