public string InsertVendor(Vendor model)
{
model.Replace4MySQL();
StringBuilder strSql = new StringBuilder();
try
{
strSql.Append("insert into vendor( vendor_id,vendor_code,vendor_status,vendor_email,vendor_password,vendor_name_full,vendor_name_simple,vendor_invoice, ");
strSql.Append("company_phone,company_fax,company_person,company_zip,company_address,invoice_zip,invoice_address,contact_type_1,contact_name_1,contact_phone_1_1,");
strSql.Append("contact_phone_2_1,contact_mobile_1,contact_email_1,contact_type_2,contact_name_2,contact_phone_1_2,contact_phone_2_2,contact_mobile_2,contact_email_2,");
strSql.Append("contact_type_3,contact_name_3,contact_phone_1_3,contact_phone_2_3,contact_mobile_3,contact_email_3,contact_type_4,contact_name_4,contact_phone_1_4,");
strSql.Append("contact_phone_2_4,contact_mobile_4,contact_email_4,contact_type_5,contact_name_5,contact_phone_1_5,contact_phone_2_5,contact_mobile_5,contact_email_5,");
strSql.Append("cost_percent,creditcard_1_percent,creditcard_3_percent,sales_limit,bonus_percent,agreement_createdate,agreement_start,agreement_end,checkout_type,");
strSql.Append("checkout_other,bank_code,bank_name,bank_number,bank_account,freight_low_limit,freight_low_money,freight_normal_limit,freight_normal_money,");
strSql.Append("freight_return_low_money,freight_return_normal_money,vendor_note,vendor_confirm_code,vendor_login_attempts,assist,dispatch,product_mode,");
strSql.Append("product_manage,erp_id,gigade_bunus_percent,gigade_bunus_threshold, procurement_days,self_send_days,stuff_ware_days,dispatch_days,export_flag,vendor_type,kuser,kdate)");//vendor_type供應商類型
strSql.AppendFormat(" values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}',",
model.vendor_id, model.vendor_code, model.vendor_status, model.vendor_email, model.vendor_password, model.vendor_name_full, model.vendor_name_simple, model.vendor_invoice);
strSql.AppendFormat("'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}',",
model.company_phone, model.company_fax, model.company_person, model.company_zip, model.company_address, model.invoice_zip, model.invoice_address, model.contact_type_1, model.contact_name_1, model.contact_phone_1_1);
strSql.AppendFormat("'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}',",
model.contact_phone_2_1, model.contact_mobile_1, model.contact_email_1, model.contact_type_2, model.contact_name_2, model.contact_phone_1_2, model.contact_phone_2_2, model.contact_mobile_2, model.contact_email_2);
strSql.AppendFormat("'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}',",
model.contact_type_3, model.contact_name_3, model.contact_phone_1_3, model.contact_phone_2_3, model.contact_mobile_3, model.contact_email_3, model.contact_type_4, model.contact_name_4, model.contact_phone_1_4);
strSql.AppendFormat("'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}',",
model.contact_phone_2_4, model.contact_mobile_4, model.contact_email_4, model.contact_type_5, model.contact_name_5, model.contact_phone_1_5, model.contact_phone_2_5, model.contact_mobile_5, model.contact_email_5);
strSql.AppendFormat("'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}',",
model.cost_percent, model.creditcard_1_percent, model.creditcard_3_percent, model.sales_limit, model.bonus_percent, model.agreement_createdate, model.agreement_start, model.agreement_end, model.checkout_type);
strSql.AppendFormat("'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}',",
model.checkout_other, model.bank_code, model.bank_name, model.bank_number, model.bank_account, model.freight_low_limit, model.freight_low_money, model.freight_normal_limit, model.freight_normal_money);
strSql.AppendFormat("'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}',",
model.freight_return_low_money, model.freight_return_normal_money, model.vendor_note, model.vendor_confirm_code, model.vendor_login_attempts, model.assist, model.dispatch, model.product_mode);
strSql.AppendFormat("'{0}','{1}','{2}','{3}',", model.product_manage, model.erp_id, model.gigade_bunus_percent, model.gigade_bunus_threshold);
strSql.AppendFormat("'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}');", model.procurement_days, model.self_send_days, model.stuff_ware_days, model.dispatch_days, model.export_flag, model.vendor_type, model.kuser, CommonFunction.DateTimeToString(model.kdate));
return strSql.ToString();
}
catch (Exception ex)
{
throw new Exception(" vendorDao-->InsertVendor-->" + ex.Message + strSql.ToString(), ex);
}
}
public string UpdateVendor(Vendor model)
{
model.Replace4MySQL();
StringBuilder strSql = new StringBuilder("");
try
{
strSql.AppendFormat("update vendor set vendor_code='{0}',vendor_status='{1}',vendor_email='{2}',vendor_name_full='{3}',vendor_name_simple='{4}',vendor_invoice='{5}', ",
model.vendor_code, model.vendor_status, model.vendor_email, model.vendor_name_full, model.vendor_name_simple, model.vendor_invoice);
strSql.AppendFormat("company_phone='{0}',company_fax='{1}',company_person='{2}',company_zip='{3}',company_address='{4}',invoice_zip='{5}',invoice_address='{6}',contact_type_1='{7}',contact_name_1='{8}',contact_phone_1_1='{9}',",
model.company_phone, model.company_fax, model.company_person, model.company_zip, model.company_address, model.invoice_zip, model.invoice_address, model.contact_type_1, model.contact_name_1, model.contact_phone_1_1);
strSql.AppendFormat("contact_phone_2_1='{0}',contact_mobile_1='{1}',contact_email_1='{2}',contact_type_2='{3}',contact_name_2='{4}',contact_phone_1_2='{5}',contact_phone_2_2='{6}',contact_mobile_2='{7}',contact_email_2='{8}',",
model.contact_phone_2_1, model.contact_mobile_1, model.contact_email_1, model.contact_type_2, model.contact_name_2, model.contact_phone_1_2, model.contact_phone_2_2, model.contact_mobile_2, model.contact_email_2);
strSql.AppendFormat("contact_type_3='{0}',contact_name_3='{1}',contact_phone_1_3='{2}',contact_phone_2_3='{3}',contact_mobile_3='{4}',contact_email_3='{5}',contact_type_4='{6}',contact_name_4='{7}',contact_phone_1_4='{8}',",
model.contact_type_3, model.contact_name_3, model.contact_phone_1_3, model.contact_phone_2_3, model.contact_mobile_3, model.contact_email_3, model.contact_type_4, model.contact_name_4, model.contact_phone_1_4);
strSql.AppendFormat("contact_phone_2_4='{0}',contact_mobile_4='{1}',contact_email_4='{2}',contact_type_5='{3}',contact_name_5='{4}',contact_phone_1_5='{5}',contact_phone_2_5='{6}',contact_mobile_5='{7}',contact_email_5='{8}',",
model.contact_phone_2_4, model.contact_mobile_4, model.contact_email_4, model.contact_type_5, model.contact_name_5, model.contact_phone_1_5, model.contact_phone_2_5, model.contact_mobile_5, model.contact_email_5);
strSql.AppendFormat("cost_percent='{0}',creditcard_1_percent='{1}',creditcard_3_percent='{2}',sales_limit='{3}',bonus_percent='{4}',agreement_createdate='{5}',agreement_start='{6}',agreement_end='{7}',checkout_type='{8}',",
model.cost_percent, model.creditcard_1_percent, model.creditcard_3_percent, model.sales_limit, model.bonus_percent, model.agreement_createdate, model.agreement_start, model.agreement_end, model.checkout_type);
strSql.AppendFormat("checkout_other='{0}',bank_code='{1}',bank_name='{2}',bank_number='{3}',bank_account='{4}',freight_low_limit='{5}',freight_low_money='{6}',freight_normal_limit='{7}',freight_normal_money='{8}',",
model.checkout_other, model.bank_code, model.bank_name, model.bank_number, model.bank_account, model.freight_low_limit, model.freight_low_money, model.freight_normal_limit, model.freight_normal_money);
strSql.AppendFormat("freight_return_low_money='{0}',freight_return_normal_money='{1}',vendor_note='{2}',vendor_confirm_code='{3}',vendor_login_attempts='{4}',assist='{5}',dispatch='{6}',product_mode='{7}',",
model.freight_return_low_money, model.freight_return_normal_money, model.vendor_note, model.vendor_confirm_code, model.vendor_login_attempts, model.assist, model.dispatch, model.product_mode);
strSql.AppendFormat("procurement_days='{0}',self_send_days='{1}',stuff_ware_days='{2}',dispatch_days='{3}',", model.procurement_days, model.self_send_days, model.stuff_ware_days, model.dispatch_days);
strSql.AppendFormat("vendor_type='{0}',", model.vendor_type);//供應商類型
strSql.AppendFormat("product_manage='{0}',gigade_bunus_percent='{1}',gigade_bunus_threshold='{2}',erp_id='{3}',export_flag='{4}' where vendor_id='{5}';",
model.product_manage, model.gigade_bunus_percent, model.gigade_bunus_threshold, model.erp_id, model.export_flag, model.vendor_id);
return strSql.ToString();
}
catch (Exception ex)
{
throw new Exception("VendorDao-->UpdateVendor-->" + ex.Message + strSql.ToString(), ex);
}
}
//chaojie天界與2015-07-09上面的用作供應商下拉列表，數據太多 可能導致延遲或數據加載不出
public List<Vendor> VendorQueryList(Vendor query)
{
query.Replace4MySQL();
StringBuilder sbSql = new StringBuilder();
try
{
sbSql.Append(@" SELECT vendor_id,vendor_name_full,vendor_name_simple FROM vendor WHERE vendor_status = 1 ");
if (query.assist != 0)
{
sbSql.AppendFormat(" AND assist = 1 ");
}
sbSql.Append(" ORDER BY vendor_id ASC");
return _dbAccess.getDataTableForObj<Vendor>(sbSql.ToString());
}
catch (Exception ex)
{
throw new Exception(" vendorDao-->VendorQueryList-->" + ex.Message + sbSql.ToString(), ex);
}
}
/// <summary>
/// 獲取所有有效的供應商（用作下拉列表框）
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public List<Vendor> VendorQueryAll(Vendor query)
{
query.Replace4MySQL();
StringBuilder sbSql = new StringBuilder();
try
{
sbSql.Append(@" SELECT vendor_id,vendor_code,vendor_status,vendor_email,vendor_password,vendor_name_full,vendor_name_simple,vendor_invoice,
company_phone,company_fax,company_person,company_zip,company_address,invoice_zip,invoice_address,contact_type_1,contact_name_1,contact_phone_1_1,
contact_phone_2_1,contact_mobile_1,contact_email_1,contact_type_2,contact_name_2,contact_phone_1_2,contact_phone_2_2,contact_mobile_2,contact_email_2,
contact_type_3,contact_name_3,contact_phone_1_3,contact_phone_2_3,contact_mobile_3,contact_email_3,contact_type_4,contact_name_4,contact_phone_1_4,
contact_phone_2_4,contact_mobile_4,contact_email_4,contact_type_5,contact_name_5,contact_phone_1_5,contact_phone_2_5,contact_mobile_5,contact_email_5,
cost_percent,creditcard_1_percent,creditcard_3_percent,sales_limit,bonus_percent,agreement_createdate,agreement_start,agreement_end,checkout_type,
checkout_other,bank_code,bank_name,bank_number,bank_account,freight_low_limit,freight_low_money,freight_normal_limit,freight_normal_money,
freight_return_low_money,freight_return_normal_money,vendor_note,vendor_confirm_code,vendor_login_attempts,assist,dispatch,product_mode,
product_manage,gigade_bunus_percent,gigade_bunus_threshold,procurement_days,self_send_days,stuff_ware_days,dispatch_days,vendor_type,kuser,kdate FROM vendor WHERE vendor_status = 1 ");
if (query.assist != 0)
{
sbSql.AppendFormat(" AND assist = 1 ");
}
sbSql.Append(" ORDER BY vendor_id ASC");
return _dbAccess.getDataTableForObj<Vendor>(sbSql.ToString());
}
catch (Exception ex)
{
throw new Exception(" vendorDao-->VendorQueryAll-->" + ex.Message + sbSql.ToString(), ex);
}
}
/// <summary>
/// 獲取一條供應商信息
/// </summary>
/// <param name="query">查詢條件</param>
/// <returns>供應商信息對象</returns>
public Vendor GetSingle(Vendor query)
{
StringBuilder strSql = new StringBuilder();
try
{
query.Replace4MySQL();
strSql.Append(" select vendor_id,vendor_code,vendor_status,vendor_email,vendor_password,vendor_name_full,vendor_name_simple,vendor_invoice,");
strSql.Append("company_phone,company_fax,company_person,company_zip,company_address,invoice_zip,invoice_address,contact_type_1,contact_name_1,contact_phone_1_1,");
strSql.Append("contact_phone_2_1,contact_mobile_1,contact_email_1,contact_type_2,contact_name_2,contact_phone_1_2,contact_phone_2_2,contact_mobile_2,contact_email_2,");
strSql.Append("contact_type_3,contact_name_3,contact_phone_1_3,contact_phone_2_3,contact_mobile_3,contact_email_3,contact_type_4,contact_name_4,contact_phone_1_4,");
strSql.Append("contact_phone_2_4,contact_mobile_4,contact_email_4,contact_type_5,contact_name_5,contact_phone_1_5,contact_phone_2_5,contact_mobile_5,contact_email_5,");
strSql.Append("cost_percent,creditcard_1_percent,creditcard_3_percent,sales_limit,bonus_percent,agreement_createdate,agreement_start,agreement_end,checkout_type,");
strSql.Append("checkout_other,bank_code,bank_name,bank_number,bank_account,freight_low_limit,freight_low_money,freight_normal_limit,freight_normal_money,erp_id,");
strSql.Append("freight_return_low_money,freight_return_normal_money,vendor_note,vendor_confirm_code,vendor_login_attempts,assist,dispatch,product_mode,");//新增字段kuser、kdate保存供應商建立信息 add by shuangshuang0420j 20150624 10:15
strSql.Append("product_manage,gigade_bunus_percent,gigade_bunus_threshold,procurement_days,self_send_days,stuff_ware_days,dispatch_days,vendor_type,kuser,kdate from vendor where 1=1");//新增字段procurement_days,self_send_days,stuff_ware_days,dispatch_days add by shuangshuang0420j 20150323 10:15
if (query.vendor_id != 0)
{
strSql.AppendFormat(" and vendor_id={0}", query.vendor_id);
}
if (!string.IsNullOrEmpty(query.vendor_email))
{
strSql.AppendFormat(" and vendor_email='{0}'", query.vendor_email);
}
if (!string.IsNullOrEmpty(query.erp_id))
{
strSql.AppendFormat(" and erp_id='{0}'", query.erp_id);
}
return _dbAccess.getSinggleObj<Vendor>(strSql.ToString());
}
catch (Exception ex)
{
throw new Exception(" vendorDao-->GetSingle-->" + ex.Message + strSql.ToString(), ex);
}
}
/// <summary>
/// 供應商下拉列表
/// </summary>
/// <param name="v"></param>
/// <returns></returns>
public DataTable GetVendor(Vendor v)
{
v.Replace4MySQL();
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat(@"SELECT vendor_id,vendor_name_simple,vendor_name_full FROM vendor WHERE 1=1");
if (v.assist != 0)
{
sql.AppendFormat(@" AND assist = '{0}' ", v.assist);
}
sql.AppendFormat(@";");
return _dbAccess.getDataTable(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("ProductDao-->UpdateProductDeatail" + ex.Message + sql.ToString(), ex);
}
}
