public static System.Xml.Schema.XmlSchemaComplexType GetTypedTableSchema(System.Xml.Schema.XmlSchemaSet xs)
{
System.Xml.Schema.XmlSchemaComplexType type = new System.Xml.Schema.XmlSchemaComplexType();
System.Xml.Schema.XmlSchemaSequence sequence = new System.Xml.Schema.XmlSchemaSequence();
EquipmentSchema ds = new EquipmentSchema();
xs.Add(ds.GetSchemaSerializable());
System.Xml.Schema.XmlSchemaAny any1 = new System.Xml.Schema.XmlSchemaAny();
any1.Namespace = "http://www.w3.org/2001/XMLSchema";
any1.MinOccurs = new decimal(0);
any1.MaxOccurs = decimal.MaxValue;
any1.ProcessContents = System.Xml.Schema.XmlSchemaContentProcessing.Lax;
sequence.Items.Add(any1);
System.Xml.Schema.XmlSchemaAny any2 = new System.Xml.Schema.XmlSchemaAny();
any2.Namespace = "urn:schemas-microsoft-com:xml-diffgram-v1";
any2.MinOccurs = new decimal(1);
any2.ProcessContents = System.Xml.Schema.XmlSchemaContentProcessing.Lax;
sequence.Items.Add(any2);
System.Xml.Schema.XmlSchemaAttribute attribute1 = new System.Xml.Schema.XmlSchemaAttribute();
attribute1.Name = "namespace";
attribute1.FixedValue = ds.Namespace;
type.Attributes.Add(attribute1);
System.Xml.Schema.XmlSchemaAttribute attribute2 = new System.Xml.Schema.XmlSchemaAttribute();
attribute2.Name = "tableTypeName";
attribute2.FixedValue = "CustomerEquipmentDataTable";
type.Attributes.Add(attribute2);
type.Particle = sequence;
return(type);
}
public override DataSet Clone()
{
EquipmentSchema cln = ((EquipmentSchema)(base.Clone()));
cln.InitVars();
return(cln);
}
public override System.Data.DataSet Clone()
{
EquipmentSchema cln = ((EquipmentSchema)(base.Clone()));
cln.InitVars();
cln.SchemaSerializationMode = this.SchemaSerializationMode;
return(cln);
}
public static System.Xml.Schema.XmlSchemaComplexType GetTypedDataSetSchema(System.Xml.Schema.XmlSchemaSet xs)
{
EquipmentSchema ds = new EquipmentSchema();
System.Xml.Schema.XmlSchemaComplexType type = new System.Xml.Schema.XmlSchemaComplexType();
System.Xml.Schema.XmlSchemaSequence sequence = new System.Xml.Schema.XmlSchemaSequence();
xs.Add(ds.GetSchemaSerializable());
System.Xml.Schema.XmlSchemaAny any = new System.Xml.Schema.XmlSchemaAny();
any.Namespace = ds.Namespace;
sequence.Items.Add(any);
type.Particle = sequence;
return(type);
}
/// <summary>
/// Returns an equipment dataset containing the customer's equipment info
/// </summary>
/// <param name="siteId"></param>
/// <param name="accountNumber9"></param>
/// <returns></returns>
public EquipmentSchema GetEquipment(int siteId, string siteCode, string accountNumber9)
{
siteId = 541;
siteCode = "SAN";
accountNumber9 = "65297304";
OracleCommand cmd = null;
OracleCommand cmd2 = null;
OracleDataAdapter da = null;
OracleDataAdapter da2 = null;
try
{
using (OracleConnection oracleConn = new OracleConnection(_connectionString))
{
// open connection
try{ oracleConn.Open(); }
catch { throw new LogonException(); }
//get customer equipment
StringBuilder equipmentSql = new StringBuilder();
equipmentSql.Append("select distinct serial_number, equipment_type_code ");
equipmentSql.AppendFormat("from {0}_EQUIPMENT ", siteCode);
//equipmentSql.Append( "where equipment_type_code != 'O' ");
equipmentSql.AppendFormat("where account_number = {0} and site_id = {1}", accountNumber9, siteId);
// build the command object
cmd = new OracleCommand(equipmentSql.ToString(), oracleConn);
cmd.CommandType = CommandType.Text;
// build the dataadapter
da = new OracleDataAdapter(cmd);
// create the dataset to fill
EquipmentSchema ds = new EquipmentSchema();
// fill equipment info
da.Fill(ds.CustomerEquipment);
//convert date to icoms date for query
Icoms1900Date now = new Icoms1900Date(DateTime.Now.Subtract(new TimeSpan(2500, 0, 0, 0)));
//Icoms1900Date now = new Icoms1900Date(1030925);
string time = now.Date.Hour.ToString() + now.Date.Minute.ToString();
//get ppv events for account
StringBuilder ppvSql = new StringBuilder();
ppvSql.Append("select distinct p.serial_number, t.event_title, p.event_start_date, ");
ppvSql.Append("p.event_start_time, d.end_date, d.end_time ");
ppvSql.AppendFormat("from {0}_title t, {0}_ppv_purchase p, {0}_pricing_group_dtl d ", siteCode);
ppvSql.Append("where p.title_code = t.title_code and p.event_start_date = d.start_date ");
ppvSql.Append("and p.event_start_time = d.start_time and p.showing_number = d.showing_number ");
ppvSql.AppendFormat("and p.ACCOUNT_NUMBER = {0} and p.SITE_ID = {1} ", accountNumber9, siteId);
ppvSql.Append("and p.purchase_status = 'P' ");
ppvSql.AppendFormat("and ((d.end_date > {0}) or (d.end_date = {0} and d.end_time >= {1}))", now.ToString(), time);
//Oracle does not support multiple recordsets like SQL Server does
//so we have to use another cmd and da...grr
// build the command object
cmd2 = new OracleCommand(ppvSql.ToString(), oracleConn);
cmd2.CommandType = CommandType.Text;
// build the dataadapter
da2 = new OracleDataAdapter(cmd2);
// now fill ppv dt
da2.Fill(ds.ActivePpvEvents);
// all done, return
return(ds);
}
}
catch (LogonException)
{
// just rethrow it. it is from our internal code block
throw;
}
catch (Exception ex)
{
// DataSourceException.
throw new DataSourceException(ex);
}
finally
{
//clean up
if (da != null)
{
da.Dispose();
}
if (da2 != null)
{
da2.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
if (cmd2 != null)
{
cmd2.Dispose();
}
}
}
/// <summary>
/// Returns an equipment dataset containing the customer's equipment info
/// </summary>
/// <param name="siteId"></param>
/// <param name="siteCode"></param>
/// <param name="accountNumber9"></param>
/// <returns></returns>
public EquipmentSchema GetEquipment(int siteId, string siteCode, string accountNumber9)
{
OracleCommand cmd = null;
OracleDataAdapter da = null;
try
{
using (OracleConnection oracleConn = new OracleConnection(_connectionString))
{
// create the dataset to fill
EquipmentSchema ds = new EquipmentSchema();
// open connection
try{ oracleConn.Open(); }
catch (Exception lex) { throw new LogonException(lex); }
//get customer equipment
//note: we want equipment with status codes V (waiting to be installed)
// or 5 (is installed) or 7 (assinged to tech to be installed)
StringBuilder equipmentSql = new StringBuilder();
equipmentSql.Append("select e.serial_number, e.item_number, ed.port_type, ed.equipment_address ");
equipmentSql.AppendFormat("from {0}_EQUIPMENT e, {0}_EQUIPMENT_DETAIL ed ", siteCode);
equipmentSql.AppendFormat("where e.account_number = {0} and e.site_id = {1}", accountNumber9, siteId);
equipmentSql.Append("and e.account_number = ed.account_number ");
equipmentSql.Append("and (e.equipment_status_code = 'V' or e.equipment_status_code = '5' ");
equipmentSql.Append("or e.equipment_status_code = '7') and e.equipment_type_code = 'C' ");
equipmentSql.Append("and (ed.CATEGORY_CODE = 'C' or ed.CATEGORY_CODE = 'D') ");
equipmentSql.Append("and e.serial_number = ed.serial_number ");
equipmentSql.Append("and e.item_number = ed.item_number ");
equipmentSql.Append("and e.site_id = ed.site_id");
// build the command object
cmd = new OracleCommand(equipmentSql.ToString(), oracleConn);
cmd.CommandType = CommandType.Text;
// build the dataadapter
da = new OracleDataAdapter(cmd);
// fill equipment info
da.Fill(ds.CustomerEquipment);
// all done, return
return(ds);
}
}
catch (LogonException)
{
// just rethrow it. it is from our internal code block
throw;
}
catch (Exception ex)
{
// DataSourceException.
throw new DataSourceException(ex);
}
finally
{
//clean up
if (da != null)
{
da.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
}
}