/// <summary>
        /// Gets a list of all the files stored in the database
        /// </summary>
        public static async Task<List<string>> GetAllFileNamessAsync()
        {
            string connectionString = defaultConnectionString;

            using (var connection = new OleDbConnection(connectionString))
            {
                try
                {
                    await connection.OpenAsync();

                    string query = "SELECT Name FROM Files;";
                    using (var command = new OleDbCommand(query, connection))
                    {
                        var reader = await command.ExecuteReaderAsync();

                        var list = new List<string>();

                        while (await reader.ReadAsync())
                            list.Add(reader.GetString(0));

                        return list;
                    }
                }
                catch (Exception ex)
                {
                    TextEditor.WPFMessageBoxException(ex);
                    return null;
                }
            }
        }
 public async Task<IList<ServiceZipDetailsSet>> GetServiceZips(string company, string filename, string month, int year)
 {
     var serviceZips = new List<ServiceZipDetailsSet>();
     var conn = new OleDbConnection(string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"", filename));
     await conn.OpenAsync();
     var cmd = new OleDbCommand();
     cmd.Connection = conn;
     cmd.CommandText = "SELECT * FROM [Лист1$]";
     var reader = await cmd.ExecuteReaderAsync();
     while (reader.Read())
     {
         int q = 0;
         if (reader["Quantity"] != DBNull.Value)
             int.TryParse(reader["Quantity"].ToString(), out q);
         if (reader["BlankNumber"].ToString().Trim() == "tr")
         {
             //here
         }
         serviceZips.Add(new ServiceZipDetailsSet
         {
             WorkDate = Convert.ToDateTime(reader["Date"]),
             BlankNumber = reader["BlankNumber"].ToString().Trim(),
             ZipName = reader["Work"].ToString().Trim(),
             StoreNumber = Convert.ToInt16(reader["StoreNumber"]),
             ZipPrice = Convert.ToDecimal(reader["Price"]),
             Company = company,
             ServiceMonth = month,
             ServiceYear = year,
             ZipQuantity = q
         });               
     }
     reader.Close();
     conn.Close();
     return serviceZips;
 }
Пример #3
0
        public override async Task<RopDocument[]> FillDate()
        {
            
            List<RopData> data=new List<RopData>();
            var dd = new DownloadData();
            var dataBytes = await dd.Data(rdBase.PathDocument);

            var path = Path.Combine(Path.GetTempPath(), Path.GetTempFileName() + ".xls");
            File.WriteAllBytes(path,dataBytes);
            //var path = "D:\\a.xls";
            var dt = new DataTable();
            using (var m = new OleDbConnection())
            {
                m.ConnectionString = ExcelHelpers.BuildExcelConnectionString(path, true);
                m.Open();
                var query = @"Select * From [Sheet1$]";
                using (var cmd = new OleDbCommand(query, m))
                {
                    using (var dr = cmd.ExecuteReaderAsync().Result)
                    {
                        
                        dt.Load(dr);
                        
                    }
                }

            }
            foreach (DataRow row in dt.Rows)
            {
                int nr;
                var arr = row.ItemArray;
                //if (arr[0] == null || string.IsNullOrWhiteSpace(arr[0].ToString()))
                if (string.IsNullOrWhiteSpace(arr[0]?.ToString()))
                    continue;

                if(!int.TryParse(arr[0].ToString(),out nr))
                    continue;

                var numeJudet = arr[1].ToString().Trim().ToLower();
                var judet = judetFinder.Find(numeJudet);
                var rd=new RopData();
                rd.Judet = judet;
                rd.Valoare = int.Parse(arr[2].ToString());
                rd.Oras = null;
                data.Add(rd);



            }
            rdBase.Data = data.ToArray();
            return new [] { rdBase };
        }
Пример #4
0
 /// <summary>
 /// Method to Get All the Records from Excel
 /// </summary>
 /// <returns></returns>
 public async Task<ObservableCollection<Employee>> GetDataFormExcelAsync()
 {
     ObservableCollection<Employee> Employees = new ObservableCollection<Employee>(); 
     await Conn.OpenAsync();
     Cmd = new OleDbCommand();
     Cmd.Connection = Conn;
     Cmd.CommandText = "Select * from [Sheet1$]";
     var Reader = await Cmd.ExecuteReaderAsync();
     while (Reader.Read())
     {
         Employees.Add(new Employee() { 
             EmpNo = Convert.ToInt32(Reader["EmpNo"]),
             EmpName = Reader["EmpName"].ToString(),
             DeptName = Reader["DeptName"].ToString(),
             Salary = Convert.ToInt32(Reader["Salary"]) 
         });
     }
     Reader.Close();
     Conn.Close();
     return Employees;
 }
        public override async Task<RopDocument[]> FillDate()
        {

            var dataAlegatori = new List<RopData>();
            var dataAlegatoriPrezenti = new List<RopData>();
            var dd = new DownloadData();
            var dataBytes = await dd.Data(rdBase.PathDocument);

            var path = Path.Combine(Path.GetTempPath(), Path.GetTempFileName() + ".xls");
            File.WriteAllBytes(path, dataBytes);
            //var path = "D:\\a.xls";
            var dt = new DataTable();
            using (var m = new OleDbConnection())
            {
                m.ConnectionString = ExcelHelpers.BuildExcelConnectionString(path, true);
                m.Open();
                var query = @"Select * From [SIAEP2014_STAT_Statistica pe lo$]";
                using (var cmd = new OleDbCommand(query, m))
                {
                    using (var dr = cmd.ExecuteReaderAsync().Result)
                    {

                        dt.Load(dr);

                    }
                }

            }
            foreach (DataRow row in dt.Rows)
            {
                
                var arr = row.ItemArray;
                //if (arr[0] == null || string.IsNullOrWhiteSpace(arr[0].ToString()))
                if (string.IsNullOrWhiteSpace(arr[0]?.ToString()))
                    continue;
                var numeJudet = arr[1].ToString().Trim().ToLower();
                if(numeJudet == "străinătate")
                    continue;
                
                var judet = judetFinder.Find(numeJudet);
                var valoare = int.Parse(arr[3].ToString());
                var rd = new RopData();
                rd.Judet = judet;
                rd.Valoare = valoare;
                rd.Oras = null;
                dataAlegatori.Add(rd);

                rd = new RopData();
                rd.Judet = judet;
                rd.Valoare = int.Parse(arr[6].ToString());
                rd.Oras = null;
                dataAlegatoriPrezenti.Add(rd);
            }
            var newRD = new RopDocument(rdBase);
            newRD.Name += "Nr Alegatori Liste";
            newRD.ID = "BA671A2B-27E8-408D-BABD-59B52661789D";
            newRD.Data = dataAlegatori
                    .GroupBy(it => it.Judet).Select(group =>

                  new RopData()
                  {
                      Judet = group.Key,
                      Valoare = group.Sum(it => it.Valoare)
                  }
            ).ToArray();

            var newRDUrne = new RopDocument(rdBase);
            newRDUrne.Name += "Nr Alegatori Prezentati La Urne";
            newRDUrne.ID = "BBB0ECA2-34BE-4177-B3A2-82BC6B41311E";
            newRDUrne.Data = dataAlegatoriPrezenti
                .GroupBy(it => it.Judet).Select(group =>
                  new RopData()
                  {
                      Judet = group.Key,
                      Valoare = group.Sum(it => it.Valoare)
                  }
            ).ToArray();

            return new[] { newRD,newRDUrne };

        }
        public async Task<IRow[]> Query(IReadOnlyDictionary<string, string> columns)
        {
            if (null == columns)
            {
                throw new ArgumentNullException(CommaDelimitedFileAdapter.ArgumentNameColumns);
            }

            string query = 
                string.Format(
                    CultureInfo.InvariantCulture,
                    CommaDelimitedFileAdapter.QueryTemplate,
                    this.fileName);
            if (columns.Any())
            {
                IReadOnlyCollection<string> filters =
                    columns
                    .Select(
                        (KeyValuePair<string, string> item) =>
                            string.Format(
                                CultureInfo.InvariantCulture,
                                CommaDelimitedFileAdapter.FilterTemplate,
                                item.Key,
                                item.Value))
                    .ToArray();
                string filter =
                    string.Join(CommaDelimitedFileAdapter.DelimiterFilter, filters);

                query = string.Concat(query, CommaDelimitedFileAdapter.WhereClausePrefix, filter);
            }

            OleDbCommand commandQuery = null;
            try
            {
                commandQuery = new OleDbCommand(query, connection);
                DbDataReader reader = null;
                try
                {
                    reader = await commandQuery.ExecuteReaderAsync();
                    IList<IRow> rows = new List<IRow>();

                    while (reader.Read())
                    {
                        string rowKey = (string)reader[0];
                        Dictionary<string, string> rowColumns = 
                            new Dictionary<string, string>(this.headers.Count - 1);
                        for (int indexColumn = 1; indexColumn < this.headers.Count; indexColumn++)
                        {
                            string columnValue = reader[indexColumn] as string;
                            if (string.IsNullOrWhiteSpace(columnValue))
                            {
                                continue;
                            }

                            string columnHeader = this.headers.ElementAt(indexColumn);

                            rowColumns.Add(columnHeader, columnValue);
                        }

                        IRow row = new Row(rowKey, rowColumns);
                        rows.Add(row);
                    }

                    IRow[] results = rows.ToArray();
                    return results;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                        reader = null;
                    }
                }
            }
            finally
            {
                if (commandQuery != null)
                {
                    commandQuery.Dispose();
                    commandQuery = null;
                }
            }
        }
        /// <summary>
        /// Checks database for existence of a file name
        /// </summary>
        /// <param name="name"></param>
        /// <param name="connection"></param>
        /// <returns></returns>
        private static async Task<bool> FileExists(string name, OleDbConnection connection)
        {
            string query = "SELECT Name FROM Files WHERE name = @name;";

            using (var command = new System.Data.OleDb.OleDbCommand(query, connection))
            {
                command.Parameters.AddWithValue("@name", name);

                var reader = await command.ExecuteReaderAsync();

                // First run of ReadAsync() will return true if there are records matching its query
                return await reader.ReadAsync();
            }
        }
Пример #8
0
        public override async Task<RopDocument[]> FillDate()
        {

            var dataCasatoriti = new List<RopData>();

            var dd = new DownloadData();
            var dataBytes = await dd.Data(rdBase.PathDocument);

            var path = Path.Combine(Path.GetTempPath(), Path.GetTempFileName() + ".xls");
            File.WriteAllBytes(path, dataBytes);
            //var path = "D:\\a.xls";
            var dt = new DataTable();
            using (var m = new OleDbConnection())
            {
                m.ConnectionString = ExcelHelpers.BuildExcel2007ConnectionString(path, true);
                m.Open();
                var query = @"Select * From [Anul2015$]";
                using (var cmd = new OleDbCommand(query, m))
                {
                    using (var dr = cmd.ExecuteReaderAsync().Result)
                    {

                        dt.Load(dr);

                    }
                }

            }
            foreach (DataRow row in dt.Rows)
            {

                var arr = row.ItemArray;
                //if (arr[0] == null || string.IsNullOrWhiteSpace(arr[0].ToString()))
                if (string.IsNullOrWhiteSpace(arr[0]?.ToString()))
                    continue;
                var numeJudet = arr[1].ToString().Trim().ToLower();
                var judet = judetFinder.Find(numeJudet);
                Func<object, int> retVal = (obj) =>
                {
                    int i;
                    if (obj == null)
                        return 0;

                    if (int.TryParse(obj.ToString(), out i))
                        return i;

                    return 0;
                };
                int valoare = retVal(arr[15]);
                var rd = new RopData();
                rd.Judet = judet;
                rd.Valoare = valoare;
                rd.Oras = null;
                dataCasatoriti.Add(rd);


            }
            rdBase.Data = dataCasatoriti
                .GroupBy(it => it.Judet).Select(group =>

                    new RopData()
                    {
                        Judet = group.Key,
                        Valoare = group.Sum(it => it.Valoare)
                    }
                ).ToArray();

            return new[] {rdBase};
        }