public static bool ChangeMenuOutOfStokcStatus(object menuId, bool isOutOfStock)
{
var cmd = new QueryCommand("UPDATE dbo.Sys_Takeaway_Menu SET IsOutOfStock = @IsOutOfStock,OutOfStockDate = GETDATE() WHERE ID=@ID AND IsOutOfStock <> @IsOutOfStock;");
cmd.AddParameter("@IsOutOfStock", isOutOfStock, DbType.Boolean);
cmd.AddParameter("@ID", menuId, DbType.Int32);
DataService.ExecuteQuery(cmd);
return true;
}
public static bool ChangeMenuPrice(object menuId, decimal newPrice)
{
var cmd = new QueryCommand("UPDATE dbo.Sys_Takeaway_Menu SET Price = @Price WHERE ID=@ID AND Price <> @Price;");
cmd.AddParameter("@Price", newPrice, DbType.Decimal);
cmd.AddParameter("@ID", menuId, DbType.Int32);
DataService.ExecuteQuery(cmd);
return true;
}
public static void DeleteMenuByDirId(int dir)
{
//SysTakeawayMenu.Columns
var cmd = new QueryCommand("UPDATE dbo.Sys_Takeaway_Menu SET IsDeleted = 1 WHERE DirID=@DirID;");
cmd.AddParameter("@DirID", dir, DbType.Int32);
DataService.ExecuteQuery(cmd);
}
public Common.ServicesResult GetMansionArea(System.Web.HttpContext context)
{
var phone = context.Request["phone"];
QueryCommand cmd = new QueryCommand(CompanyMansionAreaQuery);
cmd.AddParameter("@Phone", phone);
return new Common.ServicesResult { data = DataService.GetDataTable(cmd) };
}
public Dictionary<string, double> AppStatDetailForOneDay(string appNo, string type, string dateTime, string table, string comeFrom)
{
Dictionary<string, double> result = new Dictionary<string, double>();
// var commandText = string.Format(@"select {0},sum(count) count from YLTEST.{1} where app_no='{2}' and statistic_date='{3}'
//group by {0} ", type,table,appNo, dateTime);
if ((type.EqualsOrdinalIgnoreCase("FIRMWARE_MODE")
|| type.EqualsOrdinalIgnoreCase("MANUFACTURER")
|| type.EqualsOrdinalIgnoreCase("COME_FROM"))
&& (table.EqualsOrdinalIgnoreCase("STAT_APP_PV")
|| table.EqualsOrdinalIgnoreCase("STAT_APP_DOWN")
|| table.EqualsOrdinalIgnoreCase("STAT_APP_DOWN_OK")
))
{
StringBuilder commandText = new StringBuilder();
commandText.AppendFormat(@"select {0},sum(count) count from YLTEST.{1} where app_no=:appNo and statistic_date=:statDate ", type, table);
if (!string.IsNullOrEmpty(comeFrom) && !comeFrom.EqualsOrdinalIgnoreCase("none"))
{
commandText.AppendFormat(" and COME_FROM = :from1 ");
}
commandText.AppendFormat(" group by {0} order by count", type);
var provider = ProviderFactory.GetProvider(ConnectionStrings.Key_ORACLE_LOG);
var queryCommand = new QueryCommand(commandText.ToString(), provider);
queryCommand.AddParameter("appNo", appNo, DbType.String);
queryCommand.AddParameter("statDate", dateTime.ToInt32(), DbType.Int32);
if (!string.IsNullOrEmpty(comeFrom) && !comeFrom.EqualsOrdinalIgnoreCase("none"))
{
queryCommand.AddParameter("from1", comeFrom, DbType.String);
}
var ds = provider.ExecuteDataSet(queryCommand);
if (ds != null && ds.Tables.Count == 1)
{
var dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
result.Add(row[type.ToUpper()].ToString(), string.IsNullOrEmpty(row["COUNT"].ToString()) ? 0 : int.Parse(row["COUNT"].ToString()));
}
}
}
}
return result;
}
public Common.ServicesResult Query(System.Web.HttpContext context)
{
string result = string.Empty;
string tel = context.Request.Params["CompanyTel"];
if (string.IsNullOrEmpty(tel))
{
result = "[]";
goto label_end;
}
QueryCommand cmd = new QueryCommand("select distinct [Sys_Area_Mansion].[id],[Sys_Area_Mansion].[name] from [Sys_Area_Mansion] inner join [Sys_Company] on [Sys_Company].[AreaDepth] like [Sys_Area_Mansion].[AreaDepth]+'%' where [Sys_Company].[CompanyTel] = @companyTel;");
cmd.AddParameter("@companyTel", tel, DbType.String);
DataTable dt = DataService.GetDataSet(cmd).Tables[0];
result = JsonConvert.SerializeObject(dt);
label_end:
return new Common.ServicesResult { data = result };
}
public static void SaveCustomerDemographicMap(string varCustomerID, string[] itemList)
{
QueryCommandCollection coll = new SubSonic.QueryCommandCollection();
//delete out the existing
QueryCommand cmdDel = new QueryCommand("DELETE FROM [CustomerCustomerDemo] WHERE [CustomerCustomerDemo].[CustomerID] = PARM__CustomerID", Customer.Schema.Provider.Name);
cmdDel.AddParameter("PARM__CustomerID", varCustomerID, DbType.String);
coll.Add(cmdDel);
DataService.ExecuteTransaction(coll);
foreach (string item in itemList)
{
CustomerCustomerDemo varCustomerCustomerDemo = new CustomerCustomerDemo();
varCustomerCustomerDemo.SetColumnValue("CustomerID", varCustomerID);
varCustomerCustomerDemo.SetColumnValue("CustomerTypeID", item);
varCustomerCustomerDemo.Save();
}
}
public static void SaveCategoryMap(int varProductId, CategoryCollection items)
{
QueryCommandCollection coll = new SubSonic.QueryCommandCollection();
//delete out the existing
QueryCommand cmdDel = new QueryCommand("DELETE FROM dashCommerce_Store_Product_Category_Map WHERE ProductId=@ProductId", Product.Schema.Provider.Name);
cmdDel.AddParameter("@ProductId", varProductId);
coll.Add(cmdDel);
DataService.ExecuteTransaction(coll);
foreach (Category item in items)
{
ProductCategoryMap varProductCategoryMap = new ProductCategoryMap();
varProductCategoryMap.SetColumnValue("ProductId", varProductId);
varProductCategoryMap.SetColumnValue("CategoryId", item.GetPrimaryKeyValue());
varProductCategoryMap.Save();
}
}
public static void SaveStoreMap(int varContactTypeID, int[] itemList)
{
QueryCommandCollection coll = new SubSonic.QueryCommandCollection();
//delete out the existing
QueryCommand cmdDel = new QueryCommand("DELETE FROM StoreContact WHERE ContactTypeID=@ContactTypeID", ContactType.Schema.Provider.Name);
cmdDel.AddParameter("@ContactTypeID", varContactTypeID);
coll.Add(cmdDel);
DataService.ExecuteTransaction(coll);
foreach (int item in itemList)
{
StoreContact varStoreContact = new StoreContact();
varStoreContact.SetColumnValue("ContactTypeID", varContactTypeID);
varStoreContact.SetColumnValue("CustomerID", item);
varStoreContact.Save();
}
}
public static void SaveProductMap(int varAttributeId, int[] itemList)
{
QueryCommandCollection coll = new SubSonic.QueryCommandCollection();
//delete out the existing
QueryCommand cmdDel = new QueryCommand("DELETE FROM dashCommerce_Store_Product_Attribute_Map WHERE AttributeId=@AttributeId", Attribute.Schema.Provider.Name);
cmdDel.AddParameter("@AttributeId", varAttributeId);
coll.Add(cmdDel);
DataService.ExecuteTransaction(coll);
foreach (int item in itemList)
{
ProductAttributeMap varProductAttributeMap = new ProductAttributeMap();
varProductAttributeMap.SetColumnValue("AttributeId", varAttributeId);
varProductAttributeMap.SetColumnValue("ProductId", item);
varProductAttributeMap.Save();
}
}
public static void SaveVendorMap(int varAddressTypeID, VendorCollection items)
{
QueryCommandCollection coll = new SubSonic.QueryCommandCollection();
//delete out the existing
QueryCommand cmdDel = new QueryCommand("DELETE FROM VendorAddress WHERE AddressTypeID=@AddressTypeID", AddressType.Schema.Provider.Name);
cmdDel.AddParameter("@AddressTypeID", varAddressTypeID);
coll.Add(cmdDel);
DataService.ExecuteTransaction(coll);
foreach (Vendor item in items)
{
VendorAddress varVendorAddress = new VendorAddress();
varVendorAddress.SetColumnValue("AddressTypeID", varAddressTypeID);
varVendorAddress.SetColumnValue("VendorID", item.GetPrimaryKeyValue());
varVendorAddress.Save();
}
}
public static void SaveVendorMap(string varUnitMeasureCode, string[] itemList)
{
QueryCommandCollection coll = new SubSonic.QueryCommandCollection();
//delete out the existing
QueryCommand cmdDel = new QueryCommand("DELETE FROM ProductVendor WHERE UnitMeasureCode=@UnitMeasureCode", UnitMeasure.Schema.Provider.Name);
cmdDel.AddParameter("@UnitMeasureCode", varUnitMeasureCode);
coll.Add(cmdDel);
DataService.ExecuteTransaction(coll);
foreach (string item in itemList)
{
ProductVendor varProductVendor = new ProductVendor();
varProductVendor.SetColumnValue("UnitMeasureCode", varUnitMeasureCode);
varProductVendor.SetColumnValue("VendorID", item);
varProductVendor.Save();
}
}
public static void SaveProductMap(int varCategoryID, int[] itemList)
{
QueryCommandCollection coll = new SubSonic.QueryCommandCollection();
//delete out the existing
QueryCommand cmdDel = new QueryCommand("DELETE FROM [Product_Category_Map] WHERE [Product_Category_Map].[CategoryID] = @CategoryID", Category.Schema.Provider.Name);
cmdDel.AddParameter("@CategoryID", varCategoryID, DbType.Int32);
coll.Add(cmdDel);
DataService.ExecuteTransaction(coll);
foreach (int item in itemList)
{
ProductCategoryMap varProductCategoryMap = new ProductCategoryMap();
varProductCategoryMap.SetColumnValue("CategoryID", varCategoryID);
varProductCategoryMap.SetColumnValue("ProductID", item);
varProductCategoryMap.Save();
}
}
public static void SaveProductMap(int varDocumentID, int[] itemList)
{
QueryCommandCollection coll = new SubSonic.QueryCommandCollection();
//delete out the existing
QueryCommand cmdDel = new QueryCommand("DELETE FROM ProductDocument WHERE DocumentID=@DocumentID", Document.Schema.Provider.Name);
cmdDel.AddParameter("@DocumentID", varDocumentID);
coll.Add(cmdDel);
DataService.ExecuteTransaction(coll);
foreach (int item in itemList)
{
ProductDocument varProductDocument = new ProductDocument();
varProductDocument.SetColumnValue("DocumentID", varDocumentID);
varProductDocument.SetColumnValue("ProductID", item);
varProductDocument.Save();
}
}
public static void SaveCreditCardMap(int varContactID, int[] itemList)
{
QueryCommandCollection coll = new SubSonic.QueryCommandCollection();
//delete out the existing
QueryCommand cmdDel = new QueryCommand("DELETE FROM ContactCreditCard WHERE ContactID=@ContactID", Contact.Schema.Provider.Name);
cmdDel.AddParameter("@ContactID", varContactID);
coll.Add(cmdDel);
DataService.ExecuteTransaction(coll);
foreach (int item in itemList)
{
ContactCreditCard varContactCreditCard = new ContactCreditCard();
varContactCreditCard.SetColumnValue("ContactID", varContactID);
varContactCreditCard.SetColumnValue("CreditCardID", item);
varContactCreditCard.Save();
}
}
public static void SavePBJMap(string varKODEKELENGKAPAN, string[] itemList)
{
QueryCommandCollection coll = new SubSonic.QueryCommandCollection();
//delete out the existing
QueryCommand cmdDel = new QueryCommand("DELETE FROM KELENGKAPANPBJ WHERE KODEKELENGKAPAN=@KODEKELENGKAPAN", KELENGKAPAN.Schema.Provider.Name);
cmdDel.AddParameter("@KODEKELENGKAPAN", varKODEKELENGKAPAN);
coll.Add(cmdDel);
DataService.ExecuteTransaction(coll);
foreach (string item in itemList)
{
KELENGKAPANPBJ varKELENGKAPANPBJ = new KELENGKAPANPBJ();
varKELENGKAPANPBJ.SetColumnValue("KODEKELENGKAPAN", varKODEKELENGKAPAN);
varKELENGKAPANPBJ.SetColumnValue("KODEBPJ", item);
varKELENGKAPANPBJ.Save();
}
}
public static void SaveCurrencyMap(string varCountryRegionCode, string[] itemList)
{
QueryCommandCollection coll = new SubSonic.QueryCommandCollection();
//delete out the existing
QueryCommand cmdDel = new QueryCommand("DELETE FROM CountryRegionCurrency WHERE CountryRegionCode=@CountryRegionCode", CountryRegion.Schema.Provider.Name);
cmdDel.AddParameter("@CountryRegionCode", varCountryRegionCode);
coll.Add(cmdDel);
DataService.ExecuteTransaction(coll);
foreach (string item in itemList)
{
CountryRegionCurrency varCountryRegionCurrency = new CountryRegionCurrency();
varCountryRegionCurrency.SetColumnValue("CountryRegionCode", varCountryRegionCode);
varCountryRegionCurrency.SetColumnValue("CurrencyCode", item);
varCountryRegionCurrency.Save();
}
}
public static void SaveProductModelMap(string varCultureID, ProductModelCollection items)
{
QueryCommandCollection coll = new SubSonic.QueryCommandCollection();
//delete out the existing
QueryCommand cmdDel = new QueryCommand("DELETE FROM ProductModelProductDescriptionCulture WHERE CultureID=@CultureID", Culture.Schema.Provider.Name);
cmdDel.AddParameter("@CultureID", varCultureID);
coll.Add(cmdDel);
DataService.ExecuteTransaction(coll);
foreach (ProductModel item in items)
{
ProductModelProductDescriptionCulture varProductModelProductDescriptionCulture = new ProductModelProductDescriptionCulture();
varProductModelProductDescriptionCulture.SetColumnValue("CultureID", varCultureID);
varProductModelProductDescriptionCulture.SetColumnValue("ProductModelID", item.GetPrimaryKeyValue());
varProductModelProductDescriptionCulture.Save();
}
}
public static void SaveIllustrationMap(int varProductModelID, IllustrationCollection items)
{
QueryCommandCollection coll = new SubSonic.QueryCommandCollection();
//delete out the existing
QueryCommand cmdDel = new QueryCommand("DELETE FROM ProductModelIllustration WHERE ProductModelID=@ProductModelID", ProductModel.Schema.Provider.Name);
cmdDel.AddParameter("@ProductModelID", varProductModelID);
coll.Add(cmdDel);
DataService.ExecuteTransaction(coll);
foreach (Illustration item in items)
{
ProductModelIllustration varProductModelIllustration = new ProductModelIllustration();
varProductModelIllustration.SetColumnValue("ProductModelID", varProductModelID);
varProductModelIllustration.SetColumnValue("IllustrationID", item.GetPrimaryKeyValue());
varProductModelIllustration.Save();
}
}
public static void SaveEmployeeMap(string varTerritoryID, int[] itemList)
{
QueryCommandCollection coll = new SubSonic.QueryCommandCollection();
//delete out the existing
QueryCommand cmdDel = new QueryCommand("DELETE FROM [EmployeeTerritories] WHERE [EmployeeTerritories].[TerritoryID] = @TerritoryID", Territory.Schema.Provider.Name);
cmdDel.AddParameter("@TerritoryID", varTerritoryID, DbType.String);
coll.Add(cmdDel);
DataService.ExecuteTransaction(coll);
foreach (int item in itemList)
{
EmployeeTerritory varEmployeeTerritory = new EmployeeTerritory();
varEmployeeTerritory.SetColumnValue("TerritoryID", varTerritoryID);
varEmployeeTerritory.SetColumnValue("EmployeeID", item);
varEmployeeTerritory.Save();
}
}
public static void SaveProductModelMap(int varProductDescriptionID, int[] itemList)
{
QueryCommandCollection coll = new SubSonic.QueryCommandCollection();
//delete out the existing
QueryCommand cmdDel = new QueryCommand("DELETE FROM ProductModelProductDescriptionCulture WHERE ProductDescriptionID=@ProductDescriptionID", ProductDescription.Schema.Provider.Name);
cmdDel.AddParameter("@ProductDescriptionID", varProductDescriptionID);
coll.Add(cmdDel);
DataService.ExecuteTransaction(coll);
foreach (int item in itemList)
{
ProductModelProductDescriptionCulture varProductModelProductDescriptionCulture = new ProductModelProductDescriptionCulture();
varProductModelProductDescriptionCulture.SetColumnValue("ProductDescriptionID", varProductDescriptionID);
varProductModelProductDescriptionCulture.SetColumnValue("ProductModelID", item);
varProductModelProductDescriptionCulture.Save();
}
}
public static void SaveTemplateMap(int varTemplateRegionId, int[] itemList)
{
QueryCommandCollection coll = new SubSonic.QueryCommandCollection();
//delete out the existing
QueryCommand cmdDel = new QueryCommand("DELETE FROM dashCommerce_Content_Template_TemplateRegion_Map WHERE TemplateRegionId=@TemplateRegionId", TemplateRegion.Schema.Provider.Name);
cmdDel.AddParameter("@TemplateRegionId", varTemplateRegionId);
coll.Add(cmdDel);
DataService.ExecuteTransaction(coll);
foreach (int item in itemList)
{
TemplateTemplateRegionMap varTemplateTemplateRegionMap = new TemplateTemplateRegionMap();
varTemplateTemplateRegionMap.SetColumnValue("TemplateRegionId", varTemplateRegionId);
varTemplateTemplateRegionMap.SetColumnValue("TemplateId", item);
varTemplateTemplateRegionMap.Save();
}
}
public static void SaveSalesOrderHeaderMap(int varSalesReasonID, int[] itemList)
{
QueryCommandCollection coll = new SubSonic.QueryCommandCollection();
//delete out the existing
QueryCommand cmdDel = new QueryCommand("DELETE FROM SalesOrderHeaderSalesReason WHERE SalesReasonID=@SalesReasonID", SalesReason.Schema.Provider.Name);
cmdDel.AddParameter("@SalesReasonID", varSalesReasonID);
coll.Add(cmdDel);
DataService.ExecuteTransaction(coll);
foreach (int item in itemList)
{
SalesOrderHeaderSalesReason varSalesOrderHeaderSalesReason = new SalesOrderHeaderSalesReason();
varSalesOrderHeaderSalesReason.SetColumnValue("SalesReasonID", varSalesReasonID);
varSalesOrderHeaderSalesReason.SetColumnValue("SalesOrderID", item);
varSalesOrderHeaderSalesReason.Save();
}
}
public static void SaveAspnetUserMap(Guid varRoleId, Guid[] itemList)
{
QueryCommandCollection coll = new SubSonic.QueryCommandCollection();
//delete out the existing
QueryCommand cmdDel = new QueryCommand("DELETE FROM [aspnet_UsersInRoles] WHERE [aspnet_UsersInRoles].[RoleId] = @RoleId", AspnetRole.Schema.Provider.Name);
cmdDel.AddParameter("@RoleId", varRoleId, DbType.Guid);
coll.Add(cmdDel);
DataService.ExecuteTransaction(coll);
foreach (Guid item in itemList)
{
AspnetUsersInRole varAspnetUsersInRole = new AspnetUsersInRole();
varAspnetUsersInRole.SetColumnValue("RoleId", varRoleId);
varAspnetUsersInRole.SetColumnValue("UserId", item);
varAspnetUsersInRole.Save();
}
}
public static void SaveSysRoleMap(string varPkSuid, long[] itemList)
{
QueryCommandCollection coll = new SubSonic.QueryCommandCollection();
//delete out the existing
QueryCommand cmdDel = new QueryCommand("DELETE FROM [tbl_RolesForUsers] WHERE [tbl_RolesForUsers].[FP_sBranchID] = @FP_sBranchID", SysUser.Schema.Provider.Name);
cmdDel.AddParameter("@FP_sBranchID", varPkSuid, DbType.String);
coll.Add(cmdDel);
DataService.ExecuteTransaction(coll);
foreach (long item in itemList)
{
TblRolesForUser varTblRolesForUser = new TblRolesForUser();
varTblRolesForUser.SetColumnValue("FP_sBranchID", varPkSuid);
varTblRolesForUser.SetColumnValue("FP_sBranchID", item);
varTblRolesForUser.Save();
}
}
public static void SaveAddressTypeMap(int varCustomerID, int[] itemList)
{
QueryCommandCollection coll = new SubSonic.QueryCommandCollection();
//delete out the existing
QueryCommand cmdDel = new QueryCommand("DELETE FROM CustomerAddress WHERE CustomerID=@CustomerID", Customer.Schema.Provider.Name);
cmdDel.AddParameter("@CustomerID", varCustomerID);
coll.Add(cmdDel);
DataService.ExecuteTransaction(coll);
foreach (int item in itemList)
{
CustomerAddress varCustomerAddress = new CustomerAddress();
varCustomerAddress.SetColumnValue("CustomerID", varCustomerID);
varCustomerAddress.SetColumnValue("AddressTypeID", item);
varCustomerAddress.Save();
}
}
public static void SaveProductMap(int varSpecialOfferID, ProductCollection items)
{
QueryCommandCollection coll = new SubSonic.QueryCommandCollection();
//delete out the existing
QueryCommand cmdDel = new QueryCommand("DELETE FROM SpecialOfferProduct WHERE SpecialOfferID=@SpecialOfferID", SpecialOffer.Schema.Provider.Name);
cmdDel.AddParameter("@SpecialOfferID", varSpecialOfferID);
coll.Add(cmdDel);
DataService.ExecuteTransaction(coll);
foreach (Product item in items)
{
SpecialOfferProduct varSpecialOfferProduct = new SpecialOfferProduct();
varSpecialOfferProduct.SetColumnValue("SpecialOfferID", varSpecialOfferID);
varSpecialOfferProduct.SetColumnValue("ProductID", item.GetPrimaryKeyValue());
varSpecialOfferProduct.Save();
}
}
public static void SaveOrderMap(int varProductID, int[] itemList)
{
QueryCommandCollection coll = new SubSonic.QueryCommandCollection();
//delete out the existing
QueryCommand cmdDel = new QueryCommand("DELETE FROM [Order Details] WHERE [Order Details].[ProductID] = @ProductID", Product.Schema.Provider.Name);
cmdDel.AddParameter("@ProductID", varProductID, DbType.Int32);
coll.Add(cmdDel);
DataService.ExecuteTransaction(coll);
foreach (int item in itemList)
{
OrderDetail varOrderDetail = new OrderDetail();
varOrderDetail.SetColumnValue("ProductID", varProductID);
varOrderDetail.SetColumnValue("OrderID", item);
varOrderDetail.Save();
}
}
public static void SaveSalesPersonMap(int varTerritoryID, int[] itemList)
{
QueryCommandCollection coll = new SubSonic.QueryCommandCollection();
//delete out the existing
QueryCommand cmdDel = new QueryCommand("DELETE FROM SalesTerritoryHistory WHERE TerritoryID=@TerritoryID", SalesTerritory.Schema.Provider.Name);
cmdDel.AddParameter("@TerritoryID", varTerritoryID);
coll.Add(cmdDel);
DataService.ExecuteTransaction(coll);
foreach (int item in itemList)
{
SalesTerritoryHistory varSalesTerritoryHistory = new SalesTerritoryHistory();
varSalesTerritoryHistory.SetColumnValue("TerritoryID", varTerritoryID);
varSalesTerritoryHistory.SetColumnValue("SalesPersonID", item);
varSalesTerritoryHistory.Save();
}
}
public static void SaveCustomerMap(string varCustomerTypeID, CustomerCollection items)
{
QueryCommandCollection coll = new SubSonic.QueryCommandCollection();
//delete out the existing
QueryCommand cmdDel = new QueryCommand("DELETE FROM CustomerCustomerDemo WHERE CustomerTypeID=@CustomerTypeID", CustomerDemographic.Schema.Provider.Name);
cmdDel.AddParameter("@CustomerTypeID", varCustomerTypeID);
//add this in
coll.Add(cmdDel);
DataService.ExecuteTransaction(coll);
foreach (Customer item in items)
{
CustomerCustomerDemo varCustomerCustomerDemo = new CustomerCustomerDemo();
varCustomerCustomerDemo.SetColumnValue("CustomerTypeID", varCustomerTypeID);
varCustomerCustomerDemo.SetColumnValue("CustomerID", item.GetPrimaryKeyValue());
varCustomerCustomerDemo.Save();
}
}
public static void SaveTerritoryMap(int varEmployeeID, int[] itemList)
{
QueryCommandCollection coll = new SubSonic.QueryCommandCollection();
//delete out the existing
QueryCommand cmdDel = new QueryCommand("DELETE FROM EmployeeTerritories WHERE EmployeeID=@EmployeeID", Employee.Schema.Provider.Name);
cmdDel.AddParameter("@EmployeeID", varEmployeeID);
//add this in
coll.Add(cmdDel);
DataService.ExecuteTransaction(coll);
foreach (int item in itemList)
{
EmployeeTerritory varEmployeeTerritory = new EmployeeTerritory();
varEmployeeTerritory.SetColumnValue("EmployeeID", varEmployeeID);
varEmployeeTerritory.SetColumnValue("TerritoryID", item);
varEmployeeTerritory.Save();
}
}
public static void SaveEmployeeMap(string varTerritoryID, EmployeeCollection items)
{
QueryCommandCollection coll = new SubSonic.QueryCommandCollection();
//delete out the existing
QueryCommand cmdDel = new QueryCommand("DELETE FROM EmployeeTerritories WHERE TerritoryID=@TerritoryID", Territory.Schema.Provider.Name);
cmdDel.AddParameter("@TerritoryID", varTerritoryID);
//add this in
coll.Add(cmdDel);
DataService.ExecuteTransaction(coll);
foreach (Employee item in items)
{
EmployeeTerritory varEmployeeTerritory = new EmployeeTerritory();
varEmployeeTerritory.SetColumnValue("TerritoryID", varTerritoryID);
varEmployeeTerritory.SetColumnValue("EmployeeID", item.GetPrimaryKeyValue());
varEmployeeTerritory.Save();
}
}
public static void SaveProductMap(int varOrderID, ProductCollection items)
{
QueryCommandCollection coll = new SubSonic.QueryCommandCollection();
//delete out the existing
QueryCommand cmdDel = new QueryCommand("DELETE FROM Order Details WHERE OrderID=@OrderID", Order.Schema.Provider.Name);
cmdDel.AddParameter("@OrderID", varOrderID);
//add this in
coll.Add(cmdDel);
DataService.ExecuteTransaction(coll);
foreach (Product item in items)
{
OrderDetail varOrderDetail = new OrderDetail();
varOrderDetail.SetColumnValue("OrderID", varOrderID);
varOrderDetail.SetColumnValue("ProductID", item.GetPrimaryKeyValue());
varOrderDetail.Save();
}
}
public static void SaveProductMap(int varCategoryID, ProductCollection items)
{
QueryCommandCollection coll = new SubSonic.QueryCommandCollection();
//delete out the existing
QueryCommand cmdDel = new QueryCommand("DELETE FROM Product_Category_Map WHERE CategoryID=@CategoryID", Category.Schema.Provider.Name);
cmdDel.AddParameter("@CategoryID", varCategoryID);
//add this in
coll.Add(cmdDel);
DataService.ExecuteTransaction(coll);
foreach (Product item in items)
{
ProductCategoryMap varProductCategoryMap = new ProductCategoryMap();
varProductCategoryMap.SetColumnValue("CategoryID", varCategoryID);
varProductCategoryMap.SetColumnValue("ProductID", item.GetPrimaryKeyValue());
varProductCategoryMap.Save();
}
}
public virtual IEnumerable <T> ToEnumerable <T>(QueryCommand <T> query, object[] paramValues)
{
QueryCommand cmd = new QueryCommand(query.CommandText, this);
for (int i = 0; i < paramValues.Length; i++)
{
//need to assign a DbType
var valueType = paramValues[i].GetType();
var dbType = Database.GetDbType(valueType);
cmd.AddParameter(query.ParameterNames[i], paramValues[i], dbType);
}
// TODO: Can we use Database.ToEnumerable here? -> See commit 654aa2f48a67ba537e34 that fixes some issues
Type type = typeof(T);
//this is so hacky - the issue is that the Projector below uses Expression.Convert, which is a bottleneck
//it's about 10x slower than our ToEnumerable. Our ToEnumerable, however, stumbles on Anon types and groupings
//since it doesn't know how to instantiate them (I tried - not smart enough). So we do some trickery here.
if (type.Name.Contains("AnonymousType") || type.Name.StartsWith("Grouping`") || type.FullName.StartsWith("System."))
{
/*
* 修 改 人：Empty（AllEmpty）
* QQ 群：327360708
* 博客地址：http://www.cnblogs.com/EmptyFS/
* 修改时间：2014-03-06
* 修改说明：添加using，执行查询后关闭数据库链接
*********************************************/
using (var reader = ExecuteReader(cmd))
{
return(Project(reader, query.Projector));
}
}
else
{
using (var reader = ExecuteReader(cmd))
{
return(reader.ToEnumerable <T>(query.ColumnNames, GetInterceptor(type)));
}
}
}
public static void SaveProductMap(int varProductPhotoID, System.Web.UI.WebControls.ListItemCollection itemList)
{
QueryCommandCollection coll = new SubSonic.QueryCommandCollection();
//delete out the existing
QueryCommand cmdDel = new QueryCommand("DELETE FROM ProductProductPhoto WHERE ProductPhotoID=@ProductPhotoID", ProductPhoto.Schema.Provider.Name);
cmdDel.AddParameter("@ProductPhotoID", varProductPhotoID);
coll.Add(cmdDel);
DataService.ExecuteTransaction(coll);
foreach (System.Web.UI.WebControls.ListItem l in itemList)
{
if (l.Selected)
{
ProductProductPhoto varProductProductPhoto = new ProductProductPhoto();
varProductProductPhoto.SetColumnValue("ProductPhotoID", varProductPhotoID);
varProductProductPhoto.SetColumnValue("ProductID", l.Value);
varProductProductPhoto.Save();
}
}
}
public static void UpdateReadFlag(object faceBookID, object userId)
{
QueryCommand cmd = new QueryCommand("update Sys_Company_FaceBook set IsRead = 1 where id=@id and FaceBookMemberID=@userID;");
cmd.AddParameter("@id", faceBookID);
cmd.AddParameter("@userID", userId);
DataService.ExecuteQuery(cmd);
}
public static void GetOrderMealRateCount(int bizID, DateTime? d1, DateTime? d2, out int good, out int normal, out int bad)
{
good = 0; normal = 0; bad = 0;
var cmd = new QueryCommand(@"
select SUM(case when FaceBookRate <= 1 then 1 else 0 end) as bad,
SUM(case when FaceBookRate > 1 and FaceBookRate < 5 then 1 else 0 end) as normal,
SUM(case when FaceBookRate >= 5 then 1 else 0 end) as good
from dbo.Sys_Company_FaceBook WHERE FaceBookDate BETWEEN @d1 AND @d2 AND FaceBookBizID = @FaceBookBizID and FaceBookBizType = @FaceBookBizType;");
cmd.AddParameter("@FaceBookBizID", bizID, System.Data.DbType.Int32);
cmd.AddParameter("@FaceBookBizType", (int)FaceBookType.OrderMeal, System.Data.DbType.Int32);
cmd.AddParameter("@d1", d1 ?? System.Data.SqlTypes.SqlDateTime.MinValue, System.Data.DbType.DateTime);
cmd.AddParameter("@d2", d2 ?? DateTime.Now, System.Data.DbType.DateTime);
using (var dr = DataService.GetReader(cmd))
{
if (dr.Read( ))
{
bad = dr.IsDBNull(0) ? 0 : dr.GetInt32(0);
normal = dr.IsDBNull(1) ? 0 : dr.GetInt32(1);
good = dr.IsDBNull(2) ? 0 : dr.GetInt32(2);
}
}
}
/// <summary>
/// Saves this instance.
/// </summary>
public void Save()
{
QueryCommandCollection coll = new QueryCommandCollection();
DataProvider provider = DataService.GetInstance(providerName);
TableSchema.Table fkTable = DataService.GetSchema(foreignTableName, providerName, TableType.Table);
TableSchema.Table pkTable = DataService.GetSchema(primaryTableName, providerName, TableType.Table);
string fkPK = fkTable.PrimaryKey.ColumnName;
string pk = pkTable.PrimaryKey.ColumnName;
//delete out the existing
string idParam = Utility.PrefixParameter("id", provider);
QueryCommand cmdDel = new QueryCommand("DELETE FROM " + mapTableName + " WHERE " + pk + " = " + idParam, providerName);
cmdDel.AddParameter(idParam, primaryKeyValue, DbType.AnsiString);
//cmdDel.ProviderName = Product.Schema.ProviderName;
//add this in
coll.Add(cmdDel);
//loop the items and insert
string fkParam = Utility.PrefixParameter("fkID", provider);
string pkParam = Utility.PrefixParameter("pkID", provider);
foreach(ListItem l in Items)
{
if(l.Selected)
{
string iSql = "INSERT INTO " + mapTableName + " (" + fkPK + ", " + pk + ")" + " VALUES (" + fkParam + "," + pkParam + ")";
QueryCommand cmd = new QueryCommand(iSql, providerName);
cmd.Parameters.Add(fkParam, l.Value, fkTable.PrimaryKey.DataType);
cmd.Parameters.Add(pkParam, primaryKeyValue, pkTable.PrimaryKey.DataType);
coll.Add(cmd);
}
}
//execute
DataService.ExecuteTransaction(coll);
}
public static object GetUnReadCount( )
{
QueryCommand cmd = new QueryCommand("select count(*) from Sys_Company_FaceBook where IsRead=0 and FaceBookMemberID=@userID and FaceBookBizType=@FaceBookBizType;");
cmd.AddParameter("@userID", AppContextBase.CurrentUserID);
cmd.AddParameter("@FaceBookBizType", (int)FaceBookType.Eleooo);
return DataService.ExecuteScalar(cmd);
}
public static bool CancelMemberMealItem(Order order, out string message)
{
message = string.Empty;
if (!OrderMealBLL.HasCompanyItemInfo(order.Id))
return true;
if (order.OrderType != (int)OrderType.OrderMeal)
{
message = "订单类型错误.";
return false;
}
SysMemberItem mItem = GetMemberMealItemByOrderId(order.Id, order.OrderMemberID);
if (mItem == null)
{
message = "没有相应的抢购记录.";
return false;
}
mItem.ItemStatus = (int)MemberCompanyItemStatus.Cancel;
mItem.Save( );
QueryCommand cmd = new QueryCommand("update Sys_company_item Set ItemClicked = ItemClicked-1 Where ItemID=@ItemID AND ItemClicked > 0");
cmd.AddParameter("@ItemID", mItem.CompanyItemID, DbType.Int32);
DataService.ExecuteQuery(cmd);
if (mItem.PaymentID.HasValue)
Payment.Delete(mItem.PaymentID);
return true;
}
public static void UpdateCompanyItemSum(int? itemID, decimal itemSum, decimal? oldSum = null)
{
QueryCommand cmd;
if (oldSum.HasValue)
{
cmd = new QueryCommand("UPDATE Sys_Company_Item SET [ItemSum]= ([ItemSum] - @OldSum + @ItemSum) WHERE charindex('['+@ItemID+']',ItemInfo) > 0 OR charindex('['+@ItemID+',',ItemInfo) > 0 OR charindex(','+@ItemID+',',ItemInfo) > 0 OR charindex(','+@ItemID+']',ItemInfo) >0;");
cmd.AddParameter("@OldSum", oldSum.Value, DbType.Decimal);
cmd.AddParameter("@ItemID", itemID.ToString( ), DbType.String);
}
else
{
cmd = new QueryCommand("UPDATE Sys_Company_Item SET [ItemSum]=@ItemSum WHERE ItemID = @ItemID AND [ItemSum]<>@ItemSum;");
cmd.AddParameter("@ItemID", Math.Abs(itemID.Value), DbType.Int32);
}
cmd.AddParameter("@ItemSum", itemSum, DbType.Decimal);
DataService.ExecuteQuery(cmd);
}
public static void GetCompanyOrderRankingInfo(SysCompany company, out int ranking, out int amount)
{
ranking = amount = 0;
QueryCommand cmd = new QueryCommand(__CompanyOrderRankingQuery);
cmd.AddParameter("@ID", company.Id, System.Data.DbType.Int32);
cmd.AddParameter("@CompanyArea", company.CompanyLocation, System.Data.DbType.String);
using (var dr = DataService.GetReader(cmd))
{
if (dr.Read( ))
{
ranking = Utilities.ToInt(dr["RowNum"]);
amount = Utilities.ToInt(dr["Amount"]);
}
}
}
public Dictionary<DateTime, double> GetAppStatisticsCountDetails(string appNo, string table, string startDate, string endDate, string firmware, string pf, string from)
{
Dictionary<DateTime, double> result = new Dictionary<DateTime, double>();
if (table.EqualsOrdinalIgnoreCase("STAT_APP_PV")
|| table.EqualsOrdinalIgnoreCase("STAT_APP_DOWN")
|| table.EqualsOrdinalIgnoreCase("STAT_APP_DOWN_OK"))
{
StringBuilder comm = new StringBuilder();
comm.AppendFormat(@"select statistic_date, sum(count) count from yltest.{0} where app_no=:appNo AND STATISTIC_DATE >=:startDate AND STATISTIC_DATE <=:endDate ", table);
if (!string.IsNullOrEmpty(firmware))
{
comm.AppendFormat(" and FIRMWARE_MODE = :firmware");
}
if (!string.IsNullOrEmpty(pf))
{
comm.AppendFormat(" and MANUFACTURER = :pf");
}
if (!string.IsNullOrEmpty(from) && !from.EqualsOrdinalIgnoreCase("none"))
{
comm.AppendFormat(" and COME_FROM = :from1");
}
comm.Append(" group by statistic_date order by statistic_date");
var provider = ProviderFactory.GetProvider(ConnectionStrings.Key_ORACLE_LOG);
var queryCommand = new QueryCommand(comm.ToString(), provider);
queryCommand.AddParameter("appNo", appNo, DbType.String);
queryCommand.AddParameter("startDate", startDate.ToInt32(), DbType.Int32);
queryCommand.AddParameter("endDate", endDate.ToInt32(), DbType.Int32);
if (!string.IsNullOrEmpty(firmware))
{
queryCommand.AddParameter("firmware", firmware, DbType.String);
}
if (!string.IsNullOrEmpty(pf))
{
queryCommand.AddParameter("pf", pf, DbType.String);
}
if (!string.IsNullOrEmpty(from) && !from.EqualsOrdinalIgnoreCase("none"))
{
queryCommand.AddParameter("from1", from, DbType.String);
}
var ds = provider.ExecuteDataSet(queryCommand);
if (ds != null && ds.Tables.Count == 1)
{
var dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
result.Add(DateTime.ParseExact(row["STATISTIC_DATE"].ToString(), "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture), string.IsNullOrEmpty(row["COUNT"].ToString()) ? 0 : int.Parse(row["COUNT"].ToString()));
}
}
}
}
return result;
}
public static DateTime GetOrderUpdateOn(int orderId)
{
QueryCommand cmd = new QueryCommand("select orderupdateon from orders where id=@id");
cmd.AddParameter("@id", orderId, DbType.Int32);
return Convert.ToDateTime(DataService.ExecuteScalar(cmd));
}
public static int GetCompanyOrderElapsedRankingInfo(SysCompany company)
{
QueryCommand cmd = new QueryCommand(__CompanyOrderElapsedRankingQuery);
cmd.AddParameter("@ID", company.Id, System.Data.DbType.Int32);
return Utilities.ToInt(DataService.ExecuteScalar(cmd));
}
public Dictionary<DateTime, long> GetMarketUserStatData(string table, SearchMarketStatCriteria c)
{
Dictionary<DateTime, long> result = new Dictionary<DateTime, long>();
if (table.EqualsOrdinalIgnoreCase(StatisticConsts.STAT_MKT31_ACTIVE_UV)
|| table.EqualsOrdinalIgnoreCase(StatisticConsts.STAT_MKT31_ADD_UV)
|| table.EqualsOrdinalIgnoreCase(StatisticConsts.STAT_MKT31_PV))
{
StringBuilder comm = new StringBuilder();
comm.AppendFormat(@"select STAT_DATE, sum(count) count from yltest.{0} where STAT_DATE >=:startDate AND STAT_DATE <=:endDate ", table);//, table, c.StartDate.ToString("yyyyMMdd"), c.EndDate.ToString("yyyyMMdd"));
if (!string.IsNullOrEmpty(c.Firmware))
{
comm.AppendFormat(" and FIRMWARE_MODE = :firmware");//, c.Firmware);
}
if (!string.IsNullOrEmpty(c.Manufacturer))
{
comm.AppendFormat(" and MANUFACTURER = :pf");//, c.Manufacturer);
}
comm.Append(" group by STAT_DATE order by STAT_DATE");
var provider = ProviderFactory.GetProvider(ConnectionStrings.Key_ORACLE_LOG);
var queryCommand = new QueryCommand(comm.ToString(), provider);
queryCommand.AddParameter("startDate", c.StartDate.ToString("yyyyMMdd").ToInt32(), DbType.Int32);
queryCommand.AddParameter("endDate", c.EndDate.ToString("yyyyMMdd").ToInt32(), DbType.Int32);
if (!string.IsNullOrEmpty(c.Firmware))
{
queryCommand.AddParameter("firmware", c.Firmware, DbType.String);
}
if (!string.IsNullOrEmpty(c.Manufacturer))
{
queryCommand.AddParameter("pf", c.Manufacturer, DbType.String);
}
var ds = provider.ExecuteDataSet(queryCommand);
if (ds != null && ds.Tables.Count == 1)
{
var dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
result.Add(DateTime.ParseExact(row["STAT_DATE"].ToString(), "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture), string.IsNullOrEmpty(row["COUNT"].ToString()) ? 0 : long.Parse(row["COUNT"].ToString()));
}
}
}
}
return result;
}
public static void CancelRewardMemberPointForOrderMeal(Order order)
{
Payment p = OrderMealBLL.GetOrderMealPayment(order, order.OrderMemberID);
if (p != null)
Payment.Delete(p.Id);
order.OrderPoint = 0;
order.OrderRate = 0;
QueryCommand cmd = new QueryCommand("SELECT MemberPid FROM SYS_MEMBER WHERE ID = @ID;");
cmd.AddParameter("@ID", order.OrderMemberID);
int userPID = Utilities.ToInt(DataService.ExecuteScalar(cmd));
SysMemberReward r;
Payment rp;
TryGetMemberReward(order, userPID, out r, out rp);
if (r != null)
SysMemberReward.Delete(r.RewardID);
if (rp != null)
Payment.Delete(rp.Id);
}
public int GetAllCountForPage(SearchAppStatCriteria c, string action = "PV", string orderByAction = "DOWN")
{
int count = 0;
if (action.EqualsOrdinalIgnoreCase("PV")
|| action.EqualsOrdinalIgnoreCase("DOWN")
|| action.EqualsOrdinalIgnoreCase("DOWN_OK"))
{
Dictionary<string, long> result = new Dictionary<string, long>();
//StringBuilder comm = new StringBuilder();
//comm.AppendFormat(@" SELECT COUNT (*) FROM ( SELECT app_no, SUM (COUNT) FROM yltest.stat_app_{0} GROUP BY app_no) a FULL ( SELECT app_no, SUM (COUNT) FROM yltest.stat_app_{1} GROUP BY app_no) b on a.app_no=b.app_no ",
// action,orderbyAction);
StringBuilder commPV = new StringBuilder();
StringBuilder commDown = new StringBuilder();
commPV.AppendFormat(@"SELECT APP_NO FROM yltest.stat_app_{0} WHERE STATISTIC_DATE >=:startDate2 AND STATISTIC_DATE <=:endDate2", action);
commDown.AppendFormat(@"SELECT APP_NO FROM YLTEST.STAT_APP_{0} WHERE STATISTIC_DATE >=:startDate2 AND STATISTIC_DATE <=:endDate2 ",
orderByAction);
if (!string.IsNullOrEmpty(c.Firmware))
{
commPV.AppendFormat(" and FIRMWARE_MODE = :firmware ");
commDown.AppendFormat(" and FIRMWARE_MODE = :firmware2 ");
}
if (!string.IsNullOrEmpty(c.Manufacturer))
{
commPV.AppendFormat(" and MANUFACTURER = :pf");
commDown.AppendFormat(" and MANUFACTURER = :pf2");
}
if (!string.IsNullOrEmpty(c.From) && !c.From.EqualsOrdinalIgnoreCase("none"))
{
commPV.AppendFormat(" and COME_FROM = :from1");
commDown.AppendFormat(" and COME_FROM = :from2");
}
commPV.AppendFormat(" group by APP_NO ");
commDown.AppendFormat(" group by APP_NO ");
var provider = ProviderFactory.GetProvider(ConnectionStrings.Key_ORACLE_LOG);
StringBuilder comm = new StringBuilder();
comm.AppendFormat(@"select count(app_no) count from ( ({0}) union ({1}) )", commPV.ToString(), commDown.ToString());
var queryCommand = new QueryCommand(comm.ToString(), provider);
queryCommand.AddParameter("startDate", c.StartDate.ToString("yyyyMMdd").ToInt32(), DbType.Int32);
queryCommand.AddParameter("endDate", c.EndDate.ToString("yyyyMMdd").ToInt32(), DbType.Int32);
if (!string.IsNullOrEmpty(c.Firmware))
{
queryCommand.AddParameter("firmware", c.Firmware, DbType.String);
}
if (!string.IsNullOrEmpty(c.Manufacturer))
{
queryCommand.AddParameter("pf", c.Manufacturer, DbType.String);
}
if (!string.IsNullOrEmpty(c.From) && !c.From.EqualsOrdinalIgnoreCase("none"))
{
queryCommand.AddParameter("from1", c.From, DbType.String);
}
queryCommand.AddParameter("startDate2", c.StartDate.ToString("yyyyMMdd").ToInt32(), DbType.Int32);
queryCommand.AddParameter("endDate2", c.EndDate.ToString("yyyyMMdd").ToInt32(), DbType.Int32);
if (!string.IsNullOrEmpty(c.Firmware))
{
queryCommand.AddParameter("firmware2", c.Firmware, DbType.String);
}
if (!string.IsNullOrEmpty(c.Manufacturer))
{
queryCommand.AddParameter("pf2", c.Manufacturer, DbType.String);
}
if (!string.IsNullOrEmpty(c.From) && !c.From.EqualsOrdinalIgnoreCase("none"))
{
queryCommand.AddParameter("from2", c.From, DbType.String);
}
var ds = provider.ExecuteDataSet(queryCommand);
if (ds != null && ds.Tables.Count == 1)
{
var dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
int.TryParse(dt.Rows[0]["COUNT"].ToString(), out count);
}
}
}
return count;
}
public static int GetOrderCount(int companyID)
{
var cmd = new QueryCommand("Select count(*) from Orders where OrderStatus <> @OrderStatus and OrderSellerID = @OrderSellerID");
cmd.AddParameter("@OrderStatus", (int)OrderStatus.Canceled, DbType.Int32);
cmd.AddParameter("@OrderSellerID", companyID, DbType.Int32);
return Utilities.ToInt(DataService.ExecuteScalar(cmd));
}
/// <summary>
/// Gets the most used tags.
/// </summary>
/// <param name="hostID">The host ID.</param>
/// <param name="tagCount">The tag count.</param>
/// <param name="year">The year.</param>
/// <param name="month">The month.</param>
/// <param name="day">The day.</param>
/// <returns></returns>
public static Dictionary<string, int> GetMostUsedTags(int hostID, int tagCount, int year, int? month, int? day)
{
string cacheKey = String.Format("Zeitgeist_MostUsedTags_{0}_{1}_{2}_{3}_{4}", hostID, tagCount, year, month, day);
CacheManager<string, Dictionary<string, int>> tagCache = GetTagCollectionCache();
Dictionary<string, int> tags = tagCache[cacheKey];
if (tags == null)
{
//very messy need support for Group By and Joins in SubSonic
StringBuilder sqlSelect = new StringBuilder(128);
//select
sqlSelect.AppendFormat("SELECT TOP {0} COUNT(0) AS TagCount, {1}.{2} ",
tagCount,
Tag.Schema.TableName, Tag.Columns.TagIdentifier);
//from
sqlSelect.AppendFormat("FROM {0} INNER JOIN {1} on {0}.{2}={1}.{3} ",
StoryUserHostTag.Schema.TableName, Story.Schema.TableName,
StoryUserHostTag.Columns.StoryID, Story.Columns.StoryID);
sqlSelect.AppendFormat("INNER JOIN {0} on {0}.{1}={2}.{3} ",
Tag.Schema.TableName, Tag.Columns.TagID,
StoryUserHostTag.Schema.TableName, StoryUserHostTag.Columns.TagID);
//where
sqlSelect.AppendFormat("WHERE {0}.{1} >= @StartingDate AND {0}.{1} <= @EndingDate ",
Story.Schema.TableName, Story.Columns.CreatedOn);
sqlSelect.AppendFormat("AND {0}.{1} = 0 AND {0}.{2} = @HostId ",
Story.Schema.TableName, Story.Columns.IsSpam,
Story.Columns.HostID);
//group by
sqlSelect.AppendFormat("GROUP BY {0} ", Tag.Columns.TagIdentifier);
//order by
sqlSelect.Append("ORDER BY COUNT(0) DESC");
/*
* SELECT TOP 10 COUNT(0) AS TagCount, Kick_Tag.TagIdentifier
* FROM Kick_StoryUserHostTag INNER JOIN Kick_Story on Kick_StoryUserHostTag.StoryID=Kick_Story.StoryID
* INNER JOIN Kick_Tag on Kick_Tag.TagID=Kick_StoryUserHostTag.TagID
* WHERE Kick_Story.CreatedOn >= @StartingDate AND Kick_Story.CreatedOn <= @EndingDate
* AND Kick_Story.IsSpam = 0 AND Kick_Story.HostID = @HostId
* GROUP BY TagIdentifier
* ORDER BY COUNT(0) DESC
*/
//System.Diagnostics.Debug.WriteLine(sqlSelect.ToString());
QueryCommand qry = new QueryCommand(sqlSelect.ToString());
qry.AddParameter("@StartingDate", StartingDate(year, month, day));
qry.AddParameter("@EndingDate", EndingDate(year, month, day));
qry.AddParameter("@HostId", hostID);
tags = new Dictionary<string, int>();
IDataReader reader = DataService.GetInstance("DotNetKicks").GetReader(qry);
while (reader.Read())
{
tags.Add(reader[1].ToString(), int.Parse(reader[0].ToString()));//1=TagIdentifier (key), 0=TagCount (value)
}
reader.Close();
tagCache.Insert(cacheKey, tags, CacheHelper.CACHE_DURATION_IN_SECONDS);
}
return tags;
}
public static void RecycleAdminOrder( )
{
if (AppContext.Context.CurrentRole == (int)EleoooRoleDefine.OrderMeal)
{
QueryCommand cmd = new QueryCommand("Update Orders Set OrderOper=null where OrderOper=@OrderOper and OrderStatus in(2,3,4);");
cmd.AddParameter("@OrderOper", AppContext.Context.User.Id);
DataService.ExecuteQuery(cmd);
}
}
public Dictionary<string, long> GetMarket31StatUserDetail(string table, string date, string column)
{
Dictionary<string, long> result = new Dictionary<string, long>();
if ((table.EqualsOrdinalIgnoreCase(StatisticConsts.STAT_MKT31_ACTIVE_UV)
|| table.EqualsOrdinalIgnoreCase(StatisticConsts.STAT_MKT31_ADD_UV)
|| table.EqualsOrdinalIgnoreCase(StatisticConsts.STAT_MKT31_PV))
&& (column.EqualsOrdinalIgnoreCase(StatisticConsts.MANUFACTURER_COLUMN)
|| column.EqualsOrdinalIgnoreCase(StatisticConsts.FIRMWARE_COLUMN)
))
{
StringBuilder comm = new StringBuilder();
comm.AppendFormat(@"select {1}, sum(count) count from yltest.{0} where STAT_DATE=:statDate ", table, column);
comm.AppendFormat(" group by {0}", column);
var provider = ProviderFactory.GetProvider(ConnectionStrings.Key_ORACLE_LOG);
var queryCommand = new QueryCommand(comm.ToString(), provider);
queryCommand.AddParameter("statDate", date.ToInt32(), DbType.Int32);
var ds = provider.ExecuteDataSet(queryCommand);
if (ds != null && ds.Tables.Count == 1)
{
var dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
result.Add(row[column.ToUpper()].ToString(), string.IsNullOrEmpty(row["COUNT"].ToString()) ? 0 : int.Parse(row["COUNT"].ToString()));
}
}
}
}
return result;
}
public static Dictionary<object, OrderDetailItem> GetOrderItemMenus(int orderId)
{
Dictionary<object, OrderDetailItem> result = new Dictionary<object, OrderDetailItem>( );
var cmd = new QueryCommand(ItemDetailMenusCmd);
cmd.AddParameter("@OrderId", orderId, DbType.Int32);
using (var dr = DataService.GetReader(cmd))
{
//decimal orderSum = 0, allSum = 0;
bool isFirst = true;
OrderDetailItem item;
while (dr.Read( ))
{
var id = Utilities.ToInt(dr[SysTakeawayMenu.Columns.Id]);
if (!result.TryGetValue(id, out item))
{
item = new OrderDetailItem
{
OrderSum = isFirst ? Utilities.ToDecimal(dr["OrderSum"]) : 0,
DirName = dr[SysTakeawayDirectory.Columns.DirName],
MenuName = dr[SysTakeawayMenu.Columns.Name],
MenuId = id,
OrderId = orderId,
OrderQty = Utilities.ToInt(dr[OrdersDetail.Columns.OrderQty]),
OrderPrice = Utilities.ToDecimal(dr[SysTakeawayMenu.Columns.Price]),
IsCompanyItem = true,
IsOutOfStock = Utilities.ToBool(dr[SysTakeawayMenu.Columns.IsOutOfStock])
};
result.Add(id, item);
isFirst = false;
}
else
item.OrderQty = item.OrderQty + Utilities.ToInt(dr[OrdersDetail.Columns.OrderQty]);
}
}
return result;
}
public Dictionary<DateTime, long> GetUserTotalCount(SearchMarketStatCriteria c)
{
var result = new Dictionary<DateTime, long>();
if (c != null)
{
var provider = ProviderFactory.GetProvider(ConnectionStrings.Key_ORACLE_LOG);
var startDate = c.StartDate;
var endDate = c.EndDate;
var currentData = startDate;
int i = DateTime.Compare(currentData, endDate);
while (i <= 0)
{
StringBuilder comm = new StringBuilder();
comm.AppendFormat(@"select sum(count) totalcount from YLTEST.{0} where user_type = 0 AND STAT_DATE <=:currDate", StatisticConsts.STAT_MKT31_SUMMARY);
var queryCommand = new QueryCommand(comm.ToString(), provider);
queryCommand.AddParameter("currDate", currentData.ToString("yyyyMMdd").ToInt32(), DbType.Int32);
var ds = provider.ExecuteDataSet(queryCommand);
if (ds != null && ds.Tables.Count == 1)
{
var dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
result.Add(currentData, string.IsNullOrEmpty(dt.Rows[0]["totalcount"].ToString()) ? 0 : long.Parse(dt.Rows[0]["totalcount"].ToString()));
}
}
else {
result.Add(currentData, 0);
}
currentData = currentData.AddDays(1);
i = DateTime.Compare(currentData, endDate);
}
}
return result;
}
public static DateTime? GetUserLatestOrderDate(int userID, int companyID)
{
var cmd = new QueryCommand("Select top 1 OrderDate from Orders where OrderStatus <> @OrderStatus and OrderSellerID = @OrderSellerID and OrderMemberID = @OrderMemberID order by id desc");
cmd.AddParameter("@OrderStatus", (int)OrderStatus.Canceled, DbType.Int32);
cmd.AddParameter("@OrderSellerID", companyID, DbType.Int32);
cmd.AddParameter("@OrderMemberID", userID, DbType.Int32);
var val = DataService.ExecuteScalar(cmd);
if (Utilities.IsNull(val))
return null;
else
return Convert.ToDateTime(val);
}
public Dictionary<string, long> SearchAppStatCountSorted(SearchAppStatCriteria c, string action = "PV", string orderByAction = "DOWN", int startNum = 1, int pageNum = 20)
{
Dictionary<string, long> result = new Dictionary<string, long>();
if ((action.EqualsOrdinalIgnoreCase("PV")
|| action.EqualsOrdinalIgnoreCase("DOWN")
|| action.EqualsOrdinalIgnoreCase("DOWN_OK"))
&& (orderByAction.EqualsOrdinalIgnoreCase("PV")
|| orderByAction.EqualsOrdinalIgnoreCase("DOWN")
|| orderByAction.EqualsOrdinalIgnoreCase("DOWN_OK")))
{
StringBuilder commPV = new StringBuilder();
commPV.AppendFormat(@"SELECT APP_NO, SUM(T.COUNT) PVCOUNT FROM YLTEST.STAT_APP_{0} T WHERE T.STATISTIC_DATE >=:startDate AND T.STATISTIC_DATE <=:endDate ",
action);
StringBuilder commDown = new StringBuilder();
commDown.AppendFormat(@"SELECT APP_NO, SUM(T.COUNT) DOWNCOUNT FROM YLTEST.STAT_APP_{0} T WHERE T.STATISTIC_DATE >=:startDate2 AND T.STATISTIC_DATE <=:endDate2 ",
orderByAction);
if (!string.IsNullOrEmpty(c.Firmware))
{
commPV.AppendFormat(" and T.FIRMWARE_MODE = :firmware ");
commDown.AppendFormat(" and T.FIRMWARE_MODE = :firmware2 ");
}
if (!string.IsNullOrEmpty(c.Manufacturer))
{
commPV.AppendFormat(" and T.MANUFACTURER = :pf");
commDown.AppendFormat(" and T.MANUFACTURER = :pf2");
}
if (!string.IsNullOrEmpty(c.From) && !c.From.EqualsOrdinalIgnoreCase("none"))
{
commPV.AppendFormat(" and T.COME_FROM = :from1");
commDown.AppendFormat(" and T.COME_FROM = :from2");
}
commPV.AppendFormat(" group by app_no ");
commDown.AppendFormat(" group by app_no ");
//comm.AppendFormat("group by app_no ) )where {0}<=rn and rn<{1}", startNum, startNum + pageNum);
StringBuilder comm = new StringBuilder();
comm.AppendFormat(@"select APPNO,PVCOUNTB from (select rownum rn,APPNO,PVCOUNTB,DOWNCOUNTB from(select * from (select nvl(a.APP_NO,b.APP_NO) APPNO,nvl(a.PVCOUNT,0) PVCOUNTB,nvl(b.DOWNCOUNT,0) DOWNCOUNTB from ({0}) a full join ({1}) b on A.APP_NO = b.APP_NO) order by DOWNCOUNTB desc))where rn>=:startNum and rn <:endNum", commPV.ToString(), commDown.ToString());
var provider = ProviderFactory.GetProvider(ConnectionStrings.Key_ORACLE_LOG);
var queryCommand = new QueryCommand(comm.ToString(), provider);
queryCommand.AddParameter("startDate", c.StartDate.ToString("yyyyMMdd").ToInt32(), DbType.Int32);
queryCommand.AddParameter("endDate", c.EndDate.ToString("yyyyMMdd").ToInt32(), DbType.Int32);
if (!string.IsNullOrEmpty(c.Firmware))
{
queryCommand.AddParameter("firmware", c.Firmware, DbType.String);
}
if (!string.IsNullOrEmpty(c.Manufacturer))
{
queryCommand.AddParameter("pf", c.Manufacturer, DbType.String);
}
if (!string.IsNullOrEmpty(c.From) && !c.From.EqualsOrdinalIgnoreCase("none"))
{
queryCommand.AddParameter("from1", c.From, DbType.String);
}
queryCommand.AddParameter("startDate2", c.StartDate.ToString("yyyyMMdd").ToInt32(), DbType.Int32);
queryCommand.AddParameter("endDate2", c.EndDate.ToString("yyyyMMdd").ToInt32(), DbType.Int32);
if (!string.IsNullOrEmpty(c.Firmware))
{
queryCommand.AddParameter("firmware2", c.Firmware, DbType.String);
}
if (!string.IsNullOrEmpty(c.Manufacturer))
{
queryCommand.AddParameter("pf2", c.Manufacturer, DbType.String);
}
if (!string.IsNullOrEmpty(c.From) && !c.From.EqualsOrdinalIgnoreCase("none"))
{
queryCommand.AddParameter("from2", c.From, DbType.String);
}
if (pageNum != 0)
{
queryCommand.AddParameter("startNum", startNum, DbType.Int32);
queryCommand.AddParameter("endNum", startNum + pageNum, DbType.Int32);
}
var ds = provider.ExecuteDataSet(queryCommand);
if (ds != null && ds.Tables.Count == 1)
{
var dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
long count = 0;
long.TryParse(row["PVCOUNTB"].ToString(), out count);
result.Add(row["APPNO"].ToString(), count);
}
}
}
}
return result;
}
public Dictionary<string, long> SearchForAllAppStatCount(SearchAppStatCriteria c, string action = "PV", int startNum = 1, int pageNum = 20)
{
Dictionary<string, long> result = new Dictionary<string, long>();
if (action.EqualsOrdinalIgnoreCase("PV")
|| action.EqualsOrdinalIgnoreCase("DOWN")
|| action.EqualsOrdinalIgnoreCase("DOWN_OK"))
{
StringBuilder comm = new StringBuilder();
comm.AppendFormat(@" select App_No,Count from (select rownum rn,app_no,count from ( SELECT APP_NO, SUM(T.COUNT) COUNT FROM YLTEST.STAT_APP_{0} T WHERE T.STATISTIC_DATE >=:startDate AND T.STATISTIC_DATE <=:endDate ", action);
if (!string.IsNullOrEmpty(c.Firmware))
{
comm.AppendFormat(" and T.FIRMWARE_MODE = :firmware ");
}
if (!string.IsNullOrEmpty(c.Manufacturer))
{
comm.AppendFormat(" and T.MANUFACTURER = :pf ");
}
if (!string.IsNullOrEmpty(c.From) && !c.From.EqualsOrdinalIgnoreCase("none"))
{
comm.AppendFormat(" and T.COME_FROM = :from1 ");
}
if (pageNum == 0)
{
comm.AppendFormat("group by app_no ) )");
}
else
{
comm.AppendFormat("group by app_no ) ) where rn>=:startNum and rn<:endNum");
}
var provider = ProviderFactory.GetProvider(ConnectionStrings.Key_ORACLE_LOG);
var queryCommand = new QueryCommand(comm.ToString(), provider);
queryCommand.AddParameter("startDate", c.StartDate.ToString("yyyyMMdd").ToInt32(), DbType.Int32);
queryCommand.AddParameter("endDate", c.EndDate.ToString("yyyyMMdd").ToInt32(), DbType.Int32);
if (!string.IsNullOrEmpty(c.Firmware))
{
queryCommand.AddParameter("firmware", c.Firmware, DbType.String);
}
if (!string.IsNullOrEmpty(c.Manufacturer))
{
queryCommand.AddParameter("pf", c.Manufacturer, DbType.String);
}
if (!string.IsNullOrEmpty(c.From) && !c.From.EqualsOrdinalIgnoreCase("none"))
{
queryCommand.AddParameter("from1", c.From, DbType.String);
}
if (pageNum != 0)
{
queryCommand.AddParameter("startNum", startNum, DbType.Int32);
queryCommand.AddParameter("endNum", startNum + pageNum, DbType.Int32);
}
var ds = provider.ExecuteDataSet(queryCommand);
if (ds != null && ds.Tables.Count == 1)
{
var dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
long count = 0;
long.TryParse(row["COUNT"].ToString(), out count);
result.Add(row["APP_NO"].ToString(), count);
}
}
}
}
return result;
}
public static int UpdateCompanyMenuDate(IEnumerable<int> ids, DateTime dtDate)
{
var sql = "Update Sys_Company Set MenuDate = @MenuDate Where Id in(" + string.Join(",", ids.Select(id => id.ToString( )).ToArray( )) + ");";
var cmd = new QueryCommand(sql);
cmd.AddParameter("@MenuDate", dtDate);
return DataService.ExecuteQuery(cmd);
}