public FileContentResult ListExcel(IDbConnection connection, ListRequest request)
{
var fields = MyRow.Fields;
//request.IncludeColumns.Clear();
//request.IncludeColumns.Add(fields.ProjectType.PropertyName);
//request.IncludeColumns.Add(fields.PurposeTypeId.PropertyName);
//request.IncludeColumns.Add(fields.PriceType.PropertyName);
//request.IncludeColumns.Add(fields.TrxHeaderId.PropertyName);
//request.IncludeColumns.Add(fields.TenantId.PropertyName);
//request.IncludeColumns.Add(fields.SiteTypeVal.PropertyName);
//request.IncludeColumns.Add(fields.AreaName.PropertyName);
//request.IncludeColumns.Add(fields.ShelterOutdoor.PropertyName);
//request.IncludeColumns.Add(fields.KWHType.PropertyName);
//request.IncludeColumns.Add(fields.PoNumber.PropertyName);
//request.IncludeColumns.Add(fields.PONumberVersion.PropertyName);
//request.IncludeColumns.Add(fields.InputPONumberDate.PropertyName);
//request.IncludeColumns.Add(fields.BoqTotalAmount.PropertyName);
//request.IncludeColumns.Add(fields.SiteId.PropertyName);
//request.IncludeColumns.Add(fields.SiteName.PropertyName);
//request.IncludeColumns.Add(fields.VendorName.PropertyName);
//request.IncludeColumns.Add(fields.RefTrxId.PropertyName);
var data = List(connection, request).Entities;
var report = new DynamicDataReport(data, request.IncludeColumns, typeof(Columns.TbKtpReport));
var bytes = new ReportRepository().Render(report);
return(ExcelContentResult.Create(bytes, MyRow.Fields.AliasName.ToString() + "_" + DateTime.Now.ToString("ddMMyyyy_HHmmss") + ".xlsx"));
}
public FileContentResult ExportDataNINJA(IDbConnection connection, ExcelMassUploadImportRequest request)
{
request.CheckNotNull();
if (request.IdExport == null)
{
throw new ValidationError("Batch Id Cannot NULL");
}
var template = Server.MapPath(ConfigurationManager.AppSettings["TEMPLATENINJA"].ToString());
var filename = "NINJAAWB_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx";
var templateFile = new System.IO.FileInfo(template);
var stream = new MemoryStream();
using (var package = new ExcelPackage(templateFile, false))
{
var workbook = package.Workbook;
var worksheet = package.Workbook.Worksheets[1];
var fields = MyRow.Fields;
string[] idAll = request.IdExport[0].Split(new char[] { ',' });
int idxData = 1;
int idxCol = 2;
foreach (string id in idAll)
{
HijrahquDataContext ctx = new HijrahquDataContext();
List <ViewOrder> order = ctx.ViewOrders.Where(x => x.OrderID == int.Parse(id)).ToList();
//string cells = "";
if (order.Count > 0)
{
worksheet.Cells["A" + idxCol.ToString()].Value = order[0].OrderID.ToString();
worksheet.Cells["B" + idxCol.ToString()].Value = order[0].CustomerName;
worksheet.Cells["C" + idxCol.ToString()].Value = order[0].Address;
//worksheet.Cells["D" + idxCol.ToString()].Value = "JAWA BARAT";
worksheet.Cells["E" + idxCol.ToString()].Value = order[0].District;
worksheet.Cells["F" + idxCol.ToString()].Value = order[0].City;
worksheet.Cells["G" + idxCol.ToString()].Value = order[0].Province;
//worksheet.Cells["H" + idxCol.ToString()].Value = order[0].CustomerName;
worksheet.Cells["I" + idxCol.ToString()].Value = order[0].Handphone;
//PROVINCE
worksheet.Cells["J" + idxCol.ToString()].Value = order[0].KodePos;
//CITY
worksheet.Cells["K" + idxCol.ToString()].Value = (DateTime.Now.AddDays(1)).ToString("yyyy-MM-dd");
//COUNTRY
//worksheet.Cells["L" + idxCol.ToString()].Value = "INDONESIA";
worksheet.Cells["M" + idxCol.ToString()].Value = 1;
worksheet.Cells["N" + idxCol.ToString()].Value = "Standard";
worksheet.Cells["O" + idxCol.ToString()].Value = order[0].OrderID.ToString();
string keterangan = order[0].Keterangan;
string intruksi = "UNTUK KURIR MOHON AMANAH DALAM MELAKUKAN PENGANTARAN (HARAP LAKUKAN SESUAI SOP)";
keterangan += intruksi;
worksheet.Cells["P" + idxCol.ToString()].Value = keterangan;
worksheet.Cells["Q" + idxCol.ToString()].Value = "TRUE";
worksheet.Cells["R" + idxCol.ToString()].Value = order[0].TotalBayar.ToString();
idxData++;
idxCol++;
}
}
return(ExcelContentResult.Create(package.GetAsByteArray(), filename));
}
}
public FileContentResult ListExcel(IDbConnection connection, TransactionListRequest request)
{
var data = List(connection, request).Entities;
var report = new DynamicDataReport(data, request.IncludeColumns, typeof(Columns.TransactionColumns));
var bytes = new ReportRepository().Render(report);
return(ExcelContentResult.Create(bytes, "TransactionList_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx"));
}
public FileContentResult ListExcel(IDbConnection connection, ListRequest request)
{
var data = List(connection, request).Entities;
var report = new DynamicDataReport(data, request.IncludeColumns, typeof(Columns.ConcessioneColumns), HttpContext.RequestServices);
var bytes = ReportRepository.Render(report);
return(ExcelContentResult.Create(bytes, "Concessioni_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx"));
}
//public ListResponse<MyRow> List(IDbConnection connection, ListRequest request)
//{
// ListResponse<MyRow> rr = new ListResponse<MyRow>();
// //rr.Entities = (List<MyRow>)connection.Query<MyRow>("InsCampaignDay", commandType: CommandType.StoredProcedure);
// rr.Entities = connection.Query<MyRow>("InsCampaignDay", commandType: CommandType.StoredProcedure).ToList();
// connection.Execute("InsDailyVaccinations", commandType: CommandType.StoredProcedure);
// connection.Execute("DelTempCampaign", commandType: CommandType.StoredProcedure);
// return rr;
//}
public FileContentResult ListExcel(IDbConnection connection, ListRequest request)
{
var data = List(connection, request).Entities;
var report = new Serenity.Reporting.DynamicDataReport(data, request.IncludeColumns, typeof(Columns.TempCampaignColumns));
var bytes = new ReportRepository().Render(report);
return(ExcelContentResult.Create(bytes, "TempCampaignOPV_" +
System.DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx"));
}
public FileContentResult ListExcel(IDbConnection connection, ListRequest request,
[FromServices] IExcelExporter exporter)
{
var data = List(connection, request).Entities;
var bytes = exporter.Export(data, typeof(Columns.ProductColumns), request.ExportColumns);
return(ExcelContentResult.Create(bytes, "ProductList_" +
DateTime.Now.ToString("yyyyMMdd_HHmmss", CultureInfo.InvariantCulture) + ".xlsx"));
}
public FileContentResult ListExcel(IDbConnection connection, ListRequest request)
{
var data = List(connection, request).Entities;
var report = new DynamicDataReport(data, request.IncludeColumns, typeof(Columns.TitleColumns));
var bytes = new ReportRepository().Render(report);
var reportName = $"{GetType().Name.Replace("Controller", string.Empty)}_";
return(ExcelContentResult.Create(bytes, reportName + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx"));
}
public FileContentResult ListExcel(IDbConnection connection, ListRequest request)
{
var data = List(connection, request).Entities;
var cols = request.IncludeColumns.Where(p => p != MyRow.Fields.ReusedCount.Name);
var report = new DynamicDataReport(data, cols, typeof(Columns.IdCardColumns));
var bytes = new ReportRepository().Render(report);
return(ExcelContentResult.Create(bytes, "IdCard_" +
DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx"));
}
/// <summary>
/// Lists the excel.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="request">The request.</param>
/// <returns>Excel file</returns>
public FileContentResult ListExcel(IDbConnection connection, ListRequest request)
{
var data = List(connection, request).Entities;
var report = new DynamicDataReport(data, request.IncludeColumns,
typeof(GraduateThesisManager.ModuleCourses.Columns.ModulesColumns));
var bytes = new ReportRepository().Render(report);
return(ExcelContentResult.Create(bytes, "ModulesList_" +
DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx"));
}
public FileContentResult ExcelTemplate(IDbConnection connection, ListRequest request)
{
FileInfo file = new FileInfo(Server.MapPath("~/ExcelTemplate/IdCardTemplate.xlsx"));
using (FileStream fs = file.OpenRead())
{
BinaryReader r = new BinaryReader(fs);
r.BaseStream.Seek(0, SeekOrigin.Begin); //将文件指针设置到文件开
byte[] bytes = r.ReadBytes((int)r.BaseStream.Length);
return(ExcelContentResult.Create(bytes, "IdCardTemplate.xlsx"));
}
}
public FileContentResult ExportDataJX(IDbConnection connection, ExcelMassUploadImportRequest request)
{
request.CheckNotNull();
if (request.IdExport == null)
{
throw new ValidationError("Batch Id Cannot NULL");
}
var template = Server.MapPath(ConfigurationManager.AppSettings["TEMPLATEJX"].ToString());
var filename = "JXAWB_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx";
var templateFile = new System.IO.FileInfo(template);
var stream = new MemoryStream();
using (var package = new ExcelPackage(templateFile, false))
{
var workbook = package.Workbook;
var worksheet = package.Workbook.Worksheets[1];
var fields = MyRow.Fields;
string[] idAll = request.IdExport[0].Split(new char[] { ',' });
int idxData = 1;
int idxCol = 2;
foreach (string id in idAll)
{
HijrahquDataContext ctx = new HijrahquDataContext();
List <ViewOrder> order = ctx.ViewOrders.Where(x => x.OrderID == int.Parse(id)).ToList();
//string cells = "";
worksheet.Cells["A" + idxCol.ToString()].Value = "#" + order[0].OrderID.ToString();
worksheet.Cells["B" + idxCol.ToString()].Value = order[0].NamaPengirim;
worksheet.Cells["C" + idxCol.ToString()].Value = order[0].NoPengirim;
worksheet.Cells["D" + idxCol.ToString()].Value = "JAWA BARAT";
worksheet.Cells["E" + idxCol.ToString()].Value = "BEKASI";
worksheet.Cells["F" + idxCol.ToString()].Value = "INDONESIA";
worksheet.Cells["G" + idxCol.ToString()].Value = "RUKO HIJRAHQU, JALAN RAYA KARANG SATRIA TAMBUN UTARA BEKASI";
worksheet.Cells["H" + idxCol.ToString()].Value = order[0].CustomerName;
worksheet.Cells["I" + idxCol.ToString()].Value = order[0].Handphone;
//PROVINCE
//worksheet.Cells["J" + idxCol.ToString()].Value = order[0].TanggalWa;
//CITY
//worksheet.Cells["K" + idxCol.ToString()].Value = order[0].TanggalClosing;
//COUNTRY
worksheet.Cells["L" + idxCol.ToString()].Value = "INDONESIA";
worksheet.Cells["M" + idxCol.ToString()].Value = order[0].Address;
worksheet.Cells["N" + idxCol.ToString()].Value = "PAKET BUKU";
worksheet.Cells["O" + idxCol.ToString()].Value = "1";
worksheet.Cells["P" + idxCol.ToString()].Value = "1";
worksheet.Cells["Q" + idxCol.ToString()].Value = "COD";
worksheet.Cells["R" + idxCol.ToString()].Value = "COD";
idxData++;
idxCol++;
}
return(ExcelContentResult.Create(package.GetAsByteArray(), filename));
}
}
public ActionResult GetRequestFile(string status)
{
var req = new ListRequest();
req.EqualityFilter = new Dictionary <string, object>();
req.EqualityFilter.Add("Submit", status);
using (var connection = SqlConnections.NewByKey("Default"))
{
var spsHelper = new SpreedSheetHelper(Server.MapPath("~/Content/templates/export/result/ais/CapNhatPhongBanUser.xlsx"));
var stream = spsHelper.ExportXls <MyRow>(new MyController().List(connection, req).Entities);
return(ExcelContentResult.Create(stream.GetBuffer()));
}
}
public FileContentResult LaporanPenjualan(IDbConnection connection, ExcelMassUploadImportRequest request)
{
request.CheckNotNull();
if (request.IdExport == null)
{
throw new ValidationError("Batch Id Cannot NULL");
}
var template = Server.MapPath(ConfigurationManager.AppSettings["LapPenjualan"].ToString());
var filename = "LapPenjualan_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx";
var templateFile = new System.IO.FileInfo(template);
var stream = new MemoryStream();
using (var package = new ExcelPackage(templateFile, false))
{
var workbook = package.Workbook;
var worksheet = package.Workbook.Worksheets[1];
var fields = MyRow.Fields;
string[] idAll = request.IdExport[0].Split(new char[] { ',' });
int idxData = 1;
int idxCol = 4;
foreach (string id in idAll)
{
HijrahquDataContext ctx = new HijrahquDataContext();
List <ViewLapPenjualan> order = ctx.ViewLapPenjualans.Where(x => x.OrderID == int.Parse(id)).ToList();
//string cells = "";
worksheet.Cells["B" + idxCol.ToString()].Value = idxData.ToString();
worksheet.Cells["C" + idxCol.ToString()].Value = "#" + order[0].OrderID.ToString();
worksheet.Cells["D" + idxCol.ToString()].Value = order[0].CustomerName;
worksheet.Cells["E" + idxCol.ToString()].Value = order[0].Handphone;
worksheet.Cells["F" + idxCol.ToString()].Value = "1";
worksheet.Cells["G" + idxCol.ToString()].Value = "1";
worksheet.Cells["H" + idxCol.ToString()].Value = "1";
worksheet.Cells["I" + idxCol.ToString()].Value = "1";
worksheet.Cells["J" + idxCol.ToString()].Value = order[0].Ongkir;
worksheet.Cells["K" + idxCol.ToString()].Value = 125000 + order[0].Ongkir;
worksheet.Cells["L" + idxCol.ToString()].Value = order[0].TanggalWa;
worksheet.Cells["M" + idxCol.ToString()].Value = order[0].TanggalClosing;
worksheet.Cells["N" + idxCol.ToString()].Value = order[0].TanggalKirim;
worksheet.Cells["O" + idxCol.ToString()].Value = order[0].TanggalBayar;
worksheet.Cells["P" + idxCol.ToString()].Value = order[0].TanggalCS;
idxData++;
idxCol++;
}
return(ExcelContentResult.Create(package.GetAsByteArray(), filename));
}
}
public FileContentResult CreateJOB(IDbConnection connection, ExcelMassUploadImportRequest request)
{
request.CheckNotNull();
if (request.IdExport == null)
{
throw new ValidationError("Batch Id Cannot NULL");
}
var template = Server.MapPath(ConfigurationManager.AppSettings["JOBTemplate"].ToString());
var filename = "JOB_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx";
var templateFile = new System.IO.FileInfo(template);
var stream = new MemoryStream();
using (var package = new ExcelPackage(templateFile, false))
{
var workbook = package.Workbook;
var worksheet = package.Workbook.Worksheets[1];
var fields = MyRow.Fields;
string[] idAll = request.IdExport[0].Split(new char[] { ',' });
int idxData = 1;
int idxCol = 2;
foreach (string id in idAll)
{
HijrahquDataContext ctx = new HijrahquDataContext();
List <ViewOrder> order = ctx.ViewOrders.Where(x => x.OrderID == int.Parse(id)).ToList();
//string cells = "";
worksheet.Cells["A" + idxCol.ToString()].Value = order[0].NamaPengirim;
worksheet.Cells["B" + idxCol.ToString()].Value = "TAMBUN UTARA, BEKASI";
worksheet.Cells["E" + idxCol.ToString()].Value = order[0].NamaPengirim;
worksheet.Cells["F" + idxCol.ToString()].Value = "0877-8090-1125";
worksheet.Cells["G" + idxCol.ToString()].Value = order[0].CustomerName;
worksheet.Cells["H" + idxCol.ToString()].Value = order[0].Address;
worksheet.Cells["I" + idxCol.ToString()].Value = order[0].CustomerName;
worksheet.Cells["L" + idxCol.ToString()].Value = order[0].CustomerName;
worksheet.Cells["M" + idxCol.ToString()].Value = order[0].Handphone;
worksheet.Cells["N" + idxCol.ToString()].Value = "1";
worksheet.Cells["O" + idxCol.ToString()].Value = "1";
worksheet.Cells["P" + idxCol.ToString()].Value = "BUKU SYAMAIL MUHAMMADIYAH";
idxData++;
idxCol++;
}
return(ExcelContentResult.Create(package.GetAsByteArray(), filename));
}
}
public FileContentResult Printlabel(IUnitOfWork uow, ExcelMassUploadImportRequest request)
{
request.CheckNotNull();
if (request.IdExport == null)
{
throw new ValidationError("Batch Id Cannot NULL");
}
var template = Server.MapPath(ConfigurationManager.AppSettings["LabelTemplate"].ToString());
var filename = "CustomerList_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx";
var templateFile = new System.IO.FileInfo(template);
var stream = new MemoryStream();
using (var package = new ExcelPackage(templateFile, false))
{
var workbook = package.Workbook;
var worksheet = package.Workbook.Worksheets[1];
var fields = MyRow.Fields;
string[] idAll = request.IdExport[0].Split(new char[] { ',' });
int idxData = 1;
int idxCol = 2;
foreach (string id in idAll)
{
RetrieveRequest retReq = new RetrieveRequest();
retReq.EntityId = id;
var ord = new OrderRepository().Retrieve(uow.Connection, retReq);
HijrahquDataContext ctx = new HijrahquDataContext();
List <ViewOrder> order = ctx.ViewOrders.Where(x => x.OrderID == int.Parse(id)).ToList();//int.Parse("15")).ToList();
string cells = "";
if (order[0].OrderStatus == "NewOrder")
{
if (idxData == 1)
{
cells = "D";
worksheet.Cells[cells + "6"].Value = order[0].CustomerName;
worksheet.Cells[cells + "7"].Value = order[0].Handphone;
worksheet.Cells[cells + "8"].Value = order[0].Address;
worksheet.Cells["H3"].Value = "#" + order[0].OrderID.ToString();
}
;
if (idxData == 2)
{
cells = "D";
worksheet.Cells[cells + "21"].Value = order[0].CustomerName;
worksheet.Cells[cells + "22"].Value = order[0].Handphone;
worksheet.Cells[cells + "23"].Value = order[0].Address;
worksheet.Cells["H18"].Value = "#" + order[0].OrderID.ToString();
}
;
if (idxData == 3)
{
cells = "D";
worksheet.Cells[cells + "36"].Value = order[0].CustomerName;
worksheet.Cells[cells + "37"].Value = order[0].Handphone;
worksheet.Cells[cells + "38"].Value = order[0].Address;
worksheet.Cells["H33"].Value = "#" + order[0].OrderID.ToString();
}
;
if (idxData == 4)
{
cells = "D";
worksheet.Cells[cells + "51"].Value = order[0].CustomerName;
worksheet.Cells[cells + "52"].Value = order[0].Handphone;
worksheet.Cells[cells + "53"].Value = order[0].Address;
worksheet.Cells["H48"].Value = "#" + order[0].OrderID.ToString();
}
;
if (idxData == 5)
{
cells = "K";
worksheet.Cells[cells + "6"].Value = order[0].CustomerName;
worksheet.Cells[cells + "7"].Value = order[0].Handphone;
worksheet.Cells[cells + "8"].Value = order[0].Address;
worksheet.Cells["O3"].Value = "#" + order[0].OrderID.ToString();
}
;
if (idxData == 6)
{
cells = "K";
worksheet.Cells[cells + "21"].Value = order[0].CustomerName;
worksheet.Cells[cells + "22"].Value = order[0].Handphone;
worksheet.Cells[cells + "23"].Value = order[0].Address;
worksheet.Cells["O18"].Value = "#" + order[0].OrderID.ToString();
}
;
if (idxData == 7)
{
cells = "K";
worksheet.Cells[cells + "36"].Value = order[0].CustomerName;
worksheet.Cells[cells + "37"].Value = order[0].Handphone;
worksheet.Cells[cells + "38"].Value = order[0].Address;
worksheet.Cells["O33"].Value = "#" + order[0].OrderID.ToString();
}
;
if (idxData == 8)
{
cells = "K";
worksheet.Cells[cells + "51"].Value = order[0].CustomerName;
worksheet.Cells[cells + "52"].Value = order[0].Handphone;
worksheet.Cells[cells + "53"].Value = order[0].Address;
worksheet.Cells["O48"].Value = "#" + order[0].OrderID.ToString();
}
;
ord.Entity.OrderStatus = "Printed";
new OrderRepository().Update(uow, new SaveRequest <MyRow> {
Entity = ord.Entity, EntityId = ord.Entity.OrderId
});
}
idxData++;
idxCol++;
}
return(ExcelContentResult.Create(package.GetAsByteArray(), filename));
}
}