RuiTengJb/ICSSoft.AppSln/ICSSoft.Frame.Data.DAL/ICSMODELOP2CKGROUPDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ICSSoft.Frame.Data.Entity;
using ICSSoft.Base.Config.DBHelper;
using System.Data;
using System.Data.SqlClient;
using ICSSoft.Base.Config.AppConfig;

namespace ICSSoft.Frame.Data.DAL
{

    public class ICSMODELOP2CKGROUPDAL
    {



        #region 增加修改

        public static void AddAndEdit(List<FormICSMODELOP2CKGROUPUIModel> ckgroupInfoList, string dsconn)
        {

            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                foreach (FormICSMODELOP2CKGROUPUIModel ckgroupInfo in ckgroupInfoList)
                {

                    bool isNew = false;
                    
                    var line = db.ICSMODELOP2CKGROUP.SingleOrDefault(a => a.ITEMID == ckgroupInfo.ITEMID && a.OPID == ckgroupInfo.OPID);
                    if (line == null)
                    {
                        isNew = true;
                        line = new ICSMODELOP2CKGROUP();
                        line.ITEMID = ckgroupInfo.ITEMID;
                        line.OPID = ckgroupInfo.OPID;
                        line.CKGROUPID = ckgroupInfo.CKGROUPID;
                        
                    }
                    
                    line.ITEMCODE = ckgroupInfo.ITEMCODE;
                    line.OPCODE = ckgroupInfo.OPCODE;
                    line.CKGROUP = ckgroupInfo.CKGROUP;
                    line.SEQ = ckgroupInfo.SEQ;
                    line.MUSER = ckgroupInfo.MUSER;
                    line.MUSERName = ckgroupInfo.MUSERName;
                    line.MTIME = Convert.ToDateTime(ckgroupInfo.MTIME);
                    line.WorkPoint = ckgroupInfo.WorkPoint;
                    line.EATTRIBUTE1 = ckgroupInfo.EATTRIBUTE1;

                    if (isNew)
                        db.ICSMODELOP2CKGROUP.InsertOnSubmit(line);
                    db.SubmitChanges();
                }

                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw new Exception(ex.Message);
            }
        }
         #endregion




        public static List<FormICSMODELOP2CKGROUPUIModel> SearchCkgroupInfoByCode(string ITEMCODE, string OPCODE, string CKGROUP, string dsconn)
        {
            List<FormICSMODELOP2CKGROUPUIModel> returnshift = new List<FormICSMODELOP2CKGROUPUIModel>();
            string sql = @"select a.ITEMID as ITEMID,
a.OPID as OPID,
a.CKGROUPID as CKGROUPID,
b.INVCODE as ITEMCODE,
c.OPCODE as OPCODE,
d.CKGROUP as CKGROUP,
a.SEQ as SEQ,
a.MUSERName as MUSERName,
a.MTIME as MTIME


from ICSMODELOP2CKGROUP a
left join ICSINVENTORY b on a.ITEMID=b.ID
left join ICSOP c on a.OPID=c.ID
left join ICSOQCCKGROUP d on  a.CKGROUPID=d.ID
           where b.INVCODE='" + ITEMCODE + "'and c.OPCODE='" + OPCODE + "' and d.CKGROUP='" + CKGROUP + "'";
            sql = string.Format(sql);
            DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
            foreach (DataRow dr in dt.Rows)
            {
                FormICSMODELOP2CKGROUPUIModel shiftInfo = new FormICSMODELOP2CKGROUPUIModel();
                
                shiftInfo.inventory = new FormICSINVENTORYUIModel();
             
                shiftInfo.inventory.INVCODE = dr["ITEMCODE"].ToString();
                shiftInfo.op = new FormICSOPUIModel();
                shiftInfo.op.OPCODE = dr["OPCODE"].ToString();
                shiftInfo.oqcckgroup = new FormICSOQCCKGROUPUIModel();
                shiftInfo.oqcckgroup.CKGROUP = dr["CKGROUP"].ToString();
                shiftInfo.SEQ = int.Parse(dr["SEQ"].ToString());
                shiftInfo.MUSERName = dr["MUSERName"].ToString();
                shiftInfo.MTIME = System.DateTime.Parse(dr["MTIME"].ToString());
                
                if (!returnshift.Contains(shiftInfo))
                {
                    returnshift.Add(shiftInfo);
                }

            }
            return returnshift;

        }


        public static List<FormICSOPUIModel> SearchOPInfoList(string str,string dsconn)
        {
            try
            {
                List<FormICSOPUIModel> returntype = new List<FormICSOPUIModel>();
                string sql = @"select distinct b.OPCODE,a.OPDESC
                    from dbo.ICSITEMROUTE2OP b
                    left join dbo.ICSOP a on b.OPID=a.ID
                    WHERE ITEMCODE='" + str + "'";
                sql = string.Format(sql);
                DataTable dt = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql).Tables[0];
                foreach (DataRow dr in dt.Rows)
                {
                    FormICSOPUIModel typemodel = new FormICSOPUIModel();

                    typemodel.OPCODE = dr["OPCODE"].ToString();
                    typemodel.OPDESC = dr["OPDESC"].ToString();
                    if (!returntype.Contains(typemodel))
                        returntype.Add(typemodel);
                }
                return returntype;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #region 根据存货编码查找产品id
        public static DataTable SelectItemId(string str, string wp)
        {
            string sql = @"select ID
            from dbo.ICSINVENTORY
            where INVCODE='" + str + "' and WorkPoint='" + wp + "'";
            sql = string.Format(sql);
            DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
            return dt;
        }

        #endregion

        #region 根据工序代码查找工序id
        public static DataTable SelectOpId(string str, string wp)
        {
            string sql = @"select ID
            from dbo.ICSOP
            where OPCODE='" + str + "'and WorkPoint='" + wp + "'";
            sql = string.Format(sql);
            DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
            return dt;
        }

        #endregion

        #region 根据检验类型代码查找检验类型id
        public static DataTable SelectCkgroupId(string str, string wp)
        {
            string sql = @"select ID
            from dbo.ICSOQCCKGROUP
            where CKGROUP='" + str + "'and WorkPoint='" + wp + "'";
            sql = string.Format(sql);
            DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
            return dt;
        }

        #endregion

        

        
         #region delete
        public static void delete(List<String> codeList)
        {
            FramDataContext db = new FramDataContext(AppConfig.AppConnectString);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var lines = db.ICSMODELOP2CKGROUP.Where(a => codeList.Contains(a.ITEMCODE) && codeList.Contains(a.OPCODE) && codeList.Contains(a.CKGROUP));
                db.ICSMODELOP2CKGROUP.DeleteAllOnSubmit(lines);

                db.SubmitChanges();
                db.Transaction.Commit();

            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }
        }
        #endregion

        #region 产品工序是否已经存在
        public static bool IsIncludingItemOP(string ITEMCODE, string OPCODE, string dsconn)
        {

            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var line = db.ICSMODELOP2CKGROUP.SingleOrDefault(a => a.ITEMCODE == ITEMCODE && a.OPCODE == OPCODE);
                if (line == null)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }

        }
        #endregion


        }
    }