库存属性批量刷新

using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace DBCopy
{
    class Program
    {
        static void Main(string[] args)
        {
            string GBConnectionString = System.Configuration.ConfigurationManager.AppSettings["GBConnectionString"].ToString();
            string WarehouseIds = System.Configuration.ConfigurationManager.AppSettings["WarehouseIds"].ToString();
            char[] split = { ' ', '\t', '\r', '\n', ',', '，' };
            System.Collections.Generic.List<string> ListWhId = WarehouseIds.Split(split, StringSplitOptions.RemoveEmptyEntries).ToList();
            string DateFrom = System.Configuration.ConfigurationManager.AppSettings["StockBatchInDateFrom"];
            string DateTo = System.Configuration.ConfigurationManager.AppSettings["StockBatchInDateTo"];

            DateTime now = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
            DateTime dtfrom = now.AddDays(-1);
            DateTime dtto = now;

            if (!DateTime.TryParse(DateFrom, out dtfrom))
                dtfrom = now.AddDays(-1);
            if (!DateTime.TryParse(DateTo, out dtto))
                dtto = now;
            InsertExtraData(GBConnectionString, dtfrom, dtto, ListWhId);
        }

        private static void InsertExtraData(string connstr, DateTime dtfrom, DateTime dtto, System.Collections.Generic.List<string> listwhids)
        {
            string selectstr = string.Format(@"
SELECT a.BatchID,a.WarehouseID,a.StockInQuantity,b.GoodsName,DATEDIFF(DAY,a.CreatedOn,GETDATE()) daydiff
FROM dbo.StockInBatches a INNER JOIN dbo.Goods b ON b.SKU=a.SKU
WHERE a.WarehouseID NOT LIKE '%[_]%' AND a.BatchID NOT IN(SELECT BatchID FROM dbo.StockInbatchesExrea)
AND a.CreatedOn BETWEEN '{0}' AND '{1}' AND a.BatchID NOT IN(SELECT BatchID FROM dbo.StockInbatchesExrea)
ORDER BY a.BatchID  ", dtfrom.ToString("yyyy-MM-dd HH:mm:ss"), dtto.ToString("yyyy-MM-dd HH:mm:ss"));
            SqlConnection conn = new SqlConnection(connstr);
            if (conn.State != ConnectionState.Open)
                conn.Open();
            SqlDataAdapter ad = new SqlDataAdapter(selectstr, conn);
            DataTable dt = new DataTable();
            ad.Fill(dt);
            string insertinto_sql = @"INSERT INTO [dbo].[StockInbatchesExrea]([BatchID],[StockType],[RemainQty],[DutyQty])
VALUES(@BatchID,@StockType,@RemainQty,@RemainQty)";
            uint count1 = 0;
            uint count2 = 0;
            SqlCommand cmd = conn.CreateCommand();//创建SqlCommand对象
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = insertinto_sql;
            foreach (DataRow r in dt.Rows)
            {
                string BatchID = r["BatchID"].ToString();
                string WarehouseID = r["WarehouseID"].ToString();
                string GoodsName = r["GoodsName"].ToString();
                int StockInQuantity = int.Parse(r["StockInQuantity"].ToString());
                int daydiff = int.Parse(r["daydiff"].ToString());
                byte StockType = 0;
                if (StockInQuantity > 0)
                {
                    //默认库存属性  0正常库存
                    if (listwhids.Contains(WarehouseID))
                    { if (daydiff >= 30) { StockType = 1; } }
                    else
                    {
                        if (daydiff < 60) { }
                        else if (daydiff >= 60 && daydiff < 90) { if (!GoodsName.Contains("tronsmart")) { StockType = 1; } }
                        else { StockType = 1; }
                    }
                }

                cmd.Parameters.Add(new SqlParameter("@BatchID", BatchID));
                cmd.Parameters.Add(new SqlParameter("@StockType", StockType));
                cmd.Parameters.Add(new SqlParameter("@RemainQty", StockInQuantity));
                int j = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                if (j == 1)
                {
                    count1++;
                    Console.WriteLine(count1 + "\t插入成功{0}\t{1}\t{2}", BatchID, StockType, StockInQuantity);
                }
                else
                {
                    count2++;
                    Console.WriteLine(count2 + "插入失败");
                }
            }
        }
        private static void UpdateExtraDataStockType(string connstr, System.Collections.Generic.List<string> listwhids)
        {
            string selectstr = @"
SELECT a.Id, b.BatchID,b.WarehouseID,c.GoodsName, DATEDIFF(DAY,b.CreatedOn,GETDATE()) daydiff
FROM dbo.StockInbatchesExrea a 
INNER JOIN dbo.StockInBatches b ON b.BatchID=a.BatchID 
INNER JOIN dbo.Goods c ON c.SKU=b.SKU
WHERE b.WarehouseID  NOT LIKE '%[_]%' 
AND b.StockInQuantity>0 
AND (a.StockType=0 OR a.StockType=1)";
            SqlConnection conn = new SqlConnection(connstr);
            if (conn.State != ConnectionState.Open)
                conn.Open();
            SqlDataAdapter ad = new SqlDataAdapter(selectstr, conn);
            DataTable dt = new DataTable();
            ad.Fill(dt);
            string update_sql = @"UPDATE [dbo].[StockInbatchesExrea] SET StockType=@StockType WHERE Id=@Id";
            uint count1 = 0;
            uint count2 = 0;
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = update_sql;
            foreach (DataRow r in dt.Rows)
            {
                string Id = r["Id"].ToString();
                string WarehouseID = r["WarehouseID"].ToString();
                string GoodsName = r["GoodsName"].ToString();
                int daydiff = int.Parse(r["daydiff"].ToString());
                byte StockType = 0;
                //默认库存属性  0正常库存
                if (listwhids.Contains(WarehouseID))
                { if (daydiff >= 30) { StockType = 1; } }
                else
                {
                    if (daydiff < 60) { }
                    else if (daydiff >= 60 && daydiff < 90) { if (!GoodsName.Contains("tronsmart")) { StockType = 1; } }
                    else { StockType = 1; }
                }
                cmd.Parameters.Add(new SqlParameter("@Id", Id));
                cmd.Parameters.Add(new SqlParameter("@StockType", StockType));
                int j = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                if (j == 1)
                {
                    count1++;
                    //Console.WriteLine(count1 + "\t插入成功{0}\t{1}\t{2}", BatchID, StockType, StockInQuantity);
                }
                else
                {
                    count2++;
                    Console.WriteLine(count2 + "插入失败");
                }
            }
        }
    }
}