LKApi.git - Gitblit

			@@ -2515,6 +2515,420 @@
			mes.count = 0;
			mes.Message = e.Message;
			mes.data = null;
			}
			return mes;
			}
			#endregion



			#region[产能规划查询]
			public static ToMessage CapacityPlanningSearch(string workshop, string devicetype, string stustype, int startNum, int endNum, string prop, string order)
			{
			var dynamicParams = new DynamicParameters();
			string search = "";
			try
			{
			if (workshop != "" && workshop != null)
			{
			search += "and A.wksp_code=@workshop ";
			dynamicParams.Add("@workshop", workshop);
			}
			if (devicetype != "" && devicetype != null)
			{
			search += "and A.code=@devicetype ";
			dynamicParams.Add("@devicetype", devicetype);
			}
			if (stustype != "" && stustype != null)
			{
			search += "and B.enable=@stustype ";
			dynamicParams.Add("@stustype", stustype);
			}
			if (search == "")
			{
			search = "and 1=1 ";
			}
			// --------------查询指定数据--------------
			var total = 0; //总条数
			var sql = @"select
			B.id CaptPlanId,
			A.wksp_code CaptPlanWorkShopid,
			C.org_name CaptPlanWorkShop,
			A.code CaptPlanDeviceTypeid,
			A.name CaptPlanDeviceType,
			A.ClassType CaptPlanType,
			E.aa CaptPlanShopCalendar,
			E.RR CaptPlanShopMaxDate,
			E.TT CaptPlanShopCalendarList,
			B.wkshift_code CaptPlanWorkShiftCode,
			D.name CaptPlanWorkShift,
			D.duration CaptPlanDuration,
			B.lm_date CaptPlanDate,
			B.ENABLE CaptPlanStus,
			G.username CaptPlanUser
			FROM(
			select distinct B.wksp_code,A.code,A.name,'D' ClassType from TEqpType A
			left join TEqpInfo B on A.code=B.eqptype_code
			left join TFlw_Rteqp C on B.code=C.eqp_code
			left join TFlw_Rtdt D on C.step_code=D.step_code
			where D.first_choke='Y' --order by A.code
			)A
			left join TOrganization C on A.wksp_code=C.org_code
			left join TWkm_capac_plan B ON A.wksp_code=B.wkshop and A.code=B.eqp_typecode
			left join TBas_wkshift_info D on B.wkshift_code=D.code
			left join TUser G on B.lm_user=G.usercode
			left join (
			select m_id,
			min(CONVERT(varchar(100), wkdate, 23))+'~'+max(CONVERT(varchar(100), wkdate, 23)) aa,
			max(CONVERT(varchar(100), wkdate, 23))RR,
			STUFF((
			SELECT ',' + CONVERT(varchar(100),t1.wkdate, 23)
			FROM TWkm_capac_plan_sub t1
			WHERE t1.m_id = t0.m_id
			ORDER BY t1.m_id
			FOR XML PATH('')), 1, LEN(','), '') AS TT
			FROM TWkm_capac_plan_sub t0 where CONVERT(varchar(100), wkdate, 23)>=CONVERT(varchar(100),getdate(), 23)
			GROUP BY t0.m_id
			) E on B.id=E.m_id
			where C.description='W' ";
			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 CapacityPlanSubmit(string type, string captplanid, string wkshopcode, string capunitcode, string capsetupcode, string captplantype, string username)
			{
			var dynamicParams = new DynamicParameters();
			try
			{
			if (type == "Add")
			{
			var sql = @"insert into TWkm_capac_plan(wkshop, eqp_typecode, wkshift_code, enable, lm_user, lm_time,classtype)
			values(@wkshop,@eqp_typecode,@wkshift_code,@enable,@lm_user,@lm_time,@classtype)";
			dynamicParams.Add("@wkshop", wkshopcode);
			dynamicParams.Add("@eqp_typecode", capunitcode);
			dynamicParams.Add("@wkshift_code", capsetupcode);
			dynamicParams.Add("@enable", "Y");
			dynamicParams.Add("@lm_user", username);
			dynamicParams.Add("@lm_time", DateTime.Now.ToString());
			dynamicParams.Add("@classtype", captplantype);
			int cont = DapperHelper.SQL(sql, dynamicParams);
			if (cont > 0)
			{
			mes.code = "200";
			mes.count = 0;
			mes.Message = "新增操作成功!";
			mes.data = null;
			}
			else
			{
			mes.code = "300";
			mes.count = 0;
			mes.Message = "新增操作失败!";
			mes.data = null;
			}
			}
			if (type == "Update")
			{
			var sql = @"update TWkm_capac_plan set wkshift_code=@wkshift_code,lm_user=@username,lm_date=@CreateDate where id=@captplanid";
			dynamicParams.Add("@captplanid", captplanid);
			dynamicParams.Add("@wkshift_code", capsetupcode);
			dynamicParams.Add("@username", username);
			dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
			int cont = DapperHelper.SQL(sql, dynamicParams);
			if (cont > 0)
			{
			mes.code = "200";
			mes.count = 0;
			mes.Message = "修改操作成功!";
			mes.data = null;
			}
			else
			{
			mes.code = "300";
			mes.count = 0;
			mes.Message = "修改操作失败!";
			mes.data = null;
			}
			}
			}
			catch (Exception e)
			{
			mes.code = "300";
			mes.count = 0;
			mes.Message = e.Message;
			mes.data = null;
			}
			return mes;
			}
			#endregion

			#region[产能规划点击日历组件查找已关联的日期方案]
			public static ToMessage CapacityPlanningCalendar(string captplanid)
			{
			string sql = "";
			var dynamicParams = new DynamicParameters();
			List<CapaPlan> list = new List<CapaPlan>();
			try
			{
			sql = @"select CONVERT(varchar(100), wkdate, 23) DataTime,wkshift_code from TWkm_capac_plan_sub where m_id=@captplanid";
			dynamicParams.Add("@captplanid", captplanid);
			var data = DapperHelper.selectdata(sql, dynamicParams);
			for (int i = 0; i < dt.Rows.Count; i++)
			{
			string DataTime = dt.Rows[i]["DataTime"].ToString();//日期
			string key = dt.Rows[i]["wkshift_code"].ToString(); //方案编码
			CapaPlan cmp = new CapaPlan();
			cmp.name = DataTime;
			cmp.key = key;
			list.Add(cmp);
			}
			mes.code = "200";
			mes.Message = "查询成功!";
			mes.data = list;
			}
			catch (Exception e)
			{
			mes.code = "300";
			mes.count = 0;
			mes.Message = e.Message;
			mes.data = null;
			}
			return mes;
			}
			#endregion

			#region[产能规划日历双击时带出对应的方案]
			public static ToMessage CapacityPlanningOnclickSelect(string captplanid, string datetime)
			{
			string sql = "";
			var dynamicParams = new DynamicParameters();
			List<CapacityPlanSect> list = new List<CapacityPlanSect>();
			try
			{
			sql = @"select distinct AA.code,AA.name,(case when AA.Stus is null then 'N' else 'Y' end) flag
			from
			(
			select A.code,A.name,B.wkshift_code Stus from TBas_wkshift_info A
			left join TWkm_capac_plan_sub B on A.code= B.wkshift_code and B.m_id=@captplanid and CONVERT(varchar(100), wkdate, 23)=@datetime
			) AA order by code";
			dynamicParams.Add("@captplanid", captplanid);
			dynamicParams.Add("@datetime", datetime);
			var data = DapperHelper.selectdata(sql, dynamicParams);
			for (int i = 0; i < dt.Rows.Count; i++)
			{
			string code = dt.Rows[i]["code"].ToString();//方案编码
			string name = dt.Rows[i]["name"].ToString();//方案名称
			string flag = dt.Rows[i]["flag"].ToString(); //选中方案标识
			CapacityPlanSect cmp = new CapacityPlanSect();
			cmp.CapCode = code;
			cmp.CapName = name;
			cmp.CapStus = flag;
			cmp.list = new List<object>();

			string sql1 = @"select wktme1_start,wktme2_start,wktme3_start,wktme4_start,wktme5_start from TBas_wkshift_info where code=@code";
			dynamicParams.Add("@code", code);
			var db = DapperHelper.selectdata(sql, dynamicParams);

			for (int j = 0; j < db.Columns.Count; j++) //时间段
			{
			if (db.Rows[0][j].ToString().Trim() != null && db.Rows[0][j].ToString().Trim() != "")
			{
			cmp.list.Add(db.Rows[0][j].ToString());
			}
			}
			list.Add(cmp);
			}
			mes.code = "200";
			mes.Message = "查询成功!";
			mes.data = list;
			}
			catch (Exception e)
			{
			mes.code = "300";
			mes.count = 0;
			mes.Message = e.Message;
			mes.data = null;
			}
			return mes;
			}
			#endregion

			#region[产能规划工作日历选择提交]
			public static ToMessage CapacityPlanningGivePlanSubmit(string captplanid, string wkshopcode, string capunitcode, string capsetupcode, string captplantype, List<CapaPlan> objs, string type, string username)
			{
			string sql = "";
			List<object> list = new List<object>();
			var dynamicParams = new DynamicParameters();
			try
			{
			list.Clear();
			if (capsetupcode == "" \|\| capsetupcode == null)
			{
			mes.code = "300";
			mes.count = 0;
			mes.Message = "默认方案选择不能为空!";
			mes.data = null;
			return mes;
			}
			if (objs.Count <= 0)
			{
			mes.code = "300";
			mes.count = 0;
			mes.Message = "工作日历选择不能为空!";
			mes.data = null;
			return mes;
			}
			if (type == "Add")
			{
			for (int i = 0; i < objs.Count; i++)
			{
			sql = @"insert into TWkm_capac_plan_sub(m_id, wkdate,wkshift_code)
			values(@m_id,@wkdate,@wkshift_code)";
			list.Add(new
			{
			str = sql,
			parm = new
			{
			m_id = captplanid,
			wkdate = objs[i].name,
			wkshift_code = objs[i].key
			}
			});
			}
			bool aa = DapperHelper.DoTransaction(list);
			if (aa)
			{
			mes.code = "200";
			mes.count = 0;
			mes.Message = "操作成功!";
			mes.data = null;
			}
			else
			{
			mes.code = "200";
			mes.count = 0;
			mes.Message = "操作失败!";
			mes.data = null;
			}

			}
			if (type == "Update")
			{
			//定义两个数组用来对比修改前后日期及方案的变化
			List<CapaPlan> ids = new List<CapaPlan>();
			List<CapaPlan> ids1 = new List<CapaPlan>();
			for (int i = 0; i < objs.Count; i++)
			{
			ids.Add(objs[i]);
			}

			//根据id查找日历时间
			string sql2 = @"select CONVERT(varchar(100), wkdate, 23) wkdate,wkshift_code from TWkm_capac_plan_sub where m_id=@m_id";
			dynamicParams.Add("@m_id", captplanid);
			var dt2 = DapperHelper.selectdata(sql2, dynamicParams);
			if (dt2.Rows.Count > 0)
			{
			for (int k = 0; k < dt2.Rows.Count; k++)
			{
			CapaPlan ids2 = new CapaPlan();
			ids2.name = dt2.Rows[k]["wkdate"].ToString();
			ids2.key = dt2.Rows[k]["wkshift_code"].ToString();
			ids1.Add(ids2);
			}
			}

			List<CapaPlan> list3 = ids.Except(ids1).ToList(); //表示ids中哪些值是ids1中所不存在的;
			List<CapaPlan> list4 = ids1.Except(ids).ToList(); //表示ids1中哪些值是ids中所不存在的;
			list4 = list3.Union(list4).ToList();
			//查找关键工序设备、工位群组的所有设备
			string sql3 = @"select * from(
			select distinct A.code,A.name from TEqpInfo A
			left join TFlw_Rteqp B on A.code=B.eqp_code
			left join TFlw_Rtdt D on B.step_code=D.step_code
			where A.wksp_code=@wkshopcode and D.first_choke='Y' order by A.code)";
			dynamicParams.Add("@wkshopcode", wkshopcode);
			var dt3 = DapperHelper.selectdata(sql3, dynamicParams);
			if (list4.Count > 0) //如果日期有变动
			{
			if (dt3.Rows.Count > 0)
			{
			for (int m = 0; m < list4.Count; m++)
			{
			for (int n = 0; n < dt3.Rows.Count; n++)
			{
			string sql4 = @"select A.wo_code,A.eqp_code,A.Time_Start,A.time_end,
			(case B.Status when 'SCHED' then '已排程' when 'ALLOC' then '已派发' when 'START' then '已开工' end ) status
			from TK_Wrk_EqpAps A
			left join mes_tk_wrk_man B on A.wo_code=B.Wo_Code
			left join gn_eqp_eqpinfo C on A.eqp_code=C.code
			where TO_char(time_start,'YYYY-MM-DD')='" + list4[m].name + "' and A.eqp_code='" + dt3.Rows[n]["CODE"] + "' and B.isaps='Y' and B.status IN('SCHED','ALLOC','START')";
			DataTable dt4 = DBHelper.GetTable(sql4);
			if (dt4.Rows.Count > 0)
			{
			for (int g = 0; g < dt4.Rows.Count; g++)
			{
			json = new DWZJson()
			{
			statusCode = "300",
			message = "修改失败！,日期：" + dt4.Rows[g]["TIME_START"] + "有【" + dt4.Rows[g]["STATUS"] + "】工单：" + dt4.Rows[g]["WO_CODE"] + "",
			};
			return json;
			}
			}
			}
			}
			}
			}


			list.Add(@"update gn_wkm_capac_plan set wkshift_code='" + CapSetupCode + "',classtype='" + CaptPlanType + "', enable='Y',lm_user='" + lm_user + "',lm_time=sysdate where id='" + CaptPlanId + "'");
			list.Add(@"delete gn_wkm_capac_plan_sub where m_id='" + CaptPlanId + "'");
			for (int i = 0; i < objs.Count; i++)
			{
			list.Add(@"insert into gn_wkm_capac_plan_sub(m_id, id, wkdate,wkshift_code)
			values('" + CaptPlanId + "',gn_wkm_capac_plan_sub_S.nextVal,to_date('" + objs[i].name + "','yyyy-MM-dd'),'" + objs[i].key + "')");
			}
			int count = DBHelper.Executesqltran(list, pt.ToArray());
			if (count > 0)
			{
			json = new DWZJson()
			{
			statusCode = "200",
			message = "修改成功！",
			};
			}
			else
			{
			json = new DWZJson()
			{
			statusCode = "300",
			message = "修改失败！",
			};
			}
			}
			}
			catch (Exception e)
			{

			json = new DWZJson()
			{
			statusCode = "300",
			message = e.Message,
			};
			}
			return mes;
			}