function DatesAreValid()
{
$dt = PdoDataAccess::runquery("select * from ATN_PersonShifts\r\n\t\t\twhere PersonID=:p \r\n\t\t\tAND ( :f between FromDate AND ToDate OR :t between FromDate AND ToDate ) AND RowID <> :r", array(":p" => $this->PersonID, ":r" => $this->RowID, ":f" => DateModules::shamsi_to_miladi($this->FromDate, "-"), ":t" => DateModules::shamsi_to_miladi($this->ToDate, "-")));
if (count($dt) > 0) {
ExceptionHandler::PushException("شیفت انتخاب شده دارای تداخل زمانی میباشد");
return false;
}
/*$ShiftObj = new ATN_shifts($this->ShiftID);
$dt = PdoDataAccess::runquery("select * from ATN_PersonShifts join ATN_shifts using(ShiftID)
where PersonID=:p
AND ( (:s between FromTime AND ToTime) OR (:e between FromTime AND ToTime) ) AND RowID <> :r
AND ( (:f between FromDate AND if(ToDate='0000-00-00','4000-00-00',ToDate) )
OR (:t between FromDate AND if(ToDate='0000-00-00','4000-00-00',ToDate) ) )",
array(":p" => $this->PersonID, ":s" => $ShiftObj->FromTime,
":e" => $ShiftObj->ToTime, ":r" => $this->RowID,
":f" => DateModules::shamsi_to_miladi($this->FromDate),
":t" => DateModules::shamsi_to_miladi($this->ToDate)));
if(count($dt) > 0)
{
ExceptionHandler::PushException("شیفت های این فرد با یکدیگر دارای تداخل ساعتی می باشند");;
return false;
} */
return true;
}
function MakeWhere(&$where, &$whereParam)
{
foreach ($_POST as $key => $value) {
if ($key == "excel" || $value === "" || strpos($key, "combobox") !== false) {
continue;
}
$prefix = "";
switch ($key) {
case "FromStartDate":
case "ToStartDate":
case "FromEndDate":
case "ToEndDate":
case "FromLetterDate":
case "ToLetterDate":
$value = DateModules::shamsi_to_miladi($value, "-");
break;
case "FromAmount":
case "ToAmount":
$value = preg_replace('/,/', "", $value);
break;
}
if (strpos($key, "From") === 0) {
$where .= " AND " . $prefix . substr($key, 4) . " >= :{$key}";
} else {
if (strpos($key, "To") === 0) {
$where .= " AND " . $prefix . substr($key, 2) . " <= :{$key}";
} else {
$where .= " AND " . $prefix . $key . " = :{$key}";
}
}
$whereParam[":{$key}"] = $value;
}
}
function save()
{
$obj = new manage_professor_exe_posts();
PdoDataAccess::FillObjectByArray($obj, $_POST);
$postObj = new manage_posts($obj->post_id);
if ($postObj->post_id == "") {
echo Response::createObjectiveResponse(false, "کد پست وارد شده معتبر نمی باشد.");
die;
}
if ($postObj->validity_start != "" && $postObj->validity_start != '0000-00-00' && DateModules::CompareDate($postObj->validity_start, str_replace("/", "-", DateModules::shamsi_to_miladi($obj->from_date))) > 0 || $postObj->validity_end != "" && $postObj->validity_end != '0000-00-00' && ($obj->to_date != "" && $obj->to_date != "0000-00-00") && DateModules::CompareDate($postObj->validity_end, str_replace("/", "-", DateModules::shamsi_to_miladi($obj->to_date))) < 0) {
echo Response::createObjectiveResponse(false, "پست انتخابی از نظر تاریخ اعتبار و تاریخ های شروع و پایان وارد شده معتبر نمی باشد.");
die;
}
if (empty($obj->row_no)) {
$return = $obj->ADD();
} else {
$return = $obj->Edit();
}
if (!$return) {
echo Response::createObjectiveResponse($return, ExceptionHandler::GetExceptionsToString());
die;
}
if (isset($_POST["assign_post"])) {
$return = $obj->assign_post();
} else {
$return = $obj->release_post();
}
echo Response::createObjectiveResponse($return, ExceptionHandler::GetExceptionsToString());
die;
}
public static function simulate_bime($pay_year, $pay_month, $payment_type = NULL)
{
$e_date = "31-" . $pay_month . "-" . $pay_year;
$end_month_date = DateModules::shamsi_to_miladi($e_date);
$s_date = "1-" . $pay_month . "-" . $pay_year;
$start_month_date = DateModules::shamsi_to_miladi($s_date);
$worker_month_day = 30;
$actual_month_day = DateModules::get_month_dayno($pay_year, $pay_month);
//...................................
PdoDataAccess::runquery("DROP TABLE IF EXISTS temp_insure_include_sum ");
PdoDataAccess::runquery(" DROP TABLE IF EXISTS temp_limit_staff ");
PdoDataAccess::runquery(" CREATE TEMPORARY TABLE temp_limit_staff AS\r\n\t\t\t\t\t\t\t\tSELECT DISTINCT s.staff_id , s.person_type\r\n\t\t\t\t\t\t\t\tFROM staff s\r\n\t\t\t\t\t\t\t\t\tINNER JOIN staff_include_history si\r\n\t\t\t\t\t\t\t\t\t\tON (s.staff_id = si.staff_id AND si.start_date <= ('{$end_month_date}') AND (si.end_date IS NULL OR si.end_date >= ('{$end_month_date}'))) \r\n\t\t\t\t\t\t\t\tWHERE si.insure_include = 1 ");
PdoDataAccess::runquery("ALTER TABLE temp_limit_staff ADD INDEX (staff_id)");
PdoDataAccess::runquery(" CREATE TEMPORARY table temp_insure_include_sum AS\r\n\t\t\t\t\t\t\t\tSELECT s.staff_id ,\r\n\t\t\t\t\t\t\t\t\t\t0 value,\r\n\t\t\t\t\t\t\t\t\t\tsum(pi.pay_value) + CASE WHEN sum(pi.diff_pay_value * pi.diff_value_coef) < 0 THEN 0 ELSE sum(pi.diff_pay_value * pi.diff_value_coef) END param1 ,\r\n\t\t\t\t\t\t\t\t\t\t0 param2 ,\r\n\t\t\t\t\t\t\t\t\t\t0 param3 , \r\n\t\t\t\t\t\t\t\t\t\tCASE WHEN s.person_type = " . HR_WORKER . " THEN {$worker_month_day} ELSE {$actual_month_day} END month_days\r\n\t\t\t\t\t\t\t\tFROM payment_items pi\r\n\t\t\t\t\t\t\t\tINNER JOIN salary_item_types sit\r\n\t\t\t\t\t\t\t\t\tON (pi.salary_item_type_id = sit.salary_item_type_id AND \r\n\t\t\t\t\t\t\t\t\t\tsit.credit_topic = " . CREDIT_TOPIC_1 . " AND \r\n\t\t\t\t\t\t\t\t\t\tsit.insure_include = 1)\r\n\t\t\t\t\t\t\t\tINNER JOIN temp_limit_staff s\r\n\t\t\t\t\t\t\t\t\tON (s.staff_id = pi.staff_id)\r\n\t\t\t\t\t\t\t\tWHERE pi.pay_year = ({$pay_year}) AND \r\n\t\t\t\t\t\t\t\t\tpi.pay_month = ({$pay_month}) AND \r\n\t\t\t\t\t\t\t\t\tpi.payment_type = 1\r\n\t\t\t\t\t\t\t\t\t{$payment_type_where}\r\n\t\t\t\t\t\t\t\tGROUP BY\r\n\t\t\t\t\t\t\t\tstaff_id; ");
PdoDataAccess::runquery("ALTER TABLE temp_insure_include_sum ADD INDEX (staff_id)");
$max_daily_salary_insure_include = manage_salary_params::get_salaryParam_value("", 100, SPT_MAX_DAILY_SALARY_INSURE_INCLUDE, $start_month_date);
PdoDataAccess::runquery(" UPDATE temp_insure_include_sum\r\n\t\t\t\t\t\t\t SET param1 = {$max_daily_salary_insure_include} * {$worker_month_day} \r\n\t\t\t\t\t\t\t WHERE param1 > {$max_daily_salary_insure_include} * {$worker_month_day} ");
//استخراج درصد بیمه بیکاری سهم کارفرما
$res = PdoDataAccess::runquery("SELECT value\r\n\t\t\t\t\t\t\t\t\tFROM salary_params\r\n\t\t\t\t\t\t\t\t\t\tWHERE from_date <= '" . $start_month_date . "' AND\r\n\t\t\t\t\t\t\t\t\t\t\tto_date >= '" . $end_month_date . "' AND person_type =100 AND \r\n\t\t\t\t\t\t\t\t\t\t\tparam_type = " . SPT_UNEMPLOYMENT_INSURANCE_VALUE);
$unemployment_insurance_value = $res[0]['value'];
//استخراج درصد بیمه سهم شخص
$res = PdoDataAccess::runquery("SELECT value\r\n\t\t\t\t\t\t\t\t\tFROM salary_params\r\n\t\t\t\t\t\t\t\t\tWHERE from_date <= '" . $start_month_date . "' AND\r\n\t\t\t\t\t\t\t\t\t\t\tto_date >= '" . $end_month_date . "' AND person_type =100 AND \r\n\t\t\t\t\t\t\t\t\t\t\tparam_type = " . SPT_SOCIAL_SUPPLY_INSURE_PERSON_VALUE);
$person_value = $res[0]['value'];
//استخراج درصد بیمه سهم کارفرما
$res = PdoDataAccess::runquery("SELECT value\r\n\t\t\t\t\t\t\t\t\tFROM salary_params\r\n\t\t\t\t\t\t\t\t\tWHERE from_date <= '" . $start_month_date . "' AND\r\n\t\t\t\t\t\t\t\t\t\t\tto_date >= '" . $end_month_date . "' AND person_type =100 AND \r\n\t\t\t\t\t\t\t\t\t\t\tparam_type = " . SPT_SOCIAL_SUPPLY_INSURE_EMPLOYER_VALUE);
$employmer_value = $res[0]['value'];
PdoDataAccess::runquery(" UPDATE temp_insure_include_sum\r\n\t\t\t\t\t\t\t\tSET value = param1 * {$person_value} ,\r\n\t\t\t\t\t\t\t\t\tparam2 = param1 * {$employmer_value} ,\r\n\t\t\t\t\t\t\t\t\tparam3 = param1 * {$unemployment_insurance_value} \r\n\t\t\t\t\t\t\t\tWHERE (1=1)");
}
function SaveRequest()
{
$obj = new ATN_requests();
PdoDataAccess::FillObjectByArray($obj, $_POST);
if ($obj->ReqType != "CORRECT") {
if (!empty($obj->ToDate)) {
$dt = PdoDataAccess::runquery("\r\n\t\t\t\tselect * from ATN_requests \r\n\t\t\t\twhere PersonID=:p AND RequestID<>:r \r\n\t\t\t\tAND ( (FromDate<=:f AND ToDate>=:f) OR (FromDate<=:t AND ToDate>=:t) )\r\n\t\t\t", array(":p" => $_SESSION["USER"]["PersonID"], ":r" => $obj->RequestID, ":f" => DateModules::shamsi_to_miladi($obj->FromDate, "-"), ":t" => DateModules::shamsi_to_miladi($obj->ToDate, "-")));
if (count($dt) > 0) {
echo Response::createObjectiveResponse(false, "در بازه زمانی وارد شده قبلا درخواستی ثبت شده است");
die;
}
} else {
$dt = PdoDataAccess::runquery("\r\n\t\t\t\tselect * from ATN_requests \r\n\t\t\t\twhere PersonID=:p AND RequestID<>:r \r\n\t\t\t\tAND (\r\n\t\t\t\t\t\t(FromDate<=:f AND ToDate>=:f) OR \r\n\t\t\t\t\t\t( if(ToDate is null,FromDate=:f,FromDate<=:f AND ToDate>=:f) AND \r\n\t\t\t\t\t\t\tStartTime<=:st AND EndTime>=:st) OR \r\n\t\t\t\t\t\t( if(ToDate is null,FromDate=:f,FromDate<=:f AND ToDate>=:f) AND \r\n\t\t\t\t\t\t\tStartTime<=:et AND EndTime>= :et) )\r\n\t\t\t", array(":p" => $_SESSION["USER"]["PersonID"], ":r" => $obj->RequestID, ":f" => DateModules::shamsi_to_miladi($obj->FromDate, "-"), ":st" => $obj->StartTime, ":et" => $obj->EndTime));
//echo PdoDataAccess::GetLatestQueryString();die();
if (count($dt) > 0) {
echo Response::createObjectiveResponse(false, "در بازه زمانی وارد شده قبلا درخواستی ثبت شده است");
die;
}
}
}
if (empty($obj->RequestID)) {
$obj->PersonID = $_SESSION["USER"]["PersonID"];
$obj->ReqDate = PDONOW;
$result = $obj->Add();
} else {
$result = $obj->Edit();
}
//print_r(ExceptionHandler::PopAllExceptions());
echo Response::createObjectiveResponse($result, "");
die;
}
function Add($pdo = null)
{
$dt = self::Get(" AND ChequeNo=? AND ChequeDate=?", array($this->ChequeNo, DateModules::shamsi_to_miladi($this->ChequeDate)));
if ($dt->rowCount() > 0) {
ExceptionHandler::PushException("چک دیگری با این شماره و تاریخ قبلا ثبت شده است");
return false;
}
return parent::Add($pdo);
}
function SaveContract()
{
$pdo = PdoDataAccess::getPdoObject();
$pdo->beginTransaction();
$CntObj = new CNT_contracts();
PdoDataAccess::FillObjectByArray($CntObj, $_POST);
if ($_POST["ContractID"] == "") {
$CntObj->RegPersonID = $_SESSION['USER']["PersonID"];
$CntObj->RegDate = PDONOW;
$result = $CntObj->Add($pdo);
} else {
$result = $CntObj->Edit($pdo);
/* removing values of contract items */
CNT_ContractItems::RemoveAll($CntObj->ContractID, $pdo);
}
if (!$result) {
$pdo->rollBack();
print_r(ExceptionHandler::PopAllExceptions());
//echo PdoDataAccess::GetLatestQueryString();
echo Response::createObjectiveResponse(false, ExceptionHandler::GetExceptionsToString());
die;
}
/* Adding the values of Contract items */
foreach ($_POST as $PostData => $val) {
if (empty($val)) {
continue;
}
if (!(substr($PostData, 0, 8) == "TplItem_")) {
continue;
}
$items = explode('_', $PostData);
$TemplateItemID = $items[1];
$CntItemsObj = new CNT_ContractItems();
$CntItemsObj->ContractID = $CntObj->ContractID;
$CntItemsObj->TemplateItemID = $TemplateItemID;
$TplItemObj = new CNT_TemplateItems($CntItemsObj->TemplateItemID);
switch ($TplItemObj->ItemType) {
case 'shdatefield':
$CntItemsObj->ItemValue = DateModules::shamsi_to_miladi($val);
break;
default:
$CntItemsObj->ItemValue = $val;
}
$result = $CntItemsObj->Add($pdo);
}
if (!$result) {
$pdo->rollBack();
print_r(ExceptionHandler::PopAllExceptions());
//echo PdoDataAccess::GetLatestQueryString();
echo Response::createObjectiveResponse(false, ExceptionHandler::GetExceptionsToString());
die;
}
$pdo->commit();
echo Response::createObjectiveResponse(true, $CntObj->ContractID);
die;
}
function SelectAllLetter()
{
$where = "1=1";
$param = array();
foreach ($_POST as $field => $value) {
if ($field == "excel" || empty($value) || strpos($field, "inputEl") !== false) {
continue;
}
$prefix = "";
switch ($field) {
case "PersonID":
$prefix = "l.";
break;
case "Customer":
$prefix = "lc.";
break;
case "LetterID":
case "LetterTitle":
$prefix = "l.";
break;
case "FromSendDate":
case "FromLetterDate":
case "ToSendDate":
case "ToLetterDate":
$value = DateModules::shamsi_to_miladi($value, "-");
break;
}
if ($field == "FromPersonID" || $field == "ToPersonID") {
$where .= " AND s." . $field . " = :{$field}";
$param[":{$field}"] = $value;
} else {
if (strpos($field, "From") === 0) {
$where .= " AND " . $prefix . substr($field, 4) . " >= :{$field}";
$param[":{$field}"] = $value;
} else {
if (strpos($field, "To") === 0) {
$where .= " AND " . $prefix . substr($field, 2) . " <= :{$field}";
$param[":{$field}"] = $value;
} else {
$where .= " AND " . $prefix . $field . " like :{$field}";
$param[":{$field}"] = "%" . $value . "%";
}
}
}
}
//echo $where;
//print_r($param);
$list = OFC_letters::FullSelect($where, $param, dataReader::makeOrder());
print_r(ExceptionHandler::PopAllExceptions());
//echo PdoDataAccess::GetLatestQueryString();
$no = $list->rowCount();
$list = PdoDataAccess::fetchAll($list, $_GET["start"], $_GET["limit"]);
echo dataReader::getJsonData($list, $no, $_GET['callback']);
die;
}
function LoanReport()
{
$BranchID = $_POST["BranchID"];
$blockID = $_POST["BlockID"];
$StartDate = $_POST["StartDate"];
$EndDate = $_POST["EndDate"];
$TafsiliID = $_POST["TafsiliID"];
$where = "";
$param = array($_SESSION["accounting"]["CycleID"], $blockID);
if ($BranchID != "") {
$where .= " AND BranchID =?";
$param[] = $BranchID;
}
if ($TafsiliID != "") {
$where .= " AND (TafsiliID=? or TafsiliID2=?)";
$param[] = $TafsiliID;
$param[] = $TafsiliID;
}
if ($StartDate != "") {
$where .= " AND DocDate >=?";
$param[] = DateModules::shamsi_to_miladi($StartDate, "-");
}
if ($EndDate != "") {
$where .= " AND DocDate <=?";
$param[] = DateModules::shamsi_to_miladi($EndDate, "-");
}
$dt = PdoDataAccess::runquery("\r\n\t\tselect cc.*,concat_ws(' - ',b1.BlockDesc,b2.BlockDesc,b3.BlockDesc) CostDesc,\r\n\t\t\t\tsum(CreditorAmount - DebtorAmount ) remain\r\n\t\tfrom ACC_DocItems di\r\n\t\t\tjoin ACC_docs using(DociD)\r\n\t\t\tjoin ACC_CostCodes cc using(CostID)\r\n\t\t\tjoin ACC_blocks b1 on(cc.level1=b1.BlockID)\r\n\t\t\tjoin ACC_blocks b2 on(cc.level2=b2.BlockID)\r\n\t\t\tleft join ACC_blocks b3 on(cc.level3=b3.BlockID)\r\n\t\t\t\r\n\t\twhere CycleID=? AND cc.level2 = ? " . $where . "\r\n\t\t\r\n\t\tgroup by di.CostID", $param);
//print_r(ExceptionHandler::PopAllExceptions());
function moneyRender($row, $val)
{
$BranchID = $_POST["BranchID"];
$StartDate = $_POST["StartDate"];
$EndDate = $_POST["EndDate"];
$TafsiliID = $_POST["TafsiliID"];
$params = "IncludeRaw=true&show=true";
$params .= $BranchID != "" ? "&BranchID=" . $BranchID : "";
$params .= $TafsiliID != "" ? "&TafsiliID=" . $TafsiliID : "";
$params .= $row["level1"] != "" ? "&level1=" . $row["level1"] : "";
$params .= $row["level2"] != "" ? "&level2=" . $row["level2"] : "";
$params .= $row["level3"] != "" ? "&level3=" . $row["level3"] : "";
$params .= $StartDate != "" ? "&fromDate=" . $StartDate : "";
$params .= $EndDate != "" ? "&toDate=" . $EndDate : "";
return "<a target=_blank href='../accounting/report/flow.php?" . $params . "'>" . number_format($val) . "</a>";
}
$rpg = new ReportGenerator();
$rpg->mysql_resource = $dt;
$col = $rpg->addColumn("کد حساب", "CostDesc");
$col = $rpg->addColumn("مانده حساب", "remain", "moneyRender");
$rpg->generateReport();
die;
}
function DepositeProfit()
{
$Tafsilis = array();
$keys = array_keys($_POST);
foreach ($keys as $key) {
if (strpos($key, "chk_") !== false) {
$Tafsilis[] = substr($key, 4) * 1;
}
}
if (count($Tafsilis) == 0) {
echo Response::createObjectiveResponse(false, "هیچ ردیفی انتخاب نشده است");
die;
}
$ToDate = DateModules::shamsi_to_miladi($_POST["ToDate"]);
ComputeDepositeProfit($ToDate, $Tafsilis);
die;
}
private function date_overlap($PID)
{
$query = "\tselect count(*)\n\t\t\t\t\t\t\tfrom staff_include_history sih inner join staff s\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\ton sih.staff_id = s.staff_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t inner join persons p\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\ton s.personid = p.personid\n\t\t\t\t\t\t\t\t\twhere\n\t\t\t\t\t\t\t\t\t\t\t((sih.start_date <=:fdate AND sih.end_date>=:fdate) OR (sih.start_date>=:fdate AND sih.start_date <=:tdate)) AND\n\t\t\t\t\t\t\t\t\t\t\t p.personid =:pid ";
$whereParam = array();
$whereParam[":fdate"] = str_replace('/', '-', DateModules::shamsi_to_miladi($this->start_date));
$whereParam[":tdate"] = str_replace('/', '-', DateModules::shamsi_to_miladi($this->end_date));
$whereParam[":pid"] = $PID;
if ($this->include_history_id) {
$query .= " and include_history_id != :ihid ";
$whereParam[":ihid"] = $this->include_history_id;
}
$temp = PdoDataAccess::runquery($query, $whereParam);
if ($temp[0][0] != 0) {
parent::PushException(ER_DATE_RANGE_OVERLAP);
return false;
}
return true;
}
function Edit()
{
$whereParams = array();
$whereParams[":sid"] = $this->SalaryItemTypeID;
$whereParams[":ptype"] = $this->PersonType;
$whereParams[":FD"] = DateModules::shamsi_to_miladi($this->FromDate);
$result = parent::update("SubtractItemInfo", $this, " SalaryItemTypeID=:sid AND PersonType=:ptype AND FromDate=:FD ", $whereParams);
if (!$result) {
return false;
}
$daObj = new DataAudit();
$daObj->ActionType = DataAudit::Action_update;
$daObj->MainObjectID = $this->SalaryItemTypeID;
$daObj->SubObjectID = $this->PersonType;
$daObj->TableName = "SubtractItemInfo";
$daObj->execute();
return true;
}
private function init()
{
parent::runquery('TRUNCATE delete_payment_staff;');
parent::runquery('DROP TABLE IF EXISTS temp_cancel_limit_staff;');
parent::runquery('CREATE TABLE temp_cancel_limit_staff AS
SELECT DISTINCT s.staff_id , s.PersonID
FROM staff s
INNER JOIN writs w
ON(s.last_writ_id = w.writ_id AND s.last_writ_ver = w.writ_ver AND s.staff_id = w.staff_id )
LEFT OUTER JOIN payment_items pit
ON(s.staff_id = pit.staff_id AND ' . $this->where_clause . ')
WHERE ' . $this->staff_where . ' AND ' . '((pit.staff_id IS NOT NULL AND ' . $this->where_clause . ') OR ' . '(' . $this->writ_where . '))');
parent::runquery('ALTER TABLE temp_cancel_limit_staff ADD INDEX(staff_id);');
$this->end_date = DateModules::shamsi_to_miladi($this->year . '/' . $this->month . '/' . DateModules::DaysOfMonth($this->year, $this->month));
$this->start_date = DateModules::shamsi_to_miladi($this->year . '/' . $this->month . '/01');
$this->success_count = array();
$this->unsuccess_count = 0;
}
function MakeWhere(&$where, &$whereParam)
{
foreach ($_POST as $key => $value) {
if ($key == "excel" || $key == "OrderBy" || $key == "OrderByDirection" || $value === "" || strpos($key, "combobox") !== false) {
continue;
}
$prefix = "";
switch ($key) {
case "CustomerWage":
$prefix = "p.";
break;
case "fromRequestID":
case "toRequestID":
$prefix = "r.";
break;
case "fromReqDate":
case "toReqDate":
case "fromPartDate":
case "toPartDate":
$value = DateModules::shamsi_to_miladi($value, "-");
break;
case "fromReqAmount":
case "toReqAmount":
case "fromPartAmount":
case "toPartAmount":
$value = preg_replace('/,/', "", $value);
break;
}
if (strpos($key, "from") === 0) {
$where .= " AND " . $prefix . substr($key, 4) . " >= :{$key}";
} else {
if (strpos($key, "to") === 0) {
$where .= " AND " . $prefix . substr($key, 2) . " <= :{$key}";
} else {
$where .= " AND " . $prefix . $key . " = :{$key}";
}
}
$whereParam[":{$key}"] = $value;
}
}
function selectBases()
{
$where = " (1=1) ";
$whereParam = array();
$field = isset($_GET["fields"]) ? $_GET["fields"] : "";
if (isset($_GET["query"]) && $_GET["query"] != "") {
switch ($field) {
case "title":
$where .= " AND title LIKE :qry ";
$whereParam[":qry"] = "%" . $_GET["query"] . "%";
break;
case "BaseValue":
$where .= " AND BaseValue = :qry ";
$whereParam[":qry"] = $_GET["query"];
break;
case "typeName":
$where .= " AND i.Title like :qry ";
$whereParam[":qry"] = "%" . $_GET["query"] . "%";
break;
case "fullName":
$where .= " AND concat(pfname,' ',plname) like :qry ";
$whereParam[":qry"] = "%" . $_GET["query"] . "%";
break;
case "ExecuteDate":
$where .= " AND ExecuteDate = :qry ";
$whereParam[":qry"] = DateModules::shamsi_to_miladi($_GET["query"]);
break;
}
}
$where .= dataReader::makeOrder();
$temp = manage_bases::GetAll($where, $whereParam);
$no = count($temp);
$temp = array_slice($temp, $_GET["start"], $_GET["limit"]);
echo dataReader::getJsonData($temp, $no, $_GET["callback"]);
die;
}
private function date_overlap($PID)
{
$whereParam = array();
$whereParam[":pid"] = $PID;
$query = "select count(*) cn\n\t\t\t\t\t\t\tfrom staff_tax_history sth inner join staff s\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\ton sth.staff_id = s.staff_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t inner join persons p\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\ton s.personid = p.personid\n\t\t\t\t\t\t\t\t\twhere\n\t\t\t\t\t\t\t\t\t\t\t p.personid =:pid and payed_tax_value is null and ( sth.end_date is null or sth.end_date = '0000-00-00' )\n ";
$temp = PdoDataAccess::runquery($query, $whereParam);
if ($temp[0][0] > 0 && $this->tax_history_id == null) {
parent::PushException("لطفا ابتدا تاریخ انتهای رکورد قبل را پر نمایید.");
return false;
}
$query = "\tselect count(*)\n\t\t\t\t\t\t\tfrom staff_tax_history sth inner join staff s\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\ton sth.staff_id = s.staff_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t inner join persons p\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\ton s.personid = p.personid\n\t\t\t\t\t\t\t\t\twhere\n\t\t\t\t\t\t\t\t\t\t\t((sth.start_date <=:fdate AND sth.end_date>=:fdate) OR (sth.start_date>=:fdate AND sth.start_date <=:tdate)) AND\n\t\t\t\t\t\t\t\t\t\t\t p.personid =:pid ";
$whereParam[":fdate"] = str_replace('/', '-', DateModules::shamsi_to_miladi($this->start_date));
$whereParam[":tdate"] = str_replace('/', '-', DateModules::shamsi_to_miladi($this->end_date));
if ($this->tax_history_id) {
$query .= " and tax_history_id != :ihid ";
$whereParam[":ihid"] = $this->tax_history_id;
}
$temp = PdoDataAccess::runquery($query, $whereParam);
if ($temp[0][0] != 0) {
parent::PushException(ER_DATE_RANGE_OVERLAP);
return false;
}
return true;
}
//............................ Query ...........................................
$query = " DROP TABLE IF EXISTS temp_insure_include " ;
PdoDataAccess::runquery($query) ;
if($_POST['pay_month'] >= 1 && $_POST['pay_month'] < 7 )
$EndDay = 31 ;
else if($_POST['pay_month'] > 6 && $_POST['pay_month'] < 12 )
$EndDay = 30 ;
else if($_POST['pay_month'] == 12 )
$EndDay = 29 ;
$month_start = DateModules::shamsi_to_miladi($_POST['pay_year']."/".$_POST['pay_month']."/01") ;
$month_end = DateModules::shamsi_to_miladi($_POST['pay_year']."/".$_POST['pay_month']."/".$EndDay) ;
$next_month_start = DateModules::shamsi_to_miladi($_POST['pay_year']."/".($_POST['pay_month']+1)."/01") ;
$query = " CREATE TABLE temp_insure_include AS
SELECT DISTINCT staff_id
FROM payment_items
WHERE pay_year = ".$_POST['pay_year']." AND
pay_month = ".$_POST['pay_month']." AND
salary_item_type_id IN(".SIT_WORKER_COLLECTIVE_SECURITY_INSURE.",
".SIT_PROFESSOR_COLLECTIVE_SECURITY_INSURE.",
".SIT_STAFF_COLLECTIVE_SECURITY_INSURE.") AND
get_value <> 0 " ;
PdoDataAccess::runquery($query) ;
PdoDataAccess::runquery("ALTER TABLE temp_insure_include ADD INDEX(staff_id)") ;
PdoDataAccess::runquery("DROP TABLE IF EXISTS temp_work_sheet") ;
PdoDataAccess::runquery("CREATE TABLE temp_work_sheet AS
$evalScore += ($resES[0]['score'] == NULL ) ? 0 : $resES[0]['score'];
if (($CrrentYear - $t) == 1393)
$Eval93 = $resES[0]['score'] ;
if(($CrrentYear - $t) == 1392)
$evalScore += $Eval93;
/* if (($CrrentYear - $t) < 1394)
$evalScore += $Eval93;*/
$strat_date = DateModules::shamsi_to_miladi(($CrrentYear - $t -1) . "/01/01");
$end_date = DateModules::shamsi_to_miladi(($CrrentYear - $t -1 ) . "/12/29");
}
if ($MaxDate == 1393) {
$MinEval = $Eval93;
} else {
$MinEval = round(($evalScore / 3));
//$MinEval = round($resES[0]['score']);
}
if( $res2[0]['PersonID'] == 201199 )
{
$MinEval = 98.66 ;
}
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 MakeWhere(&$where, &$whereParam, $ForRemain = false)
{
if (!isset($_REQUEST["IncludeRaw"])) {
$where .= " AND d.DocStatus != 'RAW' ";
}
if (!empty($_REQUEST["BranchID"])) {
$where .= " AND BranchID=:b";
$whereParam[":b"] = $_REQUEST["BranchID"];
}
if (!empty($_REQUEST["GroupID"])) {
$where .= " AND b1.GroupID = :gid";
$whereParam[":gid"] = $_REQUEST["GroupID"];
}
if (!empty($_REQUEST["level1"])) {
$where .= " AND b1.BlockID = :bf1";
$whereParam[":bf1"] = $_REQUEST["level1"];
}
if (!empty($_REQUEST["level2"])) {
$where .= " AND b2.BlockID = :bf2";
$whereParam[":bf2"] = $_REQUEST["level2"];
}
if (!empty($_REQUEST["level3"])) {
$where .= " AND b3.BlockID = :bf3";
$whereParam[":bf3"] = $_REQUEST["level3"];
}
if (isset($_REQUEST["taraz"]) && isset($_REQUEST["TafsiliID"])) {
if ($_REQUEST["TafsiliID"] == "") {
$where .= " AND (di.TafsiliID=0 OR di.TafsiliID is null)";
} else {
$where .= " AND di.TafsiliID = :tid ";
$whereParam[":tid"] = $_REQUEST["TafsiliID"];
}
}
if (!empty($_REQUEST["TafsiliID"])) {
$where .= " AND (di.TafsiliID = :tid or di.TafsiliID2 = :tid)";
$whereParam[":tid"] = $_REQUEST["TafsiliID"];
}
if (!empty($_REQUEST["TafsiliType"])) {
$where .= " AND (di.TafsiliType = :tt or di.TafsiliType2 = :tt)";
$whereParam[":tt"] = $_REQUEST["TafsiliType"];
}
if (isset($_REQUEST["TafsiliID2"])) {
if ($_REQUEST["TafsiliID2"] == "") {
if (isset($_REQUEST["taraz"])) {
$where .= " AND (di.TafsiliID2=0 OR di.TafsiliID2 is null)";
}
} else {
$where .= " AND di.TafsiliID2 = :tid ";
$whereParam[":tid"] = $_REQUEST["TafsiliID2"];
}
}
if (!empty($_REQUEST["TafsiliType2"])) {
$where .= " AND di.TafsiliType2 = :tt ";
$whereParam[":tt"] = $_REQUEST["TafsiliType2"];
}
if (!empty($_REQUEST["fromLocalNo"])) {
$where .= " AND d.LocalNo >= :lo1 ";
$whereParam[":lo1"] = $_REQUEST["fromLocalNo"];
}
if (!empty($_REQUEST["toLocalNo"])) {
$where .= " AND d.LocalNo <= :lo2 ";
$whereParam[":lo2"] = $_REQUEST["toLocalNo"];
}
if (!$ForRemain && !empty($_REQUEST["fromDate"])) {
$where .= " AND d.docDate >= :q1 ";
$whereParam[":q1"] = DateModules::shamsi_to_miladi($_REQUEST["fromDate"], "-");
}
if (!$ForRemain && !empty($_REQUEST["toDate"])) {
$where .= " AND d.docDate <= :q2 ";
$whereParam[":q2"] = DateModules::shamsi_to_miladi($_REQUEST["toDate"], "-");
}
if (!empty($_REQUEST["description"])) {
$where .= " AND d.description like :des ";
$whereParam[":des"] = "%" . $_REQUEST["description"] . "%";
}
if (!empty($_REQUEST["details"])) {
$where .= " AND di.details like :det ";
$whereParam[":det"] = "%" . $_REQUEST["details"] . "%";
}
}
function selectArrearTransferWrits()
{
MakeAdvanceSearchWhere($where, $whereParam);
$state;
$state = isset($_REQUEST["return"]) ? "1" : "0";
$stateWhere = "";
$stateWhere = " w.arrear = " . $state . " AND ";
$curYear = DateModules::GetYear(DateModules::miladi_to_shamsi(DateModules::Now()));
$query = "select w.writ_id,\n w.writ_ver,\n w.staff_id,\n w.ouid,\n w.issue_date,\n w.history_only,\n w.corrective,\n w.execute_date,\n concat(wt.title,' - ', wst.title) as wt_title,\n bi_emp_state.title as emp_state_title,\n w.ref_letter_no,\n w.ref_letter_date,\n w.person_type,\n concat(p.pfname, ' ', p.plname) fullname,\n w.corrective_writ_id,\n w.correct_completed,\n\t\t\t\t\t w.view_flag\n\t\t\t\t\n\t\t\t\tfrom staff s\n \n \t\t LEFT OUTER JOIN writs w ON (w.staff_id = s.staff_id)\n LEFT OUTER JOIN writ_types wt ON ((w.writ_type_id = wt.writ_type_id) AND (w.person_type = wt.person_type))\n LEFT OUTER JOIN writ_subtypes wst ON (w.writ_subtype_id = wst.writ_subtype_id AND w.writ_type_id = wst.writ_type_id \n \t\t\t\t\t\t\t\t\t\t\tAND w.person_type = wst.person_type)\n LEFT OUTER JOIN persons p ON (s.PersonID = p.PersonID)\n LEFT OUTER JOIN org_new_units o ON (w.ouid = o.ouid)\n LEFT JOIN Basic_Info bi_emp_state on(bi_emp_state.TypeID=3 and w.emp_state=bi_emp_state.InfoID)\n LEFT JOIN payment_writs pw ON pw.writ_id = w.writ_id and pw.writ_ver = w.writ_ver and pw.staff_id = w.staff_id \n\t\t\t\t \n\t\t\twhere w.execute_date >= '" . TRANSFER_WRIT_EXE_DATE . "' AND w.state = 3 AND \n\t\t\t\t w.execute_date < '" . str_replace("/", "-", DateModules::shamsi_to_miladi($curYear . "/01/01")) . "' AND\t\t\t\t \n\t\t\t\t substr(g2j(w.execute_date),1,4) < substr(g2j(writ_recieve_date),1,4) AND \n\t\t\t\t {$stateWhere}\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n s.person_type in (" . manage_access::getValidPersonTypes() . ") AND w.cost_center_id in (" . manage_access::getValidCostCenters() . ") AND\n\t\t\t\tw.emp_state <> 0 AND\n\t\t\t\t(w.history_only=0 OR w.history_only IS NULL) AND \n\t\t\t\t(w.dont_transfer = 0 OR w.dont_transfer IS NULL) AND\n ( pw.writ_id is null ) AND\n\t\t\t\t(w.correct_completed!=" . WRIT_CORRECTING . ") AND " . $where . "\n\t\t\t\n\t\t\torder by p.plname,p.pfname,s.staff_id,w.execute_date,w.writ_id,w.writ_ver";
$temp = PdoDataAccess::runquery($query, $whereParam);
/*if($_SESSION['UserID'] == 'jafarkhani') {
echo PdoDataAccess::GetLatestQueryString();
die();
}*/
for ($i = 0; $i < count($temp); $i++) {
$temp[$i]['full_unit_title'] = manage_units::get_full_title($temp[$i]['ouid']);
}
echo dataReader::getJsonData($temp, count($temp), $_GET["callback"]);
die;
}
//.........................................................
if (!empty($_POST["FromNo"])) {
$query .= " AND ChequeNo >= :cfn";
$param[":cfn"] = $_POST["FromNo"];
}
if (!empty($_POST["ToNo"])) {
$query .= " AND ChequeNo <= :ctn";
$param[":ctn"] = $_POST["ToNo"];
}
if (!empty($_POST["FromDate"])) {
$query .= " AND PayDate >= :fd";
$param[":fd"] = DateModules::shamsi_to_miladi($_POST["FromDate"], "-");
}
if (!empty($_POST["ToDate"])) {
$query .= " AND PayDate <= :td";
$param[":td"] = DateModules::shamsi_to_miladi($_POST["ToDate"], "-");
}
if (!empty($_POST["FromAmount"])) {
$query .= " AND PayAmount >= :fa";
$param[":fa"] = preg_replace('/,/', "", $_POST["FromAmount"]);
}
if (!empty($_POST["ToAmount"])) {
$query .= " AND PayAmount <= :ta";
$param[":ta"] = preg_replace('/,/', "", $_POST["ToAmount"]);
}
if (!empty($_POST["ChequeBank"])) {
$query .= " AND ChequeBank = :cb";
$param[":cb"] = $_POST["ChequeBank"];
}
if (!empty($_POST["ChequeBranch"])) {
$query .= " AND ChequeBranch like :cb";
private function compute_salary_item5_33($writ_rec) {
//param1 : مبلغ فوق العاده تعديل سال قبل
//param2 : ضريب
$this_writ_year = substr(DateModules::miladi_to_shamsi($writ_rec['execute_date']), 0, 4);
$one_year_ago = $this_writ_year - 1;
$one_year_ago_last_day = $one_year_ago . "/12/29";
$Gone_year_ago_last_day = DateModules::shamsi_to_miladi($one_year_ago_last_day);
//آخرين حکم قبل از سال شخص را استخراج مي کند.
$prior_writObj = manage_writ::get_last_writ_by_date($writ_rec['staff_id'], $Gone_year_ago_last_day);
//مقدار قلم فوق العاده تعديل آخرين حکم سال قبل را استخراج مي کند.
//اين قلم بر اساس بخشنامه افزايش حقوق سال 87 اضافه شد.
$this->param1 = manage_writ_item::get_writSalaryItem_value($prior_writObj->writ_id, $prior_writObj->writ_ver, $prior_writObj->staff_id, SIT5_STAFF_EXTRA_ADJUST);
$this->param2 = 1.5;
$value = $this->param1 * $this->param2;
if (!($value > 0)) {
return false;
}
return $value;
}
static function calculate_mission($staff_id, $pay_year, $pay_month, $dayNo, $coef, $IncludeSalary)
{
$qry = " select person_type from staff where staff_id = " . $staff_id;
$res = PdoDataAccess::runquery($qry);
if ($res[0]['person_type'] == 1) {
$qry = " select salary_item_type_id , (pay_value ) val \n\t\t\t\t\t\tfrom hrms.payment_items\n\t\t\t\t\t\t\twhere staff_id = {$staff_id} and pay_year = {$pay_year} and pay_month = {$pay_month} and salary_item_type_id in (1,6,22) \n\t\t\t\t\t group by salary_item_type_id ";
$resItm = PdoDataAccess::runquery($qry);
if ($pay_year == 1392 && $pay_month < 7) {
for ($j = 0; $j < count($resItm); $j++) {
if ($resItm[$j]['salary_item_type_id'] == 1) {
$baseSalary = $resItm[$j]['val'];
} else {
if ($resItm[$j]['salary_item_type_id'] == 6) {
$makhsos = $resItm[$j]['val'];
}
}
}
$IncludeSalary = $baseSalary + $makhsos;
$value = ($baseSalary + $makhsos) * $dayNo / 20;
} else {
if ($pay_year >= 1392 && $pay_month > 7 || $pay_year > 1392) {
$baseValue = 3939372;
} else {
return 0;
}
$baseSalary = $makhsos = $jazb = 0;
for ($j = 0; $j < count($resItm); $j++) {
if ($resItm[$j]['salary_item_type_id'] == 1) {
$baseSalary = $resItm[$j]['val'];
} else {
if ($resItm[$j]['salary_item_type_id'] == 6) {
$makhsos = $resItm[$j]['val'];
} else {
if ($resItm[$j]['salary_item_type_id'] == 22) {
$jazb = $resItm[$j]['val'];
}
}
}
}
$IncludeSalary = $baseSalary + $makhsos + $jazb;
$sumItm = ($baseSalary + $makhsos + $jazb) / 20;
if ($sumItm > 3939372 * 20 / 100) {
$sumItm = 3939372 * 20 / 100;
}
$value = $sumItm * $dayNo;
}
} else {
// echo "-------------------------------<br>" ;
$minSalary = manage_salary_params::get_salaryParam_value("", 2, SPT_MIN_SALARY, DateModules::shamsi_to_miladi($pay_year . "/" . $pay_month . "/01"));
if ($pay_month < 7) {
$day = 31;
} else {
if ($pay_month > 6 && $pay_month < 12) {
$day = 30;
} else {
if ($pay_month == 12) {
$day = 29;
}
}
}
$param1 = $minSalary / 20;
// 4900000
$qry = " SELECT insure_include , service_include\n\t\t\t\t\t\tFROM staff_include_history\n\t\t\t\t\t\t\tWHERE staff_id = {$staff_id} and start_date <='" . DateModules::shamsi_to_miladi($pay_year . "/" . $pay_month . "/01") . "' and\n\t\t\t\t\t\t\t\t( end_date is null or end_date = '0000-00-00' or end_date >= '" . DateModules::shamsi_to_miladi($pay_year . "/" . $pay_month . "/{$day}") . "' )";
$res2 = PdoDataAccess::runquery($qry);
if ($res2[0]['service_include'] == 1) {
$param2 = manage_payment_calculation::sum_salary_items($pay_year, $pay_month, $staff_id, $res[0]['person_type'], 1);
}
if ($res2[0]['insure_include'] == 1) {
$param2 = manage_payment_calculation::sum_salary_items($pay_year, $pay_month, $staff_id, $res[0]['person_type'], 2);
}
$param3 = ($param2 - $minSalary) / 50;
$IncludeSalary = $param2;
$param4 = $param1 + $param3;
$value = ($param4 * $coef + $param4) * intval($dayNo) + $param4 * ($dayNo - intval($dayNo));
// ضریب منطقه همیشه مقدار دارد ؟؟؟؟؟
//..................
$qry = " select sum(pay_value ) pval \n\t\t\t\t\t\tfrom hrmstotal.payment_items\n\t\t\t\t\t\t\twhere staff_id = {$staff_id} and pay_year = {$pay_year} and pay_month = {$pay_month} and salary_item_type_id in (10364 , 10366 , 10367) \n\t\t\t\t\t ";
$result = PdoDataAccess::runquery($qry);
$MissionVal = $result[0]['pval'] / 20;
$value = $MissionVal > 724200 ? 724200 : $MissionVal;
}
return $value;
}
function PrepareData(){
$where = "";
$param = array();
MakeWhere($where, $param);
//.........................محاسبه کارکرد سالانه...................
$PayYear = substr( $_POST['from_date'],0,4) ;
$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'");
$MainRows = 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,
s.UnitCode,
o.ptitle unit_title ,
s.ouid,
o1.ptitle sub_unit_title,
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)
INNER JOIN org_new_units o
ON s.UnitCode = o.ouid
INNER JOIN org_new_units o1
ON s.ouid = o1.ouid
WHERE tw.end_date > \''.$year_fdate.'\' '.$where.'
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' ,$param );
//echo PdoDataAccess::GetLatestQueryString(); die();
return $MainRows;
}
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 ComputeWageOfSHekoofa($partObj)
{
$payments = LON_payments::Get(" AND RequestID=? order by PayDate", array($partObj->RequestID));
$payments = $payments->fetchAll();
//--------------- total pay months -------------
$firstPay = DateModules::miladi_to_shamsi($payments[0]["PayDate"]);
$LastPay = DateModules::miladi_to_shamsi($payments[count($payments) - 1]["PayDate"]);
$paymentPeriod = DateModules::GetDiffInMonth($firstPay, $LastPay);
//----------------------------------------------
$totalWage = 0;
$wages = array();
foreach ($payments as $row) {
$wages[] = array();
$wageindex = count($wages) - 1;
for ($i = 0; $i < $partObj->InstallmentCount; $i++) {
$monthplus = $paymentPeriod + $partObj->DelayMonths * 1 + ($i + 1) * $partObj->PayInterval * 1;
$installmentDate = DateModules::miladi_to_shamsi($payments[0]["PayDate"]);
$installmentDate = DateModules::AddToJDate($installmentDate, 0, $monthplus);
$installmentDate = DateModules::shamsi_to_miladi($installmentDate);
$jdiff = DateModules::GDateMinusGDate($installmentDate, $row["PayDate"]);
$wage = round($row["PayAmount"] / $partObj->InstallmentCount * $jdiff * $partObj->CustomerWage / 36500);
$wages[$wageindex][] = $wage;
$totalWage += $wage;
}
}
return $totalWage;
}
if (isset($_GET['task']) && $_GET['task'] == 'Compute') {
$tempArr = array();
$SDate = DateModules::shamsi_to_miladi($_REQUEST['pay_year'] . "/01/01");
$EDate = DateModules::shamsi_to_miladi($_REQUEST['pay_year'] . "/12/29");
$query = " select s.PersonID , sum(wsi.value) sv\n\n\t\t\t from staff s inner join (\n SELECT w.staff_id,\n SUBSTRING_INDEX(SUBSTRING(max(CONCAT(w.execute_date,w.writ_id,'.',w.writ_ver)),11),'.',1) writ_id,\n SUBSTRING_INDEX(max(CONCAT(w.execute_date,w.writ_id,'.',w.writ_ver)),'.',-1) writ_ver\n\n FROM writs w\n INNER JOIN staff ls ON(w.staff_id = ls.staff_id)\n\n WHERE w.person_type in (1,2,3,5) and w.execute_date <= '" . $EDate . "'\n\n GROUP BY w.staff_id\n\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t) w\t\n\t\t\t\t\t\t\t\t\ton s.staff_id = w.staff_id \n\t\t\t\t\t\t\tinner join writs wr \n\t\t\t\t\t\t\t\t\ton wr.staff_id = w.staff_id and wr.writ_id = w.writ_id and wr.writ_ver = w.writ_ver\t\t\t\n\t\t\t\t\t\t\tinner join writ_salary_items wsi\n\t\t\t\t\t\t\t\t\ton w.staff_id = wsi.staff_id and w.writ_id = wsi.writ_id and w.writ_ver = wsi.writ_ver\n\t\t\t\t\t\t\tinner join salary_item_types sit \n\t\t\t\t\t\t\t\t\ton sit.salary_item_type_id = wsi.salary_item_type_id\n\t\t\t\t\t\t\t\t\t\n\n\t\t\t\t\t\t\t\t\twhere wr.person_type in (1,2,3,5) and if(( wr.person_type =2 or wr.person_type =1 ) ,sit.retired_include =1 , sit.insure_include =1 ) and \n\t\t\t\t\t\t\t\t\t\t wr.execute_date >= '" . $SDate . "' and wr.execute_date <= '" . $EDate . "' /* AND s.PersonID = 201424*/\n\t\t\t\tgroup by s.PersonID \n\t\t\t ";
$res = PdoDataAccess::runquery($query);
for ($i = 0; $i < count($res); $i++) {
$total_year = 0;
$total_month = 0;
$total_day = 0;
$Tyear = $Tmonth = $Tday = 0;
$qry = " select execute_date , annual_effect , writ_id , writ_ver , w.onduty_year ,w.onduty_month ,\n w.onduty_day , w.person_type ,cc.AccUnitID\n\t\t\t\n\t\t\t\t from writs w inner join staff s \n\t\t\t\t on w.staff_id = s.staff_id \n\t\t\t\t\t\t\t inner join CostCenterPlan cc \n\t\t\t\t\t\t\t\ton cc.CostCenterID = w.CostCenterID \n\t\t\t\t\t\t\t\t\n\t\t\t\t where s.PersonID = " . $res[$i]['PersonID'] . " and w.execute_date <= '" . $EDate . "'\n\t\t\t\t order by execute_date ";
$res2 = PdoDataAccess::runquery($qry);
$cn = count($res2) - 1;
if ($res2[$cn]['person_type'] != 1) {
$DayToEnd = DateModules::CalculateDuration($res2[$cn]['execute_date'], DateModules::shamsi_to_miladi($_REQUEST['pay_year'] . "/12/30"));
$TotalDay = $res2[$cn]['onduty_year'] * 360 + $res2[$cn]['onduty_month'] * 30 + $res2[$cn]['onduty_day'];
$TotalDay += $DayToEnd;
//................ ذخیره مقدارنهایی
$query = " insert into YearRedemption (PersonID,duration,value,year,writ_id,writ_ver) values \n\t\t\t (" . $res[$i]['PersonID'] . "," . round($TotalDay / 360, 2) . "," . $res[$i]['sv'] . " ," . $_REQUEST['pay_year'] . "," . $res2[$cn]['writ_id'] . "," . $res2[$cn]['writ_ver'] . "); ";
} else {
//.......................
$j = 0;
for ($j = 0; $j < count($res2); $j++) {
$first_date = $res2[$j]['execute_date'];
$last_date = $j + 1 < count($res2) ? $res2[$j + 1]['execute_date'] : $EDate;
$diff = strtotime($last_date) - strtotime($first_date);
$diff = floor($diff / (60 * 60 * 24));
$year = floor($diff / 365.25);
$month = floor(($diff - floor($diff / 365.25) * 365.25) / 30.4375);
$day = floor($diff - floor($diff / 365.25) * 365.25 - floor(($diff - floor($diff / 365.25) * 365.25) / 30.4375) * 30.4375);
$where.= " AND w.staff_id >= ".$_POST['from_staff_id'] ;
}
if(!empty($_POST['to_staff_id'])){
$where.= " AND w.staff_id <= ".$_POST['to_staff_id'] ;
}
//....... تاریخ -----
if(!empty($_POST['from_execute_date'])){
$where.= " AND w.execute_date >= '".DateModules::shamsi_to_miladi($_POST['from_execute_date'])."'" ;
}
if(!empty($_POST['to_execute_date'])){
$where.= " AND w.execute_date <= '".DateModules::shamsi_to_miladi($_POST['to_execute_date'])."'" ;
}
//.......پایه .......
if(!empty($_POST['from_cur_group'])){
$where.= " AND w.base >= ".$_POST['from_cur_group'] ;
}
if(!empty($_POST['to_cur_group'])){
$where.= " AND w.base <= ".$_POST['to_cur_group'] ;
}
//........واحد محل خدمت.....
if(!empty($_POST['ouid'])){
$empWhere .= ")";
$whereDate = "";
$whereW = "";
if (!empty($_POST['FDATE:72'])) {
$whereDate = " AND tbl2.execute_date>='" . DateModules::shamsi_to_miladi($_POST['FDATE:72']) . "'";
$whereW = " AND w.execute_date >= '" . DateModules::shamsi_to_miladi($_POST['FDATE:72']) . "' ";
}
if (!empty($_POST['TDATE:72'])) {
$whereDate .= " AND tbl2.execute_date<='" . DateModules::shamsi_to_miladi($_POST['TDATE:72']) . "'";
$whereW .= " AND w.execute_date <= '" . DateModules::shamsi_to_miladi($_POST['TDATE:72']) . "'";
}
$query = "select tbl2.emp_mode as '9802',tbl3.Title as '980',count(tbl0.PersonID) as '983',tbl0.person_type as '10572',\r\n tbl1.person_type as '10582',tbl2.emp_mode as '11162',tbl1.person_type as '11382',tbl8.Title as '1138',\r\n tbl2.emp_mode as '11392',tbl1.person_type as '11402',tbl2.emp_mode as '11412',tbl1.person_type as '11432',\r\n tbl0.person_type as '11442',tbl2.emp_mode as '11452'\r\n \r\n from persons as tbl0\r\n left join staff as tbl1 on(tbl0.PersonID=tbl1.PersonID )\r\n left join (SELECT staff_id,\r\n SUBSTRING_INDEX(SUBSTRING(max_execute_date,11),'.',1) writ_id,\r\n SUBSTRING_INDEX(max_execute_date,'.',-1) writ_ver\r\n FROM (SELECT w.staff_id,\r\n max( CONCAT(w.execute_date,w.writ_id,'.',w.writ_ver) ) max_execute_date\r\n FROM writs w\r\n INNER JOIN staff ls\r\n ON(w.staff_id = ls.staff_id)\r\n WHERE \r\n w.history_only = 0 and w.person_type = " . $_POST['SELECT:196'] . " " . $whereW . " \r\n GROUP BY w.staff_id ) med) as mtbl2 on(tbl1.staff_id=mtbl2.staff_id)\r\n\r\n inner join writs as tbl2 on mtbl2.staff_id = tbl2.staff_id and\r\n mtbl2.writ_id = tbl2.writ_id and mtbl2.writ_ver = tbl2.writ_ver\r\n\r\n left join Basic_Info as tbl3 on(tbl3.TypeID=4 AND tbl3.InfoID=tbl2.emp_mode)\r\n left join Basic_Info as tbl4 on(tbl4.TypeID=4 AND tbl4.InfoID=tbl2.emp_mode)\r\n left join Basic_Info as tbl5 on(tbl5.TypeID=4 AND tbl5.InfoID=tbl2.emp_mode)\r\n left join Basic_Info as tbl6 on(tbl6.TypeID=4 AND tbl6.InfoID=tbl2.emp_mode)\r\n left join Basic_Info as tbl7 on(tbl7.TypeID=4 AND tbl7.InfoID=tbl2.emp_mode)\r\n left join Basic_Info as tbl8 on(tbl8.TypeID=16 AND tbl8.InfoID=tbl1.person_type)\r\n left join Basic_Info as tbl9 on(tbl9.TypeID=16 AND tbl9.InfoID=tbl1.person_type)\r\n left join Basic_Info as tbl10 on(tbl10.TypeID=16 AND tbl10.InfoID=tbl1.person_type)\r\n left join Basic_Info as tbl11 on(tbl11.TypeID=16 AND tbl11.InfoID=tbl1.person_type)\r\n left join Basic_Info as tbl12 on(tbl12.TypeID=16 AND tbl12.InfoID=tbl0.person_type)\r\n left join Basic_Info as tbl13 on(tbl13.TypeID=16 AND tbl13.InfoID=tbl0.person_type)\r\n\r\n where 1=1 AND tbl2.emp_mode in " . $empWhere . " \r\n AND tbl1.person_type = " . $_POST['SELECT:196'] . " " . $whereDate . "\r\n \r\n group by tbl2.emp_mode,tbl1.person_type\r\n order by tbl13.Title,tbl7.Title ";
$data = PdoDataAccess::runquery($query);
define("person_type", "11402");
//tbl0.person_type
define("person_type_title", "1138");
// tbl7.Title-tbl0.person_type
define("emp_mode", "9802");
//tbl2.emp_mode
define("emp_mode_title", "980");
//tbl3.Title-tbl2.emp_mode
define("CountPID", "983");
//persons.PersonID
$record = "";
for ($i = 0; $i < count($data); $i++) {
$record .= "<tr><td>" . $data[$i][person_type_title] . "</td><td>" . $data[$i][emp_mode_title] . "</td>\r\n\t\t\t\t\t<td><a href='/HumanResources/reports/FullInformation.php?" . "Q0=15&pt=" . $data[$i][person_type] . "&emp_mode=" . $data[$i][emp_mode] . "&FDATE=" . DateModules::shamsi_to_miladi($_POST["FDATE:72"]) . "&TDATE=" . DateModules::shamsi_to_miladi($_POST["TDATE:72"]) . "' target = '_blank' >" . $data[$i][CountPID] . "</a></td></tr>";
}
$tags = array('<!--record-->' => $record, '<!--now-->' => DateModules::shNow());
$content = file_get_contents("../../reports/group_information.htm");
$content = str_replace(array_keys($tags), array_values($tags), $content);
echo $content;
