Dairy Project Part 1.create table script

Use master
GO
Create Database [DairyProject]
GO
Use [DairyProject]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserMaster]
(
[UserId] [bigint] NOT NULL,
[Login] [nvarchar](30) NOT NULL,
[Empid] [bigint] NOT NULL,
[Password] [nvarchar](20) NULL,
[FirstName] [nvarchar](100) NULL,
[MiddleName] [nvarchar](100) NULL,
[LastName] [nvarchar](100) NULL,
[LoginTime] [datetime] NULL,
[LogoutTime] [datetime] NULL,
[LoginTimeValue] [bigint] NULL,
[SystemName] [nvarchar](50) NULL,
[isactive] [bit] NULL,
[Status] [bit] NULL,
[AddedBy] [bigint] NULL,
[UpdatedBy] [bigint] NULL
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Payment]
(
[PaymentId] [bigint] IDENTITY(1, 1) NOT NULL,
[Code] [bigint] NULL,
[CustomerID] [bigint] NOT NULL,
[Amount] [numeric](8, 2) NOT NULL,
[Remark] [nvarchar](300) NULL,
[Date_Time] [datetime] NOT NULL,
[Issettled] [bit] NULL,
[Iscancelled] [bit] NULL,
[AddedBy] [bigint] NULL,
[UpdatedBy] [bigint] NULL,
[Date] [datetime] NULL,
[Time] [datetime] NULL
)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DistrictMaster]
(
[Id] [bigint] IDENTITY(1, 1) NOT NULL,
[Code] [bigint] NULL,
[Description] [nvarchar](50) NULL,
[stateId] [bigint] NULL,
[Status] [bit] NULL,
[AddedBy] [bigint] NULL,
[UpdatedBy] [bigint] NULL,
[Date] [datetime] NULL,
[Time] [datetime] NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TalukaMaster]
(
[Id] [bigint] IDENTITY(1, 1) NOT NULL,
[Code] [bigint] NULL,
[Description] [nvarchar](50) NULL,
[DistrictId] [bigint] NULL,
[Status] [bit] NULL,
[AddedBy] [bigint] NULL,
[UpdatedBy] [bigint] NULL,
[Date] [datetime] NULL,
[Time] [datetime] NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CityMaster]
(
[Id] [bigint] IDENTITY(1, 1) NOT NULL,
[Code] [bigint] NULL,
[Description] [nvarchar](50) NULL,
[TalukaId] [bigint] NULL,
[Status] [bit] NULL,
[AddedBy] [bigint] NULL,
[UpdatedBy] [bigint] NULL,
[Date] [datetime] NULL,
[Time] [datetime] NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
)
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SaluationMaster]
(
[Id] [bigint] IDENTITY(1, 1) NOT NULL,
[code] [nvarchar](50) NOT NULL,
[Description] [nvarchar](50) NULL,
[GenderID] [bigint] NULL,
[Status] [bit] NULL,
[AddedBy] [bigint] NULL,
[UpdatedBy] [bigint] NULL,
[Date] [datetime] NULL,
[Time] [datetime] NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
)

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Advance]
(
[AdvanceId] [bigint] IDENTITY(1, 1) NOT NULL,
[Code] [bigint] NULL,
[CustomerID] [bigint] NOT NULL,
[Amount] [numeric](8, 2) NOT NULL,
[Remark] [nvarchar](300) NULL,
[Date_Time] [datetime] NOT NULL,
[Issettled] [bit] NULL,
[Iscancelled] [bit] NULL,
[AddedBy] [bigint] NULL,
[UpdatedBy] [bigint] NULL,
[Date] [datetime] NULL,
[Time] [datetime] NULL
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Advance_Summery]
(
[Id] [bigint] IDENTITY(1, 1) NOT NULL,
[CustomerID] [bigint] NOT NULL,
[AdvanceAmount] [money] NOT NULL
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BillProcess_CustomerPayment]
(
[Id] [bigint] IDENTITY(1, 1) NOT NULL,
[CustomerID] [bigint] NOT NULL,
[LiterQty] [numeric](8, 2) NULL,
[PreviousBalance] [money] NULL,
[CollectionAmount] [money] NULL,
[AdvanceAmount] [money] NULL,
[SupplimentAmount] [money] NULL,
[PayableToCustomer] [money] NULL,
[PaidAmt] [money] NULL,
[BalanceAmount] [money] NULL,
[ProcessDate] [datetime] NOT NULL,
[FromDate] [datetime] NULL,
[ToDate] [datetime] NULL,
[Issettled] [bit] NULL,
[Iscancelled] [bit] NULL,
[AddedBy] [bigint] NULL,
[UpdatedBy] [bigint] NULL,
[Date] [datetime] NULL,
[Time] [datetime] NULL
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BillProcess_Summery]
(
[Id] [bigint] IDENTITY(1, 1) NOT NULL,
[CustomerId] [bigint] NULL,
[LiterQty] [numeric](8, 2) NULL,
[CollectionAmount] [money] NULL,
[AdvanceAmount] [money] NULL,
[SupplimentAmount] [money] NULL,
[PayableToCustomer] [money] NULL,
[ProcessDate] [datetime] NULL,
[FromDate] [datetime] NOT NULL,
[ToDate] [datetime] NOT NULL
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CategoryMaster]
(
[Id] [bigint] IDENTITY(1, 1) NOT NULL,
[Code] [bigint] NULL,
[Description] [nvarchar](100) NULL,
[Status] [bit] NULL,
[AddedBy] [bigint] NULL,
[UpdatedBy] [bigint] NULL,
[Date] [datetime] NULL,
[Time] [datetime] NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Configuration]
(
[ID] [bigint] IDENTITY(1, 1) NOT NULL,
[Saluationid] [bigint] NULL,
[Genderid] [bigint] NULL,
[Countryid] [bigint] NULL,
[Stateid] [bigint] NULL,
[Districtid] [bigint] NULL,
[Talukaid] [bigint] NULL,
[Cityid] [bigint] NULL,
[Status] [bit] NULL,
[AddedBy] [bigint] NULL,
[UpdatedBy] [bigint] NULL,
[Date] [datetime] NULL,
[Time] [datetime] NULL,
PRIMARY KEY CLUSTERED ([ID] ASC)
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CountryMaster]
(
[id] [bigint] IDENTITY(1, 1) NOT NULL,
[Code] [bigint] NULL,
[Description] [nvarchar](50) NULL,
[Status] [bit] NULL,
[AddedBy] [bigint] NULL,
[UpdatedBy] [bigint] NULL,
[Date] [datetime] NULL,
[Time] [datetime] NULL,
PRIMARY KEY CLUSTERED ([id] ASC)
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CustomerLedger]
(
[Id] [bigint] IDENTITY(1, 1) NOT NULL,
[CustomerID] [bigint] NOT NULL,
[Label] [nvarchar](100) NULL,
[InAmt] [money] NULL,
[OutAmt] [money] NULL,
[Issettled] [bit] NULL,
[Iscancelled] [bit] NULL,
[Trans_advance_Supp_id] [bigint] NULL,
[Trans_advance_Supp] [bigint] NULL,
[Addedon] [datetime] NULL,
[Date] [datetime] NULL,
[Time] [datetime] NULL
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DailyMilkCollection]
(
[TransId] [bigint] IDENTITY(1, 1) NOT NULL,

[Code] [bigint] NULL,
[CustomerID] [bigint] NOT NULL,
[FATValue] [numeric](8, 2) NOT NULL,
[SNFValue] [numeric](8, 2) NOT NULL,
[Quantity] [numeric](8, 2) NOT NULL,
[Rate] [numeric](8, 2) NOT NULL,
[Amount] [money] NOT NULL,
[MilkTimeID] [bigint] NOT NULL,
[MilkTypeID] [bigint] NOT NULL,
[Date_Time] [datetime] NOT NULL,
[Issettled] [bit] NULL,
[Iscancelled] [bit] NULL,
[AddedBy] [bigint] NULL,
[UpdatedBy] [bigint] NULL,
[Date] [datetime] NULL,
[Time] [datetime] NULL
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DayEndCollection]
(
[TransId] [bigint] IDENTITY(1, 1) NOT NULL,
[Code] [bigint] NULL,
[FATValue] [numeric](8, 2) NOT NULL,
[SNFValue] [numeric](8, 2) NOT NULL,
[Quantity] [numeric](8, 2) NOT NULL,
[Rate] [numeric](8, 2) NOT NULL,
[Amount] [numeric](8, 2) NOT NULL,
[MilkTimeID] [bigint] NOT NULL,
[MilkTypeID] [bigint] NOT NULL,
[Date_Time] [datetime] NOT NULL,
[Issettled] [bit] NULL,
[Iscancelled] [bit] NULL,
[AddedBy] [bigint] NULL,
[UpdatedBy] [bigint] NULL,
[Date] [datetime] NULL,
[Time] [datetime] NULL
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DMC_Summery]
(
[Id] [bigint] IDENTITY(1, 1) NOT NULL,
[CustomerID] [bigint] NOT NULL,
[CollectionAmount] [money] NOT NULL,
[LiterQty] [numeric](8, 2) NOT NULL
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FATMaster]
(
[Id] [bigint] IDENTITY(1, 1) NOT NULL,
[code] [nvarchar](50) NOT NULL,
[FAT_Value] [numeric](8, 2) NULL,
[Status] [bit] NULL,
[AddedBy] [bigint] NULL,
[UpdatedBy] [bigint] NULL,
[Date] [datetime] NULL,
[Time] [datetime] NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GenderMaster]
(
[Id] [bigint] IDENTITY(1, 1) NOT NULL,
[Code] [bigint] NULL,
[Description] [nvarchar](50) NULL,
[Status] [bit] NULL,
[AddedBy] [bigint] NULL,
[UpdatedBy] [bigint] NULL,
[Date] [datetime] NULL,
[Time] [datetime] NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MenuMaster]
(
[MenuId] [bigint] NOT NULL,
[MenuName] [nvarchar](300) NULL,
[MenuKey] [nvarchar](300) NULL,
[ParentId] [bigint] NULL,
[MenuOrder] [bigint] NULL,
[Level] [tinyint] NULL
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MilkTimeMaster]
(
[Id] [bigint] IDENTITY(1, 1) NOT NULL,
[Code] [bigint] NULL,
[Description] [nvarchar](50) NULL,
[Status] [bit] NULL,
[AddedBy] [bigint] NULL,
[UpdatedBy] [bigint] NULL,
[Date] [datetime] NULL,
[Time] [datetime] NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MilkTypeMaster]
(
[Id] [bigint] IDENTITY(1, 1) NOT NULL,
[Code] [bigint] NULL,
[Description] [nvarchar](50) NULL,
[Status] [bit] NULL,
[AddedBy] [bigint] NULL,
[UpdatedBy] [bigint] NULL,
[Date] [datetime] NULL,
[Time] [datetime] NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RateMaster]
(
[Id] [bigint] IDENTITY(1, 1) NOT NULL,

[Code] [bigint] NULL,
[FATID] [bigint] NOT NULL,
[SNFID] [bigint] NOT NULL,
[MilkTypeID] [bigint] NOT NULL,
[Rate] [numeric](8, 2) NOT NULL,
[Status] [bit] NULL,
[AddedBy] [bigint] NULL,
[UpdatedBy] [bigint] NULL,
[Date] [datetime] NULL,
[Time] [datetime] NULL
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Registration]
(
[id] [bigint] IDENTITY(1, 1) NOT NULL,
[Code] [bigint] NULL,
[Saluationid] [bigint] NULL,
[FirstName] [nvarchar](100) NULL,
[MiddleName] [nvarchar](100) NULL,
[LastName] [nvarchar](100) NULL,
[Genderid] [bigint] NULL,
[MilkTimeId] [bigint] NULL,
[MilkTypeId] [bigint] NULL,
[StartDate] [datetime] NULL,
[openingBalance] [numeric](8, 2) NULL,
[ContactNo] [nvarchar](50) NULL,
[Address] [nvarchar](300) NULL,
[Countryid] [bigint] NULL,
[Stateid] [bigint] NULL,
[Districtid] [bigint] NULL,
[Talukaid] [bigint] NULL,
[Cityid] [bigint] NULL,
[MobileNo] [nvarchar](50) NULL,
[PhoneNo] [nvarchar](50) NULL,
[Emailid] [nvarchar](50) NULL,
[Status] [bit] NULL,
[AddedBy] [bigint] NULL,
[UpdatedBy] [bigint] NULL,
[Date] [datetime] NULL,
[Time] [datetime] NULL,
PRIMARY KEY CLUSTERED ([id] ASC)
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SNFMaster]
(
[Id] [bigint] IDENTITY(1, 1) NOT NULL,
[code] [nvarchar](50) NOT NULL,
[SNF_Value] [numeric](8, 2) NULL,
[Status] [bit] NULL,
[AddedBy] [bigint] NULL,
[UpdatedBy] [bigint] NULL,
[Date] [datetime] NULL,
[Time] [datetime] NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StateMaster]
(
[Id] [bigint] IDENTITY(1, 1) NOT NULL,
[Code] [bigint] NULL,
[Description] [nvarchar](50) NULL,
[CountryId] [bigint] NULL,
[Status] [bit] NULL,
[AddedBy] [bigint] NULL,
[UpdatedBy] [bigint] NULL,
[Date] [datetime] NULL,
[Time] [datetime] NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SubCategoryMaster]
(
[Id] [bigint] IDENTITY(1, 1) NOT NULL,
[code] [nvarchar](50) NOT NULL,
[Description] [nvarchar](100) NULL,
[CategoryID] [bigint] NULL,
[Status] [bit] NULL,
[AddedBy] [bigint] NULL,
[UpdatedBy] [bigint] NULL,
[Date] [datetime] NULL,
[Time] [datetime] NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Suppliment]
(
[SupplimentId] [bigint] IDENTITY(1, 1) NOT NULL,
[Code] [bigint] NULL,
[CustomerID] [bigint] NOT NULL,
[CategoryID] [bigint] NOT NULL,
[SubCategoryID] [bigint] NOT NULL,
[Quantity] [numeric](8, 2) NOT NULL,
[Rate] [numeric](8, 2) NOT NULL,
[Amount] [numeric](8, 2) NOT NULL,
[Remark] [nvarchar](300) NULL,
[Date_Time] [datetime] NOT NULL,
[Issettled] [bit] NULL,
[Iscancelled] [bit] NULL,
[AddedBy] [bigint] NULL,
[UpdatedBy] [bigint] NULL,
[Date] [datetime] NULL,
[Time] [datetime] NULL
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Suppliment_Summery]
(
[Id] [bigint] IDENTITY(1, 1) NOT NULL,
[CustomerID] [bigint] NOT NULL,
[SupplimentAmount] [money] NOT NULL
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserMenuMaster]
(
[Id] [bigint] NOT NULL,
[UserId] [bigint] NOT NULL,
[MenuId] [bigint] NOT NULL,
[RightStatus] [bit] NULL
)
GO
ALTER TABLE [dbo].[Advance] ADD DEFAULT ((0)) FOR [Issettled]
GO
ALTER TABLE [dbo].[Advance] ADD DEFAULT ((0)) FOR [Iscancelled]
GO
ALTER TABLE [dbo].[Advance]
ADD
DEFAULT (CONVERT([date], getdate(), 0)) FOR [Date]
GO
ALTER TABLE [dbo].[Advance]
ADD
DEFAULT (CONVERT([time], getdate(), 0)) FOR [Time]
GO
ALTER TABLE [dbo].[BillProcess_CustomerPayment]
ADD
DEFAULT ((1)) FOR [Issettled]
GO
ALTER TABLE [dbo].[BillProcess_CustomerPayment]
ADD
DEFAULT ((0)) FOR [Iscancelled]
GO
ALTER TABLE [dbo].[BillProcess_CustomerPayment]
ADD
DEFAULT (CONVERT([date], getdate(), 0)) FOR [Date]
GO
ALTER TABLE [dbo].[BillProcess_CustomerPayment]
ADD
DEFAULT (CONVERT([time], getdate(), 0)) FOR [Time]
GO

ALTER TABLE [dbo].[CategoryMaster]
ADD
DEFAULT (CONVERT([date], getdate(), 0)) FOR [Date]
GO
ALTER TABLE [dbo].[CategoryMaster]
ADD
DEFAULT (CONVERT([time], getdate(), 0)) FOR [Time]
GO
ALTER TABLE [dbo].[CityMaster]
ADD
DEFAULT (CONVERT([date], getdate(), 0)) FOR [Date]
GO
ALTER TABLE [dbo].[CityMaster]
ADD
DEFAULT (CONVERT([time], getdate(), 0)) FOR [Time]
GO
ALTER TABLE [dbo].[CountryMaster]
ADD
DEFAULT (CONVERT([date], getdate(), 0)) FOR [Date]
GO
ALTER TABLE [dbo].[CountryMaster]
ADD
DEFAULT (CONVERT([time], getdate(), 0)) FOR [Time]
GO
ALTER TABLE [dbo].[CustomerLedger] ADD DEFAULT ((0)) FOR [Issettled]
GO
ALTER TABLE [dbo].[CustomerLedger] ADD DEFAULT ((0)) FOR [Iscancelled]
GO
ALTER TABLE [dbo].[CustomerLedger]
ADD
DEFAULT (CONVERT([date], getdate(), 0)) FOR [Date]
GO
ALTER TABLE [dbo].[CustomerLedger]
ADD
DEFAULT (CONVERT([time], getdate(), 0)) FOR [Time]
GO
ALTER TABLE [dbo].[DailyMilkCollection]
ADD
DEFAULT ((0)) FOR [Issettled]
GO
ALTER TABLE [dbo].[DailyMilkCollection]
ADD
DEFAULT ((0)) FOR [Iscancelled]
GO
ALTER TABLE [dbo].[DailyMilkCollection]
ADD
DEFAULT (CONVERT([date], getdate(), 0)) FOR [Date]
GO
ALTER TABLE [dbo].[DailyMilkCollection]
ADD
DEFAULT (CONVERT([time], getdate(), 0)) FOR [Time]
GO
ALTER TABLE [dbo].[DayEndCollection] ADD DEFAULT ((0)) FOR [Issettled]
GO
ALTER TABLE [dbo].[DayEndCollection] ADD DEFAULT ((0)) FOR [Iscancelled]
GO
ALTER TABLE [dbo].[DayEndCollection]
ADD
DEFAULT (CONVERT([date], getdate(), 0)) FOR [Date]
GO
ALTER TABLE [dbo].[DayEndCollection]
ADD
DEFAULT (CONVERT([time], getdate(), 0)) FOR [Time]
GO
ALTER TABLE [dbo].[DistrictMaster]
ADD
DEFAULT (CONVERT([date], getdate(), 0)) FOR [Date]
GO
ALTER TABLE [dbo].[DistrictMaster]
ADD
DEFAULT (CONVERT([time], getdate(), 0)) FOR [Time]
GO
ALTER TABLE [dbo].[FATMaster]
ADD
DEFAULT (CONVERT([date], getdate(), 0)) FOR [Date]
GO
ALTER TABLE [dbo].[FATMaster]
ADD
DEFAULT (CONVERT([time], getdate(), 0)) FOR [Time]
GO

ALTER TABLE [dbo].[GenderMaster]
ADD
DEFAULT (CONVERT([date], getdate(), 0)) FOR [Date]
GO
ALTER TABLE [dbo].[GenderMaster]
ADD
DEFAULT (CONVERT([time], getdate(), 0)) FOR [Time]
GO
ALTER TABLE [dbo].[MilkTimeMaster]
ADD
DEFAULT (CONVERT([date], getdate(), 0)) FOR [Date]
GO
ALTER TABLE [dbo].[MilkTimeMaster]
ADD
DEFAULT (CONVERT([time], getdate(), 0)) FOR [Time]
GO
ALTER TABLE [dbo].[MilkTypeMaster]
ADD
DEFAULT (CONVERT([date], getdate(), 0)) FOR [Date]
GO
ALTER TABLE [dbo].[MilkTypeMaster]
ADD
DEFAULT (CONVERT([time], getdate(), 0)) FOR [Time]
GO
ALTER TABLE [dbo].[Payment] ADD DEFAULT ((1)) FOR [Issettled]
GO
ALTER TABLE [dbo].[Payment] ADD DEFAULT ((0)) FOR [Iscancelled]
GO
ALTER TABLE [dbo].[Payment]
ADD
DEFAULT (CONVERT([date], getdate(), 0)) FOR [Date]
GO
ALTER TABLE [dbo].[Payment]
ADD
DEFAULT (CONVERT([time], getdate(), 0)) FOR [Time]
GO
ALTER TABLE [dbo].[Registration] ADD DEFAULT ((1)) FOR [AddedBy]
GO
ALTER TABLE [dbo].[Registration] ADD DEFAULT ((1)) FOR [UpdatedBy]
GO
ALTER TABLE [dbo].[Registration]
ADD
DEFAULT (CONVERT([date], getdate(), 0)) FOR [Date]
GO
ALTER TABLE [dbo].[Registration]
ADD
DEFAULT (CONVERT([time], getdate(), 0)) FOR [Time]
GO
ALTER TABLE [dbo].[SaluationMaster]
ADD
DEFAULT (CONVERT([date], getdate(), 0)) FOR [Date]
GO
ALTER TABLE [dbo].[SaluationMaster]
ADD
DEFAULT (CONVERT([time], getdate(), 0)) FOR [Time]
GO
ALTER TABLE [dbo].[SNFMaster]
ADD
DEFAULT (CONVERT([date], getdate(), 0)) FOR [Date]
GO
ALTER TABLE [dbo].[SNFMaster]
ADD
DEFAULT (CONVERT([time], getdate(), 0)) FOR [Time]
GO
ALTER TABLE [dbo].[StateMaster]
ADD
DEFAULT (CONVERT([date], getdate(), 0)) FOR [Date]
GO
ALTER TABLE [dbo].[StateMaster]
ADD
DEFAULT (CONVERT([time], getdate(), 0)) FOR [Time]
GO
ALTER TABLE [dbo].[SubCategoryMaster]
ADD
DEFAULT (CONVERT([date], getdate(), 0)) FOR [Date]
GO
ALTER TABLE [dbo].[SubCategoryMaster]
ADD
DEFAULT (CONVERT([time], getdate(), 0)) FOR [Time]
GO
ALTER TABLE [dbo].[Suppliment] ADD DEFAULT ((0)) FOR [Issettled]
GO
ALTER TABLE [dbo].[Suppliment] ADD DEFAULT ((0)) FOR [Iscancelled]
GO
ALTER TABLE [dbo].[Suppliment]
ADD
DEFAULT (CONVERT([date], getdate(), 0)) FOR [Date]
GO
ALTER TABLE [dbo].[Suppliment]
ADD
DEFAULT (CONVERT([time], getdate(), 0)) FOR [Time]
GO
ALTER TABLE [dbo].[TalukaMaster]
ADD
DEFAULT (CONVERT([date], getdate(), 0)) FOR [Date]
GO
ALTER TABLE [dbo].[TalukaMaster]
ADD
DEFAULT (CONVERT([time], getdate(), 0)) FOR [Time]
GO
ALTER TABLE [dbo].[Advance] WITH CHECK
ADD CONSTRAINT [FK_Advance_Registration]
FOREIGN KEY ([CustomerID])
REFERENCES [dbo].[Registration] ([id])
GO
ALTER TABLE [dbo].[Advance] CHECK CONSTRAINT [FK_Advance_Registration]
GO
ALTER TABLE [dbo].[Advance_Summery] WITH CHECK
ADD CONSTRAINT [FK_Advance_Summery_Registration]
FOREIGN KEY ([CustomerID])
REFERENCES [dbo].[Registration] ([id])
GO
ALTER TABLE [dbo].[Advance_Summery] CHECK CONSTRAINT [FK_Advance_Summery_Registration]
GO
ALTER TABLE [dbo].[BillProcess_CustomerPayment] WITH CHECK
ADD CONSTRAINT [FK_BillProcess_CustomerPayment_Registration]
FOREIGN KEY ([CustomerID])
REFERENCES [dbo].[Registration] ([id])
GO
ALTER TABLE [dbo].[BillProcess_CustomerPayment] CHECK CONSTRAINT [FK_BillProcess_CustomerPayment_Registration]
GO
ALTER TABLE [dbo].[BillProcess_Summery] WITH CHECK
ADD CONSTRAINT [FK_BillProcess_Summery_Registration]
FOREIGN KEY ([CustomerId])
REFERENCES [dbo].[Registration] ([id])
GO
ALTER TABLE [dbo].[BillProcess_Summery] CHECK CONSTRAINT [FK_BillProcess_Summery_Registration]
GO
ALTER TABLE [dbo].[CityMaster] WITH CHECK
ADD CONSTRAINT [FK_CityMaster_TalukaMaster]
FOREIGN KEY ([TalukaId])
REFERENCES [dbo].[TalukaMaster] ([Id])
GO
ALTER TABLE [dbo].[CityMaster] CHECK CONSTRAINT [FK_CityMaster_TalukaMaster]
GO
ALTER TABLE [dbo].[Configuration] WITH CHECK
ADD CONSTRAINT [FK_Configuration_CityMaster]
FOREIGN KEY ([Cityid])
REFERENCES [dbo].[CityMaster] ([Id])
GO
ALTER TABLE [dbo].[Configuration] CHECK CONSTRAINT [FK_Configuration_CityMaster]
GO
ALTER TABLE [dbo].[Configuration] WITH CHECK
ADD CONSTRAINT [FK_Configuration_CountryMaster]
FOREIGN KEY ([Countryid])
REFERENCES [dbo].[CountryMaster] ([id])
GO
ALTER TABLE [dbo].[Configuration] CHECK CONSTRAINT [FK_Configuration_CountryMaster]
GO
ALTER TABLE [dbo].[Configuration] WITH CHECK
ADD CONSTRAINT [FK_Configuration_DistrictMaster]
FOREIGN KEY ([Districtid])
REFERENCES [dbo].[DistrictMaster] ([Id])
GO
ALTER TABLE [dbo].[Configuration] CHECK CONSTRAINT [FK_Configuration_DistrictMaster]
GO
ALTER TABLE [dbo].[Configuration] WITH CHECK
ADD CONSTRAINT [FK_Configuration_GenderMaster]
FOREIGN KEY ([Genderid])
REFERENCES [dbo].[GenderMaster] ([Id])
GO
ALTER TABLE [dbo].[Configuration] CHECK CONSTRAINT [FK_Configuration_GenderMaster]
GO
ALTER TABLE [dbo].[Configuration] WITH CHECK
ADD CONSTRAINT [FK_Configuration_SaluationMaster]
FOREIGN KEY ([Saluationid])
REFERENCES [dbo].[SaluationMaster] ([Id])
GO
ALTER TABLE [dbo].[Configuration] CHECK CONSTRAINT [FK_Configuration_SaluationMaster]
GO
ALTER TABLE [dbo].[Configuration] WITH CHECK
ADD CONSTRAINT [FK_Configuration_StateMaster]
FOREIGN KEY ([Stateid])
REFERENCES [dbo].[StateMaster] ([Id])
GO
ALTER TABLE [dbo].[Configuration] CHECK CONSTRAINT [FK_Configuration_StateMaster]
GO
ALTER TABLE [dbo].[Configuration] WITH CHECK
ADD CONSTRAINT [FK_Configuration_TalukaMaster]
FOREIGN KEY ([Talukaid])
REFERENCES [dbo].[TalukaMaster] ([Id])
GO
ALTER TABLE [dbo].[Configuration] CHECK CONSTRAINT [FK_Configuration_TalukaMaster]
GO
ALTER TABLE [dbo].[CustomerLedger] WITH CHECK
ADD CONSTRAINT [FK_CustomerLedger_Registration]
FOREIGN KEY ([CustomerID])
REFERENCES [dbo].[Registration] ([id])
GO
ALTER TABLE [dbo].[CustomerLedger] CHECK CONSTRAINT [FK_CustomerLedger_Registration]
GO
ALTER TABLE [dbo].[DailyMilkCollection] WITH CHECK
ADD CONSTRAINT [FK_DailyMilkCollection_MilkTimeMaster]
FOREIGN KEY ([MilkTimeID])
REFERENCES [dbo].[MilkTimeMaster] ([Id])
GO
ALTER TABLE [dbo].[DailyMilkCollection] CHECK CONSTRAINT [FK_DailyMilkCollection_MilkTimeMaster]
GO
ALTER TABLE [dbo].[DailyMilkCollection] WITH CHECK
ADD CONSTRAINT [FK_DailyMilkCollection_MilkTypeMaster]
FOREIGN KEY ([MilkTypeID])
REFERENCES [dbo].[MilkTypeMaster] ([Id])
GO
ALTER TABLE [dbo].[DailyMilkCollection] CHECK CONSTRAINT [FK_DailyMilkCollection_MilkTypeMaster]
GO
ALTER TABLE [dbo].[DailyMilkCollection] WITH CHECK
ADD CONSTRAINT [FK_DailyMilkCollection_Registration]
FOREIGN KEY ([CustomerID])
REFERENCES [dbo].[Registration] ([id])
GO
ALTER TABLE [dbo].[DailyMilkCollection] CHECK CONSTRAINT [FK_DailyMilkCollection_Registration]
GO
ALTER TABLE [dbo].[DayEndCollection] WITH CHECK
ADD CONSTRAINT [FK_DayEndCollection_MilkTimeMaster]
FOREIGN KEY ([MilkTimeID])
REFERENCES [dbo].[MilkTimeMaster] ([Id])
GO
ALTER TABLE [dbo].[DayEndCollection] CHECK CONSTRAINT [FK_DayEndCollection_MilkTimeMaster]
GO
ALTER TABLE [dbo].[DayEndCollection] WITH CHECK
ADD CONSTRAINT [FK_DayEndCollection_MilkTypeMaster]
FOREIGN KEY ([MilkTypeID])
REFERENCES [dbo].[MilkTypeMaster] ([Id])
GO
ALTER TABLE [dbo].[DayEndCollection] CHECK CONSTRAINT [FK_DayEndCollection_MilkTypeMaster]
GO
ALTER TABLE [dbo].[DistrictMaster] WITH CHECK
ADD CONSTRAINT [FK_DistrictMaster_StateMaster]
FOREIGN KEY ([stateId])
REFERENCES [dbo].[StateMaster] ([Id])
GO
ALTER TABLE [dbo].[DistrictMaster] CHECK CONSTRAINT [FK_DistrictMaster_StateMaster]
GO
ALTER TABLE [dbo].[DMC_Summery] WITH CHECK
ADD CONSTRAINT [FK_DMC_Summery_Registration]
FOREIGN KEY ([CustomerID])
REFERENCES [dbo].[Registration] ([id])
GO
ALTER TABLE [dbo].[DMC_Summery] CHECK CONSTRAINT [FK_DMC_Summery_Registration]
GO
ALTER TABLE [dbo].[Payment] WITH CHECK
ADD CONSTRAINT [FK_Payment_Registration]
FOREIGN KEY ([CustomerID])
REFERENCES [dbo].[Registration] ([id])
GO
ALTER TABLE [dbo].[Payment] CHECK CONSTRAINT [FK_Payment_Registration]
GO
ALTER TABLE [dbo].[RateMaster] WITH CHECK
ADD CONSTRAINT [FK_RateMaster_FATMaster]
FOREIGN KEY ([FATID])
REFERENCES [dbo].[FATMaster] ([Id])
GO
ALTER TABLE [dbo].[RateMaster] CHECK CONSTRAINT [FK_RateMaster_FATMaster]
GO
ALTER TABLE [dbo].[RateMaster] WITH CHECK
ADD CONSTRAINT [FK_RateMaster_SNFMaster]
FOREIGN KEY ([SNFID])
REFERENCES [dbo].[SNFMaster] ([Id])
GO
ALTER TABLE [dbo].[RateMaster] CHECK CONSTRAINT [FK_RateMaster_SNFMaster]
GO
ALTER TABLE [dbo].[Registration] WITH CHECK
ADD CONSTRAINT [FK_Registration_CityMaster]
FOREIGN KEY ([Cityid])
REFERENCES [dbo].[CityMaster] ([Id])
GO
ALTER TABLE [dbo].[Registration] CHECK CONSTRAINT [FK_Registration_CityMaster]
GO
ALTER TABLE [dbo].[Registration] WITH CHECK
ADD CONSTRAINT [FK_Registration_CountryMaster]
FOREIGN KEY ([Countryid])
REFERENCES [dbo].[CountryMaster] ([id])
GO
ALTER TABLE [dbo].[Registration] CHECK CONSTRAINT [FK_Registration_CountryMaster]
GO
ALTER TABLE [dbo].[Registration] WITH CHECK
ADD CONSTRAINT [FK_Registration_DistrictMaster]
FOREIGN KEY ([Districtid])
REFERENCES [dbo].[DistrictMaster] ([Id])
GO
ALTER TABLE [dbo].[Registration] CHECK CONSTRAINT [FK_Registration_DistrictMaster]
GO
ALTER TABLE [dbo].[Registration] WITH CHECK
ADD CONSTRAINT [FK_Registration_GenderMaster]
FOREIGN KEY ([Genderid])
REFERENCES [dbo].[GenderMaster] ([Id])
GO
ALTER TABLE [dbo].[Registration] CHECK CONSTRAINT [FK_Registration_GenderMaster]
GO
ALTER TABLE [dbo].[Registration] WITH CHECK
ADD CONSTRAINT [FK_Registration_MilkTimeMaster]
FOREIGN KEY ([MilkTimeId])
REFERENCES [dbo].[MilkTimeMaster] ([Id])
GO
ALTER TABLE [dbo].[Registration] CHECK CONSTRAINT [FK_Registration_MilkTimeMaster]
GO
ALTER TABLE [dbo].[Registration] WITH CHECK
ADD CONSTRAINT [FK_Registration_MilkTypeMaster]
FOREIGN KEY ([MilkTypeId])
REFERENCES [dbo].[MilkTypeMaster] ([Id])
GO
ALTER TABLE [dbo].[Registration] CHECK CONSTRAINT [FK_Registration_MilkTypeMaster]
GO
ALTER TABLE [dbo].[Registration] WITH CHECK
ADD CONSTRAINT [FK_Registration_SaluationMaster]
FOREIGN KEY ([Saluationid])
REFERENCES [dbo].[SaluationMaster] ([Id])
GO
ALTER TABLE [dbo].[Registration] CHECK CONSTRAINT [FK_Registration_SaluationMaster]
GO
ALTER TABLE [dbo].[Registration] WITH CHECK
ADD CONSTRAINT [FK_Registration_StateMaster]
FOREIGN KEY ([Stateid])
REFERENCES [dbo].[StateMaster] ([Id])
GO
ALTER TABLE [dbo].[Registration] CHECK CONSTRAINT [FK_Registration_StateMaster]
GO
ALTER TABLE [dbo].[Registration] WITH CHECK
ADD CONSTRAINT [FK_Registration_TalukaMaster]
FOREIGN KEY ([Talukaid])
REFERENCES [dbo].[TalukaMaster] ([Id])
GO
ALTER TABLE [dbo].[Registration] CHECK CONSTRAINT [FK_Registration_TalukaMaster]
GO
ALTER TABLE [dbo].[StateMaster] WITH CHECK
ADD CONSTRAINT [FK_StateMaster_CountryMaster]
FOREIGN KEY ([CountryId])
REFERENCES [dbo].[CountryMaster] ([id])
GO
ALTER TABLE [dbo].[StateMaster] CHECK CONSTRAINT [FK_StateMaster_CountryMaster]
GO
ALTER TABLE [dbo].[Suppliment] WITH CHECK
ADD CONSTRAINT [FK_Suppliment_CategoryMaster]
FOREIGN KEY ([CategoryID])
REFERENCES [dbo].[CategoryMaster] ([Id])
GO
ALTER TABLE [dbo].[Suppliment] CHECK CONSTRAINT [FK_Suppliment_CategoryMaster]
GO
ALTER TABLE [dbo].[Suppliment] WITH CHECK
ADD CONSTRAINT [FK_Suppliment_Registration]
FOREIGN KEY ([CustomerID])
REFERENCES [dbo].[Registration] ([id])
GO
ALTER TABLE [dbo].[Suppliment] CHECK CONSTRAINT [FK_Suppliment_Registration]
GO
ALTER TABLE [dbo].[Suppliment] WITH CHECK
ADD CONSTRAINT [FK_Suppliment_SubCategoryMaster]
FOREIGN KEY ([SubCategoryID])
REFERENCES [dbo].[SubCategoryMaster] ([Id])
GO
ALTER TABLE [dbo].[Suppliment] CHECK CONSTRAINT [FK_Suppliment_SubCategoryMaster]
GO
ALTER TABLE [dbo].[Suppliment_Summery] WITH CHECK
ADD CONSTRAINT [FK_Suppliment_Summery_Registration]
FOREIGN KEY ([CustomerID])
REFERENCES [dbo].[Registration] ([id])
GO
ALTER TABLE [dbo].[Suppliment_Summery] CHECK CONSTRAINT [FK_Suppliment_Summery_Registration]
GO
ALTER TABLE [dbo].[TalukaMaster] WITH CHECK
ADD CONSTRAINT [FK_TalukaMaster_DistrictMaster]
FOREIGN KEY ([DistrictId])
REFERENCES [dbo].[DistrictMaster] ([Id])
GO
ALTER TABLE [dbo].[TalukaMaster] CHECK CONSTRAINT [FK_TalukaMaster_DistrictMaster]
GO
-------------Stored Procedure list----------

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create or Alter PROCEDURE [dbo].[insert_DailyMilkCollection_1]
(
@Code_2 bigint,
@CustomerID_3 bigint,
@FATvalue_4 Numeric(8, 2),
@SNFvalue_5 Numeric(8, 2),
@Quantity_6 Numeric(8, 2),
@Rate_7 Numeric(8, 2),
@Amount_8 money,
@MilkTimeID_9 bigint,
@MilkTypeID_10 bigint,
@Date_time_11 datetime,
@AddedBy_12 bigint,
@UpdatedBy_13 bigint
)
AS
BEGIN
BEGIN TRY
INSERT INTO [DailyMilkCollection]
(
[Code],
[CustomerID],
[FATValue],
[SNFValue],
[Quantity],
[Rate],
[Amount],
[MilkTimeID],
[MilkTypeID],
[Date_Time],
[AddedBy],
[UpdatedBy]
)
VALUES
(@Code_2,
@CustomerID_3,
@FATvalue_4,
@SNFvalue_5,
@Quantity_6,
@Rate_7,
@Amount_8,
@MilkTimeID_9,
@MilkTypeID_10,
@Date_time_11,
@AddedBy_12,
@UpdatedBy_13
)
END TRY
BEGIN CATCH
Select ERROR_LINE(),
ERROR_PROCEDURE(),
ERROR_MESSAGE()
END CATCH
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create or Alter PROCEDURE [dbo].[insert_DayEndCollection_1]
(
@Code_2 bigint,
@FATValue_3 Numeric(8, 2),
@SNFValue_4 Numeric(8, 2),
@Quantity_5 Numeric(8, 2),
@Rate_6 Numeric(8, 2),
@Amount_7 Numeric(8, 2),
@MilkTimeID_8 bigint,
@MilkTypeID_9 bigint,
@Date1_10 datetime,
@AddedBy_12 bigint,
@UpdatedBy_13 bigint
)
AS
INSERT INTO [DayEndCollection]
(
[Code],
[FATValue],
[SNFValue],
[Quantity],
[Rate],
[Amount],
[MilkTimeID],
[MilkTypeID],
[Date_Time],
[AddedBy],
[UpdatedBy]
)
VALUES
(@Code_2,
@FATValue_3,
@SNFValue_4,
@Quantity_5,
@Rate_6,
@Amount_7,
@MilkTimeID_8,
@MilkTypeID_9,
@Date1_10,
@AddedBy_12,
@UpdatedBy_13
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec [Retrivelist_Advance_Details] '2014-09-09','2014-09-11'
create or Alter procedure [dbo].[Retrivelist_Advance_Details]
(
@FromDate datetime,
@ToDate datetime
)
as
SELECT Advance.Code AS AdvanceNo,
Advance.Date_Time AS Date_,
Advance.Amount,
Advance.Remark,
Registration.Code AS CustomerCode,
Registration.LastName + ' ' + Registration.FirstName AS patientName,
Advance.AdvanceId
FROM Registration
INNER JOIN Advance
ON Registration.id = Advance.CustomerID
WHERE (Advance.Date_Time
BETWEEN COALESCE(@FromDate, Advance.Date_Time) AND COALESCE(@ToDate, Advance.Date_Time)
)
AND (Advance.Iscancelled = 0)
AND (Advance.Issettled = 0)
ORDER BY AdvanceNo,
Date_
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec [Retrivelist_Advance_Details_Customer] '2014-09-09','2014-09-11'
create or Alter procedure [dbo].[Retrivelist_Advance_Details_Customer]
(
@FromDate datetime,
@ToDate datetime,
@Custid bigint
)
as
SELECT Advance.Code AS AdvanceNo,
Advance.Date_Time AS Date_,
Advance.Amount,
Advance.Remark,
Registration.Code AS CustomerCode,
Registration.LastName + ' ' + Registration.FirstName AS patientName,
Advance.AdvanceId
FROM Registration
INNER JOIN Advance
ON Registration.id = Advance.CustomerID
WHERE (Advance.Date_Time
BETWEEN COALESCE(@FromDate, Advance.Date_Time) AND COALESCE(@ToDate, Advance.Date_Time)
)
AND (Advance.Iscancelled = 0)
and Registration.id = @Custid
--AND (Advance.Issettled = 1)
ORDER BY AdvanceNo,
Date_
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec [Retrivelist_DMC]'2014-09-01','2014-09-13',1,1
create or Alter procedure [dbo].[Retrivelist_DMC]
(
@FromDate datetime,
@ToDate datetime,
@Milktimeid bigint,
@Milktypeid bigint
)
as
IF @Milktimeid = 0
SET @Milktimeid = NULL
IF @Milktypeid = 0
SET @Milktypeid = NULL
SELECT TOP (100) PERCENT
DailyMilkCollection.Code AS TransNo,
DailyMilkCollection.Date_Time,
MilkTimeMaster.Description AS MilkTime,
MilkTypeMaster.Description AS MilkType,
Registration.Code AS CustomerCode,
Registration.LastName + ' ' + Registration.FirstName + ' ' + Registration.MiddleName AS CustomerName,
DailyMilkCollection.Quantity AS LiterQty,
DailyMilkCollection.Amount,
DailyMilkCollection.FATValue,
DailyMilkCollection.SNFValue,
DailyMilkCollection.Rate
FROM MilkTypeMaster
INNER JOIN MilkTimeMaster
INNER JOIN DailyMilkCollection
INNER JOIN Registration
ON DailyMilkCollection.CustomerID = Registration.id
ON MilkTimeMaster.Id = DailyMilkCollection.MilkTimeID
ON MilkTypeMaster.Id = DailyMilkCollection.MilkTypeID
WHERE (DailyMilkCollection.Date_Time
BETWEEN COALESCE(@FromDate, DailyMilkCollection.Date_Time) AND COALESCE(@ToDate, DailyMilkCollection.Date_Time)
)
AND (DailyMilkCollection.MilkTimeID = @Milktimeid)
AND (DailyMilkCollection.MilkTypeID = @Milktypeid)
AND (DailyMilkCollection.Iscancelled = 0)
ORDER BY DailyMilkCollection.Date_Time
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec Retrivelist_DMC_BillProcess '2014-09-09','2014-09-11'
create or Alter procedure [dbo].[Retrivelist_DMC_BillProcess]
(
@FromDate datetime,
@ToDate datetime
)
as
SELECT DailyMilkCollection.Code AS TransNo,
DailyMilkCollection.Date_Time AS Date_,
DailyMilkCollection.Amount,
DailyMilkCollection.Quantity,
MilkTimeMaster.Description AS MilkTime,
Registration.Code AS CustomerCode,
Registration.LastName + ' ' + Registration.FirstName AS CustomerNam,
DailyMilkCollection.TransId
FROM MilkTypeMaster
INNER JOIN MilkTimeMaster
INNER JOIN DailyMilkCollection
INNER JOIN Registration
ON DailyMilkCollection.CustomerID = Registration.id
ON MilkTimeMaster.Id = DailyMilkCollection.MilkTimeID
ON MilkTypeMaster.Id = DailyMilkCollection.MilkTypeID
WHERE (DailyMilkCollection.Date_Time
BETWEEN COALESCE(@FromDate, DailyMilkCollection.Date_Time) AND COALESCE(@ToDate, DailyMilkCollection.Date_Time)
)
AND (DailyMilkCollection.Iscancelled = 0)
AND (DailyMilkCollection.Issettled = 0)
ORDER BY TransNo,
Date_,
MilkTime DESC
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec [Retrivelist_DMC_BillProcess_customerwise] '2014-09-09','2014-09-11',2
create or Alter procedure [dbo].[Retrivelist_DMC_BillProcess_customerwise]
(
@FromDate datetime,
@ToDate datetime,
@Custid bigint
)
as
SELECT DailyMilkCollection.Code AS TransNo,
DailyMilkCollection.Date_Time AS Date_,
DailyMilkCollection.Amount,
DailyMilkCollection.Quantity,
MilkTimeMaster.Description AS MilkTime,
Registration.Code AS CustomerCode,
Registration.LastName + ' ' + Registration.FirstName AS CustomerNam,
DailyMilkCollection.TransId
FROM MilkTypeMaster
INNER JOIN MilkTimeMaster
INNER JOIN DailyMilkCollection
INNER JOIN Registration
ON DailyMilkCollection.CustomerID = Registration.id
ON MilkTimeMaster.Id = DailyMilkCollection.MilkTimeID
ON MilkTypeMaster.Id = DailyMilkCollection.MilkTypeID
WHERE (DailyMilkCollection.Date_Time
BETWEEN COALESCE(@FromDate, DailyMilkCollection.Date_Time) AND COALESCE(@ToDate, DailyMilkCollection.Date_Time)
)
AND (DailyMilkCollection.Iscancelled = 0)
AND (Registration.id = @Custid)
ORDER BY TransNo,
Date_,
MilkTime DESC
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec [Retrivelist_DMC_Date_Milktimewise]'2014-09-17','2014-09-17',2
create or Alter procedure [dbo].[Retrivelist_DMC_Date_Milktimewise]
(
@FromDate datetime,
@ToDate datetime,
@Milktimeid bigint
)
as
IF @Milktimeid = 0
SET @Milktimeid = NULL
SELECT TOP (100) PERCENT
dbo.DailyMilkCollection.Code as TransNo,
dbo.DailyMilkCollection.Date_Time,
dbo.MilkTimeMaster.Description AS MilkTime,
dbo.MilkTypeMaster.Description AS MilkType,
dbo.Registration.Code AS CustomerCode,
dbo.Registration.LastName + ' ' + dbo.Registration.FirstName + ' ' + dbo.Registration.MiddleName AS CustomerName,
dbo.DailyMilkCollection.Quantity as LiterQty,
dbo.DailyMilkCollection.Amount,
dbo.DailyMilkCollection.FATValue,
dbo.DailyMilkCollection.SNFValue,
dbo.DailyMilkCollection.Rate
FROM dbo.MilkTypeMaster
INNER JOIN dbo.MilkTimeMaster
INNER JOIN dbo.DailyMilkCollection
INNER JOIN dbo.Registration
ON dbo.DailyMilkCollection.CustomerID = dbo.Registration.id
ON dbo.MilkTimeMaster.Id = dbo.DailyMilkCollection.MilkTimeID
ON dbo.MilkTypeMaster.Id = dbo.DailyMilkCollection.MilkTypeID
WHERE (DailyMilkCollection.Date_Time
BETWEEN COALESCE(@FromDate, DailyMilkCollection.Date_Time) and COALESCE(@ToDate, DailyMilkCollection.Date_Time)
)
and (DailyMilkCollection.MilkTimeID = @Milktimeid)
AND (DailyMilkCollection.Iscancelled = 0)
ORDER BY dbo.DailyMilkCollection.Date_Time
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec [Retrivelist_DMC_Datewise]'2014-09-13','2014-09-13'
create or Alter procedure [dbo].[Retrivelist_DMC_Datewise]
(
@FromDate datetime,
@ToDate datetime
)
as
SELECT TOP (100) PERCENT
DailyMilkCollection.Code AS TransNo,
Registration.Code AS CustomerCode,
Registration.LastName,
Registration.FirstName,
Registration.MiddleName,
DailyMilkCollection.Quantity,
DailyMilkCollection.FATValue,
DailyMilkCollection.SNFValue,
DailyMilkCollection.Rate,
DailyMilkCollection.Amount,
MilkTimeMaster.Description AS MilkTime,
MilkTypeMaster.Description AS MilkType,
DailyMilkCollection.Date_Time AS Date_,
DailyMilkCollection.Issettled
FROM MilkTypeMaster
INNER JOIN MilkTimeMaster
INNER JOIN DailyMilkCollection
INNER JOIN Registration
ON DailyMilkCollection.CustomerID = Registration.id
ON MilkTimeMaster.Id = DailyMilkCollection.MilkTimeID
ON MilkTypeMaster.Id = DailyMilkCollection.MilkTypeID
WHERE (DailyMilkCollection.Date_Time
BETWEEN COALESCE(@FromDate, DailyMilkCollection.Date_Time) AND COALESCE(@ToDate, DailyMilkCollection.Date_Time)
)
AND (DailyMilkCollection.Iscancelled = 0)
ORDER BY Date_
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec [Retrivelist_DMC_Datewise_Customerwise]'2014-09-24','2014-09-26',2
create or Alter procedure [dbo].[Retrivelist_DMC_Datewise_Customerwise]
(
@FromDate datetime,
@ToDate datetime,
@customerid bigint
)
as
SELECT TOP (100) PERCENT
DailyMilkCollection.Code AS TransNo,
Registration.Code AS CustomerCode,
Registration.LastName,
Registration.FirstName,
Registration.MiddleName,
DailyMilkCollection.Quantity,
DailyMilkCollection.FATValue,
DailyMilkCollection.SNFValue,
DailyMilkCollection.Rate,
DailyMilkCollection.Amount,
MilkTimeMaster.Description AS MilkTime,
MilkTypeMaster.Description AS MilkType,
DailyMilkCollection.Date_Time AS Date_,
DailyMilkCollection.Issettled
FROM MilkTypeMaster
INNER JOIN MilkTimeMaster
INNER JOIN DailyMilkCollection
INNER JOIN Registration
ON DailyMilkCollection.CustomerID = Registration.id
ON MilkTimeMaster.Id = DailyMilkCollection.MilkTimeID
ON MilkTypeMaster.Id = DailyMilkCollection.MilkTypeID
WHERE (DailyMilkCollection.Date_Time
BETWEEN COALESCE(@FromDate, DailyMilkCollection.Date_Time) AND COALESCE(@ToDate, DailyMilkCollection.Date_Time)
)
AND (DailyMilkCollection.Iscancelled = 0)
AND (Registration.id = @customerid)
ORDER BY Date_
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec [Retrivelist_DMC_Datewise_Customerwise]'2014-09-24','2014-09-26',2
create or Alter procedure [dbo].[Retrivelist_DMC_Datewise_Customerwise_Evening]
(
@FromDate datetime,
@ToDate datetime,
@customerid bigint
)
as
SELECT TOP (100) PERCENT
DailyMilkCollection.Code AS TransNo,
Registration.Code AS CustomerCode,
Registration.LastName,
Registration.FirstName,
Registration.MiddleName,
DailyMilkCollection.Quantity,
DailyMilkCollection.FATValue,
DailyMilkCollection.SNFValue,
DailyMilkCollection.Rate,
DailyMilkCollection.Amount,
MilkTimeMaster.Description AS MilkTime,
MilkTypeMaster.Description AS MilkType,
DailyMilkCollection.Date_Time AS Date_,
DailyMilkCollection.Issettled
FROM MilkTypeMaster
INNER JOIN MilkTimeMaster
INNER JOIN DailyMilkCollection
INNER JOIN Registration
ON DailyMilkCollection.CustomerID = Registration.id
ON MilkTimeMaster.Id = DailyMilkCollection.MilkTimeID
ON MilkTypeMaster.Id = DailyMilkCollection.MilkTypeID
WHERE (DailyMilkCollection.Date_Time
BETWEEN COALESCE(@FromDate, DailyMilkCollection.Date_Time) AND COALESCE(@ToDate, DailyMilkCollection.Date_Time)
)
AND (DailyMilkCollection.Iscancelled = 0)
AND (Registration.id = @customerid)
and (DailyMilkCollection.MilkTimeID = 2)
ORDER BY Date_
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec [Retrivelist_DMC_Datewise_Customerwise]'2014-09-24','2014-09-26',2
create or Alter procedure [dbo].[Retrivelist_DMC_Datewise_Customerwise_Morning]
(
@FromDate datetime,
@ToDate datetime,
@customerid bigint
)
as
SELECT TOP (100) PERCENT
DailyMilkCollection.Code AS TransNo,
Registration.Code AS CustomerCode,
Registration.LastName,
Registration.FirstName,
Registration.MiddleName,
DailyMilkCollection.Quantity,
DailyMilkCollection.FATValue,
DailyMilkCollection.SNFValue,
DailyMilkCollection.Rate,
DailyMilkCollection.Amount,
MilkTimeMaster.Description AS MilkTime,
MilkTypeMaster.Description AS MilkType,
DailyMilkCollection.Date_Time AS Date_,
DailyMilkCollection.Issettled
FROM MilkTypeMaster
INNER JOIN MilkTimeMaster
INNER JOIN DailyMilkCollection
INNER JOIN Registration
ON DailyMilkCollection.CustomerID = Registration.id
ON MilkTimeMaster.Id = DailyMilkCollection.MilkTimeID
ON MilkTypeMaster.Id = DailyMilkCollection.MilkTypeID
WHERE (DailyMilkCollection.Date_Time
BETWEEN COALESCE(@FromDate, DailyMilkCollection.Date_Time) AND COALESCE(@ToDate, DailyMilkCollection.Date_Time)
)
AND (DailyMilkCollection.Iscancelled = 0)
AND (Registration.id = @customerid)
and (DailyMilkCollection.MilkTimeID = 1)
ORDER BY Date_
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
create or Alter PROCEDURE [dbo].[update_DailyMilkCollection_1]
@Id_1 bigint,
@Code_2 bigint,
@CustomerID_3 bigint,
@FATvalue_4 Numeric(8, 2),
@SNFvalue_5 Numeric(8, 2),
@Quantity_6 Numeric(8, 2),
@Rate_7 Numeric(8, 2),
@Amount_8 money,
@MilkTimeID_9 bigint,
@MilkTypeID_10 bigint,
@Date_time_11 datetime,
@UpdatedBy_12 bigint
AS
UPDATE [DailyMilkCollection]
SET [Code] = @Code_2,
[CustomerID] = @CustomerID_3,
[FATValue] = @FATvalue_4,
[SNFValue] = @SNFvalue_5,
[Quantity] = @Quantity_6,
[Rate] = @Rate_7,
[Amount] = @Amount_8,
[MilkTimeID] = @MilkTimeID_9,
[MilkTypeID] = @MilkTypeID_10,
[Date_Time] = @Date_time_11,
[UpdatedBy] = @UpdatedBy_12
WHERE ([TransId] = @Id_1)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
create or Alter PROCEDURE [dbo].[update_DayEndCollection_1]
@Code_2 bigint,
@MilkTimeID_3 bigint,
@MilkTypeID_4 bigint,
@Quantity_5 Numeric(8, 2),
@FATvalue_6 Numeric(8, 2),
@SNFvalue_7 Numeric(8, 2),
@Rate_8 Numeric(8, 2),
@Amount_9 money,
@UpdatedBy_12 bigint
AS
UPDATE [DayEndCollection]
SET [MilkTimeID] = @MilkTimeID_3,
[MilkTypeID] = @MilkTypeID_4,
[Quantity] = @Quantity_5,
[FATValue] = @FATvalue_6,
[SNFValue] = @SNFvalue_7,
[Rate] = @Rate_8,
[Amount] = @Amount_9,
[UpdatedBy] = @UpdatedBy_12
WHERE ([Code] = @Code_2)
GO

----------Index Script

CREATE NONCLUSTERED INDEX IX_RateMaster_MilkTypeID

ON [dbo].[RateMaster] ([MilkTypeID])

INCLUDE ([SNFID])

CREATE NONCLUSTERED INDEX IX_RateMaster_MilkTypeID_FATID

ON [dbo].[RateMaster] ([MilkTypeID])

INCLUDE (FATID)

CREATE NONCLUSTERED INDEX IX_DailyMilkCollection_FAT_SNF_MilkType

ON [dbo].[DailyMilkCollection]

([FATValue],[SNFValue],[MilkTypeID])

Search This Blog

Dairy Project Part 1.create table script

Comments

Post a Comment