public override bool ShowRightDataGridView(DataGridView rightgrid, ChartColumn col, string zid)
{
string sql = "";
DataTable dt = null;
if (col.TableName == "st_dt")
{
sql = string.Format("select {0}.{1} as '{2}',count(distinct {0}.sid) as 专利数 from {0} where ztid={3} and {0}.gj in ('中国','美国') group by {0}.{1} order by {0}.{1} asc", col.TableName, col.ColName, col.ShowName, zid);
}
else
{
sql = string.Format("select {0}.{1} as '{2}',count(distinct {0}.sid) as 专利数 from {0},st_dt where {0}.sid=st_dt.sid and st_dt.ztid={3} and {0}.gj in ('中国','美国') group by {0}.{1} order by {0}.{1} asc", col.TableName, col.ColName, col.ShowName, zid);
}
dt = DBA.MySqlDbAccess.GetDataTable(CommandType.Text, sql);
rightgrid.DataSource = dt;
rightgrid.Columns[rightgrid.Columns.Count - 1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
return true;
}
public static bool ShowRightDataGridView(DataGridView rightgrid, ChartColumn col, string zid)
{
string sql = "";
DataTable dt = null;
if (col.ShowName.IndexOf("年") >= 0)
{
if (col.TableName == "st_dt")
{
sql = string.Format("select {0}.{1} as '{2}',count(distinct {0}.sid) as 专利数 from {0} where {0}.ztid={3} group by {0}.{1} order by {0}.{1} asc", col.TableName, col.ColName, col.ShowName, zid);
}
else
{
sql = string.Format("select {0}.{1} as '{2}',count(distinct {0}.sid) as 专利数 from {0},st_dt where {0}.sid=st_dt.sid and st_dt.ztid={3} group by {0}.{1} order by {0}.{1} asc", col.TableName, col.ColName, col.ShowName, zid);
}
dt = DBA.MySqlDbAccess.GetDataTable(CommandType.Text, sql);
dt = DataTableHelper.ReadDateTable(dt);
}
else
{
if (col.TableName == "st_dt")
{
sql = string.Format("select {0}.{1} as '{2}',count(distinct {0}.sid) as 专利数 from {0} where ztid={3} group by {0}.{1} order by count(distinct {0}.sid) desc", col.TableName, col.ColName, col.ShowName, zid);
}
else
{
sql = string.Format("select {0}.{1} as '{2}',count(distinct {0}.sid) as 专利数 from {0},st_dt where {0}.sid=st_dt.sid and st_dt.ztid={3} group by {0}.{1} order by count(distinct {0}.sid) desc", col.TableName, col.ColName, col.ShowName, zid);
}
dt = DBA.MySqlDbAccess.GetDataTable(CommandType.Text, sql);
}
string[] types = null;
switch (col.ShowName)
{
case "专利类型(含PCT)":
types = new string[] { "发明专利", "实用新型", "外观专利", "发明专利PCT", "实用新型PCT" };
break;
case "专利类型":
types = new string[] { "发明专利", "实用新型", "外观专利" };
break;
case "申请人类型":
case "第一申请人类型":
types = new string[] { "企业", "科研院所", "高校", "事业单位", "个人" };
break;
case "法律状态":
types = new string[] { "在审", "授权有效", "授权失效", "无效" };
break;
}
if (types != null)
{
DataTable tmpdt = new DataTable();
tmpdt.Columns.Add(col.ShowName);
tmpdt.Columns.Add("专利数");
foreach (var type in types)
{
DataRow tmprow = tmpdt.NewRow();
var x = from y in dt.AsEnumerable()
where y[0].ToString() == type
select y;
if (x.Count() == 0)
{
tmprow[0] = type;
tmprow[1] = 0;
}
else
{
tmprow[0] = type;
tmprow[1] = x.First()[1].ToString();
}
tmpdt.Rows.Add(tmprow);
}
rightgrid.DataSource = tmpdt;
}
else
{
rightgrid.DataSource = dt;
}
rightgrid.Columns[rightgrid.Columns.Count - 1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
return true;
}
public static bool ShowRightDataGridView(DataGridView rightgrid, ChartColumn col, string zid, string zhibiao)
{
string sql = "";
DataTable dt = null;
switch (zhibiao)
{
case "专利存活期":
sql = string.Format("select {0}.{1} as '{2}',count(distinct {0}.sid) as 专利数 from {0} where ztid={3} group by {0}.{1} order by {0}.{1} desc", col.TableName, col.ColName, col.ShowName, zid);
break;
case "审查周期":
sql = string.Format("select {0}.{1} as '{2}',count(distinct {0}.sid) as 专利数 from {0} where ztid={3} and type='发明专利' group by {0}.{1} order by {0}.{1} desc", col.TableName, col.ColName, col.ShowName, zid);
break;
case "有效专利维持期":
sql = string.Format("select {0}.{1} as '{2}',count(distinct {0}.sid) as 专利数 from {0} where ztid={3} and lg='授权有效' group by {0}.{1} order by {0}.{1} desc", col.TableName, col.ColName, col.ShowName, zid);
break;
case "公知技术":
if (col.TableName == "st_dt")
{
sql = string.Format("select {0}.{1} as '{2}',count(distinct {0}.sid) as 专利数 from {0} where ztid={3} and st_dt.isgongzhi=1 group by {0}.{1} order by count(distinct sid) desc", col.TableName, col.ColName, col.ShowName, zid);
}
else
{
sql = string.Format("select {0}.{1} as '{2}',count(distinct {0}.sid) as 专利数 from {0},st_dt where {0}.sid=st_dt.sid and st_dt.ztid={3} and st_dt.isgongzhi=1 group by {0}.{1} order by count(distinct {0}.sid) desc", col.TableName, col.ColName, col.ShowName, zid);
}
break;
case "":
break;
default:
break;
}
dt = DBA.MySqlDbAccess.GetDataTable(CommandType.Text, sql);
rightgrid.DataSource = dt;
rightgrid.Columns[rightgrid.Columns.Count - 1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
return true;
}
private void showdata_CellClick(object sender, DataGridViewCellEventArgs e)
{
//得到行名称
//得到列名称
if (e.RowIndex < 0) return;
if (e.ColumnIndex == 0 || e.ColumnIndex == showdata.Columns.Count - 1) return;
if (e.RowIndex == showdata.Rows.Count - 1) return;
if (showdata.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString() == "0") return;
if (showdata.Columns[e.ColumnIndex].HeaderText.IndexOf("增长率") >= 0) return;
ChartColumn columnname1 = null;
ChartColumn columnname2 = null;
string type1 = "char";
string type2 = "char";
string value1 = "";
string value2 = "";
switch (this.lib_Name.Text)
{
#region 趋势
case "专利趋势分析":
columnname1 = config.GroupYear;
value1 = showdata.Rows[e.RowIndex].Cells[0].Value.ToString();
type1 = "int";
break;
case "技术生命周期分析":
if (e.ColumnIndex >= 2)
{
MessageBox.Show("对不起，本列不支持数据钻取！", "提示");
return;
}
columnname1 = config.GroupYear;
value1 = showdata.Rows[e.RowIndex].Cells[0].Value.ToString();
type1 = "int";
break;
case "专利类型分布趋势分析":
columnname1 = config.GroupYear;
value1 = showdata.Rows[e.RowIndex].Cells[0].Value.ToString();
type1 = "int";
columnname2 = config.GroupType;
value2 = showdata.Columns[e.ColumnIndex].HeaderText;
break;
#endregion
#region 区域
case "区域专利申请比重分析":
columnname1 = config.GroupQY;
value1 = showdata.Rows[e.RowIndex].Cells[0].Value.ToString();
break;
case "区域专利申请趋势分析":
columnname1 = config.GroupYear;
value1 = showdata.Rows[e.RowIndex].Cells[0].Value.ToString();
type1 = "int";
columnname2 = config.GroupQY;
value2 = showdata.Columns[e.ColumnIndex].HeaderText;
break;
case "区域专利申请人分析":
columnname1 = config.GroupQY;
value1 = showdata.Rows[e.RowIndex].Cells[0].Value.ToString();
columnname2 = config.GroupPA;
value2 = showdata.Columns[e.ColumnIndex].HeaderText;
break;
case "区域专利发明人分析":
columnname1 = config.GroupQY;
value1 = showdata.Rows[e.RowIndex].Cells[0].Value.ToString();
columnname2 = config.GroupIN;
value2 = showdata.Columns[e.ColumnIndex].HeaderText;
break;
case "区域专利技术构成分析":
columnname1 = config.GroupQY;
value1 = showdata.Rows[e.RowIndex].Cells[0].Value.ToString();
columnname2 = config.GroupIPC;
value2 = showdata.Columns[e.ColumnIndex].HeaderText;
break;
case "区域首次专利申请分析":
columnname1 = config.GroupQY;
value1 = showdata.Rows[e.RowIndex].Cells[0].Value.ToString();
columnname2 = new ChartColumn() { ColName = "ady", TableName = "st_dt" };
value2 = showdata.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString();
break;
#endregion
#region 申请人
//申请人专利排名分析
case "申请人专利排名分析":
columnname1 = config.GroupPA;
value1 = showdata.Rows[e.RowIndex].Cells[0].Value.ToString();
break;
case "申请人类型分析":
columnname1 = config.GroupPAType;
value1 = showdata.Rows[e.RowIndex].Cells[0].Value.ToString();
break;
case "申请人趋势分析":
columnname1 = config.GroupYear;
value1 = showdata.Rows[e.RowIndex].Cells[0].Value.ToString();
type1 = "int";
columnname2 = config.GroupPA;
value2 = showdata.Columns[e.ColumnIndex].HeaderText;
break;
case "申请人首次专利申请分析":
columnname1 = config.GroupPA;
value1 = showdata.Rows[e.RowIndex].Cells[0].Value.ToString();
columnname2 = new ChartColumn() { ColName = "ady", TableName = "st_dt" };
value2 = showdata.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString();
type2 = "int";
break;
case "申请人专利类型分析":
columnname1 = config.GroupPA;
value1 = showdata.Rows[e.RowIndex].Cells[0].Value.ToString();
columnname2 = config.GroupType;
value2 = showdata.Columns[e.ColumnIndex].HeaderText;
break;
//申请人专利布局动向分析
case "申请人专利布局动向分析":
columnname1 = config.GroupPA;
value1 = showdata.Rows[e.RowIndex].Cells[0].Value.ToString();
columnname2 = config.GroupQY;
value2 = showdata.Columns[e.ColumnIndex].HeaderText;
break;
case "申请人专利技术构成分析":
columnname1 = config.GroupPA;
value1 = showdata.Rows[e.RowIndex].Cells[0].Value.ToString();
columnname2 = config.GroupIPC;
value2 = showdata.Columns[e.ColumnIndex].HeaderText;
break;
case "申请人研发阵容分析":
if (e.ColumnIndex >= 2)
{
MessageBox.Show("对不起，本列不支持数据钻取！", "提示");
return;
}
break;
case "申请人专利合作分析":
if (e.ColumnIndex >= 2)
{
MessageBox.Show("对不起，本列不支持数据钻取！", "提示");
return;
}
break;
case "发明人专利排名分析":
columnname1 = config.GroupIN;
value1 = showdata.Rows[e.RowIndex].Cells[0].Value.ToString();
break;
case "发明人专利技术构成分析":
columnname1 = config.GroupIN;
value1 = showdata.Rows[e.RowIndex].Cells[0].Value.ToString();
columnname2 = config.GroupIPC;
value2 = showdata.Columns[e.ColumnIndex].HeaderText;
break;
#endregion
#region 技术
case "专利技术构成分析":
columnname1 = config.GroupIPC;
value1 = showdata.Rows[e.RowIndex].Cells[0].Value.ToString();
break;
case "专利技术趋势分析":
columnname1 = config.GroupYear;
value1 = showdata.Rows[e.RowIndex].Cells[0].Value.ToString();
type1 = "int";
columnname2 = config.GroupIPC;
value2 = showdata.Columns[e.ColumnIndex].HeaderText;
break;
case "专利技术区域分析":
columnname1 = config.GroupIPC;
value1 = showdata.Rows[e.RowIndex].Cells[0].Value.ToString();
columnname2 = config.GroupQY;
value2 = showdata.Columns[e.ColumnIndex].HeaderText;
break;
case "专利技术申请人分析":
columnname1 = config.GroupIPC;
value1 = showdata.Rows[e.RowIndex].Cells[0].Value.ToString();
columnname2 = config.GroupPA;
value2 = showdata.Columns[e.ColumnIndex].HeaderText;
break;
#endregion
#region 法律状态
case "专利法律状态分析":
columnname1 = config.GroupLG;
value1 = showdata.Rows[e.RowIndex].Cells[0].Value.ToString();
break;
case "专利存活期分析":
columnname1 = config.GroupCS;
value1 = showdata.Rows[e.RowIndex].Cells[0].Value.ToString();
type1 = "int";
break;
case "有效专利维持期分析":
columnname1 = config.GroupCS;
value1 = showdata.Rows[e.RowIndex].Cells[0].Value.ToString();
type1 = "int";
break;
case "申请人法律状态分析":
columnname1 = config.GroupPA;
value1 = showdata.Rows[e.RowIndex].Cells[0].Value.ToString();
columnname2 = config.GroupLG;
value2 = showdata.Columns[e.ColumnIndex].HeaderText;
break;
case "区域法律状态分析":
columnname1 = config.GroupQY;
value1 = showdata.Rows[e.RowIndex].Cells[0].Value.ToString();
columnname2 = config.GroupLG;
value2 = showdata.Columns[e.ColumnIndex].HeaderText;
break;
case "技术领域法律状态分析":
columnname1 = config.GroupQY;
value1 = showdata.Rows[e.RowIndex].Cells[0].Value.ToString();
columnname2 = config.GroupLG;
value2 = showdata.Columns[e.ColumnIndex].HeaderText;
break;
case "公知技术统计分析":
columnname1 = config.GroupPA;
value1 = showdata.Rows[e.RowIndex].Cells[0].Value.ToString();
columnname2 = new ChartColumn() { ColName = "isgongzhi", TableName = "st_dt" };
value2 = "1";
type2 = "int";
break;
case "专利审查周期分析":
columnname1 = config.GroupCS;
value1 = showdata.Rows[e.RowIndex].Cells[0].Value.ToString();
type1 = "int";
break;
case "法律状态自定义分析":
break;
case "中国本土专利趋势":
break;
case "中美市场专利趋势":
break;
case "中欧市场专利趋势":
break;
case "中国市场重点技术排行榜":
break;
case "中国市场专利申请人排行榜":
break;
case "中国市场布局国家排行榜":
break;
#endregion
}
List<string> tables = new List<string>();
string where1 = "";
string where2 = "";
if (columnname1 != null)
{
tables.Add(columnname1.TableName);
if (type1 == "int")
{
where1 = string.Format(" and {0}.{1} ={2} ", columnname1.TableName, columnname1.ColName, value1);
}
else
{
where1 = string.Format(" and {0}.{1} ='{2}' ", columnname1.TableName, columnname1.ColName, value1);
}
}
if (columnname2 != null)
{
tables.Add(columnname2.TableName);
if (type2 == "int")
{
where2 = string.Format(" and {0}.{1} ={2} ", columnname2.TableName, columnname2.ColName, value2);
}
else
{
where2 = string.Format(" and {0}.{1} ='{2}' ", columnname2.TableName, columnname2.ColName, value2);
}
}
tables.Add("show_Base");
tables.Add("st_dt");
tables = tables.Distinct<string>().ToList<string>();
StringBuilder sbtable = new StringBuilder();
StringBuilder sbjoin = new StringBuilder();
foreach (var table in tables)
{
sbtable.Append(table + ",");
if (table == "st_dt") continue;
sbjoin.Append("st_dt.sid = " + table + ".sid and ");
}
string strtable = sbtable.ToString(0, sbtable.Length - 1);
string strjoin = sbjoin.ToString(0, sbjoin.Length - 4);
if (!String.IsNullOrEmpty(config.FilterSQL))
{
strtable += ", (" + config.FilterSQL + " ) as Filter ";
if (strjoin == "")
{
strjoin += string.Format("{0}.sid = Filter.sid", tables[0]);
}
else
{
strjoin += string.Format(" and {0}.sid = Filter.sid", tables[0]);
}
}
string select = " (@rowIdx:=@rowIdx+1) as '序号',st_dt.sid,st_dt.an as '申请号',st_dt.pn as '公开号',show_Base.Title as '标题'";
string where0 = " and st_dt.ztid=" + zid;
string sql = string.Format("select {0} from {1} where {2} {3} {4} {5}", select, strtable, strjoin, where0, where1, where2);
string sql1 = string.Format("select count(distinct st_dt.sid) from {1} where {2} {3} {4} {5}", select, strtable, strjoin, where0, where1, where2);
frmPtListmin frm = new frmPtListmin(dbname, zid, dbtype, sql, sql1);
frm.ShowDialog();
}