Owner.php 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310
  1. <?php
  2. namespace app\admin\controller;
  3. use think\App;
  4. use think\Db;
  5. use think\Exception;
  6. class Owner extends Auth
  7. {
  8. public function __construct(App $app = null) {
  9. parent::__construct($app);
  10. $this->model= new \app\common\model\Owner();
  11. $this->table= $this->model->table;
  12. }
  13. public function index($import_id=0){
  14. if(request()->isAjax()){
  15. //分页参数
  16. $length = input('rows',10,'intval'); //每页条数
  17. $page = input('page',1,'intval'); //第几页
  18. $start = ($page - 1) * $length; //分页开始位置
  19. //排序
  20. $sortRow = input('sidx','id','trim'); //排序列
  21. $sort = input('sord','desc','trim'); //排序方式
  22. $order = $sortRow.' '.$sort;
  23. $title = input('name','','trim');
  24. if($title){
  25. $map[] = ['name','like','%'.$title.'%'];
  26. }
  27. $title1 = input('phone','','trim');
  28. if($title1){
  29. $map[] = ['phone','like','%'.$title1.'%'];
  30. }
  31. $title2 = input('card','','trim');
  32. if($title2){
  33. $map[] = ['card','like','%'.$title2.'%'];
  34. }
  35. $enable = input('enable','','trim');
  36. if($enable != ''){
  37. $map[] = ['enable','=',$enable];
  38. }
  39. if($import_id >0){
  40. $map[] = ['import_log_id','=',$import_id];
  41. }else{
  42. $map[] = ['del','=',0];
  43. }
  44. $map[] = ['org_id','=',$this->orgId];
  45. $map= empty($map) ? true: $map;
  46. //数据查询
  47. $lists = db($this->table)->where($map)->limit($start,$length)->order($order)->select();
  48. foreach ($lists as $k=>$v){
  49. $lists[$k]['is_show_option'] =$import_id>0?false:true;
  50. }
  51. //数据返回
  52. $totalCount = db($this->table)->where($map)->count();
  53. $totalPage = ceil($totalCount/$length);
  54. $result['page'] = $page;
  55. $result['total'] = $totalPage;
  56. $result['records'] = $totalCount;
  57. $result['rows'] = $lists;
  58. return json($result);
  59. }else{
  60. $logInfo = [];
  61. if($import_id>0){
  62. $logInfo = Db::name('import_log')
  63. ->where('id',$import_id)
  64. ->find();
  65. }
  66. $this->assign('import_id',$import_id);
  67. $this->assign('importInfo',$logInfo);
  68. $this->assign('m_name','业主');
  69. return $this->fetch();
  70. }
  71. }
  72. /**
  73. * 新增/编辑
  74. */
  75. public function add($id=0){
  76. if(request()->isPost()){
  77. $res = $this->model->updates();
  78. if($res){
  79. $this->success('操作成功',url('index'));
  80. }else{
  81. $this->error($this->model->getError());
  82. }
  83. }else{
  84. if($id){
  85. $info =db($this->table)->where('id',$id)->find();
  86. $this->assign('info',$info);
  87. }
  88. return $this->fetch();
  89. }
  90. }
  91. /**
  92. * 删除记录
  93. * @param int $id
  94. */
  95. public function del($id=0){
  96. if(!$id){
  97. $this->error('参数错误');
  98. }
  99. $res = db($this->table)->where('id',$id)->setField('del',1);
  100. if($res){
  101. $this->success('删除成功');
  102. }else{
  103. $this->error('删除失败');
  104. }
  105. }
  106. /**
  107. * 改变字段值
  108. * @param int $fv
  109. * @param string $fn
  110. * @param int $fv
  111. */
  112. public function changeField($id=0,$fn='',$fv=0){
  113. if(!$fn||!$id){
  114. $this->error('参数错误');
  115. }
  116. $res = db($this->table)->where('id',$id)->setField($fn,$fv);
  117. if($res){
  118. $this->success('操作成功');
  119. }else{
  120. $this->error('操作失败');
  121. }
  122. }
  123. // excel
  124. public function import(){
  125. return $this->fetch();
  126. }
  127. /**
  128. * 下载模板
  129. */
  130. public function downloadtem(){
  131. include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
  132. set_time_limit(0);
  133. ini_set("memory_limit","512M");
  134. $fileName = '业主列表.xlsx';
  135. $excel = new \PHPExcel();
  136. $sheet = $excel->setActiveSheetIndex(0);
  137. $arr = array('A','B','C','D','E');
  138. foreach($arr as $k=>$v){
  139. $excel->getActiveSheet()->getStyle($v)->getAlignment()->setWrapText(true);//换行
  140. $excel->getActiveSheet()->getStyle($v.'1')->getFont()->setBold(true);
  141. $excel->getActiveSheet()->getColumnDimension($v)->setWidth(18);
  142. }
  143. $sheet->setCellValueByColumnAndRow(0,1,'姓名');
  144. $sheet->setCellValueByColumnAndRow(1,1,'联系电话');
  145. $sheet->setCellValueByColumnAndRow(2,1,'身份证号');
  146. $sheet->setCellValueByColumnAndRow(3,1,'预付款');
  147. $sheet->setCellValueByColumnAndRow(4,1,'类型(业主/租户)');
  148. ob_end_clean();//清除缓冲区,避免乱码
  149. header('Content-Type: application/vnd.ms-excel');
  150. header('Content-Disposition: attachment;filename="'.$fileName.'"');
  151. header('Cache-Control: max-age=0');
  152. $objWriter = \PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
  153. $objWriter->save('php://output'); //文件通过浏览器下载
  154. }
  155. /**
  156. * 导入
  157. */
  158. public function importexcel(){
  159. include_once env('root_path').'/extend/phpexcel/Classes/PHPExcel.php';
  160. set_time_limit(0);
  161. ini_set("memory_limit", -1);
  162. ob_flush();//清空缓存
  163. flush();//刷新缓存
  164. $orgid = cur_org_id();
  165. $file = request()->file('file');
  166. if(!$file){
  167. exit('请上传文件');
  168. }
  169. // 移动到框架应用根目录/uploads/ 目录下
  170. $info = $file->validate([ 'size'=>config('app.max_upload_file_size') ])
  171. ->move(env('root_path') . 'public' . DIRECTORY_SEPARATOR . 'uploads'. DIRECTORY_SEPARATOR . 'files');
  172. if(!$info){
  173. exit('上传文件失败');
  174. }
  175. $img = env('root_path').'/public/uploads/files/' . $info->getSaveName();
  176. $finfo = $info->getInfo();
  177. $fileExt = explode('.',$finfo['name']);
  178. $finfo['file_name'] = $finfo['name'];
  179. $filepath = str_replace('\\', '/', $img);
  180. $file_type = $fileExt[count($fileExt)-1];
  181. if($file_type == 'xls'){
  182. $objReader = \PHPExcel_IOFactory::createReader('Excel5');
  183. }else{
  184. $objReader = \PHPExcel_IOFactory::createReader('Excel2007');
  185. }
  186. $imData = [
  187. 'org_id'=>$orgid,
  188. 'title'=>$finfo['file_name'],
  189. 'type'=>2,
  190. 'create_time'=>getTime(),
  191. ];
  192. $importId =Db::name('import_log')->insertGetId($imData);
  193. $objPHPExcel = $objReader->load($filepath,$encode='utf-8');
  194. $table = array(0,1,2,3,4);
  195. $allobjWorksheets = $objPHPExcel->getAllSheets();
  196. try{
  197. @unlink($filepath);
  198. foreach($allobjWorksheets as $key=>$objWorksheet){
  199. $highestRow = $objWorksheet->getHighestRow();
  200. $success = 0;
  201. $error = 0;
  202. $errorMsg = [];
  203. for ($row = 2; $row <= $highestRow; ++$row) {
  204. $oneData = array();
  205. for ($col = 0; $col < count($table); ++$col) {
  206. $cell = $objWorksheet->getCellByColumnAndRow($col, $row);
  207. $value = (string)$cell->getValue();
  208. $oneData[$table[$col]]= $value;
  209. }
  210. if(!$oneData[0]||!$oneData[1] ||!$oneData[2]){
  211. $msg = "第".$row."行,数据存在空值,未导入";
  212. echo "<font color=\"red\">".$msg."</font><br />";
  213. $error++;
  214. $errorMsg[] = $msg;
  215. continue;
  216. }
  217. if($oneData[4]){
  218. if(!in_array($oneData[4],['业主','租户'])){
  219. $msg = "第".$row."行,类型错误,未导入";
  220. echo "<font color=\"red\">".$msg."</font><br />";
  221. $error++;
  222. $errorMsg[] = $msg;
  223. continue;
  224. }
  225. $type = $oneData[4]=='业主'?1:2;
  226. }else{
  227. $type = 1;
  228. }
  229. $check = Db::name('owner')
  230. ->where('card',$oneData[2])
  231. ->where('del',0)
  232. ->where('org_id',$orgid)
  233. ->find();
  234. if($check){
  235. $msg = "第".$row."行,姓名<".$oneData[0].">已存在,未导入";
  236. echo "<font color=\"red\">".$msg."</font><br />";
  237. $error++;
  238. $errorMsg[] = $msg;
  239. continue;
  240. }
  241. if($oneData[3]){
  242. if(!is_numeric($oneData[3])){
  243. $msg = "第".$row."行,预存款字段数据格式错误(只能为纯数字),未导入";
  244. echo "<font color=\"red\">".$msg."</font><br />";
  245. $error++;
  246. $errorMsg[] = $msg;
  247. continue;
  248. }
  249. $money = $oneData[3];
  250. }else{
  251. $money = '0.00';
  252. }
  253. $data = [
  254. 'org_id'=>$orgid,
  255. 'name'=>$oneData[0],
  256. 'phone'=>$oneData[1],
  257. 'card'=>$oneData[2],
  258. 'money'=>$money,
  259. 'type'=>$type,
  260. 'create_time'=>getTime(),
  261. 'import_log_id'=>$importId
  262. ];
  263. $Id = Db::name('owner')->insertGetId($data);
  264. if(!$Id){
  265. $msg = "第".$row."行,导入失败";
  266. echo "<font color=\"red\">".$msg."</font><br />";
  267. $error++;
  268. $errorMsg[] = $msg;
  269. continue;
  270. }
  271. $success++;
  272. }
  273. Db::name('import_log')->where('id',$importId)
  274. ->update(
  275. [
  276. 'success'=>$success,
  277. 'error'=>$error,
  278. 'error_msg'=>json_encode($errorMsg),
  279. ]
  280. );
  281. }
  282. echo "<font color=\"green\">导入完成</font><br />";
  283. }catch (Exception $e){
  284. echo $e->getMessage();
  285. echo "<font color=\"red\">数据异常,已停止导入</font><br />";
  286. }
  287. }
  288. }