/// <summary>
/// 用户登录
/// </summary>
/// <param name="user"></param>
/// <param name="info"></param>
/// <returns></returns>
public bool Login(User user, DBInfo info)
{
if (user == null)
{
return(false);
}
user.ConnectionString = string.Format(@"user id={0};password={1};data source={2};persist security info=True;initial catalog=UFDATA_{3}_{4};Connection Timeout=30", info.SqlUser, info.SqlPassword, info.DBServer, user.AccID, user.Year);
bool flag = false;
try
{
UFSoft.U8.Framework.Login.UI.clsLogin netLogin = new UFSoft.U8.Framework.Login.UI.clsLogin();
string strSql = string.Format("select top 1 cUser_Name from ufsystem..ua_user where cUser_id=N'{0}' and cPassword='******'", user.UserID, netLogin.EnPassWord(user.Password));
//u8Login = new clsLogin();
//isLogin = flag = u8Login.Login(ref sSubId, ref sAccID, ref sYear, ref sUserID, ref sPassword, ref sDate, ref sServer, ref sSerial);
DataTable dt = DBHelperSQL.QueryTable(user.ConnectionString, strSql);
if (dt != null && dt.Rows.Count > 0)
{
user.UserName = dt.Rows[0][0].ToString();
flag = true;
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(flag);
}
/// <summary>
/// 根据盘点单、存货编码、批次查询盘点信息
/// </summary>
/// <param name="connectionString"></param>
/// <param name="checkVouchs"></param>
/// <param name="?"></param>
/// <returns></returns>
/// <remarks>tianzhenyun 2013-07-03</remarks>
public bool SelectCheckVouch(string connectionString, ref CheckVouchs checkVouchs, out string errMsg)
{
bool flag = false;
errMsg = string.Empty;
string strSql = string.Format(@"SELECT cv.* ,i.cInvName,i.cInvStd FROM
(SELECT cCVCode,cInvCode,cCVBatch,iCVQuantity,iCVCQuantity,iMassDate,CASE cMassUnit WHEN 3 THEN '天' WHEN 2 THEN '月' WHEN 1 THEN '年' ELSE '' END AS cMassUnit,dMadeDate,dDisDate FROM dbo.CheckVouchs WHERE cCVCode='{0}' AND cInvCode='{1}' AND cCVBatch='{2}') cv
INNER JOIN dbo.Inventory i ON cv.cInvCode = i.cInvCode", checkVouchs.cCVCode, checkVouchs.cInvCode, checkVouchs.cCVBatch);
DataTable dt = DBHelperSQL.QueryTable(connectionString, strSql);
if (dt == null || dt.Rows.Count == 0)
{
errMsg = "该存货不在此盘点单中！";
return(flag);
}
DataRow row = dt.Rows[0];
checkVouchs.cInvName = Cast.ToString(row["cInvName"]);
checkVouchs.cInvStd = Cast.ToString(row["cInvStd"]);
checkVouchs.iMassDate = Cast.ToInteger(row["iMassDate"]);
checkVouchs.cMassUnit = Cast.ToString(row["cMassUnit"]);
checkVouchs.dDisDate = Cast.ToDateTime(row["dDisDate"]);
checkVouchs.dMadeDate = Cast.ToDateTime(row["dMadeDate"]);
checkVouchs.iCVQuantity = Cast.ToDouble(row["iCVQuantity"]);
flag = true;
return(flag);
}
/// <summary>
/// 查询所有账套信息
/// </summary>
/// <returns></returns>
/// <remarks>tianzhenyun 2013-06-09</remarks>
public static DataTable GetUAAccountInfo(string connectionString, string user_Id)
{
string strSql = string.Empty;
if (user_Id == null)
{
strSql = string.Format(@"select cAcc_ID as code,cAcc_name as name ,cUnitAbbre as Abbre,'' as industrytype from ufsystem..ua_account with (nolock)");
}
else
{
strSql = string.Format(@"
select cAcc_ID as code,cAcc_name as name ,cUnitAbbre as Abbre,'' as industrytype
from ufsystem..ua_account with (nolock)
where cAcc_id IN
(
--查询用户名为‘gq’的账套号
select cacc_id from ufsystem..ua_holdauth with (nolock) where cuser_id=N'{0}' and iisuser=1 group by cacc_id
Union All
--查询用户名为‘gq’所在组的账套号
select cacc_id from ufsystem..ua_holdauth with (nolock) where cUser_id in(select distinct cgroup_id from ufsystem..ua_role with (nolock) where cUser_id=N'{0}' ) and iIsuser=0 group by cacc_id
)
order by cacc_id", user_Id);
}
return(DBHelperSQL.QueryTable(connectionString, strSql));
}
/// <summary>
/// 食品追溯
/// </summary>
/// <param name="connectionString"></param>
/// <param name="cInvCode"></param>
/// <param name="cBatch"></param>
/// <param name="Number"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
/// <remarks>tianzhenyun 2013-07-01</remarks>
public DataTable Trace(string connectionString, string cInvCode, string cBatch, string Number, out string errMsg)
{
errMsg = string.Empty;
DataTable dt = null;
string strSql = string.Format(@"SELECT inventory.cInvName,inventory.cInvStd,arrChild.cBatch,arrChild.dMDate,arrChild.iAQuantity,cu.cComunitName,arrMain.cVenCode, vendor.cVenName, vendor.cVenAbbName,inventory.iMassDate,CASE inventory.cMassUnit WHEN 3 THEN '天' WHEN 2 THEN '月' WHEN 1 THEN '年' ELSE '' END AS cMassUnit,RdRecord.cMaker,RdRecord.dDate,RdRecord.cCode,RdRecord.cBusCode, RdRecord.iarriveid,customer.cCusAbbName,RdRecords.iSQuantity FROM
--查询采购到货信息（cInvCode,cBatch,dMdate,iQuantity）
(SELECT ID,cInvCode,cBatch,dPDate AS dMDate,iQuantity as iAQuantity FROM dbo.PU_ArrivalVouchs WHERE cInvCode ='{0}' AND cBatch='{1}') arrChild
INNER JOIN (SELECT ID,cVenCode FROM dbo.PU_ArrivalVouch) arrMain ON arrChild.ID = arrMain.ID
INNER JOIN (SELECT cVenCode,cVenName,cVenAbbName FROM dbo.Vendor ) vendor ON arrMain.cVenCode = vendor.cVenCode
--查询存货信息(cInvName,cInvStd,iMassDate,cMassUnit)
INNER JOIN (SELECT cInvCode,cInvName,cInvStd,cComunitCode,iMassDate,cMassUnit FROM dbo.Inventory) inventory ON arrChild.cInvCode = inventory.cInvCode
INNER JOIN(SELECT cComunitCode,cComUnitName FROM ComputationUnit) cu ON inventory.cComUnitCode = cu.cComunitCode
--查询销售信息(cCusAbbName,cMaker,dDate,iQuantity,cBusCode,cCode,iarriveid)
LEFT JOIN (SELECT TOP 1 RDID,RDSID,cInvCode,cBatch FROM UFSystem..RdRecordSN WHERE cInvCode ='{0}' AND cBatch='{1}' AND Number={2} ORDER BY ID DESC) sn ON arrChild.cInvCode = sn.cInvCode
LEFT JOIN (SELECT ID,cCusCode,cMaker,dDate,cCode,cBusCode,iarriveid FROM dbo.RdRecord) RdRecord ON sn.RDID = RdRecord.ID
LEFT JOIN (SELECT AutoID,iQuantity as iSQuantity FROM dbo.RdRecords) RdRecords ON sn.RDSID = RdRecords.AutoID
LEFT JOIN (SELECT cCusCode,cCusAbbName FROM dbo.Customer ) customer ON RdRecord.cCusCode = customer.cCusCode", cInvCode, cBatch, Model.Cast.ToInteger(Number));
try
{
dt = DBHelperSQL.QueryTable(connectionString, strSql);
}
catch (Exception ex)
{
errMsg = ex.Message;
}
return(dt);
}
/// <summary>
/// 根据采购到货单查询存货编码
/// </summary>
/// <param name="connectionString"></param>
/// <param name="cCode"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
/// <remarks>tianzhenyun 2013-06-18</remarks>
public DataTable SearchInventoryByPUArrival(string connectionString, string cCode, out string errMsg)
{
errMsg = string.Empty;
DataTable dt = null;
string strSql = string.Format(@" select pu_ArrHead.id,pu_ArrBody.autoid,dbo.pu_arrbody.cbatch,dbo.pu_arrbody.dpdate as dMDate,dbo.pu_arrbody.iquantity,inventory.*
FROM pu_ArrBody
inner join pu_ArrHead on pu_ArrHead.id=pu_ArrBody.id
inner join (SELECT cInvCode,cInvName,cInvStd,CASE cMassUnit WHEN 3 THEN '天' WHEN 2 THEN '月' WHEN 1 THEN '年' ELSE '' END AS cMassUnit,iMassDate FROM Inventory) inventory on pu_arrbody.cinvcode=inventory.cinvcode
Where CCODE = N'{0}'
and isnull(cbustype,'')<>'委外加工'
And isnull(cbcloser,N'')=N''
And isnull(cverifier,'')<>''
And iBillType =N'0'
and isnull(bGsp,N'')=N'0'
and (isnull(iQuantity,0)-isnull(fRefuseQuantity,0)>isnull(fValidInQuan,0) or (igrouptype=2 and isnull(inum,0)-isnull(frefusenum,0)>isnull(fvalidinnum,0)))", cCode);
try
{
dt = DBHelperSQL.QueryTable(connectionString, strSql);
}
catch (Exception ex)
{
errMsg = ex.Message;
}
return(dt);
}
/// <summary>
/// 查询销售发货单列表
/// </summary>
/// <param name="connectionString"></param>
/// <param name="whList">仓库列表</param>
/// <param name="errMsg"></param>
/// <returns></returns>
/// <remarks>tianzhenyun 2013-06-21</remarks>
public DataTable SelectSaleDeliveryList(string connectionString, List <Warehouse> whList, out string errMsg)
{
errMsg = string.Empty;
DataTable dt = null;
string condition = string.Empty;//条件
//判断仓库列表是否为空
if (whList == null || whList.Count == 0)
{
errMsg = "没有仓库权限";
return(dt);
}
//封装仓库条件
condition = string.Format(" AND cWhCode IN(");
foreach (Warehouse wh in whList)
{
condition += string.Format("'{0}',", wh.cWhCode);
}
condition = condition.Substring(0, condition.Length - 1);
condition += ")";
string strSql = string.Format(@"SELECT DISTINCT DispatchList.DLID, DispatchList.cDLCode,DispatchList.cSBVCode,DispatchList.dDate,customer.cCusAbbName
from DispatchList inner join DispatchLists ON DispatchList.DLID = DispatchLists.DLID
INNER JOIN Inventory ON DispatchLists.cInvCode = Inventory.cInvCode LEFT OUTER JOIN Customer ON DispatchList.cCusCode = Customer.cCusCode
WHERE
(DispatchList.cVouchType='05' OR DispatchList.cVouchType='06')
AND((ISNULL(DispatchList.cSaleOut,'') = '' OR isnull(bqaneedcheck,0)=1) OR DispatchList.cSaleOut ='ST')
AND not(isnull(DispatchList.bFirst,0) =1 And isnull(DispatchLists.bIsStQC,0) =0)
and (
(ABS(ISNULL((case when (isnull(DispatchLists.bQANeedCheck,0)=1 and DispatchLists.iquantity>0) then DispatchLists.iqaquantity else DispatchLists.iquantity end ),0))-ABS(ISNULL(DispatchLists.fOutQuantity,0)) ) >=0.01
or ( igrouptype=2 and ( ABS(ISNULL((case when (isnull(DispatchLists.bQANeedCheck,0)=1 and DispatchLists.iquantity>0) then DispatchLists.iqanum else DispatchLists.inum end ),0))-ABS(ISNULL(DispatchLists.fOutnum,0)) ) >=0.01))
AND (ISNULL(DispatchLists.cWhCode,'')<>'')
AND bInvType=0
AND bService=0
AND ( (ISNULL(DispatchLists.bSettleAll,0)=0 and DispatchList.cvouchtype='05') or DispatchList.cvouchtype='06')
and ISNULL(DispatchList.cVerifier,'')<>''
AND (
(case when isnull(DispatchLists.bQANeedCheck,0)=1 and isnull(DispatchLists.iquantity,0)>0 then DispatchLists.iqaquantity else DispatchLists.iquantity end) <>0
OR (case when isnull(DispatchLists.bQANeedCheck,0)=1 and isnull(DispatchLists.inum,0)>0 then DispatchLists.iqanum else DispatchLists.inum end) <>0
)
{0}
ORDER BY dbo.DispatchList.DLID DESC", condition);
try
{
dt = DBHelperSQL.QueryTable(connectionString, strSql);
}
catch (Exception ex)
{
errMsg = ex.Message;
}
return(dt);
}
/// <summary>
/// 根据存货名称或编码查询存货信息
/// </summary>
/// <param name="cInvCode"></param>
/// <param name="whList">仓库列表</param>
/// <param name="cInvName"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
public DataTable SearchInventory(string connectionString, List <Warehouse> whList, string cInvCode, string cInvName, out string errMsg)
{
errMsg = string.Empty;
DataTable dt = null;
string condition = string.Empty;//条件
//判断仓库列表是否为空
if (whList == null || whList.Count == 0)
{
errMsg = "没有仓库权限";
return(dt);
}
//封装仓库条件
condition = string.Format(" AND cWhCode IN(");
foreach (Warehouse wh in whList)
{
condition += string.Format("'{0}',", wh.cWhCode);
}
condition = condition.Substring(0, condition.Length - 1);
condition += ")";
//string strSql = "SELECT cInvCode,cInvName,cInvStd,cMassUnit,iMassDate FROM dbo.Inventory WHERE 1=1";
string strSql = string.Format(@"SELECT CS.cInvCode,Inventory.cInvName ,Inventory.cInvStd,CS.iQuantity ,CS.cBatch,CS.dvdate,CS.dmdate,CS.imassdate,CASE Inventory.cMassUnit WHEN 3 THEN '天' WHEN 2 THEN '月' WHEN 1 THEN '年' ELSE '' END AS cMassUnit
FROM
(SELECT * FROM CurrentStock WHERE 1=1 {0})CS
INNER JOIN Inventory ON CS.cInvCode = Inventory.cInvCode
WHERE iQuantity>0 ", condition);
//判断编码或者名称是否为空
if (!string.IsNullOrEmpty(cInvCode))
{
strSql += string.Format(" AND Inventory.cInvCode LIKE '%{0}%'", cInvCode);
}
if (!string.IsNullOrEmpty(cInvName))
{
strSql += string.Format("AND Inventory.cInvName LIKE '%{0}%'", cInvName);
}
try
{
dt = DBHelperSQL.QueryTable(connectionString, strSql);
}
catch (Exception ex)
{
errMsg = ex.Message;
}
return(dt);
}
/// <summary>
/// 根据销售发货单查询存货编码
/// </summary>
/// <param name="connectionString"></param>
/// <param name="cDLCode"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
/// <remarks>tianzhenyun 2013-06-19</remarks>
public DataTable SearchInventoryBySaleDelivery(string connectionString, string cDLCode, out string errMsg)
{
errMsg = string.Empty;
DataTable dt = null;
string strSql = string.Format(@" SELECT dbo.DispatchLists.cBatch,dbo.DispatchLists.dMDate,dbo.DispatchLists.iQuantity,Inventory.*
from DispatchList
inner join DispatchLists ON DispatchList.DLID = DispatchLists.DLID
INNER JOIN (SELECT cInvCode,cInvName,cInvStd,CASE cMassUnit WHEN 3 THEN '天' WHEN 2 THEN '月' WHEN 1 THEN '年' ELSE '' END AS cMassUnit,iMassDate,bInvType,bService,cComUnitCode,iGroupType FROM dbo.Inventory) Inventory ON DispatchLists.cInvCode = Inventory.cInvCode
left join ComputationUnit on Inventory.ccomunitcode=ComputationUnit.ccomunitcode
inner join Warehouse on DispatchLists.cwhcode=warehouse.cwhcode
LEFT OUTER JOIN SaleType ON DispatchList.cSTCode = SaleType.cSTCode
LEFT OUTER JOIN (select cpersoncode as cpersoncode2,cpersonname from Person) person ON DispatchList.cPersonCode = Person.cPersonCode2
LEFT OUTER JOIN Customer ON DispatchList.cCusCode = Customer.cCusCode
LEFT OUTER JOIN Department ON DispatchList.cDepCode = Department.cDepCode
left join SaleBillVouch on SaleBillVouch.SBVID = dbo.DispatchList.SBVID
left join VouchType on VouchType.cVouchType = case when salebillvouch.SBVID is null then dispatchlist.cVouchType else salebillvouch.cVouchType end
WHERE (DispatchList.cVouchType='05' OR DispatchList.cVouchType='06')
AND ((ISNULL(DispatchList.cSaleOut,'') = '' OR isnull(bqaneedcheck,0)=1) OR DispatchList.cSaleOut ='ST')
AND not (isnull(DispatchList.bFirst,0) =1 And isnull(DispatchLists.bIsStQC,0) =0)
and (
( ABS(ISNULL( (case when (isnull(DispatchLists.bQANeedCheck,0)=1 and DispatchLists.iquantity>0) then DispatchLists.iqaquantity else DispatchLists.iquantity end ),0))
-ABS(ISNULL(DispatchLists.fOutQuantity,0)) ) >=0.01
or
( igrouptype=2 and ( ABS(ISNULL((case when (isnull(DispatchLists.bQANeedCheck,0)=1 and DispatchLists.iquantity>0) then DispatchLists.iqanum else DispatchLists.inum end ),0))
-ABS(ISNULL(DispatchLists.fOutnum,0)) ) >=0.01)
)
AND (ISNULL(DispatchLists.cWhCode,'')<>'')
AND bInvType=0
AND bService=0
AND ((ISNULL(DispatchLists.bSettleAll,0)=0 and DispatchList.cvouchtype='05') or DispatchList.cvouchtype='06')
and ISNULL(DispatchList.cVerifier,'')<>''
AND DISPATCHLIST.CDLCODE = N'{0}'
and (
(case when isnull(DispatchLists.bQANeedCheck,0)=1 and isnull(DispatchLists.iquantity,0)>0 then DispatchLists.iqaquantity else DispatchLists.iquantity end) <>0
OR
(case when isnull(DispatchLists.bQANeedCheck,0)=1 and isnull(DispatchLists.inum,0)>0 then DispatchLists.iqanum else DispatchLists.inum end) <>0
) " , cDLCode);
try
{
dt = DBHelperSQL.QueryTable(connectionString, strSql);
}
catch (Exception ex)
{
errMsg = ex.Message;
}
return(dt);
}
/// <summary>
/// 查询采购到货列表
/// </summary>
/// <param name="connectionString"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
/// <remarks>tianzhenyun 2013-06-21</remarks>
public DataTable SelectPurchaseList(string connectionString, List <Warehouse> whList, out string errMsg)
{
errMsg = string.Empty;
DataTable dt = null;
string condition = string.Empty;//条件
////判断仓库列表是否为空
//if (whList == null || whList.Count == 0)
//{
// errMsg = "没有仓库权限";
// return dt;
//}
////封装仓库条件
//condition = string.Format(" AND cWhCode IN(");
//foreach (Warehouse wh in whList)
//{
// condition += string.Format("'{0}',", wh.cWhCode);
//}
//condition = condition.Substring(0, condition.Length - 1);
//condition += ")";
string strSql = string.Format(@"SELECT DISTINCT pu_ArrHead.id,pu_ArrHead.ccode,pu_ArrHead.ddate,pu_ArrHead.cvenabbname
FROM pu_ArrHead
inner join pu_ArrBody on dbo.pu_ArrHead.id = dbo.pu_arrbody.id
Where
isnull(cbustype,'')<>'委外加工'
And isnull(cbcloser,N'')=N''
And isnull(cverifier,'')<>''
And iBillType =N'0'
and isnull(bGsp,N'')=N'0'
and (
isnull(iQuantity,0)-isnull(fRefuseQuantity,0)>isnull(fValidInQuan,0)
or (igrouptype=2 and isnull(inum,0)-isnull(frefusenum,0)>isnull(fvalidinnum,0))
)
ORDER BY dbo.pu_ArrHead.id DESC");
try
{
dt = DBHelperSQL.QueryTable(connectionString, strSql);
}
catch (Exception ex)
{
errMsg = ex.Message;
}
return(dt);
}
/// <summary>
/// 根据用户ID查询用户操作权限
/// </summary>
/// <param name="user"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
public DataTable Competence(User user, out string errMsg)
{
errMsg = string.Empty;
string strSql = string.Empty;
strSql = string.Format(@"
--判断是否为账套主管:一个为账套主管组，一个为功能编码admin,功能名称：账套主管
SELECT CASE WHEN COUNT(1)>0 THEN 1 ELSE 0 END FROM
(SELECT * FROM UFSystem..UA_Role WHERE cUser_Id='{0}' ) r
INNER JOIN (SELECT * FROM UFSystem..UA_HoldAuth WHERE cAuth_Id='admin' AND iYear='{1}' AND cAcc_Id='{2}') ha ON ha.cUser_Id= r.cUser_Id OR ha.cUser_Id = r.cGroup_Id", user.UserID, user.Year, user.AccID);
//如果为1，说明为账套主管
if (DBHelperSQL.ExecuteScalar(user.ConnectionString, strSql).ToString() == "1")
{
//销售出库、盘点
strSql = @"SELECT 1 AS XSCK,1 AS PD,1 AS Admin";
return(DBHelperSQL.QueryTable(user.ConnectionString, strSql));
}
//否则为普通操作人员
else
{
strSql = string.Format(@"
--普通操作人员
DECLARE
@XSCK INT,--销售出库(红字)(ASM0202:销售出库单录入,ASM0203:销售出库单审核)
@PD INT --盘点(ST010202:盘点单录入)
--销售出库(包括红字)
SELECT @XSCK = CASE WHEN COUNT(1)>0 THEN 1 ELSE 0 END FROM
(SELECT cUser_Id,cGroup_Id FROM UFSystem..UA_Role WHERE cUser_Id='{0}'
UNION ALL
SELECT cUser_Id,NULL as cGroup_Id FROM UFSystem..UA_User WHERE cUser_Id='{0}') r
INNER JOIN (SELECT cUser_Id FROM UFSystem..UA_HoldAuth WHERE cAuth_Id IN('ASM0202' ,'ASM0203') AND iYear='{1}' AND cAcc_Id='{2}' GROUP BY cUser_Id HAVING COUNT(1)=2 ) ha ON ha.cUser_Id= r.cUser_Id OR ha.cUser_Id = r.cGroup_Id
--盘点
SELECT @PD = CASE WHEN COUNT(1)>0 THEN 1 ELSE 0 END FROM
(SELECT cUser_Id,cGroup_Id FROM UFSystem..UA_Role WHERE cUser_Id='{0}'
UNION ALL
SELECT cUser_Id,NULL as cGroup_Id FROM UFSystem..UA_User WHERE cUser_Id='{0}') r
INNER JOIN (SELECT cUser_Id FROM UFSystem..UA_HoldAuth WHERE cAuth_Id='ST010202' AND iYear='{1}' AND cAcc_Id='{2}') ha ON ha.cUser_Id= r.cUser_Id OR ha.cUser_Id = r.cGroup_Id
SELECT @XSCK AS XSCK,@PD AS PD,0 AS Admin", user.UserID, user.Year, user.AccID);
return(DBHelperSQL.QueryTable(user.ConnectionString, strSql));
}
}
/// <summary>
/// 查询盘点单列表
/// </summary>
/// <param name="connectionString"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
/// <remarks>tianzhenyun 2013-07-02</remarks>
public DataTable SelectCheckVouchList(string connectionString, out string errMsg)
{
errMsg = string.Empty;
DataTable dt = null;
string strSql = @"SELECT cCVCode,cv.cWhCode,ws.cWhName,dCVDate FROM
(SELECT cCVCode,cWhCode,dCVDate FROM dbo.CheckVouch WHERE dveridate IS NULL AND dnverifytime IS NULL) cv
INNER JOIN dbo.Warehouse ws ON cv.cWhCode = ws.cWhCode";
try
{
dt = DBHelperSQL.QueryTable(connectionString, strSql);
}
catch (Exception ex)
{
errMsg = ex.Message;
}
return(dt);
}
/// <summary>
/// 查询供应商资质
/// </summary>
/// <param name="connectionString"></param>
/// <param name="cVenCode"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
public DataTable VendorQuanlification(string connectionString, string cVenCode, out string errMsg)
{
errMsg = string.Empty;
DataTable dt = null;
string strSql = string.Format(@"SELECT vls.* FROM
(SELECT id FROM V_pl_gmp_vendorlicenseaudit WHERE cVendorCode='{0}') vl
INNER JOIN (SELECT autoid,id,cLicenseCode,cLicenseName,cLicenseType,cLicenseNum,dValidDate,dEndDate FROM V_pl_gmp_vendorlicenseaudits) vls ON vl.id = vls.id
ORDER BY vls.autoid ", cVenCode);
try
{
dt = DBHelperSQL.QueryTable(connectionString, strSql);
}
catch (Exception ex)
{
errMsg = ex.Message;
}
return(dt);
}
/// <summary>
/// 登录成功后,查询该用户权限下所有的仓库
/// </summary>
/// <param name="dtWarehouse"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
public bool GetWarehouse(User user, out DataTable dtWarehouse, out string errMsg)
{
dtWarehouse = null;
errMsg = string.Empty;
try
{
///2012－10－24
///仓库权限：如果用户是账套主管或所在角色为账套主管UA_holdauth
///或者如果用户是权限主管或所有角色为权限主管AA_holdBusobject
///那么显示所有的仓库
///如果不是那就进行普通权限分配查询，用户的仓库权限或所有角色仓库权限
string strSql = string.Format(@"IF EXISTS(
--查询用户是否为账套主管
SELECT 1 FROM UFSystem.dbo.UA_HoldAuth WHERE cAuth_Id ='admin' AND cAcc_Id='{0}' AND iYear='{1}' AND cUser_Id ='{2}'
UNION ALL
--查询用户所有角色是否为账套主管
SELECT 1 FROM
(SELECT cUser_Id FROM UFSystem.dbo.UA_HoldAuth WHERE cAuth_Id ='admin' AND cAcc_Id='{0}' AND iYear='{1}') h
INNER JOIN (SELECT cGroup_Id FROM UFSystem.dbo.UA_Role WHERE cUser_Id='{2}') r ON h.cUser_Id= r.cGroup_Id
UNION ALL
--查询用户是否为仓库权限主管
SELECT 1 FROM AA_holdbusobject WHERE iAdmin =1 AND CBusObId ='warehouse' AND cUserId= '{2}'
UNION ALL
--查询用户所在角色是否仓库权限主管
SELECT 1 FROM
(SELECT cUserId FROM AA_holdbusobject WHERE iAdmin =1 AND CBusObId ='warehouse' ) h
INNER JOIN (SELECT cGroup_Id FROM UFSystem.dbo.UA_Role WHERE cUser_Id='{2}') r ON h.cUserId = r.cGroup_Id
)
--若是返回1
SELECT 1 AS flag
ELSE --否则返回0
SELECT 0 AS flag ", user.AccID, user.Year, user.UserID);
int flag = Convert.ToInt32(DBHelperSQL.ExecuteScalar(user.ConnectionString, strSql));
//如果是账套主管显示所有仓库
if (flag == 1)
{
strSql = "Select cwhname,cwhcode,bWhPos from Warehouse";
}
else
{
///根据用户名查询权限分配表 查找该用户有哪些仓库可用
///cBusObId业务对象标识 这里为'仓库'
strSql = string.Format(@"SELECT wh.* FROM
(Select cwhname,cwhcode,bWhPos from Warehouse) wh
INNER JOIN
(
--查询该用户的仓库权限
SELECT cACCode FROM aa_holdauth WHERE cBusObId='warehouse' AND cUserId='{0}'
UNION ALL
--查询该用户所在角色的仓库权限
SELECT ha.cACCode FROM
(SELECT cACCode,cUserId FROM dbo.AA_HoldAuth WHERE cBusObId ='warehouse' AND isUserGroup=1 ) ha
INNER JOIN (SELECT cGroup_Id,cUser_Id FROM UFSystem.dbo.UA_Role WHERE cUser_Id='{0}') r ON ha.cUserId = r.cGroup_Id
) temp ON wh.cwhcode =temp.cACCode", user.UserID);
}
dtWarehouse = DBHelperSQL.QueryTable(user.ConnectionString, strSql);
return(true);
}
catch (Exception ex)
{
errMsg = ex.Message;
return(false);
}
}
/// <summary>
/// 销售发货:单据装载
/// </summary>
/// <param name="connectionString"></param>
/// <param name="cDLCode"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
/// <remarks>tianzhenyun 2013-06-20</remarks>
public DispatchList Load(string connectionString, string cDLCode, out string errMsg)
{
errMsg = string.Empty;
DispatchList dispatchList = null;
//查询主表
string strSql = string.Format(@" SELECT DISTINCT DispatchList.caddcode, (CASE WHEN isnull(DispatchList.cSBVCode, '') <> '' THEN salebillvouch.cVouchType ELSE Dispatchlist.cVouchType END) AS cVouchType, VouchType.cVouchName,
DispatchList.cDLCode,DispatchList.sbvid ,DispatchList.cSBVCode,DispatchList.ddate,DispatchList.cBusType, DispatchList.DLID as DLID, DispatchList.cSTCode,
SaleType.cSTName, DispatchList.cCusCode, Customer.cCusAbbName,Customer.cCusName, DispatchList.cDepCode, Department.cDepName,DispatchList.cPersonCode ,
Person.cPersonName, DispatchList.cMaker,DispatchList.cVerifier,
DispatchList.cShipAddress,convert(char,convert(money,DispatchList.ufts),2) as ufts ,
DispatchList.cDefine1 , DispatchList.cDefine2, DispatchList.cDefine3, DispatchList.cDefine4,
DispatchList.cDefine5, DispatchList.cDefine6, DispatchList.cDefine7, DispatchList.cDefine8, DispatchList.cDefine9,
DispatchList.cDefine10, DispatchList.cDefine11, DispatchList.cDefine12, DispatchList.cDefine13, DispatchList.cDefine14,
DispatchList.cDefine15, DispatchList.cDefine16,DispatchList.cMemo,DispatchList.bReturnFlag
from DispatchList inner join DispatchLists ON DispatchList.DLID = DispatchLists.DLID
INNER JOIN Inventory ON DispatchLists.cInvCode = Inventory.cInvCode left join ComputationUnit on Inventory.ccomunitcode=ComputationUnit.ccomunitcode
inner join Warehouse on DispatchLists.cwhcode=warehouse.cwhcode
LEFT OUTER JOIN SaleType ON DispatchList.cSTCode = SaleType.cSTCode
LEFT OUTER JOIN (select cpersoncode as cpersoncode2,cpersonname from Person) person ON DispatchList.cPersonCode = Person.cPersonCode2
LEFT OUTER JOIN Customer ON DispatchList.cCusCode = Customer.cCusCode
LEFT OUTER JOIN Department ON DispatchList.cDepCode = Department.cDepCode
left join SaleBillVouch on SaleBillVouch.SBVID = dbo.DispatchList.SBVID
left join VouchType on VouchType.cVouchType = case when salebillvouch.SBVID is null then dispatchlist.cVouchType else salebillvouch.cVouchType end
WHERE (DispatchList.cVouchType='05' OR DispatchList.cVouchType='06')
AND ((ISNULL(DispatchList.cSaleOut,'') = '' OR isnull(bqaneedcheck,0)=1) OR DispatchList.cSaleOut ='ST')
AND NOT (isnull(DispatchList.bFirst,0) =1 And isnull(DispatchLists.bIsStQC,0) =0)
AND (
(ABS(ISNULL((case when (isnull(DispatchLists.bQANeedCheck,0)=1 and DispatchLists.iquantity>0) then DispatchLists.iqaquantity else DispatchLists.iquantity end ),0))-ABS(ISNULL(DispatchLists.fOutQuantity,0)) ) >=0.01
or ( igrouptype=2 and ( ABS(ISNULL((case when (isnull(DispatchLists.bQANeedCheck,0)=1 and DispatchLists.iquantity>0) then DispatchLists.iqanum else DispatchLists.inum end ),0))-ABS(ISNULL(DispatchLists.fOutnum,0)) ) >=0.01)
)
AND (ISNULL(DispatchLists.cWhCode,'')<>'')
AND bInvType=0
AND bService=0
AND ((ISNULL(DispatchLists.bSettleAll,0)=0 and DispatchList.cvouchtype='05') or DispatchList.cvouchtype='06')
AND ISNULL(DispatchList.cVerifier,'')<>''
AND DISPATCHLIST.CDLCODE = N'{0}'
AND (
(case when isnull(DispatchLists.bQANeedCheck,0)=1 and isnull(DispatchLists.iquantity,0)>0 then DispatchLists.iqaquantity else DispatchLists.iquantity end) <>0
OR (case when isnull(DispatchLists.bQANeedCheck,0)=1 and isnull(DispatchLists.inum,0)>0 then DispatchLists.iqanum else DispatchLists.inum end) <>0)" , cDLCode);
DataTable dt = null;
try
{
dt = DBHelperSQL.QueryTable(connectionString, strSql);
}
catch (Exception ex)
{
errMsg = ex.Message;
return(dispatchList);
}
if (dt.Rows.Count == 0)
{
errMsg = "单据错误或单据不存在、未审核、已关闭";
return(dispatchList);
}
//转换主表
dispatchList = EntityConvert.ConvertToDispatchList(dt.Rows[0]);
//查询子表
strSql = string.Format(@"Select WareHouse.cWhName
,DispatchList.cDLCode
,DispatchLists.AutoID,DispatchLists.cWhCode, DispatchLists.DLID, DispatchLists.iCorID,DispatchLists.cInvCode,DispatchLists.iQuotedPrice,DispatchLists.ccusinvcode,DispatchLists.ccusinvname,DispatchLists.iExpiratDateCalcu,DispatchLists.cExpirationdate,DispatchLists.dExpirationdate,DispatchLists.iUnitPrice, DispatchLists.iTaxUnitPrice,DispatchLists.iMoney, DispatchLists.iTax, DispatchLists.iDisCount,DispatchLists.iSum, DispatchLists.iNatUnitPrice,DispatchLists.iNatMoney, DispatchLists.iNatTax, DispatchLists.iNatSum,DispatchLists.iNatDisCount, DispatchLists.iSettleNum,DispatchLists.iSettleQuantity, DispatchLists.iBatch,DispatchLists.iInvExchRate,DispatchLists.cBatch, DispatchLists.bSettleAll, DispatchLists.cvmivencode,DispatchLists.cFree1, DispatchLists.cFree2, DispatchLists.iTB,DispatchLists.dMDate , DispatchLists.dvDate,DispatchLists.cMassUnit,DispatchLists.iMassDate, DispatchLists.TBQuantity, DispatchLists.TBNum,DispatchLists.iSOsID, DispatchLists.iDLsID, DispatchLists.KL,DispatchLists.iTaxRate, DispatchLists.KL2,DispatchLists.cDefine22, DispatchLists.cDefine23,DispatchLists.cDefine24, DispatchLists.cDefine25,DispatchLists.cDefine26, DispatchLists.cDefine27,convert(int,1) as isotype, DispatchLists.isosid as isodid,DispatchLists.idemandtype,DispatchLists.cdemandcode,DispatchLists.cdemandid,DispatchLists.idemandseq,DispatchLists.cItemCode, DispatchLists.cItem_class,DispatchLists.fSaleCost, DispatchLists.fSalePrice,DispatchLists.cVenAbbName, DispatchLists.cItemName,DispatchLists.cContractid,DispatchLists.ccontractTagCode,DispatchLists.ccontractrowguid,DispatchLists.cItem_CName, DispatchLists.cFree3,DispatchLists.cFree4, DispatchLists.cFree5, DispatchLists.cFree6,DispatchLists.cFree7, DispatchLists.cFree8, DispatchLists.cFree9,DispatchLists.cFree10,DispatchLists.cBatchProperty1,DispatchLists.cBatchProperty2,DispatchLists.cBatchProperty3, DispatchLists.bIsSTQc,DispatchLists.cBatchProperty4,DispatchLists.cBatchProperty5,DispatchLists.cBatchProperty6,DispatchLists.cBatchProperty7,DispatchLists.cBatchProperty8,DispatchLists.cBatchProperty9,DispatchLists.cBatchProperty10,DispatchLists.cUnitID, DispatchLists.cCode,DispatchLists.iRetQuantity, DispatchLists.fEnSettleQuan,DispatchLists.fEnSettleSum, DispatchLists.iSettlePrice,DispatchLists.cDefine28, DispatchLists.cDefine29,DispatchLists.cDefine30, DispatchLists.cDefine31,DispatchLists.cDefine32, DispatchLists.cDefine33,DispatchLists.cDefine34, DispatchLists.cDefine35,DispatchLists.cDefine36, DispatchLists.cDefine37,DispatchLists.bGsp, DispatchLists.cGspState,DispatchLists.bQANeedCheck,DispatchLists.cSoCode,DispatchLists.bCosting,DispatchLists.iorderrowno
,v1.cvenabbname as cvmivenname
,ComputationUnit.cComUnitName AS cinvm_unit,ComputationUnit_1.cComUnitName AS cinva_unit
,SaleType.cstcode,saletype.cstname, SA_SORowNo.iRowNo
,inventory.cinvaddcode,Inventory.cInvName,Inventory.cInvStd, Inventory.cInvCCode, Inventory.cVenCode, Inventory.cReplaceItem, Inventory.cPosition, Inventory.bSale,Inventory.bPurchase, Inventory.bSelf, Inventory.bComsume,Inventory.bProducing, Inventory.bService, Inventory.bAccessary,Inventory.iInvWeight, Inventory.iVolume,Inventory.iInvRCost, Inventory.iInvSPrice, Inventory.iInvSCost,Inventory.iInvLSCost, Inventory.iInvNCost, Inventory.iInvAdvance,Inventory.iInvBatch, Inventory.iSafeNum, Inventory.iTopSum,Inventory.iLowSum, Inventory.iOverStock, Inventory.cInvABC,Inventory.bInvQuality, Inventory.bInvBatch, Inventory.bInvEntrust,Inventory.bInvOverStock, Inventory.dSDate, Inventory.dEDate,Inventory.bFree1, Inventory.bFree2, Inventory.cInvDefine1,Inventory.cInvDefine2, Inventory.cInvDefine3, Inventory.I_id,Inventory.bInvType, Inventory.iInvMPCost, Inventory.cQuality,Inventory.iInvSaleCost, Inventory.iInvSCost1, Inventory.iInvSCost2,Inventory.iInvSCost3, Inventory.bFree3, Inventory.bFree4,Inventory.bFree5, Inventory.bFree6, Inventory.bFree7,Inventory.bFree8, Inventory.bFree9, Inventory.bFree10,Inventory.cCreatePerson, Inventory.cModifyPerson,Inventory.dModifyDate, Inventory.fSubscribePoint,Inventory.fVagQuantity, Inventory.cValueType, Inventory.bFixExch,Inventory.fOutExcess, Inventory.fInExcess,Inventory.iWarnDays, Inventory.fExpensesExch, Inventory.bTrack,Inventory.bSerial, Inventory.bBarCode, Inventory.iId,Inventory.cBarCode, Inventory.cInvDefine4, Inventory.cInvDefine5,Inventory.cInvDefine6, Inventory.cInvDefine7, Inventory.cInvDefine8,Inventory.cInvDefine9, Inventory.cInvDefine10, Inventory.cInvDefine11,Inventory.cInvDefine12, Inventory.cInvDefine13, Inventory.cInvDefine14,Inventory.cInvDefine15, Inventory.cInvDefine16,convert(bit,0) AS bQuanSign, Inventory.iGroupType,Inventory.cGroupCode, Inventory.cAssComUnitCode,Inventory.cSAComUnitCode, Inventory.cPUComUnitCode,Inventory.cSTComUnitCode, Inventory.cComUnitCode,Inventory.cCAComUnitCode, Inventory.cFrequency, Inventory.iFrequency,Inventory.iDays, Inventory.dLastDate, Inventory.iWastage,Inventory.bSolitude, Inventory.cEnterprise, Inventory.cAddress,Inventory.cFile, Inventory.cLabel, Inventory.cCheckOut,Inventory.cLicence, Inventory.bSpecialties, Inventory.cDefWareHouse, Inventory.iHighPrice, Inventory.cPriceGroup,Inventory.iExpSaleRate, Inventory.cOfferGrade, Inventory.iOfferRate,Inventory.cMonth, Inventory.iAdvanceDate, Inventory.cCurrencyName,Inventory.cProduceAddress, Inventory.cProduceNation,Inventory.cRegisterNo, Inventory.cEnterNo, Inventory.cPackingType,Inventory.cEnglishName, Inventory.bPropertyCheck,Inventory.cPreparationType, Inventory.cCommodity,Inventory.iRecipeBatch, Inventory.cNotPatentName,Inventory.pubufts
,(case when (isnull(DispatchLists.bQANeedCheck,0)=1 and DispatchLists.iquantity>0) then isnull(DispatchLists.iqaquantity,0) else isnull(DispatchLists.iquantity,0) end ) as iquantity
, (case when (isnull(DispatchLists.bqaneedcheck,0)=1 and DispatchLists.inum>0) then isnull(DispatchLists.iqanum,0) else isnull(DispatchLists.inum,0) end) as inum,isnull(DispatchLists.fOutQuantity,0) as fOutQuantity
,isnull(case when inventory.igrouptype=1 then DispatchLists.fOutQuantity/DispatchLists.iInvExchRate else DispatchLists.fOutNum end ,0) as fOutNum
From dbo.DispatchList
INNER JOIN DispatchLists ON DispatchList.DLID = DispatchLists.DLID
INNER JOIN Inventory ON DispatchLists.cInvCode = Inventory.cInvCode
INNER JOIN Warehouse ON DispatchLists.cWhCode = Warehouse.cWhCode
INNER JOIN ComputationUnit ON Inventory.cComUnitCode = ComputationUnit.cComunitCode
LEFT OUTER JOIN Department ON DispatchList.cDepCode = Department.cDepCode
LEFT OUTER JOIN ComputationUnit ComputationUnit_1 ON DispatchLists.cUnitID = ComputationUnit_1.cComunitCode
Left Join SA_SORowNo On Dispatchlists.isosid=SA_SORowNo.isosid
Left Join SaleType ON DispatchList.cStCode=SaleType.cstcode
left join vendor v1 on v1.cvencode =dispatchlists.cvmivencode
left join (select cpersoncode as cpersoncode2,cpersonname from person) person on person.cpersoncode2 =dispatchlist.cpersoncode
left join SaleBillVouch on SaleBillVouch.SBVID = DispatchList.SBVID
left join VouchType on VouchType.cVouchType = case when salebillvouch.SBVID is null then dispatchlist.cVouchType else salebillvouch.cVouchType end
WHERE (DispatchList.cVouchType='05' OR DispatchList.cVouchType='06')
AND ((ISNULL(DispatchList.cSaleOut,'') = '' OR isnull(bqaneedcheck,0)=1) OR DispatchList.cSaleOut ='ST')
AND not (isnull(DispatchList.bFirst,0) =1 And isnull(DispatchLists.bIsStQC,0) =0)
and (
(ABS(ISNULL((case when (isnull(DispatchLists.bQANeedCheck,0)=1 and DispatchLists.iquantity>0) then DispatchLists.iqaquantity else DispatchLists.iquantity end ),0))-ABS(ISNULL(DispatchLists.fOutQuantity,0)) ) >=0.01
or ( igrouptype=2 and ( ABS(ISNULL((case when (isnull(DispatchLists.bQANeedCheck,0)=1 and DispatchLists.iquantity>0) then DispatchLists.iqanum else DispatchLists.inum end ),0))-ABS(ISNULL(DispatchLists.fOutnum,0)) ) >=0.01)
)
AND (ISNULL(DispatchLists.cWhCode,'')<>'')
AND bInvType=0
AND bService=0
AND ((ISNULL(DispatchLists.bSettleAll,0)=0 and DispatchList.cvouchtype='05') or DispatchList.cvouchtype='06')
and ISNULL(DispatchList.cVerifier,'')<>''
AND DISPATCHLIST.CDLCODE = N'{0}'
and ((case when isnull(DispatchLists.bQANeedCheck,0)=1 and isnull(DispatchLists.iquantity,0)>0 then DispatchLists.iqaquantity else DispatchLists.iquantity end) <>0 OR (case when isnull(DispatchLists.bQANeedCheck,0)=1 and isnull(DispatchLists.inum,0)>0 then DispatchLists.iqanum else DispatchLists.inum end) <>0) " , cDLCode);
try
{
dt = DBHelperSQL.QueryTable(connectionString, strSql);
}
catch (Exception ex)
{
errMsg = ex.Message;
return(dispatchList);
}
//转换子表
DispatchLists dispatchLists = null;
foreach (DataRow row in dt.Rows)
{
dispatchLists = EntityConvert.ConvertToDispatchLists(row);
dispatchList.List.Add(dispatchLists);
}
return(dispatchList);
}
