private GetPSASMainUnitDetailDto GetPsasMainToExport(GetPSASParamsDto input)
{
var UnitID = _ipriceAppService.GetParameter(input);
var getDataPersonal = (from x in _contextPers.PERSONAL
join a in _trBookingHeaderRepo.GetAll().ToList() on x.psCode equals a.psCode
join b in _contextPers.TR_Address on a.psCode equals b.psCode into address
from b in address.DefaultIfEmpty()
join c in _contextPers.TR_Phone on a.psCode equals c.psCode into phone
from c in phone.DefaultIfEmpty()
where a.Id == UnitID.bookingHeaderID
select new
{
dataPsCode = a.psCode,
npwp = x.NPWP,
nama = x.name,
address = (b == null ? "-" : b.address),
phone = (c == null ? "-" : c.number)
}).ToList();
var getDataDocumentPPJB = (from x in _contextProp.TR_BookingDocument
join a in _contextProp.MS_DocumentPS on x.docID equals a.Id
where x.bookingHeaderID == UnitID.bookingHeaderID && a.docCode == "PPJB"
select x).FirstOrDefault();
var getDataDocumentCN = (from x in _contextProp.TR_BookingDocument
join a in _contextProp.MS_DocumentPS on x.docID equals a.Id
where x.bookingHeaderID == UnitID.bookingHeaderID && a.docCode == "CN"
select x).FirstOrDefault();
var getDataDocumentKPU = (from x in _contextProp.TR_BookingDocument
join a in _contextProp.MS_DocumentPS on x.docID equals a.Id
where x.bookingHeaderID == UnitID.bookingHeaderID && a.docCode == "KPU"
select x).FirstOrDefault();
var getDataDocumentPPPU = (from x in _contextProp.TR_BookingDocument
join a in _contextProp.MS_DocumentPS on x.docID equals a.Id
where x.bookingHeaderID == UnitID.bookingHeaderID && a.docCode == "PPPU"
select x).FirstOrDefault();
var getMainData = (from a in _contextProp.TR_BookingHeader
join b in _contextProp.MS_Unit on a.unitID equals b.Id into unit
from b in unit.DefaultIfEmpty()
join d in _contextProp.MS_Area on b.areaID equals d.Id into area
from d in area.DefaultIfEmpty()
join e in _contextProp.MS_Project on b.projectID equals e.Id into project
from e in project.DefaultIfEmpty()
join f in _contextProp.MS_Product on b.productID equals f.Id into product
from f in product.DefaultIfEmpty()
join j in _contextProp.MS_Term on a.termID equals j.Id into term
from j in term.DefaultIfEmpty()
join l in getDataPersonal on a.psCode equals l.dataPsCode into pers
from l in pers.DefaultIfEmpty()
join m in _contextProp.LK_PayType on a.BFPayTypeCode equals m.payTypeCode into payType
from m in payType.DefaultIfEmpty()
join n in _contextProp.MS_Transfrom on a.transID equals n.Id into transform
from n in transform.DefaultIfEmpty()
join o in _contextNew.MS_Schema.ToList() on a.scmCode equals o.scmCode into schema
from o in schema.DefaultIfEmpty()
join q in _contextProp.MS_SalesEvent on a.eventID equals q.Id into salesEvent
from q in salesEvent.DefaultIfEmpty()
join r in _contextProp.LK_SADStatus on a.SADStatusID equals r.Id into SADStatus
from r in SADStatus.DefaultIfEmpty()
join t in _contextProp.TR_BookingDetail on a.Id equals t.bookingHeaderID
join v in _contextProp.TR_BookingCancel on a.Id equals v.bookingHeaderID into bookingCancel
from v in bookingCancel.DefaultIfEmpty()
join z in _contextProp.LK_Reason on v.reasonID equals z.Id into lkReason
from z in lkReason.DefaultIfEmpty()
join aa in _contextProp.MS_Category on b.categoryID equals aa.Id into ca
from aa in ca.DefaultIfEmpty()
join ab in _contextProp.MS_Cluster on b.clusterID equals ab.Id into cl
from ab in cl.DefaultIfEmpty()
join ac in _contextProp.MS_Detail on b.detailID equals ac.Id into dt
from ac in dt.DefaultIfEmpty()
join ae in _contextProp.MS_UnitCode on b.unitCodeID equals ae.Id into unc
from ae in unc.DefaultIfEmpty()
where a.Id == UnitID.bookingHeaderID
group t by new
{
e.projectName,
f.productName,
d.regionName,
j.termCode,
j.termNo,
termRemarks = j.remarks,
a.memberName,
l.address,
l.npwp,
l.phone,
a.psCode,
q.eventName,
n.transCode,
n.transName,
m.payTypeDesc,
a.bankName,
a.bankNo,
o.scmName,
o.scmCode,
r.statusDesc,
a.memberCode,
l.nama,
a.bookDate,
a.cancelDate,
a.remarks,
z.reasonDesc,
reasonRemarks = v.remarks,
aa.categoryName,
ab.clusterName,
ac.detailName,
b.unitNo,
ae.unitCode,
ae.unitName,
a.PPJBDue,
a.bookCode
} into G
select new GetPSASMainUnitDetailDto
{
projectName = (G.Key.projectName == null ? "" : G.Key.projectName),
productName = (G.Key.productName == null ? "" : G.Key.productName),
territory = (G.Key.regionName == null ? "" : G.Key.regionName),
termCode = (G.Key.termCode == null ? "" : G.Key.termCode),
termNo = (G.Key.termCode == null ? "" : G.Key.termNo + " - " + G.Key.termRemarks),
name = (G.Key.nama == null ? "" : G.Key.nama),
address = (G.Key.address == null ? "" : G.Key.address),
NPWP = (G.Key.npwp == null ? "" : G.Key.npwp),
phone = (G.Key.phone == null ? "" : G.Key.phone),
psCode = (G.Key.psCode == null ? "" : G.Key.psCode),
salesEvent = (G.Key.eventName == null ? "" : G.Key.eventName),
transactionCome = (G.Key.transCode == null ? "" : G.Key.transCode + " | " + G.Key.transName),
payType = (G.Key.payTypeDesc == null ? "" : G.Key.payTypeDesc),
bankName = (G.Key.bankName == null ? "" : G.Key.bankName),
amount = G.Sum(d => d.BFAmount),
noRekening = (G.Key.bankNo == null ? "" : G.Key.bankNo),
schema = (G.Key.scmCode == null ? null : G.Key.scmCode) + " - " + (G.Key.scmName == null ? null : G.Key.scmName),
memberID = (G.Key.memberCode == null ? "" : G.Key.memberCode),
memberName = (G.Key.memberName == null ? "" : G.Key.memberName),
bookDate = G.Key.bookDate,
bookedStatus = (G.Key.cancelDate == null ? "" : "Cancel"),
cancelDate = G.Key.cancelDate,
remarks = (G.Key.remarks == null ? "" : G.Key.remarks),
status = (G.Key.statusDesc == null ? "" : G.Key.statusDesc),
reason = (G.Key.reasonDesc == null ? "" : G.Key.reasonDesc),
reasonRemarks = (G.Key.reasonRemarks == null ? "" : G.Key.reasonRemarks),
finDesc = (G.Key.termRemarks == null ? "" : G.Key.termRemarks),
unitCode = (G.Key.unitCode == null ? "" : G.Key.unitCode),
unitNo = (G.Key.unitNo == null ? "" : G.Key.unitNo),
unitName = (G.Key.unitName == null ? "" : G.Key.unitName),
sadStatus = (G.Key.statusDesc == null ? "" : G.Key.statusDesc),
membershipType = (G.Key.scmName == null ? "" : G.Key.scmName),
bookCode = (G.Key.bookCode == null ? "" : G.Key.bookCode),
categoryName = (G.Key.categoryName == null ? "" : G.Key.categoryName),
clusterName = (G.Key.clusterName == null ? "" : G.Key.clusterName),
detailName = (G.Key.detailName == null ? "" : G.Key.detailName)
}
).FirstOrDefault();
getMainData.ppjb = getDataDocumentPPJB == null ? "" : getDataDocumentPPJB.docNo;
getMainData.kpu = getDataDocumentKPU == null ? "" : getDataDocumentKPU.docNo;
getMainData.pppu = getDataDocumentPPPU == null ? "" : getDataDocumentPPPU.docNo;
getMainData.cn = getDataDocumentCN == null ? "" : getDataDocumentCN.docNo;
return(getMainData);
}
public GetUniversalMainListDto GetPSASMain(GetPSASParamsDto input)
{
var UnitID = _ipriceAppService.GetParameter(input);
var getAllHeaderMain = (from x in _trBookingDetailRepo.GetAll()
join y in _lkItemRepo.GetAll() on x.itemID equals y.Id
where x.bookingHeaderID == UnitID.bookingHeaderID
select new GetPSASMainHeaderListDto
{
itemName = y.itemName,
coCode = x.coCode,
area = x.area,
netPriceMKT = x.netPriceMKT,
netPriceComm = x.netPriceComm,
netNetPrice = x.netNetPrice
}
).ToList();
//var getAllPersonals = _personalsRepo.GetAllList();
//var getAllPersonalsMember = _personalMemberRepo.GetAllList();
//var getAllAddress = _trAddressRepo.GetAllList();
//var getAllPhone = _trPhoneRepo.GetAllList();
//var getAllSchema = _msSchemaRepo.GetAllList();
var getDataPersonal = (from x in _contextPers.PERSONAL
join a in _trBookingHeaderRepo.GetAll().ToList() on x.psCode equals a.psCode
join b in _contextPers.TR_Address on a.psCode equals b.psCode into address
from b in address.DefaultIfEmpty()
join c in _contextPers.TR_Phone on a.psCode equals c.psCode into phone
from c in phone.DefaultIfEmpty()
where a.Id == UnitID.bookingHeaderID
select new
{
dataPsCode = a.psCode,
npwp = x.NPWP,
nama = x.name,
address = (b == null ? "-" : b.address),
phone = (c == null ? "-" : c.number)
}).ToList();
var getMainData = (from a in _contextProp.TR_BookingHeader
join b in _contextProp.MS_Unit on a.unitID equals b.Id into unit
from b in unit.DefaultIfEmpty()
join d in _contextProp.MS_Area on b.areaID equals d.Id into area
from d in area.DefaultIfEmpty()
join e in _contextProp.MS_Project on b.projectID equals e.Id into project
from e in project.DefaultIfEmpty()
join f in _contextProp.MS_Product on b.productID equals f.Id into product
from f in product.DefaultIfEmpty()
join j in _contextProp.MS_Term on a.termID equals j.Id into term
from j in term.DefaultIfEmpty()
join l in getDataPersonal on a.psCode equals l.dataPsCode
join m in _contextProp.LK_PayType on a.BFPayTypeCode equals m.payTypeCode into payType
from m in payType.DefaultIfEmpty()
join n in _contextProp.MS_Transfrom on a.transID equals n.Id into transform
from n in transform.DefaultIfEmpty()
join o in _contextNew.MS_Schema.ToList() on a.scmCode equals o.scmCode into schema
from o in schema.DefaultIfEmpty()
join q in _contextProp.MS_SalesEvent on a.eventID equals q.Id into salesEvent
from q in salesEvent.DefaultIfEmpty()
join r in _contextProp.LK_SADStatus on a.SADStatusID equals r.Id into SADStatus
from r in SADStatus.DefaultIfEmpty()
join t in _contextProp.TR_BookingDetail on a.Id equals t.bookingHeaderID
join v in _contextProp.TR_BookingCancel on a.Id equals v.bookingHeaderID into bookingCancel
from v in bookingCancel.DefaultIfEmpty()
join z in _contextProp.LK_Reason on v.reasonID equals z.Id into lkReason
from z in lkReason.DefaultIfEmpty()
where a.Id == UnitID.bookingHeaderID
group t by new
{
e.projectName,
f.productName,
d.regionName,
j.termCode,
j.termNo,
termRemarks = j.remarks,
a.memberName,
l.address,
l.npwp,
l.phone,
a.psCode,
q.eventName,
n.transCode,
n.transName,
m.payTypeDesc,
a.bankName,
a.bankNo,
o.scmName,
o.scmCode,
r.statusDesc,
a.memberCode,
l.nama,
a.bookDate,
a.cancelDate,
a.remarks,
z.reasonDesc,
reasonRemarks = v.remarks
} into G
select new GetPSASMainListDto
{
projectName = (G.Key.projectName == null ? "-" : G.Key.projectName),
productName = (G.Key.productName == null ? "-" : G.Key.productName),
territory = (G.Key.regionName == null ? "-" : G.Key.regionName),
termCode = (G.Key.termCode == null ? "-" : G.Key.termCode),
termNo = (G.Key.termCode == null ? "-" : G.Key.termNo + " - " + G.Key.termRemarks),
name = (G.Key.nama == null ? "-" : G.Key.nama),
address = (G.Key.address == null ? "-" : G.Key.address),
NPWP = (G.Key.npwp == null ? "-" : G.Key.npwp),
phone = (G.Key.phone == null ? "-" : G.Key.phone),
psCode = (G.Key.psCode == null ? "" : G.Key.psCode),
salesEvent = (G.Key.eventName == null ? "-" : G.Key.eventName),
transactionCome = (G.Key.transCode == null ? "-" : G.Key.transCode + " | " + G.Key.transName),
payType = (G.Key.payTypeDesc == null ? "-" : G.Key.payTypeDesc),
bankName = (G.Key.bankName == null ? "" : G.Key.bankName),
amount = G.Sum(d => d.BFAmount),
noRekening = (G.Key.bankNo == null ? "" : G.Key.bankNo),
schema = (G.Key.scmCode == null ? null : G.Key.scmCode) + " - " + (G.Key.scmName == null ? null : G.Key.scmName),
memberID = (G.Key.memberCode == null ? "" : G.Key.memberCode),
memberName = (G.Key.memberName == null ? "" : G.Key.memberName),
bookDate = G.Key.bookDate,
bookedStatus = (G.Key.cancelDate == null ? "-" : "Cancel"),
cancelDate = G.Key.cancelDate,
remarks = (G.Key.remarks == null ? "-" : G.Key.remarks),
status = (G.Key.statusDesc == null ? "-" : G.Key.statusDesc),
reason = (G.Key.reasonDesc == null ? "-" : G.Key.reasonDesc),
reasonRemarks = (G.Key.reasonRemarks == null ? "-" : G.Key.reasonRemarks)
}
).FirstOrDefault();
var getDataUnit = (from bh in _trBookingHeaderRepo.GetAll()
join u in _msUnitRepo.GetAll() on bh.unitID equals u.Id
join uc in _msUnitCodeRepo.GetAll() on u.unitCodeID equals uc.Id
where u.Id == UnitID.unitID
select new {
bh.bookCode,
u.unitNo,
uc.unitCode
}).FirstOrDefault();
var getAllData = new GetUniversalMainListDto
{
GetAllPSASMainHeaderDto = getAllHeaderMain,
GetPSASMainDto = getMainData,
unitCode = getDataUnit.unitCode,
unitNo = getDataUnit.unitNo,
bookCode = getDataUnit.bookCode
};
return(getAllData);
}
