importing-Route card journal

Hi all,

pastedimage1511933433014v1.png

I need to create a new journal with lines as for prodid 174-10 will be a journal and for prodid174-20 will be a journal…

Like that for other production also.

Thanks in advance.

What issue do you have? Please explain.

static void sri(Args _args)
{
    SysExcelApplication             application;
    SysExcelWorkbooks               workbooks;
    SysExcelWorkbook                workbook;
    SysExcelWorksheets              worksheets;
    SysExcelWorksheet               worksheet;
    SysExcelCells                   cells;
    COMVariantType                  type;
    System.DateTime                 ShlefDate;
    FilenameOpen                    filename;
    dialogField                     dialogFilename;
    Dialog                          dialog;
    int                             row;
    int                             lines;
    str                             jourID;

    //Table Declarations Starts
    ProdJournalRoute              PJR;
    ProdJournalTable              PJTab, table;
    //Table Declartions Ends
    ;
    dialog          =   new Dialog("Upload excel file");
    dialogFilename  =   dialog.addField(typeId(FilenameOpen));
    dialog.filenameLookupTitle("Upload from Excel");
    dialog.caption("Excel upload");
    dialogFilename.value(filename);
    if(!dialog.run())
        return;
    filename    =   dialogFilename.value();
    info(filename);
    application =   SysExcelApplication::construct();
    workbooks   =   application.workbooks();

    try{
     workbooks.open(filename);
    }
    catch(Exception::Error){
     throw error("Cannot open the file. Check if file exists in location ");
    }
    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();
    row = 1;
    ttsbegin;
    // create a journal header
    //journalType = Routecard
    PJTab.JournalType =ProdJournalType::RouteCard ;
    //get name ID for header
    PJTab.ProdId=cells.item(2,1).value().bStr();
    //PJTab.JournalNameId = ProdJournalName::standardJournalName(ProdJournalType::RouteCard,PJTab.ProdId);
    //PJTab.JournalNameId = ProdParametersDim::findDefault().RouteJournalNameId;
    PJTab.Description =ProdJournalName::find(PJTab.JournalNameId).Description;
    PJTab.insert();

    do{
     ttsbegin;
     row++;
     PJR.JournalId = PJTab.JournalId;

     PJR.OprId = cells.item(row,2).value().bStr();
     PJR.TransDate=cells.item(row,3).value().date();
     PJR.OprFinished=cells.item(row,4).value().boolean();
     PJR.QtyError = cells.item(row,5).value().double();
     PJR.QtyGood = cells.item(row,6).value().double();
     PJR.EmplId= cells.item(row,7).value().bStr();

    //Initialize lineNum to '0' else inventJournalTrans.insert() method wouldn't create a new lineNum for each insertion
    PJR.LineNum=0;
    //parameter for insert method
    PJR.insert();
    //update type variable to next row in file
    type= cells.item(row+1,1).value().variantType();
    ttscommit;
    }
    while(type != COMVariantType::VT_EMPTY);
    select count(recid) from PJR where PJR.JournalId == PJTab.JournalId;
    lines = PJR.RecId;
    select forupdate table where table.JournalId == PJTab.JournalId;
    table.NumOfLines=lines;
    table.update();
    application.quit();
    ttscommit;
}

I tried this coding, but the result came is like

pastedimage1511934428437v1.png

but i want the prd id 174 as a new journal with 2 lines as oprnum 10 and 20 and a new journals for other prodid’s…

i want this 2 prod id’s to be in a new journal and the same prodid with oprnum 20 to be in a new journal…

like that prodid’s 172,156…

I need to create a new journals.

When you are inserting the data (example for select line), check if there is already a journal with that production order and opr num. If there is a line, don’t create a new journal and use the existing journal. If there is no line, then create a new journal.
The other approach would be using a staging table , insert everything from the excel into that table and try applying the sorting/grouping on the staging table before actually creating the production route journal.

When you are inserting the data (example for select line), check if there is already a journal with that production order and opr num. If there is a line, don’t create a new journal and use the existing journal. If there is no line, then create a new journal.
can you give me an example for this, kranthi?

Before doing the insert do a select on ProdJournalRoute and to see if the record exists,
Example :
ProdJournalRoute prodJournalRouteFind;
select firstonly JournalId from prodJournalRouteFind
where prodJournalRouteFind.ProdId == prodId // prodId from excel
&& prodJournalRouteFind.OprNum = oprNum; //Opr num from excel

if (!prodJournalRouteFind.JournalId )
{
// create a new journal
}
else
{
// use the existing journal
}

prodId and oprid from excel, how to get this in our code…

how to get column length from excel using code?

You are already inserting them in your code.

select firstonly JournalId from prodJournalRouteFind
where prodJournalRouteFind.ProdId ==prodId // prodId from excel
&& prodJournalRouteFind.OprNum == oprNum; //Opr num from excel

if (!prodJournalRouteFind.JournalId )
{
row++;

PJTab.JournalType =ProdJournalType::RouteCard ;
PJTab.ProdId=cells.item(row,1).value().bStr();
PJTab.JournalNameId = ProdJournalName::standardJournalName(ProdJournalType::RouteCard,PJTab.ProdId);
PJTab.Description =ProdJournalName::find(PJTab.JournalNameId).Description;
PJTab.insert();

do{
ttsbegin;
row++;
PJR.JournalId = PJTab.JournalId;
PJR.OprNum = cells.item(row,2).value().double();
PJR.TransDate=cells.item(row,3).value().date();
PJR.OprFinished=cells.item(row,4).value().boolean();
PJR.QtyError = cells.item(row,6).value().double();
PJR.QtyGood = cells.item(row,5).value().double();
PJR.EmplId= cells.item(row,7).value().bStr();
//Initialize lineNum to ‘0’ else inventJournalTrans.insert() method wouldn’t create a new lineNum for each insertion
PJR.LineNum=0;
//parameter for insert method
PJR.insert();
//update type variable to next row in file
type= cells.item(row+1,1).value().variantType();
ttscommit;
}
while(type != COMVariantType::VT_EMPTY);
}
else
{
warning(strfmt("Prodid already exists %1 : ",PJR));
}

I got a error has prodid not declared…

This is what i mean Prod Id from excel. You need to use that value in the select statement.

No, Kranthi.
I dont need to check tat journal and prodid was already exist…
i just need to create a new jorunal for prodid’s…

PRD_00000174 lines as oprnum 10,20
PRD_00000172 lines as oprnum 20

Like this new journals need to be created from excel sheets…

Store ur ProdId in one variable…Then check it with the upcoming values…

For example…
If(prev == curr)
{
skip
create records only for lines…Bcz header already exists.
}
else
{
insert
Create records for both header and lines…
}

or

As kranthi said u can check with the table also…

This will work if the excel has the data sorted by ProdId.

static void import_routecard_journal(Args _args)
{
    SysExcelApplication             application;
    SysExcelWorkbooks               workbooks;
    SysExcelWorkbook                workbook;
    SysExcelWorksheets              worksheets;
    SysExcelWorksheet               worksheet;
    SysExcelCells                   cells;
    SysExcelRange                   range;
    COMVariantType                  type;
    FilenameOpen                    filename;
    dialogField                     dialogFilename;
    Dialog                          dialog;
    int                             row;
    int                             lines;
    str                             jid;
    boolean                         flag;
    //Table declaration
    ProdJournalRoute                PJR, PJRC, prodJournalRouteFind;
    ProdJournalTable                PJTab, table;
    //Field declaration
    ProdJournalIdRoute              Journalid;
    JournalTransDate                transdate;
    OprNum                          OprNum;
    ProdId                          ProdId,prevprodid, nextProdId;
    ProdReportedGood                qtygood;
    ProdReportedError               qtyerror;
    EmplId                          EmplId;
    ProdFinishedOpr                 OprFinished;
    ;

    dialog          =   new Dialog("Upload excel file");
    dialogFilename  =   dialog.addField(typeId(FilenameOpen));
    dialog.filenameLookupTitle("Upload from Excel");
    dialog.caption("Excel upload");
    dialogFilename.value(filename);
    if(!dialog.run())
    return;
    filename    =   dialogFilename.value();
    info(filename);
    application =   SysExcelApplication::construct();
    workbooks   =   application.workbooks();

    try{
     workbooks.open(filename);
    }
    catch(Exception::Error){
     throw error("Cannot open the file. Check if file exists in location ");
    }
    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();
    row = 1;
    ttsbegin;
    do
    {
    row++;
    ProdId= cells.item(row,1).value().bStr();
    prevprodid=cells.item(row-1,1).value().bStr();
    nextProdId = cells.item(row+1,1).value().bStr();
    OprNum = cells.item(row,2).value().double();
    TransDate=cells.item(row,3).value().date();
    OprFinished=cells.item(row,4).value().boolean();
    QtyGood = cells.item(row,5).value().double();
    QtyError = cells.item(row,6).value().double();
    EmplId= cells.item(row,7).value().bStr();

    /*select firstonly recid from prodJournalRouteFind
    where prodJournalRouteFind.ProdId == prodId // prodId from excel
    && prodJournalRouteFind.OprNum == oprNum; //Opr num from excel

    if (!prodJournalRouteFind.JournalId )
    {*/
    if(prevprodid!=prodid &&  row >= 2)
    {
    //create a new header
    PJTab.JournalType =ProdJournalType::RouteCard;
    PJTab.JournalNameId = ProdJournalName::standardJournalName(ProdJournalType::RouteCard,PJTab.ProdId);
    PJTab.Description =ProdJournalName::find(PJTab.JournalNameId).Description;
    PJTab.ProdId=prodid;
    pjtab.initValue();
    PJTab.JournalId="";
    PJTab.insert();
    flag = True;
    //info(strfmt("Creating new header JID:%1, PRD:%2",PJTab.JournalId, PJTab.ProdId));
    jid=PJTab.JournalId;
    //PJTab.JournalId="";
    }
    if((prevprodid==prodid && prevprodid!= "" && row >2)||(row==2)||(flag))
    {
    //populate line and insert
     pjr.ProdId = prodid;
     pjr.JournalId = jid;
     pjr.OprNum = oprNum;
     pjr.TransDate = TransDate;
     pjr.QtyGood = QtyGood;
     pjr.QtyError = QtyError;
     pjr.OprFinished = OprFinished;
     pjr.insert();
     flag = False;
     info(strfmt("jourID->PRD->Line : %1 -> %2 -> %3",pjr.JournalId, pjr.ProdId, pjr.LineNum));
     pjr.LineNum = 0;
     if(pjr.ProdId != nextProdId)
     {
        select count(recid) from pjrc where pjrc.JournalId == pjr.JournalId && pjrc.ProdId == pjr.ProdId;
        lines = pjrc.RecId;
        info(strfmt("PRD: %1. lines: %2",pjr.ProdId,lines));

        select forupdate PJTab where Pjtab.JournalId == pjr.JournalId && pjtab.ProdId == pjr.ProdId;
        pjtab.NumOfLines = lines;
        pjtab.update();
     }
    }

   /* else
    {
    warning("Journal id already exists");
    }
*/
    type = cells.item(row+1,1).value().variantType();
    }
     while(type != COMVariantType::VT_EMPTY);
    ttscommit;
}

i tried like this, whether this is correct?