【Publisher】A custom web interface for printing invoices

# Introduction #
I found an interesting customization with Sharperlight Publisher (Web Report), so I'm writing this post to share it.
The customization allows displayed invoices to be selected and saved as a PDF file on the server where the Sharperlight service is running. A prompt can be displayed on the client side to initiate printing.
I use SAPB1 datamodel to use data from SAPB1 demo database.
To achieve it, the following objects need to be prepared.
- Three published queries
    - for displaing a list of invoices
    - for generating PDF file
    - for checking if the PDF file is ready
    - for the dataset when `Print All` button is chosen
- Custome HTML and CSS
- Two tasks
    - for generating PDF and save it to the file system on the server
    - for generating PDF with `Print All` option and save it to the file system on the server

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0037cdbc-1fd9-8bfe-d89a-271356fbbde1.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/10aed796-587f-9d46-ca6f-d2287aa3ae77.png)


# Designing Queries #
### The First Published Query ###
This has a query retrieves a list of invoices.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d82d74f4-8866-fa8e-c696-dbf4cb41e150.png)

The definition is here.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6613ac3e-68d2-feec-45ba-4f166f2cb551.png)

1. Database and Table
`SAP Business One` is specified as the **Product**, and `A/R Sales Invoice Header` is selected as the **Table**.
2. Prompt filters (with a yellow background color)
They are not used in the generated SQL statement but function as parameters or constants.
The provided values are used in the custom HTML defined in the background of the web report at runtime.
3. Output Items
They define each column in the table of the web report.
The expression **Select** creates the checkbox column.
The other columns are chosen from Selection list on the left.

This published query becomes the main report and includes the custom HTML and CSS.
The custom HTML is imported into this report as a resource and its reference code is embedded in the Header option.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3a3ced3a-bb3a-6c6f-bdf8-e0b111d643d6.png)
The custom CSS is directory written in the CSS option.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/19c5411e-5bac-3322-2bd5-7c21c516a86f.png)

### The Second Published Query ###
This generates a formatted invoice suitable for PDF output. It is used by a Sharperlight scheduled task to create a PDF file based on specified parameters and save it to a secure folder on the server.
The `Default Type` option for this published query must be set to **Page PDF**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7062d6d9-b800-fdef-cc5f-97d0290dd1d8.png)
The `Page Designer` option is used to create formatted invoices.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e299e09a-3e0c-9ef7-4f0c-f24a3d899267.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/88735953-8fa4-5fe1-c2ad-ed57acad955e.png)

### The Third Published Query ###
This query checks if the PDF file has been created in the secure folder on the server. It is called by the custom HTML in the main report via the Sharperlight RESTful service, and a print prompt is displayed when it confirms that the PDF is ready for printing.

The part where this query is called in the custom HTML is shown below.
```
:
:
const startTime = Date.now();
let nowTime = 0;

while(!fileExist()){    // Waiting until PDF file is generated
    nowTime = (Date.now() - startTime) / 1000;
    if(nowTime > Number(gTimeoutInSec)){
        processing(false);
        alert("Process Time Out");
        return;
    }
}
:
:
var gURL_FileExistCheck = "{*Url.Root}DataSource/?query=" + gPublisherGroup + ".ivPrintTaskCompleteCheck&fltFilename=[[FILENAME]]&dfmt=jsonarray&dcat=UseNames&usid={_System.Rest.Usid}";
function fileExist(){
    try{
        $.ajax({
            async: false,
            url: gURL_FileExistCheck.replace('[[FILENAME]]', gMergeFileName),
            success: function (result) {
                if (result.length > 0){
                    if(result[0].Filename == gMergeFileName){
                        gIsExist = true;
                        return;
                    }else{
                        return;
                    }
                }
            }
        });
        return gIsExist;
    }catch (err){
        throw 'Error at fileExist(): ' + err.message;
    }
}
:
:
:
```
### The Fourth Published Query ###
This query retrieves a dataset based on the filters provided when the 'Print All' button is clicked. The dataset is then sent to a scheduled task, which generates a combined PDF file containing all invoices extracted using the specified filters.



# Customise - HTML & CSS #
Custom HTML and CSS add functional buttons and their backend events to the main web report mentioned above, providing users with additional features beyond the standard Sharperlight web report.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3b8b9fd8-82e3-eccb-b021-d6f3b59f8159.png)

**Select All** button selects all checkboxes in the Select column, but this action applies only to the checkboxes on the current visible page.
The **Deselect All** button performs the opposite action of the **Select All** button.
The **Print** button triggers the generation of a PDF file for the selected invoice(s) and displays a print prompt once the PDF is ready.
The **Print All** button functions similarly to the **Print** button, but it applies to all invoices (across all pages) in the web report query results.

# Tasks - Generating invoice(s) in PDF #
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/54cd89ab-a768-06fd-2419-f1d5280c1ec4.png)

A PDF file is generated by a Sharperlight scheduled task (PDFGenerator), using a template published query referred to as The Second Published Query above. The task saves the PDF file to a secure folder registered in the Sharperlight configuration. Another scheduled task (PDFGeneratorAll) is triggered when the Print All action is performed.

# Afterword #
I’ve tried to explain how the solution is created and how it works, but it might be complicated.

Essentially, this solution is a combination of several Sharperlight features, and it can be built by fewer people in less time than if we were to develop it from scratch. If you have an idea for combining features like this, it’s not too difficult to implement, right?

I’ve also included the backend code here. Please note that it's just a sample and not commercial-quality code.

#### HTML & JavaScript ####
```
<script type="text/javascript" src="File/Custom/printjs/print.min.js"></script>
<link rel="stylesheet" type="text/css" href="File/Custom/printjs/print.min.css"></link>

<script>
    // QiitaSample ARInvoices

    var gPublisherGroup = "QiitaSample";
	var gCallbackPage = "Homepage";
	var gTargetService='{*Url.Root}';
    var gTaskName = gPublisherGroup + ".PDFGenerater";
    var gTaskNameAll = gPublisherGroup + ".PDFGeneraterAll";
    
    var gURL_FileExistCheck = "{*Url.Root}DataSource/?query=" + gPublisherGroup + ".ivPrintTaskCompleteCheck&fltFilename=[[FILENAME]]&dfmt=jsonarray&dcat=UseNames&usid={_System.Rest.Usid}";
    var gTargetDateFormat = "{*Filter[@TargetDateFormat]}".replace(/'/g, "\\'");
    var gTargetDateRegion = "{*Filter[@TargetDateRegion]}".replace(/'/g, "\\'");
    var gTimeoutInSec = "{*Filter[@TimeoutInSec]}".replace(/'/g, "\\'");
    
    function btApprSelectAll_click(source, mode) {
        try{
            _toggle_Selection(true, mode);
        }catch (err){
            throw 'Error on btApprSelectAll_click(): ' + err.message;
        }
    };
    
    function btApprDeselectAll_click(source, mode) {
        try{
            _toggle_Selection(false, mode);
        }catch (err){
            throw 'Error on btApprDeselectAll_click(): ' + err.message;
        }
    };

    function _toggle_Selection(state, mode){
        try{
            $("input[name*='selectedDocuments']").prop("checked", state);
        }catch (err){
            throw 'Error on _toggle_Selection(): ' + err.message;
        }
    }
    

    //=====================================================================
    var gMergeFileName = "MergeFile.PDF"
    var gIsExist = false;
    var gTKcallback = {
        success: getCallBackOK,
        failure: getCallBackNG
    };
    var gCheckedItems = [];
    var gListOfDocuments = [];
    var gListOfEnteredFilter = [];
    //=====================================================================
    function btPrint_click(source, mode) {
        try{
            gCheckedItems = [];
            gListOfDocuments = [];
            gListOfEnteredFilter = [];
            var checkedItems = [];
            var checkboxes;
            
            //Fetch all checkbox controls
            checkboxes = $("input[name*='selectedDocuments']");
            if (checkboxes.length == 0){
                return;
            }
            
            processing(true);
            
	
            //Find out document numbers and document entry 
            for(var i=0; i<checkboxes.length; i++) {
                if(checkboxes[i].checked){
                    var checkedItem = {
                        'docNumber': checkboxes[i].dataset.docnumber,
                        'fltDocNumber1': checkboxes[i].dataset.filterdocnumber1,
						'fltDocNumber2': checkboxes[i].dataset.filterdocnumber2,
						'fltPostingDate1': checkboxes[i].dataset.filterpostingdate1,
						'fltPostingDate2': checkboxes[i].dataset.filterpostingdate2,
						'fltCustomerType': checkboxes[i].dataset.filtercustomertype,
                        'fltCompanyName': checkboxes[i].dataset.filtercompanyname,
                    }
                    gCheckedItems.push(checkedItem);
                }
            }
    
			//**************************************************************
            // Find out which documents are selected, and trigger the task
            // to generate PDF files for downloading
            //**************************************************************
            var listOfDocNums ="";
            if (gCheckedItems.length > 0){
				for (var i=0; i<gCheckedItems.length;i++){
                    if(i==0){
					    listOfDocNums = gCheckedItems[i].docNumber;
                    }else{
                        listOfDocNums += "," + gCheckedItems[i].docNumber;
                    }

                    let docNum = {
                        'companyname': gCheckedItems[i].fltCompanyName,
                        'documentnumber': gCheckedItems[i].docNumber
                    }
                    gListOfDocuments.push(docNum);
				}
                console.log("Selected Document Numbers: " + listOfDocNums);
                
                //**************************************************************
                // Execute PDF Generator (Scheduled Task)
                //**************************************************************
                gMergeFileName = "MergeFile-" +uniqueID_Get() + ".PDF";
                var zUrl = gTargetService + 'SchedulerTask?task=' + gTaskName // '&response=json&callback=MyFunc' 
                            + '&p1=DocNum:' + encodeURIComponent(listOfDocNums) 
                            + '&p2=MergeFileName:' + encodeURIComponent(gMergeFileName) 
                            + '&usid={_System.Rest.Usid}';
                var request = YAHOO.util.Connect.asyncRequest('GET', zUrl, gTKcallback);

			}else{
                processing(false);
            }
        }catch (err){
            processing(false);
            throw 'Error at btPrint_click(): ' + err.message;
        }
    };
    

    //=====================================================================
    // Callback Functions
    //=====================================================================
    function getCallBackOK(o){
        try{
            const startTime = Date.now();
            let nowTime = 0;

            console.log("OK " + o.responseText);
            while(!fileExist()){    // Waiting until merged file is generated
                nowTime = (Date.now() - startTime) / 1000;
                if(nowTime > Number(gTimeoutInSec)){
                    processing(false);
                    alert("Process Time Out");
                    return;
                }
            }
        }catch (err){
            processing(false);
            throw 'Error at getCallBackOK() 1: ' + err.message;
        }

        try{
            gIsExist = false;
            printJS(
                    {printable:gTargetService + 'File/Trusted/Temp/' + gMergeFileName,
                    type:'pdf',
                    showModal:true,
                    onPrintDialogClose: printJobComplete
                });

            processing(false);
        }catch(err){
            processing(false);
            throw 'Error at getCallBackOK() 2: ' + err.message;
        }
    }

    //===============
    function printJobComplete(){
        console.log('print job complete');
        window.location.reload();
    };
    function fileExist(){
        try{
            $.ajax({
                async: false,
                url: gURL_FileExistCheck.replace('[[FILENAME]]', gMergeFileName),
                success: function (result) {
                    if (result.length > 0){
                        if(result[0].Filename == gMergeFileName){
                            gIsExist = true;
                            return;
                        }else{
                            return;
                        }
                    }
                }
            });
            return gIsExist;
        }catch (err){
            throw 'Error at fileExist(): ' + err.message;
        }
    };
    //===============
    
    function getCallBackNG(o){
        processing(false);
        alert.log("NG " + o.responseText);
    }

    //=====================================================================
    function btPrintAll_click(source){
        try{
            gCheckedItems = [];
            gListOfDocuments = [];
            gListOfEnteredFilter = [];
            var checkedItems = [];
            var checkboxes;
            
            var enteredFilters = {
				'fltDocNumber1': "",
				'fltDocNumber2': "",
				'fltPostingDate1': "",
				'fltPostingDate2': "",
				'fltCustomer': ""
			}
            let src = decodeURIComponent(window.parent.document.getElementById('reportArea').src);
            let params = src.split("&");

            for(let p=0; p<params.length;p++){
                if (params[p].indexOf("fltDocNum=") > -1){
                    enteredFilters.fltDocNumber1 = params[p].replace("fltDocNum=","");
                }else if (params[p].indexOf("fltDocNum_2=") > -1){
                    enteredFilters.fltDocNumber2 = params[p].replace("fltDocNum_2=","");
                }else if (params[p].indexOf("fltPosDat=") > -1){
                    enteredFilters.fltPostingDate1 = params[p].replace("fltPosDat=","");
                }else if (params[p].indexOf("fltPosDat_2=") > -1){
                    enteredFilters.fltPostingDate2 = params[p].replace("fltPosDat_2=","");
                }else if (params[p].indexOf("fltCusVenCod=") > -1){
                    enteredFilters.fltCustomer = params[p].replace("fltCusVenCod=","");
                }else{

                }
            }

            if (enteredFilters.fltPostingDate1 != "" && isValidDate(enteredFilters.fltPostingDate1)){
                let xDate = stringToDate(enteredFilters.fltPostingDate1,gTargetDateFormat,"/");
                enteredFilters.fltPostingDate1 = xDate.toLocaleDateString(gTargetDateRegion);
            }
            if (enteredFilters.fltPostingDate2 != "" && isValidDate(enteredFilters.fltPostingDate2)){
                let xDate = new stringToDate(enteredFilters.fltPostingDate2,gTargetDateFormat,"/");
                enteredFilters.fltPostingDate2 = xDate.toLocaleDateString(gTargetDateRegion);
            }        

            gListOfEnteredFilter.push(enteredFilters);

            processing(true);

            
            let result = confirm("Please make sure you click the submit button before continuing with this action.\n" 
                        + "The filter you selected may not be applied yet.\n"
                        + "\nIf you are fine, please click OK to proceed for generating a PDF document.")
            if (!result){
                processing(false);
                return;
            }
            //**************************************************************
            // Execute PDF Generator (Scheduled Task)
            //**************************************************************
            gMergeFileName = "MergeFile-" +uniqueID_Get() + ".PDF";
            var zUrl = gTargetService + 'SchedulerTask?task=' + gTaskNameAll
                        + '&p1=DocNum:' + encodeURIComponent(enteredFilters.fltDocNumber1) 
                        + '&p2=DocNum2:' + encodeURIComponent(enteredFilters.fltDocNumber2)
                        + '&p3=PostDate:' + encodeURIComponent(enteredFilters.fltPostingDate1)
                        + '&p4=PostDate2:' + encodeURIComponent(enteredFilters.fltPostingDate2)
                        + '&p5=CusVenCod:' + encodeURIComponent(enteredFilters.fltCustomer)
                        + '&p6=MergeFileName:' + encodeURIComponent(gMergeFileName) 
                        + '&usid={_System.Rest.Usid}';
            var request = YAHOO.util.Connect.asyncRequest('GET', zUrl, gTKcallback);
        
        }catch (err){
            processing(false);
            throw 'Error on btPrintAll_click(): ' + err.message;
        }
    }
    
    //=====================================================================
    // Common Functions
    //=====================================================================
    function uniqueID_Get(){
        var currentdate = new Date(); 
        var id = '{*Filter[@CustomerCode]1}' + currentdate.getDate() + "-"
                + (currentdate.getMonth()+1)  + "-" 
                + currentdate.getFullYear() + "-"  
                + currentdate.getHours() + "-"  
                + currentdate.getMinutes() + "-" 
                + currentdate.getSeconds();
        return(id);
    }
    
    function processing(flg){
        if (flg){
            $('#btApprSelectAll').prop('disabled', true);
            $('#btApprDeselectAll').prop('disabled', true);
            $('#btPrint').prop('disabled', true);
            $('#btPrintAll').prop('disabled', true);



            var p = $('#btApprSelectAll').position();
            var xW = $('#btApprSelectAll').width();
            var xH = $('#btApprSelectAll').height();
            var bodyHeight = $('body').height();
            $("#processMessage").css({top: p.top, left: p.left, width: (xW+50)*8, height: bodyHeight, position:'absolute'});	
            $('#processMessage').show();
        }else{
            $('#btApprSelectAll').prop('disabled', false);
            $('#btApprDeselectAll').prop('disabled', false);
            $('#btPrint').prop('disabled', false);
            $('#btPrintAll').prop('disabled', false);
            $("#processMessage").css({top: 0, left: 0, width: 0, height: 0, position:'absolute'});	
            $('#processMessage').hide();
        }
    }
    
    function stringToDate(_date,_format,_delimiter){
            var formatLowerCase=_format.toLowerCase();
            var formatItems=formatLowerCase.split(_delimiter);
            var dateItems=_date.split(_delimiter);
            var monthIndex=formatItems.indexOf("mm");
            var dayIndex=formatItems.indexOf("dd");
            var yearIndex=formatItems.indexOf("yyyy");
            var month=parseInt(dateItems[monthIndex]);
            month-=1;
            var formatedDate = new Date(dateItems[yearIndex],month,dateItems[dayIndex]);
            return formatedDate;
    }
	
	function isValidDate(date) {
		return !isNaN(Date.parse(date));
	}


    //===========================================================
    
    </script>
    
    <div id='buttonBox'>
    <div><input class='button button1Position button1' id='btApprSelectAll' type='button' value='Select All' onClick='btApprSelectAll_click(this,"I")' /></div>
    <div><input class='button button2Position button2' id='btApprDeselectAll' type='button' value='Deselect All' onClick='btApprDeselectAll_click(this,"I")' /></div>
    <div><input class='button button3Position button3' id='btPrint' type='button' value='Print' onClick='btPrint_click(this,"I")' /></div>
	<div><input class='button button4Position button4' id='btPrintAll' type='button' value='Print All' onClick='btPrintAll_click(this,"I")' /></div>
    
    <div id="processMessage" style="display: none">
        <div id="processMessageText">Please wait .....
            <img id="processImage" src="{*Url.Root}File/Custom/Green-Spinner.gif" alt="Generating PDF file ..." />
        </div>
    </div>
    
    <div id="notification" style="display: none"></div>    
    <div id="hiddenboxforprinting" style="display: none"></div>
```
#### CSS ####
```
<style type="text/css">
body{
	font-family: Monospace;
}
#slBody{
	margin-left: 10px;
}
#sectionHeader{
	display: block;
}
#contentAlignLeft{
	margin-top:100px;
}
/* Making buttons *****************************************/
#buttonBox{
	position:absolute;
	left: 0px;
	top: 2px;
}
/* Common definition *********/
.button {   
	border-radius: 50%;
	width: 80px;
   	height: 80px;
   	display: flex;
   	justify-content: center;
   	align-items: center;
   	color: #808080;
   	text-decoration: none;
   	text-align: center;
   	font-size: 12px;
  	cursor: pointer;
  	outline: none;
  	border: none;
  	box-shadow: 0 5px #999;
}
/* Buttons Position *********/
.button1Position{
	position:absolute;
    	left: 10px;
}
.button2Position{
	position:absolute;
	left: 100px;
}
.button3Position{
	position:absolute;
	left: 190px;
}
.button4Position{
	position:absolute;
	left: 280px;
}

/* Buttons Behaviour ********/
.button1{
	background-color: #7EC7D8;
}
.button1:hover {
	background-color: #4eb3ca;
}
.button1:active {
	background-color: #4eb3ca;
	box-shadow: 0 5px #666;
	transform: translateY(4px);
}	

.button2{
	background-color: #7EC7D8;
}
.button2:hover {
	background-color: #4eb3ca;
}
.button2:active {
	background-color: #4eb3ca;
	box-shadow: 0 5px #666;
	transform: translateY(4px);
}	

.button3{
	background-color: #f0c2d0;
}
.button3:hover {
	background-color: #e184a0;
}
.button3:active {
	background-color: #e184a0;
	box-shadow: 0 5px #666;
	transform: translateY(4px);
}	

.button4{
	background-color: #f0c2d0;
}
.button4:hover {
	background-color: #e184a0;
}
.button4:active {
	background-color: #e184a0;
	box-shadow: 0 5px #666;
	transform: translateY(4px);
}


#processMessage {
  	z-index: 999999;
	opacity: 0.5;
  	transition: .5s ease;
  	background-color: lightgray;
}
#processMessageText{
	font-size: 36px;
	position: relative;
	top: 20px;
	left:30px;
	
}
#processImage{
	position: relative;
	top: 20px;
}


</style>
```