public IHttpActionResult Search([FromUri] BTSStatusSearchFilter filter)
{
string accessType = "BTSStatus_ViewAll";
ThrowIfUserHasNoRole(accessType);
if (filter == null)
{
throw new KairosException("Missing search filter parameter");
}
using (var btsStatusSearch = new BTSStatusSearch(Db))
{
var data = btsStatusSearch.GetDataByFilter(filter);
return(Ok(new SuccessResponse(data)));
}
}
public BTSStatusListDataProvider(GlobalSolusindoDb db, tblM_User user, BTSStatusSearch btsStatusSearch) : base(db, user)
{
this.btsStatusSearch = btsStatusSearch;
}
public HttpResponseMessage Export(GlobalSolusindoDb Db, string fileName, BTSStatusSearchFilter filter)
{
_fileName = fileName;
//CREATE WORKBOOK
var workbook = new XLWorkbook();
DataTable bts = new DataTable("BTSUpload"); //DataTable Name = Worksheet Name
BTSExportDTO obj = new BTSExportDTO();
//Setup Column Names
foreach (var item in obj.GetType().GetProperties())
{
bts.Columns.Add(item.Name);
}
workbook.AddWorksheet(bts); // NO DATA = ADD Worksheet to WorkBook
//Worksheet Properties
var worksheet = workbook.Worksheet(1);
worksheet.Columns().Width = 15;
//BTS Status
using (var BTSStatusSearch = new BTSStatusSearch(Db))
{
// Validation BTS Status
DataTable validationTable = new DataTable();
validationTable.Columns.Add("StatusBTSFK");
validationTable.Columns.Add("StatusBTS");
validationTable.TableName = "StatusBTS";
var data = BTSStatusSearch.GetDataByFilter(filter);
var dataExport = data.Records.Select(c => new tblM_BTSStatus
{
BTSStatus_PK = c.BTSStatus_PK,
Title = c.Title
}).ToList();
DataRow dr;
int startcell = 2, endcell = 2;
foreach (var item in dataExport)
{
dr = validationTable.NewRow();
dr["StatusBTSFK"] = item.BTSStatus_PK;
dr["StatusBTS"] = item.Title;
validationTable.Rows.Add(dr);
endcell++;
}
var worksheet2 = workbook.AddWorksheet(validationTable);
worksheet.Column(9).SetDataValidation().List(worksheet2.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
//Validation Operator
DataTable validationTable2 = new DataTable();
validationTable2.Columns.Add("Operator_FK");
validationTable2.Columns.Add("Operator");
validationTable2.TableName = "Operator";
//Operator
OperatorSearchFilter a = new OperatorSearchFilter();
using (var OperatorSearch = new OperatorSearch(Db))
{
var data2 = OperatorSearch.GetDataByFilter(a);
var dataExport2 = data2.Records.Select(c => new tblM_Operator
{
Operator_PK = c.Operator_PK,
Title = c.Title
}).ToList();
DataRow dr2;
startcell = 2; endcell = 2;
foreach (var item in dataExport2)
{
dr2 = validationTable2.NewRow();
dr2["Operator_FK"] = item.Operator_PK;
dr2["Operator"] = item.Title;
validationTable2.Rows.Add(dr2);
endcell++;
}
var worksheet3 = workbook.AddWorksheet(validationTable2);
worksheet.Column(4).SetDataValidation().List(worksheet3.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
//Validation Area
DataTable validationTable3 = new DataTable();
validationTable3.Columns.Add("AreaFK");
validationTable3.Columns.Add("Area");
validationTable3.TableName = "Area";
//Operator
AreaSearchFilter b = new AreaSearchFilter();
using (var AreaSearch = new AreaQuery(Db))
{
var data3 = AreaSearch.Search(b);
var dataExport3 = data3.Records.Select(e => new tblM_Area
{
Area_PK = e.Area_PK,
Title = e.Title
}).ToList();
DataRow dr3;
startcell = 2; endcell = 2;
foreach (var item in dataExport3)
{
dr3 = validationTable3.NewRow();
dr3["AreaFK"] = item.Area_PK;
dr3["Area"] = item.Title;
validationTable3.Rows.Add(dr3);
endcell++;
}
var worksheet4 = workbook.AddWorksheet(validationTable3);
worksheet.Column(7).SetDataValidation().List(worksheet4.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
}
}
}
//worksheet.Column(5).SetDataValidation().List(worksheet2.Range("B2:B4"), true);
MemoryStream memoryStream = GetStream(workbook);
var response = new HttpResponseMessage(HttpStatusCode.OK)
{
Content = new ByteArrayContent(memoryStream.ToArray())
};
response.Content.Headers.ContentType = new MediaTypeHeaderValue
("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.Content.Headers.ContentDisposition =
new ContentDispositionHeaderValue("attachment")
{
FileName = $"{_fileName}_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx"
};
return(response);
}