// function designs the sheet
private static void designSheet(int rows, int cols, Common.Customer customer)
{
ExcelInerop.Range range = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rows, cols]];
// borders
ExcelInerop.Borders border = range.Borders;
border.LineStyle = ExcelInerop.XlLineStyle.xlContinuous;
border.Weight = 2d;
// font
range.Cells.Font.Name = "Calibri";
range.Cells.Font.Size = "12";
// sheet name
excelSheet.Name = customer.name;
// change range - take first row only
range = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[1, cols]];
range.Cells.Font.Color = Color.White;
range.Cells.Interior.Color = Color.Red;
range.Cells.Font.FontStyle = FontStyle.Bold;
range.Cells.Font.Size = "14";
// auto fit all columns
range.Columns.EntireColumn.AutoFit();
}
// function prepares mail per customer in case it has any orders
private static void prepareOrdersMailToCustomer(Common.Customer customer)
{
DateTime now = DateTime.Now;
string outputFileName = string.Empty;
int rows = 0;
int cols = 0;
// filter the list for certain customer
// filter out past arrivals
// order by arrival date
List <Common.Order> resultList = filterCustomersByName(customer.name, customer.alias).Where(x => x.arrivalDate > now)
.OrderBy(x => x.arrivalDate)
.Distinct()
.ToList();
// check if customer has orders
if (resultList.Count == 0)
{
OrdersParser._Form.log(string.Format("{0}: no new orders for this customer - mail won't be sent", customer.name));
return;
}
OrdersParser._Form.log(string.Format("{0}: {1} new orders found", customer.name, resultList.Count));
// not all the columns are needed in the report - remove some
List <Common.OrderReport> targetResList = resultList.ConvertAll(x => new Common.OrderReport
{
jobNo = x.jobNo,
shipper = x.shipper,
consignee = x.consignee,
customerRef = x.customerRef,
tankNum = x.tankNum,
activity = x.activity,
loadingDate = x.loadingDate,
fromCountry = x.fromCountry,
fromPlace = x.fromPlace,
sailingDate = x.sailingDate,
toCountry = x.toCountry,
toPlace = x.toPlace,
arrivalDate = x.arrivalDate,
productName = x.productName,
vessel = x.vessel,
voyage = x.voyage,
});
// prepare 2d array for excel print
object[,] valuesArray = Utils.generateObjectFromList <Common.OrderReport>(targetResList, out rows, out cols);
// create new excel file with this data
// to make sure that file name is unique add high time accuracey
string extendedTimeFormat = string.Concat(Common.DATE_FORMAT, "_fffffff");
outputFileName = Path.Combine(Utils.resultsDirectoryPath, string.Format("{0}_{1}.{2}", customer.name, now.ToString(extendedTimeFormat), "xlsx"));
Excel.generateCustomerFile(valuesArray, rows, cols, customer, outputFileName);
// send mail to customer
MailDetails mailDetails = new MailDetails();
mailDetails.mailRecepient = customer;
mailDetails.mailType = Common.MailType.Reports;
mailDetails.bodyParameters = new Dictionary <string, string>()
{
{ "arrivalDate", resultList.FirstOrDefault().arrivalDate.ToString(Common.DATE_FORMAT) },
{ "totalNumOfOrders", resultList.Count.ToString() }
};
mailDetails.subject = "דו'ח סטטוס הזמנות";
mailDetails.attachments = new List <string>()
{
outputFileName
};
mailDetails.bHighImportance = true;
// send mail
sendMail(mailDetails);
}
// function generates excel file with specific customer orders
public static void generateCustomerFile(dynamic valuesArray, int rows, int cols, Common.Customer customer, string outputFileName)
{
// can be null in the first load
if (excelApp == null)
{
init();
}
try
{
workBook = excelApp.Workbooks.Add();
excelSheet = workBook.ActiveSheet;
}
catch (Exception e)
{
OrdersParser._Form.log(string.Format("Failed to add new workbook into excel. Error: {0}", e.Message), OrdersParser.LogLevel.Error);
dispose();
return;
}
// alignment
excelApp.DefaultSheetDirection = (int)ExcelInerop.Constants.xlLTR;
excelSheet.DisplayRightToLeft = false;
// fill data
fillExcelValuesFromArray(valuesArray, rows, cols);
// sheet design
designSheet(rows, cols, customer);
// save to file
workBook.SaveAs(outputFileName, // FileName
ExcelInerop.XlFileFormat.xlWorkbookDefault, // FileFormat
Type.Missing, // Password
Type.Missing, // WriteResPassword
false, // ReadOnlyRecommended
false, // CreateBackup
ExcelInerop.XlSaveAsAccessMode.xlNoChange, // AccessMode
ExcelInerop.XlSaveConflictResolution.xlLocalSessionChanges, // ConflictResolution
Type.Missing, // AddToMru
Type.Missing, // TextCodepage
Type.Missing, // TextVisualLayout
false); // Local
workBook.Close();
}
// function extracts customer list from static DB (excel file)
private static void getCustomersDetails()
{
// go to specific sheet
try
{
excelSheet = workBook.Sheets[CUSTOMERS_SHEET_NAME];
}
catch (Exception e)
{
OrdersParser._Form.log(string.Format("Failed to open excel sheet: {0}. Error: {1}", CUSTOMERS_SHEET_NAME, e.Message), OrdersParser.LogLevel.Error);
dispose();
return;
}
// go over the whole table filling the customerLust
// start from 3, since
// dynamic array starts from [1,1]
// row 1 is full of nulls
// row 2 has the column names
string val = string.Empty;
int col = 1;
int effectiveDataOffset = 3;
Common.Customer customer = new Common.Customer();
Common.customerList = new List <Common.Customer>();
int totalNumOfRows = excelSheet.UsedRange.Rows.Count - 2;
dynamic sheet = excelSheet.UsedRange.Value2;
try
{
for (int row = 0; row < totalNumOfRows; row++)
{
// name
val = sheet[row + effectiveDataOffset, col];
if (string.IsNullOrEmpty(val) == false)
{
customer.name = val;
}
// shipper/alias (if exists)
val = sheet[row + effectiveDataOffset, col + 1];
if (string.IsNullOrEmpty(val) == false)
{
customer.alias = val;
}
// to
val = sheet[row + effectiveDataOffset, col + 2];
if (string.IsNullOrEmpty(val) == false)
{
customer.to.Add(val);
}
// cc
val = sheet[row + effectiveDataOffset, col + 3];
if (string.IsNullOrEmpty(val) == false)
{
customer.cc.Add(val);
}
// report needed?
val = sheet[row + effectiveDataOffset, col + 4];
if (string.IsNullOrEmpty(val) == false)
{
if (val.Trim().ToLower().Equals("yes"))
{
customer.bSendReport = true;
}
}
// destination port
val = sheet[row + effectiveDataOffset, col + 5];
if (string.IsNullOrEmpty(val) == false)
{
// destination port found
if (val.Trim().ToLower().Equals("ashdod"))
{
customer.destinationPort = PortService.PortName.Ashdod;
}
if (val.Trim().ToLower().Equals("haifa"))
{
customer.destinationPort = PortService.PortName.Haifa;
}
}
// check that last entry for this customer (next should be not empty or out of bounds)
if ((row == (totalNumOfRows - 1)) || (string.IsNullOrEmpty(sheet[row + effectiveDataOffset + 1, col]) == false))
{
// this is the last customer, add to list and zero struct
Common.customerList.Add(customer);
// nullify and create new
customer = null;
customer = new Common.Customer();
}
}
}
catch (Exception e)
{
OrdersParser._Form.log(string.Format("Failed parsing private DB. Error: {0}", e.Message), OrdersParser.LogLevel.Error);
dispose();
return;
}
// success
OrdersParser._Form.log(string.Format("Found {0} customers in the private DB", Common.customerList.Count));
OrdersParser._Form.log(string.Format("Need to send reports to {0} customers", Common.customerList.Count(x => x.bSendReport == true)));
// update global list outside of this APP domain
ExcelRemote.RemoteExeclController.DataUpdater.updateCustomerList(Common.customerList);
excelSheet = null;
}
