ERP/TradeData/JC_ShopService.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;


namespace TradeData
{
    public class JC_ShopService 
    {
        #region 保存
        public static int Save(JC_Shop Model)
        {
            string tsql= @"
if @ShopId>0
begin
Update [JC_Shop] set [ShopName]=@ShopName,[RequestMaxNumber]=@RequestMaxNumber,[RequestNumber]=@RequestNumber,[Appkey]=@Appkey,[DeveKey]=@DeveKey,[RefreshToken]=@RefreshToken,[AccessToken]=@AccessToken,[Country]=@Country,[RefreshTokenSaveTime]=@RefreshTokenSaveTime,IsFba=@IsFba,IsAutoReadData=@IsAutoReadData,OutDay=@OutDay where ShopId=@ShopId
end
else
begin
INSERT INTO [JC_Shop]([ShopName],[PlatType],[InDate],[CompanyId],[RequestMaxNumber],[RequestNumber],[Appkey],[DeveKey],[RefreshToken],[AccessToken],[Country],[RefreshTokenSaveTime],[AccessTokenUpdateTime],IsFba,IsAutoReadData,OutDay)values(@ShopName,@PlatType,getdate(),@CompanyId,@RequestMaxNumber,@RequestNumber,@Appkey,@DeveKey,@RefreshToken,@AccessToken,@Country,@RefreshTokenSaveTime,@AccessTokenUpdateTime,@IsFba,@IsAutoReadData,@OutDay)
set @ShopId=SCOPE_IDENTITY()
end
select @ShopId";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd,"@ShopId", DbType.Int32,Model.ShopId);
            db.AddInParameter(cmd,"@ShopName", DbType.String,Model.ShopName);
            db.AddInParameter(cmd,"@PlatType", DbType.Int32,Model.PlatType);
            db.AddInParameter(cmd,"@CompanyId", DbType.Int32,Model.CompanyId);
            db.AddInParameter(cmd,"@RequestMaxNumber", DbType.Int32,Model.RequestMaxNumber);
            db.AddInParameter(cmd,"@RequestNumber", DbType.Int32,Model.RequestNumber);
            db.AddInParameter(cmd,"@Appkey", DbType.String,Model.Appkey);
            db.AddInParameter(cmd,"@DeveKey", DbType.String,Model.DeveKey);
            db.AddInParameter(cmd,"@RefreshToken", DbType.String,Model.RefreshToken);
            db.AddInParameter(cmd,"@AccessToken", DbType.String,Model.AccessToken);
            db.AddInParameter(cmd,"@Country", DbType.String,Model.Country);
            db.AddInParameter(cmd, "@IsFba", DbType.Boolean, Model.IsFba);
            db.AddInParameter(cmd, "@IsAutoReadData", DbType.Boolean, Model.IsAutoReadData);
            db.AddInParameter(cmd,"@RefreshTokenSaveTime", DbType.DateTime,Model.RefreshTokenSaveTime);
            db.AddInParameter(cmd,"@AccessTokenUpdateTime", DbType.DateTime,Model.AccessTokenUpdateTime);
            db.AddInParameter(cmd, "@OutDay", DbType.Int32, Model.OutDay);
            int a=Convert.ToInt32(db.ExecuteScalar(cmd));
            return a;
        }
        #endregion
        #region 保存
        public static int Save2(JC_Shop Model)
        {
            string tsql = @"
if @ShopId>0
begin
Update [JC_Shop] set [ShopName]=@ShopName,[RequestMaxNumber]=@RequestMaxNumber,[RequestNumber]=@RequestNumber,[Appkey]=@Appkey,[DeveKey]=@DeveKey,[Country]=@Country,IsFba=@IsFba,IsAutoReadData=@IsAutoReadData,OutDay=@OutDay where ShopId=@ShopId
end
else
begin
INSERT INTO [JC_Shop]([ShopName],[PlatType],[InDate],[CompanyId],[RequestMaxNumber],[RequestNumber],[Appkey],[DeveKey],[RefreshToken],[AccessToken],[Country],[RefreshTokenSaveTime],[AccessTokenUpdateTime],IsFba,IsAutoReadData,OutDay)values(@ShopName,@PlatType,getdate(),@CompanyId,@RequestMaxNumber,@RequestNumber,@Appkey,@DeveKey,@RefreshToken,@AccessToken,@Country,@RefreshTokenSaveTime,@AccessTokenUpdateTime,@IsFba,@IsAutoReadData,@OutDay)
set @ShopId=SCOPE_IDENTITY()
end
select @ShopId";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, "@ShopId", DbType.Int32, Model.ShopId);
            db.AddInParameter(cmd, "@ShopName", DbType.String, Model.ShopName);
            db.AddInParameter(cmd, "@PlatType", DbType.Int32, Model.PlatType);
            db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
            db.AddInParameter(cmd, "@RequestMaxNumber", DbType.Int32, Model.RequestMaxNumber);
            db.AddInParameter(cmd, "@RequestNumber", DbType.Int32, Model.RequestNumber);
            db.AddInParameter(cmd, "@Appkey", DbType.String, Model.Appkey);
            db.AddInParameter(cmd, "@DeveKey", DbType.String, Model.DeveKey);
            db.AddInParameter(cmd, "@RefreshToken", DbType.String, Model.RefreshToken);
            db.AddInParameter(cmd, "@AccessToken", DbType.String, Model.AccessToken);
            db.AddInParameter(cmd, "@Country", DbType.String, Model.Country);
            db.AddInParameter(cmd, "@IsFba", DbType.Boolean, Model.IsFba);
            db.AddInParameter(cmd, "@IsAutoReadData", DbType.Boolean, Model.IsAutoReadData);
            db.AddInParameter(cmd, "@RefreshTokenSaveTime", DbType.DateTime, Model.RefreshTokenSaveTime);
            db.AddInParameter(cmd, "@AccessTokenUpdateTime", DbType.DateTime, Model.AccessTokenUpdateTime);
            db.AddInParameter(cmd, "@OutDay", DbType.Int32, Model.OutDay);
            int a = Convert.ToInt32(db.ExecuteScalar(cmd));
            return a;
        }
        #endregion
        #region 分页查询
        public static List<JC_Shop> GetListJC_ShopForTM2(RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
        {
            if (where == null) where = new RefParameterCollection();
            QueryService ser = new QueryService();
            ser.Fields = @"ShopId=cast(a.ShopId as int),a.ShopName,PlatName=[dbo].[GetPlatName](a.ShopId),a.PlatType,a.DeptId,a.InDate,a.CompanyId,a.RequestMaxNumber,a.RequestNumber,a.Appkey,a.DeveKey,a.RefreshToken,a.AccessToken,a.Country,a.RefreshTokenSaveTime,a.AccessTokenUpdateTime,a.IsFba,a.IsAutoReadData,a.OutDay,b.DeptName,a.Master,a.Buyer,a.ShopUser,a.ReUrl";
            ser.Tables = @"JC_Shop a inner join JC_DepartMent b on a.DeptId=b.DeptId";
            ser.Filter = where.GetWhere(System.Data.CommandType.Text);
            ser.PageIndex = PageIndex;
            ser.PageSize = PageSize;
            ser.Sort = Sort;
            ser.KeyName = "ShopId";
            string tsql = ser.GetText();
            where.AddOutParameter("RowCount", System.Data.DbType.Int32);
            List<JC_Shop> ListModel = null;
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, where);
            DataTable tb = db.ExecuteDataTable(cmd);
            RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
            ListModel = tb.ToList<JC_Shop>();
            return ListModel;
        }
        #endregion
        #region 保存
        public static int SaveShop(JC_Shop Model)
        {
            string tsql = @"
if @ShopId>0
begin
Update [JC_Shop] set [ShopName]=@ShopName,[PlatType]=@PlatType,[DeptId]=@DeptId,[InDate]=@InDate,[CompanyId]=@CompanyId,[Master]=@Master,[Buyer]=@Buyer,[ShopUser]=@ShopUser where ShopId=@ShopId
end
else
begin
INSERT INTO [JC_Shop]([ShopName],[PlatType],[DeptId],[InDate],[CompanyId],[Master],[Buyer],[ShopUser])values(@ShopName,@PlatType,@DeptId,@InDate,@CompanyId,@Master,@Buyer,@ShopUser)
set @ShopId=SCOPE_IDENTITY()
end
select @ShopId";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, "@ShopId", DbType.Int32, Model.ShopId);
            db.AddInParameter(cmd, "@ShopName", DbType.String, Model.ShopName);
            db.AddInParameter(cmd, "@PlatType", DbType.Int32, Model.PlatType);
            db.AddInParameter(cmd, "@DeptId", DbType.Int32, Model.DeptId);
            db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
            db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
            db.AddInParameter(cmd, "@Master", DbType.String, Model.Master);
            db.AddInParameter(cmd, "@Buyer", DbType.String, Model.Buyer);
            db.AddInParameter(cmd, "@ShopUser", DbType.String, Model.ShopUser);
            int a = Convert.ToInt32(db.ExecuteScalar(cmd));
            return a;
        }
        #endregion
        #region 删除店铺成员
        public static void DeleteShopUser(int ShopId)
        {
            string tsql = @"
delete from JC_ShopUser where ShopId=@ShopId
";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, "@ShopId", DbType.Int32, ShopId);
            db.ExecuteNonQuery(cmd);
        }
        #endregion
        #region 保存店铺成员
        public static int SaveShopUser(JC_ShopUser Model)
        {
            string tsql = @"
if @Id>0
begin
Update [JC_ShopUser] set [SType]=@SType,[ShopId]=@ShopId,[UserId]=@UserId where Id=@Id
end
else
begin
INSERT INTO [JC_ShopUser]([SType],[ShopId],[UserId])values(@SType,@ShopId,@UserId)
set @Id=SCOPE_IDENTITY()
end
select @Id";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
            db.AddInParameter(cmd, "@SType", DbType.String, Model.SType);
            db.AddInParameter(cmd, "@ShopId", DbType.Int32, Model.ShopId);
            db.AddInParameter(cmd, "@UserId", DbType.Int32, Model.UserId);
            int a = Convert.ToInt32(db.ExecuteScalar(cmd));
            return a;
        }
        #endregion

        #region 验证店铺成员
        public static int IsShopUser(int CompanyId,string Name)
        {
            string tsql = @"
declare @UserId int
set @UserId=0
select top 1 @UserId=UserId from JC_UserInfo where Name=@Name and state=1 and CompanyId=@CompanyId
select UserId=@UserId
";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
            db.AddInParameter(cmd, "@Name", DbType.String, Name);
            int a = Convert.ToInt32(db.ExecuteScalar(cmd));
            return a;
        }
        #endregion


        #region 删除
        public static void Delete(int ShopId)
        {
            string tsql=@"
delete from JC_Shop where ShopId=@ShopId
";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd,"@ShopId", DbType.Int32,ShopId);
            db.ExecuteNonQuery(cmd);
        }
        #endregion
        #region 返回Model
        public static JC_Shop GetModel(int ShopId)
        {
            JC_Shop model = null;
            string tsql="select * from JC_Shop where ShopId=@ShopId";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd,"@ShopId", DbType.Int32,ShopId);
            DataTable tb=db.ExecuteDataTable(cmd);
            if (tb.Rows.Count>0)model=tb.Rows[0].ToModel<JC_Shop>();
            return model;
        }
        #endregion
        #region 分页查询
         public static List<JC_Shop> GetListJC_Shop(RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
        {
            if (where == null) where = new RefParameterCollection();
            QueryService ser = new QueryService();
            ser.Fields = @"ShopId=cast(a.ShopId as int),a.ShopName,PlatName=[dbo].[GetShopName](a.ShopId),a.PlatType,a.DeptId,a.InDate,a.CompanyId,a.RequestMaxNumber,a.RequestNumber,a.Appkey,a.DeveKey,a.RefreshToken,a.AccessToken,a.Country,a.RefreshTokenSaveTime,a.AccessTokenUpdateTime,a.IsFba,a.IsAutoReadData,a.OutDay";
            ser.Tables = @"JC_Shop a";
            ser.Filter = where.GetWhere(System.Data.CommandType.Text);
            ser.PageIndex = PageIndex;
            ser.PageSize = PageSize;
            ser.Sort = Sort;
            ser.KeyName = "ShopId";
            string tsql=ser.GetText();
            where.AddOutParameter("RowCount",System.Data.DbType.Int32);;
            List<JC_Shop> ListModel = null;
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, where);
            DataTable tb=db.ExecuteDataTable(cmd);
            RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
            ListModel=tb.ToList<JC_Shop>();
            return ListModel;
        }
        #endregion

         #region 分页查询
         public static List<JC_Shop> GetListJC_ShopForTM(RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
         {
             if (where == null) where = new RefParameterCollection();
             QueryService ser = new QueryService();
             ser.Fields = @"ShopId=cast(a.ShopId as int),a.ShopName,PlatName=[dbo].[GetPlatName](a.ShopId),a.PlatType,a.DeptId,a.InDate,a.CompanyId,a.RequestMaxNumber,a.RequestNumber,a.Appkey,a.DeveKey,a.RefreshToken,a.AccessToken,a.Country,a.RefreshTokenSaveTime,a.AccessTokenUpdateTime,a.IsFba,a.IsAutoReadData,a.OutDay,b.DeptName,a.Master,a.Buyer,a.ShopUser";
             ser.Tables = @"JC_Shop a left join JC_DepartMent b on a.DeptId=b.DeptId";
             ser.Filter = where.GetWhere(System.Data.CommandType.Text);
             ser.PageIndex = PageIndex;
             ser.PageSize = PageSize;
             ser.Sort = Sort;
             ser.KeyName = "ShopId";
             string tsql = ser.GetText();
             where.AddOutParameter("RowCount", System.Data.DbType.Int32); 
             List<JC_Shop> ListModel = null;
             Database db = DatabaseFactory.CreateDatabase();
             DbCommand cmd = db.GetSqlStringCommand(tsql);
             db.AddInParameter(cmd, where);
             DataTable tb = db.ExecuteDataTable(cmd);
             RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
             ListModel = tb.ToList<JC_Shop>();
             return ListModel;
         }
         #endregion

         #region 读取店铺
         public static List<JC_Shop> GetListShop(int PlatType)
         {
             string tsql = "select * from JC_Shop where (@PlatType=0 or PlatType=@PlatType) and IsAutoReadData=1";
             Database db = DatabaseFactory.CreateDatabase();
             DbCommand cmd = db.GetSqlStringCommand(tsql);
             db.AddInParameter(cmd, "@PlatType", DbType.Int32, PlatType);
             DataTable tb = db.ExecuteDataTable(cmd);
             return tb.ToList<JC_Shop>();
         }
         #endregion
         #region 读取人员
         public static List<JC_UserInfo> GetListUserInfo()
         {
             string tsql = "select * from JC_UserInfo where state=1";
             Database db = DatabaseFactory.CreateDatabase();
             DbCommand cmd = db.GetSqlStringCommand(tsql);
             DataTable tb = db.ExecuteDataTable(cmd);
             return tb.ToList<JC_UserInfo>();
         }
        #endregion
        #region 读取人员
        public static List<JC_UserInfo> GetListUserInfoFromDept(int deptid)
        {
            string tsql = "select * from JC_UserInfo where state=1 and deptid=@deptid";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(tsql);
            db.AddInParameter(cmd, "@deptid", DbType.Int32, deptid);
            DataTable tb = db.ExecuteDataTable(cmd);
            return tb.ToList<JC_UserInfo>();
        }
        #endregion
        #region 读取亚马逊_国家列表
        public static List<API_Country> GetList_API_Country(int PlatType)
         {
             string tsql = "select * from API_Country where PlatType=@PlatType";
             Database db = DatabaseFactory.CreateDatabase();
             DbCommand cmd = db.GetSqlStringCommand(tsql);
             db.AddInParameter(cmd, "@PlatType", DbType.Int32, PlatType);
             DataTable tb= db.ExecuteDataTable(cmd);
             return tb.ToList<API_Country>();
         }
        #endregion
         #region 读取所属人员
         public static List<BaseModel> GetListShopUser(int ShopId)
         {
             string tsql = @"
select ID=b.UserId,b.Name
from JC_ShopUser a
inner join JC_UserInfo b on a.UserId=b.UserId and State=1
where a.ShopId=@ShopId
";
             Database db = DatabaseFactory.CreateDatabase();
             DbCommand cmd = db.GetSqlStringCommand(tsql);
             db.AddInParameter(cmd, "@ShopId", DbType.Int32, ShopId);
             DataTable tb = db.ExecuteDataTable(cmd);
             return tb.ToList<BaseModel>();
         }
         #endregion
         #region 保存所属人员
         public static void SaveShopUser(int ShopId, List<BaseModel> ListModel)
         {
             string tsql = @"
delete from JC_ShopUser where ShopId=@ShopId
";
             Database db = DatabaseFactory.CreateDatabase();
             DbCommand cmd = db.GetSqlStringCommand(tsql);
             db.AddInParameter(cmd, "@ShopId", DbType.Int32, ShopId);
             db.ExecuteNonQuery(cmd);
             tsql = @"
insert JC_ShopUser(ShopId,UserId)values(@ShopId,@UserId)
";
             cmd = db.GetSqlStringCommand(tsql);
             foreach (var item in ListModel)
             {
                 cmd.Parameters.Clear();
                 db.AddInParameter(cmd, "@ShopId", DbType.Int32, ShopId);
                 db.AddInParameter(cmd, "@UserId", DbType.Int32, item.ID);
                 db.ExecuteNonQuery(cmd);
             }
         }
         #endregion

        #region 读取默认快递
         public static List<JC_ShopExpress> GetListShopExpress(int CompanyId, int ShopId)
         {
             string tsql = @"
select CountryName=a.Name,CountryCode=a.Code,b.ExpressID,ExpressName=c.Name
from JC_Country a
left join JC_ShopExpress b on a.Code=b.CountryCode and b.ShopId=@ShopId
left join JC_Express c on b.ExpressID=c.ExpressID
where a.IsFba=1
union
select CountryName='不限国家',CountryCode='0',b.ExpressID,ExpressName=c.Name
from (select Code='0')a 
left join JC_ShopExpress b on a.Code=b.CountryCode and b.ShopId=@ShopId
left join JC_Express c on b.ExpressID=c.ExpressID
";
             Database db = DatabaseFactory.CreateDatabase();
             DbCommand cmd = db.GetSqlStringCommand(tsql);
             db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
             db.AddInParameter(cmd, "@ShopId", DbType.Int32, ShopId);
             DataTable tb= db.ExecuteDataTable(cmd);
             return tb.ToList<JC_ShopExpress>();
         }
        #endregion
        #region 保存默认快递
         public static void SaveShopExpress(int CompanyId, int ShopId,List<JC_ShopExpress> ListModel)
         {
             string tsql = @"
delete from JC_ShopExpress where ShopId=@ShopId
";
             Database db = DatabaseFactory.CreateDatabase();
             DbCommand cmd = db.GetSqlStringCommand(tsql);
             db.AddInParameter(cmd, "@ShopId", DbType.Int32, ShopId);
             db.ExecuteNonQuery(cmd);
             tsql = @"insert JC_ShopExpress(ExpressID,ShopId,CompanyId,CountryCode)values(@ExpressID,@ShopId,@CompanyId,@CountryCode)";
             cmd = db.GetSqlStringCommand(tsql);
             foreach (var item in ListModel)
             {
                 cmd.Parameters.Clear();
                 if (item.ExpressID > 0)
                 {
                     db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
                     db.AddInParameter(cmd, "@ShopId", DbType.Int32, ShopId);
                     db.AddInParameter(cmd, "@ExpressID", DbType.Int32, item.ExpressID);
                     db.AddInParameter(cmd, "@CountryCode", DbType.String, item.CountryCode);
                     db.ExecuteNonQuery(cmd);
                 }
             }
         }
        #endregion


    }
}