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(); } }