public void createPssblValsForLov(int lovID, string pssblVal, string pssblValDesc, bool isEnbld, string allwd) { string dateStr = cmnCde.getDB_Date_time(); string sqlStr = "INSERT INTO gst.gen_stp_lov_values(" + "value_list_id, pssbl_value, pssbl_value_desc, " + "created_by, creation_date, last_update_by, last_update_date, is_enabled, allowed_org_ids) " + "VALUES (" + lovID + ", '" + pssblVal.Replace("'", "''") + "', '" + pssblValDesc.Replace("'", "''") + "', " + cmnCde.User_id + ", '" + dateStr + "', " + cmnCde.User_id + ", '" + dateStr + "', '" + cmnCde.cnvrtBoolToBitStr(isEnbld) + "', '" + allwd.Replace("'", "''") + "')"; cmnCde.insertDataNoParams(sqlStr); }
public void createTaxRec(int orgid, string codename, string codedesc, string itmTyp, bool isEnbld, int taxAcntID , int expnsAcntID, int rvnuAcntID, string sqlFormular, bool isTxRcvrbl, int txExpAccID, int prchDscAccID, int chrgExpAccID, bool isWthHldng) { string dateStr = cmnCde.getDB_Date_time(); string insSQL = "INSERT INTO scm.scm_tax_codes(" + "code_name, code_desc, created_by, creation_date, last_update_by, " + "last_update_date, itm_type, is_enabled, taxes_payables_accnt_id, " + "dscount_expns_accnt_id, " + "chrge_revnu_accnt_id, " + @"org_id, sql_formular, is_recovrbl_tax, tax_expense_accnt_id, prchs_dscnt_accnt_id, chrge_expns_accnt_id, is_withldng_tax) " + "VALUES ('" + codename.Replace("'", "''") + "', '" + codedesc.Replace("'", "''") + "', " + cmnCde.User_id + ", '" + dateStr + "', " + cmnCde.User_id + ", '" + dateStr + "', '" + itmTyp.Replace("'", "''") + "', '" + cmnCde.cnvrtBoolToBitStr(isEnbld) + "', " + taxAcntID + ", " + expnsAcntID + ", " + rvnuAcntID + ", " + orgid + ", '" + sqlFormular.Replace("'", "''") + "', '" + cmnCde.cnvrtBoolToBitStr(isTxRcvrbl) + "', " + txExpAccID + ", " + prchDscAccID + ", " + chrgExpAccID + ", '" + cmnCde.cnvrtBoolToBitStr(isWthHldng) + "')"; cmnCde.insertDataNoParams(insSQL); }
private long[] getPrsnsInvolved() { string dateStr = cmnCde.getDB_Date_time(); string extrWhr = ""; if (long.Parse(this.cstmrIDTextBox.Text) > 0) { extrWhr += " and (Select distinct z.lnkd_firm_org_id From prs.prsn_names_nos z where z.person_id=a.person_id)=" + this.cstmrIDTextBox.Text; } if (long.Parse(this.cstmrSiteIDTextBox.Text) > 0) { extrWhr += " and (Select distinct z.lnkd_firm_site_id From prs.prsn_names_nos z where z.person_id=a.person_id)=" + this.cstmrSiteIDTextBox.Text; } string grpSQL = ""; if (this.grpComboBox.Text == "Divisions/Groups") { grpSQL = "Select distinct a.person_id From pasn.prsn_divs_groups a Where ((a.div_id = " + int.Parse(this.grpNmIDTextBox.Text) + ") and (to_timestamp('" + dateStr + "','YYYY-MM-DD HH24:MI:SS') between to_timestamp(a.valid_start_date|| ' 00:00:00','YYYY-MM-DD HH24:MI:SS') " + "AND to_timestamp(a.valid_end_date || ' 23:59:59','YYYY-MM-DD HH24:MI:SS'))" + extrWhr + ") ORDER BY a.person_id"; } else if (this.grpComboBox.Text == "Grade") { grpSQL = "Select distinct a.person_id From pasn.prsn_grades a Where ((a.grade_id = " + int.Parse(this.grpNmIDTextBox.Text) + ") and (to_timestamp('" + dateStr + "','YYYY-MM-DD HH24:MI:SS') between to_timestamp(a.valid_start_date|| ' 00:00:00','YYYY-MM-DD HH24:MI:SS') " + "AND to_timestamp(a.valid_end_date || ' 23:59:59','YYYY-MM-DD HH24:MI:SS'))" + extrWhr + ") ORDER BY a.person_id"; } else if (this.grpComboBox.Text == "Job") { grpSQL = "Select distinct a.person_id From pasn.prsn_jobs a Where ((a.job_id = " + int.Parse(this.grpNmIDTextBox.Text) + ") and (to_timestamp('" + dateStr + "','YYYY-MM-DD HH24:MI:SS') between to_timestamp(a.valid_start_date|| ' 00:00:00','YYYY-MM-DD HH24:MI:SS') " + "AND to_timestamp(a.valid_end_date || ' 23:59:59','YYYY-MM-DD HH24:MI:SS'))" + extrWhr + ") ORDER BY a.person_id"; } else if (this.grpComboBox.Text == "Position") { grpSQL = "Select distinct a.person_id From pasn.prsn_positions a Where ((a.position_id = " + int.Parse(this.grpNmIDTextBox.Text) + ") and (to_timestamp('" + dateStr + "','YYYY-MM-DD HH24:MI:SS') between to_timestamp(a.valid_start_date|| ' 00:00:00','YYYY-MM-DD HH24:MI:SS') " + "AND to_timestamp(a.valid_end_date || ' 23:59:59','YYYY-MM-DD HH24:MI:SS'))" + extrWhr + ") ORDER BY a.person_id"; } else if (this.grpComboBox.Text == "Site/Location") { grpSQL = "Select distinct a.person_id From pasn.prsn_locations a Where ((a.location_id = " + int.Parse(this.grpNmIDTextBox.Text) + ") and (to_timestamp('" + dateStr + "','YYYY-MM-DD HH24:MI:SS') between to_timestamp(a.valid_start_date|| ' 00:00:00','YYYY-MM-DD HH24:MI:SS') " + "AND to_timestamp(a.valid_end_date || ' 23:59:59','YYYY-MM-DD HH24:MI:SS'))" + extrWhr + ") ORDER BY a.person_id"; } else if (this.grpComboBox.Text == "Person Type") { grpSQL = "Select distinct a.person_id From pasn.prsn_prsntyps a, prs.prsn_names_nos b " + "Where ((a.person_id = b.person_id) and (b.org_id = " + cmnCde.Org_id + ") and (a.prsn_type = '" + this.grpNmTextBox.Text.Replace("'", "''") + "') and (to_timestamp('" + dateStr + "','YYYY-MM-DD HH24:MI:SS') between to_timestamp(a.valid_start_date|| ' 00:00:00','YYYY-MM-DD HH24:MI:SS') " + "AND to_timestamp(a.valid_end_date || ' 23:59:59','YYYY-MM-DD HH24:MI:SS'))" + extrWhr + ") ORDER BY a.person_id"; } else if (this.grpComboBox.Text == "Working Hour Type") { grpSQL = "Select distinct a.person_id From pasn.prsn_work_id a Where ((a.work_hour_id = " + int.Parse(this.grpNmIDTextBox.Text) + ") and (to_timestamp('" + dateStr + "','YYYY-MM-DD HH24:MI:SS') between to_timestamp(a.valid_start_date|| ' 00:00:00','YYYY-MM-DD HH24:MI:SS') " + "AND to_timestamp(a.valid_end_date || ' 23:59:59','YYYY-MM-DD HH24:MI:SS'))" + extrWhr + ") ORDER BY a.person_id"; } else if (this.grpComboBox.Text == "Gathering Type") { grpSQL = "Select distinct a.person_id From pasn.prsn_gathering_typs a Where ((a.gatherng_typ_id = " + int.Parse(this.grpNmIDTextBox.Text) + ") and (to_timestamp('" + dateStr + "','YYYY-MM-DD HH24:MI:SS') between to_timestamp(a.valid_start_date|| ' 00:00:00','YYYY-MM-DD HH24:MI:SS') " + "AND to_timestamp(a.valid_end_date || ' 23:59:59','YYYY-MM-DD HH24:MI:SS'))" + extrWhr + ") ORDER BY a.person_id"; } else if (this.grpComboBox.Text == "Everyone") { grpSQL = "Select distinct a.person_id From prs.prsn_names_nos a Where ((a.org_id = " + cmnCde.Org_id + ")" + extrWhr + ") ORDER BY a.person_id"; } else { grpSQL = "Select distinct a.person_id From prs.prsn_names_nos a Where ((a.person_id = " + this.grpNmIDTextBox.Text + ")" + extrWhr + ") ORDER BY a.person_id"; } DataSet dtst = cmnCde.selectDataNoParams(grpSQL); this.prsnIDs = new long[dtst.Tables[0].Rows.Count]; for (int i = 0; i < dtst.Tables[0].Rows.Count; i++) { this.prsnIDs[i] = long.Parse(dtst.Tables[0].Rows[i][0].ToString()); } return(this.prsnIDs); }