private void AppointmentSelectTableLoad(string custName)
{
returnedSelectTable = FormModel.DgvBuild("SELECT appointmentID ID, " + // 0
"c.customerName Customer, title Title, location Location, " + // 123
"contact Contact, type Type, url URL, " + // 456
"date_format(start, '%Y-%m-%d') Day, " + // 7
"date_format(start, '%H %i') \"Start Time\", " + // 8
"date_format(end, '%H %i') \"End Time\", " + // 9
"description Description, a.customerID, date_format(end, '%Y-%m-%d') \"End Time\", " + // 10 11 12
"ad.phone FROM appointment a " + // 13
"INNER JOIN customer c ON a.customerID=c.customerID INNER JOIN " +
"address ad ON c.addressID=ad.addressID where " +
$" c.customerName = \"{custName}\"",
AppointmentsDGV);
AppointmentsDGV.Columns[0].Visible = false;
AppointmentsDGV.Columns[12].Visible = false;
AppointmentsDGV.Columns[11].Visible = false;
AppointmentsDGV.Columns[2].Visible = false;
AppointmentsDGV.Columns[3].Visible = false;
AppointmentsDGV.Columns[10].Visible = false;
AppointmentsDGV.Columns[4].Visible = false;
AppointmentsDGV.Columns[6].Visible = false;
if (AppointmentsDGV.Rows.Count == 0)
{
pictureBox1.Visible = true;
AppointmentsDGV.Visible = false;
}
returnedIDTestTableLoad();
}
//sends select statements and any arguements to populate DGV
private void AppointmentSelectTableLoad()
{
returnedSelectTable = FormModel.DgvBuild("SELECT appointmentID ID, " + // 0
"c.customerName Customer, title Title, location Location, " + // 123
"contact Contact, type Type, url URL, " + // 456
"date_format(start, '%Y-%m-%d %H %i') \"Start Time\", " + // 7
"date_format(end, '%Y-%m-%d %H %i') \"End Time\", " + // 8
"description Description, a.customerID, " + // 9, 10
"ad.phone FROM appointment a " + // 11
"INNER JOIN customer c ON a.customerID=c.customerID INNER JOIN " +
"address ad ON c.addressID=ad.addressID",
AppointmentsDGV);
foreach (DataRow row in returnedSelectTable.Rows)
{
DateTime dateTime = new DateTime {
};
dateTime = DateTime.ParseExact(row.ItemArray[7].ToString(), "yyyy-MM-dd HH mm",
System.Globalization.CultureInfo.InstalledUICulture).ToLocalTime();
row.ItemArray[7] = dateTime.ToString();
}
foreach (DataRow row in returnedSelectTable.Rows)
{
DateTime dateTime = new DateTime {
};
dateTime = DateTime.ParseExact(row.ItemArray[8].ToString(), "yyyy-MM-dd HH mm",
System.Globalization.CultureInfo.InstalledUICulture).ToLocalTime();
row.ItemArray[8] = dateTime.ToString();
}
foreach (DataGridViewRow row in AppointmentsDGV.Rows)
{
if (row.Index >= 0)
{
DateTime dateTime = new DateTime {
};
dateTime = DateTime.ParseExact(row.Cells[7].Value.ToString(), "yyyy-MM-dd HH mm",
System.Globalization.CultureInfo.InstalledUICulture).ToLocalTime();
row.Cells[7].Value = dateTime.ToString();
}
}
foreach (DataGridViewRow row in AppointmentsDGV.Rows)
{
if (row.Index >= 0)
{
DateTime dateTime = new DateTime {
};
dateTime = DateTime.ParseExact(row.Cells[8].Value.ToString(), "yyyy-MM-dd HH mm",
System.Globalization.CultureInfo.InstalledUICulture).ToLocalTime();
row.Cells[8].Value = dateTime.ToString();
}
}
AppointmentsDGV.Columns[0].Visible = false;
AppointmentsDGV.Columns[2].Visible = false;
AppointmentsDGV.Columns[3].Visible = false;
AppointmentsDGV.Columns[4].Visible = false;
AppointmentsDGV.Columns[6].Visible = false;
AppointmentsDGV.Columns[9].Visible = false;
AppointmentsDGV.Columns[10].Visible = false;
AppointmentsDGV.Columns[11].Visible = false;
}
//sends select statements and any arguements to populate DGV
private void MainscreenSelectTableLoad()
{
returnedSelectTable = FormModel.DgvBuild("SELECT date_format(start, '%Y-%m-%d') Day," +
" date_format(start, '%H %i') Time, appointmentID, phone Phone," +
" customerName Customer FROM appointment a inner join customer c on" +
" a.customerID=c.customerID inner join user u on a.userID=u.userID" +
" inner join address ad on c.addressID=ad.addressID",
CalenderDGV);
}
private void MainscreenSelectTableLoad(string custName)
{
returnedSelectTable = FormModel.DgvBuild("SELECT date_format(start, '%Y-%m-%d') Day," +
" date_format(start, '%H %i') Time, appointmentID, userName User," +
" customerName Customer FROM appointment a inner join customer c on" +
" a.customerID=c.customerID inner join user u on a.userID=u.userID where " +
$" c.customerName = \"{custName}\"",
CalenderDGV);
if (CalenderDGV.Rows.Count == 0)
{
pictureBox1.Visible = true;
CalenderDGV.Visible = false;
}
}
//sends select statements to populate DGV
private void CustomerSelectTableLoad()
{
returnedSelectTable = FormModel.DgvBuild("SELECT customerID ID, " + //0
"customerName Name, phone Phone, active Active, " + //123
"Address Address, city City, postalCode Zip, " + //456
"country Country, c.customerID, c.addressID, ci.cityID, " + //7 8 9 10
"co.countryID FROM customer c inner join address a on " + //11
"c.addressID=a.addressID inner join city ci on a.cityID=ci.cityID " +
"inner join country co on ci.countryID=co.countryID",
customerDGV);
customerDGV.Columns[8].Visible = false;
customerDGV.Columns[9].Visible = false;
customerDGV.Columns[10].Visible = false;
customerDGV.Columns[11].Visible = false;
}
private void MainscreenSelectTableLoad(DateTime startDate, DateTime endDate)
{
FormModel.DgvBuild("SELECT date_format(start, '%Y-%m-%d') Day," +
" date_format(start, '%H %i') Time, appointmentID, userName User," +
" customerName Customer FROM appointment a inner join customer c on" +
" a.customerID=c.customerID inner join user u on a.userID=u.userID where " +
$"a.start>=STR_TO_DATE('{startDate}', '%c/%e/%Y') and " +
$"a.end<= STR_TO_DATE('{endDate}', '%c/%e/%Y')",
CalenderDGV);
if (CalenderDGV.Rows.Count == 0)
{
pictureBox1.Visible = true;
CalenderDGV.Visible = false;
}
}