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