Post
Ask Question
Ramco Ramco

Ramco Ramco

  • 442
  • 3.4k
  • 528.6k

How to create Json according to below Schema

Feb 7 2021 2:27 PM
Hi
 
I have 2 tables . I want to convert data into Json format according to the below Schema. What should be the Models. Since DispDetls are blank in Database it should be Null.
 
I am using MVC
{
"Version": "1.1",
"TranDtls": { -
"TaxSch": "GST",
"SupTyp": "B2B",
"RegRev": "Y",
"EcmGstin": null,
"IgstOnIntra": "N"
},
"DocDtls": {
"Typ": "INV",
"No": "DOC/001",
"Dt": "18/08/2020"
},
"SellerDtls": {
"Gstin": "37ARZPT4384Q1MT",
"LglNm": "NIC company pvt ltd",
"TrdNm": "NIC Industries",
"Addr1": "5th block, kuvempu layout",
"Addr2": "kuvempu layout",
"Loc": "GANDHINAGAR",
"Pin": 518001,
"Stcd": "37",
"Ph": "9000000000",
},
"BuyerDtls": {
"Gstin": "29AWGPV7107B1Z1",
"LglNm": "XYZ company pvt ltd",
"TrdNm": "XYZ Industries",
"Pos": "12",
"Addr1": "7th block, kuvempu layout",
"Addr2": "kuvempu layout",
"Loc": "GANDHINAGAR",
"Pin": 562160,
"Stcd": "29",
"Ph": "91111111111",
},
"DispDtls": {
"Nm": "ABC company pvt ltd",
"Addr1": "7th block, kuvempu layout",
"Addr2": "kuvempu layout",
"Loc": "Banagalore",
"Pin": 562160,
"Stcd": "29"
},
"ItemList": [
{
"SlNo": "1",
"PrdDesc": "Rice",
"IsServc": "N",
"HsnCd": "1001",
"Barcde": "123456",
"Qty": 100.345,
"FreeQty": 10,
"Unit": "BAG",
"UnitPrice": 99.545,
"TotAmt": 9988.84,
"Discount": 10,
"PreTaxVal": 1,
"AssAmt": 9978.84,
"GstRt": 12.0,
"IgstAmt": 1197.46,
"CgstAmt": 0,
"SgstAmt": 0,
"CesRt": 5,
"CesAmt": 498.94,
"CesNonAdvlAmt": 10,
"StateCesRt": 12,
"StateCesAmt": 1197.46,
"StateCesNonAdvlAmt": 5,
"OthChrg": 10,
"TotItemVal": 12897.7,
"OrdLineRef": "3256",
"OrgCntry": "AG",
"PrdSlNo": "12345",
"BchDtls": {
"Nm": "123456",
"ExpDt": "01/08/2020",
"WrDt": "01/09/2020"
},
"AttribDtls": [
{
"Nm": "Rice",
"Val": "10000"
}
]
}
],
}
--------------------------------------------------------------------------------------------
USE [Test]
GO
/****** Object: Table [dbo].[Master0] Script Date: 07-02-2021 19:49:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Master0](
[TaxSch] [varchar](10) NULL,
[SubTyp] [varchar](10) NULL,
[RegRev] [varchar](1) NULL,
[EcmGstin] [varchar](15) NULL,
[IgstOnIntra] [varchar](1) NULL,
[Typ] [varchar](3) NULL,
[No] [varchar](16) NULL,
[Dt] [varchar](10) NULL,
[GSTIN] [varchar](15) NULL,
[LglNm] [varchar](100) NULL,
[TrdNm] [varchar](100) NULL,
[Addr1] [varchar](100) NULL,
[Addr2] [varchar](100) NULL,
[Loc] [varchar](50) NULL,
[Pin] [int] NULL,
[Stcd] [varchar](2) NULL,
[Ph] [varchar](12) NULL,
[Em] [varchar](100) NULL,
[BuyerGSTIN] [varchar](15) NULL,
[BuyerLglNm] [varchar](100) NULL,
[BuyerTrdNm] [varchar](100) NULL,
[BuyerPos] [varchar](2) NULL,
[BuyerAddr1] [varchar](100) NULL,
[BuyerAddr2] [varchar](100) NULL,
[BuyerLoc] [varchar](50) NULL,
[BuyerPin] [int] NULL,
[BuyerStcd] [varchar](2) NULL,
[BuyerPhone] [varchar](12) NULL,
[BuyerEmail] [varchar](100) NULL,
[DispatchNm] [varchar](100) NULL,
[DispatchAddr1] [varchar](100) NULL,
[DispatchAddr2] [varchar](100) NULL,
[DispatchLoc] [varchar](50) NULL,
[DispatchPin] [int] NULL,
[DispatchStcd] [varchar](2) NULL,
[ShipGSTIN] [varchar](15) NULL,
[ShipLglNm] [varchar](100) NULL,
[ShipTrdNm] [varchar](100) NULL,
[ShipAddr1] [varchar](100) NULL,
[ShipAddr2] [varchar](100) NULL,
[ShipLoc] [varchar](50) NULL,
[ShipPin] [int] NULL,
[ShipStcd] [varchar](2) NULL,
[AssVal] [decimal](18, 2) NULL,
[CgstVal] [decimal](18, 2) NULL,
[IgstVal] [decimal](18, 2) NULL,
[CesVal] [decimal](18, 2) NULL,
[StCesVal] [decimal](18, 2) NULL,
[Discount] [decimal](18, 2) NULL,
[OthChg] [decimal](18, 2) NULL,
[RndOfAmount] [decimal](18, 2) NULL,
[TotalInvVal] [decimal](18, 2) NULL,
[TotalInvValFc] [decimal](18, 2) NULL,
[PayeeName] [nvarchar](100) NULL,
[PayeeAccDet] [nvarchar](18) NULL,
[PayeeMode] [nvarchar](18) NULL,
[PayeeBranch] [nvarchar](11) NULL,
[PayeePayTerm] [nvarchar](100) NULL,
[PayeePaymentInstruction] [nvarchar](100) NULL,
[PayeeCreditTfr] [nvarchar](100) NULL,
[PayeeDirectDebit] [nvarchar](100) NULL,
[PayeeCreditDays] [nvarchar](100) NULL,
[PayeePaidAmt] [decimal](18, 2) NULL,
[PayeePaymentDue] [decimal](18, 2) NULL,
[RefRemarksNote] [nvarchar](100) NULL,
[InvStDt] [nvarchar](10) NULL,
[InvEndDt] [nvarchar](10) NULL,
[InvNo] [nvarchar](16) NULL,
[InvDt] [nvarchar](10) NULL,
[OtherRefNo] [nvarchar](20) NULL,
[AdviceNo] [nvarchar](20) NULL,
[ReceiptAdvicetDt] [nvarchar](10) NULL,
[BatchRef] [nvarchar](20) NULL,
[ContractRef] [nvarchar](20) NULL,
[OtherRef] [nvarchar](20) NULL,
[ProjRef] [nvarchar](20) NULL,
[RefNo] [nvarchar](16) NULL,
[RefDate] [nvarchar](10) NULL,
[DocumentUrl] [nvarchar](100) NULL,
[Docs] [nvarchar](1000) NULL,
[Info] [nvarchar](1000) NULL,
[ShipBillNo] [nvarchar](20) NULL,
[ShipBillDate] [nvarchar](10) NULL,
[Port] [nvarchar](10) NULL,
[RefundClaim] [nvarchar](1) NULL,
[ForCurrency] [nvarchar](16) NULL,
[CountryCode] [nvarchar](2) NULL,
[ExpDuty] [int] NULL,
[TransId] [nvarchar](15) NULL,
[TransName] [nvarchar](100) NULL,
[TransMode] [nvarchar](1) NULL,
[Distance] [int] NULL,
[TransDocNo] [nvarchar](15) NULL,
[TransDocDate] [nvarchar](10) NULL,
[VehicleNo] [nvarchar](20) NULL,
[VehType] [nvarchar](1) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Trans0] Script Date: 07-02-2021 19:49:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Trans0](
[No] [nvarchar](16) NULL,
[SlNo] [varchar](6) NULL,
[ProductDesc] [varchar](100) NULL,
[IsServ] [varchar](1) NULL,
[HsnCode] [varchar](8) NULL,
[BarCode] [varchar](30) NULL,
[Qty] [decimal](18, 2) NULL,
[FreeQty] [decimal](18, 2) NULL,
[Unit] [varchar](8) NULL,
[UnitPrice] [decimal](18, 2) NULL,
[TotAmount] [decimal](18, 2) NULL,
[Discount] [decimal](18, 2) NULL,
[PreTaxVal] [decimal](18, 2) NULL,
[AssAmt] [decimal](18, 2) NULL,
[GstRate] [decimal](18, 2) NULL,
[IgstAmt] [decimal](18, 2) NULL,
[CgstAmt] [decimal](18, 2) NULL,
[SgstAmt] [decimal](18, 2) NULL,
[CessRate] [decimal](18, 2) NULL,
[CessAmount] [decimal](18, 2) NULL,
[CessNonAdvolAmt] [decimal](18, 2) NULL,
[StateCessRate] [decimal](18, 2) NULL,
[StateCessNonAdvolAmt] [decimal](18, 2) NULL,
[OtherCharges] [decimal](18, 2) NULL,
[TotItemVal] [decimal](18, 2) NULL,
[OrdLineRef] [varchar](50) NULL,
[OriginCountry] [varchar](2) NULL,
[ProductSrNo] [varchar](20) NULL,
[BatchNo] [varchar](20) NULL,
[BatchExpDate] [varchar](10) NULL,
[BatchWarrantDate] [varchar](10) NULL,
[AttributeName] [varchar](100) NULL,
[AttributeVal] [varchar](100) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Master0] ([TaxSch], [SubTyp], [RegRev], [EcmGstin], [IgstOnIntra], [Typ], [No], [Dt], [GSTIN], [LglNm], [TrdNm], [Addr1], [Addr2], [Loc], [Pin], [Stcd], [Ph], [Em], [BuyerGSTIN], [BuyerLglNm], [BuyerTrdNm], [BuyerPos], [BuyerAddr1], [BuyerAddr2], [BuyerLoc], [BuyerPin], [BuyerStcd], [BuyerPhone], [BuyerEmail], [DispatchNm], [DispatchAddr1], [DispatchAddr2], [DispatchLoc], [DispatchPin], [DispatchStcd], [ShipGSTIN], [ShipLglNm], [ShipTrdNm], [ShipAddr1], [ShipAddr2], [ShipLoc], [ShipPin], [ShipStcd], [AssVal], [CgstVal], [IgstVal], [CesVal], [StCesVal], [Discount], [OthChg], [RndOfAmount], [TotalInvVal], [TotalInvValFc], [PayeeName], [PayeeAccDet], [PayeeMode], [PayeeBranch], [PayeePayTerm], [PayeePaymentInstruction], [PayeeCreditTfr], [PayeeDirectDebit], [PayeeCreditDays], [PayeePaidAmt], [PayeePaymentDue], [RefRemarksNote], [InvStDt], [InvEndDt], [InvNo], [InvDt], [OtherRefNo], [AdviceNo], [ReceiptAdvicetDt], [BatchRef], [ContractRef], [OtherRef], [ProjRef], [RefNo], [RefDate], [DocumentUrl], [Docs], [Info], [ShipBillNo], [ShipBillDate], [Port], [RefundClaim], [ForCurrency], [CountryCode], [ExpDuty], [TransId], [TransName], [TransMode], [Distance], [TransDocNo], [TransDocDate], [VehicleNo], [VehType]) VALUES (N'GST', N'B2B', N'N', N'', N'N', N'INV', N'INV/00001', N'2020/08/06', N'24AAAAA1116A1AA', N'Traders', N'', N'675', N'', N'BNG', 506060, N'01', N'', N'', N'24AAAAA1116A1AA', N'Traders', N'', N'14', N'879', N'', N'Delhi', 160087, N'14', N'', N'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[Master0] ([TaxSch], [SubTyp], [RegRev], [EcmGstin], [IgstOnIntra], [Typ], [No], [Dt], [GSTIN], [LglNm], [TrdNm], [Addr1], [Addr2], [Loc], [Pin], [Stcd], [Ph], [Em], [BuyerGSTIN], [BuyerLglNm], [BuyerTrdNm], [BuyerPos], [BuyerAddr1], [BuyerAddr2], [BuyerLoc], [BuyerPin], [BuyerStcd], [BuyerPhone], [BuyerEmail], [DispatchNm], [DispatchAddr1], [DispatchAddr2], [DispatchLoc], [DispatchPin], [DispatchStcd], [ShipGSTIN], [ShipLglNm], [ShipTrdNm], [ShipAddr1], [ShipAddr2], [ShipLoc], [ShipPin], [ShipStcd], [AssVal], [CgstVal], [IgstVal], [CesVal], [StCesVal], [Discount], [OthChg], [RndOfAmount], [TotalInvVal], [TotalInvValFc], [PayeeName], [PayeeAccDet], [PayeeMode], [PayeeBranch], [PayeePayTerm], [PayeePaymentInstruction], [PayeeCreditTfr], [PayeeDirectDebit], [PayeeCreditDays], [PayeePaidAmt], [PayeePaymentDue], [RefRemarksNote], [InvStDt], [InvEndDt], [InvNo], [InvDt], [OtherRefNo], [AdviceNo], [ReceiptAdvicetDt], [BatchRef], [ContractRef], [OtherRef], [ProjRef], [RefNo], [RefDate], [DocumentUrl], [Docs], [Info], [ShipBillNo], [ShipBillDate], [Port], [RefundClaim], [ForCurrency], [CountryCode], [ExpDuty], [TransId], [TransName], [TransMode], [Distance], [TransDocNo], [TransDocDate], [VehicleNo], [VehType]) VALUES (N'GST', N'B2B', N'N', N'', N'N', N'INV', N'INV/00002', N'2020/09/15', N'14AAAAA1116A1AA', N'Traders', N'', N'675', N'', N'BNG', 506060, N'14', N'', N'', N'17AAAAA1116A1AA', N'Traders', N'', N'17', N'879', N'', N'XYZ', 160087, N'17', N'', N'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[Trans0] ([No], [SlNo], [ProductDesc], [IsServ], [HsnCode], [BarCode], [Qty], [FreeQty], [Unit], [UnitPrice], [TotAmount], [Discount], [PreTaxVal], [AssAmt], [GstRate], [IgstAmt], [CgstAmt], [SgstAmt], [CessRate], [CessAmount], [CessNonAdvolAmt], [StateCessRate], [StateCessNonAdvolAmt], [OtherCharges], [TotItemVal], [OrdLineRef], [OriginCountry], [ProductSrNo], [BatchNo], [BatchExpDate], [BatchWarrantDate], [AttributeName], [AttributeVal]) VALUES (N'INV/00001', N'1', N'Pr01', N'N', N'1001', N'', CAST(10.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), N'BAGS', CAST(200.00 AS Decimal(18, 2)), CAST(2000.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(5.00 AS Decimal(18, 2)), CAST(1000.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(3000.00 AS Decimal(18, 2)), N'', N'', N'', N'', N'', N'', N'', N'')
INSERT [dbo].[Trans0] ([No], [SlNo], [ProductDesc], [IsServ], [HsnCode], [BarCode], [Qty], [FreeQty], [Unit], [UnitPrice], [TotAmount], [Discount], [PreTaxVal], [AssAmt], [GstRate], [IgstAmt], [CgstAmt], [SgstAmt], [CessRate], [CessAmount], [CessNonAdvolAmt], [StateCessRate], [StateCessNonAdvolAmt], [OtherCharges], [TotItemVal], [OrdLineRef], [OriginCountry], [ProductSrNo], [BatchNo], [BatchExpDate], [BatchWarrantDate], [AttributeName], [AttributeVal]) VALUES (N'INV/00002', N'1', N'Pr02', N'N', N'1002', N'', CAST(10.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), N'BAGS', CAST(200.00 AS Decimal(18, 2)), CAST(2000.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(5.00 AS Decimal(18, 2)), CAST(1000.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(3000.00 AS Decimal(18, 2)), N'', N'', N'', N'', N'', N'', N'', N'')
INSERT [dbo].[Trans0] ([No], [SlNo], [ProductDesc], [IsServ], [HsnCode], [BarCode], [Qty], [FreeQty], [Unit], [UnitPrice], [TotAmount], [Discount], [PreTaxVal], [AssAmt], [GstRate], [IgstAmt], [CgstAmt], [SgstAmt], [CessRate], [CessAmount], [CessNonAdvolAmt], [StateCessRate], [StateCessNonAdvolAmt], [OtherCharges], [TotItemVal], [OrdLineRef], [OriginCountry], [ProductSrNo], [BatchNo], [BatchExpDate], [BatchWarrantDate], [AttributeName], [AttributeVal]) VALUES (N'INV/00002', N'1', N'Pr03', N'N', N'1003', N'', CAST(10.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), N'BAGS', CAST(200.00 AS Decimal(18, 2)), CAST(2000.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(5.00 AS Decimal(18, 2)), CAST(1000.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(3000.00 AS Decimal(18, 2)), N'', N'', N'', N'', N'', N'', N'', N'')
Thanks

Answers (5)