BBMPT1/PaintingPC/Function1.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using DBUtility;
using PaintingPC.Model;

namespace PaintingPC
{
    public class Function1
    {
        /// <summary>
        /// 查询在悬挂链上的条码
        /// </summary>
        /// <returns></returns>
        public static DataTable GetOnChainBarCode()
        {
            DataTable res = new DataTable();
            try
            {
                string sql = @"select * from tb_ChainUp where flag =0 order by CreateTime asc";
                res = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql, null);
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogBase(ex.ToString(), MethodBase.GetCurrentMethod().Name);
            }
            return res;
        }

        public static DataTable GetDownInfo()
        {
            DataTable res = new DataTable();
            try
            {
                string sql = @"SELECT COUNT(*) AS sum,productName,color FROM dbo.tb_ChainDown
                                WHERE boxNum IS NULL OR boxNum =''
                                GROUP BY productName,color";
                res = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql, null);
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogBase(ex.ToString(), MethodBase.GetCurrentMethod().Name);
            }
            return res;
        }

        public static bool IsDown(string barCode) 
        {
            bool res = false;
            try
            {
                string sql = @"SELECT ID FROM dbo.tb_ChainDown
                                WHERE  barcode='"+ barCode  +"'";
                DataTable dt = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql, null);
                if (dt != null && dt.Rows.Count>0) 
                {
                    res = true;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogBase(ex.ToString(), MethodBase.GetCurrentMethod().Name);
            }
            return res;
        }

        public static DataTable PackCount(string carType, string color)
        {
            DataTable res = new DataTable();
            try
            {
                string paintNo = "",stockNo="";

                string sql1 = @"select top 1 barcode from tb_ChainDown where productName='"+ carType + @"' order by id desc";
                DataTable dt1 = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql1, null);
                if (dt1 != null && dt1.Rows.Count>0)
                {
                    stockNo = dt1.Rows[0]["barcode"].ToString().Substring(0,10);   //塑件存货代码
                }
                string sql2 = @"select Paint_No from tb_PaintColorInfo where StockNo='" + stockNo + @"' and Color='"+ color +@"'";
                DataTable dt2 = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql2, null);
                if (dt2 != null && dt2.Rows.Count > 0)
                {
                    paintNo = dt2.Rows[0]["Paint_No"].ToString();   //油漆件零件号
                }

                string sql = @"SELECT PackCount FROM dbo.tb_Product
                                WHERE PartNo='"+ paintNo +@"'";
                res = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql, null);
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogBase(ex.ToString(), MethodBase.GetCurrentMethod().Name);
            }

            return res;
        }

        public static DataTable GetBarCodeInfo(string barcode)
        {
            DataTable res = new DataTable();
            try
            {
                string sql = @"select * from tb_ChainUp where barcode='" + barcode + "'";
                res = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql, null);
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogBase(ex.ToString(), MethodBase.GetCurrentMethod().Name);
            }
            return res;
        }

        /// <summary>
        /// 查看该产品是否已打包
        /// </summary>
        /// <param name="barcode"></param>
        /// <returns></returns>
        public static DataTable isPack(string barcode)
        {
            DataTable res = new DataTable();
            try
            {
                string sql = @"SELECT * FROM dbo.tb_ChainDown
                                WHERE barcode ='"+  barcode+"' and boxNum IS NULL OR boxNum =''";
                res = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql, null);
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogBase(ex.ToString(), MethodBase.GetCurrentMethod().Name);
            }
            return res;
        }

        /// <summary>
        /// 根据二维码查找对应的一维码
        /// </summary>
        /// <param name="twoBarcode"></param>
        /// <returns></returns>
        public static DataTable oneBarcode(string twoBarcode)
        {
            DataTable res = new DataTable();
            try
            {
                string sql = @"SELECT top 1 * FROM v_Code where BarCode='" + twoBarcode + "' and IsDel =0 order by CreateTime desc";
                res = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql, null);
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogBase(ex.ToString(), MethodBase.GetCurrentMethod().Name);
            }
            return res;
        }

        public static DataTable GetNotPack(string barcode)
        {
            DataTable res = new DataTable();
            try
            {
                string sql = @"SELECT count(*) as aa FROM dbo.tb_ChainDown
                                WHERE boxNum IS NULL OR boxNum ='' and barcode SUBSTRING(barcode,1,10)='"+  barcode.Substring(0,10)+"'" ;
                res = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql, null);
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogBase(ex.ToString(), MethodBase.GetCurrentMethod().Name);
            }
            return res;
        }

        public static DataTable GetProductInfo(string productname,string color)
        {
            DataTable res = new DataTable();
            try
            {
                string sql = @"SELECT * FROM dbo.tb_Product WHERE ProductName='" + productname + "' and ColorName='"+ color +"'";
                res = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql, null);
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogBase(ex.ToString(), MethodBase.GetCurrentMethod().Name);
            }
            return res;
        }

        public static DataTable GetPackNo(string partNo)
        {
            DataTable res = new DataTable();
            try
            {
                string sql = @"SELECT top 1 * FROM tb_PaintBox WHERE boxNum like '%" + partNo + "%' and flag=0 order by createTime desc";
                res = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql, null);
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogBase(ex.ToString(), MethodBase.GetCurrentMethod().Name);
            }
            return res;
        }

        public static int  InsertPaintInfo(PaintBoxModel md)
        {
            int res = 0;

            try
            {
                string sql = @"insert into tb_PaintBox(ID,boxNum,partNo,PackCount,flag) values (@ID,@boxNum,@partNo,@PackCount,@flag)";

                SqlParameter[] param=new SqlParameter[5];

                param[0] = new SqlParameter("@ID", SqlDbType.VarChar);
                param[0].Value = md.ID;

                param[1] = new SqlParameter("@boxNum", SqlDbType.VarChar);
                param[1].Value = md.boxNum;

                param[2] = new SqlParameter("@partNo", SqlDbType.VarChar);
                param[2].Value = md.partNo;

                param[3] = new SqlParameter("@PackCount", SqlDbType.Int);
                param[3].Value = md.PackCount;

                param[4] = new SqlParameter("@flag", SqlDbType.Int);
                param[4].Value = md.flag;

                res = SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnString, CommandType.Text, sql, param);
                return res;
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogBase(ex.ToString(), MethodBase.GetCurrentMethod().Name);
                return res;
            }
        }

        public static int delChaimUp(string barCode)
        {
            int res = 0;
            try
            {
                string sql = @"update tb_ChainUp set flag=2 where barcode='" + barCode + "'";

                res = SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnString, CommandType.Text, sql, null);
                return res;
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogBase(ex.ToString(), MethodBase.GetCurrentMethod().Name);
                return res;
            }
        }

        public static int UpdateBoxNo(string barcode,string boxNo)
        {
            int res = 0;
            try
            {
                string sql = @"update tb_ChainDown set boxNum='" +boxNo+"' WHERE boxNum IS NULL OR boxNum ='' and barcode SUBSTRING(barcode,1,10)='" + barcode.Substring(0, 10) + "'";

                res = SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnString, CommandType.Text, sql, null);
                return res;
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogBase(ex.ToString(), MethodBase.GetCurrentMethod().Name);
                return res;
            }
        }

        public static DataTable BoxNo(string barcode)
        {
            DataTable res = new DataTable();
            try
            {
                string sql = @"SELECT * FROM dbo.tb_ChainDown
                                WHERE barcode ='" + barcode + "'";
                res = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql, null);
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogBase(ex.ToString(), MethodBase.GetCurrentMethod().Name);
            }
            return res;
        }

        public static DataTable boxInfo(string boxNo)
        {
            DataTable res = new DataTable();
            try
            {
                string sql = @"SELECT * FROM dbo.tb_PaintBox WHERE boxNum='" + boxNo + "' and flag=0";
                res = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql, null);
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogBase(ex.ToString(), MethodBase.GetCurrentMethod().Name);
            }
            return res;
        }

        public static DataTable SearchProdunctInfo(string partNo)
        {
            DataTable res = new DataTable();
            try
            {
                string sql = @"SELECT * FROM dbo.tb_Product WHERE PartNo='"+ partNo+"'";
                res = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql, null);
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogBase(ex.ToString(), MethodBase.GetCurrentMethod().Name);
            }
            return res;
        }

        public static DataTable IsLast()
        {
            DataTable res = new DataTable();
            string sql = "";

            try
            {
                if (DateTime.Now > Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd") + " 07:45:00") && DateTime.Now < Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd") + " 19:45:00"))
                {
                    sql = @"select top 1 * from tb_ChainUp where createTime between '" +
                          Convert.ToDateTime(DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd") + " 19:45:01") +
                          "' and '" + Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd") + " 07:45:01") + "'";
                }
                else if (DateTime.Now > Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd") + " 19:45:01") && DateTime.Now < Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59"))
                {
                    sql = @"select top 1 * from tb_ChainUp where createTime between '" +
                          Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd") + " 07:45:01") +
                          "' and '" + Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd") + " 19:45:01") + "'";
                }
                else
                {
                    sql = @"select top 1 * from tb_ChainUp where createTime between '" +
                          Convert.ToDateTime(DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd") + " 07:45:01") +
                          "' and '" + Convert.ToDateTime(DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd") + " 19:45:01") + "'";
                }

                res = SqlHelper.GetDataDateTable(SqlHelper.SqlConnString, CommandType.Text, sql, null);
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogBase(ex.ToString(), MethodBase.GetCurrentMethod().Name);
            }
            return res;
        }

        public static int UpdateIsLast(string barcode)
        {
            int res = 0;
            try
            {
                string sql = @"update tb_ChainUp set IsLast=1 where barcode='" + barcode + "'";

                res = SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnString, CommandType.Text, sql, null);
                return res;
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogBase(ex.ToString(), MethodBase.GetCurrentMethod().Name);
                return res;
            }
        }
    }
}