private List <string[]> ExecuteMaintainsQuery(string sql)
{
Log.Info(sql);
var messages = StatelessSession
.CreateSQLQuery(sql)
.List <object[]>()
.Select(m => m.Select(v => v.ToString()).ToArray())
.ToList();
Log.Info(messages.Implode(s => s.Implode(), Environment.NewLine));
return(messages);
}
public override void Execute()
{
var end = DateTime.Today.FirstDayOfWeek();
var begin = end.AddDays(-7);
var period = end.ToShortDateString();
var rows = StatelessSession.CreateSQLQuery(@"
select r.DrugID, r.InnR, r.TradeNmR, r.DrugFmNmRS, r.Pack, r.DosageR, r.ClNm, r.Segment,
:period as RptPeriod,
l.SupplierCost as PrcPrice,
cast(round(min(l.RetailCost), 2) as char) as RtlPrice
from WaybillLines l
join RegulatorRegistry r on r.ProductId = l.ProductId and r.ProducerId = l.ProducerId
join Waybills w on w.Id = l.WaybillId
where w.DocumentDate >= :begin and w.DocumentDate < :end and l.RetailCost is not null
group by r.DrugID")
.SetParameter("begin", begin)
.SetParameter("end", end)
.SetParameter("period", period)
.List();
var settings = Session.Query <Settings>().First();
var dir = settings.InitAndMap("Reports");
Result = Path.Combine(dir, FileHelper.StringToFileName($"Росздравнадзор-{period}.xls"));
var book = new HSSFWorkbook();
var sheet = book.CreateSheet("Отчет");
var reportRow = sheet.CreateRow(0);
reportRow.CreateCell(0).SetCellValue("DrugID");
reportRow.CreateCell(1).SetCellValue("InnR");
reportRow.CreateCell(2).SetCellValue("TradeNmR");
reportRow.CreateCell(3).SetCellValue("DrugFmNmRS");
reportRow.CreateCell(4).SetCellValue("Pack");
reportRow.CreateCell(5).SetCellValue("DosageR");
reportRow.CreateCell(6).SetCellValue("ClNm");
reportRow.CreateCell(7).SetCellValue("Segment");
reportRow.CreateCell(8).SetCellValue("RptPeriod");
reportRow.CreateCell(9).SetCellValue("PrcPrice");
reportRow.CreateCell(10).SetCellValue("RtlPrice");
for (var i = 0; i < rows.Count; i++)
{
reportRow = sheet.CreateRow(i + 1);
var row = ((object[])rows[i]);
for (var j = 0; j < row.Length; j++)
{
reportRow.CreateCell(j).SetCellValue(row[j]?.ToString());
}
}
using (var stream = File.Create(Result))
book.Write(stream);
}
public override void Execute()
{
InitSession();
var tables = TableNames().ToArray();
Reporter.Weight(tables.Length);
var results = new List <RepairStatus>();
foreach (var table in tables)
{
Token.ThrowIfCancellationRequested();
var messages = ExecuteMaintainsQuery($"REPAIR TABLE {table} EXTENDED");
var result = Parse(messages);
results.Add(result);
if (result == RepairStatus.NumberOfRowsChanged || result == RepairStatus.Ok)
{
result = Parse(ExecuteMaintainsQuery($"OPTIMIZE TABLE {table}"));
results.Add(result);
}
results.Add(result);
if (result == RepairStatus.Fail)
{
Log.ErrorFormat("Таблица {0} не может быть восстановлена.", table);
Log.Error($"DROP TABLE IF EXISTS {table}");
StatelessSession
.CreateSQLQuery($"DROP TABLE IF EXISTS {table}")
.ExecuteUpdate();
//если заголовок таблицы поврежден то drop table не даст результатов
//файлы останутся а при попытке создать таблицу будет ошибка
//нужно удалить файлы
Directory.GetFiles(Config.DbDir, table + ".*").Each(File.Delete);
}
Reporter.Progress();
}
Configure(new SanityCheck()).Check(true);
Result = results.All(r => r == RepairStatus.Ok);
}
void ExecuteNonQuery(string sql)
{
ISQLQuery query = StatelessSession.CreateSQLQuery(sql);
query.ExecuteUpdate();
}
public override void Execute()
{
//отчет всегда готовится за предыдущий год
var end = new DateTime(DateTime.Today.Year, 1, 1);
var year = DateTime.Today.Year - 1;
var begin = new DateTime(year, 1, 1);
var subrows = "sum(SupplierCost) / 1000 {0} SupplierCost, sum(RetailCost) / 1000 {0} RetailCost, sum(ProducerCost) / 1000 {0} ProducerCost";
if (withNds)
{
subrows = string.Format(subrows, "* 1.1");
}
else
{
subrows = string.Format(subrows, "");
}
var query = StatelessSession.CreateSQLQuery($@"
select
BarCode,
sum(Total) Total,
{subrows},
min(if(registrycost = 0, null, registrycost)) RegistryCost,
sum(Planned) Planned,
(round(avg(RetailCostM),2) - round(avg(SupplierCostM),2)) / 1000 as Margin
from
(select
b.Value as BarCode,
l.Quantity / 1000 Total,
round(l.SupplierCost * l.Quantity, 2) SupplierCost,
round(l.RetailCost * l.Quantity, 2) RetailCost,
round(producercost * l.Quantity,2) ProducerCost,
RegistryCost,
quantity / 1000 Planned,
RetailCost RetailCostM,
SupplierCost SupplierCostM
from WaybillLines l
join Waybills w on w.Id = l.WaybillId
join BarCodes b on b.Value = l.EAN13
where b.Value = l.EAN13 and w.DocumentDate > :begin and w.DocumentDate < :end
group by l.id) as sub
group by BarCode;");
query.SetParameter("begin", begin);
query.SetParameter("end", end);
var rows = query.List();
var settings = Session.Query <Settings>().First();
var dir = settings.InitAndMap("Reports");
Result = Path.Combine(dir, FileHelper.StringToFileName($"Надб-ЖНВЛС-{year}.xls"));
var book = new HSSFWorkbook();
var sheet = book.CreateSheet("ЛС");
var reportRow = sheet.CreateRow(0);
reportRow.CreateCell(0).SetCellValue("Штрихкод");
reportRow.CreateCell(1).SetCellValue("Количество");
reportRow.CreateCell(2).SetCellValue("СтоимостьПриобр");
reportRow.CreateCell(3).SetCellValue("СтоимостьРеализ");
reportRow.CreateCell(4).SetCellValue("СтоимостьВЦенахПроизв");
reportRow.CreateCell(5).SetCellValue("ПредельнаяЦенаПроизв");
reportRow.CreateCell(6).SetCellValue("КоличествоПлан");
reportRow.CreateCell(7).SetCellValue("ВаловаяПрибыльПлан");
var converter = new SlashNumber();
for (var i = 0; i < rows.Count; i++)
{
reportRow = sheet.CreateRow(i + 1);
var row = ((object[])rows[i]);
for (var j = 0; j < row.Length; j++)
{
if (j == 0)
{
reportRow.CreateCell(j).SetCellValue(row[j]?.ToString());
continue;
}
if (j > 1 && j < 5)
{
reportRow.CreateCell(j).SetCellValue(converter.Convert(Convert.ToDouble(row[j]), 5));
continue;
}
reportRow.CreateCell(j).SetCellValue(Convert.ToDouble(row[j]));
}
}
using (var stream = File.Create(Result))
book.Write(stream);
}