Read CSV file stored in database

 Local File &MYFILE;

Local array of string &ARRAY;

Local Record &REC;

Local Rowset &studentList ;

&studentList = GetLevel0()(1).GetRowset(Scroll.L_TRNS_CRSE_TBL);

&REC = CreateRecord(Record.L_TRNSCRS_FILE);

SQLExec("SELECT ATTACHSYSFILENAME FROM PS_L_TRNS_CRS_ATCH WHERE OPRID =:1 AND RUN_CNTL_ID=:2", %UserId, L_TRNS_CRS_ATCH.RUN_CNTL_ID.Value, &FileName);

rem Messagebox(0,"",0,0,""|&FileName);

&SQL = CreateSQL("%SelectAll(:2)  WHERE ATTACHSYSFILENAME =:1 ", &FileName, Record.L_TRNSCRS_FILE);

While &SQL.Fetch(&REC)  

   &FILE1 = GetFile(&FileName, "w", "a", %FilePath_Absolute);

    rem Messagebox(0,"",0,0, " | %FilePath_Absolute | "File Open:" | &FILE1.IsOpen);

   

   If &FILE1.IsOpen Then

      try

         &FILE1.WriteRaw(&REC.FILE_DATA.Value);

         &FILE1.Close();

         

      catch Exception &ex2

         throw CreateException(0, 0, "Unable to write  file: " | &ex2.ToString());

      end-try;

   End-If;

End-While;

&MYFILE = GetFile(&FileName, "r", "a", %FilePath_Absolute);

&ARRAY = CreateArrayRept("", 0);

rem MessageBox(0, "", 0, 0, "" | &MYFILE.IsOpen);

If &MYFILE.IsOpen Then

   If &MYFILE.SetFileLayout(FileLayout.L_TRNS_CRSE_MNL_FL) Then

            While &MYFILE.ReadLine(&STRING);

               &ARRAY = Split(&STRING, ",");

         For &i = 1 To &ARRAY.Len

            MessageBox(0, "", 0, 0, "" | ("Value of &ARRAY[" | &i | "]: " | &ARRAY [&i]));

         End-For;

         

         End-while;

end-if;

end-if;

how to create JSON using PeopleCode

 Below is a simple JSON example that uses the JSONObject class. 

{"invoiceHeader":   {

        "CompanyNo": 685,

        "InvoiceNo": "90000021",

        "InvoiceType": 388,

        "InvoiceDate": "2024-01-25",

        "InvoiceTime": "15:30:00",

        "UUID": "dcc3e906-42c0-4741-b871-16f63f8a2686",

        "CustomerName": "EMT Company2",

        "CustomerVATNumber": "302233808500003",

        "CustomerCRNumber": "1010627830",

        "CustomerStreetName": "Street Name",

        "CustomerBuildingNumber": "1234",

        "CustomerZipCode": "12345",

        "CustomerCityName": "City Name",

        "CustomerDistrictName": "District Name",

        "CustomerRegionName": "Region Name",

        "InvoiceSubTotal": 1100.00,

        "InvoiceDiscount": 100.00,

        "InvoiceVAT": 150.00,

        "InvoiceNetTotal": 1150.00,

        "FromInvoiceNo": "00000003",

        "InvoiceVATType": "01"

    },

    "invoiceDetails": [

        {

            "RowNo": 1,

            "ServiceName": "Villa rent for 6 months",

            "UnitPrice": 500.00,

            "Q": 2,

            "NetTotal": 1000.00

        },

        {

            "RowNo": 2,

            "ServiceName": "Another Service",

            "UnitPrice": 300.00,

            "Q": 3,

            "NetTotal": 900.00

        },

     {

            "RowNo": 3,

            "ServiceName": "Another Service",

            "UnitPrice": 300.00,

            "Q": 3,

            "NetTotal": 900.00

        }

    ]

}

------------------------------------------------------------------------------------------------------------------------

Global File &fileLog;

Global string &FileName;

&FileName = "Logging for posting of invoices from staging to zatca..." | ".TXT";

&fileLog = GetFile(&FileName, "W");

try

   

   &request = CreateMessage(Operation.T_ZATCA_INVC_OPR_POST);

   &request.IBInfo.IBConnectorInfo.ConnectorName = "HTTPTARGET";

   &request.IBInfo.IBConnectorInfo.ConnectorClassName = "HttpTargetConnector";

   

   &bOK = &request.IBInfo.IBConnectorInfo.AddConnectorProperties("Method", "POST", %Property);

   &bOK = &request.IBInfo.IBConnectorInfo.AddConnectorProperties("authentication_type", "CompanySecret", %HttpHeader);

   &bOK = &request.IBInfo.IBConnectorInfo.AddConnectorProperties("CompanySecret", "123456nh#=", %HttpHeader);

   &bOK = &request.IBInfo.IBConnectorInfo.AddConnectorProperties("Content-Type", "application/json", %HttpHeader);

   &rootJSON = CreateJsonObject();

   Local SQL &sqlHeader, &sqlDtl;

   Local Record &recHeader = CreateRecord(Record.T_ZATCA_REQ);

   Local Record &recDtl = CreateRecord(Record.T_ZATCA_REQ_DTL);

   Local Record &respRec = CreateRecord(Record.T_ZATCA_RESP);

   &sqlHeader = CreateSQL("%SelectAll(:1) WHERE T_ZATCA_PSTNG_STTS!=:2", &recHeader, "Posted");

   

   While &sqlHeader.Fetch(&recHeader)

      Local string &ZATCA_UUID = "";

      &ZATCA_UUID = UuidGenBase64();

      &rootJSON.AddProperty("CompanyNo", &recHeader.Z_COMPANY_NO.Value);

      &rootJSON.AddProperty("InvoiceNo", &recHeader.INVOICE_ID.Value);

      Local number &type = &recHeader.Z_INVOICE_TYPE.Value;

      &rootJSON.AddProperty("InvoiceType", &type);

      &rootJSON.AddProperty("InvoiceDate", &recHeader.DATE1.Value);

      &rootJSON.AddProperty("InvoiceTime", &recHeader.Z_INVOICE_TIME.Value);

      &rootJSON.AddProperty("UUID", &ZATCA_UUID);

      &rootJSON.AddProperty("CustomerName", &recHeader.Z_CUST_NAME.Value);

      &rootJSON.AddProperty("CustomerVATNumber", &recHeader.I_VAT_NO.Value);

      &rootJSON.AddProperty("CustomerCRNumber", &recHeader.Z_CR_NUM.Value);

      &rootJSON.AddProperty("CustomerStreetName", &recHeader.Z_STREET_NAME.Value);

      &rootJSON.AddProperty("CustomerBuildingNumber", &recHeader.Z_BUILDING_NO.Value);

      &rootJSON.AddProperty("CustomerZipCode", &recHeader.Z_ZIP_CODE.Value);

      &rootJSON.AddProperty("CustomerCityName", &recHeader.CITY.Value);

      &rootJSON.AddProperty("CustomerDistrictName", &recHeader.DISTRICT.Value);

      &rootJSON.AddProperty("CustomerRegionName", &recHeader.Z_REGION.Value);

      &rootJSON.AddProperty("InvoiceSubTotal", &recHeader.Z_SUBTOTAL.Value);

      &rootJSON.AddProperty("InvoiceDiscount", &recHeader.Z_DISCOUNT.Value);

      &rootJSON.AddProperty("InvoiceVAT", &recHeader.Z_VATAMOUNT.Value);

      &rootJSON.AddProperty("InvoiceNetTotal", &recHeader.Z_NETTOTAL.Value);

      &rootJSON.AddProperty("FromInvoiceNo", "00000000");

      &rootJSON.AddProperty("InvoiceVATType", &recHeader.Z_INVOICE_VATTYPE.Value);

      

      &sqlDtl = CreateSQL("%SelectAll(:1) WHERE INVOICE_ID=:2", &recDtl, &recHeader.INVOICE_ID.Value);

      

      &recDtlArray = CreateJsonArray();

      Local boolean &isDetailExist = False;

      While &sqlDtl.Fetch(&recDtl)

         &childJSON = CreateJsonObject();

         &childJSON.AddProperty("RowNo", &recDtl.Z_ROWNO.Value);

         &childJSON.AddProperty("ServiceName", &recDtl.Z_SERVICENAME.Value);

         &childJSON.AddProperty("UnitPrice", &recDtl.Z_UNITPRICE.Value);

         &childJSON.AddProperty("Q", &recDtl.Z_QUANTITY.Value);

         &childJSON.AddProperty("NetTotal", &recDtl.Z_SRVCE_NETTOTAL.Value);

         &recDtlArray.AddJsonObjectElement("", &childJSON);

         &isDetailExist = True;

      End-While;

      

      If &isDetailExist Then

         &finalJson = CreateJsonObject();

         &finalJson.AddProperty("invoiceHeader", &rootJSON);

         &finalJson.AddJsonArray("invoiceDetails", &recDtlArray);

         &rootJSONStr = &finalJson.ToString();

         

         &request.URIResourceIndex = 1;

         &bRet = &request.SetContentString(&rootJSONStr);

         

         &response = %IntBroker.SyncRequest(&request);

         &jsonRespStr = &response.GetContentString();

         &parser = CreateJsonParser();

         &ret = &parser.Parse(&jsonRespStr);

         &jsonResp = &parser.GetRootObject();

         rem &fileLog.WriteLine("Final Packet--->" | &rootJSONStr);

         

         Local boolean &isSubmitted = False;

         

         try

            &isSubmitted = &jsonResp.GetProperty("IsReportedToZatca");

         catch Exception &innerExc

            &isSubmitted = False;

         end-try;

         rem Warning "Reported to zatca-->" | &jsonRespStr;

         If All(&jsonRespStr) And

               &isSubmitted Then

            rem &respRec.BILL_DATE_TIME.Value = &jsonResp.GetProperty("SubmissionDate");

            &respRec.BILL_DATE_TIME.Value = DateTimeValue(&jsonResp.GetProperty("SubmissionDate"));

            &respRec.INVOICE_ID.Value = &recHeader.INVOICE_ID.Value;

            &respRec.Z_INVOICEHASH.Value = &jsonResp.GetProperty("InvoiceHash");

            &respRec.Z_QRCODE.Value = &jsonResp.GetProperty("QrCode");

            &respRec.Z_REPORTEDTOZATCA.Value = "Yes";

            &respRec.Z_REPORTINGRESULT.Value = &jsonResp.GetProperty("ReportingResult");

            &respRec.Z_REPORTINGSTATUS.Value = &jsonResp.GetProperty("ReportingStatus");

            &respRec.Z_SIGNEDXML.Value = &jsonResp.GetProperty("SignedXml");

            &respRec.Z_UUID.Value = &ZATCA_UUID;

            &respRec.Z_WARNINGMSG.Value = &jsonResp.GetProperty("WarningMessages");

            &respRec.T_CODE.Value = &jsonResp.GetProperty("code");

            &respRec.Insert();

            SQLExec("UPDATE PS_T_ZATCA_REQ SET T_ZATCA_PSTNG_STTS=:1,T_ZATCA_POSTED_DTT=:2,Z_UUID=:3 WHERE INVOICE_ID=:4", "Posted", %Datetime, &ZATCA_UUID, &recHeader.INVOICE_ID.Value);

            &fileLog.WriteLine("Invoivice ID[" | &recHeader.INVOICE_ID.Value | "] Posted Successfully.");

         Else

            &fileLog.WriteLine("The inovice not posted. Following are the details");

            &fileLog.WriteLine("Inovioce Number--------->" | &recHeader.INVOICE_ID.Value);

            &fileLog.WriteLine("Zatca Invoice Response---->" | &jsonRespStr);

            &fileLog.WriteLine("---------------------End------------------------------------");

         End-If;

      Else

         &json = "";

         &fileLog.WriteLine("The inovice not posted. Following are the details");

         &fileLog.WriteLine("Inovioce Number--------->" | &recHeader.INVOICE_ID.Value);

         &fileLog.WriteLine("Resaon------------------>Invoice details missing");

         &fileLog.WriteLine("---------------------End------------------------------------");

         rem Warning "There is no invoice details";

      End-If;

      

   End-While;

   rem DoSave();

   &sqlHeader.Close();

   &sqlDtl.Close();

   

   

catch Exception &exc;

   rem Warning "Error-->" | &exc.ToString();

   &fileLog.WriteLine("The inovice(s) are not posted. Following are the details");

   &fileLog.WriteLine("Error Message:-->" | &exc.ToString());

   &fileLog.WriteLine("---------------------End------------------------------------");

end-try;