/// <summary>
/// 修改部门名称
/// </summary>
/// <param name="depart"></param>
/// <param name="man"></param>
/// <param name="email"></param>
/// <param name="phone"></param>
/// <param name="id"></param>
/// <returns></returns>
public string SaveDepartMentList(string id, string name)
{
string Exist = @"select Department,DepartId from dbo.DepartMent where Department='" + name + "'";
Dictionary <string, object> Diccount = Common.CommonClass.Select(Exist);
if (Diccount != null)
{
return(Utils.GetResult(300, "该部门已存在,请重新添加部门"));
}
else
{
SqlParameter[] par;
par = new SqlParameter[]
{
new SqlParameter("Ddelete", "0"), new SqlParameter("SaveDepartName", name), new SqlParameter("DUpdatedate", DateTime.Now),
};
string UpSql = @"UPDATE [DepartMent] SET [Department] =@SaveDepartName,[Ddelete] = @Ddelete ,[DUpdatedate] = @DUpdatedate WHERE DepartId=" + id + "";
int count = Com.ExecutionSqlPar(UpSql, par);
if (count > 0)
{
return(Utils.GetResult(200, "修改成功"));
}
else
{
return(Utils.GetResult(300, "修改失败"));
}
}
}
/// <summary>
/// 创建订单
/// </summary>
/// <param name="depart"></param>
/// <param name="way"></param>
/// <param name="number"></param>
/// <param name="depot"></param>
/// <param name="man"></param>
/// <param name="supplier"></param>
/// <param name="note"></param>
/// <param name="Date"></param>
/// <returns></returns>
public string PurchaseAdd(string depart, string number, string date, string depot, string man, string supplier, string note, string type, string name, string unitname, string sum, string price)
{
//获得仓库的ID
string SelId = (@"select w.Depotid from dbo.WareHouse
w inner join dbo.WareName n on w.wid= n.wid where n.DepotName = '" + depot + "'");
//获得供应商ID
string SelSupplierId = @"select Supplierid from Purchase p inner join Supplier s on p.Supplierid=s.SId
where s.SCompanyName='" + supplier + "'";
//取出仓库ID
Dictionary <string, object> Decount = Common.CommonClass.Select(SelId);
string decount = string.Empty;
foreach (var key in Decount)
{
decount = key.Value.ToString();
}
//取出供应商ID
Dictionary <string, object> Sucount = Common.CommonClass.Select(SelSupplierId);
string sucount = string.Empty;
foreach (var key in Sucount)
{
sucount = key.Value.ToString();
}
//赋值
SqlParameter[] par = new SqlParameter[] {
new SqlParameter("Depart", depart), new SqlParameter("Name", name),
new SqlParameter("Number", number), new SqlParameter("Depotid", int.Parse(decount)),
new SqlParameter("SupplierId", int.Parse(sucount)), new SqlParameter("Note", note),
new SqlParameter("Date", date), new SqlParameter("Man", man),
new SqlParameter("Type", type), new SqlParameter("PDeleted", "0"),
new SqlParameter("UnitName", unitname), new SqlParameter("Sum", sum),
new SqlParameter("Price", price), new SqlParameter("CheckId", "0")
};
//判断创建的订单号是否存在
string ExistNum = @"select PurChaseNumber from dbo.Purchase where PurChaseNumber='" + number + "'";
Dictionary <string, object> Diccount = Common.CommonClass.Select(ExistNum);
if (Diccount != null)
{
return(Utils.GetResult(300, "订单号已存在"));
}
//添加至数据库
string AddSql = @"insert into Purchase(PurchaseDate,PurchaseNumber,DepartId,PurchaseMan,PurchaseType,PurchaseNote,Supplierid,Depotid,PDeleted,PurchaseName,PurchaseSum,PurchaseMoney,Unitid,CheckId)
values(@Date,@Number,@Depart,@Man,@Type,@Note,@SupplierId,@Depotid,@PDeleted,@Name,@Sum,@Price,@UnitName,@CheckId)";
int count = com.ExecutionSqlPar(AddSql, par);
if (count > 0)
{
return(Utils.GetResult(200, "添加成功"));
}
else
{
return(Utils.GetResult(300, "添加失败"));
}
}
/// <summary>
/// 保存商品详细信息
/// </summary>
/// <param name="Name"></param>
/// <param name="Suppliers"></param>
/// <returns></returns>
public string SaveHoustory(string Name, string Suppliers)
{
int count = 0;
decimal money = 0;
var serialize = new JavaScriptSerializer();
SqlParameter[] par;
List <JsonMerchandise> model = serialize.Deserialize <List <JsonMerchandise> >(Name);
string RandSum = RandNumber();
par = new SqlParameter[] {
new SqlParameter("SuppliersId", Suppliers), new SqlParameter("Deleted", "0"),
new SqlParameter("OrderCreatetime", DateTime.Now), new SqlParameter("Money", money),
new SqlParameter("number", RandSum),
};
string InsertOrderhistory = @"INSERT INTO [Orderhistory]([Count],[TotalMoney],[OrderCreatetime],[Number],[Suppliersid],[Deleted])
VALUES(null,@Money,@OrderCreatetime,@number,@SuppliersId,@Deleted);SELECT @@IDENTITY";//取得最后一次自增ID
int Orderhistoryid = int.Parse(com.ExecuteScalar(InsertOrderhistory, par).ToString());
for (int i = 0; i < model.Count; i++)
{
par = new SqlParameter[] {
new SqlParameter("Mid", model[i].history_id), new SqlParameter("note", model[i].history_note),
new SqlParameter("taotalproce", model[i].history_money), new SqlParameter("Suppliers", Suppliers),
new SqlParameter("sum", model[i].history_sum), new SqlParameter("Number", RandSum),
new SqlParameter("PropertyDelete", "0"), new SqlParameter("CreateTime", DateTime.Now),
new SqlParameter("Orderhistoryid", Orderhistoryid)
};
//历史详情表
string InsertPurchaseProperty = @"INSERT INTO [PurchaseProperty]([MId],[PropertySum],[PropertyToal],[PropertyDelete],[CreateTime],[PropertyNote] ,[Pid] )
VALUES(@Mid,@sum,@taotalproce,@PropertyDelete,@CreateTime,@note,@Orderhistoryid)";
count = com.ExecutionSqlPar(InsertPurchaseProperty, par);
//将进货的商品添加至库存
string insertDetail = @"INSERT INTO [DB_MgooERP].[dbo].[InventoryDetail]
([Supplierid]
,[Mid]
,[InventoryId],Number,Sum,CreateTime)VALUES(@Suppliers,@Mid,null,@Number,@sum,@CreateTime)";
count = com.ExecutionSqlPar(insertDetail, par);
// string UpdateSum = @"Update InventoryDetail set Sum=Sum+@sum where Mid=@Mid";
string UpdateStore = @"UPDATE [dbo].[Inventory] set [InvertorySum] = InvertorySum+@sum WHERE Mid=@Mid";
count = com.ExecutionSqlPar(UpdateStore, par);
}
if (count > 0)
{
return(Utils.GetResult(200, "保存成功", "true"));
}
else
{
return(Utils.GetResult(400, "保存失败"));
}
}
public string SaveOutPut(string list, string accountid)
{
//往出库单表中增加一条数据
int count = 0;
var serialize = new JavaScriptSerializer();
string rand = RandNumber();
SqlParameter[] par;
List <JsonOutPut> model = serialize.Deserialize <List <JsonOutPut> >(list);//反序列化类中字段必须与传入的字段名称对应
par = new SqlParameter[] {
new SqlParameter("RandNumber", rand), new SqlParameter("CreateTime", DateTime.Now),
new SqlParameter("AccountId", accountid), new SqlParameter("Delete", '0'),
};
string OutPut = @"INSERT INTO [OutPut] ([Out_Number],[Out_Create],[Out_Accountid],[Out_Delete])
VALUES(@RandNumber,@CreateTime,@AccountId,@Delete);SELECT @@IDENTITY";//取得最后一次增长的ID
int Out_id = int.Parse(com.ExecuteScalar(OutPut, par).ToString());
for (int i = 0; i < model.Count; i++)
{
//将商品的进行退货，出库详情表单增加两条数据，库存表相应减少
par = new SqlParameter[] {
new SqlParameter("Mid", model[i].out_mid), new SqlParameter("sum", model[i].out_sum), new SqlParameter("TotalMoney", model[i].out_totalmoney),
new SqlParameter("Rand", rand), new SqlParameter("Create", DateTime.Now), new SqlParameter("RealPrice", '0'),
new SqlParameter("Oid", Out_id), new SqlParameter("DetailDelete", '0'), new SqlParameter("OutNote", model[i].out_sum)
};
//取得出库表的ID，将数据添加至出库详情表中
string OutPutDetail = @"INSERT INTO [dbo].[OutPutDetail]([OutDate],[OutNumber],[Mid],[OutSum],[OutTotalMoney],[RealPrice],Out_id,DetailDelete,OutNote)
VALUES(@Create,@Rand,@Mid,@sum,@TotalMoney,@RealPrice,@Oid,@DetailDelete,@OutNote)";
count = com.ExecutionSqlPar(OutPutDetail, par);
//库存数量相应减少
string UpdateStore = @"UPDATE [dbo].[Inventory] set [InvertorySum] = InvertorySum-@sum WHERE Mid=@Mid";
count = com.ExecutionSqlPar(UpdateStore, par);
}
if (count > 0)
{
return(Utils.GetResult(200, "保存成功", "true"));
}
else
{
return(Utils.GetResult(400, "保存失败"));
}
}
/// <summary>
/// 添加客户信息
/// </summary>
/// <param name="NO"></param>
/// <param name="Name"></param>
/// <param name="Comtel"></param>
/// <param name="Money"></param>
/// <param name="Phone"></param>
/// <param name="Email"></param>
/// <param name="Note"></param>
/// <returns></returns>
public string Insert_AccountInfo(string NO, string Name, string Comtel, string Money, string Phone, string Email, string Note)
{
SqlParameter[] par = new SqlParameter[] {
new SqlParameter("AccountNo", NO), new SqlParameter("AccountName", Name),
new SqlParameter("AccountComtel", Comtel), new SqlParameter("AccountMoney", Money),
new SqlParameter("AccountPhone", Phone), new SqlParameter("AccountEmail", Email),
new SqlParameter("AccountNote", Note), new SqlParameter("AccountCreate", DateTime.Now),
new SqlParameter("AccountDelete", "0"),
};
string AddAccountInfo = @"INSERT INTO [dbo].[Account]([AccountNo],[AccountName],[AccountMoney],[AccountComTel],[AccountPhone],[AccountEmail],[AccountNote],[AccountCreate],[AccountDelete])
VALUES(@AccountNo,@AccountName, @AccountMoney,@AccountComtel,@AccountPhone,@AccountEmail,@AccountNote,@AccountCreate, @AccountDelete)";
int count = int.Parse(com.ExecutionSqlPar(AddAccountInfo, par).ToString());
if (count > 0)
{
return(Utils.GetResult(200, "保存成功", "true"));
}
else
{
return(Utils.GetResult(400, "保存失败"));
}
}
/// <summary>
/// 修改方法供应供应商资料
/// </summary>
/// <param name="name"></param>
/// <param name="company"></param>
/// <param name="address"></param>
/// <param name="email"></param>
/// <param name="fax"></param>
/// <param name="phone"></param>
/// <param name="note"></param>
/// <param name="id"></param>
/// <returns></returns>
public string SaveSupplierList(string name, string company, string address, string email, string fax, string phone, string note, string id, string update)
{
SqlParameter[] par;
par = new SqlParameter[] {
new SqlParameter("SContacts", name), new SqlParameter("SCompanyName", company),
new SqlParameter("SAddress", address), new SqlParameter("SPhone", phone),
new SqlParameter("SEmail", email), new SqlParameter("SFax", fax),
new SqlParameter("SDelete", "0"), new SqlParameter("SRemark", note),
new SqlParameter("SUpdateTime", DateTime.Now)
};
string UpSql = @"UPDATE Supplier set SContacts=@SContacts,SCompanyName=@SCompanyName,SAddress=@SAddress, SPhone=@SPhone,SEmail=@SEmail,SFax=@SFax,SDelete=@SDelete,SRemark=@SRemark ,SUpdateTime=@SUpdateTime where Superid=" + id + "";
//and convert(varchar(20), SUpdateTime, 120)= '" + Convert.ToDateTime( update).ToString("yyyy-MM-dd HH:mm:ss") + "'
int count = Com.ExecutionSqlPar(UpSql, par);
if (count > 0)
{
return(Utils.GetResult(200, "保存成功", "true"));
}
else
{
return(Utils.GetResult(300, "保存失败"));
}
}
