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