private Boolean SetUrl()
{
txtProcessInfo.Text += Environment.NewLine + "Configurando url do sistema no banco de dados...";
const String executionFail = "Falha ao configurar url do sistema no BD. ";
if (String.IsNullOrEmpty(txtUrl.Text))
{
txtProcessInfo.Text += Environment.NewLine + executionFail + "Favor informar a url de acesso externo. ";
return(false);
}
try
{
DBQuery dbQuery = new DBQuery(sqlConnection);
// dbQuery.Query = "USE AppCommon";
// dbQuery.Execute(false);
// Atualiza a url de acesso ao sistema no banco
dbQuery.Query = "UPDATE tb_applicationParam SET value = '" + txtUrl.Text + "' WHERE name = 'url' AND ownerTask='webAccounting'";
dbQuery.Execute(false);
}
catch (Exception exc)
{
txtProcessInfo.Text += Environment.NewLine + executionFail + Environment.NewLine + exc.Message;
return(false);
}
// Se não houve nenhuma falha retorna status de sucesso
return(true);
}
private Boolean CreateSysUser()
{
const String sysUser = "******";
const String sysPass = "******";
txtProcessInfo.Text += Environment.NewLine + "Criando usuário para uso do sistema...";
const String creationFail = "Falha ao criar usuário para o sistema. ";
try
{
// Cria o usuário que será utilizado pelo sistema para execução de procedures
String creationQuery;
if (sqlVersion > 8) // SQL Server 2005 ou superior
{
creationQuery = "CREATE LOGIN " + sysUser + " WITH PASSWORD = '******'";
}
else
{
creationQuery = "sp_addlogin '" + sysUser + "', '" + sysPass + "'";
}
DBQuery dbQuery = new DBQuery(creationQuery, sqlConnection);
dbQuery.Execute(false);
}
catch (Exception exc)
{
txtProcessInfo.Text += Environment.NewLine + creationFail + Environment.NewLine + exc.Message;
return(false);
}
// Se não houve nenhuma falha armazena o usuário e retorna status de sucesso
sysLogin = new DBLogin(sysUser, sysPass);
return(true);
}
private int GetSQLServerVersion()
{
String query = "SELECT CONVERT(INTEGER, CONVERT(FLOAT, CONVERT(VARCHAR(3), SERVERPROPERTY('ProductVersion')))) majorVersion";
DBQuery dbQuery = new DBQuery(query, sqlConnection);
dbQuery.Execute(true);
List <Object> resultSet = dbQuery.ExtractFromResultset(typeof(SQLServerVersion));
SQLServerVersion productVersion = (SQLServerVersion)resultSet[0];
return(productVersion.majorVersion);
}
private void DisplayAvailableTenants()
{
DBQuery dbQuery = new DBQuery(sqlConnection);
// dbQuery.Query = "USE AppCommon";
// dbQuery.Execute(false);
// Busca todos os tenants cadastrados no banco
dbQuery.Query = "SELECT * FROM tb_tenant";
dbQuery.Execute(true);
DataTable tenantTable = dbQuery.ExtractFromResultset(typeof(TenantInfo), "tenantTable");
tenantGridView.DataSource = tenantTable;
tenantGridView.Columns[2].MinimumWidth = tenantGridView.Width;
}
// Function from file: IsBanned.dm
public static dynamic IsBanned(string key = null, dynamic address = null, string computer_id = null)
{
dynamic _default = null;
bool admin = false;
string ckey = null;
string ckeytext = null;
string ipquery = null;
string cidquery = null;
DBQuery query = null;
string pckey = null;
dynamic ackey = null;
dynamic reason = null;
dynamic expiration = null;
string duration = null;
dynamic bantime = null;
string bantype = null;
string expires = null;
string desc = null;
if (!Lang13.Bool(key) || !Lang13.Bool(address) || !Lang13.Bool(computer_id))
{
GlobalFuncs.log_access("Failed Login (invalid data): " + key + " " + address + "-" + computer_id);
return(new ByTable()
.Set("reason", "invalid login data")
.Set("desc", "Error: Could not check ban status, Please try again. Error message: Your computer provided invalid or blank information to the server on connection (byond username, IP, and Computer ID.) Provided information for reference: Username:'******' IP:'" + address + "' Computer ID:'" + computer_id + "'. (If you continue to get this error, please restart byond or contact byond support.)")
);
}
if (String13.ParseNumber(computer_id) == 2147483648)
{
GlobalFuncs.log_access("Failed Login (invalid cid): " + key + " " + address + "-" + computer_id);
return(new ByTable().Set("reason", "invalid login data").Set("desc", "Error: Could not check ban status, Please try again. Error message: Your computer provided an invalid Computer ID.)"));
}
admin = false;
ckey = String13.CKey(key);
if (GlobalVars.admin_datums.Contains(ckey) || GlobalVars.deadmins.Contains(ckey))
{
admin = true;
}
if (GlobalFuncs.IsGuestKey(key))
{
if (!GlobalVars.guests_allowed)
{
GlobalFuncs.log_access("Failed Login: "******" - Guests not allowed");
return(new ByTable().Set("reason", "guest").Set("desc", "\nReason: Guests not allowed. Please sign in with a byond account."));
}
if (GlobalVars.config.panic_bunker && GlobalVars.dbcon != null && GlobalVars.dbcon.IsConnected())
{
GlobalFuncs.log_access("Failed Login: "******" - Guests not allowed during panic bunker");
return(new ByTable()
.Set("reason", "guest")
.Set("desc", "\nReason: Sorry but the server is currently not accepting connections from never before seen players or guests. If you have played on this server with a byond account before, please log in to the byond account you have played from.")
);
}
}
if (Lang13.Bool(GlobalVars.config.extreme_popcap) && GlobalFuncs.living_player_count() >= (GlobalVars.config.extreme_popcap ?? 0) && !admin)
{
GlobalFuncs.log_access("Failed Login: "******" - Population cap reached");
return(new ByTable().Set("reason", "popcap").Set("desc", "\nReason: " + GlobalVars.config.extreme_popcap_message));
}
if (GlobalVars.config.ban_legacy_system)
{
_default = GlobalFuncs.CheckBan(String13.CKey(key), computer_id, address);
if (Lang13.Bool(_default))
{
if (admin)
{
GlobalFuncs.log_admin("The admin " + key + " has been allowed to bypass a matching ban on " + _default["key"]);
GlobalFuncs.message_admins("<span class='adminnotice'>The admin " + key + " has been allowed to bypass a matching ban on " + _default["key"] + "</span>");
GlobalFuncs.addclientmessage(ckey, "<span class='adminnotice'>You have been allowed to bypass a matching ban on " + _default["key"] + "</span>");
}
else
{
GlobalFuncs.log_access("Failed Login: "******" " + computer_id + " " + address + " - Banned " + _default["reason"]);
return(_default);
}
}
}
else
{
ckeytext = String13.CKey(key);
if (!GlobalFuncs.establish_db_connection())
{
Game13.log.WriteMsg("Ban database connection failure. Key " + ckeytext + " not checked");
GlobalVars.diary.WriteMsg("Ban database connection failure. Key " + ckeytext + " not checked");
return(_default);
}
ipquery = "";
cidquery = "";
if (Lang13.Bool(address))
{
ipquery = " OR ip = '" + address + "' ";
}
if (Lang13.Bool(computer_id))
{
cidquery = " OR computerid = '" + computer_id + "' ";
}
query = GlobalVars.dbcon.NewQuery("SELECT ckey, ip, computerid, a_ckey, reason, expiration_time, duration, bantime, bantype FROM " + GlobalFuncs.format_table_name("ban") + " WHERE (ckey = '" + ckeytext + "' " + ipquery + " " + cidquery + ") AND (bantype = 'PERMABAN' OR bantype = 'ADMIN_PERMABAN' OR ((bantype = 'TEMPBAN' OR bantype = 'ADMIN_TEMPBAN') AND expiration_time > Now())) AND isnull(unbanned)");
query.Execute();
while (query.NextRow())
{
pckey = query.item[1];
ackey = query.item[4];
reason = query.item[5];
expiration = query.item[6];
duration = query.item[7];
bantime = query.item[8];
bantype = query.item[9];
if (bantype == "ADMIN_PERMABAN" || bantype == "ADMIN_TEMPBAN")
{
if (pckey != ckey)
{
continue;
}
}
if (admin)
{
if (bantype == "ADMIN_PERMABAN" || bantype == "ADMIN_TEMPBAN")
{
GlobalFuncs.log_admin("The admin " + key + " is admin banned, and has been disallowed access");
GlobalFuncs.message_admins("<span class='adminnotice'>The admin " + key + " is admin banned, and has been disallowed access</span>");
}
else
{
GlobalFuncs.log_admin("The admin " + key + " has been allowed to bypass a matching ban on " + pckey);
GlobalFuncs.message_admins("<span class='adminnotice'>The admin " + key + " has been allowed to bypass a matching ban on " + pckey + "</span>");
GlobalFuncs.addclientmessage(ckey, "<span class='adminnotice'>You have been allowed to bypass a matching ban on " + pckey + "</span>");
continue;
}
}
expires = "";
if ((String13.ParseNumber(duration) ?? 0) > 0)
{
expires = " The ban is for " + duration + " minutes and expires on " + expiration + " (server time).";
}
else
{
expires = " The is a permanent ban.";
}
desc = "\nReason: You, or another user of this computer or connection (" + pckey + ") is banned from playing here. The ban reason is:\n" + reason + "\nThis ban was applied by " + ackey + " on " + bantime + ", " + expires;
_default = new ByTable().Set("reason", "" + bantype).Set("desc", "" + desc);
GlobalFuncs.log_access("Failed Login: "******" " + computer_id + " " + address + " - Banned " + _default["reason"]);
return(_default);
}
}
_default = Game13._internal_IsBanned(key, address, computer_id);
if (Lang13.Bool(_default))
{
if (admin)
{
GlobalFuncs.log_admin("The admin " + key + " has been allowed to bypass a matching host/sticky ban");
GlobalFuncs.message_admins("<span class='adminnotice'>The admin " + key + " has been allowed to bypass a matching host/sticky ban</span>");
GlobalFuncs.addclientmessage(ckey, "<span class='adminnotice'>You have been allowed to bypass a matching host/sticky ban</span>");
return(null);
}
else
{
GlobalFuncs.log_access("Failed Login: "******" " + computer_id + " " + address + " - Banned " + _default["message"]);
}
}
return(_default);
}
private void btnImport_Click(object sender, EventArgs e)
{
if (!OpenConnection())
{
return;
}
DateTime?fileDate = PrintLogFile.GetDate(txtFileToImport.Text);
if (fileDate == null)
{
MessageBox.Show("Arquivo inválido");
return;
}
DateTime startDate = fileDate.Value;
DateTime endDate = startDate.Add(new TimeSpan(23, 59, 59));
DateRange dateRange = new DateRange(true);
dateRange.SetRange(startDate, endDate);
DBQuery query = new DBQuery(sqlConnection);
query.Query = "use Accounting";
query.Execute(false);
query.Query = "SELECT" + Environment.NewLine +
" PRN_LOG.id jobId," + Environment.NewLine +
" PRN_LOG.tenantId," + Environment.NewLine +
" PRN_LOG.jobTime," + Environment.NewLine +
" USR.alias userName," + Environment.NewLine +
" PRN.alias printerName," + Environment.NewLine +
" PRN_LOG.documentName name," + Environment.NewLine +
" PRN_LOG.pageCount," + Environment.NewLine +
" PRN_LOG.copyCount," + Environment.NewLine +
" PRN_LOG.duplex," + Environment.NewLine +
" PRN_LOG.color" + Environment.NewLine +
"FROM" + Environment.NewLine +
" tb_printLog PRN_LOG" + Environment.NewLine +
" INNER JOIN tb_printer PRN WITH (NOLOCK)" + Environment.NewLine +
" ON PRN_LOG.printerId = PRN.id" + Environment.NewLine +
" INNER JOIN tb_user USR WITH (NOLOCK)" + Environment.NewLine +
" ON PRN_LOG.userId = USR.id";
query.Execute(true);
DataTable printLog = query.ExtractFromResultset(typeof(PrintedDocument), "tb_ptintLog");
//dataGridView1.DataSource = printLog.DefaultView;
if (printLog.Rows.Count > 0)
{
//MessageBox.Show("Erro. Já existem registros correspondentes a data do arquivo!");
//return;
}
Boolean imported = ImportFile(txtFileToImport.Text);
CloseConnection();
if (imported)
{
MessageBox.Show("Arquivo importado com sucesso.");
}
}
/// <summary>
/// Importa os registros do arquivo de log(.CSV) e insere no banco de dados
/// </summary>
public Boolean ImportFile(String fileName)
{
CSVReader reader = new CSVReader(fileName, null);
DataTable printedDocumentTable = reader.Read();
int rowCount = printedDocumentTable.Rows.Count;
// Verifica se existem registros no CSV
if (rowCount < 1)
{
MessageBox.Show("CSV inválido. Nenhum registro encontrado.");
return(false);
}
PrintedDocument printedDocument;
foreach (DataRow row in printedDocumentTable.Rows)
{
printedDocument = new PrintedDocument();
printedDocument.tenantId = 1;
printedDocument.jobTime = DateTime.Parse(row["Time"].ToString());
printedDocument.userName = row["User"].ToString();
printedDocument.printerName = row["Printer"].ToString();
printedDocument.name = row["Document Name"].ToString();
printedDocument.pageCount = int.Parse(row["Pages"].ToString());
printedDocument.copyCount = int.Parse(row["Copies"].ToString());
printedDocument.duplex = ConvertToBool(row["Duplex"].ToString());
printedDocument.color = !ConvertToBool(row["Grayscale"].ToString());
DBQuery query = new DBQuery(sqlConnection);
query.Query = "DECLARE @pageCount INT" + Environment.NewLine +
"SET @pageCount = " + printedDocument.pageCount + Environment.NewLine +
"DECLARE @copyCount INT" + Environment.NewLine +
"SET @copyCount = " + printedDocument.copyCount + Environment.NewLine +
"DECLARE @duplex BIT" + Environment.NewLine +
"SET @duplex = " + ConvertToBit(printedDocument.duplex) + Environment.NewLine +
"DECLARE @color BIT" + Environment.NewLine +
"SET @color = " + ConvertToBit(printedDocument.color) + Environment.NewLine +
"-- Executa procedimento para garantir a existência do usuário no banco" + Environment.NewLine +
"IF NOT EXISTS(SELECT 1 FROM tb_user WHERE name = '" + printedDocument.userName + "')" + Environment.NewLine +
"BEGIN" + Environment.NewLine +
" INSERT INTO tb_user(tenantId, name, alias) VALUES (1, '" + printedDocument.userName + "', '" + printedDocument.userName + "')" + Environment.NewLine +
"END" + Environment.NewLine +
"-- Recupera os dados do usuário" + Environment.NewLine +
"DECLARE @userId INT" + Environment.NewLine +
"SELECT @userId = id" + Environment.NewLine +
"FROM tb_user" + Environment.NewLine +
"WHERE name = '" + printedDocument.userName + "'" + Environment.NewLine +
"-- Executa procedimento para garantir a existência da impresora no banco" + Environment.NewLine +
"IF NOT EXISTS(SELECT 1 FROM tb_printer WHERE name = '" + printedDocument.printerName + "')" + Environment.NewLine +
"BEGIN" + Environment.NewLine +
" INSERT INTO tb_printer(tenantId, name, alias) VALUES (1, '" + printedDocument.printerName + "', '" + printedDocument.printerName + "')" + Environment.NewLine +
"END" + Environment.NewLine +
"-- Recupera os dados da impressora" + Environment.NewLine +
"DECLARE @printerId INT" + Environment.NewLine +
"DECLARE @pageCost MONEY" + Environment.NewLine +
"DECLARE @colorCostDiff MONEY" + Environment.NewLine +
"DECLARE @bwPrinter BIT" + Environment.NewLine +
"SELECT @printerId = id, @pageCost = pageCost, @colorCostDiff = colorCostDiff, @bwPrinter = bwPrinter" + Environment.NewLine +
"FROM tb_printer" + Environment.NewLine +
"WHERE name = '" + printedDocument.printerName + "'" + Environment.NewLine +
"IF (@bwPrinter = 1) -- caso a impressora esteja definida como Monocromática define a impressão como Pb" + Environment.NewLine +
"BEGIN" + Environment.NewLine +
" SET @color = 0" + Environment.NewLine +
"END" + Environment.NewLine +
"DECLARE @jobCost MONEY" + Environment.NewLine +
"SET @jobCost = (@pageCost + (@colorCostDiff * @color) ) * @pageCount * @copyCount" + Environment.NewLine +
"INSERT INTO" + Environment.NewLine +
" tb_printLog(tenantId, jobTime, userId, printerId, documentName, pageCount, copyCount, duplex, color, jobCost)" + Environment.NewLine +
"VALUES" + Environment.NewLine +
" (1, '" + printedDocument.jobTime.ToString("yyyy-MM-dd hh:mm:ss") + "', @userId, @printerId, '" + printedDocument.name + "', @pageCount, @copyCount, @duplex, @color, @jobCost)";
query.Execute(false);
}
return(true);
}
private Boolean CreateTenant()
{
txtProcessInfo.Text += Environment.NewLine + "Criando empresa no sistema...";
const String creationFail = "Falha ao criar a empresa no sistema. ";
if (String.IsNullOrEmpty(txtTenantName.Text))
{
txtProcessInfo.Text += Environment.NewLine + creationFail + "Favor informar um identificador para a empresa. ";
return(false);
}
if (String.IsNullOrEmpty(txtTenantAlias.Text))
{
txtProcessInfo.Text += Environment.NewLine + creationFail + "Favor informar um nome amigável para a empresa. ";
return(false);
}
TenantInfo tenantInfo = null;
try
{
DBQuery dbQuery = new DBQuery(sqlConnection);
// dbQuery.Query = "USE AppCommon";
// dbQuery.Execute(false);
// Insere a empresa no banco de dados (guarda o id da empresa inserida no banco)
txtProcessInfo.Text += Environment.NewLine + "Inserindo a empresa no BD...";
dbQuery.Query = "INSERT INTO tb_tenant VALUES ('" + txtTenantName.Text + "', '" + txtTenantAlias.Text + "')" + Environment.NewLine +
"SELECT SCOPE_IDENTITY() tenantId";
dbQuery.Execute(true);
int?tenantId = dbQuery.ExtractFromResultset();
// Cria o tenantInfo, o tenantId foi o id atribuido pelo BD na operação INSERT
// e obtido através do SCOPE_IDENTITY()
tenantInfo = new TenantInfo(tenantId.Value, txtTenantName.Text, txtTenantAlias.Text);
// Insere as preferências para empresa no banco de dados
txtProcessInfo.Text += Environment.NewLine + "Inserindo preferências para a empresa no BD...";
dbQuery.Query = "INSERT INTO tb_tenantPreference VALUES (" + tenantInfo.id + ", 'sysSender', '*****@*****.**', 'System.String')";
dbQuery.Execute(false);
// Insere os logins de acesso para a empresa no banco de dados
txtProcessInfo.Text += Environment.NewLine + "Inserindo logins de acesso para a empresa no BD...";
dbQuery.Query = "INSERT INTO tb_login VALUES (" + tenantInfo.id + ", 'admin', '1E588BE3A984524C7F2C278686F44E72', 0, 0)" + Environment.NewLine +
"INSERT INTO tb_login VALUES (" + tenantInfo.id + ", 'guest', '1E588BE3A984524C7F2C278686F44E72', 1, 0)";
dbQuery.Execute(false);
// Insere o servidor de smtp default para a empresa no banco de dados
txtProcessInfo.Text += Environment.NewLine + "Inserindo servidor de smtp(default) para a empresa no BD...";
dbQuery.Query = "INSERT INTO tb_smtpServer VALUES (" + tenantInfo.id + ", 'Servidor Default', 'smtp.gmail.com', 587, '*****@*****.**', 'datacopy123', 0)";
dbQuery.Execute(false);
// Altera o database para "Accounting"
dbQuery.Query = "USE Accounting";
dbQuery.Execute(false);
// Insere o centro de custo raiz para a empresa no banco de dados
txtProcessInfo.Text += Environment.NewLine + "Inserindo centro de custo (raiz) para a empresa no BD...";
dbQuery.Query = "INSERT INTO tb_costCenter VALUES (" + tenantInfo.id + ", '" + tenantInfo.alias + "', NULL)";
dbQuery.Execute(false);
}
catch (Exception exc)
{
txtProcessInfo.Text += Environment.NewLine + creationFail + Environment.NewLine + exc.Message;
return(false);
}
// Se não houve nenhuma falha insere a empresa na lista retorna status de sucesso
if (tenantList == null)
{
tenantList = new List <TenantInfo>();
}
tenantList.Add(tenantInfo);
return(true);
}