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); }