private void Button_Click_2(object sender, RoutedEventArgs e)
{
var rep = new VisitorRepositoryDTO();
var visitorsInDB = rep.GetAllVisitors().ToList();
FileDialog fileDialog = new OpenFileDialog();
if (fileDialog.ShowDialog() == true)
{
ExelData exelData = new ExelData(fileDialog.FileName, 20);
for (int i = 1; i < exelData.excelWorksheetRow; i++)
{
try
{
var v = visitorsInDB
.Where(s => s.Column1 == exelData.data[i, 0])
.FirstOrDefault();
if (v.CurrentStatus == "actual")
{
v.Column12 = exelData.data[i, 11];
rep.AddOrUpdateVisitor(v);
}
}
catch { }
}
}
}
public void saveVisitorsTofile()
{
ExelData exelData = new ExelData();
var visitors = context.Visitors;
exelData.saveVisitorsTofile(visitors);
}
public void importRepositoryFromFileWithId(string fileName, Action <Progress_Bar> progressChanged)
{
ExelData exelData = new ExelData(fileName, progressChanged);
progress.Status = "Delete old visitors";
progress.Progress = 0;
using (var ctx = new EContext())
{
int count = 1;
var collection = ctx.Visitors.ToList();
var size = collection.Count() + 1;
foreach (var u in collection)
{
progress.Progress = (int)(count * 100 / size);
progressChanged(progress);
ctx.Visitors.Remove(u);
count++;
}
ctx.SaveChanges();
var dat = new List <Visitor>();
exelData.importVisitorsToCollectionWithId(dat, progressChanged);
progress.Status = "Add new data to database";
progress.Progress = 0;
int co = 1;
var sd = dat.Count() + 1;
foreach (var d in dat)
{
const string InsertQuery = @"SET IDENTITY_INSERT dbo.Visitors ON;
INSERT INTO Visitors(Id, Column1, Column2,
Column3, Column4, Column5, Column6, Column7,
Column8, Column9, Column10, Column11, Column12,
Column13, Column14, Column15, CurrentStatus)
VALUES({0},{1},{2},{3},{4},{5},{6},{7},
{8},{9},{10},{11},{12},{13},{14},{15},{16});
SET IDENTITY_INSERT dbo.Visitors OFF;";
context.Database.ExecuteSqlCommand(InsertQuery, d.Id, d.Column1,
d.Column2, d.Column3, d.Column4, d.Column5, d.Column6, d.Column7,
d.Column8, d.Column9, d.Column10, d.Column11, d.Column12,
d.Column13, d.Column14, d.Column15, d.CurrentStatus);
context.SaveChanges();
progress.Progress = (int)(co * 100 / sd);
progressChanged(progress);
co++;
}
}
progress.Status = "Add new data to collection";
progress.Progress = 0;
int c = 1;
var col = context.Visitors.ToList();
var s = col.Count() + 1;
foreach (var v in col)
{
progress.Progress = (int)(c * 100 / s);
progressChanged(progress);
AddOrUpdateVisitor(v);
c++;
}
}
public void SaveStatusesToFile()
{
var statuses = context.Statuses;
ExelData exelData = new ExelData();
exelData.saveStatusesToFile(statuses);
}
public IEnumerable <Visitor> AddDataToRepositoryFromFile(string fileName)
{
ExelData exelData = new ExelData(fileName);
exelData.setDataToCollection(context.Visitors);
context.SaveChanges();
var v = context.Visitors.ToList();
return(v);
}
public VisitorRepository(string file)
{
context = new EContext();
if (!context.Database.Exists())
{
context.Database.Delete();
context.SaveChanges();
exelData = new ExelData(file);
exelData.setDataToCollection(context.Visitors);
context.SaveChanges();
}
}
public void importStatusRepositoryFromFileWithId(string fileName, Action <Progress_Bar> progressChanged)
{
ExelData exelData = new ExelData(fileName, progressChanged);
progress.Status = "Delete old statuses";
progress.Progress = 0;
using (var ctx = new EContext())
{
int count = 1;
var collection = ctx.Statuses.ToList();
var size = collection.Count() + 1;
foreach (var stat in collection)
{
progress.Progress = (int)(count * 100 / size);
progressChanged(progress);
ctx.Statuses.Remove(stat);
count++;
}
ctx.SaveChanges();
var dat = new List <Status>();
exelData.importSatausToCollectionWithId(dat, progressChanged);
progress.Status = "Add new data to database";
progress.Progress = 0;
int co = 1;
var sd = dat.Count() + 1;
foreach (var d in dat)
{
const string InsertQuery = @"SET IDENTITY_INSERT dbo.Status ON;
INSERT INTO Status(Id, Name, ActionTime,
UserId, VisitorId)
VALUES({0},{1},{2},{3},{4});
SET IDENTITY_INSERT dbo.Status OFF;";
try
{
context.Database.ExecuteSqlCommand(InsertQuery, d.Id, d.Name,
d.ActionTime, d.UserId, d.VisitorId);
context.SaveChanges();
}
catch { }
progress.Progress = (int)(co * 100 / sd);
progressChanged(progress);
co++;
}
}
}
private void загрузкаФайлаToolStripMenuItem_Click(object sender, EventArgs e)
{
OpenFileDialog s = new OpenFileDialog();
if (s.ShowDialog() == DialogResult.Cancel)
{
return;
}
string filename = s.FileName;
data = new ExelData(filename, this.pb_exel_to_db);
data.createForDatabase();
// data.getForDataToDatabase();
data.getForDataToDatabase(this.pb_exel_to_db);
pb_exel_to_db.Visible = false;
}
public void initRepositoryFromFile(string fileName, Action <Progress_Bar> progressChanged)
{
ExelData exelData = new ExelData(fileName, progressChanged);
progress.Status = "Delete old visitors";
progress.Progress = 0;
using (var ctx = new EContext())
{
int count = 1;
var collection = ctx.Visitors.ToList();
var size = collection.Count() + 1;
foreach (var u in collection)
{
progress.Progress = (int)(count * 100 / size);
progressChanged(progress);
ctx.Visitors.Remove(u);
count++;
}
ctx.SaveChanges();
}
exelData.setDataToCollection(context.Visitors, progressChanged);
context.SaveChanges();
progress.Status = "Add new data to collection";
progress.Progress = 0;
int c = 1;
var col = context.Visitors;
var s = col.Count() + 1;
foreach (var v in col)
{
progress.Progress = (int)(c * 100 / s);
progressChanged(progress);
// visitorCollection.Add(v);
AddOrUpdateVisitor(v);
c++;
}
}
// SaveFileDialog sfd = new SaveFileDialog();
// DialogResult res = sfd.ShowDialog();
//if (res == DialogResult.OK)
//{
// string path = sfd.FileName + ".xlsx";
// Microsoft.Office.Interop.Excel.Application exelapp = new Microsoft.Office.Interop.Excel.Application();
// Microsoft.Office.Interop.Excel.Workbook workbook = exelapp.Workbooks.Add();
// Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.ActiveSheet;
// pb.Visible = true;
// pb.Maximum = dgv_visitors.RowCount-1;
// worksheet.Rows[1].Columns[1] = "Имя";
// worksheet.Rows[1].Columns[2] = "Фамилия";
// worksheet.Rows[1].Columns[3] = "Отчество";
// worksheet.Rows[1].Columns[4] = "Компания";
// worksheet.Rows[1].Columns[5] = "Должность";
// worksheet.Rows[1].Columns[6] = "Штрихкод";
// worksheet.Rows[1].Columns[7] = "Вы являетесь";
// worksheet.Rows[1].Columns[8] = "Телефон мобильный";
// worksheet.Rows[1].Columns[9] = "Телефон рабочий";
// worksheet.Rows[1].Columns[10] = "E-Mail";
// worksheet.Rows[1].Columns[11] = "Дата";
// worksheet.Rows[1].Columns[12] = "Выставка";
// worksheet.Rows[1].Columns[13] = "Доклад";
// worksheet.Rows[1].Columns[14] = "Город";
// for (int i = 1; i<dgv_visitors.RowCount; i++)
// {
// pb.Value = i;
// worksheet.Rows[i + 1].Columns[1] = dgv_visitors.Rows[i - 1].Cells[1].Value;
// worksheet.Rows[i + 1].Columns[2] = dgv_visitors.Rows[i - 1].Cells[2].Value;
// worksheet.Rows[i + 1].Columns[3] = dgv_visitors.Rows[i - 1].Cells[3].Value;
// worksheet.Rows[i + 1].Columns[4] = dgv_visitors.Rows[i - 1].Cells[4].Value;
// worksheet.Rows[i + 1].Columns[5] = dgv_visitors.Rows[i - 1].Cells[5].Value;
// worksheet.Rows[i + 1].Columns[6] = dgv_visitors.Rows[i - 1].Cells[6].Value;
// worksheet.Rows[i + 1].Columns[7] = dgv_visitors.Rows[i - 1].Cells[7].Value;
// worksheet.Rows[i + 1].Columns[8] = dgv_visitors.Rows[i - 1].Cells[8].Value;
// worksheet.Rows[i + 1].Columns[9] = dgv_visitors.Rows[i - 1].Cells[9].Value;
// worksheet.Rows[i + 1].Columns[10] = dgv_visitors.Rows[i - 1].Cells[10].Value;
// worksheet.Rows[i + 1].Columns[11] = dgv_visitors.Rows[i - 1].Cells[11].Value;
// worksheet.Rows[i + 1].Columns[12] = dgv_visitors.Rows[i - 1].Cells[12].Value;
// worksheet.Rows[i + 1].Columns[13] = dgv_visitors.Rows[i - 1].Cells[13].Value;
// worksheet.Rows[i + 1].Columns[14] = dgv_visitors.Rows[i - 1].Cells[14].Value;
// }
// pb.Visible = false;
// exelapp.AlertBeforeOverwriting = false;
// workbook.SaveAs(path);
// exelapp.Quit();
//}
private void Button_Click_3(object sender, RoutedEventArgs e)
{
FileDialog openFileDialog = new OpenFileDialog();
List <VisitorDTO> addedInfVisitors;
if (openFileDialog.ShowDialog() == true)
{
ExelData exelData = new ExelData(openFileDialog.FileName, 20);
addedInfVisitors = new List <VisitorDTO>();
for (int i = 0; i < exelData.excelWorksheetRow; i++)
{
var nv = new VisitorDTO
{
Column1 = exelData.data[i, 0],
Column2 = exelData.data[i, 1],
Column3 = exelData.data[i, 2],
Column4 = exelData.data[i, 3],
Column5 = exelData.data[i, 4],
Column6 = exelData.data[i, 5],
Column7 = exelData.data[i, 6],
Column8 = exelData.data[i, 7],
Column9 = exelData.data[i, 8],
Column10 = exelData.data[i, 14],
Column11 = exelData.data[i, 15],
Column12 = exelData.data[i, 16],
Column13 = exelData.data[i, 17],
Column14 = exelData.data[i, 18],
Column15 = exelData.data[i, 19],
CurrentStatus = "registered"
};
addedInfVisitors.Add(nv);
}
FileDialog saveFileDialog = new SaveFileDialog();
if (saveFileDialog.ShowDialog() == true)
{
var path = saveFileDialog.FileName + ".xlsx";
var repv = new VisitorRepositoryDTO();
var vindb = repv.GetAllVisitors();
var reps = new StatusRepositoryDTO();
var sindb = reps.GetAllStatuses();
Microsoft.Office.Interop.Excel.Application exelapp =
new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook =
exelapp.Workbooks.Add();
Microsoft.Office.Interop.Excel.Worksheet worksheet =
workbook.ActiveSheet;
int i = 0;
foreach (var stat in sindb)
{
var vis = vindb.Where(s => s.Id == stat.VisitorId).FirstOrDefault();
var visAddInf = addedInfVisitors.Where(s => s.Column1 == vis.Column1).FirstOrDefault();
var choice = vis.Column1.Split('-')[0];
switch (choice)
{
case "VHS":
worksheet.Rows[i + 1].Columns[1] = vis.Column1;
worksheet.Rows[i + 1].Columns[2] = vis.Column2;
worksheet.Rows[i + 1].Columns[3] = vis.Column3;
worksheet.Rows[i + 1].Columns[4] = vis.Column4;
worksheet.Rows[i + 1].Columns[5] = vis.Column5;
worksheet.Rows[i + 1].Columns[6] = stat.Name;
worksheet.Rows[i + 1].Columns[7] = vis.Column7;
worksheet.Rows[i + 1].Columns[8] = vis.Column8;
worksheet.Rows[i + 1].Columns[9] = vis.Column9;
worksheet.Rows[i + 1].Columns[10] = vis.Column10;
worksheet.Rows[i + 1].Columns[11] = vis.Column11;
worksheet.Rows[i + 1].Columns[12] = stat.ActionTime;
worksheet.Rows[i + 1].Columns[12] = vis.Column12;
worksheet.Rows[i + 1].Columns[13] = vis.Column13;
worksheet.Rows[i + 1].Columns[14] = vis.Column14;
worksheet.Rows[i + 1].Columns[15] = " ";
worksheet.Rows[i + 1].Columns[16] = " ";
worksheet.Rows[i + 1].Columns[17] = " ";
worksheet.Rows[i + 1].Columns[18] = " ";
worksheet.Rows[i + 1].Columns[19] = " ";
worksheet.Rows[i + 1].Columns[20] = " ";
worksheet.Rows[i + 1].Columns[21] = stat.ActionTime;
break;
default:
worksheet.Rows[i + 1].Columns[1] = vis.Column1;
worksheet.Rows[i + 1].Columns[2] = vis.Column2;
worksheet.Rows[i + 1].Columns[3] = vis.Column3;
worksheet.Rows[i + 1].Columns[4] = vis.Column4;
worksheet.Rows[i + 1].Columns[5] = vis.Column5;
worksheet.Rows[i + 1].Columns[6] = stat.Name;
worksheet.Rows[i + 1].Columns[7] = vis.Column7;
worksheet.Rows[i + 1].Columns[8] = vis.Column8;
worksheet.Rows[i + 1].Columns[9] = vis.Column9;
worksheet.Rows[i + 1].Columns[10] = vis.Column10;
worksheet.Rows[i + 1].Columns[11] = vis.Column11;
worksheet.Rows[i + 1].Columns[12] = vis.Column12;
worksheet.Rows[i + 1].Columns[13] = vis.Column13;
worksheet.Rows[i + 1].Columns[14] = vis.Column14;
worksheet.Rows[i + 1].Columns[15] = visAddInf.Column10;
worksheet.Rows[i + 1].Columns[16] = visAddInf.Column11;
worksheet.Rows[i + 1].Columns[17] = visAddInf.Column12;
worksheet.Rows[i + 1].Columns[18] = visAddInf.Column13;
worksheet.Rows[i + 1].Columns[19] = visAddInf.Column14;
worksheet.Rows[i + 1].Columns[20] = visAddInf.Column15;
switch (stat.Name)
{
case "registered":
var temp = visAddInf.Column15;
var time = temp.Replace("T", " ");
var time_correct = time.Split('+')[0] + ":00";
worksheet.Rows[i + 1].Columns[21] = time_correct;
break;
default:
worksheet.Rows[i + 1].Columns[21] = stat.ActionTime;
break;
}
// worksheet.Rows[i + 1].Columns[21] = stat.ActionTime;
break;
}
i++;
}
exelapp.AlertBeforeOverwriting = false;
workbook.SaveAs(path);
exelapp.Quit();
}
}
}
private void btn_export_Click(object sender, EventArgs e)
{
ExelData data = new ExelData();
data.saveDataToFile(this.dgv_visitors, this.progressBar1);
}
static void Main(string[] args)
{
ExelData e = new ExelData(@"C: \Users\vlad\Documents\index.html");
//ExhibitionDataForContext context = new ExhibitionDataForContext();
//City city = new City();
//city.Name = "Дебальцево";
//Company company = new Company();
//company.Name = "Итератор";
//Position position = new Position();
//position.Name = "Программист";
//Description description = new Description();
//description.Name = "ИТ Компания";
//Exhibit exhibition = new Exhibit();
//exhibition.Name = "МУК";
//Raport raport = new Raport();
//raport.Name = "Программирование это просто";
// context.Database.Delete();
//context.Cities.Add(city);
//context.Cities.Add(new City("Енакиево"));
//context.Cities.Add(new City("Горловка"));
//context.Companies.Add(company);
//context.Companies.Add(new Company("эсэнти"));
//context.Companies.Add(new Company("Энергетика и климат"));
//context.Positions.Add(position);
//context.Positions.Add(new Position("Инженер"));
//context.Positions.Add(new Position("Дизайнер"));
//context.Descriptions.Add(description);
//context.Descriptions.Add(new Description("Вендор"));
//context.Descriptions.Add(new Description("Прессса"));
//context.Exhibits.Add(exhibition);
//context.Exhibits.Add(new Exhibit("ПУК"));
//context.Exhibits.Add(new Exhibit("ТУК"));
//context.Raports.Add(raport);
//context.Raports.Add(new Raport("Посмотри как я рисую"));
//context.Raports.Add(new Raport("220 Киловат"));
//context.SaveChanges();
//ExhibitionVisitor exhibitionVisitor = new ExhibitionVisitor();
//exhibitionVisitor.FirstName = "Григорий";
//exhibitionVisitor.LastName = "Турчинов";
//exhibitionVisitor.PhoneNumber = "+3805012345679";
//exhibitionVisitor.Email = "*****@*****.**";
//exhibitionVisitor.BarCode = "9876543210";
//exhibitionVisitor.Status = "norm";
//exhibitionVisitor.WorkPhone = "04412345678";
//exhibitionVisitor.Pathronim = "Григорьевич";
//exhibitionVisitor.CityId = context.Cities.Where(c=>c.Name == "Енакиево").Select(cc=>cc.Id).FirstOrDefault();
//exhibitionVisitor.CompanyId = context.Companies.Where(co=>co.Name == "Энергетика и климат").Select(coc=>coc.Id).FirstOrDefault();
//exhibitionVisitor.ExhibitId = context.Exhibits.Where(e=>e.Name == "ПУК").Select(ex=>ex.Id).FirstOrDefault();
//exhibitionVisitor.PositionId = context.Positions.Where(p=>p.Name == "Дизайнер").Select(po=>po.Id).FirstOrDefault();
//exhibitionVisitor.DescriptionId = context.Descriptions.Where(d=>d.Name == "Вендор").Select(dd=>dd.Id).FirstOrDefault();
//exhibitionVisitor.RaportId = context.Raports.Where(r=>r.Name == "Посмотри как я рисую").Select(rr=>rr.Id).FirstOrDefault();
//context.ExhibitionVisitors.Add(exhibitionVisitor);
//context.SaveChanges();
// ExcelData exel_data = new ExcelData();
// exel_data.getDataToDatabase();
}