public static Staff StaffFromUser(string username)
{
Staff staff = new Staff();
if (@username.Contains("\\"))
{
//Contains domain so take everything after the \ doubled to handle escaping
username = username.Substring(@username.LastIndexOf("\\"));
}
SqlDataReader xlReader = XLSQL.ReaderQuery("Select * from Staff where username='******'");
if (xlReader.HasRows)
{
xlReader.Read();
staff.crmID = xlReader["crmid"].ToString();
staff.relationship = ""; //Only used in collections
staff.username = xlReader.NiceString("username");
staff.name = xlReader["fullname"].ToString();
staff.initials = xlReader["initials"].ToString();
staff.department = xlReader["department"].ToString();
staff.office = xlReader["office"].ToString();
staff.grade = xlReader["grade"].ToString();
staff.emails = EmailAddress.GetEmails(staff.crmID);
staff.numbers = null;
}
return(staff);
}
public static Staff FetchStaff(string iD)
{
try
{
Staff staff = new Staff();
DataTable xlReader = XLSQL.ReturnTable("Select * from Staff where crmID='" + iD + "'");
if (xlReader.Rows.Count == 1)
{
staff.crmID = xlReader.Rows[0]["crmid"].ToString();
staff.relationship = ""; //Only used in collections
staff.username = xlReader.Rows[0]["username"].ToString();
staff.name = xlReader.Rows[0]["fullname"].ToString();
staff.initials = xlReader.Rows[0]["initials"].ToString();
staff.department = xlReader.Rows[0]["department"].ToString();
staff.office = xlReader.Rows[0]["office"].ToString();
staff.grade = xlReader.Rows[0]["grade"].ToString();
staff.emails = EmailAddress.GetEmails(staff.crmID);
staff.addresses = Address.GetAddresses(staff.crmID);
staff.salutations = Salutation.GetSalutations(staff.crmID);
staff.numbers = Number.GetNumbers(staff.crmID);
}
return(staff);
}
catch (Exception ex)
{
XLtools.LogException("XLMain-FetchStaff", ex.ToString());
return(null);
}
}
public static Client FetchClient(string ID)
{
//Get client data from Client
Client client = new Client();
SqlDataReader xlReader = XLSQL.ReaderQuery("Select * from Client where CRMID='" + ID + "'");
if (xlReader.HasRows)
{
xlReader.Read();
client.crmID = ID;
client.clientcode = xlReader.NiceString("clientcode");
client.name = xlReader.NiceString("name");
client.relationship = "";//only used in collections
client.type = xlReader.NiceString("type");
client.status = xlReader.NiceString("status");
if (client.status.ToUpper() == "ACTIVE" || client.status.ToUpper() == "NEW")
{
client.isLive = true;
}
else
{
client.isLive = false;
}
client.department = xlReader.NiceString("department");
client.office = xlReader.NiceString("office");
client.partner = Staff.GetStaff("Partner", client.crmID);
client.manager = Staff.GetStaff("Manager", client.crmID);
}
//Start building additional data
//WIP
//xlReader = XLSQL.ReaderQuery("SELECT ISNULL((SELECT ISNULL(SUM(WIPOutstanding),0) as WIPOutstanding FROM wipentries WHERE [Clientcode] = '" + clientcode + "'),0)");
//if (xlReader.HasRows)
//{
// xlReader.Read();
// wip = float.Parse(xlReader["wipoutstanding"].ToString());
//}
//Debtor
//xlReader = XLSQL.ReaderQuery("SELECT ISNULL((SELECT ISNULL(SUM(DebtTranUnpaid),0) As DRSTotal FROM [Engine_MLDB].[dbo].[view_debtors] WHERE ([Clientcode] = '" + clientcode + "')),0)");
//if (xlReader.HasRows)
//{
//xlReader.Read();
client.wip = 0;
client.debtor = 0;
//}
//Addresses
client.addresses = Address.GetAddresses(client.crmID);
//Numbers
client.numbers = Number.GetNumbers(client.crmID);
//email
client.emails = EmailAddress.GetEmails(client.crmID);
//salutations
client.salutations = Salutation.GetSalutations(client.crmID);
//parameters
client.parameters = null;
//connections
return(client);
}
public static Contact FetchContact(string iD)
{
try
{
Contact cont = new Contact();
//Get contact data from MainContact
DataTable xlReader = XLSQL.ReturnTable("Select * from Contact where CRMID='" + iD + "'");
if (xlReader.Rows.Count == 1)
{
cont.crmID = iD;
cont.firstname = xlReader.Rows[0]["first_name"].ToString();
cont.lastname = xlReader.Rows[0]["last_name"].ToString();
cont.relationship = ""; //only used in collections
cont.type = xlReader.Rows[0]["type"].ToString();
//see whether the title exists in the enum
if (Enum.IsDefined(typeof(Title), xlReader.Rows[0]["title"].ToString()))
{
cont.title = (Title)Enum.Parse(typeof(Title), xlReader.Rows[0]["title"].ToString(), true);
}
else
{
cont.title = (Title)Enum.Parse(typeof(Title), "Mr", true);
}
cont.position = xlReader.Rows[0]["position"].ToString();
}
//Organisation
cont.organisation = Organisation.GetOrganisation(cont.crmID);
//Addresses
cont.addresses = Address.GetAddresses(cont.crmID);
//Update the address block with the organisation name
if (cont.organisation != null)
{
foreach (Address add in cont.addresses)
{
add.addressBlock = cont.organisation.name + Environment.NewLine + add.addressBlock;
}
}
//Numbers
cont.numbers = Number.GetNumbers(cont.crmID);
//email
cont.emails = EmailAddress.GetEmails(cont.crmID);
//salutations
cont.salutations = Salutation.GetSalutations(cont.crmID);
//parameters
cont.parameters = null;
return(cont);
}
catch (Exception ex)
{
XLtools.LogException("XLMain-FetchContact", ex.ToString());
return(null);
}
}
public static Staff FetchStaff(string iD)
{
Staff staff = new Staff();
SqlDataReader xlReader = XLSQL.ReaderQuery("Select * from Staff where crmID='" + iD + "'");
if (xlReader.HasRows)
{
xlReader.Read();
staff.crmID = xlReader.NiceString("crmid");
staff.relationship = ""; //Only used in collections
staff.username = xlReader.NiceString("username");
staff.name = xlReader.NiceString("fullname");
staff.initials = xlReader.NiceString("initials");
staff.department = xlReader.NiceString("department");
staff.office = xlReader.NiceString("office");
staff.grade = xlReader.NiceString("grade");
staff.emails = EmailAddress.GetEmails(staff.crmID);
staff.numbers = null;
}
return(staff);
}
public static Contact FetchContact(string iD)
{
Contact cont = new Contact();
//Get contact data from MainContact
SqlDataReader xlReader = XLSQL.ReaderQuery("Select * from Contact where CRMID='" + iD + "'");
if (xlReader.HasRows)
{
xlReader.Read();
cont.crmID = iD;
cont.firstname = xlReader.NiceString("first_name");
cont.lastname = xlReader.NiceString("last_name");
cont.relationship = ""; //only used in collections
cont.type = xlReader.NiceString("type");
cont.title = (Title)Enum.Parse(typeof(Title), xlReader.NiceString("title"), true);
cont.position = xlReader.NiceString("position");
}
//Organisation
cont.organisation = Organisation.GetOrganisation(cont.crmID);
//Addresses
cont.addresses = Address.GetAddresses(cont.crmID);
//Update the address block with the organisation name
if (cont.organisation.name != "")
{
foreach (Address add in cont.addresses)
{
add.addressBlock = cont.organisation.name + Environment.NewLine + add.addressBlock;
}
}
//Numbers
cont.numbers = Number.GetNumbers(cont.crmID);
//email
cont.emails = EmailAddress.GetEmails(cont.crmID);
//salutations
cont.salutations = Salutation.GetSalutations(cont.crmID);
//parameters
cont.parameters = null;
return(cont);
}
public static Client FetchClient(string ID)
{
try
{
if (String.IsNullOrEmpty(ID))
{
return(null);
}
//Get client data from Client
Client client = new Client();
//SqlDataReader xlReader = XLSQL.ReaderQuery("Select * from Client where CRMID='" + ID + "'");
DataTable xlReader = XLSQL.ReturnTable("Select Top 1 * from Client where CRMID='" + ID + "'");
//if we get more than one result we have a problem
if (xlReader.Rows.Count == 1)
{
client.crmID = ID;
client.clientcode = xlReader.Rows[0]["clientcode"].ToString();
client.name = xlReader.Rows[0]["name"].ToString();
client.relationship = "";//only used in collections
client.type = xlReader.Rows[0]["type"].ToString();
client.status = xlReader.Rows[0]["status"].ToString();
if (client.status.ToUpper() == "ACTIVE" || client.status.ToUpper() == "NEW")
{
client.isLive = true;
}
else
{
client.isLive = false;
}
client.department = xlReader.Rows[0]["department"].ToString();
client.office = xlReader.Rows[0]["office"].ToString();
client.partner = Staff.GetStaff("Partner", client.crmID);
client.manager = Staff.GetStaff("Manager", client.crmID);
if (client.type == "Ltd" || client.type == "Plc" || client.type == "LLP" || client.type == "PartnerS" || client.type == "Charity")
{
client.IsIndividual = false;
}
else
{
client.IsIndividual = true;
}
}
client.wip = 0;
client.debtor = 0;
//}
//Addresses
client.addresses = Address.GetAddresses(client.crmID);
//Numbers
client.numbers = Number.GetNumbers(client.crmID);
//email
client.emails = EmailAddress.GetEmails(client.crmID);
//salutations
client.salutations = Salutation.GetSalutations(client.crmID);
//parameters
client.parameters = null;
//connections
return(client);
}
catch (Exception ex)
{
XLtools.LogException("XLMain-FetchClient", ex.ToString());
return(null);
}
}