//用户基础信息表
select Id as id, (case Status when  0 then 1 when -1 then 0 else 0 end ) as status, RealName as username, Mobile as phone, ReferralUserId as hidden_level, Areas as address,
ParentUserId as pid, CONCAT('0',REPLACE(ParentNav,',,',','))  as pid_path, MakerCode as user_code, HeadPhoto as avatar,
UserLevel as user_level, AuthFlag as real_status, MerchantType as is_merchant_maker, Remark as remark, CreateDate as create_time, UpdateDate as update_time
FROM Users

//用户预设职级记录表
SELECT Id as id, UserId as user_id, CreateDate as create_time, CreateDate as start_time, UpdateDate as end_time, Rank as preset_level, QueryCount as create_by_id
FROM UserRankWhite

//用户实名信息表
select Id as id, Id as user_id, RealName as real_name, CertId as id_card, HandCertImage as id_card_hand, CertReverseImage as id_card_rear,
 CertFrontImage as id_card_front, AuthDate as create_time
FROM Users where CertId is not null and CertId<>'' and RealName<>''

//用户银行卡表
select Id as user_id, SettleBankName as bank_name, Mobile as bank_phone, SettleBankCardNo as bank_code, BankCardPositiveImage as bank_pic,
 SeoTitle as bank_open, SettleBankCardName as bank_card_name, AuthDate as create_time
FROM Users where SettleBankCardNo is not null and SettleBankName<>''

//用户收货地址表
select Id as id, UserId as user_id,
SUBSTRING_INDEX(Areas, ',', 1) AS province,
SUBSTRING_INDEX(SUBSTRING_INDEX(Areas, ',', -2), ',', 1) AS city,
SUBSTRING_INDEX(Areas, ',', -1) AS area,
address, Mobile as phone, RealName as consignee, IsDefault as is_default, CreateDate as create_time, UpdateDate as update_time
 FROM UserAddress

//用户账户表
SELECT Id as id, UserId as user_id,CreateDate as create_time, UpdateDate as update_time,
TotalAmount as total_amount, FreezeAmount as frozen_amount, WithdrawAmount as cash_withdrawal_amount, BalanceAmount as amount, ToChargeAmount as advance_amount, LeaderReserve as leader_reserve, LeaderBalanceAmount as leader_balance_amount,
LeaderToChargeAmount as leader_advance_amount, OperateToChargeAmount as partner_advance_amount
from UserAccount

//用户预扣款表
SELECT Id as id, UserId as user_id,CreateDate as create_time, UpdateDate as update_time,
	ChargeAmount	as amount, Remark as remark, SeoTitle as create_by, Status as status,
	CASE
	WHEN ChargeType = 1 AND Kind =0 THEN 0
	WHEN ChargeType = 2 AND Kind =0 THEN 1
	WHEN ChargeType = 1 AND Kind =1 THEN 3
	WHEN ChargeType = 1 AND Kind =2 THEN 4
	END advance_type,
	CASE Status WHEN -1 THEN 1 ELSE 0  END del_flag
	FROM ToChargeBackRecord ORDER BY advance_type desc

//用户提现申请表
select Id as id, UserId as user_id,CreateDate as create_time, UpdateDate as update_time,
CASE TradeType WHEN 2 THEN 0 ELSE TradeType END withdrawal_type, Status as status, CASE QueryCount WHEN 0 THEN 0 ELSE 1  END apply_status,
SettleBankCardNo as bank_code, SettleBankName as bank_name, CashOrderNo as apply_no, UpdateMan as channel_name, TradeAmount as apply_amount, ActualTradeAmount as real_amount,
TradeFee as service_amount, ManageFee as tax_amount, ResData as result, Remark as remark
FROM UserCashRecord

//用户余额记录日志表
select a.Id as id, a.UserId as user_id,a.CreateDate as create_time, a.UpdateDate as update_time,
a.ProductType as brand_id, a.ChangeType as variation_type, c.Kind as kind,
a.ChangeAmount as amount, a.BeforeTotalAmount as before_total_amount, a.AfterTotalAmount as after_total_amount, a.BeforeBalanceAmount as before_amount, a.AfterBalanceAmount as after_amount, a.Remark as remark
FROM UserAccountRecord a left join ChangeTypes c on c.id = a.ChangeType

//盟主表
SELECT Id as id, UserId as user_id,CreateDate as create_time, UpdateDate as update_time,
LeaderLevel as leader_type, LastBuyDate as last_buy_date, ExpiredDate as expired_date, CASE LeaderLevel WHEN 1 THEN 5 WHEN 2 THEN 7 END leader_level
FROM Leaders

//盟主金额变动表
SELECT Id as id, UserId as user_id,CreateDate as create_time, UpdateDate as update_time,
Sort as variation_type, ChangeAmt as amount, BeforeAmt as before_amount, AfterAmt as after_amount, Remark as remark, SeoDescription as variation_cause,
SourceUserId as source_user_id, OrderId as order_id, CASE ChangeType WHEN 1 THEN 'add' WHEN 2 THEN 'sub' END kind
FROM LeaderReserveRecord

//合伙人表
SELECT a.Id as id, a.Id as user_id, CASE a.UserType WHEN 1 THEN 0 WHEN 2 THEN 1 END partner_type, 9 as partner_level,
b.CreateDate as create_time, b.UpdateDate as expired_date,
c.ValidForGetAmount as withdrawal_amount, c.TotalAmt as amount, c.ValidAmount as store_amount
FROM Users a left join OperatorRankWhite b on a.Id = b.UserId
left join KxsOpServer.UserAccount c on a.Id = c.UserId
WHERE UserType > 0

//商品相关
SELECT Id as id, CreateDate as create_time, UpdateDate as update_time,
CASE ProductKind WHEN 1 THEN 0 ELSE 1 END goods_type, ListPicPath as pic_url, Sort as sort, Status as status, SUBSTRING_INDEX(NormJson, '#cut#', 1) as spec_list,
ProductName as goods_name, Details as subtitle, Contents as details, ColId as col_id, Price as price, PublishDate as up_time, LimitCount as limit_count, StartBuyCount as start_by_count
FROM Products
-- 商品图片
SELECT Id as id, CreateDate as create_time, UpdateDate as update_time,
ListPicPath as url, Id as goods_id
FROM Products

-- 商品SKU
SELECT a.Id as id, a.CreateDate as create_time, a.UpdateDate as update_time, a.ProductId as goods_id, b.NormJson as specifications,
a.Price as price, a.Stock as stock_num, a.BuyCount as sales, a.LimitCount as limit_count, a.StartBuyCount as start_by_count
FROM ProductNorm a left join Products b on a.ProductId = b.id