public static string GuardarHistoricoOrdenes(SqlConnection serConn, Ordenes orden, SqlTransaction aTrans) { string retorno = string.Empty; LogWriter log = new LogWriter(); //////serConn.Open(); //orden.Historicos = orden.Historicos.OrderByDescending(o => o.idEstatusOrden).ToList(); int h = orden.Historicos.Count - 1; while (h >= 0) { HistoricoOrdenes item = orden.Historicos[h]; Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US"); DateTime fechaFinal = h == orden.Historicos.Count - 1 ? DateTime.MinValue : orden.Historicos[h + 1].fechaInicial; if (fechaFinal != DateTime.MinValue) { orden.Historicos[h].fechaFinal = fechaFinal; } h--; } //orden.Historicos = orden.Historicos.OrderBy(o => o.idEstatusOrden).ToList(); for (int i = 0; i < orden.Historicos.Count; i++) { HistoricoOrdenes item = orden.Historicos[i]; //Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US"); //DateTime fechaFinal = i == orden.Historicos.Count - 1 ? item.fechaInicial : (i == 0 ? DateTime.MinValue : orden.Historicos[i - 1].fechaInicial) ; //if (fechaFinal != DateTime.MinValue) // orden.Historicos[i].fechaFinal = fechaFinal; DateTime fechaFinal = item.fechaFinal ?? DateTime.MinValue; SqlCommand cmdH = new SqlCommand(); if (fechaFinal == DateTime.MinValue) { cmdH = new SqlCommand("insert into HistorialEstatusOrden (idOrden,idEstatusOrden,fechaInicial,idUsuario) values(" + item.idOrden + "," + item.idEstatusOrden + ",CAST('" + item.fechaInicial.ToString("yyyy-MM-ddThh:mm:ss") + "' AS DATETIME)," + item.idUsuario + ")", serConn, aTrans); } else { cmdH = new SqlCommand("insert into HistorialEstatusOrden (idOrden,idEstatusOrden,fechaInicial,fechaFinal,idUsuario) values(" + item.idOrden + "," + item.idEstatusOrden + ",CAST('" + item.fechaInicial.ToString("yyyy-MM-ddThh:mm:ss") + "' AS DATETIME),CAST('" + fechaFinal.ToString("yyyy-MM-ddThh:mm:ss") + "' AS DATETIME)," + item.idUsuario + ")", serConn, aTrans); } int res = cmdH.ExecuteNonQuery(); if (res > 0) { retorno += "Historico de estatus '" + item.idEstatusOrden + "' generado.\r\n"; } else { throw new Exception("Ocurrio un error al generar el historio de estatus " + item.idEstatusOrden); } } //////serConn.Close(); log.WriteInLog(retorno); return(retorno); }
public static void MigracionGeneral() { SqlConnection serConn = new SqlConnection(Constants.ASEPROTDesarrolloStringConn); serConn.Open(); SqlTransaction trans = serConn.BeginTransaction("General"); try { SqlCommand ordCMD = new SqlCommand("select top 200 * from talleres.dbo.Cita order by idCita", serConn, trans); DataTable dt = new DataTable(); dt.Load(ordCMD.ExecuteReader()); //serConn.Close(); List <Ordenes> ordenesXCitas = new List <Ordenes>(); int contador = dt.Rows.Count; foreach (DataRow dr in dt.Rows) { int idCita = int.Parse(dr["idCita"].ToString()); Ordenes orden = OrdenesProcessor.CrearOrdenXCita(serConn, idCita, trans); #region insert de la orden int idOrden = Ordenes.InsertData(serConn, orden, trans); #endregion #region insert de historicos orden.Historicos = OrdenesProcessor.GetHistoricosOrden(serConn, idCita, idOrden, orden.idEstatusOrden, trans); Console.WriteLine(HistoricoOrdenes.GuardarHistoricoOrdenes(serConn, orden, trans)); #endregion #region insert de la tabla relacion Console.WriteLine(OrdenesProcessor.GuardarRelacionCitaOrdenes(serConn, idCita, idOrden, trans)); #endregion #region guardar cotizacion detalle por cita //Console.WriteLine(CotizacionDetalle.GuardarCotizacionDetallePorCita(serConn, idCita)); #endregion ordenesXCitas.Add(orden); contador--; Console.WriteLine("Ciclos pendientes : " + contador + "\r\n"); } //Console.WriteLine(CotizacionDetalle.GuardarCotizacionDetalleCompleto(serConn)); trans.Commit(); serConn.Close(); } catch (Exception aE) { LogWriter log = new LogWriter(); Console.WriteLine("Ocurrio un error : " + aE.Message + "\r\n"); log.WriteInLog("Ocurrio un error : " + aE.Message + "\r\n"); trans.Rollback(); serConn.Close(); } }
public static List <HistoricoOrdenes> GetHistoricosOrden(SqlConnection dbCnx, int aIdCita, int aIdOrden, int estatusOrden, SqlTransaction aTrans) { //////dbCnx.Open(); DataTable dt = new DataTable(); List <HistoricoOrdenes> historicos = new List <HistoricoOrdenes>(); List <int> estatus = new List <int> { 1, 2, 3, 4, 5, 6, 7, 8, 14, 13, 9, 10, 11, 12 }; List <string> comandos = new List <string> { "select top 1 fecha from (select * from talleres.dbo.HistorialProceso where idTipoProceso <> 3 and idEstatus not in (17,3,11,12,13,21) ) his where idProceso = " + aIdCita + " and idEstatus in (1) order by fecha desc", "select top 1 fecha from (select * from talleres.dbo.HistorialProceso where idTipoProceso <> 3 and idEstatus not in (17,3,11,12,13,21) ) his where idProceso = " + aIdCita + " and idEstatus in (2) order by fecha desc", "select top 1 fecha from (select * from talleres.dbo.HistorialProceso where idTipoProceso <> 3 and idEstatus not in (17,3,11,12,13,21) ) his where idProceso = " + aIdCita + " and idEstatus in (15) order by fecha desc", "select top 1 fecha from (select * from talleres.dbo.HistorialProceso where idTipoProceso <> 3 and idEstatus not in (17,3,11,12,13,21) ) his where idProceso = " + aIdCita + " and idEstatus in (4) order by fecha desc", "select top 1 fecha from (select * from talleres.dbo.HistorialProceso where idTipoProceso <> 3 and idEstatus not in (17,3,11,12,13,21) ) his where idProceso = " + aIdCita + " and idEstatus in (5,23) order by fecha desc", "select top 1 fecha from (select * from talleres.dbo.HistorialProceso where idTipoProceso <> 3 and idEstatus not in (17,3,11,12,13,21) ) his where idProceso = " + aIdCita + " and idEstatus in (7) order by fecha desc", "select top 1 fecha from (select * from talleres.dbo.HistorialProceso where idTipoProceso <> 3 and idEstatus not in (17,3,11,12,13,21) ) his where idProceso = " + aIdCita + " and idEstatus in (14,19,20) order by fecha desc", "select top 1 fecha from (select * from talleres.dbo.HistorialProceso where idTipoProceso <> 3 and idEstatus not in (17,3,11,12,13,21) ) his where idProceso = " + aIdCita + " and idEstatus in (24) order by fecha desc", "select top 1 fecha from (select * from talleres.dbo.HistorialProceso where idTipoProceso <> 3 and idEstatus not in (17,3,11,12,13,21) ) his where idProceso = " + aIdCita + " and idEstatus in (16) order by fecha desc", "select top 1 fecha from (select * from talleres.dbo.HistorialProceso where idTipoProceso <> 3 and idEstatus not in (17,3,11,12,13,21) ) his where idProceso = " + aIdCita + " and idEstatus in (6,22) order by fecha desc", "select top 1 fecha from (select * from talleres.dbo.HistorialProceso where idTipoProceso <> 3 and idEstatus not in (17,3,11,12,13,21) ) his where idProceso = " + aIdCita + " and idEstatus in (99999999) order by fecha desc", "select top 1 fecha from (select * from talleres.dbo.HistorialProceso where idTipoProceso <> 3 and idEstatus not in (17,3,11,12,13,21) ) his where idProceso = " + aIdCita + " and idEstatus in (99999999) order by fecha desc", "select top 1 fecha from (select * from talleres.dbo.HistorialProceso where idTipoProceso <> 3 and idEstatus not in (17,3,11,12,13,21) ) his where idProceso = " + aIdCita + " and idEstatus in (99999999) order by fecha desc", "select top 1 fecha from (select * from talleres.dbo.HistorialProceso where idTipoProceso <> 3 and idEstatus not in (17,3,11,12,13,21) ) his where idProceso = " + aIdCita + " and idEstatus in (99999999) order by fecha desc", }; SqlCommand cmd; for (int i = 0; i < estatus.Count; i++) { cmd = new SqlCommand(comandos[i], dbCnx, aTrans); dt.Load(cmd.ExecuteReader()); if (dt.Rows.Count > 0) { HistoricoOrdenes historico = new HistoricoOrdenes { idOrden = aIdOrden, fechaInicial = DateTime.Parse(dt.Rows[0]["fecha"].ToString()), idEstatusOrden = estatus[i], idUsuario = 514 }; if (estatusOrden >= estatus[i]) //descomentar si se requiere que los estatus de talleres no sobrepasen el estatus de la orden { historicos.Add(historico); } } else if (estatusOrden >= estatus[i]) { switch (estatusOrden) { case 1: case 2: case 3: case 4: case 5: case 6: case 7: case 8: case 13: if ((estatus[i] >= 9 && estatus[i] <= 12) || estatus[i] == 14) { break; } HistoricoOrdenes historico = new HistoricoOrdenes { idOrden = aIdOrden, fechaInicial = historicos[historicos.Count - 1].fechaFinal ?? historicos[historicos.Count - 1].fechaInicial, idEstatusOrden = estatus[i], idUsuario = 514 }; historicos.Add(historico); break; case 14: if (estatus[i] == 13) { break; } HistoricoOrdenes historico2 = new HistoricoOrdenes { idOrden = aIdOrden, fechaInicial = historicos[historicos.Count - 1].fechaFinal ?? historicos[historicos.Count - 1].fechaInicial, idEstatusOrden = estatus[i], idUsuario = 514 }; historicos.Add(historico2); break; case 9: case 10: case 11: case 12: bool existe = historicos.Where(o => o.idEstatusOrden == 14).ToList().Count > 0; if (estatus[i] == 13) { break; } if (estatusOrden < estatus[i]) { break; } HistoricoOrdenes historico3 = new HistoricoOrdenes { idOrden = aIdOrden, fechaInicial = historicos[historicos.Count - 1].fechaFinal ?? historicos[historicos.Count - 1].fechaInicial, idEstatusOrden = estatus[i], idUsuario = 514 }; historicos.Add(historico3); break; default: break; } } dt.Clear(); } //////dbCnx.Open(); return(historicos); }