/// <summary>
/// This function restocks passed carton on the passed location
/// </summary>
/// <param name="carton">RestockCarton entity</param>
/// <param name="locationId"></param>
public void RestockCarton(RestockCarton carton, string locationId)
{
if (carton == null)
{
throw new ArgumentNullException("carton");
}
if (carton.SkuId == null)
{
throw new ArgumentNullException("carton.SkuId");
}
const string QUERY = @"
declare
LRelatedTransactionId NUMBER(10);
begin
<proxy />pkg_resv.add_to_ialoc2(alocation_id => :alocation,
asku_id => :asku_id,
apieces => :apieces);
LRelatedTransactionId := <proxy />pkg_inv_3.openctn(acarton_id => :acarton_id,
adestination_area => :adestination_area,
arelated_transaction_id => NULL);
end;
";
var binder = SqlBinder.Create();
binder.Parameter("alocation", locationId)
.Parameter("asku_id", carton.SkuId)
.Parameter("apieces", carton.PiecesInCarton)
.Parameter("acarton_id", carton.CartonId)
.Parameter("adestination_area", "SHL");
_db.ExecuteNonQuery(QUERY, binder);
}
/// <summary>
/// This method disables the VAS configuration for passed customer and VAS, on basis of all/selective non-validated orders .
/// </summary>
/// <param name="customerId"></param>
/// <param name="vasId"></param>
/// <param name="regExp"> </param>
/// <param name="currentOrdersOnly">
/// True: method will disable configuration from Current orders only.
/// False: method will disable configuration from All orders excluding Current orders.
/// Null: method will disable configuration from All orders.
/// </param>
internal void DisableVasConfiguration(string customerId, string vasId, string regExp, bool?currentOrdersOnly)
{
const string QUERY = @"
BEGIN
<if c= 'not($currentOrdersOnly) or $allOrders'>
UPDATE <proxy />MASTER_CUSTOMER_VAS MCV
SET MCV.INACTIVE_FLAG = 'Y'
WHERE MCV.CUSTOMER_ID = :CUSTOMER_ID
AND MCV.VAS_ID = :VAS_ID;
</if>
<if c= '$currentOrdersOnly or $allOrders'>
DELETE <proxy />PS_VAS T
WHERE T.PICKSLIP_ID IN
(SELECT DISTINCT(P.PICKSLIP_ID) AS PICKSLIP_ID
FROM <proxy />PS P
INNER JOIN <proxy />MASTER_CUSTOMER_VAS MCV
ON P.CUSTOMER_ID = MCV.CUSTOMER_ID
WHERE MCV.CUSTOMER_ID = :CUSTOMER_ID
AND MCV.VAS_ID = :VAS_ID
AND P.TRANSFER_DATE IS NULL
AND P.PICKSLIP_CANCEL_DATE IS NULL
AND REGEXP_LIKE(P.PO_ID || '@' || P.LABEL_ID, NVL(:REGEX_PATTERN,'.')));
</if>
END;
";
var binder = SqlBinder.Create()
.Parameter("CUSTOMER_ID", customerId)
.Parameter("VAS_ID", vasId)
.Parameter("REGEX_PATTERN", regExp);
binder.ParameterXPath("currentOrdersOnly", currentOrdersOnly != null && currentOrdersOnly.Value);
binder.ParameterXPath("allOrders", currentOrdersOnly == null);
_db.ExecuteNonQuery(QUERY, binder);
}
/// <summary>
/// This function places pallet.
/// </summary>
/// <param name="palletId"></param>
/// <param name="ucc128Id"></param>
/// <param name="bTemporaryPallet">
/// If bTemporaryPallet = true: We put box in suspense
/// otherwise we remove the box from suspense
/// </param>
/// <param name="isVasUi"> </param>
/// <returns>This is a void function</returns>
/// <remarks>
/// This function places passed box on passed pallet.
/// The box is put to suspense if the pallet is temporary pallet, otherwise it is removed from suspense.
/// If pallet is on location, updates the area and location of the box to that of the pallet.
/// </remarks>
public void PutBoxOnPallet(string ucc128Id, string palletId, bool bTemporaryPallet, bool isVasUi)
{
const string QUERY = @"
DECLARE
LLOCATION_ID <proxy />BOX.LOCATION_ID%TYPE;
LIA_ID <proxy />BOX.IA_ID%TYPE;
BEGIN
BEGIN
SELECT MAX(B.LOCATION_ID) AS LOCATION_ID, MAX(B.IA_ID) AS IA_ID
INTO LLOCATION_ID, LIA_ID
FROM <proxy />BOX B
INNER JOIN <proxy />PS PS
ON B.PICKSLIP_ID = PS.PICKSLIP_ID
WHERE B.PALLET_ID = :palletid
AND PS.TRANSFER_DATE IS NULL
AND B.STOP_PROCESS_DATE IS NULL
GROUP BY B.PALLET_ID;
EXCEPTION WHEN NO_DATA_FOUND THEN
NULL;
END;
-- We have found pallet location. Set the box location to be same.
IF LLOCATION_ID IS NOT NULL THEN
UPDATE <proxy />BOX B
SET B.PALLET_ID = :palletid,
B.LOCATION_ID = LLOCATION_ID,
B.IA_ID = NVL(LIA_ID,B.IA_ID),
B.IA_CHANGE_DATE = SYSDATE,
<if c='not($isVasUi)'>
B.SCAN_TO_PALLET_DATE = SYSDATE,
B.SCAN_TO_PALLET_BY = USER,
</if>
B.SUSPENSE_DATE = <if c='$temporarypallet'>SYSDATE</if><else>NULL</else>
WHERE B.UCC128_ID = :ucc128Id;
ELSE
-- Update the location as NULL
UPDATE <proxy />BOX B
SET B.PALLET_ID = :palletid,
B.LOCATION_ID = NULL,
<if c='not($isVasUi)'>
B.SCAN_TO_PALLET_DATE = SYSDATE,
B.SCAN_TO_PALLET_BY = USER,
</if>
B.SUSPENSE_DATE = <if c='$temporarypallet'>SYSDATE</if><else>NULL</else>
WHERE B.UCC128_ID = :ucc128Id;
END IF;
END;
";
Contract.Assert(_db != null);
var binder = SqlBinder.Create().Parameter("ucc128id", ucc128Id)
.Parameter("palletid", palletId);
binder.ParameterXPath("temporarypallet", bTemporaryPallet);
binder.ParameterXPath("isVasUi", isVasUi);
_db.ExecuteNonQuery(QUERY, binder);
++_queryCount;
}
/// <summary>
/// Load the pallet and capture productivity.
/// </summary>
/// <param name="palletId"></param>
public void LoadPallet(string palletId)
{
const string QUERY = @"
begin
<proxy />pkg_appointment.load_pallet(apallet_id => :PALLET_ID);
END;";
var binder = SqlBinder.Create().Parameter("PALLET_ID", palletId);
_db.ExecuteNonQuery(QUERY, binder);
}
public virtual ActionResult KillSession(UserSession model)
{
OracleDatastore db = null;
try
{
db = new OracleDatastore(this.HttpContext.Trace);
DbConnectionStringBuilder dcms8 = new DbConnectionStringBuilder();
dcms8.ConnectionString = ConfigurationManager.ConnectionStrings["dcms8"].ConnectionString;
// Creating the connection as super user
db.CreateConnection(dcms8.ConnectionString, string.Empty);
const string QUERY_ALTER_USER = "******";
var sql = string.Format(QUERY_ALTER_USER, model.SessionId, model.SerialNumber);
db.ExecuteNonQuery(sql, null);
AddStatusMessage(string.Format("Session of user {0} kill successfully", model.UserName));
}
catch (ProviderException ex)
{
ModelState.AddModelError("", ex.Message);
}
finally
{
if (db != null)
{
db.Dispose();
}
}
return(RedirectToAction(Actions.ManageUser(model.UserName)));
}
public int PrintCatalog(string ucc128Id, string printerId)
{
var count = 0;
const string QUERY = @"
BEGIN
:count := <proxy />FNC_PRINT_CATALOG_LABEL(AUCC128_ID => :ucc128Id, APRINTER_NAME => :printerId);
END;
";
var binder = SqlBinder.Create()
.Parameter("ucc128Id", ucc128Id)
.Parameter("printerId", printerId)
.OutParameter("count", val => count = val ?? 0);
_db.ExecuteNonQuery(QUERY, binder);
return(count);
}
/// <summary>
/// The priority of the passed SKU is increased for 30 minutes.
/// </summary>
/// <param name="buildingId"></param>
/// <param name="areaId"></param>
/// <param name="skuId"> </param>
/// <param name="userName"> </param>
/// <returns>
/// Returns the expiry time till when Sku Priority will be high.
/// If sku is not assigned at any location null will be returned.
/// </returns>
public DateTime?IncreaseSkuPriority(string buildingId, string areaId, int skuId, string userName)
{
//TODO: Get Priority
const string QUERY = @"
DECLARE
LPRIORITY NUMBER := <proxy />PKG_REPLENISH.P_HIGH_PRIORITY;
TYPE EXPIRY_LIST_T IS TABLE OF DATE;
EXPIRY_LIST EXPIRY_LIST_T;
LRESULT <proxy />PKG_REPLENISH.PRIORITY_INFO_REC;
BEGIN
LRESULT := <proxy />PKG_REPLENISH.SET_SKU_PRIORITY(ABUILDING_ID => :WAREHOUSE_LOCATION_ID,
AAREA_ID => :IA_ID,
ASKU_ID => :SKU_ID,
APRIORITY=> LPRIORITY,
AUSER_NAME => :USER_NAME);
:EXPIRY_TIME := LRESULT.EXPIRY_TIME;
END;
";
var binder = SqlBinder.Create();
DateTime?expiryTime = null;
binder.Parameter("SKU_ID", skuId)
.Parameter("IA_ID", areaId)
.Parameter("WAREHOUSE_LOCATION_ID", buildingId)
.Parameter("USER_NAME", userName);
binder.OutParameter("EXPIRY_TIME", p => expiryTime = p);
_db.ExecuteNonQuery(QUERY, binder);
return(expiryTime);
}
public void PrintBol(string parentShippingId, string printerid, int numberOfCopies)
{
const string QUERY = @"
DECLARE
Lresult number;
BEGIN
Lresult := <proxy />pkg_print_bol.write_bol_to_file(aparent_shipping_id => :aparent_shipping_id,
aprinter_name => :aprinter_name,
ano_of_copies => :ano_of_copies);
END;
";
var binder = SqlBinder.Create().Parameter("aparent_shipping_id", parentShippingId)
.Parameter("aprinter_name", printerid)
.Parameter("ano_of_copies", numberOfCopies);
_db.ExecuteNonQuery(QUERY, binder);
}
/// <summary>
/// Method will be called to accept the Carton against the Pallet passed
/// </summary>
/// <param name="palletId"></param>
/// <param name="cartonId"></param>
/// <param name="destArea"></param>
/// <param name="processId"></param>
public void ReceiveCarton(string palletId, string cartonId, string destArea, int?processId)
{
const string QUERY = @"
BEGIN
<proxy /> pkg_rec_2.receive_carton_2(
acarton_id => :acarton_id,
apallet_id => :apallet_id,
APROCESS_ID => :aprocess_id,
acarton_storage_area => :acarton_storage_area);
END;
";
var binder = SqlBinder.Create()
.Parameter("acarton_id", cartonId)
.Parameter("apallet_id", palletId)
.Parameter("acarton_storage_area", destArea)
.Parameter("aprocess_id", processId);
//++_queryCount;
_db.ExecuteNonQuery(QUERY, binder);
}
//This function is for carton printing
public void PrintCarton(string cartonId, string printerId)
{
Contract.Assert(_db != null);
const string QUERY = @"begin
<proxy />pkg_jf_src_2.pkg_jf_src_ctn_tkt(acarton_id => :acarton_id,
aprinter_name => :aprinter_name);
end;
";
var binder = SqlBinder.Create()
.Parameter("acarton_id", cartonId)
.Parameter("aprinter_name", printerId);
_db.ExecuteNonQuery(QUERY, binder);
}
/// <summary>
/// The password is changed to <paramref name="answer"/>. The password is set to expire immediately which will force the user to change password at next login.
/// </summary>
/// <param name="username">Name of the user need to reset password</param>
/// <param name="answer">The new password, or empty to randomply generate a password</param>
/// <returns>This function will return the new assigned password</returns>
/// <remarks>
/// <para>
/// The logged in user must have the rights for resetting password of a user. Following is the script.
/// </para>
/// <code>
/// <![CDATA[
/// GRANT ALTER USER TO <user-name>;
/// ]]>
/// </code>
///</remarks>
///
public override string ResetPassword(string username, string answer)
{
var rand = new Random();
if (string.IsNullOrEmpty(answer))
{
answer = rand.Next(1, (int)Math.Pow(10, this.MinRequiredPasswordLength) - 1).ToString().PadRight(this.MinRequiredPasswordLength, '1');
}
if (string.IsNullOrWhiteSpace(username))
{
throw new ArgumentNullException("username");
}
if (HttpContext.Current == null || string.IsNullOrWhiteSpace(HttpContext.Current.User.Identity.Name))
{
throw new ProviderException("You must be logged in with proper credentials for resetting a user's password");
}
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionString, HttpContext.Current.User.Identity.Name);
try
{
var sqlQuery = string.Format("ALTER USER {0} IDENTIFIED BY \"{1}\" PASSWORD EXPIRE", username, answer);
db.ExecuteNonQuery(sqlQuery, null);
return(answer);
}
catch (OracleDataStoreException ex)
{
switch (ex.OracleErrorNumber)
{
case 1935:
//1935: missing user or role name (comes when username is null). Not expected as we are already checking the passed user.
case 922:
//922: Missing or invalid option (comes when username contains special chars or whitespace)
throw new ProviderException("User name is invalid", ex);
case 1031:
//1031: insufficient privileges
throw new ProviderException("You do not have sufficient privileges for resetting password.", ex);
default:
throw;
}
}
}
}
/// <summary>
/// This function is for deleting an existing user.
/// </summary>
/// <param name="username"></param>
/// <param name="deleteAllRelatedData">Ignored</param>
/// <returns>This function will return True if user successfully deleted else return False</returns>
/// <remarks>
/// <para>
/// The logged in user must have the rights to drop a User. Following is the script.
/// </para>
/// <code>
/// <![CDATA[
/// GRANT DROP USER To <user-name>;
/// ]]>
/// </code>
/// </remarks>
public override bool DeleteUser(string username, bool deleteAllRelatedData)
{
if (string.IsNullOrWhiteSpace(username))
{
throw new ArgumentNullException("username");
}
if (HttpContext.Current == null || string.IsNullOrWhiteSpace(HttpContext.Current.User.Identity.Name))
{
throw new ProviderException("You must be logged in with proper credentials for deleting a user");
}
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionString, HttpContext.Current.User.Identity.Name);
try
{
var sqlQuery = string.Format("DROP USER {0}", username);
db.ExecuteNonQuery(sqlQuery, null);
return(true);
}
catch (OracleDataStoreException ex)
{
switch (ex.OracleErrorNumber)
{
case 1031:
//1031: insufficient privileges
throw new ProviderException("You do not have sufficient privileges for deleting a user.", ex);
case 1918:
//1918: user does not exist
throw new ProviderException(string.Format("User {0} does not exits", username), ex);
case 921:
//921: invalid username
throw new ProviderException("User name is invalid", ex);
case 1940:
//1940: Already logged in user is trying to delete itself.
throw new ProviderException("Cannot drop a user that is currently connected");
default:
throw;
}
}
}
}
public void PrintPackingSlip(long pickslipId, bool printMasterPackingslip, bool printPackingSlip, bool printAllPackingslip, string printerid, int numberOfCopies = 1)
{
const string QUERY = @"
DECLARE
CURSOR BOX_CUR IS
SELECT BOX.UCC128_ID
FROM <proxy />BOX
WHERE BOX.Pickslip_Id = :apickslip_id
<if c='$PrintAllPackingslip=""1""'> AND BOX.UCC128_ID = :UCC128_ID </if>
ORDER BY LPAD((BOX.BOX_ID), 4, 0) DESC;
Lresult number;
BEGIN
<if c='$PrintPackingSlip=""1""'>
FOR BOX_REC IN BOX_CUR LOOP
Lresult := <proxy />pkg_print_pack.write_pspb_to_file(aucc128_id => BOX_REC.UCC128_ID,
aprinter_name => :aprinter_name,
acopies => :acopies,
aoptions => null);
END LOOP;
</if>
<if c='$PrintMasterPackingslip=""1""'>
Lresult :=<proxy />pkg_print_pack.write_mps_to_file(apickslip_id => :apickslip_id,
aprinter_name => :aprinter_name,
acopies => :acopies,
aoptions => null);
</if>
END;
";
var binder = SqlBinder.Create().Parameter("apickslip_id", pickslipId)
.Parameter("PrintAllPackingslip", printAllPackingslip == false ? "1" : "0")
.Parameter("PrintPackingSlip", printPackingSlip == true ? "1" : "0")
.Parameter("aprinter_name", printerid)
.Parameter("acopies", numberOfCopies)
.Parameter("PrintMasterPackingslip", printMasterPackingslip == true ? "1" : "0");
_db.ExecuteNonQuery(QUERY, binder);
}
/// <summary>
/// This method is use for find ctnresvId
/// </summary>
/// <param name="reqId"></param>
/// <returns></returns>
// public string GetCtnRevId(string reqId)
// {
// const string QUERY =
// @" select c.ctn_resv_id
// from <proxy />ctnresv c where c.ctn_resv_id=:dcms4_req_id";
// var binder = SqlBinder.Create(row => row.GetString("ctn_resv_id")).Parameter("dcms4_req_id", reqId);
// return _db.ExecuteSingle(QUERY, binder);
// return reqId;
// }
/// <summary>
/// Creates a new request and returns the request id.
/// 25-1-2012:Insert IS_CONVERSION_REQUEST colomn value in table when request is for conversion.
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public void CreateCartonRequest(PullRequest model)
{
//TODO: remove hardwirings of Module Code
const string QUERY = @"
declare
Lresv_rec <proxy />pkg_ctnresv.resv_rec_type;
begin
Lresv_rec.ctn_resv_id := :resv_id;
Lresv_rec.source_area := :source_area;
Lresv_rec.destination_area := :destination_area;
Lresv_rec.pieces_constraint := :pieces_constraint;
Lresv_rec.vwh_id := :source_vwh_id;
Lresv_rec.conversion_vwh_id := :conversion_vwh_id;
Lresv_rec.priority := :priority;
Lresv_rec.quality_code := :quality_code;
Lresv_rec.target_quality := :target_quality;
Lresv_rec.module_code := 'REQ2';
Lresv_rec.warehouse_location_id := :warehouse_location_id;
Lresv_rec.price_season_code := :price_season_code;
Lresv_rec.sewing_plant_code := :sewing_plant_code;
Lresv_rec.receive_date := :receive_date;
Lresv_rec.is_conversion_request := :is_conversion_request;
Lresv_rec.remarks := :remarks;
:ctn_resv_id := <proxy />pkg_ctnresv.create_resv_id(aresv_rec => Lresv_rec);
end;";
var binder = SqlBinder.Create()
.Parameter("source_area", model.SourceAreaId)
.Parameter("destination_area", model.DestinationArea)
.Parameter("pieces_constraint", model.AllowOverPulling)
.Parameter("source_vwh_id", model.SourceVwhId)
.Parameter("conversion_vwh_id", model.TargetVwhId)
.Parameter("priority", model.Priority)
.Parameter("quality_code", model.SourceQuality)
.Parameter("target_quality", model.TargetQuality)
.Parameter("warehouse_location_id", model.BuildingId)
//.Parameter("packaging_preference", model.PackagingPreferance)
.Parameter("price_season_code", model.PriceSeasonCode)
.Parameter("sewing_plant_code", model.SewingPlantCode)
.Parameter("remarks", model.Remarks)
.Parameter("receive_date", model.CartonReceivedDate)
.Parameter("resv_id", model.CtnResvId)
.Parameter("is_conversion_request", model.IsConversionRequest ? "Y" : "")
.OutParameter("ctn_resv_id", val => model.CtnResvId = val)
;
_db.ExecuteNonQuery(QUERY, binder);
return;
}
/// <summary>
/// Returns true if the setting was newly inserted. Else returns false.
/// </summary>
/// <param name="style"></param>
/// <param name="color"></param>
/// <param name="sewingPlantId"></param>
/// <param name="spotCheckPercent"></param>
/// <param name="enabled"></param>
/// <returns></returns>
public bool AddUpdateSpotCheckSetting(string style, string color, string sewingPlantId, int?spotCheckPercent, bool enabled)
{
const string QUERY = @"
BEGIN
UPDATE <proxy />MASTER_SEWINGPLANT_STYLE MS
SET MS.SPOTCHECK_PERCENT = :SPOTCHECK_PERCENT,
MS.SPOTCHECK_FLAG=:SPOTCHECK_FLAG
WHERE MS.STYLE = :STYLE
AND MS.SEWING_PLANT_CODE = :SEWING_PLANT_CODE
AND MS.COLOR= :COLOR;
IF SQL%ROWCOUNT = 0 THEN
INSERT INTO <proxy />MASTER_SEWINGPLANT_STYLE MS
(MS.STYLE,
MS.COLOR,
MS.SEWING_PLANT_CODE,
MS.SPOTCHECK_PERCENT,
MS.SPOTCHECK_FLAG
)
VALUES (:STYLE,
:COLOR,
:SEWING_PLANT_CODE,
:SPOTCHECK_PERCENT,
:SPOTCHECK_FLAG);
:inserted := 'Y';
END IF;
END;
";
string inserted = string.Empty;
var binder = SqlBinder.Create()
.Parameter("STYLE", string.IsNullOrEmpty(style)? ".": style)
.Parameter("COLOR", string.IsNullOrEmpty(color) ? "." : color)
.Parameter("SEWING_PLANT_CODE", string.IsNullOrEmpty(sewingPlantId) ? "." : sewingPlantId)
.Parameter("SPOTCHECK_PERCENT", spotCheckPercent)
.Parameter("SPOTCHECK_FLAG", enabled ? "Y" : "")
.OutParameter("inserted", val => inserted = val);
_db.ExecuteNonQuery(QUERY, binder);
return(inserted == "Y");
}
/// <summary>
/// This function is for un-locking a locked user account.
/// </summary>
/// <param name="userName"></param>
/// <remarks>
/// <para>
/// The logged in user must have the rights for resetting password of a user. Following is the script.
/// </para>
/// <code>
/// <![CDATA[
/// GRANT ALTER USER TO <user-name>;
/// ]]>
/// </code>
///</remarks>
/// <returns>This function will return True on successful unlock else return False</returns>
public override bool UnlockUser(string userName)
{
if (string.IsNullOrWhiteSpace(userName))
{
throw new ArgumentNullException("userName");
}
if (HttpContext.Current == null || string.IsNullOrWhiteSpace(HttpContext.Current.User.Identity.Name))
{
throw new ProviderException("You must be logged in with proper credentials for un locking a user account");
}
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionString, HttpContext.Current.User.Identity.Name);
try
{
var sqlQuery = string.Format("ALTER USER {0} ACCOUNT UNLOCK", userName);
db.ExecuteNonQuery(sqlQuery, null);
return(true);
}
catch (OracleDataStoreException ex)
{
switch (ex.OracleErrorNumber)
{
case 1031:
//1031: insufficient privileges
throw new ProviderException("You do not have sufficient privileges for unlocking a locked user account.", ex);
case 1918:
//1918: user does not exist
throw new ProviderException(string.Format("User {0} does not exits", userName), ex);
default:
throw;
}
}
}
}
public virtual ActionResult LockedUser(ManageUserViewModel model)
{
if (string.IsNullOrWhiteSpace(model.User.UserName))
{
throw new ArgumentNullException("userName");
}
OracleDatastore db = null;
try
{
db = new OracleDatastore(this.HttpContext.Trace);
DbConnectionStringBuilder dcms8 = new DbConnectionStringBuilder();
dcms8.ConnectionString = ConfigurationManager.ConnectionStrings["dcms8"].ConnectionString;
// Creating the connection as super user
db.CreateConnection(dcms8.ConnectionString, string.Empty);
const string QUERY_ALTER_USER = "******";
var sql = string.Format(QUERY_ALTER_USER, model.User.UserName);
db.ExecuteNonQuery(sql, null);
AddStatusMessage(string.Format("{0} user account has been locked", model.User.UserName));
}
catch (ProviderException ex)
{
ModelState.AddModelError("", ex.Message);
}
finally
{
if (db != null)
{
db.Dispose();
}
}
return(RedirectToAction(Actions.ManageUser(model.User.UserName)));
}
/// <summary>
/// The password change will succeed only if the old password is valid.
/// </summary>
/// <param name="username"></param>
/// <param name="oldPassword"></param>
/// <param name="newPassword"></param>
/// <returns>true if password successfully changed. false if the old password is invalid</returns>
/// <remarks>
/// Any data base exception encountered will be propagated to the caller.
/// Sharad 15 Feb 2012: Supported voluntary changes of passwords. Earlier only expired passwords could be changed.
/// Sharad 21 Feb 2012: Raising ValidatingPassword event
/// </remarks>
public override bool ChangePassword(string username, string oldPassword, string newPassword)
{
if (string.IsNullOrWhiteSpace(username))
{
throw new ArgumentNullException("username");
}
if (string.IsNullOrWhiteSpace(oldPassword))
{
throw new ArgumentNullException("oldPassword");
}
if (string.IsNullOrWhiteSpace(newPassword))
{
throw new ArgumentNullException("newPassword");
}
var e = new ValidatePasswordEventArgs(username, newPassword, true);
OnValidatingPassword(e);
if (e.Cancel)
{
// App decided to cancel user creation
return(false);
}
var builder = new OracleConnectionStringBuilder(_connectionString)
{
UserID = username,
Password = oldPassword,
Pooling = false,
ProxyUserId = string.Empty,
ProxyPassword = string.Empty
};
// Try to login as passed user with old password to ensure that the old password is valid
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
var msg = string.Format("Opening connection to {0} for user {1}",
builder.DataSource, builder.UserID);
Trace.WriteLine(msg, "OracleMembershipProvider");
db.CreateConnection(builder.ConnectionString, builder.UserID);
Trace.WriteLine(msg, "Opening connection with old password");
try
{
db.Connection.Open();
}
catch (OracleException ex)
{
switch (ex.Number)
{
case 1017:
// Invalid user name password
Trace.TraceWarning("Invalid password specified for user {0}", username);
return(false);
case 28001:
// If we are using ODP.NET, we can change the password now
// This will only work if the user's password has expired
Trace.WriteLine(msg, "Password expired error oracle exception encountered");
db.Connection.OpenWithNewPassword(newPassword);
return(true);
default:
throw;
}
}
// If we get here, the old password was valid. Now we will change the password
//REPLACE is used to remove exception ORA-28221
Trace.WriteLine(msg, "Executing ALTER USER with new password");
var query = string.Format("ALTER USER {0} IDENTIFIED BY \"{1}\" REPLACE \"{2}\"", username, newPassword, oldPassword);
db.ExecuteNonQuery(query, null);
}
return(true);
}
/// <summary>
/// Updates quality,SKU,pieces of the carton.
/// </summary>
/// <param name="carton"></param>
/// <param name="updateFlags"></param>
/// <param name="reasonCode"></param>
public void UpdateCarton(Carton carton, CartonUpdateFlags updateFlags, string reasonCode)
{
const string QUERY = @"
DECLARE
Lsku_rec <proxy />pkg_inv_3.SKU_REC;
LRelated_TRansaction_Id NUMBER(10);
BEGIN
Lsku_rec.sku_id := :trgSKU;
Lsku_rec.vwh_id := :trgVwh_id;
Lsku_rec.quality_code := :trgQuality;
LRelated_TRansaction_Id := <proxy />pkg_inv_3.editcarton(acarton_id => :cartonId,
atarget_sku => Lsku_rec,
anew_pieces => :trgPieces,
arelated_transaction_id => NULL,
areason_code => :reasonCode );
<if c='$priceseasoncode'>
UPDATE <proxy />SRC_CARTON
SET PRICE_SEASON_CODE = :priceseasoncode
WHERE CARTON_ID = :cartonId;
</if>
<if c='$completeRework'>
begin
LRelated_TRansaction_Id := <proxy />pkg_carton_work_2.mark_work_complete(acarton_id => :cartonId,
arelated_transaction_id => NULL);
end;
</if>
<if c='$abandonRework'>
begin
LRelated_TRansaction_Id := <proxy />pkg_carton_work_2.undo_work(acarton_id => :cartonId,
arelated_transaction_id => NULL);
end;
</if>
UPDATE <proxy />SRC_CARTON_DETAIL SCD
SET SCD.REQ_PROCESS_ID = NULL,
SCD.REQ_MODULE_CODE= NULL,
SCD.REQ_LINE_NUMBER =NULL
WHERE SCD.CARTON_ID =:cartonId;
UPDATE <proxy />SRC_CARTON SC
SET SUSPENSE_DATE = NULL
WHERE SC.CARTON_ID = :cartonId;
END; ";
if (updateFlags.HasFlag(CartonUpdateFlags.MarkReworkComplete) && updateFlags.HasFlag(CartonUpdateFlags.AbandonRework))
{
throw new ProviderException("Mark rework complete and abandon rework can not be performed on same carton. ");
}
var binder = SqlBinder.Create().Parameter("cartonId", carton.CartonId)
.Parameter("trgVwh_id", updateFlags.HasFlag(CartonUpdateFlags.Vwh) ? carton.VwhId : null)
.Parameter("trgSKU", updateFlags.HasFlag(CartonUpdateFlags.Sku) ? (int?)carton.SkuInCarton.SkuId : null)
.Parameter("trgPieces", updateFlags.HasFlag(CartonUpdateFlags.Pieces) ? (int?)carton.Pieces : null)
.Parameter("reasonCode", reasonCode)
.Parameter("trgQuality", updateFlags.HasFlag(CartonUpdateFlags.Quality) ? carton.QualityCode : null)
.Parameter("completeRework", updateFlags.HasFlag(CartonUpdateFlags.MarkReworkComplete) ? carton.CartonId : null)
.Parameter("abandonRework", updateFlags.HasFlag(CartonUpdateFlags.AbandonRework) ? carton.CartonId : null)
.Parameter("priceseasoncode", updateFlags.HasFlag(CartonUpdateFlags.PriceSeasonCode) ? carton.PriceSeasonCode : null);
_db.ExecuteNonQuery(QUERY, binder);
}
/// <summary>
/// This function is for granting the passed roles to the passed users.
/// </summary>
/// <param name="usernames"></param>
/// <param name="roleNames"></param>
/// <remarks>
/// <para>
/// The logged in user must have the rights to add roles. The logged in user must also have the insert rights to add upriv.
/// Following are the scripts.
/// </para>
/// <code>
/// <![CDATA[
/// grant GRANT ANY ROLE to <user-name>
/// grant INSERT on URPIV to <user-name>
/// ]]>
/// </code>
/// </remarks>
public override void AddUsersToRoles(string[] usernames, string[] roleNames)
{
if (usernames == null)
{
throw new ArgumentNullException("usernames");
}
if (roleNames == null)
{
throw new ArgumentNullException("roleNames");
}
if (HttpContext.Current == null || string.IsNullOrWhiteSpace(HttpContext.Current.User.Identity.Name))
{
throw new ProviderException("You must be logged in with proper credentials to add role to a user");
}
var joinUsersRoles = from user in usernames
from role in roleNames
where !string.IsNullOrEmpty(user) &&
!string.IsNullOrEmpty(role)
select new
{
Role = role.Trim().ToUpper(),
User = user.Trim().ToUpper()
};
const string QUERY_PRIV = @"INSERT INTO <proxy />UPRIV (PRIV_ID,ORACLE_USER_NAME) VALUES ('{1}','{0}')";
const string QUERY_ROLE = @"GRANT {1} to {0}";
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionStringBuilder.ConnectionString, HttpContext.Current.User.Identity.Name);
//var binder = new SqlBinder<string>("Granting Roles.");
var binder = SqlBinder.Create();
foreach (var item in joinUsersRoles)
{
var sqlQuery = string.Format(IsRolePriv(item.Role) ? QUERY_PRIV : QUERY_ROLE, item.User, item.Role);
ClearRoleCache(item.User);
try
{
db.ExecuteNonQuery(sqlQuery, null);
}
catch (OracleDataStoreException ex)
{
switch (ex.OracleErrorNumber)
{
case 1919:
case 942:
case 1031:
// 1919: Role does not exist
// 942 : UPRIV table does not exist. To us this means no rights to insert into table UPRIV
//1031 : Rights to insert the upriv are not avaliable
throw new ProviderException(string.Format("Role {0} does not exist. This could also mean that you do not have rights to grant this role", item.Role));
case 1917:
throw new ProviderException(string.Format("At least one of Role {0} or User {1} is invalid", item.Role, item.User));
case 1:
//Priv already assigned to the user(UNIQUE CONSTRAINT VOILATED) remain silent and move further.
continue;
default:
throw;
}
}
}
}
}
/// <summary>
/// This function is for revoking the passed roles from the passed users.
/// </summary>
/// <param name="usernames"></param>
/// <param name="roleNames"></param>
/// <remarks>
/// <para>
/// Empty user names and roles are silently ignored. All user names and roles are converted to upper case before they are processed.
/// </para>
/// <para>
/// The logged in user must have the rights to revoke roles. The logged in user must also have the delete rights on table upriv to delete user's priv.
/// Follwing are the scripts.
/// </para>
/// <code>
/// <![CDATA[
/// grant GRANT ANY ROLE to <user-name>
/// grant DELETE on URPIV to <user-name>
/// ]]>
/// </code>
/// </remarks>
public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
{
if (usernames == null)
{
throw new ArgumentNullException("usernames");
}
if (roleNames == null)
{
throw new ArgumentNullException("roleNames");
}
if (HttpContext.Current == null || string.IsNullOrWhiteSpace(HttpContext.Current.User.Identity.Name))
{
throw new ProviderException("You must be logged in with proper credentials to remove roles from users");
}
var joinUsersRoles = from user in usernames
from role in roleNames
where !string.IsNullOrEmpty(user) &&
!string.IsNullOrEmpty(role)
select new
{
Role = role.Trim().ToUpper(),
User = user.Trim().ToUpper()
};
const string QUERY_PRIV = @"DELETE <proxy />UPRIV WHERE ORACLE_USER_NAME ='{0}' AND PRIV_ID ='{1}'";
const string QUERY_ROLE = @"REVOKE {1} FROM {0}";
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionStringBuilder.ConnectionString, HttpContext.Current.User.Identity.Name);
foreach (var item in joinUsersRoles)
{
var query = string.Format(IsRolePriv(item.Role) ? QUERY_PRIV : QUERY_ROLE, item.User, item.Role);
ClearRoleCache(item.User);
try
{
db.ExecuteNonQuery(query, null);
}
catch (OracleDataStoreException ex)
{
switch (ex.OracleErrorNumber)
{
case 1919:
case 942:
case 1031:
// 1919: Role does not exist
// 942 : UPRIV table does not exist. To us this means no rights to delete from UPRIV
// 1031: Rights to revoke the role are not avaliable.
throw new ProviderException(string.Format("Role {0} does not exist. This could also mean that you do not have rights to revoke this role", item.Role));
case 1951:
// Role not granted. Check whether the role has been granted inderectly.
const string QUERY_ROLE_PATH = @"
WITH Q1(GRANTED_ROLE,
PATH) AS
(SELECT P.GRANTED_ROLE, CAST(U.USERNAME AS VARCHAR2(2000))
FROM DBA_ROLE_PRIVS P
INNER JOIN DBA_USERS U
ON P.GRANTEE = U.USERNAME
UNION ALL
SELECT P.GRANTED_ROLE, CAST(Q1.PATH || '/' || P.GRANTEE AS VARCHAR2(2000))
FROM DBA_ROLE_PRIVS P
INNER JOIN Q1
ON Q1.GRANTED_ROLE = P.GRANTEE
LEFT OUTER JOIN DBA_USERS U
ON P.GRANTEE = U.USERNAME
WHERE U.USERNAME IS NULL)
SELECT substr(path, instr(path, '/') + 1)
FROM Q1 Q
WHERE Q.PATH LIKE :username || '/%'
and q.granted_role = :rolename
";
// Execute this query as super user
db.CreateConnection(_connectionStringBuilder.ConnectionString, string.Empty);
//var binder = new SqlBinder<string>("Get Role Path");
var binder = SqlBinder.Create(row => row.GetString(0));
//binder.CreateMapper(QUERY_ROLE_PATH);
//binder.Query = QUERY_ROLE_PATH;
//binder.Factory = row => row.GetString();
binder.Parameter("username", item.User);
binder.Parameter("rolename", item.Role);
var path = db.ExecuteSingle<string>(QUERY_ROLE_PATH, binder);
if (!string.IsNullOrEmpty(path))
{
var roleToRevoke = path.Split('/').First();
throw new ProviderException(
string.Format(
"Role {0} has indirectly granted to user {1} and cannot be revoked directly. {2}/{0}. To revoke {0} role revoke {3} role.",
item.Role, item.User, path, roleToRevoke));
}
throw new ProviderException(ex.Message);
case 1917:
throw new ProviderException(string.Format("At least one of Role {0} or User {1} is invalid", item.Role, item.User));
default:
throw;
}
}
}
}
}
/// <summary>
/// Raises exception if carton is not located.
/// </summary>
/// <param name="cartonId"></param>
/// <param name="destAreaId"> </param>
/// <param name="destLocationId"></param>
/// <param name="locationTravelSequence"> </param>
/// <param name="destPalletId"></param>
/// <param name="destBuildingId"> </param>
/// <returns>Item 1: Previous location of the carton. Item 2: true if the carton is invalid</returns>
/// <remarks>
/// Exception raised for Forinvalid carton 20002.
/// Location is considered invalid if it does not exist in database or unavailable flag is set.
/// </remarks>
public Tuple <string, bool> LocateCarton(string cartonId, string destBuildingId, string destAreaId, string destLocationId, int?locationTravelSequence, string destPalletId)
{
const string QUERY = @"
DECLARE
LCARTON_STORAGE_AREA SRC_CARTON.CARTON_STORAGE_AREA%TYPE;
LVWH_ID SRC_CARTON.VWH_ID%TYPE;
LUPC_CODE MASTER_SKU.UPC_CODE%TYPE;
LQUANTITY SRC_CARTON_DETAIL.QUANTITY%TYPE;
LRow_id ROWID;
BEGIN
BEGIN
SELECT SC.CARTON_STORAGE_AREA,
SC.VWH_ID,
SC.LOCATION_ID,
M.UPC_CODE,
SCD.QUANTITY,
sc.rowid
INTO LCARTON_STORAGE_AREA, LVWH_ID, :LCARTON_LOCATION, LUPC_CODE, LQUANTITY, LRow_id
FROM <proxy />SRC_CARTON SC
INNER JOIN <proxy />SRC_CARTON_DETAIL SCD
ON SCD.CARTON_ID = SC.CARTON_ID
INNER JOIN <proxy />MASTER_SKU M
ON M.SKU_ID = SCD.SKU_ID
WHERE SC.CARTON_ID = :CARTON_ID AND ROWNUM < 2
FOR UPDATE OF sc.SUSPENSE_DATE NOWAIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
--RAISE_APPLICATION_ERROR(-20200, 'Invalid carton');
:INVALID_CARTON := 'Y';
RETURN;
END;
UPDATE <proxy />SRC_CARTON SC
SET SC.SUSPENSE_DATE = NULL,
SC.CARTON_STORAGE_AREA = :CARTON_STORAGE_AREA,
sc.pallet_id =null,
SC.location_id = :LOCATION_ID
WHERE SC.rowid = LRow_id;
IF SQL%ROWCOUNT != 1 THEN
RAISE_APPLICATION_ERROR(-20100, 'Internal error. Carton just selected could not be updated.');
END IF;
INSERT INTO <proxy />CARTON_PRODUCTIVITY
(
MODULE_CODE,
ACTION_CODE,
PROCESS_START_DATE,
PROCESS_END_DATE,
CARTON_ID,
PALLET_ID,
UPC_CODE,
CARTON_QUANTITY,
CARTON_SOURCE_AREA,
CARTON_DESTINATION_AREA,
AISLE,
WAREHOUSE_LOCATION_ID,
VWH_ID)
values(
'LOC',
'LOC',
SYSDATE,
SYSDATE,
:CARTON_ID,
:PALLET_ID,
LUPC_CODE,
LQUANTITY,
LCARTON_STORAGE_AREA,
:CARTON_STORAGE_AREA,
:travel_sequence,
:WAREHOUSE_LOCATION_ID,
LVWH_ID );
END;
";
string cartonLocation = string.Empty;
bool bInvalidCarton = true;
var binder = SqlBinder.Create().Parameter("CARTON_ID", cartonId)
.Parameter("LOCATION_ID", destLocationId)
.Parameter("PALLET_ID", destPalletId)
.Parameter("CARTON_STORAGE_AREA", destAreaId)
.Parameter("travel_sequence", locationTravelSequence)
.Parameter("WAREHOUSE_LOCATION_ID", destBuildingId)
.OutParameter("LCARTON_LOCATION", p => cartonLocation = p)
.OutParameter("INVALID_CARTON", p => bInvalidCarton = p == "Y")
;
_db.ExecuteNonQuery(QUERY, binder);
return(Tuple.Create(cartonLocation, bInvalidCarton));
}
/// <summary>
/// Locates all the cartons on pallet to passed locationId
/// If the parameter mergeOnPallet is not null then we merge the passed pallet also
/// </summary>
/// <param name="locationId"></param>
/// <param name="palletId"></param>
/// <param name="areaId"></param>
/// <param name="mergeOnPallet"></param>
/// <remarks>
/// We insert productivity info also using this function.
/// Action code is PLOC.
/// Sharad 24 Jan 2012: Set suspense date of each carton to null, because cartons are no longer in suspense
/// </remarks>
public void LocatePallet(string locationId, string palletId, string areaId, string mergeOnPallet)
{
const string QUERY = @"
DECLARE
LQUANTITY <proxy />SRC_CARTON_DETAIL.QUANTITY%TYPE;
LSOURCE_AREA <proxy />SRC_CARTON.CARTON_STORAGE_AREA%TYPE;
LVWH_ID <proxy />SRC_CARTON.VWH_ID%TYPE;
LWAREHOUSE_LOCATION_ID <proxy />TAB_INVENTORY_AREA.WAREHOUSE_LOCATION_ID%TYPE;
LTRAVEL_SEQUENCE <proxy />MASTER_STORAGE_LOCATION.TRAVEL_SEQUENCE%TYPE;
BEGIN
SELECT SUM(SD.QUANTITY) AS QUANTITY,
MAX(S.CARTON_STORAGE_AREA) AS SOURCE_AREA,
MAX(S.VWH_ID) AS VWH_ID,
MAX(TIA.WAREHOUSE_LOCATION_ID) AS WAREHOUSE_LOCATION_ID,
MAX((SELECT MSL.TRAVEL_SEQUENCE
FROM <proxy />MASTER_STORAGE_LOCATION MSL
WHERE MSL.LOCATION_ID = :LOCATION_ID)) AS TRAVEL_SEQUENCE
INTO LQUANTITY,
LSOURCE_AREA,
LVWH_ID,
LWAREHOUSE_LOCATION_ID,
LTRAVEL_SEQUENCE
FROM <proxy />SRC_CARTON S
INNER JOIN <proxy />SRC_CARTON_DETAIL SD
ON S.CARTON_ID = SD.CARTON_ID
INNER JOIN <proxy />MASTER_SKU MS
ON MS.SKU_ID = SD.SKU_ID
LEFT OUTER JOIN <proxy />TAB_INVENTORY_AREA TIA
ON TIA.INVENTORY_STORAGE_AREA = S.CARTON_STORAGE_AREA
WHERE S.PALLET_ID = :PALLET_ID
GROUP BY S.PALLET_ID;
INSERT INTO <proxy />CARTON_PRODUCTIVITY
(PRODUCTIVITY_ID,
MODULE_CODE,
ACTION_CODE,
PROCESS_START_DATE,
PROCESS_END_DATE,
PALLET_ID,
CARTON_QUANTITY,
CARTON_SOURCE_AREA,
CARTON_DESTINATION_AREA,
AISLE,
VWH_ID,
WAREHOUSE_LOCATION_ID)
VALUES
(PRODUCTIVITY_SEQUENCE.NEXTVAL,
:MODULE_CODE,
:ACTION_CODE,
SYSDATE,
SYSDATE,
:PALLET_ID,
LQUANTITY,
LSOURCE_AREA,
:AREA_ID,
LTRAVEL_SEQUENCE,
LVWH_ID,
LWAREHOUSE_LOCATION_ID);
UPDATE <proxy />SRC_CARTON SC
SET SC.LOCATION_ID = :LOCATION_ID,
<if> SC.pallet_id = :MERGE_ON_PALLET,</if>
SC.CARTON_STORAGE_AREA = :AREA_ID,
sc.suspense_date = null
WHERE SC.PALLET_ID = :PALLET_ID;
END;
";
var binder = new SqlBinder("LocatePallet")
.Parameter("PALLET_ID", palletId)
.Parameter("AREA_ID", areaId)
.Parameter("LOCATION_ID", locationId)
.Parameter("MERGE_ON_PALLET", mergeOnPallet)
.Parameter("MODULE_CODE", MODULE_CODE)
.Parameter("ACTION_CODE", ACTION_CODE);
++_queryCount;
_db.ExecuteNonQuery(QUERY, binder);
}
/// <summary>
/// This function is for creating a new user.
/// </summary>
/// <param name="username"></param>
/// <param name="password"></param>
/// <param name="email">Ignored</param>
/// <param name="passwordQuestion">Ignored</param>
/// <param name="passwordAnswer">Ignored</param>
/// <param name="isApproved">Ignored</param>
/// <param name="providerUserKey">Ignored</param>
/// <param name="status">
/// <para>
/// Can return InvalidUserName, DuplicateUserName, InvalidPassword or Success
/// </para>
/// </param>
/// <returns>User object when <paramref name="status"/> = Success; null otherwise. </returns>
/// <remarks>
/// <para>
/// The user is always created with an expired password. The default profile is assigned to the user. CONNECT THROUGH rights are given to the proxy user.
/// </para>
/// <para>
/// The logged in user must have the rights to crete User. Following is the script.
/// </para>
/// <code>
/// <![CDATA[
/// GRANT CREATE USER TO <user-name>
/// ]]>
/// </code>
/// </remarks>
public override MembershipUser CreateUser(string username, string password, string email, string passwordQuestion, string passwordAnswer,
bool isApproved, object providerUserKey, out MembershipCreateStatus status)
{
if (string.IsNullOrWhiteSpace(username))
{
throw new ArgumentNullException("username");
}
if (string.IsNullOrWhiteSpace(password))
{
throw new ArgumentNullException("password");
}
var e = new ValidatePasswordEventArgs(username, password, true);
OnValidatingPassword(e);
if (e.Cancel)
{
// App decided to cancel user creation
status = MembershipCreateStatus.InvalidPassword;
return(null);
}
if (HttpContext.Current == null || string.IsNullOrWhiteSpace(HttpContext.Current.User.Identity.Name))
{
throw new MembershipCreateUserException("You must be logged in with proper credentials to create a user");
}
EnsureDefaultProfile();
//var builder = new OracleConnectionStringBuilder(_connectionString);
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionString, HttpContext.Current.User.Identity.Name);
try
{
var sqlQuery = string.Format("CREATE USER {0} IDENTIFIED BY \"{1}\" PROFILE {2} PASSWORD EXPIRE", username, password, _visibleProfiles[0]);
db.ExecuteNonQuery(sqlQuery, null);
foreach (var proxy in _proxyUsers)
{
sqlQuery = string.Format("ALTER USER {0} GRANT CONNECT THROUGH {1}", username, proxy);
db.ExecuteNonQuery(sqlQuery, null);
}
status = MembershipCreateStatus.Success;
// GetUser gets too much information, so we are using FindUserByName.
//return GetUser(username, false);
int totalRecords;
return(FindUsersByName(username, 0, 100, out totalRecords).Cast <MembershipUser>().First());
}
catch (OracleDataStoreException ex)
{
switch (ex.OracleErrorNumber)
{
case 1935:
//1935: missing user or role name (comes when passing null username). Not expected as we are already checking the passed user.
case 922:
//922: Missing or invalid option (comes when password contains special chars or whitespace)
throw new MembershipCreateUserException("User name or password is invalid", ex);
case 1031:
//1031: insufficient privileges
throw new MembershipCreateUserException("You do not have sufficient privileges for creating users.", ex);
case 1920:
//1920: user name 'user-name' conflicts with another user
throw new MembershipCreateUserException(string.Format("User {0} already exists", username));
case 28003:
// ORA-28003: password verification for the specified password failed
throw new MembershipCreateUserException(ex.Message, ex);
default:
throw;
}
}
}
}
// /// <summary>
// /// If passed area is cancelled area then return true otherwise false.
// /// We update the pieces in ialoc_content if source area is cancel area.
// /// </summary>
// /// <param name="sourceArea"></param>
// /// <param name="vwhId"></param>
// /// <param name="pieces"></param>
// /// <param name="upcCode"></param>
// /// <returns>
// /// Bool: True or False
// /// </returns>
// internal void UpdateCancelAreaPieces(string sourceArea, string vwhId, int? pieces, string upcCode)
// {
// Contract.Assert(_db != null);
// const string QUERY = @"
// UPDATE <proxy />IALOC_CONTENT I
// SET I.NUMBER_OF_UNITS = I.NUMBER_OF_UNITS - :PIECES
// WHERE I.LOCATION_ID = (SELECT A.LOCATION_ID
// FROM <proxy />IALOC A
// WHERE A.IA_ID = :SOURCE_AREA
// AND A.VWH_ID = :VWH_ID)
// AND I.IACONTENT_ID = :UPC_CODE
// ";
// var binder = SqlBinder.Create()
// .Parameter("SOURCE_AREA", sourceArea)
// .Parameter("VWH_ID", vwhId)
// .Parameter("PIECES", pieces)
// .Parameter("UPC_CODE", upcCode);
// _db.ExecuteNonQuery(QUERY, binder);
// }
// /// <summary>
// /// Remove carton from SRC_OPEN_CARTON.
// /// </summary>
// /// <param name="cartonId"></param>
// internal void RemoveCarton(string cartonId)
// {
// Contract.Assert(_db != null);
// const string QUERY = @"
// DELETE FROM <proxy />SRC_OPEN_CARTON S WHERE S.CARTON_ID = :CARTON_ID";
// var binder = SqlBinder.Create()
// .Parameter("CARTON_ID", cartonId);
// _db.ExecuteNonQuery(QUERY, binder);
// }
/// <summary>
/// Creates a new carton based on the passed info.
/// Returns the id of the first and last newly created carton.
/// </summary>
/// <param name="info">This will be null if BarCode is invalid</param>
internal string[] RepackCarton(CartonRepackInfo info)
{
const string QUERY = @"
DECLARE
LRelated_TRansaction_Id NUMBER(10);
LLcarton_id VARCHAR2(255);
LFcarton_id VARCHAR2(255);
LLOCATION_ID VARCHAR2(13);
LFLAG_QUALITY NUMBER := <proxy />PKG_CARTON_WORK_2.PFLAG_QUALITY;
BEGIN
FOR i IN 1 .. :no_of_cartons LOOP
insert into <proxy />src_carton
(carton_id,
shipment_id,
pallet_id,
price_season_code,
carton_storage_area,
sewing_plant_code,
vwh_id,
quality_code)
VALUES
(
<if>:carton_id</if>
<if c='not($carton_id)'>'R'||Carton_Sequence.Nextval</if>,
:shipment_id,
:apallet_id,
:aprice_season_code,
:adestination_area,
:asewing_plant_code,
:vwh_id,
:quality_code)
RETURNING carton_id into LLcarton_id;
IF LFcarton_id is NULL THEN
LFcarton_id:=LLcarton_id;
END IF;
LRelated_TRansaction_Id := <proxy />pkg_inv_3.ADDSKUTOCARTON(acarton_id => LLcarton_id,
asku_id => :sku_id,
apieces => :apieces,
asource_area => :asource_area,
arelated_transaction_id => LRelated_TRansaction_Id);
<if c='$target_sku_id or $target_vwh_id'>
LRelated_TRansaction_Id := <proxy />pkg_carton_work_2.mark_carton_for_work(ACARTON_ID => LLcarton_id,
ATARGET_SKU_ID => :target_sku_id,
ATARGET_VWH_ID => :target_vwh_id,
ATARGET_QUALITY => :target_QualityCode,
arelated_transaction_id => LRelated_TRansaction_Id,
acomplete_flags => LFLAG_QUALITY);
</if>
<if c='$aprinter_name'>
<proxy />PKG_JF_SRC_2.PKG_JF_SRC_CTN_TKT(ACARTON_ID => LLcarton_id,
APRINTER_NAME => :aprinter_name);
</if>
END LOOP;
:acarton_id := LFcarton_id;
:acarton_id1 := LLcarton_id;
END;
";
if (string.IsNullOrEmpty(info.QualityCode))
{
throw new ArgumentNullException("info.QualityCode");
}
if (info.Pieces == null)
{
throw new ArgumentNullException("info.Pieces");
}
string[] cartonId = new string[2];
var binder = SqlBinder.Create().Parameter("carton_id", info.CartonId)
.Parameter("sku_id", info.SkuId)
.Parameter("target_sku_id", info.TartgetSkuId)
.OutParameter("acarton_id", row => cartonId[0] = row)
.OutParameter("acarton_id1", row => cartonId[1] = row)
.Parameter("vwh_id", info.VwhId)
.Parameter("adestination_area", info.DestinationCartonArea)
.Parameter("apallet_id", info.PalletId)
.Parameter("aprice_season_code", info.PriceSeasonCode)
.Parameter("quality_code", info.QualityCode)
.Parameter("asource_area", info.SourceSkuArea)
.Parameter("apieces", info.Pieces.Value)
.Parameter("asewing_plant_code", info.SewingPlantCode)
.Parameter("shipment_id", info.ShipmentId)
.Parameter("no_of_cartons", info.NumberOfCartons)
.Parameter("target_vwh_id", info.TargetVWhId)
.Parameter("target_QualityCode", info.TargetQualityCode)
.Parameter("aprinter_name", info.PrinterName)
.Parameter("UPC_CODE", info.UpcCode);
try
{
_db.ExecuteNonQuery(QUERY, binder);
}
catch (OracleException ex)
{
switch (ex.Number)
{
case 20006:
throw new Exception("Not enough inventory in source area. Overdraft is not allowed.", ex);
case 00001:
throw new Exception("Box is already convert in to carton.", ex);
default:
throw;
}
}
return(cartonId);
}
/// <summary>
/// This function is for revoking the passed roles from the passed users.
/// </summary>
/// <param name="usernames"></param>
/// <param name="roleNames"></param>
/// <remarks>
/// <para>
/// Empty user names and roles are silently ignored. All user names and roles are converted to upper case before they are processed.
/// </para>
/// <para>
/// The logged in user must have the rights to revoke roles. The logged in user must also have the delete rights on table upriv to delete user's priv.
/// Follwing are the scripts.
/// </para>
/// <code>
/// <![CDATA[
/// grant GRANT ANY ROLE to <user-name>
/// grant DELETE on URPIV to <user-name>
/// ]]>
/// </code>
/// </remarks>
public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
{
if (usernames == null)
{
throw new ArgumentNullException("usernames");
}
if (roleNames == null)
{
throw new ArgumentNullException("roleNames");
}
if (HttpContext.Current == null || string.IsNullOrWhiteSpace(HttpContext.Current.User.Identity.Name))
{
throw new ProviderException("You must be logged in with proper credentials to remove roles from users");
}
var joinUsersRoles = from user in usernames
from role in roleNames
where !string.IsNullOrEmpty(user) &&
!string.IsNullOrEmpty(role)
select new
{
Role = role.Trim().ToUpper(),
User = user.Trim().ToUpper()
};
const string QUERY_PRIV = @"DELETE <proxy />UPRIV WHERE ORACLE_USER_NAME ='{0}' AND PRIV_ID ='{1}'";
const string QUERY_ROLE = @"REVOKE {1} FROM {0}";
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionStringBuilder.ConnectionString, HttpContext.Current.User.Identity.Name);
foreach (var item in joinUsersRoles)
{
var query = string.Format(IsRolePriv(item.Role) ? QUERY_PRIV : QUERY_ROLE, item.User, item.Role);
ClearRoleCache(item.User);
try
{
db.ExecuteNonQuery(query, null);
}
catch (OracleDataStoreException ex)
{
switch (ex.OracleErrorNumber)
{
case 1919:
case 942:
case 1031:
// 1919: Role does not exist
// 942 : UPRIV table does not exist. To us this means no rights to delete from UPRIV
// 1031: Rights to revoke the role are not avaliable.
throw new ProviderException(string.Format("Role {0} does not exist. This could also mean that you do not have rights to revoke this role", item.Role));
case 1951:
// Role not granted. Check whether the role has been granted inderectly.
const string QUERY_ROLE_PATH = @"
WITH Q1(GRANTED_ROLE,
PATH) AS
(SELECT P.GRANTED_ROLE, CAST(U.USERNAME AS VARCHAR2(2000))
FROM DBA_ROLE_PRIVS P
INNER JOIN DBA_USERS U
ON P.GRANTEE = U.USERNAME
UNION ALL
SELECT P.GRANTED_ROLE, CAST(Q1.PATH || '/' || P.GRANTEE AS VARCHAR2(2000))
FROM DBA_ROLE_PRIVS P
INNER JOIN Q1
ON Q1.GRANTED_ROLE = P.GRANTEE
LEFT OUTER JOIN DBA_USERS U
ON P.GRANTEE = U.USERNAME
WHERE U.USERNAME IS NULL)
SELECT substr(path, instr(path, '/') + 1)
FROM Q1 Q
WHERE Q.PATH LIKE :username || '/%'
and q.granted_role = :rolename
";
// Execute this query as super user
db.CreateConnection(_connectionStringBuilder.ConnectionString, string.Empty);
//var binder = new SqlBinder<string>("Get Role Path");
var binder = SqlBinder.Create(row => row.GetString(0));
//binder.CreateMapper(QUERY_ROLE_PATH);
//binder.Query = QUERY_ROLE_PATH;
//binder.Factory = row => row.GetString();
binder.Parameter("username", item.User);
binder.Parameter("rolename", item.Role);
var path = db.ExecuteSingle <string>(QUERY_ROLE_PATH, binder);
if (!string.IsNullOrEmpty(path))
{
var roleToRevoke = path.Split('/').First();
throw new ProviderException(
string.Format(
"Role {0} has indirectly granted to user {1} and cannot be revoked directly. {2}/{0}. To revoke {0} role revoke {3} role.",
item.Role, item.User, path, roleToRevoke));
}
throw new ProviderException(ex.Message);
case 1917:
throw new ProviderException(string.Format("At least one of Role {0} or User {1} is invalid", item.Role, item.User));
default:
throw;
}
}
}
}
}
/// <summary>
/// This function is for un-locking a locked user account.
/// </summary>
/// <param name="userName"></param>
/// <remarks>
/// <para>
/// The logged in user must have the rights for resetting password of a user. Following is the script.
/// </para>
/// <code>
/// <![CDATA[
/// GRANT ALTER USER TO <user-name>;
/// ]]>
/// </code>
///</remarks>
/// <returns>This function will return True on successful unlock else return False</returns>
public override bool UnlockUser(string userName)
{
if (string.IsNullOrWhiteSpace(userName))
{
throw new ArgumentNullException("userName");
}
if (HttpContext.Current == null || string.IsNullOrWhiteSpace(HttpContext.Current.User.Identity.Name))
{
throw new ProviderException("You must be logged in with proper credentials for un locking a user account");
}
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionString, HttpContext.Current.User.Identity.Name);
try
{
var sqlQuery = string.Format("ALTER USER {0} ACCOUNT UNLOCK", userName);
db.ExecuteNonQuery(sqlQuery, null);
return true;
}
catch (OracleDataStoreException ex)
{
switch (ex.OracleErrorNumber)
{
case 1031:
//1031: insufficient privileges
throw new ProviderException("You do not have sufficient privileges for unlocking a locked user account.", ex);
case 1918:
//1918: user does not exist
throw new ProviderException(string.Format("User {0} does not exits", userName), ex);
default:
throw;
}
}
}
}
/// <summary>
/// This function is for granting the passed roles to the passed users.
/// </summary>
/// <param name="usernames"></param>
/// <param name="roleNames"></param>
/// <remarks>
/// <para>
/// The logged in user must have the rights to add roles. The logged in user must also have the insert rights to add upriv.
/// Following are the scripts.
/// </para>
/// <code>
/// <![CDATA[
/// grant GRANT ANY ROLE to <user-name>
/// grant INSERT on URPIV to <user-name>
/// ]]>
/// </code>
/// </remarks>
public override void AddUsersToRoles(string[] usernames, string[] roleNames)
{
if (usernames == null)
{
throw new ArgumentNullException("usernames");
}
if (roleNames == null)
{
throw new ArgumentNullException("roleNames");
}
if (HttpContext.Current == null || string.IsNullOrWhiteSpace(HttpContext.Current.User.Identity.Name))
{
throw new ProviderException("You must be logged in with proper credentials to add role to a user");
}
var joinUsersRoles = from user in usernames
from role in roleNames
where !string.IsNullOrEmpty(user) &&
!string.IsNullOrEmpty(role)
select new
{
Role = role.Trim().ToUpper(),
User = user.Trim().ToUpper()
};
const string QUERY_PRIV = @"INSERT INTO <proxy />UPRIV (PRIV_ID,ORACLE_USER_NAME) VALUES ('{1}','{0}')";
const string QUERY_ROLE = @"GRANT {1} to {0}";
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionStringBuilder.ConnectionString, HttpContext.Current.User.Identity.Name);
//var binder = new SqlBinder<string>("Granting Roles.");
var binder = SqlBinder.Create();
foreach (var item in joinUsersRoles)
{
var sqlQuery = string.Format(IsRolePriv(item.Role) ? QUERY_PRIV : QUERY_ROLE, item.User, item.Role);
ClearRoleCache(item.User);
try
{
db.ExecuteNonQuery(sqlQuery, null);
}
catch (OracleDataStoreException ex)
{
switch (ex.OracleErrorNumber)
{
case 1919:
case 942:
case 1031:
// 1919: Role does not exist
// 942 : UPRIV table does not exist. To us this means no rights to insert into table UPRIV
//1031 : Rights to insert the upriv are not avaliable
throw new ProviderException(string.Format("Role {0} does not exist. This could also mean that you do not have rights to grant this role", item.Role));
case 1917:
throw new ProviderException(string.Format("At least one of Role {0} or User {1} is invalid", item.Role, item.User));
case 1:
//Priv already assigned to the user(UNIQUE CONSTRAINT VOILATED) remain silent and move further.
continue;
default:
throw;
}
}
}
}
}
/// <summary>
/// The password is changed to <paramref name="answer"/>. The password is set to expire immediately which will force the user to change password at next login.
/// </summary>
/// <param name="username">Name of the user need to reset password</param>
/// <param name="answer">The new password, or empty to randomply generate a password</param>
/// <returns>This function will return the new assigned password</returns>
/// <remarks>
/// <para>
/// The logged in user must have the rights for resetting password of a user. Following is the script.
/// </para>
/// <code>
/// <![CDATA[
/// GRANT ALTER USER TO <user-name>;
/// ]]>
/// </code>
///</remarks>
///
public override string ResetPassword(string username, string answer)
{
var rand = new Random();
if (string.IsNullOrEmpty(answer))
{
answer = rand.Next(1, (int)Math.Pow(10, this.MinRequiredPasswordLength) - 1).ToString().PadRight(this.MinRequiredPasswordLength, '1');
}
if (string.IsNullOrWhiteSpace(username))
{
throw new ArgumentNullException("username");
}
if (HttpContext.Current == null || string.IsNullOrWhiteSpace(HttpContext.Current.User.Identity.Name))
{
throw new ProviderException("You must be logged in with proper credentials for resetting a user's password");
}
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionString, HttpContext.Current.User.Identity.Name);
try
{
var sqlQuery = string.Format("ALTER USER {0} IDENTIFIED BY \"{1}\" PASSWORD EXPIRE", username, answer);
db.ExecuteNonQuery(sqlQuery, null);
return answer;
}
catch (OracleDataStoreException ex)
{
switch (ex.OracleErrorNumber)
{
case 1935:
//1935: missing user or role name (comes when username is null). Not expected as we are already checking the passed user.
case 922:
//922: Missing or invalid option (comes when username contains special chars or whitespace)
throw new ProviderException("User name is invalid", ex);
case 1031:
//1031: insufficient privileges
throw new ProviderException("You do not have sufficient privileges for resetting password.", ex);
default:
throw;
}
}
}
}
/// <summary>
/// This function is for creating a new user.
/// </summary>
/// <param name="username"></param>
/// <param name="password"></param>
/// <param name="email">Ignored</param>
/// <param name="passwordQuestion">Ignored</param>
/// <param name="passwordAnswer">Ignored</param>
/// <param name="isApproved">Ignored</param>
/// <param name="providerUserKey">Ignored</param>
/// <param name="status">
/// <para>
/// Can return InvalidUserName, DuplicateUserName, InvalidPassword or Success
/// </para>
/// </param>
/// <returns>User object when <paramref name="status"/> = Success; null otherwise. </returns>
/// <remarks>
/// <para>
/// The user is always created with an expired password. The default profile is assigned to the user. CONNECT THROUGH rights are given to the proxy user.
/// </para>
/// <para>
/// The logged in user must have the rights to crete User. Following is the script.
/// </para>
/// <code>
/// <![CDATA[
/// GRANT CREATE USER TO <user-name>
/// ]]>
/// </code>
/// </remarks>
public override MembershipUser CreateUser(string username, string password, string email, string passwordQuestion, string passwordAnswer,
bool isApproved, object providerUserKey, out MembershipCreateStatus status)
{
if (string.IsNullOrWhiteSpace(username))
{
throw new ArgumentNullException("username");
}
if (string.IsNullOrWhiteSpace(password))
{
throw new ArgumentNullException("password");
}
var e = new ValidatePasswordEventArgs(username, password, true);
OnValidatingPassword(e);
if (e.Cancel)
{
// App decided to cancel user creation
status = MembershipCreateStatus.InvalidPassword;
return null;
}
if (HttpContext.Current == null || string.IsNullOrWhiteSpace(HttpContext.Current.User.Identity.Name))
{
throw new MembershipCreateUserException("You must be logged in with proper credentials to create a user");
}
EnsureDefaultProfile();
//var builder = new OracleConnectionStringBuilder(_connectionString);
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionString, HttpContext.Current.User.Identity.Name);
try
{
var sqlQuery = string.Format("CREATE USER {0} IDENTIFIED BY \"{1}\" PROFILE {2} PASSWORD EXPIRE", username, password, _visibleProfiles[0]);
db.ExecuteNonQuery(sqlQuery, null);
foreach (var proxy in _proxyUsers)
{
sqlQuery = string.Format("ALTER USER {0} GRANT CONNECT THROUGH {1}", username, proxy);
db.ExecuteNonQuery(sqlQuery, null);
}
status = MembershipCreateStatus.Success;
// GetUser gets too much information, so we are using FindUserByName.
//return GetUser(username, false);
int totalRecords;
return FindUsersByName(username, 0, 100, out totalRecords).Cast<MembershipUser>().First();
}
catch (OracleDataStoreException ex)
{
switch (ex.OracleErrorNumber)
{
case 1935:
//1935: missing user or role name (comes when passing null username). Not expected as we are already checking the passed user.
case 922:
//922: Missing or invalid option (comes when password contains special chars or whitespace)
throw new MembershipCreateUserException("User name or password is invalid", ex);
case 1031:
//1031: insufficient privileges
throw new MembershipCreateUserException("You do not have sufficient privileges for creating users.", ex);
case 1920:
//1920: user name 'user-name' conflicts with another user
throw new MembershipCreateUserException(string.Format("User {0} already exists", username));
case 28003:
// ORA-28003: password verification for the specified password failed
throw new MembershipCreateUserException(ex.Message, ex);
default:
throw;
}
}
}
}
/// <summary>
/// This function is for deleting an existing user.
/// </summary>
/// <param name="username"></param>
/// <param name="deleteAllRelatedData">Ignored</param>
/// <returns>This function will return True if user successfully deleted else return False</returns>
/// <remarks>
/// <para>
/// The logged in user must have the rights to drop a User. Following is the script.
/// </para>
/// <code>
/// <![CDATA[
/// GRANT DROP USER To <user-name>;
/// ]]>
/// </code>
/// </remarks>
public override bool DeleteUser(string username, bool deleteAllRelatedData)
{
if (string.IsNullOrWhiteSpace(username))
{
throw new ArgumentNullException("username");
}
if (HttpContext.Current == null || string.IsNullOrWhiteSpace(HttpContext.Current.User.Identity.Name))
{
throw new ProviderException("You must be logged in with proper credentials for deleting a user");
}
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
db.CreateConnection(_connectionString, HttpContext.Current.User.Identity.Name);
try
{
var sqlQuery = string.Format("DROP USER {0}", username);
db.ExecuteNonQuery(sqlQuery, null);
return true;
}
catch (OracleDataStoreException ex)
{
switch (ex.OracleErrorNumber)
{
case 1031:
//1031: insufficient privileges
throw new ProviderException("You do not have sufficient privileges for deleting a user.", ex);
case 1918:
//1918: user does not exist
throw new ProviderException(string.Format("User {0} does not exits", username), ex);
case 921:
//921: invalid username
throw new ProviderException("User name is invalid", ex);
case 1940:
//1940: Already logged in user is trying to delete itself.
throw new ProviderException("Cannot drop a user that is currently connected");
default:
throw;
}
}
}
}
/// <summary>
/// The password change will succeed only if the old password is valid.
/// </summary>
/// <param name="username"></param>
/// <param name="oldPassword"></param>
/// <param name="newPassword"></param>
/// <returns>true if password successfully changed. false if the old password is invalid</returns>
/// <remarks>
/// Any data base exception encountered will be propagated to the caller.
/// Sharad 15 Feb 2012: Supported voluntary changes of passwords. Earlier only expired passwords could be changed.
/// Sharad 21 Feb 2012: Raising ValidatingPassword event
/// </remarks>
public override bool ChangePassword(string username, string oldPassword, string newPassword)
{
if (string.IsNullOrWhiteSpace(username))
{
throw new ArgumentNullException("username");
}
if (string.IsNullOrWhiteSpace(oldPassword))
{
throw new ArgumentNullException("oldPassword");
}
if (string.IsNullOrWhiteSpace(newPassword))
{
throw new ArgumentNullException("newPassword");
}
var e = new ValidatePasswordEventArgs(username, newPassword, true);
OnValidatingPassword(e);
if (e.Cancel)
{
// App decided to cancel user creation
return false;
}
var builder = new OracleConnectionStringBuilder(_connectionString)
{
UserID = username,
Password = oldPassword,
Pooling = false,
ProxyUserId = string.Empty,
ProxyPassword = string.Empty
};
// Try to login as passed user with old password to ensure that the old password is valid
using (var db = new OracleDatastore(HttpContext.Current.Trace))
{
var msg = string.Format("Opening connection to {0} for user {1}",
builder.DataSource, builder.UserID);
Trace.WriteLine(msg, "OracleMembershipProvider");
db.CreateConnection(builder.ConnectionString, builder.UserID);
Trace.WriteLine(msg, "Opening connection with old password");
try
{
db.Connection.Open();
}
catch (OracleException ex)
{
switch (ex.Number)
{
case 1017:
// Invalid user name password
Trace.TraceWarning("Invalid password specified for user {0}", username);
return false;
case 28001:
// If we are using ODP.NET, we can change the password now
// This will only work if the user's password has expired
Trace.WriteLine(msg, "Password expired error oracle exception encountered");
db.Connection.OpenWithNewPassword(newPassword);
return true;
default:
throw;
}
}
// If we get here, the old password was valid. Now we will change the password
//REPLACE is used to remove exception ORA-28221
Trace.WriteLine(msg, "Executing ALTER USER with new password");
var query = string.Format("ALTER USER {0} IDENTIFIED BY \"{1}\" REPLACE \"{2}\"", username, newPassword, oldPassword);
db.ExecuteNonQuery(query, null);
}
return true;
}
