public List<VendorQuery> Query(VendorQuery query, ref int totalCount)
{
query.Replace4MySQL();
StringBuilder sql = new StringBuilder("");
StringBuilder sqlCon = new StringBuilder("");
try
{
sql.Append(@" select zipI.bigcode as i_bigcode, zipI.middlecode as i_midcode, zipI.middle as i_middle,zipI.zipcode as i_zipcode, CONCAT(zipI.zipcode ,'/',zipI.small) as 'i_zip',CONCAT(CONCAT(zipC.middle,zipC.small),vendor.company_address) as vendor_company_address, ");
sql.Append(" zipC.bigcode as c_bigcode, zipC.middlecode as c_midcode,zipC.middle as c_middle,zipC.zipcode as c_zipcode,CONCAT(zipC.zipcode ,'/',zipC.small) as 'c_zip' ,");
sql.Append(@" us.user_username as manage_name, vendor.*,");
sql.Append(" FROM_UNIXTIME(vendor.agreement_createdate) as agr_date,FROM_UNIXTIME(vendor.agreement_start) as agr_start,FROM_UNIXTIME(vendor.agreement_end) as agr_end");//將php時間轉化為net時間
sql.Append(" from vendor ");
sql.Append(" left join t_zip_code zipC on vendor.company_zip=zipC.zipcode");
sql.Append(" left join manage_user us on us.user_id=vendor.product_manage");
sql.Append(" left join t_zip_code zipI on vendor.invoice_zip=zipI.zipcode ");
sql.Append(" where 1=1 ");
if (!string.IsNullOrEmpty(query.searchEmail))
{
sqlCon.AppendFormat(" and vendor_email like '%{0}%'", query.searchEmail);
}
if (!string.IsNullOrEmpty(query.searchName))
{
sqlCon.AppendFormat(" and vendor_name_simple like '%{0}%'", query.searchName);
}
if (!string.IsNullOrEmpty(query.vendor_name_full))
{
sqlCon.AppendFormat(" and vendor_name_full like '%{0}%'", query.vendor_name_full);
}
if (!string.IsNullOrEmpty(query.searchInvoice))
{
sqlCon.AppendFormat(" and vendor.vendor_invoice like '%{0}%'", query.searchInvoice);
}
if (!string.IsNullOrEmpty(query.erp_id))
{
sqlCon.AppendFormat(" and vendor.erp_id like '%{0}%'", query.erp_id);
}
if (!string.IsNullOrEmpty(query.vendor_code))
{
sqlCon.AppendFormat(" and vendor.vendor_code like '%{0}%'", query.vendor_code);
}
if (query.vendor_id != 0)
{
sqlCon.AppendFormat(" and vendor.vendor_id = '{0}'", query.vendor_id);
}
if (query.create_dateOne != 0)
{
sqlCon.AppendFormat(" and agreement_createdate >= '{0}'", query.create_dateOne);
}
if (query.create_dateTwo != 0)
{
sqlCon.AppendFormat(" and agreement_createdate <= '{0}'", query.create_dateTwo);
}
if (query.searchStatus != -1)
{
sqlCon.AppendFormat(" and dispatch='{0}'", query.searchStatus);
}
if (!string.IsNullOrEmpty(query.vendor_type))
{
sqlCon.Append(" and (");
string[] checks = query.vendor_type.Split(',');
int num = 0;
for (int i = 0; i < checks.Length; i++)
{
if (num == 0)
{
sqlCon.AppendFormat(" vendor_type like '%{0}%'", checks[i]);
num++;
}
else
{
sqlCon.AppendFormat(" or vendor_type like '%{0}%'", checks[i]);
num++;
}
}
sqlCon.Append(" ) ");
}
sqlCon.Append(" order by vendor_id desc ");
//得到數據總條數
totalCount = 0;
System.Data.DataTable _dt = _dbAccess.getDataTable(" select count(vendor_id) as totalCount from vendor where 1=1 " + sqlCon.ToString());
if (_dt != null)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
}
sqlCon.AppendFormat(" limit {0},{1}", query.Start, query.Limit);
return _dbAccess.getDataTableForObj<VendorQuery>(sql.ToString() + sqlCon.ToString());
}
catch (Exception ex)
{
throw new Exception(" VendorDao-->Query-->" + ex.Message + sql.ToString() + sqlCon.ToString(), ex);
}
}
public string ReturnHistoryCon(VendorQuery model)
{
model.Replace4MySQL();
StringBuilder strSql = new StringBuilder();
try
{
strSql.AppendFormat(" vendor_status:'{0}',vendor_email:'{1}',vendor_password:'******',vendor_name_full:'{3}',vendor_name_simple:'{4}',vendor_invoice:'{5}', ", model.vendor_status, model.vendor_email, model.vendor_password, 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("product_manage:'{0}',gigade_bunus_percent:'{1}',gigade_bunus_threshold :'{2}'", model.product_manage, model.gigade_bunus_percent, model.gigade_bunus_threshold);
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);
return strSql.ToString();
}
catch (Exception ex)
{
throw new Exception(" VendorDao-->ReturnHistoryCon-->" + ex.Message + strSql.ToString(), ex);
}
}
/// <summary>
/// 用於返回事物所用到的sql語句
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public string Save(VendorQuery model)
{
model.Replace4MySQL();
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" insert into user_history (user_id,user_name,file_name,content,creat_time,ip ) ");
sb.AppendFormat(" values('{0}','{1}','{2}','{3}','{4}','{5}');", model.kuser_id, model.kuser_name, model.file_name, model.content, CommonFunction.GetPHPTime(model.created.ToString()), model.ip);
return sb.ToString();
}
//public int Update(VendorQuery model, string update_log)
//{
// _userhistoryDao = new UserHistoryDao(connStr);
// _serialDao = new SerialDao(connStr);
// model.Replace4MySQL();
// int i = 0;
// MySqlCommand mySqlCmd = new MySqlCommand();
// MySqlConnection mySqlConn = new MySqlConnection(connStr);
// try
// {
// if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
// {
// mySqlConn.Open();
// }
// mySqlCmd.Connection = mySqlConn;
// mySqlCmd.Transaction = mySqlConn.BeginTransaction();
// mySqlCmd.CommandType = System.Data.CommandType.Text;
// model.content = ReturnHistoryCon(model).ToString();
// #region 處理vendor表
// mySqlCmd.CommandText = UpdateVendor(model);
// i += mySqlCmd.ExecuteNonQuery();
// #endregion
// #region 處理userhistory表
// mySqlCmd.CommandText += _userhistoryDao.Save(model);
// i += mySqlCmd.ExecuteNonQuery();
// #endregion
// #region 處理table_change_log 記錄供應商資料異動
// if (!string.IsNullOrEmpty(update_log))
// {
// update_log = update_log.TrimEnd('#');//去掉最後一個#
// string[] arr_log = update_log.Split('#');//分離每條記錄
// foreach (string item in arr_log)
// {
// }
// }
// #endregion
// mySqlCmd.Transaction.Commit();
// }
// catch (Exception ex)
// {
// mySqlCmd.Transaction.Rollback();
// throw new Exception("VendorDao-->Update-->" + ex.Message, ex);
// }
// finally
// {
// if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
// {
// mySqlConn.Close();
// }
// }
// return i;
//}
public int Add(VendorQuery model)
{
model.Replace4MySQL();
int i = 0;
StringBuilder sql = new StringBuilder();
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connStr);
try
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
{
mySqlConn.Open();
}
mySqlCmd.Connection = mySqlConn;
mySqlCmd.Transaction = mySqlConn.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
#region 獲取vendor_id
mySqlCmd.CommandText = _serialDao.Update(10);
sql.Append(mySqlCmd.CommandText);
model.vendor_id = Convert.ToUInt32(mySqlCmd.ExecuteScalar());
#endregion
#region 獲取vendor_code
mySqlCmd.CommandText = "select max(vendor_code)as vendor_code from vendor;";
sql.Append(mySqlCmd.CommandText);
string tempCode = mySqlCmd.ExecuteScalar().ToString();
int nowYear = Convert.ToInt32(DateTime.Today.Year.ToString().Substring(2));
int nowDays = DateTime.Today.DayOfYear;
int nCode_4 = 1;
if (!string.IsNullOrEmpty(tempCode))
{
int tCode_2 = Convert.ToInt32(tempCode.Substring(1, 2));
int tCode_3 = Convert.ToInt32(tempCode.Substring(3, 3));
int tCode_4 = Convert.ToInt32(tempCode.Substring(6));
if (tCode_2 == nowYear && tCode_3 == nowDays)
{
nCode_4 = tCode_4 + 1;
}
}
string tYear = StrPad_Left(nowYear.ToString(), 2);
string tDays = StrPad_Left(nowDays.ToString(), 3);
model.vendor_code = "V" + tYear + tDays + StrPad_Left(nCode_4.ToString(), 4);
#endregion
model.content = ReturnHistoryCon(model).ToString();
#region 獲取erp_id
if (!string.IsNullOrEmpty(model.prod_cate) && !string.IsNullOrEmpty(model.buy_cate)
&& !string.IsNullOrEmpty(model.tax_type)
&& model.buy_cate.StartsWith(model.prod_cate))
{
//獲取serial_id;
Serial serModel = _serialDao.GetSerialById(77);
if (serModel == null)
{
serModel = new Serial();
serModel.Serial_id = 77;
serModel.Serial_Value = 500;//默認從500開始
mySqlCmd.CommandText = _serialDao.InsertStr(serModel);
sql.Append(mySqlCmd.CommandText);
i += mySqlCmd.ExecuteNonQuery();
}
mySqlCmd.CommandText = _serialDao.Update(77);//77代表erp_id的serial
sql.Append(mySqlCmd.CommandText);
model.serial = mySqlCmd.ExecuteScalar().ToString();
if (!string.IsNullOrEmpty(model.serial))
{
model.erp_id = model.buy_cate + CommonFunction.Supply(model.serial, "0", 5) + model.tax_type;
if (IsExitErpID(model.erp_id) > 0)
{
i = -1;
return i;
}
}
}
#endregion
#region 處理vendor表
mySqlCmd.CommandText = InsertVendor(model);
sql.Append(mySqlCmd.CommandText);
i += mySqlCmd.ExecuteNonQuery();
#endregion
#region 處理userhistory表
mySqlCmd.CommandText = _userhistoryDao.Save(model);
sql.Append(mySqlCmd.CommandText);
i += mySqlCmd.ExecuteNonQuery();
#endregion
mySqlCmd.Transaction.Commit();
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("VendorDao-->Add-->" + ex.Message + sql.ToString(), ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
return i;
}
public int Update(VendorQuery model, List<TableChangeLog> list)
{
UserHistoryDao _userhistoryDao = new UserHistoryDao(connStr);
SerialDao _serialDao = new SerialDao(connStr);
model.Replace4MySQL();
int i = 0;
try
{
ArrayList _list = new ArrayList();
model.content = _vendorDao.ReturnHistoryCon(model).ToString();
#region 處理vendor表
_list.Add(_vendorDao.UpdateVendor(model));
#endregion
#region 處理userhistory表
_list.Add(_userhistoryDao.Save(model));
#endregion
#region 處理table_change_log 記錄供應商資料異動
//if (!string.IsNullOrEmpty(update_log))
//{
// update_log = update_log.TrimEnd('#');//去掉最後一個#
// string[] arr_log = update_log.Split('#');//分離每條記錄
// foreach (string item in arr_log)
// {
// TableChangeLogDao _logDao = new TableChangeLogDao(connStr);
// string[] arr_col = item.Split(':');
// TableChangeLog tcl = new TableChangeLog();
// tcl.change_table = "vendor";
// tcl.change_field = arr_col[0].ToString();
// tcl.field_ch_name = arr_col[3].ToString();
// tcl.create_time = model.created;
// tcl.create_user = (int)model.kuser_id;
// tcl.pk_id = (int)model.vendor_id;
// tcl.old_value = arr_col[1].ToString();
// tcl.new_value = arr_col[2].ToString();
// tcl.user_type = model.user_type;
// _list.Add(_logDao.insert(tcl));
// }
//}
if (list != null)
{
foreach (TableChangeLog t in list)
{
TableChangeLogDao _logDao = new TableChangeLogDao(connStr);
t.change_table = "vendor";
t.create_time = model.created;
t.create_user = (int)model.kuser_id;
t.pk_id = (int)model.vendor_id;
t.user_type = model.user_type;
_list.Add(_logDao.insert(t));
}
}
#endregion
if (_mysqlDao.ExcuteSqls(_list))
{
i = 1;
}
return i;
}
catch (Exception ex)
{
throw new Exception("VendorDao-->Update-->" + ex.Message, ex);
}
}