public void ShuaXin()
{
SqlCommand com = new SqlCommand("delete from priceavg", DBHelper.con);
DBHelper.con.Open();
com.ExecuteNonQuery();
DBHelper.con.Close();
pros = new List<ProPrice>();
com.CommandText = "select proid from proprice where newprice=1";
DBHelper.con.Open();
SqlDataReader re = com.ExecuteReader();
ProPrice pp = null;
while (re.Read())
{
pp = new ProPrice() { ProID = Convert.ToInt32(re["ProID"]) };
pros.Add(pp);
}
re.Close();
DBHelper.con.Close();
//刷新上个月价格
for (int i = 0; i < pros.Count; i++)
{
com.CommandText = string.Format("select ptype from product where id={0} ", pros[i].ProID);
com.Connection = DBHelper.con;
DBHelper.con.Open();
if (com.ExecuteScalar() == null)
{
DBHelper.con.Close();
continue;
}
int id = (int)com.ExecuteScalar();
DBHelper.con.Close();
DateTime beginTime = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-01")).AddMonths(-1); //获取本月的月份然后减去一个月
DateTime finishTime = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-01")).AddDays(-1);
if (set.Tables["Avg1"] != null)
{
set.Tables.Remove("Avg1");
}
string sql = string.Format("select " + getSqlWhere(id) + " from proprice where proid={0} and addtime between '{1}' and '{2}'", pros[i].ProID, beginTime, finishTime);
adapter = new SqlDataAdapter(sql, DBHelper.con);
adapter.Fill(set, "Avg1");
DataTable dt = set.Tables["Avg1"];
string[] strs = new string[dt.Columns.Count + 1];
int count1 = 0;
if (dt.Columns.Count == 1)
{
strs = new string[dt.Columns.Count + 1];
}
double avg = 0;
for (int j = 0; j < dt.Columns.Count; j++)
{
double num = 0;
int count = 0;
for (int c = 0; c < dt.Rows.Count; c++)
{
if (dt.Rows[c][j] is DBNull || dt.Rows[c][j] == null)
{
count++;
continue;
}
if ((dt.Rows[c][j]).ToString() == "非数字")
{
count++;
continue;
}
if (Convert.ToDouble(dt.Rows[c][j]) == 0)
{
count++;
continue;
}
num += Convert.ToDouble(dt.Rows[c][j]);
}
strs[j] = Math.Round(num / (dt.Rows.Count - count), 2).ToString();
if (strs[j] == "非数字")
{
count1++;
}
else
{
avg += Convert.ToDouble(strs[j]);
}
}
strs[strs.Length - 1] = Math.Round((avg / (strs.Length - 1 - count1)), 2).ToString();
com.CommandText = string.Format("select count(*) from product where id={0} ", pros[i].ProID);
DBHelper.con.Open();
if ((int)com.ExecuteScalar() == 0)
{
DBHelper.con.Close();
continue;
}
DBHelper.con.Close();
com.CommandText = string.Format("select IsSeason,ProDescription,ID,ProductImage,Spec,ProAddress,MaoZhong,JingZhong from product where id={0}", Convert.ToInt32(pros[i].ProID));
DBHelper.con.Open();
re = com.ExecuteReader();
Model.Product p1 = null;
if (re.Read())
{
p1 = new Model.Product()
{
IsSeason = Convert.ToInt32(re["IsSeason"]),
ProDescription = re["ProDescription"].ToString(),
ID = Convert.ToInt32(re["ID"]),
ProductImage = re["ProductImage"].ToString(),
Spec = re["Spec"].ToString(),
ProAddress = re["ProAddress"].ToString(),
MaoZhong = re["MaoZhong"].ToString(),
JingZhong = re["JingZhong"].ToString()
};
}
re.Close();
DBHelper.con.Close();
string sqlstr = @"INSERT INTO priceAvg ([IsSeason],[ProDescription],[编号],[图片],[品名],[规格],[产地],[毛重],[净重],[isnoBiaoZhunJin],[isnoBiaoZhunJinls]," + insertSql(id) + ",timetype) values('" + p1.IsSeason + "','" + p1.ProDescription + "','" + p1.ID + "','" + p1.ProductImage + "','" + PinMing(Convert.ToInt32(pros[i].ProID)) + "','" + p1.Spec + "','" + p1.ProAddress + "','" + p1.MaoZhong + "','";
sqlstr = sqlstr + p1.JingZhong + "','" + 0 + "','" + 0 + "','";
if (insertSql(id).Trim() == "pavgprice")
{
strs = new string[] { strs[0] };
}
int nb = 0;
for (int m = 0; m < strs.Length; m++)
{
if (strs[m] == "非数字")
{
strs[m] = "0";
}
if (strs[m] == null)
{
strs[m] = "0";
}
if (strs[m] == "0")
{
nb++;
}
sqlstr = sqlstr + strs[m] + "','";
}
if (nb == strs.Length)
{
continue;
}
sqlstr = sqlstr + 1 + "')";
com.CommandText = sqlstr;
DBHelper.con.Open();
com.ExecuteNonQuery();
DBHelper.con.Close();
}
}
