private void GetAndBindMonthCalendar()
{
try
{
monthCalendar1.Dates.Clear();
dtNgayNghi = oChamCong.GetNgayNghiByNV(oThongTinCNVC.txt_MaNV.Text);
if (dtNgayNghi.Rows.Count>0)
{
foreach (DataRow r in dtNgayNghi.Rows)
{
DateTime tu_ngay = Convert.ToDateTime(r["tu_ngay"]);
int n = (Convert.ToDateTime(r["den_ngay"]).Date.Subtract(tu_ngay)).Days;
Pabo.Calendar.DateItem[] di = new Pabo.Calendar.DateItem[n + 1];
for (int i = 0; i < n + 1; i++)
{
di[i] = new Pabo.Calendar.DateItem();
di[i].Date = tu_ngay.AddDays(i);
di[i].BackColor1 = Color.IndianRed;
di[i].BoldedDate = true;
}
monthCalendar1.AddDateInfo(di);
}
if (dtNgayNghi.Rows.Count > 0)
{
var dt = (from row in dtNgayNghi.AsEnumerable()
select row.Field<DateTime>("tu_ngay")).Max();
monthCalendar1.SelectDate(Convert.ToDateTime(dt));
}
UIControls(true);
}
else
{
UIControls(true);
MessageBox.Show("Không tìm thấy thông tin ngày nghỉ của nhân viên " + oThongTinCNVC.txt_HoTen.Text, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception)
{
}
}
private void сделатьВыходнымДнемToolStripMenuItem1_Click(object sender, EventArgs e)
{
var btn_that_clicked = (ToolStripMenuItem)sender;
var date_that_clicked = DateTime.Parse(btn_that_clicked.GetCurrentParent().Tag.ToString());
int year = (int)yearBox.Value;
foreach (Pabo.Calendar.DateItem date in calendar.Dates)
{
if (date.Date == date_that_clicked)
{
if (date.BackColor1 == weekendBackColor)
{
return;
}
}
}
using (var conn = DBUtils.GetDBConnection())
{
conn.Open();
SqlCommand selectCalendarId = new SqlCommand("SELECT CalendarId FROM WorkingCalendar " +
$"WHERE Year = {year} AND IsFiveDaysWeek = '{fiveDaysWeekBtn.Checked}';", conn);
var calendar_id = selectCalendarId.ExecuteScalar().ToString();
SqlCommand selectMonthId = new SqlCommand("SELECT MonthId FROM WorkingMonth " +
$"WHERE CalendarId = {calendar_id} AND MonthNumber = {calendar.ActiveMonth.Month};", conn);
var month_id = selectMonthId.ExecuteScalar().ToString();
SqlCommand selectDateFromWorkingDay = new SqlCommand("SELECT Id FROM WorkingDay " +
$"WHERE [Date] = '{date_that_clicked.ToLocalTime().ToString()}' AND [MonthId] = {month_id};", conn);
// Если такая дата уже была в базе данных помечена как нерабочая
if (selectDateFromWorkingDay.ExecuteScalar() != null)
{
var date_id = selectDateFromWorkingDay.ExecuteScalar().ToString();
SqlCommand sqlUpdateHolidayDate = new SqlCommand("UPDATE WorkingDay SET " +
$"[IsWeekend] = 'True', " +
$"[IsHoliday] = 'False', " +
$"[IsPreHoliday] = 'False' " +
$"WHERE Id = {date_id};", conn);
sqlUpdateHolidayDate.ExecuteNonQuery();
}
else
{
// Добавить в таблицу новый праздничный день
SqlCommand sqlInsertHolidayDate = new SqlCommand("INSERT INTO WorkingDay([Date], [MonthId], " +
$"[IsWeekend], [IsHoliday], [IsPreHoliday]) VALUES('{date_that_clicked.ToLocalTime().ToString()}', {month_id}, " +
$"'True', 'False', 'False');", conn);
sqlInsertHolidayDate.ExecuteNonQuery();
}
SqlCommand sqlUpdateWorkingMonth = new SqlCommand("UPDATE WorkingMonth SET " +
"WorkingDaysCount = WorkingDaysCount - 1 " +
$"WHERE MonthId = {month_id};", conn);
sqlUpdateWorkingMonth.ExecuteNonQuery();
foreach (Pabo.Calendar.DateItem date_ in calendar.Dates)
{
if (date_.Date == date_that_clicked)
{
calendar.Dates.Remove(date_);
break;
}
}
var date = new Pabo.Calendar.DateItem();
date.Date = date_that_clicked;
date.BackColor1 = weekendBackColor;
calendar.Dates.Add(date);
conn.Close();
}
}
private void сделатьПредпраздничнымДнемToolStripMenuItem_Click(object sender, EventArgs e)
{
/*
* 1. Получаем id конкретного календаря
* 2. Получаем id конкретного месяца
* 3. Проверяем есть ли выбранный день в таблице WorkingDay,
* если есть, то просто изменяем флаги у записи,
* иначе добавляем запись в таблицу с нужными флагами
*/
var btn_that_clicked = (ToolStripMenuItem)sender;
var date_that_clicked = DateTime.Parse(btn_that_clicked.GetCurrentParent().Tag.ToString());
int year = (int)yearBox.Value;
Color color = Color.Empty;
foreach (Pabo.Calendar.DateItem date in calendar.Dates)
{
if (date.Date == date_that_clicked)
{
color = date.BackColor1;
}
}
if (color == preHolidayBackColor)
{
return;
}
using (var conn = DBUtils.GetDBConnection())
{
conn.Open();
SqlCommand selectCalendarId = new SqlCommand("SELECT CalendarId FROM WorkingCalendar " +
$"WHERE Year = {year} AND IsFiveDaysWeek = '{fiveDaysWeekBtn.Checked}';", conn);
var calendar_id = selectCalendarId.ExecuteScalar().ToString();
SqlCommand selectMonthId = new SqlCommand("SELECT MonthId FROM WorkingMonth " +
$"WHERE CalendarId = {calendar_id} AND MonthNumber = {calendar.ActiveMonth.Month};", conn);
var month_id = selectMonthId.ExecuteScalar().ToString();
SqlCommand selectDateFromWorkingDay = new SqlCommand("SELECT Id FROM WorkingDay " +
$"WHERE [Date] = '{date_that_clicked.ToLocalTime().ToString()}' AND [MonthId] = {month_id};", conn);
// Если такая дата уже была в базе данных помечена как нерабочая
if (selectDateFromWorkingDay.ExecuteScalar() != null)
{
var date_id = selectDateFromWorkingDay.ExecuteScalar().ToString();
SqlCommand sqlUpdatePreHolidayDate = new SqlCommand("UPDATE WorkingDay SET " +
$"[IsWeekend] = 'False', " +
$"[IsHoliday] = 'False', " +
$"[IsPreHoliday] = 'True' " +
$"WHERE Id = {date_id};", conn);
sqlUpdatePreHolidayDate.ExecuteNonQuery();
}
else
{
// Добавить в таблицу новый предпраздничный день
SqlCommand sqlInsertPreHolidayDate = new SqlCommand("INSERT INTO WorkingDay([Date], [MonthId], " +
$"[IsWeekend], [IsHoliday], [IsPreHoliday]) VALUES('{date_that_clicked.ToLocalTime().ToString()}', {month_id}, " +
$"'False', 'False', 'True');", conn);
sqlInsertPreHolidayDate.ExecuteNonQuery();
}
// Если мы меняем выходной день на предпраздничный
if (color == weekendBackColor || color == holidayBackColor)
{
SqlCommand sqlUpdateWorkingMonth = new SqlCommand("UPDATE WorkingMonth SET " +
"WorkingDaysCount = WorkingDaysCount + 1 " +
$"WHERE MonthId = {month_id};", conn);
sqlUpdateWorkingMonth.ExecuteNonQuery();
}
var date = new Pabo.Calendar.DateItem();
date.Date = date_that_clicked;
date.BackColor1 = preHolidayBackColor;
calendar.Dates.Add(date);
conn.Close();
}
}
