private void btnProductReturns_Click(object sender, EventArgs e)
{
DateTime startDate = dtePRStartDate.SelectedDate.Value.Date;
DateTime endDate = dtePREndDate.SelectedDate.Value.Date.Add(new TimeSpan(23, 59, 59));
Facade.IKPI facKPI = new Orchestrator.Facade.KPI();
DataSet dsSource = facKPI.ProductReturns(startDate, endDate);
CsvExport.Export(
dsSource.Tables[0],
"ProductReturns.csv",
new CsvExport.ColumnMappings
{
{ "OriginalCollectionPoint", "Despatch Point" },
{ "OriginalDeliveryOrderNumber", "Docket Number" },
{ "ReturnDeliveryDateTime", "Return Date", this.DateFormatter },
{ "RefusalType", "Refusal Type" },
{ "RefusalReceiptNumber", "Refusal Receipt Number" },
{ "RefusalNotes", "Refusal Notes" },
{ "ProductCode", "Product Code" },
{ "ProductName", "Product Name" },
{ "PackSize", "Pack Size" },
{ "QuantityRefused", "Quantity Refused" },
{ "ReturnDeliveryPoint", "Return To" },
{ "ReturnOrderRate", "ReturnOrderRate" },
});
}
public FileContentResult Reflection(string model)
{
List <WidgetBeta> listBetas = null;
List <WidgetGamma> listGammas = null;
List <WidgetPrime> listPrimes = null;
WidgetPrime singleSource = null;
WidgetBeta singleTarget = null;
listBetas = PopulateBeta();
listGammas = PopulateGamma();
listPrimes = PopulatePrime();// new List<WidgetPrime>();
for (int i = 0; i < listPrimes.Count; i++)
{
singleSource = listPrimes[i];
singleTarget = new WidgetBeta();
singleTarget = (WidgetBeta)TransferValues(singleSource, singleTarget);
listBetas.Add(singleTarget);
}
CsvExport <WidgetBeta> csv = new CsvExport <WidgetBeta>(listBetas);
return(File(new System.Text.UTF8Encoding().GetBytes(csv.Export()), "text/csv", "test.csv"));
// return RedirectToAction("Reflection");
}
public static string MakeCSV(List <CapturePacket> packets)
{
var csvExport = new CsvExport();
foreach (CapturePacket packet in packets)
{
csvExport.AddRow();
csvExport["Time"] = packet.Date; // + "." + l.Time.Millisecond;
csvExport["ms"] = packet.Date.Millisecond;
csvExport["Name"] = packet.Name;
if (packet.ParsedData != null)
{
csvExport["Data"] = Functions.MakeCommentString(packet.ParsedData.GetDataDictionary());
}
csvExport["Raw"] = BitConverter.ToString(packet.GetRawData());
}
string export = csvExport.Export();
return(export);
}
public void CsvExport(string path)
{
try {
var coffeeLogExport = new CsvExport();
coffeeLogExport.AddRow();
coffeeLogExport["Region"] = "Los Angeles, USA";
coffeeLogExport["Sales"] = 100000;
coffeeLogExport["Date Opened"] = new DateTime(2003, 12, 31);
coffeeLogExport.AddRow();
coffeeLogExport["Region"] = "Canberra \"in\" Australia";
coffeeLogExport["Sales"] = 50000;
coffeeLogExport["Date Opened"] = new DateTime(2005, 1, 1, 9, 30, 0);
coffeeLogExport.ExportToFile(path);
string myCsv = coffeeLogExport.Export();
byte[] myCsvData = coffeeLogExport.ExportToBytes();
// File(myExport.ExportToBytes(), "text/csv", "results.csv");
} catch (Exception ex) {
Console.WriteLine(String.Format("Error while Export CSV {0}", ex));
}
}
private void btnGenerateCsv_Click(object sender, EventArgs e)
{
if (!Page.IsValid)
{
return;
}
using (var uow = DIContainer.CreateUnitOfWork())
{
var repo = DIContainer.CreateRepository <IProfitReportRunRepository>(uow);
var filter = this.GetFilter();
var fileName = "Profitability.csv";
switch (filter.ReportInstance)
{
case ReportInstanceEnum.ByPlanner:
CsvExport.Export(
repo.GetReportDataByPlanner(filter.FromDate, filter.ToDate, string.Join(",", filter.PlannerIDs)),
fileName,
new CsvExport.PropertyMappings <Orchestrator.Repositories.DTOs.ProfitabilityReport.ByPlannerRow>
{
{ "Planner", i => i.PlannerName },
{ "Driver", i => i.VehicleRegistration },
{ "PayrollNo", i => i.PayrollNo },
{ "Revenue", i => i.Revenue.ToString("f2") },
{ "Cost", i => i.Cost.ToString("f2") },
{ "Profit", i => i.Profit.ToString("f2") },
{ "Margin", i => i.Margin.ToString("p2") },
{ "Single Delivery Count", i => i.SingleDeliveryCount.ToString() },
{ "Multiple Delivery Count", i => i.MultipleDeliveryCount.ToString() },
}
);
break;
case ReportInstanceEnum.BySingleDriver:
CsvExport.Export(
repo.GetReportDataByPlannerSingleDriver(filter.FromDate, filter.ToDate, filter.DriverIdentityID.Value),
fileName,
new CsvExport.PropertyMappings <Orchestrator.Repositories.DTOs.ProfitabilityReport.ByPlannerSinglePlannerRow>
{
{ "Planner", i => i.PlannerName },
{ "Vehicle", i => i.VehicleRegistration },
{ "Driver", i => i.DriverName },
{ "Revenue", i => i.Revenue.ToString("f2") },
{ "Cost", i => i.Cost.ToString("f2") },
{ "Profit", i => i.Profit.ToString("f2") },
{ "Margin", i => i.Margin.ToString("p2") },
{ "Single Delivery Count", i => i.SingleDeliveryCount.ToString() },
{ "Multiple Delivery Count", i => i.MultipleDeliveryCount.ToString() },
}
);
break;
}
}
}
private void exportButton_Click(object sender, EventArgs e)
{
if (!Page.IsValid)
{
return;
}
var customerIdentityID = Utilities.ParseNullable <int>(clientCombo.SelectedValue);
var fromDate = this.startDate.SelectedDate.Value.Date;
var toDate = this.endDate.SelectedDate.Value.Date;
var businessTypeIDs = this.businessTypeCheckList.AllBusinessTypesSelected ? null : this.businessTypeCheckList.SelectedBusinessTypeIDs;
var subcontractorIdentityID = Utilities.ParseNullable <int>(this.cboSubcontractor.SelectedValue);
var isInvoiced = this.rblInvoiced.SelectedIndex == 1 ? false : this.rblInvoiced.SelectedIndex == 2 ? true : (bool?)null;
var hasSubcontractorInvoice = this.rblSubcontractorInvoiced.SelectedIndex == 1 ? false : this.rblInvoiced.SelectedIndex == 2 ? true : (bool?)null;
Facade.IOrder facOrder = new Facade.Order();
var dtSource = facOrder.GetOrderListForExport(customerIdentityID, fromDate, toDate, businessTypeIDs, subcontractorIdentityID, isInvoiced, hasSubcontractorInvoice).Tables[0];
if (dtSource.Rows.Count == 0)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "NoData", "$(function() { alert('No matching orders were found'); });", true);
return;
}
Func <DataRow, string, string> dateFormatter = (dr, col) => dr.Field <DateTime>(col).ToShortDateString();
Func <DataRow, string, string> nullableCurrencyFormatter = (dr, col) => string.Format("{0:n2}", dr.Field <decimal?>(col));
Func <DataRow, string, string> nullableIntFormatter = (dr, col) => string.Format("{0}", dr.Field <int?>(col));
CsvExport.Export(
dtSource,
"Orders.csv",
new CsvExport.ColumnMappings
{
{ "OrderID", "Order ID" },
{ "CustomerName", "Customer" },
{ "DeliveryOrderNumber", "Docket number" },
{ "CustomerOrderNumber", "Load number" },
{ "JobID", "Run ID", nullableIntFormatter },
{ "BusinessType", "Business Type" },
{ "CollectionPoint", "Collection point" },
{ "CollectionPostcode", "Collection postcode" },
{ "DeliveryPoint", "Delivery point" },
{ "DeliveryPostcode", "Delivery postcode" },
{ "PalletSpaces", "Pallet spaces" },
{ "Weight", "Weight" },
{ "SubcontractorRate", "Subcontractor cost", nullableCurrencyFormatter },
{ "ForeignRateIncludingFuelSurcharge", "Total revenue" },
{ "ExtrasForeignTotal", "Extras" },
{ "FuelSurchargeForeignAmount", "Fuel surcharge" },
{ "ForeignRate", "Revenue" },
{ "CollectionDateTime", "Collection date", dateFormatter },
{ "DeliveryDateTime", "Delivery date", dateFormatter },
{ "SubcontractorName", "Subcontractor" },
{ "OrderStatus", "Order status" },
{ "HasSubcontractorInvoice", "Has subcontractor invoice?", (dr, col) => dr.Field <bool>(col) ? "Yes" : "No" },
});
}
public void TestMethod1()
{
TestDataGenerator tg = new TestDataGenerator();
tg.createCatsClassesGroups();
Race race = tg.Model.GetRace(0);
tg.createRaceParticipant(cat: tg.findCat('M'), cla: tg.findClass("2M (2010)"), points: 1.0); // 1
tg.createRaceParticipant(cat: tg.findCat('M'), cla: tg.findClass("2M (2010)"), points: 2.0);
tg.createRaceParticipant(cat: tg.findCat('M'), cla: tg.findClass("2M (2010)"), points: 3.0);
tg.createRaceParticipant(cat: tg.findCat('W'), cla: tg.findClass("2W (2010)"), points: 1.5); // 4
tg.createRaceParticipant(cat: tg.findCat('W'), cla: tg.findClass("2W (2010)"), points: 2.5);
tg.createRaceParticipant(cat: tg.findCat('W'), cla: tg.findClass("2W (2010)"), points: 3.5); // 6
RaceRun rr1 = race.GetRun(0);
RaceRun rr2 = race.GetRun(1);
rr1.SetStartFinishTime(race.GetParticipant(1), new TimeSpan(8, 0, 0), new TimeSpan(8, 1, 0));
rr2.SetRunTime(race.GetParticipant(1), new TimeSpan(0, 0, 2, 0, 123));
rr1.SetRunTime(race.GetParticipant(2), new TimeSpan(0, 1, 1));
rr1.SetResultCode(race.GetParticipant(3), RunResult.EResultCode.NiZ);
Export export = new Export(tg.Model.GetRace(0));
DataSet ds = export.ExportToDataSet();
Assert.AreEqual("Name 1", ds.Tables[0].Rows[0]["Name"]);
Assert.AreEqual("Name 2", ds.Tables[0].Rows[1]["Name"]);
Assert.AreEqual("Name 3", ds.Tables[0].Rows[2]["Name"]);
Assert.AreEqual("Name 4", ds.Tables[0].Rows[3]["Name"]);
Assert.AreEqual("Name 5", ds.Tables[0].Rows[4]["Name"]);
Assert.AreEqual("Name 6", ds.Tables[0].Rows[5]["Name"]);
Assert.AreEqual("Firstname 1", ds.Tables[0].Rows[0]["Firstname"]);
Assert.AreEqual(1.0, ds.Tables[0].Rows[0]["Points"]);
Assert.AreEqual(new TimeSpan(0, 1, 0), ds.Tables[0].Rows[0]["Runtime_1"]);
Assert.AreEqual("Normal", ds.Tables[0].Rows[0]["Resultcode_1"]);
Assert.AreEqual(new TimeSpan(0, 0, 2, 0, 120), ds.Tables[0].Rows[0]["Runtime_2"]);
Assert.AreEqual("Normal", ds.Tables[0].Rows[0]["Resultcode_2"]);
Assert.AreEqual("NiZ", ds.Tables[0].Rows[2]["Resultcode_1"]);
var excelExport = new ExcelExport();
excelExport.Export(@"c:\trash\test.xlsx", ds);
var csvExport = new CsvExport();
csvExport.Export(@"c:\trash\test.csv", ds, true);
var tsvExport = new TsvExport();
tsvExport.Export(@"c:\trash\test.txt", ds, true);
}
/// <summary>
/// Download the last saved search from App_Data/SearchResult.csv
/// </summary>
/// <returns></returns>
public ActionResult SaveToCsv()
{
var lastSearchResults = GetLastSearch();
var csvExporter = new CsvExport();
csvExporter.AddRows <ISearchResult>(lastSearchResults);
var resultsAsCsv = csvExporter.Export();
return(File(new System.Text.UTF8Encoding().GetBytes(resultsAsCsv), "text/csv", "SearchResult.csv"));
}
private void btnGenerateCsv_Click(object sender, EventArgs e)
{
if (!Page.IsValid)
{
return;
}
int depotId = 0;
int.TryParse(cboDepot.SelectedValue, out depotId);
int vehicleId = 0;
int.TryParse(cboVehicle.SelectedValue, out vehicleId);
RevenuePerVehicleReportData reportData;
Facade.IJob facJob = new Facade.Job();
if (depotId == 0 && vehicleId == 0) //All Depots - All Vehicles
{
reportData = facJob.GetRevenuePerVehicleData(dteStartDate.SelectedDate.Value,
dteEndDate.SelectedDate.Value);
}
else if (depotId != 0 && vehicleId == 0) //Single Depot - All Vehicle
{
reportData = facJob.GetRevenuePerVehicleData(dteStartDate.SelectedDate.Value,
dteEndDate.SelectedDate.Value, depotId);
}
else //Doesn't Matter - Single Vehicle
{
reportData = facJob.GetRevenuePerVehicleData(dteStartDate.SelectedDate.Value,
dteEndDate.SelectedDate.Value, depotId, vehicleId);
}
var vehicles = reportData.Depots.SelectMany(dptSummary => dptSummary.Vehicles);
List <CsvExport.PropertyMapping <VehicleRevenue> > mappings = new List <CsvExport.PropertyMapping <VehicleRevenue> >();
if (vehicleId == 0)
{
mappings.Add(new CsvExport.PropertyMapping <VehicleRevenue>("Depot", t => t.DepotName));
mappings.Add(new CsvExport.PropertyMapping <VehicleRevenue>("Vehicle", t => t.VehicleName));
}
else
{
mappings.Add(new CsvExport.PropertyMapping <VehicleRevenue>("Vehicle", t => t.DepotName));
mappings.Add(new CsvExport.PropertyMapping <VehicleRevenue>("Driver", t => t.VehicleName));
}
mappings.Add(new CsvExport.PropertyMapping <VehicleRevenue>("Revenue", t => t.Revenue_Formatted));
mappings.Add(new CsvExport.PropertyMapping <VehicleRevenue>("Percent Of Total", t => t.PercentOfDepotTotal_Formatted));
CsvExport.Export <VehicleRevenue>(vehicles, "RevenuePerVehicleReport.csv", mappings);
}
private void BtnExportCsv_Click(object sender, RoutedEventArgs e)
{
exportToTextFile
("Comma Separated Text File (.csv)|*.csv|" +
"Comma Separated Text File - UTF-8 (.csv)|*.csv"
, ".csv",
(Race race, string filePath, bool utf8) =>
{
Export exp = new Export(race);
CsvExport csvExp = new CsvExport();
csvExp.Export(filePath, exp.ExportToDataSet(), utf8);
}
);
}
public static async void Write(List<Product> products, string path)
{
var csvExport = new CsvExport();
csvExport.AddRows(products.Select(x => new
{
x.Model,
x.Brand,
x.DeviceType,
x.AdditionalComments
}));
using (var streamWriter = new StreamWriter(path, false, Encoding.UTF8))
{
await streamWriter.WriteAsync(csvExport.Export());
await streamWriter.FlushAsync();
}
}
public override void ExecuteResult(ControllerContext context)
{
if (context == null)
{
throw new ArgumentNullException("context");
}
HttpResponseBase response = context.HttpContext.Response;
response.ContentType = "text/csv";
response.AddHeader("Content-Disposition", "attachment; filename=file.csv"));
if (data!= null)
{
response.Write(data.Export());
}
}
private void btnFactoryClearance_Click(object sender, EventArgs e)
{
DateTime startDate = dteFCStartDate.SelectedDate.Value.Date;
DateTime endDate = dteFCEndDate.SelectedDate.Value.Date.Add(new TimeSpan(23, 59, 59));
Facade.IKPI facKPI = new Orchestrator.Facade.KPI();
DataSet dsSource = facKPI.FactoryClearance(startDate, endDate);
CsvExport.Export(
dsSource.Tables[0],
"FactoryClearance.csv",
new CsvExport.ColumnMappings
{
{ "MemberName", "Member" },
{ "CollectionPoint", "Collection Point" },
{ "LoadCount", "Load Count" },
});
}
private void btnCollectionPerformance_Click(object sender, EventArgs e)
{
DateTime startDate = dteCPStartDate.SelectedDate.Value.Date;
DateTime endDate = dteCPEndDate.SelectedDate.Value.Date.Add(new TimeSpan(23, 59, 59));
Facade.IKPI facKPI = new Facade.KPI();
DataSet dsSource = facKPI.CollectDropPerformance(startDate, endDate, eInstructionType.Load);
CsvExport.Export(
dsSource.Tables[0],
"CollectionPerformance.csv",
new CsvExport.ColumnMappings
{
{ "MemberName", "Member" },
{ "TargetOnTimeProportion", "Target On-Time", this.PercentageFormatter },
{ "OnTimeProportion", "Actual On-Time", this.PercentageFormatter },
});
}
private void btnCapacityUsage_Click(object sender, EventArgs e)
{
DateTime startDate = dteCUStartDate.SelectedDate.Value.Date;
DateTime endDate = dteCUEndDate.SelectedDate.Value.Date.Add(new TimeSpan(23, 59, 59));
Facade.IKPI facKPI = new Facade.KPI();
DataSet dsSource = facKPI.CapacityUsage(startDate, endDate);
CsvExport.Export(
dsSource.Tables[0],
"CapacityUsage.csv",
new CsvExport.ColumnMappings
{
{ "OrganisationName", "Client" },
{ "NumberOfLoads", "Number of Loads" },
{ "CapacityUsage", "Weight Percentage of Capacity", this.PercentageFormatter },
});
}
private void btnOutstandingPods_Click(object sender, EventArgs e)
{
DateTime startDate = dtePODStartDate.SelectedDate.Value.Date;
DateTime endDate = dtePODEndDate.SelectedDate.Value.Date.Add(new TimeSpan(23, 59, 59));
bool isDetailReport = rblPODReportType.SelectedIndex == 1;
Facade.IKPI facKPI = new Facade.KPI();
if (isDetailReport)
{
DataSet dsSource = facKPI.OutstandingPODDetail(startDate, endDate);
CsvExport.Export(
dsSource.Tables[0],
"OutstandingPODDetail.csv",
new CsvExport.ColumnMappings
{
{ "Client", "Client" },
{ "Member", "Member" },
{ "OrderID", "Order ID" },
{ "BusinessType", "Business Type" },
{ "DeliveryOrderNumber", "Docket Number" },
{ "CustomerOrderNumber", "Load Number" },
{ "DateTimeDelivered", "Date Delivered", this.DateFormatter },
{ "DeliveredInDateRange", "In Date Range?", (dr, col) => dr.Field <bool>(col) ? "Yes" : "No" },
});
}
else
{
DataSet dsSource = facKPI.OutstandingPODSummary(startDate, endDate);
CsvExport.Export(
dsSource.Tables[0],
"OutstandingPODSummary.csv",
new CsvExport.ColumnMappings
{
{ "Client", "Client" },
{ "Member", "Member" },
{ "BusinessType", "Business Type" },
{ "CountDeliveredInDateRange", "In Date Range" },
{ "CountDeliveredPriorToDateRange", "Prior To Date Range" },
});
}
}
private void btnOrderRefusals_Click(object sender, EventArgs e)
{
DateTime startDate = dteORStartDate.SelectedDate.Value.Date;
DateTime endDate = dteOREndDate.SelectedDate.Value.Date.Add(new TimeSpan(23, 59, 59));
Facade.IKPI facKPI = new Facade.KPI();
DataSet dsSource = facKPI.OrderRefusals(startDate, endDate);
CsvExport.Export(
dsSource.Tables[0],
"OrderRefusals.csv",
new CsvExport.ColumnMappings
{
{ "Total", "Total" },
{ "Haulier", "Haulier" },
{ "Premier", "Premier" },
{ "Customer", "Customer" },
{ "Other", "Other" },
});
}
public void ExportToFileTest()
{
var list = new List <TestData>();
string expected = string.Empty;
for (var x = 0; x <= 5; x++)
{
string name = Guid.NewGuid().ToString().Replace("-", "").Substring(10);
TestData data = new TestData()
{
asa = "asa" + x.ToString(),
Name = name.Substring(5),
Description = string.Format("{0},{1}", name.Substring(6), name.Substring(4))
};
list.Add(data);
}
CsvExport <TestData> csv = new CsvExport <TestData>(list);
expected = csv.Export(true);
string path = @"c:\ftp\test.csv";
if (File.Exists(path))
{
File.Delete(path);
}
if (!File.Exists(path))
{
//TextWriter tw = new StreamWriter( path );
//tw.WriteLine( expected );
using (StreamWriter sw = new StreamWriter(new FileStream(path, FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite)))
{
sw.WriteLine(expected);
}
}
File.OpenText(path);
Assert.AreNotEqual(string.Empty, expected);
}
public static async void Write(IEnumerable<ProductReview> reviews, string path)
{
var csvExport = new CsvExport();
csvExport.AddRows(reviews.Select(x => new
{
x.Reviewer,
x.ScoreMark,
x.Recommendation,
x.AddedOn,
x.ConsideredAsUsefulCount,
x.UsefulnessBaseCount,
ProductAdventages = string.Join("; ", x.ProductAdventages.Select(productAdventage => productAdventage.Feature)),
ProductDisadvantages = string.Join("; ", x.ProductDisadvantages.Select(productDisadvantage => productDisadvantage.Feature)),
x.Body
}));
using (var streamWriter = new StreamWriter(path, false, Encoding.UTF8))
{
await streamWriter.WriteAsync(csvExport.Export());
await streamWriter.FlushAsync();
}
}
//public CResult ReadByBranchAndDate(string BranchCode, DateTime FromDate, DateTime ToDate)
//{
// List<CGRDetails> oItemList = new List<CGRDetails>();
// oResult = new CResult();
// conn = oConnManager.GetConnection(out s_DBError);
// if (conn != null)
// {
// try
// {
// DataSet ds = new DataSet();
// SqlCommand cmd = new SqlCommand();
// cmd.Connection = conn;
// cmd.CommandText = "sp_GRDetail_ReadByBrnIdAndDate";
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.Clear();
// cmd.Parameters.AddWithValue("@BranchID", BranchCode);
// cmd.Parameters.AddWithValue("@SOMstr_DateFrom", FromDate);
// cmd.Parameters.AddWithValue("@SOMstr_DateTo", ToDate);
// SqlDataAdapter da = new SqlDataAdapter(cmd);
// da.Fill(ds);
// DataTable dtItem = ds.Tables[0];
// // oItemList.Add("");
// foreach (DataRow dr in dtItem.Rows)
// {
// CGRDetails oGRDetails = new CGRDetails();
// oGRDetails.GRDet_ItemOID = dr["GRDet_ItemOID"].ToString();
// oGRDetails.GRDet_QTY = float.Parse(dr["GRDet_QTY"].ToString());
// oGRDetails.GRDet_BranchOID = dr["GRDet_Branch"].ToString();
// oGRDetails.GRDet_Price = float.Parse(dr["GRDet_Price"].ToString());
// oGRDetails.GRDet_Amount = float.Parse(dr["GRDet_Amount"].ToString());
// oGRDetails.GRDet_ItemName = dr["GRDet_ItemName"].ToString();
// oItemList.Add(oGRDetails);
// }
// oResult.IsSuccess = true;
// oResult.Data = oItemList;
// }
// catch (SqlException e)
// {
// oResult.IsSuccess = false;
// oResult.ErrMsg = e.Message;
// }
// finally
// {
// oConnManager.Close();
// }
// }
// else
// {
// oResult.IsSuccess = false;
// oResult.ErrMsg = s_DBError;
// }
// return oResult;
//}
//public CResult ReduceByItemOID(List<CSODetails> oListSODetails)
//{
// oResult = new CResult();
// conn = oConnManager.GetConnection(out s_DBError);
// if (conn != null)
// {
// SqlCommand cmd = new SqlCommand();
// cmd.Connection = conn;
// cmd.Transaction = oConnManager.BeginTransaction();
// try
// {
// foreach (CSODetails oSODetails in oListSODetails)
// {
// cmd.CommandText = "sp_GRDetails_ReduceByItemOID";
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.Clear();
// cmd.Parameters.AddWithValue("@ItemOID", oSODetails.SODet_ItemOID);
// cmd.Parameters.AddWithValue("@ItemQTY", oSODetails.SODet_QTY);
// cmd.Parameters.AddWithValue("@BranchOID", oSODetails.SODet_BranchOID);
// cmd.ExecuteNonQuery();
// }
// oConnManager.Commit();
// oResult.IsSuccess = true;
// }
// catch (SqlException e)
// {
// string sRollbackError = oConnManager.Rollback();
// oResult.IsSuccess = false;
// oResult.ErrMsg = sRollbackError.Equals("") ? oConnManager.GetErrorMessage(e) : sRollbackError;
// }
// finally
// {
// oConnManager.Close();
// }
// }
// else
// {
// oResult.IsSuccess = false;
// oResult.ErrMsg = s_DBError;
// }
// return oResult;
//}
public CResult ExportAndUpdate(CGRMaster oMaster, string url)
{
CInventory oInventory = new CInventory();
CResult oResult = new CResult();
DataTable dt = new DataTable();
List <CInventory> oInvtList = new List <CInventory>();
List <CGRDetails> exportExcelList = new List <CGRDetails>();
oResult = new CResult();
conn = oConnManager.GetConnection(out s_DBError);
if (conn != null)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Parameters.Clear();
cmd.Transaction = oConnManager.BeginTransaction();
try
{
foreach (CGRDetails oDetails in oMaster.GRMstr_DetailsList)
{
oInventory = new CInventory();
oInventory.Invt_Branch = oDetails.GRDet_Branch;
oInventory.Invt_BranchOID = oDetails.GRDet_BranchOID;
oInventory.Invt_InvType = oDetails.GRDet_InvType;
oInventory.Invt_ItemOID = oDetails.GRDet_ItemOID;
oInventory.Invt_LocOID = oDetails.GRDet_LocOID;
oInventory.Invt_QTY = oDetails.GRDet_QTY;
oInvtList.Add(oInventory);
exportExcelList.Add(oDetails);
}
CsvExport <CGRDetails> csv = new CsvExport <CGRDetails>(exportExcelList);
csv.Export();
csv.ExportToFile(url);
// InventoryDT = ConvertToDataTable<CGRDetails>(exportExcelList);
CInventoryBO oInventoryBO = new CInventoryBO();
oResult = oInventoryBO.InvtDec(oInvtList);
if (oResult.IsSuccess)
{
oConnManager.Commit();
oResult.IsSuccess = true;
}
else
{
oResult.ErrMsg = oConnManager.Rollback();
oResult.IsSuccess = false;
}
}
catch (SqlException e)
{
string sRollbackError = oConnManager.Rollback();
oResult.IsSuccess = false;
oResult.ErrMsg = sRollbackError.Equals("") ? oConnManager.GetErrorMessage(e) : sRollbackError;
}
finally
{
oConnManager.Close();
}
}
else
{
oResult.IsSuccess = false;
oResult.ErrMsg = s_DBError;
}
return(oResult);
}
protected void ExcelClick_Click(object sender, EventArgs e)
{
CsvExport myExport = new CsvExport();
ArrayList columns = new ArrayList();
DataTableReader dtr = DataBase.dbDataTable(ReportSQLStatement).CreateDataReader();
for (int i = 0; i < dtr.FieldCount; i++)
{
columns.Add(dtr.GetName(i));
}
if (dtr.HasRows)
{
while (dtr.Read())
{
// Then you can do any of the following three output options:
myExport.AddRow();
foreach (var ii in columns)
{
string columnName = ii.ToString();
myExport[columnName] = dtr[columnName].ToString();
}
}
}
myCsv = myExport.Export();
string attachment = "attachment; filename=AdHocResourcesReports-data-" + DateTime.Now.ToShortDateString() + ".csv";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.AddHeader("content-disposition", attachment);
HttpContext.Current.Response.ContentType = "text/csv";
HttpContext.Current.Response.AddHeader("Pragma", "public");
HttpContext.Current.Response.Write(myCsv);
HttpContext.Current.Response.End();
}
protected void DownloadReport_Click(object sender, EventArgs e)
{
StartDateTxt = StartDate.Text;
EndDateTxt = EndDate.Text;
string CompareDataAgainstTxt = CompareDataAgainst.SelectedValue;
string DateRangeTxt = DateRange.SelectedValue;
Resources = getResources();
switch (ReportTypeDD.SelectedValue)
{
case "Comparative Resource Pricing":
showComparativeResults(Resources, CompareDataAgainstTxt, DateRangeTxt);
break;
case "Pricing Over Time":
showPricingOver(Resources, CompareDataAgainstTxt, DateRangeTxt);
break;
case "Procurement Method":
showProcurementMethod(Resources, CompareDataAgainstTxt, DateRangeTxt);
break;
case "Contract Expiration":
showExpirationContracts(Resources, CompareDataAgainstTxt, DateRangeTxt);
break;
}
//ResultsGridView.DataSource = dtResults;
CsvExport myExport = new CsvExport();
ArrayList columns = new ArrayList();
DataTableReader dtr = dtResults.CreateDataReader();
for (int i = 0; i < dtr.FieldCount; i++)
{
columns.Add(dtr.GetName(i));
}
if (dtr.HasRows)
{
while (dtr.Read())
{
// Then you can do any of the following three output options:
myExport.AddRow();
foreach (var ii in columns)
{
string columnName = ii.ToString();
myExport[columnName] = dtr[columnName].ToString();
}
}
}
myCsv = myExport.Export();
string attachment = "attachment; filename=AdHocResourcesReports-data-" + DateTime.Now.ToShortDateString() + ".csv";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.AddHeader("content-disposition", attachment);
HttpContext.Current.Response.ContentType = "text/csv";
HttpContext.Current.Response.AddHeader("Pragma", "public");
HttpContext.Current.Response.Write(myCsv);
HttpContext.Current.Response.End();
}
protected void ExcelClick_Click(object sender, EventArgs e)
{
CsvExport myExport = new CsvExport();
// Then you can do any of the following three output options:
DataTableReader reader = DataBase.dbDataTable(currentViewSQL.Value).CreateDataReader();
while (reader.Read())
{
myExport.AddRow();
myExport["ResourceName"] = reader["ResourceName"].ToString().Trim();
myExport["PasswordActive"] = reader["PasswordActive"].ToString().Trim();
}
reader.Close();
myCsv = myExport.Export();
string attachment = "attachment; filename=resource-data-" + DateTime.Now.ToShortDateString() + ".csv";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.AddHeader("content-disposition", attachment);
HttpContext.Current.Response.ContentType = "text/csv";
HttpContext.Current.Response.AddHeader("Pragma", "public");
HttpContext.Current.Response.Write(myCsv);
HttpContext.Current.Response.End();
}
protected void ExcelClick_Click(object sender, EventArgs e)
{
CsvExport myExport = new CsvExport();
// Then you can do any of the following three output options:
myExport.AddRow();
myExport["Statistic"] = "Unique Visitors";
myExport["FY" + lastYearDisplay + "(Full)"] = long.Parse(uniqueVistor_LastYearFull).ToString("N0");
myExport["FY" + lastYearDisplay + "(to Date)"] = long.Parse(uniqueVistor_LastYeartoDate).ToString("N0");
myExport["FY" + thisYearDisplay + "(to Date)"] = long.Parse(uniqueVistor_thisYeartoDate).ToString("N0");
myExport["% Difference to Date"] = percentDetails_uniqueVisitor;
myExport.AddRow();
myExport["Statistic"] = "Number of Hits";
myExport["FY" + lastYearDisplay + "(Full)"] = long.Parse(NumberofHits_LastYearFull).ToString("N0");
myExport["FY" + lastYearDisplay + "(to Date)"] = long.Parse(NumberofHits_LastYeartoDate).ToString("N0");
myExport["FY" + thisYearDisplay + "(to Date)"] = long.Parse(NumberofHits_thisYeartoDate).ToString("N0");
myExport["% Difference to Date"] = percentDetails_NumberofHits;
myCsv = myExport.Export();
string attachment = "attachment; filename=uniqueVisitors-data-" + DateTime.Now.ToShortDateString() + ".csv";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.AddHeader("content-disposition", attachment);
HttpContext.Current.Response.ContentType = "text/csv";
HttpContext.Current.Response.AddHeader("Pragma", "public");
HttpContext.Current.Response.Write(myCsv);
HttpContext.Current.Response.End();
}
protected void btnExport_Click(object sender, EventArgs e)
{
int reportType = Utils.GetIntegerOnly(ddlReportType.SelectedValue);
CsvExport export = new CsvExport();
string fileName = "export";
if(reportType==0){
//Sales summary
QueryData qd = GetSalesSummaryQuery();
UniqueList<string> values = lstSalesSummary.GetAllValues();
//IAqlQuery
IAqlQuery q = new DataQuery(qd);
List<string> fieldNames = q.GetFieldNames();
AqlResultSet rs = q.Execute(true);
while(rs.Read()){
export.AddRow();
export[fieldNames[0]] = rs[0]; //Period
export[fieldNames[1]] = rs[1]; //Number of orders
export[fieldNames[2]] = rs[2]; //Total revenue
export[fieldNames[3]] = rs[3]; // Average order value
export[fieldNames[4]] = rs[4]; //biggest order
int mostSoldProductRevenue = Utils.GetIntegerOnly(rs[5].ToString());
if (mostSoldProductRevenue > 0) {
ProductBase pMostSoldRevenue = WAFContext.Session.GetContent<ProductBase>(mostSoldProductRevenue);
export[fieldNames[5]] = pMostSoldRevenue.Name + " (" + mostSoldProductRevenue + ")"; //MostPopularProductRevenue
} else {
export[fieldNames[5]] = "";
}
int mostSoldProductNumItems = Utils.GetIntegerOnly(rs[5].ToString());
if (mostSoldProductNumItems > 0) {
ProductBase pMostSoldItems = WAFContext.Session.GetContent<ProductBase>(mostSoldProductNumItems);
export[fieldNames[6]] = pMostSoldItems.Name + " (" + mostSoldProductNumItems + ")"; //MostPopularProductNumItems
} else {
export[fieldNames[6]] = "";
}
}
fileName = "sales_summary";
}else if(reportType ==1){
//Sales over time
AqlQuery q = null;
if (txtStartDate.Text.Length > 0) {
DateTime startDT = DateTime.Parse(txtStartDate.Text);
DateTime endDT = DateTime.Now;
if (txtEndDate.Text.Length > 0) {
endDT = DateTime.Parse(txtEndDate.Text);
}
ReportPeriod period = new ReportPeriod();
period.FromDate = startDT;
period.ToDate = endDT;
q = WebshopStatistics.GetGroupSalesData(period, GetDateTimePart());
} else {
q = WebshopStatistics.GetGroupSalesData(this.GetReportPeriod(DefaultReportPeriod.AllTime), GetDateTimePart());
}
List<string> fieldNames = q.GetFieldNames();
AqlResultSet rs = q.Execute(true);
while (rs.Read()) {
export.AddRow();
export["Period"] = GetPeriodDescription(Utils.GetIntegerOnly(rs[0]), Utils.GetIntegerOnly(rs[3])); //DateTimePart
export["Revenue in period"] = ((double)rs[1]).ToString("N2"); //Revenue
export["Number of orders in period"] = rs[2]; //NumOrders
}
fileName = "sales_over_time";
}else if(reportType == 2){
//stock list
AqlQuery q = null;
if (txtStartDate.Text.Length > 0) {
DateTime startDT = DateTime.Parse(txtStartDate.Text);
DateTime endDT = DateTime.Now;
if (txtEndDate.Text.Length > 0) {
endDT = DateTime.Parse(txtEndDate.Text);
}
ReportPeriod period = new ReportPeriod();
period.FromDate = startDT;
period.ToDate = endDT;
q = WebshopStatistics.GetProductStockReportData(period, null);
} else {
q = WebshopStatistics.GetProductStockReportData(this.GetReportPeriod(DefaultReportPeriod.AllTime), null);
}
List<string> fieldNames = q.GetFieldNames();
AqlResultSet rs = q.Execute(true);
while (rs.Read()) {
export.AddRow();
export["NodeId"] = rs[0];
export["Item number"] = rs[1];
export["Product name"] = rs[2];
export["Number in stock"] = rs[3];
}
fileName = "stock_list";
}else if(reportType == 3){
//product sales report
AqlQuery q = null;
if (txtStartDate.Text.Length > 0) {
DateTime startDT = DateTime.Parse(txtStartDate.Text);
DateTime endDT = DateTime.Now;
if (txtEndDate.Text.Length > 0) {
endDT = DateTime.Parse(txtEndDate.Text);
}
ReportPeriod period = new ReportPeriod();
period.FromDate = startDT;
period.ToDate = endDT;
q = WebshopStatistics.GetProductSalesData(period, null);
} else {
q = WebshopStatistics.GetProductSalesData(this.GetReportPeriod(DefaultReportPeriod.AllTime), null);
}
List<string> fieldNames = q.GetFieldNames();
q.IncludeUnpublished = true;
AqlResultSet rs = q.Execute(true);
while (rs.Read()) {
export.AddRow();
export["NodeId"] = rs[0];
export["Item number"] = rs[1];
export["Product name"] = rs[2];
export["Numberof items sold"] = rs[3];
export["Revenue"] = rs[4];
export["Last sale"] = rs[5];
export["First sale"] = rs[6];
}
fileName = "products";
}else if(reportType == 4){
//customers
fileName = "customers";
}
//foreach (ProductBase p in products) {
// export.AddRow();
// export["Name"] = p.Name;
// export["NodeId"] = p.NodeId;
// export["SiteId"] = p.SiteId;
// export["Currency5PriceExVat"] = p.Currency5PriceExVat;
//}
Response.Clear();
Response.ClearHeaders();
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment;filename="+fileName+"_" + DateTime.Now.Day + "_" + DateTime.Now.Month + "_" + DateTime.Now.Year + ".csv");
//Response.Charset = "UTF-8";
Response.ContentEncoding = Encoding.Default;//Encoding.GetEncoding("iso-8859-1"); //Encoding.GetEncoding(System.Globalization.CultureInfo.CurrentCulture.TextInfo.OEMCodePage); //Encoding.Default;
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "text/csv";
Response.Write(export.Export());
Response.End();
}
public string GenerateCSV(List <RptScheduleViewModel> models, string path)
{
//StringBuilder sb = new StringBuilder();
List <CSVScheduleViewModel> contents = new List <CSVScheduleViewModel>();
//string header = "Date,Registry No.,Flight(1st leg),Waiting Time,Flight(2nd leg),Pilot,Co-Pilot,Crew(s),Notes,Passengers,Flight Info,Technical Stops,ETC";
//Csv csv = new Csv();
#region Loop Schedule
contents.Add(new CSVScheduleViewModel()
{
Date = "Date",
RegistryNumber = "Registry No.",
FlightFirstLeg = "Flight(1st leg)",
WaitingTime = "Waiting Time",
FlightSecondLeg = "Flight(2nd leg)",
Pilot = "Pilot",
CoPilot = "Co-Pilot",
AircraftCrew = "Crew(s)",
Notes = "Notes",
Passengers = "Passengers",
FlightInfo = "Flight Info",
TechnicalStops = "Technical Stops",
ETC = "ETC"
});
foreach (var value in models)
{
var startend = value.StartDate + " - " + value.EndDate;
if (value.StartDate == value.StartDate2)
{
startend = value.StartDate;
}
var startend2 = value.EndDate2 + " - " + value.EndDate;
if (value.EndDate2 == value.EndDate)
{
startend2 = value.EndDate2;
}
var waiting = value.StartDate2 + " - " + value.EndDate2;
if (value.StartDate2 == value.EndDate2)
{
waiting = value.StartDate2;
}
var startendTime = value.StartTime + " - " + value.StartTime2;
var waitingTime = value.StartTime2 + " - " + value.EndTime2;
var startendTime2 = value.EndTime2 + " - " + value.EndTime;
var waitingLocation = value.FirstLegOfFlight.Split('-');
var crews = string.Empty;
foreach (var crew in value.Crews)
{
crews += crew.Fullname + "\n";
}
//if ( !string.IsNullOrEmpty( crews ) )
// crews = crews.Trim().Remove( crews.Length - 1 );
if (value.StartDate == value.StartDate2)
{
startend = value.StartDate;
}
var schedule = new CSVScheduleViewModel()
{
Date = value.Date,
RegistryNumber = value.AircraftRegistration,
FlightFirstLeg = value.FirstLegOfFlight + string.Format(" [{0} ({1})]", startend, startendTime),
WaitingTime = waitingLocation[1] + string.Format(" [{0} ({1})]", waiting, waitingTime),
FlightSecondLeg = value.SecondLegOfFlight + string.Format(" [{0} ({1})]", startend2, startendTime2),
Pilot = value.Pilot,
CoPilot = value.CoPilot,
AircraftCrew = crews,
Notes = value.Notes,
Passengers = value.Passenger,
FlightInfo = value.FlightInfo,
TechnicalStops = value.TechStops,
ETC = value.Etc
};
contents.Add(schedule);
}
#endregion
string filename = "";
if (contents != null)
{
CsvExport <CSVScheduleViewModel> csv = new CsvExport <CSVScheduleViewModel>(contents);
string csvdata = csv.Export(false);
if (System.IO.File.Exists(path))
{
System.IO.File.Delete(path);
}
if (!System.IO.File.Exists(path))
{
//TextWriter tw = new StreamWriter( path );
//tw.WriteLine( expected );
using (StreamWriter sw = new StreamWriter(new FileStream(path, FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite)))
{
sw.WriteLine(csvdata);
}
}
filename = "RptSchedule.csv";
System.IO.File.OpenText(path);
}
return(filename);
}
protected void ExcelClick_Click(object sender, EventArgs e)
{
CsvExport myExport = new CsvExport();
// Then you can do any of the following three output options:
DataTableReader reader = DataBase.dbDataTable(currentViewSQL, "Admin.DbConnection").CreateDataReader();
while (reader.Read())
{
myExport.AddRow();
string ePass = "";
string SubjectAreasTaxonomyDB = reader["SubjectAreasTaxonomy"].ToString().Trim();
string AccessTypeTaxonomyDB = reader["AccessTypeTaxonomy"].ToString().Trim();
string ePassDB = reader["ePass"].ToString().Trim();
string SubjectAreasTaxonomyDisplay = string.Empty;
if (SubjectAreasTaxonomyDB != "")
{
string[] taxIds = SubjectAreasTaxonomyDB.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
foreach (string taxId in taxIds)
{
SubjectAreasTaxonomyDisplay += commonfunctions.GetTaxonomyNameFromID(long.Parse(taxId)) + " , ";
}
}
ePass = ePassDB;
myExport["Resource Name"] = reader["ResourceName"].ToString().Trim();
myExport["Subject Areas"] = SubjectAreasTaxonomyDisplay.Trim().TrimEnd(',');
myExport["e-Pass"] = ePass;
myExport["Status"] = reader["ResourceDisplayStatus"].ToString().Trim();
}
reader.Close();
myCsv = myExport.Export();
string attachment = "attachment; filename=resource-data-" + DateTime.Now.ToShortDateString() + ".csv";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.AddHeader("content-disposition", attachment);
HttpContext.Current.Response.ContentType = "text/csv";
HttpContext.Current.Response.AddHeader("Pragma", "public");
HttpContext.Current.Response.Write(myCsv);
HttpContext.Current.Response.End();
}
public MemoryStream Export(IList <string> skuList,
MarketType market,
string marketplaceId)
{
IList <GrouponGoodLine> results = new List <GrouponGoodLine>();
using (var db = _dbFactory.GetRWDb())
{
var parentASINs = db.Items.GetAllViewActual()
.Where(i => i.Market == (int)market &&
(i.MarketplaceId == marketplaceId ||
String.IsNullOrEmpty(marketplaceId)) &&
skuList.Contains(i.SKU))
.Select(i => i.ParentASIN)
.ToList();
var itemDtoList = db.Items.GetAllActualExAsDto()
.Where(i => i.Market == (int)market &&
(i.MarketplaceId == marketplaceId ||
String.IsNullOrEmpty(marketplaceId)) &&
parentASINs.Contains(i.ParentASIN))
.ToList();
var styleIdList = itemDtoList.Where(i => i.StyleId.HasValue).Select(i => i.StyleId.Value).ToList();
var styleItemIdList = itemDtoList.Where(i => i.StyleItemId.HasValue).Select(i => i.StyleItemId.Value).ToList();
var itemLinkDtoList = db.Items.GetAllActualExAsDto()
.Where(i => i.Market == (int)MarketType.Shopify &&
i.MarketplaceId == MarketplaceKeeper.ShopifyEveryCh &&
i.StyleId.HasValue &&
styleIdList.Contains(i.StyleId.Value))
.ToList();
var parentItemDtoList = db.ParentItems.GetAllAsDto()
.Where(i => i.Market == (int)market &&
(i.MarketplaceId == marketplaceId ||
String.IsNullOrEmpty(marketplaceId)) &&
parentASINs.Contains(i.ASIN))
.ToList();
var styleList = db.Styles.GetAllAsDtoEx().Where(s => styleIdList.Contains(s.Id)).ToList();
var styleItemList = db.StyleItems.GetAllAsDto().Where(si => styleItemIdList.Contains(si.StyleItemId)).ToList();
var allStyleImageList = db.StyleImages.GetAllAsDto().Where(sim => styleIdList.Contains(sim.StyleId)).ToList();
var allFeatures = db.FeatureValues.GetValuesByStyleIds(styleIdList) as List <FeatureValueDTO>;
allFeatures.AddRange(db.StyleFeatureTextValues.GetAllWithFeature().Where(ft => styleIdList.Contains(ft.StyleId))
.Select(f => new FeatureValueDTO()
{
Id = f.Id,
FeatureId = f.FeatureId,
FeatureName = f.FeatureName,
StyleId = f.StyleId,
Value = f.Value
}).ToList());
if (!String.IsNullOrEmpty(_grouponImageDirectory))
{
foreach (var styleImage in allStyleImageList)
{
try
{
var styleString = styleList.FirstOrDefault(s => s.Id == styleImage.StyleId)?.StyleID;
var filepath = ImageHelper.BuildGrouponImage(_grouponImageDirectory,
styleImage.Image,
styleString + "_" + MD5Utils.GetMD5HashAsString(styleImage.Image));
var filename = Path.GetFileName(filepath);
styleImage.Image = UrlUtils.CombinePath(_grouponImageBaseUrl, filename);
}
catch (Exception ex)
{
_log.Info("BuildGrouponImage error, image=" + styleImage.Image, ex);
}
}
}
foreach (var item in itemDtoList)
{
var itemLink = itemLinkDtoList.FirstOrDefault(i => i.StyleItemId == item.StyleItemId);
if (itemLink != null)
{
item.SourceMarketUrl = itemLink.SourceMarketUrl;
}
else
{
item.SourceMarketUrl = null;
}
var parent = parentItemDtoList.FirstOrDefault(p => p.ASIN == item.ParentASIN);
if (parent != null)
{
item.OnHold = parent.OnHold;
}
}
//itemDtoList = itemDtoList.Where(i => !i.OnHold).ToList();
if (itemDtoList.Any())
{
results = BuildProductLines(itemDtoList,
parentItemDtoList,
styleList,
allStyleImageList,
styleItemList,
allFeatures);
}
}
var stream = CsvExport.Export(results, null, new List <ExcelColumnOverrideInfo>()
{
new ExcelColumnOverrideInfo()
{
Title = "DWS Cost",
RemoveIt = true,
}
});
return(stream);
}
protected void btnExportExcel_Click(object sender, EventArgs e)
{
AqlQuery q = GetOrderSelectionQuery((OrderFilter)Utils.GetIntegerOnly(ddlFilterBy.SelectedValue));
List<Order> orders = new List<Order>();
AqlResultSet rs = q.Execute();
while (rs.Read()) {
orders.Add((Order)rs[0]);
}
Shop shop = WAFShopHelper.GetCurrentShop();
//List<Order> orders = WAFContext.Session.Query<Order>().Where(AqlOrder.SiteId == WAFContext.Session.SiteId).Where(AqlOrder.IsDerived == false).OrderBy(AqlOrder.Name).Execute();
CsvExport export = new CsvExport();
foreach (Order o in orders) {
export.AddRow();
export["OrderId"] = o.NodeId;
export["OrderStatus"] = WAFContext.Session.GetEnumName(o.OrderStatus);
export["Email"] = o.Email;
if (o.DateOrdered > o.CreateDate) {
export["DateOrdered"] = o.DateOrdered;
} else {
export["DateOrdered"] = " - ";
}
if (o.DateShipped > o.CreateDate) {
export["DateShipped"] = o.DateShipped;
} else {
export["DateShipped"] = " - ";
}
export["OrderTotal"] = o.OrderTotal;
export["VATAmount"] = o.VATAmount;
export["ItemsSubtotalExVat"] = o.ItemsSubtotalExVat;
double itemsIncVat = 0;
foreach (OrderItem item in o.OrderItems.Get()) {
itemsIncVat = itemsIncVat + item.ItemTotalIncVat;
}
export["ItemsSubtotalIncVat"] = o.ItemsSubtotalExVat;
export["PaymentTransactionId"] = o.PaymentTransactionId;
if (o.PaymentMethod.IsSet()) {
export["PaymentMethod"] = o.PaymentMethod.Get().Name;
} else {
export["PaymentMethod"] = "";
}
export["ShippingAmountExVat"] = o.ShippingAmount;
double taxPercetage = ((double)shop.DefaultTaxRate / (double)100);
export["ShippingAmountIncVat"] = o.ShippingAmount * (1 + taxPercetage);
if (o.ShippingMethod.IsSet()) {
export["ShippingMethod"] = o.ShippingMethod.Get().Name;
} else {
export["ShippingMethod"] = "";
}
string currency = "";
if (o.Currency.IsSet()) {
currency = o.Currency.Get().Code;
}
export["Currency"] = currency;
export["NumberOfItemsInOrder"] = o.GetNumberOfItemsInCart();
if (o.ClassId != Order.ContentClassId) {
//inherited class. add custom fields
ContentDefinition def = WAFContext.Engine.Definition;
var baseProp = def.ContentClass[Order.ContentClassId].GetAllPropertyIds();
var newPropIds = def.ContentClass[o.ClassId].GetAllPropertyIds().Where(p => !baseProp.Contains(p));
foreach (var p in newPropIds.Select(id => def.Property[id])) {
switch (p.BasePropertyClassId) {
case PropertyBaseClass.CheckBoxProperty:
case PropertyBaseClass.FloatProperty:
case PropertyBaseClass.ShortTextProperty:
// case PropertyBaseClass.LongTextProperty:
try { export[p.CodeName] = o.GetProperty(p.Id).ToString(); } catch { }
break;
case PropertyBaseClass.IntegerProperty:
if (p.PropertyClassId == -14) {
EnumerationPropertySettings enumPropSettings = (EnumerationPropertySettings)p.GetSettings(o.ClassId);
IntegerDataValue dv = (IntegerDataValue)o.GetDataValue(p.DataValueIdList[0]);
MemDefEnumeration defEnum = WAFContext.Session.Definitions.Enumeration[enumPropSettings.EnumerationTypeName];
if (dv.Value > 0) {
export[p.CodeName] = defEnum.Values[dv.Value].GetName(WAFContext.Session);
} else {
export[p.CodeName] = "";
}
//MemDefEnumerationValue value in o.Session.Definitions.Enumeration[s.EnumerationTypeName].Values.Values
} else {
IntegerDataValue dv = (IntegerDataValue)o.GetDataValue(p.DataValueIdList[0]);
export[p.CodeName] = dv.Value;
}
break;
default: break;
}
//customPropIds.Add(id);
}
}
export["BillingForename"] = o.BillingForename;
export["BillingSurname"] = o.BillingSurname;
export["BillingCompany"] = o.BillingCompany;
export["BillingAddress"] = o.BillingAddress;
export["BillingAddress2"] = o.BillingAddress2;
export["BillingState"] = WAFContext.Session.GetEnumName(o.BillingState);
export["BillingZip"] = o.BillingZip;
export["BillingCity"] = o.BillingCity;
string billingCountry = "";
if (o.BillingCountry.IsSet()) {
billingCountry = o.BillingCountry.Get().Name;
}
export["BillingCountry"] = billingCountry;
export["BillingMobile"] = o.BillingMobile;
export["BillingPhone"] = o.BillingPhone;
export["ShippingForename"] = o.ShippingForename;
export["ShippingSurname"] = o.ShippingSurname;
export["ShippingCompany"] = o.ShippingCompany;
export["ShippingAddress"] = o.ShippingAddress;
export["ShippingAddress2"] = o.ShippingAddress2;
export["ShippingState"] = WAFContext.Session.GetEnumName(o.ShippingState);
export["ShippingZip"] = o.ShippingZip;
export["ShippingCity"] = o.ShippingCity;
string shippingCountry = "";
if (o.ShippingCountry.IsSet()) {
shippingCountry = o.ShippingCountry.Get().Name;
}
export["ShippingCountry"] = shippingCountry;
export["ShippingMobile"] = o.ShippingMobile;
export["ShippingPhone"] = o.ShippingPhone;
export["InvoiceNotes"] = o.InvoiceNotes;
export["SiteId"] = o.SiteId;
export["LCID"] = o.LCID;
export["TemplateId"] = o.TemplateId;
export["CreateDate"] = o.CreateDate;
export["ChangeDate"] = o.ChangeDate;
export["PublishDate"] = o.PublishDate;
}
Response.Clear();
Response.ClearHeaders();
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment;filename=orders_" + DateTime.Now.Day + "_" + DateTime.Now.Month + "_" + DateTime.Now.Year + "_kl_" + DateTime.Now.Hour + "_" + DateTime.Now.Minute + ".csv");
//Response.Charset = "UTF-8";
Response.ContentEncoding = Encoding.Default;//Encoding.GetEncoding("iso-8859-1"); //Encoding.GetEncoding(System.Globalization.CultureInfo.CurrentCulture.TextInfo.OEMCodePage); //Encoding.Default;
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "text/csv";
Response.Write(export.Export());
Response.End();
}
//public DataTable dtAllResouces ;
//private DataTable getAllResouces()
//{
// return DataBase.dbDataTable("Select * from Resources order by ResourceName");
//}
protected void ExcelClick_Click(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand("HelpRequests_Reports_By");
cmd.Parameters.AddWithValue("@BuildAllSQL", BuildAllSQL);
cmd.Parameters.AddWithValue("@DisplayBy", DisplayResultsBy);
CsvExport myExport = new CsvExport();
ArrayList columns = new ArrayList();
DataTableReader dtr = DataBase.executeStoreProcudure(cmd).CreateDataReader();
for (int i = 0; i < dtr.FieldCount; i++)
{
columns.Add(dtr.GetName(i));
}
if (dtr.HasRows)
{
while (dtr.Read())
{
// Then you can do any of the following three output options:
myExport.AddRow();
foreach (var ii in columns)
{
string columnName = ii.ToString();
myExport[columnName] = dtr[columnName].ToString();
}
}
}
myCsv = myExport.Export();
string attachment = "attachment; filename=AdHocResourcesReports-data-" + DateTime.Now.ToShortDateString() + ".csv";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.AddHeader("content-disposition", attachment);
HttpContext.Current.Response.ContentType = "text/csv";
HttpContext.Current.Response.AddHeader("Pragma", "public");
HttpContext.Current.Response.Write(myCsv);
HttpContext.Current.Response.End();
}
protected void btnExportProductsData_Click(object sender, EventArgs e)
{
List<ProductBase> products = WAFContext.Session.Query<ProductBase>().Where(AqlProductBase.SiteId == WAFContext.Session.SiteId).Where(AqlProductBase.IsDerived == false).OrderBy(AqlProductBase.Name).Execute();
CsvExport export = new CsvExport();
foreach (ProductBase p in products) {
export.AddRow();
export["Name"] = p.Name;
export["NodeId"] = p.NodeId;
export["SiteId"] = p.SiteId;
export["LCID"] = p.LCID;
export["TemplateId"] = p.TemplateId;
export["ItemNumber"] = p.ItemNumber;
export["NumberInStock"] = p.NumberInStock;
export["PriceExVat"] = p.PriceExVat;
export["ShippingCost"] = p.ShippingCost;
export["Currency1PriceExVat"] = p.Currency1PriceExVat;
export["Currency2PriceExVat"] = p.Currency2PriceExVat;
export["Currency3PriceExVat"] = p.Currency3PriceExVat;
export["Currency4PriceExVat"] = p.Currency4PriceExVat;
export["Currency5PriceExVat"] = p.Currency5PriceExVat;
}
Response.Clear();
Response.ClearHeaders();
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment;filename=products_" + DateTime.Now.Day + "_" + DateTime.Now.Month + "_" + DateTime.Now.Year + ".csv");
Response.ContentEncoding = Encoding.Default;//Encoding.GetEncoding("iso-8859-1"); //Encoding.GetEncoding(System.Globalization.CultureInfo.CurrentCulture.TextInfo.OEMCodePage); //Encoding.Default;
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "text/csv";
Response.Write(export.Export());
Response.End();
}
protected void ExcelClick_Click(object sender, EventArgs e)
{
CsvExport myExport = new CsvExport();
// Then you can do any of the following three output options:
DataTableReader reader = DataBase.dbDataTable(currentViewSQL).CreateDataReader();
while (reader.Read())
{
myExport.AddRow();
string resourceID = reader["ResourceToAccess"].ToString().Trim();
myExport["Date"] = reader["SubmissionDateandTime"].ToString().Trim();
myExport["Requestor"] = getResourceRequestsAdminUsers(resourceID);
myExport["Assignted To"] = getUserNameByPIN(reader["SubmittedByPIN"].ToString().Trim());
myExport["Status"] = reader["FormStatus"].ToString().Trim();
}
reader.Close();
myCsv = myExport.Export();
string attachment = "attachment; filename=resource-data-" + DateTime.Now.ToShortDateString() + ".csv";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.AddHeader("content-disposition", attachment);
HttpContext.Current.Response.ContentType = "text/csv";
HttpContext.Current.Response.AddHeader("Pragma", "public");
HttpContext.Current.Response.Write(myCsv);
HttpContext.Current.Response.End();
}
protected void ExcelClick_Click(object sender, EventArgs e)
{
CsvExport myExport = new CsvExport();
// Then you can do any of the following three output options:
DataTableReader reader = DataBase.dbDataTable(currentViewSQL.Value).CreateDataReader();
while (reader.Read())
{
myExport.AddRow();
string pindb = reader["UserPIN"].ToString().Trim();
//Dictionary<string, string> uDetails = loginSSA.GetUsersDetails(pindb);
myExport["LastName"] = reader["UserLastName"].ToString().Trim(); //uDetails["LastName"];
myExport["FirstName"] = reader["UserFirstName"].ToString().Trim(); //uDetails["FirstName"];
myExport["Email"] = reader["UserEmail"].ToString().Trim(); //uDetails["Email"];
myExport["PIN"] = pindb;
myExport["officeCode"] = reader["UserofficeCode"].ToString().Trim(); //uDetails["OfficeCode"];
}
reader.Close();
myCsv = myExport.Export();
string attachment = "attachment; filename=resource-data-" + DateTime.Now.ToShortDateString() + ".csv";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.AddHeader("content-disposition", attachment);
HttpContext.Current.Response.ContentType = "text/csv";
HttpContext.Current.Response.AddHeader("Pragma", "public");
HttpContext.Current.Response.Write(myCsv);
HttpContext.Current.Response.End();
}