/// <summary> /// 重新填充Base_Bom_View 表信息 /// </summary> /// <returns></returns> public bool ReSetView() { StringBuilder strSql = new StringBuilder(); strSql.Append(@"truncate table Base_Bom_View; with t as (select a.ParentGoodNo ,a.SonGoodNo ,a.BiLi,a.ParentGoodName,a.SonGoodName from Base_Bom a where not exists(select 1 from Base_Bom b where b.SonGoodNo=a.ParentGoodNo) union all select d.ParentGoodNo,c.SonGoodNo,c.BiLi*d.BiLi,d.ParentGoodName,c.SonGoodName from Base_Bom c inner join t d on c.ParentGoodNo=d.SonGoodNo ) insert into Base_Bom_View select ParentGoodNo ,SonGoodNo ,BiLi,ParentGoodName,SonGoodName from ( select a.ParentGoodNo,a.SonGoodNo,a.BiLi, case when not exists(select 1 from Base_Bom b where b.ParentGoodNo=a.SonGoodNo) then '底层' else '' end as 'BiaoShi',a.ParentGoodName,a.SonGoodName from t a ) as TopBom where BiaoShi='' union select a.ParentGoodNo ,a.SonGoodNo ,a.BiLi,a.ParentGoodName,a.SonGoodName from Base_Bom a"); var parameters = WriteAdoTemplate.CreateDbParameters(); return(ReadAdoTemplate.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters) > 0); }
/// <summary> /// 获取信息列表 /// </summary> /// <param name="query"></param> /// <returns></returns> public override IList <Pro_Scheduling> GetAllDomain(QueryCondition query) { var parameters = WriteAdoTemplate.CreateDbParameters(); StringBuilder strSql = new StringBuilder(); strSql.Append("select "); strSql.Append(" Id,ProNo,ShipMainProNo,PlanFromDate,PlanToDate,CreateTime,Creater,UpdateTime,Updater,RowState "); strSql.Append(" from Pro_Scheduling "); string otherWhere = ""; if (query.GetCondition("PlanFromDate") != null) { } if (query.GetPager() != null) { strSql = new StringBuilder(GetPagerSql(strSql.ToString(), query, parameters, otherWhere)); } else { strSql.Append(query.GetSQL_Where(parameters)); strSql.Append(query.GetSQL_Order()); } return(ReadAdoTemplate.QueryWithRowMapperDelegate <Pro_Scheduling>(CommandType.Text, strSql.ToString(), MapRow, parameters)); }
/// <summary> /// 根据产线 和 商品获取对应的产能信息 /// </summary> /// <param name="query"></param> /// <returns></returns> public IList <Base_ProductionLines> GetAllDomainByLineNoAndGoodNos(QueryCondition query) { var parameters = WriteAdoTemplate.CreateDbParameters(); StringBuilder strSql = new StringBuilder(); //子产品 // strSql.Append("select distinct Base_ProductionLine.GoodNo,People,Number from[Base_ProductionLines]"); // strSql.Append("left join [Base_ProductionLine] on Base_ProductionLine.ID=[Base_ProductionLines].ProLineId"); // strSql.AppendFormat(@" left join (select SonGoodNo as GoodNo,SonGoodName as GoodName from // Pro_ShipPlan //left join Pro_ShipPlanMain on Pro_ShipPlanMain.ID = Pro_ShipPlan.MainId //inner join [dbo].[Base_Bom_View] on GoodNo=Base_Bom_View.ParentGoodNo //where Pro_ShipPlanMain.ProNo='{0}') as TB ON TB.GoodNo=Base_ProductionLine.GoodNo", query.GetCondition("ShipMainProNo").Value); // strSql.AppendFormat(" where ProLineNo='{0}'", query.GetCondition("LineNo").Value); // strSql.AppendFormat(" and People in({0})", query.GetCondition("People").Value); // strSql.Append(" union all "); //成品 strSql.Append("select distinct Base_ProductionLine.GoodNo,People,Number from[Base_ProductionLines]"); strSql.Append("left join [Base_ProductionLine] on Base_ProductionLine.ID=[Base_ProductionLines].ProLineId"); strSql.Append(@" left join Pro_ShipPlan on Pro_ShipPlan.GoodNo=Base_ProductionLine.GoodNo left join Pro_ShipPlanMain on Pro_ShipPlanMain.ID = Pro_ShipPlan.MainId"); strSql.AppendFormat(" where ProLineNo='{0}'", query.GetCondition("LineNo").Value); strSql.AppendFormat(" and Pro_ShipPlanMain.ProNo='{0}'", query.GetCondition("ShipMainProNo").Value); strSql.AppendFormat(" and People in({0})", query.GetCondition("People").Value); return(ReadAdoTemplate.QueryWithRowMapperDelegate <Base_ProductionLines>(CommandType.Text, strSql.ToString(), MapRowByLineNoAndGoodNos, parameters)); }
/// <summary> /// 获取信息列表 导出到Excel /// </summary> /// <param name="query"></param> /// <returns></returns> public IList <Pro_PSDetail> GetAllDomainToExcel(QueryCondition query, Hashtable hs) { var parameters = WriteAdoTemplate.CreateDbParameters(); StringBuilder strSql = new StringBuilder(); strSql.Append(@"select ProLineNo, ProDate,GoodNo,GoodName,ShipTo,ShipToName,Qty,SType,StartTime,EndTime FROM Pro_PSDetail left join Pro_PS on Pro_PS.Id = Pro_PSDetail.[MainId] where 1=1 "); if (query.GetCondition("ProNo") != null) { strSql.AppendFormat(" and ProNo like '%{0}%'", query.GetCondition("ProNo").Value); } if (query.GetCondition("ProLineNo") != null) { strSql.AppendFormat(" and ProLineNo like '%{0}%'", query.GetCondition("ProLineNo").Value); } if (query.GetCondition("ProDate") != null) { strSql.AppendFormat(" and ProDate >='{0}'", query.GetCondition("ProDate").Value); } if (hs.ContainsKey("ProDateTo")) { strSql.AppendFormat(" and ProDate <='{0}'", hs["ProDateTo"]); } if (query.GetCondition("SchedulingProNo") != null) { strSql.AppendFormat(" and SchedulingProNo like '%{0}%'", query.GetCondition("SchedulingProNo").Value); } strSql.Append(" order by ProDate,[ProOrderIndex] "); return(ReadAdoTemplate.QueryWithRowMapperDelegate <Pro_PSDetail>(CommandType.Text, strSql.ToString(), MapRowToExcel, parameters)); }
/// <summary> /// 微信 /// 新增 修改 基本参数 /// </summary> /// <param name="entity"></param> /// <returns></returns> private IDbParameters GetBaseParams(WeChatDetailDomain entity) { var parameters = WriteAdoTemplate.CreateDbParameters(); parameters.Add("AppId", SqlDbType.NVarChar, 50).Value = entity.AppId ?? ""; parameters.Add("SceneryName", SqlDbType.NVarChar, 50).Value = entity.SceneryName ?? ""; parameters.Add("BatchNumber", SqlDbType.NVarChar, 50).Value = entity.BatchNumber ?? ""; parameters.Add("SerialId", SqlDbType.NVarChar, 50).Value = entity.SerialId ?? ""; parameters.Add("Result_code", SqlDbType.NVarChar).Value = entity.Result_code ?? ""; parameters.Add("Err_code", SqlDbType.NVarChar).Value = entity.Err_code ?? ""; parameters.Add("Err_code_des", SqlDbType.NVarChar).Value = entity.Err_code_des ?? ""; parameters.Add("Openid", SqlDbType.NVarChar).Value = entity.Openid ?? ""; parameters.Add("Trade_type", SqlDbType.NVarChar).Value = entity.Trade_type ?? ""; parameters.Add("Bank_type", SqlDbType.NVarChar).Value = entity.Bank_type ?? ""; parameters.Add("Total_fee", SqlDbType.Int).Value = Convert.ToInt32(entity.Total_fee); parameters.Add("Settlement_total_fee", SqlDbType.Int).Value = entity.Settlement_total_fee; parameters.Add("Transaction_id", SqlDbType.NVarChar).Value = entity.Transaction_id ?? ""; parameters.Add("Out_trade_no", SqlDbType.NVarChar).Value = entity.Out_trade_no ?? ""; parameters.Add("Time_end", SqlDbType.DateTime).Value = entity.Time_end; parameters.Add("SceneryRate", SqlDbType.Decimal).Value = entity.SceneryRate; parameters.Add("WeChatPlayDate", SqlDbType.DateTime).Value = entity.WeChatPlayDate; parameters.Add("Out_refund_no", SqlDbType.NVarChar).Value = entity.Out_refund_no ?? ""; parameters.Add("Refund_id", SqlDbType.NVarChar).Value = entity.Refund_id ?? ""; parameters.Add("CreateTime", SqlDbType.DateTime).Value = DateTime.Now; parameters.Add("Creater", SqlDbType.NVarChar, 50).Value = entity.Creater ?? ""; parameters.Add("UpdateTime", SqlDbType.DateTime).Value = DateTime.Now; parameters.Add("Updater", SqlDbType.NVarChar, 50).Value = entity.Updater ?? ""; parameters.Add("RowState", SqlDbType.TinyInt, 1).Value = 1; return(parameters); }
/// <summary> /// 微信 在交易表中查询是否已经退款过 /// </summary> /// <param name="BatchNumber"></param> /// <param name="SerialId"></param> /// <param name="Total_amount"></param> /// <returns></returns> public bool IsExistsSceneryOrderRefund(string BatchNumber, string SerialId, int Total_fee) { string sql = string.Format("select count(*) from WeChatDetail with (nolock) where Total_fee={0} and BatchNumber='{1}' and SerialId='{2}'", Total_fee, BatchNumber, SerialId); return(Convert.ToInt32(WriteAdoTemplate.ExecuteScalar(CommandType.Text, sql)) > 0 ? true : false); }
/// <summary> /// 获取信息列表 /// </summary> /// <param name="query"></param> /// <returns></returns> public IList <SceneryOrderRefundDomain> GetAllDomain(QueryCondition query, Hashtable hsWhere) { query.AddEqual("RowState", "1").AddOrderBy("Id", false); var parameters = WriteAdoTemplate.CreateDbParameters(); StringBuilder strSql = new StringBuilder(); StringBuilder otherWhere = new StringBuilder(); if (hsWhere.ContainsKey("ApprovalStatus")) { otherWhere.AppendFormat(" and ApprovalStatus in (0,1)"); } strSql.Append("select Id,SceneryTCId,SceneryName,BatchNumber,SerialId,PlayDate,Total,ApprovalStatus,PayType,CreateTime,Creater,UpdateTime,Updater,RowState from SceneryOrderRefund "); if (query.GetPager() != null) { string sumSql = "sum(Total) as SumTotal"; strSql = new StringBuilder(GetPagerSql(strSql.ToString(), query, parameters, otherWhere.ToString(), sumSql)); } else { strSql.Append(query.GetSQL_Where(parameters)); strSql.Append(otherWhere); strSql.Append(query.GetSQL_Order()); } return(ReadAdoTemplate.QueryWithRowMapperDelegate <SceneryOrderRefundDomain>(CommandType.Text, strSql.ToString(), MapRow, parameters)); }
/// <summary> /// 新增 修改 基本参数 /// </summary> /// <param name="entity"></param> /// <returns></returns> private IDbParameters GetBaseParams(AlipayDetailDomain entity) { var parameters = WriteAdoTemplate.CreateDbParameters(); parameters.Add("BatchNumber", SqlDbType.NVarChar, 50).Value = entity.BatchNumber ?? ""; parameters.Add("SerialId", SqlDbType.NVarChar, 50).Value = entity.SerialId ?? ""; parameters.Add("SceneryRate", SqlDbType.Decimal).Value = entity.SceneryRate; parameters.Add("AlipayPlayDate", SqlDbType.DateTime).Value = entity.AlipayPlayDate; parameters.Add("AppId", SqlDbType.NVarChar, 50).Value = entity.AppId ?? ""; parameters.Add("SceneryName", SqlDbType.NVarChar, 50).Value = entity.SceneryName ?? ""; parameters.Add("Buyer_logon_id", SqlDbType.NVarChar, 100).Value = entity.Buyer_logon_id ?? ""; parameters.Add("Out_trade_no", SqlDbType.NVarChar, 50).Value = entity.Out_trade_no ?? ""; parameters.Add("Send_pay_date", SqlDbType.DateTime).Value = entity.Send_pay_date; parameters.Add("Total_amount", SqlDbType.Decimal, 9).Value = entity.Total_amount; parameters.Add("Trade_no", SqlDbType.NVarChar, 100).Value = entity.Trade_no ?? ""; parameters.Add("Trade_status", SqlDbType.NVarChar, 100).Value = entity.Trade_status ?? ""; parameters.Add("Code", SqlDbType.NVarChar, 50).Value = entity.Code ?? ""; parameters.Add("Msg", SqlDbType.NVarChar, 50).Value = entity.Msg ?? ""; parameters.Add("CreateTime", SqlDbType.DateTime).Value = DateTime.Now; parameters.Add("Creater", SqlDbType.NVarChar, 50).Value = entity.Creater ?? ""; parameters.Add("UpdateTime", SqlDbType.DateTime).Value = DateTime.Now; parameters.Add("Updater", SqlDbType.NVarChar, 50).Value = entity.Updater ?? ""; parameters.Add("RowState", SqlDbType.TinyInt, 1).Value = 1; return(parameters); }
/// <summary> /// 获取信息列表 /// </summary> /// <param name="query"></param> /// <returns></returns> public IList <string> GetGoodName(QueryCondition query) { var parameters = WriteAdoTemplate.CreateDbParameters(); StringBuilder strSql = new StringBuilder(); strSql.Append("select GoodNo+','+GoodName as good from Base_Goods where RowState=1 group by GoodNo,GoodName"); return(ReadAdoTemplate.QueryWithRowMapperDelegate <string>(CommandType.Text, strSql.ToString(), MapRowToString, parameters)); }
/// <summary> /// 新增 修改 基本参数 /// </summary> /// <param name="entity"></param> /// <returns></returns> private IDbParameters GetBaseParams(MenuDomain entity) { var parameters = WriteAdoTemplate.CreateDbParameters(); parameters.Add("@DisplayName", SqlDbType.NVarChar, 50).Value = entity.DisplayName; parameters.Add("@MenuIndex", SqlDbType.Int, 4).Value = entity.MenuIndex; return(parameters); }
/// <summary> /// 获取信息列表 /// </summary> /// <param name="query"></param> /// <returns></returns> public List <Base_Goods> GetBomName(string parentNo, string sonNo) { var parameters = WriteAdoTemplate.CreateDbParameters(); StringBuilder strSql = new StringBuilder(); strSql.AppendFormat("select GoodNo,GoodName from Base_Goods where RowState=1 and GoodNo in ('{0}','{1}') group by GoodNo,GoodName ", parentNo, sonNo); return(ReadAdoTemplate.QueryWithRowMapperDelegate(CommandType.Text, strSql.ToString(), MapRowBomName, parameters).ToList()); }
/// <summary> /// 删除 /// </summary> /// <param name="Id"></param> /// <returns></returns> public override bool Delete(int Id) { StringBuilder strSql = new StringBuilder(); strSql.Append("delete from Base_Stock "); strSql.Append(" where ID=@ID "); var parameters = WriteAdoTemplate.CreateDbParameters(); parameters.Add("ID", DbType.Int32, 0).Value = Id; return ReadAdoTemplate.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters) > 0; }
/// <summary> /// 微信 /// 景区信息是否存在支付记录 /// </summary> /// <param name="SceneryName">景区名称</param> /// <returns></returns> public bool IsExistsSceneryWeChat(string SceneryName) { var parameters = WriteAdoTemplate.CreateDbParameters(); parameters.Add("SceneryName", SqlDbType.NVarChar, 50).Value = SceneryName; string sql = string.Format("select count(*) from WeChatDetail with (nolock) where SceneryName=@SceneryName "); return(Convert.ToInt32(WriteAdoTemplate.ExecuteScalar(CommandType.Text, sql, parameters)) > 0 ? true : false); }
/// <summary> /// 修改时 使用 /// </summary> /// <param name="Id"></param> /// <returns></returns> public IList <Pro_ShipPlan> GetPro_SchedulingByEdit(int Id) { var parameters = WriteAdoTemplate.CreateDbParameters(); string sql = string.Format(@"select Pro_SchedulingGoods.Id,ProLineNo,Pro_SchedulingGoods.GoodNo,Pro_SchedulingGoods.GoodName,Pro_SchedulingGoods.ShipTo,Pro_SchedulingGoods.ShipToName,StockNum,PackNum,MorningNum,MiddleNum,EveningNum,ParentGoodNo,ParentGoodName,StandardDays from Pro_SchedulingLine left join Pro_SchedulingGoods on Pro_SchedulingLine.Id=Pro_SchedulingGoods.SLineId left join Base_Goods on Base_Goods.GoodNo=Pro_SchedulingGoods.GoodNo and Base_Goods.ShipTo=Pro_SchedulingGoods.ShipTo where [MainId]={0}", Id); return(ReadAdoTemplate.QueryWithRowMapperDelegate <Pro_ShipPlan>(CommandType.Text, sql, MapPro_SchedulingByEdit, parameters)); }
/// <summary> /// 删除-所有信息 /// </summary> /// <param name="Id"></param> /// <returns></returns> public bool DeleteAll(int Id) { StringBuilder strSql = new StringBuilder(); strSql.Append("truncate table [dbo].[Base_LineHour] "); var parameters = WriteAdoTemplate.CreateDbParameters(); parameters.Add("ID", DbType.Int32, 0).Value = Id; return(ReadAdoTemplate.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters) > 0); }
/// <summary> /// 新增 修改 基本参数 /// </summary> /// <param name="entity"></param> /// <returns></returns> private IDbParameters GetBaseParams(FormDomain entity) { var parameters = WriteAdoTemplate.CreateDbParameters(); parameters.Add("@MenuId", SqlDbType.Int, 4).Value = entity.MenuId; parameters.Add("@FormName", SqlDbType.NVarChar, 50).Value = entity.FormName; parameters.Add("@Path", SqlDbType.NVarChar, 50).Value = entity.Path; parameters.Add("@FormIndex", SqlDbType.Int, 4).Value = entity.FormIndex; parameters.Add("@FormImgURL", SqlDbType.NVarChar, 50).Value = entity.FormImgURL; return(parameters); }
/// <summary> /// 删除 /// </summary> /// <param name="Id"></param> /// <returns></returns> public override bool Delete(int Id) { StringBuilder strSql = new StringBuilder(); strSql.Append("Update WxPayConfig set RowState=0 "); strSql.Append(" where ID=@ID "); var parameters = WriteAdoTemplate.CreateDbParameters(); parameters.Add("ID", DbType.Int32, 0).Value = Id; return(ReadAdoTemplate.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters) > 0); }
/// <summary> /// 获取信息列表 /// </summary> /// <param name="query"></param> /// <returns></returns> public IList <RoleDomain> GetUser_RoleList(int UserId) { var parameters = WriteAdoTemplate.CreateDbParameters(); StringBuilder strSql = new StringBuilder(); strSql.Append("select SetUp_Role.ID,RoleName,SetUp_Role_User.Id as RU_ID "); strSql.Append(" FROM SetUp_Role left join SetUp_Role_User on SetUp_Role_User.Role_Id=SetUp_Role.Id and SetUp_Role_User.User_Id=" + UserId); return(ReadAdoTemplate.QueryWithRowMapperDelegate <RoleDomain>(CommandType.Text, strSql.ToString(), GetUser_RoleList_MapRow, parameters)); }
/// <summary> /// 批量删除 /// </summary> /// <param name="Id"></param> /// <returns></returns> public bool DeleteByMainId(int MainId) { StringBuilder strSql = new StringBuilder(); strSql.Append("delete from Base_ProductionLines "); strSql.Append(" where ProLineId=@ProLineId "); var parameters = WriteAdoTemplate.CreateDbParameters(); parameters.Add("ProLineId", DbType.Int32, 0).Value = MainId; return(ReadAdoTemplate.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters) > 0); }
/// <summary> /// 获取信息列表 /// </summary> /// <param name="query"></param> /// <returns></returns> public IList <Base_LineHour> GetLineHourList(string proLineNosList) { var parameters = WriteAdoTemplate.CreateDbParameters(); StringBuilder strSql = new StringBuilder(); strSql.Append("select Id,ProLineNo,MaxHours,CreateTime,Creater,UpdateTime,Updater,RowState "); strSql.Append(" FROM Base_LineHour "); strSql.AppendFormat("where RowState=1 and ProLineNo in({0})", proLineNosList); return(ReadAdoTemplate.QueryWithRowMapperDelegate <Base_LineHour>(CommandType.Text, strSql.ToString(), MapRow, parameters)); }
/// <summary> /// 获取信息列表 /// </summary> /// <param name="query"></param> /// <returns></returns> public IList <Pro_ShipPlans> GetAllDomainByLineNos(QueryCondition query) { var mainId = query.GetCondition("MainId").Value; var lineNos = query.GetCondition("LineNos").Value; var parameters = WriteAdoTemplate.CreateDbParameters(); StringBuilder strSql = new StringBuilder(); strSql.Append("select ID,PlanId,PlanDate,PlanNum "); strSql.Append(" FROM Pro_ShipPlans "); strSql.AppendFormat(@" WHERE [PlanId] IN (SELECT ID FROM [dbo].[Pro_ShipPlan] WHERE MainId={1} and (GoodNo IN (select GoodNo from [dbo].[Base_ProductionLine] where ProLineNo IN ({0}))) OR GoodNo IN (SELECT [ParentGoodNo] FROM Base_Bom))", lineNos, mainId); return(ReadAdoTemplate.QueryWithRowMapperDelegate <Pro_ShipPlans>(CommandType.Text, strSql.ToString(), MapRow, parameters)); }
/// <summary> /// 获取信息列表 /// </summary> /// <param name="query"></param> /// <returns></returns> public IList <Pro_ShipPlans> GetPro_SchedulingGoodsNumByEdit(int Id) { var parameters = WriteAdoTemplate.CreateDbParameters(); StringBuilder strSql = new StringBuilder(); strSql.Append(@"select Pro_SchedulingGoodsNum.SGoodId,SType,SDate,SNum FROM Pro_SchedulingLine left join Pro_SchedulingGoods on Pro_SchedulingLine.Id=Pro_SchedulingGoods.SLineId left join Pro_SchedulingGoodsNum on Pro_SchedulingGoodsNum.SGoodId=Pro_SchedulingGoods.Id"); strSql.AppendFormat(" where [MainId]={0} and SType>-1", Id); return(ReadAdoTemplate.QueryWithRowMapperDelegate <Pro_ShipPlans>(CommandType.Text, strSql.ToString(), MapRow_Pro_SchedulingGoodsNum, parameters)); }
/// <summary> /// 修改 /// </summary> /// <param name="entity"></param> /// <returns></returns> public override bool Update(MenuDomain entity) { StringBuilder strSql = new StringBuilder(); strSql.Append("update SetUp_Menu set "); strSql.Append("DisplayName=@DisplayName,"); strSql.Append("MenuIndex=@MenuIndex"); strSql.Append(" where Id=@Id"); var parameters = GetBaseParams(entity); parameters.Add("ID", DbType.Int32, 0).Value = entity.Id; return(WriteAdoTemplate.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters) > 0); }
/// <summary> /// 删除- 三个表一起删除 /// </summary> /// <param name="Id"></param> /// <returns></returns> public override bool Delete(int Id) { StringBuilder strSql = new StringBuilder(); strSql.Append("delete from Pro_ShipPlans where PlanId in( select ID from Pro_ShipPlan where MainId=@ID);"); strSql.Append("delete from Pro_ShipPlan where MainId=@ID;"); strSql.Append("delete from Pro_ShipPlanMain where ID=@ID;"); var parameters = WriteAdoTemplate.CreateDbParameters(); parameters.Add("ID", DbType.Int32, 0).Value = Id; return(ReadAdoTemplate.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters) > 0); }
/// <summary> /// 获取信息列表 /// </summary> /// <param name="query"></param> /// <returns></returns> public IList <Pro_SchedulingGoodsNum> GetDetailList(QueryCondition query) { var parameters = WriteAdoTemplate.CreateDbParameters(); StringBuilder strSql = new StringBuilder(); strSql.Append(@"select SType,SDate,SNum,SGoodId,Pro_SchedulingGoodsNum.Id from Pro_SchedulingLine LEFT JOIN Pro_SchedulingGoods ON Pro_SchedulingGoods.SLineId=Pro_SchedulingLine.Id LEFT JOIN Pro_SchedulingGoodsNum ON Pro_SchedulingGoodsNum.SGoodId=Pro_SchedulingGoods.Id "); strSql.AppendFormat(" WHERE SType IN (2,3,4) and Pro_SchedulingLine.MainId={0}", query.GetCondition("MainId").Value); strSql.Append(" and GoodNo is not null"); return(ReadAdoTemplate.QueryWithRowMapperDelegate <Pro_SchedulingGoodsNum>(CommandType.Text, strSql.ToString(), MapRow, parameters)); }
/// <summary> /// 删除 /// </summary> /// <param name="Id"></param> /// <returns></returns> public override bool Delete(int Id) { StringBuilder strSql = new StringBuilder(); strSql.Append(@"delete from Pro_SchedulingGoodsNum where SGoodId IN (select id from [dbo].[Pro_SchedulingGoods] where SLineId IN(select id from[dbo].[Pro_SchedulingLine] where MainId =@ID)); "); strSql.Append("delete from [Pro_SchedulingGoods] where SLineId IN ( select id from[dbo].[Pro_SchedulingLine] where MainId=@ID);"); strSql.Append("delete from [Pro_SchedulingLine] where MainId=@ID;"); strSql.Append("delete from Pro_Scheduling where ID=@ID;"); var parameters = WriteAdoTemplate.CreateDbParameters(); parameters.Add("ID", DbType.Int32, 0).Value = Id; return(ReadAdoTemplate.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters) > 0); }
/// <summary> /// 获取所有景区信息 /// </summary> /// <returns></returns> public List <SceneryDomain> GetStaticDomain() { QueryCondition query = QueryCondition.Instance.AddEqual("Scenery", "RowState", "1"); var parameters = WriteAdoTemplate.CreateDbParameters(); StringBuilder strSql = new StringBuilder(); strSql.Append("select Rate,AccountId,AccountPwd,AppName,Scenery.Id,SceneryName,SceneryTCId,AlipayId,APP_ID,ALIPAY_PUBLIC_KEY,APP_PRIVATE_KEY,APP_PUBLIC_KEY"); strSql.Append(",WeChatId,WeChatRate,[APPID],[MCHID],[PAYKEY],[APPSECRET],[SSLCERT_PATH],[SSLCERT_PASSWORD] "); strSql.Append(" FROM Scenery left join Alipay on Alipay.id=Scenery.AlipayId "); strSql.Append(" LEFT JOIN WxPayConfig ON WxPayConfig.Id=Scenery.WeChatId"); strSql.Append(query.GetSQL_Where(parameters)); strSql.Append(query.GetSQL_Order()); return(ReadAdoTemplate.QueryWithRowMapperDelegate <SceneryDomain>(CommandType.Text, strSql.ToString(), MapRow_Model, parameters).ToList()); }
/// <summary> /// 根据生产计划 产线 和 商品获取对应的产能信息 /// </summary> /// <param name="query"></param> /// <returns></returns> public IList <Base_ProductionLines> GetAllDomainByScheduing(QueryCondition query) { var parameters = WriteAdoTemplate.CreateDbParameters(); StringBuilder strSql = new StringBuilder(); strSql.Append("select distinct Base_ProductionLine.GoodNo,People,Number from[Base_ProductionLines]"); strSql.Append("left join [Base_ProductionLine] on Base_ProductionLine.ID=[Base_ProductionLines].ProLineId"); strSql.Append(@" left join Pro_SchedulingGoods on Pro_SchedulingGoods.GoodNo=Base_ProductionLine.GoodNo left join Pro_SchedulingGoodsNum on Pro_SchedulingGoodsNum.SGoodId=Pro_SchedulingGoods.Id"); strSql.AppendFormat(" where SlineId={0}", query.GetCondition("LineId").Value); strSql.AppendFormat(" and SDate='{0}'", query.GetCondition("ProDate").Value); strSql.AppendFormat(" and People in({0})", query.GetCondition("People").Value); return(ReadAdoTemplate.QueryWithRowMapperDelegate <Base_ProductionLines>(CommandType.Text, strSql.ToString(), MapRowByLineNoAndGoodNos, parameters)); }
/// <summary> /// 获取信息列表 /// </summary> /// <param name="query"></param> /// <returns></returns> public IList <Pro_SchedulingGoods> GetListBySchedulingLineId(QueryCondition query) { var parameters = WriteAdoTemplate.CreateDbParameters(); StringBuilder strSql = new StringBuilder(); strSql.Append(@"select SDate,GoodNo,GoodName,ProLineNo,ShipTo,ShipToName,PackNum,Pro_SchedulingGoods.Id,SType,SNum,Pro_SchedulingGoods.MorningNum,Pro_SchedulingGoods.MiddleNum,Pro_SchedulingGoods.EveningNum from Pro_SchedulingLine LEFT JOIN Pro_SchedulingGoods ON Pro_SchedulingGoods.SLineId=Pro_SchedulingLine.Id LEFT JOIN Pro_SchedulingGoodsNum ON Pro_SchedulingGoodsNum.SGoodId=Pro_SchedulingGoods.Id"); strSql.AppendFormat(" where SType IN (2,3,4) and Pro_SchedulingLine.Id={0}", query.GetCondition("Id").Value); strSql.AppendFormat(" and SDate='{0}'", query.GetCondition("SDate").Value); return(ReadAdoTemplate.QueryWithRowMapperDelegate <Pro_SchedulingGoods>(CommandType.Text, strSql.ToString(), MapRowBySchedulingLineId, parameters)); }
/// <summary> /// 获取信息列表 /// </summary> /// <param name="query"></param> /// <returns></returns> public IList <Pro_Scheduling> GetList(QueryCondition query, Hashtable hs) { var parameters = WriteAdoTemplate.CreateDbParameters(); StringBuilder strSql = new StringBuilder(); strSql.Append("select "); strSql.Append(" Id,ProNo,ShipMainProNo,PlanFromDate,PlanToDate,CreateTime,Creater,UpdateTime,Updater,RowState "); strSql.Append(" from Pro_Scheduling "); string otherWhere = ""; if (hs.ContainsKey("PlanFromDate")) { otherWhere += string.Format(" and '{0}'>=PlanFromDate and '{0}'<=PlanToDate", hs["PlanFromDate"]); } if (hs.ContainsKey("LineNo")) { otherWhere += string.Format(" and Exists(select id from Pro_SchedulingLine where ProLineNo like '%{0}%' and Pro_SchedulingLine.MainId=Pro_Scheduling.Id)", hs["LineNo"]); } if (hs.ContainsKey("GoodNo") || hs.ContainsKey("GoodName") || hs.ContainsKey("ShipTo")) { otherWhere += " and Id in (select MainId from Pro_SchedulingLine left join Pro_SchedulingGoods on Pro_SchedulingGoods.SLineId=Pro_SchedulingLine.Id where 1=1 "; if (hs.ContainsKey("GoodNo")) { otherWhere += string.Format(" and GoodNo like '%{0}%'", hs["GoodNo"]); } if (hs.ContainsKey("GoodName")) { otherWhere += string.Format(" and GoodName like '%{0}%'", hs["GoodName"]); } if (hs.ContainsKey("ShipTo")) { otherWhere += string.Format(" and ShipTo like '%{0}%'", hs["ShipTo"]); } otherWhere += " )"; } if (query.GetPager() != null) { strSql = new StringBuilder(GetPagerSql(strSql.ToString(), query, parameters, otherWhere)); } else { strSql.Append(query.GetSQL_Where(parameters)); strSql.Append(query.GetSQL_Order()); } return(ReadAdoTemplate.QueryWithRowMapperDelegate <Pro_Scheduling>(CommandType.Text, strSql.ToString(), MapRow, parameters)); }