public static void Trim(this PartsCat part)
{
part.CiudadCliente = part.CiudadCliente?.Trim();
part.CodigoPostalCliente = part.CodigoPostalCliente?.Trim();
part.ColoniaCliente = part.ColoniaCliente?.Trim();
part.Descripcion = part.Descripcion?.Trim();
part.DomicilioCliente = part.DomicilioCliente?.Trim();
part.EntidadFederativaCliente = part.EntidadFederativaCliente?.Trim();
part.FraccionArancelaria = part.FraccionArancelaria?.Trim();
part.NombreCliente = part.NombreCliente?.Trim();
part.NumeroExteriorCliente = part.NumeroExteriorCliente?.Trim();
part.NumeroInteriorCliente = part.NumeroInteriorCliente?.Trim();
part.NumeroParte = part.NumeroParte?.Trim();
part.NumeroProveedor = part.NumeroProveedor?.Trim();
part.Pais = part.Pais?.Trim();
part.PaisCliente = part.PaisCliente?.Trim();
part.RfcCliente = part.RfcCliente?.Trim();
}
public static string BuildQueryFindCustomer(this PartsCat part, int option)
{
var queryFindCustomer = "";
var entidadFederativaCustomer = "(SELECT sClave FROM [Admin].[ADMINC_02_ENTIDADES_FEDERATIVAS] WHERE nIdEntFed02 = ClDi.nIdEntFed02)";
var paisCustomer = "(SELECT sClavePais FROM [SIR].[SIR_01_PAISES] WHERE nIdPais01 = ClDi.nIdPais01)";
switch (option)
{
case 1:
queryFindCustomer = "SELECT Cl.nIdClie07 " +
"FROM [Admin].[ADMINC_07_CLIENTES] Cl " +
"INNER JOIN [Admin].[ADMINC_47_CLIENTES_DIR] ClDi ON ClDi.nIdClie07 = Cl.nIdClie07 " +
$"WHERE Cl.sClave = '{part.NumeroCliente}' " +
$"AND {(!string.IsNullOrEmpty(part.NombreCliente) ? $"Cl.sRazonSocial = '{part.NombreCliente}'" : "(Cl.sRazonSocial = '' OR Cl.sRazonSocial IS NULL)")} " +
$"AND Cl.sRFC = '{part.RfcCliente}' " +
$"AND ClDi.sCalle = '{part.DomicilioCliente}' " +
$"AND {(!string.IsNullOrEmpty(part.ColoniaCliente) ? $"ClDi.sColonia = '{part.ColoniaCliente}'" : "(ClDi.sColonia = '' OR ClDi.sColonia IS NULL)")} " +
$"AND {(!string.IsNullOrEmpty(part.NumeroInteriorCliente) ? $"ClDi.sNumInterior = '{part.NumeroInteriorCliente}'" : "(ClDi.sNumInterior = '' OR ClDi.sNumInterior IS NULL)")} " +
$"AND ClDi.sNumExterior = '{part.NumeroExteriorCliente}' " +
$"AND ClDi.sCP = '{part.CodigoPostalCliente}' " +
$"AND ClDi.sCiudad = '{part.CiudadCliente}' " +
$"AND {(!string.IsNullOrEmpty(part.EntidadFederativaCliente) ? $"{entidadFederativaCustomer} = '{part.EntidadFederativaCliente}'" : $"({entidadFederativaCustomer} = '' OR {entidadFederativaCustomer} IS NULL)")} " +
$"AND {(!string.IsNullOrEmpty(part.PaisCliente) ? $"{paisCustomer} = '{part.PaisCliente}'" : $"({paisCustomer} = '' OR {paisCustomer} IS NULL)")} " +
private static void ContinueOp(PartsCat part)
{
var responseFindUMT = part.FindUMT();
switch (responseFindUMT.Status)
{
case Status.Exception:
WriteLineFile($"Information: {part.PartToString()} | Description: {responseFindUMT.Description}", 1);
break;
case Status.Success:
part.TipoOperacion = part.NumeroProveedor.Equals("9999") ? 2 : 1;
var queryFindPart = "SELECT Pa.nIdParte99 " +
"FROM [SIR].[SIR].[SIR_99_PARTES] Pa " +
"INNER JOIN [SIR].[SIR].[SIR_383_RELAC_CLIENTE_PARTE] ReClPa ON ReClPa.nIdParte99 = Pa.nIdParte99 " +
$"WHERE ReClPa.nIdCliente = {part.NumeroCliente} " +
$"AND Pa.sParte = '{part.NumeroParte}' " +
$"AND Pa.sFraccion = '{part.FraccionArancelaria}' " +
$"AND Pa.nIdProveedor42 {(part.NumeroProveedor.Equals("9999") ? "IS NULL" : $"= {part.Supplier?.Numero ?? ""}")} " + //= {part.NumeroProveedor}
$"AND nTipoOperacion = {part.TipoOperacion} " +
$"AND Pa.bActiva = 1";
var responseFindPart = Sql.ExecuteReader(queryFindPart, new ConnectionMicrosoftSqlServer());
switch (responseFindPart.Status)
{
case Status.Exception:
WriteLineFile($"Information: {part.PartToString()} | Description: {responseFindPart.Description}", 1);
break;
case Status.Success:
var rowsFindPart = (responseFindPart.Data as DataTable).Rows;
if (rowsFindPart.Count > 0)
{
WriteLineFile($"Information: {part.PartToString()} | Description: La parte ya se encuentra en la base de datos.", 3);
}
else
{
var queryInsertPart = $"EXECUTE [uspInsertPartFromErycia] '{part.NumeroParte}', " +
$"'{part.Descripcion}', " +
$"'{part.Descripcion}', " +
$"'{part.FraccionArancelaria}', " +
$"'{part.Fecha.ToString("yyyy-MM-dd")}', " +
$"{part.Categoria}, " +
$"{part.ClaveUMT}, " +
$"{(part.NumeroProveedor.Equals("9999") ? "NULL" : $"{part.Supplier?.Numero ?? "NULL"}")}, " +
$"{part.TipoOperacion}";
var responseInsertPart = Sql.ExecuteNonStoredProcedure(queryInsertPart, new ConnectionMicrosoftSqlServer());
switch (responseInsertPart.Status)
{
case Status.Exception:
WriteLineFile($"Information: {part.PartToString()} | Description: {responseInsertPart.Description}", 1);
break;
case Status.Success:
countPartsInsertadas++;
WriteLineFile($"Information: {part.PartToString()} | Description: NULL ", 2);
var rows = (responseInsertPart.Data as DataTable).Rows;
var idParte = rows.Count > 0 ? rows[0]?.ItemArray[0]?.ToString() ?? "" : "";
if (!string.IsNullOrEmpty(idParte))
{
var queryInsertCustomerPart = "INSERT INTO [SIR].[SIR].[SIR_383_RELAC_CLIENTE_PARTE] (nIdParte99, nIdCliente) " +
$"VALUES ({idParte}, {part.NumeroCliente})";
var responseInsertCustomerPart = Sql.ExecuteNonQuery(queryInsertCustomerPart, new ConnectionMicrosoftSqlServer());
switch (responseInsertCustomerPart.Status)
{
case Status.Exception:
WriteLineFile($"Information: {part.PartToString()} | Description: {responseInsertCustomerPart.Description}", 1);
break;
case Status.Success:
countCustomerPartInsertados++;
WriteLineFile($"Information: nIdParte99: {idParte} nIdCliente: {part.NumeroCliente} | Description: NULL ", 2);
break;
case Status.Warning:
break;
default:
break;
}
}
else
{
WriteLineFile($"Information: {part.PartToString()} | Description: No devolvió el indice de la parte insertada.", 3);
}
break;
case Status.Warning:
break;
default:
break;
}
}
