After creating my module and pasting in his source code, I created a query:
SELECT DMode("length","Numbers") as Mode;
This gives me the MODE of the column "length" in the table "Numbers". You can pass along a where clause as necessary.
Function fConfidence(a_alpha As Double, a_count As Long, a_stdev As Double)
Dim objExcel As Object
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
fConfidence = objExcel.WorksheetFunction.CONFIDENCE_T(a_alpha, a_stdev, a_count)
Set objExcel = Nothing
End Function
This worked fine once I realized that I needed to call "CONFIDENCE_T" and not "CONFIDENCE.T" (the error I was getting said that the function didn't have the right number of parameters. Not very helpful). Thanks to http://msdn.microsoft.com/en-us/library/office/ff838173.aspx I found the name difference.
Now I can call a query to get the various statistics information I need for the report:
SELECT DMedian("length","Numbers") AS Median, DAvg("length","Numbers") AS Average,
DKurtosis("length","Numbers") AS Kurtosis, DSkewness("length","Numbers") AS Skewness,
DMode("length","Numbers") AS Mode, DMax("length","Numbers") AS [Max], DMin("length","Numbers") AS [Min],
DStDev("length","Numbers") AS StdDev, DCount("length","Numbers") AS [Count], StdDev/Sqr(Count) AS StdError,
DSum("length","Numbers") AS [Sum], Max-Min AS Range, DVar("length","Numbers") AS SampleVariance,
fConfidence(0.05, Count, StdDev) As ConfidenceLevel
Handy!
gpresult /r
<add name="ADConnectionString" connectionString ="LDAP://CTUIR-DCX.mailcomm.ctuir.com:389/DC=mailcomm,DC=ctuir,DC=com" />
<membership defaultProvider="ADMembershipProvider"> <providers> <clear/> <add name="ADMembershipProvider" type="System.Web.Security.ActiveDirectoryMembershipProvider" connectionStringName="ADConnectionString" attributeMapUsername="sAMAccountName"/> </providers> </membership>
public static void RegisterGlobalFilters(GlobalFilterCollection filters) { filters.Add(new HandleErrorAttribute()); filters.Add(new System.Web.Mvc.AuthorizeAttribute()); }
[Authorize] public class AccountController : Controller { // // GET: /Account/Login [AllowAnonymous] public ActionResult Login(string returnUrl) { ViewBag.ReturnUrl = returnUrl; return View(); } // // POST: /Account/Login [HttpPost] [AllowAnonymous] [ValidateAntiForgeryToken] public ActionResult Login(LoginModel model, string returnUrl) { if (ModelState.IsValid) { if(Membership.ValidateUser(model.UserName, model.Password)) { FormsAuthentication.SetAuthCookie(model.UserName, model.RememberMe); if(Url.IsLocalUrl(returnUrl) && returnUrl.Length > 1 && returnUrl.StartsWith("/") && !returnUrl.StartsWith("//") && !returnUrl.StartsWith("/\\")) { return Redirect(returnUrl); } else { return RedirectToAction("Index","Home"); } } else { ModelState.AddModelError("","The user name or password provided is incorrect."); } } return View(model); } // // POST: /Account/LogOff [HttpPost] [ValidateAntiForgeryToken] public ActionResult LogOff() { FormsAuthentication.SignOut(); return RedirectToAction("Index", "Home"); }
@(User.Identity.IsAuthenticated ? "Hello, " + User.Identity.Name : "Not logged in.")
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ConsoleApplication1.gov.epa.cdxnodengn;
using ConsoleApplication1.gov.epa.cdx;
using System.IO;
namespace ConsoleApplication1
{
class WQXUploader
{
private static string defaultFilePath = "."; //all .xml files in this directory will be uploaded.
private static NetworkNode node;
private static string token;
private static int numFilesUploaded = 0;
static int Main(string[] args)
{
if (args.Length < 2 )
{
log("Syntax: WQXUpload [path] (default path is current directory)");
waitForKeypress();
return 1;
}
//setup our passed in credentials.
string username = args[0];
string password = args[1];
//if they call us with a path then set it here.
string filePath = defaultFilePath;
if (args.Length == 3)
{
filePath = args[2];
}
//done with args now get rolling...
log("Starting WQX Uploader.");
Inititialize();
Authenticate(username, password);
//which files to upload?
List filesToUpload = getFilesToUpload(filePath);
//submit each file.
foreach (var file in filesToUpload)
{
//log("Uploading file: " + file);
submitFile(token, file, filePath);
numFilesUploaded++;
}
Cleanup();
log("WQX upload is finished. We uploaded "+ numFilesUploaded + " files." );
waitForKeypress();
return 0;
}
/**
* Gets a list of files from the filepath that we will upload
* @return List files to upload
*/
private static List getFilesToUpload(string filePath)
{
log("Using path: " + filePath);
List files = new List();
DirectoryInfo di = new DirectoryInfo(filePath);
var directories = di.GetFiles("*.xml", SearchOption.TopDirectoryOnly);
foreach (FileInfo file in directories)
{
files.Add(file.Name);
}
return files;
}
/*
* Wait for a keypress -- just to keep the console open.
*/
private static void waitForKeypress()
{
Console.WriteLine("Press ESC to exit");
do
{
while (!Console.KeyAvailable)
{
// Do something
}
} while (Console.ReadKey(true).Key != ConsoleKey.Escape);
}
/**
* Submits file to WQX
* @return transactionId of the submission.
*/
private static string submitFile(string token, string fileToUpload, string filePath)
{
NodeDocument doc = new NodeDocument();
byte[] buffer = File.ReadAllBytes(filePath + "\\" + fileToUpload);
doc.name = fileToUpload;
doc.type = "xml";
doc.content = buffer;
NodeDocument[] docToSend = { doc };
String transactionId = node.Submit(token, "", "WQX", docToSend);
log(fileToUpload + " " + transactionId);
//logStatus(transactionId);
return transactionId;
}
/*
* Outputs the status of the transaction to the log.
*/
private static void logStatus(string transactionId)
{
log(" Current status: " + node.GetStatus(token, transactionId));
}
/*
* Writes the message to the log
*/
private static void log(string msg)
{
Console.WriteLine(msg);
}
/*
* Initializes the NetworkNode connection
*/
private static void Inititialize()
{
node = new NetworkNode();
}
/**
* Performs any cleanup necessary
*/
private static void Cleanup()
{
node.Dispose();
}
/*
* Connects and authenticates us with the endpoint, fetching our token necessary for other functions.
* @return string token
*/
private static string Authenticate(String username, String password)
{
log("Connecting and authenticating...");
token = node.Authenticate(username, password, "password");
log("Success! Security token: " + token);
return token;
}
}
}
