/// <summary>
/// report_file_path,
/// </summary>
public void update()
{
string sqlStr = string.Format(@"UPDATE Report_Dealwith_Status
SET
sheet_visual_counts = {3},
max_row_index ={4},
max_col_index = {5},
current_row_index ={6},
current_col_index = {7},
status_description = '{8}',
status_flag = {9},
update_time = sysdate
WHERE file_name = '{0}'
AND Sheet_Index = {1}
AND Sheet_Name = '{2}'",
this._file_name,
this._sheet_index,
this._sheet_name,
this.Sheet_visual_counts,
this._max_row_index,
this._max_col_index,
this._current_row_Index,
this._current_col_index,
this._status_description,
this._status_flag?1:0);
OracleDaoHelper.executeSQL(sqlStr);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public bool ifExists()
{
string sqlStr = string.Format(@"SELECT 1 FROM RealTime_Message WHERE Name_Of_The_Handler = '{0}'", _nameOfTheHandler);
DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
return(dt.Rows.Count > 0 ? true : false);
}
public static DataTable getTop10MsgByProductsName(string name_of_the_Handler)
{
string sqlStr = string.Format(@"select row_num as ""序号"",
name_of_the_Handler as ""处理程序名称"",
prompt AS ""提示"",
case flag
when 1 then '成功'
else '失败'
end AS ""状态"",
update_time as ""更新时间"",
finish_flag as ""结束标识""
from(
select row_number() over(order by update_time desc) row_num,
name_of_the_Handler,
prompt,
flag,
update_time,
finish_flag
from FINISH_MESSAGE
where products_name = '{0}'
) Temp
where Temp.row_num <= 10", name_of_the_Handler);
return(OracleDaoHelper.getDTBySql(sqlStr));
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public bool ifExists()
{
string sqlStr = string.Format(@"SELECT 1 FROM FINISH_MESSAGE WHERE name_of_the_handler = '{0}' AND random_Str = '{1}'", this.NameOftheHandler, this.RandomStr);
DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
return(dt.Rows.Count != 0 ? true : false);
}
/// <summary>
/// 使Socket_Mess的 task_status 为 finished.
/// </summary>
public void closeTheSocketMess()
{
string sqlStr = string.Format(@"UPDATE SOCKET_MESS
SET TASK_STATUS = 'finished'
WHERE PROCESS_NAME = '{0}'",
this._process_Name);
OracleDaoHelper.executeSQL(sqlStr);
}
/// <summary>
/// 删除掉此报表的处理记录。
/// </summary>
public void del()
{
string sqlStr = string.Format(@"DELETE FROM Report_Dealwith_status
WHERE File_Name = '{0}'
AND Sheet_Name = '{1}'",
this._file_name,
this._sheet_name);
OracleDaoHelper.executeSQL(sqlStr);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public bool ifExistsTheProcess()
{
string sqlStr = string.Format(@"select
1
FROM Socket_Mess
WHERE process_name = '{0}'",
this._process_Name);
return(OracleDaoHelper.getDTBySql(sqlStr).Rows.Count > 0 ? true : false);
}
/// <summary>
/// 判断某文件中的索引为: sheet_Index，名称为: sheet_Name 的表格是否存在记录。
/// </summary>
/// <returns></returns>
public bool ifExists()
{
string sqlStr = string.Format(@"SELECT 1
FROM Report_DealWith_Status
WHERE FILE_NAME = '{0}'
AND Sheet_Name = '{1}'",
this._file_name,
this._sheet_name);
return(OracleDaoHelper.getDTBySql(sqlStr).Rows.Count > 0 ? true : false);
}
/// <summary>
///
/// </summary>
/// <param name="productName"></param>
/// <returns></returns>
public static string getVersion(string productName)
{
string sqlStr = string.Format(@"select Release_Version
from Application_Availability
where product_name = '{0}'", productName);
DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
if (dt.Rows.Count == 0)
{
return("");
}
return(dt.Rows[0]["Release_Version"].ToString());
}
/// <summary>
///
/// </summary>
/// <param name="applicationName"></param>
/// <returns></returns>
public static ProgressInfo getProgressInfo(string applicationName)
{
string sqlStr = string.Format(@"SELECT max_value,
current_value,
name_of_the_handler,
update_time,
finish_flag,
msg
FROM Progress_Info
WHERE Name_of_the_handler = '{0}'
", applicationName);
DataTable dt = OracleDaoHelper.getDTBySql(sqlStr);
return(ConvertHelper <ProgressInfo> .ConvertToObj(dt));
}
public void add()
{
string sqlStr = string.Format(@"INSERT INTO Progress_Info(current_value,max_value,name_of_the_handler,finish_flag)
VALUES(
{0},
{1},
'{2}',
{3}
)",
this._current_value,
this._max_value,
this.Name_of_the_handler,
this._finish_flag ? 1 : 0);
OracleDaoHelper.executeSQL(sqlStr);
}
private MSG isValid_TheLineName_Or_TheTeamName(string teamName, string lineName)
{
string sqlStr = string.Format(@"SELECT 1 FROM Line_Info where line_name = '{0}'", lineName);
MSG msg = new MSG();
msg.Flag = OracleDaoHelper.getDTBySql(sqlStr).Rows.Count > 0 ? true : false;
msg.Msg = msg.Flag ? "" :"此线体名： " + lineName + " 在Line_Info表中不存在";
if (!msg.Flag)
{
return(msg);
}
//线体检查通过
sqlStr = string.Format(@"SELECT 1 FROM Team_Info where team_name = '{0}'", teamName);
msg.Flag = OracleDaoHelper.getDTBySql(sqlStr).Rows.Count > 0 ? true : false;
msg.Msg = msg.Flag ? "" : "此班组名： " + teamName + " 在team_name表中不存在";
return(msg);
}
public void update()
{
string sqlStr = string.Format(@"UPDATE Progress_Info
SET current_value = {1},
max_value = {2},
finish_flag = {3},
msg= '{4}'
WHERE name_of_the_handler = '{0}'
",
this.Name_of_the_handler,
this._current_value,
this._max_value,
this._finish_flag?1:0,
this._msg);
OracleDaoHelper.executeSQL(sqlStr);
}
/// <summary>
/// 新增时是否为processing.
/// </summary>
/// <returns></returns>
public int addTheSocketMess()
{
string sqlStr = string.Format(@"INSERT INTO Socket_Mess
(
PROCESS_NAME,
TASK_STATUS
)
VALUES(
'{0}',
'{1}'
)",
this._process_Name,
this._task_status
);
return(OracleDaoHelper.executeSQL(sqlStr));
}
/// <summary>
/// 获取最新的一条信息。
/// </summary>
/// <returns></returns>
public static DataTable getLastestMsg()
{
string sqlStr = string.Format(@"SELECT Prompt,
Flag
FROM
(
select row_number() over(order by update_time desc) row_id,
name_of_the_Handler,
prompt,
flag,
finish_flag,
update_time
from FINISH_MESSAGE
) TEMP
where row_id = 1");
return(OracleDaoHelper.getDTBySql(sqlStr));
}
/// <summary>
/// 获取当天的所有处理结果.
/// </summary>
/// <returns></returns>
public static DataTable getAllReportDealWithStatusTheDay()
{
string sqlStr = string.Format(@"SELECT file_name AS ""文件名"",
sheet_visual_counts AS ""表格数"",
sheet_index AS ""索引"",
sheet_name AS ""表格名"",
current_row_index AS ""当前行"",
status_description AS ""进度"",
CASE status_flag
WHEN 1 THEN N'正常'
ELSE N'异常'
END ""状态"",
update_time AS ""更新时间""
FROM Report_Dealwith_Status
WHERE TRUNC(update_Time,'DD') = TRUNC(SYSDATE, 'DD)
ORDER BY sheet_Index ASC");
return(OracleDaoHelper.getDTBySql(sqlStr));
}
/// <summary>
/// 新增 记录
/// </summary>
public void add()
{
//如果存在，先删除
if (ifExists())
{
del();
}
string sqlStr = string.Format(@"INSERT INTO Report_Dealwith_Status(
file_name,
sheet_visual_counts,
sheet_index,
sheet_name,
max_row_index,
max_col_index,
current_row_index,
status_description,
status_flag,
update_time
)VALUES(
'{0}', --<报表名称>--
{1}, --<电子表格个数>
{2}, --<当前表格索引>
'{3}', --<表格名称>
{4}, --<最大行>
{5}, --<最大列>
{6}, --<当前行>
'{7}', --<状态描述>
{8}, --<状态标志位>
sysdate
)", this._file_name,
this.Sheet_visual_counts,
this._sheet_index,
this._sheet_name,
this._max_row_index,
this._max_col_index,
this._current_row_Index,
this._status_description,
this._status_flag?1:0);
OracleDaoHelper.executeSQL(sqlStr);
}
/// <summary>
///
/// </summary>
/// <param name="nameOfTheHandler"></param>
/// <returns></returns>
public static DataTable getLastestMsgByHandlerName(string nameOfTheHandler)
{
string sqlStr = string.Format(@"SELECT Prompt,
Flag,
Finish_Flag
FROM
(
select row_number() over(order by update_time desc) row_id,
name_of_the_Handler,
prompt,
flag,
finish_flag,
update_time
from FINISH_MESSAGE
) TEMP
WHERE name_Of_The_Handler = '{0}'
and row_id = 1", nameOfTheHandler);
return(OracleDaoHelper.getDTBySql(sqlStr));
}
/// <summary>
/// 获取此报表下的所有DataTable
/// </summary>
/// <returns></returns>
public DataTable getDTOfTheReport()
{
string sqlStr = string.Format(@"SELECT report_file_path AS ""文件名"",
sheet_visual_counts AS ""表格数"",
sheet_index AS ""索引"",
sheet_name AS ""表格名"",
current_row_index AS ""当前行"",
status_description AS ""进度"",
CASE status_flag
WHEN 1 THEN N'正常'
ELSE N'异常'
END ""状态"",
update_time AS ""更新时间""
FROM Report_Dealwith_Status
WHERE file_name = '{0}'
ORDER BY sheet_Index ASC",
this._file_name);
return(OracleDaoHelper.getDTBySql(sqlStr));
}
/// <summary>
/// OracleDaoHelper,SqlDaoHelper 数据库的连接字符串赋值
/// </summary>
/// <returns></returns>
public static bool setTheValueOfTheConnStr(out bool flag_open_mesSqlConn)
{
#region 数据库联接测试。
string host_Name = XmlFlexflow.ReadXmlNodeValue("SERVER_NAME");
string service_Name = XmlFlexflow.ReadXmlNodeValue("SERVICE_NAME");
string user_Id = XmlFlexflow.ReadXmlNodeValue("USER_ID");
string password = XmlFlexflow.ReadXmlNodeValue("PASSWORD");
string mes_host_Name = XmlFlexflow.ReadXmlNodeValue("MES_SERVER_NAME");
string mes_db_Name = XmlFlexflow.ReadXmlNodeValue("MES_DATABASE_NAME");
string mes_user_Id = XmlFlexflow.ReadXmlNodeValue("MES_USER_ID");
string mes_password = XmlFlexflow.ReadXmlNodeValue("MES_PASSWORD");
XmlFlexflow.configFilePath = System.Windows.Forms.Application.StartupPath + "\\flexflow.cfg";
//先测试是否可以ping通
if (!ConnectByPing.pingTheAddress(mes_host_Name))
{
MessageBox.Show("与" + host_Name + " 连接失败!", "提示: ", MessageBoxButtons.OK, MessageBoxIcon.Information);
flag_open_mesSqlConn = false;
return(false);
}
#endregion
//再这个启动类里，对对象进行初始化。
OracleDaoHelper daoHelper = new OracleDaoHelper(host_Name, service_Name, user_Id, password);
SqlDaoHelper sqlHelper = new SqlDaoHelper(mes_host_Name, mes_db_Name, mes_user_Id, mes_password);
SqlConnection sqlConn = new SqlConnection(SqlDaoHelper.conn_str);
try
{
sqlConn.Open();
sqlConn.Close();
sqlConn.Dispose();
flag_open_mesSqlConn = true;
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
MessageBox.Show("基于MES_制卡系统中的所属部门，组将无法获取");
flag_open_mesSqlConn = false;
return(false);
}
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public Socket_Mess getCurrentStatus()
{
string sqlStr = string.Format(@"select
PROCESS_NAME,
RESULT,
FLAG,
STATUS,
Task_Status,
CURRENT_VALUE,
TOTAL_VALUE
FROM Socket_Mess
WHERE process_name = '{0}'",
this._process_Name);
List <Socket_Mess> socketMessList = DT_To_List.TableToList <Socket_Mess>(OracleDaoHelper.getDTBySql(sqlStr));
if (socketMessList.Count == 0)
{
return(null);
}
return(socketMessList[0]);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public int updateTheSocketMess()
{
string sqlStr = string.Format(@"UPDATE Socket_MESS
SET Result = '{1}',
FLAG = {2},
STATUS = '{3}',
CURRENT_VALUE = {4},
TOTAL_VALUE = {5},
Task_Status = '{6}',
Update_Time = sysdate
WHERE Process_Name = '{0}'",
this._process_Name,
this._result,
this._flag,
this._status,
this._current_value,
this._total_value,
this._task_status
);
return(OracleDaoHelper.executeSQL(sqlStr));
}
/// <summary>
///
/// </summary>
public void update()
{
string sqlStr = string.Format(@"UPDATE FINISH_MESSAGE SET Prompt = '{2}',Flag = {3},finish_flag = {4} WHERE name_of_the_handler = '{0}' And Random_Str = '{1}'", this.NameOftheHandler, this.RandomStr, this._prompt, this.Flag ? 1:0, this.Finish_flag ? 1:0);
OracleDaoHelper.executeSQL(sqlStr);
}
/// <summary>
/// 依据PN 和 randomStr 获取 Message
/// </summary>
/// <param name="pN"></param>
/// <param name="randomStr"></param>
/// <returns></returns>
public static DataTable getMsgByNameOfTheHandlerAndRandomStr(string pN, string randomStr)
{
string sqlStr = string.Format(@"SELECT prompt,flag,finish_flag FROM FINISH_MESSAGE where name_of_the_Handler= '{0}' and random_str ='{1}'", pN, randomStr);
return(OracleDaoHelper.getDTBySql(sqlStr));
}
/// <summary>
///
/// </summary>
/// <param name="nameOfTheHandler"></param>
public static void clear(string nameOfTheHandler)
{
string sqlStr = string.Format(@"DELETE FROM FINISH_MESSAGE WHERE name_of_the_Handler = '{0}'", nameOfTheHandler);
OracleDaoHelper.executeSQL(sqlStr);
}
public void delete()
{
string sqlStr = string.Format(@"DELETE FROM Progress_Info WHERE Name_of_the_handler = '{0}'", this.Name_of_the_handler);
OracleDaoHelper.executeSQL(sqlStr);
}
public DataTable getProgressInfo()
{
return(OracleDaoHelper.getDTBySql(string.Format(@"SELECT current_value,max_value,finish_flag,msg FROM Progress_Info WHERE Name_of_the_handler = '{0}'", this.Name_of_the_handler)));
}
/// <summary>
/// 删除 Report_Dealwith_status,通过FileName删除.
/// </summary>
/// <param name="fileName"></param>
public static void delStatusByFileName(string fileName)
{
string sqlStr = string.Format(@"DELETE FROM Report_Dealwith_status WHERE File_Name = '{0}'", fileName);
OracleDaoHelper.executeSQL(sqlStr);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public DataTable getRealTimeMessage()
{
string sqlStr = string.Format(@"SELECT msg,flag FROM RealTime_Message WHERE name_Of_The_Handler = '{0}'", this._nameOfTheHandler);
return(OracleDaoHelper.getDTBySql(sqlStr));
}
public bool ifExists()
{
string sqlStr = string.Format(@"SELECT 1 FROM Progress_Info WHERE Name_of_the_handler = '{0}'", Name_of_the_handler);
return(OracleDaoHelper.getDTBySql(sqlStr).Rows.Count > 0 ? true : false);
}