/// <summary>
/// Insert or update the passed pattern of specific customer
/// </summary>
/// <param name="customerId"></param>
/// <param name="vasId"></param>
/// <param name="vasPattern"></param>
/// <param name="patternDescription"></param>
/// <param name="userRemarks"></param>
/// <param name="inactiveFlag"> </param>
/// <param name="updateVas">If true then only update the VAS settings regEx and its descriptions</param>
/// <returns></returns>
public void UpdateVasConfiguration(string customerId, string vasId, string vasPattern, string patternDescription, string userRemarks, bool inactiveFlag, bool updateVas)
{
const string QUERY = @"
MERGE INTO <proxy />MASTER_CUSTOMER_VAS MCV
USING DUAL
ON (MCV.CUSTOMER_ID = :CUSTOMER_ID AND MCV.vas_id=:VAS_ID)
WHEN MATCHED THEN
UPDATE SET
<if c='$UPDATE_VAS_FLAG'>
MCV.VAS_REGEXP = :VAS_REGEXP,
MCV.VAS_REGEXP_DESCRIPTION = :VAS_REGEXP_DESCRIPTION,
</if>
<if>MCV.USER_REMARK =:USER_REMARK,</if>
<if c='$INACTIVE_FLAG'>
MCV.INACTIVE_FLAG = 'Y'
</if>
<else>
MCV.INACTIVE_FLAG = NULL
</else>
WHEN NOT MATCHED THEN
INSERT
(MCV.CUSTOMER_ID,
MCV.VAS_ID,
MCV.VAS_REGEXP,
MCV.VAS_REGEXP_DESCRIPTION,
MCV.USER_REMARK
<if c='$INACTIVE_FLAG'>,MCV.INACTIVE_FLAG</if>)
VALUES
(:CUSTOMER_ID,
:VAS_ID,
:VAS_REGEXP,
:VAS_REGEXP_DESCRIPTION,
:USER_REMARK
<if c='$INACTIVE_FLAG'>,'Y'</if>)
";
var binder = SqlBinder.Create()
.Parameter("CUSTOMER_ID", customerId)
.Parameter("VAS_ID", vasId)
.Parameter("VAS_REGEXP", vasPattern)
.Parameter("VAS_REGEXP_DESCRIPTION", patternDescription)
.Parameter("USER_REMARK", userRemarks);
binder.ParameterXPath("INACTIVE_FLAG", inactiveFlag);
binder.ParameterXPath("UPDATE_VAS_FLAG", updateVas);
_db.ExecuteDml(QUERY, binder);
}
/// <summary>
/// Carton put in suspence
/// </summary>
/// <param name="palletId"></param>
public void MarkCartonInSuspense(string palletId)
{
const string QUERY = @"
UPDATE <proxy />SRC_CARTON SC
SET SC.SUSPENSE_DATE = SYSDATE
WHERE SC.PALLET_ID = :PALLET_ID";
var binder = SqlBinder.Create().Parameter("PALLET_ID", palletId);
_db.ExecuteDml(QUERY, binder);
}
/// <summary>
/// Removes the all reserved cartons to pull against passed pallet.
/// So that those can be assigned to others.
/// </summary>
/// <param name="pullerName"></param>
/// <param name="palletId"></param>
internal int DiscardPalletSuggestion(string pullerName, string palletId)
{
const string QUERY = @"
DELETE <proxy />TEMP_PULL_CARTON
WHERE PALLET_ID = :PALLET_ID
AND OPERATOR_NAME = :OPERATOR_NAME
";
var binder = SqlBinder.Create();
binder.Parameter("PALLET_ID", palletId)
.Parameter("OPERATOR_NAME", pullerName);
return(_db.ExecuteDml(QUERY, binder));
}
/// <summary>
/// Returns the number of rows deleted
/// </summary>
/// <param name="style"></param>
/// <param name="color"></param>
/// <param name="sewingPlantId"></param>
/// <returns></returns>
public int DeleteSpotCheckSetting(string style, string color, string sewingPlantId)
{
const string QUERY = @"
DELETE FROM <proxy />MASTER_SEWINGPLANT_STYLE MS
WHERE MS.STYLE = :STYLE
AND MS.COLOR = :COLOR
AND MS.SEWING_PLANT_CODE = :SEWING_PLANT_CODE
";
var binder = SqlBinder.Create()
.Parameter("STYLE", string.IsNullOrEmpty(style) ? "." : style)
.Parameter("COLOR", string.IsNullOrEmpty(color) ? "." : color)
.Parameter("SEWING_PLANT_CODE", string.IsNullOrEmpty(sewingPlantId) ? "." : sewingPlantId);
//++_queryCount;
return(_db.ExecuteDml(QUERY, binder));
}
public bool MarkVasComplete(string ucc128Id)
{
const string QUERY = @"
INSERT INTO <proxy />BOX_VAS BP
(BOX_PROCESS_CODE, UCC128_ID, PROCESS_STATUS)
VALUES
('$CATALOG', :UCC128_ID, 'COMPLETED')
";
var binder = SqlBinder.Create()
.Parameter("UCC128_ID", ucc128Id);
var rowCount = _db.ExecuteDml(QUERY, binder);
++_queryCount;
return(rowCount > 0);
}