Below class is to update the LedgerJournalTrans Check number
class CITBRSCheckUpload extends Runbase
{
DialogField dialogFilename;
FilenameOpen fileName;
#define.CurrentVersion(1)
#localmacro.CurrentList
filename
#endmacro
}
protected Object dialog()
{
DialogRunBase dialog = new DialogRunBase("Import Check Number", this);
;
dialogFilename = dialog.addField(typeId(FilenameOpen));
dialog.filenameLookupFilter(["@SYS28576","*.xlsx"]);
dialog.filenameLookupTitle("Upload from Excel");
dialogFilename.value(filename);
dialog.caption("Excel Upload");
return dialog;
}
public boolean getFromDialog()
{
;
filename = dialogFilename.value();
if(!filename)
{
throw error("@SYS112406");
}
return true;
}
Public void ImportDatafromExcel()
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
int row;
str c,test;
BankAccountTrans BankAcntTrans;
LedgerJournalTrans journalTrans;
VendParameters vendParms;
Voucher ledgerTransVoucher;
Date ledgerTransDate;
BankChequeNum ledgerTransChequeNum;
boolean temp;
;
temp = false;
row = 2;
application = SysExcelApplication::construct();
workbooks = application.workbooks();
workbooks.open(fileName,0,true);
try
{
workbooks.open(filename,0,0,2);
}
catch (Exception::Error)
{
throw error("File cannot be opened.");
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
select vendParms;
if(vendParms.CITPaymentVoucher ==0 ||vendParms.CITASODate ==0 ||vendParms.CITUTRNumber == 0)
throw error("Excel template mapping in AP Parameter are not done");
//do
while(cells.item(row,1).value().toString()!= "VT_EMPTY")
{
ledgerTransVoucher = cells.item(row,vendParms.CITPaymentVoucher).value().bStr();
ledgerTransDate = cells.item(row, vendParms.CITASODate).value().date();
ledgerTransChequeNum = cells.item(row, vendParms.CITUTRNumber).value().bStr();
if(ledgerTransVoucher == " ")
info("For row "+ int2str(row) + "Voucher is blank");
if(!ledgerTransDate)
info("For row "+ int2str(row) + "Transaction Date is blank");
select forupdate journalTrans where journalTrans.Voucher == ledgerTransVoucher
&& journalTrans.TransDate == ledgerTransDate;
{
if(journalTrans)
{
if(journalTrans.BankChequeNum == " ")
{
ttsbegin;
journalTrans.BankChequeNum = ledgerTransChequeNum;
journalTrans.update();
select forupdate BankAcntTrans where BankAcntTrans.Voucher == ledgerTransVoucher
&& journalTrans.TransDate == ledgerTransDate;
{
BankAcntTrans.ChequeNum = ledgerTransChequeNum;
BankAcntTrans.update();
}
temp = true;
ttscommit;
}
else
{
info("For " +ledgerTransVoucher +" No."+ strfmt(" Check number is already there. So you cannot update that "));
}
}
}
row++;
}
//while(cells.item(row,1).value().toString()!= "VT_EMPTY");
// while (type != COMVariantType::VT_EMPTY);
if(temp == true)
{
info(strfmt("Uploaded successfully."));
}
else
{
info(strfmt("Upload fail."));
}
application.quit();
}
public container pack()
{
;
return [#CurrentVersion,#CurrentList];
}
public boolean unpack(container _packedClass)
{
Integer version = conpeek(_packedClass,1);
;
switch (version)
{
case #CurrentVersion:
[version,#CurrentList] = _packedClass;
break;
default :
return false;
}
return true;
}
public static CITBRSCheckUpload construct()
{
return new CITBRSCheckUpload();
}
public static void main(Args _args)
{
CITBRSCheckUpload ExcelUpload = CITBRSCheckUpload::construct();
;
if (ExcelUpload.prompt())
{
//Call function ExcelUpload.
ExcelUpload.ImportDatafromExcel();
}
}