public CustomPriceRuleModel FindCustomPriceRule(int customPriceColumnId, CustomPriceRuleLevels customPriceRuleLevel,
int?itemId, int?manufacturerCompanyId, int?commodityId, string ucc)
{
using (IDbConnection conn = Connection)
{
string query = @"SELECT [CustomPriceRuleId]
,[CustomPriceColumnId]
,[UCC]
FROM [TSO].[dbo].[CustomPriceRule]
WHERE [CustomPriceColumnId] = @customPriceColumnId
AND [CustomPriceRuleLevelId] = @customPriceRuleLevel";
var parameters = new DynamicParameters();
parameters.Add("@customPriceColumnId", customPriceColumnId);
parameters.Add("@customPriceRuleLevel", (int)customPriceRuleLevel);
switch (customPriceRuleLevel)
{
case (CustomPriceRuleLevels.Item):
query += @" AND ItemId = @itemId";
parameters.Add("@itemId", itemId);
break;
case (CustomPriceRuleLevels.ManufacturerAndCommodity):
query += @" AND ManufacturerCompanyID = @manufacturerCompanyId
AND CommodityId = @commodityId";
parameters.Add("@manufacturerCompanyId", manufacturerCompanyId);
parameters.Add("@commodityId", commodityId);
break;
case (CustomPriceRuleLevels.Manufacturer):
query += @" AND ManufacturerCompanyID = @manufacturerCompanyId";
parameters.Add("@manufacturerCompanyId", manufacturerCompanyId);
break;
case (CustomPriceRuleLevels.Commodity):
query += @" AND CommodityId = @commodityId";
parameters.Add("@commodityId", commodityId);
break;
case (CustomPriceRuleLevels.Ucc):
query += @" AND ManufacturerCompanyID = @manufacturerCompanyId
AND Ucc = @ucc";
parameters.Add("@manufacturerCompanyId", manufacturerCompanyId);
parameters.Add("@ucc", ucc);
break;
}
return(conn.QueryFirstOrDefault <CustomPriceRuleModel>(query, parameters));
}
}
public void UpdateCustomPriceRule(int customPriceRuleId, CustomPriceRuleLevels customPriceRuleLevel, int?itemId, int?manufactuerCompanyId,
int?commodityId, PriceTypes?baseColumnPriceTypeId, CustomPriceOperations?operationTypeId, decimal customPriceRuleValue, bool isPercent, string customPriceRuleDescription,
Uom?fixedPriceUnitIfMeasureTypeCode, string ucc, string actorName, string systemUsername)
{
using (IDbConnection conn = Connection)
{
var query = @"UPDATE [TSO].[dbo].[CustomPriceRule]
SET [CustomPriceRuleLevelId] = @customPriceRuleLevel
,[ItemId] = @itemId
,[ManufacturerCompanyId] = @manufactuerCompanyId
,[CommodityId] = @commodityId
,[BaseColumnPriceTypeId] = @baseColumnPriceTypeId
,[OperationTypeId] = @operationTypeId
,[CustomPriceRuleValue] = @customPriceRuleValue
,[IsPercent] = @isPercent
,[CustomPriceRuleDescription] = @customPriceRuleDescription
,[FixedPriceUnitOfMeasureTypeCode] = @strUom
,[LastUpdatedDateTime] = getdate()
,[ActorName] = @actorName
,[SystemUsername] = @systemUsername
,[UCC] = @ucc
WHERE [CustomPriceRuleId] = @customPriceRuleId";
var strUom = fixedPriceUnitIfMeasureTypeCode != null?fixedPriceUnitIfMeasureTypeCode.ToString() : null;
// HACK: See TSL-1235 : CustomPriceOperations.FixedPrice must translate to a null in the CustomPriceRule row.
CustomPriceOperations?opTypeId = operationTypeId == CustomPriceOperations.FixedPrice ? null : operationTypeId;
conn.Execute(query,
new
{
customPriceRuleId,
customPriceRuleLevel,
itemId,
manufactuerCompanyId,
commodityId,
baseColumnPriceTypeId,
operationTypeId = opTypeId,
customPriceRuleValue,
isPercent,
customPriceRuleDescription,
strUom,
ucc,
actorName,
systemUsername
});
}
}
public int CreateCustomPriceRule(int customPriceColumnId, CustomPriceRuleLevels customPriceRuleLevel, int?itemId,
int?manufactuerCompanyId, int?commodityId, PriceTypes?baseColumnPriceTypeId, CustomPriceOperations?operationTypeId,
decimal customPriceRuleValue, bool isPercent, string customPriceRuleDescription, Uom?fixedPriceUnitIfMeasureTypeCode,
string ucc, string actorName, string systemUsername)
{
using (IDbConnection conn = Connection)
{
var query = @"INSERT INTO [TSO].[dbo].[CustomPriceRule]
([CustomPriceColumnId]
,[CustomPriceRuleLevelId]
,[ItemId]
,[ManufacturerCompanyId]
,[CommodityId]
,[BaseColumnPriceTypeId]
,[OperationTypeId]
,[CustomPriceRuleValue]
,[IsPercent]
,[CustomPriceRuleDescription]
,[FixedPriceUnitOfMeasureTypeCode]
,[CreatedDateTime]
,[LastUpdatedDateTime]
,[ActorName]
,[SystemUsername]
,[UCC])
VALUES
(@customPriceColumnId
,@customPriceRuleLevel
,@itemId
,@manufactuerCompanyId
,@commodityId
,@baseColumnPriceTypeId
,@operationTypeId
,@customPriceRuleValue
,@isPercent
,@customPriceRuleDescription
,@strUom
,getdate()
,getdate()
,@actorName
,@systemUsername
,@ucc);
SELECT CAST(SCOPE_IDENTITY() as int)";
var strUom = fixedPriceUnitIfMeasureTypeCode != null?fixedPriceUnitIfMeasureTypeCode.ToString() : null;
return(conn.ExecuteScalar <int>(query,
new
{
customPriceColumnId,
customPriceRuleLevel,
itemId,
manufactuerCompanyId,
commodityId,
baseColumnPriceTypeId,
operationTypeId,
customPriceRuleValue,
isPercent,
customPriceRuleDescription,
strUom,
ucc,
actorName,
systemUsername
}));
}
}
