public void SetAuditLogin(string appVer)
{
string pcName = Environment.MachineName.Substring(0, 8);
string pcOSVer = Environment.OSVersion.ToString();
string pcPlatForm = Environment.OSVersion.Platform.ToString();
int osMajor = Environment.OSVersion.Version.Major;
int osMinor = Environment.OSVersion.Version.Minor;
bool bitBool = Environment.Is64BitOperatingSystem; int bit = -1;
string appVersion = appVer;
if (bitBool)
{
bit = 64;
}
else
{
bit = 32;
}
long seqNum = GetSeq("annotator.GPJ_AUDIT_SEQ");
int month = DateTime.Now.Month; int day = DateTime.Now.Day; int year = DateTime.Now.Year;
conn.Open();
string sqlCommand = $"INSERT INTO GPJ_APP_AUDIT VALUES ({seqNum}, '{pcName}', SYSDATE, 'GPJ', '{pcName}', '{pcOSVer}', '{pcPlatForm}', {osMajor}, {osMinor}, " +
$"{bit.ToString()}, 'TWITTER_POSTER', '{appVersion}', 'TWITPSTR', SYSDATE, '{TimeZone.CurrentTimeZone.StandardName}');";
ADODB.Recordset rst = new ADODB.Recordset();
object rowsAffected;
rst = conn.Execute(sqlCommand, out rowsAffected);
conn.Close();
}
public object[] db_access(string strSQL)
{
ADODB.Connection objCon;
ADODB.Recordset objRec;
object[,] dataRows;
object[] dataSuite;
string strCon;
objCon = new ADODB.Connection();
objRec = new ADODB.Recordset();
//establish the connection string and open the database connection
strCon = "driver={MySQL ODBC 5.1 Driver};server=107.22.232.228;uid=qa_people;pwd=thehandcontrols;" +
"database=functional_test_data;option=3";
objCon.Open(strCon);
//execute the SQL and return the recrodset of results
objRec = objCon.Execute(strSQL, out missing, 0);
//populate a two dinmensional object array with the results
dataRows = objRec.GetRows();
//get a onedimensional array that can be placed into the Test Suite dropdown
dataSuite = thinArray(dataRows);
//close the recordset
objRec.Close();
//close the database connection
objCon.Close();
return(dataSuite);
}
public static void ubahDB2(string db)
{
ADODB.Connection aco = default(ADODB.Connection);
aco = new ADODB.Connection();
if (aco.State.Equals(ObjectStateEnum.adStateOpen))
{
aco.Close();
aco = null;
}
aco.CursorLocation = CursorLocationEnum.adUseClient;
aco.Properties.Refresh();
aco.Open("DSN=DSNask2");
object obj = Type.Missing;
aco.Execute(db, out obj);
}
public static ADODB.Recordset PangggilDB(string db)
{
ADODB.Connection AC = new ADODB.Connection();
if (AC.State.Equals(ObjectStateEnum.adStateOpen))
{
AC.Close();
AC = null;
}
AC.CursorLocation = CursorLocationEnum.adUseClient;
AC.Properties.Refresh();
AC.Open("DSN=DSNask");
object obj = Type.Missing;
ADODB.Recordset AR = new ADODB.Recordset();
AR = AC.Execute(db, out obj);
return(AR);
}
public void RefreshServerList()
{
ADODB.Connection cn = new ADODB.Connection();
ADODB.Recordset rs = new ADODB.Recordset();
ADODB.Command cmd = new ADODB.Command();
Excel.Workbook wb = Globals.ThisAddIn.Application.ActiveWorkbook;
Excel.Worksheet ws;
string ldapQry = Properties.Settings.Default.Rdg_LdapQry;
try
{
cn.Open("Provider=ADsDSOObject;");
ldapQry = ldapQry.Replace("[Rdg.LdapPath]", Properties.Settings.Default.Rdg_LdapPath);
cmd.ActiveConnection = cn;
object recs;
rs = cn.Execute(ldapQry, out recs, 0);
//bool sheetExists;
//For Each ws In wb.Sheets
// If My.Settings.Rdg_SheetName = ws.Name Then
// sheetExists = True
// ws.Activate()
// End If
//Next ws
//If sheetExists = False Then
// ws = wb.ActiveSheet
// Dim answer As Integer
// string msg = "The sheet named '" & My.Settings.Rdg_SheetName & "' does not exist."
// msg = msg & vbCrLf & "Would you like to use the current sheet?"
// answer = MsgBox(msg, vbYesNo + vbQuestion, "Sheet Not Found")
// 'MessageBox.Show(msg, "Unexpected Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
// If answer = vbYes Then
// ws = wb.ActiveSheet
// My.Settings.Rdg_SheetName = wb.ActiveSheet.Name
// Else
// Exit Try
// End If
//Else
//ws = wb.Worksheets[Properties.Settings.Default.Rdg_SheetName];
ws = wb.ActiveSheet;
//End If
ws.Activate();
Ribbon.ClearSheetContents();
for (int i = 0; i <= rs.Fields.Count - 1; i++)
{
ws.Cells[1, i + 1].Value = rs.Fields[i].Name;
}
ws.Range[ws.Cells[1, 1], ws.Cells[1, rs.Fields.Count]].Font.Bold = true;
ws.Range["A2"].CopyFromRecordset(rs);
Ribbon.FormatTableFromRange();
Excel.ListObject tbl = Globals.ThisAddIn.Application.ActiveCell.ListObject;
Ribbon.UpdateZeroStringToNull(tbl);
Ribbon.FormatDateColumns();
//'create server type column from the first 2 characters of the server name
//'If (My.Settings.Rdg_ServerGroup = "ServerType" Then
//' tbl.ListColumns.Add(3).Name = My.Settings.Rdg_ServerGroup
//' tbl.ListColumns(My.Settings.Rdg_ServerGroup).DataBodyRange.FormulaR1C1 = "=UPPER(IFERROR(IF(SEARCH(""-"", [@Name]) > 0, LEFT([@Name], 2), """"), ""(NONE)""))"
//' Globals.ThisAddIn.Application.Columns.AutoFit()
//'End If
Ribbon.ribbonref.InvalidateRibbon(); //'reset dropdown lists
Ribbon.ActivateTab();
}
catch (Exception ex)
{
ErrorHandler.DisplayMessage(ex);
}
}
public object[] db_access(string strSQL)
{
ADODB.Connection objCon;
ADODB.Recordset objRec;
object[,] dataRows;
object[] dataSuite;
string strCon;
objCon = new ADODB.Connection();
objRec = new ADODB.Recordset();
//establish the connection string and open the database connection
strCon = "driver={MySQL ODBC 5.1 Driver};server=107.22.232.228;uid=qa_people;pwd=thehandcontrols;" +
"database=functional_test_data;option=3";
objCon.Open(strCon);
//execute the SQL and return the recrodset of results
objRec = objCon.Execute(strSQL, out missing, 0);
//populate a two dinmensional object array with the results
dataRows = objRec.GetRows();
//get a onedimensional array that can be placed into the Test Suite dropdown
dataSuite = thinArray(dataRows);
//close the recordset
objRec.Close();
//close the database connection
objCon.Close();
return dataSuite;
}
private void btnExtract_Click(object sender, EventArgs e)
{
object[] rtnList;
string[] lstArray;
string argID;
string itmList;
string strCon;
string steID;
string thsFuncID;
string tstID;
int itmCount;
int argNum;
int numSteps;
int numTests;
ArrayList slctList;
ADODB.Connection objCon;
DialogResult valExtract;
itmList = "";
itmCount = lstCaseSelect.Items.Count;
lstArray = new string[itmCount];
strCon = "driver={MySQL ODBC 5.1 Driver};server=107.22.232.228;uid=qa_people;pwd=thehandcontrols;" +
"database=functional_test_data;option=3";
objCon = new ADODB.Connection();
//set lstArray to all items in the lstCaseSelect box
for (int x = 0; x < itmCount; x++)
lstArray[x] = lstCaseSelect.Items[x].ToString();
slctList = new ArrayList(itmCount);
for (int x = 0; x < itmCount; x++)
{
//get a carriage return delimited string of all entries in lstCaseSelect
itmList = itmList + lstArray[x] + "\r\n";
//add the items to slctList whiule iterating through the lstCaseSelect items
slctList.Add(lstCaseSelect.Items[x]);
}
//show an information message box with an escape option
valExtract = MessageBox.Show("You will be extracting the following tests from the database \r\n\r\n" + itmList + "\r\nSelect Yes to continue. Select No to return to TestDriver ",
"Database Test Extractor", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
if (valExtract == DialogResult.Yes)
{
for (int cnt = 0; cnt < itmCount; cnt++)
{
string strSQL;
//get the function ID aznd number of steps from the test table
strSQL = "SELECT id, number_of_steps FROM test WHERE name = '" + lstArray[cnt] + "'";
rtnList = db_access(strSQL, ref fndExcep);
tstID = rtnList[0].ToString();
if (rtnList[1].ToString() != "")
{
numSteps = Convert.ToInt32(rtnList[1]);
}
else
{
numSteps = 0;
}
//open a connection to the database
objCon.Open(strCon);
//set a for loop with x + 1 being the current step number being processed
//renmove all steps that are not used in any other tests (recCount = 1)
for (int x = 0; x < numSteps; x++)
{
//get the function id and arg set id using the
strSQL = "SELECT function_id, argument_set_id FROM step WHERE (test_id = '" + tstID + "' AND number = '" + (x + 1).ToString() + "')" ;
rtnList = db_access(strSQL, ref fndExcep);
thsFuncID = rtnList[0].ToString();
argID = rtnList[1].ToString();
strSQL = "DELETE FROM step WHERE argument_set_id = '" + argID + "' AND function_id = '" + thsFuncID +
"' AND test_id = '" + tstID + "' AND number = '" + (x + 1).ToString() + "'";
objCon.Execute(strSQL, out missing, 0);
//if an argument set is no longer used, gert rid of it
strSQL = "SELECT COUNT(*) FROM step WHERE argument_set_id = '" + argID + "'";
rtnList = db_access(strSQL, ref fndExcep);
argNum = Convert.ToInt32(rtnList[0]);
if (argNum == 0)
{
strSQL = "DELETE FROM argument WHERE argument_set_id = '" + argID + "'";
objCon.Execute(strSQL, out missing, 0);
//delete t
strSQL = "DELETE FROM argument_set WHERE id = '" + argID + "'";
objCon.Execute(strSQL, out missing, 0);
}
}
//get the regression suite id from the test being extracted
strSQL= "SELECT regression_suite_id FROM test WHERE id = '" + tstID + "'";
rtnList = db_access(strSQL, ref fndExcep);
steID = rtnList[0].ToString();
//delete the test from the test rable
strSQL = "DELETE FROM test WHERE id = '" + tstID + "'";
objCon.Execute(strSQL, out missing, 0);
//get the number of tests from in the regression suite. If delete the regression suite
strSQL = "SELECT COUNT(*) FROM test WHERE regression_suite_id = '" + steID + "'";
rtnList = db_access(strSQL, ref fndExcep);
numTests = Convert.ToInt32(rtnList[0]);
//if there are no tests left in the database delete the suite
if (numTests == 0)
{
strSQL = "DELETE FROM regression_suite WHERE id = '" + steID + "'";
objCon.Execute(strSQL, out missing, 0);
}
//close the database connection
objCon.Close();
//get the list of
for (int x = 0; x < slctList.Count; x++)
{
if (Convert.ToString(slctList[x]) == lstArray[cnt])
{
slctList.Remove(lstArray[cnt]);
break;
}
}
}
lstCaseSelect.Items.Clear();
}
}
private object[] db_access(string strSQL, ref int fndExcep)
{
ADODB.Connection objCon;
ADODB.Recordset objRec;
object[,] dataRows;
object[] dataSuite;
string strCon;
string tmpString;
dataSuite = null;
objCon = new ADODB.Connection();
objRec = new ADODB.Recordset();
try
{
//establish the connection string and open the database connection
strCon = "driver={MySQL ODBC 5.1 Driver};server=107.22.232.228;uid=qa_people;pwd=thehandcontrols;" +
"database=functional_test_data;option=3";
objCon.Open(strCon);
//execute the SQL and return the recrodset of results
objRec = objCon.Execute(strSQL, out missing, 0);
//populate a two dinmensional object array with the results
dataRows = objRec.GetRows();
//get a one dimensional array that can be placed into the Test Suite dropdown
dataSuite = thinArray(dataRows);
//close the recordset
objRec.Close();
//close the database connection
objCon.Close();
}
catch (Exception e)
{
tmpString = e.Message;
//set the variable to ternibate the script
fndExcep = -1;
}
return dataSuite;
}
public static void update_stock()
{
try
{
SqlConnectionStringBuilder decoder = new SqlConnectionStringBuilder(System.Configuration.ConfigurationManager.ConnectionStrings["Con"].ConnectionString);
SqlConnection Conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Con"].ConnectionString);
Conn.Close();
Conn.Open();
ADODB.Connection ADOconn = new ADODB.Connection();
ADOconn.Open("Provider=SQLOLEDB;Initial Catalog= " + decoder.InitialCatalog + ";Data Source=" + decoder.DataSource + ";", decoder.UserID, decoder.Password, 0);
string sql;
sql = "SELECT item_code,STOCK FROM STOCK_ITEM WHERE WR_CODE=" + Gvar.wr_code;
ADODB.Recordset tmp = new Recordset();
ADODB.Recordset tmp1 = new Recordset();
tmp.Open(sql, ADOconn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, -1);
do
{
Application.DoEvents();
object ST = 0;
object itm = "";
ST = 0;
// if (tmp.RecordCount != 0) ST = tmp.Fields[0].Value;
ST = tmp.Fields[1].Value;
itm = tmp.Fields[0].Value;
sql = "SELECT * FROM WR_STOCK_MASTER WHERE WR_CODE=" + Gvar.wr_code + " AND ITEM_CODE='" + itm + "'";
tmp1 = new Recordset();
tmp1.Open(sql, ADOconn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, -1);
tmp1.Fields["stock"].Value = ST;
tmp1.Update();
sql = "SELECT SUM(STOCK) FROM WR_STOCK_MASTER WHERE ITEM_CODE='" + itm + "'";
tmp1 = new Recordset();
tmp1.Open(sql, ADOconn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, -1);
ST = 0;
if (!tmp1.EOF)
{
ST = tmp1.Fields[0].Value;
}
if (!Convert.IsDBNull(tmp1.Fields[0].Value))
{
sql = "Update STOCK_MASTER set stock = " + Math.Round(Convert.ToDecimal(tmp1.Fields[0].Value), 2) + " where ITEM_CODE='" + itm + "'";
}
else
{
sql = "Update STOCK_MASTER set stock = 0 where ITEM_CODE='" + itm + "'";
}
object a;
ADOconn.Execute(sql, out a, -1);
Application.DoEvents();
tmp.MoveNext();
} while (!tmp.EOF);
}
catch (Exception ex)
{
}
}
private void save_form()
{
try
{
//ADOconn.BeginTrans();
ADODB.Recordset cus = new ADODB.Recordset();
//ADOconn.BeginTrans();
if (cmbgrp.Text == "")
{
MessageBox.Show("Invalid User Group", "Invalid Entry");
return;
}
dgv1.EndEdit();
ADODB.Connection ADOconn = new ADODB.Connection();
ADOconn.Open("Provider=SQLOLEDB;Initial Catalog= " + decoder.InitialCatalog + ";Data Source=" + decoder.DataSource + ";", decoder.UserID, decoder.Password, 0);
for (int i = 1; i < dgv1.RowCount; i++)
{
if (dgv1["id", i].Value == null)
{
continue;
}
cus = new ADODB.Recordset();
if (!Convert.ToBoolean(dgv1["select", i].Value))
{
sql = "delete FROM Menu_Xpress where id=" + dgv1["id", i].Value;
object a;
ADOconn.Execute(sql, out a);
continue;
}
else
{
sql = "SELECT * FROM Menu_Xpress where id=" + dgv1["id", i].Value;
cus.Open(sql, ADOconn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, -1);
if (cus.RecordCount == 0)
{
cus.AddNew();
}
cus.Fields["GROUP_NAME"].Value = cmbgrp.Text;
cus.Fields["id"].Value = Convert.ToInt16(dgv1["id", i].Value);
cus.Fields["Menu_code"].Value = dgv1["menucode", i].Value;
cus.Fields["Menu_Name"].Value = dgv1["menuname", i].Value;
cus.Update();
}
}
string grp = cmbgrp.Text;
sql = "SELECT distinct group_name FROM UserPriv ";
SqlDataAdapter adagrp = new SqlDataAdapter(sql, Conn);
///ada.TableMappings.Add("Table", "Leaders");
DataSet grpds = new DataSet();
adagrp.Fill(grpds, "UserPriv");
cmbgrp.DisplayMember = "group_name";
cmbgrp.DataSource = grpds.Tables[0];
cmbgrp.Text = grp;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
