public IEnumerable <MaterialPlanLineViewModel> GetMaterialPlanSummaryFromProcedure(MaterialPlanLineListViewModel vm, string ConnectionString, string ProcName)
{
var prodorderlinelist = vm.MaterialPlanLineViewModel.Where(m => m.Qty > 0).Select(m => new { m.SaleOrderLineId, m.ProductId, m.Qty });
int MaterialPlanHeaderId = 0;
if (vm.MaterialPlanLineViewModel.Count > 0)
{
MaterialPlanHeaderId = vm.MaterialPlanLineViewModel[0].MaterialPlanHeaderId;
}
DataTable dataTable = new DataTable();
dataTable.Columns.Add("SaleOrderLineId");
dataTable.Columns.Add("ProductId");
dataTable.Columns.Add("Qty");
foreach (var item in prodorderlinelist)
{
var dr = dataTable.NewRow();
dr["SaleOrderLineId"] = item.SaleOrderLineId;
dr["ProductId"] = item.ProductId;
dr["Qty"] = item.Qty;
dataTable.Rows.Add(dr);
}
DataSet ds = new DataSet();
using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
{
sqlConnection.Open();
using (SqlCommand cmd = new SqlCommand(ProcName))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlConnection;
cmd.Parameters.AddWithValue("@T", dataTable);
cmd.Parameters.AddWithValue("@MaterialPlanHeaderId", vm.MaterialPlanLineViewModel.FirstOrDefault().MaterialPlanHeaderId);
using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
{
adp.Fill(ds);
}
}
}
DataTable dt2 = ds.Tables[0];
List <MaterialPlanLineViewModel> temp = new List <MaterialPlanLineViewModel>();
foreach (DataRow dr in dt2.Rows)
{
MaterialPlanLineViewModel line = new MaterialPlanLineViewModel();
line.ProductId = dr["ProductId"] == System.DBNull.Value ? 0 : Convert.ToInt32(dr["ProductId"].ToString());
line.ProcessId = dr["ProcessId"] == System.DBNull.Value ? null : (int?)Convert.ToInt32(dr["ProcessId"].ToString());
line.Dimension1Id = dr["Dimension1Id"] == System.DBNull.Value ? null : (int?)Convert.ToInt32(dr["Dimension1Id"]);
line.Dimension2Id = dr["Dimension2Id"] == System.DBNull.Value ? null : (int?)Convert.ToInt32(dr["Dimension2Id"]);
line.ProductName = dr["ProductName"] == System.DBNull.Value ? null : dr["ProductName"].ToString();
line.Specification = dr["Specification"] == System.DBNull.Value ? null : dr["Specification"].ToString();
line.UnitName = dr["UnitName"] == System.DBNull.Value ? null : dr["UnitName"].ToString();
line.unitDecimalPlaces = dr["DecimalPlaces"] == System.DBNull.Value ? 0 : Convert.ToInt32(dr["DecimalPlaces"].ToString());
line.RequiredQty = dr["Qty"] == System.DBNull.Value ? 0 : Convert.ToDecimal(dr["Qty"].ToString());
line.Dimension1Name = dr["Dimension1Name"] == System.DBNull.Value ? null : dr["Dimension1Name"].ToString();
line.Dimension2Name = dr["Dimension2Name"] == System.DBNull.Value ? null : dr["Dimension2Name"].ToString();
line.ProcessName = dr["ProcessName"] == System.DBNull.Value ? null : dr["ProcessName"].ToString();
line.ExcessStockQty = 0;
line.MaterialPlanHeaderId = MaterialPlanHeaderId;
line.ProdPlanQty = (dr["PurchProd"].ToString() == "Purchase") ? 0 : Convert.ToDecimal(dr["Qty"].ToString());
line.PurchPlanQty = (dr["PurchProd"].ToString() == "Purchase") ? Convert.ToDecimal(dr["Qty"].ToString()) : 0;
line.GeneratedFor = MaterialPlanConstants.SaleOrder;
temp.Add(line);
}
return(temp);
}
public JsonResult GetSummary(IEnumerable <MaterialPlanForSaleOrderViewModel> selectedRec, int DocTypeId)
{
int SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"];
int DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];
//MaterialPlanSettings Setting = new MaterialPlanSettingsService(_unitOfWork).GetMaterialPlanSettingsForDocument(DocTypeId, DivisionId, SiteId);
var settings = new MaterialPlanSettingsService(_unitOfWork).GetMaterialPlanSettingsForDocument(DocTypeId, DivisionId, SiteId);
var ProductIds = selectedRec.Select(m => m.ProductId).ToArray();
List <MaterialPlanLineViewModel> Line = new List <MaterialPlanLineViewModel>();
if (selectedRec != null)
{
System.Web.HttpContext.Current.Session["BuyerId"] = selectedRec.FirstOrDefault().BuyerId;
}
System.Web.HttpContext.Current.Session["SODyeingPlan"] = selectedRec;
if (settings.SqlProcConsumptionSummary != null)
{
var prodorderlinelist = selectedRec.Where(m => m.Qty > 0).Select(m => new { m.SaleOrderLineId, m.Qty });
DataTable dataTable = new DataTable();
dataTable.Columns.Add("SaleOrderLineId");
dataTable.Columns.Add("ProductId");
dataTable.Columns.Add("Qty");
foreach (var item in prodorderlinelist)
{
var dr = dataTable.NewRow();
dr["SaleOrderLineId"] = item.SaleOrderLineId;
dr["ProductId"] = null;
dr["Qty"] = item.Qty;
dataTable.Rows.Add(dr);
}
DataSet ds = new DataSet();
using (SqlConnection sqlConnection = new SqlConnection((string)System.Web.HttpContext.Current.Session["DefaultConnectionString"]))
{
sqlConnection.Open();
using (SqlCommand cmd = new SqlCommand(settings.SqlProcConsumptionSummary))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlConnection;
cmd.Parameters.AddWithValue("@T", dataTable);
cmd.Parameters.AddWithValue("@MaterialPlanHeaderId", 0);
using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
{
adp.Fill(ds);
}
}
}
DataTable dt2 = ds.Tables[0];
foreach (DataRow dr in dt2.Rows)
{
MaterialPlanLineViewModel line = new MaterialPlanLineViewModel();
line.ProductId = dr["ProductId"] == System.DBNull.Value ? 0 : Convert.ToInt32(dr["ProductId"].ToString());
line.ProcessId = dr["ProcessId"] == System.DBNull.Value ? null : (int?)Convert.ToInt32(dr["ProcessId"].ToString());
line.Dimension1Id = dr["Dimension1Id"] == System.DBNull.Value ? null : (int?)Convert.ToInt32(dr["Dimension1Id"]);
line.Dimension2Id = dr["Dimension2Id"] == System.DBNull.Value ? null : (int?)Convert.ToInt32(dr["Dimension2Id"]);
line.ProductName = dr["ProductName"] == System.DBNull.Value ? null : dr["ProductName"].ToString();
line.Specification = dr["Specification"] == System.DBNull.Value ? null : dr["Specification"].ToString();
line.UnitName = dr["UnitName"] == System.DBNull.Value ? null : dr["UnitName"].ToString();
line.unitDecimalPlaces = dr["DecimalPlaces"] == System.DBNull.Value ? 0 : Convert.ToInt32(dr["DecimalPlaces"].ToString());
line.RequiredQty = dr["Qty"] == System.DBNull.Value ? 0 : Convert.ToDecimal(dr["Qty"].ToString());
line.Dimension1Name = dr["Dimension1Name"] == System.DBNull.Value ? null : dr["Dimension1Name"].ToString();
line.Dimension2Name = dr["Dimension2Name"] == System.DBNull.Value ? null : dr["Dimension2Name"].ToString();
line.ProcessName = dr["ProcessName"] == System.DBNull.Value ? null : dr["ProcessName"].ToString();
line.ExcessStockQty = 0;
line.MaterialPlanHeaderId = 0;
line.ProdPlanQty = (dr["PurchProd"].ToString() == "Purchase") ? 0 : Convert.ToDecimal(dr["Qty"].ToString());
line.PurchPlanQty = (dr["PurchProd"].ToString() == "Purchase") ? Convert.ToDecimal(dr["Qty"].ToString()) : 0;
line.GeneratedFor = MaterialPlanConstants.SaleOrder;
Line.Add(line);
}
}
else
{
//var summary = (from p in db.Product.Where(p => ProductIds.Contains(p.ProductId)).AsEnumerable()
// join t in selectedRec on p.ProductId equals t.ProductId
// where t.Qty > 0
// group t by new { t.ProductId, p.ProductName, t.Specification } into g
// join p1 in db.Product.Where(p => ProductIds.Contains(p.ProductId)).AsEnumerable() on g.Key.ProductId equals p1.ProductId
// join u1 in db.Units on p1.UnitId equals u1.UnitId
// select new
// {
// id = g.Key.ProductId,
// QtySum = g.Sum(m => m.Qty),
// //GroupedItems = g,
// name = g.Key.ProductName,
// unitname = u1.UnitName,
// Specification = g.Key.Specification,
// Fractionunits = u1.DecimalPlaces
// }).ToList();
var summary = (from t in selectedRec
join p in db.Product on t.ProductId equals p.ProductId into ProductTable from ProductTab in ProductTable.DefaultIfEmpty()
join d1 in db.Dimension1 on t.Dimension1Id equals d1.Dimension1Id into Dimension1Table from Dimension1Tab in Dimension1Table.DefaultIfEmpty()
join d2 in db.Dimension2 on t.Dimension2Id equals d2.Dimension2Id into Dimension2Table
from Dimension2Tab in Dimension2Table.DefaultIfEmpty()
where t.Qty > 0
group t by new { t.ProductId, t.Dimension1Id, t.Dimension2Id, t.Specification } into g
join p1 in db.Product.Where(p => ProductIds.Contains(p.ProductId)).AsEnumerable() on g.Key.ProductId equals p1.ProductId
join u1 in db.Units on p1.UnitId equals u1.UnitId
select new
{
id = g.Key.ProductId,
Dimension1Id = g.Key.Dimension1Id,
Dimension2Id = g.Key.Dimension2Id,
QtySum = g.Sum(m => m.Qty),
//GroupedItems = g,
name = g.Max(m => m.ProductName),
unitname = u1.UnitName,
Dimension1Name = g.Max(m => m.Dimension1Name),
Dimension2Name = g.Max(m => m.Dimension2Name),
Specification = g.Key.Specification,
Fractionunits = u1.DecimalPlaces
}).ToList();
int j = 0;
foreach (var item in summary)
{
MaterialPlanLineViewModel planline = new MaterialPlanLineViewModel();
planline.ProductName = item.name;
planline.Dimension1Name = item.Dimension1Name;
planline.Dimension2Name = item.Dimension2Name;
planline.Dimension1Id = item.Dimension1Id;
planline.Dimension2Id = item.Dimension2Id;
planline.RequiredQty = item.QtySum;
planline.ExcessStockQty = 0;
planline.Specification = item.Specification;
planline.MaterialPlanHeaderId = 0;
planline.ProductId = item.id;
planline.ProdPlanQty = item.QtySum;
planline.UnitName = item.unitname;
planline.unitDecimalPlaces = item.Fractionunits;
planline.GeneratedFor = MaterialPlanConstants.SaleOrder;
Line.Add(planline);
j++;
}
}
MaterialPlanSummaryViewModel Summary = new MaterialPlanSummaryViewModel();
//Summary.MaterialPlanSettings = Setting;
Summary.MaterialPlanSettings = Mapper.Map <MaterialPlanSettings, MaterialPlanSettingsViewModel>(settings);
var data = Line.OrderBy(m => m.ProductName).ThenBy(m => m.Dimension1Name).ThenBy(m => m.Dimension2Name)
.Select((m, i) => new
{
Dimension1Id = m.Dimension1Id,
Dimension1Name = m.Dimension1Name,
Dimension2Id = m.Dimension2Id,
Dimension2Name = m.Dimension2Name,
DueDate = m.DueDate,
MaterialPlanHeaderDocNo = m.MaterialPlanHeaderDocNo,
MaterialPlanHeaderId = m.MaterialPlanHeaderId,
MaterialPlanLineId = m.MaterialPlanLineId,
ProcessId = m.ProcessId,
ProcessName = m.ProcessName,
ProdPlanQty = m.ProdPlanQty,
ProductId = m.ProductId,
ProductName = m.ProductName,
PurchPlanQty = m.PurchPlanQty,
Remark = m.Remark,
RequiredQty = m.RequiredQty,
Specification = m.Specification,
StockPlanQty = m.StockPlanQty,
unitDecimalPlaces = m.unitDecimalPlaces,
UnitId = m.UnitId,
UnitName = m.UnitName,
Id = i
})
.ToList();
return(Json(new { success = true, data = data }, JsonRequestBehavior.AllowGet));
}
public IEnumerable <MaterialPlanLineViewModel> GetMaterialPlanSummaryFromProcedure(List <MaterialPlanForProcedureViewModel> vm, string ConnectionString, string ProcName, int MaterialPlanHeaderId)
{
DataTable dataTable = new DataTable();
dataTable.Columns.Add("ProductId");
dataTable.Columns.Add("ProdOrderLineId");
dataTable.Columns.Add("Dimension1Id");
dataTable.Columns.Add("Dimension2Id");
dataTable.Columns.Add("ProcessId");
dataTable.Columns.Add("Qty");
foreach (var item in vm)
{
var dr = dataTable.NewRow();
dr["ProductId"] = item.ProductId;
dr["ProdOrderLineId"] = item.ProdOrderLineId;
dr["Dimension1Id"] = item.Dimension1Id;
dr["Dimension2Id"] = item.Dimension2Id;
dr["ProcessId"] = item.ProcessId;
dr["Qty"] = item.Qty;
dataTable.Rows.Add(dr);
}
DataSet ds = new DataSet();
using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
{
sqlConnection.Open();
using (SqlCommand cmd = new SqlCommand("Web.ProcGetBomForWeavingPlanForYarnSummary"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlConnection;
cmd.Parameters.AddWithValue("@T", dataTable);
using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
{
adp.Fill(ds);
}
}
}
DataTable dt2 = ds.Tables[0];
List <MaterialPlanLineViewModel> temp = new List <MaterialPlanLineViewModel>();
foreach (DataRow dr in dt2.Rows)
{
MaterialPlanLineViewModel line = new MaterialPlanLineViewModel();
line.ProductId = dr["ProductId"] == System.DBNull.Value ? 0 : Convert.ToInt32(dr["ProductId"].ToString());
line.ProcessId = dr["ProcessId"] == System.DBNull.Value ? null : (int?)Convert.ToInt32(dr["ProcessId"].ToString());
line.Dimension1Id = dr["Dimension1Id"] == System.DBNull.Value ? null : (int?)Convert.ToInt32(dr["Dimension1Id"]);
line.Dimension2Id = dr["Dimension2Id"] == System.DBNull.Value ? null : (int?)Convert.ToInt32(dr["Dimension2Id"]);
line.ProductName = dr["ProductName"] == System.DBNull.Value ? null : dr["ProductName"].ToString();
line.UnitName = dr["UnitName"] == System.DBNull.Value ? null : dr["UnitName"].ToString();
line.unitDecimalPlaces = dr["DecimalPlaces"] == System.DBNull.Value ? 0 : Convert.ToInt32(dr["DecimalPlaces"].ToString());
line.RequiredQty = dr["Qty"] == System.DBNull.Value ? 0 : Convert.ToInt32(dr["Qty"].ToString());
line.Dimension1Name = dr["Dimension1Name"] == System.DBNull.Value ? null : dr["Dimension1Name"].ToString();
line.Dimension2Name = dr["Dimension2Name"] == System.DBNull.Value ? null : dr["Dimension2Name"].ToString();
line.ProcessName = dr["ProcessName"] == System.DBNull.Value ? null : dr["ProcessName"].ToString();
line.ExcessStockQty = 0;
line.MaterialPlanHeaderId = MaterialPlanHeaderId;
line.ProdPlanQty = (dr["PurchProd"].ToString() == "Purchase") ? 0 : Convert.ToInt32(dr["Qty"].ToString());
line.PurchPlanQty = (dr["PurchProd"].ToString() == "Purchase") ? Convert.ToInt32(dr["Qty"].ToString()) : 0;
line.GeneratedFor = MaterialPlanConstants.ProdOrder;
temp.Add(line);
}
return(temp);
}
