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