public Detalle(string rutReceptor, string dvReceptor, string rznSocRecep, int mntNeto, ResultInstruction instruction, bool isParticipant)
{
RutReceptor = rutReceptor;
DvReceptor = dvReceptor;
RznSocRecep = rznSocRecep;
MntNeto = mntNeto;
Instruction = instruction;
IsParticipant = isParticipant;
StatusDetalle = StatusDetalle.Pending;
}
public static async Task <Auxiliar> GetAuxiliarAsync(ResultInstruction instruction, Conexion conexion)
{
try
{
StringBuilder query = new StringBuilder();
query.Append($"SELECT * FROM softland.cwtauxi WHERE CodAux = '{instruction.ParticipantDebtor.Rut}' ");
conexion.Query = query.ToString();
DataTable dataTable = new DataTable();
dataTable = await Conexion.ExecuteReaderAsync(conexion);
if (dataTable != null && dataTable.Rows.Count == 1)
{
Auxiliar auxiliar = new Auxiliar();
if (dataTable.Rows[0]["CodAux"] != DBNull.Value)
{
auxiliar.CodAux = dataTable.Rows[0]["CodAux"].ToString();
}
if (dataTable.Rows[0]["NomAux"] != DBNull.Value)
{
auxiliar.NomAux = dataTable.Rows[0]["NomAux"].ToString();
}
if (dataTable.Rows[0]["RutAux"] != DBNull.Value)
{
auxiliar.RutAux = dataTable.Rows[0]["RutAux"].ToString();
}
if (dataTable.Rows[0]["GirAux"] != DBNull.Value)
{
auxiliar.GirAux = dataTable.Rows[0]["GirAux"].ToString();
}
if (dataTable.Rows[0]["DirAux"] != DBNull.Value)
{
auxiliar.DirAux = dataTable.Rows[0]["DirAux"].ToString();
}
if (dataTable.Rows[0]["ComAux"] != DBNull.Value)
{
auxiliar.ComAux = dataTable.Rows[0]["ComAux"].ToString();
}
return(auxiliar);
}
}
catch (Exception)
{
throw;
}
return(null);
}
/// <summary>
/// Insert a NV & details
/// </summary>
/// <param name="instruction"></param>
/// <param name="folioNV"></param>
/// <param name="codProd"></param>
/// <param name="conexion"></param>
/// <returns></returns>
public static async Task <int> InsertNvAsync(ResultInstruction instruction, int?folioNV, string codProd, Conexion conexion)
{
try
{
StringBuilder query3 = new StringBuilder();
StringBuilder query1 = new StringBuilder();
StringBuilder query2 = new StringBuilder();
string date;
string now;
if (Environment.MachineName == "DEVELOPER")
{
// Developer
now = DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss", CultureInfo.InvariantCulture);
date = instruction.PaymentMatrix.PublishDate.ToString("dd-MM-yyyy", CultureInfo.InvariantCulture);
}
else
{
now = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture);
date = instruction.PaymentMatrix.PublishDate.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture);
}
int neto = instruction.Amount;
double iva = neto * 0.19;
double total = Math.Ceiling(neto + iva);
string concepto = $"Concepto: {instruction.AuxiliaryData.PaymentMatrixConcept}";
string rut;
if (instruction.ParticipantNew != null)
{
rut = instruction.ParticipantNew.Rut;
}
else
{
rut = instruction.ParticipantDebtor.Rut;
}
string solicitadoPor = instruction.PaymentMatrix.NaturalKey.Remove(0, 4);
query1.Append("INSERT INTO softland.nw_nventa (CodAux,CveCod,NomCon,nvFeEnt,nvFem,NVNumero,nvObser,VenCod,nvSubTotal, ");
query1.Append("nvNetoAfecto,nvNetoExento,nvMonto,proceso,nvEquiv,CodMon,nvEstado,FechaHoraCreacion, CodlugarDesp, SolicitadoPor) values ( ");
query1.Append($"'{rut}','1','.','{date}','{date}',{folioNV}, '{concepto}', '1',{neto},{neto},0,{total}, ");
query1.Append($"'Centralizador',1,'01','A','{now}','{instruction.PaymentMatrix.ReferenceCode}', '{solicitadoPor}') ");
query2.Append("INSERT INTO softland.nw_detnv (NVNumero,nvLinea,nvFecCompr,CodProd,nvCant,nvPrecio,nvSubTotal,nvTotLinea,CodUMed,CantUVta,nvEquiv)VALUES(");
query2.Append($"{folioNV},1,'{date}','{codProd}',1,{neto},{neto},{neto},'UN',1,1)");
query3.Append("INSERT INTO softland.NW_Impto (nvNumero, CodImpto, ValPctIni, AfectoImpto, Impto) VALUES ( ");
query3.Append($"{folioNV},'IVA',19,{neto},{iva})");
// Execute Transaction
if (!string.IsNullOrEmpty(query1.ToString()) || !string.IsNullOrEmpty(query2.ToString()) || !string.IsNullOrEmpty(query3.ToString()))
{
int res = Convert.ToInt32(await Conexion.ExecuteNonQueryTranAsync(conexion, new List <string> {
query1.ToString(), query2.ToString(), query3.ToString()
}));
return(res);
}
else
{
return(0);
}
}
catch (Exception)
{
throw;
}
}
public static async Task <List <DteInfoRef> > GetInfoRefAsync(ResultInstruction instruction, Conexion conexion, string tipo)
{
try
{
StringBuilder query = new StringBuilder();
List <DteInfoRef> lista = new List <DteInfoRef>();
DataTable dataTable = new DataTable();
int monto = 0;
string date = null;
if (Environment.MachineName == "DEVELOPER")
{
// Developer
date = instruction.PaymentMatrix.PublishDate.ToString("dd-MM-yyyy", CultureInfo.InvariantCulture);
}
else
{
date = instruction.PaymentMatrix.PublishDate.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture);
}
if (tipo == "NC")
{
monto = instruction.Amount * -1;
}
else if (tipo == "F")
{
monto = instruction.Amount;
}
query.AppendLine("SELECT g.nroint, ");
query.AppendLine(" g.folio, ");
query.AppendLine(" g.fecha, ");
query.AppendLine(" ( SELECT folio ");
query.AppendLine(" FROM softland.iw_gsaen ");
query.AppendLine(" WHERE tipo = 'N' ");
query.AppendLine(" AND auxdocnum = g.folio) AS auxdocnum, ");
query.AppendLine(" ( SELECT fecha ");
query.AppendLine(" FROM softland.iw_gsaen ");
query.AppendLine(" WHERE tipo = 'N' ");
query.AppendLine(" AND auxdocnum = g.folio) AS auxdocfec, ");
query.AppendLine(" g.netoafecto, ");
query.AppendLine(" g.netoexento, ");
query.AppendLine(" g.iva, ");
query.AppendLine(" g.total, ");
query.AppendLine(" g.nvnumero, ");
query.AppendLine(" g.fechoracreacion, ");
query.AppendLine(" g.fechagendte, ");
query.AppendLine(" r.linearef, ");
query.AppendLine(" r.codrefsii, ");
query.AppendLine(" r.folioref, ");
query.AppendLine(" r.fecharef, ");
query.AppendLine(" r.glosa, ");
query.AppendLine(" d.tipodte, ");
query.AppendLine(" d.rutrecep, ");
query.AppendLine(" d.archivo, ");
query.AppendLine(" d.enviadosii, ");
query.AppendLine(" d.fechaenviosii, ");
query.AppendLine(" d.aceptadosii, ");
query.AppendLine(" d.enviadocliente, ");
query.AppendLine(" d.fechaenviocliente, ");
query.AppendLine(" d.aceptadocliente, ");
query.AppendLine(" d.motivo, ");
query.AppendLine(" d.idsetdtecliente, ");
query.AppendLine(" d.idsetdtesii, ");
query.AppendLine(" d.firmadte, ");
query.AppendLine(" d.idxmldoc, ");
query.AppendLine(" d.trackid ");
query.AppendLine("FROM softland.iw_gsaen g ");
query.AppendLine("LEFT JOIN softland.iw_gsaen_refdte r ");
query.AppendLine("ON g.nroint = r.nroint ");
query.AppendLine(" AND g.tipo = r.tipo ");
query.AppendLine(" AND r.codrefsii = 'SEN' ");
query.AppendLine("LEFT JOIN softland.dte_doccab d ");
query.AppendLine("ON d.tipo = g.tipo ");
query.AppendLine(" AND d.nroint = g.nroint ");
query.AppendLine(" AND d.folio = g.folio ");
query.AppendLine($"WHERE g.netoafecto = {monto} ");
if (instruction.ParticipantNew == null)
{
query.AppendLine($" AND g.codaux = '{instruction.ParticipantDebtor.Rut}' ");
}
else
{
query.AppendLine($" AND g.codaux IN ( '{instruction.ParticipantDebtor.Rut}', '{instruction.ParticipantNew.Rut}' )");
}
query.AppendLine(" AND g.estado = 'V' ");
query.AppendLine($" AND g.fechoracreacion >= '{date}' ");
query.AppendLine($" AND g.tipo = '{tipo}' ");
query.AppendLine("ORDER BY g.folio DESC");
conexion.Query = query.ToString();
dataTable = await Conexion.ExecuteReaderAsync(conexion);
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow item in dataTable.Rows)
{
DteInfoRef reference = new DteInfoRef();
if (item[0] != DBNull.Value)
{
reference.NroInt = Convert.ToInt32(item[0]);
}
if (item[1] != DBNull.Value)
{
reference.Folio = Convert.ToInt32(item[1]);
}
if (item[2] != DBNull.Value)
{
reference.Fecha = Convert.ToDateTime(item[2]);
}
if (item[3] != DBNull.Value)
{
reference.AuxDocNum = Convert.ToInt32(item[3]);
}
if (item[4] != DBNull.Value)
{
reference.AuxDocfec = Convert.ToDateTime(item[4]);
}
if (item[5] != DBNull.Value)
{
reference.NetoAfecto = Convert.ToInt32(item[5]);
}
if (item[6] != DBNull.Value)
{
reference.NetoExento = Convert.ToInt32(item[6]);
}
if (item[7] != DBNull.Value)
{
reference.IVA = Convert.ToInt32(item[7]);
}
if (item[8] != DBNull.Value)
{
reference.Total = Convert.ToInt32(item[8]);
}
if (item[9] != DBNull.Value)
{
reference.Nvnumero = Convert.ToInt32(item[9]);
}
if (item[10] != DBNull.Value)
{
reference.FecHoraCreacion = Convert.ToDateTime(item[10]);
}
if (item[11] != DBNull.Value)
{
reference.FechaGenDTE = Convert.ToDateTime(item[11]);
}
if (item[12] != DBNull.Value)
{
reference.LineaRef = Convert.ToInt32(item[12]);
}
if (item[13] != DBNull.Value)
{
reference.CodRefSII = Convert.ToString(item[13]);
}
if (item[14] != DBNull.Value)
{
reference.FolioRef = Convert.ToString(item[14]);
}
if (item[15] != DBNull.Value)
{
reference.FechaRef = Convert.ToDateTime(item[15]);
}
if (item[16] != DBNull.Value)
{
reference.Glosa = Convert.ToString(item[16]);
}
if (item[17] != DBNull.Value)
{
reference.TipoDTE = Convert.ToInt32(item[17]);
}
if (item[18] != DBNull.Value)
{
reference.RUTRecep = Convert.ToString(item[18]);
}
if (item[19] != DBNull.Value)
{
reference.Archivo = Convert.ToString(item[19]);
}
if (item[20] != DBNull.Value)
{
reference.EnviadoSII = Convert.ToInt32(item[20]);
}
if (item[21] != DBNull.Value)
{
reference.FechaEnvioSII = Convert.ToDateTime(item[21]);
}
if (item[22] != DBNull.Value)
{
reference.AceptadoSII = Convert.ToInt32(item[22]);
}
if (item[23] != DBNull.Value)
{
reference.EnviadoCliente = Convert.ToInt32(item[23]);
}
if (item[24] != DBNull.Value)
{
reference.FechaEnvioCliente = Convert.ToDateTime(item[24]);
}
if (item[25] != DBNull.Value)
{
reference.AceptadoCliente = Convert.ToInt32(item[25]);
}
if (item[26] != DBNull.Value)
{
reference.Motivo = Convert.ToString(item[26]);
}
if (item[27] != DBNull.Value)
{
reference.IDSetDTECliente = Convert.ToString(item[27]);
}
if (item[28] != DBNull.Value)
{
reference.IDSetDTESII = Convert.ToString(item[28]);
}
if (item[29] != DBNull.Value)
{
reference.FirmaDTE = Convert.ToString(item[29]);
}
if (item[30] != DBNull.Value)
{
reference.IDXMLDoc = Convert.ToInt32(item[30]);
}
if (item[31] != DBNull.Value)
{
reference.TrackID = Convert.ToString(item[31]);
}
// SEARCH & ATTACH FILES REF.
List <DteFiles> files = await DataBase.DteFiles.GetDteFilesAsync(conexion, reference.NroInt, reference.Folio);
if (files != null)
{
reference.DteFiles = files;
}
else
{
files = await DataBase.DteFiles.GetDteFilesAsync(conexion, reference.IDXMLDoc);
reference.DteFiles = files;
}
lista.Add(reference);
}
return(lista);
}
}
catch (Exception)
{
throw;
}
return(null);
}
public static async Task <Auxiliar> InsertAuxiliarAsync(ResultInstruction instruction, Conexion conexion, Comuna comuna)
{
string acteco = null;
TextInfo ti = CultureInfo.CurrentCulture.TextInfo;
string time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture);
string adressTemp = ti.ToTitleCase(instruction.ParticipantDebtor.CommercialAddress.ToLower());
if (instruction.ParticipantDebtor.CommercialAddress.Contains(','))
{
int index = instruction.ParticipantDebtor.CommercialAddress.IndexOf(',');
adressTemp = instruction.ParticipantDebtor.CommercialAddress.Substring(0, index);
}
if (adressTemp.Length > 60)
{
adressTemp = adressTemp.Substring(0, 60);
}
// Get acteco from CEN
if (instruction.ParticipantDebtor.CommercialBusiness != null)
{
if (instruction.ParticipantDebtor.CommercialBusiness.Length > 60)
{
acteco = instruction.ParticipantDebtor.CommercialBusiness.Substring(0, 60);
}
else
{
acteco = instruction.ParticipantDebtor.CommercialBusiness;
}
// Insert new Acteco
await Acteco.InsertActecoAsync(acteco, conexion);
// Production:
if (Environment.MachineName == "DEVELOPER")
{
time = DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss", CultureInfo.InvariantCulture);
}
}
try
{
string rut = string.Format(CultureInfo.CurrentCulture, "{0:N0}", instruction.ParticipantDebtor.Rut).Replace(',', '.');
StringBuilder query = new StringBuilder();
query.Append($"IF (NOT EXISTS(SELECT * FROM softland.cwtauxi WHERE CodAux = '{instruction.ParticipantDebtor.Rut}')) BEGIN ");
query.Append("INSERT INTO softland.CWTAUXI (CodAux, NomAux, NoFAux, RutAux, ActAux, GirAux, PaiAux, Comaux, ");
query.Append("DirAux, ClaCli, ClaPro, Bloqueado, BloqueadoPro, EsReceptorDTE ,eMailDTE, Usuario, Proceso, Sistema, Region, FechaUlMod) ");
query.Append($"VALUES ('{instruction.ParticipantDebtor.Rut}', ");
query.Append($"'{instruction.ParticipantDebtor.BusinessName}','{instruction.ParticipantDebtor.Name}', ");
query.Append($"'{rut}-{instruction.ParticipantDebtor.VerificationCode}','S',(SELECT TOP 1 GirCod from softland.cwtgiro where GirDes = '{acteco}' ),'CL','{comuna.ComCod}', ");
query.Append($"'{adressTemp}','S', 'S','N', 'N', 'S','{instruction.ParticipantDebtor.DteReceptionEmail}' ");
query.Append($",'Softland','Centralizador', 'IW',{comuna.Id_Region}, '{time}') END");
conexion.Query = query.ToString();
var res = await Conexion.ExecuteNonQueryAsync(conexion);
if ((int)res == 2)
{
return(new Auxiliar()
{
DirAux = adressTemp,
ComAux = comuna.ComDes
});
}
}
catch (Exception)
{
throw;
}
return(null);
}
public async Task <int> UpdateAuxiliarAsync(ResultInstruction instruction, Conexion conexion, Comuna comuna)
{
string name;
string acteco = null;
CultureInfo cultureInfo = CultureInfo.GetCultureInfo("es-CL");
TextInfo ti = CultureInfo.CurrentCulture.TextInfo;
string time = string.Format(cultureInfo, "{0:yyyy-MM-dd HH:mm:ss}", DateTime.Now);
string adressTemp = ti.ToTitleCase(instruction.ParticipantDebtor.CommercialAddress.ToLower());
if (instruction.ParticipantDebtor.CommercialAddress.Contains(','))
{
int index = instruction.ParticipantDebtor.CommercialAddress.IndexOf(',');
adressTemp = instruction.ParticipantDebtor.CommercialAddress.Substring(0, index);
}
if (adressTemp.Length > 60)
{
adressTemp = adressTemp.Substring(0, 60);
}
// Address
if (instruction.ParticipantDebtor.BusinessName.Length > 60)
{
name = instruction.ParticipantDebtor.BusinessName.Substring(0, 60);
}
else
{
name = instruction.ParticipantDebtor.BusinessName;
}
// Get acteco from CEN
if (true)
{
}
if (instruction.ParticipantDebtor.CommercialBusiness != null)
{
if (instruction.ParticipantDebtor.CommercialBusiness.Length > 60)
{
acteco = instruction.ParticipantDebtor.CommercialBusiness.Substring(0, 60);
}
else
{
acteco = instruction.ParticipantDebtor.CommercialBusiness;
}
// Insert new Acteco
await Acteco.InsertActecoAsync(acteco, conexion);
}
// Production:
if (Environment.MachineName == "DEVELOPER")
{
time = string.Format(cultureInfo, "{0:g}", DateTime.Now);
}
try
{
StringBuilder query = new StringBuilder();
string rut = string.Format(CultureInfo.CurrentCulture, "{0:N0}", instruction.ParticipantDebtor.Rut).Replace(',', '.');
query.Append($"UPDATE softland.cwtauxi SET NomAux='{name}', NoFAux='{instruction.ParticipantDebtor.Name}', ClaCli = 'S', ClaPro = 'S', Bloqueado = 'N', ");
query.Append($"Comaux = '{comuna.ComCod}', GirAux = (SELECT TOP 1 GirCod from softland.cwtgiro where GirDes = '{acteco}' ), EsReceptorDTE = 'S', PaiAux='CL', ActAux='S', ");
query.Append($"Usuario='Softland', Proceso='Centralizador', Sistema='IW', RutAux='{rut}-{instruction.ParticipantDebtor.VerificationCode}', FechaUlMod ='{time}', ");
query.Append($"DirAux = '{adressTemp}', ");
query.Append($"eMailDTE='{instruction.ParticipantDebtor.DteReceptionEmail}' WHERE CodAux='{instruction.ParticipantDebtor.Rut}'");
conexion.Query = query.ToString();
var res = await Conexion.ExecuteNonQueryAsync(conexion);
return((int)res);
}
catch (Exception)
{
throw;
}
}
public async Task <List <Detalle> > GetDebtor(List <Detalle> detalles, string p)
{
int c = 0;
ServiceEvento dataEvento = new ServiceEvento(TokenSii);
List <Detalle> detallesFinal = new List <Detalle>();
List <Task <List <Detalle> > > tareas = new List <Task <List <Detalle> > >();
tareas = detalles.Select(async item =>
{
DTEDefType xmlObjeto = null;
string nameFile = null;
// GET XML FILE
nameFile = p + $"\\{UserParticipant.Rut}-{UserParticipant.VerificationCode}\\{item.RutReceptor}-{item.DvReceptor}__{item.Tipo}__{item.Folio}.xml";
if (File.Exists(nameFile))
{
xmlObjeto = HSerialize.DTE_To_Object(nameFile);
}
// GET PARTICPANT INFO FROM CEN
ResultParticipant participant = await Participant.GetParticipantByRutAsync(item.RutReceptor.ToString());
if (participant != null && participant.Id > 0)
{
item.IsParticipant = true;
item.ParticipantMising = participant;
}
if (xmlObjeto != null)
{
item.DTEDef = xmlObjeto;
if (item.IsParticipant)
{
// GET REFERENCE SEN.
DTEDefTypeDocumentoReferencia r = null;
GetReferenceCen doc = new GetReferenceCen(item);
if (doc != null)
{
r = doc.DocumentoReferencia;
}
if (r != null && r.RazonRef != null)
{
// GET WINDOW.
ResultBillingWindow window = await BillingWindow.GetBillingWindowByNaturalKeyAsync(r);
// GET MATRIX.
if (window != null && window.Id > 0)
{
List <ResultPaymentMatrix> matrices = await PaymentMatrix.GetPaymentMatrixByBillingWindowIdAsync(window);
if (matrices != null && matrices.Count > 0)
{
ResultPaymentMatrix matrix = matrices.FirstOrDefault(x => x.NaturalKey.Equals(r.RazonRef.Trim(), StringComparison.OrdinalIgnoreCase));
if (matrix != null)
{
ResultInstruction instruction = await Instruction.GetInstructionDebtorAsync(matrix, participant, UserParticipant);
if (instruction != null && instruction.Id > 0)
{
item.Instruction = instruction;
item.Instruction.ParticipantCreditor = participant;
item.Instruction.ParticipantDebtor = UserParticipant;
}
}
}
}
}
}
}
// FLAGS IF EXISTS XML FILE
item.ValidatorFlag = new HFlagValidator(item, false);
// EVENTS FROM SII
item.DataEvento = await dataEvento.GetStatusDteAsync2("Debtor", TokenSii, "33", item, UserParticipant);
// STATUS DOC
if (item.DataEvento != null)
{
item.StatusDetalle = GetStatus(item);
}
// INSERT IN CEN
if (item.StatusDetalle == StatusDetalle.Accepted && item.Instruction != null)
{
// 1 No Facturado y cuando hay más de 1 dte informado 2 Facturado 3 Facturado
// con retraso Existe el DTE?
ResultDte doc = await Dte.GetDteAsync(item, false);
if (doc == null)
{
// Enviar el DTE
ResultDte resultDte = await Dte.SendDteDebtorAsync(item, TokenCen);
if (resultDte != null && resultDte.Folio > 0)
{
item.Instruction.Dte = resultDte;
}
}
else
{
item.Instruction.Dte = doc;
}
}
detallesFinal.Add(item);
c++;
float porcent = (float)(100 * c) / detalles.Count;
await ReportProgress(porcent, $"Processing 'Pay Instructions' {item.Folio}, wait please. ({c}/{detalles.Count})");
return(detalles);
}).ToList();
await Task.WhenAll(tareas);
return(detalles.OrderBy(x => x.FechaRecepcion).ToList());
}
