public string PaletizaVolume(AuditoriaPaletiza dados)
{
OracleConnection connection = DataBase.novaConexao();
OracleCommand exec = connection.CreateCommand();
string resposta = "Nenhum registro encontrado para a O.S.";
string pertencecarga;
int osaberta;
int palete;
string paletizado;
string atribuidoFuncionario;
DataTable osCabecalho = new DataTable();
StringBuilder query = new StringBuilder();
StringBuilder volume = new StringBuilder();
StringBuilder registraConf = new StringBuilder();
try
{
query.Append("select os.numos, os.numpalete, os.numped, os.numcar, os.numbox, os.numvol, os.tipoos, os.pendencia, pertenceCarga, paletizado, atribuidoFunc, ");
query.Append(" (SELECT COUNT(*) AS os FROM pcmovendpend");
query.Append($" WHERE numos = {dados.NumOs} AND dtfimconferencia IS NULL) AS osaberta,");
query.Append(" (select count(os.numvol) as divergencia");
query.Append(" from (select distinct numos from pcmovendpend where numcar = os.numcar and dtestorno is null) mov inner join pcvolumeos os on (mov.numos = os.numos)");
query.Append(" where os.datapalete is null) as qtospendente");
query.Append(" from (select mov.numos, nvl(mov.numpalete, 0) as numpalete, mov.numped, nvl(mov.numcar, 0) as numcar, ");
query.Append(" nvl(mov.numbox, 0) as numbox, nvl(vol.numvol, 1) as numvol, mov.tipoos, ");
query.Append($" case when mov.numcar = {dados.NumCar} then 'S' else 'N'end pertenceCarga, ");
query.Append(" case when vol.datapalete is null then 'N' else 'S' end paletizado, ");
query.Append(" case when vol.codfuncmontapalete is null then 'N' else 'S' end atribuidoFunc, ");
query.Append(" nvl(pend.pendencia, 0) as pendencia, mov.codprod as prod_os, vol.codprod as prod_vol");
query.Append(" from pcmovendpend mov left outer join (select vos.numos, vos.numvol, vos.datapalete, vos.codfuncmontapalete, vosi.codprod");
query.Append(" from pcvolumeos vos, pcvolumeosi vosi");
query.Append(" where vos.numos = vosi.numos(+)");
query.Append(" and vos.numvol = vosi.numvol(+)");
query.Append($" and vos.numos = {dados.NumOs}");
query.Append($" and vos.numvol = {dados.NumVol}");
query.Append($" and (vos.codfuncmontapalete is null or vos.codfuncmontapalete = {dados.CodFunc})) vol on (mov.numos = vol.numos)");
query.Append(" left outer join (select count(distinct numvol) pendencia, numos");
query.Append(" from pcvolumeos");
query.Append($" where numos = {dados.NumOs}");
query.Append(" and datapalete is null");
query.Append(" group by numos");
query.Append(" ) pend on (mov.numos = pend.numos)");
query.Append($" where mov.numos = {dados.NumOs}");
query.Append(" and mov.dtestorno is null");
query.Append(" order by case when mov.codprod = vol.codprod then 0 else 1 end");
query.Append(" ) os");
query.Append(" where os.prod_os = nvl(os.prod_vol, os.prod_os)");
query.Append($" and (case when os.tipoos <> 17 then os.numvol else 1 end = case when os.tipoos <> 17 then {dados.NumVol} else 1 end)");
exec.CommandText = query.ToString();
OracleDataAdapter oda = new OracleDataAdapter(exec);
oda.SelectCommand = exec;
oda.Fill(osCabecalho);
if (osCabecalho.Rows.Count > 0)
{
pertencecarga = osCabecalho.Rows[0]["pertenceCarga"].ToString();
osaberta = Convert.ToInt32(osCabecalho.Rows[0]["osaberta"]);
paletizado = osCabecalho.Rows[0]["paletizado"].ToString();
atribuidoFuncionario = osCabecalho.Rows[0]["atribuidoFunc"].ToString();
palete = Convert.ToInt32(osCabecalho.Rows[0]["numpalete"]);
if (pertencecarga == "S")
{
if (osaberta == 0)
{
if (dados.TipoConferencia == "P" && paletizado == "N")
{
if (palete == dados.NumPalete)
{
volume.Append($"update pcvolumeos set codfuncmontapalete = {dados.CodFunc}, datapalete = sysdate, numpalete = {dados.NumPalete} where numos = {dados.NumOs} and numvol = {dados.NumVol}");
exec.CommandText = volume.ToString();
OracleDataReader finalizaOs = exec.ExecuteReader();
if (atribuidoFuncionario == "N")
{
registraConf.Append($"update pcvolumeos set codfuncmontapalete = {dados.CodFunc} ");
registraConf.Append($" where exists (select 1 from pcmovendpend where numcar = {osCabecalho.Rows[0]["numcar"]} and numpalete = {osCabecalho.Rows[0]["numpalete"]}");
registraConf.Append(" and numos = pcvolumeos.numos and dtestorno is null)");
exec.CommandText = registraConf.ToString();
OracleDataReader reader = exec.ExecuteReader();
}
resposta = "S";
return(resposta);
}
else
{
resposta = "O.S. não pertence ao palete.";
return(resposta);
}
}
else
{
resposta = "O.S. já foi paletizada.";
return(resposta);
}
}
else
{
resposta = "É necessário realizar a conferência para auditar/paletizar.";
return(resposta);
}
}
else
{
resposta = "O.S. não pertence ao carregamento.";
return(resposta);
}
}
else
{
Boolean cortes = ConsultaCorte(dados.NumOs);
if (cortes)
{
resposta = "O.S: " + dados.NumOs + " possui corte. Favor verificar.";
return(resposta);
}
return(resposta);
}
}
catch (Exception ex)
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
throw new Exception(ex.Message);
}
exec.Dispose();
connection.Dispose();
throw new Exception(ex.Message);
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
exec.Dispose();
connection.Dispose();
}
}
public DataTable CabecalhoOs(AuditoriaPaletiza dados)
{
OracleConnection connection = DataBase.novaConexao();
OracleCommand exec = connection.CreateCommand();
DataTable osCabecalho = new DataTable();
StringBuilder query = new StringBuilder();
try
{
if (dados != null)
{
query.Append("select os.numos, prod.codauxiliar2 as dun, prod.qtunitcx, qtconferida, os.numpalete, os.numped, os.numcar, os.numbox, os.numvol, os.tipoos, os.dtconf, os.codfuncconf, os.pendencia as divergencia, pertenceCarga, reconferido, ");
query.Append(" (SELECT COUNT(*) AS os FROM pcmovendpend");
query.Append($" WHERE numos = {dados.NumOs} AND (posicao = 'P' AND dtfimconferencia IS NULL)) AS osaberta,");
query.Append(" (select count(os.numvol) as divergencia");
query.Append(" from (select distinct numos from pcmovendpend where numcar = os.numcar and dtestorno is null) mov inner join pcvolumeos os on (mov.numos = os.numos)");
query.Append(" where os.dtconf2 is null) as qtospendente");
query.Append(" from (select mov.numos, mov.codprod, mov.qtconferida, nvl(mov.numpalete, 0) as numpalete, mov.numped, nvl(mov.numcar, 0) as numcar, ");
query.Append(" nvl(mov.numbox, 0) as numbox, nvl(vol.numvol, 1) as numvol, mov.tipoos, ");
query.Append($" case when mov.numcar = {dados.NumCar} then 'S' else 'N'end as pertenceCarga, ");
query.Append(" case when vol.dtconf2 is not null then 'S' else 'N' end as reconferido, ");
query.Append(" vol.dtconf2 as dtconf, ");
query.Append(" vol.codfuncconf2 as codfuncconf, ");
query.Append(" nvl(pend.pendencia, 0) as pendencia, mov.codprod as prod_os, vol.codprod as prod_vol");
query.Append(" from pcmovendpend mov left outer join (select vos.numos, vos.numvol, vos.dtconf2, vos.codfuncconf2, vosi.codprod");
query.Append(" from pcvolumeos vos, pcvolumeosi vosi");
query.Append(" where vos.numos = vosi.numos(+)");
query.Append(" and vos.numvol = vosi.numvol(+)");
query.Append($" and vos.numos = {dados.NumOs}");
query.Append($" and vos.numvol = {dados.NumVol}) vol on (mov.numos = vol.numos)");
query.Append(" left outer join (select count(distinct numvol) pendencia, numos");
query.Append(" from pcvolumeos");
query.Append($" where numos = {dados.NumOs}");
query.Append(" and dtconf2 is null");
query.Append(" group by numos");
query.Append(" ) pend on (mov.numos = pend.numos)");
query.Append($" where mov.numos = {dados.NumOs}");
query.Append(" and mov.dtestorno is null");
query.Append(" order by case when mov.codprod = vol.codprod then 0 else 1 end");
query.Append(" ) os inner join pcprodut prod on (os.codprod = prod.codprod)");
query.Append(" where os.prod_os = nvl(os.prod_vol, os.prod_os)");
query.Append($" and (case when os.tipoos <> 17 then os.numvol else 1 end = case when os.tipoos <> 17 then {dados.NumVol} else 1 end)");
exec.CommandText = query.ToString();
OracleDataAdapter oda = new OracleDataAdapter(exec);
oda.SelectCommand = exec;
oda.Fill(osCabecalho);
return(osCabecalho);
}
return(osCabecalho);
}
catch (Exception ex)
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
throw ex;
}
exec.Dispose();
connection.Dispose();
throw ex;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
exec.Dispose();
connection.Dispose();
}
}
