public static string GetStatSQL(string zid, stcfg cfg, CheckedListBox cklistzhibiao, ComboBox cmbgroupcolumn) { string strzhibiao = ""; string columns = ""; string join = ""; string groupby = ""; string wherezt = "st_dt.ztid =" + zid; string where = ""; string tables = ""; string sql = "select {0},{1} from {2} where {3} {4} {5} group by {6}"; List <string> tablenames = new List <string>(); tablenames.Add("st_iv"); strzhibiao = " count(distinct st_iv.iv) as '发明人数量' "; ChartColumn group = (ChartColumn)cmbgroupcolumn.SelectedItem; columns = string.Format("{0}.{1} as '{2}'", group.TableName, group.ColName, group.ShowName); groupby = string.Format("{0}.{1}", group.TableName, group.ColName, group.ShowName); tablenames.Add(group.TableName); if (cfg.StartYear != 0 && cfg.EndYear != 0) { where += string.Format(" and {0}.{1} between {2} and {3}", group.TableName, group.ColName, cfg.StartYear, cfg.EndYear); } tablenames = tablenames.Distinct().ToList <string>(); tablenames.Remove("st_dt"); tablenames.Add("st_dt"); foreach (var tb in tablenames) { tables += tb + ","; } tables = tables.Trim(','); if (tablenames.Count > 1) { for (int i = 1; i < tablenames.Count; i++) { if (join == "") { join += string.Format("{0}.sid={1}.sid", tablenames[0], tablenames[i]); } else { join += string.Format(" and {0}.sid={1}.sid", tablenames[0], tablenames[i]); } } } if (join == "") { wherezt = string.Format("st_dt.ztid={0}", zid); } else { wherezt = string.Format(" and st_dt.ztid={0}", zid); } string tmpsql = string.Format(sql, columns, strzhibiao, tables, join, wherezt, where, groupby); return(tmpsql); }
public static string GetStatSQL(string zid, stcfg cfg, CheckedListBox cklistzhibiao, ComboBox cmbgroupcolumn) { string strzhibiao = ""; string columns = ""; string join = ""; string groupby = ""; string wherezt = "st_dt.ztid =" + zid; string where = ""; string tables = ""; string sql = "select {0},{1} from {2} where {3} {4} {5} group by {6}"; List <string> tablenames = new List <string>(); //for (int i = 0; i <= cklistzhibiao.CheckedItems.Count - 1; i++) //{ // ChartZHIbiAO x = (ChartZHIbiAO)cklistzhibiao.CheckedItems[i]; // string tmpzhibiao = string.Format("{0}(distinct {1}.{2} ) as '{3}',", x.FunCType, x.TableName, x.ZHiBIAO, x.ShowName); // tablenames.Add(x.TableName); // strzhibiao += tmpzhibiao; //} //strzhibiao = strzhibiao.Trim(','); tablenames.Add("st_pa"); if (cfg.UseCPY == false) { strzhibiao = " count(distinct st_dt.sid) as '申请量' ,count(distinct st_pa.pa) as '申请人数量' "; } else { strzhibiao = " count(distinct st_dt.sid) as '申请量' ,count(distinct st_dt.cpy) as '申请人数量' "; } ChartColumn group = (ChartColumn)cmbgroupcolumn.SelectedItem; columns = string.Format("{0}.{1} as '{2}'", group.TableName, group.ColName, group.ShowName); groupby = string.Format("{0}.{1}", group.TableName, group.ColName, group.ShowName); tablenames.Add(group.TableName); if (cfg.StartYear != 0 && cfg.EndYear != 0) { where += string.Format(" and {0}.{1} between {2} and {3}", group.TableName, group.ColName, cfg.StartYear, cfg.EndYear); } tablenames = tablenames.Distinct().ToList <string>(); tablenames.Remove("st_dt"); tablenames.Add("st_dt"); foreach (var tb in tablenames) { tables += tb + ","; } tables = tables.Trim(','); if (tablenames.Count > 1) { for (int i = 1; i < tablenames.Count; i++) { if (join == "") { join += string.Format("{0}.sid={1}.sid", tablenames[0], tablenames[i]); } else { join += string.Format(" and {0}.sid={1}.sid", tablenames[0], tablenames[i]); } } } if (join == "") { wherezt = string.Format("st_dt.ztid={0}", zid); } else { wherezt = string.Format(" and st_dt.ztid={0}", zid); } string tmpsql = string.Format(sql, columns, strzhibiao, tables, join, wherezt, where, groupby); return(tmpsql); }