Ejemplo n.º 1
0
        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);
            }
        }
Ejemplo n.º 2
0
 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);
     }
 }
Ejemplo n.º 3
0
 /// <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();
 }
Ejemplo n.º 4
0
        //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;
        }
Ejemplo n.º 5
0
        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);
            }
        }