public static OdbcConnectionStringBuilder ConnectionString(string pathToDB)
{
OdbcConnectionStringBuilder cs = new OdbcConnectionStringBuilder();
cs.Driver = "Microsoft Access Driver (*.mdb)";
cs.Add("Dbq", pathToDB);
cs.Add("Uid", "Admin");
cs.Add("Pwd", "");
return(cs);
}
static void Main()
{
OdbcConnectionStringBuilder builder =
new OdbcConnectionStringBuilder();
builder.Driver = "Microsoft Access Driver (*.mdb)";
// Call the Add method to explicitly add key/value
// pairs to the internal collection.
builder.Add("Dbq", "C:\\info.mdb");
builder.Add("Uid", "Admin");
builder.Add("Pwd", "pass!word1");
Console.WriteLine(builder.ConnectionString);
Console.WriteLine();
// Clear current values and reset known keys to their
// default values.
builder.Clear();
// Pass the OdbcConnectionStringBuilder an existing
// connection string, and you can retrieve and
// modify any of the elements.
builder.ConnectionString =
"driver={IBM DB2 ODBC DRIVER};Database=SampleDB;" +
"hostname=SampleServerName;port=SamplePortNum;" +
"protocol=TCPIP;uid=Admin;pwd=pass!word1";
Console.WriteLine("protocol = "
+ builder["protocol"].ToString());
Console.WriteLine();
// Modify existing items.
builder["uid"] = "NewUser";
builder["pwd"] = "Pass@word2";
// Call the Remove method to remove items from
// the collection of key/value pairs.
builder.Remove("port");
// Note that calling Remove on a nonexistent item does not
// throw an exception.
builder.Remove("BadItem");
Console.WriteLine(builder.ConnectionString);
Console.WriteLine();
// Setting the indexer adds the associated value, if
// necessary.
builder["NewKey"] = "newValue";
Console.WriteLine(builder.ConnectionString);
Console.WriteLine("Press Enter to finish.");
Console.ReadLine();
}
private static void ProcessQuotesNeeded()
{
OdbcConnection cn;
var notifiedlist = new ArrayList();
// user_1 = receiving rack location
// user_2 = Receiver
// user_3 = Received Date
// user_4 = Bin Cleared Date
// user_5 = Notified
// POQuery = "Select icpo.buyer, icpo.ponum, icpo.user_1, icpo.user_2, icpo.user_3, icpo.user_4, icpo.user_5, icpo.defaultjobnum, vendor.name as vendorName, icpo.user_6, icpo.defaultworkorder, icpo.attachid from icpo inner join vendor on vendor.vennum = icpo.vennum where icpo.user_3 is not null and icpo.user_5 = 0 order by icpo.ponum asc";
OdbcConnectionStringBuilder just = new OdbcConnectionStringBuilder
{
Driver = "ComputerEase"
};
just.Add("Dsn", "Company 0");
just.Add("Uid", config.Uid);
just.Add("Pwd", config.Pwd);
cn = new OdbcConnection(just.ConnectionString);
cn.Open();
log.Info("[ProcessQuotesNeeded] Connection to database opened successfully");
var dbRepository = new DatabaseRepository(cn, log, null);
var x = dbRepository.GetQuotesNeeded();
log.Info("[ProcessQuotesNeeded] Found " + x.Count.ToString() + " quotes to notify");
var hvacQuotes = x.FindAll(q => q.WorkOrder.StartsWith("H"));
var plumbingQuotes = x.FindAll(q => !q.WorkOrder.StartsWith("H"));
log.Info("[ProcessQuotesNeeded] Found " + hvacQuotes.Count.ToString() + " HVAC quotes to notify");
log.Info("[ProcessQuotesNeeded] Found " + plumbingQuotes.Count.ToString() + " Plumbing quotes to notify");
var emailSubject = string.Format(EmailSubject, RunDate);
if (Utils.sendEmail(config, config.PlumbingEmailAddresses, emailSubject, FormatEmailBody(plumbingQuotes, dbRepository, DefaultPlumbingMessage)))
{
foreach (Quote quote in hvacQuotes)
{
dbRepository.MarkQuoteAsNotified(quote);
}
}
if (Utils.sendEmail(config, config.HVACEmailAddresses, emailSubject, FormatEmailBody(hvacQuotes, dbRepository, DefaultHVACMessage)))
{
foreach (Quote quote in plumbingQuotes)
{
dbRepository.MarkQuoteAsNotified(quote);
}
}
}
//get ODBC Connection String from configation file
private string getConnStr()
{
OdbcConnectionStringBuilder ConnStr = new OdbcConnectionStringBuilder();
config.init(); //Initialized config class
ConnStr.Add("Driver", config.getValue("Driver")); // read ODBC Driver
ConnStr.Add("server", config.getValue("Server")); // read database server
ConnStr.Add("database", config.getValue("Database")); // read database name
ConnStr.Add("uid", config.getValue("User")); // read database user
ConnStr.Add("password", config.getValue("Password")); //read database password
return(ConnStr.ConnectionString.ToString());
}
/// <inheritdoc/>
protected override string GetConnectionStringWithLoginInfo(string userName, string password)
{
OdbcConnectionStringBuilder builder = new OdbcConnectionStringBuilder(ConnectionString);
builder.Remove("uid");
builder.Add("uid", userName);
builder.Remove("pwd");
builder.Add("pwd", password);
return(builder.ToString());
}
private string GenerateConnectionString()
{
var builder = new OdbcConnectionStringBuilder
{
Dsn = "MS Access Database",
};
builder.Add("Dbq", DatabaseFile);
builder.Add("Uid", "Admin");
builder.Add("Pwd", Password);
return(builder.ConnectionString);
}
// Create a connection to the SQL server
public IDbConnection sqlSocket()
{
OdbcConnectionStringBuilder builder = new OdbcConnectionStringBuilder();
builder.Driver = "SQL Server";
builder.Add("Server", "cougar5");
builder.Add("Database", "SANDBOXMODEL_PETER");
builder.Add("Trusted_Connection", "Yes");
IDbConnection socket = new OdbcConnection(builder.ConnectionString);
return(socket);
}
/// <summary>
/// Defines the Connection as an OdbcConnection object.
/// </summary>
/// <param name="directoryPath"></param>
public IDbConnection SetOdbcConnection(string directoryPath)
{
var builder = new OdbcConnectionStringBuilder {
Driver = "{Microsoft Text Driver (*.txt; *.csv)}"
};
builder.Add("Dbq", directoryPath);
builder.Add("Extensions", "asc,csv,tab,txt");
builder.Add("Persist Security Info", "False");
Connection = new OdbcConnection(builder.ConnectionString);
return(Connection);
}
private static string BuildConnectionString(SqlPassContext context)
{
OdbcConnectionStringBuilder connectionStringBuilder = new OdbcConnectionStringBuilder
{
Driver = context.Driver,
};
connectionStringBuilder.Add("Server", context.Server);
connectionStringBuilder.Add("Database", context.Database);
connectionStringBuilder.Add("User ID", context.UserId);
connectionStringBuilder.Add("Password", context.Password);
connectionStringBuilder.Add("Trusted_Connection", context.TrustedConnection);
return(connectionStringBuilder.ConnectionString);
}
public static void ProcessFile(string folderPath)
{
logger.Info("Application Started | ProcessFile");
//var file = Directory.GetFiles(@folderPath, "*.mdb").FirstOrDefault();
string file = folderPath;
if (File.Exists(file))
{
OdbcConnectionStringBuilder builder =
new OdbcConnectionStringBuilder();
builder.Driver = "Microsoft Access Driver (*.mdb, *.accdb)";
builder.Add("DBQ", file);
logger.Info(builder.ConnectionString);
string fileName = Path.GetFileName(file);
try
{
if (DataTransfer.GetCompId(fileName) != 0)
{
ReadData(builder.ConnectionString, fileName);
}
else
{
logger.Error("Competition does not exist. Please create new Competition");
}
}
catch (Exception ex)
{
logger.Error("Exception : " + ex.Message);
}
}
}
public void TryGetValue()
{
OdbcConnectionStringBuilder builder = new OdbcConnectionStringBuilder();
object value = null;
builder ["DriverID"] = "790";
builder.Add("Server", "C:\\");
Assert.IsTrue(builder.TryGetValue("DriverID", out value), "#A1");
Assert.AreEqual("790", value, "#A2");
Assert.IsTrue(builder.TryGetValue("SERVER", out value), "#B1");
Assert.AreEqual("C:\\", value, "#B2");
Assert.IsFalse(builder.TryGetValue(string.Empty, out value), "#C1");
Assert.IsNull(value, "#C2");
Assert.IsFalse(builder.TryGetValue("a;", out value), "#D1");
Assert.IsNull(value, "#D2");
Assert.IsFalse(builder.TryGetValue("\r", out value), "#E1");
Assert.IsNull(value, "#E2");
Assert.IsFalse(builder.TryGetValue(" ", out value), "#F1");
Assert.IsNull(value, "#F2");
Assert.IsFalse(builder.TryGetValue("doesnotexist", out value), "#G1");
Assert.IsNull(value, "#G2");
Assert.IsTrue(builder.TryGetValue("Driver", out value), "#H1");
Assert.AreEqual(string.Empty, value, "#H2");
Assert.IsTrue(builder.TryGetValue("Dsn", out value), "#I1");
Assert.AreEqual(string.Empty, value, "#I2");
builder ["Driver"] = "SQL Server";
Assert.IsTrue(builder.TryGetValue("Driver", out value), "#J1");
Assert.AreEqual("SQL Server", value, "#J2");
builder.Dsn = "AdventureWorks";
Assert.IsTrue(builder.TryGetValue("Dsn", out value), "#K1");
Assert.AreEqual("AdventureWorks", value, "#K2");
}
public void Add_Keyword_Invalid()
{
string [] invalid_keywords = new string [] {
string.Empty,
" ",
" abc",
"abc ",
"\r",
"ab\rc",
";abc",
"a\0b"
};
OdbcConnectionStringBuilder builder = new OdbcConnectionStringBuilder();
for (int i = 0; i < invalid_keywords.Length; i++)
{
string keyword = invalid_keywords [i];
try {
builder.Add(keyword, "abc");
Assert.Fail("#1:" + i);
} catch (ArgumentException ex) {
// Invalid keyword, contain one or more of 'no characters',
// 'control characters', 'leading or trailing whitespace'
// or 'leading semicolons'
Assert.AreEqual(typeof(ArgumentException), ex.GetType(), "#2:" + i);
Assert.IsNull(ex.InnerException, "#3:" + i);
Assert.IsNotNull(ex.Message, "#4:" + i);
Assert.IsTrue(ex.Message.IndexOf("'" + keyword + "'") == -1, "#5:" + i);
Assert.AreEqual(keyword, ex.ParamName, "#6:" + i);
}
}
}
public void Add()
{
OdbcConnectionStringBuilder builder = new OdbcConnectionStringBuilder();
builder.Add("driverid", "420");
builder.Add("driverid", "560");
builder.Add("DriverID", "840");
Assert.AreEqual("840", builder ["driverId"], "#A1");
Assert.IsTrue(builder.ContainsKey("driverId"), "#A2");
builder.Add("Driver", "OdbcDriver");
Assert.AreEqual("OdbcDriver", builder.Driver, "#B1");
Assert.AreEqual("OdbcDriver", builder ["Driver"], "#B2");
Assert.IsTrue(builder.ContainsKey("Driver"), "#B3");
builder.Add("Driver", "{OdbcDriver");
Assert.AreEqual("{OdbcDriver", builder.Driver, "#C1");
Assert.AreEqual("{OdbcDriver", builder ["Driver"], "#C2");
Assert.IsTrue(builder.ContainsKey("Driver"), "#C3");
builder.Add("Dsn", "MyDsn");
Assert.AreEqual("MyDsn", builder.Dsn, "#D1");
Assert.AreEqual("MyDsn", builder ["Dsn"], "#D2");
Assert.IsTrue(builder.ContainsKey("Dsn"), "#D3");
builder.Add("dsN", "MyDsn2");
Assert.AreEqual("MyDsn2", builder.Dsn, "#E1");
Assert.AreEqual("MyDsn2", builder ["Dsn"], "#E2");
Assert.IsTrue(builder.ContainsKey("Dsn"), "#E3");
}
public static OdbcConnection GetConnection([NotNull] string sourceDb, [CanBeNull] string sPwd = null, [CanBeNull] string user = null)
{
var builder = new OdbcConnectionStringBuilder {
Driver = "Microsoft Access Driver (*.mdb)"
};
builder.Add("Dbq", sourceDb);
if (!string.IsNullOrEmpty(user))
{
builder.Add("Uid", user);
}
if (!string.IsNullOrEmpty(sPwd))
{
builder.Add("Pwd", sPwd);
}
return(new OdbcConnection(builder.ConnectionString));
}
/// <summary>
/// Creates a new connection to the database
/// </summary>
/// <returns></returns>
private OdbcConnection GetOdbcConnection()
{
//Credentials
string user = "******";
string pass = "******";
string server = "server";
string database = "database";
//Create the Connection String
OdbcConnectionStringBuilder mscsb = new OdbcConnectionStringBuilder();
mscsb.Add("Server", server);
mscsb.Add("Database", database);
mscsb.Add("User", user);
mscsb.Add("Password", pass);
mscsb.Add("Connection Timeout", 120);
//Return a new connection using the connection string
return(new OdbcConnection(mscsb.ToString()));
}
public static string GetNameFromExternalDatabase(int playerNumber)
{
string name = "";
OdbcConnectionStringBuilder externalDB = new OdbcConnectionStringBuilder {
Driver = "Microsoft Access Driver (*.mdb)"
};
externalDB.Add("Dbq", @"C:\Bridgemate\BMPlayerDB.mdb");
externalDB.Add("Uid", "Admin");
using (OdbcConnection connection = new OdbcConnection(externalDB.ToString()))
{
object queryResult = null;
string SQLString = $"SELECT Name FROM PlayerNameDatabase WHERE ID={playerNumber}";
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
try
{
connection.Open();
ODBCRetryHelper.ODBCRetry(() =>
{
queryResult = cmd.ExecuteScalar();
if (queryResult == null)
{
name = "Unknown #" + playerNumber;
}
else
{
name = queryResult.ToString();
}
});
}
catch (OdbcException) // If we can't read the external database for whatever reason...
{
name = "#" + playerNumber;
}
finally
{
cmd.Dispose();
}
}
return(name);
}
public void Add_Keyword_Null()
{
OdbcConnectionStringBuilder builder = new OdbcConnectionStringBuilder();
try {
builder.Add(null, "abc");
Assert.Fail("#1");
} catch (ArgumentNullException ex) {
Assert.AreEqual(typeof(ArgumentNullException), ex.GetType(), "#2");
Assert.IsNull(ex.InnerException, "#3");
Assert.IsNotNull(ex.Message, "#4");
Assert.AreEqual("keyword", ex.ParamName, "#5");
}
}
private string GenerateConnectionString()
{
if (Database == SELECT_DSN_LABEL)
{
return("");
}
var dsn = "";
if (!string.IsNullOrEmpty(Database))
{
dsn = Database;
}
var builder = new OdbcConnectionStringBuilder
{
Dsn = dsn,
};
builder.Add("Uid", User);
builder.Add("Pwd", Password);
return(builder.ConnectionString);
}
public void Clear()
{
OdbcConnectionStringBuilder builder = new OdbcConnectionStringBuilder();
builder ["Dbq"] = "C:\\Data.xls";
builder.Driver = "SQL Server";
builder.Dsn = "AdventureWorks";
builder.Add("Port", "56");
builder.Clear();
Assert.AreEqual(string.Empty, builder.ConnectionString, "#1");
Assert.IsFalse(builder.ContainsKey("Dbq"), "#2");
Assert.AreEqual(string.Empty, builder.Driver, "#3");
Assert.AreEqual(string.Empty, builder.Dsn, "#4");
Assert.IsFalse(builder.ContainsKey("Port"), "#5");
}
protected override string GetConnectionString()
{
OdbcConnectionStringBuilder builder = new OdbcConnectionStringBuilder(tbConnectionString.Text);
if (!String.IsNullOrEmpty(tbUserName.Text))
{
builder.Add("uid", tbUserName.Text);
}
else
{
builder.Remove("uid");
}
if (!String.IsNullOrEmpty(tbPassword.Text))
{
builder.Add("pwd", tbPassword.Text);
}
else
{
builder.Remove("pwd");
}
return(builder.ToString());
}
private OdbcConnection CreateConnection()
{
var connectionStringBuilder = new OdbcConnectionStringBuilder
{
Driver = "SQL Server"
};
connectionStringBuilder.Add("Database", "Cookbook");
connectionStringBuilder.Add("Server", "(local)");
connectionStringBuilder.Add("Trusted_Connection", "Yes");
var connection = new OdbcConnection(connectionStringBuilder.ConnectionString);
try
{
connection.Open();
return(connection);
}
catch (Exception exception)
{
throw new GatewayNotOpenedDataException(exception);
}
}
/// <summary>
/// Create a new drive. Create a connection to the database file and set
/// the Connection property in the PSDriveInfo.
/// </summary>
/// <param name="drive">
/// Information describing the drive to add.
/// </param>
/// <returns>The added drive.</returns>
protected override PSDriveInfo NewDrive(PSDriveInfo drive)
{
// check if drive object is null
if (drive == null)
{
WriteError(new ErrorRecord(
new ArgumentNullException("drive"),
"NullDrive",
ErrorCategory.InvalidArgument,
null)
);
return(null);
}
// check if drive root is not null or empty
// and if its an existing file
if (String.IsNullOrEmpty(drive.Root) || (File.Exists(drive.Root) == false))
{
WriteError(new ErrorRecord(
new ArgumentException("drive.Root"),
"NoRoot",
ErrorCategory.InvalidArgument,
drive)
);
return(null);
}
// create a new drive and create an ODBC connection to the new drive
AccessDBPSDriveInfo accessDBPSDriveInfo = new AccessDBPSDriveInfo(drive);
OdbcConnectionStringBuilder builder = new OdbcConnectionStringBuilder();
builder.Driver = "Microsoft Access Driver (*.mdb)";
builder.Add("DBQ", drive.Root);
OdbcConnection conn = new OdbcConnection(builder.ConnectionString);
conn.Open();
accessDBPSDriveInfo.Connection = conn;
return(accessDBPSDriveInfo);
} // NewDrive
/// <summary>
/// The Windows PowerShell engine calls this method when the New-Drive
/// cmdlet is run. This provider creates a connection to the database
/// file and sets the Connection property in the PSDriveInfo.
/// </summary>
/// <param name="drive">
/// Information describing the drive to create.
/// </param>
/// <returns>An object that describes the new drive.</returns>
protected override PSDriveInfo NewDrive(PSDriveInfo drive)
{
// Check to see if the supplied drive object is null.
if (drive == null)
{
WriteError(new ErrorRecord(
new ArgumentNullException("drive"),
"NullDrive",
ErrorCategory.InvalidArgument,
null));
return null;
}
// Check to see if the drive root is not null or empty
// and if it iss an existing file.
if (String.IsNullOrEmpty(drive.Root) || (File.Exists(drive.Root) == false))
{
WriteError(new ErrorRecord(
new ArgumentException("drive.Root"),
"NoRoot",
ErrorCategory.InvalidArgument,
drive));
return null;
}
// Create the new drive and create an ODBC connection
// to the new drive.
var accessDBPSDriveInfo = new AccessDBPSDriveInfo(drive);
var builder = new OdbcConnectionStringBuilder();
builder.Driver = "Microsoft Access Driver (*.mdb)";
builder.Add("DBQ", drive.Root);
var conn = new OdbcConnection(builder.ConnectionString);
conn.Open();
accessDBPSDriveInfo.Connection = conn;
return accessDBPSDriveInfo;
}
public void Remove()
{
OdbcConnectionStringBuilder builder = new OdbcConnectionStringBuilder();
Assert.IsFalse(builder.Remove("Dsn"), "#A1");
Assert.IsFalse(builder.Remove("Driver"), "#A2");
builder.Add("DriverID", "790");
builder ["DefaultDir"] = "C:\\";
Assert.IsTrue(builder.Remove("DriverID"), "#B1");
Assert.IsFalse(builder.ContainsKey("DriverID"), "#B2");
Assert.IsFalse(builder.Remove("DriverID"), "#B3");
Assert.IsFalse(builder.ContainsKey("DriverID"), "#B4");
Assert.IsTrue(builder.Remove("defaulTdIr"), "#B5");
Assert.IsFalse(builder.ContainsKey("DefaultDir"), "#B6");
Assert.IsFalse(builder.Remove("defaulTdIr"), "#B7");
Assert.IsFalse(builder.Remove("userid"), "#B8");
Assert.IsFalse(builder.Remove(string.Empty), "#B9");
Assert.IsFalse(builder.Remove("\r"), "#B10");
Assert.IsFalse(builder.Remove("a;"), "#B11");
builder.Dsn = "myDsn";
Assert.IsTrue(builder.Remove("dSn"), "#C1");
Assert.IsTrue(builder.ContainsKey("dSn"), "#C2");
Assert.IsTrue(builder.ContainsKey("Dsn"), "#C3");
Assert.AreEqual(string.Empty, builder.Dsn, "#C4");
Assert.IsFalse(builder.Remove("Dsn"), "#C5");
builder.Driver = "SQL Server";
Assert.IsTrue(builder.Remove("driVer"), "#D1");
Assert.IsTrue(builder.ContainsKey("driVer"), "#D2");
Assert.IsTrue(builder.ContainsKey("Driver"), "#D3");
Assert.AreEqual(string.Empty, builder.Driver, "#D4");
Assert.IsFalse(builder.Remove("Driver"), "#D5");
builder ["Dsn"] = "myDsn";
Assert.IsTrue(builder.Remove("Dsn"), "#E1");
Assert.IsTrue(builder.ContainsKey("Dsn"), "#E2");
Assert.AreEqual(string.Empty, builder.Dsn, "#E3");
Assert.IsFalse(builder.Remove("Dsn"), "#E4");
builder ["Driver"] = "SQL Server";
Assert.IsTrue(builder.Remove("Driver"), "#F1");
Assert.IsTrue(builder.ContainsKey("Driver"), "#F2");
Assert.AreEqual(string.Empty, builder.Driver, "#F3");
Assert.IsFalse(builder.Remove("Driver"), "#F4");
}
public void ContainsKey()
{
OdbcConnectionStringBuilder builder = new OdbcConnectionStringBuilder();
builder ["SourceType"] = "DBC";
builder.Add("Port", "56");
Assert.IsTrue(builder.ContainsKey("SourceType"), "#1");
Assert.IsTrue(builder.ContainsKey("Port"), "#2");
Assert.IsTrue(builder.ContainsKey("Dsn"), "#3");
Assert.IsTrue(builder.ContainsKey("Driver"), "#4");
Assert.IsFalse(builder.ContainsKey("xyz"), "#5");
builder.Dsn = "myDsn";
Assert.IsTrue(builder.ContainsKey("Dsn"), "#6");
builder.Driver = "SQL Server";
Assert.IsTrue(builder.ContainsKey("Driver"), "#7");
builder ["Dsn"] = "myDsn";
Assert.IsTrue(builder.ContainsKey("Dsn"), "#8");
builder ["Driver"] = "SQL Server";
Assert.IsTrue(builder.ContainsKey("Driver"), "#9");
builder ["abc"] = "pqr";
Assert.IsTrue(builder.ContainsKey("ABC"), "#10");
Assert.IsFalse(builder.ContainsKey(string.Empty), "#11");
}
private static void ProcessPOData()
{
try
{
OdbcConnection cn;
var notifiedlist = new ArrayList();
// user_1 = receiving rack location
// user_2 = Receiver
// user_3 = Received Date
// user_4 = Bin Cleared Date
// user_5 = Notified
// POQuery = "Select icpo.buyer, icpo.ponum, icpo.user_1, icpo.user_2, icpo.user_3, icpo.user_4, icpo.user_5, icpo.defaultjobnum, vendor.name as vendorName, icpo.user_6, icpo.defaultworkorder, icpo.attachid from icpo inner join vendor on vendor.vennum = icpo.vennum where icpo.user_3 is not null and icpo.user_5 = 0 order by icpo.ponum asc";
OdbcConnectionStringBuilder just = new OdbcConnectionStringBuilder
{
Driver = "ComputerEase"
};
just.Add("Dsn", "Company 0");
just.Add("Uid", config.Uid);
just.Add("Pwd", config.Pwd);
cn = new OdbcConnection(just.ConnectionString);
cn.Open();
log.Info("[ProcessPOData] Connection to database opened successfully");
var dbRepository = new DatabaseRepository(cn, log, config.POAttachmentBasePath);
List <PurchaseOrder> purchaseOrdersToNotify;
try
{
purchaseOrdersToNotify = dbRepository.GetPurchaseOrdersToNotify();
log.Info("purchaseOrdersToNotify found " + purchaseOrdersToNotify.Count.ToString() + " items.");
foreach (PurchaseOrder po in purchaseOrdersToNotify)
{
var job = dbRepository.GetEmailBodyInformation(po.JobNumber, po.PurchaseOrderNumber, po.WorkOrderNumber);
var buyerEmployee = EmployeeLookup.FindEmployeeFromAllEmployees(dbRepository.GetEmployees(), po.Buyer);
var projectManagerEmployee = job.ProjectManagerName.Length > 0 ? EmployeeLookup.FindEmployeeFromAllEmployees(dbRepository.GetEmployees(), job.ProjectManagerName) : new Employee();
log.Info("[ProcessPOData] ----------------- Found PO Number " + po.PurchaseOrderNumber + " -------------------");
var emailSubject = String.Format(EmailSubject, po.PurchaseOrderNumber, po.Vendor);
var emailBody = FormatEmailBody(po.ReceivedOnDate, po.PurchaseOrderNumber, po.ReceivedBy, po.Bin, buyerEmployee.Name, po.Vendor, job, po.Notes);
ArrayList primaryRecipients = new ArrayList();
ArrayList bccList = new ArrayList();
if ((config.Mode == live) || (config.Mode == monitor))
{
primaryRecipients.Add(buyerEmployee.EmailAddress);
if (projectManagerEmployee.EmailAddress.Length > 0 && buyerEmployee.EmailAddress != projectManagerEmployee.EmailAddress)
{
primaryRecipients.Add(projectManagerEmployee.EmailAddress);
}
}
if (((config.Mode == monitor) || (config.Mode == debug)) &&
(config.MonitorEmailAddresses != null && config.MonitorEmailAddresses.Count > 0))
{
foreach (string monitorEmailAddress in config.MonitorEmailAddresses)
{
bccList.Add(monitorEmailAddress);
}
}
if ((primaryRecipients.Count == 0) && (bccList.Count > 0))
{
primaryRecipients.Add(bccList[0]);
}
if (sendEmail(primaryRecipients, bccList, emailSubject, emailBody, po.Attachments))
{
notifiedlist.Add(po.PurchaseOrderNumber);
}
}
}
catch (Exception ex)
{
log.Info(ex.Message);
}
foreach (string poNum in notifiedlist)
{
try
{
dbRepository.MarkPOAsNotified(poNum);
}
catch (Exception x)
{
log.Error(String.Format("[ProcessPOData] Error updating PO {0} to be Notified: {1}", poNum, x.Message));
}
}
cn.Close();
}
catch (Exception x)
{
log.Error("[ProcessPOData] Exception: " + x.Message);
return;
}
return;
}
private void EpConnectServerButton_Click(object sender, EventArgs e)
{
var classOPCUA = new OPCUA();
//Check if sessions exists; If yes > delete subscriptions and disconnect
if (mySession != null && !mySession.Disposed)
{
myClientHelperAPI.Disconnect();
mySession = myClientHelperAPI.Session;
ResetUI();
}
else
{
try
{
//Register mandatory events (cert and keep alive)
myClientHelperAPI.KeepAliveNotification += new KeepAliveEventHandler(classOPCUA.Notification_KeepAlive);
myClientHelperAPI.CertificateValidationNotification += new CertificateValidationEventHandler(classOPCUA.Notification_ServerCertificate);
//Check for a selected endpoint
if (mySelectedEndpoint != null)
{
//Call connect
myClientHelperAPI.Connect(mySelectedEndpoint, userPwButton.Checked, userTextBox.Text, pwTextBox.Text);
//Extract the session object for further direct session interactions
mySession = myClientHelperAPI.Session;
//UI settings
epConnectServerButton.Text = "Disconnect from server";
//myCertForm = null;
if (descriptionGridView.Enabled == false)
{
descriptionGridView.Enabled = true;
descriptionGridView.BackgroundColor = SystemColors.Window;
nodeTreeView.Enabled = true;
nodeTreeView.BackColor = SystemColors.Window;
numericS7RecordsCount.Enabled = true;
numericS7RecordsCount.BackColor = SystemColors.Window;
textBoxSQLTableName.Enabled = true;
textBoxSQLTableName.BackColor = SystemColors.Window;
textBoxSQLIDColName.BackColor = SystemColors.Window;
textBoxSQLIDColName.Enabled = true;
textBoxSQLValColName.BackColor = SystemColors.Window;
textBoxSQLValColName.Enabled = true;
textBoxSQLDATColName.BackColor = SystemColors.Window;
textBoxSQLDATColName.Enabled = true;
}
if (Config.Sets.Primary_ODBC_DSN != "")
{
connStringBuilder.Dsn = Config.Sets.Primary_ODBC_DSN;
connStringBuilder.Add("Uid", Config.Sets.Primary_ODBC_User);
connStringBuilder.Add("Pwd", Config.Sets.Primary_ODBC_Pass);
}
if (Config.Sets.Primary_OPCUA_Node != "")
{
//discoveryTextBox.Text = Config.Sets.Primary_OPCUA_Node;
textBoxS7DBName.Text = Config.Sets.Primary_S7_DBName;
textBoxS7RecArrayName.Text = Config.Sets.Primary_OPCUA_RecArray;
textBoxS7RecResetCountName.Text = Config.Sets.Primary_OPCUA_RecResetCount;
}
if (Config.Sets.Primary_SQL_NumberOfRec != 0)
{
numericS7RecordsCount.Value = Config.Sets.Primary_SQL_NumberOfRec;
textBoxSQLTableName.Text = Config.Sets.Primary_SQL_TableName;
textBoxSQLIDColName.Text = Config.Sets.Primary_SQL_IDColName;
textBoxSQLValColName.Text = Config.Sets.Primary_SQL_ValColName;
textBoxSQLDATColName.Text = Config.Sets.Primary_SQL_DATColName;
buttonSaveConfig.Enabled = true;
buttonSaveConfig.BackColor = Color.Transparent;
}
if (myReferenceDescriptionCollection == null)
{
try
{
myReferenceDescriptionCollection = myClientHelperAPI.BrowseRoot();
foreach (ReferenceDescription refDesc in myReferenceDescriptionCollection)
{
nodeTreeView.Nodes.Add(refDesc.DisplayName.ToString()).Tag = refDesc;
foreach (TreeNode node in nodeTreeView.Nodes)
{
node.Nodes.Add("");
}
}
}
catch (Exception ex)
{
if (BGW_OPCUA.IsBusy)
{
BGW_OPCUA.CancelAsync();
}
MessageBox.Show(ex.Message, "Error");
}
}
}
else
{
MessageBox.Show("Please select an endpoint before connecting", "Error");
return;
}
}
catch (Exception ex)
{
if (BGW_OPCUA.IsBusy)
{
BGW_OPCUA.CancelAsync();
}
//myCertForm = null;
MessageBox.Show(ex.Message, "Error");
}
}
}
private static void ProcessNewJobData()
{
try
{
var runDate = DateTime.Today;
var checkStartDate = string.Empty;
var checkEndDate = string.Empty;
if (runDate.IsMonday())
{
if (runDate.IsFederalHoliday())
{
//Today, Monday, is a Federal Holiday, payroll won't get entered until Tuesday
log.Info("Running on Monday: is a Federal Holiday, closing.");
return;
}
else
{
// It's just a regular Monday, payroll on Wednesday
checkStartDate = String.Format("{0:yyyy-MM-dd}", runDate.AddDays(2));
checkEndDate = String.Format("{0:yyyy-MM-dd}", runDate.AddDays(4));
log.Info("Running on Monday, checking payroll for Wednesday.");
}
}
else
{
if (runDate.AddDays(-1).IsMonday() && runDate.AddDays(-1).IsFederalHoliday())
{
// It's a Tuesday and yesterday, Monday, was a Federal Holiday. This was skipped above.
// We want to continue looking up Wednesday payroll
checkStartDate = String.Format("{0:yyyy-MM-dd}", runDate.AddDays(1));
checkEndDate = String.Format("{0:yyyy-MM-dd}", runDate.AddDays(3));
log.Info("Running on Tuesday. Monday was a federal holiday, checking payroll for Wednesday.");
}
else
{
log.Info("Closing as it's either: not Monday or: Tuesday when Monday was a holiday");
return;
}
}
log.Info("checkStartDate: " + checkStartDate);
log.Info("checkEndDate: " + checkEndDate);
OdbcConnection cn;
OdbcCommand cmd;
var notifiedlist = new ArrayList();
//jcjob
// user_1 = job description
// user_2 = sales person
// user_3 = designer
// user_4 = project manager
// user_5 = SM
// user_6 = Fitter
// user_7 = Plumber
// user_8 = Tech 1
// user_9 = Tech 2
// user_10 = Notified
//
//customer
// user_1 = primary contact
// user_2 = secondary contact
// var JobsQuery = "select distinct prledgerjc.jobnum, customer.user_1 from prledgerjc inner join jcjob on prledgerjc.jobnum = jcjob.jobnum inner join customer on jcjob.cusnum = customer.cusnum where checkdate = {d'" + checkDate + "'} and prledgerjc.jobnum is not null and (prledgerjc.jobnum like 'C%' or prledgerjc.jobnum like 'P%') and prledgerjc.jobnum = 'CAC18081'";
var JobsQuery = "select distinct prledgerjc.jobnum, jcjob.user_2 as primaryContact, jcjob.name as jobName, customer.cusnum as customerNumber, customer.name as customerName from prledgerjc inner join jcjob on prledgerjc.jobnum = jcjob.jobnum inner join customer on jcjob.cusnum = customer.cusnum where checkdate >= {d'" + checkStartDate + "'} and checkdate <= {d'" + checkEndDate + "'} and prledgerjc.jobnum is not null and (prledgerjc.jobnum like 'C%' or prledgerjc.jobnum like 'P%')";
OdbcConnectionStringBuilder just = new OdbcConnectionStringBuilder();
just.Driver = "ComputerEase";
just.Add("Dsn", "Company 0");
just.Add("Uid", Uid);
just.Add("Pwd", Pwd);
cn = new OdbcConnection(just.ConnectionString);
cmd = new OdbcCommand(JobsQuery, cn);
cn.Open();
log.Info("[ProcessNewJobsData] Connection to database opened successfully");
OdbcDataReader reader = cmd.ExecuteReader();
try
{
var EmployeeEmailAddresses = GetEmployees(cn);
var jobNumColumn = reader.GetOrdinal("jobnum");
var jobNameColumn = reader.GetOrdinal("jobName");
var customerNumberColumn = reader.GetOrdinal("customerNumber");
var customerNameColumn = reader.GetOrdinal("customerName");
var primaryContactColumn = reader.GetOrdinal("primaryContact");
var executiveNewJobNotifications = new List <String>();
while (reader.Read())
{
var jobNumber = reader.GetString(jobNumColumn);
var jobName = reader.GetString(jobNameColumn);
var primaryContact = reader.GetString(primaryContactColumn);
var customerNumber = reader.GetString(customerNumberColumn);
var customerName = reader.GetString(customerNameColumn);
log.Info("\r\n----------------- Found Job Number " + jobNumber + " for contact " + primaryContact + ": " + GetEmployeeInformation(EmployeeEmailAddresses, primaryContact).EmailAddress + " -------------------");
var totalActualHoursForJob = GetTotalActualHoursForJob(cn, jobNumber);
var totalEstimatedHoursForJob = GetTotalEstimatedHoursForJob(cn, jobNumber);
var x = GetHoursEnteredDuringPayPeriod(cn, jobNumber, runDate, totalActualHoursForJob, totalEstimatedHoursForJob);
log.Info(" totalActualHoursForJob: " + totalActualHoursForJob.ToString());
log.Info(" totalEstimatedHoursForJob: " + totalEstimatedHoursForJob.ToString());
var emp = GetEmployeeInformation(EmployeeEmailAddresses, primaryContact);
var message = string.Format(MessageBodyFormat, jobNumber, jobName, emp.Name, customerNumber, customerName, checkStartDate) + x;
emp.AddMessageToNotify(message);
if ((Mode == monitor) || (Mode == debug))
{
executiveNewJobNotifications.Add(message);
}
}
if ((Mode == live) || (Mode == monitor))
{
foreach (var emp in EmployeeEmailAddresses)
{
if (emp.JobPerformanceMessage.Count > 0)
{
var r = MessageBodyHeader;
foreach (var jobMessage in emp.JobPerformanceMessage)
{
r += jobMessage;
}
r += EndMessageBody;
var emailSubject = string.Format(EmailSubject, checkStartDate);
sendEmail(emp.EmailAddress, emailSubject, r);
}
}
}
if (((Mode == monitor) || (Mode == debug)) && (executiveNewJobNotifications.Count() > 0))
{
var executiveMessage = MessageBodyHeader;
foreach (var jobMessage in executiveNewJobNotifications)
{
executiveMessage += jobMessage;
}
executiveMessage += EndMessageBody;
var emailSubject = string.Format(EmailSubject, checkStartDate);
foreach (var executive in MonitorEmailAddresses)
{
sendEmail(executive, emailSubject + " - Executive", executiveMessage);
}
}
}
catch (Exception x)
{
log.Error("[ProcessNewJobsData] Reader Error: " + x.Message);
}
reader.Close();
cn.Close();
}
catch (Exception x)
{
log.Error("[ProcessNewJobsData] Exception: " + x.Message);
return;
}
return;
}
/// <summary>
/// Returns the Connection String
/// </summary>
/// <returns></returns>
private string ToConnectionString()
{
if (string.IsNullOrEmpty(this._dataProvider))
{
return(string.Empty);
}
bool bTrustedConnection = (this._protocol.ToLower() == "trusted" ? true : false);
DbProviderFactory factory = DbProviderFactories.GetFactory(this._dataProvider);
if (factory == null)
{
return(string.Empty);
}
// Determine if the Credentials are Encrypted
if (!string.IsNullOrEmpty(this._encryptKey.Key))
{
byte[] keyData = System.Text.Encoding.ASCII.GetBytes(this._encryptKey.Key);
byte[] saltData = Convert.FromBase64String(this._encryptKey.Salt);
this._user = Security.Cryptographer.Decrypt(this._user, keyData, saltData, Security.SymmetricAlgorithms.TripleDES, true);
this._password = Security.Cryptographer.Decrypt(this._password, keyData, saltData, Security.SymmetricAlgorithms.TripleDES, true);
}
// Build the Connection String
DbConnectionStringBuilder builder = factory.CreateConnectionStringBuilder();
if (builder is System.Data.SqlClient.SqlConnectionStringBuilder)
{
SqlConnectionStringBuilder sqlBuilder = (builder as SqlConnectionStringBuilder);
sqlBuilder.DataSource = this._server;
sqlBuilder.InitialCatalog = this._database;
sqlBuilder.LoadBalanceTimeout = 600;
sqlBuilder.PersistSecurityInfo = false;
sqlBuilder.Pooling = true;
sqlBuilder.Replication = false;
sqlBuilder.IntegratedSecurity = bTrustedConnection;
if (string.IsNullOrEmpty(this._user) & (this._server.ToLower().Contains("(local)") || this._server.ToLower() == Environment.MachineName.ToLower()))
{
sqlBuilder.IntegratedSecurity = true;
}
if (!sqlBuilder.IntegratedSecurity)
{
sqlBuilder.UserID = this._user;
sqlBuilder.Password = this._password;
}
}
else if (builder is System.Data.OleDb.OleDbConnectionStringBuilder)
{
OleDbConnectionStringBuilder oleBuilder = (builder as OleDbConnectionStringBuilder);
oleBuilder.DataSource = this._server;
oleBuilder.Provider = this._oleProvider;
if (this._oleProvider.ToLower().Contains("microsoft"))
{
oleBuilder.FileName = this._database;
}
else
{
oleBuilder.Add("Database", this._database);
}
oleBuilder.PersistSecurityInfo = false;
if (!string.IsNullOrEmpty(this._user))
{
oleBuilder.Add("UID", this._user);
}
if (!string.IsNullOrEmpty(this._password))
{
oleBuilder.Add("PWD", this._password);
}
}
else if (builder is System.Data.Odbc.OdbcConnectionStringBuilder)
{
OdbcConnectionStringBuilder odbcBuilder = (builder as OdbcConnectionStringBuilder);
odbcBuilder.Driver = this._oleProvider;
odbcBuilder.Dsn = this._database;
if (!string.IsNullOrEmpty(this._user))
{
odbcBuilder.Add("UID", this._user);
}
if (!string.IsNullOrEmpty(this._password))
{
odbcBuilder.Add("PWD", this._password);
}
}
else
{
return(string.Empty);
}
// Parse the Data Provider Options
if (!string.IsNullOrEmpty(this._options))
{
string[] sOptions = this._options.Split(new char[] { char.Parse(";") });
if (sOptions != null && sOptions.Length > 0)
{
foreach (string sOption in sOptions)
{
string[] sParts = sOption.Split(new char[] { char.Parse("=") });
if (sParts != null && sParts.Length == 2)
{
builder.Add(sParts[0], sParts[1]);
}
}
}
}
// Encrypt the Credentials
if (!string.IsNullOrEmpty(this._encryptKey.Key))
{
byte[] keyData = System.Text.Encoding.ASCII.GetBytes(this._encryptKey.Key);
byte[] saltData = Convert.FromBase64String(this._encryptKey.Salt);
this._user = Security.Cryptographer.Encrypt(this._user, keyData, saltData, Security.SymmetricAlgorithms.TripleDES, true);
this._password = Security.Cryptographer.Encrypt(this._password, keyData, saltData, Security.SymmetricAlgorithms.TripleDES, true);
}
return(builder.ConnectionString);
}
private void connectToDB(ConfigurationInformation b1ConnectionInfo)
{
bool blConnect = false;
try
{
_Logger.Debug("Retreiving connection information from Cache: " + b1ConnectionInfo.ConnectionId);
OdbcConnection objODBCCOnn = null;
objODBCCOnn = T1.CacheManager.CacheManager.Instance.getFromCache(b1ConnectionInfo.ConnectionId);
if (objODBCCOnn != null)
{
_Logger.Debug(b1ConnectionInfo.ConnectionId + "found. Checking Status");
if (objODBCCOnn.State != System.Data.ConnectionState.Open)
{
_Logger.Debug("Status " + objODBCCOnn.State.ToString() + " found. Starting connection mechanism");
blConnect = true;
T1.CacheManager.CacheManager.Instance.removeFromCache(b1ConnectionInfo.ConnectionId);
}
}
else
{
blConnect = true;
}
if (blConnect)
{
_Logger.Debug("Connecting configuration file " + b1ConnectionInfo.ConnectionId);
OdbcConnectionStringBuilder objODBC = new OdbcConnectionStringBuilder();
if (Settings._Main.isHANA)
{
objODBC.Driver = Settings._Main.HANADriver;
objODBC.Add("UID", b1ConnectionInfo.UserName);
objODBC.Add("PWD", b1ConnectionInfo.Password);
objODBC.Add("SERVERNODE", b1ConnectionInfo.Instance);
}
else
{
if (b1ConnectionInfo.B1DBServerType == SAPbobsCOM.BoDataServerTypes.dst_MSSQL2008)
{
objODBC.Driver = "SQL Server Native Client 10.0";
objODBC.Add("MultipleActiveResultSets", "True");
}
else if (b1ConnectionInfo.B1DBServerType == SAPbobsCOM.BoDataServerTypes.dst_MSSQL2012)
{
objODBC.Driver = "SQL Server Native Client 11.0";
}
else if (b1ConnectionInfo.B1DBServerType == SAPbobsCOM.BoDataServerTypes.dst_MSSQL2014)
{
objODBC.Driver = "SQL Server Native Client 11.0";
}
objODBC.Add("Uid", b1ConnectionInfo.UserName);
objODBC.Add("Pwd", b1ConnectionInfo.Password);
objODBC.Add("Server", b1ConnectionInfo.Instance);
objODBC.Add("Database", b1ConnectionInfo.DefaultSchema);
}
objODBCCOnn = new OdbcConnection(objODBC.ConnectionString);
objODBCCOnn.Open();
if (objODBCCOnn.State == System.Data.ConnectionState.Open)
{
_Logger.Debug("Adding connection id " + b1ConnectionInfo.ConnectionId + " to cache");
T1.CacheManager.CacheManager.Instance.addToCache(b1ConnectionInfo.ConnectionId, objODBCCOnn, T1.CacheManager.CacheManager.objCachePriority.NotRemovable);
}
else
{
_Logger.Error("Error connecting connection Id " + b1ConnectionInfo.ConnectionId);
}
}
}
catch (Exception er)
{
_Logger.Error("", er);
}
}
private void Open(string providerName, string connectionString)
{
switch (providerName.ToLower())
{
case "lcpi.ibprovider":
{
OleDbConnectionStringBuilder alias = new OleDbConnectionStringBuilder();
alias.Provider = "LCPI.IBProvider";
alias.Add("Location", connectionString);
alias.Add("User ID", "????????");
alias.Add("password", "????????");
alias.Add("ctype", "win1251");
alias.Add("auto_commit", true);
connection = new OleDbConnection(alias.ToString());
break;
}
case "odbc.firebird":
{
OdbcConnectionStringBuilder alias = new OdbcConnectionStringBuilder();
alias.Driver = "Firebird/InterBase(r) driver";
alias.Add("DBNAME", connectionString);
alias.Add("UID", "????????");
alias.Add("PWD", "????????");
alias.Add("CHARSET", "win1251");
alias.Add("DIALECT", "1");
connection = new OdbcConnection(alias.ToString());
break;
}
case "odbc.postgres":
case "odbc.postgresql":
{
OdbcConnectionStringBuilder alias = new OdbcConnectionStringBuilder();
string[] words = connectionString.Split(':');
if (words.Length < 1 || words.Length > 3)
{
throw new ArgumentException("PostgreSQL syntax: SERVER:DATABASE or SERVER:PORT:DATABASE");
}
alias.Driver = "PostgreSQL ODBC Driver(ANSI)";
alias.Add("Servername", words[0]);
if (words.Length == 2)
{
alias.Add("Database", words[1]);
}
else
{
alias.Add("Port", words[1]);
alias.Add("Database", words[2]);
}
byte[] key = { 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00 };
byte[] buffer = { 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00 };
for (int i = 0; i < buffer.Length; i++)
{
buffer[i] ^= key[i % key.Length];
}
alias.Add("Username", "????????");
alias.Add("Password", "{" + Encoding.UTF8.GetString(buffer) + "}");
connection = new OdbcConnection(alias.ToString());
break;
}
default: throw new ArgumentException("The '" + providerName + "' provider is not supported.");
}
if (!Machine.Interactive)
{
for (int i = 1; i <= (Machine.HasEntryPoint ? 8 : 15); i++)
{
try
{
connection.Open();
return;
}
catch (DbException)
{
}
Thread.Sleep(i * 1000);
}
}
connection.Open();
}
