private bool SaveCheck()
{
string SQL =
"SELECT " +
"* " +
"FROM " +
"[Documents].[SubRisk] " +
"WHERE 1 = 1 " +
"AND [Fromdate] <= @EvalDate " +
"AND [Todate] > @EvalDate " +
$"AND [SubRiskIndex] = {updSubRiskIndex.Value} " +
$"AND [SubRiskID] = '{txtSubRiskID.Text}' " +
$"AND [SubRiskType] = '{txtRiskType.Text}' " +
$"AND [SubRiskText] like '{txtSubRisk.Text}' " +
$"AND [SubRiskReportingName] = '{txtSubRisk.Text}'";
var dt = RiskTextForm.SQLQuery(SQL, connectionString, new Dates(this), true);
if (dt == null || dt.Rows.Count == 0)
{
return false;
}
else
{
return true;
}
}
private void dgvSubrisks_MouseDoubleClick(object sender, MouseEventArgs e)
{
int i = dgvSubrisks.CurrentCell.OwningRow.Index;
int MIK = form.mainRiskIK;
int SIK = int.Parse(dgvSubrisks.Rows[i].Cells["SubRiskIK"].Value.ToString());
string SQL;
SQL = $"INSERT INTO " +
$"Documents.MainRisk2SubRisk " +
$"( " +
$"[MainRiskIK], " +
$"[SubRiskIK], " +
$"[FromDate], " +
$"[ToDate] " +
$") " +
$"VALUES " +
$"( " +
$"{MIK}, " +
$"{SIK}, " +
$"@Today, " +
$"@MaxDate " +
$")";
RiskTextForm.SQLQuery(SQL, connectionString, new Dates(form), true);
this.Close();
}
private void removeToolStripMenuItem_Click(object sender, EventArgs e)
{
if (readOnly)
{
return;
}
if (dtp.Value.Date == DateTime.Today)
{
int subRiskIK = 0;
if (dgvSubRisks.CurrentCell == null || dgvSubRisks.CurrentCell.OwningColumn.Index == -1 || dgvSubRisks.CurrentCell.OwningColumn.HeaderText == "None")
{
return;
}
else
{
subRiskIK = (int)dgvSubRisks.CurrentCell.OwningColumn.HeaderCell.Tag;
string SQL =
"UPDATE " +
"[Documents].[MainRisk2SubRisk] " +
"SET " +
"[ToDate] = @Today " +
" WHERE 1 = 1 " +
$"AND [SubRiskIK] = {subRiskIK} " +
$"AND [MainRiskIK] = {mainRiskIK} " +
"AND [ToDate] = @MaxDate";
RiskTextForm.SQLQuery(SQL, connectionString, new Dates(this), false);
MessageBox.Show("Remove");
init();
}
}
}
public void init()
{
if (dgvCell == null)
{
this.Close();
return;
}
subRiskID = dgvCell.OwningColumn.HeaderText;
subRiskIK = (int)dgvCell.OwningColumn.HeaderCell.Tag;
this.Text = "Sub risk - " + subRiskID;
string SQL = $"SELECT " +
$"* " +
$"FROM " +
$"[Documents].[SubRisk] " +
$"WHERE 1=1 " +
$"AND [SubRiskIK] = {subRiskIK} " +
$"AND [FromDate] <= @EvalDate " +
$"AND [ToDate] > @EvalDate";
using (var dt = RiskTextForm.SQLQuery(SQL, connectionString, new Dates(form), true))
{
if (dt == null || dt.Rows.Count == 0)
{
this.Close();
return;
}
subRiskReportingName = dt.Rows[0]["SubRiskReportingName"].ToString();
subRiskType = dt.Rows[0]["SubRiskType"].ToString();
subRiskIndex = (int)dt.Rows[0]["SubRiskIndex"];
subRiskText = dt.Rows[0]["SubRiskText"].ToString();
subRiskTo = (DateTime)dt.Rows[0]["ToDate"];
subRiskFrom = (DateTime)dt.Rows[0]["FromDate"];
}
txtSubRiskID.Text = dgvCell.OwningColumn.HeaderText;
txtReportingName.Text = subRiskReportingName;
txtSubRisk.Text = subRiskText;
txtRiskType.Text = subRiskType;
updSubRiskIndex.Value = subRiskIndex;
lblToDate.Text = subRiskTo.ToShortDateString();
lblFromDate.Text = subRiskFrom.ToShortDateString();
txtSubRisk.DeselectAll();
if (dtp.Value.Date != DateTime.Today)
{
lblOld.Visible = true;
}
else
{
lblOld.Visible = false;
}
}
public CreateDocument(RiskTextForm _form)
{
form = _form;
wordApp.ShowAnimation = false;
wordApp.Visible = true;
wordDoc = wordApp.Documents.Add();
if (form.cboForening.Text != "All")
{
tables(form);
}
texts();
}
private void deleteToolStripMenuItem_Click(object sender, EventArgs e)
{
if (readOnly)
{
return;
}
try
{
string SubRiskID = dgvSubRisks.CurrentCell.OwningColumn.HeaderText;
int SubRiskIK = (int)dgvSubRisks.CurrentCell.OwningColumn.HeaderCell.Tag;
var result = MessageBox.Show($"Do you wish to Delete '{SubRiskID}'", $"Delete {SubRiskID}", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
string SQL = "";
SQL = "UPDATE " +
"[Documents].[SubRisk] " +
"SET " +
"[ToDate] = @Today " +
"WHERE 1 = 1 " +
$"AND [SubRiskIK] = {SubRiskIK} " +
"AND [ToDate] = @MaxDate;" +
"UPDATE " +
"[Documents].[MainRisk2SubRisk] " +
"SET " +
"[ToDate] = @Today " +
"WHERE 1 = 1 " +
$"AND [SubRiskIK] = {SubRiskIK} " +
"AND [ToDate] = @MaxDate";
RiskTextForm.SQLQuery(SQL, connectionString, new Dates(this), false);
MessageBox.Show("Deleted");
}
init();
}
catch (Exception)
{
}
}
public ChooseSubRisk(string _connectionString, MainRiskForm _form)
{
connectionString = _connectionString;
form = _form;
InitializeComponent();
string SQL = "Select " +
"* " +
"FROM " +
"[Documents].[SubRisk] " +
"WHERE 1=1" +
"AND [ToDate] > @EvalDate " +
"AND [FromDate] <= @EvalDate";
using (var dt = RiskTextForm.SQLQuery(SQL, connectionString, new Dates(form), true))
{
foreach (DataRow row in dt.Rows)
{
int rowIndex = dgvSubrisks.Rows.Add();
dgvSubrisks.Rows[rowIndex].Cells["SubRiskID"].Value = row["SubRiskID"].ToString();
dgvSubrisks.Rows[rowIndex].Cells["SubRiskIK"].Value = row["SubRiskIK"].ToString();
dgvSubrisks.Rows[rowIndex].Cells["SubRiskType"].Value = row["SubRiskType"].ToString();
}
}
}
private void saveToolStripMenuItem_Click(object sender, EventArgs e) //Ej klar
{
if (readOnly) return;
if (!SaveCheck())
{
string SQL =
"DELETE " +
"[Documents].[SubRisk] " +
"WHERE 1 = 1 " +
"AND [FromDate] = @Today " +
$"AND [SubRiskIK] = {subRiskIK};" +
"UPDATE " +
"[Documents].[SubRisk] " +
"SET " +
"[ToDate] = @Today " +
"WHERE 1 = 1 " +
$"AND [SubRiskIK] = {subRiskIK} " +
"AND [ToDate] = @MaxDate" +
";" +
"INSERT INTO " +
"[Documents].[SubRisk] " +
"( " +
"[SubRiskID], " +
"[SubRiskText], " +
"[SubRiskIndex], " +
"[SubRiskType], " +
"[SubRiskReportingName], " +
"[FromDate], " +
"[ToDate]" +
") " +
"VALUES " +
"( " +
$"'{txtSubRiskID.Text}', " +
$"'{txtSubRisk.Text}', " +
$"{(int)updSubRiskIndex.Value}, " +
$"'{txtRiskType.Text}', " +
$"'{txtReportingName.Text}', " +
"@Today, " +
"@MaxDate " +
")" +
";" +
$"DELETE " +
$"[Documents].[MainRisk2SubRisk] " +
$"WHERE 1 = 1 " +
$"AND [FromDate] = @Today " +
$"AND [SubRiskIK] = {subRiskIK};" +
"UPDATE " +
"[Documents].[MainRisk2SubRisk] " +
"SET " +
"[ToDate] = @Today " +
"WHERE 1 = 1 " +
"AND [ToDate] = @MaxDate " +
$"AND [SubRiskIK] = {subRiskIK}" +
";" +
"INSERT INTO " +
"[Documents].[MainRisk2SubRisk] " +
"( " +
"[SubRiskIK], " +
"[MainRiskIK], " +
"[FromDate], " +
"[ToDate] " +
") " +
"VALUES " +
"( " +
"(" +
"SELECT DISTINCT " +
"[SubRiskIK] " +
"FROM " +
"[Documents].[SubRisk] " +
"WHERE 1 = 1 " +
$"AND [SubRiskID] = '{txtSubRiskID.Text}' " +
"AND [ToDate] = @MaxDate" +
"), " +
$"{form.mainRiskIK}, " +
"@Today, " +
"@MaxDate " +
")" +
";";
RiskTextForm.SQLQuery(SQL, connectionString, new Dates(this), false);
MessageBox.Show("Saved");
this.Close();
}
}
private void texts()
{
string SQL;
SQL = "Select " +
"* " +
"FROM " +
"[Documents].[MainRisk] " +
"WHERE 1 = 1 " +
"AND [FromDate] <= @EvalDate " +
"AND [ToDate] > @EvalDate " +
"ORDER BY " +
"[MainRiskIndex] ASC";
using (var dtMain = RiskTextForm.SQLQuery(SQL, RiskTextForm.connectionString, new Dates(form), true))
{
foreach (DataRow rMain in dtMain.Rows)
{
wordDoc.Paragraphs.SpaceAfter = 0;
var mainRiskHeader = wordDoc.Paragraphs.Add();
if (mainRiskHeader.Format.LeftIndent == 20)
{
mainRiskHeader.Format.LeftIndent = -20;
}
mainRiskHeader.Range.Underline = word.WdUnderline.wdUnderlineSingle;
mainRiskHeader.Range.Font.Name = "garamond";
mainRiskHeader.Range.Font.Size = 11;
mainRiskHeader.Range.Text = rMain["MainRiskReportingName"].ToString() + Environment.NewLine;
mainRiskHeader = null;
var mainRiskText = wordDoc.Paragraphs.Add();
mainRiskText.Range.Font.Name = "garamond";
mainRiskText.Range.Font.Size = 11;
mainRiskText.Range.Text = rMain["MainRiskText"] + Environment.NewLine;
mainRiskText.Format.LeftIndent = 20;
SQL = "SELECT " +
"* " +
"FROM " +
"[Documents].[SubRisk] as [SR], " +
"[Documents].[MainRisk2SubRisk] as [MR2SR] " +
"WHERE 1=1 " +
"AND [SR].[SubRiskIK] = [MR2SR].[SubriskIK] " +
$"AND [MR2SR].[MainRiskIK] = {(int)rMain["MainRiskIK"]} " +
"AND [SR].[FromDate] <= @EvalDate " +
"AND [SR].[ToDate] > @EvalDate " +
"AND [MR2SR].[FromDate] <= @EvalDate " +
"AND [MR2SR].[ToDate] > @EvalDate " +
"ORDER BY " +
"[SubRiskIndex] ASC";
using (var dtSub = RiskTextForm.SQLQuery(SQL, RiskTextForm.connectionString, new Dates(form), true))
{
foreach (DataRow rSub in dtSub.Rows)
{
var subRiskHeader = wordDoc.Paragraphs.Add();
subRiskHeader.Range.Italic = 1;
subRiskHeader.Range.Font.Name = "garamond";
subRiskHeader.Range.Font.Size = 11;
subRiskHeader.Range.Text = Environment.NewLine + rSub["SubRiskReportingName"].ToString() + Environment.NewLine;
subRiskHeader.Format.LeftIndent = 20;
subRiskHeader = null;
var subRiskText = wordDoc.Paragraphs.Add();
subRiskText.Format.SpaceBeforeAuto = 0;
subRiskText.Format.SpaceBefore = 0;
subRiskText.Format.SpaceAfterAuto = 0;
subRiskText.Format.SpaceAfter = 10;
subRiskText.Range.Font.Name = "garamond";
subRiskText.Range.Font.Size = 11;
subRiskText.Range.Text = rSub["SubRiskText"] + Environment.NewLine;
}
mainRiskText.Format.LeftIndent = 0;
mainRiskText.Range.Text = mainRiskText.Range.Text;
mainRiskText = null;
}
}
}
}
private void tables(RiskTextForm form)
{
int fundCount = form.dgvRisks.Rows.Count + 1;
int riskCount = form.dgvRisks.Columns.Count + 1;
int textHeight;
int textLength;
var Table = wordDoc.Paragraphs.Add();
wordDoc.Tables.Add(Table.Range, fundCount, riskCount);
var wordTable = wordDoc.Tables[1];
wordTable.Range.Font.Size = 10;
wordTable.Range.Font.Name = "garamond";
wordTable.Range.ParagraphFormat.Alignment = word.WdParagraphAlignment.wdAlignParagraphCenter;
wordTable.Range.Cells.VerticalAlignment = word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;
int i = 0;
int maxHeight = 0;
foreach (DataGridViewColumn col in form.dgvRisks.Columns)
{
//wordTable.Cell(1, col.Index + 2).Range.Text = col.HeaderText;
string SQL = $"SELECT" +
$" [MainRiskReportingName] " +
$"FROM " +
$"[Documents].[MainRisk]" +
$"WHERE 1=1 " +
$"AND MainRiskIK = {col.HeaderCell.Tag}" +
$"AND [FromDate] <= @EvalDate " +
$"AND [ToDate] > @EvalDate"
;
using (var dt = RiskTextForm.SQLQuery(SQL, RiskTextForm.connectionString, new Dates(form), true))
{
wordTable.Cell(1, col.Index + 2).Range.Text = dt.Rows[0][0].ToString();
}
wordTable.Cell(1, col.Index + 2).Range.ParagraphFormat.Alignment = word.WdParagraphAlignment.wdAlignParagraphLeft;
wordTable.Cell(1, col.Index + 2).Range.Orientation = word.WdTextOrientation.wdTextOrientationUpward;
wordTable.Cell(1, col.Index + 2).Range.Font.Bold = 1;
textHeight = TextRenderer.MeasureText(wordTable.Cell(1, col.Index + 2).Range.Text, new System.Drawing.Font(wordTable.Range.Font.Name, 8)).Width;
if (textHeight > maxHeight)
{
maxHeight = textHeight;
}
}
int maxWidth = 0;
foreach (DataGridViewRow row in form.dgvRisks.Rows)
{
wordTable.Cell(row.Index + 2, 1).Range.Text = row.HeaderCell.Value.ToString();
wordTable.Cell(row.Index + 2, 1).Range.ParagraphFormat.Alignment = word.WdParagraphAlignment.wdAlignParagraphLeft;
wordTable.Cell(row.Index + 2, 1).Range.Font.Bold = 1;
textLength = TextRenderer.MeasureText(wordTable.Cell(row.Index + 2, 1).Range.Text, new System.Drawing.Font(wordTable.Range.Font.Name, 8)).Width;
if (textLength > maxWidth)
{
maxWidth = textLength;
}
foreach (DataGridViewColumn col in form.dgvRisks.Columns)
{
switch (form.dgvRisks.Rows[row.Index].Cells[col.Index].Value.ToString())
{
case "1":
if ((bool)form.dgvRisks.Rows[row.Index].Cells[col.Index].Tag)
{
wordTable.Cell(row.Index + 2, col.Index + 2).Range.Text = "\u25CF";
//wordTable.Cell(row.Index + 2, col.Index + 2).Range.Bold = 1;
}
else
{
wordTable.Cell(row.Index + 2, col.Index + 2).Range.Text = "\u25CB";
}
break;
default:
break;
}
}
}
wordTable.Range.Cells.Borders.Enable = 1;
wordTable.Rows[1].Range.ParagraphFormat.Alignment = word.WdParagraphAlignment.wdAlignParagraphLeft;
wordTable.Rows[1].Cells.Height = maxHeight;
wordTable.Range.Cells.Width = TextRenderer.MeasureText("xx", new System.Drawing.Font(wordTable.Range.Font.Name, 8)).Width;
wordTable.Columns[1].Cells.Width = maxWidth;
wordTable.Cell(1, 1).Range.Text = "Afdeling\n";
wordTable.Cell(1, 1).Range.Bold = 1;
wordTable.Cell(1, 1).Range.ParagraphFormat.Alignment = word.WdParagraphAlignment.wdAlignParagraphCenter;
wordTable.Cell(1, 1).Range.Cells.VerticalAlignment = word.WdCellVerticalAlignment.wdCellAlignVerticalBottom;
}
public Dates(RiskTextForm form)
{
EvalDate = form.dtp.Value.Date;
FromDate = DateTime.Parse(form.lblFromDate.Text);
ToDate = DateTime.Parse(form.lblToDate.Text);
}
private void init()
{
dgvSubRisks.Rows.Clear();
dgvSubRisks.Columns.Clear();
mainRiskID = dgvCell.OwningColumn.HeaderText;
this.Text = "Main risk - " + mainRiskID;
string SQL =
$"SELECT " +
$"* " +
$"FROM " +
$"[Documents].[MainRisk] " +
$"WHERE 1 = 1 " +
$"AND [MainRiskID] = '{mainRiskID}' " +
$"AND [ToDate] > @EvalDate " +
$"AND [FromDate] <= @EvalDate ";
using (var dt = RiskTextForm.SQLQuery(SQL, connectionString, new Dates(this), true))
{
if (dt == null || dt.Rows.Count == 0)
{
MessageBox.Show("Not Available");
this.Close();
return;
}
mainRiskText = dt.Rows[0]["MainRiskText"].ToString();
mainRiskType = dt.Rows[0]["MainRiskType"].ToString();
mainRiskIK = (int)dt.Rows[0]["MainRiskIK"];
mainRiskIndex = (int)dt.Rows[0]["MainRiskIndex"];
mainRiskReportingName = dt.Rows[0]["MainRiskReportingName"].ToString();
mainRiskTo = (DateTime)dt.Rows[0]["ToDate"];
mainRiskFrom = (DateTime)dt.Rows[0]["FromDate"];
}
txtMainRiskID.Text = mainRiskID;
txtMainRisk.Text = mainRiskText;
updMainRiskIndex.Value = mainRiskIndex;
txtMainRiskType.Text = mainRiskType;
txtReportingName.Text = mainRiskReportingName;
lblFromDate.Text = mainRiskFrom.ToShortDateString();
lblToDate.Text = mainRiskTo.ToShortDateString();
txtMainRisk.DeselectAll();
SQL = "SELECT " +
"[SR].[SubRiskID], " +
"[SR].[SubRiskIK] " +
"FROM " +
"[Documents].[MainRisk] as [MR], " +
"[Documents].[MainRisk2SubRisk] as [MR2SR], " +
"[Documents].[SubRisk] as [SR] " +
$"WHERE 1=1 " +
"AND [SR].[SubRiskIK] = [MR2SR].[SubRiskIK] " +
"AND [MR].[MainRiskIK] = [MR2SR].[MainRiskIK] " +
$"AND [MR].[MainRiskIK] = '{mainRiskIK}' " +
"AND [SR].[FromDate] <= @EvalDate " +
"AND [MR2SR].[FromDate] <= @EvalDate " +
"AND [SR].[ToDate] > @Evaldate " +
"AND [MR2SR].[ToDate] > @Evaldate " +
$"ORDER BY " +
"[SR].[SubRiskIndex] ASC";
using (var dt = RiskTextForm.SQLQuery(SQL, connectionString, new Dates(this), true))
{
DataGridViewCheckBoxColumn dgvCol;
if (dt == null || dt.Rows.Count == 0)
{
dgvCol = new DataGridViewCheckBoxColumn();
dgvCol.HeaderText = "None";
dgvCol.HeaderCell.Tag = 0;
dgvCol.FlatStyle = FlatStyle.Flat;
dgvCol.CellTemplate.Value = true;
dgvSubRisks.Columns.Add(dgvCol);
}
else
{
foreach (DataRow row in dt.Rows)
{
dgvCol = new DataGridViewCheckBoxColumn();
dgvCol.HeaderText = row["SubRiskID"].ToString();
dgvCol.HeaderCell.Tag = (int)row["SubRiskIK"];
dgvCol.FlatStyle = FlatStyle.Flat;
dgvSubRisks.Columns.Add(dgvCol);
}
}
}
dgvSubRisks.Rows.Add();
dgvSubRisks.Rows[0].HeaderCell.Value = dgvCell.OwningColumn.HeaderText;
dgvSubRisks.CurrentCell = null;
for (int j = 0; j < dgvSubRisks.ColumnCount; j++)
{
dgvSubRisks.Rows[0].Cells[j].Value = true;
}
dgvSubRisks.CurrentCell = null;
lblOld.Visible = false;
if (dtp.Value.Date != DateTime.Today)
{
lblOld.Visible = true;
}
}
private void saveToolStripMenuItem_Click(object sender, EventArgs e)
{
if (readOnly)
{
return;
}
string SQL = "";
if (!SaveCheck())
{
SQL =
"SELECT " +
"* " +
"FROM " +
"[Documents].[MainRisk] " +
"WHERE 1 = 1 " +
"AND [ToDate] = @MaxDate " +
$"AND [MainRiskIK] = {mainRiskIK};";
using (var dt = RiskTextForm.SQLQuery(SQL, connectionString, new Dates(this), true))
{
if ((DateTime)dt.Rows[0]["FromDate"] == DateTime.Today.Date)
{
SQL =
$"UPDATE " +
$"[Documents].[MainRisk] " +
$"SET " +
$"[MainRiskID] = '{txtMainRiskID.Text}', " +
$"[MainRiskText] = '{txtMainRisk.Text}', " +
$"[MainRiskIndex] = '{(int)updMainRiskIndex.Value}', " +
$"[MainRiskType] = '{txtMainRiskType.Text}', " +
$"[MainRiskReportingName] = '{txtReportingName.Text}' " +
$"WHERE 1 = 1 " +
$"AND [MainRiskIK] = '{mainRiskIK}' " +
$"AND [ToDate] = @MaxDate;";
RiskTextForm.SQLQuery(SQL, connectionString, new Dates(this), false);
}
else
{
SQL =
$"UPDATE " +
$"[Documents].[MainRisk] " +
$"SET " +
$"[ToDate] = @Today " +
$"WHERE 1 = 1 " +
$"AND [MainRiskIK] = {mainRiskIK} " +
$"AND [ToDate] = @MaxDate " +
$"AND [FromDate] <> @Today;" +
$"INSERT INTO " +
$"[Documents].[MainRisk] " +
$"(" +
$" [MainRiskID]," +
$" [MainRiskText]," +
$" [MainRiskIndex]," +
$" [MainRiskType]," +
$" [MainRiskReportingName]," +
$" [FromDate]," +
$" [ToDate]" +
$") " +
$"VALUES " +
$"( " +
$" '{txtMainRiskID.Text}'," +
$" '{txtMainRisk.Text}'," +
$" {(int)updMainRiskIndex.Value}," +
$" '{txtMainRiskType.Text}'," +
$" '{txtReportingName.Text}'," +
$" @Today," +
$" @MaxDate" +
$") " +
$";";
RiskTextForm.SQLQuery(SQL, connectionString, new Dates(this), false);
int newMainRiskIK;
SQL =
"SELECT " +
"[MainRisk2SubRiskIK] as IK, [SubRiskIK], [FromDate], [ToDate] " +
"FROM " +
"[Documents].[MainRisk2SubRisk] " +
"WHERE 1 = 1 " +
$"AND [MainRiskIK] = {mainRiskIK} " +
"AND [ToDate] = @MaxDate";
using (var dtM2S = RiskTextForm.SQLQuery(SQL, connectionString, new Dates(this), true))
{
SQL =
"SELECT " +
"[MainRiskIK] " +
"FROM " +
"[Documents].[MainRisk] " +
"WHERE 1 = 1 " +
$"AND [MainRiskID] = '{txtMainRiskID.Text}' " +
"AND [ToDate] = @MaxDate";
using (var dtIK = RiskTextForm.SQLQuery(SQL, connectionString, new Dates(this), true))
{
newMainRiskIK = (int)dtIK.Rows[0]["MainRiskIK"];
}
foreach (DataRow row in dtM2S.Rows)
{
if ((DateTime)row["FromDate"] == DateTime.Today.Date)
{
SQL =
$"UPDATE " +
$"[Documents].[MainRisk2SubRiskIK] " +
$"SET " +
$"[MainRiskIK] = {newMainRiskIK}, " +
$"WHERE 1 = 1 " +
$"AND [MainRisk2SubRiskIK] = {(int)row["IK"]} " +
$"AND [ToDate] = @MaxDate;";
}
else
{
SQL =
"UPDATE " +
"[Documents].[MainRisk2SubRisk] " +
"SET " +
"[ToDate] = @Today " +
"WHERE 1 = 1 " +
"AND [ToDate] = @MaxDate " +
$"AND [MainRisk2SubRiskIK] = {(int)row["IK"]}" +
";" +
"INSERT INTO " +
"[Documents].[MainRisk2SubRisk] " +
"( " +
"[SubRiskIK], " +
"[MainRiskIK], " +
"[FromDate], " +
"[ToDate] " +
") " +
"VALUES " +
"( " +
$"{(int)row["SubRiskIK"]}, " +
$"{newMainRiskIK}, " +
"@Today, " +
"@MaxDate " +
")" +
";";
}
RiskTextForm.SQLQuery(SQL, connectionString, new Dates(this), false);
}
}
SQL =
"SELECT " +
"[Fund2MainRiskIK] as IK, [Fund_IK], [Major], [FromDate], [ToDate] " +
"FROM " +
"[Documents].[Fund2MainRisk] " +
"WHERE 1 = 1 " +
$"AND [MainRiskIK] = {mainRiskIK} " +
"AND [ToDate] = @MaxDate";
using (var dtF2S = RiskTextForm.SQLQuery(SQL, connectionString, new Dates(this), true))
{
foreach (DataRow row in dtF2S.Rows)
{
if ((DateTime)row["FromDate"] == DateTime.Today.Date)
{
SQL =
$"UPDATE " +
$"[Documents].[Fund2MainRisk] " +
$"SET " +
$"[MainRiskIK] = {newMainRiskIK}, " +
$"WHERE 1 = 1 " +
$"AND [Fund2MainRiskIK] = {(int)row["IK"]} " +
$"AND [ToDate] = @MaxDate;";
}
else
{
SQL =
"UPDATE " +
"[Documents].[Fund2MainRisk] " +
"SET " +
"[ToDate] = @Today " +
"WHERE 1 = 1 " +
"AND [ToDate] = @MaxDate " +
$"AND [Fund2MainRiskIK] = {(int)row["IK"]}" +
";" +
"INSERT INTO " +
"[Documents].[Fund2MainRisk] " +
"( " +
"[Fund_IK], " +
"[MainRiskIK], " +
"[Major], " +
"[FromDate], " +
"[ToDate] " +
") " +
"VALUES " +
"( " +
$"{(int)row["Fund_IK"]}, " +
$"{newMainRiskIK}, " +
$"{Convert.ToInt32(row["Major"])}, " +
"@Today, " +
"@MaxDate " +
")" +
";";
}
RiskTextForm.SQLQuery(SQL, connectionString, new Dates(this), false);
}
}
}
MessageBox.Show("Saved");
this.Close();
}
}
}
private void init(string Forening)
{
dgvRisks.Rows.Clear();
dgvRisks.Columns.Clear();
dgvRisks.AllowUserToAddRows = false;
string SQL;
SQL = "SELECT " +
"* " +
"FROM " +
"[Documents].[MainRisk] " +
"WHERE 1=1 " +
"AND [FromDate] <= @EvalDate " +
"AND [ToDate] > @EvalDate " +
"ORDER BY " +
"[MainRiskIndex] ASC";
using (var dtMainRisk = SQLQuery(SQL, connectionString, new Dates(this), true))
{
DataGridViewCheckBoxColumn dgvCol;
if (dtMainRisk == null || dtMainRisk.Rows.Count == 0)
{
dgvCol = new DataGridViewCheckBoxColumn();
dgvCol.HeaderText = "No Risks";
dgvCol.HeaderCell.Tag = 0;
dgvCol.FlatStyle = FlatStyle.Flat;
dgvRisks.Columns.Add(dgvCol);
}
else
{
foreach (DataRow row in dtMainRisk.Rows)
{
dgvCol = new DataGridViewCheckBoxColumn();
dgvCol.HeaderText = row["MainRiskID"].ToString();
dgvCol.HeaderCell.Tag = (int)row["MainRiskIK"];
dgvCol.FlatStyle = FlatStyle.Flat;
dgvCol.TrueValue = 1;
dgvCol.FalseValue = 0;
dgvRisks.Columns.Add(dgvCol);
}
}
}
if (Forening == "All")
{
SQL = "SELECT " +
"[Fund_IK], " +
"[Fund], " +
"[Fund_Name], " +
"[Fund_Owner_Name] " +
"FROM " +
"[Foundation].[Funds] " +
"ORDER BY " +
"[DISPLAY_ORDER_OFFICIAL] ASC;";
}
else
{
SQL = $"SELECT " +
$"[Fund_IK], " +
$"[Fund], " +
$"[Fund_Name], " +
$"[Fund_Owner_Name] " +
$"FROM " +
$"[Foundation].[Funds] " +
$"WHERE " +
$"[Fund_Owner_Name] = '{Forening}' " +
$"ORDER BY " +
$"[DISPLAY_ORDER_OFFICIAL] ASC;";
}
using (var dtFunds = SQLQuery(SQL, connectionString, new Dates(this), true))
{
int i = 0;
if (dtFunds == null || dtFunds.Rows.Count == 0)
{
dgvRisks.Rows.Add();
dgvRisks.Rows[0].HeaderCell.Value = "No Funds";
}
else
{
foreach (DataRow rowPort in dtFunds.Rows)
{
i++;
dgvRisks.Rows.Add();
dgvRisks.Rows[i - 1].HeaderCell.Value = rowPort["Fund_Name"].ToString();
dgvRisks.Rows[i - 1].HeaderCell.Tag = (int)rowPort["Fund_IK"];
dgvRisks.Rows[i - 1].HeaderCell.ToolTipText = rowPort["Fund"].ToString();
}
}
if (dtFunds == null || dtFunds.Rows.Count == 0)
{
}
else
{
SQL = "SELECT " +
"[F].[FUND_IK] as [FUND_IK], " +
"[MR].[MainRiskID] as [MainRiskID], " +
"[F2MR].[Major] as [Major] " +
"FROM " +
"[Foundation].[Funds] as [F] " +
"LEFT JOIN " +
"[Documents].[Fund2MainRisk] as [F2MR] " +
"ON [F].[Fund_IK] = [F2MR].[Fund_IK] " +
"INNER JOIN " +
"[Documents].[MainRisk] as [MR] " +
"ON [F2MR].[MainRiskIK] = [MR].[MainRiskIK] " +
"WHERE 1 = 1 " +
$"AND [MR].[FromDate] <= @EvalDate " +
$"AND [F2MR].[FromDate] <= @EvalDate " +
$"AND [MR].[ToDate] > @EvalDate " +
$"AND [F2MR].[ToDate] > @EvalDate";
using (var dtfund2MR = RiskTextForm.SQLQuery(SQL, connectionString, new Dates(this), true))
{
foreach (DataGridViewRow r in dgvRisks.Rows)
{
DataRow[] result;
foreach (DataGridViewCell c in r.Cells)
{
if (c.OwningColumn.HeaderText == "No Funds")
{
break;
}
result = dtfund2MR.Select($"FUND_IK = {(int)c.OwningRow.HeaderCell.Tag} AND MainRiskID = '{c.OwningColumn.HeaderText}'");
if (result.LongCount() != 0)
{
c.Value = 1;
c.Style.ForeColor = SystemColors.ControlLightLight;
c.Tag = result[0]["Major"];
c.Style.BackColor = SystemColors.ControlDarkDark;
if ((bool)result[0]["Major"])
{
c.Style.BackColor = Color.Black;
}
}
else
{
c.Value = 0;
c.Tag = false;
}
}
}
}
}
dgvRisks.CurrentCell = null;
}
}
private void btnSave_Click(object sender, EventArgs e)
{
string SQL;
int SubRiskIK;
int MainRiskIK;
if (parent == "MainRisk")
{
SQL = $"SELECT " +
$"* " +
$"FROM " +
$"[Documents].[MainRisk] " +
$"WHERE 1 = 1 " +
$"AND [MainRiskID] = '{txtRiskName.Text}' " +
$"AND [ToDate] = @MaxDate";
var dt = RiskTextForm.SQLQuery(SQL, connectionString, new Dates(), true);
if (dt == null || dt.Rows.Count == 0)
{
SQL = "INSERT INTO " +
"[Documents].[MainRisk] " +
"(" +
"[MainRiskID], " +
"[MainRiskIndex], " +
"[MainRiskText], " +
"[MainRiskType], " +
"[MainRiskReportingName], " +
"[FromDate], " +
"[ToDate]" +
") " +
$"VALUES " +
$"(" +
$"'{txtRiskName.Text}', " +
$"{(int)updIndex.Value}, " +
$"'{txtText.Text}', " +
$"'{txtRiskType.Text}', " +
$"'{txtReportingName.Text}', " +
$"@Today, " +
$"@MaxDate" +
$")";
}
else
{
MessageBox.Show("Already exits ... Not saved");
return;
}
}
else
{
SQL = $"SELECT " +
$"* " +
$"FROM " +
$"[Documents].[SubRisk] " +
$"WHERE 1 = 1" +
$"AND [SubRiskID] = '{txtRiskName.Text}' " +
$"AND [ToDate] = @MaxDate";
var dt = RiskTextForm.SQLQuery(SQL, connectionString, new Dates(), true);
if (dt == null || dt.Rows.Count == 0)
{
SQL = "INSERT INTO " +
"[Documents].[SubRisk] " +
"(" +
"[SubRiskID], " +
"[SubRiskIndex], " +
"[SubRiskType], " +
"[SubRiskText], " +
"[SubRiskReportingName], " +
"[FromDate], " +
"[ToDate]" +
") " +
$"VALUES " +
$"(" +
$"'{txtRiskName.Text}', " +
$" {(int)updIndex.Value}, " +
$"'{txtRiskType.Text}', " +
$"'{txtText.Text}', " +
$"'{txtReportingName.Text}', " +
$"@Today, " +
$"@MaxDate " +
$")";
}
else
{
MessageBox.Show("Already exits ... Not saved");
return;
}
}
RiskTextForm.SQLQuery(SQL, connectionString, new Dates(), false);
if (parent != "MainRisk")
{
SQL = $"Select " +
$"[SubRiskIK] " +
$"FROM " +
$"[Documents].[SubRisk] " +
$"WHERE 1=1 " +
$"AND [SubRiskID] = '{txtRiskName.Text}' " +
$"AND [ToDate] = @MaxDate";
using (var dt = RiskTextForm.SQLQuery(SQL, connectionString, new Dates(), true))
{
SubRiskIK = (int)dt.Rows[0][0];
}
SQL = $"Select " +
$"[MainRiskIK] " +
$"FROM " +
$"[Documents].[MainRisk] " +
$"WHERE 1=1 " +
$"AND [MainRiskID] = '{txtParent.Text}' " +
$"AND [ToDate] = @MaxDate";
using (var dt = RiskTextForm.SQLQuery(SQL, connectionString, new Dates(), true))
{
MainRiskIK = (int)dt.Rows[0][0];
}
SQL = "INSERT INTO " +
"[Documents].[MainRisk2SubRisk] " +
"(" +
"[MainRiskIK], " +
"[SubRiskIK], " +
"[FromDate], " +
"[ToDate]" +
") " +
"VALUES " +
"(" +
$"'{MainRiskIK}', " +
$"'{SubRiskIK}', " +
$"@Today, " +
$"@MaxDate" +
")";
RiskTextForm.SQLQuery(SQL, connectionString, new Dates(), false);
}
MessageBox.Show("Saved");
this.Close();
}
