/** * 新增会员 */ public function queryOp() { $conn = (require BASE_DATA_PATH . '/../core/framework/db/mssqlpdo.php'); //处理数据 $page = new Page(); $page->setEachNum(10); $page->setNowPage($_REQUEST["curpage"]); $startnum = $page->getEachNum() * ($page->getNowPage() - 1); $endnum = $page->getEachNum() * $page->getNowPage(); $sql = 'from healthfile a , personalinfo b , sam_taxempcode emp, district dist ' . ' where a.fileno = b.fileno and a.inputpersonid = emp.loginname and a.districtnumber = dist.id and emp.org_id in(' . implode(',', $this->orgidarray) . ')'; if ($_GET['query_start_time']) { $sql = $sql . ' and a.inputdate >=\'' . $_GET['query_start_time'] . '\''; } if ($_GET['query_end_time']) { $sql = $sql . ' and a.inputdate < dateadd(day,1,\'' . $_GET['query_end_time'] . '\')'; } if ($_GET['idnumber']) { $sql = $sql . ' and b.idnumber = \'' . $_GET['idnumber'] . '\''; } if ($_GET['name']) { $sql = $sql . ' and a.name like \'' . $_GET['name'] . '%\''; } //处理树的参数 $checkednode = $_GET['checkednode']; if ($checkednode && isset($checkednode) && count($checkednode) > 0) { $sql = $sql . " and emp.org_id in ({$checkednode}) "; } $countsql = " select count(*) {$sql} "; // echo $countsql; $stmt = $conn->query($countsql); // echo $countsql; $total = $stmt->fetch(PDO::FETCH_NUM); $page->setTotalNum($total[0]); $tsql = "SELECT * FROM ( SELECT * FROM (SELECT TOP {$endnum} row_number() over( order by a.inputdate desc) rownum,\n a.fileno,\n a.name,\n a.address,\n a.tel,\n b.sex,\n b.birthday,\n b.idnumber,\n emp.username,\n a.inputdate\n {$sql} order by a.inputdate desc )zzzz where rownum>{$startnum} )zzzzz order by rownum"; // echo $sql; $stmt = $conn->query($tsql); $data_list = array(); while ($row = $stmt->fetch(PDO::FETCH_OBJ)) { array_push($data_list, $row); } $totalsql = " select '总数:' as fileno,\n count(a.fileno) name,\n null as address,\n null as sex,\n null as birthday,\n null as idnumber,\n null as username,\n null as inputdate\n {$sql} "; // echo $sql; $totalstmt = $conn->query($totalsql); while ($row = $totalstmt->fetch(PDO::FETCH_OBJ)) { array_push($data_list, $row); } Tpl::output('data_list', $data_list); //--0:期初入库 1:采购入库 2:购进退回 3:盘盈 5:领用 12:盘亏 14:领用退回 50:采购计划 Tpl::output('page', $page->show()); Tpl::showpage('healthfile.query'); }
/** * 参与活动 */ public function activity_applyOp() { //根据活动编号查询活动信息 $activity_id = intval($_GET['activity_id']); if ($activity_id <= 0) { showMessage(Language::get('miss_argument'), 'index.php?act=store&op=store_activity', 'html', 'error'); } $activity_model = Model('activity'); $activity_info = $activity_model->getOneById($activity_id); //活动类型必须是商品并且活动没有关闭并且活动进行中 if (empty($activity_info) || $activity_info['activity_type'] != '1' || $activity_info['activity_state'] != 1 || $activity_info['activity_start_date'] > time() || $activity_info['activity_end_date'] < time()) { showMessage(Language::get('store_activity_not_exists'), 'index.php?act=store&op=store_activity', 'html', 'error'); } Tpl::output('activity_info', $activity_info); $list = array(); //声明存放活动细节的数组 //查询商品分类列表 $gc = Model('goods_class'); $gc_list = $gc->getTreeClassList(3, array('gc_show' => 1, 'order' => 'gc_parent_id asc,gc_sort asc,gc_id asc')); foreach ($gc_list as $k => $gc) { $gc_list[$k]['gc_name'] = ''; $gc_list[$k]['gc_name'] = str_repeat(" ", $gc['deep'] * 2) . $gc['gc_name']; } Tpl::output('gc_list', $gc_list); //查询品牌列表 $brand = Model('brand'); $brand_list = $brand->getBrandList(array()); Tpl::output('brand_list', $brand_list); //查询活动细节信息 $activity_detail_model = Model('activity_detail'); $list = $activity_detail_model->getGoodsJoinList(array('activity_id' => "{$activity_id}", 'store_id' => "{$_SESSION['store_id']}", 'activity_detail_state_in' => "'0','1','3'", 'group' => 'activity_detail_state asc')); //构造通过与审核中商品的编号数组,以便在下方待选列表中,不显示这些内容 $item_ids = array(); if (is_array($list) and !empty($list)) { foreach ($list as $k => $v) { $item_ids[] = $v['item_id']; } } Tpl::output('list', $list); //根据查询条件查询商品列表 $condition = array(); if ($_GET['gc_id'] != '') { $condition['gc_id'] = intval($_GET['gc_id']); } if ($_GET['brand_id'] != '') { $condition['brand_id'] = intval($_GET['brand_id']); } if (trim($_GET['name']) != '') { $condition['keyword'] = trim($_GET['name']); } $condition['store_id'] = $_SESSION['store_id']; $condition['goods_show'] = '1'; $condition['goods_state'] = '0'; $condition['goods_store_state'] = 'open'; if (!empty($item_ids)) { $condition['no_goods_id'] = implode(',', $item_ids); } $page = new Page(); $page->setEachNum(16); $page->setStyle('admin'); $page->setNowPage(empty($_GET['curpage']) ? 1 : intval($_GET['curpage'])); $goods = Model('goods'); $goods_list = $goods->getGoods($condition, $page, '*', 'brand'); Tpl::output('goods_list', $goods_list); Tpl::output('show_page', $page->show()); Tpl::output('search', $_GET); /** * 页面输出 */ self::profile_menu('activity_apply', 'activity_apply'); Tpl::output('menu_sign', 'store_activity'); Tpl::output('menu_sign_url', 'index.php?act=store&op=store_activity'); Tpl::output('menu_sign1', 'activity_apply'); Tpl::showpage('store_activity.apply'); }
/** * 新增会员 */ public function indexOp() { $conn = (require BASE_DATA_PATH . '/../core/framework/db/mssqlpdo.php'); $page = new Page(); $page->setEachNum(10); $page->setNowPage($_REQUEST["curpage"]); $startnum = $page->getEachNum() * ($page->getNowPage() - 1); $endnum = $page->getEachNum() * $page->getNowPage(); $sql = 'from spotcheck_main a left join spotcheck_spot spot on a.id = spot.spotid, spotcheck_type b , healthfile hf, personalinfo info where a.checktype = b.code and a.fileno = hf.fileno and hf.fileno = info.fileno '; if ($_REQUEST['search_phone']) { $sql = $sql . ' and a.phone like \'%' . $_REQUEST['search_phone'] . '%\''; } if ($_REQUEST['search_name']) { $sql = $sql . ' and hf.name like \'%' . $_REQUEST['search_name'] . '%\''; } if ($_REQUEST['spot_start_time']) { $sql = $sql . ' and a.checkdate >=\'' . $_REQUEST['spot_start_time'] . '\''; } if ($_REQUEST['spot_end_time']) { $sql = $sql . ' and a.checkdate < dateadd(day,1,\'' . $_REQUEST['spot_end_time'] . '\')'; } if ($_REQUEST['input_start_time']) { $sql = $sql . ' and a.inputdate >=\'' . $_REQUEST['input_start_time'] . '\''; } if ($_REQUEST['input_end_time']) { $sql = $sql . ' and a.inputdate < dateadd(day,1,\'' . $_REQUEST['input_end_time'] . '\')'; } if ($_REQUEST['search_spot']) { $search_spot = $_REQUEST['search_spot']; if ($search_spot == 10) { $sql = $sql . ' and exists ( select 1 from spotcheck_spot where spotid = a.id)'; } elseif ($search_spot == 20) { $sql = $sql . ' and not exists ( select 1 from spotcheck_spot where spotid = a.id)'; } } if ($_REQUEST['search_result']) { $search_result = $_REQUEST['search_result']; if ($search_result && count($search_result) > 0) { $sql = $sql . ' and spot.result = \'' . $search_result . '\''; } } if ($_REQUEST['search_spottype']) { $search_result = $_REQUEST['search_spottype']; if ($search_result && count($search_result) > 0) { $sql = $sql . ' and a.checktype = \'' . $search_result . '\''; } } $countsql = " select count(*) {$sql} "; // echo $countsql; $stmt = $conn->query($countsql); // echo $countsql; $total = $stmt->fetch(PDO::FETCH_NUM); $page->setTotalNum($total[0]); $tsql = "SELECT * FROM ( SELECT * FROM (SELECT TOP {$endnum} row_number() over( order by a.inputdate) rownum,\n a.id,\n a.checkdate\n ,a.checkopt\n ,b.value 'checktype'\n ,a.checktable\n ,a.checktableid\n ,a.fileno\n ,a.name\n ,a.sex\n ,a.birthday\n ,a.phone\n ,a.address\n ,a.itype\n ,a.getgift\n ,a.bischronic\n ,a.bisservant\n ,a.serversatisfaction\n ,a.oftenbuydrug\n ,a.acceptreason\n ,a.advices\n ,a.elseneed\n ,a.remark\n ,a.inputdate\n ,a.updatedate\n ,a.inputperson\n ,a.orgid\n {$sql} order by a.inputdate)zzzz where rownum>{$startnum} )zzzzz order by rownum"; $stmt = $conn->query($tsql); $data_list = array(); while ($row = $stmt->fetch(PDO::FETCH_OBJ)) { array_push($data_list, $row); $newstmt = $conn->query(" select * from spotcheck_spot where spotid = '{$row->id}'"); $row->spotinfo = $newstmt->fetch(PDO::FETCH_OBJ); } Tpl::output('data_list', $data_list); Tpl::output('page', $page->show()); Tpl::showpage('healthplatform.index'); }
/** * 库存管理 */ public function stockOp() { $conn = (require BASE_DATA_PATH . '/../core/framework/db/mssqlpdo.php'); $treesql = 'select b.id , b.name,b.districtnumber,b.parentid pId from map_org_wechat a, Organization b where a.orgid = b.id '; $treestmt = $conn->query($treesql); $treedata_list = array(); while ($row = $treestmt->fetch(PDO::FETCH_OBJ)) { array_push($treedata_list, $row); } Tpl::output('treelist', $treedata_list); if (!isset($_GET['orgid'])) { $_GET['orgid'] = $treedata_list[0]->id; } $page = new Page(); $page->setEachNum(10); $page->setNowPage($_REQUEST["curpage"]); $startnum = $page->getEachNum() * ($page->getNowPage() - 1); $endnum = $page->getEachNum() * $page->getNowPage(); $sql = 'from Center_DrugStock stock left join shopnc_goods_common good on good.goods_commonid = stock.iDrug_ID left join Organization org on stock.orgid = org.id where good.idrug_rectype in (0,1,3) '; if ($_GET['search_goods_name'] != '') { $sql = $sql . ' and good.goods_name like \'%' . trim($_GET['search_goods_name']) . '%\''; } if (intval($_GET['search_commonid']) > 0) { $sql = $sql . ' and good.goods_commonid = ' . intval($_GET['search_commonid']); } if ($_GET['search_store_name'] != '') { $sql = $sql . ' and good.store_name like \'%' . trim($_GET['search_store_name']) . '%\''; } if (intval($_GET['search_brand_id']) > 0) { $sql = $sql . ' and good.brand_id = ' . intval($_GET['search_brand_id']) . ''; } if (intval($_GET['cate_id']) > 0) { $sql = $sql . ' and good.gc_id = ' . intval($_GET['cate_id']); } if (in_array($_GET['search_state'], array('0', '1', '10'))) { $sql = $sql . ' and good.goods_state =' . $_GET['search_state']; } if (in_array($_GET['search_verify'], array('0', '1', '10'))) { $sql = $sql . ' and good.goods_verify = ' . $_GET['search_verify']; } if ($_GET['allowzero'] && $_GET['allowzero'] == 'true') { $sql = $sql . ' '; } else { $sql = $sql . ' and (stock.fDS_OStock <> 0 or stock.fDS_LeastOStock <> 0) '; } if ($_GET['orgid'] != '') { $sql = $sql . ' and stock.orgid =\'' . $_GET['orgid'] . '\''; } $countsql = " select count(*) {$sql} "; // echo $countsql; $stmt = $conn->query($countsql); // echo $countsql; $total = $stmt->fetch(PDO::FETCH_NUM); $page->setTotalNum($total[0]); $tsql = "SELECT * FROM ( SELECT * FROM (SELECT TOP {$endnum} row_number() over( order by good.goods_commonid) rownum,\n org.name as OrgName, *\n {$sql} order by good.goods_commonid)zzzz where rownum>{$startnum} )zzzzz order by rownum"; $stmt = $conn->query($tsql); $goods_list = array(); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $goods_list[] = $row; // $newstmt = $conn->query(" select * from Center_DrugStocksub where idrug_id = '$row->goods_commonid'"); // $row->detail = $newstmt->fetch(PDO::FETCH_OBJ); } // var_dump($goods_list); Tpl::output('goods_list', $goods_list); Tpl::output('page', $page->show()); $goods_class = Model('goods_class')->getTreeClassList(1); // 品牌 $condition = array(); $condition['brand_apply'] = '1'; $brand_list = Model('brand')->getBrandList($condition); Tpl::output('search', $_GET); Tpl::output('goods_class', $goods_class); Tpl::output('brand_list', $brand_list); Tpl::output('state', array('1' => '出售中', '0' => '仓库中', '10' => '违规下架')); Tpl::output('verify', array('1' => '通过', '0' => '未通过', '10' => '等待审核')); Tpl::showpage('goods.stock'); }
protected function getPage($ml, $listRow, $curPage, $arr = array(), $is_relation = false) { import('ORG.Util.Page'); $arr['status'] = array('neq', 2); $count = $ml->where($arr)->count(); // 'header'=>'条记录','prev'=>'上一页','next'=>'下一页','first'=>'第一页','last'=>'最后一页','theme'=>' %first% %upPage% %prePage% %linkPage% %downPage% %nextPage% %end% $page = new Page($count, $listRow); $array = array(); $page->setNowPage($curPage); $str = $page->show(); $array['str'] = $str; if ($is_relation) { $array['list'] = $ml->relation(true)->page($curPage, $listRow)->where($arr)->order('id desc')->select(); } else { $array['list'] = $ml->page($curPage, $listRow)->where($arr)->order('id desc')->select(); } $array['curPage'] = $curPage; $array['listRow'] = $listRow; $array['count'] = $count; return $array; }
public function sumOp() { $conn = (require BASE_DATA_PATH . '/../core/framework/db/mssqlpdo.php'); if (!isset($_GET['search_type'])) { $_GET['search_type'] = '0'; } $sqlarray = array('iBuy_Type' => 'storetype.name as "iBuy_Type"', 'customname' => ' custom.sCustomer_Name as "customname" ', 'sDrug_Spec' => ' goods.sDrug_Spec as "sDrug_Spec" ', 'sDrug_Unit' => ' goods.sDrug_Unit as "sDrug_Unit" ', 'sDrug_Brand' => ' goods.sDrug_Brand as "sDrug_Brand" ', 'drugcount' => ' sum(fBuy_FactNum) as "drugcount" ', 'year' => ' year(a.dBuy_Date) as "year" ', 'month' => ' left(convert(varchar,dBuy_Date,112),6) as "month" ', 'day' => ' convert(varchar,dBuy_Date,112) as "day" ', 'sDrug_TradeName' => ' goods.sDrug_TradeName as "sDrug_TradeName" ', 'OrgID' => ' c.name as "OrgID" '); $config = array(0 => array('text' => '采购金额汇总', 'sqlwher' => ' and iBuy_Type in (1,2) ', 'sumcol' => array('iBuy_Type' => array(name => 'iBuy_Type', 'text' => '单据类型', map => $this->types), 'customname' => array(name => 'customname', 'text' => '供货企业'), 'good' => array('text' => '商品', 'cols' => array(0 => array(name => 'sDrug_TradeName', 'text' => '商品名称'), 1 => array(name => 'sDrug_Spec', 'text' => '规格'), 2 => array(name => 'sDrug_Unit', 'text' => '单位'), 3 => array(name => 'sDrug_Brand', 'text' => '产地厂牌'), 4 => array(name => 'drugcount', 'text' => '数量'))), 'year' => array('text' => '年', name => 'year', uncheck => 'month,day'), 'month' => array('text' => '月', name => 'month', uncheck => 'year,day'), 'day' => array('text' => '日', name => 'day', uncheck => 'year,month'))), 1 => array('text' => '领用金额汇总', 'sqlwher' => ' and iBuy_Type in (5,14) ', 'sumcol' => array('type' => array('name' => 'OrgID', 'text' => '领用部门'), 'good' => array('text' => '商品', 'cols' => array(0 => array(name => 'sDrug_TradeName', 'text' => '商品名称'), 1 => array(name => 'sDrug_Spec', 'text' => '规格'), 2 => array(name => 'sDrug_Unit', 'text' => '单位'), 3 => array(name => 'sDrug_Brand', 'text' => '产地厂牌'), 4 => array(name => 'drugcount', 'text' => '数量'))), 'year' => array('text' => '年', name => 'year', uncheck => 'month,day'), 'month' => array('text' => '月', name => 'month', uncheck => 'year,day'), 'day' => array('text' => '日', name => 'day', uncheck => 'year,month'))), 2 => array('text' => '盈亏金额汇总', 'sqlwher' => ' and iBuy_Type in (3,4,12) ', 'sumcol' => array('iBuy_Type' => array(name => 'iBuy_Type', 'text' => '单据类型'), 'good' => array('text' => '商品', 'cols' => array(0 => array(name => 'sDrug_TradeName', 'text' => '商品名称'), 1 => array(name => 'sDrug_Spec', 'text' => '规格'), 2 => array(name => 'sDrug_Unit', 'text' => '单位'), 3 => array(name => 'sDrug_Brand', 'text' => '产地厂牌'), 4 => array(name => 'drugcount', 'text' => '数量'))), 'year' => array('text' => '年', name => 'year', uncheck => 'month,day'), 'month' => array('text' => '月', name => 'month', uncheck => 'year,day'), 'day' => array('text' => '日', name => 'day', uncheck => 'year,month'))), 3 => array('text' => '采购计划数量汇总', 'sqlwher' => ' and iBuy_Type in (50) ', 'sumcol' => array('OrgID' => array('name' => 'OrgID', 'text' => '采购计划机构'), 'good' => array('text' => '商品', 'cols' => array(0 => array(name => 'sDrug_TradeName', 'text' => '商品名称'), 1 => array(name => 'sDrug_Spec', 'text' => '规格'), 2 => array(name => 'sDrug_Unit', 'text' => '单位'), 3 => array(name => 'sDrug_Brand', 'text' => '产地厂牌'), 4 => array(name => 'drugcount', 'text' => '数量'))), 'year' => array('text' => '年', name => 'year', uncheck => 'month,day'), 'month' => array('text' => '月', name => 'month', uncheck => 'year,day'), 'day' => array('text' => '日', name => 'day', uncheck => 'year,month')))); Tpl::output('config', $config); //处理汇总字段 $sumtype = $_GET['sumtype']; if ($sumtype == null) { $sumtype = array(0 => "good"); $_GET['sumtype'] = $sumtype; } $checked = $_GET['checked']; $page = new Page(); $page->setEachNum(10); $page->setNowPage($_REQUEST["curpage"]); $sql = 'from Center_Buy a , Center_Drug b , Organization c, Organization d , shopnc_goods_common goods,Center_Customer custom , Center_codes storetype where a.iDrug_ID = b.iDrug_ID ' . ' and a.SaleOrgID = -( c.id +1000) and a.orgid = d.id and a.iDrug_ID = goods.goods_commonid and a.iCustomer_ID = custom.iCustomer_ID and a.iBuy_Type = storetype.code and storetype.type=\'iBuy_Type\''; if ($_GET['query_start_time']) { $sql = $sql . ' and a.dBuy_Date >=\'' . $_GET['query_start_time'] . '\''; } if ($_GET['query_end_time']) { $sql = $sql . ' and a.dBuy_Date < dateadd(day,1,\'' . $_GET['query_end_time'] . '\')'; } if ($_GET['search_goods_name'] != '') { $sql = $sql . ' and goods.goods_name like \'%' . trim($_GET['search_goods_name']) . '%\''; } if (intval($_GET['search_commonid']) > 0) { $sql = $sql . ' and goods.goods_commonid = ' . intval($_GET['search_commonid']); } //处理树的参数 if ($_GET['orgids']) { if ($_GET['search_type'] == 0 || $_GET['search_type'] == 1) { $orgarray = array(); foreach ($_GET['orgids'] as $v) { $orgarray[] = -($v + 1000); } $sql = $sql . ' and a.SaleOrgID in (' . implode(',', $orgarray) . ' )'; } else { $sql = $sql . ' and a.OrgID in ( ' . implode(',', $_GET['orgids']) . ')'; } } $search_type = $_GET['search_type']; // echo $search_type; $colconfig = $config[intval($search_type)]; // var_dump($config[intval($search_type)]); $displaycol = array(); $displaytext = array(); $sumcol = array(); $totalcol = array(); $groupbycol = array(); foreach ($sumtype as $i => $v) { // var_dump($colconfig['sumcol'][$v]); if (isset($colconfig['sqlwher'])) { $sql = $sql . $colconfig['sqlwher']; } if (isset($colconfig['sumcol'][$v])) { if (isset($colconfig['sumcol'][$v]['cols'])) { foreach ($colconfig['sumcol'][$v]['cols'] as $item) { // echo $item['name'] . '<br>'; array_push($sumcol, $sqlarray[$item['name']]); array_push($displaycol, $item['name']); array_push($displaytext, $item['text']); $itemsplit = explode(' as ', $sqlarray[$item['name']]); array_push($totalcol, ' null as ' . $itemsplit[1]); $str = strtolower(str_replace(' ', '', trim($itemsplit[0]))); if (substr($str, 0, 4) != 'sum(' && substr($str, 0, 6) != 'count(') { array_push($groupbycol, $itemsplit[0]); } } } else { $item = $colconfig['sumcol'][$v]; array_push($sumcol, $sqlarray[$item['name']]); array_push($displaycol, $item['name']); array_push($displaytext, $item['text']); $itemsplit = explode(' as ', $sqlarray[$item['name']]); array_push($totalcol, ' null as ' . $itemsplit[1]); $str = strtolower(str_replace(' ', '', trim($itemsplit[0]))); if (substr($str, 0, 4) != 'sum(' && substr($str, 0, 6) != 'count(') { array_push($groupbycol, $itemsplit[0]); } } } } array_push($displaytext, '进价金额'); array_push($displaytext, '零价金额'); array_push($displaytext, '进销差'); // var_dump($totalcol); $totalcol[0] = '\'总计:\' as ' . explode(' as ', $totalcol[0])[1]; // var_dump($totalcol); $totalcolstr = join(',', $totalcol); $sumcolstr = join(',', $sumcol); $groupbycolstr = join(',', $groupbycol); // echo $sumcolstr; $tsql = " select {$sumcolstr} , sum(fBuy_TaxMoney) taxmoney, sum(fBuy_RetailMoney) retailmoney , sum(fBuy_RetailMoney) - sum(fBuy_TaxMoney) diffmoney\n {$sql} group by {$groupbycolstr} order by {$groupbycolstr} "; // echo $tsql; //处理合计 $totalsql = " select {$totalcolstr} , sum(fBuy_TaxMoney) taxmoney, sum(fBuy_RetailMoney) retailmoney , sum(fBuy_RetailMoney) - sum(fBuy_TaxMoney) diffmoney\n {$sql} "; if (isset($_GET['export']) && $_GET['export'] == 'true') { $this->exportxlsx(array(0 => $tsql, 1 => $totalsql), $displaytext, '仓库单据汇总'); } $stmt = $conn->query($tsql); $data_list = array(); while ($row = $stmt->fetch(PDO::FETCH_OBJ)) { array_push($data_list, $row); } // echo $totalsql; $totalstmt = $conn->query($totalsql); while ($row = $totalstmt->fetch(PDO::FETCH_OBJ)) { array_push($data_list, $row); } Tpl::output('data_list', $data_list); //--0:期初入库 1:采购入库 2:购进退回 3:盘盈 5:领用 12:盘亏 14:领用退回 50:采购计划 Tpl::output('page', $page->show()); //处理需要显示的列 $col = array(); foreach ($sumtype as $i => $v) { if (isset($sumtypestr[$v])) { foreach ($sumtypestr[$v] as $key => $item) { $col[$key] = $item; } } } // var_dump($col); Tpl::output('displaycol', $displaycol); Tpl::output('displaytext', $displaytext); Tpl::showpage('storehouse.sum'); }
public function changepriceOp() { //引入查询 $conn = (require BASE_DATA_PATH . '/../core/framework/db/mssqlpdo.php'); //存入登录人员名称 $admininfo = $this->getAdminInfo(); Tpl::output('adminname', $admininfo['name']); //查询机构列表 $treesql = 'select b.id , b.name,b.districtnumber,b.parentid pId from map_org_wechat a, Organization b where a.orgid = b.id '; $treestmt = $conn->query($treesql); $treedata_list = array(); $org_map = array(); $treedata_list[] = (object) array('id' => 0, 'name' => '全部机构', 'districtnumber' => '', 'parentid' => ''); while ($row = $treestmt->fetch(PDO::FETCH_OBJ)) { $treedata_list[] = $row; $org_map[$row->id] = $row->name; } Tpl::output('treelist', $treedata_list); //加入全部机构 $org_map[0] = '全部机构'; Tpl::output('org_map', $org_map); //初始化机构 if (!isset($_GET['orgid'])) { $_GET['orgid'] = $treedata_list[0]->id; } //生成排序字段 $orderbys = array(array('txt' => '调价日期', 'col' => ' dPrice_Date '), array('txt' => '执行开始日期', 'col' => ' dPrice_BeginDate '), array('txt' => '执行结束日期', 'col' => ' dPrice_EndDate '), array('txt' => '项目主键', 'col' => ' iDrug_ID '), array('txt' => '项目名称', 'col' => ' ItemName '), array('txt' => '审核日期', 'col' => ' dPrice_CheckDate '), array('txt' => '调价状态', 'col' => ' iPrice_State '), array('txt' => '项目类型', 'col' => ' ItemType '), array('txt' => '调前价', 'col' => ' fPrice_Before '), array('txt' => '调后价', 'col' => ' fPrice_After ')); Tpl::output('orderbys', $orderbys); //处理分页 $page = new Page(); $page->setEachNum(10); $page->setNowPage($_REQUEST["curpage"]); $startnum = $page->getEachNum() * ($page->getNowPage() - 1); $endnum = $page->getEachNum() * $page->getNowPage(); //生成SQL $sql = 'from Center_OrgPrice a where 1=1 '; //处理查询字段 //机构选择 if ($this->notEmpty($_GET['orgids'])) { $sql = $sql . ' and a.OrgID in ( ' . implode(',', $_GET['orgids']) . ')'; } //调价日期 起 if ($this->notEmpty($_GET['dPrice_Date_begin'])) { $sql = $sql . ' and a.dPrice_Date >= \'' . $_GET['dPrice_Date_begin'] . '\' '; } //调价日期 止 if ($this->notEmpty($_GET['dPrice_Date_end'])) { $sql = $sql . ' and a.dPrice_Date < dateadd(day,1,\'' . $_GET['dPrice_Date_end'] . '\') '; } //执行开始日期 起 if ($this->notEmpty($_GET['dPrice_BeginDate_begin'])) { $sql = $sql . ' and a.dPrice_BeginDate >= \'' . $_GET['dPrice_BeginDate_begin'] . '\' '; } //执行开始日期 止 if ($this->notEmpty($_GET['dPrice_BeginDate_end'])) { $sql = $sql . ' and a.dPrice_BeginDate < dateadd(day,1,\'' . $_GET['dPrice_BeginDate_end'] . '\') '; } //执行结束日期 起 if ($this->notEmpty($_GET['dPrice_EndDate_begin'])) { $sql = $sql . ' and a.dPrice_EndDate >= \'' . $_GET['dPrice_EndDate_begin'] . '\' '; } //执行结束日期 止 if ($this->notEmpty($_GET['dPrice_EndDate_end'])) { $sql = $sql . ' and a.dPrice_EndDate < dateadd(day,1,\'' . $_GET['dPrice_EndDate_end'] . '\') '; } //调价人 if ($this->notEmpty($_GET['sPrice_Person'])) { $sql = $sql . ' and a.sPrice_Person = \'' . $_GET['sPrice_Person'] . '\' '; } //状态 if ($this->notEmpty($_GET['iPrice_State'])) { $sql = $sql . ' and a.iPrice_State = ' . intval($_GET['iPrice_State']) . ' '; } //状态码值表 $map_iPrice_State = array('' => '全部', '0' => '新增', '1' => '提交', '2' => '审核'); Tpl::output('map_iPrice_State', $map_iPrice_State); //审核日期 起 if ($this->notEmpty($_GET['dPrice_CheckDate_begin'])) { $sql = $sql . ' and a.dPrice_CheckDate >= \'' . $_GET['dPrice_CheckDate_begin'] . '\' '; } //审核日期 止 if ($this->notEmpty($_GET['dPrice_CheckDate_end'])) { $sql = $sql . ' and a.dPrice_CheckDate < dateadd(day,1,\'' . $_GET['dPrice_CheckDate_end'] . '\') '; } //审核人 if ($this->notEmpty($_GET['sPrice_CheckPerson'])) { $sql = $sql . ' and a.sPrice_CheckPerson = \'' . $_GET['sPrice_CheckPerson'] . '\' '; } //项目主键 if ($this->notEmpty($_GET['iDrug_ID'])) { $sql = $sql . ' and a.iDrug_ID = ' . $_GET['iDrug_ID'] . ' '; } //项目名称 if ($this->notEmpty($_GET['ItemName'])) { $sql = $sql . ' and a.ItemName like \'%' . $_GET['ItemName'] . '%\' '; } //单位 if ($this->notEmpty($_GET['Unit'])) { $sql = $sql . ' and a.Unit = \'' . $_GET['Unit'] . '\' '; } //项目类型 if ($this->notEmpty($_GET['ItemType'])) { $sql = $sql . ' and a.ItemType = \'' . $_GET['ItemType'] . '\' '; } //单价类型 if ($this->notEmpty($_GET['iPrice_Type'])) { $sql = $sql . ' and a.iPrice_Type = ' . intval($_GET['iPrice_Type']) . ' '; } //单价类型码值表 $map_iPrice_Type = array('' => '全部', '0' => '零售价', '1' => '特价', '2' => '二件价'); Tpl::output('map_iPrice_Type', $map_iPrice_Type); //调前价 起 if ($this->notEmpty($_GET['fPrice_Before_begin'])) { $sql = $sql . ' and a.fPrice_Before >= ' . floatval($_GET['fPrice_Before_begin']) . ' '; } //调前价 止 if ($this->notEmpty($_GET['fPrice_Before_end'])) { $sql = $sql . ' and a.fPrice_Before <= ' . floatval($_GET['fPrice_Before_end']) . ' '; } //调后价 起 if ($this->notEmpty($_GET['fPrice_After_begin'])) { $sql = $sql . ' and a.fPrice_After = ' . intval($_GET['fPrice_After_begin']) . ' '; } //调后价 止 if ($this->notEmpty($_GET['fPrice_After_end'])) { $sql = $sql . ' and a.fPrice_After <= ' . intval($_GET['fPrice_After_end']) . ' '; } //特殊说明 if ($this->notEmpty($_GET['sPrice_Remark'])) { $sql = $sql . ' and a.sPrice_Remark = ' . intval($_GET['sPrice_Remark']) . ' '; } //是否下载说明 if ($this->notEmpty($_GET['Downloaded'])) { $sql = $sql . ' and a.Downloaded = ' . intval($_GET['Downloaded']) . ' '; } //是否下载 码值表 $map_Downloaded = array('0' => '未下载', '1' => '已下载'); Tpl::output('map_Downloaded', $map_Downloaded); //设置orderby 顺序 if (!$this->notEmpty($_GET['order'])) { $ordersql = 'desc'; } else { $ordersql = $_GET['order']; } //处理orderby字段 if ($this->notEmpty($_GET['orderby'])) { foreach ($orderbys as $orderby) { if ($orderby['txt'] == $_GET['orderby']) { $order = $orderby['col'] . ' ' . $ordersql; break; } } } if (empty($order)) { $order = ' ' . $orderbys[0]['col'] . ' desc'; } //查看是否导出 if (isset($_GET['export']) && $_GET['export'] == 'true') { $exportsql = "SELECT row_number() over( order by {$order}) 'rownumber',\n \t[iDrug_ID] ,\n [ItemName] ,\n convert(date,dPrice_Date) 'dPrice_Date' ,\n convert(date,dPrice_BeginDate) 'dPrice_BeginDate',\n convert(date,dPrice_EndDate) 'dPrice_EndDate',\n sPrice_Person ,\n [iPrice_State] ,\n convert(date,dPrice_CheckDate) 'date1' ,\n sPrice_CheckPerson ,\n [OrgID] ,\n [Unit] ,\n [ItemType] ,\n [iPrice_Type] ,\n [fPrice_Before] ,\n [fPrice_After]\n {$sql} order by {$order} "; $titlearray = array('序号', '商品编码', '商品名称', '调价日期', '执行开始日期', '执行结束日期', '调价人', '调价状态', '审核日期', '审核人', '机构', '商品单位', '项目类型', '单价类型', '调前价', '调后价'); $maparray = array('iPrice_Type' => $map_iPrice_Type, 'OrgID' => $org_map, 'iPrice_State' => $map_iPrice_State); $this->exportxlsx($exportsql, $titlearray, '商品调价审核', $maparray); } //合计字段 $countsql = " select count(*) {$sql} "; Log::record($countsql, 'SQL'); $stmt = $conn->query($countsql); $total = $stmt->fetch(PDO::FETCH_NUM); $page->setTotalNum($total[0]); //查询数据 $tsql = "SELECT * FROM ( SELECT * FROM (SELECT TOP {$endnum} row_number() over( order by {$order}) rownum, *\n {$sql} order by {$order})zzzz where rownum>{$startnum} )zzzzz order by {$order}"; Log::record($tsql, 'SQL'); $stmt = $conn->query($tsql); $ret_list = array(); //处理结果 while ($row = $stmt->fetch(PDO::FETCH_OBJ)) { $ret_list[] = $row; } //展示处理结果 Tpl::output('ret_list', $ret_list); Tpl::output('page', $page->show()); //显示模板 Tpl::showpage('goods.changeprice'); }
public function financegoodsumOp() { $conn = (require BASE_DATA_PATH . '/../core/framework/db/mssqlpdo.php'); if (!isset($_GET['search_type'])) { $_GET['search_type'] = '0'; } $sqlarray = array('classname' => ' case when class.sClass_ID is not null then class.sClass_ID+\'.\'+class.sClass_Name else \'\' end as "classname" ', 'Section' => 'a.StatSection as "Section"', 'execSection' => ' ', 'Doctor' => ' a.DoctorName as "Doctor" ', 'year' => ' year(a.dSale_GatherDate) as "year" ', 'month' => ' left(convert(varchar,dSale_GatherDate,112),6) as "month" ', 'day' => ' convert(varchar,dSale_GatherDate,112) as "day" ', 'OrgID' => ' org.name as "OrgID" ', 'dSale_MakeDate' => ' replace( CONVERT( CHAR(10), a.dSale_MakeDate, 102), \'.\', \'-\') as "dSale_MakeDate" ', 'dSale_GatherDate' => ' replace( CONVERT( CHAR(10), a.dSale_GatherDate , 102), \'.\', \'-\') as "dSale_GatherDate" ', 'sDrug_Spec' => ' goods.sDrug_Spec as "sDrug_Spec" ', 'sDrug_Unit' => ' goods.sDrug_Unit as "sDrug_Unit" ', 'sDrug_Brand' => ' goods.sDrug_Brand as "sDrug_Brand" ', 'sDrug_TradeName' => ' goods.sDrug_TradeName as "sDrug_TradeName" '); $config = array('sumcol' => array('OrgID' => array(name => 'OrgID', 'text' => '机构'), 'year' => array('text' => '年', name => 'year', uncheck => 'month,day'), 'month' => array('text' => '月', name => 'month', uncheck => 'year,day'), 'day' => array('text' => '日', name => 'day', uncheck => 'year,month'))); Tpl::output('config', $config); //处理汇总字段 $sumtype = $_GET['sumtype']; if ($sumtype == null) { $sumtype = array(); $_GET['sumtype'] = $sumtype; } $checked = $_GET['checked']; $page = new Page(); $page->setEachNum(10); $page->setNowPage($_REQUEST["curpage"]); $sql = 'from Center_ClinicSale a left join shopnc_goods_common goods on a.iDrug_ID = goods.goods_commonid left join Center_Class class on goods.iDrug_StatClass = class.iClass_ID , Organization org where a.orgid = org.id and goods.idrug_rectype in (0,1,3) '; if ($_GET['query_start_time']) { $sql = $sql . ' and a.dSale_MakeDate >=\'' . $_GET['query_start_time'] . '\''; } if ($_GET['query_end_time']) { $sql = $sql . ' and a.dSale_MakeDate < dateadd(day,1,\'' . $_GET['query_end_time'] . '\')'; } if ($_GET['gather_start_time']) { $sql = $sql . ' and a.dSale_GatherDate >=\'' . $_GET['gather_start_time'] . '\''; } if ($_GET['gather_end_time']) { $sql = $sql . ' and a.dSale_GatherDate < dateadd(day,1,\'' . $_GET['gather_end_time'] . '\')'; } //处理树的参数 if ($_GET['orgids']) { $sql = $sql . ' and a.OrgID in ( ' . implode(',', $_GET['orgids']) . ')'; } if ($_GET['search_goods_name'] != '') { $sql = $sql . ' and goods.goods_name like \'%' . trim($_GET['search_goods_name']) . '%\''; } if (intval($_GET['search_commonid']) > 0) { $sql = $sql . ' and goods.goods_commonid = ' . intval($_GET['search_commonid']); } $search_type = $_GET['search_type']; // echo $search_type; $colconfig = $config; // var_dump($config[intval($search_type)]); $displaycol = array(); $displaytext = array(); $sumcol = array(); $totalcol = array(); $groupbycol = array(); foreach ($sumtype as $i => $v) { // var_dump($colconfig['sumcol'][$v]); if (isset($colconfig['sqlwher'])) { $sql = $sql . $colconfig['sqlwher']; } if (isset($colconfig['sumcol'][$v])) { if (isset($colconfig['sumcol'][$v]['cols'])) { foreach ($colconfig['sumcol'][$v]['cols'] as $item) { // echo $item['name'] . '<br>'; array_push($sumcol, $sqlarray[$item['name']]); array_push($displaycol, $item['name']); array_push($displaytext, $item['text']); $itemsplit = explode(' as ', $sqlarray[$item['name']]); array_push($totalcol, ' null as ' . $itemsplit[1]); $str = strtolower(str_replace(' ', '', trim($itemsplit[0]))); if (substr($str, 0, 4) != 'sum(' && substr($str, 0, 6) != 'count(') { array_push($groupbycol, $itemsplit[0]); } } } else { $item = $colconfig['sumcol'][$v]; array_push($sumcol, $sqlarray[$item['name']]); array_push($displaycol, $item['name']); array_push($displaytext, $item['text']); $itemsplit = explode(' as ', $sqlarray[$item['name']]); array_push($totalcol, ' null as ' . $itemsplit[1]); $str = strtolower(str_replace(' ', '', trim($itemsplit[0]))); if (substr($str, 0, 4) != 'sum(' && substr($str, 0, 6) != 'count(') { array_push($groupbycol, $itemsplit[0]); } } } } array_push($displaytext, '商品名称'); array_push($displaytext, '规格'); array_push($displaytext, '单位'); array_push($displaytext, '产地厂牌'); array_push($displaytext, '数量'); array_push($displaytext, '平均单价'); array_push($displaytext, '金额'); array_push($displaytext, '成本'); array_push($displaytext, '毛利'); array_push($displaytext, '毛利率'); // var_dump($totalcol); if (count($totalcol) > 0) { $totalcol[0] = '\'总计:\' as ' . explode(' as ', $totalcol[0])[1]; } // var_dump($totalcol); if (count($totalcol) > 0) { $totalcolstr = join(',', $totalcol) . ','; } if (count($sumcol) > 0) { $sumcolstr = join(',', $sumcol) . ','; } if (count($groupbycol) > 0) { $groupbycolstr = join(',', $groupbycol) . ','; } // echo $sumcolstr; $tsql = " select\n {$sumcolstr}\n goods.sDrug_TradeName as sDrug_TradeName,\n goods.sDrug_Spec as sDrug_Spec,\n goods.sDrug_Unit as sDrug_Unit,\n goods.sDrug_Brand as sDrug_Brand,\n sum(fSale_Num) drugcount ,\n case when sum(fSale_Num) =0 then 0 else sum(fSale_TaxFactMoney)/ sum(fSale_Num) end as fSale_TaxPrice,\n sum(fSale_TaxFactMoney) taxmoney ,\n sum(fSale_NoTaxMoney) notaxmoney ,\n sum(fSale_TaxFactMoney) -sum(fSale_NoTaxMoney) grossprofit,\n case when sum(fSale_TaxFactMoney) =0 then 0 else (sum(fSale_TaxFactMoney) -sum(fSale_NoTaxMoney))/sum(fSale_TaxFactMoney) end grossprofitrate\n {$sql} group by {$groupbycolstr} goods.sDrug_TradeName ,\n goods.sDrug_Spec ,\n goods.sDrug_Unit ,\n goods.sDrug_Brand having sum(fSale_Num) >0 order by {$groupbycolstr} goods.sDrug_TradeName ,\n goods.sDrug_Spec ,\n goods.sDrug_Unit ,\n goods.sDrug_Brand "; // echo $tsql; if (count($totalcol) > 0) { $totalsql = " select {$totalcolstr}\n '' as sDrug_TradeName,\n '' as sDrug_Spec,\n '' as sDrug_Unit,\n '' as sDrug_Brand,\n sum(fSale_Num) drugcount ,\n 0 as fSale_TaxPrice,\n sum(fSale_TaxFactMoney) taxmoney ,\n sum(fSale_NoTaxMoney) notaxmoney ,\n sum(fSale_TaxFactMoney) -sum(fSale_NoTaxMoney) grossprofit,\n case when sum(fSale_TaxFactMoney) =0 then 0 else (sum(fSale_TaxFactMoney) -sum(fSale_NoTaxMoney))/sum(fSale_TaxFactMoney) end grossprofitrate\n {$sql} "; } else { $totalsql = " select '总计:' as sDrug_TradeName,\n '' as sDrug_Spec,\n '' as sDrug_Unit,\n '' as sDrug_Brand,\n sum(fSale_Num) drugcount ,\n 0 as fSale_TaxPrice,\n sum(fSale_TaxFactMoney) taxmoney ,\n sum(fSale_NoTaxMoney) notaxmoney ,\n sum(fSale_TaxFactMoney) -sum(fSale_NoTaxMoney) grossprofit,\n case when sum(fSale_TaxFactMoney) =0 then 0 else (sum(fSale_TaxFactMoney) -sum(fSale_NoTaxMoney))/sum(fSale_TaxFactMoney) end grossprofitrate\n {$sql} "; } if (isset($_GET['export']) && $_GET['export'] == 'true') { $this->exportxlsx(array(0 => $tsql, 1 => $totalsql), $displaytext, '收入统计'); } // echo $totalsql; $stmt = $conn->query($tsql); $data_list = array(); while ($row = $stmt->fetch(PDO::FETCH_OBJ)) { array_push($data_list, $row); } //处理合计 // echo $totalsql; $totalstmt = $conn->query($totalsql); while ($row = $totalstmt->fetch(PDO::FETCH_OBJ)) { array_push($data_list, $row); } Tpl::output('data_list', $data_list); //--0:期初入库 1:采购入库 2:购进退回 3:盘盈 5:领用 12:盘亏 14:领用退回 50:采购计划 Tpl::output('page', $page->show()); //处理需要显示的列 $col = array(); foreach ($sumtype as $i => $v) { if (isset($sumtypestr[$v])) { foreach ($sumtypestr[$v] as $key => $item) { $col[$key] = $item; } } } // var_dump($col); Tpl::output('displaycol', $displaycol); Tpl::output('displaytext', $displaytext); Tpl::showpage('finance.good.sum'); }
public function rechargesumOp() { $conn = (require BASE_DATA_PATH . '/../core/framework/db/mssqlpdo.php'); if (!isset($_GET['search_type'])) { $_GET['search_type'] = '0'; } $sqlarray = array('ChargePerson' => 'person.sPerson_Name as "ChargePerson"', 'type' => ' type.name as "type" ', 'state' => ' state.name as "state" ', 'year' => ' year(a.RechargeDate) as "year" ', 'month' => ' left(convert(varchar,RechargeDate,112),6) as "month" ', 'day' => ' convert(varchar,RechargeDate,112) as "day" ', 'OrgID' => ' org.name as "OrgID" '); // $config = array(0 => array('sumcol' => array('iBuy_Type' => array(name => 'iBuy_Type', 'text' => '单据类型', map => $this->types), // 'customname' => array(name => 'customname', 'text' => '供货企业'), // 'good' => array('text' => '商品', // 'cols' => array(0 => array(name => 'sDrug_TradeName', 'text' => '商品名称') // , 1 => array(name => 'sDrug_Spec', 'text' => '规格') // , 2 => array(name => 'sDrug_Unit', 'text' => '单位') // , 3 => array(name => 'sDrug_Brand', 'text' => '产地厂牌') // , 4 => array(name => 'drugcount', 'text' => '数量'))), // 'year' => array('text' => '年', name=>'year',uncheck=>'month,day' ), // 'month' => array('text' => '月', name=>'month',uncheck=>'year,day'), // 'day' => array('text' => '日', name=>'day',uncheck=>'year,month') // ))); $config = array('sumcol' => array('OrgID' => array(name => 'OrgID', 'text' => '机构', value => 0), 'member' => array('name' => 'member', 'text' => '会员', value => 1, 'cols' => array(0 => array(name => 'sMemberID', 'text' => '会员卡号'), 1 => array(name => 'member_truename', 'text' => '姓名'), 2 => array(name => 'member_sex', 'text' => '性别'), 3 => array(name => 'Mobile', 'text' => '联系电话'), 4 => array(name => 'member_Birthday', 'text' => '生日'))), 'Referrer' => array(name => 'Referrer', value => 2, 'text' => '推荐人'), 'iYear' => array('text' => '年', name => 'iYear', value => 3, uncheck => 'iMonth,dPayDate'), 'iMonth' => array('text' => '月', name => 'iMonth', value => 4, uncheck => 'iYear,dPayDate'), 'dPayDate' => array('text' => '日', name => 'dPayDate', value => 5, uncheck => 'iYear,iMonth'))); // $config = array('sumcol' => array('OrgID' => array(name => 'OrgID', 'text' => '机构'), // 'ChargePerson' => array(name => 'ChargePerson', 'text' => '收款人'), // 'type' => array(name => 'type', 'text' => '类型'), // 'state' => array(name => 'state', 'text' => '状态'), // 'year' => array('text' => '年', name=>'year',uncheck=>'month,day' ), // 'month' => array('text' => '月', name=>'month',uncheck=>'year,day'), // 'day' => array('text' => '日', name=>'day',uncheck=>'year,month'), // )); Tpl::output('config', $config); // 处理汇总字段 $sumtype = $_GET['sumtype']; if ($sumtype == null) { $sumtype = array(0 => "OrgID"); $_GET['sumtype'] = $sumtype; } $starttime; $endtime; $orgids; $checked = $_GET['checked']; $page = new Page(); $page->setEachNum(10); $page->setNowPage($_REQUEST["curpage"]); $startnum = $page->getEachNum() * ($page->getNowPage() - 1); $endnum = $page->getEachNum() * $page->getNowPage(); if ($_GET['query_start_time']) { $starttime = $_GET['query_start_time']; } if ($_GET['query_end_time']) { $endtime = $_GET['query_end_time']; } // 处理树的参数 if ($_GET['orgids']) { $orgids = implode(',', $_GET['orgids']); } $search_type = $_GET['search_type']; // echo $search_type; $colconfig = $config; // var_dump($config[intval($search_type)]); $displaycol = array(); $displaytext = array(); $sumcol = array(); $totalcol = array(); $groupbycol = array(); $sumtypeparam = array(0 => '0', 1 => '0', 2 => '0', 3 => '0', 4 => '0', 5 => '0'); foreach ($sumtype as $i => $v) { // var_dump($colconfig['sumcol'][$v]); if (isset($colconfig['sqlwher'])) { $sql = $sql . $colconfig['sqlwher']; } if (isset($colconfig['sumcol'][$v])) { if (isset($colconfig['sumcol'][$v]['cols'])) { $sumtypeparam[$colconfig['sumcol'][$v]['value']] = '1'; foreach ($colconfig['sumcol'][$v]['cols'] as $item) { // echo $item['name'] . '<br>'; array_push($sumcol, $sqlarray[$item['name']]); array_push($displaycol, $item['name']); array_push($displaytext, $item['text']); $itemsplit = explode(' as ', $sqlarray[$item['name']]); array_push($totalcol, ' null as ' . $itemsplit[1]); $str = strtolower(str_replace(' ', '', trim($itemsplit[0]))); if (substr($str, 0, 4) != 'sum(' && substr($str, 0, 6) != 'count(') { array_push($groupbycol, $itemsplit[0]); } } } else { $item = $colconfig['sumcol'][$v]; $sumtypeparam[$item['value']] = '1'; array_push($sumcol, $sqlarray[$item['name']]); array_push($displaycol, $item['name']); array_push($displaytext, $item['text']); $itemsplit = explode(' as ', $sqlarray[$item['name']]); array_push($totalcol, ' null as ' . $itemsplit[1]); $str = strtolower(str_replace(' ', '', trim($itemsplit[0]))); if (substr($str, 0, 4) != 'sum(' && substr($str, 0, 6) != 'count(') { array_push($groupbycol, $itemsplit[0]); } } } } $param1 = implode('', $sumtypeparam); array_push($displaytext, '充值下账信息'); array_push($displaytext, '诊疗购买信息'); $tsql = "SET NOCOUNT ON; Exec pFMemberPayStat '{$param1}','{$orgids}','{$starttime}','{$endtime}','','{$startnum}','{$endnum}';SET NOCOUNT off; "; // echo $tsql; $stmt = $conn->prepare($tsql); $stmt->execute(); $data_list = array(); while ($row = $stmt->fetchObject()) { array_push($data_list, $row); } Tpl::output('data_list', $data_list); Tpl::output('page', $page->show()); // // var_dump($totalcol); // $totalcol[0] = '\'总计:\' as ' . explode(' as ', $totalcol[0])[1]; // // var_dump($totalcol); // $totalcolstr = join(',', $totalcol); // $sumcolstr = join(',', $sumcol); // $groupbycolstr = join(',', $groupbycol); // // echo $sumcolstr; // $tsql = " select $sumcolstr , sum(RechargeMoney) rechargemMoney,sum(GiveMoney) givemoney , sum(RechargeMoney+GiveMoney) allmoney // $sql group by $groupbycolstr order by $groupbycolstr "; // //处理合计 // $totalsql = " select $totalcolstr , count(1) cliniccount // $sql "; // if(isset($_GET['export']) && $_GET['export']=='true'){ // $this->exportxlsx(array(0=>$tsql,1=>$totalsql),$displaytext,'充值下账汇总'); // } // $stmt = $conn->query($tsql); // $data_list = array(); // while ($row = $stmt->fetch(PDO::FETCH_OBJ)) { // array_push($data_list, $row); // } // // echo $totalsql; // $totalstmt = $conn->query($totalsql); // while ($row = $totalstmt->fetch(PDO::FETCH_OBJ)) { // array_push($data_list, $row); // } // Tpl::output('data_list', $data_list); // //--0:期初入库 1:采购入库 2:购进退回 3:盘盈 5:领用 12:盘亏 14:领用退回 50:采购计划 // Tpl::output('page', $page->show()); // //处理需要显示的列 // $col = array(); // foreach ($sumtype as $i => $v) { // if (isset($sumtypestr[$v])) { // foreach ($sumtypestr[$v] as $key => $item) { // $col[$key] = $item; // } // } // } // var_dump($col); Tpl::output('displaycol', $displaycol); Tpl::output('displaytext', $displaytext); Tpl::showpage('member.recharge.sum'); }
public function checkOp() { $orderbys = array(array('txt' => '会员编号', 'col' => ' member_id '), array('txt' => '机构编码', 'col' => ' orgid '), array('txt' => '预存余额', 'col' => ' available_predeposit '), array('txt' => '赠送余额', 'col' => ' fConsumeBalance '), array('txt' => '消费积分', 'col' => ' member_points ')); Tpl::output('orderbys', $orderbys); $conn = (require BASE_DATA_PATH . '/../core/framework/db/mssqlpdo.php'); //处理数据 $page = new Page(); $page->setEachNum(10); $page->setNowPage($_REQUEST["curpage"]); $startnum = $page->getEachNum() * ($page->getNowPage() - 1); $endnum = $page->getEachNum() * $page->getNowPage(); $sql = ''; if ($_GET['orgids']) { $sql = $sql . ' and CreateOrgID in ( ' . implode(',', $_GET['orgids']) . ')'; } if (isset($_GET['cardtype']) and $_GET['cardtype'] != '') { $condition['cardtype'] = $_GET['cardtype']; $sql = $sql . ' and cardtype = \'' . $_GET['cardtype'] . '\''; } if (isset($_GET['cardgrade']) and $_GET['cardgrade'] != '') { $sql = $sql . ' and cardgrade = \'' . $_GET['cardgrade'] . '\''; } if (!isset($_GET['order'])) { $ordersql = 'desc'; } else { $ordersql = $_GET['order']; } if ($_GET['orderby']) { foreach ($orderbys as $orderby) { if ($orderby['txt'] == $_GET['orderby']) { $order = $orderby['col'] . ' ' . $ordersql; break; } } } if ($_GET['search_field_value'] != '') { switch ($_GET['search_field_name']) { case 'member_name': $sql = $sql . ' and member_name like \'%' . $_GET['search_field_value'] . '%\''; break; case 'member_email': $sql = $sql . ' and member_email like \'%' . $_GET['search_field_value'] . '%\''; break; case 'member_truename': $sql = $sql . ' and member_truename like \'%' . $_GET['search_field_value'] . '%\''; break; } } if ($_GET['member_id'] != '') { $sql = $sql . ' and member_id like \'%' . $_GET['member_id'] . '%\''; } switch ($_GET['search_state']) { case 'no_informallow': $sql = $sql . ' and inform_allow = \'2\' '; break; case 'no_isbuy': $sql = $sql . ' and is_buy = \'0\' '; break; case 'no_isallowtalk': $sql = $sql . ' and is_allowtalk = \'0\' '; break; case 'no_memberstate': $sql = $sql . ' and member_state = \'0\' '; break; } // if (!$_GET ['flag1'] && !$_GET ['flag2'] && !$_GET ['flag3']){ // $_GET ['flag1'] = '1'; // } if ($_GET['flag1'] && $_GET['flag1'] == '1') { $flag1 = 1; } else { $flag1 = 0; } if ($_GET['flag2'] && $_GET['flag2'] == '1') { $flag2 = 1; } else { $flag2 = 0; } if ($_GET['flag3'] && $_GET['flag3'] == '1') { $flag3 = 1; } else { $flag3 = 0; } /** * 排序 */ $orderbysql = ' member_id '; $ordersql = 'desc'; if (!isset($_GET['orderby'])) { $_GET['orderby'] = '会员编号'; } if (!isset($_GET['order'])) { $ordersql = 'desc'; } else { $ordersql = $_GET['order']; } foreach ($orderbys as $orderby) { if ($orderby['txt'] == $_GET['orderby']) { $orderbysql = $orderby['col']; break; } } $paramsql = $sql . ' order by ' . $orderbysql . $ordersql; $paramsql = str_replace('\'', '\'\'', $paramsql); $tsql = "SET NOCOUNT ON; Exec p_query_member_check '{$paramsql}','{$startnum}','{$endnum}',{$flag1},{$flag2},{$flag3};SET NOCOUNT off; "; // echo $tsql; $stmt = $conn->query($tsql); //第一次获得总页数 $total = $stmt->fetch(PDO::FETCH_NUM); $page->setTotalNum($total[0]); //第二次获得数据 $stmt->nextRowset(); $data_list = array(); while ($row = $stmt->fetch(PDO::FETCH_OBJ)) { array_push($data_list, $row); } Tpl::output('data_list', $data_list); Tpl::output('orderbys', $orderbys); Tpl::output('page', $page->show()); Tpl::showpage('member.check'); }
public function incomesumOp() { $conn = (require BASE_DATA_PATH . '/../core/framework/db/mssqlpdo.php'); if (!isset($_GET['search_type'])) { $_GET['search_type'] = '0'; } $sqlarray = array('iCO_Type' => 'ico.name as "iCO_Type"', 'MakePerson' => ' sMakePerson as "MakePerson" ', 'section' => ' sStatSection as "section" ', 'doctor' => ' sDoctor as "doctor" ', 'iCO_GatherType' => ' gather.name as "iCO_GatherType" ', 'year' => ' year(a.dCO_Date) as "year" ', 'month' => ' left(convert(varchar,dCO_Date,112),6) as "month" ', 'day' => ' convert(varchar,dCO_Date,112) as "day" ', 'OrgID' => ' org.name as "OrgID" '); $config = array('sumcol' => array('OrgID' => array(name => 'OrgID', 'text' => '机构'), 'iCO_Type' => array(name => 'iCO_Type', 'text' => '类型', map => $this->types), 'section' => array(name => 'section', 'text' => '科室'), 'doctor' => array(name => 'doctor', 'text' => '医生'), 'MakePerson' => array(name => 'MakePerson', 'text' => '收费员'), 'iCO_GatherType' => array(name => 'iCO_GatherType', 'text' => '医保类型'), 'year' => array('text' => '年', name => 'year', uncheck => 'month,day'), 'month' => array('text' => '月', name => 'month', uncheck => 'year,day'), 'day' => array('text' => '日', name => 'day', uncheck => 'year,month'))); Tpl::output('config', $config); //处理汇总字段 $sumtype = $_GET['sumtype']; if ($sumtype == null) { $sumtype = array(0 => "OrgID"); $_GET['sumtype'] = $sumtype; } $checked = $_GET['checked']; $page = new Page(); $page->setEachNum(10); $page->setNowPage($_REQUEST["curpage"]); $sql = 'from Center_CheckOut a left join Center_codes gather on a.iCO_GatherType = gather.code and gather.type=\'iCO_GatherType\' , Center_codes ico,Center_codes state, Organization org where a.iCO_Type = ico.code and ico.type=\'iCO_Type\' and a.iCO_State = state.code and state.type=\'iCO_State\' and a.orgid = org.id '; if ($_GET['query_start_time']) { $sql = $sql . ' and a.dCO_Date >=\'' . $_GET['query_start_time'] . '\''; } if ($_GET['query_end_time']) { $sql = $sql . ' and a.dCO_Date < dateadd(day,1,\'' . $_GET['query_end_time'] . '\')'; } //处理树的参数 if ($_GET['orgids']) { $sql = $sql . ' and a.OrgID in ( ' . implode(',', $_GET['orgids']) . ')'; } $search_type = $_GET['search_type']; // echo $search_type; $colconfig = $config; // var_dump($config[intval($search_type)]); $displaycol = array(); $displaytext = array(); $sumcol = array(); $totalcol = array(); $groupbycol = array(); foreach ($sumtype as $i => $v) { // var_dump($colconfig['sumcol'][$v]); if (isset($colconfig['sqlwher'])) { $sql = $sql . $colconfig['sqlwher']; } if (isset($colconfig['sumcol'][$v])) { if (isset($colconfig['sumcol'][$v]['cols'])) { foreach ($colconfig['sumcol'][$v]['cols'] as $item) { // echo $item['name'] . '<br>'; array_push($sumcol, $sqlarray[$item['name']]); array_push($displaycol, $item['name']); array_push($displaytext, $item['text']); $itemsplit = explode(' as ', $sqlarray[$item['name']]); array_push($totalcol, ' null as ' . $itemsplit[1]); $str = strtolower(str_replace(' ', '', trim($itemsplit[0]))); if (substr($str, 0, 4) != 'sum(' && substr($str, 0, 6) != 'count(') { array_push($groupbycol, $itemsplit[0]); } } } else { $item = $colconfig['sumcol'][$v]; array_push($sumcol, $sqlarray[$item['name']]); array_push($displaycol, $item['name']); array_push($displaytext, $item['text']); $itemsplit = explode(' as ', $sqlarray[$item['name']]); array_push($totalcol, ' null as ' . $itemsplit[1]); $str = strtolower(str_replace(' ', '', trim($itemsplit[0]))); if (substr($str, 0, 4) != 'sum(' && substr($str, 0, 6) != 'count(') { array_push($groupbycol, $itemsplit[0]); } } } } array_push($displaytext, '应缴现金'); array_push($displaytext, '处方金额'); array_push($displaytext, '统筹支付'); array_push($displaytext, '医保卡支付'); array_push($displaytext, '现金支付'); array_push($displaytext, '银行卡付'); array_push($displaytext, '处方总数'); array_push($displaytext, '预存下账'); array_push($displaytext, '赠送下账'); array_push($displaytext, '积分下账金额'); // var_dump($totalcol); $totalcol[0] = '\'总计:\' as ' . explode(' as ', $totalcol[0])[1]; // var_dump($totalcol); $totalcolstr = join(',', $totalcol); $sumcolstr = join(',', $sumcol); $groupbycolstr = join(',', $groupbycol); // echo $sumcolstr; $tsql = " select {$sumcolstr} ,\n Sum(fCO_InComeMoney - fCO_Medicare - fCO_Card - fCO_PosPay - fRecharge - fConsume - fScaleToMoney) factmoney,\n Sum(fCO_InComeMoney) incomemoney,\n Sum(fCO_Medicare) medicare,\n Sum(fCO_Card) cardmoney,\n Sum(fCO_Cash) cashmoney,\n Sum(fCO_PosPay) postpaymoney,\n Sum( case when RecipeID > 0 and fCO_IncomeMoney>0 then 1 when RecipeID > 0 and fCO_IncomeMoney<0 then -1 end ) cliniccount,\n Sum(fRecharge) sumfRecharge,\n Sum(fConsume) sumfConsume,\n Sum(fScaleToMoney) scaletomoney\n\n {$sql} group by {$groupbycolstr} order by {$groupbycolstr} "; // echo $tsql; //处理合计 $totalsql = " select {$totalcolstr} ,\n Sum(fCO_InComeMoney - fCO_Medicare - fCO_Card - fCO_PosPay - fRecharge - fConsume - fScaleToMoney) factmoney,\n Sum(fCO_InComeMoney) incomemoney,\n Sum(fCO_Medicare) medicare,\n Sum(fCO_Card) cardmoney,\n Sum(fCO_Cash) cashmoney,\n Sum(fCO_PosPay) postpaymoney,\n Sum( case when RecipeID > 0 and fCO_IncomeMoney>0 then 1 when RecipeID > 0 and fCO_IncomeMoney<0 then -1 end ) cliniccount,\n Sum(fRecharge) sumfRecharge,\n Sum(fConsume) sumfConsume,\n Sum(fScaleToMoney) scaletomoney\n {$sql} "; if (isset($_GET['export']) && $_GET['export'] == 'true') { $this->exportxlsx(array(0 => $tsql, 1 => $totalsql), $displaytext, '收入汇总'); } $stmt = $conn->query($tsql); $data_list = array(); while ($row = $stmt->fetch(PDO::FETCH_OBJ)) { array_push($data_list, $row); } // echo $totalsql; $totalstmt = $conn->query($totalsql); while ($row = $totalstmt->fetch(PDO::FETCH_OBJ)) { array_push($data_list, $row); } Tpl::output('data_list', $data_list); //--0:期初入库 1:采购入库 2:购进退回 3:盘盈 5:领用 12:盘亏 14:领用退回 50:采购计划 Tpl::output('page', $page->show()); //处理需要显示的列 $col = array(); foreach ($sumtype as $i => $v) { if (isset($sumtypestr[$v])) { foreach ($sumtypestr[$v] as $key => $item) { $col[$key] = $item; } } } // var_dump($col); Tpl::output('displaycol', $displaycol); Tpl::output('displaytext', $displaytext); Tpl::showpage('community.income.sum'); }