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');
}
