public DataTable load_worker_tocmb_box()
{
Class_sql sql = new Class_sql();
var comand = @"select tabel.id_tn, ttime,concat(Last_name,' ',First_name,' ',Second_name) FIO,position from tabel join worker on tabel.id_tn=worker.id_tn join Position on worker.id_Position=Position.id_position where (select id_Subunit from subunit where subunit='" +
lUp_subunit.Text + "') =id_Subunit and" + " tdate between '" + dEdit_start.DateTime.ToString("yyyy-MM-01") +
"' and '" + dEdit_start.DateTime.ToString("yyyy-MM-" + DateTime.DaysInMonth(dEdit_start.DateTime.Year, dEdit_start.DateTime.Month)) + "'";
return(sql.sql_dt(comand, "table"));
}
public void load_togridCalendar()
{
var command = string.Format(@"select distinct id_models,md.fName_model,md.sName_model,md.id_creator,(worker.Last_name+' '+worker.First_name+' '+worker.Second_name) fio,worker.photo, position.position,md.start_date,
(select distinct status from calendar cal where date='{1}-{0}-01' and cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models) d1,
(select distinct status from calendar cal where date='{1}-{0}-02' and cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models) d2,
(select distinct status from calendar cal where date='{1}-{0}-03' and cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models) d3,
(select distinct status from calendar cal where date='{1}-{0}-04' and cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models) d4,
(select distinct status from calendar cal where date='{1}-{0}-05' and cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models) d5,
(select distinct status from calendar cal where date='{1}-{0}-06' and cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models) d6,
(select distinct status from calendar cal where date='{1}-{0}-07' and cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models) d7,
(select distinct status from calendar cal where date='{1}-{0}-08' and cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models) d8,
(select distinct status from calendar cal where date='{1}-{0}-09' and cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models) d9,
(select distinct status from calendar cal where date='{1}-{0}-10' and cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models) d10,
(select distinct status from calendar cal where date='{1}-{0}-11' and cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models) d11,
(select distinct status from calendar cal where date='{1}-{0}-12' and cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models) d12,
(select distinct status from calendar cal where date='{1}-{0}-13' and cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models) d13,
(select distinct status from calendar cal where date='{1}-{0}-14' and cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models) d14,
(select distinct status from calendar cal where date='{1}-{0}-15' and cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models) d15,
(select distinct status from calendar cal where date='{1}-{0}-16' and cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models) d16,
(select distinct status from calendar cal where date='{1}-{0}-17' and cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models) d17,
(select distinct status from calendar cal where date='{1}-{0}-18' and cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models) d18,
(select distinct status from calendar cal where date='{1}-{0}-19' and cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models) d19,
(select distinct status from calendar cal where date='{1}-{0}-20' and cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models) d20,
(select distinct status from calendar cal where date='{1}-{0}-21' and cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models) d21,
(select distinct status from calendar cal where date='{1}-{0}-22' and cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models) d22,
(select distinct status from calendar cal where date='{1}-{0}-23' and cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models) d23,
(select distinct status from calendar cal where date='{1}-{0}-24' and cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models) d24,
(select distinct status from calendar cal where date='{1}-{0}-25' and cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models) d25,
(select distinct status from calendar cal where date='{1}-{0}-26' and cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models) d26,
(select distinct status from calendar cal where date='{1}-{0}-27' and cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models) d27,
(select distinct status from calendar cal where date='{1}-{0}-28' and cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models) d28,
(select distinct status from calendar cal where date='{1}-{0}-29' and cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models) d29,
(select distinct status from calendar cal where date='{1}-{0}-30' and cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models) d30,
(select distinct status from calendar cal where date=(select max(date) from calendar where cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models) and cal.id_tn=calendar.id_tn and cal.id_models=calendar.id_models and status!='') status_now
from calendar
left join models md on calendar.id_models=md.id
left join worker on md.id_creator=worker.id_tn
left join Position on worker.id_Position=Position.id_position where calendar.id_tn={2}", month, year, formLogin.id_tn);
dt_gridCalendar = class_Sql.sql_dt(command, "1");
dt_gridCalendar.Columns.Add("image", typeof(Image));
ImageLoadToDT(dt_gridCalendar);
grid_Calendar.DataSource = dt_gridCalendar;
}
public void grid_control_total()
{
if (string.IsNullOrEmpty(select_subunit))
{
return;
}
else
{
dt_total_rep = sql.sql_dt(comandSelectBOSS(), "t1");
gridControl_total.DataSource = dt_total_rep;
}
}
public void timeToLabel()
{
DateTime selectDate = date_AddRecord.DateTime.Date;
string query = @"SELECT [time_span] FROM [total]
WHERE [id_tn]='" + formLogin.id_tn + "'"
+ "AND [date]='" + _Date.convert(date_AddRecord.DateTime) + "'";
DataTable dtHours = _Sql.sql_dt(query, "t1");
if (dtHours.Rows.Count != 0)
{
TimeSpan ts1 = new TimeSpan(0, 0, 0);
for (int i = 0; i < dtHours.Rows.Count; i++)
{
ts1 += TimeSpan.Parse(dtHours.Rows[i]["time_span"].ToString());
}
lblTime.Text = ts1.ToString().Substring(0, 5);
}
else
{
lblTime.Text = "00:00";
}
}
public void load_dtWorker()
{
string comand = @"Select worker.*,concat(Last_name,' ',First_name,' ',Second_name)fio,subunit,position from worker join subunit on subunit.id_subunit = worker.id_Subunit join Position on Position.id_position = worker.id_Position";
dtWorker = _sql.sql_dt(comand, "t1");
}
public void load_dtWorker()
{
dtWorker = _sql.sql_dt(@"select worker.*, concat(Last_name,' ',First_name,' ',Second_name)fio,subunit, position
from worker join subunit on worker.id_Subunit=subunit.id_subunit join position on position.id_position=worker.id_Position where id_tn=" +
formLogin.id_tn, "t1");
}
private void GenerateForm()
{
Size = new Size(360, 130);
labelList.Add(startLabel);
textList.Add(startTextEdit);
LabelPositionX = startLabel.Location.X;
TextEditPositionX = startTextEdit.Location.X;
TitleLabel.Text = "Табель сотрудников " + (subunit == "Все" ? "" : subunit + " ") + "за " +
searchdate.ToString("MMMM ") + searchdate.Year;
idList = sql.sql_dt(
@"select distinct id_tn from total where" + (subunit == "Все" ? "" :" (select id_Subunit from subunit where subunit='" + subunit + "')=id_subunit_worker and") + " date between '" + searchdate.ToString("yyyy-MM-01") + "' and '" + searchdate.ToString("yyyy-MM-" + DateTime.DaysInMonth(searchdate.Year, searchdate.Month)) + "' order by id_tn", "t1").Select().Select(input => Convert.ToInt32(input[0])).ToList();
int addHeight = 0;
foreach (var tabel in idList)
{
if (addHeight == 0 && LabelPositionX == startLabel.Location.X)
{
addHeight += 30;
continue;
}
LabelControl label = new LabelControl();
label.Name = "label" + addHeight;
label.AutoSizeMode = LabelAutoSizeMode.None;
label.Width = startLabel.Width;
label.Height = startLabel.Height;
label.Location = new Point(LabelPositionX, startLabel.Location.Y + addHeight);
TextEdit textEdit = new TextEdit();
textEdit.Name = "text" + addHeight;
textEdit.Width = startTextEdit.Width;
textEdit.Height = startLabel.Height;
textEdit.Properties.Mask.EditMask = @"\d{0,5}:[0-5]\d";
textEdit.Properties.Mask.MaskType = MaskType.RegEx;
textEdit.Location = new Point(TextEditPositionX, startTextEdit.Location.Y + addHeight);
this.Controls.Add(label);
this.Controls.Add(textEdit);
labelList.Add(label);
textList.Add(textEdit);
addHeight += 33;
if (labelList.Count % 20 == 0 && idList.Count != labelList.Count)
{
addHeight = 0;
LabelPositionX += 360;
TextEditPositionX += 360;
MaximumSize = new Size(Size.Width + 360, Size.Height);
Size = new Size(Size.Width + 360, Size.Height);
}
}
SimpleButton button = new SimpleButton();
button.Name = "button1";
button.Width = 115;
button.Height = 30;
button.Text = "Подтвердить";
button.Click += button_Click;
int buttonLocationX = this.Size.Width / 2 - 57;
int buttonLocationY = (labelList.Count >= 20) ? labelList[19].Location.Y + 30:startLabel.Location.Y + addHeight;
MaximumSize = new Size(Size.Width, buttonLocationY + 70);
Size = new Size(Size.Width, buttonLocationY + 70);
button.Location = new Point(buttonLocationX, buttonLocationY);
this.Controls.Add(button);
FillLabels();
FillEdits();
}