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