Ejemplo n.º 1
0
        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;
                }
            }));
        }
Ejemplo n.º 2
0
        //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();
            }
        }