public static DataTable GetWarehouseByID(string strUserID)
{
DataTable dtWarehouse=null ; //保存返回的仓库列表
string strSql = string.Format("select * from [wms_user_warehouse] a, wms_warehouse b where a.user_name='{0}' and a.warehouse_code=b.warehouse_code order by b.warehouse_name", strUserID);//sql语句
try
{
//调用DBSqlserver类
DBSqlServer db = new DBSqlServer();
db.CommandText = strSql;
db.HandleExceptions = true; //开启处理错误
DataSet ds = db.ExecuteDataSet(); //访问数据库
if (db.LastError.Length == 0)//若数据访问过程中未出错
{
if (ds.Tables[0].Rows.Count != 0)//若查询到结果
{
dtWarehouse = ds.Tables[0]; //保存返回的仓库数据集
}
}
}
catch//出错处理
{
dtWarehouse = null;
}
return dtWarehouse;
}
/// <summary>
/// 根据用户名获取用户权限
/// </summary>
/// <param name="strID">用户名</param>
/// <returns>返回权限</returns>
public static string GetUserAuthByID(string strID)
{
string strAuth = ""; //保存返回的权限
string strSql = string.Format(@"select chinese_name from [auth_user] where user_name='{0}'", strID);//sql语句
try
{
//调用DBSqlserver类
DBSqlServer db = new DBSqlServer();
db.CommandText = strSql;
db.HandleExceptions = true; //开启处理错误
DataSet ds = db.ExecuteDataSet(); //访问数据库
if (db.LastError.Length == 0)//若数据访问过程中未出错
{
if (ds.Tables[0].Rows.Count != 0)//若查询到结果
{
strAuth = ds.Tables[0].Rows[0][0].ToString(); //保存返回的姓名
}
}
}
catch//出错处理
{
strAuth = "";
}
return strAuth;
}
public static DataTable GetCustomerByID(string strUserID)
{
DataTable dtCustomer = null; //保存返回的客户列表
string strSql = string.Format("select (a.customer_code+'||'+b.customer_name) as customer_whole_name,* from [wms_user_customer] a, wms_customer b where a.user_name='{0}' and a.customer_code=b.customer_code order by customer_whole_name", strUserID);//sql语句
try
{
//调用DBSqlserver类
DBSqlServer db = new DBSqlServer();
db.CommandText = strSql;
db.HandleExceptions = true; //开启处理错误
DataSet ds = db.ExecuteDataSet(); //访问数据库
if (db.LastError.Length == 0)//若数据访问过程中未出错
{
if (ds.Tables[0].Rows.Count != 0)//若查询到结果
{
dtCustomer = ds.Tables[0]; //保存返回的客户数据集
}
}
}
catch//出错处理
{
dtCustomer = null;
}
return dtCustomer;
}
public static DataTable VerifyProductCode(string productcode,string customercode)
{
DataTable dtProduct = null; //保存返回的产品列表
string strSql = string.Format("select product_code,product_name from [wms_product] where product_code='{0}' and customer_code='{1}'", productcode, customercode);//sql语句
try
{
//调用DBSqlserver类
DBSqlServer db = new DBSqlServer();
db.CommandText = strSql;
db.HandleExceptions = true; //开启处理错误
DataSet ds = db.ExecuteDataSet(); //访问数据库
if (db.LastError.Length == 0)//若数据访问过程中未出错
{
if (ds.Tables[0].Rows.Count != 0)//若查询到结果
{
dtProduct = ds.Tables[0]; //保存返回的客户数据集
}
}
}
catch//出错处理
{
dtProduct = null;
}
return dtProduct;
}
public static DataTable GetASNHeader(string WarehouseCode, string ASNStatus, string CustomerCode, string RevStatus, string CostStatus, string ProductName, string RefNo, string DO, string GateInDateFrom, string GateInDateTo, string LotNo, string FlowNo)
{
DataTable dtASNHeader = null; //保存返回的ASN列表
//if (GateInDateFrom == "")
//{
GateInDateFrom = "1900-1-1";
//}
if (GateInDateTo == "")
{
DateTime dt = System.DateTime.Now.AddDays(1);
GateInDateTo = dt.ToString();
}
/*
string strSql = string.Format(
@"select * from [wms_in_bill_master] a, [wms_in_bill_detail] b,[wms_customer] c,[wms_product] d,[auth_user] e where a.customer_code=c.customer_code
and a.operate_person_id=e.user_id
and a.bill_no=b.bill_no
and b.product_code=d.product_code
and a.warehouse_code like '%{0}%'
and a.status like '%{1}%'
and c.customer_code like '%{2}%'
and d.product_name like '%{5}%'
and a.[customized_field1] like '%{6}%'
and a.update_time >='{8}'
and a.update_time<='{9}'
and isnull(b.[customized_field1],'') like '%{10}%'
and isnull(b.[customized_field2],'') like '%{11}%'
order by c.customer_name,d.product_code,d.product_name,b.real_quantity,a.update_time",
WarehouseCode,
ASNStatus,
CustomerCode,
RevStatus,
CostStatus,
ProductName,
RefNo, //Master表中的customized_field1
DO,
GateInDateFrom,
GateInDateTo,
LotNo, //Detail表中的customized_field1，
FlowNo);//Detail表中的customized_field2，如果是残损会加P在后面
*/
string strSql = string.Format(
@"select distinct a.bill_no,a.bill_date,e.user_name,c.customer_code,c.customer_name,a.status from [wms_in_bill_master] a, [wms_in_bill_detail] b,[wms_customer] c,[wms_product] d,[auth_user] e
where a.customer_code=c.customer_code
and a.operate_person_id=e.user_id
and a.bill_no=b.bill_no
and b.product_code=d.product_code
and a.warehouse_code like '%{0}%'
and a.status like '%{1}%'
and c.customer_code like '%{2}%'
and d.product_name like '%{5}%'
and a.[customized_field1] like '%{6}%'
and a.update_time >='{8}'
and a.update_time<='{9}'
and isnull(b.[customized_field1],'') like '%{10}%'
and isnull(b.[customized_field2],'') like '%{11}%'
order by a.bill_no",
WarehouseCode,
ASNStatus,
CustomerCode,
RevStatus,
CostStatus,
ProductName,
RefNo, //Master表中的customized_field1
DO,
GateInDateFrom,
GateInDateTo,
LotNo, //Detail表中的customized_field1，
FlowNo);//Detail表中的customized_field2，如果是残损会加P在后面
//sql语句
try
{
//调用DBSqlserver类
DBSqlServer db = new DBSqlServer();
db.CommandText = strSql;
db.HandleExceptions = true; //开启处理错误
DataSet ds = db.ExecuteDataSet(); //访问数据库
if (db.LastError.Length == 0)//若数据访问过程中未出错
{
if (ds.Tables[0].Rows.Count != 0)//若查询到结果
{
dtASNHeader = ds.Tables[0]; //保存返回的ASN数据集
}
}
}
catch//出错处理
{
dtASNHeader = null;
}
return dtASNHeader;
}
public static Boolean UpdateASNGR(string GRlineNo,string strField,string value)
{
Boolean resultUpdateGR=false ; //保存返回是否更改成功，true为成功，false为失败
string strSql = string.Format(
@"update wms_in_bill_allot set {1}={2} where id='{0}'",
GRlineNo);
//sql语句
try
{
//调用DBSqlserver类
DBSqlServer db = new DBSqlServer();
db.CommandText = strSql;
db.HandleExceptions = true; //开启处理错误
DataSet ds = db.ExecuteDataSet(); //访问数据库
if (db.LastError.Length == 0)//若数据访问过程中未出错
{
if (ds.Tables[0].Rows.Count != 0)//若查询到结果
{
resultUpdateGR = true; //保存返回的ASN收货列表GR
}
}
}
catch//出错处理
{
resultUpdateGR = false;
}
return resultUpdateGR;
}
public static DataTable GetASNGR(string BillNo)
{
DataTable dtASNGR = null; //保存返回的ASN 收货列表GR
string strSql = string.Format(
@"select a.id, a.cell_code,a.product_code,b.product_name,a.unit_code,a.allot_quantity,a.real_quantity,a.cbm,a.net_weight,a.gross_weight,a.customized_field1 from [wms_in_bill_allot] a,[wms_product] b where a.bill_no='{0}' and a.product_code=b.product_code order by product_code",
BillNo);
//sql语句
try
{
//调用DBSqlserver类
DBSqlServer db = new DBSqlServer();
db.CommandText = strSql;
db.HandleExceptions = true; //开启处理错误
DataSet ds = db.ExecuteDataSet(); //访问数据库
if (db.LastError.Length == 0)//若数据访问过程中未出错
{
if (ds.Tables[0].Rows.Count != 0)//若查询到结果
{
dtASNGR = ds.Tables[0]; //保存返回的ASN收货列表GR
}
}
}
catch//出错处理
{
dtASNGR = null;
}
return dtASNGR;
}
public static DataTable GetASNCost(string BillNo)
{
DataTable dtASNCost = null; //保存返回的ASN 应收明细列表
//if (GateInDateFrom == "")
//{
string strSql = string.Format(
@"select b.charge_item,b.charge_desc,b.rate,b.measurement,b.quantity,b.amount,b.adjust_amount,a.update_time from [wms_billing_master] a,wms_billing_detail b
where a.[ref_bill_no]='{0}' and a.bill_no=b.bill_no and a.type_code='AP'",
BillNo);
//sql语句
try
{
//调用DBSqlserver类
DBSqlServer db = new DBSqlServer();
db.CommandText = strSql;
db.HandleExceptions = true; //开启处理错误
DataSet ds = db.ExecuteDataSet(); //访问数据库
if (db.LastError.Length == 0)//若数据访问过程中未出错
{
if (ds.Tables[0].Rows.Count != 0)//若查询到结果
{
dtASNCost = ds.Tables[0]; //保存返回的ASN应收数据集
}
}
}
catch//出错处理
{
dtASNCost = null;
}
return dtASNCost;
}
public static DataTable GetASNDetail(string BillNo)
{
DataTable dtASNDetail = null; //保存返回的ASN 明细列表
//if (GateInDateFrom == "")
//{
string strSql = string.Format(
@"select a.product_code,b.product_name as 品名,a.unit_code,a.bill_quantity,a.cbm,a.net_weight,a.gross_weight,a.description,a.customized_field1 from [wms_in_bill_detail] a,[wms_product] b where a.bill_no='{0}' and a.product_code=b.product_code order by product_code",
BillNo);
//sql语句
try
{
//调用DBSqlserver类
DBSqlServer db = new DBSqlServer();
db.CommandText = strSql;
db.HandleExceptions = true; //开启处理错误
DataSet ds = db.ExecuteDataSet(); //访问数据库
if (db.LastError.Length == 0)//若数据访问过程中未出错
{
if (ds.Tables[0].Rows.Count != 0)//若查询到结果
{
dtASNDetail = ds.Tables[0]; //保存返回的ASN数据集
}
}
}
catch//出错处理
{
dtASNDetail = null;
}
return dtASNDetail;
}