/// <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));
}