public async Task <List <City> > GetCityListByProvinceID(string ProvinceID) { string sql = "SELECT T.* FROM SIDE_PLM_City T"; sql += " where Province=@Province "; sql += " order by nameEN"; DynamicParameters parameters = new DynamicParameters(); parameters.Add("@Province", ProvinceID, DbType.String, ParameterDirection.Input); using (IDbConnection conn = DataBaseConfig.GetSqlConnection()) { return(await Task.Run(() => conn.Query <City>(sql, parameters).ToList())); } }
/// <summary> /// MD_Grading /// </summary> /// <returns></returns> public async Task <List <MD_Grading> > GetMD_Grading(string channel) { string filter = string.Empty; if (!string.IsNullOrEmpty(channel)) { filter = string.Format("where channel ='{0}'", channel); } string selectSql = string.Format(@"SELECT Id, Channel, Grading FROM SIDE_MD_Grading {0}", filter); using (IDbConnection conn = DataBaseConfig.GetSqlConnection()) { return(await Task.Run(() => conn.Query <MD_Grading>(selectSql).ToList())); } }
public async Task <List <Province> > GetProvinceListByRegion(string Region) { string sql = "SELECT T.* FROM SIDE_PLM_Province T"; sql += " where id in (select value from dbo.Side_Split((select province from[SIDE_PLM_RegionToProvince] where region = @Region),',')) "; sql += " order by nameEN"; DynamicParameters parameters = new DynamicParameters(); parameters.Add("@Region", Region, DbType.String, ParameterDirection.Input); using (IDbConnection conn = DataBaseConfig.GetSqlConnection()) { return(await Task.Run(() => conn.Query <Province>(sql, parameters).ToList())); } }
public async Task <List <string> > GetOwnGroups(string username) { string selectsql = @"select distinct [Group] from SIDE_PLM_BLR_Customer_Group where ID in( select substring(Branch+',',number,charindex(',',Branch+',',number)-number) from SIDE_PLM_BLR_SalesOperation ,master..spt_values s where type='p' and number>0 and substring(','+Branch,number,1)=',' and SalesName=@username) "; using (IDbConnection conn = DataBaseConfig.GetSqlConnection()) { //string selectSql = @"SELECT Id, UserName, Password, Gender, Birthday, CreateDate, IsDelete FROM dbo.Users"; return(await Task.Run(() => conn.Query <string>(selectsql, new { username = username }).ToList())); } }
public async Task <string> CheckPosCode(string year, string season, string dummycode, string poscode) { using (IDbConnection conn = DataBaseConfig.GetSqlConnection()) { DynamicParameters parm = new DynamicParameters(); parm.Add("Year", year); parm.Add("@Season", season); parm.Add("DummyCode", dummycode); parm.Add("PosCode", poscode); var list = await Task.Run(() => conn.Query <string>("SIDE_PLM_CheckPoscodeAndDummyCode", parm, commandType : CommandType.StoredProcedure).ToList()); string result = list.FirstOrDefault().ToString().Trim(); return(result); } }
/// <summary> /// 分页信息 /// </summary> /// <param name="query"></param> /// <returns></returns> public PageList <BookQueryInfo> GetList(BookQuery query) { var barList = new PageList <BookQueryInfo>(query.PageIndex, query.PageSize); var queryString = @" select * from ( SELECT ROW_NUMBER() OVER(ORDER BY CreateTime DESC ) as RowID,* FROM Books where 1=1 {0}) r where RowID between @StartIndex and @EndIndex; select Count(*) as RecordTotal from Books where 1=1 {0} "; DynamicParameters para = new DynamicParameters(); para.Add("@StartIndex", query.StartIndex); para.Add("@EndIndex", query.EndIndex); string whereStr = string.Empty; if (query.ParentType > 0) { whereStr += " and ParentType=@ParentType "; para.Add("@ParentType", query.ParentType); } if (query.SubType > 0) { whereStr += " and SubType=@SubType "; para.Add("@SubType", query.SubType); } if (query.PublishHouseId > 0) { whereStr += " and PublishHouseId=@PublishHouseId "; para.Add("@PublishHouseId", query.PublishHouseId); } queryString = string.Format(queryString, whereStr); using (IDbConnection conn = DataBaseConfig.GetSqlConnection()) { using (var multi = conn.QueryMultiple(queryString, para)) { barList.List = multi.Read <BookQueryInfo>().ToList(); barList.RecordTotal = multi.ReadSingle <int>(); } } return(barList); }
/// <summary> /// /// </summary> /// <returns></returns> public DataTable GetDataSetList(string strWhere) { DataTable dt = new DataTable(); StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT ID, POS_Channel, POS_OCSGrading, POS_Type, Type_Sequence, Type_Status FROM [dbo].SIDE_MD_OCSGrading_Type "); strSql.Append(" FROM SIDE_MD_OCSGrading_Type T "); if (strWhere.Trim() != "") { strSql.Append(" where 1=1 " + strWhere); } using (IDbConnection conn = DataBaseConfig.GetSqlConnection()) { dt.Load(conn.ExecuteReader(strSql.ToString())); } return(dt); }
/// <summary> /// 创建一个用户 /// </summary> /// <param name="entity">用户</param> /// <param name="connectionString">链接字符串</param> /// <returns></returns> public Task <Boolean> CreateEntity(User entity, String connectionString = null) { using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString)) { string insertSql = @"INSERT INTO dbo.auth_User ( TenantId , Name , Password , SecurityStamp , FullName , Surname , PhoneNumber , IsPhoneNumberConfirmed , EmailAddress , IsEmailConfirmed , EmailConfirmationCode , IsActive , PasswordResetCode , LastLoginTime , IsLockoutEnabled , AccessFailedCount , LockoutEndDateUtc ) VALUES ( @tenantid , @name , @password , @securitystamp , @fullname , @surname , @phonenumber , @isphonenumberconfirmed , @emailaddress , @isemailconfirmed , @emailconfirmationcode , @isactive , @passwordresetcode , @lastlogintime , @islockoutenabled , @accessfailedcount , @lockoutenddateutc );"; return(Task.FromResult <Boolean>(conn.Execute(insertSql, entity) > 0)); } }
public async Task UpdateSalesOperation(List <CustomerGroup> channels, string username) { string ownchannels = ""; foreach (var channel in channels) { ownchannels += channel.ID + ","; } string updatesql = @"if exists (select * from SIDE_PLM_BLR_SalesOperation where SalesName=@username) update SIDE_PLM_BLR_SalesOperation set Branch=@ownchannels where SalesName=@username else insert into SIDE_PLM_BLR_SalesOperation(SalesName,Branch)values(@username,@ownchannels) "; using (IDbConnection conn = DataBaseConfig.GetSqlConnection()) { await Task.Run(() => conn.ExecuteAsync(updatesql, new { username, ownchannels = ownchannels.Substring(0, ownchannels.Length - 1) })); } }
public async Task <object> GetMD_BulkShipToByWhere([FromQuery] string BranchCN, string BranchEN, string Province, string City, string StoreFormat, string WHSChannel) { string selectSql = @"SELECT top 1 * FROM SIDE_MD_BulkShipTo where BRANCH_CN = @BranchCN and BRANCH_EN=@BranchEN and WHS_Channel=@WHSChannel "; if (!string.IsNullOrEmpty(StoreFormat) && StoreFormat != "0") { selectSql = selectSql + " and ID in (select ID from SIDE_MD_BulkShipTo where(charindex(',' + @StoreFormat + ',',',' + StoreFormat + ',')> 0 or isnull(StoreFormat,'')='' ) )"; } else { selectSql = selectSql + " and isnull(StoreFormat,'')='' "; } if (!string.IsNullOrEmpty(Province) && Province != "0") { selectSql = selectSql + " and ID in (select ID from SIDE_MD_BulkShipTo where(charindex(',' + @Province + ',',',' + Province + ',')> 0 or isnull(Province,'')='' ) )"; } else { selectSql = selectSql + " and isnull(Province,'')='' "; } if (!string.IsNullOrEmpty(City) && City != "0") { selectSql = selectSql + " and ID in (select ID from SIDE_MD_BulkShipTo where(charindex(',' + @City + ',',',' + City + ',')> 0 or isnull(City,'')='' ) )"; } else { selectSql = selectSql + " and isnull(City,'')='' "; } DynamicParameters parameters = new DynamicParameters(); parameters.Add("@BranchEN", BranchEN, DbType.String, ParameterDirection.Input); parameters.Add("@BranchCN", BranchCN, DbType.String, ParameterDirection.Input); parameters.Add("@Province", Province, DbType.String, ParameterDirection.Input); parameters.Add("@City", City, DbType.String, ParameterDirection.Input); parameters.Add("@StoreFormat", StoreFormat, DbType.String, ParameterDirection.Input); parameters.Add("@WHSChannel", WHSChannel, DbType.String, ParameterDirection.Input); using (IDbConnection conn = DataBaseConfig.GetSqlConnection()) { return(Ok(await Task.Run(() => conn.Query <MD_BulkShipTo>(selectSql, parameters).FirstOrDefault()))); } }
public async Task <List <POS_MasterImport> > ImportData(List <POS_MasterImport> Model, List <string> colString, string ProcName) { StringBuilder sbCol = new StringBuilder(); StringBuilder sbColProp = new StringBuilder(); foreach (var col in colString) { sbCol.Append(col + ","); sbColProp.Append("@" + col + ","); } string Col = sbCol.ToString().Substring(0, sbCol.ToString().Length - 1); string ColProp = sbColProp.ToString().Substring(0, sbColProp.ToString().Length - 1); string insertSql = $"insert into [SIDE_PLM_POS_MasterImport]({Col}) Values({ColProp})"; string delSql = $"delete SIDE_PLM_POS_MasterImport where UserID={Model[0].UserID}"; using (IDbConnection conn = DataBaseConfig.GetSqlConnection()) { IDbTransaction transaction = conn.BeginTransaction(); try { await conn.ExecuteAsync(delSql, null, transaction); foreach (var mol in Model) { await conn.ExecuteAsync(insertSql, mol, transaction); } DynamicParameters parm = new DynamicParameters(); parm.Add("USERID", Model[0].UserID); // var list = await Task.Run(() => conn.Query <POS_MasterImport>(ProcName, parm, transaction, commandType : CommandType.StoredProcedure).ToList()); transaction.Commit(); return(list); } catch (Exception exception) { transaction.Rollback(); return(null); throw new Exception(exception.Message); } } }
public OkObjectResult MD_POS_TypeEdit(MD_POS_Type entity) { ResultModel result = new ResultModel(); result.IsSuccess = true; int Total = 0;//这一步在优先次序上加一 string sqlstr = "select count(ID) from SIDE_MD_POS_Type where POS_Channel=@POS_Channel and POS_Segment=@POS_Segment and POS_Type=@POS_Type "; DynamicParameters parameters = new DynamicParameters(); parameters.Add("@POS_Channel", entity.POS_Channel, DbType.String, ParameterDirection.Input); parameters.Add("@POS_Segment", entity.POS_Segment, DbType.String, ParameterDirection.Input); parameters.Add("@POS_Type", entity.POS_Type, DbType.Int32, ParameterDirection.Input); using (IDbConnection conn = DataBaseConfig.GetSqlConnection()) { Total = (Int32)conn.ExecuteScalar(sqlstr, parameters); } if (Total > 0) { result.IsSuccess = false; result.Message = "Already exist"; result.StatusCode = "-4"; } else { if (entity.ID > 0) { int row = masterdataRepository.UpdateMD_POS_Type(entity); result.StatusCode = row.ToString(); result.Message = "OK"; } else { entity.Type_Status = 1; int row = masterdataRepository.AddMD_POS_Type(entity); result.StatusCode = row.ToString(); result.Message = "OK"; } } return(Ok(result)); }
public Users GetUserByEmail(string email) { Users userList = new Users(); string selectSql = @"SELECT [id] ,[region] ,[name] ,[ntAccount] ,[isValid] ,[isHeadquarter] ,[email] ,[is_internal] ,[password_encryption] FROM SIDE_PLM_Employee WHERE email=@email"; using (IDbConnection connection = DataBaseConfig.GetSqlConnection()) { Users _user = connection.QueryFirstOrDefault <Users>(selectSql, new { email }); return(_user); } }
/// <summary> /// 通过角色名获取所有Account Sales /// </summary> /// <returns></returns> public async Task <List <Users> > GetAccountSalesByRole() { string sql = @"SELECT DISTINCT dbo.SIDE_PLM_Employee.id, dbo.SIDE_PLM_Employee.region, dbo.SIDE_PLM_Employee.name, dbo.SIDE_PLM_Employee.ntAccount, dbo.SIDE_PLM_Employee.isValid, dbo.SIDE_PLM_Employee.isHeadquarter, dbo.SIDE_PLM_Employee.email, dbo.SIDE_PLM_Employee.is_internal, dbo.SIDE_PLM_Employee.password_encryption FROM (SELECT id FROM dbo.SIDE_PLM_Role WHERE (nameEN = 'Account Sales(RA)') OR (nameEN = 'Account Sales(Kids)') OR (nameEN = 'Account Sales(Belle)') OR (nameEN = 'Account Sales(YY)') OR (nameEN = 'Account Sales(MB)')) AS tmep_role INNER JOIN dbo.SIDE_PLM_EmployeeRole ON tmep_role.id = dbo.SIDE_PLM_EmployeeRole.role INNER JOIN dbo.SIDE_PLM_Employee ON dbo.SIDE_PLM_EmployeeRole.employee = dbo.SIDE_PLM_Employee.id "; using (IDbConnection conn = DataBaseConfig.GetSqlConnection()) { return(await Task.Run(() => conn.Query <Users>(sql, null).ToList())); } }
/// <summary> /// 添加买货窗口 /// </summary> /// <param name="model"></param> /// <returns></returns> public ResultModel NewBuyWindowSP(SIDE_PLM_BLR_BuyWindow model) { ResultModel result = new ResultModel(); using (IDbConnection conn = DataBaseConfig.GetSqlConnection()) { DynamicParameters parameters = new DynamicParameters(); parameters.Add("@POSConfirmationStartDate", model.POSConfirmationStartDate, DbType.String, ParameterDirection.Input); parameters.Add("@POSConfirmationEndDate", model.POSConfirmationEndDate, DbType.String, ParameterDirection.Input); parameters.Add("@TargetPOSConfirmationStartDate", model.TargetPOSConfirmationStartDate, DbType.String, ParameterDirection.Input); parameters.Add("@TargetPOSConfirmationEndDate", model.TargetPOSConfirmationEndDate, DbType.String, ParameterDirection.Input); parameters.Add("@TradingMeetingStartDate", model.TradingMeetingStartDate, DbType.String, ParameterDirection.Input); parameters.Add("@TradingMeetingEndDate", model.TradingMeetingEndDate, DbType.String, ParameterDirection.Input); parameters.Add("@STC_MandatoryStartDate", model.STC_MandatoryStartDate, DbType.String, ParameterDirection.Input); parameters.Add("@STC_MandatoryEndDate", model.STC_MandatoryEndDate, DbType.String, ParameterDirection.Input); parameters.Add("@Segment_POSTypeStartDate", model.Segment_POSTypeStartDate, DbType.String, ParameterDirection.Input); parameters.Add("@Segment_POSTypeEndDate", model.Segment_POSTypeEndDate, DbType.String, ParameterDirection.Input); parameters.Add("@V1StartDate", model.V1StartDate, DbType.String, ParameterDirection.Input); parameters.Add("@V1EndDate", model.V1EndDate, DbType.String, ParameterDirection.Input); parameters.Add("@V2StartDate", model.V2StartDate, DbType.String, ParameterDirection.Input); parameters.Add("@V2EndDate", model.V2EndDate, DbType.String, ParameterDirection.Input); parameters.Add("@Year", model.Year, DbType.String, ParameterDirection.Input); parameters.Add("@Name", model.WindowContent, DbType.String, ParameterDirection.Input); parameters.Add("@Season", model.Season, DbType.String, ParameterDirection.Input); parameters.Add("@CreatePeople", model.CreatePeople, DbType.String, ParameterDirection.Input); parameters.Add("@ReturnMsg", "", DbType.String, ParameterDirection.Output, 100); parameters.Add("@ReturnCode", "0", DbType.String, ParameterDirection.Output, 100); conn.Execute("SIDE_NewBuyingWindow", parameters, null, null, CommandType.StoredProcedure); string ReturnCode = parameters.Get <string>("@ReturnCode"); result.StatusCode = ReturnCode.ToString(); result.Message = parameters.Get <string>("@ReturnMsg"); if (ReturnCode == "1") { result.IsSuccess = true; } return(result); } }
public int UpdateMD_BulkShipTo(MD_BulkShipTo model) { string updateSql = @"update SIDE_MD_BulkShipTo set BRANCH_EN=@BRANCH_EN,City=@City, BRANCH_CN=@BRANCH_CN,BLR=@BLR,Province=@Province,StoreFormat=@StoreFormat, WHS_Channel=@WHS_Channel, Bulk_Ship_to_Code_Core=@Bulk_Ship_to_Code_Core, Bulk_Ship_to_Code_Originals=@Bulk_Ship_to_Code_Originals, Bulk_Ship_to_Code_Neo=@Bulk_Ship_to_Code_Neo, Bulk_Ship_to_Code_Kids=@Bulk_Ship_to_Code_Kids WHERE ID=@ID"; DynamicParameters parameters = new DynamicParameters(); parameters.Add("@ID", model.ID, DbType.Int32, ParameterDirection.Input); parameters.Add("@BRANCH_EN", model.BRANCH_EN, DbType.String, ParameterDirection.Input); parameters.Add("@BRANCH_CN", model.BRANCH_CN, DbType.String, ParameterDirection.Input); parameters.Add("@WHS_Channel", model.WHS_Channel, DbType.String, ParameterDirection.Input); parameters.Add("@BLR", model.BLR, DbType.String, ParameterDirection.Input); parameters.Add("@City", model.City, DbType.String, ParameterDirection.Input); parameters.Add("@Province", model.Province, DbType.String, ParameterDirection.Input); parameters.Add("@StoreFormat", model.StoreFormat, DbType.String, ParameterDirection.Input); parameters.Add("@Bulk_Ship_to_Code_Core", model.Bulk_Ship_to_Code_Core, DbType.String, ParameterDirection.Input); parameters.Add("@Bulk_Ship_to_Code_Originals", model.Bulk_Ship_to_Code_Originals, DbType.String, ParameterDirection.Input); parameters.Add("@Bulk_Ship_to_Code_Neo", model.Bulk_Ship_to_Code_Neo, DbType.String, ParameterDirection.Input); parameters.Add("@Bulk_Ship_to_Code_Kids", model.Bulk_Ship_to_Code_Kids, DbType.String, ParameterDirection.Input); using (IDbConnection conn = DataBaseConfig.GetSqlConnection()) { return(conn.Execute(updateSql, parameters)); } }
/// <summary> /// /// </summary> /// <returns></returns> public int UpdateMD_POS_Type(MD_POS_Type model) { StringBuilder strSqlupdate = new StringBuilder(); // 修改有限顺序序号 strSqlupdate.Append(" if exists(select * from SIDE_MD_POS_Type where POS_Channel = @POS_Channel and POS_Segment = @POS_Segment and Type_Sequence = @Type_Sequence and ID<>@ID)"); strSqlupdate.Append(" begin"); strSqlupdate.Append(" update SIDE_MD_POS_Type set Type_Sequence = Type_Sequence + 1 where POS_Channel = @POS_Channel and POS_Segment = @POS_Segment and Type_Sequence>= @Type_Sequence"); strSqlupdate.Append(" end "); string updateSql = @"update SIDE_MD_POS_Type set POS_Channel=@POS_Channel, POS_Segment=@POS_Segment, POS_Type=@POS_Type, Type_Sequence=@Type_Sequence, Type_Status=@Type_Status FROM [dbo].SIDE_MD_POS_Type WHERE ID=@ID"; DynamicParameters parameters = new DynamicParameters(); parameters.Add("@ID", model.ID, DbType.Int32, ParameterDirection.Input); parameters.Add("@POS_Channel", model.POS_Channel, DbType.String, ParameterDirection.Input); parameters.Add("@POS_Segment", model.POS_Segment, DbType.String, ParameterDirection.Input); parameters.Add("@POS_Type", model.POS_Type, DbType.String, ParameterDirection.Input); parameters.Add("@Type_Sequence", model.Type_Sequence, DbType.Int32, ParameterDirection.Input); parameters.Add("@Type_Status", model.Type_Status, DbType.Int32, ParameterDirection.Input); using (IDbConnection conn = DataBaseConfig.GetSqlConnection()) { conn.Execute(strSqlupdate.ToString(), parameters); return(conn.Execute(updateSql, parameters)); } }
/// <summary> /// /// </summary> /// <param name="viewName"></param> /// <param name="fieldName"></param> /// <param name="whereString"></param> /// <param name="orderString"></param> /// <param name="page"></param> /// <param name="pageSize"></param> /// <returns></returns> public async Task <Tuple <List <MD_BulkShipTo>, int> > GetPageByProcList_MD_BulkShipTo(string viewName, string fieldName = "*", string whereString = " 1=1", string orderString = "ID", int page = 1, int pageSize = 10) { int recordTotal = 0; using (IDbConnection conn = DataBaseConfig.GetSqlConnection()) { //conn.Open(); DynamicParameters parm = new DynamicParameters(); parm.Add("viewName", viewName); parm.Add("fieldName", fieldName); parm.Add("whereString", whereString); parm.Add("pageSize", pageSize); parm.Add("pageNo", page); parm.Add("orderString", orderString); parm.Add("recordTotal", 0, DbType.Int32, ParameterDirection.Output); var list = await Task.Run(() => conn.Query <MD_BulkShipTo>("SIDE_PLM_ProcViewPager", parm, commandType : CommandType.StoredProcedure).ToList()); recordTotal = parm.Get <int>("@recordTotal");//返回总页数 // conn.Close(); return(new Tuple <List <MD_BulkShipTo>, int>(list, recordTotal)); } }
//public async Task<Tuple<List<POS_Master>, int>> GetPageByProcList(string viewName, string fieldName = "*", string wherestring = " 1=1", string orderString = "ID", int page = 1, int pageSize = 10) //{ // int recordTotal = 0; // using (IDbConnection conn = DataBaseConfig.GetSqlConnection()) // { // //conn.Open(); // DynamicParameters parm = new DynamicParameters(); // parm.Add("viewName", viewName); // parm.Add("fieldName", fieldName); // parm.Add("whereString", wherestring); // parm.Add("pageSize", pageSize); // parm.Add("pageNo", page); // parm.Add("orderString", orderString); // parm.Add("recordTotal", 0, DbType.Int32, ParameterDirection.Output); // var list = await Task.Run(() => conn.Query<POS_Master>("SIDE_PLM_ProcViewPager", parm, commandType: CommandType.StoredProcedure).ToList()); // recordTotal = parm.Get<int>("@recordTotal");//返回总页数 // // conn.Close(); // return new Tuple<List<POS_Master>, int>(list, recordTotal); // } //} public async Task <Tuple <List <POS_Master>, int> > GetPageByProcList(CTCApproval Model) { string sqlText = "select * from SIDE_PLM_V_POS_Master where 1=1"; DynamicParameters parm = new DynamicParameters(); if (!string.IsNullOrEmpty(Model.Year)) { sqlText += " and TM_YEAR = @Year"; parm.Add("Year", Model.Year); } if (!string.IsNullOrEmpty(Model.Season)) { sqlText += " and TM_SEASON = @Season"; parm.Add("Season", Model.Season); } if (!string.IsNullOrEmpty(Model.Group)) { sqlText += " and CUSTOMER_GROUP = @Group"; parm.Add("Group", Model.Group); } if (!string.IsNullOrEmpty(Model.Branch)) { sqlText += " and BRANCH_EN = @Branch"; parm.Add("Branch", Model.Branch); } if (!string.IsNullOrEmpty(Model.Channel)) { sqlText += " and POS_CHANNEL = @Channel"; parm.Add("Channel", Model.Channel); } if (!string.IsNullOrEmpty(Model.City)) { sqlText += " and City_CN LIKE @City"; parm.Add("City", "%" + Model.City + "%"); } if (!string.IsNullOrEmpty(Model.Province)) { sqlText += " and PROVINCE_CN LIKE @Province"; parm.Add("Province", "%" + Model.Province + "%"); } if (!string.IsNullOrEmpty(Model.Sellspacebegin) && string.IsNullOrEmpty(Model.Sellspaceend)) { sqlText += " and SELLING_SPACE >= @Sellspacebegin"; parm.Add("Sellspacebegin", Model.Sellspacebegin); } if (string.IsNullOrEmpty(Model.Sellspacebegin) && !string.IsNullOrEmpty(Model.Sellspaceend)) { sqlText += " and SELLING_SPACE <= @Sellspaceend"; parm.Add("Sellspaceend", Model.Sellspaceend); } if (!string.IsNullOrEmpty(Model.Province) && !string.IsNullOrEmpty(Model.Sellspaceend)) { sqlText += " and SELLING_SPACE between @Sellspacebegin and @Sellspaceend"; parm.Add("Sellspacebegin", Model.Sellspacebegin); parm.Add("Sellspaceend", Model.Sellspaceend); } if (!string.IsNullOrEmpty(Model.Range)) { sqlText += " and (POS_Type is NULL OR POS_Type in (select POS_TYPE from SIDE_MD_POS_Type WHERE Type_Status=0) )"; } parm.Add("PageIndex", Model.PageIndex); parm.Add("PageSize", Model.PageSize); string selectQuery = @" ;WITH _data AS (" + sqlText + @" ), _count AS ( SELECT COUNT(1) AS TotalCount FROM _data ) SELECT * FROM _data CROSS APPLY _count Order by ID OFFSET @PageIndex * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY; "; string sqlText2 = sqlText.Replace("*", "COUNT(*)"); selectQuery += sqlText2; using (IDbConnection conn = DataBaseConfig.GetSqlConnection()) { var list = await conn.QueryMultipleAsync(selectQuery, parm); var i = list.Read <POS_Master>(); var s = list.Read <int>().Single(); Tuple <List <POS_Master>, int> tuple = new Tuple <List <POS_Master>, int>(i.ToList(), s); return(tuple); } }
public async Task <List <POS_Master> > GetExportList(SOApproval Model, string RoleName, string userid, string username, string roleid) { string sqlText = "select * from SIDE_PLM_V_POS_Master where 1=1"; DynamicParameters parm = new DynamicParameters(); if (RoleName == "AS") { sqlText += $" AND CUSTOMER_CODE IN (select Customer_Code from SIDE_PLM_BLR_CustomersInfo where Account_SalesID={userid})"; } else if (RoleName == "ASM") { sqlText += $" AND CUSTOMER_CODE IN (select Customer_Code from SIDE_PLM_BLR_CustomersInfo where Account_SalesID IN (SELECT AS_ID FROM SIDE_PLM_BLR_Approve_Process WHERE ASM_ID={userid}))"; } else if (RoleName == "SO") { sqlText += @" AND BRANCH_EN in ( select distinct [Branch] from SIDE_PLM_BLR_Customer_Group where ID in( select substring(Branch+',',number,charindex(',',Branch+',',number)-number) from SIDE_PLM_BLR_SalesOperation ,master..spt_values s where type='p' and number>0 and substring(','+Branch,number,1)=','" + $"and SalesName='{username}'))"; } else if (roleid.Contains('4')) { sqlText += $" AND CUSTOMER_CODE IN (select Customer_Code from SIDE_PLM_BLR_CustomersInfo where kids_account_salesID={userid})"; } //else if (HttpContext.User.IsInRole("admin") || HttpContext.User.IsInRole("BLR") || HttpContext.User.IsInRole("RE") || HttpContext.User.IsInRole("CTC")) //{ //} if (!string.IsNullOrEmpty(Model.year)) { sqlText += " and TM_YEAR = @Year"; parm.Add("Year", Model.year); } if (!string.IsNullOrEmpty(Model.season)) { sqlText += " and TM_SEASON = @Season"; parm.Add("Season", Model.season); } if (!string.IsNullOrEmpty(Model.customer)) { sqlText += " and CUSTOMER_GROUP = @customer"; parm.Add("customer", Model.customer); } if (!string.IsNullOrEmpty(Model.branch)) { sqlText += " and BRANCH_EN = @Branch"; parm.Add("Branch", Model.branch); } if (!string.IsNullOrEmpty(Model.channel)) { sqlText += " and POS_CHANNEL = @Channel"; parm.Add("Channel", Model.channel); } if (!string.IsNullOrEmpty(Model.city)) { sqlText += " and City_CN LIKE @City"; parm.Add("City", "%" + Model.city + "%"); } if (!string.IsNullOrEmpty(Model.province)) { sqlText += " and PROVINCE_CN LIKE @Province"; parm.Add("Province", "%" + Model.province + "%"); } if (!string.IsNullOrEmpty(Model.poscode)) { sqlText += " and POS_CODE LIKE @POS_CODE"; parm.Add("POS_CODE", "%" + Model.poscode + "%"); } if (!string.IsNullOrEmpty(Model.format)) { sqlText += " and BIG_FORMAT LIKE @Format"; parm.Add("Format", "%" + Model.format + "%"); } if (!string.IsNullOrEmpty(Model.posname)) { sqlText += " and POS_NAME LIKE @Posname"; parm.Add("Posname", "%" + Model.posname + "%"); } using (IDbConnection conn = DataBaseConfig.GetSqlConnection()) { var list = await conn.QueryAsync <POS_Master>(sqlText, parm); return(list.ToList()); } }
public async Task <List <POS_Master> > GetExportList(CTCApproval Model) { string sqlText = "select * from SIDE_PLM_V_POS_Master where 1=1"; DynamicParameters parm = new DynamicParameters(); if (!string.IsNullOrEmpty(Model.Year)) { sqlText += " and TM_YEAR = @Year"; parm.Add("Year", Model.Year); } if (!string.IsNullOrEmpty(Model.Season)) { sqlText += " and TM_SEASON = @Season"; parm.Add("Season", Model.Season); } if (!string.IsNullOrEmpty(Model.Group)) { sqlText += " and CUSTOMER_GROUP = @Group"; parm.Add("Group", Model.Group); } if (!string.IsNullOrEmpty(Model.Branch)) { sqlText += " and BRANCH_EN = @Branch"; parm.Add("Branch", Model.Branch); } if (!string.IsNullOrEmpty(Model.Channel)) { sqlText += " and POS_CHANNEL = @Channel"; parm.Add("Channel", Model.Channel); } if (!string.IsNullOrEmpty(Model.City)) { sqlText += " and City_CN LIKE @City"; parm.Add("City", "%" + Model.City + "%"); } if (!string.IsNullOrEmpty(Model.Province)) { sqlText += " and PROVINCE_CN LIKE @Province"; parm.Add("Province", "%" + Model.Province + "%"); } if (!string.IsNullOrEmpty(Model.Sellspacebegin) && string.IsNullOrEmpty(Model.Sellspaceend)) { sqlText += " and SELLING_SPACE >= @Sellspacebegin"; parm.Add("Sellspacebegin", Model.Sellspacebegin); } if (!string.IsNullOrEmpty(Model.Sellspacebegin) && string.IsNullOrEmpty(Model.Sellspaceend)) { sqlText += " and SELLING_SPACE <= @Sellspaceend"; parm.Add("Sellspaceend", Model.Sellspaceend); } if (!string.IsNullOrEmpty(Model.Province) && !string.IsNullOrEmpty(Model.Sellspaceend)) { sqlText += " and SELLING_SPACE between @Sellspacebegin and @Sellspaceend"; parm.Add("Sellspacebegin", Model.Sellspacebegin); parm.Add("Sellspaceend", Model.Sellspaceend); } if (!string.IsNullOrEmpty(Model.Range)) { sqlText += " and (POS_Type is NULL OR POS_Type in (select POS_TYPE from SIDE_MD_POS_Type WHERE Type_Status=0) )"; } using (IDbConnection conn = DataBaseConfig.GetSqlConnection()) { var list = await conn.QueryAsync <POS_Master>(sqlText, parm); return(list.ToList()); } }
/// <summary> /// 数据库连接信息 /// </summary> /// <returns></returns> public IDbConnection GetConnection() { IDbConnection conn = DataBaseConfig.GetSqlConnection(); return(conn); }