I doubt somewhere that there is "a lot of money" to be made from Alpha Five developers.
Could you elaborate a bit more on what you found difficult in the coding process (Alpha > .NET ? Or do you mean the logic of your app?) and how the documentation served you with that?
What drove your choice to using .NET in this specific case?
What drove your choice to using .NET in this specific case?
Code:
[FONT=Courier New]FUNCTION CreateSheet as V (Doc as Office::ExcelDocument,curCostcenter as c) ' Create a new spreadsheet. All formatting is done in FormatSheet() dim cn as sql::connection dim args as sql::arguments dim Sheet as Office::Spreadsheet ' Pointer to the spreadsheet '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' dim HeaderFormat as Office::Format dim ColHeaderFormat as Office::Format dim DetailFormat as Office::Format dim NumaricFormat as Office::Format dim Font as Office::Font dim LabelFont as Office::Font 'set base font Font = Doc.AddFont() Font.Color = Office::Color::Black Font.Name = "Calibri" Font.Size = 11 Font.Bold = .f. 'set TitleFont TitleFont = Doc.AddFont() TitleFont.Color = Office::Color::Green TitleFont.Name = "Calibri" TitleFont.Size = 12 TitleFont.Bold = .t. 'set ColHeadFont ColHeadFont = Doc.AddFont() ColHeadFont.Color = Office::Color::Black ColHeadFont.Name = "Calibri" ColHeadFont.Size = 11 ColHeadFont.Bold = .t. 'set TitleFormat TitleFormat = Doc.AddFormat() TitleFormat.Font = TitleFont 'set ColHeadFormat ColHeadFormat = Doc.AddFormat() ColHeadFormat.Font = ColHeadFont 'set NumericFormat format NumericFormat = Doc.AddFormat() NumericFormat.Font = Font NumericFormat.HorizontalAlignment = Office::HorizontalAlignment::Right NumericFormat.NumericFormat = Office::NumericFormat::CurrencyDec2NegBracketedInRed 'set 2 decimal number format CostFormat = Doc.AddFormat() CostFormat.Font = Font CostFormat.HorizontalAlignment = Office::HorizontalAlignment::Right CostFormat.NumericFormat = Office::NumericFormat::NumberDecimal2 'right format RightFormat = Doc.AddFormat() RightFormat.Font = ColHeadFont RightFormat.HorizontalAlignment = Office::HorizontalAlignment::Right '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' if cn.open("::name::conn")=.f. errorout(cn.callresult.text) end end if ' curCostcenter = "1034600000" ' test value 'Use this statement below to get only records that HAVE charges ' vSql = "SELECT billingid as Item ,sum(callcost) as Charge FROM cb_calldetail_imp WHERE costcenter = '{curCostcenter}' GROUP BY billingid ORDER BY billingid" 'use this statement below to get all Extensions, even if they have no charges vSql = <<%txt% SELECT extension.extension AS Item, Sum(ISNULL(cb_calldetail_imp.callcost,0)) AS Charge, equip_type.charge AS EquipCharge FROM extension FULL OUTER JOIN cb_calldetail_imp ON extension.extension = cb_calldetail_imp.billingid FULL OUTER JOIN equip_type ON extension.equip_type = equip_type.equip_type_id WHERE extension.costcenter = '{curCostcenter}' GROUP BY extension.extension,equip_type.charge ORDER BY extension.extension ASC %txt% vSql = evaluate_string(vSql) Sheet = Doc.AddSheet("Summary") dim arr[0] as p cn.ToPropertyArray(vQ,args,arr) dim crgsum as n detailcount = arr.size() if arr.size() > 0 for x = 1 to arr.size() vname = sql_lookup(cn,"extension","extension = "+arr[x].Item,"alltrim(firstname) + ' ' + alltrim(lastname)") Sheet.Write(x + 3,1,"Total Charges for extension " + arr[x].Item + " ("+vname+")") extchg = arr[x].Charge + arr[x].EquipCharge Sheet.Write(x + 3,2,extchg) crgsum = crgsum + extchg next Sheet.Write(x + 4,1,"Total",RightFormat) Sheet.Write(x + 4,2,crgsum,RightFormat) end if dim curMonth as c = sql_lookup(cn,"defaults","tid=1","cur_period_mm") dim curYear as c = sql_lookup(cn,"defaults","tid=1","cur_period_yy") Sheet.Write(1,1,GetMonth(val(curMonth)) + " " + curYear + " Telephone Charges for " + curCostcenter) Sheet.Write(3,1,"Item") Sheet.Write(3,2,"Charges") Sheet.SetFormat(1,1,TitleFormat) Sheet.SetFormat(3,1,3,10,ColHeadFormat) Sheet.SetFormat(4,2,500,2,NumericFormat) Sheet.SetColumn(1,1,60) Sheet.SetColumn(2,2,13) Sheet.SetMerge(1,1,1,5) 'Add sheets for each extension if arr.size() > 0 for x = 1 to arr.size() vname = sql_lookup(cn,"extension","extension = "+arr[x].Item,"alltrim(firstname) + ' ' + alltrim(lastname)") vSql = <<%txt% SELECT count(*) AS CNT, sum(callminutes) as sumcallminutes, sum(callcost) as sumcallcost FROM cb_calldetail_imp WHERE costcenter = '{curCostcenter}' and billingid = {arr[x].Item} %txt% vSql = evaluate_string(vSql) if cn.execute(vSelect)=.f. errorout(cn.callresult.text) end if dim resultsetcnt as n = cn.resultset.data("cnt") dim sumcallminutes as n = cn.resultset.data("sumcallminutes") dim sumcallcost as n = cn.resultset.data("sumcallcost") dim stationeqid as n = sql_lookup(cn,"extension","extension="+arr[x].Item,"equip_type") dim stationcharge as n = if(convert_type(stationeqid,"C")<>"",sql_lookup(cn,"equip_type","equip_type_id="+stationeqid,"charge"),0) vSql = <<%txt% SELECT convert(varchar(10),calldatetime,101) as CallDate, substring(convert(varchar(20),calldatetime,9),13,5) + ' ' + substring(convert(varchar(30), calldatetime,9),25,2) as CallTime, dialednumber, location, callminutes, callcost FROM cb_calldetail_imp WHERE costcenter = '{curCostcenter}' and billingid = {arr[x].Item} %txt% vSql = evaluate_string(vSql) if cn.execute(vSql)=.f. errorout(cn.callresult.text) end if Sheet = Doc.AddSheetFromResultSet(cn.ResultSet,"x"+ alltrim(str(arr[x].Item)),.f.) Sheet.InsertRow(1,3) Sheet.Write(1,1,"Calls for " + vname + " (x"+alltrim(str(arr[x].Item)) + ")") Sheet.Write(3,1,"Call Date") Sheet.Write(3,2,"Call Time") Sheet.Write(3,3,"Dialed Number") Sheet.Write(3,4,"Location") Sheet.Write(3,5,"Minutes") Sheet.Write(3,6,"Call Cost") Sheet.SetFormat(1,1,TitleFormat) Sheet.SetMerge(1,1,1,5) Sheet.SetColumn(1,1,12) Sheet.SetColumn(2,2,12) Sheet.SetColumn(3,3,22) Sheet.SetColumn(4,4,35) Sheet.SetColumn(5,6,10) Sheet.SetFormat(3,1,3,10,ColHeadFormat) 'totals Sheet.Write(resultsetcnt + 5,4,"Call Totals:") Sheet.Write(resultsetcnt + 5,5,sumcallminutes,CostFormat) Sheet.Write(resultsetcnt + 5,6,sumcallcost,CostFormat) Sheet.Write(resultsetcnt + 6,4,"Fixed Charges:") Sheet.Write(resultsetcnt + 6,6,stationcharge,CostFormat) Sheet.Write(resultsetcnt + 7,4,"Total:") Sheet.Write(resultsetcnt + 7,6,sumcallcost + stationcharge,CostFormat) next end if curBillingid = 3000 end function[/FONT]
Leave a comment: