//用户基础信息表
select Id as id, (case Status when  0 then 1 when -1 then 0 else 1 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,
ChargeType as advance_type, Kind as kind, Field1 as ext_field,
	CASE Status WHEN -1 THEN 1 ELSE 0  END del_flag
	FROM ToChargeBackRecord

//用户提现申请表
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, case c.Kind when 1 then 'add' ELSE 'sub' end 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, a.UserType -1 as  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 a.Id as id, a.UserId as user_id, a.CreateDate as create_time, a.UpdateDate as update_time,
UseAmount as amount, BeforeAmount as before_amount, AfterAmount as after_amount, case OperateType when 0 then 'add' when 1 then 'add' when 2 then 'sub' end kind,
AfterValidForGetAmount as after_withdrawal_mount, AfterTotalAmt as after_total_mount, AfterValidAmount as after_store_amount, Remark as remark, ChangeTypeId as change_type,
UseValidAmount as use_store_amount, UseTotalAmt as use_total_amount, UseValidForGetAmount as use_withdrawal_mount
FROM AmountRecordNew a

//商品相关
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

//每日晨会表
SELECT
Id as id, CreateDate as create_time, UpdateDate as update_time,
Title as title, Photo as photo, Lecturer as lecturer, StudyPerson as study_person, SeoDescription as url, Url as app_link, SeoKeyword as page_link, IsBanner as is_banner, BannerPhoto as banner_photo
from SchoolMorningMeet where status > -1
------
SELECT
Id as id, UserId as user_id, MeetId as morning_id
from SchoolMorningMeetLog

//创客学堂表
select Id as id, CreateDate as create_time, UpdateDate as update_time, QueryCount as study_person,
SeoTitle as lecturer, SeoKeyword as menu_id, Url as url, ListPic as list_pic, Detail as detail, Title as title
FROM SchoolMakerStudy where `Status` > -1
select Id as id,  CreateDate as create_time, UpdateDate as update_time, Sort as sort,
ParentId as pid, ColDetail as col_detail, ColPicPath as col_pic_path, ColName as col_name, ColId as col_id
FROM Col where id in(21,22,23,25,26,27,30)

//市场活动
select Id as id, CreateDate as create_time, UpdateDate as update_time, Sort as sort,
Title as title, case ColId when '007001' then 2 when '007002' then 1 end menu_id, PicPath as pic_path, Contents as contents, Url as url, Status as status
FROM Advertisment where ColId in ('007001', '007002')

//服务中心
select Id as id, CreateDate as create_time, UpdateDate as update_time, Sort as sort,
Title as title, Content as content, CategoryId as menu_id, CreateMan as create_by
from ServiceCenter
 where `Status` > -1

select Id as id, CreateDate as create_time, UpdateDate as update_time, Sort as sort,
ColName as title
FROM Col where id in (3,4,5,6,32,33,34,35,36,38)

//品牌表
SELECT a.Id as id, a.CreateDate as create_time, a.UpdateDate as update_time, a.Sort as sort,
a.Name as name, case a.Kind when 1 then 0 when 2 then 1 end brand_type, a.PosPrice as pos_price, a.DefaultDeposit as default_pledge, a.CountOfBox as count_of_box,ActTradeAmount as act_amount,FluxAmount as flux_amount, FluxProfit as flux_profit,
 c.ColPicPath
from KqProducts a
left join KxsBsServer.Col c on a.AdColId = c.ColId
where a.`Status` > 0

//机具表
select a.Id as id, a.BuyUserId as user_id,a.CreateDate as create_time, a.UpdateDate as update_time,
a.PosSn as pos_sn, a.StoreId as warehouse_id, a.BindMerchantId as merchant_id, a.BrandId as brand_id, a.PreUserId as pre_user_id, a.BatchNo as batch_no,
(case when a.IsPurchase = 0 && a.RecycEndDate > NOW() && a.ActivationState = 1 then 1 ELSE 0 end) as  recycle_status, a.RecycEndDate as recycle_end_time,
a.PosSnType as machine_type, ifnull(a.PrizeParams, 299) as cash_pledge, case when a.SeoKeyword > 1000 then a.SeoKeyword / 100 ELSE a.SeoKeyword end as source_pledge,
a.BindingTime as bind_time, a.ActivationTime as act_time, a.DownFee as machine_ratio, a.ActivationState as is_act, a.BindingState as bind_status,
a.IsFirst as is_first, a.LeaderUserId as  leader_id, a.OpId as  partner_id, TransferTime as delivery_time, UpFeeFlag as up_fee_flag, DownFeeFlag as down_fee_flag,
IsSupplement as is_supply, IsOpAct as is_op_act, case when a.UserId = 0 then 0 when a.Status = -1 then -1 ELSE 1 end status,
(case when a.IsPurchase = 0 && a.RecycEndDate < NOW() && a.BindingState = 0 then 1 ELSE 0 end) as return_status,
a.CreditTrade as credit_trade, a.DebitCardTrade as debit_card_trade, a.ScanQrTrade as scan_qr_trade,
b.StoreName as warehouse_name
FROM PosMachinesTwo a LEFT JOIN StoreHouse b on  a.StoreId = b.id



//仓库表
select a.Id as id, a.UserId as user_id,a.CreateDate as create_time, a.UpdateDate as update_time,
StoreNo as warehouse_no, StoreName as warehouse_name, BrandId as brand_id, ProductName as brand_name,a.Address as warehouse_address,
ManageUserId as warehouse_admin_id, ManagerEmail as admin_email, ParentStoreId as pid,
SUBSTRING_INDEX(a.Areas, ',', 1) AS province,
SUBSTRING_INDEX(SUBSTRING_INDEX(a.Areas, ',', -2), ',', 1) AS city,
SUBSTRING_INDEX(a.Areas, ',', -1) AS area,
StoreStatus as status, case a.StoreKind when 0 then 1 when 1 then 0 when 2 then 2  end warehouse_type, FilePath as file_url, ManageMobile as warehouse_phone,
a.CreateMan as create_by ,Deposit as pledge_amount, a.Remark as remark, a.UpdateMan as update_by, u.RealName as contacts
from StoreHouse a
LEFT JOIN Users u on a.UserId = u.Id
-- 仓库库存统计表
SELECT a.Id as id, a.UserId as user_id,a.CreateDate as create_time, a.UpdateDate as update_time,
TotalNum as stock_total, LaveNum as stock_num, OutNum as delivery_num, a.Id as warehouse_id
from StoreHouse a
-- 仓库额度表
SELECT s.Id as id, s.UserId as user_id, s.CreateDate as create_time, s.UpdateDate as update_time,
a.ValidAmount as valid_amount, a.FixedAmount as fixed_amount, a.TempAmount as for_amount, TempAmountForBalance as yue_for_amount, ifnull(b.PromissAmount,0) as credit_amount
from StoreHouse s
left join UserAccount a on a.UserId = s.UserId
left JOIN StoreHouseAmountPromiss b on s.UserId = b.ToUserId and b.status = 1
GROUP BY s.UserId
-- 仓库担保记录
SELECT a.Id as id, a.CreateDate as create_time, a.UpdateDate as update_time, case a.Status when  1 then 0 when -1 then 1 end  del_flag,
a.PromissAmount as credit_amount, a.FromUserId as credit_user_id, a.ToUserId as to_credit_user_id, a.SeoDescription as remark,
u.RealName as username, u.MakerCode as user_code
FROM StoreHouseAmountPromiss a
LEFT JOIN Users u on u.id = a.ToUserId WHERE a.`Status` < 2

//机具回收表
SELECT  a.Id as id, a.UserId as user_id,a.CreateDate as create_time, a.UpdateDate as update_time,
case a.PostType when 2 then 0 ELSE 1 end get_mode, a.RecycOrderNo as order_sn, a.RecycStoreId as warehouse_id,
a.ErpCode as  track_no, a.BrandId as brand_id, b.`Name` as brand_name,
u.RealName as username, u.Mobile as phone, u.MakerCode as user_code,
s.UserId as warehouse_user_id
FROM RecycMachineOrder a
LEFT JOIN KqProducts b on a.BrandId = b.Id
left JOIN Users u on a.UserId = u.id
LEFT JOIN StoreHouse s on s.id = a.RecycStoreId
-------
SELECT a.Id as id, a.CreateDate as create_time, a.UpdateDate as update_time,
a.SnNo as pos_sn, a.OrderId as order_id, a.BrandId as brand_id, a.ResultDate as confirm_time, a.CancelDate as cancel_time,
case a.Status when -1 then 3 when 0 then 0 when 1 then 1 when 2 then 2 end status, b.RecycEndDate as recycle_end_time
FROM RecycMachineOrderPos a LEFT JOIN PosMachinesTwo b on a.SnNo = b.PosSn

//押金调整记录表
SELECT  a.Id as id, a.UserId as user_id, a.CreateDate as create_time, a.UpdateDate as update_time,
 a.SeoTitle as before_pledge, a.DepositAmount as after_pledge, a.ReturnNote as result, a.sort as brand_id,
a.SnNo as pos_sn
FROM MerchantDepositSet a

//费率调整记录
SELECT  a.Id as id, a.UserId as user_id, a.CreateDate as create_time, a.UpdateDate as update_time,
case a.status when -1 then 0 when 1 then 1 end as status, a.SeoDescription as remark, a.MerchantId as merchant_id,
 a.ChangeFee as ratio, a.sort as brand_id, a.PosId as machine_id, a.PosUserId as pos_user_id,
a.PosSn as pos_sn
FROM PosMachinesFeeChangeRecord a
//机具券
SELECT  a.Id as id, a.UserId as user_id, a.CreateDate as create_time, a.UpdateDate as update_time,
LeaderUserId as leader_id, OpId as partner_id, ExchangeCode as ticket_code,
CASE when IsUse = 1 then 1 when IsLock = 1 then 2 ELSE 0 end status,
a.QueryCount-1 as ticket_type
FROM PosCoupons a
//机具券划拨订单
SELECT a.Id as id, a.CreateDate as create_time, a.UpdateDate as update_time,
a.FromUserId as send_user_id, a.ToUserId as to_user_id, a.ChangeCount as transfer_num, a.QueryCount-1 as ticket_type, a.OrderNo as order_sn,
u.RealName as send_user_name, u.MakerCode as send_user_code,
uu.RealName as to_user_name, uu.MakerCode as to_user_code
FROM PosCouponOrders a
left join Users u on u.id = a.FromUserId
left join Users uu on uu.id = a.ToUserId
//机具券划拨订单明细
SELECT a.Id as id, a.CreateDate as create_time, a.UpdateDate as update_time,
a.PosCouponId as ticket_id,a.OrderNo as order_sn, b.ExchangeCode as ticket_code
FROM PosCouponRecord a left join PosCoupons b on a.PosCouponId = b.id
//商户表
SELECT a.Id as id, b.BuyUserId as user_id, a.CreateDate as create_time, a.UpdateDate as update_time,
a.MerchantNo as merchant_no, a.MerchantName as merchant_name, a.MerIdcardNo as merchant_card, a.MerchantMobile as merchant_phone,
a.BrandId as brand_id, a.UserId as maker_user_id, a.MerUserType as is_maker, b.id as machine_id, a.StandardStatus as standard_status, a.StandardMonths as standard_months,
a.Remark as remark
FROM PosMerchantInfo a LEFT JOIN PosMachinesTwo b on a.KqSnNo = b.PosSn

--创客消息表
SELECT Id as id, CreateDate as create_time, UpdateDate as update_time,
UserId as user_id, MsgType as msg_type, BgPic as bg_pic, Title as title, Content as content, Url as url, Summary as summary, ReadFlag as read_flag
FROM MsgPersonal

-- 系统消息表
SELECT Id as id, CreateDate as create_time, UpdateDate as update_time,EffectStartDate as start_time, EffectEndDate as end_time,
MsgType as msg_type, BgPic as bg_pic, Title as title, Content as content, Url as url, Summary as summary, PushType as push_type, PushTime as push_time
FROM MsgPlacard
-- 系统消息已读表
SELECT Id as id, UserId as user_id, PlacardId as msg_id FROM MsgPlacardRead

-- APP弹窗表
SELECT Id as id, CreateDate as create_time, UpdateDate as update_time,EffectStartDate as start_time, EffectEndDate as end_time, case Status when  0 then 0 when -1 then 1 else 0 end  del_flag,
MsgType - 1 as msg_type, BgPic as bg_pic, Title as title, Content as content, Url as url,
BtnText as btn_text, TimeByDay as time_by_day, IsWeekend as is_weekend, UrlParam as url_param
FROM MsgAlert
--用户结算卡变更记录
select a.Id as id, a.UserId as user_id, a.CreateDate as create_time, a.UpdateDate as update_time,
BeforeCardNo as before_card_no, AfterCardNo as after_card_no
FROM SettlementCardChangeRecord a

--分期表同步
SELECT a.Id as id, a.UserId as user_id, a.CreateDate as create_time, a.UpdateDate as update_time, case `Status` when -1 then 1 ELSE 0 end del_flag, case  when `Status` = 0 and QueryCount = 0 then 0 when `Status` = 1 and TimeNumber = QueryCount then 2 ELSE 1 end status,
SeoTitle as create_by, SeoKeyword as stage_sn, TotalAmount as stage_amount, TimeNumber as number, StartDate as deduct_start_time, Remark as remark, ChargeAmount as deduct_amount
FROM ToChargeByStage a WHERE status > -1

-- 分期扣款明细
SET @row_number = 0;
SELECT a.Id as id, a.CreateDate as create_time, a.UpdateDate as update_time, case `Status` when -1 then 1 ELSE 0 end del_flag, `Status` as status,
(@row_number:= IF(@last_field1 = ParentId, @row_number + 1, 1)) AS this_number,
 @last_field1 := ParentId,
SeoTitle as create_by, Remark as remark, ParentId as stage_id, ChargeAmount as amount, StartDate as deduct_time
FROM ToChargeBackRecordSub a WHERE status > -1
ORDER BY Id, ParentId

--未绑定划拨记录
select a.Id as id,a.CreateDate as create_time, a.UpdateDate as update_time,
a.SnNo as machine_sn, a.BrandId as brand_id, a.FromUserId as send_user_id, a.ToUserId as to_user_id, m.id as machine_id, m.PosSnType as machine_type, 0 as transfer_type,
u.RealName as to_user_name, u.MakerCode as to_user_code
from UserStoreChange a
left join Users u on a.ToUserId = u.Id
left join PosMachinesTwo m on a.SnNo = m.PosSn
WHERE a.TransType = 11

--机具轨迹记录表
select a.Id as id,a.CreateDate as create_time, a.UpdateDate as update_time,
m.id as machine_id, a.BrandId as brand_id, a.FromUserId as before_user_id, ToUserId as after_user_id, TransType as track_type, a.OpId as partner_id
FROM StoreChangeHistory a
left join PosMachinesTwo m on a.SnNo = m.PosSn
where m.id is not null

--商城订单表
SELECT  a.Id as id, a.UserId as user_id, a.CreateDate as create_time, a.UpdateDate as update_time,
a.OrderNo as order_sn, case a.DeliveryType when 1 then 1 when 2 then 0 else 0 end get_mode, case a.PayMode when 1 then 1 when 3 then 0 when 2 then 2 when 4 then 3 end  pay_type,
PayDate as pay_time, SendDate as send_time, a.Remark as remark, RealName as consignee, Mobile as phone, Address as address, ErpCode as erp_sn, ErpName as erp_name,
case when a.Status = -1 then 4 when a.Status = 0 then 0 when a.Status = 1 then 1 when a.Status = 2 then 3 when a.Status = 3 then 2 when a.Status = 4 then 2 end  status,
TotalPrice as total_price, TradeNo as trade_no, TotalPrice as pay_price, StoreId as warehouse_id, TopUserId as top_user_id,
RefundReason as return_reason, UpdateMan as update_by, RefundActAmount as  return_price, case when a.`Status` = 4 then 2  when a.RefundStatus = 1 then 2 else 0 end  return_status
FROM Orders a
where a.Sort = 0
ORDER BY a.CreateDate desc limit 1000

-- 订单详情表
SELECT  a.Id as id, a.UserId as user_id, a.CreateDate as create_time, a.UpdateDate as update_time,
OrderId as order_id, ProductId as goods_id, ProductName as goods_name, ProductPrice as goods_price, ProductCount as number, ProductPhoto as pic_url, ifnull(NormDetail, '默认') as specifications, ifnull(NormId, 0) as sku_id
FROM OrderProduct a
limit 1000



























