/// <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;
}
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 };
}
/// <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();
}
}
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};
}