/// <summary>
/// insert a salesshipment
/// <summary>
/// <param name=mod>model object of salesshipment</param>
/// <param name=out emsg>return error message</param>
/// <returns>true/false</returns>
public bool Save(string oprtype, modQuotationForm mod, BindingCollection <modQuotationDetail> list, out string emsg)
{
using (TransactionScope transaction = new TransactionScope())//使用事务
{
try
{
string sql = string.Empty;
string formid = mod.FormId;
string formno = mod.No;
string content = string.Empty;
string salesman = string.Empty;
string actioncode = "QUOTATION";
dalCustomerList dalcust = new dalCustomerList();
salesman = dalcust.GetSalesMan(mod.CustId);
dalCustomerScoreRule dalcsr = new dalCustomerScoreRule();
modCustomerScoreRule modcsr = dalcsr.GetItem(actioncode, out emsg);
int?seq = 0;
switch (oprtype)
{
case "ADD":
case "NEW":
if (Exists(formid, out emsg))
{
formid = GetNewId(mod.FormDate);
}
sql = string.Format("insert into quotation_form(form_id,form_date,no,cust_id,remark,currency,contact_person,update_user,update_time)values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}',getdate())", formid, mod.FormDate, mod.No, mod.CustId, mod.Remark, mod.Currency, mod.ContactPerson, mod.UpdateUser);
SqlHelper.ExecuteNonQuery(sql);
if (list != null && list.Count > 0)
{
seq = 0;
foreach (modQuotationDetail modd in list)
{
seq++;
sql = string.Format("insert into quotation_detail(form_id,seq,product_id,product_name,specify,brand,unit_no,qty,price,remark,size)values('{0}',{1},'{2}','{3}','{4}','{5}','{6}',{7},{8},'{9}', 1)", formid, seq, modd.ProductId, modd.ProductName, modd.Specify, modd.Brand, modd.UnitNo, modd.Qty, modd.Price, modd.Remark);
SqlHelper.ExecuteNonQuery(sql);
if (string.IsNullOrEmpty(content))
{
content = "产品:" + modd.ProductName + " 规格:" + modd.Specify + " 价格:" + modd.Price.ToString() + "/" + modd.UnitNo;
}
else
{
content += "\r\n产品:" + modd.ProductName + " 规格:" + modd.Specify + " 价格:" + modd.Price.ToString() + "/" + modd.UnitNo;
}
}
}
sql = string.Format("insert into customer_log(cust_id,cust_name,action_code,action_type,action_man,form_id,action_subject,action_content,object_name,venue,from_time,to_time,scores,ad_flag,update_user,update_time)values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}',{12},{13},'{14}',getdate())", mod.CustId, mod.CustName, actioncode, "客户报价", salesman, formid, string.Empty, content, string.Empty, string.Empty, mod.FormDate, mod.FormDate, modcsr.Scores, 1, mod.UpdateUser);
SqlHelper.ExecuteNonQuery(sql);
break;
case "EDIT":
case "UPDATE":
case "MODIFY":
sql = string.Format("update quotation_form set form_date='{0}',no='{1}',cust_id='{2}',remark='{3}',currency='{4}',contact_person='{5}' where form_id='{6}'", mod.FormDate, mod.No, mod.CustId, mod.Remark, mod.Currency, mod.ContactPerson, formid);
SqlHelper.ExecuteNonQuery(sql);
sql = string.Format("delete quotation_detail where form_id='{0}'", formid);
SqlHelper.ExecuteNonQuery(sql);
seq = 0;
foreach (modQuotationDetail modd in list)
{
seq++;
sql = string.Format("insert into quotation_detail(form_id,seq,product_id,product_name,specify,brand,unit_no,qty,price,remark,size)values('{0}',{1},'{2}','{3}','{4}','{5}','{6}',{7},{8},'{9}', 1)", formid, seq, modd.ProductId, modd.ProductName, modd.Specify, modd.Brand, modd.UnitNo, modd.Qty, modd.Price, modd.Remark);
SqlHelper.ExecuteNonQuery(sql);
if (string.IsNullOrEmpty(content))
{
content = "产品:" + modd.ProductName + " 规格:" + modd.Specify + " 价格:" + modd.Price.ToString() + "/" + modd.UnitNo;
}
else
{
content += "\r\n产品:" + modd.ProductName + " 规格:" + modd.Specify + " 价格:" + modd.Price.ToString() + "/" + modd.UnitNo;
}
}
sql = string.Format("update customer_log set cust_id='{0}',cust_name='{1}',action_type='{2}',action_man='{3}',action_subject='{4}',action_content='{5}',object_name='{6}',venue='{7}',from_time='{8}',to_time='{9}',scores={10},ad_flag={11},update_user='******',update_time=getdate() where action_code='{13}' and form_id='{14}' ", mod.CustId, mod.CustName, "客户报价", salesman, string.Empty, content, string.Empty, string.Empty, mod.FormDate, mod.FormDate, modcsr.Scores, 1, mod.UpdateUser, actioncode, formid);
SqlHelper.ExecuteNonQuery(sql);
break;
case "DEL":
case "DELETE":
sql = string.Format("delete customer_log where action_code='{0}' and form_id='{1}'", actioncode, mod.FormId);
SqlHelper.ExecuteNonQuery(sql);
sql = string.Format("delete quotation_detail where form_id='{0}'", mod.FormId);
SqlHelper.ExecuteNonQuery(sql);
sql = string.Format("delete quotation_form where form_id='{0}'", mod.FormId);
SqlHelper.ExecuteNonQuery(sql);
break;
}
transaction.Complete();//就这句就可以了。
emsg = formid;
return(true);
}
catch (Exception ex)
{
emsg = dalUtility.ErrorMessage(ex.Message);
return(false);
}
}
}
/// <summary>
/// audit
/// <summary>
/// <param name=id>id</param>
/// <param name=updateuser>updateuser</param>
/// <param name=out emsg>return error message</param>
/// <returns>true/false</returns>
public bool Audit(int id, string updateuser, out string emsg)
{
string sql = string.Empty;
SqlConnection conn = SqlHelper.getConn();
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
SqlCommand cmd = new SqlCommand();
try
{
string content = string.Empty;
string salesman = string.Empty;
string actioncode = "GATHERING";
int adflag = 1;
modAccReceivableForm mod = GetItem(id, out emsg);
if (mod.SubjectId.IndexOf("9135") == 0)
{
actioncode = "BADDEBTS";
adflag = -1;
}
dalCustomerList dalcust = new dalCustomerList();
salesman = dalcust.GetSalesMan(mod.CustId);
dalCustomerScoreRule dalcsr = new dalCustomerScoreRule();
modCustomerScoreRule modcsr = dalcsr.GetItem(actioncode, out emsg);
content = "科目:" + mod.SubjectName + " 实收金额:" + mod.GetMny + " 帐款金额:" + mod.ReceivableMny.ToString() + mod.Currency;
sql = string.Format("update acc_receivable_form set status={0},audit_man='{1}',audit_time=getdate() where id={2}", 1, updateuser, id);
SqlHelper.PrepareCommand(cmd, conn, trans, CommandType.Text, sql, null);
cmd.ExecuteNonQuery();
sql = string.Format("insert into customer_log(cust_id,cust_name,action_code,action_type,action_man,form_id,action_subject,action_content,object_name,venue,from_time,to_time,scores,ad_flag,update_user,update_time)values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}',{12},{13},'{14}',getdate())", mod.CustId, mod.CustName, actioncode, "收货款", salesman, id, string.Empty, content, string.Empty, string.Empty, mod.FormDate, mod.FormDate, modcsr.Scores * mod.GetMny * mod.ExchangeRate, adflag, mod.UpdateUser);
SqlHelper.PrepareCommand(cmd, conn, trans, CommandType.Text, sql, null);
cmd.ExecuteNonQuery();
if (mod.ReceivableMny - mod.GetMny > 0)
{
actioncode = "BADDEBTS";
modcsr = dalcsr.GetItem(actioncode, out emsg);
content = "折扣金额:" + (mod.ReceivableMny - mod.GetMny) + mod.Currency;
sql = string.Format("insert into customer_log(cust_id,cust_name,action_code,action_type,action_man,form_id,action_subject,action_content,object_name,venue,from_time,to_time,scores,ad_flag,update_user,update_time)values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}',{12},{13},'{14}',getdate())", mod.CustId, mod.CustName, actioncode, "货款折扣", salesman, id, string.Empty, content, string.Empty, string.Empty, mod.FormDate, mod.FormDate, modcsr.Scores * (mod.ReceivableMny - mod.GetMny) * mod.ExchangeRate, -1, mod.UpdateUser);
SqlHelper.PrepareCommand(cmd, conn, trans, CommandType.Text, sql, null);
cmd.ExecuteNonQuery();
}
trans.Commit();
emsg = string.Empty;
return(true);
}
catch (Exception ex)
{
trans.Rollback();
emsg = dalUtility.ErrorMessage(ex.Message);
return(false);
}
finally
{
trans.Dispose();
cmd.Dispose();
if (conn.State != ConnectionState.Closed)
{
conn.Dispose();
}
}
}
