예제 #1
0
        public bool Update(DTcms.Model.TransportOrder model, IList<Model.Order> orders, IList<Model.Consumption> consumptions)
        {
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        StringBuilder strSql = new StringBuilder();
                        strSql.Append("update mtms_TransportOrder set ");

                        strSql.Append(" Code = @Code , ");
                        strSql.Append(" DispatchTime = @DispatchTime , ");
                        strSql.Append(" FactDispatchTime = @FactDispatchTime , ");
                        strSql.Append(" TimeLimit = @TimeLimit , ");
                        strSql.Append(" ReceiptTime = @ReceiptTime , ");
                        strSql.Append(" WarningTime = @WarningTime , ");
                        strSql.Append(" BackTime = @BackTime , ");
                        strSql.Append(" FactBackTime = @FactBackTime , ");
                        strSql.Append(" DriverId = @DriverId , ");
                        strSql.Append(" Advance = @Advance , ");
                        strSql.Append(" Payee = @Payee , ");
                        strSql.Append(" Repayment = @Repayment , ");
                        strSql.Append(" FactRepayment = @FactRepayment , ");
                        strSql.Append(" CarriageUnitPrice = @CarriageUnitPrice , ");
                        strSql.Append(" Carriage = @Carriage , ");
                        strSql.Append(" FactCarriage = @FactCarriage , ");
                        strSql.Append(" CostTotal = @CostTotal , ");
                        strSql.Append(" Profit = @Profit , ");
                        strSql.Append(" AddTime = @AddTime , ");
                        strSql.Append(" Status = @Status , ");
                        strSql.Append(" CustomerRemarks = @CustomerRemarks , ");
                        strSql.Append(" HaulwayRemarks = @HaulwayRemarks , ");
                        strSql.Append(" Remarks = @Remarks , ");
                        strSql.Append(" FactTotalPrice = @FactTotalPrice , ");
                        strSql.Append(" TotalPrice = @TotalPrice , ");
                        strSql.Append(" UnitPrice = @UnitPrice , ");
                        strSql.Append(" FactDispatchCount = @FactDispatchCount , ");
                        strSql.Append(" DispatchCount = @DispatchCount , ");
                        strSql.Append(" ReceivedWeight = @ReceivedWeight , ");
                        strSql.Append(" UnloadingWeight = @UnloadingWeight , ");
                        strSql.Append(" ArriveDate = @ArriveDate , ");
                        strSql.Append(" FactArriveDate = @FactArriveDate , ");
                        strSql.Append(" LoadingCapacityRunning = @LoadingCapacityRunning , ");
                        strSql.Append(" NoLoadingCapacityRunning = @NoLoadingCapacityRunning , ");
                        strSql.Append(" Weight = @Weight , ");
                        strSql.Append(" FactWeight = @FactWeight , ");
                        strSql.Append(" LoadingDate = @LoadingDate  ");
                        strSql.Append(" where Id=@Id ");

                        SqlParameter[] parameters = {
                                    new SqlParameter("@Id", SqlDbType.Int,4) ,
                                    new SqlParameter("@Code", SqlDbType.VarChar,254) ,
                                    new SqlParameter("@DispatchTime", SqlDbType.DateTime) ,
                                    new SqlParameter("@FactDispatchTime", SqlDbType.DateTime) ,
                                    new SqlParameter("@TimeLimit", SqlDbType.Int,4) ,
                                    new SqlParameter("@ReceiptTime", SqlDbType.DateTime) ,
                                    new SqlParameter("@WarningTime", SqlDbType.DateTime) ,
                                    new SqlParameter("@BackTime", SqlDbType.DateTime) ,
                                    new SqlParameter("@FactBackTime", SqlDbType.DateTime) ,
                                    new SqlParameter("@DriverId", SqlDbType.Int,4) ,
                                    new SqlParameter("@Advance", SqlDbType.Decimal,9) ,
                                    new SqlParameter("@Payee", SqlDbType.VarChar,254) ,
                                    new SqlParameter("@Repayment", SqlDbType.Decimal,9) ,
                                    new SqlParameter("@FactRepayment", SqlDbType.Decimal,9) ,
                                    new SqlParameter("@CarriageUnitPrice", SqlDbType.Decimal,9) ,
                                    new SqlParameter("@Carriage", SqlDbType.Decimal,9) ,
                                    new SqlParameter("@FactCarriage", SqlDbType.Decimal,9) ,
                                    new SqlParameter("@CostTotal", SqlDbType.Decimal,9) ,
                                    new SqlParameter("@Profit", SqlDbType.Decimal,9) ,
                                    new SqlParameter("@AddTime", SqlDbType.DateTime) ,
                                    new SqlParameter("@Status", SqlDbType.Int,4) ,
                                    new SqlParameter("@CustomerRemarks", SqlDbType.VarChar,254) ,
                                    new SqlParameter("@HaulwayRemarks", SqlDbType.VarChar,254) ,
                                    new SqlParameter("@Remarks", SqlDbType.VarChar,254) ,
                                    new SqlParameter("@FactTotalPrice", SqlDbType.Decimal,9) ,
                                    new SqlParameter("@TotalPrice", SqlDbType.Decimal,9) ,
                                    new SqlParameter("@UnitPrice", SqlDbType.Decimal,9) ,
                                    new SqlParameter("@FactDispatchCount", SqlDbType.Decimal,9) ,
                                    new SqlParameter("@DispatchCount", SqlDbType.Decimal,9) ,
                                    new SqlParameter("@ReceivedWeight", SqlDbType.Decimal,9) ,
                                    new SqlParameter("@UnloadingWeight", SqlDbType.Decimal,9) ,
                                    new SqlParameter("@ArriveDate", SqlDbType.DateTime) ,
                                    new SqlParameter("@FactArriveDate", SqlDbType.DateTime) ,
                                    new SqlParameter("@LoadingCapacityRunning", SqlDbType.Decimal,9) ,
                                    new SqlParameter("@NoLoadingCapacityRunning", SqlDbType.Decimal,9) ,
                                    new SqlParameter("@Weight", SqlDbType.Decimal,9) ,
                                    new SqlParameter("@LoadingDate", SqlDbType.DateTime),
                                    new SqlParameter("@FactWeight", SqlDbType.Decimal,9)

                        };

                        parameters[0].Value = model.Id;
                        parameters[1].Value = model.Code;
                        parameters[2].Value = model.DispatchTime;
                        parameters[3].Value = model.FactDispatchTime;
                        parameters[4].Value = model.TimeLimit;
                        parameters[5].Value = model.ReceiptTime;
                        parameters[6].Value = model.WarningTime;
                        parameters[7].Value = model.BackTime;
                        parameters[8].Value = model.FactBackTime;
                        parameters[9].Value = model.DriverId;
                        parameters[10].Value = model.Advance;
                        parameters[11].Value = model.Payee;
                        parameters[12].Value = model.Repayment;
                        parameters[13].Value = model.FactRepayment;
                        parameters[14].Value = model.CarriageUnitPrice;
                        parameters[15].Value = model.Carriage;
                        parameters[16].Value = model.FactCarriage;
                        parameters[17].Value = model.CostTotal;
                        parameters[18].Value = model.Profit;
                        parameters[19].Value = model.AddTime;
                        parameters[20].Value = model.Status;
                        parameters[21].Value = model.CustomerRemarks;
                        parameters[22].Value = model.HaulwayRemarks;
                        parameters[23].Value = model.Remarks;
                        parameters[24].Value = model.FactTotalPrice;
                        parameters[25].Value = model.TotalPrice;
                        parameters[26].Value = model.UnitPrice;
                        parameters[27].Value = model.FactDispatchCount;
                        parameters[28].Value = model.DispatchCount;
                        parameters[29].Value = model.ReceivedWeight;
                        parameters[30].Value = model.UnloadingWeight;
                        parameters[31].Value = model.ArriveDate;
                        parameters[32].Value = model.FactArriveDate;
                        parameters[33].Value = model.LoadingCapacityRunning;
                        parameters[34].Value = model.NoLoadingCapacityRunning;
                        parameters[35].Value = model.Weight;
                        parameters[36].Value = model.LoadingDate;
                        parameters[37].Value = model.FactWeight;

                        DbHelperSQL.ExecuteSql(conn, trans, strSql.ToString(), parameters);

                        #region 订单====================
                        if (orders.Count > 0)
                        {
                            Order orderDAL = new Order();
                            foreach (Model.Order order in orders)
                            {
                                orderDAL.UpdateField(conn, trans, order.Id,
                                    "UnitPrice = " + order.UnitPrice + ", Weight = " + order.Weight + ", Freight = " + order.Freight + ", PaidFreight = " + order.PaidFreight + ", UnpaidFreight = " + order.UnpaidFreight + ", Status = 1");
                            }
                        }
                        #endregion

                        #region 费用====================
                        Consumption consumptionDAL = new Consumption();
                        consumptionDAL.Delete(conn, trans, model.Id);
                        if (consumptions.Count > 0)
                        {
                            foreach (Model.Consumption consumption in consumptions)
                            {
                                consumptionDAL.Add(conn, trans, consumption);
                            }
                        }
                        #endregion

                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        return false;
                    }
                }
            }

            return true;
        }
예제 #2
0
        //更新
        public bool Update(Model.TransportOrder model, List<Model.TransportOrderItem> Item_list, List<Model.Consumption> consumption_list, List<Model.Order> orders)
        {
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        StringBuilder strSql = new StringBuilder();
                        strSql.Append("update mtms_TransportOrder set ");

                        strSql.Append(" Code = @Code , ");
                        strSql.Append(" DispatchTime = @DispatchTime , ");
                        strSql.Append(" FactDispatchTime = @FactDispatchTime , ");
                        strSql.Append(" TimeLimit = @TimeLimit , ");
                        strSql.Append(" BackTime = @BackTime , ");
                        strSql.Append(" FactBackTime = @FactBackTime , ");
                        strSql.Append(" MotorcadeName = @MotorcadeName , ");
                        strSql.Append(" CarNumber = @CarNumber , ");
                        strSql.Append(" Trailer = @Trailer , ");
                        strSql.Append(" Driver = @Driver , ");
                        strSql.Append(" Advance = @Advance , ");
                        strSql.Append(" Payee = @Payee , ");
                        strSql.Append(" Repayment = @Repayment , ");
                        strSql.Append(" FactRepayment = @FactRepayment , ");
                        strSql.Append(" Percentage = @Percentage , ");
                        strSql.Append(" Carriage = @Carriage , ");
                        strSql.Append(" FactCarriage = @FactCarriage , ");
                        strSql.Append(" CostTotal = @CostTotal , ");
                        strSql.Append(" Profit = @Profit , ");
                        strSql.Append(" AddTime = @AddTime , ");
                        strSql.Append(" Status = @Status , ");
                        strSql.Append(" WarningTime = @WarningTime , ");
                        strSql.Append(" Remarks = @Remarks  ");
                        strSql.Append(" where Id=@Id ");

                        SqlParameter[] parameters = {
                        new SqlParameter("@Id", SqlDbType.Int,4) ,
                        new SqlParameter("@Code", SqlDbType.VarChar,254) ,
                        new SqlParameter("@DispatchTime", SqlDbType.DateTime) ,
                        new SqlParameter("@FactDispatchTime", SqlDbType.DateTime) ,
                        new SqlParameter("@TimeLimit", SqlDbType.Int,4) ,
                        new SqlParameter("@BackTime", SqlDbType.DateTime) ,
                        new SqlParameter("@FactBackTime", SqlDbType.DateTime) ,
                        new SqlParameter("@MotorcadeName", SqlDbType.VarChar,254) ,
                        new SqlParameter("@CarNumber", SqlDbType.VarChar,254) ,
                        new SqlParameter("@Trailer", SqlDbType.VarChar,254) ,
                        new SqlParameter("@Driver", SqlDbType.VarChar,254) ,

                        new SqlParameter("@Advance", SqlDbType.Decimal,9) ,
                        new SqlParameter("@Payee", SqlDbType.VarChar,254) ,
                        new SqlParameter("@Repayment", SqlDbType.Decimal,9) ,
                        new SqlParameter("@FactRepayment", SqlDbType.Decimal,9) ,
                        new SqlParameter("@Percentage", SqlDbType.Decimal,9) ,
                        new SqlParameter("@Carriage", SqlDbType.Decimal,9) ,
                        new SqlParameter("@FactCarriage", SqlDbType.Decimal,9) ,
                        new SqlParameter("@CostTotal", SqlDbType.Decimal,9) ,
                        new SqlParameter("@Profit", SqlDbType.Decimal,9) ,
                        new SqlParameter("@AddTime", SqlDbType.DateTime) ,
                        new SqlParameter("@Status", SqlDbType.Int,4) ,
                        new SqlParameter("@WarningTime", SqlDbType.DateTime) ,
                        new SqlParameter("@Remarks", SqlDbType.VarChar,254)};

                        parameters[0].Value = model.Id;
                        parameters[1].Value = model.Code;
                        parameters[2].Value = model.DispatchTime;
                        parameters[3].Value = model.FactDispatchTime;
                        parameters[4].Value = model.TimeLimit;
                        parameters[5].Value = model.BackTime;
                        parameters[6].Value = model.FactBackTime;
                        parameters[7].Value = model.MotorcadeName;
                        parameters[8].Value = model.CarNumber;
                        parameters[9].Value = model.Trailer;
                        parameters[10].Value = model.Driver;
                        parameters[11].Value = model.Advance;
                        parameters[12].Value = model.Payee;
                        parameters[13].Value = model.Repayment;
                        parameters[14].Value = model.FactRepayment;
                        parameters[15].Value = model.Percentage;
                        parameters[16].Value = model.Carriage;
                        parameters[17].Value = model.FactCarriage;
                        parameters[18].Value = model.CostTotal;
                        parameters[19].Value = model.Profit;
                        parameters[20].Value = model.AddTime;
                        parameters[21].Value = model.Status;
                        parameters[22].Value = model.WarningTime;
                        parameters[23].Value = model.Remarks;
                        DbHelperSQL.ExecuteSql(conn, trans, strSql.ToString(), parameters);

                        if (Item_list != null)
                        {
                            foreach (Model.TransportOrderItem modelt in Item_list)
                            {
                                StringBuilder strSql1 = new StringBuilder();
                                strSql1.Append("update mtms_TransportOrderItem set ");

                                strSql1.Append(" TransportOrderId = @TransportOrderId , ");
                                strSql1.Append(" RoundStatus = @RoundStatus , ");
                                strSql1.Append(" ContractNumber = @ContractNumber , ");
                                strSql1.Append(" BillNumber = @BillNumber , ");
                                strSql1.Append(" Shipper = @Shipper , ");
                                strSql1.Append(" Receiver = @Receiver , ");
                                strSql1.Append(" LoadingAddress = @LoadingAddress , ");
                                strSql1.Append(" UnloadingAddress = @UnloadingAddress , ");
                                strSql1.Append(" Goods = @Goods , ");
                                strSql1.Append(" Unit = @Unit , ");
                                strSql1.Append(" FactDispatchCount = @FactDispatchCount , ");
                                strSql1.Append(" FactReceivedCount = @FactReceivedCount , ");
                                strSql1.Append(" CompensationCosts = @CompensationCosts , ");
                                strSql1.Append(" MyCosts = @MyCosts , ");
                                strSql1.Append(" Haulway = @Haulway , ");
                                strSql1.Append(" LoadingCapacityRunning = @LoadingCapacityRunning , ");
                                strSql1.Append(" NoLoadingCapacityRunning = @NoLoadingCapacityRunning , ");
                                strSql1.Append(" Formula = @Formula , ");
                                strSql1.Append(" UnitPrice = @UnitPrice , ");
                                strSql1.Append(" TotalPrice = @TotalPrice , ");
                                strSql1.Append(" CompanyPrice = @CompanyPrice,  ");
                                strSql1.Append(" OrderCode = @OrderCode , ");
                                strSql1.Append(" OrderId = @OrderId  ");
                                strSql1.Append(" where Id=@Id ");

                                SqlParameter[] parameters1 = {
                        new SqlParameter("@Id", SqlDbType.Int,4) ,
                        new SqlParameter("@TransportOrderId", SqlDbType.Int,4) ,
                        new SqlParameter("@RoundStatus", SqlDbType.VarChar,254) ,
                        new SqlParameter("@ContractNumber", SqlDbType.VarChar,254) ,
                        new SqlParameter("@BillNumber", SqlDbType.VarChar,254) ,
                        new SqlParameter("@Shipper", SqlDbType.VarChar,254) ,
                        new SqlParameter("@Receiver", SqlDbType.VarChar,254) ,
                        new SqlParameter("@LoadingAddress", SqlDbType.VarChar,254) ,
                        new SqlParameter("@UnloadingAddress", SqlDbType.VarChar,254) ,
                        new SqlParameter("@Goods", SqlDbType.VarChar,254) ,
                        new SqlParameter("@Unit", SqlDbType.VarChar,254) ,
                        new SqlParameter("@FactDispatchCount", SqlDbType.Decimal,9) ,
                        new SqlParameter("@FactReceivedCount", SqlDbType.Decimal,9) ,
                        new SqlParameter("@CompensationCosts", SqlDbType.Decimal,9) ,
                        new SqlParameter("@MyCosts", SqlDbType.Decimal,9) ,
                        new SqlParameter("@Haulway", SqlDbType.VarChar,254) ,
                        new SqlParameter("@LoadingCapacityRunning", SqlDbType.Decimal,9) ,
                        new SqlParameter("@NoLoadingCapacityRunning", SqlDbType.Decimal,9) ,
                        new SqlParameter("@Formula", SqlDbType.VarChar,254) ,
                        new SqlParameter("@UnitPrice", SqlDbType.Decimal,9) ,
                        new SqlParameter("@TotalPrice", SqlDbType.Decimal,9) ,
                        new SqlParameter("@CompanyPrice", SqlDbType.Decimal,9) ,
                        new SqlParameter("@OrderCode", SqlDbType.VarChar,254) ,
                        new SqlParameter("@OrderId", SqlDbType.Int,4)};

                                parameters1[0].Value = modelt.Id;
                                parameters1[1].Value = modelt.TransportOrderId;
                                parameters1[2].Value = modelt.RoundStatus;
                                parameters1[3].Value = modelt.ContractNumber;
                                parameters1[4].Value = modelt.BillNumber;
                                parameters1[5].Value = modelt.Shipper;
                                parameters1[6].Value = modelt.Receiver;
                                parameters1[7].Value = modelt.LoadingAddress;
                                parameters1[8].Value = modelt.UnloadingAddress;
                                parameters1[9].Value = modelt.Goods;
                                parameters1[10].Value = modelt.Unit;
                                parameters1[11].Value = modelt.FactDispatchCount;
                                parameters1[12].Value = modelt.FactReceivedCount;
                                parameters1[13].Value = modelt.CompensationCosts;
                                parameters1[14].Value = modelt.MyCosts;
                                parameters1[15].Value = modelt.Haulway;
                                parameters1[16].Value = modelt.LoadingCapacityRunning;
                                parameters1[17].Value = modelt.NoLoadingCapacityRunning;
                                parameters1[18].Value = modelt.Formula;
                                parameters1[19].Value = modelt.UnitPrice;
                                parameters1[20].Value = modelt.TotalPrice;
                                parameters1[21].Value = modelt.CompanyPrice;
                                parameters1[22].Value = modelt.OrderCode;
                                parameters1[23].Value = modelt.OrderId;
                                DbHelperSQL.ExecuteSql(conn, trans, strSql1.ToString(), parameters1);
                            }
                        }

                        if (consumption_list != null)
                        {
                            foreach (Model.Consumption modelt in consumption_list)
                            {
                                StringBuilder strSql2 = new StringBuilder();
                                strSql2.Append("insert into mtms_Consumption(");
                                strSql2.Append("Name,TransportOrderId,Money");
                                strSql2.Append(") values (");
                                strSql2.Append("@Name,@TransportOrderId,@Money");
                                strSql2.Append(") ");
                                SqlParameter[] parameters2 = {
                                    new SqlParameter("@Name", SqlDbType.VarChar,254) ,
                                    new SqlParameter("@TransportOrderId", SqlDbType.Int,4) ,
                                    new SqlParameter("@Money", SqlDbType.Decimal,9)};

                                parameters2[0].Value = modelt.Name;
                                parameters2[1].Value = modelt.TransportOrderId;
                                parameters2[2].Value = modelt.Money;
                                DbHelperSQL.ExecuteSql(conn, trans, strSql2.ToString(), parameters2);
                            }
                        }

                        if (orders.Count  > 0)
                        {
                            Order orderDAL = new Order();
                            foreach (Model.Order order in orders)
                            {
                                orderDAL.UpdateField(conn, trans, order.Id, " DispatchedCount = " + order.DispatchedCount + " ");
                            }
                        }

                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        return false;
                    }
                }
            }
            return true;
        }