/// <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)); }