public IEnumerable <SaleDispatchListViewModel> GetPendingReceipts(int id, int SaleGoodsReturnHeaderId)
{
var SaleGoodsReturn = new SaleDispatchReturnHeaderService(_unitOfWork).Find(SaleGoodsReturnHeaderId);
var settings = new SaleDispatchSettingService(_unitOfWork).GetSaleDispatchSettingForDocument(SaleGoodsReturn.DocTypeId, SaleGoodsReturn.DivisionId, SaleGoodsReturn.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"];
return(from p in db.ViewSaleDispatchBalance
join t in db.SaleDispatchHeader on p.SaleDispatchHeaderId equals t.SaleDispatchHeaderId into table
from tab in table.DefaultIfEmpty()
join t1 in db.SaleOrderLine on p.SaleOrderLineId equals t1.SaleOrderLineId into table1
from tab1 in table1.DefaultIfEmpty()
where p.ProductId == id && tab.SaleToBuyerId == SaleGoodsReturn.BuyerId && 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()))
select new SaleDispatchListViewModel
{
SaleDispatchLineId = p.SaleDispatchLineId,
SaleDispatchHeaderId = p.SaleDispatchHeaderId,
DocNo = tab.DocNo,
Dimension1Name = tab1.Dimension1.Dimension1Name,
Dimension2Name = tab1.Dimension2.Dimension2Name,
}
);
}
public IEnumerable <ComboBoxResult> GetPendingOrdersForDispatch(int id, string term)
{
var SaleDispatchHeader = new SaleDispatchHeaderService(_unitOfWork).Find(id);
var settings = new SaleDispatchSettingService(_unitOfWork).GetSaleDispatchSettingForDocument(SaleDispatchHeader.DocTypeId, SaleDispatchHeader.DivisionId, SaleDispatchHeader.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"];
return(from p in db.ViewSaleOrderBalance
join t in db.SaleOrderHeader on p.SaleOrderHeaderId equals t.SaleOrderHeaderId into table
from tab in table.DefaultIfEmpty()
where p.BalanceQty > 0 &&
(string.IsNullOrEmpty(settings.filterContraDocTypes) ? 1 == 1 : contraDocTypes.Contains(tab.DocTypeId.ToString())) &&
(string.IsNullOrEmpty(settings.filterContraSites) ? tab.SiteId == CurrentSiteId : contraSites.Contains(tab.SiteId.ToString())) &&
(string.IsNullOrEmpty(settings.filterContraDivisions) ? tab.DivisionId == CurrentDivisionId : contraDivisions.Contains(tab.DivisionId.ToString())) &&
(string.IsNullOrEmpty(term) ? 1 == 1 : p.SaleOrderNo.ToLower().Contains(term.ToLower()))
group p by p.SaleOrderHeaderId into g
select new ComboBoxResult
{
id = g.Key.ToString(),
text = g.Max(m => m.SaleOrderNo),
}
);
}
public IQueryable <ComboBoxResult> GetCustomProducts(int Id, string term)
{
var SaleDispatch = new SaleDispatchHeaderService(_unitOfWork).Find(Id);
var settings = new SaleDispatchSettingService(_unitOfWork).GetSaleDispatchSettingForDocument(SaleDispatch.DocTypeId, SaleDispatch.DivisionId, SaleDispatch.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 p in db.Product
where (string.IsNullOrEmpty(settings.filterProductTypes) ? 1 == 1 : ProductTypes.Contains(p.ProductGroup.ProductTypeId.ToString())) &&
(string.IsNullOrEmpty(settings.filterProducts) ? 1 == 1 : Products.Contains(p.ProductId.ToString())) &&
(string.IsNullOrEmpty(settings.filterProductGroups) ? 1 == 1 : ProductGroups.Contains(p.ProductGroupId.ToString())) &&
(string.IsNullOrEmpty(term) ? 1 == 1 : p.ProductName.ToLower().Contains(term.ToLower()))
orderby p.ProductName
select new ComboBoxResult
{
id = p.ProductId.ToString(),
text = p.ProductName,
});
}
public IQueryable <ComboBoxResult> GetCustomPerson(int Id, string term)
{
int DocTypeId = Id;
int SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"];
int DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];
var settings = new SaleDispatchSettingService(_unitOfWork).GetSaleDispatchSettingForDocument(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 &&
(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 IQueryable <ComboBoxResult> GetPendingProductsForSaleDispatch(int id, string term)//DocTypeId
{
var SaleDispatch = new SaleDispatchHeaderService(_unitOfWork).Find(id);
var settings = new SaleDispatchSettingService(_unitOfWork).GetSaleDispatchSettingForDocument(SaleDispatch.DocTypeId, SaleDispatch.DivisionId, SaleDispatch.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.ViewSaleOrderBalance
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()) && p.BuyerId == SaleDispatch.SaleToBuyerId &&
(string.IsNullOrEmpty(settings.filterContraSites) ? p.SiteId == SaleDispatch.SiteId : ContraSites.Contains(p.SiteId.ToString())) &&
(string.IsNullOrEmpty(settings.filterContraDivisions) ? p.DivisionId == SaleDispatch.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 IEnumerable <ComboBoxResult> GetPendingStockInForDispatch(int SaleDispatchHeaderId, int GodownId, int ProductId, int?Dimension1Id, int?Dimension2Id, string term)
{
var SaleDispatchHeader = new SaleDispatchHeaderService(_unitOfWork).Find(SaleDispatchHeaderId);
var settings = new SaleDispatchSettingService(_unitOfWork).GetSaleDispatchSettingForDocument(SaleDispatchHeader.DocTypeId, SaleDispatchHeader.DivisionId, SaleDispatchHeader.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" };
}
int CurrentSiteId = (int)System.Web.HttpContext.Current.Session["SiteId"];
int CurrentDivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];
SqlParameter SqlParameterSaleDispatchHeaderId = new SqlParameter("@SaleDispatchHeaderId", SaleDispatchHeaderId);
SqlParameter SqlParameterProductId = new SqlParameter("@ProductId", ProductId);
SqlParameter SqlParameterGodownId = new SqlParameter("@GodownId", GodownId);
SqlParameter SqlParameterDimension1Id = new SqlParameter("@Dimension1Id", Dimension1Id);
SqlParameter SqlParameterDimension2Id = new SqlParameter("@Dimension2Id", Dimension2Id);
if (Dimension1Id == null)
{
SqlParameterDimension1Id.Value = DBNull.Value;
}
if (Dimension2Id == null)
{
SqlParameterDimension2Id.Value = DBNull.Value;
}
IEnumerable <PendingStockInForDispatch> PendingStockInForDispatch = db.Database.SqlQuery <PendingStockInForDispatch>("" + ConfigurationManager.AppSettings["DataBaseSchema"] + ".spGetHelpListPendingStockInForDispatch @SaleDispatchHeaderId, @GodownId, @ProductId, @Dimension1Id, @Dimension2Id", SqlParameterSaleDispatchHeaderId, SqlParameterGodownId, SqlParameterProductId, SqlParameterDimension1Id, SqlParameterDimension2Id).ToList();
return(from p in PendingStockInForDispatch
where (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(term) ? 1 == 1 : p.StockInNo.ToLower().Contains(term.ToLower()))
select new ComboBoxResult
{
id = p.StockInId.ToString(),
text = p.StockInNo,
TextProp1 = "Lot No :" + p.LotNo,
TextProp2 = "Balance :" + p.BalanceQty,
AProp1 = p.ProductName + ", " + p.Dimension1Name + ", " + p.Dimension2Name,
AProp2 = "Date :" + p.StockInDate
});
//return (from p in db.ViewStockInBalance
// 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.BalanceQty > 0
// && p.PersonId == SaleDispatchHeader.SaleToBuyerId
// && p.Dimension1Id == Dimension1Id
// && (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(term) ? 1 == 1 : p.StockInNo.ToLower().Contains(term.ToLower()))
// select new ComboBoxResult
// {
// id = p.StockInId.ToString(),
// text = p.StockInNo,
// TextProp1 = "Lot No :" + p.LotNo,
// TextProp2 = "Balance :" + p.BalanceQty,
// AProp1 = ProductTab.ProductName + ", " + Dimension1Tab.Dimension1Name + ", " + Dimension2Tab.Dimension2Name,
// AProp2 = "Date :" + p.StockInDate
// });
}
public IQueryable <ComboBoxResult> GetSaleOrderHelpListForProduct(int filter, string term)
{
var SaleDispatchHeader = new SaleDispatchHeaderService(_unitOfWork).Find(filter);
var settings = new SaleDispatchSettingService(_unitOfWork).GetSaleDispatchSettingForDocument(SaleDispatchHeader.DocTypeId, SaleDispatchHeader.DivisionId, SaleDispatchHeader.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.ViewSaleOrderBalance
join t in db.SaleOrderHeader on p.SaleOrderHeaderId equals t.SaleOrderHeaderId
join t2 in db.SaleOrderLine on p.SaleOrderLineId equals t2.SaleOrderLineId
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.BuyerId == SaleDispatchHeader.SaleToBuyerId &&
((string.IsNullOrEmpty(term) ? 1 == 1 : p.SaleOrderNo.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()))
orderby t.DocDate, t.DocNo
select new ComboBoxResult
{
text = ProductTab.ProductName,
id = p.SaleOrderLineId.ToString(),
TextProp1 = "Order No: " + p.SaleOrderNo.ToString(),
TextProp2 = "BalQty: " + p.BalanceQty.ToString(),
AProp1 = Dimension1Tab.Dimension1Name,
AProp2 = Dimension2Tab.Dimension2Name
});
return(list);
}
