private void metrobtnExpiry_Click(object sender, EventArgs e)
{
if (String.IsNullOrEmpty(txtCoreAccountss.Text.ToString()) || string.IsNullOrEmpty(cmbDatabases.Text.ToString()))
{
MessageBox.Show(" Please select Account and Coredatabase");
return;
}
string SQL;
int count = 0;
//Check whether Database exists on the SQL Server
if (!(ClassCoreUtility.checkIfDatabaseExistsOnSQL(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Database does not exist on SQL Server");
return;
}
//Check whether database is Core Database
if (!(ClassCoreUtility.checkIfCoreDatabase(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Selected Database not a Core Database!!");
return;
}
//Check whether account present in CORE
if (!(ClassCoreUtility.checkIfAccountPresentInCore(txtCoreAccountss.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString(), cmbDatabases.Text.ToString())))
{
MessageBox.Show("Account does not exist in Core! Please give correct Account");
return;
}
// Check whether Database exist in CORE
if (!(ClassCoreUtility.checkIfDatabaseAlreadyAddedToCore(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Database does not exist Core");
return;
}
SQL = "UPDATE BQECoreHost.dbo.Company SET CompanyStatus=2,TrailExpDate='2038-01-02 15:23:19.973' WHERE DatabaseID='" + cmbDatabases.Text + "';";
count = ClassCoreUtility.executeSQLInsertUpdate(SQL, this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString());
if (count > 0)
{
MessageBox.Show("Expiry extended sucessully in Core");
}
else
{
MessageBox.Show("Opeartion not sucessfull");
}
}
private void btnDeleteQuery_Click(object sender, EventArgs e)
{
string SQL;
int count;
try
{
if (txtSQLQuery.Text == "")
{
MessageBox.Show("Kindly Write the Query first");
return;
}
//Check whether Database Coreutility exists on the SQL Server
if (!(ClassCoreUtility.checkIfDatabaseExistsOnSQL("CoreUtility", cmbSQLServer.Text.ToString(), txtUserID.Text.ToString(), txtPassword.Text.ToString())))
{
MessageBox.Show("Create Querylist table first!!");
return;
}
//Check if Query exists Querylist table
SQL = "SELECT * FROM CoreUtility.dbo.QueryList WHERE Query='" + txtSQLQuery.Text + "';";
count = ClassCoreUtility.executeSQLSelect(SQL, this.server, txtUserID.Text, txtPassword.Text, cmbDatabases.Text);
if (count == 0)
{
MessageBox.Show("Query not present in the QueryList table");
return;
}
//Delete Query in QueryList table
SQL = "";
SQL += "DELETE FROM CoreUtility.dbo.QueryList WHERE Query='" + txtSQLQuery.Text + "'AND Comment='" + txtComment.Text + "';";
count = ClassCoreUtility.executeSQLInsertUpdate(SQL, this.server.ToString(), txtUserID.Text.ToString(), txtPassword.Text.ToString());
if (count != 0)
{
MessageBox.Show("Query deleted sucessfully");
btnLoadQueries.PerformClick();
txtComment.Text = "";
txtSQLQuery.Text = "";
}
else
{
MessageBox.Show("Query not deleted, Try Again !!");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void btnDeleteQuerylistTable_Click(object sender, EventArgs e)
{
try
{
string SQL;
int count;
if (String.IsNullOrEmpty(cmbSQLServer.Text.ToString()) || string.IsNullOrEmpty(cmbDatabases.Text.ToString()))
{
MessageBox.Show(" Please select UserID, Password, SQL Server or Coredatabase");
return;
}
//Check whether Database Coreutility exists on the SQL Server
if (!(ClassCoreUtility.checkIfDatabaseExistsOnSQL("CoreUtility", cmbSQLServer.Text.ToString(), txtUserID.Text.ToString(), txtPassword.Text.ToString())))
{
MessageBox.Show("Database does not exist on SQL Server");
return;
}
//restore Querylist table to original form
SQL = "";
// count = ClassCoreUtility.executeSQLInsertUpdate(SQL, cmbSQLServer.Text.ToString(), txtUserID.Text.ToString(), txtPassword.Text.ToString());
// Create table Query List, Add Default valuse to the QueryList table
SQL = "";
SQL += "USE [CoreUtility]; DELETE FROM dbo.QueryList; SET IDENTITY_INSERT [dbo].[QueryList] ON ; ";
SQL += " INSERT[dbo].[QueryList]([ID], [Query], [Comment], [Software], [IsActive]) VALUES(CAST(1 AS Numeric(18, 0)), N'SELECT inv.InvoiceDetail_ID,inv.amt,pay.Payamt FROM (SELECT InvoiceDetail_ID, SUM(Amount) AS amt FROM dbo.InvoiceDetail GROUP BY InvoiceDetail_ID) inv JOIN (SELECT InvoiceDetail_ID, SUM(AmountPaid) AS Payamt FROM dbo.PaymentDetail GROUP BY InvoiceDetail_ID) pay ON pay.InvoiceDetail_ID = inv.InvoiceDetail_ID WHERE inv.amt <> pay.Payamt AND(ABS(inv.amt - pay.Payamt) BETWEEN 0 AND 1);', N'--Penny issue in invoices and Paymnets', N'CORE ', 1) ; INSERT[dbo].[QueryList] ([ID], [Query], [Comment], [Software], [IsActive]) VALUES(CAST(2 AS Numeric(18, 0)), N'SELECT inv.InvoiceDetail_ID,inv.amt,pay.Payamt FROM (SELECT InvoiceDetail_ID, SUM(Amount) AS amt FROM dbo.InvoiceDetail GROUP BY InvoiceDetail_ID) inv JOIN (SELECT InvoiceDetail_ID, SUM(AmountPaid) AS Payamt FROM dbo.PaymentDetail GROUP BY InvoiceDetail_ID) pay ON pay.InvoiceDetail_ID = inv.InvoiceDetail_ID WHERE inv.amt <> pay.Payamt AND pay.Payamt > inv.amt;', N'--Payment more than invoice', N'CORE ', 1) ; INSERT[dbo].[QueryList] ([ID], [Query], [Comment], [Software], [IsActive]) VALUES(CAST(3 AS Numeric(18, 0)), N'SELECT i.Invoice_ID,i.InvoiceAmount,inv.amt FROM (SELECT Invoice_ID, SUM(Amount) AS amt FROM dbo.InvoiceDetail GROUP BY Invoice_ID)inv JOIN dbo.Invoice i ON i.Invoice_ID = inv.Invoice_ID WHERE i.InvoiceAmount <> inv.amt;', N'--Different amount in Invoice and InvoiceDetail for same invoice', N'CORE ', 1) ; INSERT[dbo].[QueryList] ([ID], [Query], [Comment], [Software], [IsActive]) VALUES(CAST(4 AS Numeric(18, 0)), N'SELECT p.Payment_ID,p.PayAmt,pay.paymentamount FROM (SELECT Payment_ID, SUM(AmountPaid) AS paymentamount FROM dbo.PaymentDetail GROUP BY Payment_ID) pay JOIN dbo.Payment p ON p.Payment_ID = pay.Payment_ID WHERE p.PayAmt <> pay.paymentamount;', N'--Different amount in Payment and PaymentDetail', N'CORE ', 1) ; INSERT[dbo].[QueryList] ([ID], [Query], [Comment], [Software], [IsActive]) VALUES(CAST(5 AS Numeric(18, 0)), N'SELECT Entity_ID,COUNT(*) FROM dbo.Workflow GROUP BY Entity_ID HAVING COUNT(*)>1;', N'--Duplicate workflow entry for same Time/Expense Entry', N'CORE ', 1) ; INSERT[dbo].[QueryList] ([ID], [Query], [Comment], [Software], [IsActive]) VALUES(CAST(6 AS Numeric(18, 0)), N'SELECT * FROM dbo.BillPayment WHERE BillPayment_ID NOT IN (SELECT MasterTransaction_Id FROM dbo.Accounting WHERE MasterTransaction_Id IS NOT NULL);', N'--Vendor bill payments present in BillPayment/BillPaymentdetails table but not present in Accounting table', N'CORE ', 1) ; INSERT[dbo].[QueryList] ([ID], [Query], [Comment], [Software], [IsActive]) VALUES(CAST(7 AS Numeric(18, 0)), N'SELECT id.Client_ID AS InvoiceClient, p.Client_ID AS ProjClient,id.Invoice_ID FROM dbo.InvoiceDetail id JOIN dbo.Project p ON p.Project_ID = id.Project_ID WHERE id.Client_ID <> p.Client_ID;', N'--Different client in InvoiceDetails table and Project table/Wrong client in Invoice', N'CORE ', 1) ; INSERT[dbo].[QueryList] ([ID], [Query], [Comment], [Software], [IsActive]) VALUES(CAST(8 AS Numeric(18, 0)), N'SELECT debt.DebtAmount,credit.CreditAmount,credit.Accounting_ID FROM (SELECT SUM(ISNULL(Amount, 0)) AS DebtAmount, Accounting_ID FROM dbo.AccountingDetail WHERE IsDebit = 1 GROUP BY Accounting_ID)debt INNER JOIN (SELECT SUM(ISNULL(Amount, 0)) AS CreditAmount, Accounting_ID FROM dbo.AccountingDetail WHERE IsDebit = 0 GROUP BY Accounting_ID)credit ON credit.Accounting_ID = debt.Accounting_ID WHERE debt.DebtAmount <> credit.CreditAmount;', N'--Sum of Debit/Credits not equal', N'CORE ', 1) ; INSERT[dbo].[QueryList] ([ID], [Query], [Comment], [Software], [IsActive]) VALUES(CAST(9 AS Numeric(18, 0)), N'SELECT DefaultGroup_ID,* FROM dbo.Project WHERE DefaultGroup_ID NOT IN (SELECT Group_ID FROM dbo.Groups );', N'--Orphan default group in Projects', N'CORE ', 1) ; INSERT[dbo].[QueryList] ([ID], [Query], [Comment], [Software], [IsActive]) VALUES(CAST(10 AS Numeric(18, 0)), N'SELECT * FROM dbo.Accounting WHERE TransactionType=450 AND Transaction_Id NOT IN(SELECT VendorBill_ID FROM dbo.VendorBill );', N'--Orphan vendor bills/vendor bill details present in accounting tables but deleted from vendor bill tables', N'CORE ', 1) ; INSERT[dbo].[QueryList] ([ID], [Query], [Comment], [Software], [IsActive]) VALUES(CAST(11 AS Numeric(18, 0)), N'SELECT * FROM dbo.TimeEntry WHERE VendorBill_ID IS NOT NULL AND VendorBill_ID NOT IN (SELECT VendorBill_ID FROM dbo.VendorBill);', N'--Orphan VBs in Time Entry', N'CORE ', 1) ; INSERT[dbo].[QueryList] ([ID], [Query], [Comment], [Software], [IsActive]) VALUES(CAST(12 AS Numeric(18, 0)), N'SELECT * FROM dbo.ExpenseLog WHERE VendorBill_ID IS NOT NULL AND VendorBill_ID NOT IN (SELECT VendorBill_ID FROM dbo.VendorBill);', N'--Orphan VBs in Expense Entry', N'CORE ', 1) ; INSERT[dbo].[QueryList] ([ID], [Query], [Comment], [Software], [IsActive]) VALUES(CAST(14 AS Numeric(18, 0)), N'SELECT * FROM dbo.TimeEntry WHERE Invoice_ID IS NOT NULL AND Invoice_ID NOT IN (SELECT Invoice_ID FROM dbo.Invoice);', N'--Orphan Invoices in Time Entry', N'CORE ', 1) ; INSERT[dbo].[QueryList] ([ID], [Query], [Comment], [Software], [IsActive]) VALUES(CAST(15 AS Numeric(18, 0)), N'SELECT * FROM dbo.ExpenseLog WHERE Invoice_ID IS NOT NULL AND Invoice_ID NOT IN (SELECT Invoice_ID FROM dbo.Invoice);', N'--Orphan Invoices in Expense Entry', N'CORE ', 1) ; INSERT[dbo].[QueryList] ([ID], [Query], [Comment], [Software], [IsActive]) VALUES(CAST(16 AS Numeric(18, 0)), N'SELECT * FROM dbo.CheckExpenseLineItem WHERE Check_ID NOT IN (SELECT Check_ID FROM dbo.Checks);', N'--Orphan Checks in CheckExpenseLineItem', N'CORE ', 1) ; INSERT[dbo].[QueryList] ([ID], [Query], [Comment], [Software], [IsActive]) VALUES(CAST(17 AS Numeric(18, 0)), N'SELECT * FROM dbo.Checks WHERE IsBillPayment =1 AND Check_ID NOT IN (SELECT Entity_ID FROM dbo.BillPayment WHERE Entity_ID IS NOT NULL);', N'--Orphan check in Checks Payment deleted in BillPaymnet', N'CORE ', 1) ; INSERT[dbo].[QueryList] ([ID], [Query], [Comment], [Software], [IsActive]) VALUES(CAST(19 AS Numeric(18, 0)), N'SELECT * FROM dbo.VendorBillExpenseItem WHERE VendorBill_ID NOT IN (SELECT VendorBill_ID FROM dbo.VendorBill);', N'--Orphan VB in VendorBillExpenseItem', N'CORE ', 1) ; INSERT[dbo].[QueryList] ([ID], [Query], [Comment], [Software], [IsActive]) VALUES(CAST(20 AS Numeric(18, 0)), N'SELECT * FROM dbo.BillPaymentDetail WHERE BillPayment_ID NOT IN (SELECT BillPayment_ID FROM dbo.BillPayment);', N'--Orphan bill paymnet in BillPaymentDetail', N'CORE ', 1) ; INSERT[dbo].[QueryList] ([ID], [Query], [Comment], [Software], [IsActive]) VALUES(CAST(21 AS Numeric(18, 0)), N'SELECT * FROM dbo.InvoiceDetail WHERE Invoice_ID NOT IN (SELECT Invoice_ID FROM dbo.Invoice);', N'--Orphan Invoices in InvoiceDetail', N'CORE ', 1) ; INSERT[dbo].[QueryList] ([ID], [Query], [Comment], [Software], [IsActive]) VALUES(CAST(22 AS Numeric(18, 0)), N'SELECT * FROM dbo.CreditCardExpenseItem WHERE CreditCardPayment_ID NOT IN (SELECT CreditCardPayment_ID FROM dbo.CreditCardPayment);', N'--Orphan Credit card payment in CreditCardExpenseItem ', N'CORE ', 1) ; INSERT[dbo].[QueryList] ([ID], [Query], [Comment], [Software], [IsActive]) VALUES(CAST(23 AS Numeric(18, 0)), N'SELECT * FROM dbo.PaymentDetail WHERE Payment_ID NOT IN (SELECT Payment_ID FROM dbo.Payment);', N'--Orphan Invoice Payments in PaymentDetail', N'CORE ', 1) ; INSERT[dbo].[QueryList] ([ID], [Query], [Comment], [Software], [IsActive]) VALUES(CAST(24 AS Numeric(18, 0)), N'SELECT * FROM dbo.Invoice i JOIN(SELECT Invoice_ID, SUM(Amount) AS amt FROM dbo.InvoiceDetail GROUP BY Invoice_ID)id ON id.Invoice_ID = i.Invoice_ID WHERE i.InvoiceAmount <> id.amt;', N'-- Different amount in Invoice and InvoiceDetails for same invoice ', N'CORE ', 1) ; INSERT[dbo].[QueryList] ([ID], [Query], [Comment], [Software], [IsActive]) VALUES(CAST(26 AS Numeric(18, 0)), N'SELECT * FROM dbo.Payment i JOIN (SELECT Payment_ID, SUM(AmountPaid) AS amt FROM dbo.PaymentDetail GROUP BY Payment_ID)id ON id.Payment_ID = i.Payment_ID WHERE i.PayAmt <> id.amt;', N'-- Different amount in Payment and PaymentDetails for same Payment', N'CORE ', 1) ; INSERT[dbo].[QueryList] ([ID], [Query], [Comment], [Software], [IsActive]) VALUES(CAST(27 AS Numeric(18, 0)), N'SELECT el.VendorBill_ID,el.Reimbursable,el.ExpenseLog_ID,* FROM dbo.ExpenseLog el JOIN dbo.Workflow wf ON el.ExpenseLog_ID=wf.Entity_ID WHERE el.Reimbursable = 1 AND el.VendorBill_ID IS NULL AND wf.WorkflowAction = 2 ;', N'----Approved Reimbursable Expenses with NULL Vendor Bill IDs', N'CORE ', 1) ; INSERT[dbo].[QueryList] ([ID], [Query], [Comment], [Software], [IsActive]) VALUES(CAST(28 AS Numeric(18, 0)), N'SELECT p1.Client_ID, p1.RetainerPaymentTaken,p2.RetainerAmtApplied FROM (SELECT Client_ID, SUM(PayAmt) AS RetainerPaymentTaken FROM dbo.Payment WHERE PayRetainer = 1 GROUP BY Client_ID) p1 JOIN (SELECT Client_ID, SUM(PayAmt) AS RetainerAmtApplied FROM dbo.Payment WHERE PayMethod = -1 GROUP BY Client_ID) p2 ON p2.Client_ID = p1.Client_ID WHERE p2.RetainerAmtApplied > p1.RetainerPaymentTaken;', N'-- --Retainer applied more than retainer taken ', N'CORE ', 1) ; INSERT[dbo].[QueryList] ([ID], [Query], [Comment], [Software], [IsActive]) VALUES(CAST(29 AS Numeric(18, 0)), N'SELECT * FROM dbo.PaymentDetail WHERE Payment_ID IN (SELECT Payment_ID FROM dbo.Payment WHERE PayMethod=-1) AND AmountPaid<> CRA+PRA+PPRA;', N'-- CRA+PRA+PPRA <> AMOUNT PAID', N'CORE ', 1) ; INSERT[dbo].[QueryList] ([ID], [Query], [Comment], [Software], [IsActive]) VALUES(CAST(30 AS Numeric(18, 0)), N'SELECT * FROM dbo.Project WHERE Project_ID IN (SELECT RootProject_ID FROM dbo.InvoiceDetail WHERE RootProject_ID IS NOT NULL) AND ProjectLevel <>0;', N'----Child project set as Root Project ', N'CORE ', 1) ; INSERT[dbo].[QueryList] ([ID], [Query], [Comment], [Software], [IsActive]) VALUES(CAST(31 AS Numeric(18, 0)), N'SELECT id.RootProject_ID,p.ParentProject_ID,id.Project_ID,* FROM dbo.InvoiceDetail id JOIN dbo.Project p ON p.Project_ID = id.Project_ID WHERE p.ParentProject_ID IS NOT NULL AND id.RootProject_ID IS NULL AND id.InvoiceDetail_ID IN (SELECT InvoiceDetail_ID FROM dbo.PaymentDetail WHERE(PPRA <> 0 OR PRA <> 0) AND InvoiceDetail_ID IS NOT NULL) ;', N'---Root project as NULL in Invoice Details for retainer applied', N'CORE ', 1) ; INSERT[dbo].[QueryList] ([ID], [Query], [Comment], [Software], [IsActive]) VALUES(CAST(32 AS Numeric(18, 0)), N'SELECT a.TransactionAmount,i.InvoiceAmount, (a.TransactionAmount-i.InvoiceAmount) AS diff,i.Invoice_ID FROM dbo.Accounting a JOIN dbo.Invoice i ON i.Invoice_ID=a.MasterTransaction_Id WHERE a.TransactionType IN (''250'',''12'',''22'',''200'') AND a.TransactionAmount<>i.InvoiceAmount;', N'---- Different amount for Invoices in Invoice table and Accounting table', N'CORE ', 1) ; SET IDENTITY_INSERT [dbo].[QueryList] OFF; ";
count = ClassCoreUtility.executeSQLInsertUpdate(SQL, cmbSQLServer.Text.ToString(), txtUserID.Text.ToString(), txtPassword.Text.ToString());
if (count > 0)
{
MessageBox.Show("QueryList table successfully restored ");
}
else
{
MessageBox.Show("Operation not successfull,,Try again!!");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void btnLoadQueryDetails_Click(object sender, EventArgs e)
{
string SQL;
string connectString;
try
{
//Check whether Database Coreutility exists on the SQL Server
if (!(ClassCoreUtility.checkIfDatabaseExistsOnSQL("CoreUtility", cmbSQLServer.Text.ToString(), txtUserID.Text.ToString(), txtPassword.Text.ToString())))
{
MessageBox.Show("Create Querylist table first!!");
return;
}
//Check if Queries loaded
if (cmbQueryList.Text == "")
{
MessageBox.Show("Load Queries First ");
return;
}
// Set connection string with selected server and integrated security
connectString = "Data Source=" + this.server + ";Integrated Security=false;Initial Catalog=master;User ID=" +
txtUserID.Text + ";Password="******";";
txtComment.Text = cmbQueryList.Text;
using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(connectString))
{
// Open connection
con.Open();
//Get QueryList names in server in a datareader
SQL = "SELECT Query FROM CoreUtility.dbo.QueryList WHERE Software='CORE' AND IsActive=1 and Comment='" + cmbQueryList.Text + "';";
System.Data.SqlClient.SqlCommand com =
new System.Data.SqlClient.SqlCommand(SQL, con);
System.Data.SqlClient.SqlDataReader dr = com.ExecuteReader();
while (dr.Read())
{
txtSQLQuery.Text = dr["Query"].ToString();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void btnLoadServers_Click(object sender, EventArgs e)
{
try
{
ClassCoreUtility.CaluculateAll(PBarSQLServer);
// Create a instance of the SqlDataSourceEnumerator class
servers = SqlDataSourceEnumerator.Instance;
// Fetch all visible SQL server 2000 or SQL Server 2005 instances
tableServers = new DataTable();
// Check if datatable is empty
if (tableServers.Rows.Count == 0)
{
// Get a datatable with info about SQL Server 2000 and 2005 instances
tableServers = servers.GetDataSources();
// List that will be combobox’s datasource
List <string> listservers = new List <string>();
// For each element in the datatable add a new element in the list
foreach (DataRow rowServer in tableServers.Rows)
{
// Server instance could have instace name or only server name,
// check this for show the name
if (String.IsNullOrEmpty(rowServer["InstanceName"].ToString()))
{
listservers.Add(rowServer["ServerName"].ToString());
}
else
{
listservers.Add(rowServer["ServerName"] + "\\" + rowServer["InstanceName"]);
}
}
// Set servers list to combobox’s datasource
this.cmbSQLServer.DataSource = listservers;
}
}
catch (Exception ex)
{ ex.ToString(); }
}
private void btnValidateQuery_Click(object sender, EventArgs e)
{
string SQL;
int count;
try
{
if (txtSQLQuery.Text == "")
{
MessageBox.Show("Kindly load the Query first");
return;
}
//Check whether database is Core Database
if (!(ClassCoreUtility.checkIfCoreDatabase(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPassword.Text.ToString())))
{
MessageBox.Show("Selected Database not a Core Database!!");
return;
}
//Validate Query
SQL = txtSQLQuery.Text;
count = ClassCoreUtility.executeSQLSelect(SQL, this.server, txtUserID.Text, txtPassword.Text, cmbDatabases.Text);
if (count == 1)
{
MessageBox.Show("Query validated sucessfully on SQL Server");
}
if (count == 0)
{
MessageBox.Show("Query validated sucessfully on SQL Server");
}
}
catch (Exception ex)
{
MessageBox.Show("Error in Query. Correct your Query!!!");
}
}
private void metrobtnDeleteDatabaseSQLServer_Click(object sender, EventArgs e)
{
try
{
DialogResult dialogResult = MessageBox.Show("Do you really want to delete the database from SQL server?", "Delete database", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
txtCurrentCoreLoginUsersss.Text = "";
cmbFullAccessUser.Text = string.Empty;
if (string.IsNullOrEmpty(cmbDatabases.Text.ToString()) || string.IsNullOrEmpty(txtCoreAccountss.Text.ToString()))
{
MessageBox.Show("Please select Coredatabase and Account");
return;
}
string SQL;
int count = 0;
//Check whether Database exists on the SQL Server
if (!(ClassCoreUtility.checkIfDatabaseExistsOnSQL(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Database does not exist on SQL Server");
return;
}
//Check whether account present in CORE
if (!(ClassCoreUtility.checkIfAccountPresentInCore(txtCoreAccountss.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString(), cmbDatabases.Text.ToString())))
{
MessageBox.Show("Account does not exist in Core! Please give correct Account");
return;
}
//Delete database from SQL Server
SQL = "SELECT * FROM sys.databases WHERE name='" + cmbDatabases.Text + "';";
count = ClassCoreUtility.executeSQLSelect(SQL, this.server, txtUserID.Text, txtPasswordsss.Text, cmbDatabases.Text);
if (count == 0)
{
MessageBox.Show("Database does not exist on SQL Server");
}
SQL = "";
count = 0;
SQL = " DROP DATABASE [" + cmbDatabases.Text + "];";
count = ClassCoreUtility.executeSQLInsertUpdate(SQL, this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString());
if (count != 0)
{
MessageBox.Show("Database deleted sucessully from SQL Server");
}
else
{
MessageBox.Show("Database not deleted");
}
//Call connect databases button event explicitly
btnConnectDB.PerformClick();
}
}
catch (Exception ex)
{ MessageBox.Show("Cannot delete database as it is in use. Kindly close all open connections on SQL Server!!"); }
}
private void btnMakeCoreLoginUsers_Click(object sender, EventArgs e)
{
try
{
if (String.IsNullOrEmpty(txtCoreAccountss.Text.ToString()) || string.IsNullOrEmpty(cmbDatabases.Text.ToString()))
{
MessageBox.Show(" Please select Account and Coredatabase");
return;
}
string SQL;
//Check whether Database exists on the SQL Server
if (!(ClassCoreUtility.checkIfDatabaseExistsOnSQL(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Database does not exist on SQL Server");
return;
}
//Check whether database is Core Database
if (!(ClassCoreUtility.checkIfCoreDatabase(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Selected Database not a Core Database!!");
return;
}
//Check whether account present in CORE
if (!(ClassCoreUtility.checkIfAccountPresentInCore(txtCoreAccountss.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString(), cmbDatabases.Text.ToString())))
{
MessageBox.Show("Account does not exist in Core! Please give correct Account");
return;
}
//Check whether database has full access security employee
if (!(ClassCoreUtility.checkIfCoreDBHasFullAccessUser(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("No Employee with full access security/Set the security manually");
return;
}
//Make selected user as Core login User
SQL = "UPDATE [" + cmbDatabases.Text + "]. dbo.Employee SET HostAccount_ID=NULL WHERE HostAccount_ID IS NOT NULL; UPDATE [" + cmbDatabases.Text + "]. dbo.Employee SET HostAccount_ID = (SELECT ID FROM BQECoreHost.dbo.Account WHERE Email= '" + txtCoreAccountss.Text + "'), Password = (SELECT Password FROM BQECoreHost.dbo.Account WHERE Email= '" + txtCoreAccountss.Text + "') WHERE EmployeeID = '" + cmbFullAccessUser.Text + "'; ";
int count = ClassCoreUtility.executeSQLInsertUpdate(SQL, this.server, txtUserID.Text, txtPasswordsss.Text);
if (count > 0)
{
MessageBox.Show("Selected Employee made as Core Login User sucessfully!!");
}
else
{
MessageBox.Show("Opeartion not sucessful!!");
}
//Get current login Core User
string st = ClassCoreUtility.getCurrentCoreLoginUser(cmbDatabases.Text, txtCoreAccountss.Text, this.server, txtUserID.Text, txtPasswordsss.Text);
txtCurrentCoreLoginUsersss.Text = st;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void btlLoadFullAccessUsers_Click(object sender, EventArgs e)
{
try
{
if (!(ClassCoreUtility.checkIfDatabaseAlreadyAddedToCore(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Database does not exist in CORE!!");
return;
}
if (String.IsNullOrEmpty(txtCoreAccountss.Text.ToString()) || string.IsNullOrEmpty(cmbDatabases.Text.ToString()))
{
MessageBox.Show(" Please select Account and Coredatabase");
return;
}
string SQL, connectString;
List <string> lstFullAccessUsers = new List <string>();
//Check whether Database exists on the SQL Server
if (!(ClassCoreUtility.checkIfDatabaseExistsOnSQL(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Database does not exist on SQL Server");
return;
}
//Check whether database is Core Database
if (!(ClassCoreUtility.checkIfCoreDatabase(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Selected Database not a Core Database!!");
return;
}
//Check whether account present in CORE
if (!(ClassCoreUtility.checkIfAccountPresentInCore(txtCoreAccountss.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString(), cmbDatabases.Text.ToString())))
{
MessageBox.Show("Account does not exist in Core! Please give correct Account");
return;
}
//Check whether database has full access security employee
if (!(ClassCoreUtility.checkIfCoreDBHasFullAccessUser(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("No Employee with full access security/Set the security manually");
return;
}
// Set connection string with selected server and integrated security
connectString = "Data Source=" + this.server + ";Integrated Security=false;Initial Catalog=master;User ID=" +
txtUserID.Text + ";Password="******";";
using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(connectString))
{
// Open connection
con.Open();
//Get list of full access users
SQL = "SELECT EmployeeID FROM [" + cmbDatabases.Text + "].dbo.Employee WHERE Employee_ID IN (SELECT Employee_ID FROM [" + cmbDatabases.Text + "].dbo.Security WHERE SecurityTemplate_ID IN(SELECT SecurityTemplate_ID FROM [" + cmbDatabases.Text + "]. dbo.SecurityTemplate WHERE SecurityTemplateName = 'Full Access'));";
SqlCommand com =
new SqlCommand(SQL, con);
SqlDataReader dr = com.ExecuteReader();
while (dr.Read())
{
lstFullAccessUsers.Add(dr[0].ToString());
}
// Set databases list as combobox’s datasource
this.cmbFullAccessUser.DataSource = lstFullAccessUsers;
//Get current login Core User
string st = ClassCoreUtility.getCurrentCoreLoginUser(cmbDatabases.Text, txtCoreAccountss.Text, this.server, txtUserID.Text, txtPasswordsss.Text);
txtCurrentCoreLoginUsersss.Text = st;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void btnGetDiscrepency_Click(object sender, EventArgs e)
{
try
{
richTxtDiscrepency.Text = "";
ClassCoreUtility.CaluculateAll(PBarDiscrepency);
//ClassCoreUtility.CaluculateAll(pgrDiscrepency);
if (string.IsNullOrEmpty(cmbDatabases.Text.ToString()) || string.IsNullOrEmpty(txtCoreAccountss.Text.ToString()))
{
MessageBox.Show("Please select Coredatabase and Account");
return;
}
string SQL;
int count = 0;
//Check whether Database exists on the SQL Server
if (!(ClassCoreUtility.checkIfDatabaseExistsOnSQL(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Database does not exist on SQL Server");
return;
}
//Check whether database is Core Database
if (!(ClassCoreUtility.checkIfCoreDatabase(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Selected Database not a Core Database!!");
return;
}
//Check whether Database exist in CORE
if (!(ClassCoreUtility.checkIfDatabaseAlreadyAddedToCore(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Database does not exist Core");
return;
}
//Get discrepency in Core database
Dictionary <string, string> dctDiscrepency = ClassCoreUtility.GetDiscrepency(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString());
foreach (var item in dctDiscrepency)
{
string comment = item.Key.ToString();
int lencomment = comment.Length;
richTxtDiscrepency.Text += comment;
//richTxtDiscrepency.Select(richTxtDiscrepency.Text.IndexOf("--"), lencomment);
//richTxtDiscrepency.ForeColor = System.Drawing.Color.Green;
//richTxtDiscrepency.SelectionColor = Color.Green;
//richTxtDiscrepency.SelectedText = Environment.NewLine + item.Value;
richTxtDiscrepency.AppendText(Environment.NewLine);
int lenSQL = item.Value.Length;
richTxtDiscrepency.Text += item.Value.ToString();
richTxtDiscrepency.AppendText(Environment.NewLine);
richTxtDiscrepency.AppendText(Environment.NewLine);
}
if (richTxtDiscrepency.Text == "")
{
MessageBox.Show("No Discrepency found!!");
}
else
{
MessageBox.Show("Copy the Discrepencies!!");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void metrobtn_delete_Click(object sender, EventArgs e)
{
try
{
DialogResult dialogResult = MessageBox.Show("Do you really want to delete the Database from Core?", "Delete from Core", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
txtCurrentCoreLoginUsersss.Text = "";
cmbFullAccessUser.Text = string.Empty;
richTxtDiscrepency.Text = "";
if (string.IsNullOrEmpty(cmbDatabases.Text.ToString()) || string.IsNullOrEmpty(txtCoreAccountss.Text.ToString()))
{
MessageBox.Show("Please select Coredatabase and Account");
return;
}
string SQL;
int count = 0;
//Check whether Database exists on the SQL Server
if (!(ClassCoreUtility.checkIfDatabaseExistsOnSQL(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Database does not exist on SQL Server");
return;
}
//Check whether database is Core Database
if (!(ClassCoreUtility.checkIfCoreDatabase(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Selected Database not a Core Database!!");
return;
}
//Check whether Database exist in CORE
if (!(ClassCoreUtility.checkIfDatabaseAlreadyAddedToCore(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Database does not exist Core");
return;
}
//Check whether account present in CORE
if (!(ClassCoreUtility.checkIfAccountPresentInCore(txtCoreAccountss.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString(), cmbDatabases.Text.ToString())))
{
MessageBox.Show("Account does not exist in Core! Please give correct Account");
return;
}
//Delete database from Core
SQL = "";
SQL = "USE BQECoreHost;";
SQL += "delete FROM dbo.AccountCompany WHERE Company_ID IN(SELECT ID FROM dbo.Company WHERE DatabaseID='" + cmbDatabases.Text + "');";
SQL += "DELETE FROM company WHERE DatabaseID = '" + cmbDatabases.Text + "'; ";
count = ClassCoreUtility.executeSQLInsertUpdate(SQL, this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString());
if (count > 0)
{
MessageBox.Show("Database deleted sucessully from Core");
}
else
{
MessageBox.Show("Database not deleted");
}
btnConnectDB.PerformClick();
}
}
catch (Exception ex)
{ MessageBox.Show(ex.ToString()); }
}
private void metrobtnConnectCore_Click(object sender, EventArgs e)
{
try
{
txtCurrentCoreLoginUsersss.Text = "";
cmbFullAccessUser.Text = string.Empty;
richTxtDiscrepency.Text = "";
if (String.IsNullOrEmpty(txtCoreAccountss.Text.ToString()) || string.IsNullOrEmpty(cmbDatabases.Text.ToString()))
{
MessageBox.Show(" Please select Account and Coredatabase");
return;
}
string SQL;
int count = 0;
//Check whether Database exists on the SQL Server
if (!(ClassCoreUtility.checkIfDatabaseExistsOnSQL(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Database does not exist on SQL Server");
return;
}
//Check whether database is Core Database
if (!(ClassCoreUtility.checkIfCoreDatabase(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Selected Database not a Core Database!!");
return;
}
//Check whether Database exist in CORE
if ((ClassCoreUtility.checkIfDatabaseAlreadyAddedToCore(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("Database already added to Core");
return;
}
//Check whether account present in CORE
if (!(ClassCoreUtility.checkIfAccountPresentInCore(txtCoreAccountss.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString(), cmbDatabases.Text.ToString())))
{
MessageBox.Show("Account does not exist in Core! Please give correct Account");
return;
}
//Check whether database has full access security employee
if (!(ClassCoreUtility.checkIfCoreDBHasFullAccessUser(cmbDatabases.Text.ToString(), this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString())))
{
MessageBox.Show("No Employee with full access security/Set the security manually");
}
//Add Database to the CORE
SQL = "";
SQL = "USE BQECoreHost;";
SQL += " DECLARE @industry AS UNIQUEIDENTIFIER, @country AS UNIQUEIDENTIFIER, @version AS NVARCHAR(MAX),";
SQL += " @DatabaseID AS NVARCHAR(MAX), @account AS UNIQUEIDENTIFIER, @company AS UNIQUEIDENTIFIER, @route AS UNIQUEIDENTIFIER,";
SQL += " @DatabaseName AS NVARCHAR(MAX), @password AS NVARCHAR(MAX), @EmployeeID AS NVARCHAR(MAX), @DataFileLocation AS NVARCHAR(MAX), @LogFileLocation AS NVARCHAR(MAX); ";
SQL += "SET @industry =(SELECT TOP 1 ID FROM BQECoreMaster.dbo.Industry WHERE SICDescription =" + "'" + "Computer Programmer" + "');";
SQL += "SET @country =( SELECT TOP 1 ID FROM BQECoreHost.dbo.Country WHERE Name ='" + "United States" + "');";
SQL += "SET @version =( SELECT TOP 1 CoreVersion FROM BQECoreMaster.dbo.UpdateQuery ORDER BY CreateDate DESC);";
SQL += "SET @DatabaseID = '" + cmbDatabases.Text + "';";
SQL += "SET @account =( SELECT ID FROM BQECoreHost.dbo.Account WHERE Email = '" + txtCoreAccountss.Text + "');";
SQL += "SET @password =( SELECT Password FROM BQECoreHost.dbo.Account WHERE Email = '" + txtCoreAccountss.Text + "');";
SQL += "SET @route =( SELECT ID FROM BQECoreHost.dbo.Route WHERE ID='88888888-4444-4444-4444-123456789012');";
SQL += "SET @DataFileLocation = '' + 'C:\\Program Files\\Microsoft SQL Server\\MSSQL13.MSSQLSERVER2016D\\MSSQL\\DATA\\" + cmbDatabases.Text + ".mdf';";
SQL += "SET @LogFileLocation = '' + 'C:\\Program Files\\Microsoft SQL Server\\MSSQL13.MSSQLSERVER2016D\\MSSQL\\DATA\\" + cmbDatabases.Text + "_log.ldf';";
SQL += " INSERT INTO Company ( [ID], [Name], [Industry_ID], [Country_ID], [CreatedOn], [TrailExpDate], [CompanyStatus], [StatusMessage],[CoreDBVersion],[Mode],[UpdatedBy],[UpdatedOn],[CreatedBy], [HasArchive], [DatabaseID], [CoreDeliveredVersion],[DataFileLocation], [LogFileLocation] )";
SQL += "VALUES (NEWID(),'" + cmbDatabases.Text + "'," + " @industry, @country, GETDATE(), DATEADD(yy, 20, GETDATE()), 2, NULL,@version,2,NULL,GETDATE(), @account,0,'" + cmbDatabases.Text + "', @version,@DataFileLocation, @LogFileLocation); ";
SQL += "SET @company =(SELECT TOP 1 ID FROM BQECoreHost.dbo.Company WHERE DatabaseID = @DatabaseID); ";
SQL += " INSERT INTO AccountCompany( [ID], [Account_ID], [UpdatedOn],[UserType], [CreatedOn], [Company_ID],[UserStatus], [UpdatedBy],[CreatedBy],[IsDefault], [Route_ID])";
SQL += "VALUES (NEWID(),@account, GETDATE(),0, GETDATE(), @company,0,@account,@account,NULL, @route);";
SQL += "SET @EmployeeID=(SELECT TOP 1 Employee_ID FROM [" + cmbDatabases.Text + "].dbo.Employee WHERE HostAccount_ID IS NOT NULL AND Employee_ID IN(SELECT Employee_ID FROM [" + cmbDatabases.Text + "].dbo.Security WHERE SecurityTemplate_ID = '5DE836B0-F3F2-45DD-8A44-0C689E8B2ACD')); ";
SQL += "UPDATE [" + cmbDatabases.Text + "].dbo.Employee SET HostAccount_ID = @account,Password = @password WHERE Employee_ID = @EmployeeID; ";
count = ClassCoreUtility.executeSQLInsertUpdate(SQL, this.server.ToString(), txtUserID.Text.ToString(), txtPasswordsss.Text.ToString());
if (count > 0)
{
MessageBox.Show("Database added sucessfully to Core");
}
else
{
MessageBox.Show("Database not added to Core");
}
}
catch (Exception ex)
{ MessageBox.Show(ex.ToString()); }
}
private void btnAddQuery_Click(object sender, EventArgs e)
{
string SQL;
int count;
try
{
if (txtSQLQuery.Text == "")
{
MessageBox.Show("Kindly Write the Query first");
return;
}
//Check whether Database Coreutility exists on the SQL Server
if (!(ClassCoreUtility.checkIfDatabaseExistsOnSQL("CoreUtility", cmbSQLServer.Text.ToString(), txtUserID.Text.ToString(), txtPassword.Text.ToString())))
{
MessageBox.Show("Create Querylist table first!!");
return;
}
//Check if Query already added to Querylist table
SQL = "SELECT * FROM CoreUtility.dbo.QueryList WHERE Query='" + txtSQLQuery.Text + "';";
count = ClassCoreUtility.executeSQLSelect(SQL, this.server, txtUserID.Text, txtPassword.Text, cmbDatabases.Text);
if (count == 1)
{
MessageBox.Show("Query already added to QueryList table");
return;
}
//Check if Comment already used in Querylist table
SQL = "";
SQL = "SELECT * FROM CoreUtility.dbo.QueryList WHERE Comment='" + txtComment.Text + "';";
count = ClassCoreUtility.executeSQLSelect(SQL, this.server, txtUserID.Text, txtPassword.Text, cmbDatabases.Text);
if (count == 1)
{
MessageBox.Show("Comment already usedin QueryList table. Please choose different comment!!");
return;
}
//Validate Query
SQL = "";
SQL = txtSQLQuery.Text;
count = ClassCoreUtility.executeSQLSelect(SQL, this.server, txtUserID.Text, txtPassword.Text, cmbDatabases.Text);
//Insert Query in QueryList table
SQL = "";
SQL += "INSERT INTO CoreUtility. dbo.QueryList(Query,Comment,Software,IsActive) VALUES( '" + txtSQLQuery.Text + "','" + txtComment.Text + "','CORE',1);";
count = ClassCoreUtility.executeSQLInsertUpdate(SQL, this.server.ToString(), txtUserID.Text.ToString(), txtPassword.Text.ToString());
if (count != 0)
{
MessageBox.Show("Query added sucessfully");
btnLoadQueries.PerformClick();
txtComment.Text = "";
txtSQLQuery.Text = "";
}
else
{
MessageBox.Show("Query not added, Try Again !!");
}
}
catch (Exception ex)
{
MessageBox.Show("Error in Query.Kindly correct youq query first");
}
}
