public int AddImport(ImportDTO import)
{
ImportDTO x = new ImportDTO();
SqlCommand cmd = new SqlCommand("USP_INSERT_IMPORT", _conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@nvcIdImportProduct", import.IdImport);
cmd.Parameters.AddWithValue("@nvcIdOrderProduct", import.IdOrder);
cmd.Parameters.AddWithValue("@nvcTotalPrice", import.totalPrice);
cmd.Parameters.AddWithValue("@nvcCreatedDate", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));
cmd.Parameters.AddWithValue("@nvcStatus", 0);
cmd.Parameters.AddWithValue("@nvcIDPackage", import.idPackage);
cmd.Parameters.AddWithValue("@nvcIdUser", import.idUser);
var q = import.IdImport;
var w = import.IdOrder;
var e = import.totalPrice;
var r = import.idPackage;
var t = import.idUser;
_conn.Open();
int i = cmd.ExecuteNonQuery();
_conn.Close();
return(i);
}
public ImportDTO loadImportByID(int id)
{
//Step 1: connect to database
OleDbConnection connection = Connect();
//Step 2: get information by ID
string sql = "SELECT * FROM Import WHERE ID=@ID";
OleDbCommand command = new OleDbCommand(sql, connection);
OleDbParameter para = command.Parameters.Add("@ID", OleDbType.Integer);
para.Value = id;
OleDbDataReader reader = command.ExecuteReader();
ImportDTO result = new ImportDTO();
while (reader.Read())
{
result.ID = reader.GetInt32(0);
result.Product = reader.GetInt32(1);
result.Suplier = reader.GetInt32(2);
result.Amount = reader.GetInt32(3);
result.DateInput = reader.GetString(4);
result.Total = reader.GetInt32(5);
}
reader.Close();
// B3: Close connection
connection.Close();
return(result);
}
public List <ImportDTO> loadAllImport()
{
//Step 1: connect to database
OleDbConnection connection = Connect();
//Step 2: get information
string sql = "SELECT * FROM Import";
OleDbCommand command = new OleDbCommand(sql, connection);
OleDbDataReader reader = command.ExecuteReader();
List <ImportDTO> result = new List <ImportDTO>();
while (reader.Read())
{
ImportDTO import = new ImportDTO();
import.ID = reader.GetInt32(0);
import.Product = reader.GetInt32(1);
import.Suplier = reader.GetInt32(2);
import.Amount = reader.GetInt32(3);
import.DateInput = reader.GetString(4);
import.Total = reader.GetInt32(5);
result.Add(import);
}
reader.Close();
// B3: Close connection
connection.Close();
return(result);
}
public void updateImport(ImportDTO import)
{
//Step 1: connect to database
OleDbConnection connection = Connect();
//Step 2: update by ID
string sql = "UPDATE Import SET Product = @product, Suplier = @suplier, Amount = @amount, DateInput = @dateinput, Total = @total WHERE ID=@ID";
OleDbCommand command = new OleDbCommand(sql, connection);
OleDbParameter para = command.Parameters.Add("@product", OleDbType.Integer);
para.Value = import.Product;
para = command.Parameters.Add("@suplier", OleDbType.Integer);
para.Value = import.Suplier;
para = command.Parameters.Add("@amount", OleDbType.Integer);
para.Value = import.Amount;
para = command.Parameters.Add("@dateinput", OleDbType.VarChar);
para.Value = import.DateInput;
para = command.Parameters.Add("@total", OleDbType.Integer);
para.Value = import.Total;
para = command.Parameters.Add("@ID", OleDbType.Integer);
para.Value = import.ID;
OleDbDataReader reader = command.ExecuteReader();
reader.Close();
//Step 3: Close connection
connection.Close();
return;
}
public ImportDTO LoadOneImport(int id)
{
ImportDAO dao = new ImportDAO();
ImportDTO result = dao.loadImportByID(id);
return(result);
}
private void btnView_ButtonClick(object sender, DevExpress.XtraEditors.Controls.ButtonPressedEventArgs e)
{
tempDetailDTO = dsAll.Find(o => o.DID == gridView1.GetFocusedRowCellValue("DID").ToString());
if (tempDetailDTO != null)
{
lblDetail.Text = tempDetailDTO.Data.Tables[0].Rows[0]["SourceFileRefName"].ToString();
LoadDetailInFile(tempDetailDTO.Data.Tables[1]);
popDetail.Width = 1120;
popDetail.Show();
}
}
private void guna2Button1_Click(object sender, EventArgs e)
{
string idImport = txtIdImportDetail.Text;
string idUser = txtIdUserImportDetail.Text.Substring(0, 10);
string idOrder = txtIdOrderinImportDetail.Text;
string idPackage = txtIdPackage.Text;
string totalPrice = txtTotalPriceImport.Text;
var rowCount = dataGridImportDetail.RowCount;
if (rowCount > 0)
{
ImportDTO import = new ImportDTO(idImport, idOrder, idPackage, totalPrice, idUser);
var i = Ibus.AddImport(import);
List <ImportDetailDTO> listImport = new List <ImportDetailDTO>();
for (int index = 0; index < dataGridImportDetail.RowCount; index++)
{
DataGridViewRow row = this.dataGridImportDetail.Rows[index];
ImportDetailDTO importDetailDTO = new ImportDetailDTO();
importDetailDTO.IdImport = idImport;
importDetailDTO.IdProduct = row.Cells[0].Value.ToString();
importDetailDTO.amount = row.Cells[1].Value.ToString();
importDetailDTO.idUnit = row.Cells[2].Value.ToString();
importDetailDTO.idSuplier = row.Cells[3].Value.ToString();
importDetailDTO.price = row.Cells[4].Value.ToString();
importDetailDTO.intoPrice = row.Cells[5].Value.ToString();
DateTime oDate = Convert.ToDateTime(row.Cells[6].Value.ToString());
importDetailDTO.expired = oDate;
listImport.Add(importDetailDTO);
}
var result = Ibus.AddImportDetail(listImport);
if (result == 1 && i == 1)
{
var confimrOrder = Obus.ComfirmOrder(idOrder);
MessageBox.Show("Tạo chi tiết nhập hàng thành công", "Kho", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Tạo chi tiết nhập hàng thất bại", "Kho", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
private void btnImport_Clicked(object sender, RoutedEventArgs e)
{
if (txtAmount.Text.Count() == 0 || btnProduct.Content.ToString().Count() == 0 || btnSuplier.Content.ToString().Count() == 0)
{
lblNotice.Content = "Please fill all information.";
lblNotice.Visibility = Visibility.Visible;
return;
}
int amount;
bool isNumeric = Int32.TryParse(txtAmount.Text, out amount);
if (!isNumeric)
{
lblNotice.Content = "Amount isn't number!";
lblNotice.Visibility = Visibility.Visible;
return;
}
ProductBUS bus = new ProductBUS();
ProductDTO result = bus.LoadOneProduct(Global.IDProduct);
result.IvenNum = result.IvenNum + amount;
bus.UpdateProduct(result);
ImportDTO import = new ImportDTO();
import.Product = Int32.Parse(btnProduct.Content.ToString());
import.Suplier = Int32.Parse(btnSuplier.Content.ToString());
import.Amount = amount;
import.Total = amount * result.InputPrice;
import.DateInput = lblDate.Content.ToString();
ImportBUS importBUS = new ImportBUS();
importBUS.AddNewImport(import);
this.Close();
}
private void btnImport_Click(object sender, EventArgs e)
{
DataTable dtTImportHeader = ImportDTO.ImportHeaderData();
DataTable dtTImportDetail = ImportDTO.ImportDetailData();
importBal = new ImportTempBAL();
if (DevExpress.XtraEditors.XtraMessageBox.Show("Do you want to Import All?", "Import ALL", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
splashScreenManager1.ShowWaitForm();
DataRow drTImportHeader = dtTImportHeader.NewRow();
drTImportHeader["SourceFileHID"] = ReportSourceID;
drTImportHeader["TranDate"] = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString().PadLeft(2, '0') + DateTime.Now.Day.ToString().PadLeft(2, '0');
DateTime nowDate = DateTime.Now;
drTImportHeader["ReportName"] = System.IO.Path.GetFileName(txtSourceFile.Text);
drTImportHeader["CREATE_BY"] = MyLogin.USER_LOGIN;
drTImportHeader["Remark"] = txtRemark.Text;
dtTImportHeader.Rows.Add(drTImportHeader);
// @SourceFileHID_2[nvarchar](50),
//@SourceFileDID_3[nvarchar](50),
DataRow drTImportDetail = dtTImportDetail.NewRow();
drTImportHeader["SourceFileHID"] = ReportSourceID;
drTImportDetail["SourceFileDID"] = SourceMappingDID;
drTImportDetail["FilesCount"] = "1";
drTImportDetail["FileNameList"] = "";
drTImportDetail["HasImport"] = "Y";
drTImportDetail["ImportStatus"] = "P";
drTImportDetail["Remark"] = txtRemark.Text;
drTImportDetail["ImportedRec"] = dsAll[0].Data.Tables[0].Rows[0]["FromTotal"].ToString();
drTImportDetail["TotalRec"] = dsAll[0].Data.Tables[0].Rows[0]["FromTotal"].ToString();
drTImportDetail["CREATE_BY"] = MyLogin.USER_LOGIN;
dtTImportDetail.Rows.Add(drTImportDetail);
importBal.Import(dsAll, dtTImportHeader, dtTImportDetail);
splashScreenManager1.CloseWaitForm();
DevExpress.XtraEditors.XtraMessageBox.Show("Import All completely!!", "Import ALL", MessageBoxButtons.OK);
}
}
// 0: failed
// 1: success
public int AddNewImport(ImportDTO Import)
{
//Step 1: connect to database
OleDbConnection connection = Connect();
//Step 2: add to the database
string sql = "INSERT INTO Import (Product, Suplier, Amount, DateInput, Total) VALUES(@product, @suplier, @amount, @dateinput, @total)";
OleDbCommand command = new OleDbCommand(sql, connection);
OleDbParameter para = command.Parameters.Add("@product", OleDbType.Integer);
para.Value = Import.Product;
para = command.Parameters.Add("@suplier", OleDbType.Integer);
para.Value = Import.Suplier;
para = command.Parameters.Add("@amount", OleDbType.Integer);
para.Value = Import.Amount;
para = command.Parameters.Add("@dateinput", OleDbType.VarChar);
para.Value = Import.DateInput;
para = command.Parameters.Add("@total", OleDbType.Integer);
para.Value = Import.Total;
int count = command.ExecuteNonQuery();
if (count < 1)
{
count = 0;
}
else
{
count = 1;
}
// Step 3: Close connection
connection.Close();
return(count);
}
public bool ImportAll(List <ImportDTO> dataList)
{
isCan = false;
DataTable dtDetail = null;
try
{
string createBy = "";
OpenConection();
ExcecuteNoneQueryObj("sp_Temp_ClearAll", null);
isCan = true;
CloseConnection();
OpenConection();
string ImportID = Guid.NewGuid().ToString();
foreach (ImportDTO dto in dataList)
{
createBy = dto.CREATE_BY != null ? dto.CREATE_BY:"";
dto.Data.Tables[1].Columns.Add("ImportID");
foreach (DataRow dr in dto.Data.Tables[1].Rows)
{
dr["ImportID"] = ImportID;
}
if (dto.Data != null && dto.Data.Tables.Count > 1)
{
ExcecuteNoneQuery(string.Format("sp_Temp_{0}_Insert"
, dto.Data.Tables[0].Rows[0]["KeyWord"].ToString())
, dto.Data.Tables[1]);
}
}
/* if (dtImportHeader != null
* && dtImportHeader.Rows.Count > 0)
* {
* dtImportHeader.Rows[0]["ImportID"] = ImportID;
* ExcecuteNoneQuery("sp_T_ImportHeader_Insert", dtImportHeader);
* }
*
*
*
* if (dtImportDetail != null
* && dtImportDetail.Rows.Count > 0)
* {
* dtImportDetail.Rows[0]["ImportID"] = ImportID;
* ExcecuteNoneQuery("sp_T_ImportDetail_Insert", dtImportDetail);
* }*/
ImportDTO tempDTO = new ImportDTO();
tempDTO.ImportID = ImportID;
tempDTO.CREATE_BY = createBy;
ExcecuteNoneQueryObj("sp_Importing_TempToCustomer", tempDTO);
isCan = true;
CloseConnection();
}
catch (Exception ex)
{ }
finally
{ }
return(isCan);
}
public int AddImport(ImportDTO import)
{
return(dal.AddImport(import));
}
private void btnVerify_Click(object sender, EventArgs e)
{
allFile = new List <string>();
dsAll = new List <ImportDTO>();
DataTable tempHeaderDT, tempDetailDT;
ImportDTO importDTO = null;
string[] filePaths = null;
if (DevExpress.XtraEditors.XtraMessageBox.Show("Do you want to Verify Existing File?", "Verify Existing File", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
tempDetailDT = null;
splashScreenManager1.ShowWaitForm();
// allFile.AddRange(filePaths);
if (dtDetail != null && dtDetail.Rows.Count > 0)
{
int row = 0;
tempHeaderDT = new DataTable("Header");
tempHeaderDT = dtDetail.Clone();
ds = new DataSet();
DataView dvFilterColumns = new DataView(dtDetail_Col);
dvFilterColumns.RowFilter = string.Format("DID='{0}'", dvFilter[0]["DID"].ToString());
dvFilterColumns.Sort = "No asc";
SourceMappingDID = dvFilter[0]["DID"].ToString();
importDTO = new ImportDTO();
importDTO.HID = dvFilter[0]["HID"].ToString();
importDTO.DID = dvFilter[0]["DID"].ToString();
tempHeaderDT.Rows.Add(dvFilter[0].Row.ItemArray);
tempDetailDT = DTO.Util.DevExcelUtils.ConvertExcelToDataTable(txtFileName.Text, dvFilterColumns,
DevExpress.Spreadsheet.DocumentFormat.Xlsx,
DTO.Util.Converting.StringToInt(dvFilter[0]["StartRow"].ToString()),
DTO.Util.Converting.StringToInt(dvFilter[0]["EndRow"].ToString()),
dvFilter[0]["StartColumn"].ToString(),
dvFilter[0]["HID"].ToString(), dvFilter[0]["KeyWord"].ToString());
tempHeaderDT.Rows[0]["FromTotal"] = tempDetailDT.Rows.Count;
// tempHeaderDT.Rows[0]["Remark"] = txtRemark.Text;
ds.Tables.Add(tempHeaderDT.Copy());
ds.Tables.Add(tempDetailDT.Copy());
// ds.Tables.Add(dtTImportHeader.Copy());
LoadDetail(tempDetailDT);
importDTO.Data = ds.Copy();
dsAll.Add(importDTO);
splashScreenManager1.CloseWaitForm();
DevExpress.XtraEditors.XtraMessageBox.Show("Verify Existing File completely!!", "Verify Existing File", MessageBoxButtons.OK);
// dtDetail.Rows[0]["FileCount"] = "test";
}
else
{
splashScreenManager1.CloseWaitForm();
}
}
}
public void UpdateImport(ImportDTO import)
{
ImportDAO dao = new ImportDAO();
dao.updateImport(import);
}
public int AddNewImport(ImportDTO import)
{
ImportDAO dao = new ImportDAO();
return(dao.AddNewImport(import));
}
void ImportData()
{
DataTable tempHeaderDT, tempDetailDT;
ImportDTO importDTO = null;
string[] filePaths = System.IO.Directory.GetFiles(string.Format(@"{0}", browseFolder));
allFile.AddRange(filePaths);
if (dtDetail != null && dtDetail.Rows.Count > 0)
{
int row = 0;
foreach (DataRow dr in dtDetail.Rows)
{
SetupProgressBar();
string keyWord = dr["KeyWord"].ToString();
if (keyWord != "")
{
tempHeaderDT = new DataTable("Header");
tempHeaderDT = dtDetail.Clone();
List <string> selectFile = allFile.FindAll(f => f.Contains(keyWord));
if (selectFile != null && selectFile.Count > 0)
{
dr["FileCount"] = selectFile.Count;
dr["FileNameList"] = selectFile[0];
dr["Imported"] = "N";
tempHeaderDT.Rows.Add(dr.ItemArray);
importDTO = new ImportDTO();
importDTO.HID = dr["HID"].ToString();
importDTO.DID = dr["DID"].ToString();
ds = new DataSet();
pStatus.PerformStep();
pStatus.Update();
ds.Tables.Add(tempHeaderDT.Copy());
lblStatus.Text = string.Format("Loading : {0}", dr["SourceFileRefName"].ToString());
lblStatus.Refresh();
txtReportCondtion.Text = string.Format("{0}{1}", dr["SourceFileRefName"].ToString()
, dr["Desc"].ToString());
tempDetailDT = DTO.Util.DevExcelUtils.ConvertExcelToDataTable(selectFile[0],
DevExpress.Spreadsheet.DocumentFormat.Xlsx,
DTO.Util.Converting.StringToInt(dr["StartRow"].ToString()),
DTO.Util.Converting.StringToInt(dr["EndRow"].ToString()),
dr["StartColumn"].ToString(),
dr["HID"].ToString());
dr["Import"] = tempDetailDT.Rows.Count;
ds.Tables.Add(tempDetailDT.Copy());
dr["Imported"] = tempDetailDT.Rows.Count;
importDTO.Data = ds.Copy();
dsAll.Add(importDTO);
gridView1.SetRowCellValue(row, "FileCount", (object)dr["FileCount"].ToString());
gridView1.SetRowCellValue(row, "FileNameList", (object)dr["FileNameList"].ToString());
gridView1.SetRowCellValue(row, "Imported", (object)dr["Imported"].ToString());
}
}
pStatus.Increment(5);
pStatus.Update();
gridView1.MoveNext();
gridView1.RefreshData();
gdView.Refresh();
pStatus.Increment(10);
pStatus.Update();
row++;
LoadDetail(dtDetail);
// pStatus.Text = string.Format("Loading : {0}",dr["SourceFileRefName"].ToString());
System.Threading.Thread.Sleep(1000);
dtDetail.AcceptChanges();
}
lblStatus.Text = string.Format("Loading {0} files completetly!", allFile.Count);
// dtDetail.Rows[0]["FileCount"] = "test";
}
}
