public static List <RefGroupsEnt> GetAllGroups()
{
using (base2Entities db = new base2Entities())
{
return((from cref in db.C_Reference91 // выгрузим все, чтобы 1000 раз не лезть в БД
select new RefGroupsEnt
{
Name = cref.C_Description,
Code = cref.C_Code
}).ToList());
}
}
public static List<SalesEnt> QueryForGetSalesForSeb(DateTime stDate, DateTime endDate, base3Entities db, base2Entities db3)
{
var result = new List<SalesEnt>();
var res = (from r77 in db3.C_Reference69
join r78 in db3.C_Reference69 on r77.C_ParentIDRRef equals r78.C_IDRRef
select new SalesEnt
{
Mont = 0,
Year = 0,
Period = new DateTime(),
refId = r77.C_IDRRef,
ClientRefId = new byte[] { },
SalersRefId = new byte[] { },
CostPrise = decimal.Zero,
CountPur = decimal.Zero,
SalesWithoutNDS = decimal.Zero,
CountSal = decimal.Zero,
DivName = r77.C_Description,
GroupCode = r78.C_Code,
GroupName = r78.C_Description,
BuyerCode = string.Empty,
BuyerName = string.Empty
}
);
//покупатели
var res4 = (from s888 in db3.C_Reference62
select new SalesEnt
{
Mont = 0,
Year = 0,
Period = new DateTime(),
refId = new byte[] { },
ClientRefId = s888.C_IDRRef,
SalersRefId = new byte[] { },
CostPrise = decimal.Zero,
CountPur = decimal.Zero,
SalesWithoutNDS = decimal.Zero,
CountSal = decimal.Zero,
DivName = string.Empty,
GroupCode = string.Empty,
GroupName = string.Empty,
BuyerCode = s888.C_Code,
BuyerName = s888.C_Description
});
#region посчитаем себистоимость товара за период
/// Группируем по группа товаров в разрезе по месяцам
/// нам этого будет достаточно, чтобы посчитать среднюю себестоимость остатка товара на указанный период
/// т.к. минимальный период это месяц
/// после будем циклом бежать по группам и внутри считать сколько пришло сколько ушло товара
/// и по какой цене
//Себестоимость считаем от начала времен + сам поставщик (C_Value1_RRRef)
var resCostSeb = (from s888 in db3.C_AccumRg6661
where s888.C_Period < endDate
group s888 by new { s888.C_Fld6662_RRRef, s888.C_Period.Month, s888.C_Period.Year } into g
select new SalesEnt
{
Mont = g.FirstOrDefault().C_Period.Month,
Year = g.FirstOrDefault().C_Period.Year,
Period = g.FirstOrDefault().C_Period,
refId = g.FirstOrDefault().C_Fld6662_RRRef,
ClientRefId = new byte[] { },
SalersRefId = g.FirstOrDefault().C_Fld6670RRef,
CostPrise = g.Sum(_ => _.C_Fld6673),
CountPur = decimal.Zero,
SalesWithoutNDS = decimal.Zero,
CountSal = decimal.Zero,
DivName = string.Empty,
GroupCode = string.Empty,
GroupName = string.Empty,
BuyerCode = string.Empty,
BuyerName = string.Empty
});
// количество сколько поступило от начала времен
var resCountSeb = (from s888 in db3.C_AccumRgTn6677
where s888.C_Period < endDate
group s888 by new { s888.C_Fld6662_RRRef, s888.C_Period.Month, s888.C_Period.Year } into g
select new SalesEnt
{
Mont = 0,
Year = 0,
Period = new DateTime(),
refId = new byte[] { },
ClientRefId = new byte[] { },
SalersRefId = g.FirstOrDefault().C_Fld6662_RRRef,
CostPrise = decimal.Zero,
CountPur = g.Sum(_ => _.C_Fld6672),
SalesWithoutNDS = decimal.Zero,
CountSal = decimal.Zero,
DivName = string.Empty,
GroupCode = string.Empty,
GroupName = string.Empty,
BuyerCode = string.Empty,
BuyerName = string.Empty
});
var resSalesSeb = (from r27 in db3.C_AccumRgTn6903
where r27.C_Period < endDate
group r27 by new { r27.C_Fld6883_RRRef, r27.C_Period.Month, r27.C_Period.Year } into g
select new SalesEnt
{
Mont = 0,
Year = 0,
Period = new DateTime(),
refId = g.FirstOrDefault().C_Fld6883_RRRef,
ClientRefId = g.FirstOrDefault().C_Fld6890RRef,
SalersRefId = new byte[] { },
CostPrise = decimal.Zero,
CountPur = decimal.Zero,
SalesWithoutNDS = decimal.Zero,
CountSal = g.Sum(_ => _.C_Fld6893),
DivName = string.Empty,
GroupCode = string.Empty,
GroupName = string.Empty,
BuyerCode = string.Empty,
BuyerName = string.Empty
}
);
var resSeb =
(from r in res
join r1 in resCostSeb on r.refId equals r1.refId
join r2 in resSalesSeb on r.refId equals r2.refId
join r3 in resCountSeb on r.refId equals r3.SalersRefId
join r4 in res4 on r2.ClientRefId equals r4.ClientRefId
select new SalesEnt
{
Mont = r1.Mont,
Year = r1.Year,
Period = r1.Period,
refId = r.refId,
ClientRefId = r2.ClientRefId,
SalersRefId = r1.SalersRefId,
CostPrise = r3.CountPur != 0 ? r1.CostPrise / r3.CountPur : 0,
CountPur = r3.CountPur,
SalesWithoutNDS = r2.SalesWithoutNDS,
CountSal = r2.CountSal,
DivName = r.DivName,
GroupCode = r.GroupCode,
GroupName = r.GroupName,
BuyerCode = r4.BuyerCode,
BuyerName = r4.BuyerName
}).ToList();
List<SalesEnt> gSeb = new List<SalesEnt>();
foreach (var gr in db.C_Reference63.ToList())
{
decimal resSebValue = decimal.Zero;
decimal resSebValueCount = decimal.Zero;
var gst = decimal.Zero;
var gent = decimal.Zero;
var pastTmp = decimal.Zero;
var tmp = decimal.Zero;
var counterPur = 0;
var counterSales = 0;
bool WeGoCalcSeb = false;
bool WeGoCalcSebReal = false;
bool isSoSmall = false;
// вытащим только нашу группу
var salesForGroup = resSeb.Where(_ => _.GroupCode == gr.C_Code).OrderBy(_ => _.Period).ToList();
while (counterSales < salesForGroup.Count && !WeGoCalcSebReal)
{
if (!isSoSmall)
pastTmp = tmp;
if (tmp <= 0 && counterPur < salesForGroup.Count)
{
tmp += salesForGroup[counterPur].CountPur;
counterPur++;
}
// если после того как мы сбегали за партией, у нас на складе все еще отрецательное кол-во,
// бежим еще за одной партией
if (tmp <= 0)
{
// это нужно в том случае если мы дойдем до момента расчета себестоимости для нашего периода
// а мы дважды бегали за новой партией
// мы должны будем вернуться и посмотреть цены за прошлые месяца
// нам нельзя перетереть pastTmp
isSoSmall = true;
if (counterPur < salesForGroup.Count)
continue;
}
else
{
isSoSmall = false;
}
while (counterSales < salesForGroup.Count && !WeGoCalcSebReal)
{
// если мы еще не дошли до даты начала периода - перебираем месяца смотрим что подали что купили
// чтобы определить с какого месяца брать себестоимость для нашего периода
// смотрим от продаж, т.к. мы могли продать то что купили 2 года назад в этом периоде
var dtT = new DateTime(salesForGroup[counterSales].Year, salesForGroup[counterSales].Mont, 1);
var dtMt = new DateTime(stDate.Year, stDate.Month, 1);
if (!WeGoCalcSeb && dtT < dtMt)
{
tmp -= salesForGroup[counterSales].CountSal;
counterSales++;
if (tmp > 0) // после того как мы отняли от кол-ва поставки, кол-во продажи, проверим если у нас еще из партии что то на складе
// если есть, то эту же партию мы будем продавать и в следующем месяце
{
continue;
}
else // если нет, бежим за новой партией
{
break;
}
}//если дошли - считаем среднюю себестоимость остатка
else
{
if (!WeGoCalcSeb)
{
for (var i = counterPur; i < counterSales; i++)
{ gst += salesForGroup[i].CostPrise; }
}
WeGoCalcSeb = true;
// при расчете среднего остатка за период мы уже бежим до конца периода по продажам
if (dtMt <= dtT)
{
counterSales++;
if (pastTmp < 0)
{
var antiCounter = 1;
var summCount = decimal.Zero;
do
{
summCount += salesForGroup[counterPur - 1 - antiCounter].CountPur;
if (summCount > Math.Abs(pastTmp))
{
resSebValue += Math.Abs(pastTmp) * salesForGroup[counterPur - 1 - antiCounter].CostPrise;
resSebValueCount += Math.Abs(pastTmp) * salesForGroup[counterPur - 1 - antiCounter].CountPur;
pastTmp = summCount + pastTmp;
break;
}
else
{
resSebValue += summCount * salesForGroup[counterPur - 1 - antiCounter].CostPrise;
resSebValue += summCount * salesForGroup[counterPur - 1 - antiCounter].CountPur;
pastTmp = 0;
antiCounter++;
continue;
}
}
while (pastTmp < 0);
}
resSebValue += salesForGroup.Skip(counterPur).Where(_ => _.Year <= endDate.Year && _.Mont < endDate.Month).Sum(_ => _.CostPrise * _.CountSal);
resSebValueCount += salesForGroup.Skip(counterPur).Where(_ => _.Year <= endDate.Year && _.Mont < endDate.Month).Sum(_ => _.CountPur);
WeGoCalcSebReal = true;
continue;
}
else //все посчитали выходим
{
break;
}
}
};
};
gent = gst + salesForGroup.Skip(counterSales).Sum(_ => _.CostPrise);
//for (var i = counterPur; i < counterSales; i++)
//{ gent += salesForGroup[i].CountPur; }
if (resSebValue > 0 && resSebValueCount > 0)
gSeb.Add(new SalesEnt() { GroupCode = gr.C_Code, CostPrise = resSebValue / resSebValueCount, CountGoodsSt = gst, CountGoodsEnd = gent });
}
#endregion
return gSeb;
}
public static List<SalesEnt> getPurMan(DateTime stDate, DateTime endDate)
{
using (base2Entities db = new base2Entities())
{
List<SalesEnt> result = new List<SalesEnt>();
var tt = (from adz in db.C_AccumRg6661
group adz by adz.C_Fld6670RRef into g
select new SalesEnt
{
SalersRefId = g.FirstOrDefault().C_Fld6670RRef,
CostPrise = g.Sum(_ => _.C_Fld6673),
SalerCode = string.Empty,
SalerName = string.Empty
});
var t1 = (from r67 in db.C_Reference62
select new SalesEnt
{
SalersRefId = r67.C_IDRRef,
CostPrise = decimal.Zero,
SalerCode = r67.C_Code,
SalerName = r67.C_Description
});
result = (from t0 in tt
join t_1 in t1 on t0.SalersRefId equals t_1.SalersRefId
select new SalesEnt
{
SalersRefId = t0.SalersRefId,
CostPrise = t0.CostPrise,
SalerCode = t_1.SalerCode,
SalerName = t_1.SalerName
}).ToList();
return result;
}
}
public static List<SalesModel> GetSalesOneQuery(DateTime stDate, DateTime endDate)
{
using (base3Entities db = new base3Entities())
{
var db3 = new base2Entities();
List<SalesModel> result = new List<SalesModel>();
var qSeb = Accessors.QueryForGetSalesForSeb(stDate, endDate, db, db3);
//только инфа по группам и производителям или как их
SalesModel tmp = new SalesModel();
tmp.Date = stDate;
tmp.Sales = QueryForGetSales(stDate, endDate, db, qSeb, db3);
if (tmp.Sales.Count != 0)
result.Add(tmp);
return result;
}
}
public static List<SalesModel> GetSales(DateTime stDate, DateTime endDate, bool isLoadBeginOfTheTime = false)
{
using (base3Entities db = new base3Entities())
{
endDate = endDate.AddSeconds(-1);
List<SalesModel> result = new List<SalesModel>();
int[] endMonthYear = new int[] { endDate.Month, endDate.Year };
var db3 = new base2Entities();
int monthCount = 0;
int[] startMonthYear = new int[] { stDate.Month, stDate.Year };//будем бежать от начала до конца периода
List<SalesEnt> qSeb = new List<SalesEnt>();
if(!isLoadBeginOfTheTime)
qSeb = Accessors.QueryForGetSalesForSeb(stDate, endDate, db, db3);
do
{
DateTime stDt = new DateTime(startMonthYear[1], startMonthYear[0], 1);
DateTime endDt = new DateTime();
#region Cчитаем кол-во месяцев
if (startMonthYear[0] == 12)
{
startMonthYear[1]++;
startMonthYear[0] = 1;
monthCount++;
endDt = new DateTime(startMonthYear[1], startMonthYear[0], 1);
}
else
{
startMonthYear[0]++;
monthCount++;
endDt = new DateTime(startMonthYear[1], startMonthYear[0], 1);
}
#endregion
SalesModel tmp = new SalesModel();
tmp.Date = stDt;
tmp.Sales = QueryForGetSales(stDt, endDt, db, qSeb, db3);
if(tmp.Sales.Count != 0)
result.Add(tmp);
}
while ((startMonthYear[1] <= endMonthYear[1] && startMonthYear[1] != endMonthYear[1]) || (startMonthYear[0] <= endMonthYear[0] && startMonthYear[1] == endMonthYear[1]));
return result;
}
}
private static void ContinGetAddzTr(DateTime stDate, DateTime endDate, List<string> GroupsCode, base2Entities db, out List<GroupsEnt> res, out List<string> codeGroups)
{
if (GroupsCode.Count() > 0)
{
res = (from accEd in db.C_AccumRg6542
join refs in db.C_Reference91 on accEd.C_Fld6546RRef equals refs.C_IDRRef
//join en302 in db.C_Enum320 on refs.C_Fld1141RRef equals en302.C_IDRRef
//join en450 in db.C_Enum217 on refs.C_Fld1142RRef equals en450.C_IDRRef
join men in db.C_Reference62 on accEd.C_Fld6548_RRRef equals men.C_IDRRef
where accEd.C_Period >= stDate && accEd.C_Period < endDate && GroupsCode.Contains(refs.C_Code)
select new GroupsEnt
{
Money = accEd.C_Fld6555,
period = accEd.C_Period,
GroupCode = refs.C_Code,
en302 = 1,//en302.C_EnumOrder,
en450 = 0, //en450.C_EnumOrder,
MenCode = men.C_Code,
MenName = men.C_Description
}).ToList();
codeGroups = (from gg in db.C_Reference91
where GroupsCode.Contains(gg.C_Code)
select gg.C_Code).OrderBy(_ => _).ToList();
}
else
{
res = (from accEd in db.C_AccumRg6542
join refs in db.C_Reference91 on accEd.C_Fld6546RRef equals refs.C_IDRRef
//join en302 in db.C_Enum302 on refs.C_Fld1334RRef equals en302.C_IDRRef
//join en450 in db.C_Enum450 on refs.C_Fld1333RRef equals en450.C_IDRRef
join men in db.C_Reference62 on accEd.C_Fld6548_RRRef equals men.C_IDRRef
where accEd.C_Period >= stDate && accEd.C_Period < endDate
select new GroupsEnt
{
Money = accEd.C_Fld6555,
period = accEd.C_Period,
GroupCode = refs.C_Code,
en302 = 1,//en302.C_EnumOrder,
en450 = 0, //en450.C_EnumOrder,
MenCode = men.C_Code,
MenName = men.C_Description
}).ToList();
codeGroups = (from gg in db.C_Reference91
select gg.C_Code).OrderBy(_ => _).ToList();
}
}
public static List<GroupsEnt> GetAddsTranz(DateTime stDate, DateTime endDate, List<RefGroupsEnt> group, List<string> GroupsCode)
{
using (base2Entities db = new base2Entities())
{
List<GroupsEnt> res = new List<GroupsEnt>();
List<string> codeGroups = new List<string>();
ContinGetAddzTr(stDate, endDate, GroupsCode, db, out res, out codeGroups);
List<GroupsEnt> realres = new List<GroupsEnt>();
decimal money;
string desc = string.Empty;
string descNum = string.Empty;
decimal e3 = 0;
decimal e5 = 0;
foreach(var code in codeGroups)
{
money = res.Where(_ => _.GroupCode != null && _.GroupCode.Contains(code)).Sum(_ => Math.Abs(_.Money));
if (res.Any(_ => _.GroupCode.Contains(code)))
{
descNum = res.FirstOrDefault(_ => _.GroupCode != null && _.GroupCode.Contains(code)).GroupCode;
desc = group.FirstOrDefault(_ => _.Name != null && _.Code == code).Name;
e3 = res.FirstOrDefault(_ => _.GroupCode != null && _.GroupCode.Contains(code)).en302;
e5 = res.FirstOrDefault(_ => _.GroupCode != null && _.GroupCode.Contains(code)).en450;
}
if (money != 0)
{
realres.Add(new GroupsEnt()
{
Money = money,
GroupCode = descNum,
GroupName = desc,
en302 = e3,
en450 = e5
});
}
}
return realres;
}
}
public static List<GroupsEnt> GetAllAddsTranz(DateTime stDate, DateTime endDate, List<RefGroupsEnt> group, List<string> GroupsCode)
{
using (base2Entities db = new base2Entities())
{
List<GroupsEnt> res = new List<GroupsEnt>();
List<string> codeGroups = new List<string>();
ContinGetAddzTr(stDate, endDate, GroupsCode, db, out res, out codeGroups);
return res;
}
}
private static List<SalesEnt> QueryForGetSales(DateTime stDate, DateTime endDate, base3Entities db, List<SalesEnt> gSeb, base2Entities db3)
{
var res = (from r77 in db3.C_Reference69
join r78 in db3.C_Reference69 on r77.C_ParentIDRRef equals r78.C_IDRRef
select new SalesEnt
{
Mont = 0,
Year = 0,
refId = r77.C_IDRRef,
ClientRefId = new byte[] { },
SalersRefId = new byte[] { },
CostPrise = decimal.Zero,
CountPur = decimal.Zero,
SalesWithoutNDS = decimal.Zero,
CountSal = decimal.Zero,
DivName = r77.C_Description,
GroupCode = r78.C_Code,
GroupName = r78.C_Description,
BuyerCode = string.Empty,
BuyerName = string.Empty,
CountGoodsEnd = decimal.Zero,
CountGoodsSt = decimal.Zero,
AveCostPrise = decimal.Zero
}
);
//Себестоимость за период + кол-во сколько поставили(в C_AccumRgTn10122) + сам поставщик (C_Value1_RRRef)
var res1 = (from s888 in db3.C_AccumRg6753
where s888.C_Period >= stDate && s888.C_Period < endDate
group s888 by s888.C_Fld6761_RRRef into g
select new SalesEnt
{
Mont = 0,
Year = 0,
refId = g.FirstOrDefault().C_Fld6761_RRRef,
ClientRefId = new byte[] { },
SalersRefId = g.FirstOrDefault().C_Fld6760RRef,
CostPrise = g.Sum(_ => _.C_Fld6765),
CountPur = decimal.Zero,
SalesWithoutNDS = decimal.Zero,
CountSal = decimal.Zero,
DivName = string.Empty,
GroupCode = string.Empty,
GroupName = string.Empty,
BuyerCode = string.Empty,
BuyerName = string.Empty,
CountGoodsEnd = decimal.Zero,
CountGoodsSt = decimal.Zero,
AveCostPrise = decimal.Zero
});
//Цена продажи без ндс
var res2 = (from r27 in db3.C_AccumRgTn6677
where r27.C_Period >= stDate && r27.C_Period < endDate
group r27 by r27.C_Fld6662_RRRef into g
select new SalesEnt
{
Mont = 0,
Year = 0,
refId = g.FirstOrDefault().C_Fld6662_RRRef,
ClientRefId = g.FirstOrDefault().C_Fld6670RRef,
SalersRefId = new byte[] { },
CostPrise = decimal.Zero,
CountPur = decimal.Zero,
SalesWithoutNDS = g.Sum(_ => _.C_Fld6673),
CountSal = g.Sum(_ => _.C_Fld6672),
DivName = string.Empty,
GroupCode = string.Empty,
GroupName = string.Empty,
BuyerCode = string.Empty,
BuyerName = string.Empty,
CountGoodsEnd = decimal.Zero,
CountGoodsSt = decimal.Zero,
AveCostPrise = decimal.Zero
}
);
// количество сколько поступило на конечную дату
var res3 = (from s888 in db3.C_AccumRgTn6903
where s888.C_Period < endDate && s888.C_Period >= stDate
group s888 by s888.C_Fld6883_RRRef into g
select new SalesEnt
{
Mont = 0,
Year = 0,
refId = new byte[] { },
ClientRefId = new byte[] { },
SalersRefId = g.FirstOrDefault().C_Fld6883_RRRef,
CostPrise = decimal.Zero,
CountPur = g.Sum(_ => _.C_Fld6893),
SalesWithoutNDS = decimal.Zero,
CountSal = decimal.Zero,
DivName = string.Empty,
GroupCode = string.Empty,
GroupName = string.Empty,
BuyerCode = string.Empty,
BuyerName = string.Empty,
CountGoodsEnd = decimal.Zero,
CountGoodsSt = decimal.Zero,
AveCostPrise = decimal.Zero
});
//покупатели
var res4 = (from s888 in db3.C_Reference62
select new SalesEnt
{
Mont = 0,
Year = 0,
refId = new byte[] { },
ClientRefId = s888.C_IDRRef,
SalersRefId = new byte[] { },
CostPrise = decimal.Zero,
CountPur = decimal.Zero,
SalesWithoutNDS = decimal.Zero,
CountSal = decimal.Zero,
DivName = string.Empty,
GroupCode = string.Empty,
GroupName = string.Empty,
BuyerCode = s888.C_Code,
BuyerName = s888.C_Description,
CountGoodsEnd = decimal.Zero,
CountGoodsSt = decimal.Zero,
AveCostPrise = decimal.Zero
});
var res5 =
(from r in res
join r1 in res1 on r.refId equals r1.refId
join r2 in res2 on r.refId equals r2.refId
join r3 in res3 on r.refId equals r3.SalersRefId
join r4 in res4 on r2.ClientRefId equals r4.ClientRefId
select new SalesEnt
{
Mont = 0,
Year = 0,
refId = r.refId,
ClientRefId = r2.ClientRefId,
SalersRefId = r1.SalersRefId,
CostPrise = r1.CostPrise,
CountPur = r3.CountPur,
SalesWithoutNDS = r2.SalesWithoutNDS,
CountSal = r2.CountSal,
DivName = r.DivName,
GroupCode = r.GroupCode,
GroupName = r.GroupName,
BuyerCode = r4.BuyerCode,
BuyerName = r4.BuyerName,
CountGoodsEnd = decimal.Zero,
CountGoodsSt = decimal.Zero,
AveCostPrise = decimal.Zero
});
var result = res5.ToList();
if (gSeb.Count() > 0)
foreach(var i in result)
{
if (gSeb.FirstOrDefault(f => f.GroupCode == i.GroupCode) != null)
{
i.AveCostPrise = gSeb.FirstOrDefault(f => f.GroupCode == i.GroupCode).CostPrise;
i.CountGoodsSt = gSeb.FirstOrDefault(f => f.GroupCode == i.GroupCode).CountGoodsSt;
i.CountGoodsEnd = gSeb.FirstOrDefault(f => f.GroupCode == i.GroupCode).CountGoodsEnd;
}
}
return result;
}
public static List<RefGroupsEnt> GetAllGroups()
{
using (base2Entities db = new base2Entities())
{
return (from cref in db.C_Reference91 // выгрузим все, чтобы 1000 раз не лезть в БД
select new RefGroupsEnt
{
Name = cref.C_Description,
Code = cref.C_Code
}).ToList();
}
}