void SelectCheckReport() //点检查询结果
{
JavaScriptSerializer s = new JavaScriptSerializer();
HttpRequest request = HttpContext.Current.Request;
string AssemblyLine = request.Params["fl_name"];
string Station = request.Params["st_no"];
string StartTime = request.Params["StartTime"];
string EndTime = request.Params["EndTime"];
int PageIndex = Convert.ToInt32(request["page"]);
int PageSize = Convert.ToInt32(request["rows"]); // string.IsNullOrEmpty(StartTime)
string SortFlag = request["sort"];
string sortOrder = request["order"];
sort = SortFlag;
order = sortOrder;
string where = "";
if (!string.IsNullOrEmpty(AssemblyLine))
{
where = " and a.StationNO LIKE '" + AssemblyLine + "%'";
}
if (!string.IsNullOrEmpty(Station))
{
where = " and a.StationNO = '" + Station + "'";
}
if (!string.IsNullOrEmpty(StartTime))
{
where += " and cast(a.CreateTime as datetime) > '" + StartTime + "'";
}
if (!string.IsNullOrEmpty(EndTime))
{
where += " and cast(a.CreateTime as datetime) < '" + EndTime + "'";
}
int StartIndex = PageSize * (PageIndex - 1) + 1;
int EndIndex = StartIndex + PageSize - 1;
int totalcount;
DataTable ResTable = checkReport_BLL.getTable(PageSize, PageIndex, StartIndex, EndIndex, sort, order, where, out totalcount);
DataTable NewResTable = GetPagedTable(ResTable, PageIndex, PageSize);
string json = FunCommon.DataTableToJson2(totalcount, NewResTable);
Response.Write(json);
}
void QueryBOMFororderPn()
{
if (page == 1)
{
string sql1 = @"select count([bom_id]) total from [mg_BOM];";
string sql2 = @"
with data as
(
select p.part_id,p.part_no,pbrel.bom_id from mg_part_bom_rel pbrel left join mg_part p on pbrel.part_id=p.part_id
)
SELECT [bom_id]
,[bom_PN]
,[bom_customerPN]
,[bom_isCustomerPN]
,case [bom_isCustomerPN]
when 1 then '是'
else '否'
end bom_isCustomerPNName
,[bom_leve]
,l.prop_name [bom_leveName]
,[bom_materialid]
,m.prop_name [bom_material]
,[bom_suppllerid]
,s.prop_name [bom_suppller]
,[bom_categoryid]
,ca.prop_name [bom_category]
,[bom_storeid]
,st.prop_name [bom_storeName]
,[bom_colorid]
,co.prop_name [bom_colorname]
,mm.Prop_type [bom_proptype]
,mm.prop_name [bom_propname]
,mm.prop_id [bom_propid]
,[bom_profile]
,[bom_weight]
,[bom_desc]
,[bom_descCH]
,[bom_picture]
,STUFF((SELECT ','+cast (part_id as varchar) from data p where p.bom_id=b.bom_id for xml path('')),1,1,'')partIDs
,STUFF((SELECT ','+cast (part_no as varchar) from data p where p.bom_id=b.bom_id for xml path('')),1,1,'')partNOs
,b.barcode_start
,b.barcode_number
FROM [mg_BOM] b
left join mg_Property m on b.bom_materialid=m.prop_id
left join mg_Property s on b.bom_suppllerid=s.prop_id
left join mg_Property ca on b.bom_categoryid=ca.prop_id
left join mg_Property co on b.bom_colorid=co.prop_id
left join mg_Property l on b.bom_leve=l.prop_id
left join mg_Property st on b.bom_storeid=st.prop_id
left join Mat_SupplierList ms on b.bom_suppllerid=ms.SupplierID
left join Mg_MaterialProperty mm on ms.WareHouseID=mm.prop_id
order by b.bom_id desc;mm
";
DataSet ds = SqlHelper.GetDataSetTableMapping(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql1 + sql2, new string[] { "count", "data" }, null);
DataTable dt1 = ds.Tables["count"];
//total = DataHelper.GetCellDataToStr(dt1.Rows[0], "total");
dt2 = ds.Tables["data"];
}
else
{
string sql1 = @"select count([bom_id]) total from [mg_BOM];";
string sql2 = @"
with data as
(
select p.part_id,p.part_no,pbrel.bom_id from mg_part_bom_rel pbrel left join mg_part p on pbrel.part_id=p.part_id
)
SELECT [bom_id]
,[bom_PN]
,[bom_customerPN]
,[bom_isCustomerPN]
,case [bom_isCustomerPN]
when 1 then '是'
else '否'
end bom_isCustomerPNName
,[bom_leve]
,l.prop_name [bom_leveName]
,[bom_materialid]
,m.prop_name [bom_material]
,[bom_suppllerid]
,s.prop_name [bom_suppller]
,[bom_categoryid]
,ca.prop_name [bom_category]
,[bom_storeid]
,st.prop_name [bom_storeName]
,[bom_colorid]
,co.prop_name [bom_colorname]
,mm.Prop_type [bom_proptype]
,mm.prop_name [bom_propname]
,mm.prop_id [bom_propid]
,[bom_profile]
,[bom_weight]
,[bom_desc]
,[bom_descCH]
,[bom_picture]
,STUFF((SELECT ','+cast (part_id as varchar) from data p where p.bom_id=b.bom_id for xml path('')),1,1,'')partIDs
,STUFF((SELECT ','+cast (part_no as varchar) from data p where p.bom_id=b.bom_id for xml path('')),1,1,'')partNOs
,b.barcode_start
,b.barcode_number
FROM [mg_BOM] b
left join mg_Property m on b.bom_materialid=m.prop_id
left join mg_Property s on b.bom_suppllerid=s.prop_id
left join mg_Property ca on b.bom_categoryid=ca.prop_id
left join mg_Property co on b.bom_colorid=co.prop_id
left join mg_Property l on b.bom_leve=l.prop_id
left join mg_Property st on b.bom_storeid=st.prop_id
left join Mat_SupplierList ms on b.bom_suppllerid=ms.SupplierID
left join Mg_MaterialProperty mm on ms.WareHouseID=mm.prop_id
order by b.bom_id desc;
";
DataSet ds = SqlHelper.GetDataSetTableMapping(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql1 + sql2, new string[] { "count", "data" }, null);
DataTable dt1 = ds.Tables["count"];
//string total = DataHelper.GetCellDataToStr(dt1.Rows[0], "total");
dt2 = ds.Tables["data"];
}
DataTable dt = dt2; //这里是填充DataTable数据
DataTable dtNew = dt.Copy(); //复制dt表数据结构
dtNew.Clear(); //清楚数据
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["bom_PN"].ToString() == OrderPn)
{
dtNew.Rows.Add(dt.Rows[i].ItemArray); //添加数据行
}
}
DataTable dt4 = GetPagedTable(dtNew, 1, 10);
int total = dtNew.Rows.Count;
string json = FunCommon.DataTableToJson2(total, dt4);
//ExcelHelper.ExportDTtoExcel(dtNew, "HeaderText", HttpContext.Current.Request.MapPath("~/App_Data/excel2776.xlsx"));
ListView a = new ListView();
a = dataTableToListview.dataTableToListview1(a, dtNew);
List <int> list = new List <int>()
{
18, 18, 18, 18, 18, 18, 18, 18, 18, 18, 18, 18, 18, 18, 18, 18, 18, 18, 18, 18, 18, 18, 18, 18, 18, 18
};
dataTableToListview.ReportToExcel(a, list, "HeaderText");
Response.Write(json);
Response.End();
}
public void ProcessRequest(HttpContext context)
{
HttpRequest request = System.Web.HttpContext.Current.Request;
string StartTime = request["start_time"];
string EndTime = request["end_time"];
string clnameid = request["clnameid"];
string method = context.Request["method"];
int PageSize = Convert.ToInt32(request["rows"]);
int PageIndex = Convert.ToInt32(request["page"]);
string SortFlag = request["sort"];
string sortOrder = request["order"];
string where = "";
if (!string.IsNullOrEmpty(StartTime))
{
where += " and cl_starttime>='" + StartTime + "'";
}
if (!string.IsNullOrEmpty(EndTime))
{
where += " and cl_endtime<='" + EndTime + "'";
}
if (!string.IsNullOrEmpty(clnameid))
{
where += " and cl_name like'%" + clnameid + "%'";
}
int totalcount;
DataTable resTable = new DataTable();
sort = SortFlag;
order = sortOrder;
int StartIndex = PageSize * (PageIndex - 1) + 1;
int EndIndex = StartIndex + PageSize - 1;
if (string.IsNullOrEmpty(method))
{
resTable = FTT_BLL.getTable(PageSize, PageIndex, StartIndex, EndIndex, sort, order, where, out totalcount);
string JsonStr = FunCommon.DataTableToJson2(totalcount, resTable);
context.Response.ContentType = "text/plain";
context.Response.Write(JsonStr);
context.Response.End();
}
if (method == "Export")
{
string json = "";
string fileName = HttpContext.Current.Request.MapPath("~/App_Data/FTT数据查询.xlsx");
try
{
string err = "";
StartIndex = 1;
EndIndex = -1;
totalcount = 0;
resTable = FTT_BLL.getTableExcel(PageSize, PageIndex, StartIndex, EndIndex, sort, order, where, out totalcount);
// ExcelHelper.ExportDTtoExcel(resTable, "生产线报警趋势报表", fileName);
AsposeExcelTools.DataTableToExcel2(resTable, fileName, out err);
string ss = "true";
if (err.Length < 1)
{
ss = "true";
}
else
{
ss = "false";
}
json = "{\"Result\":\"" + ss + "\"}";
}
catch (Exception e)
{
string ss1 = "false";
json = "{\"Result\":\"" + ss1 + "\"}";
}
context.Response.ContentType = "json";
context.Response.Write(json);
}
}
public void ProcessRequest(HttpContext context)
{
HttpRequest request = System.Web.HttpContext.Current.Request;
string OrderType = request["OrderType"];
string SerialNumber = request["SerialNumber"];
string start_time = context.Request["StartTime"];
string end_time = context.Request["EndTime"];
int SortFlag = Convert.ToInt32(request["SortFlag"]);
int PageSize = Convert.ToInt32(request["rows"]);
int PageIndex = Convert.ToInt32(request["page"]);
string method = request["method"];
sort = request["sort"];
order = request["order"];
string where = "";
if (string.IsNullOrEmpty(start_time))
{
DateTime t = DateTime.Now;
start_time = t.ToString("yyyy-MM-dd HH:mm:ss");
}
string StartTime = start_time.Substring(0, 17) + "00";
if (string.IsNullOrEmpty(end_time))
{
DateTime t = DateTime.Now;
end_time = t.ToString("yyyy-MM-dd HH:mm:ss");
}
string EndTime = end_time.Substring(0, 17) + "59";
where += " and a.CreateTime >='" + StartTime + "'";
where += " and a.CreateTime <='" + EndTime + "'";
if (!string.IsNullOrEmpty(OrderType))
{
where += " and a.OrderType = " + OrderType + " ";
}
if (!string.IsNullOrEmpty(SerialNumber))
{
where += " and a.SerialNumber like '%" + SerialNumber + "%' ";
}
string JsonStr = "";
if (string.IsNullOrWhiteSpace(method))
{
int StartIndex = PageSize * (PageIndex - 1) + 1;
int EndIndex = StartIndex + PageSize - 1;
int totalcount;
DataTable resTable = mg_CustomerOrderBLL.getTable(PageSize, PageIndex, StartIndex, EndIndex, sort, order, where, out totalcount);
// DataTable resTable1 = GetPagedTable(resTable, PageIndex, PageSize);
JsonStr = FunCommon.DataTableToJson2(totalcount, resTable);
context.Response.ContentType = "text/plain";
context.Response.Write(JsonStr);
context.Response.End();
}
if ("Export" == method)
{
try
{
int totalcount;
DataTable resTable = mg_CustomerOrderBLL.getTableExcel(sort, order, where, out totalcount);
//ExcelHelper.ExportDTtoExcel(resTable, "", HttpContext.Current.Request.MapPath("~/App_Data/客户订单报表.xlsx"));
string fileName = HttpContext.Current.Request.MapPath("~/App_Data/客户订单报表.xlsx");
string err = "";
AsposeExcelTools.DataTableToExcel2(resTable, fileName, out err);
string ss = "true";
if (err.Length < 1)
{
ss = "true";
}
else
{
ss = "false";
}
JsonStr = ss;
}
catch
{
JsonStr = "false";
}
context.Response.ContentType = "json";
context.Response.Write(JsonStr);
context.Response.End();
}
}