private void ExportShippingCheckExecute()
{
List <StoreSearchData <StoreSearchColorDetail> > excelDailyShippedList = ExcelModule.GetExcelDailyShippedList(ShippingCheckDate);
IEnumerable <TrashShipped> trashShipped = TrashModule.GetTrashShippedQuantitySum(ShippingCheckDate, ShippingCheckDate);
ExternalDataHelper externalDataHelper = new ExternalDataHelper();
IEnumerable <TextileNameMapping> textileNameMappings = externalDataHelper.GetTextileNameMappings();
List <OriginalSource> trashItems = new List <OriginalSource>();
foreach (TrashShipped shipped in trashShipped)
{
trashItems.Add(new OriginalSource
{
DateTime = shipped.IN_DATE,
TextileNo = shipped.I_01,
TextileColorName = shipped.I_03,
Weight = shipped.Quantity
});
}
List <Container> trashPrimary = new List <Container>();
foreach (OriginalSource trashItem in trashItems)
{
int priviousDistance = 10;
string textileName = string.Empty;
string textileColor = string.Empty;
int shippedCount = 0;
foreach (StoreSearchData <StoreSearchColorDetail> excelDailyShippedItem in excelDailyShippedList)
{
TextileNameMapping textileNameMapping = textileNameMappings.ToList().Find(f => f.Inventory.Contains(excelDailyShippedItem.TextileName)) ?? new TextileNameMapping();
foreach (StoreSearchColorDetail colorDetail in excelDailyShippedItem.StoreSearchColorDetails)
{
string accountMapping = textileNameMapping.Account == null ? string.Empty : textileNameMapping.Account.FirstOrDefault();
if (trashItem.TextileColorName == string.Concat(accountMapping.Split('*')[0], colorDetail.ColorName.Split('-')[0]))
{
textileColor = colorDetail.ColorName;
textileName = excelDailyShippedItem.TextileName;
priviousDistance = 0;
shippedCount = colorDetail.ShippedCount;
break;
}
}
}
trashPrimary.Add(new Container()
{
OriginalSource = trashItem,
TextileName = textileName,
ColorName = textileColor,
ShippedCount = shippedCount,
Distance = priviousDistance
});
}
List <Container> excelPrimary = new List <Container>();
foreach (StoreSearchData <StoreSearchColorDetail> excelDailyShippedItem in excelDailyShippedList)
{
var priviousDistance = 10;
var textileName = string.Empty;
var textileColor = string.Empty;
foreach (var colorDetail in excelDailyShippedItem.StoreSearchColorDetails)
{
TextileNameMapping textileNameMapping = textileNameMappings.ToList().Find(f => f.Inventory.Contains(excelDailyShippedItem.TextileName)) ?? new TextileNameMapping();
OriginalSource originalSource = new OriginalSource();
foreach (var trashItem in trashItems)
{
string accountMapping = textileNameMapping.Account == null ? string.Empty : textileNameMapping.Account.FirstOrDefault();
if (trashItem.TextileColorName == string.Concat(accountMapping.Split('*')[0], colorDetail.ColorName.Split('-')[0]))
{
originalSource.DateTime = trashItem.DateTime;
originalSource.TextileColorName = trashItem.TextileColorName;
originalSource.Weight = trashItem.Weight;
originalSource.TextileNo = trashItem.TextileNo;
break;
}
}
excelPrimary.Add(new Container()
{
OriginalSource = originalSource,
TextileName = excelDailyShippedItem.TextileName,
ColorName = colorDetail.ColorName,
ShippedCount = colorDetail.ShippedCount,
Distance = priviousDistance
});
}
}
ExcelHelper excelHelper = new ExcelHelper();
IWorkbook wb = new XSSFWorkbook();
ICellStyle positionStyle = wb.CreateCellStyle();
positionStyle.WrapText = true;
positionStyle.Alignment = HorizontalAlignment.Center;
positionStyle.VerticalAlignment = VerticalAlignment.Center;
ICellStyle estyle = wb.CreateCellStyle();
estyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
estyle.FillPattern = FillPattern.SolidForeground;
ICellStyle a2style = wb.CreateCellStyle();
a2style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Coral.Index;
a2style.FillPattern = FillPattern.SolidForeground;
List <ExcelRowContent> trashPrimaryExcelRowContent = new List <ExcelRowContent>();
foreach (var item in trashPrimary.OrderByDescending(t => t.OriginalSource.TextileColorName == null).ThenBy(t => t.TextileName).ThenBy(o => o.ColorName))
{
var approximateNumber = item.OriginalSource.Weight / 20;
var round = Math.Round(approximateNumber, 0, MidpointRounding.AwayFromZero);
var isEqual = round == item.ShippedCount;
ExcelRowContent excelCellContents = new ExcelRowContent
{
ExcelCellContents = new List <ExcelCellContent>
{
new ExcelCellContent {
CellValue = item.OriginalSource.TextileColorName, CellStyle = positionStyle
},
new ExcelCellContent {
CellValue = item.OriginalSource.Weight.ToString(), CellStyle = positionStyle
},
new ExcelCellContent {
CellValue = (approximateNumber).ToString(), CellStyle = positionStyle
},
new ExcelCellContent {
CellValue = item.TextileName, CellStyle = positionStyle
},
new ExcelCellContent {
CellValue = item.ColorName, CellStyle = positionStyle
},
new ExcelCellContent {
CellValue = item.ShippedCount.ToString(), CellStyle = isEqual ? positionStyle : estyle
},
}
};
trashPrimaryExcelRowContent.Add(excelCellContents);
}
;
List <ExcelRowContent> excelPrimaryExcelRowContent = new List <ExcelRowContent>();
foreach (var item in excelPrimary.OrderByDescending(t => t.OriginalSource.TextileColorName == null).ThenBy(t => t.TextileName).ThenBy(o => o.ColorName))
{
var approximateNumber = item.OriginalSource.Weight / 20;
var round = Math.Round(approximateNumber, 0, MidpointRounding.AwayFromZero);
var isEqual = round == item.ShippedCount;
ExcelRowContent excelCellContents = new ExcelRowContent
{
ExcelCellContents = new List <ExcelCellContent>
{
new ExcelCellContent {
CellValue = item.OriginalSource.TextileColorName, CellStyle = positionStyle
},
new ExcelCellContent {
CellValue = item.OriginalSource.Weight.ToString(), CellStyle = positionStyle
},
new ExcelCellContent {
CellValue = (approximateNumber).ToString(), CellStyle = positionStyle
},
new ExcelCellContent {
CellValue = item.TextileName, CellStyle = positionStyle
},
new ExcelCellContent {
CellValue = item.ColorName, CellStyle = positionStyle
},
new ExcelCellContent {
CellValue = item.ShippedCount.ToString(), CellStyle = isEqual ? positionStyle : estyle
}
}
};
excelPrimaryExcelRowContent.Add(excelCellContents);
}
;
ExcelContent excelContent = new ExcelContent
{
FileName = string.Concat("庫存對照清單", ShippingCheckDate.ToString("yyyy-MM-dd")),
ExcelSheetContents = new List <ExcelSheetContent>
{
new ExcelSheetContent
{
SheetName = "Super為主",
ExcelColumnContents = new List <ExcelColumnContent>
{
new ExcelColumnContent
{
CellValue = "Super布種名稱顏色",
Width = 6450
},
new ExcelColumnContent
{
CellValue = "出貨重量",
Width = 2800
},
new ExcelColumnContent
{
CellValue = "約略出貨數",
Width = 2000
},
new ExcelColumnContent
{
CellValue = "布種名稱",
Width = 4550
},
new ExcelColumnContent
{
CellValue = "顏色",
Width = 5550
},
new ExcelColumnContent
{
CellValue = "出貨數量",
Width = 1850
}
},
ExcelRowContents = trashPrimaryExcelRowContent
},
new ExcelSheetContent
{
SheetName = "Excel為主",
ExcelColumnContents = new List <ExcelColumnContent>
{
new ExcelColumnContent
{
CellValue = "Super布種名稱顏色",
Width = 6450
},
new ExcelColumnContent
{
CellValue = "出貨重量",
Width = 2800
},
new ExcelColumnContent
{
CellValue = "約略出貨數",
Width = 2000
},
new ExcelColumnContent
{
CellValue = "布種名稱",
Width = 4550
},
new ExcelColumnContent
{
CellValue = "顏色",
Width = 5550
},
new ExcelColumnContent
{
CellValue = "出貨數量",
Width = 1850
}
},
ExcelRowContents = excelPrimaryExcelRowContent
}
}
};
excelHelper.CreateExcelFile(wb, excelContent);
}
private void ButtonExportExecute()
{
_trashShippeds.AddRange(TrashModule.GetTrashShippedQuantitySum(DatePickerBegin, DatePickerEnd));
#region OleDB
//string databaseDirectory = AppSettingConfig.DbfFilePath();
//string sql4 = "SELECT invosub.IN_DATE,invosub.I_01,item.I_03,SUM(invosub.QUANTITY) FROM INVOSUB.dbf invosub " +
// "INNER JOIN ITEM.dbf item ON invosub.I_01 = item.I_01 AND invosub.F_01 = item.F_01 " +
// "WHERE invosub.IN_DATE Between cDate('" + DatePickerBegin.ToString() + "') and cDate('" + DatePickerEnd.ToString() + "') " +
// "GROUP BY invosub.IN_DATE,invosub.I_01,item.I_03 " +
// "ORDER BY invosub.IN_DATE,invosub.I_01";
//DataTable dt4 = GetOleDbDbfDataTable(databaseDirectory, sql4);
//DataGridInCash_Copy3.ItemsSource = dt4.DefaultView;
#endregion
var originalSources = new List <OriginalSource>();
foreach (var shipped in _trashShippeds)
{
originalSources.Add(new OriginalSource
{
DateTime = shipped.IN_DATE,
TextileNo = shipped.I_01,
TextileColorName = shipped.I_03,
Weight = shipped.Quantity
});
}
var filterText = FilterText;
var filterOriginalSources = string.IsNullOrEmpty(filterText) == true?originalSources.Select(s => s.TextileNo).Distinct() : originalSources.Where(w => w.TextileColorName.Contains(filterText)).Select(s => s.TextileNo).Distinct();;
var everyDate = originalSources.Select(s => s.DateTime).Distinct();
var everyTextileNo = filterOriginalSources;
var textileDatas = new List <TextileData>();
foreach (var date in everyDate)
{
textileDatas.Add(new TextileData
{
DateTime = date,
TextileColors = originalSources.Where(w => w.DateTime == date).Select(s => new TextileColor {
TextileNo = s.TextileNo, Weight = s.Weight, TextileColorName = s.TextileColorName
}).ToList()
});
}
IWorkbook wb = new XSSFWorkbook();
ISheet ws = wb.CreateSheet("Report");
XSSFRow row = (XSSFRow)ws.CreateRow(0);
var textileNameNumber = 1;
ExcelHelper.CreateCell(row, 0, "時間", null);
var priviousWeightList = new Dictionary <string, double>();
foreach (var item in everyTextileNo)
{
ExcelHelper.CreateCell(row, textileNameNumber, originalSources.Where(w => w.TextileNo == item).First().TextileColorName, null);
priviousWeightList.Add(item, 0);
textileNameNumber++;
}
var rowNumber = 1;
var priviousYear = textileDatas.First().DateTime.ToString("yyyy");
foreach (var textiles in textileDatas)
{
var columnNumber = 1;
XSSFRow rowTextile = (XSSFRow)ws.CreateRow(rowNumber);
ExcelHelper.CreateCell(rowTextile, 0, textiles.DateTime.ToString("yyyy/MM/dd"), null);
if (textiles.DateTime.ToString("yyyy") != priviousYear)
{
priviousYear = textiles.DateTime.ToString("yyyy");
foreach (var item in everyTextileNo)
{
priviousWeightList[item] = 0;
}
}
foreach (var textileNo in everyTextileNo)
{
var textileData = textiles.TextileColors.Where(w => w.TextileNo == textileNo);
var weight = textileData.Count() == 0 ? priviousWeightList[textileNo] : textileData.First().Weight + priviousWeightList[textileNo];
ExcelHelper.CreateCell(rowTextile, columnNumber, weight.ToString(), null);
priviousWeightList[textileNo] = weight;
columnNumber++;
}
rowNumber++;
}
FileStream file = new FileStream(string.Concat(AppSettingConfig.ReportsFilePath(), @"\", filterText, "報表", DatePickerBegin.ToString("yyyyMMdd"), "-", DatePickerEnd.ToString("yyyyMMdd"), ".xlsx"), FileMode.Create);//產生檔案
wb.Write(file);
file.Close();
}