public AgentViewModel GetAgentViewModel(int id)
{
AgentViewModel Agentviewmodel = (from b in db.Agent
join bus in db.BusinessEntity on b.PersonID equals bus.PersonID into BusinessEntityTable
from BusinessEntityTab in BusinessEntityTable.DefaultIfEmpty()
join p in db.Persons on BusinessEntityTab.PersonID equals p.PersonID into PersonTable
from PersonTab in PersonTable.DefaultIfEmpty()
join pa in db.PersonAddress on PersonTab.PersonID equals pa.PersonId into PersonAddressTable
from PersonAddressTab in PersonAddressTable.DefaultIfEmpty()
join ac in db.LedgerAccount on PersonTab.PersonID equals ac.PersonId into AccountTable
from AccountTab in AccountTable.DefaultIfEmpty()
where PersonTab.PersonID == id
select new AgentViewModel
{
PersonId = PersonTab.PersonID,
Name = PersonTab.Name,
Suffix = PersonTab.Suffix,
Code = PersonTab.Code,
Phone = PersonTab.Phone,
Mobile = PersonTab.Mobile,
Email = PersonTab.Email,
Address = PersonAddressTab.Address,
CityId = PersonAddressTab.CityId,
Zipcode = PersonAddressTab.Zipcode,
IsSisterConcern = BusinessEntityTab.IsSisterConcern,
IsActive = PersonTab.IsActive,
LedgerAccountGroupId = AccountTab.LedgerAccountGroupId,
CreatedBy = PersonTab.CreatedBy,
CreatedDate = PersonTab.CreatedDate,
PersonAddressID = PersonAddressTab.PersonAddressID,
AccountId = AccountTab.LedgerAccountId,
ImageFileName = PersonTab.ImageFileName,
ImageFolderName = PersonTab.ImageFolderName
}).FirstOrDefault();
var PersonRegistration = (from pp in db.PersonRegistration
where pp.PersonId == id
select new
{
PersonRegistrationId = pp.PersonRegistrationID,
RregistrationType = pp.RegistrationType,
RregistrationNo = pp.RegistrationNo
}).ToList();
return(Agentviewmodel);
}
public SupplierViewModel GetSupplierViewModel(int id)
{
SupplierViewModel Supplierviewmodel = (from b in db.Supplier
join bus in db.BusinessEntity on b.PersonID equals bus.PersonID into BusinessEntityTable
from BusinessEntityTab in BusinessEntityTable.DefaultIfEmpty()
join p in db.Persons on BusinessEntityTab.PersonID equals p.PersonID into PersonTable
from PersonTab in PersonTable.DefaultIfEmpty()
join pa in db.PersonAddress on PersonTab.PersonID equals pa.PersonId into PersonAddressTable
from PersonAddressTab in PersonAddressTable.DefaultIfEmpty()
join ac in db.LedgerAccount on PersonTab.PersonID equals ac.PersonId into AccountTable
from AccountTab in AccountTable.DefaultIfEmpty()
where PersonTab.PersonID == id
select new SupplierViewModel
{
PersonId = PersonTab.PersonID,
Name = PersonTab.Name,
Suffix = PersonTab.Suffix,
Code = PersonTab.Code,
Phone = PersonTab.Phone,
Mobile = PersonTab.Mobile,
Email = PersonTab.Email,
Address = PersonAddressTab.Address,
CityId = PersonAddressTab.CityId,
Zipcode = PersonAddressTab.Zipcode,
IsSisterConcern = BusinessEntityTab.IsSisterConcern,
PersonRateGroupId = BusinessEntityTab.PersonRateGroupId,
CreaditDays = BusinessEntityTab.CreaditDays,
CreaditLimit = BusinessEntityTab.CreaditLimit,
IsActive = PersonTab.IsActive,
SalesTaxGroupPartyId = b.SalesTaxGroupPartyId,
LedgerAccountGroupId = AccountTab.LedgerAccountGroupId,
CreatedBy = PersonTab.CreatedBy,
CreatedDate = PersonTab.CreatedDate,
PersonAddressID = PersonAddressTab.PersonAddressID,
AccountId = AccountTab.LedgerAccountId,
SiteIds = BusinessEntityTab.SiteIds,
DivisionIds = BusinessEntityTab.DivisionIds,
Tags = PersonTab.Tags,
ImageFileName = PersonTab.ImageFileName,
ImageFolderName = PersonTab.ImageFolderName
}).FirstOrDefault();
var PersonProcess = (from pp in db.PersonProcess
where pp.PersonId == id
select new
{
ProcessId = pp.ProcessId
}).ToList();
foreach (var item in PersonProcess)
{
if (Supplierviewmodel.ProcessIds == "" || Supplierviewmodel.ProcessIds == null)
{
Supplierviewmodel.ProcessIds = item.ProcessId.ToString();
}
else
{
Supplierviewmodel.ProcessIds = Supplierviewmodel.ProcessIds + "," + item.ProcessId.ToString();
}
}
var PersonRegistration = (from pp in db.PersonRegistration
where pp.PersonId == id
select new
{
PersonRegistrationId = pp.PersonRegistrationID,
RregistrationType = pp.RegistrationType,
RregistrationNo = pp.RegistrationNo
}).ToList();
if (PersonRegistration != null)
{
foreach (var item in PersonRegistration)
{
if (item.RregistrationType == PersonRegistrationType.CstNo)
{
Supplierviewmodel.PersonRegistrationCstNoID = item.PersonRegistrationId;
Supplierviewmodel.CstNo = item.RregistrationNo;
}
if (item.RregistrationType == PersonRegistrationType.TinNo)
{
Supplierviewmodel.PersonRegistrationTinNoID = item.PersonRegistrationId;
Supplierviewmodel.TinNo = item.RregistrationNo;
}
}
}
string Divisions = Supplierviewmodel.DivisionIds;
if (Divisions != null)
{
Divisions = Divisions.Replace('|', ' ');
Supplierviewmodel.DivisionIds = Divisions;
}
string Sites = Supplierviewmodel.SiteIds;
if (Sites != null)
{
Sites = Sites.Replace('|', ' ');
Supplierviewmodel.SiteIds = Sites;
}
return(Supplierviewmodel);
}
public CourierViewModel GetCourierViewModel(int id)
{
CourierViewModel Courierviewmodel = (from b in db.Courier
join bus in db.BusinessEntity on b.PersonID equals bus.PersonID into BusinessEntityTable
from BusinessEntityTab in BusinessEntityTable.DefaultIfEmpty()
join p in db.Persons on BusinessEntityTab.PersonID equals p.PersonID into PersonTable
from PersonTab in PersonTable.DefaultIfEmpty()
join pa in db.PersonAddress on b.PersonID equals pa.PersonId into PersonAddressTable
from PersonAddressTab in PersonAddressTable.DefaultIfEmpty()
join ac in db.LedgerAccount on b.PersonID equals ac.PersonId into AccountTable
from AccountTab in AccountTable.DefaultIfEmpty()
where b.PersonID == id
select new CourierViewModel
{
PersonId = b.PersonID,
Name = PersonTab.Name,
Suffix = PersonTab.Suffix,
Code = PersonTab.Code,
Phone = PersonTab.Phone,
Mobile = PersonTab.Mobile,
Email = PersonTab.Email,
Address = PersonAddressTab.Address,
CityId = PersonAddressTab.CityId,
Zipcode = PersonAddressTab.Zipcode,
TdsCategoryId = BusinessEntityTab.TdsCategoryId,
TdsGroupId = BusinessEntityTab.TdsGroupId,
IsActive = PersonTab.IsActive,
LedgerAccountGroupId = AccountTab.LedgerAccountGroupId,
CreatedBy = PersonTab.CreatedBy,
CreatedDate = PersonTab.CreatedDate,
PersonAddressID = PersonAddressTab.PersonAddressID,
AccountId = AccountTab.LedgerAccountId,
ImageFileName = PersonTab.ImageFileName,
ImageFolderName = PersonTab.ImageFolderName
}
).FirstOrDefault();
var PersonRegistration = (from pp in db.PersonRegistration
where pp.PersonId == id
select new
{
PersonRegistrationId = pp.PersonRegistrationID,
RregistrationType = pp.RegistrationType,
RregistrationNo = pp.RegistrationNo
}).ToList();
if (PersonRegistration != null)
{
foreach (var item in PersonRegistration)
{
if (item.RregistrationType == PersonRegistrationType.PANNo)
{
Courierviewmodel.PersonRegistrationPanNoID = item.PersonRegistrationId;
Courierviewmodel.PanNo = item.RregistrationNo;
}
if (item.RregistrationType == PersonRegistrationType.ServiceTaxNo)
{
Courierviewmodel.PersonRegistrationServiceTaxNoID = item.PersonRegistrationId;
Courierviewmodel.ServiceTaxNo = item.RregistrationNo;
}
if (item.RregistrationType == PersonRegistrationType.KYCNo)
{
Courierviewmodel.PersonRegistrationKYCNoID = item.PersonRegistrationId;
Courierviewmodel.KYCNo = item.RregistrationNo;
}
}
}
return(Courierviewmodel);
}
public PersonViewModel GetPersonViewModel(int id)
{
PersonViewModel PersonViewModel = (from bus in db.BusinessEntity
join p in db.Persons on bus.PersonID equals p.PersonID into PersonTable
from PersonTab in PersonTable.DefaultIfEmpty()
join pa in db.PersonAddress on PersonTab.PersonID equals pa.PersonId into PersonAddressTable
from PersonAddressTab in PersonAddressTable.DefaultIfEmpty()
join ac in db.LedgerAccount on PersonTab.PersonID equals ac.PersonId into AccountTable
from AccountTab in AccountTable.DefaultIfEmpty()
where PersonTab.PersonID == id
select new PersonViewModel
{
PersonID = PersonTab.PersonID,
Name = PersonTab.Name,
Suffix = PersonTab.Suffix,
Code = PersonTab.Code,
Phone = PersonTab.Phone,
Mobile = PersonTab.Mobile,
Email = PersonTab.Email,
Address = PersonAddressTab.Address,
CityId = PersonAddressTab.CityId,
CityName = PersonAddressTab.City.CityName,
Zipcode = PersonAddressTab.Zipcode,
PersonRateGroupId = bus.PersonRateGroupId,
CreaditDays = bus.CreaditDays,
CreaditLimit = bus.CreaditLimit,
IsActive = PersonTab.IsActive,
SalesTaxGroupPartyId = bus.SalesTaxGroupPartyId,
CreatedBy = PersonTab.CreatedBy,
CreatedDate = PersonTab.CreatedDate,
SiteIds = bus.SiteIds,
Tags = PersonTab.Tags,
ImageFileName = PersonTab.ImageFileName,
ImageFolderName = PersonTab.ImageFolderName,
IsSisterConcern = (bool?)bus.IsSisterConcern ?? false,
AccountId = (int?)AccountTab.LedgerAccountId ?? 0,
PersonAddressID = (PersonAddressTab == null ? 0 : PersonAddressTab.PersonAddressID),
LedgerAccountGroupId = (int?)AccountTab.LedgerAccountGroupId ?? 0,
}).FirstOrDefault();
var PersonRegistration = (from pp in db.PersonRegistration
where pp.PersonId == id
select new
{
PersonRegistrationId = pp.PersonRegistrationID,
RregistrationType = pp.RegistrationType,
RregistrationNo = pp.RegistrationNo
}).ToList();
if (PersonRegistration != null)
{
foreach (var item in PersonRegistration)
{
if (item.RregistrationType == PersonRegistrationType.CstNo)
{
PersonViewModel.PersonRegistrationCstNoID = item.PersonRegistrationId;
PersonViewModel.CstNo = item.RregistrationNo;
}
if (item.RregistrationType == PersonRegistrationType.TinNo)
{
PersonViewModel.PersonRegistrationTinNoID = item.PersonRegistrationId;
PersonViewModel.TinNo = item.RregistrationNo;
}
if (item.RregistrationType == PersonRegistrationType.PANNo)
{
PersonViewModel.PersonRegistrationPanNoID = item.PersonRegistrationId;
PersonViewModel.PanNo = item.RregistrationNo;
}
if (item.RregistrationType == PersonRegistrationType.GstNo)
{
PersonViewModel.PersonRegistrationGstNoID = item.PersonRegistrationId;
PersonViewModel.GstNo = item.RregistrationNo;
}
if (item.RregistrationType == PersonRegistrationType.AadharNo)
{
PersonViewModel.PersonRegistrationAadharNoID = item.PersonRegistrationId;
PersonViewModel.AadharNo = item.RregistrationNo;
}
}
}
return(PersonViewModel);
}
public BuyerViewModel GetBuyerViewModel(int id)
{
BuyerViewModel buyerviewmodel = (from b in db.Buyer
join bus in db.BusinessEntity on b.PersonID equals bus.PersonID into BusinessEntityTable
from BusinessEntityTab in BusinessEntityTable.DefaultIfEmpty()
join p in db.Persons on b.PersonID equals p.PersonID into PersonTable
from PersonTab in PersonTable.DefaultIfEmpty()
join pa in db.PersonAddress on PersonTab.PersonID equals pa.PersonId into PersonAddressTable
from PersonAddressTab in PersonAddressTable.DefaultIfEmpty()
join ac in db.LedgerAccount on PersonTab.PersonID equals ac.PersonId into AccountTable
from AccountTab in AccountTable.DefaultIfEmpty()
where PersonTab.PersonID == id
select new BuyerViewModel
{
PersonId = PersonTab.PersonID,
Name = PersonTab.Name,
Suffix = PersonTab.Suffix,
Code = PersonTab.Code,
Phone = PersonTab.Phone,
Mobile = PersonTab.Mobile,
Email = PersonTab.Email,
Address = PersonAddressTab.Address,
CityId = PersonAddressTab.CityId,
Zipcode = PersonAddressTab.Zipcode,
PersonRateGroupId = BusinessEntityTab.PersonRateGroupId,
CreaditDays = BusinessEntityTab.CreaditDays,
CreaditLimit = BusinessEntityTab.CreaditLimit,
IsActive = PersonTab.IsActive,
SalesTaxGroupPartyId = BusinessEntityTab.SalesTaxGroupPartyId,
CreatedBy = PersonTab.CreatedBy,
CreatedDate = PersonTab.CreatedDate,
SiteIds = BusinessEntityTab.SiteIds,
Tags = PersonTab.Tags,
ImageFileName = PersonTab.ImageFileName,
ImageFolderName = PersonTab.ImageFolderName,
ParentId = BusinessEntityTab.ParentId,
GuarantorId = BusinessEntityTab.GuarantorId,
IsSisterConcern = (bool?)BusinessEntityTab.IsSisterConcern ?? false,
AccountId = (int?)AccountTab.LedgerAccountId ?? 0,
PersonAddressID = (PersonAddressTab == null ? 0 : PersonAddressTab.PersonAddressID),
LedgerAccountGroupId = (int?)AccountTab.LedgerAccountGroupId ?? 0,
}).FirstOrDefault();
var PersonRegistration = (from pp in db.PersonRegistration
where pp.PersonId == id
select new
{
PersonRegistrationId = pp.PersonRegistrationID,
RregistrationType = pp.RegistrationType,
RregistrationNo = pp.RegistrationNo
}).ToList();
if (PersonRegistration != null)
{
foreach (var item in PersonRegistration)
{
if (item.RregistrationType == PersonRegistrationType.CstNo)
{
buyerviewmodel.PersonRegistrationCstNoID = item.PersonRegistrationId;
buyerviewmodel.CstNo = item.RregistrationNo;
}
if (item.RregistrationType == PersonRegistrationType.TinNo)
{
buyerviewmodel.PersonRegistrationTinNoID = item.PersonRegistrationId;
buyerviewmodel.TinNo = item.RregistrationNo;
}
}
}
string Sites = buyerviewmodel.SiteIds;
if (Sites != null)
{
Sites = Sites.Replace('|', ' ');
buyerviewmodel.SiteIds = Sites;
}
return(buyerviewmodel);
}
public IQueryable <ComboBoxResult> GetLedgerAccounts(string term, string AccGroups, string ExcludeAccGroups, string Process)
{
string[] ContraAccGroups = null;
if (!string.IsNullOrEmpty(AccGroups))
{
ContraAccGroups = AccGroups.Split(",".ToCharArray());
}
else
{
ContraAccGroups = new string[] { "NA" };
}
string[] ExcludeContraAccGroups = null;
if (!string.IsNullOrEmpty(ExcludeAccGroups))
{
ExcludeContraAccGroups = ExcludeAccGroups.Split(",".ToCharArray());
}
else
{
ExcludeContraAccGroups = new string[] { "NA" };
}
string[] ContraProcess = null;
if (!string.IsNullOrEmpty(Process))
{
ContraProcess = Process.Split(",".ToCharArray());
}
else
{
ContraProcess = new string[] { "NA" };
}
int CurrentSiteId = (int)System.Web.HttpContext.Current.Session["SiteId"];
int CurrentDivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];
string DivId = "|" + CurrentDivisionId.ToString() + "|";
string SiteId = "|" + CurrentSiteId.ToString() + "|";
var temp = (from p in db.LedgerAccount
join t2 in db.BusinessEntity on p.PersonId equals t2.PersonID into table2 from tab2 in table2.DefaultIfEmpty()
join t in db.Persons on tab2.PersonID equals t.PersonID into table
from tab in table.DefaultIfEmpty()
join Pa in db.PersonAddress on tab.PersonID equals Pa.PersonId into PersonAddressTable from PersonAddressTab in PersonAddressTable.DefaultIfEmpty()
join t3 in db.PersonProcess on tab.PersonID equals t3.PersonId into table3 from perproc in table3.DefaultIfEmpty()
where (string.IsNullOrEmpty(AccGroups) ? 1 == 1 : ContraAccGroups.Contains(p.LedgerAccountGroupId.ToString())) &&
(string.IsNullOrEmpty(ExcludeAccGroups) ? 1 == 1 : !ExcludeContraAccGroups.Contains(p.LedgerAccountGroupId.ToString())) &&
p.IsActive == true &&
(string.IsNullOrEmpty(term) ? 1 == 1 : (p.LedgerAccountName.ToLower().Contains(term.ToLower())) ||
tab.Code.ToLower().Contains(term.ToLower()) ||
tab.Suffix.ToLower().Contains(term.ToLower())) &&
(tab2 == null || ((string.IsNullOrEmpty(Process) ? 1 == 1 : ContraProcess.Contains(perproc.ProcessId.ToString())) && tab2.DivisionIds.IndexOf(DivId) != -1 &&
tab2.SiteIds.IndexOf(SiteId) != -1))
select new ComboBoxResult
{
id = p.LedgerAccountId.ToString(),
text = p.LedgerAccountName + (tab2 == null ? "" : ", " + tab.Suffix + " [" + tab.Code + "]"),
TextProp1 = p.LedgerAccountGroup.LedgerAccountGroupName,
TextProp2 = ((PersonAddressTab.Address == null) ? "" : PersonAddressTab.Address + "," + PersonAddressTab.City.CityName)
});
var GroupedRec = from p in temp
group p by p.id into g
orderby g.Max(m => m.text)
select new ComboBoxResult
{
id = g.Max(m => m.id),
text = g.Max(m => m.text),
TextProp1 = g.Max(m => m.TextProp1),
TextProp2 = g.Max(m => m.TextProp2),
};
return(GroupedRec);
}
public IQueryable <PersonIndexViewModel> GetPersonListForIndex(int id)
{
var PersonAddress = from Pa in db.PersonAddress
group new { Pa } by new { Pa.PersonId } into Result
select new
{
PersonId = Result.Key.PersonId,
Address = Result.Max(m => m.Pa.Address)
};
var temp = from p in db.Persons
join Pr in db.PersonRole on p.PersonID equals Pr.PersonId into PersonRoleTable from PersonRoleTab in PersonRoleTable.DefaultIfEmpty()
join Pa in PersonAddress on p.PersonID equals Pa.PersonId into PersonAddressTable from PersonAddressTab in PersonAddressTable.DefaultIfEmpty()
where PersonRoleTab.RoleDocTypeId == id
orderby p.Name
select new PersonIndexViewModel
{
PersonId = p.PersonID,
DocTypeId = PersonRoleTab.RoleDocTypeId,
Name = p.Name,
Suffix = p.Suffix,
Code = p.Code,
Mobile = p.Mobile,
Email = p.Email,
Address = PersonAddressTab.Address
};
return(temp);
}