using System.Collections;
using Estsh.Core.Dapper;
using Estsh.Core.Models;
using System.Data;
using System.Text;
using Dapper;
using Estsh.Core.Repository.IRepositories;
using Estsh.Core.Model.Result;
using Estsh.Core.Model.ExcelModel;

/***************************************************************************************************
*
* 更新人：sitong.dong
* 描述：BOM管理数据访问类
* 修改时间：2022.06.22
* 修改日志：系统迭代升级
*
**************************************************************************************************/
namespace Estsh.Core.Repositories
{
    /// <summary>
    /// BOM管理数据访问类
    /// </summary>
    public class BOMDefineRepository : BaseRepository<SysBom>, IBOMDefineRepository
    {
        public BOMDefineRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {
        }
        #region  成员方法

        /// <summary>
        /// 获取下拉框菜单数据,这里显示的是配置信息 
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> getSelectModel(String modelType)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("select model_id as [value],model_name as [key] from sys_model where Enabled = 'Y' and model_type_id =@modelType");

                DynamicParameters Params = new DynamicParameters();
                Params.Add("@modelType", modelType);
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(SqlStringBuilder.ToString(), Params).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获取下拉框菜单数据，这里显示的是零件号        
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> getSelectPartNo(String bomType, String model)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                StringBuilder sqlbuilder = new StringBuilder();
                if (model == null || model.ToLower().Equals("null"))
                {
                    //DynamicParameters parameters = new DynamicParameters();
                    //parameters.Add("@bom_type", bomType);

                    //SqlStringBuilder.Append("SELECT DISTINCT ");
                    //SqlStringBuilder.Append("      b.part_id as [value],b.part_no as [key] ");
                    //SqlStringBuilder.Append("FROM  dbo.sys_bom a , ");
                    //SqlStringBuilder.Append("      dbo.sys_part b ");
                    //SqlStringBuilder.Append("WHERE a.part_id = b.part_id and bom_type=@bom_type and a.enabled='Y'  ");
                    //SqlStringBuilder.Append("ORDER BY b.part_no ");


                    SqlStringBuilder.Append("SELECT DISTINCT ");
                    SqlStringBuilder.Append("      b.part_id as [value],b.part_no as [key] ");
                    SqlStringBuilder.Append("FROM  dbo.sys_bom a , ");
                    SqlStringBuilder.Append("      dbo.sys_part b ");
                    SqlStringBuilder.Append("WHERE a.part_id = b.part_id  and a.enabled='Y'  ");
                    SqlStringBuilder.Append("ORDER BY b.part_no ");

                    List<KeyValueResult> result = dbConn.Query<KeyValueResult>(SqlStringBuilder.ToString()).ToList();
                    return result;
                }

                else
                {
                    DynamicParameters parameters = new DynamicParameters();

                    parameters.Add("@bom_type", bomType);
                    parameters.Add("@model_id", model);
                    if (bomType == "10")//绑定
                    {
                        sqlbuilder.AppendLine(" select DISTINCT d.part_id as [value],d.part_no as [key]  ");
                        sqlbuilder.AppendLine(" from sys_bom a");
                        sqlbuilder.AppendLine(" JOIN sys_part b ON a.part_id=b.part_id");
                        sqlbuilder.AppendLine(" JOIN sys_bom_detail c ON a.bom_id=c.bom_id ");
                        sqlbuilder.AppendLine(" join sys_part d ON d.part_id=c.item_part_id ");
                        sqlbuilder.AppendLine(" JOIN sys_model e ON e.model_name=b.part_no ");
                        sqlbuilder.AppendLine(" where e.model_id = @model_id and d.enabled = 'Y' ");
                        sqlbuilder.AppendLine(" ORDER BY d.part_no ");
                    }
                    else if (bomType == "20")//回冲
                    {
                        sqlbuilder.Append("SELECT DISTINCT ");
                        sqlbuilder.Append("      d.part_id AS [value] ,d.part_no AS [key] ");
                        sqlbuilder.Append("FROM  dbo.sys_bom a ");
                        sqlbuilder.Append("      LEFT JOIN dbo.sys_part b ON a.part_id = b.part_id ");
                        sqlbuilder.Append("                                  AND b.part_type = 2 ");
                        sqlbuilder.Append("      LEFT JOIN dbo.sys_bom_detail c ON a.bom_id = c.bom_id ");
                        sqlbuilder.Append("      LEFT JOIN dbo.sys_part d ON c.item_part_id = d.part_id ");
                        sqlbuilder.Append("WHERE b.model_id = @model_id and a.enabled='Y' ");
                        sqlbuilder.Append("ORDER BY d.part_no  ");
                    }
                    else
                    {
                        sqlbuilder.AppendLine("SELECT DISTINCT b.part_id as [value],b.part_no as [key] ");
                        sqlbuilder.AppendLine("FROM dbo.sys_bom a, dbo.sys_part b ");
                        sqlbuilder.AppendLine("WHERE a.part_id = b.part_id ");
                        sqlbuilder.AppendLine("	AND bom_type = @bom_type ");
                        sqlbuilder.AppendLine("	AND b.model_id = @model_id and a.enabled='Y' ");
                        sqlbuilder.AppendLine("ORDER BY b.part_no ");
                    }

                    List<KeyValueResult> result = dbConn.Query<KeyValueResult>(sqlbuilder.ToString(), parameters).ToList();
                    return result;
                }
            }
        }

        /// <summary>
        /// 获取获取工站数据列表
        /// </summary>
        /// <param name="factoryId"></param>
        /// <returns></returns>
        public List<KeyValueResult> getProcessData(int factoryId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT b.process_id as [value],b.process_name as [key] ");
                SqlStringBuilder.Append("FROM sys_stage a, sys_process b ");
                SqlStringBuilder.Append("WHERE b.stage_id = a.stage_id ");
                SqlStringBuilder.Append("AND b.factory_id = @factory_id ");
                SqlStringBuilder.Append("AND a.enabled = 'Y' ");
                SqlStringBuilder.Append("AND b.enabled = 'Y' ");
                SqlStringBuilder.Append("ORDER BY a.stage_code ");
                SqlStringBuilder.Append("	   , b.process_code  ");

                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@factory_id", factoryId);
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(SqlStringBuilder.ToString(), parameters).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获取BOM数据列表
        /// </summary>
        /// <param name="bomType"></param>
        /// <param name="partNo"></param>
        /// <param name="sort"></param>
        /// <param name="direction"></param>
        /// <returns></returns>
        public List<SysBom> getBOMList(String bomType, String partId, String sort, String direction, string pdline, string enabled)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT a.part_id ");
                SqlStringBuilder.Append(" , a.bom_id ");
                SqlStringBuilder.Append(" , b.item_part_id ");
                SqlStringBuilder.Append(" , d.part_no ");
                SqlStringBuilder.Append(" , d.part_spec ");
                SqlStringBuilder.Append(" , e.part_no AS item_part_no ");
                SqlStringBuilder.Append(" , e.part_spec AS item_part_spec ");
                SqlStringBuilder.Append(" , b.step_type ");
                SqlStringBuilder.Append(" , b.item_group ");
                SqlStringBuilder.Append(" , b.location ");
                SqlStringBuilder.Append(" , b.unit ");
                SqlStringBuilder.Append(" , b.vitual_part ");
                SqlStringBuilder.Append(" , b.version ");
                SqlStringBuilder.Append(" , c.process_name ");
                SqlStringBuilder.Append(" , c.process_id ");
                SqlStringBuilder.Append(" , b.guid ");
                SqlStringBuilder.Append("FROM sys_bom a, sys_bom_detail b ");
                if (bomType == "10")//绑定
                {
                    SqlStringBuilder.Append(" LEFT JOIN sys_process c ON b.process_id = c.process_id LEFT JOIN dbo.sys_terminal f ON c.process_id=f.process_id, sys_part d, sys_part e ");
                }
                else
                {
                    SqlStringBuilder.Append(" LEFT JOIN sys_process c ON b.process_id = c.process_id , sys_part d, sys_part e ");
                }
                SqlStringBuilder.Append("WHERE a.part_id = d.part_id ");
                SqlStringBuilder.Append(" AND b.item_part_id = e.part_id ");
                SqlStringBuilder.Append(" AND a.bom_id = b.bom_id ");
                if (bomType == "10")//绑定
                {
                    SqlStringBuilder.Append(" AND f.pdline_id=@pdline ");
                }
                SqlStringBuilder.Append(" AND d.part_id = @part_id ");
                SqlStringBuilder.Append(" AND a.bom_type = @bom_type ");
                if (!string.IsNullOrEmpty(enabled))
                {
                    SqlStringBuilder.Append(" AND a.enabled = @enabled ");
                }
                //SqlStringBuilder.Append("ORDER BY e.part_no ");
                SqlStringBuilder.Append("ORDER BY process_name, e.part_no ");

                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@part_id", partId);
                parameters.Add("@bom_type", bomType);
                parameters.Add("@pdline", pdline == "null" ? 0 : pdline);
                if (!string.IsNullOrEmpty(enabled))
                {
                    parameters.Add("@enabled", enabled);
                }

                List<SysBom> result = dbConn.Query<SysBom>(SqlStringBuilder.ToString(), parameters).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获取导出BOM数据列表
        /// </summary>
        /// <param name="bomType"></param>
        /// <param name="partNo"></param>
        /// <param name="sort"></param>
        /// <param name="direction"></param>
        /// <returns></returns>
        public List<BOMDefine> getExpotBOMList(String bomType, String partId, String sort, String direction)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT ");
                SqlStringBuilder.Append(" d.part_no as part_no_f ");//父阶零件号
                SqlStringBuilder.Append(" , e.part_no as part_no_z ");//子阶零件号
                SqlStringBuilder.Append(" , a.bom_type ");//BOM类型
                SqlStringBuilder.Append(" , '' as 'ItemGroup' ");//分组
                SqlStringBuilder.Append(" ,  b.location ");//位置
                SqlStringBuilder.Append(" , '' as 'Version' ");//版本
                SqlStringBuilder.Append(" , c.process_name ");//工站
                SqlStringBuilder.Append(" , b.step_type ");//工步序号
                SqlStringBuilder.Append(" , 'N' as 'virtual_order' ");//虚阶
                SqlStringBuilder.Append(" , d.part_spec AS 'part_spec_f' ");//父阶零件号描述
                SqlStringBuilder.Append(" , e.part_spec AS 'part_spec_z' ");//子阶零件号描述
                //SqlStringBuilder.Append(" , c.process_name AS '流程名称' ");
                //SqlStringBuilder.Append(" , b.location AS '显示位置' ");
                SqlStringBuilder.Append("FROM sys_bom a, sys_bom_detail b ");
                SqlStringBuilder.Append(" LEFT JOIN sys_process c ON b.process_id = c.process_id, sys_part d, sys_part e ");
                SqlStringBuilder.Append("WHERE a.part_id = d.part_id ");
                SqlStringBuilder.Append(" AND b.item_part_id = e.part_id ");
                SqlStringBuilder.Append(" AND a.bom_id = b.bom_id ");
                SqlStringBuilder.Append(" AND d.part_id = @part_id ");
                SqlStringBuilder.Append(" AND a.bom_type = @bom_type ");
                SqlStringBuilder.Append(" AND a.enabled = 'Y' ");
                SqlStringBuilder.Append("ORDER BY e.part_no ");

                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@part_id", partId);
                parameters.Add("@bom_type", bomType);
                List<BOMDefine> result = dbConn.Query<BOMDefine>(SqlStringBuilder.ToString(), parameters).ToList();
                return result;
            }
        }

        public List<BOMDefine> exportALL(String bomType, String sort, String direction)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append(" d.part_no_f ");//父阶零件号
                SqlStringBuilder.Append(" , e.part_no_z ");//子阶零件号
                SqlStringBuilder.Append(" , a.bom_type ");//BOM类型
                SqlStringBuilder.Append(" , '' as 'ItemGroup' ");//分组
                SqlStringBuilder.Append(" ,  b.location ");//位置
                SqlStringBuilder.Append(" , '' as 'Version' ");//版本
                SqlStringBuilder.Append(" , c.process_name ");//工站
                SqlStringBuilder.Append(" , b.step_type ");//工步序号
                SqlStringBuilder.Append(" , 'N' as 'virtual_order' ");//虚阶
                SqlStringBuilder.Append(" , d.part_spec AS 'part_spec_f' ");//父阶零件号描述
                SqlStringBuilder.Append(" , e.part_spec AS 'part_spec_z' ");//子阶零件号描述
                SqlStringBuilder.Append("FROM sys_bom a , ");
                SqlStringBuilder.Append("sys_bom_detail b ");
                SqlStringBuilder.Append("LEFT JOIN sys_process c ON b.process_id = c.process_id , ");
                SqlStringBuilder.Append("sys_part d , ");
                SqlStringBuilder.Append("sys_part e ");
                SqlStringBuilder.Append("WHERE a.part_id = d.part_id ");
                SqlStringBuilder.Append("AND b.item_part_id = e.part_id ");
                SqlStringBuilder.Append("AND a.bom_id = b.bom_id ");
                SqlStringBuilder.Append("AND a.bom_type = @bom_type ");
                //lvf   20210317 加入过滤条件，过滤禁用的BOM
                SqlStringBuilder.Append(" AND a.enabled = 'Y'  and d.enabled = 'Y' ");
                SqlStringBuilder.Append("ORDER BY d.part_no , ");
                SqlStringBuilder.Append("e.part_no ");
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@bom_type", bomType);

                List<BOMDefine> result = dbConn.Query<BOMDefine>(SqlStringBuilder.ToString(), parameters).ToList();
                return result;
            }
        }

        /// <summary>
        /// 根据客户零件号查询零件编号
        /// </summary>
        /// <param name="cpn">客户零件号</param>
        /// <returns>零件编号</returns>
        public int GetPartIDByCPN(string cpn)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = "SELECT  part_id FROM dbo.sys_part WHERE cust_part_no=@cpn AND enabled='Y'";
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@cpn", cpn);

                object obj = dbConn.ExecuteScalar(SqlString, parameters);
                if (obj == null)
                    return 0;
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 根据零件号 或 客户零件号 查询零件编号
        /// </summary>
        /// <param name="partNO">零件号 或 客户零件号</param>
        /// <returns>零件编号</returns>
        public int GetPartID(string partNO)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = "SELECT  part_id FROM dbo.sys_part WHERE part_no=@partNo AND enabled='Y' union SELECT  part_id FROM dbo.sys_part WHERE cust_part_no=@partNo";

                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@partNo", partNO);
                object obj = dbConn.ExecuteScalar(SqlString, parameters);
                return obj == null ? 0 : Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 通过part_id获取bom类型
        /// </summary>
        /// <param name="bomID"></param>
        /// <returns></returns>
        public string GetBOMType(int bomID)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = "select bom_type from dbo.sys_bom where bom_id=@bom_id ";

                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@bom_id", bomID);
                object obj = dbConn.ExecuteScalar(SqlString, parameters);
                if (obj == null)
                    return string.Empty;

                return obj.ToString();
            }
        }

        /// <summary>
        /// 通过part_no获取part_id类型
        /// </summary>
        /// <param name="bomID"></param>
        /// <returns></returns>
        public string GetBOMPartId(string partNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = "SELECT TOP 1 part_id FROM dbo.sys_part WHERE part_no=@partNo";

                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@partNo", partNo);
                object obj = dbConn.ExecuteScalar(SqlString, parameters);
                if (obj == null)
                    return string.Empty;

                return obj.ToString();
            }
        }

        /// <summary>
        /// 根据零件号和bom类型获取bomID
        /// </summary>
        /// <param name="partNo"></param>
        /// <param name="bomType"></param>
        /// <returns></returns>
        public int GetBOMID(string partNo, int bomType)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = "select a.bom_id from sys_bom a, sys_part b where a.part_id=b.part_id and a.enabled='Y' and b.part_no=@partNo and a.bom_type=@bom_type";

                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@partNo", partNo);
                parameters.Add("@bom_type", bomType);
                List<SysBom> result = dbConn.Query<SysBom>(SqlString, parameters).ToList();
                if (result == null)
                    return 0;

                if (result.Count == 0)
                    return 0;

                return Convert.ToInt32(result[0].BomId.ToString());
            }
        }

        //更新表字段
        public bool UpdateBOMDetail(SysBomDetail values, int bomID, int itemPartID)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();

                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("UPDATE sys_bom_detail SET  ");
                SqlStringBuilder.Append(@"bom_id=@bomId,part_id=@partId,part_no=@partNo,step_type=@stepType,item_part_id=@itemPartId,
                 item_group=@itemGroup,process_id=@processId,version=@version,location=@location,vitual_part=@vitualPart ,update_userid = @updateUserId ,update_time=CONVERT(varchar(50), GETDATE(), 21)  ");
                SqlStringBuilder.Append(" Where bom_id=" + bomID + " and item_part_id=" + itemPartID);

                return dbConn.Execute(SqlStringBuilder.ToString(), values) > 0 ? true : false;
            }
        }

        public bool UpdateBOMDetail(SysBomDetail values, string guid)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();

                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("UPDATE sys_bom_detail SET  ");
                SqlStringBuilder.Append(@"bom_id=@bomId,part_id=@partId,part_no=@partNo,step_type=@stepType,item_part_id=@itemPartId,
                 item_group=@itemGroup,process_id=@processId,version=@version,location=@location,vitual_part=@vitualPart ,update_userid = @updateUserId ,update_time=CONVERT(varchar(50), GETDATE(), 21)  ");
                SqlStringBuilder.Append(" Where guid='" + guid + "'");
                return dbConn.Execute(SqlStringBuilder.ToString(), values) > 0 ? true : false;
            }
        }

        /// <summary>
        /// 删除 BOM 明细表
        /// </summary>
        /// <param name="bomID"></param>
        /// <returns></returns>
        public bool DeleteBOMDetail(int bomID)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                if (bomID == 0)
                    return false;

                List<string> SqlStrings = new List<string>(3);
                List<DynamicParameters> Parameters = new List<DynamicParameters>();

                DynamicParameters Params = new DynamicParameters();
                Params.Add("@bom_id", bomID);

                SqlStrings.Add("update sys_bom_match set Enabled='N' WHERE bom_id=@bom_id");
                Parameters.Add(Params);
                SqlStrings.Add("update sys_bom_detail set Enabled='N' WHERE bom_id=@bom_id");
                Parameters.Add(Params);

                for (int i = 0; i < SqlStrings.Count; i++)
                {
                    object obj = dbConn.Execute(SqlStrings[i], Parameters[i]);
                }
                return true;

            }
        }

        //启用
        public int EnableData(String ids)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();

                DynamicParameters Params = new DynamicParameters();
                Params.Add("@bom_id", ids);
                string SqlStrings = "update sys_bom_ set Enabled='Y' WHERE bom_id=@bom_id";

                int obj = dbConn.Execute(SqlStrings, Params);
                return obj;
            }
        }

        //禁用
        public int DisableData(String ids)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();

                DynamicParameters Params = new DynamicParameters();
                Params.Add("@bom_id", ids);
                string SqlStrings="update sys_bom_ set Enabled='N' WHERE bom_id=@bom_id";

                int obj = dbConn.Execute(SqlStrings, Params);
                return obj;
            }
        }

        /// <summary>
        /// 根据Guid删除BomDetail数据
        /// </summary>
        /// <param name="row"></param>
        /// <returns></returns>
        public int DeleteBOMDetial(String guid)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                if (guid == null)
                    return 0;
                String delStr = "update sys_bom_detail set Enabled='N'  where guid ='" + guid + "'";
                object obj = dbConn.Execute(delStr);
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 判断零件号是否已经定义了 KPSN RULE
        /// </summary>
        /// <param name="partID"></param>
        /// <returns></returns>
        public bool ExistsPartSNRule(int partID)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = "select top 1 1 from dbo.sys_part_sn_rule where part_id=@part_id";
                DynamicParameters Params = new DynamicParameters();
                Params.Add("@part_id", partID);

                object obj = dbConn.ExecuteScalar(SqlString, Params);
                return Convert.ToInt32(obj) != null;
            }
        }

        /// <summary>
        /// 判断流程是否存在
        /// </summary>
        /// <param name="partID"></param>
        /// <returns></returns>
        public int ExistsProcessId(string processName)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = "SELECT * FROM dbo.sys_process WHERE process_name=@processName and a.enabled='Y' ";
                DynamicParameters Params = new DynamicParameters();
                Params.Add("@processName", processName);

                object obj = dbConn.ExecuteScalar(SqlString, Params);
                return obj == null ? 0 : Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 为特征 BOM 产生匹配清单，用于发运绑定时的车型匹配
        /// </summary>
        /// <param name="bomID"></param>
        /// <returns></returns>
        public bool BuildBOMMatchForTransBind(int bomID)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT  c.fix1 ");
                SqlStringBuilder.Append("FROM  dbo.sys_bom a , ");
                SqlStringBuilder.Append("      dbo.sys_bom_detail b , ");
                SqlStringBuilder.Append("      dbo.sys_part_sn_rule c ");
                SqlStringBuilder.Append("WHERE a.bom_id = b.bom_id ");
                SqlStringBuilder.Append("      AND b.item_part_id = c.part_id ");
                SqlStringBuilder.Append("      AND a.bom_id = @bom_id and a.enabled='Y' ");
                SqlStringBuilder.Append("ORDER BY c.fix1 ");
                DynamicParameters Params = new DynamicParameters();
                Params.Add("@bom_id", bomID);
                List<SysBom> slist1 = dbConn.Query<SysBom>(SqlStringBuilder.ToString(), Params).ToList();

                if (slist1 == null)
                    return false;

                if (slist1.Count == 0)
                    return false;

                string MatchContent = string.Empty;

                for (int i = 0; i < slist1.Count; i++)
                {
                    MatchContent += slist1[i].Fix1.ToString() + " ";
                }

                Params = new DynamicParameters();
                Params.Add("bom_id", bomID);
                Params.Add("match_content", MatchContent.Trim());
                Hashtable hsParams = new Hashtable();
                hsParams.Add("bom_id", bomID);
                hsParams.Add("match_content", MatchContent.Trim());

                String sql = "Delete from sys_bom_match where bom_id = " + bomID;
                object obj = dbConn.Execute(sql);
                StringBuilder sqlInsert = new StringBuilder(1024);
                sqlInsert.Append("INSERT INTO dbo.sys_bom_match(bom_id,match_content,enabled) values");
                sqlInsert.Append("(@bom_id,@match_content,'Y'))");

                int result = dbConn.Execute(sqlInsert.ToString(), hsParams);
                return result > 0 ? true : false;
            }
        }

        /// <summary>
        /// 从 SYS_BASE 表中读取 BOMMatchByCPN 参数
        /// 判断生成车型匹配字符串时是否使用 sys_part.cust_part_no 字段
        /// </summary>
        /// <returns></returns>
        public bool BOMMatchByCPN()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = "select 1 from dbo.sys_base where param_name='BOMMatchByCPN' and param_value='Y' and a.enabled='Y' ";
                object obj = dbConn.ExecuteScalar(SqlString);
                return obj != null;
            }
        }

        /// <summary>
        /// 更新 BOM 匹配表，用于车型匹配
        /// </summary>
        /// <param name="bomID"></param>
        /// <returns></returns>
        public bool BuildBOMMatch(int bomID)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = string.Empty;
                bool isBomByCPN;
                // 如果系统参数配置了使用客户零件号，则用其组合匹配字符串
                if (BOMMatchByCPN())
                {
                    SqlString = @"select b.cust_part_no from sys_bom_detail a, sys_part b where a.item_part_id=b.part_id 
                                  and a.enabled='Y'  and a.bom_id=@bom_id  order by b.cust_part_no ";
                    isBomByCPN = true;
                }
                else
                {
                    SqlString = @"select b.part_no from sys_bom_detail a, sys_part b  where a.item_part_id=b.part_id 
                                  and a.enabled='Y'  and a.bom_id=@bom_id order by b.part_no";
                    isBomByCPN = false;
                }

                DynamicParameters Params = new DynamicParameters();
                Params.Add("@bom_id", bomID);

                List<SysBomDetail> slist1 = dbConn.Query<SysBomDetail>(SqlString, Params).ToList();
                if (slist1 == null)
                    return false;

                if (slist1.Count == 0)
                    return false;

                string MatchContent = string.Empty;

                for (int i = 0; i < slist1.Count; i++)
                {
                    if (isBomByCPN == true)
                    {
                        MatchContent += slist1[i].CustPartNo.ToString() + " ";
                    }
                    else
                    {
                        MatchContent += slist1[i].PartNo.ToString() + " ";
                    }
                }
                Params = new DynamicParameters();
                Params.Add("bom_id", bomID);
                Params.Add("match_content", MatchContent.Trim());
                Hashtable hsParams = new Hashtable();
                hsParams.Add("bom_id", bomID);
                hsParams.Add("match_content", MatchContent.Trim());

                String sql = "delete from sys_bom_match where bom_id =" + bomID;
                object obj = dbConn.Execute(sql);
                StringBuilder sqlInsert = new StringBuilder(1024);
                sqlInsert.Append("INSERT INTO dbo.sys_bom_match(bom_id,match_content,enabled) values");
                sqlInsert.Append("(@bom_id,@match_content,'Y'))");
                int result = dbConn.Execute(sqlInsert.ToString(), hsParams);
                return result > 0 ? true : false;
            }
        }

        public bool InsertData(List<BOMDefine> dt, bool isBOMMatchByCPN)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                List<string> Parts = new List<string>(dt.Count);
                List<String> SqlStrings = new List<string>(dt.Count);
                List<DynamicParameters> hs = new List<DynamicParameters>(dt.Count);
                int ProcessID = 0;

                for (int i = 0; i < dt.Count; i++)
                {
                    // 跳过空行
                    if (string.IsNullOrEmpty(dt[i].PartNoF.ToString().Trim()) || string.IsNullOrEmpty(dt[i].PartNoZ.ToString().Trim()))
                        continue;

                    ProcessID = GetProcessID(dt[i].ProcessName.ToString());
                    // 跳过 父零件号+子零件号+工站 重复的行
                    if (!Parts.Contains(dt[i].PartNoF.ToString() + dt[i].PartNoZ.ToString() + ProcessID.ToString()))
                    {
                        Parts.Add(dt[i].PartNoF.ToString() + dt[i].PartNoZ.ToString() + ProcessID.ToString());
                    }
                    else
                    {
                        continue;
                    }

                    StringBuilder SqlStringBuilder = new StringBuilder(1024);
                    SqlStringBuilder.Append("INSERT INTO dbo.sys_bom_detail ");
                    SqlStringBuilder.Append("        ( bom_id , ");
                    SqlStringBuilder.Append("          part_id , ");
                    SqlStringBuilder.Append("          item_part_id , ");
                    SqlStringBuilder.Append("          item_group , ");
                    SqlStringBuilder.Append("          location , ");
                    SqlStringBuilder.Append("          version , ");
                    SqlStringBuilder.Append("          process_id , ");
                    SqlStringBuilder.Append("          step_type , ");
                    SqlStringBuilder.Append("          vitual_part, ");
                    SqlStringBuilder.Append("          enabled, ");
                    SqlStringBuilder.Append("		   create_userid ,");
                    SqlStringBuilder.Append("		   create_time ,");
                    SqlStringBuilder.Append("          guid ");
                    SqlStringBuilder.Append("        ) ");
                    SqlStringBuilder.Append("VALUES  (  ");
                    SqlStringBuilder.Append("		   @bom_id, ");
                    SqlStringBuilder.Append("		   @part_id, ");
                    SqlStringBuilder.Append("		   @item_part_id, ");
                    SqlStringBuilder.Append("          @item_group , ");
                    SqlStringBuilder.Append("          @location , ");
                    SqlStringBuilder.Append("          @version , ");
                    SqlStringBuilder.Append("          @process_id , ");
                    SqlStringBuilder.Append("		   @step_type , ");
                    SqlStringBuilder.Append("          @vitual_part,  ");
                    SqlStringBuilder.Append("		  'Y' ,");
                    SqlStringBuilder.Append("		   @createUserid ,");
                    SqlStringBuilder.Append("		   CONVERT(varchar(50), GETDATE(), 21),");
                    SqlStringBuilder.Append("            newid()  ");
                    SqlStringBuilder.Append("        ) ");

                    int BomID = GetBOMID(dt[i].PartNoF.ToString(), int.Parse(dt[i].BomType.ToString()));
                    int PartID = GetPartID(dt[i].PartNoF.ToString());
                    int ItemPartID = GetPartID(dt[i].PartNoZ.ToString()); ;

                    DynamicParameters values = new DynamicParameters();
                    values.Add("@bom_id", BomID);
                    values.Add("@part_id", PartID);
                    values.Add("@item_part_id", ItemPartID);
                    values.Add("@item_group", dt[i].ItemGroup.ToString());
                    values.Add("@location", dt[i].location.ToString());
                    values.Add("@version", dt[i].version.ToString());
                    values.Add("@process_id", ProcessID);
                    values.Add("@step_type", string.IsNullOrEmpty(dt[i].StepType.ToString()) ? 0 : Convert.ToDouble(dt[i].StepType.ToString()));
                    values.Add("@vitual_part", dt[i].VirtualOrder.ToString());
                    SqlStrings.Add(SqlStringBuilder.ToString());
                    hs.Add(values);
                }
                for (int i = 0; i < SqlStrings.Count; i++)
                {
                    object obj = dbConn.Execute(SqlStrings[i], hs[i]);
                }
                return true;
            }
        }

        public int GetProcessID(string processName)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                int ProcessID = 0;
                int.TryParse(processName, out ProcessID);

                // 如果用户填的是 PROCESS_ID 则直接返回
                if (ProcessID != 0)
                    return ProcessID;

                string SqlString = "select process_id from dbo.sys_process where process_name=@process_name and a.enabled='Y' ";
                DynamicParameters Params = new DynamicParameters();
                Params.Add("@process_name", processName);

                object obj = dbConn.ExecuteScalar(SqlString, Params);
                return obj != null ? Convert.ToInt32(obj) : 0;
            }
        }

        /// <summary>
        /// 新增BOM
        /// </summary>
        /// <param name="Params"></param>
        /// <returns></returns>
        public bool InsertBOM(SysBom Params)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder sqlInsert = new StringBuilder(1024);
                sqlInsert.Append("INSERT INTO dbo.sys_bom(part_id,bom_type,create_userid, create_time,enabled) values");
                sqlInsert.Append("(@partId,@bomType,@createUserid, CONVERT(varchar(50), GETDATE(), 21),@enabled)");
                int result = dbConn.Execute(sqlInsert.ToString(), Params);
                return result > 0 ? true : false;
            }
        }

        /// <summary>
        /// 根据 BOM ID 删除整个 BOM （主表和所有系表)
        /// </summary>
        /// <param name="bomID">BOM ID</param>
        /// <returns></returns>
        public bool DeleteBOM(int bomID)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                if (bomID == 0)
                    return false;

                List<string> SqlStrings = new List<string>(3);
                List<DynamicParameters> Parameters = new List<DynamicParameters>();
                DynamicParameters Params = new DynamicParameters();
                Params.Add("@bom_id", bomID);

                SqlStrings.Add("update sys_bom_match set Enabled='N'  WHERE bom_id=@bom_id");
                Parameters.Add(Params);

                SqlStrings.Add("update sys_bom_detail set Enabled='N'  WHERE bom_id=@bom_id");
                Parameters.Add(Params);

                SqlStrings.Add("update sys_bom set Enabled='N'  WHERE bom_id=@bom_id");
                Parameters.Add(Params);

                for (int i = 0; i < SqlStrings.Count; i++)
                {
                    object obj = dbConn.Execute(SqlStrings[i], Parameters[i]);
                }
                return true;
            }
        }

        /// <summary>
        /// 为特征 BOM 产生匹配清单，用于发运绑定时的车型匹配
        /// </summary>
        /// <param name="bomID"></param>
        /// <returns></returns>
        public bool insertBom(string tableName, SysBom Params)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder sqlInsert = new StringBuilder(1024);
                sqlInsert.Append("INSERT INTO dbo." + tableName + "(part_id,bom_type,enabled,create_userid, create_time) values");
                sqlInsert.Append("(@partId,@bomType,'Y',@createUserid, CONVERT(varchar(50), GETDATE(), 21)))");

                int result = dbConn.Execute(sqlInsert.ToString(), Params);
                return result > 0 ? true : false;
            }
        }
        public bool insertBomDetail(string tableName, SysBomDetail Params)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("INSERT INTO dbo.sys_bom_detail ");
                SqlStringBuilder.Append("(bom_id,part_id,part_no,enabled,item_part_id,item_part_no ,item_group,process_id,version,location ");
                SqlStringBuilder.Append(",unit,vitual_part,enabled,update_userid,update_time,create_userid,create_time ");
                SqlStringBuilder.Append(",step_type,only_retrospect) ");
                SqlStringBuilder.Append(" VALUES (@bomId,@partid ,@partno,'Y',@itempartid,@itempartno,@itemgroup ,@processid,@version ");
                SqlStringBuilder.Append(",@location,@unit,@vitualpart,@enabled,@updateuserid,CONVERT(varchar(50), GETDATE(), 21),@createuserid,CONVERT(varchar(50), GETDATE(), 21) ");
                SqlStringBuilder.Append(",@steptype,@onlyretrospect) ");

                int result = dbConn.Execute(SqlStringBuilder.ToString(), Params);
                return result > 0 ? true : false;
            }
        }

        #endregion  成员方法
    }
}