private void fnReadDBM(string p_Year)
{
double dSum;
string strSQL = @"SELECT MAX(SUBSTR(Date,5,2)) AS 월, SUM((SPrice * Quantity) - (BPrice * Quantity) - (SPrice * Quantity * 0.003)) AS 손익
FROM Revenue
WHERE SUBSTR(Date,1,4) = '" + p_Year + @"'
GROUP BY SUBSTR(Date,1,6);";
strSQL += @"SELECT SUM(손익) AS 합
FROM (
SELECT MAX(SUBSTR(Date,5,2)) AS 월, SUM((SPrice * Quantity) - (BPrice * Quantity) - (SPrice * Quantity * 0.003)) AS 손익
FROM Revenue
WHERE SUBSTR(Date,1,4) = '" + p_Year + @"'
GROUP BY SUBSTR(Date,1,6)
);";
DataSet dsM = Network.GetDBSet(strSQL);
dgvMonth.DataSource = dsM.Tables[0];
fnSortGrid(true, dgvMonth, "월", "손익");
if (Convert.ToInt32(dsM.Tables[0].Rows.Count) > 0)
{
dSum = Convert.ToDouble(dsM.Tables[1].Rows[0][0].ToString());
txtSumM.Text = dSum.ToString("#,##");
}
}
private void fnReadDBD(string p_YearMonth)
{
double dSum;
string strSQL = @"SELECT MAX(SUBSTR(Date,7,2)) AS 일, SUM((SPrice * Quantity) - (BPrice * Quantity) - (SPrice * Quantity * 0.003)) AS 손익
FROM Revenue
WHERE SUBSTR(Date,1,6) = '" + p_YearMonth + @"'
GROUP BY SUBSTR(Date,1,8)
ORDER BY 일;";
strSQL += @"SELECT SUM(손익) AS 합
FROM (
SELECT MAX(SUBSTR(Date,7,2)) AS 일, SUM((SPrice * Quantity) - (BPrice * Quantity) - (SPrice * Quantity * 0.003)) AS 손익
FROM Revenue
WHERE SUBSTR(Date,1,6) = '" + p_YearMonth + @"'
GROUP BY SUBSTR(Date,1,8)
);";
DataSet dsD = Network.GetDBSet(strSQL);
dgvDate.DataSource = dsD.Tables[0];
fnSortGrid(true, dgvDate, "일", "손익");
if (dsD.Tables[0].Rows.Count > 0)
{
dSum = Convert.ToDouble(dsD.Tables[1].Rows[0][0].ToString());
txtSumD.Text = dSum.ToString("#,##");
}
}
/// <summary>
/// tab2 Year불러오기.
/// </summary>
private void fnReadDBY()
{
double dSum;
string strSQL = @"SELECT MAX(SUBSTR(Date,1,4)) AS 년, SUM((SPrice * Quantity) - (BPrice * Quantity) - (SPrice * Quantity * 0.003)) AS 손익
FROM Revenue
GROUP BY SUBSTR(Date,1,4);";
strSQL += @"SELECT SUM(손익) AS 합
FROM (
SELECT MAX(SUBSTR(Date,1,4)) AS 년, SUM((SPrice * Quantity) - (BPrice * Quantity) - (SPrice * Quantity * 0.003)) AS 손익
FROM Revenue
GROUP BY SUBSTR(Date,1,4)
);";
DataSet dsY = Network.GetDBSet(strSQL);
dgvYear.DataSource = dsY.Tables[0];
fnSortGrid(true, dgvYear, "년", "손익");
if (Convert.ToInt32(dsY.Tables[0].Rows.Count) > 0)
{
dSum = Convert.ToDouble(dsY.Tables[1].Rows[0][0].ToString());
txtSumY.Text = dSum.ToString("#,##");
}
}
//추가버튼 연결
private void efnInsert()
{
if (cboInter.Text.Replace(" ", "") == "")
{
MessageBox.Show("관심목록이 빈 값입니다.");
return;
}
string strSQL = @"
SELECT *
FROM Interest
WHERE COMPANY = '" + cboStoSearch.Text.ToUpper() + @"'
AND IFNULL(InterName,'') = '" + cboInter.Text + @"';
SELECT *
FROM Code
WHERE COMPANY = '" + cboStoSearch.Text.ToUpper() + @"'";
DataSet ds = Network.GetDBSet(strSQL);
if (ds.Tables[0].Rows.Count >= 1)
{
cboStoSearch.Text = "";
}
else if (ds.Tables[1].Rows.Count < 1)
{
MessageBox.Show("없는 종목입니다.");
}
else
{
strSQL = @"
INSERT INTO Interest(Company, CodeNum, DisSeq, InterName)
SELECT Company
, CodeNum
, IFNULL((SELECT MAX(DisSeq) + 1 AS DisSeq
FROM Interest
WHERE IFNULL(InterName,'') = '" + cboInter.Text + @"'
GROUP BY InterName),1) AS DisSeq
, '" + cboInter.Text + @"'
FROM Code
WHERE COMPANY = '" + cboStoSearch.Text.ToUpper() + "';";
if (Network.ExecDB(strSQL) > 0)
{
MessageBox.Show("추가하였습니다.");
}
else
{
MessageBox.Show("추가실패");
}
fnInterestSearch();
}
cboStoSearch.Text = "";
}
/// <summary>
/// Report에서 팝업으로 넘어오기
/// </summary>
/// <param name="p_repSeq">Buy Sell</param>
/// <param name="p_repSeq">[0]테이블 [1]종목의 Seq</param>
/// <param name="p_memSeq">메모의 Seq(기존 데이터가 있을 경우(수정)만)</param>
public EnrollForm(string p_reportType, string p_reportSeq, string p_memSeq)
{
InitializeComponent();
mReportSeq = p_reportSeq;
mReportType = p_reportType;
//콤보바인딩
fnComboInit();
string strSQL = "SELECT * FROM Memo WHERE Valid = 'Y' AND Seq = '" + p_memSeq + "';\r\n";
if (p_reportType == "Buy")
{
strSQL += "SELECT Name, Date FROM Buy WHERE Seq = '" + p_reportSeq + "'";
}
else if (p_reportType == "Sell")
{
strSQL += "SELECT Name, Date FROM Sell WHERE Seq = '" + p_reportSeq + "'";
}
DataSet ds = Network.GetDBSet(strSQL);
//수정으로 들어왔을 경우
if (ds.Tables[0].Rows.Count > 0)
{
mSeq = Convert.ToInt32(ds.Tables[0].Rows[0]["Seq"].ToString());
//mDate = ds.Tables[0].Rows[0]["Date"].ToString();
dte.Value = DateTime.ParseExact(ds.Tables[0].Rows[0]["Date"].ToString(), "yyyyMMdd", null);
//신규 수정 여부
mIsNew = false;
//그 밖에 컨트롤 바인딩
fnSelectedInit(ds.Tables[0]);
}
else
{
txtTitle.Text = p_reportType == "Buy" ? "Buy" : p_reportType == "Sell" ? "Sell" : "";
dte.Value = DateTime.ParseExact(ds.Tables[1].Rows[0]["Date"].ToString(), "yyyyMMdd", null);
//신규 수정 여부
mIsNew = true;
}
cboMain.Text = "종목";
cboSmall.Text = ds.Tables[1].Rows[0]["Name"].ToString();
cboMain.Enabled = false;
cboSmall.Enabled = false;
dte.Enabled = false;
}
/// <summary>
/// 현재보유량 SELECT
/// </summary>
private void fnHoldSearch()
{
string strSQL = "";
mDTHold = new DataSet();
strSQL = @"SELECT MAX(Name) AS 종목명
, SUM(Left * Price) / SUM(Left) AS 평균가
, SUM(Left) AS 수량
, SUM(Left * Price) AS 합계
FROM Buy
WHERE Left > 0
GROUP BY Name;";
strSQL += @"SELECT *
FROM Buy;";
strSQL += @"SELECT *
FROM Code;";
mDTHold = Network.GetDBSet(strSQL);
//dgvHold.DataSource = mDTHold.Tables[0];
//컬럼추가
//fnColumnAdd(dgvHold);
// 컬럼정렬
//fnSortGrid(dgvHold, true);
//매도 콤보 아이템 초기화
cboName.Items.Clear();
foreach (DataRow dr in mDTHold.Tables[0].Rows)
{
if (!cboName.Items.Contains(dr["종목명"].ToString()))
{
cboName.Items.Add(dr["종목명"].ToString());
}
}
}
/// <summary>
/// 디비읽기
/// </summary>
private void fnReadDB()
{
string strSQL = "";
mds = new DataSet();
strSQL = @"SELECT Date AS 날짜
, Name AS 종목명
, BPrice AS 매수가
, SPrice AS 매도가
, Quantity AS 수량
, BPrice * Quantity AS 매수합계
, SPrice * Quantity AS 매도합계
, SPrice * Quantity * 0.003 AS 수수료
, (SPrice * Quantity) - (BPrice * Quantity) - (SPrice * Quantity * 0.003) AS 손익
, ((SPrice * Quantity) - (BPrice * Quantity) - (SPrice * Quantity * 0.003)) / (BPrice * Quantity) * 100 AS 비율
FROM Revenue
WHERE Name LIKE '%%'";
if (!chkDate.Checked)
{
strSQL += " AND Date >= '" + dteF.Value.ToString("yyyyMMdd") + "' AND Date <= '" + dteS.Value.ToString("yyyyMMdd") + "'";
}
if (txtName.Text.Replace(" ", "") != "")
{
strSQL += " AND Name LIKE '%" + txtName.Text + "%'";
}
strSQL += ";";
mds = Network.GetDBSet(strSQL);
dataGridView1.DataSource = mds.Tables[0];
//그리드 초기화
fnSortGrid(true);
//셀색칠
//fnPaint();
}
/// <summary>
/// µðºñÀбâ
/// </summary>
private void fnReadDB()
{
//µðºñ¿¬°á
string strSQL = "";
mds = new DataSet();
strSQL = @"SELECT b.Date AS ³¯Â¥
, '¸Å¼ö' AS Á¾·ù
, b.Name AS Á¾¸ñ¸í
, b.Quantity AS ¼ö·®
, b.Left AS ÀÜ·®
, b.Price AS °¡°Ý
, b.Quantity * Price AS ÇÕ°è
, CASE WHEN IFNULL(m.ReportSeq,'') = ''
THEN ''
ELSE 'Y' END AS ¸Þ¸ð
, b.Seq
, m.Seq AS memSeq
FROM Buy b
LEFT JOIN Memo m ON b.Seq = m.ReportSeq AND m.ReportType = 'Buy' AND m.Valid = 'Y'
WHERE b.Name LIKE '%%'"; // ÀÌ°Å ¿Ö ÀÌ·¸°Ô Ç؇JÁö??
if (cboType.Text == "¸Åµµ")
{
strSQL += " AND Name = '!#@$%#^&#%^#'"; //¸Å¼öÀÏ °æ¿ì¸¸ °Ë»öÀÌ µÇµµ·Ï ´õ¹Ì°ªÀ» ³ÖÀ½
}
if (!chkDate.Checked)
{
strSQL += " AND Date >= '" + dteF.Value.ToString("yyyyMMdd") + "' AND Date <= '" + dteS.Value.ToString("yyyyMMdd") + "'";
}
if (txtName.Text.Replace(" ", "") != "")
{
strSQL += " AND Name LIKE '%" + txtName.Text.ToUpper() + "%'";
}
strSQL += @"
UNION ALL
SELECT s.Date AS ³¯Â¥
, '¸Åµµ' AS Á¾·ù
, s.Name AS Á¾¸ñ¸í
, s.Quantity AS ¼ö·®
, ''
, s.Price AS °¡°Ý
, s.Quantity * Price AS ÇÕ°è
, CASE WHEN IFNULL(m.ReportSeq,'') = ''
THEN ''
ELSE 'Y' END AS ¸Þ¸ð
, s.Seq
, m.Seq AS memSeq
FROM Sell s
LEFT JOIN Memo m ON s.Seq = m.ReportSeq AND m.ReportType = 'Sell' AND m.Valid = 'Y'
WHERE s.Name LIKE '%%'";
if (cboType.Text == "¸Å¼ö")
{
strSQL += " AND Name = '!#@$%#^&#%^#'"; //¸ÅµµÀÏ °æ¿ì¸¸ °Ë»öÀÌ µÇµµ·Ï
}
if (!chkDate.Checked)
{
strSQL += " AND Date >= '" + dteF.Value.ToString("yyyyMMdd") + "' AND Date <= '" + dteF.Value.ToString("yyyyMMdd") + "'";
}
if (txtName.Text.Replace(" ", "") != "")
{
strSQL += " AND Name LIKE '%" + txtName.Text.ToUpper() + "%'";
}
strSQL += @"
ORDER BY ³¯Â¥
";
strSQL += ";";
/*
* strSQL += @"
* SELECT CASE WHEN (SELECT COUNT(*) FROM Sell) < 1
* THEN -1
* ELSE (SELECT MAX(Seq) FROM Sell) END;
* ";
*/
mds = Network.GetDBSet(strSQL);
dataGridView1.DataSource = mds.Tables[0];
//±×¸®µå ÃʱâÈ
fnSortGrid(true);
//¼¿»öÄ¥
fnPaint();
}