สร้างข้อมูลหลักสาขา

สร้างข้อมูลหลักสาขา

1.Stucture Table Branch

2. Stored Procedures

*การสร้าง Stored Procedures

BranchInsert

***********

USE [POSDatabase] GO
/****** Object:  StoredProcedure [dbo].[BranchInsert]    Script Date: 6/11/2017 11:07:36 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BranchInsert] (
@CompanyCode nvarchar(10),
@BranchCode nvarchar(10),
@BranchName nvarchar(100),
@Address1 nvarchar(100),
@Address2 nvarchar(100),
@City nvarchar(50),
@District nvarchar(50),
@Province nvarchar(50),
@PostCode nvarchar(50),
@Tell nvarchar(50),
@Fax nvarchar(50),
@BranchId nvarchar(20)
)
AS
SET NOCOUNT OFF;
INSERT INTO [Branch] ([CompanyCode], [BranchCode], [BranchName], [Address1], [Address2], [City], [District], [Province], [PostCode], [Tell], [Fax],[BranchId]) VALUES (@CompanyCode, @BranchCode, @BranchName, @Address1, @Address2, @City, @District, @Province, @PostCode, @Tell, @Fax,@BranchId);

****end branchInsert

BranchUpdate

************

USE [POSDatabase] GO
/****** Object:  StoredProcedure [dbo].[BranchUpdate]    Script Date: 6/11/2017 11:08:32 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BranchUpdate] (
@CompanyCode nvarchar(10),
@BranchCode nvarchar(10),
@BranchName nvarchar(100),
@Address1 nvarchar(100),
@Address2 nvarchar(100),
@City nvarchar(50),
@District nvarchar(50),
@Province nvarchar(50),
@PostCode nvarchar(50),
@Tell nvarchar(50),
@Fax nvarchar(50),
@BranchId nvarchar(20)
)
AS
SET NOCOUNT OFF;
UPDATE [Branch] SET [CompanyCode] = @CompanyCode, [BranchCode] = @BranchCode, [BranchName] = @BranchName, [Address1] = @Address1, [Address2] = @Address2, [City] = @City, [District] = @District, [Province] = @Province, [PostCode] = @PostCode, [Tell] = @Tell, [Fax] = @Fax ,[BranchId] =@BranchId WHERE (([CompanyCode] = @CompanyCode) AND ([BranchCode] = @BranchCode))

************end BranchUpdate

BranchDelete

***********

USE [POSDatabase] GO
/****** Object:  StoredProcedure [dbo].[BranchDelete]    Script Date: 6/11/2017 11:08:57 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BranchDelete] (
@CompanyCode nvarchar(10),
@BranchCode nvarchar(10)
)
AS
SET NOCOUNT OFF;
DELETE FROM [Branch] WHERE (([CompanyCode] = @CompanyCode) AND ([BranchCode] = @BranchCode))

***********End BranchDelete

BranchSelect

***********

USE [POSDatabase] GO
/****** Object:  StoredProcedure [dbo].[BranchSelect]    Script Date: 6/11/2017 11:09:25 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BranchSelect] (
@CompanyCode nvarchar(10),
@BranchCode nvarchar(10),
@BranchName nvarchar(100)
)
AS
SET NOCOUNT ON;
Select * from Branch
where ( companycode = case when @companycode = ” then companycode else @companycode end )
and ( BranchCode = case when @BranchCode = ” then BranchCode else @BranchCode end )
and (BranchName like ‘%’ + case when @BranchName=” then BranchName else @BranchName end + ‘%’)

***********End BranchSelect

3.เพิ่มฟอร์ม frmBranch.cs ภายใต้ Folder Setup

ใส่ Control ดังรูป

Source code frmBranch.cs

************

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using POS.Classes.Info;
using POS.Classes.Sp;
using POS.General;
using POS.Search;

namespace POS.Setup
{
public partial class frmBranch : Form
{
public string _BranchCode;
public frmBranch()
{
InitializeComponent();
}

private void btnClose_Click(object sender, EventArgs e)
{
this.Close();
}
//สำหรับ clear control
public void ClearCtrl()
{
txtBranchCode.Clear();
txtBranchName.Clear();
txtAddress1.Clear();
txtAddress2.Clear();
txtCity.Clear();
txtDistrict.Clear();
txtProvince.Clear();
txtPostCode.Clear();
txtTell.Clear();
txtFax.Clear();
txtBranchId.Clear();
}

public int BranchInsert()
{
Branch_Info infoBranch = new Branch_Info();
Branch_Sp spBranch = new Branch_Sp();
int insert = 0;
try
{
infoBranch.CompanyCode = PublicVariables.PCompanyCode.ToString();
infoBranch.BranchCode = Convert.ToString(txtBranchCode.Text.ToString());
infoBranch.BranchName = Convert.ToString(txtBranchName.Text.ToString());
infoBranch.Address1 = Convert.ToString(txtAddress1.Text.ToString());
infoBranch.Address2 = Convert.ToString(txtAddress2.Text.ToString());
infoBranch.District = Convert.ToString(txtDistrict.Text.ToString());
infoBranch.City = Convert.ToString(txtCity.Text.ToString());
infoBranch.Province = Convert.ToString(txtProvince.Text.ToString());
infoBranch.PostCode = Convert.ToString(txtPostCode.Text.ToString());
infoBranch.Tell = Convert.ToString(txtTell.Text.ToString());
infoBranch.Fax = Convert.ToString(txtFax.Text.ToString());
infoBranch.BranchId = Convert.ToString(txtBranchId.Text.ToString());
spBranch.BranchInsert(infoBranch);
insert = 1;
}
catch
{
insert = 0;
}
return insert;
}

public int BranchUpdate()
{
Branch_Info infoBranch = new Branch_Info();
Branch_Sp spBranch = new Branch_Sp();
int isUpdate = 0;
try
{
infoBranch.CompanyCode = PublicVariables.PCompanyCode.ToString();
infoBranch.BranchCode = Convert.ToString(txtBranchCode.Text.ToString());
infoBranch.BranchName = Convert.ToString(txtBranchName.Text.ToString());
infoBranch.Address1 = Convert.ToString(txtAddress1.Text.ToString());
infoBranch.Address2 = Convert.ToString(txtAddress2.Text.ToString());
infoBranch.District = Convert.ToString(txtDistrict.Text.ToString());
infoBranch.City = Convert.ToString(txtCity.Text.ToString());
infoBranch.Province = Convert.ToString(txtProvince.Text.ToString());
infoBranch.PostCode = Convert.ToString(txtPostCode.Text.ToString());
infoBranch.Tell = Convert.ToString(txtTell.Text.ToString());
infoBranch.Fax = Convert.ToString(txtFax.Text.ToString());
infoBranch.BranchId = Convert.ToString(txtBranchId.Text.ToString());
spBranch.BranchUpdate(infoBranch);
isUpdate = 1;
}
catch
{
isUpdate = 0;
}
return isUpdate;
}

//สำหรับตรวจสอบรหัสสาขา ว่ารหัสนี้เคยสร้างรึยัง
public int checkBranch(string CompanyCode, string BranchCode)
{
int isBranch = 0;
DataTable dtl = new DataTable();
Branch_Sp spCompany = new Branch_Sp();
dtl = spCompany.BranchSelect(Convert.ToString(CompanyCode.Trim()), Convert.ToString(BranchCode.Trim()), string.Empty);
if (dtl.Rows.Count > 0)
{
isBranch = 1;
}

return isBranch;
}

private void btnClear_Click(object sender, EventArgs e)
{
ClearCtrl();
btnSave.Text = “Save”;
txtBranchCode.Enabled = true;
}

private void frmBranch_Load(object sender, EventArgs e)
{
lblCompany.Text = PublicVariables.PCompanyCode.ToString() +  ” ” +PublicVariables.PCompanyName.ToString();
txtBranchCode.MaxLength = 10;
txtBranchName.MaxLength = 100;
txtAddress1.MaxLength = 100;
txtAddress2.MaxLength = 100;
txtCity.MaxLength = 50;
txtDistrict.MaxLength = 50;
txtProvince.MaxLength = 50;
txtPostCode.MaxLength = 50;
txtTell.MaxLength = 50;
txtFax.MaxLength = 50;
txtBranchId.MaxLength = 10;
}

private void btnSave_Click(object sender, EventArgs e)
{
if (String.IsNullOrEmpty(txtBranchCode.Text.ToString()))
{
MessageBox.Show(“กรุณาระบุข้อมูล รหัสสาขา”);
txtBranchCode.Focus();
return;
}

if (String.IsNullOrEmpty(txtBranchName.Text.ToString()))
{
MessageBox.Show(“กรุณาระบุข้อมูล ชื่อสาขา”);
txtBranchName.Focus();
return;
}

if (String.IsNullOrEmpty(txtAddress1.Text.ToString()))
{
MessageBox.Show(“กรุณาระบุข้อมูล ที่อยู่สาขา”);
txtAddress1.Focus();
return;
}

if (String.IsNullOrEmpty(txtDistrict.Text.ToString()))
{
MessageBox.Show(“กรุณาระบุข้อมูล ตำบล”);
txtDistrict.Focus();
return;
}

if (String.IsNullOrEmpty(txtCity.Text.ToString()))
{
MessageBox.Show(“กรุณาระบุข้อมูล อำเภอ”);
txtCity.Focus();
return;
}

if (String.IsNullOrEmpty(txtProvince.Text.ToString()))
{
MessageBox.Show(“กรุณาระบุข้อมูล จังหวัด”);
txtProvince.Focus();
return;
}

if (String.IsNullOrEmpty(txtPostCode.Text.ToString()))
{
MessageBox.Show(“กรุณาระบุข้อมูล รหัสไปรษณีย์”);
txtPostCode.Focus();
return;
}

if (String.IsNullOrEmpty(txtBranchId.Text.ToString()))
{
MessageBox.Show(“กรุณาระบุข้อมูล ลำดับที่สาขา”);
txtBranchId.Focus();
return;
}

if (btnSave.Text.ToString() == “Save”)
{
int isCompany = checkBranch(PublicVariables.PCompanyCode.ToString(),txtBranchCode.Text.Trim());
if (isCompany == 1)
{
MessageBox.Show(“รหัสสาขานี้มีอยู่แล้ว สำหรับหรัสบริษัท ” + PublicVariables.PCompanyCode.ToString(), “POS”, MessageBoxButtons.OK, MessageBoxIcon.Information);
txtBranchCode.Focus();
return;
}
int isInsert = BranchInsert();
if (isInsert == 1)
{
MessageBox.Show(“สร้างข้อมูลหลักบริษัทเรียบร้อย”, “Smart POS”, MessageBoxButtons.OK, MessageBoxIcon.Information);
ClearCtrl();
txtBranchCode.Focus();
}
else
{
MessageBox.Show(“ไม่สามารถสร้างข้อมูลหลักบริษัทได้ กรุณาติดต่อเจ้าหน้าที่ผู้ดูแลระบบ”, “POS”, MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
if (btnSave.Text.ToString() == “Edit”)
{
int isUpdate = BranchUpdate();
if (isUpdate == 1)
{
MessageBox.Show(“แก้ไขข้อมูลหลักสาขาเรียบร้อย”, “POS”, MessageBoxButtons.OK, MessageBoxIcon.Information);
ClearCtrl();
txtBranchCode.Focus();
}
else
{
MessageBox.Show(“ไม่สามารถแก้ไขข้อมูลหลักสาขาได้ กรุณาติดต่อเจ้าหน้าที่ผู้ดูแลระบบ”, “POS”, MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}

}

private void txtBranchCode_KeyDown(object sender, KeyEventArgs e)
{
try
{
if (e.KeyCode == Keys.Enter)
{
txtBranchName.Focus();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, “Smart POS”, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}

private void txtBranchName_KeyDown(object sender, KeyEventArgs e)
{
try
{
if (e.KeyCode == Keys.Enter)
{
txtAddress1.Focus();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, “Smart POS”, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}

private void txtAddress1_KeyDown(object sender, KeyEventArgs e)
{
try
{
if (e.KeyCode == Keys.Enter)
{
txtAddress2.Focus();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, “Smart POS”, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}

private void txtAddress2_KeyDown(object sender, KeyEventArgs e)
{
try
{
if (e.KeyCode == Keys.Enter)
{
txtCity.Focus();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, “Smart POS”, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}

private void txtDistrict_KeyDown(object sender, KeyEventArgs e)
{
try
{
if (e.KeyCode == Keys.Enter)
{
txtProvince.Focus();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, “Smart POS”, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}

private void txtProvince_KeyDown(object sender, KeyEventArgs e)
{
try
{
if (e.KeyCode == Keys.Enter)
{
txtPostCode.Focus();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, “Smart POS”, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}

private void txtPostCode_KeyDown(object sender, KeyEventArgs e)
{
try
{
if (e.KeyCode == Keys.Enter)
{
txtTell.Focus();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, “Smart POS”, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}

private void txtTell_KeyDown(object sender, KeyEventArgs e)
{
try
{
if (e.KeyCode == Keys.Enter)
{
txtFax.Focus();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, “Smart POS”, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}

private void txtFax_KeyDown(object sender, KeyEventArgs e)
{
try
{
if (e.KeyCode == Keys.Enter)
{
txtBranchId.Focus();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, “Smart POS”, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}

private void txtCity_KeyDown(object sender, KeyEventArgs e)
{
try
{
if (e.KeyCode == Keys.Enter)
{
txtDistrict.Focus();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, “Smart POS”, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}

private void txtBranchId_KeyDown(object sender, KeyEventArgs e)
{
try
{
if (e.KeyCode == Keys.Enter)
{
btnSave.Focus();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, “Smart POS”, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}

private void btnSearch_Click(object sender, EventArgs e)
{
BranchSearch searchBranch = new BranchSearch(this);
searchBranch.ShowDialog();
if (_BranchCode.ToString() != string.Empty)
{
DataTable dtbl = new DataTable();
Branch_Sp spBranch = new Branch_Sp();
dtbl = spBranch.BranchSelect(PublicVariables.PCompanyCode.ToString(), _BranchCode.ToString(), string.Empty);
foreach (DataRow dr in dtbl.Rows)
{
txtBranchCode.Enabled = false;
txtBranchCode.Text = dr[“BranchCode”].ToString();
txtBranchName.Text = dr[“BranchName”].ToString();
txtAddress1.Text = dr[“Address1”].ToString();
txtAddress2.Text = dr[“Address2”].ToString();
txtCity.Text = dr[“City”].ToString();
txtDistrict.Text = dr[“District”].ToString();
txtProvince.Text = dr[“Province”].ToString();
txtPostCode.Text = dr[“PostCode”].ToString();
txtTell.Text = dr[“Tell”].ToString();
txtFax.Text = dr[“Fax”].ToString();
txtBranchId.Text = dr[“BranchId”].ToString();
btnSave.Text = “Edit”;

}
}
}
}
}

************End frmBranch.cs

4. สร้างฟอร์ม BranchSearch.cs

ใส่ Control ดังรูป

Source code ฟอร์ม SearchBranch.cs

****************

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using POS.General;
using POS.Setup;
using POS.Classes.Info;
using POS.Classes.Sp;

namespace POS.Search
{
public partial class BranchSearch : Form
{
frmBranch _FrmBranch;

public BranchSearch(frmBranch f)
{
InitializeComponent();
_FrmBranch = f;
}

public void BranchSelect()
{
DataTable dtbl = new DataTable();
Branch_Sp spBranch = new Branch_Sp();
dtbl = spBranch.BranchSelect(PublicVariables.PCompanyCode.ToString(), string.Empty, string.Empty);
dgvBranch.DataSource = dtbl;

}
public void BranchSelectSearch()
{
DataTable dtbl = new DataTable();
Branch_Sp spBranch = new Branch_Sp();
if (cmbSearch.SelectedIndex == 0)
{
dtbl = spBranch.BranchSelect(PublicVariables.PCompanyCode.ToString(), txtSearch.Text.ToString(), string.Empty);
}
if (cmbSearch.SelectedIndex == 1)
{
dtbl = spBranch.BranchSelect(PublicVariables.PCompanyCode.ToString(), string.Empty ,txtSearch.Text.ToString());
}

dgvBranch.DataSource = dtbl;
}
private void BranchSearch_Load(object sender, EventArgs e)
{
dgvBranch.AutoGenerateColumns = false;
cmbSearch.SelectedIndex = 0;
lblCompany.Text = PublicVariables.PCompanyCode.ToString() + ” ” + PublicVariables.PCompanyName.ToString();
BranchSelect();
}

private void btnSearch_Click(object sender, EventArgs e)
{
BranchSelectSearch();
}

private void button1_Click(object sender, EventArgs e)
{
txtSearch.Clear();
}

private void dgvBranch_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
try
{
if (e.RowIndex != -1)
{
_FrmBranch._BranchCode = Convert.ToString(dgvBranch.CurrentRow.Cells[“BranchCode”].Value);
this.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, “Pos”, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
}

****************End SearchBranch

 

5. การทำงานของโปรแกรม

5.1 การสร้างข้อมูลสาขาใหม่

login เข้าสู่ระบบ

ในกรณีที่ในระบบมีข้อมูลหลักบริษัทมากกว่า 1 บริษัท ระบบจะแสดงรายชื่อบริษัทขึ้นมา ให้ดับเบิ้ลคลิ๊กที่บรรทัดสบริษัทที่ต้องการทำงาน

ไปที่ข้อมูลเมนูหลัก –> เลือกข้อมูลสาขา

กรอกข้อมูลสาขา

กดปุ่ม Save ถ้าไม่มีอะไรผิดพลาด ระบบจะแจ้งว่า สร้างข้อมูลหลักสาขาเรียบร้อย

 

5.2 การแก้ไขข้อมูลหลักสาขา

ไปที่เมนู ข้อมูลหลัก –>สาขา

คลิี๊กที่ปุ่ม Search

ระบบจะแสดงข้อมูลของสาขา ภายใต้รหัสบริษัทที่เรากำลังทำงาน

ดับเบิ้ลคลิ๊กบรรทัดสาขาที่ที่ต้องการแก้ไข

ปุ่ม Save จะเป็นเป็น Edit

ทำการแก้ไขข้อมูล กดปุ่ม Edit ถ้าไม่มีข้อผิดพลาด ระบบจะแสดงข้อความ “แก้ไขข้อมูลหลักสาขาเรียร้อย”

Add a Comment

Your email address will not be published. Required fields are marked *