//写入单身信息
private void SetDetailInfo(ERP_Create_Purtg_HeadObject headObj, ERP_Create_Purtg_DetailObject detailObj)
{
detailObj.TH015 = detailObj.TH007;
detailObj.TH016 = detailObj.TH007;
detailObj.TH034 = detailObj.TH007;
detailObj.TH064 = detailObj.TH008;
detailObj.TH065 = detailObj.TH008;
detailObj.TH019 = Math.Round(float.Parse(detailObj.TH007) * float.Parse(detailObj.TH018), 6, MidpointRounding.AwayFromZero).ToString();
detailObj.TH003 = detailObj.RowIndex.ToString().PadLeft(4, '0');
string sqlstr = @"INSERT INTO PURTH(COMPANY,CREATOR,USR_GROUP,CREATE_DATE,FLAG,
TH001,TH002,TH003,TH004,TH005,TH006,TH007,TH008,TH009,TH010,
TH011,TH012,TH013,TH014,TH015,TH016,TH018,TH019,TH026,TH027,
TH029,TH030,TH031,TH032,TH033,TH034,TH035,TH042,TH043,TH044,
TH060,TH064,TH065,TH071,TH072,THC02)
VALUES('{0}','{1}','{2}','{3}',1,'{4}','{5}','{6}',
'{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}',
'{18}','{19}','{20}','{21}','N','{22}','N','N','N','N','{23}',
'{24}','{25}','{26}','N','N','0','{27}','{28}','1','##########','{29}')";
mssql.SQLexcute(strConnection, string.Format(sqlstr, headObj.Company, headObj.Uid, headObj.Ugroup, headObj.Time, headObj.TG001, headObj.TG002,
detailObj.TH003, detailObj.TH004, detailObj.TH005, detailObj.TH006, detailObj.TH007, detailObj.TH008, detailObj.TH009, detailObj.TH010,
detailObj.TH011, detailObj.TH012, detailObj.TH013, detailObj.TH014, detailObj.TH015, detailObj.TH016, detailObj.TH018, detailObj.TH019,
detailObj.TH027, detailObj.TH033, detailObj.TH034, detailObj.TH035, detailObj.TH042, detailObj.TH064, detailObj.TH065, detailObj.THC02));
}
private void SetDetailDefaultDef(ERP_Create_Purtg_HeadObject headObj)
{
ERP_Create_Purtg_DetailObject detailObj = new ERP_Create_Purtg_DetailObject();
GetLsDt(headObj, detailObj);
if (detailObj.LsDt != null)
{
for (int lsRowIndex = 0; lsRowIndex < detailObj.LsDt.Rows.Count; lsRowIndex++)
{
try
{
detailObj.TH004 = detailObj.LsDt.Rows[lsRowIndex][0].ToString();
detailObj.TH005 = detailObj.LsDt.Rows[lsRowIndex][1].ToString();
detailObj.TH006 = detailObj.LsDt.Rows[lsRowIndex][2].ToString();
detailObj.TH008 = detailObj.LsDt.Rows[lsRowIndex][3].ToString();
detailObj.TH009 = detailObj.LsDt.Rows[lsRowIndex][4].ToString();
detailObj.Total = float.Parse(detailObj.LsDt.Rows[lsRowIndex][5].ToString());
detailObj.TH010 = headObj.TG005;
detailObj.TH014 = headObj.TG003;
GetSlDt(headObj, detailObj);
GetNumber(headObj, detailObj);
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
}
}
}
//数量
private void GetNumber(ERP_Create_Purtg_HeadObject headObj, ERP_Create_Purtg_DetailObject detailObj)
{
if (detailObj.SlDt != null && detailObj.SlDt.Rows.Count != 0)
{
detailObj.RowIndex++;
detailObj.TH011 = detailObj.SlDt.Rows[0][1].ToString();
detailObj.TH012 = detailObj.SlDt.Rows[0][2].ToString();
detailObj.TH013 = detailObj.SlDt.Rows[0][3].ToString();
detailObj.TH018 = Math.Round(float.Parse(detailObj.SlDt.Rows[0][4].ToString()), 6, MidpointRounding.AwayFromZero).ToString();
detailObj.TH033 = detailObj.SlDt.Rows[0][5].ToString();
detailObj.TH035 = detailObj.SlDt.Rows[0][6].ToString();
detailObj.TH042 = detailObj.SlDt.Rows[0][7].ToString();
detailObj.THC02 = detailObj.SlDt.Rows[0][8].ToString();
if (float.Parse(detailObj.SlDt.Rows[0][0].ToString()) >= detailObj.Total)
{
detailObj.TH007 = Math.Round(detailObj.Total, 6, MidpointRounding.AwayFromZero).ToString();
SetDetailInfo(headObj, detailObj);
}
else
{
float sl = float.Parse(detailObj.SlDt.Rows[0][0].ToString());
detailObj.TH007 = Math.Round(sl, 6, MidpointRounding.AwayFromZero).ToString();
detailObj.Total -= sl;
SetDetailInfo(headObj, detailObj);
detailObj.SlDt.Rows.RemoveAt(0);
GetNumber(headObj, detailObj);
}
}
}
private void UptJHXAInfo(ERP_Create_Purtg_HeadObject headObj)
{
string time = mssql.SQLselect(strConnection, "select dbo.f_getTime(1)").Rows[0][0].ToString();
string sqlstr = @"UPDATE COMFORT.dbo.JH_LYXA SET MODIFIER='{1}', MODI_DATE='{2}',
FLAG=(convert(int,COMFORT.dbo.JH_LYXA.FLAG))%999+1, JHXA011 = 'Y', UDF01 = '{3}'WHERE JHXA005 = '{0}' ";
mssql.SQLexcute(strConnection, string.Format(sqlstr, headObj.FlowId, headObj.Uid, time, headObj.TG001 + '-' + headObj.TG002));
}
//根据流水号，获获取临时表的明细
private void GetLsDt(ERP_Create_Purtg_HeadObject headObj, ERP_Create_Purtg_DetailObject detailObj)
{
string sqlstr = @"SELECT RTRIM(MB001), RTRIM(MB002), RTRIM(MB003), RTRIM(MB004),
RTRIM(JHXA003), RTRIM(JHXA009) FROM INVMB
INNER JOIN JH_LYXA ON JHXA007 = MB001 WHERE 1=1 AND JHXA005 = '{0}' ORDER BY ID";
detailObj.LsDt = mssql.SQLselect(strConnection, string.Format(sqlstr, headObj.FlowId));
}
private void GetHeadTime(ERP_Create_Purtg_HeadObject headObj)
{
string sqlstr = "select dbo.f_getTime(1)";
DataTable dt = mssql.SQLselect(strConnection, sqlstr);
if (dt != null)
{
headObj.Time = dt.Rows[0][0].ToString();
}
}
// 写入单头
private void SetHeadInfo(ERP_Create_Purtg_HeadObject headObj)
{
string sqlstr = @"INSERT INTO PURTG (COMPANY, CREATOR, USR_GROUP, CREATE_DATE, FLAG, TG001, TG002, TG003, TG004, TG005,
TG006, TG007, TG008, TG009, TG010, TG013, TG014, TG015, TG021, TG030, TG033, TG016, TG043, TG052)
VALUES('{0}', '{1}', '{2}', '{3}', '1', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}',
'{12}', '{13}', '{14}', '{15}', '{16}', '{17}', '{18}', '{19}', '', '', '' )";
mssql.SQLexcute(strConnection, string.Format(sqlstr, headObj.Company, headObj.Uid, headObj.Ugroup, headObj.Time, headObj.TG001,
headObj.TG002, headObj.TG003, headObj.TG004, headObj.TG005, headObj.TG006, headObj.TG007, headObj.TG008, headObj.TG009,
headObj.TG010, headObj.TG013, headObj.TG014, headObj.TG015, headObj.TG021, headObj.TG030, headObj.TG033));
}
//更新单身金额信息
private void UptDetailMoney(ERP_Create_Purtg_HeadObject headObj)
{
string sqlstr = @"UPDATE COMFORT.dbo.PURTH SET
TH045 = CAST(ROUND(TH019/(1+CONVERT(FLOAT, TG030)),2) AS NUMERIC(10,2)),
TH046 = CAST(ROUND(TH019 - (TH019/(1+CONVERT(FLOAT, TG030))),2) AS NUMERIC(10,2)),
TH047 = CAST(ROUND((TH019 * CONVERT(FLOAT, TG008)/(1+CONVERT(FLOAT, TG030))),2)
AS NUMERIC(10,2)),
TH048 = CAST(ROUND((TH019 * CONVERT(FLOAT, TG008)) -
(TH019 * CONVERT(FLOAT, TG008)/(1+CONVERT(FLOAT, TG030))),2) AS NUMERIC(10,2))
FROM PURTH INNER JOIN COMFORT.dbo.PURTG AS PURTG ON TG001 = TH001 AND TG002 = TH002
WHERE TG001= '{0}' AND TG002= '{1}' ";
mssql.SQLexcute(strConnection, string.Format(sqlstr, headObj.TG001, headObj.TG002));
}
private void GetHeadTG002(ERP_Create_Purtg_HeadObject headObj)
{
//string sqlstr = @"SELECT (CASE WHEN A1 IS NULL THEN A2 + '0001' ELSE A1 END ) B FROM
// (SELECT MAX(TG002) + 1 A1, SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 112), 3, 4) A2
// FROM PURTG
// WHERE TG001 = '{0}' AND SUBSTRING(TG002, 1, 4) =
// SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 112), 3, 4)) A";
string sqlstr = "EXEC dbo.P_GETDH '{0}'";
DataTable dt = mssql.SQLselect(strConnection, string.Format(sqlstr, headObj.TG001));
if (dt != null)
{
headObj.TG002 = dt.Rows[0][0].ToString();
}
}
//更新单头金额信息
private void UptHeadMoney(ERP_Create_Purtg_HeadObject headObj)
{
string sqlstr = @"UPDATE A SET TG017=SUMTH019,TG019=SUMTH046,TG026=SUMTH015,TG028=SUMTH045,TG031=SUMTH047,
TG032=SUMTH048,TG040=SUMTH050,TG041=SUMTH052,TG053=SUMTH007,TG054=SUMTH049
FROM COMFORT.dbo.PURTG A
INNER JOIN (SELECT TH001,TH002,SUMTH019=SUM(TH019),SUMTH046=SUM(TH046),
SUMTH007=SUM(CASE WHEN MA024='2' THEN FLOOR(TH007) ELSE TH007 END),
SUMTH015=SUM(CASE WHEN MA024='2' THEN FLOOR(TH015) ELSE TH015 END),SUMTH045=SUM(TH045),
SUMTH047=SUM(TH047),SUMTH048=SUM(TH048),SUMTH050=SUM(TH050),SUMTH052=SUM(TH052),
SUMTH049=SUM(TH049)
FROM COMFORT.dbo.PURTH
INNER JOIN COMFORT.dbo.CMSMA ON 1=1
GROUP BY TH001,TH002) AS B ON A.TG001=B.TH001 AND A.TG002=B.TH002
WHERE TG001= '{0}' AND TG002= '{1}' ";
mssql.SQLexcute(strConnection, string.Format(sqlstr, headObj.TG001, headObj.TG002));
}
//更新单头税率，汇率
private void UptHeadInfo2(ERP_Create_Purtg_HeadObject headObj)
{
string sqlstr = @"UPDATE COMFORT.dbo.PURTG SET TG008 = ISNULL(MG003, 1) FROM COMFORT.dbo.PURTG
LEFT JOIN(
SELECT G.MG001, G.MG003 FROM(
SELECT MG001, MAX(MG002) MG002 FROM CMSMG
WHERE 1 = 1
AND MG002 <= LEFT(dbo.f_getTime(1), 8)
GROUP BY MG001
) AS K LEFT JOIN COMFORT.dbo.CMSMG AS G ON K.MG001 = G.MG001 AND K.MG002 = G.MG002
) AS A ON TG007 = A.MG001
WHERE TG001 = '{0}' AND TG002 = '{1}'
UPDATE COMFORT.dbo.PURTG SET TG030 = ISNULL(MA004, 0) FROM COMFORT.dbo.PURTG LEFT JOIN COMFORT.dbo.CMSMA ON CMSMA.COMPANY = 'COMFORT'
WHERE TG001 = '{0}' AND TG002 ='{1}' ";
mssql.SQLexcute(strConnection, string.Format(sqlstr, headObj.TG001, headObj.TG002));
}
public string HandelDef(string flowID)
{
ERP_Create_Purtg_HeadObject headObj = new ERP_Create_Purtg_HeadObject();
headObj.FlowId = flowID;
GetHeadInfo(headObj);
GetHeadTG002(headObj);
GetHeadTime(headObj);
SetHeadInfo(headObj);
UptHeadInfo2(headObj);
SetDetailDef(headObj);
UptDetailMoney(headObj);
UptHeadMoney(headObj);
UptJHXAInfo(headObj);
return(headObj.TG001 + '-' + headObj.TG002);
}
//可进货采购单明细逻辑
private void GetSlDt(ERP_Create_Purtg_HeadObject headObj, ERP_Create_Purtg_DetailObject detailObj)
{
string sqlstr = @"SELECT DISTINCT TOP 200 TD008 - TD015 - ( SELECT isnull( SUM ( TH007 ), 0 )
FROM COMFORT.dbo.PURTH(NOLOCK) AS PURTH
INNER JOIN COMFORT.dbo.PURTG(NOLOCK) AS PURTG ON TG001 = TH001 AND TG002 = TH002
WHERE TH011 = TD001 AND TH012 = TD002 AND TH013 = TD003
AND TG013 = 'N'
) AS WJL,
TD001 AS TH011, RTRIM(TD002) AS TH012, TD003 AS TH013, (CASE WHEN TD010 IS NULL THEN 0 ELSE TD010 END) AS TH018, TD014 AS TH033, RTRIM(TD020) AS TH035,
RTRIM(TD022) AS TH042, RTRIM(TDC03) AS THC02, TC003, TD012
FROM COMFORT.dbo.PURTD(NOLOCK) AS PURTD
LEFT JOIN COMFORT.dbo.PURTC(NOLOCK) AS PURTC ON TC001 = TD001 AND TC002 = TD002
WHERE TC004 = '{0}' AND TD004 = '{1}'
AND (TD008 - TD015 - ( SELECT isnull( SUM ( TH007 ), 0 ) FROM COMFORT.dbo.PURTH PURTH
WHERE TH011 = TD001 AND TH012 = TD002 AND TH013 = TD003 AND TH030 = 'N' )) > 0
AND TD016 = 'N' AND TC014 = 'Y' AND TC001 <> '3305' AND TC001 <> '3306'
ORDER BY TC003 DESC, TD012, TD001 DESC, RTRIM(TD002), TD003";
detailObj.SlDt = mssql.SQLselect(strConnection, string.Format(sqlstr, headObj.TG005, detailObj.TH004));
}
private void GetHeadInfo(ERP_Create_Purtg_HeadObject headObj)
{
string sqlstr = @"SELECT DISTINCT RTRIM(JHXA.COMPANY) 公司别, RTRIM(JHXA.CREATOR) 创建人, RTRIM(JHXA.USR_GROUP) 用户组,
RTRIM(JHXA001) 进货单别, RTRIM(JHXA004) 进货日期, RTRIM(JHXA002) 供应商编号, RTRIM(JHXA013) 送货单号,
RTRIM(MA021) 交易币种, MG2.MG003 汇率,
RTRIM(MA030) 发票种类,
(CASE WHEN NOT (TC018 = '' OR TC018 IS NULL) THEN TC018
ELSE (CASE WHEN MA044 ='' OR MA044 IS NULL THEN '1' ELSE MA044 END ) END) AS TC018C,
RTRIM(MA003) 供应商全称,
(CASE WHEN TC026 IS NULL THEN MA064 ELSE TC026 END) AS TC026C,
(CASE WHEN TC027 = '' OR TC027 IS NULL THEN MA055 ELSE TC027 END) AS TC027C
FROM COMFORT.dbo.JH_LYXA AS JHXA
LEFT JOIN COMFORT.dbo.PURTC AS PURTC ON 1=2
LEFT JOIN COMFORT.dbo.INVMB AS INVMB ON MB001=JHXA007
LEFT JOIN COMFORT.dbo.PURMA AS PURMA ON MA001=JHXA002
LEFT JOIN (SELECT CMSMG.MG003, CMSMG.MG001 FROM COMFORT.dbo.CMSMG
INNER JOIN (SELECT MAX(MG002) MAXMG02, MG001 MAXMG01 FROM CMSMG GROUP BY MG001) AS MG
ON MG.MAXMG01 = CMSMG.MG001 AND MG.MAXMG02 = CMSMG.MG002) AS MG2 ON MG2.MG001 = MA021
WHERE JHXA005 IN ('{0}') AND JHXA011 = 'N' ";
DataTable dt = mssql.SQLselect(strConnection, string.Format(sqlstr, headObj.FlowId));
if (dt != null)
{
headObj.Company = dt.Rows[0][0].ToString();
headObj.Uid = dt.Rows[0][1].ToString();
headObj.Ugroup = dt.Rows[0][2].ToString();
headObj.TG001 = dt.Rows[0][3].ToString();
headObj.TG003 = dt.Rows[0][4].ToString();
headObj.TG005 = dt.Rows[0][5].ToString();
headObj.TG006 = dt.Rows[0][6].ToString();
headObj.TG007 = dt.Rows[0][7].ToString();
headObj.TG008 = dt.Rows[0][8].ToString();
headObj.TG009 = dt.Rows[0][9].ToString();
headObj.TG010 = dt.Rows[0][10].ToString();
headObj.TG014 = headObj.TG003;
headObj.TG021 = dt.Rows[0][11].ToString();
headObj.TG030 = dt.Rows[0][12].ToString();
headObj.TG033 = dt.Rows[0][13].ToString();
}
}
private void SetDetailDef(ERP_Create_Purtg_HeadObject headObj)
{
SetDetailDefaultDef(headObj);
}