everlight.eap/backend/DllSqlCoreAcc/SqlCoreAcc.cs

using Cksoft.Data;
using Cksoft.Data.Repository;
using Cksoft.Unity;
using DllHsms;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace DllSqlCoreAcc
{
    public class SqlCoreAcc
    {
        public static string CurrDbCode = "BusinessDb";
        public static string CurrMySqlCode = "Eap";
        //判断事件是否与最后事件相同，返回值为该机台最后事件记录ID值
        public static int JudgeEventSame(SqlCommand scmd, DataSet ds, ref string errorinfo)
        {
            try
            {
                //说明不是事件，直接返回
                if (!ds.Tables.Contains("oab"))
                    return 0;
                DataRow mainrow = ds.Tables["McaSecVMst"].Rows[0];
                string mcacode = mainrow["McaCode"].ToString();
                StringBuilder sqlstr = new StringBuilder(100);
                sqlstr.AppendFormat("select a.id,a.eventcode from mcasecvmst as a where a.id = (select MAX(id) from mcasecvmst where mcacode = '{0}')", mcacode);
                scmd.CommandText = sqlstr.ToString();
                SqlDataAdapter sda = new SqlDataAdapter(scmd);
                DataSet reds = new DataSet();
                sda.Fill(reds, "mcasecvmst");
                if (reds.Tables[0].Rows.Count <= 0)
                    return 0;
                string eventcode = reds.Tables[0].Rows[0]["eventcode"].ToString();
                if (eventcode != mainrow["eventcode"].ToString())
                    return 0;
                int preid = int.Parse(reds.Tables[0].Rows[0]["id"].ToString());
                //读取事件明细
                sqlstr.Clear();
                sqlstr.AppendFormat("select fcode,fval from McaSecVDetail where preid={0} order by fcode", preid);

                scmd.CommandText = sqlstr.ToString();
                sda.Fill(reds, "McaSecVDetail");


                DataRow[] orgrows = ds.Tables["oab"].Select("", "参数代码");
                if (orgrows.Length != reds.Tables[0].Rows.Count)
                    return 0;
                for (int i = 0; i < orgrows.Length; i++)
                {
                    if (orgrows[i]["参数代码"].ToString() != reds.Tables[0].Rows[i]["fcode"].ToString())
                        return 0;
                    if (orgrows[i]["数据内容"].ToString() != reds.Tables[0].Rows[i]["fval"].ToString())
                        return 0;
                }
                return preid;
            }
            catch (Exception ex)
            {
                errorinfo = ex.Message.ToString();
                return -1;
            }
        }
        /// <summary>
        /// 读取所有机台最后一次事件
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="errorinfo"></param>
        /// <returns></returns>
        public DataSet ReadLastMcaSecVMst(ref string errorinfo)
        {
            SqlConnection dbconn = null;
            try
            {
                string connstr = AppConfigurtaionServices.Configuration[$"{CurrDbCode}:ConnectionStrings"];

                dbconn = new SqlConnection(connstr);
                dbconn.Open();
                StringBuilder sqlstr = new StringBuilder(100);
                SqlTransaction mytrans = dbconn.BeginTransaction();
                SqlCommand scmd = dbconn.CreateCommand();
                scmd.Transaction = mytrans;
                sqlstr.AppendFormat("select a.ID,a.McaCode,a.ptime,a.pMill,a.etime,a.eMill,a.FType,a.FStatus,a.EventCode,a.FCount,a.EventID,1 islast");
                sqlstr.AppendFormat(" from McaSecVMst a");
                sqlstr.AppendFormat(" inner");
                sqlstr.AppendFormat(" join");
                sqlstr.AppendFormat(" (SELECT McaCode");
                sqlstr.AppendFormat(" , max(ptime)maxtime");
                sqlstr.AppendFormat(" FROM McaSecVMst");
                sqlstr.AppendFormat(" group by McaCode) b on a.McaCode = b.McaCode and a.ptime = b.maxtime");
                scmd.CommandText = sqlstr.ToString();
                SqlDataAdapter sda = new SqlDataAdapter(scmd);
                DataSet reds = new DataSet();
                sda.Fill(reds, "McaSecVMst");

                sqlstr.Clear();
                sqlstr.AppendFormat("SELECT TOP 0 a.ID,a.PreID,a.FCode,a.FVal,a.fnum FROM McaSecVDetail a");
                sqlstr.AppendFormat(" inner join(select a.ID");
                sqlstr.AppendFormat(" from McaSecVMst a");
                sqlstr.AppendFormat(" inner");
                sqlstr.AppendFormat(" join");
                sqlstr.AppendFormat(" (SELECT McaCode");
                sqlstr.AppendFormat(" , max(ptime)maxtime");
                sqlstr.AppendFormat(" FROM McaSecVMst");
                sqlstr.AppendFormat(" group by McaCode) b on a.McaCode = b.McaCode and a.ptime = b.maxtime) c on a.preid=c.id");
                scmd.CommandText = sqlstr.ToString();
                sda = new SqlDataAdapter(scmd);
                DataTable tempdt = new DataTable("McaSecVDetail");
                sda.Fill(tempdt);
                reds.Merge(tempdt);

                sqlstr.Clear();
                sqlstr.AppendFormat("SELECT TOP 0 a.ID,a.PreID,a.ptime,a.pMill,a.OrgFNum FROM McaSecTime a");
                scmd.CommandText = sqlstr.ToString();
                sda = new SqlDataAdapter(scmd);
                tempdt = new DataTable("McaSecTime");
                sda.Fill(tempdt);
                reds.Merge(tempdt);

                mytrans.Commit();
                return reds;
            }
            catch (Exception ex)
            {
                errorinfo = ex.Message.ToString();
                return null;
            }
            finally
            {
                if (dbconn != null)
                    dbconn.Close();
            }
        }

        public int BatIMcaSecVMst01(DataSet ds, ref string errorinfo)
        {
            SqlConnection dbconn = null;
            try
            {
                string connstr = AppConfigurtaionServices.Configuration[$"{CurrDbCode}:ConnectionStrings"];// System.Configuration.ConfigurationManager.AppSettings[CurrDbCode].ToString();
                DataRow row = ds.Tables[0].Rows[0];
                dbconn = new SqlConnection(connstr);
                dbconn.Open();
                StringBuilder sqlstr = new StringBuilder(100);
                SqlTransaction mytrans = dbconn.BeginTransaction();
                //SqlCommand scmd = dbconn.CreateCommand();
                //scmd.Transaction = mytrans;
                DataTable mstdt = ds.Tables["McaSecVMst"].Clone();
                DataRow[] rows = ds.Tables["McaSecVMst"].Select("", "", DataViewRowState.Added);
                foreach (DataRow temprow in rows)
                    mstdt.ImportRow(temprow);
                mstdt.Columns.Remove("islast");
                SqlBulkCopy bulkCopy = new SqlBulkCopy(dbconn, SqlBulkCopyOptions.CheckConstraints, mytrans);
                bulkCopy.DestinationTableName = mstdt.TableName;
                bulkCopy.BatchSize = mstdt.Rows.Count;
                bulkCopy.WriteToServer(mstdt);
                //修改主档数据
                SqlCommand scmd = dbconn.CreateCommand();
                scmd.Transaction = mytrans;
                rows = ds.Tables["McaSecVMst"].Select($"EventCode='{StandardCode.CEID_EachFinish}'", "", DataViewRowState.ModifiedCurrent);
                foreach(DataRow ttrow in rows)
                {
                    scmd.CommandText = $"update McaSecVMst set etime='{((DateTime)ttrow["etime"]).ToString("yyyy-MM-dd HH:mm:ss.fff")}',FCount={ttrow["FCount"].ToString()} where id='{ttrow["ID"].ToString()}'";
                    scmd.ExecuteNonQuery();
                }

                mstdt = ds.Tables["McaSecVDetail"].Clone();
                rows = ds.Tables["McaSecVDetail"].Select("", "", DataViewRowState.Added);
                foreach (DataRow temprow in rows)
                    mstdt.ImportRow(temprow);
                bulkCopy = new SqlBulkCopy(dbconn, SqlBulkCopyOptions.CheckConstraints, mytrans);
                bulkCopy.DestinationTableName = mstdt.TableName;
                bulkCopy.BatchSize = mstdt.Rows.Count;
                bulkCopy.WriteToServer(mstdt);

                mstdt = ds.Tables["McaSecTime"].Clone();
                rows = ds.Tables["McaSecTime"].Select("", "", DataViewRowState.Added);
                foreach (DataRow temprow in rows)
                    mstdt.ImportRow(temprow);
                bulkCopy = new SqlBulkCopy(dbconn, SqlBulkCopyOptions.CheckConstraints, mytrans);
                bulkCopy.DestinationTableName = mstdt.TableName;
                bulkCopy.BatchSize = mstdt.Rows.Count;
                bulkCopy.WriteToServer(mstdt);
                mytrans.Commit();
                return 1;
            }
            catch (Exception ex)
            {
                errorinfo = ex.Message.ToString();
                return -1;
            }
            finally
            {
                if (dbconn != null)
                    dbconn.Close();
            }
        }

        public DataSet ReadSecMstDs(ref string errorinfo)
        {
            IDatabase db = null;
            try
            {
                DataSet reds = new DataSet();
                db = DbFactory.Base(CurrMySqlCode);
                db.BeginTrans();

                //读取机台关联事件信息
                StringBuilder sqlstr = new StringBuilder(100);
                sqlstr.AppendFormat("select distinct a.FNum,a.EventReportMstID,b.EventID,c.FVal,w.fcode  参数代码,m.mc10 机台编号");
                sqlstr.AppendFormat(" from SecMstEvent a");
                sqlstr.AppendFormat(" inner join EventReportMst b on a.EventReportMstID = b.id");
                sqlstr.AppendFormat(" inner join SecDetail c on b.EventID = c.SecID and c.PreID = a.SecMstID");
                sqlstr.AppendFormat(" inner join McaEvent d on a.SecMstID = d.PreID");
                sqlstr.AppendFormat(" inner join Sec w on b.EventID = w.ID");
                sqlstr.AppendFormat(" inner join mca m on d.mcaid=m.id");
                //sqlstr.AppendFormat(" where  d.McaID = {0}", mcaid);
                DataTable tempdt = db.FindTableFor(sqlstr.ToString(), "SecMstEvent");
                reds.Merge(tempdt);

                //读取机台事件关联报告信息
                sqlstr.Clear();
                sqlstr.AppendFormat("select distinct t.FNum,a.EventReportMstID,t.ReportMstID,c.FVal,m.mc10 机台编号");
                sqlstr.AppendFormat(" from EventReportDetail t");
                sqlstr.AppendFormat(" inner join  SecMstEvent a on t.preid=a.EventReportMstID");
                sqlstr.AppendFormat(" inner join EventReportMst b on a.EventReportMstID = b.id");
                sqlstr.AppendFormat(" inner join SecDetail c on b.EventID = c.SecID and c.PreID = a.SecMstID");
                sqlstr.AppendFormat(" inner join McaEvent d on a.SecMstID = d.PreID");
                sqlstr.AppendFormat(" inner join mca m on d.mcaid=m.id");
                //sqlstr.AppendFormat(" where  d.McaID = {0}", mcaid);
                tempdt = db.FindTableFor(sqlstr.ToString(), "EventReportDetail");
                reds.Merge(tempdt);

                //读取机台报告关联参数指令
                sqlstr.Clear();
                sqlstr.AppendFormat("select distinct z.FNum,z.SecID,z.PreID ReportMstID,c.FVal,w.FCode 参数代码,c.dcode 参数类型,m.mc10 机台编号");
                sqlstr.AppendFormat(" from ReportDetail z");
                sqlstr.AppendFormat(" inner join EventReportDetail t on z.PreID=t.ReportMstID");
                sqlstr.AppendFormat(" inner join  SecMstEvent a on t.preid=a.EventReportMstID");
                sqlstr.AppendFormat(" inner join EventReportMst b on a.EventReportMstID = b.id");
                sqlstr.AppendFormat(" inner join SecDetail c on z.SecID = c.SecID and c.PreID = a.SecMstID");
                sqlstr.AppendFormat(" inner join McaEvent d on a.SecMstID = d.PreID");
                sqlstr.AppendFormat(" inner join Sec w on z.SecID = w.ID");
                sqlstr.AppendFormat(" inner join mca m on d.mcaid=m.id");
                //sqlstr.AppendFormat(" where  d.McaID = {0}", mcaid);
                tempdt = db.FindTableFor(sqlstr.ToString(), "ReportDetail");
                reds.Merge(tempdt);

                sqlstr.Clear();
                sqlstr.AppendFormat("select distinct a.id,a.SVal,a.FVal,m.mc10 机台编号 from McaDynamicMst as a");
                sqlstr.AppendFormat(" inner join McaEvent as b on a.PreID = b.PreID");
                sqlstr.AppendFormat(" inner join mca m on b.mcaid=m.id");
                //sqlstr.AppendFormat(" where b.McaID = {0}", mcaid);
                tempdt = db.FindTableFor(sqlstr.ToString(), "McaDynamicMst");
                reds.Merge(tempdt);

                sqlstr.Clear();
                sqlstr.AppendFormat("select distinct f.FCode 参数代码,t.fnum 序号,d.fval 参数值,d.dcode 数据类型代码,t.preid,m.mc10 机台编号 from McaDynamicDetail as t");
                sqlstr.AppendFormat(" inner join McaDynamicMst as a on t.PreID = a.id");
                sqlstr.AppendFormat(" inner join McaEvent as b on a.PreID = b.PreID");
                sqlstr.AppendFormat(" inner join SecDetail d on a.preid = d.PreID and t.SecID = d.SecID");
                sqlstr.AppendFormat(" inner join Sec f on t.SecID = f.id");
                sqlstr.AppendFormat(" inner join mca m on b.mcaid=m.id");
                //sqlstr.AppendFormat(" where b.McaID = {0}", mcaid);
                tempdt = db.FindTableFor(sqlstr.ToString(), "McaDynamicDetail");
                reds.Merge(tempdt);

                db.Commit();
                return reds;
            }
            catch (Exception ex)
            {
                errorinfo = ex.Message.ToString();
                return null;
            }
            finally
            {
                if (db != null)
                    db.Close();
            }
        }
    }
}