/// <summary>
/// 出库取消
/// </summary>
public int DeleteShipment(string slipNumber, string userCode)
{
DataSet shipmentLines = GetList(" SLIP_NUMBER = '" + slipNumber + "'");
List <CommandInfo> listSql = new List <CommandInfo>();
StringBuilder strSql = new StringBuilder();
strSql.Append("update BLL_SHIPMENT set ");
strSql.Append("STATUS_FLAG =@STATUS_FLAG,");
strSql.Append("LAST_UPDATE_USER =@LAST_UPDATE_USER, ");
strSql.Append("LAST_UPDATE_TIME =GETDATE() ");
strSql.Append("where SLIP_NUMBER=@SLIP_NUMBER");
SqlParameter[] shipmentParameters =
{
new SqlParameter("@SLIP_NUMBER", SqlDbType.VarChar, 50),
new SqlParameter("@STATUS_FLAG", SqlDbType.Int, 4),
new SqlParameter("@LAST_UPDATE_USER", SqlDbType.VarChar, 20)
};
shipmentParameters[0].Value = slipNumber;
shipmentParameters[1].Value = CConstant.DELETE_STATUS;
shipmentParameters[2].Value = userCode;
listSql.Add(new CommandInfo(strSql.ToString(), shipmentParameters));
foreach (DataRow dr in shipmentLines.Tables[0].Rows)
{
BaseProductTable pTable = new ProductManage().GetModel(CConvert.ToString(dr["PRODUCT_CODE"]));
if (pTable != null && pTable.STOCK_FLAG != 2)
{
//库存更新
strSql = new StringBuilder();
strSql.Append("update BASE_STOCK set ");
strSql.Append("QUANTITY=QUANTITY+@QUANTITY,");
strSql.Append("LAST_UPDATE_TIME=GETDATE(),");
strSql.Append("LAST_UPDATE_USER=@LAST_UPDATE_USER");
strSql.Append(" where WAREHOUSE_CODE=@WAREHOUSE_CODE and PRODUCT_CODE=@PRODUCT_CODE ");
SqlParameter[] stockParam =
{
new SqlParameter("@WAREHOUSE_CODE", SqlDbType.VarChar, 20),
new SqlParameter("@PRODUCT_CODE", SqlDbType.VarChar, 20),
new SqlParameter("@QUANTITY", SqlDbType.Decimal, 9),
new SqlParameter("@LAST_UPDATE_USER", SqlDbType.VarChar, 20)
};
stockParam[0].Value = dr["DEPARTUAL_WAREHOUSE_CODE"];
stockParam[1].Value = dr["PRODUCT_CODE"];
stockParam[2].Value = dr["QUANTITY"];
stockParam[3].Value = userCode;
listSql.Add(new CommandInfo(strSql.ToString(), stockParam));
}
//出库数量的更新
strSql = new StringBuilder();
strSql.Append("update BLL_ORDER_LINE set ");
strSql.Append("SHIPMENT_QUANTITY=SHIPMENT_QUANTITY-@SHIPMENT_QUANTITY");
strSql.Append(" where SLIP_NUMBER=@SLIP_NUMBER and LINE_NUMBER=@LINE_NUMBER ");
strSql.AppendFormat("and STATUS_FLAG <>{0}", CConstant.DELETE_STATUS);
SqlParameter[] orderLineParam =
{
new SqlParameter("@SLIP_NUMBER", SqlDbType.VarChar, 20),
new SqlParameter("@LINE_NUMBER", SqlDbType.Int, 4),
new SqlParameter("@SHIPMENT_QUANTITY", SqlDbType.Decimal, 9)
};
orderLineParam[0].Value = dr["ORDER_SLIP_NUMBER"];
orderLineParam[1].Value = dr["ORDER_LINE_NUMBER"];
orderLineParam[2].Value = dr["QUANTITY"];
listSql.Add(new CommandInfo(strSql.ToString(), orderLineParam));
}
return(DbHelperSQL.ExecuteSqlTran(listSql));
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(List <BllShipmentTable> datas)
{
List <CommandInfo> listSql = new List <CommandInfo>();
StringBuilder strSql = null;
int maxSlipNumber = CConvert.ToInt32(GetShipmentMaxSlipNumber(datas[0].COMPANY_CODE));
foreach (BllShipmentTable shipmentTable in datas)
{
strSql = new StringBuilder();
shipmentTable.SLIP_NUMBER = CConvert.ToString(++maxSlipNumber);
strSql.Append("insert into BLL_SHIPMENT(");
strSql.Append("SLIP_NUMBER,ORDER_SLIP_NUMBER,SERIAL_NUMBER,SLIP_DATE,ARRIVAL_DATE,AMOUNT,CURRENCY_CODE,STATUS_FLAG,CREATE_USER,CREATE_DATE_TIME,LAST_UPDATE_TIME,LAST_UPDATE_USER,AMOUNT_INCLUDED_TAX,AMOUNT_WITHOUT_TAX,TAX_RATE,TAX_AMOUNT,COMPANY_CODE)");
strSql.Append(" values (");
strSql.Append("@SLIP_NUMBER,@ORDER_SLIP_NUMBER,@SERIAL_NUMBER,@SLIP_DATE,@ARRIVAL_DATE,@AMOUNT,@CURRENCY_CODE,@STATUS_FLAG,@CREATE_USER,GETDATE(),GETDATE(),@LAST_UPDATE_USER,@AMOUNT_INCLUDED_TAX,@AMOUNT_WITHOUT_TAX,@TAX_RATE,@TAX_AMOUNT,@COMPANY_CODE)");
SqlParameter[] shipmentParam =
{
new SqlParameter("@SLIP_NUMBER", SqlDbType.VarChar, 20),
new SqlParameter("@ORDER_SLIP_NUMBER", SqlDbType.VarChar, 20),
new SqlParameter("@SERIAL_NUMBER", SqlDbType.VarChar, 50),
new SqlParameter("@SLIP_DATE", SqlDbType.DateTime),
new SqlParameter("@ARRIVAL_DATE", SqlDbType.DateTime),
new SqlParameter("@AMOUNT", SqlDbType.Decimal, 9),
new SqlParameter("@CURRENCY_CODE", SqlDbType.VarChar, 20),
new SqlParameter("@STATUS_FLAG", SqlDbType.Int, 4),
new SqlParameter("@CREATE_USER", SqlDbType.VarChar, 20),
new SqlParameter("@LAST_UPDATE_USER", SqlDbType.VarChar, 20),
new SqlParameter("@AMOUNT_INCLUDED_TAX", SqlDbType.Decimal, 9),
new SqlParameter("@AMOUNT_WITHOUT_TAX", SqlDbType.Decimal, 9),
new SqlParameter("@TAX_RATE", SqlDbType.Decimal, 5),
new SqlParameter("@TAX_AMOUNT", SqlDbType.Decimal, 9),
new SqlParameter("@COMPANY_CODE", SqlDbType.VarChar, 20)
};
shipmentParam[0].Value = shipmentTable.SLIP_NUMBER;
shipmentParam[1].Value = shipmentTable.ORDER_SLIP_NUMBER;
shipmentParam[2].Value = shipmentTable.SERIAL_NUMBER;
shipmentParam[3].Value = shipmentTable.SLIP_DATE;
shipmentParam[4].Value = shipmentTable.ARRIVAL_DATE;
shipmentParam[5].Value = shipmentTable.AMOUNT;
shipmentParam[6].Value = shipmentTable.CURRENCY_CODE;
shipmentParam[7].Value = shipmentTable.STATUS_FLAG;
shipmentParam[8].Value = shipmentTable.CREATE_USER;
shipmentParam[9].Value = shipmentTable.LAST_UPDATE_USER;
shipmentParam[10].Value = shipmentTable.AMOUNT_INCLUDED_TAX;
shipmentParam[11].Value = shipmentTable.AMOUNT_WITHOUT_TAX;
shipmentParam[12].Value = shipmentTable.TAX_RATE;
shipmentParam[13].Value = shipmentTable.TAX_AMOUNT;
shipmentParam[14].Value = shipmentTable.COMPANY_CODE;
listSql.Add(new CommandInfo(strSql.ToString(), shipmentParam));
foreach (BllShipmentLineTable shipmentLineTable in shipmentTable.Items)
{
//明细的保存
strSql = new StringBuilder();
strSql.Append("insert into BLL_SHIPMENT_LINE(");
strSql.Append("SLIP_NUMBER,LINE_NUMBER,DEPARTUAL_WAREHOUSE_CODE,PRODUCT_CODE,QUANTITY,UNIT_CODE,PRICE,AMOUNT,MEMO,STATUS_FLAG,ORDER_LINE_NUMBER)");
strSql.Append(" values (");
strSql.Append("@SLIP_NUMBER,@LINE_NUMBER,@DEPARTUAL_WAREHOUSE_CODE,@PRODUCT_CODE,@QUANTITY,@UNIT_CODE,@PRICE,@AMOUNT,@MEMO,@STATUS_FLAG,@ORDER_LINE_NUMBER)");
SqlParameter[] shipmentLineParam =
{
new SqlParameter("@SLIP_NUMBER", SqlDbType.VarChar, 20),
new SqlParameter("@LINE_NUMBER", SqlDbType.Int, 4),
new SqlParameter("@DEPARTUAL_WAREHOUSE_CODE", SqlDbType.VarChar, 20),
new SqlParameter("@PRODUCT_CODE", SqlDbType.VarChar, 40),
new SqlParameter("@QUANTITY", SqlDbType.Decimal, 9),
new SqlParameter("@UNIT_CODE", SqlDbType.VarChar, 20),
new SqlParameter("@PRICE", SqlDbType.Decimal, 9),
new SqlParameter("@AMOUNT", SqlDbType.Decimal, 9),
new SqlParameter("@MEMO", SqlDbType.NVarChar, 255),
new SqlParameter("@STATUS_FLAG", SqlDbType.Int, 4),
new SqlParameter("@ORDER_LINE_NUMBER", SqlDbType.Int, 4)
};
shipmentLineParam[0].Value = shipmentTable.SLIP_NUMBER;
shipmentLineParam[1].Value = shipmentLineTable.LINE_NUMBER;
shipmentLineParam[2].Value = shipmentLineTable.DEPARTUAL_WAREHOUSE_CODE;
shipmentLineParam[3].Value = shipmentLineTable.PRODUCT_CODE;
shipmentLineParam[4].Value = shipmentLineTable.QUANTITY;
shipmentLineParam[5].Value = shipmentLineTable.UNIT_CODE;
shipmentLineParam[6].Value = shipmentLineTable.PRICE;
shipmentLineParam[7].Value = shipmentLineTable.AMOUNT;
shipmentLineParam[8].Value = shipmentLineTable.MEMO;
shipmentLineParam[9].Value = shipmentLineTable.STATUS_FLAG;
shipmentLineParam[10].Value = shipmentLineTable.ORDER_LINE_NUMBER;
listSql.Add(new CommandInfo(strSql.ToString(), shipmentLineParam));
BaseProductTable pTable = new ProductManage().GetModel(shipmentLineTable.PRODUCT_CODE);
if (pTable != null && pTable.STOCK_FLAG != 2)
{
//库存更新
strSql = new StringBuilder();
strSql.Append("update BASE_STOCK set ");
strSql.Append("QUANTITY=QUANTITY-@QUANTITY,");
strSql.Append("LAST_UPDATE_TIME=GETDATE(),");
strSql.Append("LAST_UPDATE_USER=@LAST_UPDATE_USER");
strSql.Append(" where WAREHOUSE_CODE=@WAREHOUSE_CODE and PRODUCT_CODE=@PRODUCT_CODE ");
SqlParameter[] stockParam =
{
new SqlParameter("@WAREHOUSE_CODE", SqlDbType.VarChar, 20),
new SqlParameter("@PRODUCT_CODE", SqlDbType.VarChar, 20),
new SqlParameter("@QUANTITY", SqlDbType.Decimal, 9),
new SqlParameter("@LAST_UPDATE_USER", SqlDbType.VarChar, 20)
};
stockParam[0].Value = shipmentLineTable.DEPARTUAL_WAREHOUSE_CODE;
stockParam[1].Value = shipmentLineTable.PRODUCT_CODE;
stockParam[2].Value = shipmentLineTable.QUANTITY;
stockParam[3].Value = shipmentTable.LAST_UPDATE_USER;
listSql.Add(new CommandInfo(strSql.ToString(), stockParam));
}
//引当状态的结束
strSql = new StringBuilder();
strSql.Append("update BLL_ALLOATION set ");
strSql.Append("STATUS_FLAG=@STATUS_FLAG,");
strSql.Append("LAST_UPDATE_TIME=GETDATE(),");
strSql.Append("LAST_UPDATE_USER=@LAST_UPDATE_USER");
strSql.Append(" where ORDER_SLIP_NUMBER=@ORDER_SLIP_NUMBER and ORDER_LINE_NUMBER=@ORDER_LINE_NUMBER ");
strSql.AppendFormat("and STATUS_FLAG <>{0} AND STATUS_FLAG <> {1}", CConstant.DELETE_STATUS, CConstant.ALLOATION_SHIPMENT);
SqlParameter[] alloationParam =
{
new SqlParameter("@ORDER_SLIP_NUMBER", SqlDbType.VarChar, 20),
new SqlParameter("@ORDER_LINE_NUMBER", SqlDbType.Int, 4),
new SqlParameter("@STATUS_FLAG", SqlDbType.Int, 4),
new SqlParameter("@LAST_UPDATE_USER", SqlDbType.VarChar, 20)
};
alloationParam[0].Value = shipmentTable.ORDER_SLIP_NUMBER;
alloationParam[1].Value = shipmentLineTable.ORDER_LINE_NUMBER;
alloationParam[2].Value = CConstant.ALLOATION_SHIPMENT;
alloationParam[3].Value = shipmentTable.LAST_UPDATE_USER;
listSql.Add(new CommandInfo(strSql.ToString(), alloationParam));
//出库数量的回写
strSql = new StringBuilder();
strSql.Append("update BLL_ORDER_LINE set ");
strSql.Append("SHIPMENT_QUANTITY=SHIPMENT_QUANTITY+@SHIPMENT_QUANTITY");
strSql.Append(" where SLIP_NUMBER=@SLIP_NUMBER and LINE_NUMBER=@LINE_NUMBER ");
strSql.AppendFormat("and STATUS_FLAG <>{0}", CConstant.DELETE_STATUS);
SqlParameter[] orderLineParam =
{
new SqlParameter("@SLIP_NUMBER", SqlDbType.VarChar, 20),
new SqlParameter("@LINE_NUMBER", SqlDbType.Int, 4),
new SqlParameter("@SHIPMENT_QUANTITY", SqlDbType.Decimal, 9)
};
orderLineParam[0].Value = shipmentTable.ORDER_SLIP_NUMBER;
orderLineParam[1].Value = shipmentLineTable.ORDER_LINE_NUMBER;
orderLineParam[2].Value = shipmentLineTable.QUANTITY;
listSql.Add(new CommandInfo(strSql.ToString(), orderLineParam));
//将修改的数据回写到BLL_ORDER_HEADER
strSql = new StringBuilder();
strSql.Append("update BLL_ORDER_HEADER set ");
strSql.Append("SERIAL_NUMBER=@SERIAL_NUMBER,CHECK_NUMBER=@CHECK_NUMBER,CUSTOMER_PO_NUMBER=@CUSTOMER_PO_NUMBER ");
strSql.Append("where SLIP_NUMBER=@SLIP_NUMBER");
SqlParameter[] orderHeader =
{
new SqlParameter("@SLIP_NUMBER", SqlDbType.VarChar, 20),
new SqlParameter("@SERIAL_NUMBER", SqlDbType.VarChar, 50),
new SqlParameter("@CHECK_NUMBER", SqlDbType.VarChar, 20),
new SqlParameter("@CUSTOMER_PO_NUMBER", SqlDbType.VarChar, 50)
};
orderHeader[0].Value = shipmentTable.ORDER_SLIP_NUMBER;
orderHeader[1].Value = shipmentTable.SERIAL_NUMBER;
orderHeader[2].Value = shipmentTable.CHECK_NUMBER;
orderHeader[3].Value = shipmentTable.CUSTOMER_PO_NUMBER;
listSql.Add(new CommandInfo(strSql.ToString(), orderHeader));
}
}
return(DbHelperSQL.ExecuteSqlTran(listSql));
}
