public IEnumerable <Models.MeterHistory> GetMeterHistoryByMeterId(Models.MeterHistory mpsRequest)
{
var sql = "SELECT * FROM MeterHistories mh "
+ "WHERE MIRN = '" + mpsRequest.MIRN + "' "
+ "ORDER BY TransactionDate DESC;";
var meterHistoryResult = _context.MeterHistories.FromSqlRaw(sql).ToList();
List <Models.MeterHistory> mhList = new List <Models.MeterHistory>();
foreach (DbModels.MeterHistory mh in meterHistoryResult)
{
var meterHistoy = helper.ConvertMeterHistoryModel(mh);
mhList.Add(meterHistoy);
}
if (mhList != null)
{
return(mhList);
}
else
{
return(null);
}
}
public IActionResult GetMeterByPayRollIDAndStatus(ViewByInstallerMeterRequest mpsRequest)
{
// Getting Payroll ID and Meter Status
var viewModel = new Models.MeterHistory();
viewModel.PayRollID = mpsRequest.PayRollID;
viewModel.MeterStatus = mpsRequest.MeterStatus;
// Get meter id, payroll from meterhistory table
var result = _repo.GetMeterByUserAndStatus(viewModel);
if (result.Item1 == false)
{
ModelState.AddModelError("Error", result.Item2);
return(NotFound());
}
var dbModelMeterList = result.Item3;
ArrayList vMeter = new ArrayList();
foreach (DbModels.Meter dbMeter in dbModelMeterList)
{
var m = helper.ConvertMeterModel(dbMeter);
vMeter.Add(m);
}
return(Ok(vMeter));
}
// converting dbModel meterHistory into view model meter History
public Models.MeterHistory ConvertMeterHistoryModel(DbModels.MeterHistory dbMeterHistory)
{
var vmhMeter = new Models.MeterHistory()
{
MeterHistoryID = Convert.ToString(dbMeterHistory.MeterHistoryID),
MIRN = dbMeterHistory.MIRN,
PayRollID = dbMeterHistory.PayRollID,
MeterStatus = ConvertViewModelMeterStatus(dbMeterHistory.MeterStatus),
Location = dbMeterHistory.Location,
TransfereeID = dbMeterHistory.TransfereeID,
TransactionDate = dbMeterHistory.TransactionDate,
Comment = dbMeterHistory.Comment
};
return(vmhMeter);
}
//Get Meter details by passing MeterID, PayRollId and MeterStatus
public Tuple <bool, string, ArrayList> GetMeterByUserAndStatus(Models.MeterHistory mpsRequest)
{
// Store meter in array
ArrayList metersList = new ArrayList();
var result = Tuple.Create(true, "", metersList);
DbModels.MeterStatus dbmStatus;
var validMeterStatus = validater.ValidateMeterStatus(mpsRequest.MeterStatus);
if (validMeterStatus == true)
{
dbmStatus = helper.ConvertMeterStatus(mpsRequest.MeterStatus);
// validating PayRoll ID
var user = _urepo.Get(mpsRequest.PayRollID);
if (user == null)
{
return(Tuple.Create(false, "PayRollID " + mpsRequest.PayRollID + " is not found", metersList));
}
// Find meter ID using payroll id and meter status from meter history table
var meters = _context.MeterHistories.Where(x => x.PayRollID.Contains(mpsRequest.PayRollID) && (x.MeterStatus == dbmStatus)).ToList();
foreach (DbModels.MeterHistory meter in meters)
{
// Find the meter record from meter table using meterid
// var m = _context.Meters.Where(x => x.MIRN.Equals(meter.MIRN));
// _context.Installations.FirstOrDefault(x => x.InstallationID == id);
DbModels.Meter meterDb = _context.Meters.FirstOrDefault(x => x.MIRN == meter.MIRN && x.MeterStatus == dbmStatus);
if (meterDb != null)
{
metersList.Add(meterDb);
}
}
return(Tuple.Create(true, "Meters List", metersList));
}
else
{
return(Tuple.Create(false, "Invalid meter status: " + mpsRequest.MeterStatus, metersList));
}
}
public IEnumerable <Models.MeterHistoryMeters> GetMeterHistoryByUserId(Models.MeterHistory mpsRequest)
{
// var dbmStatus = helper.ConvertMeterStatus(mpsRequest.MeterStatus);
var meterStatus = 0;
switch (mpsRequest.MeterStatus)
{
case Models.MeterStatus.Inhouse:
meterStatus = 1;
break;
case Models.MeterStatus.Install:
meterStatus = 5;
break;
case Models.MeterStatus.Pickup:
meterStatus = 2;
break;
case Models.MeterStatus.Return:
meterStatus = 3;
break;
case Models.MeterStatus.Transfer:
meterStatus = 4;
break;
default:
meterStatus = 0;
break;
}
var sql = "select h.*,m.MeterType, m.MeterCondition, m.ExpriyDate from MeterHistories h "
+ "JOIN Meters m ON m.MIRN = h.MIRN "
+ "where PayRollID = '" + mpsRequest.PayRollID + "' "
+ "AND MeterHistoryID = (select MAX(mh.MeterHistoryID) from MeterHistories mh where mh.MIRN = h.MIRN) "
+ "AND h.MeterStatus = " + meterStatus + ";";
var meterHistoryResult = _context.MeterHistories.FromSqlRaw(sql).ToList();
List <Models.MeterHistoryMeters> mhList = new List <Models.MeterHistoryMeters>();
foreach (DbModels.MeterHistory mh in meterHistoryResult)
{
Models.MeterHistoryMeters meterHistoyMeter = new MeterHistoryMeters();
var meter = _mrepo.Get(mh.MIRN);
meterHistoyMeter.MeterHistory = helper.ConvertMeterHistoryModel(mh);
meterHistoyMeter.Meter = helper.ConvertMeterModel(meter);
mhList.Add(meterHistoyMeter);
}
if (mhList != null)
{
return(mhList);
}
else
{
return(null);
}
}