public List <clsSalesData> getSalesData(clsSales salesClass) { List <clsSalesData> list = new List <clsSalesData>(); clsSalesData oSalesData; clsInventory oInventory; clsCar oCar; string sQuery = "SELECT idSalesData, ITrackingCode, Inventory.idInventory, CarModel.CMCode, CarModel.idCarMOdel, CarModel.CMName, Inventory.IDetails, Inventory.IDateCreated, Inventory.IIsSoldDate, " + "Inventory.IStatus FROM Inventory INNER JOIN CarModel ON CarModel.idCarModel = Inventory.idCarModel INNER JOIN SalesData " + "ON Inventory.idInventory = SalesData.idInventory INNER JOIN SalesControl ON SalesControl.idSalesControl = SalesData.idSalesData " + "INNER JOIN Employee ON Employee.idEmployee = SalesControl.idEmployee WHERE Inventory.IIsSold = 'True' AND SalesControl.idSalesControl = @salesid"; using (SqlConnection oConnection = new SqlConnection(General.connectionString())) { try { oConnection.Open(); using (SqlCommand oCommand = new SqlCommand(sQuery, oConnection)) { oCommand.Parameters.AddWithValue("@salesid", salesClass.ID); using (SqlDataReader oReader = oCommand.ExecuteReader()) { if (oReader.HasRows) { while (oReader.Read()) { oSalesData = new clsSalesData(); oInventory = new clsInventory(); oCar = new clsCar(); oSalesData.ID = oReader.GetInt32(oReader.GetOrdinal("idSalesData")); oCar.ID = oReader.GetInt32(oReader.GetOrdinal("idCarMOdel")); oCar.Name = oReader.GetString(oReader.GetOrdinal("CMName")); oInventory.Car = oCar; oInventory.ID = oReader.GetInt32(oReader.GetOrdinal("idInventory")); oInventory.TrackingCode = oReader.GetString(oReader.GetOrdinal("ITrackingCode")); oInventory.Details = oReader.GetString(oReader.GetOrdinal("IDetails")); oInventory.SoldDate = oReader.GetDateTime(oReader.GetOrdinal("IIsSoldDate")); oSalesData.Inventory = oInventory; oSalesData.SalesControl = salescontrol; oSalesData.DateCreated = oReader.GetDateTime(oReader.GetOrdinal("IDateCreated")); oSalesData.Status = oReader.GetBoolean(oReader.GetOrdinal("IStatus")); list.Add(oSalesData); } } } } } catch (Exception ex) { General.showMessageBox("Error", ex.Message, MsgTypes.danger, System.Reflection.MethodBase.GetCurrentMethod().Name); } } return(list); }
public List <clsInventory> getList() { List <clsInventory> list = new List <clsInventory>(); clsInventory inventory; clsCar car; string SQuery = "SELECT idInventory, CarModel.idCarModel, Inventory.ITrackingCode, CarModel.CMCode, CarModel.CMName, IDetails, " + "ISupplier, IIsSold, IIsSoldDate, IDateCreated, IStatus FROM Inventory " + "INNER JOIN CarModel ON CarModel.idCarModel = Inventory.idCarModel WHERE Inventory.IIsSold = 'False' "; using (SqlConnection oConnection = new SqlConnection(General.connectionString())) { oConnection.Open(); try { using (SqlCommand oCommand = new SqlCommand(SQuery, oConnection)) { using (SqlDataReader oReader = oCommand.ExecuteReader()) { if (oReader.HasRows) { while (oReader.Read()) { inventory = new clsInventory(); car = new clsCar(); inventory.ID = oReader.GetInt32(oReader.GetOrdinal("idInventory")); inventory.TrackingCode = oReader.GetString(oReader.GetOrdinal("ITrackingCode")); car.ID = oReader.GetInt32(oReader.GetOrdinal("idCarModel")); car.Code = oReader.GetString(oReader.GetOrdinal("CMCode")); car.Name = oReader.GetString(oReader.GetOrdinal("CMName")); inventory.car = car; inventory.Details = oReader.GetString(oReader.GetOrdinal("IDetails")); inventory.Supplier = oReader.GetString(oReader.GetOrdinal("ISupplier")); inventory.IsSold = oReader.GetBoolean(oReader.GetOrdinal("IIsSold")); inventory.SoldDate = oReader.GetDateTime(oReader.GetOrdinal("IIsSoldDate")); inventory.DateCreated = oReader.GetDateTime(oReader.GetOrdinal("IDateCreated")); inventory.Status = oReader.GetBoolean(oReader.GetOrdinal("IStatus")); list.Add(inventory); } } } } } catch (Exception ex) { General.showMessageBox("Error", ex.Message, MsgTypes.danger); } } return(list); }
public bool delete(clsInventory inventory) { using (SqlConnection oConnection = new SqlConnection(General.connectionString())) { oConnection.Open(); try { using (SqlCommand oCommand = new SqlCommand("DELETE FROM Inventory WHERE idInventory = @idInventory", oConnection)) { oCommand.Parameters.AddWithValue("@idInventory", inventory.ID); oCommand.ExecuteNonQuery(); return(true); } } catch (Exception ex) { General.showMessageBox("Error", ex.Message, MsgTypes.danger, ex.Source); } } return(false); }
//public bool save(clsInventory oInventory, clsSales oSales) // @03271901 : temporary public bool save(clsInventory oInventory) { string sqlQuery = "INSERT INTO SalesData(idSalesControl, idInventory, SDDateCreated, SDStatus) " + "VALUES(@idsalescontrol,@inventory, GETDATE(), 1)"; using (SqlConnection connection = new SqlConnection(General.connectionString())) { connection.Open(); try { using (SqlCommand oCommand = new SqlCommand(sqlQuery, connection)) { // @03271901 : temporary oCommand.Parameters.AddWithValue("@idsalescontrol", this.idSalesControl); //oCommand.Parameters.AddWithValue("@idsalescontrol", oSales.ID); oCommand.Parameters.AddWithValue("@inventory", oInventory.ID); oCommand.ExecuteNonQuery(); } using (SqlCommand oCommand = new SqlCommand("UPDATE Inventory SET IIsSold = 'true', IIsSoldDate = @solddate WHERE idInventory = @idinventory", connection)) { oCommand.Parameters.AddWithValue("@idinventory", oInventory.ID); oCommand.Parameters.AddWithValue("@solddate", this.SalesControlsoldDate); oCommand.ExecuteNonQuery(); return(true); } } catch (Exception ex) { General.showMessageBox("Error", ex.Message, MsgTypes.danger, ex.Source); } } return(false); }