public IQueryable <SaleDeliveryOrderCancelHeaderViewModel> GetSaleDeliveryOrderCancelHeaderList(int id, string Uname) { var DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"]; var SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"]; List <string> UserRoles = (List <string>)System.Web.HttpContext.Current.Session["Roles"]; return(from p in db.SaleDeliveryOrderCancelHeader join t in db._Users on p.ModifiedBy equals t.UserName into table from tab in table.DefaultIfEmpty() orderby p.DocDate descending, p.DocNo ascending where p.SiteId == SiteId && p.DivisionId == DivisionId && p.DocTypeId == id select new SaleDeliveryOrderCancelHeaderViewModel { DocDate = p.DocDate, DocNo = p.DocNo, SaleDeliveryOrderCancelHeaderId = p.SaleDeliveryOrderCancelHeaderId, ReasonName = p.Reason.ReasonName, Remark = p.Remark, Status = p.Status, BuyerName = p.Buyer.Person.Name, ModifiedBy = p.ModifiedBy, //FirstName = tab.FirstName, ReviewCount = p.ReviewCount, ReviewBy = p.ReviewBy, Reviewed = (SqlFunctions.CharIndex(Uname, p.ReviewBy) > 0), } ); }
public IQueryable <PurchaseQuotationHeaderViewModel> GetPurchaseQuotationHeaderList(int id, string name) { var DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"]; var SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"]; List <string> UserRoles = (List <string>)System.Web.HttpContext.Current.Session["Roles"]; return(from p in db.PurchaseQuotationHeader orderby p.DocDate descending, p.DocNo descending where p.SiteId == SiteId && p.DivisionId == DivisionId && p.DocTypeId == id && p.DocTypeId == id select new PurchaseQuotationHeaderViewModel { DocDate = p.DocDate, DocNo = p.DocNo, Remark = p.Remark, Status = p.Status, PurchaseQuotationHeaderId = p.PurchaseQuotationHeaderId, ModifiedBy = p.ModifiedBy, ReviewCount = p.ReviewCount, ReviewBy = p.ReviewBy, VendorQuotationDate = p.VendorQuotationDate, VendorQuotationNo = p.VendorQuotationNo, Reviewed = (SqlFunctions.CharIndex(name, p.ReviewBy) > 0), SupplierName = p.Supplier.Person.Name, }); }
public IQueryable <OverTimeApplicationHeaderViewModel> GetOverTimeApplicationHeaderList(int id, string Uname) { var DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"]; var SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"]; List <string> UserRoles = (List <string>)System.Web.HttpContext.Current.Session["Roles"]; return(from p in db.OverTimeApplicationHeader join t in db._Users on p.ModifiedBy equals t.UserName into table from tab in table.DefaultIfEmpty() orderby p.DocDate descending, p.DocNo descending where p.SiteId == SiteId && p.DocTypeId == id select new OverTimeApplicationHeaderViewModel { DocDate = p.DocDate, DocNo = p.DocNo, DocTypeName = p.DocType.DocumentTypeName, OverTimeApplicationId = p.OverTimeApplicationId, Remark = p.Remark, Name = p.Person.Name, Status = p.Status, ModifiedBy = p.ModifiedBy, ReviewCount = p.ReviewCount, ReviewBy = p.ReviewBy, DepartmentName = p.Department.DepartmentName, GodownId = p.GodownId, Reviewed = (SqlFunctions.CharIndex(Uname, p.ReviewBy) > 0), } ); }
public IQueryable <ExcessMaterialHeaderViewModel> GetExcessMaterialHeaderList(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.ExcessMaterialHeader orderby p.DocDate descending, p.DocNo descending where p.SiteId == SiteId && p.DivisionId == DivisionId && p.DocTypeId == DocTypeId select new ExcessMaterialHeaderViewModel { CurrencyName = p.Currency.Name, DivisionName = p.Division.DivisionName, DocDate = p.DocDate, DocNo = p.DocNo, DocTypeId = p.DocTypeId, DocTypeName = p.DocType.DocumentTypeName, GodownName = p.Godown.GodownName, PersonName = p.Person.Name, ProcessName = p.Process.ProcessName, Remark = p.Remark, Status = p.Status, ExcessMaterialHeaderId = p.ExcessMaterialHeaderId, ModifiedBy = p.ModifiedBy, ReviewCount = p.ReviewCount, ReviewBy = p.ReviewBy, Reviewed = (SqlFunctions.CharIndex(UName, p.ReviewBy) > 0), } ); }
public IQueryable <DyeingViewModel> GetDyeingList(int DocumentTypeId, string Uname) { var DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"]; var SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"]; List <string> UserRoles = (List <string>)System.Web.HttpContext.Current.Session["Roles"]; return(from p in _DyeingRepository.Instance join l in _unitOfWork.Repository <JobReceiveLine>().Instance on p.JobReceiveHeaderId equals l.JobReceiveHeaderId join t in _unitOfWork.Repository <Person>().Instance on p.JobWorkerId equals t.PersonID join dt in _unitOfWork.Repository <DocumentType>().Instance on p.DocTypeId equals dt.DocumentTypeId orderby p.DocDate descending, p.DocNo descending where p.DocTypeId == DocumentTypeId && p.DivisionId == DivisionId && p.SiteId == SiteId select new DyeingViewModel { DocTypeName = dt.DocumentTypeName, JobWorkerName = t.Name + "," + t.Suffix, MachineName = p.Machine.ProductName, DocDate = p.DocDate, DocNo = p.DocNo, Remark = p.Remark, Status = p.Status, JobReceiveHeaderId = p.JobReceiveHeaderId, ModifiedBy = p.ModifiedBy, ReviewCount = p.ReviewCount, ReviewBy = p.ReviewBy, Reviewed = (SqlFunctions.CharIndex(Uname, p.ReviewBy) > 0), Dimension1Name = l.JobOrderLine.Dimension1.Dimension1Name }); }
public IQueryable <SaleDispatchHeaderIndexViewModel> GetSaleDispatchHeaderList(int id, string Uname) { var DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"]; var SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"]; var temp = from p in db.SaleDispatchHeader join t in db.Persons on p.SaleToBuyerId equals t.PersonID orderby p.DocDate descending, p.DocNo descending where p.DivisionId == DivisionId && p.SiteId == SiteId && p.DocTypeId == id select new SaleDispatchHeaderIndexViewModel { Remark = p.Remark, DocDate = p.DocDate, SaleDispatchHeaderId = p.SaleDispatchHeaderId, DocNo = p.DocNo, SaleToBuyerName = t.Name, Status = p.Status, ModifiedBy = p.ModifiedBy, ReviewCount = p.ReviewCount, ReviewBy = p.ReviewBy, Reviewed = (SqlFunctions.CharIndex(Uname, p.ReviewBy) > 0), GatePassDocNo = p.GatePassHeader.DocNo, GatePassHeaderId = p.GatePassHeaderId, GatePassDocDate = p.GatePassHeader.DocDate, GatePassStatus = (p.GatePassHeaderId != null ? p.GatePassHeader.Status : 0), }; return(temp); }
public IQueryable <DispatchWaybillHeaderViewModel> GetDispatchWaybillHeaderList(int DocTypeId, string Uname) { IQueryable <DispatchWaybillHeaderViewModel> DispatchWaybillHeader = from H in db.DispatchWaybillHeader join p in db.Persons on H.ConsigneeId equals p.PersonID into PersonTable from PersonTab in PersonTable.DefaultIfEmpty() join s in db.SaleInvoiceHeader on H.SaleInvoiceHeaderId equals s.SaleInvoiceHeaderId into SaleInvoiceHeaderTable from SaleInvoiceHeaderTab in SaleInvoiceHeaderTable.DefaultIfEmpty() where H.DocTypeId == DocTypeId orderby H.DispatchWaybillHeaderId select new DispatchWaybillHeaderViewModel { DispatchWaybillHeaderId = H.DispatchWaybillHeaderId, DocDate = H.DocDate, DocNo = H.DocNo, ConsigneeName = PersonTab.Name, SaleInvoiceHeaderDocNo = SaleInvoiceHeaderTab.DocNo, WaybillNo = H.WaybillNo, WaybillDate = H.WaybillDate, Remark = H.Remark, Status = H.Status, ModifiedBy = H.ModifiedBy, ReviewCount = H.ReviewCount, ReviewBy = H.ReviewBy, Reviewed = (SqlFunctions.CharIndex(Uname, H.ReviewBy) > 0), }; return(DispatchWaybillHeader); }
public IQueryable <JobOrderInspectionHeaderViewModel> GetJobOrderInspectionHeaderList(int DocTypeId, string Uname) { var DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"]; var SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"]; return(from p in db.JobOrderInspectionHeader join J in db.Persons on p.JobWorkerId equals J.PersonID into JTable from JTab in JTable.DefaultIfEmpty() join E in db.Persons on p.InspectionById equals E.PersonID into ETable from ETab in ETable.DefaultIfEmpty() orderby p.DocDate descending, p.DocNo descending where p.SiteId == SiteId && p.DivisionId == DivisionId && p.DocTypeId == DocTypeId select new JobOrderInspectionHeaderViewModel { JobOrderInspectionHeaderId = p.JobOrderInspectionHeaderId, DocDate = p.DocDate, DocNo = p.DocNo, JobWorkerName = JTab.Name, InspectionByName = ETab.Name, DocTypeName = p.DocType.DocumentTypeName, Remark = p.Remark, Status = p.Status, ModifiedBy = p.ModifiedBy, ReviewCount = p.ReviewCount, ReviewBy = p.ReviewBy, Reviewed = (SqlFunctions.CharIndex(Uname, p.ReviewBy) > 0), } ); }
public IQueryable <SaleQuotationHeaderViewModel> GetSaleQuotationHeaderList(int DocumentTypeId, string Uname) { var DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"]; var SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"]; List <string> UserRoles = (List <string>)System.Web.HttpContext.Current.Session["Roles"]; return(from p in db.SaleQuotationHeader join t in db.Persons on p.SaleToBuyerId equals t.PersonID join dt in db.DocumentType on p.DocTypeId equals dt.DocumentTypeId orderby p.DocDate descending, p.DocNo descending where p.DocTypeId == DocumentTypeId && p.DivisionId == DivisionId && p.SiteId == SiteId select new SaleQuotationHeaderViewModel { DocTypeName = dt.DocumentTypeName, DueDate = p.DueDate, SaleToBuyerName = t.Name + "," + t.Suffix, DocDate = p.DocDate, DocNo = p.DocNo, CostCenterName = p.CostCenter.CostCenterName, Remark = p.Remark, Status = p.Status, SaleQuotationHeaderId = p.SaleQuotationHeaderId, ModifiedBy = p.ModifiedBy, ReviewCount = p.ReviewCount, ReviewBy = p.ReviewBy, Reviewed = (SqlFunctions.CharIndex(Uname, p.ReviewBy) > 0), }); }
public IQueryable <PurchaseInvoiceHeaderViewModel> GetPurchaseInvoiceHeaderList(int id, string Uname) { var DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"]; var SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"]; List <string> UserRoles = (List <string>)System.Web.HttpContext.Current.Session["Roles"]; var pt = (from p in db.PurchaseInvoiceHeader join t in db.DocumentType on p.DocTypeId equals t.DocumentTypeId into table from tab in table.DefaultIfEmpty() join t1 in db.Persons on p.SupplierId equals t1.PersonID into table2 from tab2 in table2.DefaultIfEmpty() join t in db._Users on p.ModifiedBy equals t.UserName into table3 from tab3 in table3.DefaultIfEmpty() orderby p.DocDate descending, p.DocNo descending where p.SiteId == SiteId && p.DivisionId == DivisionId && p.DocTypeId == id select new PurchaseInvoiceHeaderViewModel { DocDate = p.DocDate, DocNo = p.DocNo, DocTypeName = tab.DocumentTypeName, PurchaseInvoiceHeaderId = p.PurchaseInvoiceHeaderId, SupplierDocNo = p.SupplierDocNo, Remark = p.Remark, Status = p.Status, SupplierName = tab2.Name, ModifiedBy = p.ModifiedBy, FirstName = tab3.FirstName, ReviewCount = p.ReviewCount, ReviewBy = p.ReviewBy, Reviewed = (SqlFunctions.CharIndex(Uname, p.ReviewBy) > 0), } ); return(pt); }
//New Functions public IQueryable <PackingHeaderIndexViewModel> GetPackingHeaderList(int id, string Uname) { var DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"]; var SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"]; IQueryable <PackingHeaderIndexViewModel> packingheaderlist = from H in db.PackingHeader join B in db.Persons on H.BuyerId equals B.PersonID into BuyerTable from BuyerTab in BuyerTable.DefaultIfEmpty() orderby H.DocDate descending, H.DocNo descending where H.SiteId == SiteId && H.DivisionId == DivisionId && H.DocTypeId == id select new PackingHeaderIndexViewModel { PackingHeaderId = H.PackingHeaderId, DocTypeId = H.DocTypeId, DocDate = H.DocDate, DocNo = H.DocNo, BuyerName = BuyerTab.Name, Remark = H.Remark, Status = H.Status, ModifiedBy = H.ModifiedBy, ReviewCount = H.ReviewCount, ReviewBy = H.ReviewBy, Reviewed = (SqlFunctions.CharIndex(Uname, H.ReviewBy) > 0), }; return(packingheaderlist); }
public IQueryable <PurchaseOrderAmendmentHeaderViewModel> GetPurchaseOrderAmendmentHeaderList(int id, string Uname) { var DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"]; var SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"]; List <string> UserRoles = (List <string>)System.Web.HttpContext.Current.Session["Roles"]; return(from p in db.PurchaseOrderAmendmentHeader join t in db.Persons on p.SupplierId equals t.PersonID orderby p.DocNo descending, p.DocDate descending where p.SiteId == SiteId && p.DivisionId == p.DivisionId && p.DocTypeId == id select new PurchaseOrderAmendmentHeaderViewModel { DocDate = p.DocDate, DocNo = p.DocNo, PurchaseOrderAmendmentHeaderId = p.PurchaseOrderAmendmentHeaderId, CreatedBy = p.CreatedBy, CreatedDate = p.CreatedDate, DocTypeId = p.DocTypeId, ModifiedBy = p.ModifiedBy, ModifiedDate = p.ModifiedDate, Remark = p.Remark, SupplierName = t.Name, Status = p.Status, Reviewed = (SqlFunctions.CharIndex(Uname, p.ReviewBy) == 1), ReviewCount = p.ReviewCount, ReviewBy = p.ReviewBy, } ); }
public IQueryable <PackingHeaderViewModel> GetPackingHeaderList(string Uname) { var DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"]; var SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"]; IQueryable <PackingHeaderViewModel> packingheaderlist = from H in db.PackingHeader join B in db.Persons on H.BuyerId equals B.PersonID into BuyerTable from BuyerTab in BuyerTable.DefaultIfEmpty() orderby H.DocDate descending, H.DocNo descending where H.SiteId == SiteId && H.DivisionId == DivisionId select new PackingHeaderViewModel { PackingHeaderId = H.PackingHeaderId, DocTypeId = H.DocTypeId, DocDate = H.DocDate, DocNo = H.DocNo, BuyerName = BuyerTab.Name, Remark = H.Remark, Status = H.Status, ModifiedBy = H.ModifiedBy, ReviewCount = H.ReviewCount, ReviewBy = H.ReviewBy, Reviewed = (SqlFunctions.CharIndex(Uname, H.ReviewBy) > 0), TotalQty = H.PackingLines.Sum(m => m.Qty), DecimalPlaces = (from o in H.PackingLines 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(), }; return(packingheaderlist); }
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 <ProdOrderHeaderViewModel> GetProdOrderHeaderList(int id, string Uname) { var DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"]; var SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"]; return(from p in db.ProdOrderHeader join t in db.DocumentType on p.DocTypeId equals t.DocumentTypeId into table from tab in table.DefaultIfEmpty() where p.DivisionId == DivisionId && p.SiteId == SiteId && p.DocTypeId == id orderby p.DocDate descending, p.DocNo descending select new ProdOrderHeaderViewModel { DocDate = p.DocDate, DocNo = p.DocNo, DocTypeName = tab.DocumentTypeName, DueDate = p.DueDate, ProdOrderHeaderId = p.ProdOrderHeaderId, Remark = p.Remark, BuyerName = p.Buyer.Name, Status = p.Status, ModifiedBy = p.ModifiedBy, ReviewCount = p.ReviewCount, ReviewBy = p.ReviewBy, Reviewed = (SqlFunctions.CharIndex(Uname, p.ReviewBy) > 0), } ); }
public IQueryable <SaleOrderHeaderIndexViewModel> GetSaleOrderHeaderList(int id, string Uname) { int divisionid = (int)System.Web.HttpContext.Current.Session["DivisionId"]; int siteid = (int)System.Web.HttpContext.Current.Session["SiteId"]; var temp = from p in db.SaleOrderHeader join t in db.Persons on p.SaleToBuyerId equals t.PersonID orderby p.DocDate descending, p.DocNo descending where p.DivisionId == divisionid && p.SiteId == siteid && p.DocTypeId == id select new SaleOrderHeaderIndexViewModel { Remark = p.Remark, DocDate = p.DocDate, SaleOrderHeaderId = p.SaleOrderHeaderId, DocNo = p.DocNo, DueDate = p.DueDate, SaleToBuyerName = t.Name, BuyerOrderNo = p.BuyerOrderNo, Status = p.Status, ModifiedBy = p.ModifiedBy, ReviewCount = p.ReviewCount, ReviewBy = p.ReviewBy, Reviewed = (SqlFunctions.CharIndex(Uname, p.ReviewBy) > 0), }; return(temp); }
public IQueryable <MaterialPlanHeaderViewModel> GetMaterialPlanHeaderList(int DocTypeId, string Uname) { int DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"]; int SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"]; return(from p in db.MaterialPlanHeader join PR in db.Persons on p.BuyerId equals PR.PersonID into PRtable from PRtab in PRtable.DefaultIfEmpty() orderby p.DocDate descending, p.DocNo descending where p.DocTypeId == DocTypeId && p.SiteId == SiteId && p.DivisionId == DivisionId select new MaterialPlanHeaderViewModel { DocDate = p.DocDate, DocNo = p.DocNo, DocTypeName = p.DocType.DocumentTypeName, DueDate = p.DueDate, BuyerName = PRtab.Name, MaterialPlanHeaderId = p.MaterialPlanHeaderId, Remark = p.Remark, Status = p.Status, ModifiedBy = p.ModifiedBy, ReviewCount = p.ReviewCount, ReviewBy = p.ReviewBy, Reviewed = (SqlFunctions.CharIndex(Uname, p.ReviewBy) > 0), } ); }
public IQueryable <JobOrderCancelHeaderIndexViewModel> GetJobOrderCancelHeaderList(int id, string Uname) { var DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"]; var SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"]; List <string> UserRoles = (List <string>)System.Web.HttpContext.Current.Session["Roles"]; return(from p in db.JobOrderCancelHeader orderby p.DocDate descending, p.DocNo descending where p.SiteId == SiteId && p.DivisionId == DivisionId && p.DocTypeId == id select new JobOrderCancelHeaderIndexViewModel { DocDate = p.DocDate, DocNo = p.DocNo, JobOrderCancelHeaderId = p.JobOrderCancelHeaderId, ReasonName = p.Reason.ReasonName, Remark = p.Remark, Status = p.Status, JobWorkerName = p.JobWorker.Name, ModifiedBy = p.ModifiedBy, ReviewCount = p.ReviewCount, ReviewBy = p.ReviewBy, Reviewed = (SqlFunctions.CharIndex(Uname, p.ReviewBy) > 0), TotalQty = p.JobOrderCancelLines.Sum(m => m.Qty), DecimalPlaces = (from o in p.JobOrderCancelLines join ol in db.JobOrderLine on o.JobOrderCancelLineId equals ol.JobOrderLineId join prod in db.Product on ol.ProductId equals prod.ProductId join u in db.Units on prod.UnitId equals u.UnitId select u.DecimalPlaces).Max(), } ); }
public IQueryable <GatePassHeaderViewModel> GetGatePassHeaderList(int DocumentTypeId, string Uname) { var DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"]; var SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"]; List <string> UserRoles = (List <string>)System.Web.HttpContext.Current.Session["Roles"]; return(from p in db.GatePassHeader join t in db.Persons on p.PersonId equals t.PersonID join dt in db.DocumentType on p.DocTypeId equals dt.DocumentTypeId join G in db.Godown on p.GodownId equals G.GodownId orderby p.DocDate descending, p.DocNo descending where p.DocTypeId == DocumentTypeId && p.DivisionId == DivisionId && p.SiteId == SiteId select new GatePassHeaderViewModel { DocTypeName = dt.DocumentTypeName, Name = t.Name + "," + t.Suffix, DocDate = p.DocDate, DocNo = p.DocNo, Remark = p.Remark, Status = p.Status, GatePassHeaderId = p.GatePassHeaderId, ModifiedBy = p.ModifiedBy, ReviewCount = p.ReviewCount, GodownName = G.GodownName, ReviewBy = p.ReviewBy, Reviewed = (SqlFunctions.CharIndex(Uname, p.ReviewBy) > 0), OrderById = p.OrderById, TotalQty = p.GatePassLines.Sum(m => m.Qty), DecimalPlaces = (from o in p.GatePassLines join u in db.Units on o.UnitId equals u.UnitId select u.DecimalPlaces).Max(), }); }
public IQueryable <JobInvoiceAmendmentHeaderIndexViewModel> GetJobInvoiceAmendmentHeaderList(int id, string Uname) { var DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"]; var SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"]; List <string> UserRoles = (List <string>)System.Web.HttpContext.Current.Session["Roles"]; return(from p in db.JobInvoiceAmendmentHeader orderby p.DocNo descending, p.DocDate descending where p.SiteId == SiteId && p.DivisionId == DivisionId && p.DocTypeId == id select new JobInvoiceAmendmentHeaderIndexViewModel { DocDate = p.DocDate, DocNo = p.DocNo, JobInvoiceAmendmentHeaderId = p.JobInvoiceAmendmentHeaderId, ProcessName = p.Process.ProcessName, OrderByName = p.OrderBy.Person.Name, Remark = p.Remark, Status = p.Status, JobWorkerName = p.JobWorker.Person.Name, ModifiedBy = p.ModifiedBy, Reviewed = (SqlFunctions.CharIndex(Uname, p.ReviewBy) == 1), ReviewCount = p.ReviewCount, ReviewBy = p.ReviewBy, } ); }
public IQueryable <PurchaseIndentHeaderViewModel> GetPurchaseIndentHeaderList(int id, string name) { var DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"]; var SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"]; List <string> UserRoles = (List <string>)System.Web.HttpContext.Current.Session["Roles"]; return(from p in db.PurchaseIndentHeader join t in db._Users on p.ModifiedBy equals t.UserName into table from tab in table.DefaultIfEmpty() orderby p.DocDate descending, p.DocNo descending where p.SiteId == SiteId && p.DivisionId == DivisionId && p.DocTypeId == id && p.DocTypeId == id select new PurchaseIndentHeaderViewModel { DocDate = p.DocDate, DocNo = p.DocNo, DepartmentName = p.Department.DepartmentName, Remark = p.Remark, Status = p.Status, PurchaseIndentHeaderId = p.PurchaseIndentHeaderId, ModifiedBy = p.ModifiedBy, FirstName = tab.FirstName, ReviewCount = p.ReviewCount, ReviewBy = p.ReviewBy, Reviewed = (SqlFunctions.CharIndex(name, 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 Task <IReadOnlyCollection <Claim> > GetClaimsForGroups(int projectId, ClaimStatusSpec active, int[] characterGroupsIds) { return(GetClaimsImpl(projectId, active, claim => (claim.CharacterGroupId != null && characterGroupsIds.Contains(claim.CharacterGroupId.Value)) || (claim.CharacterId != null && characterGroupsIds.Any(id => SqlFunctions.CharIndex(id.ToString(), claim.Character.ParentGroupsImpl.ListIds) > 0 )))); }
public IQueryable <JobReturnHeaderViewModel> GetJobReturnHeaderList(int id, string Uname) { var DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"]; var SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"]; List <string> UserRoles = (List <string>)System.Web.HttpContext.Current.Session["Roles"]; var pt = (from p in db.JobReturnHeader join t in db.DocumentType on p.DocTypeId equals t.DocumentTypeId into table from tab in table.DefaultIfEmpty() join t1 in db.Persons on p.JobWorkerId equals t1.PersonID into table2 from tab2 in table2.DefaultIfEmpty() orderby p.DocDate descending, p.DocNo descending where p.SiteId == SiteId && p.DivisionId == DivisionId && p.DocTypeId == id select new JobReturnHeaderViewModel { DocDate = p.DocDate, DocNo = p.DocNo, DocTypeName = tab.DocumentTypeName, JobReturnHeaderId = p.JobReturnHeaderId, Remark = p.Remark, Status = p.Status, JobWorkerName = tab2.Name, ModifiedBy = p.ModifiedBy, ReviewCount = p.ReviewCount, ReviewBy = p.ReviewBy, Reviewed = (SqlFunctions.CharIndex(Uname, p.ReviewBy) > 0), GatePassDocNo = p.GatePassHeader.DocNo, GatePassHeaderId = p.GatePassHeaderId, GatePassDocDate = p.GatePassHeader.DocDate, GatePassStatus = (p.GatePassHeaderId != null ? p.GatePassHeader.Status : 0), TotalQty = p.JobReturnLines.Sum(m => m.Qty), DecimalPlaces = (from o in p.JobReturnLines join rl in db.JobReceiveLine on o.JobReceiveLineId equals rl.JobReceiveLineId join ol in db.JobOrderLine on rl.JobOrderLineId equals ol.JobOrderLineId join prod in db.Product on ol.ProductId equals prod.ProductId join u in db.Units on prod.UnitId equals u.UnitId select u.DecimalPlaces).Max(), } ); return(pt); var tem = (from p in db.JobOrderHeader select p).FirstOrDefault(); var query = db.Entry(tem) .Collection(m => m.JobOrderLines).Query(); var q2 = from p in query where p.Qty > 0 select p; var q3 = (from p in query where p.Qty > 0 select p).Count(); }
public void CharIndex() { var person = from c in jambore.Person where SqlFunctions.CharIndex("Si", c.LastName) == 1 select c; foreach (var s in person) { Console.WriteLine(s.LastName); } Console.ReadKey(); }
public IQueryable <JobInvoiceHeaderViewModel> GetJobInvoiceHeaderList(int id, string Uname) { 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"]; var TempJobInvoiceHeaderCharges = from H in db.JobInvoiceHeader join Hc in db.JobInvoiceHeaderCharges on H.JobInvoiceHeaderId equals Hc.HeaderTableId into JobInvoiceHeaderChargesTable from JobInvoiceHeaderChargesTab in JobInvoiceHeaderChargesTable.DefaultIfEmpty() join C in db.Charge on JobInvoiceHeaderChargesTab.ChargeId equals C.ChargeId into ChargeTable from ChargeTab in ChargeTable.DefaultIfEmpty() where H.SiteId == SiteId && H.DivisionId == DivisionId && H.DocTypeId == id && ChargeTab.ChargeName == "Net Amount" select new { JobInvoiceHeaderId = H.JobInvoiceHeaderId, NetAmount = JobInvoiceHeaderChargesTab.Amount }; return(from p in db.JobInvoiceHeader join Hc in TempJobInvoiceHeaderCharges on p.JobInvoiceHeaderId equals Hc.JobInvoiceHeaderId into JobInvoiceHeaderChargesTable from JobInvoiceHeaderChargesTab in JobInvoiceHeaderChargesTable.DefaultIfEmpty() orderby p.DocDate descending, p.DocNo descending where p.SiteId == SiteId && p.DivisionId == DivisionId && p.DocTypeId == id select new JobInvoiceHeaderViewModel { DocDate = p.DocDate, DocNo = p.DocNo, DocTypeName = p.DocType.DocumentTypeName, JobInvoiceHeaderId = p.JobInvoiceHeaderId, Remark = p.Remark, Status = p.Status, JobWorkerDocNo = p.JobWorkerDocNo, JobWorkerName = p.JobWorker.Name + ", " + p.JobWorker.Suffix + " [" + p.JobWorker.Code + "]", JobWorkerDocDate = p.JobWorkerDocDate, ModifiedBy = p.ModifiedBy, ReviewCount = p.ReviewCount, GodownName = p.JobReceiveHeader.Godown.GodownName, ReviewBy = p.ReviewBy, Reviewed = (SqlFunctions.CharIndex(Uname, p.ReviewBy) > 0), TotalQty = p.JobInvoiceLines.Sum(m => m.Qty), TotalAmount = JobInvoiceHeaderChargesTab.NetAmount ?? (p.JobInvoiceLines.Sum(m => m.Amount)), DecimalPlaces = (from o in p.JobInvoiceLines join rl in db.JobReceiveLine on o.JobReceiveLineId equals rl.JobReceiveLineId join ol in db.JobOrderLine on rl.JobOrderLineId equals ol.JobOrderLineId join prod in db.Product on ol.ProductId equals prod.ProductId join u in db.Units on prod.UnitId equals u.UnitId select u.DecimalPlaces).Max(), }); }
public IQueryable <SaleInvoiceHeaderIndexViewModel> GetSaleInvoiceHeaderList(int id, string Uname) { var DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"]; var SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"]; var TempSaleInvoiceHeaderCharges = from H in db.SaleInvoiceHeader join Hc in db.SaleInvoiceHeaderCharge on H.SaleInvoiceHeaderId equals Hc.HeaderTableId into SaleInvoiceHeaderChargesTable from SaleInvoiceHeaderChargesTab in SaleInvoiceHeaderChargesTable.DefaultIfEmpty() join C in db.Charge on SaleInvoiceHeaderChargesTab.ChargeId equals C.ChargeId into ChargeTable from ChargeTab in ChargeTable.DefaultIfEmpty() where H.SiteId == SiteId && H.DivisionId == DivisionId && H.DocTypeId == id && ChargeTab.ChargeName == "Net Amount" select new { SaleInvoiceHeaderId = H.SaleInvoiceHeaderId, NetAmount = SaleInvoiceHeaderChargesTab.Amount }; var temp = from p in db.SaleInvoiceHeader join t in db.Persons on p.BillToBuyerId equals t.PersonID join Hc in TempSaleInvoiceHeaderCharges on p.SaleInvoiceHeaderId equals Hc.SaleInvoiceHeaderId into SaleInvoiceHeaderChargesTable from SaleInvoiceHeaderChargesTab in SaleInvoiceHeaderChargesTable.DefaultIfEmpty() orderby p.DocDate descending, p.DocNo descending where p.DivisionId == DivisionId && p.SiteId == SiteId && p.DocTypeId == id select new SaleInvoiceHeaderIndexViewModel { Remark = p.Remark, DocDate = p.DocDate, SaleInvoiceHeaderId = p.SaleInvoiceHeaderId, DocNo = p.DocNo, BillToBuyerName = t.Name + ", " + t.Suffix + " [" + t.Code + "]", Status = p.Status, ModifiedBy = p.ModifiedBy, ReviewCount = p.ReviewCount, ReviewBy = p.ReviewBy, Reviewed = (SqlFunctions.CharIndex(Uname, p.ReviewBy) > 0), GatePassDocNo = p.SaleDispatchHeader.GatePassHeader.DocNo, GatePassHeaderId = p.SaleDispatchHeader.GatePassHeader.GatePassHeaderId, GatePassDocDate = p.SaleDispatchHeader.GatePassHeader.DocDate, GatePassStatus = (p.SaleDispatchHeader.GatePassHeader.Status != null ? p.SaleDispatchHeader.GatePassHeader.Status : 0), TotalQty = p.SaleInvoiceLines.Sum(m => m.Qty), TotalAmount = SaleInvoiceHeaderChargesTab.NetAmount ?? (p.SaleInvoiceLines.Sum(m => m.Amount)), DecimalPlaces = (from o in p.SaleInvoiceLines join rl in db.SaleDispatchLine on o.SaleDispatchLineId equals rl.SaleDispatchLineId join ol in db.PackingLine on rl.PackingLineId equals ol.PackingLineId join prod in db.Product on ol.ProductId equals prod.ProductId join u in db.Units on prod.UnitId equals u.UnitId select u.DecimalPlaces).Max(), }; return(temp); }
public void test1() { var arr1 = BitConverter.GetBytes(123); using (var edm = new NorthwindEntities()) { var cust1 = edm.Employees .Where(e => SqlFunctions.CharIndex(arr1, e.Photo, (long?)0) > (long?)-1); //WHERE ( CAST(CHARINDEX(@p__linq__0, [Extent1].[Photo], cast(0 as bigint)) AS bigint)) > -1 cust1.TraceSql(); Console.WriteLine(cust1.Count()); } }
public void test5() { var arr1 = BitConverter.GetBytes(123); using (var edm = new NorthwindEntities()) { var cust1 = edm.Employees .Where(c => SqlFunctions.CharIndex(arr1, c.Photo) >= -1); //WHERE ( CAST(CHARINDEX(@p__linq__0, [Extent1].[Photo]) AS int)) >= -1 cust1.TraceSql(); Console.WriteLine(cust1.Count()); } }
public IQueryable <SaleOrderCancelHeaderIndexViewModel> GetSOCList(int id, string Uname) { //Working copy:: //var temp = (from p in db.SaleOrderCancelHeader // orderby p.DocDate descending, p.DocNo descending // select new SaleOrderCancelHeaderDetailsViewModel // { // SaleOrderCancelHeaderId = p.SaleOrderCancelHeaderId, // DocDate = p.DocDate, // DocNo = p.DocNo, // Reason = p.Reason.ReasonName, // Remark = p.Remark, // Status = p.Status, // }); //var SaleOrderCancelBuyer = from L in db.SaleOrderCancelLine // join Ol in db.SaleOrderLine on L.SaleOrderLineId equals Ol.SaleOrderLineId into SaleOrderLineTable // from SaleOrderLineTab in SaleOrderLineTable.DefaultIfEmpty() // join Oh in db.SaleOrderHeader on SaleOrderLineTab.SaleOrderHeaderId equals Oh.SaleOrderHeaderId into SaleOrderHeaderTable // from SaleOrderHeaderTab in SaleOrderHeaderTable.DefaultIfEmpty() // group new { L, SaleOrderHeaderTab } by new { L.SaleOrderCancelHeaderId, SaleOrderHeaderTab.SaleToBuyerId } into Result // select new // { // SaleOrderCancelHeaderId = Result.Key.SaleOrderCancelHeaderId, // BuyerId = Result.Key.SaleToBuyerId // }; var DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"]; var SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"]; var temp = from H in db.SaleOrderCancelHeader orderby H.DocDate descending, H.DocNo descending where H.SiteId == SiteId && H.DivisionId == DivisionId && H.DocTypeId == id select new SaleOrderCancelHeaderIndexViewModel { SaleOrderCancelHeaderId = H.SaleOrderCancelHeaderId, DocNo = H.DocNo, DocDate = H.DocDate, BuyerName = H.Buyer.Person.Name, ReasonName = H.Reason.ReasonName, Remark = H.Remark, Status = H.Status, ModifiedBy = H.ModifiedBy, ReviewCount = H.ReviewCount, ReviewBy = H.ReviewBy, Reviewed = (SqlFunctions.CharIndex(Uname, H.ReviewBy) > 0), }; return(temp); }