private void ExportCommon(OrderedDictionary columns, string sql, ExcelPackage xl, string sheetName)
{
int colIndex;
var ws = xl.Workbook.Worksheets.Add(sheetName);
using (IDbConnection conn = _sys.NewDBConnection()) {
conn.Open();
using (IDataReader dr = _sys.ExecuteReader(conn, sql)) {
if (!string.IsNullOrEmpty(_sys.LastError))
{
throw new Exception(_sys.LastError);
}
string[] keys = new string[columns.Count];
columns.Keys.CopyTo(keys, 0);
int rowIndex = 2;
while (dr.Read())
{
for (colIndex = 1; colIndex <= columns.Count; colIndex++)
{
ws.Cells[rowIndex, colIndex].Value = dr[keys[colIndex - 1]];
}
rowIndex++;
}
dr.Close();
}
conn.Close();
}
ws.Cells["A1:H1"].AutoFilter = true;
ws.Column(5).Style.Numberformat.Format = "MM/dd/yyyy"; //TODO: put this in the config file
ws.Column(8).Style.Numberformat.Format = "MM/dd/yyyy"; //TODO: put this in the config file
for (colIndex = 1; colIndex <= columns.Count; colIndex++)
{
ws.Cells[1, colIndex].Value = columns[colIndex - 1];
ws.Column(colIndex).AutoFit();
ws.Column(colIndex).Style.WrapText = true;
}
}
private bool ExportForOPT(DateTime startDate, DateTime endDate, ExcelPackage xl, string lang)
{
OrderedDictionary columns = new OrderedDictionary()
{
{ "cityname", "Destination" },
{ "productsubtypename", "Type" },
{ "operatorname", "Supplier" },
{ "title", "Product Name" },
{ "code", "Product Code" },
{ "catdesc", "Category" },
{ "cat", "Category Code" },
{ "validity", "Validity" },
{ "net", "Agent Net" },
{ "retail", "Retail Price" },
{ "chd limit", "Child Age" },
{ "chd grate", "Child Net" },
{ "childretail", "Child Retail" },
{ "jr limit", "Junior Age" },
{ "jr grate", "Junior Net" },
{ "juniorretail", "Junior Retail" },
{ "duration", "Duration" },
};
endDate = endDate.AddDays(1).AddSeconds(-1);
using (IDbConnection conn = _sys.NewDBConnection()) {
conn.Open();
using (IDbCommand cmd = _sys.NewDBCommand(conn)) {
cmd.CommandText = "usp_ProductList";
cmd.CommandType = CommandType.StoredProcedure;
AddParameter(cmd, DbType.DateTime, "@startDate", startDate);
AddParameter(cmd, DbType.DateTime, "@endDate", endDate);
AddParameter(cmd, DbType.String, "@lang", lang);
return(ExportCommon(columns, cmd, xl, lang));
}
}
}
private void Export(DateTime startDate, DateTime endDate, ExcelPackage xl)
{
Dictionary <string, string> agencies = new Dictionary <string, string> {
{ "8572040812", "LPG" },
{ "7026802096", "Expedia" },
{ "2625068382", "Musement" },
{ "8556648235", "GYG" },
{ "7027495698", "Viator" },
{ "HOTELBEDS", "HotelBeds" },
{ "REZDY", "Rezdy" },
{ "R-VENETIAN", "Venetian" },
};
string agencyList = string.Join(",", agencies.Select(x => $"'{x.Key}'"));
Dictionary <string, ExcelWorksheet> sheets = new Dictionary <string, ExcelWorksheet>();
foreach (var item in agencies)
{
sheets.Add(item.Key, xl.Workbook.Worksheets.Add(item.Value));
}
Dictionary <string, string> columns = new Dictionary <string, string>()
{
{ "res no", "Trip #" },
{ "item", "Item" },
{ "res agt", "Res Agent" },
{ "tripremarks", "Trip Remarks" },
{ "itemremarks", "Item Remarks" },
{ "reference", "Reference" },
{ "doc nbr", "Doc Nbr" },
{ "last name", "Last Name" },
{ "first name", "First Name" },
{ "code", "Code" },
{ "description", "Description" },
{ "category", "Category" },
{ "catdesc", "Category Description" },
{ "start date", "Service Date" },
{ "res date", "Booking Date" },
{ "cancelled", "Cancelled" },
{ "net", "Amount" },
{ "cost", "Cost" },
};
string[] keys = new string[columns.Count];
columns.Keys.CopyTo(keys, 0);
//Create another dictionary with the same keys holding the index of the current row in each sheet
int colCounter = 1;
Dictionary <string, int> rowCounters = new Dictionary <string, int>();
foreach (var sheet in sheets)
{
colCounter = 1;
foreach (var key in columns)
{
sheet.Value.Cells[1, colCounter].Value = key.Value;
sheet.Value.Cells[1, colCounter].Style.Font.Bold = true;
colCounter++;
}
rowCounters.Add(sheet.Key, 2); //row 1 is the column header
}
string dateField = $"[{radioGroupDate.EditValue}]";
endDate = endDate.AddDays(1).AddSeconds(-1);
string sql = $@"SELECT reshdr.[Res No], resitm.Item, resitm.[Res Agt], reshdr.InternalRemarks as TripRemarks, resitm.InternalRemarks as ItemRemarks,
reshdr.Reference, psgrlist.[Doc Nbr], psgrlist.[Last Name], psgrlist.[First Name], Code, DESCRIP AS description, resroom.CAT AS Category, resroom.catdesc, [Strt Date] AS [Start Date], resitm.[Res Date],
CASE WHEN resitm.Inactive = 1 THEN 'Yes' ELSE 'No' END AS Cancelled, resitm.agency, resitm.Net, resnight.Cost FROM resitm
INNER JOIN reshdr ON resitm.[res no] = reshdr.[res no]
INNER JOIN psgrres ON reshdr.[res no] = psgrres.[res no] and psgrres.[client level] = 1
INNER JOIN psgrlist ON psgrres.[CLIENT NO] = psgrlist.[CLIENT NO]
INNER JOIN resroom ON resroom.[res no] = resitm.[res no] and resroom.item = resitm.item and resroom.room = 0
INNER JOIN resnight ON resnight.[res no] = resitm.[res no] and resnight.item = resitm.item and resnight.room = 0 and resnight.night = 0
WHERE AGENCY in ({agencyList}) AND resitm.{dateField} >= '{startDate}' AND resitm.{dateField} <= '{endDate}'
ORDER BY {dateField}";
sql = sql.Replace(Environment.NewLine, string.Empty);
using (IDbConnection conn = _sys.NewDBConnection()) {
conn.Open();
using (IDataReader dr = _sys.ExecuteReader(conn, sql)) {
if (!string.IsNullOrEmpty(_sys.LastError))
{
throw new Exception(_sys.LastError);
}
while (dr.Read())
{
string agency = dr["agency"].ToString();
var ws = sheets[agency];
rowCounters[agency] = AddRow(dr, ws, keys, rowCounters[agency]);
}
dr.Close();
}
conn.Close();
}
foreach (var sheet in sheets)
{
var ws = sheet.Value;
ws.Cells[sheet.Value.Dimension.Address].AutoFilter = true;
ws.Column(13).Style.Numberformat.Format = "MM/dd/yyyy"; //TODO: put this in the config file
ws.Column(14).Style.Numberformat.Format = "MM/dd/yyyy"; //TODO: put this in the config file
for (colCounter = 1; colCounter < keys.Count(); colCounter++)
{
sheet.Value.Column(colCounter).AutoFit();
sheet.Value.Column(colCounter).Style.WrapText = true;
}
}
}