PhotoExport.cs
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
namespace PhotoExport
{
class Program
{
static void Main(string[] args)
{
BK();
}
public static void BK()
{
SqlCommand Command;
SqlDataReader DataReader;
SqlConnection Connection = new SqlConnection();
Connection.ConnectionString = "Data Source=zsks.whcm.com.cn;Initial Catalog=whcm;User ID=cityonline;password=cityonline";
Connection.Open();
SqlCommand Command = Connection.CreateCommand();
Command.CommandText = "select IDCardCode,Photo from su_records_ba where approved=1 and Photo is not null order by IDCardCode";
MemoryStream MS = new MemoryStream();
FileStream FS = new FileStream("D:\\本科报名.txt", FileMode.Create);
SqlDataReader DataReader=Command.ExecuteReader();
StringWriter SW = new StringWriter();
while (DataReader.Read())
{
SW.WriteLine(DataReader.GetString(0) + "=" + DataReader.GetSqlBytes(1).Length.ToString());
FileStream PhotoStream = new FileStream("D:\\Photo\\"+DataReader.GetString(0)+".jpg", FileMode.Create);
PhotoStream.Write((Byte[])DataReader["Photo"], 0, (int)DataReader.GetSqlBytes(1).Length);
PhotoStream.Close();
}
FS.Write(Encoding.Default.GetBytes(SW.ToString()), 0, SW.ToString().Length);
FS.Close();
Console.Out.WriteLine("2008-2-24至2008-2-25数据统计：");
//数据统计：
//1.统计总数
Command = Connection.CreateCommand();
Command.CommandText = "select count(*) from su_records_ba where approved=1 and ApprovedTime>'2008-2-20'";
DataReader = Command.ExecuteReader();
DataReader.Read();
Console.Out.WriteLine("====================");
Console.Out.WriteLine("已确认报名记录总数:{0}", DataReader[0]);
DataReader.Close();
//2.统计实际人数
Command = Connection.CreateCommand();
Command.CommandText = "select count(distinct idcardcode) from su_records_ba where approved=1 and ApprovedTime>'2008-2-23'";
DataReader = Command.ExecuteReader();
DataReader.Read();
Console.Out.WriteLine("====================");
Console.Out.WriteLine("已确认实际人数总数:{0}", DataReader[0]);
DataReader.Close();
//3.统计各院系总数
Command.CommandText = "select t2.dept_name,count(*) from su_records_ba as t1 left join su_department_ba as t2 on t1.dept_id=t2.dept_id where approved=1 and ApprovedTime>'2008-2-23' group by t1.dept_id,t2.dept_name";
DataReader = Command.ExecuteReader();
Console.Out.WriteLine("====================");
Console.Out.WriteLine("各院系确认报名记录总数：");
while (DataReader.Read())
{
Console.Out.WriteLine(DataReader.GetString(0) + ":" + DataReader.GetInt32(1));
}
DataReader.Close();
//4.统计各院系总数
Command.CommandText = "select t2.dept_name,count(distinct idcardcode) from su_records_ba as t1 left join su_department_ba as t2 on t1.dept_id=t2.dept_id where approved=1 and ApprovedTime>'2008-2-23' group by t1.dept_id,t2.dept_name order by t2.dept_name";
DataReader = Command.ExecuteReader();
Console.Out.WriteLine("====================");
Console.Out.WriteLine("各院系确认报名实际人数：");
while (DataReader.Read())
{
Console.Out.WriteLine(DataReader.GetString(0) + ":" + DataReader.GetInt32(1));
}
DataReader.Close();
//5.各院系专业方向总数
Command.CommandText = "select t2.dept_name,t3.major_name,count(*) from su_records_ba as t1 left join su_department_ba as t2 on t1.dept_id=t2.dept_id left join su_major_ba as t3 on t2.dept_id=t3.dept_id and (t1.major1=t3.major_id or t1.major2=t3.major_id) where approved=1 and ApprovedTime>'2008-2-23' group by t2.dept_name,t3.major_name order by t2.dept_name";
DataReader = Command.ExecuteReader();
Console.Out.WriteLine("====================");
Console.Out.WriteLine("各院系确认报名记录数(包含主报及副报)：");
while (DataReader.Read())
{
Console.Out.WriteLine(DataReader.GetString(0) + "->" + DataReader.GetString(1) + ":" + DataReader.GetInt32(2));
}
DataReader.Close();
//6.各院系专业方向总数
Command.CommandText = "select t2.dept_name,t3.major_name,count(*) from su_records_ba as t1 left join su_department_ba as t2 on t1.dept_id=t2.dept_id inner join su_major_ba as t3 on t2.dept_id=t3.dept_id and (t1.major1=t3.major_id) where approved=1 and ApprovedTime>'2008-2-23' group by t2.dept_name,t3.major_name order by t2.dept_name";
DataReader = Command.ExecuteReader();
Console.Out.WriteLine("====================");
Console.Out.WriteLine("各院系确认报名记录数(只含主报)：");
while (DataReader.Read())
{
Console.Out.WriteLine(DataReader.GetString(0) + "->" + DataReader.GetString(1) + ":" + DataReader.GetInt32(2));
}
DataReader.Close();
//6.各院系专业方向总数
Command.CommandText = "select t2.dept_name,t3.major_name,count(*) from su_records_ba as t1 left join su_department_ba as t2 on t1.dept_id=t2.dept_id inner join su_major_ba as t3 on t2.dept_id=t3.dept_id and (t1.major2=t3.major_id) where approved=1 and ApprovedTime>'2008-2-23' group by t2.dept_name,t3.major_name order by t2.dept_name";
DataReader = Command.ExecuteReader();
Console.Out.WriteLine("====================");
Console.Out.WriteLine("各院系确认报名记录数(只含副报)：");
while (DataReader.Read())
{
Console.Out.WriteLine(DataReader.GetString(0) + "->" + DataReader.GetString(1) + ":" + DataReader.GetInt32(2));
}
DataReader.Close();
//7.各院系按小专业方向总数
Command.CommandText = "select t2.dept_name,t3.major_name,t4.subject_name,count(*) from su_records_ba as t1 left join su_department_ba as t2 on t1.dept_id=t2.dept_id inner join su_major_ba as t3 on t2.dept_id=t3.dept_id and (t1.major1=t3.major_id or t1.major2=t3.major_id) inner join su_subject_ba as t4 on (PATINDEX(t4.subject_id,t1.subject1)>0 or PATINDEX(t4.subject_id,t1.subject2)>0) where approved=1 and ApprovedTime>'2008-2-23' group by t2.dept_name,t3.major_name,t4.subject_name order by t2.dept_name";
DataReader = Command.ExecuteReader();
Console.Out.WriteLine("====================");
Console.Out.WriteLine("各院系小专业确认报名记录数(包含主副报)：");
while (DataReader.Read())
{
Console.Out.WriteLine(DataReader.GetString(0) + "->" + DataReader.GetString(1) + "->" + DataReader.GetString(2) + ":" + DataReader.GetInt32(3));
}
DataReader.Close();
//===================
Connection.Close();
}
public static void FZ()
{
SqlCommand Command;
SqlDataReader DataReader;
SqlConnection Connection = new SqlConnection();
Connection.ConnectionString = "Data Source=zsks.whcm.com.cn;Initial Catalog=whcm;User ID=cityonline;password=cityonline";
Connection.Open();
Console.Out.WriteLine("2008-2-24至2008-2-25报考附中数据统计：");
//数据统计：
//1.统计总数
Command = Connection.CreateCommand();
Command.CommandText = "select count(*) from su_records_fz where approved=1";
DataReader = Command.ExecuteReader();
DataReader.Read();
Console.Out.WriteLine("====================");
Console.Out.WriteLine("已确认报名记录总数:{0}", DataReader[0]);
DataReader.Close();
//2.统计实际人数
Command = Connection.CreateCommand();
Command.CommandText = "select count(distinct compellation) from su_records_fz where approved=1";
DataReader = Command.ExecuteReader();
DataReader.Read();
Console.Out.WriteLine("====================");
Console.Out.WriteLine("已确认实际人数总数:{0}", DataReader[0]);
DataReader.Close();
//3.统计各院系总数
Command.CommandText = "select t2.dept_name,count(*) from su_records_fz as t1 left join su_department_fz as t2 on t1.dept_id=t2.dept_id where approved=1 group by t1.dept_id,t2.dept_name";
DataReader = Command.ExecuteReader();
Console.Out.WriteLine("====================");
Console.Out.WriteLine("各层次确认报名记录总数：");
while (DataReader.Read())
{
Console.Out.WriteLine(DataReader.GetString(0) + ":" + DataReader.GetInt32(1));
}
DataReader.Close();
//4.统计各专业品种总数
Command.CommandText = "select t2.dept_name,t3.direct_name,count(*) from su_records_fz as t1 left join su_department_fz as t2 on t1.dept_id=t2.dept_id inner join su_direct_fz as t3 on (t1.direct1=t3.direct_id or t1.direct2=t3.direct_id) where approved=1 group by t2.dept_name,t3.direct_name order by t2.dept_name";
DataReader = Command.ExecuteReader();
Console.Out.WriteLine("====================");
Console.Out.WriteLine("各品种报名确认记录数(包含两个方向)：");
while (DataReader.Read())
{
Console.Out.WriteLine(DataReader.GetString(0) + "->" + DataReader.GetString(1) + ":" + DataReader.GetInt32(2));
}
DataReader.Close();
//5.各院系专业方向总数
Command.CommandText = "select t2.dept_name,t3.direct_name,count(*) from su_records_fz as t1 left join su_department_fz as t2 on t1.dept_id=t2.dept_id inner join su_direct_fz as t3 on (t1.direct1=t3.direct_id) where approved=1 group by t2.dept_name,t3.direct_name order by t2.dept_name";
DataReader = Command.ExecuteReader();
Console.Out.WriteLine("====================");
Console.Out.WriteLine("各品种报名确认记录数(只含第一方向)：");
while (DataReader.Read())
{
Console.Out.WriteLine(DataReader.GetString(0) + "->" + DataReader.GetString(1) + ":" + DataReader.GetInt32(2));
}
DataReader.Close();
//6.各院系专业方向总数
Command.CommandText = "select t2.dept_name,t3.direct_name,count(*) from su_records_fz as t1 left join su_department_fz as t2 on t1.dept_id=t2.dept_id inner join su_direct_fz as t3 on (t1.direct2=t3.direct_id) where approved=1 group by t2.dept_name,t3.direct_name order by t2.dept_name";
DataReader = Command.ExecuteReader();
Console.Out.WriteLine("====================");
Console.Out.WriteLine("各品种报名确认记录数(只含第二方向)：");
while (DataReader.Read())
{
Console.Out.WriteLine(DataReader.GetString(0) + "->" + DataReader.GetString(1) + ":" + DataReader.GetInt32(2));
}
DataReader.Close();
//===================
Connection.Close();
}
}
}
