public List <clsSales> getSales(DateTime from, DateTime to)
{
List <clsSales> list = new List <clsSales>();
clsSales oSales;
clsAccountUser oEmployee;
string sQuery = "SELECT idSalesControl, SalesControl.SCReceiptCode, Employee.idEmployee, Employee.EMPLastName, Employee.EMPFirstName, Employee.EMPMIddleName, " +
" SalesControl.SCCustFullName, SalesControl.SCCustAddress, SalesControl.SCCustContactNo, SalesControl.SCDateOfPurchase, SCDateCreated, SCStatus " +
" FROM SalesControl INNER JOIN Employee on Employee.idEmployee = SalesControl.idEmployee WHERE SalesControl.SCDateOfPurchase >= @fromdate " +
"AND SalesControl.SCDateOfPurchase <= @todate";
using (SqlConnection oConnection = new SqlConnection(General.connectionString()))
{
try
{
oConnection.Open();
using (SqlCommand oCommand = new SqlCommand(sQuery, oConnection))
{
oCommand.Parameters.AddWithValue("@fromdate", from);
oCommand.Parameters.AddWithValue("@todate", to);
using (SqlDataReader oReader = oCommand.ExecuteReader())
{
if (oReader.HasRows)
{
while (oReader.Read())
{
oSales = new clsSales();
oEmployee = new clsAccountUser();
oSales.ID = oReader.GetInt32(oReader.GetOrdinal("idSalesControl"));
oSales.Code = oReader.GetString(oReader.GetOrdinal("SCReceiptCode"));
oEmployee.ID = oReader.GetInt32(oReader.GetOrdinal("idEmployee"));
oEmployee.Firstname = oReader.GetString(oReader.GetOrdinal("EMPFirstName"));
oEmployee.Middlename = oReader.GetString(oReader.GetOrdinal("EMPMiddleName"));
oEmployee.Lastname = oReader.GetString(oReader.GetOrdinal("EMPLastName"));
oSales.Employee = oEmployee;
oSales.CustFullName = oReader.GetString(oReader.GetOrdinal("SCCustFullName"));
oSales.CustAddress = oReader.GetString(oReader.GetOrdinal("SCCustAddress"));
oSales.CustContactNo = oReader.GetString(oReader.GetOrdinal("SCCustContactNo"));
oSales.DateOfPurchase = oReader.GetDateTime(oReader.GetOrdinal("SCDateOfPurchase"));
oSales.DateCreated = oReader.GetDateTime(oReader.GetOrdinal("SCDateCreated"));
oSales.Status = oReader.GetBoolean(oReader.GetOrdinal("SCStatus"));
list.Add(oSales);
}
}
}
}
}
catch (Exception ex)
{
General.showMessageBox("Error", ex.Message, MsgTypes.danger, System.Reflection.MethodBase.GetCurrentMethod().Name);
}
}
return(list);
}
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 bool delete(clsSales car)
{
using (SqlConnection oConnection = new SqlConnection(General.connectionString()))
{
oConnection.Open();
try
{
using (SqlCommand oCommand = new SqlCommand("DELETE FROM SalesControl WHERE idSalesControl = @id", oConnection))
{
oCommand.Parameters.AddWithValue("@id", car.ID);
oCommand.ExecuteNonQuery();
return(true);
}
}
catch (Exception ex)
{
General.showMessageBox("Error", ex.Message, MsgTypes.danger, System.Reflection.MethodBase.GetCurrentMethod().Name);
}
}
return(false);
}
