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);
}
