public sqlCommand(string sqlQuery) { DB2UdbCommand.Connection = conn_i5; DB2UdbCommand.CommandText = sqlQuery; DB2UdbCommand.CommandTimeout = 0; DB2UdbCommand.DeriveParameters(); }
private Boolean checkValidClient(iDB2Connection DBConnection, DataRow myDR, Int32 rowCnt) { if (myDR[0].ToString() == null || myDR[0].ToString() == "" || myDR[0].ToString() == " " || myDR[2].ToString() == null || myDR[2].ToString() == "" || myDR[2].ToString() == " ") { dgvDetail.Rows[rowCnt].Cells[0].Style.BackColor = Color.Red; dgvDetail.Rows[rowCnt].Cells[1].Style.BackColor = Color.Red; dgvDetail.Rows[rowCnt].Cells[2].Style.BackColor = Color.Red; Application.DoEvents(); return(false); } try { iDB2Command cmd = DBConnection.CreateCommand(); cmd.CommandText = string.Format("SELECT CLTSTS FROM {0}.CLTMST WHERE CLTCAS = @CLIENT_ID", SCHEMA); cmd.DeriveParameters(); cmd.Parameters["@CLIENT_ID"].Value = myDR[0].ToString(); iDB2DataReader db2r = cmd.ExecuteReader(CommandBehavior.Default); DataTable myDT = new DataTable(); myDT.Load(db2r); if (myDT.Rows.Count > 0) { if (myDT.Rows[0][0].ToString() == "A") { dgvDetail.Rows[rowCnt].Cells[0].Style.BackColor = Color.LightGreen; dgvDetail.Rows[rowCnt].Cells[1].Style.BackColor = Color.LightGreen; dgvDetail.Rows[rowCnt].Cells[2].Style.BackColor = Color.LightGreen; Application.DoEvents(); return(true); } else { dgvDetail.Rows[rowCnt].Cells[0].Style.BackColor = Color.Red; dgvDetail.Rows[rowCnt].Cells[1].Style.BackColor = Color.Red; dgvDetail.Rows[rowCnt].Cells[2].Style.BackColor = Color.Red; Application.DoEvents(); return(false); } } else { dgvDetail.Rows[rowCnt].Cells[0].Style.BackColor = Color.Red; dgvDetail.Rows[rowCnt].Cells[1].Style.BackColor = Color.Red; dgvDetail.Rows[rowCnt].Cells[2].Style.BackColor = Color.Red; Application.DoEvents(); return(false); } } catch (Exception) { throw; } }
//Mark the Status to Done "D" on FSTINV private void SetInvoiceToDone(iDB2Command db2Command, Invoice inv) { st.insertLog("Set " + inv.SIID + " invoice to done", "info", inv.SIID.ToString(), LogId); using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction(IsolationLevel.Chaos)) { db2Command.Transaction = db2Transaction; db2Command.CommandText = string.Format("UPDATE FSTINV SET SISTAT = @status WHERE SIID = {0}", inv.SIID); db2Command.DeriveParameters(); db2Command.Parameters["@status"].Value = 'D'; // st.insertLog(db2Command.CommandText, "FSTINV Update Query", "NA", LogId); var recordsAffected = db2Command.ExecuteNonQuery(); db2Command.Transaction.Commit(); db2Command.Transaction.Dispose(); st.insertLog(string.Format("Rows affected: {0}", recordsAffected), "Info", "NA", LogId); } }
public void upsertGroup(Group group) { //Create connection to the iSeries iDB2Connection cn = new iDB2Connection(); cn.ConnectionString = connectionString; //Create query iDB2Command cmd = new iDB2Command(); cmd.CommandText = "merge into issysdta.shoreteleccinboundgroupdaily as t " + "using ( values( cast(@groupId as smallint) " + ",cast(@groupName as varchar(40)) " + ",cast(@queuedCallsAboveTasa as smallint) " + ",cast(@queuedCalls as smallint) " + ",cast(@maxQueTime as integer) " + ",cast(@agentsLoggedIn as smallint) " + ",cast(@agentsIdle as smallint) " + ",cast(@acdInCalls as integer) " + ",cast(@tsf as smallint) " + ",cast(@abandonedCalls as integer) " + ")) " + "as zac(group_id " + ", group_name " + ", queued_calls_above_tasa " + ", queued_calls " + ", max_que_time " + ", agents_logged_in " + ", agents_idle " + ", acd_in_calls " + ", tsf " + ", abandoned_calls) " + "on t.group_id = zac.group_id " + "when matched then update " + "set t.group_name = zac.group_name " + ", t.queued_calls_above_tasa = zac.queued_calls_above_tasa " + ", t.queued_calls = zac.queued_calls " + ", t.max_que_time = zac.max_que_time " + ", t.agents_logged_in = zac.agents_logged_in " + ", t.agents_idle = zac.agents_idle " + ", t.acd_in_calls = zac.acd_in_calls " + ", t.tsf = zac.tsf " + ", t.abandoned_calls = zac.abandoned_calls " + "when not matched then " + "insert (group_id " + ", group_name " + ", queued_calls_above_tasa " + ", queued_calls " + ", max_que_time " + ", agents_logged_in " + ", agents_idle " + ", acd_in_calls " + ", tsf " + ", abandoned_calls) " + "values (zac.group_id " + ", zac.group_name " + ", zac.queued_calls_above_tasa " + ", zac.queued_calls " + ", zac.max_que_time " + ", zac.agents_logged_in " + ", zac.agents_idle " + ", zac.acd_in_calls " + ", zac.tsf " + ", zac.abandoned_calls) "; cmd.Connection = cn; cn.Open(); cmd.DeriveParameters(); cmd.Parameters["@groupName"].Value = group.GroupName; cmd.Parameters["@queuedCallsAboveTasa"].Value = Convert.ToInt32(group.InboundAboveTargetAverageSpeedAnswer); cmd.Parameters["@queuedCalls"].Value = Convert.ToInt32(group.InboundCallsInQueue); cmd.Parameters["@maxQueTime"].Value = Convert.ToInt32(group.InboundMaxInQueue); cmd.Parameters["@agentsLoggedIn"].Value = group.NumberOfAgents; cmd.Parameters["@agentsIdle"].Value = group.NumberIdle; cmd.Parameters["@acdInCalls"].Value = group.AutomaticCallDistribution; cmd.Parameters["@tsf"].Value = group.TargetServiceFactor; cmd.Parameters["@abandonedCalls"].Value = group.AbandonedCalls; cmd.Parameters["@groupId"].Value = group.Id; cmd.ExecuteNonQuery(); //Clean up - Close connections cmd.Dispose(); cn.Close(); }
private void MigrateUnprocessedLocations(iDB2Command db2Command) { Dictionary <string, Location> jdeLocList = new Dictionary <string, Location>(); // log.Debug("Migrating Location"); using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction()) { db2Command.Transaction = db2Transaction; db2Command.CommandText = string.Format("SELECT * FROM FSTLOCN WHERE SLSTAT != '{0}'", "P"); iDB2DataReader reader = db2Command.ExecuteReader(); while (reader.Read()) { Location temp = new Location(); temp.slan8 = ((string)reader["SLAN8"]).Trim(); temp.slpan8 = ((string)reader["SLPAN8"]).Trim(); temp.name = ((string)reader["SLNAME"]).Trim(); temp.addressStreet = ((string)reader["SLADDR"]).Trim(); temp.addressCity = ((string)reader["SLCITY"]).Trim(); temp.addressState = ((string)reader["SLST"]).Trim(); temp.addressPostalCode = ((string)reader["SLZIP"]).Trim(); temp.phoneNumber = ((string)reader["SLPHON"]).Trim(); temp.type = Char.Parse(((string)reader["SLACTN"]).Trim()); temp.officeIds = new int[] { Int32.Parse(((string)reader["SLMCU"]).Trim()) }; jdeLocList.Add(temp.slan8, temp); } reader.Close(); db2Command.Transaction.Dispose(); } if (jdeLocList.Count > 0) { // log.Info(string.Format("Found {0} locations for update", jdeLocList.Count)); foreach (var jde in jdeLocList) { // log.Debug("Migrating " + jde.Value.name); bool success = jde.Value.type == 'A' ? serviceTrade.AddLocation(jde.Value) : serviceTrade.UpdateLocation(jde.Value); if (success) { using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction(IsolationLevel.Chaos)) { db2Command.Transaction = db2Transaction; db2Command.CommandText = string.Format("UPDATE FSTLOCN SET SLSTAT = @slstat WHERE SLAN8 = @key", jde.Key); db2Command.DeriveParameters(); db2Command.Parameters["@slstat"].Value = 'P'; db2Command.Parameters["@key"].Value = jde.Key; try { db2Command.ExecuteNonQuery(); } catch (Exception ex) { // log.Error("Location update unsuccessful ", ex); } db2Command.Transaction.Dispose(); } } } } else { // log.Info("No location found for processing."); } }
private void MigrateUnprocessedCompaniesFromJDE(iDB2Command db2Command) { Dictionary <string, Company> jdeCompList = new Dictionary <string, Company>(); st.insertLog("Migrating Companies", "Info", "NA", LogId); iDB2DataReader reader; using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction()) { db2Command.Transaction = db2Transaction; db2Command.CommandText = string.Format("SELECT * FROM FSTCOMP WHERE SCSTAT != '{0}'", "P"); reader = db2Command.ExecuteReader(); while (reader.Read()) { Company temp = new Company(); temp.scan8 = (string)reader["SCAN8"]; temp.type = Char.Parse((string)reader["SCACTN"]); //Company Name temp.name = ((string)reader["SCNAME"]).Trim(); temp.addressStreet = ((string)reader["SCADDR"]).Trim(); temp.addressCity = ((string)reader["SCCITY"]).Trim(); temp.addressState = ((string)reader["SCST"]).Trim(); temp.addressPostalCode = ((string)reader["SCZIP"]).Trim(); temp.customer = Int32.Parse((string)reader["SCCUST"]) == 1 ? true : false; temp.vendor = Int32.Parse((string)reader["SCVEND"]) == 1 ? true : false; temp.phoneNumber = ((string)reader["SCPHON"]).Trim(); temp.status = ((string)reader["SCSSTS"]).Trim(); jdeCompList.Add((string)reader["SCAN8"], temp); } reader.Close(); db2Command.Transaction.Dispose(); } if (jdeCompList.Count > 0) { st.insertLog(string.Format("Found {0} companies for update", jdeCompList.Count), "Info", "NA", HillerServiceDataMigrator.LogId); foreach (var jde in jdeCompList) { st.insertLog("Migrating " + jde.Value.name, "Info", "NA", HillerServiceDataMigrator.LogId); bool success = jde.Value.type == 'A' ? serviceTrade.AddCompany(jde.Value) : serviceTrade.UpdateCompany(jde.Value); if (success) { // log.Debug("Updating Migrated Companies."); using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction(IsolationLevel.Chaos)) { db2Command.Transaction = db2Transaction; db2Command.CommandText = "UPDATE FSTCOMP SET SCSTAT = @scstat WHERE SCAN8 = @key"; db2Command.DeriveParameters(); db2Command.Parameters["@scstat"].Value = 'P'; db2Command.Parameters["@key"].Value = jde.Key; // log.Debug(db2Command.CommandText); try { db2Command.ExecuteNonQuery(); } catch (Exception ex) { // log.Error("Company update unsuccessful ", ex); } db2Command.Transaction.Dispose(); } } } } else { // log.Info("No company found for processing."); } }
private void updateCampaignMetricData(string metric, string callMethod, string campaign) { //Create connection to the iSeries iDB2Connection cn = new iDB2Connection(); cn.ConnectionString = connectionString; //Create query iDB2Command cmd = new iDB2Command(); cmd.CommandText = "update monprddta.campaignmetrics set Metric = @metric where CallType = @callMethod and Campaign = @campaign"; cmd.Connection = cn; cn.Open(); cmd.DeriveParameters(); cmd.Parameters["@metric"].Value = Convert.ToDecimal(metric); cmd.Parameters["@callMethod"].Value = callMethod; cmd.Parameters["@campaign"].Value = campaign; cmd.ExecuteNonQuery(); //Clean up - Close connections cmd.Dispose(); cn.Close(); }
private void getCampaignBucketData() { //remove all preexisting data from the list campaignBuckets.Clear(); //Create a DataSet to hold data from iSeries Table DataSet dataStructure = new DataSet(); //Create a table to hold the iSeries data DataTable dt = new DataTable("CampaignBuckets"); dt.Columns.Add("Campaign"); dt.Columns.Add("Bucket"); dt.Columns.Add("CallMethod"); dt.Columns.Add("Count"); //Add the datatable to the data set dataStructure.Tables.Add(dt); //Create connection to the iSeries iDB2Connection cn = new iDB2Connection(); cn.ConnectionString = connectionString; //Create query iDB2Command cmd = new iDB2Command(); cmd.CommandText = "SELECT bucket, CCPPRJ, 'P' as type , count(rmkey) FROM cdqfil.obpcmt left join monprddta.buckets on char(bucket)<>ccpprj left join cdqfil.crmmas on ccpprj=rmp2prjt and rmacono between digits(bucketmin) and digits(bucketmax) and rmncdt=@Date and rmp2rflg in ('1','H') and rmdnc='' WHERE ccpprj in ('MAS', 'MASSP', 'MASSS', 'MASSPS', 'MASFC', 'MASSPFC') GROUP BY bucket, ccpprj UNION SELECT bucket, CCPPRJ, 'M' as type , count(rmkey) FROM cdqfil.obpcmt left join monprddta.buckets on char(bucket) <> ccpprj left join cdqfil.crmmas on ccpprj = rmp2prjt and rmacono between digits(bucketmin) and digits(bucketmax) and rmncdt = @Date and rmp2rflg in ('M', 'h') and rmdnc = '' WHERE ccpprj in ('MAS', 'MASSP', 'MASSS', 'MASSPS', 'MASFC', 'MASSPFC') GROUP BY bucket, ccpprj ORDER BY type, ccpprj, bucket"; cmd.Connection = cn; //prepare for Date parameter String date = long.Parse(DateTime.Today.Date.ToString("yyyyMMdd")).ToString(); cn.Open(); cmd.DeriveParameters(); cmd.Parameters["@Date"].Value = date; //Execute the sql statement. Get a Data Reader object iDB2DataReader readFile = cmd.ExecuteReader(); //Read each row from the table and output the results into the data set while (readFile.Read()) { //Create a row to hold data DataRow datarow = dataStructure.Tables["CampaignBuckets"].NewRow(); datarow["Bucket"] = readFile.GetString(0).Trim(); datarow["Campaign"] = readFile.GetString(1).Trim(); datarow["CallMethod"] = readFile.GetString(2); datarow["Count"] = readFile.GetString(3); //add the row to the data table Campaigns dataStructure.Tables["CampaignBuckets"].Rows.Add(datarow); } //Clean up - Close connections readFile.Close(); cmd.Dispose(); cn.Close(); foreach (DataRow row in dt.Rows) { switch (row["Campaign"].ToString()) { case "MAS": //do nothing as it's named correctly already break; case "MASSP": row["Campaign"] = "MAS SP"; break; case "MASSS": row["Campaign"] = "ALT"; break; case "MASSPS": row["Campaign"] = "ALT SP"; break; case "MASFC": row["Campaign"] = "FOCUS"; break; case "MASSPFC": row["Campaign"] = "FOCUS SP"; break; default: //nothing break; } campaignBuckets.Add(new CampaignBucket(row["Campaign"].ToString(), row["Bucket"].ToString(), row["CallMethod"].ToString(), row["Count"].ToString())); } }
private void getCampaignData() { //remove all preexisting data from the list campaignData.Clear(); // Create a DataSet to hold data from iSeries Table DataSet dataStructure = new DataSet(); //Create a table to hold the iSeries data DataTable dt = new DataTable("Campaigns"); dt.Columns.Add("CampaignName"); dt.Columns.Add("CampaignDisplayName"); dt.Columns.Add("CallMethod"); dt.Columns.Add("AccountsRemaining"); dt.Columns.Add("InitialAccounts"); //Add the datatable to the data set dataStructure.Tables.Add(dt); //Create connection to the iSeries iDB2Connection cn = new iDB2Connection(); cn.ConnectionString = connectionString; //Create query iDB2Command cmd = new iDB2Command(); cmd.CommandText = "select m.rmp2prjt " + ",m.CallMethod " + ",COALESCE(c.cnt, 0) AS nowAccounts " + ",COALESCE(m.cnt, 0 ) AS initialAccounts " + "FROM " + "(SELECT rmp2prjt " + ",case when rmp2prjt = rmp3prjt Then 'I' when rmp2rflg in ('1', 'H') Then 'P' else 'M' end as CallMethod " + ",count(*) as cnt " + "FROM monprddta.crmmascopy " + "WHERE (rmp2prjt like 'MAS%' or rmp2prjt in ('MS2BW210', 'MSCYC21', 'M2GEN', 'M2GSP')) and rmncdt = @Date and rmp2rflg in ('1','H','M','h') and rmdnc = '' " + "GROUP BY rmp2prjt, case when rmp2prjt = rmp3prjt then 'I' when rmp2rflg in ('1','H') Then 'P' else 'M' end) m " + "LEFT JOIN " + "(SELECT rmp2prjt " + ",case when rmp2prjt = rmp3prjt Then 'I' when rmp2rflg in ('1', 'H') Then 'P' else 'M' end as CallMethod " + ",count(*) as cnt " + "FROM cdqfil.crmmas " + "WHERE (rmp2prjt like 'MAS%' or rmp2prjt in ('MS2BW210', 'MSCYC21', 'M2GEN', 'M2GSP')) and rmncdt = @Date and rmp2rflg in ('1','H','M','h') and rmdnc = '' " + "GROUP BY rmp2prjt, case when rmp2prjt = rmp3prjt then 'I' when rmp2rflg in ('1','H') Then 'P' else 'M' end) c on c.rmp2prjt = m.rmp2prjt and c.callmethod = m.callmethod"; cmd.Connection = cn; //prepare for Date parameter String date = long.Parse(DateTime.Today.Date.ToString("yyyyMMdd")).ToString(); cn.Open(); cmd.DeriveParameters(); cmd.Parameters["@Date"].Value = date; //Execute the sql statement. Get a Data Reader object iDB2DataReader readFile = cmd.ExecuteReader(); //Read each row from the table and output the results into the data set while (readFile.Read()) { //Create a row to hold data DataRow datarow = dataStructure.Tables["Campaigns"].NewRow(); datarow["CampaignName"] = readFile.GetString(0).Trim(); datarow["CallMethod"] = readFile.GetString(1); datarow["AccountsRemaining"] = readFile.GetiDB2Integer(2); datarow["InitialAccounts"] = readFile.GetiDB2Integer(3); //add the row to the data table Campaigns dataStructure.Tables["Campaigns"].Rows.Add(datarow); } //Clean up - Close connections readFile.Close(); cmd.Dispose(); cn.Close(); foreach (DataRow row in dt.Rows) { switch (row["CampaignName"].ToString()) { case "MAS": row["CampaignDisplayName"] = "MAS"; sortId = 0; break; case "MASSP": row["CampaignDisplayName"] = "MAS SP"; sortId = 1; break; case "MASSS": row["CampaignDisplayName"] = "ALT"; sortId = 2; break; case "MASSPS": row["CampaignDisplayName"] = "ALT SP"; sortId = 3; break; case "MASFC": row["CampaignDisplayName"] = "FOCUS"; sortId = 4; break; case "MASSPFC": row["CampaignDisplayName"] = "FOCUS SP"; sortId = 5; break; case "MASWC": row["CampaignDisplayName"] = "WC"; sortId = 6; break; case "MASWCSP": row["CampaignDisplayName"] = "WC SP"; sortId = 7; break; case "M2GEN": row["CampaignDisplayName"] = "2G"; sortId = 8; break; case "M2GSP": row["CampaignDisplayName"] = "2G SP"; sortId = 9; break; case "MASPS": row["CampaignDisplayName"] = "BULK"; sortId = 10; break; case "2NDPASS": row["CampaignDisplayName"] = "2NDPASS"; sortId = 11; break; case "INBOUND": row["CampaignDisplayName"] = "INBOUND"; sortId = 12; break; default: //nothing break; } campaignData.Add(new Campaign(sortId , row["CampaignName"].ToString() , row["CampaignDisplayName"].ToString() , row["CallMethod"].ToString() , row["InitialAccounts"].ToString() , row["AccountsRemaining"].ToString(), "0", "0")); } }
public Int32 addVital(iDB2Connection DBConnection, string SCHEMA, string clientID, string vitalType, string vitalDescription, string vitalValue1, string vitalValue2, string myInterviewDate) { try { iDB2Command cmd = DBConnection.CreateCommand(); cmd.CommandText = string.Format("INSERT INTO {0}.CLTCVL (CVLCAS, CVLTYPE, CVLDATE, CVLVAL, CVLVAL2, CVLMODE, CVLNOTES, CVLCRTUSR, CVLCRTDAT, CVLCHGUSR, CVLCHGDAT, CVLACTPLN) VALUES (@CLIENT_ID, @VITAL_TYPE, @DATE_ENTERED, @VITAL_VALUE_1, @VITAL_VALUE_2, ' ', @CVLNOTES, 'Nashua Vitals Loader', CURRENT TIMESTAMP, 'Nashua Vitals Loader', CURRENT TIMESTAMP, 0)", SCHEMA); cmd.DeriveParameters(); cmd.Parameters["@CLIENT_ID"].Value = clientID; cmd.Parameters["@VITAL_TYPE"].Value = vitalType; cmd.Parameters["@DATE_ENTERED"].Value = myInterviewDate; switch (vitalDescription) { case "8 Hour Fasting Vital Loader": string HourFastingInd = string.Empty; if (vitalValue1 == "Y") { HourFastingInd = "1"; } else { HourFastingInd = "2"; } cmd.Parameters["@VITAL_VALUE_1"].Value = Convert.ToDouble(HourFastingInd); cmd.Parameters["@VITAL_VALUE_2"].Value = null; break; case "Blood Pressure Vital Loader": cmd.Parameters["@VITAL_VALUE_1"].Value = vitalValue1; cmd.Parameters["@VITAL_VALUE_2"].Value = vitalValue2; break; case "Abdominal Girth Vital Loader": double abdominalGirthcm = Convert.ToDouble(vitalValue1); double abdominalGirth = abdominalGirthcm * .39; cmd.Parameters["@VITAL_VALUE_1"].Value = Math.Round(abdominalGirth, 1); cmd.Parameters["@VITAL_VALUE_2"].Value = null; break; case "Height Vital Loader": double heightKilo = Convert.ToDouble(vitalValue1); double heightInches = heightKilo * .39; cmd.Parameters["@VITAL_VALUE_1"].Value = Math.Round(heightInches, 1); cmd.Parameters["@VITAL_VALUE_2"].Value = null; break; case "Weight Vital Loader": double weightKilo = Convert.ToDouble(vitalValue1); double weightLBS = weightKilo * 2.2046; cmd.Parameters["@VITAL_VALUE_1"].Value = Math.Round(weightLBS, 1); cmd.Parameters["@VITAL_VALUE_2"].Value = null; break; default: cmd.Parameters["@VITAL_VALUE_1"].Value = Convert.ToDouble(vitalValue1); cmd.Parameters["@VITAL_VALUE_2"].Value = null; break; } cmd.Parameters["@CVLNOTES"].Value = vitalDescription; //cmd.Parameters["@VITAL_MASTER_UID"].Value = vitalUID; iDB2DataReader db2r1 = cmd.ExecuteReader(CommandBehavior.Default); Int32 insertedRow = db2r1.RecordsAffected; return(insertedRow); } catch (Exception ex) { throw new Exception(ex.Message); } }