//삭제버튼 연결
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;
}
}
