/// <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(); }
/// <summary> /// »èÁ¦ ¹öÆ° ¿¬°á /// </summary> private void efnDel() { //µðºñ¿¬°á string strSQL = ""; string strName = dataGridView1.CurrentRow.Cells["Á¾¸ñ¸í"].Value.ToString(); //¼±ÅÃµÈ À̸§ DataRow[] drr = mds.Tables[0].Select("Á¾·ù = '¸Åµµ' AND Á¾¸ñ¸í = '" + strName + "' AND ÀÜ·® <> ¼ö·®", "Seq DESC"); if (dataGridView1.CurrentRow.Cells["Á¾·ù"].Value.ToString() == "¸Å¼ö") { if (dataGridView1.CurrentRow.Cells["ÀÜ·®"].Value.ToString() != dataGridView1.CurrentRow.Cells["¼ö·®"].Value.ToString()) { MessageBox.Show("ÇØ´ç Á¾¸ñÀÇ °¡Àå ÃÖ±Ù ¸Åµµ¸¦ ¸ÕÀú Áö¿ì½Ê½Ã¿À.\r\n(³¯Â¥ : " + drr[0]["³¯Â¥"].ToString() + ", ¼ö·® : " + drr[0]["¼ö·®"].ToString() + ", °¡°Ý : " + drr[0]["°¡°Ý"].ToString() + ")"); return; } strSQL = @" DELETE FROM Buy WHERE Seq = '" + dataGridView1.CurrentRow.Cells["Seq"].Value.ToString() + @"'; "; int iresult = Network.ExecDB(strSQL); if (iresult > 0) { MessageBox.Show("»èÁ¦¼º°ø"); } else { MessageBox.Show("»èÁ¦½ÇÆÐ"); } } else if (dataGridView1.CurrentRow.Cells["Á¾·ù"].Value.ToString() == "¸Åµµ") { string strSellSeq; // = mds.Tables[1].Rows[0][0].ToString(); //¼±ÅÃµÈ Seq int i = 0; //¹Ýº¹¹® º¯¼ö int intSellQ = Convert.ToInt32(dataGridView1.CurrentRow.Cells["¼ö·®"].Value.ToString()); //ÆǸÅÇÏ°íÀÚ ÇÏ´Â ¼ö·® int intQ; //buyÀÇ ³²Àº ¼ö·® DataRow[] dr = mds.Tables[0].Select("Á¾·ù = '¸Å¼ö' AND Á¾¸ñ¸í = '" + strName + "' AND ÀÜ·® <> ¼ö·®", "Seq DESC"); strSellSeq = drr[0]["Seq"].ToString(); if (dataGridView1.CurrentRow.Cells["Seq"].Value.ToString() != strSellSeq) { MessageBox.Show("ÇØ´ç Á¾¸ñÀÇ °¡Àå ÃÖ±Ù ¸Åµµ¸¦ ¸ÕÀú Áö¿ì½Ê½Ã¿À.\r\n(³¯Â¥ : " + drr[0]["³¯Â¥"].ToString() + ", ¼ö·® : " + drr[0]["¼ö·®"].ToString() + ", °¡°Ý : " + drr[0]["°¡°Ý"].ToString() + ")"); return; } else { strSQL = @" DELETE FROM Revenue WHERE SellSeq = '" + strSellSeq + @"'; "; strSQL += @" DELETE FROM Sell WHERE Seq = '" + strSellSeq + @"'; "; while (intSellQ > 0) { intQ = Convert.ToInt32(dr[i]["¼ö·®"].ToString()) - Convert.ToInt32(dr[i]["ÀÜ·®"].ToString()); if (intSellQ >= intQ) { strSQL += @" UPDATE Buy SET Left = Quantity WHERE Seq = '" + dr[i]["Seq"].ToString() + @"'; "; intSellQ = intSellQ - intQ; } else { strSQL += @" UPDATE Buy SET Left = " + (Convert.ToInt32(dr[i]["ÀÜ·®"].ToString()) + intSellQ) + @" WHERE Seq = '" + dr[i]["Seq"].ToString() + @"'; "; intSellQ = 0; } i++; } int iresult = Network.ExecDB(strSQL); if (iresult > 0) { MessageBox.Show("»èÁ¦ ¼º°ø"); } else { MessageBox.Show("»èÁ¦½ÇÆÐ"); } } } fnReadDB(); }