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