

INSERT INTO [sSystem].[tVersionUpdate] ([VersionNumber],[VersionId],[Status],[UpdateScript],[StartDateTime],[EndDateTime],[OrgGroupId])
VALUES('24.06.15',240615,'BEGIN','Script',sCommon.fGetDate(),sCommon.fGetDate(),0)
GO
GO
PRINT N'Creating Table [sStudent].[tOnlineRegistrationStudentHealth]...';


GO
CREATE TABLE [sStudent].[tOnlineRegistrationStudentHealth] (
    [StudentId]                    BIGINT         NOT NULL,
    [IdentificationMark1]          NVARCHAR (100) NULL,
    [IdentificationMark2]          NVARCHAR (100) NULL,
    [IsPysicalDeformity]           BIT            NULL,
    [PysicalDeformityRemark]       NVARCHAR (200) NULL,
    [IsMajorIllness]               BIT            NULL,
    [MajorIllnessRemark]           NVARCHAR (200) NULL,
    [IsAllergy]                    BIT            NULL,
    [AllergyRemark]                NVARCHAR (200) NULL,
    [IsPastHistory]                BIT            NULL,
    [PastHistoryRemark]            NVARCHAR (200) NULL,
    [FamilyDoctorName]             NVARCHAR (150) NULL,
    [FamilyDoctorContactNo]        NVARCHAR (100) NULL,
    [UpdatedUserId]                BIGINT         NULL,
    [UpdatedDateTime]              DATETIME       NULL,
    [PhysicalDisabilityCategoryId] INT            NULL,
    CONSTRAINT [PK_tOnlineRegistrationStudentHealth] PRIMARY KEY CLUSTERED ([StudentId] ASC) WITH (FILLFACTOR = 85)
);


GO
PRINT N'Creating Table [sStudent].[tTempStudentId]...';


GO
CREATE TABLE [sStudent].[tTempStudentId] (
    [NewStudentId]        BIGINT        NULL,
    [StudentId]           BIGINT        NULL,
    [Code]                NVARCHAR (24) NULL,
    [AdmissionCategoryId] INT           NULL
);


GO
PRINT N'Creating Table [sTransport].[tVTSTrackingData]...';


GO
CREATE TABLE [sTransport].[tVTSTrackingData] (
    [AutoId]          INT             IDENTITY (1, 1) NOT NULL,
    [VehicleId]       INT             NOT NULL,
    [BusRouteId]      INT             NOT NULL,
    [Latitude]        NVARCHAR (20)   NOT NULL,
    [Longitude]       NVARCHAR (20)   NOT NULL,
    [CreatedDateTime] DATETIME        NOT NULL,
    [UpdatedDateTime] DATETIME        NOT NULL,
    [VTSVendorId]     SMALLINT        NOT NULL,
    [VTSResponse]     NVARCHAR (2000) NOT NULL,
    [APICallCount]    INT             NOT NULL,
    CONSTRAINT [PK_VTSTrackingData] PRIMARY KEY CLUSTERED ([AutoId] ASC)
);


GO
PRINT N'Altering View [sStudent].[vStudentColumnName]...';


GO

ALTER VIEW [sStudent].[vStudentColumnName]  
AS 
SELECT 	
	  --SA.AcademicId,	  
	  --SA.ClassId,
	  --SA.DivisionId,
	  SA.RollNo,
	  --SA.OrgId,
	  --SA.BatchId,
	  OM.Title AS Organization,
	  BM.Title AS Batch,
	  DM.Title AS Department,
	  --SA.StudentId,
	  SA.SUId,
	  SA.Code AS [ID No],
	  SA.Code AS [GR No],
	  SA.Number,
	  SA.FirstName,
	  SA.MiddleName,
	  SA.LastName,
	  SA.Name,
	  --SA.DepartmentId,
	  --SA.StatusId,
	  SA.ClassName,
	  SA.DivisionName,
	  SA.StatusName,
	  SO.UIDNumber,
	  SO.AdhaarNumber,
      SO.DateOfBirth,
      SO.BirthdateInWord,
      --SO.TransportModeId,
      TM.Title AS TransportMode,
      --SO.GenderId,
      GM.Title AS Gender,
      --SO.BloodGroupId,
      BGM.Title AS BloodGroup,
      SO.BirthPlace,
      SO.BirthTaluka,
      SO.BirthDistrict,
      SO.FamilyCast As FamilyCaste,
      --SO.FamilySubCast,
      --SO.CastCategoryId,
      
      CCM.Title AS CasteCategory,
      --SO.ReligionId,
      RM.Title AS Religion,
      --SO.HouseId,
      HM.Title AS House,
      --SO.MotherTongueId,
      MTM.Title AS MotherTongue,
      --SO.NationalityId,
      NM.Title AS Nationality,
      SO.Hobby,
      SH.IdentificationMark1,
      SH.IdentificationMark2,
      SH.IsPysicalDeformity AS IsPhysicalDeformity,
      SH.PysicalDeformityRemark AS PhysicalDeformityRemark,
      SH.IsMajorIllness,
      SH.MajorIllnessRemark,
      SH.FamilyDoctorName,
      SH.FamilyDoctorContactNo,
    STUFF(
        COALESCE(',' + NULLIF(SC.PresentStreetOne, ''), '')  + 
        COALESCE(',' + NULLIF(SC.PresentStreetTwo, ''), '') +
        COALESCE(',' + NULLIF(SC.PresentStreetThree, ''), '') +
        COALESCE(',' + NULLIF(SC.PresentCountry, ''), '') +
        COALESCE(',' + NULLIF(SC.PresentState, ''), '') +
        COALESCE(',' + NULLIF(SC.PresentCity, ''), '') +
        COALESCE(',' + NULLIF(SC.PresentZipCode, ''), '') +
        COALESCE(',' + NULLIF(SC.PresentContactNo, ''), ''),
        1, 1, '') AS PresentAddress,
	  SC.PresentStreetOne,
      SC.PresentStreetTwo,
      SC.PresentStreetThree,
      SC.PresentCountry,
      SC.PresentState,
      SC.PresentCity,
      SC.PresentZipCode,
      SC.PresentContactNo,
	   STUFF(
           COALESCE(',' + NULLIF(SC.PermanentStreetOne, ''), '')  + 
           COALESCE(',' + NULLIF(SC.PermanentStreetTwo, ''), '') +
           COALESCE(',' + NULLIF(SC.PermanentStreetThree, ''), '') +
           COALESCE(',' + NULLIF(SC.PermanentCountry, ''), '') +
           COALESCE(',' + NULLIF(SC.PermanentState, ''), '') +
           COALESCE(',' + NULLIF(SC.PermanentCity, ''), '') +
            COALESCE(',' + NULLIF(SC.PermanentZipCode, ''), '') +
            COALESCE(',' + NULLIF(SC.PermanentContactNo, ''), ''),
           1, 1, '') AS PermanentAddress ,
      SC.PermanentStreetOne,
      SC.PermanentStreetTwo,
      SC.PermanentStreetThree,
      SC.PermanentCountry,
      SC.PermanentState,
      SC.PermanentCity,
      SC.PermanentZipCode,
      SC.PermanentContactNo,
	   
      STUFF(
           COALESCE(',' + NULLIF(SC.EmergencyStreetOne, ''), '')  + 
           COALESCE(',' + NULLIF(SC.EmergencyStreetTwo, ''), '') +
           COALESCE(',' + NULLIF(SC.EmergencyStreetThree, ''), '') +
           COALESCE(',' + NULLIF(SC.EmergencyCountry, ''), '') +
           COALESCE(',' + NULLIF(SC.EmergencyState, ''), '') +
           COALESCE(',' + NULLIF(SC.EmergencyCity, ''), '') +
            COALESCE(',' + NULLIF(SC.EmergencyZipCode, ''), '') +
            COALESCE(',' + NULLIF(SC.EmergencyContactNo, ''), ''),
           1, 1, '') AS EmergencyAddress ,
      SC.EmergencyStreetOne,
      SC.EmergencyStreetTwo,
      SC.EmergencyStreetThree,
      SC.EmergencyCountry,
      SC.EmergencyState,
      SC.EmergencyCity,
      SC.EmergencyZipCode,
      SC.EmergencyContactNo,
      SC.ParentContactNo,
      SC.ParentEmail,
      SC.StudentContactNo,
      SC.StudentEmail,
	  --AR.PickupDestinationId AS BusRouteDestinationId,
	  BDM.Title AS BusRouteDestination,
	  SF.FatherName,
	  SF.FatherDateOfBirth,	 
	  SF.FatherQualification,
	  SF.FatherOccupation,
	  SF.FatherContactNo,
	  SF.FatherMail,
	  SF.FatherOfficeContactNo,
	  SF.FatherOfficeAddress,
	  SF.FatherIncome AS FatherAnnualIncome,	-- MantisId : 0029214
	  SF.MotherName,
	  SF.MotherDateOfBirth,
	  SF.MotherQualification,
	  SF.MotherOccupation,
      SF.MotherContactNo,
      SF.MotherMail,
	  SF.MotherOfficeContactNo,
      SF.MotherOfficeAddress,
      SF.MotherIncome AS MotherAnnualIncome,	-- MantisId : 0029214   
	  AC.Title AS AdmissionCategory,
	  SAD.PreviousSchool,
	  SAD.AdmissionDate,
	  SAD.AdmissionFormNumber,
	  SAD.AdmissionNumber,
	  SAD.IsTransfer,
	  SAD.TransferFrom,
	  SAD.PassingYear,
	  SAD.IsTFWS,
	  SAD.IsCertificateHolder,
	  SAD.CertificateName,
	  AQM.Title AS AdmissionQuota,
      SAD.InactiveDate,
	  SAD.InActiveReason,
	  SAD.LCBook,
	  SAD.LeftStandardId AS LeftStandard,
	  SAD.LeftDate,
	  SAD.LCDate,
	  SAD.LCCopy,
	  SAD.LeftReason,
	  SAD.LCProgress,
	  SAD.LCConduct,
	  SAD.LCRemark,
	  SA.BlackListReason,
	  SA.DivisionOrder,
	  SA.ClassOrderIndex,
	  AD.BankAccountNumber,
	  AD.BankName,
	  AD.BankBranch,
	  AD.BankIFSCCode,
	  AD.BankAccountHolderName,
	  BSM.Title AS StudentBatchStatus,
	  AD.Class9BoardRegistrationNumber,
	  AD.Class11BoardRegistrationNumber,
	  AD.PassportNumber,
	  AD.PassportType,
	  AD.PassportCountryCode,
	  AD.PassportIssueDate,
	  AD.PassportExpiryDate,
	  SS.SiblingName1,
	  SS.SiblingInstitue1,
	  SS.SiblingClass1,
	  SS.SiblingAge1,
	  SS.SiblingRelation1,
	  SS.SiblingName2,
	  SS.SiblingInstitue2,
	  SS.SiblingClass2,
	  SS.SiblingAge2,
	  SS.SiblingRelation2,
	  SS.SiblingName3,
	  SS.SiblingInstitue3,
	  SS.SiblingClass3,
	  SS.SiblingAge3,
	  SS.SiblingRelation3,
	  CD.SSCBoard,
	  CD.SSCSchool,
	  CD.SSCPassingYear,
	  CD.SSCObtainMarks,
	  CD.SSCOutOfMarks,
	  CD.SSCPercentage,
	  CD.SSCMathMarks,
	  CD.SSCMathOutOfMarks,
	  CD.HSCBoard,
	  CD.HSCSchool,
	  CD.HSCPassingYear,
	  CD.HSCObtainMarks,
	  CD.HSCOutOfMarks,
	  CD.HSCPercentage,
	  
	  CD.HSCBiologyMarks,
	  CD.HSCBiologyOutOfMarks,
	 
	  CD.HSCChemistryMarks,
	  CD.HSCChemistryOutOfMarks,
	  CD.HSCMathMarks,
	  CD.HSCMathOutOfMarks,
	  cd.HSCPhysicsMarks,
	  CD.HSCPhysicsOutOfMarks,
	  CD.PCMMarks,
	  CD.PCBMarks,
	  CD.JEERollNo,
	  CD.JEEMeritNo,
	  CD.JEEMeritMarks,
	  CD.JEEOutOfMarks,
	  CD.DiplomaBoard,
	  CD.DiplomaCollege,
	  CD.DiplomaPassingYear,
	  CD.DiplomaObtainMarks,
	  CD.DiplomaOutOfMarks,
	  CD.DiplomaPercentage,
	  CD.ApplicationNo,
	  CD.AdmissionRound,
	  CD.MaritalStatusId,
	  ISNULL(HHM.Title,'') AS HostelName,
	  ISNULL(HRM.Title,'') AS Room,
	  ISNULL(HBM.Title,'') AS BuildingName,
	  ISNULL(HAR.BedNumber,'') AS BedNumber,
	  ISNULL(SGM.Title,'') AS StudentGroup,
	  ISNULL(SGMO.Title,'') AS StudentOtherGroup,
	  SO.BirthState,
	  SO.BirthCountry,
	  ISNULL(BDM.PickupTime,'') AS PickupTime,
	  ISNULL(BDM.DropTime,'') AS DropTime,
	  ISNULL(BRM.Title,'') AS BusRoute,
	  ISNULL(SAD.PreviousStandard,'') AS PreviousStandard,
	  ISNULL(So.ReferenceRemark,'') AS ReferenceRemark,
	  ISNULL(SAD.LCNumber,'') AS LCNumber,
	  CM.Title AS AdmissionStandard,
	  SF.IsFatherExStudent,
	  SF.IsMotherExStudent,
	  SF.FatherOrganization,
	  SF.FatherDesignation,
	  SF.MotherOrganization,
	  SF.MotherDesignation,
	  SF.RelativeOrganization,
	  SF.RelativeDesignation,
	  SH.IsAllergy,
      SH.AllergyRemark,
      SO.FamilySubCast,
      SF.RelativeName,
      SF.RelativeDateOfBirth,
      SF.RelativeOccupation,
      SF.RelativeQualification,
      SF.RelativeContactNo,
      SF.RelativeMail,
      SF.RelativeOfficeAddress,      
      SF.RelativeIncome AS RelativeAnnualIncome,		-- MantisId : 0029214
     
    
		STUFF( (SELECT DISTINCT ',' + CTM.Title FROM sStudent.tStudentCustomTag SCT INNER JOIN sStudent.tCustomTagMaster CTM ON SCT.CustomTagId=CTM.Id 
							WHERE SCT.StudentId = SA.StudentId FOR XML PATH ('')), 1, 1, '')  AS CustomTag,
		STM.Title AS RecordTag,
		SC.WhatsAppContactNo,
        SC.IsStopApplicationAccess,
		CASE WHEN ISNULL(SO.DateOfBirth, '') = '' THEN '' ELSE (SELECT [sCommon].[fGetDateOfBirthInWord](SO.DateOfBirth)) END AS Age,
		BDM.Title AS PickupPoint,
		ISNULL(SA.LocalName,'') AS LocalName -- Added for mantis : 34211
  FROM sStudent.vStudentAcademic SA
	INNER JOIN sStudent.tStudentAdmission SAD ON SAD.StudentId=SA.StudentId
	LEFT JOIN sSetup.tClassMaster CM ON CM.Id=SAD.AdmissionStandardId
	INNER JOIN sCommon.tAdmissionQuotaMaster AQM ON AQM.Id = SAD.AdmissionQuotaId
	INNER JOIN sSetup.tBatchMaster BM ON BM.Id=SA.BatchId
	INNER JOIN sStudent.tStudentOther SO ON SO.StudentId=SA.StudentId
	INNER JOIN sStudent.tStudentCommunication SC ON SC.StudentId=SA.StudentId
	INNER JOIN sTransport.tAssignRoute AR ON AR.SourceId=SA.StudentId
	INNER JOIN sCommon.tTransportModeMaster TM ON TM.Id=SO.TransportModeId
	INNER JOIN sCommon.tGenderMaster GM ON GM.Id=SO.GenderId
	INNER JOIN sCommon.tBloodGroupMaster BGM ON BGM.Id=SO.BloodGroupId
	INNER JOIN sCommon.tCastCategoryMaster CCM ON CCM.Id=SO.CastCategoryId
	INNER JOIN sCommon.tReligionMaster RM ON RM.Id=SO.ReligionId
	INNER JOIN sActivity.tHouseMaster HM ON HM.Id=SO.HouseId
	INNER JOIN sCommon.tMotherTongueMaster MTM ON MTM.Id=SO.MotherTongueId
	INNER JOIN sCommon.tCountryMaster NM ON NM.Id=SO.NationalityId
	INNER JOIN sTransport.tBusRouteDestinationMaster BDM ON BDM.Id=AR.PickupDestinationId
	INNER JOIN sStudent.tStudentFamily SF ON SF.StudentId=SA.StudentId
	INNER JOIN sStudent.tStudentHealth SH ON SH.StudentId=SA.StudentId
	INNER JOIN sStudent.tAdmissionCategoryMaster AC ON AC.Id=SA.AdmissionCategoryId
	INNER JOIN sStudent.tStudentadditional AD ON AD.StudentId= SA.StudentId
	INNER JOIN sStudent.tStudentBatchStatus SBS ON SBS.AcademicId = SA.AcademicId
	INNER JOIN sStudent.tBatchStatusMaster BSM ON BSM.Id = SBS.BatchStatusId
	INNER JOIN sStudent.tStudentSibling SS ON SS.StudentId= SA.StudentId
	INNER JOIN sStudent.tCollegeDetail CD ON CD.StudentId = SA.StudentId
	INNER JOIN sHostel.tAssignRoom HAR ON HAR.SourceId = SA.StudentId
	INNER JOIN sHostel.tRoomMaster HRM ON HRM.Id = HAR.RoomId
	INNER JOIN sHostel.tBuildingMaster HBM ON HBM.HostelId = HRM.HostelId
	INNER JOIN sHostel.tHostelMaster HHM ON HHM.Id = HRM.HostelId
	INNER JOIN sStudent.tStudentGroupMaster SGM ON SGM.Id = SA.StudentGroupId
	INNER JOIN sStudent.tStudentOtherGroupMaster SGMO ON SGMO.Id = SA.StudentOtherGroupId
    INNER JOIN sTransport.tBusRouteMaster BRM ON BRM.Id=BDM.BusRouteId
	INNER JOIN sCommon.tStatusMaster STM ON STM.Id = SA.RecordStatusId AND STM.StatusTypeId=102
	INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = SA.OrgId
	INNER JOIN sSetup.tDepartmentMaster DM ON DM.Id = SA.DepartmentId
GO
PRINT N'Altering View [sStudent].[vStudentCustomSearch]...';


GO

ALTER VIEW [sStudent].[vStudentCustomSearch]  
  
AS  
  
SELECT 	
	  SA.AcademicId,	  
	  SA.ClassId,
	  SA.DivisionId,
	  SA.RollNo,
	  SA.OrgId,
	  OM.Title AS Organization,
	  SA.BatchId,
	  BM.Title AS Batch,
	  BM.OrgGroupBatchId,
	  SA.DepartmentId,
	  DM.Title AS Department,
	  SA.StudentId,
	  SA.SUId,
	  SA.Code,
	  SA.Number,
	  SA.FirstName,
	  SA.MiddleName,
	  SA.LastName,
	  SA.Name,
	  --SA.DepartmentId,
	  SA.StatusId,
	  SA.ClassName,
	  SA.DivisionName,
	  SA.StatusName,
	  SO.UIDNumber,
	  SO.AdhaarNumber,
      CONVERT(VARCHAR(10), SO.DateOfBirth, 111) AS DateOfBirth,
      SO.BirthdateInWord,
      SO.TransportModeId,
      TM.Title AS TransportMode,
      SO.GenderId,
      GM.Title AS Gender,
      SO.BloodGroupId,
      BGM.Title AS BloodGroup,
      SO.BirthPlace,
      SO.BirthTaluka,
      SO.BirthDistrict,
      SO.FamilyCast AS FamilyCaste,
      SO.FamilySubCast,
      
      
      SO.CastCategoryId,
      CCM.Title AS CasteCategory,
      SO.ReligionId,
      RM.Title AS Religion,
      SO.HouseId,
      HM.Title AS House,
      SO.MotherTongueId,
     SO.MotherTongue AS MotherTongue,
      SO.NationalityId,
      NM.Nationality AS Nationality,
      SO.Hobby,
      SH.IdentificationMark1,
      SH.IdentificationMark2,
      SH.IsPysicalDeformity,
      SH.PysicalDeformityRemark,
      SH.IsMajorIllness,
      SH.MajorIllnessRemark,
      SH.FamilyDoctorName,
      SH.FamilyDoctorContactNo,
      
	  SC.PresentStreetOne,
      SC.PresentStreetTwo,
      SC.PresentStreetThree,
      SC.PresentCountry,
      SC.PresentState,
      SC.PresentCity,
      SC.PresentZipCode,
      SC.PresentContactNo,
      SC.PermanentStreetOne,
      SC.PermanentStreetTwo,
      SC.PermanentStreetThree,
      SC.PermanentCountry,
      SC.PermanentState,
      SC.PermanentCity,
      SC.PermanentZipCode,
      SC.PermanentContactNo,
      SC.EmergencyStreetOne,
      SC.EmergencyStreetTwo,
      SC.EmergencyStreetThree,
      SC.EmergencyCountry,
      SC.EmergencyState,
      SC.EmergencyCity,
      SC.EmergencyZipCode,
      SC.EmergencyContactNo,
      SC.ParentContactNo,
      SC.ParentEmail,
      SC.StudentContactNo,
      SC.StudentEmail,
	  ISNULL(SC.WhatsAppContactNo,'') AS WhatsAppContactNo,
	  RT.BusRouteDestinationId AS BusRouteDestinationId,
	  RT.BusRouteDestination AS BusRouteDestination,
	  SF.FatherName,
	  CONVERT(VARCHAR(10), SF.FatherDateOfBirth, 111)AS FatherDateOfBirth,
	  SF.FatherQualification,
	  SF.FatherOccupation,
	  SF.FatherContactNo,
	  SF.FatherMail,
	  SF.FatherOfficeAddress,
	  SF.FatherIncome AS FatherAnnualIncome,	-- MantisId : 0029214
	  SF.FatherOfficeContactNo,
	  SF.MotherName,
	 CONVERT(VARCHAR(10),  SF.MotherDateOfBirth,111) AS MotherDateOfBirth,
	  SF.MotherQualification,
	  SF.MotherOccupation,
      SF.MotherContactNo,
      SF.MotherMail,
	  SF.MotherOfficeContactNo,
      SF.MotherOfficeAddress,
      SF.MotherIncome AS MotherAnnualIncome,    -- MantisId : 0029214  
	  ISNULL(SA.AdmissionCategoryId,0) AS AdmissionCategoryId,
	  AC.Title AS AdmissionCategory,
	  SAD.PreviousSchool,
	  CONVERT(VARCHAR(10), SAD.AdmissionDate, 111) AS AdmissionDate,
	  SAD.AdmissionFormNumber,
	  SAD.AdmissionNumber,
	  SAD.IsTransfer,
	  SAD.TransferFrom,
	  SAD.PassingYear,
	  SAD.IsTFWS,
	  SAD.IsCertificateHolder,
	  SAD.CertificateName,
	  AQM.Title AS AdmissionQuota,
	  CONVERT(VARCHAR(10),SAD.LeftDate, 111) AS LeftDate,
	  SA.IsBlackList,
	  SA.BlackListReason,
	  SA.DivisionOrder,
	  SA.ClassOrderIndex,
	  AD.RepeatCount,
	  ADI.BankAccountNumber,
	  ADi.BankBranch,
	  ADI.BankIFSCCode,
	  ADI.BankName,
	  ADI.BankAccountHolderName,
	  ESM.Title AS StudentBatchStatus,
	  EnrollmentStatusId AS StudentStatusId,
	  ADI.Class9BoardRegistrationNumber,
	  ADI.Class11BoardRegistrationNumber,
	  ADI.PassportNumber,
	  ADI.PassportType,
	  ADI.PassportCountryCode,
	CONVERT(VARCHAR(10),ADI.PassportIssueDate,111) AS PassportIssueDate,
	  ADI.PassportExpiryDate,
	  ISNULL(SS.SiblingName1,'') As SiblingName1,
	  ISNULL(SS.SiblingInstitue1,'') As SiblingInstitue1,
	  ISNULL(SS.SiblingClass1,'') As SiblingClass1,
	  ISNULL(SS.SiblingAge1,'') As SiblingAge1,
	  ISNULL(SS.SiblingRelation1,'') As SiblingRelation1,
	  ISNULL(SS.SiblingName2,'') As SiblingName2,
	  ISNULL(SS.SiblingInstitue2,'') As SiblingInstitue2,
	  ISNULL(SS.SiblingClass2,'') As SiblingClass2,
	  ISNULL(SS.SiblingAge2,'') As SiblingAge2,
	  ISNULL(SS.SiblingRelation2,'') As SiblingRelation2,
	  ISNULL(SS.SiblingName3,'') As SiblingName3,
	  ISNULL(SS.SiblingInstitue3,'') As SiblingInstitue3,
	  ISNULL(SS.SiblingClass3,'') As SiblingClass3,
	  ISNULL(SS.SiblingAge3,'') As SiblingAge3,
	  ISNULL(SS.SiblingRelation3,'') As SiblingRelation3,
	  ISNULL(HHM.Title,'') AS HostelName,
	  ISNULL(HRM.Title,'') AS Room,
	  ISNULL(HBM.Title,'') AS BuildingName,
	  ISNULL(HAR.BedNumber,'') AS BedNumber,
	  
	  ISNULL(SM.StudentGroupId,0) AS StudentGroupId,
	  ISNULL(SM.StudentOtherGroupId,0) AS StudentOtherGroupId,
	  
	  SGM.Title AS StudentGroup,
	  SOGM.Title AS StudentOtherGroup,
	  SO.BirthState,
	  So.BirthCountry,
	  ISNULL(RT.BusRouteId,0) AS BusRouteId,
	  ISNULL(RT.PickupTime,'') AS PickupTime,
	  ISNULL(RT.Droptime,'') AS Droptime,
	  ISNULL(RT.BusRoute,'' )AS BusRoute,
	   CD.SSCBoard,
	   CD.SSCSchool,
	  CD.SSCPassingYear,
	  CD.SSCObtainMarks,
	  CD.SSCOutOfMarks,
	  CD.SSCPercentage,
	  CD.SSCMathMarks,
	  CD.SSCMathOutOfMarks,
	  CD.HSCBoard,
	  CD.HSCSchool,
	  CD.HSCPassingYear,
	  CD.HSCObtainMarks,
	  CD.HSCOutOfMarks,
	  CD.HSCPercentage,
	  
	  CD.HSCBiologyMarks,
	  CD.HSCBiologyOutOfMarks,
	 
	  CD.HSCChemistryMarks,
	  CD.HSCChemistryOutOfMarks,
	  CD.HSCMathMarks,
	  CD.HSCMathOutOfMarks,
	  cd.HSCPhysicsMarks,
	  CD.HSCPhysicsOutOfMarks,
	  CD.PCMMarks,
	  CD.PCBMarks,
	  CD.JEERollNo,
	  CD.JEEMeritNo,
	  CD.JEEMeritMarks,
	  CD.JEEOutOfMarks,
	  CD.DiplomaBoard,
	  CD.DiplomaCollege,
	  CD.DiplomaPassingYear,
	  CD.DiplomaObtainMarks,
	  CD.DiplomaOutOfMarks,
	  CD.DiplomaPercentage,
	  CD.ApplicationNo,
	  CD.AdmissionRound,
	  CD.MaritalStatusId,
	  EX.Id As ExemptionTypeId,
	  SAD.PreviousStandard,
	  SO.ReferenceRemark,
	  SAD.LCNumber,
	  CM.Title AS AdmissionStandard,
	  SH.IsAllergy,
      SH.AllergyRemark,
      SF.RelativeName,
      SF.RelativeDateOfBirth,
      SF.RelativeOccupation,
      SF.RelativeQualification,
      SF.RelativeContactNo,
      SF.RelativeMail,
      SF.RelativeOfficeAddress,      
      SF.RelativeIncome AS RelativeAnnualIncome,		-- MantisId : 0029214
      STUFF(
           COALESCE(',' + NULLIF(SC.PresentStreetOne, ''), '')  + 
           COALESCE(',' + NULLIF(SC.PresentStreetTwo, ''), '') +
           COALESCE(',' + NULLIF(SC.PresentStreetThree, ''), '') +
           COALESCE(',' + NULLIF(SC.PresentCountry, ''), '') +
           COALESCE(',' + NULLIF(SC.PresentState, ''), '') +
           COALESCE(',' + NULLIF(SC.PresentCity, ''), '') +
            COALESCE(',' + NULLIF(SC.PresentZipCode, ''), '') +
            COALESCE(',' + NULLIF(SC.PresentContactNo, ''), ''),
           1, 1, '') AS PresentAddress,
      STUFF(
           COALESCE(',' + NULLIF(SC.PermanentStreetOne, ''), '')  + 
           COALESCE(',' + NULLIF(SC.PermanentStreetTwo, ''), '') +
           COALESCE(',' + NULLIF(SC.PermanentStreetThree, ''), '') +
           COALESCE(',' + NULLIF(SC.PermanentCountry, ''), '') +
           COALESCE(',' + NULLIF(SC.PermanentState, ''), '') +
           COALESCE(',' + NULLIF(SC.PermanentCity, ''), '') +
            COALESCE(',' + NULLIF(SC.PermanentZipCode, ''), '') +
            COALESCE(',' + NULLIF(SC.PermanentContactNo, ''), ''),
           1, 1, '') AS PermanentAddress ,
      STUFF(
           COALESCE(',' + NULLIF(SC.EmergencyStreetOne, ''), '')  + 
           COALESCE(',' + NULLIF(SC.EmergencyStreetTwo, ''), '') +
           COALESCE(',' + NULLIF(SC.EmergencyStreetThree, ''), '') +
           COALESCE(',' + NULLIF(SC.EmergencyCountry, ''), '') +
           COALESCE(',' + NULLIF(SC.EmergencyState, ''), '') +
           COALESCE(',' + NULLIF(SC.EmergencyCity, ''), '') +
            COALESCE(',' + NULLIF(SC.EmergencyZipCode, ''), '') +
            COALESCE(',' + NULLIF(SC.EmergencyContactNo, ''), ''),
           1, 1, '') AS EmergencyAddress ,
	  (SELECT STUFF( (SELECT DISTINCT ',' + CTM.Title FROM sStudent.tStudentCustomTag SCT INNER JOIN sStudent.tCustomTagMaster CTM ON SCT.CustomTagId=CTM.Id 
							WHERE SCT.StudentId = SA.StudentId FOR XML PATH ('')), 1, 1, '')  AS CustomTag FROM sStudent.tStudentMaster SM1 WHERE SM1.Id = SM.Id GROUP BY SM1.Id) AS CustomTag,
		STM.Title AS RecordTag,
		SC.IsStopApplicationAccess,
		CASE WHEN ISNULL(SO.DateOfBirth, '') = '' THEN '' ELSE (SELECT [sCommon].[fGetDateOfBirthInWord](SO.DateOfBirth)) END AS Age,
		RT.PickupPoint as PickupPoint,
		ISNULL(SA.LocalName, '') AS LocalName -- Added for mantis : 34211

  FROM sStudent.vStudentAcademic SA
    INNER JOIN sStudent.tStudentMaster SM ON SM.Id = SA.StudentId
	LEFT JOIN sStudent.tStudentAdmission SAD ON SAD.StudentId=SA.StudentId
	LEFT JOIN sSetup.tClassMaster CM ON CM.Id=SAD.AdmissionStandardId
	LEFT JOIN sCommon.tAdmissionQuotaMaster AQM ON AQM.Id = SAD.AdmissionQuotaId
	LEFT JOIN sSetup.tBatchMaster BM ON BM.Id=SA.BatchId
	LEFT JOIN sStudent.tStudentOther SO ON SO.StudentId=SA.StudentId
	LEFT JOIN sStudent.tStudentCommunication SC ON SC.StudentId=SA.StudentId
	--LEFT JOIN sTransport.tAssignRoute AR ON AR.SourceId=SA.StudentId
	LEFT JOIN sCommon.tTransportModeMaster TM ON TM.Id=SO.TransportModeId
	LEFT JOIN sCommon.tGenderMaster GM ON GM.Id=SO.GenderId
	LEFT JOIN sCommon.tBloodGroupMaster BGM ON BGM.Id=SO.BloodGroupId
	LEFT JOIN sCommon.tCastCategoryMaster CCM ON CCM.Id=SO.CastCategoryId
	LEFT JOIN sCommon.tReligionMaster RM ON RM.Id=SO.ReligionId
	LEFT JOIN sActivity.tHouseMaster HM ON HM.Id=SO.HouseId
	LEFT JOIN sCommon.tMotherTongueMaster MTM ON MTM.Id=SO.MotherTongueId
	LEFT JOIN sCommon.tCountryMaster NM ON NM.Id=SO.NationalityId
	--LEFT JOIN sTransport.tBusRouteDestinationMaster BDM ON BDM.Id=AR.PickupDestinationId
	LEFT JOIN sStudent.tStudentFamily SF ON SF.StudentId=SA.StudentId
	LEFT JOIN sStudent.tStudentHealth SH ON SH.StudentId=SA.StudentId
	LEFT JOIN sStudent.tAdmissionCategoryMaster AC ON AC.Id=SA.AdmissionCategoryId
	LEFT JOIN sStudent.tAcademicDetail AD ON AD.AcademicId = SA.AcademicId
	LEFT JOIN sStudent.tStudentadditional ADI ON ADI.StudentId = SA.StudentId
	LEFT JOIN sStudent.tEnrollmentStatusMaster ESM ON ESM.Id = SM.EnrollmentStatusId
	LEFT JOIN sStudent.tStudentSibling SS ON SS.StudentId = SA.StudentId
	LEFT JOIN sHostel.tAssignRoom HAR ON HAR.SourceId = SM.Id AND HAR.OrgGroupBatchId = SA.OrgGroupBatchId
	LEFT JOIN sHostel.tRoomMaster HRM ON HRM.Id = HAR.RoomId
	LEFT JOIN sHostel.tBuildingMaster HBM ON HRM.BuildingId = HBM.Id 
	LEFT JOIN sHostel.tHostelMaster HHM ON HHM.Id = HRM.HostelId
	
	LEFT JOIN sStudent.tStudentGroupMaster SGM ON SGM.Id=SM.StudentGroupId
	LEFT JOIN sStudent.tStudentOtherGroupMaster SOGM ON SOGM.Id=SM.StudentOtherGroupId
	--LEFT JOIN sTransport.tBusRouteMaster BRM ON BRM.Id=BDM.BusRouteId
	LEFT JOIN sStudent.tCollegeDetail CD ON CD.StudentId = SA.StudentId
	LEFT JOIN sFee.tExemptionTypeMaster EX ON Ex.Id=SA.ExemptionTypeId
   LEFT JOIN
	(SELECT AR.SourceId,AR.DropDestinationId AS BusRouteDestinationId,BDM2.Title AS BusRouteDestination,AR.PickupDestinationId AS BusRoutePickupDestinationId,BDM.Title AS PickupPoint, ISNULL(BRM.Id,0) AS BusRouteId,
	  ISNULL(BDM.PickupTime,'') AS PickupTime,
	  ISNULL(BDM.Droptime,'') AS Droptime,
	  ISNULL(BRM.Title,'' )AS BusRoute,BRM.OrgGroupBatchId FROM sTransport.tAssignRoute AR 
    INNER JOIN sTransport.tBusRouteDestinationMaster BDM ON BDM.Id=AR.PickupDestinationId
	LEFT JOIN sTransport.tBusRouteDestinationMaster BDM2 ON BDM2.Id=AR.DropDestinationId
    INNER JOIN sTransport.tBusRouteMaster BRM ON BRM.Id=BDM.BusRouteId AND  AR.SourceTypeId=2003  )  RT 
    ON RT.SourceId = SA.StudentId     AND RT.OrgGroupBatchId =SA.OrgGroupBatchId
	INNER JOIN sCommon.tStatusMaster STM ON STM.Id = SA.RecordStatusId AND STM.StatusTypeId=102
	INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = SA.OrgId
	INNER JOIN sSetup.tDepartmentMaster DM ON DM.Id = SA.DepartmentId
GO
PRINT N'Creating Function [sAttendance].[fGetAbsentDatesDateWise]...';


GO

CREATE FUNCTION [sAttendance].[fGetAbsentDatesDateWise]
(
    @AcademicID BIGINT,
    @AttendanceTypeId SMALLINT,
    @FromDate DATE,
    @ToDate DATE,
	@SubjectId Int,
	@PeriodNo SmallInt
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @AbsentDates NVARCHAR(MAX);

    SELECT @AbsentDates = STUFF(
        (SELECT ',' + CONVERT(VARCHAR(10), AttendanceDate, 103)
         FROM sAttendance.tStudentRegister AS SR
         WHERE SR.AcademicID = @AcademicID
           AND SR.IsPresent = 0
           AND SR.AttendanceDate >= @FromDate
		   AND SR.AttendanceDate <= @ToDate
           AND SR.AttendanceTypeId = @AttendanceTypeId
		   AND (SR.SubjectId = @SubjectId OR @SubjectId = 0)
		   AND (SR.PeriodNo = @PeriodNo OR @PeriodNo = 0)
		   ORDER BY AttendanceDate
         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
        , 1, 1, '');

    RETURN @AbsentDates;
END;
GO
PRINT N'Creating Function [sHrm].[fEmployeeHolidayDates]...';


GO


CREATE FUNCTION [sHrm].[fEmployeeHolidayDates](
    @rEmployeeId bigint,
    @rDepartmentId int, 
    @rForYear int,
    @rForMonth int,
    @rBatchId int,
    @rOrgId int
)
RETURNS @HolidayDays TABLE (
    HolidayDate DATE
)
AS 
BEGIN
    DECLARE @FirstDate DATE
    DECLARE @LastDate DATE

    SET @FirstDate = CONVERT(DATE, CAST(@rForYear AS VARCHAR(4)) + '-' + RIGHT('0' + CAST(@rForMonth AS VARCHAR(2)), 2) + '-01')
    SET @LastDate = DATEADD(DAY, -1, DATEADD(MONTH, 1, @FirstDate))

    ;WITH HolidayEvents AS (
        SELECT ES.FromDate, ES.ToDate
        FROM sActivity.tEventSchedule ES
        INNER JOIN sActivity.tEventAssign EA ON EA.EventScheduleId = ES.Id AND EA.SourceTypeId IN (2004, 95)
        LEFT JOIN sHrm.vEmployeeCommunication EC ON EC.EmployeeId = EA.SourceId AND (EC.EmployeeId = @rEmployeeId OR @rEmployeeId = 0)
        WHERE ES.BatchId = @rBatchId
          AND ((SourceId = @rDepartmentId) OR (@rDepartmentId = 0))
          AND MONTH(ES.FromDate) = @rForMonth AND YEAR(ES.FromDate) = @rForYear
          AND MONTH(ES.ToDate) = @rForMonth AND YEAR(ES.ToDate) = @rForYear
          AND ES.IsHoliday = 1
    ),
    DateRange AS (
        SELECT HE.FromDate AS HolidayDate, HE.ToDate
        FROM HolidayEvents HE
        UNION ALL
        SELECT DATEADD(DAY, 1, DR.HolidayDate), DR.ToDate
        FROM DateRange DR
        WHERE DATEADD(DAY, 1, DR.HolidayDate) <= DR.ToDate
    )
    INSERT INTO @HolidayDays
    SELECT DISTINCT HolidayDate
    FROM DateRange
    WHERE HolidayDate BETWEEN @FirstDate AND @LastDate
    OPTION (MAXRECURSION 0);

    RETURN;
END
GO
PRINT N'Altering Procedure [sAdmission].[pExportAllAdmissionFormDetail]...';


GO
ALTER PROCEDURE [sAdmission].[pExportAllAdmissionFormDetail]
	 @rAdmissionFormId bigint = 0,
	 @rFormNo int=0,
	 @rForClassId int=0,
	 @rBatchId int=0,
	 @rStatusId smallint='',
	 @rOrgId int,
	 @rFirstName nvarchar(200)='',
	 @rLastName nvarchar(200)='',
 
	 @rFromDate datetime= '1900-01-01',
	 @rToDate datetime = '9990-12-31',
	 @rDepartmentId int=0,
	 @rDataEntryStatusId smallint=0
	 	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	
	SET @rToDate = DATEADD(d,1,@rToDate)		 

BEGIN 
		--IF @rIsSearch = 1
		--BEGIN
		--SET @rFirstName = ISNULL(@rFirstName,'')
		--SET @rLastName = ISNULL(@rLastName,'')
		--	SET @rFirstName = sCommon.fSearchStringGet(@rFirstName)
		--	SET @rLastName = sCommon.fSearchStringGet(@rLastName)
		--END
	CREATE Table #tempAdmissionForm
	(
		AdmissionFormId bigint,
		FormNo int,
		FirstName nvarchar(500),
		MiddleName nvarchar(500),
		LastName nvarchar(500),
		Name nvarchar(500),
		ClassId int,
		ClassName nvarchar(500),
		ForBatch nvarchar(500),
		DateOfBirth date,
		--StatusName nvarchar(200),
		ProcessStatus nvarchar(100),
		FormStatus nvarchar(500),
		StreetOne nvarchar(500),
		StreetTwo nvarchar(500),
		StreetThree nvarchar(500),
		Country nvarchar(500),
		State nvarchar(500),
		City nvarchar(500),
		ZipCode nvarchar(500),
		ContactNo nvarchar(500),
		ContactEmail nvarchar(500),
		Religion nvarchar(500),
		Nationality nvarchar(500),
		MotherTongue nvarchar(500),
		OtherInformation nvarchar(500),
		IsPysicalDeformity	bit	,
		PysicalDeformityRemark	nvarchar(500)	,
		IsMajorIllness	bit	,
		MajorIllnessRemark	nvarchar(500)	,
		IsAllergy	bit	,
		AllergyRemark	nvarchar(500)	,
		BirthPlace	nvarchar(500)	,
		BirthTaluka	nvarchar(500)	,
		BirthDistrict	nvarchar(500),
		BloodGroupName	nvarchar(500)	,
		AdhaarNumber	nvarchar(500)	,
		IsHostelRequired	nvarchar(500)	,
		IsTransportRequired	nvarchar(500)	,
		Remark nvarchar(500),
		PreviousSchool nvarchar(500),
		MeritMarks decimal(6,2),
		MeritGrade nvarchar(500),
		Gender nvarchar(500),
		
		FamilyCast nvarchar(500),
		FamilySubCast nvarchar(500),
		 
		AdmissionFormDate datetime,
		FatherName nvarchar(500),
		--FatherDateOfBirth date,
		FatherOccupation nvarchar(500),
		FatherQualification nvarchar(500),
		FatherDesignation  nvarchar(500),
		FatherOrganization nvarchar(500),
		FatherOfficePhone nvarchar(500),
		FatherContactNo nvarchar(500),
		FatherMail nvarchar(500),
		FatherOfficeAddress nvarchar(500),
		FatherIncome int	,
		FatherAadharNo nvarchar(20),
		MotherName nvarchar(500),
		--MotherDateOfBirth date ,
		MotherOccupation nvarchar(500),
		MotherQualification nvarchar(500),
		MotherDesignation nvarchar(500),
		MotherOrganization nvarchar(500),
		MotherOfficePhone nvarchar(500),
		MotherContactNo nvarchar(500),
		MotherMail nvarchar(500),
		MotherOfficeAddress nvarchar(500), 
		MotherIncome int,
		RelationshipName nvarchar(500),
		GuardianName nvarchar(500),
		--GuardianDateOfBirth date,
		GuardianOccupation nvarchar(500),
		GuardianQualification nvarchar(500),
		GuardianOrganization nvarchar(500),
		GuardianDesignation nvarchar(500),
		GuardianOfficePhone nvarchar(500),
		GuardianContactNo nvarchar(500),
		GuardianMail nvarchar(500),
		GuardianOfficeAddress nvarchar(500),
		GuardianIncome int,
		FatherPassOutYear nvarchar(500),
		MotherPassOutYear nvarchar(500),
		RelativePassOutYear nvarchar(500),
		CustomField1Answer  nvarchar(500),
		CustomField2Answer  nvarchar(500),
		CustomField3Answer  nvarchar(500),
		CastCategory nvarchar(50),
		DataEntryMode nvarchar(20),
		StatusName nvarchar(200) -- Added for mantis : 34323 by Nilesh
		
	)
	Insert into #tempAdmissionForm
	(
		AdmissionFormId,
		FormNo ,
		FirstName ,
		MiddleName ,
		LastName ,
		Name ,
		ClassId,
		ClassName ,
		ForBatch ,
		DateOfBirth ,
		--StatusName ,
		ProcessStatus ,
		FormStatus ,
		StreetOne ,
		StreetTwo ,
		StreetThree,
		Country,
		State ,
		City ,
		ZipCode ,
		ContactNo ,
		ContactEmail,
		Religion,
		Nationality,
		MotherTongue,
		OtherInformation,
		IsPysicalDeformity,
		PysicalDeformityRemark,
		IsMajorIllness,
		MajorIllnessRemark,
		IsAllergy,
		AllergyRemark,
		BirthPlace,
		BirthTaluka,
		BirthDistrict,
		BloodGroupName,
		AdhaarNumber,
		IsHostelRequired,
		IsTransportRequired,
		Remark ,
		PreviousSchool,
		MeritMarks ,
		MeritGrade,
		Gender,
		
		FamilyCast ,
		FamilySubCast,
		 
		AdmissionFormDate,
		FatherName ,
		--FatherDateOfBirth ,
		FatherOccupation,
		FatherQualification ,
		FatherDesignation,
		FatherOrganization,
		FatherOfficePhone,
		FatherContactNo  ,
		FatherMail  ,
		FatherOfficeAddress   ,
		FatherIncome  	,
		FatherAadharNo,
		MotherName  ,
		--MotherDateOfBirth   ,
		MotherOccupation  ,
		MotherQualification  ,
		MotherDesignation,
		MotherOrganization,
		MotherOfficePhone,
		MotherContactNo  ,
		MotherMail  ,
		MotherOfficeAddress  , 
		MotherIncome  ,
		RelationshipName  ,
		GuardianName  ,
		--GuardianDateOfBirth  ,
		GuardianOccupation  ,
		GuardianQualification  ,
		GuardianDesignation,
		GuardianOrganization,
		GuardianOfficePhone,
		GuardianContactNo  ,
		GuardianMail  ,
		GuardianOfficeAddress   ,
		GuardianIncome  ,
		FatherPassOutYear,
		MotherPassOutYear,
		RelativePassOutYear,
		CustomField1Answer,
		CustomField2Answer,
		CustomField3Answer,
		CastCategory,
		DataEntryMode,
		StatusName -- Added for mantis : 34323 by Nilesh
	)
	SELECT 
			  AF.Id
			,AF.FormNo
			,AF.FirstName
			,AF.MiddleName
			,AF.LastName
			,AF.FirstName+' '+AF.LastName as Name
			--,AF.ForClassId
			--,AF.BatchId
			,CM.Id AS ClassId
			,CM.Title AS ClassName
			,AF.ForBatch
			,ISNULL(AF.DateOfBirth,'') AS DateOfBirth
			--,AF.DateOfBirthInWord
			--,AF.StatusId
			--,SM.Title as StatusName
			,SM1.Title AS DataEntryStatus
			,ISNULL(ASM.Title,'') AS FormStatus
			,AF.StreetOne
			,AF.StreetTwo
			,AF.StreetThree
			,AF.Country
			,AF.State
			,AF.City
			,AF.ZipCode
			,AF.ContactNo
			,AF.ContactEmail,
			RM.Title AS Religion,
			AF.Nationality,
			AF.MotherTongue,
			AF.OtherInformation,
			AF.IsPysicalDeformity,
			AF.PysicalDeformityRemark,
			AF.IsMajorIllness,
			AF.MajorIllnessRemark,
			AF.IsAllergy,
			AF.AllergyRemark,
			AF.BirthPlace,
			AF.BirthTaluka,
			AF.BirthDistrict,
			BGm.Title,
			AF.AdhaarNumber,
			CAse WHEn ISnull(AF.IsHostelRequired,0)=0 THEN 'No' Else 'Yes' END AS IsHostelRequired,
			CAse WHEn ISnull(AF.IsTransportRequired,0)=0 THEN 'No' Else 'Yes' END AS IsTransportRequired
			
			,AF.Remark
			,AF.PreviousSchool
			,AF.MeritMarks
			,AF.MeritGrade
			--,AF.GenderId
			,ISNULL(GM.Title,'') AS Gender
			--,ISNULL(AF.ReligionId,0) AS ReligionId
			,AF.FamilyCast
			,AF.FamilySubCast
			--,ISNULL(AF.CastCategoryId,0) AS CastCategoryId
			,AF.CreatedDateTime AS AdmissionFormDate,
			--,ISNULL(AF.StudentId,0) AS StudentId
			--,AF.StudentPhoto,
			ISNULL(AFFD.FatherName,'') AS FatherName,
			--Isnull(AFFD.FatherDateOfBirth,'') AS FatherDateOfBirth,
			Isnull(AFFD.FatherOccupation,'') AS FatherOccupation,
			Isnull(AFFD.FatherQualification,'') AS FatherQualification,
			Isnull(AFFD.FatherDesignation,'') AS FatherDesignation,
			Isnull(AFFD.FatherOrganization,'') AS FatherOrganization,
			Isnull(AFFD.FatherOfficePhone,'') AS FatherOfficePhone,
			Isnull(AFFD.FatherContactNo,'') AS FatherContactNo,
			Isnull(AFFD.FatherMail,'') AS FatherMail,
			Isnull(AFFD.FatherOfficeAddress,'') As FatherOfficeAddress,
			Isnull(AFFD.FatherIncome,'') as FatherIncome,
			Isnull(AFFD.FatherAdhaarNumber,'') as FatherAadharNo,
			Isnull(AFFD.MotherName,'') AS MotherName,
			--iSNULL(AFFD.MotherDateOfBirth,'')AS MotherDateOfBirth,
			isnull(AFFD.MotherOccupation,'') AS MotherOccupation,
			isnull(AFFD.MotherQualification,'') AS MotherQualification,
			
			Isnull(AFFD.MotherDesignation,'') AS MotherDesignation,
			Isnull(AFFD.MotherOrganization,'') AS MotherOrganization,
			Isnull(AFFD.MotherOfficePhone,'') AS MotherOfficePhone,
			
			isnull(AFFD.MotherContactNo,'') as MotherContactNo,
			isnull(AFFD.MotherMail,'') As MotherMail,
			isnull(AFFD.MotherOfficeAddress,'') as MotherOfficeAddress,
			isnull(AFFD.MotherIncome,'') as MotherIncome,
		 
			isnull(AFFD.RelationshipName,'') as RelationshipName,
			isnull(AFFD.RelativeName,'')  AS GuardianName ,
			--isnull(AFFD.RelativeDateOfBirth ,'')AS GuardianDateOfBirth,
			isnull(AFFD.RelativeOccupation,'') AS GuardianOccupation,
			isnull(AFFD.RelativeQualification,'') AS GuardianQualification,
			
			Isnull(AFFD.RelativeDesignation,'') AS GuardianDesignation,
			Isnull(AFFD.RelativeOrganization,'') AS GuardianOrganization,
			Isnull(AFFD.RelativeOfficePhone,'') AS GuardianOfficePhone,
			
			isnull(AFFD.RelativeContactNo,'') AS GuardianContactNo,
			isnull(AFFD.RelativeMail,'')  AS GuardianMail,
			isnull(AFFD.RelativeOfficeAddress,'') AS GuardianOfficeAddress,
			isnull(AFFD.RelativeIncome,'') AS GuardianIncome  ,
			isnull(AFFD.FatherPassOutYear,'') AS FatherPassOutYear,
			isnull(AFFD.MotherPassOutYear,'')As MotherPassOutYear,
			isnull(AFFD.RelativePassOutYear,'') As RelativePassOutYear,
			isnull(AF.CustomField1Answer,'') AS CustomField1Answer,
			isnull(AF.CustomField2Answer,'')As CustomField2Answer,
			isnull(AF.CustomField3Answer,'') As CustomField3Answer,
   			ISNULL(CCM.Title,'') AS CastCategory,
			CASE WHEN AF.DataEntryModeId = 561 THEN 'Online' ELSE 'Offline' END AS DataEntryMode,
			SM.Title as StatusName -- Added for mantis : 34323 by Nilesh
  FROM 
            sAdmission.tAdmissionForm AF
			INNER JOIN sCommon.tStatusMaster SM ON SM.Id=AF.StatusId
			INNER JOIN sCommon.tStatusMaster SM1 ON SM1.Id=AF.DataEntryStatusId
			INNER JOIN sSetup.tClassMaster CM ON CM.Id=AF.ForClassId			
			LEFT JOIN sCommon.tGenderMaster GM ON GM.Id=AF.GenderId
			LEFT JOIN sAdmission.tAdmissionFormSelected AFS ON AF.Id = AFS.AdmissionFormId
			LEFT JOIN sAdmission.tAdmissionStatusMaster ASM ON ASM.Id = AFS.AdmissionStatusId
			LEFT JOIN sAdmission.tAdmissionFormFamilyDetail AFFD ON AFFD.AdmissionFormId = AF.Id
			LEFT JOIN sCommon.tReligionMaster RM on RM.Id= AF.religionId
			LEFT JOIN sCommon.tBloodGroupMaster BGM on BGM.Id= AF.BloodGroupId
			--LEFT JOIN sAdmission.tAdmissionFormSibling ASB ON ASB.AdmissionFormId = AF.Id
			LEFT JOIN sCommon.tCastCategoryMaster CCM ON CCM.Id=AF.CastCategoryId
			 
  WHERE 
  
      ((AF.Id = @rAdmissionFormId) OR (@rAdmissionFormId = 0))
		AND ((AF.FormNo=@rFormNo) OR (@rFormNo=0))
		AND ((AF.FirstName like @rFirstName) OR (@rFirstName=''))
		AND ((AF.LastName like @rLastName) OR (@rLastName=''))
		AND ((AF.CreatedDateTime>= @rFromDate) OR (@rFromDate=''))
		AND ((AF.CreatedDateTime < @rToDate) OR (@rToDate=''))
		AND ((AF.StatusId=@rStatusId) OR (@rStatusId=0))
		AND ((AF.ForClassId=@rForClassId) OR (@rForClassId=0)) 
		AND ((AF.BatchId=@rBatchId) OR (@rBatchId=0)) 
		--AND ((AF.MeritMarks=@rMeritMarks) OR (@rMeritMarks=0))
		
		AND ((AF.StatusId=@rStatusId) OR (@rStatusId = ''))
		AND ((CM.DepartmentId=@rDepartmentId) OR (@rDepartmentId = 0))
		AND AF.OrgId=@rOrgId
		AND ((AF.DataEntryStatusId=@rDataEntryStatusId) OR (@rDataEntryStatusId = 0))
 
 create table #tempStudentAcademic
(
	AdmissionFormId bigint,
	 
	
	StudentAcademicYear1 nvarchar(500),	StudentAdmissionClass1 nvarchar(500),	StudentInstituteName1 nvarchar(500),	StudentInstituteCity1 nvarchar(500),	StudentInstituteAffiliaton1 nvarchar(500),	StudentLanguage1 nvarchar(500),	StudentResult1 Nvarchar(500),
	StudentAcademicYear2 nvarchar(500),	StudentAdmissionClass2 nvarchar(500),	StudentInstituteName2 nvarchar(500),	StudentInstituteCity2 nvarchar(500),	StudentInstituteAffiliaton2 nvarchar(500),	StudentLanguage2 nvarchar(500),	StudentResult2 Nvarchar(500),
	StudentAcademicYear3 nvarchar(500),	StudentAdmissionClass3 nvarchar(500),	StudentInstituteName3 nvarchar(500),	StudentInstituteCity3 nvarchar(500),	StudentInstituteAffiliaton3 nvarchar(500),	StudentLanguage3 nvarchar(500),	StudentResult3 Nvarchar(500),
	StudentAcademicYear4 nvarchar(500),	StudentAdmissionClass4 nvarchar(500),	StudentInstituteName4 nvarchar(500),	StudentInstituteCity4 nvarchar(500),	StudentInstituteAffiliaton4 nvarchar(500),	StudentLanguage4 nvarchar(500),	StudentResult4 Nvarchar(500),
	StudentAcademicYear5 nvarchar(500),	StudentAdmissionClass5 nvarchar(500),	StudentInstituteName5 nvarchar(500),	StudentInstituteCity5 nvarchar(500),	StudentInstituteAffiliaton5 nvarchar(500),	StudentLanguage5 nvarchar(500),	StudentResult5 Nvarchar(500),
	StudentAcademicYear6 nvarchar(500),	StudentAdmissionClass6 nvarchar(500),	StudentInstituteName6 nvarchar(500),	StudentInstituteCity6 nvarchar(500),	StudentInstituteAffiliaton6 nvarchar(500),	StudentLanguage6 nvarchar(500),	StudentResult6 Nvarchar(500),
	StudentAcademicYear7 nvarchar(500),	StudentAdmissionClass7 nvarchar(500),	StudentInstituteName7 nvarchar(500),	StudentInstituteCity7 nvarchar(500),	StudentInstituteAffiliaton7 nvarchar(500),	StudentLanguage7 nvarchar(500),	StudentResult7 Nvarchar(500),
	StudentAcademicYear8 nvarchar(500),	StudentAdmissionClass8 nvarchar(500),	StudentInstituteName8 nvarchar(500),	StudentInstituteCity8 nvarchar(500),	StudentInstituteAffiliaton8 nvarchar(500),	StudentLanguage8 nvarchar(500),	StudentResult8 Nvarchar(500),
	StudentAcademicYear9 nvarchar(500),	StudentAdmissionClass9 nvarchar(500),	StudentInstituteName9 nvarchar(500),	StudentInstituteCity9 nvarchar(500),	StudentInstituteAffiliaton9 nvarchar(500),	StudentLanguage9 nvarchar(500),	StudentResult9 Nvarchar(500),
	StudentAcademicYear10 nvarchar(500),	StudentAdmissionClass10 nvarchar(500),	StudentInstituteName10 nvarchar(500),	StudentInstituteCity10 nvarchar(500),	StudentInstituteAffiliaton10 nvarchar(500),	StudentLanguage10 nvarchar(500),	StudentResult10 Nvarchar(
500) 


	 
	

)
INsert into #tempStudentAcademic
(AdmissionFormId, 
StudentAcademicYear1  ,	StudentAdmissionClass1  ,	StudentInstituteName1  ,	StudentInstituteCity1  ,	StudentInstituteAffiliaton1   ,	StudentLanguage1  ,	StudentResult1  ,
	StudentAcademicYear2  ,	StudentAdmissionClass2  ,	StudentInstituteName2  ,	StudentInstituteCity2  ,	StudentInstituteAffiliaton2  ,	StudentLanguage2  ,	StudentResult2   ,
	StudentAcademicYear3   ,	StudentAdmissionClass3  ,	StudentInstituteName3  ,	StudentInstituteCity3  ,	StudentInstituteAffiliaton3  ,	StudentLanguage3  ,	StudentResult3  ,
	StudentAcademicYear4  ,	StudentAdmissionClass4  ,	StudentInstituteName4  ,	StudentInstituteCity4  ,	StudentInstituteAffiliaton4  ,	StudentLanguage4   ,	StudentResult4  ,
	StudentAcademicYear5  ,	StudentAdmissionClass5  ,	StudentInstituteName5  ,	StudentInstituteCity5  ,	StudentInstituteAffiliaton5  ,	StudentLanguage5  ,	StudentResult5  ,
	StudentAcademicYear6  ,	StudentAdmissionClass6  ,	StudentInstituteName6  ,	StudentInstituteCity6  ,	StudentInstituteAffiliaton6  ,	StudentLanguage6  ,	StudentResult6  ,
	StudentAcademicYear7  ,	StudentAdmissionClass7  ,	StudentInstituteName7  ,	StudentInstituteCity7  ,	StudentInstituteAffiliaton7  ,	StudentLanguage7   ,	StudentResult7  ,
	StudentAcademicYear8  ,	StudentAdmissionClass8  ,	StudentInstituteName8  ,	StudentInstituteCity8  ,	StudentInstituteAffiliaton8  ,	StudentLanguage8  ,	StudentResult8  ,
	StudentAcademicYear9  ,	StudentAdmissionClass9  ,	StudentInstituteName9  ,	StudentInstituteCity9  ,	StudentInstituteAffiliaton9  ,	StudentLanguage9  ,	StudentResult9  ,
	StudentAcademicYear10  ,	StudentAdmissionClass10  ,	StudentInstituteName10  ,	StudentInstituteCity10  ,	StudentInstituteAffiliaton10  ,	StudentLanguage10  ,	StudentResult10  
	
)

SELECT AdmissionFormId,
  ISNULL(max(case when    rn = 1  then  AcademicYear  end),'') StudentAcademicYear1,
  ISNULL(max(case when    rn = 1  then  AdmissionClass  end),'') StudentAdmissionClass1,
   ISNULL(max(case when    rn = 1  then  InstituteName  end),'') StudentInstituteName1,
  ISNULL(max(case when    rn = 1  then  InstituteCity  end),'') StudentInstituteCity1,
  ISNULL(max(case when    rn = 1  then  InstituteAffiliaton  end),'') StudentInstituteAffiliaton1,
  ISNULL(max(case when    rn = 1  then  Language  end),'') StudentLanguage1,
    ISNULL(max(case when    rn = 1  then  Result  end),'') StudentResult1,
     
  ISNULL(max(case when    rn = 2 then  AcademicYear  end),'') StudentAcademicYear2,
  ISNULL(max(case when    rn = 2  then  AdmissionClass  end),'') StudentAdmissionClass2,
   ISNULL(max(case when    rn = 2  then  InstituteName  end),'') StudentInstituteName2,
  ISNULL(max(case when    rn = 2  then  InstituteCity  end),'') StudentInstituteCity2,
  ISNULL(max(case when    rn = 2  then  InstituteAffiliaton  end),'') StudentInstituteAffiliaton2,
  ISNULL(max(case when    rn = 2  then  Language  end),'') StudentLanguage2,
    ISNULL(max(case when    rn = 2  then  Result  end),'') StudentResult2,

     
  ISNULL(max(case when    rn = 3  then  AcademicYear  end),'') StudentAcademicYear3,
  ISNULL(max(case when    rn = 3  then  AdmissionClass  end),'') StudentAdmissionClass3,
   ISNULL(max(case when    rn = 3  then  InstituteName  end),'') StudentInstituteName3,
  ISNULL(max(case when    rn = 3  then  InstituteCity  end),'') StudentInstituteCity3,
  ISNULL(max(case when    rn = 3  then  InstituteAffiliaton  end),'') StudentInstituteAffiliaton3,
  ISNULL(max(case when    rn = 3  then  Language  end),'') StudentLanguage3,
  ISNULL(max(case when    rn = 3  then  Result  end),'') StudentResult3,
     
  ISNULL(max(case when    rn = 4  then  AcademicYear  end),'') StudentAcademicYear4,
  ISNULL(max(case when    rn = 4  then  AdmissionClass  end),'') StudentAdmissionClass4,
   ISNULL(max(case when    rn = 4  then  InstituteName  end),'') StudentInstituteName4,
  ISNULL(max(case when    rn = 4  then  InstituteCity  end),'') StudentInstituteCity4,
  ISNULL(max(case when    rn = 4  then  InstituteAffiliaton  end),'') StudentInstituteAffiliaton4,
  ISNULL(max(case when    rn = 4  then  Language  end),'') StudentLanguage4,
  ISNULL(max(case when    rn = 4  then  Result  end),'') StudentResult4,
           
  ISNULL(max(case when    rn = 5  then  AcademicYear  end),'') StudentAcademicYear5,
  ISNULL(max(case when    rn = 5  then  AdmissionClass  end),'') StudentAdmissionClass5,
   ISNULL(max(case when    rn = 5  then  InstituteName  end),'') StudentInstituteName5,
  ISNULL(max(case when    rn = 5  then  InstituteCity  end),'') StudentInstituteCity5,
  ISNULL(max(case when    rn = 5  then  InstituteAffiliaton  end),'') StudentInstituteAffiliaton5,
  ISNULL(max(case when    rn = 5  then  Language  end),'') StudentLanguage5,
    ISNULL(max(case when    rn = 5  then  Result  end),'') StudentResult5,
   
  ISNULL(max(case when    rn = 6  then  AcademicYear  end),'') StudentAcademicYear6,
  ISNULL(max(case when    rn = 6  then  AdmissionClass  end),'') StudentAdmissionClass6,
   ISNULL(max(case when    rn = 6  then  InstituteName  end),'') StudentInstituteName6,
  ISNULL(max(case when    rn = 6  then  InstituteCity  end),'') StudentInstituteCity6,
  ISNULL(max(case when    rn = 6  then  InstituteAffiliaton  end),'') StudentInstituteAffiliaton6,
  ISNULL(max(case when    rn = 6  then  Language  end),'') StudentLanguage6,
  ISNULL(max(case when    rn = 6  then  Result  end),'') StudentResult6,
     
  ISNULL(max(case when    rn = 7  then  AcademicYear  end),'') StudentAcademicYear7,
  ISNULL(max(case when    rn = 7  then  AdmissionClass  end),'') StudentAdmissionClass7,
  ISNULL(max(case when    rn = 7  then  InstituteName  end),'') StudentInstituteName7,
  ISNULL(max(case when    rn = 7  then  InstituteCity  end),'') StudentInstituteCity7,
  ISNULL(max(case when    rn = 7  then  InstituteAffiliaton  end),'') StudentInstituteAffiliaton7,
  ISNULL(max(case when    rn = 7  then  Language  end),'') StudentLanguage7,
   ISNULL(max(case when    rn = 7  then  Result  end),'') StudentResult7,
  
  ISNULL(max(case when    rn = 8  then  AcademicYear  end),'') StudentAcademicYear8,
  ISNULL(max(case when    rn = 8  then  AdmissionClass  end),'') StudentAdmissionClass8,
   ISNULL(max(case when    rn = 8  then  InstituteName  end),'') StudentInstituteName8,
  ISNULL(max(case when    rn = 8  then  InstituteCity  end),'') StudentInstituteCity8,
  ISNULL(max(case when    rn = 8  then  InstituteAffiliaton  end),'') StudentInstituteAffiliaton8,
  ISNULL(max(case when    rn = 8  then  Language  end),'') StudentLanguage8,
  ISNULL(max(case when    rn = 8  then  Result  end),'') StudentResult8,
     
  ISNULL(max(case when    rn = 9  then  AcademicYear  end),'') StudentAcademicYear9,
  ISNULL(max(case when    rn = 9  then  AdmissionClass  end),'') StudentAdmissionClass9,
   ISNULL(max(case when    rn = 9  then  InstituteName  end),'') StudentInstituteName9,
  ISNULL(max(case when    rn = 9  then  InstituteCity  end),'') StudentInstituteCity9,
  ISNULL(max(case when    rn = 9  then  InstituteAffiliaton  end),'') StudentInstituteAffiliaton9,
  ISNULL(max(case when    rn = 9  then  Language  end),'') StudentLanguage9,
   ISNULL(max(case when    rn = 9  then  Result  end),'') StudentResult9,
     
  ISNULL(max(case when    rn = 10  then  AcademicYear  end),'') StudentAcademicYear10,
  ISNULL(max(case when    rn = 10 then  AdmissionClass  end),'') StudentAdmissionClass10,
   ISNULL(max(case when    rn = 10 then  InstituteName  end),'') StudentInstituteName10,
  ISNULL(max(case when    rn = 10  then  InstituteCity  end),'') StudentInstituteCity10,
  ISNULL(max(case when    rn = 10  then  InstituteAffiliaton  end),'') StudentInstituteAffiliaton10,
  ISNULL(max(case when    rn = 10  then  Language  end),'') StudentLanguage10,
    ISNULL(max(case when    rn = 10  then  Result  end),'') StudentResult10
                     
from
(
  select AP.AdmissionFormId,  AcademicYear, AdmissionClass, InstituteName, InstituteCity,InstituteAffiliaton,Language,Result
      , row_number() over(partition by AP.AdmissionFormId 
                          Order by AP.AdmissionFormId) rn
  from sAdmission.tAdmissionFormStudentAcademic AP
  INNER JOIN #tempAdmissionForm TAF ON TAF.AdmissionFormId = Ap.AdmissionFormId
  
) d
group by AdmissionFormId order by AdmissionFormId 

Create Table #tempFatherAcademic
(
	AdmissionFormId bigint,
	FatherAcademicYear1 nvarchar(500),
	FatherInstituteName1 nvarchar(500),
	FatherInstituteCity1 nvarchar(500),
	FatherInstituteAffilation1 nvarchar(500),
	FatherAdmissionClass1 nvarchar(500),
	FatherMediumStudy1 nvarchar(500),
	
	FatherAcademicYear2 nvarchar(500),
	FatherInstituteName2 nvarchar(500),
	FatherInstituteCity2 nvarchar(500),
	FatherInstituteAffilation2 nvarchar(500),
	FatherAdmissionClass2 nvarchar(500),
	FatherMediumStudy2 nvarchar(500),
	
	
	FatherAcademicYear3 nvarchar(500),
	FatherInstituteName3 nvarchar(500),
	FatherInstituteCity3 nvarchar(500),
	FatherInstituteAffilation3 nvarchar(500),
	FatherAdmissionClass3 nvarchar(500),
	FatherMediumStudy3 nvarchar(500),
	
	
	
	FatherAcademicYear4 nvarchar(500),
	FatherInstituteName4 nvarchar(500),
	FatherInstituteCity4 nvarchar(500),
	FatherInstituteAffilation4 nvarchar(500),
	FatherAdmissionClass4 nvarchar(500),
	FatherMediumStudy4 nvarchar(500),
	
	FatherAcademicYear5 nvarchar(500), 
	FatherInstituteName5 nvarchar(500), 
	FatherInstituteCity5 nvarchar(500),
    FatherInstituteAffilation5 nvarchar(500),
    FatherAdmissionClass5 nvarchar(500),
    FatherMediumStudy5 nvarchar(500),
	
	MotherAcademicYear1 nvarchar(500),
	MotherInstituteName1 nvarchar(500),
	MotherInstituteCity1 nvarchar(500),
	MotherInstituteAffilation1 nvarchar(500),
	MotherMediumStudy1 nvarchar(500),
	MotherAdmissionClass1 nvarchar(500),
	
	MotherAcademicYear2 nvarchar(500),
	MotherInstituteName2 nvarchar(500),
	MotherInstituteCity2 nvarchar(500),
	MotherInstituteAffilation2 nvarchar(500),
	MotherMediumStudy2 nvarchar(500),
	MotherAdmissionClass2 nvarchar(500),
	
	MotherAcademicYear3 nvarchar(500),
	MotherInstituteName3 nvarchar(500),
	MotherInstituteCity3 nvarchar(500),
	MotherInstituteAffilation3 nvarchar(500),
	MotherMediumStudy3 nvarchar(500),
	MotherAdmissionClass3 nvarchar(500),
	
	
	MotherAcademicYear4 nvarchar(500),
	MotherInstituteName4 nvarchar(500),
	MotherInstituteCity4 nvarchar(500),
	MotherInstituteAffilation4 nvarchar(500),
	MotherMediumStudy4 nvarchar(500),
	MotherAdmissionClass4 nvarchar(500),
	
	MotherAcademicYear5 nvarchar(500), 
	MotherInstituteName5 nvarchar(500), 
	MotherInstituteCity5 nvarchar(500), 
	MotherInstituteAffilation5 nvarchar(500), 
	MotherMediumStudy5 nvarchar(500), 
	MotherAdmissionClass5 nvarchar(500) ,
	
	GuardianAcademicYear1 nvarchar(500),
	GuardianInstituteName1 nvarchar(500),
	GuardianInstituteCity1 nvarchar(500),
	GuardianInstituteAffilation1 nvarchar(500),
	GuardianAdmissionClass1 nvarchar(500),
	GuardianMediumStudy1 nvarchar(500),
	
	
	GuardianAcademicYear2 nvarchar(500),
	GuardianInstituteName2 nvarchar(500),
	GuardianInstituteCity2 nvarchar(500),
	GuardianInstituteAffilation2 nvarchar(500),
	GuardianAdmissionClass2 nvarchar(500),
	GuardianMediumStudy2 nvarchar(500),
	
	
	GuardianAcademicYear3 nvarchar(500),
	GuardianInstituteName3 nvarchar(500),
	GuardianInstituteCity3 nvarchar(500),
	GuardianInstituteAffilation3 nvarchar(500),
	GuardianAdmissionClass3 nvarchar(500),
	GuardianMediumStudy3 nvarchar(500),
	
	
	GuardianAcademicYear4 nvarchar(500),
	GuardianInstituteName4 nvarchar(500),
	GuardianInstituteCity4 nvarchar(500),
	GuardianInstituteAffilation4 nvarchar(500),
	GuardianAdmissionClass4 nvarchar(500),
	GuardianMediumStudy4 nvarchar(500),
	
	GuardianAcademicYear5 nvarchar(500),
	GuardianInstituteName5 nvarchar(500),
	GuardianInstituteCity5 nvarchar(500),
	GuardianInstituteAffilation5 nvarchar(500),
	 GuardianAdmissionClass5 nvarchar(500),
	 GuardianMediumStudy5 nvarchar(500)
	
)	
Insert into #tempFatherAcademic
(
	AdmissionFormId, 
	FatherAcademicYear1,FatherInstituteName1,FatherInstituteCity1,FatherInstituteAffilation1,FatherMediumStudy1,FatherAdmissionClass1,
	FatherAcademicYear2,FatherInstituteName2,FatherInstituteCity2,FatherInstituteAffilation2,FatherMediumStudy2,FatherAdmissionClass2,
	FatherAcademicYear3,FatherInstituteName3,FatherInstituteCity3,FatherInstituteAffilation3,FatherMediumStudy3,FatherAdmissionClass3,
	FatherAcademicYear4,FatherInstituteName4,FatherInstituteCity4,FatherInstituteAffilation4,FatherMediumStudy4,FatherAdmissionClass4,
	FatherAcademicYear5,FatherInstituteName5,FatherInstituteCity5,FatherInstituteAffilation5,FatherMediumStudy5,FatherAdmissionClass5,
	
	 
	MotherAcademicYear1,MotherInstituteName1,MotherInstituteCity1,MotherInstituteAffilation1,MotherMediumStudy1,MotherAdmissionClass1,
	MotherAcademicYear2,MotherInstituteName2,MotherInstituteCity2,MotherInstituteAffilation2,MotherMediumStudy2,MotherAdmissionClass2,
	MotherAcademicYear3,MotherInstituteName3,MotherInstituteCity3,MotherInstituteAffilation3,MotherMediumStudy3,MotherAdmissionClass3,
	MotherAcademicYear4,MotherInstituteName4,MotherInstituteCity4,MotherInstituteAffilation4,MotherMediumStudy4,MotherAdmissionClass4,
	MotherAcademicYear5,MotherInstituteName5,MotherInstituteCity5,MotherInstituteAffilation5,MotherMediumStudy5,MotherAdmissionClass5,
	    
	GuardianAcademicYear1,GuardianInstituteName1, GuardianInstituteCity1,GuardianInstituteAffilation1,GuardianMediumStudy1,GuardianAdmissionClass1,
	GuardianAcademicYear2,GuardianInstituteName2,GuardianInstituteCity2,GuardianInstituteAffilation2,GuardianMediumStudy2,GuardianAdmissionClass2,
	GuardianAcademicYear3,GuardianInstituteName3,GuardianInstituteCity3,GuardianInstituteAffilation3,GuardianMediumStudy3,GuardianAdmissionClass3,
	GuardianAcademicYear4,GuardianInstituteName4,GuardianInstituteCity4,GuardianInstituteAffilation4,GuardianMediumStudy4,GuardianAdmissionClass4,
	GuardianAcademicYear5,GuardianInstituteName5,GuardianInstituteCity5,GuardianInstituteAffilation5,GuardianMediumStudy5,GuardianAdmissionClass5
	
	 
)
SELECT AdmissionFormId,
  ISNULL(max(case when  relationshipId =2 and rn = 1  then  AcademicYear  end),'') FatherAcademicYear1,
  ISNULL(max(case when  relationshipId =2 and rn = 1  then  InstituteName  end),'') FatherInstituteName1, 
  ISNULL(max(case when  relationshipId =2 and rn = 1  then  InstituteCity  end),'') FatherInstituteCity1,
   ISNULL(max(case when  relationshipId =2 and rn = 1  then  InstituteAffiliaton  end),'') FatherInstituteAffilation1,
   ISNULL(max(case when  relationshipId =2 and rn = 1  then  Language  end),'') FatherMediumStudy1,
     ISNULL(max(case when  relationshipId =2 and rn = 1  then  AdmissionClass  end),'') FatherAdmissionClass1,
     
  ISNULL(max(case when  relationshipId =2 and rn = 2  then  AcademicYear  end),'') FatherAcademicYear2,
    ISNULL(max(case when  relationshipId =2 and rn = 2  then  InstituteName  end),'') FatherInstituteName2,
   ISNULL(max(case when  relationshipId =2 and rn = 2  then  InstituteCity  end),'') FatherInstituteCity2,
   ISNULL(max(case when  relationshipId =2 and rn = 2  then  InstituteAffiliaton  end),'') FatherInstituteAffilation2,
   ISNULL(max(case when  relationshipId =2 and rn = 2  then  Language  end),'') FatherMediumStudy2,
   ISNULL(max(case when  relationshipId =2 and rn = 2  then  AdmissionClass  end),'') FatherAdmissionClass2,


  ISNULL(max(case when  relationshipId =2 and rn = 3  then  AcademicYear  end),'') FatherAcademicYear3,
 ISNULL(max(case when  relationshipId =2 and rn = 3  then  InstituteName  end),'') FatherInstituteName3,
ISNULL(max(case when  relationshipId =2 and rn = 3  then  InstituteCity  end),'') FatherInstituteCity3,
ISNULL(max(case when  relationshipId =2 and rn = 3  then  InstituteAffiliaton  end),'') FatherInstituteAffilation3,
    ISNULL(max(case when  relationshipId =2 and rn = 3  then  Language  end),'') FatherMediumStudy3,
    ISNULL(max(case when  relationshipId =2 and rn = 3  then  AdmissionClass  end),'') FatherAdmissionClass3,

  ISNULL(max(case when  relationshipId =2 and rn = 4  then  AcademicYear  end),'') FatherAcademicYear4,
      ISNULL(max(case when  relationshipId =2 and rn = 4  then  InstituteName  end),'') FatherInstituteName4,
    ISNULL(max(case when  relationshipId =2 and rn = 4  then  InstituteCity  end),'') FatherInstituteCity4,
    ISNULL(max(case when  relationshipId =2 and rn = 4  then  InstituteAffiliaton  end),'') FatherInstituteAffilation4,
    ISNULL(max(case when  relationshipId =2 and rn = 4  then  Language  end),'') FatherMediumStudy4,
    ISNULL(max(case when  relationshipId =2 and rn = 4  then  AdmissionClass  end),'') FatherAdmissionClass4,

  ISNULL(max(case when  relationshipId =2 and rn = 5  then  AcademicYear  end),'') FatherAcademicYear5,
   ISNULL(max(case when  relationshipId =2 and rn = 5  then  InstituteName  end),'') FatherInstituteName5,
    ISNULL(max(case when  relationshipId =2 and rn = 5  then  InstituteCity  end),'') FatherInstituteCity5,
    ISNULL(max(case when  relationshipId =2 and rn = 5  then  InstituteAffiliaton  end),'') FatherInstituteAffilation5,
   ISNULL(max(case when  relationshipId =2 and rn = 5  then  Language  end),'') FatherMediumStudy5,
    ISNULL(max(case when  relationshipId =2 and rn = 5  then  AdmissionClass  end),'') FatherAdmissionClass5,
   
   ISNULL(max(case when  relationshipId =2 and rn = 1  then  AcademicYear  end),'') MotherAcademicYear1,
   ISNULL(max(case when  relationshipId =2 and rn = 1  then  InstituteName  end),'') MotherInstituteName1,
    ISNULL(max(case when  relationshipId =2 and rn = 1  then  InstituteCity  end),'') MotherInstituteCity1,
   ISNULL(max(case when  relationshipId =2 and rn = 1  then  InstituteAffiliaton  end),'') MotherInstituteAffilation1,
  ISNULL(max(case when  relationshipId =2 and rn = 1  then  Language  end),'') MotherMediumStudy1,
  ISNULL(max(case when  relationshipId =2 and rn = 1  then  AdmissionClass  end),'') MotherAdmissionClass1,

  ISNULL(max(case when  relationshipId =2 and rn = 2  then  AcademicYear  end),'') MotherAcademicYear2,
   ISNULL(max(case when  relationshipId =2 and rn = 2  then  InstituteName  end),'') MotherInstituteName2,
    ISNULL(max(case when  relationshipId =2 and rn = 2  then  InstituteCity  end),'') MotherInstituteCity2,
     ISNULL(max(case when  relationshipId =2 and rn = 2  then  InstituteAffiliaton  end),'') MotherInstituteAffilation2,
       ISNULL(max(case when  relationshipId =2 and rn = 2  then  Language  end),'') MotherMediumStudy2,
       ISNULL(max(case when  relationshipId =2 and rn = 2  then  AdmissionClass  end),'') MotherAdmissionClass2,
    
  ISNULL(max(case when  relationshipId =2 and rn = 3  then  AcademicYear  end),'') MotherAcademicYear3,
  ISNULL(max(case when  relationshipId =2 and rn = 3  then  InstituteName  end),'') MotherInstituteName3,
  ISNULL(max(case when  relationshipId =2 and rn = 3  then  InstituteCity  end),'') MotherInstituteCity3,
  ISNULL(max(case when  relationshipId =2 and rn = 3  then  InstituteAffiliaton  end),'') MotherInstituteAffilation3,
ISNULL(max(case when  relationshipId =2 and rn = 3  then  Language  end),'') MotherMediumStudy3,
ISNULL(max(case when  relationshipId =2 and rn = 3  then  AdmissionClass  end),'') MotherAdmissionClass3,
  
  ISNULL(max(case when  relationshipId =2 and rn = 4  then  AcademicYear  end),'') MotherAcademicYear4,
  ISNULL(max(case when  relationshipId =2 and rn = 4  then  InstituteName  end),'') MotherInstituteName4,
  ISNULL(max(case when  relationshipId =2 and rn = 4  then  InstituteCity  end),'') MotherInstituteCity4,
  ISNULL(max(case when  relationshipId =2 and rn = 4  then  InstituteAffiliaton  end),'') MotherInstituteAffilation4,
  ISNULL(max(case when  relationshipId =2 and rn = 4  then  Language  end),'') MotherMediumStudy4,
  ISNULL(max(case when  relationshipId =2 and rn = 4  then  AdmissionClass  end),'') MotherAdmissionClass4,
  
  ISNULL(max(case when  relationshipId =2 and rn = 5  then  AcademicYear  end),'') MotherAcademicYear5,
   ISNULL(max(case when  relationshipId =2 and rn = 5  then  InstituteName  end),'') MotherInstituteName5,
   ISNULL(max(case when  relationshipId =2 and rn = 5  then  InstituteCity  end),'') MotherInstituteCity5,
   ISNULL(max(case when  relationshipId =2 and rn = 5  then  InstituteAffiliaton  end),'') MotherInstituteAffilation5,
   ISNULL(max(case when  relationshipId =2 and rn = 5  then  Language  end),'') MotherMediumStudy5,
   ISNULL(max(case when  relationshipId =2 and rn = 5  then  AdmissionClass  end),'') MotherAdmissionClass5,
   
    ISNULL(max(case when  relationshipId =2 and rn = 1  then  AcademicYear  end),'') GuardianAcademicYear1,
    ISNULL(max(case when  relationshipId =2 and rn = 1  then  InstituteName  end),'') GuardianInstituteName1,
    ISNULL(max(case when  relationshipId =2 and rn = 1  then  InstituteCity  end),'') GuardianInstituteCity1,
    ISNULL(max(case when  relationshipId =2 and rn = 1  then  InstituteAffiliaton  end),'') GuardianInstituteAffilation1,
    ISNULL(max(case when  relationshipId =2 and rn = 1  then  Language  end),'') GuardianMediumStudy1, 
    ISNULL(max(case when  relationshipId =2 and rn = 1  then  AdmissionClass  end),'') GuardianAdmissionClass1,
    
  ISNULL(max(case when  relationshipId =2 and rn = 2  then  AcademicYear  end),'') GuardianAcademicYear2,
  ISNULL(max(case when  relationshipId =2 and rn = 2  then  InstituteName  end),'') GuardianInstituteName2,
  ISNULL(max(case when  relationshipId =2 and rn = 2  then  InstituteCity  end),'') GuardianInstituteCity2,
  ISNULL(max(case when  relationshipId =2 and rn = 2  then  InstituteAffiliaton  end),'') GuardianInstituteAffilation2,
  ISNULL(max(case when  relationshipId =2 and rn = 2  then  Language  end),'') GuardianMediumStudy2,
  ISNULL(max(case when  relationshipId =2 and rn = 2  then  AdmissionClass  end),'') GuardianAdmissionClass2,
  
  ISNULL(max(case when  relationshipId =2 and rn = 3  then  AcademicYear  end),'') GuardianAcademicYear3,
  ISNULL(max(case when  relationshipId =2 and rn = 3  then  InstituteName  end),'') GuardianInstituteName3,
  ISNULL(max(case when  relationshipId =2 and rn = 3  then  InstituteCity  end),'') GuardianInstituteCity3,
   ISNULL(max(case when  relationshipId =2 and rn = 3  then  InstituteAffiliaton  end),'') GuardianInstituteAffilation3,
   ISNULL(max(case when  relationshipId =2 and rn = 3  then  Language  end),'') GuardianMediumStudy3,
   ISNULL(max(case when  relationshipId =2 and rn = 3  then  AdmissionClass  end),'') GuardianAdmissionClass3,
   
   
  ISNULL(max(case when  relationshipId =2 and rn = 4  then  AcademicYear  end),'') GuardianAcademicYear4,
  ISNULL(max(case when  relationshipId =2 and rn = 4  then  InstituteName  end),'') GuardianInstituteName4,
  ISNULL(max(case when  relationshipId =2 and rn = 4  then  InstituteCity  end),'') GuardianInstituteCity4,
  ISNULL(max(case when  relationshipId =2 and rn = 4  then  InstituteAffiliaton  end),'') GuardianInstituteAffilation4,
  ISNULL(max(case when  relationshipId =2 and rn = 4  then  Language  end),'') GuardianMediumStudy4,
  ISNULL(max(case when  relationshipId =2 and rn = 4  then  AdmissionClass  end),'') GuardianAdmissionClass4,
  
  ISNULL(max(case when  relationshipId =2 and rn = 5  then  AcademicYear  end),'') GuardianAcademicYear5,
   ISNULL(max(case when  relationshipId =2 and rn = 5  then  InstituteName  end),'') GuardianInstituteName5,
   ISNULL(max(case when  relationshipId =2 and rn = 5  then  InstituteCity  end),'') GuardianInstituteCity5,
   ISNULL(max(case when  relationshipId =2 and rn = 5  then  InstituteAffiliaton  end),'') GuardianInstituteAffilation5,
   ISNULL(max(case when  relationshipId =2 and rn = 5  then  Language  end),'') GuardianMediumStudy5,
   ISNULL(max(case when  relationshipId =2 and rn = 5  then  AdmissionClass  end),'') GuardianAdmissionClass5  
   
from
(
  select AP.AdmissionFormId, RelationshipId, AcademicYear, AdmissionClass, InstituteName, InstituteCity,InstituteAffiliaton,Language
      , row_number() over(partition by AP.AdmissionFormId, RelationshipId
                          order by RelationshipId) rn
  from sAdmission.tAdmissionFormParentAcademic AP
  INNER JOIN #tempAdmissionForm TAF ON TAF.AdmissionFormId = Ap.AdmissionFormId
) d
group by AdmissionFormId order by AdmissionFormId 


 

create table #tempReference
(
	AdmissionFormId bigint ,
	ReferenceDetail1 nvarchar(500),
	ReferenceName1 nvarchar(500),
	ReferenceRelation1 nvarchar(500),
	ReferenceContactNo1 nvarchar(500),
	ReferenceDetail2 nvarchar(500),
	ReferenceName2 nvarchar(500),
	ReferenceRelation2 nvarchar(500),
	ReferenceContactNo2 nvarchar(500)
)
INsert Into  #tempReference
 (
	AdmissionFormId,
	ReferenceDetail1,
	ReferenceName1,
	ReferenceRelation1,
	ReferenceContactNo1,
	ReferenceDetail2,
	ReferenceName2,
	ReferenceRelation2,
	ReferenceContactNo2
 )
SELECT AdmissionFormId,
  ISNULL(max(case when    rn = 1  then  ReferenceDetail  end),'') ReferenceDetail1,
  ISNULL(max(case when   rn = 1  then  ReferenceName  end),'') ReferenceName1,
  ISNULL(max(case when    rn = 1  then  ReferenceRelation  end),'') ReferenceRelation1,
  ISNULL(max(case when    rn = 1  then  ReferenceContactNo  end),'') ReferenceContactNo1,
  
  ISNULL(max(case when  rn = 2  then  ReferenceDetail  end),'') ReferenceDetail2,
  ISNULL(max(case when   rn = 2  then  ReferenceName  end),'') ReferenceName2,
  ISNULL(max(case when   rn = 2  then  ReferenceRelation  end),'') ReferenceRelation2,
  ISNULL(max(case when  rn = 2 then  ReferenceContactNo  end),'') ReferenceContactNo2
   
from
(
  select AP.AdmissionFormId,ReferenceDetail, ReferenceName, ReferenceRelation, ReferenceContactNo 
      , row_number() over(partition by AP.AdmissionFormId 
                           order by AP.AdmissionFormId ) rn
  from sAdmission.tAdmissionFormReference AP
  INNER JOIN #tempAdmissionForm TAF ON TAF.AdmissionFormId = Ap.AdmissionFormId
) d
group by AdmissionFormId order by AdmissionFormId 

Create table #TempSibling
(
	AdmissionFormId bigint,
	Code1 nvarchar(500),
	SiblingName1 nvarchar(500),
	SiblingRelation1 nvarchar(500),
	SiblingStandard1 nvarchar(500),
	SiblingSchool1 nvarchar(500),
	Code2 nvarchar(500),
	SiblingName2 nvarchar(500),
	SiblingRelation2 nvarchar(500),
	SiblingStandard2 nvarchar(500),
	SiblingSchool2 nvarchar(500),
	Code3 nvarchar(500),
	SiblingName3 nvarchar(500),
	SiblingRelation3 nvarchar(500),
	SiblingStandard3 nvarchar(500),
	SiblingSchool3 nvarchar(500),
	Code4 nvarchar(500),
	SiblingName4 nvarchar(500),
	SiblingRelation4 nvarchar(500),
	SiblingStandard4 nvarchar(500),
	SiblingSchool4 nvarchar(500)
)
Insert Into #TempSibling
(AdmissionFormId,
Code1,	
SiblingName1,
	SiblingRelation1,
	SiblingStandard1,
	SiblingSchool1,
	Code2,	
	SiblingName2,
	SiblingRelation2,
	SiblingStandard2,
	SiblingSchool2,
	Code3,	
	SiblingName3,
	SiblingRelation3,
	SiblingStandard3,
	SiblingSchool3,
	Code4,	
	SiblingName4,
	SiblingRelation4,
	SiblingStandard4,
	SiblingSchool4
)
SELECT AdmissionFormId,
  ISNULL(max(case when    rn = 1  then  Code  end),'') Code1,
  ISNULL(max(case when    rn = 1  then  SiblingName  end),'') SiblingName1,
  ISNULL(max(case when    rn = 1  then  SiblingRelation  end),'') SiblingRelation1,
   ISNULL(max(case when    rn = 1  then  SiblingStandard  end),'') SiblingStandard1,
    ISNULL(max(case when    rn = 1  then  SiblingSchool  end),'') SiblingSchool1,
  
  ISNULL(max(case when    rn = 2  then  Code  end),'') Code2,
  ISNULL(max(case when    rn = 2  then  SiblingName  end),'') SiblingName2,
  ISNULL(max(case when    rn = 2  then  SiblingRelation  end),'') SiblingRelation2,
  ISNULL(max(case when    rn = 2  then  SiblingStandard  end),'') SiblingStandard2,
  ISNULL(max(case when    rn = 2 then  SiblingSchool  end),'') SiblingSchool2,
  
  ISNULL(max(case when    rn = 2  then  Code  end),'') Code3,
   ISNULL(max(case when    rn = 2  then  SiblingName  end),'') SiblingName3,
  ISNULL(max(case when    rn = 2  then  SiblingRelation  end),'') SiblingRelation3,
  ISNULL(max(case when    rn = 2  then  SiblingStandard  end),'') SiblingStandard3,
  ISNULL(max(case when    rn = 2 then  SiblingSchool  end),'') SiblingSchool3,
  
  ISNULL(max(case when    rn = 2  then  Code  end),'') Code4,
   ISNULL(max(case when    rn = 2  then  SiblingName  end),'') SiblingName4,
  ISNULL(max(case when    rn = 2  then  SiblingRelation  end),'') SiblingRelation4,
  ISNULL(max(case when    rn = 2  then  SiblingStandard  end),'') SiblingStandard4,
  ISNULL(max(case when    rn = 2 then  SiblingSchool  end),'') SiblingSchool4
   
from
(
  select AP.AdmissionFormId,    SiblingName, SiblingRelation, SiblingStandard,SiblingSchool,Code  
      , row_number() over(partition by AP.AdmissionFormId 
                          order by AP.AdmissionFormId) rn
  from sAdmission.tAdmissionFormSibling AP
  INNER JOIN #tempAdmissionForm TAF ON TAF.AdmissionFormId = Ap.AdmissionFormId
) d
group by AdmissionFormId order by AdmissionFormId 

 
DECLARE @cols AS NVARCHAR(MAX),@query  AS NVARCHAR(MAX)

-- CTE for Admission Form Field
;WITH AdmissionCriteriaFormCTE AS (SELECT  DISTINCT FieldRemark FROM sAdmission.tAdmissionCriteriaForm ACF
INNER JOIN sAdmission.tAdmissionFormField AFF ON ACF.Id = AFF.AdmissionCriteriaFormId
WHERE ACF.BatchId = @rBatchId
GROUP BY Id,FieldRemark,ClassId,BatchId
)

SELECT @cols = STUFF((SELECT  ',' + QUOTENAME(FieldRemark) FROM AdmissionCriteriaFormCTE
GROUP BY FieldRemark
ORDER BY FieldRemark FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'),1,1,'')

IF object_id('tempdb..##tempAdmissionCriteriaForm') IS NOT NULL 
BEGIN
   DROP TABLE ##tempAdmissionCriteriaForm 
END

IF(@cols IS NOT NULL)
BEGIN 
	SET @query = N'SELECT DISTINCT AdmissionFormId,' + @cols + N' INTO ##tempAdmissionCriteriaForm FROM 
				 (
					SELECT DISTINCT  AFF.AdmissionFormId,AdmissionFormFieldResponse, FieldRemark
					FROM 
					sAdmission.tAdmissionCriteriaForm ACF
					INNER JOIN sAdmission.tAdmissionFormField AFF ON ACF.Id = AFF.AdmissionCriteriaFormId
					WHERE ACF.BatchId = '+CONVERT(nvarchar(10),@rBatchId)+'
				) x
				PIVOT
				(
					MAX(AdmissionFormFieldResponse)
					FOR FieldRemark IN (' + @cols + N')
				) p '
			
	exec sp_executesql @query
	
	SELECT * from #tempAdmissionForm tempAF
	LEFT JOIN #tempStudentAcademic tempSA ON tempSA.AdmissionFormId = tempAF.AdmissionFormId
	LEFT JOIN #tempFatherAcademic tempFA ON tempFA.AdmissionFormId=tempAF.AdmissionFormId
	LEFT JOIN #tempReference tempRF on tempRF.AdmissionFormId=tempAF.AdmissionFormId
	LEFT JOIN #TempSibling tempSB ON tempSB.AdmissionFormId = tempAF.AdmissionFormId
	LEFT JOIN ##tempAdmissionCriteriaForm tempACF ON tempACF.AdmissionFormId = tempAF.AdmissionFormId
	Order by tempAF.Formno

END
ELSE 
BEGIN 
	SELECT * from #tempAdmissionForm tempAF
	LEFT JOIN #tempStudentAcademic tempSA ON tempSA.AdmissionFormId = tempAF.AdmissionFormId
	LEFT JOIN #tempFatherAcademic tempFA ON tempFA.AdmissionFormId=tempAF.AdmissionFormId
	LEFT JOIN #tempReference tempRF on tempRF.AdmissionFormId=tempAF.AdmissionFormId
	LEFT JOIN #TempSibling tempSB ON tempSB.AdmissionFormId = tempAF.AdmissionFormId
	Order by tempAF.Formno
END
END
GO
PRINT N'Altering Procedure [sAdmission].[pRptAdmissionFeeGet]...';


GO

ALTER PROCEDURE [sAdmission].[pRptAdmissionFeeGet]
	
	
	 @rBatchId int,
	 @rClassId int,
	 @rBuilder1 nvarchar(max)='',
	 @rBuilder2 nvarchar(max)='',
	 @rFromDate date,
	 @rToDate date,
	 @rIsDetail bit =0
AS
 SET NOCOUNT ON
			 

BEGIN 
	
	SELECT 
		 AF.FormNo,
		 AF.FirstName+' '+AF.MiddleName+' '+AF.LastName AS Name,
		 AF.ForClassId,
		 AF.BatchId,
		 AF.OrgId,
		 AF.City,
		 AF.FamilyCast,
		 CM.Title AS Class,
		 AF.FamilySubCast,
		 AF.CastCategoryId,
		 --for online and offline display same feename
		 AC.FormFeeName AS FeeName,
		 --ACF.FeeName,
		 --in online entry display amount from pt  and in offline entry amount display from aff
		 CASE WHEN AF.DataEntryModeId = 561 THEN PT.PayAmount ELSE AFF.FeeAmount END  AS FeeAmount,
		 --AFF.FeeAmount,
		 --ACF.FeeNumber,
		 @rIsDetail AS IsDetail,
		 @rBuilder1 AS Builder1,
		 @rBuilder2 AS Builder2,
		 ISNULL(AF.CreatedDateTime,'') AS CreatedDateTime,
		 ISNULL(AFR.ReceiptNumber, 0) AS ReceiptNumber --Mantis - 0033981
	 
	 FROM 
		sAdmission.tAdmissionForm AF	
		INNER JOIN sSetup.tClassMaster CM ON AF.ForClassId=CM.Id
		LEFT JOIN sAdmission.tAdmissionFormFee AFF ON AFF.AdmissionFormId = AF.Id
		LEFT JOIN sAdmission.tAdmissionCriteriaFee ACF ON ACF.Id = AFF.AdmissionCriteriaFeeId
		INNER JOIN sAdmission.tAdmissionCriteria AC ON AC.ClassId=CM.Id AND AC.BatchId = @rBatchId
		LEFT JOIN sOp.tPaymentTransaction PT ON AF.Id = PT.PayerSourceId AND PT.TransactionSourceTypeId = 2027 AND PT.TransactionStatusId IN (2,4)
		LEFT JOIN sAdmission.tAdmissionFormReceipt AFR ON AF.Id = AFR.SourceId AND  AF.BatchId = AFR.BatchId --Mantis - 0033981

	WHERE 	
		AF.BatchId=@rBatchId
		--AND AF.OrgId=@rOrgId
		AND ((AF.ForClassId=@rClassId) OR (@rClassId=0))	
		AND ((CONVERT(DATE,AF.CreatedDateTime) >= @rFromDate) OR (isnull(@rFromDate,'')=''))
	    AND ((CONVERT(DATE,AF.CreatedDateTime) <= @rToDate) OR (isnull(@rToDate,'')=''))
	    ORDER BY AF.FormNo
END
GO
PRINT N'Altering Procedure [sAssessment].[pRptResultWithSubjectListGet]...';


GO
ALTER PROCEDURE [sAssessment].[pRptResultWithSubjectListGet]
	 
   	 @rBuilder1 nvarchar(max)='',
	 @rBuilder2 nvarchar(max)='', 
	 @rSubjectId int=0,
	 @rBatchId int,
	 @rClassId int,
	 @rDivisionId int,
	 @rExamId int=0,
     @rExamSubGroupId int=0,
	 @rCode nvarchar(20),
	 @rFromRollNo int,
	 @rToRollNo int,
	 @rFromRank int,
	 @rToRank int,
	 @rGrade nvarchar(20),
	 @rStatusId int = 0,
	 @rFromPercentage decimal(5,2)=0,
	 @rToPercentage decimal(5,2)=0,
	 @rResultStatus nvarchar(20)='',
	 @rDepartmentId int=0,
	 @rClassStatusId int=0,
	 @rGenderId int = 0
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			
BEGIN 

Declare @lOrgId int, @lAbsentRemarkConfig nvarchar(20) = ''
SELECT @lOrgId=OrgId FROM sSetup.tBatchMaster WHERE Id=@rBatchId
SELECT @lAbsentRemarkConfig =ItemValue From sConfig.tConfigurationSetup WHERE ConfigurationId = 515 AND SourceTypeId = 1016 AND SourceId = @lOrgId

	SELECT 
			@rBuilder1 AS Builder1,
			@rBuilder2 AS Builder2,
			SO.GenderId,
			ESGM.Title AS Exam,
			StuM.Code,
			[sStudent].[fStudentNameGet](StuM.FirstName , StuM.MiddleName, StuM.LastName,OM.StudentNameFormatId ) AS Name,
			A.RollNo,
			R.AcademicId,
			RD.Obtain,
			R.ResultStatus,
			CM.DisplayTitle AS Class,
			Cm.Id AS ClassId,
			DM.DisplayTitle AS Division,
			DM.Id AS DivisionId,
			DM.OrderIndex AS DivisinOrderIndex,
			R.TotalMarks,
			R.TotalObtain,
			R.Percentage,
			R.RankNumber,
			R.ResultGrade,
			SM.Title AS ClassSubject,
			RD.Id AS ResultDetailId,
			R.Id AS ResultId,
			CS.Id AS SubjectId,
			ED.Maximum,
			RD.SubjectGrade, 
			
			EM.IsMarksInDecimal,
			--SM.OrderIndex AS SubjectCode,
			SM.Code AS SubjectCode,
			ED.DisplayOrder AS SubjectOrderIndex,
			CM.OrderIndex AS ClassOrderIndex,
			RD.IsPresent,
			EM.IsGrade,
			EM.IsPercentage,
			EM.IsResultStatus,
			EM.IsRankNo ,
			ED.Minimum,
			RD.GraceMark,
			EM.DecimalFormat,
			CS.IsOptional,
			ISNULL(SR.PresentDays,0)AS PresentDays,
			ISNULL(SR.TotalDays,0)AS TotalDays,
			ED.IsShowSubjectGrade,
			ED.IsShowSubjectMark,
			(EmpM.FirstName + ' ' +EmpM.LastName ) AS InchargeEmployeeName,
			CONVERT(bit,@lAbsentRemarkConfig) AS IsAbsentRemark,
			ISNULL(RD.AbsentRemark,'') AS AbsentRemark,
			ISNULL(StuM.SUId,'') AS SUId -- Mantis : 0034451

	FROM 
	
			sAssessment.tResultDetail RD
			INNER JOIN sAssessment.tResult R ON R.Id = RD.ResultId
			INNER JOIN sAssessment.tExamDetail ED ON ED.Id=RD.ExamDetailId
			INNER JOIN sAssessment.tExamMaster EM ON EM.Id=ED.ExamId	
			INNER JOIN sAssessment.tExamSubGroupMaster ESGM ON ESGM.Id = EM.ExamSubGroupId		
			INNER JOIN sSetup.tClassSubject CS ON CS.Id=ED.ClassSubjectId
			INNER JOIN sSetup.tSubjectMaster SM ON SM.Id=CS.SubjectId					 
			INNER JOIN sStudent.tAcademic A ON A.Id=R.AcademicId
			LEFT JOIN sAssessment.tStudentRemark SR ON SR.AcademicId = A.Id AND SR.ExamId =  EM.Id
			INNER JOIN sStudent.tStudentMaster StuM ON StuM.Id=A.StudentId
			INNER JOIN sSetup.tClassMaster CM ON CM.Id=A.ClassId
			INNER JOIN sSetup.tDivisionMaster DM ON DM.Id=A.DivisionId
			INNER JOIN sSetup.tOrganizationMaster OM ON StuM.OrgId = OM.Id
			LEFT JOIN sStudent.tStudentOther SO ON SO.StudentId=A.StudentId
			LEFT JOIN sHrm.tEmployeeMaster EmpM ON EmpM.Id = DM.InchargeEmployeeId AND EmpM.StatusId = 7
			
	WHERE 
			
		EM.BatchId = @rBatchId 
		AND (A.ClassId=@rClassId OR @rClassId=0) 
		AND ((SM.Id=@rSubjectId) OR (RD.ExamDetailId=@rSubjectId) OR (@rSubjectId=0))
		AND (A.DivisionId=@rDivisionId OR @rDivisionId=0)
		AND ((A.RollNo >= @rFromRollNo) OR (@rFromRollNo=0))
		AND ((A.RollNo <= @rToRollNo) OR (@rToRollNo=0))
		AND ((R.RankNumber >= @rFromRank) OR (@rFromRank=0))
		AND ((R.RankNumber <= @rToRank) OR (@rToRank=0))
		AND ((R.ResultGrade = @rGrade) OR (@rGrade=''))
		AND ((ESGM.Id = @rExamSubGroupId) OR (@rExamSubGroupId = 0))		
		AND ((EM.Id=@rExamId )	OR (@rExamId = 0))		
		AND ((StuM.Code=@rCode) OR (@rCode=''))
		AND ((StuM.StatusId=@rStatusId)OR(@rStatusId=0))	
		AND ((R.Percentage>=@rFromPercentage) OR (@rFromPercentage=0))
		AND ((R.Percentage<=@rToPercentage) OR(@rToPercentage=0))	
		AND ((R.ResultStatus=@rResultStatus) OR (@rResultStatus=''))
		AND ((CM.DepartmentId = @rDepartmentId) OR (@rDepartmentId=0))
		AND	A.BatchId = @rBatchId 
		AND (A.ClassStatusId=@rClassStatusId OR @rClassStatusId=0) 
		AND ((SO.GenderId=@rGenderId) OR (@rGenderId=0))
		--AND RD.IsPresent = 0
		order by A.RollNo,CS.OrderIndex 

		
END
GO
PRINT N'Altering Procedure [sAttendance].[pStudentRegisterXMLInsert]...';


GO
 
ALTER PROCEDURE [sAttendance].[pStudentRegisterXMLInsert]
	
	@rStrXml nvarchar(MAX),
    @rOrgId int,
    @rClassId int=0,
    @rDivisionId int,
    @rAttendanceDate date='',
    @rAttendanceTypeId tinyint,
    @rCreatedUserId bigint,
    @rSubjectId int=0,
    @rRepeatCount tinyint=0,
    @rBatchId int=0,
    @rAttendanceGroupId int=0,
    @rPeriodNo int=0 
		
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
SET @rStrXml=REPLACE(@rStrXml,'&','AND')
DECLARE @loDoc INT
EXEC sp_xml_preparedocument @loDoc OUTPUT, @rStrXml
BEGIN
	BEGIN TRY
	 DECLARE @tmp varchar(1000)
	 IF @rDivisionId != 0
	 BEGIN 
		IF EXISTS(SELECT 1 FROM sAttendance.tStudentRegister SR INNER JOIN sStudent.tAcademic SA ON SA.Id = SR.AcademicId INNER JOIN (SELECT AcademicId FROM OPENXML(@loDoc, '/Table/RowData', 1) WITH (AcademicId bigint))A ON SR.AcademicId = A.AcademicId 
				 AND SR.SubjectId = @rSubjectId AND SR.AttendanceDate = @rAttendanceDate AND SR.PeriodNo = @rPeriodNo AND ISNULL(SR.AttendanceGroupId,0) > 0)
		BEGIN
		SET @tmp = ''
		SELECT @tmp = @tmp + SA.Code + ', ' FROM sAttendance.tStudentRegister SR INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId = SR.AcademicId INNER JOIN (SELECT AcademicId FROM OPENXML(@loDoc, '/Table/RowData', 1) WITH (AcademicId bigint))A ON SR.AcademicId = A.AcademicId AND SR.SubjectId = @rSubjectId AND SR.AttendanceDate = @rAttendanceDate AND SR.PeriodNo = @rPeriodNo AND ISNULL(SR.AttendanceGroupId,0) > 0	-- Add ISNULL to AttendanceGroupId for mantis : 0034075
		SELECT -101 AS Result, SUBSTRING(@tmp, 0, LEN(@tmp)) AS ResultDetail
		RETURN
		END
	 END
	 ELSE
	 BEGIN 
		IF EXISTS(SELECT 1 FROM sAttendance.tStudentRegister SR INNER JOIN sStudent.tAcademic SA ON SA.Id = SR.AcademicId INNER JOIN (SELECT AcademicId FROM OPENXML(@loDoc, '/Table/RowData', 1) WITH (AcademicId bigint))A ON SR.AcademicId = A.AcademicId 
				 AND SR.SubjectId = @rSubjectId AND SR.AttendanceDate = @rAttendanceDate AND SR.PeriodNo = @rPeriodNo AND ISNULL(SR.AttendanceGroupId,0) = 0)
		BEGIN
		SET @tmp = ''
		SELECT @tmp = @tmp + SA.Code + ', ' FROM sAttendance.tStudentRegister SR INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId = SR.AcademicId INNER JOIN (SELECT AcademicId FROM OPENXML(@loDoc, '/Table/RowData', 1) WITH (AcademicId bigint))A ON SR.AcademicId = A.AcademicId AND SR.SubjectId = @rSubjectId AND SR.AttendanceDate = @rAttendanceDate AND SR.PeriodNo = @rPeriodNo AND ISNULL(SR.AttendanceGroupId,0) = 0	-- Add ISNULL to AttendanceGroupId for mantis : 0034075
		SELECT -101 AS Result, SUBSTRING(@tmp, 0, LEN(@tmp)) AS ResultDetail
		RETURN
		END
	 END
	
	 BEGIN TRANSACTION 
	 BEGIN
	 
	 DELETE SR FROM sAttendance.tStudentRegister SR 
	 INNER JOIN sStudent.tAcademic SA ON SA.Id = SR.AcademicId
	 --INNER JOIN (SELECT AcademicId FROM OPENXML(@loDoc, '/Table/RowData', 1)
	 --WITH
	 --(AcademicId bigint))A ON SR.AcademicId = A.AcademicId 
	    WHERE 
	    SR.AttendanceTypeId = @rAttendanceTypeId AND SR.AttendanceDate = @rAttendanceDate
	    --AND SA.ClassId = @rClassId 
	    AND ((SA.DivisionId=@rDivisionId AND ISNULL(SR.AttendanceGroupId,0)=0) OR (@rDivisionId=0))
	    AND SA.OrgId = @rOrgId 
	    AND ((ISNULL(SR.SubjectId,0) = ISNULL(@rSubjectId,0)) OR ( ISNULL(@rSubjectId,0)=0))
	    AND ((ISNULL(SR.RepeatCount,0) = ISNULL(@rRepeatCount,0)) OR (ISNULL(@rRepeatCount,0)=0))
	    AND ((ISNULL(SR.AttendanceGroupId,0)=@rAttendanceGroupId) OR (@rAttendanceGroupId=0))
	    AND SA.BatchId =@rBatchId
	 
	 INSERT INTO sAttendance.tStudentRegister 
		(
			AcademicId,
			EmployeeId,
			AttendanceTypeId,
			AttendanceDate,
			SubjectId,
			RepeatCount,
			IsPresent,
			AbsentReasonId,
			CreatedUserId,
			CreatedDateTime,
			UpdatedUserId,
			UpdatedDateTime,
			AttendanceTagId,
			PeriodNo,
			AttendanceGroupId,
			AttendanceRemark
			)
		SELECT	
		DISTINCT	
			AcademicId,
			EmployeeId,
			@rAttendanceTypeId,
			@rAttendanceDate,
			ISNULL(@rSubjectId,0),
			ISNULL(@rRepeatCount,0),
			IsPresent,
			ISNULL(AbsentReasonId,0),
			@rCreatedUserId,
			sCommon.fGetDate(),
			@rCreatedUserId,
			sCommon.fGetDate(),
			ISNULL(AttendanceTagId,0),
			@rPeriodNo,
			@rAttendanceGroupId,
			ISNULL(AttendanceRemark,'')
						
		FROM OPENXML (@loDoc, '/Table/RowData', 1)
		WITH ( 
				AcademicId bigint,
				EmployeeId bigint,
				IsPresent bit,
				AbsentReasonId int,
				AttendanceTagId smallint,
				AttendanceRemark nvarchar(500)
			  )  
        SELECT 1 AS Result
	 END	
	
		COMMIT TRANSACTION
		
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
		
END
GO

PRINT N'Altering Procedure [sCertificate].[pRptFirstTrialCertificateGet]...';


GO
ALTER PROCEDURE [sCertificate].[pRptFirstTrialCertificateGet]
	 
	 @rBuilder1 nvarchar(max)='',
	 @rBuilder2 nvarchar(max)='',
	 @rOrgId smallint,
	 @rBatchId int,
	 @rFromNo nvarchar(20)='',
	 @rToNo nvarchar(20)='',
	 @rNumber nvarchar(20)='',
	 @rClassId int=0,
	 @rDivisionId int=0,
	 @rFromDate date='',
	 @rToDate date='',
	 @rCode nvarchar(20)='',
     @rName nvarchar(20)=''
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
DECLARE @lIsOnPrePrinted bit = 0

DECLARE @lIsDisplayImageHeader bit = 0

DECLARE @lConfigValue nvarchar(50)
Declare @lIDConfigValue nvarchar(50)='',@lOrgGroupId int=0

Select @lOrgGroupId = OrgGroupId From  sSetup.tOrganizationMaster Where Id = @rOrgId
SELECT @lIDConfigValue =ItemValue From sConfig.tConfigurationSetup
WHERE ConfigurationId = 236 AND SourceId = @lOrgGroupId

SELECT @lConfigValue = ItemValue FROM sConfig.tConfigurationSetup WHERE ConfigurationId = 179 AND SourceTypeId = 1016 AND SourceId = @rOrgId
	
	IF (@lConfigValue IS NULL) OR (@lConfigValue = 'Default')
	BEGIN
	SET @lIsOnPrePrinted = 0
	SET @lIsDisplayImageHeader = 0
	END
	ELSE IF(@lConfigValue = 'Blank')
	BEGIN
	SET @lIsOnPrePrinted = 1
	SET @lIsDisplayImageHeader = 0
	END
	ELSE IF(@lConfigValue = 'Image')
	BEGIN
	SET @lIsOnPrePrinted = 0
	SET @lIsDisplayImageHeader = 1
	END
	
	--SELECT @lConfigValue = ItemValue FROM sConfig.tConfigurationSetup WHERE ConfigurationId = 158 AND SourceTypeId = 1016 AND SourceId = @rOrgId
	
	--IF (@lConfigValue IS NULL) OR (@lConfigValue = 'False')
	--SET @lIsDisplayImageHeader = 0
	--ELSE
	--SET @lIsDisplayImageHeader = 1	
	
	--add for 28093 mantis start
	DECLARE @lIsShowPhoto bit = 0
	SELECT @lConfigValue = ItemValue FROM sConfig.tConfigurationSetup WHERE ConfigurationId = 124 AND SourceTypeId = 1016 AND SourceId = @rOrgId
	
	IF (@lConfigValue IS NULL) OR (@lConfigValue = 'False')
	SET @lIsShowPhoto = 0
	ELSE
	SET @lIsShowPhoto = 1
	--add for 28093 mantis end

	SELECT
			@rBuilder1 AS Builder1,
		    @rBuilder2 AS Builder2,
			FT.Id,
			FT.Number,
			FT.AcademicId,
			FT.FirstTrialDate,
			FT.ExamCentre,
			FT.ExamYear,
			FT.SeatNo,
			FT.Trial,
			SM.Code,
			--SM.FirstName+' '+SM.MiddleName+' '+SM.LastName AS Name,
			[sStudent].[fStudentNameGet](SM.FirstName , SM.MiddleName, SM.LastName,OM.StudentNameFormatId ) AS Name,
			SM.LastName as Surname,
			SO.GenderId,
			ISNULL(OM.CityName,'') AS City,
			ISNULL(OM.StateName,'') AS CityState,
			SM.StatusId,
			OM.OrgLogo,
			OM.Title AS OrgTitle,
			OM.StreetOne+' '+OM.StreetTwo+' '+OM.StreetThree AS OrgAddress,
			OM.PhoneNo,
			A.RollNo,
			DM.OrderIndex AS DivisionOrder,
			OM.ReportOrgTitle,
			OM.SmallAddress1,
			OM.SmallAddress2,
			OM.RegistrationDetail,
			@lIsOnPrePrinted AS IsOnPrePrinted,
			CM.DisplayTitle+' - '+DM.DisplayTitle AS Class,
			CM.DisplayTitle  AS ClassName,
			@lIsDisplayImageHeader AS IsDisplayImageHeader,
			--OI.CertificateHeaderA4  ,
			(CASE WHEN CGM.Title='10' THEN  'SSC'
			WHEN  CGM.Title='12'  THEN 'HSC'  ElSE CGM.Title + ' '+'std'
			 END) AS Exam,
			 FT.Remark AS Remark,
			 ISNULL(SF.FatherName,'') As FatherName,
			 ISNULL(SF.MotherName,'') As MotherName,
			 SA.AdmissionDate,
			 SO.BirthDateInWord ,
			 SO.DateOfBirth,
			 SI.StudentPhoto,
			 @lIDConfigValue AS ItemValue,
			 SM.SUID ,
			 STM.Title as StreamName,
			 ISNULL(OM.ReportOrgGroupTitle,'') AS ReportOrgGroupTitle,
			 OM.ReportOrgTitle,
			--(CASE WHEN OI.CertificateHeaderA4Portrait IS NULL THEN
			--OI.CertificateHeaderA4
			--ELSE OI.CertificateHeaderA4Portrait END ) AS CertificateHeader
			OI.CertificateHeaderA4Portrait AS CertificateHeaderA4,
			BM.Title AS Batch,
			ISNULL(SA.LeftDate,'') AS LeftDate,
			ISNULL(EI.EmployeeSign,0x) AS EmployeeSign,
			ISNULL(OM.PrincipalSignature,0x) AS PrincipalSignature,
			EI.EmployeeId,
			SC.PresentStreetOne AS PresentStreetOne,		--add for 28093 mantis start
			SC.PresentStreetTwo AS PresentStreetTwo,
			SC.PresentStreetThree AS PresentStreetThree,
			SC.PresentCountry AS PresentCountry,
			SC.PresentState AS PresentState,
			SC.PresentCity AS PresentCity,
			SC.PresentZipCode AS PresentZipCode,
			SC.PresentContactNo AS PresentContactNo,
			@lIsShowPhoto AS IsShowPhoto,
			CAST((CASE WHEN SI.QRCode IS NULL THEN 0 ELSE 1 END) AS bit) AS IsDisplayQRCode,
			ISNULL(SI.QRCode,'') AS QRCode,					--add for 28093 mantis end
			ISNULL(FT.LastExamName,'') AS LastExamName,   -- Mantis : 0027927 #Start
			ISNULL(FT.ObtainedMark,'') AS ObtainedMarkInLastExam,
			ISNULL(FT.AdditionalRemark,'') AS AdditionalRemark,
			DM.DisplayTitle AS DivisionName,
			BM.FromDate AS BatchStartDate,
			BM.ToDate AS BatchEndDate,
			ISNULL(SGM.Title,'') AS StudentGroup,  -- #End
			SA.AdmissionNumber, --Add for Mentis : 0033616
			ISNULL(SM.MiddleName,'') As MiddleName--Add for Mentis : 0034319

	FROM
			sCertificate.tFirstTrial FT
			INNER JOIN sStudent.tAcademic A ON FT.AcademicId=A.Id
			LEFT JOIN sStudent.tStudentImages SI ON SI.StudentId=A.StudentId
			INNER JOIN sStudent.tStudentMaster SM ON SM.Id=A.StudentId
			INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=A.OrgId
			INNER JOIN sStudent.tStudentOther SO ON SO.StudentId=A.StudentId
			INNER JOIN sSetup.tDivisionMaster DM ON A.DivisionId = DM.Id
			INNER JOIN sSetup.tClassMaster CM ON CM.Id= DM.ClassId
			INNER JOIN  sSetup.tStreamMaster STM ON STM.Id= CM.StreamId
			Left JOIN sSetup.tOrgImages OI ON OI.OrgId = OM.Id
			LEFT JOIN sSetup.tClassGroupMaster CGM ON CGM.Id = CM.ClassGroupId
			LEFT JOIN sStudent.tStudentFamily SF ON SF.StudentId=A.StudentId
			LEFT JOIN sStudent.tStudentAdmission SA ON SA.StudentId=A.StudentId
			INNER JOIN sSetup.tBatchMaster BM ON BM.Id=A.BatchId
			LEFT JOIN sHrm.tEmployeeImages EI ON EI.EmployeeId = DM.InchargeEmployeeId
			LEFT JOIN sStudent.tStudentCommunication SC ON SC.StudentId=SM.Id			--add for 28093 mantis
			LEFT JOIN sStudent.tStudentGroupMaster SGM ON SGM.Id = SM.StudentGroupId -- Mantis : 0027927
			
	WHERE
			A.OrgId=@rOrgId
			AND	A.BatchId = @rBatchId
			AND ((A.ClassId=@rClassId) OR (@rClassId=0))
			AND ((A.DivisionId=@rDivisionId) OR (@rDivisionId=0))
			AND ((SM.Code = @rCode) OR (ISNULL(@rCode,'') = ''))
			AND ((SM.FirstName = @rName) OR (ISNULL(@rName,'') = ''))
			AND ((FT.FirstTrialDate>=@rFromDate) OR (ISNULL(@rFromDate,'')=''))
			AND ((FT.FirstTrialDate<=@rToDate) OR (ISNULL(@rToDate,'')=''))
			AND ((FT.Number=@rNumber) OR (@rNumber=''))
			AND ((FT.Number >= @rFromNo) OR (@rFromNo=''))
			AND ((FT.Number <= @rToNo) OR (@rToNo=''))
			--Group By FT.AcademicId,SM.Code,SM.FirstName,SM.LastName,SM.MiddleName,FT.SeatNo,FT.Trial,SM.Code,SM.FirstName,OM.StateName,SO.GenderId,
			--OM.CityName,OM.StateName,FT.FirstTrialDate,FT.ExamCentre,FT.ExamYear,SM.MiddleName,SM.LastName,OM.CityName,SM.StatusId
			--,OM.OrgLogo,OM.PhoneNo,OM.Title,OM.StreetOne,OM.StreetTwo,OM.StreetThree
	END
GO
PRINT N'Altering Procedure [sCommon].[pScreenGroupDataListGet]...';


GO
ALTER PROCEDURE [sCommon].[pScreenGroupDataListGet]
          
           @rScreenGroupId int,
           @rUserId bigint   
AS
SET NOCOUNT ON

 
	BEGIN
	
	IF EXISTS(SELECT 1 FROM sSecurity.tPermissionUser WHERE UserId = @rUserId)
	BEGIN
		Select 
			S.Id AS Id,
			LEFT(S.Title,40) AS Title,
				S.IconPath,
			 CASE WHEN S.Id = 20020 THEN Page+'?MenuID=5310'
			      WHEN S.Id = 20033 THEN Page+'?MenuID=5410'
			      WHEN S.Id = 20037 THEN Page+'?MenuID=5111'
			      WHEN S.Id = 20038 THEN Page+'?MenuID=5112'
			      WHEN S.Id = 9018 THEN Page+'?MenuID=331111'
			      --WHEN S.Id = 297 THEN Page+'?MenuID=181115'
			      --WHEN S.Id = 296 THEN Page+'?MenuID=181116'
			      WHEN S.Id = 449 THEN Page+'?MenuID=221214'
			       WHEN S.Id = 5 THEN Page+'?MenuID=161014'
			         WHEN S.Id = 191 THEN Page+'?MenuID=15101015'
			         WHEN S.Id = 32 THEN Page+'?MenuID=141112'
			         
			           --WHEN S.Id = 57 THEN Page+'?MenuID=312052'
			   ELSE Page 
			   END AS Page,
			   S.OrderIndex	,
			   
			  ISNULL( S.ScreenGroupId,0) AS  ScreenGroupId,
			  T.ResourceTypeId,
			  RTM.Title AS ResourceTypeTitle
			  		  
			   From sSecurity.tScreen S
	    
		INNER JOIN sSecurity.tPermissionUser P ON P.TaskId=S.TaskId
		--INNER JOIN sSecurity.tUserRole U ON U.RoleId=P.RoleId
		INNER JOIN sSecurity.tTask T ON T.ID = p.TaskId
		INNER JOIN sSecurity.tResourceTypeMaster RTM ON RTM.ID = T.ResourceTypeId
		INNER JOIN sSecurity.tOrganizationTask OT ON T.Id = OT.TaskId AND OT.OrgGroupId = P.OrgGroupId
		INNER JOIN sSecurity.tModulePermission MP ON T.ModuleId = MP.ModuleId And OT.OrgGroupId = MP.OrgGroupId
	 
		WHERE 
		(P.Permission & 2) =2
		AND ( ScreenGroupId=@rScreenGroupId OR @rScreenGroupId = 0)
		-- AND U.IsObsolete=0
		AND P.UserId=@rUserId 
		AND T.IsObsolete = 0
		AND S.IsObsolete = 0
		AND S.IsHideInScreenGroup = 0
		
		--GROUP By S.Title,S.Id,Page,S.OrderIndex,S.IconPath
		Order by RTM.OrderIndex,S.OrderIndex 
	END
	ELSE
	BEGIN
		Select 
			S.Id AS Id,
			LEFT(S.Title,40) AS Title,
				S.IconPath,
			 CASE WHEN S.Id = 20020 THEN Page+'?MenuID=5310'
			      WHEN S.Id = 20033 THEN Page+'?MenuID=5410'
			      WHEN S.Id = 20037 THEN Page+'?MenuID=5111'
			      WHEN S.Id = 20038 THEN Page+'?MenuID=5112'
			      WHEN S.Id = 9018 THEN Page+'?MenuID=331111'
			      --WHEN S.Id = 297 THEN Page+'?MenuID=181115'
			      --WHEN S.Id = 296 THEN Page+'?MenuID=181116'
			      WHEN S.Id = 449 THEN Page+'?MenuID=221214'
			       WHEN S.Id = 5 THEN Page+'?MenuID=161014'
			         WHEN S.Id = 191 THEN Page+'?MenuID=15101015'
			         WHEN S.Id = 32 THEN Page+'?MenuID=141112'
			         
			           --WHEN S.Id = 57 THEN Page+'?MenuID=312052'
			   ELSE Page 
			   END AS Page,
			   S.OrderIndex	,
			  ISNULL( S.ScreenGroupId,0) AS  ScreenGroupId,
			   T.ResourceTypeId,
			  RTM.Title AS ResourceTypeTitle		  
			   From sSecurity.tScreen S
	    
		INNER JOIN sSecurity.tPermission P ON P.TaskId=S.TaskId
		INNER JOIN sSecurity.tUserRole U ON U.RoleId=P.RoleId
		INNER JOIN sSecurity.tTask T ON T.ID = p.TaskId
		INNER JOIN sSecurity.tResourceTypeMaster RTM ON RTM.ID = T.ResourceTypeId
		INNER JOIN sSecurity.tOrganizationTask OT ON T.Id = OT.TaskId AND OT.OrgGroupId = P.OrgGroupId
		INNER JOIN sSecurity.tModulePermission MP ON T.ModuleId = MP.ModuleId And OT.OrgGroupId = MP.OrgGroupId
	 
		WHERE 
		(P.Permission & 2) =2
		AND ( ScreenGroupId=@rScreenGroupId OR @rScreenGroupId = 0)
		 AND U.IsObsolete=0
		AND U.UserId=@rUserId 
		AND T.IsObsolete = 0
		AND S.IsObsolete = 0
		AND S.IsHideInScreenGroup = 0
		
		--GROUP By S.Title,S.Id,Page,S.OrderIndex,S.IconPath
		Order by RTM.OrderIndex,S.OrderIndex 
	END
	END
GO
PRINT N'Altering Procedure [sCommunication].[pEbookScopeGet]...';


GO
ALTER PROCEDURE [sCommunication].[pEbookScopeGet]
	 @rEbookId bigint
	 --@rBatchId int
	 --@rOrgId smallint,
	 --@rOrgGroupId smallint
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN 		
		
				SELECT
			 CS.EbookId,
			 CS.SourceTypeId,
			 CS.SourceId,
			 OM.DisplayTitle AS Source,
			 'Organization' AS SourceType
			 
			FROM sCommunication.tEbookScope CS
			INNER JOIN sSetup.tOrganizationMaster OM
				ON CS.SourceId=OM.Id
			WHERE CS.EbookId=@rEbookId AND CS.SourceTypeId=2021

			UNION ALL

			SELECT
			 CS.EbookId,
			 CS.SourceTypeId,
			 CS.SourceId,
			 DM.DisplayTitle AS Source,
			 'Department' AS SourceType
			 
			FROM sCommunication.tEbookScope CS
			INNER JOIN sSetup.tDepartmentMaster DM
				ON CS.SourceId=DM.Id
			WHERE CS.EbookId=@rEbookId AND CS.SourceTypeId=2004

			UNION ALL

			SELECT
			 CS.EbookId,
			 CS.SourceTypeId,
			 CS.SourceId,
			 CM.DisplayTitle AS Source,
			 'Class' AS SourceType
			 
			FROM sCommunication.tEbookScope CS
			INNER JOIN  sSetup.tClassMaster CM
				ON CS.SourceId=CM.Id
			WHERE CS.EbookId=@rEbookId AND CS.SourceTypeId=2016

			UNION ALL

			SELECT
			 CS.EbookId,
			 CS.SourceTypeId,
			 CS.SourceId,
			 CM.DisplayTitle +'-'+ DM.DisplayTitle AS Source,
			 'Division' AS SourceType
			 
			FROM sCommunication.tEbookScope CS
			INNER JOIN   sSetup.tDivisionMaster DM
				ON CS.SourceId=DM.Id
			--Add by Dhananjay Mantis 0034410
			Inner JOIN sSetup.tClassMaster CM 
				ON DM.ClassId=CM.Id
			--Add by Dhananjay Mantis 0034410
			WHERE CS.EbookId=@rEbookId AND CS.SourceTypeId=2005


			UNION ALL
			
			SELECT
			 CS.EbookId,
			 CS.SourceTypeId,
			 CS.SourceId,
			 [sStudent].[fStudentNameGet](SM.FirstName , SM.MiddleName, SM.LastName,OM.StudentNameFormatId ) AS Source,
			 'Student' AS SourceType
			 
			FROM sCommunication.tEbookScope CS
		    INNER JOIN sStudent.tStudentMaster SM
				ON CS.SourceId=SM.Id	
			INNER JOIN sSetup.tOrganizationMaster OM
				ON SM.OrgId = OM.Id
			WHERE CS.EbookId=@rEbookId AND CS.SourceTypeId=2003
			
			UNION ALL
			
			SELECT
			 CS.EbookId,
			 CS.SourceTypeId,
			 CS.SourceId,
			 SA.Name AS Source,
			 'Student' AS SourceType
			 
			FROM sCommunication.tEbookScope CS
		   	INNER JOIN  sStudent.vStudentAcademic SA
				ON CS.SourceId=SA.AcademicId AND SA.IsActiveBatch=1
			WHERE CS.EbookId=@rEbookId AND CS.SourceTypeId=2001
			
			

			--SELECT
			-- CS.CircularId,
			-- CS.SourceTypeId,
			-- CS.SourceId,
			-- SA.Name AS Source,
			-- 'Student' AS SourceType
			 
			--FROM sCommunication.tCircularScope CS
			--INNER JOIN  sStudent.vStudentAcademic SA
			--	ON CS.SourceId=SA.StudentId AND SA.IsActiveBatch=1
			--WHERE CS.CircularId=@rCircularId AND CS.SourceTypeId=2003
				





	
		
END
GO
PRINT N'Altering Procedure [sFee].[pDeleteFee]...';


GO
ALTER PROCEDURE [sFee].[pDeleteFee]
	 
		@rReceiptBookId int=0,	 
		@rClassId int=0,
		@rDivisionId int=0,	 
		@rBatchId int=0,	 
		@rOrgId int=0,
		@rOrgGroupBatchId int=0,
		@rStatusId int=0, 
	    @rCode nvarchar(20)='',
	    @rSourceId bigint,
	    @rDeleteType nvarchar(20)='',
	    @rSourceTypeId smallint=0,
	    @rFromReceiptNo int,
	    @rToReceiptNo int,
		@rMultiReceiptNo nvarchar(max) = ''
	    
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 
BEGIN 	

IF (@rDeleteType = 'Receipt')
BEGIN
DELETE sFee.tReceiptDetail 
		FROM sFee.tReceiptDetail RD
		INNER JOIN sFee.tReceipt R  
		ON R.Id=RD.ReceiptId
		INNER JOIN sStudent.vStudentAcademic vSA 
		ON vSA.AcademicId=R.SourceId
	WHERE 
		R.ReceiptBookId=@rReceiptBookId
		AND ((R.ReceiptNumber >= @rFromReceiptNo) OR (@rFromReceiptNo=0))
		AND ((R.ReceiptNumber <= @rToReceiptNo) OR (@rToReceiptNo=0))
		AND ((R.SourceId=@rSourceId) OR (@rSourceId=0))
		AND ((R.SourceTypeId=@rSourceTypeId) OR (@rSourceTypeId=0))
		AND ((vSA.ClassId=@rClassId) OR (@rClassId=0))
		AND ((vSA.DivisionId=@rDivisionId) OR (@rDivisionId=0))
		AND vSA.BatchId=@rBatchId
		AND ((vSA.OrgId=@rOrgId) OR (@rOrgId=0))
		AND (R.ReceiptNumber IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rMultiReceiptNo,',')) OR @rMultiReceiptNo='')

	-- For Mantis 0023321 By Foram on 14/03/2024 START
	SELECT R.Id INTO #TempReceiptId
		FROM sFee.tReceipt R
		INNER JOIN sStudent.vStudentAcademic vSA 
		ON vSA.AcademicId=R.SourceId
	WHERE 
		R.ReceiptBookId=@rReceiptBookId
		AND ((R.ReceiptNumber >= @rFromReceiptNo) OR (@rFromReceiptNo=0))
		AND ((R.ReceiptNumber <= @rToReceiptNo) OR (@rToReceiptNo=0))
		AND ((R.SourceId=@rSourceId) OR (@rSourceId=0))
		AND ((R.SourceTypeId=@rSourceTypeId) OR (@rSourceTypeId=0))
		AND ((vSA.ClassId=@rClassId) OR (@rClassId=0))
		AND ((vSA.DivisionId=@rDivisionId) OR (@rDivisionId=0))
		AND vSA.BatchId=@rBatchId
		AND ((vSA.OrgId=@rOrgId) OR (@rOrgId=0))
		AND (R.ReceiptNumber IN (SELECT cast(item as int) FROM  sCommon.fnsplit(@rMultiReceiptNo,',')) OR @rMultiReceiptNo='')
	-- For Mantis 0023321 By Foram on 14/03/2024 END
	
	DELETE sFee.tReceipt 
		FROM sFee.tReceipt R
		INNER JOIN sStudent.vStudentAcademic vSA 
		ON vSA.AcademicId=R.SourceId
	WHERE 
		R.ReceiptBookId=@rReceiptBookId
		AND ((R.ReceiptNumber >= @rFromReceiptNo) OR (@rFromReceiptNo=0))
		AND ((R.ReceiptNumber <= @rToReceiptNo) OR (@rToReceiptNo=0))
		AND ((R.SourceId=@rSourceId) OR (@rSourceId=0))
		AND ((R.SourceTypeId=@rSourceTypeId) OR (@rSourceTypeId=0))
		AND ((vSA.ClassId=@rClassId) OR (@rClassId=0))
		AND ((vSA.DivisionId=@rDivisionId) OR (@rDivisionId=0))
		AND vSA.BatchId=@rBatchId
		AND ((vSA.OrgId=@rOrgId) OR (@rOrgId=0))
		AND (R.ReceiptNumber IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rMultiReceiptNo,',')) OR @rMultiReceiptNo='')

	-- For Mantis 0023321 By Foram on 14/03/2024 START
	UPDATE sFee.tPostDatePayment SET ReceiptId = 0, StatusId = CASE WHEN ISNULL(DepositedDate, '') <> '' THEN 753 ELSE 751 END
	WHERE ReceiptId IN (SELECT Id FROM #TempReceiptId)
	-- For Mantis 0023321 By Foram on 14/03/2024 END

	SELECT 1 AS Result
END

IF(@rDeleteType = 'FeeAllocation')
BEGIN

	IF EXISTS
	(
	SELECT 1 FROM 
	    sFee.tFeeAllocation FA
		INNER JOIN sFee.tFeeSetup FS 
		ON FS.Id=FA.FeeSetupId
		INNER JOIN sStudent.vStudentAcademic vSA 
		ON vSA.AcademicId=FA.SourceId
		INNER JOIN sFee.tReceipt R 
		ON FA.SourceId = R.SourceId AND FA.SourceTypeId = R.SourceTypeId
	WHERE 
		((R.ReceiptBookId=@rReceiptBookId) OR (@rReceiptBookId=0))
		AND ((R.SourceId=@rSourceId) OR (@rSourceId=0))
		AND ((R.SourceTypeId=@rSourceTypeId) OR (@rSourceTypeId=0))
		AND ((vSA.ClassId=@rClassId) OR (@rClassId=0))
		AND ((vSA.DivisionId=@rDivisionId) OR (@rDivisionId=0))
		AND vSA.BatchId=@rBatchId
		AND ((vSA.OrgId=@rOrgId) OR (@rOrgId=0))
    )
    BEGIN
		SELECT -101 AS Result
		RETURN
	END 

    DELETE sFee.tFeeAllocation
		FROM sFee.tFeeAllocation FA
		INNER JOIN sFee.tFeeSetup FS 
		ON FS.Id=FA.FeeSetupId
		INNER JOIN sStudent.vStudentAcademic vSA 
		ON vSA.AcademicId=FA.SourceId
	WHERE 
		((FS.ReceiptBookId=@rReceiptBookId) OR (@rReceiptBookId=0))
		AND ((FA.SourceId=@rSourceId) OR (@rSourceId=0))
		AND ((FA.SourceTypeId=@rSourceTypeId) OR (@rSourceTypeId=0))
		AND ((vSA.ClassId=@rClassId) OR (@rClassId=0))
		AND ((vSA.DivisionId=@rDivisionId) OR (@rDivisionId=0))
		AND vSA.BatchId=@rBatchId
		AND ((vSA.OrgId=@rOrgId) OR (@rOrgId=0))
SELECT 2 AS Result
END	
		
IF(@rDeleteType = 'FeeOptional')
BEGIN
	
	IF EXISTS
	( 
    SELECT 1 FROM
        sFee.tFeeOptional FO
		INNER JOIN sFee.tFeeSetup FS 
		ON FS.Id=FO.FeeSetupId
		INNER JOIN sStudent.vStudentAcademic vSA 
		ON vSA.AcademicId=FO.SourceId
		INNER JOIN sFee.tReceipt R 
		ON FO.SourceId = R.SourceId AND FO.SourceTypeId = R.SourceTypeId
	WHERE 
		((R.ReceiptBookId=@rReceiptBookId) OR (@rReceiptBookId=0))
		AND ((R.SourceId=@rSourceId) OR (@rSourceId=0))
		AND ((R.SourceTypeId=@rSourceTypeId) OR (@rSourceTypeId=0))
		AND ((vSA.ClassId=@rClassId) OR (@rClassId=0))
		AND ((vSA.DivisionId=@rDivisionId) OR (@rDivisionId=0))
		AND vSA.BatchId=@rBatchId
		AND ((vSA.OrgId=@rOrgId) OR (@rOrgId=0))
    )
    BEGIN
		SELECT -102 AS Result
		RETURN
	END 

    DELETE sFee.tFeeOptional
		FROM sFee.tFeeOptional FO
		INNER JOIN sFee.tFeeSetup FS 
		ON FS.Id=FO.FeeSetupId
		INNER JOIN sStudent.vStudentAcademic vSA 
		ON vSA.AcademicId=FO.SourceId
	WHERE 
		((FS.ReceiptBookId=@rReceiptBookId) OR (@rReceiptBookId=0))
		AND ((FO.SourceId=@rSourceId) OR (@rSourceId=0))
		AND ((FO.SourceTypeId=@rSourceTypeId) OR (@rSourceTypeId=0))
		AND ((vSA.ClassId=@rClassId) OR (@rClassId=0))
		AND ((vSA.DivisionId=@rDivisionId) OR (@rDivisionId=0))
		AND vSA.BatchId=@rBatchId
		AND ((vSA.OrgId=@rOrgId) OR (@rOrgId=0))		
SELECT 3 AS Result
END
END
GO
PRINT N'Altering Procedure [sFee].[pFeeCancelReceipt]...';


GO
ALTER PROCEDURE [sFee].[pFeeCancelReceipt] 
	@rReceiptId BIGINT
	,@rRemark NVARCHAR(300) = ''
	,@rUserId BIGINT = 0
	,@rCancelStatusId INT
	,@rCancelRemark NVARCHAR(300) = ''

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN
	BEGIN TRY
		BEGIN TRANSACTION

		IF EXISTS (
				SELECT 1
				FROM sFee.tReceipt
				WHERE Id = @rReceiptId
					AND IsObsolete = 0
				)
		BEGIN
			DECLARE @lFeeSetupId INT
				,@rCharge DECIMAL(12, 2) = 0
				,@lSourceId BIGINT
				,@lSourceTypeId INT

			SELECT @lFeeSetupId = FS.ID
				,@rCharge = RD.Amount
				,@lSourceId = R.SourceId
				,@lSourceTypeId = SourceTypeId
			FROM sFee.tReceiptDetail RD
			INNER JOIN sFee.tFeeSetup FS ON RD.FeeSetupId = FS.Id
			INNER JOIN sFee.tReceipt R ON R.Id = RD.ReceiptId
			INNER JOIN sFee.tFeeMaster FM ON FS.FeeId = FM.Id
			INNER JOIN sFee.tFeeHeadMaster FH ON FH.Id = FM.FeeHeadId
			WHERE RD.ReceiptId = @rReceiptId
				AND FH.Number = 100
				AND FS.IsVariable = 1

			IF @lFeeSetupId IS NOT NULL
			BEGIN
				UPDATE sFee.tFeeAllocation
				SET VariableAmount = VariableAmount - @rCharge
				WHERE FeeSetupId = @lFeeSetupId
					AND SourceTypeId = @lSourceTypeId
					AND SourceId = @lSourceId

				DELETE
				FROM sFee.tFeeAllocation
				WHERE FeeSetupId = @lFeeSetupId
					AND SourceTypeId = @lSourceTypeId
					AND SourceId = @lSourceId
					AND (VariableAmount - DiscountAmount - NotApplicableAmount + AdditionalAmount <= 0)
			END
		END

		BEGIN
			UPDATE sFee.tReceipt
			SET IsObsolete = 1
				,Remark = @rRemark
				,CancelStatusId = @rCancelStatusId
				,CancelStatusRemark = @rCancelRemark
			WHERE Id = @rReceiptId

			UPDATE sFee.tPostDatePayment
			SET ReceiptId = 0
				,StatusId = CASE WHEN ISNULL(DepositedDate, '') <> '' THEN 753 ELSE 751 END
			WHERE ReceiptId = @rReceiptId

			SELECT 1 AS Result
		END

		COMMIT TRANSACTION
	END TRY

	BEGIN CATCH
		ROLLBACK TRANSACTION

		SELECT - 1 AS Result
	END CATCH
END
GO
PRINT N'Altering Procedure [sFee].[pGSTReceiptAdd]...';


GO
ALTER PROCEDURE [sFee].[pGSTReceiptAdd]
	
	@rId bigint,
	@rSourceTypeId smallint,
	@rSourceId bigint,
	@rReceiptNumber int,
	@rReceiptDate date,
	@rReceiptAmount decimal(12,2),
	@rTotalReceiptAmount decimal(12,2),
	@rDiscountAmount decimal(12,2),
	@rPayTypeId tinyint,
	@rChequeNo nvarchar(40),
	@rBankName nvarchar(200),
	@rReceiptBookId int,
	@rIsObsolete bit,
	@rCreatedUserId bigint,
	@rOrgId int,
	@rBatchId int,
	@rOrgGroupBatchId int,
	@rRemark nvarchar(200),
	@rReceiptDetailXml [nvarchar](max),
	@rChequeDate date,
	@rCharge decimal(12,2)=0,
	@rFeeInvoiceId bigint = NULL,
	@rCollectionGroupId int = 0,
	@rPaymentTransactionId bigint = 0,
	@rIsDiscountCoupon bit = 0,
	@rReceiptPrintGroupId nvarchar(100) = ''

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

	DECLARE @lItemValue nvarchar(20) = ''
	SELECT @lItemValue = CASE WHEN CS.Id IS NOT NULL THEN ISNULL(CS.ItemValue,'') ELSE ISNULL(CM.ItemValue,'') END
	FROM sConfig.tConfigurationMaster CM
	LEFT JOIN sConfig.tConfigurationSetup CS ON CM.Id = CS.ConfigurationId
	AND SourceTypeId = 1016 AND SourceId = @rOrgId WHERE CM.Id = 239

	-- GST
	DECLARE @lAmountAfterDiscount decimal(12,2) = NULL
	DECLARE @lCGSTPercentage decimal(12,2)  = NULL
	DECLARE @lSGSTPercentage decimal(12,2)  = NULL
	DECLARE @lCGSTAmount decimal(12,2)  = NULL
	DECLARE @lSGSTAmount decimal(12,2)  = NULL

	IF(@lItemValue='True' AND @rPayTypeId = 1)
	BEGIN 
		SET @lAmountAfterDiscount = 0
		SET @lCGSTAmount = 0
		SET @lSGSTAmount = 0
		SET @lCGSTPercentage = 0
		SET @lSGSTPercentage = 0
	END
	ELSE
	BEGIN 		
		SET @lAmountAfterDiscount = @rTotalReceiptAmount - @rDiscountAmount
		SELECT @lCGSTPercentage = CGSTPercentage,@lSGSTPercentage = SGSTPercentage FROM sFee.tReceiptBookMaster WHERE Id = @rReceiptBookId
		SET @lCGSTAmount = @rReceiptAmount*@lCGSTPercentage / 100
		SET @lSGSTAmount = @rReceiptAmount*@lSGSTPercentage / 100
		SET @rReceiptAmount = @lAmountAfterDiscount + @lCGSTAmount + @lSGSTAmount
	END
-- GST
	
	DECLARE @loDoc INT
	DECLARE @lIsCreated INT
	EXEC sp_xml_preparedocument @loDoc OUTPUT, @rReceiptDetailXml

	BEGIN
	BEGIN TRY

	 SELECT @rOrgGroupBatchId = OrgGroupBatchId FROM sSetup.tBatchMaster where Id = @rBatchId
	
	 DECLARE @lConfigValue nvarchar(50)=''
	 SELECT @lConfigValue = ItemValue FROM sConfig.tConfigurationSetup WHERE ConfigurationId = 339 AND SourceTypeId = 2010 AND SourceId = LEFT(@rOrgGroupBatchId,4)

	 IF @lConfigValue IS NULL 
		SET @lConfigValue = 'ReceiptBookWise'
	 
	IF(@rReceiptNumber <> 0)
	BEGIN
		IF(@lConfigValue='ReceiptBookWise')
		BEGIN
			IF EXISTS(SELECT 1 from sFee.tReceipt where ReceiptNumber=@rReceiptNumber AND ReceiptBookId=@rReceiptBookId AND OrgGroupBatchId = @rOrgGroupBatchId)
			BEGIN
				SELECT -102 AS Result
				RETURN	
			END
		END
		ELSE IF(@lConfigValue='PayTypeWise')
		BEGIN
			IF EXISTS(SELECT 1 from sFee.tReceipt where ReceiptNumber=@rReceiptNumber AND ReceiptBookId=@rReceiptBookId AND  PayTypeId=@rPayTypeId AND OrgGroupBatchId = @rOrgGroupBatchId)
			BEGIN
				SELECT -102 AS Result
				RETURN	
			END
		END
	END
	ELSE
	BEGIN
		SET @rReceiptNumber = sFee.fReceiptNumberGet(@rReceiptBookId,@rOrgGroupBatchId,@rPayTypeId,@lConfigValue)
		Print @rReceiptNumber
	END	

	EXECUTE [sFee].[pReceiptAddValidate] @rSourceId,'',@rReceiptDetailXml,@rPayTypeId,@rPaymentTransactionId,@rReceiptBookId,@rIsCreated = @lIsCreated OUTPUT
 	
	IF(@lIsCreated = 1)
 	BEGIN
		SELECT -501 AS Result
		RETURN
	END
	
	IF(@lIsCreated = -1)	-- For Mantis 0032370 by Foram
 	BEGIN
		SELECT -502 AS Result
		RETURN
	END
 
	 BEGIN TRANSACTION	
	 IF(@rId = 0)	
	 BEGIN

	 DECLARE @lNextNumber BIGINT     
	 EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sFee.tReceipt', 0, 1, 1,@outNextNumber = @lNextNumber output


	 INSERT INTO sFee.tReceipt
		(
		   Id,
		   SourceTypeId,
		   SourceId,
		   ReceiptNumber,
		   ReceiptDate,
		   ReceiptAmount,
		   TotalReceiptAmount,
		   DiscountAmount,
		  PayTypeId,
		   ChequeNo,
		   BankName,
		   ReceiptBookId,
		   Remark,
		   IsObsolete,
		   BatchId,
		   CreatedUserId,
		   CreatedDateTime,
		   OrgGroupBatchId,
		   ChequeDate,
		   FeeInvoiceId,
		   CollectionGroupId,
		   AmountAfterDiscount,
		   CGSTPercentage,
		   SGSTPercentage,
		   CGSTAmount,
		   SGSTAmount,
		   PaymentTransactionId,
		   ReceiptPrintGroupId,
		   IsDiscountCoupon  ---Added By Dhruv for Issue . 0011150
        )
	VALUES
		(
			@lNextNumber,
			@rSourceTypeId,
			@rSourceId,
		    @rReceiptNumber,
		    @rReceiptDate,
		    @rReceiptAmount,
		    @rTotalReceiptAmount,
		    @rDiscountAmount,
		    @rPayTypeId,
		    @rChequeNo,
		    @rBankName,
		    @rReceiptBookId,
		    @rRemark,
		    @rIsObsolete,
		    @rBatchId,
		    @rCreatedUserId,
		    sCommon.fGetDate(),
		    @rOrgGroupBatchId,
		    @rChequeDate,
		    @rFeeInvoiceId,
		    @rCollectionGroupId,
		    @lAmountAfterDiscount,
		    @lCGSTPercentage,
		    @lSGSTPercentage,
		    @lCGSTAmount,
		    @lSGSTAmount,
		    @rPaymentTransactionId,
			@rReceiptPrintGroupId,
			@rIsDiscountCoupon
		)
		
		INSERT INTO sFee.tReceiptDetail
		(
		   ReceiptId,
		   FeeSetupId,
		   Amount
        )
        SELECT  	
		  @lNextNumber,
		  FeeSetupId,
		  Amount
		
	    FROM OPENXML (@loDoc, '/Table/ReceiptDetail', 1)
		WITH ( 
				FeeSetupId int,
				Amount decimal(12,2)
			  ) 
		
		SELECT @lNextNumber AS Result
		EXEC sp_xml_removedocument @loDoc
		
		IF (@rCharge > 0)
		BEGIN
			EXEC [sFee].[pReceiptChargeAdd] @rSourceTypeId,@rSourceId,@lNextNumber,@rCreatedUserId,@rOrgId,@rBatchId,@rCharge,@rReceiptBookId
		END
	
		DECLARE @lIsServiceTaxApplicable bit
	    DECLARE @lServiceTaxPercentage decimal(6,2)
	
	    SELECT @lIsServiceTaxApplicable = ISNULL(IsServiceTaxApplicable,0),@lServiceTaxPercentage = ISNULL(ServiceTaxPercentage,0) FROM sFee.tReceiptBookMaster where Id = @rReceiptBookId
		
		IF (@lIsServiceTaxApplicable > 0 AND @lServiceTaxPercentage > 0)
		BEGIN
		EXEC [sFee].[pReceiptServiceTaxAdd] @rSourceTypeId,@rSourceId,@lNextNumber,@rCreatedUserId,@rOrgId,@rBatchId,@lServiceTaxPercentage
		END

		SET @rId = @lNextNumber
	END 
	ELSE
	BEGIN
		UPDATE sFee.tReceipt SET
			ReceiptDate=@rReceiptDate,
			PayTypeId=@rPayTypeId,
			ChequeDate=@rChequeDate,
		    ChequeNo=@rChequeNo,
		    BankName=@rBankName,
		    Remark=@rRemark
		WHERE Id=@rId
		
		SELECT @rId AS RESULT
	END 
		
	-- For Mantis 0023321 By Foram on 14/03/2024 START
	DECLARE @lSourceId bigint, @lSourceTypeId1 smallint, @lBatchId int, @lOrgGroupBatchId int, @lReceiptBookId int, 
		    @lPayTypeId smallint, @lChequeNo nvarchar(40), @lBankName nvarchar(200), @lTotalReceiptAmount decimal(12,2)

	SELECT 
		@lSourceId = SourceId, 
		@lSourceTypeId1 = SourceTypeId, 
		@lBatchId = BatchId, 
		@lOrgGroupBatchId = OrgGroupBatchId, 
		@lReceiptBookId = ReceiptBookId, 
		@lPayTypeId = PayTypeId, 
		@lChequeNo = ChequeNo, 
		@lBankName = BankName, 
		@lTotalReceiptAmount = TotalReceiptAmount
	FROM sFee.tReceipt WHERE Id = @rId


	IF EXISTS(SELECT 1 FROM sFee.tPostDatePayment WHERE BatchId = @lBatchId AND OrgGroupBatchId = @lOrgGroupBatchId 
													AND SourceTypeId = @lSourceTypeId1 AND SourceId = @lSourceId AND ReceiptBookId = @lReceiptBookId 
													AND PayTypeId = @lPayTypeId AND ChequeNo = @lChequeNo AND BankName = @lBankName AND Amount = @lTotalReceiptAmount)
	BEGIN
		UPDATE sFee.tPostDatePayment 
		SET ReceiptId = @rId, StatusId = 752 
		WHERE BatchId = @lBatchId AND OrgGroupBatchId = @lOrgGroupBatchId 
			AND SourceTypeId = @lSourceTypeId1 AND SourceId = @lSourceId AND ReceiptBookId = @lReceiptBookId 
			AND PayTypeId = @lPayTypeId AND ChequeNo = @lChequeNo AND BankName = @lBankName AND Amount = @lTotalReceiptAmount
	END
	ELSE
	BEGIN
		UPDATE sFee.tPostDatePayment SET ReceiptId = 0, StatusId = CASE WHEN ISNULL(DepositedDate, '') <> '' THEN 753 ELSE 751 END WHERE ReceiptId = @rId
	END
	-- For Mantis 0023321 By Foram on 14/03/2024 END
	
	COMMIT TRANSACTION
		
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
		
END
GO
PRINT N'Altering Procedure [sFee].[pPostDatedPaymentAdd]...';


GO
ALTER PROCEDURE sFee.pPostDatedPaymentAdd
	@rPostDatedPaymentXML  nvarchar(MAX),
	@rOrgId int,
	@rBatchId int,
	@rOrgGroupBatchId int,
	@rCreatedUserId bigint,
	@rSourceId bigint,
	@rSourceTypeId int

AS
SET NOCOUNT ON
BEGIN

	DECLARE @lInterrorCode INT,@intDoc INT
	DECLARE @lNextNumber BIGINT			
 
	EXECUTE @lInterrorCode = sp_xml_preparedocument @intDoc OUTPUT, @rPostDatedPaymentXML
	
	BEGIN TRY

	CREATE TABLE #tempTable  
	(
		AutoId bigint identity
		,Id	bigint
		,ReceiptBookId	int
		,PayTypeId	tinyint
		,PaymentClearanceDate	date
		,ChequeNo	nvarchar(40)
		,BankName	nvarchar(200)
		,Amount	decimal
		,Remark	nvarchar(200)
	)

	INSERT INTO #tempTable
	(
		Id
		,ReceiptBookId
		,PayTypeId
		,PaymentClearanceDate
		,ChequeNo
		,BankName
		,Amount
		,Remark
	)
	SELECT	
		A.Id
		,A.ReceiptBookId
		,A.PayTypeId
		,A.PaymentClearanceDate
		,A.ChequeNo
		,A.BankName
		,A.Amount
		,A.Remark
	FROM OPENXML (@intDoc, '/Table/PostDatedPayment', 1)
	WITH
	(
		Id	bigint					 '@Id'
		,ReceiptBookId	int			 '@ReceiptBookId'
		,PayTypeId	tinyint			 '@PayTypeId'
		,PaymentClearanceDate date   '@PaymentClearanceDate'
		,ChequeNo	nvarchar(40)	 '@ChequeNo'
		,BankName	nvarchar(200)	 '@BankName'
		,Amount	decimal				 '@Amount'
		,Remark	nvarchar(200)		 '@Remark'
	)A 

	DECLARE @lCount int,@lId int
	SELECT @lCount = COUNT(Id) FROM #tempTable
	SELECT Top 1 @lId=AutoId FROM #tempTable
	
	WHILE @lId <= @lCount 
	BEGIN
		
		DECLARE @lId1 bigint = 0
		SELECT @lId1 = Id FROM #tempTable WHERE AutoId = @lId
		
		IF(@lId1 = 0)
		BEGIN
			EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sFee.tPostDatePayment', 0, 1, 1,@outNextNumber = @lNextNumber output

			INSERT INTO sFee.tPostDatePayment
			(	
				Id
				,OrgId
				,BatchId
				,OrgGroupBatchId
				,SourceTypeId
				,SourceId
				,ReceiptBookId
				,PayTypeId
				,PaymentClearanceDate
				,ChequeNo
				,BankName
				,Amount
				,Remark
				,CreatedUserId
				,CreatedDateTime
				,StatusId
			)
			SELECT 
				@lNextNumber
				,@rOrgId
				,@rBatchId
				,@rOrgGroupBatchId
				,@rSourceTypeId
				,@rSourceId
				,TT.ReceiptBookId
				,TT.PayTypeId
				,TT.PaymentClearanceDate
				,TT.ChequeNo
				,TT.BankName
				,TT.Amount
				,TT.Remark
				,@rCreatedUserId
				,sCommon.fGetDate()
				,751
			FROM 
				#tempTable TT
			WHERE TT.AutoId = @lId

			SELECT @lNextNumber = MAX(Id) FROM sFee.tPostDatePayment Where OrgId=@rOrgId
			EXECUTE sCommon.pTableRowIdModify @rOrgId, @rTableTitle = 'sFee.tPostDatePayment', @rCurrentNumber = @lNextNumber 
		END
		ELSE
		BEGIN
			DECLARE @lReceiptBookId	int = 0
			DECLARE @lPayTypeId	tinyint = 0
			DECLARE @lPaymentClearanceDate date = NULL
			DECLARE @lChequeNo nvarchar(40) = NULL
			DECLARE @lBankName nvarchar(200) = NULL
			DECLARE @lAmount	decimal = 0
			DECLARE @lRemark	nvarchar(200) = NULL
			
			SELECT @lReceiptBookId = ReceiptBookId
				,@lPayTypeId = PayTypeId
				,@lPaymentClearanceDate = PaymentClearanceDate
				,@lChequeNo = ChequeNo
				,@lBankName = BankName
				,@lAmount = Amount
				,@lRemark = Remark
			 FROM #tempTable WHERE Id = @lId1

			UPDATE sFee.tPostDatePayment
			SET 
				OrgId = @rOrgId
				,BatchId = @rBatchId
				,OrgGroupBatchId = @rOrgGroupBatchId
				,SourceTypeId = @rSourceTypeId
				,SourceId = @rSourceId
				,ReceiptBookId = @lReceiptBookId
				,PayTypeId = @lPayTypeId
				,PaymentClearanceDate = @lPaymentClearanceDate
				,ChequeNo = @lChequeNo
				,BankName = @lBankName
				,Amount = @lAmount
				,Remark = @lRemark
				,UpdatedUserId = @rCreatedUserId
				,UpdatedDateTime = sCommon.fGetDate()
			WHERE Id = @lId1
		END
		
		SET @lId=@lId+1
		
	END
	EXEC sp_xml_removedocument @intDoc
	SELECT 1 as Result
	DROP Table #tempTable
	END TRY	

	BEGIN CATCH 
	SELECT -1 AS Result,error_message() AS MSG, ERROR_LINE()
	END CATCH
END
GO
PRINT N'Altering Procedure [sFee].[pReceiptAdd]...';


GO
ALTER PROCEDURE [sFee].[pReceiptAdd]
	
	@rId bigint,
	@rSourceTypeId smallint,
	@rSourceId bigint,
	@rReceiptNumber int,
	@rReceiptDate date,
	@rReceiptAmount decimal(12,2),
	@rTotalReceiptAmount decimal(12,2),
	@rDiscountAmount decimal(12,2),
	@rPayTypeId tinyint,
	@rChequeNo nvarchar(40),
	@rBankName nvarchar(200),
	@rReceiptBookId int,
	@rIsObsolete bit,
	@rCreatedUserId bigint,
	@rOrgId int,
	@rBatchId int,
	@rOrgGroupBatchId int,
	@rRemark nvarchar(200),
	@rReceiptDetailXml [nvarchar](max),
	@rChequeDate date,
	@rCharge decimal(12,2)=0,
	@rFeeInvoiceId bigint = NULL,
	@rCollectionGroupId int = 0,
	@rDepositedLedgerId int = 0,
	@rPaymentTransactionId bigint = 0,
	@rReceiptPrintGroupId nvarchar(100),
	@rIsDiscountCoupon bit = 0

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

DECLARE @loDoc INT
DECLARE @lIsCreated INT
EXEC sp_xml_preparedocument @loDoc OUTPUT, @rReceiptDetailXml

BEGIN
	BEGIN TRY
	
	 SELECT @rOrgGroupBatchId = OrgGroupBatchId FROM sSetup.tBatchMaster where Id = @rBatchId

	 DECLARE @lConfigValue nvarchar(50)=''
	 SELECT @lConfigValue = ItemValue FROM sConfig.tConfigurationSetup WHERE ConfigurationId = 339 AND SourceTypeId = 2010 AND SourceId = LEFT(@rOrgGroupBatchId,4)

	 IF @lConfigValue = ''
		SET @lConfigValue = 'ReceiptBookWise'

	IF @rChequeDate = '1900-01-01'
		SET @rChequeDate = NULL

	-- Mantis - 0030695
	 DECLARE @lConfigValue1 nvarchar(50)=''
	 SELECT @lConfigValue1 = ItemValue FROM sConfig.tConfigurationSetup WHERE ConfigurationId = 543 AND SourceTypeId = 2010 AND SourceId = LEFT(@rOrgGroupBatchId,4)
	-- Mantis - 0030695	 

	IF(@rReceiptNumber <> 0)
	BEGIN
		IF(@rId = 0)--ID Condition Added by dhruv for issue no.0013266	
		BEGIN
			IF(@lConfigValue='ReceiptBookWise')
			BEGIN
				IF EXISTS(SELECT 1 from sFee.tReceipt where ReceiptNumber=@rReceiptNumber AND ReceiptBookId=@rReceiptBookId AND OrgGroupBatchId = @rOrgGroupBatchId)
				BEGIN
					SELECT -102 AS Result
					RETURN	
				END
			END
			ELSE IF(@lConfigValue='PayTypeWise')
			BEGIN
				IF EXISTS(SELECT 1 from sFee.tReceipt where ReceiptNumber=@rReceiptNumber AND ReceiptBookId=@rReceiptBookId AND  PayTypeId=@rPayTypeId AND OrgGroupBatchId = @rOrgGroupBatchId)
				BEGIN
					SELECT -102 AS Result
					RETURN	
				END
			END
			-- Mantis - 0030695
			IF(@rChequeNo <> '' AND @lConfigValue1 = 'True')
			BEGIN
				IF EXISTS(SELECT 1 from sFee.tReceipt where ChequeNo=@rChequeNo)
				BEGIN
					SELECT -103 AS Result
					RETURN	
				END
			END
			-- Mantis - 0030695
		END
		ELSE
		BEGIN
			IF(@lConfigValue='ReceiptBookWise')
			BEGIN
				IF EXISTS(SELECT 1 from sFee.tReceipt where ReceiptNumber=@rReceiptNumber AND ReceiptBookId=@rReceiptBookId AND OrgGroupBatchId = @rOrgGroupBatchId AND Id != @rId)
				BEGIN
					SELECT -102 AS Result
					RETURN	
				END
			END
			ELSE IF(@lConfigValue='PayTypeWise')
			BEGIN
				IF EXISTS(SELECT 1 from sFee.tReceipt where ReceiptNumber=@rReceiptNumber AND ReceiptBookId=@rReceiptBookId AND  PayTypeId=@rPayTypeId AND OrgGroupBatchId = @rOrgGroupBatchId AND Id != @rId)
				BEGIN
					SELECT -102 AS Result
					RETURN	
				END
			END
			-- Mantis - 0030695
			IF(@rChequeNo <> '' AND @lConfigValue1 = 'True')
			BEGIN
				IF EXISTS(SELECT 1 from sFee.tReceipt where ChequeNo=@rChequeNo AND Id != @rId)
				BEGIN
					SELECT -103 AS Result
					RETURN	
				END
			END
			-- Mantis - 0030695
		END 
	END
	ELSE
	BEGIN
		SET @rReceiptNumber = sFee.fReceiptNumberGet(@rReceiptBookId,@rOrgGroupBatchId,@rPayTypeId,@lConfigValue)
	END	

	EXECUTE [sFee].[pReceiptAddValidate] @rSourceId,'',@rReceiptDetailXml,@rPayTypeId,@rPaymentTransactionId,@rReceiptBookId,@rIsCreated = @lIsCreated OUTPUT -- Added ,@rPayTypeId,@rPaymentTransactionId for mantis 0031290 By Foram	

	IF(@lIsCreated = 1)
 	BEGIN
		SELECT -501 AS Result
		RETURN
	END
	IF(@lIsCreated = -1)	-- For Mantis 0032370 by Foram
 	BEGIN
		SELECT -502 AS Result
		RETURN
	END
 
	 BEGIN TRANSACTION	
	IF(@rId = 0)	
	BEGIN

	DECLARE @lNextNumber BIGINT     
	EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sFee.tReceipt', 0, 1, 1,@outNextNumber = @lNextNumber output
	
--IF EXISTS(SELECT 1 from sFee.tReceipt where ReceiptNumber=@rReceiptNumber AND ReceiptBookId=@rReceiptBookId AND OrgGroupBatchId = @rOrgGroupBatchId)
--			BEGIN
--				SELECT -102 AS Result
--				RETURN	
--			END

		--Added By Dhruv
			IF(@lConfigValue='ReceiptBookWise')
		BEGIN
			IF EXISTS(SELECT 1 from sFee.tReceipt where ReceiptNumber=@rReceiptNumber AND ReceiptBookId=@rReceiptBookId AND OrgGroupBatchId = @rOrgGroupBatchId)
			BEGIN
				SELECT -102 AS Result
				RETURN	
			END
		END
		ELSE IF(@lConfigValue='PayTypeWise')
		BEGIN
			IF EXISTS(SELECT 1 from sFee.tReceipt where ReceiptNumber=@rReceiptNumber AND ReceiptBookId=@rReceiptBookId AND  PayTypeId=@rPayTypeId AND OrgGroupBatchId = @rOrgGroupBatchId)
			BEGIN
				SELECT -102 AS Result
				RETURN	
			END
		END
		--Added By Dhruv
		-- Mantis - 0030695
		IF(@rChequeNo <> '' AND @lConfigValue1 = 'True')
		BEGIN
			IF EXISTS(SELECT 1 from sFee.tReceipt where ChequeNo=@rChequeNo)
			BEGIN
				SELECT -103 AS Result
				RETURN	
			END
		END
		-- Mantis - 0030695

	Declare @lSourceTypeId int 
	SET @lSourceTypeId =(select ISNULL(SourceTypeId,0) from sSecurity.tUser where Id = @rCreatedUserId)

	DECLARE @lCreatedUserId bigint
	IF(@lSourceTypeId = 2001 OR @lSourceTypeId = 2003 OR @rCreatedUserId = 3)
		SET @lCreatedUserId=(Select [sCommon].[fGetBigIntIdForOrg](4,LEFT(@rOrgGroupBatchId,4)))
	ELSE
		SET @lCreatedUserId = @rCreatedUserId 

		INSERT INTO sFee.tReceipt
		(
		   Id,
		   SourceTypeId,
		   SourceId,
		   ReceiptNumber,
		   ReceiptDate,
		   ReceiptAmount,
		   TotalReceiptAmount,
		   DiscountAmount,
		   PayTypeId,
		   ChequeNo,
		   BankName,
		   ReceiptBookId,
		   Remark,
		   IsObsolete,
		   BatchId,
		   CreatedUserId,
		   CreatedDateTime,
		   OrgGroupBatchId,
		   ChequeDate,
		   FeeInvoiceId,
		   CollectionGroupId,
		   DepositedLedgerId,
		   PaymentTransactionId,
		   ReceiptPrintGroupId,
		   IsDiscountCoupon  ---Added By Dhruv for Issue . 0011150
        )
	VALUES
		(
			@lNextNumber,
			@rSourceTypeId,
			@rSourceId,
		    @rReceiptNumber,
		    @rReceiptDate,
		    @rReceiptAmount,
		    @rTotalReceiptAmount,
		    @rDiscountAmount,
		    @rPayTypeId,
		    @rChequeNo,
		    @rBankName,
		    @rReceiptBookId,
		    @rRemark,
		    @rIsObsolete,
		    @rBatchId,
		    @lCreatedUserId,
		    sCommon.fGetDate(),
		    @rOrgGroupBatchId,
		    @rChequeDate,
		    @rFeeInvoiceId,
		    @rCollectionGroupId,
		    @rDepositedLedgerId,
		    @rPaymentTransactionId,
			@rReceiptPrintGroupId,
			@rIsDiscountCoupon
		)
		
		INSERT INTO sFee.tReceiptDetail
		(
		   ReceiptId,
		   FeeSetupId,
		   Amount
        )
        SELECT  	
		  @lNextNumber,
		  FeeSetupId,
		  Amount
		
	FROM OPENXML (@loDoc, '/Table/ReceiptDetail', 1)
		WITH ( 
				FeeSetupId int,
				Amount decimal(12,2)
			  ) 
		
		
		SELECT @lNextNumber AS Result
		EXEC sp_xml_removedocument @loDoc
		
		IF (@rCharge > 0)
		BEGIN
		EXEC [sFee].[pReceiptChargeAdd] @rSourceTypeId,@rSourceId,@lNextNumber,@lCreatedUserId,@rOrgId,@rBatchId,@rCharge,@rReceiptBookId
	
		END
	
		
		DECLARE @lIsServiceTaxApplicable bit
	    DECLARE @lServiceTaxPercentage decimal(6,2)
	
	    SELECT @lIsServiceTaxApplicable = ISNULL(IsServiceTaxApplicable,0),@lServiceTaxPercentage = ISNULL(ServiceTaxPercentage,0) FROM sFee.tReceiptBookMaster where Id = @rReceiptBookId
		
		IF (@lIsServiceTaxApplicable > 0 AND @lServiceTaxPercentage > 0)
		BEGIN
		EXEC [sFee].[pReceiptServiceTaxAdd] @rSourceTypeId,@rSourceId,@lNextNumber,@lCreatedUserId,@rOrgId,@rBatchId,@lServiceTaxPercentage
		END

		EXEC [sFee].[pFeeProcessGeneratePaidFeeAdd] @rOrgId,@rBatchId,@rSourceId,@lNextNumber,@lCreatedUserId
		
		SET @rId = @lNextNumber
	END 
	ELSE
	
	IF EXISTS(SELECT 1 FROM sFee.tReceipt where ReceiptNumber=@rReceiptNumber AND Id = @rId)
	BEGIN
		UPDATE sFee.tReceipt SET
			ReceiptDate=@rReceiptDate,
			PayTypeId=@rPayTypeId,
			ChequeDate=@rChequeDate,
		    ChequeNo=@rChequeNo,
		    BankName=@rBankName,
		    Remark=@rRemark
		WHERE Id=@rId
		
	SELECT @rId AS RESULT
	END 
	ELSE
	IF(@rReceiptNumber <> 0)
	BEGIN
		IF(@lConfigValue='ReceiptBookWise')
		BEGIN
			IF EXISTS(SELECT 1 from sFee.tReceipt where ReceiptNumber=@rReceiptNumber AND ReceiptBookId=@rReceiptBookId AND OrgGroupBatchId = @rOrgGroupBatchId)
			BEGIN
				SELECT -102 AS Result
				RETURN	
			END
		END
		ELSE IF(@lConfigValue='PayTypeWise')
		BEGIN
			IF EXISTS(SELECT 1 from sFee.tReceipt where ReceiptNumber=@rReceiptNumber AND ReceiptBookId=@rReceiptBookId AND  PayTypeId=@rPayTypeId AND OrgGroupBatchId = @rOrgGroupBatchId)
			BEGIN
				SELECT -102 AS Result
				RETURN	
			END
		END
		-- Mantis - 0030695
			IF(@rChequeNo <> '' AND @lConfigValue1 = 'True')
			BEGIN
				IF EXISTS(SELECT 1 from sFee.tReceipt where ChequeNo=@rChequeNo AND Id != @rId)
				BEGIN
					SELECT -103 AS Result
					RETURN	
				END
			END
			-- Mantis - 0030695

		BEGIN
		UPDATE sFee.tReceipt SET
			ReceiptNumber = @rReceiptNumber,
			ReceiptDate=@rReceiptDate,
			PayTypeId=@rPayTypeId,
			ChequeDate=@rChequeDate,
		    ChequeNo=@rChequeNo,
		    BankName=@rBankName,
		    Remark=@rRemark
		WHERE Id=@rId
		
	SELECT @rId AS RESULT
	END 
	END
	
	-- For Mantis 0023321 By Foram on 14/03/2024 START
	DECLARE @lSourceId bigint, @lSourceTypeId1 smallint, @lBatchId int, @lOrgGroupBatchId int, @lReceiptBookId int, 
		    @lPayTypeId smallint, @lChequeNo nvarchar(40), @lBankName nvarchar(200), @lTotalReceiptAmount decimal(12,2)

	SELECT 
		@lSourceId = SourceId, 
		@lSourceTypeId1 = SourceTypeId, 
		@lBatchId = BatchId, 
		@lOrgGroupBatchId = OrgGroupBatchId, 
		@lReceiptBookId = ReceiptBookId, 
		@lPayTypeId = PayTypeId, 
		@lChequeNo = ChequeNo, 
		@lBankName = BankName, 
		@lTotalReceiptAmount = TotalReceiptAmount
	FROM sFee.tReceipt WHERE Id = @rId


	IF EXISTS(SELECT 1 FROM sFee.tPostDatePayment WHERE BatchId = @lBatchId AND OrgGroupBatchId = @lOrgGroupBatchId 
													AND SourceTypeId = @lSourceTypeId1 AND SourceId = @lSourceId AND ReceiptBookId = @lReceiptBookId 
													AND PayTypeId = @lPayTypeId AND ChequeNo = @lChequeNo AND BankName = @lBankName AND Amount = @lTotalReceiptAmount)
	BEGIN
		UPDATE sFee.tPostDatePayment 
		SET ReceiptId = @rId, StatusId = 752 
		WHERE BatchId = @lBatchId AND OrgGroupBatchId = @lOrgGroupBatchId 
			AND SourceTypeId = @lSourceTypeId1 AND SourceId = @lSourceId AND ReceiptBookId = @lReceiptBookId 
			AND PayTypeId = @lPayTypeId AND ChequeNo = @lChequeNo AND BankName = @lBankName AND Amount = @lTotalReceiptAmount
	END
	ELSE
	BEGIN
		UPDATE sFee.tPostDatePayment SET ReceiptId = 0, StatusId = CASE WHEN ISNULL(DepositedDate, '') <> '' THEN 753 ELSE 751 END WHERE ReceiptId = @rId
	END
	-- For Mantis 0023321 By Foram on 14/03/2024 END

	COMMIT TRANSACTION
		
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
		
END
GO
PRINT N'Altering Procedure [sFee].[pRptFeeReceiptDetailsGet]...';


GO

ALTER PROCEDURE [sFee].[pRptFeeReceiptDetailsGet]
						
			@rReportId int=0,
			@rReceiptId bigint = 0,
			@rOrgGroupId int = 0,
			@rReceiptBookId int = 0,
			@rFromReceiptNo int=0,
			@rToReceiptNo int=0,
			@rOrgId int = 0,
			@rBatchId int = 0,
			@rReceiptDate Date='',
			@rClassId int = 0,
			@rOrgIds nvarchar(max) = '',
			@rReceiptPrintGroupId nvarchar(100)='',
			@rDivisionId int = 0,
			@rAdmissionCategoryId int = 0
			

	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	
BEGIN 	
	
	DECLARE @lOrgGroupId int = 0
	DECLARE @lAmountInWord nvarchar(500) = ''
	DECLARE @lReceiptAmount Decimal(12,2) = 0.0
	DECLARE @lCurrencyCode nvarchar(20) = ''
	DECLARE @lCurrencyTitle nvarchar(100) = ''
	Declare @lIDConfigValue nvarchar(50)='',@lOrgGroup_Id int=0
	Select @lOrgGroup_Id = OrgGroupId From  sSetup.tOrganizationMaster Where Id = @rOrgId
	 
	SELECT @lIDConfigValue =ItemValue From sConfig.tConfigurationSetup
	WHERE ConfigurationId = 236 AND SourceId = @rOrgGroupId
	
	IF(@rOrgGroupId=0)
	BEGIN
		SELECT @lOrgGroupId = OrgGroupId ,@lReceiptAmount = R.ReceiptAmount  FROM sFee.tReceipt R 
		INNER JOIN sSetup.tOrgGroupBatchMaster OB ON R.OrgGroupBatchId = OB.Id 
		WHERE R.Id = @rReceiptId

		SELECT  @lAmountInWord = sFinanceCommon.fNumberToWordsWithDecimal(ISNULL(@lReceiptAmount,0.00),1,2,@lOrgGroupId)
		SELECT  @lCurrencyCode = [sFinanceCommon].[fDefaultCurrencyCodeGet](@lOrgGroupId) 
		SELECT  @lCurrencyTitle = [sFinanceCommon].[fDefaultCurrencyTitleGet](@lOrgGroupId) 
	END
	ELSE
	BEGIN
		SELECT  @lCurrencyCode = [sFinanceCommon].[fDefaultCurrencyCodeGet](@rOrgGroupId) 
	END

	IF(@rOrgGroupId=0) SET @rOrgGroupId=@lOrgGroupId

	SELECT 
			FRD.ReceiptId,
			FR.ReceiptNumber,
			FR.ReceiptDate,			
			SA.AcademicId,
			--SA.FirstName+' '+SA.MiddleName+' '+SA.LastName AS Name,
			SA.Name AS Name,
			SA.Code,
			SA.RollNo,
			SA.ClassId,
			SA.DivisionId,
			SA.ClassName,
			SA.DivisionName,
			SA.ClassName+' - '+SA.DivisionName AS ClassDivision,
			FM.FeeHeadId,
			FHM.Title AS FeeHead,
			FM.Id AS FeeId,
			FM.Title AS FeeName,
			FRD.Amount AS PaidAmount,
			PTM.Id AS PayTypeId,
			PTM.Title AS PayType,			
			--changes by Jash on 06/11/2020--%
			CASE WHEN ISNULL(FR.ChequeNo,'') != '' THEN FR.ChequeNo ELSE CONVERT(nvarchar(20),ISNULL(FR.PaymentTransactionId,''))  END AS ChequeNo,
			--CASE WHEN ISNULL(FR.ChequeNo,'') != '' THEN FR.ChequeNo
			--	WHEN ISNULL(PT.BankTransactionId,'') = '' THEN  CONVERT(nvarchar(20),FR.PaymentTransactionId)
			--	ELSE CONVERT(nvarchar(20),FR.PaymentTransactionId) +' - '+ CONVERT(nvarchar(20),PT.BankTransactionId) END AS ChequeNo,
			--%ISNULL(FR.ChequeNo,'') AS ChequeNo,
			ISNULL(FR.BankName,'') AS BankName,
			ISNULL(RBM.Address1,'') AS Address1,
			ISNULL(RBM.Address2,'') AS Address2,
			ISNULL(RBM.FullTitle,'') AS FullTitle,
			ISNULL(FR.TotalReceiptAmount,0.00) AS TotalReceiptAmount,
			ISNULL(FR.ReceiptAmount,0.00) AS ReceiptAmount,					
			(CASE WHEN @rOrgGroupId = 0 -- For Single Receipt
					THEN '' 
				  WHEN @rOrgGroupId != 0 -- For Bulk Receipt
					THEN sFinanceCommon.fNumberToWordsWithDecimal(ISNULL(FR.ReceiptAmount,0.00),1,2,@rOrgGroupId)
			END) AS ReceiptAmountInWords,			
			ISNULL(FR.DiscountAmount,0.00) AS DiscountAmount,
			FR.Remark,
			(CASE WHEN  ISNULL(FR.PaymentTransactionId,0) > 0 
			  THEN -- Navrachana
				'Online Generated'
				ELSE ISNULL(U.DisplayTitle,'') END) AS UserName,
			--ISNULL(U.DisplayTitle,'') AS UserName,
			OM.OrgLogo,
			RBM.Remark1,
			RBM.Remark2,			 
			ISNULL(RBM.IsOnPrePrinted,0) AS IsOnPrePrinted,
			ISNULL(RBM.IsSupressLogo,0) AS IsSupressLogo,
			ISNULL(RBM.IsDisplaySchoolName,0) AS IsDisplaySchoolName,
			RBM.ReportId,
			OM.Title AS OrganizationName,
			--(CASE  WHEN (RBM.ReportId = 20775 OR RBM.ReportId = 20777 OR RBM.ReportId = 20874 OR RBM.ReportId =20427 OR RBM.ReportId =20890 OR RBM.ReportId =20901 OR RBM.ReportId =20902) AND ISNULL(RBM.IsMonthDetailOnly,0) = 0 THEN 
			--	[sFee].[fFeeHeadMonthDetailGet](FR.ID,FHM.Id) -- Fee name in detail	 			  
			(CASE WHEN  RBM.ReportId = 20576
			      THEN -- HM patel report
				  [sFee].[fReceiptMonthDetailGet](FR.ID,0)
			      WHEN RBM.ReportId = 30457 
			      THEN
				  [sFee].[fReceiptMonthDetailGet](FR.ID,1)		
			      WHEN ISNULL(RBM.IsMonthDetailOnly,0) = 1  AND ISNULL(RBM.IsFeeInvoiceHeadwise,0) = 1
			      THEN				
				  [sFee].[fFeeHeadMonthNameGet](FR.ID,FHM.Id) -- Head wise Month name in detail
				  WHEN ISNULL(RBM.IsMonthDetailOnly,0) = 1  AND ISNULL(RBM.IsFeeInvoiceHeadwise,0) = 0
			      THEN				
				  [sFee].[fFeeMonthNameGet](FR.ID,FM.Id) -- Fee wise Month name in detail
			ELSE '' END) 
			AS MonthDetail,    			
			(CASE WHEN ISNULL(RBM.IsFeeInvoiceHeadwise,0) = 1
			      THEN				
					 Stuff((SELECT  N', ' +  FS1.FeeDuration FROM 
					sFee.tReceipt FR1				
					INNER JOIN sFee.tReceiptDetail FRD1	ON FR1.Id=FRD1.ReceiptId AND FR1.SourceTypeId=2001				
					INNER JOIN sFee.tFeeSetup FS1 ON FRD1.FeeSetupId = FS1.Id
					INNER JOIN sFee.tFeeMaster FM1 ON FS1.FeeId=FM1.Id						
					INNER JOIN sFee.tFeeHeadMaster FHM1 ON FHM1.Id=FM1.FeeHeadId 
					INNER JOIN sStudent.vStudentAcademic SA1 ON SA1.AcademicId = FR1.SourceId
					Where FM1.FeeHeadId = FM.FeeHeadId AND FS.ClassId = FS1.ClassId AND FR1.Id = @rReceiptId
					--Where FM1.Id = FM.Id AND FS.ClassId = FS1.ClassId AND FR1.Id = @rReceiptId
					AND (ISNULL(SA1.OrgId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rOrgIds,','))OR @rOrgIds='')
					Order By FS1.StartDate
					FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,N'' )    -- Fee Head wise Fee Duration
				  WHEN ISNULL(RBM.IsFeeInvoiceHeadwise,0) = 0
			      THEN
					FS.FeeDuration -- Fee wise Fee Duration
				 ELSE '' END) AS FeeDuration,			
			--Stuff((SELECT  N', ' +  FS1.FeeDuration FROM 
			--sFee.tReceipt FR1				
			--INNER JOIN sFee.tReceiptDetail FRD1	ON FR1.Id=FRD1.ReceiptId AND FR1.SourceTypeId=2001				
			--INNER JOIN sFee.tFeeSetup FS1 ON FRD1.FeeSetupId = FS1.Id
			--INNER JOIN sFee.tFeeMaster FM1 ON FS1.FeeId=FM1.Id						
			--INNER JOIN sFee.tFeeHeadMaster FHM1 ON FHM1.Id=FM1.FeeHeadId 
			--INNER JOIN sStudent.vStudentAcademic SA1 ON SA1.AcademicId = FR1.SourceId
			----Where FM1.FeeHeadId = FM.FeeHeadId AND FS.ClassId = FS1.ClassId AND FR1.Id = @rReceiptId
			--Where FM1.Id = FM.Id AND FS.ClassId = FS1.ClassId AND FR1.Id = @rReceiptId
			--AND (ISNULL(SA1.OrgId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rOrgIds,','))OR @rOrgIds='')
			-- Order By FS.StartDate
			--FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,N'' ) AS FeeDuration,			
			DM.Title AS DepartmentName,
			FM.OrderIndex AS FeeOrderIndex,
			FHM.OrderIndex AS FeeHeadOrderIndex,
			FR.IsObsolete AS IsCancelReceipt,
			@lCurrencyCode AS CurrencyCode,
			@lCurrencyTitle AS CurrencyTitle,
			BM.Title AS Batch,
			FR.ChequeDate AS ChequeDate,
			ISNULL(RBM.IsMonthDetailOnly,0) AS IsMonthDetailOnly,
	        ISNULL(RBM.IsFeeInvoiceHeadwise,0) AS IsFeeInvoiceHeadwise,
	        ISNULL(RBM.GroupTitle,'') AS GroupTitle,
	        ISNULL(RBM.IsSuppressFeeDetail,0) AS IsSuppressFeeDetail,
	        ISNULL(CGM.Title,'') AS CollectionGroupName,
	        ISNULL(FI.InvoiceNumber,0) AS InvoiceNumber,
	        SA.SUID,
	        CASE
				 WHEN RBM.IsOnPrePrinted=1       
				 THEN 2
				 ELSE  ISNULL(RBM.HeaderDisplayId,1)
				END AS HeaderDisplayId,
	        RBM.ReceiptBookHeader,
	        RBM.ServiceTaxPercentage,
	        ISNULL(FR.AmountAfterDiscount,0) AS AmountAfterDiscount,
	        (CASE WHEN  FR.CGSTPercentage IS NULL THEN RBM.CGSTPercentage ELSE FR.CGSTPercentage END) AS CGSTPercentage,
	        (CASE WHEN  FR.SGSTPercentage IS NULL THEN RBM.SGSTPercentage ELSE FR.SGSTPercentage END) AS SGSTPercentage,
	        ISNULL(FR.CGSTAmount,0) AS CGSTAmount,
	        ISNULL(FR.SGSTAmount,0) AS SGSTAmount,
			ISNULL(OOM.GSTINNo,'') AS GSTINNo,
	        ISNULL(OOM.HSNSACCode,'') AS HSNSACCode,
	        @lIDConfigValue AS ItemValue ,
	        SF.FatherName,
	        SF.MotherName,
	        ISNULL(FA.DiscountAmount,0) AS ExemptionAmount,
			ISNULL(FA.Remark,'') AS ExemptionRemark,
			EI.EmployeeSign,
			ISNULL(SC.PresentCity,'') AS PresentCity,
			(ISNULL((CASE WHEN SC.PresentStreetOne IS NOT NULL AND SC.PresentStreetOne!='' THEN ''+ SC.PresentStreetOne END),'')+
			ISNULL((CASE WHEN SC.PresentStreetTwo IS NOT NULL AND SC.PresentStreetTwo!=''  THEN ', '+ SC.PresentStreetTwo END),'')+
			ISNULL((CASE WHEN SC.PresentStreetThree IS NOT NULL AND SC.PresentStreetThree!=''  THEN ', '+ SC.PresentStreetThree END),'')+
			ISNULL((CASE WHEN SC.PresentCity IS NOT NULL AND SC.PresentCity!=''  THEN ', ' + SC.PresentCity  END),'')+
			ISNULL((CASE WHEN SC.PresentState IS NOT NULL AND SC.PresentState!=''  THEN  ', '+ SC.PresentState END),'')+
			ISNULL((CASE WHEN SC.PresentZipCode IS NOT NULL AND SC.PresentZipCode!=''  THEN  ', '+ SC.PresentZipCode  END),''))
			 AS PresentAdress,
			--ISNULL(SC.PresentStreetOne +','+ SC.PresentStreetTwo +','+ SC.PresentStreetThree +','+ SC.PresentCity +','+ SC.PresentState +'-'+ SC.PresentZipCode ,'') AS PresentAdress,
			SC.ParentContactNo,			
			ISNULL(RBM.IsDisplayPendingFeeAmount,0) AS IsDisplayPendingFeeAmount,
			CASE WHEN @rOrgGroupId = 1134 OR @rOrgGroupId = 1742 THEN
			(CASE WHEN  ISNULL(RBM.IsDisplayPendingFeeAmount,0) = 0 THEN 0 ELSE ISNULL([sFee].[fPendingFeeAmountReceiptBookwiseGet](FR.SourceId,RBM.Id),0) END)
			ELSE 
			(CASE WHEN  ISNULL(RBM.IsDisplayPendingFeeAmount,0) = 0 THEN 0 ELSE ISNULL([sFee].[fPendingFeeAmountReceiptBookwiseGet](FR.SourceId,0),0) END) END AS PendingFeeAmount,
			SA.FirstName,
			SA.LastName,
			ISNULL([sFee].[fFeeAmountSumGet](FR.SourceId),0) AS FeeAmount,
			ISNULL([sFee].[fFeeReceiptDiscountSumGet](FR.SourceId),0) AS ExemptionAmountSum,
			[sFee].[fTransportBusRouteGet](SA.StudentId,FR.OrgGroupBatchId) AS TransportBusRoute,
			ISNULL(RBM.BankAccountNumber,'') AS BankAccountNumber,
			ISNULL(FHM.Number,0) AS FeeHeadNumber,
			ISNULL(FM.Number,0) AS FeeNumber,
			ISNULL(RBM.DigitalSignature,0x) AS DigitalSignature,
			ISNULL(FR.CancelStatusRemark,'') AS CancelStatusRemark,
			FS.Amount AS FeeSetupAmount,
			FR.CreatedDateTime ,
			ISNULL(FR.PaymentTransactionId,0) As PaymentTransactionId,
			SA.MiddleName,
			CONVERT(char(5),FR.CreatedDateTime,108) AS CreatedTime,
			--by megha - 0026430

			(CASE WHEN ISNULL(FA.VariableAmount,0) > 0 Then FA.VariableAmount 
			      When ISNULL(FS.Amount,0) > 0 Then FS.Amount END) + ISNULL(FA.AdditionalAmount,0) - ISNULL(FA.DiscountAmount,0) - ISNULL(FA.NotApplicableAmount,0) As TotalPayableAmount,
			OGM.Title AS OrgGroupTitle,
			 NID.CollectionGroupName AS NextInstallmentName, --Mantis End- 0031305
			ISNULL(HM.Title,'') AS House,	--add for mantis: 32658
			NID.StartDate AS NextInstallmentStartDate, --Mantis -  Start 32760
			NID.EndDate AS NextInstallmentEndDate,
			NID.PendingAmount AS NextInstallmentAmount,   --Mantis -  End 32760			
			ISNULL([sFee].[fFeeActualAmountSumGet](FR.SourceId),0) AS FeeActualAmount,	-- MantisId : 0032658
			ISNULL(SC.StudentContactNo,'') AS StudentContactNo, -- MantisId : 0032895
			--mantis Start - 0033973
			ISNULL(SC.PermanentStreetOne,'')AS PermanentStreetOne,
			ISNULL(SC.PermanentStreetTwo,'')AS PermanentStreetTwo,
			ISNULL(SC.PermanentStreetThree,'')AS PermanentStreetThree,
			--mantis End - 0033973
			--mantis Start - 0034206
			ISNull(SC.PermanentCountry,'')AS PermanentCountry,
			ISNull(SC.PermanentState,'')AS PermanentState,
			ISNull(SC.PermanentCity,'')AS PermanentCity,
			ISNull(SC.PermanentZipCode,'')AS PermanentZipCode,
			--mantis End - 0034206
			FS.StartDate,          --mantis : 0034274
			FS.EndDate          --mantis : 0034274

 	FROM 
	
			sFee.tReceipt FR				
			INNER JOIN sFee.tReceiptDetail FRD ON FR.Id=FRD.ReceiptId AND FR.SourceTypeId=2001				
			INNER JOIN sFee.tFeeSetup FS ON FRD.FeeSetupId = FS.Id				
			INNER JOIN sFee.tFeeMaster FM ON FS.FeeId=FM.Id						
			INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id=FM.FeeHeadId
			INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId = FR.SourceId
			INNER JOIN sStudent.tStudentCommunication SC ON SC.StudentId = SA.StudentId
			INNER JOIn sFinanceCommon.tPayTypeMaster PTM ON PTM.Id=FR.PayTypeId
			LEFT JOIN sFee.tFeeAllocation FA ON FA.FeeSetupId=FS.Id AND FA.SourceId=SA.AcademicId
			INNER JOIN sFee.tReceiptBookMaster RBM  ON RBM.Id=FR.ReceiptBookId
			LEFT JOIN sSecurity.tUser U ON U.Id=FR.CreatedUserId
			INNER JOIN sSetup.tDepartmentMaster DM ON SA.DepartmentId =DM.Id
			LEFT JOIN sSetup.tOrganizationMaster OM ON OM.Id=DM.OrgId
			LEFT JOIN sSetup.tOrganizationOtherDetail OOM ON OM.Id=OOM.OrgId	
			INNER JOIN sSetup.tBatchMaster BM ON BM.Id = FR.BatchId 
			LEFT JOIN sFee.tCollectionGroupMaster CGM ON CGM.Id=FR.CollectionGroupId 
			LEFT JOIN sFee.tFeeInvoice FI ON FI.Id=FR.FeeInvoiceId
			LEFT JOIN sStudent.tStudentFamily SF ON SF.StudentId = SA.StudentId
			LEFT JOIN sHrm.tEmployeeImages EI ON EI.EmployeeId = U.SourceId AND U.SourceTypeId=95
			LEFT JOIN sOp.tPaymentTransaction PT ON PT.Id = FR.PaymentTransactionId
			INNER JOIN sSetup.tOrgGroupMaster OGM ON OGM.Id = OM.OrgGroupId  -- Mantis : 0029314
			LEFT JOIN (SELECT * FROM [sFee].[fNextInstallmentDetailsGet](@rReceiptBookId, @rReceiptId)) NID -- Mantis Start - 0031305
			ON NID.SourceId = FR.SourceId AND NID.SourceTypeId = FR.SourcetypeId -- Mantis End - 0031305
			LEFT JOIN sStudent.tStudentOther AS SO ON SO.StudentId = SA.StudentId
			LEFT JOIN sActivity.tHouseMaster HM ON HM.Id=SO.HouseId		--add for mantis: 32658
	WHERE 
			((FR.Id=@rReceiptId) OR (@rReceiptId = 0))
			AND ((RBM.OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId = 0))
			AND ((FR.ReceiptBookId = @rReceiptBookId) OR (@rReceiptBookId = 0))
			AND ((FR.ReceiptNumber >= @rFromReceiptNo) OR (@rFromReceiptNo = 0))
			AND ((FR.ReceiptNumber <= @rToReceiptNo) OR (@rToReceiptNo = 0))
			AND ((FR.BatchId = @rBatchId) OR (@rBatchId = 0))
			AND ((BM.OrgId = @rOrgId) OR (@rOrgId = 0))
			AND ((FR.ReceiptDate = @rReceiptDate) OR (ISNULL(@rReceiptDate,'') = ''))
			AND ((SA.ClassId = @rClassId) OR (@rClassId = 0))
			AND ((SA.DivisionId = @rDivisionId) OR (@rDivisionId = 0))
			AND (ISNULL(SA.OrgId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rOrgIds,','))OR @rOrgIds='')			
			AND ((ISNULL(FR.ReceiptPrintGroupId,'')=@rReceiptPrintGroupId) OR (ISNULL(@rReceiptPrintGroupId,'') = ''))
			AND ((FS.AdmissionCategoryId = @rAdmissionCategoryId) OR (@rAdmissionCategoryId = 0) )
    ORDER BY 
            FS.StartDate,FHM.OrderIndex,FM.OrderIndex ASC 
END

--BEGIN
--SELECT
-- CAST(0 AS bigint) AS ReceiptId,
-- 0	AS ReceiptNumber,
-- CAST('' AS date) AS ReceiptDate,
-- CAST(0 AS bigint) AS AcademicId,
-- ''	AS Name,
--''	AS Code,
-- 0	AS RollNo,
--0	AS ClassId,
--0	AS DivisionId,
--''	AS ClassName,
--''	AS DivisionName,
--''	AS ClassDivision,
--0	AS FeeHeadId,
--''	AS FeeHead,
--0	AS FeeId,
--''	AS FeeName,
--CAST(0.0 AS decimal(12,2)) AS PaidAmount,
--CAST(0 AS tinyint) AS PayTypeId,
--''	AS PayType,
--''	AS ChequeNo,	
--''	AS BankName,
--''	AS Address1,
--''	AS Address2,
--''	AS FullTitle,
--CAST(0.0 AS decimal(12,2)) AS TotalReceiptAmount,
--CAST(0.0 AS decimal(12,2)) AS ReceiptAmount,					
--''	AS ReceiptAmountInWords,		
--CAST(0.0 AS decimal(12,2)) AS DiscountAmount,
--''	AS Remark,
--''	AS UserName,		
--0x  AS OrgLogo,
--''	AS Remark1,
--''	AS Remark2,			 
--CAST(0 AS bit) AS IsOnPrePrinted,
--CAST(0 AS bit) AS IsSupressLogo,
--CAST(0 AS bit) AS IsDisplaySchoolName,
--0	AS ReportId,
--''	AS OrganizationName,		  
--''	AS MonthDetail,    			
--''	AS FeeDuration,		
--''	AS DepartmentName,
-- 0	AS FeeOrderIndex,
--CAST(0 AS smallint)	AS FeeHeadOrderIndex,
--CAST(0 AS bit) AS IsCancelReceipt,
--''	AS CurrencyCode,
--''	AS CurrencyTitle,
--''	AS Batch,
--CAST('' AS date) AS ChequeDate,
--CAST(0 AS bit) AS IsMonthDetailOnly,
--CAST(0 AS bit) AS IsFeeInvoiceHeadwise,
--''	AS GroupTitle,
--CAST(0 AS bit) AS IsSuppressFeeDetail,
--''	AS CollectionGroupName,
--0	AS InvoiceNumber,
--''	AS SUID,
--CAST(0 AS tinyint) AS HeaderDisplayId,
--0x  AS ReceiptBookHeader,
--CAST(0.0 AS decimal(6,2)) AS ServiceTaxPercentage,
--CAST(0.0 AS decimal(12,2)) AS AmountAfterDiscount,
--CAST(0.0 AS decimal(5,2)) AS CGSTPercentage,
--CAST(0.0 AS decimal(5,2)) AS SGSTPercentage,
--CAST(0.0 AS decimal(12,2)) AS CGSTAmount,
--CAST(0.0 AS decimal(12,2))	AS SGSTAmount,
--''	AS GSTINNo,
--''	AS HSNSACCode,
--''	AS ItemValue ,
--''	AS FatherName,
--''	AS MotherName,
--CAST(0.0 AS decimal(12,2)) AS ExemptionAmount,
--''	AS ExemptionRemark,
--0x AS EmployeeSign,
--''	AS PresentCity,
--''	AS PresentAdress,
--''	AS ParentContactNo,	
--CAST(0 AS bit) AS IsDisplayPendingFeeAmount,
--CAST(0.0 AS decimal(12,0)) AS PendingFeeAmount,
--''	AS FirstName,
--''	AS LastName,
--CAST(0.0 AS decimal(12,0))	AS FeeAmount,
--CAST(0.0 AS decimal(12,0)) AS ExemptionAmountSum,
--''	AS TransportBusRoute,
--''	AS BankAccountNumber,
--0	AS FeeHeadNumber,
--0	AS FeeNumber,
--0x AS DigitalSignature,
--''	AS CancelStatusRemark,
--CAST(0.0 AS decimal(12,2))	AS FeeSetupAmount,
--''	AS CreatedDateTime ,
--CAST(0 AS bigint)	AS PaymentTransactionId,
--''	AS MiddleName,
--'' AS CreatedTime,
-- 0 AS TotalPayableAmount,
-- '' AS OrgGroupTitle,
-- '' AS NextInstallmentName,
-- '' AS House,
-- CAST('' AS date) AS NextInstallmentStartDate,
-- CAST('' AS date) NextInstallmentEndDate,
-- CAST(0.0 AS decimal(12,2)) AS NextInstallmentAmount,
-- CAST(0.0 AS decimal(12,2)) AS FeeActualAmount,
-- '' AS StudentContactNo,
-- '' AS PermanentStreetOne,
-- '' AS PermanentStreetTwo,
-- '' AS PermanentStreetThree,
--'' AS PermanentCountry,
-- '' AS PermanentState,
-- '' AS PermanentCity,
-- '' AS PermanentZipCode,
-- CAST('' AS date) AS StartDate,
-- CAST('' AS date) AS EndDate  

--END
GO
PRINT N'Altering Procedure [sPayroll].[pCopyPaySetupAdd]...';


GO

ALTER PROCEDURE [sPayroll].[pCopyPaySetupAdd]
	  
	 @rSourceTypeId smallint,
	 @rSourceId bigint,
	 @rOrgGroupId int,
     @rCopyGradeId bigint,
     @rPayrollId int,
     @rPayrollYearId int=0
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN 
       DELETE PSD FROM sPayroll.tPaySetup PS INNER JOIN  sPayroll.tPaySetupDependent PSD ON PSD.PaySetupId = PS.ID 
	   WHERE SourceId = @rCopyGradeId AND SourceTypeId = @rSourceTypeId AND PayrollId = @rPayrollId  AND PayrollYearId = @rPayrollYearId
		
	   DELETE FROM sPayroll.tPaySetup WHERE SourceId = @rCopyGradeId AND SourceTypeId = @rSourceTypeId AND PayrollId = @rPayrollId AND PayrollYearId = @rPayrollYearId
		
		DECLARE @1Count int
		DECLARE @1PaySetupId int
		DECLARE @lNextNumber BIGINT
		
		CREATE TABLE #TempTable
		(
		    ID INT IDENTITY(1,1),
		    PaySetupId INT,
		    PayElementId INT,
		    AMOUNT DECIMAL(22,2),
		    Percentage DECIMAL(22,2),
		    ProcessOrder int,
		    DefaultAmount decimal(22,2),
			Formula NVARCHAR(200) --Added by Nilesh for mantis : 34316
		 ) 
       		
       	INSERT INTO #TempTable 
        SELECT  ID, 
         PayElementId
		,[Amount]
		,[Percentage]
        ,[ProcessOrder]  
        ,[DefaultAmount]
	    ,Formula --Added by Nilesh for mantis : 34316
	   FROM [sPayroll].[tPaySetup] PS   
			  
	   WHERE PayrollId = @rPayrollId  AND PayrollYearId = @rPayrollYearId  AND  PS.SourceTypeId = @rSourceTypeId AND PS.SourceId = @rSourceId
	   
	   SELECT @1Count = COUNT(Id) FROM #TempTable
	   
	   WHILE @1Count > 0	     
	     BEGIN 
	        SET @1PaySetupId = 0
	        EXECUTE sCommon.pTableRowIdGet @rOrgGroupId, 'sPayroll.tPaySetup', 0, 1, 1,@outNextNumber = @lNextNumber output
	        
	        INSERT INTO sPayroll.tPaySetup   
	        SELECT @lNextNumber,
				   @rSourceTypeId,      
                   @rCopyGradeId,
	               PayElementId
				   ,[Amount]
				   ,[Percentage]
				   ,[ProcessOrder]     
	               ,@rPayrollId
	               ,NULL
	               ,@rPayrollYearId
	               ,[DefaultAmount]
				   ,Formula --Added by Nilesh for mantis : 34316
	                
	        FROM #TempTable Where Id = @1Count				    
	        
	        SELECT @1PaySetupId = PaySetupId From #TempTable Where Id = @1Count
	        
	        IF EXISTS(SELECT 1 FROM sPayroll.tPaySetupDependent Where PaySetupId = @1PaysetupId)
	          BEGIN  
	          
	                --Select @1PaysetupId
	                INSERT INTO sPayroll.tPaySetupDependent
	                SELECT DependentPayElementId,@lNextNumber,IsDeduct FROM sPayroll.tPaySetupDependent Where PaySetupId = @1PaysetupId	               
	          END       
	          
	        SET @1Count = @1Count - 1
	     END
	     
	     DROP TABLE #TempTable
	     
	     SELECT @lNextNumber AS Result
       
END
GO
PRINT N'Altering Procedure [sStudent].[pOnlineRegistrationStudentAdd]...';


GO


ALTER PROCEDURE [sStudent].[pOnlineRegistrationStudentAdd]
	 
	@rId bigint,
	@rCode nvarchar(20),
	@rRegistrationNumber nvarchar(20),
	--@rNumber bigint,
	@rFirstName nvarchar(100),
	@rMiddleName nvarchar(100),
	@rLastName nvarchar(100),
	@rOrgId int,
	@rAdmissionCategoryId int,
	@rStatusId smallint,
	@rRecordStatusId smallint,
	@rCreatedUserId bigint,
	@rUpdatedUserId bigint,
	@rOrgGroupBatchId int =0,
	@OutStudentId BIGINT = NULL OUTPUT,
	--@rSUId nvarchar(20) = '',
	@rOrgGroupId int = 0,
	--@rMobileNo nvarchar(50)='',
	@rStudentGroupId int=0,
	@rStudentOtherGroupId int=0,
	@rDateOfBirth Date=NULL,
    @rGenderId int=0,
    @rAcademicId bigint = 0,
	@rClassId int=0,
	@rDivisionId int=0,
	@rRollNo int=0,
	@rBatchId int=0,
	@rStudentStatusId int,
	@rStudentPhoto varbinary(max)=0,
	--Other
	@rUIDNumber nvarchar(50),
	@rBirthdateInWord nvarchar(150),
	@rTransportModeId int,
	@rBloodGroupId tinyint,
	@rBirthPlace nvarchar(150),
    @rBirthTaluka nvarchar(150),
    @rBirthDistrict nvarchar(150),
	@rFamilyCast nvarchar(100),
    @rFamilySubCast nvarchar(100),
    @rCastCategoryId int,
	@rReligionId int,
	@rHouseId int,
    @rMotherTongueId int,
	@rNationalityId int,
	@rHobby nvarchar(50),
	@rReferenceRemark nvarchar(100) = '',
	@rAdhaarNumber nvarchar(20) = '',
	@rMotherTongue nvarchar(100) = '',
	@rQuotaId int =0,
    @rbirthState nvarchar(100)='',
    @rBirthCountry nvarchar(100)='',
	@rBankAccountNumber nvarchar(20),
	@rBankName nvarchar(100),
	@rBankBranch nvarchar(100),
	@rBankIFSCCode nvarchar(20),
	@rClass9BoardRegistrationNumber nvarchar(20),
	@rClass11BoardRegistrationNumber nvarchar(20),
	@rPassportNumber nvarchar(20),
	@rPassportType nvarchar(20),
	@rPassportCountryCode nvarchar(100),
	@rPassportIssueDate date,
	@rPassportExpiryDate date,
	@rBankAccountHolderName nvarchar(200) = '',

	--Family
	@rFatherName	nvarchar(100),
	@rFatherDateOfBirth	date,
	@rFatherOccupation	nvarchar(50),
	@rFatherQualification	nvarchar(50),
	@rFatherContactNo	nvarchar(50),
	@rFatherMail	nvarchar(50),
	@rFatherOfficeAddress	nvarchar(500),
	@rFatherIncome	int,
	@rMotherName	nvarchar(100),
	@rMotherDateOfBirth	date,
	@rMotherOccupation	nvarchar(50),
	@rMotherQualification	nvarchar(50),
	@rMotherContactNo	nvarchar(50),
	@rMotherMail	nvarchar(50),
	@rMotherOfficeAddress	nvarchar(500),
	@rMotherIncome	int,
	@rRelationshipName	nvarchar(100),
	@rRelativeName	nvarchar(100),
	@rRelativeDateOfBirth	date,
	@rRelativeOccupation	nvarchar(50),
	@rRelativeQualification	nvarchar(50),
	@rRelativeContactNo	nvarchar(50),
	@rRelativeMail	nvarchar(50),
	@rRelativeOfficeAddress	nvarchar(500),
	@rRelativeIncome	int,
	@rFatherPhoto varbinary(max)=0,
	@rMotherPhoto varbinary(max)=0,
	@rRelativePhoto varbinary(max)=0,
	@rLiveWithId tinyint=0,
	@rIsFatherExStudent bit=0,
	@rFatherPassOutYear nvarchar(20)='',
	@rIsMotherExStudent bit=0,
	@rMotherPassOutYear nvarchar(20)='',
	@rIsRelativeExStudent bit=0,
	@rRelativePassOutYear nvarchar(20)='',
	@rIsSingleChild bit = 0,
	@rFatherOrganization nvarchar(200),
	@rFatherDesignation nvarchar(100),
	@rMotherOrganization nvarchar(200),
	@rMotherDesignation nvarchar(100),
	@rRelativeOrganization nvarchar(200),
	@rRelativeDesignation nvarchar(100),
	@rFatherAadharNo nvarchar(20)= '',
	@rMotherAadharNo nvarchar(20)= '',
	@rFatherOfficeContactNo nvarchar(50) = '',
	@rMotherOfficeContactNo nvarchar(50) = '',
	--Change for mantis 29443
	@rMarriageAnniversaryDate date, 

	--Communication
	@rPresentStreetOne nvarchar(150),
	@rPresentStreetTwo nvarchar(150),
	@rPresentStreetThree nvarchar(150),
	@rPresentCountry nvarchar(50),
	@rPresentState nvarchar(50),
	@rPresentCity nvarchar(50),
	@rPresentZipCode nvarchar(20),
	@rPresentContactNo nvarchar(20),
	@rPermanentStreetOne nvarchar(150),
	@rPermanentStreetTwo nvarchar(150),
	@rPermanentStreetThree nvarchar(150),
	@rPermanentCountry nvarchar(50),
	@rPermanentState nvarchar(50),
	@rPermanentCity nvarchar(50),
	@rPermanentZipCode nvarchar(20),
	@rPermanentContactNo nvarchar(20),
	@rEmergencyStreetOne nvarchar(150),
	@rEmergencyStreetTwo nvarchar(150),
	@rEmergencyStreetThree nvarchar(150),
	@rEmergencyCountry nvarchar(50),
	@rEmergencyState nvarchar(50),
	@rEmergencyCity nvarchar(50),
	@rEmergencyZipCode nvarchar(20),
	@rEmergencyContactNo nvarchar(20),
	@rParentContactNo nvarchar(20),
	@rParentEmail nvarchar(50),
	@rStudentContactNo nvarchar(50),
	@rStudentEmail nvarchar(50),
	@rPresentDistanceFromSchool decimal(10,2),
	@rStudentCustomAlert nvarchar(1000) = '',
	@rWhatsAppContactNo nvarchar(20) = '',

	--Admission
	@rPreviousSchool nvarchar(200) ,
	@rPreviousSchoolTypeId int,
	@rPreviousStudentId bigint ,
	@rAdmissionFormId bigint ,
	@rAdmissionFormNumber nvarchar(20) ,
	@rAdmissionStandardId int ,
	@rAdmissionDate date ,
	@rAdmissionYear nvarchar(10) ,
	@rLeftStandardId int ,
	@rLeftDate date ,
	@rLeftYear nvarchar(10) ,
	@rInactiveDate date ,
	@rInActiveReason nvarchar(200) ,
	@rLeftReason nvarchar(200) ,
	@rLCProgress nvarchar(200) ,
	@rLCConduct nvarchar(200) ,
	@rLCBook nvarchar(20) ,
	@rLCNumber nvarchar(20) ,
	@rLCDate date ,
	@rLCRemark nvarchar(200) ,
	@rLCCopy tinyint ,
	@rLcId bigint ,
	@rExamSeatNo nvarchar(20) ,
	@rAdmissionNumber nvarchar(50) ,
	@rIsTransfer bit ,
	@rTransferFrom nvarchar(150) ,
	@rPassingYear nvarchar(20) ,
	@rIsTFWS bit ,
	@rIsCertificateHolder bit ,
	@rCertificateName nvarchar(100),
	@rAdmissionQuotaId int ,
	@rPreviousStandard nvarchar (50),
	@rPreviousLCNumber nvarchar (50) ,
	@rPreviousLCDate date,
	@rPreviousLCResult nvarchar (50) ,
	@rProofOfDOB nvarchar (200),

	--CollegeDetail
	--@rAdmissionNumber nvarchar(50),
    --@rIsTransfer bit,
    --@rTransferFrom nvarchar(150),
    --@rPassingYear nvarchar(20),
    --@rIsTFWS bit,
    --@rIsCertificateHolder bit,
    --@rCertificateName nvarchar(100),
    @rSSCBoard nvarchar(200),
    @rSSCSchool nvarchar(200),
    @rSSCPassingYear nvarchar(10),
    @rSSCObtainMarks decimal(10,2),
    @rSSCOutOfMarks decimal(10,2),
    @rSSCPercentage decimal(10,2),
    @rSSCMathMarks decimal(10,2),
    @rSSCMathOutOfMarks decimal(10,2),
    @rHSCBoard nvarchar(200),
    @rHSCSchool nvarchar(200),
    @rHSCPassingYear nvarchar(10),
    @rHSCObtainMarks decimal(10,2),
    @rHSCOutOfMarks decimal(10,2),
    @rHSCPercentage decimal(10,2),
    @rHSCPhysicsMarks decimal(10,2),
    @rHSCPhysicsOutOfMarks decimal(10,2),
    @rHSCChemistryMarks decimal(10,2),
    @rHSCChemistryOutOfMarks decimal(10,2),
    @rHSCMathMarks decimal(10,2),
    @rHSCMathOutOfMarks decimal(10,2),
    @rHSCBiologyMarks decimal(10,2),
    @rHSCBiologyOutOfMarks decimal(10,2),
    @rPCMMarks decimal(10,2),
    @rPCBMarks decimal(10,2),
    @rJEERollNo nvarchar(20),
    @rJEEMeritNo nvarchar(20),
    @rJEEMeritMarks decimal(10,2),
    @rJEEOutOfMarks decimal(10,2),
    @rDiplomaBoard nvarchar(200),
    @rDiplomaCollege nvarchar(200),
    @rDiplomaPassingYear nvarchar(10),
    @rDiplomaObtainMarks decimal(10,2),
    @rDiplomaOutOfMarks decimal(10,2),
    @rDiplomaPercentage decimal(10,2),
    @rApplicationNo nvarchar(20),
    @rAdmissionRound nvarchar(20),
    @rMaritalStatusId tinyint,
    @rSSCPercentile decimal(10,2),
    @rSSCGrade nvarchar(20),

	--Added by dhananjay for mantis start- 33982
	--HealthDetails
	@rIdentificationMark1 nvarchar(100)='',
	@rIdentificationMark2 nvarchar(100)='',
	@rIsPysicalDeformity bit = 0,
	@rPysicalDeformityRemark nvarchar(200)='',
	@rIsMajorIllness bit = 0,
	@rMajorIllnessRemark nvarchar(200)='',
	@rIsAllergy bit = 0,
	@rAllergyRemark nvarchar(200)='',
	@rIsPastHistory bit = 0,
	@rPastHistoryRemark nvarchar(200)='',
	@rFamilyDoctorName nvarchar(150)='',
	@rFamilyDoctorContactNo nvarchar(100)='',
	@rPhysicalDisabilityCategoryId int= 0
	--Added by dhananjay for mantis End  - 33982
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	

	Declare @rNumber bigint
	Select @rNumber = ISNULL(Max(Id),0) + 1 from sStudent.tOnlineRegistrationStudentMaster Where OrgId = @rOrgId

	IF(@rId=0)
	BEGIN		 
	 
					
			IF EXISTS (SELECT Id from sStudent.tOnlineRegistrationStudentMaster WHERE Code=@rCode AND OrgId=@rOrgId)
			BEGIN
				SELECT -100 AS RESULT, '' AS Code, 0 AS Id 
				RETURN
			END 
		
			IF(ISNULL(@rUIDNumber,'')!='')		
			  BEGIN
				IF EXISTS(SELECT 1 FROM sStudent.tStudentOther SO INNER JOIN sStudent.tStudentMaster SM ON SM.Id = SO.StudentId WHERE SM.OrgId = LEFT(@rBatchId,4) AND UIDNumber=@rUIDNumber)
				BEGIN
					SELECT -101 AS Result
					RETURN
				END 
			  END
		 
			IF(ISNULL(@rAdhaarNumber,'')!='')		 		 
			  BEGIN
				IF EXISTS(SELECT 1 FROM sStudent.tStudentOther SO INNER JOIN sStudent.tStudentMaster SM ON SM.Id = SO.StudentId WHERE SM.OrgId = LEFT(@rBatchId,4) AND  AdhaarNumber=@rAdhaarNumber)
					BEGIN
						SELECT -102 AS Result
						RETURN
					END 
			  END
		--END
	END
	ELSE
	BEGIN
	
		IF EXISTS (SELECT Id from sStudent.tOnlineRegistrationStudentMaster WHERE Code=@rCode AND OrgId=@rOrgId AND Id<>@rId)
		BEGIN
			SELECT -100 AS RESULT, '' AS Code, 0 AS Id 
			RETURN
		END 
	
		IF(ISNULL(@rUIDNumber,'')!='')		
		  BEGIN
			IF EXISTS(SELECT 1 FROM sStudent.tStudentOther SO INNER JOIN sStudent.tStudentMaster SM ON SM.Id = SO.StudentId WHERE SM.OrgId = LEFT(@rBatchId,4) AND UIDNumber=@rUIDNumber AND StudentId <> @rId)
			BEGIN
				SELECT -101 AS Result
				RETURN
			END 
		  END
		  
		 IF(ISNULL(@rAdhaarNumber,'')!='')	
		  BEGIN
			IF EXISTS(SELECT 1 FROM sStudent.tStudentOther SO INNER JOIN sStudent.tStudentMaster SM ON SM.Id = SO.StudentId WHERE SM.OrgId = LEFT(@rBatchId,4) AND AdhaarNumber=@rAdhaarNumber AND StudentId <> @rId)
				BEGIN
					SELECT -102 AS Result
					RETURN
				END 
		  END
	END

	BEGIN TRY
	
	 BEGIN TRANSACTION	
	IF(@rId=0)
	BEGIN	
	DECLARE @lNextNumber BIGINT     
	EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sStudent.tOnlineRegistrationStudentMaster', 0, 1, 1,@outNextNumber = @lNextNumber output
	--PRINT(@lNextNumber)
	IF(@rNumber=0)
	BEGIN
	SET @rNumber=@lNextNumber
	END
	INSERT INTO sStudent.tOnlineRegistrationStudentMaster
		(
			Id,
			Code,
			Number,
			RegistrationNumber,
			FirstName,
			MiddleName,
			LastName,
			OrgId,
			--DepartmentId,
			AdmissionCategoryId,
			StatusId,
			RecordStatusId,
			CreatedUserId,
			CreatedDateTime,
			ClassId,
			DivisionId,
			RollNo,
			BatchId,
			StudentStatusId,
			StudentPhoto
		)
	VALUES
		(
			@lNextNumber,
			@rCode,
			@rNumber,
			@rRegistrationNumber,
			@rFirstName,
			@rMiddleName,
			@rLastName,
			@rOrgId,
			--@rDepartmentId,
			@rAdmissionCategoryId,
			@rStatusId,
			@rRecordStatusId,
			@rCreatedUserId,
			sCommon.fGetDate(),
			@rClassId,
			@rDivisionId,
			@rRollNo,
			@rBatchId,
			@rStudentStatusId,
			@rStudentPhoto
			
		)
		
	INSERT INTO sStudent.tOnlineRegistrationStudentOther
		(
			StudentId,
			UIDNumber,
			DateOfBirth,
			BirthDateInWord,
			TransportModeId,
			GenderId,
			BloodGroupId,
			BirthPlace,
			BirthTaluka,
			BirthDistrict,
			FamilyCast,
			FamilySubCast,
			CastCategoryId,
			ReligionId,
			HouseId,
			MotherTongueId,
			NationalityId,
			Hobby,
			CreatedUserId,
			CreatedDateTime,
			ReferenceRemark,
			AdhaarNumber,
			MotherTongue,
			QuotaId,
			BirthState,
			BirthCountry,
			BankAccountNumber,
			BankName,
			BankBranch,
			BankIFSCCode,
			Class9BoardRegistrationNumber,
			Class11BoardRegistrationNumber,
			PassportNumber,
			PassportType,
			PassportCountryCode,
			PassportIssueDate,
			PassportExpiryDate,
			BankAccountHolderName

		)
		VALUES
		(
			@lNextNumber,
			@rUIDNumber,
			@rDateOfBirth,
			@rBirthDateInWord,
			@rTransportModeId,
			@rGenderId,
			@rBloodGroupId,
			@rBirthPlace,
			@rBirthTaluka,
			@rBirthDistrict,
			@rFamilyCast,
			@rFamilySubCast,
			@rCastCategoryId,
			@rReligionId,
			@rHouseId,
			@rMotherTongueId,
			@rNationalityId,
			@rHobby,
			@rCreatedUserId,
			sCommon.fGetDate(),
		    @rReferenceRemark,
			@rAdhaarNumber,
			@rMotherTongue,
			@rQuotaId,
			@rBirthState,
			@rBirthCountry,
			@rBankAccountNumber,
			@rBankName,
			@rBankBranch,
			@rBankIFSCCode,
			@rClass9BoardRegistrationNumber,
			@rClass11BoardRegistrationNumber,
			@rPassportNumber,
			@rPassportType,
			@rPassportCountryCode,
			@rPassportIssueDate,
			@rPassportExpiryDate,
			@rBankAccountHolderName
		)
	
	INSERT INTO sStudent.tOnlineRegistrationStudentFamily
           (StudentId
           ,FatherName
           ,FatherDateOfBirth
           ,FatherOccupation
           ,FatherQualification
           ,FatherContactNo
           ,FatherMail
           ,FatherOfficeAddress
           ,FatherIncome
           ,MotherName
           ,MotherDateOfBirth
           ,MotherOccupation
           ,MotherQualification
           ,MotherContactNo
           ,MotherMail
           ,MotherOfficeAddress
           ,MotherIncome
           ,RelationshipName
           ,RelativeName
           ,RelativeDateOfBirth
           ,RelativeOccupation
           ,RelativeQualification
           ,RelativeContactNo
           ,RelativeMail
           ,RelativeOfficeAddress
           ,RelativeIncome
           ,CreatedUserId
           ,CreatedDateTime
           ,FatherPhoto
           ,MotherPhoto
           ,RelativePhoto
           ,LiveWithId
           ,IsFatherExStudent
           ,FatherPassOutYear
           ,IsMotherExStudent
           ,MotherPassOutYear
           ,IsRelativeExStudent
           ,RelativePassOutYear
           ,IsSingleChild
           ,FatherOrganization
           ,FatherDesignation
           ,MotherOrganization
           ,MotherDesignation
           ,RelativeOrganization
           ,RelativeDesignation
           ,FatherAdhaarNumber
           ,MotherAdhaarNumber
           ,FatherOfficeContactNo
           ,MotherOfficeContactNo
		   ,MarriageAnniversaryDate --Change for mantis 29443
		   )
     VALUES
			(@lNextNumber,
			@rFatherName,
			@rFatherDateOfBirth,
			@rFatherOccupation,
			@rFatherQualification,
			@rFatherContactNo,
			@rFatherMail,
			@rFatherOfficeAddress,
			@rFatherIncome,
			@rMotherName,
			@rMotherDateOfBirth,
			@rMotherOccupation,
			@rMotherQualification,
			@rMotherContactNo,
			@rMotherMail,
			@rMotherOfficeAddress,
			@rMotherIncome,
			@rRelationshipName,
			@rRelativeName,
			@rRelativeDateOfBirth,
			@rRelativeOccupation,
			@rRelativeQualification,
			@rRelativeContactNo,
			@rRelativeMail,
			@rRelativeOfficeAddress,
			@rRelativeIncome,
			@rCreatedUserId,
			sCommon.fGetDate(),
			@rFatherPhoto,
			@rMotherPhoto,
			@rRelativePhoto,			
			@rLiveWithId,
			@rIsFatherExStudent,
			@rFatherPassOutYear,
			@rIsMotherExStudent,
			@rMotherPassOutYear,
			@rIsRelativeExStudent,
			@rRelativePassOutYear,
			@rIsSingleChild,
			@rFatherOrganization,
			@rFatherDesignation,
			@rMotherOrganization,
			@rMotherDesignation,
			@rRelativeOrganization,
			@rRelativeDesignation,
			@rFatherAadharNo,
			@rMotherAadharNo,
			@rFatherOfficeContactNo,
			@rMotherOfficeContactNo,
			@rMarriageAnniversaryDate --Change for mantis 29443
			)	

	INSERT INTO sStudent.tOnlineRegistrationStudentCommunication
		(
			StudentId,
			PresentStreetOne,
			PresentStreetTwo,
			PresentStreetThree,
			PresentCountry,
			PresentState,
			PresentCity,
			PresentZipCode,
			PresentContactNo,
			PermanentStreetOne,
			PermanentStreetTwo,
			PermanentStreetThree,
			PermanentCountry,
			PermanentState,
			PermanentCity,
			PermanentZipCode,
			PermanentContactNo,
			EmergencyStreetOne,
			EmergencyStreetTwo,
			EmergencyStreetThree,
			EmergencyCountry,
			EmergencyState,
			EmergencyCity,
			EmergencyZipCode,
			EmergencyContactNo,
			ParentContactNo,
			ParentEmail,
			StudentContactNo,
			StudentEmail,
			CreatedUserId,
			CreatedDateTime,
			PresentDistanceFromSchool,
			StudentCustomAlert
			--WhatsAppContactNo
		)
	VALUES
		(
			@lNextNumber,
			@rPresentStreetOne,
			@rPresentStreetTwo,
			@rPresentStreetThree,
			@rPresentCountry,
			@rPresentState,
			@rPresentCity,
			@rPresentZipCode,
			@rPresentContactNo,
			@rPermanentStreetOne,
			@rPermanentStreetTwo,
			@rPermanentStreetThree,
			@rPermanentCountry,
			@rPermanentState,
			@rPermanentCity,
			@rPermanentZipCode,
			@rPermanentContactNo,
			@rEmergencyStreetOne,
			@rEmergencyStreetTwo,
			@rEmergencyStreetThree,
			@rEmergencyCountry,
			@rEmergencyState,
			@rEmergencyCity,
			@rEmergencyZipCode,
			@rEmergencyContactNo,
			@rParentContactNo,
			@rParentEmail,
			@rStudentContactNo,
			@rStudentEmail,
			@rCreatedUserId,
			sCommon.fGetDate(),
			@rPresentDistanceFromSchool,
			@rStudentCustomAlert
			--@rWhatsAppContactNo
			
		)

	INSERT INTO sStudent.tOnlineRegistrationStudentAdmission
           (
		   StudentId,
           PreviousSchool,
           PreviousSchoolTypeId,
           PreviousStudentId,
           --AdmissionFormId,
           AdmissionFormNumber,
           AdmissionStandardId,
           AdmissionDate,
           AdmissionYear,
           LeftStandardId,
           LeftDate,
           LeftYear,
           InactiveDate,
           InActiveReason,
           LeftReason,
           LCProgress,
           LCConduct,
           LCBook,
           LCNumber,
           LCDate,
           LCRemark,
           LCCopy,
           LcId,
           CreatedUserId,
           CreatedDateTime,
           ExamSeatNo,
           AdmissionNumber,
           IsTransfer,
           TransferFrom,
           PassingYear,
           IsTFWS,
           IsCertificateHolder,
           CertificateName,
           AdmissionQuotaId,
           PreviousStandard,
           PreviousLCNumber,
           PreviousLCDate,
           PreviousLCResult,
           ProofOfDOB
		   )
     VALUES
           (
		   @lNextNumber,
           @rPreviousSchool,
           @rPreviousSchoolTypeId,
           @rPreviousStudentId,
          -- @rAdmissionFormId,
           @rAdmissionFormNumber,
           @rAdmissionStandardId,
           @rAdmissionDate,
           @rAdmissionYear,
           @rLeftStandardId,
           @rLeftDate,
           @rLeftYear,
           @rInactiveDate,
           @rInActiveReason,
           @rLeftReason,
           @rLCProgress,
           @rLCConduct,
           @rLCBook,
           @rLCNumber,
           @rLCDate,
           @rLCRemark,
           @rLCCopy,
           @rLcId,
           @rCreatedUserId,
		   sCommon.fGetDate(),
           @rExamSeatNo,
           @rAdmissionNumber,
           @rIsTransfer,
           @rTransferFrom,
           @rPassingYear,
           @rIsTFWS,
           @rIsCertificateHolder,
           @rCertificateName,
           @rAdmissionQuotaId,
           @rPreviousStandard,
           @rPreviousLCNumber,
           @rPreviousLCDate,
           @rPreviousLCResult,
           @rProofOfDOB
		   )

	INSERT INTO sStudent.tOnlineRegistrationCollegeDetail
           (
		   StudentId,
           --AdmissionNumber,
           --IsTransfer,
           --TransferFrom,
           --PassingYear,
           --IsTFWS,
           --IsCertificateHolder,
           --CertificateName,
           SSCBoard,
           SSCSchool,
           SSCPassingYear,
           SSCObtainMarks,
           SSCOutOfMarks,
           SSCPercentage,
           SSCMathMarks,
           SSCMathOutOfMarks,
           HSCBoard,
           HSCSchool,
           HSCPassingYear,
           HSCObtainMarks,
           HSCOutOfMarks,
           HSCPercentage,
           HSCPhysicsMarks,
           HSCPhysicsOutOfMarks,
           HSCChemistryMarks,
           HSCChemistryOutOfMarks,
           HSCMathMarks,
           HSCMathOutOfMarks,
           HSCBiologyMarks,
           HSCBiologyOutOfMarks,
           PCMMarks,
           PCBMarks,
           JEERollNo,
           JEEMeritNo,
           JEEMeritMarks,
           JEEOutOfMarks,
           DiplomaBoard,
           DiplomaCollege,
           DiplomaPassingYear,
           DiplomaObtainMarks,
           DiplomaOutOfMarks,
           DiplomaPercentage,
           ApplicationNo,
           AdmissionRound,
           MaritalStatusId,
           SSCPercentile,
           SSCGrade
		   )
     VALUES
           (
		   @lNextNumber,
           --@rAdmissionNumber,
           --@rIsTransfer,
           --@rTransferFrom,
           --@rPassingYear,
           --@rIsTFWS,
           --@rIsCertificateHolder,
           --@rCertificateName,
           @rSSCBoard,
           @rSSCSchool,
           @rSSCPassingYear,
           @rSSCObtainMarks,
           @rSSCOutOfMarks,
           @rSSCPercentage,
           @rSSCMathMarks,
           @rSSCMathOutOfMarks,
           @rHSCBoard,
           @rHSCSchool,
           @rHSCPassingYear,
           @rHSCObtainMarks,
           @rHSCOutOfMarks,
           @rHSCPercentage,
           @rHSCPhysicsMarks,
           @rHSCPhysicsOutOfMarks,
           @rHSCChemistryMarks,
           @rHSCChemistryOutOfMarks,
           @rHSCMathMarks,
           @rHSCMathOutOfMarks,
           @rHSCBiologyMarks,
           @rHSCBiologyOutOfMarks,
           @rPCMMarks,
           @rPCBMarks,
           @rJEERollNo,
           @rJEEMeritNo,
           @rJEEMeritMarks,
           @rJEEOutOfMarks,
           @rDiplomaBoard,
           @rDiplomaCollege,
           @rDiplomaPassingYear,
           @rDiplomaObtainMarks,
           @rDiplomaOutOfMarks,
           @rDiplomaPercentage,
           @rApplicationNo,
           @rAdmissionRound,
           @rMaritalStatusId,
           @rSSCPercentile,
           @rSSCGrade
		   )
	--Added by dhananjay for mantis Start - 33982
	--Health Details
	INSERT INTO sStudent.tOnlineRegistrationStudentHealth
	(
		StudentId,
		IdentificationMark1,
		IdentificationMark2,
		IsPysicalDeformity,
		PysicalDeformityRemark,
		IsMajorIllness,
		MajorIllnessRemark,
		IsAllergy,
		AllergyRemark, 
		IsPastHistory,
		PastHistoryRemark,
		FamilyDoctorName,
		FamilyDoctorContactNo,
		UpdatedUserId,
		UpdatedDateTime ,
		PhysicalDisabilityCategoryId
	)
	values
	(
		@lNextNumber ,
		@rIdentificationMark1,
		@rIdentificationMark2,
		@rIsPysicalDeformity,
		@rPysicalDeformityRemark, 
		@rIsMajorIllness,
		@rMajorIllnessRemark, 
		@rIsAllergy,
		@rAllergyRemark,
		@rIsPastHistory,
		@rPastHistoryRemark,
		@rFamilyDoctorName,
		@rFamilyDoctorContactNo,
		@rUpdatedUserId,
		sCommon.fGetDate(),
		@rPhysicalDisabilityCategoryId
		   
	)
	--Added by dhananjay for mantis End- 33982 
	END	 
	 ELSE
	 BEGIN
	 SET @lNextNumber = @rId
			IF(@rNumber=0)
			BEGIN
			SET  @rNumber=(SELECT ISnull(Number,0) FROM sStudent.tOnlineRegistrationStudentMaster WHERE  Id=@rId)
			IF(@rNumber=0)
			SET @rNumber=@lNextNumber
			END
	    UPDATE  sStudent.tOnlineRegistrationStudentMaster
	    SET
			--Code=@rCode,
			Number=@rNumber,
			RegistrationNumber=@rRegistrationNumber,
			FirstName=@rFirstName,
			MiddleName=@rMiddleName,
			LastName=@rLastName,
			--OrgId=@rOrgId,
			--DepartmentId=@rDepartmentId,
			AdmissionCategoryId=@rAdmissionCategoryId,
			StatusId=@rStatusId,
			RecordStatusId=@rRecordStatusId,
	        UpdatedUserId=@rUpdatedUserId,
	        UpdatedDateTime=sCommon.fGetDate(),
			ClassId=@rClassId,
			DivisionId=@rDivisionId,
			RollNo=@rRollNo,
			BatchId=@rBatchId
			--StudentGroupId=@rStudentGroupId ,
		 --   StudentOtherGroupId=@rStudentOtherGroupId
	      
	      WHERE Id = @rId 
	      
	    UPDATE  sStudent.tOnlineRegistrationStudentOther
		SET
			UIDNumber =@rUIDNumber,
			DateOfBirth=@rDateOfBirth,
			BirthDateInWord=@rBirthDateInWord,
			TransportModeId=@rTransportModeId,
			GenderId=@rGenderId,
			BloodGroupId=@rBloodGroupId,
			BirthPlace=@rBirthPlace,
			BirthTaluka=@rBirthTaluka,
			BirthDistrict=@rBirthDistrict,
			FamilyCast=@rFamilyCast,
			FamilySubCast=@rFamilySubCast,
			CastCategoryId=@rCastCategoryId,
			ReligionId=@rReligionId,
			HouseId=@rHouseId,
			MotherTongueId=@rMotherTongueId,
			NationalityId=@rNationalityId,
			Hobby=@rHobby,
			UpdatedUserId=@rUpdatedUserId,
			UpdatedDateTime=sCommon.fGetDate(),
			ReferenceRemark=@rReferenceRemark,
			AdhaarNumber=@rAdhaarNumber,
			MotherTongue=@rMotherTongue,
			QuotaId=@rQuotaId,
			BirthState=@rBirthState,
			BirthCountry=@rBirthCountry,
			BankAccountNumber=@rBankAccountNumber,
			BankName=@rBankName,
			BankBranch=@rBankBranch,
			BankIFSCCode=@rBankIFSCCode,
			Class9BoardRegistrationNumber=@rClass9BoardRegistrationNumber,
			Class11BoardRegistrationNumber=@rClass11BoardRegistrationNumber,
			PassportNumber=@rPassportNumber,
			PassportType=@rPassportType,
			PassportCountryCode=@rPassportCountryCode,
			PassportIssueDate=@rPassportIssueDate,
			PassportExpiryDate=@rPassportExpiryDate,
			BankAccountHolderName=@rBankAccountHolderName
	       
	      WHERE StudentId = @rId 
	     
		UPDATE sStudent.tOnlineRegistrationStudentFamily 
	SET
           FatherName=@rFatherName,
           FatherDateOfBirth=@rFatherDateOfBirth,
           FatherOccupation=@rFatherOccupation,
           FatherQualification=@rFatherQualification,
           FatherContactNo=@rFatherContactNo,
           FatherMail=@rFatherMail,
           FatherOfficeAddress=@rFatherOfficeAddress,
           FatherIncome=@rFatherIncome,
           MotherName=@rMotherName,
           MotherDateOfBirth=@rMotherDateOfBirth,
           MotherOccupation=@rMotherOccupation,
           MotherQualification=@rMotherQualification,
           MotherContactNo=@rMotherContactNo,
           MotherMail=@rMotherMail,
           MotherOfficeAddress=@rMotherOfficeAddress,
           MotherIncome=@rMotherIncome,
           RelationshipName=@rRelationshipName,
           RelativeName=@rRelativeName,
           RelativeDateOfBirth=@rRelativeDateOfBirth,
           RelativeOccupation=@rRelativeOccupation,
           RelativeQualification=@rRelativeQualification,
           RelativeContactNo=@rRelativeContactNo,
           RelativeMail=@rRelativeMail,
           RelativeOfficeAddress=@rRelativeOfficeAddress,
           RelativeIncome=@rRelativeIncome,
           UpdatedUserId=@rUpdatedUserId,
           UpdatedDateTime=sCommon.fGetDate(),
           FatherPhoto=@rFatherPhoto,
           MotherPhoto=@rMotherPhoto,
           RelativePhoto=@rRelativePhoto,          
           LiveWithId=@rLiveWithId,
           IsFatherExStudent=@rIsFatherExStudent,
           FatherPassOutYear=@rFatherPassOutYear,
           IsMotherExStudent=@rIsMotherExStudent,
           MotherPassOutYear=@rMotherPassOutYear,
           IsRelativeExStudent=@rIsRelativeExStudent,
           RelativePassOutYear=@rRelativePassOutYear,
           IsSingleChild=@rIsSingleChild,
           FatherOrganization=@rFatherOrganization,
           FatherDesignation=@rFatherDesignation,
           MotherOrganization=@rMotherOrganization,
           MotherDesignation=@rMotherDesignation,
           RelativeOrganization=@rRelativeOrganization,
           RelativeDesignation=@rRelativeDesignation,
           FatherAdhaarNumber=@rFatherAadharNo,
           MotherAdhaarNumber=@rMotherAadharNo,
           FatherOfficeContactNo=@rFatherOfficeContactNo,
           MotherOfficeContactNo=@rMotherOfficeContactNo,
		   MarriageAnniversaryDate=@rMarriageAnniversaryDate --Change for mantis 29443
	      WHERE StudentId = @rId 

		UPDATE  sStudent.tOnlineRegistrationStudentCommunication
	    SET
			
			PresentStreetOne=@rPresentStreetOne, 
			PresentStreetTwo=@rPresentStreetTwo, 
			PresentStreetThree=@rPresentStreetThree,
			PresentCountry=@rPresentCountry,
			PresentState=@rPresentState,
			PresentCity=@rPresentCity,
			PresentZipCode=@rPresentZipCode,
			PresentContactNo=@rPresentContactNo,
			PermanentStreetOne=@rPermanentStreetOne,
			PermanentStreetTwo=@rPermanentStreetTwo,
			PermanentStreetThree=@rPermanentStreetThree,
			PermanentCountry=@rPermanentCountry,
			PermanentState=@rPermanentState,
			PermanentCity=@rPermanentCity,
			PermanentZipCode=@rPermanentZipCode,
			PermanentContactNo=@rPermanentContactNo,
			EmergencyStreetOne=@rEmergencyStreetOne,
			EmergencyStreetTwo=@rEmergencyStreetTwo,
			EmergencyStreetThree=@rEmergencyStreetThree,
			EmergencyCountry=@rEmergencyCountry,
			EmergencyState=@rEmergencyState,
			EmergencyCity=@rEmergencyCity,
			EmergencyZipCode=@rEmergencyZipCode,
			EmergencyContactNo=@rEmergencyContactNo,
			ParentContactNo=@rParentContactNo,
			ParentEmail=@rParentEmail,
			StudentContactNo=@rStudentContactNo,
			StudentEmail=@rStudentEmail,
			UpdatedUserId=@rUpdatedUserId,
			UpdatedDateTime=sCommon.fGetDate(),
			PresentDistanceFromSchool=@rPresentDistanceFromSchool,
			StudentCustomAlert = @rStudentCustomAlert,
			WhatsAppContactNo = @rWhatsAppContactNo
	      
	     WHERE StudentId = @rId 	

		UPDATE sStudent.tOnlineRegistrationStudentAdmission
		
		SET 
          PreviousSchool = @rPreviousSchool ,
		  PreviousSchoolTypeId = @rPreviousSchoolTypeId ,
		  PreviousStudentId = @rPreviousStudentId ,
		 -- AdmissionFormId = @rAdmissionFormId ,
		  AdmissionFormNumber = @rAdmissionFormNumber,
		  AdmissionStandardId = @rAdmissionStandardId,
		  AdmissionDate = @rAdmissionDate,
		  AdmissionYear = @rAdmissionYear,
		  LeftStandardId = @rLeftStandardId,
		  LeftDate = @rLeftDate,
		  LeftYear = @rLeftYear,
		  InactiveDate = @rInactiveDate, 
		  InActiveReason = @rInActiveReason,
		  LeftReason = @rLeftReason,
		  LCProgress = @rLCProgress,
		  LCConduct = @rLCConduct,
		  LCBook = @rLCBook,
		  LCNumber = @rLCNumber,
		  LCDate = @rLCDate,
		  LCRemark = @rLCRemark,
		  LCCopy = @rLCCopy,
		  LcId = @rLcId,
		  UpdatedUserId=@rUpdatedUserId,
		  UpdatedDateTime=sCommon.fGetDate(),
		  ExamSeatNo = @rExamSeatNo,
		  AdmissionNumber = @rAdmissionNumber,
		  IsTransfer = @rIsTransfer,
		  TransferFrom = @rTransferFrom,
		  PassingYear = @rPassingYear,
		  IsTFWS = @rIsTFWS,
		  IsCertificateHolder = @rIsCertificateHolder,
		  CertificateName = @rCertificateName,
		  AdmissionQuotaId = @rAdmissionQuotaId,
		  PreviousStandard = @rPreviousStandard,
		  PreviousLCNumber = @rPreviousLCNumber,
		  PreviousLCDate = @rPreviousLCDate,
		  PreviousLCResult = @rPreviousLCResult,
		  ProofOfDOB = @rProofOfDOB
		WHERE StudentId = @rId 
		
		UPDATE sStudent.tOnlineRegistrationCollegeDetail

		SET
           --AdmissionNumber = @rAdmissionNumber,
           --IsTransfer = @rIsTransfer,
           --TransferFrom = @rTransferFrom,
           --PassingYear = @rPassingYear,
           --IsTFWS = @rIsTFWS,
           --IsCertificateHolder = @rIsCertificateHolder,
           --CertificateName = @rCertificateName,
           SSCBoard = @rSSCBoard,
           SSCSchool =@rSSCSchool,
           SSCPassingYear =@rSSCPassingYear,
           SSCObtainMarks = @rSSCObtainMarks,
           SSCOutOfMarks = @rSSCOutOfMarks,
           SSCPercentage = @rSSCPercentage,
           SSCMathMarks = @rSSCMathMarks,
           SSCMathOutOfMarks = @rSSCMathOutOfMarks,
           HSCBoard = @rHSCBoard,
           HSCSchool = @rHSCSchool,
           HSCPassingYear = @rHSCPassingYear,
           HSCObtainMarks = @rHSCObtainMarks,
           HSCOutOfMarks = @rHSCOutOfMarks,
           HSCPercentage = @rHSCPercentage,
           HSCPhysicsMarks = @rHSCPhysicsMarks,
           HSCPhysicsOutOfMarks = @rHSCPhysicsOutOfMarks,
           HSCChemistryMarks = @rHSCChemistryMarks,
           HSCChemistryOutOfMarks = @rHSCChemistryOutOfMarks,
           HSCMathMarks = @rHSCMathMarks,
           HSCMathOutOfMarks = @rHSCMathOutOfMarks,
           HSCBiologyMarks = @rHSCBiologyMarks,
           HSCBiologyOutOfMarks = @rHSCBiologyOutOfMarks,
           PCMMarks = @rPCMMarks,
           PCBMarks = @rPCBMarks,
           JEERollNo = @rJEERollNo,
           JEEMeritNo = @rJEEMeritNo,
           JEEMeritMarks = @rJEEMeritMarks,
           JEEOutOfMarks = @rJEEOutOfMarks,
           DiplomaBoard = @rDiplomaBoard,
           DiplomaCollege = @rDiplomaCollege,
           DiplomaPassingYear =@rDiplomaPassingYear,
           DiplomaObtainMarks = @rDiplomaObtainMarks,
           DiplomaOutOfMarks = @rDiplomaOutOfMarks,
           DiplomaPercentage = @rDiplomaPercentage,
           ApplicationNo = @rApplicationNo,
           AdmissionRound = @rAdmissionRound,
           MaritalStatusId = @rMaritalStatusId,
           SSCPercentile = @rSSCPercentile,
           SSCGrade =@rSSCGrade
		   WHERE StudentId = @rId

		   --Added by dhananjay for mantis start - 33982
		Update sStudent.tOnlineRegistrationStudentHealth

		SET 	
		IdentificationMark1 = @rIdentificationMark1 ,
		IdentificationMark2 = @rIdentificationMark2 ,
		IsPysicalDeformity = @rIsPysicalDeformity ,
		PysicalDeformityRemark = @rPysicalDeformityRemark,
		IsMajorIllness = @rIsMajorIllness ,
		MajorIllnessRemark = @rMajorIllnessRemark,
		IsAllergy = @rIsAllergy ,
		AllergyRemark =	@rAllergyRemark,
		IsPastHistory = @rIsPastHistory ,
		PastHistoryRemark = @rPastHistoryRemark ,
		FamilyDoctorName = @rFamilyDoctorName ,
		FamilyDoctorContactNo =	@rFamilyDoctorContactNo ,
		UpdatedUserId =	@rUpdatedUserId ,
		UpdatedDateTime = sCommon.fGetDate() ,
		PhysicalDisabilityCategoryId = @rPhysicalDisabilityCategoryId 

		where StudentId = @rId
			--Added by dhananjay for mantis start - 33982

	 --    IF(@rStatusId=3)
		--BEGIN
		--    UPDATE sSecurity.tUser
		--	   SET IsObsolete=1 
		--	WHERE SourceTypeId=2003 AND SourceId=@rId
		--END
	 END  
	 
		SET @OutStudentId = @lNextNumber
	  SELECT 1 AS Result, @rCode AS Code, @lNextNumber AS Id 
		--SELECT @lNextNumber AS Result
	
	
	
		COMMIT TRANSACTION
		
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
		
	END CATCH	
		
END
GO
PRINT N'Altering Procedure [sStudent].[pOnlineStudentSearchGet]...';


GO

ALTER PROCEDURE [sStudent].[pOnlineStudentSearchGet]
	 
	@rStudentId bigint,
	@rCode nvarchar(20),
	@rFirstName nvarchar(100)='',
	@rMiddleName nvarchar(100)='',
	@rLastName nvarchar(100)='',
	@rClassId int=0,
	@rDivisionId int=0,
	--@rRollNo int=NULL,
	@rOrgId int,
	@rStatusId smallint=0,
	@rBatchId int = 0
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON			 

BEGIN 	 
	BEGIN
		SET @rCode= sCommon.fSearchStringGet(@rCode)
		SET @rFirstName = sCommon.fSearchStringGet(@rFirstName)
		SET @rMiddleName = sCommon.fSearchStringGet(@rMiddleName)
		SET @rLastName = sCommon.fSearchStringGet(@rLastName)
	END

	SELECT 
			SM.Id AS StudentId,
			SM.Code,
			SM.FirstName,
			SM.MiddleName,
			SM.LastName,
			SM.ClassId,
			SM.DivisionId,
			SM.FirstName +' '+SM.MiddleName +' '+SM.LastName AS Name,
			SM.RollNo,
			(CM.DisplayTitle+'-'+DM.DisplayTitle) AS ClassDivision,
			CM.DisplayTitle AS ClassName,
			DM.DisplayTitle AS DivisionName,
			SM.StudentStatusId,
            SM.OrgId,
			SM.StatusId,
			SM.RecordStatusId,
			SM.AdmissionCategoryId,
			SM.RegistrationNumber,
			SM.BatchId,
			(CASE WHEN SM.StatusId =1 THEN 'Active'
			WHEN SM.StatusId =2 THEN 'InActive'
			WHEN SM.StatusId =2 THEN 'Left' ELSE '' END) AS StatusName,
			SM.StudentPhoto,

			--Other
			SO.UIDNumber,
			ISNULL(SO.DateOfBirth,'0001/01/01')AS DateOfBirth,
			SO.BirthDateInWord,
			SO.TransportModeId,
			SO.GenderId,
			SO.BloodGroupId,
			SO.BirthPlace,
			SO.BirthTaluka,
			SO.BirthDistrict,
			SO.FamilyCast,
			SO.FamilySubCast,
			SO.CastCategoryId,
			SO.ReligionId,
			SO.HouseId,
			SO.MotherTongueId,
			SO.NationalityId,
			SO.Hobby,
			SO.CreatedUserId,
			--SO.CreatedDateTime,
			SO.UpdatedUserId,
			--SO.UpdatedDateTime,
			SO.ReferenceRemark,
			SO.AdhaarNumber,
			SO.MotherTongue,
			SO.QuotaId,
			SO.BirthState,
			SO.BirthCountry,
			SO.BankAccountNumber,
			SO.BankName,
			SO.BankBranch,
			SO.BankIFSCCode,
			SO.Class9BoardRegistrationNumber,
			SO.Class11BoardRegistrationNumber,
			SO.PassportNumber,
			SO.PassportType,
			SO.PassportCountryCode,
			ISNULL(SO.PassportIssueDate,'0001/01/01')AS PassportIssueDate,
			ISNULL(SO.PassportExpiryDate,'0001/01/01')AS PassportExpiryDate,
			SO.BankAccountHolderName,

			--Family

			SF.FatherName,
			ISNULL(SF.FatherDateOfBirth ,'0001/01/01')AS FatherDateOfBirth,
			SF.FatherOccupation,
			SF.FatherQualification,
			SF.FatherContactNo,
			SF.FatherMail,
			SF.FatherOfficeAddress,
			SF.FatherIncome,
			SF.MotherName,
			ISNULL(SF.MotherDateOfBirth ,'0001/01/01')AS MotherDateOfBirth,
			SF.MotherOccupation,
			SF.MotherQualification,
			SF.MotherContactNo,
			SF.MotherMail,
			SF.MotherOfficeAddress,
			SF.MotherIncome,
			SF.RelationshipName,
			SF.RelativeName,
			ISNULL(SF.RelativeDateOfBirth ,'0001/01/01')AS RelativeDateOfBirth,
			SF.RelativeOccupation,
			SF.RelativeQualification,
			SF.RelativeContactNo,
			SF.RelativeMail,
			SF.RelativeOfficeAddress,
			SF.RelativeIncome,
			SF.FatherPhoto,
			SF.MotherPhoto,
			SF.RelativePhoto,			
			SF.LiveWithId,
			SF.IsFatherExStudent,
			SF.FatherPassOutYear,
			SF.IsMotherExStudent,
			SF.MotherPassOutYear,
			SF.IsRelativeExStudent,
			SF.RelativePassOutYear,
			SF.IsSingleChild,
			SF.FatherOrganization,
			SF.FatherDesignation,
			SF.MotherOrganization,
			SF.MotherDesignation,
			SF.RelativeOrganization,
			SF.RelativeDesignation,
			SF.FatherAdhaarNumber,
			SF.MotherAdhaarNumber,
			SF.FatherOfficeContactNo,
			SF.MotherOfficeContactNo,
			ISNULL(SF.MarriageAnniversaryDate ,'0001/01/01')AS MarriageAnniversaryDate, --Change for mantis 29443

			--Admission
			SA.PreviousSchool,
			SA.PreviousSchoolTypeId,
			SA.PreviousStudentId,
			SA.AdmissionFormNumber,
			SA.AdmissionStandardId,
			ISNULL(SA.AdmissionDate ,'0001/01/01')AS AdmissionDate,
			SA.AdmissionYear,
			SA.LeftStandardId,
			ISNULL(SA.LeftDate ,'0001/01/01')AS LeftDate,
			SA.LeftYear,
			ISNULL(SA.InactiveDate ,'0001/01/01')AS InactiveDate,
			SA.InActiveReason,
			SA.LeftReason,
			SA.LCProgress,
			SA.LCConduct,
			SA.LCBook,
			SA.LCNumber,
			ISNULL(SA.LCDate ,'0001/01/01')AS LCDate,
			SA.LCRemark,
			SA.LCCopy,
			SA.LcId,
			SA.ExamSeatNo,
			SA.AdmissionNumber,
			SA.IsTransfer,
			SA.TransferFrom,
			SA.PassingYear,
			SA.IsTFWS,
			SA.IsCertificateHolder,
			SA.CertificateName,
			SA.AdmissionQuotaId,
			SA.PreviousStandard,
			SA.PreviousLCNumber,
			ISNULL(SA.PreviousLCDate ,'0001/01/01')AS PreviousLCDate,
			SA.PreviousLCResult,
			SA.ProofOfDOB,

			--Communication
			SC.PresentStreetOne,
			SC.PresentStreetTwo,
			SC.PresentStreetThree,
			SC.PresentCountry,
			SC.PresentState,
			SC.PresentCity,
			SC.PresentZipCode,
			SC.PresentContactNo,
			SC.PermanentStreetOne,
			SC.PermanentStreetTwo,
			SC.PermanentStreetThree,
			SC.PermanentCountry,
			SC.PermanentState,
			SC.PermanentCity,
			SC.PermanentZipCode,
			SC.PermanentContactNo,
			SC.EmergencyStreetOne,
			SC.EmergencyStreetTwo,
			SC.EmergencyStreetThree,
			SC.EmergencyCountry,
			SC.EmergencyState,
			SC.EmergencyCity,
			SC.EmergencyZipCode,
			SC.EmergencyContactNo,
			SC.ParentContactNo,
			SC.ParentEmail,
			SC.StudentContactNo,
			SC.StudentEmail,
			SC.PresentDistanceFromSchool,
			SC.StudentCustomAlert,
			SC.WhatsAppContactNo,

			--College
			SCD.SSCBoard,
			SCD.SSCSchool,
			SCD.SSCPassingYear,
			SCD.SSCObtainMarks,
			SCD.SSCOutOfMarks,
			SCD.SSCPercentage,
			SCD.SSCMathMarks,
			SCD.SSCMathOutOfMarks,
			SCD.HSCBoard,
			SCD.HSCSchool,
			SCD.HSCPassingYear,
			SCD.HSCObtainMarks,
			SCD.HSCOutOfMarks,
			SCD.HSCPercentage,
			SCD.HSCPhysicsMarks,
			SCD.HSCPhysicsOutOfMarks,
			SCD.HSCChemistryMarks,
			SCD.HSCChemistryOutOfMarks,
			SCD.HSCMathMarks,
			SCD.HSCMathOutOfMarks,
			SCD.HSCBiologyMarks,
			SCD.HSCBiologyOutOfMarks,
			SCD.PCMMarks,
			SCD.PCBMarks,
			SCD.JEERollNo,
			SCD.JEEMeritNo,
			SCD.JEEMeritMarks,
			SCD.JEEOutOfMarks,
			SCD.DiplomaBoard,
			SCD.DiplomaCollege,
			SCD.DiplomaPassingYear,
			SCD.DiplomaObtainMarks,
			SCD.DiplomaOutOfMarks,
			SCD.DiplomaPercentage,
			SCD.ApplicationNo,
			SCD.AdmissionRound,
			SCD.MaritalStatusId,
			SCD.SSCPercentile,
			SCD.SSCGrade,

			OM.Title AS OrgName,
			BM.Title AS BatchName,
			StM.Title AS RecordStatus,
			GM.Title AS Gender,
			RM.Title AS Religion,
			BGM.Title AS BloodGroup,
			CCM.Title AS CastCategory,
			HM.Title AS House,
			MT.Title AS MotherTongue,
			CM1.Nationality AS Nationality,
			TM.Title AS Transport,
			QM.Title AS Quota,
			LWM.Title AS LiveWith,

			--ACM.Title AS AdmissionCategory,
			--CM.Title AS AdmissionStandard
		
		--Added by dhananjay for mantis start - 33982
			--Health
			ISNULL(SH.IdentificationMark1,'') AS IdentificationMark1,
			ISNULL(SH.IdentificationMark2,'') AS IdentificationMark2,
			ISNULL(SH.IsPysicalDeformity, 0) AS IsPysicalDeformity,
			ISNULL(SH.PysicalDeformityRemark,'') AS PysicalDeformityRemark,
			ISNULL(SH.IsMajorIllness, 0) AS IsMajorIllness,
			ISNULL(SH.MajorIllnessRemark,'') AS MajorIllnessRemark,
			ISNULL(SH.IsAllergy, 0) AS IsAllergy,
			ISNULL(SH.AllergyRemark,'') AS AllergyRemark,
			ISNULL(SH.IsPastHistory,0) AS IsPastHistory,
			ISNULL(SH.PastHistoryRemark,'') AS PastHistoryRemark,
			ISNULL(SH.FamilyDoctorName,'') AS FamilyDoctorName,
			ISNULL(SH.FamilyDoctorContactNo,'') AS FamilyDoctorContactNo,
			ISNULL(SH.PhysicalDisabilityCategoryId, 0) AS PhysicalDisabilityCategoryId

		--Added by dhananjay for mantis start - 33982
  FROM 
         sStudent.tOnlineRegistrationStudentMaster SM    
		 INNER JOIN sSetup.tClassMaster CM ON CM.Id=SM.ClassId
	     INNER JOIN sSetup.tDivisionMaster DM ON DM.Id=SM.DivisionId  
		 LEFT JOIN sStudent.tOnlineRegistrationStudentOther SO ON SM.Id = SO.StudentId
		 LEFT JOIN sStudent.tOnlineRegistrationStudentFamily SF ON SM.Id = SF.StudentId
		 LEFT JOIN sStudent.tOnlineRegistrationStudentAdmission SA ON SM.Id = SA.StudentId
		 LEFT JOIN sStudent.tOnlineRegistrationStudentCommunication SC ON SM.Id = SC.StudentId
		 LEFT JOIN sStudent.tOnlineRegistrationCollegeDetail SCD on SM.Id = SCD.StudentId
		 INNER JOIN sSetup.tOrganizationMaster OM ON SM.OrgId = OM.Id
		 INNER JOIN sSetup.tBatchMaster BM ON SM.BatchId = BM.Id
		 LEFT JOIN sCommon.tStatusMaster StM ON SM.RecordStatusId = StM.Id
		 LEFT JOIN sCommon.tGenderMaster GM ON SO.GenderId = GM.Id
		 LEFT JOIn sCommon.tReligionMaster RM ON SO.ReligionId = RM.Id
		 LEFT JOIN sCommon.tBloodGroupMaster BGM ON SO.BloodGroupId = BGM.Id
		 LEFT JOIN sCommon.tCastCategoryMaster CCM ON SO.CastCategoryId = CCM.Id
		 LEFT JOIN sActivity.tHouseMaster HM ON SO.HouseId = HM.Id
		 LEFT JOIN sCommon.tMotherTongueMaster MT ON SO.MotherTongueId = MT.Id
		 LEFT JOIN sCommon.tCountryMaster CM1 ON SO.NationalityId = CM1.Id 
		 LEFT JOIN sTransport.tTransportMaster TM ON SO.TransportModeId = TM.Id
		 LEFT JOIn sCommon.tQuotaMaster QM ON SO.QuotaId = QM.Id
		 LEFT JOIn sCommon.tLiveWithMaster LWM ON SF.LiveWithId = LWM.Id
		 --LEFT JOIN sStudent.tAdmissionCategoryMaster ACM ON SM.AdmissionCategoryId = ACM.Id
		 --LEFT JOIN sStudent.tOnlineRegistrationStudentAdmission SA1 ON CM.Id = SA1.AdmissionStandardId
		 LEFT JOIN sStudent.tOnlineRegistrationStudentHealth SH ON SH.StudentId = SM.Id  --Added by dhananjay for mantis start - 33982
  WHERE 
		((SM.Id = @rStudentId) OR (@rStudentId = 0))
        AND((SM.Code like @rCode) OR (ISNULL(@rCode,'') = '')) 
		AND((SM.FirstName like @rFirstName) OR (ISNULL(@rFirstName,'') = '')) 
		AND((SM.MiddleName like @rMiddleName) OR (ISNULL(@rMiddleName,'') = '')) 
		AND((SM.LastName like @rLastName) OR (ISNULL(@rLastName,'') = '')) 
		AND((SM.ClassId=@rClassId)OR(@rClassId=0))
		AND((SM.DivisionId=@rDivisionId)OR(@rDivisionId=0))
		--AND(SM.RollNo=@rRollNo)
		AND((SM.StatusId=@rStatusId)OR(@rStatusId=0))	
		AND(SM.OrgId=@rOrgId)
		AND(SM.BatchId=@rBatchId or @rBatchId = 0)
		
ORDER BY SM.RollNo

		
END
GO
PRINT N'Altering Procedure [sTimetable].[pWebGetDivisionWorkLoadbyDivisionId]...';


GO
ALTER Procedure [sTimetable].[pWebGetDivisionWorkLoadbyDivisionId] 
@rDivisionId int,
@rSchemaId int
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
begin 
    SELECT DISTINCT
        tw.employeeid,
        dw.DivisionId,
        dw.NoOfLecture - ISNULL(tt.CountSubjectId, 0) AS DivisionWorkLoad,
        dw.SubjectId,
        tw.nooflecture - ISNULL(tt.CountTeacherId, 0) AS TeacherWorkLoad
    FROM 
        sTimetable.tDivisionWorkLoad AS dw
    INNER JOIN 
        sTimetable.tTeacherWorkLoad AS tw ON dw.DivisionId = tw.divisionid
                                           AND dw.SubjectId = tw.subjectid -- This condition ensures only teacher workloads for the subjects taught in the division are considered
										   AND dw.TimeTableSchemaId=tw.TimeTableSchemaId
    LEFT JOIN (
        SELECT
            DivisionId,
            SubjectId,
            COUNT(*) AS CountSubjectId,
            EmployeeId,
            COUNT(EmployeeId) AS CountTeacherId
        FROM
            sTimetable.tTempSTimeTable
        WHERE
            DivisionId = @rDivisionId And TimetableschemaId=@rSchemaId
        GROUP BY
            DivisionId,
            SubjectId,
            EmployeeId
    ) AS tt ON dw.DivisionId = tt.DivisionId AND dw.SubjectId = tt.SubjectId AND tw.employeeid = tt.EmployeeId 
    WHERE 
        dw.DivisionId = @rDivisionId 
        AND dw.TimetableSchemaId=@rSchemaId;
end
GO
PRINT N'Altering Procedure [sTransport].[pTrackBusRouteGet]...';


GO
ALTER PROCEDURE [sTransport].[pTrackBusRouteGet]
	@rSourceId bigint,
	@rOrgGroupBatchId int
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN
	SELECT		  
		  ISNULL(VTSSubUserPassword,'') AS VTSSubUserPassword,
		  ISNULL(VTSSubUserName,'') AS VTSSubUserName,
		  ISNULL(BRM.TrackingURL,'') AS TrackingURL,
		  'PickUp' AS RouteType,
		  ISNULL(RegistrationNumber, '') AS VehicleRegistrationNo,
		  ISNULL(BRM.VehicleId, 0) AS VehicleId,
		  BRM.Id AS BusRouteId
	FROM
		sTransport.tAssignRoute AR
		INNER JOIN sTransport.tBusRouteDestinationMaster BRDM ON BRDM.Id=AR.PickupDestinationId
        INNER JOIN sTransport.tBusRouteMaster BRM On BRM.Id=BRDM.BusRouteId
		LEFT JOIN sTransport.tVehicleMaster VM ON VM.Id = BRM.VehicleId
	WHERE 
		SourceId=@rSourceId 
		AND SourceTypeId=2003
		AND OrgGroupBatchId=@rOrgGroupBatchId

	UNION

	SELECT		  
		  ISNULL(VTSSubUserPassword,'') AS VTSSubUserPassword,
		  ISNULL(VTSSubUserName,'') AS VTSSubUserName,
		  ISNULL(BRM.TrackingURL,'') AS TrackingURL,
		  'Drop' AS RouteType,
		  ISNULL(RegistrationNumber, '') AS RegistrationNumber,
		  ISNULL(BRM.VehicleId, 0) AS VehicleId,
		  BRM.Id AS BusRouteId
	FROM
		sTransport.tAssignRoute AR
		INNER JOIN sTransport.tBusRouteDestinationMaster BRDM ON BRDM.Id=AR.DropDestinationId
        INNER JOIN sTransport.tBusRouteMaster BRM On BRM.Id=BRDM.BusRouteId
		LEFT JOIN sTransport.tVehicleMaster VM ON VM.Id = BRM.VehicleId
	WHERE 
		SourceId=@rSourceId 
		AND SourceTypeId=2003
		AND OrgGroupBatchId=@rOrgGroupBatchId
END
GO
PRINT N'Creating Procedure [sAccounting].[pAccountwiseBalanceGet]...';


GO
CREATE PROCEDURE [sAccounting].[pAccountwiseBalanceGet]
	@rOrgGroupId int,
	@rAccountId int
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN

Declare @LedgerId int 
Declare @LOpeningBalance Decimal (25,5)
Declare @OpeningBalance Decimal (25,5)
Declare @lClOsingBalance Decimal (25,5)
Declare @CreditBalance Decimal (25,5)
Declare @DebitBalance Decimal (25,5)
Declare @lCount int
Declare @lLedgerTypeId int
Declare @LedgerName Nvarchar(150)
Declare @LedgerGroupId int
declare @lAccountYearId int
Declare @DecimalCount INT
declare @lStartDate Date,@lEndDate Date

	SELECT @DecimalCount = CASE WHEN CS.Id IS NOT NULL THEN ISNULL(CS.ItemValue,'') ELSE ISNULL(CM.ItemValue,'') END
	FROM sConfig.tConfigurationMaster CM
	LEFT JOIN sConfig.tConfigurationSetup CS ON CM.Id = CS.ConfigurationId
	AND SourceTypeId = 2010 AND SourceId = @rOrgGroupId WHERE CM.Id = 548
	
	IF OBJECT_ID('tempdb..#tempAccountYearMaster') IS NOT NULL DROP TABLE #tempAccountYearMaster

	IF OBJECT_ID('tempdb..#tempUpdateClosingBalance') IS NOT NULL DROP TABLE #tempUpdateClosingBalance

	IF OBJECT_ID('tempdb..#tCashBankBookTemp') IS NOT NULL DROP TABLE #tCashBankBookTemp

	IF OBJECT_ID('tempdb..#tCashBankBook') IS NOT NULL DROP TABLE #tCashBankBook

	CREATE TABLE #tempAccountYearMaster
	(	
 		Id INT IDENTITY (1, 1),
		AccountYearId int,
		AccountId int,
 		AccountYearName nvarchar(100),
	)

	INSERT INTO #tempAccountYearMaster
	select Id,AccountId,Code from sAccounting.tAccountingYearMaster where OrgGroupId = @rOrgGroupId AND AccountId = @rAccountId AND IsLocked = 0
		
   CREATE TABLE #tempUpdateClosingBalance
	(	
	    Id INT IDENTITY (1, 1),
		LedgerId int,
		Temp_Op_Balance decimal(25,5), 
		Closing_Balance decimal(25,5),
		Transaction_Balance decimal(25,5) 
	) 
 	
	CREATE TABLE #tCashBankBookTemp
 	(	Id INT IDENTITY (1, 1),
 		LedgerId int,
 		LedgerName nvarchar(150),
 		OpBalance Decimal(25,5), 
 		CrBalance Decimal(25,5),
 		DrBalance Decimal(25,5),
 		ClBalance Decimal(25,5),
 		LedgerGroupId int
 	) 
	
	CREATE TABLE #tCashBankBook
 	(	Id INT IDENTITY (1, 1),
 		LedgerId int,
 		LedgerName nvarchar(150),
 		OpBalance Decimal(25,5),
 		LedgerTypeId int,
 		LedgerGroupId int,
		AccountYearId int
 	) 

	declare @lCount1 int
	SELECT @lCount1 =  COUNT(Id) FROM #tempAccountYearMaster
	
	WHILE (@lCount1 > 0)
	BEGIN	
		select @lAccountYearId= AccountYearId FROM #tempAccountYearMaster where Id = @lCount1 

		INSERT INTO #tCashBankBook
		select L.Id,L.Title,L.OpeningBalance,LedgerTypeId,L.LedgerGroupId,@lAccountYearId from sAccounting.tLedgerMaster L 
		INNER JOIN sAccounting.tLedgerGroupMaster LG ON L.LedgerGroupId = LG.Id
		where LG.OrgGroupId = L.OrgGroupId 
		AND LG.OrgGroupId = @rOrgGroupId and L.AccountingYearId = @lAccountYearId AND LG.AccountingYearId = @lAccountYearId
		ORDER BY L.Id Asc

		SET @lCount1 = @lCount1 - 1
	END	 
	
	SELECT @lCount =  COUNT(Id) FROM #tCashBankBook    
	WHILE (@lCount > 0)
	BEGIN		
		SELECT @LedgerId =LedgerId,@LedgerName= LedgerName,@OpeningBalance=ISNULL(OpBalance,0),@LedgerGroupId = LedgerGroupId, 
		@lLedgerTypeId=LedgerTypeId, @lAccountYearId = AccountYearId FROM #tCashBankBook WHERE Id = @lCount				

		SELECT  @lStartDate = FromDate, @lEndDate = ToDate from sAccounting.tAccountingYearMaster where OrgGroupId = @rOrgGroupId AND Id = @lAccountYearId
		print @lStartDate
		print @lEndDate
		INSERT INTO #tempUpdateClosingBalance (LedgerId,Temp_Op_Balance,Closing_Balance,Transaction_Balance)
		EXEC [sAccounting].[pUpdateClosingBalance] @lStartDate,@lEndDate,@rOrgGroupId,@lAccountYearId,@LedgerId,''

		 SELECT @LOpeningBalance=ISNULL(Sum(TD.Amount) ,0)
		 FROM  sAccounting.tTransaction T INNER JOIN sAccounting.tTransactionDetail TD
		 ON T.Id=TD.TransactionId And TD.LedgerId=@LedgerId
			AND T.AccountingYearId = @lAccountYearId 
			AND T.TransactionDate < @lStartDate

		SELECT @CreditBalance= -1 * ISNULL(SUM(AMOUNT), 0)
		 FROM  sAccounting.tTransaction T INNER JOIN sAccounting.tTransactionDetail TD
		 ON T.Id=TD.TransactionId 		 
			AND T.AccountingYearId = @lAccountYearId 
			AND T.TransactionDate >= @lStartDate AND T.TransactionDate <= @lEndDate
			AND TD.TransactionTypeId = 1 And TD.LedgerId=@LedgerId

		 SELECT @DebitBalance=  ISNULL(Sum(TD.Amount) ,0)
		 FROM  sAccounting.tTransaction T INNER JOIN sAccounting.tTransactionDetail TD
		 ON T.Id=TD.TransactionId 
			AND T.AccountingYearId = @lAccountYearId 
			AND T.TransactionDate >= @lStartDate AND T.TransactionDate <= @lEndDate
			AND TD.TransactionTypeId = 2 And TD.LedgerId=@LedgerId

			SET @LOpeningBalance = @LOpeningBalance  +  @OpeningBalance

			SELECT  @lClOsingBalance = Closing_Balance FROM #tempUpdateClosingBalance WHERE LedgerId=@LedgerId

			INSERT INTO #tCashBankBookTemp  (LedgerId,LedgerName,OpBalance,CrBalance,DrBalance,ClBalance,LedgerGroupId)
			VALUES 	(@LedgerId,@LedgerName,@LOpeningBalance,@CreditBalance,@DebitBalance,@lClOsingBalance,@LedgerGroupId)
			
			SET @lCount = @lCount - 1
		END	

		--Declare @lYearId int 

		--select @lYearId = Id from sAccounting.tAccountingYearMaster where OrgGroupId = @rOrgGroupId AND AccountId = @rAccountId 
		--AND IsLocked = 0 AND Code = ''


		SELECT LedgerId,LedgerName, ([sAccounting].[fDecimalValueGet] (OpBalance, @DecimalCount)) AS OpBalance,
		  ([sAccounting].[fDecimalValueGet] (ISNULL(CrBalance,0), @DecimalCount)) AS CrBalance
		  ,([sAccounting].[fDecimalValueGet] (ISNULL(DrBalance,0), @DecimalCount)) AS DrBalance
		  ,([sAccounting].[fDecimalValueGet] (ClBalance, @DecimalCount)) AS ClBalance 
		FROM #tCashBankBookTemp	
		--CB
		--INNER JOIN sAccounting.tLedgerMaster L ON 

	DROP TABLE #tCashBankBook
	DROP TABLE #tCashBankBookTemp
	DROP TABLE #tempUpdateClosingBalance
	DROP TABLE #tempAccountYearMaster
END
GO
PRINT N'Creating Procedure [sAccounting].[pDashboardLedgerwiseClosingBalanceGet]...';


GO

CREATE Procedure [sAccounting].[pDashboardLedgerwiseClosingBalanceGet]
	@rOrgGroupId int,
	@rISProfitLoss [smallint],
	@rAccountYearTitle nvarchar(100)

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN

Declare @OpeningBalance decimal(25,5)
Declare @AccOpBalance decimal(25,5) = 0
Declare @ClosingBalance decimal(25,5)
Declare @TransactionBalance decimal(25,5)
Declare @LedgerId int 
Declare @lCount int
Declare @LedgerTypeId int,@lAccountYearId int
declare @lStartDate Date,	@lEndDate Date
	
	IF OBJECT_ID('tempdb..#tempClosingBalance') IS NOT NULL DROP TABLE #tempClosingBalance
	
	IF OBJECT_ID('tempdb..#tempLedger') IS NOT NULL DROP TABLE #tempLedger

	CREATE TABLE #tempClosingBalance
 		(	
 		    Id INT IDENTITY (1, 1),
 			LedgerId int,
 			Temp_Op_Balance decimal(25,5),
 			Closing_Balance decimal(25,5),
 			Trasaction_Balance decimal(25,5) not null
  		) 
	
	CREATE TABLE #tempLedger
 	    (
 	        Id INT IDENTITY (1, 1),
 			LedgerId int,
			AccountId int,
			AccountingYearId int,
 			AccOpBalance decimal(25,5),
 			LedgerTypeId int,
			StartDate DATE,
			EndDate DATE				 
 		) 

 	INSERT INTO #tempLedger
	SELECT LM.Id AS LedgerId ,AM.Id AS AccountId, LM.AccountingYearId, LM.OpeningBalance, LM.LedgerTypeId,AYM.FromDate,AYM.ToDate
	FROM sAccounting.tLedgerMaster LM
	INNER JOIN sAccounting.tAccountingYearMaster AYM ON AYM.Id = LM.AccountingYearId
	INNER JOIN sAccounting.tAccountMaster AM ON AM.Id = AYM.AccountId 
	WHERE LM.AccountingYearId IN (select Id from sAccounting.tAccountingYearMaster where Title = @rAccountYearTitle AND OrgGroupId = @rOrgGroupId AND IsLocked = 0) 
	AND AM.IsActive = 0
	ORDER BY LM.Id Asc
	
	SELECT @lCount =  COUNT(Id) FROM #tempLedger
	
	WHILE (@lCount > 0)
	BEGIN
		SELECT @AccOpBalance =AccOpBalance,@LedgerTypeId= LedgerTypeId,@lAccountYearId=AccountingYearId,
		@lStartDate = StartDate, @lEndDate = EndDate, @LedgerId =LedgerId 
		FROM #tempLedger WHERE Id = @lCount
	 
		IF @rISProfitLoss = 1 
		BEGIN
			SELECT @OpeningBalance = ISNULL(SUM(AMOUNT),0) FROM sAccounting.tTransactionDetail TD
			INNER JOIN sAccounting.tTransaction T ON T.ID = TD.TransactionId
			WHERE T.OrgGroupId = @rOrgGroupId AND T.AccountingYearId = @lAccountYearId
			AND (TD.LedgerId = @LedgerId OR @LedgerId = 0) AND T.IsProfitAndLoss <> 1
		END
		ELSE
		  BEGIN
			SELECT @OpeningBalance = ISNULL(SUM(AMOUNT),0) FROM sAccounting.tTransactionDetail TD
			INNER JOIN sAccounting.tTransaction T ON T.ID = TD.TransactionId
			WHERE T.OrgGroupId = @rOrgGroupId AND T.AccountingYearId = @lAccountYearId
			AND  (TD.LedgerId = @LedgerId OR @LedgerId = 0)
		 END				
   -- Transaction Balance
   		IF @rISProfitLoss = 1 
		  BEGIN
			SELECT @TransactionBalance = ISNULL(SUM(AMOUNT),0) FROM sAccounting.tTransactionDetail TD
			INNER JOIN sAccounting.tTransaction T ON T.ID = TD.TransactionId
			WHERE T.OrgGroupId = @rOrgGroupId AND T.AccountingYearId = @lAccountYearId
			AND (TD.LedgerId = @LedgerId OR @LedgerId = 0) AND T.IsProfitAndLoss <> 1
		  END
		ELSE
		  BEGIN
			SELECT @TransactionBalance = ISNULL(SUM(AMOUNT),0) FROM sAccounting.tTransactionDetail TD
			INNER JOIN sAccounting.tTransaction T ON T.ID = TD.TransactionId
			WHERE T.OrgGroupId = @rOrgGroupId AND T.AccountingYearId = @lAccountYearId
			AND (TD.LedgerId = @LedgerId OR @LedgerId = 0)
		  END				
	--
		IF @LedgerTypeId = 1 
		BEGIN
			SET @TransactionBalance = -1 * @TransactionBalance
			SET @OpeningBalance = -1 * @OpeningBalance
		END
		ELSE
		BEGIN
			SET @TransactionBalance = @TransactionBalance
			SET @OpeningBalance = @OpeningBalance
		END				
			
		SET @ClosingBalance = @OpeningBalance + @TransactionBalance				
	
		INSERT INTO #tempClosingBalance (LedgerId,Temp_Op_Balance,Closing_Balance,Trasaction_Balance)
		EXEC [sAccounting].[pUpdateClosingBalance] @lStartDate,@lEndDate,@rOrgGroupId,@lAccountYearId,@LedgerId,''
		SET @lCount = @lCount - 1
	END	

	DECLARE  @columns NVARCHAR(MAX) = '', @sql     NVARCHAR(MAX) = '';
	
	SET @columns =(SELECT STUFF(
    ( SELECT  distinct ',' +  QUOTENAME(title) FROM sAccounting.tLedgerMaster where OrgGroupId = @rOrgGroupId
	FOR XML PATH('')), 1, 1, '') AS Ledgers)


	DECLARE  @columnsum NVARCHAR(MAX) = ''	
	SET @columnsum =(SELECT STUFF(
    (
        SELECT  distinct '+ ISNULL(' + QUOTENAME(Title) + ', 0) '
         FROM sAccounting.tLedgerMaster where OrgGroupId = @rOrgGroupId
        FOR XML PATH('')
    ), 1, 1, '') AS Ledgers)
	
	DECLARE  @columnisnull NVARCHAR(MAX) = ''	
	SET @columnisnull =(SELECT STUFF(
    (
        SELECT  distinct ', ISNULL(' + QUOTENAME(Title) + ', 0) AS ' + QUOTENAME(Title)
         FROM sAccounting.tLedgerMaster where OrgGroupId = @rOrgGroupId
        FOR XML PATH('')
    ), 1, 2, '') AS Ledgers)
	
	-- construct dynamic SQL
  SET @sql ='SELECT AccountId,Account, ' + @columnisnull + ', '+@columnsum+' AS Total FROM ( 
  SELECT * FROM
  (SELECT ISNULL(TCB.Closing_Balance,0.00) AS Closing_Balance
		,AM.Title AS Account, AM.Id AS AccountId, LM.Title AS LedgerName
		FROM #tempClosingBalance TCB
		INNER JOIN sAccounting.tLedgerMaster  LM ON LM.Id=TCB.LedgerId
		INNER JOIN #tempLedger TL ON TL.LedgerId=LM.Id
		--INNER JOIN sAccounting.tAccountingYearMaster AY ON AY.Id = LM.AccountingYearId
		INNER JOIN sAccounting.tAccountMaster AM ON AM.Id = TL.AccountId
		AND AM.IsActive = 0
		) AS Table1
  PIVOT(
    SUM(Closing_Balance)
    FOR LedgerName IN ('+ @columns +')
  ) AS pivot_table ) AS A';

  EXECUTE sp_executesql @sql;

	drop table #tempClosingBalance
	drop table #tempLedger
END
GO
PRINT N'Creating Procedure [sAttendance].[pMonthlyAttenanceRegisterDateWiseGet]...';


GO


CREATE PROCEDURE [sAttendance].[pMonthlyAttenanceRegisterDateWiseGet]
	 @rBuilder1 nvarchar(max)='',
	 @rBuilder2 nvarchar(max)='', 
	 @rAttendanceTypeId smallint=0,
	 @rOrgId int,
	 @rFromDate date,
	 @rToDate date,
	 @rClassId int=0,
	 @rDivisionId int=0,
	 @rBatchId int = 0,
	 @rSubjectId int = 0,
	 @rPeriodNo SmallInt = 0
	 WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	 
BEGIN 

	IF(@rSubjectId > 0)	
		BEGIN
			DECLARE @lClassSubjectId int = 0;
			SELECT @lClassSubjectId = Id FROM sSetup.tClassSubject WHERE ClassId = @rClassId AND SubjectId = @rSubjectId

			SELECT	    
				ISNULL(SA.ClassName + '-' + SA.DivisionName,'') AS ClassDivisionTitle,
				SA.Code,
				SA.RollNo,
				SA.AcademicId,
				SA.Name,
				SA.ClassId,
				SA.DivisionId,
				SA.ClassName,Sa.DivisionName,	 
				A.LeftDate,
				SA.StatusId,
				A.AdmissionDate,
				ISNULL(sAttendance.fGetAbsentDatesDateWise(SA.AcademicId, @rAttendanceTypeId, @rFromDate, @rToDate, @rSubjectId,@rPeriodNo),'') AS AbsentDates
	 	 
			FROM
				sStudent.vStudentAcademic SA 
				INNER JOIN sStudent.tStudentAdmission A ON SA.StudentId = A.StudentId
				INNER JOIN sSetup.tClassMaster CM ON CM.Id = SA.ClassId
				INNER JOIN sSetup.tDivisionMaster DM ON DM.Id=SA.DivisionId
				INNER JOIN sSetup.tClassSubject CS ON CM.Id = CS.ClassId
				LEFT JOIN sAssessment.tOptionalSubject OS ON OS.AcademicId= SA.AcademicId AND OS.ClassSubjectId = @lClassSubjectId
             
			WHERE  
	        
				SA.OrgId=@rOrgId  
				AND  (SA.BatchId=@rBatchId) AND  (SA.ClassId=@rClassId) 
				AND  (SA.DivisionId=@rDivisionId)
				AND  A.AdmissionDate <=  @rToDate
				AND ((A.LeftDate >= @rFromDate) OR ISNULL(A.LeftDate,'') = '')
				AND (CS.SubjectId = @rSubjectId)
				AND (CS.IsOptional = 0 OR OS.ClassSubjectId = @lClassSubjectId )
				Order By SA.RollNo
		END
	ELSE
		BEGIN
			SELECT	    
				ISNULL(SA.ClassName + '-' + SA.DivisionName,'') AS ClassDivisionTitle,
				SA.Code,
				SA.RollNo,
				SA.AcademicId,
				SA.Name,
				SA.ClassId,
				SA.DivisionId,
				SA.ClassName,Sa.DivisionName,	 
				A.LeftDate,
				SA.StatusId,
				A.AdmissionDate,
				ISNULL(sAttendance.fGetAbsentDatesDateWise(SA.AcademicId, @rAttendanceTypeId, @rFromDate, @rToDate, 0,0),'') AS AbsentDates
	 	 
			FROM
				sStudent.vStudentAcademic SA 
				INNER JOIN sStudent.tStudentAdmission A ON SA.StudentId = A.StudentId
				--INNER JOIN sSetup.tClassMaster CM ON CM.Id = SA.ClassId
				--INNER JOIN sSetup.tDivisionMaster DM ON DM.Id=SA.DivisionId
             
			WHERE  
	        
				SA.OrgId=@rOrgId  
				AND  (SA.BatchId=@rBatchId) AND  (SA.ClassId=@rClassId) 
				AND  (SA.DivisionId=@rDivisionId)
				AND  A.AdmissionDate <=  @rToDate
				AND ((A.LeftDate >= @rFromDate) OR ISNULL(A.LeftDate,'') = '')
				Order By SA.RollNo
		END
	
END
GO
PRINT N'Creating Procedure [sAttendance].[pMonthlyAttendaceImportDateWiseAdd]...';


GO
  
CREATE PROCEDURE [sAttendance].[pMonthlyAttendaceImportDateWiseAdd]
	
	@rStrXml nvarchar(MAX),
    @rOrgId int,
    @rClassId int=0,
    @rDivisionId int,
    @rAcademicId bigint,
    @rAttendanceTypeId tinyint,
    @rCreatedUserId bigint,
    @rBatchId int=0,
	@rSubjectId int = 0,
	@rPeriodNo int = 0
		
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
SET @rStrXml=REPLACE(@rStrXml,'&','AND')
DECLARE @loDoc INT
EXEC sp_xml_preparedocument @loDoc OUTPUT, @rStrXml
BEGIN
	BEGIN TRY
	
	 BEGIN TRANSACTION 
	 BEGIN

		CREATE TABLE #TmpMonthlyAttendance
		(
		 Id int IDENTITY(1,1),
		 AttendanceDate Date,
		 IsPresent Bit
		)
		 
		INSERT INTO #TmpMonthlyAttendance 
		SELECT	
		DISTINCT	
			AttendanceDate,
			IsPresent
		FROM OPENXML (@loDoc, '/Table/RowData', 1)
		WITH ( 
				AttendanceDate DateTime,
				IsPresent bit
		   ) AS At

		    
		DECLARE @cnt INT = 1, @lAttendanceDateCount INT = 0
		SELECT @lAttendanceDateCount = COUNT(*) FROM #TmpMonthlyAttendance

		 
		WHILE @cnt < @lAttendanceDateCount + 1
		BEGIN
		     DECLARE @lIsPresent bit , @lAttendanceDate Date

			 SELECT @lIsPresent = IsPresent,@lAttendanceDate = AttendanceDate FROM #TmpMonthlyAttendance WHERE Id = @cnt

			 DELETE SR FROM sAttendance.tStudentRegister SR 
			 INNER JOIN sStudent.tAcademic SA ON SA.Id = SR.AcademicId
			 WHERE 
			 SR.AttendanceTypeId = @rAttendanceTypeId AND SR.AttendanceDate = @lAttendanceDate
			 AND SA.DivisionId=@rDivisionId AND SA.Id = @rAcademicId
			 AND SA.OrgId = @rOrgId AND SA.BatchId =@rBatchId
			 AND (SR.SubjectId = @rSubjectId OR @rSubjectId = 0)
			 AND (SR.PeriodNo = @rPeriodNo OR @rPeriodNo = 0)

			 INSERT INTO sAttendance.tStudentRegister 
			 (
				AcademicId,
				EmployeeId,
				AttendanceTypeId,
				AttendanceDate,
				SubjectId,
				RepeatCount,
				IsPresent,
				AbsentReasonId,
				CreatedUserId,
				CreatedDateTime,
				UpdatedUserId,
				UpdatedDateTime,
				AttendanceTagId,
				PeriodNo
			 )
			SELECT	
			DISTINCT	
				A.Id,
				NULL,
				@rAttendanceTypeId,
				@lAttendanceDate,
				@rSubjectId,
				@rPeriodNo,
				@lIsPresent,
				0,
				@rCreatedUserId,
				sCommon.fGetDate(),
				@rCreatedUserId,
				sCommon.fGetDate(),
				0,
				@rPeriodNo
			FROM   sStudent.tAcademic A  
			INNER JOIN sStudent.tStudentAdmission SA ON SA.StudentId=A.StudentId
			WHERE A.Id = @rAcademicId AND SA.AdmissionDate <= @lAttendanceDate
			AND ((SA.LeftDate > @lAttendanceDate) OR  ISNULL(SA.LeftDate,'') = '') 

			 SET @cnt = @cnt + 1
		END

		 
		

	 END
	 COMMIT TRANSACTION
		
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
		
END
GO
--PRINT N'Creating Procedure [sHrm].[pEmployeeCustomFieldUpdate]...';


--GO
--CREATE PROCEDURE [sHrm].[pEmployeeCustomFieldUpdate]
		 
--	@rId int,
--	@rOrgId int,
--	--@rFieldTitle nvarchar(500),
--	--@rFieldTypeId tinyint,
--	--@rFieldList nvarchar(500),
--	@rIsFieldMandatory bit,
--	--@rFieldGroup nvarchar(200),
--	@rFieldOrder smallint,
--	--@rCreatedUserId bigint,	
--	--@rCreatedDateTime datetime,
--	@rUpdatedUserId bigint,
--	@rUpdatedDateTime datetime,
--	@rIsObsolete int =0
--	--@rDataTypeId int = 1

--WITH EXECUTE AS CALLER
--AS
--SET NOCOUNT ON
--BEGIN

--	BEGIN TRY
--		BEGIN TRANSACTION	

--			DECLARE @lOrgGroupId int=0
--			SELECT @lOrgGroupId= OrgGroupId FROM sSetup.tOrganizationMAster WHERE Id = @rOrgId
 
--			--IF(@rId = 0)	
--			--BEGIN
--				--IF NOT EXISTS(SELECT 2 FROM sHrm.tEmployeeCustomFieldMaster WHERE FieldTitle=@rFieldTitle AND FieldGroup=@rFieldGroup AND DataId=@rDataTypeId AND OrgGroupId=@lOrgGroupId)
--				----IF NOT EXISTS(SELECT 2 FROM sStudent.tStudentCustomFieldMaster WHERE FieldTitle=@rFieldTitle)
--				--BEGIN
--	      --DECLARE @lNextNumber int
----	      EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sHrm.tEmployeeCustomFieldMaster', 0, 1, 1,@outNextNumber = @lNextNumber output
----Print @lNextNumber
----	      INSERT INTO sHrm.tEmployeeCustomFieldMaster
----	      (
----		    Id
----		   ,FieldTitle
----		   ,FieldTypeId
----		   ,FieldList
----		   ,IsFieldMandatory
----		   ,FieldGroup
----		   ,FieldOrder
----		   ,CreatedUserId
----		   ,CreatedDateTime
----		   ,OrgGroupId
----		   ,IsObsolete
----		   ,DataId
----	      )
----	     VALUES
----	     (
----		   @lNextNumber
----		  ,@rFieldTitle
----		  ,@rFieldTypeId
----		  ,@rFieldList
----		  ,@rIsFieldMandatory
----		  ,@rFieldGroup
----		  ,@rFieldOrder
----		  ,@rCreatedUserId
----		  ,@rCreatedDateTime
----		  ,@lOrgGroupId
----		  ,@rIsObsolete
----		  ,@rDataTypeId
----	     )
----     END
----	 ELSE
----	 SELECT -101 As RESULT
----END
	 	 
----ELSE
----BEGIN
--	--SET @lNextNumber = @rId

	
	
--			UPDATE sHrm.tEmployeeCustomFieldMaster
--			SET 
--				--FieldTitle = @rFieldTitle
--				--,FieldTypeId = @rFieldTypeId
--				--,FieldList=@rFieldList
--				IsFieldMandatory=@rIsFieldMandatory
--				,IsObsolete=@rIsObsolete
--				--,FieldGroup=@rFieldGroup
--				,FieldOrder=@rFieldOrder
--				,UpdatedUserId=@rUpdatedUserId
--				,UpdatedDateTime=@rUpdatedDateTime
--				--,DataId = @rDataTypeId
--			WHERE Id = @rId 
	
	
----END  
----	SELECT @lNextNumber AS Result
	
--		COMMIT TRANSACTION
--	END TRY
--	BEGIN CATCH 
--		ROLLBACK TRANSACTION
--		SELECT -1 AS Result
--	END CATCH	
--END
--GO
--PRINT N'Creating Procedure [sHrm].[pNewEmployeeCustomFieldAdd]...';


--GO
--CREATE PROCEDURE [sHrm].[pNewEmployeeCustomFieldAdd]
		 
--	--@rId int,
--	@rOrgId int,
--	@rFieldTitle nvarchar(500),
--	@rFieldTypeId tinyint,
--	@rFieldList nvarchar(500),
--	--@rIsFieldMandatory bit,
--	@rFieldGroup nvarchar(200),
--	--@rFieldOrder smallint,
--	@rCreatedUserId bigint,	
--	@rCreatedDateTime datetime,
--	@rUpdatedUserId bigint,
--	@rUpdatedDateTime datetime,
--	--@rIsObsolete int =0,
--	@rDataTypeId int = 1,
--	@rCustomFieldMasterId int = 0

--WITH EXECUTE AS CALLER
--AS
--SET NOCOUNT ON
--BEGIN

--	BEGIN TRY
--		BEGIN TRANSACTION	

--			DECLARE @lOrgGroupId int=0, @lIsEmployee bit = 0
--			SELECT @lOrgGroupId= OrgGroupId FROM sSetup.tOrganizationMAster WHERE Id = @rOrgId
--			select @lIsEmployee = IsEmployee From sSetup.tCustomFieldMaster WHERE FieldTitle=@rFieldTitle AND FieldGroup=@rFieldGroup AND DataId=@rDataTypeId AND OrgGroupId=@lOrgGroupId
 
--			IF(@lIsEmployee=1)	
--			BEGIN
--				IF NOT EXISTS(SELECT 2 FROM sHrm.tEmployeeCustomFieldMaster WHERE CustomFieldMasterId=@rCustomFieldMasterId)
--				BEGIN
--					DECLARE @lNextNumber int
--					EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sHrm.tEmployeeCustomFieldMaster', 0, 1, 1,@outNextNumber = @lNextNumber output
--					Print @lNextNumber
--					INSERT INTO sHrm.tEmployeeCustomFieldMaster
--					(
--						Id
--					   ,FieldTitle
--					   ,FieldTypeId
--					   ,FieldList
--					   ,IsFieldMandatory
--					   ,FieldGroup
--					   ,FieldOrder
--					   ,CreatedUserId
--					   ,CreatedDateTime
--					   ,OrgGroupId
--					   ,IsObsolete
--					   ,DataId
--					   ,CustomFieldMasterId		--add for mantis: 32799
--					)
--					VALUES
--					(
--					   @lNextNumber
--					  ,@rFieldTitle
--					  ,@rFieldTypeId
--					  ,@rFieldList
--					  ,0
--					  ,@rFieldGroup
--					  ,0
--					  ,@rCreatedUserId
--					  ,@rCreatedDateTime
--					  ,@lOrgGroupId
--					  ,0
--					  ,@rDataTypeId
--					  ,@rCustomFieldMasterId	--add for mantis: 32799
--					)
--				END
--			--ELSE
--	--	 SELECT -101 As RESULT
--	--END
	 	 
--				ELSE
--				BEGIN
--				--SET @lNextNumber = @rId

--					UPDATE sHrm.tEmployeeCustomFieldMaster
--					SET 
--						FieldTitle = @rFieldTitle
--						,FieldTypeId = @rFieldTypeId
--						,FieldList=@rFieldList
--						--,IsFieldMandatory=@rIsFieldMandatory
--						--,IsObsolete=@rIsObsolete
--						,FieldGroup=@rFieldGroup
--						--,FieldOrder=@rFieldOrder
--						,UpdatedUserId=@rUpdatedUserId
--						,UpdatedDateTime=@rUpdatedDateTime
--						,DataId = @rDataTypeId
--					WHERE CustomFieldMasterId=@rCustomFieldMasterId
	
	
--				END  
--			END
--			ELSE
--			BEGIN
--				IF EXISTS(SELECT 2 FROM sHrm.tEmployeeCustomFieldMaster WHERE CustomFieldMasterId=@rCustomFieldMasterId)
--				BEGIN
--					DECLARE @lEmployeeCustomFieldMasterId int=0
--					SELECT @lEmployeeCustomFieldMasterId = Id FROM sHrm.tEmployeeCustomFieldMaster WHERE CustomFieldMasterId=@rCustomFieldMasterId

--					EXEC sHrm.pEmployeeCustomFieldDelete @lEmployeeCustomFieldMasterId, @rOrgId, @lOrgGroupId
--				END
--			END	
--		COMMIT TRANSACTION
--	END TRY
--	BEGIN CATCH 
--		ROLLBACK TRANSACTION
--		SELECT -1 AS Result
--	END CATCH	
--END
--GO
--PRINT N'Creating Procedure [sRecruitment].[pVacancyCustomFieldDelete]...';


--GO
--CREATE PROCEDURE [sRecruitment].[pVacancyCustomFieldDelete]

--@rId int,
--@rOrgId smallint=0,
--@rOrgGroupId int=0
--AS
--BEGIN
 	 
--	 IF NOT EXISTS(Select 1 from sHrm.tEmployeeCustomFieldData ECF 
--	          INNER JOIN sHrm.tEmployeeMaster EM ON EM.Id=ECF.EmployeeId
--	          INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=EM.OrgId 
--	        WHERE ECF.EmployeeCustomFieldId=@rId AND StatusId=7 AND OM.OrgGroupId=@rOrgGroupId
--	           AND (ECF.FieldData <> '0' AND ECF.FieldData<>'' AND ECF.FieldData IS NOT NULL))
--	 BEGIN
        
--        DELETE ECF FROM sHrm.tEmployeeCustomFieldData ECF 
--           INNER JOIN sHrm.tEmployeeMaster EM ON EM.Id=ECF.EmployeeId
--	       INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=EM.OrgId 
--        WHERE ECF.EmployeeCustomFieldId=@rId AND StatusId=1 AND OM.OrgGroupId=@rOrgGroupId
         
--		DELETE	FROM sHrm.tEmployeeCustomFieldMaster WHERE Id=@rId 
--		SELECT 1 AS RESULT
--	  END 

--	  ELSE

--	    SELECT -101 As RESULT

 
--END
--GO
--PRINT N'Creating Procedure [sRecruitment].[pVacancyCustomFieldDetailsGet]...';


--GO
--CREATE PROCEDURE [sRecruitment].[pVacancyCustomFieldDetailsGet]	 
--	@rOrgGroupId int
	 	 
--	WITH EXECUTE AS CALLER
--AS
--SET NOCOUNT ON
	
--BEGIN 
--	SELECT 
--		Id,
--	    FieldTitle,	
--		FieldGroup,	
--		FieldTypeId,
--		FieldList,
--		IsFieldMandatory,
--		IsObsolete,
--		FieldOrder,
--		ISNULL(DataId,0) DataId
--	FROM 
--		 sRecruitment.tVacancyCustomFieldMaster

--	WHERE 
--		OrgGroupId = @rOrgGroupId
		
--END
--GO
--PRINT N'Creating Procedure [sRecruitment].[pVacancyCustomFieldUpdate]...';


--GO
--CREATE PROCEDURE [sRecruitment].[pVacancyCustomFieldUpdate]
		 
--	@rId int,
--	@rOrgId int,
--	--@rFieldTitle nvarchar(500),
--	--@rFieldTypeId tinyint,
--	--@rFieldList nvarchar(500),
--	@rIsFieldMandatory bit,
--	--@rFieldGroup nvarchar(200),
--	@rFieldOrder smallint,
--	--@rCreatedUserId bigint,	
--	--@rCreatedDateTime datetime,
--	@rUpdatedUserId bigint,
--	@rUpdatedDateTime datetime,
--	@rIsObsolete int =0
--	--@rDataTypeId int = 1

--WITH EXECUTE AS CALLER
--AS
--SET NOCOUNT ON
--BEGIN

--	BEGIN TRY
--		BEGIN TRANSACTION	

--			DECLARE @lOrgGroupId int=0
--			SELECT @lOrgGroupId= OrgGroupId FROM sSetup.tOrganizationMaster WHERE Id = @rOrgId
 
--			--IF(@rId = 0)	
--			--BEGIN
--				--IF NOT EXISTS(SELECT 2 FROM sHrm.tEmployeeCustomFieldMaster WHERE FieldTitle=@rFieldTitle AND FieldGroup=@rFieldGroup AND DataId=@rDataTypeId AND OrgGroupId=@lOrgGroupId)
--				----IF NOT EXISTS(SELECT 2 FROM sStudent.tStudentCustomFieldMaster WHERE FieldTitle=@rFieldTitle)
--				--BEGIN
--	      --DECLARE @lNextNumber int
----	      EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sHrm.tEmployeeCustomFieldMaster', 0, 1, 1,@outNextNumber = @lNextNumber output
----Print @lNextNumber
----	      INSERT INTO sHrm.tEmployeeCustomFieldMaster
----	      (
----		    Id
----		   ,FieldTitle
----		   ,FieldTypeId
----		   ,FieldList
----		   ,IsFieldMandatory
----		   ,FieldGroup
----		   ,FieldOrder
----		   ,CreatedUserId
----		   ,CreatedDateTime
----		   ,OrgGroupId
----		   ,IsObsolete
----		   ,DataId
----	      )
----	     VALUES
----	     (
----		   @lNextNumber
----		  ,@rFieldTitle
----		  ,@rFieldTypeId
----		  ,@rFieldList
----		  ,@rIsFieldMandatory
----		  ,@rFieldGroup
----		  ,@rFieldOrder
----		  ,@rCreatedUserId
----		  ,@rCreatedDateTime
----		  ,@lOrgGroupId
----		  ,@rIsObsolete
----		  ,@rDataTypeId
----	     )
----     END
----	 ELSE
----	 SELECT -101 As RESULT
----END
	 	 
----ELSE
----BEGIN
--	--SET @lNextNumber = @rId

	
	
--			UPDATE sRecruitment.tVacancyCustomFieldMaster
--			SET 
--				--FieldTitle = @rFieldTitle
--				--,FieldTypeId = @rFieldTypeId
--				--,FieldList=@rFieldList
--				IsFieldMandatory=@rIsFieldMandatory
--				,IsObsolete=@rIsObsolete
--				--,FieldGroup=@rFieldGroup
--				,FieldOrder=@rFieldOrder
--				,UpdatedUserId=@rUpdatedUserId
--				,UpdatedDateTime=@rUpdatedDateTime
--				--,DataId = @rDataTypeId
--			WHERE Id = @rId 
	
	
----END  
----	SELECT @lNextNumber AS Result
	
--		COMMIT TRANSACTION
--	END TRY
--	BEGIN CATCH 
--		ROLLBACK TRANSACTION
--		SELECT -1 AS Result
--	END CATCH	
--END
--GO
--PRINT N'Creating Procedure [sSetup].[pCustomFieldAdd]...';


--GO
--CREATE PROCEDURE [sSetup].[pCustomFieldAdd]
		 
--	@rId int,
--	@rOrgId int,
--	@rFieldTitle nvarchar(500),
--	@rFieldTypeId tinyint,
--	@rFieldList nvarchar(500),
--	@rIsEmployee bit,
--	@rFieldGroup nvarchar(200),
--	--@rFieldOrder smallint,
--	@rCreatedUserId bigint,	
--	@rCreatedDateTime datetime,
--	@rUpdatedUserId bigint,
--	@rUpdatedDateTime datetime,
--	@rIsVacancy int = 0,
--	@rDataTypeId int = 1

--WITH EXECUTE AS CALLER
--AS
--SET NOCOUNT ON
--BEGIN

--	BEGIN TRY
--	BEGIN TRANSACTION	

--		DECLARE @lOrgGroupId int=0
--		SELECT @lOrgGroupId= OrgGroupId FROM sSetup.tOrganizationMaster WHERE Id = @rOrgId
 
--		IF(@rId = 0)
--		BEGIN
--			IF NOT EXISTS(SELECT 2 FROM sSetup.tCustomFieldMaster WHERE FieldTitle=@rFieldTitle AND FieldGroup=@rFieldGroup AND DataId=@rDataTypeId AND OrgGroupId=@lOrgGroupId)
--			--IF NOT EXISTS(SELECT 2 FROM sStudent.tStudentCustomFieldMaster WHERE FieldTitle=@rFieldTitle)
--			BEGIN
--				DECLARE @lNextNumber int
--				EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sSetup.tCustomFieldMaster', 0, 1, 1,@outNextNumber = @lNextNumber output
--				Print @lNextNumber
--				INSERT INTO sSetup.tCustomFieldMaster
--				(
--					Id
--					,FieldGroup
--					,FieldTitle
--					,FieldTypeId
--					,FieldList
--					,IsEmployee
--					,IsVacancy
--					--,FieldOrder
--					,CreatedUserId
--					,CreatedDateTime
--					,OrgGroupId
--					,DataId
--				)
--				VALUES
--				(
--					@lNextNumber
--					,@rFieldGroup
--					,@rFieldTitle
--					,@rFieldTypeId
--					,@rFieldList
--					,@rIsEmployee
--					,@rIsVacancy
--					--,@rFieldOrder
--					,@rCreatedUserId
--					,@rCreatedDateTime
--					,@lOrgGroupId
--					,@rDataTypeId
--				)
--			END
--			ELSE
--			BEGIN
--				SELECT -101 As RESULT
--			END
--		END
	 	 
--		ELSE
--		BEGIN
--			SET @lNextNumber = @rId

--			UPDATE sSetup.tCustomFieldMaster
--			SET 
--				FieldGroup=@rFieldGroup
--				,FieldTitle = @rFieldTitle
--				,FieldTypeId = @rFieldTypeId
--				,FieldList=@rFieldList
--				,IsEmployee=@rIsEmployee
--				,IsVacancy=@rIsVacancy
--				--,FieldOrder=@rFieldOrder
--				,UpdatedUserId=@rUpdatedUserId
--				,UpdatedDateTime=@rUpdatedDateTime
--				,DataId = @rDataTypeId
--			WHERE Id = @rId
--		END  

--	SELECT @lNextNumber AS Result
	
--	COMMIT TRANSACTION
--	END TRY
--	BEGIN CATCH 
--		ROLLBACK TRANSACTION
--		SELECT -1 AS Result
--	END CATCH	
--END
--GO
--PRINT N'Creating Procedure [sSetup].[pCustomFieldDelete]...';


--GO
--CREATE PROCEDURE [sSetup].[pCustomFieldDelete]

--@rId int,
--@rOrgId smallint=0,
--@rOrgGroupId int=0
--AS
--BEGIN
--	IF EXISTS(Select 1 from sSetup.tCustomFieldMaster CFM
--							INNER JOIN sHrm.tEmployeeCustomFieldMaster ECFM ON ECFM.CustomFieldMasterId=CFM.Id
--							INNER JOIN sHrm.tEmployeeCustomFieldData ECF ON ECF.EmployeeCustomFieldId=ECFM.Id
--							INNER JOIN sHrm.tEmployeeMaster EM ON EM.Id=ECF.EmployeeId
--							INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=EM.OrgId 
--						WHERE CFM.Id=@rId AND StatusId=7 AND OM.OrgGroupId=@rOrgGroupId
--							AND (ECF.FieldData <> '0' AND ECF.FieldData<>'' AND ECF.FieldData IS NOT NULL))
--	BEGIN
--		SELECT -101 As RESULT
--		RETURN
--	END

--	--IF EXISTS(Select 1 from sSetup.tCustomFieldMaster CFM
--	--						INNER JOIN sRecruitment.tVacancyCustomFieldMaster VCFM ON VCFM.CustomFieldMasterId=CFM.Id
--	--						INNER JOIN sHrm.tEmployeeCustomFieldData ECF ON ECF.EmployeeCustomFieldId=ECFM.Id
--	--						INNER JOIN sHrm.tEmployeeMaster EM ON EM.Id=ECF.EmployeeId
--	--						INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=EM.OrgId 
--	--					WHERE CFM.Id=@rId AND StatusId=7 AND OM.OrgGroupId=@rOrgGroupId
--	--						AND (ECF.FieldData <> '0' AND ECF.FieldData<>'' AND ECF.FieldData IS NOT NULL))
--	BEGIN
--		SELECT -102 As RESULT
--		RETURN
--	END

--	DELETE ECF FROM sSetup.tCustomFieldMaster CFM
--		INNER JOIN sHrm.tEmployeeCustomFieldMaster ECFM ON ECFM.CustomFieldMasterId=CFM.Id
--		INNER JOIN sHrm.tEmployeeCustomFieldData ECF ON ECF.EmployeeCustomFieldId=ECFM.Id
--		INNER JOIN sHrm.tEmployeeMaster EM ON EM.Id=ECF.EmployeeId
--		INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=EM.OrgId 
--	WHERE CFM.Id=@rId AND StatusId=1 AND OM.OrgGroupId=@rOrgGroupId

--	DELETE	FROM sHrm.tEmployeeCustomFieldMaster WHERE CustomFieldMasterId=@rId

--	DELETE FROM sSetup.tCustomFieldMaster WHERE Id=@rId
--	SELECT 1 AS RESULT
--END
--GO
--PRINT N'Creating Procedure [sSetup].[pCustomFieldDetailsGet]...';


--GO
--CREATE PROCEDURE [sSetup].[pCustomFieldDetailsGet]
--	@rOrgGroupId int

--WITH EXECUTE AS CALLER
--AS
--SET NOCOUNT ON

--BEGIN
--	SELECT
--		Id,
--		FieldGroup,
--		FieldTitle,
--		FieldTypeId,
--		FieldList,
--		IsEmployee,
--		IsVacancy,
--		ISNULL(DataId,0) AS DataId
--	FROM
--		sSetup.tCustomFieldMaster
--	WHERE
--		OrgGroupId = @rOrgGroupId

--END
--GO
PRINT N'Creating Procedure [sTransport].[pVTSTrackingDataAdd]...';


GO
CREATE PROCEDURE sTransport.pVTSTrackingDataAdd
	 
	@rVehicleId int,
	@rBusRouteId int,
	@rLatitude nvarchar(20),
	@rLongitude nvarchar(20),
	@rVTSVendorId int,
	@rVTSApiResponse nvarchar(2000)

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	

	IF NOT EXISTS(SELECT 1 FROM [sTransport].[tVTSTrackingData] WHERE VehicleId = @rVehicleId AND BusRouteId = @rBusRouteId)
	BEGIN
		INSERT INTO sTransport.tVTSTrackingData 
		(
			VehicleId
			,BusRouteId
			,Latitude
			,Longitude
			,CreatedDateTime
			,UpdatedDateTime
			,VTSVendorId
			,VTSResponse
			,APICallCount
		)
		VALUES 
		(
			@rVehicleId
			,@rBusRouteId
			,@rLatitude
			,@rLongitude
			,sCommon.fGetDate()
			,sCommon.fGetDate()
			,@rVTSVendorId
			,@rVTSApiResponse
			,1
		)

		SELECT 1 AS Result -- Save First record
	END
	ELSE
	BEGIN
		IF EXISTS(SELECT 1 FROM [sTransport].[tVTSTrackingData] WHERE VehicleId = @rVehicleId AND BusRouteId = @rBusRouteId)
		BEGIN
			DECLARE @lUpdatedDateTime DateTime, @lAPICallCount int
			SELECT @lUpdatedDateTime = UpdatedDateTime, @lAPICallCount = APICallCount FROM [sTransport].[tVTSTrackingData] WHERE VehicleId = @rVehicleId AND BusRouteId = @rBusRouteId

			UPDATE [sTransport].[tVTSTrackingData]
			SET 
				 Latitude = @rLatitude
				,Longitude = @rLongitude
				,UpdatedDateTime = GETDATE()
				,VTSResponse = @rVTSApiResponse
				,APICallCount = @lAPICallCount + 1
			WHERE 
				VehicleId = @rVehicleId
			AND BusRouteId = @rBusRouteId

			SELECT 11 AS Result -- Update Exists Record
		END
	END	

END
GO
PRINT N'Creating Procedure [sTransport].[pVTSTrackingDataGet]...';


GO
CREATE PROCEDURE sTransport.pVTSTrackingDataGet
	 
	@rVehicleId int,
	@rBusRouteId int,
	@rIsError smallint = 0

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	IF(@rIsError = 1)
	BEGIN
		IF EXISTS(SELECT 1 FROM sTransport.tVTSTrackingData WHERE VehicleId = @rVehicleId AND BusRouteId = @rBusRouteId)
		BEGIN
			SELECT
				VehicleId,
				BusRouteId,
				Latitude,
				Longitude,
				CreatedDateTime,
				UpdatedDateTime,
				VTSVendorId,
				VTSResponse,
				APICallCount
			FROM
				sTransport.tVTSTrackingData
			WHERE 
				VehicleId = @rVehicleId 
			AND BusRouteId = @rBusRouteId

			UPDATE sTransport.tVTSTrackingData SET UpdatedDateTime = GETDATE() WHERE VehicleId = @rVehicleId AND BusRouteId = @rBusRouteId
		END
	END
	ELSE
	BEGIN
		DECLARE @lUpdatedDateTime DateTime
		SELECT @lUpdatedDateTime = UpdatedDateTime FROM [sTransport].[tVTSTrackingData] WHERE VehicleId = @rVehicleId AND BusRouteId = @rBusRouteId

		IF EXISTS(SELECT 1 FROM sTransport.tVTSTrackingData WHERE VehicleId = @rVehicleId AND BusRouteId = @rBusRouteId AND DATEDIFF(MINUTE, GETDATE(), @lUpdatedDateTime) < 1)
		BEGIN
			SELECT
				VehicleId,
				BusRouteId,
				Latitude,
				Longitude,
				CreatedDateTime,
				UpdatedDateTime,
				VTSVendorId,
				VTSResponse,
				APICallCount
			FROM
				sTransport.tVTSTrackingData
			WHERE 
				VehicleId = @rVehicleId 
			AND BusRouteId = @rBusRouteId

			UPDATE sTransport.tVTSTrackingData SET UpdatedDateTime = GETDATE() WHERE VehicleId = @rVehicleId AND BusRouteId = @rBusRouteId 
		END
	END
END
GO
--PRINT N'Creating Procedure [sRecruitment].[pVacancyCustomFieldAdd]...';


--GO
--CREATE PROCEDURE [sRecruitment].[pVacancyCustomFieldAdd]
		 
--	--@rId int,
--	@rOrgId int,
--	@rFieldTitle nvarchar(500),
--	@rFieldTypeId tinyint,
--	@rFieldList nvarchar(500),
--	--@rIsFieldMandatory bit,
--	@rFieldGroup nvarchar(200),
--	--@rFieldOrder smallint,
--	@rCreatedUserId bigint,	
--	@rCreatedDateTime datetime,
--	@rUpdatedUserId bigint,
--	@rUpdatedDateTime datetime,
--	--@rIsObsolete int =0,
--	@rDataTypeId int = 1,
--	@rCustomFieldMasterId int = 0

--WITH EXECUTE AS CALLER
--AS
--SET NOCOUNT ON
--BEGIN

--	BEGIN TRY
--		BEGIN TRANSACTION	

--			DECLARE @lOrgGroupId int=0, @lIsVacancy bit = 0
--			SELECT @lOrgGroupId= OrgGroupId FROM sSetup.tOrganizationMAster WHERE Id = @rOrgId
--			select @lIsVacancy = IsVacancy From sSetup.tCustomFieldMaster WHERE FieldTitle=@rFieldTitle AND FieldGroup=@rFieldGroup AND DataId=@rDataTypeId AND OrgGroupId=@lOrgGroupId
 
--			IF(@lIsVacancy=1)	
--			BEGIN
--				IF NOT EXISTS(SELECT 2 FROM sRecruitment.tVacancyCustomFieldMaster WHERE CustomFieldMasterId=@rCustomFieldMasterId)
--				BEGIN
--					DECLARE @lNextNumber int
--					EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sRecruitment.tVacancyCustomFieldMaster', 0, 1, 1,@outNextNumber = @lNextNumber output
--					Print @lNextNumber
--					INSERT INTO sRecruitment.tVacancyCustomFieldMaster
--					(
--						Id
--					   ,FieldTitle
--					   ,FieldTypeId
--					   ,FieldList
--					   ,IsFieldMandatory
--					   ,FieldGroup
--					   ,FieldOrder
--					   ,CreatedUserId
--					   ,CreatedDateTime
--					   ,OrgGroupId
--					   ,IsObsolete
--					   ,DataId
--					   ,CustomFieldMasterId
--					)
--					VALUES
--					(
--					   @lNextNumber
--					  ,@rFieldTitle
--					  ,@rFieldTypeId
--					  ,@rFieldList
--					  ,0
--					  ,@rFieldGroup
--					  ,0
--					  ,@rCreatedUserId
--					  ,@rCreatedDateTime
--					  ,@lOrgGroupId
--					  ,0
--					  ,@rDataTypeId
--					  ,@rCustomFieldMasterId
--					)
--				END
--			--ELSE
--	--	 SELECT -101 As RESULT
--	--END
	 	 
--				ELSE
--				BEGIN
--				--SET @lNextNumber = @rId

--					UPDATE sRecruitment.tVacancyCustomFieldMaster
--					SET 
--						FieldTitle = @rFieldTitle
--						,FieldTypeId = @rFieldTypeId
--						,FieldList=@rFieldList
--						--,IsFieldMandatory=@rIsFieldMandatory
--						--,IsObsolete=@rIsObsolete
--						,FieldGroup=@rFieldGroup
--						--,FieldOrder=@rFieldOrder
--						,UpdatedUserId=@rUpdatedUserId
--						,UpdatedDateTime=@rUpdatedDateTime
--						,DataId = @rDataTypeId
--					WHERE CustomFieldMasterId=@rCustomFieldMasterId
	
	
--				END  
--			END
--			ELSE
--			BEGIN
--				IF EXISTS(SELECT 2 FROM sRecruitment.tVacancyCustomFieldMaster WHERE CustomFieldMasterId=@rCustomFieldMasterId)
--				BEGIN
--					DECLARE @lVacancyCustomFieldMasterId int=0
--					SELECT @lVacancyCustomFieldMasterId = Id FROM sRecruitment.tVacancyCustomFieldMaster WHERE CustomFieldMasterId=@rCustomFieldMasterId

--					EXEC sRecruitment.pVacancyCustomFieldDelete @lVacancyCustomFieldMasterId, @rOrgId, @lOrgGroupId
--				END
--			END	
--		COMMIT TRANSACTION
--	END TRY
--	BEGIN CATCH 
--		ROLLBACK TRANSACTION
--		SELECT -1 AS Result
--	END CATCH	
--END
--GO
PRINT N'Creating Extended Property [sTransport].[tVTSTrackingData].[MS_Description]...';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'', @level0type = N'SCHEMA', @level0name = N'sTransport', @level1type = N'TABLE', @level1name = N'tVTSTrackingData';


GO
PRINT N'Creating Extended Property [sTransport].[tVTSTrackingData].[VTSVendorId].[MS_Description]...';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Id will be same as Configuration Value of ''VTS.VendorName''
70    : Sahaj
71    : Track Now
102  : MSB
217  : Insta Telematics
218  : VTS Redirection', @level0type = N'SCHEMA', @level0name = N'sTransport', @level1type = N'TABLE', @level1name = N'tVTSTrackingData', @level2type = N'COLUMN', @level2name = N'VTSVendorId';

GO
PRINT N'Update complete.';


GO


GO
INSERT INTO [sSystem].[tVersionUpdate] ([VersionNumber],[VersionId],[Status],[UpdateScript],[StartDateTime],[EndDateTime],[OrgGroupId])
VALUES('24.06.15',240615,'END','Script',sCommon.fGetDate(),sCommon.fGetDate(),0)
GO

