using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WMS.IData;using Oracle.ManagedDataAccess.Client;
using WMS.Model.Stock;

using WMS.DBUtility;
using System.Data;
using WMS.IData.IStock;

namespace WMS.SqlServerData.StockData
{

	public partial class CountingData :ICounting
	{
		#region  BasicMethod

		/// <summary>
		/// 增加一条数据
		/// </summary>
        public void Add(CountingModel model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("insert into T_STOCK_COUNTING(");
			strSql.Append("COUNT_ID,COUNT_DATE,COUNT_MAN,CONFIRM_MAN,CONFIRM_DATE,PLAN_MAN,PLAN_DATE,REVIEW_MAN,REVIEW_DATE,STO_ID,COUNT_TYPE,REMARK,COUNT_BACK,COUNT_STATUS,CREATE_TYPE,ori_id)");
			strSql.Append(" values (");
			strSql.Append(":COUNT_ID,:COUNT_DATE,:COUNT_MAN,:CONFIRM_MAN,:CONFIRM_DATE,:PLAN_MAN,:PLAN_DATE,:REVIEW_MAN,:REVIEW_DATE,:STO_ID,:COUNT_TYPE,:REMARK,:COUNT_BACK,:COUNT_STATUS,:CREATE_TYPE,:ori_id)");
			OracleParameter[] parameters = {
					new OracleParameter(":COUNT_ID",OracleDbType.Varchar2),
					new OracleParameter(":COUNT_DATE", OracleDbType.Date),
					new OracleParameter(":COUNT_MAN",OracleDbType.Varchar2),
					new OracleParameter(":CONFIRM_MAN",OracleDbType.Varchar2),
					new OracleParameter(":CONFIRM_DATE", OracleDbType.Date),
					new OracleParameter(":PLAN_MAN",OracleDbType.Varchar2),
					new OracleParameter(":PLAN_DATE", OracleDbType.Date),
					new OracleParameter(":REVIEW_MAN",OracleDbType.Varchar2),
					new OracleParameter(":REVIEW_DATE", OracleDbType.Date),
					new OracleParameter(":STO_ID",OracleDbType.Varchar2),
					new OracleParameter(":COUNT_TYPE",OracleDbType.Varchar2),
					new OracleParameter(":REMARK",OracleDbType.Varchar2),
					new OracleParameter(":COUNT_BACK",OracleDbType.Varchar2),
					new OracleParameter(":COUNT_STATUS",OracleDbType.Varchar2),
					new OracleParameter(":CREATE_TYPE",OracleDbType.Varchar2),
                   new OracleParameter(":ori_id",OracleDbType.Varchar2)};
			parameters[0].Value = model.COUNT_ID;
			parameters[1].Value = model.COUNT_DATE;
			parameters[2].Value = model.COUNT_MAN;
			parameters[3].Value = model.CONFIRM_MAN;
			parameters[4].Value = model.CONFIRM_DATE;
			parameters[5].Value = model.PLAN_MAN;
			parameters[6].Value = model.PLAN_DATE;
			parameters[7].Value = model.REVIEW_MAN;
			parameters[8].Value = model.REVIEW_DATE;
			parameters[9].Value = model.STO_ID;
			parameters[10].Value = model.COUNT_TYPE;
			parameters[11].Value = model.REMARK;
			parameters[12].Value = model.COUNT_BACK;
			parameters[13].Value = model.COUNT_STATUS;
			parameters[14].Value = model.CREATE_TYPE;
            parameters[15].Value = model.ORI_ID;

            SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
		}
		/// <summary>
		/// 更新一条数据
		/// </summary>
		public void Update(CountingModel model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("update T_STOCK_COUNTING set ");
			strSql.Append("COUNT_DATE=:COUNT_DATE,");
			strSql.Append("COUNT_MAN=:COUNT_MAN,");
			strSql.Append("CONFIRM_MAN=:CONFIRM_MAN,");
			strSql.Append("CONFIRM_DATE=:CONFIRM_DATE,");
			strSql.Append("PLAN_MAN=:PLAN_MAN,");
			strSql.Append("PLAN_DATE=:PLAN_DATE,");
			strSql.Append("REVIEW_MAN=:REVIEW_MAN,");
			strSql.Append("REVIEW_DATE=:REVIEW_DATE,");
			strSql.Append("STO_ID=:STO_ID,");
			strSql.Append("COUNT_TYPE=:COUNT_TYPE,");
			strSql.Append("REMARK=:REMARK,");
			strSql.Append("COUNT_BACK=:COUNT_BACK,");
			strSql.Append("COUNT_STATUS=:COUNT_STATUS,");
            strSql.Append("CREATE_TYPE=:CREATE_TYPE,");
            strSql.Append("ori_id=:ori_id");
			strSql.Append(" where COUNT_ID=:COUNT_ID ");
			OracleParameter[] parameters = {
					new OracleParameter(":COUNT_DATE", OracleDbType.Date),
					new OracleParameter(":COUNT_MAN",OracleDbType.Varchar2),
					new OracleParameter(":CONFIRM_MAN",OracleDbType.Varchar2),
					new OracleParameter(":CONFIRM_DATE", OracleDbType.Date),
					new OracleParameter(":PLAN_MAN",OracleDbType.Varchar2),
					new OracleParameter(":PLAN_DATE", OracleDbType.Date),
					new OracleParameter(":REVIEW_MAN",OracleDbType.Varchar2),
					new OracleParameter(":REVIEW_DATE", OracleDbType.Date),
					new OracleParameter(":STO_ID",OracleDbType.Varchar2),
					new OracleParameter(":COUNT_TYPE",OracleDbType.Varchar2),
					new OracleParameter(":REMARK",OracleDbType.Varchar2),
					new OracleParameter(":COUNT_BACK",OracleDbType.Varchar2),
					new OracleParameter(":COUNT_STATUS",OracleDbType.Varchar2),
					new OracleParameter(":CREATE_TYPE",OracleDbType.Varchar2),
                   new OracleParameter(":ori_id",OracleDbType.Varchar2),
					new OracleParameter(":COUNT_ID",OracleDbType.Varchar2),
};
			parameters[0].Value = model.COUNT_DATE;
			parameters[1].Value = model.COUNT_MAN;
			parameters[2].Value = model.CONFIRM_MAN;
			parameters[3].Value = model.CONFIRM_DATE;
			parameters[4].Value = model.PLAN_MAN;
			parameters[5].Value = model.PLAN_DATE;
			parameters[6].Value = model.REVIEW_MAN;
			parameters[7].Value = model.REVIEW_DATE;
			parameters[8].Value = model.STO_ID;
			parameters[9].Value = model.COUNT_TYPE;
			parameters[10].Value = model.REMARK;
			parameters[11].Value = model.COUNT_BACK;
			parameters[12].Value = model.COUNT_STATUS;
			parameters[13].Value = model.CREATE_TYPE;
            parameters[14].Value = model.ORI_ID;
            parameters[15].Value = model.COUNT_ID;

            SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
		}
        /// <summary>
		/// 更新Status
		/// </summary>
        public void UpdateImport(string id,string status)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_STOCK_COUNTING set ");
            strSql.Append("COUNT_STATUS=:COUNT_STATUS");
            strSql.Append(" where COUNT_ID=:COUNT_ID ");
            OracleParameter[] parameters = {
					new OracleParameter(":COUNT_STATUS",OracleDbType.Varchar2),
					new OracleParameter(":COUNT_ID",OracleDbType.Varchar2),};
            parameters[0].Value = status;
            parameters[1].Value = id;
           SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);

        }

		/// <summary>
		/// 删除一条数据
		/// </summary>
		public void Delete(string COUNT_ID)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from T_STOCK_COUNTING ");
			strSql.Append(" where COUNT_ID=:COUNT_ID ");
			OracleParameter[] parameters = {
					new OracleParameter(":COUNT_ID",OracleDbType.Varchar2)			};
			parameters[0].Value = COUNT_ID;

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

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public void Delete(CountingModel model)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from T_STOCK_COUNTING ");
            strSql.Append(" where COUNT_ID=:COUNT_ID ");
            OracleParameter[] parameters = {
					new OracleParameter(":COUNT_ID",OracleDbType.Varchar2)			};
            parameters[0].Value = model.COUNT_ID;

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


		/// <summary>
		/// 获得数据列表
		/// </summary>
        public DataTable GetList(CountingModel model)
		{
			StringBuilder strSql=new StringBuilder();
            strSql.Append("select t.COUNT_ID,");
            strSql.Append("t.COUNT_DATE,");
            strSql.Append("t.COUNT_MAN,");
            strSql.Append("a.NAME COUNT_MAN_NAME,");
            strSql.Append("t.CONFIRM_MAN,");
            strSql.Append("b.NAME CONFIRM_MAN_NAME,");
            strSql.Append("t.CONFIRM_DATE,");
            strSql.Append("t.PLAN_MAN,");
            strSql.Append("c.NAME PLAN_MAN_NAME,");
            strSql.Append("t.PLAN_DATE,");
            strSql.Append("t.REVIEW_MAN,");
            strSql.Append("d.NAME REVIEW_MAN_NAME,");
            strSql.Append("t.REVIEW_DATE,");
            strSql.Append("t.STO_ID,");
            strSql.Append("e.STORAGE_NAME STO_NAME,");
            strSql.Append("t.COUNT_TYPE,");
            strSql.Append("t.REMARK,");
            strSql.Append("t.COUNT_BACK,");
            strSql.Append("t.COUNT_STATUS,");
            strSql.Append("t.ori_id,");
            strSql.Append("t.CREATE_TYPE ");
			strSql.Append(" FROM T_STOCK_COUNTING t");
            strSql.Append(" LEFT JOIN T_BASE_USERINFO a ON a.USER_ID = t.COUNT_MAN");
            strSql.Append(" LEFT JOIN T_BASE_USERINFO b ON b.USER_ID = t.CONFIRM_MAN");
            strSql.Append(" LEFT JOIN T_BASE_USERINFO c ON c.USER_ID = t.PLAN_MAN");
            strSql.Append(" LEFT JOIN T_BASE_USERINFO d ON d.USER_ID = t.REVIEW_MAN");
            strSql.Append(" LEFT JOIN T_BASE_STORAGEINFO e ON e.STORAGE_ID = t.STO_ID");
            strSql.Append(" where 1=1 ");
			if(!string.IsNullOrEmpty(model.COUNT_ID))
			{
                strSql.Append(" and t.COUNT_ID like '%" + model.COUNT_ID + "%'");
			}
            if (!string.IsNullOrEmpty(model.STO_ID))
            {
                strSql.Append(" and t.STO_ID = '" + model.STO_ID + "'");
            }
            if (!string.IsNullOrEmpty(model.COUNT_STATUS))
            {
                strSql.Append(" and t.COUNT_STATUS = '" + model.COUNT_STATUS + "'");
            }
            if (!string.IsNullOrEmpty(model.COUNT_TYPE))
            {
                strSql.Append(" and t.COUNT_TYPE = '" + model.COUNT_TYPE + "'");
            }
            if (model.PLAN_DATE_STAR != DateTime.MinValue && model.PLAN_DATE_END != DateTime.MinValue)
            {
                strSql.Append(" and t.PLAN_DATE between convert(varchar(100),'" + model.PLAN_DATE_STAR.ToString("yyyy-MM-dd") + "',20) and convert(varchar(100),'" + model.PLAN_DATE_END.ToString("yyyy-MM-dd") + "',20)");
            }
            return SystemDataObject.Instance.GetDataTable(strSql.ToString());
		}
		#endregion  BasicMethod

        /// <summary>
        /// 获取流水号
        /// </summary>
        /// <returns></returns>
        public string GetSerialNum()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select NEXT VALUE FOR seq_stock_counting");
            DataSet ds =   SystemDataObject.Instance.GetDataSet( strSql.ToString());
            return ds.Tables[0].Rows[0][0].ToString();
        }
	}
}