/// <summary> /// get all customerscorerule /// <summary> /// <param name=out emsg>return error message</param> ///<returns>details of all customerscorerule</returns> public BindingCollection <modCustomerScoreRule> GetIList(string traceflaglist, out string emsg) { try { BindingCollection <modCustomerScoreRule> modellist = new BindingCollection <modCustomerScoreRule>(); //Execute a query to read the categories string traceflagwhere = string.Empty; if (!string.IsNullOrEmpty(traceflaglist) && traceflaglist.CompareTo("ALL") != 0) { traceflagwhere = "and trace_flag in ('" + traceflaglist.Replace(",", "','") + "') "; } string sql = "select action_code,action_type,scores,trace_flag,update_user,update_time from customer_score_rule where 1=1 " + traceflagwhere + " order by seq"; using (SqlDataReader rdr = SqlHelper.ExecuteReader(sql)) { while (rdr.Read()) { modCustomerScoreRule model = new modCustomerScoreRule(); model.ActionCode = dalUtility.ConvertToString(rdr["action_code"]); model.ActionType = dalUtility.ConvertToString(rdr["action_type"]); model.Scores = dalUtility.ConvertToDecimal(rdr["scores"]); model.TraceFlag = dalUtility.ConvertToInt(rdr["trace_flag"]); model.UpdateUser = dalUtility.ConvertToString(rdr["update_user"]); model.UpdateTime = dalUtility.ConvertToDateTime(rdr["update_time"]); modellist.Add(model); } } emsg = null; return(modellist); } catch (Exception ex) { emsg = dalUtility.ErrorMessage(ex.Message); return(null); } }
/// <summary> /// get table record /// <summary> /// <param name=actioncode>actioncode</param> /// <param name=out emsg>return error message</param> ///<returns>get a record detail of customerscorerule</returns> public modCustomerScoreRule GetItem(string actioncode, out string emsg) { try { //Execute a query to read the categories string sql = string.Format("select action_code,action_type,scores,trace_flag,update_user,update_time from customer_score_rule where action_code='{0}' order by action_code", actioncode); using (SqlDataReader rdr = SqlHelper.ExecuteReader(sql)) { if (rdr.Read()) { modCustomerScoreRule model = new modCustomerScoreRule(); model.ActionCode = dalUtility.ConvertToString(rdr["action_code"]); model.ActionType = dalUtility.ConvertToString(rdr["action_type"]); model.Scores = dalUtility.ConvertToDecimal(rdr["scores"]); model.TraceFlag = dalUtility.ConvertToInt(rdr["trace_flag"]); model.UpdateUser = dalUtility.ConvertToString(rdr["update_user"]); model.UpdateTime = dalUtility.ConvertToDateTime(rdr["update_time"]); emsg = null; return(model); } else { emsg = "Error on read data"; return(null); } } } catch (Exception ex) { emsg = dalUtility.ErrorMessage(ex.Message); return(null); } }
private void DBGrid_RowValidating(object sender, DataGridViewCellCancelEventArgs e) { if (!rowChanged) { return; } DataGridViewRow row = DBGrid.Rows[e.RowIndex]; try { this.Cursor = Cursors.WaitCursor; string valResult = ValidateRowEntry(e); if (valResult.Length > 0) { row.ErrorText = valResult; e.Cancel = true; if (row.IsNewRow) { row.ErrorText = ""; DBGrid.CancelEdit(); } return; } //action_code,action_name,scores string actioncode = row.Cells[0].Value.ToString().ToUpper(); string actionname = row.Cells[1].Value.ToString(); decimal scores = Convert.ToDecimal(row.Cells[2].Value); modCustomerScoreRule mod = new modCustomerScoreRule(actioncode, actionname, scores, 0, Util.UserId, DateTime.Now); bool ret = _dal.Update(actioncode, mod, out Util.emsg); if (!ret) { MessageBox.Show(Util.emsg, clsTranslate.TranslateString("Failure"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } else { row.Cells[0].Value = row.Cells[0].Value.ToString().ToUpper(); row.Cells[3].Value = Util.UserId; row.Cells[4].Value = DateTime.Now.ToString(); StatusLabel4.Text = "Update succeed!"; } rowChanged = false; } catch (Exception ex) { MessageBox.Show(ex.Message.ToString(), clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } finally { this.Cursor = Cursors.Default; } }
/// <summary> /// update a customerscorerule /// <summary> /// <param name=actioncode>actioncode</param> /// <param name=mod>model object of customerscorerule</param> /// <param name=out emsg>return error message</param> /// <returns>true/false</returns> public bool Update(string actioncode, modCustomerScoreRule mod, out string emsg) { try { string sql = string.Format("update customer_score_rule set action_type='{0}',scores={1},update_user='******',update_time=getdate() where action_code='{3}'", mod.ActionType, mod.Scores, mod.UpdateUser, actioncode); int i = SqlHelper.ExecuteNonQuery(sql); if (i > 0) { emsg = null; return(true); } else { emsg = "Unknown error when ExecuteNonQuery!"; return(false); } } catch (Exception ex) { emsg = dalUtility.ErrorMessage(ex.Message); return(false); } }
/// <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); } } }
private void toolSave_Click(object sender, EventArgs e) { try { this.Cursor = Cursors.WaitCursor; if (dtpFromTime.Value < Util.modperiod.StartDate) { MessageBox.Show("该日期的数据已锁定,不能更新数据!", clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information); dtpFromTime.Focus(); return; } if (string.IsNullOrEmpty(txtActionMan.Text.Trim())) { MessageBox.Show(clsTranslate.TranslateString("Action Man") + clsTranslate.TranslateString(" can not be null!"), clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation); txtActionMan.Focus(); return; } if (string.IsNullOrEmpty(txtActionSubject.Text.Trim())) { MessageBox.Show(clsTranslate.TranslateString("Action Subject") + clsTranslate.TranslateString(" can not be null!"), clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation); txtActionSubject.Focus(); return; } if (string.IsNullOrEmpty(txtActionContent.Text.Trim())) { MessageBox.Show(clsTranslate.TranslateString("Action Content") + clsTranslate.TranslateString(" can not be null!"), clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation); txtActionContent.Focus(); return; } if (Util.GetStrLength(txtActionContent.Text.Trim()) < 20) { MessageBox.Show(clsTranslate.TranslateString("Action Content") + clsTranslate.TranslateString(" length must >= 20!"), clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation); txtActionContent.Focus(); return; } if (Util.GetStrLength(txtActionContent.Text.Trim()) > 1024) { MessageBox.Show(clsTranslate.TranslateString("Action Content") + clsTranslate.TranslateString(" length must <= 1024!"), clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation); txtActionContent.Focus(); return; } if (string.IsNullOrEmpty(txtVenue.Text.Trim())) { MessageBox.Show(clsTranslate.TranslateString("Venue") + clsTranslate.TranslateString(" can not be null!"), clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation); txtVenue.Focus(); return; } if (string.IsNullOrEmpty(txtObjectName.Text.Trim())) { MessageBox.Show(clsTranslate.TranslateString("Object Name") + clsTranslate.TranslateString(" can not be null!"), clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation); txtObjectName.Focus(); return; } if (string.IsNullOrEmpty(cboActionType.Text.Trim())) { MessageBox.Show(clsTranslate.TranslateString("Action Type") + clsTranslate.TranslateString(" can not be null!"), clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation); cboActionType.Focus(); return; } if (dtpToTime.Value <= dtpFromTime.Value) { MessageBox.Show("截止时间必须大于起始时间!", clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation); dtpToTime.Focus(); return; } modCustomerLog mod = new modCustomerLog(); mod.Id = Convert.ToInt32(txtId.Text); mod.CustId = txtCustId.Text.Trim(); mod.CustName = txtCustName.Text.Trim(); mod.ActionMan = txtActionMan.Text.Trim(); mod.ActionCode = cboActionType.SelectedValue.ToString(); mod.ActionType = cboActionType.Text.Replace("加分", ""); mod.ActionSubject = txtActionSubject.Text.Trim(); mod.ActionContent = txtActionContent.Text.Trim(); mod.ObjectName = txtObjectName.Text.Trim(); mod.Venue = txtVenue.Text.Trim(); mod.AdFlag = 1; mod.TraceFlag = 1; mod.FromTime = dtpFromTime.Text; mod.ToTime = dtpToTime.Text; mod.UpdateUser = Util.UserId; dalCustomerScoreRule dalcsr = new dalCustomerScoreRule(); modCustomerScoreRule modcsr = dalcsr.GetItem(mod.ActionCode, out Util.emsg); mod.Scores = modcsr.Scores; bool ret; if (_action == "ADD" || _action == "NEW") { ret = _dal.Insert(mod, out Util.emsg); } else { ret = _dal.Update(mod.Id, mod, out Util.emsg); } if (ret) { this.DialogResult = DialogResult.OK; this.Dispose(); } else { MessageBox.Show(Util.emsg, clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information); } } catch (Exception ex) { MessageBox.Show(ex.Message, clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information); return; } finally { this.Cursor = Cursors.Default; } }
/// <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(); } } }
private void LoadData() { try { this.Cursor = Cursors.WaitCursor; string fromtime = txtContent0101.Tag.ToString(); string totime = txtContent0607.Tag.ToString(); if (rbSchedule.Checked) { dalCrmActionSchedule dal = new dalCrmActionSchedule(); BindingCollection <modCrmActionSchedule> list = dal.GetIList(string.Empty, txtSalesMan.Text, fromtime, totime, out Util.emsg); if (list != null && list.Count > 0) { foreach (modCrmActionSchedule mod in list) { bool find = false; for (int i = 0; i < 6; i++) { for (int j = 0; j < 7; j++) { string txtName = "txtContent" + (i + 1).ToString().PadLeft(2, '0') + (j + 1).ToString().PadLeft(2, '0'); TextBox txt = (TextBox)(this.Controls.Find(txtName, true))[0]; if (txt.GetType().ToString() == "System.Windows.Forms.TextBox" && txt.Tag != null && txt.Tag.ToString() == mod.ActionDate.ToString("yyyy-MM-dd")) { txt.Text = mod.ActionContent; if (mod.Status == 0) { txt.ReadOnly = false; txt.ForeColor = Color.Black; } else if (mod.Status == 1) { txt.ReadOnly = true; txt.ForeColor = Color.DarkGoldenrod; } else { txt.ReadOnly = true; txt.ForeColor = Color.DarkGray; } txt.ContextMenuStrip = contextMenuStrip1; find = true; break; } } if (find) { break; } } } } } else if (rbCustomerLog.Checked) { string actioncodelist = string.Empty; if (lstActionCode.SelectedItems.Count > 0 && lstActionCode.SelectedItems.Count < lstActionCode.Items.Count) { for (int i = 0; i < lstActionCode.SelectedItems.Count; i++) { modCustomerScoreRule mod = (modCustomerScoreRule)lstActionCode.SelectedItems[i]; if (i == 0) { actioncodelist = mod.ActionCode; } else { actioncodelist += "," + mod.ActionCode; } } } dalCustomerLog dal = new dalCustomerLog(); BindingCollection <modCustomerDailyLog> list = dal.GetCustomerDailyLog(actioncodelist, txtSalesMan.Text, fromtime, totime, out Util.emsg); if (list != null && list.Count > 0) { foreach (modCustomerDailyLog mod in list) { bool find = false; for (int i = 0; i < 6; i++) { for (int j = 0; j < 7; j++) { string txtName = "txtContent" + (i + 1).ToString().PadLeft(2, '0') + (j + 1).ToString().PadLeft(2, '0'); TextBox txt = (TextBox)(this.Controls.Find(txtName, true))[0]; if (txt.GetType().ToString() == "System.Windows.Forms.TextBox" && txt.Tag != null && txt.Tag.ToString() == mod.ActionDate) { txt.Text = mod.ActionContent; find = true; break; } } if (find) { break; } } } } } else { string actioncodelist = string.Empty; if (lstActionCode.SelectedItems.Count > 0 && lstActionCode.SelectedItems.Count < lstActionCode.Items.Count) { for (int i = 0; i < lstActionCode.SelectedItems.Count; i++) { modCustomerScoreRule mod = (modCustomerScoreRule)lstActionCode.SelectedItems[i]; if (i == 0) { actioncodelist = mod.ActionCode; } else { actioncodelist += "," + mod.ActionCode; } } } dalCustomerLog dal = new dalCustomerLog(); BindingCollection <modCustomerDailyScores> list = dal.GetCustomerDailyScores(actioncodelist, txtSalesMan.Text, fromtime, totime, out Util.emsg); if (list != null && list.Count > 0) { foreach (modCustomerDailyScores mod in list) { bool find = false; for (int i = 0; i < 6; i++) { for (int j = 0; j < 7; j++) { string txtName = "txtContent" + (i + 1).ToString().PadLeft(2, '0') + (j + 1).ToString().PadLeft(2, '0'); TextBox txt = (TextBox)(this.Controls.Find(txtName, true))[0]; if (txt.GetType().ToString() == "System.Windows.Forms.TextBox" && txt.Tag != null && txt.Tag.ToString() == mod.ActionDate) { txt.Text = "积分:" + mod.Scores.ToString(); find = true; break; } } if (find) { break; } } } } } } catch (Exception ex) { MessageBox.Show(ex.Message, clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information); return; } finally { this.Cursor = Cursors.Default; } }