/// <summary>
/// 获取存货分类编码规则
/// </summary>
/// <returns></returns>
public string GetGradeDef()
{
string rule = string.Empty;
string sql = string.Format(@"select top 1 CODINGRULE from GradeDef where keyword=N'Inventoryclass'");
DataTable dt = DB_SqlHelper.ExecuteDataTable(sql);
if (dt != null && dt.Rows.Count > 0)
{
rule = dt.Rows[0]["CODINGRULE"].ToString();
}
return(rule);
}
/// <summary>
/// 获取物料表（bas_part）最大ID
/// </summary>
/// <returns></returns>
public int GetPartId()
{
int num = 0;
string sql = @"select Max(PartId) as PartId from bas_part";
DataTable dt = DB_SqlHelper.ExecuteDataTable(sql);
if (dt != null && dt.Rows.Count > 0)
{
return(num = int.Parse(dt.Rows[0]["PartId"].ToString()));
}
return(num);
}
/// <summary>
/// 获取发货单实体类数据
/// </summary>
/// <param name="cDLCode"></param>
/// <returns></returns>
public EntityDispatchListHead GetEntityDispatchListHead(string cDLCode)
{
EntityDispatchListHead entity = new EntityDispatchListHead();
List <EntityDispatchListBody> listBody = new List <EntityDispatchListBody>();
string sqlHead = "SELECT cDLCode ,dDate,cDepCode,cCusCode,cDefine1,cSTCode,cDefine14,ccusperson,cShipAddress,cDefine8,cDefine3,cMemo FROM DispatchList WHERE cDLCode=@cDLCode ";
string sqlBody = "SELECT cInvCode ,cWhCode ,iQuantity,iQuotedPrice,iSum,iTaxRate,cBatch FROM DispatchLists where DLID = (SELECT DLID FROM DispatchList WHERE cDLCode = @cDLCode )";
SqlParameter[] paras =
{
new SqlParameter("@cDLCode", cDLCode)
};
SqlDataReader drHead = DB_SqlHelper.ExecuteReader(CommandType.Text, sqlHead, paras);
SqlDataReader drBody = DB_SqlHelper.ExecuteReader(CommandType.Text, sqlBody, paras);
while (drBody.Read())
{
EntityDispatchListBody body = new EntityDispatchListBody();
body.cInvCode = drBody["cInvCode"].ToString();
body.cWhCode = drBody["cWhCode"].ToString();
body.iQuantity = ConvertToDecimal(drBody["iQuantity"]);
body.iQuotedPrice = ConvertToDecimal(drBody["iQuotedPrice"]);
body.iSum = ConvertToDecimal(drBody["iSum"]);
body.iTaxRate = ConvertToDecimal(drBody["iTaxRate"]);
body.cBatch = drBody["cBatch"].ToString();
listBody.Add(body);
}
if (drHead.Read())
{
entity.cDLCode = cDLCode;
entity.dDate = Convert.ToDateTime(drHead["dDate"]);
entity.cDepCode = drHead["cDepCode"].ToString();
entity.cCusCode = drHead["cCusCode"].ToString();
entity.cDefine1 = drHead["cDefine1"].ToString();
entity.cSTCode = drHead["cSTCode"].ToString();
entity.cDefine14 = drHead["cDefine14"].ToString();
entity.ccusperson = drHead["ccusperson"].ToString();
entity.cShipAddress = drHead["cShipAddress"].ToString();
entity.cDefine8 = drHead["cDefine8"].ToString();
entity.cDefine3 = drHead["cDefine3"].ToString();
entity.cMemo = drHead["cMemo"].ToString();
entity.Details = listBody;
}
return(entity);
}
/// <summary>
/// 获取其他出库单实体数据
/// </summary>
/// <param name="cCode"></param>
/// <returns></returns>
public EntityRdRecord09Head GetEntityRdRecord09Head(string cCode)
{
EntityRdRecord09Head entity = new EntityRdRecord09Head();
List <EntityRdRecord09Body> listBody = new List <EntityRdRecord09Body>();
string sqlHead = "SELECT cCode,dDate,cWhCode,cRdCode,cDepCode,cDefine1,cCusCode,cDefine8,cDefine10,cShipAddress,cMemo,cMaker FROM RdRecord09 WHERE cCode = @cCode ";
string sqlBody = "SELECT cInvCode,iQuantity,cDefine26,cDefine27,cDefine22,cDefine23,cBatch FROM RdRecords09 WHERE ID = (SELECT ID FROM RdRecord09 WHERE cCode = @cCode )";
SqlParameter[] paras =
{
new SqlParameter("@cCode", cCode)
};
SqlDataReader drHead = DB_SqlHelper.ExecuteReader(CommandType.Text, sqlHead, paras);
SqlDataReader drBody = DB_SqlHelper.ExecuteReader(CommandType.Text, sqlBody, paras);
while (drBody.Read())
{
EntityRdRecord09Body body = new EntityRdRecord09Body();
body.cInvCode = drBody["cInvCode"].ToString();
body.iQuantity = Convert.ToDecimal(drBody["iQuantity"] ?? 0);
body.cDefine26 = Convert.ToDecimal(drBody["cDefine26"] ?? 0);
body.cDefine27 = Convert.ToDecimal(drBody["cDefine27"] ?? 0);
body.cDefine22 = drBody["cDefine22"].ToString();
body.cDefine23 = drBody["cDefine23"].ToString();
body.cBatch = drBody["cBatch"].ToString();
listBody.Add(body);
}
if (drHead.Read())
{
entity.cCode = cCode;
entity.dDate = Convert.ToDateTime(drHead["dDate"]);
entity.cWhCode = drHead["cWhCode"].ToString();
entity.cRdCode = drHead["cRdCode"].ToString();
entity.cDepCode = drHead["cDepCode"].ToString();
entity.cDefine1 = drHead["cDefine1"].ToString();
entity.cCusCode = drHead["cCusCode"].ToString();
entity.cDefine8 = drHead["cDefine8"].ToString();
entity.cDefine10 = drHead["cDefine10"].ToString();
entity.cShipAddress = drHead["cShipAddress"].ToString();
entity.cMaker = drHead["cMaker"].ToString();
entity.cMemo = drHead["cMemo"].ToString();
entity.Details = listBody;
}
return(entity);
}
public EntityComputationUnit GetComputationUnit(string cComUnitName)
{
EntityComputationUnit entity = new EntityComputationUnit();
string sql = @"select cComUnitCode,cGroupCode from ComputationUnit where cComUnitName=@cComUnitName";
SqlParameter[] para =
{
new SqlParameter("@cComUnitName", cComUnitName),
};
DataTable dt = DB_SqlHelper.ExecuteDataTable(sql, para);
if (dt != null && dt.Rows.Count > 0)
{
entity.cComUnitCode = dt.Rows[0]["cComUnitCode"].ToString();
entity.cGroupCode = dt.Rows[0]["cGroupCode"].ToString();
}
return(entity);
}
/// <summary>
/// 库存量查询
/// </summary>
/// <param name="cInvCode"></param>
/// <param name="cWhCode"></param>
/// <returns></returns>
public List <EntityCurrentStock> GetCurrentStock(EntityCurrentStock entity, out string msg)
{
msg = "";
List <EntityCurrentStock> list = new List <EntityCurrentStock>();
if (string.IsNullOrEmpty(entity.cInvCode) && string.IsNullOrEmpty(entity.cWhCode))
{
msg = "参数传递有误!存货编号和仓库编号不能同时为空";
return(list);
}
string sql = @"select I.cInvCode,cWhCode,cBatch,(CASE WHEN bInvBatch=1 THEN CASE WHEN bStopFlag =1 OR bGSPStop= 1 THEN 0 ELSE
ISNULL(iQuantity,0)- IsNull(fStopQuantity,0) END + ISNULL(fInQuantity,0) - ISNULL(fOutQuantity,0) ELSE
CASE WHEN bStopFlag =1 OR bGSPStop= 1 THEN 0 ELSE ISNULL(iQuantity,0)- IsNull(fStopQuantity,0) END +
ISNULL(fInQuantity,0) - ISNULL(fOutQuantity,0) END) AS iQuantity
from v_ST_currentstockForReport CS inner join dbo.Inventory I ON I.cInvCode = CS.cInvCode where 1= 1";
if (!string.IsNullOrWhiteSpace(entity.cInvCode))
{
//sql += string.Format(" and cInvCode = '{0}'", entity.cInvCode);
sql += string.Format(" and I.cInvCode in ('{0}')", entity.cInvCode.Replace(",", "','"));
}
if (!string.IsNullOrWhiteSpace(entity.cWhCode))
{
//sql += string.Format(" and cWhCode = '{0}'" ,entity.cWhCode);
sql += string.Format(" and cWhCode in ('{0}')", entity.cWhCode.Replace(",", "','"));
}
SqlDataReader datareader = DB_SqlHelper.ExecuteReader(CommandType.Text, sql, null);
while (datareader.Read())
{
EntityCurrentStock ecs = new EntityCurrentStock();
ecs.cInvCode = datareader["cInvCode"].ToString();
ecs.cWhCode = datareader["cWhCode"].ToString();
ecs.cBatch = datareader["cBatch"].ToString();
ecs.iQuantity = Convert.ToInt32(datareader["iQuantity"]);
list.Add(ecs);
}
return(list);
}
/// <summary>
/// 获取采购入库单实体数据
/// </summary>
/// <param name="cCode"></param>
/// <returns></returns>
public EntityRdRecord01Head GetEntityRdRecord01Head(string cCode)
{
EntityRdRecord01Head entity = new EntityRdRecord01Head();
List <Entityrdrecords01Body> listBody = new List <Entityrdrecords01Body>();
string sqlHead = "SELECT cCode ,dDate,cVenCode,cPTCode,cWhCode,cRdCode,cOrderCode,cMaker,cMemo FROM RdRecord01 WHERE cCode = @cCode ";
string sqlBody = "SELECT cInvCode,iQuantity,iOriTaxCost,ioriSum,iTaxRate,cBatch FROM RdRecords01 WHERE ID = (SELECT ID FROM RdRecord01 WHERE cCode = @cCode )";
SqlParameter[] paras =
{
new SqlParameter("@cCode", cCode)
};
SqlDataReader drHead = DB_SqlHelper.ExecuteReader(CommandType.Text, sqlHead, paras);
SqlDataReader drBody = DB_SqlHelper.ExecuteReader(CommandType.Text, sqlBody, paras);
while (drBody.Read())
{
Entityrdrecords01Body body = new Entityrdrecords01Body();
body.cInvCode = drBody["cInvCode"].ToString();
body.iQuantity = Convert.ToDecimal(drBody["iQuantity"] ?? 0);
body.iOriTaxCost = Convert.ToDecimal(drBody["iOriTaxCost"] ?? 0);
body.ioriSum = Convert.ToDecimal(drBody["ioriSum"] ?? 0);
body.iTaxRate = Convert.ToDecimal(drBody["iTaxRate"] ?? 0);
body.cBatch = drBody["cBatch"].ToString();
listBody.Add(body);
}
if (drHead.Read())
{
entity.cCode = cCode;
entity.dDate = Convert.ToDateTime(drHead["dDate"]);
entity.cVenCode = drHead["cVenCode"].ToString();
entity.cPTCode = drHead["cPTCode"].ToString();
entity.cWhCode = drHead["cWhCode"].ToString();
entity.cRdCode = drHead["cRdCode"].ToString();
entity.cOrderCode = drHead["cOrderCode"].ToString();
entity.cMaker = drHead["cMaker"].ToString();
entity.cMemo = drHead["cMemo"].ToString();
entity.Details = listBody;
}
return(entity);
}
/// <summary>
/// 查询退货分析报表
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public List <EntityReturnAnalysisReport> GetReturnAnalysis(EntityReturnAnalysisRQC entity)
{
List <EntityReturnAnalysisReport> list = new List <EntityReturnAnalysisReport>();
//string sql = string.Format(@"exec prc_report_ReturnAnalysis '{0}','{0}','{0}','{0}','{0}','{0}','{0}','{0}','{0}','{0}','{0}','{0}','{0}','{0}','{0}','{0}','{0}','{0}'",
// entity.DateBeg, entity.DateEed, entity.Depart1, entity.Depart2, entity.cCusCode1, entity.cCusCode2, entity.cCusDefine11, entity.cCusDefine12, entity.ICategory1, entity.ICategory2,
// entity.InvCode1, entity.InvCode2, entity.SumQuantity1, entity.SumQuantity2, entity.SumMoney1, entity.SumMoney2, entity.ReProportion1, entity.ReProportion2);
//return this.DB_SqlHelper.ExecuteDataTable(CommandType.Text, sql);
string SQL_StoredProcedure = "prc_report_ReturnAnalysis";
SqlParameter[] sp_para = new SqlParameter[4];
sp_para[0] = new SqlParameter("@datebeg", entity.DateBeg);
sp_para[1] = new SqlParameter("@dateend", entity.DateEed);
//sp_para[2] = new SqlParameter("@Depart1", entity.Depart1);
//sp_para[3] = new SqlParameter("@Depart2", entity.Depart2);
sp_para[2] = new SqlParameter("@cCusCode", entity.cCusCode);
//sp_para[5] = new SqlParameter("@cCusCode2", entity.cCusCode2);
//sp_para[6] = new SqlParameter("@cCusDefine11", entity.cCusDefine11);
//sp_para[7] = new SqlParameter("@cCusDefine12", entity.cCusDefine12);
//sp_para[8] = new SqlParameter("@ICategory1", entity.ICategory1);
//sp_para[9] = new SqlParameter("@ICategory2", entity.ICategory2);
sp_para[3] = new SqlParameter("@InvCode", entity.InvCode);
//sp_para[11] = new SqlParameter("@InvCode2", entity.InvCode2);
//sp_para[12] = new SqlParameter("@SumQuantity1", entity.SumQuantity1);
// sp_para[13] = new SqlParameter("@SumQuantity2", entity.SumQuantity2);
//sp_para[14] = new SqlParameter("@SumMoney1", entity.SumMoney1);
//sp_para[15] = new SqlParameter("@SumMoney2", entity.SumMoney2);
//sp_para[16] = new SqlParameter("@ReProportion1", entity.ReProportion1);
//sp_para[17] = new SqlParameter("@ReProportion2", entity.ReProportion2);
DataTable dt = new DataTable();
dt = DB_SqlHelper.ExecuteDataTable(CommandType.StoredProcedure, SQL_StoredProcedure, sp_para);
for (int i = 0; i < dt.Rows.Count; i++)
{
EntityReturnAnalysisReport data = new EntityReturnAnalysisReport();
DataRow dr = dt.Rows[i];
data.cDepCode = dr["cDepCode"].ToString();
data.cDepName = dr["cDepName"].ToString();
data.cCusCode = dr["cCusCode"].ToString();
data.cCusName = dr["cCusName"].ToString();
data.cCusAbbName = dr["cCusAbbName"].ToString();
data.cCusDefine1 = dr["cCusDefine1"].ToString();
data.cInvCode = dr["cInvCode"].ToString();
data.cInvName = dr["cInvName"].ToString();
data.cInvStd = dr["cInvStd"].ToString();
data.cInvCCode = dr["cInvCCode"].ToString();
data.cInvCName = dr["cInvCName"].ToString();
data.cComUnitCode = dr["cComUnitCode"].ToString();
data.cComUnitName = dr["cComUnitName"].ToString();
data.thIQuantity = Convert.ToDecimal(dr["thIQuantity"].ToString());
data.thINatSum = Convert.ToDecimal(dr["thINatSum"].ToString());
data.fhIQuantity = Convert.ToDecimal(dr["fhIQuantity"].ToString());
data.fhINatSum = Convert.ToDecimal(dr["fhINatSum"].ToString());
data.actualQty = Convert.ToDecimal(dr["actualQty"].ToString());
data.actualSum = Convert.ToDecimal(dr["actualSum"].ToString());
//data.percentSum = dr["percentSum"].ToString();
list.Add(data);
}
return(list);
}
