private void bsearch_Click(object sender, EventArgs e)
{
// Execute the query again
string searchname = param.SearchDataset;
DataInfo dinfo = report.DataInfo[searchname];
DatabaseInfo dbinfo = report.DatabaseInfo[dinfo.DatabaseAlias];
if (param.SearchParam.Length > 0)
{
report.Params[param.SearchParam].Value = textsearch.Text;
ptop.Visible = true;
}
else
{
ptop.Visible = false;
}
IDataReader areader = dbinfo.GetDataReaderFromSQL(dinfo.SQL, dinfo.Alias, report.Params, false);
PagedDataTable atable = new PagedDataTable();
atable.CurrentReader = areader;
if (dgrid.DataSource == null)
{
dgrid.TableStyles.Add(DataShow.CreateDataGridStyle(atable, dgrid));
}
dgrid.DataSource = atable;
}
public ActionResult GetZLDeviceData(string id)
{
string page = Request["page"].Trim();
string rows = Request["rows"].Trim();
FieldModel where =
T_BASE_COMPANY_ZL.ISDEL == "0";
if (!string.IsNullOrEmpty(id))
{
id = id.Trim();
where &= T_BASE_COMPANY_ZL.COMPANYID == id;
}
else
{
where &= T_BASE_COMPANY_ZL.COMPANYID == "0";
}
SqlModel sql = SqlModel.SelectAll(T_BASE_COMPANY_ZL.NAME.As("PKNAME")
)
.From(DB.T_BASE_COMPANY_ZL)
.LeftJoin(DB.T_BASE_COMPANY_PK).On(T_BASE_COMPANY_ZL.PKID == T_BASE_COMPANY_PK.ID)
//.LeftJoin(DB.BASDIC.As("ISHB")).On(T_BASE_COMPANY_CONTACT.ISHB == "ISHB".Field("CODE") & "ISHB".Field("TYPECODE") == "TrueOrFalse")
.Where(where)
.OrderByDesc(T_BASE_COMPANY_ZL.CREATETIME);
PagedDataTable dtResult = sql.ExecToPagedTable(Convert.ToInt32(page), Convert.ToInt32(rows), new OrderByModel()
{
OrderType = OrderType.Desc, FieldModel = T_BASE_COMPANY_ZL.CREATETIME
});
return(Content(JsonConvert.SerializeObject(new
{
total = dtResult.TotalCount,
rows = dtResult.Data
}), "application/json", Encoding.UTF8));
}
public ActionResult Products2()
{
PagedDataTable model = new PagedDataTable();
model.PageSize = 10;
model.InitOrderBy("ProductID");
return(ProductDataSet(model));
}
public ActionResult Products3()
{
PagedDataTable model = new PagedDataTable();
model.PageSize = 10;
model.InitOrderBy("ProductName");
ViewBag.SupplierList = db.Suppliers.ToList();
return(ProductDataSet(model));
}
/// <summary>
/// 支持用DataTable
/// </summary>
/// <param name="sqlModel"></param>
/// <param name="data"></param>
/// <param name="dtData"></param>
/// <returns></returns>
public JsonResult PagedJson(SqlModel sqlModel, RequestData data, DataTable dtData)
{
int page = this.GetPage(data);
int rows = this.GetRows(data);
OrderByModel om = this.GetOrderBy(data, sqlModel);
PagedDataTable pdt = sqlModel.ExecToPagedTable(page, rows, om, dtData);
dynamic result = pdt.ToDynamic();
return(Json(result));
}
public ActionResult ProductDataSet(PagedDataTable model)
{
model.ToggleSort();
var srt = model.ParseSort();
string queryText = "SELECT Count(*) RowCt FROM [dbo].[Products] " +
" \r\n" +
"SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued \r\n" +
"FROM (SELECT *, " +
" (ROW_NUMBER() "+
" OVER ( ORDER BY "+
" CASE when @srtDir = 'ASC' AND @srt = 'ProductID' THEN ProductID END ASC, "+
" CASE when @srtDir = 'ASC' AND @srt = 'ProductName' THEN ProductName END ASC, "+
" CASE when @srtDir = 'ASC' AND @srt = 'SupplierID' THEN SupplierID END ASC, "+
" CASE when @srtDir = 'ASC' AND @srt = 'CategoryID' THEN CategoryID END ASC, "+
" CASE when @srtDir = 'ASC' AND @srt = 'QuantityPerUnit' THEN QuantityPerUnit END ASC, "+
" CASE when @srtDir = 'ASC' AND @srt = 'UnitPrice' THEN UnitPrice END ASC, "+
" CASE when @srtDir = 'ASC' AND @srt = 'UnitsInStock' THEN UnitsInStock END ASC, "+
" CASE when @srtDir = 'ASC' AND @srt = 'UnitsOnOrder' THEN UnitsOnOrder END ASC, "+
" CASE when @srtDir = 'ASC' AND @srt = 'ReorderLevel' THEN ReorderLevel END ASC, "+
" CASE when @srtDir = 'ASC' AND @srt = 'Discontinued' THEN Discontinued END ASC, "+
" CASE when @srtDir = 'DESC' AND @srt = 'ProductID' THEN ProductID END DESC, "+
" CASE when @srtDir = 'DESC' AND @srt = 'ProductID' THEN ProductID END DESC, "+
" CASE when @srtDir = 'DESC' AND @srt = 'ProductName' THEN ProductName END DESC, "+
" CASE when @srtDir = 'DESC' AND @srt = 'SupplierID' THEN SupplierID END DESC, "+
" CASE when @srtDir = 'DESC' AND @srt = 'CategoryID' THEN CategoryID END DESC, "+
" CASE when @srtDir = 'DESC' AND @srt = 'QuantityPerUnit' THEN QuantityPerUnit END DESC, "+
" CASE when @srtDir = 'DESC' AND @srt = 'UnitPrice' THEN UnitPrice END DESC, "+
" CASE when @srtDir = 'DESC' AND @srt = 'UnitsInStock' THEN UnitsInStock END DESC, "+
" CASE when @srtDir = 'DESC' AND @srt = 'UnitsOnOrder' THEN UnitsOnOrder END DESC, "+
" CASE when @srtDir = 'DESC' AND @srt = 'ReorderLevel' THEN ReorderLevel END DESC, "+
" CASE when @srtDir = 'DESC' AND @srt = 'Discontinued' THEN Discontinued END DESC "+
" )) as RowID \r\n"+
"FROM [dbo].[Products] ) as tbl \r\n" +
"WHERE RowID BETWEEN @startRow AND @endRow ";
Dictionary <string, string> dict = new Dictionary <string, string>();
dict.Add("srt", srt.SortField);
dict.Add("srtDir", srt.SortDirection);
dict.Add("startRow", (1 + (model.PageNumberZeroIndex * model.PageSize)).ToString());
dict.Add("endRow", (model.PageNumber * model.PageSize).ToString());
List <SqlParameter> parms = BuildParms(dict);
DataSet ds = ExecDataSet(queryText, parms);
model.TotalRecords = Convert.ToInt32(ds.Tables[0].Rows[0]["RowCt"]);
model.SetData(ds.Tables[1]);
ModelState.Clear();
return(View(model));
}
public ActionResult GetData(string CompanyName, string AREA, string HYLB, string GZCD, string isVOCs)
{
string page = Request["page"].Trim();
string rows = Request["rows"].Trim();
FieldModel where = T_BASE_COMPANY.ISDEL == "0";
if (!string.IsNullOrEmpty(CompanyName))
{
CompanyName = HttpUtility.UrlDecode(CompanyName.Trim()).Trim();
where &= T_BASE_COMPANY.NAME.Like(CompanyName);
}
if (!string.IsNullOrEmpty(AREA) && AREA != "all")
{
AREA = AREA.Trim();
where &= T_BASE_COMPANY.AREA == AREA;
}
if (!string.IsNullOrEmpty(HYLB))
{
HYLB = HYLB.Trim();
where &= T_BASE_COMPANY.BASTYPE == HYLB;
}
if (!string.IsNullOrEmpty(GZCD))
{
GZCD = GZCD.Trim();
where &= T_BASE_COMPANY.GZCD == GZCD;
}
if (!string.IsNullOrEmpty(isVOCs))
{
isVOCs = isVOCs.Trim();
where &= T_BASE_COMPANY.ISVOCS == isVOCs;
}
SqlModel sql = SqlModel.Select(T_BASE_COMPANY.ID, T_BASE_COMPANY.ID.As("ID2"), T_BASE_COMPANY.NAME, T_SYS_AREA.AREA_TEXT.As("NAREA"), T_BASE_COMPANY.ADDRESS, T_BASE_COMPANY.BASTYPE, "BASTYPE".Field("TITLE").As("NBASTYPE"),
"GZCD".Field("TITLE").As("NGZCD"), T_BASE_COMPANY.LEGALOR
)
.From(DB.T_BASE_COMPANY)
.LeftJoin(DB.T_SYS_AREA).On(T_BASE_COMPANY.AREA == T_SYS_AREA.AREA_CODE)
.LeftJoin(DB.BASDIC.As("BASTYPE")).On(T_BASE_COMPANY.BASTYPE == "BASTYPE".Field("CODE") & "BASTYPE".Field("TYPECODE") == "IndustryType")
.LeftJoin(DB.BASDIC.As("GZCD")).On(T_BASE_COMPANY.GZCD == "GZCD".Field("CODE") & "GZCD".Field("TYPECODE") == "CompanyGZCD")
.Where(where)
.OrderByDesc(T_BASE_COMPANY.CREATETIME);
PagedDataTable dtResult = sql.ExecToPagedTable(Convert.ToInt32(page), Convert.ToInt32(rows), new OrderByModel()
{
OrderType = OrderType.Desc, FieldModel = T_BASE_COMPANY.CREATETIME
});
return(Content(JsonConvert.SerializeObject(new
{
total = dtResult.TotalCount,
rows = dtResult.Data
}), "application/json", Encoding.UTF8));
}
public ActionResult GetPKInfoData(string id, string PKType)
{
string page = Request["page"].Trim();
string rows = Request["rows"].Trim();
FieldModel where =
T_BASE_COMPANY_PK.ISDEL == "0";
if (!string.IsNullOrEmpty(id))
{
id = id.Trim();
where &= T_BASE_COMPANY_PK.COMPANYID == id;
}
else
{
where &= T_BASE_COMPANY_PK.COMPANYID == "0";
}
if (!string.IsNullOrEmpty(PKType))
{
id = id.Trim();
where &= T_BASE_COMPANY_PK.TYPE == PKType;
}
SqlModel sql = SqlModel.SelectAll("GASTYPE".Field("TITLE").As("NGASTYPE"), "WAY".Field("TITLE").As("NWAY"), "REGULAR".Field("TITLE").As("NREGULAR")
)
.From(DB.T_BASE_COMPANY_PK)
.LeftJoin(DB.BASDIC.As("GASTYPE")).On(T_BASE_COMPANY_PK.TYPE == "GASTYPE".Field("CODE") & "GASTYPE".Field("TYPECODE") == "CompanyPKGasType")
.LeftJoin(DB.BASDIC.As("WAY")).On(T_BASE_COMPANY_PK.WAY == "WAY".Field("CODE") & "WAY".Field("TYPECODE") == "CompanyPKWay")
.LeftJoin(DB.BASDIC.As("REGULAR")).On(T_BASE_COMPANY_PK.REGULAR == "REGULAR".Field("CODE") & "REGULAR".Field("TYPECODE") == "CompanyPKPFGL")
.Where(where)
.OrderByDesc(T_BASE_COMPANY_PK.CREATETIME);
PagedDataTable dtResult = sql.ExecToPagedTable(Convert.ToInt32(page), Convert.ToInt32(rows), new OrderByModel()
{
OrderType = OrderType.Desc, FieldModel = T_BASE_COMPANY_PK.CREATETIME
});
return(Content(JsonConvert.SerializeObject(new
{
total = dtResult.TotalCount,
rows = dtResult.Data
}), "application/json", Encoding.UTF8));
}
public ActionResult Products3(PagedDataTable model)
{
ViewBag.SupplierList = db.Suppliers.ToList();
return(ProductDataSet(model));
}
public ActionResult Products2(PagedDataTable model)
{
return(ProductDataSet(model));
}
