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