protected string GenerateServiceReport(int? orderID)
{
int signatureMaxWidth = 200, signatureMaxHeight = 180;
double excelBorderWidth = 0.01;
string returnFilePath = null;
IOrderService orderService = AutoSessionServiceFactory.GetOrderService(ApplicationSetting.Current.DefaultConnectionString);
string templateFilePath = System.Web.Hosting.HostingEnvironment.MapPath("~/ReportTemplate/REC_CRM_Service_Report_Template.xlsx");
try
{
SLDocument template = new SLDocument(templateFilePath, "Sheet1");
ServiceReportOrderDTO order = orderService.GetServiceReportOrderByID(orderID.Value);
string completionDate = order.dCompletionDate.HasValue ? order.dCompletionDate.Value.ToString("yyyy/MM/dd") : string.Empty;
string customerName = string.IsNullOrEmpty(order.sCustomerName) ? string.Empty : order.sCustomerName;
string customerCode = string.IsNullOrEmpty(order.sCustomerCode) ? string.Empty : order.sCustomerCode;
string workingAddress = string.IsNullOrEmpty(order.sAddress) ? string.Empty : order.sAddress;
string contactPerson = string.IsNullOrEmpty(order.sContactPersonName) ? string.Empty : order.sContactPersonName;
string model = string.IsNullOrEmpty(order.sModel) ? string.Empty : order.sModel;
string fault = string.IsNullOrEmpty(order.sFault) ? string.Empty : order.sFault;
string remark = string.IsNullOrEmpty(order.sRemark) ? string.Empty : order.sRemark;
string repair = string.IsNullOrEmpty(order.sRepair) ? string.Empty : order.sRepair;
string report = string.IsNullOrEmpty(order.sReport) ? string.Empty : order.sReport;
string reviewerName = string.IsNullOrEmpty(order.sReviewerName) ? string.Empty : order.sReviewerName;
string technicians = string.IsNullOrEmpty(order.sTechnicianNameString) ? string.Empty : order.sTechnicianNameString;
string startTime = order.tStartTime.HasValue ? order.tStartTime.Value.ToString(@"hh\:mm") : string.Empty;
string endTime = order.tEndTime.HasValue ? order.tEndTime.Value.ToString(@"hh\:mm") : string.Empty;
string signDate = order.dReportSignDate.HasValue ? order.dReportSignDate.Value.ToString("yyyy/MM/dd") : string.Empty;
string customerOpinion = string.IsNullOrEmpty(order.sCustomerOpinion) ? string.Empty : order.sCustomerOpinion;
string receipt = string.IsNullOrEmpty(order.sReceipt) ? string.Empty : order.sReceipt;
#region Fill Data
List<SLDefinedName> definedNames = template.GetDefinedNames();
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "RepairDate"), completionDate);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "CustomerName"), customerName);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "CustomerCode"), customerCode);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "WorkingAddress"), workingAddress);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "ContactPerson"), contactPerson);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "Model"), model);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "Fault"), fault);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "Remark"), remark);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "Repair"), repair);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "Report"), report);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "Technicians"), technicians);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "StartTime"), startTime);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "EndTime"), endTime);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "ReviewerName"), reviewerName);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "SignDate"), signDate);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "CustomerOpinion"), customerOpinion);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "Receipt"), receipt);
#region Insert Signature
string signaturePath = ApplicationSetting.Current.RootFolderPath + order.sReportSignPath;
string signatureCellReference = this.ConvertSingleCellDefinedNameCellReference(definedNames, "CustomerSignature");
int signatureColumnIndex = SLConvert.ToColumnIndex(signatureCellReference);
int signatureRowIndex = int.Parse(signatureCellReference.Substring(1));
try
{
//Get Signature Dimension
System.Drawing.Bitmap bm = new System.Drawing.Bitmap(signaturePath);
double pictureHeight = bm.Height;
double pictureWidth = bm.Width;
bm.Dispose();
int scale = 0;
if (pictureHeight / pictureWidth > ((double)signatureMaxHeight) / ((double)signatureMaxWidth))
{
scale = (int)(((double)signatureMaxHeight / pictureHeight) * 100.0);
}
else
{
scale = (int)(((double)signatureMaxWidth / pictureWidth) * 100.0);
}
SLPicture pic = new SLPicture(signaturePath);
pic.SetPosition(signatureRowIndex - 1 + excelBorderWidth, signatureColumnIndex - 1 + excelBorderWidth);
pic.ResizeInPercentage(scale, scale);
template.InsertPicture(pic);
}
catch (Exception ex)
{
//File path not correct or file not exist
}
#endregion Insert Signature
#endregion Fill Data
#region Auto Fit Row
for (int i = 9; i <= 13; i++)
{
template.AutoFitRow(i);
}
#endregion Auto Fit Row
returnFilePath = string.Format(@"{0}\ServiceReport_{1}_{2}.xlsx", _serviceReportTempFolderPath, orderID, DateTime.Now.ToString("yyyyMMddHHmmss"));
template.SaveAs(returnFilePath);
}
catch (Exception e)
{
LogManager.Write(e, "RecWomCrm", "System");
returnFilePath = null;
}
return returnFilePath;
}
