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