private void MonthUseListFill()
{
using (db = new MyDBContext())
{
db.ViewResults.Load();
var qrySource = from o in db.ViewResults.ToList()
where o.Year * 100 + o.Month >= StartYear * 100 + StartMonth
where o.Year * 100 + o.Month <= EndYear * 100 + EndMonth
where o.IdEnergyResource == SelectedER
where SelectedCostCenter == 0 ? true : o.IdCostCenter == SelectedCostCenter
group o by new { o.IdEnergyResource, o.Period } into gr
orderby gr.Max(m => m.Year), gr.Max(m => m.Month)
select new ViewResult
{
IdEnergyResource = gr.Key.IdEnergyResource,
Period = gr.Key.Period,
ResourceName = gr.Max(m => m.ResourceName),
Year = gr.Max(m => m.Year),
Month = gr.Max(m => m.Month),
UnitName = gr.Max(m => m.UnitName),
Plan = gr.Sum(m => m.Plan),
Fact = gr.Sum(m => m.Fact),
Difference = gr.Sum(m => m.Difference),
PlanCost = gr.Sum(m => m.PlanCost),
FactCost = gr.Sum(m => m.FactCost),
DifferenceCost = gr.Sum(m => m.DifferenceCost),
IsMain = gr.Max(m => m.IsMain)
};
MonthUseList.Clear();
MonthUseList = Global.ObservableCollection <ViewResult>(qrySource);
MonthUseTotalList.Clear();
MonthUseTotalList.Add(new ViewResult()
{
Period = "ИТОГО:",
Plan = MonthUseList.Sum(n => n.Plan),
Fact = MonthUseList.Sum(n => n.Fact),
Difference = MonthUseList.Sum(n => n.Difference),
PlanCost = MonthUseList.Sum(n => n.PlanCost),
FactCost = MonthUseList.Sum(n => n.FactCost),
DifferenceCost = MonthUseList.Sum(n => n.DifferenceCost)
});
}
}
private void MonthUseListFill()
{
using (db = new MyDBContext())
{
db.ViewResults.Load();
var qrySource = from o in db.ViewFabricateUses.ToList()
where o.Year * 100 + o.Month >= StartYear * 100 + StartMonth
where o.Year * 100 + o.Month <= EndYear * 100 + EndMonth
where o.IdProduct == SelectedER
group o by new { o.IdProduct, o.Period } into gr
orderby gr.Max(m => m.Year), gr.Max(m => m.Month)
select new ViewFabricateUse
{
IdProduct = gr.Key.IdProduct,
Period = gr.Key.Period,
ERName = gr.Max(m => m.ERName),
Year = gr.Max(m => m.Year),
Month = gr.Max(m => m.Month),
UnitName = gr.Max(m => m.UnitName),
Fabricate = gr.Sum(m => m.Fabricate),
Fact1 = gr.Sum(m => m.Fact1),
Fact0 = gr.Sum(m => m.Fact0),
Loss = gr.Sum(m => m.Loss)
};
MonthUseList.Clear();
MonthUseList = Global.ObservableCollection <ViewFabricateUse>(qrySource);
FabricateUseTotalList.Clear();
FabricateUseTotalList.Add(new ViewFabricateUse()
{
Period = "ИТОГО:",
Fabricate = MonthUseList.Sum(n => n.Fabricate),
Fact1 = MonthUseList.Sum(n => n.Fact1),
Fact0 = MonthUseList.Sum(n => n.Fact0),
Loss = MonthUseList.Sum(n => n.Loss)
});
}
}
private void MonthUseListFill()
{
using (db = new MyDBContext())
{
db.ViewResults.Load();
db.EnergyResources.Load();
db.ViewLossesFacts.Load();
var qrySource = from o in db.ViewResults.Local.ToList()
where o.Year * 100 + o.Month >= StartYear * 100 + StartMonth
where o.Year * 100 + o.Month <= EndYear * 100 + EndMonth
join a in db.EnergyResources.ToList() on o.IdEnergyResource equals a.Id
where a.IsPrime == 1
group o by new { o.IdEnergyResource } into gr
//orderby gr.Sum(m => m.FactCost) descending
select new
{
IdEnergyResource = gr.Key.IdEnergyResource,
ResourceName = gr.Max(m => m.ResourceName),
Year = gr.Max(m => m.Year),
Month = gr.Max(m => m.Month),
UnitName = gr.Max(m => m.UnitName),
Plan = gr.Sum(m => m.Plan),
Fact = gr.Sum(m => m.Fact),
Difference = gr.Sum(m => m.Difference),
PlanCost = gr.Sum(m => m.PlanCost),
FactCost = gr.Sum(m => m.FactCost),
DifferenceCost = gr.Sum(m => m.DifferenceCost),
};
var qry1 = from o in db.ViewLossesFacts
where o.Year * 100 + o.Month >= StartYear * 100 + StartMonth
where o.Year * 100 + o.Month <= EndYear * 100 + EndMonth
group o by new { o.IdEnergyResource } into gr
select new
{
IdEnergyResource = gr.Key.IdEnergyResource,
Year = gr.Max(m => m.Year),
Month = gr.Max(m => m.Month),
Loss = gr.Sum(m => m.FactLoss),
Total = gr.Sum(m => m.FactTotal),
};
var qry = from o in qrySource.ToList()
join b in qry1.ToList() on new { o.IdEnergyResource, o.Year, o.Month } equals new { b.IdEnergyResource, b.Year, b.Month }
select new PrimeERFull
{
IdEnergyResource = o.IdEnergyResource,
ResourceName = o.ResourceName,
Year = o.Year,
Month = o.Month,
UnitName = o.UnitName,
Plan = o.Plan,
Fact = o.Fact,
Difference = o.Difference,
PlanCost = o.PlanCost,
FactCost = o.FactCost,
DifferenceCost = o.DifferenceCost,
Loss = b.Loss,
Total = b.Total,
LossCost = b.Loss * o.FactCost / o.Fact,
TotalCost = b.Total * o.FactCost / o.Fact
};
MonthUseList.Clear();
MonthUseList = Global.ObservableCollection <PrimeERFull>(qry);
MonthUseTotalList.Clear();
MonthUseTotalList.Add(new PrimeERFull()
{
Period = "ИТОГО:",
PlanCost = MonthUseList.Sum(n => n.PlanCost),
FactCost = MonthUseList.Sum(n => n.FactCost),
DifferenceCost = MonthUseList.Sum(n => n.DifferenceCost),
TotalCost = MonthUseList.Sum(n => n.TotalCost),
LossCost = MonthUseList.Sum(n => n.LossCost)
});
}
}
private void OnExport(int numToEdit = 0)
{
int len1 = MonthUseList.Count();
int len2 = 9;
int len3;
int len4;
object[,] arrRes = new object[len1, len2];
object[,] arrChart = new object[len1, 2];
object[] arrCaption = new object[] { "Код", "Ресурс", "ЕдИзм", "План", "Факт", "Откл", "ПланРуб", "ФактРуб", "ОтклРуб" };
object[] arrCaption1 = new object[] { "Ресурс", "ФактРуб" };
DBListToArray();
ArrToExcel();
void DBListToArray()
{
int i = 0;
foreach (var r in MonthUseList)
{
arrRes[i, 0] = r.IdEnergyResource;
arrRes[i, 1] = r.ResourceName;
arrRes[i, 2] = r.UnitName;
arrRes[i, 3] = r.Plan;
arrRes[i, 4] = r.Fact;
arrRes[i, 5] = r.Difference;
arrRes[i, 6] = r.PlanCost;
arrRes[i, 7] = r.FactCost;
arrRes[i, 8] = r.DifferenceCost;
i++;
}
var qry2 = from o in MonthUseList
select new
{
o.FactCost,
o.DifferenceCost,
o.ResourceName
};
double Total = qry2.Sum(n => n.FactCost);
//var qry3 = from o in MonthUseList
// where o.FactCost >= Total * Proc / 100
// orderby o.FactCost descending
// select new
// {
// o.FactCost,
// o.ResourceName
// };
//var qry4 = from o in MonthUseList
// where o.FactCost < Total * Proc / 100
// select new
// {
// o.FactCost,
// o.ResourceName
// };
//double Total1 = qry4.Sum(n => n.FactCost);
//int j = 0;
//ChartList.Clear();
//foreach (var newY in qry3.ToList())
//{
// ViewResult r = new ViewResult();
// r.ResourceName = newY.ResourceName;
// r.FactCost = newY.FactCost;
// ChartList.Add(r);
// j++;
//}
//ViewResult s = new ViewResult();
//s.ResourceName = "Прочие";
//s.FactCost = Total1;
//ChartList.Add(s);
//len3 = ChartList.Count();
//len4 = 2;
//i = 0;
//foreach (var l in ChartList)
//{
// arrChart[i, 0] = l.ResourceName;
// arrChart[i, 1] = l.FactCost;
// i++;
//}
}
void ArrToExcel()
{
Excel.Application ex = new Excel.Application();
//ex.SheetsInNewWorkbook = 1;
//Excel.Workbook workBook = ex.Workbooks.Add(Type.Missing);
ex.Workbooks.Open(AppDomain.CurrentDomain.BaseDirectory + @"tmpcostcenters.xlsx",
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
ex.DisplayAlerts = true;
Excel.Worksheet sheet = (Excel.Worksheet)ex.Worksheets.get_Item(1);
sheet.Name = "ЦентрыЗатрат";
Excel.Range c1 = (Excel.Range)sheet.Cells[1, 1];
Excel.Range c2 = (Excel.Range)sheet.Cells[1, 1];
Excel.Range rangeCapt = sheet.get_Range(c1, c2);
rangeCapt.Value = ChartCaption;
c1 = (Excel.Range)sheet.Cells[3, 1];
c2 = (Excel.Range)sheet.Cells[3, 9];
Excel.Range rangeCaption = sheet.get_Range(c1, c2);
rangeCaption.Value = arrCaption;
c1 = (Excel.Range)sheet.Cells[4, 1];
c2 = (Excel.Range)sheet.Cells[4 + len1 - 1, 9];
Excel.Range range = sheet.get_Range(c1, c2);
range.Value = arrRes;
sheet = (Excel.Worksheet)ex.Worksheets.get_Item(2);
c1 = (Excel.Range)sheet.Cells[1, 1];
c2 = (Excel.Range)sheet.Cells[1, 1];
//rangeCapt = sheet.get_Range(c1, c2);
//rangeCapt.Value = "Структура фактического потребления, руб.";
//c1 = (Excel.Range)sheet.Cells[3, 1];
//c2 = (Excel.Range)sheet.Cells[3, 2];
//rangeCaption = sheet.get_Range(c1, c2);
//rangeCaption.Value = arrCaption1;
//c1 = (Excel.Range)sheet.Cells[4, 1];
//c2 = (Excel.Range)sheet.Cells[4 + len3 - 1, 2];
//range = sheet.get_Range(c1, c2);
//range.Value = arrChart;
ex.Visible = true;
ex.DisplayAlerts = true;
//Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();
//dlg.FileName = "CostCenters01"; // Default file name
//dlg.DefaultExt = ".xlsx"; // Default file extension
//dlg.Filter = "Книга Excel (.xlsx)|*.xlsx"; // Filter files by extension
//dlg.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
//Nullable<bool> result = dlg.ShowDialog();
//if (result == true)
//{
// string filename = dlg.FileName;
// ex.Application.ActiveWorkbook.SaveAs(filename + @"", Type.Missing,
// Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
// Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// ex.WindowState = Excel.XlWindowState.xlMaximized;
// MessageBoxResult result1 = System.Windows.MessageBox.Show("Отчёт успешно сформирован",
// "Поздравляю",
// MessageBoxButton.OK,
// MessageBoxImage.Information);
// //ex.Visible = true;
//}
//ex.Quit();
}
}
public void DiagrammInit()
{
int dlina = MonthUseList.Count();
string[] SeriesTitle = new string[] { "План", "Факт", "Откл", "ПланРуб", "ФактРуб", "ОтклРуб" };
int i = !RBCostIsChecked ? 0 : 3;
SeriesCollection.Clear();
LineSeries ls1 = new LineSeries
{
Title = SeriesTitle[i],
LineSmoothness = 0,
StrokeThickness = 2.5,
//Stroke = System.Windows.Media.Brushes.DeepSkyBlue,
Fill = System.Windows.Media.Brushes.AliceBlue,
DataLabels = dlina <= 12 ? true : false,
FontSize = 10,
FontWeight = FontWeights.Bold,
LabelPoint = point => string.Format("{0:N0}", point.Y),
Values = new ChartValues <ObservableValue>(ValueYFill(SeriesTitle[i]))
};
SeriesCollection.Add(ls1);
LineSeries ls2 = new LineSeries
{
Title = SeriesTitle[i + 1],
LineSmoothness = 0,
DataLabels = true,
StrokeThickness = 2.5,
//Stroke = System.Windows.Media.Brushes.Red,
Fill = System.Windows.Media.Brushes.Transparent,
FontSize = 10,
FontWeight = FontWeights.Bold,
LabelPoint = point => string.Format("{0:N0}", point.Y),
Values = new ChartValues <ObservableValue>(ValueYFill(SeriesTitle[i + 1]))
};
SeriesCollection.Add(ls2);
LineSeries ls3 = new LineSeries
{
Title = SeriesTitle[i + 2],
LineSmoothness = 0,
DataLabels = true,
FontSize = 10,
FontWeight = FontWeights.Bold,
LabelPoint = point => string.Format("{0:N0}", point.Y),
AreaLimit = 0,
Values = new ChartValues <ObservableValue>(ValueYFill(SeriesTitle[i + 2]))
};
SeriesCollection.Add(ls3);
Labels.Clear();
foreach (var newX in MonthUseList)
{
Labels.Add(newX.Period.ToString());
}
;
//Labels.Clear();
//Labels = new ObservableCollection<string>(MonthUseList.Select(x => x.Period));
Formatter = val => val.ToString("N0");
ChartValues <ObservableValue> ValueYFill(string typeSeries = "")
{
ChartValues <ObservableValue> rez = new ChartValues <ObservableValue>();
switch (typeSeries)
{
case ("План"):
foreach (ViewResult newY in MonthUseList)
{
rez.Add(new ObservableValue(newY.Plan));
}
break;
case ("Факт"):
foreach (ViewResult newY in MonthUseList)
{
rez.Add(new ObservableValue(newY.Fact));
}
break;
case ("Откл"):
foreach (ViewResult newY in MonthUseList)
{
rez.Add(new ObservableValue(newY.Difference));
}
break;
case ("ПланРуб"):
foreach (ViewResult newY in MonthUseList)
{
rez.Add(new ObservableValue(newY.PlanCost));
}
break;
case ("ФактРуб"):
foreach (ViewResult newY in MonthUseList)
{
rez.Add(new ObservableValue(newY.FactCost));
}
break;
case ("ОтклРуб"):
foreach (ViewResult newY in MonthUseList)
{
rez.Add(new ObservableValue(newY.DifferenceCost));
}
break;
}
return(rez);
}
}
public void DiagrammInit()
{
string[] SeriesTitle = new string[] { "Потребл", "Продажа", "Потери" };
int i = 0;
int dlina = MonthUseList.Count();
SeriesCollection.Clear();
StackedAreaSeries ls1 = new StackedAreaSeries
{
Title = SeriesTitle[i],
LineSmoothness = 0,
DataLabels = true,
StrokeThickness = 2.5,
//Stroke = System.Windows.Media.Brushes.DeepSkyBlue,
Fill = System.Windows.Media.Brushes.AliceBlue,
FontSize = 10,
Foreground = System.Windows.Media.Brushes.Black,
FontWeight = FontWeights.Bold,
LabelPoint = point => string.Format("{0:N0}", point.Y),
Values = new ChartValues <ObservableValue>(ValueYFill(SeriesTitle[i]))
};
StackedAreaSeries ls2 = new StackedAreaSeries
{
Title = SeriesTitle[i + 1],
LineSmoothness = 0,
DataLabels = true,
StrokeThickness = 2.5,
//Stroke = System.Windows.Media.Brushes.Red,
Fill = System.Windows.Media.Brushes.Pink,
FontSize = 10,
Foreground = System.Windows.Media.Brushes.Black,
FontWeight = FontWeights.Bold,
LabelPoint = point => string.Format("{0:N0}", point.Y),
Values = new ChartValues <ObservableValue>(ValueYFill(SeriesTitle[i + 1]))
};
StackedAreaSeries ls3 = new StackedAreaSeries
{
Title = SeriesTitle[i + 2],
LineSmoothness = 0,
DataLabels = true,
FontSize = 10,
StrokeThickness = 2.5,
Foreground = System.Windows.Media.Brushes.Black,
Stroke = System.Windows.Media.Brushes.DarkRed,
Fill = System.Windows.Media.Brushes.Moccasin,
FontWeight = FontWeights.Bold,
LabelPoint = point => string.Format("{0:N0}", point.Y),
AreaLimit = 0,
Values = new ChartValues <ObservableValue>(ValueYFill(SeriesTitle[i + 2]))
};
SeriesCollection.Add(ls1);
SeriesCollection.Add(ls2);
SeriesCollection.Add(ls3);
Labels.Clear();
foreach (var newX in MonthUseList)
{
Labels.Add(newX.Period.ToString());
}
;
//Labels.Clear();
//Labels = ObservableCollection<string>(MonthUseList.Select(x => x.Period));
ChartValues <ObservableValue> ValueYFill(string typeSeries = "")
{
ChartValues <ObservableValue> rez = new ChartValues <ObservableValue>();
switch (typeSeries)
{
case ("Потребл"):
foreach (ViewFabricateUse newY in MonthUseList)
{
rez.Add(new ObservableValue(newY.Fact1));
}
break;
case ("Продажа"):
foreach (ViewFabricateUse newY in MonthUseList)
{
rez.Add(new ObservableValue(newY.Fact0));
}
break;
case ("Потери"):
foreach (ViewFabricateUse newY in MonthUseList)
{
rez.Add(new ObservableValue(newY.Loss));
}
break;
}
return(rez);
}
}
public void DiagrammInit()
{
int dlina = MonthUseList.Count();
string[] SeriesTitle = new string[] { "Всего", "Потери", "ВсегоРуб", "ПотериРуб" };
int i = !RBCostIsChecked ? 0 : 2;
SeriesCollection.Clear();
LineSeries ls1 = new LineSeries
{
Title = SeriesTitle[i],
LineSmoothness = 0,
StrokeThickness = 2.5,
Fill = System.Windows.Media.Brushes.AliceBlue,
DataLabels = true,
FontSize = 10,
FontWeight = FontWeights.Bold,
LabelPoint = point => string.Format("{0:N0}", point.Y),
Values = new ChartValues <ObservableValue>(ValueYFill(SeriesTitle[i]))
};
SeriesCollection.Add(ls1);
LineSeries ls2 = new LineSeries
{
Title = SeriesTitle[i + 1],
LineSmoothness = 0,
DataLabels = true,
StrokeThickness = 2.5,
Fill = System.Windows.Media.Brushes.Transparent,
FontSize = 10,
FontWeight = FontWeights.Bold,
LabelPoint = point => string.Format("{0:N0}", point.Y),
Values = new ChartValues <ObservableValue>(ValueYFill(SeriesTitle[i + 1]))
};
SeriesCollection.Add(ls2);
Labels.Clear();
foreach (var newX in MonthUseList)
{
Labels.Add(newX.Period.ToString());
}
;
//Labels.Clear();
//Labels = new ObservableCollection<string>(MonthUseList.Select(x => x.Period));
Formatter = val => val.ToString("N0");
ChartValues <ObservableValue> ValueYFill(string typeSeries = "")
{
ChartValues <ObservableValue> rez = new ChartValues <ObservableValue>();
switch (typeSeries)
{
case ("Всего"):
foreach (ViewLossesFact newY in MonthUseList)
{
rez.Add(new ObservableValue(newY.FactTotal));
}
break;
case ("Потери"):
foreach (ViewLossesFact newY in MonthUseList)
{
rez.Add(new ObservableValue(newY.FactLoss));
}
break;
case ("ВсегоРуб"):
foreach (ViewLossesFact newY in MonthUseList)
{
rez.Add(new ObservableValue(newY.FactTotalCost));
}
break;
case ("ПотериРуб"):
foreach (ViewLossesFact newY in MonthUseList)
{
rez.Add(new ObservableValue(newY.FactLossCost));
}
break;
}
return(rez);
}
}
