Пример #1
0
        /// <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));
        }
Пример #2
0
        /// <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));
        }