public static void BuildSvedProf(IBackgroundContext context, MonthAccountParams args)
{
var dateBegin = args.DateBegin;
var dateEnd = args.DateEnd;
var miacCode = args.LpuCode;
using (var dataConext = new VistaMedDataContext(context))
{
var smpBills = (from b in dataConext.VistaMed.Accounts
where b.Date >= args.DateBegin.AddDays(15) && b.Date < args.NextMonthBegin.AddDays(15)
&& b.Contract != null && b.Contract.RbFinance != null
&& b.Contract.RbFinance.Name.ToLower() == "омс"
&& (from a in b.AccountItems
from s in dataConext.VistaMed.RbServices
where a.ServiceId == s.Id
&& s.Code.StartsWith("062")
select a).Any()
select b).ToList();
smpBills = smpBills.Where(x => args.IsValidAccountNumber(x.Number)
&& !x.Number.StartsWith(PlanExecution.ReserveAccountName)).ToList();
if (smpBills.Count == 0)
context.ReportError(@"Счета ДД за период с {0} по {1} не найдены", dateBegin, dateEnd);
else
{
var excel = new Application();
excel.Visible = true;
var plans = loadPlans(excel);
var templateName = Utils.GetReportFileName(@"SvedProf.xls");
if (File.Exists(templateName))
{
var newDoc = excel.Workbooks.Add(templateName);
excel.ScreenUpdating = false;
var ws = ((Worksheet) newDoc.Worksheets[1]);
newDoc.Names.Item(@"ДатаНачала").RefersToRange.Value2 = dateBegin;
newDoc.Names.Item(@"ДатаКонца").RefersToRange.Value2 = dateEnd;
newDoc.Names.Item(@"ДатаОтчета").RefersToRange.Value2 = DateTime.Today;
newDoc.Names.Item(@"ОтчетныйПериод").RefersToRange.Value2 = Utils.GetPeriodName(dateBegin, dateEnd);
var lpu = dataConext.VistaMed.Organisations.FirstOrDefault(x => x.MiacCode == miacCode);
if (lpu != null)
{
newDoc.Names.Item("ЛПУ").RefersToRange.Value2 = lpu.FullName;
newDoc.Names.Item("ФИОРуководителя").RefersToRange.Value2 = lpu.Chief;
newDoc.Names.Item("ФИОГлавбуха").RefersToRange.Value2 = lpu.Accountant;
}
var reportColumn = new ReportColumn();
reportColumn.Man = plans.Sum(x => x.Man);
reportColumn.Woman = plans.Sum(x => x.Woman);
fillReportColumn(ws, reportColumn, 3);
reportColumn = new ReportColumn();
reportColumn.Man = plans.Where(y => y.Month == dateEnd.Month).Sum(x => x.Man);
reportColumn.Woman = plans.Where(y => y.Month == dateEnd.Month).Sum(x => x.Woman);
fillReportColumn(ws, reportColumn, 7);
var allevents = (from b in smpBills
from a in b.AccountItems
where !a.Deleted && a.Event != null && a.Event.Client != null
group a by a.Event
into e
select new {Event = e.Key, Client = e.Key.Client, Summa = e.Sum(x => x.Sum)}).ToList();
var man = (from e in allevents where e.Client.Sex == 1 select e).ToList();
var woman = (from e in allevents where e.Client.Sex == 2 select e).ToList();
reportColumn = new ReportColumn();
reportColumn.Man = man.Sum(x => x.Summa);
reportColumn.Woman = woman.Sum(x => x.Summa);
fillReportColumn(ws, reportColumn, 8);
reportColumn = new ReportColumn();
reportColumn.Man = man.Count();
reportColumn.Woman = woman.Count();
fillReportColumn(ws, reportColumn, 9);
excel.ScreenUpdating = true;
ws.Activate();
}
}
}
}
public static void BuildSvedReport(IBackgroundContext context, MonthAccountParams args)
{
var dateBegin = args.DateBegin;
var dateEnd = args.DateEnd;
var miacCode = args.LpuCode;
using (var dataConext = new VistaMedDataContext(context))
{
var smpBills = (from b in dataConext.VistaMed.Accounts
where b.Date >= args.DateBegin.AddDays(15) && b.Date < args.NextMonthBegin.AddDays(15)
&& b.Contract != null && b.Contract.RbFinance != null
&& b.Contract.RbFinance.Name.ToLower() == "омс"
&& (from a in b.AccountItems
from s in dataConext.VistaMed.RbServices
where a.ServiceId == s.Id
&& (s.Code.StartsWith("161") || s.Code.StartsWith("162"))
select a).Any()
select b).ToList();
smpBills = smpBills.Where(x => x.AccountType == AccountType.Osm18
&& args.IsValidAccountNumber(x.Number)
&& !x.Number.StartsWith(PlanExecution.ReserveAccountName)).ToList();
if (smpBills.Count == 0)
context.ReportError(@"Счета ДД за период с {0} по {1} не найдены", dateBegin, dateEnd);
else
{
var templateName = Utils.GetReportFileName(@"SvedOsm18.xls");
if (File.Exists(templateName))
{
var excel = new Application();
excel.Visible = true;
var planDataList = loadPlan(excel);
var newDoc = excel.Workbooks.Add(templateName);
excel.ScreenUpdating = false;
var ws = ((Worksheet)newDoc.Worksheets[1]);
newDoc.Names.Item(@"ДатаНачала").RefersToRange.Value2 = dateBegin;
newDoc.Names.Item(@"ДатаКонца").RefersToRange.Value2 = dateEnd;
newDoc.Names.Item(@"ДатаОтчета").RefersToRange.Value2 = DateTime.Today;
newDoc.Names.Item(@"ОтчетныйПериод").RefersToRange.Value2 = Utils.GetPeriodName(dateBegin, dateEnd);
var lpu = dataConext.VistaMed.Organisations.FirstOrDefault(x => x.MiacCode == miacCode);
if (lpu != null)
{
newDoc.Names.Item("ЛПУ").RefersToRange.Value2 = lpu.FullName;
newDoc.Names.Item("ФИОРуководителя").RefersToRange.Value2 = lpu.Chief;
newDoc.Names.Item("ФИОГлавбуха").RefersToRange.Value2 = lpu.Accountant;
}
ReportData colData;
colData = new ReportData();
colData.Prof = planDataList.Sum(x => x.Prof);
colData.Pred = null;
colData.Period = planDataList.Sum(x => x.Period); ;
fillColumn(ws, colData, 3);
colData = new ReportData();
colData.Prof = planDataList.Where(y => y.Month <= dateEnd.Month).Sum(x => x.Prof);
colData.Pred = null;
colData.Period = planDataList.Where(y => y.Month <= dateEnd.Month).Sum(x => x.Period); ;
fillColumn(ws, colData, 4);
var eventList = (from b in smpBills
from a in b.AccountItems
where !a.Deleted && a.Event != null && a.Event.RbResult != null && a.Action != null && a.Action.ActionType != null
group a by a.Event into e select e).ToList();
var profActionCodes = new[] { "162043", "162044", "162045", "162046", "162002",
"162003", "162005", "162006", "162007", "162008", "162009", "162010" };
var predActionCodes = new[] { "162040", "162041", "162042" };
var perActionsCodes = new[] { "162060", "162061", "162062"};
var txtFileName = Utils.GetOptionsFileName(@"SvedOsm18Services.txt");
if (File.Exists(txtFileName))
{
var allLines = File.ReadAllLines(txtFileName, Encoding.GetEncoding(1251));
foreach (var currLine in allLines)
{
if (currLine.StartsWith(@"Профилактика"))
{
var lines2 = currLine.Split('=');
profActionCodes = lines2[1].Split(',');
}
if (currLine.StartsWith(@"Предварительные"))
{
var lines2 = currLine.Split('=');
predActionCodes = lines2[1].Split(',');
}
if (currLine.StartsWith(@"Периодические"))
{
var lines2 = currLine.Split('=');
perActionsCodes = lines2[1].Split(',');
}
}
}
var profList = (from e in eventList
where e.Any(a => profActionCodes.Any(x => a.Action.ActionType.Code == x))
select e).ToList();
var predList = (from e in eventList
where e.Any(a => predActionCodes.Any(x => a.Action.ActionType.Code == x))
select e).ToList();
var periodList = eventList.Except(profList).Except(predList).ToList();
colData = new ReportData();
colData.Prof = (from e in profList from a in e select a).Sum(x => x.Sum) / 1000;
colData.Pred = (from e in predList from a in e select a).Sum(x => x.Sum) / 1000;
colData.Period = (from e in periodList from a in e select a).Sum(x => x.Sum) / 1000;
fillColumn(ws, colData, 5);
colData = new ReportData();
colData.Prof = profList.Count;
colData.Pred = predList.Count;
colData.Period = periodList.Count;
fillColumn(ws, colData, 6);
string grName = "";
int grColIndex = 0;
grColIndex = 15;
grName = @" I группа";
grZd(profList, grName, predList, ws, grColIndex++);
grName = @" II группа";
grZd(profList, grName, predList, ws, grColIndex++);
grName = @" III группа";
grZd(profList, grName, predList, ws, grColIndex++);
grName = @" IV группа";
grZd(profList, grName, predList, ws, grColIndex++);
grName = @" V группа";
grZd(profList, grName, predList, ws, grColIndex++);
excel.ScreenUpdating = true;
ws.Activate();
}
}
}
context.ProgressSeparator('-');
}
public static void BuildSvedReport(IBackgroundContext context, MonthAccountParams args)
{
var dateBegin = args.DateBegin;
var dateEnd = args.DateEnd;
var miacCode = args.LpuCode;
using (var dataConext = new VistaMedDataContext(context))
{
var smpBills = (from b in dataConext.VistaMed.Accounts
where b.Date >= args.DateBegin.AddDays(15) && b.Date < args.NextMonthBegin.AddDays(15)
&& b.Contract != null && b.Contract.RbFinance != null
&& b.Contract.RbFinance.Name.ToLower() == "омс"
&& (from a in b.AccountItems
from s in dataConext.VistaMed.RbServices
where a.ServiceId == s.Id
&& s.Code.StartsWith("062")
select a).Any()
select b).ToList();
smpBills = smpBills.Where(x => x.AccountType == AccountType.ProfOsm).ToList();
smpBills = smpBills.Where(x => args.IsValidAccountNumber(x.Number)).ToList();
if (smpBills.Count == 0)
context.ReportError(@"Счета ДД за период с {0} по {1} не найдены", dateBegin, dateEnd);
else
{
var templateName = Utils.GetReportFileName(@"SvodSpravProf.xls");
if (File.Exists(templateName))
{
var excel = new Application();
excel.Visible = true;
foreach (var smpBill in smpBills)
{
var newDoc = excel.Workbooks.Add(templateName);
excel.ScreenUpdating = false;
var ws = ((Worksheet)newDoc.Worksheets[1]);
newDoc.Names.Item(@"ОтчетныйПериод").RefersToRange.Value2 =
Utils.GetPeriodName(smpBill.SettleDate.BeginOfMonth(), smpBill.SettleDate);
var lpu = dataConext.VistaMed.Organisations.FirstOrDefault(x => x.MiacCode == miacCode);
if (lpu != null)
{
newDoc.Names.Item("ЛПУ").RefersToRange.Value2 = lpu.FullName;
newDoc.Names.Item("ФИОРуководителя").RefersToRange.Value2 = lpu.Chief;
newDoc.Names.Item("ФИОГлавбуха").RefersToRange.Value2 = lpu.Accountant;
}
var smo =
(from payer in dataConext.VistaMed.Organisations
where payer.Id == smpBill.PayerId
select payer).FirstOrDefault();
if (smo != null)
{
newDoc.Names.Item("СМО").RefersToRange.Value2 = smo.FullName;
}
ReportData colData;
var eventList = (from a in smpBill.AccountItems
where !a.Deleted && a.Event != null && a.Event.RbResult != null && a.Action != null && a.Action.ActionType != null
group a by a.Event into e
select e).ToList();
colData = new ReportData();
colData.Prof = eventList.Count;
fillColumn(ws, colData, 2);
colData = new ReportData();
colData.Prof = (from e in eventList from a in e select a).Sum(x => x.Sum);
fillColumn(ws, colData, 3);
excel.ScreenUpdating = true;
ws.Activate();
}
}
}
}
context.ProgressSeparator('-');
}
public static void BuildSvedReport(IBackgroundContext context, MonthAccountParams args)
{
var dateBegin = args.DateBegin;
var dateEnd = args.DateEnd;
var miacCode = args.LpuCode;
using (var dataConext = new VistaMedDataContext(context))
{
var smpBills = (from b in dataConext.VistaMed.Accounts
where b.Date >= args.DateBegin.AddDays(15) && b.Date < args.NextMonthBegin.AddDays(15)
&& b.Contract != null && b.Contract.RbFinance != null
&& b.Contract.RbFinance.Name.ToLower() == "омс"
&& (from a in b.AccountItems
from s in dataConext.VistaMed.RbServices
where a.ServiceId == s.Id
&& s.Code.StartsWith("162")
select a).Any()
select b).ToList();
smpBills = smpBills.Where(x => x.AccountType == AccountType.Osm18).ToList();
smpBills = smpBills.Where(x => args.IsValidAccountNumber(x.Number)).ToList();
if (smpBills.Count == 0)
context.ReportError(@"Счета ДД за период с {0} по {1} не найдены", dateBegin, dateEnd);
else
{
var templateName = Utils.GetReportFileName(@"SvodSpravOsm18.xls");
if (File.Exists(templateName))
{
var excel = new Application();
excel.Visible = true;
foreach (var smpBill in smpBills)
{
var newDoc = excel.Workbooks.Add(templateName);
excel.ScreenUpdating = false;
var ws = ((Worksheet)newDoc.Worksheets[1]);
newDoc.Names.Item(@"ОтчетныйПериод").RefersToRange.Value2 =
Utils.GetPeriodName(smpBill.SettleDate.BeginOfMonth(), smpBill.SettleDate);
var lpu = dataConext.VistaMed.Organisations.FirstOrDefault(x => x.MiacCode == miacCode);
if (lpu != null)
{
newDoc.Names.Item("ЛПУ").RefersToRange.Value2 = lpu.FullName;
newDoc.Names.Item("ФИОРуководителя").RefersToRange.Value2 = lpu.Chief;
newDoc.Names.Item("ФИОГлавбуха").RefersToRange.Value2 = lpu.Accountant;
}
var smo =
(from payer in dataConext.VistaMed.Organisations
where payer.Id == smpBill.PayerId
select payer).FirstOrDefault();
if (smo != null)
{
newDoc.Names.Item("СМО").RefersToRange.Value2 = smo.FullName;
}
ReportData colData;
var eventList = (from a in smpBill.AccountItems
where !a.Deleted && a.Event != null && a.Event.RbResult != null && a.Action != null && a.Action.ActionType != null
group a by a.Event into e
select e).ToList();
var profActionCodes = new[] { "162043", "162044", "162045", "162046", "162002",
"162003", "162005", "162006", "162007", "162008", "162009", "162010" };
var predActionCodes = new[] { "162040", "162041", "162042" };
var txtFileName = Utils.GetOptionsFileName(@"SvedOsm18Services.txt");
if (File.Exists(txtFileName))
{
var allLines = File.ReadAllLines(txtFileName, Encoding.GetEncoding(1251));
foreach (var currLine in allLines)
{
if (currLine.StartsWith(@"Профилактика"))
{
var lines2 = currLine.Split('=');
profActionCodes = lines2[1].Split(',');
}
if (currLine.StartsWith(@"Предварительные"))
{
var lines2 = currLine.Split('=');
predActionCodes = lines2[1].Split(',');
}
}
}
var profList = (from e in eventList
where e.Any(a => profActionCodes.Any(x => a.Action.ActionType.Code == x))
select e).ToList();
var predList = (from e in eventList
where e.Any(a => predActionCodes.Any(x => a.Action.ActionType.Code == x))
select e).ToList();
var periodList = eventList.Except(profList).Except(predList).ToList();
colData = new ReportData();
colData.Prof = profList.Count;
colData.Pred = predList.Count;
colData.Period = periodList.Count;
fillColumn(ws, colData, 2);
colData = new ReportData();
colData.Prof = (from e in profList from a in e select a).Sum(x => x.Sum);
colData.Pred = (from e in predList from a in e select a).Sum(x => x.Sum);
colData.Period = (from e in periodList from a in e select a).Sum(x => x.Sum);
fillColumn(ws, colData, 3);
excel.ScreenUpdating = true;
ws.Activate();
}
}
}
}
context.ProgressSeparator('-');
}