private void toolStripButton2_Click(object sender, EventArgs e)
{
var sl = new AccountSelectForm(new SysConfigManager().Get().Accounts);
if (sl.ShowDialog() == DialogResult.OK && sl.CheckedInfo != null)
{
_from = sl.CheckedInfo;
var partner = textBox1.Text;
var sd = dateTimePicker1.Value.ToString("yyyy-MM-dd 00:00:00");
var ed = dateTimePicker2.Value.ToString("yyyy-MM-dd 23:59:59");
var data = new DataAccess(new SqlHelper(_from.GetConnectionString())).GetPurchaseOrder(partner, sd, ed);
dataGridView1.Columns.Clear();
dataGridView1.Columns.Add(new DataGridViewCheckBoxColumn { ReadOnly = false, Width = 30 });
foreach (DataColumn cln in data.Columns)
{
dataGridView1.Columns.Add(new DataGridViewTextBoxColumn { Name = cln.ColumnName, ReadOnly = true });
}
dataGridView1.Columns[1].Visible = false;
foreach (DataRow row in data.Rows)
{
dataGridView1.Rows.Add(
false,
row["id"],
row["单据日期"],
row["单据编号"],
row["供应商"],
row["业务员"],
row["预计到货日期"],
row["付款方式"],
row["订金金额"],
row["预付款百分比"]
);
}
textBox1.Focus();
}
}
private void toolStripButton3_Click(object sender, EventArgs e)
{
if (_from == null)
{
MessageBox.Show("请先选择导入采购订单");
return;
}
var sl = new AccountSelectForm(new SysConfigManager().Get().Accounts);
if (sl.ShowDialog() != DialogResult.OK) return;
var fromSqlHelper = new SqlHelper(_from.GetConnectionString());
var toSqlHelper = new SqlHelper(sl.CheckedInfo.GetConnectionString());
var da = new DataAccess(fromSqlHelper);
var result = new List<string>();
foreach (DataGridViewRow row in dataGridView1.Rows)
{
if (!(bool)row.Cells[0].Value) continue;
var purchaseOrder = da.ImportPurchaseOrder((Guid)row.Cells[1].Value);
if (purchaseOrder != null && purchaseOrder.Rows.Count > 0)
{
var saleOrder = DataAccess.PurchaseOrderToSaleOrder(purchaseOrder, _from.Name);
var rt = new DataAccess(toSqlHelper).ExportSaleOrder(saleOrder);
result.AddRange(rt);
}
}
if (result.Count > 0)
{
result.Insert(0, "导出成功！\r\n");
MessageBox.Show(string.Join("\r\n", result));
}
else
{
MessageBox.Show("没有可导入的采购订单");
}
}
private void 安装采购订单预付款程序ToolStripMenuItem_Click(object sender, EventArgs e)
{
var sl = new AccountSelectForm(new SysConfigManager().Get().Accounts);
if (sl.ShowDialog() != DialogResult.OK) return;
var sqls = new[]
{
@"IF ( OBJECT_ID('yvfukuan_insert', 'tr') IS NOT NULL )
DROP TRIGGER yvfukuan_insert",
//------------------------
@"CREATE TRIGGER yvfukuan_insert ON dbo.PU_PurchaseOrder
FOR INSERT
AS
--定义变量
DECLARE @percent FLOAT ,--预付款百分比
@money DECIMAL ,--含税金额
@id UNIQUEIDENTIFIER ,--单据id
@earnestMoney DECIMAL ,--预付款
@accountId UNIQUEIDENTIFIER ,--账户id
@styleId UNIQUEIDENTIFIER--结算方式id
--获取预付款百分比
SELECT @percent = CONVERT(FLOAT, REPLACE(ISNULL(pubuserdefnvc1, '0%'),
'%', '')) ,
@money = totalTaxAmount ,
@id = id
FROM INSERTED
IF(@percent=0)BEGIN
RETURN
END
--获取账户id
DECLARE @accName NVARCHAR(50)
SELECT @accName = pubuserdefnvc2
FROM INSERTED
IF ( @accName IS NULL
OR @accName = ''
)
BEGIN
SET @accName = '现金'
END
SELECT @accountId = id
FROM dbo.AA_BankAccount
WHERE name = @accName
--获取结算方式id
DECLARE @styName NVARCHAR(50)
SELECT @styName = pubuserdefnvc3
FROM INSERTED
IF ( @styName IS NULL
OR @styName = ''
) BEGIN
SET @styName = '现金'
END
SELECT @styleId = id
FROM dbo.AA_SettleStyle
WHERE name = @styName
--计算预付款
SET @earnestMoney = @money * @percent / 100
--添加预付款项
INSERT INTO [PU_PurchaseOrder_ArnestMoney]
( id ,
idPurchaseOrderDTO ,
idbankaccount ,
updated ,
idsettlestyle ,
updatedBy ,
amount ,
sequencenumber ,
origAmount ,
code
)
VALUES ( NEWID() ,
@id ,
@accountId ,
GETDATE() ,
@styleId ,
'demo' ,
@earnestMoney ,
0 ,
@earnestMoney ,
'0000'
);
--更新主记录预付款
UPDATE PU_PurchaseOrder
SET earnestMoney = @earnestMoney ,
origEarnestMoney = @earnestMoney
WHERE id = @id",
//------------------------
@"IF ( OBJECT_ID('yvfukuan_update', 'tr') IS NOT NULL )
DROP TRIGGER yvfukuan_update",
//------------------------
@"CREATE TRIGGER yvfukuan_update ON dbo.PU_PurchaseOrder
FOR UPDATE
AS
--定义变量
DECLARE @percent FLOAT ,--当前预付款百分比
@money DECIMAL ,--含税金额
@id UNIQUEIDENTIFIER ,--单据id
@earnestMoney DECIMAL ,--预付款
@accountId UNIQUEIDENTIFIER ,--账户id
@styleId UNIQUEIDENTIFIER--结算方式id
--如果更新的数据与之前的数据一致，则不用执行更新
IF ( ( SELECT COUNT(0)
FROM inserted AS a
JOIN deleted AS b ON a.id = b.id
AND a.pubuserdefnvc2 = b.pubuserdefnvc2
AND a.pubuserdefnvc3 = b.pubuserdefnvc3
AND a.pubuserdefnvc1 = b.pubuserdefnvc1
AND a.totalTaxAmount = b.totalTaxAmount
) > 0 ) BEGIN
RETURN
END
--获取当前预付款百分比
SELECT @percent = CONVERT(FLOAT, REPLACE(ISNULL(pubuserdefnvc1, '0%'),
'%', '')) ,
@money = totalTaxAmount ,
@id = id
FROM INSERTED
IF(@percent=0) BEGIN
DELETE PU_PurchaseOrder_ArnestMoney WHERE idPurchaseOrderDTO=@id
RETURN
END
--获取账户id
DECLARE @accName NVARCHAR(50)
SELECT @accName = pubuserdefnvc2
FROM INSERTED
IF ( @accName IS NULL
OR @accName = ''
)
BEGIN
SET @accName = '现金'
END
SELECT @accountId = id
FROM dbo.AA_BankAccount
WHERE name = @accName
--获取结算方式id
DECLARE @styName NVARCHAR(50)
SELECT @styName = pubuserdefnvc3
FROM INSERTED
IF ( @styName IS NULL
OR @styName = ''
) BEGIN
SET @styName = '现金'
END
SELECT @styleId = id
FROM dbo.AA_SettleStyle
WHERE name = @styName
--计算预付款
SET @earnestMoney = @money * @percent / 100
--更新预付款记录
IF ( ( SELECT COUNT(0)
FROM dbo.PU_PurchaseOrder_ArnestMoney
WHERE idPurchaseOrderDTO = @id
) > 0 )
BEGIN
UPDATE PU_PurchaseOrder_ArnestMoney
SET amount = @earnestMoney ,
idbankaccount = @accountId ,
idsettlestyle = @styleId ,
origAmount = @earnestMoney
WHERE idPurchaseOrderDTO = @id
END
ELSE
BEGIN
INSERT INTO [PU_PurchaseOrder_ArnestMoney]
( id ,
idPurchaseOrderDTO ,
idbankaccount ,
updated ,
idsettlestyle ,
updatedBy ,
amount ,
sequencenumber ,
origAmount ,
code
)
VALUES ( NEWID() ,
@id ,
@accountId ,
GETDATE() ,
@styleId ,
'demo' ,
@earnestMoney ,
0 ,
@earnestMoney ,
'0000'
);
END
--更新主记录预付款
UPDATE PU_PurchaseOrder
SET earnestMoney = @earnestMoney ,
origEarnestMoney = @earnestMoney
WHERE id = @id"
};
var sqlHelper = new SqlHelper(sl.CheckedInfo.GetConnectionString());
sqlHelper.Open();
foreach (var sql in sqls)
{
sqlHelper.Execute(sql);
}
sqlHelper.Close();
MessageBox.Show("安装成功");
}
private void 卸载采购订单预付款程序ToolStripMenuItem_Click(object sender, EventArgs e)
{
var sl = new AccountSelectForm(new SysConfigManager().Get().Accounts);
if (sl.ShowDialog() != DialogResult.OK) return;
var sql = @"IF ( OBJECT_ID('yvfukuan_insert', 'tr') IS NOT NULL )
DROP TRIGGER yvfukuan_insert;
IF ( OBJECT_ID('yvfukuan_update', 'tr') IS NOT NULL )
DROP TRIGGER yvfukuan_update";
var sqlHelper = new SqlHelper(sl.CheckedInfo.GetConnectionString());
sqlHelper.Open();
sqlHelper.Execute(sql);
sqlHelper.Close();
MessageBox.Show("卸载成功");
}