public static KaMiViewModel SearchById(int id)
{
string sql = "select * from Kamis where id=" + id;
KaMiViewModel model = new KaMiViewModel();
ManagerSqlite.GetSQLiteDataReader(sql, null, new IDbDataReaderCallBack(delegate(DbDataReader reader)
{
if (reader != null)
{
while (reader.Read())
{
model.Id = Convert.ToInt32(reader[EKamis.Id.ToString()]);
model.Content = reader[EKamis.Content.ToString()].ToString();
model.State = Convert.ToInt32(reader[EKamis.State.ToString()]);
model.Product_Id = Convert.ToInt32(reader[EKamis.Product_Id.ToString()]);
model.ProductType_Id = Convert.ToInt32(reader[EKamis.ProductType_Id.ToString()]);
model.AddedTime = Convert.ToDateTime(reader[EKamis.AddedTime.ToString()]);
model.UpdateTime = Convert.ToDateTime(reader[EKamis.UpdateTime.ToString()]);
model.Trade_No = reader[EKamis.Trade_No.ToString()].ToString();
model.Remark = reader[EKamis.Remark.ToString()].ToString();
}
}
}
));
return(model);
}
public static List <KaMiViewModel> SearchBysql(string name, string typeId, string Id)
{
string sql = "select a.*,b.Name,c.ProductName FROM Kamis a JOIN Products b on a.Product_Id = b.Id JOIN ProductTypes c on a.ProductType_Id = c.Id where a.[ProductType_Id]=" + typeId + " and a.[Product_Id]=" + Id + " and a.[Content] like '%" + name + "%'";
List <KaMiViewModel> modelArr = new List <KaMiViewModel>();
ManagerSqlite.GetSQLiteDataReader(sql, null, new IDbDataReaderCallBack(delegate(DbDataReader reader)
{
if (reader != null)
{
while (reader.Read())
{
KaMiViewModel model = new KaMiViewModel();
model.Id = Convert.ToInt32(reader[EKamis.Id.ToString()]);
model.Content = reader[EKamis.Content.ToString()].ToString();
model.State = Convert.ToInt32(reader[EKamis.State.ToString()]);
model.Product_Id = Convert.ToInt32(reader[EKamis.Product_Id.ToString()]);
model.ProductName = reader["Name"].ToString();
model.ProductType_Id = Convert.ToInt32(reader[EKamis.ProductType_Id.ToString()]);
model.ProductTypeName = reader["ProductName"].ToString();
model.AddedTime = Convert.ToDateTime(reader[EKamis.AddedTime.ToString()]);
model.UpdateTime = Convert.ToDateTime(reader[EKamis.UpdateTime.ToString()]);
model.Trade_No = reader[EKamis.Trade_No.ToString()].ToString();
model.Remark = reader[EKamis.Remark.ToString()].ToString();
modelArr.Add(model);
}
}
}
));
return(modelArr);
}
public ActionResult Add()
{
string name = Request.Form["proT"];
if (string.IsNullOrEmpty(name))
{
return(Content(ReturnMsg.empty.ToString()));
}
KaMiViewModel model = LitJson.JsonMapper.ToObject <KaMiViewModel>(name);
if (model == null)
{
return(Content(ReturnMsg.fail.ToString()));
}
if (model.Content.Contains("\n"))
{
//批量操作
string kamis = model.Content;
string[] contentArr = kamis.Split(new char[] { '\n' }, StringSplitOptions.RemoveEmptyEntries);
List <KaMiViewModel> kamiModes = new List <KaMiViewModel>();
foreach (var item in contentArr)
{
KaMiViewModel subModel = new KaMiViewModel();
subModel.Product_Id = model.Product_Id;
subModel.ProductType_Id = model.ProductType_Id;
subModel.Remark = model.Remark;
subModel.Content = item;
kamiModes.Add(subModel);
}
if (BKaMi.Insert(kamiModes))
{
return(Content(ReturnMsg.success.ToString()));
}
else
{
return(Content(ReturnMsg.fail.ToString()));
}
}
else
{
//单个操作
model.AddedTime = DateTime.Now;
model.UpdateTime = DateTime.Now;
if (BKaMi.Insert(model))
{
return(Content(ReturnMsg.success.ToString()));
}
else
{
return(Content(ReturnMsg.fail.ToString()));
}
}
}
public ActionResult KaMiList()
{
//查询卡密，并显示
string tNo = Request.QueryString["tNo"];
OrderViewModel order = BOrder.SearchByTradeNo(tNo);
if (order == null)
{
return(View());
}
Dictionary <int, string> kamis = BKaMi.SearchKamiByTrade(order);
#region 更新卡密状态为已使用
List <KaMiViewModel> models = new List <KaMiViewModel>();
foreach (var item in kamis)
{
KaMiViewModel ka = new KaMiViewModel();
ka.Id = item.Key;
ka.Remark = order.Remark;
ka.Trade_No = tNo;
models.Add(ka);
}
BKaMi.UpdateBySql(models);
#endregion
string dir = Server.MapPath("~/Files");
string path = dir + "/" + tNo + ".csv";
using (StreamWriter writer = new StreamWriter(path, true, Encoding.UTF8))
{
string title = "订单号,卡密内容,联系方式\r\n";
writer.Write(title);
string content = "";
foreach (var item in kamis)
{
content += tNo + "," + item.Value + "," + order.Remark + "\r\n";
}
writer.Write(content);
}
ViewBag.tNo = tNo;
return(View(kamis));
}
public ActionResult Update()
{
string newName = Request.Form["proM"];
KaMiViewModel model = LitJson.JsonMapper.ToObject <KaMiViewModel>(newName);
model.UpdateTime = DateTime.Now;
if (model == null)
{
return(Content(ReturnMsg.fail.ToString()));
}
if (string.IsNullOrEmpty(model.Content))
{
return(Content(ReturnMsg.empty.ToString()));
}
if (BKaMi.Update(model))
{
return(Content(ReturnMsg.success.ToString()));
}
else
{
return(Content(ReturnMsg.fail.ToString()));
}
}
public static bool Update(KaMiViewModel model)
{
if (model == null)
{
return(false);
}
StringBuilder sb = new StringBuilder(2048);
List <SQLiteParameter> ps = new List <SQLiteParameter>();
sb.Append("UPDATE Kamis SET ");
sb.Append(EKamis.Content.ToString() + "=@" + EKamis.Content.ToString() + ",");
ps.Add(new SQLiteParameter()
{
ParameterName = EKamis.Content.ToString(), Value = model.Content
});
sb.Append(EKamis.State.ToString() + "=@" + EKamis.State.ToString() + ",");
ps.Add(new SQLiteParameter()
{
ParameterName = EKamis.State.ToString(), Value = model.State
});
sb.Append(EKamis.Product_Id.ToString() + "=@" + EKamis.Product_Id.ToString() + ",");
ps.Add(new SQLiteParameter()
{
ParameterName = EKamis.Product_Id.ToString(), Value = model.Product_Id
});
sb.Append(EKamis.ProductType_Id.ToString() + "=@" + EKamis.ProductType_Id.ToString() + ",");
ps.Add(new SQLiteParameter()
{
ParameterName = EKamis.ProductType_Id.ToString(), Value = model.ProductType_Id
});
sb.Append(EKamis.Trade_No.ToString() + "=@" + EKamis.Trade_No.ToString() + ",");
ps.Add(new SQLiteParameter()
{
ParameterName = EKamis.Trade_No.ToString(), Value = model.Trade_No
});
sb.Append(EKamis.Remark.ToString() + "=@" + EKamis.Remark.ToString() + ",");
ps.Add(new SQLiteParameter()
{
ParameterName = EKamis.Remark.ToString(), Value = model.Remark
});
sb.Append(EKamis.UpdateTime.ToString() + "=@" + EKamis.UpdateTime.ToString());
ps.Add(new SQLiteParameter()
{
ParameterName = EKamis.UpdateTime.ToString(), Value = model.UpdateTime
});
sb.Append(" where ");
sb.Append(EKamis.Id.ToString() + "=@" + EKamis.Id.ToString());
ps.Add(new SQLiteParameter()
{
ParameterName = EKamis.Id.ToString(), Value = model.Id
});
string sql = sb.ToString();
int count = ManagerSqlite.ExecuteNonQuery(sql, ps.ToArray());
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
public static bool Insert(KaMiViewModel model)
{
if (model == null)
{
return(false);
}
string sql = string.Empty;
List <SQLiteParameter> ps = new List <SQLiteParameter>();
sql = "INSERT INTO Kamis ("
+ EKamis.Content.ToString() + ","
+ EKamis.State.ToString() + ","
+ EKamis.Product_Id.ToString() + ","
+ EKamis.ProductType_Id.ToString() + ","
+ EKamis.Trade_No.ToString() + ","
+ EKamis.Remark.ToString() + ","
+ EKamis.AddedTime.ToString() + ","
+ EKamis.UpdateTime.ToString()
+ ") VALUES ("
+ "@" + EKamis.Content.ToString() + ","
+ "@" + EKamis.State.ToString() + ","
+ "@" + EKamis.Product_Id.ToString() + ","
+ "@" + EKamis.ProductType_Id.ToString() + ","
+ "@" + EKamis.Trade_No.ToString() + ","
+ "@" + EKamis.Remark.ToString() + ","
+ "@" + EKamis.AddedTime.ToString() + ","
+ "@" + EKamis.UpdateTime.ToString()
+ ");";
ps.Add(new SQLiteParameter()
{
ParameterName = EKamis.Id.ToString(), Value = model.Id
});
ps.Add(new SQLiteParameter()
{
ParameterName = EKamis.Content.ToString(), Value = model.Content
});
ps.Add(new SQLiteParameter()
{
ParameterName = EKamis.State.ToString(), Value = model.State
});
ps.Add(new SQLiteParameter()
{
ParameterName = EKamis.Product_Id.ToString(), Value = model.Product_Id
});
ps.Add(new SQLiteParameter()
{
ParameterName = EKamis.ProductType_Id.ToString(), Value = model.ProductType_Id
});
ps.Add(new SQLiteParameter()
{
ParameterName = EKamis.Trade_No.ToString(), Value = model.Trade_No
});
ps.Add(new SQLiteParameter()
{
ParameterName = EKamis.Remark.ToString(), Value = model.Remark
});
ps.Add(new SQLiteParameter()
{
ParameterName = EKamis.AddedTime.ToString(), Value = model.AddedTime
});
ps.Add(new SQLiteParameter()
{
ParameterName = EKamis.UpdateTime.ToString(), Value = model.UpdateTime
});
int count = ManagerSqlite.ExecuteNonQuery(sql, ps.ToArray());
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
