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.
2182 lines
115 KiB
C#
2182 lines
115 KiB
C#
using NetLibrary.ReportPrint;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.ComponentModel.Design;
|
|
using System.Data;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Web;
|
|
using System.Web.Script.Serialization;
|
|
using System.Web.Services;
|
|
using System.Xml.Linq;
|
|
using TradeData;
|
|
using TradeManage;
|
|
using TradeManageNew;
|
|
using TradeModel;
|
|
|
|
namespace ERPOMS
|
|
{
|
|
/// <summary>
|
|
/// SysManageServiceNew 的摘要说明
|
|
/// </summary>
|
|
[WebService(Namespace = "http://tempuri.org/")]
|
|
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
|
|
[System.ComponentModel.ToolboxItem(false)]
|
|
// 若要允许使用 ASP.NET AJAX 从脚本中调用此 Web 服务，请取消注释以下行。
|
|
[System.Web.Script.Services.ScriptService]
|
|
public class SysManageServiceNew : System.Web.Services.WebService
|
|
{
|
|
|
|
[WebMethod]
|
|
public string HelloWorld()
|
|
{
|
|
return "Hello World";
|
|
}
|
|
#region 登陆
|
|
[WebMethod(EnableSession = true)]
|
|
public bool Login(string UserName, string PassWord, string ul, string LoginIp)
|
|
{
|
|
//HttpContext.Current.Request.UserHostAddress
|
|
//HttpContext.Current.Request.ServerVariables.GetValues("REMOTE_ADDR")[0]
|
|
//string LoginIp = HttpContext.Current.Request.ServerVariables.GetValues("REMOTE_ADDR")[0];
|
|
int IsNb = 1;
|
|
//if (ul.Contains("192.168."))
|
|
// IsNb = 1;
|
|
//if (ul.Contains("http://localhost:"))
|
|
// IsNb = 1;
|
|
|
|
var list = DataNew.Login(UserName, PassWord, LoginIp, IsNb);
|
|
if (list == null)
|
|
{
|
|
|
|
return false;
|
|
}
|
|
UserModel model = new UserModel();
|
|
if (list.CompanyId > 0) model.CompanyID = list.CompanyId.Value;
|
|
model.Name = list.Name;
|
|
model.UserID = list.UserId.Value;
|
|
model.UserType = list.UserType;
|
|
model.UserName = UserName;
|
|
model.PassWord = PassWord;
|
|
model.CompanyName = list.CompanyName;
|
|
if (list.DeptId != null)
|
|
model.DeptId = list.DeptId.Value;
|
|
else
|
|
model.DeptId = 0;
|
|
model.Duty = list.Duty;
|
|
|
|
|
|
Session["CompanyId"] = model.CompanyID;
|
|
Session["DeptId"] = model.DeptId;
|
|
Session["Name"] = model.Name;
|
|
Session["UserId"] = model.UserID;
|
|
Session["UserName"] = model.UserName;
|
|
Session["UserType"] = model.UserType;
|
|
Session["CompanyName"] = model.CompanyName;
|
|
Session["Duty"] = model.Duty;
|
|
Session["AllShop"] = list.UserCard;//是否查询所有店铺
|
|
Session["Login_UserModel"] = model;
|
|
|
|
return true;
|
|
}
|
|
#endregion
|
|
|
|
#region 修改密码
|
|
[WebMethod(EnableSession = true)]
|
|
public int UpdatePassWord(string UserName, string OldPassword, string NewPassWord)
|
|
{
|
|
PagesNew.Login(this.Session);
|
|
int UserID = Convert.ToInt32(Session["UserId"]);
|
|
return DataNew.UpdatePassWord(UserName, UserID, OldPassword, NewPassWord);
|
|
}
|
|
#endregion
|
|
#region 读取菜单
|
|
[WebMethod(EnableSession = true)]
|
|
public List<JC_Menu> GetListMenuErp()
|
|
{
|
|
PagesNew.Login(this.Session);
|
|
string UserCode = Convert.ToString(Session["UserId"]);
|
|
int UserID = Convert.ToInt32(Session["UserId"]);
|
|
int CompanyId = Convert.ToInt32(Session["CompanyId"]);
|
|
var slist = DataNew.GetListMenu_Oms(UserID, CompanyId);
|
|
List<JC_Menu> list = new List<JC_Menu>();
|
|
if (slist != null)
|
|
{
|
|
foreach (var md in slist)
|
|
{
|
|
JC_Menu smd = new JC_Menu();
|
|
smd.parent_menu_no = md.ParentModuleNo;
|
|
smd.menu_no = md.ModuleNo;
|
|
smd.menu_url = md.ModuleUrl;
|
|
smd.target = md.Target;
|
|
smd.menu_name = md.ModuleName;
|
|
smd.sort_no = md.SortNo;
|
|
list.Add(smd);
|
|
}
|
|
|
|
}
|
|
|
|
|
|
List<JC_Menu> mlist = new List<JC_Menu>();
|
|
if (list != null)
|
|
{
|
|
|
|
var pmd = list.FindAll(n => n.parent_menu_no == null);
|
|
if (pmd != null)
|
|
{
|
|
foreach (var md in pmd)
|
|
{
|
|
|
|
md.layer = 1;
|
|
mlist.Add(md);
|
|
|
|
var ppmd = list.FindAll(n => n.parent_menu_no == md.menu_no);
|
|
if (ppmd != null)
|
|
{
|
|
if (ppmd.Count > 0)
|
|
md.has_child = 1;
|
|
foreach (var mmd in ppmd)
|
|
{
|
|
mmd.layer = 2;
|
|
mlist.Add(mmd);
|
|
|
|
var ppmd2 = list.FindAll(n => n.parent_menu_no == mmd.menu_no);
|
|
if (ppmd2 != null)
|
|
{
|
|
if (ppmd2.Count > 0)
|
|
mmd.has_child = 1;
|
|
foreach (var mmd2 in ppmd2)
|
|
{
|
|
|
|
mmd2.layer = 3;
|
|
mlist.Add(mmd2);
|
|
var ppmd3 = list.FindAll(n => n.parent_menu_no == mmd2.menu_no);
|
|
if (ppmd3 != null)
|
|
{
|
|
if (ppmd3.Count > 0)
|
|
mmd2.has_child = 1;
|
|
foreach (var mmd3 in ppmd3)
|
|
{
|
|
mmd3.layer = 4;
|
|
mlist.Add(mmd3);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
|
|
}
|
|
|
|
if (mlist != null)
|
|
{
|
|
var umd = mlist.FindAll(n => n.menu_url != null && n.menu_url != "");
|
|
if (umd != null)
|
|
{
|
|
foreach (var md in umd)
|
|
{
|
|
md.use_state = 2;
|
|
var pmd = mlist.Find(n => n.menu_no == md.parent_menu_no);
|
|
if (pmd != null)
|
|
{
|
|
pmd.use_state = 2;
|
|
var pmd2 = mlist.Find(n => n.menu_no == pmd.parent_menu_no);
|
|
if (pmd2 != null)
|
|
{
|
|
pmd2.use_state = 2;
|
|
var pmd3 = mlist.Find(n => n.menu_no == pmd2.parent_menu_no);
|
|
if (pmd3 != null)
|
|
pmd3.use_state = 2;
|
|
}
|
|
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
if (mlist != null)
|
|
{
|
|
List<JC_Menu> mmlist = mlist.FindAll(n => n.use_state == 2);
|
|
|
|
return mmlist;
|
|
}
|
|
return mlist;
|
|
}
|
|
#endregion
|
|
|
|
|
|
|
|
#region 普通查询列表
|
|
[WebMethod(EnableSession = true)]
|
|
public List<JC_UserInfo> GetListUserInfoFromGroup2(int iscp)
|
|
{
|
|
PagesNew.Login(this.Session);
|
|
int UserId = Convert.ToInt32(Session["UserId"]);
|
|
var ListModel = DataNew.GetListUserInfoFromGroup2(UserId, iscp);
|
|
return ListModel;
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region 查询部门列表
|
|
[WebMethod(EnableSession = true)]
|
|
public List<JC_DepartMent> GetDepartList(string DeptType)
|
|
{
|
|
PagesNew.Login(this.Session);
|
|
int CompanyId = Convert.ToInt32(Session["CompanyId"]);
|
|
var list = DataNew.GetDepartList(CompanyId, DeptType);
|
|
return list;
|
|
}
|
|
#endregion
|
|
|
|
#region 读取平台
|
|
[WebMethod(EnableSession = true)]
|
|
public List<JC_BaseCodeDetail> GetPlatBaseCode(int CompanyID, string KeyName)
|
|
{
|
|
PagesNew.Login(this.Session);
|
|
if (CompanyID == 0) CompanyID = Convert.ToInt32(Session["CompanyId"]);
|
|
return DataNew.GetPlatBaseCode(CompanyID, KeyName);
|
|
}
|
|
#endregion
|
|
|
|
#region 查询物流
|
|
[WebMethod(EnableSession = true)]
|
|
public List<JC_ExpressType> GetExpressTypeList()
|
|
{
|
|
PagesNew.Login(this.Session);
|
|
int CompanyId = Convert.ToInt32(Session["CompanyId"]);
|
|
var list = DataNew.GetExpressTypeList();
|
|
return list;
|
|
}
|
|
#endregion
|
|
|
|
#region 查询开启仓库
|
|
[WebMethod(EnableSession = true)]
|
|
public List<CK_StoreHouse> GetUseStoreHouse()
|
|
{
|
|
PagesNew.Login(this.Session);
|
|
int CompanyId = Convert.ToInt32(Session["CompanyId"]);
|
|
|
|
var list = DataNew.GetUseStoreHouse(CompanyId);
|
|
|
|
return list;
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 平台店铺
|
|
[WebMethod(EnableSession = true)]
|
|
public List<JC_Shop> GetShopListForTM(int PlatType)
|
|
{
|
|
PagesNew.Login(this.Session);
|
|
int UserId = Convert.ToInt32(Session["UserId"]);
|
|
|
|
string UserType = Convert.ToString(Session["UserType"]);
|
|
string AllShop = Convert.ToString(Session["AllShop"]);
|
|
var list = DataNew.GetShopListUser(UserId, PlatType, UserType, AllShop);
|
|
return list;
|
|
}
|
|
#endregion
|
|
|
|
#region 读取留言模板
|
|
[WebMethod(EnableSession = true)]
|
|
public List<LY_Templete> GetTempleteList()
|
|
{
|
|
PagesNew.Login(this.Session);
|
|
int CompanyId = Convert.ToInt32(Session["CompanyId"]);
|
|
return DataNew.GetLY_TempleteList(CompanyId);
|
|
}
|
|
#endregion
|
|
|
|
#region 读取货币
|
|
[WebMethod(EnableSession = true)]
|
|
public List<JC_Money> GetMoneyList()
|
|
{
|
|
Pages.Login(this.Session);
|
|
int CompanyId = Convert.ToInt32(Session["CompanyId"]);
|
|
return DataNew.GetMoneyList(CompanyId);
|
|
}
|
|
#endregion
|
|
|
|
#region 读取物流渠道
|
|
[WebMethod(EnableSession = true)]
|
|
public List<JC_ExpressDetail> GetExpressPostList(int LogisticsId)
|
|
{
|
|
Pages.Login(this.Session);
|
|
int CompanyId = Convert.ToInt32(Session["CompanyId"]);
|
|
var list = DataNew.GetExpressPostList(CompanyId, LogisticsId);
|
|
return list;
|
|
}
|
|
#endregion
|
|
|
|
#region 获取赔款
|
|
[WebMethod(EnableSession = true)]
|
|
public DT_OrderBackMoneyApply GetOrderBackMoneyModel2(int OrderId)
|
|
{
|
|
Pages.Login(this.Session);
|
|
DD_OrderData obj = new DD_OrderData();
|
|
|
|
|
|
var md = DataNew.GetOrderBackMoneyModel2(OrderId);
|
|
|
|
if (md != null && md.ImageIds != null && md.ImageIds != "")
|
|
{
|
|
var list = DataNew.GetListJC_Resource(md.ImageIds.Trim(','));
|
|
md.imglist = list;
|
|
}
|
|
if (md != null)
|
|
{
|
|
if (md.CompanyDate1 != null)
|
|
{
|
|
string remark = "";
|
|
if (md.CompanyAgree1 != null && md.CompanyAgree1 != "")
|
|
remark = md.CompanyAgree1;
|
|
if (md.CompanyRemark != null && md.CompanyRemark != "")
|
|
remark += md.CompanyRemark;
|
|
|
|
md.CompanyContent = remark + "【" + md.CompanyName1 + " " + md.CompanyDate1.Value.ToString("yyyy-MM-dd HH:mm") + "】";
|
|
|
|
}
|
|
if (md.CompanyDate2 != null)
|
|
{
|
|
string remark2 = "";
|
|
if (md.CompanyAgree2 != null && md.CompanyAgree2 != "")
|
|
remark2 = md.CompanyAgree2;
|
|
if (md.CompanyRemark2 != null && md.CompanyRemark2 != "")
|
|
remark2 += md.CompanyRemark2;
|
|
|
|
md.CompanyContent += "<br/>" + remark2 + "【总经理" + md.CompanyDate2.Value.ToString("yyyy-MM-dd HH:mm") + "】";
|
|
|
|
}
|
|
}
|
|
return md;
|
|
|
|
}
|
|
|
|
#endregion
|
|
|
|
|
|
#region 报表40
|
|
[WebMethod(EnableSession = true)]
|
|
public string GetReportTable40(DateTime? SDate, DateTime? EDate, int UserId, string GoodsCode)
|
|
{
|
|
var obj = new BaseService();
|
|
int CompanyId = Convert.ToInt32(Session["CompanyId"]);
|
|
string tj = "";
|
|
string tj2 = "";
|
|
if (SDate != null)
|
|
{
|
|
tj += " and OrderDate>='" + SDate.Value.ToString("yyyy-MM-dd") + "' ";
|
|
// tj2 += " b.LastOrderDate>='" + SDate.Value.ToString("yyyy-MM-dd") + "' ";
|
|
}
|
|
if (EDate != null)
|
|
{
|
|
tj += " and OrderDate<'" + EDate.Value.AddDays(1).ToString("yyyy-MM-dd") + "' ";
|
|
// tj2 += " and b.LastOrderDate<'" + EDate.Value.AddDays(1).ToString("yyyy-MM-dd") + "' ";
|
|
}
|
|
|
|
if (UserId > 0)
|
|
{
|
|
tj2 += " and a.UserId=" + UserId + " ";
|
|
|
|
}
|
|
if (GoodsCode != "")
|
|
tj2 += " and e.GoodsCode='" + GoodsCode + "' ";
|
|
string sql = @"
|
|
select * into #DT_OrderInfoUser from DT_OrderInfo with(nolock) where CompanyId=1 and State>0 and State<3 " + tj + @"
|
|
|
|
select a.UserId,Num=COUNT(distinct a.GoodsId) into #DT_OrderInfoUser3 from HW_GoodsUser a with(nolock)
|
|
inner join HW_GoodsInfo b with(nolock) on a.GoodsId=b.GoodsId
|
|
inner join HW_GoodsDetail e with(nolock) on b.GoodsId=e.GoodsId
|
|
inner join DT_OrderGoods c with(nolock) on e.DetailId=c.DetailId
|
|
inner join #DT_OrderInfoUser d with(nolock) on c.OrderId=d.OrderId and a.ShopId=d.ShopId
|
|
where d.CompanyId=1 " + tj2 + @" group by a.UserId
|
|
|
|
|
|
select b.UserId,b.Num,Price=dbo.MoneyUSDChange(a.TotalPrice,a.MoneyCode,a.CompanyId) into #DT_OrderInfoUser2 from #DT_OrderInfoUser a inner join (
|
|
select a.UserId,d.OrderId,Num=SUM(c.GoodsNum) from HW_GoodsUser a with(nolock)
|
|
inner join HW_GoodsInfo b with(nolock) on a.GoodsId=b.GoodsId
|
|
inner join HW_GoodsDetail e with(nolock) on b.GoodsId=e.GoodsId
|
|
inner join DT_OrderGoods c with(nolock) on e.DetailId=c.DetailId
|
|
inner join #DT_OrderInfoUser d on c.OrderId=d.OrderId and a.ShopId=d.ShopId
|
|
where d.CompanyId=1 " + tj2 + @"
|
|
group by a.UserId,d.OrderId)b on a.OrderId=b.OrderId
|
|
|
|
|
|
select c.Name,c.UserName,a.*,GoodsNum=d.Num,OutGoods=isnull(e.Num,0),Rate=case when d.Num>0 then isnull(e.Num,0)*1.0/d.Num else 0 end,NoGoods=d.Num-isnull(e.Num,0) from (
|
|
select UserId,Num=sum(Num),OrderNum=COUNT(0),Price=SUM(Price) from #DT_OrderInfoUser2
|
|
group by UserId)a
|
|
inner join JC_UserInfo c on a.UserId=c.UserId
|
|
inner join (select UserId,Num=COUNT(distinct GoodsId) from HW_GoodsUser with(nolock)
|
|
group by UserId)d on c.UserId=d.UserId
|
|
left join #DT_OrderInfoUser3 e on c.UserId=e.UserId
|
|
|
|
drop table #DT_OrderInfoUser, #DT_OrderInfoUser2, #DT_OrderInfoUser3
|
|
";
|
|
DataTable dt = obj.GetReportData(sql);
|
|
|
|
return GetReport2(dt, 40);
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region 报表
|
|
|
|
public string GetReport2(DataTable dt, int ReportId)
|
|
{
|
|
PagesNew.Login(this.Session);
|
|
var obj = new BaseService();
|
|
var rmd = BaseService.GetReportModel(ReportId);
|
|
var rlist = obj.GetRepoartColsList(ReportId);
|
|
if (rmd == null || rlist == null)
|
|
return "";
|
|
|
|
StringBuilder html = new StringBuilder();
|
|
html.Append("<tr class='HeaderStyle'><th>序号</th>");
|
|
foreach (var md in rlist)
|
|
{
|
|
html.Append("<th>" + md.ColName + "</th>");
|
|
|
|
}
|
|
html.Append("</tr>");
|
|
if (dt != null)
|
|
{
|
|
DataRow row = null;
|
|
|
|
|
|
for (int i = 0; i < dt.Rows.Count; i++)
|
|
{
|
|
html.Append("<tr class='DataGridRowStyle'><td>" + (i + 1).ToString() + "</td>");
|
|
int rowindex = 1;
|
|
foreach (var md in rlist)
|
|
{
|
|
if (dt.Columns.Contains(md.ColValue) == false)
|
|
{
|
|
html.Append("<td rowindex='" + rowindex + "' rowtr='" + i + "'></td>");
|
|
rowindex++;
|
|
continue;
|
|
}
|
|
if (md.IsSum == 1 && i == 0)
|
|
{
|
|
if (row == null)
|
|
row = dt.NewRow();
|
|
if (md.ColType == "int")
|
|
{
|
|
try
|
|
{
|
|
row[md.ColValue] = Convert.ToInt32(dt.Compute("sum(" + md.ColValue + ")", "" + md.ColValue + " is not null"));
|
|
}
|
|
catch
|
|
{
|
|
row[md.ColValue] = 0;
|
|
|
|
}
|
|
|
|
}
|
|
else if (md.ColType == "dec")
|
|
{
|
|
try
|
|
{
|
|
row[md.ColValue] = Convert.ToDecimal(dt.Compute("sum(" + md.ColValue + ")", "" + md.ColValue + ">0"));
|
|
}
|
|
catch
|
|
{
|
|
row[md.ColValue] = 0;
|
|
|
|
}
|
|
|
|
|
|
}
|
|
}
|
|
if (dt.Rows[i][md.ColValue] != null && dt.Rows[i][md.ColValue].ToString() != "")
|
|
{
|
|
string csscolor = "";
|
|
if (md.ContionType == "=" && dt.Rows[i][md.ColValue].ToString() == md.SValue)
|
|
{
|
|
csscolor = md.ColColor;
|
|
}
|
|
else
|
|
if (md.ContionType == ">" && Convert.ToDecimal(dt.Rows[i][md.ColValue]) > Convert.ToDecimal(md.SValue) && Convert.ToDecimal(dt.Rows[i][md.ColValue]) < Convert.ToDecimal(md.EValue))
|
|
{
|
|
csscolor = md.ColColor;
|
|
}
|
|
else
|
|
if (md.ContionType == ">=" && Convert.ToDecimal(dt.Rows[i][md.ColValue]) >= Convert.ToDecimal(md.SValue) && Convert.ToDecimal(dt.Rows[i][md.ColValue]) <= Convert.ToDecimal(md.EValue))
|
|
{
|
|
csscolor = md.ColColor;
|
|
}
|
|
if (csscolor != "")
|
|
csscolor = "style='color:" + csscolor + "'";
|
|
string datav = "";
|
|
if (md.ColType == "date1")
|
|
datav = Convert.ToDateTime(dt.Rows[i][md.ColValue]).ToString("yyyy-MM-dd");
|
|
else
|
|
if (md.ColType == "date2")
|
|
datav = Convert.ToDateTime(dt.Rows[i][md.ColValue]).ToString("yyyy-MM-dd HH:mm");
|
|
else if (md.ColType == "dec")
|
|
{
|
|
datav = Convert.ToDecimal(dt.Rows[i][md.ColValue]).ToString("0.00");
|
|
|
|
}
|
|
else
|
|
datav = dt.Rows[i][md.ColValue].ToString();
|
|
html.Append("<td " + csscolor + " rowindex='" + rowindex + "' rowtr='" + i + "'><a uid='" + dt.Rows[i]["UserId"].ToString() + "'>" + datav + "<a></td>");
|
|
}
|
|
else
|
|
html.Append("<td rowindex='" + rowindex + "' rowtr='" + i + "'></td>");
|
|
rowindex++;
|
|
}
|
|
html.Append("</tr>");
|
|
|
|
}
|
|
int hj = 0;
|
|
if (row != null)
|
|
{
|
|
html.Append("<tr class='DataGridRowStyle'><td>合计：</td>");
|
|
foreach (var md in rlist)
|
|
{
|
|
if (dt.Columns.Contains(md.ColValue) == false)
|
|
{
|
|
html.Append("<td></td>");
|
|
continue;
|
|
}
|
|
if (md.IsSum == 1 && row[md.ColValue] != null)
|
|
{
|
|
if (hj == 0)
|
|
{
|
|
hj = 1;
|
|
html.Append("<td>" + row[md.ColValue] + "</td>");
|
|
}
|
|
else
|
|
html.Append("<td>" + row[md.ColValue] + "</td>");
|
|
}
|
|
else
|
|
html.Append("<td></td>");
|
|
}
|
|
html.Append("</tr>");
|
|
}
|
|
|
|
|
|
}
|
|
return html.ToString();
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 报表18 每日利润
|
|
|
|
#region GetReportTable18
|
|
/// <summary>
|
|
/// 报表18 每日利润 只展示自身的 最新需求
|
|
/// </summary>
|
|
/// <param name="InDate"></param>
|
|
/// <param name="DeptId"></param>
|
|
/// <param name="ShopId"></param>
|
|
/// <param name="PlatId"></param>
|
|
/// <param name="MoneyCode"></param>
|
|
/// <param name="Sort"></param>
|
|
/// <param name="isyc"></param>
|
|
/// <returns></returns>
|
|
[WebMethod(EnableSession = true)]
|
|
public string GetReportTable18Old(DateTime? InDate, int DeptId, int ShopId, int PlatId, string MoneyCode, string Sort, bool isyc)
|
|
{
|
|
string dd = "";
|
|
try
|
|
{
|
|
//测试数据
|
|
//InDate = DateTime.Parse("2024-07-14");
|
|
DateTime newInDate = new DateTime(InDate.Value.Year, InDate.Value.Month, 1); ;
|
|
|
|
//判断登录者
|
|
|
|
|
|
|
|
var InUserId = Convert.ToInt32(Session["UserId"]);
|
|
InUserId =243;
|
|
string selectUserIdFirst = "";
|
|
string selectUserId = "";
|
|
string selectUserId1 = "";
|
|
string selectUserId2 = "";
|
|
string selectUserId3 = " ";
|
|
string selectUserId31 = " ";
|
|
string selectUserId32 = " ";
|
|
string selectShopId1 = "";
|
|
string selectShopId2 = "";
|
|
string selectgk1 = "";
|
|
string selectgk2 = "";
|
|
string selectgk3 = "";
|
|
string selectgk4 = "";
|
|
if (InUserId != 1)
|
|
{
|
|
selectUserIdFirst = "DECLARE @skuUserId INT = " + InUserId + ";\r\n";
|
|
//selectUserIdFirst = "DECLARE @skuUserId INT = 190;\r\n";
|
|
selectUserId = " where \r\n(a.guserid_shopify = @skuUserId or\r\na.guserid_wayfair= @skuUserId or\r\na.guserid_sheIn = @skuUserId or\r\na.guserid_ebay = @skuUserId or\r\na.guserid_wlmart = @skuUserId or\r\na.muserid = @skuUserId or \r\na.guserid = @skuUserId)";
|
|
selectUserId1 = " \r\n left join DT_OrderGoods d with(nolock) on a.OrderId=d.OrderId\r\nleft join HW_GoodsDetail e with(nolock) on e.DetailId=d.DetailId\r\nleft JOIN HW_GoodsInfo f with(nolock) on e.GoodsId=f.GoodsId \r\n";
|
|
selectUserId2 = " and \r\n(f.guserid_shopify = @skuUserId or\r\nf.guserid_wayfair= @skuUserId or\r\nf.guserid_sheIn = @skuUserId or\r\nf.guserid_ebay = @skuUserId or\r\nf.guserid_wlmart = @skuUserId or\r\nf.muserid = @skuUserId or \r\nf.guserid = @skuUserId) \r\n";
|
|
selectUserId3 = " select DISTINCT a.ShopId into #ls3 from DT_OrderInfo a with(nolock)\r\ninner join DT_OrderXXInfo b with(nolock) on a.OrderId=b.OrderId\r\nleft join DT_Fees c with(nolock) on a.OrderId=c.orderid\r\nleft join DT_OrderGoods d with(nolock) on a.OrderId=d.OrderId\r\nleft join HW_GoodsDetail e with(nolock) on e.DetailId=d.DetailId\r\nleft JOIN HW_GoodsInfo f with(nolock) on e.GoodsId=f.GoodsId\r\nwhere DATEDIFF(day,a.OrderDate,'" + InDate.Value.ToString("yyyy-MM-dd") + "')=0 and a.State>0 and a.State<3 and \r\n(f.guserid_shopify = @skuUserId or\r\nf.guserid_wayfair= @skuUserId or\r\nf.guserid_sheIn = @skuUserId or\r\nf.guserid_ebay = @skuUserId or\r\nf.guserid_wlmart = @skuUserId or\r\nf.muserid = @skuUserId or \r\nf.guserid = @skuUserId) \r\n ";
|
|
selectUserId31 = "INNER JOIN #ls3 ff on d.ShopId=ff.ShopId\r\n";
|
|
selectUserId32 = "INNER JOIN #ls3 ff on a.ShopId=ff.ShopId\r\n";
|
|
selectShopId1 = " and a.ShopId in (SELECT ShopId\r\nFROM JC_Shop\r\nWHERE ShopUser LIKE '%' + (\r\n SELECT TOP 1 Name\r\n FROM JC_UserInfo\r\n WHERE UserID = @skuUserId\r\n) + '%');\r\n";
|
|
selectShopId2 = @"and b.ShopId in (SELECT ShopId
|
|
FROM JC_Shop
|
|
WHERE ShopUser LIKE '%' + (
|
|
SELECT TOP 1 Name
|
|
FROM JC_UserInfo
|
|
WHERE UserID = @skuUserId
|
|
) +'%')";
|
|
//日广告
|
|
selectgk1 = "select a.shopid,\r\n SUM(ABS(isnull(adfee, 0))) Rate3 into #ls4\r\n from\r\n AD_FeeList a\r\n\t\t\t\t inner join (\r\n\t\t\t\t select DISTINCT a.ShopId, f.GoodsId from DT_OrderInfo a with(nolock)\r\n\r\nleft join DT_Fees c with(nolock) on a.OrderId=c.orderid\r\nleft join DT_OrderGoods d with(nolock) on a.OrderId=d.OrderId\r\nleft join HW_GoodsDetail e with(nolock) on e.DetailId=d.DetailId\r\nleft JOIN HW_GoodsInfo f with(nolock) on e.GoodsId=f.GoodsId\r\nwhere DATEDIFF(day,a.OrderDate,'" + InDate.Value.ToString("yyyy-MM-dd") + "')=0 and a.State>0 and a.State<3 and \r\n(f.guserid_shopify = @skuUserId or\r\nf.guserid_wayfair= @skuUserId or\r\nf.guserid_sheIn = @skuUserId or\r\nf.guserid_ebay = @skuUserId or\r\nf.guserid_wlmart = @skuUserId or\r\nf.muserid = @skuUserId or \r\nf.guserid = @skuUserId) and a.ShopId in (SELECT ShopId\r\nFROM JC_Shop\r\nWHERE ShopUser LIKE '%' + (\r\n SELECT TOP 1 Name\r\n FROM JC_UserInfo\r\n WHERE UserID = @skuUserId\r\n) + '%'))b on a.GoodsId = b.GoodsId and a.shopid = b.shopid where addate = '" + InDate.Value.ToString("yyyy-MM-dd") + "' group by\r\n \r\n a.shopid \r\n ";
|
|
//月广告
|
|
selectgk2 = "select a.shopid,\r\n SUM(ABS(isnull(adfee, 0))) Rate6 into #ls5\r\n from\r\n AD_FeeList a\r\n\t\t\t\t inner join (\r\n\t\t\t\t select DISTINCT a.ShopId, f.GoodsId from DT_OrderInfo a with(nolock)\r\n\r\nleft join DT_Fees c with(nolock) on a.OrderId=c.orderid\r\nleft join DT_OrderGoods d with(nolock) on a.OrderId=d.OrderId\r\nleft join HW_GoodsDetail e with(nolock) on e.DetailId=d.DetailId\r\nleft JOIN HW_GoodsInfo f with(nolock) on e.GoodsId=f.GoodsId\r\nwhere DATEDIFF(day,a.OrderDate,'" + InDate.Value.ToString("yyyy-MM-dd") + "')=0 and a.State>0 and a.State<3 and \r\n(f.guserid_shopify = @skuUserId or\r\nf.guserid_wayfair= @skuUserId or\r\nf.guserid_sheIn = @skuUserId or\r\nf.guserid_ebay = @skuUserId or\r\nf.guserid_wlmart = @skuUserId or\r\nf.muserid = @skuUserId or \r\nf.guserid = @skuUserId) and a.ShopId in (SELECT ShopId\r\nFROM JC_Shop\r\nWHERE ShopUser LIKE '%' + (\r\n SELECT TOP 1 Name\r\n FROM JC_UserInfo\r\n WHERE UserID = @skuUserId\r\n) + '%'))b on a.GoodsId = b.GoodsId and a.shopid = b.shopid where addate >= '" + newInDate.ToString("yyyy-MM-dd") + "' and addate <= '" + InDate.Value.ToString("yyyy-MM-dd") + "'\r\n group by\r\n \r\n a.shopid \r\n ";
|
|
selectgk3 = @"select a.ShopId,SUM(a1.AdFee) as Rate3 into #ls6 from DT_OrderInfo a with(nolock)
|
|
left join DT_OrderGoods d with(nolock) on a.OrderId = d.OrderId
|
|
left join HW_GoodsDetail e with(nolock) on e.DetailId = d.DetailId
|
|
left JOIN HW_GoodsInfo f with(nolock) on e.GoodsId = f.GoodsId
|
|
left JOIN ShopGoodsAdFee a1 with(nolock) on a1.GoodsId = e.GoodsId and a1.ShopId = a.ShopId
|
|
where DATEDIFF(day, a.OrderDate,'" + InDate.Value.ToString("yyyy-MM-dd") + @"')= 0 and a.State > 0 and a.State < 3 and
|
|
(f.guserid_shopify = @skuUserId or
|
|
f.guserid_wayfair = @skuUserId or
|
|
f.guserid_sheIn = @skuUserId or
|
|
f.guserid_ebay = @skuUserId or
|
|
f.guserid_wlmart = @skuUserId or
|
|
f.muserid = @skuUserId or
|
|
f.guserid = @skuUserId) and a.ShopId in (SELECT ShopId
|
|
FROM JC_Shop
|
|
WHERE ShopUser LIKE '%' + (
|
|
SELECT TOP 1 Name
|
|
FROM JC_UserInfo
|
|
WHERE UserID = @skuUserId
|
|
) +'%') and CONVERT(date, a1.AdDate)= '" + InDate.Value.ToString("yyyy-MM-dd") + @"'
|
|
group by a.ShopId
|
|
";
|
|
selectgk4 = @"select a.ShopId,SUM(a1.AdFee) as Rate6 into #ls7 from DT_OrderInfo a with(nolock)
|
|
left join DT_OrderGoods d with(nolock) on a.OrderId = d.OrderId
|
|
left join HW_GoodsDetail e with(nolock) on e.DetailId = d.DetailId
|
|
left JOIN HW_GoodsInfo f with(nolock) on e.GoodsId = f.GoodsId
|
|
left JOIN ShopGoodsAdFee a1 with(nolock) on a1.GoodsId = e.GoodsId and a1.ShopId = a.ShopId
|
|
where DATEDIFF(day, a.OrderDate,'" + InDate.Value.ToString("yyyy-MM-dd") + @"')= 0 and a.State > 0 and a.State < 3 and
|
|
(f.guserid_shopify = @skuUserId or
|
|
f.guserid_wayfair = @skuUserId or
|
|
f.guserid_sheIn = @skuUserId or
|
|
f.guserid_ebay = @skuUserId or
|
|
f.guserid_wlmart = @skuUserId or
|
|
f.muserid = @skuUserId or
|
|
f.guserid = @skuUserId) and a.ShopId in (SELECT ShopId
|
|
FROM JC_Shop
|
|
WHERE ShopUser LIKE '%' + (
|
|
SELECT TOP 1 Name
|
|
FROM JC_UserInfo
|
|
WHERE UserID = @skuUserId
|
|
) +'%') and CONVERT(date, a1.AdDate)>= '" + newInDate.ToString("yyyy-MM-dd") + @"' and CONVERT(date, a1.AdDate)<= '" + InDate.Value.ToString("yyyy-MM-dd") + @"'
|
|
group by a.ShopId
|
|
";
|
|
}
|
|
var obj = new BaseService();
|
|
|
|
string tj = "";
|
|
string tj1 = "";
|
|
if (PlatId > 0)
|
|
{
|
|
tj += " and a.PlatId=" + PlatId + " ";
|
|
tj1 += " and a.PlatId=" + PlatId + " ";
|
|
}
|
|
|
|
if (ShopId > 0)
|
|
{
|
|
tj += " and a.ShopId=" + ShopId + " ";
|
|
tj1 += " and a.ShopId=" + ShopId + " ";
|
|
}
|
|
|
|
string sql = selectUserIdFirst + @"
|
|
declare @moneyrate decimal(18,4)
|
|
select @moneyrate=MRate from JC_Money where MCode='USD' and PlatId=2
|
|
select a.ShopId,a.TotalPrice,escrowFee=isnull(a.escrowFee,0),a.RMBPrice,c.yj,yf=c.yf,cb=c.cb,hwsf=c.hwsf,c.tc,c.ck,c.md,a.Ad_Fee,a.DR_BackFee,a.DR_PostFee,a.DR_SSFee,a.Other_Fee,moneyrate=isnull(c.moneyrate,@moneyrate),ISnull( c.SubsidyFee,0) SubsidyFee into #ls from DT_OrderInfo a with(nolock)
|
|
inner join DT_OrderXXInfo b with(nolock) on a.OrderId=b.OrderId
|
|
left join DT_Fees c with(nolock) on a.OrderId=c.orderid " + selectUserId1 + @"
|
|
where DATEDIFF(day,a.OrderDate,'" + InDate.Value.ToString("yyyy-MM-dd") + @"')=0 " + tj + @" and a.State>0 and a.State<3 " + selectUserId2 + selectShopId1 + @"
|
|
select a.*,Num2=isnull(b.Num2,0) into #ls2 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 " + selectUserId + selectShopId2 + @"
|
|
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 f with(nolock) on f.GoodsId = c.GoodsId
|
|
where a.State>0 and a.State<3 and DATEDIFF(day,a.OrderDate,'" + InDate.Value.ToString("yyyy-MM-dd") + @"')=0 and
|
|
(
|
|
f.guserid_shopify = @skuUserId
|
|
or f.guserid_wayfair = @skuUserId
|
|
or f.guserid_sheIn = @skuUserId
|
|
or f.guserid_ebay = @skuUserId
|
|
or f.guserid_wlmart = @skuUserId
|
|
or f.muserid = @skuUserId
|
|
or f.guserid = @skuUserId
|
|
)
|
|
and a.ShopId in ( SELECT
|
|
ShopId
|
|
FROM
|
|
JC_Shop
|
|
WHERE
|
|
ShopUser LIKE '%' + (
|
|
SELECT
|
|
TOP 1 Name
|
|
FROM
|
|
JC_UserInfo
|
|
WHERE
|
|
UserID = @skuUserId
|
|
) + '%'
|
|
) group by a.ShopId)b on a.ShopId=b.ShopId
|
|
" + selectUserId3 + selectShopId1 + selectgk1 + selectgk2 + selectgk3 + selectgk4 + @"
|
|
select * from (
|
|
select d.ShopName,a.OrderNum,a.RMBMoney,a.USDMoney,AvgPrice=case when a.OrderNum>0 then cast(a.USDMoney/a.OrderNum as decimal(18,2)) else 0 end,a.InPrice,LR=cast(b.ml as decimal(18,2)),MLL=case when b.TotalPrice>0 then cast(b.ml*100/b.TotalPrice as decimal(18,2)) else 0 end,ShopTJGoods=e.Num,ShopCDGoods=e.Num2,ckfee,a.mdfee,a.adfee,bz=case when a.InPrice>0 then CAST(a.RMBMoney/a.InPrice as decimal(18,2)) else 0 end,a.yf,c.GoodRate,c.ODR,t3.CVR DSRService,t3.user_sessions DSRGoods,c.USDUseMoney, n1.originalTotalCurrencyAmount as USDNoMoney,c.RMBUseMoney,c.RMBNoMoney,t2.orderDefectRate Rate1,t2.validTrackingRate Rate2,Case when d.PlatType=2 then f.Rate3 else c.Rate3 end Rate3,t4.RefundRate as Rate4,0 as PropertyViolate,t2.cancelRate Rate5,Case when d.PlatType=2 then g.Rate6 else (select SUM(ISNULL(Rate3,0)) from JC_ShopData where ShopId=d.ShopId and DATEDIFF(MONTH,InDate,'2023-10-18')=0) end Rate6,Rate7=isnull(c.Rate7,d.dayfee),JL=cast(b.ml as decimal(18,2))+isnull(ckfee,0)-isnull(f.Rate3,0) from
|
|
JC_Shop d left join (
|
|
select ShopId,OrderNum=COUNT(0),RMBMoney=SUM(RMBPrice),escrowFee=sum(escrowFee),USDMoney=SUM(TotalPrice),InPrice=SUM(cb),ckfee=SUM(ck),mdfee=SUM(md),adfee=SUM(Ad_Fee),yf=SUM(yf) from #ls
|
|
group by ShopId)a on d.ShopId=a.ShopId
|
|
left join (select ShopId,TotalPrice=sum(TotalPrice),ml=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 #ls where yf>0
|
|
group by ShopId)b on a.ShopId=b.ShopId
|
|
left join (
|
|
select GoodRate,ODR,DSRService,DSRGoods,DSRPost,USDUseMoney,USDNoMoney,RMBUseMoney, RMBNoMoney,Rate1,Rate2,Rate3,Rate4,PropertyViolate,Rate5,Rate6,Rate7,FeeDate,TXFee
|
|
TXFee,ShopId from JC_ShopData
|
|
where DATEDIFF(day,indate,'" + InDate.Value.ToString("yyyy-MM-dd") + @"')=0 )c on a.ShopId=c.ShopId
|
|
left Join #ls2 e on a.ShopId=e.ShopId
|
|
left join [192.168.1.187].[UpGoods].dbo.LxERP_Amazon_Shop o on d.ShopName=o.name
|
|
left join #ls4 f on f.ShopId = a.ShopId" +
|
|
" \r\n left join #ls5 g on a.ShopId = g.ShopId " +
|
|
" \r\n left join (select storename,CONVERT(decimal(12,2), SUM(ISNULL(originalTotalCurrencyAmount,0))) originalTotalCurrencyAmount from [192.168.1.187].[UpGoods].dbo.LxERP_SettlementSummaryData where processingStatus='Open' and accountType='Standard' group by storename) n1 on d.ShopName=n1.storeName " +
|
|
"\r\n" +
|
|
" left join (select sid, CAST( orderDefectRate *100 as decimal(12,2)) orderDefectRate,CAST( validTrackingRate *100 as decimal(12,2)) validTrackingRate,cast(CancelRate *100 as decimal(12,2)) cancelRate from (\r\nselect ROW_NUMBER() over(PARTITION BY sid ORDER BY requestDate DESC) row,sid, orderDefectRate,validTrackingRate,CancelRate from [192.168.1.187].[UpGoods].dbo.Amazon_V2_SELLER_PERFORMANCE_REPORT where CONVERT(date, RequestDate)='" + InDate.Value.AddDays(-1).Date.ToString("yyyy-MM-dd") + "') t where t.row=1)t2 on t2.sid=o.sid \r\nleft join (select sid, user_sessions,totalOrderItems, case when user_sessions=0 or totalOrderItems=0 then 0 else convert(decimal(12,2),(totalOrderItems*1.00/user_sessions)*100 ) end as CVR from [192.168.1.187].[UpGoods].dbo.Amazon_SALES_AND_TRAFFIC_REPORT where CONVERT(date, ReportDate)='" + InDate.Value.AddDays(-2).Date.ToString("yyyy-MM-dd") + "') t3 on t3.sid=o.sid\r\nleft join [192.168.1.187].[UpGoods].dbo.AmazonStoreRefundAmountAndRate t4 on t4.sid=o.sid and Convert(date, t4.ReportDate)='" + InDate.Value.AddDays(-2).Date.ToString("yyyy-MM-dd") + "'\r\n"
|
|
+ selectUserId31 + " where d.PlatType=2 union all\r\n select d.ShopName,a.OrderNum,a.RMBMoney,a.USDMoney,AvgPrice=case when a.OrderNum>0 then cast(a.USDMoney/a.OrderNum as decimal(18,2)) else 0 end,a.InPrice,LR=cast(b.ml as decimal(18,2)),MLL=case when b.TotalPrice>0 then cast(b.ml*100/b.TotalPrice as decimal(18,2)) else 0 end,ShopTJGoods=e.Num,ShopCDGoods=e.Num2,ckfee,a.mdfee,a.adfee,bz=case when a.InPrice>0 then CAST(a.RMBMoney/a.InPrice as decimal(18,2)) else 0 end,a.yf,c.GoodRate,c.ODR,c.DSRService,c.DSRGoods,c.USDUseMoney,c.USDNoMoney,c.RMBUseMoney,c.RMBNoMoney,c.Rate1,c.Rate2,newRate1.Rate3,c.Rate4,0 as PropertyViolate,c.Rate5,newRate2.Rate6,Rate7=isnull(c.Rate7,d.dayfee),JL=cast(b.ml as decimal(18,2))+isnull(ckfee,0)-isnull(newRate1.Rate3,0) from \r\nJC_Shop d left join (\r\nselect ShopId,OrderNum=COUNT(0),RMBMoney=SUM(RMBPrice),escrowFee=sum(escrowFee),USDMoney=SUM(TotalPrice),InPrice=SUM(cb),ckfee=SUM(ck),mdfee=SUM(md),adfee=SUM(Ad_Fee),yf=SUM(yf) from #ls\r\ngroup by ShopId)a on d.ShopId=a.ShopId\r\nleft join (select ShopId,TotalPrice=sum(TotalPrice),ml=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 #ls where yf>0\r\ngroup by ShopId)b on a.ShopId=b.ShopId\r\nleft join (\r\nselect GoodRate,ODR,DSRService,DSRGoods,DSRPost,USDUseMoney,USDNoMoney,RMBUseMoney, RMBNoMoney,Rate1,Rate2,Rate3,Rate4,PropertyViolate,Rate5,Rate6,Rate7,FeeDate,TXFee\r\nTXFee,ShopId from JC_ShopData\r\nwhere DATEDIFF(day,indate,'" + InDate.Value.ToString("yyyy-MM-dd") + "')=0 )c on a.ShopId=c.ShopId\r\nleft Join #ls6 newRate1 on a.ShopId=newRate1.ShopId\r\nleft Join #ls7 newRate2 on a.ShopId=newRate2.ShopId\r\n left Join #ls2 e on a.ShopId=e.ShopId " + selectUserId32 + "where d.PlatType!=2 " + (isyc ? " and d.Master!='东南亚专用'" : "") + "\r\n) a order by " + Sort;
|
|
|
|
DataTable dt = obj.GetReportData(sql);
|
|
|
|
|
|
|
|
return GetReport3(dt, 181);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
string a = dd;
|
|
return "";
|
|
|
|
}
|
|
}
|
|
#endregion
|
|
#region GetReportTable18New
|
|
/// <summary>
|
|
/// 报表18 每日利润 只展示自身的 最新需求
|
|
/// </summary>
|
|
/// <param name="InDate"></param>
|
|
/// <param name="DeptId"></param>
|
|
/// <param name="ShopId"></param>
|
|
/// <param name="PlatId"></param>
|
|
/// <param name="MoneyCode"></param>
|
|
/// <param name="Sort"></param>
|
|
/// <param name="isyc"></param>
|
|
/// <returns></returns>
|
|
[WebMethod(EnableSession = true)]
|
|
public string GetReportTable18(DateTime? InDate)
|
|
{
|
|
string dd = "";
|
|
try
|
|
{
|
|
//int UserId = 243;
|
|
var UserId = Convert.ToInt32(Session["UserId"]);
|
|
var list = DataNew.MRJL_GetUserOderMsgByDay((DateTime)InDate, UserId);
|
|
var goodsList = DataNew.MRJL_GetGoodsIdByDay((DateTime)InDate, UserId);
|
|
|
|
if (list != null && list.Count() > 0)
|
|
{
|
|
foreach (var l in list)
|
|
{
|
|
//名称
|
|
l.ShopName = DataNew.MRJL_GetShopName(l.ShopId);
|
|
//客单价
|
|
l.AvgPrice = Math.Round(l.USDMoney / l.OrderNum, 2);
|
|
|
|
if (goodsList != null && goodsList.Count() > 0)
|
|
{
|
|
//获取广告
|
|
var plstId = goodsList.Where(t => t.ShopId == l.ShopId).Select(t => t.PlatId).FirstOrDefault();
|
|
var goods = goodsList.Where(t => t.ShopId == l.ShopId).Select(t => t.GoodsID.ToString()).ToList();
|
|
|
|
var adfeeModel1 = DataNew.MRJL_GetGKFeeByDay((DateTime)InDate, plstId, l.ShopId, goods);
|
|
var adfeeModel2 = DataNew.MRJL_GetGKFeeByMouth((DateTime)InDate, plstId, l.ShopId, goods);
|
|
|
|
|
|
l.Rate3 = adfeeModel1 != null ? adfeeModel1.Adfee : 0;
|
|
l.Rate6 = adfeeModel2 != null ? adfeeModel2.Adfee : 0;
|
|
|
|
var goodsMsgModel = DataNew.MRJL_GetGoodsMsgByMouth((DateTime)InDate, plstId, l.ShopId, UserId);
|
|
l.ShopCDGoods = goodsMsgModel != null ? goodsMsgModel.Num2 : 0;
|
|
l.ShopTJGoods = goodsMsgModel != null ? goodsMsgModel.Num : 0;
|
|
//
|
|
|
|
}
|
|
|
|
//净利润
|
|
l.LR = Math.Round(l.LR, 2);
|
|
l.JL = l.LR + l.ckfee - l.Rate3;
|
|
}
|
|
}
|
|
|
|
var dt = DataNew.ListToDataTable<BB_MRJL_User>(list.OrderByDescending(t => t.OrderNum).ToList());
|
|
//var list = DataNew.GetUserOderMsgByDay((DateTime)InDate, UserId);
|
|
//DataTable dt = obj.GetReportData(sql);
|
|
return GetReport3(dt, 181);
|
|
//return "";
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
string a = dd;
|
|
return "";
|
|
|
|
}
|
|
}
|
|
#endregion
|
|
#region GetReportTable18_Old1
|
|
|
|
[WebMethod(EnableSession = true)]
|
|
public string GetReportTable18_Old1(DateTime? InDate, int DeptId, int ShopId, int PlatId, string MoneyCode, string Sort, bool isyc)
|
|
{
|
|
string dd = "";
|
|
try
|
|
{
|
|
//判断登录者
|
|
var InUserId = Convert.ToInt32(Session["UserId"]);
|
|
string selectUserIdFirst = "";
|
|
string selectUserId = "";
|
|
string selectUserId1 = "";
|
|
string selectUserId2 = "";
|
|
|
|
if (InUserId != 1)
|
|
{
|
|
selectUserIdFirst = "DECLARE @skuUserId INT = " + InUserId + ";\r\n";
|
|
selectUserId = " where \r\n(a.guserid_shopify = @skuUserId or\r\na.guserid_wayfair= @skuUserId or\r\na.guserid_sheIn = @skuUserId or\r\na.guserid_ebay = @skuUserId or\r\na.guserid_wlmart = @skuUserId or\r\na.muserid = @skuUserId or \r\na.guserid = @skuUserId)";
|
|
selectUserId1 = " \r\n left join DT_OrderGoods d with(nolock) on a.OrderId=d.OrderId\r\nleft join HW_GoodsDetail e with(nolock) on e.DetailId=d.DetailId\r\nleft JOIN HW_GoodsInfo f with(nolock) on e.GoodsId=f.GoodsId \r\n";
|
|
selectUserId2 = " and \r\n(f.guserid_shopify = @skuUserId or\r\nf.guserid_wayfair= @skuUserId or\r\nf.guserid_sheIn = @skuUserId or\r\nf.guserid_ebay = @skuUserId or\r\nf.guserid_wlmart = @skuUserId or\r\nf.muserid = @skuUserId or \r\nf.guserid = @skuUserId) \r\n";
|
|
|
|
}
|
|
var obj = new BaseService();
|
|
|
|
string tj = "";
|
|
string tj1 = "";
|
|
//if (DeptId > 0)
|
|
//{
|
|
// tj += " and b.DeptId=" + DeptId + " ";
|
|
// tj1 += " and a.DeptId=" + DeptId + " ";
|
|
//}
|
|
if (PlatId > 0)
|
|
{
|
|
tj += " and a.PlatId=" + PlatId + " ";
|
|
tj1 += " and a.PlatId=" + PlatId + " ";
|
|
}
|
|
|
|
if (ShopId > 0)
|
|
{
|
|
tj += " and a.ShopId=" + ShopId + " ";
|
|
tj1 += " and a.ShopId=" + ShopId + " ";
|
|
}
|
|
//if (MoneyCode != "0")
|
|
// tj += " and a.MoneyCode='" + MoneyCode + "' ";
|
|
// string sql = @"
|
|
//declare @moneyrate decimal(18,4)
|
|
//select @moneyrate=MRate from JC_Money where MCode='USD' and PlatId=2
|
|
//select a.ShopId,a.TotalPrice,escrowFee=isnull(a.escrowFee,0),a.RMBPrice,c.yj,yf=c.yf,cb=c.cb,hwsf=c.hwsf,c.tc,c.ck,c.md,a.Ad_Fee,a.DR_BackFee,a.DR_PostFee,a.DR_SSFee,a.Other_Fee,moneyrate=isnull(c.moneyrate,@moneyrate) into #ls from DT_OrderInfo a with(nolock)
|
|
//inner join DT_OrderXXInfo b with(nolock) on a.OrderId=b.OrderId
|
|
//left join DT_Fees c with(nolock) on a.OrderId=c.orderid
|
|
//where DATEDIFF(day,a.OrderDate,'" + InDate.Value.ToString("yyyy-MM-dd") + @"')=0 " + tj + @" and a.State>0 and a.State<3
|
|
//select a.*,Num2=isnull(b.Num2,0) into #ls2 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
|
|
//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
|
|
//where a.State>0 and a.State<3 and DATEDIFF(day,a.OrderDate,'" + InDate.Value.ToString("yyyy-MM-dd") + @"')=0 group by a.ShopId)b on a.ShopId=b.ShopId
|
|
|
|
//select d.ShopName,Master=case when d.Master=d.buyer then d.Master when d.buyer is not null and d.buyer<>'' then d.Master+'-'+d.buyer else d.Master end,a.OrderNum,a.RMBMoney,a.USDMoney,AvgPrice=case when a.OrderNum>0 then cast(a.USDMoney/a.OrderNum as decimal(18,2)) else 0 end,a.InPrice,LR=cast(b.ml as decimal(18,2)),MLL=case when b.TotalPrice>0 then cast(b.ml*100/b.TotalPrice as decimal(18,2)) else 0 end,ShopTJGoods=e.Num,ShopCDGoods=e.Num2,ckfee,a.mdfee,a.adfee,bz=case when a.InPrice>0 then CAST(a.RMBMoney/a.InPrice as decimal(18,2)) else 0 end,c.GoodRate,c.ODR,c.DSRService,c.DSRGoods,h.DSRPost,c.USDUseMoney,c.USDNoMoney,c.RMBUseMoney,c.RMBNoMoney,c.Rate1,c.Rate2,f.Rate3,c.Rate4,c.PropertyViolate,c.Rate5,g.Rate6,Rate7=isnull(c.Rate7,d.dayfee),JL=cast(b.ml as decimal(18,2))+isnull(ckfee,0)+isnull(mdfee,0)-isnull(c.Rate3,0)-isnull(c.Rate7,d.dayfee),FeeDate=isnull(c.FeeDate,d.FeeDate),TXFee=isnull(c.TXFee,d.TXFee) from
|
|
//JC_Shop d left join (
|
|
//select ShopId,OrderNum=COUNT(0),RMBMoney=SUM(RMBPrice),escrowFee=sum(escrowFee),USDMoney=SUM(TotalPrice),InPrice=SUM(cb),ckfee=SUM(ck),mdfee=SUM(md),adfee=SUM(Ad_Fee) from #ls
|
|
//group by ShopId)a on d.ShopId=a.ShopId
|
|
//left join (select ShopId,TotalPrice=sum(TotalPrice),ml=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)) from #ls where yf>0
|
|
//group by ShopId)b on a.ShopId=b.ShopId
|
|
//left join (
|
|
//select GoodRate,ODR,DSRService,DSRGoods,DSRPost,USDUseMoney,USDNoMoney,RMBUseMoney, RMBNoMoney,Rate1,Rate2,Rate3,Rate4,PropertyViolate,Rate5,Rate6,Rate7,FeeDate,TXFee
|
|
//TXFee,ShopId from JC_ShopData
|
|
//where DATEDIFF(day,indate,'" + InDate.Value.ToString("yyyy-MM-dd") + @"')=0 )c on a.ShopId=c.ShopId
|
|
//left Join #ls2 e on a.ShopId=e.ShopId
|
|
// left join (select shopid,SUM(ABS(adfee)) Rate3 from AD_FeeList where DATEDIFF(day,addate,'" + InDate.Value.AddDays(-2).ToString("yyyy-MM-dd") + "')=0 group by shopid) f on d.ShopId=f.shopid "+
|
|
// " left join ( select shopid,SUM(ABS(adfee)) Rate6 from AD_FeeList where CONVERT(varchar(7), addate, 120)='"+ InDate.Value.ToString("yyyy-MM") + "' group by shopid) g on d.ShopId=g.shopid "+
|
|
// " left join(select b.name shopName,case when a.Spend=0 or a.ad_sale_amount=0 then 0 else cast((Spend/ad_sale_amount)*100 as decimal(12,2)) end DSRPost from ( select sid,SUM(ISNULL(ABS(spend),0)) Spend, SUM(ISNULL(ad_sales_amount,0)) ad_sale_amount from [192.168.1.187].[UpGoods].dbo.LxERP_ProductPerformance where summary_field='Asin' and RequestDate='" + InDate.Value.AddDays(-2).ToString("yyyy-MM-dd") + "' group by sid) a left join [192.168.1.187].[UpGoods].dbo.LxERP_Amazon_Shop b on a.sid=b.sid) h on d.ShopName=h.shopName "
|
|
|
|
// + " order by " + Sort;
|
|
|
|
|
|
|
|
string sql = selectUserIdFirst + @"
|
|
declare @moneyrate decimal(18,4)
|
|
select @moneyrate=MRate from JC_Money where MCode='USD' and PlatId=2
|
|
select a.ShopId,a.TotalPrice,escrowFee=isnull(a.escrowFee,0),a.RMBPrice,c.yj,yf=c.yf,cb=c.cb,hwsf=c.hwsf,c.tc,c.ck,c.md,a.Ad_Fee,a.DR_BackFee,a.DR_PostFee,a.DR_SSFee,a.Other_Fee,moneyrate=isnull(c.moneyrate,@moneyrate),ISnull( c.SubsidyFee,0) SubsidyFee into #ls from DT_OrderInfo a with(nolock)
|
|
inner join DT_OrderXXInfo b with(nolock) on a.OrderId=b.OrderId
|
|
left join DT_Fees c with(nolock) on a.OrderId=c.orderid " + selectUserId1 + @"
|
|
where DATEDIFF(day,a.OrderDate,'" + InDate.Value.ToString("yyyy-MM-dd") + @"')=0 " + tj + @" and a.State>0 and a.State<3 " + selectUserId2 + @"
|
|
select a.*,Num2=isnull(b.Num2,0) into #ls2 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 " + selectUserId + @"
|
|
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
|
|
where a.State>0 and a.State<3 and DATEDIFF(day,a.OrderDate,'" + InDate.Value.ToString("yyyy-MM-dd") + @"')=0 group by a.ShopId)b on a.ShopId=b.ShopId
|
|
|
|
select * from (
|
|
select d.ShopName,Master=case when d.Master=d.buyer then d.Master when d.buyer is not null and d.buyer<>'' then d.Master+'-'+d.buyer else d.Master end,a.OrderNum,a.RMBMoney,a.USDMoney,AvgPrice=case when a.OrderNum>0 then cast(a.USDMoney/a.OrderNum as decimal(18,2)) else 0 end,a.InPrice,LR=cast(b.ml as decimal(18,2)),MLL=case when b.TotalPrice>0 then cast(b.ml*100/b.TotalPrice as decimal(18,2)) else 0 end,ShopTJGoods=e.Num,ShopCDGoods=e.Num2,ckfee,a.mdfee,a.adfee,bz=case when a.InPrice>0 then CAST(a.RMBMoney/a.InPrice as decimal(18,2)) else 0 end,a.yf,c.GoodRate,c.ODR,t3.CVR DSRService,t3.user_sessions DSRGoods,c.USDUseMoney, n1.originalTotalCurrencyAmount as USDNoMoney,c.RMBUseMoney,c.RMBNoMoney,t2.orderDefectRate Rate1,t2.validTrackingRate Rate2,Case when d.PlatType=2 then f.Rate3 else c.Rate3 end Rate3,t4.RefundRate as Rate4, ABS( ISNULL( t4.RefundAmount,0) ) as PropertyViolate,t2.cancelRate Rate5,Case when d.PlatType=2 then g.Rate6 else (select SUM(ISNULL(Rate3,0)) from JC_ShopData where ShopId=d.ShopId and DATEDIFF(MONTH,InDate,'2023-10-18')=0) end Rate6,Rate7=isnull(c.Rate7,d.dayfee),JL=cast(b.ml as decimal(18,2))+isnull(ckfee,0)-isnull(c.Rate3,0)-isnull(c.Rate7,d.dayfee),t1.financialEventGroupEndLocale as FeeDate,t1.originalTotalCurrencyAmount as TXFee from
|
|
JC_Shop d left join (
|
|
select ShopId,OrderNum=COUNT(0),RMBMoney=SUM(RMBPrice),escrowFee=sum(escrowFee),USDMoney=SUM(TotalPrice),InPrice=SUM(cb),ckfee=SUM(ck),mdfee=SUM(md),adfee=SUM(Ad_Fee),yf=SUM(yf) from #ls
|
|
group by ShopId)a on d.ShopId=a.ShopId
|
|
left join (select ShopId,TotalPrice=sum(TotalPrice),ml=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 #ls where yf>0
|
|
group by ShopId)b on a.ShopId=b.ShopId
|
|
left join (
|
|
select GoodRate,ODR,DSRService,DSRGoods,DSRPost,USDUseMoney,USDNoMoney,RMBUseMoney, RMBNoMoney,Rate1,Rate2,Rate3,Rate4,PropertyViolate,Rate5,Rate6,Rate7,FeeDate,TXFee
|
|
TXFee,ShopId from JC_ShopData
|
|
where DATEDIFF(day,indate,'" + InDate.Value.ToString("yyyy-MM-dd") + @"')=0 )c on a.ShopId=c.ShopId
|
|
left Join #ls2 e on a.ShopId=e.ShopId
|
|
left join [192.168.1.187].[UpGoods].dbo.LxERP_Amazon_Shop o on d.ShopName=o.name
|
|
left join ( select sid, CONVERT(decimal(12,2), SUM(t.cost)) as Rate3,CONVERT(decimal(12,2), SUM(t.sales)) as ad_sales , case when CONVERT(decimal(12,2), SUM(t.sales))<=0 then '∞' else CONVERT(nvarchar(20), convert(decimal(12,2), (CONVERT(decimal(12,2), SUM(t.cost))/CONVERT(decimal(12,2), SUM(t.sales)))*100)) end DSRPost from( select sid, SUM( ISNULL(cost,0)) as cost,SUM(ISNULL(sales,0))sales from [192.168.1.187].[UpGoods].dbo.LxERP_SPAdGroupReports where CONVERT(date,report_date)='" + InDate.Value.AddDays(-2).Date.ToString("yyyy-MM-dd") + "' group by sid union all select sid, SUM( ISNULL(cost,0)) as cost,SUM(ISNULL(sales,0))sales from [192.168.1.187].[UpGoods].dbo.LxERP_SBCampaignReports where CONVERT(date,report_date)='" + InDate.Value.AddDays(-2).Date.ToString("yyyy-MM-dd") + "' group by sid union all select sid, SUM( ISNULL(cost,0)) as cost,SUM(ISNULL(sales,0))sales from [192.168.1.187].[UpGoods].dbo.LxERP_SDAdGroupReports where CONVERT(date,report_date)='" + InDate.Value.AddDays(-2).Date.ToString("yyyy-MM-dd") + "' group by sid) t group by sid ) f on f.sid=o.sid" +
|
|
" \r\n left join (select sid, CONVERT(decimal(12,2), SUM(t.cost)) as Rate6 from( select sid, SUM( ISNULL(cost,0)) as cost from [192.168.1.187].[UpGoods].dbo.LxERP_SPAdGroupReports where CONVERT(date,report_date)<='" + InDate.Value.AddDays(-2).ToString("yyyy-MM-dd") + "' and CONVERT(date,report_date)>='" + InDate.Value.AddDays(-31).ToString("yyyy-MM-dd") + "' group by sid union all select sid, SUM( ISNULL(cost,0)) as cost from [192.168.1.187].[UpGoods].dbo.LxERP_SBCampaignReports where CONVERT(date,report_date)<='" + InDate.Value.AddDays(-2).ToString("yyyy-MM-dd") + "' and CONVERT(date,report_date)>='" + InDate.Value.AddDays(-31).ToString("yyyy-MM-dd") + "' group by sid union all select sid, SUM( ISNULL(cost,0)) as cost from [192.168.1.187].[UpGoods].dbo.LxERP_SDAdGroupReports where CONVERT(date,report_date)<='" + InDate.Value.AddDays(-2).ToString("yyyy-MM-dd") + "' and CONVERT(date,report_date)>='" + InDate.Value.AddDays(-31).ToString("yyyy-MM-dd") + "' group by sid) t group by sid) g on o.sid=g.sid " +
|
|
" \r\n left join(select t.row, t.storename,t.financialEventGroupEndLocale,t.originCurrencyIcon,CAST(ISNULL( t.originalTotalCurrencyAmount,0) as decimal(12,2)) as originalTotalCurrencyAmount from ( select ROW_NUMBER() over(PARTITION BY storename ORDER BY requestDate DESC) row,storename, financialEventGroupEndLocale,originCurrencyIcon,originalTotalCurrencyAmount from [192.168.1.187].[UpGoods].dbo.LxERP_SettlementSummaryData where accountType='Standard' and processingStatus='Closed ') t where t.row=1) t1 on d.ShopName=t1.storeName left join (select storename,CONVERT(decimal(12,2), SUM(ISNULL(originalTotalCurrencyAmount,0))) originalTotalCurrencyAmount from [192.168.1.187].[UpGoods].dbo.LxERP_SettlementSummaryData where processingStatus='Open' and accountType='Standard' group by storename) n1 on d.ShopName=n1.storeName " +
|
|
"\r\n" +
|
|
" left join (select sid, CAST( orderDefectRate *100 as decimal(12,2)) orderDefectRate,CAST( validTrackingRate *100 as decimal(12,2)) validTrackingRate,cast(CancelRate *100 as decimal(12,2)) cancelRate from (\r\nselect ROW_NUMBER() over(PARTITION BY sid ORDER BY requestDate DESC) row,sid, orderDefectRate,validTrackingRate,CancelRate from [192.168.1.187].[UpGoods].dbo.Amazon_V2_SELLER_PERFORMANCE_REPORT where CONVERT(date, RequestDate)='" + InDate.Value.AddDays(-1).Date.ToString("yyyy-MM-dd") + "') t where t.row=1)t2 on t2.sid=o.sid \r\nleft join (select sid, user_sessions,totalOrderItems, case when user_sessions=0 or totalOrderItems=0 then 0 else convert(decimal(12,2),(totalOrderItems*1.00/user_sessions)*100 ) end as CVR from [192.168.1.187].[UpGoods].dbo.Amazon_SALES_AND_TRAFFIC_REPORT where CONVERT(date, ReportDate)='" + InDate.Value.AddDays(-2).Date.ToString("yyyy-MM-dd") + "') t3 on t3.sid=o.sid\r\nleft join [192.168.1.187].[UpGoods].dbo.AmazonStoreRefundAmountAndRate t4 on t4.sid=o.sid and Convert(date, t4.ReportDate)='" + InDate.Value.AddDays(-2).Date.ToString("yyyy-MM-dd") + "'\r\n where d.PlatType=2 union all\r\n select d.ShopName,Master=case when d.Master=d.buyer then d.Master when d.buyer is not null and d.buyer<>'' then d.Master+'-'+d.buyer else d.Master end,a.OrderNum,a.RMBMoney,a.USDMoney,AvgPrice=case when a.OrderNum>0 then cast(a.USDMoney/a.OrderNum as decimal(18,2)) else 0 end,a.InPrice,LR=cast(b.ml as decimal(18,2)),MLL=case when b.TotalPrice>0 then cast(b.ml*100/b.TotalPrice as decimal(18,2)) else 0 end,ShopTJGoods=e.Num,ShopCDGoods=e.Num2,ckfee,a.mdfee,a.adfee,bz=case when a.InPrice>0 then CAST(a.RMBMoney/a.InPrice as decimal(18,2)) else 0 end,a.yf,c.GoodRate,c.ODR,c.DSRService,c.DSRGoods,case when ISNULL(c.DSRPost,0)=0 then '' else CONVERT(nvarchar(20),c.DSRPost) end DSRPost,c.USDUseMoney,c.USDNoMoney,c.RMBUseMoney,c.RMBNoMoney,c.Rate1,c.Rate2,c.Rate3,c.Rate4,c.PropertyViolate,c.Rate5,c.Rate6,Rate7=isnull(c.Rate7,d.dayfee),JL=cast(b.ml as decimal(18,2))+isnull(ckfee,0)-isnull(c.Rate3,0)-isnull(c.Rate7,d.dayfee),FeeDate=isnull(c.FeeDate,d.FeeDate),TXFee=isnull(c.TXFee,d.TXFee) from \r\nJC_Shop d left join (\r\nselect ShopId,OrderNum=COUNT(0),RMBMoney=SUM(RMBPrice),escrowFee=sum(escrowFee),USDMoney=SUM(TotalPrice),InPrice=SUM(cb),ckfee=SUM(ck),mdfee=SUM(md),adfee=SUM(Ad_Fee),yf=SUM(yf) from #ls\r\ngroup by ShopId)a on d.ShopId=a.ShopId\r\nleft join (select ShopId,TotalPrice=sum(TotalPrice),ml=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 #ls where yf>0\r\ngroup by ShopId)b on a.ShopId=b.ShopId\r\nleft join (\r\nselect GoodRate,ODR,DSRService,DSRGoods,DSRPost,USDUseMoney,USDNoMoney,RMBUseMoney, RMBNoMoney,Rate1,Rate2,Rate3,Rate4,PropertyViolate,Rate5,Rate6,Rate7,FeeDate,TXFee\r\nTXFee,ShopId from JC_ShopData\r\nwhere DATEDIFF(day,indate,'" + InDate.Value.ToString("yyyy-MM-dd") + "')=0 )c on a.ShopId=c.ShopId\r\nleft Join #ls2 e on a.ShopId=e.ShopId where d.PlatType!=2 " + (isyc ? " and d.Master!='东南亚专用'" : "") + "\r\n) a order by " + Sort;
|
|
|
|
DataTable dt = obj.GetReportData(sql);
|
|
|
|
|
|
|
|
return GetReport3(dt, 18);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
string a = dd;
|
|
return "";
|
|
|
|
}
|
|
}
|
|
#endregion
|
|
#region GetReportTable18_Old2
|
|
/// <summary>
|
|
/// 报表18 每日利润 只展示自身的
|
|
/// </summary>
|
|
/// <param name="InDate"></param>
|
|
/// <param name="DeptId"></param>
|
|
/// <param name="ShopId"></param>
|
|
/// <param name="PlatId"></param>
|
|
/// <param name="MoneyCode"></param>
|
|
/// <param name="Sort"></param>
|
|
/// <param name="isyc"></param>
|
|
/// <returns></returns>
|
|
[WebMethod(EnableSession = true)]
|
|
public string GetReportTable18_Old2(DateTime? InDate, int DeptId, int ShopId, int PlatId, string MoneyCode, string Sort, bool isyc)
|
|
{
|
|
string dd = "";
|
|
try
|
|
{
|
|
//判断登录者
|
|
var InUserId = Convert.ToInt32(Session["UserId"]);
|
|
string selectUserIdFirst = "";
|
|
string selectUserId = "";
|
|
string selectUserId1 = "";
|
|
string selectUserId2 = "";
|
|
string selectUserId3 = " ";
|
|
string selectUserId31 = " ";
|
|
string selectUserId32 = " ";
|
|
if (InUserId != 1)
|
|
{
|
|
selectUserIdFirst = "DECLARE @skuUserId INT = " + InUserId + ";\r\n";
|
|
selectUserId = " where \r\n(a.guserid_shopify = @skuUserId or\r\na.guserid_wayfair= @skuUserId or\r\na.guserid_sheIn = @skuUserId or\r\na.guserid_ebay = @skuUserId or\r\na.guserid_wlmart = @skuUserId or\r\na.muserid = @skuUserId or \r\na.guserid = @skuUserId)";
|
|
selectUserId1 = " \r\n left join DT_OrderGoods d with(nolock) on a.OrderId=d.OrderId\r\nleft join HW_GoodsDetail e with(nolock) on e.DetailId=d.DetailId\r\nleft JOIN HW_GoodsInfo f with(nolock) on e.GoodsId=f.GoodsId \r\n";
|
|
selectUserId2 = " and \r\n(f.guserid_shopify = @skuUserId or\r\nf.guserid_wayfair= @skuUserId or\r\nf.guserid_sheIn = @skuUserId or\r\nf.guserid_ebay = @skuUserId or\r\nf.guserid_wlmart = @skuUserId or\r\nf.muserid = @skuUserId or \r\nf.guserid = @skuUserId) \r\n";
|
|
selectUserId3 = " select DISTINCT a.ShopId into #ls3 from DT_OrderInfo a with(nolock)\r\ninner join DT_OrderXXInfo b with(nolock) on a.OrderId=b.OrderId\r\nleft join DT_Fees c with(nolock) on a.OrderId=c.orderid\r\nleft join DT_OrderGoods d with(nolock) on a.OrderId=d.OrderId\r\nleft join HW_GoodsDetail e with(nolock) on e.DetailId=d.DetailId\r\nleft JOIN HW_GoodsInfo f with(nolock) on e.GoodsId=f.GoodsId\r\nwhere DATEDIFF(day,a.OrderDate,'" + InDate.Value.ToString("yyyy-MM-dd") + "')=0 and a.State>0 and a.State<3 and \r\n(f.guserid_shopify = @skuUserId or\r\nf.guserid_wayfair= @skuUserId or\r\nf.guserid_sheIn = @skuUserId or\r\nf.guserid_ebay = @skuUserId or\r\nf.guserid_wlmart = @skuUserId or\r\nf.muserid = @skuUserId or \r\nf.guserid = @skuUserId); ";
|
|
selectUserId31 = "INNER JOIN #ls3 ff on d.ShopId=ff.ShopId\r\n";
|
|
selectUserId32 = "INNER JOIN #ls3 ff on a.ShopId=ff.ShopId\r\n";
|
|
}
|
|
var obj = new BaseService();
|
|
|
|
string tj = "";
|
|
string tj1 = "";
|
|
//if (DeptId > 0)
|
|
//{
|
|
// tj += " and b.DeptId=" + DeptId + " ";
|
|
// tj1 += " and a.DeptId=" + DeptId + " ";
|
|
//}
|
|
if (PlatId > 0)
|
|
{
|
|
tj += " and a.PlatId=" + PlatId + " ";
|
|
tj1 += " and a.PlatId=" + PlatId + " ";
|
|
}
|
|
|
|
if (ShopId > 0)
|
|
{
|
|
tj += " and a.ShopId=" + ShopId + " ";
|
|
tj1 += " and a.ShopId=" + ShopId + " ";
|
|
}
|
|
//if (MoneyCode != "0")
|
|
// tj += " and a.MoneyCode='" + MoneyCode + "' ";
|
|
// string sql = @"
|
|
//declare @moneyrate decimal(18,4)
|
|
//select @moneyrate=MRate from JC_Money where MCode='USD' and PlatId=2
|
|
//select a.ShopId,a.TotalPrice,escrowFee=isnull(a.escrowFee,0),a.RMBPrice,c.yj,yf=c.yf,cb=c.cb,hwsf=c.hwsf,c.tc,c.ck,c.md,a.Ad_Fee,a.DR_BackFee,a.DR_PostFee,a.DR_SSFee,a.Other_Fee,moneyrate=isnull(c.moneyrate,@moneyrate) into #ls from DT_OrderInfo a with(nolock)
|
|
//inner join DT_OrderXXInfo b with(nolock) on a.OrderId=b.OrderId
|
|
//left join DT_Fees c with(nolock) on a.OrderId=c.orderid
|
|
//where DATEDIFF(day,a.OrderDate,'" + InDate.Value.ToString("yyyy-MM-dd") + @"')=0 " + tj + @" and a.State>0 and a.State<3
|
|
//select a.*,Num2=isnull(b.Num2,0) into #ls2 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
|
|
//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
|
|
//where a.State>0 and a.State<3 and DATEDIFF(day,a.OrderDate,'" + InDate.Value.ToString("yyyy-MM-dd") + @"')=0 group by a.ShopId)b on a.ShopId=b.ShopId
|
|
|
|
//select d.ShopName,Master=case when d.Master=d.buyer then d.Master when d.buyer is not null and d.buyer<>'' then d.Master+'-'+d.buyer else d.Master end,a.OrderNum,a.RMBMoney,a.USDMoney,AvgPrice=case when a.OrderNum>0 then cast(a.USDMoney/a.OrderNum as decimal(18,2)) else 0 end,a.InPrice,LR=cast(b.ml as decimal(18,2)),MLL=case when b.TotalPrice>0 then cast(b.ml*100/b.TotalPrice as decimal(18,2)) else 0 end,ShopTJGoods=e.Num,ShopCDGoods=e.Num2,ckfee,a.mdfee,a.adfee,bz=case when a.InPrice>0 then CAST(a.RMBMoney/a.InPrice as decimal(18,2)) else 0 end,c.GoodRate,c.ODR,c.DSRService,c.DSRGoods,h.DSRPost,c.USDUseMoney,c.USDNoMoney,c.RMBUseMoney,c.RMBNoMoney,c.Rate1,c.Rate2,f.Rate3,c.Rate4,c.PropertyViolate,c.Rate5,g.Rate6,Rate7=isnull(c.Rate7,d.dayfee),JL=cast(b.ml as decimal(18,2))+isnull(ckfee,0)+isnull(mdfee,0)-isnull(c.Rate3,0)-isnull(c.Rate7,d.dayfee),FeeDate=isnull(c.FeeDate,d.FeeDate),TXFee=isnull(c.TXFee,d.TXFee) from
|
|
//JC_Shop d left join (
|
|
//select ShopId,OrderNum=COUNT(0),RMBMoney=SUM(RMBPrice),escrowFee=sum(escrowFee),USDMoney=SUM(TotalPrice),InPrice=SUM(cb),ckfee=SUM(ck),mdfee=SUM(md),adfee=SUM(Ad_Fee) from #ls
|
|
//group by ShopId)a on d.ShopId=a.ShopId
|
|
//left join (select ShopId,TotalPrice=sum(TotalPrice),ml=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)) from #ls where yf>0
|
|
//group by ShopId)b on a.ShopId=b.ShopId
|
|
//left join (
|
|
//select GoodRate,ODR,DSRService,DSRGoods,DSRPost,USDUseMoney,USDNoMoney,RMBUseMoney, RMBNoMoney,Rate1,Rate2,Rate3,Rate4,PropertyViolate,Rate5,Rate6,Rate7,FeeDate,TXFee
|
|
//TXFee,ShopId from JC_ShopData
|
|
//where DATEDIFF(day,indate,'" + InDate.Value.ToString("yyyy-MM-dd") + @"')=0 )c on a.ShopId=c.ShopId
|
|
//left Join #ls2 e on a.ShopId=e.ShopId
|
|
// left join (select shopid,SUM(ABS(adfee)) Rate3 from AD_FeeList where DATEDIFF(day,addate,'" + InDate.Value.AddDays(-2).ToString("yyyy-MM-dd") + "')=0 group by shopid) f on d.ShopId=f.shopid "+
|
|
// " left join ( select shopid,SUM(ABS(adfee)) Rate6 from AD_FeeList where CONVERT(varchar(7), addate, 120)='"+ InDate.Value.ToString("yyyy-MM") + "' group by shopid) g on d.ShopId=g.shopid "+
|
|
// " left join(select b.name shopName,case when a.Spend=0 or a.ad_sale_amount=0 then 0 else cast((Spend/ad_sale_amount)*100 as decimal(12,2)) end DSRPost from ( select sid,SUM(ISNULL(ABS(spend),0)) Spend, SUM(ISNULL(ad_sales_amount,0)) ad_sale_amount from [192.168.1.187].[UpGoods].dbo.LxERP_ProductPerformance where summary_field='Asin' and RequestDate='" + InDate.Value.AddDays(-2).ToString("yyyy-MM-dd") + "' group by sid) a left join [192.168.1.187].[UpGoods].dbo.LxERP_Amazon_Shop b on a.sid=b.sid) h on d.ShopName=h.shopName "
|
|
|
|
// + " order by " + Sort;
|
|
|
|
|
|
|
|
string sql = selectUserIdFirst + @"
|
|
declare @moneyrate decimal(18,4)
|
|
select @moneyrate=MRate from JC_Money where MCode='USD' and PlatId=2
|
|
select a.ShopId,a.TotalPrice,escrowFee=isnull(a.escrowFee,0),a.RMBPrice,c.yj,yf=c.yf,cb=c.cb,hwsf=c.hwsf,c.tc,c.ck,c.md,a.Ad_Fee,a.DR_BackFee,a.DR_PostFee,a.DR_SSFee,a.Other_Fee,moneyrate=isnull(c.moneyrate,@moneyrate),ISnull( c.SubsidyFee,0) SubsidyFee into #ls from DT_OrderInfo a with(nolock)
|
|
inner join DT_OrderXXInfo b with(nolock) on a.OrderId=b.OrderId
|
|
left join DT_Fees c with(nolock) on a.OrderId=c.orderid " + selectUserId1 + @"
|
|
where DATEDIFF(day,a.OrderDate,'" + InDate.Value.ToString("yyyy-MM-dd") + @"')=0 " + tj + @" and a.State>0 and a.State<3 " + selectUserId2 + @"
|
|
select a.*,Num2=isnull(b.Num2,0) into #ls2 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 " + selectUserId + @"
|
|
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
|
|
where a.State>0 and a.State<3 and DATEDIFF(day,a.OrderDate,'" + InDate.Value.ToString("yyyy-MM-dd") + @"')=0 group by a.ShopId)b on a.ShopId=b.ShopId
|
|
" + selectUserId3 + @"
|
|
select * from (
|
|
select d.ShopName,Master=case when d.Master=d.buyer then d.Master when d.buyer is not null and d.buyer<>'' then d.Master+'-'+d.buyer else d.Master end,a.OrderNum,a.RMBMoney,a.USDMoney,AvgPrice=case when a.OrderNum>0 then cast(a.USDMoney/a.OrderNum as decimal(18,2)) else 0 end,a.InPrice,LR=cast(b.ml as decimal(18,2)),MLL=case when b.TotalPrice>0 then cast(b.ml*100/b.TotalPrice as decimal(18,2)) else 0 end,ShopTJGoods=e.Num,ShopCDGoods=e.Num2,ckfee,a.mdfee,a.adfee,bz=case when a.InPrice>0 then CAST(a.RMBMoney/a.InPrice as decimal(18,2)) else 0 end,a.yf,c.GoodRate,c.ODR,t3.CVR DSRService,t3.user_sessions DSRGoods,f.DSRPost,c.USDUseMoney, n1.originalTotalCurrencyAmount as USDNoMoney,c.RMBUseMoney,c.RMBNoMoney,t2.orderDefectRate Rate1,t2.validTrackingRate Rate2,Case when d.PlatType=2 then f.Rate3 else c.Rate3 end Rate3,t4.RefundRate as Rate4, ABS( ISNULL( t4.RefundAmount,0) ) as PropertyViolate,t2.cancelRate Rate5,Case when d.PlatType=2 then g.Rate6 else (select SUM(ISNULL(Rate3,0)) from JC_ShopData where ShopId=d.ShopId and DATEDIFF(MONTH,InDate,'2023-10-18')=0) end Rate6,Rate7=isnull(c.Rate7,d.dayfee),JL=cast(b.ml as decimal(18,2))+isnull(ckfee,0)-isnull(c.Rate3,0)-isnull(c.Rate7,d.dayfee),t1.financialEventGroupEndLocale as FeeDate,t1.originalTotalCurrencyAmount as TXFee from
|
|
JC_Shop d left join (
|
|
select ShopId,OrderNum=COUNT(0),RMBMoney=SUM(RMBPrice),escrowFee=sum(escrowFee),USDMoney=SUM(TotalPrice),InPrice=SUM(cb),ckfee=SUM(ck),mdfee=SUM(md),adfee=SUM(Ad_Fee),yf=SUM(yf) from #ls
|
|
group by ShopId)a on d.ShopId=a.ShopId
|
|
left join (select ShopId,TotalPrice=sum(TotalPrice),ml=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 #ls where yf>0
|
|
group by ShopId)b on a.ShopId=b.ShopId
|
|
left join (
|
|
select GoodRate,ODR,DSRService,DSRGoods,DSRPost,USDUseMoney,USDNoMoney,RMBUseMoney, RMBNoMoney,Rate1,Rate2,Rate3,Rate4,PropertyViolate,Rate5,Rate6,Rate7,FeeDate,TXFee
|
|
TXFee,ShopId from JC_ShopData
|
|
where DATEDIFF(day,indate,'" + InDate.Value.ToString("yyyy-MM-dd") + @"')=0 )c on a.ShopId=c.ShopId
|
|
left Join #ls2 e on a.ShopId=e.ShopId
|
|
left join [192.168.1.187].[UpGoods].dbo.LxERP_Amazon_Shop o on d.ShopName=o.name
|
|
left join ( select sid, CONVERT(decimal(12,2), SUM(t.cost)) as Rate3,CONVERT(decimal(12,2), SUM(t.sales)) as ad_sales , case when CONVERT(decimal(12,2), SUM(t.sales))<=0 then '∞' else CONVERT(nvarchar(20), convert(decimal(12,2), (CONVERT(decimal(12,2), SUM(t.cost))/CONVERT(decimal(12,2), SUM(t.sales)))*100)) end DSRPost from( select sid, SUM( ISNULL(cost,0)) as cost,SUM(ISNULL(sales,0))sales from [192.168.1.187].[UpGoods].dbo.LxERP_SPAdGroupReports where CONVERT(date,report_date)='" + InDate.Value.AddDays(-2).Date.ToString("yyyy-MM-dd") + "' group by sid union all select sid, SUM( ISNULL(cost,0)) as cost,SUM(ISNULL(sales,0))sales from [192.168.1.187].[UpGoods].dbo.LxERP_SBCampaignReports where CONVERT(date,report_date)='" + InDate.Value.AddDays(-2).Date.ToString("yyyy-MM-dd") + "' group by sid union all select sid, SUM( ISNULL(cost,0)) as cost,SUM(ISNULL(sales,0))sales from [192.168.1.187].[UpGoods].dbo.LxERP_SDAdGroupReports where CONVERT(date,report_date)='" + InDate.Value.AddDays(-2).Date.ToString("yyyy-MM-dd") + "' group by sid) t group by sid ) f on f.sid=o.sid" +
|
|
" \r\n left join (select sid, CONVERT(decimal(12,2), SUM(t.cost)) as Rate6 from( select sid, SUM( ISNULL(cost,0)) as cost from [192.168.1.187].[UpGoods].dbo.LxERP_SPAdGroupReports where CONVERT(date,report_date)<='" + InDate.Value.AddDays(-2).ToString("yyyy-MM-dd") + "' and CONVERT(date,report_date)>='" + InDate.Value.AddDays(-31).ToString("yyyy-MM-dd") + "' group by sid union all select sid, SUM( ISNULL(cost,0)) as cost from [192.168.1.187].[UpGoods].dbo.LxERP_SBCampaignReports where CONVERT(date,report_date)<='" + InDate.Value.AddDays(-2).ToString("yyyy-MM-dd") + "' and CONVERT(date,report_date)>='" + InDate.Value.AddDays(-31).ToString("yyyy-MM-dd") + "' group by sid union all select sid, SUM( ISNULL(cost,0)) as cost from [192.168.1.187].[UpGoods].dbo.LxERP_SDAdGroupReports where CONVERT(date,report_date)<='" + InDate.Value.AddDays(-2).ToString("yyyy-MM-dd") + "' and CONVERT(date,report_date)>='" + InDate.Value.AddDays(-31).ToString("yyyy-MM-dd") + "' group by sid) t group by sid) g on o.sid=g.sid " +
|
|
" \r\n left join(select t.row, t.storename,t.financialEventGroupEndLocale,t.originCurrencyIcon,CAST(ISNULL( t.originalTotalCurrencyAmount,0) as decimal(12,2)) as originalTotalCurrencyAmount from ( select ROW_NUMBER() over(PARTITION BY storename ORDER BY requestDate DESC) row,storename, financialEventGroupEndLocale,originCurrencyIcon,originalTotalCurrencyAmount from [192.168.1.187].[UpGoods].dbo.LxERP_SettlementSummaryData where accountType='Standard' and processingStatus='Closed ') t where t.row=1) t1 on d.ShopName=t1.storeName left join (select storename,CONVERT(decimal(12,2), SUM(ISNULL(originalTotalCurrencyAmount,0))) originalTotalCurrencyAmount from [192.168.1.187].[UpGoods].dbo.LxERP_SettlementSummaryData where processingStatus='Open' and accountType='Standard' group by storename) n1 on d.ShopName=n1.storeName " +
|
|
"\r\n" +
|
|
" left join (select sid, CAST( orderDefectRate *100 as decimal(12,2)) orderDefectRate,CAST( validTrackingRate *100 as decimal(12,2)) validTrackingRate,cast(CancelRate *100 as decimal(12,2)) cancelRate from (\r\nselect ROW_NUMBER() over(PARTITION BY sid ORDER BY requestDate DESC) row,sid, orderDefectRate,validTrackingRate,CancelRate from [192.168.1.187].[UpGoods].dbo.Amazon_V2_SELLER_PERFORMANCE_REPORT where CONVERT(date, RequestDate)='" + InDate.Value.AddDays(-1).Date.ToString("yyyy-MM-dd") + "') t where t.row=1)t2 on t2.sid=o.sid \r\nleft join (select sid, user_sessions,totalOrderItems, case when user_sessions=0 or totalOrderItems=0 then 0 else convert(decimal(12,2),(totalOrderItems*1.00/user_sessions)*100 ) end as CVR from [192.168.1.187].[UpGoods].dbo.Amazon_SALES_AND_TRAFFIC_REPORT where CONVERT(date, ReportDate)='" + InDate.Value.AddDays(-2).Date.ToString("yyyy-MM-dd") + "') t3 on t3.sid=o.sid\r\nleft join [192.168.1.187].[UpGoods].dbo.AmazonStoreRefundAmountAndRate t4 on t4.sid=o.sid and Convert(date, t4.ReportDate)='" + InDate.Value.AddDays(-2).Date.ToString("yyyy-MM-dd") + "'\r\n"
|
|
+ selectUserId31 + " where d.PlatType=2 union all\r\n select d.ShopName,Master=case when d.Master=d.buyer then d.Master when d.buyer is not null and d.buyer<>'' then d.Master+'-'+d.buyer else d.Master end,a.OrderNum,a.RMBMoney,a.USDMoney,AvgPrice=case when a.OrderNum>0 then cast(a.USDMoney/a.OrderNum as decimal(18,2)) else 0 end,a.InPrice,LR=cast(b.ml as decimal(18,2)),MLL=case when b.TotalPrice>0 then cast(b.ml*100/b.TotalPrice as decimal(18,2)) else 0 end,ShopTJGoods=e.Num,ShopCDGoods=e.Num2,ckfee,a.mdfee,a.adfee,bz=case when a.InPrice>0 then CAST(a.RMBMoney/a.InPrice as decimal(18,2)) else 0 end,a.yf,c.GoodRate,c.ODR,c.DSRService,c.DSRGoods,case when ISNULL(c.DSRPost,0)=0 then '' else CONVERT(nvarchar(20),c.DSRPost) end DSRPost,c.USDUseMoney,c.USDNoMoney,c.RMBUseMoney,c.RMBNoMoney,c.Rate1,c.Rate2,c.Rate3,c.Rate4,c.PropertyViolate,c.Rate5,c.Rate6,Rate7=isnull(c.Rate7,d.dayfee),JL=cast(b.ml as decimal(18,2))+isnull(ckfee,0)-isnull(c.Rate3,0)-isnull(c.Rate7,d.dayfee),FeeDate=isnull(c.FeeDate,d.FeeDate),TXFee=isnull(c.TXFee,d.TXFee) from \r\nJC_Shop d left join (\r\nselect ShopId,OrderNum=COUNT(0),RMBMoney=SUM(RMBPrice),escrowFee=sum(escrowFee),USDMoney=SUM(TotalPrice),InPrice=SUM(cb),ckfee=SUM(ck),mdfee=SUM(md),adfee=SUM(Ad_Fee),yf=SUM(yf) from #ls\r\ngroup by ShopId)a on d.ShopId=a.ShopId\r\nleft join (select ShopId,TotalPrice=sum(TotalPrice),ml=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 #ls where yf>0\r\ngroup by ShopId)b on a.ShopId=b.ShopId\r\nleft join (\r\nselect GoodRate,ODR,DSRService,DSRGoods,DSRPost,USDUseMoney,USDNoMoney,RMBUseMoney, RMBNoMoney,Rate1,Rate2,Rate3,Rate4,PropertyViolate,Rate5,Rate6,Rate7,FeeDate,TXFee\r\nTXFee,ShopId from JC_ShopData\r\nwhere DATEDIFF(day,indate,'" + InDate.Value.ToString("yyyy-MM-dd") + "')=0 )c on a.ShopId=c.ShopId\r\nleft Join #ls2 e on a.ShopId=e.ShopId " + selectUserId32 + "where d.PlatType!=2 " + (isyc ? " and d.Master!='东南亚专用'" : "") + "\r\n) a order by " + Sort;
|
|
|
|
DataTable dt = obj.GetReportData(sql);
|
|
|
|
|
|
|
|
return GetReport3(dt, 18);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
string a = dd;
|
|
return "";
|
|
|
|
}
|
|
}
|
|
#endregion
|
|
#region
|
|
/// <summary>
|
|
/// 报表18 每日利润 只展示自身的 最新需求
|
|
/// </summary>
|
|
/// <param name="InDate"></param>
|
|
/// <param name="DeptId"></param>
|
|
/// <param name="ShopId"></param>
|
|
/// <param name="PlatId"></param>
|
|
/// <param name="MoneyCode"></param>
|
|
/// <param name="Sort"></param>
|
|
/// <param name="isyc"></param>
|
|
/// <returns></returns>
|
|
[WebMethod(EnableSession = true)]
|
|
public string GetReportTable18Old5(DateTime? InDate)
|
|
{
|
|
string dd = "";
|
|
try
|
|
{
|
|
//int UserId = 243;
|
|
var UserId = Convert.ToInt32(Session["UserId"]);
|
|
var list = DataNew.MRJL_GetUserOderMsgByDay((DateTime)InDate, UserId);
|
|
var goodsList = DataNew.MRJL_GetGoodsIdByDay((DateTime)InDate, UserId);
|
|
|
|
if (list != null && list.Count() > 0)
|
|
{
|
|
foreach (var l in list)
|
|
{
|
|
//名称
|
|
l.ShopName = DataNew.MRJL_GetShopName(l.ShopId);
|
|
//客单价
|
|
l.AvgPrice = Math.Round(l.USDMoney / l.OrderNum, 2);
|
|
|
|
if (goodsList != null && goodsList.Count() > 0)
|
|
{
|
|
//获取广告
|
|
var plstId = goodsList.Where(t => t.ShopId == l.ShopId).Select(t => t.PlatId).FirstOrDefault();
|
|
var goods = goodsList.Where(t => t.ShopId == l.ShopId).Select(t => t.GoodsID.ToString()).ToList();
|
|
|
|
var adfeeModel1 = DataNew.MRJL_GetGKFeeByDay((DateTime)InDate, plstId, l.ShopId, goods);
|
|
var adfeeModel2 = DataNew.MRJL_GetGKFeeByMouth((DateTime)InDate, plstId, l.ShopId, goods);
|
|
|
|
|
|
l.Rate3 = adfeeModel1 != null ? adfeeModel1.Adfee : 0;
|
|
l.Rate6 = adfeeModel2 != null ? adfeeModel2.Adfee : 0;
|
|
|
|
//
|
|
|
|
}
|
|
|
|
//净利润
|
|
l.LR = Math.Round(l.LR, 2);
|
|
l.JL = l.LR + l.ckfee - l.Rate3;
|
|
}
|
|
}
|
|
|
|
var dt = DataNew.ListToDataTable<BB_MRJL_User>(list.OrderByDescending(t => t.OrderNum).ToList());
|
|
//var list = DataNew.GetUserOderMsgByDay((DateTime)InDate, UserId);
|
|
//DataTable dt = obj.GetReportData(sql);
|
|
return GetReport3(dt, 181);
|
|
return "";
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
string a = dd;
|
|
return "";
|
|
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
|
|
|
|
|
|
|
|
#endregion
|
|
|
|
#region 报表
|
|
|
|
public string GetReport3(DataTable dt, int ReportId)
|
|
{
|
|
PagesNew.Login(this.Session);
|
|
var obj = new BaseService();
|
|
var rmd = BaseService.GetReportModel(ReportId);
|
|
var rlist = obj.GetRepoartColsList(ReportId);
|
|
if (rmd == null || rlist == null)
|
|
return "";
|
|
|
|
StringBuilder html = new StringBuilder();
|
|
html.Append("<tr class='HeaderStyle'><th>序号</th>");
|
|
foreach (var md in rlist)
|
|
{
|
|
html.Append("<th>" + md.ColName + "</th>");
|
|
|
|
}
|
|
html.Append("</tr>");
|
|
if (dt != null)
|
|
{
|
|
DataRow row = null;
|
|
|
|
|
|
for (int i = 0; i < dt.Rows.Count; i++)
|
|
{
|
|
html.Append("<tr class='DataGridRowStyle'><td>" + (i + 1).ToString() + "</td>");
|
|
int rowindex = 1;
|
|
foreach (var md in rlist)
|
|
{
|
|
if (dt.Columns.Contains(md.ColValue) == false)
|
|
{
|
|
html.Append("<td rowindex='" + rowindex + "' rowtr='" + i + "' rname='" + md.ColName + "'></td>");
|
|
rowindex++;
|
|
continue;
|
|
}
|
|
if (md.IsSum == 1 && i == 0)
|
|
{
|
|
if (row == null)
|
|
row = dt.NewRow();
|
|
if (md.ColType == "int")
|
|
{
|
|
try
|
|
{
|
|
row[md.ColValue] = Convert.ToInt32(dt.Compute("sum(" + md.ColValue + ")", "" + md.ColValue + " is not null"));
|
|
}
|
|
catch
|
|
{
|
|
row[md.ColValue] = 0;
|
|
|
|
}
|
|
|
|
}
|
|
else if (md.ColType == "dec")
|
|
{
|
|
try
|
|
{
|
|
row[md.ColValue] = Convert.ToDecimal(dt.Compute("sum(" + md.ColValue + ")", "" + md.ColValue + ">0"));
|
|
}
|
|
catch
|
|
{
|
|
row[md.ColValue] = 0;
|
|
|
|
}
|
|
|
|
|
|
}
|
|
}
|
|
if (dt.Rows[i][md.ColValue] != null && dt.Rows[i][md.ColValue].ToString() != "")
|
|
{
|
|
string csscolor = "";
|
|
if (md.ContionType == "=" && dt.Rows[i][md.ColValue].ToString() == md.SValue)
|
|
{
|
|
csscolor = md.ColColor;
|
|
}
|
|
else
|
|
if (md.ContionType == ">" && Convert.ToDecimal(dt.Rows[i][md.ColValue]) > Convert.ToDecimal(md.SValue) && Convert.ToDecimal(dt.Rows[i][md.ColValue]) < Convert.ToDecimal(md.EValue))
|
|
{
|
|
csscolor = md.ColColor;
|
|
}
|
|
else
|
|
if (md.ContionType == ">=" && Convert.ToDecimal(dt.Rows[i][md.ColValue]) >= Convert.ToDecimal(md.SValue) && Convert.ToDecimal(dt.Rows[i][md.ColValue]) <= Convert.ToDecimal(md.EValue))
|
|
{
|
|
csscolor = md.ColColor;
|
|
}
|
|
if (csscolor != "")
|
|
csscolor = "style='color:" + csscolor + "'";
|
|
string datav = "";
|
|
if (md.ColType == "date1")
|
|
datav = Convert.ToDateTime(dt.Rows[i][md.ColValue]).ToString("yyyy-MM-dd");
|
|
else
|
|
if (md.ColType == "date2")
|
|
datav = Convert.ToDateTime(dt.Rows[i][md.ColValue]).ToString("yyyy-MM-dd HH:mm");
|
|
else
|
|
datav = dt.Rows[i][md.ColValue].ToString();
|
|
html.Append("<td " + csscolor + " rowindex='" + rowindex + "' rowtr='" + i + "' rname='" + md.ColName + "'>" + datav + "</td>");
|
|
}
|
|
else
|
|
html.Append("<td rowindex='" + rowindex + "' rowtr='" + i + "' rname='" + md.ColName + "'></td>");
|
|
rowindex++;
|
|
}
|
|
html.Append("</tr>");
|
|
|
|
}
|
|
int hj = 0;
|
|
if (row != null)
|
|
{
|
|
html.Append("<tr class='DataGridRowStyle'><td>合计：</td>");
|
|
foreach (var md in rlist)
|
|
{
|
|
if (dt.Columns.Contains(md.ColValue) == false)
|
|
{
|
|
html.Append("<td></td>");
|
|
continue;
|
|
}
|
|
if (md.IsSum == 1 && row[md.ColValue] != null)
|
|
{
|
|
if (hj == 0)
|
|
{
|
|
hj = 1;
|
|
html.Append("<td rtype='hj' rname='" + md.ColName + "'>" + row[md.ColValue] + "</td>");
|
|
}
|
|
else
|
|
html.Append("<td rtype='hj' rname='" + md.ColName + "'>" + row[md.ColValue] + "</td>");
|
|
}
|
|
else
|
|
html.Append("<td></td>");
|
|
}
|
|
html.Append("</tr>");
|
|
}
|
|
|
|
|
|
}
|
|
return html.ToString();
|
|
|
|
}
|
|
#endregion
|
|
|
|
|
|
|
|
#region 首页订单数
|
|
[WebMethod(EnableSession = true)]
|
|
public DT_DefaultDataNew GetDefaultDataForTM2(DateTime? st, Decimal MRate)
|
|
{
|
|
PagesNew.Login(this.Session);
|
|
int CompanyId = Convert.ToInt32(Session["CompanyId"]);
|
|
int userid = Convert.ToInt32(Session["UserId"]);
|
|
|
|
BaseService obj = new BaseService();
|
|
DateTime sdate = Convert.ToDateTime(DateTime.Today.AddDays(-1).ToString("yyyy-MM-dd"));
|
|
DateTime edate = Convert.ToDateTime(DateTime.Today.ToString("yyyy-MM-dd"));
|
|
|
|
var model = DataNew.GetDefaultDataForTM2(userid, CompanyId, st, st.Value.AddDays(-1), sdate, edate);
|
|
|
|
return model;
|
|
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region 平台店铺主管
|
|
[WebMethod(EnableSession = true)]
|
|
public List<JC_Shop> GetShopAllList(int PlatType)
|
|
{
|
|
PagesNew.Login(this.Session);
|
|
int UserId = Convert.ToInt32(Session["UserId"]);
|
|
var list = DataNew.GetShopListFPage(UserId, PlatType, "M");
|
|
return list;
|
|
}
|
|
#endregion
|
|
|
|
|
|
[WebMethod(EnableSession = true)]
|
|
public string GetListUSMap2()
|
|
{
|
|
|
|
DateTime Sdate = DateTime.Today.AddDays(-1);
|
|
int UserId = Convert.ToInt32(Session["UserId"]);
|
|
// BaseService.SetData();
|
|
List<DT_USMap> list = DataNew.GetListUSOrder(UserId,"", Sdate, Sdate.AddDays(1));
|
|
JavaScriptSerializer serializer = new JavaScriptSerializer();
|
|
return serializer.Serialize(list);
|
|
}
|
|
|
|
|
|
#region 首页订单统计
|
|
[WebMethod(EnableSession = true)]
|
|
public DT_OrderNum GetDefaultOrderCount1(List<DT_OrderCountContent> listshop, int Type, int Days)
|
|
{
|
|
PagesNew.Login(this.Session);
|
|
int CompanyId = Convert.ToInt32(Session["CompanyId"]);
|
|
int UserId = Convert.ToInt32(Session["UserId"]);
|
|
var obj = new DD_OrderData();
|
|
string Ids = "";
|
|
|
|
string[] categories = null;
|
|
DT_OrderNum md = new DT_OrderNum();
|
|
var list = new List<DT_OrderCountData>();
|
|
|
|
DateTime SDate = DateTime.Today.AddDays(-1 * Days);
|
|
DateTime EDate = DateTime.Today;
|
|
TimeSpan ts = EDate - SDate;
|
|
int days = ts.Days + 1;
|
|
|
|
|
|
List<DT_CountListFromDay> countlist = new List<DT_CountListFromDay>();
|
|
|
|
countlist = DataNew.GetDefaultCountListFromDay2(UserId, Type, Ids.Trim(','), SDate, EDate.AddDays(1), CompanyId);
|
|
List<DT_CountListFromDay> countlist2 = new List<DT_CountListFromDay>();
|
|
List<DT_CountListFromDay> countlist3 = new List<DT_CountListFromDay>();
|
|
if (Type == 3)
|
|
{
|
|
countlist2 = DataNew.GetDefaultCountListFromDay2(UserId, 4, Ids.Trim(','), SDate, EDate.AddDays(1), CompanyId);
|
|
//countlist3 = obj.GetDefaultCountListFromDay2(4, Ids.Trim(','), SDate.AddYears(-1), EDate.AddDays(1).AddYears(-1), CompanyId);
|
|
countlist3 = DataNew.GetDefaultCountListFromDay2(UserId, 4, Ids.Trim(','), SDate.AddYears(-1), EDate.AddDays(1).AddYears(-1), CompanyId);
|
|
}
|
|
|
|
categories = new string[days];
|
|
if (days > 0)
|
|
{
|
|
|
|
for (int i = 0; i < days; i++)
|
|
{
|
|
categories[i] = (SDate.AddDays(i)).ToString("MM月dd日");
|
|
|
|
|
|
|
|
}
|
|
}
|
|
if (Type == 3)
|
|
{
|
|
var smd = new DT_OrderCountContent();
|
|
smd.Id = 0;
|
|
smd.Name = "总销售额";
|
|
listshop.Insert(0, smd);
|
|
|
|
var _smd = new DT_OrderCountContent();
|
|
_smd.Id = -1;
|
|
_smd.Name = "去年总销售额";
|
|
listshop.Insert(0, _smd);
|
|
}
|
|
if (listshop != null && listshop.Count > 0)
|
|
{
|
|
for (int i = 0; i < listshop.Count; i++)
|
|
{
|
|
decimal[] data = new decimal[days];
|
|
if (days > 0)
|
|
{
|
|
|
|
for (int j = 0; j < days; j++)
|
|
{
|
|
if (listshop[i].Id == 0 && Type == 3)
|
|
{
|
|
if (countlist2 != null)
|
|
{
|
|
var countmd2 = countlist2.Find(n => n.OrderDate == SDate.AddDays(j).ToString("yyyy-MM-dd"));
|
|
if (countmd2 != null)
|
|
data[j] = countmd2.Price.Value;
|
|
else
|
|
data[j] = 0;
|
|
}
|
|
else
|
|
data[j] = 0;
|
|
}
|
|
else if (listshop[i].Id == -1 && Type == 3)
|
|
{
|
|
if (countlist3 != null)
|
|
{
|
|
var countmd2 = countlist3.Find(n => n.OrderDate == SDate.AddYears(-1).AddDays(j).ToString("yyyy-MM-dd"));
|
|
if (countmd2 != null)
|
|
data[j] = countmd2.Price.Value;
|
|
else
|
|
data[j] = 0;
|
|
}
|
|
else
|
|
data[j] = 0;
|
|
}
|
|
else
|
|
{
|
|
if (countlist != null)
|
|
{
|
|
var countmd = countlist.Find(n => n.PlatId == listshop[i].Id && n.OrderDate == SDate.AddDays(j).ToString("yyyy-MM-dd"));
|
|
if (countmd != null && Type == 3)
|
|
data[j] = countmd.Price.Value;
|
|
else if (countmd != null)
|
|
data[j] = countmd.Num.Value;
|
|
else
|
|
data[j] = 0;
|
|
}
|
|
else
|
|
data[j] = 0;
|
|
}
|
|
|
|
|
|
}
|
|
}
|
|
var datamd = new DT_OrderCountData();
|
|
|
|
datamd.name = listshop[i].Name;
|
|
|
|
datamd.data = data;
|
|
list.Add(datamd);
|
|
}
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
md.categories = categories;
|
|
|
|
JavaScriptSerializer JsonConvert = new JavaScriptSerializer();
|
|
string postmessage = JsonConvert.Serialize(list);
|
|
md.Data = postmessage;
|
|
|
|
|
|
return md;
|
|
|
|
}
|
|
#endregion
|
|
|
|
|
|
|
|
#region 首页订单统计
|
|
[WebMethod(EnableSession = true)]
|
|
public DT_OrderNum GetDefaultOrderCountMonth(List<DT_OrderCountContent> listshop, int Type)
|
|
{
|
|
PagesNew.Login(this.Session);
|
|
int CompanyId = Convert.ToInt32(Session["CompanyId"]);
|
|
int userid = Convert.ToInt32(Session["UserId"]);
|
|
var obj = new DD_OrderData();
|
|
string Ids = "";
|
|
|
|
string[] categories = null;
|
|
DT_OrderNum md = new DT_OrderNum();
|
|
var list = new List<DT_OrderCountData>();
|
|
|
|
DateTime SDate = Convert.ToDateTime(DateTime.Today.ToString("yyyy-MM-01")).AddMonths(-11);
|
|
DateTime EDate = DateTime.Today;
|
|
|
|
|
|
|
|
|
|
List<DT_CountForMonth> countlist = new List<DT_CountForMonth>();
|
|
if (Type == 1)
|
|
countlist = DataNew.GetDefaultCountListFromMonth(userid, SDate, EDate.AddDays(1), CompanyId);
|
|
else
|
|
countlist = DataNew.GetDefaultCountListFromMonthPrice(userid, SDate, EDate.AddDays(1), CompanyId);
|
|
List<DT_CountForMonth> countlist2 = new List<DT_CountForMonth>();
|
|
if (Type == 1)
|
|
countlist2 = DataNew.GetDefaultCountListFromMonth2(userid, SDate, EDate.AddDays(1), CompanyId);
|
|
else
|
|
countlist2 = DataNew.GetDefaultCountListFromMonthPrice2(userid, SDate, EDate.AddDays(1), CompanyId);
|
|
categories = new string[12];
|
|
int m = 11;
|
|
for (int i = 0; i < 12; i++)
|
|
{
|
|
|
|
categories[i] = (DateTime.Today.AddMonths(-1 * m).Month).ToString() + "月";
|
|
m--;
|
|
|
|
|
|
}
|
|
|
|
if (Type == 1)
|
|
{
|
|
var smd = new DT_OrderCountContent();
|
|
smd.Id = 0;
|
|
smd.Name = "总单量";
|
|
listshop.Insert(0, smd);
|
|
}
|
|
else
|
|
if (Type == 2)
|
|
{
|
|
var smd = new DT_OrderCountContent();
|
|
smd.Id = 0;
|
|
smd.Name = "总销售额";
|
|
listshop.Insert(0, smd);
|
|
}
|
|
if (listshop != null && listshop.Count > 0)
|
|
{
|
|
for (int i = 0; i < listshop.Count; i++)
|
|
{
|
|
decimal[] data = new decimal[12];
|
|
int m2 = 11;
|
|
for (int j = 0; j < 12; j++)
|
|
{
|
|
if (listshop[i].Id == 0)
|
|
{
|
|
if (countlist2 != null)
|
|
{
|
|
|
|
var countmd2 = countlist2.Find(n => n.Month == DateTime.Today.AddMonths(-1 * m2).Month);
|
|
if (countmd2 != null)
|
|
{
|
|
if (Type == 1)
|
|
data[j] = countmd2.Num.Value;
|
|
else
|
|
data[j] = countmd2.Price.Value;
|
|
}
|
|
else
|
|
data[j] = 0;
|
|
}
|
|
else
|
|
data[j] = 0;
|
|
}
|
|
else
|
|
{
|
|
if (countlist != null)
|
|
{
|
|
var countmd = countlist.Find(n => n.ShopId == listshop[i].Id && n.Month == DateTime.Today.AddMonths(-1 * m2).Month);
|
|
if (countmd != null)
|
|
{
|
|
if (Type == 1)
|
|
data[j] = countmd.Num.Value;
|
|
else
|
|
data[j] = countmd.Price.Value;
|
|
}
|
|
else
|
|
data[j] = 0;
|
|
}
|
|
else
|
|
data[j] = 0;
|
|
}
|
|
|
|
m2--;
|
|
}
|
|
|
|
var datamd = new DT_OrderCountData();
|
|
|
|
datamd.name = listshop[i].Name;
|
|
|
|
datamd.data = data;
|
|
list.Add(datamd);
|
|
}
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
md.categories = categories;
|
|
|
|
JavaScriptSerializer JsonConvert = new JavaScriptSerializer();
|
|
string postmessage = JsonConvert.Serialize(list);
|
|
md.Data = postmessage;
|
|
|
|
|
|
return md;
|
|
|
|
}
|
|
#endregion
|
|
|
|
|
|
|
|
#region 首页订单统计
|
|
[WebMethod(EnableSession = true)]
|
|
public DT_OrderNum GetDefaultOrderCountMonth2(List<DT_OrderCountContent> listshop, int Type)
|
|
{
|
|
PagesNew.Login(this.Session);
|
|
int CompanyId = Convert.ToInt32(Session["CompanyId"]);
|
|
int userid = Convert.ToInt32(Session["UserId"]);
|
|
var obj = new DD_OrderData();
|
|
string Ids = "";
|
|
|
|
string[] categories = null;
|
|
DT_OrderNum md = new DT_OrderNum();
|
|
var list = new List<DT_OrderCountData>();
|
|
|
|
DateTime SDate = Convert.ToDateTime(DateTime.Today.ToString("yyyy-MM-01")).AddMonths(-11);
|
|
DateTime EDate = DateTime.Today;
|
|
|
|
|
|
|
|
|
|
List<DT_CountForMonth> countlist = new List<DT_CountForMonth>();
|
|
if (Type == 1)
|
|
countlist = DataNew.GetDefaultCountListFromMonthPT(userid, SDate, EDate.AddDays(1), CompanyId);
|
|
else
|
|
countlist = DataNew.GetDefaultCountListFromMonthPTPrice(userid, SDate, EDate.AddDays(1), CompanyId);
|
|
List<DT_CountForMonth> countlist2 = new List<DT_CountForMonth>();
|
|
if (Type == 1)
|
|
countlist2 = DataNew.GetDefaultCountListFromMonth2(userid, SDate, EDate.AddDays(1), CompanyId);
|
|
else
|
|
countlist2 = DataNew.GetDefaultCountListFromMonthPrice2(userid, SDate, EDate.AddDays(1), CompanyId);
|
|
categories = new string[12];
|
|
int m = 11;
|
|
for (int i = 0; i < 12; i++)
|
|
{
|
|
|
|
categories[i] = (DateTime.Today.AddMonths(-1 * m).Month).ToString() + "月";
|
|
m--;
|
|
|
|
|
|
}
|
|
|
|
if (Type == 1)
|
|
{
|
|
var smd = new DT_OrderCountContent();
|
|
smd.Id = 0;
|
|
smd.Name = "总单量";
|
|
listshop.Insert(0, smd);
|
|
}
|
|
else
|
|
if (Type == 2)
|
|
{
|
|
var smd = new DT_OrderCountContent();
|
|
smd.Id = 0;
|
|
smd.Name = "总销售额";
|
|
listshop.Insert(0, smd);
|
|
}
|
|
if (listshop != null && listshop.Count > 0)
|
|
{
|
|
for (int i = 0; i < listshop.Count; i++)
|
|
{
|
|
decimal[] data = new decimal[12];
|
|
int m2 = 11;
|
|
for (int j = 0; j < 12; j++)
|
|
{
|
|
if (listshop[i].Id == 0)
|
|
{
|
|
if (countlist2 != null)
|
|
{
|
|
|
|
var countmd2 = countlist2.Find(n => n.Month == DateTime.Today.AddMonths(-1 * m2).Month);
|
|
if (countmd2 != null)
|
|
{
|
|
if (Type == 1)
|
|
data[j] = countmd2.Num.Value;
|
|
else
|
|
data[j] = countmd2.Price.Value;
|
|
}
|
|
else
|
|
data[j] = 0;
|
|
}
|
|
else
|
|
data[j] = 0;
|
|
}
|
|
else
|
|
{
|
|
if (countlist != null)
|
|
{
|
|
var countmd = countlist.Find(n => n.PlatId == listshop[i].Id && n.Month == DateTime.Today.AddMonths(-1 * m2).Month);
|
|
if (countmd != null)
|
|
{
|
|
if (Type == 1)
|
|
data[j] = countmd.Num.Value;
|
|
else
|
|
data[j] = countmd.Price.Value;
|
|
}
|
|
else
|
|
data[j] = 0;
|
|
}
|
|
else
|
|
data[j] = 0;
|
|
}
|
|
|
|
m2--;
|
|
}
|
|
|
|
var datamd = new DT_OrderCountData();
|
|
|
|
datamd.name = listshop[i].Name;
|
|
|
|
datamd.data = data;
|
|
list.Add(datamd);
|
|
}
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
md.categories = categories;
|
|
|
|
JavaScriptSerializer JsonConvert = new JavaScriptSerializer();
|
|
string postmessage = JsonConvert.Serialize(list);
|
|
md.Data = postmessage;
|
|
|
|
|
|
return md;
|
|
|
|
}
|
|
#endregion
|
|
|
|
|
|
|
|
#region 发货立方
|
|
[WebMethod(EnableSession = true)]
|
|
public DT_OrderNum GetOrderCountGoodsSoildDay_New()
|
|
{
|
|
PagesNew.Login(this.Session);
|
|
int CompanyId = Convert.ToInt32(Session["CompanyId"]);
|
|
int userid = Convert.ToInt32(Session["UserId"]);
|
|
var obj = new DD_OrderData();
|
|
|
|
|
|
string[] categories = null;
|
|
DT_OrderNum md = new DT_OrderNum();
|
|
var list = new List<DT_OrderCountData>();
|
|
|
|
string tj = string.Format(" and ( d.muserid={0} or d.guserid_ebay={0} or d.guserid_wlmart={0} or d.guserid_wayfair={0} or d.guserid_shopify={0} or d.guserid_sheIn={0} or d.guserid_tiktok={0} or d.guserid_temu={0}) ", userid);
|
|
DateTime SDate = DateTime.Today.AddDays(-30);
|
|
DateTime EDate = DateTime.Today.AddDays(1);
|
|
|
|
if (SDate != null)
|
|
tj += " and a.OrderDate>='" + SDate.ToString("yyyy-MM-dd") + "' ";
|
|
if (EDate != null)
|
|
tj += " and a.OrderDate<'" + EDate.AddDays(1).ToString("yyyy-MM-dd") + "' ";
|
|
|
|
|
|
string sql = @"select OrderDate=CONVERT(varchar(10),a.OrderDate, 120),Solid=sum(isnull(d.Solid,0)*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.state>0 and a.State<3 " + tj + @" group by CONVERT(varchar(10),a.OrderDate, 120)
|
|
|
|
select OrderDate=CONVERT(varchar(10),a.OrderDate, 120),Solid=sum(isnull(d.Solid,0)*b.GoodsNum) from DT_OrderInfo a with(nolock)
|
|
inner join DT_OrderXXInfo e on a.OrderId = e.OrderId
|
|
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 e.StoreId = 6 " + tj + @" group by CONVERT(varchar(10),a.OrderDate, 120)
|
|
|
|
select OrderDate = CONVERT(varchar(10), a.OrderDate, 120), Solid = sum(isnull(d.Solid, 0) * b.GoodsNum) from DT_OrderInfo a with(nolock)
|
|
inner join DT_OrderXXInfo e on a.OrderId = e.OrderId
|
|
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 e.StoreId = 11 " + tj + @" group by CONVERT(varchar(10), a.OrderDate, 120)
|
|
";
|
|
|
|
|
|
var obj2 = new BaseService();
|
|
DataSet ds = obj2.GetReportData2(sql);
|
|
List<OrderSoild> countlist = new List<OrderSoild>();
|
|
List<OrderSoild> countlist2 = new List<OrderSoild>();
|
|
List<OrderSoild> countlist3 = new List<OrderSoild>();
|
|
if (ds != null)
|
|
{
|
|
if(ds.Tables[0]!=null && ds.Tables[0].Rows.Count > 0)
|
|
{
|
|
for(int i = 0; i < ds.Tables[0].Rows.Count; i++)
|
|
{
|
|
var cl = new OrderSoild()
|
|
{
|
|
OrderDate = ds.Tables[0].Rows[i]["OrderDate"].ToString(),
|
|
Solid = string.IsNullOrEmpty(ds.Tables[0].Rows[i]["Solid"].ToString()) ? 0 : Convert.ToDecimal(ds.Tables[0].Rows[i]["Solid"])
|
|
};
|
|
countlist.Add(cl);
|
|
}
|
|
}
|
|
}
|
|
if (ds != null && ds.Tables.Count > 1)
|
|
{
|
|
if (ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0)
|
|
{
|
|
for (int i = 0; i < ds.Tables[1].Rows.Count; i++)
|
|
{
|
|
var cl = new OrderSoild()
|
|
{
|
|
OrderDate = ds.Tables[1].Rows[i]["OrderDate"].ToString(),
|
|
Solid = string.IsNullOrEmpty(ds.Tables[1].Rows[i]["Solid"].ToString()) ? 0 : Convert.ToDecimal(ds.Tables[1].Rows[i]["Solid"])
|
|
};
|
|
countlist2.Add(cl);
|
|
}
|
|
}
|
|
}
|
|
if (ds != null && ds.Tables.Count > 2)
|
|
{
|
|
if (ds.Tables[2] != null && ds.Tables[2].Rows.Count > 0)
|
|
{
|
|
for (int i = 0; i < ds.Tables[2].Rows.Count; i++)
|
|
{
|
|
var cl = new OrderSoild()
|
|
{
|
|
OrderDate = ds.Tables[2].Rows[i]["OrderDate"].ToString(),
|
|
Solid = string.IsNullOrEmpty(ds.Tables[2].Rows[i]["Solid"].ToString()) ? 0 : Convert.ToDecimal(ds.Tables[2].Rows[i]["Solid"])
|
|
};
|
|
countlist3.Add(cl);
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
|
|
|
|
TimeSpan ts = EDate - SDate;
|
|
int days = ts.Days + 1;
|
|
|
|
categories = new string[days];
|
|
|
|
for (int i = 0; i < days; i++)
|
|
{
|
|
|
|
categories[i] = SDate.AddDays(i).ToString("MM月dd日");
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
decimal[] data = new decimal[days];
|
|
decimal[] data2 = new decimal[days];
|
|
decimal[] data3 = new decimal[days];
|
|
for (int j = 0; j < days; j++)
|
|
{
|
|
|
|
if (countlist != null)
|
|
{
|
|
var glist = countlist.Find(n => n.OrderDate == SDate.AddDays(j).ToString("yyyy-MM-dd"));
|
|
|
|
if (glist == null)
|
|
{
|
|
data[j] = 0;
|
|
|
|
}
|
|
else
|
|
data[j] = glist.Solid.Value;
|
|
}
|
|
else
|
|
data[j] = 0;
|
|
if (countlist2 != null)
|
|
{
|
|
var glist2 = countlist2.Find(n => n.OrderDate == SDate.AddDays(j).ToString("yyyy-MM-dd"));
|
|
|
|
if (glist2 == null)
|
|
{
|
|
data2[j] = 0;
|
|
|
|
}
|
|
else
|
|
data2[j] = glist2.Solid.Value;
|
|
}
|
|
else
|
|
data2[j] = 0;
|
|
if (countlist3 != null)
|
|
{
|
|
var glist3 = countlist3.Find(n => n.OrderDate == SDate.AddDays(j).ToString("yyyy-MM-dd"));
|
|
|
|
if (glist3 == null)
|
|
{
|
|
data3[j] = 0;
|
|
|
|
}
|
|
else
|
|
data3[j] = glist3.Solid.Value;
|
|
}
|
|
else
|
|
data3[j] = 0;
|
|
|
|
}
|
|
|
|
var datamd = new DT_OrderCountData();
|
|
|
|
datamd.name = "货物立方--合计";
|
|
|
|
datamd.data = data;
|
|
list.Add(datamd);
|
|
|
|
datamd = new DT_OrderCountData();
|
|
|
|
datamd.name = "货物立方--W仓";
|
|
|
|
datamd.data = data2;
|
|
list.Add(datamd);
|
|
datamd = new DT_OrderCountData();
|
|
|
|
datamd.name = "货物立方--E仓";
|
|
|
|
datamd.data = data3;
|
|
list.Add(datamd);
|
|
md.categories = categories;
|
|
|
|
md.categories = categories;
|
|
|
|
JavaScriptSerializer JsonConvert = new JavaScriptSerializer();
|
|
string postmessage = JsonConvert.Serialize(list);
|
|
md.Data = postmessage;
|
|
|
|
|
|
return md;
|
|
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region 发货立方
|
|
[WebMethod(EnableSession = true)]
|
|
public DT_OrderNum GetOrderCountGoodsSoildMonth_New()
|
|
{
|
|
PagesNew.Login(this.Session);
|
|
int CompanyId = Convert.ToInt32(Session["CompanyId"]);
|
|
int userid = Convert.ToInt32(Session["UserId"]);
|
|
var obj = new DD_OrderData();
|
|
|
|
|
|
string[] categories = null;
|
|
DT_OrderNum md = new DT_OrderNum();
|
|
var list = new List<DT_OrderCountData>();
|
|
|
|
string tj = string.Format(" and ( d.muserid={0} or d.guserid_ebay={0} or d.guserid_wlmart={0} or d.guserid_wayfair={0} or d.guserid_shopify={0} or d.guserid_sheIn={0} or d.guserid_tiktok={0} or d.guserid_temu={0}) ", userid);
|
|
DateTime SDate = Convert.ToDateTime(DateTime.Today.ToString("yyyy-MM-01")).AddMonths(-11);
|
|
DateTime EDate = DateTime.Today.AddDays(1);
|
|
|
|
if (SDate != null)
|
|
tj += " and a.OrderDate>='" + SDate.ToString("yyyy-MM-dd") + "' ";
|
|
if (EDate != null)
|
|
tj += " and a.OrderDate<'" + EDate.AddDays(1).ToString("yyyy-MM-dd") + "' ";
|
|
|
|
|
|
|
|
string sql = @"select OrderDate=cast(DATEPART(month,OrderDate) as nvarchar(10)),Solid=sum(isnull(d.Solid,0)*b.GoodsNum) 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 a.state>0 and a.State<3 " + tj + @" group by DATEPART(month,OrderDate)
|
|
|
|
--W仓
|
|
select OrderDate = cast(DATEPART(month, OrderDate) as nvarchar(10)),Solid = sum(isnull(d.Solid, 0) * b.GoodsNum) from DT_OrderInfo a
|
|
inner join DT_OrderXXInfo e on a.OrderId = e.OrderId
|
|
inner join DT_OrderGoods b on a.OrderId = b.OrderId
|
|
inner join HW_GoodsDetail c on b.DetailId = c.DetailId
|
|
inner join HW_GoodsInfo d on c.GoodsId = d.GoodsId
|
|
where a.state > 0 and a.State < 3 and e.StoreId = 6 " + tj + @" group by DATEPART(month, OrderDate)
|
|
|
|
|
|
--E仓
|
|
select OrderDate = cast(DATEPART(month, OrderDate) as nvarchar(10)),Solid = sum(isnull(d.Solid, 0) * b.GoodsNum) from DT_OrderInfo a
|
|
inner join DT_OrderXXInfo e on a.OrderId = e.OrderId
|
|
inner join DT_OrderGoods b on a.OrderId = b.OrderId
|
|
inner join HW_GoodsDetail c on b.DetailId = c.DetailId
|
|
inner join HW_GoodsInfo d on c.GoodsId = d.GoodsId
|
|
where a.state > 0 and a.State < 3 and e.StoreId = 11 " + tj + @" group by DATEPART(month, OrderDate)
|
|
";
|
|
|
|
|
|
var obj2 = new BaseService();
|
|
DataSet ds = obj2.GetReportData2(sql);
|
|
List<OrderSoild> countlist = new List<OrderSoild>();
|
|
List<OrderSoild> countlist2 = new List<OrderSoild>();
|
|
List<OrderSoild> countlist3 = new List<OrderSoild>();
|
|
|
|
|
|
if (ds != null)
|
|
{
|
|
if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
|
|
{
|
|
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
|
|
{
|
|
var cl = new OrderSoild()
|
|
{
|
|
OrderDate = ds.Tables[0].Rows[i]["OrderDate"].ToString(),
|
|
Solid = string.IsNullOrEmpty(ds.Tables[0].Rows[i]["Solid"].ToString()) ? 0 : Convert.ToDecimal(ds.Tables[0].Rows[i]["Solid"])
|
|
};
|
|
countlist.Add(cl);
|
|
}
|
|
}
|
|
}
|
|
if (ds != null && ds.Tables.Count > 1)
|
|
{
|
|
if (ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0)
|
|
{
|
|
for (int i = 0; i < ds.Tables[1].Rows.Count; i++)
|
|
{
|
|
var cl = new OrderSoild()
|
|
{
|
|
OrderDate = ds.Tables[1].Rows[i]["OrderDate"].ToString(),
|
|
Solid = string.IsNullOrEmpty(ds.Tables[1].Rows[i]["Solid"].ToString()) ? 0 : Convert.ToDecimal(ds.Tables[1].Rows[i]["Solid"])
|
|
};
|
|
countlist2.Add(cl);
|
|
}
|
|
}
|
|
}
|
|
if (ds != null && ds.Tables.Count > 2)
|
|
{
|
|
if (ds.Tables[2] != null && ds.Tables[2].Rows.Count > 0)
|
|
{
|
|
for (int i = 0; i < ds.Tables[2].Rows.Count; i++)
|
|
{
|
|
var cl = new OrderSoild()
|
|
{
|
|
OrderDate = ds.Tables[2].Rows[i]["OrderDate"].ToString(),
|
|
Solid = string.IsNullOrEmpty(ds.Tables[2].Rows[i]["Solid"].ToString()) ? 0 : Convert.ToDecimal(ds.Tables[2].Rows[i]["Solid"])
|
|
};
|
|
countlist3.Add(cl);
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
|
|
int days = 12;
|
|
|
|
categories = new string[days];
|
|
|
|
for (int i = 0; i < days; i++)
|
|
{
|
|
|
|
categories[i] = SDate.AddMonths(i).ToString("MM月");
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
decimal[] data = new decimal[days];
|
|
decimal[] data2 = new decimal[days];
|
|
decimal[] data3 = new decimal[days];
|
|
for (int j = 0; j < days; j++)
|
|
{
|
|
|
|
|
|
var glist = countlist.Find(n => n.OrderDate == SDate.AddMonths(j).Month.ToString());
|
|
|
|
if (glist == null)
|
|
{
|
|
data[j] = 0;
|
|
|
|
}
|
|
else
|
|
data[j] = glist.Solid.Value;
|
|
if (countlist2 != null)
|
|
{
|
|
var glist2 = countlist2.Find(n => n.OrderDate == SDate.AddMonths(j).Month.ToString());
|
|
|
|
if (glist2 == null)
|
|
{
|
|
data2[j] = 0;
|
|
|
|
}
|
|
else
|
|
data2[j] = glist2.Solid.Value;
|
|
}
|
|
else
|
|
data2[j] = 0;
|
|
if (countlist3 != null)
|
|
{
|
|
var glist3 = countlist3.Find(n => n.OrderDate == SDate.AddMonths(j).Month.ToString());
|
|
|
|
if (glist3 == null)
|
|
{
|
|
data3[j] = 0;
|
|
|
|
}
|
|
else
|
|
data3[j] = glist3.Solid.Value;
|
|
}
|
|
else
|
|
data3[j] = 0;
|
|
}
|
|
|
|
var datamd = new DT_OrderCountData();
|
|
|
|
datamd.name = "货物立方--合计";
|
|
|
|
datamd.data = data;
|
|
list.Add(datamd);
|
|
|
|
datamd = new DT_OrderCountData();
|
|
|
|
datamd.name = "货物立方--W仓";
|
|
|
|
datamd.data = data2;
|
|
list.Add(datamd);
|
|
datamd = new DT_OrderCountData();
|
|
|
|
datamd.name = "货物立方--E仓";
|
|
|
|
datamd.data = data3;
|
|
list.Add(datamd);
|
|
md.categories = categories;
|
|
|
|
JavaScriptSerializer JsonConvert = new JavaScriptSerializer();
|
|
string postmessage = JsonConvert.Serialize(list);
|
|
md.Data = postmessage;
|
|
|
|
|
|
return md;
|
|
|
|
}
|
|
#endregion
|
|
|
|
|
|
}
|
|
}
|