/// <summary>
/// Method to execute the query to add customer data to the object. The field names
/// read in this method must be included in the InitializeCommand() method of the factory class.
/// </summary>
/// <see cref="InitializeCommand()"/>
/// <param name="cmd"></param>
internal bool QueryForCustomerInfo(IPSCommand cmd)
{
bool isRow = false;
try
{
using (IPSDataAdapter da = new IPSDataAdapter(cmd))
{
DataTable table = new DataTable();
int nrows = da.Fill(table);
foreach (DataRow row in table.Rows)
{
this.name = Convert.ToString(row["NAME"]);
this.location = Convert.ToString(row["LOCATION"]);
if (row["PHONE"] == null)
{
this.phone = string.Empty;
}
else
{
this.phone = Convert.ToString(row["PHONE"]);
}
if (row["CUST_TYPE"] == null)
{
this.phone = string.Empty;
}
else
{
this.phone = Convert.ToString(row["CUST_TYPE"]);
}
this.premise = Convert.ToString(row["PREMISE"]);
this.dgroup = Convert.ToString(row["DGROUP"]);
this.accountNumber = Convert.ToString(row["ACCOUNT_NUM"]);
this.xfmr = Convert.ToString(row["XFMR"]);
this.custType = Convert.ToString(row["CUST_TYPE"]);
this.meterStatus = Convert.ToString(row["METER_STATUS"]);
this.meterNo = Convert.ToString(row["METER_NUM"]);
}
if (nrows >= 1)
{
isRow = true;
}
}
StringBuilder sb = new StringBuilder();
sb.Append("CALLBACK=N;");
sb.AppendFormat("NAME={0};", this.name);
sb.AppendFormat("CUST_TYPE={0};", "A"); // InService wants 'A' in this field so it can render the calls on the map as an 'A' per Pratap 3/7/2013
sb.AppendFormat("LOCATION={0};", this.location);
sb.AppendFormat("PHONE={0};", this.phone);
sb.Append("PRIORITY=;");
sb.AppendFormat("PREMISE={0};", this.premise);
sb.AppendFormat("METER={0};", this.meterNo);
sb.Append("AGENCY=OUTAGE;");
sb.AppendFormat("DGROUP={0};", this.dgroup);
sb.Append("IS_METER=T;");
sb.AppendFormat("ACCT={0};", this.accountNumber);
sb.AppendFormat("XFMR={0}", this.xfmr);
sb.Append("REMARKS=Call Created By AMI;");
fieldvalues = sb.ToString();
}
catch (IPSDataException o)
{
_log.ErrorFormat("Exception in QueryForCustomerInfo() method. Message: {0}", o.Message);
}
return(isRow);
}
public void processRestoreMessage(string meterNumber, string id, string meterOffDts)
{
string query = "";
string sName = "";
string sPhone = "";
string sLocation = "";
string sXfmr = "";
string sAccountNum = "";
string sMeterNum = "";
long lPremiseID = 0;
int queryResult = 0;
string sDate = String.Format("{0:yyyyMMddHHmmss}", System.DateTime.Now).ToString();
try
{
IPSTransaction transaction = _connection.BeginTransaction();
// query1
query = "select name, phone, location, xfmr, account_num, meter_num, premise from cispersl where meter_num = '" + meterNumber + "'";
_log.DebugFormat("processRestoreMessage()::query1 - {0}", query);
using (IPSCommand cmd = new IPSCommand(query, _connection))
{
using (IPSDataAdapter da = new IPSDataAdapter(cmd))
{
DataTable table = new DataTable();
int nrows = da.Fill(table);
foreach (DataRow row in table.Rows)
{
sName = row["name"].ToString();
sPhone = row["phone"].ToString();
sLocation = row["location"].ToString();
sXfmr = row["xfmr"].ToString();
sAccountNum = row["account_num"].ToString();
sMeterNum = row["meter_num"].ToString();
lPremiseID = Convert.ToInt64(row["premise"]);
}
}
}// query1 end
int iReqID = 0;
int iID = 1;
//query2
query = "select n_num from numbr where purpose like 'AMI Unsolicited%' and curent='T'";
_log.DebugFormat("processRestoreMessage()::query2 - {0}", query);
using (IPSCommand cmd = new IPSCommand(query, _connection))
{
using (IPSDataAdapter da = new IPSDataAdapter(cmd))
{
DataTable table = new DataTable();
int nrows = da.Fill(table);
foreach (DataRow row in table.Rows)
{
if (row["n_num"] != System.DBNull.Value)
{
iID = Convert.ToInt32(row["n_num"]);
}
else
{
iID = iID + 1;
}
}
}
} //query2 end
String dst_value = String.Empty;
String std_value = String.Empty;
String is_dst_used = String.Empty;
String suffix = String.Empty;
//query3
query = "select is_dst_used, dst_name, std_name from time_zone_info";
_log.DebugFormat("processRestoreMessage()::query3 - {0}", query);
using (IPSCommand cmd = new IPSCommand(query, _connection))
{
using (IPSDataAdapter da = new IPSDataAdapter(cmd))
{
DataTable table = new DataTable();
int nrows = da.Fill(table);
foreach (DataRow row in table.Rows)
{
is_dst_used = row["is_dst_used"].ToString();
dst_value = row["dst_name"].ToString();
std_value = row["std_name"].ToString();
}
}
}// query3 end
if (is_dst_used != null)
{
if (is_dst_used == "T")
{
suffix = dst_value;
}
else
{
suffix = std_value;
}
}
else
{
suffix = dst_value; //by default
}
//query4
int xfmr_mslink = 0;
if (!string.IsNullOrEmpty(sXfmr))
{
query = "select mslink from OMS_TRANSFORMER where name = '" + sXfmr + "'";
_log.DebugFormat("processRestoreMessage()::query4 - {0}", query);
using (IPSCommand cmd = new IPSCommand(query, _connection))
{
using (IPSDataAdapter da = new IPSDataAdapter(cmd))
{
DataTable table = new DataTable();
int nrows = da.Fill(table);
foreach (DataRow row in table.Rows)
{
xfmr_mslink = Convert.ToInt32(row["mslink"]);
}
}
}// query4 end
}
//query5
query = "INSERT INTO AMI_DATA_RESPONSE_DETAILS (METER_NUM, METER_RESPONSE, ";
query += "METER_RESPONSE_DTS, PROCESSING_COMPLETE, REQUEST_ID, UNSOLICITED_NOTIFICATION_ID, ";
query += "XFMR_NAME, XFMR_MSLINK) VALUES ( '" + sMeterNum + "', 'Meter On', '" + sDate + suffix + "', 'T', " + iReqID + ", " + iID + ", '" + sXfmr + "' , " + xfmr_mslink + " )";
_log.DebugFormat("processRestoreMessage()::query5 - {0}", query);
using (IPSCommand cmd = new IPSCommand(query, _connection))
{
queryResult = cmd.ExecuteNonQuery();
} //query5 end
//query6
query = "update numbr set n_num = n_num + 1 where purpose like 'AMI Request%' and curent='T'";
_log.DebugFormat("processRestoreMessage()::query6 - {0}", query);
using (IPSCommand cmd = new IPSCommand(query, _connection))
{
queryResult = cmd.ExecuteNonQuery();
}
//query6 end
//query7
query = "update numbr set n_num = n_num + 1 where purpose like 'AMI Unsolicited%' and curent='T'";
_log.DebugFormat("processRestoreMessage()::query7 - {0}", query);
using (IPSCommand cmd = new IPSCommand(query, _connection))
{
queryResult = cmd.ExecuteNonQuery();
}
//query7 end
transaction.Commit();
}
catch (Exception e)
{
_log.ErrorFormat("Exception in processRestoreMessage() method. Message: {0}", e.Message);
}
}
