//삭제버튼 연결 private void efnDelete() { string strSQL = @" SELECT * FROM Interest WHERE COMPANY = '" + cboStoSearch.Text.ToUpper() + @"' AND IFNULL(InterName,'') = '" + cboInter.Text + @"';"; DataTable dt = Network.GetDBTable(strSQL); if (dt.Rows.Count == 1) { strSQL = @" DELETE FROM Interest WHERE COMPANY = '" + cboStoSearch.Text.ToUpper() + @"' AND IFNULL(InterName,'') = '" + cboInter.Text + @"';"; if (Network.ExecDB(strSQL) > 0) { MessageBox.Show("삭제하였습니다."); } else { MessageBox.Show("삭제실패"); } mfnInterestBinder(); //fnInterestSearch(); } else { MessageBox.Show("관심종목에 없는 종목입니다."); } cboStoSearch.Text = ""; }
/// <summary> /// 콤보 초기화 /// </summary> /// <param name="p_Main"></param> /// <param name="p_Small"></param> private void fnComboInit() { cboMain.Items.Clear(); cboSmall.Items.Clear(); cboMain.Items.Add("<전체>"); cboColor.Items.Add("흰색"); cboColor.Items.Add("회색"); cboColor.Items.Add("하늘"); cboColor.Items.Add("노랑"); cboColor.Items.Add("빨강"); dtCombo = Network.GetDBTable(@"SELECT MainCate as '대분류', SmallCate as '소분류' FROM Memo WHERE Valid = 'Y' GROUP BY SmallCate, MainCate"); foreach (DataRow dr in dtCombo.Rows) { if (!cboMain.Items.Contains(dr["대분류"].ToString())) { cboMain.Items.Add(dr["대분류"].ToString()); } } }
/// <summary> /// 콤보초기화 //내부함수 /// </summary> private void fnComboReset() { string strCombMain = cboMain.Text; string strCombSmall = cboSmall.Text; strSQL = @"SELECT MainCate as '대분류', SmallCate as '소분류' FROM Memo WHERE Valid = 'Y' GROUP BY SmallCate, MainCate"; dtCombo = Network.GetDBTable(strSQL); cboMain.Items.Clear(); cboSmall.Items.Clear(); cboMain.Items.Add("<전체>"); //콤보박스 바인딩 foreach (DataRow dr in dtCombo.Rows) { if (!cboMain.Items.Contains(dr["대분류"].ToString())) { cboMain.Items.Add(dr["대분류"].ToString()); } } //폼에서 받아 온 콤보로 초기화 cboMain.SelectedItem = strCombMain; cboSmall.SelectedItem = strCombSmall; }
//콤보바인딩(관심목록) private void fnCboBingding() { string strSelItem = cboInter.SelectedItem == null ? "" : cboInter.SelectedItem.ToString(); cboInter.Items.Clear(); DataTable dtCombo = Network.GetDBTable("SELECT IFNULL(MAX(InterName),'') AS InterName FROM Interest GROUP BY InterName"); foreach (DataRow dr in dtCombo.Rows) { if (!cboInter.Items.Contains(dr["InterName"].ToString())) { cboInter.Items.Add(dr["InterName"].ToString()); } } /* * if (cboInter.Items.Count > 0) * cboInter.SelectedIndex = 0; */ if (strSelItem != "") { cboInter.SelectedItem = strSelItem; } else if (cboInter.Items.Count > 0) { cboInter.SelectedIndex = 0; } }
//현재보유량 /// <summary> /// 현재보유량 SELECT /// </summary> private void fnHoldSearch() { string strSQL = ""; strSQL = @"SELECT MAX(Name) AS 종목명 , SUM(Left * Price) / SUM(Left) AS 평균가 , SUM(Left) AS 수량 , SUM(Left * Price) AS 합계 , MAX(CodeNum) AS CodeNum FROM Buy LEFT JOIN Code ON Name = Company WHERE Left > 0 GROUP BY Name;"; DataTable dtHold = Network.GetDBTable(strSQL); //dgvView.DataSource = dtHold; //dgvView.Columns["CodeNum"].Visible = false; dgvView.Columns.Clear(); dgvView.Rows.Clear(); //컬럼추가 fnColumnAdd(enViewFlag.Hold, dtHold); // 컬럼정렬 fnSortGrid(enViewFlag.Hold, true); //컬럼에 값 넣기 fnInsertValue(enViewFlag.Hold, dtHold); }
// 관심 읽기 /// <summary> /// DB관심종목 읽기 /// </summary> private void fnInterestSearch() { mInterListName = cboInter.Text; string strSQL = ""; strSQL = @"SELECT Company AS 종목명 ,CodeNum ,DisSeq FROM Interest WHERE IFNULL(InterName,'') = '" + cboInter.Text + @"' ORDER BY DisSeq"; DataTable dtInterest = Network.GetDBTable(strSQL); dgvView.Columns.Clear(); dgvView.Rows.Clear(); //컬럼추가 fnColumnAdd(enViewFlag.Interest, dtInterest); //컬럼 정렬 fnSortGrid(enViewFlag.Interest, true); //컬럼에 값 넣기 fnInsertValue(enViewFlag.Interest, dtInterest); }
//컨트롤 초기화 private void mfnInitControl() { string strQuery = ""; strQuery += "SELECT Name FROM List GROUP BY Name"; DataTable dt = Network.GetDBTable(strQuery, mDbSource); for (int i = 0; i < dt.Rows.Count; i++) { if (i == 0) { txtDbList.Text = dt.Rows[i]["Name"].ToString(); } else { txtDbList.Text += "," + dt.Rows[i]["Name"].ToString(); } } mliStock.Clear(); if (txtDbList.Text.Contains(",")) { for (int i = 0; i < txtDbList.Text.Split(',').Length; i++) { mliStock.Add(txtDbList.Text.Split(',')[i].ToUpper()); } } else if (txtDbList.Text.Replace(" ", "") == "") { return; } else { mliStock.Add(txtDbList.Text.ToUpper()); } }
private DataTable mfnGetStock() { string strQuery = ""; strQuery += "\r\n" + "SELECT Company FROM Code"; return(Network.GetDBTable(strQuery)); }
private void fnInitCon() { DataTable dt = Network.GetDBTable("SELECT Contents FROM SimpleMemo WHERE CodeNum = '" + mCodeNum + "'"); if (dt.Rows.Count > 0) { txtMemo.Text = dt.Rows[0]["Contents"].ToString(); mIsContents = true; } }
//콤보바인딩-목록 private void fnInitComb() { string strQuery = ""; strQuery += "\r\n" + "SELECT Company FROM Code"; DataTable dt = Network.GetDBTable(strQuery); foreach (DataRow dr in dt.Rows) { cboStoSearch.Items.Add(dr["Company"].ToString()); } }
//관심 DisSeq Down버튼 private void efnDisSeqDown() { int intSelectedRow; try { intSelectedRow = dgvView.CurrentCell.RowIndex; if (intSelectedRow == dgvView.Rows.Count - 1) { return; } } catch (NullReferenceException) { return; } DataTable dtDisSeq = Network.GetDBTable(@"SELECT DisSeq FROM Interest WHERE InterName = '" + cboInter.Text + @"' AND (Company = '" + dgvView["종목명", intSelectedRow].Value.ToString() + @"' OR Company = '" + dgvView["종목명", intSelectedRow + 1].Value.ToString() + @"') ORDER BY DisSeq"); if (dtDisSeq.Rows.Count != 2) { MessageBox.Show("SELECT 실패"); return; } int intResult; intResult = Network.ExecDB(@"UPDATE Interest SET DisSeq = '" + dtDisSeq.Rows[1][0].ToString() + @"' WHERE InterName = '" + cboInter.Text + @"' AND Company = '" + dgvView["종목명", intSelectedRow].Value.ToString() + @"'; UPDATE Interest SET DisSeq = '" + dtDisSeq.Rows[0][0].ToString() + @"' WHERE InterName = '" + cboInter.Text + @"' AND Company = '" + dgvView["종목명", intSelectedRow + 1].Value.ToString() + @"';"); if (intResult < 1) { MessageBox.Show("Update 실패"); return; } fnInterestSearch(); dgvView.Rows[intSelectedRow + 1].Selected = true; dgvView.CurrentCell = dgvView.Rows[intSelectedRow + 1].Cells["종목명"]; dgvView.BeginEdit(true); }
//메인메모 바인딩 private void mfnMainMemoBinder() { DataTable dt = Network.GetDBTable("SELECT Contents FROM SimpleMemo WHERE CodeNum = 'MainMemo'"); if (dt.Rows.Count > 0) { txtMemo.Text = dt.Rows[0]["Contents"].ToString(); } else { Network.ExecDB("INSERT INTO SimpleMemo (Contents, CodeNum) VALUES ('', 'MainMemo')"); } }
private void fnQuery() { string strSQL = txtQuery.Text; try { dteShow.DataSource = Network.GetDBTable(strSQL); MessageBox.Show("Äõ¸®¼º°ø"); } catch (Exception e) { MessageBox.Show(e.ToString()); } }
/* * private void fnImportEx() * { * string connectString = * "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + System.Environment.CurrentDirectory + "\\StockCode.xlsx; Mode=ReadWrite;Extended Properties=\"Excel 12.0;HDR=YES;\";"; * OleDbConnection connect = new OleDbConnection(connectString); * OleDbDataAdapter da = new OleDbDataAdapter("Select [회사명] AS Company, [종목코드] AS CodeNum From [코스닥$]", connect); * DataTable dt = new DataTable(); * da.Fill(dt); * * string strSQL = ""; * * for (int i = 1500; i < dt.Rows.Count; i++) * { * strSQL += @" * INSERT INTO Code (Company, CodeNum) * VALUES ('" + dt.Rows[i]["Company"] + "', '" + dt.Rows[i]["CodeNum"] + "');"; * } * * SQLiteCommand sqlcmd; * sqlcmd = conn.CreateCommand(); * sqlcmd.CommandText = string.Format(strSQL); * SQLiteDataAdapter ap = new SQLiteDataAdapter(sqlcmd); * ap.Fill(dt); * } */ private void fnReadDB() { string strSQL = ""; strSQL = @"SELECT * FROM Code"; if (txtName.Text.Replace(" ", "") != "") { strSQL += @" WHERE Company LIKE '%" + txtName.Text + "%' OR CodeNum LIKE '%" + txtName.Text + "%'"; } dataGridView1.DataSource = Network.GetDBTable(strSQL); }
/// <summary> /// 신규 버튼 /// </summary> private void fnNew() { DataTable dtt = new DataTable(); strSQL = @"SELECT CASE WHEN Max(Seq) IS NULL THEN 0 ELSE Max(Seq) END AS 'Seq' FROM Memo"; dtt = Network.GetDBTable(strSQL); EnrollForm EF = new EnrollForm(); EF.StartPosition = FormStartPosition.CenterParent; if (EF.ShowDialog() == DialogResult.Cancel) { } }
//주식의 코드를 가져오기 private string mfnSearchCode(string p_Name) { string strQuery = @" SELECT CodeNum FROM Code WHERE Company = '" + p_Name.ToUpper() + "'"; DataTable dt = Network.GetDBTable(strQuery); if (dt.Rows.Count < 1) { MessageBox.Show("Wrong Input : " + p_Name); return(""); } else { return(dt.Rows[0]["CodeNum"].ToString()); } }
/// <summary> /// 재조회 /// - 스크롤 유지 /// - 재검색할 쿼리, 처음시작하는 인덱스로우 /// </summary> /// <param name="p_Query"></param> /// <param name="p_scrIndex"></param> private void fnReadDB(string p_Query, int p_scrIndex) { if (p_Query != "") { dt = Network.GetDBTable(mSchQuery); dataGridView1.DataSource = dt; } fnSortGrid(true); if (p_scrIndex > 0) { dataGridView1.FirstDisplayedScrollingRowIndex = p_scrIndex; } //그리드 색칠 fnPaint(); }
//*****************내부함수******************// #region 콤보초기화 /// <summary> /// 콤보초기화 /// </summary> private void fnInitCbo() { cboType.Items.Clear(); cboType.Items.Add("매수"); cboType.Items.Add("매도"); cboType.SelectedItem = "매수"; string strQuery = ""; strQuery += "\r\n" + "SELECT Company FROM Code"; DataTable dt = Network.GetDBTable(strQuery); foreach (DataRow dr in dt.Rows) { cboStoSearch.Items.Add(dr["Company"].ToString()); } }
//디비에서 디테일 채워주는 private void mfnGetDetailFromDB(string p_Name, int p_HowLoing) { string strQuery = ""; strQuery += "SELECT * FROM List WHERE Name = '" + p_Name + "' ORDER BY Date LIMIT " + (p_HowLoing + 60).ToString(); DataTable dt = Network.GetDBTable(strQuery, mDbSource); Variables.DetailInfo[] vdTemp = new Variables.DetailInfo[p_HowLoing + 60]; for (int i = 0; i < dt.Rows.Count; i++) { vdTemp[i].date = dt.Rows[i]["Date"].ToString(); vdTemp[i].price = Convert.ToInt32(dt.Rows[i]["price"]); vdTemp[i].volumn = Convert.ToInt32(dt.Rows[i]["volumn"]); vdTemp[i].highPrice = Convert.ToInt32(dt.Rows[i]["highPrice"]); vdTemp[i].lowPrice = Convert.ToInt32(dt.Rows[i]["lowPrice"]); vdTemp[i].startPrice = Convert.ToInt32(dt.Rows[i]["startPrice"]); } mDetailInfo = vdTemp; }
/// <summary> /// DB SELECT 및 열 초기화 /// </summary> /// <param name="p_isAll"></param> /// <param name="p_DateF"></param> /// <param name="p_DateS"></param> /// <param name="p_MainCate"></param> /// <param name="p_SmallCate"></param> /// <param name="p_Title"></param> /// <param name="p_Contents"></param> private void fnReadDB(string p_DateF, string p_DateS, string p_MainCate, string p_SmallCate, string p_Search) { mSchQuery = @" SELECT Seq as 'Seq' , Date as '날짜' , MainCate as '대분류' , SmallCate as '소분류' , REPLACE(Title,'♤','''') as '제목' , REPLACE(Contents, '♤','''') as '내용' , CASE WHEN File != '' THEN 'Y' ELSE '' END as '파일' , File as 'FileDetail' , Contents_Rtf as 'Contents_Rtf' , Color as 'Color' , reportSeq as 'reportSeq' , reportType as 'reportType' FROM Memo WHERE Title != '' AND Valid = 'Y'"; //if (p_SelectedRow != -99) //{ mSchQuery += !chkDate.Checked ? " AND Date >= " + p_DateF + " AND Date <= " + p_DateS : ""; if (cboMain.Text != "") { mSchQuery += (p_MainCate.Equals("<전체>") ? "" : " AND MainCate = '" + p_MainCate + "'"); } if (cboSmall.Text != "") { mSchQuery += " AND SmallCate = '" + p_SmallCate + "'"; } if (chkWhite.Checked || chkGray.Checked || chkBlue.Checked || chkYellow.Checked || chkRed.Checked) { mSchQuery += @" AND Color IN (" + (chkWhite.Checked ? "''," : "") + (chkGray.Checked ? "'회색'," : "") + (chkBlue.Checked ? "'하늘'," : "") + (chkYellow.Checked ? "'노랑'," : "") + (chkRed.Checked ? "'빨강'," : ""); mSchQuery = mSchQuery.Substring(0, mSchQuery.Length - 1); mSchQuery += ")"; } if (txtSearch.Text != "") { mSchQuery += chkTitle.Checked && !chkContents.Checked ? " AND Title LIKE '%" + p_Search.Replace("'", "♤") + "%'" : ""; mSchQuery += !chkTitle.Checked && chkContents.Checked ? " AND Contents LIKE '%" + p_Search.Replace("'", "♤") + "%'" : ""; mSchQuery += chkTitle.Checked && chkContents.Checked ? " AND (Title LIKE '%" + p_Search.Replace("'", "♤") + "%'" + " OR Contents LIKE '%" + p_Search.Replace("'", "♤") + "%'" + ")" : ""; } //} /* * else * { * mSchQuery += " AND Date >= " + p_DateF + " AND Date <= " + p_DateS; * } * */ mSchQuery += " ORDER BY Date"; dt = Network.GetDBTable(mSchQuery); dataGridView1.DataSource = dt; //제일 밑을 포커싱 fnFocusGrid(-99); //그리드 컬럼 정렬 fnSortGrid(true); //그리드 색칠 fnPaint(); }
/// <summary> /// 체크박스 초기화 /// </summary> private void fnInitOpt() { DataTable dtOpt = new DataTable(); strSQL = @" SELECT * FROM InitMemoOpt"; dtOpt = Network.GetDBTable(strSQL); if (dtOpt.Rows.Count < 1) { strSQL = @"INSERT INTO InitMemoOpt (chkDate, DateF, DateS, chkTitle, chkWhite, chkGray, chkBlue, chkYellow, chkRed, chkContents, isCopy) VALUES ('Y', '20000101', '20801231', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y')"; Network.ExecDB(strSQL); strSQL = @" SELECT * FROM InitMemoOpt"; dtOpt = Network.GetDBTable(strSQL); } if (dtOpt.Rows[0]["chkDate"].ToString().ToUpper().Equals("Y")) { chkDate.Checked = true; } else { chkDate.Checked = false; } dteF.Value = new DateTime(Convert.ToInt32(dtOpt.Rows[0]["DateF"].ToString().Substring(0, 4)) , Convert.ToInt32(dtOpt.Rows[0]["DateF"].ToString().Substring(4, 2)) , Convert.ToInt32(dtOpt.Rows[0]["DateF"].ToString().Substring(6, 2)), 0, 0, 0); dteS.Value = new DateTime(Convert.ToInt32(dtOpt.Rows[0]["DateS"].ToString().Substring(0, 4)) , Convert.ToInt32(dtOpt.Rows[0]["DateS"].ToString().Substring(4, 2)) , Convert.ToInt32(dtOpt.Rows[0]["DateS"].ToString().Substring(6, 2)), 0, 0, 0); if (dtOpt.Rows[0]["chkTitle"].ToString().ToUpper().Equals("Y")) { chkTitle.Checked = true; } else { chkTitle.Checked = false; } if (dtOpt.Rows[0]["chkContents"].ToString().ToUpper().Equals("Y")) { chkContents.Checked = true; } else { chkContents.Checked = false; } if (dtOpt.Rows[0]["chkWhite"].ToString().ToUpper().Equals("Y")) { chkWhite.Checked = true; } else { chkWhite.Checked = false; } if (dtOpt.Rows[0]["chkGray"].ToString().ToUpper().Equals("Y")) { chkGray.Checked = true; } else { chkGray.Checked = false; } if (dtOpt.Rows[0]["chkBlue"].ToString().ToUpper().Equals("Y")) { chkBlue.Checked = true; } else { chkBlue.Checked = false; } if (dtOpt.Rows[0]["chkYellow"].ToString().ToUpper().Equals("Y")) { chkYellow.Checked = true; } else { chkYellow.Checked = false; } if (dtOpt.Rows[0]["chkRed"].ToString().ToUpper().Equals("Y")) { chkRed.Checked = true; } else { chkRed.Checked = false; } }