## 20170721工作內容 #### 銷售出庫業務梳理 + 獲取銷售方式： > SELECT FTypeID,FInterID FItemID, FID FNumber,FName,FSpec FROM t_SubMessage Where FInterID>0 AND FDeleted=0 And FTypeID=101 AND (FInterID<> 20296) And (FInterID<> 20296) ORDER BY FID > SELECT TOP 1000 [FBrNo] ,[FInterID] ,[FID] ,[FParentID] ,[FName] ,[FTypeID] ,[FDetail] ,[FDeleted] ,[FModifyTime] ,[FSystemType] ,[FSpec] ,[UUID] FROM [AIS20170612094709].[dbo].[t_SubMessage] t1 Where t1.FInterID>0 AND t1.FDeleted=0 And t1.FTypeID=101 AND (t1.FInterID<> 20296) And (t1.FInterID<> 20296) ORDER BY t1.FID + 獲取購貨單位： + SELECT TOP 50 t1.FItemID ,t1.FDeleted ,t1.FNumber,t1.FName,t1.FDetail FROM t_Item t1 with(index (uk_Item2)) LEFT JOIN t_Organization x2 ON t1.FItemID = x2.FItemID WHERE FItemClassID = 1 AND t1.FDetail=1 AND t1.FDeleteD=0 ORDER BY t1.FNumber + SELECT TOP 1000 t1.[FItemID] ,t1.[FItemClassID] ,t1.[FExternID] ,t1.[FNumber] ,t1.[FParentID] ,t1.[FLevel] ,t1.[FDetail] ,t1.[FName] ,t1.[FUnUsed] ,t1.[FBrNo] ,t1.[FFullNumber] ,t1.[FDiff] ,t1.[FDeleted] ,t1.[FShortNumber] ,t1.[FFullName] ,t1.[UUID] ,t1.[FGRCommonID] ,t1.[FSystemType] ,t1.[FUseSign] ,t1.[FChkUserID] ,t1.[FAccessory] ,t1.[FGrControl] ,t1.[FModifyTime] ,t1.[FHavePicture] FROM [AIS20170612094709].[dbo].[t_Item]t1 with(index (uk_Item2)) LEFT JOIN [AIS20170612094709].[dbo].t_Organization x2 ON t1.FItemID = x2.FItemID WHERE t1.[FItemClassID] = 1 AND t1.[FDetail]=1 AND t1.[FDeleted]= 0 ORDER BY t1.[FNumber] DESC #### 打卡結算顯示微信與支付寶分別收款 > 1. 添加列 > 2. 部署api > 3. 部署打卡歷史記錄頁面 > 4. 部署打卡提示 + 添加列: > ALTER TABLE `crm_store_employee_clockon` ADD COLUMN `wx_pay_amount` DECIMAL (11,2) DEFAULT '0.00' COMMENT '微信支付總額', ADD COLUMN `ali_pay_amount` DECIMAL (11,2) DEFAULT '0.00' COMMENT '支付寶支付總額'; + 批量添加微信與支付寶支付總額 > UPDATE crm_store_employee_clockon sec SET sec.`wx_pay_amount` = (SELECT IFNULL( SUM( CASE WHEN pai.`pay_type` = 1 THEN pai.`amount` ELSE 0.00 END ), 0.00 ) AS wx_pay_amount FROM crm_pay_info pai WHERE pai.`store_id` = sec.store_id AND pai.`pay_status` = 2 AND pai.`pay_time` >= sec.start_time AND pai.`pay_time` < sec.end_time), sec.`ali_pay_amount` = (SELECT IFNULL( SUM( CASE WHEN pai.`pay_type` = 2 THEN pai.`amount` ELSE 0.00 END ), 0.00 ) AS ali_pay_amount FROM crm_pay_info pai WHERE pai.`store_id` = sec.store_id AND pai.`pay_status` = 2 AND pai.`pay_time` >= sec.start_time AND pai.`pay_time` < sec.end_time)