/// <summary>
/// 增加一条数据
/// </summary>
public string Add(UFIDA.U8.UAP.CustomApp.ControlForm.Model.PurBillVouchs_Temp model)
{
StringBuilder strSql = new StringBuilder();
StringBuilder strSql1 = new StringBuilder();
StringBuilder strSql2 = new StringBuilder();
if (model.PONO != null)
{
strSql1.Append("PONO,");
strSql2.Append("'" + model.PONO + "',");
}
if (model.cInvCode != null)
{
strSql1.Append("cInvCode,");
strSql2.Append("'" + model.cInvCode + "',");
}
if (model.iQuantity != null)
{
strSql1.Append("iQuantity,");
strSql2.Append("" + model.iQuantity + ",");
}
strSql.Append("insert into PurBillVouchs_Temp(");
strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
strSql.Append(")");
strSql.Append(" values (");
strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
strSql.Append(")");
return(strSql.ToString());
}
private void CheckData(SqlTransaction tran)
{
try
{
string sErr = "";
string sDate = dateEdit1.DateTime.ToString("yyyy-MM-dd");
string sInvoiceNo = txtInvoiceNO.Text.Trim();
string sSQL = @"
SELECT InvoiceNo,[Date] FROM [dbo].[PurBillVouchs_Import] where InvoiceNo = 'aaaaaa'
union
SELECT InvoiceNo,[Date] FROM [dbo].[Rdrecords01_Import] where InvoiceNo = 'aaaaaa'
";
sSQL = sSQL.Replace("aaaaaa", sInvoiceNo);
DataTable dtExists = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtExists != null && dtExists.Rows.Count > 0)
{
if (MessageBox.Show("The invoice number has been imported and whether to continue?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk) != DialogResult.Yes)
{
throw new Exception("User cancelled");
}
}
sSQL = "select * from foreigncurrency where cexch_code = 'aaaaaaaa' or cexch_name = 'aaaaaaaa'";
sSQL = sSQL.Replace("aaaaaaaa", txtCurrency.Text.Trim());
DataTable dtCurrency = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtCurrency == null || dtCurrency.Rows.Count == 0)
{
throw new Exception("Currency does not exist");
}
sSQL = "select getdate()";
DateTime dNow = BaseFunction.ReturnDate(DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0].Rows[0][0]);
DateTime dNowDate = BaseFunction.ReturnDate(dNow.ToString("yyyy-MM-dd"));
string sDateTxt = dNow.ToString("yyyyMMddHHmmss");
sSQL = "truncate table RdRecords01_Temp";
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
for (int i = 0; i < gridViewPL.RowCount; i++)
{
Model.RdRecords01_Temp modRdsTemp = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.RdRecords01_Temp();
modRdsTemp.cInvCode = gridViewPL.GetRowCellValue(i, gridColPARTNO).ToString().Trim();
modRdsTemp.PONO = gridViewPL.GetRowCellValue(i, gridColPONO).ToString().Trim();
modRdsTemp.iQuantity = BaseFunction.ReturnDecimal(gridViewPL.GetRowCellValue(i, gridColQUANTITY), 6);
DAL.RdRecords01_Temp dalRdsTemp = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.RdRecords01_Temp();
sSQL = dalRdsTemp.Add(modRdsTemp);
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
sSQL = "truncate table PurBillVouchs_Temp";
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
for (int i = 0; i < gridViewIN.RowCount; i++)
{
Model.PurBillVouchs_Temp modINTemp = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.PurBillVouchs_Temp();
modINTemp.cInvCode = gridViewIN.GetRowCellValue(i, gridCol_PARTNO).ToString().Trim();
modINTemp.PONO = gridViewIN.GetRowCellValue(i, gridCol_PONO).ToString().Trim();
modINTemp.iQuantity = BaseFunction.ReturnDecimal(gridViewIN.GetRowCellValue(i, gridCol_QUANTITY), 6);
DAL.PurBillVouchs_Temp dalINTemp = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.PurBillVouchs_Temp();
sSQL = dalINTemp.Add(modINTemp);
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
for (int i = 0; i < gridViewPL.RowCount; i++)
{
string sInvAbbCode = gridViewPL.GetRowCellValue(i, gridColPARTNO).ToString().Trim();
if (sInvAbbCode.Trim() == "")
{
continue;
}
string sPONO = gridViewPL.GetRowCellDisplayText(i, gridColPONO).ToString().Trim();
//判断订单是否存在
sSQL = @"
select cPOID from PO_Pomain where cPOID = 'aaaaaa'
";
sSQL = sSQL.Replace("aaaaaa", sPONO);
DataTable dtCou = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtCou == null || dtCou.Rows.Count == 0)
{
sErr = sErr + "PL: line " + (i + 1).ToString() + " PO No does not match\n";
continue;
}
sSQL = @"
select * from Inventory where cInvCode = '{0}' or cInvAddCode = '{0}'
";
sSQL = string.Format(sSQL, sInvAbbCode);
DataTable dtInv = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtInv == null || dtInv.Rows.Count == 0)
{
sErr = sErr + "PL: Line " + (i + 1) + " cInvAddCode is not exists\n";
continue;
}
//判断订单是否存在物料
sSQL = @"
select a.cPOID ,b.cInvCode
from PO_Pomain a inner join PO_PODetails b on a.poid = b.poid
inner join Inventory inv on b.cInvCode = inv.cInvCode
where a.cPOID = 'aaaaaa' and (b.cInvCode = 'bbbbbb' or inv.cInvAddCode = 'bbbbbb')
";
sSQL = sSQL.Replace("aaaaaa", gridViewPL.GetRowCellDisplayText(i, gridColPONO).ToString().Trim());
sSQL = sSQL.Replace("bbbbbb", sInvAbbCode);
dtCou = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtCou == null || dtCou.Rows.Count == 0)
{
sErr = sErr + "PL: line " + (i + 1).ToString() + " PO No does not exist in this material\n";
continue;
}
sSQL = @"
select inv.cInvCode,isnull(inv.bPurchase,0) as bPurchase
from Inventory inv
inner join (
select distinct b.cInvCode
from PO_Pomain a inner join po_podetails b on a.POID = b.POID
where a.cPOID = '{1}'
) po on inv.cInvCode = po.cInvCode
where inv.cInvAddCode = '{0}' or inv.cInvCode = '{0}'
";
sSQL = string.Format(sSQL, sInvAbbCode, gridViewPL.GetRowCellValue(i, gridColPONO).ToString().Trim());
dtInv = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtInv == null || dtInv.Rows.Count == 0)
{
sErr = sErr + "PL: Line " + (i + 1) + " cInvAddCode is not exists\n";
continue;
}
if (dtInv.Rows.Count > 1)
{
sSQL = @"
select cInvCode,isnull(bPurchase,0) as bPurchase from Inventory where cInvAddCode = 'AAAAAAAA'
";
sSQL = sSQL.Replace("AAAAAAAA", sInvAbbCode);
dtInv = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtInv == null || dtInv.Rows.Count != 1)
{
sErr = sErr + "PL: Line " + (i + 1) + " cInvAddCode is non-unique and code is not exists,Excel line(PL) " + (i + 14) + "\n";
continue;
}
}
string sInvCode = dtInv.Rows[0]["cInvCode"].ToString().Trim();
if (!Convert.ToBoolean(dtInv.Rows[0]["bPurchase"]))
{
sErr = sErr + "PL: line " + (i + 1).ToString() + " inventory has no purchase attributes\n";
continue;
}
//判断是否生成了采购入库单
string sRdsID = gridViewPL.GetRowCellValue(i, gridColRds01ID).ToString().Trim();
if (sRdsID != "")
{
sSQL = @"
select Autoid from Rdrecords01 where autoid = aaaaaa
";
sSQL = sSQL.Replace("aaaaaa", sRdsID);
DataTable dtRds = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtRds != null && dtRds.Rows.Count > 0)
{
sErr = sErr + "PL: Line " + (i + 1) + " purchase receipt warrant is exists\n";
continue;
}
}
// PONO ,containerNO ,PARTNO 相同的只允许导入一次
sSQL = @"
select *
from Rdrecords01_Import
where PONO = 'aaaaaa' AND CONTAINERNO = 'bbbbbb' AND PARTNO = 'cccccc'
";
sSQL = sSQL.Replace("aaaaaa", gridViewPL.GetRowCellDisplayText(i, gridColPONO).ToString().Trim());
sSQL = sSQL.Replace("bbbbbb", gridViewPL.GetRowCellDisplayText(i, gridColCONTAINERNO).ToString().Trim());
sSQL = sSQL.Replace("cccccc", sInvCode);
dtCou = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtCou != null && dtCou.Rows.Count > 0)
{
sErr = sErr + "PL: line " + (i + 1).ToString() + " PONO CONTAINERNO PARTNO have been imported\n";
continue;
}
//判断入库单与发票是否存在不一致数据
sSQL = @"
if object_id('tempdb..#_Temp') is not null
drop table #_Temp
select a.cInvCode,a.PONO,sum(a.iQuantity) as iINQty,cast(null as decimal(16,6)) as iRDQty
into #_Temp
from PurBillVouchs_Temp a
where a.cInvCode = 'aaaaaa'
group by a.cInvCode,a.PONO
insert into #_Temp
select a.cInvCode,a.PONO,cast(null as decimal(16,6)) as iINQty,sum(a.iQuantity) as iRDQty
from Rdrecords01_Temp a
where a.cInvCode = 'aaaaaa'
group by a.cInvCode,a.PONO
select count(1) as iCou,cInvCode,PONO,sum(iINQty) as iINQty, sum(iRDQty) as iRDQty
from #_Temp
group by cInvCode,PONO
having sum(isnull(iINQty,0)) <> sum(isnull(iRDQty,0))
";
sSQL = sSQL.Replace("aaaaaa", sInvCode);
dtCou = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtCou != null && dtCou.Rows.Count > 0)
{
sErr = sErr + "PL: Excel line " + (i + 1).ToString() + " IN & PL is not equal\n";
continue;
}
//判断入库单是否超出订单
sSQL = @"
if object_id('tempdb..#_Temp2') is not null
drop table #_Temp2
select a.cPOID,b.cInvCode,sum(b.iQuantity) as iQtyPO,cast(null as decimal(16,6)) as iRDQty
into #_Temp2
from PO_Pomain a inner join po_podetails b on a.poid = b.poid
where b.cInvCode = 'aaaaaa' and a.cPOID = 'bbbbbb'
group by a.cPOID,b.cInvCode
insert into #_Temp2
select a.PONO,a.cInvCode,cast(null as decimal(16,6)) as iQtyPO,sum(a.iQuantity) as iRDQty
from Rdrecords01_Temp a
where a.cInvCode = 'aaaaaa' and a.PONO = 'bbbbbb'
group by a.cInvCode,a.PONO
select count(1) as iCou,cInvCode,cPOID,sum(iQtyPO) as iINQty, sum(iRDQty) as iRDQty
from #_Temp2
group by cInvCode,cPOID
having sum(isnull(iQtyPO,0)) < sum(isnull(iRDQty,0))
";
sSQL = sSQL.Replace("aaaaaa", sInvCode);
sSQL = sSQL.Replace("bbbbbb", gridViewPL.GetRowCellDisplayText(i, gridColPONO).ToString().Trim());
dtCou = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtCou != null && dtCou.Rows.Count > 0)
{
sErr = sErr + "PL: PO No line " + (i + 1).ToString() + " overorder entry\n";
continue;
}
}
for (int i = 0; i < gridViewIN.RowCount; i++)
{
string sPONO = gridViewIN.GetRowCellValue(i, gridCol_PONO).ToString().Trim();
string sInvAbbCode = gridViewIN.GetRowCellValue(i, gridCol_PARTNO).ToString().Trim();
if (sInvAbbCode.Trim() == "")
{
continue;
}
//判断订单是否存在
sSQL = @"
select cPOID from PO_Pomain where cPOID = 'aaaaaa'
";
sSQL = sSQL.Replace("aaaaaa", sPONO);
DataTable dtCou = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtCou == null || dtCou.Rows.Count == 0)
{
sErr = sErr + "IN: line " + (i + 1).ToString() + " PO No does not match\n";
continue;
}
sSQL = @"
select * from Inventory where cInvCode = '{0}' or cInvAddCode = '{0}'
";
sSQL = string.Format(sSQL, sInvAbbCode);
DataTable dtInv = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtInv == null || dtInv.Rows.Count == 0)
{
sErr = sErr + "IN: Line " + (i + 1) + " cInvAddCode is not exists\n";
continue;
}
//判断订单是否存在物料
sSQL = @"
select a.cPOID ,b.cInvCode
from PO_Pomain a inner join PO_PODetails b on a.poid = b.poid
inner join Inventory inv on b.cInvCode = inv.cInvCode
where a.cPOID = 'aaaaaa' and (b.cInvCode = 'bbbbbb' or inv.cInvAddCode = 'bbbbbb')
";
sSQL = sSQL.Replace("aaaaaa", gridViewIN.GetRowCellDisplayText(i, gridCol_PONO).ToString().Trim());
sSQL = sSQL.Replace("bbbbbb", sInvAbbCode);
dtCou = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtCou == null || dtCou.Rows.Count == 0)
{
sErr = sErr + "IN: line " + (i + 1).ToString() + " PO No does not exist in this material\n";
continue;
}
sSQL = @"
select inv.cInvCode,isnull(inv.bPurchase,0) as bPurchase
from Inventory inv
inner join (
select distinct b.cInvCode
from PO_Pomain a inner join po_podetails b on a.POID = b.POID
where a.cPOID = '{1}'
) po on inv.cInvCode = po.cInvCode
where inv.cInvAddCode = '{0}' or inv.cInvCode = '{0}'
";
sSQL = string.Format(sSQL, sInvAbbCode, gridViewIN.GetRowCellValue(i, gridCol_PONO).ToString().Trim());
dtInv = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtInv == null || dtInv.Rows.Count == 0)
{
sErr = sErr + "IN: Line " + (i + 1) + " cInvAddCode is not exists\n";
continue;
}
if (dtInv.Rows.Count > 1)
{
sSQL = @"
select cInvCode,isnull(bPurchase,0) as bPurchase from Inventory where cInvAddCode = 'AAAAAAAA'
";
sSQL = sSQL.Replace("AAAAAAAA", sInvAbbCode);
dtInv = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtInv == null || dtInv.Rows.Count != 1)
{
sErr = sErr + "IN: Line " + (i + 1) + " cInvAddCode is non-unique and code is not exists,Excel line(PL) " + (i + 14) + "\n";
continue;
}
}
string sInvCode = dtInv.Rows[0]["cInvCode"].ToString().Trim();
//防止有空数据
if (sInvCode != "")
{
decimal dPrice = BaseFunction.ReturnDecimal(gridViewIN.GetRowCellValue(i, gridCol_PRICEPERUNIT));
decimal dSum = BaseFunction.ReturnDecimal(gridViewIN.GetRowCellValue(i, gridCol_TOTALPRICE));
if (dPrice <= 0 || dSum <= 0)
{
sErr = sErr + "IN: Line " + (i + 1) + " unit price or amount is not correct\n";
continue;
}
}
}
if (sErr != "")
{
throw new Exception(sErr);
}
}
catch (Exception ee)
{
throw new Exception(ee.Message);
}
}
