using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using WMS.Model.Stock;

using WMS.DBUtility;
using WMS.IData.IStock;
using WMS.IData;using Oracle.ManagedDataAccess.Client;

namespace WMS.SqlServerData.StockData
{
    public class MoveLocTabData : IMoveLocTab
    {
        /// <summary>
        /// 获取移位单明细
        /// </summary>
        /// <returns>移位单明细</returns>
        public DataTable GetMoveLocTabDT(MoveLocTabModel model)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.AppendLine("select 												");
            strSql.AppendLine("  a.MOVELOC_ID,                                      ");
            strSql.AppendLine("  a.GOODS_ID,                                        ");
            strSql.AppendLine("  a.OLD_LOC_ID,                                      ");
            strSql.AppendLine("  a.NEW_LOC_ID,                                      ");
            strSql.AppendLine("  a.PLAN_NUM,                                        ");
            strSql.AppendLine("  a.REAL_NUM,                                        ");
            strSql.AppendLine("  a.MOVELOC_TAB_ID,                                   ");
            strSql.AppendLine("  a.MISTOCK_NUM,                                     ");
            strSql.AppendLine("  a.UNIT,                           --UNIT            ");
            strSql.AppendLine("  a.SCALE_UNIT,                    --重量UNIT        ");
            strSql.AppendLine("  a.PRICE,                         --标准价格        ");
            strSql.AppendLine("  a.PUTIN_ID,       --入库批次号                                     ");
            strSql.AppendLine("  a.DOCCTNUMBER,    --单据编号（调整填写调整号，入库填写入库号）     ");
            strSql.AppendLine("  a.SPECIFICATION,  --规格                                           ");
            strSql.AppendLine("  a.PRODUCTION_DATE,--生产日期                                       ");
            strSql.AppendLine("  a.WARE_DATE,      --上架时间                                       ");
            strSql.AppendLine("  a.PROVIDER_ID,    --供应商编号                                     ");
            strSql.AppendLine("  a.LOC_NUM,        --某商品某库位某批次号库存数                     ");
            strSql.AppendLine("  b.goods_name                                       ");

            if (model.SORT == 1)//去备份表查
            {
                strSql.AppendLine(" FROM T_STOCK_MOVELOC_TAB_BACK a                          ");
            }
            else
            {
                strSql.AppendLine(" FROM T_STOCK_MOVELOC_TAB a                          ");
            }
            
            strSql.AppendLine(" left join t_base_goods b                            ");
            strSql.AppendLine("	on a.goods_id = b.goods_id                          ");
            strSql.AppendLine("where 1 = 1");
            if (!string.IsNullOrEmpty(model.MOVELOC_TAB_ID))
            {
                strSql.AppendLine(" and a.MOVELOC_TAB_ID like '%" + model.MOVELOC_TAB_ID + "%' ");
            }
            if (!string.IsNullOrEmpty(model.MOVELOC_ID))
            {
                strSql.AppendLine(" and a.MOVELOC_ID = '" + model.MOVELOC_ID + "' ");
            }
            strSql.AppendLine(" order by a.MOVELOC_TAB_ID");

            return SystemDataObject.Instance.GetDataTable(strSql.ToString());
        }

        /// <summary>
        /// 插入移位单明细信息
        /// </summary>
        /// <param name="MoveLocTabModel">移位单明细Model</param>
        public void InsertMoveLocTab(MoveLocTabModel model)
        {
            StringBuilder strSql = new StringBuilder();

            //if (model.MOVELOC_ID == null)
            //{
            //    strSql.AppendLine("insert into T_STOCK_MOVELOC_TAB(");
            //    strSql.AppendLine("MOVELOC_ID,GOODS_ID,OLD_LOC_ID,NEW_LOC_ID,PLAN_NUM,REAL_NUM,MOVELOC_TAB_ID,MISTOCK_NUM)");
            //    strSql.AppendLine(" values (");
            //    strSql.AppendLine("SEQ_STOCK_MOVELOC.currval,:GOODS_ID,:OLD_LOC_ID,:NEW_LOC_ID,:PLAN_NUM,:REAL_NUM,SEQ_STOCK_MOVELOC_TAB.nextval,:MISTOCK_NUM)");
            //    OracleParameter[] parameters = {
            //       new OracleParameter(":GOODS_ID",OracleDbType.Varchar2,20),
            //       new OracleParameter(":OLD_LOC_ID",OracleDbType.Varchar2,20),
            //       new OracleParameter(":NEW_LOC_ID",OracleDbType.Varchar2,20),
            //       new OracleParameter(":PLAN_NUM", OracleDbType.Decimal,4),
            //       new OracleParameter(":REAL_NUM", OracleDbType.Decimal,4),
            //       new OracleParameter(":MISTOCK_NUM", OracleDbType.Decimal,4)};
            //    parameters[0].Value = model.GOODS_ID;
            //    parameters[1].Value = model.OLD_LOC_ID;
            //    parameters[2].Value = model.NEW_LOC_ID;
            //    parameters[3].Value = model.PLAN_NUM;
            //    parameters[4].Value = model.REAL_NUM;
            //    parameters[5].Value = model.MISTOCK_NUM;

            //    SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
            //}
            //else
            //{
                if (model.MOVELOC_TAB_ID == null)
                {
                    strSql.AppendLine("insert into T_STOCK_MOVELOC_TAB(");
                    strSql.AppendLine("MOVELOC_ID,GOODS_ID,OLD_LOC_ID,NEW_LOC_ID,PLAN_NUM,REAL_NUM,MISTOCK_NUM,UNIT,SCALE_UNIT,PUTIN_ID,DOCCTNUMBER,SPECIFICATION,PRODUCTION_DATE,WARE_DATE,PROVIDER_ID,LOC_NUM,PRICE)");
                    strSql.AppendLine(" values (");
                    strSql.AppendLine(":MOVELOC_ID,:GOODS_ID,:OLD_LOC_ID,:NEW_LOC_ID,:PLAN_NUM,:REAL_NUM,:MISTOCK_NUM,:UNIT,:SCALE_UNIT,:PUTIN_ID,:DOCCTNUMBER,:SPECIFICATION,:PRODUCTION_DATE,:WARE_DATE,:PROVIDER_ID,:LOC_NUM,:PRICE)");
                    OracleParameter[] parameters = {
                   new OracleParameter(":MOVELOC_ID",OracleDbType.Varchar2),
					new OracleParameter(":GOODS_ID",OracleDbType.Varchar2),
					new OracleParameter(":OLD_LOC_ID",OracleDbType.Varchar2),
					new OracleParameter(":NEW_LOC_ID",OracleDbType.Varchar2),
					new OracleParameter(":PLAN_NUM", OracleDbType.Decimal),
					new OracleParameter(":REAL_NUM", OracleDbType.Decimal),
                   new OracleParameter(":MISTOCK_NUM", OracleDbType.Decimal),
                   new OracleParameter(":UNIT",OracleDbType.Varchar2),
                   new OracleParameter(":SCALE_UNIT",OracleDbType.Varchar2),
					new OracleParameter(":PUTIN_ID",OracleDbType.Varchar2),
					new OracleParameter(":DOCCTNUMBER",OracleDbType.Varchar2),
					new OracleParameter(":SPECIFICATION",OracleDbType.Varchar2),
					new OracleParameter(":PRODUCTION_DATE", OracleDbType.Date),
					new OracleParameter(":WARE_DATE", OracleDbType.Date),
					new OracleParameter(":PROVIDER_ID",OracleDbType.Varchar2),
					new OracleParameter(":LOC_NUM", OracleDbType.Decimal),
                   new OracleParameter(":PRICE",OracleDbType.Varchar2)};
                    parameters[0].Value = model.MOVELOC_ID;
                    parameters[1].Value = model.GOODS_ID;
                    parameters[2].Value = model.OLD_LOC_ID;
                    parameters[3].Value = model.NEW_LOC_ID;
                    parameters[4].Value = model.PLAN_NUM;
                    parameters[5].Value = model.REAL_NUM;
                    parameters[6].Value = model.MISTOCK_NUM;
                    parameters[7].Value = model.UNIT;
                    parameters[8].Value = model.SCALE_UNIT;
                    parameters[9].Value = model.PUTIN_ID;
                    parameters[10].Value = model.DOCCTNUMBER;
                    parameters[11].Value = model.SPECIFICATION;
                    parameters[12].Value = model.PRODUCTION_DATE;
                    parameters[13].Value = model.WARE_DATE;
                    parameters[14].Value = model.PROVIDER_ID;
                    parameters[15].Value = model.LOC_NUM;
                    parameters[16].Value = model.PRICE;

                    SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
                }
                else {
                    strSql.AppendLine("insert into T_STOCK_MOVELOC_TAB(");
                    strSql.AppendLine("MOVELOC_ID,GOODS_ID,OLD_LOC_ID,NEW_LOC_ID,PLAN_NUM,REAL_NUM,MOVELOC_TAB_ID,MISTOCK_NUM,UNIT,SCALE_UNIT,PUTIN_ID,DOCCTNUMBER,SPECIFICATION,PRODUCTION_DATE,WARE_DATE,PROVIDER_ID,LOC_NUM,PRICE)");
                    strSql.AppendLine(" values (");
                    strSql.AppendLine(":MOVELOC_ID,:GOODS_ID,:OLD_LOC_ID,:NEW_LOC_ID,:PLAN_NUM,:REAL_NUM,:MOVELOC_TAB_ID,:MISTOCK_NUM,:UNIT,:SCALE_UNIT,:PUTIN_ID,:DOCCTNUMBER,:SPECIFICATION,:PRODUCTION_DATE,:WARE_DATE,:PROVIDER_ID,:LOC_NUM,:PRICE)");
                    OracleParameter[] parameters = {
                   new OracleParameter(":MOVELOC_ID",OracleDbType.Varchar2),
					new OracleParameter(":GOODS_ID",OracleDbType.Varchar2),
					new OracleParameter(":OLD_LOC_ID",OracleDbType.Varchar2),
					new OracleParameter(":NEW_LOC_ID",OracleDbType.Varchar2),
					new OracleParameter(":PLAN_NUM", OracleDbType.Decimal),
					new OracleParameter(":REAL_NUM", OracleDbType.Decimal),
					new OracleParameter(":MOVELOC_TAB_ID",OracleDbType.Varchar2),
                   new OracleParameter(":MISTOCK_NUM", OracleDbType.Decimal),
                   new OracleParameter(":UNIT",OracleDbType.Varchar2),
                   new OracleParameter(":SCALE_UNIT",OracleDbType.Varchar2),
					new OracleParameter(":PUTIN_ID",OracleDbType.Varchar2),
					new OracleParameter(":DOCCTNUMBER",OracleDbType.Varchar2),
					new OracleParameter(":SPECIFICATION",OracleDbType.Varchar2),
					new OracleParameter(":PRODUCTION_DATE", OracleDbType.Date),
					new OracleParameter(":WARE_DATE", OracleDbType.Date),
					new OracleParameter(":PROVIDER_ID",OracleDbType.Varchar2),
					new OracleParameter(":LOC_NUM", OracleDbType.Decimal),
                   new OracleParameter(":PRICE",OracleDbType.Varchar2)};
                    parameters[0].Value = model.MOVELOC_ID;
                    parameters[1].Value = model.GOODS_ID;
                    parameters[2].Value = model.OLD_LOC_ID;
                    parameters[3].Value = model.NEW_LOC_ID;
                    parameters[4].Value = model.PLAN_NUM;
                    parameters[5].Value = model.REAL_NUM;
                    parameters[6].Value = model.MOVELOC_TAB_ID;
                    parameters[7].Value = model.MISTOCK_NUM;
                    parameters[8].Value = model.UNIT;
                    parameters[9].Value = model.SCALE_UNIT;
                    parameters[10].Value = model.PUTIN_ID;
                    parameters[11].Value = model.DOCCTNUMBER;
                    parameters[12].Value = model.SPECIFICATION;
                    parameters[13].Value = model.PRODUCTION_DATE;
                    parameters[14].Value = model.WARE_DATE;
                    parameters[15].Value = model.PROVIDER_ID;
                    parameters[16].Value = model.LOC_NUM;
                    parameters[17].Value = model.PRICE;

                    SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
                }
                
            //}
        }

        /// <summary>
        /// 修改移位单明细信息
        /// </summary>
        /// <param name="MoveLocTabModel">移位单明细Model</param>
        public void UpdateMoveLocTab(MoveLocTabModel model) {
            StringBuilder strSql = new StringBuilder();

            strSql.AppendLine("update T_STOCK_MOVELOC_TAB set ");
            strSql.AppendLine("MOVELOC_ID=:MOVELOC_ID,");
            strSql.AppendLine("GOODS_ID=:GOODS_ID,");
            strSql.AppendLine("OLD_LOC_ID=:OLD_LOC_ID,");
            strSql.AppendLine("NEW_LOC_ID=:NEW_LOC_ID,");
            strSql.AppendLine("PLAN_NUM=:PLAN_NUM,");
            strSql.AppendLine("REAL_NUM=:REAL_NUM,");
            strSql.AppendLine("unit=:unit,");
            strSql.AppendLine("SCALE_UNIT=:SCALE_UNIT,");
            strSql.AppendLine("MOVELOC_TAB_ID=:MOVELOC_TAB_ID,");
            strSql.AppendLine("PRICE=:PRICE");
            strSql.AppendLine(" where MOVELOC_TAB_ID=:MOVELOC_TAB_ID ");
            OracleParameter[] parameters = {
					new OracleParameter(":MOVELOC_ID",OracleDbType.Varchar2),
					new OracleParameter(":GOODS_ID",OracleDbType.Varchar2),
					new OracleParameter(":OLD_LOC_ID",OracleDbType.Varchar2),
					new OracleParameter(":NEW_LOC_ID",OracleDbType.Varchar2),
					new OracleParameter(":PLAN_NUM", OracleDbType.Decimal),
					new OracleParameter(":REAL_NUM", OracleDbType.Decimal),
                   new OracleParameter(":UNIT",OracleDbType.Varchar2),
                   new OracleParameter(":SCALE_UNIT",OracleDbType.Varchar2),
					new OracleParameter(":MOVELOC_TAB_ID",OracleDbType.Varchar2),
                   new OracleParameter(":PRICE",OracleDbType.Varchar2)};
            parameters[0].Value = model.MOVELOC_ID;
            parameters[1].Value = model.GOODS_ID;
            parameters[2].Value = model.OLD_LOC_ID;
            parameters[3].Value = model.NEW_LOC_ID;
            parameters[4].Value = model.PLAN_NUM;
            parameters[5].Value = model.REAL_NUM;
            parameters[6].Value = model.UNIT;
            parameters[7].Value = model.SCALE_UNIT;
            parameters[8].Value = model.MOVELOC_TAB_ID;
            parameters[9].Value = model.PRICE;

            SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 删除移位单明细信息
        /// </summary>
        /// <param name="ID"></param>
        public void DeleteMoveLocTab(string ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("delete from T_STOCK_MOVELOC_TAB ");
            strSql.AppendLine(" where MOVELOC_ID=:MOVELOC_ID ");
            OracleParameter[] parameters = {
					new OracleParameter(":MOVELOC_ID",OracleDbType.Varchar2)			};
            parameters[0].Value = ID;

            SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters); 
        }

        /// <summary>
        /// 通过移位单编号删除移位单明细
        /// </summary>
        /// <param name="ID"></param>
        public void DeleteMoveLocTabByMoveLocID(string ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("delete from T_STOCK_MOVELOC_TAB ");
            strSql.AppendLine(" where MOVELOC_TAB_ID=:MOVELOC_TAB_ID ");
            OracleParameter[] parameters = {
					new OracleParameter(":MOVELOC_TAB_ID",OracleDbType.Varchar2)			};
            parameters[0].Value = ID;

            SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 向备份表中添加移位单明细
        /// </summary>
        /// <param name="moveLocID">移位单编号</param>
        public void InsertMoveLocTabBack(string moveLocID)
        {
            string strSql = "insert into T_STOCK_MOVELOC_TAB_BACK select * from T_STOCK_MOVELOC_TAB where MOVELOC_ID =:MOVELOC_ID";
            OracleParameter[] parameters = {
				new OracleParameter(":MOVELOC_ID",OracleDbType.Varchar2)};
            parameters[0].Value = moveLocID;

            SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);

        }


    }
}