HGSRM/ICSSoft.WMS.WebAPI/ICSSoft.DataProject/ICSWMSVersions.cs

using ICSSoft.Common;
using ICSSoft.Entity;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ICSSoft.DataProject
{
    /// <summary>
    /// pda版本
    /// </summary>
   public  class ICSWMSVersions
    {
        private static log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        private static string connString = System.Configuration.ConfigurationManager.AppSettings["ConnStr"];
        private static string ERPDB = System.Configuration.ConfigurationManager.AppSettings["ERPDB"];
        DataTable table = null;
        SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString);
        string sql = string.Empty;
        string sqlInfo = string.Empty;
        VerificationMethod verification = new VerificationMethod();
        public List<ICSVersions> Get    (ICSVersions JsonData)
        {
            conn.Open();
            SqlTransaction sqlTran = conn.BeginTransaction();
            SqlCommand cmd = new SqlCommand();
            cmd.Transaction = sqlTran;
            cmd.Connection = conn;
            try
            {
                sql = @"select top(1)* from ICSVersions where 1=1";
                if (!string.IsNullOrWhiteSpace(JsonData.ProjectName))
                {
                    sql += " and ProjectName='{0}'";
                }
                sql = string.Format(sql, JsonData.ProjectName);
                table = DBHelper.SQlReturnData(sql, cmd);
                string json = JsonConvert.SerializeObject(table);
                List<ICSVersions> model = JsonConvert.DeserializeObject<List<ICSVersions>>(json);
                cmd.Transaction.Commit();
                return model;
            }
            catch (Exception ex)
            {
                if (cmd.Transaction != null)
                    cmd.Transaction.Rollback();
                log.Error(ex.Message);
                throw new Exception(ex.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
            }

        }




        public List<ICSWorkPoint> GetWorkPoint(ICSWorkPoint JsonData)
        {
            conn.Open();
            SqlTransaction sqlTran = conn.BeginTransaction();
            SqlCommand cmd = new SqlCommand();
            cmd.Transaction = sqlTran;
            cmd.Connection = conn;
            try
            {
                sql = @"select * from Sys_WorkPoint where 1=1";
                if (!string.IsNullOrWhiteSpace(JsonData.WorkPointCode))
                {
                    sql += " and WorkPointCode='{0}'";
                }

                sql = string.Format(sql, JsonData.WorkPointCode);
                log.Debug("站点查询：" + sql);
                table = DBHelper.SQlReturnData(sql, cmd);
                string json = JsonConvert.SerializeObject(table);
                List<ICSWorkPoint> model = JsonConvert.DeserializeObject<List<ICSWorkPoint>>(json);
                cmd.Transaction.Commit();
                return model;
            }
            catch (Exception ex)
            {
                if (cmd.Transaction != null)
                    cmd.Transaction.Rollback();
                log.Error(ex.Message);
                throw new Exception(ex.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
            }

        }

        public DataTable GetWHCode(ICSWorkPoint JsonData)
        {
            conn.Open();
            SqlTransaction sqlTran = conn.BeginTransaction();
            SqlCommand cmd = new SqlCommand();
            cmd.Transaction = sqlTran;
            cmd.Connection = conn;
            try
            {
                sql = @"SELECT DISTINCT 
                        b.F_ItemName AS CJCode,
                        STUFF((
                            SELECT ',' + b2.F_ItemCode
                            FROM Sys_SRM_ItemsDetail b2
                            WHERE b2.F_ItemName = b.F_ItemName
                            ORDER BY b2.F_ItemCode
                            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS WHCode
                    FROM Sys_SRM_Items a 
                    LEFT JOIN Sys_SRM_ItemsDetail b ON a.F_Id = b.F_ItemId 
                    WHERE a.F_EnCode = 'workShopToWareHouse'
                    GROUP BY b.F_ItemName;";
                DataTable table = DBHelper.SQlReturnData(sql, cmd);
                if (table == null || table.Rows.Count <= 0)
                {
                    return null;
                }
                else
                {
                    return table;
                    //string json = JsonConvert.SerializeObject(table);
                    //List<ControlMode> model = JsonConvert.DeserializeObject<List<ControlMode>>(json);
                    //cmd.Transaction.Commit();
                    //return model[0];
                }
            }
            catch (Exception ex)
            {
                if (cmd.Transaction != null)
                    cmd.Transaction.Rollback();
                log.Error(ex.Message);
                throw new Exception(ex.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
            }

        }



        public List<ICSWorkPoint> GetBadCode(ICSWorkPoint JsonData)
        {
            conn.Open();
            SqlTransaction sqlTran = conn.BeginTransaction();
            SqlCommand cmd = new SqlCommand();
            cmd.Transaction = sqlTran;
            cmd.Connection = conn;
            try
            {
                sql = @"select badcode as WorkPointCode,BadDesc as WorkPointName from ICSBadCode where 1=1";
                if (!string.IsNullOrWhiteSpace(JsonData.WorkPointCode))
                {
                    sql += " and WorkPoint='{0}'";
                }

                sql = string.Format(sql, JsonData.WorkPointCode);
                log.Debug("不良代码查询：" + sql);
                table = DBHelper.SQlReturnData(sql, cmd);
                string json = JsonConvert.SerializeObject(table);
                List<ICSWorkPoint> model = JsonConvert.DeserializeObject<List<ICSWorkPoint>>(json);
                cmd.Transaction.Commit();
                return model;
            }
            catch (Exception ex)
            {
                if (cmd.Transaction != null)
                    cmd.Transaction.Rollback();
                log.Error(ex.Message);
                throw new Exception(ex.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
            }

        }
        public List<ICSWorkPoint> GetBadReason(ICSWorkPoint JsonData)
        {
            conn.Open();
            SqlTransaction sqlTran = conn.BeginTransaction();
            SqlCommand cmd = new SqlCommand();
            cmd.Transaction = sqlTran;
            cmd.Connection = conn;
            try
            {
                sql = @"select BadReasonCode as WorkPointCode,BadReasonDesc as WorkPointName from ICSBadReason where 1=1";
                if (!string.IsNullOrWhiteSpace(JsonData.WorkPointCode))
                {
                    sql += " and WorkPoint='{0}'";
                }

                sql = string.Format(sql, JsonData.WorkPointCode);
                log.Debug("不良原因查询：" + sql);
                table = DBHelper.SQlReturnData(sql, cmd);
                string json = JsonConvert.SerializeObject(table);
                List<ICSWorkPoint> model = JsonConvert.DeserializeObject<List<ICSWorkPoint>>(json);
                cmd.Transaction.Commit();
                return model;
            }
            catch (Exception ex)
            {
                if (cmd.Transaction != null)
                    cmd.Transaction.Rollback();
                log.Error(ex.Message);
                throw new Exception(ex.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
            }

        }

        public List<ICSBadReason> GetBadReasonGroupAndItems(ICSWorkPoint JsonData)
        {
            conn.Open();
            SqlTransaction sqlTran = conn.BeginTransaction();
            SqlCommand cmd = new SqlCommand();
            cmd.Transaction = sqlTran;
            cmd.Connection = conn;
            try
            {
                sql = @"SELECT 
                          a.BRGCode,a.BRGDesc,b.BadReasonCode,b.BadReasonDesc
                        FROM ICSBadReasonGroup a
                        INNER JOIN ICSBadReason b ON b.BRGroupID=a.ID AND b.WorkPoint=a.WorkPoint
                        WHERE 1=1";
                if (!string.IsNullOrWhiteSpace(JsonData.WorkPointCode))
                {
                    sql += " and a.WorkPoint='{0}'";
                }

                sql = string.Format(sql, JsonData.WorkPointCode);
                log.Debug("不良原因(连分组)查询SQL：" + Environment.NewLine + sql);
                table = DBHelper.SQlReturnData(sql, cmd);
                string json = JsonConvert.SerializeObject(table);
                List<ICSBadReason> model = JsonConvert.DeserializeObject<List<ICSBadReason>>(json);
                cmd.Transaction.Commit();
                return model;
            }
            catch (Exception ex)
            {
                if (cmd.Transaction != null)
                    cmd.Transaction.Rollback();
                log.Error(ex.Message);
                throw new Exception(ex.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
            }

        }

    }
}