public IQueryable <ComboBoxResult> GetJobOrderHelpListForProduct(int filter, string term)
{
int CurrentSiteId = (int)System.Web.HttpContext.Current.Session["SiteId"];
int CurrentDivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];
var list = (from p in _unitOfWork.Repository <ViewJobOrderBalance>().Instance
join t in _unitOfWork.Repository <JobOrderHeader>().Instance on p.JobOrderHeaderId equals t.JobOrderHeaderId
join t2 in _unitOfWork.Repository <JobOrderLine>().Instance on p.JobOrderLineId equals t2.JobOrderLineId
join pt in _unitOfWork.Repository <Product>().Instance on p.ProductId equals pt.ProductId into ProductTable
from ProductTab in ProductTable.DefaultIfEmpty()
join D1 in _unitOfWork.Repository <Dimension1>().Instance on p.Dimension1Id equals D1.Dimension1Id into Dimension1Table
from Dimension1Tab in Dimension1Table.DefaultIfEmpty()
join D2 in _unitOfWork.Repository <Dimension2>().Instance on p.Dimension2Id equals D2.Dimension2Id into Dimension2Table
from Dimension2Tab in Dimension2Table.DefaultIfEmpty()
where p.BalanceQty > 0 &&
(string.IsNullOrEmpty(term) ? 1 == 1 : p.JobOrderNo.ToLower().Contains(term.ToLower()) ||
string.IsNullOrEmpty(term) ? 1 == 1 : Dimension1Tab.Dimension1Name.ToLower().Contains(term.ToLower()) ||
string.IsNullOrEmpty(term) ? 1 == 1 : Dimension2Tab.Dimension2Name.ToLower().Contains(term.ToLower())) &&
p.SiteId == CurrentSiteId &&
p.DivisionId == CurrentDivisionId
orderby t.DocDate, t.DocNo
select new ComboBoxResult
{
text = t.DocType.DocumentTypeName + "-" + p.JobOrderNo,
id = p.JobOrderLineId.ToString(),
TextProp1 = "Product: " + ProductTab.ProductName.ToString(),
TextProp2 = "Qty: " + p.BalanceQty.ToString(),
AProp1 = Dimension1Tab.Dimension1Name,
AProp2 = Dimension2Tab.Dimension2Name
});
return(list);
}
public ProductUidDetail FGetProductUidDetail(string ProductUidNo)
{
ProductUidDetail UidDetail = (from Pu in _productRepository.Instance
join P in _unitOfWork.Repository <Product>().Instance on Pu.ProductId equals P.ProductId into ProductTable
from Producttab in ProductTable.DefaultIfEmpty()
join Pr in _unitOfWork.Repository <Process>().Instance on Pu.CurrenctProcessId equals Pr.ProcessId into ProcessTable
from ProcessTab in ProcessTable.DefaultIfEmpty()
join Pe in _unitOfWork.Repository <Person>().Instance on Pu.LastTransactionPersonId equals Pe.PersonID into PersonTable
from PersonTab in PersonTable.DefaultIfEmpty()
where Pu.ProductUidName == ProductUidNo
select new ProductUidDetail
{
ProductUidId = Pu.ProductUIDId,
ProductId = Pu.ProductId,
ProductName = Producttab.ProductName,
PrevProcessId = Pu.CurrenctProcessId,
PrevProcessName = ProcessTab.ProcessName,
LastTransactionDocNo = Pu.LastTransactionDocNo,
LastTransactionDocDate = Pu.LastTransactionDocDate,
LastTransactionPersonName = PersonTab.Name,
CurrenctGodownId = Pu.CurrenctGodownId,
Status = Pu.Status,
GenDocTypeId = Pu.GenDocTypeId,
DivisionId = Producttab.DivisionId
}).FirstOrDefault();
return(UidDetail);
}
private void PrepareViewBag(DesignConsumptionLineViewModel svm)
{
var ProductFaceContentGroups = from p in db.Product
join pg in db.ProductGroups on p.ReferenceDocId equals pg.ProductGroupId into ProductGroupTable
from ProductGroupTab in ProductGroupTable.DefaultIfEmpty()
join fp in db.FinishedProduct on ProductGroupTab.ProductGroupId equals fp.ProductGroupId into FinishedProductTable
from FinishedProductTab in FinishedProductTable.DefaultIfEmpty()
join pcl in db.ProductContentLine on FinishedProductTab.FaceContentId equals pcl.ProductContentHeaderId into ProductContentLineTable
from ProductContentLineTab in ProductContentLineTable.DefaultIfEmpty()
where p.ProductId == svm.BaseProductId && ((int?)ProductContentLineTab.ProductGroupId ?? 0) != 0
group new { ProductContentLineTab } by new { ProductContentLineTab.ProductGroupId } into Result
select new
{
ProductGroupId = Result.Key.ProductGroupId
};
var TotalMainContents = (from L in db.BomDetail
join P in db.Product on L.ProductId equals P.ProductId into ProductTable
from ProductTab in ProductTable.DefaultIfEmpty()
join pcon in ProductFaceContentGroups on ProductTab.ProductGroupId equals pcon.ProductGroupId into ProductFaceContentTable
from ProductFaceContentTab in ProductFaceContentTable.DefaultIfEmpty()
where L.BaseProductId == svm.BaseProductId && ((int?)ProductFaceContentTab.ProductGroupId ?? 0) != 0
group new { L } by new { L.BaseProductId } into Result
select new
{
TotalQty = Result.Sum(i => i.L.Qty)
}).FirstOrDefault();
var TotalOtherContents = (from L in db.BomDetail
join P in db.Product on L.ProductId equals P.ProductId into ProductTable
from ProductTab in ProductTable.DefaultIfEmpty()
join pcon in ProductFaceContentGroups on ProductTab.ProductGroupId equals pcon.ProductGroupId into ProductFaceContentTable
from ProductFaceContentTab in ProductFaceContentTable.DefaultIfEmpty()
where L.BaseProductId == svm.BaseProductId && ((int?)ProductFaceContentTab.ProductGroupId ?? 0) == 0
group new { L } by new { L.BaseProductId } into Result
select new
{
TotalQty = Result.Sum(i => i.L.Qty)
}).FirstOrDefault();
if (TotalMainContents != null && svm.Weight != 0)
{
Decimal TotalMainContentPercentage = Math.Round(TotalMainContents.TotalQty * 100 / svm.Weight, 2);
ViewBag.LastTransaction = TotalMainContentPercentage + "% Main Contents filled, " + (100 - TotalMainContentPercentage) + " remaining.";
}
if (TotalOtherContents != null && svm.Weight != 0)
{
Decimal TotalOtherContentPercentage = Math.Round(TotalOtherContents.TotalQty * 100 / svm.Weight, 2);
ViewBag.LastTransaction = ViewBag.LastTransaction + (TotalOtherContentPercentage + "% Other Contents filled.").ToString();
}
}
public IQueryable <StockHeaderViewModel> GetStockHeaderList(int DocTypeId, string UName)
{
List <string> UserRoles = (List <string>)System.Web.HttpContext.Current.Session["Roles"];
var DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];
var SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"];
return(from p in db.StockHeader
join prod1 in db.Product on p.MachineId equals prod1.ProductId
into ProductTable from ProductTab in ProductTable.DefaultIfEmpty()
orderby p.DocDate descending, p.DocNo descending
where p.SiteId == SiteId && p.DivisionId == DivisionId && p.DocTypeId == DocTypeId
select new StockHeaderViewModel
{
CurrencyName = p.Currency.Name,
DivisionName = p.Division.DivisionName,
DocDate = p.DocDate,
DocHeaderId = p.DocHeaderId,
MachineName = ProductTab.ProductName,
CostCenterName = p.CostCenter.CostCenterName,
DocNo = p.DocNo,
DocTypeId = p.DocTypeId,
DocTypeName = p.DocType.DocumentTypeName,
FromGodownName = p.FromGodown.GodownName,
GodownName = p.Godown.GodownName,
PersonName = p.Person.Name,
ProcessName = p.Process.ProcessName,
Remark = p.Remark,
Status = p.Status,
StockHeaderId = p.StockHeaderId,
ModifiedBy = p.ModifiedBy,
GatePassDocNo = p.GatePassHeader.DocNo,
GatePassHeaderId = p.GatePassHeaderId,
GatePassDocDate = p.GatePassHeader.DocDate,
GatePassStatus = (p.GatePassHeaderId != null ? p.GatePassHeader.Status : 0),
ReviewCount = p.ReviewCount,
ReviewBy = p.ReviewBy,
Reviewed = (SqlFunctions.CharIndex(UName, p.ReviewBy) > 0),
TotalQty = p.StockLines.Sum(m => m.Qty),
DecimalPlaces = (from o in p.StockLines
join prod in db.Product on o.ProductId equals prod.ProductId
join u in db.Units on prod.UnitId equals u.UnitId
select u.DecimalPlaces).Max(),
}
);
}
public IEnumerable <JobInvoiceReturnLineIndexViewModel> GetLineListForIndex(int HeaderId)
{
return(from L in db.JobInvoiceReturnLine
join Jil in db.JobInvoiceLine on L.JobInvoiceLineId equals Jil.JobInvoiceLineId into JobInvoiceLineTable
from JobInvoiceLineTab in JobInvoiceLineTable.DefaultIfEmpty()
join Jrl in db.JobReceiveLine on JobInvoiceLineTab.JobReceiveLineId equals Jrl.JobReceiveLineId into JobReceiveLineTable
from JobReceiveLineTab in JobReceiveLineTable.DefaultIfEmpty()
join Jrh in db.JobReceiveHeader on JobReceiveLineTab.JobReceiveHeaderId equals Jrh.JobReceiveHeaderId into JobReceiveHeaderTable
from JobReceiveHeaderTab in JobReceiveHeaderTable.DefaultIfEmpty()
join P in db.Product on JobReceiveLineTab.ProductId equals P.ProductId into ProductTable
from ProductTab in ProductTable.DefaultIfEmpty()
where L.JobInvoiceReturnHeaderId == HeaderId
orderby L.Sr
select new JobInvoiceReturnLineIndexViewModel
{
ProductName = ProductTab.ProductName,
Qty = L.Qty,
JobInvoiceReturnLineId = L.JobInvoiceReturnLineId,
UnitId = ProductTab.UnitId,
Specification = JobReceiveLineTab.Specification,
Dimension1Name = JobReceiveLineTab.Dimension1.Dimension1Name,
Dimension2Name = JobReceiveLineTab.Dimension2.Dimension2Name,
Dimension3Name = JobReceiveLineTab.Dimension3.Dimension3Name,
Dimension4Name = JobReceiveLineTab.Dimension4.Dimension4Name,
LotNo = JobReceiveLineTab.LotNo,
JobGoodsRecieptHeaderDocNo = JobReceiveHeaderTab.DocNo,
JobInvoiceHeaderDocNo = JobInvoiceLineTab.JobInvoiceHeader.DocNo,
DealQty = L.DealQty,
DealUnitId = L.DealUnitId,
unitDecimalPlaces = ProductTab.Unit.DecimalPlaces,
DealunitDecimalPlaces = L.DealUnit.DecimalPlaces,
Rate = L.Rate,
Amount = L.Amount,
Remark = L.Remark,
ProductUidName = JobReceiveLineTab.ProductUid.ProductUidName,
UnitName = ProductTab.Unit.UnitName,
DealUnitName = L.DealUnit.UnitName,
JobInvoiceDocTypeId = L.JobInvoiceLine.JobInvoiceHeader.DocTypeId,
JobInvoiceHeaderId = L.JobInvoiceLine.JobInvoiceHeaderId,
});
}
public JsonResult GetSaleOrderDetailJson(int SaleOrderLineId)
{
var temp = (from L in db.ViewSaleOrderBalance
join Dl in db.SaleOrderLine on L.SaleOrderLineId equals Dl.SaleOrderLineId into SaleOrderLineTable
from SaleOrderLineTab in SaleOrderLineTable.DefaultIfEmpty()
join P in db.Product on L.ProductId equals P.ProductId into ProductTable
from ProductTab in ProductTable.DefaultIfEmpty()
join U in db.Units on ProductTab.UnitId equals U.UnitId into UnitTable
from UnitTab in UnitTable.DefaultIfEmpty()
join D1 in db.Dimension1 on L.Dimension1Id equals D1.Dimension1Id into Dimension1Table
from Dimension1Tab in Dimension1Table.DefaultIfEmpty()
join D2 in db.Dimension2 on L.Dimension2Id equals D2.Dimension2Id into Dimension2Table
from Dimension2Tab in Dimension2Table.DefaultIfEmpty()
where L.SaleOrderLineId == SaleOrderLineId
select new
{
SaleOrderHeaderDocNo = L.SaleOrderNo,
UnitId = UnitTab.UnitId,
UnitName = UnitTab.UnitName,
DealUnitId = SaleOrderLineTab.DealUnitId,
Specification = SaleOrderLineTab.Specification,
UnitConversionMultiplier = SaleOrderLineTab.UnitConversionMultiplier,
ProductId = L.ProductId,
Dimension1Id = L.Dimension1Id,
Dimension1Name = Dimension1Tab.Dimension1Name,
Dimension2Id = L.Dimension2Id,
Dimension2Name = Dimension2Tab.Dimension2Name,
Rate = L.Rate,
BalanceQty = L.BalanceQty
}).FirstOrDefault();
if (temp != null)
{
return(Json(temp));
}
else
{
return(null);
}
}
public JsonResult GetConsumptionTotalQty(int BaseProductId, Decimal TotalWeight, Decimal BomQty, int BomDetailId, int BaseProcessId)
{
var ProductFaceContentGroups = from p in db.Product
join pg in db.ProductGroups on p.ReferenceDocId equals pg.ProductGroupId into ProductGroupTable
from ProductGroupTab in ProductGroupTable.DefaultIfEmpty()
join fp in db.FinishedProduct on ProductGroupTab.ProductGroupId equals fp.ProductGroupId into FinishedProductTable
from FinishedProductTab in FinishedProductTable.DefaultIfEmpty()
join pcl in db.ProductContentLine on FinishedProductTab.FaceContentId equals pcl.ProductContentHeaderId into ProductContentLineTable
from ProductContentLineTab in ProductContentLineTable.DefaultIfEmpty()
where p.ProductId == BaseProductId && ((int?)ProductContentLineTab.ProductGroupId ?? 0) != 0
group new { ProductContentLineTab } by new { ProductContentLineTab.ProductGroupId } into Result
select new
{
ProductGroupId = Result.Key.ProductGroupId
};
Decimal TotalFillQty = 0;
var temp = (from L in db.BomDetail
join p in db.Product on L.ProductId equals p.ProductId into ProductTable
from ProductTab in ProductTable.DefaultIfEmpty()
join pcon in ProductFaceContentGroups on ProductTab.ProductGroupId equals pcon.ProductGroupId into ProductFaceContentTable
from ProductFaceContentTab in ProductFaceContentTable.DefaultIfEmpty()
where L.BaseProductId == BaseProductId && L.BomDetailId != BomDetailId && L.BaseProcessId == BaseProcessId && ((int?)ProductFaceContentTab.ProductGroupId ?? 0) != 0
group(L) by(L.BaseProductId) into Result
select new
{
TotalQty = Result.Sum(i => i.Qty)
}).FirstOrDefault();
if (temp != null)
{
TotalFillQty = temp.TotalQty;
}
return(Json(TotalFillQty));
}
public IQueryable <ComboBoxResult> GetSaleEnquiryHelpListForProduct(int PersonId, string term)
{
int CurrentSiteId = (int)System.Web.HttpContext.Current.Session["SiteId"];
int CurrentDivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];
var list = (from p in db.ViewSaleEnquiryBalanceForQuotation
join t in db.SaleEnquiryHeader on p.SaleEnquiryHeaderId equals t.SaleEnquiryHeaderId
join t2 in db.SaleEnquiryLine on p.SaleEnquiryLineId equals t2.SaleEnquiryLineId
join pt in db.Product on p.ProductId equals pt.ProductId into ProductTable
from ProductTab in ProductTable.DefaultIfEmpty()
join D1 in db.Dimension1 on p.Dimension1Id equals D1.Dimension1Id into Dimension1Table
from Dimension1Tab in Dimension1Table.DefaultIfEmpty()
join D2 in db.Dimension2 on p.Dimension2Id equals D2.Dimension2Id into Dimension2Table
from Dimension2Tab in Dimension2Table.DefaultIfEmpty()
join D3 in db.Dimension3 on p.Dimension3Id equals D3.Dimension3Id into Dimension3Table
from Dimension3Tab in Dimension3Table.DefaultIfEmpty()
join D4 in db.Dimension4 on p.Dimension4Id equals D4.Dimension4Id into Dimension4Table
from Dimension4Tab in Dimension4Table.DefaultIfEmpty()
where p.BuyerId == PersonId &&
((string.IsNullOrEmpty(term) ? 1 == 1 : p.SaleEnquiryNo.ToLower().Contains(term.ToLower())) ||
(string.IsNullOrEmpty(term) ? 1 == 1 : ProductTab.ProductName.ToLower().Contains(term.ToLower()))) &&
p.SiteId == CurrentSiteId &&
p.DivisionId == CurrentDivisionId
orderby t.DocDate, t.DocNo
select new ComboBoxResult
{
text = ProductTab.ProductName,
id = p.SaleEnquiryLineId.ToString(),
TextProp1 = "Enquiry No: " + p.SaleEnquiryNo.ToString(),
TextProp2 = "BalQty: " + p.BalanceQty.ToString(),
AProp1 = Dimension1Tab.Dimension1Name + (string.IsNullOrEmpty(Dimension1Tab.Dimension1Name) ? "" : ",") + Dimension2Tab.Dimension2Name,
AProp2 = Dimension3Tab.Dimension3Name + (string.IsNullOrEmpty(Dimension3Tab.Dimension3Name) ? "" : ",") + Dimension4Tab.Dimension4Name,
});
return(list);
}
public IQueryable <ComboBoxResult> GetSaleInvoiceHelpListForProduct(int filter, string term)
{
var SaleDeliveryHeader = new SaleDeliveryHeaderService(_unitOfWork).Find(filter);
var settings = new SaleDeliverySettingService(_unitOfWork).GetSaleDeliverySettingForDocument(SaleDeliveryHeader.DocTypeId, SaleDeliveryHeader.DivisionId, SaleDeliveryHeader.SiteId);
string[] contraSites = null;
if (!string.IsNullOrEmpty(settings.filterContraSites))
{
contraSites = settings.filterContraSites.Split(",".ToCharArray());
}
else
{
contraSites = new string[] { "NA" };
}
string[] contraDivisions = null;
if (!string.IsNullOrEmpty(settings.filterContraDivisions))
{
contraDivisions = settings.filterContraDivisions.Split(",".ToCharArray());
}
else
{
contraDivisions = new string[] { "NA" };
}
string[] contraDocTypes = null;
if (!string.IsNullOrEmpty(settings.filterContraDocTypes))
{
contraDocTypes = settings.filterContraDocTypes.Split(",".ToCharArray());
}
else
{
contraDocTypes = new string[] { "NA" };
}
int CurrentSiteId = (int)System.Web.HttpContext.Current.Session["SiteId"];
int CurrentDivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];
var list = (from p in db.ViewSaleInvoiceBalanceForDelivery
join t in db.SaleInvoiceHeader on p.SaleInvoiceHeaderId equals t.SaleInvoiceHeaderId
join t2 in db.SaleInvoiceLine on p.SaleInvoiceLineId equals t2.SaleInvoiceLineId
join pt in db.Product on p.ProductId equals pt.ProductId into ProductTable
from ProductTab in ProductTable.DefaultIfEmpty()
join D1 in db.Dimension1 on p.Dimension1Id equals D1.Dimension1Id into Dimension1Table
from Dimension1Tab in Dimension1Table.DefaultIfEmpty()
join D2 in db.Dimension2 on p.Dimension2Id equals D2.Dimension2Id into Dimension2Table
from Dimension2Tab in Dimension2Table.DefaultIfEmpty()
where p.SaleToBuyerId == SaleDeliveryHeader.SaleToBuyerId &&
((string.IsNullOrEmpty(term) ? 1 == 1 : p.SaleInvoiceNo.ToLower().Contains(term.ToLower())) ||
(string.IsNullOrEmpty(term) ? 1 == 1 : ProductTab.ProductName.ToLower().Contains(term.ToLower())) ||
(string.IsNullOrEmpty(term) ? 1 == 1 : Dimension1Tab.Dimension1Name.ToLower().Contains(term.ToLower())) ||
(string.IsNullOrEmpty(term) ? 1 == 1 : Dimension2Tab.Dimension2Name.ToLower().Contains(term.ToLower()))) &&
(string.IsNullOrEmpty(settings.filterContraSites) ? p.SiteId == CurrentSiteId : contraSites.Contains(p.SiteId.ToString())) &&
(string.IsNullOrEmpty(settings.filterContraDivisions) ? p.DivisionId == CurrentDivisionId : contraDivisions.Contains(p.DivisionId.ToString())) &&
(string.IsNullOrEmpty(settings.filterContraDocTypes) ? 1 == 1 : contraDocTypes.Contains(t.DocTypeId.ToString())) &&
p.BalanceQty > 0
orderby t.DocDate, t.DocNo
select new ComboBoxResult
{
text = ProductTab.ProductName,
id = p.SaleInvoiceLineId.ToString(),
TextProp1 = "Order No: " + p.SaleInvoiceNo.ToString(),
TextProp2 = "BalQty: " + p.BalanceQty.ToString(),
AProp1 = Dimension1Tab.Dimension1Name,
AProp2 = Dimension2Tab.Dimension2Name
});
return(list);
}
// here we simulate SQL search, sorting and paging operations
private List <ProductRateIndex> FilterProductData(ref int recordFiltered, ref int recordTotal, int start, int length, string search, int sortColumn, string sortDirection, FilterArgs Fvm)
{
bool Pending = false;
bool Sample = false;
if (string.IsNullOrEmpty(Fvm.WizardType) || Fvm.WizardType == "Pending")
{
Pending = true;
}
List <ProductRateIndex> list = new List <ProductRateIndex>();
IQueryable <ProductRateIndex> _data = (from Pp in db.ProductProcess
join Rh in db.RateListHeader on Pp.ProcessId equals Rh.ProcessId into RateListHeaderTable
from RateListHeaderTab in RateListHeaderTable.DefaultIfEmpty()
join P in db.Product on Pp.ProductId equals P.ProductId into ProductTable
from ProductTab in ProductTable.DefaultIfEmpty()
join D1 in db.Dimension1 on Pp.Dimension1Id equals D1.Dimension1Id into Dimension1Table
from Dimension1Tab in Dimension1Table.DefaultIfEmpty()
join D2 in db.Dimension2 on Pp.Dimension2Id equals D2.Dimension2Id into Dimension2Table
from Dimension2Tab in Dimension2Table.DefaultIfEmpty()
where RateListHeaderTab.RateListHeaderId == Fvm.RateListHeaderId && (Pending ? Pp.ProductRateGroupId == null : 1 == 1) &&
RateListHeaderTab.DivisionId == ProductTab.DivisionId && ProductTab.IsActive == true
group new { Pp, ProductTab, Dimension1Tab, Dimension2Tab } by new { ProductTab.ProductId, Dimension1Tab.Dimension1Id, Dimension2Tab.Dimension2Id } into g
select new ProductRateIndex
{
ProductId = g.Key.ProductId,
ProductName = g.Max(m => m.ProductTab.ProductName),
Dimension1Id = g.Key.Dimension1Id,
Dimension1Name = g.Max(m => m.Dimension1Tab.Dimension1Name),
Dimension2Id = g.Key.Dimension2Id,
Dimension2Name = g.Max(m => m.Dimension2Tab.Dimension2Name),
ImageFileName = g.Max(m => m.ProductTab.ImageFileName),
ImageFolderName = g.Max(m => m.ProductTab.ImageFolderName),
ProductRateGroupId = g.Max(m => m.Pp.ProductRateGroupId),
});
recordTotal = _data.Count();
if (string.IsNullOrEmpty(search))
{
}
else
{
// simulate search
_data = from m in _data
where (m.ProductName).ToLower().Contains(search.ToLower()) || (m.Dimension1Name).ToLower().Contains(search.ToLower()) ||
(m.Dimension2Name).ToLower().Contains(search.ToLower())
select m;
}
_data = _data.OrderBy(m => m.ProductName);
recordFiltered = _data.Count();
// get just one page of data
list = _data.Select(m => new ProductRateIndex
{
ProductId = m.ProductId,
ProductName = m.ProductName,
Dimension1Id = m.Dimension1Id,
Dimension1Name = m.Dimension1Name,
Dimension2Id = m.Dimension2Id,
Dimension2Name = m.Dimension2Name,
ImageFileName = m.ImageFileName,
ImageFolderName = m.ImageFolderName,
ProductRateGroupId = m.ProductRateGroupId,
})
.Skip(start).Take((start == 0) ? 90 : length).ToList();
return(list);
}
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 IQueryable <ComboBoxResult> GetCustomProducts(int Id, string term)
{
var SaleEnquiry = new SaleEnquiryHeaderService(_unitOfWork).Find(Id);
var settings = new SaleEnquirySettingsService(_unitOfWork).GetSaleEnquirySettingsForDucument(SaleEnquiry.DocTypeId, SaleEnquiry.DivisionId, SaleEnquiry.SiteId);
string[] ProductTypes = null;
if (!string.IsNullOrEmpty(settings.filterProductTypes))
{
ProductTypes = settings.filterProductTypes.Split(",".ToCharArray());
}
else
{
ProductTypes = new string[] { "NA" };
}
string[] Products = null;
if (!string.IsNullOrEmpty(settings.filterProducts))
{
Products = settings.filterProducts.Split(",".ToCharArray());
}
else
{
Products = new string[] { "NA" };
}
string[] ProductGroups = null;
if (!string.IsNullOrEmpty(settings.filterProductGroups))
{
ProductGroups = settings.filterProductGroups.Split(",".ToCharArray());
}
else
{
ProductGroups = new string[] { "NA" };
}
return(from pb in db.ViewProductBuyer
join Pt in db.Product on pb.ProductId equals Pt.ProductId into ProductTable from ProductTab in ProductTable.DefaultIfEmpty()
where (string.IsNullOrEmpty(settings.filterProductTypes) ? 1 == 1 : ProductTypes.Contains(ProductTab.ProductGroup.ProductTypeId.ToString())) &&
(string.IsNullOrEmpty(settings.filterProducts) ? 1 == 1 : Products.Contains(ProductTab.ProductId.ToString())) &&
(string.IsNullOrEmpty(settings.filterProductGroups) ? 1 == 1 : ProductGroups.Contains(ProductTab.ProductGroupId.ToString())) &&
(string.IsNullOrEmpty(term) ? 1 == 1 : pb.ProductName.ToLower().Contains(term.ToLower())) &&
pb.BuyerId == SaleEnquiry.SaleToBuyerId
orderby pb.ProductName
select new ComboBoxResult
{
id = pb.ProductId.ToString(),
text = pb.ProductName,
AProp1 = pb.BuyerSpecification,
AProp2 = pb.BuyerSpecification1,
TextProp1 = pb.BuyerSpecification2,
TextProp2 = pb.BuyerSpecification3
});
}
public IEnumerable <SaleEnquiryHeaderListViewModel> GetPendingSaleEnquiriesWithPatternMatch(int Id, string term, int Limiter)
{
var SaleQuotationHeader = new SaleQuotationHeaderService(_unitOfWork).Find(Id);
var settings = new SaleQuotationSettingsService(_unitOfWork).GetSaleQuotationSettingsForDocument(SaleQuotationHeader.DocTypeId, SaleQuotationHeader.DivisionId, SaleQuotationHeader.SiteId);
string settingProductTypes = "";
string settingProductDivision = "";
string settingProductCategory = "";
if (!string.IsNullOrEmpty(settings.filterProductTypes))
{
settingProductTypes = "|" + settings.filterProductTypes.Replace(",", "|,|") + "|";
}
if (!string.IsNullOrEmpty(settings.FilterProductDivision))
{
settingProductDivision = "|" + settings.FilterProductDivision.Replace(",", "|,|") + "|";
}
string[] contraDocTypes = null;
if (!string.IsNullOrEmpty(settings.filterContraDocTypes))
{
contraDocTypes = settings.filterContraDocTypes.Split(",".ToCharArray());
}
else
{
contraDocTypes = new string[] { "NA" };
}
string[] contraSites = null;
if (!string.IsNullOrEmpty(settings.filterContraSites))
{
contraSites = settings.filterContraSites.Split(",".ToCharArray());
}
else
{
contraSites = new string[] { "NA" };
}
string[] contraDivisions = null;
if (!string.IsNullOrEmpty(settings.filterContraDivisions))
{
contraDivisions = settings.filterContraDivisions.Split(",".ToCharArray());
}
else
{
contraDivisions = new string[] { "NA" };
}
string[] ProductTypes = null;
if (!string.IsNullOrEmpty(settings.filterProductTypes))
{
ProductTypes = settingProductTypes.Split(",".ToCharArray());
}
else
{
ProductTypes = new string[] { "NA" };
}
string[] ProductDivision = null;
if (!string.IsNullOrEmpty(settings.FilterProductDivision))
{
ProductDivision = settingProductDivision.Split(",".ToCharArray());
}
else
{
ProductDivision = new string[] { "NA" };
}
int CurrentSiteId = (int)System.Web.HttpContext.Current.Session["SiteId"];
int CurrentDivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];
var list = (from p in db.ViewSaleEnquiryBalanceForQuotation
join Pt in db.Product on p.ProductId equals Pt.ProductId into ProductTable from ProductTab in ProductTable.DefaultIfEmpty()
join Fp in db.FinishedProduct on p.ProductId equals Fp.ProductId into FinishedProductTable from FinishedProductTab in FinishedProductTable.DefaultIfEmpty()
join D1 in db.Dimension1 on p.Dimension1Id equals D1.Dimension1Id into Dimension1Table from Dimension1Tab in Dimension1Table.DefaultIfEmpty()
join D2 in db.Dimension2 on p.Dimension2Id equals D2.Dimension2Id into Dimension2Table
from Dimension2Tab in Dimension2Table.DefaultIfEmpty()
join D3 in db.Dimension3 on p.Dimension3Id equals D3.Dimension3Id into Dimension3Table
from Dimension3Tab in Dimension3Table.DefaultIfEmpty()
join D4 in db.Dimension4 on p.Dimension4Id equals D4.Dimension4Id into Dimension4Table
from Dimension4Tab in Dimension4Table.DefaultIfEmpty()
where (
string.IsNullOrEmpty(term) ? 1 == 1 : p.SaleEnquiryNo.ToLower().Contains(term.ToLower()) ||
string.IsNullOrEmpty(term) ? 1 == 1 : ProductTab.ProductName.ToLower().Contains(term.ToLower()) ||
string.IsNullOrEmpty(term) ? 1 == 1 : Dimension1Tab.Dimension1Name.ToLower().Contains(term.ToLower()) ||
string.IsNullOrEmpty(term) ? 1 == 1 : Dimension2Tab.Dimension2Name.ToLower().Contains(term.ToLower()) ||
string.IsNullOrEmpty(term) ? 1 == 1 : Dimension3Tab.Dimension3Name.ToLower().Contains(term.ToLower()) ||
string.IsNullOrEmpty(term) ? 1 == 1 : Dimension4Tab.Dimension4Name.ToLower().Contains(term.ToLower())
) && p.BalanceQty > 0 &&
(string.IsNullOrEmpty(settings.filterContraDocTypes) ? 1 == 1 : contraDocTypes.Contains(p.DocTypeId.ToString())) &&
(string.IsNullOrEmpty(settings.filterContraSites) ? p.SiteId == CurrentSiteId : contraSites.Contains(p.SiteId.ToString())) &&
(string.IsNullOrEmpty(settings.filterContraDivisions) ? p.DivisionId == CurrentDivisionId : contraDivisions.Contains(p.DivisionId.ToString())) &&
(string.IsNullOrEmpty(settings.filterProductTypes) ? 1 == 1 : ProductTypes.Contains("|" + ProductTab.ProductGroup.ProductTypeId.ToString() + "|")) &&
(string.IsNullOrEmpty(settings.FilterProductDivision) ? 1 == 1 : ProductDivision.Contains("|" + ProductTab.DivisionId.ToString() + "|"))
orderby p.SaleEnquiryNo
select new SaleEnquiryHeaderListViewModel
{
DocNo = p.SaleEnquiryNo,
SaleEnquiryLineId = p.SaleEnquiryLineId,
ProductName = ProductTab.ProductName,
Dimension1Name = Dimension1Tab.Dimension1Name,
Dimension2Name = Dimension2Tab.Dimension2Name,
Dimension3Name = Dimension3Tab.Dimension3Name,
Dimension4Name = Dimension4Tab.Dimension4Name,
}).Take(Limiter);
return(list);
}
public IQueryable <ComboBoxResult> GetPendingSaleEnquiryHelpList(int Id, string term)
{
var SaleQuotationHeader = new SaleQuotationHeaderService(_unitOfWork).Find(Id);
var settings = new SaleQuotationSettingsService(_unitOfWork).GetSaleQuotationSettingsForDocument(SaleQuotationHeader.DocTypeId, SaleQuotationHeader.DivisionId, SaleQuotationHeader.SiteId);
string[] contraDocTypes = null;
if (!string.IsNullOrEmpty(settings.filterContraDocTypes))
{
contraDocTypes = settings.filterContraDocTypes.Split(",".ToCharArray());
}
else
{
contraDocTypes = new string[] { "NA" };
}
string[] contraSites = null;
if (!string.IsNullOrEmpty(settings.filterContraSites))
{
contraSites = settings.filterContraSites.Split(",".ToCharArray());
}
else
{
contraSites = new string[] { "NA" };
}
string[] contraDivisions = null;
if (!string.IsNullOrEmpty(settings.filterContraDivisions))
{
contraDivisions = settings.filterContraDivisions.Split(",".ToCharArray());
}
else
{
contraDivisions = new string[] { "NA" };
}
string[] ProductTypes = null;
if (!string.IsNullOrEmpty(settings.filterProductTypes))
{
ProductTypes = settings.filterProductTypes.Split(",".ToCharArray());
}
else
{
ProductTypes = new string[] { "NA" };
}
int CurrentSiteId = (int)System.Web.HttpContext.Current.Session["SiteId"];
int CurrentDivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];
var list = (from p in db.ViewSaleEnquiryBalanceForQuotation
join t in db.Persons on p.BuyerId equals t.PersonID into table
from tab in table.DefaultIfEmpty()
join Pt in db.Product on p.ProductId equals Pt.ProductId into ProductTable
from ProductTab in ProductTable.DefaultIfEmpty()
join Fp in db.FinishedProduct on p.ProductId equals Fp.ProductId into FinishedProductTable
from FinishedProductTab in FinishedProductTable.DefaultIfEmpty()
join D in db.DocumentType on p.DocTypeId equals D.DocumentTypeId into DocumentTypeTable
from DocumentTyoeTab in DocumentTypeTable.DefaultIfEmpty()
where (string.IsNullOrEmpty(term) ? 1 == 1 : p.SaleEnquiryNo.ToLower().Contains(term.ToLower())) && p.BalanceQty > 0 &&
(string.IsNullOrEmpty(settings.filterContraDocTypes) ? 1 == 1 : contraDocTypes.Contains(p.DocTypeId.ToString())) &&
(string.IsNullOrEmpty(settings.filterContraSites) ? p.SiteId == CurrentSiteId : contraSites.Contains(p.SiteId.ToString())) &&
(string.IsNullOrEmpty(settings.filterContraDivisions) ? p.DivisionId == CurrentDivisionId : contraDivisions.Contains(p.DivisionId.ToString())) &&
(string.IsNullOrEmpty(settings.filterProductTypes) ? 1 == 1 : ProductTypes.Contains(ProductTab.ProductGroup.ProductTypeId.ToString()))
group new { p, tab.Code, DocumentTyoeTab } by p.SaleEnquiryHeaderId into g
orderby g.Max(m => m.p.EnquiryDate)
select new ComboBoxResult
{
text = g.Max(m => m.DocumentTyoeTab.DocumentTypeShortName) + "-" + g.Max(m => m.p.SaleEnquiryNo) + " {" + g.Max(m => m.Code) + "}",
id = g.Key.ToString(),
});
return(list);
}
public decimal GetSaleOrderBalanceQty(string SaleOrderNo, string Product)
{
//return (
// from p in db.SaleOrderLine
// join bal in db.ViewSaleOrderBalance on p.SaleOrderLineId equals bal.SaleOrderLineId
// where p.SaleOrderHeader.DocNo == SaleOrderNo & p.Product.ProductName == Product
// select bal.BalanceQty
// ).FirstOrDefault();
var SaleOrderLine1 = from L in db.SaleOrderLine
join H in db.SaleOrderHeader on L.SaleOrderHeaderId equals H.SaleOrderHeaderId into SaleOrderHeaderTable
from SaleOrderHeaderTab in SaleOrderHeaderTable.DefaultIfEmpty()
join P in db.Product on L.ProductId equals P.ProductId into ProductTable
from ProductTab in ProductTable.DefaultIfEmpty()
where SaleOrderHeaderTab.DocNo == SaleOrderNo && ProductTab.ProductName == Product
select new
{
SaleOrderLineId = L.SaleOrderLineId
};
var SaleOrderLine = (from L in db.SaleOrderLine
join H in db.SaleOrderHeader on L.SaleOrderHeaderId equals H.SaleOrderHeaderId into SaleOrderHeaderTable
from SaleOrderHeaderTab in SaleOrderHeaderTable.DefaultIfEmpty()
join P in db.Product on L.ProductId equals P.ProductId into ProductTable
from ProductTab in ProductTable.DefaultIfEmpty()
where SaleOrderHeaderTab.DocNo == SaleOrderNo && ProductTab.ProductName == Product
select new
{
SaleOrderLineId = L.SaleOrderLineId
}).FirstOrDefault();
if (SaleOrderLine != null)
{
try
{
int SaleOrderLineId = SaleOrderLine.SaleOrderLineId;
var BalanceList = from L in db.ViewSaleOrderBalanceForCancellation
join P in db.Product on L.ProductId equals P.ProductId
where L.SaleOrderLineId == SaleOrderLineId
select new
{
BalanceQty = L.BalanceQty
};
if (BalanceList != null)
{
return(BalanceList.FirstOrDefault().BalanceQty);
}
else
{
return(0);
}
}
catch (Exception e)
{
string str = e.Message;
return(0);
}
}
else
{
return(0);
}
}
