/**
* @test
* @covers Cocur\NQM\QueryHelper::convertParameters()
*/
public function convertParametersAddsColonToParameter()
{
$params = QueryHelper::convertParameters(['foo' => 'bar', 'qoo' => 'baz']);
$this->assertArrayHasKey(':foo', $params);
$this->assertArrayHasKey(':qoo', $params);
$this->assertArrayNotHasKey('foo', $params);
$this->assertArrayNotHasKey('qoo', $params);
}
function summary_doc_list()
{
$query = " DROP TABLE if EXISTS tmp_doc_writs ";
PdoDataAccess::runquery($query);
$query = " CREATE TABLE tmp_doc_writs\r\n (\r\n auto_id int(11) NOT NULL auto_increment,\r\n writ_id int(11) default NULL,\r\n writ_ver smallint(6) default NULL,\r\n staff_id int(11) default NULL,\r\n annual_effect smallint(1) default NULL,\r\n show_in_summary_doc smallint(1) default NULL,\r\n execute_date datetime NULL ,\r\n PRIMARY KEY (auto_id)\r\n )type=MYISAM AS\r\n SELECT\r\n w.writ_id ,\r\n w.writ_ver ,\r\n w.staff_id ,\r\n w.annual_effect ,\r\n wst.show_in_summary_doc ,\r\n w.execute_date\r\n FROM writs w\r\n INNER JOIN writ_subtypes wst\r\n ON w.writ_type_id = wst.writ_type_id AND\r\n w.writ_subtype_id = wst.writ_subtype_id AND\r\n w.person_type = wst.person_type\r\n WHERE\r\n (w.history_only=0 OR w.history_only IS NULL) AND\r\n w.person_type in(" . manage_access::getValidPersonTypes() . ")\r\n ORDER BY staff_id , execute_date , writ_id , writ_ver' ";
PdoDataAccess::runquery($query);
$query = "ALTER TABLE tmp_doc_writs ADD INDEX(writ_id,writ_ver)";
PdoDataAccess::runquery($query);
$query = "ALTER TABLE tmp_doc_writs ADD INDEX(staff_id,execute_date,writ_id,writ_ver)";
PdoDataAccess::runquery($query);
$query = " DELETE tmp_doc_writs sw1\r\n FROM tmp_doc_writs sw1\r\n INNER JOIN tmp_doc_writs sw2\r\n ON sw1.staff_id = sw2.staff_id AND\r\n sw1.execute_date = sw2.execute_date\r\n WHERE (sw1.writ_id > sw2.writ_id OR (sw1.writ_id = sw2.writ_id AND sw1.writ_ver > sw2.writ_ver))";
PdoDataAccess::runquery($query);
$query = "DROP TABLE if EXISTS tmp_sum_doc_writs";
PdoDataAccess::runquery($query);
$query = " CREATE TABLE tmp_sum_doc_writs\r\n (\r\n auto_id int(11) NOT NULL auto_increment,\r\n writ_id int(11) default NULL,\r\n writ_ver smallint(6) default NULL,\r\n staff_id int null,\r\n execute_date datetime null ,\r\n annual_effect smallint null ,\r\n show_in_summary_doc smallint null ,\r\n PRIMARY KEY (auto_id)\r\n )type=MYISAM AS\r\n SELECT w1.writ_id , w1.writ_ver , w1.staff_id , w1.execute_date execute_date , w1.annual_effect , w1.show_in_summary_doc\r\n FROM tmp_doc_writs w1\r\n LEFT OUTER JOIN tmp_doc_writs w0\r\n ON w1.auto_id - 1 = w0.auto_id AND w1.staff_id = w0.staff_id\r\n LEFT OUTER JOIN tmp_doc_writs w2\r\n ON w1.auto_id + 1 = w2.auto_id AND w1.staff_id = w2.staff_id\r\n WHERE ((w1.annual_effect <> w0.annual_effect OR w0.writ_id is null)OR(w1.show_in_summary_doc=1))\r\n ORDER by w1.staff_id ";
PdoDataAccess::runquery($query);
$query = "DROP TABLE IF EXISTS temp_writs";
PdoDataAccess::runquery($query);
$query = " CREATE TABLE temp_writs TYPE=MyIsam AS\r\n SELECT wa.writ_type_id ,\r\n wa.writ_subtype_id ,\r\n wa.staff_id,\r\n wa.emp_mode,\r\n wa.annual_effect,\r\n wa.science_level,\r\n wa.execute_date,\r\n dw2.execute_date end_date ,\r\n wa.writ_id,\r\n wa.writ_ver\r\n FROM staff s\r\n INNER JOIN writs w\r\n ON w.writ_id = s.last_writ_id AND w.writ_ver = s.last_writ_ver AND w.staff_id = s.staff_id \r\n INNER JOIN writs wa\r\n ON wa.staff_id = s.staff_id\r\n INNER JOIN tmp_sum_doc_writs dw\r\n ON(dw.writ_id = wa.writ_id AND dw.writ_ver = wa.writ_ver)\r\n LEFT OUTER JOIN tmp_sum_doc_writs dw2\r\n ON(dw2.auto_id -1 = dw.auto_id and dw2.staff_id = dw.staff_id)\r\n WHERE (wa.history_only = 0 OR wa.history_only IS NULL) AND\r\n s.person_type in(" . manage_access::getValidPersonTypes() . ") AND\r\n (dw.annual_effect <> " . ANNUAL_NOT_CALC . " OR dw.show_in_summary_doc=1 OR dw2.auto_id IS NOT NULL) AND\r\n '.sisSession('__sisReportWhereClause') ";
PdoDataAccess::runquery($query);
$query = " ALTER TABLE temp_writs ADD INDEX(writ_id,writ_ver) ";
PdoDataAccess::runquery($query);
$query = " ALTER TABLE temp_writs ADD INDEX(staff_id,execute_date,writ_id,writ_ver) ";
PdoDataAccess::runquery($query);
$query = " DELETE temp_writs tw1\r\n FROM temp_writs tw1\r\n INNER JOIN temp_writs tw2\r\n ON tw1.staff_id = tw2.staff_id AND\r\n tw1.execute_date = tw2.execute_date\r\n WHERE (tw1.writ_id > tw2.writ_id OR (tw1.writ_id = tw2.writ_id AND tw1.writ_ver > tw2.writ_ver)) ";
PdoDataAccess::runquery($query);
$query = "DROP TABLE IF EXISTS temp_person_doc";
PdoDataAccess::runquery($query);
$query = "CREATE TABLE temp_person_doc TYPE=MyIsam AS\r\n SELECT w.staff_id,\r\n w.science_level,\r\n w.execute_date first_date,\r\n CASE\r\n WHEN w.end_date IS NULL THEN NOW()\r\n ELSE w.end_date\r\n END last_date,\r\n CASE w.annual_effect\r\n WHEN " . ANNUAL_NOT_CALC . " THEN 1\r\n ELSE 2\r\n END accept_id,\r\n writ_id twrit_id ,\r\n writ_ver twrit_ver\r\n FROM temp_writs w ";
PdoDataAccess::runquery($query);
$query = "ALTER TABLE temp_person_doc ADD INDEX(twrit_id)";
PdoDataAccess::runquery($query);
$query = "DROP TABLE IF EXISTS temp_history";
PdoDataAccess::runquery($query);
$query = " CREATE TABLE temp_history TYPE=MyIsam AS\r\n (SELECT 1 AS rowno,\r\n p.PersonID,\r\n NULL AS post_title,\r\n p.military_from_date AS from_date,\r\n p.military_to_date AS to_date,\r\n (p.military_duration DIV 12) AS d_year,\r\n (p.military_duration - ((p.military_duration DIV 12) * 12)) AS d_month,\r\n 0 AS d_day,\r\n \\'قابل قبول\\' AS accept_type,\r\n \\'سربازي\\' AS org_title,\r\n \\'سربازي\\' AS emp_state_title,\r\n NULL AS writ_id,\r\n NULL AS writ_date\r\n FROM persons p\r\n WHERE p.military_duration IS NOT NULL AND\r\n p.military_from_date IS NOT NULL AND\r\n p.military_to_date IS NOT NULL)\r\n\t\t\t\tUNION\r\n (SELECT 2 AS rowno,\r\n pe.PersonID,\r\n pe.title AS post_title,\r\n pe.from_date,\r\n pe.to_date,\r\n retired_duration_year AS d_year,\r\n retired_duration_month AS d_month,\r\n retired_duration_day AS d_day,\r\n \\'قابل قبول\\' AS accept_type,\r\n pe.organization AS org_title,\r\n emps.Title emp_state_title,\r\n NULL AS writ_id,\r\n NULL AS writ_date\r\n FROM person_employments pe " . QueryHelper::makeBasicInfoJoin(BINFTYPE_emp_state, "emps", "pe.emp_state") . " )\r\n\t\t\t\tUNION\r\n (SELECT 3 AS rowno,\r\n pe.PersonID,\r\n pe.title AS post_title,\r\n pe.from_date,\r\n pe.to_date,\r\n FLOOR(((duration_year - retired_duration_year)*365.25 + (duration_month - retired_duration_month)*30.4375 + (duration_day - retired_duration_day)) / 365.25) AS d_year,\r\n FLOOR((((duration_year - retired_duration_year)*365.25 + (duration_month - retired_duration_month)*30.4375 + (duration_day - retired_duration_day)) -FLOOR(((duration_year - retired_duration_year)*365.25 + (duration_month - retired_duration_month)*30.4375 + (duration_day - retired_duration_day)) / 365.25)*365.25) / 30.4375) AS d_month ,\r\n (((duration_year - retired_duration_year)*365.25 + (duration_month - retired_duration_month)*30.4375 + (duration_day - retired_duration_day))-\r\n FLOOR(((duration_year - retired_duration_year)*365.25 + (duration_month - retired_duration_month)*30.4375 + (duration_day - retired_duration_day)) / 365.25) *365.25\r\n -ROUND(FLOOR((((duration_year - retired_duration_year)*365.25 + (duration_month - retired_duration_month)*30.4375 + (duration_day - retired_duration_day)) -FLOOR(((duration_year - retired_duration_year)*365.25 + (duration_month - retired_duration_month)*30.4375 + (duration_day - retired_duration_day)) / 365.25)*365.25) / 30.4375)*30.4375)) AS d_day,\r\n \\'غير قابل قبول\\' AS accept_type,\r\n pe.organization AS org_title,\r\n emps.Title emp_state_title , \r\n NULL AS writ_id,\r\n NULL AS writ_date\r\n FROM person_employments pe " . QueryHelper::makeBasicInfoJoin(BINFTYPE_emp_state, "emps", "pe.emp_state") . " )\r\n\t\t\t\tUNION\r\n (SELECT\r\n 4 AS rowno,\r\n s.PersonID,\r\n CASE\r\n WHEN w.person_type =" . HR_PROFESSOR . " THEN slvl.Title\r\n WHEN w.person_type =" . HR_WORKER . " THEN j.title\r\n ELSE p.title\r\n END post_title ,\r\n pd.first_date AS from_date,\r\n pd.last_date AS to_date,\r\n FLOOR(DATEDIFF(pd.last_date,pd.first_date) / 365.25) AS d_year,\r\n FLOOR( ( DATEDIFF(pd.last_date,pd.first_date) - FLOOR(DATEDIFF(pd.last_date,pd.first_date) / 365.25)*365.25 ) / 30.4375 ) AS d_month,\r\n DATEDIFF(pd.last_date,pd.first_date) - FLOOR(DATEDIFF(pd.last_date,pd.first_date) / 365.25)*365.25 - FLOOR( ( DATEDIFF(pd.last_date,pd.first_date) - FLOOR(DATEDIFF(pd.last_date,pd.first_date) / 365.25)*365.25 ) / 30.4375 )*30.4375 AS d_day,\r\n CASE pd.accept_id\r\n WHEN 1 THEN \\'غير قابل قبول\\'\r\n ELSE \\'قابل قبول\\'\r\n END AS accept_type,\r\n CASE pd.accept_id\r\n WHEN 1 THEN empd.Title\r\n ELSE o.ptitle\r\n END AS org_title,\r\n emps.Title emp_state_title,\r\n w.send_letter_no AS writ_id,\r\n w.execute_date AS writ_date\r\n FROM temp_person_doc pd\r\n INNER JOIN writs w\r\n ON(w.writ_id = pd.twrit_id and w.writ_ver=pd.twrit_ver)\r\n INNER JOIN staff s\r\n ON(w.staff_id = s.staff_id)\r\n LEFT OUTER JOIN org_new_units o\r\n ON(w.ouid = o.ouid)\r\n LEFT OUTER JOIN position p\r\n ON p.post_id = w.post_id\r\n LEFT OUTER JOIN jobs j\r\n ON w.job_id = j.job_id " . QueryHelper::makeBasicInfoJoin(BINFTYPE_science_level, "slvl", "w.science_level") . QueryHelper::makeBasicInfoJoin(BINFTYPE_emp_mode, "empd", "w.emp_mode") . QueryHelper::makeBasicInfoJoin(BINFTYPE_emp_state, "emps", "w.emp_state") . " WHERE w.history_only = 0) ";
//..............................................................................
$query = " DROP TABLE IF EXISTS temp_exe_post ";
PdoDataAccess::runquery($query);
$query = " CREATE TABLE temp_exe_post Type=MyIsam AS\r\n SELECT staff_id,SUBSTRING(MAX(CONCAT(from_date , row_no)),11) max_row\r\n FROM professor_exe_posts\r\n GROUP BY staff_id";
PdoDataAccess::runquery($query);
$query = " ALTER TABLE temp_exe_post ADD INDEX(staff_id,max_row) ";
PdoDataAccess::runquery($query);
$query = "DROP TABLE IF EXISTS temp_devotion ";
PdoDataAccess::runquery($query);
$query = " CREATE TABLE temp_devotion Type=MyIsam AS\r\n\t\t\t\t\tSELECT\r\n\t\t\t\t\t PersonID,\r\n\t\t\t\t\t SUM(CASE devotion_type\r\n\t\t\t\t\t WHEN " . FIGHTING_DEVOTION . " THEN amount\r\n\t\t\t\t\t ELSE 0\r\n\t\t\t\t\t END) razm,\r\n\t\t\t\t\t MAX(CASE devotion_type\r\n\t\t\t\t\t WHEN " . SACRIFICE_DEVOTION . " THEN amount\r\n\t\t\t\t\t ELSE 0\r\n\t\t\t\t\t END) janbaz\r\n\t\t\t\t\tFROM person_devotions\r\n\t\t\t\t\tWHERE devotion_type IN(" . FIGHTING_DEVOTION . "," . SACRIFICE_DEVOTION . ")\r\n\t\t\t\t\tGROUP BY PersonID ";
PdoDataAccess::runquery($query);
$query = "ALTER TABLE temp_devotion ADD INDEX(PersonID)";
PdoDataAccess::runquery($query);
$query = " SELECT s.staff_id,\r\n p.plname,\r\n p.pfname,\r\n p.father_name ,\r\n p.birth_date ,\r\n idcard_no ,\r\n national_code ,\r\n concat(st.ptitle ,'-',ct.ptitle ) birth_place ,\r\n w.base ,\r\n sf.ptitle AS field_title,\r\n sb.ptitle AS branch_title,\r\n pe.doc_date,\r\n c.ptitle AS country_title,\r\n u.ptitle AS university_title,\r\n td.razm ,\r\n td.janbaz,\r\n o.ptitle AS unit_title,\r\n o.ouid ,\r\n po.post_no,\r\n po.title AS last_post_title,\r\n po2.title AS exe_post_title,\r\n pep.from_date AS exe_post_from,\r\n slvl.Title science_level_title ,\r\n wtt.Title worktime_type_title ,\r\n mlt.Title military_type_title ,\r\n edl.Title education_level_title ,\r\n emps.Title emp_state_title \r\n\r\n FROM staff s \r\n\t\t\t\t\t INNER JOIN persons p\r\n\t\t\t\t\t ON(s.PersonID = p.PersonID)\r\n\t\t\t\t\t LEFT OUTER JOIN states st\r\n\t\t\t\t\t \t\tON (st.state_id = p.birth_state_id)\r\n\t\t\t\t\t LEFT OUTER JOIN cities ct\r\n\t\t\t\t\t \t\tON (ct.city_id = p.birth_city_id)\r\n\t\t\t\t\t LEFT OUTER JOIN writs w\r\n\t\t\t\t\t ON(s.last_writ_ver = w.writ_ver AND s.last_writ_id = w.writ_id)\r\n\t\t\t\t\t LEFT OUTER JOIN person_educations pe\r\n\t\t\t\t\t ON(s.PersonID = pe.PersonID AND w.education_level = pe.education_level AND w.sfid = pe.sfid AND (w.sbid = pe.sbid OR w.sbid IS NULL))\r\n\t\t\t\t\t LEFT OUTER JOIN study_fields sf\r\n\t\t\t\t\t ON(w.sfid = sf.sfid)\r\n\t\t\t\t\t LEFT OUTER JOIN study_branchs sb\r\n\t\t\t\t\t ON(w.sfid = sb.sfid AND w.sbid = sb.sbid)\r\n\t\t\t\t\t LEFT OUTER JOIN countries c\r\n\t\t\t\t\t ON(pe.country_id = c.country_id)\r\n\t\t\t\t\t LEFT OUTER JOIN universities u\r\n\t\t\t\t\t ON(pe.country_id = u.country_id AND pe.university_id = u.university_id)\r\n\t\t\t\t\t LEFT OUTER JOIN org_new_units o\r\n\t\t\t\t\t ON(w.ouid = o.ouid)\r\n\t\t\t\t\t LEFT OUTER JOIN position po\r\n\t\t\t\t\t ON(w.post_id = po.post_id)\r\n\t\t\t\t\t LEFT OUTER JOIN temp_exe_post tep\r\n\t\t\t\t\t ON(s.staff_id = tep.staff_id)\r\n\t\t\t\t\t LEFT OUTER JOIN professor_exe_posts pep\r\n\t\t\t\t\t ON(tep.staff_id = pep.staff_id AND tep.max_row = pep.row_no AND (pep.to_date IS NULL OR pep.to_date>=CURDATE()))\r\n\t\t\t\t\t LEFT OUTER JOIN position po2\r\n\t\t\t\t\t ON(pep.post_id = po2.post_id)\r\n\t\t\t\t\t LEFT OUTER JOIN temp_devotion td\r\n\t\t\t\t\t ON(s.PersonID = td.PersonID) " . QueryHelper::makeBasicInfoJoin(BINFTYPE_science_level, "slvl", "w.science_level") . QueryHelper::makeBasicInfoJoin(BINFTYPE_worktime_type, "wtt", "w.worktime_type") . QueryHelper::makeBasicInfoJoin(BINFTYPE_military_type, "mlt", "w.military_type") . QueryHelper::makeBasicInfoJoin(BINFTYPE_education_level, "edl", "w.education_level") . QueryHelper::makeBasicInfoJoin(BINFTYPE_emp_state, "emps", "w.emp_state") . " WHERE ((th.d_year<>0 OR th.d_month<>0 OR th.d_day<>0)OR(w.emp_mode IN(11,12,13,14) AND\r\n th.writ_date = w.execute_date)) AND\r\n s.person_type in(" . manage_access::getValidPersonTypes() . ") AND\r\n s.last_cost_center_id in(" . manage_access::getValidCostCenters() . ") ";
$dt = PdoDataAccess::runquery($query, $whereParam);
return $dt;
}
/**
* @param string $name
* @param array $parameters
* @param array $options
*
* @return StatementCollection
*/
public function execute($name, $parameters = [], $options = [])
{
$pdo = $this->nqm->getPdo();
$statements = new StatementCollection();
foreach ($this->getQueries($name) as $index => $query) {
$statement = $pdo->prepare($query, $options);
foreach ($this->getQueryParameters($query, QueryHelper::convertParameters($parameters)) as $key => $value) {
$statement->bindValue($key, $value);
}
$statement->execute();
$statements->add($statement);
}
return $statements;
}
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;
}
/**
* Returns the resulting table but uses the $key field as an index
*
* @abstract
* @access public
*
* @param String $table the table name
* @param String $id_col the name of the primary key column
* @param String $id the key of the dataset to clone
* @param Array $exclusion columns not to copy
*
* @return Mixed new key or FALSE
*/
function cloneDataset(&$db, $table, $id_col, $id, $exclusion = null)
{
$db->query("SELECT * FROM {$table} WHERE {$id_col}='{$id}'");
$data = $db->getNext();
if ($data) {
unset($data[$id_col]);
if (is_array($exclusion)) {
foreach ($exclusion as $key) {
unset($data[$key]);
}
}
if ($db->query(QueryHelper::insert($table, $data))) {
$res = $db->getInsertId();
if (!$res) {
$db->query(QueryHelper::select($id_col, $table, $data));
return $db->getValue();
}
return $res;
}
}
echo $db->lastQuery;
return false;
}
public function find($table, $condition = array(), $limit = array(), $order_by = array())
{
$ret = array();
$query = QueryHelper::find($this->dbcon, $table, $condition, $limit, $order_by);
if ($res = $this->exec($query)) {
$ret = $this->fetch($res);
} else {
echo mysqli_error($this->dbcon);
}
return $ret;
}
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;
}
/**
* Builds the where clause
*
* @access private
* @static
*
* @param Mixed $where
* @param Boolean $smartQuotes
*
* @returns String
*/
function _where($where, $smartQuotes = true, $separator = ',')
{
if (is_array($where)) {
$vals = '';
foreach ($where as $key => $val) {
if ($smartQuotes) {
if (is_int($val)) {
$vals .= "{$key}={$val}{$separator} ";
} else {
$vals .= "{$key}='{$val}'{$separator} ";
}
} else {
$vals .= "{$key}={$val}{$separator} ";
}
}
$len = (strlen($separator) + 1) * -1;
$vals = substr($vals, 0, $len);
return 'WHERE ' . $vals;
} else {
if ($where != '') {
return 'WHERE ' . QueryHelper::_getCommaSep($where);
}
}
}
/**
* Returns the data for one table
*
* @access public
*
* @param AbstractDB $db db identifier
* @param String $table table name
* @param Integer $type ANYDB_DUMP_ constants
* @param String $seperator for csv files
*
* @returns Array the table data
*/
function getTableData(&$db, $table, $type = ANYDB_DUMP_SQL, $seperator = "\t")
{
$res = '';
$first = true;
// get all the data
$query = "SELECT * FROM {$table}";
$db->query($query, ANYDB_RES_ASSOC);
while ($line = $db->getNext()) {
$line = $db->escapeStr($line);
switch ($type) {
case ANYDB_DUMP_SQL:
$res .= QueryHelper::insert($table, $line) . ";\n";
break;
case ANYDB_DUMP_CSV:
if ($first) {
$res .= implode($seperator, array_keys($line)) . "\n";
$first = false;
}
$res .= implode($seperator, $line) . "\n";
break;
}
}
return $res;
}
/**
* @param \DateTime $begin
* @param \DateTime $end
* @param array $options
*
* @return \Doctrine\ORM\QueryBuilder
*/
public function getEventsQueryBuilder(\DateTime $begin, \DateTime $end, array $options = array())
{
return QueryHelper::addEventQuery($this->createQueryBuilderForGetEvent($options), $this->getBeginFieldName(), $this->getEndFieldName(), $begin, $end);
}
public function find($table, $condition = array(), $limit = array(), $order_by = array())
{
$ret = array();
$query = QueryHelper::find($table, $condition, $limit, $order_by);
if ($res = $this->exec($query)) {
$ret = $this->fetch($res);
} else {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
return $ret;
}
/**
* Builds the where clause
*
* @access private
* @static
*
* @param Mixed $where
* @param Boolean $smartQuotes
*
* @returns String
*/
function _where($where, $smartQuotes = true)
{
if (is_array($where)) {
$vals = '';
foreach ($where as $key => $val) {
if ($smartQuotes) {
if (is_int($val)) {
$vals .= "{$key}={$val}, ";
} else {
$vals .= "{$key}='{$val}', ";
}
} else {
$vals .= "{$key}={$val}, ";
}
}
$vals = substr($vals, 0, -2);
return 'WHERE ' . $vals;
} else {
if ($where != '') {
return 'WHERE ' . QueryHelper::_getCommaSep($where);
}
}
}
$whereParam = array_merge($whereParam, $org["param"]);
}
} else {
$QUERY_WHERE .= " AND " . $base_field . " = :f" . $i;
$whereParam[":f" . $i] = $_POST["SELECT:" . $column_id];
}
}
break;
case "CHECK":
if ($_POST["CHECK:" . $column_id] != "-1") {
$QUERY_WHERE .= " AND " . $field_name . " = :t" . $column_id;
$whereParam[":t" . $column_id] = $_POST["CHECK:" . $column_id];
}
break;
case "CHECKLIST":
$st = QueryHelper::makeWhereOfCheckboxList($base_field, "CHECKLIST:" . $columns[$i]["field_name"] . ":");
$QUERY_WHERE .= $st != "" ? " AND " . $st : "";
break;
}
}
for ($i = 0; $i < count($formula_columns); $i++) {
$QUERY_SELECT = str_replace("[0:" . $formula_columns[$i][0] . "]", $formula_columns[$i][1], $QUERY_SELECT);
$QUERY_SELECT = str_replace("[1:" . $formula_columns[$i][0] . "]", $formula_columns[$i][2], $QUERY_SELECT);
}
$QUERY_SELECT = substr($QUERY_SELECT, 0, strlen($QUERY_SELECT) - 1);
$QUERY_GROUP = $QUERY_GROUP != "" ? substr($QUERY_GROUP, 0, strlen($QUERY_GROUP) - 1) : "";
$QUERY_ORDER = $seprationOrder . $QUERY_ORDER;
$QUERY_ORDER = $QUERY_ORDER != "" ? substr($QUERY_ORDER, 0, strlen($QUERY_ORDER) - 1) : "";
//------------------------------------------------------------------------------
preg_match_all('|#([^#].*)#|U', $QUERY_WHERE, $requestParams);
for ($i = 0; $i < count($requestParams[0]); $i++) {
function selectAllPosts()
{
require_once inc_manage_post;
$where = "1=1";
$whereParam = array();
//-----------------------
if (!empty($_POST["post_no"])) {
$where .= " AND p.post_no=:pno";
$whereParam[":pno"] = $_POST["post_no"];
}
if (!empty($_POST["post_type"]) && $_POST["post_type"] != -1) {
$where .= " AND p.post_type=:ptype";
$whereParam[":ptype"] = $_POST["post_type"];
}
if (!empty($_POST["title"])) {
$where .= " AND p.title like :title";
$whereParam[":title"] = "%" . $_POST["title"] . "%";
}
if (!empty($_POST["ouid"]) && $_POST["ouid"] != -1) {
$result = QueryHelper::MK_org_units($_POST["ouid"], isset($_POST["sub_units"]) ? true : false);
$where .= " AND " . $result["where"];
$whereParam = array_merge($whereParam, $result["param"]);
}
$where .= dataReader::makeOrder();
$temp = manage_posts::GetAllPosts($where, $whereParam);
$no = count($temp);
$temp = array_slice($temp, $_GET["start"], $_GET["limit"]);
for ($i = 0; $i < count($temp); $i++) {
if ($temp[$i]["ouid"] != "") {
$temp[$i]["full_unit_title"] = manage_units::get_full_title($temp[$i]["ouid"]);
} else {
$temp[$i]["full_unit_title"] = "";
}
}
echo dataReader::getJsonData($temp, $no, $_GET["callback"]);
die;
}
function ProcessArrearPayment()
{
$payArrCalcObj = new manage_arrear_pay_calculation();
$payArrCalcObj->__YEAR = $_POST["pay_year"];
$payArrCalcObj->__MONTH = 12;
$start_date = $_POST["pay_year"] . "/01/01";
$end_date = $_POST["pay_year"] . "/12/" . DateModules::DaysOfMonth($payArrCalcObj->__YEAR, 12);
$payArrCalcObj->__CALC_NORMALIZE_TAX = isset($_POST['tax_normalize']) ? "1" : "0";
$payArrCalcObj->__START_NORMALIZE_TAX_MONTH = 1;
$payArrCalcObj->__START_NORMALIZE_TAX_YEAR = $_POST["pay_year"];
$payArrCalcObj->__BACKPAY_BEGIN_FROM = 1;
$payArrCalcObj->month_start = DateModules::shamsi_to_miladi($start_date);
$payArrCalcObj->month_end = DateModules::shamsi_to_miladi($end_date);
$payArrCalcObj->__MSG = $_POST["message"];
// <editor-fold defaultstate="collapsed" desc="Create Where" >
$where = "1=1";
$whereParam = array();
if (!empty($_POST["person_type"])) {
$where .= " AND p.person_type=:ptype";
$whereParam[":ptype"] = $_POST["person_type"];
}
if (!empty($_POST["from_staff_id"])) {
$where .= " AND s.staff_id >= :fsid";
$whereParam[":fsid"] = $_POST["from_staff_id"];
}
if (!empty($_POST["to_staff_id"])) {
$where .= " AND s.staff_id <= :tsid";
$whereParam[":tsid"] = $_POST["to_staff_id"];
}
if (!empty($_POST["from_cost_center_id"])) {
$where .= " AND w.cost_center_id >= :fccid";
$whereParam[":fccid"] = $_POST["from_cost_center_id"];
}
if (!empty($_POST["to_cost_center_id"])) {
$where .= " AND w.cost_center_id <= :tccid";
$whereParam[":tccid"] = $_POST["to_cost_center_id"];
}
if (!isset($_POST["ouid"])) {
$result = QueryHelper::MK_org_units($_POST["ouid"], true);
$where .= " AND " . $result["where"];
$whereParams = array_merge($whereParam, $result["param"]);
}
// </editor-fold>
$payArrCalcObj->__WHERE = $where;
$payArrCalcObj->__WHEREPARAM = $whereParam;
$res = $payArrCalcObj->run_back();
if (!$res) {
echo Response::createObjectiveResponse(false, ExceptionHandler::popExceptionDescription());
die;
} else {
echo Response::createObjectiveResponse(true, $payArrCalcObj->success_counter . "_" . $payArrCalcObj->fail_counter);
die;
}
}
public function find($table, $condition = array(), $limit = array(), $order_by = array())
{
$query = QueryHelper::find($table, $condition, $limit, $order_by);
$res = $this->exec($query);
return $this->fetch($res);
}
