摘要:List
建立類別 Entity
//欄為類別
public class RptB11_2Entity
{
public int sendno { get; set; }
public string range { get; set; }
public int P_a { get; set; }
public int P_b { get; set; }
public int P_c { get; set; }
public int P_d { get; set; }
public int P_e { get; set; }
public int P_f { get; set; }
public int P_g { get; set; }
public int L_a { get; set; }
public int L_b { get; set; }
public int L_c { get; set; }
public int L_d { get; set; }
public int L_e { get; set; }
public int L_f { get; set; }
public int L_g { get; set; }
public int PL_a { get; set; }
public int PL_b { get; set; }
public int PL_c { get; set; }
public int PL_d { get; set; }
public int PL_e { get; set; }
public int PL_f { get; set; }
public int PL_g { get; set; }
}
-------------------------------------------------------------------------
//查詢條件類別
public class QryArg
{
public string A01 { get; set; }
public string A02 { get; set; }
public string A16 { get; set; }
public string A11 { get; set; }
public string SDate { get; set; }
public string EDate { get; set; }
public string MoneyStart { get; set; }
public string MoneyEnd { get; set; }
}------------------------------------------------------------------
//套入SQL語法
public DataSet GetRangeData(QryArg qryArg)
{
//單一資本額區間查詢條件
string SQL = "";
SQL += " Select * from testDB " //帶入SQL 主語法
//條件語法
string strWhere = " Where 1=1 ";
if (!string.IsNullOrEmpty(qryArg.A01))
strWhere += " AND A01 =@A01 ";
if (!string.IsNullOrEmpty(qryArg.A02))
strWhere += " AND A02 like @A02 ";
if (!string.IsNullOrEmpty(qryArg.SDate))
strWhere += " AND c.Createtime >= @SDate ";
if (!string.IsNullOrEmpty(qryArg.EDate))
strWhere += " AND c.Createtime <= @EDate ";
if (!string.IsNullOrEmpty(qryArg.MoneyStart))
strWhere += " AND A05 >= @MoneyStart ";
if (!string.IsNullOrEmpty(qryArg.MoneyEnd))
strWhere += " AND A05 <= @MoneyEnd ";
GenericCore mygc = new GenericCore("02");
SqlCommand mycom = new SqlCommand(SQL + strWhere, mygc.GetDBDaoObject().GetConnection);
if (!string.IsNullOrEmpty(qryArg.A01))
mycom.Parameters.AddWithValue("@A01", qryArg.A01);
if (!string.IsNullOrEmpty(qryArg.A02))
mycom.Parameters.AddWithValue("@A02", "%" + qryArg.A02 + "%");
if (!string.IsNullOrEmpty(qryArg.SDate))
{
string tsdate = qryArg.SDate.Substring(3, 6);
tsdate = (Convert.ToInt32(qryArg.SDate.Substring(0, 3)) + 1911).ToString() + tsdate;//民國轉西元
mycom.Parameters.AddWithValue("@SDate", Convert.ToDateTime(tsdate));
}
if (!string.IsNullOrEmpty(qryArg.EDate))
{
string tedate = qryArg.EDate.Substring(3, 6);
tedate = (Convert.ToInt32(qryArg.EDate.Substring(0, 3)) + 1911).ToString() + tedate + " 23:59:59.997" ; //SQL時間最晚化
mycom.Parameters.AddWithValue("@EDate", Convert.ToDateTime(tedate));
}
if (!string.IsNullOrEmpty(qryArg.MoneyStart))
mycom.Parameters.AddWithValue("@MoneyStart", qryArg.MoneyStart);
if (!string.IsNullOrEmpty(qryArg.MoneyEnd))
mycom.Parameters.AddWithValue("@MoneyEnd", qryArg.MoneyEnd);
return SqlDBUtils.ExecuteDataSet(mycom);
}
------------------------------------------------------------------------------------------------------------------------------
//區分資本額區間
private List SetWhere()
{
List lst = new List(); //純粹放查詢條件組合物件
bool isAllMRange = false; //判斷是否有填寫資本額區間
//有填寫資本額區間
for (int i = 1; i <= 20; i++)
{
TextBox txtMStart = (TextBox)this.Master.FindControl("ContentPlaceHolder1").FindControl(string.Format("txtA051_{0}", i));
TextBox txtMEnd = (TextBox)this.Master.FindControl("ContentPlaceHolder1").FindControl(string.Format("txtA052_{0}", i));
if (txtMStart.Text != "" || txtMEnd.Text != "") //有設開始.結束區間
{
QryArg arg = new QryArg();
if (txtA01.SelectedValue != "")
arg.A01 = txtA01.SelectedValue;
if (txtA02.SelectedValue != "")
arg.A02 = txtA02.SelectedValue;
if (txtCreatetime_1.Text != "")
arg.SDate = txtCreatetime_1.Text;
if (txtCreatetime_2.Text != "")
arg.EDate = txtCreatetime_2.Text;
arg.MoneyStart = txtMStart.Text;
arg.MoneyEnd = txtMEnd.Text;
lst.Add(arg);
isAllMRange = true;
}
}
if (isAllMRange == true)
{
int firstCount = lst.Where(p => p.MoneyStart == "0").Count();
if (firstCount == 0)
{
var aMin = (from q in lst select new { Start = int.Parse(q.MoneyStart) }).Min(p => p.Start).ToString();
QryArg FirstArg = new QryArg();
if (txtA01.SelectedValue != "")
FirstArg.A01 = txtA01.SelectedValue;
if (txtA02.SelectedValue != "")
FirstArg.A02 = txtA02.SelectedValue;
if (txtCreatetime_1.Text != "")
FirstArg.SDate = txtCreatetime_1.Text;
if (txtCreatetime_2.Text != "")
FirstArg.EDate = txtCreatetime_2.Text;
FirstArg.MoneyStart = "0";
FirstArg.MoneyEnd = (Convert.ToInt64(aMin)-1).ToString().Trim();
lst.Insert(0, FirstArg);
}
int lastCount = lst.Where(p => p.MoneyEnd == "").Count();
if (lastCount == 0)
{
var aMax = (from q in lst select new { End = int.Parse(q.MoneyEnd) }).Max(p => p.End).ToString();
QryArg lastArg = new QryArg();
if (txtA01.SelectedValue != "")
lastArg.A01 = txtA01.SelectedValue;
if (txtA02.SelectedValue != "")
lastArg.A02 = txtA02.SelectedValue;
if (txtA16.SelectedValue != "")
lastArg.A16 = txtA16.SelectedValue;
if (txtA11.SelectedValue != "")
lastArg.A11 = txtA11.SelectedValue;
if (txtCreatetime_1.Text != "")
lastArg.SDate = txtCreatetime_1.Text;
if (txtCreatetime_2.Text != "")
lastArg.EDate = txtCreatetime_2.Text;
lastArg.MoneyStart = (Convert.ToInt64(aMax)+1).ToString().Trim();
lst.Add(lastArg);
}
}
//無填寫資本額區間
if (isAllMRange == false)
{
string[,] MoneyRange = { { "0", "3000000" }, { "3000001", "6000000" }, { "6000001", "9000000" }, { "9000001", "12000000" }, { "12000001", "" } };
for (int i = 0; i < 5; i++)
{
QryArg arg = new QryArg();
if (txtA01.SelectedValue != "")
arg.A01 = txtA01.SelectedValue;
if (txtA02.SelectedValue != "")
arg.A02 = txtA02.SelectedValue;
if (txtA16.SelectedValue != "")
arg.A16 = txtA16.SelectedValue;
if (txtA11.SelectedValue != "")
arg.A11 = txtA11.SelectedValue;
if (txtCreatetime_1.Text != "")
arg.SDate = txtCreatetime_1.Text;
if (txtCreatetime_2.Text != "")
arg.EDate = txtCreatetime_2.Text;
arg.MoneyStart = MoneyRange[i, 0];
arg.MoneyEnd = MoneyRange[i, 1];
lst.Add(arg);
}
}
return lst;
}
----------------------------------------------------------------------
//資料bind GridView
private void QueryData(bool bType)
{
List lst = SetWhere();
DataSet ds = new DataSet();
List lst_Tclass = new List();
foreach (QryArg arg in lst)
{
RptEntity.RptB11_2Entity tclass = new RptEntity.RptB11_2Entity();
ds = GetRangeData(arg);
///分配P
int P_ra = (from q1 in ds.Tables[0].AsEnumerable()
where q1.Field("A11") != "" && q1.Field("A11").Substring(0, 1) == "A"
&& q1.Field("A02").IndexOf("P00") >= 0
select q1).Count();
int P_rb = (from q1 in ds.Tables[0].AsEnumerable()
where q1.Field("A11") != "" && q1.Field("A11").Substring(1, 1) == "B"
&& q1.Field("A02").IndexOf("P00") >= 0
select q1).Count();
int P_rc = (from q1 in ds.Tables[0].AsEnumerable()
where q1.Field("A11") != "" && q1.Field("A11").Substring(2, 1) == "C"
&& q1.Field("A02").IndexOf("P00") >= 0
select q1).Count();
int P_rd = (from q1 in ds.Tables[0].AsEnumerable()
where q1.Field("A11") != "" && q1.Field("A11").Substring(3, 1) == "D"
&& q1.Field("A02").IndexOf("P00") >= 0
select q1).Count();
int P_re = (from q1 in ds.Tables[0].AsEnumerable()
where q1.Field("A11") != "" && q1.Field("A11").Substring(4, 1) == "E"
&& q1.Field("A02").IndexOf("P00") >= 0
select q1).Count();
int P_rf = (from q1 in ds.Tables[0].AsEnumerable()
where q1.Field("A11") != "" && q1.Field("A11").Substring(5, 1) == "F"
&& q1.Field("A02").IndexOf("P00") >= 0
select q1).Count();
int P_rg = (from q1 in ds.Tables[0].AsEnumerable()
where q1.Field("A11") != "" && q1.Field("A11").Substring(6, 1) == "Z"
&& q1.Field("A02").IndexOf("P00") >= 0
select q1).Count();
///分配L
int L_ra = (from q1 in ds.Tables[0].AsEnumerable()
where q1.Field("A11") != "" && q1.Field("A11").Substring(0, 1) == "A"
&& q1.Field("A02").IndexOf("0L0") >= 0
select q1).Count();
int L_rb = (from q1 in ds.Tables[0].AsEnumerable()
where q1.Field("A11") != "" && q1.Field("A11").Substring(1, 1) == "B"
&& q1.Field("A02").IndexOf("0L0") >= 0
select q1).Count();
int L_rc = (from q1 in ds.Tables[0].AsEnumerable()
where q1.Field("A11") != "" && q1.Field("A11").Substring(2, 1) == "C"
&& q1.Field("A02").IndexOf("0L0") >= 0
select q1).Count();
int L_rd = (from q1 in ds.Tables[0].AsEnumerable()
where q1.Field("A11") != "" && q1.Field("A11").Substring(3, 1) == "D"
&& q1.Field("A02").IndexOf("0L0") >= 0
select q1).Count();
int L_re = (from q1 in ds.Tables[0].AsEnumerable()
where q1.Field("A11") != "" && q1.Field("A11").Substring(4, 1) == "E"
&& q1.Field("A02").IndexOf("0L0") >= 0
select q1).Count();
int L_rf = (from q1 in ds.Tables[0].AsEnumerable()
where q1.Field("A11") != "" && q1.Field("A11").Substring(5, 1) == "F"
&& q1.Field("A02").IndexOf("0L0") >= 0
select q1).Count();
int L_rg = (from q1 in ds.Tables[0].AsEnumerable()
where q1.Field("A11") != "" && q1.Field("A11").Substring(6, 1) == "Z"
&& q1.Field("A02").IndexOf("0L0") >= 0
select q1).Count();
///分配PL
int PL_ra = (from q1 in ds.Tables[0].AsEnumerable()
where q1.Field("A11") != "" && q1.Field("A11").Substring(0, 1) == "A"
&& q1.Field("A02").IndexOf("PL0") >= 0
select q1).Count();
int PL_rb = (from q1 in ds.Tables[0].AsEnumerable()
where q1.Field("A11") != "" && q1.Field("A11").Substring(1, 1) == "B"
&& q1.Field("A02").IndexOf("PL0") >= 0
select q1).Count();
int PL_rc = (from q1 in ds.Tables[0].AsEnumerable()
where q1.Field("A11") != "" && q1.Field("A11").Substring(2, 1) == "C"
&& q1.Field("A02").IndexOf("PL0") >= 0
select q1).Count();
int PL_rd = (from q1 in ds.Tables[0].AsEnumerable()
where q1.Field("A11") != "" && q1.Field("A11").Substring(3, 1) == "D"
&& q1.Field("A02").IndexOf("PL0") >= 0
select q1).Count();
int PL_re = (from q1 in ds.Tables[0].AsEnumerable()
where q1.Field("A11") != "" && q1.Field("A11").Substring(4, 1) == "E"
&& q1.Field("A02").IndexOf("PL0") >= 0
select q1).Count();
int PL_rf = (from q1 in ds.Tables[0].AsEnumerable()
where q1.Field("A11") != "" && q1.Field("A11").Substring(5, 1) == "F"
&& q1.Field("A02").IndexOf("PL0") >= 0
select q1).Count();
int PL_rg = (from q1 in ds.Tables[0].AsEnumerable()
where q1.Field("A11") != "" && q1.Field("A11").Substring(6, 1) == "Z"
&& q1.Field("A02").IndexOf("PL0") >= 0
select q1).Count();
if (string.IsNullOrEmpty(arg.MoneyEnd))
tclass.range = arg.MoneyStart + "以上";
else if (string.IsNullOrEmpty(arg.MoneyStart))
tclass.range = "0 ~" + arg.MoneyEnd;
else
tclass.range = arg.MoneyStart + "~" + arg.MoneyEnd;
tclass.P_a = P_ra;
tclass.P_b = P_rb;
tclass.P_c = P_rc;
tclass.P_d = P_rd;
tclass.P_e = P_re;
tclass.P_f = P_rf;
tclass.P_g = P_rg;
tclass.L_a = L_ra;
tclass.L_b = L_rb;
tclass.L_c = L_rc;
tclass.L_d = L_rd;
tclass.L_e = L_re;
tclass.L_f = L_rf;
tclass.L_g = L_rg;
tclass.PL_a = PL_ra;
tclass.PL_b = PL_rb;
tclass.PL_c = PL_rc;
tclass.PL_d = PL_rd;
tclass.PL_e = PL_re;
tclass.PL_f = PL_rf;
tclass.PL_g = PL_rg;
lst_Tclass.Add(tclass);
}
if (bType)
{
grdB11_2.DataSource = lst_Tclass;
grdB11_2.DataBind();
}
else
{
rptB11_2_chi rpt = new rptB11_2_chi();
string path = rpt.ExportExcel(lst_Tclass, txtA01.Text.Trim(), txtA02.Text.Trim(), txtCreatetime_1.Text.Trim(), txtCreatetime_2.Text.Trim());
Response.Redirect(path);
}
}
-------------------------------------------------
