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(); }