Example #1
0
 /**
  * 新增会员
  */
 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');
 }
Example #2
0
 /**
  * 参与活动
  */
 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("&nbsp;", $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');
 }
Example #3
0
 /**
  * 新增会员
  */
 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');
 }
Example #4
0
 /**
  * 库存管理
  */
 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');
 }
Example #5
0
 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;
 }
Example #6
0
 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');
 }
Example #7
0
 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');
 }
Example #8
0
 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');
 }
Example #9
0
 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');
 }
Example #10
0
 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');
 }
Example #11
0
 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');
 }