public void SetPrintingDevice(PrintingDevice printingDevice)
{
ProcedureCall storePrintingDevice = new ProcedureCall("pr_storePrintingDevice", sqlConnection);
storePrintingDevice.parameters.Add(new ProcedureParam("@tenantId", SqlDbType.Int, 4, printingDevice.tenantId));
storePrintingDevice.parameters.Add(new ProcedureParam("@ipAddress", SqlDbType.VarChar, 100, printingDevice.ipAddress));
storePrintingDevice.parameters.Add(new ProcedureParam("@description", SqlDbType.VarChar, 100, printingDevice.description));
storePrintingDevice.parameters.Add(new ProcedureParam("@serialNumber", SqlDbType.VarChar, 100, printingDevice.serialNumber));
storePrintingDevice.parameters.Add(new ProcedureParam("@counter", SqlDbType.Int, 4, printingDevice.counter));
storePrintingDevice.Execute(true);
int?deviceId = storePrintingDevice.ExtractFromResultset();
if (deviceId != null)
{
List <Object> counterHistory = GetCounterHistory(deviceId.Value);
if (counterHistory.Count > 0)
{
// Verifica se o contador é repetido, considera uma diferença de 50 páginas ao comparar
DateTime today = DateTime.Now;
PageCounter lastCounter = (PageCounter)counterHistory[0];
Decimal diff = Math.Abs(printingDevice.counter - lastCounter.counter);
if ((lastCounter.date.Day != today.Day) || (diff > 50))
{
SetPageCounter(deviceId.Value, printingDevice.counter);
}
}
else
{
// Nenhum contador prévio, insere primeira ocorrência
SetPageCounter(deviceId.Value, printingDevice.counter);
}
}
}
public static Object[] Retrieve(String procedureName, SqlConnection sqlConnection, Type objectType)
{
// Convenções:
// A procedure deve retornar registros compatíveis com objectType
// objectType deve possuir campos com os atributos [ItemId] e [ItemName]
// Estes campos devem ser publicos e de instancia (não estáticos)
ProcedureCall procedureCall = new ProcedureCall(procedureName, sqlConnection);
procedureCall.parameters.Add(new ProcedureParam("@tenantId", SqlDbType.Int, 4, 4));
procedureCall.Execute(true);
List <Object> returnList = procedureCall.ExtractFromResultset(objectType);
ItemScaffolding itemScaffolding = new ItemScaffolding();
itemScaffolding.FindItemFields(objectType);
Object[] itemArray = new Object[returnList.Count + 1];
itemArray[0] = Activator.CreateInstance(objectType);
itemScaffolding.SetItemId(itemArray[0], 0);
itemScaffolding.SetItemName(itemArray[0], "");
int ndx = 1;
foreach (Object obj in returnList)
{
itemArray[ndx] = Activator.CreateInstance(objectType);
itemScaffolding.SetItemId(itemArray[ndx], itemScaffolding.GetItemId(obj));
itemScaffolding.SetItemName(itemArray[ndx], itemScaffolding.GetItemName(obj));
ndx++;
}
return(itemArray);
}
public List <Object> GetAllParams()
{
List <Object> accountingParams;
ProcedureCall retrieveAccountingParams = new ProcedureCall("pr_retrieveAccountingParams", sqlConnection);
retrieveAccountingParams.Execute(true);
accountingParams = retrieveAccountingParams.ExtractFromResultset(typeof(ApplicationParam));
return(accountingParams);
}
public int?SetTenant(Tenant tenant)
{
ProcedureCall storeTenant = new ProcedureCall("pr_storeTenant", sqlConnection);
storeTenant.parameters.Add(new ProcedureParam("@tenantId", SqlDbType.Int, 4, tenant.id));
storeTenant.parameters.Add(new ProcedureParam("name", SqlDbType.VarChar, 100, tenant.name));
storeTenant.parameters.Add(new ProcedureParam("@alias", SqlDbType.VarChar, 100, tenant.alias));
storeTenant.Execute(true);
return(storeTenant.ExtractFromResultset()); // retorna o id da empresa
}
public List <Object> GetAllTenants()
{
List <Object> tenantList;
ProcedureCall retrieveTenants = new ProcedureCall("pr_retrieveTenant", sqlConnection);
retrieveTenants.Execute(true);
tenantList = retrieveTenants.ExtractFromResultset(typeof(Tenant));
return(tenantList);
}
public List <Object> GetAllLogins()
{
List <Object> administratorLogins;
ProcedureCall retrieveLogin = new ProcedureCall("pr_retrieveAdministratorLogin", sqlConnection);
retrieveLogin.Execute(true);
administratorLogins = retrieveLogin.ExtractFromResultset(typeof(AdministratorLogin));
return(administratorLogins);
}
public List <Object> GetAllMailings(int tenantId)
{
List <Object> mailingList;
ProcedureCall retrieveMailings = new ProcedureCall("pr_retrieveMailing", sqlConnection);
retrieveMailings.parameters.Add(new ProcedureParam("@tenantId", SqlDbType.Int, 4, tenantId));
retrieveMailings.Execute(true);
mailingList = retrieveMailings.ExtractFromResultset(typeof(Mailing));
return(mailingList);
}
public List <Object> GetAllSmtpServers(int tenantId)
{
List <Object> smtpServers;
ProcedureCall retrieveSmtpServers = new ProcedureCall("pr_retrieveSmtpServer", sqlConnection);
retrieveSmtpServers.parameters.Add(new ProcedureParam("@tenantId", SqlDbType.Int, 4, tenantId));
retrieveSmtpServers.Execute(true);
smtpServers = retrieveSmtpServers.ExtractFromResultset(typeof(SmtpServer));
return(smtpServers);
}
public List <Object> GetAllTenantPreferences(int tenantId)
{
List <Object> tenantPreferences;
ProcedureCall retrieveTenantPreferences = new ProcedureCall("pr_retrieveTenantPreference", sqlConnection);
retrieveTenantPreferences.parameters.Add(new ProcedureParam("@tenantId", SqlDbType.Int, 4, tenantId));
retrieveTenantPreferences.Execute(true);
tenantPreferences = retrieveTenantPreferences.ExtractFromResultset(typeof(Preference));
return(tenantPreferences);
}
public List <Object> GetCounterHistory(int deviceId)
{
List <Object> counterHistory;
ProcedureCall retrievePageCounters = new ProcedureCall("pr_retrievePageCounter", sqlConnection);
retrievePageCounters.parameters.Add(new ProcedureParam("@deviceId", SqlDbType.Int, 4, deviceId));
retrievePageCounters.Execute(true);
counterHistory = retrievePageCounters.ExtractFromResultset(typeof(PageCounter));
return(counterHistory);
}
public List <Object> GetAllPrintingDevices(int tenantId)
{
List <Object> deviceList;
ProcedureCall retrievePrintingDevices = new ProcedureCall("pr_retrievePrintingDevice", sqlConnection);
retrievePrintingDevices.parameters.Add(new ProcedureParam("@tenantId", SqlDbType.Int, 4, tenantId));
retrievePrintingDevices.Execute(true);
deviceList = retrievePrintingDevices.ExtractFromResultset(typeof(PrintingDevice));
return(deviceList);
}
public List <Object> GetAllLogins(int tenantId)
{
List <Object> logins;
ProcedureCall retrieveLogins = new ProcedureCall("pr_retrieveLogin", sqlConnection);
retrieveLogins.parameters.Add(new ProcedureParam("@tenantId", SqlDbType.Int, 4, tenantId));
retrieveLogins.Execute(true);
logins = retrieveLogins.ExtractFromResultset(typeof(Login));
return(logins);
}
public List <Object> GetAllUsers(int tenantId)
{
List <Object> userList;
ProcedureCall retrieveUsers = new ProcedureCall("pr_retrieveUser", sqlConnection);
retrieveUsers.parameters.Add(new ProcedureParam("@tenantId", SqlDbType.Int, 4, tenantId));
retrieveUsers.Execute(true);
userList = retrieveUsers.ExtractFromResultset(typeof(User));
return(userList);
}
public List <Object> GetAllAssociates(int tenantId)
{
List <Object> associateList;
ProcedureCall retrieveAssociates = new ProcedureCall("pr_retrieveAssociates", sqlConnection);
retrieveAssociates.parameters.Add(new ProcedureParam("@tenantId", SqlDbType.Int, 4, tenantId));
retrieveAssociates.Execute(true);
associateList = retrieveAssociates.ExtractFromResultset(typeof(CostCenterAssociate));
return(associateList);
}
public int?SetUser(User user)
{
ProcedureCall storeUser = new ProcedureCall("pr_storeUser", sqlConnection);
storeUser.parameters.Add(new ProcedureParam("@userId", SqlDbType.Int, 4, user.id));
storeUser.parameters.Add(new ProcedureParam("@tenantId", SqlDbType.Int, 4, user.tenantId));
storeUser.parameters.Add(new ProcedureParam("@name", SqlDbType.VarChar, 100, user.name));
storeUser.parameters.Add(new ProcedureParam("@alias", SqlDbType.VarChar, 100, user.alias));
storeUser.parameters.Add(new ProcedureParam("@quota", SqlDbType.Money, 8, user.quota));
storeUser.Execute(true);
return(storeUser.ExtractFromResultset()); // retorna o id do usuário
}
public List <Object> GetUserPrintingCosts(int tenantId, DateTime startDate, DateTime endDate)
{
List <Object> userPrintingCosts;
ProcedureCall retrieveUserPrintingCosts = new ProcedureCall("pr_retrieveUserPrintingCosts", sqlConnection);
retrieveUserPrintingCosts.parameters.Add(new ProcedureParam("@tenantId", SqlDbType.Int, 4, tenantId));
retrieveUserPrintingCosts.parameters.Add(new ProcedureParam("@startDate", SqlDbType.DateTime, 8, startDate));
retrieveUserPrintingCosts.parameters.Add(new ProcedureParam("@endDate", SqlDbType.DateTime, 8, endDate));
retrieveUserPrintingCosts.Execute(true);
userPrintingCosts = retrieveUserPrintingCosts.ExtractFromResultset(typeof(UserPrintingCost));
return(userPrintingCosts);
}
public Tenant GetTenant(String tenantName)
{
ProcedureCall retrieveTenant = new ProcedureCall("pr_retrieveTenant", sqlConnection);
retrieveTenant.parameters.Add(new ProcedureParam("@tenantName", SqlDbType.VarChar, 100, tenantName));
retrieveTenant.Execute(true);
List <Object> returnList = retrieveTenant.ExtractFromResultset(typeof(Tenant));
// Verifica se retornou apenas um item, mais de um indica falha, nenhum também
if (returnList.Count != 1)
{
return(null);
}
return((Tenant)returnList[0]);
}
public AdministratorLogin GetLogin(String username)
{
ProcedureCall retrieveLogin = new ProcedureCall("pr_retrieveAdministratorLogin", sqlConnection);
retrieveLogin.parameters.Add(new ProcedureParam("@username", SqlDbType.VarChar, 100, username));
retrieveLogin.Execute(true);
List <Object> returnList = retrieveLogin.ExtractFromResultset(typeof(AdministratorLogin));
// Verifica se retornou apenas um item, mais de um indica falha, nenhum também
if (returnList.Count != 1)
{
return(null);
}
return((AdministratorLogin)returnList[0]);
}
public List <Object> GetUserCostDetails(int tenantId, int userId, DateTime startDate, DateTime endDate, String detailType)
{
List <Object> userCostDetails;
ProcedureCall retrieveUserCostDetails = new ProcedureCall("pr_retrieveUserCostDetails", sqlConnection);
retrieveUserCostDetails.parameters.Add(new ProcedureParam("tenantId", SqlDbType.Int, 4, tenantId));
retrieveUserCostDetails.parameters.Add(new ProcedureParam("@userId", SqlDbType.Int, 4, userId));
retrieveUserCostDetails.parameters.Add(new ProcedureParam("@startDate", SqlDbType.DateTime, 8, startDate));
retrieveUserCostDetails.parameters.Add(new ProcedureParam("@endDate", SqlDbType.DateTime, 8, endDate));
retrieveUserCostDetails.parameters.Add(new ProcedureParam("@detailType", SqlDbType.VarChar, 50, detailType));
retrieveUserCostDetails.Execute(true);
userCostDetails = retrieveUserCostDetails.ExtractFromResultset(typeof(UserCostDetail));
return(userCostDetails);
}
private void btnGetTenants_Click(object sender, EventArgs e)
{
if (!OpenConnection())
{
return;
}
ProcedureCall retrieveTenants = new ProcedureCall("pr_retrieveTenant", sqlConnection);
retrieveTenants.Execute(true);
DataTable tenantTable = retrieveTenants.ExtractFromResultset(typeof(Tenant), "tb_tenant");
dataGridView1.DataSource = tenantTable.DefaultView;
CloseConnection();
}
public List <Object> GetCopiedDocuments(int tenantId, DateTime startDate, DateTime endDate, int?userId, int?printerId)
{
List <Object> copiedDocuments;
ProcedureCall retrieveCopiedDocuments = new ProcedureCall("pr_retrieveCopiedDocuments", sqlConnection);
retrieveCopiedDocuments.parameters.Add(new ProcedureParam("@tenantId", SqlDbType.Int, 4, tenantId));
retrieveCopiedDocuments.parameters.Add(new ProcedureParam("@startDate", SqlDbType.DateTime, 8, startDate));
retrieveCopiedDocuments.parameters.Add(new ProcedureParam("@endDate", SqlDbType.DateTime, 8, endDate));
retrieveCopiedDocuments.parameters.Add(new ProcedureParam("@userId", SqlDbType.Int, 4, userId));
retrieveCopiedDocuments.parameters.Add(new ProcedureParam("@printerId", SqlDbType.Int, 4, printerId));
retrieveCopiedDocuments.Execute(true);
copiedDocuments = retrieveCopiedDocuments.ExtractFromResultset(typeof(CopiedDocument));
return(copiedDocuments);
}
public Login GetLogin(int tenantId, int loginId)
{
ProcedureCall retrieveLogin = new ProcedureCall("pr_retrieveLogin", sqlConnection);
retrieveLogin.parameters.Add(new ProcedureParam("@tenantId", SqlDbType.Int, 4, tenantId));
retrieveLogin.parameters.Add(new ProcedureParam("@loginId", SqlDbType.Int, 4, loginId));
retrieveLogin.Execute(true);
List <Object> returnList = retrieveLogin.ExtractFromResultset(typeof(Login));
// Verifica se retornou apenas um item, mais de um indica falha, nenhum também
if (returnList.Count != 1)
{
return(null);
}
return((Login)returnList[0]);
}
public Preference GetTenantPreference(int tenantId, String preferenceName)
{
ProcedureCall retrieveTenantPreference = new ProcedureCall("pr_retrieveTenantPreference", sqlConnection);
retrieveTenantPreference.parameters.Add(new ProcedureParam("@tenantId", SqlDbType.Int, 4, tenantId));
retrieveTenantPreference.parameters.Add(new ProcedureParam("@preferenceName", SqlDbType.VarChar, 100, preferenceName));
retrieveTenantPreference.Execute(true);
List <Object> returnList = retrieveTenantPreference.ExtractFromResultset(typeof(Preference));
// Verifica se retornou apenas um item, mais de um indica falha, nenhum também
if (returnList.Count != 1)
{
return(null);
}
return((Preference)returnList[0]);
}
public User GetUser(int tenantId, int userId)
{
ProcedureCall retrieveUser = new ProcedureCall("pr_retrieveUser", sqlConnection);
retrieveUser.parameters.Add(new ProcedureParam("@tenantId", SqlDbType.Int, 4, tenantId));
retrieveUser.parameters.Add(new ProcedureParam("@userId", SqlDbType.Int, 4, userId));
retrieveUser.Execute(true);
List <Object> returnList = retrieveUser.ExtractFromResultset(typeof(User));
// Verifica se retornou apenas um item, mais de um indica falha, nenhum também
if (returnList.Count != 1)
{
return(null);
}
return((User)returnList[0]);
}
public PrintingDevice GetPrintingDevice(int tenantId, String serialNumber)
{
ProcedureCall retrievePrintingDevices = new ProcedureCall("pr_retrievePrintingDevice", sqlConnection);
retrievePrintingDevices.parameters.Add(new ProcedureParam("@tenantId", SqlDbType.Int, 4, tenantId));
retrievePrintingDevices.parameters.Add(new ProcedureParam("@serialNumber", SqlDbType.VarChar, 100, serialNumber));
retrievePrintingDevices.Execute(true);
List <Object> deviceList = retrievePrintingDevices.ExtractFromResultset(typeof(PrintingDevice));
if (deviceList.Count == 1)
{
return((PrintingDevice)deviceList[0]);
}
else
{
return(null);
}
}
public Printer GetPrinter(int tenantId, int printerId)
{
ProcedureCall retrievePrinter = new ProcedureCall("pr_retrievePrinter", sqlConnection);
retrievePrinter.parameters.Add(new ProcedureParam("@tenantId", SqlDbType.Int, 4, tenantId));
retrievePrinter.parameters.Add(new ProcedureParam("@printerId", SqlDbType.Int, 4, printerId));
retrievePrinter.Execute(true);
List <Object> retrievedPrinter = retrievePrinter.ExtractFromResultset(typeof(Printer));
if (retrievedPrinter.Count == 1)
{
return((Printer)retrievedPrinter[0]);
}
else
{
return(null);
}
}
public Mailing GetMailing(int tenantId, int?mailingId)
{
ProcedureCall retrieveMailing = new ProcedureCall("pr_retrieveMailing", sqlConnection);
retrieveMailing.parameters.Add(new ProcedureParam("@tenantId", SqlDbType.Int, 4, tenantId));
retrieveMailing.parameters.Add(new ProcedureParam("@mailingId", SqlDbType.Int, 4, mailingId));
retrieveMailing.Execute(true);
List <Object> returnList = retrieveMailing.ExtractFromResultset(typeof(Mailing));
if (returnList.Count == 1)
{
return((Mailing)returnList[0]);
}
else
{
return(null);
}
}
public SmtpServer GetSmtpServer(int tenantId, int smtpServerId)
{
ProcedureCall retrieveSmtpServer = new ProcedureCall("pr_retrieveSmtpServer", sqlConnection);
retrieveSmtpServer.parameters.Add(new ProcedureParam("@tenantId", SqlDbType.Int, 4, tenantId));
retrieveSmtpServer.parameters.Add(new ProcedureParam("@smtpServerId", SqlDbType.Int, 4, smtpServerId));
retrieveSmtpServer.Execute(true);
List <Object> returnList = retrieveSmtpServer.ExtractFromResultset(typeof(SmtpServer));
if (returnList.Count == 1)
{
return((SmtpServer)returnList[0]);
}
else
{
return(null);
}
}
public ApplicationParam GetParam(String paramName, String ownerTask)
{
// Para identificar unicamente um parametro são necessários três campos:
// [name], [applicationID], [ownerTask]
// a procedure utilizada retorna somente parametros onde [applicationId] = ID("Print Accounting")
// [name] e [ownerTask] são passados na chamada deste método
ApplicationParam accountingParam;
ProcedureCall retrieveAccountingParams = new ProcedureCall("pr_retrieveAccountingParams", sqlConnection);
retrieveAccountingParams.Execute(true);
List <Object> paramList = retrieveAccountingParams.ExtractFromResultset(typeof(ApplicationParam));
this.currentParamName = paramName;
this.currentOwnerTask = ownerTask;
accountingParam = (ApplicationParam)paramList.Find(CheckParamMethod);
return(accountingParam);
}
private static ListItem[] GetItems(String procedureName, SqlConnection sqlConnection, Type objectType, Boolean strict)
{
ProcedureCall procedureCall = new ProcedureCall(procedureName, sqlConnection);
Object tenant = HttpContext.Current.Session["tenant"];
NameValueCollection tenantInfo = HttpUtility.ParseQueryString(tenant.ToString());
int tenantId = 0;
int.TryParse(tenantInfo["id"], out tenantId);
procedureCall.parameters.Add(new ProcedureParam("@tenantId", SqlDbType.Int, 4, tenantId));
procedureCall.Execute(true);
List <Object> returnList = procedureCall.ExtractFromResultset(objectType);
ItemScaffolding itemScaffolding = new ItemScaffolding();
itemScaffolding.FindItemFields(objectType);
ListItem[] itemArray;
int ndx;
if (strict)
{
itemArray = new ListItem[returnList.Count];
ndx = 0;
}
else
{
itemArray = new ListItem[returnList.Count + 1];
itemArray[0] = new ListItem();
itemArray[0].Value = "0";
itemArray[0].Text = "";
ndx = 1;
}
foreach (Object obj in returnList)
{
itemArray[ndx] = new ListItem();
itemArray[ndx].Value = itemScaffolding.GetItemId(obj).ToString();
itemArray[ndx].Text = itemScaffolding.GetItemName(obj);
ndx++;
}
return(itemArray);
}