public void UpdateTable(string tablename, PrioritaDS ds)
{
string query = string.Format(CultureInfo.InvariantCulture, "SELECT * FROM {0}", tablename);
using (DbDataAdapter a = BuildDataAdapter(query))
{
try
{
a.ContinueUpdateOnError = false;
DataTable dt = ds.Tables[tablename];
DbCommandBuilder cmd = BuildCommandBuilder(a);
a.UpdateCommand = cmd.GetUpdateCommand();
a.DeleteCommand = cmd.GetDeleteCommand();
a.InsertCommand = cmd.GetInsertCommand();
a.Update(dt);
}
catch (DBConcurrencyException ex)
{
}
catch
{
throw;
}
}
}
private void VerificaRispettoScadenza()
{
PrioritaDS ds = new PrioritaDS();
using (PrioritaBusiness bPriorita = new PrioritaBusiness())
{
bPriorita.FillRW_SCADENZE(ds, DateTime.Today.AddDays(-8), DateTime.Today);
bPriorita.FillUSR_PRD_FLUSSO_MOVFASI_By_RW_SCADENZE(ds, DateTime.Today.AddDays(-8), DateTime.Today);
List <string> IDPRDMOVFASI = ds.RW_SCADENZE.Select(x => x.IDPRDMOVFASE).Distinct().ToList();
foreach (string IDPRDMOVFASE in IDPRDMOVFASI)
{
List <PrioritaDS.RW_SCADENZERow> scadenze = ds.RW_SCADENZE.Where(x => x.IDPRDMOVFASE == IDPRDMOVFASE && x.DATA <= DateTime.Today).OrderBy(x => x.DATA).ToList();
List <PrioritaDS.USR_PRD_FLUSSO_MOVFASIRow> termini = ds.USR_PRD_FLUSSO_MOVFASI.Where(x => x.IDPRDMOVFASE == IDPRDMOVFASE && x.IDPRDCAUFASE == "0000000008").OrderBy(x => x.DATAFLUSSOMOVFASE).ToList();
foreach (PrioritaDS.RW_SCADENZERow scadenza in scadenze)
{
decimal quantitainScadenza = scadenze.Where(x => x.DATA <= scadenza.DATA).Sum(x => x.QTA);
decimal lavoroEseguito = termini.Where(x => x.DATAFLUSSOMOVFASE <= scadenza.DATA).Sum(x => x.QTAFLUSSO);
if (lavoroEseguito < quantitainScadenza)
{
scadenza.SCADUTO = 1;
}
else
{
scadenza.SCADUTO = 0;
}
}
}
}
}
public void FillTABFAS(PrioritaDS ds)
{
string select = @"select distinct *
from gruppo.TABFAS order by CODICEFASE ";
using (DbDataAdapter da = BuildDataAdapter(select))
{
da.Fill(ds.TABFAS);
}
}
public void FillCLIFO(PrioritaDS ds)
{
string select = @"select distinct *
from gruppo.clifo order by RAGIONESOC ";
using (DbDataAdapter da = BuildDataAdapter(select))
{
da.Fill(ds.CLIFO);
}
}
public void FillREPARTI(PrioritaDS ds)
{
string select = @"select distinct *
from gruppo.clifo
where substr(codice,1,1)<>'0'
order by RAGIONESOC";
using (DbDataAdapter da = BuildDataAdapter(select))
{
da.Fill(ds.REPARTI);
}
}
public void FillMAGAZZ(PrioritaDS ds, List <string> IDMAGAZZ)
{
string inCOndition = ConvertToStringForInCondition(IDMAGAZZ);
string select = @"SELECT DISTINCT * FROM GRUPPO.MAGAZZ WHERE IDMAGAZZ in ( {0} )";
select = string.Format(select, inCOndition);
using (DbDataAdapter da = BuildDataAdapter(select))
{
da.Fill(ds.MAGAZZ);
}
}
public void FillUSR_PRD_LANCIOD(PrioritaDS ds, List <string> IDLANCIOD)
{
string inCOndition = ConvertToStringForInCondition(IDLANCIOD);
string select = @"SELECT DISTINCT * FROM USR_PRD_LANCIOD WHERE IDLANCIOD in ( {0} )";
select = string.Format(select, inCOndition);
using (DbDataAdapter da = BuildDataAdapter(select))
{
da.Fill(ds.USR_PRD_LANCIOD);
}
}
public void FillRW_SCADENZE(PrioritaDS ds, List <string> IDPRDMOVFASE)
{
string inCOndition = ConvertToStringForInCondition(IDPRDMOVFASE);
string select = @"SELECT DISTINCT * FROM RW_SCADENZE WHERE IDPRDMOVFASE in ( {0} )";
select = string.Format(select, inCOndition);
using (DbDataAdapter da = BuildDataAdapter(select))
{
da.Fill(ds.RW_SCADENZE);
}
}
public void FillSEGNALATORI(PrioritaDS ds)
{
string select = @"select distinct cli.*
from usr_prd_lanciod ld
inner join gruppo.clifo cli on cli.codice = ld.segnalatore
where substr(segnalatore,1,1)='0' and segnalatore !=00001
AND CLI.TIPO = 'C'
order by cli.RAGIONESOC";
using (DbDataAdapter da = BuildDataAdapter(select))
{
da.Fill(ds.SEGNALATORI);
}
}
public void FillRW_SCADENZE(PrioritaDS ds, DateTime dtInizio, DateTime dtFine)
{
string select = @"select * from rw_scadenze WHERE DATA BETWEEN $P{DATA1} and $P{DATA2}";
ParamSet ps = new ParamSet();
ps.AddParam("DATA1", DbType.DateTime, dtInizio);
ps.AddParam("DATA2", DbType.DateTime, dtFine);
using (DbDataAdapter da = BuildDataAdapter(select, ps))
{
da.Fill(ds.RW_SCADENZE);
}
}
public void FillUSR_PRD_FLUSSO_MOVFASI(PrioritaDS ds, string IDPRDMOVFASE)
{
string select = @"select mv.*,cau.desprdcaufase as causale from usr_prd_flusso_movfasi mv
inner join gruppo.usr_prd_caufasi cau on cau.idprdcaufase = mv.idprdcaufase
where idprdmovfase = $P<IDPRDMOVFASE>
order by idflussomovfase";
ParamSet ps = new ParamSet();
ps.AddParam("IDPRDMOVFASE", DbType.String, IDPRDMOVFASE);
using (DbDataAdapter da = BuildDataAdapter(select, ps))
{
da.Fill(ds.USR_PRD_FLUSSO_MOVFASI);
}
}
public void FillUSR_PRD_FLUSSO_MOVFASI_By_RW_SCADENZE(PrioritaDS ds, DateTime dtInizio, DateTime dtFine)
{
string select = @"select fmf.* from USR_PRD_FLUSSO_MOVFASI fmf
inner join rw_scadenze sc on sc.idprdmovfase = fmf.idprdmovfase
WHERE sc.DATA BETWEEN $P{DATA1} and $P{DATA2}";
ParamSet ps = new ParamSet();
ps.AddParam("DATA1", DbType.DateTime, dtInizio);
ps.AddParam("DATA2", DbType.DateTime, dtFine);
using (DbDataAdapter da = BuildDataAdapter(select, ps))
{
da.Fill(ds.USR_PRD_FLUSSO_MOVFASI);
}
}
public void FillUSR_VENDITET(PrioritaDS ds, List <string> IDPRDMOVFASE)
{
string inCOndition = ConvertToStringForInCondition(IDPRDMOVFASE);
string select = @" select distinct mm.idprdmovfase,vt.*
from usr_prd_movmate mm
inner join USR_PRD_FLUSSO_MOVMATE fmm on fmm.idprdmovmate = mm.idprdmovmate
inner join usr_venditet vt on vt.idvenditet = fmm.idvenditet
WHERE mm.IDPRDMOVFASE in ( {0} )";
select = string.Format(select, inCOndition);
using (DbDataAdapter da = BuildDataAdapter(select))
{
da.Fill(ds.USR_VENDITET);
}
}
public void FillUSR_PRD_LANCIOD(PrioritaDS ds, List <string> idIDLANCIOD)
{
PrioritaAdapter a = new PrioritaAdapter(DbConnection, DbTransaction);
while (idIDLANCIOD.Count > 0)
{
List <string> articoliDaCaricare;
if (idIDLANCIOD.Count > 999)
{
articoliDaCaricare = idIDLANCIOD.GetRange(0, 999);
idIDLANCIOD.RemoveRange(0, 999);
}
else
{
articoliDaCaricare = idIDLANCIOD.GetRange(0, idIDLANCIOD.Count);
idIDLANCIOD.RemoveRange(0, idIDLANCIOD.Count);
}
a.FillUSR_PRD_LANCIOD(ds, articoliDaCaricare);
}
}
public void FillUSR_PRD_FASI(PrioritaDS ds, List <string> IDPRDFASE)
{
PrioritaAdapter a = new PrioritaAdapter(DbConnection, DbTransaction);
while (IDPRDFASE.Count > 0)
{
List <string> articoliDaCaricare;
if (IDPRDFASE.Count > 999)
{
articoliDaCaricare = IDPRDFASE.GetRange(0, 999);
IDPRDFASE.RemoveRange(0, 999);
}
else
{
articoliDaCaricare = IDPRDFASE.GetRange(0, IDPRDFASE.Count);
IDPRDFASE.RemoveRange(0, IDPRDFASE.Count);
}
a.FillUSR_PRD_FASI(ds, articoliDaCaricare);
}
}
public void FillUSR_PRD_MOVFASI_Chiusi(PrioritaDS ds, string codiceSegnalatore, string codiceReparto, string idtabfas, string Articolo, int giorniIndietro)
{
string select = @"select mf.*
from usr_prd_movfasi mf
inner join usr_prd_fasi fa on mf.idprdfase = fa.idprdfase
inner join usr_prd_lanciod ld on ld.idlanciod = fa.idlanciod
where
mf.qtadater = 0
and datamovfase >= sysdate-" + giorniIndietro.ToString();
if (!String.IsNullOrEmpty(Articolo))
{
select = @"select mf.*
from usr_prd_movfasi mf
inner join usr_prd_fasi fa on mf.idprdfase = fa.idprdfase
inner join usr_prd_lanciod ld on ld.idlanciod = fa.idlanciod
inner join gruppo.magazz ma on ma.idmagazz = mf.idmagazz
where ma.modello like '%" + Articolo.ToUpper() + @"%'
and mf.qtadater = 0
and datamovfase >= sysdate-" + giorniIndietro.ToString();
}
if (!string.IsNullOrEmpty(codiceSegnalatore))
{
select = select + " and ld.segnalatore = '" + codiceSegnalatore + "'";
}
if (!string.IsNullOrEmpty(codiceReparto))
{
select = select + " and mf.codiceclifo = '" + codiceReparto + "'";
}
if (!string.IsNullOrEmpty(idtabfas))
{
select = select + " and mf.idtabfas = '" + idtabfas + "'";
}
using (DbDataAdapter da = BuildDataAdapter(select))
{
da.Fill(ds.USR_PRD_MOVFASI);
}
}
public void FillMAGAZZ(PrioritaDS ds, List <string> IDMAGAZZ)
{
List <string> articoliPresenti = ds.MAGAZZ.Select(x => x.IDMAGAZZ).Distinct().ToList();
List <string> articoliMancanti = IDMAGAZZ.Except(articoliPresenti).ToList();
PrioritaAdapter a = new PrioritaAdapter(DbConnection, DbTransaction);
while (articoliMancanti.Count > 0)
{
List <string> articoliDaCaricare;
if (articoliMancanti.Count > 999)
{
articoliDaCaricare = articoliMancanti.GetRange(0, 999);
articoliMancanti.RemoveRange(0, 999);
}
else
{
articoliDaCaricare = articoliMancanti.GetRange(0, articoliMancanti.Count);
articoliMancanti.RemoveRange(0, articoliMancanti.Count);
}
a.FillMAGAZZ(ds, articoliDaCaricare);
}
}
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);
}
public void FillTABFAS(PrioritaDS ds)
{
PrioritaAdapter a = new PrioritaAdapter(DbConnection, DbTransaction);
a.FillTABFAS(ds);
}
public void FillUSR_PRD_MOVFASI_Chiusi(PrioritaDS ds, string codiceSegnalatore, string codiceReparto, string idtabfas, string Articolo, int giorniIndietro)
{
PrioritaAdapter a = new PrioritaAdapter(DbConnection, DbTransaction);
a.FillUSR_PRD_MOVFASI_Chiusi(ds, codiceSegnalatore, codiceReparto, idtabfas, Articolo, giorniIndietro);
}
public void UpdateRW_SCADENZE(PrioritaDS ds)
{
PrioritaAdapter a = new PrioritaAdapter(DbConnection, DbTransaction);
a.UpdateTable("RW_SCADENZE", ds);
}
public void FillREPARTI(PrioritaDS ds)
{
PrioritaAdapter a = new PrioritaAdapter(DbConnection, DbTransaction);
a.FillREPARTI(ds);
}
public void FillUSR_PRD_FLUSSO_MOVFASI(PrioritaDS ds, string IDPRMOVFASE)
{
PrioritaAdapter a = new PrioritaAdapter(DbConnection, DbTransaction);
a.FillUSR_PRD_FLUSSO_MOVFASI(ds, IDPRMOVFASE);
}
private void btnTrova_Click(object sender, EventArgs e)
{
if (bgwBIL.IsBusy)
{
MessageBox.Show("Attività di recupero BIL in corso, impossibile interrompere adesso. Aspetta che l'attività sia stata annullata e riprova", "ATTENZIONE", MessageBoxButtons.OK, MessageBoxIcon.Warning);
CancellaBackgroundWorker();
return;
}
Cursor.Current = Cursors.WaitCursor;
statusBarLabel.Text = "Ricerca in corso...";
try
{
_dsGriglieODL.Tables[_nomeTabellaGrigliaODL].Clear();
string codiceSegnalatore = string.Empty;
string idTabFas = string.Empty;
string codiceReparto = GetCodiceReparto();
if (ddlSegnalatore.SelectedIndex != -1)
{
codiceSegnalatore = ddlSegnalatore.SelectedItem as string;
if (_dsAnagrafica.SEGNALATORI.Any(x => x.RAGIONESOC == codiceSegnalatore))
{
codiceSegnalatore = _dsAnagrafica.SEGNALATORI.Where(x => x.RAGIONESOC == codiceSegnalatore).Select(x => x.CODICE).FirstOrDefault();
}
}
if (ddlFase.SelectedIndex != -1)
{
idTabFas = ddlFase.SelectedItem as string;
if (_dsAnagrafica.TABFAS.Any(x => x.DESTABFAS == idTabFas))
{
idTabFas = _dsAnagrafica.TABFAS.Where(x => x.DESTABFAS == idTabFas).Select(x => x.IDTABFAS).FirstOrDefault();
}
}
_dsPriorita = new PrioritaDS();
using (PrioritaBusiness bPriorita = new PrioritaBusiness())
{
bPriorita.FillUSR_PRD_MOVFASI_Aperti(_dsPriorita, codiceSegnalatore, codiceReparto, idTabFas, txtArticolo.Text.ToUpper());
if (!chkSoloAperti.Checked)
{
bPriorita.FillUSR_PRD_MOVFASI_Chiusi(_dsPriorita, codiceSegnalatore, codiceReparto, idTabFas, txtArticolo.Text.ToUpper(), 7);
}
List <string> articoli = _dsPriorita.USR_PRD_MOVFASI.Select(x => x.IDMAGAZZ).Distinct().ToList();
bPriorita.FillMAGAZZ(_dsAnagrafica, articoli);
List <string> IDPRDFASE = _dsPriorita.USR_PRD_MOVFASI.Select(x => x.IDPRDFASE).Distinct().ToList();
bPriorita.FillUSR_PRD_FASI(_dsPriorita, IDPRDFASE);
List <string> IDLANCIOD = _dsPriorita.USR_PRD_FASI.Select(x => x.IDLANCIOD).Distinct().ToList();
bPriorita.FillUSR_PRD_LANCIOD(_dsPriorita, IDLANCIOD);
List <string> IDPRDMOVFASE = _dsPriorita.USR_PRD_MOVFASI.Select(x => x.IDPRDMOVFASE).Distinct().ToList();
bPriorita.FillRW_SCADENZE(_dsPriorita, IDPRDMOVFASE);
//IDPRDMOVFASE = _dsPriorita.USR_PRD_MOVFASI.Select(x => x.IDPRDMOVFASE).Distinct().ToList();
//bPriorita.FillUSR_VENDITET(_dsPriorita, IDPRDMOVFASE);
PopolaDSGrigliaODL();
bgwBIL.RunWorkerAsync();
statusBarLabel.Text = "Recupero BIL in corso...";
}
}
catch (Exception ex)
{
ExceptionFrm form = new ExceptionFrm(ex);
form.ShowDialog();
}
finally
{
Cursor.Current = Cursors.Default;
}
}
public void FillSEGNALATORI(PrioritaDS ds)
{
PrioritaAdapter a = new PrioritaAdapter(DbConnection, DbTransaction);
a.FillSEGNALATORI(ds);
}
public void FillUSR_PRD_FLUSSO_MOVFASI_By_RW_SCADENZE(PrioritaDS ds, DateTime dtInizio, DateTime dtFine)
{
PrioritaAdapter a = new PrioritaAdapter(DbConnection, DbTransaction);
a.FillUSR_PRD_FLUSSO_MOVFASI_By_RW_SCADENZE(ds, dtInizio, dtFine);
}
public void FillRW_SCADENZE(PrioritaDS ds, DateTime dtInizio, DateTime dtFine)
{
PrioritaAdapter a = new PrioritaAdapter(DbConnection, DbTransaction);
a.FillRW_SCADENZE(ds, dtInizio, dtFine);
}
