public IEnumerable <ComboBoxList> GetProductHelpList(int Id, string term)
{
var PurchaseOrderCancel = new PurchaseOrderCancelHeaderService(_unitOfWork).Find(Id);
var settings = new PurchaseOrderSettingService(_unitOfWork).GetPurchaseOrderSettingForDocument(PurchaseOrderCancel.DocTypeId, PurchaseOrderCancel.DivisionId, PurchaseOrderCancel.SiteId);
string[] ProductTypes = null;
if (!string.IsNullOrEmpty(settings.filterProductTypes))
{
ProductTypes = settings.filterProductTypes.Split(",".ToCharArray());
}
else
{
ProductTypes = new string[] { "NA" };
}
var list = (from p in db.Product
where (string.IsNullOrEmpty(term) ? 1 == 1 : p.ProductName.ToLower().Contains(term.ToLower())) &&
(string.IsNullOrEmpty(settings.filterProductTypes) ? 1 == 1 : ProductTypes.Contains(p.ProductGroup.ProductTypeId.ToString()))
select new ComboBoxList
{
PropFirst = p.ProductName,
Id = p.ProductId,
}
).Take(20);
return(list.ToList());
}
public List <String> GetProcGenProductUids(int DocTypeId, decimal Qty, int DivisionId, int SiteId)
{
string ProcName = new PurchaseOrderSettingService(_unitOfWork).GetPurchaseOrderSettingForDocument(DocTypeId, DivisionId, SiteId).SqlProcGenProductUID;
List <string> CalculationLineList = new List <String>();
if (!string.IsNullOrEmpty(ProcName))
{
using (SqlConnection sqlConnection = new SqlConnection((string)System.Web.HttpContext.Current.Session["DefaultConnectionString"]))
{
sqlConnection.Open();
int TypeId = DocTypeId;
SqlCommand Totalf = new SqlCommand("SELECT * FROM " + ProcName + "( " + TypeId + ", " + Qty + ")", sqlConnection);
SqlDataReader ExcessStockQty = (Totalf.ExecuteReader());
while (ExcessStockQty.Read())
{
CalculationLineList.Add((string)ExcessStockQty.GetValue(0));
}
}
}
//IEnumerable<string> CalculationLineList = db.Database.SqlQuery<string>("SELECT * FROM " + ProcName + " ("+ SqlParameterDocType+"," +SqlParameterQty+") ").ToList();
return(CalculationLineList.ToList());
}
public IQueryable <ComboBoxResult> GetPendingProductsForRateAmndmt(int id, string term)//DocTypeId
{
var PurchaseOrderAmendmentHeader = new PurchaseOrderAmendmentHeaderService(db).Find(id);
var settings = new PurchaseOrderSettingService(_unitOfWork).GetPurchaseOrderSettingForDocument(PurchaseOrderAmendmentHeader.DocTypeId, PurchaseOrderAmendmentHeader.DivisionId, PurchaseOrderAmendmentHeader.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 p in db.ViewPurchaseOrderBalanceForInvoice
join t in db.PurchaseOrderHeader on p.PurchaseOrderHeaderId equals t.PurchaseOrderHeaderId
join t2 in db.Product on p.ProductId equals t2.ProductId
where (string.IsNullOrEmpty(term) ? 1 == 1 : t2.ProductName.ToLower().Contains(term.ToLower())) && p.BalanceQty > 0 &&
(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())) &&
(t.SupplierId == PurchaseOrderAmendmentHeader.SupplierId)
group new { p, t2 } by p.ProductId into g
orderby g.Max(m => m.t2.ProductName)
select new ComboBoxResult
{
text = g.Max(m => m.t2.ProductName),
id = g.Key.ToString(),
}
);
return(list);
}
public IEnumerable <PurchaseOrderLineListViewModel> GetPendingPurchaseOrdersForOrderCancel(int ProductId, int PurchaseOrderCancelHeaderId)//Product Id
{
var PurchaseOrderCancel = new PurchaseOrderCancelHeaderService(_unitOfWork).Find(PurchaseOrderCancelHeaderId);
var settings = new PurchaseOrderSettingService(_unitOfWork).GetPurchaseOrderSettingForDocument(PurchaseOrderCancel.DocTypeId, PurchaseOrderCancel.DivisionId, PurchaseOrderCancel.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 p in db.ViewPurchaseOrderBalance
join t in db.PurchaseOrderLine on p.PurchaseOrderLineId equals t.PurchaseOrderLineId into table
from tab in table.DefaultIfEmpty()
where p.ProductId == ProductId && p.BalanceQty > 0 && p.SupplierId == PurchaseOrderCancel.SupplierId &&
(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()))
orderby p.PurchaseOrderNo
select new PurchaseOrderLineListViewModel
{
DocNo = p.PurchaseOrderNo,
PurchaseOrderLineId = p.PurchaseOrderLineId,
Dimension1Name = tab.Dimension1.Dimension1Name,
Dimension2Name = tab.Dimension2.Dimension2Name,
};
return(tem);
}
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 PurchaseOrderSettingService(_unitOfWork).GetPurchaseOrderSettingForDocument(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.ViewPurchaseOrderBalance
join t in db.Product on p.ProductId equals t.ProductId
where (string.IsNullOrEmpty(term) ? 1 == 1 : p.PurchaseOrderNo.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()))
group new { t } by p.ProductId into g
orderby g.Max(m => m.t.ProductName)
select new ComboBoxResult
{
text = g.Max(m => m.t.ProductName),
id = (g.Key).ToString(),
// DocumentTypeName=g.Max(p=>p.p.DocumentTypeShortName)
}
);
return(list);
}
public IEnumerable <PurchaseIndentLineListViewModel> GetPendingPurchaseIndentHelpList(int Id, string term)
{
var PurchaseOrder = new PurchaseOrderHeaderService(_unitOfWork).Find(Id);
var settings = new PurchaseOrderSettingService(_unitOfWork).GetPurchaseOrderSettingForDocument(PurchaseOrder.DocTypeId, PurchaseOrder.DivisionId, PurchaseOrder.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 p in db.ViewPurchaseIndentBalance
where (string.IsNullOrEmpty(term) ? 1 == 1 : p.PurchaseIndentNo.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()))
group new { p } by p.PurchaseIndentHeaderId into g
select new PurchaseIndentLineListViewModel
{
DocNo = g.Max(m => m.p.PurchaseIndentNo) + " | " + g.Max(m => m.p.DocType.DocumentTypeShortName),
PurchaseIndentHeaderId = g.Key,
// DocumentTypeName=g.Max(p=>p.p.DocumentTypeShortName)
}
).Take(20);
return(list.ToList());
}
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 PurchaseOrderSettingService(_unitOfWork).GetPurchaseOrderSettingForDocument(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 IEnumerable <PurchaseOrderListViewModel> GetPendingPurchaseOrdersForRateAmndmt(int HeaderId, string term, int Limit)//Product Id
{
var Header = new PurchaseOrderAmendmentHeaderService(db).Find(HeaderId);
var settings = new PurchaseOrderSettingService(_unitOfWork).GetPurchaseOrderSettingForDocument(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 p in db.ViewPurchaseOrderBalanceForInvoice
join t in db.PurchaseOrderLine on p.PurchaseOrderLineId equals t.PurchaseOrderLineId
join t2 in db.Product on p.ProductId equals t2.ProductId
join t3 in db.PurchaseOrderHeader on p.PurchaseOrderHeaderId equals t3.PurchaseOrderHeaderId
where p.BalanceQty > 0 && (p.SupplierId == Header.SupplierId) &&
(string.IsNullOrEmpty(term) ? 1 == 1 : (p.PurchaseOrderNo.ToLower().Contains(term.ToLower()) || t2.ProductName.ToLower().Contains(term.ToLower()) ||
t.Dimension1.Dimension1Name.ToLower().Contains(term.ToLower()) || t.Dimension2.Dimension2Name.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 p.PurchaseOrderNo
select new PurchaseOrderListViewModel
{
DocNo = p.PurchaseOrderNo,
PurchaseOrderLineId = p.PurchaseOrderLineId,
Dimension1Name = t.Dimension1.Dimension1Name,
Dimension2Name = t.Dimension2.Dimension2Name,
ProductName = t2.ProductName,
BalanceQty = p.BalanceQty,
};
return(tem.Take(Limit).ToList());
}