using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using System.Data.SqlClient;
using ApServerProvider;
using DbCommon;
using System.Collections;

namespace Estsh.Core.Repositories
{

    /**
     * 客户产线对应关系
     * 
     * NOAH
     * 
     */
    public class SRMDefineDal : BaseApp
    {
        public SRMDefineDal(RemotingProxy remotingProxy)
            : base(remotingProxy)
        {
        }
        #region  成员方法
        /// <summary>
        /// 获得菜单列表数据
        /// </summary>
        public DataTable getList(string strWhere, string filedOrder)
        {
            lock (_remotingProxy)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select a.*,b.cust_pdline_name from sys_model_type a LEFT JOIN dbo.sys_cust_pdline b ON a.cust_pdline_id = b.cust_pdline_id ");
                if (!strWhere.Trim().Equals(""))
                {
                    strSql.Append(" where " + strWhere);
                }
                if (filedOrder != null && !filedOrder.Trim().Equals(""))
                {
                    strSql.Append(" order by " + filedOrder);
                }
                return this._remotingProxy.GetDataTable(strSql.ToString());
            }
        }

        /// <summary>
        /// 获取分页数据列表
        /// </summary>
        public DataTable getListByPage(int PageSize, int PageIndex, string strWhere, string OrderBy)
        {
            lock (_remotingProxy)
            {
                if (strWhere.Trim() != "")
                {
                    strWhere = strWhere.Remove(0, 6);//因为存储过程中已经有where，这里需要把之前加入的where去掉，这里截除前六个字符
                }

                OrderBy = " importNo DESC,crossingPointNo ";  //排序

                Hashtable Params = new Hashtable(6);

                Params.Add("@TotalCount", 100);
                Params.Add("@TotalPage", 100);
                Params.Add("@GroupColumn", "");
                Params.Add("@Table", "g_srm");
                Params.Add("@Column", @"ruid,importNo,crossingPointNo,partNo,partName,vendorCode,vendorName,xuQiuQty,VIN,
                                        Convert(varchar(20),dingDanTime,120) as dingDanTime ,dingDanNo,
                                        Convert(varchar(20),zhiDanTime,120) as zhiDanTime,dingDanStatus,danJuPrint,
                                        Convert(varchar(20),huiZhiTime,120) as huiZhiTime,Convert(varchar(20),updateTime,120) as updateTime,
                                        serial_number,Convert(varchar(20),inTime,120) as inTime,rfid,CASE status WHEN 0 THEN '未校验' WHEN 1 THEN '已校验' END AS status,
                                        CASE scan_status WHEN 0 THEN '未扫描' WHEN 1 THEN '已扫描' END AS scan_status,create_ymd+' '+create_hms AS importTime");
                Params.Add("@PageSize", PageSize);
                Params.Add("@CurrentPage", PageIndex);
                Params.Add("@Condition", strWhere);
                Params.Add("@OrderColumn", OrderBy);
                Params.Add("@Group", 0);

                String strProcedure = "EXEC Com_Pagination @TotalCount, @TotalPage, @Table, @Column,@OrderColumn,@GroupColumn,@PageSize, @CurrentPage,@Group,@Condition";
                return _remotingProxy.GetDataTable(strProcedure, Params);
            }
        }

        /// <summary>
        /// 获取分页记录总数
        /// </summary>
        public int getCountWhere(string strWhere)
        {
            lock (_remotingProxy)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select count(*) as Total FROM g_srm ");
                if (strWhere.Trim() != "")
                {
                    strSql.Append(" " + strWhere);
                }
                int i = 0;
                DataTable dt = _remotingProxy.GetDataTable(strSql.ToString());
                if (dt.Rows.Count > 0)
                {
                    if (dt.Rows[0]["Total"].ToString() != "")
                    {
                        i = int.Parse(dt.Rows[0]["Total"].ToString());
                    }
                }
                return i;
            }
        }

        /// <summary>
        /// 插入菜单数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int saveModelType(Hashtable htParams)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("INSERT INTO dbo.sys_model_type(type_code,type_name,type_alias,type_desc,cust_pdline_id,create_userid,create_ymd,create_hms) values");
                SqlStringBuilder.Append("(@type_code,@type_name,@type_alias,@type_desc,@cust_pdline_id,@create_userid,Convert(varchar(10),getdate(),23),Convert(varchar(10),getdate(),108))");

                return _remotingProxy.ExecuteNonQuery(SqlStringBuilder.ToString(), htParams);
            }
        }

        /// <summary>
        /// 更新菜单数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int updateModelType(Hashtable htParams)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("UPDATE sys_model_type SET  ");
                SqlStringBuilder.Append("type_code=@type_code,type_name=@type_name,type_alias=@type_alias,type_desc=@type_desc,cust_pdline_id=@cust_pdline_id,update_userid=@update_userid,update_ymd=Convert(varchar(10),getdate(),23),update_hms=Convert(varchar(10),getdate(),108) ");
                SqlStringBuilder.Append("WHERE type_id=@type_id ");

                return _remotingProxy.ExecuteNonQuery(SqlStringBuilder.ToString(), htParams);
            }
        }

        /// <summary>
        /// 删除菜单数据
        /// </summary>
        /// <param name="type_id"></param>
        /// <returns></returns>
        public int deleteSRM(String ruid)
        {
            lock (_remotingProxy)
            {
                //status=0 AND del ='N'已发运的不能删除，已删除的不显示
                String delStr = @"UPDATE  g_srm
SET     status=1,del='Y',importNo=0,VIN = ( SELECT  VIN
                FROM    dbo.g_srm
                WHERE   ruid = @ruid
              ) + '-DEL' +  REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),121),'-',''),':',''),' ',''),'.','')
WHERE   ruid = @ruid AND status=0 AND del ='N'";
                Hashtable htparams = new Hashtable();
                htparams.Add("@ruid", ruid);
                return _remotingProxy.ExecuteNonQuery(delStr, htparams);
            }
        }

        /// <summary>
        /// 获取VIN号
        /// </summary>
        /// <param name="VIN"></param>
        /// <returns></returns>
        public string GetVIN(string VIN)
        {
            lock (_remotingProxy)
            {
                string SqlString = "SELECT VIN FROM dbo.g_srm WHERE VIN =@VIN";

                Hashtable Params = new Hashtable(1);

                Params.Add("@VIN", VIN);

                object obj = _remotingProxy.GetScalar(SqlString, Params);

                return obj != null ? Convert.ToString(obj) : null;
            }
        }

        /// <summary>
        /// 客户零件号是否存在
        /// </summary>
        /// <returns></returns>
        public string GetCustPart(string custPart)
        {
            lock (_remotingProxy)
            {
                string SqlString = "SELECT cust_part_no FROM dbo.sys_part WHERE part_type = 1 AND cust_part_no = @custPart";
                Hashtable Params = new Hashtable(1);

                Params.Add("@custPart", custPart);

                object obj = _remotingProxy.GetScalar(SqlString, Params);

                return obj != null ? Convert.ToString(obj) : null;
            }
        }

        /// <summary>
        /// 获取系统最后一次导入序号
        /// </summary>
        /// <returns></returns>
        public int GetImportNo()
        {
            lock (_remotingProxy)
            {
                string SqlString = "SELECT ISNULL(MAX(importNo),0) FROM dbo.g_srm";

                object obj = _remotingProxy.GetScalar(SqlString);

                return obj != null ? Convert.ToInt32(obj) : 0;
            }
        }

        /// <summary>
        /// 导入EXCEL时插入数据
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public int InsertData(DataTable dt, int importNo, int userId, string pdlineOff)
        {
            lock (_remotingProxy)
            {
                List<String> SqlStrings = new List<string>(dt.Rows.Count);

                List<String> ModelCache = new List<string>(dt.Rows.Count);

                List<Hashtable> hs = new List<Hashtable>(dt.Rows.Count);
                DataTable dataTime = getDataTime();
                for (int i = 0; i < dt.Rows.Count; i++)
                {


                    string CplID = dt.Rows[i][4].ToString().Trim();

                    StringBuilder SqlStringBuilder = new StringBuilder(1024);

             
                        SqlStringBuilder.Append(@"INSERT  INTO dbo.g_srm
                                              ( importNo ,dingDanNo ,zhiDanTime ,dingDanTime ,peiSongType ,
                                                dingDanStatus ,danJuPrint , factory ,vendorCode ,vendorName ,
                                                partNo ,partName ,danWei ,zhuangPeiNo ,kuWei ,
                                                daoKou ,xuQiuQty ,shouHuoQty ,line ,partTypeNo ,
                                                partTypeName ,pickTime ,path ,jianYiDaoDaTime ,qiWangDaoDaTime ,
                                                vendorShipTime ,firstCrossingPointTime ,chaiFenNo ,chaiFenPartNo ,chaiFenBoxNo ,
                                                partQty ,crossingPointNo ,VIN ,daoHuoTime ,shouHuoRen ,
                                                shouHuoSynTime ,huiZhiTime ,updateTime ,zhiDanRen ,enabled ,
                                                planStatus ,shipStatus ,create_userid ,create_ymd ,create_hms,status,location_status
                                              )");
                        SqlStringBuilder.Append("  values( ");
                        SqlStringBuilder.Append(@"@importNo ,@dingDanNo ,@zhiDanTime ,@dingDanTime ,@peiSongType ,
                                                @dingDanStatus ,@danJuPrint , @factory ,@vendorCode ,@vendorName ,
                                                @partNo ,@partName ,@danWei ,@zhuangPeiNo ,@kuWei ,
                                                @daoKou ,@xuQiuQty ,@shouHuoQty ,@line ,@partTypeNo ,
                                                @partTypeName ,@pickTime ,@path ,@jianYiDaoDaTime ,@qiWangDaoDaTime ,
                                                @vendorShipTime ,@firstCrossingPointTime ,@chaiFenNo ,@chaiFenPartNo ,@chaiFenBoxNo ,
                                                @partQty ,@crossingPointNo ,@VIN ,@daoHuoTime ,@shouHuoRen ,
                                                @shouHuoSynTime ,@huiZhiTime ,@updateTime ,@zhiDanRen ,@enabled ,
                                                @planStatus ,@shipStatus ,@create_userid ,@create_ymd ,@create_hms,@status,@location_status
                                              )");
                    


                    Hashtable values = new Hashtable(51);
                    values.Add("@importNo", importNo);
                    values.Add("@dingDanNo", dt.Rows[i][0].ToString());
                    values.Add("@zhiDanTime", dt.Rows[i][1].ToString());
                    values.Add("@dingDanTime", dt.Rows[i][2].ToString());
                    values.Add("@peiSongType", dt.Rows[i][3].ToString());
                    values.Add("@dingDanStatus", dt.Rows[i][4].ToString());
                    values.Add("@danJuPrint", dt.Rows[i][5].ToString());
                    values.Add("@factory", dt.Rows[i][6].ToString());
                    values.Add("@vendorCode", dt.Rows[i][7].ToString());
                    values.Add("@vendorName", dt.Rows[i][8].ToString());
                    values.Add("@partNo", dt.Rows[i][9].ToString());
                    values.Add("@partName", dt.Rows[i][10].ToString());
                    values.Add("@danWei", dt.Rows[i][11].ToString());
                    values.Add("@zhuangPeiNo", dt.Rows[i][12].ToString());
                    values.Add("@kuWei", dt.Rows[i][13].ToString());
                    values.Add("@daoKou", dt.Rows[i][14].ToString());
                    values.Add("@xuQiuQty", dt.Rows[i][15].ToString());
                    values.Add("@shouHuoQty", dt.Rows[i][16].ToString());
                    values.Add("@line", dt.Rows[i][17].ToString());
                    values.Add("@partTypeNo", dt.Rows[i][18].ToString());
                    values.Add("@partTypeName", dt.Rows[i][19].ToString());
                    values.Add("@pickTime", dt.Rows[i][20].ToString());
                    values.Add("@path", dt.Rows[i][21].ToString());
                    values.Add("@jianYiDaoDaTime", dt.Rows[i][22].ToString());
                    values.Add("@qiWangDaoDaTime", dt.Rows[i][23].ToString());
                    values.Add("@vendorShipTime", dt.Rows[i][24].ToString());
                    values.Add("@firstCrossingPointTime", dt.Rows[i][25].ToString());
                    values.Add("@chaiFenNo", dt.Rows[i][26].ToString());
                    values.Add("@chaiFenPartNo", dt.Rows[i][27].ToString());
                    values.Add("@chaiFenBoxNo", dt.Rows[i][28].ToString());
                    values.Add("@partQty", dt.Rows[i][29].ToString());
                    values.Add("@crossingPointNo", dt.Rows[i][30].ToString());
                    values.Add("@VIN", dt.Rows[i][31].ToString());
                    values.Add("@daoHuoTime", dt.Rows[i][32].ToString());
                    values.Add("@shouHuoRen", dt.Rows[i][33].ToString());
                    values.Add("@shouHuoSynTime", dt.Rows[i][34].ToString());
                    values.Add("@huiZhiTime", dt.Rows[i][35].ToString());
                    values.Add("@updateTime", dt.Rows[i][36].ToString());
                    values.Add("@zhiDanRen", dt.Rows[i][37].ToString());

                    values.Add("@enabled", "Y");
                    if (pdlineOff.Equals("1"))
                    {
                        values.Add("@planStatus", 0);
                    }
                    else
                    {
                        values.Add("@planStatus", 1);
                    }
                    values.Add("@shipStatus", 0);

                    values.Add("@create_userid", userId);


                    values.Add("@create_ymd", dataTime.Rows[0][0].ToString());
                    values.Add("@create_hms", dataTime.Rows[0][1].ToString());
                    values.Add("@status", 0);
                    values.Add("@location_status", 0);
                    SqlStrings.Add(SqlStringBuilder.ToString());

                    hs.Add(values);
                }
                if (pdlineOff.Equals("1"))
                {
                    //生成排产计划
                    if (_remotingProxy.ExecuteSqlTransaction(SqlStrings, hs))
                    {
                        List<StoreProcedureParameter> Parameters = new List<StoreProcedureParameter>();

                        Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Output, "@tres", 500));

                        Hashtable ht = _remotingProxy.ExecuteSotreProcedure("dbo.sys_release_order_srm", Parameters);

                        if (!ht.ContainsKey("@tres"))
                        {
                            //导入成功释放失败
                            return 2;
                        }
                        else
                        {
                            //导入成功并释放成功
                            return 1;
                        }

                    }
                    else
                    {
                        //导入失败
                        return 3;
                    }

                }
                else if (pdlineOff.Equals("2"))
                {
                    if (_remotingProxy.ExecuteSqlTransaction(SqlStrings, hs))
                    {
                        //只生成SRM发运计划
                        return 4;
                    }

                }
                return 3;

            }
        }

        /// <summary>
        /// 获取时间
        /// </summary>
        /// <returns></returns>
        public DataTable getDataTime()
        {
            lock (_remotingProxy)
            {
                string SqlString = "select Convert(varchar(10),getdate(),23),Convert(varchar(10),getdate(),108)";



                return _remotingProxy.GetDataTable(SqlString);
            }
        }

        #endregion  成员方法

    }
}