function selectEqualizations()
{
$dt = PdoDataAccess::runquery_fetchMode("select EqualizationID,RegDate,BankID,BankDesc \r\n\t\tfrom ACC_equalizations left join ACC_banks using(BankID)");
$temp = PdoDataAccess::fetchAll($dt, $_GET["start"], $_GET["limit"]);
echo dataReader::getJsonData($temp, $dt->rowCount(), $_GET["callback"]);
die;
}
function selectDeposites()
{
$temp = PdoDataAccess::runquery_fetchMode("\r\n\t\tselect TafsiliID,CostID,concat_ws('-',b1.BlockDesc,b2.BlockDesc) CostDesc,\r\n\t\t\tsum(CreditorAmount-DebtorAmount) amount,TafsiliDesc\r\n\t\tfrom ACC_DocItems \r\n\t\t\tjoin ACC_docs using(DocID)\r\n\t\t\tjoin ACC_CostCodes cc using(CostID)\r\n\t\t\tleft join ACC_blocks b1 on(b1.BlockID=cc.level1)\r\n\t\t\tleft join ACC_blocks b2 on(b2.BlockID=cc.level2)\r\n\t\t\tjoin ACC_tafsilis using(TafsiliID)\r\n\t\t\r\n\t\twhere /*DocStatus != 'RAW'*/ 1=1\r\n\t\t\tAND CostID in(" . COSTID_ShortDeposite . "," . COSTID_LongDeposite . ")\r\n\t\t\tAND CycleID=" . $_SESSION["accounting"]["CycleID"] . "\r\n\t\t\tAND BranchID=" . $_SESSION["accounting"]["BranchID"] . "\r\n\t\tgroup by TafsiliID,CostID");
$dt = PdoDataAccess::fetchAll($temp, $_GET["start"], $_GET["limit"]);
//echo PdoDataAccess::GetLatestQueryString();
echo dataReader::getJsonData($dt, $temp->rowCount(), $_GET["callback"]);
die;
}
function GetSavingFlow()
{
$query = "select d.*,di.*\n\t\tfrom ACC_DocItems di\n\t\t\tjoin ACC_docs d using(DocID)\n\t\t\tjoin ACC_tafsilis t on(di.TafsiliID=t.TafsiliID)\n\t\t\tjoin BSC_persons p on(t.ObjectID=p.PersonID)\n\t\twhere d.CycleID=:c AND d.BranchID=:b AND \n\t\t\tdi.CostID=:cost AND di.TafsiliType = :t AND p.PersonID=:p";
$param = array(":c" => $_SESSION["accounting"]["CycleID"], ":b" => $_SESSION["accounting"]["BranchID"], ":cost" => COSTID_saving, ":t" => TAFTYPE_PERSONS, ":p" => $_REQUEST["PersonID"]);
$temp = PdoDataAccess::runquery_fetchMode($query, $param);
$no = $temp->rowCount();
$temp = PdoDataAccess::fetchAll($temp, $_GET["start"], $_GET["limit"]);
echo dataReader::getJsonData($temp, $no, $_GET["callback"]);
die;
}
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;
}
function SelectAll()
{
$where = "1=1";
if (!empty($_REQUEST["TaskStatus"])) {
$where .= " AND TaskStatus in(" . ($_REQUEST["TaskStatus"] == "RAW" ? "'RAW'" : "'DONE','RESPONSE'") . ")";
}
$res = PdoDataAccess::runquery_fetchMode("\r\n\t\tselect\tt.* , \r\n\t\t\t\tSysName,\r\n\t\t\t\tconcat_ws(' ',fname,lname,CompanyName) RegPersonName\r\n\t\t\t\t\r\n\t\tfrom FRW_tasks t\r\n\t\tjoin BSC_persons on(RegPersonID = PersonID)\r\n\t\tjoin FRW_systems using(SystemID)\r\n\t\t\r\n\t\twhere {$where} order by FIELD(TaskStatus,'RAW') desc,ifnull(DoneDate,CreateDate) desc");
$cnt = $res->rowCount();
$res = PdoDataAccess::fetchAll($res, $_GET["start"], $_GET["limit"]);
echo dataReader::getJsonData($res, $cnt, $_GET["callback"]);
die;
}
function SelectBlocks()
{
if (!empty($_REQUEST["PreLevel"])) {
$level = $_REQUEST['level'];
$query = "select b{$level}.* from ACC_CostCodes \n\t\t\t join ACC_blocks b1 on(b1.BlockID=level1)\n\t\t\tleft join ACC_blocks b2 on(b2.BlockID=level2)\n\t\t\tleft join ACC_blocks b3 on(b3.BlockID=level3)\n\t\t\n\t\twhere b{$level}.BlockID is not null AND b" . ($level * 1 - 1) . ".BlockID=:b";
$param = array(":b" => $_REQUEST["PreLevel"]);
if (isset($_REQUEST['fields']) && isset($_REQUEST['query'])) {
$field = $_REQUEST['fields'];
$query .= ' and ' . $field . ' like :' . $field;
$param[':' . $field] = '%' . $_REQUEST['query'] . '%';
}
$query .= " group by b{$level}.BlockID";
$list = PdoDataAccess::runquery_fetchMode($query, $param);
$count = $list->rowCount();
if (isset($_GET["start"])) {
$list = PdoDataAccess::fetchAll($list, $_GET["start"], $_GET["limit"]);
} else {
$list = $list->fetchAll();
}
echo dataReader::getJsonData($list, $count, $_GET['callback']);
die;
}
$where = "IsActive='YES' AND LevelID=:LevelID";
$param = array();
$param[':LevelID'] = $_REQUEST['level'];
if (isset($_REQUEST['fields']) && isset($_REQUEST['query'])) {
$field = $_REQUEST['fields'];
$where .= ' and ' . $field . ' like :' . $field;
$param[':' . $field] = '%' . $_REQUEST['query'] . '%';
}
if (!isset($_REQUEST['fields']) && !empty($_REQUEST['query'])) {
$where .= " AND ( BlockDesc like :qu or BlockCode like :qu)";
$param[':qu'] = '%' . $_REQUEST['query'] . '%';
}
if (!empty($_REQUEST['BlockID'])) {
$where .= " AND BlockID=:b";
$param[':b'] = $_REQUEST['BlockID'];
}
$where .= " order by BlockCode*1";
$list = ACC_blocks::GetAll($where, $param);
$count = $list->rowCount();
if (isset($_GET["start"]) && !isset($_GET["All"])) {
$list = PdoDataAccess::fetchAll($list, $_GET["start"], $_GET["limit"]);
} else {
$list = $list->fetchAll();
}
echo dataReader::getJsonData($list, $count, $_GET['callback']);
die;
}
function selectIncomeCheques()
{
$where = "1=1";
$param = array();
MakeWhere($where, $param);
$query = "\r\n\t\tselect i.*,\r\n\t\t\tcase when i.CostID is null then group_concat(t2.TafsiliDesc SEPARATOR '<br>')\r\n\t\t\t\telse t1.TafsiliDesc end fullname,\r\n\t\t\tcase when i.CostID is null then group_concat(concat_ws('-', bb1.blockDesc, bb2.blockDesc) SEPARATOR '<br>') \r\n\t\t\t\telse concat_ws('-', b1.blockDesc, b2.blockDesc, b3.blockDesc) end CostDesc,\r\n\t\t\tb.BankDesc, \r\n\t\t\tt3.TafsiliDesc ChequeStatusDesc,\r\n\t\t\tt.docs\r\n\t\t\t\r\n\t\tfrom ACC_IncomeCheques i\r\n\t\t\tleft join ACC_tafsilis t1 using(TafsiliID)\r\n\t\t\tleft join ACC_CostCodes cc using(CostID)\r\n\t\t\tleft join ACC_blocks b1 on(cc.level1=b1.BlockID)\r\n\t\t\tleft join 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\t\tleft join LON_BackPays bp using(IncomeChequeID)\r\n\t\t\tleft join LON_requests using(RequestID)\r\n\t\t\tleft join LON_loans l using(LoanID)\r\n\t\t\tleft join ACC_CostCodes cc2 on(cc2.level1=" . BLOCKID_LOAN . " AND cc2.level2=l.blockID)\r\n\t\t\tleft join ACC_blocks bb1 on(cc2.level1=bb1.BlockID)\r\n\t\t\tleft join ACC_blocks bb2 on(cc2.level2=bb2.BlockID)\r\n\t\t\tleft join ACC_tafsilis t2 on(t2.TafsiliType=" . TAFTYPE_PERSONS . " AND t2.ObjectID=LoanPersonID)\r\n\t\t\r\n\t\tleft join ACC_banks b on(ChequeBank=BankID)\r\n\t\tleft join ACC_tafsilis t3 on(t3.TafsiliType=" . TAFTYPE_ChequeStatus . " AND t3.TafsiliID=ChequeStatus)\r\n\t\tleft join (\r\n\t\t\tselect SourceID, group_concat(distinct LocalNo) docs\r\n\t\t\tfrom ACC_DocItems join ACC_docs using(DocID)\r\n\t\t\twhere SourceType='" . DOCTYPE_INCOMERCHEQUE . "' \r\n\t\t\tgroup by SourceID\r\n\t\t)t on(i.IncomeChequeID=t.SourceID)\r\n\t\t\r\n\t\twhere " . $where . " \r\n\t\tgroup by i.IncomeChequeID";
//.........................................................
$query .= dataReader::makeOrder();
$temp = PdoDataAccess::runquery_fetchMode($query, $param);
print_r(ExceptionHandler::PopAllExceptions());
//echo PdoDataAccess::GetLatestQueryString();
$no = $temp->rowCount();
$temp = PdoDataAccess::fetchAll($temp, $_GET["start"], $_GET["limit"]);
echo dataReader::getJsonData($temp, $no, $_GET["callback"]);
die;
}
function writ_print_list($transcript_no, $last_writ_flag)
{
$staff_onclause = $last_writ_flag ? "s.staff_id=w.staff_id AND s.last_writ_id=w.writ_id AND s.last_writ_ver=w.writ_ver" : "s.staff_id=w.staff_id";
$title = $transcript_no == "all" ? 'wts.title transcripts_title,wts.transcript_id , tbl1.mtid,' : "";
$writ_transcripts_join = $transcript_no == "all" ? '
LEFT OUTER JOIN HRM_writ_transcripts wts ON(w.person_type = wts.person_type AND w.emp_state = wts.emp_state)
LEFT OUTER JOIN (select max(transcript_id) mtid ,person_type , emp_state
from HRM_writ_transcripts group by person_type , emp_state) tbl1
ON(tbl1.person_type = w.person_type AND tbl1.emp_state = w.emp_state)' : "";
$order = $transcript_no == "all" ? ' wts.transcript_id ASC' : "";
$query = " select\n\t\t\t\ts.staff_id,\n\t\t\t\tw.writ_id,\n\t\t\t\tw.writ_ver,\n\t\t\t\tw.staff_id,\n\t\t\t\tw.person_type,\n\t\t\t\tw.onduty_year,\n\t\t\t\tw.onduty_month,\n\t\t\t\tw.onduty_day,\n\t\t\t\tw.writ_type_id,\n\t\t\t\tw.emp_state,\n\t\t\t\tw.emp_mode,\n\t\t\t\tw.ref_letter_date,\n\t\t\t\tw.ref_letter_no,\n\t\t\t\tw.send_letter_no,\n\t\t\t\tw.contract_start_date,\n\t\t\t\tw.contract_end_date,\n\t\t\t\tw.corrective,\n\t\t\t\tw.issue_date,\n\t\t\t\tw.execute_date ,\n\t\t\t\twst.time_limited,\n\t\t\t\tp.sex sex ,w.marital_status , \n\t\t\t\tw.notes,w.family_responsible , \n\t\t\t\tw.pay_date pay_date,\n w.MissionPlace , \n\t\t\t\tmsts.Title marital_status_title,\n\t\t\t\tedulv.Title education_level_title,\n\n\t\t\t\t(w.cur_group-jf.start_group)+1 old_grup,\n\t\t\t\t(w.cur_group - 4) new_grup,\n\t\t\t\tw.cur_group ,\n\t\t\t\tw.description description,\n\t\t\t\tw.included_children_count,\n\t\t\t\tw.children_count,\n\t\t\t\tmiltype.Title as military,miltype.InfoID as militaryID , \n\t\t\t\tw.job_id,\n\t\t\t\tsinclv.Title snc_level,\n\t\t\t\tw.base,\n\t\t\t\tw.ouid,\n\t\t\t\tempstt.Title emp_st,\n\t\t\t\tworktime.Title worktime,\n\t\t\t\tp.sex gnd,\n\t\t\t\tp.pfname ps_pfname,\n\t\t\t\tp.birth_place ps_birth_place,\n\t\t\t\tp.issue_place,\n\t\t\t\tp.plname ps_plname,\n\t\t\t\tp.idcard_no ps_idcard_no,\n\t\t\t\tp.father_name ps_father_name,\n\t\t\t\tp.birth_date ps_birth_date,\n\t\t\t\tp.national_code,\n\t\t\t\tp.military_from_date,\n\t\t\t\tp.military_to_date,\n\t\t\t\tp.military_type,\n\t\t\t\tp.military_duration_day, \n\t\t\t\tp.military_duration ,\n\t\t\t\to.ptitle o_ptitle,\n\t\t\t\to.ouid o_ouid,\n\t\t\t\tparentu.ouid ou_ouid ,\n\t\t\t\tparentu.ptitle ou_ptitle ,\n\t\t\t\to.org_unit_type org_sub_unit_type ,\n\t\t\t\to.ptitle os_ptitle ,\n\t\t\t\tpunit.ouid po_ouid,\n\t\t\t\tpunit.ptitle po_ptitle,\n\t\t\t\tpsubunit.ptitle pos_ptitle ,\n\t\t\t\tpsubunit.ouid pos_ouid ,\n\t\t\t\tj.title j_title,\n\t\t\t\tj.job_group,\n\t\t\t\tsf.ptitle sf_title,\n\t\t\t\tsbs.ptitle sbs_title,\n\t\t\t\twst.print_title wst_title,\n\t\t\t\tpo.title p_title,\n\t\t\t\tpo.post_no p_post_no,\n\t\t\t\tpo.post_id ,\n\t\t\t\tpo.ouid pouid ,\n\t\t\t\tposttype.Title post_type,\n\t\t\t\tcw.ptitle cw_ptitle,\n\t\t\t\tcb.ptitle cb_ptitle,\n\t\t\t\tci.ptitle ci_ptitle,\t\t\n\t\t\t\tsi.ptitle si_ptitle,\n\t\t\t\tsw.ptitle sw_ptitle,\n\t\t\t\tjf.title jf_title,\n\t\t\t\tjsc.title jsc_title,\n\t\t\t\tjc.title jc_title,\n\t\t\t\ts.personel_no,\n\t\t\t\ts.work_start_date,\n\t\t\t\tc.ptitle country_title,\n\t\t\t\tsbs.ptitle sbs_title,\n\t\t\t\tsf.ptitle sf_ptitle,\n\t\t\t\tsf.sfid , s.unitCode ,\n\t\t\t\tj.job_id ,\n\t\t\t\tj.title j_title,\n\t\t\t\twt.title writ_type,\n\t\t\t\t" . $title . "\n\t\t\t\tp.PersonID ,\n\t\t\t\tw.writ_signature_post_title,\n\t\t\t\tw.writ_signature_post_owner ,w.grade ,\n\t\t\t\thistory_only,\n\t\t\t\twst.req_staff_signature, \n\t\t\t\twsi.param8 E_base, \n\t\t\t\twsi.param5 ComputeGrade, \n\t\t\t\tba.SBase S_base, \n\t\t\t\tba.IsarValue I_base ,\n\t\t\t\tba.TashvighiValue T_base ";
$query .= " from staff s\n\t\t\t INNER JOIN HRM_writs w ON (" . $staff_onclause . ")\n\n\t\t\t LEFT OUTER JOIN HRM_writ_subtypes wst\n\t\t\t\t ON ((w.person_type = wst.person_type) AND (w.writ_type_id = wst.writ_type_id) AND (w.writ_subtype_id=wst.writ_subtype_id))\n\t\t\t LEFT OUTER JOIN HRM_writ_types wt\n\t\t\t\t ON ((w.person_type = wt.person_type) AND (w.writ_type_id = wt.writ_type_id))\n\t\t\t LEFT OUTER JOIN HRM_person_educations pe1\n\t\t\t\t ON ((w.education_level = pe1.education_level) AND (w.sfid = pe1.sfid) AND (w.sbid = pe1.sbid) AND (pe1.PersonID = s.PersonID))\n\t\t\t LEFT OUTER JOIN HRM_countries c ON (pe1.country_id = c.country_id)\n\t\t\t LEFT OUTER JOIN HRM_universities u ON ((pe1.university_id = u.university_id) AND (pe1.country_id = u.country_id))\n\t\t\t LEFT OUTER JOIN HRM_study_fields sf ON ((w.sfid = sf.sfid))\n\t\t\t LEFT OUTER JOIN HRM_study_branchs sbs ON ((w.sfid = sbs.sfid)AND(w.sbid=sbs.sbid))\n\t\t\t LEFT OUTER JOIN HRM_persons p ON (s.PersonID = p.PersonID)\n\t\t\t LEFT OUTER JOIN HRM_cities cw ON ((w.work_city_id = cw.city_id) AND (w.work_state_id = cw.state_id))\n\t\t\t " . $writ_transcripts_join . "\n\t\t\t LEFT OUTER JOIN HRM_states sw ON (cw.state_id=sw.state_id)\n\t\t\t LEFT OUTER JOIN HRM_cities cb ON ((p.birth_city_id = cb.city_id) AND (p.birth_state_id = cb.state_id))\n\t\t\t LEFT OUTER JOIN HRM_cities ci ON ((p.issue_city_id=ci.city_id) AND (p.issue_state_id = ci.state_id))\n\t\t\t LEFT OUTER JOIN HRM_states si ON (ci.state_id=si.state_id)\n\t\t\t LEFT OUTER JOIN HRM_position po ON (w.post_id = po.post_id)\n\t\t\t LEFT OUTER JOIN HRM_org_new_units psubunit ON (psubunit.ouid = po.ouid)\n\t\t\t LEFT OUTER JOIN HRM_org_new_units punit ON (punit.ouid = psubunit.parent_ouid)\n\t\t\t LEFT OUTER JOIN HRM_job_fields jf ON (po.jfid = jf.jfid)\n\t\t\t LEFT OUTER JOIN HRM_job_subcategory jsc ON ((jf.jsid = jsc.jsid) AND (jf.jcid=jsc.jcid))\n\t\t\t LEFT OUTER JOIN HRM_job_category jc ON (jsc.jcid = jc.jcid)\n\t\t\t LEFT OUTER JOIN HRM_org_new_units o ON (w.ouid = o.ouid)\n\t\t\t LEFT OUTER JOIN HRM_org_new_units parentu ON (parentu.ouid = o.parent_ouid)\n\t\t\t LEFT OUTER JOIN HRM_jobs j ON (w.job_id = j.job_id) \n\t\t\t \n\t\t\t LEFT OUTER JOIN HRM_writ_salary_items wsi ON w.writ_id = wsi.writ_id AND \n\t\t\t\t\t\t\t\tw.writ_ver = wsi.writ_ver AND w.staff_id = wsi.staff_id AND wsi.salary_item_type_id = 10364 \n\t\t\t\t\t\t\t\t\n\t\t\t LEFT OUTER JOIN (select PersonID , sum(if(ba.BaseType in (6,2,20,21,22,23,24,25,26,27) and ba.BaseStatus = 'NORMAL' ,ba.BaseValue,0)) TashvighiValue ,\n\t\t\t\t\t\t\t\t\t\tsum(if(ba.BaseType in (3,4,5,7) and ba.BaseStatus = 'NORMAL' ,ba.BaseValue,0)) IsarValue ,\n\t\t\t\t\t\t\t\t\t\tsum(if(ba.BaseType in (1 ) and ba.BaseStatus = 'NORMAL' ,ba.BaseValue,0)) SBase\n\n\t\t\t\t\t\t\t\t\tfrom bases ba\n\n\t\t\t\t\t\t\t group by PersonID) ba \n\t\t\t\t\t\t\t ON ba.PersonID = s.PersonID " . QueryHelper::makeBasicInfoJoin(15, "msts", "w.marital_status") . QueryHelper::makeBasicInfoJoin(6, "edulv", "w.education_level") . QueryHelper::makeBasicInfoJoin(10, "miltype", "p.military_type") . QueryHelper::makeBasicInfoJoin(8, "sinclv", "w.science_level") . QueryHelper::makeBasicInfoJoin(3, "empstt", "w.emp_state") . QueryHelper::makeBasicInfoJoin(14, "worktime", "w.worktime_type") . QueryHelper::makeBasicInfoJoin(27, "posttype", "po.post_type");
$where = "1=1";
$whereParam = array();
require_once '../data/writ.data.php';
echo "****";
die;
MakeAdvanceSearchWhere($where, $whereParam);
if (!empty($_REQUEST["writ_id"]) && !empty($_REQUEST["writ_ver"]) && !empty($_REQUEST["staff_id"])) {
$where .= " AND w.writ_id = :wid AND w.writ_ver=:wver AND w.staff_id=:stid";
$whereParam[":wid"] = $_REQUEST["writ_id"];
$whereParam[":wver"] = $_REQUEST["writ_ver"];
$whereParam[":stid"] = $_REQUEST["staff_id"];
} else {
if ($last_writ_flag == 1) {
$where .= " AND w.writ_id = s.last_writ_id AND w.writ_ver=s.last_writ_ver";
}
}
if (!empty($_REQUEST["ouid"])) {
$return = QueryHelper::MK_org_units($_REQUEST["ouid"]);
$where .= " AND " . $return["where"];
$whereParam = array_merge($whereParam, $return["param"]);
}
if ($transcript_no != 1 && $transcript_no != 2) {
$transcript_no = 2;
}
//--------------------------------------------------------------------------
if ($_REQUEST['transcript_no'] == "all") {
$where .= " AND wts.transcript_id not in (117 , 123 , 127 , 44 , 9 , 114 ) ";
}
$query .= " where " . $where;
$query .= " order by p.plname , p.pfname ";
$dt = PdoDataAccess::runquery_fetchMode($query, $whereParam);
echo PdoDataAccess::GetLatestQueryString();
die;
return $dt;
}
/**
* @param manage_writ $cur_writ
*/
function compute_automatic_writ_salary_items($cur_writ,$t="") {
$query = " select * from HRM_salary_item_types
WHERE person_type in ( 3 ) AND
compute_place = " . SALARY_ITEM_COMPUTE_PLACE_WRIT . " AND
salary_compute_type = " . SALARY_COMPUTE_TYPE_FUNCTION . " AND
user_data_entry = " . AUTOMATIC . " AND
validity_start_date <= '" . $cur_writ->execute_date . "' AND
(validity_end_date >= '" . $cur_writ->execute_date . "' OR
validity_end_date IS NULL OR validity_end_date ='0000-00-00' )
ORDER BY ComputeOrder ";
$salary_items_DT = parent::runquery_fetchMode($query);
for ($i = 0; $i < $salary_items_DT->rowCount(); $i++) {
$salary_items_row = $salary_items_DT->fetch();
$query = "SELECT w.writ_id,
w.writ_ver,
w.execute_date,
w.staff_id,
s.personID,
w.person_type,
w.cur_group,
w.education_level,
w.onduty_year,
w.onduty_month,
w.onduty_day,
w.family_responsible,
w.included_children_count,
w.post_id,
w.emp_mode,
w.emp_state,
w.job_id,
j.job_group,
w.marital_status,
p.PersonID,p.sex , p.military_duration_day,
p.military_duration,
s.last_retired_pay,
sit.salary_item_type_id,
wsi.param1,
wsi.param2,
wsi.param3,
wsi.param4,
wsi.param5,
wsi.param6,
wsi.param7,
wsi.value,
wsi.automatic,
wsi.remember_date,
wsi.remember_message,
wsi.remembered,
sit.salary_compute_type,
sit.multiplicand,
sit.function_name,
sit.user_data_entry,
sit.editable_value,
s.work_start_date,
bf.param1 master_education_level,
sit.person_type as sp_person_type
FROM HRM_writs as w
inner join HRM_staff as s on(w.staff_id = s.staff_id)
left join HRM_jobs j on(j.job_id=w.job_id)
inner join HRM_persons as p on(s.PersonID = p.PersonID)
join BaseInfo bf on(bf.TypeID=56 AND bf.InfoID=w.education_level)
join HRM_salary_item_types sit
left join HRM_writ_salary_items wsi on(sit.salary_item_type_id=wsi.salary_item_type_id AND
wsi.writ_id=w.writ_id AND wsi.writ_ver=w.writ_ver AND
wsi.staff_id=w.staff_id)
WHERE w.writ_id= " . $cur_writ->writ_id . " and
w.writ_ver=" . $cur_writ->writ_ver . " and
w.staff_id =" . $cur_writ->staff_id . " and
sit.salary_item_type_id = " . $salary_items_row["salary_item_type_id"];
$dt = parent::runquery_fetchMode($query);
if ($dt->rowCount() > 0)
$wsi_record = $dt->fetch();
else
continue;
$this->writ_id = $cur_writ->writ_id;
$this->writ_ver = $cur_writ->writ_ver;
$this->staff_id = $cur_writ->staff_id;
$this->salary_item_type_id = $salary_items_row["salary_item_type_id"];
$this->automatic = 1;
$this->remember_date = '0000-00-00';
$this->remember_message = $salary_items_row['remember_message'];
$function_name = $salary_items_row['function_name'];
$this->value = manage_writ_item::$function_name($wsi_record);
if ($this->value === false){
return false;
}
$this->param1 = $wsi_record["param1"];
$this->param2 = $wsi_record["param2"];
$this->param3 = $wsi_record["param3"];
$this->param4 = $wsi_record["param4"];
$this->param5 = $wsi_record["param5"];
$this->param6 = $wsi_record["param6"];
$this->param7 = $wsi_record["param7"];
if ($this->value > 0) {
$this->replaceWritItem();
}
else
manage_writ_item::RemoveWritItem(" writ_id = " . $this->writ_id . " AND
writ_ver = " . $this->writ_ver . " AND
staff_id = " . $this->staff_id . " AND
salary_item_type_id = " . $this->salary_item_type_id);
}
return true;
}
static function SelectReceivedForms($where = "", $params = array())
{
$dt = PdoDataAccess::runquery("select FlowID,StepID \n\t\t\tfrom WFM_FlowSteps s \n\t\t\tleft join BSC_persons p using(PostID)\n\t\t\twhere s.IsActive='YES' AND if(s.PersonID>0,s.PersonID=:pid,p.PersonID=:pid)", array(":pid" => $_SESSION["USER"]["PersonID"]));
if (count($dt) == 0) {
return array();
}
$where .= " AND fr.IsEnded='NO' AND (";
foreach ($dt as $row) {
$preStep = $row["StepID"] * 1 - 1;
$nextStep = $row["StepID"] * 1 + 1;
$where .= "(fr.FlowID=" . $row["FlowID"] . " AND fs.StepID" . ($preStep == 0 ? " is null" : "=" . $preStep) . " AND ActionType='CONFIRM') OR (fr.FlowID=" . $row["FlowID"] . " AND fs.StepID=" . $nextStep . " AND ActionType='REJECT') OR";
}
$where = substr($where, 0, strlen($where) - 2) . ")";
//--------------------------------------------------------
$query = "select fr.*,f.FlowDesc, \n\t\t\t\t\t\tb.InfoDesc ObjectTypeDesc,\n\t\t\t\t\t\tifnull(fr.StepDesc,'شروع گردش') StepDesc,\n\t\t\t\t\t\tif(p.IsReal='YES',concat(p.fname, ' ',p.lname),p.CompanyName) fullname,\n\t\t\t\t\t\tb.param1 url,\n\t\t\t\t\t\tb.param2 parameter\n\t\t\t\t\tfrom WFM_FlowRows fr\n\t\t\t\t\tjoin ( select max(RowID) RowID,FlowID,ObjectID from WFM_FlowRows group by FlowID,ObjectID )t\n\t\t\t\t\t\tusing(RowID,FlowID,ObjectID)\n\t\t\t\t\tjoin WFM_flows f using(FlowID)\n\t\t\t\t\tjoin BaseInfo b on(b.TypeID=11 AND b.InfoID=f.ObjectType)\n\t\t\t\t\tleft join WFM_FlowSteps fs on(fr.StepRowID=fs.StepRowID)\n\t\t\t\t\tjoin BSC_persons p on(fr.PersonID=p.PersonID)\n\n\t\t\t\t\tleft join LON_ReqParts lp on(fr.ObjectID=PartID)\n\t\t\t\t\tleft join LON_requests lr on(lp.RequestID=lr.RequestID)\n\t\t\t\t\tleft join BSC_persons pp on(lr.LoanPersonID=pp.PersonID)\n\n\t\t\t\t\twhere 1=1 " . $where . dataReader::makeOrder();
return PdoDataAccess::runquery_fetchMode($query, $params);
}
function exe_service_insure_sql()
{
$this->monitor(6);
/*if(!$this->backpay){
//parent::runquery(" DROP TABLE IF EXISTS mpds ");
parent::runquery('TRUNCATE mpds');
parent::runquery(" /*CREATE TABLE mpds AS*/
/*insert into mpds
SELECT pds.PersonID,
pds.master_row_no,
MAX(pds.from_date) from_date ,
SUBSTR(MAX(CONCAT(pds.from_date,pds.row_no)),11) row_no
FROM person_dependent_supports pds
WHERE pds.from_date <= '".$this->month_end."' AND(pds.to_date >= '".$this->month_start."' OR
pds.to_date IS NULL OR pds.to_date = '0000-00-00' ) AND
(pds.status = ".DELETE_IN_EMPLOYEES." OR pds.status = ".IN_SALARY.")
GROUP BY pds.PersonID,pds.master_row_no;");
$this->service_insure_rs = parent::runquery_fetchMode(" SELECT pds.PersonID ,
s.staff_id,
COUNT( CASE pds.insure_type
WHEN ".NORMAL." THEN 1
END) normal,
COUNT( CASE pds.insure_type
WHEN ".NORMAL." AND pd.dependency IN(".OWN.") THEN 1
END) own_normal,
COUNT( CASE pds.insure_type
WHEN ".NORMAL2." THEN 5
END) normal2,
COUNT( CASE pds.insure_type
WHEN ".FIRST_SURPLUS." THEN 2
END) extra1,
COUNT( CASE pds.insure_type
WHEN ".SECOND_SURPLUS." THEN 3
END) extra2,
COUNT( CASE
WHEN pds.insure_type = ".NORMAL." AND pd.dependency IN(".OWN.",".FATHER.",".MOTHER.",".WIFE.",".BOY.",".DAUGHTER.") THEN 1
END)ret_normal,
COUNT( CASE
WHEN pds.insure_type = ".NORMAL2." AND pd.dependency IN(".OWN.",".FATHER.",".MOTHER.",".WIFE.",".BOY.",".DAUGHTER.") THEN 5
END)ret_normal2,
COUNT( CASE
WHEN pds.insure_type = ".FIRST_SURPLUS." AND pd.dependency IN(".OWN.",".FATHER.",".MOTHER.",".WIFE.",".BOY.",".DAUGHTER.") THEN 2
END)ret_extra1,
COUNT( CASE
WHEN pds.insure_type = ".SECOND_SURPLUS." AND pd.dependency IN(".OWN.",".FATHER.",".MOTHER.",".WIFE.",".BOY.",".DAUGHTER.") THEN 3
END)ret_extra2,
sit.validity_start_date,
sit.validity_end_date
FROM mpds mp
INNER JOIN person_dependent_supports pds
ON(mp.PersonID = pds.PersonID AND mp.master_row_no = pds.master_row_no AND mp.row_no = pds.row_no)
INNER JOIN person_dependents pd
ON(pd.PersonID = pds.PersonID AND pd.row_no = pds.master_row_no)
INNER JOIN staff s
ON(pds.PersonID = s.PersonID)
INNER JOIN limit_staff ls
ON(s.staff_id = ls.staff_id)
INNER JOIN salary_item_types sit
ON((sit.salary_item_type_id = ".SIT_PROFESSOR_REMEDY_SERVICES_INSURE." AND s.person_type = ".HR_PROFESSOR.") OR
(sit.salary_item_type_id = ".SIT_STAFF_REMEDY_SERVICES_INSURE." AND s.person_type = ".HR_EMPLOYEE."))
GROUP BY s.staff_id,
pds.PersonID,
sit.validity_start_date,
sit.validity_end_date");
}
else
{*/
$this->service_insure_rs = parent::runquery_fetchMode(" SELECT \n p.PersonID ,\n s.staff_id,\n IFNULL(pi.param1,0) normal,\n IFNULL(pi.param8,0) normal2,\n IFNULL(pi.param2,0) extra1,\n IFNULL(pi.param3,0) extra2,\n IFNULL(pi.param7,0) own_normal ,\n IFNULL(pi2.param1,0) ret_normal,\n IFNULL(pi2.param8,0) ret_normal2,\n IFNULL(pi2.param2,0) ret_extra1,\n IFNULL(pi2.param3,0) ret_extra2,\n null validity_start_date,\n null validity_end_date\n\n FROM staff s\n INNER JOIN persons p\n ON(p.PersonID = s.PersonID)\n INNER JOIN Arrear_limit_staff ls\n ON(s.staff_id = ls.staff_id)\n LEFT OUTER JOIN payment_items pi\n ON\n pi.staff_id = s.staff_id\n AND pi.pay_year = " . $this->__YEAR . "\n AND pi.pay_month = " . $this->__MONTH . "\n AND (\n (pi.salary_item_type_id = " . SIT_PROFESSOR_REMEDY_SERVICES_INSURE . " AND s.person_type = " . HR_PROFESSOR . ") OR\n (pi.salary_item_type_id = " . SIT_STAFF_REMEDY_SERVICES_INSURE . " AND s.person_type = " . HR_EMPLOYEE . ")\n )\n LEFT OUTER JOIN payment_items pi2\n ON\n pi2.staff_id = s.staff_id\n AND pi2.pay_year = " . $this->__YEAR . "\n AND pi2.pay_month = " . $this->__MONTH . "\n AND (pi2.salary_item_type_id = " . RETURN_FIRST_MONTH_MOGHARARY . ") ");
/*} */
}
function GetAccountFlow()
{
$CostID = $_REQUEST["BaseCostID"];
$TafsiliID = $_REQUEST["TafsiliID"];
$query = "select d.*,di.*\n\t\tfrom ACC_DocItems di\n\t\t\tjoin ACC_docs d using(DocID)\n\t\twhere d.CycleID=:c AND d.BranchID=:b AND \n\t\t\tdi.CostID=:cost AND di.TafsiliType = :t AND di.TafsiliID=:tid " . dataReader::makeOrder();
$param = array(":c" => $_SESSION["accounting"]["CycleID"], ":b" => $_SESSION["accounting"]["BranchID"], ":cost" => $CostID, ":t" => TAFTYPE_PERSONS, ":tid" => $TafsiliID);
$temp = PdoDataAccess::runquery_fetchMode($query, $param);
$no = $temp->rowCount();
//------------------------------------------------
$BlockedAmount = ACC_CostBlocks::GetBlockAmount($CostID, TAFTYPE_PERSONS, $TafsiliID);
//------------------------------------------------
echo dataReader::getJsonData($temp->fetchAll(), $no, $_GET["callback"], $BlockedAmount);
die;
}
public static function select($where = "")
{
$query = "SELECT * FROM FGR_forms";
$query .= $where != "" ? " where " . $where : "";
return PdoDataAccess::runquery_fetchMode($query);
}
function GetChangesExcel(){
$param = array();
$where = "";
$param[":year"] = $_POST["pay_year"];
$param[":month"] = $_POST["pay_month"];
$param[":pt"] = $_POST["PayType"];
$pre_year = $_POST["pay_year"] ;
$pre_month = $_POST["pay_month"] - 1 ;
if($pre_month == 0){
$pre_year--;
$pre_month = 12 ;
}
$param[":preyear"] = $pre_year;
$param[":premonth"] = $pre_month;
MakeWhere($where, $param);
$dataTable = PdoDataAccess::runquery_fetchMode("
select
'' date,
concat(plname,' ',pfname) name,
s.staff_id ,
bi.Title emp_mode_desc,
pit1.get_value val1,
if( pit2.param1 - pit1.param1>0 , pit2.param1 - pit1.param1 , 0 ) normal_insure_inc,
if( pit2.param8 - pit1.param8>0 , pit2.param8 - pit1.param8 , 0 ) normal2_insure_inc,
if( pit2.param2 - pit1.param2>0 , pit2.param2 - pit1.param2 , 0 ) first_surplus_insure_inc,
if( pit2.param3 - pit1.param3>0 , pit2.param3 - pit1.param3 , 0 ) second_surplus_insure_inc,
if( pit2.param1 - pit1.param1>0 , pit2.param1 - pit1.param1 , 0 ) +
if(pit2.param2 - pit1.param2>0 , pit2.param2 - pit1.param2 , 0 ) +
if(pit2.param3 - pit1.param3>0 , pit2.param3 - pit1.param3 , 0 ) insure_inc_count,
bi2.Title emp_mode_desc2,
pit2.get_value val2,
if( pit2.param1 - pit1.param1>0 , 0 , pit1.param1 - pit2.param1 ) normal_insure_dec,
if( pit2.param8 - pit1.param8>0 , 0 , pit1.param8 - pit2.param8 ) normal2_insure_dec,
if( pit2.param2 - pit1.param2>0 , 0 , pit1.param2 - pit2.param2 ) first_surplus_insure_dec,
if( pit2.param3 - pit1.param3>0 , 0 , pit1.param3 - pit2.param3 ) second_surplus_insure_dec,
if( pit2.param1 - pit1.param1>0 , 0 , pit1.param1 - pit2.param1 ) +
if( pit2.param2 - pit1.param2>0 , 0 , pit1.param2 - pit2.param2 ) +
if( pit2.param3 - pit1.param3>0 , 0 , pit1.param3 - pit2.param3 ) insure_dec_count
from staff s
INNER JOIN persons per ON (per.personID = s.personID)
LEFT OUTER JOIN payments p1
ON p1.pay_year = :preyear AND p1.pay_month = :premonth AND p1.payment_type = :pt AND s.staff_id = p1.staff_id
LEFT OUTER JOIN payments p2
ON p2.pay_year = :year AND p2.pay_month = :month AND p2.payment_type = :pt AND s.staff_id = p2.staff_id
LEFT OUTER JOIN writs w1
ON (w1.writ_id = p1.writ_id AND w1.writ_ver = p1.writ_ver AND w1.staff_id = p1.staff_id )
LEFT OUTER JOIN writs w2
ON (w2.writ_id = p2.writ_id AND w2.writ_ver = p2.writ_ver AND w2.staff_id = p2.staff_id )
LEFT OUTER JOIN payment_items pit1
ON ((p1.staff_id = pit1.staff_id) AND (p1.pay_year = pit1.pay_year) AND
(p1.pay_month = pit1.pay_month) AND (p1.payment_type = pit1.payment_type))
AND (pit1.salary_item_type_id IN (143,38) )
LEFT OUTER JOIN payment_items pit2
ON ((p2.staff_id = pit2.staff_id) AND (p2.pay_year = pit2.pay_year) AND
(p2.pay_month = pit2.pay_month) AND (p2.payment_type = pit2.payment_type))
AND (pit2.salary_item_type_id IN (143,38) )
left join Basic_Info bi on(bi.TypeID=4 AND bi.InfoID=w1.emp_mode)
left join Basic_Info bi2 on(bi2.TypeID=4 AND bi2.InfoID=w2.emp_mode)
where (pit1.get_value > 0 OR pit2.get_value >0 ) AND
(pit1.param1 != pit2.param1 OR
pit1.param8 != pit2.param8 OR
pit1.param2 != pit2.param2 OR
pit1.param3 != pit2.param3 OR
w1.emp_mode != w2.emp_mode ) $where ", $param);
//echo PdoDataAccess::GetLatestQueryString();die();
$rpt = new ReportGenerator();
$rpt->mysql_resource = $dataTable;
$rpt->addColumn("تاريخ", "date");
$rpt->addColumn('نام و نام خانوادگي', "name");
$rpt->addColumn("كد اصلي بيمه شده", "staff_id");
$rpt->addColumn("وضعيت استخدامي قبل", "emp_mode_desc");
$rpt->addColumn("مبلغ قبلي", "val1");
$rpt->addColumn("افزايش تبعي1", "normal_insure_inc");
$rpt->addColumn("افزايش تبعي 1 جديد", "normal2_insure_inc");
$rpt->addColumn("افزايش تبعي2", "first_surplus_insure_inc");
$rpt->addColumn("افزايش تبعي3", "second_surplus_insure_inc");
$rpt->addColumn("جمع افزايش", "insure_inc_count");
$rpt->addColumn("وضعيت استخدامي جديد", "emp_mode_desc2");
$rpt->addColumn("'مبلغ جديد", "val2");
$rpt->addColumn("كاهش تبعي1", "normal_insure_dec");
$rpt->addColumn("كاهش تبعي 1 جدید", "normal2_insure_dec");
$rpt->addColumn("كاهش تبعي2", "first_surplus_insure_dec");
$rpt->addColumn("كاهش تبعي3", "second_surplus_insure_dec");
$rpt->addColumn("جمع كاهش", "insure_dec_count");
$rpt->excel = true;
$rpt->generateReport();
die();
}
public static function SelectAccounts($where = '', $param = array())
{
$query = " select * from ACC_accounts acc ";
if ($where != '') {
$query .= ' where ' . $where;
}
$res = parent::runquery_fetchMode($query, $param);
return $res;
}
private function exe_tax_history()
{
if ($this->backpay) {
return true;
}
if ($this->__CALC_NORMALIZE_TAX) {
$start_date = DateModules::shamsi_to_miladi($this->__START_NORMALIZE_TAX_YEAR . "/" . $this->__START_NORMALIZE_TAX_MONTH . "/01");
$w = "end_date IS NULL OR end_date = '0000-00-00' OR end_date > '{$start_date}'";
} else {
$w = "NOT((start_date > '" . $this->month_end . "') OR (end_date IS NOT NULL AND end_date != '0000-00-00' AND end_date < '" . $this->month_start . "'))";
}
$this->tax_history_rs = parent::runquery_fetchMode("\n\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT sth.staff_id,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tsth.start_date,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tsth.end_date,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tsth.tax_table_type_id,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tsth.payed_tax_value\n\t\t\t\t\t\t\t\t\t\t\t\t\tFROM HRM_limit_staff ls\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN HRM_staff_tax_history sth\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tON(ls.staff_id = sth.staff_id)\n\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE " . $w . "\n\t\t\t\t\t\t\t\t\t\t\t\t\tORDER BY sth.staff_id,sth.start_date\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t");
}
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 SelectAllForms()
{
$where = "1=1";
$param = array();
$ObjectDesc = "case f.FlowID \r\n\t\t\twhen 1 then concat_ws(' ','وام شماره',lp.RequestID,'به مبلغ',\r\n\t\t\t\tPartAmount,'مربوط به',if(pp.IsReal='YES',concat(pp.fname, ' ', pp.lname),pp.CompanyName))\r\n\t\t\t\t\r\n\t\t\twhen 4 then concat_ws(' ','ضمانت نامه', wp.CompanyName,wp.fname,wp.lname, 'به مبلغ ',wr.amount)\r\n\t\t\t\r\n\t\tend";
if (!empty($_GET["fields"]) && !empty($_GET["query"])) {
$field = $_GET["fields"] == "ObjectDesc" ? $ObjectDesc : $_GET["fields"];
$field = $_GET["fields"] == "StepDesc" ? "ifnull(fs.StepDesc,'شروع گردش')" : $field;
$where .= " AND {$field} like :fld";
$param[":fld"] = "%" . $_GET["query"] . "%";
}
//----------------- received forms ----------------------
if (!empty($_GET["MyForms"]) && $_GET["MyForms"] == "true") {
$dt = PdoDataAccess::runquery("select FlowID,StepID \r\n\t\t\tfrom WFM_FlowSteps s \r\n\t\t\tleft join BSC_persons p using(PostID)\r\n\t\t\twhere s.IsActive='YES' AND if(s.PersonID>0,s.PersonID=:pid,p.PersonID=:pid)", array(":pid" => $_SESSION["USER"]["PersonID"]));
if (count($dt) == 0) {
echo dataReader::getJsonData(array(), 0, $_GET["callback"]);
die;
}
$where .= " AND fr.IsEnded='NO' AND (";
foreach ($dt as $row) {
$preStep = $row["StepID"] * 1 - 1;
$nextStep = $row["StepID"] * 1 + 1;
$where .= "(fr.FlowID=" . $row["FlowID"] . " AND fs.StepID" . ($preStep == 0 ? " is null" : "=" . $preStep) . " AND ActionType='CONFIRM') OR (fr.FlowID=" . $row["FlowID"] . " AND fs.StepID=" . $nextStep . " AND ActionType='REJECT') OR";
}
$where = substr($where, 0, strlen($where) - 2) . ")";
}
//--------------------------------------------------------
$query = "select fr.*,f.FlowDesc, \r\n\t\t\t\t\tb.InfoDesc ObjectTypeDesc,\r\n\t\t\t\t\tifnull(fr.StepDesc,'شروع گردش') StepDesc,\r\n\t\t\t\t\tif(p.IsReal='YES',concat(p.fname, ' ',p.lname),p.CompanyName) fullname,\r\n\t\t\t\t\t{$ObjectDesc} ObjectDesc,\r\n\t\t\t\t\tb.param1 url,\r\n\t\t\t\t\tb.param2 parameter\r\n\t\t\t\tfrom WFM_FlowRows fr\r\n\t\t\t\tjoin ( select max(RowID) RowID,FlowID,ObjectID from WFM_FlowRows group by FlowID,ObjectID )t\r\n\t\t\t\t\tusing(RowID,FlowID,ObjectID)\r\n\t\t\t\tjoin WFM_flows f using(FlowID)\r\n\t\t\t\tjoin BaseInfo b on(b.TypeID=11 AND b.InfoID=f.ObjectType)\r\n\t\t\t\tleft join WFM_FlowSteps fs on(fr.StepRowID=fs.StepRowID)\r\n\t\t\t\tjoin BSC_persons p on(fr.PersonID=p.PersonID)\r\n\t\t\t\t\r\n\t\t\t\tleft join LON_ReqParts lp on(fr.FlowID=1 AND fr.ObjectID=PartID)\r\n\t\t\t\tleft join LON_requests lr on(lp.RequestID=lr.RequestID)\r\n\t\t\t\tleft join BSC_persons pp on(lr.LoanPersonID=pp.PersonID)\r\n\t\r\n\t\t\t\tleft join WAR_requests wr on(fr.FlowID=4 AND wr.RequestID=fr.ObjectID)\r\n\t\t\t\tleft join BaseInfo bf on(bf.TypeID=74 AND bf.InfoID=wr.TypeID)\r\n\t\t\t\tleft join BSC_persons wp on(wp.PersonID=wr.PersonID)\r\n\r\n\t\t\t\twhere " . $where . dataReader::makeOrder();
$temp = PdoDataAccess::runquery_fetchMode($query, $param);
//echo PdoDataAccess::GetLatestQueryString();
$no = $temp->rowCount();
$temp = PdoDataAccess::fetchAll($temp, $_GET["start"], $_GET["limit"]);
echo dataReader::getJsonData($temp, $no, $_GET["callback"]);
die;
}
//------------------------------------------------------------------------------
$TotalQuery = $QUERY_SELECT;
$TotalQuery .= $rptobj->query;
$TotalQuery .= " where " . $QUERY_WHERE . " AND persons.person_type in(" . manage_access::getValidPersonTypes() . ")";
// bahar
$TotalQuery .= $QUERY_GROUP != "" ? " group by " . $QUERY_GROUP : "";
$TotalQuery .= $QUERY_ORDER != "" ? " order by " . $QUERY_ORDER : "";
if (!isset($_REQUEST["excel"])) {
echo "<div style='display:none'>" . $TotalQuery . "<br>";
print_r($whereParam);
echo "</div>";
}
if (isset($_REQUEST["preview"])) {
$TotalQuery .= " limit 10";
}
$statement = PdoDataAccess::runquery_fetchMode($TotalQuery, $whereParam);
if (ExceptionHandler::GetExceptionCount() != 0) {
print_r(ExceptionHandler::PopAllExceptions());
}
if ($statement->rowCount() == 0) {
$output = "<span class='reportGenerator'>گزارش مورد نظر خالی می باشد.</span>";
} else {
if ($rptobj->refer_page != "") {
/*for($i=0; $i<count($data); $i++)
{
for($k=0; $k<count($columns); $k++)
{
if(in_array($columns[$k]["used_type"], array("group", "order", "separation", "formula_column", "condition", "filter")))
continue;
if($columns[$k]["renderer"] != "")
function selectStudents()
{
$query = "\n\t\tSELECT StNo,concat(PFName,' ',PLName) fullname FROM students\n\t\twhere stNO like :s OR concat(PFName,' ',PLName) like :s";
$st = PdoDataAccess::runquery_fetchMode($query, array(":s" => '%' . $_GET["query"] . '%'));
$no = $st->rowCount();
$temp = PdoDataAccess::fetchAll($st, $_GET["start"], $_GET["limit"]);
echo dataReader::getJsonData($temp, $no, $_GET["callback"]);
die;
}
static function MinSelect($where = "", $param = array())
{
return PdoDataAccess::runquery_fetchMode("\r\n\t\t\tselect p.PersonID, concat_ws(' ',fname,lname,CompanyName) fullname\r\n\t\t\tfrom BSC_persons p\r\n\t\t\twhere " . $where, $param);
}
static function GetAllWrits($where = "", $whereParam = array())
{
/* $staff_group_join = "";
if(!empty($_REQUEST['staff_group_id']))
{
$staff_group_join = " LEFT JOIN staff_group_members sgm
ON sgm.staff_id = s.staff_id AND sgm.staff_group_id = ".$_REQUEST['staff_group_id'];
} */
// PdoDataAccess::runquery("insert into temp_sum_item_writs select * from sum_items_writs i on duplicate key update sumValue=i.sumValue");
$whr = "";
/*if($_SESSION['UserID'] != 'jafarkhani' && $_SESSION['UserID'] != 'delkalaleh' && $_SESSION['UserID'] != 'nadaf' &&
$_SESSION['UserID'] != 'm-hakimi' && $_SESSION['UserID'] != 'shokri' ) {
$whr = " AND w.execute_date < '2014-02-20' " ;
} */
if (isset($_REQUEST['last_writ_view']) && (empty($_REQUEST['to_execute_date']) || $_REQUEST['to_execute_date'] == '0000-00-00')) {
$query = " SELECT w.*,\n bi1.Title corrective_title ,\n\t\t bi2.Title history_only_title ,\n bi3.Title science_level_title ,\n p.pfname ,\n p.plname ,\n p.PersonID ,\n concat(p.pfname ,' ',p.plname) fullname ,\n wt.title MainWtitle,\n wst.title wst_title,\n wst.time_limited,\n concat(wt.title ,' ',wst.title) wt_title ,\n bi4.Title emp_state_title ,\n bi5.Title educTitle ,\n bi6.Title SPTitle ,\n bi7.Title AETitle ,\n o.ptitle o_ptitle,\n c.title c_title,\n c.cost_center_id ,\n sf.sfid,\n sf.ptitle sf_ptitle,\n sb.sbid,\n sb.ptitle sb_ptitle ,\n parentu.ouid ,\n parentu.ptitle parentTitle ,\n temp.sumValue\n\n\t\t FROM persons p\n JOIN staff s ON(p.personid = s.personid)\n " . $staff_group_join . "\n INNER JOIN writs w ON(s.staff_id = w.staff_id AND s.last_writ_id = w.writ_id AND s.last_writ_ver = w.writ_ver)\n LEFT JOIN sum_items_writs temp ON temp.writ_id = w.writ_id and\n temp.writ_ver = w.writ_ver and\n temp.staff_id = w.staff_id\n LEFT JOIN Basic_Info bi1 ON ( bi1.InfoID = w.corrective AND bi1.TypeID = 5)\n LEFT JOIN Basic_Info bi2 ON ( bi2.InfoID = w.history_only AND bi2.TypeID = 5)\n LEFT JOIN Basic_Info bi3 ON (bi3.InfoID = w.science_level AND bi3.TypeID = 8)\n LEFT JOIN Basic_Info bi4 ON (bi4.InfoID = w.emp_state AND bi4.TypeID = 3)\n LEFT JOIN Basic_Info bi5 ON (bi5.InfoID = w.education_level AND bi5.TypeID = 6 )\n LEFT JOIN Basic_Info bi6 ON (bi6.InfoID = w.salary_pay_proc AND bi6.TypeID = 12 )\n LEFT JOIN Basic_Info bi7 ON (bi7.InfoID = w.annual_effect AND bi7.TypeID = 13 )\n LEFT OUTER JOIN position po ON (w.post_id = po.post_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\n w.writ_type_id = wst.writ_type_id AND w.person_type = wst.person_type)\n LEFT OUTER JOIN study_branchs sb ON ((w.sbid = sb.sbid) AND (w.sfid = sb.sfid))\n LEFT OUTER JOIN study_fields sf ON (w.sfid = sf.sfid)\n LEFT OUTER JOIN org_new_units o ON (o.ouid = w.ouid)\n LEFT OUTER JOIN org_new_units parentu ON (parentu.ouid = o.parent_ouid)\n LEFT OUTER JOIN cost_centers c ON (w.cost_center_id = c.cost_center_id)\n\n where (s.last_cost_center_id is null OR s.last_cost_center_id in(" . manage_access::getValidCostCenters() . "))\n\t\t\t\t\t\tAND s.person_type in(" . manage_access::getValidPersonTypes() . ") {$whr} ";
} else {
if (isset($_REQUEST['last_writ_view']) && (!empty($_REQUEST['to_execute_date']) && $_REQUEST['to_execute_date'] != '0000-00-00')) {
$whereW = " AND w.execute_date >= '" . DateModules::shamsi_to_miladi($_REQUEST['from_execute_date']) . "'";
$whereW .= " AND w.execute_date <= '" . DateModules::shamsi_to_miladi($_REQUEST['to_execute_date']) . "'";
$query = " SELECT w.*,\n bi1.Title corrective_title ,\n\t\t bi2.Title history_only_title ,\n bi3.Title science_level_title ,\n p.pfname ,\n p.plname ,\n p.PersonID ,\n concat(p.pfname ,' ',p.plname) fullname ,\n wt.title MainWtitle,\n wst.title wst_title,\n wst.time_limited,\n concat(wt.title ,' ',wst.title) wt_title ,\n bi4.Title emp_state_title ,\n bi5.Title educTitle ,\n bi6.Title SPTitle ,\n bi7.Title AETitle ,\n o.ptitle o_ptitle,\n c.title c_title,\n c.cost_center_id ,\n sf.sfid,\n sf.ptitle sf_ptitle,\n sb.sbid,\n sb.ptitle sb_ptitle ,\n parentu.ouid ,\n parentu.ptitle parentTitle ,\n temp.sumValue\n\n\t\t FROM persons p\n JOIN staff s ON(p.personid = s.personid)\n " . $staff_group_join . "\n INNER JOIN (SELECT staff_id,\n SUBSTRING_INDEX(SUBSTRING(max_execute_date,11),'.',1) writ_id,\n SUBSTRING_INDEX(max_execute_date,'.',-1) writ_ver\n FROM (SELECT w.staff_id,\n max( CONCAT(w.execute_date,w.writ_id,'.',w.writ_ver) ) max_execute_date\n FROM writs w\n INNER JOIN staff ls\n ON(w.staff_id = ls.staff_id)\n WHERE w.history_only = 0 " . $whereW . "\n GROUP BY w.staff_id)tbl2) tbl1\n on s.staff_id = tbl1.staff_id \n INNER JOIN writs w\n on tbl1.writ_id = w.writ_id and\n tbl1.writ_ver = w.writ_ver and\n tbl1.staff_id = w.staff_id \n LEFT JOIN sum_items_writs temp ON temp.writ_id = w.writ_id and\n temp.writ_ver = w.writ_ver and\n temp.staff_id = w.staff_id\n LEFT JOIN Basic_Info bi1 ON ( bi1.InfoID = w.corrective AND bi1.TypeID = 5)\n LEFT JOIN Basic_Info bi2 ON ( bi2.InfoID = w.history_only AND bi2.TypeID = 5)\n LEFT JOIN Basic_Info bi3 ON (bi3.InfoID = w.science_level AND bi3.TypeID = 8)\n LEFT JOIN Basic_Info bi4 ON (bi4.InfoID = w.emp_state AND bi4.TypeID = 3)\n LEFT JOIN Basic_Info bi5 ON (bi5.InfoID = w.education_level AND bi5.TypeID = 6 )\n LEFT JOIN Basic_Info bi6 ON (bi6.InfoID = w.salary_pay_proc AND bi6.TypeID = 12 )\n LEFT JOIN Basic_Info bi7 ON (bi7.InfoID = w.annual_effect AND bi7.TypeID = 13 )\n LEFT OUTER JOIN position po ON (w.post_id = po.post_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\n w.writ_type_id = wst.writ_type_id AND w.person_type = wst.person_type)\n LEFT OUTER JOIN study_branchs sb ON ((w.sbid = sb.sbid) AND (w.sfid = sb.sfid))\n LEFT OUTER JOIN study_fields sf ON (w.sfid = sf.sfid)\n LEFT OUTER JOIN org_new_units o ON (o.ouid = w.ouid)\n LEFT OUTER JOIN org_new_units parentu ON (parentu.ouid = o.parent_ouid)\n LEFT OUTER JOIN cost_centers c ON (w.cost_center_id = c.cost_center_id)\n\n where (s.last_cost_center_id is null OR s.last_cost_center_id in(" . manage_access::getValidCostCenters() . "))\n\t\t\t\t\t\tAND s.person_type in(" . manage_access::getValidPersonTypes() . ") {$whr} ";
} else {
$query = " SELECT w.*, w.ouid sub_ouid ,\n\t bi1.InfoDesc corrective_title ,\n\t\t\t bi2.InfoDesc history_only_title ,\t \n\t p.pfname ,\n\t p.plname ,\n\t p.PersonID ,\n\t concat(p.pfname ,' ',p.plname) fullname ,\n\t wt.title MainWtitle,\n\t wst.title wst_title,\n\t wst.time_limited,\n\t concat(wt.title ,' ',wst.title) wt_title ,\n\t bi4.InfoDesc emp_state_title ,\n\t bi5.InfoDesc educTitle ,\n\t bi6.InfoDesc SPTitle ,\n\t bi7.InfoDesc AETitle ,\n\t bi8.InfoDesc ModeTitle ,\n\t o.ptitle o_ptitle,\t \n\t sf.sfid,\n\t sf.ptitle sf_ptitle,\n\t sb.sbid,\n\t sb.ptitle sb_ptitle ,\n\t parentu.ouid ,\n\t parentu.ptitle parentTitle ,\n\t po.title post_title ,\n\t po.post_no ,\n\t j.title job_title ,\n\t j.job_group ,\n\t \n\t\t\t\t\t\t\t (w.cur_group - jf.start_group) + 1 job_category \n\n\t\t\t FROM HRM_persons p\n\t JOIN HRM_staff s ON(p.personid = s.personid)\t \n\t INNER JOIN HRM_writs w ON (s.staff_id = w.staff_id )\n \n\t LEFT JOIN BaseInfo bi1 ON ( bi1.InfoID = w.corrective AND bi1.TypeID = 57)\n\t LEFT JOIN BaseInfo bi2 ON ( bi2.InfoID = w.history_only AND bi2.TypeID = 57)\t \n\t LEFT JOIN BaseInfo bi4 ON (bi4.InfoID = w.emp_state AND bi4.TypeID = 58)\n\t LEFT JOIN BaseInfo bi5 ON (bi5.InfoID = w.education_level AND bi5.TypeID = 56 )\n\t LEFT JOIN BaseInfo bi6 ON (bi6.InfoID = w.salary_pay_proc AND bi6.TypeID = 59 )\n\t LEFT JOIN BaseInfo bi7 ON (bi7.InfoID = w.annual_effect AND bi7.TypeID = 60 )\n\t LEFT OUTER JOIN BaseInfo bi8 ON ( bi8.InfoID = w.emp_mode AND bi8.TypeID = 61 )\n\t LEFT OUTER JOIN HRM_position po ON (w.post_id = po.post_id)\n\t\t\t\t\t\t\tLEFT OUTER JOIN HRM_job_fields jf ON (po.jfid = jf.jfid)\n\t LEFT OUTER JOIN HRM_writ_types wt ON ((w.writ_type_id = wt.writ_type_id) AND (w.person_type = wt.person_type))\n\t LEFT OUTER JOIN HRM_writ_subtypes wst ON ((w.writ_subtype_id = wst.writ_subtype_id) AND (w.writ_type_id = wst.writ_type_id) AND (w.person_type = wst.person_type))\n\t LEFT OUTER JOIN HRM_study_branchs sb ON ((w.sbid = sb.sbid) AND (w.sfid = sb.sfid))\n\t LEFT OUTER JOIN HRM_study_fields sf ON (w.sfid = sf.sfid)\n\t LEFT OUTER JOIN HRM_org_new_units o ON (o.ouid = w.ouid)\n\t LEFT OUTER JOIN HRM_org_new_units parentu ON (parentu.ouid = o.parent_ouid)\t \n\t LEFT OUTER JOIN HRM_jobs j ON ( w.job_id = j.job_id ) \n\n\n\t\t\t\t\twhere (1=1) {$whr}\n ";
}
}
$query .= $where != "" ? " AND " . $where : "";
$temp = parent::runquery_fetchMode($query, $whereParam);
return $temp;
}
$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";
$param[":cb"] = "%" . $_POST["ChequeBranch"] . "%";
}
if (!empty($_POST["ChequeStatus"])) {
$query .= " AND ChequeStatus = :cst";
$param[":cst"] = $_POST["ChequeStatus"];
}
//.........................................................
$dataTable = PdoDataAccess::runquery_fetchMode($query, $param);
function dateRender($row, $value)
{
return DateModules::miladi_to_shamsi($value);
}
function dateRender2($row, $val)
{
return DateModules::miladi_to_shamsi($val);
}
function moneyRender($row, $val)
{
return number_format($val, 0, '.', ',');
}
function durationRender($row)
{
return (string) ((int) substr($row["toDate"], 5, 2) - (int) substr($row["fromDate"], 5, 2) + 1);
static function IsEmpty($FolderID)
{
$dt = PdoDataAccess::runquery_fetchMode("select * from OFC_ArchiveItems where FolderID=?", array($FolderID));
return $dt->rowCount() > 0;
}
function GroupIssueWrit()
{
ini_set('max_execution_time', 1000);
//300 seconds = 5 minutes
ini_set("memory_limit", '500M');
$where = "s.person_type=" . $_POST["person_type"];
$whereParams = array();
//------------------------ Make Where Clause ----------------------
if (!empty($_REQUEST["ouid"])) {
$where .= " AND ( o.ouid = :ouid OR o.parent_path LIKE :ouid2 OR o.parent_path LIKE :ouid3 OR o.parent_path LIKE :ouid4 ) ";
$whereParams[":ouid"] = $_REQUEST["ouid"];
$whereParams[":ouid2"] = "%," . $_REQUEST["ouid"] . ",%";
$whereParams[":ouid3"] = "%" . $_REQUEST["ouid"] . ",%";
$whereParams[":ouid4"] = "%," . $_REQUEST["ouid"] . "%";
}
if (!empty($_POST["from_PersonID"])) {
$where .= " AND s.staff_id >= :f_personid";
$whereParams[":f_personid"] = $_POST["from_PersonID"];
}
if (!empty($_POST["to_PersonID"])) {
$where .= " AND s.staff_id <= :t_personid";
$whereParams[":t_personid"] = $_POST["to_PersonID"];
}
$where .= " AND " . QueryHelper::makeWhereOfCheckboxList("emp_state", "emp_state");
//-----------------------------------------------------------------
//$execute_date = DateModules::Shamsi_to_Miladi($_POST["execute_date"]);
//-----------------------------------------------------------------
/*$sql = 'SELECT s.staff_id,
s.last_writ_id,
s.last_writ_ver,
p.pfname ,
p.plname
FROM staff s
JOIN writs w ON (s.staff_id=w.staff_id AND s.last_writ_id = w.writ_id AND s.last_writ_ver = w.writ_ver)
JOIN persons p ON(s.PersonID = p.PersonID and s.person_type=p.person_type)';
$sql .= (!empty($_REQUEST["ouid"])) ?
"LEFT OUTER JOIN org_new_units o ON (s.ouid=o.ouid)" : "";
//JOIN Basic_Info bi ON bi.typeid = 6 and w.education_level = bi.InfoID and bi.MasterID = 1
$sql .= ' WHERE s.last_writ_id > 0 AND s.last_writ_ver > 0 AND w.cost_center_id != 46 AND
w.emp_mode in('.EMP_MODE_PRACTITIONER.',
'.EMP_MODE_EDUCATIONAL_MISSION.',
'.EMP_MODE_INTERNAL_STUDY_OPPORTUNITY.',
'.EMP_MODE_RUSTICATION_EXTERNAL_STUDY_OPPORTUNITY.',
'.EMP_MODE_INTERNAL_STUDY_MISSION.',
'.EMP_MODE_EXTERNAL_STUDY_MISSION.',
'.EMP_MODE_ENGAGEMENT.') AND ' . $where; */
$whereParams = array();
$sql = " select s.staff_id,\n s.last_writ_id,\n s.last_writ_ver,\n p.pfname ,\n p.plname , writ_type_id, writ_subtype_id ,\n g2j(wr.execute_date) execute_date \n\n from writs w inner join staff s\n on s.staff_id=w.staff_id AND s.last_writ_id = w.writ_id AND\n s.last_writ_ver = w.writ_ver\n inner join writs wr on s.staff_id = wr.staff_id\ninner join persons p on s.personid = p.personid\n\n where w.emp_state =4 and\n wr.writ_type_id = 5 and\n wr.writ_subtype_id = 145 and\n wr.execute_date > '2014-08-22' and\n wr.execute_date < '2014-11-22' and\n wr.history_only = 0 ";
$staff_dt = PdoDataAccess::runquery_fetchMode($sql, $whereParams);
unset($sql);
unset($where);
unset($whereParams);
if ($staff_dt->rowCount() == 0) {
echo "موارد انتخابی شما هیج فردی را برای صدور حکم شامل نمی شود.";
die;
}
$log_obj = new manage_writ_group_issue_log();
$send_letter_no = $_POST["send_letter_no"];
$unsuccess_count = 0;
if (empty($_POST["step"])) {
$_POST["step"] = 1;
}
$staff_count = $staff_dt->rowCount();
$writ_obj = "";
for ($index = 0; $index < $staff_count; $index++) {
$staff_row = $staff_dt->fetch();
/*if(isset($_POST["prevent_two_writ_at_one_day"]))
{
if(count(PdoDataAccess::runquery("
select * from writs
where staff_id=" . $staff_dt[$index]["staff_id"] . "
and execute_date = '" . $execute_date . "'")) != 0)
continue;
}*/
//.............................
if (!empty($_POST["to_send_letter_no"])) {
if ($send_letter_no > $_POST["to_send_letter_no"]) {
ExceptionHandler::PushException(EXCEED_OF_END_SEND_LETTER_NO_ERR);
break;
}
}
//.............................
/*$writ_obj = manage_writ::IssueWrit($staff_row["staff_id"],
$_POST["writ_type_id"],
$_POST["writ_subtype_id"],
$_POST["execute_date"],
$_POST["person_type"],
DateModules::Shamsi_to_Miladi($_POST["issue_date"]),
false,
false,
$send_letter_no); */
$_POST["issue_date"] = DateModules::shNow();
$execute_date = $staff_row["execute_date"];
$arr = preg_split('/\\//', $execute_date);
$execute_date = '1394/' . $arr[1] . "/" . $arr[2];
$writ_obj = manage_writ::IssueWrit($staff_row["staff_id"], $staff_row["writ_type_id"], $staff_row["writ_subtype_id"], $execute_date, $_POST["person_type"], DateModules::Shamsi_to_Miladi($_POST["issue_date"]), false, false, $send_letter_no);
if (!$writ_obj) {
$log_obj->make_unsuccess_rows($staff_row);
continue;
}
manage_writ_item::compute_writ_items($writ_obj->writ_id, $writ_obj->writ_ver, $writ_obj->staff_id);
/*@var $writ_obj manage_writ*/
if ($writ_obj !== false && $writ_obj->writ_id > 0) {
$staff_row['writ_id'] = $writ_obj->writ_id;
$log_obj->make_success_row($staff_row);
$send_letter_no = !empty($_POST["step"]) && !empty($_POST["send_letter_no"]) ? $send_letter_no + $_POST["step"] : "";
} else {
$log_obj->make_unsuccess_rows($staff_row);
if (!empty($_POST["stop_error_count"]) && $unsuccess_count >= $_POST["stop_error_count"]) {
ExceptionHandler::PushException(TOO_MANY_ERR);
break;
}
}
unset($writ_obj);
}
$log_obj->finalize();
$log_obj->make_result();
die;
}
function selectDataAudits()
{
$query = "select \r\n\t\t\tSysName,\r\n\t\t\tconcat_ws(' ',fname,lname,CompanyName) fullname , \r\n\t\t\tMainObjectID , \r\n\t\t\tSubObjectID, \r\n\t\t\tActionType , \r\n\t\t\tActionTime, \r\n\t\t\ttable_comment\r\n\t\t\t\r\n\t\tfrom DataAudit d\r\n\t\tjoin FRW_systems using(SystemID)\r\n\t\tjoin BSC_persons using(PersonID)\r\n\t\tjoin information_schema.TABLES on(Table_schema = 'krrtfir_rtfund' AND Table_name=d.TableName)\r\n\t\t\r\n\t\twhere 1=1";
$param = array();
//------------------------------------------------------
if (!empty($_POST["PersonID"])) {
$query .= " AND d.PersonID=:p";
$param[":p"] = $_POST["PersonID"];
}
if (!empty($_POST["SystemID"])) {
$query .= " AND d.SystemID=:s";
$param[":s"] = $_POST["SystemID"];
}
if (!empty($_POST["StartDate"])) {
$query .= " AND d.ActionTime>:sd";
$param[":sd"] = DateModules::shamsi_to_miladi($_POST["StartDate"], "-") . " 00:00:00";
}
if (!empty($_POST["EndDate"])) {
$query .= " AND d.ActionTime<:ed";
$param[":ed"] = DateModules::shamsi_to_miladi($_POST["EndDate"], "-") . " 23:59:59";
}
//------------------------------------------------------
$temp = PdoDataAccess::runquery_fetchMode($query . dataReader::makeOrder(), $param);
//print_r(ExceptionHandler::PopAllExceptions());
//echo PdoDataAccess::GetLatestQueryString();
$cnt = $temp->rowCount();
$temp = PdoDataAccess::fetchAll($temp, $_REQUEST["start"], $_REQUEST["limit"]);
echo dataReader::getJsonData($temp, $cnt, $_GET["callback"]);
die;
}
public static function Get($where = "", $param = array())
{
return PdoDataAccess::runquery_fetchMode("select TemplateID, TemplateTitle from " . static::TableName . " where 1=1 " . $where, $param);
}
function SelectMyMessages()
{
if ($_REQUEST["mode"] == "receive") {
$query = "select m.*,r.* ,concat(fname,' ',lname) FromPersonName, substr(MsgDate,1,10) _MsgDate\n\t\t\tfrom OFC_messages m join OFC_MessageReceivers r using(MessageID)\n\t\t\t\tjoin BSC_persons p on(m.PersonID=p.PersonID)\n\t\t\twhere r.PersonID=:p";
$query .= isset($_REQUEST["deleted"]) && $_REQUEST["deleted"] == "true" ? " AND r.IsDeleted='YES'" : " AND r.IsDeleted='NO'";
} else {
$query = "select m.*,r.* ,concat(fname,' ',lname) ToPersonName, substr(MsgDate,1,10) _MsgDate\n\t\t\tfrom OFC_messages m join OFC_MessageReceivers r using(MessageID)\n\t\t\tjoin BSC_persons p on(r.PersonID=p.PersonID)\n\t\t\twhere m.PersonID=:p";
$query .= isset($_REQUEST["deleted"]) && $_REQUEST["deleted"] == "true" ? " AND m.IsDeleted='YES'" : " AND m.IsDeleted='NO'";
}
$param = array(":p" => $_SESSION["USER"]["PersonID"]);
$dt = PdoDataAccess::runquery_fetchMode($query, $param);
print_r(ExceptionHandler::PopAllExceptions());
$cnt = $dt->rowCount();
$dt = PdoDataAccess::fetchAll($dt, $_GET["start"], $_GET["limit"]);
echo dataReader::getJsonData($dt, $cnt, $_GET["callback"]);
die;
}
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;
}
public static function Get($where = '', $whereParams = array())
{
$query = "select c.*,cc.CostCode , \r\n\t\t\t\tconcat_ws(' - ',b1.BlockDesc,b2.BlockDesc,b3.BlockDesc) CostCodeDesc\r\n\t\t\tfrom WAR_costs c\r\n\t\t\tjoin ACC_CostCodes cc on(c.CostCodeID=cc.CostID)\r\n\t\t\tjoin ACC_blocks b1 on(level1=b1.BlockID)\r\n\t\t\tleft join ACC_blocks b2 on(level2=b2.BlockID)\r\n\t\t\tleft join ACC_blocks b3 on(level3=b3.BlockID)\r\n\t\t\twhere 1=1 " . $where;
return PdoDataAccess::runquery_fetchMode($query, $whereParams);
}
