internal string sysConfigUpdate(sysConfig sys)
{
string sql = @"UPDATE sysConfig
SET
stockUpdate = @stockUpdate,
otcUpdate = @otcUpdate,
nowDate = @nowDate,
avgStartDate = @avgStartDate,
avgEndDate = @avgEndDate
WHERE id = @id;";
using (var db = new WebStockEntities())
{
if (sys != null)
{
int res = db.Database.ExecuteSqlCommand(sql,
new SqlParameter("@stockUpdate", sys.stockUpdate),
new SqlParameter("@otcUpdate", sys.otcUpdate),
new SqlParameter("@nowDate", sys.nowDate),
new SqlParameter("@avgStartDate", sys.avgStartDate),
new SqlParameter("@avgEndDate", sys.avgEndDate),
new SqlParameter("@id", sys.id)
);
db.SaveChanges();
return("upDate success !");
}
else
{
return("upDate error !");
}
}
}
internal string createFavoriteStock(string code, int operId)
{
int status = 0;
using (var db = new WebStockEntities())
{
var check = db.stockIndex.Where(x => (x.code == code || x.company == code) && x.isEnable == true).FirstOrDefault();
if (check != null)
{
var r = db.stockFavorite.Where(x => x.operId == operId && x.code == check.code).FirstOrDefault();
if (r == null)
{
stockFavorite data = new stockFavorite();
data.operId = operId;
data.code = check.code;
db.stockFavorite.Add(data);
status = db.SaveChanges();
return(status > 0 ? "01" : "02");
}
else
{
//已存在無須新增
return("03");
}
}
else
{
return("02");
}
}
}
public int updatesysConfigotcUpdate(DateTime nextdate)
{
using (var db = new WebStockEntities())
{
var res = db.sysConfig.Where(x => x.id == 1).FirstOrDefault();
res.otcUpdate = nextdate;
return(db.SaveChanges());
}
}
internal bool deleteStockInventory(int id)
{
int status = 0;
using (var db = new WebStockEntities())
{
var d = db.stockProfit.Where(x => x.id == id).FirstOrDefault();
db.stockProfit.Remove(d);
status = db.SaveChanges();
}
return(status > 0 ? true : false);
}
public sysLog createLog(string type, string message)
{
using (var db = new WebStockEntities())
{
sysLog log = new sysLog();
log.date = DateTime.Now;
log.type = type;
log.message = message;
db.sysLog.Add(log);
db.SaveChanges();
return(log);
}
}
internal bool EditSysConfig(sysConfig sys)
{
using (var db = new WebStockEntities())
{
sysConfig d = db.sysConfig.Where(x => x.id == 1).FirstOrDefault();
Mapper.Initialize(cfg => cfg.CreateMap <sysConfig, sysConfig>()
.ForMember(x => x.id, opt => opt.Ignore())
);
Mapper.Map(sys, d);
int r = db.SaveChanges();
return(r >= 0 ? true : false);
}
}
internal string addFavorite(string code, int OperId)
{
using (var db = new WebStockEntities())
{
stockFavorite favorite = new stockFavorite();
favorite.operId = OperId;
favorite.code = code;
db.stockFavorite.Add(favorite);
db.SaveChanges();
return("success");
}
}
internal bool updateFavoriteStockMemo(stockFavorite favorite)
{
int status = 0;
using (var db = new WebStockEntities())
{
var d = db.stockFavorite.Where(x => x.id == favorite.id).FirstOrDefault();
Mapper.Initialize(cfg => cfg.CreateMap <stockFavorite, stockFavorite>()
.ForMember(x => x.id, opt => opt.Ignore())
.ForMember(x => x.operId, opt => opt.Ignore())
.ForMember(x => x.code, opt => opt.Ignore())
);
Mapper.Map(favorite, d);
status = db.SaveChanges();
}
return(status >= 0 ? true : false);
}
internal string Register(member formData)
{
var check = db.member.Where(x => x.account == formData.account).FirstOrDefault();
if (check == null)
{
string strPassword = FormsAuthentication.HashPasswordForStoringInConfigFile(formData.password, "SHA1");
formData.password = strPassword;
formData.role = "user";
db.member.Add(formData);
int r = db.SaveChanges();
return(r > 0 ? "01" : "02");
}
else
{
return("03");
}
}
internal bool createStockInventory(stockProfit profit)
{
bool status = false;
try
{
using (var db = new WebStockEntities())
{
stockIndex company = db.stockIndex.Where(x => x.company == profit.code).FirstOrDefault();
if (company != null)
{
profit.code = company.code;
}
const double buycommision = 1.001425;
const double sellcommision = 0.004425;
const int percentage = 100;
stockProfit stockProfit = new stockProfit();
stockNow stockNow = db.stockNow.Where(x => x.code == profit.code).FirstOrDefault();
if (stockNow == null)
{
return(status);
}
stockProfit.operId = profit.operId;
stockProfit.code = profit.code;
stockProfit.buyPrice = profit.buyPrice;
stockProfit.buyShares = profit.buyShares;
stockProfit.buyCost = (profit.buyPrice * profit.buyShares * buycommision);
stockProfit.profit = (stockNow.closePrice * profit.buyShares * (1 - sellcommision) - (profit.buyPrice * profit.buyShares * buycommision));
stockProfit.profitPercentage = Math.Round((stockProfit.profit / stockProfit.buyCost) * percentage, 2);
db.stockProfit.Add(stockProfit);
db.SaveChanges();
}
status = true;
return(status);
}
catch (Exception ex)
{
return(status);
}
}
internal bool stockNowsStatistics()
{
bool status = false;
try
{
List <stockNowStatistics> stockStatisticsNows = new List <stockNowStatistics>();
using (var db = new WebStockEntities())
{
string sql = @"SELECT
i.code AS code
,dt.dataDate AS dataDate
,dt.closePrice AS closePrice
,a.highestPrice AS highestPrice
,a.lowestPrice AS lowestPrice
FROM stockIndex i
JOIN stockDataTmp dt
ON i.code = dt.code
JOIN stockAvg a
ON i.code = a.code
UNION
SELECT
i.code AS code
,dto.dataDate AS dataDate
,dto.closePrice AS closePrice
,a.highestPrice AS highestPrice
,a.lowestPrice AS lowestPrice
FROM stockIndex i
JOIN stockDataTmpOtc dto
ON i.code = dto.code
JOIN stockAvg a
ON i.code = a.code
ORDER BY i.code";
stockStatisticsNows = db.Database.SqlQuery <stockNowStatistics>(sql).ToList();
//buckCopy Init
DataTable dt = new DataTable();
dt.Columns.Add("id", typeof(Int64));
dt.Columns.Add("code", typeof(string));
dt.Columns.Add("closePrice", typeof(double));
dt.Columns.Add("position", typeof(double));
dt.Columns.Add("dataDate", typeof(DateTime));
foreach (var item in db.stockIndex.ToList())
{
var stockStatisticsNow = stockStatisticsNows.Where(x => x.code == item.code).AsEnumerable();
if (stockStatisticsNow.Count() == 0)
{
continue;
}
foreach (var data in stockStatisticsNow)
{
//BuckCopy寫入結果
DataRow row = dt.NewRow();
row["code"] = data.code;
row["closePrice"] = data.closePrice;
row["position"] = Math.Round((data.closePrice - data.lowestPrice) / (data.highestPrice - data.lowestPrice), 2);
row["dataDate"] = data.dataDate;
dt.Rows.Add(row);
}
}
//再foreach尚未更新前的stockNow list ，找出code不存在本次list，補充寫入上去dt
foreach (var item in db.stockNow.ToList())
{
var stockStatisticsNow = stockStatisticsNows.Where(x => x.code == item.code).AsEnumerable();
if (stockStatisticsNow.Count() == 0)
{
DataRow row = dt.NewRow();
row["code"] = item.code;
row["closePrice"] = item.closePrice;
row["position"] = item.position;
row["dataDate"] = item.dataDate;
dt.Rows.Add(row);
}
}
db.Database.ExecuteSqlCommand(@"truncate table stockNow");
//sqlBulkCopy 寫入資料Table
SqlConnection conn = (SqlConnection)db.Database.Connection;
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
using (var sqlBulkCopy = new SqlBulkCopy((SqlConnection)db.Database.Connection))
{
sqlBulkCopy.DestinationTableName = "dbo.stockNow";
sqlBulkCopy.WriteToServer(dt);
}
var sysConfig = db.sysConfig.FirstOrDefault();
var stocknow = db.stockNow.Where(x => x.id == 1).FirstOrDefault();
sysConfig.nowDate = stocknow.dataDate;
db.SaveChanges();
status = true;
}
return(status);
}
catch (Exception ex)
{
return(status);
}
}
