public void SwitchOffClients()
{
var filter = new SwitchOffClientsFilter();
BindObjectInstance(filter, IsPost ? ParamStore.Form : ParamStore.QueryString, "filter", AutoLoadBehavior.NullIfInvalidKey);
PropertyBag["filter"] = filter;
PropertyBag["Clients"] = filter.Find(DbSession);
}
public static byte[] ExcelSwitchOffClients(SwitchOffClientsFilter filter)
{
var wb = new Workbook();
var ws = new Worksheet("Список отключенных клиентов");
int row = 4;
int colShift = 0;
ws.Merge(0, 0, 0, 6);
ExcelHelper.WriteHeader1(ws, 0, 0, "Список отключенных клиентов", false, true);
ws.Merge(1, 1, 1, 2);
ExcelHelper.Write(ws, 1, 0, "Регион:", false);
string regionName;
if (filter.Region == null)
{
regionName = "Все";
}
else
{
regionName = filter.Region.Name;
}
ExcelHelper.Write(ws, 1, 1, regionName, false);
ws.Merge(2, 1, 2, 2);
ExcelHelper.Write(ws, 2, 0, "Период:", false);
if (filter.Period.Begin != filter.Period.End)
{
ExcelHelper.Write(ws, 2, 1,
"С " + filter.Period.Begin.ToString("dd.MM.yyyy") + " по " + filter.Period.End.ToString("dd.MM.yyyy"), false);
}
else
{
ExcelHelper.Write(ws, 2, 1, "За " + filter.Period.Begin.ToString("dd.MM.yyyy"), false);
}
var reportData = GetExcelSwitchOffClients(filter);
foreach (var item in reportData)
{
ExcelHelper.Write(ws, row, colShift + 0, item.ClientId, true);
ExcelHelper.Write(ws, row, colShift + 1, item.ClientName, true);
ExcelHelper.Write(ws, row, colShift + 2, item.RegionName, true);
ExcelHelper.Write(ws, row, colShift + 3, item.LogTime, true);
ExcelHelper.Write(ws, row, colShift + 4, item.Comment, true);
row++;
}
FormatSwitchOffClients(ws, filter);
wb.Worksheets.Add(ws);
var ms = new MemoryStream();
wb.Save(ms);
return(ms.ToArray());
}
public void Base_filter_test()
{
var filter = new SwitchOffClientsFilter {
SortBy = "ClientName"
};
filter.Find(session);
}
private static IEnumerable <SwitchOffCounts> GetExcelSwitchOffClients(SwitchOffClientsFilter filter)
{
var criteria = filter.GetCriteria();
filter.ApplySort(criteria);
return(ArHelper.WithSession(s => filter.Find(s, true)).ToList());
}
public void ExportSwitchOffClientsTest()
{
var filter = new SwitchOffClientsFilter();
var buf = ExportModel.ExcelSwitchOffClients(filter);
var stream = new MemoryStream(buf);
var wb = Workbook.Load(stream);
var ws = wb.Worksheets.First();
Assert.That(ws.Name, Is.StringContaining("Список отключенных клиентов"));
}
private static void FormatSwitchOffClients(Worksheet ws, SwitchOffClientsFilter filter)
{
int headerRow = 3;
ExcelHelper.WriteHeader1(ws, headerRow, 0, "Код клиента", true, true);
ExcelHelper.WriteHeader1(ws, headerRow, 1, "Наименование клиента", true, true);
ExcelHelper.WriteHeader1(ws, headerRow, 2, "Регион", true, true);
ExcelHelper.WriteHeader1(ws, headerRow, 3, "Дата отключения", true, true);
ExcelHelper.WriteHeader1(ws, headerRow, 4, "Комментарий оператора", true, true);
ws.Cells.ColumnWidth[0] = 4000;
ws.Cells.ColumnWidth[1] = 12000;
ws.Cells.ColumnWidth[2] = 6000;
ws.Cells.ColumnWidth[3] = 5000;
ws.Cells.ColumnWidth[4] = 20000;
ws.Cells.Rows[headerRow].Height = 514;
}
public void ExcelSwitchOffClients()
{
var filter = new SwitchOffClientsFilter();
BindObjectInstance(filter, IsPost ? ParamStore.Form : ParamStore.QueryString, "filter", AutoLoadBehavior.NullIfInvalidKey);
this.RenderFile("Список_отключенных_клиентов.xls", ExportModel.ExcelSwitchOffClients(filter));
}