public string[][] LookupShippersForCFDI()
{
DataSet dataSetLookUp = new DataSet();
dataSetLookUp = dataRetrieve.SelectDataRetrieve("select * from xCFDIXML Where Status = 'O'", Util.ConnectionString);
string[][] sShipperIdForCFDI = new string[dataSetLookUp.Tables[0].Rows.Count][];//{new string[dataSetLookUp.Tables[0].Rows.Count], new string[2]};
int i = 0;
foreach (DataRow dr in dataSetLookUp.Tables[0].Rows)
{
//Cancelled, InvcDate, InvcNbr, RFC, ShipperId
sShipperIdForCFDI[i] = new string[6];
sShipperIdForCFDI[i][0] = dr["Cancelled"].ToString();
sShipperIdForCFDI[i][1] = dr["InvcDate"].ToString();
_view.InvcDate = dr["InvcDate"].ToString();
sShipperIdForCFDI[i][2] = dr["InvcNbr"].ToString();
_view.InvcNbr = dr["InvcNbr"].ToString();
sShipperIdForCFDI[i][3] = dr["RFC"].ToString();
_view.RFCReceptor = dr["RFC"].ToString();
sShipperIdForCFDI[i][4] = dr["ShipperId"].ToString();
_view.ShipperIdStr = dr["ShipperId"].ToString();
sShipperIdForCFDI[i][5] = dr["UUID"].ToString();
if (dr["UUID"].ToString() != "")
{
_view.UUID = new System.Guid(dr["UUID"].ToString());
}
i++;
}
return(sShipperIdForCFDI);
}
private void txtboxShipperId_Leave(object sender, EventArgs e)
{
DataSet dataSetStatus = new DataSet();
dataSetStatus = dataRetrieve.SelectDataRetrieve("Select Status, XMLSAT, InvcNbr From xCFDIXML Where ShipperID = '" + txtboxShipperId.Text.Trim() + "'", Util.ConnectionString);
foreach (DataRow dr in dataSetStatus.Tables[0].Rows)
{
XDocument XMLTimbradoSelladoFirmado = new XDocument();
sXMLSAT = dr[1].ToString();
XMLTimbradoSelladoFirmado = XDocument.Parse(sXMLSAT);
XElement signatureSAT = new XElement("{http://www.sat.gob.mx/TimbreFiscalDigital}TimbreFiscalDigital");
uuid = XMLTimbradoSelladoFirmado.Root.Descendants(signatureSAT.Name).ToArray()[0].Attribute("UUID").Value;
FechaTimbrado = XMLTimbradoSelladoFirmado.Root.Descendants(signatureSAT.Name).ToArray()[0].Attribute("FechaTimbrado").Value;
selloCFD = XMLTimbradoSelladoFirmado.Root.Descendants(signatureSAT.Name).ToArray()[0].Attribute("selloCFD").Value;
noCertificadoSAT = XMLTimbradoSelladoFirmado.Root.Descendants(signatureSAT.Name).ToArray()[0].Attribute("noCertificadoSAT").Value;
selloSAT = XMLTimbradoSelladoFirmado.Root.Descendants(signatureSAT.Name).ToArray()[0].Attribute("selloSAT").Value;
sInvcNbr = dr[2].ToString();
switch (dr[0].ToString())
{
case "O": lblStatusCFDI.Text = "O - XML OPEN - INICIO DE XML";
btnGenerarPDF.Enabled = false;
break;
case "P": lblStatusCFDI.Text = "P - XML PARSER";
btnGenerarPDF.Enabled = false;
break;
case "U": lblStatusCFDI.Text = "U - XML SAT";
btnGenerarPDF.Enabled = true;
break;
case "Q": lblStatusCFDI.Text = "Q - XML QRCODE";
btnGenerarPDF.Enabled = true;
break;
case "X": lblStatusCFDI.Text = "X - XML SAVED ON DISC";
btnGenerarPDF.Enabled = true;
break;
case "C": lblStatusCFDI.Text = "C - COMPLETED";
btnGenerarPDF.Enabled = true;
break;
case "V": lblStatusCFDI.Text = "V - VOID";
btnGenerarPDF.Enabled = false;
break;
}
}
}
public XmlTextWriter fAddLevesXML(DataSet datasetCFDI_XML, XmlTextWriter writerXML, string sActualColumnLevel_2, string sActualColumnLevel_3)
{
string sCellValue;
string sActualColumnLevel_4;
sActualColumnLevel_4 = "";
foreach (DataRow datarowCFDI_XML in datasetCFDI_XML.Tables[0].Rows)
{
sActualColumnLevel_4 = "";
foreach (DataColumn datacolCFDI_XML in datasetCFDI_XML.Tables[0].Columns)
{
string[] sColumnNameSplit = fNivelesCol(datacolCFDI_XML.ColumnName);
sCellValue = datarowCFDI_XML[datacolCFDI_XML, DataRowVersion.Current].ToString();
if (sColumnNameSplit[0] != "null" && sCellValue != "")
{
switch (sColumnNameSplit.LongLength)
{
case 1:
writerXML.WriteAttributeString(sColumnNameSplit[0], sCellValue);
sActualColumnLevel_2 = sNivel0;
iLevelAnt = 1;
break;
case 2:
if (sActualColumnLevel_2 != sColumnNameSplit[0])
{
for (int i = 0; i < iLevelAnt - 1; i++)
{
writerXML.WriteEndElement();
}
sActualColumnLevel_2 = sColumnNameSplit[0];
writerXML.WriteStartElement("cfdi:" + sActualColumnLevel_2);
if (sColumnNameSplit[1] != "null")
{
writerXML.WriteAttributeString(sColumnNameSplit[1], sCellValue);
}
}
else if (sActualColumnLevel_2 == sColumnNameSplit[0])
{
if (sColumnNameSplit[1] != "null")
{
writerXML.WriteAttributeString(sColumnNameSplit[1], sCellValue);
}
}
iLevelAnt = 2;
break;
case 3:
if (sActualColumnLevel_2 == sColumnNameSplit[0] && sActualColumnLevel_3 != sColumnNameSplit[1])
{
if (iLevelAnt == 3)
{
writerXML.WriteEndElement();
}
if (iLevelAnt == 4)
{
writerXML.WriteEndElement();
writerXML.WriteEndElement();
}
sActualColumnLevel_3 = sColumnNameSplit[1];
writerXML.WriteStartElement("cfdi:" + sActualColumnLevel_3);
if (sColumnNameSplit[2] != "null")
{
writerXML.WriteAttributeString(sColumnNameSplit[2], sCellValue);
}
}
else if (sActualColumnLevel_2 == sColumnNameSplit[0] && sActualColumnLevel_3 == sColumnNameSplit[1])
{
if (sColumnNameSplit[2] != "null")
{
writerXML.WriteAttributeString(sColumnNameSplit[2], sCellValue);
}
}
else if (sActualColumnLevel_2 == sColumnNameSplit[0])
{
sActualColumnLevel_3 = sColumnNameSplit[1];
writerXML.WriteStartElement("cfdi:" + sActualColumnLevel_3);
if (sColumnNameSplit[2] != "null")
{
writerXML.WriteAttributeString(sColumnNameSplit[2], sCellValue);
}
}
iLevelAnt = 3;
break;
case 4:
if (sActualColumnLevel_2 == sColumnNameSplit[0] && sActualColumnLevel_3 == sColumnNameSplit[1] && sActualColumnLevel_4 != sColumnNameSplit[2])
{
if (iLevelAnt == 4)
{
writerXML.WriteEndElement();
}
sActualColumnLevel_4 = sColumnNameSplit[2];
writerXML.WriteStartElement("cfdi:" + sActualColumnLevel_4);
if (sColumnNameSplit[3] != "null")
{
writerXML.WriteAttributeString(sColumnNameSplit[3], sCellValue);
}
}
else if (sActualColumnLevel_2 == sColumnNameSplit[0] && sActualColumnLevel_3 == sColumnNameSplit[1] && sActualColumnLevel_4 == sColumnNameSplit[2])
{
if (sColumnNameSplit[3] != "null")
{
writerXML.WriteAttributeString(sColumnNameSplit[3], sCellValue);
}
}
iLevelAnt = 4;
break;
}
}
else if (sColumnNameSplit[0] == "null" && sColumnNameSplit[1] == "SQL")
{
DataSet datasetCFDI_XML_Nested = new DataSet();
DataRetrieve dataRetrieve = new DataRetrieve();
datasetCFDI_XML_Nested = dataRetrieve.SelectDataRetrieve(sCellValue, Util.ConnectionString);
writerXML = fAddLevesXML(datasetCFDI_XML_Nested, writerXML, sActualColumnLevel_2, sActualColumnLevel_3);
sActualColumnLevel_3 = "";
}
}
}
return(writerXML);
}
public void fCreatePDF(DataSet dsXML, string sXMLSAT, string sShipperId)
{
XDocument XMLTimbradoSelladoFirmado = new XDocument();
XMLTimbradoSelladoFirmado = XDocument.Parse(sXMLSAT);
XElement signatureSAT = new XElement("{http://www.sat.gob.mx/TimbreFiscalDigital}TimbreFiscalDigital");
string uuid = XMLTimbradoSelladoFirmado.Root.Descendants(signatureSAT.Name).ToArray()[0].Attribute("UUID").Value;
string FechaTimbrado = XMLTimbradoSelladoFirmado.Root.Descendants(signatureSAT.Name).ToArray()[0].Attribute("FechaTimbrado").Value;
string selloCFD = XMLTimbradoSelladoFirmado.Root.Descendants(signatureSAT.Name).ToArray()[0].Attribute("selloCFD").Value;
string noCertificadoSAT = XMLTimbradoSelladoFirmado.Root.Descendants(signatureSAT.Name).ToArray()[0].Attribute("noCertificadoSAT").Value;
string selloSAT = XMLTimbradoSelladoFirmado.Root.Descendants(signatureSAT.Name).ToArray()[0].Attribute("selloSAT").Value;
#region FILL DATASET WITH ADDITIONAL INFO FROM HEADER
DataSet datasetComplementoEGSA = new DataSet();
DataRow drEGSA = dsXML.Tables["EGSA"].NewRow();
string sComplementoEGSAHeader;
sComplementoEGSAHeader = "select * from xvr_ComplementoEGSAHeader where ShipperId = '" + sShipperId + "'";
datasetComplementoEGSA = dataRetrieve.SelectDataRetrieve(sComplementoEGSAHeader, Util.ConnectionString);
foreach (DataRow dr in datasetComplementoEGSA.Tables[0].Rows)
{
foreach (DataColumn dc in datasetComplementoEGSA.Tables[0].Columns)
{
drEGSA[dc.ColumnName] = dr[dc, DataRowVersion.Current].ToString();
}
}
drEGSA["Comprobante_Id"] = dsXML.Tables["Comprobante"].Rows[0]["Comprobante_Id"].ToString();
dsXML.Tables["EGSA"].Rows.Add(drEGSA);
#endregion
#region FILL DATASET WITH ADDITIONAL INFO FROM LINE
DataSet datasetComplementoEGSALine = new DataSet();
DataTable dataTableLineXML = new DataTable();
DataTable dataTableLineNotes = new DataTable();
string sComplementoEGSALine;
string sLotSerNbr;
string sTipoLoteSerie;
string sLineRefComplemento;
List <string> lLotSerNbrByLineRef = new List <string>();
string sInvtId;
string sInvtIdXML = "";
string sDescr;
string sDescrXML = "";
string sQtyShip;
string sQtyShipXML;
int iQtyShipXML = 0;
int iQtyShipLotSer;
int iRowCount;
int i;
string sPDFPath = Util.PDFPath;
string sSerie = dsXML.Tables["Comprobante"].Rows[0]["serie"].ToString();
string sFolio = dsXML.Tables["Comprobante"].Rows[0]["folio"].ToString();
string sRFCCliente = dsXML.Tables["Receptor"].Rows[0]["rfc"].ToString();
string sFactura = sSerie + sFolio;
sComplementoEGSALine = "select * from xvr_ComplementoEGSALine where Sample = 0 and ShipperId = '" + sShipperId + "' Order By LineRef";
datasetComplementoEGSA = dataRetrieve.SelectDataRetrieve(sComplementoEGSALine, Util.ConnectionString);
dataTableLineNotes = datasetComplementoEGSA.Tables[0];
dataTableLineXML = dsXML.Tables["Concepto"];
if (dataTableLineNotes.Rows.Count > 0)
{
sInvtId = dataTableLineNotes.Rows[0]["InvtId"].ToString().Trim().ToUpper();
sDescr = dataTableLineNotes.Rows[0]["Descr"].ToString().Trim().ToUpper();
sQtyShip = dataTableLineNotes.Rows[0]["QtyShip"].ToString().Trim().ToUpper();
iQtyShipLotSer = Convert.ToInt32(Math.Round(Convert.ToDecimal(sQtyShip, System.Globalization.CultureInfo.InvariantCulture)));
lLotSerNbrByLineRef.Add(dataTableLineNotes.Rows[0]["LotSerNbr"].ToString().Trim().ToUpper());
sTipoLoteSerie = dataTableLineNotes.Rows[0]["LotSerTrack"].ToString().Trim().ToUpper();
sLotSerNbr = dataTableLineNotes.Rows[0]["sNoteText"].ToString();
iRowCount = 0;
sInvtIdXML = dataTableLineXML.Rows[iRowCount]["noIdentificacion"].ToString().Trim();
sDescrXML = dataTableLineXML.Rows[iRowCount]["descripcion"].ToString().Trim();
sQtyShipXML = dataTableLineXML.Rows[iRowCount]["cantidad"].ToString().Trim();
iQtyShipXML = Convert.ToInt32(Math.Round(Convert.ToDecimal(sQtyShipXML, System.Globalization.CultureInfo.InvariantCulture)));
sLineRefComplemento = "";
for (i = 0; i < dataTableLineNotes.Rows.Count; i++)
{
if (sLineRefComplemento != dataTableLineNotes.Rows[i]["LineRef"].ToString())
{
sLineRefComplemento = dataTableLineNotes.Rows[i]["LineRef"].ToString();
if (sInvtIdXML == sInvtId && sDescrXML == sDescr && iQtyShipXML == iQtyShipLotSer)
{
if (lLotSerNbrByLineRef[0].ToString().Trim() != "")
{
sLotSerNbr = sTipoLoteSerie + string.Join(", ", lLotSerNbrByLineRef) + "\r\n" + sLotSerNbr;
}
dsXML.Tables["Concepto"].Rows[iRowCount]["Notas"] = sLotSerNbr;
lLotSerNbrByLineRef.Clear();
if (iRowCount + 1 < dataTableLineXML.Rows.Count)
{
iRowCount++;
}
sInvtIdXML = dataTableLineXML.Rows[iRowCount]["noIdentificacion"].ToString().Trim();
sDescrXML = dataTableLineXML.Rows[iRowCount]["descripcion"].ToString().Trim();
sQtyShipXML = dataTableLineXML.Rows[iRowCount]["cantidad"].ToString().Trim();
iQtyShipXML = Convert.ToInt32(Math.Round(Convert.ToDecimal(sQtyShipXML, System.Globalization.CultureInfo.InvariantCulture)));
sInvtId = dataTableLineNotes.Rows[i]["InvtId"].ToString().Trim().ToUpper();
sDescr = dataTableLineNotes.Rows[i]["Descr"].ToString().Trim().ToUpper();
sQtyShip = dataTableLineNotes.Rows[i]["QtyShip"].ToString().Trim().ToUpper();
iQtyShipLotSer = Convert.ToInt32(Math.Round(Convert.ToDecimal(sQtyShip, System.Globalization.CultureInfo.InvariantCulture)));
lLotSerNbrByLineRef.Add(dataTableLineNotes.Rows[i]["LotSerNbr"].ToString().Trim().ToUpper());
sTipoLoteSerie = dataTableLineNotes.Rows[i]["LotSerTrack"].ToString().Trim().ToUpper();
sLotSerNbr = dataTableLineNotes.Rows[i]["sNoteText"].ToString();
}
else
{
sInvtId = dataTableLineNotes.Rows[i]["InvtId"].ToString().Trim().ToUpper();
sDescr = dataTableLineNotes.Rows[i]["Descr"].ToString().Trim().ToUpper();
sQtyShip = dataTableLineNotes.Rows[i]["QtyShip"].ToString().Trim().ToUpper();
iQtyShipLotSer = Convert.ToInt32(Math.Round(Convert.ToDecimal(sQtyShip, System.Globalization.CultureInfo.InvariantCulture)));
lLotSerNbrByLineRef.Add(dataTableLineNotes.Rows[i]["LotSerNbr"].ToString().Trim().ToUpper());
sTipoLoteSerie = dataTableLineNotes.Rows[i]["LotSerTrack"].ToString().Trim().ToUpper();
}
}
else
{
iQtyShipLotSer = iQtyShipLotSer + Convert.ToInt32(Math.Round(Convert.ToDecimal(sQtyShip, System.Globalization.CultureInfo.InvariantCulture)));
lLotSerNbrByLineRef.Add(dataTableLineNotes.Rows[i]["LotSerNbr"].ToString());
}
}
if (sInvtIdXML == sInvtId.Trim() && sDescrXML == sDescr.Trim() && iQtyShipXML == iQtyShipLotSer)
{
sLotSerNbr = dataTableLineNotes.Rows[i - 1]["LotSerNbr"].ToString().Trim().ToUpper();
sTipoLoteSerie = dataTableLineNotes.Rows[i - 1]["LotSerTrack"].ToString().Trim().ToUpper();
if (sLotSerNbr != "")
{
sLotSerNbr = sTipoLoteSerie + string.Join(", ", lLotSerNbrByLineRef) + "\r\n" + dataTableLineNotes.Rows[i - 1]["sNoteText"].ToString();
}
else
{
sLotSerNbr = dataTableLineNotes.Rows[i - 1]["sNoteText"].ToString();
}
dsXML.Tables["Concepto"].Rows[iRowCount]["Notas"] = sLotSerNbr;
}
lLotSerNbrByLineRef.Clear();
}
#endregion
#region CREATE CRYSTAL REPORT, ASSIGN VARIABLES FROM SAT & EXPORT TO PDF
ReportDocument cryRpt = new ReportDocument();
string AppPathCrystal = AppDomain.CurrentDomain.BaseDirectory;
if (sRFCCliente.Trim() == "XEXX010101000")
{
AppPathCrystal = AppPathCrystal + @"\Reports\CFDIEGSAE.rpt";
}
else
{
AppPathCrystal = AppPathCrystal + @"\Reports\CFDIEGSA.rpt";
}
cryRpt.Load(AppPathCrystal); // se carga el crystalrepor para poder escribir en el
cryRpt.SetDataSource(dsXML);
cryRpt.SetParameterValue("ShipperId", sShipperId.ToUpper());
cryRpt.SetParameterValue("uuid", uuid);
cryRpt.SetParameterValue("FechaTimbrado", FechaTimbrado);
cryRpt.SetParameterValue("selloCFD", selloCFD);
cryRpt.SetParameterValue("noCertificadoSAT", noCertificadoSAT);
cryRpt.SetParameterValue("selloSAT", selloSAT);
cryRpt.ExportToDisk(ExportFormatType.PortableDocFormat, sPDFPath + sFactura + ".pdf"); // ubicacion donde se guardaran los arcivos
dataRetrieve.SelectDataRetrieve("update xCFDIXML set End_DateTime = GETDATE(), status = 'C' where shipperid = '" + sShipperId + "'", Util.ConnectionString);
cryRpt.Close();
cryRpt.Dispose();
#endregion
}
