ERP/TradeServer/DataLogic.cs

using System;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Data.SqlClient;
using System.Collections.Generic;
using NetLibrary;
using NetLibrary.Data;
using NetLibrary.ReportPrint;
using TradeModel;
using NetLibrary.Log;

namespace TradeServer
{
    public class DataLogic
    {
        #region 读取链接
        public static List<HW_GoodsStar> GetLinkList()
        {
            try
            {
                string tsql = "select top 100  * from HW_GoodsStar where LastDate2 is null order by Id desc";
                Database db = DatabaseFactory.CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(tsql);
                DataTable tb = db.ExecuteDataTable(cmd);
                return tb.ToList<HW_GoodsStar>();
            }
            catch (Exception ex)
            {
                ErrorFollow.TraceWrite("GetLinkList", ex.Message, ex.Data.ToString());
                return null;
            }
        }
        #endregion
        #region 删除
        public static void AddGoodsStar()
        {
            string tsql = @"
INSERT INTO [dbo].[HW_GoodsStar]
           ([GoodsId]
           ,[ShopId]
           ,[Url]
           )
    
select distinct d.GoodsId,a.ShopId,'https://www.amazon.com/gp/product/'+b.productImgUrl from DT_OrderInfo a
inner join DT_OrderXXInfo e on a.OrderId=e.OrderId
inner join DT_OrderGoods b on a.OrderId=b.OrderId
inner join HW_GoodsDetail c on b.DetailId=c.DetailId
inner join HW_GoodsInfo d on c.GoodsId=d.GoodsId
left join [HW_GoodsStar] f on 'https://www.amazon.com/gp/product/'+b.productImgUrl=f.Url
where a.PlatId=2 and b.productImgUrl is not null and e.CountryCode='US' and f.Id is null


INSERT INTO [dbo].[HW_GoodsStar]
           ([GoodsId]
           ,[ShopId]
           ,[Url]
           )
    
select distinct d.GoodsId,a.ShopId,'https://www.amazon.jp/gp/product/'+b.productImgUrl from DT_OrderInfo a
inner join DT_OrderXXInfo e on a.OrderId=e.OrderId
inner join DT_OrderGoods b on a.OrderId=b.OrderId
inner join HW_GoodsDetail c on b.DetailId=c.DetailId
inner join HW_GoodsInfo d on c.GoodsId=d.GoodsId
left join [HW_GoodsStar] f on 'https://www.amazon.jp/gp/product/'+b.productImgUrl=f.Url
where a.PlatId=2 and b.productImgUrl is not null and e.CountryCode='JP' and f.Id is null



INSERT INTO [dbo].[HW_GoodsStar]
           ([GoodsId]
           ,[ShopId]
           ,[Url]
           )
    
select distinct d.GoodsId,a.ShopId,'https://www.amazon.ca/gp/product/'+b.productImgUrl from DT_OrderInfo a
inner join DT_OrderXXInfo e on a.OrderId=e.OrderId
inner join DT_OrderGoods b on a.OrderId=b.OrderId
inner join HW_GoodsDetail c on b.DetailId=c.DetailId
inner join HW_GoodsInfo d on c.GoodsId=d.GoodsId
left join [HW_GoodsStar] f on 'https://www.amazon.ca/gp/product/'+b.productImgUrl=f.Url
where a.PlatId=2 and b.productImgUrl is not null and e.CountryCode='CA'  and f.Id is null



INSERT INTO [dbo].[HW_GoodsStar]
           ([GoodsId]
           ,[ShopId]
           ,[Url]
           )
    
select distinct d.GoodsId,a.ShopId,'https://www.amazon.fr/gp/product/'+b.productImgUrl from DT_OrderInfo a
inner join DT_OrderXXInfo e on a.OrderId=e.OrderId
inner join DT_OrderGoods b on a.OrderId=b.OrderId
inner join HW_GoodsDetail c on b.DetailId=c.DetailId
inner join HW_GoodsInfo d on c.GoodsId=d.GoodsId
left join [HW_GoodsStar] f on 'https://www.amazon.fr/gp/product/'+b.productImgUrl=f.Url
where a.PlatId=2 and b.productImgUrl is not null and e.CountryCode not in ('US','JP','CA')  and f.Id is null
            update  HW_GoodsStar set LastDate2=null";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.ExecuteNonQuery(cmd);
        }
        #endregion
        #region 保存
        public static int Save_GoodsStar(HW_GoodsStar Model)
        {
            string tsql = @"
if @Id>0
begin
Update [HW_GoodsStar] set [Star1]=@Star1,[Star2]=@Star2,[Star3]=@Star3,[Star4]=@Star4,[Star5]=@Star5,[Star6]=@Star6,[LastDate]=getdate(),LastDate2=getdate() where Id=@Id
end
select @Id";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
            db.AddInParameter(cmd, "@GoodsId", DbType.Int32, Model.GoodsId);
            db.AddInParameter(cmd, "@ShopId", DbType.Int32, Model.ShopId);
            db.AddInParameter(cmd, "@Url", DbType.String, Model.Url);
            db.AddInParameter(cmd, "@Star1", DbType.Int32, Model.Star1);
            db.AddInParameter(cmd, "@Star2", DbType.Int32, Model.Star2);
            db.AddInParameter(cmd, "@Star3", DbType.Int32, Model.Star3);
            db.AddInParameter(cmd, "@Star4", DbType.Int32, Model.Star4);
            db.AddInParameter(cmd, "@Star5", DbType.Int32, Model.Star5);
            db.AddInParameter(cmd, "@Star6", DbType.Int32, Model.Star6);
            db.AddInParameter(cmd, "@LastDate", DbType.DateTime, Model.LastDate);
            int a = Convert.ToInt32(db.ExecuteScalar(cmd));
            return a;
        }
        #endregion

        #region 修改
        public static void UpdateGoodsStar2(int Id)
        {
            string tsql = @"
            update  HW_GoodsStar set LastDate=getdate(),LastDate2=getdate()  where Id=@Id";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
            db.ExecuteNonQuery(cmd);
        }
        #endregion
    }

    public class HW_GoodsStar
    {
        /// <summary>
        /// Id
        /// </summary>
        public Int32? Id { get; set; }
        /// <summary>
        /// GoodsId
        /// </summary>
        public Int32? GoodsId { get; set; }
        /// <summary>
        /// ShopId
        /// </summary>
        public Int32? ShopId { get; set; }
        /// <summary>
        /// Url
        /// </summary>
        public String Url { get; set; }
        /// <summary>
        /// Star1
        /// </summary>
        public Int32? Star1 { get; set; }
        /// <summary>
        /// Star2
        /// </summary>
        public Int32? Star2 { get; set; }
        /// <summary>
        /// Star3
        /// </summary>
        public Int32? Star3 { get; set; }
        /// <summary>
        /// Star4
        /// </summary>
        public Int32? Star4 { get; set; }
        /// <summary>
        /// Star5
        /// </summary>
        public Int32? Star5 { get; set; }
        /// <summary>
        /// Star6
        /// </summary>
        public Int32? Star6 { get; set; }
        /// <summary>
        /// LastDate
        /// </summary>
        public DateTime? LastDate { get; set; }

        public DateTime? LastDate2 { get; set; }
    }
}