/// <summary>
/// 得到某个料件的平均单价
/// </summary>
/// <returns></returns>
public decimal sp_GetMaterialPrice(string MaterialGuID, string Depot)
{
CommonInterface pComm = CommonFactory.CreateInstance(CommonData.sql);
try
{
decimal price = 0;
DataSet dset = new DataSet();
string[,] par;
par = new string[1, 2];
par[0, 0] = "@MaterialGuID";
par[0, 1] = MaterialGuID;
dset = pComm.ExcuteSp("sp_GetMaterialSumByDepot", par);
pComm.Close();
if (dset.Tables[0].Rows.Count > 0)
{
DataRow[] drArr = dset.Tables[0].Select("DepotName='" + Depot + "'");
if (drArr.Length > 0)
{
DataRow dr = drArr[0];
price = decimal.Parse(dr[2].ToString());
}
}
return(price);
}
catch (Exception e)
{
pComm.Close();
throw e;
}
}
/// <summary>
/// 得到数据-各仓库汇总表
/// </summary>
/// <returns></returns>
public DataTable sp_GetAllDepotSum(string MaterialGuid, string begindate, string enddate)
{
CommonInterface pComm = CommonFactory.CreateInstance(CommonData.sql);
try
{
DataSet dset = new DataSet();
string[,] par;
par = new string[3, 2];
par[0, 0] = "@MaterialGuid";
par[0, 1] = MaterialGuid;
par[1, 0] = "@BeginDate";
par[1, 1] = begindate;
par[2, 0] = "@EndDate";
par[2, 1] = enddate;
dset = pComm.ExcuteSp("sp_GetAllDepotSum", par);
pComm.Close();
return(dset.Tables[0]);
}
catch (Exception e)
{
pComm.Close();
throw e;
}
}
/// <summary>
/// 收发存汇总表
/// </summary>
/// <param name="strsql"></param>
/// <returns></returns>
public DataTable sp_GetInOutStorageSum(string strbegindate, string strenddate, string MaterialGuid, string StorageID, string MaterialTypeGuid, string SupplierGuid)
{
CommonInterface pComm = CommonFactory.CreateInstance(CommonData.sql);
try
{
DataSet dset = new DataSet();
string[,] par;
par = new string[6, 2];
par[0, 0] = "@BeginDate";
par[0, 1] = strbegindate;
par[1, 0] = "@EndDate";
par[1, 1] = strenddate;
par[2, 0] = "@MaterialGuid";
par[2, 1] = MaterialGuid;
par[3, 0] = "@StorageID";
par[3, 1] = StorageID;
par[4, 0] = "@MaterialTypeGuid";
par[4, 1] = MaterialTypeGuid;
par[5, 0] = "@Supplier";
par[5, 1] = SupplierGuid;
dset = pComm.ExcuteSp("sp_GetInOutStorageSum", par);
pComm.Close();
return(dset.Tables[0]);
}
catch (Exception e)
{
pComm.Close();
throw e;
}
}
//判断文件是否被使用(文件入库、文件申请、工程更改单)
public bool IsFileUse(string FileGuID)
{
CommonInterface pComm = CommonFactory.CreateInstance(CommonData.sql);
try
{
DataSet dset = new DataSet();
string[,] par;
par = new string[1, 2];
par[0, 0] = "@FileGuID";
par[0, 1] = FileGuID;
dset = pComm.ExcuteSp("sp_FileIsUse", par);
pComm.Close();
if (dset.Tables[0].Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception e)
{
pComm.Close();
throw e;
}
}
//采购订单执行情况报表,
public DataTable sp_GetSelectStockOrderDataReport(string strStockOrderID, string begindate, string enddate, string endflag)
{
CommonInterface pComm = CommonFactory.CreateInstance(CommonData.sql);
try
{
DataSet dset = new DataSet();
string[,] par;
par = new string[4, 2];
par[0, 0] = "@StockOrderID";
par[0, 1] = strStockOrderID;
par[1, 0] = "@BeginDate";
par[1, 1] = begindate;
par[2, 0] = "@EndDate";
par[2, 1] = enddate;
par[3, 0] = "@endFlag";
par[3, 1] = endflag;
dset = pComm.ExcuteSp("sp_GetSelectStockOrderDataReport", par);
pComm.Close();
return(dset.Tables[0]);
}
catch (Exception e)
{
pComm.Close();
throw e;
}
}
//-------------------------------------------------------------------------------------------------
/// <summary>
/// 得到数据-库存查询(包括出入库，调拨)
/// </summary>
/// <param name="strsql">查询条件仓库</param>
/// <param name="strsql2">查询条件调拨</param>
/// <param name="depot">仓库名</param>
/// <returns></returns>
public DataTable sp_GetDepotMaterailSum(string strsql1, string strsql2, string strsql3, string depot, string strsql4, string strsql5)
{
CommonInterface pComm = CommonFactory.CreateInstance(CommonData.sql);
try
{
DataSet dset = new DataSet();
string[,] par;
par = new string[6, 2];
par[0, 0] = "@WhereSql";
par[0, 1] = strsql1;
par[1, 0] = "@WhereSql2";
par[1, 1] = strsql2;
par[2, 0] = "@WhereSql3";
par[2, 1] = strsql3;
par[3, 0] = "@Depot";
par[3, 1] = depot;
par[4, 0] = "@WhereSql4";
par[4, 1] = strsql4;
par[5, 0] = "@WhereSql5";
par[5, 1] = strsql5;
dset = pComm.ExcuteSp("sp_GetDepotDetailSum", par);
pComm.Close();
return(dset.Tables[0]);
}
catch (Exception e)
{
pComm.Close();
throw e;
}
}
//销售出库单从客户订单中选择数据，这里是加载数据,数据中已经包括:客户订单数量,已出库数量,可出库数量
public DataTable sp_GetSelectClientOrderData(string strClientOrderGuID, string begindate, string enddate)
{
CommonInterface pComm = CommonFactory.CreateInstance(CommonData.sql);
try
{
DataSet dset = new DataSet();
string[,] par;
par = new string[3, 2];
par[0, 0] = "@ClientOrderGuID";
par[0, 1] = strClientOrderGuID;
par[1, 0] = "@BeginDate";
par[1, 1] = begindate;
par[2, 0] = "@EndDate";
par[2, 1] = enddate;
dset = pComm.ExcuteSp("sp_GetSelectClientOrderData", par);
pComm.Close();
return(dset.Tables[0]);
}
catch (Exception e)
{
pComm.Close();
throw e;
}
}
/// <summary>
/// 库存报表
/// </summary>
/// <param name="MaterialGuid"></param>
/// <param name="StorageID"></param>
/// <param name="MaterialTypeGuid"></param>
/// <param name="EndDate"></param>
/// <returns></returns>
public DataTable StockReport(string MaterialGuid, string StorageID, string MaterialTypeGuid, string EndDate)
{
CommonInterface pComm = CommonFactory.CreateInstance(CommonData.sql);
try
{
DataSet dset = new DataSet();
string[,] par;
par = new string[4, 2];
par[0, 0] = "@MaterialGuid";
par[0, 1] = MaterialGuid;
par[1, 0] = "@StorageID";
par[1, 1] = StorageID;
par[2, 0] = "@MaterialTypeGuid";
par[2, 1] = MaterialTypeGuid;
par[3, 0] = "@EndDate";
par[3, 1] = EndDate;
dset = pComm.ExcuteSp("sp_GetStorageReport", par);
pComm.Close();
return(dset.Tables[0]);
}
catch (Exception e)
{
pComm.Close();
throw e;
}
}
/// <summary>
/// 采购付款单报表
/// </summary>
/// <param name="strsql"></param>
/// <returns></returns>
public DataTable sp_Payment_Report(string strbegindate, string strenddate)
{
CommonInterface pComm = CommonFactory.CreateInstance(CommonData.sql);
try
{
DataSet dset = new DataSet();
string[,] par;
par = new string[2, 2];
par[0, 0] = "@BeginDate";
par[0, 1] = strbegindate;
par[1, 0] = "@EndDate";
par[1, 1] = strenddate;
dset = pComm.ExcuteSp("sp_Payment_Report", par);
pComm.Close();
return(dset.Tables[0]);
}
catch (Exception e)
{
pComm.Close();
throw e;
}
}
//-------------------------------------------------------------
/// <summary>
/// 得到当前子件的用量即单耗
/// </summary>
/// <param name="MaterialGuid">料件Guid</param>
/// <param name="ProductGuid">产品Guid</param>
/// <returns></returns>
public DataSet sp_GetMaterialBomConsumeByDrawOrder(string strsql, string FatherGuid)
{
CommonInterface pComm = CommonFactory.CreateInstance(CommonData.sql);
try
{
DataSet dset = new DataSet();
string[,] par;
par = new string[2, 2];
par[0, 0] = "@MaterialFatherGuid";
par[0, 1] = FatherGuid;
par[1, 0] = "@strsql";
par[1, 1] = strsql;
dset = pComm.ExcuteSp("sp_GetMaterialBomConsumeByDrawOrder", par);
pComm.Close();
return(dset);
}
catch (Exception e)
{
pComm.Close();
throw e;
}
}
/// <summary>
/// 采购入库单是否已开票报表(已勾结，未勾结)
/// </summary>
/// <param name="strsql"></param>
/// <returns></returns>
public DataTable sp_GetStockInOrderStatus_Report(string StockInOrderID, string strbegindate, string strenddate, string SupplierGuid, string IsInvoice)
{
CommonInterface pComm = CommonFactory.CreateInstance(CommonData.sql);
try
{
DataSet dset = new DataSet();
string[,] par;
par = new string[5, 2];
par[0, 0] = "@StockInOrderID";
par[0, 1] = StockInOrderID;
par[1, 0] = "@BeginDate";
par[1, 1] = strbegindate;
par[2, 0] = "@EndDate";
par[2, 1] = strenddate;
par[3, 0] = "@SupplierGuid";
par[3, 1] = SupplierGuid;
par[4, 0] = "@IsInvoice";
par[4, 1] = IsInvoice;
dset = pComm.ExcuteSp("sp_GetStockInOrderStatus", par);
pComm.Close();
return(dset.Tables[0]);
}
catch (Exception e)
{
pComm.Close();
throw e;
}
}
//------------------------------------------------------------------------
//调用存储过程，得到某个物料的在指定仓库中的数量
public DataTable sp_GetOneStorageSumData(string StorageID, string Materialguid)
{
CommonInterface pComm = CommonFactory.CreateInstance(CommonData.sql);
try
{
DataSet dset = new DataSet();
string[,] par;
par = new string[2, 2];
par[0, 0] = "@StorageID";
par[0, 1] = StorageID;
par[1, 0] = "@MaterialGuid";
par[1, 1] = Materialguid;
dset = pComm.ExcuteSp("sp_GetOneStorageSumData", par);
pComm.Close();
return(dset.Tables[0]);
}
catch (Exception e)
{
pComm.Close();
throw e;
}
}
//修改1
//计算客户订单中产品所需要用到的生产领料用量--按BOM的层级来加载出物料
public DataTable CalcMaterialDrawPlan3(string strDrawPlanGuID, string strlevel)
{
CommonInterface pComm = CommonFactory.CreateInstance(CommonData.sql);
try
{
DataSet dset = new DataSet();
string[,] par;
par = new string[2, 2];
par[0, 0] = "@DrawPlanGuID";
par[0, 1] = strDrawPlanGuID;
par[1, 0] = "@Level";
par[1, 1] = strlevel;
dset = pComm.ExcuteSp("sp_GetMaterialDataByDrawPlanCalc3", par);
pComm.Close();
return(dset.Tables[0]);
}
catch (Exception e)
{
pComm.Close();
throw e;
}
}
//显示本人所能查看的文件
public DataTable sp_GetFileDataView(string LoginUserID, string LoginUserName, string strsql, string IsRight)
{
CommonInterface pComm = CommonFactory.CreateInstance(CommonData.sql);
try
{
DataSet dset = new DataSet();
string[,] par;
par = new string[4, 2];
par[0, 0] = "@LoginUserID";
par[0, 1] = LoginUserID;
par[1, 0] = "@LoginUserName";
par[1, 1] = LoginUserName;
par[2, 0] = "@strWhereSQL";
par[2, 1] = strsql;
par[3, 0] = "@IsRight";
par[3, 1] = IsRight;
dset = pComm.ExcuteSp("sp_GetFileDataView", par);
pComm.Close();
return(dset.Tables[0]);
}
catch (Exception e)
{
pComm.Close();
throw e;
}
}
//计算客户订单中产品所需要用到的物料需求用量
public void CalcMaterialBomPlan2(string strMaterialMRPPlanGuID)
{
CommonInterface pComm = CommonFactory.CreateInstance(CommonData.sql);
try
{
DataSet dset = new DataSet();
string[,] par;
par = new string[1, 2];
par[0, 0] = "@MaterialMRPPlanGuID";
par[0, 1] = strMaterialMRPPlanGuID;
pComm.ExcuteSp("sp_GetMaterialDataByBomCalc2", par);
pComm.Close();
}
catch (Exception e)
{
pComm.Close();
throw e;
}
}
//系统数据库备份-调用存储过程
public void sp_BackupDatabase(string strPath, string strFileName)
{
CommonInterface pComm = CommonFactory.CreateInstance(CommonData.sql);
try
{
string[,] par;
par = new string[2, 2];
par[0, 0] = "@strBackupPath";
par[0, 1] = strPath;
par[1, 0] = "@strFileName";
par[1, 1] = strFileName;
pComm.ExcuteSp("sp_BackupDatabase", par);
pComm.Close();
}
catch (Exception e)
{
pComm.Close();
throw e;
}
}
/// <summary>
/// 得到某个料件在所有仓库中的库存数据
/// </summary>
/// <returns></returns>
public DataTable sp_GetMaterialSumByDepot(string MaterialGuID)
{
CommonInterface pComm = CommonFactory.CreateInstance(CommonData.sql);
try
{
DataSet dset = new DataSet();
string[,] par;
par = new string[1, 2];
par[0, 0] = "@MaterialGuID";
par[0, 1] = MaterialGuID;
dset = pComm.ExcuteSp("sp_GetMaterialSumByDepot", par);
pComm.Close();
return(dset.Tables[0]);
}
catch (Exception e)
{
pComm.Close();
throw e;
}
}
/// <summary>
/// 将子类别全部置为一级类别名称
/// </summary>
/// <param name="FileType"></param>
/// <returns></returns>
public void GetSubFileType2(string FileType)
{
CommonInterface pComm = CommonFactory.CreateInstance(CommonData.sql);
try
{
//DataSet dset = new DataSet();
string[,] par;
par = new string[1, 2];
par[0, 0] = "@FileType";
par[0, 1] = FileType;
pComm.ExcuteSp("sp_GetSubFileType2", par);
pComm.Close();
// return dset.Tables[0];
}
catch (Exception e)
{
pComm.Close();
throw e;
}
}
//生成一个产品下面所有的子件
public DataSet sp_GetMaterialBomConsume(String Materialfatherguid)
{
CommonInterface pComm = CommonFactory.CreateInstance(CommonData.sql);
try
{
DataSet dset = new DataSet();
string[,] par;
par = new string[1, 2];
par[0, 0] = "@MaterialFatherGuid";
par[0, 1] = Materialfatherguid;
dset = pComm.ExcuteSp("sp_GetMaterialBomConsume", par);
pComm.Close();
return(dset);
}
catch (Exception e)
{
pComm.Close();
throw e;
}
}
//调用存储过程，得到某个物料BOM子件，以Table形式加载
public DataTable sp_GetBomDataByMaterialGuid(String Materialguid)
{
CommonInterface pComm = CommonFactory.CreateInstance(CommonData.sql);
try
{
DataSet dset = new DataSet();
string[,] par;
par = new string[1, 2];
par[0, 0] = "@MaterialFatherGuid";
par[0, 1] = Materialguid;
dset = pComm.ExcuteSp("sp_GetMaterialBomData_BomQry", par);
pComm.Close();
return(dset.Tables[0]);
}
catch (Exception e)
{
pComm.Close();
throw e;
}
}
/// <summary>
/// 报废单明细报表
/// </summary>
/// <param name="strsql"></param>
/// <returns></returns>
public DataTable sp_RejectOrderDetail_Report(string strWheresql)
{
CommonInterface pComm = CommonFactory.CreateInstance(CommonData.sql);
try
{
DataSet dset = new DataSet();
string[,] par;
par = new string[1, 2];
par[0, 0] = "@strWheresql";
par[0, 1] = strWheresql;
dset = pComm.ExcuteSp("sp_RejectOrderDetail_Report", par);
pComm.Close();
return(dset.Tables[0]);
}
catch (Exception e)
{
pComm.Close();
throw e;
}
}
//计算无BOM的物料需求计划
public DataTable sp_Calc_NoBOM(string BomPlanGuid)
{
CommonInterface pComm = CommonFactory.CreateInstance(CommonData.sql);
try
{
DataSet dset = new DataSet();
string[,] par;
par = new string[1, 2];
par[0, 0] = "@MaterialMRPPlanGuID";
par[0, 1] = BomPlanGuid;
dset = pComm.ExcuteSp("sp_Calc_NoBOM", par);
pComm.Close();
return(dset.Tables[0]);
}
catch (Exception e)
{
pComm.Close();
throw e;
}
}
/// <summary>
/// 得到数据-收发类型汇总表(包括出入库，调拨)
/// </summary>
/// <param name="strsql">查询条件仓库</param>
/// <param name="strsql2">查询条件调拨</param>
/// <param name="depot">仓库名</param>
/// <returns></returns>
public DataTable sp_GetDepotClassTypeDetailSum(string BeginDate, string EndDate, string Depot, string BarNo,
string MaterialID, string MaterialName, string Spec, string Classid)
{
CommonInterface pComm = CommonFactory.CreateInstance(CommonData.sql);
try
{
DataSet dset = new DataSet();
string[,] par;
par = new string[8, 2];
par[0, 0] = "@BeginDate";
par[0, 1] = BeginDate;
par[1, 0] = "@EndDate";
par[1, 1] = EndDate;
par[2, 0] = "@Depot";
par[2, 1] = Depot;
par[3, 0] = "@BarNo";
par[3, 1] = BarNo;
par[4, 0] = "@MaterialID";
par[4, 1] = MaterialID;
par[5, 0] = "@MaterialName";
par[5, 1] = MaterialName;
par[6, 0] = "@Spec";
par[6, 1] = Spec;
par[7, 0] = "@ClassID";
par[7, 1] = Classid;
dset = pComm.ExcuteSp("sp_GetDepotClassTypeDetailSum", par);
pComm.Close();
return(dset.Tables[0]);
}
catch (Exception e)
{
pComm.Close();
throw e;
}
}
//采购入库单号,付款单从采购入库单中选择
public DataTable sp_GetSelectStockInOrder(string PaymentID)
{
CommonInterface pComm = CommonFactory.CreateInstance(CommonData.sql);
try
{
DataSet dset = new DataSet();
string[,] par;
par = new string[1, 2];
par[0, 0] = "@PaymentID";
par[0, 1] = PaymentID;
dset = pComm.ExcuteSp("sp_GetSelectStockInOrder", par);
pComm.Close();
return(dset.Tables[0]);
}
catch (Exception e)
{
pComm.Close();
throw e;
}
}
/// <summary>
/// 库存明细报表
/// </summary>
/// <param name="MaterialGuid"></param>
/// <param name="StorageID"></param>
public DataTable StockDetailReport(string MaterialGuid, string StorageID)
{
CommonInterface pComm = CommonFactory.CreateInstance(CommonData.sql);
try
{
DataSet dset = new DataSet();
string[,] par;
par = new string[2, 2];
par[0, 0] = "@Depot";
par[0, 1] = StorageID;
par[1, 0] = "@MaterialGuID";
par[1, 1] = MaterialGuid;
dset = pComm.ExcuteSp("sp_GetStorageMaterialDetailSum", par);
pComm.Close();
return(dset.Tables[0]);
}
catch (Exception e)
{
pComm.Close();
throw e;
}
}