public IList <AccountTypeRelation> Select(AccountTypeRelation data)
{
IList <AccountTypeRelation> datos = new List <AccountTypeRelation>();
datos = GetHsql(data).List <AccountTypeRelation>();
if (!Factory.IsTransactional)
{
Factory.Commit();
}
return(datos);
}
public override IQuery GetHsql(Object data)
{
StringBuilder sql = new StringBuilder("select a from AccountTypeRelation a where ");
AccountTypeRelation accounttyperelation = (AccountTypeRelation)data;
if (accounttyperelation != null)
{
Parms = new List <Object[]>();
if (accounttyperelation.RowID != 0)
{
sql.Append(" a.RowID = :id and ");
Parms.Add(new Object[] { "id", accounttyperelation.RowID });
}
if (accounttyperelation.Account != null && accounttyperelation.Account.AccountID != 0)
{
sql.Append(" a.Account.AccountID = :id1 and ");
Parms.Add(new Object[] { "id1", accounttyperelation.Account.AccountID });
}
if (accounttyperelation.AccountType != null && accounttyperelation.AccountType.AccountTypeID != 0)
{
sql.Append(" a.AccountType.AccountTypeID = :id2 and ");
Parms.Add(new Object[] { "id2", accounttyperelation.AccountType.AccountTypeID });
}
if (!String.IsNullOrEmpty(accounttyperelation.ErpCode))
{
sql.Append(" a.ErpCode = :nom and ");
Parms.Add(new Object[] { "nom", accounttyperelation.ErpCode });
}
if (accounttyperelation.Status != null && accounttyperelation.Status.StatusID != 0)
{
sql.Append(" a.Status.StatusID = :id3 and ");
Parms.Add(new Object[] { "id3", accounttyperelation.Status.StatusID });
}
}
sql = new StringBuilder(sql.ToString());
sql.Append(" 1=1 order by a.RowID asc ");
IQuery query = Factory.Session.CreateQuery(sql.ToString());
SetParameters(query);
return(query);
}
private void ProcessErpAccounts(IList <Account> list)
{
if (list == null)
{
return;
}
DaoFactory Factory = new DaoFactory();
Account qAcc = null;
AccountAddress curLine;
AccountTypeRelation curAccType;
foreach (Account e in list)
{
try
{
qAcc = new Account();
qAcc.Company = e.Company;
qAcc.AccountCode = e.AccountCode;
qAcc.BaseType = e.BaseType;
//Evalua si la entidad ya existe
IList <Account> exList = Factory.DaoAccount().Select(qAcc);
e.ModDate = DateTime.Now;
e.ModifiedBy = WmsSetupValues.SystemUser;
//Si existe
if (exList.Count == 0)
{
e.CreationDate = DateTime.Now;
e.CreatedBy = WmsSetupValues.SystemUser;
Factory.DaoAccount().Save(e);
}
else
{
e.AccountID = exList.First().AccountID;
e.CreatedBy = exList.First().CreatedBy;
e.CreationDate = exList.First().CreationDate;
//Preservar
e.UserDefine1 = exList.First().UserDefine1; //Shipping Mails
//Evaluar los Account Types
int i = 0;
foreach (AccountTypeRelation line in e.AccountTypes)
{
curAccType = new AccountTypeRelation();
curAccType.Account = e;
curAccType.ErpCode = line.ErpCode;
curAccType.AccountType = line.AccountType;
IList <AccountTypeRelation> listLines = Factory.DaoAccountTypeRelation().Select(curAccType);
if (listLines.Count > 0)
{
e.AccountTypes[i].ModDate = DateTime.Now;
e.AccountTypes[i].ModifiedBy = WmsSetupValues.SystemUser;
e.AccountTypes[i].CreationDate = listLines.First().CreationDate;
e.AccountTypes[i].CreatedBy = listLines.First().CreatedBy;
e.AccountTypes[i].RowID = listLines.First().RowID;
}
else
{
e.AccountTypes[i].CreationDate = DateTime.Now;
e.AccountTypes[i].CreatedBy = WmsSetupValues.SystemUser;
}
i++;
}
//Evaluar los document Lines
i = 0;
foreach (AccountAddress line in e.AccountAddresses)
{
curLine = new AccountAddress();
curLine.Account = e;
curLine.ErpCode = line.ErpCode;
IList <AccountAddress> listLines = Factory.DaoAccountAddress().Select(curLine);
if (listLines.Count > 0)
{
e.AccountAddresses[i].ModDate = DateTime.Now;
e.AccountAddresses[i].ModifiedBy = WmsSetupValues.SystemUser;
e.AccountAddresses[i].AddressID = listLines.First().AddressID;
e.AccountAddresses[i].CreationDate = listLines.First().CreationDate;
e.AccountAddresses[i].CreatedBy = listLines.First().CreatedBy;
}
else
{
e.AccountAddresses[i].CreationDate = DateTime.Now;
e.AccountAddresses[i].CreatedBy = WmsSetupValues.SystemUser;
}
i++;
}
Factory.DaoAccount().Update(e);
}
}
catch (Exception ex)
{
Factory.Rollback();
ExceptionMngr.WriteEvent("ProcessErpAccounts:" + qAcc.Name, ListValues.EventType.Fatal, ex, null, ListValues.ErrorCategory.Business);
////throw;
}
}
}
public AccountTypeRelation SelectById(AccountTypeRelation data)
{
return((AccountTypeRelation)base.SelectById(data));
}
public Boolean Update(AccountTypeRelation data)
{
return(base.Update(data));
}
public Boolean Delete(AccountTypeRelation data)
{
return(base.Delete(data));
}
public void DeleteAccountTypeRelation(AccountTypeRelation data)
{
try {
SetService(); SerClient.DeleteAccountTypeRelation(data); }
finally
{
SerClient.Close();
if (SerClient.State == CommunicationState.Faulted)
SerClient.Abort();
}
}
public AccountTypeRelation Save(AccountTypeRelation data)
{
return((AccountTypeRelation)base.Save(data));
}
public AccountTypeRelation SaveAccountTypeRelation(AccountTypeRelation data)
{
try {
SetService(); return SerClient.SaveAccountTypeRelation(data); }
finally
{
SerClient.Close();
if (SerClient.State == CommunicationState.Faulted)
SerClient.Abort();
}
}
private IList <Account> GetCustomers(string sWhere)
{
IList <Account> list = new List <Account>();
Account tmpData;
AccountTypeRelation tmpAccTypeRel;
try
{
//Lamar los documents que necesita del Erp usando econnect
//TODO: Revisar obtener solo lo modificado last X days
ds = DynamicsGP_ec.GetDataSet(DynamicsGP_ec.RetreiveData("Customer", false, 2, 0, sWhere, true));
if (ds.Tables.Count == 0)
{
return(null);
}
//Company company = WType.GetDefaultCompany();
AccountType accType = WType.GetAccountType(new AccountType {
AccountTypeID = AccntType.Customer
});
Status status = WType.GetStatus(new Status {
StatusID = EntityStatus.Active
});
//En el dataset, Tables: 1 - CustomerHeader, 2 - CustomerAddress
foreach (DataRow dr in ds.Tables[1].Rows)
{
//Map Properties
tmpData = new Account();
try
{
tmpData.AccountCode = dr["CUSTNMBR"].ToString();
if (string.IsNullOrEmpty(tmpData.AccountCode))
{
continue;
}
tmpData.Company = CurCompany;
tmpData.ContactPerson = dr["CNTCPRSN"].ToString();
tmpData.Name = dr["CUSTNAME"].ToString();
tmpData.Phone = dr["PHONE1"].ToString();
tmpData.UserDefine1 = dr["USERDEF1"].ToString();
tmpData.UserDefine2 = dr["USERDEF2"].ToString();
//Account Type
tmpAccTypeRel = new AccountTypeRelation();
tmpAccTypeRel.Account = tmpData;
tmpAccTypeRel.AccountType = accType;
tmpAccTypeRel.ErpCode = dr["CUSTNMBR"].ToString();
tmpAccTypeRel.Status = status;
tmpAccTypeRel.CreationDate = DateTime.Now;
tmpAccTypeRel.CreatedBy = WmsSetupValues.SystemUser;
tmpData.AccountTypes = new AccountTypeRelation[] { tmpAccTypeRel };
tmpData.IsFromErp = true;
tmpData.BaseType = accType;
//Asignacion de Lines
tmpData.AccountAddresses = GetCustomerAddress(tmpData,
ds.Tables[2].Select("CUSTNMBR='" + dr["CUSTNMBR"].ToString() + "'"));
list.Add(tmpData);
}
catch (Exception ex) {
ExceptionMngr.WriteEvent("GetCustomers:" + tmpData.AccountCode + "-" +
tmpData.Name, ListValues.EventType.Error, ex, null, ListValues.ErrorCategory.ErpConnection);
}
}
//retornar la lista
return(list);
}
catch (Exception ex)
{
ExceptionMngr.WriteEvent("GetCustomers", ListValues.EventType.Error, ex, null, ListValues.ErrorCategory.ErpConnection);
//throw;
return(null);
}
}
private IList <Account> GetCustomers(string sWhere)
{
IList <Account> list = new List <Account>();
Account tmpData;
AccountTypeRelation tmpAccTypeRel;
try
{
Command.Connection = new SqlConnection(CurCompany.ErpConnection.CnnString);
// CAA ??? CUST ???
Query = "select DISTINCT t.f200_rowid, t.f200_id, t.f200_nit, t.f200_razon_social, c.f015_contacto " +
" from dbo.t200_mm_terceros t JOIN t201_mm_clientes cli ON t.f200_rowid = cli.f201_rowid_tercero AND cli.f201_id_cia = " + CurCompany.ErpCode +
" LEFT OUTER JOIN t015_mm_contactos c ON t.f200_rowid_contacto = c.f015_rowid " +
" WHERE t.f200_ind_cliente = 1 AND t.f200_id_cia=" + CurCompany.ErpCode + " AND c.f015_id_cia = " + CurCompany.ErpCode;
ds = ReturnDataSet(Query, sWhere, "f200_ind_cliente", Command.Connection);
if (ds == null || ds.Tables.Count == 0)
{
return(null);
}
//Company company = WType.GetDefaultCompany();
AccountType accType = WType.GetAccountType(new AccountType {
AccountTypeID = AccntType.Customer
});
Status status = WType.GetStatus(new Status {
StatusID = EntityStatus.Active
});
foreach (DataRow dr in ds.Tables[0].Rows)
{
//Map Properties
tmpData = new Account();
tmpData.AccountCode = dr["f200_id"].ToString();
if (string.IsNullOrEmpty(tmpData.AccountCode))
{
continue;
}
tmpData.Company = CurCompany;
tmpData.ContactPerson = dr["f015_contacto"].ToString();
tmpData.Name = dr["f200_razon_social"].ToString();
//tmpData.Phone = dr["PHONE1"].ToString();
tmpData.UserDefine1 = dr["f200_nit"].ToString();
//tmpData.UserDefine2 = dr["USERDEF2"].ToString();
//Account Type
tmpAccTypeRel = new AccountTypeRelation();
tmpAccTypeRel.Account = tmpData;
tmpAccTypeRel.AccountType = accType;
tmpAccTypeRel.ErpCode = dr["f200_rowid"].ToString();
tmpAccTypeRel.Status = status;
tmpAccTypeRel.CreationDate = DateTime.Now;
tmpAccTypeRel.CreatedBy = WmsSetupValues.SystemUser;
tmpData.AccountTypes = new AccountTypeRelation[] { tmpAccTypeRel };
tmpData.IsFromErp = true;
tmpData.BaseType = accType;
//Asignacion de Lines
tmpData.AccountAddresses = GetCustomerAddress(tmpData, dr["f200_rowid"].ToString());
list.Add(tmpData);
}
//retornar la lista
return(list);
}
catch (Exception ex)
{
ExceptionMngr.WriteEvent("GetCustomers", ListValues.EventType.Error, ex, null, ListValues.ErrorCategory.ErpConnection);
//throw;
return(null);
}
}
private IList <Account> GetVendors(string sWhere)
{
IList <Account> list = new List <Account>();
Account tmpData;
AccountTypeRelation tmpAccTypeRel;
try
{
Command.Connection = new SqlConnection(CurCompany.ErpConnection.CnnString);
Query = " select v.VEND_CODE, v.NAME, v.CONTCODE, IsNull(a.first_name+' '+a.last_name,a.name) as CONTNAME " +
" from VENDORS v LEFT OUTER JOIN ADDRESS a ON v.CONTCODE = a.ADDR_CODE WHERE v.ACTIVE ='T' ";
ds = ReturnDataSet(Query, sWhere, "VENDORS", Command.Connection);
if (ds == null || ds.Tables.Count == 0)
{
return(null);
}
//Company company = WType.GetDefaultCompany();
AccountType accType = WType.GetAccountType(new AccountType {
AccountTypeID = AccntType.Vendor
});
Status status = WType.GetStatus(new Status {
StatusID = EntityStatus.Active
});
foreach (DataRow dr in ds.Tables[0].Rows)
{
//Map Properties
tmpData = new Account();
tmpData.AccountCode = dr["VEND_CODE"].ToString();
if (string.IsNullOrEmpty(tmpData.AccountCode))
{
continue;
}
tmpData.Company = CurCompany;
tmpData.ContactPerson = dr["CONTNAME"].ToString();
tmpData.Name = dr["NAME"].ToString();
// tmpData.Phone = dr["PHNUMBR1"].ToString();
// tmpData.UserDefine1 = dr["f200_nit"].ToString();
// tmpData.UserDefine2 = dr["USERDEF2"].ToString();
//Account Type
tmpAccTypeRel = new AccountTypeRelation();
tmpAccTypeRel.Account = tmpData;
tmpAccTypeRel.AccountType = accType;
tmpAccTypeRel.ErpCode = dr["VEND_CODE"].ToString();
tmpAccTypeRel.Status = status;
tmpAccTypeRel.CreationDate = DateTime.Now;
tmpAccTypeRel.CreatedBy = WmsSetupValues.SystemUser;
tmpData.AccountTypes = new AccountTypeRelation[] { tmpAccTypeRel };
tmpData.IsFromErp = true;
tmpData.BaseType = accType;
//Asignacion de Lines.... Datos del contacto del vendor (proveedor)
// caa ??? info de ADDRESS ?
// tmpData.AccountAddresses = GetVendorAddress(tmpData, dr["f200_rowid"].ToString());
list.Add(tmpData);
}
//retornar la lista
return(list);
}
catch (Exception ex)
{
ExceptionMngr.WriteEvent("GetVendors", ListValues.EventType.Error, ex, null, ListValues.ErrorCategory.ErpConnection);
//throw;
return(null);
}
}
private IList <Account> GetVendors(string sWhere)
{
IList <Account> list = new List <Account>();
Account tmpData;
AccountTypeRelation tmpAccTypeRel;
try
{
Command.Connection = new SqlConnection(CurCompany.ErpConnection.CnnString);
Query = GetErpQuery("VENDORS");
ds = ReturnDataSet(Query, sWhere, "VENDORS", Command.Connection);
if (ds == null || ds.Tables.Count == 0)
{
return(null);
}
//Company company = WType.GetDefaultCompany();
AccountType accType = WType.GetAccountType(new AccountType {
AccountTypeID = AccntType.Vendor
});
Status status = WType.GetStatus(new Status {
StatusID = EntityStatus.Active
});
foreach (DataRow dr in ds.Tables[0].Rows)
{
//Map Properties
tmpData = new Account();
try
{
tmpData.AccountCode = dr["f200_id"].ToString();
if (string.IsNullOrEmpty(tmpData.AccountCode))
{
continue;
}
tmpData.Company = CurCompany;
tmpData.ContactPerson = dr["f015_contacto"].ToString();
tmpData.Name = dr["f200_razon_social"].ToString();
//tmpData.Phone = dr["PHNUMBR1"].ToString();
tmpData.UserDefine1 = dr["f200_nit"].ToString();
tmpData.UserDefine2 = dr["f202_id_sucursal"].ToString();
//Account Type
tmpAccTypeRel = new AccountTypeRelation();
tmpAccTypeRel.Account = tmpData;
tmpAccTypeRel.AccountType = accType;
tmpAccTypeRel.ErpCode = dr["f200_rowid"].ToString();
tmpAccTypeRel.Status = status;
tmpAccTypeRel.CreationDate = DateTime.Now;
tmpAccTypeRel.CreatedBy = WmsSetupValues.SystemUser;
tmpData.AccountTypes = new AccountTypeRelation[] { tmpAccTypeRel };
tmpData.IsFromErp = true;
tmpData.BaseType = accType;
//Asignacion de Lines.... Datos del contacto del vendor (proveedor)
tmpData.AccountAddresses = GetVendorAddress(tmpData, dr["f200_rowid"].ToString());
list.Add(tmpData);
}
catch (Exception ex)
{
ExceptionMngr.WriteEvent("GetVendors:" + tmpData.AccountCode + "-" +
tmpData.Name, ListValues.EventType.Error, ex, null, ListValues.ErrorCategory.ErpConnection);
}
}
//retornar la lista
return(list);
}
catch (Exception ex)
{
ExceptionMngr.WriteEvent("GetVendors", ListValues.EventType.Error, ex, null, ListValues.ErrorCategory.ErpConnection);
//throw;
return(null);
}
}
