| | |
|---|
| | | } |
|---|
| | | #endregion |
|---|
| | | |
|---|
| | | |
|---|
| | | #region[工序检验记录列表查询] |
|---|
| | | public static ToMessage StepCheckTableSearch(string wocode, string partcode, string partname, string partapec, string stepname, string standname, string checktype, string checkresult, int startNum, int endNum, string prop, string order) |
|---|
| | | { |
|---|
| | | var dynamicParams = new DynamicParameters(); |
|---|
| | | string search = ""; |
|---|
| | | try |
|---|
| | | { |
|---|
| | | if (wocode != "" && wocode != null) |
|---|
| | | { |
|---|
| | | search += "and A.wo_code like '%'+@wocode+'%' "; |
|---|
| | | dynamicParams.Add("@wocode", wocode); |
|---|
| | | } |
|---|
| | | if (partcode != "" && partcode != null) |
|---|
| | | { |
|---|
| | | search += "and A.partcode like '%'+@stanedname+'%' "; |
|---|
| | | dynamicParams.Add("@partcode", partcode); |
|---|
| | | } |
|---|
| | | if (partname != "" && partname != null) |
|---|
| | | { |
|---|
| | | search += "and B.partname like '%'+@partname+'%' "; |
|---|
| | | dynamicParams.Add("@partname", partname); |
|---|
| | | } |
|---|
| | | if (partapec != "" && partapec != null) |
|---|
| | | { |
|---|
| | | search += "and B.partspec like '%'+@partapec+'%' "; |
|---|
| | | dynamicParams.Add("@partapec", partapec); |
|---|
| | | } |
|---|
| | | if (stepname != "" && stepname != null) |
|---|
| | | { |
|---|
| | | search += "and S.stepname like '%'+@stepname+'%' "; |
|---|
| | | dynamicParams.Add("@stepname", stepname); |
|---|
| | | } |
|---|
| | | if (standname != "" && standname != null) |
|---|
| | | { |
|---|
| | | search += "and T.name like '%'+@standname+'%' "; |
|---|
| | | dynamicParams.Add("@standname", standname); |
|---|
| | | } |
|---|
| | | if (checktype != "" && checktype != null) |
|---|
| | | { |
|---|
| | | search += "and A.check_type=@checktype "; |
|---|
| | | dynamicParams.Add("@checktype", checktype); |
|---|
| | | } |
|---|
| | | if (checkresult != "" && checkresult != null) |
|---|
| | | { |
|---|
| | | search += "and A.check_result=@checkresult "; |
|---|
| | | dynamicParams.Add("@checkresult", checkresult); |
|---|
| | | } |
|---|
| | | if (search == "") |
|---|
| | | { |
|---|
| | | search = "and 1=1 "; |
|---|
| | | } |
|---|
| | | search = search.Substring(3);//截取索引2后面的字符 |
|---|
| | | // --------------查询指定数据-------------- |
|---|
| | | var total = 0; //总条数 |
|---|
| | | var sql = @"select A.wo_code,A.partcode,B.partname,B.partspec,A.step_code,S.stepname,A.checkstaned_code,T.name as checkstaned_name,A.check_user, |
|---|
| | | A.check_type,A.check_result,A.check_descr,A.check_qty,U.username as lm_user,A.lm_date |
|---|
| | | from TStepCheckRecord A |
|---|
| | | left join TMateriel_Info B on A.partcode=B.partcode |
|---|
| | | left join TStep S on A.step_code=S.stepcode |
|---|
| | | left join TStepCheckStandard T on A.checkstaned_code=T.code |
|---|
| | | left join TUser U on A.lm_user=U.usercode |
|---|
| | | where " + search; |
|---|
| | | var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total); |
|---|
| | | mes.code = "200"; |
|---|
| | | mes.Message = "查询成功!"; |
|---|
| | | mes.count = total; |
|---|
| | | mes.data = data.ToList(); |
|---|
| | | } |
|---|
| | | catch (Exception e) |
|---|
| | | { |
|---|
| | | mes.code = "300"; |
|---|
| | | mes.count = 0; |
|---|
| | | mes.Message = e.Message; |
|---|
| | | mes.data = null; |
|---|
| | | } |
|---|
| | | return mes; |
|---|
| | | } |
|---|
| | | #endregion |
|---|
| | | |
|---|
| | | #region[工序检验记录列表明细查询] |
|---|
| | | public static ToMessage StepCheckTableSubSearch(string id) |
|---|
| | | { |
|---|
| | | string sql = ""; |
|---|
| | | var dynamicParams = new DynamicParameters(); |
|---|
| | | try |
|---|
| | | { |
|---|
| | | sql = @"select A.checkitem_seq,A.checkitem_code,A.checkitem_name,A.checkitem_descr,A.check_result,U.username as lm_user,A.lm_date |
|---|
| | | from TStepCheckRecordSub A |
|---|
| | | left join TUser U on A.lm_user=U.usercode |
|---|
| | | where A.m_id=@id"; |
|---|
| | | dynamicParams.Add("@id", id); |
|---|
| | | var data = DapperHelper.selectdata(sql, dynamicParams); |
|---|
| | | |
|---|
| | | mes.code = "200"; |
|---|
| | | mes.Message = "查询成功!"; |
|---|
| | | mes.data = data; |
|---|
| | | } |
|---|
| | | catch (Exception e) |
|---|
| | | { |
|---|
| | | mes.code = "300"; |
|---|
| | | mes.count = 0; |
|---|
| | | mes.Message = e.Message; |
|---|
| | | mes.data = null; |
|---|
| | | } |
|---|
| | | return mes; |
|---|
| | | } |
|---|
| | | #endregion |
|---|
| | | |
|---|
| | | #region[工序检验记录导出] |
|---|
| | | public static ToMessage StepCheckTableOutExcel(string wocode, string partcode, string partname, string partapec, string stepname, string standname, string checktype, string checkresult, string prop, string order) |
|---|
| | | { |
|---|
| | | var dynamicParams = new DynamicParameters(); |
|---|
| | | string search = ""; |
|---|
| | | try |
|---|
| | | { |
|---|
| | | if (wocode != "" && wocode != null) |
|---|
| | | { |
|---|
| | | search += "and A.wo_code like '%'+@wocode+'%' "; |
|---|
| | | dynamicParams.Add("@wocode", wocode); |
|---|
| | | } |
|---|
| | | if (partcode != "" && partcode != null) |
|---|
| | | { |
|---|
| | | search += "and A.partcode like '%'+@stanedname+'%' "; |
|---|
| | | dynamicParams.Add("@partcode", partcode); |
|---|
| | | } |
|---|
| | | if (partname != "" && partname != null) |
|---|
| | | { |
|---|
| | | search += "and B.partname like '%'+@partname+'%' "; |
|---|
| | | dynamicParams.Add("@partname", partname); |
|---|
| | | } |
|---|
| | | if (partapec != "" && partapec != null) |
|---|
| | | { |
|---|
| | | search += "and B.partspec like '%'+@partapec+'%' "; |
|---|
| | | dynamicParams.Add("@partapec", partapec); |
|---|
| | | } |
|---|
| | | if (stepname != "" && stepname != null) |
|---|
| | | { |
|---|
| | | search += "and S.stepname like '%'+@stepname+'%' "; |
|---|
| | | dynamicParams.Add("@stepname", stepname); |
|---|
| | | } |
|---|
| | | if (standname != "" && standname != null) |
|---|
| | | { |
|---|
| | | search += "and T.name like '%'+@standname+'%' "; |
|---|
| | | dynamicParams.Add("@standname", standname); |
|---|
| | | } |
|---|
| | | if (checktype != "" && checktype != null) |
|---|
| | | { |
|---|
| | | search += "and A.check_type=@checktype "; |
|---|
| | | dynamicParams.Add("@checktype", checktype); |
|---|
| | | } |
|---|
| | | if (checkresult != "" && checkresult != null) |
|---|
| | | { |
|---|
| | | search += "and A.check_result=@checkresult "; |
|---|
| | | dynamicParams.Add("@checkresult", checkresult); |
|---|
| | | } |
|---|
| | | if (search == "") |
|---|
| | | { |
|---|
| | | search = "and 1=1 "; |
|---|
| | | } |
|---|
| | | search = search.Substring(3);//截取索引2后面的字符 |
|---|
| | | // --------------查询指定数据-------------- |
|---|
| | | var total = 0; //总条数 |
|---|
| | | var sql = @"select |
|---|
| | | CASE WHEN |
|---|
| | | ROW_NUMBER() OVER (PARTITION BY A.wo_code ORDER BY B.checkitem_code) = 1 THEN A.wo_code |
|---|
| | | ELSE ''END AS '工单号', |
|---|
| | | CASE WHEN |
|---|
| | | ROW_NUMBER() OVER (PARTITION BY A.partcode ORDER BY B.checkitem_code) = 1 THEN M.partcode |
|---|
| | | ELSE ''END AS '产品编码', |
|---|
| | | CASE WHEN |
|---|
| | | ROW_NUMBER() OVER (PARTITION BY A.partcode ORDER BY B.checkitem_code) = 1 THEN M.partname |
|---|
| | | ELSE ''END AS '产品名称', |
|---|
| | | CASE WHEN |
|---|
| | | ROW_NUMBER() OVER (PARTITION BY A.partcode ORDER BY B.checkitem_code) = 1 THEN M.partspec |
|---|
| | | ELSE ''END AS '规格型号', |
|---|
| | | CASE WHEN |
|---|
| | | ROW_NUMBER() OVER (PARTITION BY A.step_code ORDER BY B.checkitem_code) = 1 THEN S.stepname |
|---|
| | | ELSE ''END AS '工序名称', |
|---|
| | | CASE WHEN |
|---|
| | | ROW_NUMBER() OVER (PARTITION BY A.checkstaned_code ORDER BY B.checkitem_code) = 1 THEN A.checkstaned_code |
|---|
| | | ELSE ''END AS '标准编码', |
|---|
| | | CASE WHEN |
|---|
| | | ROW_NUMBER() OVER (PARTITION BY A.checkstaned_code ORDER BY B.checkitem_code) = 1 THEN T.name |
|---|
| | | ELSE ''END AS '标准名称', |
|---|
| | | CASE WHEN |
|---|
| | | ROW_NUMBER() OVER (PARTITION BY A.check_user ORDER BY B.checkitem_code) = 1 THEN U.username |
|---|
| | | ELSE ''END AS '检验人员', |
|---|
| | | CASE WHEN |
|---|
| | | ROW_NUMBER() OVER (PARTITION BY A.check_typename ORDER BY B.checkitem_code) = 1 THEN A.check_typename |
|---|
| | | ELSE ''END AS '检验类型', |
|---|
| | | CASE WHEN |
|---|
| | | ROW_NUMBER() OVER (PARTITION BY A.check_result ORDER BY B.checkitem_code) = 1 THEN A.check_result |
|---|
| | | ELSE ''END AS '检验结果', |
|---|
| | | CASE WHEN |
|---|
| | | ROW_NUMBER() OVER (PARTITION BY A.check_descr ORDER BY B.checkitem_code) = 1 THEN A.check_descr |
|---|
| | | ELSE ''END AS '检验描述', |
|---|
| | | CASE WHEN |
|---|
| | | ROW_NUMBER() OVER (PARTITION BY A.check_qty ORDER BY B.checkitem_code) = 1 THEN A.check_qty |
|---|
| | | END AS '检验数量', |
|---|
| | | CASE WHEN |
|---|
| | | ROW_NUMBER() OVER (PARTITION BY A.lm_date ORDER BY B.checkitem_code) = 1 THEN A.lm_date |
|---|
| | | END AS '检验时间', |
|---|
| | | B.checkitem_seq as '检验项目序号',B.checkitem_code '检验项目编码',B.checkitem_name '检验项目名称',B.checkitem_descr '检验项目描述',B.check_result '检验项目结果' |
|---|
| | | from TStepCheckRecord A |
|---|
| | | left join TStepCheckRecordSub B on A.id=B.m_id |
|---|
| | | left join TMateriel_Info M on A.partcode=M.partcode |
|---|
| | | left join TStep S on A.step_code=S.stepcode |
|---|
| | | left join TStepCheckStandard T on A.checkstaned_code=T.code |
|---|
| | | left join TUser U on A.check_user=U.usercode |
|---|
| | | where " + search; |
|---|
| | | DataTable data = DapperHelper.selectdata(sql, dynamicParams); |
|---|
| | | data.TableName ="Table"; //设置DataTable的名称 |
|---|
| | | string msg = DownLoad.DataTableToExcel(data, "工序检验记录"); |
|---|
| | | mes.code = "200"; |
|---|
| | | mes.Message = "查询成功!"; |
|---|
| | | mes.count = total; |
|---|
| | | mes.data = msg; |
|---|
| | | } |
|---|
| | | catch (Exception e) |
|---|
| | | { |
|---|
| | | mes.code = "300"; |
|---|
| | | mes.count = 0; |
|---|
| | | mes.Message = e.Message; |
|---|
| | | mes.data = null; |
|---|
| | | } |
|---|
| | | return mes; |
|---|
| | | } |
|---|
| | | #endregion |
|---|
| | | } |
|---|
| | | } |
|---|
