private void toolEdit_Click(object sender, EventArgs e)
{
try
{
this.Cursor = Cursors.WaitCursor;
if (DBGrid.CurrentRow == null)
{
return;
}
modQuotationForm mod = (modQuotationForm)DBGrid.CurrentRow.DataBoundItem;
EditQuotationForm frm = new EditQuotationForm();
frm.EditItem(mod.FormId);
if (frm.ShowDialog() == DialogResult.OK)
{
LoadData();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
finally
{
this.Cursor = Cursors.Default;
}
}
private void toolExport_Click(object sender, EventArgs e)
{
if (DBGrid.CurrentRow == null)
{
return;
}
IList <modExcelRangeData> list = new List <modExcelRangeData>();
modQuotationForm mod = (modQuotationForm)DBGrid.CurrentRow.DataBoundItem;
BindingCollection <modQuotationDetail> listdetail = _dal.GetDetail(mod.FormId, out Util.emsg);
dalCustomerList dalcust = new dalCustomerList();
modCustomerList modcust = dalcust.GetItem(mod.CustId, out Util.emsg);
//list.Add(new modExcelRangeData(clsLxms.GetParameterValue("COMPANY_NAME"), "B1", "J1"));
list.Add(new modExcelRangeData("电话/TEL: " + clsLxms.GetParameterValue("COMPANY_TEL") + " 传真/FAX: " + clsLxms.GetParameterValue("COMPANY_FAX") + " 联系人/Contact Person: " + mod.ContactPerson, "A4", "I4"));
//list.Add(new modExcelRangeData("公司地址:" + clsLxms.GetParameterValue("COMPANY_ADDR"), "B3", "J3"));
list.Add(new modExcelRangeData(modcust.FullName, "B6", "B6"));
list.Add(new modExcelRangeData("TEL: " + modcust.Linkman + " " + modcust.Tel, "B7", "B7"));
list.Add(new modExcelRangeData("单号Invoice No.: " + mod.No, "H6", "H6"));
list.Add(new modExcelRangeData(mod.FormDate.ToString("yyyy/MM/dd"), "I8", "I8"));
for (int i = 0; i < listdetail.Count; i++)
{
modQuotationDetail modd = listdetail[i];
string col = (10 + i).ToString().Trim();
list.Add(new modExcelRangeData((i + 1).ToString(), "A" + col, "A" + col));
list.Add(new modExcelRangeData(modd.ProductName, "B" + col, "B" + col));
list.Add(new modExcelRangeData(modd.Brand, "C" + col, "C" + col));
list.Add(new modExcelRangeData(modd.Qty.ToString(), "D" + col, "D" + col));
list.Add(new modExcelRangeData(modd.UnitNo, "E" + col, "E" + col));
list.Add(new modExcelRangeData(string.Format("{0:C2}", modd.Price), "G" + col, "G" + col));
list.Add(new modExcelRangeData(string.Format("{0:C2}", modd.Mny), "H" + col, "H" + col));
list.Add(new modExcelRangeData(modd.Remark, "I" + col, "I" + col));
}
clsExport.ExportByTemplate(list, "报价单", 1, 20, 10, 1);
}
private void toolDel_Click(object sender, EventArgs e)
{
try
{
this.Cursor = Cursors.WaitCursor;
if (DBGrid.CurrentRow == null)
{
return;
}
if (MessageBox.Show(clsTranslate.TranslateString("Do you really want to delete it?"), clsTranslate.TranslateString("Confirm"), MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.No)
{
return;
}
modQuotationForm mod = _dal.GetItem(DBGrid.CurrentRow.Cells[0].Value.ToString(), out Util.emsg);
bool ret = _dal.Save("DEL", mod, null, out Util.emsg);
if (ret)
{
LoadData();
}
else
{
MessageBox.Show(Util.emsg, clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
finally
{
this.Cursor = Cursors.Default;
}
}
public void EditItem(string formid)
{
try
{
this.Cursor = Cursors.WaitCursor;
_action = "EDIT";
modQuotationForm mod = _dal.GetItem(formid, out Util.emsg);
if (mod != null)
{
txtFormId.Text = formid;
dtpFormDate.Value = mod.FormDate;
txtNo.Text = mod.No;
txtCurrency.Text = mod.Currency;
txtCustName.Tag = mod.CustId;
txtCustName.Text = mod.CustName;
txtRemark.Text = mod.Remark;
txtContactPerson.Text = mod.ContactPerson;
txtFormId.ReadOnly = true;
txtCustName.ReadOnly = true;
DBGrid.Rows.Clear();
LoadDBGrid();
BindingCollection <modQuotationDetail> list = _dal.GetDetail(formid, out Util.emsg);
if (list == null && !string.IsNullOrEmpty(Util.emsg))
{
MessageBox.Show(Util.emsg, clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
else
{
dalProductList dalpdt = new dalProductList();
foreach (modQuotationDetail modd in list)
{
DataGridViewRow row = new DataGridViewRow();
row.CreateCells(DBGrid);
row.Cells[0].Value = modd.ProductId;
row.Cells[1].Value = modd.ProductName;
row.Cells[2].Value = modd.Specify;
row.Cells[3].Value = modd.UnitNo;
row.Cells[4].Value = modd.Brand;
row.Cells[5].Value = modd.Qty.ToString();
row.Cells[6].Value = modd.Price.ToString();
row.Cells[7].Value = modd.Remark;
row.Height = 40;
DBGrid.Rows.Add(row);
row.Dispose();
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
finally
{
this.Cursor = Cursors.Default;
}
}
/// <summary>
/// get all quotationform
/// <summary>
/// <param name=currentPage>currentPage</param>
/// <param name=pagesize>pagesize</param>
/// <param name=custname>custname</param>
/// <param name=fromdate>fromdate</param>
/// <param name=todate>todate</param>
/// <param name=out emsg>return error message</param>
///<returns>details of all quotationform</returns>
public BindingCollection <modQuotationForm> GetIList(int currentPage, int pagesize, string custname, string fromdate, string todate, out string emsg)
{
try
{
BindingCollection <modQuotationForm> modellist = new BindingCollection <modQuotationForm>();
//Execute a query to read the categories
string custnamewhere = string.Empty;
if (!string.IsNullOrEmpty(custname))
{
custnamewhere = "and b.cust_name like '%" + custname + "%' ";
}
string formdatewhere = string.Empty;
if (!string.IsNullOrEmpty(fromdate))
{
formdatewhere = "and a.form_date >= '" + Convert.ToDateTime(fromdate) + "' ";
}
if (!string.IsNullOrEmpty(todate))
{
formdatewhere += "and a.form_date <= '" + Convert.ToDateTime(todate) + "' ";
}
int startindex = (currentPage - 1) * pagesize + 1;
int endindex = currentPage * pagesize;
string sql = "select row_number() over(order by a.form_id desc) as rn,a.form_id,a.form_date,a.no,a.cust_id,b.cust_name,a.remark,a.contact_person,a.currency,a.update_user,a.update_time from quotation_form a inner join customer_list b on a.cust_id=b.cust_id where 1=1 "
+ custnamewhere + formdatewhere;
string sql2 = "select count(1) from (" + sql + ") t";
sql = string.Format("select * from (" + sql + ") t where rn>='{0}' and rn<='{1}'", startindex, endindex);
using (SqlDataReader rdr = SqlHelper.ExecuteReader(sql))
{
while (rdr.Read())
{
modQuotationForm model = new modQuotationForm();
model.FormId = dalUtility.ConvertToString(rdr["form_id"]);
model.FormDate = dalUtility.ConvertToDateTime(rdr["form_date"]);
model.No = dalUtility.ConvertToString(rdr["no"]);
model.CustId = dalUtility.ConvertToString(rdr["cust_id"]);
model.CustName = dalUtility.ConvertToString(rdr["cust_name"]);
model.Remark = dalUtility.ConvertToString(rdr["remark"]);
model.ContactPerson = dalUtility.ConvertToString(rdr["contact_person"]);
model.Currency = dalUtility.ConvertToString(rdr["currency"]);
model.DetailCount = DetailCount(model.FormId);
model.UpdateUser = dalUtility.ConvertToString(rdr["update_user"]);
model.UpdateTime = dalUtility.ConvertToDateTime(rdr["update_time"]);
modellist.Add(model);
}
}
emsg = SqlHelper.ExecuteScalar(sql2).ToString();
return(modellist);
}
catch (Exception ex)
{
emsg = dalUtility.ErrorMessage(ex.Message);
return(null);
}
}
/// <summary>
/// get table record
/// <summary>
/// <param name=formid>formid</param>
/// <param name=out emsg>return error message</param>
///<returns>get a record detail of quotationform</returns>
public modQuotationForm GetItem(string formid, out string emsg)
{
try
{
//Execute a query to read the categories
string sql = string.Format("select a.form_id,a.form_date,a.no,a.cust_id,b.cust_name,a.remark,a.contact_person,a.currency,a.update_user,a.update_time from quotation_form a inner join customer_list b on a.cust_id=b.cust_id where a.form_id='{0}' order by a.form_id", formid);
using (SqlDataReader rdr = SqlHelper.ExecuteReader(sql))
{
if (rdr.Read())
{
modQuotationForm model = new modQuotationForm();
model.FormId = dalUtility.ConvertToString(rdr["form_id"]);
model.FormDate = dalUtility.ConvertToDateTime(rdr["form_date"]);
model.No = dalUtility.ConvertToString(rdr["no"]);
model.CustId = dalUtility.ConvertToString(rdr["cust_id"]);
model.CustName = dalUtility.ConvertToString(rdr["cust_name"]);
model.Remark = dalUtility.ConvertToString(rdr["remark"]);
model.ContactPerson = dalUtility.ConvertToString(rdr["contact_person"]);
model.Currency = dalUtility.ConvertToString(rdr["currency"]);
model.DetailCount = DetailCount(model.FormId);
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 toolSave_Click(object sender, EventArgs e)
{
try
{
this.Cursor = Cursors.WaitCursor;
DBGrid.EndEdit();
if (dtpFormDate.Value < Util.modperiod.StartDate)
{
MessageBox.Show("该日期的数据已锁定,不能更新数据!", clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information);
dtpFormDate.Focus();
return;
}
if (txtCustName.Tag == null)
{
MessageBox.Show(clsTranslate.TranslateString("Cust Id") + clsTranslate.TranslateString(" can not be null!"), clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
txtCustName.Focus();
return;
}
if (string.IsNullOrEmpty(txtCustName.Text))
{
MessageBox.Show(clsTranslate.TranslateString("Cust Name") + clsTranslate.TranslateString(" can not be null!"), clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
txtCustName.Focus();
return;
}
if (clsLxms.GetParameterValue("NEED_QUOTATION_NO").CompareTo("T") == 0 && string.IsNullOrEmpty(txtNo.Text))
{
MessageBox.Show(clsTranslate.TranslateString("No") + clsTranslate.TranslateString(" can not be null!"), clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
txtNo.Focus();
return;
}
if (DBGrid.RowCount == 0)
{
MessageBox.Show("没有明细数据", clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
for (int i = 0; i < DBGrid.RowCount; i++)
{
if (DBGrid.Rows[i].Cells[0].Value == null || string.IsNullOrEmpty(DBGrid.Rows[i].Cells[0].Value.ToString()))
{
MessageBox.Show(clsTranslate.TranslateString("Product id") + clsTranslate.TranslateString(" can not be null!"), clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
if (DBGrid.Rows[i].Cells[1].Value == null || string.IsNullOrEmpty(DBGrid.Rows[i].Cells[1].Value.ToString()))
{
MessageBox.Show(clsTranslate.TranslateString("Product name") + clsTranslate.TranslateString(" can not be null!"), clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
if (DBGrid.Rows[i].Cells[5].Value == null || string.IsNullOrEmpty(DBGrid.Rows[i].Cells[5].Value.ToString()))
{
MessageBox.Show(clsTranslate.TranslateString("Qty") + clsTranslate.TranslateString(" can not be null!"), clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
else if (!Util.IsNumeric(DBGrid.Rows[i].Cells[5].Value.ToString()))
{
MessageBox.Show(clsTranslate.TranslateString("Qty") + clsTranslate.TranslateString(" must be a numeric!"), clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
else if (Convert.ToDecimal(DBGrid.Rows[i].Cells[5].Value.ToString()) <= 0)
{
MessageBox.Show(clsTranslate.TranslateString("Qty") + clsTranslate.TranslateString(" must > 0!"), clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
if (DBGrid.Rows[i].Cells[6].Value == null || string.IsNullOrEmpty(DBGrid.Rows[i].Cells[6].Value.ToString()))
{
MessageBox.Show(clsTranslate.TranslateString("Price") + clsTranslate.TranslateString(" can not be null!"), clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
else if (!Util.IsNumeric(DBGrid.Rows[i].Cells[6].Value.ToString()))
{
MessageBox.Show(clsTranslate.TranslateString("Price") + clsTranslate.TranslateString(" must be a numeric!"), clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
else if (Convert.ToDecimal(DBGrid.Rows[i].Cells[6].Value.ToString()) <= 0)
{
MessageBox.Show(clsTranslate.TranslateString("Price") + clsTranslate.TranslateString(" must > 0!"), clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
}
modQuotationForm mod = new modQuotationForm();
mod.FormId = txtFormId.Text.Trim();
mod.FormDate = dtpFormDate.Value;
mod.No = txtNo.Text.Trim();
mod.CustId = txtCustName.Tag.ToString();
mod.CustName = txtCustName.Text.Trim();
mod.Remark = txtRemark.Text.Trim();
mod.ContactPerson = txtContactPerson.Text.Trim();
mod.Currency = txtCurrency.Text.Trim();
mod.UpdateUser = Util.UserId;
string detaillist = string.Empty;
BindingCollection <modQuotationDetail> list = new BindingCollection <modQuotationDetail>();
for (int i = 0; i < DBGrid.RowCount; i++)
{
modQuotationDetail modd = new modQuotationDetail();
modd.Seq = i + 1;
modd.ProductId = DBGrid.Rows[i].Cells[0].Value.ToString();
modd.ProductName = DBGrid.Rows[i].Cells[1].Value.ToString();
modd.Specify = DBGrid.Rows[i].Cells[2].Value == null ? string.Empty : DBGrid.Rows[i].Cells[2].Value.ToString();
modd.UnitNo = DBGrid.Rows[i].Cells[3].Value == null ? string.Empty : DBGrid.Rows[i].Cells[3].Value.ToString();
modd.Brand = DBGrid.Rows[i].Cells[4].Value == null ? string.Empty : DBGrid.Rows[i].Cells[4].Value.ToString();
modd.Qty = Convert.ToDecimal(DBGrid.Rows[i].Cells[5].Value.ToString());
modd.Price = Convert.ToDecimal(DBGrid.Rows[i].Cells[6].Value.ToString());
modd.Remark = DBGrid.Rows[i].Cells[7].Value == null ? string.Empty : DBGrid.Rows[i].Cells[7].Value.ToString();
list.Add(modd);
}
bool ret = _dal.Save(_action, mod, list, out Util.emsg);
if (ret)
{
ini.IniWriteValue("QUOTATION", "CONTACT_PERSON", txtContactPerson.Text.Trim());
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>
/// 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>
/// get all quotationform
/// <summary>
/// <param name=custid>custid</param>
/// <param name=out emsg>return error message</param>
///<returns>details of all quotationform</returns>
public BindingCollection <modQuotationForm> GetIList(string formidlist, string custlist, string custname, string fromdate, string todate, out string emsg)
{
try
{
BindingCollection <modQuotationForm> modellist = new BindingCollection <modQuotationForm>();
//Execute a query to read the categories
string formidwhere = string.Empty;
if (!string.IsNullOrEmpty(formidlist) && formidlist.CompareTo("ALL") != 0)
{
formidwhere = "and a.form_id in ('" + formidlist.Replace(",", "','") + "') ";
}
string cusidtwhere = string.Empty;
if (!string.IsNullOrEmpty(custlist) && custlist.CompareTo("ALL") != 0)
{
cusidtwhere = "and a.cust_id in ('" + custlist.Replace(",", "','") + "') ";
}
string custnamewhere = string.Empty;
if (!string.IsNullOrEmpty(custname))
{
custnamewhere = "and b.cust_name like '%" + custname + "%' ";
}
string formdatewhere = string.Empty;
if (!string.IsNullOrEmpty(fromdate))
{
formdatewhere = "and a.form_date >= '" + Convert.ToDateTime(fromdate) + "' ";
}
if (!string.IsNullOrEmpty(todate))
{
formdatewhere += "and a.form_date <= '" + Convert.ToDateTime(todate) + "' ";
}
string sql = "select a.form_id,a.form_date,a.no,a.cust_id,b.cust_name,a.remark,a.contact_person,a.currency,a.update_user,a.update_time from quotation_form a inner join customer_list b on a.cust_id=b.cust_id where 1=1 "
+ formidwhere + cusidtwhere + custnamewhere + formdatewhere + " order by form_id desc";
using (SqlDataReader rdr = SqlHelper.ExecuteReader(sql))
{
while (rdr.Read())
{
modQuotationForm model = new modQuotationForm();
model.FormId = dalUtility.ConvertToString(rdr["form_id"]);
model.FormDate = dalUtility.ConvertToDateTime(rdr["form_date"]);
model.No = dalUtility.ConvertToString(rdr["no"]);
model.CustId = dalUtility.ConvertToString(rdr["cust_id"]);
model.CustName = dalUtility.ConvertToString(rdr["cust_name"]);
model.Remark = dalUtility.ConvertToString(rdr["remark"]);
model.ContactPerson = dalUtility.ConvertToString(rdr["contact_person"]);
model.Currency = dalUtility.ConvertToString(rdr["currency"]);
model.DetailCount = DetailCount(model.FormId);
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);
}
}
