public ActionResult SAUpload(HttpPostedFileBase SApostedFile)
{
ShippingAdviceVM shippingAdviceVM = new ShippingAdviceVM();
List <ViewModel.ShippingAdviceVM> shippingAdviceModel = new List <ViewModel.ShippingAdviceVM>();
try
{
if (SApostedFile != null)
{
string path = Server.MapPath("~/ShippingAdviseSheets/");
if (Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
SApostedFile.SaveAs(path + Path.GetFileName(SApostedFile.FileName));
shippingAdviceVM = new ShippingAdviceVM();
ProessSASheet((path + Path.GetFileName(SApostedFile.FileName)));
shippingAdviceModel = GetDetailsFromShippingAdvice();
}
return(View(shippingAdviceModel));
}
catch (Exception ex)
{
//throw ex;
TempData["SuccessMessage"] = ex.Message;
shippingAdviceModel = GetDetailsFromShippingAdvice();
return(View(shippingAdviceModel));
}
}
public void ProessSASheet(string fileFullPath)
{
try
{
Trading.BLL.Trade trade = new BLL.Trade();
trade.TradeDBConnectionString = ConfigurationManager.ConnectionStrings["TradeConnectionString"].ConnectionString;
List <ShippingAdviceVM> currentMothTimeSheetList = new List <ShippingAdviceVM>();
FileStream currentTimeSheetFile = new FileStream(fileFullPath, FileMode.Open, FileAccess.Read);
XSSFWorkbook hssfwb = new XSSFWorkbook(currentTimeSheetFile);
XSSFFormulaEvaluator formula = new XSSFFormulaEvaluator(hssfwb);
ISheet WorkerTimeSheet = hssfwb.GetSheet("mike");
ICellStyle style1 = hssfwb.CreateCellStyle();
IDataFormat dataFormatCustom = hssfwb.CreateDataFormat();
style1.DataFormat = dataFormatCustom.GetFormat("dd/MM/yyyy HH:mm:ss");
Guid companyID = Guid.Parse("CCAA3E3F-4486-4465-B5A1-723F647EAD17");
//DateTime TimeSheetDateValue = WorkerTimeSheet.GetRow(2).GetCell(3).DateCellValue;
//int days = DateTime.DaysInMonth(TimeSheetDateValue.Year, TimeSheetDateValue.Month);
for (int row = 5; row < WorkerTimeSheet.LastRowNum - 1; row++) //Loop the records upto filled row
{
//System.IO.File.AppendAllText(log, "Reading " + row + " row For EMPCode " + getValueFromCell(WorkerTimeSheet.GetRow(row).GetCell(1), formula));
ShippingAdviceVM shippingAdviceVM = new ShippingAdviceVM();
var currentCellValue2 = WorkerTimeSheet.GetRow(row);
// string currentCellValue = getValueFromCell(WorkerTimeSheet.GetRow(row).GetCell(2), formula);
//if (currentCellValue != "" && currentCellValue.ToLower().Equals("total"))
//{
// break;
//}
if (currentCellValue2 == null)
{
continue;
}
string currentCellValue = WorkerTimeSheet.GetRow(row).GetCell(2).StringCellValue;
if (currentCellValue != null && currentCellValue != "") //null is when the row only contains empty cells
{
//int validRow = Regex.Matches(currentCellValue.Substring(0, 1), @"[a-zA-Z]").Count;
//if (validRow == 0)
//{
shippingAdviceVM.CompanyID = companyID;
var valueFrom2thCell = WorkerTimeSheet.GetRow(row).GetCell(2);
if (valueFrom2thCell == null)
{
shippingAdviceVM.SCInvoiceNo = " ";
}
else
{
shippingAdviceVM.SCInvoiceNo = WorkerTimeSheet.GetRow(row).GetCell(2).StringCellValue;
}
var valueFrom3thCell = WorkerTimeSheet.GetRow(row).GetCell(3);
if (valueFrom3thCell == null)
{
shippingAdviceVM.InvoiceAmount = 0;
}
else
{
shippingAdviceVM.InvoiceAmount = Convert.ToDecimal(WorkerTimeSheet.GetRow(row).GetCell(3).NumericCellValue);
}
var valueFrom4thCell = WorkerTimeSheet.GetRow(row).GetCell(4);
if (valueFrom4thCell == null)
{
shippingAdviceVM.Consignee = string.Empty;
}
else if (valueFrom4thCell.CellType == CellType.Numeric)
{
shippingAdviceVM.Consignee = Convert.ToString(WorkerTimeSheet.GetRow(row).GetCell(4).NumericCellValue);
}
else
{
shippingAdviceVM.Consignee = WorkerTimeSheet.GetRow(row).GetCell(4).StringCellValue;
}
var valueFrom5thCell = WorkerTimeSheet.GetRow(row).GetCell(5);
if (valueFrom5thCell == null)
{
shippingAdviceVM.BLDate = DateTime.Now;
}
else
{
shippingAdviceVM.BLDate = DateTime.Parse(WorkerTimeSheet.GetRow(row).GetCell(5).StringCellValue);
}
var valueFrom6thCell = WorkerTimeSheet.GetRow(row).GetCell(6);
if (valueFrom6thCell == null)
{
shippingAdviceVM.ReceivedDate = DateTime.Now;
}
else
{
shippingAdviceVM.ReceivedDate = DateTime.Parse(WorkerTimeSheet.GetRow(row).GetCell(6).StringCellValue);
}
var valueFrom7thCell = WorkerTimeSheet.GetRow(row).GetCell(7);
if (valueFrom7thCell == null)
{
shippingAdviceVM.Shiper = string.Empty;
}
else
{
shippingAdviceVM.Shiper = WorkerTimeSheet.GetRow(row).GetCell(7).StringCellValue;
}
var valueFrom8thCell = WorkerTimeSheet.GetRow(row).GetCell(8);
if (valueFrom8thCell == null)
{
shippingAdviceVM.BLNo = string.Empty;
}
else if (valueFrom8thCell.CellType == CellType.Numeric)
{
shippingAdviceVM.Consignee = Convert.ToString(WorkerTimeSheet.GetRow(row).GetCell(4).NumericCellValue);
}
else
{
shippingAdviceVM.BLNo = WorkerTimeSheet.GetRow(row).GetCell(8).StringCellValue;
}
var valueFrom9thCell = WorkerTimeSheet.GetRow(row).GetCell(9);
if (valueFrom9thCell == null)
{
shippingAdviceVM.Factory = " ";
}
else
{
shippingAdviceVM.Factory = WorkerTimeSheet.GetRow(row).GetCell(9).StringCellValue;
}
var valueFrom10thCell = WorkerTimeSheet.GetRow(row).GetCell(10);
if (valueFrom10thCell == null)
{
shippingAdviceVM.Department = string.Empty;
}
else
{
shippingAdviceVM.Department = WorkerTimeSheet.GetRow(row).GetCell(10).StringCellValue;
}
var valueFrom11thCell = WorkerTimeSheet.GetRow(row).GetCell(11);
if (valueFrom11thCell == null)
{
shippingAdviceVM.Material = string.Empty;
}
else
{
shippingAdviceVM.Material = WorkerTimeSheet.GetRow(row).GetCell(11).StringCellValue;
}
var valueFrom12thcell = WorkerTimeSheet.GetRow(row).GetCell(12);
if (valueFrom12thcell == null)
{
shippingAdviceVM.Quantity = 0;
}
else if (valueFrom12thcell.CellType == CellType.Numeric)
{
shippingAdviceVM.Quantity = Convert.ToInt32(WorkerTimeSheet.GetRow(row).GetCell(12).NumericCellValue);
}
else
{
shippingAdviceVM.Quantity = Convert.ToInt32(WorkerTimeSheet.GetRow(row).GetCell(12).NumericCellValue);
}
var valueFrom13thCell = WorkerTimeSheet.GetRow(row).GetCell(13);
if (valueFrom13thCell == null)
{
shippingAdviceVM.FOB = 0;
}
else
{
shippingAdviceVM.FOB = Convert.ToDecimal(WorkerTimeSheet.GetRow(row).GetCell(13).NumericCellValue);
}
var valueFrom14thCell = WorkerTimeSheet.GetRow(row).GetCell(14);
if (valueFrom14thCell == null)
{
shippingAdviceVM.PurchaseDocumentNo = 0;
}
else
{
shippingAdviceVM.PurchaseDocumentNo = Convert.ToInt32(WorkerTimeSheet.GetRow(row).GetCell(14).NumericCellValue);
}
var valueFrom15thCell = WorkerTimeSheet.GetRow(row).GetCell(15);
if (valueFrom15thCell == null)
{
shippingAdviceVM.Item1 = 0;
}
else
{
shippingAdviceVM.Item1 = Convert.ToInt32(WorkerTimeSheet.GetRow(row).GetCell(15).NumericCellValue);
}
var valueFrom16thcell = WorkerTimeSheet.GetRow(row).GetCell(16);
if (valueFrom16thcell == null)
{
shippingAdviceVM.SAPSO = string.Empty;
}
else
{
shippingAdviceVM.SAPSO = Convert.ToString(WorkerTimeSheet.GetRow(row).GetCell(16).NumericCellValue);
}
var valueFrom17thCell = WorkerTimeSheet.GetRow(row).GetCell(17);
if (valueFrom17thCell == null)
{
shippingAdviceVM.Item2 = 0;
}
else
{
shippingAdviceVM.Item2 = Convert.ToInt32(WorkerTimeSheet.GetRow(row).GetCell(17).NumericCellValue);
}
var valueFrom18thcell = WorkerTimeSheet.GetRow(row).GetCell(16);
if (valueFrom18thcell == null)
{
shippingAdviceVM.SAPDO = string.Empty;
}
else
{
shippingAdviceVM.SAPDO = Convert.ToString(WorkerTimeSheet.GetRow(row).GetCell(18).NumericCellValue);
}
var valueFrom19thCell = WorkerTimeSheet.GetRow(row).GetCell(19);
if (valueFrom19thCell == null)
{
shippingAdviceVM.PInt = 0;
}
else
{
shippingAdviceVM.PInt = Convert.ToInt32(WorkerTimeSheet.GetRow(row).GetCell(19).NumericCellValue);
}
var valueFrom20thcell = WorkerTimeSheet.GetRow(row).GetCell(20);
if (valueFrom20thcell == null)
{
shippingAdviceVM.SLoc = string.Empty;
}
else if (valueFrom20thcell.CellType == CellType.Numeric)
{
shippingAdviceVM.SLoc = Convert.ToString(WorkerTimeSheet.GetRow(row).GetCell(20).NumericCellValue);
}
else
{
shippingAdviceVM.SLoc = WorkerTimeSheet.GetRow(row).GetCell(20).StringCellValue;
}
var valueFrom21stcell = WorkerTimeSheet.GetRow(row).GetCell(21);
if (valueFrom21stcell == null)
{
shippingAdviceVM.Temp1 = string.Empty;
}
else
{
shippingAdviceVM.Temp1 = WorkerTimeSheet.GetRow(row).GetCell(21).StringCellValue;
}
var valueFrom22thCell = WorkerTimeSheet.GetRow(row).GetCell(22);
if (valueFrom22thCell == null)
{
shippingAdviceVM.Seq = 0;
}
else
{
shippingAdviceVM.Seq = Convert.ToInt32(WorkerTimeSheet.GetRow(row).GetCell(22).NumericCellValue);
}
var valueFrom23rdcell = WorkerTimeSheet.GetRow(row).GetCell(23);
if (valueFrom23rdcell == null)
{
shippingAdviceVM.Del = string.Empty;
}
else
{
shippingAdviceVM.Del = WorkerTimeSheet.GetRow(row).GetCell(23).StringCellValue;
}
var valueFrom24thCell = WorkerTimeSheet.GetRow(row).GetCell(24);
if (valueFrom24thCell == null)
{
shippingAdviceVM.Comp = string.Empty;
}
else
{
shippingAdviceVM.Comp = WorkerTimeSheet.GetRow(row).GetCell(24).StringCellValue;
}
//var valueFrom25thcell = WorkerTimeSheet.GetRow(row).GetCell(25);
//if (valueFrom25thcell == null)
// shippingAdviceVM.DeliveryDate = DateTime.Now;
//else
var sd = WorkerTimeSheet.GetRow(row).GetCell(25).StringCellValue;
if (sd == null)
{
shippingAdviceVM.DeliveryDate = DateTime.Now;
}
else
{
shippingAdviceVM.DeliveryDate = DateTime.Parse(sd, new CultureInfo("en-IN", true));
}
currentMothTimeSheetList.Add(shippingAdviceVM);
// }
//Here for sample , I just save the value in "value" field, Here you can write your custom logics...
}
}
if (currentMothTimeSheetList.Count() > 0)
{
trade.SaveShippingAdvice(currentMothTimeSheetList);
TempData["SuccessMessage"] = "ShippingAdvice data appended to DB";
}
}
catch (Exception ex)
{
//TempData["ErrorMessage"] = "There is a Issues on Uploading Workers OT Data .Please contact IT Support";
//Console.Write(ex.Message);
throw ex;
}
}
