private static void SaveDynamicMethods(ShippingProvider provider, int providerId, AspxCommonInfo commonInfo)
{
foreach (var dynamicMethod in provider.DynamicMethods)
{
try
{
List <KeyValuePair <string, object> > paraMeter = new List <KeyValuePair <string, object> >();
paraMeter.Add(new KeyValuePair <string, object>("ShippingProviderId", providerId));
paraMeter.Add(new KeyValuePair <string, object>("AssemblyName", provider.AssemblyName));
paraMeter.Add(new KeyValuePair <string, object>("ClassName", provider.ShippingProviderClass));
paraMeter.Add(new KeyValuePair <string, object>("NameSpace", provider.ShippingProviderNamespace));
paraMeter.Add(new KeyValuePair <string, object>("MethodName", dynamicMethod.MethodName));
paraMeter.Add(new KeyValuePair <string, object>("MethodType", dynamicMethod.MethodType));
paraMeter.Add(new KeyValuePair <string, object>("ParameterCount",
dynamicMethod.DynamicParams.Count()));
paraMeter.Add(new KeyValuePair <string, object>("StoreId", commonInfo.StoreID));
paraMeter.Add(new KeyValuePair <string, object>("PortalId", commonInfo.PortalID));
paraMeter.Add(new KeyValuePair <string, object>("UserName", commonInfo.UserName));
OracleHandler sqLh = new OracleHandler();
int dynamicmId = sqLh.ExecuteAsScalar <int>("usp_aspx_AddDynamicMethod", paraMeter);
SaveDynamicMethodParams(dynamicMethod.DynamicParams, dynamicmId, commonInfo);
}
catch (Exception ex)
{
throw ex;
}
}
}
public static int GetMonthlyVisit()
{
//SQLHandler SQLH = new SQLHandler();
OracleHandler SQLH = new OracleHandler();
return(SQLH.ExecuteAsScalar <int>("usp_GetMonthlyVisit"));
}
/// <summary>
/// 获取RFC配置的【用户，IP等相关信息】
/// </summary>
/// <param name="VR_CLASS"></param>
/// <param name="VR_ITEM"></param>
/// <param name="VR_NAME"></param>
/// <returns></returns>
public ReturnResult Get_RFC_ConfigInfo_ByKey(string VR_CLASS, string VR_ITEM, string VR_NAME, string PRG_NAME)
{
ReturnResult result = new ReturnResult();
DBParameter dbParams = new DBParameter();
try
{
dbParams.Clear();
string sql = @"SELECT PI.VR_VALUE FROM SFIS1.C_PARAMETER_INI PI
WHERE PI.PRG_NAME =:PRG_NAME AND PI.VR_CLASS = :VR_CLASS AND PI.VR_ITEM = :VR_ITEM AND PI.VR_NAME = :VR_NAME";
dbParams.Add(":VR_CLASS", OracleDbType.Varchar2, VR_CLASS);
dbParams.Add(":VR_ITEM", OracleDbType.Varchar2, VR_ITEM);
dbParams.Add(":VR_NAME", OracleDbType.Varchar2, VR_NAME);
dbParams.Add(":PRG_NAME", OracleDbType.Varchar2, PRG_NAME);
result.Anything = dbHandler.ExecuteAsScalar <string>(sql, CommandType.Text, dbParams.GetParameters());
result.Status = true;
}
catch (Exception ex)
{
result.Status = false;
result.Message = ex.Message;
this.logHandler.Error(MethodBase.GetCurrentMethod().Name, "Call Get_RFC_ConfigInfo_ByKey Fail: " + ex.Message);
}
return(result);
}
public static string GetCategoryCheckedItems(int CategoryID, AspxCommonInfo aspxCommonObj)
{
OracleHandler sqlHandler = new OracleHandler();
List <KeyValuePair <string, object> > parameterCollection = CommonParmBuilder.GetParamSP(aspxCommonObj);
parameterCollection.Add(new KeyValuePair <string, object>("CategoryID", CategoryID));
string categoryItem = sqlHandler.ExecuteAsScalar <string>("usp_Aspx_GetItemsByCategoryIDAdmin", parameterCollection);
return(categoryItem);
}
public static double GetRatefromTable(AspxCommonInfo aspxCommonObj, string currencyCode)
{
OracleHandler sqlH = new OracleHandler();
List <KeyValuePair <string, object> > Parameter = CommonParmBuilder.GetParamSP(aspxCommonObj);
Parameter.Add(new KeyValuePair <string, object>("CurrencyCode", currencyCode));
decimal rate = sqlH.ExecuteAsScalar <decimal>("usp_Aspx_GetRateFromTable", Parameter);
return(Convert.ToDouble(rate));
}
public string GetFullName(int portalId, string userName)
{
var paramCol = new List <KeyValuePair <string, object> >();
paramCol.Add(new KeyValuePair <string, object>("PortalID", portalId));
paramCol.Add(new KeyValuePair <string, object>("UserName", userName));
var sageSQL = new OracleHandler();
//return sageSQL.ExecuteAsScalar<string>("usp_Aspx_GetUserFirstandLastName", paramCol);
return(sageSQL.ExecuteAsScalar <string>("usp_Aspx_GetUserFirstandLastNa", paramCol));
}
public static string GetStateCode(string cCode, string stateName)
{
List <KeyValuePair <string, object> > paramCol = new List <KeyValuePair <string, object> >();
paramCol.Add(new KeyValuePair <string, object>("CountryCode", cCode));
paramCol.Add(new KeyValuePair <string, object>("StateName", stateName));
OracleHandler sqlHl = new OracleHandler();
string stateCode = sqlHl.ExecuteAsScalar <string>("usp_Aspx_GetStateCodeByName", paramCol);
return(stateCode);
}
public static void SaveGiftCardByAdmin(int giftCardId, GiftCard giftCardDetail, bool isActive, AspxCommonInfo aspxCommonObj)
{
try
{
List <KeyValuePair <string, object> > parameter = CommonParmBuilder.GetParamSPUC(aspxCommonObj);
parameter.Add(new KeyValuePair <string, object>("@GiftCardId", giftCardId));
parameter.Add(new KeyValuePair <string, object>("@GiftCardGraphicsId", giftCardDetail.GraphicThemeId));
parameter.Add(new KeyValuePair <string, object>("@IsRecipientNotified",
giftCardDetail.IsRecipientNotified));
//add column in table expiry date /add default 1 year after/ paid
parameter.Add(new KeyValuePair <string, object>("@ExpireDate", giftCardDetail.ExpireDate));
parameter.Add(new KeyValuePair <string, object>("@Amount", giftCardDetail.Price));
parameter.Add(new KeyValuePair <string, object>("@IsActive", isActive));
parameter.Add(new KeyValuePair <string, object>("@RecipientEmail", giftCardDetail.RecipientEmail));
parameter.Add(new KeyValuePair <string, object>("@RecipientName", giftCardDetail.RecipientName));
parameter.Add(new KeyValuePair <string, object>("@SenderEmail", giftCardDetail.SenderEmail));
parameter.Add(new KeyValuePair <string, object>("@SenderName", giftCardDetail.SenderName));
parameter.Add(new KeyValuePair <string, object>("@Messege", giftCardDetail.Messege));
if (giftCardId > 0)
{
parameter.Add(new KeyValuePair <string, object>("@GiftCardCode", giftCardDetail.GiftCardCode));
}
else
{
parameter.Add(new KeyValuePair <string, object>("@GiftCardCode", GetGiftCardKey()));
}
parameter.Add(new KeyValuePair <string, object>("@GiftCardTypeId", giftCardDetail.GiftCardTypeId));
OracleHandler sqlH = new OracleHandler();
int i = sqlH.ExecuteAsScalar <int>("usp_Aspx_SaveGiftCardByAdmin", parameter);
var typeId = GetGiftCardType(aspxCommonObj.StoreID, aspxCommonObj.PortalID, i);
if (giftCardId == 0)
{
IssueGiftCard(i, null, aspxCommonObj);
if (typeId == 1) //both or virtual
{
NotifyUser(i, aspxCommonObj);
}
}
else
{
if ((bool)(!giftCardDetail.IsRecipientNotified))
{
NotifyUser(i, aspxCommonObj);
}
}
}
catch (Exception e)
{
throw e;
}
}
public decimal GetTotalShippingCost(int shippingMethodID, int storeID, int portalID, string userName, string cultureName)
{
List <KeyValuePair <string, object> > parameter = new List <KeyValuePair <string, object> >();
parameter.Add(new KeyValuePair <string, object>("ShippingMethodID", shippingMethodID));
parameter.Add(new KeyValuePair <string, object>("StoreID", storeID));
parameter.Add(new KeyValuePair <string, object>("PortalID", portalID));
parameter.Add(new KeyValuePair <string, object>("UserName", userName));
parameter.Add(new KeyValuePair <string, object>("CultureName", cultureName));
OracleHandler sqlH = new OracleHandler();
return(sqlH.ExecuteAsScalar <decimal>("usp_Aspx_ShippingCost", parameter));
}
public static int CountWishItems(AspxCommonInfo aspxCommonObj)
{
try
{
List <KeyValuePair <string, object> > parameter = CommonParmBuilder.GetParamNoCID(aspxCommonObj);
OracleHandler sqlH = new OracleHandler();
return(sqlH.ExecuteAsScalar <int>("usp_Aspx_GetWishItemsCount", parameter));
}
catch (Exception e)
{
throw e;
}
}
public static decimal GetCartItemsCount(AspxCommonInfo aspxCommonObj)
{
try
{
List <KeyValuePair <string, object> > parameter = CommonParmBuilder.GetFullParam(aspxCommonObj);
OracleHandler sqlH = new OracleHandler();
return(sqlH.ExecuteAsScalar <decimal>("usp_Aspx_GetCartItemsCount", parameter));
}
catch (Exception e)
{
throw e;
}
}
public static int GetCompareItemsCount(AspxCommonInfo aspxCommonObj)
{
try
{
List <KeyValuePair <string, object> > parameter = CommonParmBuilder.GetParamSPUS(aspxCommonObj);
OracleHandler sqlH = new OracleHandler();
int compCount = sqlH.ExecuteAsScalar <int>("usp_Aspx_GetCompareItemsCount", parameter);
return(compCount);
}
catch (Exception e)
{
throw e;
}
}
public static string GetShoppingBagSetting(AspxCommonInfo aspxCommonObj)
{
try
{
List <KeyValuePair <string, object> > parameterCollection = CommonParmBuilder.GetParamSPC(aspxCommonObj);
OracleHandler sqlHandle = new OracleHandler();
string bagType = sqlHandle.ExecuteAsScalar <string>("usp_Aspx_GetShoppingBagSetting", parameterCollection);
return(bagType);
}
catch (Exception e)
{
throw e;
}
}
public static bool CheckGiftCardCategory(AspxCommonInfo aspxCommonObj, string giftcardCategoryName)
{
try
{
List <KeyValuePair <string, object> > parameter = CommonParmBuilder.GetParamSPC(aspxCommonObj);
parameter.Add(new KeyValuePair <string, object>("@GiftCardCategoryName", giftcardCategoryName.Trim()));
OracleHandler sqlH = new OracleHandler();
bool isGiftCard = sqlH.ExecuteAsScalar <bool>("usp_Aspx_CheckGiftCardCategory ", parameter);
return(isGiftCard);
}
catch (Exception e)
{
throw e;
}
}
public static decimal GetTotalCartItemPrice(AspxCommonInfo aspxCommonObj)
{
try
{
List <KeyValuePair <string, object> > parameter = CommonParmBuilder.GetParamSPSCt(aspxCommonObj);
OracleHandler sqlH = new OracleHandler();
//decimal cartPrice = sqlH.ExecuteAsScalar<decimal>("usp_Aspx_GetCartItemsTotalAmount", parameter);
decimal cartPrice = sqlH.ExecuteAsScalar <decimal>("usp_Aspx_GetCartItemsTotalAmou", parameter);
return(cartPrice);
}
catch (Exception e)
{
throw e;
}
}
public static string GetUserBillingEmail(int addressID, AspxCommonInfo aspxCommonObj)
{
try
{
List <KeyValuePair <string, object> > parameter = CommonParmBuilder.GetFParamNoSCode(aspxCommonObj);
parameter.Add(new KeyValuePair <string, object>("AddressID", addressID));
OracleHandler sqlH = new OracleHandler();
string userEmail = sqlH.ExecuteAsScalar <string>("[usp_Aspx_GetUserBillingEmail]", parameter);
return(userEmail);
}
catch (Exception e)
{
throw e;
}
}
public static string GetGiftCardItemCategory(int itemId, AspxCommonInfo aspxCommonObj)
{
try
{
List <KeyValuePair <string, object> > parameter = CommonParmBuilder.GetParamSPC(aspxCommonObj);
parameter.Add(new KeyValuePair <string, object>("@ItemId", itemId));
OracleHandler sqlH = new OracleHandler();
string strValue = sqlH.ExecuteAsScalar <string>("usp_Aspx_GetGiftCardItemCategory", parameter);
return(strValue);
}
catch (Exception e)
{
throw e;
}
}
public static string GetPromoItemCheckIDs(int CouponID, AspxCommonInfo aspxCommonObj)
{
try
{
List <KeyValuePair <string, object> > parameter = CommonParmBuilder.GetParamSP(aspxCommonObj);
parameter.Add(new KeyValuePair <string, object>("CouponID", CouponID));
OracleHandler sqlh = new OracleHandler();
string lst = sqlh.ExecuteAsScalar <string>("dbo.usp_Aspx_GetPromoItemCheckIDs", parameter);
return(lst);
}
catch (Exception ex)
{
throw ex;
}
}
public static int GetGiftCardType(AspxCommonInfo aspxCommonObj, int cartitemId)
{
try
{
List <KeyValuePair <string, object> > parameter = CommonParmBuilder.GetParamSP(aspxCommonObj);
parameter.Add(new KeyValuePair <string, object>("@CartItemId", cartitemId));
OracleHandler sqlH = new OracleHandler();
int strType = sqlH.ExecuteAsScalar <int>("usp_Aspx_GetGiftCardType", parameter);
return(strType);
}
catch (Exception e)
{
throw e;
}
}
public static string GetSymbolFromCurrencyCode(string currencyCode, int storeID, int portalID)
{
try
{
List <KeyValuePair <string, object> > parameter = new List <KeyValuePair <string, object> >();
parameter.Add(new KeyValuePair <string, object>("CurrencyCode", currencyCode));
parameter.Add(new KeyValuePair <string, object>("StoreID", storeID));
parameter.Add(new KeyValuePair <string, object>("PortalID", portalID));
OracleHandler sqlH = new OracleHandler();
return(sqlH.ExecuteAsScalar <string>("usp_Aspx_GetSymbolFromCurrency", parameter));
}
catch (Exception ex)
{
throw ex;
}
}
public static decimal CheckItemQuantity(int itemID, AspxCommonInfo aspxCommonObj, string itemCostVariantIDs)
{
try
{
List <KeyValuePair <string, object> > parameter = CommonParmBuilder.GetParamSPSCt(aspxCommonObj);
parameter.Add(new KeyValuePair <string, object>("ItemID", itemID));
parameter.Add(new KeyValuePair <string, object>("ItemCostVariantIDs", itemCostVariantIDs));
OracleHandler sqlH = new OracleHandler();
decimal retValue = sqlH.ExecuteAsScalar <decimal>("usp_Aspx_CheckItemQuantity", parameter);
return(retValue);
}
catch (Exception e)
{
throw e;
}
}
private static int GetGiftCardType(int storeId, int portalId, int giftCardId)
{
try
{
List <KeyValuePair <string, object> > parameter = new List <KeyValuePair <string, object> >();
parameter.Add(new KeyValuePair <string, object>("@GiftCardId", giftCardId));
parameter.Add(new KeyValuePair <string, object>("@StoreID", storeId));
parameter.Add(new KeyValuePair <string, object>("@PortalID", portalId));
OracleHandler sqlH = new OracleHandler();
return(sqlH.ExecuteAsScalar <int>("usp_Aspx_GetGiftTypeId", parameter));
}
catch (Exception e)
{
throw e;
}
}
private static int GetGiftCardIdByCartItemId(int cartItemId, int storeId, int portalId)
{
try
{
List <KeyValuePair <string, object> > parameter = new List <KeyValuePair <string, object> >();
parameter.Add(new KeyValuePair <string, object>("CartItemId", cartItemId));
parameter.Add(new KeyValuePair <string, object>("StoreId", storeId));
parameter.Add(new KeyValuePair <string, object>("PortalId", portalId));
OracleHandler sqlH = new OracleHandler();
int giftCardID = sqlH.ExecuteAsScalar <int>("usp_Aspx_GetGiftCardIdByCartItemId ", parameter);
return(giftCardID);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 获取当前日期当前班次的LOT_SEQ值
/// </summary>
/// <param name="shift"></param>
/// <param name="createTime"></param>
/// <returns></returns>
public int GetMaxLotSeq(string shift, string createTime,string lineName)
{
int result = 1;
//string strSql = "SELECT * FROM (SELECT LOT_SEQ FROM SFISM4.R_WIP_LOT_TRACKING_T LR WHERE LR.SHIFT=:SHIFT AND LR.LINE_NAME=:LINE_NAME AND LR.CREATE_TIME=TO_DATE(:CREATE_TIME,'YYYY/MM/DD') ORDER BY LR.LOT_SEQ DESC ) WHERE ROWNUM=1";
string strSql = "SELECT * FROM (SELECT CAST(LR.LOT_SEQ AS int) LOTSEQ FROM SFISM4.R_WIP_LOT_TRACKING_T LR WHERE LR.SHIFT=:SHIFT AND LR.LINE_NAME=:LINE_NAME AND LR.CREATE_TIME=TO_DATE(:CREATE_TIME,'YYYY/MM/DD') ORDER BY LOTSEQ DESC ) WHERE ROWNUM=1";
DBParameter dbParameter = new DBParameter();
dbParameter.Clear();
dbParameter.Add(":SHIFT", OracleDbType.Varchar2, shift);
dbParameter.Add(":LINE_NAME", OracleDbType.Varchar2, lineName);
dbParameter.Add(":CREATE_TIME", OracleDbType.Varchar2, createTime);
var resultValue = dbHander.ExecuteAsScalar<object>(strSql, CommandType.Text, dbParameter.GetParameters());
if (resultValue != null && resultValue.ToString().Length > 0)
{
result += int.Parse(resultValue.ToString());
}
return result;
}
public static string GetStoreSettingValueByKey(string settingKey, int storeID, int portalID, string cultureName)
{
try
{
List <KeyValuePair <string, object> > parameter = new List <KeyValuePair <string, object> >();
parameter.Add(new KeyValuePair <string, object>("SettingKey", settingKey));
parameter.Add(new KeyValuePair <string, object>("StoreID", storeID));
parameter.Add(new KeyValuePair <string, object>("PortalID", portalID));
parameter.Add(new KeyValuePair <string, object>("CultureName", cultureName));
OracleHandler sqlH = new OracleHandler();
return(sqlH.ExecuteAsScalar <string>("usp_Aspx_GetStoreSettingValueBYKey", parameter));
}
catch (Exception ex)
{
throw ex;
}
}
public int SaveComponent(KitComponent kitcomponent, AspxCommonInfo commonInfo)
{
try
{
List <KeyValuePair <string, object> > parameter = CommonParmBuilder.GetParamSPUC(commonInfo);
parameter.Add(new KeyValuePair <string, object>("KitComponentID", kitcomponent.KitComponentID));
parameter.Add(new KeyValuePair <string, object>("ComponentName", kitcomponent.KitComponentName));
parameter.Add(new KeyValuePair <string, object>("KitComponentType", kitcomponent.KitComponentType));
OracleHandler sqLh = new OracleHandler();
int kitComponentID = sqLh.ExecuteAsScalar <int>("usp_Aspx_AddUpdateKitComponent", parameter);
return(kitComponentID);
}
catch (Exception ex)
{
throw ex;
}
}
public static int SaveUpdateProviderSetting(ShippingProvider provider, string settingKey, string settingValue, AspxCommonInfo commonInfo)
{
try
{
if (!provider.IsUnique)
{
DeleteShippingProvider(provider.ShippingProviderID, commonInfo);
}
List <KeyValuePair <string, object> > parameter = new List <KeyValuePair <string, object> >();
parameter.Add(new KeyValuePair <string, object>("ShippingProviderID", 0));
parameter.Add(new KeyValuePair <string, object>("StoreID", commonInfo.StoreID));
parameter.Add(new KeyValuePair <string, object>("PortalID", commonInfo.PortalID));
parameter.Add(new KeyValuePair <string, object>("CultureName", commonInfo.CultureName));
parameter.Add(new KeyValuePair <string, object>("UserName", commonInfo.UserName));
parameter.Add(new KeyValuePair <string, object>("ShippingProviderServiceCode",
provider.ShippingProviderServiceCode));
parameter.Add(new KeyValuePair <string, object>("ShippingProviderName", provider.ShippingProviderName));
parameter.Add(new KeyValuePair <string, object>("ShippingProviderAliasHelp",
provider.ShippingProviderAliasHelp));
parameter.Add(new KeyValuePair <string, object>("AssemblyName", provider.AssemblyName));
parameter.Add(new KeyValuePair <string, object>("Namespace", provider.ShippingProviderNamespace));
parameter.Add(new KeyValuePair <string, object>("Class", provider.ShippingProviderClass));
parameter.Add(new KeyValuePair <string, object>("SettingControlSrc", provider.SettingControlPath));
parameter.Add(new KeyValuePair <string, object>("LabelControlSrc", provider.LabelControlPath));
parameter.Add(new KeyValuePair <string, object>("TrackControlSrc", provider.TrackControlPath));
parameter.Add(new KeyValuePair <string, object>("IsActive", provider.IsActive));
parameter.Add(new KeyValuePair <string, object>("IsAddedZipFlag", true));
OracleHandler sqlH = new OracleHandler();
int providerId = sqlH.ExecuteAsScalar <int>("usp_Aspx_ShippingProviderAddUpdate", parameter);
SaveSetting(providerId, settingKey, settingValue, commonInfo);
SaveDynamicMethods(provider, providerId, commonInfo);
ExtractFile(provider);
return(providerId);
}
catch (Exception ex)
{
throw ex;
}
}
public static decimal GetCostVariantQuantity(int itemID, int storeID, int portalID, string itemCostVariantIDs)
{
try
{
List <KeyValuePair <string, object> > parameter = new List <KeyValuePair <string, object> >();
parameter.Add(new KeyValuePair <string, object>("ItemID", itemID));
parameter.Add(new KeyValuePair <string, object>("StoreID", storeID));
parameter.Add(new KeyValuePair <string, object>("PortalID", portalID));
parameter.Add(new KeyValuePair <string, object>("ItemCostVariantIDs", itemCostVariantIDs));
OracleHandler sqlH = new OracleHandler();
decimal cvQty = sqlH.ExecuteAsScalar <decimal>("usp_Aspx_GetCostVariantQuantity", parameter);
return(cvQty);
}
catch (Exception e)
{
throw e;
}
}
public int GetItemID(int iPortalID)
{
int result = 0;
try
{
List <KeyValuePair <string, object> > parameterCollection = new List <KeyValuePair <string, object> >();
parameterCollection.Add(new KeyValuePair <string, object>("PortalID", iPortalID));
OracleHandler sqLH = new OracleHandler();
result = sqLH.ExecuteAsScalar <int>("usp_Aspx_CatalogGetTotalItem", parameterCollection);
}
catch (Exception e)
{
throw e;
}
return(result);
}
public static string GetCategoryForItem(int storeID, int portalID, string itemSku, string cultureName)
{
try
{
List <KeyValuePair <string, object> > parameter = new List <KeyValuePair <string, object> >();
parameter.Add(new KeyValuePair <string, object>("StoreID", storeID));
parameter.Add(new KeyValuePair <string, object>("PortalID", portalID));
parameter.Add(new KeyValuePair <string, object>("ItemSku", itemSku));
parameter.Add(new KeyValuePair <string, object>("CultureName", cultureName));
OracleHandler sqlH = new OracleHandler();
string retString = sqlH.ExecuteAsScalar <string>("usp_Aspx_GetCategoryforItems", parameter);
return(retString);
}
catch (Exception e)
{
throw e;
}
}
