/// d ##class(apptools.python.xlsx).test()Class apptools.python.xlsx
{

Parameter DATADIRECTORY = "/home/irisowner/dev/data/";/// d ##class(apptools.python.xlsx).test()ClassMethod test(name As%String = "titanicTest") As%Status
{
 set name=name_$INCREMENT(^RunAppTest(name))
 set sql="select * FROM dc_demo.titanic"
#;  set sql="select name,sex,age FROM dc_demo.titanic"set gn="^||tmp",format=",n,n,n,n,s5,s5,s40,s5,s50"set format("freeze")="B3";freezeset format("title")="Title "_sql
 set format("sheetname")="test"
#;  do ##class(apptools.core.sys).SaveSQL(sql,gn)set exec="##class(apptools.python.xlsx).MarkRed(.%AppLogInfoVal, .%AppLogInfoCol, .%AppLogInfoHead, .%AppLogInfoTemp)"
#;  do ##class(apptools.python.xlsx).gn2xlsx(gn,.format,..#DATADIRECTORY_"___gn2xlsx*.xlsx","Title "_sql,,,,exec)set format("freeze")="B4";freezedo##class(apptools.python.xlsx).sql2xlsx(sql,.format,..#DATADIRECTORY_name_".xlsx")
 do..toPDF(name)
 return$$$OK
}

ClassMethod sql2xlsx(sql, format = "", file As%String = "/home/irisowner/dev/data/sample-py-*.xlsx", title = "")
{
	set st=$$$OKset statement = ##CLASS(%SQL.Statement).%New()
	set status=statement.%Prepare(sql)
	if$$$ISERR(status) {write"%Prepare failed:"do$SYSTEM.Status.DisplayError(status) quit}

	set rset=statement.%Execute()
	if (rset.%SQLCODE '= 0) {write"%Execute failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Messagequit}

	;prepare XLSXtry {
        set openpyxl = ##class(%SYS.Python).Import("openpyxl") 
    } catch err {
        do..InstallPy("openpyxl")
        set openpyxl = ##class(%SYS.Python).Import("openpyxl") 
    }
	set wb = openpyxl.Workbook()
	#; grab the active worksheetset ws = wb.active
	set ws.title = $select($D(format("sheetname"),ds):ds,1:"sheetname")
	set pybuiltins = $system.Python.Import("builtins")
	set pycols = pybuiltins.list()
	do pycols.append(" ")
	do ws.append(pycols)

	set pycols = pybuiltins.list()
	set (count)=0set ColCount=statement.%Metadata.columns.Count()
	for i=1:1:ColCount {	
		do pycols.append(statement.%Metadata.columns.GetAt(i).colName)
		set col=openpyxl.utils."get_column_letter"(i)
	}
	do ws.append(pycols)

	while (rset.%Next())	{
		set count=count+1set pyrows = pybuiltins.list()
		for ii=1:1:ColCount {
			set val=..CleanCtrl(rset.%GetData(ii))
			if$e($P(format,",",ii),1)="d" { //dateset val=..ToPyDate(val)
			}
			elseif$e($P(format,",",ii),1,2)="dt" { //datetimeset val=..ToPyDateTime(val)
			}
			elseif$e($P(format,",",ii),1)="n" { //numberset val=+val
			}
			do pyrows.append(val)
		}
		do ws.append(pyrows)
	}
   ;}if$g(format("freeze"))'="" {
		; set builtins = $system.Python.Import("builtins") zwrite builtins.type(ws)  zwrite builtins.dir(wb)set ws."freeze_panes" = pybuiltins.str(format("freeze")) ;format("freeze");set ws."freeze_panes" = "B2" ;format("freeze")
	}
	else {
		set ws."freeze_panes" = pybuiltins.str("B2") ;default
	}
	if file["*"set file=$replace(file,"*","-"_$zd($h,3)_"_"_$tr($zt($p($h,",",2),1),":"))
	write !,"Save into "_file_" rows: "_count
	do wb.save(file)
	
	return$GET(count)
}

ClassMethod toPDF(file As%String) As%Status [ Language = python ]
{
  import pandas as pd
  from csv2pdf import convert
  PATH = '/home/irisowner/dev/data/'
  PATH_TO_XLSX = PATH+file+'.xlsx'
  PATH_TO_CSV = PATH+file+'.csv'
  PATH_TO_PDF = PATH+file+'.pdf'
  
  read_file = pd.read_excel (PATH_TO_XLSX)
  read_file.to_csv (PATH_TO_CSV, index = None, header=True)
  convert(PATH_TO_CSV , PATH_TO_PDF)
}

/// set gn="^||tmp",format="n,s150,,,,,,,,d," /// set format("freeze")="B5" ;freeze/// d ##class(apptools.core.sys).SaveSQL("select * from apptools_core.Log order by id desc",gn)/// d ##class(apptools.python.xlsx).gn2xlsx(gn,.format,"/iris-backup/temp/test*.xlsx","Test")/// Example coloring a column values if is not null///  set exec="##class(apptools.python.xlsx).MarkRed(.%AppLogInfoVal, %AppLogInfoCol, %AppLogInfoHead, .%AppLogInfoTemp)"ClassMethod MarkRed(Val, Col, Head, openpyxl)
{
	s res=Val
	if$g(Head) {
		if res.value="DispatchClass"set Col("DispatchClass",Col)=""
	}
	else {
		if$Data(Col("DispatchClass",Col)) {
			if res.value'="" {
				;set argsfill = {"start_color":"D3D3D3", "end_color":"D3D3D3", "fill_type":"solid"};set fill = openpyxl.styles.PatternFill(argsfill...)	set argsfont = {"color":"DC143C", "bold":true, "italic":false, "size":11} ;color Crimsonset font = openpyxl.styles.Font(argsfont...)
				;set res.value=$FN(res.value,"",2);set res.fill=fillset res.font=font
			}
		}
	}
	q res
}

/// do ##class(apptools.python.xlsx).InstallPy("openpyxl")ClassMethod InstallPy(lib)
{
    //  depricated  ;set sc = ##class(%SYS.Python).Install("openpyxl")set cmd="pip3 install --target /usr/irissys/mgr/python/ "_lib
    if$zversion(1)'=3 {
        set tMgrDir = $System.Util.ManagerDirectory()
		set tBinDir = $System.Util.BinaryDirectory()_"irispip.exe"set cmd=tBinDir_" install --target "_tMgrDir_"python "_lib
    }
    set st=##class(%ZPM.PackageManager.Developer.Utils).RunCommandViaZF(cmd,.tLog,.tErr)
    quit$$$OK
}

/// Date YYYY-MM-DD HH:MM:SS to Python dt.datetimeClassMethod ToPyDateTime(val, ByRef dt)
{
	quit:'val val
	if val["."set val=$p(val,".",3)_"-"_$p(val,".",2)_"-"_$p(val,".",1) // format YYYY-MM-DDif val'["-"set val=$zd(+val,3) // $h formatset dt = ##class(%SYS.Python).Import("datetime")
	set val=dt.datetime(+$p(val,"-",1), +$p(val,"-",2), +$p($p(val,"-",3)," "),+$p($p(val,":",1)," ",2),+$p($p(val,":",2)," ",2),+$p($p(val,":",3)," ",2))
	quit val
}

/// Date YYYY-MM-DD to Python dt.dateClassMethod ToPyDate(val, ByRef dt)
{
	quit:'val val
	if val["."set val=$p(val,".",3)_"-"_$p(val,".",2)_"-"_$p(val,".",1) // format YYYY-MM-DDif val'["-"w111_val_111 set val=$zd(+val,3) // $h formatset dt = ##class(%SYS.Python).Import("datetime")
	set val=dt.date(+$p(val,"-",1), +$p(val,"-",2), +$p($p(val,"-",3)," "))
	quit val
}

ClassMethod CleanCtrl(val)
{
	quit$zstrip(val,"*C")
}

}