/**
* 新增会员
*/
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 stracelistOp(){
//查询收藏的店铺
$model_favorites = Model('favorites');
//条件
$where = array('member_id' => $_SESSION['member_id']);
$fav_store_id = $model_favorites->getStoreFavoritesList($where, 'fav_id');
// 整理
if(!empty($fav_store_id) && is_array($fav_store_id)){
$storeid_array = '';
foreach($fav_store_id as $val){
$storeid_array[] = $val['fav_id'];
}
$where = array(
'strace_storeid'=>array('in',$storeid_array),
'strace_state'=>1
);
$model_stracelog = Model('store_sns_tracelog');
$count = $model_stracelog->getStoreSnsTracelogCount($where);
//分页
$page = new Page();
$page->setEachNum(30);
$page->setStyle('admin');
$page->setTotalNum($count);
$delaypage = intval($_GET['delaypage'])>0?intval($_GET['delaypage']):1;//本页延时加载的当前页数
$lazy_arr = lazypage(10,$delaypage,$count,true,$page->getNowPage(),$page->getEachNum(),$page->getLimitStart());
//动态列表
$limit = $lazy_arr['limitstart'].",".$lazy_arr['delay_eachnum'];
$strace_array = $count = $model_stracelog->getStoreSnsTracelogList($where, '*', 'strace_id desc', $limit);
if (!empty($strace_array)){
foreach ($strace_array as $key=>$val){
if($val['strace_content'] == ''){
$val['strace_goodsdata'] = json_decode($val['strace_goodsdata'],true);
if( CHARSET == 'GBK') {
foreach ((array)$val['strace_goodsdata'] as $k=>$v){
$val['strace_goodsdata'][$k] = Language::getGBK($v);
}
}
$content = $model_stracelog->spellingStyle($val['strace_type'], $val['strace_goodsdata']);
$strace_array[$key]['strace_content'] = str_replace("%siteurl%", SHOP_SITE_URL.DS, $content);
}
}
}
Tpl::output('show_page',$page->show());
}
Tpl::output('hasmore',$lazy_arr['hasmore']);
Tpl::output('strace_array',$strace_array);
Tpl::output('type','index');
Tpl::showpage('member_storesns.tracelist','null_layout');
}
/**
* SNS动态列表
*/
public function tracelistOp(){
//查询关注以及好友列表
$friend_model = Model('sns_friend');
$friend_list = $friend_model->listFriend(array('friend_frommid'=>"{$_SESSION['member_id']}"),'*','','simple');
$mutualfollowid_arr = array();
$followid_arr = array();
if (!empty($friend_list)){
foreach ($friend_list as $k=>$v){
$followid_arr[] = $v['friend_tomid'];
if ($v['friend_followstate'] == 2){
$mutualfollowid_arr[] = $v['friend_tomid'];
}
}
}
$tracelog_model = Model('sns_tracelog');
//条件
$condition = array();
$condition['allowshow'] = '1';
$condition['allowshow_memberid'] = "{$_SESSION['member_id']}";
$condition['allowshow_followerin'] = "";
if (!empty($followid_arr)){
$condition['allowshow_followerin'] = implode("','",$followid_arr);
}
$condition['allowshow_friendin'] = "";
if (!empty($mutualfollowid_arr)){
$condition['allowshow_friendin'] = implode("','",$mutualfollowid_arr);
}
$condition['trace_state'] = "0";
$count = $tracelog_model->countTrace($condition);
//分页
$page = new Page();
$page->setEachNum(30);
$page->setStyle('admin');
$page->setTotalNum($count);
$delaypage = intval($_GET['delaypage'])>0?intval($_GET['delaypage']):1;//本页延时加载的当前页数
$lazy_arr = lazypage(10,$delaypage,$count,true,$page->getNowPage(),$page->getEachNum(),$page->getLimitStart());
//动态列表
$condition['limit'] = $lazy_arr['limitstart'].",".$lazy_arr['delay_eachnum'];
$tracelist = $tracelog_model->getTracelogList($condition);
if (!empty($tracelist)){
foreach ($tracelist as $k=>$v){
if ($v['trace_title']){
$v['trace_title'] = str_replace("%siteurl%", SHOP_SITE_URL.DS, $v['trace_title']);
$v['trace_title_forward'] = '|| @'.$v['trace_membername'].Language::get('nc_colon').preg_replace("/<a(.*?)href=\"(.*?)\"(.*?)>@(.*?)<\/a>([\s|:|：]|$)/is",'@${4}${5}',$v['trace_title']);
}
if(!empty($v['trace_content'])){
//替换内容中的siteurl
$v['trace_content'] = str_replace("%siteurl%", SHOP_SITE_URL.DS, $v['trace_content']);
}
$tracelist[$k] = $v;
}
}
Tpl::output('hasmore',$lazy_arr['hasmore']);
Tpl::output('tracelist',$tracelist);
Tpl::output('show_page',$page->show());
Tpl::output('type','index');
Tpl::showpage('member_snstracelist','null_layout');
}
/**
* 某会员的SNS动态列表
*/
public function tracelistOp()
{
$tracelog_model = Model('sns_tracelog');
$condition = array();
$condition['trace_memberid'] = $this->master_id;
switch ($this->relation) {
case 3:
$condition['trace_privacyin'] = "";
break;
case 2:
$condition['trace_privacyin'] = "0','1";
break;
case 1:
$condition['trace_privacyin'] = "0";
break;
default:
$condition['trace_privacyin'] = "0";
break;
}
$condition['trace_state'] = "0";
$count = $tracelog_model->countTrace($condition);
//分页
$page = new Page();
$page->setEachNum(30);
$page->setStyle('admin');
$page->setTotalNum($count);
$delaypage = intval($_GET['delaypage']) > 0 ? intval($_GET['delaypage']) : 1;
//本页延时加载的当前页数
$lazy_arr = lazypage(10, $delaypage, $count, true, $page->getNowPage(), $page->getEachNum(), $page->getLimitStart());
//动态列表
$condition['limit'] = $lazy_arr['limitstart'] . "," . $lazy_arr['delay_eachnum'];
$tracelist = $tracelog_model->getTracelogList($condition);
if (!empty($tracelist)) {
foreach ($tracelist as $k => $v) {
if ($v['trace_title']) {
$v['trace_title'] = str_replace("%siteurl%", SHOP_SITE_URL . DS, $v['trace_title']);
$v['trace_title_forward'] = '|| @' . $v['trace_membername'] . Language::get('nc_colon') . preg_replace("/<a(.*?)href=\"(.*?)\"(.*?)>@(.*?)<\\/a>([\\s|:|：]|\$)/is", '@${4}${5}', $v['trace_title']);
}
if (!empty($v['trace_content'])) {
//替换内容中的siteurl
$v['trace_content'] = str_replace("%siteurl%", SHOP_SITE_URL . DS, $v['trace_content']);
}
$tracelist[$k] = $v;
}
}
Tpl::output('hasmore', $lazy_arr['hasmore']);
Tpl::output('tracelist', $tracelist);
Tpl::output('show_page', $page->show());
Tpl::output('type', 'home');
//验证码
Tpl::output('nchash', substr(md5(SHOP_SITE_URL . $_GET['act'] . $_GET['op']), 0, 8));
Tpl::output('menu_sign', 'snstrace');
Tpl::showpage('sns_tracelist', 'null_layout');
}
/**
* 新增会员
*/
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');
}
/**
* 新增会员
*/
public function detailOp()
{
$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 Center_Buy a , Center_Drug b , Organization c, Organization d,shopnc_goods_common good ,
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 = good.goods_commonid
and a.iBuy_Type = storetype.code and storetype.type=\'iBuy_Type\' ';
if (!isset($_GET['search_type'])) {
$_GET['search_type'] = '1';
}
if (gettype($_GET['search_type']) == 'string' && intval($_GET['search_type']) >= 0) {
$sql = $sql . ' and a.iBuy_Type = \'' . $_GET['search_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 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['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']) . ')';
}
}
//处理树的参数
$checkednode = $_GET['checkednode'];
if ($checkednode && isset($checkednode) && count($checkednode) > 0) {
$sql = $sql . " and a.SaleOrgID in ({$checkednode}) ";
}
$countsql = " select count(*) {$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 a.dBuy_Date) rownum,\n a.iBuy_TicketID,\n a.sBuy_A6,\n a.iBuy_ID,\n a.dBuy_Date,\n b.iDrug_RecType,\n storetype.name as 'iBuy_Type',\n d.name OrgId,\n c.name SaleOrgID,\n good.sdrug_chemname,\n good.spec_name,\n good.sdrug_unit,\n a.iDrug_ID,\n a.fBuy_FactNum,\n a.sBuy_DrugUnit,\n a.fBuy_TaxMoney,\n a.fBuy_RetailMoney,\n a.fBuy_RetailMoney - a.fBuy_TaxMoney as diffmoney\n\n {$sql} order by a.dBuy_Date)zzzz where rownum>{$startnum} )zzzzz order by rownum";
// echo $sql;
$exportsql = "SELECT\n a.iBuy_TicketID,\n a.iBuy_ID,\n a.dBuy_Date,\n b.iDrug_RecType,\n storetype.name as 'iBuy_Type',\n d.name OrgId,\n a.iDrug_ID,\n good.sdrug_chemname,\n good.spec_name,\n good.sdrug_unit,\n a.fBuy_FactNum,\n a.fBuy_TaxMoney,\n a.fBuy_RetailMoney,\n a.fBuy_RetailMoney - a.fBuy_TaxMoney as diffmoney\n {$sql} order by a.dBuy_Date ";
$exporttotalsql = "SELECT\n '总计：' as iBuy_TicketID,\n null as iBuy_ID,\n null as dBuy_Date,\n null as iDrug_RecType,\n null as iBuy_Type,\n null as OrgId,\n null as iDrug_ID,\n null as sdrug_chemname,\n null as spec_name,\n null as sdrug_unit,\n null as fBuy_FactNum,\n sum(fBuy_TaxMoney) as fBuy_TaxMoney,\n sum(fBuy_RetailMoney) as fBuy_RetailMoney,\n sum(fBuy_RetailMoney)-sum(fBuy_TaxMoney) as diffmoney\n {$sql} ";
if (isset($_GET['export']) && $_GET['export'] == 'true') {
$this->exportxlsx(array(0 => $exportsql, 1 => $exporttotalsql), array('总票据', '明细号', '发生日期', '商品类型', '单据类型', '机构', '商品编码', '商品名称', '规格', '单位', '数量', '进价金额', '零价金额', '进销差'), '仓库单据明细');
}
$stmt = $conn->query($tsql);
$data_list = array();
while ($row = $stmt->fetch(PDO::FETCH_OBJ)) {
array_push($data_list, $row);
}
$totalsql = " select '总计：' as iBuy_TicketID,\n null as sBuy_A6,\n null as iBuy_ID,\n null as dBuy_Date,\n null as iDrug_RecType,\n null as iBuy_Type,\n null as OrgId,\n null as SaleOrgID,\n null as iDrug_ID,\n null as fBuy_FactNum,\n null as sBuy_DrugUnit,\n sum(fBuy_TaxMoney) as fBuy_TaxMoney,\n sum(fBuy_RetailMoney) as fBuy_RetailMoney,\n sum(fBuy_RetailMoney)-sum(fBuy_TaxMoney) as diffmoney\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('storehouse.detail');
}
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 saledetailOp()
{
$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 Center_ClinicSale a
left join shopnc_goods_common good on a.iDrug_ID = good.goods_commonid
left join Center_Class class on good.iDrug_StatClass = class.iClass_ID
, Organization org
where a.orgid = org.id ';
if ($_GET['itemtype']) {
$sql = $sql . ' and a.itemtype =\'' . $_GET['itemtype'] . '\'';
}
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['orgids']) {
$sql = $sql . ' and a.OrgID in ( ' . implode(',', $_GET['orgids']) . ')';
}
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']);
}
//处理树的参数
$checkednode = $_GET['checkednode'];
if ($checkednode && isset($checkednode) && count($checkednode) > 0) {
$sql = $sql . " and a.SaleOrgID in ({$checkednode}) ";
}
$countsql = " select count(*) {$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 a.dSale_MakeDate desc) rownum,\n a.sSale_id ,\n a.dSale_MakeDate,\n good.sDrug_TradeName ,\n a.ItemType ,\n good.sDrug_Spec ,\n good.sDrug_Unit ,\n good.sDrug_Brand ,\n a.fSale_Num ,\n a.fSale_TaxPrice ,\n a.fSale_TaxFactMoney ,\n org.name,\n a.StatSection,\n a.DoctorName,\n a.sClinicKey ,\n a.ida_id\n {$sql} order by a.dSale_MakeDate desc)zzzz where rownum>{$startnum} )zzzzz order by rownum";
// echo $sql;
$exportsql = "SELECT row_number() over( order by a.dSale_MakeDate desc) rownum,\n a.sSale_id ,\n a.dSale_MakeDate,\n good.sDrug_TradeName ,\n a.ItemType ,\n good.sDrug_Spec ,\n good.sDrug_Unit ,\n good.sDrug_Brand ,\n a.fSale_Num ,\n a.fSale_TaxPrice ,\n a.fSale_TaxFactMoney ,\n org.name,\n a.StatSection,\n a.DoctorName,\n a.sClinicKey ,\n a.ida_id\n {$sql} order by a.dSale_MakeDate desc ";
if (isset($_GET['export']) && $_GET['export'] == 'true') {
$this->exportxlsx($exportsql, array('序号', '单据编号', '制单日期', '项目名称', '项目类型', '规格', '单位', '产地/厂商', '数量', '单价', '金额', '机构', '科室', '医生', '就诊流水', '处方流水'), '销售明细');
}
$stmt = $conn->query($tsql);
$data_list = array();
while ($row = $stmt->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('finance.saledetail');
}
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 incomedetailOp()
{
$conn = (require BASE_DATA_PATH . '/../core/framework/db/mssqlpdo.php');
//处理数据
$page = new Page();
$page->setEachNum(10);
// $page->setStyle(5);
$page->setNowPage($_REQUEST["curpage"]);
$startnum = $page->getEachNum() * ($page->getNowPage() - 1);
$endnum = $page->getEachNum() * $page->getNowPage();
$sql = 'from Center_CheckOut a left join Center_ClinicLog log on a.sLogKey = log.ClinicKey and a.orgid = log.orgid
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 (!isset($_GET['search_type'])) {
// $_GET['search_type'] = '1';
// }
// if (gettype($_GET['search_type']) == 'string' && intval($_GET['search_type']) >= 0) {
// $sql = $sql . ' and a.iBuy_Type = \'' . $_GET['search_type'] . '\'';
// }
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']) . ')';
}
if (isset($_GET['types']) and $_GET['types'] != '') {
$sql = $sql . ' and a.iCO_Type = ' . $_GET['types'] . '';
}
//处理树的参数
$checkednode = $_GET['checkednode'];
if ($checkednode && isset($checkednode) && count($checkednode) > 0) {
$sql = $sql . " and a.orgid in ({$checkednode}) ";
}
$moneycol = array('fCO_Foregift', 'fCO_Balance', 'fCO_FactMoney', 'fCO_IncomeMoney', 'fCO_GetMoney', 'fCO_PayMoney', 'fCO_Card', 'fCO_Cash', 'fCO_StartMoney', 'fCO_Medicare', 'fCO_SelfCost', 'fCO_SelfPay', 'fCO_HospitalSubsidy', 'fCO_BeforeSubsidy', 'fOweMoney', 'fCO_PosPay', 'fRecharge', 'fConsume', 'fRechargeBalance', 'fConsumeBalance', 'fGive', 'fCanConsume');
Tpl::output('moneycol', $moneycol);
$countsql = " select count(*) {$sql} ";
$stmt = $conn->query($countsql);
$total = $stmt->fetch(PDO::FETCH_NUM);
$page->setTotalNum($total[0]);
$orderbys = array(array('txt' => '类型', 'col' => ' a.iCO_Type '), array('txt' => '结算日期', 'col' => ' a.dCO_Date '), array('txt' => '制单日期', 'col' => ' a.dCO_MakeDate '), array('txt' => '收费员', 'col' => ' a.iCO_MakePerson '), array('txt' => '病人姓名', 'col' => ' log.sSickName '), array('txt' => '处方金额', 'col' => ' a.fCO_IncomeMoney '), array('txt' => '统筹支付', 'col' => ' a.fCO_Medicare '), array('txt' => '医保卡支付', 'col' => ' a.fCO_Card '), array('txt' => '现金支付', 'col' => ' a.fCO_Cash '), array('txt' => '银行卡付', 'col' => ' a.fCO_PosPay '), array('txt' => '预存下账', 'col' => ' a.fRecharge '), array('txt' => '赠送下账', 'col' => ' a.fConsume '), array('txt' => '积分下账金额', 'col' => ' a.fScaleToMoney '));
$orderbysql = ' a.dCO_Date ';
$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;
}
}
$tsql = "SELECT * FROM ( SELECT * FROM (SELECT TOP {$endnum} row_number() over( order by {$orderbysql} {$ordersql}) rownum,\n ico.name as 'iCO_Type',\n sMakePerson 'iCO_MakePerson',\n a.dCO_Date,\n a.dCO_MakeDate,\n a.fCO_Foregift,\n a.fCO_Balance,\n a.fCO_FactMoney,\n a.fCO_IncomeMoney ,\n a.fCO_GetMoney,\n a.fCO_PayMoney,\n gather.name as 'iCO_GatherType',\n state.name as 'iCO_State',\n a.sCO_CapitalMoney,\n a.sCO_Remark,\n a.fCO_Card,\n a.fCO_Cash,\n a.fCO_StartMoney,\n a.fCO_Medicare,\n a.fCO_SelfCost,\n a.fCO_SelfPay,\n a.fCO_HospitalSubsidy,\n a.sCO_SubsidyReason,\n a.fCO_BeforeSubsidy,\n a.fOweMoney,\n a.fCO_PosPay,\n a.sMemberID,\n a.sMemberAssistantID,\n a.fRecharge,\n a.fConsume,\n a.fRechargeBalance,\n a.fConsumeBalance,\n a.fScale,\n a.fScaleBalance,\n a.fScaleToMoney,\n a.fGive,\n a.fCanConsume,\n a.fCanScale,\n a.fCanGive,\n a.fAddScale,\n org.name as 'OrgID',\n log.sSickName\n {$sql} order by {$orderbysql} {$ordersql} )zzzz where rownum>{$startnum} )zzzzz order by rownum";
// echo $tsql;
// $exportsql = "SELECT row_number() over( order by a.dCO_Date desc) rownum,
// ico.name as 'iCO_Type',
// a.dCO_Date,
// a.dCO_MakeDate,
// person.sPerson_Name 'iCO_MakePerson',
// a.fCO_Foregift,
// a.fCO_Balance,
// a.fCO_FactMoney,
// a.fCO_IncomeMoney ,
// a.fCO_GetMoney,
// a.fCO_PayMoney,
// a.sCO_CapitalMoney
// $sql order by a.dCO_Date desc ";
//
// Sum(fCO_InComeMoney - fCO_Medicare - fCO_Card - fCO_PosPay - fRecharge - fConsume - fScaleToMoney) factmoney,
// Sum(fCO_InComeMoney) incomemoney,
// Sum(fCO_Medicare) medicare,
// Sum(fCO_Card) cardmoney,
// Sum(fCO_Cash) cashmoney,
// Sum(fCO_PosPay) postpaymoney,
// Sum( case when RecipeID > 0 and fCO_IncomeMoney>0 then 1 when RecipeID > 0 and fCO_IncomeMoney<0 then -1 end ) cliniccount,
// Sum(fRecharge) sumfRecharge,
// Sum(fConsume) sumfConsume,
// Sum(fScaleToMoney) scaletomoney
$exportsql = "SELECT row_number() over( order by a.dCO_Date desc) as '序号',\n ico.name as '类型',\n a.dCO_Date '结算日期',\n a.dCO_MakeDate '制单日期',\n sMakePerson '收费员',\n log.sSickName '病人姓名',\n a.fCO_InComeMoney '处方金额',\n a.fCO_Medicare '统筹支付',\n a.fCO_Card '医保卡支付',\n a.fCO_Cash '现金支付' ,\n a.fCO_PosPay '银行卡付',\n a.fRecharge '预存下账',\n a.fConsume '赠送下账',\n a.fScaleToMoney '积分下账金额'\n {$sql} order by {$orderbysql} {$ordersql} ";
// echo $_GET['export']=='true';
// echo $_GET['export'];
$sumcols = array(array('txt' => '处方金额汇总', 'col' => 'fCO_InComeMoney', 'decimal' => 2, 'unit' => '元'), array('txt' => '现金支付汇总', 'col' => 'fCO_Cash', 'decimal' => 2, 'unit' => '元'), array('txt' => '总记录数', 'col' => 'rownum', sql => 'count(*) rownum', 'decimal' => 0, 'unit' => '条'));
$sumcolsstr = $page->getSumsql($sumcols);
$sumsql = " select {$sumcolsstr} {$sql} ";
// echo json_encode( $sumsql);
$stmt = $conn->query($sumsql);
$sumrow = $stmt->fetch(PDO::FETCH_OBJ);
foreach ($sumcols as &$col) {
$col['num'] = $sumrow->{$col}['col'];
}
$page->setSumarray($sumcols);
// echo json_encode( $sumcols);
if (isset($_GET['export']) && $_GET['export'] == 'true') {
$this->exportxlsx($exportsql, array('序号', '类型', '结算日期', '制单日期', '收费员', '病人姓名', '处方金额', '统筹支付', '医保卡支付', '现金支付', '银行卡付', '预存下账', '赠送下账', '积分下账金额'), '收入明细');
}
$stmt = $conn->query($tsql);
$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('community.income.detail');
}
public function incomedetailOp()
{
$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 Center_CheckOut a , Center_codes ico, Center_codes gather,Center_codes state,Center_codes tag,
Organization org
where a.iCO_Type = ico.code and ico.type=\'iCO_Type\'
and a.iCO_GatherType = gather.code and gather.type=\'iCO_GatherType\'
and a.iCO_State = state.code and state.type=\'iCO_State\'
and a.iCO_Tag = tag.code and tag.type=\'iCO_Tag\'
and a.orgid = org.id ';
// if (!isset($_GET['search_type'])) {
// $_GET['search_type'] = '1';
// }
// if (gettype($_GET['search_type']) == 'string' && intval($_GET['search_type']) >= 0) {
// $sql = $sql . ' and a.iBuy_Type = \'' . $_GET['search_type'] . '\'';
// }
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']) . ')';
}
if (isset($_GET['types']) and $_GET['types'] != '') {
$sql = $sql . ' and a.iCO_Type = ' . $_GET['types'] . '';
}
//处理树的参数
$checkednode = $_GET['checkednode'];
if ($checkednode && isset($checkednode) && count($checkednode) > 0) {
$sql = $sql . " and a.orgid in ({$checkednode}) ";
}
$moneycol = array('fCO_Foregift', 'fCO_Balance', 'fCO_FactMoney', 'fCO_IncomeMoney', 'fCO_GetMoney', 'fCO_PayMoney', 'fCO_Card', 'fCO_Cash', 'fCO_StartMoney', 'fCO_Medicare', 'fCO_SelfCost', 'fCO_SelfPay', 'fCO_HospitalSubsidy', 'fCO_BeforeSubsidy', 'fOweMoney', 'fCO_PosPay', 'fRecharge', 'fConsume', 'fRechargeBalance', 'fConsumeBalance', 'fGive', 'fCanConsume');
Tpl::output('moneycol', $moneycol);
$countsql = " select count(*) {$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 a.dCO_Date desc) rownum,\n ico.name as 'iCO_Type',\n sMakePerson 'iCO_MakePerson',\n a.dCO_Date,\n a.dCO_MakeDate,\n a.fCO_Foregift,\n a.fCO_Balance,\n a.fCO_FactMoney,\n a.fCO_IncomeMoney ,\n a.fCO_GetMoney,\n a.fCO_PayMoney,\n gather.name as 'iCO_GatherType',\n state.name as 'iCO_State',\n a.sCO_CapitalMoney,\n a.sCO_Remark,\n tag.name as 'iCO_Tag',\n a.fCO_Card,\n a.fCO_Cash,\n a.fCO_StartMoney,\n a.fCO_Medicare,\n a.fCO_SelfCost,\n a.fCO_SelfPay,\n a.fCO_HospitalSubsidy,\n a.sCO_SubsidyReason,\n a.fCO_BeforeSubsidy,\n a.fOweMoney,\n a.fCO_PosPay,\n a.sMemberID,\n a.sMemberAssistantID,\n a.fRecharge,\n a.fConsume,\n a.fRechargeBalance,\n a.fConsumeBalance,\n a.fScale,\n a.fScaleBalance,\n a.fScaleToMoney,\n a.fGive,\n a.fCanConsume,\n a.fCanScale,\n a.fCanGive,\n a.fAddScale,\n org.name as 'OrgID'\n {$sql} order by a.dCO_Date desc)zzzz where rownum>{$startnum} )zzzzz order by rownum";
// $exportsql = "SELECT row_number() over( order by a.dCO_Date desc) rownum,
// ico.name as 'iCO_Type',
// a.dCO_Date,
// a.dCO_MakeDate,
// person.sPerson_Name 'iCO_MakePerson',
// a.fCO_Foregift,
// a.fCO_Balance,
// a.fCO_FactMoney,
// a.fCO_IncomeMoney ,
// a.fCO_GetMoney,
// a.fCO_PayMoney,
// a.sCO_CapitalMoney
// $sql order by a.dCO_Date desc ";
$exportsql = "SELECT row_number() over( order by a.dCO_Date desc) as '序号',\n ico.name as '类型',\n a.dCO_Date '结算日期',\n a.dCO_MakeDate '制单日期',\n sMakePerson '收费员',\n a.fCO_Foregift '押金',\n a.fCO_Balance '结算余额',\n a.fCO_FactMoney '实际金额',\n a.fCO_IncomeMoney '结算金额' ,\n a.fCO_GetMoney '收取金额',\n a.fCO_PayMoney '支付金额',\n a.sCO_CapitalMoney '金额大写'\n {$sql} order by a.dCO_Date desc ";
// echo $_GET['export']=='true';
// echo $_GET['export'];
if (isset($_GET['export']) && $_GET['export'] == 'true') {
$this->exportxlsx($exportsql, array('序号', '类型', '结算日期', '制单日期', '收费员', '押金', '结算余额', '实际金额', '结算金额', '收取金额', '支付金额', '金额大写'), '收入明细');
}
$stmt = $conn->query($tsql);
$data_list = array();
while ($row = $stmt->fetch(PDO::FETCH_OBJ)) {
array_push($data_list, $row);
}
Tpl::output('data_list', $data_list);
Tpl::output('page', $page->show());
Tpl::showpage('community.income.detail');
}
