public SqlDataReader datareader(Model.downlist md)
{
string str = "select * from downlist where _id=" + md.id + " ";
SqlDataReader sdr = DbHelperSQL.ExecuteReader(str);
return(sdr);
}
public static SqlDataReader SearchAllSuperviseList()
{
string sql = "select * from R_Supervise";
List <R_Supervise> list = new List <R_Supervise>();
SqlDataReader read = DbHelperSQL.ExecuteReader(sql);
return(read);
}
public List <Empolyee> SelEmpList()
{
List <Empolyee> list = new List <Empolyee>();
string sql = @"select Emp_Code,
Emp_Name,
r.Role_Name,
Emp_Area,
Emp_CardCode,
dep.Dt_Name,
Emp_Sex,
Emp_Card,
Emp_Phone,
Emp_Bank,
Emp_OpenBank,
Emp_Birthday,
Emp_Email,
Emp_Education,
Emp_School,
Emp_Entry,
Emp_State,
Emp_Enable
from T_Empolyee emp,
T_Role r,
T_Department dep
where Emp_Clear=1
and emp.Emp_Depid=dep.Dt_Code
and emp.Emp_UserRole=r.Role_Code";
SqlDataReader read = DbHelperSQL.ExecuteReader(sql);
while (read.Read())
{
Empolyee emp = new Empolyee()
{
Emp_CardCode = XYEEncoding.strHexDecode(read["Emp_CardCode"].ToString()),
Emp_Area = XYEEncoding.strHexDecode(read["Emp_Area"].ToString()),
Emp_Code = XYEEncoding.strHexDecode(read["Emp_Code"].ToString()),
Emp_Sex = XYEEncoding.strHexDecode(read["Emp_Sex"].ToString()),
Emp_Card = XYEEncoding.strHexDecode(read["Emp_Card"].ToString()),
Emp_Phone = XYEEncoding.strHexDecode(read["Emp_Phone"].ToString()),
Emp_Bank = XYEEncoding.strHexDecode(read["Emp_Bank"].ToString()),
Emp_OpenBank = XYEEncoding.strHexDecode(read["Emp_OpenBank"].ToString()),
Emp_Birthday = Convert.ToDateTime(read["Emp_Birthday"].ToString()),
Emp_Email = XYEEncoding.strHexDecode(read["Emp_Email"].ToString()),
Emp_Education = XYEEncoding.strHexDecode(read["Emp_Education"].ToString()),
Emp_School = XYEEncoding.strHexDecode(read["Emp_School"].ToString()),
Emp_Entry = Convert.ToDateTime(read["Emp_Entry"].ToString()),
Emp_State = Convert.ToInt32(read["Emp_State"].ToString()),
Emp_Enable = Convert.ToInt32(read["Emp_Enable"].ToString()),
Roles = new RoleService().GetModel(read["Emp_UserRole"] == null ? "" : read["Emp_UserRole"].ToString()),
Departments = new DepartmentService().SelDepartmentByCode(read["Emp_Depid"] == null ? "" : read["Emp_Depid"].ToString())
};
list.Add(emp);
}
return(list);
}
public Sell SelSellGatheringBySellCode(string Sell_Code)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 Sell_ID,Sell_Code,Sell_Type,Sell_Date,Sell_TransportType,Sell_Review,Sell_ChangeDate,Sell_Operation,Sell_Auditman,Sell_Remark,");
strSql.Append("Sell_IsPay,Sell_IsPutSto,Sell_PayMathod,Sell_GetDate,Sell_Logistics,Sell_LogCode,Sell_LogPhone,Sell_OddMoney,Sell_AccountCode,Sell_InMoney,Sell_LastMoney,Sell_Address,");
strSql.Append("Sell_ClientName,Sell_CliPhone,Sell_LinkMan,Sell_Salesman,Sell_OddStatus,Sell_jiajiState,Sell_zuiwanshijian,Sell_fukuanfangshi from T_Sell");
strSql.Append(" where Sell_Code=@Sell_Code");
SqlParameter[] parameters =
{
new SqlParameter("@Sell_Code", SqlDbType.NVarChar, 512)
};
parameters[0].Value = XYEEncoding.strCodeHex(Sell_Code);
SqlDataReader read = DbHelperSQL.ExecuteReader(strSql.ToString(), parameters);
while (read.Read())
{
Sell model = new Sell();
model.Sell_ID = Convert.ToInt32(read["Sell_ID"]);
model.Sell_Code = XYEEncoding.strHexDecode(read["Sell_Code"].ToString());
model.Sell_Type = XYEEncoding.strHexDecode(read["Sell_Type"].ToString());
model.Sell_Date = Convert.ToDateTime(read["Sell_Date"]);
model.Sell_TransportType = XYEEncoding.strHexDecode(read["Sell_TransportType"].ToString());
model.Sell_Review = Convert.ToInt32(read["Sell_Review"]);
model.Sell_ChangeDate = Convert.ToDateTime(read["Sell_ChangeDate"]);
model.Sell_Operation = XYEEncoding.strHexDecode(read["Sell_Operation"].ToString());
model.Sell_Auditman = XYEEncoding.strHexDecode(read["Sell_Auditman"].ToString());
model.Sell_Remark = XYEEncoding.strHexDecode(read["Sell_Remark"].ToString());
model.Sell_IsPay = Convert.ToInt32(read["Sell_IsPay"]);
model.Sell_IsPutSto = Convert.ToInt32(read["Sell_IsPutSto"]);
model.Sell_PayMathod = Convert.ToInt32(read["Sell_PayMathod"]);
model.Sell_GetDate = Convert.ToDateTime(read["Sell_GetDate"]);
model.Sell_Logistics = XYEEncoding.strHexDecode(read["Sell_Logistics"].ToString());
model.Sell_LogCode = XYEEncoding.strHexDecode(read["Sell_LogCode"].ToString());
model.Sell_LogPhone = XYEEncoding.strHexDecode(read["Sell_LogPhone"].ToString());
model.Sell_OddMoney = read["Sell_OddMoney"].ToString();
model.Sell_AccountCode = XYEEncoding.strHexDecode(read["Sell_AccountCode"].ToString());
model.Sell_InMoney = read["Sell_InMoney"].ToString();
model.Sell_LastMoney = read["Sell_LastMoney"].ToString();
model.Sell_Address = XYEEncoding.strHexDecode(read["Sell_Address"].ToString());
model.Sell_ClientName = XYEEncoding.strHexDecode(read["Sell_ClientName"].ToString());
model.Sell_CliPhone = XYEEncoding.strHexDecode(read["Sell_CliPhone"].ToString());
model.Sell_LinkMan = XYEEncoding.strHexDecode(read["Sell_LinkMan"].ToString());
model.Sell_Salesman = XYEEncoding.strHexDecode(read["Sell_Salesman"].ToString());
model.Sell_OddStatus = Convert.ToInt32(read["Sell_OddStatus"]);
model.Sell_jiajiState = Convert.ToInt32(read["Sell_jiajiState"]);
model.Sell_zuiwanshijian = Convert.ToDateTime(read["Sell_zuiwanshijian"]);
model.Sell_fukuanfangshi = XYEEncoding.strHexDecode(read["Sell_fukuanfangshi"].ToString());
return(model);
}
return(null);
}
public static SqlDataReader StaffLogin(S_UserInFo userInFo)
{
string sql1 = "select * from S_UserInFo where UserID= '{0}'and UserPWD='{1}'";
sql1 = string.Format(sql1, userInFo.UserID, userInFo.UserPWD);
try
{
SqlDataReader read = DbHelperSQL.ExecuteReader(sql1);
return(read);
}
catch
{
return(null);
}
}
//返回SqlDataReader
public static SqlDataReader selectDepartments()
{
string sql = "select * from C_Code where CodeClass='部门'";
sql = string.Format(sql);
try
{
SqlDataReader read = DbHelperSQL.ExecuteReader(sql);
return(read);
}
catch
{
return(null);
}
}
public static SqlDataReader SearchFileByAssignIDAndUserID(int assignID, string userID)
{
string sql = "select * from F_Files where SuperviseAssignID='{0}' and UserID='{1}'";
sql = string.Format(sql, assignID, userID);
try
{
SqlDataReader read = DbHelperSQL.ExecuteReader(sql);
return(read);
}
catch
{
return(null);
}
}
public static SqlDataReader FindSuperviseAssignByMxIDNotDep(int mxID)
{
string sql = "select * from R_SuperviseAssign where mxID='{0}' and AssignNo not like '%部'";
sql = string.Format(sql, mxID);
try
{
SqlDataReader read = DbHelperSQL.ExecuteReader(sql);
return(read);
}
catch
{
return(null);
}
}
public static SqlDataReader FindSuperviseAssignByRID(int rID)
{
string sql = "select R_SuperviseAssign.AssignNo,(select DeptName from vwS_UserInFo where R_SuperviseAssign.AssignNo=UserID) as DeptName from R_SuperviseAssign where RID='{0}'";
sql = string.Format(sql, rID);
try
{
SqlDataReader read = DbHelperSQL.ExecuteReader(sql);
return(read);
}
catch
{
return(null);
}
}
public int InsertReturnID(string sql, Hashtable paramlist = null)
{
// SqlParameter[] cmdParms = BuildSqlParameters(paramlist);
sql += ";select @@IDENTITY as id";
int rs = 0;
using (IDataReader dr = DbHelperSQL.ExecuteReader(sql, paramlist))
{
if (dr.Read())
{
rs = Convert.ToInt32(dr["id"]);
}
dr.Close();
}
return(rs);
}
/// <summary>
/// 根据商品名查询建议售价
/// </summary>
/// <param name="Ma_Name"></param>
/// <returns></returns>
public Material SelPriceByMaName(string Ma_Name)
{
string sql = string.Format("select * from T_Material where Ma_Name='{0}'", XYEEncoding.strCodeHex(Ma_Name));
SqlDataReader read = DbHelperSQL.ExecuteReader(sql);
Material material = new Material();
DataSet ds = DbHelperSQL.Query(sql);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 查询所有信息
/// </summary>
/// <param name="isDisEC">isDisEC：true仅显示启用,false显示启用和未删除</param>
/// <returns></returns>
public List <Supplier> SelSupplier(bool isDisEC)
{
List <Supplier> list = new List <Supplier>();
string sql = "select * from T_Supplier";
if (isDisEC == true)
{
sql += " where Su_Clear=1";
}
else
{
sql += " where Su_Enable=1 and Su_Clear=1";
}
SqlDataReader read = DbHelperSQL.ExecuteReader(sql);
while (read.Read())
{
Supplier supplier = new Supplier()
{
Su_ID = Convert.ToInt32(read["Su_ID"]),
Su_Name = XYEEncoding.strHexDecode(read["Su_Name"].ToString()),
Su_Phone = XYEEncoding.strHexDecode(read["Su_Phone"].ToString()),
Su_Address = XYEEncoding.strHexDecode(read["Su_Address"].ToString()),
Su_fax = XYEEncoding.strHexDecode(read["Su_fax"].ToString()),
Su_Email = XYEEncoding.strHexDecode(read["Su_email"].ToString()),
Su_Bankaccounts = XYEEncoding.strHexDecode(read["Su_Bankaccounts"].ToString()),
Su_Bank = XYEEncoding.strHexDecode(read["Su_Bank"].ToString()),
Su_Profession = XYEEncoding.strHexDecode(read["Su_Profession"].ToString()),
Su_ProCode = XYEEncoding.strHexDecode(read["Su_ProCode"].ToString()),
Su_Credit = XYEEncoding.strHexDecode(read["Su_Credit"].ToString()),
Su_Money = XYEEncoding.strHexDecode(read["Su_Money"].ToString()),
Su_Surplus = XYEEncoding.strHexDecode(read["Su_Surplus"].ToString()),
Su_Reckoning = XYEEncoding.strHexDecode(read["Su_Reckoning"].ToString()),
Su_Empname = XYEEncoding.strHexDecode(read["Su_Empname"].ToString()),
Su_EmpPhone = XYEEncoding.strHexDecode(read["Su_EmpPhone"].ToString()),
Su_Remark = XYEEncoding.strHexDecode(read["Su_Remark"].ToString()),
Su_Enable = Convert.ToInt32(read["Su_Enable"].ToString()),
Su_Clear = Convert.ToInt32(read["Su_Clear"].ToString()),
Su_Code = XYEEncoding.strHexDecode(read["Su_Code"].ToString()),
Su_Area = XYEEncoding.strHexDecode(read["Su_Area"].ToString()),
Su_EnableStr = Convert.ToInt32(read["Su_Enable"].ToString()) == 1 ? "未禁用" : "已禁用"
};
list.Add(supplier);
}
return(list);
}
/// <summary>
/// 根据ID查询信息
/// </summary>
/// <param name="TypeID">类型ID</param>
/// <returns>List集合</returns>
public List <Borrow> SelBorrowByTypeID(int TypeID)
{
List <Borrow> list = new List <Borrow>();
string sql = string.Format("select * from T_Borrow where Bo_TypeID={0}", TypeID);
SqlDataReader read = DbHelperSQL.ExecuteReader(sql);
while (read.Read())
{
Borrow tb = new Borrow()
{
Bo_TypeID = Convert.ToInt32(read["Bo_TypeID"]),
Bo_TypeName = read["Bo_TypeName"].ToString()
};
list.Add(tb);
}
return(list);
}
/// <summary>
/// 根据编号查询信息
/// </summary>
/// <param name="Ot_ID">编号</param>
/// <returns></returns>
public OrderType SelOrderTypeByCode(int Ot_ID)
{
string sql = "select * from T_OrderType";
SqlDataReader read = DbHelperSQL.ExecuteReader(sql);
while (read.Read())
{
OrderType ot = new OrderType()
{
Ot_ID = Convert.ToInt32(read["Ot_ID"]),
Ot_Code = read["Ot_Code"].ToString(),
Ot_Name = read["Ot_Name"].ToString()
};
return(ot);
}
return(null);
}
/// <summary>
/// 自定义where查询
/// </summary>
/// <returns></returns>
public List <Supplier> SelSupplierByWhere(string SQLWhere)
{
List <Supplier> list = new List <Supplier>();
string sql = string.Format("select * from T_Supplier ");
if (!string.IsNullOrWhiteSpace(SQLWhere))
{
sql = string.Format("{0} where {1} and Su_Enable=1 and Su_Clear=1", sql, SQLWhere);
}
else
{
sql += SQLWhere + " where Su_Enable=1 and Su_Clear=1";
}
SqlDataReader read = DbHelperSQL.ExecuteReader(sql);
while (read.Read())
{
Supplier supplier = new Supplier()
{
Su_ID = Convert.ToInt32(read["Su_ID"]),
Su_Name = XYEEncoding.strHexDecode(read["Su_Name"].ToString()),
Su_Phone = XYEEncoding.strHexDecode(read["Su_Phone"].ToString()),
Su_Address = XYEEncoding.strHexDecode(read["Su_Address"].ToString()),
Su_fax = XYEEncoding.strHexDecode(read["Su_fax"].ToString()),
Su_Email = XYEEncoding.strHexDecode(read["Su_email"].ToString()),
Su_Bankaccounts = XYEEncoding.strHexDecode(read["Su_Bankaccounts"].ToString()),
Su_Bank = XYEEncoding.strHexDecode(read["Su_Bank"].ToString()),
Su_Profession = XYEEncoding.strHexDecode(read["Su_Profession"].ToString()),
Su_ProCode = XYEEncoding.strHexDecode(read["Su_ProCode"].ToString()),
Su_Credit = XYEEncoding.strHexDecode(read["Su_Credit"].ToString()),
Su_Money = XYEEncoding.strHexDecode(read["Su_Money"].ToString()),
Su_Surplus = XYEEncoding.strHexDecode(read["Su_Surplus"].ToString()),
Su_Reckoning = XYEEncoding.strHexDecode(read["Su_Reckoning"].ToString()),
Su_Empname = XYEEncoding.strHexDecode(read["Su_Empname"].ToString()),
Su_EmpPhone = XYEEncoding.strHexDecode(read["Su_EmpPhone"].ToString()),
Su_Remark = XYEEncoding.strHexDecode(read["Su_Remark"].ToString()),
Su_Enable = Convert.ToInt32(read["Su_Enable"].ToString()),
Su_Clear = Convert.ToInt32(read["Su_Clear"].ToString()),
Su_Code = XYEEncoding.strHexDecode(read["Su_Code"].ToString()),
Su_EnableStr = Convert.ToInt32(read["Su_Enable"].ToString()) == 1 ? "未禁用" : "已禁用"
};
list.Add(supplier);
}
return(list);
}
public List <Buy> SelBuyByCode(string code)
{
List <Buy> list = new List <Buy>();
string sql = string.Format("select * from T_Buy where Buy_Clear=1 and Buy_Code={0}", code);
SqlDataReader read = DbHelperSQL.ExecuteReader(sql);
while (read.Read())
{
Buy buy = new Buy()
{
Buy_ID = Convert.ToInt32(read["Buy_ID"]),
Buy_Code = XYEEncoding.strHexDecode(read["Buy_Code"].ToString()),
Buy_Date = Convert.ToDateTime(read["Buy_Date"])
};
list.Add(buy);
}
return(list);
}
/// <summary>
/// 根据编号查询信息
/// </summary>
/// <param name="Dt_Code">编号</param>
/// <returns></returns>
public Department SelDepartmentByCode(string Dt_Code)
{
string sql = string.Format("select * from T_Department WHERE Dt_Code='{0}'", Dt_Code);
SqlDataReader read = DbHelperSQL.ExecuteReader(sql);
while (read.Read())
{
Department department = new Department()
{
Dt_ID = Convert.ToInt32(read["Dt_ID"]),
Dt_Name = read["Dt_Name"].ToString(),
Dt_RoleCode = read["Dt_RoleCode"].ToString(),
Dt_Code = read["Dt_Code"].ToString(),
Dt_Clear = Convert.ToInt32(read["Dt_Clear"])
};
return(department);
}
return(null);
}
/// <summary>
/// 查询所有信息
/// </summary>
/// <returns>List集合</returns>
public List <ProjectCost> SelProjectCost()
{
List <ProjectCost> list = new List <ProjectCost>();
string sql = "select * from T_ProjectCost where PIC_Clear=1 and PIC_Enable=1";
SqlDataReader read = DbHelperSQL.ExecuteReader(sql);
while (read.Read())
{
ProjectCost pic = new ProjectCost()
{
PC_ID = Convert.ToInt32(read["PIC_ID"]),
PC_Name = XYEEncoding.strHexDecode(read["PIC_Name"].ToString()),
PC_Code = XYEEncoding.strHexDecode(read["PIC_Code"].ToString()),
PC_ParentId = XYEEncoding.strHexDecode(read["PIC_ParentId"].ToString()),
PC_Clear = Convert.ToInt32(read["PIC_Clear"].ToString()),
PC_Enable = Convert.ToInt32(read["PIC_Enable"].ToString())
};
list.Add(pic);
}
return(list);
}
/// <summary>
/// 查询所有信息
/// </summary>
/// <returns>List集合</returns>
public List <City> SelCity()
{
List <City> list = new List <City>();
string sql = "select * from T_City where City_Clear=1 and City_Enable=1";
SqlDataReader read = DbHelperSQL.ExecuteReader(sql);
while (read.Read())
{
City city = new City()
{
City_ID = Convert.ToInt32(read["City_ID"]),
City_Name = XYEEncoding.strHexDecode(read["City_Name"].ToString()),
City_Code = XYEEncoding.strHexDecode(read["City_Code"].ToString()),
City_ParentId = XYEEncoding.strHexDecode(read["City_ParentId"].ToString()),
City_Clear = Convert.ToInt32(read["City_Clear"].ToString()),
City_Enable = Convert.ToInt32(read["City_Enable"].ToString())
};
list.Add(city);
}
return(list);
}
/// <summary>
/// 根据编号查询信息
/// </summary>
/// <returns>List集合</returns>
public List <Profession> SelProfessionByCode(string ST_Code)
{
List <Profession> list = new List <Profession>();
string sql = string.Format("select * from T_Profession where ST_Code={0} and ST_Enable=1 and ST_Clear=1", XYEEncoding.strCodeHex(ST_Code));
SqlDataReader read = DbHelperSQL.ExecuteReader(sql);
while (read.Read())
{
Profession profession = new Profession()
{
ST_ID = Convert.ToInt32(read["ST_ID"]),
ST_Name = XYEEncoding.strHexDecode(read["ST_Name"].ToString()),
ST_Code = XYEEncoding.strHexDecode(read["ST_Code"].ToString()),
ST_ParentId = XYEEncoding.strHexDecode(read["ST_ParentId"].ToString()),
ST_Enable = Convert.ToInt32(read["ST_Enable"]),
ST_Clear = Convert.ToInt32(read["ST_Clear"]),
};
list.Add(profession);
}
return(list);
}
/// <summary>
/// 根据编号查询信息 单条
/// </summary>
/// <param name="Ba_Code">编号</param>
/// <returns></returns>
public BankAccount SelBankAccountByCode(string Ba_Code)
{
string sql = string.Format("select top 1 * from T_BankAccount where Ba_Code='{0}'", XYEEncoding.strCodeHex(Ba_Code));
SqlDataReader read = DbHelperSQL.ExecuteReader(sql);
while (read.Read())
{
BankAccount BankAccount = new BankAccount()
{
Ba_ID = Convert.ToInt32(read["Ba_ID"]),
Ba_Code = read["Ba_Code"].ToString(),
Ba_OpenBank = read["Ba_OpenBank"].ToString(),
Ba_Account = read["Ba_Account"].ToString(),
Ba_CardHolder = read["Ba_CardHolder"].ToString(),
Ba_Remark = read["Ba_Remark"].ToString(),
Ba_AvailablePrice = read["Ba_Remark"].ToString(),
Ba_Enable = Convert.ToInt32(read["Ba_Enable"])
};
return(BankAccount);
}
return(null);
}
/// <summary>
/// 根据城市编号查询信息
/// </summary>
/// <returns></returns>
public List <Supplier> SelSupplierByCityCode(string Su_CityName)
{
List <Supplier> list = new List <Supplier>();
string sql = string.Format("select * from T_Supplier where Su_Area like '%{0}%' and Su_Enable=1 and Su_Clear=1", XYEEncoding.strCodeHex(Su_CityName));
SqlDataReader read = DbHelperSQL.ExecuteReader(sql);
while (read.Read())
{
Supplier supplier = new Supplier();
supplier.Su_ID = Convert.ToInt32(read["Su_ID"]);
supplier.Su_Name = XYEEncoding.strHexDecode(read["Su_Name"].ToString());
supplier.Su_Phone = XYEEncoding.strHexDecode(read["Su_Phone"].ToString());
supplier.Su_Address = XYEEncoding.strHexDecode(read["Su_Address"].ToString());
supplier.Su_fax = XYEEncoding.strHexDecode(read["Su_fax"].ToString());
supplier.Su_Email = XYEEncoding.strHexDecode(read["Su_email"].ToString());
supplier.Su_Bankaccounts = XYEEncoding.strHexDecode(read["Su_Bankaccounts"].ToString());
supplier.Su_Bank = XYEEncoding.strHexDecode(read["Su_Bank"].ToString());
supplier.Su_Profession = XYEEncoding.strHexDecode(read["Su_Profession"].ToString());
supplier.Su_ProCode = XYEEncoding.strHexDecode(read["Su_ProCode"].ToString());
supplier.Su_Credit = XYEEncoding.strHexDecode(read["Su_Credit"].ToString());
supplier.Su_Money = XYEEncoding.strHexDecode(read["Su_Money"].ToString());
supplier.Su_Surplus = XYEEncoding.strHexDecode(read["Su_Surplus"].ToString());
supplier.Su_Reckoning = XYEEncoding.strHexDecode(read["Su_Reckoning"].ToString());
supplier.Su_Empname = XYEEncoding.strHexDecode(read["Su_Empname"].ToString());
supplier.Su_EmpPhone = XYEEncoding.strHexDecode(read["Su_EmpPhone"].ToString());
supplier.Su_Remark = XYEEncoding.strHexDecode(read["Su_Remark"].ToString());
supplier.Su_Enable = Convert.ToInt32(read["Su_Enable"].ToString());
supplier.Su_Clear = Convert.ToInt32(read["Su_Clear"].ToString());
supplier.Su_Code = XYEEncoding.strHexDecode(read["Su_Code"].ToString());
supplier.Su_Area = XYEEncoding.strHexDecode(read["Su_Area"].ToString());
supplier.Su_EnableStr = Convert.ToInt32(read["Su_Enable"].ToString()) == 1 ? "未禁用" : "已禁用";
list.Add(supplier);
}
;
return(list);
}