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;
}
//更新
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;
}