123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224 |
- <?php
- namespace app\admin\controller\finance;
- use app\common\Util;
- use app\model\SysCategory;
- use app\model\SysDept;
- use support\Db;
- use support\Request;
- class WriteOffController
- {
- public function list(Request $request)
- {
- $page = $request->get('page',1);
- $pageSize = $request->get('pageSize',20);
- $days = $request->get('days',[]);
- $salePremisesId = $request->get('sale_premises_id','');
- $writeOffPremisesId = $request->get('write_off_premises_id','');
- $categoryId = $request->get('category_id','');
- $goodsName = $request->get('goods_name','');
- if (!empty($days)) {
- $days[0] = strtotime($days[0]);
- $days[1] = strtotime($days[1]);
- if (date('m', $days[0]) != date('m', $days[1])) {
- return json_fail('暂不支持跨月查询');
- }
- $month = date('Ym', $days[0]);
- $days[1] = strtotime(date('Y-m-d', $days[1]) . " 23:59:59");
- } else {
- $month = date('Ym');
- }
- if(!Util::schema()->hasTable("data_used_{$month}")){
- return json_fail('暂无数据');
- }
- $categorySearchIds = '';
- if (!empty($categoryId)){
- $categorySearchPath = SysCategory::where('category_id',$categoryId)->value('category_super_path');
- $categorySearchPath .= '#'. $categoryId.'#';
-
- $categorySearchIds = SysCategory::where('category_super_path','like',$categorySearchPath.'%')->pluck('category_id')->toArray();
- $categorySearchIds = array_merge($categorySearchIds,[$categoryId]);
- $categorySearchIds = implode(',',$categorySearchIds);
- }
-
- $where = " where 1 ";
- if (!empty($days)) {
- $where .= "and du.data_used_addtimes between {$days[0]} and {$days[1]} ";
- }
- if(!empty($salePremisesId)){
- $where .= " and JSON_EXTRACT(du.join_data_used_object_json,'$.salesman_premises_id') = {$salePremisesId} ";
- }
- if (!empty($writeOffPremisesId)){
- $where .= " and du.join_data_used_dept_id = {$writeOffPremisesId}";
- }
- if (!empty($categorySearchIds)){
- $where .= " and g.join_goods_category_id in({$categorySearchIds})";
- }
- if (!empty($goodsName)){
- $where .= " and g.goods_name like '%{$goodsName}%' ";
- }
- $totalSql = "
- select
- count(*) as total
- from app_data_used_{$month} as du
- left join app_goods as g on JSON_EXTRACT(du.join_data_used_object_json,'$.goods_id') = g.goods_id
- {$where}";
- $total = Db::select($totalSql);
- $recordStart = ($page - 1) * 20;
- $sql = "select
- g.goods_name,g.goods_sales_price,g.join_goods_category_id,gs.goods_sku_specs_json,wu.user_name as wu_username, su.user_name as su_username,du.data_used_addtimes,
- JSON_EXTRACT(du.join_data_used_object_json,'$.charge.charge_premises') as write_off_premises, su.join_user_dept_id as premises_id,du.data_used_count as nbr,
- ssd.dept_id as ssd_dept_id, ssd.dept_name as ssd_dept_name
- from app_data_used_{$month} as du
- left join app_goods as g on REPLACE(JSON_EXTRACT(du.join_data_used_object_json,'$.goods_id'),'\"','') = CAST(g.goods_id AS CHAR)
- left join app_goods_sku as gs on JSON_EXTRACT(du.join_data_used_object_json,'$.goods_sku_id') = gs.goods_sku_id
- left join app_sys_user as wu on du.join_data_used_user_id = wu.user_id
- left join app_sys_user as su on JSON_EXTRACT(du.join_data_used_object_json,'$.salesman_id') = su.user_id
- left join app_sys_dept as ssd on JSON_EXTRACT(du.join_data_used_object_json,'$.salesman_premises_id') = ssd.dept_id
- {$where}
- order by du.data_used_addtimes desc
- limit " . $recordStart . ',' . $pageSize;
- $rows = Db::select($sql);
- foreach ($rows as &$row){
- $row->salesman_premises = '';
- if (!empty($row->premises_id) && $row->premises_id != 0){
- $premises = SysDept::where('dept_id',$row->premises_id)
- ->select('dept_id','dept_name')
- ->first();
- $row->salesman_premises = !empty($premises) ? $premises->dept_name : '';
- }
- if(!empty($row->goods_sku_specs_json)){
- $specsJson = json_decode($row->goods_sku_specs_json,true);
- $skuTitle = '';
- foreach($specsJson as $key => $item){
- if(is_array($item)){
- $item = implode('',$item);
- }
- $skuTitle .= $key.':'.$item.',';
- }
- $row->sku_title = rtrim($skuTitle,',');
- }
- $categoryClassify = 'SERVICE';
- $row->category = '';
- if(!empty($row->join_goods_category_id)){
- $category = SysCategory::where('category_id',$row->join_goods_category_id)->select('category_name','category_classify')->first();
- $row->category = $category->category_name ?? '';
- $categoryClassify = $category->category_classify ?? 'SERVICE';
- }
- $row->write_off_premises = trim($row->write_off_premises,"\"");
- $row->data_used_addtimes = date('Y-m-d H:i:s',$row->data_used_addtimes);
- }
- return json_success('', [
- 'page' => $page,
- 'pageSize' => $pageSize,
- 'total' => $total[0]->total,
- 'rows' => $rows
- ]);
- }
- public function statistics(Request $request)
- {
- $days = $request->get('days',[]);
- $salePremisesId = $request->get('sale_premises_id','');
- $writeOffPremisesId = $request->get('write_off_premises_id','');
- $categoryId = $request->get('category_id','');
- $goodsName = $request->get('goods_name','');
- if (!empty($days)) {
- $days[0] = strtotime($days[0]);
- $days[1] = strtotime($days[1]);
- if (date('m', $days[0]) != date('m', $days[1])) {
- return json_fail('暂不支持跨月查询');
- }
- $month = date('Ym', $days[0]);
- $days[1] = strtotime(date('Y-m-d', $days[1]) . " 23:59:59");
- } else {
- $month = date('Ym');
- }
- if(!Util::schema()->hasTable("data_used_{$month}")){
- return json_fail('暂无数据');
- }
- $categorySearchIds = '';
- if (!empty($categoryId)){
- $categorySearchPath = SysCategory::where('category_id',$categoryId)->value('category_super_path');
- $categorySearchPath .= '#'. $categoryId.'#';
-
- $categorySearchIds = SysCategory::where('category_super_path','like',$categorySearchPath.'%')->pluck('category_id')->toArray();
- $categorySearchIds = array_merge($categorySearchIds,[$categoryId]);
- $categorySearchIds = implode(',',$categorySearchIds);
- }
-
- $where = " where 1 ";
- if (!empty($days)) {
- $where .= "and du.data_used_addtimes between {$days[0]} and {$days[1]} ";
- }
- if(!empty($salePremisesId)){
- $where .= " and JSON_EXTRACT(du.join_data_used_object_json,'$.salesman_premises_id') = {$salePremisesId} ";
- }
- if (!empty($writeOffPremisesId)){
- $where .= " and du.join_data_used_dept_id = {$writeOffPremisesId}";
- }
- if (!empty($categorySearchIds)){
- $where .= " and g.join_goods_category_id in({$categorySearchIds})";
- }
- if (!empty($goodsName)){
- $where .= " and g.goods_name like '%{$goodsName}%' ";
- }
- $sql = "select
- SUM(data_used_count) as total,data_used_classify
- from app_data_used_{$month} as du
- left join app_goods as g on JSON_EXTRACT(du.join_data_used_object_json,'$.goods_id') = g.goods_id
- {$where}
- group by data_used_classify";
- $rows = Db::select($sql);
- $rows = json_decode(json_encode($rows),true);
-
- //核销归类 ORDER-PACKAGE=套包订单|ORDER-DISHES=点餐订单|ROLE-SERVICE=角色权益服务卡|ROLE-GOODS=角色权益产品卡|APPOINTMENT=后付费预约|ORDER=预付费订单
- $statistics = ['service'=>0,'meals'=>0,'quota'=>0,'package'=>0,'goods'=>0,'appointment'=>0];
- foreach ($rows as &$row){
- if($row['data_used_classify'] == 'ORDER-DISHES'){
- $statistics['meals'] = $row['total'];
- }elseif($row['data_used_classify'] == 'ORDER-PACKAGE'){
- $statistics['package'] = $row['total'];
- }elseif($row['data_used_classify'] == 'ROLE-SERVICE'){
- $statistics['quota'] = $row['total'];
- }elseif($row['data_used_classify'] == 'ROLE-GOODS'){
- $statistics['goods'] = $row['total'];
- }elseif($row['data_used_classify'] == 'APPOINTMENT'){
- $statistics['appointment'] = $row['total'];
- }elseif($row['data_used_classify'] == 'ORDER'){
- $statistics['service'] = $row['total'];
- }
- }
- return json_success('',$statistics);
- }
- public function records()
- {
- $sql = "select a.appointment_id as benefit_id,a.appointment_addtimes as addtimes,m.member_mobile,c.member_cert_name,b.member_benefit_name as benefit_name,a.appointment_done_json as used_json,a.appointment_done_datetime from app_appointment as a
- left join app_member as m on m.member_id = a.join_appointment_member_id
- left join app_member_cert as c on c.join_cert_member_id = a.join_appointment_member_id
- left join app_member_benefit as b on b.join_benefit_member_id = a.join_appointment_member_id
- UNION
- select q.member_quota_id as benefit_id,q.member_quota_addtimes as addtimes,q.member_quota_used_json as used_json, q.member_quota_name as benefit_name,m.member_mobile,c.member_cert_name from app_member_quota as q
- left join app_member as m on m.member_id = q.join_quota_member_id
- left join app_member_cert as c on c.join_cert_member_id = q.join_quota_member_id
- order by addtimes desc limit 0,10";
- $records = Db::select($sql);
- dump($records);
- }
- }
|