/// <summary>
/// 得到数据
/// </summary>
/// <returns></returns>
public DataTable GetBillData()
{
CommonInterface pObj_Comm = CommonFactory.CreateInstance(CommonData.sql);
try
{
string ps_Sql = "select BillGuid,BatchNo ,HandlePerson,CreatePerson,CreateDate,CheckPerson,CheckDate,Flag,Remark,BillAutoID,BillDate,DepotGuid,StorageTypeGuid,SupplierGuid,DeptGuid,Bearing,BillID from Bill order by CreateDate desc";
DataTable pDTMain = pObj_Comm.ExeForDtl(ps_Sql);
pObj_Comm.Close();
return(pDTMain);
}
catch (Exception e)
{
pObj_Comm.Close();
throw e;
}
}
/// <summary>
/// 得到数据
/// </summary>
/// <returns></returns>
public DataTable GetClientData_CN(string ClientGuid)
{
CommonInterface pObj_Comm = CommonFactory.CreateInstance(CommonData.sql);
try
{
string ps_Sql = "select Guid,SimpName,[Name] ,LinkMan,Telephone ,Fax ,Address,Zip ,Remark from Client where Guid='" + ClientGuid + "'";
DataTable pDTMain = pObj_Comm.ExeForDtl(ps_Sql);
pObj_Comm.Close();
return(pDTMain);
}
catch (Exception e)
{
pObj_Comm.Close();
throw e;
}
}
/// <summary>
/// 得到数据
/// </summary>
/// <returns></returns>
public DataTable GetClientData(string ClientGuid)
{
CommonInterface pObj_Comm = CommonFactory.CreateInstance(CommonData.sql);
try
{
string ps_Sql = "select Guid,SimpName as 客户简称,[Name] as 客户名称,LinkMan as 联系人,Telephone as 电话,Fax as 传真,Address as 地址,Zip as 邮编,Remark as 备注 from Client where Guid='" + ClientGuid + "'";
DataTable pDTMain = pObj_Comm.ExeForDtl(ps_Sql);
pObj_Comm.Close();
return(pDTMain);
}
catch (Exception e)
{
pObj_Comm.Close();
throw e;
}
}
///<summary>
/// 输入条件返回结果查询
/// </summary>
/// <param name="tableid">SQL查询条件</param>
/// <returns>存放查询结果的DataTable</returns>
public DataTable GetLoginUserInfo_CN()
{
CommonInterface pComm = CommonFactory.CreateInstance(CommonData.sql);
try
{
DataTable dtl = new DataTable();
string StrSQL = " select USERID 用户账号,USERNAME 用户中文名称,Email from LoginUser ";
dtl = pComm.ExeForDtl(StrSQL);
pComm.Close();
return(dtl);
}
catch (Exception e)
{
pComm.Close();
throw e;
}
}
//得到设备数据
public DataTable GetEquipmentDataByEquipmentGuID(string EquipmentGuID)
{
CommonInterface pObj_Comm = CommonFactory.CreateInstance(CommonData.sql);
try
{
string ps_Sql = "select *,case EquipmentState when '1' then '正常' when '0' then '停用' end as EquipmentStateName,case EquipmentType when 1 then '模具' when 2 then '计量器具' when 3 then '生产设备' when 4 then '办公设备' when 5 then '工装' end as EquipmentTypeName from Equipment where EquipmentGuID='" + EquipmentGuID + "'";
DataTable pDTMain = pObj_Comm.ExeForDtl(ps_Sql);
pObj_Comm.Close();
return(pDTMain);
}
catch (Exception e)
{
pObj_Comm.Close();
throw e;
}
}
/// <summary>
/// 得到数据
/// </summary>
/// <returns></returns>
public DataTable GetSupplierData()
{
CommonInterface pObj_Comm = CommonFactory.CreateInstance(CommonData.sql);
try
{
string ps_Sql = "select Guid,SimpName as 供应商简称,[Name] as 供应商名称,LinkMan as 联系人,Telephone as 电话,Fax as 传真,Address as 地址,Zip as 邮编,ProduceType as 生产类型,Remark as 备注,Case when IsEnable=1 then '停用' when IsEnable=0 then '可用' end as 是否可用 from Supplier ";
DataTable pDTMain = pObj_Comm.ExeForDtl(ps_Sql);
pObj_Comm.Close();
return(pDTMain);
}
catch (Exception e)
{
pObj_Comm.Close();
throw e;
}
}
/// <summary>
/// 得到数据
/// </summary>
/// <returns></returns>
public DataTable GetStockYJData(string strWheresql)
{
CommonInterface pObj_Comm = CommonFactory.CreateInstance(CommonData.sql);
try
{
string ps_Sql = "select MaterialGuID,MaterialID,MaterialName,UnitID,UnitName,Spec,SpecName,StockYJGuid,isnull(StockYJDown,0) as StockYJDown,isnull(StockYJUp,0) as StockYJUp,isnull(BomSum,0) as BomSum from V_StockYJ " + strWheresql + " Order by MaterialID ";
DataTable pDTMain = pObj_Comm.ExeForDtl(ps_Sql);
pObj_Comm.Close();
return(pDTMain);
}
catch (Exception e)
{
pObj_Comm.Close();
throw e;
}
}
/// <summary>
/// 得到数据
/// </summary>
/// <returns></returns>
public DataTable GetSupplierDataBySelect()
{
CommonInterface pObj_Comm = CommonFactory.CreateInstance(CommonData.sql);
try
{
string ps_Sql = "select Guid,[Name] from Supplier where IsEnable=0 ";
DataTable pDTMain = pObj_Comm.ExeForDtl(ps_Sql);
pObj_Comm.Close();
return(pDTMain);
}
catch (Exception e)
{
pObj_Comm.Close();
throw e;
}
}
/// <summary>
/// 得到数据
/// </summary>
/// <returns></returns>
public DataTable GetDepotData2()
{
CommonInterface pObj_Comm = CommonFactory.CreateInstance(CommonData.sql);
try
{
string ps_Sql = "select DepotGuid,DepotName as 仓库名称,DepotPerson as 负责人,Telephone as 各仓库汇总显示顺序,Remark as 各仓库汇总显示 from Depot order by convert(int,telephone) ";
DataTable pDTMain = pObj_Comm.ExeForDtl(ps_Sql);
pObj_Comm.Close();
return(pDTMain);
}
catch (Exception e)
{
pObj_Comm.Close();
throw e;
}
}
/// <summary>
/// 得到数据
/// </summary>
/// <returns></returns>
public DataTable GetStorageTypeData(string value)
{
CommonInterface pObj_Comm = CommonFactory.CreateInstance(CommonData.sql);
try
{
string ps_Sql = "select StorageTypeName from StorageType where flag='" + value + "'";
DataTable pDTMain = pObj_Comm.ExeForDtl(ps_Sql);
pObj_Comm.Close();
return(pDTMain);
}
catch (Exception e)
{
pObj_Comm.Close();
throw e;
}
}
/// <summary>
/// 得到数据
/// </summary>
/// <returns></returns>
public DataTable GetBillDetailData(string BillGuid)
{
CommonInterface pObj_Comm = CommonFactory.CreateInstance(CommonData.sql);
try
{
string ps_Sql = "select MaterialGuid,MaterialID,MaterialName,BarNo,Spec,Unit,Qty,Price,Total,Remark,(select ClassName from V_MaterialClass where MaterialGuid=BillDetail.MaterialGuid) as ClassName from BillDetail where BillGuid='" + BillGuid + "' order by SortID";
DataTable pDTMain = pObj_Comm.ExeForDtl(ps_Sql);
pObj_Comm.Close();
return(pDTMain);
}
catch (Exception e)
{
pObj_Comm.Close();
throw e;
}
}
/// <summary>
/// 得到数据
/// </summary>
/// <returns></returns>
public DataTable GetEmployeeData(string EmpGuid)
{
CommonInterface pObj_Comm = CommonFactory.CreateInstance(CommonData.sql);
try
{
string ps_Sql = "select EmpGuid,EmpID,EmpName,Sex,Telephone,Address,CardID,dept from Employee where EmpGuid='" + EmpGuid + "'";
DataTable pDTMain = pObj_Comm.ExeForDtl(ps_Sql);
pObj_Comm.Close();
return(pDTMain);
}
catch (Exception e)
{
pObj_Comm.Close();
throw e;
}
}
/// <summary>
/// 判断当前工种更改单号是否已经存在
/// </summary>
public bool IsExistChangeBillID(string ChangeBillID, string ChangeBillGuID, bool IsEdit)
{
string ps_Sql = "";
DataTable pDTMain = new DataTable();
CommonInterface pObj_Comm = CommonFactory.CreateInstance(CommonData.sql);
ps_Sql = " select ChangeBillGuID from ChangeBill where ChangeBillID='" + ChangeBillID + "' ";
try
{
DataTable dt = pObj_Comm.ExeForDtl(ps_Sql);
pObj_Comm.Close();
if (dt.Rows.Count > 0)
{
if (IsEdit == false)
{
return(true); //Exist
}
else
{
string strChangeBillGuID = dt.Rows[0]["ChangeBillGuID"].ToString();
if (strChangeBillGuID.Trim() != ChangeBillGuID.Trim())
{
return(true); //Exist
}
else
{
return(false); //Not Exist
}
}
}
else
{
return(false); //Not Exist
}
}
catch (Exception e)
{
pObj_Comm.Close();
throw e;
}
}
/// <summary>
/// 辅助数据类别：
/// </summary>
/// <param name="Flag">(1-单位 2-规格 3:封装 4:计价法)</param>
/// <returns></returns>
public DataTable GetBasicData(int Flag)
{
CommonInterface pObj_Comm = CommonFactory.CreateInstance(CommonData.sql);
try
{
string ps_Sql = "select UnitName from BasicData where flag=" + Flag.ToString() + " order by UnitID";
DataTable pDTMain = pObj_Comm.ExeForDtl(ps_Sql);
pObj_Comm.Close();
return(pDTMain);
}
catch (Exception e)
{
pObj_Comm.Close();
throw e;
}
}
///<summary>
/// 输入条件返回结果查询
/// </summary>
/// <param name="tableid">SQL查询条件</param>
/// <returns>存放查询结果的DataTable</returns>
public DataTable GetLoginUserInfo_CN()
{
CommonInterface pComm = CommonFactory.CreateInstance(CommonData.sql);
try
{
DataTable dtl = new DataTable();
string StrSQL = " select USERID 用户账号,USERNAME 用户中文名称,Email,case when IsEnable=1 then '禁用' else '可用' end as 是否禁用 from LoginUser where USERID<>'SuperAdmin' order by UserID ";
dtl = pComm.ExeForDtl(StrSQL);
pComm.Close();
return(dtl);
}
catch (Exception e)
{
pComm.Close();
throw e;
}
}
/// <summary>
/// 得到数据
/// </summary>
/// <returns></returns>
public DataTable GetEmployeeData()
{
CommonInterface pObj_Comm = CommonFactory.CreateInstance(CommonData.sql);
try
{
string ps_Sql = "select EmpGuid,EmpID as 员工编号,EmpName as 员工姓名,Sex as 性别,Telephone as 电话,Address as 地址,CardID as 身份证号,Dept as 所在部门 from Employee ";
DataTable pDTMain = pObj_Comm.ExeForDtl(ps_Sql);
pObj_Comm.Close();
return(pDTMain);
}
catch (Exception e)
{
pObj_Comm.Close();
throw e;
}
}
/// <summary>
/// 得到数据
/// </summary>
/// <returns></returns>
public DataTable GetDeptDataSelectBySQL(string strsql)
{
CommonInterface pObj_Comm = CommonFactory.CreateInstance(CommonData.sql);
try
{
string ps_Sql = "select DeptGuid,DeptName from Dept " + strsql;
DataTable pDTMain = pObj_Comm.ExeForDtl(ps_Sql);
pObj_Comm.Close();
return(pDTMain);
}
catch (Exception e)
{
pObj_Comm.Close();
throw e;
}
}
///<summary>
/// 输入条件返回结果查询
/// </summary>
/// <param name="tableid">SQL查询条件</param>
/// <returns>存放查询结果的DataTable</returns>
public int GetBillAutoId(string flag)
{
CommonInterface pComm = CommonFactory.CreateInstance(CommonData.sql);
try
{
DataTable dtl = new DataTable();
string StrSQL = " select BillAutoID from BillAutoID where Flag='" + flag + "'";
dtl = pComm.ExeForDtl(StrSQL);
pComm.Close();
return(int.Parse(dtl.Rows[0]["BillAutoID"].ToString()));
}
catch (Exception e)
{
pComm.Close();
throw e;
}
}
/// <summary>
/// 得到数据
/// </summary>
/// <returns></returns>
public DataTable GetDeptData()
{
CommonInterface pObj_Comm = CommonFactory.CreateInstance(CommonData.sql);
try
{
string ps_Sql = "select DeptGuid,DeptName as 部门名称,DeptPerson as 负责人,Telephone as 电话,Fax as 传真,address as 地址,Case when IsEnable=1 then '停用' when IsEnable=0 then '可用' end as 是否可用 from Dept ";
DataTable pDTMain = pObj_Comm.ExeForDtl(ps_Sql);
pObj_Comm.Close();
return(pDTMain);
}
catch (Exception e)
{
pObj_Comm.Close();
throw e;
}
}
/// <summary>
/// 得到数据
/// </summary>
/// <returns></returns>
public DataTable GetDeptData(string DeptGuid)
{
CommonInterface pObj_Comm = CommonFactory.CreateInstance(CommonData.sql);
try
{
string ps_Sql = "select DeptGuid,DeptName,DeptPerson,Telephone,Fax,address,IsEnable from Dept where DeptGuid='" + DeptGuid + "'";
DataTable pDTMain = pObj_Comm.ExeForDtl(ps_Sql);
pObj_Comm.Close();
return(pDTMain);
}
catch (Exception e)
{
pObj_Comm.Close();
throw e;
}
}
/// <summary>
/// 得到客户订单是否已审核或已结单-
/// </summary>
/// <param name="FatherMaterialGuid"></param>
/// <returns></returns>
public bool GetIsCheck(string ClientOrderGuid)
{
string ps_Sql = "";
DataTable pDTMain = new DataTable();
CommonInterface pObj_Comm = CommonFactory.CreateInstance(CommonData.sql);
try
{
ps_Sql = " select ClientOrderGuid,CheckGuid,CheckGuid2,EndGuid from ClientOrder where ClientOrderGuid='" + ClientOrderGuid + "' ";
pDTMain = pObj_Comm.ExeForDtl(ps_Sql);
pObj_Comm.Close();
if (pDTMain.Rows.Count > 0)
{
if (pDTMain.Rows[0]["CheckGuid"].ToString().Trim() != "")
{
return(true);
}
if (pDTMain.Rows[0]["CheckGuid2"].ToString().Trim() != "")
{
return(true);
}
if (pDTMain.Rows[0]["EndGuid"].ToString().Trim() != "")
{
return(true);
}
return(false);
}
else
{
return(true);
}
}
catch (Exception e)
{
pObj_Comm.Close();
throw e;
}
}
///<summary>
/// 输入条件返回结果查询
/// </summary>
/// <param name="tableid">SQL查询条件</param>
/// <returns>存放查询结果的DataTable</returns>
public DataTable GetLoginUserInfo()
{
CommonInterface pComm = CommonFactory.CreateInstance(CommonData.sql);
try
{
DataTable dtl = new DataTable();
string StrSQL = " select * from LoginUser where USERID<>'SuperAdmin' order by UserID ";
dtl = pComm.ExeForDtl(StrSQL);
pComm.Close();
return(dtl);
}
catch (Exception e)
{
pComm.Close();
throw e;
}
}
///<summary>
/// 输入条件返回结果查询
/// </summary>
/// <param name="tableid">SQL查询条件</param>
/// <returns>存放查询结果的DataTable</returns>
public DataTable GetSystemLogInfo(string strsql)
{
CommonInterface pComm = CommonFactory.CreateInstance(CommonData.sql);
try
{
DataTable dtl = new DataTable();
// string StrSQL = " select NoID, OperateUser as 操作员,OperateModule as 操作模块,OperateType as 操作类型,OperateContent as 操作内容,OperateDate as 操作日期 from SystemLog order by OperateDate desc ";
string StrSQL = " select 'False' as selectvalue,NoID, OperateUser,OperateModule ,OperateType ,OperateContent,convert(nvarchar(30),OperateDate,120) as OperateDate from SystemLog " + strsql + " order by OperateDate desc ";
dtl = pComm.ExeForDtl(StrSQL);
pComm.Close();
return(dtl);
}
catch (Exception e)
{
pComm.Close();
throw e;
}
}
//----------------------------仓库收发明细与部门收发明细--------------------------------
/// <summary>
/// 得到数据-仓库收发明细表
/// </summary>
/// <returns></returns>
public DataTable GetInOutDepotDetailData(string strsql)
{
CommonInterface pObj_Comm = CommonFactory.CreateInstance(CommonData.sql);
try
{
string ps_Sql = "select * from V_ALLDepotDetail " + strsql + " order by BillDate";
DataTable pDTMain = pObj_Comm.ExeForDtl(ps_Sql);
pObj_Comm.Close();
return(pDTMain);
}
catch (Exception e)
{
pObj_Comm.Close();
throw e;
}
}
/// <summary>
/// 得到数据
/// </summary>
/// <returns></returns>
public DataTable GetCheckBillData(string CheckBillGuid)
{
CommonInterface pObj_Comm = CommonFactory.CreateInstance(CommonData.sql);
try
{
string ps_Sql = "select CheckBillGuid ,HandlePerson,CreatePerson,CreateDate,CheckPerson,CheckDate,Remark,BillAutoID,BillDate,Depot,"
+ "BillID from CheckBill where CheckBillGuid='" + CheckBillGuid + "' order by CreateDate desc";
DataTable pDTMain = pObj_Comm.ExeForDtl(ps_Sql);
pObj_Comm.Close();
return(pDTMain);
}
catch (Exception e)
{
pObj_Comm.Close();
throw e;
}
}
/// <summary>
/// 得到数据
/// </summary>
/// <returns></returns>
public DataTable GetEmployeeData()
{
CommonInterface pObj_Comm = CommonFactory.CreateInstance(CommonData.sql);
try
{
string ps_Sql = "select EmpGuid,EmpID as 员工编号,EmpName as 员工姓名,(select deptname from Dept where Dept.deptguid=Employee.Dept) as 所在部门,Sex as 性别,Telephone as 电话,Address as 地址,CardID as 身份证号,"
+ " Case when IsEnable=1 then '停用' when IsEnable=0 then '可用' end as 是否可用 from Employee ";
DataTable pDTMain = pObj_Comm.ExeForDtl(ps_Sql);
pObj_Comm.Close();
return(pDTMain);
}
catch (Exception e)
{
pObj_Comm.Close();
throw e;
}
}
/// <summary>
/// 得到数据-调拨单
/// </summary>
/// <returns></returns>
public DataTable GetCheckBillData_CN()
{
CommonInterface pObj_Comm = CommonFactory.CreateInstance(CommonData.sql);
try
{
string ps_Sql = "select CheckBillGuid,BillAutoID as 编号,BillID as 单号,BillDate as 业务日期,Depot as 仓库,"
+ "HandlePerson as 经手人, CreateDate as 制单时间,CheckPerson as 审核人,CheckDate as 审核日期 from CheckBill order by CreateDate desc";
DataTable pDTMain = pObj_Comm.ExeForDtl(ps_Sql);
pObj_Comm.Close();
return(pDTMain);
}
catch (Exception e)
{
pObj_Comm.Close();
throw e;
}
}
/// <summary>
/// 得到采购订单主表数据
/// </summary>
/// <param name="FatherMaterialGuid"></param>
/// <returns></returns>
public DataTable GetHalfGoodsByHalfGoodsGuid(string HalfGoodsGuid)
{
string ps_Sql = "";
DataTable pDTMain = new DataTable();
CommonInterface pObj_Comm = CommonFactory.CreateInstance(CommonData.sql);
try
{
ps_Sql = "select * from V_HalfGoods where HalfGoodsGuid='" + HalfGoodsGuid + "'";
pDTMain = pObj_Comm.ExeForDtl(ps_Sql);
pObj_Comm.Close();
return(pDTMain);
}
catch (Exception e)
{
pObj_Comm.Close();
throw e;
}
}
/// <summary>
/// 得到领料单主表数据
/// </summary>
/// <param name="FatherMaterialGuid"></param>
/// <returns></returns>
public DataTable GetHalfGoodsBySQL(string strsql)
{
string ps_Sql = "";
DataTable pDTMain = new DataTable();
CommonInterface pObj_Comm = CommonFactory.CreateInstance(CommonData.sql);
try
{
ps_Sql = "select * from V_HalfGoods " + strsql + " order by CreateDate desc";
pDTMain = pObj_Comm.ExeForDtl(ps_Sql);
pObj_Comm.Close();
return(pDTMain);
}
catch (Exception e)
{
pObj_Comm.Close();
throw e;
}
}
/// <summary>
/// 得到采购订单主表数据
/// </summary>
/// <param name="FatherMaterialGuid"></param>
/// <returns></returns>
public DataTable GetStockOrderByStockOrderGuid(string StockOrderGuid)
{
string ps_Sql = "";
DataTable pDTMain = new DataTable();
CommonInterface pObj_Comm = CommonFactory.CreateInstance(CommonData.sql);
try
{
ps_Sql = "select * from V_StockOrder where StockOrderGuid='" + StockOrderGuid + "' order by CreateDate desc";
pDTMain = pObj_Comm.ExeForDtl(ps_Sql);
pObj_Comm.Close();
return(pDTMain);
}
catch (Exception e)
{
pObj_Comm.Close();
throw e;
}
}
