using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Commom.Utility;
using Common;
using Model;

namespace BLL
{
    public class DALMatNo : IDALMatNo
    {
        private readonly object balanceLock = new object();

        public bool Add(Material model, string loginUser)
        {
            bool result = false;
            try
            {
                Hashtable ht = new Hashtable();

                ht["MatNo"]          = "'" + model.MatNo + "'";
                ht["MatName"]        = "'" + model.MatName + "'";
                ht["PackFormat"]     = "'" + model.PackFormat + "'";
                ht["Unit"]           = model.Unit.AddQuotes();
                ht["NWeight"]        = "'" + model.Nweight + "'";
                ht["GWeight"]        = "'" + model.Gweight + "'";                
                ht["StoreCondition"] = "'" + model.StoreCondition + "'";
                ht["Long"]       = "'" + model.Long + "'";
                ht["Width"]      = "'" + model.Width + "'";
                ht["Height"]     = "'" + model.Height + "'";
                ht["Price"]      = "'" + model.Price + "'";
                ht["Demo"]       = "'" + model.Demo + "'";
                ht["CreateUser"] = "'" + loginUser + "'";
                ht["IsDel"]      = " 0 ";
                ht["BrandId"] = model.BrandId.AddQuotes();
                ht["MatType"] = model.MatTypeId.AddQuotes();

                int _ret = DataFactory.SqlDataBase().InsertByHashtableNullParam("Material", ht);

                if (_ret == 1) result = true;

                return result;

            }
            catch
            {
                return result;
            }
        }

        public bool BatchDelete(string[] guids, string loginUser)
        {
            bool result = false;
            try
            {
                int rowCount = 0;
                foreach (string guid in guids) 
                {
                    StringBuilder sql = new StringBuilder();
                    sql.Append("Update Material set isdel = '1',UpdateUser = "+loginUser.AddQuotes()+"" +
                        ",UpdateTime = GETDATE() where guid=" + guid.AddQuotes() + "");
                    rowCount += DataFactory.SqlDataBase().ExecuteBySql(sql);
                }

                if (rowCount >= guids.Length) 
                {
                    result = true;
                }

                return result;
            }
            catch
            {
                return result;
            }
        }

        /// <summary>
        /// 验证该物料是否存在库存
        /// </summary>
        /// <param name="DepartNum"></param>
        /// <returns></returns>
        public string IsChecks(string[] guids)
        {
            try
            {
                string result = "";
                //foreach (string guid in guids)
                //{
                //    StringBuilder sql = new StringBuilder();
                //    sql.Append("select Quant from Log_Stroe_Mat where MatNo = '"+ guid + "'");
                //    DataTable tb = DataFactory.SqlDataBase().GetDataTableBySQL(sql);
                //    if (tb.Rows.Count > 0)
                //    {
                //        if (tb.Rows[0][0].ToString() != "0") 
                //        {
                //            result += guid + "剩余库存:" + tb.Rows[0][0].ToString() + ";";
                //            continue;
                //        }
                //    }

                //    sql.Clear();
                //    sql.Append("select count(*) from ErpInDetail where MatNo = '" + matNo + "'");
                //    DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(sql);
                //    if (dt.Rows[0][0].ToString() != "0")
                //    {
                //        result += matNo + ":已关联单据,禁止删除。";
                //    }
                //}

                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool Delete(string MatNo)
        {
            throw new NotImplementedException();
        }

        public DataTable GetDataTable(string[] strWhere)
        {
            DataTable dt = null;
            try
            {
                int index = 0;
                string str = "@MatNo" + index;
                SqlParam[] param = new SqlParam[strWhere.Length];
                StringBuilder sql = new StringBuilder();
                sql.Append("Select MatNo as 物料编码,MatName as 物料名称,packFormat as 包装规格,PackQuant as 包装量,UnitFrist as 计量单位(主),UnitSec as 计量单位(辅),Matweighttype as 物料轻重,storecondition as 储存环境,usefreq as 频率,sizeNum as 包装尺寸 FROM MatNo where MatNo in (");

                for (int i = 0; i < param.Length - 1; i++)
                {
                    string obj2 = strWhere[i];
                    str = "@MatNo" + index;
                    sql.Append(str).Append(",");
                    param[index] = new SqlParam(str, obj2);
                    index++;
                }
                str = "@MatNo" + index;
                sql.Append(str);
                param[index] = new SqlParam(str, strWhere[index]);
                sql.Append(") and IsDel = 0");

                dt = DataFactory.SqlDataBase().GetDataTableBySQL(sql, param, "");

                return dt;

            }
            catch
            {
                throw new NotImplementedException();
            }
        }

        public DataTable GetDataTable(int PageSize, int PageIndex, string strWhere)
        {
            throw new NotImplementedException();
        }

        public IList<Material> GetList(AjaxMatList Json, ref PageInfo pageInfo)
        {
            try
            {
                IList<Material> list = new List<Material>();
                StringBuilder strSql = new StringBuilder();
                List<SqlParam> para = new List<SqlParam>();
                strSql.Append("Select * from View_Material where isdel = 0 ");

                if (Json.MatNo != null && Json.MatNo != "")
                {
                    strSql.Append(" and MatNo like '%' + @MatNo + '%' ");
                    para.Add(new SqlParam("@MatNo", Json.MatNo));
                }
                if (Json.MatName != null && Json.MatName != "")
                {
                    strSql.Append("and MatName like '%' + @MatName + '%'  ");
                    para.Add(new SqlParam("@MatName", Json.MatName));
                }
                if (Json.PackFormat != null && Json.PackFormat != "") 
                {
                    strSql.Append("and PackFormat like '%' + @PackFormat + '%'  ");
                    para.Add(new SqlParam("@PackFormat", Json.PackFormat));
                }
                if (!string.IsNullOrEmpty(Json.BrandId)) 
                {
                    strSql.Append("and BrandId =  @BrandId  ");
                    para.Add(new SqlParam("@BrandId", Json.BrandId));
                }
                if (!string.IsNullOrEmpty(Json.MatTypeId)) 
                {
                    strSql.Append("and MatTypeId = @MatTypeId ");
                    para.Add(new SqlParam("@MatTypeId", Json.MatTypeId));
                }
                if (Json.IsDel != -1) 
                {
                    strSql.Append("and IsDel = @IsDel ");
                    para.Add(new SqlParam("@IsDel", Json.IsDel));
                }
                
                SqlParam[] param = null;
                if (para != null)
                    param = para.ToArray();

                DataTable dt = DataFactory.SqlDataBase().GetPageList(strSql.ToString(), param, "CreateTime", "DESC", ref pageInfo);
                list = ModelConvertHelper<Material>.DataTableToModel(dt);

                return list;
            }
            catch
            {
                throw new NotImplementedException();
            }
        }

        public IList<Material> GetList(string MatNo)
        {
            try
            {
                IList<Material> list = new List<Material>();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("Select top 5 * from view_Material where ");
                strSql.Append("MatNo like '%' + @MatNo + '%' order by MatNo ");

                SqlParam[] para = new SqlParam[]
                {
                    new SqlParam("@MatNo", MatNo),
                };

                DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(strSql, para,"");

                list = ModelConvertHelper<Material>.DataTableToModel(dt);

                return list;
            }
            catch
            {
                throw new NotImplementedException();
            }
        }

        public Material GetModel(string guid)
        {
            try
            {
                Material us = null;
                StringBuilder strSql = new StringBuilder();
                strSql.Append("Select * from view_Material where ");
                strSql.Append("guid = @guid;");
              
                SqlParam[] para = new SqlParam[]
                {
                    new SqlParam("@guid", guid),
                };

                IDataReader dt = DataFactory.SqlDataBase().GetDataReaderBySQL(strSql, para);

                us = ModelConvertHelper<Material>.ReaderToModel(dt);

                return us;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool IsExist(string name, string value)
        {
            bool result = false;
            try
            {
                string[] para = new string[] { value };
                int dt = DataFactory.SqlDataBase().IsExist("Material", name, para);
                if (dt > 0) result = true;
                return result;
            }
            catch
            {
                throw new NotImplementedException();
            }
        }
        public bool IsExist(Hashtable ht)
        {
            bool result = false;
            try
            {
                int dt = DataFactory.SqlDataBase().IsExist("Material", ht);
                if (dt > 0) result = true;
                return result;
            }
            catch
            {
                throw new NotImplementedException();
            }
        }

        public bool Update(Material model)
        {
            bool result = false;
            try
            {
                Hashtable ht = new Hashtable();

                ht["MatName"] = string.IsNullOrEmpty(model.MatName) ? "''" : "'" + model.MatName + "'";
                ht["PackFormat"] = string.IsNullOrEmpty(model.PackFormat) ? "''" : "'" + model.PackFormat + "'";
                ht["Unit"] = model.Unit.AddQuotes();
                ht["Demo"] = string.IsNullOrEmpty(model.Demo) ? "''" : "'" + model.Demo + "'";
                ht["UpdateUser"] = "'" + model.UpdateUser + "'";
                ht["UpdateTime"] = "getdate()";
                ht["NWeight"] = "'" + model.Nweight + "'";
                ht["Long"] = "'" + model.Long + "'";
                ht["Width"] = "'" + model.Width + "'";
                ht["Height"] = "'" + model.Height + "'";
                ht["BrandId"] = model.BrandId.AddQuotes();
                ht["MatType"] = model.MatTypeId.AddQuotes();

                string MatNo = "'" + model.MatNo + "'";

                int _ret = DataFactory.SqlDataBase().UpdateByHashtable("Material", "MatNo", MatNo, ht);

                if (_ret == 1) result = true;

                return result;

            }
            catch
            {
                return result;
            }

           
        }


        public bool AuditMat(string[] MatNo, string AuditModel,string user)
        {
            bool result = false;
            try
            {
                int dt = DataFactory.SqlDataBase().IsExist("Material", "MatNo", MatNo);
                if (dt >= MatNo.Length)
                {
                    Hashtable ht = new Hashtable();

                    ht["AuditFlag"] = string.IsNullOrEmpty(AuditModel) ? "'AD01'" :"'" + AuditModel + "'";
                    ht["AuditUser"] = "'" + user + "'";
                    ht["AuditTime"] = "getdate()";
                    int _ret = DataFactory.SqlDataBase().UpdateByHashtableA("Material", "MatNo", MatNo, ht);

                    if (_ret >= MatNo.Length) result = true;
                }
                return result;

            }
            catch
            {
                return result;
            }
        }

        public string GetMatNo() 
        {
            lock (balanceLock)
            {
                string matNo = "";
                StringBuilder strSql = new StringBuilder();
                strSql.Append("Select isnull(max(MatNo),'QH'+ (DATENAME(YYYY,GETDATE())+'000000') )  from Material;");
                DataRow row = DataFactory.SqlDataBase().GetDataRowBySQL(strSql);
                matNo = row[0].ToString();
                matNo = "QH" + (int.Parse(matNo.Substring(2, matNo.Length-2)) + 1);
                return matNo;
            }
        }
        public int GetMatNoCount(string MatNo)
        {
            lock (balanceLock)
            {
                string matNo = "";
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select count(ID) from Material where IsDel = 0 and MatNo = '" + MatNo + "'");
                DataRow row = DataFactory.SqlDataBase().GetDataRowBySQL(strSql);
                matNo = row[0].ToString();
                return int.Parse(matNo);
            }
        }

        ///<summary>
        ///将DataTable中数据写入数据库中
        ///
        /// </summary>
        public static string ImportExcel(DataTable dt, string createUser)
        {
            if (dt == null || dt.Rows.Count == 0)
            {
                return "Excel无内容";
            }

            try
            {
                string message;
                if (dt.Columns.Contains("物料名称") && dt.Columns.Contains("单位") && dt.Columns.Contains("类别") && dt.Columns.Contains("物料编码"))
                {
                    StringBuilder sqlStr = new StringBuilder();
                    sqlStr.Append("select UnitName,UnitNum,Guid from Unit where IsDel = 0;");
                    DataTable dtUnit = DataFactory.SqlDataBase().GetDataTableBySQL(sqlStr);
                    sqlStr.Clear();
                    sqlStr.Append("select BrandCode,BrandName,Guid from Brand where IsDel = 0;");
                    DataTable dtBrand = DataFactory.SqlDataBase().GetDataTableBySQL(sqlStr);
                    sqlStr.Clear();
                    sqlStr.Append("select * from Dictionary where IsDel = 0 and TopCode = 'MatType'");
                    DataTable dtType = DataFactory.SqlDataBase().GetDataTableBySQL(sqlStr);

                    int rowCount = 0;
                    foreach (DataRow row in dt.Rows)
                    {
                        DALMatNo privt = new DALMatNo();
                        AjaxMatInfo model = new AjaxMatInfo();
                        model.Operation = "Add";
                        string matNoStr = "";
                        try
                        {
                            matNoStr = row["物料编码"].ToString();
                        }
                        catch {}
                        if (matNoStr.Length <= 0)
                        {
                            model.MatNo = privt.GetMatNo();
                        }
                        else
                        {
                            int MatNoCount = privt.GetMatNoCount(matNoStr);
                            if (MatNoCount < 0)
                            {
                                continue;
                            }
                            else
                            {
                                model.MatNo = matNoStr;
                            }
                        }

                        if (row["物料名称"].ToString() == "") 
                        {
                            
                        }
                        
                        model.MatName = row["物料名称"].ToString();                      
                        DataRow[] rows = dtUnit.Select("UnitName = '" + row["单位"].ToString() + "'");
                        if (rows.Length > 0)
                        {
                            model.Unit = rows[0]["Guid"].ToString();
                        }
                        else 
                        {
                            continue;
                        }
                        try
                        {
                            model.PackFormat = row["规格型号"].ToString();
                        }
                        catch { }

                        try
                        {
                            DataRow[] rowss = dtBrand.Select("BrandName = '" + row["品牌"].ToString() + "'");
                            if (rowss.Length > 0)
                            {
                                model.BrandId = rowss[0]["Guid"].ToString();
                            }
                        }
                        catch { }

                        try
                        {
                            DataRow[] rowsss = dtType.Select("TypeName = '" + row["类别"].ToString() + "'");
                            if (rowsss.Length > 0)
                            {
                                model.MatTypeId = rowsss[0]["Guid"].ToString();
                            }
                        }
                        catch { }

                        //switch (row["类别"].ToString())
                        //{
                        //    case "生产件":
                        //        model.MatTypeId = "01";
                        //        break;
                        //    case "标准件":
                        //        model.MatTypeId = "02";
                        //        break;
                        //    case "辅料":
                        //        model.MatTypeId = "03";
                        //        break;
                        //    case "所供件":
                        //        model.MatTypeId = "04";
                        //        break;
                        //    case "其他":
                        //        model.MatTypeId = "05";
                        //        break;
                        //    default:continue;
                        //}

                        try
                        {
                            model.Nweight = row["重量"].ToString();
                        }
                        catch { }
                        try
                        {
                            model.Long = row["长"].ToString();
                        }
                        catch { }

                        try
                        {
                            model.Width = row["宽"].ToString();
                        }
                        catch { }

                        try
                        {
                            model.Height = row["高"].ToString();
                        }
                        catch { }

                        try
                        {
                            model.Demo = row["备注"].ToString();
                        }
                        catch { }

                        if (privt.IsExist(model))
                        {
                            if (privt.Add(model, createUser))
                            {
                                rowCount++;
                            }
                        }
                    }

                    int dtCount = dt.Rows.Count;

                    if (rowCount == 0)
                    {
                        message = "导入失败，请检查Excel文档！";
                    }
                    else if (rowCount > 0 && rowCount < dt.Rows.Count)
                    {
                        dtCount = dtCount - rowCount;
                        message = "成功" + rowCount + "条，失败" + dtCount + "条,请检查数据！";
                    }
                    else
                    {
                        message = "导入成功!";
                    }
                }
                else
                {
                    message = "物料信息不全，Excel缺少必填列。";
                }

                return message;
            }
            catch (Exception)
            {
                return "导入失败，请检查Excel文档！";
            }
           

            #region liudl 注释
            ////获取要插入列的名字（为动态，由Excel列数决定）
            //string colNames = "";

            ////循环获取列名
            //for (int i = 0; i < dt.Columns.Count; i++)
            //{
            //    if (dt.Columns[i].ColumnName.Equals("存货编码") || dt.Columns[i].ColumnName.Equals("存货名称") ||
            //        dt.Columns[i].ColumnName.Equals("规格型号") || dt.Columns[i].ColumnName.Equals("主计量单位"))
            //    {
            //        if (dt.Columns[i].ColumnName.Equals("存货编码"))
            //        {
            //            dt.Columns[i].ColumnName = "MatNo";
            //        }
            //        if (dt.Columns[i].ColumnName.Equals("存货名称"))
            //        {
            //            dt.Columns[i].ColumnName = "MatName";
            //        }
            //        if (dt.Columns[i].ColumnName.Equals("规格型号"))
            //        {
            //            dt.Columns[i].ColumnName = "PackFormat";
            //        }
            //        if (dt.Columns[i].ColumnName.Equals("主计量单位"))
            //        {
            //            dt.Columns[i].ColumnName = "UnitFrist";
            //        }

            //        if (dt.Columns[i].ColumnName.Equals("MatNo") || dt.Columns[i].ColumnName.Equals("MatName") ||
            //            dt.Columns[i].ColumnName.Equals("PackFormat")||dt.Columns[i].ColumnName.Equals("UnitFrist"))
            //        {
            //            colNames += dt.Columns[i].ColumnName + ",";
            //        }
            //    }
            //    else
            //    {
            //        //删除多余的列
            //        dt.Columns.Remove(dt.Columns[i]);
            //        i--;
            //    }
            //}
            //colNames += "AuditFlag" + ",";
            //colNames += "CreateTime" + ",";
            //colNames += "CreateUser" + ",";
            ////去除最后一位‘，’防止SQL语句错误
            //colNames = colNames.TrimEnd(',');

            ////定义SQL语句
            //string cmd = "";
            ////定义获取对应列的内容变量
            //string colValues;
            ////table添加时间，创建人，审核状态列
            //dt.Columns.Add("AuditFlag",Type.GetType("System.String"));
            //dt.Columns.Add("CreateTime",Type.GetType("System.DateTime"));
            //dt.Columns.Add("CreateUser", Type.GetType("System.String"));
            ////初始SQL语句
            //string cmdmode = string.Format("insert into {0}({1}) values({{0}});", "Material", colNames);
            ////第一个循环，遍历每一行
            //for (int i = 0; i < dt.Rows.Count; i++)
            //{
            //    colValues = "";
            //    //第二个循环，遍历第每一列
            //    for (int j = 0; j < dt.Columns.Count; j++)
            //    {

            //        if (dt.Columns[j].ColumnName.Equals("MatNo") || dt.Columns[j].ColumnName.Equals("MatName") ||
            //            dt.Columns[j].ColumnName.Equals("PackFormat") ||dt.Columns[j].ColumnName.Equals("UnitFrist")||
            //           dt.Columns[j].ColumnName.Equals("AuditFlag")|| dt.Columns[j].ColumnName.Equals("CreateTime")||
            //           dt.Columns[j].ColumnName.Equals("CreateUser"))
            //        {
            //            //如果为空，就跳出循环
            //            if (dt.Rows[i][j].GetType() == typeof(DBNull))
            //            {
            //                if (dt.Columns[j].ColumnName.Equals("AuditFlag") || dt.Columns[j].ColumnName.Equals("CreateTime") ||
            //               dt.Columns[j].ColumnName.Equals("CreateUser"))
            //                {
            //                    if (dt.Columns[j].ColumnName.Equals("AuditFlag"))
            //                    {
            //                        colValues += string.Format("'{0}',", "AD01");
            //                    }
            //                    if (dt.Columns[j].ColumnName.Equals("CreateTime"))
            //                    {

            //                        colValues += string.Format("'{0}',", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"));

            //                    }
            //                    if (dt.Columns[j].ColumnName.Equals("CreateUser"))
            //                    {
            //                        colValues += string.Format("'{0}',", createUser);
            //                    }
            //                    continue;
            //                }
            //                else
            //                {
            //                    colValues += "NULL,";
            //                    continue;
            //                }
            //            }
            //            if (dt.Columns[j].ColumnName.Equals("UnitFrist"))
            //            {
            //              colValues+= string.Format("({0}),", "select UnitNum from log_unit where UnitName = '" + dt.Rows[i][j] + "'");
            //                continue;
            //            }
            //            //字段类型
            //            if (dt.Columns[j].DataType == typeof(string))
            //                colValues += string.Format("'{0}',", dt.Rows[i][j]);
            //            else if (dt.Columns[j].DataType == typeof(int) || dt.Columns[j].DataType == typeof(float) || dt.Columns[j].DataType == typeof(double))
            //            {
            //                colValues += string.Format("{0},", dt.Rows[i][j]);
            //            }
            //            else if (dt.Columns[j].DataType == typeof(DateTime))
            //            {
            //                colValues += string.Format("cast('{0}' as datetime),", dt.Rows[i][j]);
            //            }
            //            else if (dt.Columns[j].DataType == typeof(bool))
            //            {
            //                colValues += string.Format("{0},", dt.Rows[i][j].ToString());
            //            }
            //            else
            //                colValues += string.Format("'{0}',", dt.Rows[i][j]);
            //        }

            //    }
            //    StringBuilder sql = new StringBuilder();
            //    cmd = string.Format(cmdmode, colValues.TrimEnd(','));
            //    sql.Append(cmd);

            //    try
            //     {


            //        ret = DataFactory.SqlDataBase().ExecuteBySql(sql);

            //        if (ret <= 0)
            //        {
            //            return "导入失败，请检查匹配";
            //        }
            //    }
            //    catch (Exception e)
            //    {
            //        return "导入失败，请检查匹配";
            //    }
            //}
            #endregion
        }

        public bool IsExist(AjaxMatInfo model) 
        {
            bool result = false;
            try
            {
                StringBuilder sbStr = new StringBuilder();
                sbStr.Append("select count(id) from Material ");
                //sbStr.Append(" and PackFormat = " + model.PackFormat.AddQuotes());
                if (model.Operation == "Add")
                {
                    sbStr.Append("where IsDel = 0 and (MatNo = " + model.MatNo.AddQuotes());
                    sbStr.Append(" or MatName = " + model.MatName.AddQuotes() +");");
                }
                else 
                {
                    sbStr.Append("where IsDel = 0 and MatName = " + model.MatName.AddQuotes());
                    sbStr.Append(" and Guid != " + model.Guid.AddQuotes() + ";");
                }
                
                DataRow dr = DataFactory.SqlDataBase().GetDataRowBySQL(sbStr);
                if (dr[0].ToString() == "0") 
                {
                    result = true;
                }

                return result;
            }
            catch 
            {
                return result;
            }
        }


        public bool IsCheckMatNo(string MatNo)
        {
            bool bl = false;
            try
            {

                StringBuilder sql = new StringBuilder();
                sql.Append("select count(id) from Material where MatNo = '" + MatNo + "' and isdel = '0';");
                DataTable tb = DataFactory.SqlDataBase().GetDataTableBySQL(sql);
                if (tb.Rows.Count > 0)
                {
                    if (tb.Rows[0][0].ToString() == "0")
                    {
                        bl = true;
                    }
                }
                
                return bl;
            }
            catch (Exception ex)
            {
                return bl;
            }
        }
    }
}