public static System.Xml.Schema.XmlSchemaComplexType GetTypedDataSetSchema(System.Xml.Schema.XmlSchemaSet xs) {
ProjectPipeProgressDs ds = new ProjectPipeProgressDs();
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;
}
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();
ProjectPipeProgressDs ds = new ProjectPipeProgressDs();
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 = "ProjectPipeProgressDataTable";
type.Attributes.Add(attribute2);
type.Particle = sequence;
return type;
}
private DataSet GetDs()
{
ProjectPipeProgressDs ds = new ProjectPipeProgressDs();
#region Sql���
string strSql = @"select sysid,
nvl(sysname,0)sysname,
nvl(init_count,0)init_count,
nvl(approving_count,0)approving_count,
nvl(approved_count,0)approved_count,
nvl(assigning_count,0)assigning_count,
nvl(processing_count,0)processing_count,
nvl(feedback_count,0)feedback_count,
nvl(feedbackd_count,0)feedbackd_count,
nvl(testing_count,0)testing_count,
nvl(apfeedback_count,0)apfeedback_count,
nvl(failure_count,0)failure_count,
nvl(wset_count,0)wset_count,
nvl(seting_count,0)seting_count,
nvl(wdebug_count,0)wdebug_count,
nvl(debuged_count,0)debuged_count,
nvl(debugfail_count,0)debugfail_count,
nvl(sum_count,0)sum_count
from (select sysid, sysname
from sp_system
where project_id = '" + toolStripComboBox1.ComboBox.Text.ToString() + @"'
group by sysid, sysname) s
left join (select systemid, count(t.rowid) init_count
from SP_SPOOL_TAB t
where t.flag = 'Y'
and t.flowstatus = 0
and t.projectid = '" + toolStripComboBox1.ComboBox.Text.ToString() + @"'
group by t.systemid) init on s.sysid = init.systemid
left join (select systemid, count(t.rowid) approving_count
from SP_SPOOL_TAB t
where t.flag = 'Y'
and t.flowstatus = 1
and t.projectid ='" + toolStripComboBox1.ComboBox.Text.ToString() + @"'
group by t.systemid) approving on s.sysid = approving.systemid
left join (select t.systemid, count(t.rowid) approved_count
from SP_SPOOL_TAB t
where t.flag = 'Y'
and t.flowstatus = 2
and t.projectid ='" + toolStripComboBox1.ComboBox.Text.ToString() + @"'
group by t.systemid) approved on s.sysid = approved.systemid
left join (select t.systemid, count(t.rowid) assigning_count
from SP_SPOOL_TAB t
where t.flag = 'Y'
and t.flowstatus = 3
and t.projectid ='" +toolStripComboBox1 .ComboBox .Text .ToString ()+@"'
group by t.systemid) assigning on s.sysid = assigning.systemid
left join (select t.systemid, count(t.rowid) processing_count
from SP_SPOOL_TAB t
where t.flag = 'Y'
and t.flowstatus = 4
and t.projectid ='" + toolStripComboBox1.ComboBox.Text.ToString() + @"'
group by t.systemid) processing on s.sysid = processing.systemid
left join (select t.systemid, count(t.rowid) feedback_count
from SP_SPOOL_TAB t
where t.flag = 'Y'
and t.flowstatus = 5
and t.projectid = '" + toolStripComboBox1.ComboBox.Text.ToString() + @"'
group by t.systemid) feedback on s.sysid = feedback.systemid
left join (select t.systemid, count(t.rowid) feedbackd_count
from SP_SPOOL_TAB t
where t.flag = 'Y'
and t.flowstatus = 6
and t.projectid = '" + toolStripComboBox1.ComboBox.Text.ToString() + @"'
group by t.systemid) feedbackd on s.sysid = feedbackd.systemid
left join (select t.systemid, count(t.rowid) testing_count
from SP_SPOOL_TAB t
where t.flag = 'Y'
and t.flowstatus = 7
and t.projectid = '" + toolStripComboBox1.ComboBox.Text.ToString() + @"'
group by t.systemid) testing on s.sysid = testing.systemid
left join (select t.systemid, count(t.rowid) dealdesign_count
from SP_SPOOL_TAB t
where t.flag = 'Y'
and t.flowstatus = 8
and t.projectid ='" + toolStripComboBox1.ComboBox.Text.ToString() + @"'
group by t.systemid) dealdesign on s.sysid = dealdesign.systemid
left join (select t.systemid, count(t.rowid) apfeedback_count
from SP_SPOOL_TAB t
where t.flag = 'Y'
and t.flowstatus = 9
and t.projectid ='" + toolStripComboBox1.ComboBox.Text.ToString() + @"'
group by t.systemid) apfeedback on s.sysid = apfeedback.systemid
left join (select t.systemid, count(t.rowid) failure_count
from SP_SPOOL_TAB t
where t.flag = 'Y'
and t.flowstatus = 10
and t.projectid = '" + toolStripComboBox1.ComboBox.ValueMember.ToString() + @"'
group by t.systemid) failure on s.sysid = failure.systemid
left join (select t.systemid, count(t.rowid) wset_count
from SP_SPOOL_TAB t
where t.flag = 'Y'
and t.flowstatus = 11
and t.projectid = '" + toolStripComboBox1.ComboBox.Text.ToString() + @"'
group by t.systemid) wset on s.sysid = wset.systemid
left join (select t.systemid, count(t.rowid) seting_count
from SP_SPOOL_TAB t
where t.flag = 'Y'
and t.flowstatus = 12
and t.projectid ='" + toolStripComboBox1.ComboBox.Text.ToString() + @"'
group by t.systemid) seting on s.sysid = seting.systemid
left join (select t.systemid, count(t.rowid)wdebug_count
from SP_SPOOL_TAB t
where t.flag = 'Y'
and t.flowstatus = 13
and t.projectid = '" + toolStripComboBox1.ComboBox.Text.ToString() + @"'
group by t.systemid) wdebug on s.sysid = wdebug.systemid
left join (select t.systemid, count(t.rowid) debuged_count
from SP_SPOOL_TAB t
where t.flag = 'Y'
and t.flowstatus = 14
and t.projectid = '" + toolStripComboBox1.ComboBox.Text.ToString() + @"'
group by t.systemid) debuged on s.sysid = debuged.systemid
left join (select t.systemid, count(t.rowid) debugfail_count
from SP_SPOOL_TAB t
where t.flag = 'Y'
and t.flowstatus = 15
and t.projectid = '" + toolStripComboBox1.ComboBox.Text.ToString() + @"'
group by t.systemid) debugfail on s.sysid = debugfail.systemid
left join (select t.systemid, count(t.rowid) sum_count
from SP_SPOOL_TAB t
where t.flag = 'Y'
and t.projectid = '" + toolStripComboBox1.ComboBox.Text.ToString() + @"'
group by t.systemid) s2 on s.sysid = s2.systemid
";
#endregion
OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = strSql;
OracleDataAdapter oda = new OracleDataAdapter();
oda.SelectCommand = cmd;
oda.Fill(ds, "ProjectPipeProgress");
conn.Close();
return ds;
}
