/// <summary>
/// Формирования объекта отображения информации об отправках (по накладной за отчётный месяц)
/// </summary>
/// <param name="operationCategory">filter on category</param>
/// <param name="chooseDate">Work period</param>
/// <param name="page">Current page</param>
/// <param name="pageSize">Count item on page</param>
/// <param name="recordCount"></param>
/// <returns></returns>
public IEnumerable <ShippingDTO> ShippingsViews(EnumOperationType operationCategory, DateTime chooseDate, int page, int pageSize, out short recordCount)
{
//exit when empty result (decrease count server query)
if (this._engage.GetCountRows <krt_Guild18>(x => x.reportPeriod == chooseDate) == 0)
{
recordCount = 0;
return(new List <ShippingDTO>());
}
//linq to object(etsng) copy in memory (because EF don't support two dbcontext work together, resolve through expression tree maybe)
var wrkData = this._engage.GetTable <krt_Guild18, int>(x => x.reportPeriod == chooseDate).ToList();
//dispatch
var kg18Src = wrkData.GroupBy(x => new { x.reportPeriod, x.idDeliviryNote, x.warehouse })
.OrderBy(x => x.Key.idDeliviryNote).ToList();
/*linqkit*/
//v_otpr
var votprPredicate = PredicateBuilder.New <v_otpr>(false).DefaultExpression.And(x => ((x.oper == (short)operationCategory) || operationCategory == EnumOperationType.All) && x.state == 32 && (new[] { "3494", "349402" }.Contains(x.cod_kl_otpr) || new[] { "3494", "349402" }.Contains(x.cod_klient_pol)));
votprPredicate = kg18Src.Select(x => x.Key.idDeliviryNote).Aggregate(votprPredicate, (current, value) => current.Or(e => e.id == value && ((e.oper == (short)operationCategory) || operationCategory == EnumOperationType.All))).Expand();
var voSrc = this._engage.GetTable <v_otpr, int>(votprPredicate).ToList();
recordCount = (short)voSrc.Count();
//v_o_v
//var vovPredicate = PredicateBuilder.New<v_o_v>(false).DefaultExpression;
var vovPredicate = voSrc.Select(x => x.id).Aggregate(PredicateBuilder.New <v_o_v>(false).DefaultExpression, (current, value) => current.Or(v => v.id_otpr == value)).Expand();
var vovSrc = this._engage.GetTable <v_o_v, int>(vovPredicate).ToList();
//etsng
//var etsngPredicate = PredicateBuilder.New<etsng>(false);
var etsngPredicate = voSrc.Select(x => x.cod_tvk_etsng).Aggregate(PredicateBuilder.New <Etsng>(false).DefaultExpression, (current, value) => current.Or(v => v.Etsng1 == value)).Expand();
var etsngSrc = this._engage.GetTable <Etsng, int>(etsngPredicate).ToList();
var result = (from kg in kg18Src join vo in voSrc on kg.Key.idDeliviryNote equals vo.id into g1
from item in g1.DefaultIfEmpty() where (item != null && item.oper == (short)operationCategory) || operationCategory == EnumOperationType.All
join e in etsngSrc on item == null ? "" : item.cod_tvk_etsng equals e.Etsng1 into g2
from item2 in g2.DefaultIfEmpty()
select new ShippingDTO()
{
VOtpr = item,
Vovs = vovSrc.Where(x => (x != null) && x.id_otpr == (item == null ? 0 : item.id)),
VPams = this._engage.GetTable <v_pam, int>(PredicateBuilder.New <v_pam>().DefaultExpression.And(x => x.state == 32 && new[] { "3494", "349402" }.Contains(x.kodkl))
.And(PredicateExtensions.InnerContainsPredicate <v_pam, int>("id_ved",
wrkData.Where(x => x.reportPeriod == chooseDate && x.idDeliviryNote == (item != null ? item.id : 0) && x.type_doc == 2).Select(y => y.idSrcDocument != null ? (int)y.idSrcDocument : 0))).Expand())
.ToList(),
VAkts = this._engage.GetTable <v_akt, int>(PredicateBuilder.New <v_akt>().DefaultExpression.And(x => new[] { "3494", "349402" }.Contains(x.kodkl) && x.state == 32)
.And(PredicateExtensions.InnerContainsPredicate <v_akt, int>("Id",
wrkData.Where(x => x.reportPeriod == chooseDate && x.idDeliviryNote == (item != null ? item.id : 0) && x.type_doc == 3).Select(y => y.idSrcDocument != null ? (int)y.idSrcDocument : 0))).Expand())
.ToList(),
VKarts = this._engage.GetTable <v_kart, int>(PredicateBuilder.New <v_kart>().DefaultExpression.And(x => new[] { "3494", "349402" }.Contains(x.cod_pl))
.And(PredicateExtensions.InnerContainsPredicate <v_kart, int>("Id",
wrkData.Where(z => z.reportPeriod == chooseDate && z.idDeliviryNote == (item != null ? item.id : (int?)null)).Select(y => y.idCard != null ? (int)y.idCard : 0))).Expand())
.ToList(),
KNaftan = this._engage.GetTable <krt_Naftan, int>(PredicateExtensions.InnerContainsPredicate <krt_Naftan, long>("keykrt",
wrkData.Where(z => z.reportPeriod == chooseDate && z.idDeliviryNote == (item != null ? item.id : (int?)null)).Select(y => y.idScroll != null ? (long)y.idScroll : 0)))
.ToList(),
Etsng = item2,
Guild18 = new krt_Guild18 {
reportPeriod = kg.Key.reportPeriod,
idDeliviryNote = kg.Key.idDeliviryNote,
warehouse = kg.Key.warehouse
}
}).Skip(pageSize * (page - 1)).Take(pageSize).OrderByDescending(x => x.VOtpr != null ? x.VOtpr.n_otpr : x.Guild18.idDeliviryNote.ToString()).ToList();
return(result);
}
/// <summary>
/// Получение данных со стороны БД САПОД (используется для предварительного просмотра текущей документации по накладным)
/// </summary>
/// <param name="reportPeriod"></param>
/// <param name="preview"></param>
/// <param name="shiftPage"></param>
/// <returns></returns>
public bool PackDocuments(DateTime reportPeriod, IList <ShippingInfoLineDTO> preview, byte shiftPage = 3)
{
var startDate = reportPeriod.AddDays(-shiftPage);
var endDate = reportPeriod.AddMonths(1).AddDays(shiftPage);
try {
//type_doc 1 => one transaction (one request per one dbcontext)
List <krt_Guild18> result = (from item in preview join vn in this._engage.GetTable <v_nach, int>(PredicateBuilder.New <v_nach>().DefaultExpression
.And(x => x.type_doc == 1 && new[] { "3494", "349402" }.Contains(x.cod_kl))
.And(PredicateExtensions.InnerContainsPredicate <v_nach, int?>("id_otpr", preview.Select(x => (int?)x.Shipping.id))).Expand())
on item.Shipping.id equals vn.id_otpr
select new krt_Guild18()
{
reportPeriod = reportPeriod,
warehouse = item.Warehouse,
idDeliviryNote = item.Shipping.id,
type_doc = 1, idSrcDocument = item.Shipping.id,
code = Convert.ToInt32(vn.cod_sbor.Split(new[] { '.', ',' })[0]),
sum = (decimal)(vn.summa + vn.nds),
rateVAT = Math.Round((decimal)(vn.nds / vn.summa), 2),
codeType = new[] { 166, 173, 300, 301, 344 }.Contains(Convert.ToInt32(vn.cod_sbor.Split(new[] { '.', ',' })[0])),
idCard = vn.id_kart,
idScroll = this._engage.GetGroup <krt_Naftan_orc_sapod, long>(x => x.keykrt, x => x.id_kart == vn.id_kart).Select(y => y.First().keykrt).FirstOrDefault()
}).ToList();
foreach (var dispatch in preview)
{
var shNumbers = dispatch.WagonsNumbers.Select(x => x.n_vag).ToList();
//type_doc 2 =>one transaction (one request per one dbcontext) (type 2 and type_doc 4 (065))
//in memory because not all method support entity to sql => more easy do it in memory
using (this._engage.Uow = new UnitOfWork()) {
result.AddRange((from vpv in this._engage.Uow.GetRepository <v_pam_vag>().GetAll(x => shNumbers.Contains(x.nomvag), false)
join vp in this._engage.Uow.GetRepository <v_pam>().GetAll(x => x.state == 32 && new[] { "3494", "349402" }.Contains(x.kodkl) && x.dved > startDate && x.dved < endDate, false) on vpv.id_ved equals vp.id_ved
join vn in this._engage.Uow.GetRepository <v_nach>().GetAll(x => x.type_doc == 2 && new[] { "3494", "349402" }.Contains(x.cod_kl), false)
on vp.id_kart equals vn.id_kart
select new { vp.id_ved, vn.cod_sbor, vn.summa, vn.nds, vn.id_kart }).Distinct().ToList()
.Select(x => new krt_Guild18 {
reportPeriod = reportPeriod,
warehouse = dispatch.Warehouse,
idDeliviryNote = dispatch.Shipping.id,
type_doc = 2, idSrcDocument = x.id_ved,
code = Convert.ToInt32(x.cod_sbor.Split(new[] { '.', ',' })[0]),
sum = (decimal)(x.summa + x.nds),
idCard = x.id_kart, rateVAT = Math.Round((decimal)(x.nds / x.summa), 2),
codeType = new[] { "166", "173", "300", "301", "344" }.Contains(x.cod_sbor.Split(new[] { '.', ',' })[0]),
idScroll = this._engage.GetGroup <krt_Naftan_orc_sapod, long>(y => y.keykrt, z => z.id_kart == x.id_kart).Select(y => y.First().keykrt).FirstOrDefault()
}));
}
//065
using (this._engage.Uow = new UnitOfWork()) {
result.AddRange((from vpv in this._engage.Uow.GetRepository <v_pam_vag>().GetAll(x => shNumbers.Contains(x.nomvag), false) join vn in this._engage.Uow.GetRepository <v_nach>()
.GetAll(x => x.type_doc == 4 && x.cod_sbor == "065" && new[] { "3494", "349402" }.Contains(x.cod_kl) && x.date_raskr > startDate && x.date_raskr < endDate, false) on
new { p1 = vpv.d_pod, p2 = vpv.d_ub } equals new { p1 = vn.date_raskr, p2 = vn.date_raskr }
select new { vpv.id_ved, vn.cod_sbor, vn.summa, vn.nds, vn.id_kart }).Distinct().ToList()
.Select(x =>
new krt_Guild18 {
reportPeriod = reportPeriod,
warehouse = dispatch.Warehouse,
idDeliviryNote = dispatch.Shipping.id,
type_doc = 4, idSrcDocument = x.id_kart,
code = Convert.ToInt32(x.cod_sbor.Split(new[] { '.', ',' })[0]),
sum = (decimal)(x.summa + x.nds),
idCard = x.id_kart, rateVAT = Math.Round((decimal)(x.nds / x.summa), 2),
codeType = new[] { "166", "173", "300", "301", "344" }.Contains(x.cod_sbor.Split(new[] { '.', ',' })[0]),
idScroll = this._engage.GetGroup <krt_Naftan_orc_sapod, long>(y => y.keykrt, z => z.id_kart == x.id_kart).Select(y => y.First().keykrt).FirstOrDefault()
}));
}
////type_doc 3 =>one transaction (one request per one dbcontext)
using (this._engage.Uow = new UnitOfWork()) {
result.AddRange((from vav in this._engage.Uow.GetRepository <v_akt_vag>().GetAll(x => shNumbers.Contains(x.nomvag), false)
join va in this._engage.Uow.GetRepository <v_akt>().GetAll(x => x.state == 32 && new[] { "3494", "349402" }.Contains(x.kodkl) && x.dakt > startDate && x.dakt < endDate, false) on vav.id_akt equals va.id
join vn in this._engage.Uow.GetRepository <v_nach>().GetAll(x => x.type_doc == 3 && new[] { "3494", "349402" }.Contains(x.cod_kl), false)
on va.id_kart equals vn.id_kart
select new { va.id, vn.cod_sbor, vn.summa, vn.nds, vn.id_kart }).Distinct().ToList()
.Select(x =>
new krt_Guild18 {
reportPeriod = reportPeriod,
warehouse = dispatch.Warehouse,
idDeliviryNote = dispatch.Shipping.id,
type_doc = 3, idSrcDocument = x.id,
code = Convert.ToInt32(x.cod_sbor.Split(new[] { '.', ',' })[0]),
sum = (decimal)(x.summa + x.nds),
idCard = x.id_kart,
rateVAT = Math.Round((decimal)(x.nds / x.summa), 2),
codeType = new[] { "166", "173", "300", "301", "344" }.Contains(x.cod_sbor.Split(new[] { '.', ',' })[0]),
idScroll = this._engage.GetGroup <krt_Naftan_orc_sapod, long>(y => y.keykrt, z => z.id_kart == x.id_kart).Select(y => y.First().keykrt).FirstOrDefault()
}));
}
//luggage (type_doc 0 or 4)
result.AddRange(this._engage.GetTable <krt_Naftan_orc_sapod, long>(x => new[] { 611, 629, 125 }.Contains(x.vidsbr) && x.dt.Month == reportPeriod.Month && x.dt.Year == reportPeriod.Year).ToList()
.Select(x => new krt_Guild18 {
reportPeriod = reportPeriod,
type_doc = x.tdoc,
idSrcDocument = (int)x.id_kart, code = x.vidsbr,
sum = x.sm, rateVAT = Math.Round((decimal)(x.nds / x.sm_no_nds), 2),
codeType = new[] { 166, 173, 300, 301, 344 }.Contains(x.vidsbr),
idCard = (int)x.id_kart, idScroll = x.keykrt
}));
}
} catch (Exception) {
return(false);
}
return(true);
}
