/// <summary>
/// 刪除資料庫中的相關資料
/// </summary>
private void RunDeleteData(SIS.Configuration.ClsRMAConfig OldCRC)
{
try
{
My.MyDatabase MyDb = new My.MyDatabase();
SIS.Configuration.ClsRMAConfig CRC = new Configuration.ClsRMAConfig();
CRC.RMAID = txtRMAID.Text;
SIS.DBClass.DBClassRMAMaster DBCRM = new DBClass.DBClassRMAMaster();
if (MyDb.AuthPK(CRC.RMAID, "RMAID", "RMAMaster") == true)
{
if (DBCRM.DeleteMasterDetailsData(CRC.RMAID, OldCRC))
{
MessageBox.Show("刪除[" + CRC.RMAID +
"]退貨單資料成功", "刪除退貨單資料", MessageBoxButtons.OK, MessageBoxIcon.Information);
LoadDefaultValue();
}
else
{
MessageBox.Show("刪除[" + CRC.RMAID +
"]退貨單資料失敗", "刪除退貨單資料", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
else
{
MessageBox.Show("對不起，資料庫不存在[ " + CRC.RMAID +
" ]退貨單資料!!(資料不存在)", "資料刪除");
}
}
catch (Exception ex)
{
MessageBox.Show("錯誤訊息:" + ex.Message.ToString(), "發生例外");
}
}
//刪除
private void tsbDelete_Click(object sender, EventArgs e)
{
string Msg = "是否要進行退貨單[" + txtRMAID.Text + "]刪除動作?\r\n";
DialogResult DR;
DR = MessageBox.Show(Msg, "刪除退貨單", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
if (DR == DialogResult.Yes)
{
if (CheckField(SIS.Configuration.CheckFieldType.Delete))
{
SIS.DBClass.DBClassRMAMaster DBCSM = new DBClass.DBClassRMAMaster();
SIS.Configuration.ClsRMAConfig CRC = new SIS.Configuration.ClsRMAConfig();
bool result = DBCSM.QueryData(txtRMAID.Text, CRC);
if (result == false)
{
MessageBox.Show("對不起，資料庫不存在[ " + txtRMAID.Text +
" ]出貨單資料!!(資料不存在)", "資料刪除");
return;
}
RunDeleteData(CRC);
}
}
else
{
MessageBox.Show("取消退貨單刪除動作!!", "刪除退貨單");
}
}
/// <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>
/// <param name="RMAID">傳入退貨單編號</param>
/// <param name="CRC">傳入存放查詢結果資料的SIS.Configuration.ClsRMAConfig CRC</param>
/// <returns></returns>
public bool QueryData(string RMAID, SIS.Configuration.ClsRMAConfig CRC)
{
InitDB();
string selectCmd;
selectCmd = "Select * From " + TableName + " Where RMAID='" + RMAID + "'";
try
{
cmd = new SqlCommand(selectCmd, conn);
dr = cmd.ExecuteReader();
if (dr.Read())
{
CRC.RMAID = RMAID;
CRC.RMADate = dr["RMADate"].ToString();
CRC.RMAType = dr["RMAType"].ToString();
CRC.TotalPreTax = int.Parse(dr["TotalPreTax"].ToString());
CRC.Tax = int.Parse(dr["Tax"].ToString());
CRC.TotalAfterTax = int.Parse(dr["TotalAfterTax"].ToString());
CRC.StockIDOrShipID = dr["StockIDOrShipID"].ToString();
CRC.BusinessTax = int.Parse(dr["BusinessTax"].ToString());
CRC.AmountPaid = int.Parse(dr["AmountPaid"].ToString());
CRC.UnpaidAmount = int.Parse(dr["UnpaidAmount"].ToString());
CRC.RMAAmount = int.Parse(dr["RMAAmount"].ToString());
CRC.Staff = dr["Staff"].ToString();
CRC.PaymentType = dr["PaymentType"].ToString();
CRC.Notes = dr["Notes"].ToString();
conn.Close();
SIS.DBClass.DBClassRMADetails DBRD = new DBClassRMADetails();
CRC.RMAItems = DBRD.QueryData(RMAID);
if (CRC.RMAItems == null)
{
return(false);
}
else
{
return(true);
}
}
else
{
conn.Close();
return(false);
}
}
catch (Exception ex)
{
errorMsg = ex.Message;
conn.Close();
return(false);
}
}
/// <summary>
/// 新增一筆資料
/// </summary>
/// <param name="CRC">傳入存放更新資料的SIS.Configuration.ClsRMAConfig CRC</param>
/// <returns></returns>
public bool InsertData(SIS.Configuration.ClsRMAConfig CRC)
{
InitDB();
string insertCmd, insertCmd2;
insertCmd = "Insert Into " + TableName + " (RMAID,RMADate,RMAType ,TotalPreTax, Tax,TotalAfterTax,StockIDOrShipID ,BusinessTax ," +
"AmountPaid ,UnpaidAmount , RMAAmount , Staff , PaymentType ,Notes";
insertCmd = insertCmd + ") Values(";
insertCmd = insertCmd + "'" + CRC.RMAID + "',";
insertCmd = insertCmd + "'" + CRC.RMADate + "',";
insertCmd = insertCmd + "'" + CRC.RMAType + "',";
insertCmd = insertCmd + "" + CRC.TotalPreTax + ",";
insertCmd = insertCmd + "" + CRC.Tax + ",";
insertCmd = insertCmd + "" + CRC.TotalAfterTax + ",";
insertCmd = insertCmd + "'" + CRC.StockIDOrShipID + "',";
insertCmd = insertCmd + "" + CRC.BusinessTax + ",";
insertCmd = insertCmd + "" + CRC.AmountPaid + ",";
insertCmd = insertCmd + "" + CRC.UnpaidAmount + ",";
insertCmd = insertCmd + "" + CRC.RMAAmount + ",";
insertCmd = insertCmd + "'" + CRC.Staff + "',";
insertCmd = insertCmd + "'" + CRC.PaymentType + "',";
insertCmd = insertCmd + "'" + CRC.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 < CRC.RMAItems.Length; i++)
{
insertCmd2 = "Insert Into " + TableName2 + " (RMAID ,ItemsID ,NAME ,Quantity ,ItemsUnit ," +
"Price ,Totals ,Notes";
insertCmd2 = insertCmd2 + ") Values(";
insertCmd2 = insertCmd2 + "'" + CRC.RMAID + "',";
insertCmd2 = insertCmd2 + "'" + CRC.RMAItems[i].ItemsID + "',";
insertCmd2 = insertCmd2 + "'" + CRC.RMAItems[i].NAME + "',";
insertCmd2 = insertCmd2 + "" + CRC.RMAItems[i].Quantity + ",";
insertCmd2 = insertCmd2 + "'" + CRC.RMAItems[i].ItemsUnit + "',";
insertCmd2 = insertCmd2 + "" + CRC.RMAItems[i].Price + ",";
insertCmd2 = insertCmd2 + "" + CRC.RMAItems[i].Totals + ",";
insertCmd2 = insertCmd2 + "'" + CRC.RMAItems[i].Notes + "'";
insertCmd2 = insertCmd2 + ")";
cmd.CommandText = insertCmd2;//new SqlCommand(insertCmd2, conn);
cmd.ExecuteNonQuery();
if (CRC.RMAType == "Customer")
{
DBCItemsInfo.UpdateInventory(CRC.RMAItems[i].ItemsID, (CRC.RMAItems[i].Quantity)); //更新商品庫存量
}
else
{
DBCItemsInfo.UpdateInventory(CRC.RMAItems[i].ItemsID, -(CRC.RMAItems[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="CRC">傳入存放更新資料的SIS.Configuration.ClsRMAConfig</param>
/// <param name="OLDItems">傳入更新前的商品集合</param>
/// <returns></returns>
public bool Update(SIS.Configuration.ClsRMAConfig CRC, SIS.Configuration.Items[] OLDItems)
{
InitDB();
string updateCmd, updateCmd2;
updateCmd = "UPDATE " + TableName + " SET ";
updateCmd = updateCmd + " RMAID='" + CRC.RMAID + "',";
updateCmd = updateCmd + " RMADate='" + CRC.RMADate + "',";
updateCmd = updateCmd + " RMAType='" + CRC.RMAType + "',";
updateCmd = updateCmd + " TotalPreTax=" + CRC.TotalPreTax + ",";
updateCmd = updateCmd + " Tax=" + CRC.Tax + ",";
updateCmd = updateCmd + " TotalAfterTax=" + CRC.TotalAfterTax + ",";
updateCmd = updateCmd + " StockIDOrShipID='" + CRC.StockIDOrShipID + "',";
updateCmd = updateCmd + " BusinessTax=" + CRC.BusinessTax + ",";
updateCmd = updateCmd + " AmountPaid=" + CRC.AmountPaid + ",";
updateCmd = updateCmd + " UnpaidAmount=" + CRC.UnpaidAmount + ",";
updateCmd = updateCmd + " RMAAmount=" + CRC.RMAAmount + ",";
updateCmd = updateCmd + " Staff='" + CRC.Staff + "',";
updateCmd = updateCmd + " PaymentType='" + CRC.PaymentType + "',";
updateCmd = updateCmd + " Notes='" + CRC.Notes + "'";
updateCmd = updateCmd + " WHERE RMAID='" + CRC.RMAID + "'";
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();
DBClass.DBClassRMADetails DBCRD = new DBClassRMADetails();
for (int i = 0; i < CRC.RMAItems.Length; i++)
{
updateCmd2 = "UPDATE " + TableName2 + " SET ";
updateCmd2 = updateCmd2 + " RMAID='" + CRC.RMAID + "',";
updateCmd2 = updateCmd2 + " ItemsID='" + CRC.RMAItems[i].ItemsID + "',";
updateCmd2 = updateCmd2 + " NAME='" + CRC.RMAItems[i].NAME + "',";
updateCmd2 = updateCmd2 + " Quantity=" + CRC.RMAItems[i].Quantity + ",";
updateCmd2 = updateCmd2 + " ItemsUnit='" + CRC.RMAItems[i].ItemsUnit + "',";
updateCmd2 = updateCmd2 + " Price=" + CRC.RMAItems[i].Price + ",";
updateCmd2 = updateCmd2 + " Totals=" + CRC.RMAItems[i].Totals + ",";
updateCmd2 = updateCmd2 + " Notes='" + CRC.RMAItems[i].Notes + "'";
updateCmd2 = updateCmd2 + " WHERE RMAID='" + CRC.RMAID + "' And ItemsID='" + CRC.RMAItems[i].ItemsID + "'";
cmd.CommandText = updateCmd2;
cmd.ExecuteNonQuery();
int nowQuantity = OLDItems[i].Quantity;
if (CRC.RMAType == "Customer")
{
if (nowQuantity < CRC.RMAItems[i].Quantity)
{
DBCItemsInfo.UpdateInventory(CRC.RMAItems[i].ItemsID, (CRC.RMAItems[i].Quantity - nowQuantity)); //更新商品庫存量
}
else if (nowQuantity > CRC.RMAItems[i].Quantity)
{
DBCItemsInfo.UpdateInventory(CRC.RMAItems[i].ItemsID, (CRC.RMAItems[i].Quantity - nowQuantity)); //更新商品庫存量
}
else if (nowQuantity == CRC.RMAItems[i].Quantity)
{
//數量一樣不用更新庫存
}
}
else
{
if (nowQuantity < CRC.RMAItems[i].Quantity)
{
DBCItemsInfo.UpdateInventory(CRC.RMAItems[i].ItemsID, -(CRC.RMAItems[i].Quantity - nowQuantity)); //更新商品庫存量
}
else if (nowQuantity > CRC.RMAItems[i].Quantity)
{
DBCItemsInfo.UpdateInventory(CRC.RMAItems[i].ItemsID, -(CRC.RMAItems[i].Quantity - nowQuantity)); //更新商品庫存量
}
else if (nowQuantity == CRC.RMAItems[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);
}
}
