private void LoadTakeStockItems()
{
SIS.DBClass.DBClassItemsInfo DBCII = new DBClass.DBClassItemsInfo();
SIS.Configuration.ClsTakeStockConfig CTSC = new Configuration.ClsTakeStockConfig();
bool result = DBCII.QueryAllItems(CTSC);
if (result)
{
dgvTakeStockItemsInfo.Rows.Clear();
for (int i = 0; i < CTSC.TakeStockItems.Length; i++)
{
var index = dgvTakeStockItemsInfo.Rows.Add();
dgvTakeStockItemsInfo.Rows[index].Cells["ItemsID"].Value = CTSC.TakeStockItems[i].ItemsID;
dgvTakeStockItemsInfo.Rows[index].Cells["NAME"].Value = CTSC.TakeStockItems[i].NAME;
dgvTakeStockItemsInfo.Rows[index].Cells["Inventory"].Value = CTSC.TakeStockItems[i].Inventory.ToString();
dgvTakeStockItemsInfo.Rows[index].Cells["TakeStockInventory"].Value = CTSC.TakeStockItems[i].TakeStockInventory.ToString();
dgvTakeStockItemsInfo.Rows[index].Cells["GainLossInventory"].Value = CTSC.TakeStockItems[i].GainLossInventory.ToString();
dgvTakeStockItemsInfo.Rows[index].Cells["IsTakeStock"].Value = CTSC.TakeStockItems[i].IsTakeStock;
dgvTakeStockItemsInfo.Rows[index].Cells["ItemsUnit"].Value = CTSC.TakeStockItems[i].ItemsUnit;
dgvTakeStockItemsInfo.Rows[index].Cells["Price"].Value = CTSC.TakeStockItems[i].Price.ToString();
dgvTakeStockItemsInfo.Rows[index].Cells["Totals"].Value = CTSC.TakeStockItems[i].Totals.ToString();
dgvTakeStockItemsInfo.Rows[index].Cells["Notes"].Value = CTSC.TakeStockItems[i].Notes;
}
}
}
private void RunDeleteData()
{
My.MyDatabase MyDb = new My.MyDatabase();
SIS.Configuration.ClsItemsConfig CIC = new Configuration.ClsItemsConfig();
CIC.ItemsID = txtItemsID.Text;
CIC.NAME = txtNAME.Text;
SIS.DBClass.DBClassItemsInfo DBCII = new DBClass.DBClassItemsInfo();
if (MyDb.AuthPK(CIC.ItemsID, "ItemsID", "ItemsInfo") == true)
{
if (DBCII.DeleteOneData(CIC.ItemsID))
{
MessageBox.Show("刪除[" + CIC.ItemsID + "-" + CIC.NAME +
"]商品資料成功", "刪除商品資料", MessageBoxButtons.OK, MessageBoxIcon.Information);
LoadDefaultValue();
}
else
{
MessageBox.Show("刪除[" + CIC.ItemsID + "-" + CIC.NAME +
"]商品資料失敗", "刪除商品資料", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
else
{
MessageBox.Show("對不起，資料庫不存在[ " + CIC.ItemsID + "-" + CIC.NAME +
" ]商品資料!!(資料不存在)", "資料刪除");
}
}
/// <summary>
/// 刪除主表和明細資料
/// </summary>
/// <param name="PKval">傳入欲刪除資料的主鍵值</param>
/// <param name="OLDItems">傳入刪除前商品集合</param>
/// <returns></returns>
public bool DeleteMasterDetailsData(string PKval, SIS.Configuration.ClsRMAConfig OLDCRC)
{
InitDB();
string delCmd, delCmd2;
int i = 0;
delCmd = "Delete From " + TableName + " Where RMAID='" + PKval + "'";
transaction = conn.BeginTransaction("MyDeleteTransaction");
try
{
cmd = conn.CreateCommand(); // new SqlCommand(updateCmd, conn);
cmd.CommandText = delCmd;
cmd.Transaction = transaction;
i = cmd.ExecuteNonQuery();//若沒有任何資料進行異動,則會回傳0
delCmd2 = "Delete From " + TableName2 + " Where RMAID='" + PKval + "'";
cmd.CommandText = delCmd2;
i = i + cmd.ExecuteNonQuery();//若沒有任何資料進行異動,則會回傳0
DBClass.DBClassItemsInfo DBCItemsInfo = new DBClassItemsInfo();
for (int j = 0; j < OLDCRC.RMAItems.Length; j++)
{
if (OLDCRC.RMAType == "Customer")
{
DBCItemsInfo.UpdateInventory(OLDCRC.RMAItems[j].ItemsID, -(OLDCRC.RMAItems[j].Quantity)); //更新商品庫存量
}
else
{
DBCItemsInfo.UpdateInventory(OLDCRC.RMAItems[j].ItemsID, (OLDCRC.RMAItems[j].Quantity)); //更新商品庫存量
}
}
transaction.Commit(); //try to Commit above sql command
conn.Close();
return(true);
}
catch (Exception ex)
{
try
{
transaction.Rollback();
}
catch (Exception ex2)
{
errorMsg = ex2.Message;
return(false);
}
errorMsg = ex.Message;
conn.Close();
return(false);
}
}
/// <summary>
/// 將資料寫入資料庫中
/// </summary>
private void RunInsertData()
{
try
{
My.MyDatabase MyDb = new My.MyDatabase();
SIS.Configuration.ClsItemsConfig CIC = new Configuration.ClsItemsConfig();
CIC.ItemsID = txtItemsID.Text;
CIC.NAME = txtNAME.Text;
CIC.ItemsType = cboItemsType.Text.Substring(0, cboItemsType.Text.IndexOf("-"));
CIC.Specifications = txtSpecifications.Text.Replace("'", "''"); //將一個單引號置換成二個單引號避免SQL語法錯誤
CIC.ItemsUnit = cboItemsUnit.Text;
CIC.SellingPrice = int.Parse(mtbSellingPrice.Text);
CIC.CostPrice = int.Parse(mtbCostPrice.Text);
CIC.MSRP = int.Parse(mtbMSRP.Text);
CIC.ManufacturerID = cboManufacturer.Text.Substring(0, cboManufacturer.Text.IndexOf("-"));
CIC.Inventory = int.Parse(mtbInventory.Text);
CIC.SafeInventory = int.Parse(mtbSafeInventory.Text);
CIC.Notes = rtbNotes.Text.Replace("'", "''");
SIS.DBClass.DBClassItemsInfo DBCII = new DBClass.DBClassItemsInfo();
if (MyDb.AuthPK(CIC.ItemsID, "ItemsID", "ItemsInfo") == false)
{
if (DBCII.InsertData(CIC))
{
MessageBox.Show("新增[" + CIC.ItemsID + "-" + CIC.NAME +
"]商品資料成功", "新增商品資料", MessageBoxButtons.OK, MessageBoxIcon.Information);
LoadDefaultValue();
}
else
{
MessageBox.Show("新增[" + CIC.ItemsID + "-" + CIC.NAME +
"]商品資料失敗", "新增商品資料", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
else
{
MessageBox.Show("對不起，資料庫已存在[ " + CIC.ItemsID + "-" + CIC.NAME +
" ]商品資料!!(資料重複)", "資料新增");
}
}
catch (Exception ex)
{
MessageBox.Show("錯誤訊息:" + ex.Message.ToString(), "發生例外");
}
}
private void RunUpdateData()
{
My.MyDatabase MyDb = new My.MyDatabase();
SIS.Configuration.ClsItemsConfig CIC = new Configuration.ClsItemsConfig();
CIC.ItemsID = txtItemsID.Text;
CIC.NAME = txtNAME.Text;
CIC.ItemsType = cboItemsType.Text.Substring(0, cboItemsType.Text.IndexOf("-"));
CIC.Specifications = txtSpecifications.Text.Replace("'", "''");
CIC.ItemsUnit = cboItemsUnit.Text;
CIC.SellingPrice = int.Parse(mtbSellingPrice.Text);
CIC.CostPrice = int.Parse(mtbCostPrice.Text);
CIC.MSRP = int.Parse(mtbMSRP.Text);
CIC.ManufacturerID = cboManufacturer.Text.Substring(0, cboManufacturer.Text.IndexOf("-"));
CIC.Inventory = int.Parse(mtbInventory.Text);
CIC.SafeInventory = int.Parse(mtbSafeInventory.Text);
CIC.Notes = rtbNotes.Text.Replace("'", "''");
SIS.DBClass.DBClassItemsInfo DBCII = new DBClass.DBClassItemsInfo();
if (MyDb.AuthPK(CIC.ItemsID, "ItemsID", "ItemsInfo") == true)
{
if (DBCII.Update(CIC))
{
MessageBox.Show("更新[" + CIC.ItemsID + "-" + CIC.NAME +
"]商品資料成功", "更新商品資料", MessageBoxButtons.OK, MessageBoxIcon.Information);
LoadDefaultValue();
}
else
{
MessageBox.Show("更新[" + CIC.ItemsID + "-" + CIC.NAME +
"]商品資料失敗", "更新商品資料", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
else
{
MessageBox.Show("對不起，資料庫不存在[ " + CIC.ItemsID + "-" + CIC.NAME +
" ]商品資料!!(資料不存在)", "資料更新");
}
}
/// <summary>
/// 新增一筆資料
/// </summary>
/// <param name="CSC">傳入存放更新資料的SIS.Configuration.ClsStockConfig CSC</param>
/// <returns></returns>
public bool InsertData(SIS.Configuration.ClsStockConfig CSC)
{
InitDB();
string insertCmd, insertCmd2;
insertCmd = "Insert Into " + TableName + " (StockID,StockDate ,TotalPreTax, Tax,TotalAfterTax,ManufacturerID ,BusinessTaxStockTaxRate ," +
"AmountPaid ,UnpaidAmount ,StockStaff , PaymentType ,Notes";
insertCmd = insertCmd + ") Values(";
insertCmd = insertCmd + "'" + CSC.StockID + "',";
insertCmd = insertCmd + "'" + CSC.StockDate + "',";
insertCmd = insertCmd + "" + CSC.TotalPreTax + ",";
insertCmd = insertCmd + "" + CSC.Tax + ",";
insertCmd = insertCmd + "" + CSC.TotalAfterTax + ",";
insertCmd = insertCmd + "'" + CSC.ManufacturerID + "',";
insertCmd = insertCmd + "" + CSC.BusinessTaxStockTaxRate + ",";
insertCmd = insertCmd + "" + CSC.AmountPaid + ",";
insertCmd = insertCmd + "" + CSC.UnpaidAmount + ",";
insertCmd = insertCmd + "'" + CSC.StockStaff + "',";
insertCmd = insertCmd + "'" + CSC.PaymentType + "',";
insertCmd = insertCmd + "'" + CSC.Notes + "'";
insertCmd = insertCmd + ")";
transaction = conn.BeginTransaction("MyInsertTransaction");
try
{
cmd = conn.CreateCommand(); // new SqlCommand(insertCmd, conn);
cmd.CommandText = insertCmd;
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
DBClass.DBClassItemsInfo DBCItemsInfo = new DBClassItemsInfo();
for (int i = 0; i < CSC.StockItems.Length; i++)
{
insertCmd2 = "Insert Into " + TableName2 + " (StockID ,ItemsID ,NAME ,Quantity ,ItemsUnit ," +
"CostPrice ,Totals ,Notes";
insertCmd2 = insertCmd2 + ") Values(";
insertCmd2 = insertCmd2 + "'" + CSC.StockID + "',";
insertCmd2 = insertCmd2 + "'" + CSC.StockItems[i].ItemsID + "',";
insertCmd2 = insertCmd2 + "'" + CSC.StockItems[i].NAME + "',";
insertCmd2 = insertCmd2 + "" + CSC.StockItems[i].Quantity + ",";
insertCmd2 = insertCmd2 + "'" + CSC.StockItems[i].ItemsUnit + "',";
insertCmd2 = insertCmd2 + "" + CSC.StockItems[i].Price + ",";
insertCmd2 = insertCmd2 + "" + CSC.StockItems[i].Totals + ",";
insertCmd2 = insertCmd2 + "'" + CSC.StockItems[i].Notes + "'";
insertCmd2 = insertCmd2 + ")";
cmd.CommandText = insertCmd2; //new SqlCommand(insertCmd2, conn);
cmd.ExecuteNonQuery();
DBCItemsInfo.UpdateInventory(CSC.StockItems[i].ItemsID, CSC.StockItems[i].Quantity); //更新商品庫存量
}
transaction.Commit(); //try to Commit above sql command
conn.Close();
return(true);
}
catch (Exception ex)
{
try
{
transaction.Rollback();
}
catch (Exception ex2)
{
errorMsg = ex2.Message;
}
errorMsg = ex.Message;
conn.Close();
return(false);
}
}
/// <summary>
/// 更新一筆資料
/// </summary>
/// <param name="CSC">傳入存放更新資料的SIS.Configuration.ClsStockConfig</param>
/// <param name="OLDItems">傳入更新前的商品集合</param>
/// <returns></returns>
public bool Update(SIS.Configuration.ClsStockConfig CSC, SIS.Configuration.Items[] OLDItems)
{
InitDB();
string updateCmd, updateCmd2;
updateCmd = "UPDATE " + TableName + " SET ";
updateCmd = updateCmd + " StockID='" + CSC.StockID + "',";
updateCmd = updateCmd + " StockDate='" + CSC.StockDate + "',";
updateCmd = updateCmd + " TotalPreTax=" + CSC.TotalPreTax + ",";
updateCmd = updateCmd + " Tax=" + CSC.Tax + ",";
updateCmd = updateCmd + " TotalAfterTax=" + CSC.TotalAfterTax + ",";
updateCmd = updateCmd + " ManufacturerID='" + CSC.ManufacturerID + "',";
updateCmd = updateCmd + " BusinessTaxStockTaxRate=" + CSC.BusinessTaxStockTaxRate + ",";
updateCmd = updateCmd + " AmountPaid=" + CSC.AmountPaid + ",";
updateCmd = updateCmd + " UnpaidAmount=" + CSC.UnpaidAmount + ",";
updateCmd = updateCmd + " StockStaff='" + CSC.StockStaff + "',";
updateCmd = updateCmd + " PaymentType='" + CSC.PaymentType + "',";
updateCmd = updateCmd + " Notes='" + CSC.Notes + "'";
updateCmd = updateCmd + " WHERE StockID='" + CSC.StockID + "'";
transaction = conn.BeginTransaction("MyUpdateTransaction");
try
{
cmd = conn.CreateCommand(); // new SqlCommand(updateCmd, conn);
cmd.CommandText = updateCmd;
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
DBClass.DBClassItemsInfo DBCItemsInfo = new DBClassItemsInfo();
for (int i = 0; i < CSC.StockItems.Length; i++)
{
updateCmd2 = "UPDATE " + TableName2 + " SET ";
updateCmd2 = updateCmd2 + " StockID='" + CSC.StockID + "',";
updateCmd2 = updateCmd2 + " ItemsID='" + CSC.StockItems[i].ItemsID + "',";
updateCmd2 = updateCmd2 + " NAME='" + CSC.StockItems[i].NAME + "',";
updateCmd2 = updateCmd2 + " Quantity=" + CSC.StockItems[i].Quantity + ",";
updateCmd2 = updateCmd2 + " ItemsUnit='" + CSC.StockItems[i].ItemsUnit + "',";
updateCmd2 = updateCmd2 + " CostPrice=" + CSC.StockItems[i].Price + ",";
updateCmd2 = updateCmd2 + " Totals=" + CSC.StockItems[i].Totals + ",";
updateCmd2 = updateCmd2 + " Notes='" + CSC.StockItems[i].Notes + "'";
updateCmd2 = updateCmd2 + " WHERE StockID='" + CSC.StockID + "' And ItemsID='" + CSC.StockItems[i].ItemsID + "'";
cmd.CommandText = updateCmd2;
cmd.ExecuteNonQuery();
int nowQuantity = OLDItems[i].Quantity;
if (nowQuantity < CSC.StockItems[i].Quantity)
{
DBCItemsInfo.UpdateInventory(CSC.StockItems[i].ItemsID, (CSC.StockItems[i].Quantity - nowQuantity)); //更新商品庫存量
}
else if (nowQuantity > CSC.StockItems[i].Quantity)
{
DBCItemsInfo.UpdateInventory(CSC.StockItems[i].ItemsID, (CSC.StockItems[i].Quantity - nowQuantity)); //更新商品庫存量
}
else if (nowQuantity == CSC.StockItems[i].Quantity)
{
//數量一樣不用更新庫存
}
}
transaction.Commit(); //try to Commit above sql command
conn.Close();
return(true);
}
catch (Exception ex)
{
try
{
transaction.Rollback();
}
catch (Exception ex2)
{
errorMsg = ex2.Message;
return(false);
}
errorMsg = ex.Message;
conn.Close();
return(false);
}
}
