62,046
社区成员
发帖
与我相关
我的任务
分享
public void RunDataPanel()
{
da.SaveLogs("WorkOrderID当前已执行位置:startID=" + startID + "; endID=" + endID);
string strSql = string.Empty;
strSql = "select distinct a.assetnum 资产编号,a.description 设备名称,w.wonum 工单号,w.description 工单描述,w.worktype 工单类型,w.FAILDATE 日期,w.SiteID,w.WO20 ";
strSql += "from workorder w,asset a ";
strSql += "where w.worktype not in ('MR','MQ') and a.siteid = w.siteid and a.assetnum = w.assetnum ";
strSql += "and workorderid>=" + startID + " and workorderid<" + endID + "";
//资产工单信息
DataTable dt = da.RunSelectSQL(strSql);
da.SaveLogs("工单资产信息行数:" + dt.Rows.Count);
//插入SQL语句集合
ArrayList sqlArr = new ArrayList();
//维修记录ID
int CMID = da.GetMaxID("ID", "ENQ_DEVICESERVICE");
//维修更换配件ID
int WXPJID = da.GetMaxID("ID", "ENQ_SERVICEPARTS");
//维修步骤ID
int BZID = da.GetMaxID("ID", "ENQ_SERVICEBZ");
//维保记录ID
int PMID = da.GetMaxID("ID", "ENQ_DEVICEPROTECTED");
//维保更换配件ID
int WBPJID = da.GetMaxID("ID", "ENQ_PROTECTEDPARTS");
//维保内容ID
int NRID = da.GetMaxID("ID", "ENQ_PROTECTEDNR");
foreach (DataRow dr in dt.Rows)
{
#region 基本信息收集
//资产编号
string assetNum = dr["资产编号"].ToString();
//工单号
string woNum = dr["工单号"].ToString();
//故障原因附加
strSql = "select distinct f.CUSTDESCCN 故障现象, f1.CUSTDESCCN 原因分析 ";
strSql += "from workorder w, failurecode f0, extwo01 e, CUSTFAILUREREPORT c, failurecode f, CUSTFAILUREREPORT c2, failurecode f1 ";
strSql += "where w.status in (select value from synonymdomain where domainid = 'WOSTATUS' and maxvalue in ('APPR','COMP','CLOSE')) ";
strSql += "and w.orgid = f0.orgid(+) and w.failurecode = f0.failurecode(+) and w.siteid = e.siteid(+) ";
strSql += "and w.wonum = e.wonum(+) and w.siteid = c.siteid(+) and w.wonum = c.wonum(+) and c.parent(+) is null ";
strSql += "and c.orgid = f.orgid(+) and c.failurecode = f.failurecode(+) and c.siteid = c2.siteid(+) and c.wonum = c2.wonum(+) ";
strSql += "and c.custfailurereporid = c2.parent(+) and c2.orgid = f1.orgid(+) and c2.failurecode = f1.failurecode(+) ";
strSql += "and w.wonum='" + woNum + "' ";
DataTable temp1 = da.RunSelectSQL(strSql);
//故障现象
string strGZXX = string.Empty;
//原因分析
string strYYFX = string.Empty;
if (temp1 != null)
{
foreach (DataRow dr1 in temp1.Rows)
{
strGZXX += dr1["故障现象"] + ",";
strYYFX += dr1["原因分析"] + ",";
}
}
if (strGZXX.Length > 1)
{
strGZXX = strGZXX.Substring(0, strGZXX.Length - 1);
}
if (strYYFX.Length > 1)
{
strYYFX = strYYFX.Substring(0, strYYFX.Length - 1);
}
//使用工具附加
strSql = "select distinct c.description 使用工具 from tooltrans t,tool c where t.orgid = c.orgid and t.itemnum = c.toolnum ";
strSql += "and t.refwo = '" + woNum + "' ";
DataTable temp2 = da.RunSelectSQL(strSql);
//使用工具
string strSYGJ = string.Empty;
if (temp2 != null)
{
foreach (DataRow dr2 in temp2.Rows)
{
strSYGJ += dr2["使用工具"] + ",";
}
}
if (strSYGJ.Length > 1)
{
strSYGJ = strSYGJ.Substring(0, strSYGJ.Length - 1);
}
//维修人员附加
strSql = "select p.displayname 维修人员 from labtrans t,craft c,labor l,person p ";
strSql += "where t.orgid = c.orgid(+) and t.craft = c.craft(+) ";
strSql += "and t.laborcode = l.laborcode(+) and t.orgid = l.orgid(+) and l.personid = p.personid(+) ";
strSql += "and t.refwo = '" + woNum + "' ";
DataTable temp3 = da.RunSelectSQL(strSql);
//维修人员
string strWXRY = string.Empty;
if (temp3 != null)
{
foreach (DataRow dr3 in temp3.Rows)
{
strWXRY += dr3["维修人员"] + ",";
}
}
if (strWXRY.Length > 1)
{
strWXRY = strWXRY.Substring(0, strWXRY.Length - 1);
}
//维修耗时
strSql = "select trunc(((t.actfinish - t.actstart)*24),2) 维修耗时 from workorder t ";
strSql += "where t.wonum = '" + woNum + "' ";
DataTable temp4 = da.RunSelectSQL(strSql);
//维修耗时
string strWXHS = string.Empty;
if (temp4 != null)
{
foreach (DataRow dr4 in temp4.Rows)
{
strWXHS = dr4["维修耗时"].ToString();
}
}
//保养周期
strSql = "select p.pmnum 预防性维护编码,p.FREQUENCY 维护频率,p.FREQUNIT 频率单位,p.description 预防性维护描述 ";
strSql += "from asset t,pm p ";
strSql += "where t.siteid = p.siteid and t.assetnum = p.assetnum and p.status = 'ACTIVE' ";
strSql += "and t.assetnum = '"+assetNum+"' ";
DataTable temp11 = da.RunSelectSQL(strSql);
//保养周期
string strBYZQ = string.Empty;
//维保名称
string strWBMC = string.Empty;
//维保名称
if (temp11 != null)
{
foreach (DataRow dr11 in temp11.Rows)
{
strBYZQ = dr11["维护频率"].ToString() + dr11["频率单位"].ToString();
strWBMC = dr11["预防性维护描述"].ToString();
}
}
//da.SaveLogs("资产编号:" + assetNum + " 工单号:" + woNum + " 故障现象:" + strGZXX + " 原因分析:" + strYYFX + " 使用工具:" + strSYGJ + " 维修人员:" + strWXRY + " 维修耗时:" + strWXHS + " 保养周期:" + strBYZQ);
#endregion
//工单所属区域
string SiteID = dr["SiteID"].ToString();
//WO20
string WO20=dr["WO20"].ToString();
//设备名称
string SBName = dr["设备名称"].ToString();
//日期
string RiQi = dr["日期"].ToString();
//工单描述
string Remark = dr["工单描述"].ToString();
//省略连接字符串
SqlConnection conn = new SqlConnection(".....");
conn.Open();
//初始化类
using (System.Data.SqlClient.SqlBulkCopy sqlBC = new System.Data.SqlClient.SqlBulkCopy(conn))
{
//获取需要导入的数据表
DataTable dt = GetDataTable();
//每10W条数据一个事物
sqlBC.BatchSize = 100000;
//超时时间
sqlBC.BulkCopyTimeout = 60;
//表名Users
sqlBC.DestinationTableName = "dbo.Users";
//字段对应,分表为原数据表字段名,和导入数据库的字段名
sqlBC.ColumnMappings.Add("Access_ID", "MSSQL_ID");
sqlBC.ColumnMappings.Add("Access_Name", "MSSQL_Name");
//sqlBC.ColumnMappings.Add("Access_...", "MSSQL_...");
//sqlBC.ColumnMappings.Add("Access_...", "MSSQL_...");
//导入到数据库
sqlBC.WriteToServer(dt);