public override int BackUpdate(DateTime startTime, DateTime endTime)
        {
            var result = 0;

            try
            {
                SourceConn.Open();
                RelatedConn.Open();
                var readCmd = new OracleCommand()
                {
                    Connection  = RelatedConn,
                    CommandText = $"select PlanCode from ProductionPlan where SourceDb='{ Source.Name}'"
                };
                var updateCmd = new OracleCommand
                {
                    Connection  = RelatedConn,
                    CommandText = $@"update ProductionPlan set {Source.Name}del=1 where PlanCode=:PlanCode"
                };
                updateCmd.Parameters.Add(new OracleParameter("PlanCode", OracleDbType.Char));
                updateCmd.Prepare();
                var reader    = readCmd.ExecuteReader();
                var sourceCmd = new SqlCommand
                {
                    Connection     = SourceConn,
                    CommandText    = $@"SELECT FBillNo,(select FNumber from t_icitem where t_icitem.FItemID=ICmo.FItemID) as FShortNumber,
(select FName from t_User where t_User.FUserID=ICmo.FBillerID) FBillerID,FCheckDate,(select FBOMNumber from icbom where icbom.FInterID= ICmo.FBomInterID) as FBOMNumber,(select FVersion from icbom where icbom.FInterID= ICmo.FBomInterID) as FVersion,FStatus,FAuxQty,(SELECT FName FROM T_MeasureUnit where T_MeasureUnit.FMeasureUnitID=ICmo.FUnitID)  FUnitID,FType,
FPlanCommitDate,FPlanFinishDate,(select FName from t_Department where t_Department.FItemID=ICmo.FWorkShop) FWorkShop,FWorkTypeID,FConfirmDate,FGMPBatchNo FROM ICmo   
                                    where FStatus=1 and FBillNo=@FBillNo",
                    CommandTimeout = 10
                };
                sourceCmd.Parameters.Add(new SqlParameter("FBillNo", System.Data.SqlDbType.Char, 8000));
                sourceCmd.Prepare();
                while (reader.Read())
                {
                    sourceCmd.Parameters[0].Value = reader[0];
                    if (sourceCmd.ExecuteScalar() == null)//如果找不到了,则说明源对应的行被删除,需要标记中间表数据为删除状态
                    {
                        updateCmd.Parameters[0].Value = reader[0];
                        updateCmd.ExecuteNonQuery();
                        result++;
                    }
                }
            }
            catch (Exception ex)
            {
                log4net.LogManager.GetLogger("Logger").Error(ex.ToString());
                throw;
            }
            finally
            {
                SourceConn.Close();
                RelatedConn.Close();
            }
            return(result);
        }
Exemple #2
0
        public override int BackUpdate(DateTime startTime, DateTime endTime)
        {
            var result = 0;

            try
            {
                SourceConn.Open();
                RelatedConn.Open();
                var readCmd = new OracleCommand()
                {
                    Connection  = RelatedConn,
                    CommandText = $"select Code from materiel where SourceDb='{Source.Name}'"
                };
                var updateCmd = new OracleCommand
                {
                    Connection  = RelatedConn,
                    CommandText = $"update materiel set {Source.Name}del = 1 where Code =:Code"
                };
                updateCmd.Parameters.Add(new OracleParameter("Code", OracleDbType.Char));
                updateCmd.Prepare();
                var reader    = readCmd.ExecuteReader();
                var sourceCmd = new SqlCommand
                {
                    Connection     = SourceConn,
                    CommandText    = $@"select FNumber,FName,(select FName from t_SubMessage where t_SubMessage.FInterID=t_icitem.FErpClsID) FTypeName,(SELECT FName FROM T_MeasureUnit where T_MeasureUnit.FMeasureUnitID=t_icitem.FUnitID) unit,
                                 FModel, FLastCheckDate, FItemID from t_icitem where FNumber like '30%' and FNumber=@FNumber",
                    CommandTimeout = 0
                };
                sourceCmd.Parameters.Add(new SqlParameter("FNumber", System.Data.SqlDbType.Char, 8000));
                sourceCmd.Prepare();
                while (reader.Read())
                {
                    sourceCmd.Parameters[0].Value = reader[0];
                    var obj = sourceCmd.ExecuteScalar();
                    if (obj == null)//如果找不到了,则说明源对应的行被删除,需要标记中间表数据为删除状态
                    {
                        updateCmd.Parameters[0].Value = reader[0];
                        updateCmd.ExecuteNonQuery();
                        result++;
                    }
                }
            }
            catch (Exception ex)
            {
                log4net.LogManager.GetLogger("Logger").Error(ex.ToString());
                throw;
            }
            finally
            {
                SourceConn.Close();
                RelatedConn.Close();
            }
            return(result);
        }
        public override int InitImport(DateTime startTime, DateTime endTime)
        {
            var result = 0;
            //定义批处理的
            var models = new BaseModel[BatchNum];

            SourceConn.Open();
            RelatedConn.Open();

            var sourceCmd = new SqlCommand()
            {
                Connection  = SourceConn,
                CommandText =
                    $@"SELECT FBillNo,(select FNumber from t_icitem where t_icitem.FItemID=ICmo.FItemID) as FShortNumber,
(select FName from t_User where t_User.FUserID=ICmo.FBillerID) FBillerID,FCheckDate,(select FBOMNumber from icbom where icbom.FInterID= ICmo.FBomInterID) as FBOMNumber,(select FVersion from icbom where icbom.FInterID= ICmo.FBomInterID) as FVersion,FStatus,FAuxQty,(SELECT FName FROM T_MeasureUnit where T_MeasureUnit.FMeasureUnitID=ICmo.FUnitID)  FUnitID,FType,
FPlanCommitDate,FPlanFinishDate,(select FName from t_Department where t_Department.FItemID=ICmo.FWorkShop) FWorkShop,FWorkTypeID,FConfirmDate,FGMPBatchNo FROM ICmo   
                                    where FStatus=1 and FCheckDate between CONVERT(datetime, '{startTime}') and CONVERT(datetime, '{endTime}')"
            };
            var reader     = sourceCmd.ExecuteReader();
            var relatedCmd = new OracleCommand
            {
                Connection  = RelatedConn,
                CommandText = GetInsertCmdText()
            };
            var i = 0;

            try
            {
                while (reader.Read())
                {
                    var plan = new ProductionPlan();
                    if (reader["FBillNo"] == DBNull.Value)
                    {
                        continue;
                    }
                    //华为只要"JP"开头单据,烽火套账只需要“BB”开头单据
                    if (!reader["FBillNo"].ToString().ToUpper().StartsWith(Source.PlanCodePrefix))
                    {
                        continue;
                    }
                    plan.PlanCode       = reader["FBillNo"].ToString();
                    plan.WorkOrder      = reader["FGMPBatchNo"].ToString();
                    plan.MaterielCode   = reader["fshortnumber"].ToString();
                    plan.Planner        = reader["FBillerID"].ToString();
                    plan.BillDate       = DateTime.Parse(reader["FCheckDate"].ToString());
                    plan.BOMCode        = reader["FBOMNumber"].ToString();
                    plan.BOMVersion     = reader["FVersion"].ToString();
                    plan.OrderState     = reader["FStatus"].ToString();
                    plan.PlanQuantity   = reader["FAuxQty"] == DBNull.Value ? 0 : double.Parse(reader["FAuxQty"].ToString());
                    plan.BaseUnit       = reader["FUnitID"].ToString();
                    plan.ProductionType = reader["FType"].ToString();
                    if (reader["FPlanCommitDate"] != DBNull.Value)
                    {
                        plan.PlanStartTime = DateTime.Parse(reader["FPlanCommitDate"].ToString());
                    }
                    if (reader["FPlanFinishDate"] != DBNull.Value)
                    {
                        plan.PlanEndTime = DateTime.Parse(reader["FPlanFinishDate"].ToString());
                    }
                    plan.WorkShop    = reader["FWorkShop"].ToString();
                    plan.Flag        = 'C';
                    plan.K3TimeStamp = DateTime.Parse(reader["FCheckDate"].ToString());
                    plan.SourceDb    = "XG";
                    plan.Line        = "FH";
                    plan.ID          = Guid.NewGuid().ToString();
                    models[i]        = plan;
                    i++;
                    if (i == BatchNum)
                    {
                        CommitBatch(relatedCmd, models);
                        result += i;
                        i       = 0;
                        models  = new BaseModel[BatchNum];//重置批
                    }
                }
                if (i > 0)
                {
                    var oddModels = new BaseModel[i];
                    for (int j = 0; j < i; j++)
                    {
                        oddModels[j] = models[j];
                    }
                    CommitBatch(relatedCmd, oddModels);
                    result += i;
                }
                reader.Close();
            }
            catch (Exception e)
            {
                log4net.LogManager.GetLogger("Logger").Error(e.ToString() + "\r\n" + sourceCmd.CommandText + "\r\n" + relatedCmd.CommandText);
                throw;
            }
            finally
            {
                SourceConn.Close();
                RelatedConn.Close();
            }
            return(result);
        }
        public override int Update(DateTime startTime, DateTime endTime)
        {
            var result = 0;

            SourceConn.Open();
            RelatedConn.Open();
            //定义批处理的
            var insertModels = new BaseModel[BatchNum];
            var updateModels = new BaseModel[BatchNum];
            var sourceCmd    = new SqlCommand
            {
                Connection  = SourceConn,
                CommandText =
                    $@"SELECT FBillNo,(select FNumber from t_icitem where t_icitem.FItemID=ICmo.FItemID) as FShortNumber,
(select FName from t_User where t_User.FUserID=ICmo.FBillerID) FBillerID,FCheckDate,(select FBOMNumber from icbom where icbom.FInterID= ICmo.FBomInterID) as FBOMNumber,(select FVersion from icbom where icbom.FInterID= ICmo.FBomInterID) as FVersion,FStatus,FAuxQty,(SELECT FName FROM T_MeasureUnit where T_MeasureUnit.FMeasureUnitID=ICmo.FUnitID)  FUnitID,FType,
FPlanCommitDate,FPlanFinishDate,(select FName from t_Department where t_Department.FItemID=ICmo.FWorkShop) FWorkShop,FWorkTypeID,FConfirmDate,FGMPBatchNo FROM ICmo   
                                    where FStatus=1 ",
                CommandTimeout = 0
                                 //and FConfirmDate between CONVERT(datetime, '{startTime}') and CONVERT(datetime, '{endTime}')
            };
            var relatedCmd = new OracleCommand
            {
                Connection  = RelatedConn,
                CommandText = "select ID||','||HashCode||','||SourceDb from ProductionPlan where PlanCode=:PlanCode"
            };

            relatedCmd.Parameters.Add(new OracleParameter("PlanCode", OracleDbType.Char));
            relatedCmd.Prepare();
            var reader    = sourceCmd.ExecuteReader();
            var insertCmd = new OracleCommand()
            {
                Connection  = RelatedConn,
                CommandText = GetInsertCmdText()
            };
            var updateCmd = new OracleCommand()
            {
                Connection  = RelatedConn,
                CommandText = GetUpdateCmdText() + $@" where PlanCode=:PlanCode"
            };

            try
            {
                var i = 0;
                var j = 0;
                while (reader.Read())
                {
                    var plan = new ProductionPlan();
                    if (reader["FBillNo"] == DBNull.Value || reader["FGMPBatchNo"] == DBNull.Value || reader["fshortnumber"] == DBNull.Value || reader["FBillerID"] == DBNull.Value ||
                        reader["FCheckDate"] == DBNull.Value || reader["FBOMNumber"] == DBNull.Value || reader["FVersion"] == DBNull.Value || reader["FStatus"] == DBNull.Value)
                    {
                        continue;
                    }
                    //武汉华为只要"JP"开头单据,孝感烽火只需要“BB”开头单据
                    if (!reader["FBillNo"].ToString().ToUpper().StartsWith(Source.PlanCodePrefix))
                    {
                        continue;
                    }
                    plan.PlanCode       = reader["FBillNo"].ToString();
                    plan.WorkOrder      = reader["FGMPBatchNo"].ToString();
                    plan.MaterielCode   = reader["fshortnumber"].ToString();
                    plan.Planner        = reader["FBillerID"].ToString();
                    plan.BillDate       = DateTime.Parse(reader["FCheckDate"].ToString());
                    plan.BOMCode        = reader["FBOMNumber"].ToString();
                    plan.BOMVersion     = reader["FVersion"].ToString();
                    plan.OrderState     = reader["FStatus"].ToString();
                    plan.PlanQuantity   = reader["FAuxQty"] == DBNull.Value ? 0 : double.Parse(reader["FAuxQty"].ToString());
                    plan.BaseUnit       = reader["FUnitID"].ToString();
                    plan.ProductionType = reader["FType"].ToString();
                    if (reader["FPlanCommitDate"] != DBNull.Value)
                    {
                        plan.PlanStartTime = DateTime.Parse(reader["FPlanCommitDate"].ToString());
                    }
                    if (reader["FPlanFinishDate"] != DBNull.Value)
                    {
                        plan.PlanEndTime = DateTime.Parse(reader["FPlanFinishDate"].ToString());
                    }
                    plan.WorkShop    = reader["FWorkShop"].ToString();
                    plan.Flag        = 'C';
                    plan.K3TimeStamp = DateTime.Parse(reader["FCheckDate"].ToString());
                    plan.K3WriteTime = DateTime.Now;
                    plan.SourceDb    = Source.Name;
                    plan.Line        = Source.Line;
                    plan.CalculateHashCode();
                    if (CheckHasNull(plan.PlanCode, plan.WorkOrder, plan.MaterielCode, plan.Planner, plan.BOMCode, plan.BOMVersion, plan.OrderState))
                    {
                        continue;
                    }
                    relatedCmd.Parameters[0].Value = plan.PlanCode;
                    var obj = relatedCmd.ExecuteScalar();
                    if (obj == null)
                    {
                        plan.ID         = Guid.NewGuid().ToString();
                        insertModels[i] = plan;
                        i++;
                        if (i == BatchNum)
                        {
                            CommitBatch(insertCmd, insertModels);
                            result      += i;
                            i            = 0;
                            insertModels = new BaseModel[BatchNum];//重置批
                        }
                    }
                    else
                    {
                        var arr = obj.ToString().Split(',');
                        if (plan.HashCode != arr[1])
                        {
                            if (arr[2] != Source.Name && Source.Name != Configuration.Current.MainData)
                            {
                                continue;
                            }
                            log4net.LogManager.GetLogger("Logger").Info($"检测到生产计划更新【{plan.PlanCode}】");
                            plan.Flag       = 'U';
                            plan.ID         = obj.ToString().Split(',')[0].ToString();
                            updateModels[j] = plan;
                            j++;
                            if (j == BatchNum)
                            {
                                CommitBatch(updateCmd, updateModels);
                                result      += j;
                                j            = 0;
                                updateModels = new BaseModel[BatchNum];//重置批
                            }
                        }
                    }
                }
                if (i > 0)
                {
                    var oddModels = new BaseModel[i];
                    for (int k = 0; k < i; k++)
                    {
                        oddModels[k] = insertModels[k];
                    }
                    CommitBatch(insertCmd, oddModels);
                    result += i;
                }
                if (j > 0)
                {
                    var oddModels = new BaseModel[j];
                    for (int k = 0; k < j; k++)
                    {
                        oddModels[k] = updateModels[k];
                    }
                    CommitBatch(updateCmd, oddModels);
                    result += j;
                }
                reader.Close();
            }
            catch (Exception e)
            {
                log4net.LogManager.GetLogger("Logger").Error(e.ToString() + "\r\n" + sourceCmd.CommandText + "\r\n" + insertCmd.CommandText);
                throw;
            }
            finally
            {
                SourceConn.Close();
                RelatedConn.Close();
            }
            return(result);
        }
Exemple #5
0
        public override int InitImport(DateTime startTime, DateTime endTime)
        {
            var result = 0;
            //定义批处理的
            var models = new BaseModel[BatchNum];

            SourceConn.Open();
            RelatedConn.Open();
            var sourceCmd = new SqlCommand
            {
                Connection  = SourceConn,
                CommandText =
                    $@"select FNumber,FName,(select FName from t_SubMessage where t_SubMessage.FInterID=t_icitem.FErpClsID) FTypeName,(SELECT FName FROM T_MeasureUnit where T_MeasureUnit.FMeasureUnitID=t_icitem.FUnitID) unit,
                                 FModel,FLastCheckDate,FItemID from t_icitem 
                                    where FNumber like '30%' and FLastCheckDate between CONVERT(datetime, '{startTime}') and CONVERT(datetime, '{endTime}')"
            };
            var reader     = sourceCmd.ExecuteReader();
            var relatedCmd = new OracleCommand()
            {
                Connection  = RelatedConn,
                CommandText = GetInsertCmdText()
            };

            try
            {
                var i = 0;
                while (reader.Read())
                {
                    var materiel = new Materiel();
                    if (reader["FNumber"] == DBNull.Value)
                    {
                        continue;
                    }
                    materiel.Code          = reader["FNumber"].ToString();
                    materiel.Name          = reader["FName"].ToString();
                    materiel.Type          = reader["FTypeName"].ToString();
                    materiel.BaseUnit      = reader["unit"].ToString();
                    materiel.Specification = reader["FModel"].ToString();
                    materiel.Flag          = 'C';
                    materiel.K3TimeStamp   = DateTime.Now;
                    materiel.SourceDb      = "XG";
                    materiel.ID            = Guid.NewGuid().ToString();
                    models[i] = materiel;
                    i++;
                    if (i == BatchNum)
                    {
                        CommitBatch(relatedCmd, models);
                        result += i;
                        i       = 0;
                        models  = new BaseModel[BatchNum];//重置批
                    }
                }
                if (i > 0)
                {
                    var oddModels = new BaseModel[i];
                    for (int j = 0; j < i; j++)
                    {
                        oddModels[j] = models[j];
                    }
                    CommitBatch(relatedCmd, oddModels);
                    result += i;
                }
                reader.Close();
            }
            catch (Exception e)
            {
                log4net.LogManager.GetLogger("Logger").Error(e.Message + "\r\n" + sourceCmd.CommandText + "\r\n" + relatedCmd.CommandText);
                throw;
            }
            finally
            {
                SourceConn.Close();
                RelatedConn.Close();
            }
            return(result);
        }
Exemple #6
0
        public override int Update(DateTime startTime, DateTime endTime)
        {
            var result = 0;

            SourceConn.Open();
            RelatedConn.Open();
            //定义批处理的
            var insertModels = new BaseModel[BatchNum];
            var updateModels = new BaseModel[BatchNum];
            var sourceCmd    = new SqlCommand
            {
                Connection  = SourceConn,
                CommandText =
                    $@"select FNumber,FName,(select FName from t_SubMessage where t_SubMessage.FInterID=t_icitem.FErpClsID) FTypeName,(SELECT FName FROM T_MeasureUnit where T_MeasureUnit.FMeasureUnitID=t_icitem.FUnitID) unit,
                                 FModel,FLastCheckDate,FItemID from t_icitem 
                                    where FNumber like '30%' ",
                // and FLastCheckDate between CONVERT(datetime, '{startTime}') and CONVERT(datetime, '{endTime}'),
                CommandTimeout = 0
            };
            var relatedCmd = new OracleCommand
            {
                Connection  = RelatedConn,
                CommandText = "select ID||','||HashCode||','||sourceDb from Materiel where Code=:Code"
            };

            relatedCmd.Parameters.Add(new OracleParameter("Code", OracleDbType.Char));
            relatedCmd.Prepare();
            var reader    = sourceCmd.ExecuteReader();
            var insertCmd = new OracleCommand()
            {
                Connection  = RelatedConn,
                CommandText = GetInsertCmdText()
            };
            var updateCmd = new OracleCommand()
            {
                Connection  = RelatedConn,
                CommandText = GetUpdateCmdText() + $@" where Code=:Code"
            };

            try
            {
                var i = 0;
                var j = 0;
                while (reader.Read())
                {
                    var materiel = new Materiel();
                    if (reader["FNumber"] == DBNull.Value)
                    {
                        continue;
                    }
                    materiel.Code          = reader["FNumber"].ToString();
                    materiel.Name          = reader["FName"].ToString();
                    materiel.Type          = reader["FTypeName"].ToString();
                    materiel.BaseUnit      = reader["unit"].ToString();
                    materiel.Specification = reader["FModel"].ToString();
                    materiel.Flag          = 'C';
                    materiel.K3TimeStamp   = DateTime.Now;
                    materiel.SourceDb      = Source.Name;
                    materiel.CalculateHashCode();
                    materiel.K3WriteTime           = DateTime.Now;
                    relatedCmd.Parameters[0].Value = materiel.Code;
                    var obj = relatedCmd.ExecuteScalar();
                    if (obj == null)
                    {
                        materiel.ID     = Guid.NewGuid().ToString();
                        insertModels[i] = materiel;
                        i++;
                        if (i == BatchNum)
                        {
                            CommitBatch(insertCmd, insertModels);
                            result      += i;
                            i            = 0;
                            insertModels = new BaseModel[BatchNum];//重置批
                        }
                    }
                    else
                    {
                        var arr = obj.ToString().Split(',');
                        //对比哈希值决定是否需要更新
                        if (materiel.HashCode != arr[1])
                        {
                            if (arr[2] != Source.Name && Source.Name != Configuration.Current.MainData)
                            {
                                continue;
                            }
                            log4net.LogManager.GetLogger("Logger").Info($"检测到物料更新【{materiel.Code}】");
                            materiel.ID     = obj.ToString().Split(',')[0].ToString();
                            materiel.Flag   = 'U';
                            updateModels[j] = materiel;
                            j++;
                            if (j == BatchNum)
                            {
                                CommitBatch(updateCmd, updateModels);
                                result      += j;
                                j            = 0;
                                updateModels = new BaseModel[BatchNum];//重置批
                            }
                        }
                    }
                }
                if (i > 0)
                {
                    var oddModels = new BaseModel[i];
                    for (int k = 0; k < i; k++)
                    {
                        oddModels[k] = insertModels[k];
                    }
                    CommitBatch(insertCmd, oddModels);
                    result += i;
                }
                if (j > 0)
                {
                    var oddModels = new BaseModel[j];
                    for (int k = 0; k < j; k++)
                    {
                        oddModels[k] = updateModels[k];
                    }
                    CommitBatch(updateCmd, oddModels);
                    result += j;
                }
                reader.Close();
            }
            catch (Exception e)
            {
                log4net.LogManager.GetLogger("Logger").Error(e.ToString() + "\r\n" + sourceCmd.CommandText + "\r\n" + insertCmd.CommandText + "\r\n" + updateCmd.CommandText);
                throw;
            }
            finally
            {
                SourceConn.Close();
                RelatedConn.Close();
            }
            return(result);
        }
Exemple #7
0
        public override int InitImport(DateTime startTime, DateTime endTime)
        {
            var result = 0;
            //定义批处理的
            var models = new BaseModel[BatchNum];

            SourceConn.Open();
            RelatedConn.Open();
            var sourceCmd = new SqlCommand
            {
                Connection  = SourceConn,
                CommandText =
                    $@"select * from 
(select FBOMNumber,(select FNumber from t_icitem where t_icitem.FItemID=icbom.FItemID) fshortnumber,FVersion,FStatus,FQty, (SELECT FName FROM T_MeasureUnit where T_MeasureUnit.FMeasureUnitID=icbom.FUnitID) FUnitID
,FInterID,FEnterTime from icbom) a right join (
select FEntryID,(select FNumber from t_icitem where t_icitem.FItemID=ICBOMCHILD.FItemID) detailfshortnumber,(select (SELECT FName FROM T_MeasureUnit where T_MeasureUnit.FMeasureUnitID=t_icitem.FUnitID) from t_icitem where t_icitem.FItemID=ICBOMCHILD.FItemID) detailFUnitID,FQty as detailfqty,FInterID from  ICBOMCHILD ) b on a.FInterID=b.FInterID where fshortnumber like '30%' and detailfshortnumber like '30%'
                       and FEnterTime between CONVERT(datetime, '{startTime}') and CONVERT(datetime, '{endTime}')"
            };
            var reader     = sourceCmd.ExecuteReader();
            var relatedCmd = new OracleCommand
            {
                Connection  = RelatedConn,
                CommandText = GetInsertCmdText()
            };
            var i = 0;

            try
            {
                while (reader.Read())
                {
                    var bom = new BOM();
                    if (reader["FBOMNumber"] == DBNull.Value)
                    {
                        continue;
                    }
                    bom.BOMCode            = reader["FBOMNumber"].ToString();
                    bom.MaterielCode       = reader["fshortnumber"].ToString();
                    bom.Version            = reader["FVersion"].ToString();
                    bom.UseState           = reader["FStatus"].ToString();
                    bom.MaterielQuantity   = reader["FQty"] == DBNull.Value ? 0 : double.Parse(reader["FQty"].ToString());
                    bom.MaterielUnit       = reader["FunitID"].ToString();
                    bom.DetailCode         = reader["FEntryID"].ToString();
                    bom.DetailMaterielCode = reader["detailfshortnumber"].ToString();
                    bom.DetailQuantity     = reader["detailfqty"] == DBNull.Value ? 0 : double.Parse(reader["FQty"].ToString());
                    bom.DetailUnit         = reader["detailFUnitID"].ToString();
                    bom.Flag        = 'C';
                    bom.K3TimeStamp = DateTime.Parse(reader["FEnterTime"].ToString());
                    bom.SourceDb    = "XG";
                    bom.ID          = Guid.NewGuid().ToString();
                    models[i]       = bom;
                    i++;
                    if (i == BatchNum)
                    {
                        CommitBatch(relatedCmd, models);
                        result += i;
                        i       = 0;
                        models  = new BaseModel[BatchNum];//重置批
                    }
                }
                if (i > 0)
                {
                    var oddModels = new BaseModel[i];
                    for (int j = 0; j < i; j++)
                    {
                        oddModels[j] = models[j];
                    }
                    CommitBatch(relatedCmd, oddModels);
                    result += i;
                }
                reader.Close();
            }
            catch (Exception e)
            {
                log4net.LogManager.GetLogger("Logger").Error(e.Message + "\r\n" + sourceCmd.CommandText + "\r\n" + relatedCmd.CommandText);
                throw;
            }
            finally
            {
                SourceConn.Close();
                RelatedConn.Close();
            }
            return(result);
        }
Exemple #8
0
        public override int Update(DateTime startTime, DateTime endTime)
        {
            var result = 0;

            SourceConn.Open();
            RelatedConn.Open();
            //定义批处理的
            var insertModels = new BaseModel[BatchNum];
            var updateModels = new BaseModel[BatchNum];
            var sourceCmd    = new SqlCommand
            {
                Connection  = SourceConn,
                CommandText =
                    $@"select * from 
(select FBOMNumber,(select FNumber from t_icitem where t_icitem.FItemID=icbom.FItemID) fshortnumber,FVersion,FStatus,FQty, (SELECT FName FROM T_MeasureUnit where T_MeasureUnit.FMeasureUnitID=icbom.FUnitID) FUnitID
,FInterID,FEnterTime from icbom) a right join (
select FEntryID,(select FNumber from t_icitem where t_icitem.FItemID=ICBOMCHILD.FItemID) detailfshortnumber,(select (SELECT FName FROM T_MeasureUnit where T_MeasureUnit.FMeasureUnitID=t_icitem.FUnitID) from t_icitem where t_icitem.FItemID=ICBOMCHILD.FItemID) detailFUnitID,FQty as detailfqty,FInterID from  ICBOMCHILD ) b on a.FInterID=b.FInterID where fshortnumber like '30%' and detailfshortnumber like '30%'
                       ",
                CommandTimeout = 0
                                 //and FEnterTime between CONVERT(datetime, '{startTime}') and CONVERT(datetime, '{endTime}'),
            };
            var relatedCmd = new OracleCommand
            {
                Connection  = RelatedConn,
                CommandText = "select ID||','||HashCode||','||SourceDb from BOM where BOMCode=:BOMCode and DetailCode=:DetailCode and Version=:Version"
            };

            relatedCmd.Parameters.Add(new OracleParameter("BOMCode", OracleDbType.Char));
            relatedCmd.Parameters.Add(new OracleParameter("DetailCode", OracleDbType.Char));
            relatedCmd.Parameters.Add(new OracleParameter("Version", OracleDbType.Char));
            relatedCmd.Prepare();
            var reader    = sourceCmd.ExecuteReader();
            var insertCmd = new OracleCommand()
            {
                Connection  = RelatedConn,
                CommandText = GetInsertCmdText()
            };
            var updateCmd = new OracleCommand()
            {
                Connection  = RelatedConn,
                CommandText = GetUpdateCmdText() + $@" where BOMCode=:BOMCode and DetailCode=:DetailCode and Version=:Version"
            };

            try
            {
                var i = 0;
                var j = 0;
                while (reader.Read())
                {
                    var bom = new BOM();
                    if (reader["FBOMNumber"] == DBNull.Value || reader["FVersion"].ToString() == "")
                    {
                        continue;
                    }
                    bom.BOMCode            = reader["FBOMNumber"].ToString();
                    bom.MaterielCode       = reader["fshortnumber"].ToString();
                    bom.Version            = reader["FVersion"].ToString();
                    bom.UseState           = reader["FStatus"].ToString();
                    bom.MaterielQuantity   = reader["FQty"] == DBNull.Value ? 0 : double.Parse(reader["FQty"].ToString());
                    bom.MaterielUnit       = reader["FunitID"].ToString();
                    bom.DetailCode         = reader["FEntryID"].ToString();
                    bom.DetailMaterielCode = reader["detailfshortnumber"].ToString();
                    bom.DetailQuantity     = reader["detailfqty"] == DBNull.Value ? 0 : double.Parse(reader["FQty"].ToString());
                    bom.DetailUnit         = reader["detailFUnitID"].ToString();
                    bom.Flag        = 'C';
                    bom.K3TimeStamp = DateTime.Parse(reader["FEnterTime"].ToString());
                    bom.K3WriteTime = DateTime.Now;
                    bom.SourceDb    = Source.Name;
                    bom.CalculateHashCode();
                    relatedCmd.Parameters[0].Value = bom.BOMCode;
                    relatedCmd.Parameters[1].Value = bom.DetailCode;
                    relatedCmd.Parameters[2].Value = bom.Version;
                    var obj = relatedCmd.ExecuteScalar();
                    if (obj == null)
                    {
                        bom.ID          = Guid.NewGuid().ToString();
                        insertModels[i] = bom;
                        i++;
                        if (i == BatchNum)
                        {
                            CommitBatch(insertCmd, insertModels);
                            result      += i;
                            i            = 0;
                            insertModels = new BaseModel[BatchNum];//重置批
                        }
                    }
                    else
                    {
                        var arr = obj.ToString().Split(',');
                        if (bom.HashCode != arr[1])
                        {
                            if (arr[2] != Source.Name && Source.Name != Configuration.Current.MainData)
                            {
                                continue;
                            }
                            log4net.LogManager.GetLogger("Logger").Info($"检测到BOM更新【{bom.BOMCode}】");
                            bom.ID          = obj.ToString().Split(',')[0].ToString();
                            bom.Flag        = 'U';
                            updateModels[j] = bom;
                            j++;
                            if (j == BatchNum)
                            {
                                CommitBatch(updateCmd, updateModels);
                                result      += j;
                                j            = 0;
                                updateModels = new BaseModel[BatchNum];//重置批
                            }
                        }
                    }
                }
                if (i > 0)
                {
                    var oddModels = new BaseModel[i];
                    for (int k = 0; k < i; k++)
                    {
                        oddModels[k] = insertModels[k];
                    }
                    CommitBatch(insertCmd, oddModels);
                    result += i;
                }
                if (j > 0)
                {
                    var oddModels = new BaseModel[j];
                    for (int k = 0; k < j; k++)
                    {
                        oddModels[k] = updateModels[k];
                    }
                    CommitBatch(updateCmd, oddModels);
                    result += j;
                }
                reader.Close();
            }
            catch (Exception e)
            {
                log4net.LogManager.GetLogger("Logger").Error(e.ToString() + "\r\n" + sourceCmd.CommandText + "\r\n" + insertCmd.CommandText);
                throw;
            }
            finally
            {
                SourceConn.Close();
                RelatedConn.Close();
            }
            return(result);
        }
Exemple #9
0
        public override int BackUpdate(DateTime startTime, DateTime endTime)
        {
            var result = 0;

            SourceConn.Open();
            RelatedConn.Open();
            try
            {
                var readCmd = new OracleCommand()
                {
                    Connection  = RelatedConn,
                    CommandText = $"select BOMCode,DetailCode,Version from BOM where SourceDb='{ Source.Name}'"
                };
                var updateCmd = new OracleCommand
                {
                    Connection  = RelatedConn,
                    CommandText = $@"update BOM set {Source.Name}del=1 where BOMCode=:BOMCode and DetailCode=:DetailCode and Version=:Version"
                };
                updateCmd.Parameters.Add(new OracleParameter("BOMCode", OracleDbType.Char));
                updateCmd.Parameters.Add(new OracleParameter("DetailCode", OracleDbType.Char));
                updateCmd.Prepare();
                var reader    = readCmd.ExecuteReader();
                var sourceCmd = new SqlCommand
                {
                    Connection     = SourceConn,
                    CommandText    = $@"select * from 
(select FBOMNumber,(select FNumber from t_icitem where t_icitem.FItemID=icbom.FItemID) fshortnumber,FVersion,FStatus,FQty, (SELECT FName FROM T_MeasureUnit where T_MeasureUnit.FMeasureUnitID=icbom.FUnitID) FUnitID
,FInterID,FEnterTime from icbom) a right join (
select FEntryID,(select FNumber from t_icitem where t_icitem.FItemID=ICBOMCHILD.FItemID) detailfshortnumber,(select (SELECT FName FROM T_MeasureUnit where T_MeasureUnit.FMeasureUnitID=t_icitem.FUnitID) from t_icitem where t_icitem.FItemID=ICBOMCHILD.FItemID) detailFUnitID,FQty as detailfqty,FInterID from  ICBOMCHILD ) b on a.FInterID=b.FInterID where fshortnumber like '30%' and detailfshortnumber like '30%'
                       and (FBOMNumber=@FBOMNumber and FEntryID=@FEntryID and FVersion=@FVersion)",
                    CommandTimeout = 0
                };
                sourceCmd.Parameters.Add(new SqlParameter("FBOMNumber", System.Data.SqlDbType.Char, 8000));
                sourceCmd.Parameters.Add(new SqlParameter("FEntryID", System.Data.SqlDbType.Char, 8000));
                sourceCmd.Parameters.Add(new SqlParameter("FVersion", System.Data.SqlDbType.Char, 8000));
                sourceCmd.Prepare();
                var a = 0;
                while (reader.Read())
                {
                    sourceCmd.Parameters[0].Value = reader[0];
                    sourceCmd.Parameters[1].Value = reader[1];
                    sourceCmd.Parameters[2].Value = reader[2];
                    //log4net.LogManager.GetLogger("logger").Info($"a:{++a}BOMNumber:{reader[0]}");
                    if (sourceCmd.ExecuteScalar() == null)//如果找不到了,则说明源对应的行被删除,需要标记中间表数据为删除状态
                    {
                        updateCmd.Parameters[0].Value = reader[0];
                        updateCmd.Parameters[1].Value = reader[1];
                        updateCmd.Parameters[2].Value = reader[2];;
                        updateCmd.ExecuteNonQuery();
                        result++;
                    }
                }
            }
            catch (Exception ex)
            {
                log4net.LogManager.GetLogger("Logger").Error(ex.ToString());
                throw;
            }
            finally
            {
                SourceConn.Close();
                RelatedConn.Close();
            }
            return(result);
        }