public bool ExecuteQuery(string strSql)
{
bool flag = false;
try
{
conn.BeginTrans();
ADODB.Command cmd = new ADODB.Command();
cmd.ActiveConnection = conn;
cmd.CommandTimeout = 90;
cmd.CommandType = ADODB.CommandTypeEnum.adCmdText;
cmd.CommandText = strSql;
object objRecordsAffected = Type.Missing;
object objParams = Type.Missing;
cmd.Execute(out objRecordsAffected, ref objParams, (int)ADODB.ExecuteOptionEnum.adExecuteNoRecords);
conn.CommitTrans();
flag = true;
}
catch (Exception ex)
{
flag = false;
conn.RollbackTrans();
//PublicLib.handleError("", this.GetType().Name, ex.Message + string.Format("({0})", strSql));
}
return(flag);
}
/// <summary>
/// Delete Row from access table
/// </summary>
/// <returns></returns>
public bool QueryDeleteOrUpdate(string queryString)
{
if (con == null || con.State != 1)
{
this.OpenConnection();
}
if (con.State != 1)
{
return(false);
}
//Assign the connection to the command
cmd = new CommandClass();
cmd.ActiveConnection = con;
cmd.CommandType = CommandTypeEnum.adCmdText;
//delete row
cmd.CommandText = queryString;
//Execute the command
object nRecordsAffected = Type.Missing;
object oParams = Type.Missing;
cmd.Execute(out nRecordsAffected, ref oParams, (int)ADODB.ExecuteOptionEnum.adExecuteNoRecords);
cmd = null;
return(true);
}
private void _InitSqlLst()
{
//
if (queryLst != null)
{
return;
}
if (con == null)
{
throw new InvalidOperationException();
}
var querylst = new List <string>();
string s;
var cat = new ADOX.Catalog();
var cmd = new ADODB.Command();
queryLst = new List <Query>();
//アクションクエリ
var rs = con.OpenSchema(ADODB.SchemaEnum.adSchemaProcedures);
while (!rs.EOF)
{
s = rs.Fields["PROCEDURE_NAME"].Value.ToString();
rs.MoveNext();
if (!s.StartsWith("~"))
{
querylst.Add(s);
}
}
cat.ActiveConnection = con;
foreach (var q in querylst)
{
//
cmd = (ADODB.Command)cat.Procedures[q].Command;
var sql = cmd.CommandText.ToUpper();
//var name = cmd.Name;
var name = q;
if (!(sql.StartsWith("UPDATE") || sql.StartsWith("INSERT")))
{
return;
}
var query = new Query(name, sql);
queryLst.Add(query);
//if (sql.StartsWith("UPDATE"))
// updateSqlLst.Add(sql);
//if (sql.StartsWith("INSERT"))
// insertSqlLst.Add(sql);
}
}
private void DisplayResults()
{
//SS:01/03/2018:2018-R1:ABSEXCH-19796: When Running the ExchDVT.exe, SQL Admin Passwords are visible in dump file.
ADODB.Connection conn = new ADODB.Connection();
ADODB.Command cmd = new ADODB.Command();
cmd.CommandText = "SELECT RCount = COUNT(*) " +
"FROM [common].[SQLDataValidation] " +
"WHERE UPPER(Severity) = 'HIGH' " +
"AND SchemaName = '" + CompanyCode + "'";
cmd.CommandTimeout = 10000;
if (conn.State == 0)
{
if (connPassword.Trim() == "")
{
conn.Open();
}
else
{
conn.Open(ExchequerCommonSQLConnection, "", connPassword.Trim(),
(int)ADODB.ConnectModeEnum.adModeUnknown);
}
}
conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
DataTable dataTable = new DataTable();
cmd.CommandType = ADODB.CommandTypeEnum.adCmdText;
cmd.ActiveConnection = conn;
ADODB.Recordset recordSet = null;
object objRecAff;
try
{
recordSet = (ADODB.Recordset)cmd.Execute(out objRecAff, Type.Missing, (int)ADODB.CommandTypeEnum.adCmdText);
}
catch
{
throw;
}
for (int i = 0; i < recordSet.RecordCount; i++)
{
if (Convert.ToInt32(recordSet.Fields["RCount"].Value) == 0)
{
// Posting Enabled
PostingEnabledDisabled(true, false);
}
else
{
// Posting Disabled
PostingEnabledDisabled(false, false);
}
}
File.Delete(fileString);
Application.Exit();
}
public static bool Gf_ExecSql(ADODB.Connection Conn, string sQuery)
{
bool returnValue = false;
//Dim iCount As Integer
object[,] OutParam = new object[3, 5];
ADODB.Command adoCmd;
try
{
//Db Connection Check
if (GeneralCommon.M_CN1.State == 0)
{
if (GeneralCommon.GF_DbConnect() == false)
{
return(returnValue);
}
}
Cursor.Current = Cursors.WaitCursor;
//Ado Setting
GeneralCommon.M_CN1.CursorLocation = ADODB.CursorLocationEnum.adUseServer;
adoCmd = new ADODB.Command();
Conn.BeginTrans();
adoCmd.ActiveConnection = GeneralCommon.M_CN1;
adoCmd.CommandText = "UPDATE GP_USTRESULT SET UST_LOC= '',UST_REMARTS = '' WHERE PLATE_NO = '05201206040102'";
object null_object = "";
object null_object2 = "";
adoCmd.Execute(out null_object, ref null_object2, -1);
Conn.CommitTrans();
adoCmd = null;
Cursor.Current = Cursors.Default;
if (GeneralCommon.M_CN1.State != 0)
{
GeneralCommon.M_CN1.Close();
}
}
catch (Exception ex)
{
adoCmd = null;
Conn.RollbackTrans();
returnValue = false;
Cursor.Current = Cursors.Default;
if (GeneralCommon.M_CN1.State != 0)
{
GeneralCommon.M_CN1.Close();
}
GeneralCommon.Gp_MsgBoxDisplay((string)("Gf_ExecProcedure Error : " + ex.Message), "", "");
}
return(returnValue);
}
public int ExecuteSql(CommandTypeEnum eCmdType, SqlFrag frag)
{
ADODB.Command sqlCmd = GenerateCommand(eCmdType, frag);
object obRecsAffected = 0;
sqlCmd.Execute(out obRecsAffected);
return((int)obRecsAffected);
}
public Recordset CreateRecordset(SqlFrag frag, CommandTypeEnum eCmdType = CommandTypeEnum.adCmdText,
CursorLocationEnum eCursorLoc = CursorLocationEnum.adUseClient, CursorTypeEnum eCursorType = CursorTypeEnum.adOpenDynamic,
LockTypeEnum eLockType = LockTypeEnum.adLockOptimistic, ExecuteOptionEnum eExecOptions = ExecuteOptionEnum.adOptionUnspecified)
{
ADODB.Command SqlCmd = GenerateCommand(eCmdType, frag);
Recordset rRecords = new Recordset();
rRecords.CursorLocation = eCursorLoc;
rRecords.Open(SqlCmd, Type.Missing, eCursorType, eLockType, (int)eExecOptions);
return(rRecords);
}
// Call the GISPKG_CCB_ESILOCATION.ValidateESILocation stored procedure to validate
// if the ESI Location is being used by a Premise record on a different Service Point.
private bool ValidateDuplicate(string esiLocation, int g3eFID)
{
bool returnValue = true;
try
{
ADODB.Command cmd = new ADODB.Command();
cmd.CommandText = "{call GISPKG_CCB_ESILOCATION.ValidateESILocation(?,?,?,?,?)}";
cmd.CommandType = CommandTypeEnum.adCmdText;
ADODB.Parameter param = cmd.CreateParameter("ESILocation", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 10, esiLocation);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("G3EFID", DataTypeEnum.adBigInt, ParameterDirectionEnum.adParamInput, 10, g3eFID);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("ExistingFID", DataTypeEnum.adBigInt, ParameterDirectionEnum.adParamOutput, 10);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("JobID", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamOutput, 30);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("Status", DataTypeEnum.adSingle, ParameterDirectionEnum.adParamOutput, 1);
cmd.Parameters.Append(param);
int recordsAffected = 0;
Recordset spRS = m_DataContext.ExecuteCommand(cmd, out recordsAffected);
if (!Convert.ToBoolean(cmd.Parameters["Status"].Value))
{
string job = string.Empty;
if (cmd.Parameters["JobID"].Value.ToString().Length > 0)
{
job = " in Job " + cmd.Parameters["JobID"].Value;
}
if (m_InteractiveMode)
{
MessageBox.Show(ERROR_DUPLICATE_ESILOCATION + cmd.Parameters["ExistingFID"].Value + job, "G/Technology", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
returnValue = false;
}
}
catch (Exception ex)
{
if (m_InteractiveMode)
{
MessageBox.Show("Error in fiESILocationUpdate:ValidateDuplicate - " + ex.Message, "G/Technology", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
returnValue = false;
}
return(returnValue);
}
/// <summary>
/// Method to execute sql query and return the result record set
/// </summary>
/// <param name="sqlString"></param>
/// <returns></returns>
private void ExecuteCommand(string sqlString)
{
try
{
int outRecords = 0;
ADODB.Command command = new ADODB.Command();
command.CommandText = sqlString;
m_rsDesignArea = m_gtdataContext.ExecuteCommand(command, out outRecords);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// Method to execute sql query and return the result record set
/// </summary>
/// <param name="sqlString"></param>
/// <returns></returns>
private Recordset GetRecordSet(string sqlString)
{
try
{
int outRecords = 0;
ADODB.Command command = new ADODB.Command();
command.CommandText = sqlString;
ADODB.Recordset results = DataContext.ExecuteCommand(command, out outRecords);
return(results);
}
catch (Exception)
{
throw;
}
}
private int ExecuteCommand(string sql)
{
ADODB.Recordset results = null;
try
{
int outRecords = 0;
ADODB.Command command = new ADODB.Command();
command.CommandText = sql;
results = m_gTDataContext.ExecuteCommand(command, out outRecords);
return(outRecords);
}
catch
{
throw;
}
}
private void GetExchequerVersion(out string ExVersion, ConnectionStringBuilder connObj)
{
ExVersion = "";
//SS:01/03/2018:2018-R1:ABSEXCH-19796: When Running the ExchDVT.exe, SQL Admin Passwords are visible in dump file.
ADODB.Connection conn = new ADODB.Connection();
ADODB.Command cmd = new ADODB.Command();
if (conn.State == 0)
{
if (connObj.DecryptedPassword.Trim() == "")
{
conn.Open();
}
else
{
conn.Open(ExchequerCommonSQLConnection, "", connObj.DecryptedPassword.Trim(),
(int)ConnectModeEnum.adModeUnknown);
}
}
conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
try
{
cmd.CommandType = ADODB.CommandTypeEnum.adCmdText;
cmd.CommandText = "SELECT name, value FROM fn_listextendedproperty(default, default, default, default, default, default, default); ";
cmd.ActiveConnection = conn;
ADODB.Recordset recordSet = null;
object objRecAff;
recordSet = (ADODB.Recordset)cmd.Execute(out objRecAff, Type.Missing, (int)ADODB.CommandTypeEnum.adCmdText);
if (recordSet.RecordCount > 0)
{
ExVersion = "Exchequer " + recordSet.Fields["value"].Value;
}
if (conn.State == 1)
{
conn.Close();
}
}
catch
{
throw;
}
}
/// <summary>
/// Insert data to table and ignored column tb_ID; True = Success, False = Fail
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <param name="table_name"></param>
/// <returns></returns>
public bool InsertDataToTable <T> (T t, string table_name, string ignore_column_name)
{
if (con == null || con.State != 1)
{
this.OpenConnection();
}
if (con.State != 1)
{
return(false);
}
//Get properties of T
Type itemType = typeof(T);
var properties = itemType.GetProperties(BindingFlags.Public | BindingFlags.Instance);
//get field and value
string s1 = "", s2 = "";
foreach (var p in properties)
{
if (!p.Name.ToLower().Equals(ignore_column_name.ToLower()))
{
s1 += string.Format("[{0}],", p.Name);
s2 += string.Format("'{0}',", p.GetValue(t, null));
}
}
s1 = s1.Substring(0, s1.Length - 1);
s2 = s2.Substring(0, s2.Length - 1);
//Assign the connection to the command
cmd = new CommandClass();
cmd.ActiveConnection = con;
cmd.CommandType = CommandTypeEnum.adCmdText;
cmd.CommandText = string.Format("INSERT INTO {0}({1}) VALUES({2})", table_name, s1, s2);
//Execute the command
object nRecordsAffected = Type.Missing;
object oParams = Type.Missing;
cmd.Execute(out nRecordsAffected, ref oParams, (int)ADODB.ExecuteOptionEnum.adExecuteNoRecords);
cmd = null;
return(true);
}
public void Execute()
{
short fNo = 0;
Recordset connectivityComponentRs = null;
string feederId = string.Empty;
string subStationCode = string.Empty;
try
{
IGTComponent component = Components.GetComponent(11);
connectivityComponentRs = component.Recordset;
if (connectivityComponentRs.RecordCount > 0)
{
connectivityComponentRs.MoveFirst();
fNo = Convert.ToInt16(connectivityComponentRs.Fields["G3E_FNO"].Value);
}
if (fNo == 16) // If affected feature is substation breaker then do nothing
{
return;
}
feederId = Convert.ToString(connectivityComponentRs.Fields["FEEDER_1_ID"].Value);
ADODB.Command command = null;
int outRecords = 0;
string sqlString = string.Format("select distinct ssta_c SSCODE from CONNECTIVITY_N where g3e_fno = 16 and feeder_1_id = '{0}' ", feederId);
command = new ADODB.Command();
command.CommandText = sqlString;
ADODB.Recordset results = DataContext.ExecuteCommand(command, out outRecords);
if (results.RecordCount > 0)
{
results.MoveFirst();
subStationCode = Convert.ToString(results.Fields["SSCODE"].Value);
}
connectivityComponentRs.Fields["SSTA_C"].Value = subStationCode;
}
catch (Exception ex)
{
MessageBox.Show("Error during Set Substation Code execution. " + ex.Message, "G/Technology");
}
}
public DataTable GetData(string strSql, string strSqlData)
{
DataTable dt = new DataTable();
try
{
conn.BeginTrans();
ADODB.Command cmd = new ADODB.Command();
cmd.ActiveConnection = conn;
cmd.CommandTimeout = 90;
cmd.CommandType = ADODB.CommandTypeEnum.adCmdText;
cmd.CommandText = strSql;
object objRecordsAffected = Type.Missing;
object objParams = Type.Missing;
cmd.Execute(out objRecordsAffected, ref objParams, (int)ADODB.ExecuteOptionEnum.adExecuteNoRecords);
ADODB.Recordset rs = new ADODB.Recordset();
rs.Open(strSqlData, conn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, -1);
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.Fill(dt, rs);
adapter.Dispose();
rs.Close();
conn.CommitTrans();
}
catch (Exception ex)
{
conn.RollbackTrans();
//PublicLib.handleError("", this.GetType().Name, ex.Message + string.Format("({0})", strSql));
}
return(dt);
}
protected internal ADOCommand(ADOConnection connection, ADODB.Command command) : base(connection)
{
_command = command;
_useOrdinalBinding = true;
}
private void DisplayResults()
{
lvSummaryResults.Items.Clear();
//SS:01/03/2018:2018-R1:ABSEXCH-19796: When Running the ExchDVT.exe, SQL Admin Passwords are visible in dump file.
ADODB.Connection conn = new ADODB.Connection();
ADODB.Command cmd = new ADODB.Command();
cmd.CommandText = "SELECT [IntegrityErrorCode] " +
", [SummaryDescription] = CONVERT(VARCHAR(100), count(IntegrityErrorCode)) + ' ' + [IntegritySummaryDescription] " +
", [Severity] " +
", [SchemaName] " +
", [TableName] " +
"FROM [common].[SQLDataValidation] " +
"GROUP BY SchemaName, Severity, IntegrityErrorCode, IntegritySummaryDescription, TableName " +
"ORDER BY SchemaName, Severity, IntegrityErrorCode";
cmd.CommandTimeout = 10000;
if (conn.State == 0)
{
if (connPassword.Trim() == "")
{
conn.Open();
}
else
{
conn.Open(ExchequerCommonSQLConnection, "", connPassword.Trim(),
(int)ADODB.ConnectModeEnum.adModeUnknown);
}
}
conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
cmd.CommandType = ADODB.CommandTypeEnum.adCmdText;
cmd.ActiveConnection = conn;
ADODB.Recordset recordSet = null;
object objRecAff;
try
{
recordSet = (ADODB.Recordset)cmd.Execute(out objRecAff, Type.Missing, (int)ADODB.CommandTypeEnum.adCmdText);
}
catch
{
throw;
}
for (int i = 0; i < recordSet.RecordCount; i++)
{
ListViewItem lvItem = new ListViewItem(recordSet.Fields[0].Value);
lvItem.SubItems.Add(recordSet.Fields[1].Value);
lvItem.SubItems.Add(recordSet.Fields[2].Value);
lvItem.SubItems.Add(recordSet.Fields[3].Value);
lvItem.SubItems.Add(recordSet.Fields[4].Value);
lvSummaryResults.Items.Add(lvItem);
recordSet.MoveNext();
}
if (conn.State == 1)
{
conn.Close();
}
// Set INI file based on results of each company
foreach (ListViewItem lvItem in lvCompanies.CheckedItems)
{
tsCheckStatus.Text = "Updating INI file: " + lvItem.SubItems[1].Text.TrimEnd();
Thread.Sleep(1000);
Application.DoEvents();
//SS:01/03/2018:2018-R1:ABSEXCH-19796: When Running the ExchDVT.exe, SQL Admin Passwords are visible in dump file.
conn = new ADODB.Connection();
Command command = new ADODB.Command();
command.CommandText = "SELECT RCount = COUNT(*) " +
"FROM [common].[SQLDataValidation] " +
"WHERE UPPER(Severity) = 'HIGH' " +
"AND SchemaName = '" + lvItem.SubItems[1].Text.TrimEnd() + "'";
command.CommandTimeout = 10000;
if (conn.State == 0)
{
if (connPassword.Trim() == "")
{
conn.Open();
}
else
{
conn.Open(ExchequerCommonSQLConnection, "", connPassword.Trim(),
(int)ADODB.ConnectModeEnum.adModeUnknown);
}
}
conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
command.ActiveConnection = conn;
ADODB.Recordset rs = null;
Object objAff;
try
{
rs = (ADODB.Recordset)command.Execute(out objAff, Type.Missing, (int)ADODB.CommandTypeEnum.adCmdText);
}
catch
{
throw;
}
{
if (Convert.ToInt32(rs.Fields["RCount"].Value) == 0)
{
// Posting Enabled
tsCheckStatus.Text = "Posting Enabled: " + lvItem.SubItems[1].Text.TrimEnd();
Thread.Sleep(1000);
Application.DoEvents();
PostingEnabledDisabled(true, lvItem.SubItems[1].Text.TrimEnd());
lvItem.SubItems[4].Text = "Enabled";
}
else
{
// Posting Disabled
tsCheckStatus.Text = "Posting Disabled: " + lvItem.SubItems[1].Text.TrimEnd();
Thread.Sleep(1000);
Application.DoEvents();
PostingEnabledDisabled(false, lvItem.SubItems[1].Text.TrimEnd());
lvItem.SubItems[4].Text = "Disabled";
}
}
if (conn.State == 1)
{
conn.Close();
}
}
tsCheckStatus.Text = "Check Complete";
Thread.Sleep(1000);
Application.DoEvents();
}
private void lvSummaryResults_DoubleClick(object sender, EventArgs e)
{
lvDetailedResults.Items.Clear();
//SS:01/03/2018:2018-R1:ABSEXCH-19796: When Running the ExchDVT.exe, SQL Admin Passwords are visible in dump file.
ADODB.Connection conn = new ADODB.Connection();
ADODB.Command cmd = new ADODB.Command();
cmd.CommandText = "SELECT [IntegrityErrorMessage] " +
", [SchemaName] " +
", [TableName] " +
", [PositionID] " +
"FROM [common].[SQLDataValidation] " +
"WHERE IntegrityErrorCode = '" + lvSummaryResults.SelectedItems[0].Text + "' " +
"AND SchemaName = '" + lvSummaryResults.SelectedItems[0].SubItems[3].Text + "' " +
"ORDER BY PositionID";
cmd.CommandTimeout = 10000;
if (conn.State == 0)
{
if (connPassword.Trim() == "")
{
conn.Open();
}
else
{
conn.Open(ExchequerCommonSQLConnection, "", connPassword.Trim(),
(int)ADODB.ConnectModeEnum.adModeUnknown);
}
}
conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
cmd.CommandType = ADODB.CommandTypeEnum.adCmdText;
cmd.ActiveConnection = conn;
ADODB.Recordset recordSet = null;
object objRecAff;
try
{
recordSet = (ADODB.Recordset)cmd.Execute(out objRecAff, Type.Missing, (int)ADODB.CommandTypeEnum.adCmdText);
}
catch
{
throw;
}
for (int i = 0; i < recordSet.RecordCount; i++)
{
ListViewItem lvItem = new ListViewItem(recordSet.Fields[0].Value);
lvItem.SubItems.Add(recordSet.Fields[1].Value);
lvItem.SubItems.Add(recordSet.Fields[2].Value);
lvItem.SubItems.Add(recordSet.Fields[3].Value.ToString());
lvDetailedResults.Items.Add(lvItem);
recordSet.MoveNext();
}
if (conn.State == 1)
{
conn.Close();
}
lvDetailedResults.Visible = true;
btnReturnToSummary.Visible = true;
lvSummaryResults.Visible = false;
}
/// <summary>
/// Inserts a record into the DEIS_TRANSACTION table.
/// </summary>
/// <returns>Boolean indicating status</returns>
private bool InsertTransaction()
{
bool returnValue = false;
try
{
ADODB.Command cmd = new ADODB.Command();
cmd.CommandText = "insert into deis_transaction (tran_code,tran_date,bank_fln,wr_no,serv_area,yard,user_id,conn_sec,existing_bank,wiring_config_code," +
"locn,ins_trf1_company_no,ins_trf1_tsn,ins_trf1_kva_size,ins_trf1_phs_code,ins_trf1_type_code,ins_trf1_mount_code,ins_trf1_kind_code," +
"ins_trf1_pri_volt_code,ins_trf1_sec_volt_code,ins_trf2_company_no,ins_trf2_tsn,ins_trf2_kva_size,ins_trf2_phs_code,ins_trf2_type_code," +
"ins_trf2_mount_code,ins_trf2_kind_code,ins_trf2_pri_volt_code,ins_trf2_sec_volt_code,ins_trf3_company_no,ins_trf3_tsn,ins_trf3_kva_size," +
"ins_trf3_phs_code,ins_trf3_type_code,ins_trf3_mount_code,ins_trf3_kind_code,ins_trf3_pri_volt_code,ins_trf3_sec_volt_code,rem_trf1_company_no," +
"rem_trf1_tsn,rem_trf1_kva_size,rem_trf1_phs_code,rem_trf1_type_code,rem_trf1_mount_code,rem_trf1_kind_code,rem_trf1_pri_volt_code," +
"rem_trf1_sec_volt_code,rem_trf2_company_no,rem_trf2_tsn,rem_trf2_kva_size,rem_trf2_phs_code,rem_trf2_type_code,rem_trf2_mount_code," +
"rem_trf2_kind_code,rem_trf2_pri_volt_code,rem_trf2_sec_volt_code,rem_trf3_company_no,rem_trf3_tsn,rem_trf3_kva_size,rem_trf3_phs_code," +
"rem_trf3_type_code,rem_trf3_mount_code,rem_trf3_kind_code,rem_trf3_pri_volt_code,rem_trf3_sec_volt_code) " +
"values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) " +
"returning tran_no into ?";
cmd.CommandType = CommandTypeEnum.adCmdText;
// Input parameters
ADODB.Parameter param = cmd.CreateParameter("TranCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 1, TransactionDEIS.TransactionCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("TranDate", DataTypeEnum.adDate, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.TransactionDate.Replace('T', ' '));
cmd.Parameters.Append(param);
param = cmd.CreateParameter("BankFLN", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.BankFLN);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("WrNo", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.WrNumber);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("ServArea", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.ServiceArea);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("Yard", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.Yard);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("UserID", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.UserID);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("SecConn", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.SecondaryConnection);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("ExistingBank", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.ExistingBank);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("WiringConfigCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.WiringConfigCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("Location", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.Location);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("InsTrf1CompanyNumber", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf1CompanyNumber);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("InsTrf1TSN", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf1TSN);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("InsTrf1KvaSize", DataTypeEnum.adDouble, ParameterDirectionEnum.adParamInput, 10, TransactionDEIS.InsTrf1KvaSize);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("InsTrf1PhaseCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf1PhaseCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("InsTrf1TypeCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf1TypeCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("InsTrf1MountCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf1MountCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("InsTrf1KindCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf1KindCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("InsTrf1PriVoltCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf1PriVoltCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("InsTrf1SecVoltCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf1SecVoltCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("InsTrf2CompanyNumber", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf2CompanyNumber);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("InsTrf2TSN", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf2TSN);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("InsTrf2KvaSize", DataTypeEnum.adDouble, ParameterDirectionEnum.adParamInput, 10, TransactionDEIS.InsTrf2KvaSize);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("InsTrf2PhaseCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf2PhaseCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("InsTrf2TypeCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf2TypeCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("InsTrf2MountCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf2MountCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("InsTrf2KindCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf2KindCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("InsTrf2PriVoltCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf2PriVoltCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("InsTrf2SecVoltCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf2SecVoltCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("InsTrf3CompanyNumber", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf3CompanyNumber);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("InsTrf3TSN", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf3TSN);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("InsTrf3KvaSize", DataTypeEnum.adDouble, ParameterDirectionEnum.adParamInput, 10, TransactionDEIS.InsTrf3KvaSize);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("InsTrf3PhaseCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf3PhaseCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("InsTrf3TypeCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf3TypeCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("InsTrf3MountCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf3MountCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("InsTrf3KindCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf3KindCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("InsTrf3PriVoltCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf3PriVoltCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("InsTrf3SecVoltCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf3SecVoltCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("RemTrf1CompanyNumber", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf1CompanyNumber);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("RemTrf1TSN", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf1TSN);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("RemTrf1KvaSize", DataTypeEnum.adDouble, ParameterDirectionEnum.adParamInput, 10, TransactionDEIS.RemTrf1KvaSize);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("RemTrf1PhaseCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf1PhaseCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("RemTrf1TypeCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf1TypeCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("RemTrf1MountCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf1MountCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("RemTrf1KindCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf1KindCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("RemTrf1PriVoltCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf1PriVoltCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("RemTrf1SecVoltCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf1SecVoltCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("RemTrf2CompanyNumber", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf2CompanyNumber);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("RemTrf2TSN", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf2TSN);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("RemTrf2KvaSize", DataTypeEnum.adDouble, ParameterDirectionEnum.adParamInput, 10, TransactionDEIS.RemTrf2KvaSize);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("RemTrf2PhaseCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf2PhaseCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("RemTrf2TypeCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf2TypeCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("RemTrf2MountCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf2MountCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("RemTrf2KindCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf2KindCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("RemTrf2PriVoltCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf2PriVoltCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("RemTrf2SecVoltCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf2SecVoltCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("RemTrf3CompanyNumber", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf3CompanyNumber);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("RemTrf3TSN", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf3TSN);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("RemTrf3KvaSize", DataTypeEnum.adDouble, ParameterDirectionEnum.adParamInput, 10, TransactionDEIS.RemTrf3KvaSize);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("RemTrf3PhaseCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf3PhaseCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("RemTrf3TypeCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf3TypeCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("RemTrf3MountCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf3MountCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("RemTrf3KindCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf3KindCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("RemTrf3PriVoltCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf3PriVoltCode);
cmd.Parameters.Append(param);
param = cmd.CreateParameter("RemTrf3SecVoltCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf3SecVoltCode);
cmd.Parameters.Append(param);
// Output parameter
param = cmd.CreateParameter("TranNo", DataTypeEnum.adBigInt, ParameterDirectionEnum.adParamOutput, 10);
cmd.Parameters.Append(param);
int recordsAffected = 0;
Recordset spRS = m_Application.DataContext.ExecuteCommand(cmd, out recordsAffected);
m_Application.DataContext.Execute("commit", out recordsAffected, (int)CommandTypeEnum.adCmdText);
if (!Convert.IsDBNull(cmd.Parameters["TranNo"].Value))
{
TransactionDEIS.TransactionNumber = Convert.ToInt32(cmd.Parameters["TranNo"].Value);
}
returnValue = true;
}
catch (Exception ex)
{
TransactionDEIS.TransactionMessage = "FAILED - Error inserting record into " + TransactionDEIS.TABLE_DEIS_TRANSACTION + ": " + ex.Message;
returnValue = false;
}
return(returnValue);
}
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);
}
}
private void _InitSqlLst()
{
//
if (queryLst != null)
return;
if (con == null)
throw new InvalidOperationException();
var querylst = new List<string>();
string s;
var cat = new ADOX.Catalog();
var cmd = new ADODB.Command();
queryLst = new List<Query>();
//アクションクエリ
var rs = con.OpenSchema(ADODB.SchemaEnum.adSchemaProcedures);
while (!rs.EOF)
{
s = rs.Fields["PROCEDURE_NAME"].Value.ToString();
rs.MoveNext();
if (!s.StartsWith("~"))
querylst.Add(s);
}
cat.ActiveConnection = con;
foreach (var q in querylst)
{
//
cmd = (ADODB.Command)cat.Procedures[q].Command;
var sql = cmd.CommandText.ToUpper();
//var name = cmd.Name;
var name = q;
if (!(sql.StartsWith("UPDATE") || sql.StartsWith("INSERT")))
return;
var query = new Query(name, sql);
queryLst.Add(query);
//if (sql.StartsWith("UPDATE"))
// updateSqlLst.Add(sql);
//if (sql.StartsWith("INSERT"))
// insertSqlLst.Add(sql);
}
}
private void InitializeCustomerSettings(ADODB.Connection mycn)
{
var myInsert =
@"INSERT INTO tblCustomerSettings
( CustomerID,
BillingMethodID,
MobileCarrierID
)
SELECT
tblCustomers.CustomerID,
0 AS bmID,
0 AS mcID
FROM tblCustomers;";
var myCmd = new ADODB.Command {ActiveConnection = mycn, CommandText = myInsert};
myCmd.CommandType = CommandTypeEnum.adCmdText;
object dummy = Type.Missing;
ADODB.Recordset rs = myCmd.Execute(out dummy, ref dummy,0);
}
private void UpdateDBVersion(ADODB.Connection mycn)
{
var myUpdate = @"Update tblCaptions Set mdbVersion = '1.01.09'";
var myCmd = new ADODB.Command { ActiveConnection = mycn, CommandText = myUpdate };
myCmd.CommandType = CommandTypeEnum.adCmdText;
object dummy = Type.Missing;
ADODB.Recordset rs = myCmd.Execute(out dummy, ref dummy, 0);
}
/// <summary>
///
/// </summary>
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;
Excel.ListObject tbl;
int iCols = 0;
string msg = String.Empty;
string ldapQry = Properties.Settings.Default.Rdg_LdapQry;
try
{
cn.Open("Provider=ADsDSOObject;");
ldapQry = ldapQry.Replace("[Rdg.LdapPath]", Properties.Settings.Default.Rdg_LdapPath);
cmd.CommandText = ldapQry;
cmd.ActiveConnection = cn;
object objRecAff = null;
object objParameters = null;
rs = cmd.Execute(out objRecAff, ref objParameters, (int)ADODB.CommandTypeEnum.adCmdText);
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
// 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 = Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets(My.Settings.Rdg_SheetName)
//End If
//Globals.ThisAddIn.Application.Sheets(My.Settings.Rdg_SheetName).Activate
//Ribbon.ClearSheetContents()
//For iCols = 0 To rs.Fields.Count - 1
// ws.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
//Next
//ws.Range(ws.Cells(1, 1), ws.Cells(1, rs.Fields.Count)).Font.Bold = True
//ws.Range("A2").CopyFromRecordset(rs)
//Ribbon.CreateTableFromRange()
//Ribbon.UpdateBlankCells()
//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.InvalidateRibbon() 'reset dropdown lists
//Ribbon.ActivateTab()
}
catch (Exception ex)
{
ErrorHandler.DisplayMessage(ex);
}
}
private void CheckCompany()
{
this.UseWaitCursor = true;
clsCoreChecks CoreChecks = new clsCoreChecks();
clsTransactionHeaderChecks TransactionHeaderChecks = new clsTransactionHeaderChecks();
clsTransactionLineChecks TransactionLineChecks = new clsTransactionLineChecks();
clsTransactionLineJobCostingChecks TransactionLineJobCostingChecks = new clsTransactionLineJobCostingChecks();
clsTransactionLineStockCheck TransactionLineStockChecks = new clsTransactionLineStockCheck();
clsHistoryChecks HistoryChecks = new clsHistoryChecks();
clsHistoryCalculations HistoryCalculations = new clsHistoryCalculations();
//SS:01/03/2018:2018-R1:ABSEXCH-19796: When Running the ExchDVT.exe, SQL Admin Passwords are visible in dump file.
ADODB.Connection conn = new ADODB.Connection();
ADODB.Command cmd = new ADODB.Command();
cmd.CommandText = "IF EXISTS (SELECT * FROM sys.tables WHERE name LIKE 'SQLDataValidation%') DROP TABLE common.SQLDataValidation; " +
"CREATE TABLE common.SQLDataValidation(IntegrityErrorNo varchar(50), " +
"IntegrityErrorCode varchar(50), " +
"Severity varchar(50), " +
"IntegrityErrorMessage varchar(max), " +
"IntegritySummaryDescription varchar(max), " +
"SchemaName varchar(10), " +
"TableName varchar(100), " +
"PositionId int);";
cmd.CommandTimeout = 10000;
if (conn.State == 0)
{
if (connPassword.Trim() == "")
{
conn.Open();
}
else
{
conn.Open(ExchequerCommonSQLConnection, "", connPassword.Trim(),
(int)ConnectModeEnum.adModeUnknown);
}
}
conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
try
{
Object recAff;
cmd.ActiveConnection = conn;
cmd.CommandType = ADODB.CommandTypeEnum.adCmdText;
cmd.Execute(out recAff, Type.Missing, (int)ADODB.CommandTypeEnum.adCmdText);
Console.WriteLine("Table Created Successfully...");
if (conn.State == 1)
{
conn.Close();
}
}
catch
{
throw;
}
// Clear Old Results for Company
cmd = new ADODB.Command();
cmd.CommandText = "DELETE common.SQLDataValidation WHERE SchemaName = '" + CompanyCode + "'";
cmd.CommandTimeout = 10000;
if (conn.State == 0)
{
if (connPassword.Trim() == "")
{
conn.Open();
}
else
{
conn.Open(ExchequerCommonSQLConnection, "", connPassword.Trim(),
(int)ConnectModeEnum.adModeUnknown);
}
}
try
{
cmd.ActiveConnection = conn;
Object recAff;
cmd.CommandType = ADODB.CommandTypeEnum.adCmdText;
cmd.Execute(out recAff, Type.Missing, (int)ADODB.CommandTypeEnum.adCmdText);
Console.WriteLine("Old Results Deleted Successfully...");
if (conn.State == 1)
{
conn.Close();
}
}
catch
{
throw;
}
tsProgressBar.Value = 0;
tsProgressBar.Maximum = 26;
string Company = CompanyCode;
IncrementToolbar();
tsProgressBar.Refresh();
lblStatus.Text = "Checking Currencies";
// Run Core Checks
// Check for inverted currencies if system either Euro or Multi-Currency
if (MultiCurrency == true)
{
CoreChecks.CheckInvertedCurrencies(ExchequerCommonSQLConnection, Company, connPassword);
}
IncrementToolbar();
lblStatus.Text = "Checking Transaction Headers";
// Run Transaction Header Checks
// Check for Transaction Headers with Zero Folio Number
TransactionHeaderChecks.TransactionZeroFolioNum(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check if Transaction Header Trader Codes Exist
TransactionHeaderChecks.TransactionTraderCodesExist(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check Control GL codes exist if they are set
TransactionHeaderChecks.TransactionCheckControlGLCodesExistIfSet(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check Control GL Codes are not Headers
TransactionHeaderChecks.TransactionCheckControlGLNotHeaders(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check Currency Codes exist
TransactionHeaderChecks.TransactionCheckCurrencyCodeExists(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
lblStatus.Text = "Checking Transaction Lines";
// Run Transaction Line Checks
// Check Transaction Header exists for non RUN transaction lines
TransactionLineChecks.TransactionLineExistsWithNoTransactionHeader(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check if Transaction Lines exist with invalid GL Code
TransactionLineChecks.TransactionLineExistWithInvalidGLCode(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check if Transaction Lines exist with Header GL Code
TransactionLineChecks.TransactionLineExistWithHeaderGLCode(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check if Transction Lines exist with invalid Trader Code
TransactionLineChecks.TransactionLineExistInvalidTraderCode(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check if Transaction Line Trader Code is same as Transaction Header Trader Code
TransactionLineChecks.TransactionLineTraderCodeMatchesHeader(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check Transaction Line Currency Codes Exist
TransactionLineChecks.TransactionLineCurrencyCodesExist(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check Transaction Line VAT Code Exists
TransactionLineChecks.TransactionLineVATCodeExists(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check Transaction Line Inclusive VAT Code Exists
TransactionLineChecks.TransactionLineInclusiveVATCodeExists(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check Transaction Line Period Matches Header Period
TransactionLineChecks.TransactionLinePeriodMatchesHeaderPeriod(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
//// Check History Currency Codes Exist
//HistoryChecks.HistoryCheckCurrencyCodesExist(ExchequerCommonSQLConnection, Company);
//IncrementToolbar();
//// Check History General Ledger Codes Exist
//HistoryChecks.HistoryCheckGLCodesExist(ExchequerCommonSQLConnection, Company);
//IncrementToolbar();
// Check if Cost Centres/Departments are used
if (CCDept == true)
{
lblStatus.Text = "Checking Cost Centres and Departments";
// Check Transaction Line Cost Centre Codes Exist
TransactionLineChecks.TransactionLineCostCentresExist(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check Transaction Line Department Codes Exist
TransactionLineChecks.TransactionLineDepartmentsExist(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
//// Check History Cost Centre Codes Exist
//HistoryChecks.HistoryCheckCostCentreCodesExist(ExchequerCommonSQLConnection, Company);
//IncrementToolbar();
//// Check History Department Codes Exist
//HistoryChecks.HistoryCheckDepartmentCodesExist(ExchequerCommonSQLConnection, Company);
//IncrementToolbar();
}
else
{
IncrementToolbar();
IncrementToolbar();
}
// Check if Job Costing enabled
if (JobCosting == true)
{
lblStatus.Text = "Checking Job Costing";
// Check Employee Codes on Header exist
TransactionHeaderChecks.TransactionCheckEmployeeCodesExist(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check Transaction Line Job Exists
TransactionLineJobCostingChecks.TransactionLineJobExist(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check Transaction Line Job check not Contract
TransactionLineJobCostingChecks.TransactionLineCheckJobNotContract(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check Transaction Line Analysis Code Exists
TransactionLineJobCostingChecks.TransactionLineCheckAnalysisCodeExists(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
//// Check Employee Currency History Codes Exist
//HistoryChecks.HistoryCheckEmployeeCurrencyCodesExist(ExchequerCommonSQLConnection, Company);
//IncrementToolbar();
//// Check Employee History Codes Exist
//HistoryChecks.HistoryCheckEmployeeCodesExist(ExchequerCommonSQLConnection, Company);
//IncrementToolbar();
//// Check Job Currency History Codes Exist
//HistoryChecks.HistoryCheckJobCurrencyExist(ExchequerCommonSQLConnection, Company);
//IncrementToolbar();
//// Check Job History Codes Exist
//HistoryChecks.HistoryCheckJobExist(ExchequerCommonSQLConnection, Company);
//IncrementToolbar();
//// Check Analysis Id History Codes Exist
//HistoryChecks.HistoryCheckAnalysisIdExist(ExchequerCommonSQLConnection, Company);
//IncrementToolbar();
}
else
{
IncrementToolbar();
IncrementToolbar();
IncrementToolbar();
IncrementToolbar();
}
// Check if Stock enabled
if (StockModule == true)
{
lblStatus.Text = "Checking Stock Records";
// Check Transaction Line Stock Code Exists
TransactionLineStockChecks.TransactionLineStockCodeExists(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check Transaction Line Stock Code not a Group
TransactionLineStockChecks.TransactionLineStockCodeNotGroup(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
//// Check Stock History Stock Codes Exists
//HistoryChecks.HistoryCheckStockExist(ExchequerCommonSQLConnection, Company);
//IncrementToolbar();
// Check if Locations is enabled
if (Locations == true)
{
lblStatus.Text = "Checking Locations";
// Check if Transaction Line Location Codes exist
TransactionLineStockChecks.TransactionLineLocationCodeDoesNotExist(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
//// Check Stock History Location Codes Exists
//HistoryChecks.HistoryCheckLocationExist(ExchequerCommonSQLConnection, Company);
//IncrementToolbar();
//// Check Stock History Stock Location Codes Exists
//HistoryChecks.HistoryCheckStockLocationExist(ExchequerCommonSQLConnection, Company);
//IncrementToolbar();
}
else
{
IncrementToolbar();
}
}
else
{
IncrementToolbar();
IncrementToolbar();
IncrementToolbar();
}
lblStatus.Text = "Checking Profit and Loss Brought Forward";
// Check Profit and Loss Brought Forward
HistoryCalculations.ProfitAndLossBroughtForward(ExchequerCommonSQLConnection, Company, CommitmentAccounting, connPassword);
IncrementToolbar();
this.UseWaitCursor = false;
lblStatus.Text = "Checking Completed";
tsProgressBar.Value = tsProgressBar.Maximum;
Thread.Sleep(100);
Application.DoEvents();
// Display Summary Results in UI
DisplayResults();
}
protected override SQLCommand InternalCreateCommand()
{
ADODB.Command command = new ADODB.Command();
command.ActiveConnection = _connection;
return(new ADOCommand(this, command));
}