private JewelData ProcessExcelData(IList <ExcelData> rows) { var data = new JewelData(); if (rows.Count == 0) { return(data); } for (int i = 0; i < rows.Count(); i++) { var row = rows[i]; var item = new JewelData.Item(); if (row.SRNO == 0) { var prev = data.Items.LastOrDefault(); if (prev != null) { if (CostingRate.CostingItems.Any(x => x.ProductCategory == ProductCategory.Stone && x.Particulars == prev.STONETYPE) == false) { prev.ErrorMessage = String.Format(@"Costing is not defined for stone type {0}, for record {1}", row.STONETYPE, row.SRNO); continue; } prev.SIEVESIZE.Add(row.SIEVESIZE); prev.DIAPCSDETAIL.Add(row.DIAPCSDETAIL); prev.DIAWTDETAIL.Add(row.DIAWTDETAIL); continue; } item.ErrorMessage = "SrNo no is missing for record " + row.SRNO; data.Items.Add(item); continue; } // Validation Check Start if (row.SRNO != 0) { if (row.TYPE.IsEmpty()) { item.ErrorMessage = "Metal type is missing for record " + row.SRNO; data.Items.Add(item); continue; } if (row.GRWT == 0.0M) { item.ErrorMessage = "Total weight is missing for record " + row.SRNO; data.Items.Add(item); continue; } if (row.KT.IsEmpty()) { item.ErrorMessage = "KT detail is missing for record" + row.SRNO; data.Items.Add(item); continue; } } item.SRNO = row.SRNO; item.CERTNO = row.CERTNO; item.DESIGNNO = row.DESIGNNO; item.GRWT = row.GRWT; if (CostingRate.CostingItems.Any(x => x.ProductCategory == ProductCategory.Labour && x.Particulars == row.TYPE) == false) { item.ErrorMessage = String.Format("Costing is not defined for type {0}, for record {1}", row.TYPE, row.SRNO); data.Items.Add(item); continue; } item.TYPE = row.TYPE; if (CostingRate.CostingItems.Any(x => x.ProductCategory == ProductCategory.Metal && x.Particulars == row.KT) == false) { item.ErrorMessage = String.Format("Costing is not defined for Metal type {0}, for record {1}", row.KT, row.SRNO); data.Items.Add(item); continue; } item.KT = row.KT; if (row.STONETYPE.IsEmpty() == false) { item.STONETYPE = row.STONETYPE; if (CostingRate.CostingItems.Any(x => x.ProductCategory == ProductCategory.Stone && x.Particulars == row.STONETYPE) == false) { item.ErrorMessage = String.Format(@"Costing is not defined for stone type {0}, for record {1} " , row.STONETYPE, row.SRNO); data.Items.Add(item); continue; } item.TOTALDIAPCS = row.TOTALDIAPCS; item.TOTALDIAWT = row.TOTALDIAWT; if (row.TOTALDIAPCS == 00) { item.ErrorMessage = String.Format(@"Invalid dia pcs for stone type {0}, for record {1} " , row.STONETYPE, row.SRNO); data.Items.Add(item); continue; } if (row.TOTALDIAWT == 00M) { item.ErrorMessage = String.Format(@"Invalid dia pcs for stone type {0}, for record {1} " , row.STONETYPE, row.SRNO); data.Items.Add(item); continue; } if (row.DIAPCSDETAIL == 00) { item.ErrorMessage = String.Format(@"Invalid dia pcs for stone type {0}, for record {1} " , row.STONETYPE, row.SRNO); data.Items.Add(item); continue; } if (row.DIAWTDETAIL == 00M) { item.ErrorMessage = String.Format(@"Invalid dia pcs for stone type {0}, for record {1} " , row.STONETYPE, row.SRNO); data.Items.Add(item); continue; } if (row.SIEVESIZE.IsEmpty() == false && CostingRate.CostingItems.Any(x => x.ProductCategory == ProductCategory.Stone && x.Particulars == row.STONETYPE && x.ConfigurationValue == row.SIEVESIZE) == false) { item.ErrorMessage = String.Format(@"Costing is not defined for stone type {0} with sieve group {1}, for record {2} " , row.STONETYPE, row.SIEVESIZE, row.SRNO); data.Items.Add(item); continue; } item.SIEVESIZE.Add(row.SIEVESIZE); item.DIAPCSDETAIL.Add(row.DIAPCSDETAIL); item.DIAWTDETAIL.Add(row.DIAWTDETAIL); } item.CPCS = row.CPCS; item.CWT = row.CWT; item.STAMP = row.STAMP; item.BRAND = row.BRAND; data.Items.Add(item); } // Fill sieve group data to the list var correctData = data.Items.Where(x => x.IsValid); foreach (var item in correctData) { if (!item.HasDiamond) { continue; } var tmpFlag = false; for (int i = 0; i < item.SIEVESIZE.Count; i++) { var sievesz = item.SIEVESIZE[i]; var diapcs = item.DIAPCSDETAIL[i]; var diawt = item.DIAWTDETAIL[i]; if (diapcs == 0) { continue; } var perpcsrate = diawt / diapcs; var sieveGroups = LocalStore.SieveGroups.GetSieveGroupName(perpcsrate); if (sievesz.IsEmpty()) { item.SIEVESIZE[i] = sieveGroups; } if (item.SIEVESIZE[i].IsEmpty()) { item.ErrorMessage = String.Format(@"Costing is not defined for stone type {0}, for record {1} " , item.STONETYPE, item.SRNO); tmpFlag = true; break; } } // ReSharper disable RedundantJumpStatement if (tmpFlag) { continue; } // ReSharper restore RedundantJumpStatement } return(data); }
private void openFileDialog1_FileOk(object sender, CancelEventArgs e) { PurchaseTransactionItems = new List <JewelTransaction>(); txtUploadError.Text = String.Empty; txtUploadError.Height = 1; dgvJewel.Rows.Clear(); txtUploadError.Text = Resources.frmExcelCostingRates_openFileDialog1_FileOk_Following_error_s__occoured_while_processing_your_excel + Environment.NewLine + Resources.frmExcelCostingRates_openFileDialog1_FileOk_ + Environment.NewLine + Environment.NewLine; var excel = new ExcelQueryFactory(openFileDialog1.FileName); var uploadData = excel.Worksheet <ExcelData>("COSTING FORMAT"); try { Queryable.Count(uploadData); } catch (Exception ex) { MessageBox.Show(Resources.frmExcelCostingRates_openFileDialog1_FileOk_ + "\n" + ex.Message); return; } var allRows = ProcessExcelData(uploadData.ToList()); var rows = new JewelData { Items = allRows.Items.Where(x => x.IsValid).ToList() }; if (allRows.Items.Count() != allRows.Items.Select(x => x.SRNO).Distinct().Count()) { MessageBox.Show(Resources.frmExcelCostingRates_openFileDialog1_FileOk_Please_ensure_excel_file_is_numbered_correctly_); return; } foreach (var row in rows.Items) { var item = new ItemDetail { TotalWeight = row.GRWT, CertificateNumber = row.CERTNO, DesignCode = row.DESIGNNO, ItemCategory = row.TYPE, ItemDescription = row.TYPE }; var metalRate = CostingRate.CostingItems.Single(x => x.ProductCategory == ProductCategory.Metal && x.Particulars == row.KT); var labour = CostingRate.CostingItems.FirstOrDefault(x => x.Particulars == row.TYPE && x.ProductCategory == ProductCategory.Labour); if (labour == null) { row.ErrorMessage = String.Format("Invalid metal type, for record {0}", row.SRNO); continue; } decimal labourCharges; var labourCost = labour.Amount; var minlabourCost = labour.MinimumAmount; var netMetalWt = Math.Max(row.GRWT - row.TOTALDIAWT / 5, 0); if (netMetalWt * labourCost <= minlabourCost) { labourCharges = Math.Max(minlabourCost, 0); } else { labourCharges = netMetalWt * labourCost; } item.LabourCharges = labourCharges; item.MetalDetail = new MetalDetail { MetalNetWt = netMetalWt, MetalType = row.KT, MetalNetAmount = Math.Max(row.GRWT - row.TOTALDIAWT / 5, 0) * Math.Max(metalRate.Amount, 0), MetalKT = row.KT }; if (row.HasDiamond) { var diawt = row.TOTALDIAWT; var tempstncnt = row.DIAPCSDETAIL.Sum(); if (tempstncnt != row.TOTALDIAPCS) { row.ErrorMessage = String.Format("Invalid diamond pcs count for type {0}, for record {1}", row.TYPE, row.SRNO); continue; } var tempstnwt = row.DIAWTDETAIL.Sum(); if (tempstnwt != row.TOTALDIAWT) { row.ErrorMessage = String.Format("Invalid diamond pcs wt for type {0}, for record {1}", row.TYPE, row.SRNO); continue; } var metailDetail = new List <StoneMetaDetail>(); var tempflg = false; for (int i = 0; i < row.SIEVESIZE.Count; i++) { var svSize = row.SIEVESIZE[i]; if (CostingRate.CostingItems.Any(x => x.ProductCategory == ProductCategory.Stone && x.Particulars == row.STONETYPE && x.ConfigurationValue == svSize) == false) { row.ErrorMessage = String.Format("SeiveSize {0} is not defined for record {1}", row.TYPE, row.SRNO); tempflg = true; break; } var stonePcs = row.DIAPCSDETAIL[i]; var stoneWt = row.DIAWTDETAIL[i]; var price = CostingRate.CostingItems.Single(x => x.ProductCategory == ProductCategory.Stone && x.Particulars == row.STONETYPE && x.ConfigurationValue == svSize); var stoneValue = Math.Max((stoneWt / stoneWt) * price.Amount, 0); metailDetail.Add(new StoneMetaDetail { StoneSieveSz = svSize, StonePcs = stonePcs, StoneWt = stoneWt, StoneValue = stoneValue }); } if (tempflg) { continue; } var stoneChart = new StoneChart { StoneMetaDetailList = metailDetail }; var cost = CostingRate.CertificateRate.Items.SingleOrDefault(x => x.RangeMinValue <= diawt && x.RangeMaxValue >= diawt); if (cost == null) // for out of scope { item.CertificateCharges = Math.Max(CostingRate.CertificateRate.Items.Max(x => x.Amount) * diawt, 0); } else if (cost.RangeMinValue == CostingRate.CertificateRate.Items.Min(x => x.RangeMinValue)) { item.CertificateCharges = Math.Max(cost.Amount, 0); } else { item.CertificateCharges = Math.Max(cost.Amount * diawt, 0); } item.StoneDetail = new StoneDetail { StoneType = row.STONETYPE, StoneNetWt = row.TOTALDIAWT, TotalStonePcs = row.TOTALDIAPCS, StoneChart = stoneChart, StoneNetAmount = metailDetail.Any() ? Math.Max(metailDetail.Sum(x => x.StoneValue) * row.TOTALDIAWT, 0) : 0 }; } item.StampingCharges = Math.Max(row.STAMP, 0); var totalPrice = Math.Max(item.StoneDetail.StoneNetAmount + item.CertificateCharges + item.MetalDetail.MetalNetAmount + labourCharges + item.StampingCharges , 0); var jewelTran = new JewelTransaction { CostingDetail = _costingDetails, TransactionType = TransactionType.PurchaseTransaction, KT = row.KT, CertificateNumber = row.CERTNO, DesignCode = row.DESIGNNO, StonePcs = row.HasDiamond ? row.TOTALDIAPCS : 0, StoneWeight = row.HasDiamond ? row.TOTALDIAWT : 0, //CStonePcs = HasStoneMode && IsColorStoneMode ? Convert.ToInt32(row.GetCellValue(_columncstonepcs)) : 0, //CStoneWeight = HasStoneMode && IsColorStoneMode ? Convert.ToDecimal(row.GetCellValue(_columncstonewt)) : 0, JewelType = row.TYPE, JewelItemCategory = MetalType, MetalWeight = netMetalWt, //MetalColor = row.MetalColor, TotalWeight = row.GRWT, Properties = new TransactionDetails { ItemDetails = item }, TransactionPartyRef = ((Supplier)cboCustomer.SelectedItem).SupplierCode, TransactionDate = dtCosting.Value, TotalAmount = totalPrice }; PurchaseTransactionItems.Add(jewelTran); } if (allRows.Items.Any(x => !x.IsValid)) { txtUploadError.Height = 55; foreach (var row in allRows.Items.Where(x => x.IsValid == false)) { txtUploadError.Text = txtUploadError.Text + row.ErrorMessage + Environment.NewLine; } } else { txtUploadError.Height = 1; } //Bind to grid BindToGrid(); }