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

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

namespace ICSSoft.Frame.Data.DAL
{
    public class ICSOPBOMDETAILDAL
    {
        
        private FramDataContext _domainDataProvider = null;


        public ICSOPBOMDETAILDAL(FramDataContext domainDataProvider)
        {
            this._domainDataProvider = domainDataProvider;
        }

        //新增 修改
        public static void Add(List<ICSOPBOMDETAIL> tbInfoList, string dsconn)
        {
            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                foreach (ICSOPBOMDETAIL tbinfo in tbInfoList)
                {
                    bool isNew = false;
                    var line = db.ICSOPBOMDETAIL.SingleOrDefault(a => a.OPID == tbinfo.OPID&&a.OPBOMVER==tbinfo.OPBOMVER&&a.ACTIONTYPE==tbinfo.ACTIONTYPE&&a.WorkPoint==tbinfo.WorkPoint);
                    if (line == null)
                    {
                        isNew = true;
                        line = new ICSOPBOMDETAIL();
                        line.OBITEMCODE = tbinfo.OBITEMCODE;
                        line.ITEMCODE = tbinfo.ITEMCODE;
                        line.OBCODE = tbinfo.OBCODE;
                        line.OPBOMVER = tbinfo.OPBOMVER;
                        line.OPID = tbinfo.OPID;
                        line.ACTIONTYPE = tbinfo.ACTIONTYPE;
                        line.WorkPoint = tbinfo.WorkPoint;
                    }

                    line.ITEMID = tbinfo.ITEMID;
                    line.OPCODE = tbinfo.OPCODE;
                    line.OBITEMNAME = tbinfo.OBITEMNAME;
                    line.OBITEMECN = tbinfo.OBITEMECN;
                    line.OBITEMUOM = tbinfo.OBITEMUOM;
                    line.OBITEMQTY = tbinfo.OBITEMQTY;
                    line.OBSITEMCODE = tbinfo.OBSITEMCODE;
                    line.OBITEMVER = tbinfo.OBITEMVER;
                    line.OBITEMTYPE = tbinfo.OBITEMTYPE;
                    line.OBITEMCONTYPE = tbinfo.OBITEMCONTYPE;
                    line.OBITEMEFFTIME = tbinfo.OBITEMEFFTIME;
                    line.OBITEMINVTIME = tbinfo.OBITEMINVTIME;
                    line.ISITEMCHECK = tbinfo.ISITEMCHECK;
                    line.ITEMCHECKVALUE = tbinfo.ITEMCHECKVALUE;

                    line.MUSER = tbinfo.MUSER;
                    line.MUSERName = tbinfo.MUSERName;
                    line.MTIME = tbinfo.MTIME;
                    line.EATTRIBUTE1 = tbinfo.EATTRIBUTE1;
                    line.CHECKSTATUS = tbinfo.CHECKSTATUS;
                    line.OBITEMSEQ = tbinfo.OBITEMSEQ;
                    line.OBPARSETYPE = tbinfo.OBPARSETYPE;
                    line.OBCHECKTYPE = tbinfo.OBCHECKTYPE;
                    line.OBVALID = tbinfo.OBVALID;
                    line.SNLENGTH = tbinfo.SNLENGTH;
                    line.NeedVendor = tbinfo.NeedVendor
;
                    if (isNew)
                        db.ICSOPBOMDETAIL.InsertOnSubmit(line);
                    db.SubmitChanges();

                }

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

        public static void deleteInfo(string dsconn, List<string> codeList)
        {

            FramDataContext db = new FramDataContext(dsconn);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            try
            {
                var lines = db.ICSOPBOMDETAIL.Where(a => codeList.Contains(a.ITEMCODE) && codeList.Contains(a.OBCODE) && codeList.Contains(a.OPBOMVER) && codeList.Contains(a.OBITEMCODE) && codeList.Contains(a.WorkPoint) && codeList.Contains(a.ACTIONTYPE.ToString()));
                db.ICSOPBOMDETAIL.DeleteAllOnSubmit(lines);

                db.SubmitChanges();
                db.Transaction.Commit();
            }
            catch (Exception ex)
            {
                db.Transaction.Rollback();
                throw ex;
            }

        }

        public List<ICSOPBOMDETAIL> QueryOPBOMDetail(string itemCode, string OPID, string BOMCode, string BOMVersion, string routeCode, string OPCode, int actiontype)
        {
            return QueryOPBOMDetail(itemCode, OPID, BOMCode, BOMVersion, routeCode, OPCode, actiontype, false);
        }

        public List<ICSOPBOMDETAIL> QueryOPBOMDetail(string itemCode, string OPID, string BOMCode, string BOMVersion, string routeCode, string OPCode, int actiontype, bool onlyValid)
        {
            string selectSql = "select * from icsopbomdetail a  left outer join Base_Inventory b ";
            selectSql += "on a.workpoint = b.workpoint ";
            selectSql += "and a.itemcode = b.invcode ";
            selectSql += "where 1=1 ";
            if (actiontype >= 0)
            {
                selectSql += "and ActionType = " + actiontype.ToString() + " ";
            }

            string tmpString = string.Empty;
            if ((itemCode != string.Empty) && (itemCode.Trim() != string.Empty))
            {
                tmpString += " and a.itemcode ='" + itemCode.Trim() + "'";
            }
            if ((OPID != string.Empty) && (OPID.Trim() != string.Empty))
            {
                tmpString += " and a.opid ='" + OPID.Trim() + "'";
            }
            if ((BOMCode != string.Empty) && (BOMCode.Trim() != string.Empty))
            {
                tmpString += "  and a.obcode ='" + BOMCode.Trim() + "'";
            }
            if ((BOMVersion != string.Empty) && (BOMVersion.Trim() != string.Empty))
            {
                tmpString += " and a.opbomver ='" + BOMVersion.Trim() + "'";
            }

            if ((OPCode != string.Empty) && (OPCode.Trim() != string.Empty))
            {
                tmpString += " and a.opcode ='" + OPCode.Trim() + "'";
            }

            if ((routeCode != string.Empty) && (routeCode.Trim() != string.Empty))
            {
                tmpString += " and a.opcode in (select opcode from icsitemroute2op where routecode ='" + routeCode.Trim() + "' and workpoint='" + AppConfig.WorkPointCode+"') ";
                tmpString += " and a.obcode in (select obcode from icsopbom where obroute ='" + routeCode.Trim() + "' and workpoint='" + AppConfig.WorkPointCode+"') ";
            }
            tmpString += " and a.workpoint='" + AppConfig.WorkPointCode+"'";
            if (onlyValid)
            {
                tmpString += " and a.obvalid = 1 ";
            }

            List<ICSOPBOMDETAIL> list = this._domainDataProvider.ExecuteQuery<ICSOPBOMDETAIL>(tmpString).ToList();
            return list;
        }

        public List<ICSOPBOMDETAIL> QueryOPBOMDetail(string itemCode, string mCode, string OPID, string BOMCode, string BOMVersion, string routeCode, string OPCode, int actiontype, bool onlyValid)
        {

            string selectSql = "select * from icsopbomdetail a left outer join Base_Inventory b ";
            selectSql += "on a.workpoint = b.workpoint ";
            selectSql += "and a.itemcode = b.invcode ";
            selectSql += "where 1=1 ";
            if (actiontype >= 0)
            {
                selectSql += "and ActionType = " + actiontype.ToString() + " ";
            }
            selectSql += " {0}";

            string tmpString = string.Empty;
            if ((itemCode != string.Empty) && (itemCode.Trim() != string.Empty))
            {
                tmpString += " and a.itemcode ='" + itemCode.Trim() + "'";
            }
            if ((mCode != string.Empty) && (mCode.Trim() != string.Empty))
            {
                tmpString += " and (a.OBITEMCODE ='" + mCode.Trim()
                           + "' or a.obsitemcode ='" + mCode.Trim() + "')";
            }
            if ((OPID != string.Empty) && (OPID.Trim() != string.Empty))
            {
                tmpString += " and a.opid ='" + OPID.Trim() + "'";
            }
            if ((BOMCode != string.Empty) && (BOMCode.Trim() != string.Empty))
            {
                tmpString += "  and a.obcode ='" + BOMCode.Trim() + "'";
            }
            if ((BOMVersion != string.Empty) && (BOMVersion.Trim() != string.Empty))
            {
                tmpString += " and a.opbomver ='" + BOMVersion.Trim() + "'";
            }

            if ((OPCode != string.Empty) && (OPCode.Trim() != string.Empty))
            {
                tmpString += " and a.opcode ='" + OPCode.Trim() + "'";
            }

            if ((routeCode != string.Empty) && (routeCode.Trim() != string.Empty))
            {
                tmpString += " and a.opcode in (select opcode from icsitemroute2op where routecode ='" + routeCode.Trim() + "' and workpoint='" + AppConfig.WorkPointCode+"') ";
                tmpString += " and a.obcode in (select obcode from icsopbom where obroute ='" + routeCode.Trim() + "' and workpoint='" + AppConfig.WorkPointCode+"') ";
            }
            tmpString += " and a.workpoint='" + AppConfig.WorkPointCode+"'";
            if (onlyValid)
            {
                tmpString += " and a.obvalid = 1 ";
            }
            List<ICSOPBOMDETAIL> list = this._domainDataProvider.ExecuteQuery<ICSOPBOMDETAIL>(tmpString).ToList();
            return list;

        }

        public List<ICSOPBOMDETAIL> QueryOPBOMDetail(string itemCode, string OPID, string BOMCode, string BOMVersion, string routeCode, string OPCode, bool check, bool onlyValid)
        {
            string selectSql = "select * from ICSopbomdetail where actiontype = 1 {0}";
            string tmpString = string.Empty;
            if ((itemCode != string.Empty) && (itemCode.Trim() != string.Empty))
            {
                tmpString += " and itemcode ='" + itemCode.Trim() + "'";
            }
            if ((OPID != string.Empty) && (OPID.Trim() != string.Empty))
            {
                tmpString += " and opid ='" + OPID.Trim() + "'";
            }
            if ((BOMCode != string.Empty) && (BOMCode.Trim() != string.Empty))
            {
                tmpString += "  and obcode ='" + BOMCode.Trim() + "'";
            }
            if ((BOMVersion != string.Empty) && (BOMVersion.Trim() != string.Empty))
            {
                tmpString += " and opbomver ='" + BOMVersion.Trim() + "'";
            }

            if ((OPCode != string.Empty) && (OPCode.Trim() != string.Empty))
            {
                tmpString += " and opcode ='" + OPCode.Trim() + "'";
            }

            if (check == true)
            {
                tmpString += " and OBITEMCONTYPE='item_control_keyparts'";
            }

            if ((routeCode != string.Empty) && (routeCode.Trim() != string.Empty))
            {
                tmpString += " and opcode in (select opcode from icsitemroute2op where routecode ='" + routeCode.Trim() + "' and workPoint='" + AppConfig.WorkPointCode + "')";
                tmpString += " and obcode in (select obcode from icsopbom where obroute ='" + routeCode.Trim() + "' and workPoint='" + AppConfig.WorkPointCode + "') ";
            }

            if (onlyValid)
            {
                tmpString += " and icsopbomdetail.obvalid = 1 ";
            }
            tmpString += " and workPoint='" + AppConfig.WorkPointCode+"'";
            tmpString += "order by obitemseq";

            return this._domainDataProvider.ExecuteQuery<ICSOPBOMDETAIL>(String.Format(selectSql, tmpString)).ToList();
        }

        public List<ICSOPBOMDETAIL> GetOPBOMDetails(string moCode, string routeCode, string opCode, bool check, bool onlyValid)
        {
            ICSMO currentMO = this._domainDataProvider.ICSMO.SingleOrDefault<ICSMO>(a => a.MOBOM == moCode && a.WorkPoint == AppConfig.WorkPointCode);
            if (currentMO == null)
            {
                throw new Exception("$Error_MONotExisted");
            }
            ICSOPBOMDAL dal = new ICSOPBOMDAL(this._domainDataProvider);
            ICSOPBOM currentOPBOM = dal.GetOPBOMByRouteCode(currentMO.ITEMCODE, routeCode, currentMO.MOVER);
            return this.QueryOPBOMDetail(currentOPBOM.ITEMCODE, string.Empty, currentOPBOM.OBCODE, currentOPBOM.OPBOMVER, currentOPBOM.OBROUTE, opCode,check, onlyValid);
        }

        public List<ICSOPBOMDETAIL> QueryOPDropBOMDetail(string itemCode, string OPID, string BOMCode, string BOMVersion, string routeCode, string OPCode)
        {
            string selectSql = "select * from icsopbomdetail where actiontype= 1 " + " {0}";
            string tmpString = string.Empty;
            if ((itemCode != string.Empty) && (itemCode.Trim() != string.Empty))
            {
                tmpString += " and itemcode ='" + itemCode.Trim() + "'";
            }
            if ((OPID != string.Empty) && (OPID.Trim() != string.Empty))
            {
                tmpString += " and opid ='" + OPID.Trim() + "'";
            }
            if ((BOMCode != string.Empty) && (BOMCode.Trim() != string.Empty))
            {
                tmpString += "  and obcode ='" + BOMCode.Trim() + "'";
            }
            if ((BOMVersion != string.Empty) && (BOMVersion.Trim() != string.Empty))
            {
                tmpString += " and opbomver ='" + BOMVersion.Trim() + "'";
            }

            if ((OPCode != string.Empty) && (OPCode.Trim() != string.Empty))
            {
                tmpString += " and opcode ='" + OPCode.Trim() + "'";
            }

            if ((routeCode != string.Empty) && (routeCode.Trim() != string.Empty))
            {
                tmpString += " and opcode in (select opcode from icsitemroute2op where routecode ='" + routeCode.Trim() + "' and WorkPoint='"+AppConfig.WorkPointCode+"')";
            }
            tmpString += " and WorkPoint='"+AppConfig.WorkPointCode+"'";
            return this._domainDataProvider.ExecuteQuery<ICSOPBOMDETAIL>(String.Format(selectSql, tmpString)).ToList();
        }


    }
}