using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Web; using Dapper; namespace Warehouse.DAL.AppDal { public class ModuleInfoCheckDal { static DbUtility dbhelp = new DbUtility(System.Configuration.ConfigurationManager.ConnectionStrings["mesConn"].ToString(), DbProviderType.SqlServer); #region 组件信息查询 public DataTable ModuleInfoDT(string serial) { return dbhelp.ExecuteDataTable(SeachModuleSql(serial), null); } public string SeachModuleSql(string serial) { string sql = @" select case when a.serial_status = 'G' then '合格' when a.serial_status = 'H' then '扣留' when a.serial_status = 'P' then '待返' when a.serial_status = 'R' then '已返' when a.serial_status = 'S' then '报废' end descriptions ,c.OEMcustomer --,a.workorder ,a.exterior_grade,a.el_grade,a.final_grade from [mes_main].[dbo].[assembly_status] a left join [mes_main].[dbo].[df_serial_status] b on a.serial_status = b.serial_status left join [mes_main].[dbo].[wo_mfg] c on a.workorder = c.workorder where serial_nbr = '" + serial +"'"; return sql; } #endregion #region 组件详细信息查询 public DataTable ModuleInfoTwoDT(string serial) { return dbhelp.ExecuteDataTable(SeachModuleTwoSql(serial), null); } public string SeachModuleTwoSql(string serial) { string sql = @" select a.serial_nbr 组件序列号,c.descriptions 车间, b.sale_order 订单号,b.workorder 工单号 ,ISNULL(a.pallet_nbr,'') 托盘号 ,case when a.serial_status = 'G' then '合格' when a.serial_status = 'H' then '扣留' when a.serial_status = 'P' then '待返' when a.serial_status = 'R' then '已返' when a.serial_status = 'S' then '报废' end 组件状态 ,d.descriptions 当前站点 ,a.exterior_grade 组件等级 ,a.el_grade EL等级 ,a.final_grade 最终等级 FROM [mes_main].[dbo].[assembly_status] a left join mes_main.dbo.wo_mfg b on a.workorder = b.workorder left join mes_main.dbo.df_areas c on b.area_code = c.area_code left join mes_main.dbo.df_processes d on a.process_code = d.process_code where serial_nbr = '" + serial + "'"; return sql; } #endregion #region 生码表更新组件状态hold public DataTable HoldsDT(string serial) { return dbhelp.ExecuteDataTable(HoldsSql(serial), null); } public string HoldsSql(string serial) { string sql = @" update [mes_main].[dbo].[assembly_status] set serial_status = 'H' where serial_nbr = '" + serial + "'"; return sql; } #endregion #region qc表增加扣留记录 public DataTable HoldsQcDT(dynamic info) { return dbhelp.ExecuteDataTable(HoldsQcSql(info), null); } public string HoldsQcSql(dynamic info) { string sql = @" insert into [mes_main].[dbo].[qc_visit] values ('" + info.module + "','" + info.workorder + "','" + info.serial_status + "','" + info.create_time + "','" + info.visit_type + "','" + info.operators + "','" + info.wks_id + "')"; return sql; } #endregion #region 查询大不良 public DataTable searchBigBadDT() { return dbhelp.ExecuteDataTable(searchBigBadSql(), null); } public string searchBigBadSql() { string sql = @" select defect_group_id value,defect_group_desc text from [mes_main].[dbo].[config_defect_group] where active_flag = '1'"; return sql; } #endregion #region 查询大不良 public DataTable searchSmallBadDT(string BigBad) { return dbhelp.ExecuteDataTable(searchSmallBadSql(BigBad), null); } public string searchSmallBadSql(string BigBad) { string sql = @" SELECT [defect_type_id] value ,[defect_type_desc] text FROM [mes_main].[dbo].[config_defect_type] where active_flag = '1' and defect_group_id = '"+ BigBad +"'"; return sql; } #endregion #region 添加不良 public DataTable addBadnesssDT(dynamic defectInfo) { return dbhelp.ExecuteDataTable(addBadnesssSql(defectInfo), null); } public string addBadnesssSql(dynamic defectInfo) { string sql = @"insert into [mes_main].[dbo].[defects] values ('" + defectInfo.module + "','" + defectInfo.wks_id + "','" + defectInfo.create_time + "','" + defectInfo.defect_type_id + "','" + defectInfo.remark + "','" + defectInfo.position + "','" + defectInfo.accordstd + "','" + defectInfo.operators + "','" + defectInfo.process_code + "','" + defectInfo.process_code_desc + "','" + defectInfo.defect_cell_qty + "')"; return sql; } #endregion } }