230 lines
10 KiB
C#
230 lines
10 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using WMS.IData.IBase;
|
|
using System.Data;
|
|
using WMS.Model.Base;
|
|
using WMS.DBUtility;
|
|
using WMS.IData;
|
|
using Oracle.ManagedDataAccess.Client;
|
|
|
|
|
|
namespace WMS.SqlServerData.BaseData
|
|
{
|
|
public class CustomerData : ICustomer
|
|
{
|
|
/// <summary>
|
|
/// 根据编号获得用户信息如果为空则获得全部
|
|
/// </summary>
|
|
/// <param name="customer"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetCustomer(CustomerModel customer)
|
|
{
|
|
#region 查询sql
|
|
string sql =@" select T_CUS_CUSTOMER.CUSTOMER_ID, --客户编号
|
|
T_CUS_CUSTOMER.CUSTOMER_NAME, --客户名称
|
|
T_CUS_CUSTOMER.CUSTOMER_SHORT_NAME,--客户简称
|
|
T_CUS_CUSTOMER.PY_NAME, --拼音简称
|
|
T_CUS_CUSTOMER.CUS_TYPE_ID, --客户分类编号
|
|
e.CUS_TYPE_NAME, -- 客户分类名称
|
|
T_CUS_CUSTOMER.LINKMAN, --联系人
|
|
T_CUS_CUSTOMER.PHONE, --联系电话
|
|
T_CUS_CUSTOMER.FAX, --联系传真
|
|
T_CUS_CUSTOMER.EMAIL, --联系邮箱
|
|
T_CUS_CUSTOMER.ADDRESS, --联系地址
|
|
T_CUS_CUSTOMER.POSTCODE, --邮编
|
|
T_CUS_CUSTOMER.STATUS, --Status
|
|
T_CUS_CUSTOMER.REMARK, --备注
|
|
T_CUS_CUSTOMER.VERSION, --版本号
|
|
T_CUS_CUSTOMER.OPERATORID, --操作人
|
|
T_CUS_CUSTOMER.OPERATEDATE, --操作日期
|
|
T_CUS_CUSTOMER.PROVINCE_ID, --省份编号
|
|
b.add_name Province_name, --省份名称
|
|
T_CUS_CUSTOMER.CITY_ID, --市区编号
|
|
c.add_name City_name, --市区名称
|
|
T_CUS_CUSTOMER.TOWN_ID, --县/区编号
|
|
d.add_name Town_name, --县/区名称
|
|
T_CUS_CUSTOMER.Telephone, --手机号
|
|
T_CUS_CUSTOMER.GUID --GUID
|
|
from T_CUS_CUSTOMER
|
|
left join T_CUS_CUSTOMER_TYPE e on T_CUS_CUSTOMER.Cus_Type_Id = e.cus_type_id
|
|
left join t_base_addressarea b on T_CUS_CUSTOMER.PROVINCE_ID = b.add_id
|
|
left join t_base_addressarea c on T_CUS_CUSTOMER.CITY_ID = c.add_id
|
|
left join t_base_addressarea d on T_CUS_CUSTOMER.TOWN_ID = d.add_id
|
|
where 1 = 1 ";
|
|
if (!string.IsNullOrEmpty(customer.Customer_id))
|
|
{
|
|
sql += " and CUSTOMER_ID ='" + customer.Customer_id + "'";
|
|
}
|
|
if (!string.IsNullOrEmpty(customer.Guid))
|
|
{
|
|
sql += " and guid = '" + customer.Guid + "'";
|
|
}
|
|
sql += " Order by CUSTOMER_ID";
|
|
#endregion
|
|
|
|
return SystemDataObject.Instance.GetDataSet(sql).Tables[0];
|
|
}
|
|
|
|
/// <summary>
|
|
/// 根据编号获得用户信息如果为空则获得全部
|
|
/// </summary>
|
|
/// <param name="customer"></param>
|
|
/// <returns></returns>
|
|
public DataSet GetCustomer()
|
|
{
|
|
|
|
string sql =@" select CUSTOMER_ID, --客户编号
|
|
T_CUS_CUSTOMER.CUSTOMER_NAME, --客户名称
|
|
T_CUS_CUSTOMER.CUSTOMER_SHORT_NAME,--客户简称
|
|
|
|
LINKMAN
|
|
|
|
from T_CUS_CUSTOMER
|
|
|
|
";
|
|
|
|
|
|
return SystemDataObject.Instance.GetDataSet(sql);
|
|
}
|
|
#region 客户信息的修改或增加
|
|
|
|
public void AddCustomer(CustomerModel model)
|
|
{
|
|
|
|
string sql = string.Format(@" insert into T_CUS_CUSTOMER(
|
|
CUSTOMER_ID, --客户编号
|
|
CUSTOMER_NAME, --客户名称
|
|
CUSTOMER_SHORT_NAME,--客户简称
|
|
PY_NAME, --拼音简称
|
|
CUS_TYPE_ID, --客户分类编号
|
|
LINKMAN, --联系人
|
|
PHONE, --联系电话
|
|
FAX, --联系传真
|
|
EMAIL, --联系邮箱
|
|
ADDRESS, --联系地址
|
|
POSTCODE, --邮编
|
|
STATUS, --Status
|
|
REMARK, --备注
|
|
VERSION, --版本号
|
|
OPERATORID, --操作人
|
|
OPERATEDATE, --操作日期
|
|
PROVINCE_ID, --省份编号
|
|
CITY_ID, --市区编号
|
|
TOWN_ID, --县/区编号
|
|
TELEPHONE, --手机号
|
|
GUID
|
|
)
|
|
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}') ",
|
|
|
|
model.Customer_id,
|
|
model.Customer_name,
|
|
model.Customer_short_name,
|
|
model.Py_name,
|
|
model.Cus_type_id,
|
|
model.Linkman,
|
|
model.Phone,
|
|
model.Fax,
|
|
model.Email,
|
|
model.Address,
|
|
model.Postcode,
|
|
model.Status,
|
|
model.Remark,
|
|
model.Version,
|
|
model.OperatorID,
|
|
model.OperateDate,
|
|
model.Province_id,
|
|
model.City_id,
|
|
model.Town_id,
|
|
model.Telephone,
|
|
model.Guid);
|
|
|
|
SystemDataObject.Instance.ExecuteSql(sql);
|
|
}
|
|
/// <summary>
|
|
/// 客户信息的修改或增加
|
|
/// </summary>
|
|
/// <param name="model">客户信息Model</param>
|
|
/// <param name="flg">0 新增、 1 修改</param>
|
|
public void UpdateCustomer(CustomerModel model)
|
|
{
|
|
string sql = string.Format(@"update T_CUS_CUSTOMER
|
|
set
|
|
CUSTOMER_NAME='{0}',
|
|
CUSTOMER_SHORT_NAME ='{1}',
|
|
PY_NAME='{2}',
|
|
CUS_TYPE_ID ='{3}',
|
|
LINKMAN='{4}',
|
|
PHONE ='{5}',
|
|
FAX='{6}',
|
|
TELEPHONE='{7}',
|
|
EMAIL ='{8}',
|
|
ADDRESS='{9}',
|
|
POSTCODE='{10}',
|
|
STATUS='{11}',
|
|
REMARK='{12}',
|
|
VERSION='{13}',
|
|
OPERATORID='{14}',
|
|
OPERATEDATE='{15}',
|
|
PROVINCE_ID='{16}',
|
|
CITY_ID='{17}',
|
|
TOWN_ID='{18}',
|
|
GUID='{19}'
|
|
WHERE CUSTOMER_ID='{20}'",
|
|
model.Customer_name,
|
|
model.Customer_short_name,
|
|
model.Py_name,
|
|
model.Cus_type_id,
|
|
model.Linkman,
|
|
model.Phone,
|
|
model.Fax,
|
|
model.Telephone,
|
|
model.Email,
|
|
model.Address,
|
|
model.Postcode,
|
|
model.Status,
|
|
model.Remark,
|
|
model.Version,
|
|
model.OperatorID,
|
|
model.OperateDate.ToString("yyyy-MM-dd hh:mm:ss"),
|
|
model.Province_id,
|
|
model.City_id,
|
|
model.Town_id,
|
|
model.Guid,
|
|
model.Customer_id);
|
|
SystemDataObject.Instance.ExecuteSql(sql);
|
|
|
|
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region 客户信息的删除
|
|
/// <summary>
|
|
/// 客户信息的删除
|
|
/// </summary>
|
|
/// <param name="customer_id"></param>
|
|
public int execCustomerDel(string customer_id)
|
|
{
|
|
string sql = string.Format("delete from T_CUS_CUSTOMER where CUSTOMER_ID ='{0}'", customer_id);
|
|
return SystemDataObject.Instance.ExecuteSqlCommit(sql);
|
|
}
|
|
#endregion
|
|
|
|
#region 获取流水号
|
|
/// <summary>
|
|
/// 获取流水号
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public string GetSerialNum()
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append("select NEXT VALUE FOR SEQ_BASE_CUSTOMER_ID");
|
|
DataSet ds = SystemDataObject.Instance.GetDataSet(strSql.ToString());
|
|
return ds.Tables[0].Rows[0][0].ToString();
|
|
}
|
|
#endregion
|
|
}
|
|
}
|