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 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 saveStaffData()
{
$obj = new manage_staff();
PdoDataAccess::FillObjectByArray($obj, $_POST);
if (isset($_POST["work_start_date"])) {
$obj->work_start_date = DateModules::Shamsi_to_Miladi($_POST["work_start_date"]);
}
if (isset($_POST["ProfWorkStart"])) {
$obj->ProfWorkStart = DateModules::Shamsi_to_Miladi($_POST["ProfWorkStart"]);
}
if (isset($_POST["retired_date"])) {
$obj->retired_date = DateModules::Shamsi_to_Miladi($_POST["retired_date"]);
}
if (isset($_POST["last_retired_pay"])) {
$obj->last_retired_pay = DateModules::Shamsi_to_Miladi($_POST["last_retired_pay"]);
}
if (!isset($_POST['Over25'])) {
$obj->Over25 = 0;
}
$obj->sum_paied_pension = $obj->sum_paied_pension > 0 ? $obj->sum_paied_pension : "0";
$return = $obj->EditStaff();
$key = $obj->staff_id . "," . $obj->PersonID . "," . $obj->person_type;
echo $return ? Response::createObjectiveResponse("true", $key) : Response::createObjectiveResponse(false, ExceptionHandler::GetExceptionsToString("\n"));
die;
}
function saveEducData()
{
//........ Fill object ..............
$obj = new manage_person_education();
PdoDataAccess::FillObjectByArray($obj, $_POST);
$obj->PersonID = $_POST['PersonID'];
$obj->doc_date = DateModules::Shamsi_to_Miladi($obj->doc_date);
$obj->certificated = empty($obj->certificated) ? "0" : $obj->certificated;
//....................................
if ($_POST['sfid'] == "-1") {
$obj->sfid = PDONULL;
}
if (!isset($_POST['sbid'])) {
$obj->sbid = PDONULL;
}
if ($_POST['university_id'] == "-1") {
$obj->university_id = PDONULL;
}
if ($_POST['country_id'] == "-1") {
$obj->country_id = PDONULL;
}
if (empty($_POST["row_no"])) {
$return = $obj->AddEducation();
} else {
$return = $obj->EditEducation();
}
echo $return ? Response::createObjectiveResponse(true, $obj->row_no) : Response::createObjectiveResponse(false, ExceptionHandler::GetExceptionsToString("\n"));
die;
}
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;
}
function GetTreeNodes()
{
$LetterID = $_REQUEST["LetterID"];
$creator = PdoDataAccess::runquery("\r\n\t\tselect PersonID,\r\n\t\t\tconcat_ws(' ',fname, lname,CompanyName) text, \r\n\t\t\t'true' as leaf, 'true' expanded,'user' iconCls,\r\n\t\t\tl.RegDate\r\n\t\tfrom OFC_letters l join BSC_persons p using(PersonID) where LetterID=?", array($LetterID));
$index = 1;
$returnArray = array();
$refArray = array();
$creator[0]["id"] = $index++;
$creator[0]["text"] .= " [ " . substr($creator[0]["RegDate"], 10, 6) . " " . DateModules::miladi_to_shamsi($creator[0]["RegDate"]) . "\t]";
$returnArray[] = $creator[0];
$refArray[$creator[0]["PersonID"]] =& $returnArray[count($returnArray) - 1];
$nodes = PdoDataAccess::runquery("\r\n\t\tselect FromPersonID ,ToPersonID, SendDate,\r\n\t\t\tconcat_ws(' ',fname, lname,CompanyName,' - ',InfoDesc) text, \r\n\t\t\tconcat('<b>توضیحات ارجاع : </b>' ,replace(SendComment,'\n','<br>')) qtip,\r\n\t\t\t'true' as leaf, 'true' expanded,'user' iconCls\r\n\t\tfrom OFC_send \r\n\t\t\tjoin BSC_persons p on(ToPersonID=PersonID) \r\n\t\t\tjoin BaseInfo bf on(bf.TypeID=12 AND SendType=InfoID)\r\n\t\twhere LetterID=?\r\n\t\torder by SendID", array($LetterID));
foreach ($nodes as $row) {
$row["id"] = $index++;
$row["text"] .= " [ " . substr($row["SendDate"], 10, 6) . " " . DateModules::miladi_to_shamsi($row["SendDate"]) . " ]";
$parentNode =& $refArray[$row["FromPersonID"]];
if (!isset($parentNode["children"])) {
$parentNode["children"] = array();
$parentNode["leaf"] = "false";
}
$lastIndex = count($parentNode["children"]);
$parentNode["children"][$lastIndex] = $row;
$refArray[$row["ToPersonID"]] =& $parentNode["children"][$lastIndex];
}
$str = json_encode($returnArray);
$str = str_replace('"children"', 'children', $str);
$str = str_replace('"leaf"', 'leaf', $str);
$str = str_replace('"iconCls"', 'iconCls', $str);
$str = str_replace('"text"', 'text', $str);
$str = str_replace('"id"', 'id', $str);
$str = str_replace('"true"', 'true', $str);
$str = str_replace('"false"', 'false', $str);
echo $str;
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 duty_year_month_day($staff_id = "", $personID = "", $toDate) {
if ($staff_id == "" && $personID = "") {
PdoDataAccess::PushException("يکي از دو پارامتر staff_id و PersonID بايد فرستاده شود");
return false;
}
$query = "select w.execute_date,
w.contract_start_date ,
w.contract_end_date ,
w.person_type ,
w.onduty_year ,
w.onduty_month ,
w.onduty_day ,
w.annual_effect
from writs as w";
if ($personID != "")
$query .= " join staff as s using(staff_id) where s.PersonID=" . $personID;
else if ($staff_id != "")
$query .= " where w.staff_id = $staff_id";
$query .= " AND (w.history_only != " . HISTORY_ONLY . " OR w.history_only is null) AND w.execute_date <= '$toDate'
order by w.execute_date DESC,w.writ_id DESC,w.writ_ver DESC
limit 1";
$temp = PdoDataAccess::runquery($query);
if (count($temp) == 0)
return array("year" => 0, "month" => 0, "day" => 0);
$writ_rec = $temp[0];
$temp_duration = 0;
if (DateModules::CompareDate($toDate, $writ_rec['execute_date']) >= 0)
$temp_duration = DateModules::GDateMinusGDate($toDate, $writ_rec['execute_date']);
if ($writ_rec['annual_effect'] == HALF_COMPUTED)
$temp_duration *= 0.5;
else if ($writ_rec['annual_effect'] == DOUBLE_COMPUTED)
$temp_duration *= 2;
else if ($writ_rec['annual_effect'] == NOT_COMPUTED)
$temp_duration = 0;
$prev_writ_duration = DateModules::ymd_to_days($writ_rec['onduty_year'], $writ_rec['onduty_month'], $writ_rec['onduty_day']);
$duration = $prev_writ_duration + $temp_duration;
$return = array();
DateModules::day_to_ymd($duration, $return['year'], $return['month'], $return['day']);
return $return;
}
function AccDocFlow()
{
$CostID = $_REQUEST["CostID"];
$CurYear = substr(DateModules::shNow(), 0, 4);
$temp = PdoDataAccess::runquery_fetchMode("\r\n\t\tselect d.DocDate,\r\n\t\t\td.description,\r\n\t\t\tdi.DebtorAmount,\r\n\t\t\tdi.CreditorAmount,\r\n\t\t\tdi.details\r\n\t\tfrom ACC_DocItems di join ACC_docs d using(DocID)\r\n\t\tleft join ACC_tafsilis t1 on(t1.TafsiliType=1 AND di.TafsiliID=t1.TafsiliID)\r\n\t\tleft join ACC_tafsilis t2 on(t2.TafsiliType=1 AND di.TafsiliID2=t2.TafsiliID)\r\n\t\twhere CycleID=:year AND CostID=:cid AND (t1.ObjectID=:pid or t2.ObjectID=:pid)\r\n\t\t\tAND DocStatus in('CONFIRM','ARCHIVE')\r\n\t\torder by DocDate\r\n\t", array(":year" => $CurYear, ":pid" => $_SESSION["USER"]["PersonID"], ":cid" => $CostID));
//print_r(ExceptionHandler::PopAllExceptions());
$count = $temp->rowCount();
$temp = PdoDataAccess::fetchAll($temp, $_GET["start"], $_GET["limit"]);
echo dataReader::getJsonData($temp, $count, $_GET["callback"]);
die;
}
public static function MakeHeader($title, $fromDate = "", $toDate = "", $pageNumber = "", $returnMode = false, $excel = false)
{
$str = "\r\n\t\t\t<div style='text-align: right;float:right;width:20%;' align=right><img width='60px' style='padding:2px' \r\n\t\t\t\t" . ($excel ? "" : "src='/framework/icons/logo.jpg'") . "></div>" . "<div style='float:right;font-family:b titr;font-size:15px;width:60%' align=center>" . "<br>" . SoftwareName . "<br>" . $title . "</div>\r\n\r\n\t\t\t<div style='text-align: left;float:right;width:20%;font-family:tahoma;font-size:11px' align=left>\r\n\t\t\t<br>تاریخ تهیه گزارش : " . DateModules::shNow() . "<br>";
$str .= !empty($fromDate) ? "<br>گزارش از تاریخ : " . $fromDate . ($toDate != "" ? " - " . $toDate : "") : "";
$str .= $pageNumber != "" ? "<br>شماره صفحه :" . $pageNumber : "";
$str .= "</div>\r\n\t\t<br>";
if ($returnMode) {
return $str;
} else {
echo $str;
}
}
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 SaveJob()
{
$obj = new manage_person_job();
PdoDataAccess::FillObjectByJsonData($obj, $_POST["record"]);
$obj->FromDate = DateModules::Shamsi_to_Miladi($obj->FromDate);
$obj->ToDate = DateModules::Shamsi_to_Miladi($obj->ToDate);
if (empty($obj->RowNO)) {
$return = $obj->AddJobHistory();
} else {
$return = $obj->EditJobHistory();
}
echo $return ? Response::createObjectiveResponse(true, $obj->RowNO) : Response::createObjectiveResponse(false, ExceptionHandler::GetExceptionsToString("\n"));
die;
}
function saveTaxHis()
{
$obj = new manage_staff_tax();
PdoDataAccess::FillObjectByArray($obj, $_POST);
$obj->start_date = DateModules::Shamsi_to_Miladi($_POST['start_date']);
$obj->end_date = DateModules::Shamsi_to_Miladi($_POST['end_date']);
if (!empty($_POST['tax_history_id'])) {
$return = $obj->EditStaffTaxHistory($_POST['PersonID']);
} else {
$return = $obj->SaveStaffTaxHistory($_POST['PersonID']);
}
echo $return ? Response::createObjectiveResponse(true, $_POST['PersonID']) : Response::createObjectiveResponse(false, ExceptionHandler::GetExceptionsToString("\n"));
die;
}
function OnBeforeInsert()
{
if ($this->duration_year == 0 && $this->duration_month == 0 && $this->duration_day == 0) {
$duration = DateModules::getDateDiff($this->to_date, $this->from_date);
$this->group_duration_year = $this->retired_duration_year = $this->duration_year = floor($duration / 360);
$this->group_duration_month = $this->retired_duration_month = $this->duration_month = floor(($duration - $this->duration_year * 360) / 30);
$this->group_duration_day = $this->retired_duration_day = $this->duration_day = $duration - $this->duration_year * 360 - $this->duration_month * 30;
}
if ($this->emp_state == EMP_STATE_NONE_GOVERNMENT) {
$this->retired_duration_year = 0;
$this->retired_duration_month = 0;
$this->retired_duration_day = 0;
}
return true;
}
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;
}
static function get_rial_coefs($from_j_year, $to_j_year, $person_type)
{
$query = " SELECT *\r\n FROM salary_params\r\n\t\t\t\t\t\t\t WHERE param_type='" . SPT_RIAL_COEF . "' AND person_type = " . $person_type . "\r\n\t\t\t\t\t\t\t\tORDER BY from_date ";
$res = parent::runquery($query);
$rial_coefs = array();
for ($i = 0; $i < count($res); $i++) {
$cur_j_date = '01/01/' . $from_j_year;
$cur_g_date = DateModules::Shamsi_to_Miladi($cur_j_date);
while ($res[$i]['from_date'] <= $cur_g_date && $res[$i]['to_date'] >= $cur_g_date && $from_j_year <= $to_j_year) {
$rial_coefs[$from_j_year] = $res[$i]['value'];
$from_j_year++;
$cur_j_date = '01/01/' . $from_j_year;
$cur_g_date = DateModules::Shamsi_to_Miladi($cur_j_date);
}
}
return $rial_coefs;
}
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 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 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 GetSavingLoanInfo()
{
if (isset($_SESSION["USER"]["portal"])) {
$PersonID = $_SESSION["USER"]["PersonID"];
} else {
$PersonID = $_REQUEST["PersonID"];
}
//----------- check for all docs confirm --------------
/*$dt = PdoDataAccess::runquery("select group_concat(distinct LocalNo) from ACC_docs
join ACC_DocItems using(DocID) join ACC_tafsilis t using(TafsiliType,TafsiliID)
where TafsiliType=" . TAFTYPE_PERSONS . "
AND ObjectID = ? AND CostID in(" . COSTID_saving . ")
AND DocType not in(" . DOCTYPE_STARTCYCLE . "," . DOCTYPE_ENDCYCLE . ")
AND DocStatus not in('CONFIRM','ARCHIVE')", array($PersonID));
if(count($dt) > 0 && $dt[0][0] != "")
{
$msg = "اسناد با شماره های [" . $dt[0][0] . "] تایید نشده اند.";
echo dataReader::getJsonData(array(), 0, $_GET["callback"], $msg);
die();
}*/
//------------ get sum of savings ----------------
$dt = PdoDataAccess::runquery("\n\t\tselect DocDate,sum(CreditorAmount-DebtorAmount) amount\n\t\tfrom ACC_DocItems di\n\t\t\tjoin ACC_docs d using(DocID)\n\t\t\tjoin ACC_tafsilis t using(TafsiliType,TafsiliID)\n\t\t\n\t\twhere TafsiliType=" . TAFTYPE_PERSONS . " \n\t\t\tAND ObjectID = ?\n\t\t\tAND CostID in(" . COSTID_saving . ")\n\t\t\tAND DocType not in(" . DOCTYPE_STARTCYCLE . "," . DOCTYPE_ENDCYCLE . ")\n\t\t\tAND BranchID=" . $_SESSION["accounting"]["BranchID"] . "\n\t\tgroup by DocDate\n\t\torder by DocDate", array($PersonID));
if (count($dt) == 0) {
$msg = "شخص مورد نظر فاقد حساب پس انداز می باشد";
echo dataReader::getJsonData(array(), 0, $_GET["callback"], $msg);
die;
}
//------------ get the Deposite amount -------------
$totalAmount = 0;
$totalDays = 0;
for ($i = 1; $i < count($dt); $i++) {
$days = DateModules::GDateMinusGDate($dt[$i]["DocDate"], $dt[$i - 1]["DocDate"]);
$totalDays += $days;
$totalAmount += $dt[$i - 1]["amount"] * $days;
}
$days = DateModules::GDateMinusGDate(DateModules::Now(), $dt[$i - 1]["DocDate"]);
$totalDays += $days;
$totalAmount += $dt[$i - 1]["amount"] * $days;
$average = round($totalAmount / $totalDays);
$returnArray = array("PersonID" => $PersonID, "FirstDate" => DateModules::miladi_to_shamsi($dt[0]["DocDate"]), "AverageAmount" => $average, "TotalMonths" => floor($totalDays / 30.5));
echo dataReader::getJsonData($returnArray, 1, $_GET["callback"]);
die;
}
function saveDevData()
{
$obj = new manage_person_devotion();
PdoDataAccess::FillObjectByArray($obj, $_POST);
$obj->PersonID = $_POST['PersonID'];
$obj->from_date = DateModules::Shamsi_to_Miladi($_POST["from_date"]);
$obj->to_date = DateModules::Shamsi_to_Miladi($_POST["to_date"]);
$obj->letter_date = DateModules::Shamsi_to_Miladi($_POST["letter_date"]);
$obj->amount = empty($obj->amount) ? "0" : $obj->amount;
$obj->continous = !empty($obj->continous) ? $obj->continous : 0;
$obj->enlisted = !empty($obj->enlisted) ? $obj->enlisted : 0;
if (empty($_POST["devotion_row"])) {
$return = $obj->AddDevotion();
} else {
$return = $obj->EditDevotion();
}
echo $return ? Response::createObjectiveResponse(true, $obj->devotion_row) : Response::createObjectiveResponse(false, ExceptionHandler::GetExceptionsToString("\n"));
die;
}
function saveMiscDoc()
{
//........ Fill object ..............
$obj = new manage_person_misc_doc();
$arr = get_object_vars($obj);
$KeyArr = array_keys($arr);
for ($i = 0; $i < count($arr); $i++) {
eval("\$obj->" . $KeyArr[$i] . " = (isset(\$_POST) && isset(\$_POST['" . $KeyArr[$i] . "'])) \r\n\t\t\t? \$_POST['" . $KeyArr[$i] . "'] : '';");
}
$obj->PersonID = $_POST['PersonID'];
$obj->doc_date = DateModules::Shamsi_to_Miladi($obj->doc_date);
//....................................
if (empty($_POST["row_no"])) {
$return = $obj->AddMiscDoc();
} else {
$return = $obj->EditMiscDoc();
}
echo $return ? Response::createObjectiveResponse(true, $obj->row_no) : Response::createObjectiveResponse(false, ExceptionHandler::GetExceptionsToString("\n"));
die;
}
function onCalcField(&$rec)
{
if(empty($rec['work_sheet'])) {
$DT = PdoDataAccess::runquery('SELECT MAX(DISTINCT pai.param4) work_days,
s.person_type
FROM payment_items pai
INNER JOIN salary_item_types sit
ON (pai.salary_item_type_id = sit.salary_item_type_id)
INNER JOIN staff s
ON (pai.staff_id = s.staff_id)
WHERE pai.pay_year = '.$_POST['pay_year'].' AND
pai.pay_month = '.$_POST['pay_month'].' AND
pai.payment_type = '.$_POST['PayType'].' AND
pai.staff_id = '.$rec['staff_id'].' AND
sit.compute_place = '.SALARY_ITEM_COMPUTE_PLACE_WRIT.'
GROUP BY s.staff_id') ;
if( $DT[0]['work_days'] > 1 )
$DT[0]['work_days'] = 1 ;
$work_days = $DT[0]['work_days'];
$person_type = $DT[0]['person_type'];
$work_days *= DateModules::DaysOfMonth($rec['pay_month'],$rec['pay_year']);
$rec['work_sheet'] = $work_days;
}
if(!empty($rec['work_sheet']))
$rec['work_sheet'] = round($rec['work_sheet']) ;
if ($rec['person_type'] == HR_WORKER) {
$rec['daily_fee'] = $rec['monthly_fee'] / $rec['work_sheet'];
} else {
$rec['daily_fee'] = $rec['monthly_fee'] / DateModules::DaysOfMonth($rec['pay_month'],$rec['pay_year']);
}
$rec['monthly_premium'] = $rec['monthly_insure_include'] - $rec['monthly_fee'];
$rec['other_gets'] = $rec['gets'] - $rec['worker_insure_include'];
return true;
}
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;
}