/// <summary>
/// 生产订单信息
/// </summary>
/// <param name="pPlanStartDate">计划开工日期</param>
/// <param name="pFormId">业务标识</param>
/// <returns></returns>
public DataTable GetMo(string pPlanStartDate, Model.Enum.FormID pFormId)
{
string strOrg;
switch (pFormId)
{
case Model.Enum.FormID.PRD_PPBOM:
strOrg = "100508";
break;
case Model.Enum.FormID.PRD_PPBOM_DX:
strOrg = "492501088";
break;
default:
strOrg = "100508";
break;
}
_SQL = string.Format("SELECT DISTINCT MTL.FNUMBER 物料编码, MTLL.FNAME 物料名称, DEP.FNUMBER 部门编码, DEPL.FNAME 部门 ");
_SQL += string.Format(" FROM T_PRD_MO A ");
_SQL += string.Format(" INNER JOIN T_PRD_MOENTRY AE ON A.FID = AE.FID ");
_SQL += string.Format(" INNER JOIN T_PRD_MOENTRY_A AA ON AE.FENTRYID = AA.FENTRYID AND AA.FSTATUS IN({0}) ", strOrg == "100508" ? "3,4" : "4");
_SQL += string.Format(" INNER JOIN T_BD_DEPARTMENT DEP ON AE.FWORKSHOPID = DEP.FDEPTID ");
_SQL += string.Format(" INNER JOIN T_BD_DEPARTMENT_L DEPL ON DEP.FDEPTID = DEPL.FDEPTID AND DEPL.FLOCALEID = 2052 ");
_SQL += string.Format(" INNER JOIN T_PRD_PPBOMENTRY PBE ON AA.FENTRYID = PBE.FMOENTRYID ");
_SQL += string.Format(" INNER JOIN T_BD_MATERIAL MTL ON PBE.FMATERIALID = MTL.FMATERIALID AND MTL.FUSEORGID = {0} ", strOrg);
_SQL += string.Format(" INNER JOIN T_BD_MATERIAL_L MTLL ON MTL.FMATERIALID = MTLL.FMATERIALID AND MTLL.FLOCALEID = 2052 ");
_SQL += string.Format(" LEFT JOIN T_AUTO_MSTOCKSETTING MST ON MTL.FMATERIALID = MST.FMATERIALID AND DEP.FDEPTID = MST.FDEPTID ");
_SQL += string.Format(" WHERE (MST.FMATERIALID IS NULL OR MST.FSTOCKID IS NULL) AND A.FDOCUMENTSTATUS = 'C' AND A.FPRDORGID = {0} AND TO_CHAR(AE.FPLANSTARTDATE,'yyyy-mm-dd') = '{1}'", strOrg, pPlanStartDate);
return(ORAHelper.ExecuteTable(_SQL));
}
/// <summary>
/// 根据日期获取入库单编号
/// </summary>
/// <param name="pDateTime">日期</param>
/// <returns>DataTable</returns>
public DataTable GetInstockBillNo(DateTime pDateTime)
{
_SQL = string.Format("SELECT DISTINCT A.FBILLNO ");
_SQL += string.Format(" FROM T_PRD_INSTOCK A ");
_SQL += string.Format(" LEFT JOIN T_PRD_PICKMTRLDATA_A PICA ON A.FID = PICA.FSRCBIZINTERID ");
_SQL += string.Format(" WHERE TO_CHAR(A.FDATE, 'yyyy-MM-dd') >= '{0}' AND A.FDOCUMENTSTATUS = 'C' AND PICA.FSRCBIZBILLNO IS NULL", pDateTime.ToString("yyyy-MM-dd"));
return(ORAHelper.ExecuteTable(_SQL));
}
/// <summary>
/// 更新单价为空的销售出库单单价
/// </summary>
/// <param name="pOrgId">组织</param>
/// <param name="pYear">年份</param>
/// <param name="pMonth">月份</param>
public void SynPrice(int pOrgId, int pYear, int pMonth)
{
string strFrom = pYear.ToString() + "-" + (pMonth > 9 ? pMonth.ToString() : "0" + pMonth.ToString()) + "-01";
string strTo = pYear.ToString() + "-" + (pMonth + 1 > 9 ? (pMonth + 1).ToString() : "0" + (pMonth + 1).ToString()) + "-01";
_SQL = string.Format("UPDATE T_SAL_OUTSTOCKENTRY_F AF SET (FPRICE,FTAXPRICE) = ");
_SQL += string.Format(" (SELECT BF.FPRICE,BF.FPRICE FROM T_SAL_OUTSTOCK A,T_SAL_OUTSTOCKENTRY AE,T_SAL_OUTSTOCKENTRY_R AR,T_SAL_ORDER B,T_SAL_ORDERENTRY BE,T_SAL_ORDERENTRY_F BF ");
_SQL += string.Format(" WHERE AF.FENTRYID = AR.FENTRYID AND AF.FENTRYID = AE.FENTRYID AND A.FID = AE.FID AND AR.FSRCBILLNO = B.FBILLNO AND B.FID = BE.FID AND AE.PRODUCTIONSEQ = BE.PRODUCTIONSEQ AND BE.FENTRYID = BF.FENTRYID AND A.FSALEORGID = {0} AND A.FDATE BETWEEN TO_DATE('{1}','YYYY-MM-DD') AND TO_DATE('{2}','YYYY-MM-DD') AND AE.PRODUCTIONSEQ != ' ') ", pOrgId, strFrom, strTo);
_SQL += string.Format(" WHERE FPRICE = 0 AND EXISTS ");
_SQL += string.Format(" (SELECT 1 FROM T_SAL_OUTSTOCK A,T_SAL_OUTSTOCKENTRY AE,T_SAL_OUTSTOCKENTRY_R AR,T_SAL_ORDER B,T_SAL_ORDERENTRY BE,T_SAL_ORDERENTRY_F BF ");
_SQL += string.Format(" WHERE AF.FENTRYID = AR.FENTRYID AND AF.FENTRYID = AE.FENTRYID AND A.FID = AE.FID AND AR.FSRCBILLNO = B.FBILLNO AND B.FID = BE.FID AND AE.PRODUCTIONSEQ = BE.PRODUCTIONSEQ AND BE.FENTRYID = BF.FENTRYID AND A.FSALEORGID = {0} AND A.FDATE BETWEEN TO_DATE('{1}','YYYY-MM-DD') AND TO_DATE('{2}','YYYY-MM-DD') AND AE.PRODUCTIONSEQ != ' ') ", pOrgId, strFrom, strTo);
ORAHelper.ExecuteNonQuery(_SQL);
}
