protected void IndexParticipant(Participant p)
{
foreach (ParticipantClass c in p.Classes)
{
if (!ParticipantListByClass.ContainsKey(c.Id))
ParticipantListByClass.Add(c.Id, new List<Participant>());
ParticipantListByClass[c.Id].Add(p);
}
ParticipantByEmit.Add(p.EmitID, p);
}
public void AddParticipant(Participant p)
{
Participants.Add(p);
IndexParticipant(p);
}
public void BuildRace(Race race)
{
race.Name = _year;
MySqlConnection conn = new MySqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[_conn].ConnectionString);
conn.Open();
// Adding supers
var cmd = new MySqlCommand(@"select
kop_person.startNumber, kop_person.chipNumber, kop_person.firstname, kop_person.surname, kop_person.personClassCode, ifnull(kop_person.companyClass, ifnull(kop_team.companyClass, 0)) as companyClass, kop_team.companyName, kop_person.phoneNumber, kop_person.club,
case
when cupClass = 1 and (2015 - birthyear) in (10, 11,12) then concat('NM', gender, '11')
when cupClass = 1 and (2015 - birthyear) in (13,14) then concat('NM', gender, '13')
when cupClass = 1 and (2015 - birthyear) in (15,16) then concat('NM', gender, '15')
when cupClass = 1 and (2015 - birthyear) in (17,18,19,20,21) then concat('NM', gender, '17')
when cupClass = 1 and (2015 - birthyear) > 21 then concat('NM', gender, '22')
else null
end as cupClass
from
kop_person left join kop_team on kop_person.teamid = kop_team.id where superwife = 1 and kop_person.deleted = 0 and kop_person.startnumber is not null and kop_person.chipnumber is not null
order by startnumber", conn);
var data = cmd.ExecuteReader();
while (data.Read())
{
var p = new Participant()
{
Startnumber = data.GetInt32("startNumber"),
EmitID = int.Parse(data.GetString("chipNumber")),
Name = data.GetString("firstname") + " " + data.GetString("surname"),
Telephone = new List<String>() { data.GetString("phoneNumber") },
Classes = new List<ParticipantClass>() { race.Classes.Find(x => x.Id.Equals(data.GetString("personClassCode"))) },
IsTeam = false,
IsSuper = true,
IsCompany = data.GetInt32("companyClass").Equals(1),
CompanyName = data.IsDBNull(data.GetOrdinal("club")) ? "": data.GetString("club")
};
if (p.IsCompany)
{
p.Classes.Add(race.Classes.Find(x => x.Id.Equals(data.GetString("personClassCode").Substring(0, 2) + "BED")));
p.Classes.Add(race.Classes.Find(x => x.Id.Equals("BED")));
}
if (!data.IsDBNull(data.GetOrdinal("club")) && !String.IsNullOrWhiteSpace(data.GetString("club")))
{
var cn = data.GetString("club");
if (cn.StartsWith("SVV"))
cn = "SVV";
if (!race.Classes.Exists(x => x.Id.Equals(cn)))
race.Classes.Add(new ParticipantClass() { Id = cn, Official = false, Name = cn, Sequence = race.Classes.Count + 1 });
p.Classes.Add(race.Classes.Find(x => x.Id.Equals(cn)));
}
if (!data.IsDBNull(data.GetOrdinal("cupClass")))
p.Classes.Add(race.Classes.Find(x => x.Id.Equals(data.GetString("cupClass"))));
race.AddParticipant(p);
}
data.Close();
// Add NM Cup
cmd = new MySqlCommand(@"select
startNumber, chipNumber, firstname, surname, personClassCode, ifnull(companyClass, 0) as companyClass, companyName, phoneNumber,
case
when cupClass = 1 and (2015 - birthyear) in (10, 11,12) then concat('NM', gender, '11')
when cupClass = 1 and (2015 - birthyear) in (13,14) then concat('NM', gender, '13')
when cupClass = 1 and (2015 - birthyear) in (15,16) then concat('NM', gender, '15')
when cupClass = 1 and (2015 - birthyear) in (17,18,19,20,21) then concat('NM', gender, '17')
when cupClass = 1 and (2015 - birthyear) > 21 then concat('NM', gender, '22')
else null
end as cupClass
from kop_person where superwife = 0 and cupClass = 1 and deleted = 0 and startnumber is not null and chipnumber is not null", conn);
data = cmd.ExecuteReader();
while (data.Read())
{
var p = new Participant()
{
Startnumber = data.GetInt32("startNumber"),
EmitID = int.Parse(data.GetString("chipNumber")),
Name = data.GetString("firstname") + " " + data.GetString("surname"),
Telephone = new List<String>() { data.GetString("phoneNumber") },
Classes = new List<ParticipantClass>() { race.Classes.Find(x => x.Id.Equals(data.GetString("cupClass"))) },
IsTeam = false,
IsSuper = true,
IsCompany = data.GetInt32("companyClass").Equals(1),
CompanyName = data.IsDBNull(data.GetOrdinal("companyName")) ? "" : data.GetString("companyName")
};
if (p.IsCompany)
{
p.Classes.Add(race.Classes.Find(x => x.Id.Equals(data.GetString("personClassCode").Substring(0, 2) + "BED")));
p.Classes.Add(race.Classes.Find(x => x.Id.Equals("BED")));
}
if (!data.IsDBNull(data.GetOrdinal("companyName")) && !String.IsNullOrWhiteSpace(data.GetString("companyName")))
{
var cn = data.GetString("companyName");
if (cn.StartsWith("SVV"))
cn = "SVV";
if (!race.Classes.Exists(x => x.Id.Equals(cn)))
race.Classes.Add(new ParticipantClass() { Id = cn, Official = false, Name = cn, Sequence = race.Classes.Count + 1 });
p.Classes.Add(race.Classes.Find(x => x.Id.Equals(cn)));
}
race.AddParticipant(p);
}
data.Close();
// Adding teams
cmd = new MySqlCommand(@"SELECT t.startNumber, t.chipNumber, t.name, t.teamClassCode, t.companyClass, p.firstname, p.surname, p.phoneNumber, p.sprintNumber FROM kop_team t inner join kop_person p on t.id = p.teamid where t.deleted = 0 and t.startNumber is not null and t.chipNumber is not null order by t.startNumber, p.sprintNumber", conn);
data = cmd.ExecuteReader();
data.Read();
bool moreData = true;
do
{
var p = new Participant()
{
Startnumber = data.GetInt32("startNumber"),
EmitID = int.Parse(data.GetString("chipNumber")),
Name = data.GetString("name"),
Telephone = new List<String>() { data.GetString("phoneNumber").Replace(" ", "") },
Classes = new List<ParticipantClass> { race.Classes.Find(x => x.Id.Equals(data.GetString("teamClassCode"))) },
IsTeam = true,
IsSuper = false,
IsCompany = data.GetInt32("companyClass").Equals(1)
};
if (p.IsCompany)
{
p.Classes.Add(race.Classes.Find(x => x.Id.Equals(data.GetString("teamClassCode").Substring(0, 2) + "BED")));
p.Classes.Add(race.Classes.Find(x => x.Id.Equals("BED")));
}
if (p.Name.StartsWith("SVV"))
{
String cn = "SVV";
if (!race.Classes.Exists(x => x.Id.Equals(cn)))
race.Classes.Add(new ParticipantClass() { Id = cn, Official = false, Name = cn, Sequence = race.Classes.Count + 1 });
p.Classes.Add(race.Classes.Find(x => x.Id.Equals(cn)));
}
// Add medlemmer
while (moreData && data.GetInt32("startNumber").Equals(p.Startnumber))
{
p.TeamMembers.Add(data.GetString("firstName") + " " + data.GetString("surname"));
if (!String.IsNullOrEmpty(data.GetString("phoneNumber")))
p.Telephone.Add(data.GetString("phoneNumber").Replace(" ", ""));
moreData = data.Read();
}
p.Telephone = p.Telephone.Distinct().ToList<String>();
race.AddParticipant(p);
} while (moreData);
data.Close();
conn.Close();
// Add testers
foreach (int testId in Testers)
{
var parTest = new Participant()
{
Startnumber = testId,
EmitID = testId,
Name = "Test " + testId.ToString(),
Telephone = new List<String>() { "95116354", "95246298", "", "41530965", "48021455" },
Classes = new List<ParticipantClass>() { race.Classes.Find(x => x.Id.Equals("TEST")) },
IsTeam = true,
TeamMembers = new List<String>() {"Rune Kjørlaug", "Petter Stenstavold", "Erlend Klakegg Bergheim", "Even Østvold"},
IsSuper = false,
IsCompany = false
};
race.AddParticipant(parTest);
}
race.Testers = Testers;
foreach (Participant p in race.Participants)
race.AddPass(new EmitData()
{
BoxId = 1,
Id = p.EmitID,
Time = new DateTime(2016, 4, 16, 13, 14, 0)
});
}
private static void PrintParticiant(Participant data)
{
//Create a pdf document.<br>
PdfDocument doc = new PdfDocument();
//margin
PdfUnitConvertor unitCvtr = new PdfUnitConvertor();
PdfMargins margin = new PdfMargins();
margin.Top = unitCvtr.ConvertUnits(1f, PdfGraphicsUnit.Centimeter, PdfGraphicsUnit.Point);
margin.Bottom = margin.Top;
margin.Left = unitCvtr.ConvertUnits(1f, PdfGraphicsUnit.Centimeter, PdfGraphicsUnit.Point);
margin.Right = margin.Left;
// Create new page
PdfPageBase page = doc.Pages.Add(PdfPageSize.A4, margin);
float y = 10;
//title
PdfBrush brush1 = PdfBrushes.Black;
PdfFont font32b = new PdfFont(PdfFontFamily.Helvetica, 40f, PdfFontStyle.Bold); ;
PdfFont font32 = new PdfFont(PdfFontFamily.TimesRoman, 32f, PdfFontStyle.Regular);
PdfFont font20b = new PdfFont(PdfFontFamily.TimesRoman, 20f, PdfFontStyle.Bold);
PdfFont font20 = new PdfFont(PdfFontFamily.TimesRoman, 18f, PdfFontStyle.Regular);
PdfStringFormat format1 = new PdfStringFormat(PdfTextAlignment.Left);
//Draw the image
PdfImage image = PdfImage.FromFile(@"price.jpg");
float width = image.Width;
float height = image.Height;
float x = page.Canvas.ClientSize.Width - width;
page.Canvas.DrawImage(image, x, y, width, height);
page.Canvas.DrawString(data.Splits(data.Classes[0].Id).Last().Position.ToString(), font32b, brush1, x + 60, y + 50, format1);
y = image.Height;
page.Canvas.DrawString(data.Name, font32b, brush1, 0, y, format1);
y = y + font32b.MeasureString(data.Name, format1).Height + 15;
foreach (ParticipantClass c in data.Classes)
{
StringBuilder sb = new StringBuilder(c.Name);
sb.Append(" - ");
var res = data.Leg(c.Id, 248);
if (res != null)
sb.Append(data.Leg(c.Id, 248).Position);
sb.Append(".plass");
page.Canvas.DrawString(sb.ToString(), font20, brush1, 0, y, format1);
y += font20.MeasureString(sb.ToString(), format1).Height + 15;
}
List<String[]> splits = data.Splits(data.Classes[0].Id).Select(p => new String[] {p.Leg, p.IsSuper? "":p.Name, p.Time }).ToList<String[]>();
if (splits.Count() > 0)
{
splits.Add(new String[] { "Totaltid", "", data.TotalTime });
y = y + 30;
page.Canvas.DrawString("Etappetider", font20b, brush1, 0, y, format1);
y += font20b.MeasureString("Etappetider", format1).Height + 5;
PdfTable table = new PdfTable();
table.Style.BorderPen = new PdfPen(Color.Transparent);
table.Style.DefaultStyle.TextBrush = brush1;
table.Style.DefaultStyle.Font = font20;
table.Style.DefaultStyle.BorderPen = new PdfPen(Color.Transparent);
table.Style.CellPadding = 2;
table.Style.HeaderSource = PdfHeaderSource.Rows;
table.Style.HeaderRowCount = 0;
table.Style.ShowHeader = false;
table.Style.AlternateStyle = new PdfCellStyle();
table.Style.AlternateStyle.TextBrush = brush1;
table.Style.AlternateStyle.Font = font20;
table.Style.AlternateStyle.BackgroundBrush = PdfBrushes.LightGray;
table.Style.AlternateStyle.BorderPen = new PdfPen(Color.Transparent);
table.DataSource = splits.ToArray<String[]>();
PdfLayoutResult result = table.Draw(page, new PointF(0, y));
y = y + result.Bounds.Height + 5;
}
StringBuilder s = new StringBuilder("Start # ");
s.Append(data.Startnumber.ToString());
s.Append(" Emit # ");
s.Append(data.EmitID.ToString());
y += 50;
page.Canvas.DrawString(s.ToString(), font20, brush1, page.Canvas.ClientSize.Width / 2, y, format1);
y = page.Canvas.ClientSize.Height - 60;
image = PdfImage.FromFile(@"kjeringi2016_logo-2.png");
width = image.Width;
height = image.Height;
x = page.Canvas.ClientSize.Width - width;
page.Canvas.DrawImage(image, 0, y, width, height);
image = PdfImage.FromFile(@"difi-logo.png");
width = image.Width;
height = image.Height;
x = page.Canvas.ClientSize.Width - width;
page.Canvas.DrawImage(image, x, y, width, height);
doc.SaveToFile(@"c:\temp\" + data.EmitID + ".pdf");
doc.PrintDocument.Print();
}
public void BuildRace(Race race)
{
race.Name = _year;
MySqlConnection conn = new MySqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[_conn].ConnectionString);
conn.Open();
// Adding supers
var cmd = new MySqlCommand(@"select startNumber, cardid, firstname, surname, personClassCode, ifnull(companyClass, 0) as companyClass, phoneNumber from " + _year + "_person where superwife = 1 and deleted = 0 and startnumber is not null and cardid is not null", conn);
var data = cmd.ExecuteReader();
while (data.Read())
{
var p = new Participant()
{
Startnumber = data.GetInt32("startNumber"),
EmitID = int.Parse(data.GetString("cardid")),
Name = data.GetString("firstname") + " " + data.GetString("surname"),
Telephone = new List<String>() { data.GetString("phoneNumber") },
Classes = new List<ParticipantClass>() { race.Classes.Find(x => x.Id.Equals(data.GetString("personClassCode"))) },
IsTeam = false,
IsSuper = true,
IsCompany = data.GetInt32("companyClass").Equals(1)
};
if (p.IsCompany)
{
p.Classes.Add(race.Classes.Find(x => x.Id.Equals(data.GetString("personClassCode").Substring(0, 2) + "BED")));
p.Classes.Add(race.Classes.Find(x => x.Id.Equals("BED")));
}
race.AddParticipant(p);
}
data.Close();
// Adding teams
cmd = new MySqlCommand(@"SELECT t.startNumber, t.cardid, t.name, t.teamClassCode, t.companyClass, p.firstname, p.surname, p.phoneNumber, p.sprintNumber FROM " + _year + "_team t inner join " + _year + "_person p on t.id = p.teamid where t.deleted = 0 and t.startNumber is not null and t.cardid is not null order by t.startNumber, p.sprintNumber", conn);
data = cmd.ExecuteReader();
data.Read();
bool moreData = true;
do
{
var p = new Participant()
{
Startnumber = data.GetInt32("startNumber"),
EmitID = int.Parse(data.GetString("cardid")),
Name = data.GetString("name"),
Telephone = new List<String>() { data.GetString("phoneNumber").Replace(" ", "") },
Classes = new List<ParticipantClass> { race.Classes.Find(x => x.Id.Equals(data.GetString("teamClassCode"))) },
IsTeam = true,
IsSuper = false,
IsCompany = data.GetInt32("companyClass").Equals(1)
};
if (p.IsCompany)
{
p.Classes.Add(race.Classes.Find(x => x.Id.Equals(data.GetString("teamClassCode").Substring(0, 2) + "BED")));
p.Classes.Add(race.Classes.Find(x => x.Id.Equals("BED")));
}
// Add medlemmer
while (moreData && data.GetInt32("startNumber").Equals(p.Startnumber))
{
p.TeamMembers.Add(data.GetString("firstName") + " " + data.GetString("surname"));
if (!String.IsNullOrEmpty(data.GetString("phoneNumber")))
p.Telephone.Add(data.GetString("phoneNumber").Replace(" ", ""));
moreData = data.Read();
}
p.Telephone = p.Telephone.Distinct().ToList<String>();
race.AddParticipant(p);
} while (moreData);
data.Close();
conn.Close();
foreach (Participant p in race.Participants)
race.AddPass(new EmitData()
{
BoxId = 1,
Id = p.EmitID,
Time = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 13, 14, 0)
});
}
public void BuildRace(Race race)
{
race.Name = "KjeringiOpen 2014";
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["kjeringi"].ConnectionString);
conn.Open();
// Read klasser
SqlCommand cmd = new SqlCommand(@"SELECT id, name, official, sequence FROM kop_station", conn);
SqlDataReader data = cmd.ExecuteReader();
race.TimeStations.Add(new TimeStation()
{
Id = -1,
Name = "Start",
Official = true,
Sequence = 1
});
while (data.Read())
{
race.TimeStations.Add(new TimeStation()
{
Id = data.GetInt32(data.GetOrdinal("id")),
Name = data.GetString(data.GetOrdinal("name")),
Official = data.GetInt32(data.GetOrdinal("official")).Equals(1),
Sequence = data.GetInt32(data.GetOrdinal("sequence"))
});
}
data.Close();
// Read klasser
cmd = new SqlCommand(@"SELECT code, name FROM kop_personClass union SELECT code, name FROM kop_teamClass", conn);
data = cmd.ExecuteReader();
while (data.Read())
{
race.Classes.Add(new ParticipantClass() {
Id = data.GetString(data.GetOrdinal("code")),
Name = data.GetString(data.GetOrdinal("name")),
Sequence = 1
});
}
data.Close();
// Add static classes
var companyClass = new ParticipantClass { Id = "BED", Name = "Bedriftsklasse", Sequence = 100 };
var testClass = new ParticipantClass { Id = "TEST", Name = "Testklasse", Sequence = 101 };
race.Classes.Add(companyClass);
race.Classes.Add(testClass);
// Adding supers
cmd = new SqlCommand(@"select startNumber, chipNumber, firstname, surname, personClassCode, phoneNumber from kop_person where superwife = 1 and deleted = 0 and startnumber is not null and chipnumber is not null", conn);
data = cmd.ExecuteReader();
while (data.Read())
{
race.AddParticipant(new Participant()
{
Startnumber = data.GetInt32(data.GetOrdinal("startNumber")),
EmitID = int.Parse(data.GetString(data.GetOrdinal("chipNumber"))),
Name = data.GetString(data.GetOrdinal("firstname")) + " " + data.GetString(data.GetOrdinal("surname")),
Telephone = new List<String>() {data.GetString(data.GetOrdinal("phoneNumber"))},
Classes = new List<ParticipantClass>() {race.Classes.Find(x => x.Id.Equals(data.GetString(data.GetOrdinal("personClassCode"))))},
IsTeam = false,
IsSuper = true,
IsCompany = false
});
}
data.Close();
cmd = new SqlCommand(@"SELECT t.startNumber, t.chipNumber, t.name, t.teamClassCode, t.companyClass, p.firstname, p.surname, p.phoneNumber, p.sprintNumber FROM kop_team t inner join kop_person p on t.id = p.teamid where t.deleted = 0 and t.startNumber is not null and t.chipNumber is not null order by t.startNumber, p.sprintNumber", conn);
data = cmd.ExecuteReader();
data.Read();
bool moreData = true;
do
{
var p = new Participant()
{
Startnumber = data.GetInt32(data.GetOrdinal("startNumber")),
EmitID = int.Parse(data.GetString(data.GetOrdinal("chipNumber"))),
Name = data.GetString(data.GetOrdinal("name")),
Telephone = new List<String>() {data.GetString(data.GetOrdinal("phoneNumber"))},
Classes = new List<ParticipantClass> {race.Classes.Find(x => x.Id.Equals(data.GetString(data.GetOrdinal("teamClassCode"))))},
IsTeam = true,
IsSuper = false,
IsCompany = data.GetInt32(data.GetOrdinal("companyClass")).Equals(1)
};
if (p.IsCompany)
p.Classes.Add(companyClass);
// Add medlemmer
while (moreData && data.GetInt32(data.GetOrdinal("startNumber")).Equals(p.Startnumber))
{
p.TeamMembers.Add(data.GetString(data.GetOrdinal("firstName")) + " " + data.GetString(data.GetOrdinal("surname")));
if (!String.IsNullOrEmpty(data.GetString(data.GetOrdinal("phoneNumber"))))
p.Telephone.Add(data.GetString(data.GetOrdinal("phoneNumber")));
moreData = data.Read();
}
race.AddParticipant(p);
} while (moreData);
data.Close();
conn.Close();
foreach (Participant p in race.Participants)
race.AddPass(new EmitData()
{
BoxId = -1,
Id = p.EmitID,
Time = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 13, 14, 0)
});
}
