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