wufan
/
ERP
2
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
You cannot select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
master
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'master'
${ noResults }
ERP/ERPOMS/DataNew.cs

3472 lines
200 KiB
C#
Raw Permalink Blame History Unescape Escape

This file contains ambiguous Unicode characters!

This file contains ambiguous Unicode characters that may be confused with others in your current locale. If your use case is intentional and legitimate, you can safely ignore this warning. Use the Escape button to highlight these characters.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Common;
using System.Text;
using NetLibrary;
using NetLibrary.Data;
using TradeModel;
using TradeManageNew;
using TradeManage.HuoWu;
namespace ERPOMS
{
public class DataNew
{
#region 登录
public static JC_UserInfo Login(string UserName, string PassWord, string LoginIp, int IsNB)
{
string tsql = @"
declare @UserID int,@Name nvarchar(20),@UserType nvarchar(5)
if @IsNB=1 or (select count(0) from [JC_InnerIp] where InnerIp=@LoginIp and InnerIp Is not null)>0
begin
select top 1 @UserID=UserID from JC_UserInfo a
inner join JC_Company b on a.CompanyId=b.CompanyId
where a.UserName=@UserName and a.PassWord=@PassWord and UserType<>'H' and a.State=1 and b.State=1 and isoms=1
end
else
begin
select top 1 @UserID=UserID from JC_UserInfo a
inner join JC_Company b on a.CompanyId=b.CompanyId
where a.UserName=@UserName and a.PassWord=@PassWord and UserType<>'H' and a.State=1 and b.State=1 and a.IsLogin=1 and isoms=1
end
select a.UserID,a.Name,a.UserType,a.CompanyId,CompanyName=b.SimpleName,a.DeptId,a.Duty,UserCard=isnull(a.RoomAddr,0),a.Mobile3
from JC_UserInfo a
inner join JC_Company b on a.CompanyId=b.CompanyId
where a.UserID=@UserID
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserName", DbType.String, UserName);
db.AddInParameter(cmd, "@PassWord", DbType.String, PassWord);
db.AddInParameter(cmd, "@LoginIp", DbType.String, LoginIp);
db.AddInParameter(cmd, "@IsNB", DbType.String, IsNB);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count == 0) return null;
var list = tb.Rows[0].ToModel<JC_UserInfo>();
return list;
}
#endregion
#region 读取菜单
public static List<JC_MenuList> GetListMenu_Oms(int UserID, int CompanyId)
{
string tsql = @"
declare @UserType nvarchar(10)
select top 1 @UserType=UserType from JC_UserInfo where UserID=@UserID
if @UserType='S' or @UserType='M'
begin
select * from JC_MenuList_OMS where IsUse=1
order by SortNo
end
else
begin
select *,css='ico_edit' from JC_MenuList_OMS where IsUse=1 and PopedomType>0 order by SortNo
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserID", DbType.Int32, UserID);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_MenuList>();
}
#endregion
#region 修改密码
public static int UpdatePassWord(string UserName, int UserID, string OldPassword, string NewPassWord)
{
string tsql = @"
declare @bk int
select @bk=count(0) from JC_UserInfo where UserID=@UserID and Password=@OldPassword
if @bk=1 and (select count(0) from JC_UserInfo where UserID<>@UserID and UserName=@UserName and State=1)>0
begin
select @bk=-1
end
if @bk=1
begin
update JC_UserInfo set Password=@NewPassWord,UserName=@UserName where UserID=@UserID
end
select @bk
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserID", DbType.Int32, UserID);
db.AddInParameter(cmd, "@UserName", DbType.String, UserName);
db.AddInParameter(cmd, "@OldPassword", DbType.String, OldPassword);
db.AddInParameter(cmd, "@NewPassWord", DbType.String, NewPassWord);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 返回列表
public static List<DT_Order> GetListStoreYC(DateTime? InDate)
{
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetStoredProcCommand("OrderStore_Compare");
db.AddInParameter(cmd, "@InDate", DbType.DateTime, InDate);
DataTable tb = db.ExecuteDataTable(cmd);
List<DT_Order> ListModel = tb.ToList<DT_Order>();
return ListModel;
}
#endregion
#region 分页查询
public static List<DT_OrderListNew> GetListDT_OrderInfoForUser_New(int CompanyId, DateTime? SDate, string GoodsName, string Name, string TrackCode, int IsSku, int IsDH, RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
{
string sql1 = "";
string NoGoodsSql = "";
if (IsDH == 1)
NoGoodsSql = " and isnull(b.NoGoods,0)=1";
if (IsDH == 0)
NoGoodsSql = " and isnull(b.NoGoods,0)=0";
if (TrackCode != "")
{
sql1 = @"select OrderId into #OrderTemp from DT_TrackCodeApply with (nolock)
where state=1 and TrackCode ='" + TrackCode + "' ";
}
else
if (GoodsName != "")
{
if (GoodsName.Contains(" "))
{
sql1 = @"
select distinct OrderId into #OrderTemp from DT_OrderGoods a with (nolock)
inner join HW_GoodsDetail b on a.DetailId=b.DetailId
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
where c.CompanyId=@CompanyId " + NoGoodsSql + " and (c.GoodsCode like '%" + GoodsName.Split(' ')[0].Trim() + "%' or c.GoodsOldCode like '%" + GoodsName.Split(' ')[0].Trim() + @"%') and (b.TypeCode like '%" + GoodsName.Split(' ')[1].Trim() + @"%' or b.TypeDesc like '%" + GoodsName.Split(' ')[1].Trim() + @"%')";
}
else if (GoodsName.Contains("-"))
{
sql1 = @"
select distinct OrderId into #OrderTemp from DT_OrderGoods a with (nolock)
inner join HW_GoodsDetail b on a.DetailId=b.DetailId
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
where c.CompanyId=@CompanyId " + NoGoodsSql + " and (c.GoodsCode like '%" + GoodsName.Split('-')[0].Trim() + "%' or c.GoodsOldCode like '%" + GoodsName.Split('-')[0].Trim() + @"%') and (b.TypeCode like '%" + GoodsName.Split('-')[1].Trim() + @"%' or b.TypeDesc like '%" + GoodsName.Split('-')[1].Trim() + @"%')";
}
else
{
sql1 = @"
select distinct OrderId into #OrderTemp from (select a.OrderId from DT_OrderGoods a with (nolock)
inner join HW_GoodsDetail b on a.DetailId=b.DetailId
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
where c.CompanyId=@CompanyId " + NoGoodsSql + " and (c.GoodsName like '%" + GoodsName + "%' or c.GoodsCode like '%" + GoodsName + @"%'or c.GoodsOldCode like '%" + GoodsName + @"%')
union
select OrderId from DT_OrderGoods where DetailId=0 and GoodsSKU like '%" + GoodsName + "%')a";
}
}
else if (Name != "")
{
sql1 = @"select distinct a.OrderId into #OrderTemp from DT_OrderGoods a with (nolock)
inner join DT_OrderInfo b with (nolock) on a.OrderId=b.OrderId
inner join HW_GoodsDetail c on a.detailId=c.detailId
inner join HW_GoodsUser d on c.GoodsId=d.GoodsId and b.ShopId=d.ShopId
inner join jc_userinfo e on d.UserId=e.UserId
where e.name='" + Name + "'";
if (SDate != null)
sql1 += " and b.OrderDate>='" + SDate.Value.ToString("yyyy-MM-dd") + "' ";
}
// if (IsSku == 0 && sql1 == "")
// sql1 = @"select distinct a.OrderId from DT_OrderGoods a
// inner join DT_OrderInfo b on a.OrderId=b.OrderId
// where b.CompanyId=@CompanyId and a.DetailId=0";
// else
// if (IsSku == 0 && GoodsName != "")
// sql1 = "select distinct OrderId from DT_OrderGoods where DetailId=0 and GoodsName like '%" + GoodsName + "%'";
// else
// if (IsSku == 0 && SKU != "")
// sql1 = sql1 + " a.DetailId=0";
// else if (IsSku == 1 && sql1 == "")
// sql1 = @"select distinct a.OrderId from DT_OrderGoods a
// inner join DT_OrderInfo b on a.OrderId=b.OrderId
// where b.CompanyId=@CompanyId and a.DetailId>0";
// else
// if (IsSku == 1 && GoodsName != "")
// sql1 = "select distinct OrderId from DT_OrderGoods where DetailId>0 and GoodsSKU like '%" + GoodsName + "%'";
// else
// if (IsSku == 1 && SKU != "")
// sql1 = sql1 + " a.DetailId>0";
if (sql1 == "" && (IsDH > -1))
sql1 = @"select distinct a.OrderId into #OrderTemp from DT_OrderGoods a
inner join HW_GoodsDetail b on a.DetailId=b.DetailId
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
where c.CompanyId=@CompanyId " + NoGoodsSql;
if (where == null) where = new RefParameterCollection();
QueryService ser = new QueryService();
ser.Fields = @"OrderId=cast(a.OrderId as int),PlatId=c.PlatType,a.ShopId,a.PlatOrderCode,a.JoinOrderCode,a.IsJoin,IsJoinState=case when a.IsJoin=1 then '[合并]' else '' end,a.GoodsNum,a.TotalPrice,a.MoneyCode,a.PostState,a.PostDate,a.FPDate,a.PrintState,a.MateState,a.State,a.OrderDate,a.InDate,a.CompanyId,a.IsBlank,a.CustomID,ErrorInfo='',
a.OrderState,c.ShopName,c.Country,StateName=case when a.State=1 and PostState=0 then '未发货' when a.State=1 and PostState=1 then '已发货' when a.State=1 and PostState=3 then '部分发货' when a.State=2 then '已结束' when a.State=3 then '已取消' when a.State=1 and PostState=2 then '不允许发货' end,a.CountryCode,CountryName=case when a.CountryName is null then a.RevCountry else a.CountryName end,a.TrackState,a.IsSpare,a.IsSDan,a.BuyDate,a.RevName,a.RevProvince,a.escrowFee,a.Fee6,a.Fee7,a.Fee8,a.Post,a.PostFee";
if (sql1 != "")
ser.Tables = @"VW_Orders a with (nolock) inner join #OrderTemp b on a.OrderId=b.OrderId inner join JC_Shop c with (nolock) on a.ShopId=c.ShopId";
else
ser.Tables = @"VW_Orders a with (nolock) inner join JC_Shop c with (nolock) on a.ShopId=c.ShopId";
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
ser.PageIndex = PageIndex;
ser.PageSize = PageSize;
ser.Sort = Sort;
ser.KeyName = "OrderId";
string tsql = ser.GetText();
if (sql1 != "")
tsql = sql1 + " " + tsql;
where.AddOutParameter("RowCount", System.Data.DbType.Int32);
List<DT_OrderListNew> ListModel = null;
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, where);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
DataTable tb = db.ExecuteDataTable(cmd);
RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
ListModel = tb.ToList<DT_OrderListNew>();
return ListModel;
}
#endregion
#region 分页查询
public static List<DT_OrderListNew> GetListDT_OrderInfoForTM2_New(int CompanyId, string MName, string GoodsName, string SKU, string TrackCode, int IsSku, int IsDH, RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount, string groupName = "")
{
string sql1 = "";
string NoGoodsSql = "";
if (IsDH == 1)
NoGoodsSql = " and isnull(b.NoGoods,0)=1";
if (IsDH == 0)
NoGoodsSql = " and isnull(b.NoGoods,0)=0";
//if (MName != "")
// NoGoodsSql += " and isnull(c.MName,'')='" + MName + "'";
if (TrackCode != "")
{
sql1 = @"select OrderId into #OrderTemp from DT_TrackCodeApply with (nolock)
where state=1 and TrackCode ='" + TrackCode + "' ";
}
else
if (GoodsName != "")
{
if (GoodsName.Contains(" "))
{
sql1 = @"
select distinct OrderId into #OrderTemp from DT_OrderGoods a with (nolock)
inner join HW_GoodsDetail b on a.DetailId=b.DetailId
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
where c.CompanyId=@CompanyId " + NoGoodsSql + " and (c.GoodsCode like '%" + GoodsName.Split(' ')[0].Trim() + "%' or c.GoodsOldCode like '%" + GoodsName.Split(' ')[0].Trim() + @"%') and (b.TypeCode like '%" + GoodsName.Split(' ')[1].Trim() + @"%' or b.TypeDesc like '%" + GoodsName.Split(' ')[1].Trim() + @"%')";
}
else if (GoodsName.Contains("-"))
{
sql1 = @"
select distinct OrderId into #OrderTemp from DT_OrderGoods a with (nolock)
inner join HW_GoodsDetail b on a.DetailId=b.DetailId
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
where c.CompanyId=@CompanyId " + NoGoodsSql + " and (c.GoodsCode like '%" + GoodsName.Split('-')[0].Trim() + "%' or c.GoodsOldCode like '%" + GoodsName.Split('-')[0].Trim() + @"%') and (b.TypeCode like '%" + GoodsName.Split('-')[1].Trim() + @"%' or b.TypeDesc like '%" + GoodsName.Split('-')[1].Trim() + @"%')";
}
else
{
sql1 = @"
select distinct OrderId into #OrderTemp from (select a.OrderId from DT_OrderGoods a with (nolock)
inner join HW_GoodsDetail b with (nolock) on a.DetailId=b.DetailId
inner join HW_GoodsInfo c with (nolock) on b.GoodsId=c.GoodsId
where c.CompanyId=@CompanyId " + NoGoodsSql + " and (c.GoodsName like '%" + GoodsName + "%' or c.GoodsCode like '%" + GoodsName + @"%'or c.GoodsOldCode like '%" + GoodsName + @"%')
union
select OrderId from DT_OrderGoods with (nolock) where DetailId=0 and GoodsSKU like '%" + GoodsName + "%')a";
}
}
else if (SKU != "")
sql1 = @"select distinct a.OrderId into #OrderTemp from DT_OrderGoods a with (nolock)
inner join DT_OrderInfo b with (nolock) on a.OrderId=b.OrderId
where b.CompanyId=@CompanyId and a.GoodsSku like '%" + SKU + "%'";
if (sql1 == "" && ((IsDH > -1) || MName != "" || groupName != ""))
{
sql1 = @"select distinct z.OrderId into #OrderTemp from (
select b.OrderId,b.muserid,u.Name as MName, u.GroupName from (
select a.OrderId , case when d.PlatId=2 then muserid when d.PlatId=3 then guserid_ebay when d.PlatId=6 then guserid_wlmart when d.PlatId=15 then guserid_shopify when d.PlatId=18 then guserid_wayfair when d.PlatId=21 then guserid_sheIn when d.PlatId=22 then guserid_tiktok when d.PlatId=23 then guserid_temu end muserid from DT_OrderGoods a with (nolock)
inner join HW_GoodsDetail b with (nolock) on a.DetailId=b.DetailId
inner join HW_GoodsInfo c with (nolock) on b.GoodsId=c.GoodsId
inner join DT_OrderInfo d with (nolock) on a.OrderId=d.OrderId
where c.CompanyId=1 " + NoGoodsSql + " ) b left join JC_UserInfo u on b.muserid=u.UserId ) z where 1=1 ";
//添加组长筛选
if (!string.IsNullOrEmpty(MName))
sql1 = sql1 + " and z.MName='" + MName + "'";
if (!string.IsNullOrEmpty(groupName))
sql1 = sql1 + " and z.groupname='" + groupName + "'";
}
if (where == null) where = new RefParameterCollection();
QueryService ser = new QueryService();
ser.Fields = @"OrderId=cast(a.OrderId as int),PlatId=c.PlatType,a.ShopId,a.PlatOrderCode,a.JoinOrderCode,a.IsJoin,IsJoinState=case when a.IsJoin=1 then '[合并]' else '' end,a.GoodsNum,a.TotalPrice,a.MoneyCode,a.PostState,a.PostDate,a.FPDate,a.PrintState,a.MateState,a.State,a.OrderDate,a.InDate,a.CompanyId,a.IsBlank,a.CustomID,ErrorInfo='',
a.OrderState,c.ShopName,c.Country,StateName=case when a.State=1 and PostState=0 then '未发货' when a.State=1 and PostState=1 then '已发货' when a.State=1 and PostState=3 then '部分发货' when a.State=2 then '已结束' when a.State=3 then '已取消' when a.State=1 and PostState=2 then '不允许发货' end,a.CountryCode,CountryName=case when a.CountryName is null then a.RevCountry else a.CountryName end,a.TrackState,a.IsSpare,a.IsSDan,a.BuyDate,a.RevName,a.RevProvince,a.escrowFee,a.Fee6,a.Fee7,a.Fee8,a.Post,a.PostFee,a.OrderOutDate,SaleUserId=a.WLSS,a.RevAddr,a.RevCity,a.RevPostCode,a.RevMoblie,a.RevPhone,a.RevCountry,a.StoreId,a.UpsFeeR,a.UpsFeeE,a.Fee11,a.Fee12,a.Fee13,a.RevFax as AddressType";
if (sql1 != "")
ser.Tables = @"VW_Orders a with (nolock) inner join #OrderTemp b on a.OrderId=b.OrderId inner join JC_Shop c with (nolock) on a.ShopId=c.ShopId";
else
ser.Tables = @"VW_Orders a with (nolock) inner join JC_Shop c with (nolock) on a.ShopId=c.ShopId";
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
ser.PageIndex = PageIndex;
ser.PageSize = PageSize;
ser.Sort = Sort;
ser.KeyName = "OrderId";
string tsql = ser.GetText();
if (sql1 != "")
tsql = sql1 + " " + tsql;
where.AddOutParameter("RowCount", System.Data.DbType.Int32);
List<DT_OrderListNew> ListModel = null;
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, where);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
DataTable tb = db.ExecuteDataTable(cmd);
RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
ListModel = tb.ToList<DT_OrderListNew>();
return ListModel;
}
#endregion
public static List<JC_Express> GetExpressList()
{
List<JC_Express> list = null;
string query = "\r\nselect * from JC_Express \r\n";
Database database = DatabaseFactory.CreateDatabase();
DbCommand sqlStringCommand = database.GetSqlStringCommand(query);
DataSet dataSet = database.ExecuteDataSet(sqlStringCommand);
return dataSet.Tables[0].ToList<JC_Express>();
}
#region 返回列表
public static List<DT_OrderModelNew> GetListUSMap4(string OrderIds)
{
//string tsql = "select a.Id,a.statename,a.ename,a.Num,a.code,a.color,a.Price,a.StoreId,a.IsEast from DT_USMap";
string tsql = @"select a.OrderId,b.StoreId,a.Fee6,a.Fee7,a.Fee8,a.Fee10,a.Fee11,a.Fee12,a.Fee13,a.Fee14 from DT_OrderInfo a
inner join DT_OrderXXInfo b on a.OrderId=b.OrderId
where a.OrderId in (" + OrderIds + ")";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
DataTable tb = db.ExecuteDataTable(cmd);
List<DT_OrderModelNew> ListModel = tb.ToList<DT_OrderModelNew>();
return ListModel;
}
#endregion
public static List<DT_OrderError> GetErrorList(string OrderIds)
{
List<DT_OrderError> list = null;
string query = "\r\nselect ErrorInfo=isnull(ErrorInfo,'')+isnull(PostError,''),OrderId from DT_OrderXXInfo where OrderId in (" + OrderIds + ") and (ErrorInfo is not null or PostError is not null)";
Database database = DatabaseFactory.CreateDatabase();
DbCommand sqlStringCommand = database.GetSqlStringCommand(query);
database.AddInParameter(sqlStringCommand, "@OrderIds", DbType.String, OrderIds);
DataSet dataSet = database.ExecuteDataSet(sqlStringCommand);
return dataSet.Tables[0].ToList<DT_OrderError>();
}
#region 返回列表
public static List<HW_BuyLinkNew> GetListMBLinkOrder(string OrderIds)
{
//string tsql = "select a.Id,a.GoodsId,a.LinkUrl,a.Price,a.Sort,a.Price2,a.Price3,a.Remark,a.Remark2,a.Remark3 from HW_JZLink";
string tsql = @"select SKU1=c.GoodsCode,f.*,a.OrderId,GoodsHJNum2=f.Num2,e.ShopId from DT_OrderGoods a with(nolock)
inner join HW_GoodsDetail b with(nolock) on a.DetailId=b.DetailId
inner join HW_GoodsInfo c with(nolock) on b.GoodsId=c.GoodsId
inner join DT_OrderInfo d with(nolock) on a.OrderId=d.OrderId
inner join JC_Shop e with(nolock) on d.ShopId=e.ShopId
left join (select GoodsId,LinkDesc,Num=MAX(Num),PM=MAX(PM),Num2=MAX(Num2) from HW_BuyLink group by GoodsId,LinkDesc)f on c.GoodsId=f.GoodsId and e.ShopName=f.LinkDesc
where a.OrderId in (" + OrderIds + ")";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
DataTable tb = db.ExecuteDataTable(cmd);
List<HW_BuyLinkNew> ListModel = tb.ToList<HW_BuyLinkNew>();
return ListModel;
}
#endregion
#region 查询订单明细匹配
public static List<DT_OrderGoodsListNew> GetOrderGoodsListNew2_New(string OrderIds)
{
//BGPrice 作 货物税费
//GoodsFee=a.GoodsFee+isnull(a.GoodsRate,0)*0.01*a.GoodsFee 货物税费加上去
List<DT_OrderGoodsListNew> list = null;
string tsql = @" select z.*,u.Name as mname,u.GroupName from (
select a.Id,a.OrderId,a.DetailId,a.GoodsNum,a.RMBMoney,a.MoneyCode,LockNum=case when d.matestate=2 then a.GoodsNum else 0 end,a.PostInfo,GoodsName=case when b.DetailId is not null then b.GoodsName else a.GoodsName end,
GoodsSKU=b.TypeDesc,CKGoodsNum=isnull(b.WestNum,0),SpareNum=isnull(b.EastNum,0),GoodsInNum=isnull(b.GoodsInNum,0),GoodsInNum2=isnull(b.GoodsHJNum14,0),ChaseId=isnull(b.GoodsHJNum15,0),GoodsInNum3=isnull(b.GoodsInNum,0),GoodsLeftNum=isnull(b.WestNum,0)+isnull(b.EastNum,0)+isnull(b.GoodsHJNum14,0)+isnull(b.GoodsHJNum15,0)-isnull(b.GoodsPlanNum,0),
OutNum=case when d.State=2 then a.GoodsNum else 0 end,d.PlatId,productImgUrl2=a.productImgUrl,productImgUrl=case when d.PlatId<>2 and a.productImgUrl is not null and a.productImgUrl<>'' then a.productImgUrl when b.FirstImgUrl is not null and b.FirstImgUrl<>'' then b.FirstImgUrl else b.ImgUrl end,a.GoodsDesc,b.GoodsCode,b.GoodsOldCode,b.GoodsId,b.TypeCode,b.TypeDesc,a.OldTypeCode,a.OldTypeDesc,GoodsFee=a.GoodsFee+isnull(a.GoodsRate,0)*0.01*a.GoodsFee,a.TCFee,GoodsPrice=case when f.Price is not null and f.Price>0 then f.Price+isnull(f.PostPrice,0) else b.InPrice end,b.Solid2,BGPrice=b.FeeRate,JoinOrderCode=e.RevFax,Weight=isnull(b.Weight2,0),PostionCode=cast(isnull(b.GoodsHJNum11,0) as nvarchar(10)),a.adfee,case when d.PlatId=2 then b.muserid when d.PlatId=3 then b.guserid_ebay when d.PlatId=6 then b.guserid_wlmart
when d.PlatId=15 then b.guserid_shopify when d.PlatId=18 then b.guserid_wayfair when d.PlatId=21 then b.guserid_sheIn end muserid ,b.WestNum,b.EastNum from DT_OrderGoods a
inner join DT_OrderInfo d on a.OrderId=d.OrderId
inner join DT_OrderXXInfo e on a.OrderId=e.OrderId
left join (select c.GoodsName,c.GoodsCode,c.GoodsOldCode,ImgUrl=c.FirstImgUrl,d.*,c.InPrice,Solid2=c.Solid,FeeRate=isnull(c.FeeRate,0),c.Weight2,c.muserid,c.guserid_wlmart,c.guserid_shopify,c.guserid_ebay,c.guserid_wayfair,c.guserid_sheIn from HW_GoodsInfo c
inner join HW_GoodsDetail d on c.GoodsId=d.GoodsId)b on a.DetailId=b.DetailId
left join (select OrderGoodsId,Price=MAX(price),PostPrice=MAX(PostPrice) from HW_GoodsInDetail where OrderGoodsId>0 group by OrderGoodsId) f on a.Id=f.OrderGoodsId
where a.OrderId in (" + OrderIds + ")) z left join JC_UserInfo u on z.muserid=u.UserId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@OrderIds", DbType.String, OrderIds);
DataSet ds = db.ExecuteDataSet(cmd);
list = ds.Tables[0].ToList<DT_OrderGoodsListNew>();
return list;
}
#endregion
public static List<DT_TrackCodeApply> GetTrackCodeApplyList(string OrderIds)
{
List<DT_TrackCodeApply> list = null;
string query = "\r\nselect a.OrderId,a.TrackCode,b.Weight,a.OrderCode,a.TrackType,ScanDate=b.InDate from DT_TrackCodeApply a with (nolock) \r\nleft join DT_TrackCodeScan b with (nolock) on a.TrackCode=b.TrackCode and b.OrderId in (" + OrderIds + ")\r\nwhere a.State=1 and a.OrderId in (" + OrderIds + ")";
Database database = DatabaseFactory.CreateDatabase();
DbCommand sqlStringCommand = database.GetSqlStringCommand(query);
database.AddInParameter(sqlStringCommand, "@OrderIds", DbType.String, OrderIds);
DataSet dataSet = database.ExecuteDataSet(sqlStringCommand);
return dataSet.Tables[0].ToList<DT_TrackCodeApply>();
}
#region 返回Model
public static List<DT_Fees> GetOrderFees(string OrderIds)
{
List<DT_Fees> list = null;
string tsql = @"select a.orderid,a.PlatId, a.TotalPrice,escrowFee=isnull(a.escrowFee,0),DR_BackFee=isnull(a.DR_BackFee,0),DR_PostFee=isnull(a.DR_PostFee,0),DR_SSFee=isnull(a.DR_SSFee,0),Ad_Fee=isnull(a.Ad_Fee,0),Other_Fee=isnull(a.Other_Fee,0),yj=isnull(b.yj,0),yf=isnull(b.yf,0),cb=isnull(b.cb,0),hwsf=isnull(b.hwsf,0),sjyf=isnull(b.sjyf,0),sjcb=isnull(b.sjcb,0),sjhwsf=isnull(b.sjhwsf,0),tc=isnull(b.tc,0),moneyrate=isnull(b.moneyrate,0),md=ISNULL(b.md,0),ck=ISNULL(b.ck,0),sjtc=isnull(b.sjtc,0), Isnull( b.SubsidyFee,0) SubsidyFee from DT_OrderInfo a with(nolock)
left Join DT_Fees b with(nolock) on a.OrderId=b.orderid
where a.OrderId in (" + OrderIds + ")";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
DataSet ds = db.ExecuteDataSet(cmd);
list = ds.Tables[0].ToList<DT_Fees>();
return list;
}
#endregion
#region 获取订单商品的大小类排名
public static List<GoodsRankModel> GetGoodsRankModelList(string shopids, DateTime? maxDate, DateTime? minDate)
{
string filt = "";
if (maxDate.HasValue)
filt += " and CONVERT(date,OrderDate)>='" + minDate.Value.Date + "'";
if (minDate.HasValue)
filt += " and CONVERT(date,OrderDate)<='" + maxDate.Value.Date + "'";
string tsql = " select ShopId,GoodsId,OrderDate,Int2 ,Int3 ,Int10,asin from DD_GoodsCodeData where ShopId in (" + shopids + ")" + filt;
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
DataTable tb = db.ExecuteDataTable(cmd);
List<GoodsRankModel> ListModel = tb.ToList<GoodsRankModel>();
return ListModel;
}
#endregion
#region 返回列表
public static List<HW_JZLink> GetListJZLinkOrder(string OrderIds)
{
//string tsql = "select a.Id,a.GoodsId,a.LinkUrl,a.Price,a.Sort,a.Price2,a.Price3,a.Remark,a.Remark2,a.Remark3 from HW_JZLink";
string tsql = @"select distinct d.*,a.OrderId from DT_OrderGoods a with(nolock)
inner join HW_GoodsDetail b with(nolock) on a.DetailId=b.DetailId
inner join HW_GoodsInfo c with(nolock) on b.GoodsId=c.GoodsId
inner join HW_JZLink d with(nolock) on c.GoodsId=d.GoodsId
where a.OrderId in (" + OrderIds + ")";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
DataTable tb = db.ExecuteDataTable(cmd);
List<HW_JZLink> ListModel = tb.ToList<HW_JZLink>();
return ListModel;
}
#endregion
public static List<GoodAD_LastWeek_Spend> GetAllGoodWeekAdSpend()
{
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetStoredProcCommand("GetAllGoodsLastWeekADSpend");
DataTable tb = db.ExecuteDataTable(cmd);
List<GoodAD_LastWeek_Spend> ListModel = tb.ToList<GoodAD_LastWeek_Spend>();
return ListModel;
}
#region 返回列表
public static List<GoodAD_LastWeek_Spend> GetGoodsLast2DayAdFeeListByOrders(string oids)
{
string tsql = @"select distinct b.GoodsId into #ls_1 from DT_OrderGoods a with (nolock) inner join HW_GoodsDetail b with (nolock) on a.DetailId = b.DetailId where OrderId in (" + oids + ") ";
tsql += @" select shopid, t1.goodsid,SUM(ABS(isnull(adfee, 0))) spend from AD_FeeList t1 inner join #ls_1 t2 on t1.goodsid=t2.GoodsId
where CONVERT(date, addate) = CONVERT(date, DATEADD(day, -2, GETDATE()))
group by t1.goodsid,shopid ";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
DataTable tb = db.ExecuteDataTable(cmd);
List<GoodAD_LastWeek_Spend> ListModel = tb.ToList<GoodAD_LastWeek_Spend>();
return ListModel;
}
#endregion
public static List<JC_Money> GetMoneyList2()
{
string query = "\r\n\r\nselect * from JC_Money \r\n";
Database database = DatabaseFactory.CreateDatabase();
DbCommand sqlStringCommand = database.GetSqlStringCommand(query);
DataTable tb = database.ExecuteDataTable(sqlStringCommand);
return tb.ToList<JC_Money>();
}
#region 读取人员
public static List<JC_UserInfo> GetListUserInfoFromGroup2(int UserId, int iscp)
{
string tsql = @"
if @UserId=1 or @UserId=51 or @UserId=53 or @UserId=225 or @UserId=114 or @iscp=0
begin
select * from JC_UserInfo where state=1 and deptid in (1,27,32,33)
end
else if(select count(0) from JC_UserInfo where state=1 and guserid=@UserId)>0
begin
select UserId,Name from JC_UserInfo where state=1 and userid=@UserId
union
select UserId,Name from JC_UserInfo where state=1 and guserid=@UserId and userid<>@UserId
end
else
begin
select UserId,Name from JC_UserInfo where state=1 and userid=@UserId
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
db.AddInParameter(cmd, "@iscp", DbType.Int32, iscp);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_UserInfo>();
}
#endregion
#region 返回部门列表
public static List<JC_DepartMent> GetDepartList(int CompanyId, string DeptType)
{
List<JC_DepartMent> list = null;
string tsql = "select * from JC_DepartMent where CompanyId=@CompanyId and DeptType=@DeptType";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.AddInParameter(cmd, "@DeptType", DbType.String, DeptType);
DataSet ds = db.ExecuteDataSet(cmd);
list = ds.Tables[0].ToList<JC_DepartMent>();
return list;
}
#endregion
#region 读取平台
public static List<JC_BaseCodeDetail> GetPlatBaseCode(int CompanyID, string KeyName)
{
string tsql = @"
select * from JC_BaseCodeDetail where KeyName=@KeyName and (@CompanyID=0 or CompanyID=@CompanyID) and IsUse=1 order by SortNo
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
db.AddInParameter(cmd, "@KeyName", DbType.String, KeyName);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_BaseCodeDetail>();
}
#endregion
#region 查询物流
public static List<JC_ExpressType> GetExpressTypeList()
{
List<JC_ExpressType> List = null;
string tsql = @" select * from JC_ExpressType ";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
DataSet ds = db.ExecuteDataSet(cmd);
List = ds.Tables[0].ToList<JC_ExpressType>();
return List;
}
#endregion
#region 查询
public static List<CK_StoreHouse> GetUseStoreHouse(int CompanyId)
{
List<CK_StoreHouse> list = null;
string tsql = @"
select StoreId,StoreName,IsDefault,StoreType from CK_StoreHouse where CompanyId=@CompanyId and IsUse=1 order by IsDefault desc
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
DataSet ds = db.ExecuteDataSet(cmd);
list = ds.Tables[0].ToList<CK_StoreHouse>();
return list;
}
#endregion
#region 读取店铺
public static List<JC_Shop> GetShopListUser(int UserId, int PlatType, string UserType, string AllShop)
{
string tsql = @"
select ShopId,ShopName,PlatType,DeptId,InDate,CompanyId,LevelGrade from JC_Shop where
ShopId IN (SELECT ShopId from JC_ShopUser WHERE UserId=@UserId)
and (@PlatType=0 or PlatType=@PlatType) order by PlatType
";
if (AllShop == "1" || UserType == "M" || UserType == "S" || UserId == 48 || UserId == 51) tsql = @"
select ShopId,ShopName,PlatType,DeptId,InDate,CompanyId,LevelGrade from JC_Shop where CompanyId=1 and (@PlatType=0 or PlatType=@PlatType) order by PlatType
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
db.AddInParameter(cmd, "@PlatType", DbType.Int32, PlatType);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_Shop>();
}
#endregion
#region 读取留言模板
public static List<LY_Templete> GetLY_TempleteList(int CompanyId)
{
string tsql = @"
if(select count(0) from LY_Templete where CompanyId=@CompanyId)=0
begin
INSERT INTO [LY_Templete]([TempName],[TempContent],[CompanyId])
select [TempName],[TempContent],CompanyId=@CompanyId from LY_Templete where CompanyId=0
end
select * from LY_Templete where CompanyId=@CompanyId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<LY_Templete>();
}
#endregion
#region 订单状态
public static int IsOrderState(int OrderId)
{
string tsql = @" select top 1 MateState=isnull(MateState,0) from DT_OrderInfo where OrderId=@OrderId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@OrderId", DbType.Int32, OrderId);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 取消订单
public static void DeleteCancelOrder2(int OrderId, int InUserId)
{
string tsql = @"
update DT_OrderInfo set state=3,updatedate=getdate(),MateState=0,PrintState=0,InUserId=@InUserId,IsJoin=0,IsJoinAddr=0,JoinOrderCode=null where OrderId=@OrderId
update DT_OrderInfoNew set state=3,updatedate=getdate(),MateState=0,PrintState=0,InUserId=@InUserId,IsJoin=0,IsJoinAddr=0,JoinOrderCode=null where OrderId=@OrderId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@OrderId", DbType.Int32, OrderId);
db.AddInParameter(cmd, "@InUserId", DbType.Int32, InUserId);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 取消订单数量
public static void UpdateCancelOrderGoodsForTM2(int OrderId, string InName, int CompanyId)
{
string tsql = @"
INSERT INTO [DT_OrderGoodsChange]([InName],[NowDetailId],[OldDetailId],[InDate],[OrderId],[OldNum],[NowNum],[CompanyId])
select @InName,0,DetailId,getdate(),@OrderId,GoodsNum,0,@CompanyId from DT_OrderGoods where OrderId=@OrderId and DetailId>0
update b set b.GoodsPlanNum=b.GoodsPlanNum-a.GoodsNum from [DT_OrderGoods] a
inner join HW_GoodsDetail b on a.DetailId=b.DetailId
where a.OrderId=@OrderId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@OrderId", DbType.Int32, OrderId);
db.AddInParameter(cmd, "@InName", DbType.String, InName);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 读取货币
public static List<JC_Money> GetMoneyList(int CompanyID)
{
string tsql = @"
if @CompanyID=1
begin
select * from JC_Money
where CompanyId=@CompanyID and PlatId=1
end
begin
select * from JC_Money
where CompanyId=@CompanyID
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_Money>();
}
#endregion
#region 读取物流渠道
public static List<JC_ExpressDetail> GetExpressPostList(int CompanyID, int LogisticsId)
{
string tsql = @"
select c.ExpressID,c.Name,c.PrintTemplateName from JC_ExpressPost a
--inner join JC_ExpressPostDetail b on a.ExpressPostID=b.ExpressPostID
inner join JC_Express c on a.ExpressID=c.ExpressID
where a.CompanyId=@CompanyID and a.IsUse=1 and (@LogisticsId=0 or LogisticsId=@LogisticsId) order by c.LogisticsId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
db.AddInParameter(cmd, "@LogisticsId", DbType.Int32, LogisticsId);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_ExpressDetail>();
}
#endregion
#region 获取订单详情页广告花费,当天的订单取前一天的广告花费
public static decimal GetOrderAdFeeByOrderID(int orderid)
{
var sql = @"select SUM(ISNULL(adfee,0)) TotalAdfee from DT_OrderGoods where OrderId=@OrderId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@OrderId", DbType.Int32, orderid);
var obj = db.ExecuteScalar(cmd);
return obj == null ? 0 : Convert.ToDecimal(obj);
}
#endregion
#region 返回订单Model
public static DT_OrderModelNew GetOrderModel2(int OrderId)
{
DT_OrderModelNew model = null;
string tsql = @"select a.*,
b.TotalWeight,b.TotalSoild,b.MoneyType,b.CustomID,b.SKU,b.RevName,b.RevCountry,b.RevProvince,b.RevCity,b.RevArea,b.RevAddr,b.RevPhone,b.RevMoblie,b.RevMail,b.RevFax,b.RevPostCode,b.SendInfo,b.ErrorInfo,b.BuyRemark,b.LeaveWord,b.FeeType,b.IsRegister,b.Post,b.PostFee,b.GoodsFee,b.BankCode,b.BankNo,b.CustomBankCode,b.CustomType,b.ErrorImage,b.Channel,b.GoodsAddr,b.TrackCode,b.FontTrackCode,b.StoreId,b.PostInfo,b.OrderState,b.RevProvince,b.RevCity,b.RevArea,ShopName=[dbo].[GetShopName](a.ShopId),b.CountryCode,b.CountryName,b.PostError,b.SendAddr,b.PostOneCode,b.LogisticsId,b.RevProvinceCode,a.Fee6,a.Fee7,a.Fee8,BoxFee=a.Fee10,a.Fee11,a.Fee12,a.Fee13,a.Fee14,a.FactOrderFee,a.FactOrderCommison,a.FactOrderFax,b.Zone,a.UpsFeeR,a.UpsFeeE,isnull(a.IsSyncToWMS,1) IsSyncToWMS from DT_OrderInfo a with(nolock)
inner join DT_OrderXXInfo b with(nolock) on a.OrderId=b.OrderId
where a.OrderId=@OrderId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@OrderId", DbType.Int32, OrderId);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<DT_OrderModelNew>();
return model;
}
#endregion
public static List<DT_OrderSpare> GetOrderSpareList(int OrderId)
{
string text = "";
List<DT_OrderSpare> list = null;
text = "select * from DT_OrderSpare where OrderId=@OrderId";
Database database = DatabaseFactory.CreateDatabase();
DbCommand sqlStringCommand = database.GetSqlStringCommand(text);
database.AddInParameter(sqlStringCommand, "@OrderId", DbType.Int32, OrderId);
DataSet dataSet = database.ExecuteDataSet(sqlStringCommand);
return dataSet.Tables[0].ToList<DT_OrderSpare>();
}
#region 查询订单货物明细
public static List<DT_OrderMateGoods> GetOrderGoodsListNew(int OrderId)
{
//Weight2 用于预估运费
List<DT_OrderMateGoods> list = null;
string tsql = @"
select a.Id,a.OrderId,d.PlatId,a.DetailId,a.GoodsNum,GoodsPrice=isnull(c.FeeRate,0),a.MoneyCode,a.LockNum,a.ChaseId,a.GoodsSKU,a.PostInfo,SKUState=case when a.DetailId>0 then '已经分配' else '未分配' end,GoodsName=case when c.GoodsName is null then a.GoodsName else c.GoodsName end,productImgUrl=case when productImgUrl is not null and productImgUrl<>'' then productImgUrl else '../images/no.jpg' end,TypeDesc=a.GoodsDesc,GoodsDesc=case when a.detailid<=0 then '' when a.detailid>0 and c.GoodsCode is not null and c.GoodsCode<>'' then c.GoodsCode+' '+b.TypeCode+'['+b.TypeDesc+']' else c.GoodsOldCode+' '+b.TypeCode+'['+b.TypeDesc+']' end,a.OldNum,c.GoodsCode,GoodsId=isnull(c.GoodsId,0),OldTypeDesc=case when a.OldTypeDesc is not null and a.OldTypeDesc<>'' then '改为'+a.OldTypeDesc+'发' else '' end,a.OldTypeCode,Weight=case when c.Weight2 is not null and c.Weight2>0 then c.Weight2 when c.Weight is not null and c.Weight>0 then c.Weight*0.035274 else null end,c.Width2,Length2=c.Long2,c.Height2,InPrice=isnull(c.InPrice,0),c.Solid, Weight2=case when a.Fee6>0 and (a.Fee6<a.Fee7 or a.Fee7 is null) and (a.Fee6<a.Fee8 or a.Fee8 is null) then a.Fee6
when a.Fee7>0 and (a.Fee7<a.Fee6 or a.Fee6 is null) and (a.Fee7<a.Fee8 or a.Fee8 is null) then a.Fee7 when a.Fee8>0 and (a.Fee8<a.Fee6 or a.Fee6 is null) and (a.Fee8<a.Fee7 or a.Fee8 is null) then a.Fee8 end,Price=case when d.Fee6>0 and (d.Fee6<d.Fee7 or d.Fee7 is null) and (d.Fee6<d.Fee8 or d.Fee8 is null) then d.Fee6
when d.Fee7>0 and (d.Fee7<d.Fee6 or d.Fee6 is null) and (d.Fee7<d.Fee8 or d.Fee8 is null) then d.Fee7 when d.Fee8>0 and (d.Fee8<d.Fee6 or d.Fee6 is null) and (d.Fee8<d.Fee7 or d.Fee8 is null) then d.Fee8 end,Solid=isnull(c.Solid,0)*isnull(a.GoodsNum,0) from DT_OrderGoods a
inner join DT_OrderInfo d on a.OrderId=d.OrderId
left join HW_GoodsDetail b on a.DetailId=b.DetailId
left join HW_GoodsInfo c on b.GoodsId=c.GoodsId
where a.OrderId=@OrderId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@OrderId", DbType.Int32, OrderId);
DataSet ds = db.ExecuteDataSet(cmd);
list = ds.Tables[0].ToList<DT_OrderMateGoods>();
return list;
}
#endregion
#region 返回Model
public static DT_FeesTC GetModel_FeesTC()
{
DT_FeesTC model = null;
//string tsql = "select a.id,a.tc_fee,a.indate,a.state from DT_FeesTC";
string tsql = @"
select * from DT_FeesTC where state=1";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
DataSet ds = db.ExecuteDataSet(cmd);
if (ds.Tables[0].Rows.Count > 0)
{
model = ds.Tables[0].Rows[0].ToModel<DT_FeesTC>();
}
return model;
}
#endregion
#region 查询订单货物明细
public static List<DT_OrderMateGoods> GetOrderGoodsPostionCode(int OrderId)
{
List<DT_OrderMateGoods> list = null;
string tsql = @"
select d.PlatId,b.Id,c.PostionCode,Price=isnull(a.Price,0),PostPrice=isnull(a.PostPrice,0),HWCFee=isnull(a.HWCFee,0),InPrice=isnull(f.InPrice,0),GoodsPrice=isnull(a.GoodsRate,0) from HW_GoodsInDetail a
inner join DT_OrderGoods b on a.OrderGoodsId=b.Id
inner join CK_StorePostion c on a.PostionId=c.PostionId
inner join DT_OrderInfo d on b.OrderId=d.OrderId
left join HW_GoodsDetail e on b.DetailId=e.DetailId
left join HW_GoodsInfo f on e.GoodsId=f.GoodsId
where b.OrderId=@OrderId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@OrderId", DbType.Int32, OrderId);
DataSet ds = db.ExecuteDataSet(cmd);
list = ds.Tables[0].ToList<DT_OrderMateGoods>();
return list;
}
#endregion
#region 查询订单跟踪码
public static List<DT_TrackCodeApplyNew> GetOrderTrackCodeNew(int OrderId, string JoinOrderCode)
{
List<DT_TrackCodeApplyNew> list = null;
string tsql = @"
if @JoinOrderCode<>''
begin
select a.Id,a.PostId,a.TrackType,a.TrackCode,a.TrackCode2,a.GetDate,a.InName,a.OrderId,a.State,a.UpdateTime,a.OrderCode,a.OldPostFee,a.ScanState,TrackTypeName=case when a.TrackType=3 then '空包裹' else '' end,convert(nvarchar(100), b.Weight) as Weight,b.PostFee,ScanDate=b.InDate,a.SKU,Weight2=a.Weight,a.Long,a.Width,a.Height,a.OrderCode2,ScanName=c.Name from DT_TrackCodeApply a with(nolock)
left join DT_TrackCodeScan b with(nolock) on a.OrderId=b.OrderId and a.TrackCode=b.TrackCode
left Join JC_UserInfo c with(nolock) on b.SureUserId=c.UserId
where a.OrderCode=@JoinOrderCode and a.state=1
end
else
begin
select a.Id,a.PostId,a.TrackType,a.TrackCode,a.TrackCode2,a.GetDate,a.InName,a.OrderId,a.State,a.UpdateTime,a.OrderCode,a.OldPostFee,a.ScanState,TrackTypeName=case when a.TrackType=3 then '空包裹' else '' end,convert(nvarchar(100), b.Weight) as Weight,b.PostFee,ScanDate=b.InDate,a.SKU,Weight2=a.Weight,a.Long,a.Width,a.Height,a.OrderCode2,ScanName=c.Name from DT_TrackCodeApply a with(nolock)
left join DT_TrackCodeScan b with(nolock) on a.OrderId=b.OrderId and a.TrackCode=b.TrackCode
left Join JC_UserInfo c with(nolock) on b.SureUserId=c.UserId
where a.OrderId=@OrderId and a.state=1
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@OrderId", DbType.Int32, OrderId);
db.AddInParameter(cmd, "@JoinOrderCode", DbType.String, JoinOrderCode);
DataSet ds = db.ExecuteDataSet(cmd);
list = ds.Tables[0].ToList<DT_TrackCodeApplyNew>();
return list;
}
#endregion
#region 返回列表
public static List<DT_SpareFee> GetListSpareFee(int OrderId)
{
//string tsql = "select a.Id,a.OrderId,a.Sku,a.Weight,a.Long,a.Width,a.Height,a.Fee1,a.Fee2,a.Fee3,a.InDate,a.InUserId from DT_SpareFee";
string tsql = "select * from DT_SpareFee where OrderId=@OrderId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@OrderId", DbType.Int32, OrderId);
DataTable tb = db.ExecuteDataTable(cmd);
List<DT_SpareFee> ListModel = tb.ToList<DT_SpareFee>();
return ListModel;
}
#endregion
#region 返回Model
public static DT_PostVote GetModel_PostVote(int OrderId)
{
DT_PostVote model = null;
//string tsql = "select a.Id,a.OrderId,a.trackcode,a.ActualWeightAmount,a.ActualWeightUnits,a.RatedWeightAmount,a.DimLength,a.DimWidth,a.DimHeight,a.DimDivisor,a.DimUnit,a.ZoneCode,a.bigdifference,a.result,a.sku,a.indate,a.inname,a.fee1,a.fee2,a.fee3,a.fee4,a.fee5,a.int1,a.int2,a.int3,a.str1,a.str2,a.str3 from DT_PostVote";
string tsql = @"
declare @Id int
select top 1 @Id=Id from DT_PostVote where OrderId=@OrderId
select * from DT_PostVote where Id=@Id
select * from DT_PostVoteDetail where mid=@Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@OrderId", DbType.Int32, OrderId);
DataSet ds = db.ExecuteDataSet(cmd);
if (ds.Tables[0].Rows.Count > 0)
{
model = ds.Tables[0].Rows[0].ToModel<DT_PostVote>();
model.list = ds.Tables[1].ToList<DT_PostVoteDetail>();
}
return model;
}
#endregion
#region 返回赔款Model
public static DT_OrderBackMoneyApply GetOrderBackMoneyModel2(int OrderId)
{
DT_OrderBackMoneyApply model = null;
string tsql = "select a.Id,a.OrderId,a.OrderCode,a.GoodsCode,a.BackReason1,a.BackReason2,a.BackReason3,a.Remark,OrderPrice=c.TotalPrice,a.BackPrice,a.PlanPrice,a.FactPrice,a.InName,a.InUserId,a.DeptName,a.DeptRemark,a.CompanyRemark,a.CompanyRemark2,a.CompanyName1,a.Indate,a.CompanyDate1,a.CompanyDate2,a.CompanyAgree1,a.CompanyAgree2,a.CompanyUserId,a.ShopId,a.MoneyCode1,a.MoneyCode2,a.MoneyCode3,a.MoneyCode4,a.ImageIds,a.BackDate,a.PostCode,a.PostFee,a.PostFee2,a.BackState,a.BackState2,a.BackType,a.OrderState,a.GoodState,a.BackAdvise,a.BackAdvise2,c.OrderDate,BackStateName=case when a.BackState=2 then '外箱破损重新包装入库' when a.BackState=1 then '正常入库' when a.BackState=3 then '破损报废' when a.BackState=4 then '丢失' else '未到货' end,b.ShopName from DT_OrderBackMoneyApply a inner join jc_shop b on a.ShopId=b.ShopId inner join DT_OrderInfo c on a.OrderId=c.OrderId where a.OrderId=@OrderId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@OrderId", DbType.Int32, OrderId);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<DT_OrderBackMoneyApply>();
return model;
}
#endregion
#region 分页查询
public static List<JC_Resource> GetListJC_Resource(string Ids)
{
string tsql = "select a.id,a.Type,a.FileName,a.FileUrl,a.FileSize,a.ExtType,a.FileKeyWord,a.CreateTime,a.UpdateTime,a.State,a.ItemName,a.InUserId,a.CompanyId from JC_Resource a where Id in (" + Ids + ")";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_Resource>();
}
#endregion
#region 修改订单基本信息
public static void UpdateOrderBaseInfo23(DT_OrderModelSave Model)
{
string tsql = @"
if @JoinOrderCode<>''
begin
Update DT_OrderInfo set PostState=@PostState,IsFba=@IsFba,MoneyState=@MoneyState,IsSyncToWMS=@IsSyncToWMS where JoinOrderCode=@JoinOrderCode
Update a set a.[ErrorInfo]=@ErrorInfo from DT_OrderXXInfo a
inner join DT_OrderInfo b on a.OrderId=b.OrderId
where b.JoinOrderCode=@JoinOrderCode
Update DT_OrderInfoNew set PostState=@PostState,IsFba=@IsFba,MoneyState=@MoneyState where JoinOrderCode=@JoinOrderCode
Update a set a.[ErrorInfo]=@ErrorInfo from DT_OrderXXInfoNew a
inner join DT_OrderInfoNew b on a.OrderId=b.OrderId
where b.JoinOrderCode=@JoinOrderCode
end
else
begin
Update [DT_OrderInfo] set PostState=@PostState,IsFba=@IsFba,MoneyState=@MoneyState,IsSyncToWMS=@IsSyncToWMS where OrderId=@OrderId
Update [DT_OrderXXInfo] set [ErrorInfo]=@ErrorInfo,StoreId=@StoreId where OrderId=@OrderId
Update [DT_OrderInfoNew] set PostState=@PostState,IsFba=@IsFba,MoneyState=@MoneyState where OrderId=@OrderId
Update [DT_OrderXXInfoNew] set [ErrorInfo]=@ErrorInfo,StoreId=@StoreId where OrderId=@OrderId
end
update DT_OrderInfo set escrowFee=@escrowFee where OrderId=@OrderId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@OrderId", DbType.Int32, Model.OrderId);
db.AddInParameter(cmd, "@ErrorInfo", DbType.String, Model.ErrorInfo);
db.AddInParameter(cmd, "@PostState", DbType.Int32, Model.PostState);
db.AddInParameter(cmd, "@IsFba", DbType.Int32, Model.IsFba);
db.AddInParameter(cmd, "@escrowFee", DbType.Decimal, Model.escrowFee);
db.AddInParameter(cmd, "@MoneyState", DbType.Int32, Model.MoneyState);
db.AddInParameter(cmd, "@JoinOrderCode", DbType.String, Model.JoinOrderCode);
db.AddInParameter(cmd, "@StoreId", DbType.Int32, Model.StoreId);
db.AddInParameter(cmd, "@IsSyncToWMS", DbType.Int32, Model.IsSyncToWMS);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 验证国家
public static DT_Country GetCountryCode(string CountryName)
{
DT_Country model = null;
string tsql = @"
select top 1 Code,CountryName=Name,EnglishName from JC_Country where Name=@CountryName or EnglishName=@CountryName or Code=@CountryName
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CountryName", DbType.String, CountryName);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<DT_Country>();
return model;
}
#endregion
#region 修改订单地址
public static void UpdateOrderAddr2(DT_OrderModel Model)
{
string tsql = @"
Update [DT_OrderXXInfo] set [RevName]=@RevName,CustomID=@CustomID,[RevCountry]=@RevCountry,RevMail=@RevMail,[RevAddr]=@RevAddr,[RevPhone]=@RevPhone,[RevMoblie]=@RevMoblie,[RevPostCode]=@RevPostCode,RevProvince=@RevProvince,RevCity=@RevCity,RevArea=@RevArea,CountryCode=@CountryCode,CountryName=@CountryName where OrderId=@OrderId
Update [DT_OrderXXInfoNew] set [RevName]=@RevName,CustomID=@CustomID,[RevCountry]=@RevCountry,RevMail=@RevMail,[RevAddr]=@RevAddr,[RevPhone]=@RevPhone,[RevMoblie]=@RevMoblie,[RevPostCode]=@RevPostCode,RevProvince=@RevProvince,RevCity=@RevCity,RevArea=@RevArea,CountryCode=@CountryCode,CountryName=@CountryName where OrderId=@OrderId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@OrderId", DbType.Int32, Model.OrderId);
db.AddInParameter(cmd, "@RevName", DbType.String, Model.RevName);
db.AddInParameter(cmd, "@RevCountry", DbType.String, Model.RevCountry);
db.AddInParameter(cmd, "@RevAddr", DbType.String, Model.RevAddr);
db.AddInParameter(cmd, "@RevPhone", DbType.String, Model.RevPhone);
db.AddInParameter(cmd, "@CustomID", DbType.String, Model.CustomID);
db.AddInParameter(cmd, "@RevMoblie", DbType.String, Model.RevMoblie);
db.AddInParameter(cmd, "@RevMail", DbType.String, Model.RevMail);
db.AddInParameter(cmd, "@RevPostCode", DbType.String, Model.RevPostCode);
db.AddInParameter(cmd, "@RevProvince", DbType.String, Model.RevProvince);
db.AddInParameter(cmd, "@RevCity", DbType.String, Model.RevCity);
db.AddInParameter(cmd, "@RevArea", DbType.String, Model.RevArea);
db.AddInParameter(cmd, "@CountryCode", DbType.String, Model.CountryCode);
db.AddInParameter(cmd, "@CountryName", DbType.String, Model.CountryName);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 保存
public static int Save_BlackName(DT_BlackName Model)
{
string tsql = @"
if @id>0
begin
Update [DT_BlackName] set [RevName]=@RevName,[RevCountry]=@RevCountry,[RevProvince]=@RevProvince,[RevAddr]=@RevAddr,[RevPhone]=@RevPhone,[RevMoblie]=@RevMoblie,[RevCity]=@RevCity,[orderid]=@orderid,[userid]=@userid where id=@id
end
else
begin
INSERT INTO [DT_BlackName]([RevName],[RevCountry],[RevProvince],[RevAddr],[RevPhone],[RevMoblie],[RevCity],[orderid],[userid])values(@RevName,@RevCountry,@RevProvince,@RevAddr,@RevPhone,@RevMoblie,@RevCity,@orderid,@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, "@RevName", DbType.String, Model.RevName);
db.AddInParameter(cmd, "@RevCountry", DbType.String, Model.RevCountry);
db.AddInParameter(cmd, "@RevProvince", DbType.String, Model.RevProvince);
db.AddInParameter(cmd, "@RevAddr", DbType.String, Model.RevAddr);
db.AddInParameter(cmd, "@RevPhone", DbType.String, Model.RevPhone);
db.AddInParameter(cmd, "@RevMoblie", DbType.String, Model.RevMoblie);
db.AddInParameter(cmd, "@RevCity", DbType.String, Model.RevCity);
db.AddInParameter(cmd, "@orderid", DbType.String, Model.orderid);
db.AddInParameter(cmd, "@userid", DbType.Int32, Model.userid);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 根据sku查询货物
public static List<HW_GoodsSKUModel> GetGoodsModelFromGoodsCode(int TJ, int CompanyId, string GoodsCode, string TypeCode)
{
List<HW_GoodsSKUModel> list = null;
string tsql = @"
if @TJ=1 and @TypeCode<>''
begin
select top 50 a.DetailId,a.TypeCode,a.TypeDesc,a.GoodsId,b.GoodsCode,b.GoodsOldCode,b.GoodsName,a.GoodsNum from HW_GoodsDetail a
inner join HW_GoodsInfo b on a.GoodsId=b.GoodsId
where b.CompanyId=@CompanyId and (b.GoodsCode=@GoodsCode or b.GoodsOldCode=@GoodsCode) and a.TypeCode=@TypeCode order by b.GoodsId,a.TypeCode
end
else if @TJ=1 and @TypeCode=''
begin
select top 300 a.DetailId,a.TypeCode,a.TypeDesc,a.GoodsId,b.GoodsCode,b.GoodsOldCode,b.GoodsName,a.GoodsNum from HW_GoodsDetail a
inner join HW_GoodsInfo b on a.GoodsId=b.GoodsId
where b.CompanyId=@CompanyId and (b.GoodsCode=@GoodsCode or b.GoodsOldCode=@GoodsCode) order by b.GoodsId,a.TypeCode
end
else if @TJ=2
begin
select top 300 a.DetailId,a.TypeCode,a.TypeDesc,a.GoodsId,b.GoodsCode,b.GoodsOldCode,b.GoodsName,a.GoodsNum from HW_GoodsDetail a
inner join HW_GoodsInfo b on a.GoodsId=b.GoodsId
where b.CompanyId=@CompanyId and b.GoodsName like '%'+@GoodsCode+'%' order by b.GoodsId,a.TypeCode
end
else if @TJ=3
begin
select top 300 a.DetailId,a.TypeCode,a.TypeDesc,a.GoodsId,b.GoodsCode,b.GoodsOldCode,b.GoodsName,a.GoodsNum from HW_GoodsDetail a
inner join HW_GoodsInfo b on a.GoodsId=b.GoodsId
where b.CompanyId=@CompanyId and (a.SKU1 like '%'+@GoodsCode+'%' or a.SKU2 like '%'+@GoodsCode+'%' or a.SKU3 like '%'+@GoodsCode+'%' or a.SKU4 like '%'+@GoodsCode+'%' or a.SKU5 like '%'+@GoodsCode+'%' or a.SKU6 like '%'+@GoodsCode+'%' or a.SKU7 like '%'+@GoodsCode+'%' or a.SKU8 like '%'+@GoodsCode+'%' or a.SKU9 like '%'+@GoodsCode+'%' or a.SKU10 like '%'+@GoodsCode+'%') order by b.GoodsId,a.TypeCode
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.AddInParameter(cmd, "@TJ", DbType.Int32, TJ);
db.AddInParameter(cmd, "@GoodsCode", DbType.String, GoodsCode);
db.AddInParameter(cmd, "@TypeCode", DbType.String, TypeCode);
DataSet ds = db.ExecuteDataSet(cmd);
list = ds.Tables[0].ToList<HW_GoodsSKUModel>();
return list;
}
#endregion
#region 返回货物明细
public static string GetGoodsDetailTypeDesc(int GoodsId, string TypeCode)
{
string tsql = "select top 1 TypeDesc from HW_GoodsDetail where GoodsId=@GoodsId and TypeCode=@TypeCode";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@GoodsId", DbType.Int32, GoodsId);
db.AddInParameter(cmd, "@TypeCode", DbType.String, TypeCode);
string a = Convert.ToString(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 根据明细ID查询货物
public static HW_GoodsSPDetail GetGoodsDetailForTM(int DetailId)
{
HW_GoodsSPDetail model = null;
string tsql = @"
SELECT TypeCode,TypeDesc,b.GoodsCode,b.GoodsOldCode FROM [HW_GoodsDetail] a
inner join HW_GoodsInfo b on a.GoodsId=b.GoodsId
WHERE DetailId=@DetailId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@DetailId", DbType.Int32, DetailId);
//DataSet ds = db.ExecuteDataSet(cmd);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<HW_GoodsSPDetail>();
return model;
}
#endregion
#region 查询改分配货物
public static List<DT_OrderGoods> GetOrderChangeGoods(int OrderId, int Id)
{
List<DT_OrderGoods> list = null;
string tsql = @"
select a.DetailId,b.TypeCode,b.TypeDesc,GoodsCode=isnull(c.GoodsCode,c.GoodsOldCode) from DT_OrderGoods a
inner join HW_GoodsDetail b on a.DetailId=b.DetailId
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
where a.DetailId>0 and a.OrderId=@OrderId and (@Id=0 or a.Id=@Id)";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
db.AddInParameter(cmd, "@OrderId", DbType.Int32, OrderId);
DataSet ds = db.ExecuteDataSet(cmd);
list = ds.Tables[0].ToList<DT_OrderGoods>();
return list;
}
#endregion
#region 查询改分配货物提醒
public static List<JC_UserInfo> GetOrderChangeNotice(int DetailId)
{
List<JC_UserInfo> list = null;
string tsql = @"
select UserId=a.InUserId from CG_Purchase a
inner join CG_PurchaseGoods b on a.ChaseId=b.ChaseId
inner join HW_GoodsDetail c on b.GoodsDetailId=c.DetailId
where c.DetailId=@DetailId and a.GoodsState<2 and a.State<3 and (c.GoodsNum+c.GoodsInNum-c.GoodsPlanNum)>0 and c.GoodsInNum>0";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@DetailId", DbType.Int32, DetailId);
DataSet ds = db.ExecuteDataSet(cmd);
list = ds.Tables[0].ToList<JC_UserInfo>();
return list;
}
#endregion
#region 保存提示消息
public static int SaveOpenMess(JC_OpenMess Model)
{
string tsql = @"
if @Id>0
begin
Update [JC_OpenMess] set [Content]=@Content,[IsRead]=@IsRead,[OpenDate]=@OpenDate,[Target]=@Target,[Type]=@Type,[UserType]=@UserType,[UserId]=@UserId,[Url]=@Url,[MenuName]=@MenuName where Id=@Id
end
else
begin
INSERT INTO [JC_OpenMess]([Content],[IsRead],[OpenDate],[Target],[Type],[UserType],[UserId],[Url],[MenuName],InUserId)values(@Content,@IsRead,@OpenDate,@Target,@Type,@UserType,@UserId,@Url,@MenuName,@InUserId)
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, "@Content", DbType.String, Model.Content);
db.AddInParameter(cmd, "@IsRead", DbType.Int32, Model.IsRead);
db.AddInParameter(cmd, "@OpenDate", DbType.DateTime, Model.OpenDate);
db.AddInParameter(cmd, "@Target", DbType.Int32, Model.Target);
db.AddInParameter(cmd, "@Type", DbType.Int32, Model.Type);
db.AddInParameter(cmd, "@UserType", DbType.String, Model.UserType);
db.AddInParameter(cmd, "@UserId", DbType.Int32, Model.UserId);
db.AddInParameter(cmd, "@Url", DbType.String, Model.Url);
db.AddInParameter(cmd, "@MenuName", DbType.String, Model.MenuName);
db.AddInParameter(cmd, "@InUserId", DbType.String, Model.InUserId);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存
public static int Save_SpareFee(DT_SpareFee Model)
{
string tsql = @"
if @Id>0
begin
Update [DT_SpareFee] set [OrderId]=@OrderId,[Sku]=@Sku,[Weight]=@Weight,[Long]=@Long,[Width]=@Width,[Height]=@Height,[Fee1]=@Fee1,[Fee2]=@Fee2,[Fee3]=@Fee3,[InDate]=@InDate,[InUserId]=@InUserId where Id=@Id
end
else
begin
INSERT INTO [DT_SpareFee]([OrderId],[Sku],[Weight],[Long],[Width],[Height],[Fee1],[Fee2],[Fee3],[InDate],[InUserId])values(@OrderId,@Sku,@Weight,@Long,@Width,@Height,@Fee1,@Fee2,@Fee3,@InDate,@InUserId)
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, "@OrderId", DbType.Int32, Model.OrderId);
db.AddInParameter(cmd, "@Sku", DbType.String, Model.Sku);
db.AddInParameter(cmd, "@Weight", DbType.String, Model.Weight);
db.AddInParameter(cmd, "@Long", DbType.String, Model.Long);
db.AddInParameter(cmd, "@Width", DbType.String, Model.Width);
db.AddInParameter(cmd, "@Height", DbType.String, Model.Height);
db.AddInParameter(cmd, "@Fee1", DbType.Decimal, Model.Fee1);
db.AddInParameter(cmd, "@Fee2", DbType.Decimal, Model.Fee2);
db.AddInParameter(cmd, "@Fee3", DbType.Decimal, Model.Fee3);
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
db.AddInParameter(cmd, "@InUserId", DbType.Int32, Model.InUserId);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region
/// <summary>
/// 更新订单的UPS运费
/// </summary>
/// <param name="orderid"></param>
/// <param name="postfee">运费</param>
/// <param name="postid">仓库：241是RANCHO，242是E仓</param>
public static void UpdateOrderUPSPostFee(int orderid, decimal postfee, int postid, string upslog)
{
var sql = "";
if (postid == 241)
{
sql = @" update DT_OrderInfo set UpsFeeR=@postfee,UpsFeelog=@upslog where OrderId=@orderid";
}
else if (postid == 242)
{
sql = @" update DT_OrderInfo set UpsFeeE=@postfee,UpsFeelog=@upslog where OrderId=@orderid";
}
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@postfee", DbType.Decimal, postfee);
db.AddInParameter(cmd, "@orderid", DbType.Int32, orderid);
db.AddInParameter(cmd, "@upslog", DbType.String, upslog);
db.ExecuteNonQuery(cmd);
}
#endregion
#region
/// <summary>
/// 更新订单
/// </summary>
public static void UpdateOrderUPSLog(int orderid, string upslog)
{
var sql = "update DT_OrderInfo set UpsFeelog=@upslog where OrderId=@orderid";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@orderid", DbType.Int32, orderid);
db.AddInParameter(cmd, "@upslog", DbType.String, upslog);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 修改订单采购时间
public static void UpdateOrderBuyDateForTM2(int OrderId, int GoodsNum)
{
string tsql = @"
if(select COUNT(0) from DT_OrderGoods a
inner join HW_GoodsDetail b on a.DetailId=b.DetailId
where a.OrderId=@OrderId and isnull(b.GoodsNum,0)+isnull(b.GoodsInNum,0)-isnull(b.GoodsPlanNum,0)>0)>0
begin
update DT_OrderInfo set BuyDate=GETDATE() where OrderId=@OrderId and BuyDate is null
update DT_OrderInfonew set BuyDate=GETDATE() where OrderId=@OrderId and BuyDate is null
update DT_OrderInfo set GoodsNum=@GoodsNum where OrderId=@OrderId
update DT_OrderInfonew set GoodsNum=@GoodsNum where OrderId=@OrderId
end
else
begin
update DT_OrderInfo set BuyDate=null,GoodsNum=@GoodsNum where OrderId=@OrderId
update DT_OrderInfonew set BuyDate=null,GoodsNum=@GoodsNum where OrderId=@OrderId
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@OrderId", DbType.Int32, OrderId);
db.AddInParameter(cmd, "@GoodsNum", DbType.Int32, GoodsNum);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 获取物流对象
public static JC_ExpressPost GetExpressModel(int CompanyId, int ExpressID)
{
JC_ExpressPost model = null;
string tsql = @"
if(select count(0) from [JC_ExpressPost] a
inner join JC_Express c on a.ExpressID=c.ExpressID
where a.ExpressID=@ExpressID and a.CompanyId=@CompanyId)>0
begin
select a.ExpressPostID,a.ExpressID,a.CompanyId,a.Name,c.PostType,a.IsUse,a.UserCode,a.CheckCode,a.operationtype,a.customercode,a.vipcode,a.clcttype,a.DefaultAddr,a.client_id,a.client_secret,a.refresh_token,a.GetTime,a.Code,a.redirect_uri,c.EName,c.LogisticsId from [JC_ExpressPost] a
inner join JC_Express c on a.ExpressID=c.ExpressID
where a.ExpressID=@ExpressID and a.CompanyId=@CompanyId
end
else
begin
select a.ExpressPostID,a.ExpressID,a.CompanyId,a.Name,c.PostType,a.IsUse,a.UserCode,a.CheckCode,a.operationtype,a.customercode,a.vipcode,a.clcttype,a.DefaultAddr,a.client_id,a.client_secret,a.refresh_token,a.GetTime,a.Code,a.redirect_uri,c.EName,c.LogisticsId from [JC_ExpressPost] a
inner join JC_Express c on a.ExpressID=c.ExpressID
where a.ExpressID=@ExpressID and a.CompanyId=0
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.AddInParameter(cmd, "@ExpressID", DbType.Int32, ExpressID);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<JC_ExpressPost>();
return model;
}
#endregion
#region 返回订单Model
public static List<TradeManageNew.DT_OrderExpressModel> GetOrderModelList(string OrderIds)
{
List<TradeManageNew.DT_OrderExpressModel> list = null;
string tsql = @"select a.OrderId,a.PlatOrderCode,a.JoinOrderCode,a.OrderCode,a.TotalPrice,a.MoneyCode,b.RevAddr,b.RevMail,b.RevCountry,b.RevPhone,b.RevMoblie,ISNULL(b.RevProvinceCode,b.RevProvince) RevProvince,b.RevPhone,b.CountryCode,b.RevCity,b.RevArea,b.Post,b.RevMail,b.LogisticsId,b.RevName,b.RevPostCode,b.CountryName,a.State,b.CustomID,a.PlatId from DT_OrderInfo a
inner join DT_OrderXXInfo b on a.OrderId=b.OrderId
where a.OrderId in (" + OrderIds + ")";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
DataSet ds = db.ExecuteDataSet(cmd);
list = ds.Tables[0].ToList<TradeManageNew.DT_OrderExpressModel>();
return list;
}
#endregion
#region 查询订单明细匹配
public static List<DT_OrderGoodsNew> GetOrderGoodsList2(string OrderIds)
{
List<DT_OrderGoodsNew> list = null;
string tsql = @"
select a.Id,a.OrderId,a.DetailId,a.GoodsNum,f.GoodsName,f.GoodsCode,Weight=case when e.Weight is not null then e.Weight else f.Weight end,f.Weight2,f.Height,f.Long,f.Width,f.Height2,f.Long2,f.Width2,GoodsSKU=e.sku1,g.sendname from DT_OrderGoods a
inner join DT_OrderInfo d on a.OrderId=d.OrderId
inner join HW_GoodsDetail e on a.DetailId=e.DetailId
inner join HW_GoodsInfo f on e.GoodsId=f.GoodsId
inner join jc_shop g on d.shopid=g.shopid
where a.OrderId in (" + OrderIds + ")";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@OrderIds", DbType.String, OrderIds);
DataSet ds = db.ExecuteDataSet(cmd);
list = ds.Tables[0].ToList<DT_OrderGoodsNew>();
return list;
}
#endregion
#region 保存订单跟踪码申请
public static void SaveTrackCodeApply(DT_TrackCodeApplyNew Model)
{
string tsql = @"
select top 1 @LogisticsId=LogisticsId from JC_Express where ExpressId=@PostId
if @PostError is not null and @PostError<>''
begin
update DT_OrderXXInfo set LogisticsId=@LogisticsId,PostError=@PostError,Post=@PostId where OrderId=@OrderId
update DT_OrderXXInfoNew set LogisticsId=@LogisticsId,PostError=@PostError,Post=@PostId where OrderId=@OrderId
end
else
begin
update DT_OrderXXInfo set LogisticsId=@LogisticsId,PostError=null,TrackState=1,Post=@PostId where OrderId=@OrderId
update DT_OrderXXInfoNew set LogisticsId=@LogisticsId,PostError=null,TrackState=1,Post=@PostId where OrderId=@OrderId
end
INSERT INTO [DT_TrackCodeApply]([PostId],[TrackType],[TrackCode],[TrackCode2],[GetDate],[InName],[OrderId],[State],[UpdateTime],[OrderCode],[SKU],[Weight],[Long],[Width],[Height],[OrderCode2],LabelUrl)values(@PostId,@TrackType,@TrackCode,@TrackCode2,@GetDate,@InName,@OrderId,@State,@UpdateTime,@OrderCode,@SKU,@Weight,@Long,@Width,@Height,@OrderCode2,@LabelUrl)
set @Id=SCOPE_IDENTITY()
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@LogisticsId", DbType.Int32, Model.LogisticsId);
db.AddInParameter(cmd, "@PostId", DbType.Int32, Model.PostId);
db.AddInParameter(cmd, "@TrackType", DbType.Int32, Model.TrackType);
db.AddInParameter(cmd, "@TrackCode", DbType.String, Model.TrackCode);
db.AddInParameter(cmd, "@TrackCode2", DbType.String, Model.TrackCode2);
db.AddInParameter(cmd, "@GetDate", DbType.DateTime, Model.GetDate);
db.AddInParameter(cmd, "@InName", DbType.String, Model.InName);
db.AddInParameter(cmd, "@OrderId", DbType.Int32, Model.OrderId);
db.AddInParameter(cmd, "@State", DbType.Int32, Model.State);
db.AddInParameter(cmd, "@UpdateTime", DbType.DateTime, Model.UpdateTime);
db.AddInParameter(cmd, "@OrderCode", DbType.String, Model.OrderCode);
db.AddInParameter(cmd, "@IsSpare", DbType.Int32, Model.IsSpare);
db.AddInParameter(cmd, "@JoinOrderCode", DbType.String, Model.JoinOrderCode);
db.AddInParameter(cmd, "@PostError", DbType.String, Model.PostError);
db.AddInParameter(cmd, "@LabelUrl", DbType.String, Model.LabelUrl);
db.AddInParameter(cmd, "@SKU", DbType.String, Model.SKU);
db.AddInParameter(cmd, "@Weight", DbType.String, Model.Weight2);
db.AddInParameter(cmd, "@Long", DbType.String, Model.Long);
db.AddInParameter(cmd, "@Width", DbType.String, Model.Width);
db.AddInParameter(cmd, "@Height", DbType.String, Model.Height);
db.AddInParameter(cmd, "@OrderCode2", DbType.String, Model.OrderCode2);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 保存
public static int Save_OrderLTLFee(int OrderId, decimal FeeLTL)
{
string tsql = @"
Update [DT_OrderInfo] set [FeeLTL]=@FeeLTL where OrderId=@OrderId
exec [dbo].[ComputerOrderFee_Order] @OrderId
select 1";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@OrderId", DbType.Int32, OrderId);
db.AddInParameter(cmd, "@FeeLTL", DbType.Decimal, FeeLTL);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存
public static int Save_ErrorJH(int JHError, string JHRemark, int OrderId)
{
string tsql = @"
Update [DT_OrderInfo] set [JHError]=@JHError,[JHRemark]=@JHRemark where OrderId=@OrderId
select @OrderId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@OrderId", DbType.Int32, OrderId);
db.AddInParameter(cmd, "@JHError", DbType.Int32, JHError);
db.AddInParameter(cmd, "@JHRemark", DbType.String, JHRemark);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 修改订单状态
public static void UpdateOrderWLSS(int orderid, int WLSS)
{
string tsql = @"
update DT_OrderInfo set WLSS=@WLSS where orderid=@orderid";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@orderid", DbType.Int32, orderid);
db.AddInParameter(cmd, "@WLSS", DbType.Int32, WLSS);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 删除
public static void Delete_PostVote(int OrderId)
{
string tsql = @"
declare @Id int
select top 1 @Id=Id from DT_PostVote where OrderId=@OrderId
delete from DT_PostVote where Id=@Id
delete from DT_PostVoteDetail where mid=@Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@OrderId", DbType.Int32, OrderId);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 保存
public static int Save_PostVote(DT_PostVote Model)
{
string tsql = @"
select top 1 @Id=Id from DT_PostVote where OrderId=@OrderId
if @Id>0
begin
Update [DT_PostVote] set [OrderId]=@OrderId,[trackcode]=@trackcode,[ActualWeightAmount]=@ActualWeightAmount,[ActualWeightUnits]=@ActualWeightUnits,[RatedWeightAmount]=@RatedWeightAmount,[RatedWeightUnits]=@RatedWeightUnits,[DimLength]=@DimLength,[DimWidth]=@DimWidth,[DimHeight]=@DimHeight,[DimDivisor]=@DimDivisor,[DimUnit]=@DimUnit,[ZoneCode]=@ZoneCode,[bigdifference]=@bigdifference,[result]=@result,[sku]=@sku,[inname]=@inname,[fee1]=@fee1,[fee2]=@fee2,[fee3]=@fee3,[fee4]=@fee4,[fee5]=@fee5,[int1]=@int1,[int2]=@int2,[int3]=@int3,[str1]=@str1,[str2]=@str2,[str3]=@str3,[NetChargeAmount]=@NetChargeAmount,[estimatefee]=@estimatefee where Id=@Id
delete from DT_PostVoteDetail where mid=@Id
end
else
begin
INSERT INTO [DT_PostVote]([OrderId],[trackcode],[ActualWeightAmount],[ActualWeightUnits],[RatedWeightAmount],[RatedWeightUnits],[DimLength],[DimWidth],[DimHeight],[DimDivisor],[DimUnit],[ZoneCode],[bigdifference],[result],[sku],[indate],[inname],[fee1],[fee2],[fee3],[fee4],[fee5],[int1],[int2],[int3],[str1],[str2],[str3],[NetChargeAmount],[estimatefee])values(@OrderId,@trackcode,@ActualWeightAmount,@ActualWeightUnits,@RatedWeightAmount,@RatedWeightUnits,@DimLength,@DimWidth,@DimHeight,@DimDivisor,@DimUnit,@ZoneCode,@bigdifference,@result,@sku,@indate,@inname,@fee1,@fee2,@fee3,@fee4,@fee5,@int1,@int2,@int3,@str1,@str2,@str3,@NetChargeAmount,@estimatefee)
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, "@OrderId", DbType.Int32, Model.OrderId);
db.AddInParameter(cmd, "@trackcode", DbType.String, Model.trackcode);
db.AddInParameter(cmd, "@ActualWeightAmount", DbType.Decimal, Model.ActualWeightAmount);
db.AddInParameter(cmd, "@ActualWeightUnits", DbType.String, Model.ActualWeightUnits);
db.AddInParameter(cmd, "@RatedWeightAmount", DbType.Decimal, Model.RatedWeightAmount);
db.AddInParameter(cmd, "@RatedWeightUnits", DbType.String, Model.RatedWeightUnits);
db.AddInParameter(cmd, "@DimLength", DbType.Decimal, Model.DimLength);
db.AddInParameter(cmd, "@DimWidth", DbType.Decimal, Model.DimWidth);
db.AddInParameter(cmd, "@DimHeight", DbType.Decimal, Model.DimHeight);
db.AddInParameter(cmd, "@DimDivisor", DbType.Decimal, Model.DimDivisor);
db.AddInParameter(cmd, "@DimUnit", DbType.String, Model.DimUnit);
db.AddInParameter(cmd, "@ZoneCode", DbType.Decimal, Model.ZoneCode);
db.AddInParameter(cmd, "@bigdifference", DbType.Decimal, Model.bigdifference);
db.AddInParameter(cmd, "@result", DbType.String, Model.result);
db.AddInParameter(cmd, "@sku", DbType.String, Model.sku);
db.AddInParameter(cmd, "@indate", DbType.DateTime, Model.indate);
db.AddInParameter(cmd, "@inname", DbType.String, Model.inname);
db.AddInParameter(cmd, "@fee1", DbType.Decimal, Model.fee1);
db.AddInParameter(cmd, "@fee2", DbType.Decimal, Model.fee2);
db.AddInParameter(cmd, "@fee3", DbType.Decimal, Model.fee3);
db.AddInParameter(cmd, "@fee4", DbType.Decimal, Model.fee4);
db.AddInParameter(cmd, "@fee5", DbType.Decimal, Model.fee5);
db.AddInParameter(cmd, "@int1", DbType.Int32, Model.int1);
db.AddInParameter(cmd, "@int2", DbType.Int32, Model.int2);
db.AddInParameter(cmd, "@int3", DbType.Int32, Model.int3);
db.AddInParameter(cmd, "@str1", DbType.String, Model.str1);
db.AddInParameter(cmd, "@str2", DbType.String, Model.str2);
db.AddInParameter(cmd, "@str3", DbType.String, Model.str3);
db.AddInParameter(cmd, "@NetChargeAmount", DbType.Decimal, Model.NetChargeAmount);
db.AddInParameter(cmd, "@estimatefee", DbType.Decimal, Model.estimatefee);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
tsql = @"INSERT INTO [DT_PostVoteDetail]([mid],[TrackingIDChargDescription],[TrackingIDChargeAmount])values(@mid,@TrackingIDChargDescription,@TrackingIDChargeAmount)";
cmd = db.GetSqlStringCommand(tsql);
foreach (var item in Model.list)
{
cmd.Parameters.Clear();
db.AddInParameter(cmd, "@mid", DbType.Int32, a);
db.AddInParameter(cmd, "@TrackingIDChargDescription", DbType.String, item.TrackingIDChargDescription);
db.AddInParameter(cmd, "@TrackingIDChargeAmount", DbType.Decimal, item.TrackingIDChargeAmount);
db.ExecuteNonQuery(cmd);
}
return a;
}
#endregion
#region 保存赔款申请
public static int SaveBackMoneyApply2(int BackState, DT_OrderBackMoneyApply Model)
{
string tsql = @"
declare @BackAdvise3 nvarchar(1000),@BackAdvise4 nvarchar(1000),@IsRead int
set @IsRead=1
select top 1 @Id=Id,@BackAdvise3=isnull(BackAdvise,''),@BackAdvise4=isnull(BackAdvise2,'') from DT_OrderBackMoneyApply where OrderId=@OrderId
if @BackAdvise<>'' and @BackAdvise3<>@BackAdvise
begin
set @IsRead=0
end
if @BackAdvise2<>'' and @BackAdvise3<>@BackAdvise2
begin
set @IsRead=2
end
if @Id>0
begin
Update [DT_OrderBackMoneyApply] set [GoodsCode]=@GoodsCode,[BackReason1]=@BackReason1,[BackReason2]=@BackReason2,[BackReason3]=@BackReason3,[Remark]=@Remark,[OrderPrice]=@OrderPrice,[BackPrice]=@BackPrice,[PlanPrice]=@PlanPrice,[FactPrice]=@FactPrice,[DeptName]=@DeptName,[DeptRemark]=@DeptRemark,[ShopId]=@ShopId,[MoneyCode1]=@MoneyCode1,[MoneyCode2]=@MoneyCode2,[MoneyCode3]=@MoneyCode3,[MoneyCode4]=@MoneyCode4,BackDate=@BackDate,ImageIds=@ImageIds,[PostCode]=@PostCode,[PostFee]=@PostFee,[BackState]=@BackState,[PostFee2]=@PostFee2,[BackState2]=@BackState2,[BackType]=@BackType,[OrderState]=@OrderState,[GoodState]=@GoodState,[BackAdvise]=@BackAdvise,[BackAdvise2]=@BackAdvise2,IsRead=@IsRead where Id=@Id
end
else
begin
INSERT INTO [DT_OrderBackMoneyApply]([OrderId],[OrderCode],[GoodsCode],[BackReason1],[BackReason2],[BackReason3],[Remark],[OrderPrice],[BackPrice],[PlanPrice],[FactPrice],[InName],[InUserId],[DeptName],[DeptRemark],[CompanyRemark],[CompanyRemark2],[CompanyName1],[Indate],[CompanyDate1],[CompanyDate2],[CompanyAgree1],[CompanyAgree2],[CompanyUserId],[ShopId],[MoneyCode1],[MoneyCode2],[MoneyCode3],[MoneyCode4],[ImageIds],[BackDate],[PostCode],[PostFee],[PostFee2],[BackState],[BackState2],[BackType],[OrderState],[GoodState],[BackAdvise],[BackAdvise2])values(@OrderId,@OrderCode,@GoodsCode,@BackReason1,@BackReason2,@BackReason3,@Remark,@OrderPrice,@BackPrice,@PlanPrice,@FactPrice,@InName,@InUserId,@DeptName,@DeptRemark,@CompanyRemark,@CompanyRemark2,@CompanyName1,@Indate,@CompanyDate1,@CompanyDate2,@CompanyAgree1,@CompanyAgree2,@CompanyUserId,@ShopId,@MoneyCode1,@MoneyCode2,@MoneyCode3,@MoneyCode4,@ImageIds,@BackDate,@PostCode,@PostFee,@PostFee2,@BackState,@BackState2,@BackType,@OrderState,@GoodState,@BackAdvise,@BackAdvise2)
set @Id=SCOPE_IDENTITY()
end
select @Id";
if (BackState == 0)
{
tsql = @"
delete from DT_OrderBackMoneyApply where OrderId=@OrderId
select 1
";
}
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@OrderId", DbType.Int32, Model.OrderId);
db.AddInParameter(cmd, "@OrderCode", DbType.String, Model.OrderCode);
db.AddInParameter(cmd, "@GoodsCode", DbType.String, Model.GoodsCode);
db.AddInParameter(cmd, "@BackReason1", DbType.String, Model.BackReason1);
db.AddInParameter(cmd, "@BackReason2", DbType.String, Model.BackReason2);
db.AddInParameter(cmd, "@BackReason3", DbType.String, Model.BackReason3);
db.AddInParameter(cmd, "@Remark", DbType.String, Model.Remark);
db.AddInParameter(cmd, "@OrderPrice", DbType.Decimal, Model.OrderPrice);
db.AddInParameter(cmd, "@BackPrice", DbType.Decimal, Model.BackPrice);
db.AddInParameter(cmd, "@PlanPrice", DbType.Decimal, Model.PlanPrice);
db.AddInParameter(cmd, "@FactPrice", DbType.Decimal, Model.FactPrice);
db.AddInParameter(cmd, "@InName", DbType.String, Model.InName);
db.AddInParameter(cmd, "@InUserId", DbType.Int32, Model.InUserId);
db.AddInParameter(cmd, "@DeptName", DbType.String, Model.DeptName);
db.AddInParameter(cmd, "@DeptRemark", DbType.String, Model.DeptRemark);
db.AddInParameter(cmd, "@CompanyRemark", DbType.String, Model.CompanyRemark);
db.AddInParameter(cmd, "@CompanyRemark2", DbType.String, Model.CompanyRemark2);
db.AddInParameter(cmd, "@CompanyName1", DbType.String, Model.CompanyName1);
db.AddInParameter(cmd, "@Indate", DbType.DateTime, Model.Indate);
db.AddInParameter(cmd, "@CompanyDate1", DbType.DateTime, Model.CompanyDate1);
db.AddInParameter(cmd, "@CompanyDate2", DbType.DateTime, Model.CompanyDate2);
db.AddInParameter(cmd, "@CompanyAgree1", DbType.String, Model.CompanyAgree1);
db.AddInParameter(cmd, "@CompanyAgree2", DbType.String, Model.CompanyAgree2);
db.AddInParameter(cmd, "@ShopId", DbType.Int32, Model.ShopId);
db.AddInParameter(cmd, "@MoneyCode1", DbType.String, Model.MoneyCode1);
db.AddInParameter(cmd, "@MoneyCode2", DbType.String, Model.MoneyCode2);
db.AddInParameter(cmd, "@MoneyCode3", DbType.String, Model.MoneyCode3);
db.AddInParameter(cmd, "@MoneyCode4", DbType.String, Model.MoneyCode4);
db.AddInParameter(cmd, "@BackDate", DbType.DateTime, Model.BackDate);
db.AddInParameter(cmd, "@CompanyUserId", DbType.String, Model.CompanyUserId);
db.AddInParameter(cmd, "@PostFee", DbType.Decimal, Model.PostFee);
db.AddInParameter(cmd, "@PostCode", DbType.String, Model.PostCode);
db.AddInParameter(cmd, "@BackState", DbType.Int32, Model.BackState);
db.AddInParameter(cmd, "@ImageIds", DbType.String, Model.ImageIds);
db.AddInParameter(cmd, "@PostFee2", DbType.Decimal, Model.PostFee2);
db.AddInParameter(cmd, "@BackState2", DbType.Int32, Model.BackState2);
db.AddInParameter(cmd, "@BackType", DbType.Int32, Model.BackType);
db.AddInParameter(cmd, "@OrderState", DbType.Int32, Model.OrderState);
db.AddInParameter(cmd, "@GoodState", DbType.Int32, Model.GoodState);
db.AddInParameter(cmd, "@BackAdvise", DbType.String, Model.BackAdvise);
db.AddInParameter(cmd, "@BackAdvise2", DbType.String, Model.BackAdvise2);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 返回Model
public static void ComputerOrderFee_Order(int orderid)
{
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetStoredProcCommand("ComputerOrderFee_Order");
db.AddInParameter(cmd, "@orderid", DbType.Int32, orderid);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 根据sku1获取商品详情
public static HW_GoodsDetail GetGoodsDetailBySku1(string sku1)
{
var sql = "select top 1 DetailId,GoodsId from HW_GoodsDetail where SKU1=@sku ";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@sku", DbType.String, sku1);
var dt = db.ExecuteDataTable(cmd);
if (dt != null && dt.Rows.Count > 0)
{
return dt.Rows[0].ToModel<HW_GoodsDetail>();
}
else
return null;
}
#endregion
public static void UpdateTrackingCodeSKU(int id, string sku)
{
var sql = @" update DT_TrackCodeApply set SKU=@sku where Id=@ID";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@ID", DbType.Int32, id);
db.AddInParameter(cmd, "@sku", DbType.String, sku);
db.ExecuteNonQuery(cmd);
}
#region 标记LTL数量
public static void UpdateOrderLTLBack(int OrderId)
{
string tsql = @"
update DT_OrderInfo set PostState=0
where OrderId=@OrderId and PostState=3
update DT_OrderInfoNew set PostState=0
where OrderId=@OrderId and PostState=3
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@OrderId", DbType.Int32, OrderId);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 标记LTL数量
public static void UpdateOrderLTL(int OrderId)
{
string tsql = @"
update DT_OrderInfo set PostState=3
where OrderId=@OrderId and state<>2
update DT_OrderInfoNew set PostState=3
where OrderId=@OrderId and state<>2
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@OrderId", DbType.Int32, OrderId);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 删除订单
public static void DeleteOrderForTM2(string OrderIds)
{
string tsql = @"
if(select count(1) from DT_OrderInfo where OrderId in (" + OrderIds + @") and MateState>0)=0
begin
delete from DT_OrderXXInfo where OrderId in (" + OrderIds + @")
delete from DT_OrderXXInfoNew where OrderId in (" + OrderIds + @")
update b set b.GoodsPlanNum=ISNULL(b.GoodsPlanNum,0)-isnull(a.GoodsNum,0) from [DT_OrderGoods] a
inner join HW_GoodsDetail b on a.DetailId=b.DetailId
where a.OrderId in (" + OrderIds + @")
delete from API_OrderInfo where OrderCode in (select distinct PlatOrderCode from DT_OrderInfo where OrderId in (" + OrderIds + @"))
delete from DT_OrderGoods where OrderId in (" + OrderIds + @")
delete from DT_OrderInfo where OrderId in (" + OrderIds + @")
delete from DT_OrderInfoNew where OrderId in (" + OrderIds + @")
delete from DT_TrackCodeApply where OrderId in (" + OrderIds + @")
end ";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.ExecuteNonQuery(cmd);
}
#endregion
#region
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
public static List<DT_OrderMateGoods> GetFedexOrderGoodsList(string OrderIds)
{
string query = " select a.Id,a.OrderId,a.GoodsNum,Weight=case when c.Weight2 is not null and c.Weight2>0 then c.Weight2 when c.Weight is not null and c.Weight>0 then c.Weight*0.035274 else null end,c.Width2,Length2=c.Long2,c.Height2,InPrice=isnull(c.InPrice,0) from DT_OrderGoods a with(nolock) inner join HW_GoodsDetail b with(nolock) on a.DetailId=b.DetailId inner join HW_GoodsInfo c with(nolock) on b.GoodsId=c.GoodsId where OrderId in (" + OrderIds + ")";
Database database = DatabaseFactory.CreateDatabase();
DbCommand sqlStringCommand = database.GetSqlStringCommand(query);
return database.ExecuteDataSet(sqlStringCommand).Tables[0].ToList<DT_OrderMateGoods>();
}
public static List<DT_OrderModel> GetFedexOrderList2(string OrderIds)
{
string query = " select b.* from DT_OrderInfo a with(nolock) inner join DT_OrderXXInfo b with(nolock) on a.OrderId=b.OrderId where a.OrderId in (" + OrderIds + ")";
Database database = DatabaseFactory.CreateDatabase();
DbCommand sqlStringCommand = database.GetSqlStringCommand(query);
return database.ExecuteDataTable(sqlStringCommand).ToList<DT_OrderModel>();
}
#region 返回Model
public static void GetModel_OrderFeeML()
{
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetStoredProcCommand("ComputerOrderFees_WL");
db.ExecuteNonQuery(cmd);
}
#endregion
public static List<HW_Goods_New> GetListHW_GoodsInfoNewForTM(int userid, int CompanyId, string SKU, int IsDH, int IsBH, RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
{
string tsql1 = @"
select distinct a.GoodsId from HW_GoodsDetail a with(nolock)
inner join HW_GoodsInfo b with(nolock) on a.GoodsId=b.GoodsId
where b.CompanyId=@CompanyId and (@SKU='' or a.SKU1 like '%'+@SKU+'%' or a.SKU2 like '%'+@SKU+'%' or a.SKU3 like '%'+@SKU+'%' or a.SKU4 like '%'+@SKU+'%' or a.SKU5 like '%'+@SKU+'%' or a.SKU6 like '%'+@SKU+'%' or a.TypeCode like '%'+@SKU+'%' or a.TypeDesc like '%'+@SKU+'%' or b.GoodsName like '%'+@SKU+'%' or b.GoodsCode like '%'+@SKU+'%') and (@IsDH=0 or a.NoGoods=1) and (@IsSafe=0 or a.SafeNum>0)
";
string[] templist = SKU.Split(' ');
if (templist.Length > 1)
{
tsql1 = @"
select distinct a.GoodsId from HW_GoodsDetail a with(nolock)
inner join HW_GoodsInfo b with(nolock) on a.GoodsId=b.GoodsId
WHERE (a.TypeCode LIKE '%" + templist[0] + @"%' OR a.TypeDesc LIKE '%" + templist[0] + @"%' OR b.GoodsCode LIKE '%" + templist[0] + @"%' OR b.GoodsOldCode LIKE '%" + templist[0] + @"%' OR b.GoodsName LIKE '%" + templist[0] + @"%')
and (a.TypeCode LIKE '%" + templist[1] + @"%' OR a.TypeDesc LIKE '%" + templist[1] + @"%' OR b.GoodsCode LIKE '%" + templist[1] + @"%' OR b.GoodsOldCode LIKE '%" + templist[1] + @"%' OR b.GoodsName LIKE '%" + templist[1] + @"%')
";
}
if (where == null) where = new RefParameterCollection();
QueryService ser = new QueryService();
ser.Fields = @"GoodsId=cast(a.GoodsId as int),a.GoodsCode,a.GoodsEg,a.GoodsNo,a.GoodsOldCode,a.SortId,a.SortName,a.SupplierId,a.GoodsName,a.GoodsEnglisgName,b.GoodsNum,GoodsOutNum=isnull(b.GoodsNum,0)+isnull(b.GoodsInNum2,0),GoodsInNum2=isnull(b.GoodsInNum2,0),b.GoodsInNum,b.LeftNum,b.GoodsHJNum2,b.GoodsHJNum3,b.GoodsHJNum4,a.InPrice,a.NowPrice,a.Weight,a.WeightUnit,a.Solid,a.SolidUnit,a.Position,a.InDate,a.UpdateDate,a.FirstImgUrl,a.GoodsImageIds,a.CompanyId,a.GoodsPlanNum,b.GoodsHJNum,a.SafeNum,a.HGCode,a.HGCompanyCode,a.UpdateName,a.GoodsRemark,a.AutoPlan,a.NoGoods,a.AvgTime,a.State,NoGoodsState=case when a.NoGoods=1 then '是' else '否' end,a.Cert,a.UpOff,NoticeDays=case when b.GoodsHJNum2-isnull(b.GoodsNum,0)-isnull(b.GoodsInNum2,0)>0 then b.GoodsHJNum2/3 else 0 end,GoodsInNum3=isnull(b.GoodsInNum,0),IsNull(a.IsYC,0) IsYC,Case when IsNull(a.IsYC,0)=0 then '隐藏' else '显示' end YCXS,IsNull(c.BuyQty,0) BuyQty ";
if (SKU != "" || IsDH == 1 || IsBH == 1)
{
ser.Tables = @"(select * from HW_GoodsInfo a with(nolock)
where GoodsId in (" + tsql1 + @"))a left join (
select GoodsId,GoodsNum=isnull(SUM(GoodsNum),0),GoodsHJNum=isnull(SUM(GoodsHJNum),0),GoodsHJNum2=isnull(SUM(GoodsHJNum2),0),GoodsHJNum3=isnull(SUM(GoodsHJNum3),0),GoodsHJNum4=isnull(SUM(GoodsHJNum4),0),GoodsInNum=isnull(SUM(GoodsInNum),0),GoodsInNum2=isnull(SUM(GoodsInNum2),0),LeftNum=isnull(SUM(GoodsNum),0)+isnull(SUM(GoodsInNum2),0)-isnull(SUM(GoodsPlanNum),0) from HW_GoodsDetail with(nolock) group by GoodsId)b on a.GoodsId=b.GoodsId";
}
else
ser.Tables = @"HW_GoodsInfo a with(nolock)
left join (
select GoodsId,GoodsNum=isnull(SUM(GoodsNum),0),GoodsHJNum=isnull(SUM(GoodsHJNum),0),GoodsHJNum2=isnull(SUM(GoodsHJNum2),0),GoodsInNum=isnull(SUM(GoodsInNum),0),GoodsHJNum3=isnull(SUM(GoodsHJNum3),0),GoodsHJNum4=isnull(SUM(GoodsHJNum4),0),GoodsInNum2=isnull(SUM(GoodsInNum2),0),LeftNum=isnull(SUM(GoodsNum),0)+isnull(SUM(GoodsInNum2),0)-isnull(SUM(GoodsPlanNum),0) from HW_GoodsDetail with(nolock) group by GoodsId)b on a.GoodsId=b.GoodsId";
ser.Tables += @" left join (select GoodsId,SUM(Quantity) BuyQty from CK_GoodsApply where userid=@userid group by GoodsId) c on a.goodsid=c.Goodsid ";
ser.Filter = where.GetWhere(System.Data.CommandType.Text) + " and (a.muserid=@userid or a.guserid_ebay=@userid or a.guserid_wlmart=@userid or a.guserid_wayfair=@userid or a.guserid_shopify=@userid or a.guserid_sheIn=@userid or a.guserid_tiktok=@userid or a.guserid_temu=@userid ) ";
ser.PageIndex = PageIndex;
ser.PageSize = PageSize;
ser.Sort = Sort;
ser.KeyName = "GoodsId";
string tsql = ser.GetText();
where.AddOutParameter("RowCount", System.Data.DbType.Int32);
List<HW_Goods_New> ListModel = null;
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, where);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.AddInParameter(cmd, "@SKU", DbType.String, SKU);
db.AddInParameter(cmd, "@IsDH", DbType.Int32, IsDH);
db.AddInParameter(cmd, "@IsSafe", DbType.Int32, IsBH);
db.AddInParameter(cmd, "@userid", DbType.Int32, userid);
DataTable tb = db.ExecuteDataTable(cmd);
RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
ListModel = tb.ToList<HW_Goods_New>();
return ListModel;
}
#region 返回货物Model
public static HW_GoodsNew GetGoodsModelNew(int GoodsId)
{
HW_GoodsNew model = null;
string tsql = "select * from HW_GoodsInfo where GoodsId=@GoodsId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@GoodsId", DbType.Int32, GoodsId);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<HW_GoodsNew>();
return model;
}
#endregion
#region 查询货物明细列表
public static List<HW_GoodsDetailNew> GetGoodsDetail(int GoodsId)
{
List<HW_GoodsDetailNew> list = null;
string tsql = @"
select *,LeftNum=GoodsNum+GoodsInNum-GoodsPlanNum,NoGoodsState=case when NoGoods=1 then '是' else '否' end from HW_GoodsDetail where GoodsId=@GoodsId ORDER BY TypeCode
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@GoodsId", DbType.Int32, GoodsId);
DataSet ds = db.ExecuteDataSet(cmd);
list = ds.Tables[0].ToList<HW_GoodsDetailNew>();
return list;
}
#endregion
#region 查询货物链接列表
public static List<HW_BuyLinkNew> GetGoodsBuyLink(int GoodsId, int UserId)
{
List<HW_BuyLinkNew> list = null;
string tsql = @"
if(select count(0) from HW_BuyLink a
inner join JC_Shop b on a.LinkDesc=b.ShopName
where GoodsId=@GoodsId)=0
begin
select * from HW_BuyLink where GoodsId=@GoodsId
end
else if @UserId>1
begin
select * from HW_BuyLink where GoodsId=@GoodsId and LinkDesc in (select b.ShopName from JC_ShopUser a
inner join JC_Shop b on a.ShopId=b.ShopId
where UserId=@UserId and (SType='M' or SType='S'))
end
else
begin
select * from HW_BuyLink where GoodsId=@GoodsId
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@GoodsId", DbType.Int32, GoodsId);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
DataSet ds = db.ExecuteDataSet(cmd);
list = ds.Tables[0].ToList<HW_BuyLinkNew>();
return list;
}
#endregion
#region 返回列表
public static List<HW_JZLink> GetListJZLink(int GoodsId, int UserId)
{
//string tsql = "select a.Id,a.GoodsId,a.LinkUrl,a.Price,a.Sort,a.Price2,a.Price3,a.Remark,a.Remark2,a.Remark3 from HW_JZLink";
string tsql = @"
if @UserId>0
begin
select * from HW_JZLink where GoodsId=@GoodsId and shopid in (select ShopId from JC_ShopUser where UserId=@UserId and (SType='M' or SType='S'))
end
else
begin
select * from HW_JZLink where GoodsId=@GoodsId
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@GoodsId", DbType.Int32, GoodsId);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
DataTable tb = db.ExecuteDataTable(cmd);
List<HW_JZLink> ListModel = tb.ToList<HW_JZLink>();
return ListModel;
}
#endregion
#region 返回列表
public static HW_PostFee GetModelPostFee(int GoodsId)
{
HW_PostFee model = null;
//string tsql = "select a.Id,a.GoodsId,a.LinkUrl,a.Price,a.Sort,a.Price2,a.Price3,a.Remark,a.Remark2,a.Remark3 from HW_JZLink";
string tsql = @"
select * from HW_PostFee where GoodsId=@GoodsId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@GoodsId", DbType.Int32, GoodsId);
DataSet ds = db.ExecuteDataSet(cmd);
if (ds.Tables[0].Rows.Count > 0)
{
model = ds.Tables[0].Rows[0].ToModel<HW_PostFee>();
}
return model;
}
#endregion
#region 保存货物
/// <summary>
/// 保存货物
/// </summary>
public static int SaveGoodsInfoForTMNew(HW_GoodsNew Model)
{
string tsql = @"
if @GoodsId>0
begin
Update [HW_GoodsInfo] set GoodsOldCode=@GoodsOldCode,SortId=@SortId,SortId1=@SortId1,SortId2=@SortId2,[SupplierId]=@SupplierId,Supplier=@Supplier,[GoodsName]=@GoodsName,[GoodsEnglisgName]=@GoodsEnglisgName,[GoodsNum]=@GoodsNum,[GoodsLockNum]=@GoodsLockNum,[InPrice]=@InPrice,[NowPrice]=@NowPrice,[Weight]=@Weight,[Weight2]=@Weight2,[WeightUnit]=@WeightUnit,[Solid]=@Solid,[SolidUnit]=@SolidUnit,[Position]=@Position,[UpdateDate]=@UpdateDate,[FirstImgUrl]=@FirstImgUrl,[GoodsImageIds]=@GoodsImageIds,[CompanyId]=@CompanyId,[GoodsPlanNum]=@GoodsPlanNum,[GoodsOutNum]=@GoodsOutNum,[GoodsInNum]=@GoodsInNum,[SafeNum]=@SafeNum,[HGCode]=@HGCode,[HGCompanyCode]=@HGCompanyCode,[UpdateName]=@UpdateName,[GoodsRemark]=@GoodsRemark,[AutoPlan]=@AutoPlan,[NoGoods]=@NoGoods,[AvgTime]=@AvgTime,[State]=@State,[DeptRemark]=@DeptRemark,[NoticeDays]=@NoticeDays,[GoodsInfo]=@GoodsInfo,[DefaultCity]=@DefaultCity,[PageFee]=@PageFee,[PostPrice]=@PostPrice,GoodsSupplyCode=@GoodsSupplyCode,BGPrice=@BGPrice,NoticeShop=@NoticeShop,[NetWeight]=@NetWeight,[Long]=@Long,[Width]=@Width,[Height]=@Height,[Long2]=@Long2,[Width2]=@Width2,[Height2]=@Height2,[BoxRate]=@BoxRate,Cert=@Cert,FeeRate=@FeeRate,JYPrice=@JYPrice,BoxNum=@BoxNum,BoxStoreCode=@BoxStoreCode,JHDays=@JHDays,KFName=@KFName,KFName2=@KFName2,SortName=@SortName,SubsidyFee=@SubsidyFee where GoodsId=@GoodsId
end
else
begin
--select @GoodsNo=isnull(max(GoodsNo),0)+1 from HW_GoodsInfo where SortId=@SortId
INSERT INTO [HW_GoodsInfo]([GoodsCode],[GoodsEg],[GoodsNo],[GoodsOldCode],[SortId],SortId1,SortId2,[SortName],[SupplierId],[GoodsName],[GoodsEnglisgName],[GoodsNum],[GoodsLockNum],[InPrice],[NowPrice],[Weight],[Weight2],[WeightUnit],[Solid],[SolidUnit],[Position],[InDate],[UpdateDate],[FirstImgUrl],[GoodsImageIds],[CompanyId],[GoodsPlanNum],[GoodsOutNum],[GoodsInNum],[SafeNum],[HGCode],[HGCompanyCode],[UpdateName],[GoodsRemark],[AutoPlan],[NoGoods],[AvgTime],[State],[DeptRemark],[NoticeDays],[GoodsInfo],[DefaultCity],[PageFee],[PostPrice],GoodsSupplyCode,BGPrice,NoticeShop,[NetWeight],[Long],[Width],[Height],[Long2],[Width2],[Height2],[BoxRate],Cert,FeeRate,JYPrice,BoxNum,BoxStoreCode,JHDays,KFName,KFName2,SubsidyFee)values(@GoodsCode,@GoodsEg,@GoodsNo,@GoodsOldCode,@SortId,@SortId1,@SortId2,@SortName,@SupplierId,@GoodsName,@GoodsEnglisgName,@GoodsNum,@GoodsLockNum,@InPrice,@NowPrice,@Weight,@Weight2,@WeightUnit,@Solid,@SolidUnit,@Position,@InDate,@UpdateDate,@FirstImgUrl,@GoodsImageIds,@CompanyId,@GoodsPlanNum,@GoodsOutNum,@GoodsInNum,@SafeNum,@HGCode,@HGCompanyCode,@UpdateName,@GoodsRemark,@AutoPlan,@NoGoods,@AvgTime,@State,@DeptRemark,@NoticeDays,@GoodsInfo,@DefaultCity,@PageFee,@PostPrice,@GoodsSupplyCode,@BGPrice,@NoticeShop,@NetWeight,@Long,@Width,@Height,@Long2,@Width2,@Height2,@BoxRate,@Cert,@FeeRate,@JYPrice,@BoxNum,@BoxStoreCode,@JHDays,@KFName,@KFName2,@SubsidyFee)
set @GoodsId=SCOPE_IDENTITY()
update XP_GoodsSelect set GoodsCode=@GoodsCode,IsAdd=1 where Id=@XPId
INSERT INTO [HW_GoodsCode]([EnglishCode],[GoodsNo])
values(@GoodsEg,@GoodsNo)
end
select @GoodsId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@GoodsId", DbType.Int32, Model.GoodsId);
db.AddInParameter(cmd, "@GoodsCode", DbType.String, Model.GoodsCode);
db.AddInParameter(cmd, "@GoodsEg", DbType.String, Model.GoodsEg);
db.AddInParameter(cmd, "@GoodsNo", DbType.Int32, Model.GoodsNo);
db.AddInParameter(cmd, "@GoodsOldCode", DbType.String, Model.GoodsOldCode);
db.AddInParameter(cmd, "@SortId", DbType.Int32, Model.SortId);
db.AddInParameter(cmd, "@SortId1", DbType.Int32, Model.SortId1);
db.AddInParameter(cmd, "@SortId2", DbType.String, Model.SortId2);
db.AddInParameter(cmd, "@SortName", DbType.String, Model.SortName);
db.AddInParameter(cmd, "@SupplierId", DbType.String, Model.SupplierId);
db.AddInParameter(cmd, "@Supplier", DbType.Int32, Model.Supplier);
db.AddInParameter(cmd, "@GoodsName", DbType.String, Model.GoodsName);
db.AddInParameter(cmd, "@GoodsEnglisgName", DbType.String, Model.GoodsEnglisgName);
db.AddInParameter(cmd, "@GoodsNum", DbType.Int32, Model.GoodsNum);
db.AddInParameter(cmd, "@GoodsLockNum", DbType.Int32, Model.GoodsLockNum);
db.AddInParameter(cmd, "@InPrice", DbType.Decimal, Model.InPrice);
db.AddInParameter(cmd, "@NowPrice", DbType.Decimal, Model.NowPrice);
db.AddInParameter(cmd, "@Weight", DbType.Decimal, Model.Weight);
db.AddInParameter(cmd, "@Weight2", DbType.Decimal, Model.Weight2);
db.AddInParameter(cmd, "@WeightUnit", DbType.String, Model.WeightUnit);
db.AddInParameter(cmd, "@Solid", DbType.Decimal, Model.Solid);
db.AddInParameter(cmd, "@SolidUnit", DbType.String, Model.SolidUnit);
db.AddInParameter(cmd, "@Position", DbType.Int32, Model.Position);
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
db.AddInParameter(cmd, "@UpdateDate", DbType.DateTime, Model.UpdateDate);
db.AddInParameter(cmd, "@FirstImgUrl", DbType.String, Model.FirstImgUrl);
db.AddInParameter(cmd, "@GoodsImageIds", DbType.String, Model.GoodsImageIds);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
db.AddInParameter(cmd, "@GoodsPlanNum", DbType.Int32, Model.GoodsPlanNum);
db.AddInParameter(cmd, "@GoodsOutNum", DbType.Int32, Model.GoodsOutNum);
db.AddInParameter(cmd, "@GoodsInNum", DbType.Int32, Model.GoodsInNum);
db.AddInParameter(cmd, "@SafeNum", DbType.Int32, Model.SafeNum);
db.AddInParameter(cmd, "@HGCode", DbType.String, Model.HGCode);
db.AddInParameter(cmd, "@HGCompanyCode", DbType.String, Model.HGCompanyCode);
db.AddInParameter(cmd, "@UpdateName", DbType.String, Model.UpdateName);
db.AddInParameter(cmd, "@GoodsRemark", DbType.String, Model.GoodsRemark);
db.AddInParameter(cmd, "@AutoPlan", DbType.Int32, Model.AutoPlan);
db.AddInParameter(cmd, "@NoGoods", DbType.Int32, Model.NoGoods);
db.AddInParameter(cmd, "@AvgTime", DbType.Decimal, Model.AvgTime);
db.AddInParameter(cmd, "@State", DbType.Int32, Model.State);
db.AddInParameter(cmd, "@DeptRemark", DbType.String, Model.DeptRemark);
db.AddInParameter(cmd, "@NoticeDays", DbType.Int32, Model.NoticeDays);
db.AddInParameter(cmd, "@GoodsInfo", DbType.String, Model.GoodsInfo);
db.AddInParameter(cmd, "@DefaultCity", DbType.String, Model.DefaultCity);
db.AddInParameter(cmd, "@PageFee", DbType.Decimal, Model.PageFee);
db.AddInParameter(cmd, "@PostPrice", DbType.Decimal, Model.PostPrice);
db.AddInParameter(cmd, "@GoodsSupplyCode", DbType.String, Model.GoodsSupplyCode);
db.AddInParameter(cmd, "@BGPrice", DbType.Decimal, Model.BGPrice);
db.AddInParameter(cmd, "@NoticeShop", DbType.String, Model.NoticeShop);
db.AddInParameter(cmd, "@XPId", DbType.Int32, Model.XPId);
db.AddInParameter(cmd, "@NetWeight", DbType.Decimal, Model.NetWeight);
db.AddInParameter(cmd, "@Long", DbType.Decimal, Model.Long);
db.AddInParameter(cmd, "@Width", DbType.Decimal, Model.Width);
db.AddInParameter(cmd, "@Height", DbType.Decimal, Model.Height);
db.AddInParameter(cmd, "@Long2", DbType.Decimal, Model.Long2);
db.AddInParameter(cmd, "@Width2", DbType.Decimal, Model.Width2);
db.AddInParameter(cmd, "@Height2", DbType.Decimal, Model.Height2);
db.AddInParameter(cmd, "@BoxRate", DbType.Int32, Model.BoxRate);
db.AddInParameter(cmd, "@Cert", DbType.String, Model.Cert);
db.AddInParameter(cmd, "@FeeRate", DbType.Decimal, Model.FeeRate);
db.AddInParameter(cmd, "@JYPrice", DbType.Decimal, Model.JYPrice);
db.AddInParameter(cmd, "@BoxNum", DbType.Int32, Model.BoxNum);
db.AddInParameter(cmd, "@BoxStoreCode", DbType.String, Model.BoxStoreCode);
db.AddInParameter(cmd, "@JHDays", DbType.Int32, Model.JHDays);
db.AddInParameter(cmd, "@KFName", DbType.String, Model.KFName);
db.AddInParameter(cmd, "@KFName2", DbType.String, Model.KFName2);
db.AddInParameter(cmd, "@SubsidyFee", DbType.Decimal, Model.SubsidyFee);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存货物明细
/// <summary>
/// 保存货物明细
/// </summary>
public static int SaveGoodsDetail(HW_GoodsDetailNew Model)
{
string tsql = @"
if @DetailId>0
begin
Update [HW_GoodsDetail] set [GoodsId]=@GoodsId,[TypeCode]=@TypeCode,[TypeDesc]=@TypeDesc,[SKU1]=@SKU1,[SKU2]=@SKU2,[SKU3]=@SKU3,[SKU4]=@SKU4,[SKU5]=@SKU5,[SKU6]=@SKU6,[SKU7]=@SKU7,[SKU8]=@SKU8,SKU9=@SKU9,SKU10=@SKU10,SKU11=@SKU11,SKU12=@SKU12,SKU13=@SKU13,SKU14=@SKU14,SKU15=@SKU15,[Weight]=@Weight,[AutoPlan]=@AutoPlan,[NoGoods]=@NoGoods,[MinBuyNum]=@MinBuyNum,[Price]=@Price,[GoodsImageIds]=@GoodsImageIds,[FirstImgUrl]=@FirstImgUrl,[UpdateDate]=@UpdateDate,[UpdateName]=@UpdateName,[GoodsRemark]=@GoodsRemark,[SaleDays]=@SaleDays,[AvgTime]=@AvgTime,[SafeNum]=@SafeNum,[Solid]=@Solid,Barcode=@Barcode where DetailId=@DetailId
end
else
begin
INSERT INTO [HW_GoodsDetail]([GoodsId],[TypeCode],[TypeDesc],[SKU1],[SKU2],[SKU3],[SKU4],[SKU5],[SKU6],[SKU7],[SKU8],[Weight],[AutoPlan],[NoGoods],[MinBuyNum],[GoodsNum],[GoodsLockNum],[GoodsPlanNum],[GoodsOutNum],[GoodsInNum],[Price],[GoodsImageIds],[FirstImgUrl],[UpdateDate],[UpdateName],[GoodsRemark],[SaleDays],[AvgTime],[SafeNum],[Solid],SKU9,SKU10,SKU11,SKU12,SKU13,SKU14,SKU15,Barcode)values(@GoodsId,@TypeCode,@TypeDesc,@SKU1,@SKU2,@SKU3,@SKU4,@SKU5,@SKU6,@SKU7,@SKU8,@Weight,@AutoPlan,@NoGoods,@MinBuyNum,@GoodsNum,@GoodsLockNum,@GoodsPlanNum,@GoodsOutNum,@GoodsInNum,@Price,@GoodsImageIds,@FirstImgUrl,@UpdateDate,@UpdateName,@GoodsRemark,@SaleDays,@AvgTime,@SafeNum,@Solid,@SKU9,@SKU10,@SKU11,@SKU12,@SKU13,@SKU14,@SKU15,@Barcode)
set @DetailId=@@IDENTITY
end
select @DetailId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@DetailId", DbType.Int32, Model.DetailId);
db.AddInParameter(cmd, "@GoodsId", DbType.Int32, Model.GoodsId);
db.AddInParameter(cmd, "@TypeCode", DbType.String, Model.TypeCode);
db.AddInParameter(cmd, "@TypeDesc", DbType.String, Model.TypeDesc);
db.AddInParameter(cmd, "@SKU1", DbType.String, Model.SKU1);
db.AddInParameter(cmd, "@SKU2", DbType.String, Model.SKU2);
db.AddInParameter(cmd, "@SKU3", DbType.String, Model.SKU3);
db.AddInParameter(cmd, "@SKU4", DbType.String, Model.SKU4);
db.AddInParameter(cmd, "@SKU5", DbType.String, Model.SKU5);
db.AddInParameter(cmd, "@SKU6", DbType.String, Model.SKU6);
db.AddInParameter(cmd, "@SKU7", DbType.String, Model.SKU7);
db.AddInParameter(cmd, "@SKU8", DbType.String, Model.SKU8);
db.AddInParameter(cmd, "@SKU9", DbType.String, Model.SKU9);
db.AddInParameter(cmd, "@SKU10", DbType.String, Model.SKU10);
db.AddInParameter(cmd, "@SKU11", DbType.String, Model.SKU11);
db.AddInParameter(cmd, "@SKU12", DbType.String, Model.SKU12);
db.AddInParameter(cmd, "@SKU13", DbType.String, Model.SKU13);
db.AddInParameter(cmd, "@SKU14", DbType.String, Model.SKU14);
db.AddInParameter(cmd, "@SKU15", DbType.String, Model.SKU15);
db.AddInParameter(cmd, "@Weight", DbType.Decimal, Model.Weight);
db.AddInParameter(cmd, "@AutoPlan", DbType.Int32, Model.AutoPlan);
db.AddInParameter(cmd, "@NoGoods", DbType.Int32, Model.NoGoods);
db.AddInParameter(cmd, "@MinBuyNum", DbType.Int32, Model.MinBuyNum);
db.AddInParameter(cmd, "@GoodsNum", DbType.Int32, Model.GoodsNum);
db.AddInParameter(cmd, "@GoodsLockNum", DbType.Int32, Model.GoodsLockNum);
db.AddInParameter(cmd, "@GoodsPlanNum", DbType.Int32, Model.GoodsPlanNum);
db.AddInParameter(cmd, "@GoodsOutNum", DbType.Int32, Model.GoodsOutNum);
db.AddInParameter(cmd, "@GoodsInNum", DbType.Int32, Model.GoodsInNum);
db.AddInParameter(cmd, "@Price", DbType.Decimal, Model.Price);
db.AddInParameter(cmd, "@GoodsImageIds", DbType.String, Model.GoodsImageIds);
db.AddInParameter(cmd, "@FirstImgUrl", DbType.String, Model.FirstImgUrl);
db.AddInParameter(cmd, "@UpdateDate", DbType.DateTime, Model.UpdateDate);
db.AddInParameter(cmd, "@UpdateName", DbType.String, Model.UpdateName);
db.AddInParameter(cmd, "@GoodsRemark", DbType.String, Model.GoodsRemark);
db.AddInParameter(cmd, "@SaleDays", DbType.Decimal, Model.SaleDays);
db.AddInParameter(cmd, "@AvgTime", DbType.Decimal, Model.AvgTime);
db.AddInParameter(cmd, "@SafeNum", DbType.Int32, Model.SafeNum);
db.AddInParameter(cmd, "@Solid", DbType.Decimal, Model.Solid);
db.AddInParameter(cmd, "@Barcode", DbType.String, Model.Barcode);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 结束采购明细
public static void Delete_CFSKU(int detailid)
{
string tsql = @"
delete from HW_CFSKU where DetailId=@DetailId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@DetailId", DbType.Int32, detailid);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 保存
public static int Save_CFSKU(int detailid, string sku, string inname)
{
string tsql = @"
declare @DetailId2 int
set @DetailId2=0
select top 1 @DetailId2=DetailId from HW_GoodsDetail where sku1=@sku
if @DetailId2=0
begin
select top 1 @DetailId2=DetailId from HW_GoodsDetail where sku2=@sku or sku2=@sku or sku3=@sku or sku4=@sku or sku5=@sku or sku6=@sku or sku7=@sku or sku8=@sku or sku9=@sku or sku10=@sku or sku11=@sku or sku12=@sku or sku13=@sku
end
if @DetailId2>0
begin
INSERT INTO [HW_CFSKU]([DetailId],[DetailId2],[InDate],[InName])values(@DetailId,@DetailId2,getdate(),@InName)
end
select 1";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@DetailId", DbType.Int32, detailid);
db.AddInParameter(cmd, "@sku", DbType.String, sku);
db.AddInParameter(cmd, "@InName", DbType.String, inname);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存货物链接
/// <summary>
/// 保存货物链接
/// </summary>
public static int SaveBuyLink(HW_BuyLinkNew Model)
{
string tsql = @"
if @LinkId>0
begin
Update [HW_BuyLink] set [GoodsId]=@GoodsId,[LinkUrl]=@LinkUrl,[LinkName]=@LinkName,[LinkDesc]=@LinkDesc,Num=@Num,PM=@PM where LinkId=@LinkId
end
else
begin
INSERT INTO [HW_BuyLink]([GoodsId],[LinkUrl],[LinkName],[LinkDesc],Num,PM)values(@GoodsId,@LinkUrl,@LinkName,@LinkDesc,@Num,@PM)
set @LinkId=@@IDENTITY
end
select @LinkId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@LinkId", DbType.Int32, Model.LinkId);
db.AddInParameter(cmd, "@GoodsId", DbType.Int32, Model.GoodsId);
db.AddInParameter(cmd, "@LinkUrl", DbType.String, Model.LinkUrl);
db.AddInParameter(cmd, "@LinkName", DbType.String, Model.LinkName);
db.AddInParameter(cmd, "@LinkDesc", DbType.String, Model.LinkDesc);
db.AddInParameter(cmd, "@Num", DbType.Int32, Model.Num);
db.AddInParameter(cmd, "@PM", DbType.Int32, Model.PM);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存货物店铺
public static int SaveShopForTM(HW_Shop Model)
{
string tsql = @"
set @Id=0
select top 1 @Id=Id from HW_Shop where [GoodsId]=@GoodsId and [ShopId]=@ShopId
if @Id=0
begin
INSERT INTO [HW_Shop]([GoodsId],[ShopId],InName,InDate)values(@GoodsId,@ShopId,@InName,getdate())
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, "@GoodsId", DbType.Int32, Model.GoodsId);
db.AddInParameter(cmd, "@ShopId", DbType.Int32, Model.ShopId);
db.AddInParameter(cmd, "@InName", DbType.String, Model.InName);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 删除货物店铺
public static void DeleteShopForTM(string Ids, int GoodsId, string Name)
{
string tsql = "";
if (Ids != "")
{
tsql = @"delete from HW_Shop where GoodsId=@GoodsId and InName=@Name and Id not in (" + Ids + ")";
}
else
tsql = @"delete from HW_Shop where GoodsId=@GoodsId and InName=@Name ";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@GoodsId", DbType.Int32, GoodsId);
db.AddInParameter(cmd, "@Name", DbType.String, Name);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 分页查询
public static List<HW_GoodsDetailNew2> GetListHW_GoodsDetailNew22(int platid, int StoreId, RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
{
if (where == null)
{
where = new RefParameterCollection();
}
QueryService service = new QueryService
{
Fields = "DetailId=cast(a.DetailId as int),a.GoodsId,a.TypeCode,a.TypeDesc,a.SKU1,a.SKU2,a.SKU3,a.SKU4,a.SKU5,a.Weight,GoodsNum=isnull(a.GoodsNum,0),a.GoodsLockNum,a.GoodsPlanNum,a.GoodsOutNum,a.GoodsInNum,GoodsInNum3=isnull(a.GoodsInNum,0)-isnull(a.BHNum3,0),GoodsInNum2=isnull(a.GoodsInNum2,0),GoodsHJNum=isnull(a.GoodsHJNum,0),GoodsHJNum2=isnull(a.GoodsHJNum2,0),GoodsHJNum3=isnull(a.GoodsHJNum3,0),a.GoodsHJNum4,a.Price,a.GoodsImageIds,FirstImgUrl=case when a.FirstImgUrl is null or a.FirstImgUrl='' then b.FirstImgUrl else a.FirstImgUrl end,a.UpdateDate,a.UpdateName,a.GoodsRemark,a.SaleDays,a.AvgTime,a.SafeNum,b.Solid,a.BGPrice,a.SKU6,a.SKU7,a.SKU8,a.SKU9,a.SKU10,b.GoodsCode,b.GoodsName,b.SupplierId,LeftNum=isnull(a.GoodsNum,0)+isnull(a.GoodsInNum,0)+isnull(a.GoodsInNum2,0)-isnull(a.GoodsPlanNum,0),LeftNumW=isnull(a.WestNum,0)+isnull(a.GoodsHJNum14,0)-isnull(a.GoodsPlanNumW,0),LeftNumE=isnull(a.EastNum,0)+isnull(a.GoodsHJNum15,0)-isnull(a.GoodsPlanNumE,0),b.Cert,b.JYPrice,OutNum=isnull(a.GoodsBackNum,0),SJOutNum=isnull(a.GoodsBackNum2,0),MinBuyNum=isnull(a.GoodsBackNum3,0),AutoPlan=DATEDIFF(day,b.LastOrderDate,GETDATE()),NoGoods=isnull(b.IsSure,0),YCState=case when a.IsYC=1 then '显示SKU' else '隐藏SKU' end,CGNum=case when FHPlan=3 then isnull(CGNum,0) when FHPlan=1 then isnull(CGNum2,0) when FHPlan=2 then isnull(CGNum3,0) else isnull(CGNum,0) end,GoodsBackNum4=isnull(a.GoodsBackNum4,0),GoodsBackNum5=isnull(a.GoodsBackNum5,0),GoodsBackNum6=isnull(a.GoodsBackNum6,0),GoodsHJNum5=isnull(a.GoodsHJNum5,0),GoodsHJNum6=isnull(a.GoodsHJNum6,0),GoodsHJNum7=isnull(a.GoodsHJNum7,0),GoodsHJNum8=isnull(a.GoodsHJNum8,0),GoodsHJNum9=isnull(a.GoodsHJNum9,0),GoodsHJNum10=isnull(a.GoodsHJNum10,0),GoodsHJNum11=isnull(a.GoodsHJNum11,0),WestNum=isnull(a.WestNum,0),EastNum=isnull(a.EastNum,0),GoodsHJNum12=isnull(a.GoodsHJNum12,0),GoodsHJNum13=isnull(a.GoodsHJNum13,0),GoodsHJNum14=isnull(a.GoodsHJNum14,0),GoodsHJNum15=isnull(a.GoodsHJNum15,0),JHDays=isnull(b.JHDays,0),FHPlan=isnull(FHPlan,0),CGNum2=isnull(CGNum2,0),CGNum3=isnull(CGNum3,0),bhremark,a.BHNum1,a.BHNum2,a.BHNum3,a.JH_Date,IsSP=isnull(a.IsSP,0),SalesType=isnull(SalesType,-1),b.MName,d.groupname,b.IsExamine,GoodsHJNum_FBA=isnull(a.GoodsHJNum_FBA,0),b.SubsidyFee "
};
if (platid == 3)
{
service = new QueryService
{
Fields = "DetailId=cast(a.DetailId as int),a.GoodsId,a.TypeCode,a.TypeDesc,a.SKU1,a.SKU2,a.SKU3,a.SKU4,a.SKU5,a.Weight,GoodsNum=isnull(a.GoodsNum,0),a.GoodsLockNum,a.GoodsPlanNum,a.GoodsOutNum,a.GoodsInNum,GoodsInNum3=isnull(a.GoodsInNum,0)-isnull(a.BHNum3,0),GoodsInNum2=isnull(a.GoodsInNum2,0),GoodsHJNum=isnull(a.GoodsHJNum,0),GoodsHJNum2=isnull(a.GoodsHJNum2,0),GoodsHJNum3=isnull(a.GoodsHJNum3,0),a.GoodsHJNum4,a.Price,a.GoodsImageIds,FirstImgUrl=case when a.FirstImgUrl is null or a.FirstImgUrl='' then b.FirstImgUrl else a.FirstImgUrl end,a.UpdateDate,a.UpdateName,a.GoodsRemark,a.SaleDays,a.AvgTime,a.SafeNum,b.Solid,a.BGPrice,a.SKU6,a.SKU7,a.SKU8,a.SKU9,a.SKU10,b.GoodsCode,b.GoodsName,b.SupplierId,LeftNum=isnull(a.GoodsNum,0)+isnull(a.GoodsInNum,0)+isnull(a.GoodsInNum2,0)-isnull(a.GoodsPlanNum,0),LeftNumW=isnull(a.WestNum,0)+isnull(a.GoodsHJNum14,0)-isnull(a.GoodsPlanNumW,0),LeftNumE=isnull(a.EastNum,0)+isnull(a.GoodsHJNum15,0)-isnull(a.GoodsPlanNumE,0),b.Cert,b.JYPrice,OutNum=isnull(a.GoodsBackNum,0),SJOutNum=isnull(a.GoodsBackNum2,0),MinBuyNum=isnull(a.GoodsBackNum3,0),AutoPlan=DATEDIFF(day,b.LastOrderDate,GETDATE()),NoGoods=isnull(b.IsSure,0),YCState=case when a.IsYC=1 then '显示SKU' else '隐藏SKU' end,CGNum=case when FHPlan=3 then isnull(CGNum,0) when FHPlan=1 then isnull(CGNum2,0) when FHPlan=2 then isnull(CGNum3,0) else isnull(CGNum,0) end,GoodsBackNum4=isnull(a.GoodsBackNum4,0),GoodsBackNum5=isnull(a.GoodsBackNum5,0),GoodsBackNum6=isnull(a.GoodsBackNum6,0),GoodsHJNum5=isnull(a.GoodsHJNum5,0),GoodsHJNum6=isnull(a.GoodsHJNum6,0),GoodsHJNum7=isnull(a.GoodsHJNum7,0),GoodsHJNum8=isnull(a.GoodsHJNum8,0),GoodsHJNum9=isnull(a.GoodsHJNum9,0),GoodsHJNum10=isnull(a.GoodsHJNum10,0),GoodsHJNum11=isnull(a.GoodsHJNum11,0),WestNum=isnull(a.WestNum,0),EastNum=isnull(a.EastNum,0),GoodsHJNum12=isnull(a.GoodsHJNum12,0),GoodsHJNum13=isnull(a.GoodsHJNum13,0),GoodsHJNum14=isnull(a.GoodsHJNum14,0),GoodsHJNum15=isnull(a.GoodsHJNum15,0),JHDays=isnull(b.JHDays,0),FHPlan=isnull(FHPlan,0),CGNum2=isnull(CGNum2,0),CGNum3=isnull(CGNum3,0),bhremark,a.BHNum1,a.BHNum2,a.BHNum3,a.JH_Date,IsSP=isnull(a.IsSP,0),SalesType=isnull(SalesType,-1),MName=b.groupname_ebay,d.groupname,b.IsExamine,GoodsHJNum_FBA=isnull(a.GoodsHJNum_FBA,0),b.SubsidyFee"
};
}
else if (platid == 6)
{
service = new QueryService
{
Fields = "DetailId=cast(a.DetailId as int),a.GoodsId,a.TypeCode,a.TypeDesc,a.SKU1,a.SKU2,a.SKU3,a.SKU4,a.SKU5,a.Weight,GoodsNum=isnull(a.GoodsNum,0),a.GoodsLockNum,a.GoodsPlanNum,a.GoodsOutNum,a.GoodsInNum,GoodsInNum3=isnull(a.GoodsInNum,0)-isnull(a.BHNum3,0),GoodsInNum2=isnull(a.GoodsInNum2,0),GoodsHJNum=isnull(a.GoodsHJNum,0),GoodsHJNum2=isnull(a.GoodsHJNum2,0),GoodsHJNum3=isnull(a.GoodsHJNum3,0),a.GoodsHJNum4,a.Price,a.GoodsImageIds,FirstImgUrl=case when a.FirstImgUrl is null or a.FirstImgUrl='' then b.FirstImgUrl else a.FirstImgUrl end,a.UpdateDate,a.UpdateName,a.GoodsRemark,a.SaleDays,a.AvgTime,a.SafeNum,b.Solid,a.BGPrice,a.SKU6,a.SKU7,a.SKU8,a.SKU9,a.SKU10,b.GoodsCode,b.GoodsName,b.SupplierId,LeftNum=isnull(a.GoodsNum,0)+isnull(a.GoodsInNum,0)+isnull(a.GoodsInNum2,0)-isnull(a.GoodsPlanNum,0),LeftNumW=isnull(a.WestNum,0)+isnull(a.GoodsHJNum14,0)-isnull(a.GoodsPlanNumW,0),LeftNumE=isnull(a.EastNum,0)+isnull(a.GoodsHJNum15,0)-isnull(a.GoodsPlanNumE,0),b.Cert,b.JYPrice,OutNum=isnull(a.GoodsBackNum,0),SJOutNum=isnull(a.GoodsBackNum2,0),MinBuyNum=isnull(a.GoodsBackNum3,0),AutoPlan=DATEDIFF(day,b.LastOrderDate,GETDATE()),NoGoods=isnull(b.IsSure,0),YCState=case when a.IsYC=1 then '显示SKU' else '隐藏SKU' end,CGNum=case when FHPlan=3 then isnull(CGNum,0) when FHPlan=1 then isnull(CGNum2,0) when FHPlan=2 then isnull(CGNum3,0) else isnull(CGNum,0) end,GoodsBackNum4=isnull(a.GoodsBackNum4,0),GoodsBackNum5=isnull(a.GoodsBackNum5,0),GoodsBackNum6=isnull(a.GoodsBackNum6,0),GoodsHJNum5=isnull(a.GoodsHJNum5,0),GoodsHJNum6=isnull(a.GoodsHJNum6,0),GoodsHJNum7=isnull(a.GoodsHJNum7,0),GoodsHJNum8=isnull(a.GoodsHJNum8,0),GoodsHJNum9=isnull(a.GoodsHJNum9,0),GoodsHJNum10=isnull(a.GoodsHJNum10,0),GoodsHJNum11=isnull(a.GoodsHJNum11,0),WestNum=isnull(a.WestNum,0),EastNum=isnull(a.EastNum,0),GoodsHJNum12=isnull(a.GoodsHJNum12,0),GoodsHJNum13=isnull(a.GoodsHJNum13,0),GoodsHJNum14=isnull(a.GoodsHJNum14,0),GoodsHJNum15=isnull(a.GoodsHJNum15,0),JHDays=isnull(b.JHDays,0),FHPlan=isnull(FHPlan,0),CGNum2=isnull(CGNum2,0),CGNum3=isnull(CGNum3,0),bhremark,a.BHNum1,a.BHNum2,a.BHNum3,a.JH_Date,IsSP=isnull(a.IsSP,0),SalesType=isnull(SalesType,-1),MName=b.groupname_wlmart,d.groupname,b.IsExamine,GoodsHJNum_FBA=isnull(a.GoodsHJNum_FBA,0),b.SubsidyFee"
};
}
else if (platid == 15)
{
service = new QueryService
{
Fields = "DetailId=cast(a.DetailId as int),a.GoodsId,a.TypeCode,a.TypeDesc,a.SKU1,a.SKU2,a.SKU3,a.SKU4,a.SKU5,a.Weight,GoodsNum=isnull(a.GoodsNum,0),a.GoodsLockNum,a.GoodsPlanNum,a.GoodsOutNum,a.GoodsInNum,GoodsInNum3=isnull(a.GoodsInNum,0)-isnull(a.BHNum3,0),GoodsInNum2=isnull(a.GoodsInNum2,0),GoodsHJNum=isnull(a.GoodsHJNum,0),GoodsHJNum2=isnull(a.GoodsHJNum2,0),GoodsHJNum3=isnull(a.GoodsHJNum3,0),a.GoodsHJNum4,a.Price,a.GoodsImageIds,FirstImgUrl=case when a.FirstImgUrl is null or a.FirstImgUrl='' then b.FirstImgUrl else a.FirstImgUrl end,a.UpdateDate,a.UpdateName,a.GoodsRemark,a.SaleDays,a.AvgTime,a.SafeNum,b.Solid,a.BGPrice,a.SKU6,a.SKU7,a.SKU8,a.SKU9,a.SKU10,b.GoodsCode,b.GoodsName,b.SupplierId,LeftNum=isnull(a.GoodsNum,0)+isnull(a.GoodsInNum,0)+isnull(a.GoodsInNum2,0)-isnull(a.GoodsPlanNum,0),LeftNumW=isnull(a.WestNum,0)+isnull(a.GoodsHJNum14,0)-isnull(a.GoodsPlanNumW,0),LeftNumE=isnull(a.EastNum,0)+isnull(a.GoodsHJNum15,0)-isnull(a.GoodsPlanNumE,0),b.Cert,b.JYPrice,OutNum=isnull(a.GoodsBackNum,0),SJOutNum=isnull(a.GoodsBackNum2,0),MinBuyNum=isnull(a.GoodsBackNum3,0),AutoPlan=DATEDIFF(day,b.LastOrderDate,GETDATE()),NoGoods=isnull(b.IsSure,0),YCState=case when a.IsYC=1 then '显示SKU' else '隐藏SKU' end,CGNum=case when FHPlan=3 then isnull(CGNum,0) when FHPlan=1 then isnull(CGNum2,0) when FHPlan=2 then isnull(CGNum3,0) else isnull(CGNum,0) end,GoodsBackNum4=isnull(a.GoodsBackNum4,0),GoodsBackNum5=isnull(a.GoodsBackNum5,0),GoodsBackNum6=isnull(a.GoodsBackNum6,0),GoodsHJNum5=isnull(a.GoodsHJNum5,0),GoodsHJNum6=isnull(a.GoodsHJNum6,0),GoodsHJNum7=isnull(a.GoodsHJNum7,0),GoodsHJNum8=isnull(a.GoodsHJNum8,0),GoodsHJNum9=isnull(a.GoodsHJNum9,0),GoodsHJNum10=isnull(a.GoodsHJNum10,0),GoodsHJNum11=isnull(a.GoodsHJNum11,0),WestNum=isnull(a.WestNum,0),EastNum=isnull(a.EastNum,0),GoodsHJNum12=isnull(a.GoodsHJNum12,0),GoodsHJNum13=isnull(a.GoodsHJNum13,0),GoodsHJNum14=isnull(a.GoodsHJNum14,0),GoodsHJNum15=isnull(a.GoodsHJNum15,0),JHDays=isnull(b.JHDays,0),FHPlan=isnull(FHPlan,0),CGNum2=isnull(CGNum2,0),CGNum3=isnull(CGNum3,0),bhremark,a.BHNum1,a.BHNum2,a.BHNum3,a.JH_Date,IsSP=isnull(a.IsSP,0),SalesType=isnull(SalesType,-1),MName=b.groupname_shopify,d.groupname,b.IsExamine,GoodsHJNum_FBA=isnull(a.GoodsHJNum_FBA,0),b.SubsidyFee"
};
}
else if (platid == 18)
{
service = new QueryService
{
Fields = "DetailId=cast(a.DetailId as int),a.GoodsId,a.TypeCode,a.TypeDesc,a.SKU1,a.SKU2,a.SKU3,a.SKU4,a.SKU5,a.Weight,GoodsNum=isnull(a.GoodsNum,0),a.GoodsLockNum,a.GoodsPlanNum,a.GoodsOutNum,a.GoodsInNum,GoodsInNum3=isnull(a.GoodsInNum,0)-isnull(a.BHNum3,0),GoodsInNum2=isnull(a.GoodsInNum2,0),GoodsHJNum=isnull(a.GoodsHJNum,0),GoodsHJNum2=isnull(a.GoodsHJNum2,0),GoodsHJNum3=isnull(a.GoodsHJNum3,0),a.GoodsHJNum4,a.Price,a.GoodsImageIds,FirstImgUrl=case when a.FirstImgUrl is null or a.FirstImgUrl='' then b.FirstImgUrl else a.FirstImgUrl end,a.UpdateDate,a.UpdateName,a.GoodsRemark,a.SaleDays,a.AvgTime,a.SafeNum,b.Solid,a.BGPrice,a.SKU6,a.SKU7,a.SKU8,a.SKU9,a.SKU10,b.GoodsCode,b.GoodsName,b.SupplierId,LeftNum=isnull(a.GoodsNum,0)+isnull(a.GoodsInNum,0)+isnull(a.GoodsInNum2,0)-isnull(a.GoodsPlanNum,0),LeftNumW=isnull(a.WestNum,0)+isnull(a.GoodsHJNum14,0)-isnull(a.GoodsPlanNumW,0),LeftNumE=isnull(a.EastNum,0)+isnull(a.GoodsHJNum15,0)-isnull(a.GoodsPlanNumE,0),b.Cert,b.JYPrice,OutNum=isnull(a.GoodsBackNum,0),SJOutNum=isnull(a.GoodsBackNum2,0),MinBuyNum=isnull(a.GoodsBackNum3,0),AutoPlan=DATEDIFF(day,b.LastOrderDate,GETDATE()),NoGoods=isnull(b.IsSure,0),YCState=case when a.IsYC=1 then '显示SKU' else '隐藏SKU' end,CGNum=case when FHPlan=3 then isnull(CGNum,0) when FHPlan=1 then isnull(CGNum2,0) when FHPlan=2 then isnull(CGNum3,0) else isnull(CGNum,0) end,GoodsBackNum4=isnull(a.GoodsBackNum4,0),GoodsBackNum5=isnull(a.GoodsBackNum5,0),GoodsBackNum6=isnull(a.GoodsBackNum6,0),GoodsHJNum5=isnull(a.GoodsHJNum5,0),GoodsHJNum6=isnull(a.GoodsHJNum6,0),GoodsHJNum7=isnull(a.GoodsHJNum7,0),GoodsHJNum8=isnull(a.GoodsHJNum8,0),GoodsHJNum9=isnull(a.GoodsHJNum9,0),GoodsHJNum10=isnull(a.GoodsHJNum10,0),GoodsHJNum11=isnull(a.GoodsHJNum11,0),WestNum=isnull(a.WestNum,0),EastNum=isnull(a.EastNum,0),GoodsHJNum12=isnull(a.GoodsHJNum12,0),GoodsHJNum13=isnull(a.GoodsHJNum13,0),GoodsHJNum14=isnull(a.GoodsHJNum14,0),GoodsHJNum15=isnull(a.GoodsHJNum15,0),JHDays=isnull(b.JHDays,0),FHPlan=isnull(FHPlan,0),CGNum2=isnull(CGNum2,0),CGNum3=isnull(CGNum3,0),bhremark,a.BHNum1,a.BHNum2,a.BHNum3,a.JH_Date,IsSP=isnull(a.IsSP,0),SalesType=isnull(SalesType,-1),MName=b.groupname_wayfair,d.groupname,b.IsExamine,GoodsHJNum_FBA=isnull(a.GoodsHJNum_FBA,0),b.SubsidyFee"
};
}
else if (platid == 21)
{
service = new QueryService
{
Fields = "DetailId=cast(a.DetailId as int),a.GoodsId,a.TypeCode,a.TypeDesc,a.SKU1,a.SKU2,a.SKU3,a.SKU4,a.SKU5,a.Weight,GoodsNum=isnull(a.GoodsNum,0),a.GoodsLockNum,a.GoodsPlanNum,a.GoodsOutNum,a.GoodsInNum,GoodsInNum3=isnull(a.GoodsInNum,0)-isnull(a.BHNum3,0),GoodsInNum2=isnull(a.GoodsInNum2,0),GoodsHJNum=isnull(a.GoodsHJNum,0),GoodsHJNum2=isnull(a.GoodsHJNum2,0),GoodsHJNum3=isnull(a.GoodsHJNum3,0),a.GoodsHJNum4,a.Price,a.GoodsImageIds,FirstImgUrl=case when a.FirstImgUrl is null or a.FirstImgUrl='' then b.FirstImgUrl else a.FirstImgUrl end,a.UpdateDate,a.UpdateName,a.GoodsRemark,a.SaleDays,a.AvgTime,a.SafeNum,b.Solid,a.BGPrice,a.SKU6,a.SKU7,a.SKU8,a.SKU9,a.SKU10,b.GoodsCode,b.GoodsName,b.SupplierId,LeftNum=isnull(a.GoodsNum,0)+isnull(a.GoodsInNum,0)+isnull(a.GoodsInNum2,0)-isnull(a.GoodsPlanNum,0),LeftNumW=isnull(a.WestNum,0)+isnull(a.GoodsHJNum14,0)-isnull(a.GoodsPlanNumW,0),LeftNumE=isnull(a.EastNum,0)+isnull(a.GoodsHJNum15,0)-isnull(a.GoodsPlanNumE,0),b.Cert,b.JYPrice,OutNum=isnull(a.GoodsBackNum,0),SJOutNum=isnull(a.GoodsBackNum2,0),MinBuyNum=isnull(a.GoodsBackNum3,0),AutoPlan=DATEDIFF(day,b.LastOrderDate,GETDATE()),NoGoods=isnull(b.IsSure,0),YCState=case when a.IsYC=1 then '显示SKU' else '隐藏SKU' end,CGNum=case when FHPlan=3 then isnull(CGNum,0) when FHPlan=1 then isnull(CGNum2,0) when FHPlan=2 then isnull(CGNum3,0) else isnull(CGNum,0) end,GoodsBackNum4=isnull(a.GoodsBackNum4,0),GoodsBackNum5=isnull(a.GoodsBackNum5,0),GoodsBackNum6=isnull(a.GoodsBackNum6,0),GoodsHJNum5=isnull(a.GoodsHJNum5,0),GoodsHJNum6=isnull(a.GoodsHJNum6,0),GoodsHJNum7=isnull(a.GoodsHJNum7,0),GoodsHJNum8=isnull(a.GoodsHJNum8,0),GoodsHJNum9=isnull(a.GoodsHJNum9,0),GoodsHJNum10=isnull(a.GoodsHJNum10,0),GoodsHJNum11=isnull(a.GoodsHJNum11,0),WestNum=isnull(a.WestNum,0),EastNum=isnull(a.EastNum,0),GoodsHJNum12=isnull(a.GoodsHJNum12,0),GoodsHJNum13=isnull(a.GoodsHJNum13,0),GoodsHJNum14=isnull(a.GoodsHJNum14,0),GoodsHJNum15=isnull(a.GoodsHJNum15,0),JHDays=isnull(b.JHDays,0),FHPlan=isnull(FHPlan,0),CGNum2=isnull(CGNum2,0),CGNum3=isnull(CGNum3,0),bhremark,a.BHNum1,a.BHNum2,a.BHNum3,a.JH_Date,IsSP=isnull(a.IsSP,0),SalesType=isnull(SalesType,-1),MName=b.groupname_sheIn,d.groupname,b.IsExamine,GoodsHJNum_FBA=isnull(a.GoodsHJNum_FBA,0),b.SubsidyFee"
};
}
else if (platid == 22)
{
service = new QueryService
{
Fields = "DetailId=cast(a.DetailId as int),a.GoodsId,a.TypeCode,a.TypeDesc,a.SKU1,a.SKU2,a.SKU3,a.SKU4,a.SKU5,a.Weight,GoodsNum=isnull(a.GoodsNum,0),a.GoodsLockNum,a.GoodsPlanNum,a.GoodsOutNum,a.GoodsInNum,GoodsInNum3=isnull(a.GoodsInNum,0)-isnull(a.BHNum3,0),GoodsInNum2=isnull(a.GoodsInNum2,0),GoodsHJNum=isnull(a.GoodsHJNum,0),GoodsHJNum2=isnull(a.GoodsHJNum2,0),GoodsHJNum3=isnull(a.GoodsHJNum3,0),a.GoodsHJNum4,a.Price,a.GoodsImageIds,FirstImgUrl=case when a.FirstImgUrl is null or a.FirstImgUrl='' then b.FirstImgUrl else a.FirstImgUrl end,a.UpdateDate,a.UpdateName,a.GoodsRemark,a.SaleDays,a.AvgTime,a.SafeNum,b.Solid,a.BGPrice,a.SKU6,a.SKU7,a.SKU8,a.SKU9,a.SKU10,b.GoodsCode,b.GoodsName,b.SupplierId,LeftNum=isnull(a.GoodsNum,0)+isnull(a.GoodsInNum,0)+isnull(a.GoodsInNum2,0)-isnull(a.GoodsPlanNum,0),LeftNumW=isnull(a.WestNum,0)+isnull(a.GoodsHJNum14,0)-isnull(a.GoodsPlanNumW,0),LeftNumE=isnull(a.EastNum,0)+isnull(a.GoodsHJNum15,0)-isnull(a.GoodsPlanNumE,0),b.Cert,b.JYPrice,OutNum=isnull(a.GoodsBackNum,0),SJOutNum=isnull(a.GoodsBackNum2,0),MinBuyNum=isnull(a.GoodsBackNum3,0),AutoPlan=DATEDIFF(day,b.LastOrderDate,GETDATE()),NoGoods=isnull(b.IsSure,0),YCState=case when a.IsYC=1 then '显示SKU' else '隐藏SKU' end,CGNum=case when FHPlan=3 then isnull(CGNum,0) when FHPlan=1 then isnull(CGNum2,0) when FHPlan=2 then isnull(CGNum3,0) else isnull(CGNum,0) end,GoodsBackNum4=isnull(a.GoodsBackNum4,0),GoodsBackNum5=isnull(a.GoodsBackNum5,0),GoodsBackNum6=isnull(a.GoodsBackNum6,0),GoodsHJNum5=isnull(a.GoodsHJNum5,0),GoodsHJNum6=isnull(a.GoodsHJNum6,0),GoodsHJNum7=isnull(a.GoodsHJNum7,0),GoodsHJNum8=isnull(a.GoodsHJNum8,0),GoodsHJNum9=isnull(a.GoodsHJNum9,0),GoodsHJNum10=isnull(a.GoodsHJNum10,0),GoodsHJNum11=isnull(a.GoodsHJNum11,0),WestNum=isnull(a.WestNum,0),EastNum=isnull(a.EastNum,0),GoodsHJNum12=isnull(a.GoodsHJNum12,0),GoodsHJNum13=isnull(a.GoodsHJNum13,0),GoodsHJNum14=isnull(a.GoodsHJNum14,0),GoodsHJNum15=isnull(a.GoodsHJNum15,0),JHDays=isnull(b.JHDays,0),FHPlan=isnull(FHPlan,0),CGNum2=isnull(CGNum2,0),CGNum3=isnull(CGNum3,0),bhremark,a.BHNum1,a.BHNum2,a.BHNum3,a.JH_Date,IsSP=isnull(a.IsSP,0),SalesType=isnull(SalesType,-1),MName=b.groupname_tiktok,d.groupname,b.IsExamine,GoodsHJNum_FBA=isnull(a.GoodsHJNum_FBA,0),b.SubsidyFee"
};
}
else if (platid == 23)
{
service = new QueryService
{
Fields = "DetailId=cast(a.DetailId as int),a.GoodsId,a.TypeCode,a.TypeDesc,a.SKU1,a.SKU2,a.SKU3,a.SKU4,a.SKU5,a.Weight,GoodsNum=isnull(a.GoodsNum,0),a.GoodsLockNum,a.GoodsPlanNum,a.GoodsOutNum,a.GoodsInNum,GoodsInNum3=isnull(a.GoodsInNum,0)-isnull(a.BHNum3,0),GoodsInNum2=isnull(a.GoodsInNum2,0),GoodsHJNum=isnull(a.GoodsHJNum,0),GoodsHJNum2=isnull(a.GoodsHJNum2,0),GoodsHJNum3=isnull(a.GoodsHJNum3,0),a.GoodsHJNum4,a.Price,a.GoodsImageIds,FirstImgUrl=case when a.FirstImgUrl is null or a.FirstImgUrl='' then b.FirstImgUrl else a.FirstImgUrl end,a.UpdateDate,a.UpdateName,a.GoodsRemark,a.SaleDays,a.AvgTime,a.SafeNum,b.Solid,a.BGPrice,a.SKU6,a.SKU7,a.SKU8,a.SKU9,a.SKU10,b.GoodsCode,b.GoodsName,b.SupplierId,LeftNum=isnull(a.GoodsNum,0)+isnull(a.GoodsInNum,0)+isnull(a.GoodsInNum2,0)-isnull(a.GoodsPlanNum,0),LeftNumW=isnull(a.WestNum,0)+isnull(a.GoodsHJNum14,0)-isnull(a.GoodsPlanNumW,0),LeftNumE=isnull(a.EastNum,0)+isnull(a.GoodsHJNum15,0)-isnull(a.GoodsPlanNumE,0),b.Cert,b.JYPrice,OutNum=isnull(a.GoodsBackNum,0),SJOutNum=isnull(a.GoodsBackNum2,0),MinBuyNum=isnull(a.GoodsBackNum3,0),AutoPlan=DATEDIFF(day,b.LastOrderDate,GETDATE()),NoGoods=isnull(b.IsSure,0),YCState=case when a.IsYC=1 then '显示SKU' else '隐藏SKU' end,CGNum=case when FHPlan=3 then isnull(CGNum,0) when FHPlan=1 then isnull(CGNum2,0) when FHPlan=2 then isnull(CGNum3,0) else isnull(CGNum,0) end,GoodsBackNum4=isnull(a.GoodsBackNum4,0),GoodsBackNum5=isnull(a.GoodsBackNum5,0),GoodsBackNum6=isnull(a.GoodsBackNum6,0),GoodsHJNum5=isnull(a.GoodsHJNum5,0),GoodsHJNum6=isnull(a.GoodsHJNum6,0),GoodsHJNum7=isnull(a.GoodsHJNum7,0),GoodsHJNum8=isnull(a.GoodsHJNum8,0),GoodsHJNum9=isnull(a.GoodsHJNum9,0),GoodsHJNum10=isnull(a.GoodsHJNum10,0),GoodsHJNum11=isnull(a.GoodsHJNum11,0),WestNum=isnull(a.WestNum,0),EastNum=isnull(a.EastNum,0),GoodsHJNum12=isnull(a.GoodsHJNum12,0),GoodsHJNum13=isnull(a.GoodsHJNum13,0),GoodsHJNum14=isnull(a.GoodsHJNum14,0),GoodsHJNum15=isnull(a.GoodsHJNum15,0),JHDays=isnull(b.JHDays,0),FHPlan=isnull(FHPlan,0),CGNum2=isnull(CGNum2,0),CGNum3=isnull(CGNum3,0),bhremark,a.BHNum1,a.BHNum2,a.BHNum3,a.JH_Date,IsSP=isnull(a.IsSP,0),SalesType=isnull(SalesType,-1),MName=b.groupname_temu,d.groupname,b.IsExamine,GoodsHJNum_FBA=isnull(a.GoodsHJNum_FBA,0),b.SubsidyFee"
};
}
if (StoreId > 0)
{
service.Tables = "HW_GoodsDetail a with(nolock) inner join HW_GoodsInfo b with(nolock) on a.GoodsId=b.GoodsId inner join (select distinct DetailId from HW_GoodsInDetail where StoreId=" + StoreId + ")c on a.DetailId=c.DetailId left join JC_UserInfo d with(nolock) on b.muserid=d.UserId";
if (platid == 3)
{
service.Tables = "HW_GoodsDetail a with(nolock) inner join HW_GoodsInfo b with(nolock) on a.GoodsId=b.GoodsId inner join (select distinct DetailId from HW_GoodsInDetail where StoreId=" + StoreId + ")c on a.DetailId=c.DetailId left join JC_UserInfo d with(nolock) on b.guserid_ebay=d.UserId";
}
else if (platid == 6)
{
service.Tables = "HW_GoodsDetail a with(nolock) inner join HW_GoodsInfo b with(nolock) on a.GoodsId=b.GoodsId inner join (select distinct DetailId from HW_GoodsInDetail where StoreId=" + StoreId + ")c on a.DetailId=c.DetailId left join JC_UserInfo d with(nolock) on b.guserid_wlmart=d.UserId";
}
else if (platid == 15)
{
service.Tables = "HW_GoodsDetail a with(nolock) inner join HW_GoodsInfo b with(nolock) on a.GoodsId=b.GoodsId inner join (select distinct DetailId from HW_GoodsInDetail where StoreId=" + StoreId + ")c on a.DetailId=c.DetailId left join JC_UserInfo d with(nolock) on b.guserid_shopify=d.UserId";
}
else if (platid == 18)
{
service.Tables = "HW_GoodsDetail a with(nolock) inner join HW_GoodsInfo b with(nolock) on a.GoodsId=b.GoodsId inner join (select distinct DetailId from HW_GoodsInDetail where StoreId=" + StoreId + ")c on a.DetailId=c.DetailId left join JC_UserInfo d with(nolock) on b.guserid_wayfair=d.UserId";
}
else if (platid == 21)
{
service.Tables = "HW_GoodsDetail a with(nolock) inner join HW_GoodsInfo b with(nolock) on a.GoodsId=b.GoodsId inner join (select distinct DetailId from HW_GoodsInDetail where StoreId=" + StoreId + ")c on a.DetailId=c.DetailId left join JC_UserInfo d with(nolock) on b.guserid_sheIn=d.UserId";
}
else if (platid == 22)
{
service.Tables = "HW_GoodsDetail a with(nolock) inner join HW_GoodsInfo b with(nolock) on a.GoodsId=b.GoodsId inner join (select distinct DetailId from HW_GoodsInDetail where StoreId=" + StoreId + ")c on a.DetailId=c.DetailId left join JC_UserInfo d with(nolock) on b.guserid_tiktok=d.UserId";
}
else if (platid == 23)
{
service.Tables = "HW_GoodsDetail a with(nolock) inner join HW_GoodsInfo b with(nolock) on a.GoodsId=b.GoodsId inner join (select distinct DetailId from HW_GoodsInDetail where StoreId=" + StoreId + ")c on a.DetailId=c.DetailId left join JC_UserInfo d with(nolock) on b.guserid_temu=d.UserId";
}
}
else
{
service.Tables = "HW_GoodsDetail a with(nolock) inner join HW_GoodsInfo b with(nolock) on a.GoodsId=b.GoodsId left join JC_UserInfo d with(nolock) on b.muserid=d.UserId";
if (platid == 3)
{
service.Tables = "HW_GoodsDetail a with(nolock) inner join HW_GoodsInfo b with(nolock) on a.GoodsId=b.GoodsId left join JC_UserInfo d with(nolock) on b.guserid_ebay=d.UserId";
}
else if (platid == 6)
{
service.Tables = "HW_GoodsDetail a with(nolock) inner join HW_GoodsInfo b with(nolock) on a.GoodsId=b.GoodsId left join JC_UserInfo d with(nolock) on b.guserid_wlmart=d.UserId";
}
else if (platid == 15)
{
service.Tables = "HW_GoodsDetail a with(nolock) inner join HW_GoodsInfo b with(nolock) on a.GoodsId=b.GoodsId left join JC_UserInfo d with(nolock) on b.guserid_shopify=d.UserId";
}
else if (platid == 18)
{
service.Tables = "HW_GoodsDetail a with(nolock) inner join HW_GoodsInfo b with(nolock) on a.GoodsId=b.GoodsId left join JC_UserInfo d with(nolock) on b.guserid_wayfair=d.UserId";
}
else if (platid == 21)
{
service.Tables = "HW_GoodsDetail a with(nolock) inner join HW_GoodsInfo b with(nolock) on a.GoodsId=b.GoodsId left join JC_UserInfo d with(nolock) on b.guserid_sheIn=d.UserId";
}
else if (platid == 21)
{
service.Tables = "HW_GoodsDetail a with(nolock) inner join HW_GoodsInfo b with(nolock) on a.GoodsId=b.GoodsId left join JC_UserInfo d with(nolock) on b.guserid_tiktok=d.UserId";
}
else if (platid == 21)
{
service.Tables = "HW_GoodsDetail a with(nolock) inner join HW_GoodsInfo b with(nolock) on a.GoodsId=b.GoodsId left join JC_UserInfo d with(nolock) on b.guserid_temu=d.UserId";
}
}
service.Filter = where.GetWhere(CommandType.Text);
service.PageIndex = PageIndex;
service.PageSize = PageSize;
service.Sort = Sort;
service.KeyName = "DetailId";
string text = service.GetText();
where.AddOutParameter("RowCount", DbType.Int32);
Database database = DatabaseFactory.CreateDatabase();
DbCommand sqlStringCommand = database.GetSqlStringCommand(text);
database.AddInParameter(sqlStringCommand, where);
DataTable tb = database.ExecuteDataTable(sqlStringCommand);
RowCount = Convert.ToInt32(sqlStringCommand.Parameters["@RowCount"].Value);
return tb.ToList<HW_GoodsDetailNew2>();
}
#endregion
#region 获取东西部仓库货物当天的销售数量
public static List<StoreGoodsSaleNum> GetStoreGoodsSaleNumsNowDay()
{
var sql = @"select t.StoreId,t.DetailId,SUM(ISNULL(t.GoodsNum,0)) SaleNum from (
select case when c.StoreId=9 then 6 when c.StoreId=6 then 6 when c.StoreId=11 then 11 end StoreId, b.DetailId,b.GoodsNum from DT_OrderInfo a
inner join DT_OrderXXInfo c on a.OrderId=c.OrderId
inner join DT_OrderGoods b on a.OrderId=b.OrderId
where a.State>0 and c.StoreId in (6,9,11) and a.State<3 and DATEDIFF(day,a.OrderDate,getdate())=0
) t group by t.StoreId,t.DetailId
";
var db = DatabaseFactory.CreateDatabase();
var cmd = db.GetSqlStringCommand(sql);
return db.ExecuteDataTable(cmd).ToList<StoreGoodsSaleNum>();
}
#endregion
#region 返回列表
public static List<HW_GoodsStar> GetListGoodsStar(string GoodsIds)
{
//string tsql = "select a.Id,a.GoodsId,a.ShopId,a.Url,a.Star1,a.Star2,a.Star3,a.Star4,a.Star5,a.Star6,a.LastDate,a.LastDate2 from HW_GoodsStar";
string tsql = @"select a.Id,a.GoodsId,a.ShopId,a.Url,Star1=isnull(a.Star1,0),Star2=isnull(a.Star2,0),Star3=isnull(a.Star3,0),Star4=isnull(a.Star4,0),Star5=isnull(a.Star5,0),Star6=isnull(a.Star6,0),a.LastDate,a.LastDate2,b.ShopName from HW_GoodsStar a
inner join JC_Shop b on a.ShopId=b.ShopId
where a.GoodsId in (" + GoodsIds + ") order by isnull(a.Star6,0) desc";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
DataTable tb = db.ExecuteDataTable(cmd);
List<HW_GoodsStar> ListModel = tb.ToList<HW_GoodsStar>();
return ListModel;
}
#endregion
#region
public static void UpdateGoodsDays()
{
string tsql = @"
update a set a.LastOrderDate=b.OrderDate from HW_GoodsInfo a
inner join (
select d.GoodsId,OrderDate=MAX(a.OrderDate) from DT_OrderInfo a
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
where DATEDIFF(day, a.OrderDate,getdate())<2
group by d.GoodsId)b on a.GoodsId=b.GoodsId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 读取首页订单数
public static DT_DefaultDataNew GetDefaultDataForTM2(int userid,int CompanyID, DateTime? InDate, DateTime? PreDate, DateTime? SDate, DateTime? EDate)
{
DT_DefaultDataNew model = null;
string tsql = @"
declare @Num int,@TotalPrice Decimal(18,2),@SiglePrice Decimal(18,2),@TotalPriceUSD Decimal(18,2),@TotalPriceUSD2 Decimal(18,2),@SiglePriceUSD Decimal(18,2),@BuyMoney Decimal(18,2),@Num2 int,@Num3 int,@Num4 money,@Num5 money,@Num6 money,@Num7 money,@Num9 int,@Num10 money,@Num11 int,@Num12 int,@Num13 int,@Num14 int,@Num15 int,@Num16 int,@Num17 int,@BackPrice Decimal(18,2),@MRate Decimal(18,4),@Num18 int,@Num19 Decimal(18,1),@Num20 Decimal(18,1),@Num21 Decimal(18,1)
--总订单量
select @Num=COUNT( distinct a.OrderId ) from DT_OrderInfo a with(nolock) inner join DT_OrderGoods b with(nolock) on a.OrderId=b.OrderId inner join HW_GoodsDetail c with(nolock) on b.DetailId=c.DetailId inner join HW_GoodsInfo d with(nolock) on c.GoodsId=d.GoodsId where a.CompanyId=@CompanyID and a.state>0 and OrderDate>=@SDate and OrderDate<@EDate and ( d.muserid=@userid or d.guserid_ebay=@userid or d.guserid_wlmart=@userid or d.guserid_wayfair=@userid or d.guserid_shopify=@userid or d.guserid_sheIn=@userid or d.guserid_tiktok=@userid or d.guserid_temu=@userid)
--西部订单量
select @Num12=COUNT(distinct a.OrderId ) from DT_OrderInfo a with(nolock)
inner join DT_OrderXXInfo b with(nolock) on a.OrderId=b.OrderId
inner join DT_OrderGoods e with(nolock) on a.OrderId=e.OrderId inner join HW_GoodsDetail c with(nolock) on e.DetailId=c.DetailId inner join HW_GoodsInfo d with(nolock) on c.GoodsId=d.GoodsId
where a.CompanyId=@CompanyID and a.state>0 and a.state<3 and OrderDate>=@SDate and OrderDate<@EDate and b.StoreId=6 and ( d.muserid=@userid or d.guserid_ebay=@userid or d.guserid_wlmart=@userid or d.guserid_wayfair=@userid or d.guserid_shopify=@userid or d.guserid_sheIn=@userid or d.guserid_tiktok=@userid or d.guserid_temu=@userid)
--东部订单量
select @Num13=COUNT(distinct a.OrderId ) from DT_OrderInfo a with(nolock)
inner join DT_OrderXXInfo b with(nolock) on a.OrderId=b.OrderId
inner join DT_OrderGoods e with(nolock) on a.OrderId=e.OrderId inner join HW_GoodsDetail c with(nolock) on e.DetailId=c.DetailId inner join HW_GoodsInfo d with(nolock) on c.GoodsId=d.GoodsId
where a.CompanyId=@CompanyID and a.state>0 and a.state<3 and OrderDate>=@SDate and OrderDate<@EDate and b.StoreId=11 and ( d.muserid=@userid or d.guserid_ebay=@userid or d.guserid_wlmart=@userid or d.guserid_wayfair=@userid or d.guserid_shopify=@userid or d.guserid_sheIn=@userid or d.guserid_tiktok=@userid or d.guserid_temu=@userid)
select @Num18=COUNT(distinct a.OrderId ) from DT_OrderInfo a with(nolock)
inner join DT_OrderXXInfo b with(nolock) on a.OrderId=b.OrderId
inner join DT_OrderGoods e with(nolock) on a.OrderId=e.OrderId inner join HW_GoodsDetail c with(nolock) on e.DetailId=c.DetailId inner join HW_GoodsInfo d with(nolock) on c.GoodsId=d.GoodsId
where a.CompanyId=@CompanyID and a.state>0 and a.state<3 and OrderDate>=@SDate and OrderDate<@EDate and b.StoreId=5 and ( d.muserid=@userid or d.guserid_ebay=@userid or d.guserid_wlmart=@userid or d.guserid_wayfair=@userid or d.guserid_shopify=@userid or d.guserid_sheIn=@userid or d.guserid_tiktok=@userid or d.guserid_temu=@userid)
--昨天订单金额
select @TotalPrice=SUM(RMBPrice),@TotalPriceUSD=SUM(dbo.MoneyUSDChange(TotalPrice,MoneyCode,1)) from DT_OrderInfo with(nolock) where orderid in (
select distinct a.OrderId from DT_OrderInfo a with(nolock) inner join DT_OrderGoods b with(nolock) on a.OrderId=b.OrderId
inner join HW_GoodsDetail c with(nolock) on b.DetailId=c.DetailId
inner join HW_GoodsInfo d with(nolock) on c.GoodsId=d.GoodsId
where a.CompanyId=@CompanyID and a.state>0 and a.state<3 and OrderDate>=@SDate and OrderDate<@EDate and ( d.muserid=@userid or d.guserid_ebay=@userid or d.guserid_wlmart=@userid or d.guserid_wayfair=@userid or d.guserid_shopify=@userid or d.guserid_sheIn=@userid or d.guserid_tiktok=@userid or d.guserid_temu=@userid))
--select @TotalPrice=SUM(RMBPrice),@TotalPriceUSD=SUM(dbo.MoneyUSDChange(TotalPrice,MoneyCode,1)) from DT_OrderInfo with(nolock) where CompanyId=@CompanyID and state>0 and state<3 and OrderDate>='2024-07-15' and OrderDate<'2024-07-16'
--今天订单金额
select @TotalPriceUSD2=SUM(dbo.MoneyUSDChange(TotalPrice,MoneyCode,1)) from DT_OrderInfo with(nolock) where orderid in (
select distinct a.OrderId from DT_OrderInfo a with(nolock) inner join DT_OrderGoods b with(nolock) on a.OrderId=b.OrderId
inner join HW_GoodsDetail c with(nolock) on b.DetailId=c.DetailId
inner join HW_GoodsInfo d with(nolock) on c.GoodsId=d.GoodsId
where a.CompanyId=@CompanyID and a.state>0 and a.state<3 and datediff(day,OrderDate,getdate())=0 and ( d.muserid=@userid or d.guserid_ebay=@userid or d.guserid_wlmart=@userid or d.guserid_wayfair=@userid or d.guserid_shopify=@userid or d.guserid_sheIn=@userid or d.guserid_tiktok=@userid or d.guserid_temu=@userid))
--select @TotalPriceUSD2=SUM(dbo.MoneyUSDChange(TotalPrice,MoneyCode,1)) from DT_OrderInfo with(nolock) where CompanyId=@CompanyID and state>0 and state<3 and datediff(day,OrderDate,getdate())=0
select @SiglePrice=@TotalPrice/@Num
select @SiglePriceUSD=@TotalPriceUSD/@Num
--set @MRate=6.7
--select top 1 @MRate=MRate from JC_Money where MCode='USD'
--set @TotalPriceUSD=@TotalPrice/@MRate
--set @SiglePriceUSD=@SiglePrice/@MRate
select @Num2=COUNT(distinct a.OrderId) from DT_OrderInfo a with(nolock)
inner join DT_OrderGoods b with(nolock) on a.OrderId=b.OrderId
inner join HW_GoodsDetail c with(nolock) on b.DetailId=c.DetailId
inner join HW_GoodsInfo d with(nolock) on c.GoodsId=d.GoodsId
where a.CompanyId=@CompanyID and a.state>0 and a.state<3 and datediff(day,a.InDate,@InDate)=0 and ( d.muserid=@userid or d.guserid_ebay=@userid or d.guserid_wlmart=@userid or d.guserid_wayfair=@userid or d.guserid_shopify=@userid or d.guserid_sheIn=@userid or d.guserid_tiktok=@userid or d.guserid_temu=@userid)
select @Num14=COUNT(distinct a.OrderId) from DT_OrderInfo a with(nolock)
inner join DT_OrderXXInfo b with(nolock) on a.OrderId=b.OrderId
inner join DT_OrderGoods e with(nolock) on a.OrderId=e.OrderId inner join HW_GoodsDetail c with(nolock) on e.DetailId=c.DetailId inner join HW_GoodsInfo d with(nolock) on c.GoodsId=d.GoodsId
where a.CompanyId=@CompanyID and a.state>0 and a.state<3 and datediff(day,a.InDate,@InDate)=0 and b.StoreId=6 and ( d.muserid=@userid or d.guserid_ebay=@userid or d.guserid_wlmart=@userid or d.guserid_wayfair=@userid or d.guserid_shopify=@userid or d.guserid_sheIn=@userid or d.guserid_tiktok=@userid or d.guserid_temu=@userid)
select @Num15=COUNT(distinct a.OrderId) from DT_OrderInfo a with(nolock)
inner join DT_OrderXXInfo b with(nolock) on a.OrderId=b.OrderId
inner join DT_OrderGoods e with(nolock) on a.OrderId=e.OrderId inner join HW_GoodsDetail c with(nolock) on e.DetailId=c.DetailId inner join HW_GoodsInfo d with(nolock) on c.GoodsId=d.GoodsId
where a.CompanyId=@CompanyID and a.state>0 and a.state<3 and datediff(day,a.InDate,@InDate)=0 and b.StoreId=11 and ( d.muserid=@userid or d.guserid_ebay=@userid or d.guserid_wlmart=@userid or d.guserid_wayfair=@userid or d.guserid_shopify=@userid or d.guserid_sheIn=@userid or d.guserid_tiktok=@userid or d.guserid_temu=@userid)
--发货订单量
select @Num3=COUNT(distinct a.OrderId) from DT_OrderInfo a with(nolock)
inner join DT_OrderGoods b with(nolock) on a.OrderId=b.OrderId
inner join HW_GoodsDetail c with(nolock) on b.DetailId=c.DetailId
inner join HW_GoodsInfo d with(nolock) on c.GoodsId=d.GoodsId
where a.CompanyId=@CompanyID and a.state=2 and datediff(day,PostDate,@PreDate)=0 and ( d.muserid=@userid or d.guserid_ebay=@userid or d.guserid_wlmart=@userid or d.guserid_wayfair=@userid or d.guserid_shopify=@userid or d.guserid_sheIn=@userid or d.guserid_tiktok=@userid or d.guserid_temu=@userid)
select @Num19=SUM(b.GoodsNum*ISNULL(d.Solid,0)) from DT_OrderInfo a with(nolock)
inner join DT_OrderGoods b with(nolock) on a.OrderId=b.OrderId
inner join HW_GoodsDetail c with(nolock) on b.DetailId=c.DetailId
inner join HW_GoodsInfo d with(nolock) on c.GoodsId=d.GoodsId
where a.CompanyId=@CompanyID and a.state=2 and datediff(day,PostDate,@PreDate)=0 and ( d.muserid=@userid or d.guserid_ebay=@userid or d.guserid_wlmart=@userid or d.guserid_wayfair=@userid or d.guserid_shopify=@userid or d.guserid_sheIn=@userid or d.guserid_tiktok=@userid or d.guserid_temu=@userid)
select @Num16=COUNT(distinct a.OrderId) from DT_OrderInfo a with(nolock)
inner join DT_OrderXXInfo b with(nolock) on a.OrderId=b.OrderId
inner join DT_OrderGoods e with(nolock) on a.OrderId=e.OrderId inner join HW_GoodsDetail c with(nolock) on e.DetailId=c.DetailId inner join HW_GoodsInfo d with(nolock) on c.GoodsId=d.GoodsId
where a.CompanyId=@CompanyID and a.state=2 and datediff(day,a.PostDate,@PreDate)=0 and b.StoreId=6 and ( d.muserid=@userid or d.guserid_ebay=@userid or d.guserid_wlmart=@userid or d.guserid_wayfair=@userid or d.guserid_shopify=@userid or d.guserid_sheIn=@userid or d.guserid_tiktok=@userid or d.guserid_temu=@userid)
select @Num20=SUM(b.GoodsNum*ISNULL(d.Solid,0)) from DT_OrderInfo a with(nolock)
inner join DT_OrderGoods b with(nolock) on a.OrderId=b.OrderId
inner join DT_OrderXXInfo e with(nolock) on a.OrderId=e.OrderId
inner join HW_GoodsDetail c with(nolock) on b.DetailId=c.DetailId
inner join HW_GoodsInfo d with(nolock) on c.GoodsId=d.GoodsId
where a.CompanyId=@CompanyID and a.state=2 and datediff(day,PostDate,@PreDate)=0 and e.StoreId=6 and ( d.muserid=@userid or d.guserid_ebay=@userid or d.guserid_wlmart=@userid or d.guserid_wayfair=@userid or d.guserid_shopify=@userid or d.guserid_sheIn=@userid or d.guserid_tiktok=@userid or d.guserid_temu=@userid)
select @Num17=COUNT(distinct a.OrderId) from DT_OrderInfo a with(nolock)
inner join DT_OrderXXInfo b with(nolock) on a.OrderId=b.OrderId
inner join DT_OrderGoods e with(nolock) on a.OrderId=e.OrderId inner join HW_GoodsDetail c with(nolock) on e.DetailId=c.DetailId inner join HW_GoodsInfo d with(nolock) on c.GoodsId=d.GoodsId
where a.CompanyId=@CompanyID and a.state=2 and datediff(day,a.PostDate,@PreDate)=0 and b.StoreId=11 and ( d.muserid=@userid or d.guserid_ebay=@userid or d.guserid_wlmart=@userid or d.guserid_wayfair=@userid or d.guserid_shopify=@userid or d.guserid_sheIn=@userid or d.guserid_tiktok=@userid or d.guserid_temu=@userid)
select @Num21=SUM(b.GoodsNum*ISNULL(d.Solid,0)) from DT_OrderInfo a with(nolock)
inner join DT_OrderGoods b with(nolock) on a.OrderId=b.OrderId
inner join DT_OrderXXInfo e with(nolock) on a.OrderId=e.OrderId
inner join HW_GoodsDetail c with(nolock) on b.DetailId=c.DetailId
inner join HW_GoodsInfo d with(nolock) on c.GoodsId=d.GoodsId
where a.CompanyId=@CompanyID and a.state=2 and datediff(day,PostDate,@PreDate)=0 and e.StoreId=11 and ( d.muserid=@userid or d.guserid_ebay=@userid or d.guserid_wlmart=@userid or d.guserid_wayfair=@userid or d.guserid_shopify=@userid or d.guserid_sheIn=@userid or d.guserid_tiktok=@userid or d.guserid_temu=@userid)
select @Num10=sum((b.GoodsNum-b.InGoodsNum)*b.GoodsPrice) from CG_Purchase a with(nolock)
inner join CG_PurchaseGoods b with(nolock) on a.ChaseId=b.ChaseId
inner join HW_GoodsDetail c with(nolock) on b.GoodsDetailId=c.DetailId
inner join HW_GoodsInfo d with(nolock) on d.GoodsId=c.GoodsId
where a.IsDelete=0 and a.GoodsState<2 and a.State<3 and b.InGoodsNum<b.GoodsNum and ( d.muserid=@userid or d.guserid_ebay=@userid or d.guserid_wlmart=@userid or d.guserid_wayfair=@userid or d.guserid_shopify=@userid or d.guserid_sheIn=@userid or d.guserid_tiktok=@userid or d.guserid_temu=@userid)
select @Num4=SUM(c.Price*c.KCNum) from DT_OrderInfo a with(nolock)
inner join DT_OrderGoods b with(nolock) on a.OrderId=b.OrderId
inner join HW_GoodsInDetail c with(nolock) on b.Id=c.OrderGoodsId
inner join HW_GoodsDetail e with(nolock) on b.DetailId=e.DetailId
inner join HW_GoodsInfo d with(nolock) on d.GoodsId=e.GoodsId
where a.CompanyId=@CompanyID and a.state=2 and datediff(day,a.PostDate,@PreDate)=0 and ( d.muserid=@userid or d.guserid_ebay=@userid or d.guserid_wlmart=@userid or d.guserid_wayfair=@userid or d.guserid_shopify=@userid or d.guserid_sheIn=@userid or d.guserid_tiktok=@userid or d.guserid_temu=@userid)
select @Num5=SUM(GoodsMoney)+sum(PostFee) from CG_Purchase with(nolock) where IsDelete=0 and CompanyId=@CompanyID and datediff(day,InDate,@PreDate)=0
select @Num6=SUM(Price*KCNum) from HW_GoodsInDetail with(nolock) where datediff(day,InDate,@PreDate)=0 and OrderGoodsId>-1
select @Num7=SUM(Price*KCNum) from HW_GoodsInDetail with(nolock) where CKDetailId=0
--select @Num9=COUNT(0) from DT_OrderInfoNew where State=1 and FPDate is not null and MoneyState=1 and PostState=0 and BuyDate is not null
--select @Num9=COUNT(0) from DT_OrderInfoNew with(nolock) where State=1 and PostState=0
select @Num9=COUNT(distinct a.OrderId) from DT_OrderInfoNew a with(nolock) inner join DT_OrderGoods b with(nolock) on a.OrderId=b.OrderId inner join HW_GoodsDetail c with(nolock) on b.DetailId=c.DetailId inner join HW_GoodsInfo d with(nolock) on c.GoodsId=d.GoodsId where a.State=1 and PostState=0 and ( d.muserid=@userid or d.guserid_ebay=@userid or d.guserid_wlmart=@userid or d.guserid_wayfair=@userid or d.guserid_shopify=@userid or d.guserid_sheIn=@userid or d.guserid_tiktok=@userid or d.guserid_temu=@userid)
select @Num11=COUNT(0) from DT_OrderInfo a with(nolock)
inner join DT_OrderBagBack b with(nolock) on a.OrderId=b.OrderId
where a.CompanyId=@CompanyID and DATEDIFF(day,b.InDate,GETDATE())<=7
select @BackPrice=SUM(a.TotalPrice) from DT_OrderInfo a with(nolock)
inner join DT_OrderBagBack b with(nolock) on a.OrderId=b.OrderId
where a.CompanyId=@CompanyID and DATEDIFF(day,b.InDate,GETDATE())<=7
select Num=isnull(@Num,0),Num15=isnull(@Num15,0),Num16=isnull(@Num16,0),Num17=isnull(@Num17,0),Num12=isnull(@Num12,0),Num13=isnull(@Num13,0),Num14=isnull(@Num14,0),TotalPrice=isnull(@TotalPrice,0),SiglePrice=isnull(@SiglePrice,0),TotalPriceUSD=isnull(@TotalPriceUSD,0),TotalPriceUSD2=isnull(@TotalPriceUSD2,0),SiglePriceUSD=isnull(@SiglePriceUSD,0),Num2=isnull(@Num2,0),Num3=isnull(@Num3,0),Num4=isnull(@Num4,0),Num5=isnull(@Num5,0),Num6=isnull(@Num6,0),Num7=isnull(@Num7,0),Num9=isnull(@Num9,0),Num10=isnull(@Num10,0),Num11=isnull(@Num11,0),BackPrice=isnull(@BackPrice,0),Num18=isnull(@Num18,0),Num19=isnull(@Num19,0),Num20=isnull(@Num20,0),Num21=isnull(@Num21,0)
select top 3 a.RMBPrice,b.ShopName from (
select ShopId,RMBPrice=SUM(RMBPrice) from DT_OrderInfo a with(nolock)
inner join DT_OrderGoods b with(nolock) on a.OrderId=b.OrderId
inner join HW_GoodsDetail c with(nolock) on b.DetailId=c.DetailId
inner join HW_GoodsInfo d with(nolock) on c.GoodsId=d.GoodsId
where a.State>0 and a.State<3 and DATEDIFF(day,OrderDate,@PreDate)=0 and ( d.muserid=@userid or d.guserid_ebay=@userid or d.guserid_wlmart=@userid or d.guserid_wayfair=@userid or d.guserid_shopify=@userid or d.guserid_sheIn=@userid or d.guserid_tiktok=@userid or d.guserid_temu=@userid) group by ShopId
)a
inner join JC_Shop b with(nolock) on a.ShopId=b.ShopId
order by a.RMBPrice desc
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
db.AddInParameter(cmd, "@InDate", DbType.DateTime, InDate);
db.AddInParameter(cmd, "@PreDate", DbType.DateTime, PreDate);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, EDate);
db.AddInParameter(cmd, "@userid", DbType.Int32, userid);
DataSet tb = db.ExecuteDataSet(cmd);
if (tb.Tables[0].Rows.Count > 0) model = tb.Tables[0].Rows[0].ToModel<DT_DefaultDataNew>();
string shop = "";
if (tb.Tables[1].Rows.Count > 0)
{
for (int i = 0; i < tb.Tables[1].Rows.Count; i++)
{
//shop += tb.Tables[1].Rows[i]["ShopName"].ToString() + "【" + tb.Tables[1].Rows[i]["RMBPrice"].ToString() + "】 ";
shop += tb.Tables[1].Rows[i]["ShopName"].ToString() + " ";
}
}
model.Num8 = shop.Trim();
return model;
}
#endregion
#region 读取首页编号
public static List<DT_DefaultData5> GetDefaultDataForCode2(int userid,int CompanyID, DateTime? SDate, DateTime? EDate)
{
string tsql = @"
select top 3 Num1=b.GoodsCode,Num2=b.FirstImgUrl,Num4=a.Price from (
select d.GoodsId,Price=SUM(dbo.MoneyUSDChange(a.TotalPrice,a.moneycode,a.companyId)) from DT_OrderInfo a with(nolock)
inner join DT_OrderGoods b with(nolock) on a.OrderId=b.OrderId
inner join HW_GoodsDetail c with(nolock) on b.DetailId=c.DetailId
inner join HW_GoodsInfo d with(nolock) on c.GoodsId=d.GoodsId
where a.CompanyId=@CompanyID and a.state>0 and a.state<3 and a.OrderDate>=@SDate and a.OrderDate<@EDate
and ( d.muserid=@userid or d.guserid_ebay=@userid or d.guserid_wlmart=@userid or d.guserid_wayfair=@userid or d.guserid_shopify=@userid or d.guserid_sheIn=@userid or d.guserid_tiktok=@userid or d.guserid_temu=@userid)
group by d.GoodsId)a
inner join HW_GoodsInfo b with(nolock) on a.GoodsId=b.GoodsId
order by a.Price desc
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@userid", DbType.Int32, userid);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, EDate);
DataTable tb = db.ExecuteDataTable(cmd);
List<DT_DefaultData5> ListModel = tb.ToList<DT_DefaultData5>();
return ListModel;
}
#endregion
#region 读取首页编号
public static List<DT_DefaultData5> GetDefaultDataForCode(int userid,int CompanyID, DateTime? SDate, DateTime? EDate)
{
string tsql = @"
select top 3 Num1=b.GoodsCode,Num2=b.FirstImgUrl,Num3=a.GoodsNum from (
select d.GoodsId,GoodsNum=SUM(b.GoodsNum) from DT_OrderInfo a with(nolock)
inner join DT_OrderGoods b with(nolock) on a.OrderId=b.OrderId
inner join HW_GoodsDetail c with(nolock) on b.DetailId=c.DetailId
inner join HW_GoodsInfo d with(nolock) on c.GoodsId=d.GoodsId
where a.CompanyId=@CompanyID and a.state>0 and a.state<3 and a.OrderDate>=@SDate and a.OrderDate<@EDate
and ( d.muserid=@userid or d.guserid_ebay=@userid or d.guserid_wlmart=@userid or d.guserid_wayfair=@userid or d.guserid_shopify=@userid or d.guserid_sheIn=@userid or d.guserid_tiktok=@userid or d.guserid_temu=@userid)
group by d.GoodsId)a
inner join HW_GoodsInfo b with(nolock) on a.GoodsId=b.GoodsId
order by a.GoodsNum desc
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@userid", DbType.Int32, userid);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, EDate);
DataTable tb = db.ExecuteDataTable(cmd);
List<DT_DefaultData5> ListModel = tb.ToList<DT_DefaultData5>();
return ListModel;
}
#endregion
#region 读取主管店铺
public static List<JC_Shop> GetShopListFPage(int UserId, int PlatType, string UserType)
{
string tsql = @"
select ShopId,ShopName,PlatType,DeptId,InDate,CompanyId,LevelGrade from JC_Shop where
ShopId IN (SELECT ShopId from JC_ShopUser WHERE SType='M' and UserId=@UserId)
and (@PlatType=0 or PlatType=@PlatType) order by PlatType
";
if (UserType == "M" || UserType == "S") tsql = @"
if(select count(0) from JC_Shop where CompanyId=1 and (PlatType=2 or PlatType=6))>4
begin
select ShopId,ShopName,PlatType,DeptId,InDate,CompanyId,LevelGrade from JC_Shop where CompanyId=1 and (PlatType=2 or PlatType=6) and (@PlatType=0 or PlatType=@PlatType) order by PlatType
end
else
begin
select ShopId,ShopName,PlatType,DeptId,InDate,CompanyId,LevelGrade from JC_Shop where CompanyId=1 and (@PlatType=0 or PlatType=@PlatType) order by PlatType
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
db.AddInParameter(cmd, "@PlatType", DbType.Int32, PlatType);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_Shop>();
}
#endregion
#region 返回列表
public static List<DT_USMap> GetListUSOrder(int userid,string GoodsCode, DateTime? SDate, DateTime? EDate)
{
//string tsql = "select a.Id,a.statename,a.Num,a.code,a.color,a.Price from DT_USMap";
string tsql = @"
select b.RevProvinceCode,Num=COUNT(0),Price=SUM(a.totalprice) into #map_ls from DT_OrderInfo a with(nolock)
inner join DT_OrderXXInfo b with(nolock) on a.OrderId=b.OrderId where a.OrderId in (
select distinct a.OrderId from DT_OrderInfo a with(nolock)
inner join DT_OrderXXInfo b with(nolock) on a.OrderId=b.OrderId
inner join DT_OrderGoods c with (nolock) on a.OrderId=c.OrderId
inner join HW_GoodsDetail d with(nolock) on c.DetailId=d.DetailId
inner join HW_GoodsInfo f with(nolock) on d.GoodsId=f.GoodsId
where b.CountryCode='US' and a.OrderDate>=@SDate and a.OrderDate<@EDate and a.State>0 and a.State<3
and ( f.muserid=@userid or f.guserid_ebay=@userid or f.guserid_wlmart=@userid or f.guserid_wayfair=@userid or f.guserid_shopify=@userid or f.guserid_sheIn=@userid or f.guserid_tiktok=@userid or f.guserid_temu=@userid)
)
group by b.RevProvinceCode
select a.Id,statename=a.code,a.ename,name=a.code,a.code,value=isnull(b.Num,1),num=isnull(b.Num,0),Price=isnull(b.Price,0) from DT_USMap a left join #map_ls b on a.code=b.RevProvinceCode order by isnull(b.Num,0) desc,isnull(b.Price,0) desc
drop table #map_ls
";
if (GoodsCode != "")
{
tsql = @"select a.Id,a.statename,a.ename,name=a.code,a.code,value=isnull(b.Num,0),num=isnull(b.Num,0),Price=isnull(b.Price,0) from DT_USMap a
left join (
select b.RevProvinceCode,Num=COUNT(0),Price=SUM(a.totalprice) from DT_OrderInfo a
inner join DT_OrderXXInfo b on a.OrderId=b.OrderId
inner join DT_OrderGoods c on a.OrderId=c.OrderId
inner join HW_GoodsDetail d on c.DetailId=d.DetailId
inner join HW_GoodsInfo e on d.GoodsId=e.GoodsId
where b.CountryCode='US' and e.GoodsCode like '%'+@GoodsCode+'%' and a.OrderDate>=@SDate and a.OrderDate<@EDate and a.State>0 and a.State<3 group by b.RevProvinceCode)b on a.code=b.RevProvinceCode
order by isnull(b.Num,0) desc,isnull(b.Price,0) desc";
}
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@userid", DbType.Int32, userid);
db.AddInParameter(cmd, "@GoodsCode", DbType.String, GoodsCode);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, EDate);
DataTable tb = db.ExecuteDataTable(cmd);
List<DT_USMap> ListModel = tb.ToList<DT_USMap>();
return ListModel;
}
#endregion
#region 按s首页日期返回订单数
public static List<DT_CountListFromDay> GetDefaultCountListFromDay2(int userid, int PlatType, string Ids, DateTime? SDate, DateTime? EDate, int CompanyId)
{
string tsql = "";
List<DT_CountListFromDay> list = null;
if (PlatType == 1)
{
tsql = @"select PlatId,OrderDate=convert(nvarchar(10),OrderDate,120),Num=COUNT(0) from DT_OrderInfo a
inner join DT_OrderGoods b on a.OrderId=b.OrderId
inner join HW_GoodsDetail c on c.DetailId=b.DetailId
inner join HW_GoodsInfo d on d.GoodsId=c.GoodsId
where a.State>0 and a.State<3 and a.CompanyId=@CompanyId and orderDate>=@SDate and orderDate<@EDate
and ( d.muserid=@userid or d.guserid_ebay=@userid or d.guserid_wlmart=@userid or d.guserid_wayfair=@userid or d.guserid_shopify=@userid or d.guserid_sheIn=@userid or d.guserid_tiktok=@userid or d.guserid_temu=@userid)
group by PlatId,convert(nvarchar(10),OrderDate,120) ";
}
else if (PlatType == 3)
{
tsql = @"select PlatId=ShopId,OrderDate=convert(nvarchar(10),OrderDate,120),Price=SUM(dbo.MoneyUSDChange(totalprice,a.moneycode,1)) from DT_OrderInfo a
inner join DT_OrderGoods b on a.OrderId=b.OrderId
inner join HW_GoodsDetail c on c.DetailId=b.DetailId
inner join HW_GoodsInfo d on d.GoodsId=c.GoodsId
where a.State>0 and a.State<3 and a.CompanyId=@CompanyId and orderDate>=@SDate and orderDate<@EDate
and ( d.muserid=@userid or d.guserid_ebay=@userid or d.guserid_wlmart=@userid or d.guserid_wayfair=@userid or d.guserid_shopify=@userid or d.guserid_sheIn=@userid or d.guserid_tiktok=@userid or d.guserid_temu=@userid)
group by ShopId,convert(nvarchar(10),OrderDate,120)";
}
else if (PlatType == 4)
{
tsql = @"select PlatId=0,OrderDate=convert(nvarchar(10),OrderDate,120),Price=SUM(dbo.MoneyUSDChange(totalprice,a.moneycode,1)) from DT_OrderInfo a
inner join DT_OrderGoods b on a.OrderId=b.OrderId
inner join HW_GoodsDetail c on c.DetailId=b.DetailId
inner join HW_GoodsInfo d on d.GoodsId=c.GoodsId
where a.State>0 and a.State<3 and a.CompanyId=@CompanyId and orderDate>=@SDate and orderDate<@EDate
and ( d.muserid=@userid or d.guserid_ebay=@userid or d.guserid_wlmart=@userid or d.guserid_wayfair=@userid or d.guserid_shopify=@userid or d.guserid_sheIn=@userid or d.guserid_tiktok=@userid or d.guserid_temu=@userid)
group by convert(nvarchar(10),OrderDate,120)";
}
else
{
tsql = @"select PlatId=ShopId,OrderDate=convert(nvarchar(10),OrderDate,120),Num=COUNT(0) from DT_OrderInfo a
inner join DT_OrderGoods b on a.OrderId=b.OrderId
inner join HW_GoodsDetail c on c.DetailId=b.DetailId
inner join HW_GoodsInfo d on d.GoodsId=c.GoodsId
where a.State>0 and a.State<3 and a.CompanyId=@CompanyId and orderDate>=@SDate and orderDate<@EDate
and ( d.muserid=@userid or d.guserid_ebay=@userid or d.guserid_wlmart=@userid or d.guserid_wayfair=@userid or d.guserid_shopify=@userid or d.guserid_sheIn=@userid or d.guserid_tiktok=@userid or d.guserid_temu=@userid)
group by ShopId,convert(nvarchar(10),OrderDate,120)";
}
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Ids", DbType.String, Ids);
db.AddInParameter(cmd, "@userid", DbType.Int32, userid);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, EDate);
DataSet ds = db.ExecuteDataSet(cmd);
list = ds.Tables[0].ToList<DT_CountListFromDay>();
return list;
}
#endregion
#region 按s首页日期返回订单数
public static List<DT_CountForMonth> GetDefaultCountListFromMonth(int userid, DateTime? SDate, DateTime? EDate, int CompanyId)
{
string tsql = "";
List<DT_CountForMonth> list = null;
tsql = @"select ShopId,Month=DATEPART(month,OrderDate),Num=COUNT(0) from DT_OrderInfo a with(nolock)
inner join DT_OrderGoods b with(nolock) on a.OrderId=b.OrderId
inner join HW_GoodsDetail c with(nolock) on b.DetailId=c.DetailId
inner join HW_GoodsInfo d with(nolock) on d.GoodsId=c.GoodsId
where a.CompanyId=@CompanyId and a.State>0 and a.State<3 and OrderDate>=@SDate
and ( d.muserid=@userid or d.guserid_ebay=@userid or d.guserid_wlmart=@userid or d.guserid_wayfair=@userid or d.guserid_shopify=@userid or d.guserid_sheIn=@userid or d.guserid_tiktok=@userid or d.guserid_temu=@userid)
group by shopId,DATEPART(month,OrderDate)";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@userid", DbType.Int32, userid);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, EDate);
DataSet ds = db.ExecuteDataSet(cmd);
list = ds.Tables[0].ToList<DT_CountForMonth>();
return list;
}
#endregion
#region 按s首页日期返回订单金额
public static List<DT_CountForMonth> GetDefaultCountListFromMonthPrice(int userid, DateTime? SDate, DateTime? EDate, int CompanyId)
{
string tsql = "";
List<DT_CountForMonth> list = null;
tsql = @"select ShopId,Month=DATEPART(month,OrderDate),Price=SUM(dbo.MoneyUSDChange(TotalPrice,a.moneycode,a.companyid)) from DT_OrderInfo a with(nolock)
inner join DT_OrderGoods b with(nolock) on a.OrderId=b.OrderId
inner join HW_GoodsDetail c with(nolock) on b.DetailId=c.DetailId
inner join HW_GoodsInfo d with(nolock) on d.GoodsId=c.GoodsId
where a.CompanyId=@CompanyId and a.State>0 and a.State<3 and OrderDate>=@SDate
and ( d.muserid=@userid or d.guserid_ebay=@userid or d.guserid_wlmart=@userid or d.guserid_wayfair=@userid or d.guserid_shopify=@userid or d.guserid_sheIn=@userid or d.guserid_tiktok=@userid or d.guserid_temu=@userid)
group by shopId,DATEPART(month,OrderDate)";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@userid", DbType.Int32, userid);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, EDate);
DataSet ds = db.ExecuteDataSet(cmd);
list = ds.Tables[0].ToList<DT_CountForMonth>();
return list;
}
#endregion
#region 按s首页日期返回订单数
public static List<DT_CountForMonth> GetDefaultCountListFromMonth2(int userid, DateTime? SDate, DateTime? EDate, int CompanyId)
{
string tsql = "";
List<DT_CountForMonth> list = null;
tsql = @"select ShopId=0,Month=DATEPART(month,OrderDate),Num=COUNT(0) from DT_OrderInfo a with(nolock)
inner join DT_OrderGoods b with(nolock) on a.OrderId=b.OrderId
inner join HW_GoodsDetail c with(nolock) on b.DetailId=c.DetailId
inner join HW_GoodsInfo d with(nolock) on d.GoodsId=c.GoodsId
where a.CompanyId=@CompanyId and a.State>0 and a.State<3 and OrderDate>=@SDate
and ( d.muserid=@userid or d.guserid_ebay=@userid or d.guserid_wlmart=@userid or d.guserid_wayfair=@userid or d.guserid_shopify=@userid or d.guserid_sheIn=@userid or d.guserid_tiktok=@userid or d.guserid_temu=@userid)
group by DATEPART(month,OrderDate)";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@userid", DbType.Int32, userid);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, EDate);
DataSet ds = db.ExecuteDataSet(cmd);
list = ds.Tables[0].ToList<DT_CountForMonth>();
return list;
}
#endregion
#region 按s首页日期返回订单金额
public static List<DT_CountForMonth> GetDefaultCountListFromMonthPrice2(int userid, DateTime? SDate, DateTime? EDate, int CompanyId)
{
string tsql = "";
List<DT_CountForMonth> list = null;
tsql = @"select ShopId=0,Month=DATEPART(month,OrderDate),Price=SUM(dbo.MoneyUSDChange(TotalPrice,a.moneycode,a.companyid)) from DT_OrderInfo a with(nolock)
inner join DT_OrderGoods b with(nolock) on a.OrderId=b.OrderId
inner join HW_GoodsDetail c with(nolock) on b.DetailId=c.DetailId
inner join HW_GoodsInfo d with(nolock) on d.GoodsId=c.GoodsId
where a.CompanyId=@CompanyId and a.State>0 and a.State<3 and OrderDate>=@SDate
and ( d.muserid=@userid or d.guserid_ebay=@userid or d.guserid_wlmart=@userid or d.guserid_wayfair=@userid or d.guserid_shopify=@userid or d.guserid_sheIn=@userid or d.guserid_tiktok=@userid or d.guserid_temu=@userid)
group by DATEPART(month,OrderDate)";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@userid", DbType.Int32, userid);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, EDate);
DataSet ds = db.ExecuteDataSet(cmd);
list = ds.Tables[0].ToList<DT_CountForMonth>();
return list;
}
#endregion
#region 按s首页日期返回订单数
public static List<DT_CountForMonth> GetDefaultCountListFromMonthPT(int userid, DateTime? SDate, DateTime? EDate, int CompanyId)
{
string tsql = "";
List<DT_CountForMonth> list = null;
tsql = @"select PlatId,Month=DATEPART(month,OrderDate),Num=COUNT(0) from DT_OrderInfo a with(nolock)
inner join DT_OrderGoods b with(nolock) on a.OrderId=b.OrderId
inner join HW_GoodsDetail c with(nolock) on b.DetailId=c.DetailId
inner join HW_GoodsInfo d with(nolock) on d.GoodsId=c.GoodsId
where a.CompanyId=@CompanyId and a.State>0 and a.State<3 and OrderDate>=@SDate
and ( d.muserid=@userid or d.guserid_ebay=@userid or d.guserid_wlmart=@userid or d.guserid_wayfair=@userid or d.guserid_shopify=@userid or d.guserid_sheIn=@userid or d.guserid_tiktok=@userid or d.guserid_temu=@userid)
group by PlatId,DATEPART(month,OrderDate)";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@userid", DbType.Int32, userid);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, EDate);
DataSet ds = db.ExecuteDataSet(cmd);
list = ds.Tables[0].ToList<DT_CountForMonth>();
return list;
}
#endregion
#region 按s首页日期返回订单金额
public static List<DT_CountForMonth> GetDefaultCountListFromMonthPTPrice(int userid, DateTime? SDate, DateTime? EDate, int CompanyId)
{
string tsql = "";
List<DT_CountForMonth> list = null;
tsql = @"select PlatId,Month=DATEPART(month,OrderDate),Price=SUM(dbo.MoneyUSDChange(TotalPrice,a.moneycode,a.companyid)) from DT_OrderInfo a with(nolock)
inner join DT_OrderGoods b with(nolock) on a.OrderId=b.OrderId
inner join HW_GoodsDetail c with(nolock) on b.DetailId=c.DetailId
inner join HW_GoodsInfo d with(nolock) on d.GoodsId=c.GoodsId
where a.CompanyId=@CompanyId and a.State>0 and a.State<3 and OrderDate>=@SDate
and ( d.muserid=@userid or d.guserid_ebay=@userid or d.guserid_wlmart=@userid or d.guserid_wayfair=@userid or d.guserid_shopify=@userid or d.guserid_sheIn=@userid or d.guserid_tiktok=@userid or d.guserid_temu=@userid)
group by PlatId,DATEPART(month,OrderDate)";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@userid", DbType.Int32, userid);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, EDate);
DataSet ds = db.ExecuteDataSet(cmd);
list = ds.Tables[0].ToList<DT_CountForMonth>();
return list;
}
#endregion
#region 首页报表查询的新方法
#region 查询某人某日分配sku的订单
/// <summary>
/// 查询某人某日分配sku的订单
/// </summary>
/// <param name="newDate">查询时间</param>
/// <param name="userid">查询人</param>
/// <returns></returns>
public static List<DT_Order> MRJL_GetUserListByDay(DateTime newDate, int userid)
{
//返回数据
List<DT_Order> ListModel = new List<DT_Order>();
//店铺数据记录
Dictionary<int, string> shopMsg = new Dictionary<int, string>();
shopMsg.Add(15, "guserid_shopify");
shopMsg.Add(18, "guserid_wayfair");
shopMsg.Add(21, "guserid_sheIn");
shopMsg.Add(3, "guserid_ebay");
shopMsg.Add(6, "guserid_wlmart");
shopMsg.Add(2, "muserid");
shopMsg.Add(22, "guserid_tiktok");
shopMsg.Add(23, "guserid_temu");
//循环获取订单数据
foreach (var sMsg in shopMsg)
{
string tsql = @"select oi.* from DT_OrderInfo oi with(nolock)
left join DT_OrderGoods og with(nolock) on oi.OrderId=og.OrderId
left join HW_GoodsDetail hgd with(nolock) on hgd.DetailId=og.DetailId
where DATEDIFF(day,oi.OrderDate,@newDate)=0
and oi.ShopId in (select DISTINCT ShopId from JC_Shop where PlatType = @PlatType)
and hgd.GoodsID in (select GoodsID from HW_GoodsInfo where " + sMsg.Value + " = @skuUserId)";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@newDate", DbType.Date, newDate.Date);
db.AddInParameter(cmd, "@PlatType", DbType.Int32, sMsg.Key);
//db.AddInParameter(cmd, "@PlatGoodsId", DbType.String, sMsg.Value);
db.AddInParameter(cmd, "@skuUserId", DbType.Int32, userid);
DataTable tb = db.ExecuteDataTable(cmd);
var list = tb.ToList<DT_Order>();
if (list != null && list.Count() > 0)
{
ListModel.AddRange(list);
}
}
return ListModel;
}
#endregion
#region 获取店铺信息
/// <summary>
/// 获取店铺信息
/// </summary>
/// <param name="newDate">查询时间</param>
/// <param name="userid">查询人</param>
/// <returns></returns>
public static Dictionary<int, string> GetShopUserId()
{
//返回数据
List<DT_Order> ListModel = new List<DT_Order>();
//店铺数据记录
Dictionary<int, string> shopMsg = new Dictionary<int, string>();
shopMsg.Add(15, "guserid_shopify");
shopMsg.Add(18, "guserid_wayfair");
shopMsg.Add(21, "guserid_sheIn");
shopMsg.Add(3, "guserid_ebay");
shopMsg.Add(6, "guserid_wlmart");
shopMsg.Add(2, "muserid");
shopMsg.Add(22, "guserid_tiktok");
shopMsg.Add(23, "guserid_temu");
return shopMsg;
}
#endregion
#region 查询某人某日分配sku的订单总和信息
/// <summary>
/// 查询某人某日分配sku的订单
/// </summary>
/// <param name="newDate">查询时间</param>
/// <param name="userid">查询人</param>
/// <returns></returns>
public static List<BB_MRJL_User> MRJL_GetUserOderMsgByDay(DateTime newDate, int userid)
{
//返回数据
List<BB_MRJL_User> ListModel = new List<BB_MRJL_User>();
//店铺数据记录
Dictionary<int, string> shopMsg = GetShopUserId();
//循环获取订单数据
foreach (var sMsg in shopMsg)
{
string tsql = @"
select oi.ShopId,
OrderNum = COUNT(0),
RMBMoney = SUM(RMBPrice),
escrowFee = sum(isnull(oi.escrowFee, 0)),
USDMoney = SUM(oi.TotalPrice),
InPrice = SUM(fee.cb),
ckfee = SUM(fee.ck),
mdfee = SUM(fee.md),
adfee = SUM(oi.Ad_Fee),
yf = SUM(fee.yf),
LR=SUM(isnull(TotalPrice, 0) - isnull(escrowFee, 0) - isnull(yj, 0) - isnull(yf, 0) -((isnull(cb, 0) + isnull(hwsf, 0)) / moneyrate) - isnull(tc, 0) - isnull(ck, 0) - isnull(md, 0) - isnull(Ad_Fee, 0) + SubsidyFee)
from DT_OrderInfo oi with(nolock)
left join DT_Fees fee with(nolock) on oi.OrderId = fee.orderid
left join DT_OrderGoods og with(nolock) on oi.OrderId=og.OrderId
left join HW_GoodsDetail hgd with(nolock) on hgd.DetailId=og.DetailId
where DATEDIFF(day,oi.OrderDate,@newDate)=0
and oi.ShopId in (select DISTINCT ShopId from JC_Shop where PlatType = @PlatType)
and hgd.GoodsID in (select GoodsID from HW_GoodsInfo where " + sMsg.Value + @" = @skuUserId)
group by oi.ShopId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@newDate", DbType.Date, newDate.Date);
db.AddInParameter(cmd, "@PlatType", DbType.Int32, sMsg.Key);
db.AddInParameter(cmd, "@skuUserId", DbType.Int32, userid);
DataTable tb = db.ExecuteDataTable(cmd);
var list = tb.ToList<BB_MRJL_User>();
if (list != null && list.Count() > 0)
{
ListModel.AddRange(list);
}
}
return ListModel;
}
#endregion
#region 查询某人某日分配sku的订单的goodsid信息
/// <summary>
/// 查询某人某日分配sku的订单
/// </summary>
/// <param name="newDate">查询时间</param>
/// <param name="userid">查询人</param>
/// <returns></returns>
public static List<BB_MRJL_GoodsID> MRJL_GetGoodsIdByDay(DateTime newDate, int userid)
{
//返回数据
List<BB_MRJL_GoodsID> ListModel = new List<BB_MRJL_GoodsID>();
//店铺数据记录
Dictionary<int, string> shopMsg = GetShopUserId();
//循环获取订单数据
foreach (var sMsg in shopMsg)
{
string tsql = @"select DISTINCT oi.PlatId,oi.ShopId,hgd.GoodsID
from DT_OrderInfo oi with(nolock)
left join DT_OrderGoods og with(nolock) on oi.OrderId=og.OrderId
left join HW_GoodsDetail hgd with(nolock) on hgd.DetailId=og.DetailId
where DATEDIFF(day,oi.OrderDate,@newDate)=0
and oi.ShopId in (select DISTINCT ShopId from JC_Shop where PlatType = @PlatType)
and hgd.GoodsID in (select GoodsID from HW_GoodsInfo where " + sMsg.Value + @" = @skuUserId)";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@newDate", DbType.Date, newDate.Date);
db.AddInParameter(cmd, "@PlatType", DbType.Int32, sMsg.Key);
db.AddInParameter(cmd, "@skuUserId", DbType.Int32, userid);
DataTable tb = db.ExecuteDataTable(cmd);
var list = tb.ToList<BB_MRJL_GoodsID>();
if (list != null && list.Count() > 0)
{
ListModel.AddRange(list);
}
}
return ListModel;
}
#endregion
#region 获取日广告信息
/// <summary>
/// 获取日广告信息
/// </summary>
/// <param name="newDate">查询时间</param>
/// <param name="userid">查询人</param>
/// <returns></returns>
public static BB_MRJL_Adfee MRJL_GetGKFeeByDay(DateTime newDate, int plat, int shopid, List<string> goodsIds)
{
//返回数据
BB_MRJL_Adfee model = new BB_MRJL_Adfee();
string tsql = "";
if (goodsIds.Count() > 0)
{
string goodsIdStr = string.Join(",", goodsIds);
//亚马逊
if (plat == 2)
{
tsql = @"
SELECT shopid,Adfee=SUM(isnull(adfeenew, 0))
from AD_FeeList
where
DATEDIFF(day,addate,@newDate)=0
and shopid = @shopid
and goodsid in (" + goodsIdStr + @")
GROUP BY shopid";
}
else
{
tsql = @"
SELECT shopid,Adfee=SUM(isnull(adfee, 0))
from ShopGoodsAdFee
where DATEDIFF(day,addate,@newDate)=0
and shopid = @shopid
and goodsid in (" + goodsIdStr + @")
GROUP BY shopid";
}
}
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@newDate", DbType.Date, newDate.Date);
db.AddInParameter(cmd, "@shopid", DbType.Int32, shopid);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb != null && tb.Rows.Count > 0) model = tb.Rows[0].ToModel<BB_MRJL_Adfee>();
return model;
}
#endregion
#region 获取店铺名称
/// <summary>
/// 店铺名称
/// </summary>
/// <param name="newDate">查询时间</param>
/// <param name="userid">查询人</param>
/// <returns></returns>
public static string MRJL_GetShopName(int shopid)
{
string name = "";
try
{
string tsql = "SELECT top 1 ShopName from jc_shop where ShopId = @ShopId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@ShopId", DbType.Int32, shopid);
name = Convert.ToString(db.ExecuteScalar(cmd));
}
catch
{
}
return name;
}
#endregion
#region 获取月广告信息
/// <summary>
/// 获取月广告信息
/// </summary>
/// <param name="newDate">查询时间</param>
/// <param name="userid">查询人</param>
/// <returns></returns>
public static BB_MRJL_Adfee MRJL_GetGKFeeByMouth(DateTime newDate, int plat, int shopid, List<string> goodsIds)
{
//返回数据
BB_MRJL_Adfee model = new BB_MRJL_Adfee();
string tsql = "";
if (goodsIds.Count() > 0)
{
string goodsIdStr = string.Join(",", goodsIds);
//亚马逊
if (plat == 2)
{
tsql = @"
SELECT shopid,Adfee=SUM(isnull(adfeenew, 0))
from AD_FeeList
where
CONVERT(date, addate) <= @newDate
and CONVERT(date, addate) >= @newDate1
and shopid = @shopid
and goodsid in (" + goodsIdStr + @")
GROUP BY shopid";
}
else
{
tsql = @"
SELECT shopid,Adfee=SUM(isnull(adfee, 0))
from ShopGoodsAdFee
where CONVERT(date, addate) <= @newDate
and CONVERT(date, addate) >= @newDate1
and shopid = @shopid
and goodsid in (" + goodsIdStr + @")
GROUP BY shopid";
}
}
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@newDate", DbType.Date, newDate.Date);
db.AddInParameter(cmd, "@newDate1", DbType.Date, new DateTime(newDate.Year, newDate.Month, 1).Date);
db.AddInParameter(cmd, "@shopid", DbType.Int32, shopid);
DataTable tb = db.ExecuteDataTable(cmd);
//ListModel = tb.ToList<BB_MRJL_Adfee>();
if (tb != null && tb.Rows.Count > 0) model = tb.Rows[0].ToModel<BB_MRJL_Adfee>();
return model;
}
#endregion
#region 查询上架编号数和订单编号数
/// <summary>
/// 获取月广告信息
/// </summary>
/// <param name="newDate">查询时间</param>
/// <param name="userid">查询人</param>
/// <returns></returns>
public static BB_MRJL_GoodsIDMsg MRJL_GetGoodsMsgByMouth(DateTime newDate, int plat, int shopid, int userid)
{
//返回数据
BB_MRJL_GoodsIDMsg model = new BB_MRJL_GoodsIDMsg();
//店铺数据记录
Dictionary<int, string> shopMsg = GetShopUserId();
string tsql = @"
select
a.*,
Num2 = isnull(b.Num2, 0) from
(
select
b.ShopId,
Num = COUNT(0)
from
HW_GoodsInfo a with(nolock)
inner join HW_Shop b with(nolock) on a.GoodsId = b.GoodsId
where
a." + shopMsg[plat] + @" = @skuUserId
and b.shopid = @shopid
group by
b.ShopId
) a
left join (
select
a.ShopId,
Num2 = COUNT(distinct c.GoodsId)
from
DT_OrderInfo a with(nolock)
inner join DT_OrderGoods b with(nolock) on a.OrderId = b.OrderId
inner join HW_GoodsDetail c with(nolock) on b.DetailId = c.DetailId
inner JOIN HW_GoodsInfo d with(nolock) on d.GoodsId = c.GoodsId
where
a.State > 0
and a.State < 3
and DATEDIFF(day, a.OrderDate, @newDate) = 0
and
d." + shopMsg[plat] + @" = @skuUserId
and a.ShopId = @shopid
group by
a.ShopId
) b on a.ShopId = b.ShopId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@newDate", DbType.Date, newDate.Date);
db.AddInParameter(cmd, "@shopid", DbType.Int32, shopid);
db.AddInParameter(cmd, "@skuUserId", DbType.Int32, userid);
DataTable tb = db.ExecuteDataTable(cmd);
//ListModel = tb.ToList<BB_MRJL_Adfee>();
if (tb != null && tb.Rows.Count > 0) model = tb.Rows[0].ToModel<BB_MRJL_GoodsIDMsg>();
return model;
}
#endregion
#region list转DataTable
public static DataTable ListToDataTable<T>(List<T> items)
{
// 获取类型T的所有公共属性
var properties = typeof(T).GetProperties();
// 创建DataTable
var table = new DataTable();
// 添加列
foreach (var prop in properties)
{
table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
}
// 添加行
foreach (var item in items)
{
var values = properties.Select(prop => prop.GetValue(item, null)).ToArray();
table.Rows.Add(values);
}
return table;
}
#endregion
#endregion
}
}
Reference in New Issue View Git Blame Copy Permalink