public static int GetTimeSpan()
{
using (base3Entities db = new base3Entities())
{
return(db.C_YearOffset.First().Offset);
}
}
/// <summary>
/// официалки Взять все транзакции(Если endDate == null - на начало периода. Если endDate != null - на конец периода)
/// </summary>
/// <param name="stDate"></param>
/// <param name="endDate"></param>
/// <returns></returns>
public static List <TranzEnt> GetAllTransOriginal(DateTime stDate, DateTime?endDate)
{
List <string> tmp = new List <string>();
List <TranzEnt> res = new List <TranzEnt>();
using (base3Entities db = new base3Entities())
{
if (endDate == null)
{
res = (from acctr in db.C_AccRg420
join accCr in db.C_Acc17 on acctr.C_AccountCtRRef equals accCr.C_IDRRef
join accDt in db.C_Acc17 on acctr.C_AccountDtRRef equals accDt.C_IDRRef
where acctr.C_Period < stDate
select new TranzEnt
{
Money = acctr.C_Fld424,
period = acctr.C_Period,
ScoreCt = accCr.C_Code,
ScoreDt = accDt.C_Code
}).ToList();
}
if (endDate != null)
{
res = (
from accRg in db.C_AccRg420 // Выгрузим все транзакции на период который мы указали
join accDt in db.C_Acc17 on accRg.C_AccountDtRRef equals accDt.C_IDRRef
join accCt in db.C_Acc17 on accRg.C_AccountCtRRef equals accCt.C_IDRRef
where accRg.C_Period >= stDate &&
(tmp.Contains(accCt.C_Code) ||
tmp.Contains(accDt.C_Code))
select new TranzEnt
{
Money = accRg.C_Fld424,
period = accRg.C_Period,
ScoreDt = accDt.C_Code,
ScoreCt = accCt.C_Code
}).ToList();
}
return(res);
}
}
/// <summary>
/// официалки Взять все транзакции(Если endDate == null - на начало периода. Если endDate != null - на конец периода)
/// </summary>
/// <param name="stDate"></param>
/// <param name="endDate"></param>
/// <returns></returns>
public static List<TranzEnt> GetAllTransOriginal(DateTime stDate, DateTime? endDate)
{
List<string> tmp = new List<string>();
List<TranzEnt> res = new List<TranzEnt>();
using (base3Entities db = new base3Entities())
{
if (endDate == null)
{
res = (from acctr in db.C_AccRg420
join accCr in db.C_Acc17 on acctr.C_AccountCtRRef equals accCr.C_IDRRef
join accDt in db.C_Acc17 on acctr.C_AccountDtRRef equals accDt.C_IDRRef
where acctr.C_Period < stDate
select new TranzEnt
{
Money = acctr.C_Fld424,
period = acctr.C_Period,
ScoreCt = accCr.C_Code,
ScoreDt = accDt.C_Code
}).ToList();
}
if (endDate != null)
{
res = (
from accRg in db.C_AccRg420 // Выгрузим все транзакции на период который мы указали
join accDt in db.C_Acc17 on accRg.C_AccountDtRRef equals accDt.C_IDRRef
join accCt in db.C_Acc17 on accRg.C_AccountCtRRef equals accCt.C_IDRRef
where accRg.C_Period >= stDate &&
(tmp.Contains(accCt.C_Code) ||
tmp.Contains(accDt.C_Code))
select new TranzEnt
{
Money = accRg.C_Fld424,
period = accRg.C_Period,
ScoreDt = accDt.C_Code,
ScoreCt = accCt.C_Code
}).ToList();
}
return res;
}
}
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<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;
}
}
public static ReportProfitAndLoss GetReportProfitAndLoss(MainModel mainModel)
{
ReportProfitAndLoss model = new ReportProfitAndLoss();
using (base3Entities db = new base3Entities())
{
//var scores = mainModel.Scores;
List<int> ourScr = new List<int>();
List<TranzEnt> ourDbt = new List<TranzEnt>();
List<TranzEnt> ourCrt = new List<TranzEnt>();
int[] endMonthYear = new int[] { mainModel.EndDate.Month, mainModel.EndDate.Year };
int[] startMonthYear = new int[] { mainModel.StartDate.Month, mainModel.StartDate.Year };//будем бежать от начала до конца периода
#region Посчитаем кол-во месяцев
int monthCount = 0;
do
{
#region Cчитаем кол-во месяцев
if (startMonthYear[0] == 12)
{
startMonthYear[1]++;
startMonthYear[0] = 1;
monthCount++;
}
else
{
startMonthYear[0]++;
monthCount++;
}
#endregion
}
while ((startMonthYear[1] <= endMonthYear[1] && startMonthYear[1] != endMonthYear[1]) || (startMonthYear[0] <= endMonthYear[0] && startMonthYear[1] == endMonthYear[1]));
#endregion
List<TranzEnt> monthTranz = new List<TranzEnt>();//все транзакции за месяц (первый и последний могут быть обрезаны)
DateTime oneMonthDateStart = new DateTime();// стартовая дата для каждого месяца
DateTime oneMonthDateEnd = new DateTime();// конечная дата для каждого месяца
#region Инициализация
model.TotalIncome = new List<decimal>();
model.TotalCostPrice = new List<decimal>();
model.GrossProfit = new List<decimal>();
model.OtherIncome = new List<decimal>();
model.Costs = new List<decimal>();
model.CostsSalesServices = new List<decimal>();
model.AdministrativeExpenses = new List<decimal>();
model.FinancingCosts = new List<decimal>();
model.OtherCosts = new List<decimal>();
model.OperatingProfit = new List<decimal>();
model.Depreciation = new List<decimal>();
model.ProfitBeforeTaxation = new List<decimal>();
model.OtherTaxes = new List<decimal>();
model.KPN20 = new List<decimal>();
model.TotalProfit = new List<decimal>();
#endregion
int monthCounter = 0; // счетчик пройденых месяцев
for (int scoreNum = 0; scoreNum < (int)ProfitAndLossNumServer.Total; scoreNum++)
{
endMonthYear = new int[] { mainModel.EndDate.Month, mainModel.EndDate.Year };
startMonthYear = new int[] { mainModel.StartDate.Month, mainModel.StartDate.Year };
switch (scoreNum)
{
#region Вытаскиваем наши счета
case (int)ProfitAndLossNumServer.Income:
ourScr = new List<int> { (int)ScoresReportProfitAndLoss.Income0,
(int)ScoresReportProfitAndLoss.Income1,
(int)ScoresReportProfitAndLoss.Income2
};
break;
case (int)ProfitAndLossNumServer.CostPrice:
ourScr = new List<int> { (int)ScoresReportProfitAndLoss.CostPrice };
break;
case (int)ProfitAndLossNumServer.OtherIncome:
ourScr = new List<int> { (int)ScoresReportProfitAndLoss.OtherIncome0,
(int)ScoresReportProfitAndLoss.OtherIncome1,
(int)ScoresReportProfitAndLoss.OtherIncome2,
(int)ScoresReportProfitAndLoss.OtherIncome3,
};
break;
case (int)ProfitAndLossNumServer.CostsSalesServices:
ourScr = new List<int> { (int)ScoresReportProfitAndLoss.CostsSalesServices };
break;
case (int)ProfitAndLossNumServer.AdministrativeExpenses:
ourScr = new List<int> { (int)ScoresReportProfitAndLoss.AdministrativeExpenses };
break;
case (int)ProfitAndLossNumServer.FinancingCosts:
ourScr = new List<int> { (int)ScoresReportProfitAndLoss.FinancingCosts };
break;
case (int)ProfitAndLossNumServer.OtherCosts:
ourScr = new List<int> { (int)ScoresReportProfitAndLoss.OtherCosts0,
(int)ScoresReportProfitAndLoss.OtherCosts1,
(int)ScoresReportProfitAndLoss.OtherCosts2,
(int)ScoresReportProfitAndLoss.OtherCosts3,
(int)ScoresReportProfitAndLoss.OtherCosts4,
(int)ScoresReportProfitAndLoss.OtherCosts5,
(int)ScoresReportProfitAndLoss.OtherCosts6,
};
break;
case (int)ProfitAndLossNumServer.Depreciation:
ourScr = new List<int> { (int)ScoresReportProfitAndLoss.Depreciation };
break;
case (int)ProfitAndLossNumServer.OtherTaxes:
ourScr = new List<int> { (int)ScoresReportProfitAndLoss.OtherTaxes };
break;
case (int)ProfitAndLossNumServer.KPN20:
ourScr = new List<int> { (int)ScoresReportProfitAndLoss.KPN20 };
break;
#endregion
}
bool isFirstMonth = true;
monthCounter = 0; // счетчик пройденых месяцев
do
{
#region Определим дату начала и конца месяца
monthCounter++;
if (isFirstMonth)
{
oneMonthDateStart = new DateTime(startMonthYear[1], startMonthYear[0], mainModel.StartDate.Day);
isFirstMonth = false;
}
else
{
oneMonthDateStart = new DateTime(startMonthYear[1], startMonthYear[0], 1);
}
#region Cчитаем кол-во месяцев
if (startMonthYear[0] == 12)
{
startMonthYear[1]++;
startMonthYear[0] = 1;
}
else
{
startMonthYear[0]++;
}
#endregion
if (monthCounter > monthCount) // если последний месяц возьмем с него дату
{
oneMonthDateEnd = new DateTime(endMonthYear[1], endMonthYear[0], mainModel.EndDate.Day);
}
else // иначе мы должны довести старт до начала следующего месяца
{
oneMonthDateEnd = new DateTime(startMonthYear[1], startMonthYear[0], 1);
}
#endregion
List<TranzEnt> monthTrans = new List<TranzEnt>();
if (monthCounter > monthCount)
{
monthTrans = mainModel.EndTranz.Where(_ => _.period >= oneMonthDateStart && _.period <= oneMonthDateEnd).ToList();
}
else
{
monthTrans = mainModel.EndTranz.Where(_ => _.period >= oneMonthDateStart && _.period < oneMonthDateEnd).ToList();// тут строго т.к. мы в середине общего периода берем конец как начало следующуго
}
GetPeriodMoney(monthTrans, ourScr, out ourDbt, out ourCrt);
switch (scoreNum)
{
#region Запишем суммы в модель
case (int)ProfitAndLossNumServer.Income:
model.TotalIncome.Add(ourCrt.Select(_ => _.Money).Sum() * 112 / 100); // + 12 процентов
break;
case (int)ProfitAndLossNumServer.CostPrice:
model.TotalCostPrice.Add(ourCrt.Select(_ => _.Money).Sum());
break;
case (int)ProfitAndLossNumServer.OtherIncome:
model.OtherIncome.Add(ourDbt.Select(_ => _.Money).Sum());
break;
case (int)ProfitAndLossNumServer.CostsSalesServices:
model.CostsSalesServices.Add(ourDbt.Select(_ => _.Money).Sum());
break;
case (int)ProfitAndLossNumServer.AdministrativeExpenses:
model.AdministrativeExpenses.Add(ourDbt.Select(_ => _.Money).Sum());
break;
case (int)ProfitAndLossNumServer.FinancingCosts:
model.FinancingCosts.Add(ourDbt.Select(_ => _.Money).Sum());
break;
case (int)ProfitAndLossNumServer.OtherCosts:
model.OtherCosts.Add(ourDbt.Select(_ => _.Money).Sum());
break;
case (int)ProfitAndLossNumServer.Depreciation:
model.Depreciation.Add(ourDbt.Select(_ => _.Money).Sum());
break;
case (int)ProfitAndLossNumServer.OtherTaxes:
model.OtherTaxes.Add(ourDbt.Select(_ => _.Money).Sum());
break;
case (int)ProfitAndLossNumServer.KPN20:
model.KPN20.Add(ourDbt.Select(_ => _.Money).Sum() * 20 / 100); // берем только 20 процентов
break;
#endregion
}
}
while ((startMonthYear[1] <= endMonthYear[1] && startMonthYear[1] != endMonthYear[1]) || (startMonthYear[0] < endMonthYear[0] && startMonthYear[1] == endMonthYear[1]));
}
// Заполним расчитываемые поля
for (int i = 0; i < monthCounter; i++)
{
model.GrossProfit.Add(model.TotalIncome[i] + model.TotalCostPrice[i]);
model.Costs.Add(model.AdministrativeExpenses[i] + model.CostsSalesServices[i] + model.FinancingCosts[i] + model.OtherTaxes[i]);
model.OperatingProfit.Add(model.GrossProfit[i] + model.OtherIncome[i] - model.Costs[i]);
model.ProfitBeforeTaxation.Add(model.OperatingProfit[i] - model.Depreciation[i]);
model.TotalProfit.Add(model.ProfitBeforeTaxation[i] - model.OtherTaxes[i] - model.KPN20[i]);
}
#region Расчитаем среднюю и общую сумму по каждой строке
model.TotalIncome.Add(model.TotalIncome.Take(monthCounter).Sum());// общее
model.TotalIncome.Add(model.TotalIncome.Take(monthCounter).Sum() / monthCounter);// среднее
model.TotalCostPrice.Add(model.TotalCostPrice.Take(monthCounter).Sum());// общее
model.TotalCostPrice.Add(model.TotalCostPrice.Take(monthCounter).Sum() / monthCounter);// среднее
model.GrossProfit.Add(model.GrossProfit.Take(monthCounter).Sum());// общее
model.GrossProfit.Add(model.GrossProfit.Take(monthCounter).Sum() / monthCounter);// среднее
model.OtherIncome.Add(model.OtherIncome.Take(monthCounter).Sum());// общее
model.OtherIncome.Add(model.OtherIncome.Take(monthCounter).Sum() / monthCounter);// среднее
model.CostsSalesServices.Add(model.CostsSalesServices.Take(monthCounter).Sum());// общее
model.CostsSalesServices.Add(model.CostsSalesServices.Take(monthCounter).Sum() / monthCounter);// среднее
model.Costs.Add(model.Costs.Take(monthCounter).Sum());// общее
model.Costs.Add(model.Costs.Take(monthCounter).Sum() / monthCounter);// среднее
model.Depreciation.Add(model.Depreciation.Take(monthCounter).Sum());// общее
model.Depreciation.Add(model.Depreciation.Take(monthCounter).Sum() / monthCounter);// среднее
model.KPN20.Add(model.KPN20.Take(monthCounter).Sum());// общее
model.KPN20.Add(model.KPN20.Take(monthCounter).Sum() / monthCounter);// среднее
model.ProfitBeforeTaxation.Add(model.ProfitBeforeTaxation.Take(monthCounter).Sum());// общее
model.ProfitBeforeTaxation.Add(model.ProfitBeforeTaxation.Take(monthCounter).Sum() / monthCounter);// среднее
model.AdministrativeExpenses.Add(model.AdministrativeExpenses.Take(monthCounter).Sum());// общее
model.AdministrativeExpenses.Add(model.AdministrativeExpenses.Take(monthCounter).Sum() / monthCounter);// среднее
model.FinancingCosts.Add(model.FinancingCosts.Take(monthCounter).Sum());// общее
model.FinancingCosts.Add(model.FinancingCosts.Take(monthCounter).Sum() / monthCounter);// среднее
model.OtherCosts.Add(model.OtherCosts.Take(monthCounter).Sum());// общее
model.OtherCosts.Add(model.OtherCosts.Take(monthCounter).Sum() / monthCounter);// среднее
model.OtherTaxes.Add(model.OtherTaxes.Take(monthCounter).Sum());// общее
model.OtherTaxes.Add(model.OtherTaxes.Take(monthCounter).Sum() / monthCounter);// среднее
model.OperatingProfit.Add(model.OperatingProfit.Take(monthCounter).Sum());// общее
model.OperatingProfit.Add(model.OperatingProfit.Take(monthCounter).Sum() / monthCounter);// среднее
model.TotalProfit.Add(model.TotalProfit.Take(monthCounter).Sum());// общее
model.TotalProfit.Add(model.TotalProfit.Take(monthCounter).Sum() / monthCounter);// среднее
#endregion
model.Calculate();
return model;
}
}
public static BusinessResults GetBusinessResults(MainModel mainModel, bool isForMonth = false)
{
BusinessResults model = new BusinessResults();
model.StartDate = mainModel.StartDate;
model.EndDate = mainModel.EndDate;
using (base3Entities db = new base3Entities())
{
Start = mainModel.StartTranz;
End = mainModel.EndTranz;
StartOriginal = mainModel.StartTranzOriginal;
EndOriginal = mainModel.EndTranzOriginal;
if (isForMonth)
{
Start.AddRange(mainModel.EndTranz.Where(_ => _.period < mainModel.StartDate).ToList());
End = mainModel.EndTranz.Where(_ => _.period >= mainModel.StartDate && _.period < mainModel.EndDate).ToList();
//StartOriginal.AddRange(mainModel.EndTranzOriginal.Where(_ => _.period < mainModel.StartDate).ToList());
//EndOriginal = mainModel.EndTranzOriginal.Where(_ => _.period >= mainModel.StartDate && _.period < mainModel.EndDate).ToList();
}
ourScr = new List<int>(); // Вытащим ID интересующих нас счетов нас счетов
decimal _outStart = 0;
decimal _outEnd = 0;
#region Оборотные активы
#region Денежные средства в кассе
Calculate(out _outStart, out _outEnd,
(int)ScoresForBusinessResults.CashInCashBox1,
(int)ScoresForBusinessResults.CashInCashBox2);
model.CashInCashBoxStart = _outStart;
model.CashInCashBoxEnd = _outEnd;
#endregion
#region Денежные средства на рассчетном счете
Calculate(out _outStart, out _outEnd,
(int)ScoresForBusinessResults.MoneyInTheBankAccounts1,
(int)ScoresForBusinessResults.MoneyInTheBankAccounts2);
model.MoneyInTheBankAccountsStart = _outStart;
model.MoneyInTheBankAccountsEnd = _outEnd;
#endregion
#region Депозиты
Calculate(out _outStart, out _outEnd,
(int)ScoresForBusinessResults.Deposits);
model.DepositsStart = _outStart;
model.DepositsEnd = _outEnd;
#endregion
#region Долги клиентов и переплаты
Calculate(out _outStart, out _outEnd,
(int)ScoresForBusinessResults.DebtsOfCustomersAndOverpayments);
model.DebtsOfCustomersAndOverpaymentsStart = _outStart;
model.DebtsOfCustomersAndOverpaymentsEnd = _outEnd;
#endregion
#region Сырье и материалы
Calculate(out _outStart, out _outEnd,
(int)ScoresForBusinessResults.RawAndMaterials);
model.RawAndMaterialsStart = _outStart;
model.RawAndMaterialsEnd = _outEnd;
#endregion
#region Товары
Calculate(out _outStart, out _outEnd,
(int)ScoresForBusinessResults.Goods1,
(int)ScoresForBusinessResults.Goods2);
model.GoodsStart = _outStart;
model.GoodsEnd = _outEnd;
#endregion
#region Незавершенное производство
Calculate(out _outStart, out _outEnd,
(int)ScoresForBusinessResults.UnfinishedProduction);
model.UnfinishedProductionStart = _outStart;
model.UnfinishedProductionEnd = _outEnd;
#endregion
#region Прочие оборотные активы
Calculate(out _outStart, out _outEnd,
(int)ScoresForBusinessResults.OtherCurrentAssets1,
(int)ScoresForBusinessResults.OtherCurrentAssets2,
(int)ScoresForBusinessResults.OtherCurrentAssets3,
(int)ScoresForBusinessResults.OtherCurrentAssets4,
(int)ScoresForBusinessResults.OtherCurrentAssets5,
(int)ScoresForBusinessResults.OtherCurrentAssets6,
(int)ScoresForBusinessResults.OtherCurrentAssets7,
(int)ScoresForBusinessResults.OtherCurrentAssets8,
(int)ScoresForBusinessResults.OtherCurrentAssets9,
(int)ScoresForBusinessResults.OtherCurrentAssets10,
(int)ScoresForBusinessResults.OtherCurrentAssets11,
(int)ScoresForBusinessResults.OtherCurrentAssets12);
model.OtherCurrentAssetsStart = _outStart;
model.OtherCurrentAssetsEnd = _outEnd;
#endregion
#region Налоговые переплаты / авансы
Calculate(out _outStart, out _outEnd,
(int)ScoresForBusinessResults.TaxOverpaymentsAndAdvances);
model.TaxOverpaymentsAndAdvancesStart = _outStart;
model.TaxOverpaymentsAndAdvancesEnd = _outEnd;
#endregion
#endregion
#region Долгосрочные активы
#region Долгосрочная дебиторская задолженность контрагентов
Calculate(out _outStart, out _outEnd,
(int)ScoresForBusinessResults.CustomerDebts);
model.CustomerDebtsStart = _outStart;
model.CustomerDebtsEnd = _outEnd;
#endregion
#region Прочие долги клиентов/переплаты
Calculate(out _outStart, out _outEnd,
(int)ScoresForBusinessResults.OtherDebtsOfClientsAndOverpayment1,
(int)ScoresForBusinessResults.OtherDebtsOfClientsAndOverpayment2,
(int)ScoresForBusinessResults.OtherDebtsOfClientsAndOverpayment3,
(int)ScoresForBusinessResults.OtherDebtsOfClientsAndOverpayment4,
(int)ScoresForBusinessResults.OtherDebtsOfClientsAndOverpayment5,
(int)ScoresForBusinessResults.OtherDebtsOfClientsAndOverpayment6,
(int)ScoresForBusinessResults.OtherDebtsOfClientsAndOverpayment7,
(int)ScoresForBusinessResults.OtherDebtsOfClientsAndOverpayment8,
(int)ScoresForBusinessResults.OtherDebtsOfClientsAndOverpayment9,
(int)ScoresForBusinessResults.OtherDebtsOfClientsAndOverpayment10,
(int)ScoresForBusinessResults.OtherDebtsOfClientsAndOverpayment11,
(int)ScoresForBusinessResults.OtherDebtsOfClientsAndOverpayment12);
model.OtherDebtsOfClientsAndOverpaymentStart = _outStart;
model.OtherDebtsOfClientsAndOverpaymentEnd = _outEnd;
#endregion
#region Инвестиции
Calculate(out _outStart, out _outEnd,
(int)ScoresForBusinessResults.Investments1,
(int)ScoresForBusinessResults.Investments2,
(int)ScoresForBusinessResults.Investments3);
model.InvestmentsStart = _outStart;
model.InvestmentsEnd = _outEnd;
#endregion
#region Основные средства
Calculate(out _outStart, out _outEnd,
(int)ScoresForBusinessResults.FixedAssets);
model.FixedAssetsStart = _outStart;
model.FixedAssetsEnd = _outEnd;
#endregion
#region Нематериальные активы
Calculate(out _outStart, out _outEnd,
(int)ScoresForBusinessResults.IntangibleAssets);
model.IntangibleAssetsStart = _outStart;
model.IntangibleAssetsEnd = _outEnd;
#endregion
#region Отложенные налоговые переплаты/авансы
Calculate(out _outStart, out _outEnd,
(int)ScoresForBusinessResults.TheDeferredTaxOverpaymentsAndAdvances);
model.TheDeferredTaxOverpaymentsAndAdvancesStart = _outStart;
model.TheDeferredTaxOverpaymentsAndAdvancesEnd = _outEnd;
#endregion
#endregion
#region Текущая задолженность
#region Кредиты сроком до одного года
Calculate(out _outStart, out _outEnd,
(int)ScoresForBusinessResults.CreditsForOneYear1,
(int)ScoresForBusinessResults.CreditsForOneYear2);
model.CreditsForOneYearStart = _outStart * minusOne;
model.CreditsForOneYearEnd = _outEnd * minusOne;
#endregion
#region Задолженность по КПН/ИПН
Calculate(out _outStart, out _outEnd,
(int)ScoresForBusinessResults.DebtCitIit);
model.DebtCitIitStart = _outStart * minusOne;
model.DebtCitIitEnd = _outEnd * minusOne;
#endregion
#region Задолженность по НДС
Calculate(out _outStart, out _outEnd,
(int)ScoresForBusinessResults.DebtVat);
model.DebtVatStart = _outStart * minusOne;
model.DebtVatEnd = _outEnd * minusOne;
#endregion
#region Прочая задолженность по налогам
Calculate(out _outStart, out _outEnd,
(int)ScoresForBusinessResults.OtherTaxesPayable1,
(int)ScoresForBusinessResults.OtherTaxesPayable2,
(int)ScoresForBusinessResults.OtherTaxesPayable3,
(int)ScoresForBusinessResults.OtherTaxesPayable4,
(int)ScoresForBusinessResults.OtherTaxesPayable5,
(int)ScoresForBusinessResults.OtherTaxesPayable6,
(int)ScoresForBusinessResults.OtherTaxesPayable7);
model.OtherTaxesPayableStart = _outStart * minusOne;
model.OtherTaxesPayableEnd = _outEnd * minusOne;
#endregion
#region Задолжность перед поставщиками
Calculate(out _outStart, out _outEnd,
(int)ScoresForBusinessResults.PayablesToSuppliersShortTermDebts);
model.PayablesToSuppliersShortTermDebtsStart = _outStart * minusOne;
model.PayablesToSuppliersShortTermDebtsEnd = _outEnd * minusOne;
#endregion
#region Задолженность перед сотрудниками
Calculate(out _outStart, out _outEnd,
(int)ScoresForBusinessResults.PayablesToEmployees);
model.PayablesToEmployeesStart = _outStart * minusOne;
model.PayablesToEmployeesEnd = _outEnd * minusOne;
#endregion
#region Прочая задолженность
Calculate(out _outStart, out _outEnd,
(int)ScoresForBusinessResults.OtherDebtsShortTermDebts1,
(int)ScoresForBusinessResults.OtherDebtsShortTermDebts2,
(int)ScoresForBusinessResults.OtherDebtsShortTermDebts3,
(int)ScoresForBusinessResults.OtherDebtsShortTermDebts4,
(int)ScoresForBusinessResults.OtherDebtsShortTermDebts5,
(int)ScoresForBusinessResults.OtherDebtsShortTermDebts6,
(int)ScoresForBusinessResults.OtherDebtsShortTermDebts7,
(int)ScoresForBusinessResults.OtherDebtsShortTermDebts8,
(int)ScoresForBusinessResults.OtherDebtsShortTermDebts9,
(int)ScoresForBusinessResults.OtherDebtsShortTermDebts10);
model.OtherDebtsShortTermDebtsStart = _outStart * minusOne;
model.OtherDebtsShortTermDebtsEnd = _outEnd * minusOne;
#endregion
#endregion
#region Долгосрочные долги
#region Долгосрочные банковские займы
Calculate(out _outStart, out _outEnd,
(int)ScoresForBusinessResults.CreditsForLongerThanOneYear1,
(int)ScoresForBusinessResults.CreditsForLongerThanOneYear2);
model.CreditsForLongerThanOneYearStart = _outStart * minusOne;
model.CreditsForLongerThanOneYearEnd = _outEnd * minusOne;
#endregion
#region Задолженность перед контрагентами
Calculate(out _outStart, out _outEnd,
(int)ScoresForBusinessResults.PayablesToSuppliersLongTermDebts);
model.PayablesToSuppliersLongTermDebtsStart = _outStart * minusOne;
model.PayablesToSuppliersLongTermDebtsEnd = _outEnd * minusOne;
#endregion
#region Отложеннные налоговая задолженность
Calculate(out _outStart, out _outEnd,
(int)ScoresForBusinessResults.DefferedTaxDebts);
model.DefferedTaxDebtsStart = _outStart * minusOne;
model.DefferedTaxDebtsEnd = _outEnd * minusOne;
#endregion
#region Прочая задолженность
Calculate(out _outStart, out _outEnd,
(int)ScoresForBusinessResults.OtherDebtsLongTermDebts1,
(int)ScoresForBusinessResults.OtherDebtsLongTermDebts2,
(int)ScoresForBusinessResults.OtherDebtsLongTermDebts3,
(int)ScoresForBusinessResults.OtherDebtsLongTermDebts4);
model.OtherDebtsLongTermDebtsStart = _outStart * minusOne;
model.OtherDebtsLongTermDebtsEnd = _outEnd * minusOne;
#endregion
#endregion
#region Собственный капитал
#region Уставной капитал
Calculate(out _outStart, out _outEnd,
(int)ScoresForBusinessResults.AuthorizedCapital1,
(int)ScoresForBusinessResults.AuthorizedCapital2);
model.AuthorizedCapitalStart = _outStart * minusOne;
model.AuthorizedCapitalEnd = _outEnd * minusOne;
#endregion
#region Прочий капитал
Calculate(out _outStart, out _outEnd,
(int)ScoresForBusinessResults.OtherCapital);
model.OtherCapitalStart = _outStart * minusOne;
model.OtherCapitalEnd = _outEnd * minusOne;
#endregion
#endregion
#region Итого
// Сначала считаем пассивые, потом активы
#region Текущая задолженность
model.CalculateCurrentDebt();
#endregion
#region Долгосрочная задолженность
model.CalculateLongTermDebt();
#endregion
#region Собственный капитал
model.CalculateOwnCapital();
#endregion
#region Оборотные активы
model.CalculateCirculatingAssets();
#endregion
#region Долгосрочные активы
model.CalculateLongTermAssets();
#endregion
#region Итого активов
model.CalculateTotalAssets();
#endregion
#region Итого пассивов
model.CalculateTotalLiabilities();
#endregion
#region Накопленная прибыль/убыток
model.CalculateAccumulatedProfitAndLoss();
//Calculate(out _outStart, out _outEnd,
// (int)ScoresForBusinessResults.AccumulatedProfitAndLoss);
//model.AccumulatedProfitAndLossStart = _outStart;
//model.AccumulatedProfitAndLossEnd = _outEnd;
#endregion
#endregion
//model.StartDate = mainModel.StartDate.AddYears(mainModel.TimeSpan * -1);
//model.EndDate = mainModel.EndDate.AddYears(mainModel.TimeSpan * -1);
return model;
}
}
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 int GetTimeSpan()
{
using (base3Entities db = new base3Entities())
{
return db.C_YearOffset.First().Offset;
}
}
