public List <AssetOpnameTransactionViewModel> GetAssetLatestLocationByLocationID(int LocationID, DateTime Opnamedate)
{
List <AssetOpnameTransactionViewModel> result = new List <AssetOpnameTransactionViewModel>();
using (AstonContext dbContext = new AstonContext())
{
dbContext.Database.OpenConnection();
DbCommand cmd = dbContext.Database.GetDbConnection().CreateCommand();
cmd.CommandText = "dbo.sp_AssetLocationLatest";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@LocationID", SqlDbType.Int)
{
Value = LocationID
});
cmd.Parameters.Add(new SqlParameter("@OpnameDate", SqlDbType.DateTime)
{
Value = Opnamedate
});
using (var reader = cmd.ExecuteReader())
{
var assetlocationlist = dbContext.DataReaderMapToList <AssetTransactionViewModel>(reader);
foreach (var assetlocation in assetlocationlist)
{
result.Add(new AssetOpnameTransactionViewModel()
{
AssetLatest = assetlocation
});
}
cmd.Connection.Close();
}
}
return(result);
}
public List <AssetLocationViewModel> Pagination_AssetLocation_SP(int Skip)
{
var result = new List <AssetLocationViewModel>();
var obj = new AssetLocationViewModel();
using (AstonContext dbContext = new AstonContext())
{
dbContext.Database.OpenConnection();
DbCommand cmd = dbContext.Database.GetDbConnection().CreateCommand();
cmd.CommandText = "dbo.sp_AssetLocation_Pagination";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@Skip", SqlDbType.Int)
{
Value = Skip
});
using (var reader = cmd.ExecuteReader())
{
var assetlocationlist = dbContext.DataReaderMapToList <AssetLocationPagination>(reader);
foreach (var assetlocation in assetlocationlist)
{
result.Add(new AssetLocationViewModel()
{
AssetLocation = assetlocation
});
}
cmd.Connection.Close();
}
}
return(result);
}
public List <HistoryViewModel> AssetHistory_SP(int AssetID, int Skip)
{
var result = new List <HistoryViewModel>();
using (AstonContext dbContext = new AstonContext())
{
dbContext.Database.OpenConnection();
DbCommand cmd = dbContext.Database.GetDbConnection().CreateCommand();
cmd.CommandText = "dbo.sp_AssetHistory_Pagination";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@AssetID", SqlDbType.Int)
{
Value = AssetID
});
cmd.Parameters.Add(new SqlParameter("@Skip", SqlDbType.Int)
{
Value = Skip
});
using (var reader = cmd.ExecuteReader())
{
//a = reader.<AssetViewModel>():
var historyList = dbContext.DataReaderMapToList <HistoryViewModel>(reader);
foreach (var history in historyList)
{
result.Add(history);
}
cmd.Connection.Close();
}
}
return(result);
}
public List<DepartmentViewModel> GetDepartment_Pagination(int Skip)
{
var result = new List<DepartmentViewModel>();
var obj = new DepartmentViewModel();
using (AstonContext dbContext = new AstonContext())
{
dbContext.Database.OpenConnection();
DbCommand cmd = dbContext.Database.GetDbConnection().CreateCommand();
cmd.CommandText = "dbo.sp_DepartmentPagination";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@Skip", SqlDbType.Int) { Value = Skip });
using (var reader = cmd.ExecuteReader())
{
var list = dbContext.DataReaderMapToList<DepartmentViewModel>(reader);
foreach (var item in list)
{
result.Add(item);
}
cmd.Connection.Close();
}
}
return result;
}
public List <AssetViewModel> SearchAsset_SP(int categorycode, bool?ismovable, string owner, int Skip)
{
var result = new List <AssetViewModel>();
var obj = new AssetViewModel();
using (AstonContext dbContext = new AstonContext())
{
dbContext.Database.OpenConnection();
DbCommand cmd = dbContext.Database.GetDbConnection().CreateCommand();
cmd.CommandText = "dbo.sp_SearchAsset";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@CategoryCD", SqlDbType.Int)
{
Value = categorycode
});
cmd.Parameters.Add(new SqlParameter("@IsMovable", SqlDbType.Bit)
{
Value = ismovable
});
cmd.Parameters.Add(new SqlParameter("@Owner", SqlDbType.NVarChar)
{
Value = owner
});
cmd.Parameters.Add(new SqlParameter("@Skip", SqlDbType.Int)
{
Value = Skip
});
using (var reader = cmd.ExecuteReader())
{
//a = reader.<AssetViewModel>():
var assetlist = dbContext.DataReaderMapToList <AseetSearchResult>(reader);
foreach (var asset in assetlist)
{
result.Add(new AssetViewModel()
{
Asset = asset
});
}
cmd.Connection.Close();
}
}
return(result);
}
public List <MovementRequestViewModel> SearchMovementRequests_SP(int LocationID, int ApprovalStatus, int Skip)
{
var result = new List <MovementRequestViewModel>();
var obj = new MovementRequestViewModel();
using (AstonContext dbContext = new AstonContext())
{
dbContext.Database.OpenConnection();
DbCommand cmd = dbContext.Database.GetDbConnection().CreateCommand();
cmd.CommandText = "dbo.sp_SearchMovementRequest";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@LocationID", SqlDbType.Int)
{
Value = LocationID
});
cmd.Parameters.Add(new SqlParameter("@ApprovalStatus", SqlDbType.Int)
{
Value = ApprovalStatus
});
cmd.Parameters.Add(new SqlParameter("@Skip", SqlDbType.Int)
{
Value = Skip
});
using (var reader = cmd.ExecuteReader())
{
//a = reader.<AssetViewModel>():
var assetlist = dbContext.DataReaderMapToList <MovementRequestSearchResult>(reader);
foreach (var asset in assetlist)
{
result.Add(new MovementRequestViewModel()
{
MovementRequest = asset
});
}
cmd.Connection.Close();
}
}
return(result);
}
public List <LocationViewModel> SearchLocation_SP(int?LocationTypeCD, string Floor, int Skip)
{
AstonContext dbContext = new AstonContext();
var result = new List <LocationViewModel>();
dbContext.Database.OpenConnection();
using (var cmd = dbContext.Database.GetDbConnection().CreateCommand())
{
cmd.CommandText = "dbo.sp_SearchLocation";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@LocationTypeCD", SqlDbType.Int)
{
Value = LocationTypeCD
});
cmd.Parameters.Add(new SqlParameter("@Floor", SqlDbType.NVarChar)
{
Value = Floor
});
cmd.Parameters.Add(new SqlParameter("@Skip", SqlDbType.Int)
{
Value = Skip
});
using (var reader = cmd.ExecuteReader())
{
var locationlist = dbContext.DataReaderMapToList <LocationSearchResult>(reader);
foreach (var location in locationlist)
{
result.Add(new LocationViewModel()
{
Location = location
});
}
cmd.Connection.Close();
}
}
return(result);
}
public List <MismatchReportViewModel> LostAssetReport_SP(int categorycode, bool?ismovable, string owner)
{
var result = new List <MismatchReportViewModel>();
using (AstonContext dbContext = new AstonContext())
{
dbContext.Database.OpenConnection();
DbCommand cmd = dbContext.Database.GetDbConnection().CreateCommand();
cmd.CommandText = "dbo.sp_LostAssetReport";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@CategoryCD", SqlDbType.Int)
{
Value = categorycode
});
cmd.Parameters.Add(new SqlParameter("@IsMovable", SqlDbType.Bit)
{
Value = ismovable
});
cmd.Parameters.Add(new SqlParameter("@Owner", SqlDbType.NVarChar)
{
Value = owner
});
using (var reader = cmd.ExecuteReader())
{
//a = reader.<AssetViewModel>():
var datalist = dbContext.DataReaderMapToList <MismatchReportViewModel>(reader);
foreach (var data in datalist)
{
result.Add(data);
}
cmd.Connection.Close();
}
}
return(result);
}
