private void PopolaDSGrigliaODL()
{
foreach (PrioritaDS.USR_PRD_MOVFASIRow odl in _dsPriorita.USR_PRD_MOVFASI)
{
DataTable dtGriglia = _dsGriglieODL.Tables[_nomeTabellaGrigliaODL];
DataRow riga = dtGriglia.NewRow();
PrioritaDS.USR_PRD_FASIRow fase = _dsPriorita.USR_PRD_FASI.Where(x => x.IDPRDFASE == odl.IDPRDFASE).FirstOrDefault();
if (fase == null)
{
new Exception(String.Format("ODL senza fase. IDPRDMOVFASE: {0} IDPRDFASE: {1}", odl.IDPRDMOVFASE, odl.IDPRDFASE));
}
PrioritaDS.USR_PRD_LANCIODRow lanciod = _dsPriorita.USR_PRD_LANCIOD.Where(x => x.IDLANCIOD == fase.IDLANCIOD).FirstOrDefault();
if (lanciod == null)
{
new Exception(String.Format("ODL senza lancio. IDPRDMOVFASE: {0} IDPRDFASE: {1} IDLANCIOD: {2}", odl.IDPRDMOVFASE, odl.IDPRDFASE, fase.IDLANCIOD));
}
PrioritaDS.MAGAZZRow articolo = _dsAnagrafica.MAGAZZ.Where(x => x.IDMAGAZZ == odl.IDMAGAZZ).FirstOrDefault();
riga[(int)Colonne.IDLANCIOD] = lanciod.IsIDLANCIODNull() ? string.Empty : lanciod.IDLANCIOD;
riga[(int)Colonne.IDPRDFASE] = fase.IsIDPRDFASENull() ? string.Empty : fase.IDPRDFASE;
riga[(int)Colonne.IDPRDMOVFASE] = odl.IsIDPRDMOVFASENull() ? string.Empty : odl.IDPRDMOVFASE;
riga[(int)Colonne.IDMAGAZZ] = odl.IsIDMAGAZZNull() ? string.Empty : odl.IDMAGAZZ;
riga[(int)Colonne.AZIENDA] = odl.AZIENDA;
if (!lanciod.IsSEGNALATORENull())
{
PrioritaDS.SEGNALATORIRow segnalatore = _dsAnagrafica.SEGNALATORI.Where(x => x.CODICE == lanciod.SEGNALATORE).FirstOrDefault();
if (segnalatore != null)
{
riga[(int)Colonne.SEGNALATORE] = segnalatore.IsRAGIONESOCNull() ? string.Empty : segnalatore.RAGIONESOC;
}
}
riga[(int)Colonne.COMMESSA] = lanciod.IsNOMECOMMESSANull() ? string.Empty : lanciod.NOMECOMMESSA;
riga[(int)Colonne.DATACOMMESSA] = lanciod.IsDATACOMMESSANull() ? string.Empty : lanciod.DATACOMMESSA.ToShortDateString();
riga[(int)Colonne.ODL] = odl.IsNUMMOVFASENull() ? string.Empty : odl.NUMMOVFASE;
riga[(int)Colonne.DATAODL] = odl.IsDATAMOVFASENull() ? string.Empty : odl.DATAMOVFASE.ToShortDateString();
riga[(int)Colonne.BARCODE] = odl.IsBARCODENull() ? string.Empty : odl.BARCODE;
if (!odl.IsCODICECLIFONull())
{
PrioritaDS.CLIFORow clifo = _dsAnagrafica.CLIFO.Where(x => x.CODICE == odl.CODICECLIFO).FirstOrDefault();
if (clifo != null)
{
riga[(int)Colonne.REPARTO] = clifo.IsRAGIONESOCNull() ? string.Empty : clifo.RAGIONESOC;
}
}
if (!odl.IsCODICECLIFONull())
{
PrioritaDS.TABFASRow tabfas = _dsAnagrafica.TABFAS.Where(x => x.IDTABFAS == odl.IDTABFAS).FirstOrDefault();
if (tabfas != null)
{
riga[(int)Colonne.FASE] = tabfas.IsDESTABFASPPRENull() ? string.Empty : tabfas.DESTABFAS;
}
}
if (articolo != null)
{
riga[(int)Colonne.ARTICOLO] = articolo.MODELLO;
}
//StringBuilder BIL = new StringBuilder();
//List<PrioritaDS.USR_VENDITETRow> vendite = _dsPriorita.USR_VENDITET.Where(x => x.IDPRDMOVFASE == odl.IDPRDMOVFASE).ToList();
//if (vendite.Count > 0)
//{
// foreach (PrioritaDS.USR_VENDITETRow vendita in vendite)
// {
// string str = string.Format("{0} del {1}, ", vendita.NUMDOC, vendita.DATDOC.ToShortDateString());
// BIL.Append(str);
// }
//}
//riga[(int)Colonne.BIL] = BIL.Length == 0 ? string.Empty : BIL.ToString().Substring(0, BIL.Length - 2);
riga[(int)Colonne.QUANTITA] = odl.QTA;
riga[(int)Colonne.QUANTITADATERMINARE] = odl.IsQTADATERNull() ? 0 : odl.QTADATER;
dtGriglia.Rows.Add(riga);
}
}
public byte[] CreaExcelScadenze(PrioritaDS ds, PrioritaDS dsAnagrafica)
{
byte[] content;
MemoryStream ms = new MemoryStream();
//string filename = @"c:\temp\mancanti.xlsx";
using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
// Adding style
WorkbookStylesPart stylePart = workbookPart.AddNewPart <WorkbookStylesPart>();
stylePart.Stylesheet = GenerateStylesheet();
stylePart.Stylesheet.Save();
int numeroColonne = 6;
Columns columns = new Columns();
for (int i = 0; i < numeroColonne; i++)
{
Column c = new Column();
UInt32Value u = new UInt32Value((uint)(i + 1));
c.Min = u;
c.Max = u;
c.Width = 20;
c.CustomWidth = true;
columns.Append(c);
}
worksheetPart.Worksheet.AppendChild(columns);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Scadenze"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
row.Append(ConstructCell("Barcode", CellValues.String, 2));
row.Append(ConstructCell("ODL", CellValues.String, 2));
row.Append(ConstructCell("Reparto", CellValues.String, 2));
row.Append(ConstructCell("Articolo", CellValues.String, 2));
row.Append(ConstructCell("Quantità", CellValues.String, 2));
row.Append(ConstructCell("Scadenza", CellValues.String, 2));
// Insert the header row to the Sheet Data
sheetData.AppendChild(row);
List <string> IDPRDMOVFASE = ds.RW_SCADENZE.Select(x => x.IDPRDMOVFASE).Distinct().ToList();
foreach (string idMovFase in IDPRDMOVFASE)
{
PrioritaDS.USR_PRD_MOVFASIRow odl = ds.USR_PRD_MOVFASI.Where(x => x.IDPRDMOVFASE == idMovFase).FirstOrDefault();
PrioritaDS.MAGAZZRow articolo = dsAnagrafica.MAGAZZ.Where(x => x.IDMAGAZZ == odl.IDMAGAZZ).FirstOrDefault();
foreach (PrioritaDS.RW_SCADENZERow scadenza in ds.RW_SCADENZE.Where(x => x.IDPRDMOVFASE == idMovFase).OrderBy(x => x.DATA))
{
row = new Row();
row.Append(ConstructCell(odl.BARCODE, CellValues.String, 1));
row.Append(ConstructCell(odl.NUMMOVFASE, CellValues.String, 1));
row.Append(ConstructCell(odl.CODICECLIFO, CellValues.String, 1));
row.Append(ConstructCell(articolo.MODELLO, CellValues.String, 1));
row.Append(ConstructCell(scadenza.QTA.ToString(), CellValues.String, 1));
row.Append(ConstructCell(scadenza.DATA.ToShortDateString(), CellValues.String, 1));
sheetData.AppendChild(row);
}
}
workbookPart.Workbook.Save();
document.Save();
document.Close();
ms.Seek(0, SeekOrigin.Begin);
content = ms.ToArray();
}
return(content);
}
