public IQueryable <ComboBoxResult> GetPendingJobWorkersForRateAmndmt(int id, string term)//DocTypeId
{
var JobInvoiceAmendmentHeader = new JobInvoiceAmendmentHeaderService(_unitOfWork).Find(id);
var settings = new JobInvoiceSettingsService(_unitOfWork).GetJobInvoiceSettingsForDocument(JobInvoiceAmendmentHeader.DocTypeId, JobInvoiceAmendmentHeader.DivisionId, JobInvoiceAmendmentHeader.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" };
}
int CurrentSiteId = (int)System.Web.HttpContext.Current.Session["SiteId"];
int CurrentDivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];
var list = (from VJ in db.ViewJobInvoiceBalanceForRateAmendment
join p in db.JobInvoiceLine on VJ.JobInvoiceLineId equals p.JobInvoiceLineId
join H in db.JobInvoiceHeader on p.JobInvoiceHeaderId equals H.JobInvoiceHeaderId
join t in db.Persons on p.JobWorkerId equals t.PersonID
where (string.IsNullOrEmpty(term) ? 1 == 1 : t.Name.ToLower().Contains(term.ToLower())) && p.Qty > 0 &&
(string.IsNullOrEmpty(settings.filterContraDocTypes) ? 1 == 1 : contraDocTypes.Contains(H.DocTypeId.ToString())) &&
(string.IsNullOrEmpty(settings.filterContraSites) ? H.SiteId == CurrentSiteId : contraSites.Contains(H.SiteId.ToString())) &&
(string.IsNullOrEmpty(settings.filterContraDivisions) ? H.DivisionId == CurrentDivisionId : contraDivisions.Contains(H.DivisionId.ToString())) &&
(JobInvoiceAmendmentHeader.JobWorkerId.HasValue && JobInvoiceAmendmentHeader.JobWorkerId.Value > 0 ? p.JobWorkerId == JobInvoiceAmendmentHeader.JobWorkerId : 1 == 1)
group new { t } by t.PersonID into g
orderby g.Max(m => m.t.Name)
select new ComboBoxResult
{
text = g.Max(m => m.t.Name),
id = g.Key.ToString(),
}
);
return(list);
}
public IQueryable <ComboBoxResult> GetPendingProductHelpList(int Id, string term)
{
int CurrentSiteId = (int)System.Web.HttpContext.Current.Session["SiteId"];
int CurrentDivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];
var settings = new JobInvoiceSettingsService(_unitOfWork).GetJobInvoiceSettingsForDocument(Id, CurrentDivisionId, CurrentSiteId);
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" };
}
var list = (from p in db.ViewJobInvoiceBalanceForRateAmendment
join t in db.JobInvoiceHeader on p.JobInvoiceHeaderId equals t.JobInvoiceHeaderId
where (string.IsNullOrEmpty(term) ? 1 == 1 : p.Product.ProductName.ToLower().Contains(term.ToLower())) && p.BalanceQty > 0 && t.ProcessId == settings.ProcessId &&
(string.IsNullOrEmpty(settings.filterContraDocTypes) ? 1 == 1 : contraDocTypes.Contains(t.DocTypeId.ToString())) &&
(string.IsNullOrEmpty(settings.filterContraSites) ? t.SiteId == CurrentSiteId : contraSites.Contains(t.SiteId.ToString())) &&
(string.IsNullOrEmpty(settings.filterContraDivisions) ? t.DivisionId == CurrentDivisionId : contraDivisions.Contains(t.DivisionId.ToString()))
group new { p, t } by p.ProductId into g
orderby g.Max(m => m.p.InvoiceDate)
select new ComboBoxResult
{
text = g.Max(m => m.p.Product.ProductName),
id = g.Key.ToString(),
}
);
return(list);
}
public IQueryable <ComboBoxResult> GetCustomPerson(int Id, string term, int?ProcessId = null)
{
int DocTypeId = Id;
int SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"];
int DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];
var settings = new JobInvoiceSettingsService(_unitOfWork).GetJobInvoiceSettingsForDocument(DocTypeId, DivisionId, SiteId);
string[] PersonRoles = null;
if (!string.IsNullOrEmpty(settings.filterPersonRoles))
{
PersonRoles = settings.filterPersonRoles.Split(",".ToCharArray());
}
else
{
PersonRoles = new string[] { "NA" };
}
string DivIdStr = "|" + DivisionId.ToString() + "|";
string SiteIdStr = "|" + SiteId.ToString() + "|";
var list = (from p in db.Persons
join bus in db.BusinessEntity on p.PersonID equals bus.PersonID into BusinessEntityTable
from BusinessEntityTab in BusinessEntityTable.DefaultIfEmpty()
join pp in db.PersonProcess on p.PersonID equals pp.PersonId into PersonProcessTable
from PersonProcessTab in PersonProcessTable.DefaultIfEmpty()
join pr in db.PersonRole on p.PersonID equals pr.PersonId into PersonRoleTable
from PersonRoleTab in PersonRoleTable.DefaultIfEmpty()
//where PersonProcessTab.ProcessId == settings.ProcessId
where (ProcessId == null ? PersonProcessTab.ProcessId == settings.ProcessId : PersonProcessTab.ProcessId == ProcessId) &&
(string.IsNullOrEmpty(term) ? 1 == 1 : (p.Name.ToLower().Contains(term.ToLower()) || p.Code.ToLower().Contains(term.ToLower()))) &&
(string.IsNullOrEmpty(settings.filterPersonRoles) ? 1 == 1 : PersonRoles.Contains(PersonRoleTab.RoleDocTypeId.ToString())) &&
BusinessEntityTab.DivisionIds.IndexOf(DivIdStr) != -1 &&
BusinessEntityTab.SiteIds.IndexOf(SiteIdStr) != -1 &&
(p.IsActive == null ? 1 == 1 : p.IsActive == true)
group new { p } by new { p.PersonID } into Result
orderby Result.Max(m => m.p.Name)
select new ComboBoxResult
{
id = Result.Key.PersonID.ToString(),
text = Result.Max(m => m.p.Name + ", " + m.p.Suffix + " [" + m.p.Code + "]"),
}
);
return(list);
}
public IEnumerable <JobInvoiceHeaderListViewModel> GetPendingJobInvoicesForRateAmndmt(int HeaderId, string term, int Limit)//Product Id
{
var Header = new JobInvoiceAmendmentHeaderService(_unitOfWork).Find(HeaderId);
var settings = new JobInvoiceSettingsService(_unitOfWork).GetJobInvoiceSettingsForDocument(Header.DocTypeId, Header.DivisionId, Header.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" };
}
int CurrentSiteId = (int)System.Web.HttpContext.Current.Session["SiteId"];
int CurrentDivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];
var tem = from VJB in db.ViewJobInvoiceBalanceForRateAmendment
join p in db.JobInvoiceLine on VJB.JobInvoiceLineId equals p.JobInvoiceLineId
join JR in db.JobReceiveLine on p.JobReceiveLineId equals JR.JobReceiveLineId
join JO in db.JobOrderLine on JR.JobOrderLineId equals JO.JobOrderLineId
join t2 in db.Product on JO.ProductId equals t2.ProductId
join t3 in db.JobInvoiceHeader on p.JobInvoiceHeaderId equals t3.JobInvoiceHeaderId
where p.Qty > 0 && (Header.JobWorkerId.HasValue && Header.JobWorkerId.Value > 0 ? p.JobWorkerId == Header.JobWorkerId : 1 == 1) &&
(string.IsNullOrEmpty(term) ? 1 == 1 : (t3.DocNo.ToLower().Contains(term.ToLower()) || t2.ProductName.ToLower().Contains(term.ToLower()) ||
JO.Dimension1.Dimension1Name.ToLower().Contains(term.ToLower()) || JO.Dimension2.Dimension2Name.ToLower().Contains(term.ToLower()) || JO.Dimension3.Dimension3Name.ToLower().Contains(term.ToLower()) || JO.Dimension4.Dimension4Name.ToLower().Contains(term.ToLower()) || VJB.ProductUidName.ToLower().Contains(term.ToLower()))) &&
(string.IsNullOrEmpty(settings.filterContraDocTypes) ? 1 == 1 : contraDocTypes.Contains(t3.DocTypeId.ToString())) &&
(string.IsNullOrEmpty(settings.filterContraSites) ? t3.SiteId == CurrentSiteId : contraSites.Contains(t3.SiteId.ToString())) &&
(string.IsNullOrEmpty(settings.filterContraDivisions) ? t3.DivisionId == CurrentDivisionId : contraDivisions.Contains(t3.DivisionId.ToString()))
orderby t3.DocNo
select new JobInvoiceHeaderListViewModel
{
DocNo = t3.DocNo,
JobInvoiceLineId = p.JobInvoiceLineId,
Dimension1Name = JO.Dimension1.Dimension1Name,
Dimension2Name = JO.Dimension2.Dimension2Name,
Dimension3Name = JO.Dimension3.Dimension3Name,
Dimension4Name = JO.Dimension4.Dimension4Name,
ProductName = t2.ProductName,
BalanceQty = (p.Qty),
ProductUidName = VJB.ProductUidName,
};
return(tem.Take(Limit).ToList());
}
public IEnumerable <JobInvoiceRateAmendmentLineViewModel> GetJobInvoiceLineForMultiSelect(JobInvoiceAmendmentFilterViewModel svm)
{
var Header = db.JobInvoiceAmendmentHeader.Find(svm.JobInvoiceAmendmentHeaderId);
var Settings = new JobInvoiceSettingsService(_unitOfWork).GetJobInvoiceSettingsForDocument(Header.DocTypeId, Header.DivisionId, Header.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[] ProductIdArr = null;
if (!string.IsNullOrEmpty(svm.ProductId))
{
ProductIdArr = svm.ProductId.Split(",".ToCharArray());
}
else
{
ProductIdArr = new string[] { "NA" };
}
string[] SaleOrderIdArr = null;
if (!string.IsNullOrEmpty(svm.JobInvoiceId))
{
SaleOrderIdArr = svm.JobInvoiceId.Split(",".ToCharArray());
}
else
{
SaleOrderIdArr = new string[] { "NA" };
}
string[] ProductGroupIdArr = null;
if (!string.IsNullOrEmpty(svm.ProductGroupId))
{
ProductGroupIdArr = svm.ProductGroupId.Split(",".ToCharArray());
}
else
{
ProductGroupIdArr = new string[] { "NA" };
}
string[] Dim1Id = null;
if (!string.IsNullOrEmpty(svm.ProductGroupId))
{
Dim1Id = svm.Dimension1Id.Split(",".ToCharArray());
}
else
{
Dim1Id = new string[] { "NA" };
}
string[] Dim2Id = null;
if (!string.IsNullOrEmpty(svm.ProductGroupId))
{
Dim2Id = svm.Dimension2Id.Split(",".ToCharArray());
}
else
{
Dim2Id = new string[] { "NA" };
}
string[] Dim3Id = null;
if (!string.IsNullOrEmpty(svm.ProductGroupId))
{
Dim3Id = svm.Dimension3Id.Split(",".ToCharArray());
}
else
{
Dim3Id = new string[] { "NA" };
}
string[] Dim4Id = null;
if (!string.IsNullOrEmpty(svm.ProductGroupId))
{
Dim4Id = svm.Dimension4Id.Split(",".ToCharArray());
}
else
{
Dim4Id = new string[] { "NA" };
}
var Query = (from VJ in db.ViewJobInvoiceBalanceForRateAmendment
join p in db.JobInvoiceLine on VJ.JobInvoiceLineId equals p.JobInvoiceLineId
join t2 in db.JobInvoiceHeader on p.JobInvoiceHeaderId equals t2.JobInvoiceHeaderId
join JR in db.JobReceiveLine on p.JobReceiveLineId equals JR.JobReceiveLineId
join JO in db.JobOrderLine on JR.JobOrderLineId equals JO.JobOrderLineId
join product in db.Product on JO.ProductId equals product.ProductId into table2
from tab2 in table2.DefaultIfEmpty()
join AL in db.JobInvoiceRateAmendmentLine on p.JobInvoiceLineId equals AL.JobInvoiceLineId into ALTable
from AlTab in ALTable.DefaultIfEmpty()
where
//(string.IsNullOrEmpty(svm.ProductId) ? 1 == 1 : ProductIdArr.Contains(JO.ProductId.ToString()))
//&& (string.IsNullOrEmpty(svm.JobInvoiceId) ? 1 == 1 : SaleOrderIdArr.Contains(p.JobInvoiceHeaderId.ToString()))
//&& (string.IsNullOrEmpty(svm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(tab2.ProductGroupId.ToString()))
//&& (string.IsNullOrEmpty(svm.Dimension1Id) ? 1 == 1 : Dim1Id.Contains(JO.Dimension1Id.ToString()))
//&& (string.IsNullOrEmpty(svm.Dimension2Id) ? 1 == 1 : Dim2Id.Contains(JO.Dimension2Id.ToString()))
//&& (string.IsNullOrEmpty(Settings.filterContraDocTypes) ? 1 == 1 : contraDocTypes.Contains(t2.DocTypeId.ToString()))
//&& (string.IsNullOrEmpty(Settings.filterContraDivisions) ? t2.DivisionId == Header.DivisionId : contraDivisions.Contains(t2.DivisionId.ToString()))
//&& (string.IsNullOrEmpty(Settings.filterContraSites) ? t2.SiteId == Header.SiteId : contraSites.Contains(t2.SiteId.ToString()))
//&& ((svm.JobWorkerId.HasValue && svm.JobWorkerId.Value > 0) ? p.JobWorkerId == svm.JobWorkerId : 1 == 1)
//&& ((svm.OldRate > 0) ? p.Rate == svm.OldRate : 1 == 1)
//&& (svm.UpToDate.HasValue ? t2.DocDate <= svm.UpToDate : 1 == 1)
//&&
p.Qty > 0 && t2.ProcessId == Header.ProcessId
orderby t2.DocDate, t2.DocNo, p.Sr
select new
{
Dimension1Name = JO.Dimension1.Dimension1Name,
Dimension2Name = JO.Dimension2.Dimension2Name,
Dimension3Name = JO.Dimension3.Dimension3Name,
Dimension4Name = JO.Dimension4.Dimension4Name,
UnitName = tab2.Unit.UnitName,
DealUnitName = p.DealUnit.UnitName,
DealQty = (p.Qty) * p.UnitConversionMultiplier,
UnitConversionMultiplier = p.UnitConversionMultiplier,
JobInvoiceRate = p.Rate,
AmendedRate = (svm.Rate == 0 ? p.Rate : svm.Rate),
Qty = (p.Qty),
JobInvoiceDocNo = t2.DocNo,
ProductName = tab2.ProductName,
ProductId = tab2.ProductId,
JobInvoiceAmendmentHeaderId = svm.JobInvoiceAmendmentHeaderId,
JobInvoiceLineId = p.JobInvoiceLineId,
unitDecimalPlaces = tab2.Unit.DecimalPlaces,
DealunitDecimalPlaces = p.DealUnit.DecimalPlaces,
JobWorkerId = p.JobWorkerId,
AAmended = (AlTab == null ? false : true),
JobInvoiceHeaderId = p.JobInvoiceHeaderId,
ProductGroupId = tab2.ProductGroupId,
Dimension1Id = JO.Dimension1Id,
Dimension2Id = JO.Dimension2Id,
Dimension3Id = JO.Dimension3Id,
Dimension4Id = JO.Dimension4Id,
DocTypeId = t2.DocTypeId,
DivisionId = t2.DivisionId,
SiteId = t2.SiteId,
DocDate = t2.DocDate,
});
if (!string.IsNullOrEmpty(svm.ProductId))
{
Query = Query.Where(m => ProductIdArr.Contains(m.ProductId.ToString()));
}
if (!string.IsNullOrEmpty(svm.JobInvoiceId))
{
Query = Query.Where(m => SaleOrderIdArr.Contains(m.JobInvoiceHeaderId.ToString()));
}
if (!string.IsNullOrEmpty(svm.ProductGroupId))
{
Query = Query.Where(m => ProductGroupIdArr.Contains(m.ProductGroupId.ToString()));
}
if (!string.IsNullOrEmpty(svm.Dimension1Id))
{
Query = Query.Where(m => Dim1Id.Contains(m.Dimension1Id.ToString()));
}
if (!string.IsNullOrEmpty(svm.Dimension2Id))
{
Query = Query.Where(m => Dim2Id.Contains(m.Dimension2Id.ToString()));
}
if (!string.IsNullOrEmpty(svm.Dimension3Id))
{
Query = Query.Where(m => Dim3Id.Contains(m.Dimension3Id.ToString()));
}
if (!string.IsNullOrEmpty(svm.Dimension4Id))
{
Query = Query.Where(m => Dim4Id.Contains(m.Dimension4Id.ToString()));
}
if (!string.IsNullOrEmpty(Settings.filterContraSites))
{
Query = Query.Where(m => contraSites.Contains(m.SiteId.ToString()));
}
else
{
Query = Query.Where(m => m.SiteId == Header.SiteId);
}
if (!string.IsNullOrEmpty(Settings.filterContraDivisions))
{
Query = Query.Where(m => contraDivisions.Contains(m.DivisionId.ToString()));
}
else
{
Query = Query.Where(m => m.DivisionId == Header.DivisionId);
}
if (!string.IsNullOrEmpty(Settings.filterContraDocTypes))
{
Query = Query.Where(m => contraDocTypes.Contains(m.DocTypeId.ToString()));
}
if (svm.OldRate > 0)
{
Query = Query.Where(m => m.JobInvoiceRate == svm.OldRate);
}
if (svm.JobWorkerId.HasValue && svm.JobWorkerId.Value > 0)
{
Query = Query.Where(m => m.JobWorkerId == svm.JobWorkerId);
}
if (svm.UpToDate.HasValue)
{
Query = Query.Where(m => m.DocDate <= svm.UpToDate);
}
return(from p in Query
select new JobInvoiceRateAmendmentLineViewModel
{
Dimension1Name = p.Dimension1Name,
Dimension2Name = p.Dimension2Name,
Dimension3Name = p.Dimension3Name,
Dimension4Name = p.Dimension4Name,
UnitName = p.UnitName,
DealUnitName = p.UnitName,
DealQty = p.DealQty,
UnitConversionMultiplier = p.UnitConversionMultiplier,
JobInvoiceRate = p.JobInvoiceRate,
AmendedRate = p.AmendedRate,
Qty = (p.Qty),
JobInvoiceDocNo = p.JobInvoiceDocNo,
ProductName = p.ProductName,
ProductId = p.ProductId,
JobInvoiceAmendmentHeaderId = p.JobInvoiceAmendmentHeaderId,
JobInvoiceLineId = p.JobInvoiceLineId,
unitDecimalPlaces = p.unitDecimalPlaces,
DealunitDecimalPlaces = p.DealunitDecimalPlaces,
JobWorkerId = p.JobWorkerId,
AAmended = p.AAmended,
});
}
