private static DocumentConcept GetDocumentConcept(int docId, string filePath)
{
DocumentConcept response = null;
int connDocos, dbResponse, fqResponse;
StringBuilder sConceptCode = new StringBuilder(31);
StringBuilder sConceptName = new StringBuilder(61);
string key, conceptCode, conceptName;
connDocos = AdminPaqLib.dbLogIn("", filePath);
if (connDocos == 0)
{
ErrLogger.Log("Unable to open connection to concepts table from [" + filePath + "]");
return(response);
}
key = docId.ToString().PadLeft(11);
dbResponse = AdminPaqLib.dbGetNoLock(connDocos, TableNames.CONCEPTOS_DOCUMENTOS, IndexNames.PRIMARY_KEY, key);
if (dbResponse == 0)
{
fqResponse = AdminPaqLib.dbFieldChar(connDocos, TableNames.CONCEPTOS_DOCUMENTOS, 2, sConceptCode, 31);
conceptCode = sConceptCode.ToString().Substring(0, 30).Trim();
fqResponse = AdminPaqLib.dbFieldChar(connDocos, TableNames.CONCEPTOS_DOCUMENTOS, 3, sConceptName, 61);
conceptName = sConceptName.ToString().Substring(0, 60).Trim();
response = new DocumentConcept();
response.Id = docId;
response.Code = conceptCode;
response.Name = conceptName;
}
AdminPaqLib.dbLogOut(connDocos);
return(response);
}
private string GetCompanyCode(int companyId, string filePath)
{
int connCompany, dbResponse, fqResponse;
StringBuilder sbCompanyCode = new StringBuilder(31);
string key, result = null;
connCompany = AdminPaqLib.dbLogIn("", filePath);
if (connCompany == 0)
{
return(null);
}
key = companyId.ToString().PadLeft(11);
dbResponse = AdminPaqLib.dbGetNoLock(connCompany, "MGW10002", "PRIMARYKEY", key);
if (dbResponse == 0)
{
fqResponse = AdminPaqLib.dbFieldChar(connCompany, "MGW10002", 2, sbCompanyCode, 31);
result = sbCompanyCode.ToString().Substring(0, 30).Trim();
}
AdminPaqLib.dbLogOut(connCompany);
return(result);
}
public void UpdateCollectable(Account account)
{
int connection, dbResponse;
string key, command;
connection = AdminPaqLib.dbLogIn("", account.Company.EnterprisePath);
if (connection == 0)
{
ErrLogger.Log("Unable to open connection to documents table for company [" + account.Company.EnterprisePath + "]");
throw new Exception("No se pudo establecer conexión con adminPaq en la siguiente ruta: " + account.Company.EnterprisePath);
}
key = account.ApId.ToString().PadLeft(11);
dbResponse = AdminPaqLib.dbGet(connection, TableNames.DOCUMENTOS, IndexNames.PRIMARY_KEY, key);
if (dbResponse == 0)
{
if (account.CollectDate.Ticks > 0)
{
string sCollectDate = account.CollectDate.ToString("yyyyMMdd");
command = string.Format("UPDATE {0}(CFECHAEX01=\"{1}\",CTEXTOEX01=\"{2}\",CTEXTOEX02=\"{3}\");",
TableNames.DOCUMENTOS, sCollectDate, account.CollectType, account.Note);
}
else
{
command = string.Format("UPDATE {0}(CFECHAEX01=\"{1}\",CTEXTOEX01=\"{2}\",CTEXTOEX02=\"{3}\");",
TableNames.DOCUMENTOS, "18991230", account.CollectType, account.Note);
}
dbResponse = AdminPaqLib.dbCmdExec(connection, command);
if (dbResponse != 0)
{
dbResponse = AdminPaqLib.dbCmdExec(connection, "ROLLBACK;");
AdminPaqLib.dbLogOut(connection);
throw new Exception("No se pudo actualizar el registro");
}
else
{
dbResponse = AdminPaqLib.dbCmdExec(connection, "COMMIT;");
if (dbResponse != 0)
{
dbResponse = AdminPaqLib.dbCmdExec(connection, "ROLLBACK;");
AdminPaqLib.dbLogOut(connection);
throw new Exception("No se pudo confirmar la actualización del registro.");
}
}
}
else
{
AdminPaqLib.dbLogOut(connection);
throw new Exception("El registro del documento se encuentra bloqueado por otro usuario. \n" +
"folio: " + account.ApId.ToString() + "\n" +
"ruta empresa: " + account.Company.EnterprisePath);
}
AdminPaqLib.dbLogOut(connection);
}
public void UpdateAccounts(EventLog log)
{
var configuredClients = ConfigurationManager.AppSettings as NameValueCollection;
if (configuredClients == null)
{
log.WriteEntry("Unable to load the configuration file.", EventLogEntryType.Warning, 13, 1);
return;
}
if (configuredClients.Count == 0)
{
log.WriteEntry("No keys detected in configuration file.", EventLogEntryType.Warning, 14, 1);
return;
}
foreach (var key in configuredClients.AllKeys)
{
string configuredClient = configuredClients.GetValues(key).FirstOrDefault();
EnterpriseSection clientConfig = (EnterpriseSection)System.Configuration.ConfigurationManager.GetSection("Empresas/" + configuredClient);
if (clientConfig == null)
{
log.WriteEntry("Client configuration not found for Empresas/" + configuredClient + ".", EventLogEntryType.Warning, 15, 1);
continue;
}
Empresa empresa = PgDbCollector.GetCompanyByName(clientConfig.NombreEmpresa);
log.WriteEntry("Client configuration found in database: " + clientConfig.NombreEmpresa + "; " + empresa.Ruta);
if (empresa != null)
{
log.WriteEntry("Payment codes found for " + clientConfig.NombreEmpresa + " as [" + clientConfig.CodigosPago + "]", EventLogEntryType.Information, 17, 1);
string[] abonos = clientConfig.CodigosPago.Split(',');
List <int> accounts = PgDbCollector.GetAccointIds(empresa.Id);
log.WriteEntry("Updating " + accounts.Count + " Accounts", EventLogEntryType.Information, 19, 1);
int connection = AdminPaqLib.dbLogIn("", empresa.Ruta);
if (connection == 0)
{
throw new Exception("No fue posible establecer la conexión con la base de datos de la empresa en la ruta: " + empresa.Ruta);
}
foreach (int accountId in accounts)
{
Account account = PgDbCollector.GetAccountById(accountId);
AdminPaqImpl.DownloadCollectable(account, abonos, connection);
}
accounts.Clear();
accounts = null;
AdminPaqLib.dbLogOut(connection);
}
}
}
public void SetCollectDate(int docId, DateTime collectDate, string rutaEmpresa)
{
int connection, dbResponse;
string key, command;
connection = AdminPaqLib.dbLogIn("", rutaEmpresa);
if (connection == 0)
{
ErrLogger.Log("Unable to open connection to documents table for company [" + rutaEmpresa + "]");
throw new Exception("No se pudo establecer conexión con adminPaq en la siguiente ruta: " + rutaEmpresa);
}
key = docId.ToString().PadLeft(11);
dbResponse = AdminPaqLib.dbGet(connection, TableNames.DOCUMENTOS, IndexNames.PRIMARY_KEY, key);
if (dbResponse == 0)
{
string sCollectDate = collectDate.ToString("yyyyMMdd");
command = string.Format("UPDATE {0}(CFECHAEX01=\"{1}\");",
TableNames.DOCUMENTOS, sCollectDate);
dbResponse = AdminPaqLib.dbCmdExec(connection, command);
if (dbResponse != 0)
{
dbResponse = AdminPaqLib.dbCmdExec(connection, "ROLLBACK;");
AdminPaqLib.dbLogOut(connection);
throw new Exception("No se pudo actualizar el registro");
}
else
{
dbResponse = AdminPaqLib.dbCmdExec(connection, "COMMIT;");
if (dbResponse != 0)
{
dbResponse = AdminPaqLib.dbCmdExec(connection, "ROLLBACK;");
AdminPaqLib.dbLogOut(connection);
throw new Exception("No se pudo confirmar la actualización del registro.");
}
}
}
else
{
AdminPaqLib.dbLogOut(connection);
throw new Exception("El registro del documento se encuentra bloqueado por otro usuario. \n" +
"folio: " + docId + "\n" +
"ruta empresa: " + rutaEmpresa);
}
AdminPaqLib.dbLogOut(connection);
}
public AdminPaqImp()
{
lib = new AdminPaqLib();
lib.SetDllFolder();
empresas = new List <Empresa>();
Empresa defEmpresa = new Empresa();
defEmpresa.Id = 0;
defEmpresa.Nombre = "Seleccione Empresa";
empresas.Add(defEmpresa);
InitializeSDK();
}
private static List <CatCliente> GetClientes(int connectionId)
{
List <CatCliente> result = new List <CatCliente>();
int dbResponse, fqResponse;
int STEP = 1, LOCAL = 1;
int CCODIGOC01 = 2, CRAZONSO01 = 3, CIDVALOR01 = 14, CRFC = 5;
string PRIMARYKEY = "PRIMARYKEY";
int location = 0;
StringBuilder sbCompanyCode = new StringBuilder(31);
StringBuilder sbCompanyName = new StringBuilder(61);
StringBuilder sbRfc = new StringBuilder(21);
dbResponse = AdminPaqLib.dbGetTopNoLock(connectionId, TABLE_NAME, PRIMARYKEY);
while (dbResponse == 0)
{
fqResponse = AdminPaqLib.dbFieldLong(connectionId, TABLE_NAME, CIDVALOR01, ref location);
if (location == 0)
{
dbResponse = AdminPaqLib.dbSkip(connectionId, TABLE_NAME, PRIMARYKEY, STEP);
continue;
}
CatCliente cliente = new CatCliente();
cliente.EsLocal = (location == LOCAL); // LOCAL=1; FORANEO=2
fqResponse = AdminPaqLib.dbFieldChar(connectionId, TABLE_NAME, CCODIGOC01, sbCompanyCode, 31);
cliente.CodigoCliente = sbCompanyCode.ToString().Substring(0, 30).Trim();
fqResponse = AdminPaqLib.dbFieldChar(connectionId, TABLE_NAME, CRAZONSO01, sbCompanyName, 61);
cliente.RazonSocial = sbCompanyName.ToString().Substring(0, 60).Trim();
fqResponse = AdminPaqLib.dbFieldChar(connectionId, TABLE_NAME, CRFC, sbRfc, 21);
cliente.RFC = sbRfc.ToString().Substring(0, 20).Trim();
result.Add(cliente);
dbResponse = AdminPaqLib.dbSkip(connectionId, TABLE_NAME, PRIMARYKEY, STEP);
}
return(result);
}
private static List <CatSeller> GetSellers(int connectionId)
{
List <CatSeller> result = new List <CatSeller>();
int dbResponse;
int STEP = 1;
int CIDAGENTE = 1, CCODIGOA01 = 2, CNOMBREA01 = 3, CTIPOAGE01 = 5;
int COLLECTOR_TYPE = 3;
string PRIMARYKEY = "PRIMARYKEY";
StringBuilder sbNombreAgente = new StringBuilder(61);
StringBuilder sbCodigoAgente = new StringBuilder(31);
int id = 0, type = 0;
dbResponse = AdminPaqLib.dbGetTopNoLock(connectionId, TABLE_NAME, PRIMARYKEY);
while (dbResponse == 0)
{
AdminPaqLib.dbFieldLong(connectionId, TABLE_NAME, CTIPOAGE01, ref type);
if (type == COLLECTOR_TYPE)
{
dbResponse = AdminPaqLib.dbSkip(connectionId, TABLE_NAME, PRIMARYKEY, STEP);
continue;
}
CatSeller seller = new CatSeller();
AdminPaqLib.dbFieldLong(connectionId, TABLE_NAME, CIDAGENTE, ref id);
seller.IdVendedor = id;
AdminPaqLib.dbFieldChar(connectionId, TABLE_NAME, CCODIGOA01, sbCodigoAgente, 31);
seller.CodigoVendedor = sbCodigoAgente.ToString().Substring(0, 30).Trim();
AdminPaqLib.dbFieldChar(connectionId, TABLE_NAME, CNOMBREA01, sbNombreAgente, 61);
seller.NombreVendedor = sbNombreAgente.ToString().Substring(0, 60).Trim();
result.Add(seller);
dbResponse = AdminPaqLib.dbSkip(connectionId, TABLE_NAME, PRIMARYKEY, STEP);
}
return(result);
}
public static List <CatSeller> GetSellers(string rutaEmpresa)
{
int connSellers = AdminPaqLib.dbLogIn("", rutaEmpresa);
/*if (!Directory.Exists(rutaEmpresa))
* {
* throw new Exception("Unable to validate existance of company directory for SELLERS: " + rutaEmpresa);
* }*/
if (connSellers == 0)
{
throw new Exception("Unable to work with route [" + rutaEmpresa + "]");
}
List <CatSeller> result = GetSellers(connSellers);
AdminPaqLib.dbLogOut(connSellers);
return(result);
}
public static List <CatCliente> GetClientes(string rutaEmpresa)
{
// Generate connection from company route
int connClientes = AdminPaqLib.dbLogIn("", rutaEmpresa);
if (!Directory.Exists(rutaEmpresa))
{
throw new Exception("Unable to validate existance of company directory for CLIENTES: " + rutaEmpresa);
}
if (connClientes == 0)
{
throw new Exception("Unable to work with route [" + rutaEmpresa + "]");
}
List <CatCliente> result = GetClientes(connClientes);
AdminPaqLib.dbLogOut(connClientes);
return(result);
}
private void InitializeSDK()
{
int connEmpresas, dbResponse, fieldResponse;
connEmpresas = AdminPaqLib.dbLogIn("", lib.DataDirectory);
if (connEmpresas == 0)
{
ErrLogger.Log("No se pudo crear conexión a la tabla de Empresas de AdminPAQ.");
return;
}
dbResponse = AdminPaqLib.dbGetTopNoLock(connEmpresas, TableNames.EMPRESAS, IndexNames.PRIMARY_KEY);
while (dbResponse == 0)
{
Empresa empresa = new Empresa();
int idEmpresa = 0;
fieldResponse = AdminPaqLib.dbFieldLong(connEmpresas, TableNames.EMPRESAS, Empresa.ID_EMPRESA, ref idEmpresa);
empresa.Id = idEmpresa;
StringBuilder nombreEmpresa = new StringBuilder(151);
fieldResponse = AdminPaqLib.dbFieldChar(connEmpresas, TableNames.EMPRESAS, Empresa.NOMBRE_EMPRESA, nombreEmpresa, 151);
string sNombreEmpresa = nombreEmpresa.ToString(0, 150).Trim();
empresa.Nombre = sNombreEmpresa;
StringBuilder rutaEmpresa = new StringBuilder(254);
fieldResponse = AdminPaqLib.dbFieldChar(connEmpresas, TableNames.EMPRESAS, Empresa.RUTA_EMPRESA, rutaEmpresa, 254);
string sRutaEmpresa = rutaEmpresa.ToString(0, 253).Trim();
empresa.Ruta = sRutaEmpresa;
empresas.Add(empresa);
dbResponse = AdminPaqLib.dbSkip(connEmpresas, TableNames.EMPRESAS, IndexNames.PRIMARY_KEY, 1);
}
AdminPaqLib.dbLogOut(connEmpresas);
}
private int AdvanceConnectionIndex(string tipoDoc, string startDate, bool useDocDate, int connection)
{
int dbResponse;
string key;
key = tipoDoc;
if (useDocDate)
{
key = tipoDoc + startDate;
dbResponse = AdminPaqLib.dbGetNoLock(connection, TableNames.DOCUMENTOS, IndexNames.DOCUMENTOS_ID_DOCUMENTO01, key);
if (dbResponse != 0)
{
key = tipoDoc + startDate.Substring(0, 6);
dbResponse = AdminPaqLib.dbGetNoLock(connection, TableNames.DOCUMENTOS, IndexNames.DOCUMENTOS_ID_DOCUMENTO01, key);
if (dbResponse != 0)
{
key = tipoDoc + startDate.Substring(0, 4);
dbResponse = AdminPaqLib.dbGetNoLock(connection, TableNames.DOCUMENTOS, IndexNames.DOCUMENTOS_ID_DOCUMENTO01, key);
if (dbResponse != 0)
{
key = tipoDoc;
dbResponse = AdminPaqLib.dbGetNoLock(connection, TableNames.DOCUMENTOS, IndexNames.DOCUMENTOS_ID_DOCUMENTO01, key);
}
}
}
}
else
{
dbResponse = AdminPaqLib.dbGetNoLock(connection, TableNames.DOCUMENTOS, IndexNames.DOCUMENTOS_ID_DOCUMENTO01, key);
}
return(dbResponse);
}
private static string CurrencyName(int currencyId, string filePath)
{
int connCurrency, dbResponse, fqResponse;
StringBuilder sbCurrencyName = new StringBuilder(61);
string key, sCurrencyName = String.Empty;
connCurrency = AdminPaqLib.dbLogIn("", filePath);
if (connCurrency == 0)
{
ErrLogger.Log("Unable to open connection for currency table from [" + filePath + "]");
return(null);
}
key = currencyId.ToString().PadLeft(11);
dbResponse = AdminPaqLib.dbGetNoLock(connCurrency, TableNames.MONEDAS, IndexNames.PRIMARY_KEY, key);
if (dbResponse == 0)
{
fqResponse = AdminPaqLib.dbFieldChar(connCurrency, TableNames.MONEDAS, 6, sbCurrencyName, 61);
sCurrencyName = sbCurrencyName.ToString().Substring(0, 60).Trim();
}
AdminPaqLib.dbLogOut(connCurrency);
return(sCurrencyName);
}
public void Execute(Empresa empresa, string[] conceptosFactura, string[] conceptosAbono, string[] codigosVenta,
string[] codigosDevolucion, EventLog log)
{
int connDocos, dbResponse, fieldResponse;
DateTime today = DateTime.Today;
DateTime fromDate = today.AddYears(-1);
DateTime toDate = today;
DateTime dueDate = today;
string sFromDate = fromDate.ToString("yyyyMMdd");
Dictionary <int, string> currencies = new Dictionary <int, string>();
Dictionary <int, DimClientes> customers = new Dictionary <int, DimClientes>();
int cancelado = 0, devuelto = 0, impreso = 0, conceptId = 0, companyId = 0, currencyId = 0, idAgente = 0;
StringBuilder sbFechaDoc = new StringBuilder(9);
StringBuilder sbFechaVto = new StringBuilder(9);
string sFechaDoc, sFechaVto, companyCode, currencyName;
double saldo = 0, cambio = 0;
bool esVenta = false, esDevolucion = false, esCredito = false, esPago = false;
connDocos = AdminPaqLib.dbLogIn("", empresa.Ruta);
if (connDocos == 0)
{
throw new Exception("Unable to work with route [" + empresa.Ruta + "]");
}
dbResponse = AdminPaqLib.dbGetTopNoLock(connDocos, TABLE_NAME, INDEX);
log.WriteEntry("Downloading documents from AdminPaq");
if (dbResponse != 0)
{
AdminPaqLib.dbLogOut(connDocos);
log.WriteEntry("No data found in database, the following exception code was reported: "
+ dbResponse.ToString() + " on path: " + empresa.Ruta, EventLogEntryType.Warning);
throw new Exception("No data found in database, the following exception code was reported: "
+ dbResponse.ToString());
}
int cancelados = 0, devueltos = 0, otros = 0, no_impresos = 0, no_co = 0, no_cliente = 0, valido = 0;
while (dbResponse == 0)
{
esVenta = false;
esDevolucion = false;
esCredito = false;
esPago = false;
AdminPaqLib.dbFieldLong(connDocos, TABLE_NAME, 26, ref cancelado);
if (cancelado != 0)
{
dbResponse = AdminPaqLib.dbSkip(connDocos, TABLE_NAME, INDEX, 1);
cancelados++;
continue;
}
AdminPaqLib.dbFieldLong(connDocos, TABLE_NAME, 27, ref devuelto);
if (devuelto != 0)
{
dbResponse = AdminPaqLib.dbSkip(connDocos, TABLE_NAME, INDEX, 1);
devueltos++;
continue;
}
AdminPaqLib.dbFieldLong(connDocos, TABLE_NAME, 3, ref conceptId);
DocumentConcept concept = GetDocumentConcept(conceptId, empresa.Ruta);
if (concept == null)
{
dbResponse = AdminPaqLib.dbSkip(connDocos, TABLE_NAME, INDEX, 1);
otros++;
continue;
}
esVenta = codigosVenta != null && codigosVenta.Contains(concept.Code);
esDevolucion = codigosDevolucion != null && codigosDevolucion.Contains(concept.Code);
esCredito = conceptosFactura.Contains(concept.Code);
esPago = conceptosAbono.Contains(concept.Code);
if (!esVenta && !esDevolucion && !esCredito && !esPago)
{
dbResponse = AdminPaqLib.dbSkip(connDocos, TABLE_NAME, INDEX, 1);
otros++;
continue;
}
if (esVenta)
{
AdminPaqLib.dbFieldLong(connDocos, TABLE_NAME, 25, ref impreso);
if (impreso == 0)
{
dbResponse = AdminPaqLib.dbSkip(connDocos, TABLE_NAME, INDEX, 1);
no_impresos++;
continue;
}
}
if (esCredito)
{
AdminPaqLib.dbFieldDouble(connDocos, TABLE_NAME, 44, ref saldo);
}
else
{
AdminPaqLib.dbFieldDouble(connDocos, TABLE_NAME, 31, ref saldo);
}
AdminPaqLib.dbFieldLong(connDocos, TABLE_NAME, 15, ref currencyId);
if (!currencies.ContainsKey(currencyId))
{
currencyName = CurrencyName(currencyId, empresa.Ruta);
if (currencyName != null)
{
currencies.Add(currencyId, currencyName);
}
}
currencyName = currencies[currencyId];
if (!currencyName.ToUpper().Contains("PESO"))
{
AdminPaqLib.dbFieldDouble(connDocos, TABLE_NAME, 16, ref cambio);
saldo = saldo * cambio;
}
if (esDevolucion)
{
saldo = saldo * -1;
}
AdminPaqLib.dbFieldChar(connDocos, TABLE_NAME, 6, sbFechaDoc, 9);
sFechaDoc = sbFechaDoc.ToString().Substring(0, 8).Trim();
AdminPaqLib.dbFieldLong(connDocos, TABLE_NAME, 10, ref idAgente);
AdminPaqDocument doco = new AdminPaqDocument();
doco.IsCredit = esCredito;
doco.IsSale = esVenta || esDevolucion;
doco.IsPayment = esPago;
doco.Amount = saldo;
doco.SellerId = idAgente;
doco.DocumentDate = DateTime.ParseExact(sFechaDoc, "yyyyMMdd", CultureInfo.InvariantCulture, DateTimeStyles.None);
// SI ES CREDITO NECESITO LA FECHA DE VTO.
if (esCredito)
{
AdminPaqLib.dbFieldChar(connDocos, TABLE_NAME, 11, sbFechaVto, 9);
sFechaVto = sbFechaVto.ToString().Substring(0, 8).Trim();
doco.DueDate = DateTime.ParseExact(sFechaVto, "yyyyMMdd", CultureInfo.InvariantCulture, DateTimeStyles.None);
// detect client assignment
fieldResponse = AdminPaqLib.dbFieldLong(connDocos, TABLE_NAME, 7, ref companyId);
if (!customers.ContainsKey(companyId))
{
companyCode = GetCompanyCode(companyId, empresa.Ruta);
if (companyCode == null)
{
dbResponse = AdminPaqLib.dbSkip(connDocos, TABLE_NAME, INDEX, 1);
no_co++;
continue;
}
DimClientes cliente = DimClientes.GetCliente(empresa.Id, companyCode, null);
if (cliente == null)
{
dbResponse = AdminPaqLib.dbSkip(connDocos, TABLE_NAME, INDEX, 1);
no_cliente++;
continue;
}
customers.Add(companyId, cliente);
}
DimClientes customer = customers[companyId];
doco.Client = customer;
}
OrganizeDoco(doco, log);
valido++;
dbResponse = AdminPaqLib.dbSkip(connDocos, TABLE_NAME, INDEX, 1);
}
log.WriteEntry("Data collection summary: cancelled=" + cancelados + ";returns=" + devueltos + ";other=" + otros + ";not printed=" + no_impresos + ";no_co=" + no_co + ";no_cliente=" + no_cliente + ";valid=" + valido);
AdminPaqLib.dbLogOut(connDocos);
}
