public List <LogEntry> LoadLog(string lastForeignID)
{
return(_readDB.WithReadDBConnection(conn =>
{
var trans = conn.BeginTransaction();
try {
using (System.Data.OleDb.OleDbCommand cmd = (System.Data.OleDb.OleDbCommand)conn.CreateCommand()) {
((System.Data.IDbCommand)cmd).Transaction = trans;
long epoch = 1;
long lastID = 0;
DateTime lastDate = DateTime.MinValue;
bool useDate = false;
string[] s = lastForeignID.Split('-');
if (s.Length == 1)
{
epoch = 1;
if (!long.TryParse(s[0], out lastID))
{
useDate = true;
}
}
else if (s.Length == 2)
{
if (!long.TryParse(s[0], out epoch))
{
useDate = true;
}
if (!long.TryParse(s[1], out lastID))
{
useDate = true;
}
}
else if (s.Length == 3)
{
if (!long.TryParse(s[0], out epoch))
{
useDate = true;
}
if (!long.TryParse(s[1], out lastID))
{
useDate = true;
}
lastDate = DateTime.ParseExact(s[2], DateTimeFormat, null);
}
else
{
useDate = true;
}
if (useDate)
{
cmd.CommandText = "SELECT ID, Date, LogMessageCode, ResourceNumber, PartName, ProcessNumber," +
"FixedQuantity, PalletNumber, ProgramNumber, FromPosition, ToPosition " +
"FROM Log WHERE Date > ? ORDER BY ID ASC";
var param = cmd.CreateParameter();
param.OleDbType = System.Data.OleDb.OleDbType.Date;
param.Value = DateTime.Now.AddDays(-7);
cmd.Parameters.Add(param);
}
else
{
CheckIDRollover(trans, conn, ref epoch, ref lastID, lastDate);
cmd.CommandText = "SELECT ID, Date, LogMessageCode, ResourceNumber, PartName, ProcessNumber," +
"FixedQuantity, PalletNumber, ProgramNumber, FromPosition, ToPosition " +
"FROM Log WHERE ID > ? ORDER BY ID ASC";
var param = cmd.CreateParameter();
param.OleDbType = System.Data.OleDb.OleDbType.Numeric;
param.Value = lastID;
cmd.Parameters.Add(param);
}
var ret = new List <LogEntry>();
using (var reader = cmd.ExecuteReader()) {
while (reader.Read())
{
if (reader.IsDBNull(0))
{
continue;
}
if (reader.IsDBNull(1))
{
continue;
}
if (reader.IsDBNull(2))
{
continue;
}
if (!Enum.IsDefined(typeof(LogCode), reader.GetInt32(2)))
{
continue;
}
var e = new LogEntry();
e.ForeignID = epoch.ToString() + "-" + reader.GetInt32(0).ToString()
+ "-" + reader.GetDateTime(1).ToString(DateTimeFormat);
e.TimeUTC = new DateTime(reader.GetDateTime(1).Ticks, DateTimeKind.Local);
e.TimeUTC = e.TimeUTC.ToUniversalTime();
e.Code = (LogCode)reader.GetInt32(2);
e.StationNumber = reader.IsDBNull(3) ? -1 : reader.GetInt32(3);
e.FullPartName = reader.IsDBNull(4) ? "" : reader.GetString(4);
e.Process = reader.IsDBNull(5) ? 1 : reader.GetInt32(5);
e.FixedQuantity = reader.IsDBNull(6) ? 1 : reader.GetInt32(6);
e.Pallet = reader.IsDBNull(7) ? -1 : reader.GetInt32(7);
e.Program = reader.IsDBNull(8) ? "" : reader.GetInt32(8).ToString();
e.FromPosition = reader.IsDBNull(9) ? "" : reader.GetString(9);
e.TargetPosition = reader.IsDBNull(10) ? "" : reader.GetString(10);
int idx = e.FullPartName.IndexOf(':');
if (idx > 0)
{
e.JobPartName = e.FullPartName.Substring(0, idx);
}
else
{
e.JobPartName = e.FullPartName;
}
ret.Add(e);
}
}
trans.Commit();
return ret;
}
} catch {
trans.Rollback();
throw;
}
}));
}
//driver download from:https://www.microsoft.com/en-us/download/details.aspx?id=13255
public static void Save(this System.Data.DataSet dataSet, string fileName = null, string tableName = null, IWin32Window owner = null)
{
if (string.IsNullOrEmpty(fileName))
{
fileName = UI.FileIO.GetSaveFileName(owner, fileName ?? ".xls", "Comma Separated Values Files (*.csv)|*.csv|Microsoft Excel Files (*.xls)|*.xls|Microsoft Access Files (*.mdb)|*.mdb|All files (*.*)|*.*");
}
if (fileName == null)
{
return;
}
string providerName = IntPtr.Size == 8 ? "Microsoft.ACE.OLEDB.12.0" : "Microsoft.Jet.OLEDB.4.0";
string providerProds = null;
string tableNameFormat = "{0}";
string fileExtention = System.IO.Path.GetExtension(fileName).ToLower();
var dataSource = fileName;
switch (fileExtention)
{
case ".csv":
tableNameFormat = System.IO.Path.GetFileNameWithoutExtension(fileName) + "_{0}.csv";
dataSource = System.IO.Path.GetDirectoryName(fileName);
providerProds = "text;HDR=YES;FMT=Delimited";
break;
case ".xls": providerProds = "Excel 8.0;HDR=YES"; break;
case ".mdb": providerProds = "Access 8.0;HDR=YES"; break;
default: throw new Exception("unknown file extention");
}
using (var con = new System.Data.OleDb.OleDbConnection())
{
con.ConnectionString = "Provider=" + providerName + ";Data Source=" + dataSource + ";Extended Properties='" + providerProds + "'";
con.Open();
foreach (System.Data.DataTable t in dataSet.Tables)
{
if (tableName != null && tableName != t.TableName)
{
continue;
}
var exportName = string.Format(tableNameFormat, t.TableName);
using (var command = new System.Data.OleDb.OleDbCommand(null, con))
{
string sqlCreate = string.Format("Create Table {0} (", exportName);
string sqlInsert = string.Format("Insert Into {0} (", exportName);
string sqlInsertValues = "Values(";
foreach (System.Data.DataColumn c in t.Columns)
{
var p = command.CreateParameter();
p.SourceColumn = c.ColumnName;
command.Parameters.Add(p);
string oleDbType = "Note";
if (c.DataType == typeof(int) | c.DataType == typeof(short) | c.DataType == typeof(byte))
{
oleDbType = "LONG";
}
sqlCreate += string.Format("[{0}] {1}, ", c.ColumnName, oleDbType);
sqlInsert += string.Format("[{0}], ", c.ColumnName);
sqlInsertValues += "?, ";
}
command.CommandText = sqlCreate.TrimEnd(' ', ',') + ")";
command.ExecuteNonQuery();
command.CommandText = sqlInsert.TrimEnd(' ', ',') + ")" + sqlInsertValues.TrimEnd(' ', ',') + ")";
using (var adp = new System.Data.OleDb.OleDbDataAdapter(null, con))
{
if (t.TableName != exportName)
{
adp.TableMappings.Add(t.TableName, exportName);
}
adp.InsertCommand = command;
adp.Update(t);
adp.InsertCommand = null;
}
}
}
con.Close();
}
}
