/// <summary> /// Select Vehicle by VehicleID /// </summary> /// <param name="vehicleID"></param> /// <returns></returns> public VehicleInfo SelectByID(int vehicleID) { var vehicleInfo = new VehicleInfo(); try { var parameters = new SqlParameter[1]; parameters[0] = new SqlParameter("@VehicleID", vehicleID); var dataTable = _dbConnection.ExecuteSelectQuery(ConstantInfo.SP_SELECTVEHICLEBYID, parameters); if (dataTable.Rows.Count == 1) { vehicleInfo.CreateFrom(dataTable.Rows[0]); } return vehicleInfo; } catch (Exception ex) { throw ex; } }
public List<VehicleInfo> GetExportingVehicles(int mode, int declarationID, string search) { var result = new List<VehicleInfo>(); VehicleInfo vehicleInfo; try { //var sqlCommand = // "SELECT TOP(1) * FROM tblVehicle AS a WHERE a.PlateNumber='" + search + "' AND a.IsExport = 1 AND a.IsImport = 0 AND a.PlateNumber NOT IN ( SELECT a.PlateNumber FROM dbo.tblVehicle AS a WHERE a.DeclarationID = " + declarationID + " ) ORDER BY a.ExportDate DESC"; var sqlCommand = string.Empty; if (mode == 1) sqlCommand = "SELECT * FROM tblVehicle AS a WHERE a.PlateNumber LIKE '%" + search + "%' AND a.IsExport = 1 AND a.IsImport = 0 AND a.PlateNumber NOT IN ( SELECT a.PlateNumber FROM dbo.tblVehicle AS a WHERE a.DeclarationID = " + declarationID + " ) ORDER BY a.ExportDate DESC"; else if(mode==2) sqlCommand = "SELECT * FROM tblVehicle AS a WHERE a.PlateNumber LIKE '%" + search + "%' AND a.IsExport = 1 AND a.IsImport = 1 AND IsGoodsImported = 0 AND HasGoodsImported = 1 AND a.PlateNumber NOT IN ( SELECT a.PlateNumber FROM dbo.tblVehicle AS a WHERE a.DeclarationID = " + declarationID + " ) ORDER BY a.ExportDate DESC"; //var sqlCommand = "SELECT * FROM tblVehicle WHERE IsExport=1 AND IsImport=0 AND PlateNumber = '" + search + "' AND NOT DeclarationID =" + declarationID; //var sqlCommand = string.Empty; //if (search.Equals("")) //{ // if (mode == 1) // sqlCommand = "SELECT * FROM tblVehicle WHERE IsExport=1 AND IsImport=0 AND NOT DeclarationID =" + declarationID; // else if (mode == 2) // sqlCommand = "SELECT * FROM tblVehicle WHERE IsExport=1 AND IsImport=1 AND ImportStatus ='Nhập cảnh có hàng' AND NOT DeclarationID =" + declarationID; // else if (mode == 3) //Vehicle is exported // sqlCommand = "SELECT * FROM tblVehicle WHERE IsExport=1 AND NOT DeclarationID =" + declarationID; //} //else //{ // if (mode == 1) // sqlCommand = "SELECT * FROM tblVehicle WHERE IsExport=1 AND IsImport=0 AND PlateNumber like '%" + search + "%' AND NOT DeclarationID =" + declarationID; // else if (mode == 2) // sqlCommand = "SELECT * FROM tblVehicle WHERE IsExport=1 AND IsImport=1 AND ImportStatus ='Nhập cảnh có hàng' AND PlateNumber like '%" + search + "%' AND NOT DeclarationID =" + declarationID; // else if (mode == 3) //Vehicle is exported // sqlCommand = "SELECT * FROM tblVehicle WHERE IsExport=1 AND PlateNumber like '%" + search + "%' AND NOT DeclarationID =" + declarationID; //} var dataTable = _dbConnection.ExecuteSelectCommandText(sqlCommand); foreach (DataRow dr in dataTable.Rows) { vehicleInfo = new VehicleInfo(); vehicleInfo.CreateFrom(dr); result.Add(vehicleInfo); } return result; } catch (Exception ex) { throw; } }
/// <summary> /// Get Vehicle by DeclarationID /// </summary> /// <param name="declarationID">DeclarationID</param> /// <returns>List VehicleInfo objects</returns> public List<VehicleInfo> SelectByDeclarationID(int declarationID) { var result = new List<VehicleInfo>(); VehicleInfo vehicleInfo; try { var parameters = new SqlParameter[1]; parameters[0] = new SqlParameter("@DeclarationID", declarationID); var dataTable = _dbConnection.ExecuteSelectQuery(ConstantInfo.SP_SELECTVEHICLEBYDECLARATIONID, parameters); foreach (DataRow dr in dataTable.Rows) { vehicleInfo = new VehicleInfo(); vehicleInfo.CreateFrom(dr); result.Add(vehicleInfo); } return result; } catch (Exception) { throw; } }