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