private bool ValidateInvoiceNo(ReceivingTransaction item, Guid branchId)
{
var invoiceNo = item.InvoiceNumber;
if (invoiceNo != String.Empty && invoiceNo != null)
{
QueryExpression query = new QueryExpression("gsc_cmn_receivingtransaction");
query.ColumnSet.AddColumns("gsc_invoiceno");
query.Criteria.AddCondition(new ConditionExpression("gsc_invoiceno", ConditionOperator.Equal, invoiceNo));
query.Criteria.AddCondition(new ConditionExpression("gsc_receivingstatus", ConditionOperator.Equal, 100000000));
query.Criteria.AddCondition(new ConditionExpression("gsc_branchid", ConditionOperator.Equal, branchId));
EntityCollection collection = _service.RetrieveMultiple(query);
if (collection.Entities.Count > 0)
{
return(false);
}
}
return(true);
}
private bool IsSameVehicleDetailsWithPO(ReceivingTransaction item, Entity vpoDetail, int counter)
{
var modelCode = item.ReceivingDetails.ModelCode;
var optionCode = item.ReceivingDetails.OptionCode;
var modelYear = item.ReceivingDetails.ModelYear;
var poModelCode = vpoDetail.Contains("gsc_modelcode")
? vpoDetail.GetAttributeValue <String>("gsc_modelcode")
: String.Empty;
var poOptionCode = vpoDetail.Contains("gsc_optioncode")
? vpoDetail.GetAttributeValue <String>("gsc_optioncode")
: String.Empty;
var poModelYear = vpoDetail.Contains("gsc_modelyear")
? vpoDetail.GetAttributeValue <String>("gsc_modelyear")
: String.Empty;
if (modelCode.TrimEnd() != poModelCode.TrimEnd())
{
_logger.Log(LogLevel.Error, "Unable to save row:[{0}], vpo:[{1}]. Model Code provided in template must match Model Code of Purchase Order record.", counter, item.VehiclePurchaseOrderNumber);
return(false);
}
if (optionCode != poOptionCode)
{
_logger.Log(LogLevel.Error, "Unable to save row:[{0}], vpo:[{1}]. Option Code provided in template must match Model Code of Purchase Order record.", counter, item.VehiclePurchaseOrderNumber);
return(false);
}
if (modelYear != poModelYear)
{
_logger.Log(LogLevel.Error, "Unable to save row:[{0}], vpo:[{1}]. Model Year provided in template must match Model Code of Purchase Order record.", counter, item.VehiclePurchaseOrderNumber);
return(false);
}
else
{
return(true);
}
}
private bool IsRowEmpty(ReceivingTransaction row)
{
return(row.GetType().GetProperties().Where(pi => pi.GetValue(row) is string)
.Select(pi => (string)pi.GetValue(row)).Any(value => String.IsNullOrEmpty(value)));
}
public List <ReceivingTransaction> Read()
{
List <ReceivingTransaction> rtList = new List <ReceivingTransaction>();
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(_filePath, false))
{
//Read the first Sheets
Sheet sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild <Sheet>();
Worksheet worksheet = (doc.WorkbookPart.GetPartById(sheet.Id.Value) as WorksheetPart).Worksheet;
IEnumerable <Row> rows = worksheet.GetFirstChild <SheetData>().Descendants <Row>();
foreach (Row row in rows)
{
ReceivingTransaction rt = new ReceivingTransaction();
//Read the first row as header
if (row.RowIndex.Value != 1)
{
int columnIndex = 0;
foreach (Cell cell in row.Descendants <Cell>())
{
// Gets the column index of the cell with data
int cellColumnIndex = (int)GetColumnIndexFromName(GetColumnName(cell.CellReference));
if (columnIndex < cellColumnIndex)
{
do
{
if (columnIndex == 0)
{
rt.DealerCode = string.Empty;
}
if (columnIndex == 1)
{
rt.BranchCode = string.Empty;
}
if (columnIndex == 2)
{
rt.VehiclePurchaseOrderNumber = string.Empty;
}
if (columnIndex == 3)
{
rt.PullOutDate = string.Empty;
}
if (columnIndex == 4)
{
rt.MMPCStatus = string.Empty;
}
if (columnIndex == 5)
{
rt.InvoiceNumber = string.Empty;
}
if (columnIndex == 6)
{
rt.InTransitReceiptDate = string.Empty;
}
if (columnIndex == 7)
{
rt.InvoiceDate = string.Empty;
}
if (columnIndex == 8)
{
rt.InTransitSite = string.Empty;
}
if (columnIndex == 9)
{
rt.ReceivingDetails.ModelCode = string.Empty;
}
if (columnIndex == 10)
{
rt.ReceivingDetails.OptionCode = string.Empty;
}
if (columnIndex == 11)
{
rt.ReceivingDetails.ModelYear = string.Empty;
}
if (columnIndex == 12)
{
rt.ReceivingDetails.ColorCode = string.Empty;
}
if (columnIndex == 13)
{
rt.ReceivingDetails.EngineNumber = string.Empty;
}
if (columnIndex == 14)
{
rt.ReceivingDetails.CSNumber = string.Empty;
}
if (columnIndex == 15)
{
rt.ReceivingDetails.ProductionNumber = string.Empty;
}
if (columnIndex == 16)
{
rt.ReceivingDetails.VIN = string.Empty;
}
columnIndex++;
}while (columnIndex < cellColumnIndex);
}
if (columnIndex == 0)
{
rt.DealerCode = GetCellValue(doc, cell);
}
if (columnIndex == 1)
{
rt.BranchCode = GetCellValue(doc, cell);
}
if (columnIndex == 2)
{
rt.VehiclePurchaseOrderNumber = GetCellValue(doc, cell);
}
if (columnIndex == 3)
{
string cellValue = GetCellValue(doc, cell);
DateTime sample;
if (string.IsNullOrEmpty(cellValue) || DateTime.TryParse(cellValue, out sample))
{
rt.PullOutDate = cellValue;
}
else
{
rt.PullOutDate = DateTime.FromOADate(Convert.ToDouble(cellValue)).ToString();
}
}
if (columnIndex == 4)
{
rt.MMPCStatus = GetCellValue(doc, cell);
}
if (columnIndex == 5)
{
rt.InvoiceNumber = GetCellValue(doc, cell);
}
if (columnIndex == 6)
{
string cellValue = GetCellValue(doc, cell);
DateTime sample;
if (string.IsNullOrEmpty(cellValue) || DateTime.TryParse(cellValue, out sample))
{
rt.InTransitReceiptDate = cellValue;
}
else
{
rt.InTransitReceiptDate = DateTime.FromOADate(Convert.ToDouble(cellValue)).ToString();
}
}
if (columnIndex == 7)
{
string cellValue = GetCellValue(doc, cell);
DateTime sample;
if (string.IsNullOrEmpty(cellValue) || DateTime.TryParse(cellValue, out sample))
{
rt.InvoiceDate = cellValue;
}
else
{
rt.InvoiceDate = DateTime.FromOADate(Convert.ToDouble(cellValue)).ToString();
}
}
if (columnIndex == 8)
{
rt.InTransitSite = GetCellValue(doc, cell);
}
if (columnIndex == 9)
{
rt.ReceivingDetails.ModelCode = GetCellValue(doc, cell);
}
if (columnIndex == 10)
{
rt.ReceivingDetails.OptionCode = GetCellValue(doc, cell);
}
if (columnIndex == 11)
{
rt.ReceivingDetails.ModelYear = GetCellValue(doc, cell);
}
if (columnIndex == 12)
{
rt.ReceivingDetails.ColorCode = GetCellValue(doc, cell);
}
if (columnIndex == 13)
{
rt.ReceivingDetails.EngineNumber = GetCellValue(doc, cell);
}
if (columnIndex == 14)
{
rt.ReceivingDetails.CSNumber = GetCellValue(doc, cell);
}
if (columnIndex == 15)
{
rt.ReceivingDetails.ProductionNumber = GetCellValue(doc, cell);
}
if (columnIndex == 16)
{
rt.ReceivingDetails.VIN = GetCellValue(doc, cell);
}
columnIndex++;
}
}
rtList.Add(rt);
}
}
rtList.RemoveAt(0);
return(rtList);
}
