Contract Class:
class MZNNM_InventoryStockContract { ItemId itemCode; TransDate fromDate; TransDate toDate; VendAccount vendAccount; [ DataMemberAttribute("VendorNumber"), sysoperationlabelAttribute(literalstr("Vendor Code")) ] public VendAccount parmVendAccount( VendAccount _vendAccount = vendAccount) { vendAccount= _vendAccount; return vendAccount; } [ DataMemberAttribute("ItemCode"), sysoperationlabelAttribute(literalstr("Item Code")) ] public ItemId parmItemCode( ItemId _itemCode= itemCode) { itemCode= _itemCode; return itemCode; } [ DataMemberAttribute("Fromdate"), sysoperationlabelAttribute(literalstr("From date")) ] public TransDate parmFromdate( TransDate _fromDate = fromDate) { fromDate= _fromDate; return fromDate; } [ DataMemberAttribute("Todate"), sysoperationlabelAttribute(literalstr("To date")) ] public TransDate parmTodate( TransDate _toDate = toDate) { toDate= _toDate; return toDate; } }
DP Class:
[ SRSReportParameterAttribute (classstr(MZNNM_InventoryStockContract)), SRSReportQueryAttribute (queryStr(MZNNM_PurchInventoryStockQuery)) ] class MZNNM_InventoryStockDP extends SRSReportDataProviderBase { MZNNM_InventoryStockTmp objInvStockTmp; PurchLine purchLine; PurchTable purchTable; ItemId itemId; VENDPACKINGSLIPTRANS VENDPACKINGSLIPTRANS; VENDPACKINGSLIPJOUR VENDPACKINGSLIPJOUR; VendTable vendTable; DirPartyTable dirPartyTable; VendInvoiceTrans vendInvoiceTrans; VendInvoiceJour vendInvoiceJour; TransDate fromDate; TransDate toDate; VendAccount vendAccount; [SRSReportDataSetAttribute (tablestr(MZNNM_InventoryStockTmp))] public MZNNM_InventoryStockTmp getMZNNM_InventoryStockTmp() { select * from objInvStockTmp; return objInvStockTmp; } public void processReport() { MZNNM_InventoryStockContract contract = new MZNNM_InventoryStockContract(); contract = this.parmDataContract() as MZNNM_InventoryStockContract; itemId = contract.parmItemCode(); fromDate = contract.parmFromdate(); toDate = contract.parmTodate(); vendAccount = contract.parmVendAccount(); CompanyInfo companyInfo = CompanyInfo::find(); Query query = new Query(this.parmQuery()); if(fromDate && toDate){ query.dataSourceTable(tableNum(PurchTable)).addRange(fieldnum(PurchTable, CreatedDateTime)).value(queryRange(DateTimeUtil::newDateTime(fromDate,0),DateTimeUtil::newDateTime(toDate,0))); } if(vendAccount){ query.dataSourceTable(tableNum(PurchLine)).addRange(fieldnum(PurchLine, VendAccount)).value(vendAccount); } if(itemId){ query.dataSourceTable(tableNum(PurchLine)).addRange(fieldnum(PurchLine, ItemId)).value(itemId); } QueryRun run = new QueryRun(query); while(run.next()) { purchLine = run.get(tableNum(PurchLine)); purchTable = run.get(tableNum(PurchTable)); while select VENDPACKINGSLIPTRANS where VENDPACKINGSLIPTRANS.InventTransId == purchLine.InventTransId { VENDPACKINGSLIPJOUR.clear(); select VENDPACKINGSLIPJOUR where VENDPACKINGSLIPJOUR.PackingSlipId == VENDPACKINGSLIPTRANS.PackingSlipId; vendTable.clear(); select vendTable where vendTable.AccountNum == purchTable.OrderAccount; dirPartyTable.clear(); select dirPartyTable where dirPartyTable.RecId == vendTable.Party; vendInvoiceTrans.clear(); vendInvoiceJour.clear(); select InvoiceId,InvoiceDate from vendInvoiceJour join vendInvoiceTrans where vendInvoiceTrans.InventTransId == VENDPACKINGSLIPTRANS.InventTransId && vendInvoiceJour.InvoiceId == vendInvoiceTrans.InvoiceId; objInvStockTmp.clear(); //logo objInvStockTmp.Logo = CompanyImage::findByRecord(CompanyInfo).Image; //Packing info objInvStockTmp.GRNQty = VENDPACKINGSLIPTRANS.Qty; objInvStockTmp.GRNDate = VENDPACKINGSLIPJOUR.DeliveryDate; objInvStockTmp.GRNNo = VENDPACKINGSLIPJOUR.PackingSlipId; //Purch line info objInvStockTmp.Description = this.productWithDimensions(purchLine.RecId); objInvStockTmp.SupplierName = vendTable.AccountNum+':'+dirPartyTable.Name; objInvStockTmp.PurchLineQty = purchLine.PurchQty; objInvStockTmp.PuchLineCostPerUnit = purchLine.PurchPrice; objInvStockTmp.PurchLineTotal = purchLine.LineAmount; objInvStockTmp.PONo = purchLine.PurchId; objInvStockTmp.POStatus = enum2Str(purchLine.PurchStatus); objInvStockTmp.PODateUTC = purchTable.CreatedDateTime; //invoice details objInvStockTmp.InvoiceDate = vendInvoiceJour.InvoiceDate; //payment details objInvStockTmp.PaymentDates = this.paymentDate(vendInvoiceJour.InvoiceId); objInvStockTmp.insert(); } } } private str paymentDate( VendInvoiceId invoiceId= '') { VendTrans vendTrans , vendTrans2; InventDim inventDim; LedgerJournalTrans ledgerJournalTrans; VendInvoiceJour VendInvoiceJour2; VendSettlement VendSettlement; str TransDates; TransDates=''; vendTrans.clear(); select RecId from vendTrans where vendTrans.Invoice == InvoiceId; if(vendTrans){ while select * from vendSettlement where vendSettlement.TransRecId == vendTrans.RecId { ledgerJournalTrans.clear(); select TRANSDATE from ledgerJournalTrans where ledgerJournalTrans.VendTransId == vendSettlement.OffsetRecid; if(ledgerJournalTrans){ TransDates += (TransDates=='') ? date2Str(ledgerJournalTrans.TransDate,123,DateDay::Digits2,DateSeparator::Space,DateMonth::Short,DateSeparator::Space,DateYear::Digits4) : '\n'+ date2Str(ledgerJournalTrans.TransDate,123,DateDay::Digits2,DateSeparator::Space,DateMonth::Short,DateSeparator::Space,DateYear::Digits4); } } } return TransDates; } private str productWithDimensions( RecId purchLineId) { InventDim inventDim; InventTable inventTable; PurchLine purchline2; EcoResProductTranslation ecoResProductTranslation; str dimensionsList; str productName; inventDim.clear(); productName = ''; dimensionsList = ''; purchline2.clear(); select * from purchline2 where purchline2.RecId == purchLineId; inventTable.clear(); select * from inventTable where inventTable.ItemId == purchline2.ItemId; productName = purchline2.ItemId; productName +=' : '+ EcoResProductTranslation::findByProductLanguage(inventTable.Product,CompanyInfo::languageId()).Name; //productName +=' : '+ inventTable::find(purchline2.ItemId).productName(CompanyInfo::languageId(),purchline2.InventDimId); select * from inventDim where inventDim.InventDimId == purchline2.InventDimId; if(inventDim){ dimensionsList = (inventDim.CONFIGID == '') ? '' : inventDim.CONFIGID; dimensionsList += (inventDim.INVENTSIZEID == '') ? '' : ' : '+inventDim.INVENTSIZEID; dimensionsList += (inventDim.INVENTCOLORID == '') ? '' : ' : '+inventDim.INVENTCOLORID; dimensionsList += (inventDim.INVENTSTYLEID == '') ? '' : ' : '+inventDim.INVENTSTYLEID; } return productName+' : '+dimensionsList; } }
Adding the Simple Query:
Adding Sr# in the report design: