private List <EconomicActivity> GetCasheEconomicActivities()
{
List <EconomicActivity> doaList = new List <EconomicActivity>();
const string sqlText =
"SELECT id,name,deleted,parent_id FROM EconomicActivities WHERE deleted = 0";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand selectAll = new OpenCbsCommand(sqlText, connection))
{
using (OpenCbsReader reader = selectAll.ExecuteReader())
{
while (reader.Read())
{
EconomicActivity domain = new EconomicActivity
{
Id = reader.GetInt("id"),
Name = reader.GetString("name"),
ParentId = reader.GetNullInt("parent_id")
};
doaList.Add(domain);
}
}
}
foreach (var economicActivity in doaList)
{
economicActivity.Parent = doaList.FirstOrDefault(val => val.Id == economicActivity.ParentId);
economicActivity.Childrens = doaList.Where(val => economicActivity.Id == val.ParentId).ToList();
}
return(doaList);
}
public Dictionary <int, List <int> > SelectBranchRel()
{
const string q = @"SELECT user_id, branch_id
FROM dbo.UsersBranches
ORDER BY user_id";
Dictionary <int, List <int> > retval = new Dictionary <int, List <int> >();
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
using (OpenCbsReader r = c.ExecuteReader())
{
if (r.Empty)
{
return(retval);
}
while (r.Read())
{
int userId = r.GetInt("user_id");
if (!retval.ContainsKey(userId))
{
retval.Add(userId, new List <int>());
}
retval[userId].Add(r.GetInt("branch_id"));
}
}
return(retval);
}
public string GetBranchCodeByClientId(int clientId)
{
const string q = @"SELECT Branches.code FROM Tiers
INNER JOIN Branches ON Branches.id = Tiers.branch_id
WHERE Tiers.id = @id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@id", clientId);
string code = string.Empty;
using (OpenCbsReader r = c.ExecuteReader())
{
if (r.Empty)
{
return(null);
}
if (r.Read())
{
code = r.GetString("code");
}
}
return(code);
}
}
public Teller SelectVault(int branchId)
{
var teller = new Teller();
const string q = @"SELECT id
, name
, [desc]
, account_id
, deleted
, branch_id
, currency_id
FROM dbo.Tellers
WHERE branch_id = @branch_id AND deleted = 0 AND user_id = 0";
using (var conn = GetConnection())
using (var c = new OpenCbsCommand(q, conn))
{
c.AddParam("@branch_id", branchId);
using (OpenCbsReader r = c.ExecuteReader())
{
r.Read();
if (r.Empty)
{
return(null);
}
teller.Id = r.GetInt("id");
teller.Name = r.GetString("name");
teller.Description = r.GetString("desc");
teller.Deleted = r.GetBool("deleted");
teller.Account = accountManager.Select(r.GetInt("account_id"));
teller.Branch = branchManager.Select(r.GetInt("branch_id"));
teller.Currency = currencyManager.SelectCurrencyById(r.GetInt("currency_id"));
}
}
return(teller);
}
public List <District> SelectDistrictsByProvinceId(int pProvinceId)
{
List <District> districts = new List <District>();
const string q = "SELECT Districts.id, Districts.name, Districts.province_id, " +
"Provinces.id AS province_id, Provinces.name AS province_name " +
"FROM Districts INNER JOIN " +
"Provinces ON Districts.province_id = Provinces.id " +
"WHERE Provinces.id= @id AND Districts.deleted = 0 ORDER BY Districts.name";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@id", pProvinceId);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r != null)
{
while (r.Read())
{
District district = new District();
district.Province = new Province();
district.Id = r.GetInt("id");
district.Name = r.GetString("name");
district.Province.Id = r.GetInt("province_id");
district.Province.Name = r.GetString("province_name");
districts.Add(district);
}
}
}
}
return(districts);
}
public List <City> SelectCityByDistrictId(int pDistrictId)
{
List <City> cities = new List <City>();
const string q = "SELECT name, id FROM City WHERE district_id = @id and deleted = 0 ORDER BY name";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@id", pDistrictId);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r != null)
{
while (r.Read())
{
City city = new City
{
Name = r.GetString("name"),
Id = r.GetInt("id"),
DistrictId = pDistrictId
};
cities.Add(city);
}
}
}
}
return(cities);
}
public PaymentMethod SelectPaymentMethodByName(string name)
{
const string q = @"SELECT pm.[id]
,[name]
,[description]
,[pending]
,0 AS account_id
FROM [PaymentMethods] pm
WHERE [name] = @name";
PaymentMethod pm = new PaymentMethod();
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@name", name);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r != null && !r.Empty)
{
r.Read();
pm = GetPaymentMethodFromReader(r);
}
}
}
return(pm);
}
public bool FieldValuesExistForFieldId(int fieldId)
{
string sqlText = @"SELECT COUNT(*)
AS [number]
FROM dbo.AdvancedFieldsValues
WHERE field_id = @field_id";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand selectCmd = new OpenCbsCommand(sqlText, conn))
{
selectCmd.AddParam("@field_id", fieldId);
using (OpenCbsReader reader = selectCmd.ExecuteReader())
{
if (reader == null || reader.Empty)
{
return(false);
}
reader.Read();
if (reader.GetInt("number") > 0)
{
return(true);
}
}
}
return(false);
}
/// <summary>
/// Returns requested picture.
/// </summary>
/// <param name="pGroup">Picture group</param>
/// <param name="pId">Picture Id</param>
/// <param name="pSubID">Picture sub Id</param>
/// <param name="pThumbnail">Do you want the thumbnail or the actual picture?</param>
/// <returns>Found picture informations</returns>
public PictureInfo GetPicture(string pGroup, int pId, int pSubID, bool pThumbnail)
{
string sql = pThumbnail
? "SELECT thumbnail,name FROM Pictures WHERE [group]=@group AND id=@id AND subid=@subid"
: "SELECT picture,name FROM Pictures WHERE [group]=@group AND id=@id AND subid=@subid";
using (OpenCbsCommand c = new OpenCbsCommand(sql, AttachmentsConnection))
{
c.AddParam("@group", pGroup);
c.AddParam("@id", pId);
c.AddParam("@subid", pSubID);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r == null || r.Empty)
{
return(null);
}
r.Read();
PictureInfo pi = new PictureInfo
{
Binary = r.GetBytes(0),
Name = r.GetString(1),
Id = pId,
SubId = pSubID,
Group = pGroup
};
return(pi);
}
}
}
public PaymentMethod SelectPaymentMethodById(int paymentMethodId)
{
InitCache();
return(_cache.Find(pm2 => pm2.Id == paymentMethodId));
string q = @"SELECT pm.[id]
,[name]
,[description]
,[pending]
, 0 AS account_id
FROM [dbo].[PaymentMethods] pm
WHERE pm.id = @id";
PaymentMethod pm = new PaymentMethod();
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@id", paymentMethodId);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r != null && !r.Empty)
{
r.Read();
pm = GetPaymentMethodFromReader(r);
}
}
}
return(pm);
}
public int GetNumberProject(string pQuery)
{
string SELECT_FROM_PROJET_ = @" SELECT DISTINCT pro.id,pro.code,pro.name as name_project,pro.aim,pers.first_name,
pers.last_name,tie.client_type_code,tie.id as tiers_id,corp.name as companyName
FROM (Projects as pro
INNER JOIN Tiers tie on pro.tiers_id=tie.id )
LEFT JOIN Corporates corp on corp.id=tie.id
LEFT JOIN Persons pers on pers.id=tie.id ) maTable" ;
string CloseWhere = @" WHERE ( companyName LIKE @companyName OR code LIKE @code OR name_project LIKE @nameProject OR aim LIKE @aim OR last_name LIKE @lastName OR first_name LIKE @firtName )) maTable ";
QueryEntity q = new QueryEntity(pQuery, SELECT_FROM_PROJET_, CloseWhere);
string pSqlText = q.ConstructSQLEntityNumberProxy();
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand select = new OpenCbsCommand(pSqlText, conn))
{
foreach (var item in q.DynamiqParameters())
{
select.AddParam(item.Key, string.Format("%{0}%", item.Value));
}
using (OpenCbsReader reader = select.ExecuteReader())
{
reader.Read();
return(reader.GetInt(0));
}
}
}
public Project SelectProjectByContractId(int pContractId)
{
string sqlText = @"SELECT Projects.*
FROM Projects
INNER JOIN Contracts ON Projects.id = Contracts.project_id
WHERE Contracts.id = @contractId";
Project project = null;
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
select.AddParam("@contractId", pContractId);
using (OpenCbsReader reader = select.ExecuteReader())
{
if (!reader.Empty)
{
reader.Read();
project = GetProject(reader);
}
}
}
if (_clientManager != null)
{
project.Client = _clientManager.SelectClientByProjectId(project.Id);
}
return(project);
}
public List <Project> SelectProjectsByClientId(int pClientId)
{
var list = new List <Project>();
var listIds = new List <int>();
const string query = "SELECT id FROM Projects WHERE tiers_id = @tiersId";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand select = new OpenCbsCommand(query, conn))
{
select.AddParam("@tiersId", pClientId);
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader != null)
{
if (!reader.Empty)
{
while (reader.Read())
{
listIds.Add(reader.GetInt("id"));
}
}
}
}
foreach (int id in listIds)
{
list.Add(SelectProject(id));
}
}
return(list);
}
private IEnumerable <FollowUp> SelectFollowUps(int pProjectId)
{
List <FollowUp> list = new List <FollowUp>();
const string sqlText = "SELECT * FROM FollowUp WHERE project_id = @pId";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
{
select.AddParam("@pId", pProjectId);
using (OpenCbsReader reader = select.ExecuteReader())
{
if (reader == null || reader.Empty)
{
return(new List <FollowUp>());
}
while (reader.Read())
{
FollowUp followUp = new FollowUp();
followUp.Id = reader.GetInt("id");
followUp.Year = reader.GetInt("year");
followUp.Jobs1 = reader.GetInt("Jobs1");
followUp.Jobs2 = reader.GetInt("Jobs2");
followUp.CA = reader.GetMoney("CA");
followUp.PersonalSituation = reader.GetString("PersonalSituation");
followUp.Activity = reader.GetString("activity");
followUp.Comment = reader.GetString("comment");
list.Add(followUp);
}
return(list);
}
}
}
public List <PaymentMethod> SelectPaymentMethodsForClosure()
{
string q = @"SELECT pm.[id]
,[name]
,[description]
,[pending]
,[account_id]
FROM [PaymentMethods] pm
INNER JOIN LinkBranchesPaymentMethods lbpm ON lbpm.payment_method_id = pm.id
ORDER BY pm.[id]";
List <PaymentMethod> paymentMethods = new List <PaymentMethod>();
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
using (OpenCbsReader r = c.ExecuteReader())
{
if (r != null && !r.Empty)
{
while (r.Read())
{
paymentMethods.Add(GetPaymentMethodFromReader(r));
}
}
}
return(paymentMethods);
}
public List <string> SelectAllEntites()
{
string sqlText = @"SELECT name
FROM AdvancedFieldsEntities";
List <string> entities = new List <string>();
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, conn))
{
using (OpenCbsReader reader = cmd.ExecuteReader())
{
if (reader.Empty)
{
return(new List <string>());
}
while (reader.Read())
{
entities.Add(reader.GetString("name"));
}
}
}
return(entities);
}
public List <PaymentMethod> GetPaymentMethodsWithoutBranch()
{
string q = @"SELECT pm.[id]
,[name]
,[description]
,[pending]
,0 AS [account_id]
FROM [PaymentMethods] pm
ORDER BY pm.[id]";
List <PaymentMethod> paymentMethods = new List <PaymentMethod>();
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
using (OpenCbsReader r = c.ExecuteReader())
{
if (r != null && !r.Empty)
{
while (r.Read())
{
PaymentMethod paymentMethod = new PaymentMethod
{
Id = r.GetInt("id"),
Name = r.GetString("name"),
Description = r.GetString("description"),
IsPending = r.GetBool("pending"),
Account = _accountManager.Select(r.GetInt("account_id"))
};
paymentMethods.Add(paymentMethod);
}
}
}
return(paymentMethods);
}
public District SelectDistrictByName(string name)
{
District district = null;
const string q = "SELECT Districts.id, Districts.name, Districts.province_id, " +
"Provinces.id AS province_id, Provinces.name AS province_name " +
"FROM Districts INNER JOIN " +
"Provinces ON Districts.province_id = Provinces.id " +
"WHERE Districts.name= @name";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@name", name);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r != null)
{
if (!r.Empty)
{
r.Read();
district = new District();
district.Province = new Province();
district.Id = r.GetInt("id");
district.Name = r.GetString("name");
district.Province.Id = r.GetInt("province_id");
district.Province.Name = r.GetString("province_name");
}
}
}
}
return(district);
}
/// <summary>
/// Select a Role by its database id with an Sqltransaction contexte
/// </summary>
/// <param name="pRoleId"></param>
/// <param name="pIncludeDeletedRole"></param>
/// <param name="pSqlTransac"></param>
/// <returns>selected Role or null otherwise</returns>
public Role SelectRole(int pRoleId, bool pIncludeDeletedRole, SqlTransaction pSqlTransac)
{
string q = @"SELECT [Roles].[id], [code], [deleted], [description]
FROM [Roles] WHERE [id] = @id ";
if (!pIncludeDeletedRole)
{
q += " AND [deleted] = 0";
}
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@id", pRoleId);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r != null)
{
if (!r.Empty)
{
r.Read();
Role role = GetRole(r);
return(role);
}
}
}
return(null);
}
}
public List <Province> SelectAllProvinces()
{
List <Province> provinces = new List <Province>();
const string q = "SELECT id,name FROM Provinces ORDER BY name";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
using (OpenCbsReader r = c.ExecuteReader())
{
if (r != null)
{
while (r.Read())
{
Province province = new Province
{
Id = r.GetInt("id"),
Name = r.GetString("name")
};
provinces.Add(province);
}
}
}
return(provinces);
}
public int SelectUserForThisRole(string pRoleName)
{
string q = @"SELECT TOP 1 [user_id]
FROM UserRole
INNER JOIN Roles ON UserRole.role_id = Roles.id
INNER JOIN Users ON Users.id = UserRole.[user_id]
WHERE Roles.code = @roleCode AND Users.deleted = 0";
int foundId = 0;
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@roleCode", pRoleName);
using (OpenCbsReader r = c.ExecuteReader())
{
if (r != null)
{
if (!r.Empty)
{
r.Read();
foundId = r.GetInt("user_id");
}
}
}
return(foundId);
}
}
}
public List <District> GetDistricts()
{
List <Province> provinces = GetProvinces();
List <District> districts = new List <District>();
const string q = "SELECT [id], [name], [province_id] FROM [Districts] WHERE [deleted]=0 ORDER BY name";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
using (OpenCbsReader reader = c.ExecuteReader())
{
if (reader != null)
{
while (reader.Read())
{
District district = new District();
district.Id = reader.GetInt("id");
district.Name = reader.GetString("name");
int province_id = reader.GetInt("province_id");
foreach (Province p in provinces)
{
if (p.Id == province_id)
{
district.Province = p;
}
}
districts.Add(district);
}
}
}
return(districts);
}
/// <summary>
/// Select a role by its name
/// </summary>
/// <param name="pRoleName"></param>
/// <param name="pIncludeDeleted"></param>
/// <returns>selected role or null otherwise</returns>
public Role SelectRole(string pRoleName, bool pIncludeDeleted)
{
string q = @"SELECT [id], [code], [deleted], [description]
FROM [Roles] WHERE [code] = @name ";
q += pIncludeDeleted ? "" : "AND [deleted] = 0";
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
c.AddParam("@name", pRoleName);
Role role;
using (OpenCbsReader r = c.ExecuteReader())
{
if (r == null || r.Empty)
{
return(null);
}
r.Read();
role = GetRole(r);
}
role.SetMenuItems(GetAllowedMenuList(role.Id));
role.SetActionItems(GetAllowedActionList(role.Id));
return(role);
}
}
}
public Branch SelectBranchByName(string name)
{
string query = @"SELECT id
, name
, deleted
, code
, address
, description
FROM dbo.Branches
WHERE name LIKE '%{0}%'";
query = string.Format(query, name);
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand cmd = new OpenCbsCommand(query, conn))
using (OpenCbsReader r = cmd.ExecuteReader())
{
if (r.Empty)
{
return(null);
}
if (!r.Read())
{
return(null);
}
return(new Branch
{
Id = r.GetInt("id"),
Code = r.GetString("code"),
Name = r.GetString("name"),
Deleted = r.GetBool("deleted"),
Description = r.GetString("description")
});
}
}
public Dictionary <int, int> SelectUserToRole()
{
const string q = @"SELECT u.id AS user_id, r.id AS role_id
FROM dbo.Users AS u
LEFT JOIN dbo.Roles AS r ON r.code = u.role_code";
Dictionary <int, int> userToRole = new Dictionary <int, int>();
using (SqlConnection conn = GetConnection())
{
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
using (OpenCbsReader r = c.ExecuteReader())
{
if (r.Empty)
{
return(userToRole);
}
while (r.Read())
{
int userId = r.GetInt("user_id");
int roleId = r.GetInt("role_id");
userToRole.Add(userId, roleId);
}
}
}
}
return(userToRole);
}
public List <MenuObject> GetMenuList(OSecurityObjectTypes[] securityObjectTypes)
{
string q = @"SELECT [id], [component_name] FROM [MenuItems]";
if (securityObjectTypes.Any())
{
string[] types = securityObjectTypes.Select(t => Convert.ToString((int)t)).ToArray();
string condition = string.Format(" WHERE [type] in ({0})", string.Join(",", types));
q += condition;
}
List <MenuObject> menus = new List <MenuObject>();
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
{
using (OpenCbsReader r = c.ExecuteReader())
{
if (r != null && !r.Empty)
{
while (r.Read())
{
menus.Add(new MenuObject
{
Id = r.GetInt("id"),
Name = r.GetString("component_name").Trim(),
NotSavedInDBYet = false
});
}
}
}
}
return(menus);
}
private string SelectFieldNamesForEntity(int entityId)
{
string sqlText = @"SELECT name
FROM dbo.AdvancedFields
WHERE entity_id = @entity_id ";
CommaDelimitedStringCollection commaStr = new CommaDelimitedStringCollection();
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, conn))
{
cmd.AddParam("@entity_id", entityId);
using (OpenCbsReader reader = cmd.ExecuteReader())
{
if (reader.Empty)
{
return(string.Empty);
}
while (reader.Read())
{
commaStr.Add(reader.GetString("name"));
}
}
}
return(commaStr.ToString());
}
public Dictionary <int, List <int> > SelectSubordinateRel()
{
const string q = @"SELECT user_id, subordinate_id
FROM dbo.UsersSubordinates
ORDER BY user_id";
Dictionary <int, List <int> > retval = new Dictionary <int, List <int> >();
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
using (OpenCbsReader r = c.ExecuteReader())
{
if (r.Empty)
{
return(retval);
}
int currentId = 0;
while (r.Read())
{
int userId = r.GetInt("user_id");
if (currentId != userId)
{
currentId = userId;
retval.Add(currentId, new List <int>());
}
retval[currentId].Add(r.GetInt("subordinate_id"));
}
}
return(retval);
}
public bool CustomizableFieldsExistFor(OCustomizableFieldEntities entity)
{
string sqlText = @"SELECT COUNT(*) AS [number]
FROM dbo.AdvancedFields
WHERE [entity_id] = @entity_id ";
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand selectCmd = new OpenCbsCommand(sqlText, conn))
{
selectCmd.AddParam("@entity_id",
(int)Enum.Parse(typeof(OCustomizableFieldEntities), entity.ToString()));
using (OpenCbsReader reader = selectCmd.ExecuteReader())
{
if (reader == null || reader.Empty)
{
return(false);
}
reader.Read();
if (reader.GetInt("number") > 0)
{
return(true);
}
}
}
return(false);
}
/// <summary>
/// This methods allows us to find all domains of application
/// </summary>
/// <returns>hierarchic collection of DomainOfApplication
/// </returns>
public List <EconomicActivity> SelectAllEconomicActivities()
{
List <EconomicActivity> doaList = new List <EconomicActivity>();
const string sqlText = "SELECT id FROM EconomicActivities WHERE parent_id IS NULL AND deleted = 0";
using (SqlConnection connection = GetConnection())
using (OpenCbsCommand selectAll = new OpenCbsCommand(sqlText, connection))
{
using (OpenCbsReader reader = selectAll.ExecuteReader())
{
while (reader.Read())
{
EconomicActivity domain = new EconomicActivity
{
Id = reader.GetInt("id")
};
doaList.Add(domain);
}
}
}
for (int i = 0; i < doaList.Count; i++)
{
doaList[i] = SelectEconomicActivity(doaList[i].Id);
}
return(doaList);
}
private static EconomicActivity GetEconomicActivity(OpenCbsReader pReader)
{
EconomicActivity doa = new EconomicActivity();
if (pReader != null)
{
if (!pReader.Empty)
{
pReader.Read();
doa.Id = pReader.GetInt("id");
doa.Name = pReader.GetString("name");
doa.Deleted = pReader.GetBool("deleted");
}
}
return doa;
}