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