public Stt(string sttId, string sttFromOrTo)
{
SttId = sttId;
Dictionary <string, string> dic;
string sql;
sql = $@"SELECT
prj.prj_id
,prj_nm
FROM prj
JOIN stt ON stt.prj_id = prj.prj_id
WHERE stt_id = {sttId}";
dic = new db3work(sql).ValuesRow();
PrjId = dic["prj_id"];
PrjName = dic["prj_nm"];
dic.Clear();
sql = $@"SELECT
ctg.ctg_id
,ctg_nm
FROM ctg
JOIN stt ON stt.ctg_id = ctg.ctg_id
WHERE stt_id = {sttId}";
dic = new db3work(sql).ValuesRow();
CtgId = dic["ctg_id"];
CtgName = dic["ctg_nm"];
SttName = CtgName + " / " + PrjName;
SttFromOrTo = sttFromOrTo;
dic.Clear();
}
public void Edit(DataGrid dataGrid)
{
DataRowView drv = (DataRowView)dataGrid.SelectedItem;
if (drv == null)
{
return;
}
CtgId = drv.Row.ItemArray[0].ToString();
InputWindow inputWindow = new InputWindow();
inputWindow.Left = Application.Current.MainWindow.Left;
inputWindow.Title = "Изменение имени категории";
string sql = $"SELECT ctg_nm FROM ctg WHERE ctg_id = '{CtgId}'";
CtgName = new db3work(sql).ScalarSql();
inputWindow.TextBoxInput.Text = CtgName;
inputWindow.ShowDialog();
string tbValue = inputWindow.TextBoxInput.Text;
if (inputWindow.IsОК == false)
{
return;
}
CtgName = tbValue;
sql = $"UPDATE ctg SET ctg_nm = '{CtgName}' WHERE ctg_id={CtgId}";
new db3work(sql).RunSql();
inputWindow.Close();
Fill(dataGrid);
}
public Stt(Ctg ctg, Prj prj)
{
InitCtg(ctg);
PrjId = prj.PrjId;
PrjName = prj.PrjName;
string sql = $"SELECT stt_id FROM stt WHERE ctg_id = {CtgId} AND prj_id = {PrjId}";
SttId = new db3work(sql).ScalarSql();
SttName = CtgName + " / " + PrjName;
}
public void Fill(DataGrid dataGrid)
{
string sql = @"SELECT [ctg_id] ,[ctg_nm] FROM [ctg] ORDER BY [ctg_nm]";
DataTable table = new db3work(sql).SelectSql();
dataGrid.DataContext = table.DefaultView;
if (dataGrid.Items.Count != 0)
{
dataGrid.SelectedIndex = 0;
dataGrid.ScrollIntoView(dataGrid.Items[0]);
}
}
public void Del(DataGrid dataGrid)
{
DataRowView drv = (DataRowView)dataGrid.SelectedItem;
if (drv == null)
{
return;
}
string ctgId = drv.Row.ItemArray[0].ToString();
//Проверка используется ли проект в основной таблице
string sql = $@"
SELECT COUNT(stt.stt_id)
FROM [ctg]
LEFT JOIN stt ON stt.ctg_id = ctg.ctg_id
LEFT JOIN csh AS csh_from ON csh_from.stt_id_from = stt.stt_id
LEFT JOIN csh AS csh_to ON csh_to.stt_id_to = stt.stt_id
WHERE [ctg].[ctg_id] = {ctgId}
GROUP BY csh_from.stt_id_from
;";
string rowsCount = new db3work(sql).ScalarSql();
if (rowsCount == "0")
{
//Удаление связки в stt
sql = $@"DELETE FROM stt WHERE ctg_id = {ctgId}";
new db3work(sql).RunSql();
//Удаление категории
sql = $@"DELETE FROM ctg WHERE ctg_id = {ctgId}";
new db3work(sql).RunSql();
//Заполнение DataGrid
Fill(dataGrid);
}
else
{
MessageBox.Show("Внимание, категория используется в основной таблие!\nЕё удалить нельзя!");
}
}
public void Add(DataGrid dataGrid)
{
InputWindow inputWindow = new InputWindow();
inputWindow.Left = Application.Current.MainWindow.Left;
inputWindow.TextBoxInput.Text = "";
inputWindow.Title = "Ввод имени новой категории";
inputWindow.ShowDialog();
string tbValue = inputWindow.TextBoxInput.Text;
if (inputWindow.IsОК == false)
{
return;
}
string sql = $"INSERT INTO ctg (ctg_nm) VALUES ('{tbValue}')";
new db3work(sql).RunSql();
CtgName = tbValue;
sql = $"SELECT ctg_id FROM ctg WHERE ctg_nm = '{CtgName}'";
CtgId = new db3work(sql).ScalarSql();
inputWindow.Close();
Fill(dataGrid);
}
public Ctg(DataGrid dataGrid, TextBox textBox)
{
DataRowView drv = (DataRowView)dataGrid.SelectedItem;
if (drv == null)
{
this.Fill(dataGrid);
return;
}
CtgId = drv.Row[0].ToString();
if (textBox.Name == "TextBoxFrom")
{
CtgField = "ctg_id_from";
}
if (textBox.Name == "TextBoxTo")
{
CtgField = "ctg_id_to";
}
string sql = "SELECT ctg_nm FROM ctg WHERE ctg_id = " + CtgId;
CtgName = new db3work(sql).ScalarSql();
textBox.Text = CtgName;
}
