function uploadScore($exam_paper) { $this->exam->id = $exam_paper; if ($this->input->post('submit')) { try { if ($_FILES['score_table']['error'] > 0) { throw new Exception('文件上错错误:错误代码: ' . $_FILES['score_table']['error']); } if (!(preg_match('/\\.(\\w*?)$/', $_FILES['score']['name'], $extname_match) && $extname_match[1] == 'xls')) { throw new Exception('文件格式错误,请上传xls格式的excel表格'); } require APPPATH . 'third_party/PHP-ExcelReader/reader.php'; $data = new Spreadsheet_Excel_Reader(); $data->setOutputEncoding('utf-8'); $data->setRowColOffset(0); $data->read($_FILES['score']['tmp_name']); $this->exam->uploadScore($data); } catch (Exception $e) { $this->output->message($e->getMessage(), 'warning'); } } $this->load->view('exam/uploadscore'); }
function InsertData() { if (!empty($_FILES['attach']['name'])) { $data = new Spreadsheet_Excel_Reader(); $data->setOutputEncoding('utf-8'); $data->setRowColOffset(0); $data->read($_FILES["attach"]["tmp_name"]); } $log_obj = new manage_group_pay_get_log(); $FileType = $_POST["PayType"]; $PayYear = $_POST["pay_year"]; $PayMonth = $_POST["pay_month"]; $SID = $_POST["sid"]; $success_count = 0; $unsuccess_count = 0; //.......ماموریت....................................................... if ($FileType == 8) { if (empty($_FILES['attach']['name'])) { $SDate = $PayYear . "/" . $PayMonth . "/01"; if ($PayMonth < 7) { $endDay = "31"; } elseif ($PayMonth > 6 && $PayMonth < 12) { $endDay = "30 "; } elseif ($PayMonth == 12) { $endDay = "29"; } $EDate = $PayYear . "/" . $PayMonth . "/" . $endDay; $pdo = PdoDataAccess::getPdoObject(); $pdo->beginTransaction(); $query = "\tSELECT staff_id , duration , region_coef , salary_item_type_id , mli.list_id , mli.list_row_no \n\n\t\t\t\t\t\t\tFROM pay_get_lists pgl inner join mission_list_items mli\n\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\ton pgl.list_id = mli.list_id\n\n\t\t\t\t\t\t\t\t\t\t\t\twhere list_type = 9 and pgl.list_date >= '" . DateModules::shamsi_to_miladi($SDate) . "' and\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tpgl.list_date <= j2g({$PayYear},{$PayMonth},{$endDay}) and doc_state = 3 "; //".DateModules::shamsi_to_miladi($EDate) ." $res = PdoDataAccess::runquery($query); for ($i = 0; $i < count($res); $i++) { $PaymentObj = new manage_payments(); $PayItmObj = new manage_payment_items(); $query = " select staff_id , bank_id , account_no , last_cost_center_id , person_type \n\t\t\t\t\t\t from hrmstotal.staff where staff_id =" . $res[$i]['staff_id']; $resStf = PdoDataAccess::runquery($query); if (!isset($resStf[0]['bank_id']) || !($resStf[0]['bank_id'] > 0)) { $log_obj->make_unsuccess_rows($res[$i]['staff_id'], "-", "بانک فرد جهت پرداخت مشخص نمی باشد."); $unsuccess_count++; continue; } if (!($resStf[0]['account_no'] > 0)) { $log_obj->make_unsuccess_rows($res[$i]['staff_id'], "-", "شماره حساب فرد جهت پرداخت مشخص نمی باشد."); $unsuccess_count++; continue; } if (!($resStf[0]['last_cost_center_id'] > 0)) { $log_obj->make_unsuccess_rows($res[$i]['staff_id'], "-", "آخرین مرکز هزینه فرد مشخص نشده است."); $unsuccess_count++; continue; } //......... محاسبه ماموریت............................ $coef = !empty($res[$i]['region_coef']) ? $res[$i]['region_coef'] : 0; $param1 = 0; $missionValue = manage_payment_calculation::calculate_mission($res[$i]['staff_id'], $PayYear, $PayMonth, $res[$i]['duration'], $coef, $param1); /*if($res[$i]['staff_id'] == 882660 ) { echo "----".$missionValue ; die() ; }*/ //.................................................... $PaymentObj->staff_id = $res[$i]['staff_id']; $PaymentObj->pay_year = $PayYear; $PaymentObj->pay_month = $PayMonth; $PaymentObj->payment_type = $FileType; $PaymentObj->bank_id = $resStf[0]['bank_id']; $PaymentObj->account_no = $resStf[0]['account_no']; $PaymentObj->state = 2; unset($payRes); /*if( $resStf[0]['person_type'] == 1 || $resStf[0]['person_type'] == 2 || $resStf[0]['person_type'] == 3 ) $DB = "hrms."; else $DB = "hrms_sherkati."; */ $qry = " select count(*) cn \n\t\t\t\t\t\t\t\t\tfrom payments \n\t\t\t\t\t\t\t\t\t\t\twhere pay_year = " . $PayYear . " and pay_month = " . $PayMonth . " and staff_id = " . $PaymentObj->staff_id . " and payment_type = " . $FileType; $payRes = PdoDataAccess::runquery($qry); if ($payRes[0]['cn'] == 0) { if ($PaymentObj->Add($pdo) === false) { $log_obj->make_unsuccess_rows($res[$i]['staff_id'], "-", "خطا در ثبت فیش ماموریت"); $unsuccess_count++; continue; } if ($resStf[0]['person_type'] == 1) { $SID = 42; } if ($resStf[0]['person_type'] == 2) { $SID = 43; } if ($resStf[0]['person_type'] == 3) { $SID = 10315; } if ($resStf[0]['person_type'] == 5) { $SID = 643; } //$SID = $res[$i]['salary_item_type_id'] ; //............ مرکز هزینه ..................... $PayItmObj->pay_year = $PayYear; $PayItmObj->pay_month = $PayMonth; $PayItmObj->param1 = $param1; $PayItmObj->staff_id = $res[$i]['staff_id']; $PayItmObj->salary_item_type_id = $SID; $PayItmObj->pay_value = $missionValue; $PayItmObj->cost_center_id = $resStf[0]['last_cost_center_id']; $PayItmObj->payment_type = 8; if ($PayItmObj->Add() === false) { $log_obj->make_unsuccess_rows($res[$i]['staff_id'], "-", " عدم ثبت رکورد"); $unsuccess_count++; continue; } $qry = " update hrmstotal.mission_list_items set PayValue =" . $missionValue . " \n\t\t\t\t\t\t\t\t\t\twhere list_id= " . $res[$i]['list_id'] . " and list_row_no=" . $res[$i]['list_row_no']; //print_r(ExceptionHandler::PopAllExceptions()) ; //echo PdoDataAccess::GetLatestQueryString() .'----<br>'; if (PdoDataAccess::runquery($qry, array(), $pdo) === false) { $log_obj->make_unsuccess_rows($res[$i]['staff_id'], "-", "خطا در ثبت مبلغ در رکورد ماموریت"); $unsuccess_count++; continue; } } else { if ($payRes[0]['cn'] > 0) { $qry = " select pay_value \n\t\t\t\t\t\t\t\t\tfrom payment_items \n\t\t\t\t\t\t\t\t\t\twhere pay_year = " . $PayYear . " and pay_month = " . $PayMonth . " and staff_id = " . $PaymentObj->staff_id . " and payment_type = " . $FileType; $resItem = PdoDataAccess::runquery($qry); //................................................ if (count($resItem) > 0) { $PayItmObj->pay_year = $PayYear; $PayItmObj->pay_month = $PayMonth; $PayItmObj->param1 = $param1; $PayItmObj->staff_id = $PaymentObj->staff_id; $PayItmObj->pay_value = $missionValue + $resItem[0]['pay_value']; $PayItmObj->payment_type = 8; if ($PayItmObj->Edit($pdo) === false) { $log_obj->make_unsuccess_rows($PaymentObj->staff_id, "-", " خطای بروز رسانی "); $unsuccess_count++; continue; } $qry = " update hrmstotal.mission_list_items set PayValue =" . $missionValue . " \n\t\t\t\t\t\t\t\t\t\twhere list_id= " . $res[$i]['list_id'] . " and list_row_no=" . $res[$i]['list_row_no']; //PdoDataAccess::runquery($qry) ; // echo "***we*".PdoDataAccess::AffectedRows()."---"; //print_r(ExceptionHandler::PopAllExceptions()) ; echo PdoDataAccess::GetLatestQueryString() .'----<br>'; die() ; if (PdoDataAccess::runquery($qry, array(), $pdo) === false) { $log_obj->make_unsuccess_rows($res[$i]['staff_id'], "-", "خطا در ثبت مبلغ در رکورد ماموریت"); $unsuccess_count++; continue; } } } } } //End for $log_obj->finalize(); $st = preg_replace('/\\r\\n/', "", $log_obj->make_result("UploadPayFilesObj.expand();")); if ($unsuccess_count > 0) { $pdo->rollBack(); } else { //echo "************" ; die() ; $pdo->commit(); } echo "{success:true,data:'" . $st . "'}"; die; } else { die; $pdo = PdoDataAccess::getPdoObject(); $pdo->beginTransaction(); for ($i = 1; $i < $data->sheets[0]['numRows']; $i++) { $PaymentObj = new manage_payments(); $PayItmObj = new manage_payment_items(); if (!isset($data->sheets[0]['cells'][$i][0]) && !isset($data->sheets[0]['cells'][$i][1])) { break; } $query = " select staff_id , bank_id , account_no , last_cost_center_id , person_type \n\t\t\t\t\t\t from staff where staff_id =" . $data->sheets[0]['cells'][$i][0]; $resStf = PdoDataAccess::runquery($query); if (count($resStf) == 0) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", " شماره شناسایی معتبر نمی باشد."); $unsuccess_count++; continue; } if (!($resStf[0]['bank_id'] > 0)) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", "بانک فرد جهت پرداخت مشخص نمی باشد."); $unsuccess_count++; continue; } if (!($resStf[0]['account_no'] > 0)) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", "شماره حساب فرد جهت پرداخت مشخص نمی باشد."); $unsuccess_count++; continue; } if (!($resStf[0]['last_cost_center_id'] > 0)) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", "آخرین مرکز هزینه فرد مشخص نشده است."); $unsuccess_count++; continue; } //......... محاسبه ماموریت............................ $coef = !empty($data->sheets[0]['cells'][$i][2]) ? $data->sheets[0]['cells'][$i][2] : 0; $missionValue = manage_payment_calculation::calculate_mission($data->sheets[0]['cells'][$i][0], $PayYear, $PayMonth, $data->sheets[0]['cells'][$i][1], $coef); //.................................................... $PaymentObj->staff_id = $data->sheets[0]['cells'][$i][0]; $PaymentObj->pay_year = $PayYear; $PaymentObj->pay_month = $PayMonth; $PaymentObj->payment_type = $FileType; $PaymentObj->bank_id = $resStf[0]['bank_id']; $PaymentObj->account_no = $resStf[0]['account_no']; $PaymentObj->state = 2; $qry = " select count(*) cn \n\t\t\t\t\t\t\t\t\tfrom hrms.payments \n\t\t\t\t\t\t\t\t\t\t\twhere pay_year = " . $PayYear . " and pay_month = " . $PayMonth . " and staff_id = " . $PaymentObj->staff_id . " and payment_type = " . $FileType; $payRes = PdoDataAccess::runquery($qry); if ($payRes[0]['cn'] == 0) { if ($PaymentObj->Add() === false) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", " خطا "); $unsuccess_count++; continue; } if ($resStf[0]['person_type'] == 1) { $SID = 42; } if ($resStf[0]['person_type'] == 2) { $SID = 43; } //............ مرکز هزینه ..................... $PayItmObj->pay_year = $PayYear; $PayItmObj->pay_month = $PayMonth; $PayItmObj->staff_id = $data->sheets[0]['cells'][$i][0]; $PayItmObj->salary_item_type_id = $SID; $PayItmObj->pay_value = $missionValue; $PayItmObj->cost_center_id = $resStf[0]['last_cost_center_id']; $PayItmObj->payment_type = 8; if ($PayItmObj->Add() === false) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", " خطا "); $unsuccess_count++; continue; } } else { if (count($payRes) > 0) { $qry = " select pay_value \n\t\t\t\t\t\t\t\t\tfrom hrms.payment_items \n\t\t\t\t\t\t\t\t\t\twhere pay_year = " . $PayYear . " and pay_month = " . $PayMonth . " and staff_id = " . $PaymentObj->staff_id . " and payment_type = " . $FileType; $res = PdoDataAccess::runquery($qry); $PayItmObj->pay_year = $PayYear; $PayItmObj->pay_month = $PayMonth; $PayItmObj->staff_id = $data->sheets[0]['cells'][$i][0]; $PayItmObj->pay_value = $missionValue + $res[0]['pay_value']; $PayItmObj->payment_type = 8; if ($PayItmObj->Edit() === false) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", " خطای بروز رسانی "); $unsuccess_count++; continue; } } } } // End for $log_obj->finalize(); $st = preg_replace('/\\r\\n/', "", $log_obj->make_result("UploadPayFilesObj.expand();")); if ($unsuccess_count > 0) { $pdo->rollBack(); } else { $pdo->commit(); } echo "{success:true,data:'" . $st . "'}"; die; } } //...................... بن غیر نقدی شش ماهه ........................ if ($FileType == 4 || $FileType == 5) { $pdo = PdoDataAccess::getPdoObject(); $pdo->beginTransaction(); for ($i = 1; $i < $data->sheets[0]['numRows']; $i++) { $PaymentObj = new manage_payments(); $PayItmObj = new manage_payment_items(); if (!isset($data->sheets[0]['cells'][$i][0])) { break; } $query = " select staff_id , bank_id , account_no ,person_type , last_cost_center_id\n\t\t\t\t\t\t from staff where staff_id =" . $data->sheets[0]['cells'][$i][0]; $resStf = PdoDataAccess::runquery($query); if (count($resStf) == 0) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", " شماره شناسایی معتبر نمی باشد."); $unsuccess_count++; continue; } if (!($resStf[0]['bank_id'] > 0)) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", "بانک فرد جهت پرداخت مشخص نمی باشد."); $unsuccess_count++; continue; } if (!($resStf[0]['account_no'] > 0)) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", "شماره حساب فرد جهت پرداخت مشخص نمی باشد."); $unsuccess_count++; continue; } if (!($resStf[0]['last_cost_center_id'] > 0)) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", "مرکز هزینه برای فرد مشخص نشده است."); $unsuccess_count++; continue; } //......... محاسبه بن نقدی............................ $BonValue = !empty($data->sheets[0]['cells'][$i][1]) ? $data->sheets[0]['cells'][$i][1] : 0; $PaymentObj->staff_id = $data->sheets[0]['cells'][$i][0]; $PaymentObj->pay_year = $PayYear; $PaymentObj->pay_month = $PayMonth; $PaymentObj->payment_type = $FileType; $PaymentObj->bank_id = $resStf[0]['bank_id']; $PaymentObj->account_no = $resStf[0]['account_no']; $PaymentObj->state = 1; /*if($resStf[0]['person_type'] == 10) { $DB = "hrmr." ; } else {*/ $DB = "hrmstotal."; //} //..................................... $qry = " select count(*) cn \n\t\t\t\t\t\t\t\t\tfrom " . $DB . "payments \n\t\t\t\t\t\t\t\t\t\t\twhere pay_year = " . $PayYear . " and pay_month = " . $PayMonth . " and staff_id = " . $PaymentObj->staff_id . " and payment_type = " . $FileType; $payRes = PdoDataAccess::runquery($qry); if ($payRes[0]['cn'] == 0) { if ($PaymentObj->Add("", $DB) === false) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", " خطا 22"); $unsuccess_count++; continue; } //............ مرکز هزینه ..................... $PayItmObj->pay_year = $PayYear; $PayItmObj->pay_month = $PayMonth; $PayItmObj->staff_id = $data->sheets[0]['cells'][$i][0]; $PayItmObj->salary_item_type_id = 9941; $PayItmObj->pay_value = $BonValue; $PayItmObj->cost_center_id = $resStf[0]['last_cost_center_id']; $PayItmObj->payment_type = $FileType; if ($PayItmObj->Add("", $DB) === false) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", " خطا 444"); $unsuccess_count++; continue; } } else { if (count($payRes) > 0) { $qry = " select pay_value \n\t\t\t\t\t\t\t\t\tfrom " . $DB . "payment_items \n\t\t\t\t\t\t\t\t\t\twhere pay_year = " . $PayYear . " and pay_month = " . $PayMonth . " and staff_id = " . $PaymentObj->staff_id . " and payment_type = " . $FileType; $res = PdoDataAccess::runquery($qry); $PayItmObj->pay_year = $PayYear; $PayItmObj->pay_month = $PayMonth; $PayItmObj->staff_id = $data->sheets[0]['cells'][$i][0]; $PayItmObj->pay_value = $BonValue + $res[0]['pay_value']; $PayItmObj->payment_type = $FileType; if ($PayItmObj->Edit("", $DB) === false) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", " خطای بروز رسانی "); $unsuccess_count++; continue; } } } } // End for $log_obj->finalize(); $st = preg_replace('/\\r\\n/', "", $log_obj->make_result("UploadPayFilesObj.expand();")); if ($unsuccess_count > 0) { $pdo->rollBack(); } else { $pdo->commit(); } echo "{success:true,data:'" . $st . "'}"; die; } // محاسبه پرداخت تالیف و ویراستاری............................. if ($FileType == 12) { $pdo = PdoDataAccess::getPdoObject(); $pdo->beginTransaction(); for ($i = 1; $i < $data->sheets[0]['numRows']; $i++) { $PaymentObj = new manage_payments(); $PayItmObj = new manage_payment_items(); unset($TaxRes); if (!isset($data->sheets[0]['cells'][$i][0])) { break; } $query = " select staff_id , bank_id , account_no ,person_type ,last_cost_center_id\n\t\t\t\t\t\t from staff where staff_id =" . $data->sheets[0]['cells'][$i][0]; $resStf = PdoDataAccess::runquery($query); if (count($resStf) == 0) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", " شماره شناسایی معتبر نمی باشد."); $unsuccess_count++; continue; } if (!($resStf[0]['bank_id'] > 0)) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", "بانک فرد جهت پرداخت مشخص نمی باشد."); $unsuccess_count++; continue; } if (!($resStf[0]['account_no'] > 0)) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", "شماره حساب فرد جهت پرداخت مشخص نمی باشد."); $unsuccess_count++; continue; } if (!($resStf[0]['last_cost_center_id'] > 0)) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", "آخرین مرکز هزینه فرد مشخص نشده است."); $unsuccess_count++; continue; } //.........محاسبه تالیف و ویراستاری........................... $TValue = !empty($data->sheets[0]['cells'][$i][1]) ? $data->sheets[0]['cells'][$i][1] : 0; $PaymentObj->staff_id = $data->sheets[0]['cells'][$i][0]; $PaymentObj->pay_year = $PayYear; $PaymentObj->pay_month = $PayMonth; $PaymentObj->payment_type = $FileType; $PaymentObj->bank_id = $resStf[0]['bank_id']; $PaymentObj->account_no = $resStf[0]['account_no']; $PaymentObj->message = !empty($data->sheets[0]['cells'][$i][3]) ? $data->sheets[0]['cells'][$i][3] : 0; $PaymentObj->state = 1; //..................................... $qry = " select count(*) cn \n\t\t\t\t\t\t\t\t\tfrom payments \n\t\t\t\t\t\t\t\t\t\t\twhere pay_year = " . $PayYear . " and pay_month = " . $PayMonth . " and \n\t\t\t\t\t\t\t\t\t\t\t\t staff_id = " . $PaymentObj->staff_id . " and payment_type = " . $FileType; $payRes = PdoDataAccess::runquery($qry); if ($payRes[0]['cn'] == 0) { if ($PaymentObj->Add() === false) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", " خطا 22"); $unsuccess_count++; continue; } //............ مرکز هزینه ..................... $PayItmObj->pay_year = $PayYear; $PayItmObj->pay_month = $PayMonth; $PayItmObj->staff_id = $data->sheets[0]['cells'][$i][0]; $PayItmObj->salary_item_type_id = 10389; $PayItmObj->pay_value = $TValue; $PayItmObj->get_value = 0; $PayItmObj->cost_center_id = $resStf[0]['last_cost_center_id']; $PayItmObj->payment_type = 12; if ($PayItmObj->Add() === false) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", " خطا 444"); $unsuccess_count++; continue; } //.......................................... محاسبه مالیات ........................................ if ($resStf[0]['person_type'] == 1 || $resStf[0]['person_type'] == 10) { $TaxKey = 146; } elseif ($resStf[0]['person_type'] == 2) { $TaxKey = 147; } elseif ($resStf[0]['person_type'] == 3) { $TaxKey = 148; } elseif ($resStf[0]['person_type'] == 5) { $TaxKey = 747; } if ($resStf[0]['person_type'] == 10) { $TaxResVal = $TValue / 10; } else { $TaxRes = process_tax($data->sheets[0]['cells'][$i][0], $TValue, $PayYear, $PayMonth); } $PayItmObj->pay_year = $PayYear; $PayItmObj->pay_month = $PayMonth; $PayItmObj->staff_id = $data->sheets[0]['cells'][$i][0]; $PayItmObj->salary_item_type_id = $TaxKey; $PayItmObj->get_value = $resStf[0]['person_type'] == 10 ? $TaxResVal : $TaxRes['get_value']; $PayItmObj->pay_value = 0; $PayItmObj->cost_center_id = $resStf[0]['last_cost_center_id']; $PayItmObj->payment_type = 12; $PayItmObj->param1 = $resStf[0]['person_type'] == 10 ? 0 : $TaxRes['param1']; $PayItmObj->param3 = $resStf[0]['person_type'] == 10 ? 0 : $TaxRes['param3']; $PayItmObj->param4 = 1; $PayItmObj->param5 = $resStf[0]['person_type'] == 10 ? 0 : $TaxRes['param5']; if ($PayItmObj->Add() === false) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", " خطا 444"); $unsuccess_count++; continue; } //...................افزودن مبلغ علی الحساب..................... if (!empty($data->sheets[0]['cells'][$i][2]) && $data->sheets[0]['cells'][$i][2] > 0) { $PayItmObj->pay_year = $PayYear; $PayItmObj->pay_month = $PayMonth; $PayItmObj->staff_id = $data->sheets[0]['cells'][$i][0]; $PayItmObj->salary_item_type_id = 4600; $PayItmObj->get_value = $data->sheets[0]['cells'][$i][2]; $PayItmObj->pay_value = 0; $PayItmObj->cost_center_id = $resStf[0]['last_cost_center_id']; $PayItmObj->payment_type = 12; $PayItmObj->param1 = 0; $PayItmObj->param2 = 0; $PayItmObj->param3 = 0; $PayItmObj->param4 = 2; $PayItmObj->param5 = 0; if ($PayItmObj->Add() === false) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", " خطا 444"); $unsuccess_count++; continue; } } } } // End for $log_obj->finalize(); $st = preg_replace('/\\r\\n/', "", $log_obj->make_result("UploadPayFilesObj.expand();")); if ($unsuccess_count > 0) { $pdo->rollBack(); } else { $pdo->commit(); } echo "{success:true,data:'" . $st . "'}"; die; } //......... محاسبه حق التدریس.................................. if ($FileType == 14) { $pdo = PdoDataAccess::getPdoObject(); $pdo->beginTransaction(); for ($i = 1; $i < $data->sheets[0]['numRows']; $i++) { $PaymentObj = new manage_payments(); $PayItmObj = new manage_payment_items(); if (!isset($data->sheets[0]['cells'][$i][0])) { break; } $query = " select staff_id , bank_id , account_no ,person_type ,last_cost_center_id\n\t\t\t\t\t\t from staff where staff_id =" . $data->sheets[0]['cells'][$i][0] . " AND person_type in (1,10) "; $resStf = PdoDataAccess::runquery($query); if (count($resStf) == 0) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", " شماره شناسایی معتبر نمی باشد."); $unsuccess_count++; continue; } if (!($resStf[0]['bank_id'] > 0)) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", "بانک فرد جهت پرداخت مشخص نمی باشد."); $unsuccess_count++; continue; } if (!($resStf[0]['account_no'] > 0)) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", "شماره حساب فرد جهت پرداخت مشخص نمی باشد."); $unsuccess_count++; continue; } if (!($resStf[0]['last_cost_center_id'] > 0)) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", "آخرین مرکز هزینه فرد مشخص نشده است."); $unsuccess_count++; continue; } //............................. محاسبه مبلغ حق التدریس................ $TValue = !empty($data->sheets[0]['cells'][$i][1]) ? $data->sheets[0]['cells'][$i][1] : 0; $PaymentObj->staff_id = $data->sheets[0]['cells'][$i][0]; $PaymentObj->pay_year = $PayYear; $PaymentObj->pay_month = $PayMonth; $PaymentObj->payment_type = $FileType; $PaymentObj->writ_id = !empty($data->sheets[0]['cells'][$i][2]) ? $data->sheets[0]['cells'][$i][2] : 0; $PaymentObj->writ_ver = !empty($data->sheets[0]['cells'][$i][3]) ? $data->sheets[0]['cells'][$i][3] : 0; $PaymentObj->bank_id = $resStf[0]['bank_id']; $PaymentObj->account_no = $resStf[0]['account_no']; $PaymentObj->message = !empty($data->sheets[0]['cells'][$i][5]) ? $data->sheets[0]['cells'][$i][5] : 0; $PaymentObj->state = 1; //..................................... $qry = " select count(*) cn \n\t\t\t\t\t\t\t\t\tfrom payments \n\t\t\t\t\t\t\t\t\t\t\twhere pay_year = " . $PayYear . " and pay_month = " . $PayMonth . " and \n\t\t\t\t\t\t\t\t\t\t\t\t staff_id = " . $PaymentObj->staff_id . " and payment_type = " . $FileType; $payRes = PdoDataAccess::runquery($qry); if ($payRes[0]['cn'] == 0) { if ($PaymentObj->Add() === false) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", " خطا 22"); $unsuccess_count++; continue; } //............ مرکز هزینه ..................... $PayItmObj->pay_year = $PayYear; $PayItmObj->pay_month = $PayMonth; $PayItmObj->staff_id = $data->sheets[0]['cells'][$i][0]; $PayItmObj->salary_item_type_id = 40; // کد قلم مربوط به حق التدریس $PayItmObj->pay_value = $TValue; $PayItmObj->cost_center_id = $resStf[0]['last_cost_center_id']; $PayItmObj->payment_type = 14; if ($PayItmObj->Add() === false) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", " خطا 444"); $unsuccess_count++; continue; } //.......................................... محاسبه مالیات ........................................ if ($resStf[0]['person_type'] == 1) { $TaxKey = 146; } elseif ($resStf[0]['person_type'] == 2) { $TaxKey = 147; } elseif ($resStf[0]['person_type'] == 3) { $TaxKey = 148; } elseif ($resStf[0]['person_type'] == 5) { $TaxKey = 747; } elseif ($resStf[0]['person_type'] == 10) { $TaxKey = 146; } //.............. تعدیل مالیات با توجه به بازه مرتبط با ترم ................................ // $TaxRes = process_tax_normalize($data->sheets[0]['cells'][$i][0] , $TValue ,$PayYear ,$PayMonth ) ; /*اين فرد مشمول ماليات نمي باشد*/ $SDate = $PayYear . "/" . $PayMonth . "/01"; if ($PayMonth < 7) { $endDay = "31"; } elseif ($PayMonth > 6 && $PayMonth < 12) { $endDay = "30 "; } elseif ($PayMonth == 12) { $endDay = "29"; } $EDate = $PayYear . "/" . $PayMonth . "/" . $endDay; $EDate = DateModules::shamsi_to_miladi($EDate); $SDate = DateModules::shamsi_to_miladi($SDate); $staffID = $data->sheets[0]['cells'][$i][0]; $qry = " select tax_include\n\t\t\t\t\t\t\t\t\tfrom staff_include_history\n\t\t\t\t\t\t\t\t\t\twhere staff_id = " . $staffID . " and start_date <= '" . $EDate . "' AND\n\t\t\t\t\t\t\t\t\t\t\t (end_date IS NULL OR end_date = '0000-00-00' OR\n\t\t\t\t\t\t\t\t\t\t\t end_date >= '" . $EDate . "' OR end_date > '" . $SDate . "' ) "; $res = PdoDataAccess::runquery($qry); if ($res[0]['tax_include'] == 0) { $TaxRes = 0; } else { $TaxRes = $TValue * 10 / 100; } $PayItmObj->pay_year = $PayYear; $PayItmObj->pay_month = $PayMonth; $PayItmObj->staff_id = $data->sheets[0]['cells'][$i][0]; $PayItmObj->salary_item_type_id = $TaxKey; $PayItmObj->get_value = $TaxRes; //$TaxRes['get_value'] ; $PayItmObj->pay_value = 0; $PayItmObj->cost_center_id = $resStf[0]['last_cost_center_id']; $PayItmObj->payment_type = 14; $PayItmObj->param1 = $TValue; //$TaxRes['param1'] ; $PayItmObj->param2 = 0; //$TaxRes['param2'] ; $PayItmObj->param3 = 0; //$TaxRes['param3'] ; $PayItmObj->param4 = 2; $PayItmObj->param5 = 0; //$TaxRes['param5'] ; if ($TaxRes > 0) { if ($PayItmObj->Add() === false) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", " خطا 444"); $unsuccess_count++; continue; } } //...................افزودن مبلغ علی الحساب..................... if (!empty($data->sheets[0]['cells'][$i][4]) && $data->sheets[0]['cells'][$i][4] > 0) { $PayItmObj->pay_year = $PayYear; $PayItmObj->pay_month = $PayMonth; $PayItmObj->staff_id = $data->sheets[0]['cells'][$i][0]; $PayItmObj->salary_item_type_id = 4600; $PayItmObj->get_value = $data->sheets[0]['cells'][$i][4]; $PayItmObj->pay_value = 0; $PayItmObj->cost_center_id = $resStf[0]['last_cost_center_id']; $PayItmObj->payment_type = 14; $PayItmObj->param1 = 0; $PayItmObj->param2 = 0; $PayItmObj->param3 = 0; $PayItmObj->param4 = 2; $PayItmObj->param5 = 0; if ($PayItmObj->Add() === false) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", " خطا 444"); $unsuccess_count++; continue; } } } } // End for $log_obj->finalize(); $st = preg_replace('/\\r\\n/', "", $log_obj->make_result("UploadPayFilesObj.expand();")); if ($unsuccess_count > 0) { $pdo->rollBack(); } else { $pdo->commit(); } echo "{success:true,data:'" . $st . "'}"; die; } //.........محاسبه عیدی و پاداش.......................................... if ($FileType == 2) { $pdo = PdoDataAccess::getPdoObject(); $pdo->beginTransaction(); //.........................محاسبه کارکرد سالانه................... $year_fdate = DateModules::shamsi_to_miladi($PayYear . "/01/01"); $year_edate = DateModules::shamsi_to_miladi($PayYear + 1 . "/01/01"); $year_fdate = str_replace("/", "-", $year_fdate); $year_edate = str_replace("/", "-", $year_edate); PdoDataAccess::runquery('DROP TABLE IF EXISTS temp_work_writs;'); PdoDataAccess::runquery(' CREATE TABLE temp_work_writs AS SELECT w.staff_id, CASE WHEN w.emp_mode IN (3,8,9,15,7,16,11,12,14,20,22,25,27,28,29) THEN 0 WHEN w.emp_mode IN ( ' . EMP_MODE_LEAVE_WITH_SALARY . ' ) THEN 1 ELSE (CASE w.annual_effect WHEN 1 THEN 1 WHEN 2 THEN 0.5 WHEN 3 THEN 0 WHEN 4 THEN 2 END) END annual_coef, CASE WHEN w.execute_date < \'' . $year_fdate . '\' THEN \'' . $year_fdate . '\' ELSE w.execute_date END execute_date, CASE WHEN ( SELECT MIN(w2.execute_date) execute_date FROM writs w2 WHERE w2.execute_date <= \'' . $year_edate . '\' AND w2.staff_id = w.staff_id AND w2.history_only = 0 AND w2.state = ' . WRIT_SALARY . ' AND (w2.execute_date > w.execute_date OR (w2.execute_date = w.execute_date AND w2.writ_id > w.writ_id) OR (w2.execute_date = w.execute_date AND w2.writ_id = w.writ_id AND w2.writ_ver > w.writ_ver)) GROUP BY staff_id) IS NULL THEN \'' . $year_edate . '\' ELSE ( SELECT MIN(w2.execute_date) execute_date FROM writs w2 WHERE w2.execute_date <= \'' . $year_edate . '\' AND w2.staff_id = w.staff_id AND w2.history_only = 0 AND w2.state = ' . WRIT_SALARY . ' AND (w2.execute_date > w.execute_date OR (w2.execute_date = w.execute_date AND w2.writ_id > w.writ_id) OR (w2.execute_date = w.execute_date AND w2.writ_id = w.writ_id AND w2.writ_ver > w.writ_ver)) GROUP BY staff_id) END end_date, w.person_type FROM writs w WHERE w.history_only = 0 AND w.state = ' . WRIT_SALARY . ' AND ( \'' . $year_edate . '\' >= w.execute_date OR w.execute_date IS NULL OR w.execute_date = \'0000-00-00\') '); PdoDataAccess::runquery('ALTER TABLE temp_work_writs ADD INDEX(staff_id)'); PdoDataAccess::runquery('DROP TABLE IF EXISTS temp_last_salary_writs;'); PdoDataAccess::runquery('CREATE TABLE temp_last_salary_writs AS SELECT w.staff_id, SUBSTRING_INDEX(SUBSTRING( MAX( CONCAT(w.execute_date,w.writ_id,\'.\',w.writ_ver) ),11) ,\'.\',1) writ_id, SUBSTRING_INDEX(MAX( CONCAT(w.execute_date,w.writ_id,\'.\',w.writ_ver) ) ,\'.\',-1) writ_ver FROM writs w WHERE w.state = ' . WRIT_SALARY . ' AND w.history_only = 0 AND if(w.person_type = 3 , w.emp_mode not in ( 3,8,9,15,7,16,11,12,14,20,22) , (1=1)) GROUP BY w.staff_id;'); PdoDataAccess::runquery('ALTER TABLE temp_last_salary_writs ADD INDEX(staff_id,writ_id,writ_ver);'); PdoDataAccess::runquery("SET NAMES 'utf8'"); $WritWrk_DT = PdoDataAccess::runquery_fetchMode(' SELECT w.staff_id, p.plname, p.pfname, w.person_type, w.cost_center_id, tlw.writ_id last_writ_id, tlw.writ_ver last_writ_ver, s.bank_id, s.account_no, si.tax_include, pay.staff_id as before_calced, ( SELECT tax_table_type_id FROM staff_tax_history sth WHERE sth.staff_id = w.staff_id ORDER BY start_date DESC LIMIT 1 ) as tax_table_type_id, ( SELECT SUM(wsi.value) FROM writ_salary_items wsi WHERE wsi.writ_id = w.writ_id AND wsi.writ_ver = w.writ_ver AND wsi.salary_item_type_id IN(' . SIT_WORKER_BASE_SALARY . ',' . SIT_WORKER_ANNUAL_INC . ') AND w.person_type = ' . HR_WORKER . ' AND w.state = ' . WRIT_SALARY . ' ) as worker_base_salary, SUM(DATEDIFF(tw.end_date,tw.execute_date) * tw.annual_coef) work_time FROM temp_work_writs tw INNER JOIN staff s ON(tw.staff_id = s.staff_id) INNER JOIN staff_include_history si ON(s.staff_id = si.staff_id AND si.start_date <= \'' . $year_edate . '\' AND (si.end_date IS NULL OR si.end_date = \'0000-00-00\' OR si.end_date >= \'' . $year_edate . '\') ) INNER JOIN persons p ON(s.PersonID = p.PersonID) INNER JOIN temp_last_salary_writs tlw ON(s.staff_id = tlw.staff_id) INNER JOIN writs w ON(tlw.staff_id = w.staff_id AND tlw.writ_id = w.writ_id AND tlw.writ_ver = w.writ_ver AND (w.person_type = ' . HR_WORKER . ' OR w.emp_mode <> ' . EMP_MODE_RETIRE . ') ) LEFT OUTER JOIN payments pay ON(pay.pay_year = ' . $PayYear . ' AND pay.pay_month=12 AND pay.payment_type= ' . HANDSEL_PAYMENT . ' AND pay.staff_id = s.staff_id) WHERE s.staff_id not in (1085919 , 25 , 29 , 1086493 , 1085766 , 1086203 , 1086272 , 1085025 ,2003012 ,1081975) AND tw.end_date > \'' . $year_fdate . '\' GROUP BY w.staff_id, p.plname, p.pfname, w.person_type, w.cost_center_id, tlw.writ_id, tlw.writ_ver, s.bank_id, s.account_no, s.tafsili_id, pay.staff_id having work_time > 0 '); // echo PdoDataAccess::GetLatestQueryString(); die(); $count = $WritWrk_DT->rowCount(); //$WritWrkRes //..................................................................... if (DateModules::YearIsLeap($PayYear)) { $Month12Leng = 30; } else { $Month12Leng = 29; } //..............................انتقال پارامترهای حقوقی به یک آرایه ............. exe_param_sql($PayYear, 12, $salaryParam); //........................................انتقال داده های جداول مالیاتی به یک آرایه ...................................... exe_taxtable_sql($PayYear, 12, $taxTable); //...................................................................... for ($i = 0; $i < $count; $i++) { $WritWrkRes = $WritWrk_DT->fetch(); $PaymentObj = new manage_payments(); $PayItmObj = new manage_payment_items(); //................... اعمال کنترل.................................. if ($WritWrkRes['cost_center_id'] == NULL) { $log_obj->make_unsuccess_rows($WritWrkRes['staff_id'], "-", "براي اين شخص مرکز هزينه مشخص نشده است."); $unsuccess_count++; continue; } if ($WritWrkRes['tax_table_type_id'] == NULL) { $log_obj->make_unsuccess_rows($WritWrkRes['staff_id'], "-", "براي اين شخص جدول مالياتي مشخص نشده است."); $unsuccess_count++; continue; } if ($WritWrkRes['before_calced'] > 0) { $log_obj->make_unsuccess_rows($WritWrkRes['staff_id'], "-", "محاسبه عيدي و پاداش اين شخص قبلا انجام شده است."); $unsuccess_count++; continue; } if (empty($taxTable[$WritWrkRes['tax_table_type_id']])) { $log_obj->make_unsuccess_rows($WritWrkRes['staff_id'], "-", " جدول مالیاتی برای فرد ناقص تعریف شده است."); $unsuccess_count++; continue; } if ($WritWrkRes['work_time'] == 0) { continue; } //..................... if ($WritWrkRes['staff_id'] == 2002664) { $WritWrkRes['work_time'] = 365; } if ($WritWrkRes['staff_id'] == 2002612) { $WritWrkRes['work_time'] = 365; } if ($WritWrkRes['staff_id'] == 2002561) { $WritWrkRes['work_time'] = 365; } if ($WritWrkRes['staff_id'] == 2003053 || $WritWrkRes['staff_id'] == 2002691) { $WritWrkRes['work_time'] = 365; } //.......................... //................................محاسبه عیدی و پاداش با توجه به کارکرد فرد در طول سال................................ $value = 0; if (DateModules::YearIsLeap($PayYear)) { $year_length = 366; } else { $year_length = 365; } if ($WritWrkRes['person_type'] == HR_WORKER) { if ($WritWrkRes['worker_base_salary'] * 2 > $salaryParam[SPT_JOB_SALARY][1] * 30 * 3) { $value = $salaryParam[SPT_JOB_SALARY][1] * 30 * 3; } else { $value = $WritWrkRes['worker_base_salary'] * 2; } $value *= $WritWrkRes['work_time'] / $year_length; $param2 = $salaryParam[SPT_JOB_SALARY][1]; } else { $value = $WritWrkRes['work_time'] / $year_length * $salaryParam[SPT_HANDSEL_VALUE][0]; $param2 = $salaryParam[SPT_HANDSEL_VALUE][0]; } if ($WritWrkRes['person_type'] == HR_EMPLOYEE) { $key = 164; } elseif ($WritWrkRes['person_type'] == HR_PROFESSOR) { $key = 163; } elseif ($WritWrkRes['person_type'] == HR_WORKER) { $key = 165; } elseif ($WritWrkRes['person_type'] == HR_CONTRACT) { $key = 764; } $PaymentObj->staff_id = $WritWrkRes['staff_id']; $PaymentObj->pay_year = $PayYear; $PaymentObj->pay_month = 12; $PaymentObj->payment_type = $FileType; $PaymentObj->writ_id = $WritWrkRes['last_writ_id']; $PaymentObj->writ_ver = $WritWrkRes['last_writ_ver']; $PaymentObj->bank_id = $WritWrkRes['bank_id']; $PaymentObj->account_no = $WritWrkRes['account_no']; $PaymentObj->state = 1; if ($PaymentObj->Add() === false) { $log_obj->make_unsuccess_rows($WritWrkRes['staff_id'], "-", " خطا در ثبت جدول پرداخت"); $unsuccess_count++; continue; } $PayItmObj->pay_year = $PayYear; $PayItmObj->pay_month = 12; $PayItmObj->staff_id = $WritWrkRes['staff_id']; $PayItmObj->salary_item_type_id = $key; $PayItmObj->pay_value = $value; $PayItmObj->get_value = 0; $PayItmObj->param1 = $WritWrkRes['work_time']; $PayItmObj->param2 = $param2; $PayItmObj->cost_center_id = $WritWrkRes['cost_center_id']; $PayItmObj->payment_type = HANDSEL_PAYMENT; if ($PayItmObj->Add() === false) { $log_obj->make_unsuccess_rows($WritWrkRes['staff_id'], "-", "خطا در ثبت اقلام پرداختی"); $unsuccess_count++; continue; } //..........................محاسبه مالیات مربوط به عیدی و پاداش ................... if (empty($WritWrkRes['tax_include'])) { continue; } $handsel_key = $key; if ($WritWrkRes['person_type'] == 1) { $TaxKey = 146; } elseif ($WritWrkRes['person_type'] == 2) { $TaxKey = 147; } elseif ($WritWrkRes['person_type'] == 3) { $TaxKey = 148; } elseif ($WritWrkRes['person_type'] == 5) { $TaxKey = 747; } $handsel_value = $value; $tax = 0; //متغيري جهت نگهداري ماليات reset($taxTable); foreach ($taxTable[$WritWrkRes['tax_table_type_id']] as $tax_table_row) { $pay_mid_month_date = DateModules::shamsi_to_miladi($PayYear . "/12/15"); if (DateModules::CompareDate($pay_mid_month_date, $tax_table_row['from_date']) != -1 && DateModules::CompareDate($pay_mid_month_date, $tax_table_row['to_date']) != 1) { if ($handsel_value >= $tax_table_row['from_value'] && $handsel_value <= $tax_table_row['to_value']) { $tax += ($handsel_value - $tax_table_row['from_value']) * $tax_table_row['coeficient']; } else { if ($handsel_value > $tax_table_row['to_value']) { $tax += ($tax_table_row['to_value'] - $tax_table_row['from_value']) * $tax_table_row['coeficient']; } } } } if ($tax > 0) { $PayItmObj->pay_year = $PayYear; $PayItmObj->pay_month = 12; $PayItmObj->staff_id = $WritWrkRes['staff_id']; $PayItmObj->salary_item_type_id = $TaxKey; $PayItmObj->pay_value = 0; $PayItmObj->get_value = $tax; $PayItmObj->param1 = $handsel_value; $PayItmObj->param2 = $WritWrkRes['tax_table_type_id']; $PayItmObj->cost_center_id = $WritWrkRes['cost_center_id']; $PayItmObj->payment_type = HANDSEL_PAYMENT; if ($PayItmObj->Add() === false) { $log_obj->make_unsuccess_rows($WritWrkRes['staff_id'], "-", "خطا در ثبت اقلام پرداختی"); $unsuccess_count++; continue; } } } //End For $log_obj->finalize(); $st = preg_replace('/\\r\\n/', "", $log_obj->make_result("UploadPayFilesObj.expand();")); if ($unsuccess_count > 0) { $pdo->rollBack(); } else { $pdo->commit(); } echo "{success:true,data:'" . $st . "'}"; die; } if ($FileType == 7) { //echo "*ererer**" ; die(); $pdo = PdoDataAccess::getPdoObject(); $pdo->beginTransaction(); for ($i = 1; $i < $data->sheets[0]['numRows']; $i++) { $query = " select staff_id , PersonID\n\t\t\t\t\t\t\t\t\t\t\tfrom staff where staff_id =" . $data->sheets[0]['cells'][$i][0]; $resStf = PdoDataAccess::runquery($query); //echo PdoDataAccess::GetLatestQueryString() ; die(); if (count($resStf) == 0) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", " شماره شناسایی معتبر نمی باشد."); $unsuccess_count++; continue; } $query = " insert into person_subtracts (PersonID , subtract_type , first_value , instalment,\n\t\t\t\t\t\t\t\t\t\t\t remainder , start_date , end_date , salary_item_type_id , \n\t\t\t\t\t\t\t\t\t\t\t reg_date ) values (" . $resStf[0]['PersonID'] . " , 3 , \n\t\t\t\t\t\t\t\t\t\t\t\t " . $data->sheets[0]['cells'][$i][1] . ",\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t " . $data->sheets[0]['cells'][$i][1] . ",\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t " . $data->sheets[0]['cells'][$i][1] . ",\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t '2015-02-20' ,'2015-03-20' , 9945 , '2015-03-15' ) "; //echo $query ; die(); PdoDataAccess::runquery($query); //echo PdoDataAccess::GetLatestQueryString() ; die(); } $log_obj->finalize(); $st = preg_replace('/\\r\\n/', "", $log_obj->make_result("UploadPayFilesObj.expand();")); if ($unsuccess_count > 0) { $pdo->rollBack(); } else { $pdo->commit(); } echo "{success:true,data:'" . $st . "'}"; die; } }
function ImportHolidaysFromExcel() { require_once inc_phpExcelReader; $data = new Spreadsheet_Excel_Reader(); $data->setOutputEncoding('utf-8'); $data->setRowColOffset(0); $data->read($_FILES["attach"]["tmp_name"]); for ($i = 0; $i < $data->sheets[0]['numRows']; $i++) { $row = $data->sheets[0]['cells'][$i]; $obj = new ATN_holidays(); $obj->TheDate = DateModules::shamsi_to_miladi($row[0]); $obj->details = $row[1]; $result = $obj->Add(); } echo Response::createObjectiveResponse($result, ""); die; }
function Equalization_UpdateChecks() { $BankID = $_POST["BankID"]; $result = ""; require_once "phpExcelReader.php"; $data = new Spreadsheet_Excel_Reader(); $data->setOutputEncoding('utf-8'); $data->setRowColOffset(0); $data->read($_FILES["attach"]["tmp_name"]); $pdo = PdoDataAccess::getPdoObject(); $pdo->beginTransaction(); //--------------- add to equalizations ------------- $EqualObj = new ACC_equalizations(); $EqualObj->RegDate = PDONOW; $EqualObj->BankID = $BankID; $EqualObj->ImportFile = fread(fopen($_FILES["attach"]["tmp_name"], 'r'), $_FILES["attach"]["size"]); $EqualObj->Add($pdo); //----------- insert DocHeader -------------------- $obj = new ACC_docs(); $obj->RegDate = PDONOW; $obj->regPersonID = $_SESSION['USER']["PersonID"]; $obj->DocDate = PDONOW; $obj->CycleID = $_SESSION["accounting"]["CycleID"]; $obj->BranchID = $_SESSION["accounting"]["BranchID"]; $obj->DocType = DOCTYPE_EQUALCHECKS; $obj->description = "مغایرت گیری بانکی / به روز رسانی چک ها "; if (!$obj->Add($pdo)) { ExceptionHandler::PushException("خطا در ایجاد سند"); return false; } $successCount = 0; for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) { $checkNo = ""; switch ($BankID) { case "4": // اقتصاد نوین $TafsiliID = ""; $TafsiliID2 = ""; if (empty($data->sheets[0]['cells'][$i][1])) { continue; } $cellData = $data->sheets[0]['cells'][$i][1]; if (isset($cellData) && strpos(trim($cellData), "چک عادي ش.") !== false) { $arr = preg_split("/\\//", $cellData); $checkNo = $arr[2]; $checkNo = substr($checkNo, 0, strpos($checkNo, "صندوق پژوهش")); } break; } if ($checkNo == "") { continue; } //---------------- add doc items -------------------- $dt = PdoDataAccess::runquery("\r\n\t\t\tSELECT * FROM ACC_IncomeCheques \r\n\t\t\t\twhere ChequeNo=? AND ChequeStatus<>" . INCOMECHEQUE_VOSUL, array($checkNo), $pdo); if (count($dt) > 0) { $inChequeObj = new ACC_IncomeCheques($dt[0]["IncomeChequeID"]); $inChequeObj->EqualizationID = $EqualObj->EqualizationID; $inChequeObj->ChequeStatus = INCOMECHEQUE_VOSUL; $inChequeObj->Edit($pdo); $temp = $inChequeObj->GetBackPays($pdo); foreach ($temp as $row) { $BackPayObj = new LON_BackPays($row["BackPayID"]); $BackPayObj->EqualizationID = $EqualObj->EqualizationID; $BackPayObj->Edit($pdo); } ACC_IncomeCheques::AddToHistory($inChequeObj->IncomeChequeID, $inChequeObj->ChequeStatus, $pdo); RegisterOuterCheque($obj->DocID, $inChequeObj, $pdo, COSTID_Bank, $TafsiliID, $TafsiliID2); $successCount++; $result .= "شماره چک : " . $checkNo . " به روز رسانی شد <br>"; } else { $result .= "<font color=red> شماره چک : " . $checkNo . " یافت نشد </font><br>"; } } if ($successCount == 0) { $pdo->rollBack(); } else { $pdo->commit(); } echo Response::createObjectiveResponse(true, $successCount == 0 ? "هیچ چکی به روز نگردید" : $result); die; }
public function importAction() { $result = array('success' => true, 'info' => '导入成功'); $request = $this->getRequest()->getParams(); $now = date('Y-m-d H:i:s'); if (isset($_FILES['csv'])) { $now = date('Y-m-d H:i:s'); $user_session = new Zend_Session_Namespace('user'); $user_id = $user_session->user_info['user_id']; $file = $_FILES['csv']; $file_extension = strrchr($file['name'], "."); $h = new Application_Model_Helpers(); $tmp_file_name = $h->getMicrotimeStr() . $file_extension; $savepath = "../temp/"; $tmp_file_path = $savepath . $tmp_file_name; move_uploaded_file($file["tmp_name"], $tmp_file_path); $plist = new Product_Model_Productlist(); // 读取文件 require_once LIBRARY_PATH . "/Excel/reader.php"; $reader = new Spreadsheet_Excel_Reader(); $reader->setOutputEncoding('UTF-8'); $reader->setUTFEncoder('mb'); $reader->setRowColOffset(1); $reader->read($tmp_file_path); error_reporting(E_ALL ^ E_NOTICE); $data = array(); for ($i = 1; $i <= $reader->sheets[0]['numRows']; $i++) { $row = array(); for ($j = 1; $j <= $reader->sheets[0]['numCols']; $j++) { $row[] = $reader->sheets[0]['cells'][$i][$j]; } $data[] = $row; } // 数据校验 if (count($data) <= 1) { $result['success'] = false; $result['info'] = "文件中无数据!"; echo Zend_Json::encode($result); exit; } $cols = array("sn", "code", "step", "description", "is_bom_exists", "bom_apply_time", "bom_archive_time", "product_code", "bosa", "bosa_supply", "tosa", "tosa_supply", "rosa", "rosa_supply", "pcb", "pcba", "dg02", "dg01", "product_label", "barcode_label", "label_print_rule", "tooling_model", "sample_send_time", "pra", "trial_produce_qa1", "pmr", "dl", "ipa", "cri", "ds", "dd", "pl", "pes", "pcb_file", "icd", "smt", "mp", "sqr", "dvs", "dvp", "dvr", "dvt", "rtr", "emr", "mtb", "tsq", "sqc", "ed", "pts", "sp", "ep", "fep", "fsp", "ld", "pd", "pg", "nfc", "frm", "pfc", "wi", "other", "create_time", "create_user", "update_time", "update_user"); for ($i = 1; $i < count($data); $i++) { $num = $i + 1; $row = $data[$i]; if (count($row) < 2 || !$row[1]) { continue; } $code = $row[1]; if (!$this->checkExists($code)) { $insertData = array(); $k = 0; foreach ($cols as $c) { $insertData[$c] = $row[$k]; $k++; } try { $plist->insert($insertData); } catch (Exception $e) { $result['result'] = false; $result['info'] = $e->getMessage(); echo Zend_Json::encode($result); exit; } } else { $errors[] = $code; } } $result['error'] = $errors; } echo Zend_Json::encode($result); exit; }
function UpdateChecks() { $AccountID = $_POST["AccountID"]; $result = ""; require_once "phpExcelReader.php"; $data = new Spreadsheet_Excel_Reader(); $data->setOutputEncoding('utf-8'); $data->setRowColOffset(0); $data->read($_FILES["attach"]["tmp_name"]); for ($i = 0; $i < $data->sheets[0]['numRows']; $i++) { $CheckNo = ""; switch ($AccountID) { case "1": // ملی if (trim($data->sheets[0]['cells'][$i][3]) == "چك") { $CheckNo = $data->sheets[0]['cells'][$i][4]; $CheckNo = substr($CheckNo, strlen($CheckNo) - 6); } break; case "5": //پاسارگاد if (strpos($data->sheets[0]['cells'][$i][8], "وصول چک") !== false) { $CheckNo = $data->sheets[0]['cells'][$i][7]; $CheckNo = substr($CheckNo, strlen($CheckNo) - 6); } } if ($CheckNo == "") { continue; } //............... add debtor rows to doc ........................... if ($_POST["DocID"] != "") { $dt = PdoDataAccess::runquery("select * from ACC_DocCheques \n\t\t\t\twhere CheckStatus in(1,2) AND AccountID=? AND CheckNo=?", array($AccountID, $CheckNo)); if (count($dt) > 0) { $obj = new ACC_DocItems(); $obj->DocID = $_POST["DocID"]; $obj->kolID = 42; // اسناد پرداختنی $obj->moinID = 1; // کوتاه مدت $obj->TafsiliID = $dt[0]["TafsiliID"]; $obj->DebtorAmount = $dt[0]["amount"]; $obj->CreditorAmount = 0; $obj->Add(); } } //.................................................................. PdoDataAccess::runquery("update ACC_DocCheques set CheckStatus=3 where AccountID=? AND CheckNo=?", array($AccountID, $CheckNo)); if (PdoDataAccess::AffectedRows() > 0) { $result .= "شماره چک : " . $CheckNo . " [ تعداد ردیف به روز شده : " . PdoDataAccess::AffectedRows() . "]<br>"; } } echo Response::createObjectiveResponse(true, $result == "" ? "هیچ چکی به روز نگردید" : $result); die; while (($row = fgetcsv($handle)) !== false) { $row[3] = iconv(mb_detect_encoding($row[3], mb_detect_order(), true), "UTF-8", $row[3]); echo $row[3] . "-------"; if (trim($row[3]) == "چك") { echo $row[4] . "*********"; $CheckNo = substr($row[4], 4); PdoDataAccess::runquery("update ACC_DocCheques set CheckStatus=2 where AccountID=? AND CheckNo=?", array($AccountID, (int) $CheckNo)); if (PdoDataAccess::AffectedRows() > 0) { $result .= "شماره چک : " . $CheckNo . " [ تعداد ردیف به روز شده : " . PdoDataAccess::AffectedRows() . "]<br>"; } } } fclose($handle); echo Response::createObjectiveResponse(true, $result == "" ? "هیچ چکی به روز نگردید" : $result); die; }
} if (is_uploaded_file($_FILES['userfile']['tmp_name']) === TRUE and isset($annettuvuosi) and $annettuvuosi != 0 and isset($tilinumero) and trim($tilinumero) != '' and $tee == 'LUO') { $path_parts = pathinfo($_FILES['userfile']['name']); $ext = strtoupper($path_parts['extension']); if ($ext != "XLS") { die("<font class='error'><br>" . t("Ainoastaan .xls tiedostot sallittuja") . "!</font>"); } if ($_FILES['userfile']['size'] == 0) { die("<font class='error'><br>" . t("Tiedosto on tyhjä") . "!</font>"); } require_once 'excel_reader/reader.php'; // ExcelFile $data = new Spreadsheet_Excel_Reader(); // Set output Encoding. $data->setOutputEncoding('CP1251'); $data->setRowColOffset(0); $data->read($_FILES['userfile']['tmp_name']); echo "<font class='message'>" . t("Tarkastetaan lähetetty tiedosto") . "...<br><br></font>"; echo "<form method='post'>"; // luetaan eka rivi tiedostosta.. $headers = array(); for ($excej = 0; $excej < $data->sheets[0]['numCols']; $excej++) { $headers[] = strtoupper(trim($data->sheets[0]['cells'][0][$excej])); } // Poistetaan tyhjät headerit oikealta for ($excej = 0; $excej = count($headers) - 1; $excej--) { if ($headers[$excej] != "") { break; } else { unset($headers[$excej]); }
function InsertData() { $data = new Spreadsheet_Excel_Reader(); $data->setOutputEncoding('utf-8'); $data->setRowColOffset(0); $data->read($_FILES["attach"]["tmp_name"]); $FileType = $_POST["FileType"]; $obj = new manage_pay_get_lists(); $MissionObj = new manage_mission_list_items(); $PGIObj = new manage_pay_get_list_items(); $log_obj = new manage_group_pay_get_log(); $success_count = 0; $unsuccess_count = 0; $costId = ""; if ($FileType == EXTRA_WORK_LIST) { for ($i = 1; $i < $data->sheets[0]['numRows']; $i++) { //for ($j = 0; $j < $data->sheets[0]['numCols']; $j++) //$data->sheets[0]['cells'][$i][$j]; if (!isset($data->sheets[0]['cells'][$i][0]) && !isset($data->sheets[0]['cells'][$i][1])) { break; } $query = " select p.pfname , p.plname , s.person_type from staff s inner join persons p on s.personid = p.personid\n\t\t\t\t\twhere staff_id =" . $data->sheets[0]['cells'][$i][1]; $result = PdoDataAccess::runquery($query); if ($costId != $data->sheets[0]['cells'][$i][0]) { $obj->list_id = null; $obj->cost_center_id = $data->sheets[0]['cells'][$i][0]; $obj->list_date = DateModules::Now(); $obj->doc_state = 1; $obj->list_type = EXTRA_WORK_LIST; if ($obj->AddList() === false) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][1], $result[0]["pfname"] . " " . $result[0]["plname"], " کد مرکز هزینه نامعتبر است."); $unsuccess_count++; continue; } $costId = $obj->cost_center_id; } if (count($result) > 0) { $pt = $result[0]["person_type"]; if ($pt == 3) { $salaryItemId = 152; } elseif ($pt == 2) { $salaryItemId = 39; } elseif ($pt == 5) { $salaryItemId = 639; } $PGIObj->staff_id = $data->sheets[0]['cells'][$i][1]; $PGIObj->salary_item_type_id = $salaryItemId; $PGIObj->approved_amount = $data->sheets[0]['cells'][$i][2]; $PGIObj->comments = $data->sheets[0]['cells'][$i][3]; $PGIObj->list_id = $obj->GetLastID("pay_get_lists", "list_id", " list_type = 1 and cost_center_id =" . $obj->cost_center_id); $PGIObj->list_row_no = $PGIObj->GetLastID("pay_get_list_items", "list_row_no", " list_id =" . $PGIObj->list_id) + 1; if ($PGIObj->Add() == false) { $log_obj->make_unsuccess_rows($PGIObj->staff_id); $unsuccess_count++; } } else { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][1], " ", "شماره شناسایی نامعتبر است."); $unsuccess_count++; } } $log_obj->finalize(); $st = preg_replace('/\\r\\n/', "", $log_obj->make_result()); echo "{success:true,data:'" . $st . "'}"; die; } elseif ($FileType == MISSION_LIST) { for ($i = 1; $i < $data->sheets[0]['numRows']; $i++) { //for ($j = 0; $j < $data->sheets[0]['numCols']; $j++) //$data->sheets[0]['cells'][$i][$j]; $query = " select p.pfname , p.plname , s.person_type from staff s inner join persons p on s.personid = p.personid\n\t\t\t\t\twhere staff_id =" . $data->sheets[0]['cells'][$i][1]; $result = PdoDataAccess::runquery($query); if ($costId != $data->sheets[0]['cells'][$i][0]) { $obj->list_id = null; $obj->cost_center_id = $data->sheets[0]['cells'][$i][0]; $obj->list_date = DateModules::Now(); $obj->doc_state = 1; $obj->list_type = MISSION_LIST; if ($obj->AddList() === false) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][1], $result[0]["pfname"] . " " . $result[0]["plname"], " کد مرکز هزینه نامعتبر است."); $unsuccess_count++; continue; } $costId = $obj->cost_center_id; } if (count($result) > 0) { $pt = $result[0]["person_type"]; if ($pt == 1) { $salaryItemId = 42; } elseif ($pt == 2) { $salaryItemId = 43; } elseif ($pt == 3) { $salaryItemId = 10315; } elseif ($pt == 5) { $salaryItemId = 643; } $MissionObj->staff_id = $data->sheets[0]['cells'][$i][1]; $MissionObj->doc_no = $data->sheets[0]['cells'][$i][2]; $MissionObj->doc_date = DateModules::shamsi_to_miladi($data->sheets[0]['cells'][$i][3]); $MissionObj->from_date = DateModules::shamsi_to_miladi($data->sheets[0]['cells'][$i][4]); $MissionObj->to_date = DateModules::shamsi_to_miladi($data->sheets[0]['cells'][$i][5]); $MissionObj->duration = $data->sheets[0]['cells'][$i][6]; $MissionObj->travel_cost = isset($data->sheets[0]['cells'][$i][7]) ? $data->sheets[0]['cells'][$i][7] : ""; $MissionObj->destination = $data->sheets[0]['cells'][$i][8]; $MissionObj->using_facilities = isset($data->sheets[0]['cells'][$i][9]) ? $data->sheets[0]['cells'][$i][9] : ""; // $MissionObj->report_summary = (isset($data->sheets[0]['cells'][$i][11])) ? $data->sheets[0]['cells'][$i][11] : "" ; $MissionObj->comments = isset($data->sheets[0]['cells'][$i][12]) ? $data->sheets[0]['cells'][$i][12] : ""; $MissionObj->region_coef = isset($data->sheets[0]['cells'][$i][10]) ? $data->sheets[0]['cells'][$i][10] : ""; $MissionObj->salary_item_type_id = $salaryItemId; $MissionObj->list_id = $obj->GetLastID("pay_get_lists", "list_id", " list_type = 9 and cost_center_id =" . $obj->cost_center_id); $MissionObj->list_row_no = $MissionObj->GetLastID("mission_list_items", "list_row_no", " list_id =" . $MissionObj->list_id) + 1; if ($MissionObj->Add() == false) { $log_obj->make_unsuccess_rows($MissionObj->staff_id, $result[0]["pfname"] . " " . $result[0]["plname"], "اطلاعات مربوط به این فرد معتبر نمی باشد."); $unsuccess_count++; } } else { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][1], " ", "شماره شناسایی نامعتبر است."); $unsuccess_count++; } } $log_obj->finalize(); $st = preg_replace('/\\r\\n/', "", $log_obj->make_result()); echo "{success:true,data:'" . $st . "'}"; die; } elseif ($FileType == 11) { //.... محاسبه باقیمانده وامها .......... $Arr = preg_split('/[\\/]/', DateModules::shNow()); $LYear = $Arr[0]; $LMonth = $Arr[1]; manage_subtracts::GetRemainder("", "", "", true, $LMonth, $LYear); //...................................... $pdo = PdoDataAccess::getPdoObject(); $pdo->beginTransaction(); $DB = ""; //echo $data->sheets[0]['numRows'].'----' ; die() ; for ($i = 1; $i < $data->sheets[0]['numRows']; $i++) { $subObj = new manage_subtracts(); $subFlowObj = new manage_subtract_flows(); if (!isset($data->sheets[0]['cells'][$i][1])) { $log_obj->make_unsuccess_rows('-', '-', 'ردیف ' . ($i + 1) . ' معتبر نمی باشد.'); $unsuccess_count++; continue; } // آیا این قلم وجود دارد ؟...................................... $qry = " select salary_item_type_id , available_for\n\t\t\t\t\t\t\tfrom salary_item_types where salary_item_type_id=" . $data->sheets[0]['cells'][$i][1]; $resItem = PdoDataAccess::runquery($qry); //.............................................................. if (isset($resItem[0]['salary_item_type_id']) && $resItem[0]['salary_item_type_id'] > 0) { if (empty($data->sheets[0]['cells'][$i][0])) { $log_obj->make_unsuccess_rows('-', $data->sheets[0]['cells'][$i][1], 'لطفا شماره شناسایی ردیف ' . ($i + 1) . ' را وارد نمایید.'); $unsuccess_count++; continue; } // قلم مربوط به وام نبوده است ............................... if ($data->sheets[0]['cells'][$i][2] == 1 && $resItem[0]['available_for'] != 1) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], $data->sheets[0]['cells'][$i][1], " قلم مربوط به وام نمی باشد. "); $unsuccess_count++; continue; } if ($data->sheets[0]['cells'][$i][2] == 2 && !($resItem[0]['available_for'] == 4 || $resItem[0]['available_for'] == 5)) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], $data->sheets[0]['cells'][$i][1], " قلم مربوط به کسور نمی باشد. "); $unsuccess_count++; continue; } if ($data->sheets[0]['cells'][$i][2] == 1 && !empty($data->sheets[0]['cells'][$i][6]) && $data->sheets[0]['cells'][$i][6] != 0 && !empty($data->sheets[0]['cells'][$i][5]) && $data->sheets[0]['cells'][$i][5] != 0 && $data->sheets[0]['cells'][$i][6] > $data->sheets[0]['cells'][$i][5]) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], $data->sheets[0]['cells'][$i][1], 'قسط وام مورد نظر از مبلغ اوليه آن بزرگتر است.'); $unsuccess_count++; continue; } if (!empty($data->sheets[0]['cells'][$i][8]) && DateModules::GetDateFormat($data->sheets[0]['cells'][$i][7]) > DateModules::GetDateFormat($data->sheets[0]['cells'][$i][8])) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], $data->sheets[0]['cells'][$i][1], 'تاريخ پايان بايد بزرگتر و يا مساوي تاريخ شروع باشد.'); $unsuccess_count++; continue; } if (empty($data->sheets[0]['cells'][$i][3])) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], $data->sheets[0]['cells'][$i][1], 'نوع وام/کسور مشخص نشده است.'); $unsuccess_count++; continue; } if (!empty($data->sheets[0]['cells'][$i][4])) { $qry = "select bank_id from banks where branch_code=" . $data->sheets[0]['cells'][$i][4]; $resbank = PdoDataAccess::runquery($qry); if (count($resbank) == 0) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], $data->sheets[0]['cells'][$i][1], 'کد بانک معتبر نمی باشد.'); $unsuccess_count++; continue; } } $qry = " select staff_id,PersonID from staff where staff_id =" . $data->sheets[0]['cells'][$i][0]; $resStaff = PdoDataAccess::runquery($qry); if (count($resStaff) == 0) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], $data->sheets[0]['cells'][$i][1], ' شماره شناسایی معتبر نمی باشد.'); $unsuccess_count++; continue; } //.............................................................................. $Arr2 = preg_split('/[\\/]/', DateModules::GetDateFormat($data->sheets[0]['cells'][$i][7])); $sfd = (int) $Arr2[0] . "/" . (int) $Arr2[1] . "/" . (int) $Arr2[2]; if (!empty($data->sheets[0]['cells'][$i][8])) { $Arr3 = preg_split('/[\\/]/', DateModules::GetDateFormat($data->sheets[0]['cells'][$i][8])); $efd = (int) $Arr3[0] . "/" . (int) $Arr3[1] . "/" . (int) $Arr3[2]; } //........................................................................... $Arr = preg_split('/[\\/]/', DateModules::GetDateFormat($data->sheets[0]['cells'][$i][7])); $sdate = $edate = ''; $start_day = ""; $start_month = (int) $Arr[1]; if ($start_month < 7) { $day = 31; } elseif ($start_month < 12) { $day = 30; } elseif ($start_month == 12) { $day = 29; } $sd = (int) $Arr[0] . "/" . (int) $Arr[1] . "/01"; $ed = (int) $Arr[0] . "/" . (int) $Arr[1] . "/" . $day; $sdate = DateModules::shamsi_to_miladi($sd); $edate = DateModules::shamsi_to_miladi($ed); $PID = $resStaff[0]['PersonID']; //ثبت وام یا کسور جدید.................................................. if ($data->sheets[0]['cells'][$i][3] == 1) { $qry = " select subtract_id, remainder, instalment \n\t\t\t\t\t\t\t\t\tfrom person_subtracts \n\t\t\t\t\t\t\t\t\t\twhere salary_item_type_id = " . $data->sheets[0]['cells'][$i][1] . " and\n\t\t\t\t\t\t\t\t\t\t\t PersonID =" . $PID . " AND IsFinished = 0 and \n\t\t\t\t\t\t\t\t\t\t\t start_date <= '" . str_replace("/", "-", $sdate) . "' and \n\t\t\t\t\t\t\t\t\t\t\t ( end_date >= '" . str_replace("/", "-", $edate) . "' OR end_date IS NULL OR end_date = '0000-00-00' ) "; $res = PdoDataAccess::runquery($qry); if (count($res) > 0 && $res[0]['subtract_id'] > 0) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", "این /کسور قبلا در سیستم ثبت گردیده است."); $unsuccess_count++; continue; } //....................................................... $subObj->PersonID = $PID; $subObj->subtract_type = $data->sheets[0]['cells'][$i][2] == 1 ? LOAN : FIX_FRACTION; $subObj->bank_id = !empty($data->sheets[0]['cells'][$i][4]) ? $resbank[0]['bank_id'] : ""; $subObj->first_value = !empty($data->sheets[0]['cells'][$i][5]) ? $data->sheets[0]['cells'][$i][5] : 0; $subObj->instalment = !empty($data->sheets[0]['cells'][$i][6]) ? $data->sheets[0]['cells'][$i][6] : 0; $subObj->remainder = !empty($data->sheets[0]['cells'][$i][5]) ? $data->sheets[0]['cells'][$i][5] : 0; $subObj->start_date = DateModules::shamsi_to_miladi($sfd); $subObj->end_date = !empty($data->sheets[0]['cells'][$i][8]) ? DateModules::shamsi_to_miladi($efd) : ""; $subObj->loan_no = !empty($data->sheets[0]['cells'][$i][9]) ? $data->sheets[0]['cells'][$i][9] : ""; $subObj->contract_no = !empty($data->sheets[0]['cells'][$i][10]) ? $data->sheets[0]['cells'][$i][10] : ""; $subObj->salary_item_type_id = $data->sheets[0]['cells'][$i][1]; $subObj->reg_date = DateModules::NowDateTime(); //Isfinished = 0 ; if ($subObj->Add() === false) { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", " خطا "); $unsuccess_count++; continue; } } // ثبت گردش ................................................... if ($data->sheets[0]['cells'][$i][3] == 2) { $qry = " select psf.subtract_id \n\t\t\t\t\t\t\t\t\tfrom person_subtracts ps inner join person_subtract_flows psf \n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\ton ps.subtract_id = psf.subtract_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\twhere ps.salary_item_type_id = " . $data->sheets[0]['cells'][$i][1] . " and\n\t\t\t\t\t\t\t\t\t\t\t ps.PersonID =" . $PID . " and \n\t\t\t\t\t\t\t\t\t\t\t psf.flow_date >= '" . str_replace("/", "-", $sdate) . "' and \n\t\t\t\t\t\t\t\t\t\t\t psf.flow_date <= '" . str_replace("/", "-", $edate) . "'"; $res = PdoDataAccess::runquery($qry); if (count($res) > 0 && $res[0]['subtract_id'] > 0) { //echo PdoDataAccess::GetLatestQueryString() ; die(); $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], "-", " این گردش قبلا در سیستم ثبت گردیده است."); $unsuccess_count++; continue; } //................................................................. $qry = " select ps.subtract_id, ts.remainder, ps.instalment \n\t\t\t\t\t\t\t\t\tfrom person_subtracts ps left join tmp_SubtractRemainders ts \n\t\t\t\t\t\t\t\t\t on ps.subtract_id = ts.subtract_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t \n\t\t\t\t\t\t\t\t\t\twhere ps.salary_item_type_id = " . $data->sheets[0]['cells'][$i][1] . " and\n\t\t\t\t\t\t\t\t\t\t\t ps.PersonID =" . $PID . " AND ps.IsFinished = 0 AND ps.start_date <= '" . str_replace("/", "-", $sdate) . "' AND \n\t\t\t\t\t\t\t\t\t\t\t( ps.end_date >= '" . str_replace("/", "-", $edate) . "' OR ps.end_date IS NULL OR ps.end_date = '0000-00-00' ) "; $res = PdoDataAccess::runquery($qry); // echo PdoDataAccess::GetLatestQueryString() ; if (count($res) > 0 && $res[0]['subtract_id'] > 0) { $new_remainder = $new_instalment = 0; if ($data->sheets[0]['cells'][$i][2] == 1) { $subFlowObj->subtract_id = $res[0]['subtract_id']; $subFlowObj->flow_type = REGISTER_NEW_FLOW_TYPE; $subFlowObj->flow_date = DateModules::NowDateTime(); $subFlowObj->flow_coaf = !empty($data->sheets[0]['cells'][$i][5]) && $data->sheets[0]['cells'][$i][5] * 1 > $res[0]['remainder'] * 1 ? -1 : 1; $subFlowObj->amount = !empty($data->sheets[0]['cells'][$i][5]) && $data->sheets[0]['cells'][$i][5] * 1 > $res[0]['remainder'] * 1 ? $data->sheets[0]['cells'][$i][5] * 1 - $res[0]['remainder'] * 1 : $res[0]['remainder'] * 1 - $data->sheets[0]['cells'][$i][5] * 1; $subFlowObj->newRemainder = !empty($data->sheets[0]['cells'][$i][5]) ? $data->sheets[0]['cells'][$i][5] : 0; $subFlowObj->comments = "گردش دستی"; if ($subFlowObj->Add() == false) { $log_obj->make_unsuccess_rows($subObj->staff_id); $unsuccess_count++; continue; } else { $new_remainder = !empty($data->sheets[0]['cells'][$i][5]) ? $data->sheets[0]['cells'][$i][5] : 0; $new_instalment = !empty($data->sheets[0]['cells'][$i][6]) ? $data->sheets[0]['cells'][$i][6] : 0; $qry = " update person_subtracts \n\t\t\t\t\t\t\t\t\t\t\tset\t\tremainder = " . $new_remainder . ",\n\t\t\t\t\t\t\t\t\t\t\t\t\tinstalment = " . $new_instalment . ",\n\t\t\t\t\t\t\t\t\t\t\t\t\treg_date = '" . DateModules::NowDateTime() . "'\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\twhere subtract_id =" . $subFlowObj->subtract_id; PdoDataAccess::runquery($qry); } } else { //echo "*****"; die() ; $new_instalment = !empty($data->sheets[0]['cells'][$i][6]) ? $data->sheets[0]['cells'][$i][6] : 0; $qry = " update person_subtracts \n\t\t\t\t\t\t\t\t\t\tset\t\t\n\t\t\t\t\t\t\t\t\t\t\t\tinstalment = " . $new_instalment . ",\n\t\t\t\t\t\t\t\t\t\t\t\treg_date = '" . DateModules::NowDateTime() . "'\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\twhere subtract_id =" . $res[0]['subtract_id']; PdoDataAccess::runquery($qry); } } else { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], $data->sheets[0]['cells'][$i][1], "این وام / کسور ثبت سیستم نشده است."); $unsuccess_count++; continue; } } } else { $log_obj->make_unsuccess_rows($data->sheets[0]['cells'][$i][0], $data->sheets[0]['cells'][$i][1], "کد قلم معتبر نمی باشد."); $unsuccess_count++; continue; } } $log_obj->finalize(); $st = preg_replace('/\\r\\n/', "", $log_obj->make_result()); if ($unsuccess_count > 0) { $pdo->rollBack(); } else { $pdo->commit(); } echo "{success:true,data:'" . $st . "'}"; die; } }
function uploadBoard() { $this->load->model('exam_model', 'exam'); $examArray = $this->exam->getInfoList(); $this->load->addViewData('examArray', $examArray); if ($this->input->get('exam_paper')) { foreach ($examArray as $exam) { if ($exam['exam_paper'] == intval($this->input->get('exam_paper'))) { $currentExam = $exam; } } } elseif (!empty($examArray) > 0) { $currentExam = $examArray[0]; } else { $currentExam = false; } $this->load->addViewData('currentExam', $currentExam); while ($submit == 'score_table') { if ($_FILES['score_table']['error'] > 0) { showMessage('文件上错错误:错误代码: ' . $_FILES['score_table']['error'], 'warning'); break; } if (!(preg_match('/\\.(\\w*?)$/', $_FILES['score_table']['name'], $extname_match) && $extname_match[1] == 'xls')) { showMessage('文件格式错误,请上传xls格式的excel表格', 'warning'); break; } require APPPATH . 'third_party/PHP-ExcelReader/reader.php'; $data = new Spreadsheet_Excel_Reader(); $data->setOutputEncoding('utf-8'); $data->setRowColOffset(0); $data->read($_FILES['score_table']['tmp_name']); $rows = $data->sheets[0]['numRows']; $cols = $data->sheets[0]['numCols']; $exam_part_array = array(); $break = false; for ($i = 1; $i < $cols; $i++) { if ($data->sheets[0]['cells'][0][$i] == '' || is_numeric($data->sheets[0]['cells'][0][$i])) { showMessage('某大题的名字是空的或是数字', 'warning'); $break = true; break; } $exam_part_data_array[] = array('exam_paper' => $currentExam['exam_paper'], 'name' => $data->sheets[0]['cells'][0][$i]); } if ($break) { break; } for ($i = 1; $i < $rows; $i++) { for ($j = 1; $j < $cols; $j++) { $cell = isset($data->sheets[0]['cells'][$i][$j]) ? $data->sheets[0]['cells'][$i][$j] : ''; if (!(is_numeric($cell) || $cell == '') || $cell < 0) { showMessage('第' . ($i + 1) . '行 第' . ($j + 1) . '列的数据' . $cell . '中包含错误字符,注意只能是数字或留空(缺考)', 'warning'); $break = true; break; } } if ($break) { break; } if (array_sum(array_slice($data->sheets[0]['cells'][$i], 1)) > 150) { showMessage('第' . $i . '行的小分和超过了150分!注意不用填写总分', 'warning'); $break = true; break; } } if ($break) { break; } if ($rows - 1 < $currentExam['students']) { //showMessage('本张试卷有'.$currentExam['students'].'人参考,上传的分数为'.($rows-1).'条,请检核数据重新上传','warning');break; } foreach ($exam_part_data_array as $exam_part_data) { //插入大题 $this->db->insert('exam_part', $exam_part_data); $exam_part_array[] = $this->db->insert_id(); } //创建一张临时表 $q_create_temp_table = "CREATE TEMPORARY TABLE `t` (`id` INT NOT NULL AUTO_INCREMENT, `num` CHAR( 6 ) NOT NULL,"; foreach ($exam_part_array as $exam_part) { $q_create_temp_table .= "`" . $exam_part . "` DECIMAL( 10, 1 ) NULL,"; } $q_create_temp_table .= " PRIMARY KEY (`id`) ,UNIQUE (`num`))"; $this->db->query($q_create_temp_table); //excel表格上传到临时表 $q_insert_t_score = 'INSERT INTO t (num,`' . implode('`,`', $exam_part_array) . '`) VALUES'; for ($i = 1; $i < $rows; $i++) { $q_insert_t_score .= "('" . $data->sheets[0]['cells'][$i][0] . "'"; for ($j = 1; $j < $cols; $j++) { $cell = isset($data->sheets[0]['cells'][$i][$j]) ? $data->sheets[0]['cells'][$i][$j] : ''; $q_insert_t_score .= "," . ($cell == '' ? 'NULL' : "'" . $cell . "'") . ""; } $q_insert_t_score .= ')'; if ($i != $rows - 1) { $q_insert_t_score .= ','; } } if (!$this->db->query($q_insert_t_score)) { showMessage('上传错误,可能有重复学号或者错误的学号', 'warning'); break; } $q_search_illegal_student = "\n\t\t\t\tSELECT id,num FROM t WHERE num NOT IN\n\t\t\t\t(\n\t\t\t\t\tSELECT view_student.num \n\t\t\t\t\tFROM exam_student INNER JOIN view_student ON view_student.id=exam_student.student\n\t\t\t\t\tWHERE exam_student.exam='" . $currentExam['exam'] . "'" . (isset($currentExam['extra_course']) ? " AND exam_student.extra_course='" . $currentExam['extra_course'] . "'" : '') . "\n\t\t\t\t)\n\t\t\t\tLIMIT 1\n\t\t\t"; $r_search_illegal_student = $this->db->query($q_search_illegal_student); if (1) { $illegal_line = db_fetch_array($r_search_illegal_student); showMessage($illegal_line['id'] + 1 . '行的"' . $illegal_line['num'] . '"学号不正确,可能填写错误或学生不属于本场考试', 'warning'); break; } foreach ($exam_part_array as $exam_part) { //插入分数 $q_insert_score = "\n\t\t\t\tREPLACE INTO score (student,exam,exam_paper,exam_part,score,is_absent,scorer,scorer_username,time)\n\t\t\t\tSELECT view_student.id,'" . $currentExam['exam'] . "','" . $currentExam['exam_paper'] . "','" . $exam_part . "',t.`" . $exam_part . "`,if(t.`" . $exam_part . "` IS NULL,1,0),{$this->user->id},'" . $_SESSION['username'] . "','" . $this->date->now . "' \n\t\t\t\tFROM t INNER JOIN view_student ON t.num=view_student.num\n\t\t\t\t"; mysql_query($q_insert_score); } showMessage('文件上传成功!'); break; } }
function SaveOperation() { $obj = new NTC_operations(); PdoDataAccess::FillObjectByArray($obj, $_POST); $obj->GroupLetter = isset($_POST["GroupLetter"]) ? "YES" : "NO"; $pdo = PdoDataAccess::getPdoObject(); $pdo->beginTransaction(); if (empty($obj->OperationID)) { $obj->OperationDate = PDONOW; $result = $obj->Add($pdo); } else { $result = $obj->Edit($pdo); } require_once "phpExcelReader.php"; $data = new Spreadsheet_Excel_Reader(); $data->setOutputEncoding('utf-8'); $data->setRowColOffset(0); $data->read($_FILES["PersonFile"]["tmp_name"]); for ($i = 0; $i < $data->sheets[0]['numRows']; $i++) { if (!empty($data->sheets[0]['cells'][$i][0])) { $PersonID = $data->sheets[0]['cells'][$i][0]; $dt = PdoDataAccess::runquery("select PersonID from BSC_persons where PersonID=?", array($PersonID)); if (count($dt) > 0) { $PersonObj = new NTC_persons(); $PersonObj->OperationID = $obj->OperationID; $PersonObj->PersonID = $PersonID; for ($j = 1; $j < count($data->sheets[0]['cells'][$i]); $j++) { eval("\$PersonObj->col{$j} = '" . $data->sheets[0]['cells'][$i][$j] . "';"); } $PersonObj->Add($pdo); } } } $dt = NTC_persons::Get(" AND OperationID=?", array($obj->OperationID), $pdo); if ($dt->rowCount() == 0) { $pdo->rollBack(); echo Response::createObjectiveResponse(false, "در فایل ارسالی هیچ فرد معتبری یافت نشد"); die; } $dt = $dt->fetchAll(); //----------- create letter ------------- if ($obj->SendType == "LETTER" && $obj->GroupLetter == "YES") { $LetterObj = new OFC_letters(); $LetterObj->LetterType = "INNER"; $LetterObj->LetterTitle = $obj->title; $LetterObj->LetterDate = PDONOW; $LetterObj->RegDate = PDONOW; $LetterObj->PersonID = $_SESSION["USER"]["PersonID"]; $LetterObj->context = $obj->context; if (!$LetterObj->AddLetter($pdo)) { ExceptionHandler::PushException("خطا در ثبت نامه"); } } //--------------------------------------- foreach ($dt as $row) { $context = $obj->context; for ($i = 1; $i < 10; $i++) { $context = preg_replace("/\\[col" . $i . "\\]/", $row["col" . $i], $context); } switch ($obj->SendType) { case "SMS": break; //------------------------------------------------------------------ //------------------------------------------------------------------ case "EMAIL": $email = $row["email"]; if ($email == "") { ExceptionHandler::PushException("فاقد ایمیل"); continue; } $result = SendEmail($email, $obj->title, $context); if (!$result) { ExceptionHandler::PushException("خطا در ارسال ایمیل"); } break; //------------------------------------------------------------------ //------------------------------------------------------------------ case "LETTER": if ($obj->GroupLetter == "NO") { $LetterObj = new OFC_letters(); $LetterObj->LetterType = "INNER"; $LetterObj->LetterTitle = $obj->title; $LetterObj->LetterDate = PDONOW; $LetterObj->RegDate = PDONOW; $LetterObj->PersonID = $_SESSION["USER"]["PersonID"]; $LetterObj->context = $context; $LetterObj->AddLetter($pdo); $SendObj = new OFC_send(); $SendObj->LetterID = $LetterObj->LetterID; $SendObj->FromPersonID = $LetterObj->PersonID; $SendObj->ToPersonID = $row["PersonID"]; $SendObj->SendDate = PDONOW; $SendObj->SendType = 1; if (!$SendObj->AddSend($pdo)) { ExceptionHandler::PushException("خطا در ثبت نامه"); } } else { $Cobj = new OFC_LetterCustomers(); $Cobj->LetterID = $LetterObj->LetterID; $Cobj->PersonID = $row["PersonID"]; $Cobj->IsHide = "NO"; $Cobj->LetterTitle = $obj->title; if (!$Cobj->Add($pdo)) { ExceptionHandler::PushException("خطا در ثبت ذینفع نامه"); } } break; //------------------------------------------------------------------ } if (ExceptionHandler::GetExceptionCount() == 0) { $PObj = new NTC_persons(); $PObj->RowID = $row["RowID"]; $PObj->IsSuccess = "YES"; if ($obj->SendType == "LETTER") { $PObj->LetterID = $LetterObj->LetterID; } $PObj->Edit($pdo); } } $pdo->commit(); //print_r(ExceptionHandler::PopAllExceptions()); echo Response::createObjectiveResponse($result, ExceptionHandler::GetExceptionsToString()); die; }