private void button3_Click(object sender, EventArgs e)
{
if(Validate())
{
var srcDirectoryName = @"C:\Reportes";
var destDirectoryName = @"C:\Reportes\Merge\ReporteConsolidado" + mes_cmb.SelectedItem.ToString() + ".csv";
var allCsv = Directory.EnumerateFiles(srcDirectoryName, "*", System.IO.SearchOption.TopDirectoryOnly);
string[] header = { File.ReadLines(allCsv.First()).First(l => !string.IsNullOrWhiteSpace(l)) };
var mergedData = allCsv
.SelectMany(csv => File.ReadLines(csv)
.SkipWhile(l => string.IsNullOrWhiteSpace(l)).Skip(1)); // skip header of each file
File.WriteAllLines(destDirectoryName, header.Concat(mergedData));
MessageBox.Show("El reporte consolidado se ha generado exitosamente...");
IO io = new IO();
var dataTable = io.GetDataTabletFromCSVFile(destDirectoryName);
//var dataTable = GetDataTabletFromCSVFile(destDirectoryName);
DataAccess da = new DataAccess();
da.InsertDataIntoSQLServerUsingSQLBulkCopy(dataTable);
this.Close();
}
}
private void subirRegistroLocalMenu_Click(object sender, EventArgs e)
{
IO io = new IO();
if (io.UpdateDBase(ConfigurationManager.AppSettings["BackupPath"]))
{
Log.Info("UpdateDBase Passed Ok");
MessageBox.Show("Base de datos actualizada correctamente");
labelFichadasLocales.Visible = false;
GetStatus();
}
else
{
MessageBox.Show("Error al actualizar la base de datos. Comuniquese con JP.");
}
}
public void SaveAction(string sqlCommand, int userId, Enums.Actions action, string currentTime, string observaciones, string station)
{
MySqlCommand command = new MySqlCommand();
command.Connection = conn;
command.CommandType = CommandType.Text;
command.CommandText = sqlCommand;
string query = sqlCommand;
Thread.CurrentThread.CurrentCulture = new CultureInfo("es-AR");
DateTime dt = DateTime.ParseExact(currentTime, ConfigurationManager.AppSettings["DateTimeFormat"], CultureInfo.CurrentCulture);
command.Parameters.AddWithValue("@userId", userId);
command.Parameters.AddWithValue("@fecha", dt.ToShortDateString());
command.Parameters.AddWithValue("@hora", dt.ToString("HH:mm"));
command.Parameters.AddWithValue("@actionId", action);
command.Parameters.AddWithValue("@station", station);
command.Parameters.AddWithValue("@observaciones", observaciones == "Observaciones..." ? null : observaciones);
Log.Info("Id de Usuario para insert = " + userId.ToString());
try
{
conn.Open();
//conn.Open();
// ... other parameters
command.ExecuteNonQuery();
conn.Close();
}
catch(Exception ex)
{
//Creates the INSERT command to save in a file in case the connection drops
var queryTest = CommandAsSql(command);
IO io = new IO();
io.WriteToFile(ConfigurationManager.AppSettings["BackupPath"], queryTest);
throw new Exception("Error en conexion. Se ha registrado la actividad en forma local.\n\r" + ex.Message);
}
}
public void SaveOfflineAction(string sqlCommand)
{
MySqlCommand command = new MySqlCommand();
command.Connection = conn;
command.CommandType = CommandType.Text;
command.CommandText = sqlCommand;
try
{
conn.Open();
command.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
//Creates the INSERT command to save in a file in case the connection drops
var queryTest = CommandAsSql(command);
IO io = new IO();
io.WriteToFile(ConfigurationManager.AppSettings["BackupPath"], queryTest);
throw new Exception("Error en conexion. Se ha registrado la actividad en forma local.\n\r" + ex.Message);
}
}
public void GetRecords(string storedProcedure, string Filename, string date1, string date2, int userId = 0)
{
conn.Open();
MySqlCommand command = new MySqlCommand(storedProcedure, conn);
Thread.CurrentThread.CurrentCulture = new CultureInfo("es-AR");
var dateFrom = DateTime.ParseExact(date1, ConfigurationManager.AppSettings["DateFormat"], CultureInfo.InvariantCulture);
var dateTo = DateTime.ParseExact(date2, ConfigurationManager.AppSettings["DateFormat"], CultureInfo.InvariantCulture);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@userId", userId);
command.Parameters.AddWithValue("@dateFrom", date1);
command.Parameters.AddWithValue("@dateTo", date2);
var queryTest = CommandAsSql(command);
MySqlDataReader dr = command.ExecuteReader();
IO io = new IO();
ExcelUtility eu = new ExcelUtility();
var dataTable = new DataTable();
dataTable.Load(dr);
List<DataTable> result = dataTable.AsEnumerable()
.GroupBy(row => row.Field<string>("username"))
.Select(g => g.CopyToDataTable())
.ToList();
DataSet tables = new DataSet();
foreach (var t in result)
{
t.TableName = t.Select().First().Field<string>(1);
tables.Tables.Add(t);
}
eu.ExportDataSetToExcel(tables, Application.StartupPath, Filename);
//eu.WriteDataTableToExcel(dataTable,"Prueba","C:\\" + Filename,"Test");
//io.WriteToFile(Filename, dr);
}
public void GetRecords(string storedProcedure, string Filename, string date1, string date2, int userId = 0)
{
conn.Open();
SqlCommand command = new SqlCommand(storedProcedure, conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@userId", SqlDbType.Int).Value = userId;
command.Parameters.Add("@dateFrom", SqlDbType.DateTime).Value = DateTime.ParseExact(date1, ConfigurationManager.AppSettings["DateFormat"], CultureInfo.InvariantCulture);
command.Parameters.Add("@dateTo", SqlDbType.DateTime).Value = DateTime.ParseExact(date2, ConfigurationManager.AppSettings["DateFormat"], CultureInfo.InvariantCulture);
SqlDataReader dr = command.ExecuteReader();
IO io = new IO();
io.WriteToFile(Filename, dr);
}