Exemplo n.º 1
0
        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();
            }
        }
Exemplo n.º 2
0
        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();
            }
        }