/// <summary>
/// Query for Reconciled Package screen in Package Audit Wizard
/// </summary>
/// <param name="connection"></param>
/// <param name="currentLocation"></param>
/// <param name="packageType"></param>
/// <param name="barCodeXml"></param>
/// <returns></returns>
public static List <ReconciledPackage> GetReconciledPackages(string connectionString, DistributionCentre currentLocation, StandardPackageType packageType, List <string> barCodeList)
{
var reconciledPackages = new List <ReconciledPackage>();
using (var ctx = new Entities(connectionString))
{
var receivedPackages = (from p in ctx.Package.Include("CurrentLocation")
join b in barCodeList on p.BarCode equals b
where p.PackageTypeId == packageType.PackageTypeId && (p.CurrentLocationCentreId != currentLocation.CentreId || p.CurrentStatus != PackageStatus.InStock)
select p).ToList();
foreach (var package in receivedPackages)
{
var reconciledPackage = new ReconciledPackage()
{
PackageId = package.PackageId,
BarCode = package.BarCode,
CurrentLocationCentreId = package.CurrentLocationCentreId ?? -1,
CurrentLocationCentreName = package.CurrentLocationCentreId == null ? string.Empty : package.CurrentLocation.Name,
CurrentStatus = package.CurrentStatus,
NewStatus = PackageStatus.InStock
};
reconciledPackages.Add(reconciledPackage);
}
var lostPackages = (from p in ctx.Package.Include("CurrentLocation")
join b in barCodeList on p.BarCode equals b into ps
from b in ps.DefaultIfEmpty()
where p.PackageTypeId == packageType.PackageTypeId && p.CurrentLocationCentreId == currentLocation.CentreId &&
b == null && p.CurrentStatus == PackageStatus.InStock
select p).ToList();
foreach (var package in lostPackages)
{
var reconciledPackage = new ReconciledPackage()
{
PackageId = package.PackageId,
BarCode = package.BarCode,
CurrentLocationCentreId = package.CurrentLocationCentreId.Value,
CurrentLocationCentreName = package.CurrentLocation.Name,
CurrentStatus = package.CurrentStatus,
NewStatus = PackageStatus.Lost
};
reconciledPackages.Add(reconciledPackage);
}
}
return(reconciledPackages);
}
/// <summary>
/// Query for Reconciled Package screen in Package Audit Wizard
/// </summary>
/// <param name="connection"></param>
/// <param name="currentLocation"></param>
/// <param name="packageType"></param>
/// <param name="barCodeXml"></param>
/// <returns></returns>
public static List <ReconciledPackage> GetReconciledPackages(SqlConnection connection, DistributionCentre currentLocation, StandardPackageType packageType, XElement barCodeXml)
{
string query = "SELECT PackageId, BarCode, CurrentLocationCentreId, d.Name as CurrentLocationCentreName, CurrentStatus, 'INSTOCK' AS NewStatus " +
"FROM Package p " +
"INNER JOIN @BarCodeList.nodes('/Root/BarCode') AS Tbl(C) ON p.BarCode = Tbl.C.value('@Text', 'varchar(20)') " +
"LEFT OUTER JOIN DistributionCentre d ON d.CentreId = p.CurrentLocationCentreId " +
"WHERE p.PackageTypeId = @PackageTypeId AND " +
"(CurrentLocationCentreId <> @DistributionCentreId OR CurrentStatus <> 'INSTOCK') " +
"UNION ALL " +
"SELECT PackageId, BarCode, CurrentLocationCentreId, d.Name as CurrentLocationCentreName, CurrentStatus, 'LOST' AS NewStatus " +
"FROM Package p " +
"LEFT OUTER JOIN DistributionCentre d ON d.CentreId = p.CurrentLocationCentreId " +
"LEFT OUTER JOIN @BarCodeList.nodes('/Root/BarCode') AS Tbl(C) ON p.BarCode = Tbl.C.value('@Text', 'varchar(20)') " +
"WHERE Tbl.C.value('@Text', 'varchar(20)') IS NULL AND p.CurrentStatus = 'INSTOCK' AND p.PackageTypeId = @PackageTypeId AND p.CurrentLocationCentreId = @DistributionCentreId " +
"ORDER BY PackageId ";
var packageList = new List <ReconciledPackage>();
var cmd = new SqlCommand(query);
cmd.Connection = connection;
cmd.Parameters.Add("@BarCodeList", SqlDbType.Xml).Value = barCodeXml.ToString();
cmd.Parameters.Add("@DistributionCentreId", SqlDbType.Int).Value = currentLocation.CentreId;
cmd.Parameters.Add("@PackageTypeId", SqlDbType.Int).Value = packageType.PackageTypeId;
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default))
{
while (reader.Read())
{
var package = new ReconciledPackage();
if (reader["PackageId"] != DBNull.Value)
{
package.PackageId = Convert.ToInt32(reader["PackageId"]);
}
if (reader["BarCode"] != DBNull.Value)
{
package.BarCode = (string)reader["BarCode"];
}
if (reader["CurrentLocationCentreId"] != DBNull.Value)
{
package.CurrentLocationCentreId = Convert.ToInt32(reader["CurrentLocationCentreId"]);
}
if (reader["CurrentLocationCentreName"] != DBNull.Value)
{
package.CurrentLocationCentreName = (string)reader["CurrentLocationCentreName"];
}
if (reader["CurrentStatus"] != DBNull.Value)
{
package.CurrentStatus = (PackageStatus)Enum.Parse(typeof(PackageStatus), (string)reader["CurrentStatus"], true);
}
if (reader["NewStatus"] != DBNull.Value)
{
package.NewStatus = (PackageStatus)Enum.Parse(typeof(PackageStatus), (string)reader["NewStatus"], true);
}
packageList.Add(package);
}
}
return(packageList);
}
