public IEnumerable <ComboBoxList> GetPendingProductsForSaleDelivery(int sid, string term)//DocTypeId
{
var SaleDelivery = new SaleDeliveryOrderHeaderService(_unitOfWork).Find(sid);
// var settings = new JobInvoiceSettingsService(_unitOfWork).GetJobInvoiceSettingsForDocument(JobInvoice.DocTypeId, JobInvoice.DivisionId, JobInvoice.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" }; }
return((from p in db.ViewSaleOrderBalanceForCancellation
join t in db.Product on p.ProductId equals t.ProductId into ProdTable
from ProTab in ProdTable.DefaultIfEmpty()
where p.BalanceQty > 0 && ProTab.ProductName.ToLower().Contains(term.ToLower()) && SaleDelivery.BuyerId == p.BuyerId
group new { p, ProTab } by p.ProductId into g
orderby g.Key descending
select new ComboBoxList
{
Id = g.Key,
PropFirst = g.Max(m => m.ProTab.ProductName)
}
).Take(20));
}
public IQueryable <LedgerHeaderViewModel> GetLedgerHeaderList(int id, string Uname)//Document Category Id
{
int SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"];
int DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];
List <string> UserRoles = (List <string>)System.Web.HttpContext.Current.Session["Roles"];
return(from p in _ledgerHeaderRepository.Instance
join t in _unitOfWork.Repository <LedgerLine>().Instance on p.LedgerHeaderId equals t.LedgerHeaderId into ProdTable
from ProTab in ProdTable.DefaultIfEmpty()
where p.DocTypeId == id && p.SiteId == SiteId && p.DivisionId == DivisionId
group new { p, ProTab } by p.LedgerHeaderId into g
orderby g.Key descending
select new LedgerHeaderViewModel
{
CreditDays = g.Max(m => m.p.CreditDays),
DivisionId = g.Max(m => m.p.DivisionId),
DocDate = g.Max(m => m.p.DocDate),
DocNo = g.Max(m => m.p.DocNo),
DocTypeName = g.Max(m => m.p.DocType.DocumentTypeName),
LedgerAccountName = g.Max(m => m.p.LedgerAccount.LedgerAccountName) + " {" + g.Max(m => m.p.LedgerAccount.LedgerAccountSuffix) + "}",
Narration = g.Max(m => m.p.Narration),
ModifiedBy = g.Max(m => m.p.ModifiedBy),
Remark = g.Max(m => m.p.Remark),
SiteName = g.Max(m => m.p.Site.SiteName),
Status = g.Max(m => m.p.Status),
LedgerHeaderId = g.Max(m => m.p.LedgerHeaderId),
AccountName = g.Max(m => m.ProTab.LedgerAccount.LedgerAccountName) + " {" + g.Max(m => m.ProTab.LedgerAccount.LedgerAccountSuffix) + "}",
ReviewCount = g.Max(m => m.p.ReviewCount),
ReviewBy = g.Max(m => m.p.ReviewBy),
Reviewed = (SqlFunctions.CharIndex(Uname, g.Max(m => m.p.ReviewBy)) > 0),
});
}
public IQueryable <LedgerHeaderViewModel> GetLedgerHeaderList(int id, string Uname)//Document Category Id
{
int SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"];
int DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];
List <string> UserRoles = (List <string>)System.Web.HttpContext.Current.Session["Roles"];
//return (from p in db.LedgerHeader
// where p.DocTypeId == id && p.SiteId == SiteId && p.DivisionId == DivisionId && (p.Status == 0 ? (p.ModifiedBy == Uname || UserRoles.Contains("Admin")) : 1 == 1)
// orderby p.DocDate descending, p.DocNo descending
// select new LedgerHeaderViewModel
// {
// CreditDays = p.CreditDays,
// DivisionId = p.DivisionId,
// DocDate = p.DocDate,
// DocNo=p.DocNo,
// DocTypeName = p.DocType.DocumentTypeName,
// LedgerAccountName = p.LedgerAccount.LedgerAccountName,
// Narration = p.Narration,
// ModifiedBy=p.ModifiedBy,
// Remark = p.Remark,
// SiteName = p.Site.SiteName,
// Status = p.Status,
// LedgerHeaderId=p.LedgerHeaderId,
// });
return(from p in db.LedgerHeader
join t in db.LedgerLine on p.LedgerHeaderId equals t.LedgerHeaderId into ProdTable
from ProTab in ProdTable.DefaultIfEmpty()
where p.DocTypeId == id && p.SiteId == SiteId && p.DivisionId == DivisionId
group new { p, ProTab } by p.LedgerHeaderId into g
orderby g.Key descending
select new LedgerHeaderViewModel
{
CreditDays = g.Max(m => m.p.CreditDays),
DivisionId = g.Max(m => m.p.DivisionId),
DocDate = g.Max(m => m.p.DocDate),
DocNo = g.Max(m => m.p.DocNo),
DocTypeName = g.Max(m => m.p.DocType.DocumentTypeName),
//LedgerAccountName = g.Max(m => m.p.LedgerAccount.LedgerAccountName) + " {" + g.Max(m => m.p.LedgerAccount.LedgerAccountSuffix) + "}",
LedgerAccountName = g.Max(m => m.p.LedgerAccount.LedgerAccountName + ", " + (m.p.LedgerAccount.PersonId != null ? m.p.LedgerAccount.Person.Suffix + " [" + m.p.LedgerAccount.Person.Code + "]" : m.p.LedgerAccount.LedgerAccountSuffix)),
Narration = g.Max(m => m.p.Narration),
ModifiedBy = g.Max(m => m.p.ModifiedBy),
Remark = g.Max(m => m.p.Remark),
SiteName = g.Max(m => m.p.Site.SiteName),
Status = g.Max(m => m.p.Status),
LedgerHeaderId = g.Max(m => m.p.LedgerHeaderId),
//AccountName = g.Max(m => m.ProTab.LedgerAccount.LedgerAccountName) + " {" + g.Max(m => m.ProTab.LedgerAccount.LedgerAccountSuffix) + "}",
//AccountName = g.Max(m => m.ProTab.LedgerAccount.LedgerAccountName + "," + m.ProTab.LedgerAccount.LedgerAccountSuffix),
AccountName = g.Max(m => m.ProTab.LedgerAccount.LedgerAccountName + ", " + (m.ProTab.LedgerAccount.PersonId != null ? m.ProTab.LedgerAccount.Person.Suffix + " [" + m.ProTab.LedgerAccount.Person.Code + "]" : m.ProTab.LedgerAccount.LedgerAccountSuffix)),
ReviewCount = g.Max(m => m.p.ReviewCount),
ReviewBy = g.Max(m => m.p.ReviewBy),
Reviewed = (SqlFunctions.CharIndex(Uname, g.Max(m => m.p.ReviewBy)) > 0),
TotalAmount = g.Sum(m => m.ProTab.Amount),
});
}
public IEnumerable <ComboBoxList> GetPendingJobOrders(string DocTypes, string term, int JobOrderCancelHeaderId)//DocTypeId
{
JobOrderCancelHeader header = new JobOrderCancelHeaderService(_unitOfWork).Find(JobOrderCancelHeaderId);
return(from p in db.ViewJobOrderBalance
join t in db.JobOrderHeader on p.JobOrderHeaderId equals t.JobOrderHeaderId into ProdTable
from ProTab in ProdTable.DefaultIfEmpty()
where p.BalanceQty > 0 && ProTab.DocNo.ToLower().Contains(term.ToLower()) &&
p.SiteId == header.SiteId && p.DivisionId == header.DivisionId && p.JobWorkerId == header.JobWorkerId
group new { p, ProTab } by p.JobOrderHeaderId into g
select new ComboBoxList
{
Id = g.Key,
PropFirst = g.Max(m => m.ProTab.DocNo),
}
);
}
public IQueryable <ComboBoxResult> GetPendingProductsForSaleDelivery(int id, string term)//DocTypeId
{
var SaleDelivery = new SaleDeliveryHeaderService(_unitOfWork).Find(id);
var settings = new SaleDeliverySettingService(_unitOfWork).GetSaleDeliverySettingForDocument(SaleDelivery.DocTypeId, SaleDelivery.DivisionId, SaleDelivery.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" };
}
return(from p in db.ViewSaleInvoiceBalanceForDelivery
join t in db.Product on p.ProductId equals t.ProductId into ProdTable
from ProTab in ProdTable.DefaultIfEmpty()
where p.BalanceQty > 0 && p.SaleToBuyerId == SaleDelivery.SaleToBuyerId &&
(string.IsNullOrEmpty(settings.filterContraSites) ? p.SiteId == SaleDelivery.SiteId : ContraSites.Contains(p.SiteId.ToString())) &&
(string.IsNullOrEmpty(settings.filterContraDivisions) ? p.DivisionId == SaleDelivery.DivisionId : ContraDivisions.Contains(p.DivisionId.ToString())) &&
(string.IsNullOrEmpty(term) ? 1 == 1 : ProTab.ProductName.ToLower().Contains(term.ToLower()))
group new { p, ProTab } by p.ProductId into g
orderby g.Key descending
select new ComboBoxResult
{
id = g.Key.ToString(),
text = g.Max(m => m.ProTab.ProductName)
}
);
}
public IQueryable <ComboBoxResult> GetPendingProductsForJobOrderInspectionRequestCancel(string term, int JobOrderInspectionRequestCancelHeaderId)//DocTypeId
{
JobOrderInspectionRequestCancelHeader header = new JobOrderInspectionRequestCancelHeaderService(db).Find(JobOrderInspectionRequestCancelHeaderId);
var settings = new JobOrderInspectionRequestSettingsService(db).GetJobOrderInspectionRequestSettingsForDocument(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" };
}
var Query = (from p in db.ViewJobOrderInspectionRequestBalance
join jir in db.JobOrderInspectionRequestHeader on p.JobOrderInspectionRequestHeaderId equals jir.JobOrderInspectionRequestHeaderId
join t in db.Product on p.ProductId equals t.ProductId into ProdTable
from ProTab in ProdTable.DefaultIfEmpty()
where p.BalanceQty > 0 &&
p.SiteId == header.SiteId && p.DivisionId == header.DivisionId && p.JobWorkerId == header.JobWorkerId && jir.ProcessId == header.ProcessId
select new
{
ProductId = p.ProductId,
ProductName = ProTab.ProductName,
DocTypeId = jir.DocTypeId,
SiteId = jir.SiteId,
DivisionId = jir.DivisionId,
}
);
if (!string.IsNullOrEmpty(term))
{
Query = Query.Where(m => m.ProductName.ToLower().Contains(term.ToLower()));
}
if (!string.IsNullOrEmpty(settings.filterContraDocTypes))
{
Query = Query.Where(m => contraDocTypes.Contains(m.DocTypeId.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);
}
return(from p in Query
group p by p.ProductId into g
orderby g.Max(m => m.ProductName)
select new ComboBoxResult
{
text = g.Max(m => m.ProductName),
id = g.Key.ToString(),
});
}
public IQueryable <ComboBoxResult> GetPendingProductsForProdOrderCancel(int filter, string term)//DocTypeId
{
var HEader = new ProdOrderCancelHeaderService(_unitOfWork).Find(filter);
var settings = new ProdOrderSettingsService(_unitOfWork).GetProdOrderSettingsForDocument(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[] filterProducts = null;
if (!string.IsNullOrEmpty(settings.filterProducts))
{
filterProducts = settings.filterProducts.Split(",".ToCharArray());
}
else
{
filterProducts = new string[] { "NA" };
}
string[] filterProductTypes = null;
if (!string.IsNullOrEmpty(settings.filterProductTypes))
{
filterProductTypes = settings.filterProductTypes.Split(",".ToCharArray());
}
else
{
filterProductTypes = new string[] { "NA" };
}
string[] filterProductGroups = null;
if (!string.IsNullOrEmpty(settings.filterProductGroups))
{
filterProductGroups = settings.filterProductGroups.Split(",".ToCharArray());
}
else
{
filterProductGroups = new string[] { "NA" };
}
int CurrentSiteId = (int)System.Web.HttpContext.Current.Session["SiteId"];
int CurrentDivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];
var Query = from p in db.ViewProdOrderBalance
join t in db.ProdOrderHeader on p.ProdOrderHeaderId equals t.ProdOrderHeaderId into ProdTable
from ProTab in ProdTable.DefaultIfEmpty()
join Prod in db.Product on p.ProductId equals Prod.ProductId
where p.BalanceQty > 0
select new
{
ProductName = Prod.ProductName,
DocTypeId = p.DocTypeId,
ProductId = p.ProductId,
ProductGroupId = Prod.ProductGroupId,
ProductTypeId = Prod.ProductGroup.ProductTypeId,
SiteId = ProTab.SiteId,
DivisionId = ProTab.DivisionId,
};
if (!string.IsNullOrEmpty(term))
{
Query = Query.Where(m => m.ProductName.ToLower().Contains(term.ToLower()));
}
if (!string.IsNullOrEmpty(settings.filterContraDocTypes))
{
Query = Query.Where(m => contraDocTypes.Contains(m.DocTypeId.ToString()));
}
if (!string.IsNullOrEmpty(settings.filterProducts))
{
Query = Query.Where(m => filterProducts.Contains(m.ProductId.ToString()));
}
if (!string.IsNullOrEmpty(settings.filterProductGroups))
{
Query = Query.Where(m => filterProductGroups.Contains(m.ProductGroupId.ToString()));
}
if (!string.IsNullOrEmpty(settings.filterProductTypes))
{
Query = Query.Where(m => filterProductTypes.Contains(m.ProductTypeId.ToString()));
}
if (!string.IsNullOrEmpty(settings.filterContraSites))
{
Query = Query.Where(m => contraSites.Contains(m.SiteId.ToString()));
}
else
{
Query = Query.Where(m => m.SiteId == CurrentSiteId);
}
if (!string.IsNullOrEmpty(settings.filterContraDivisions))
{
Query = Query.Where(m => contraDivisions.Contains(m.DivisionId.ToString()));
}
else
{
Query = Query.Where(m => m.DivisionId == CurrentDivisionId);
}
return(from p in Query
group p by p.ProductId into g
orderby g.Max(m => m.ProductName)
select new ComboBoxResult
{
id = g.Key.ToString(),
text = g.Max(m => m.ProductName),
});
}