public static List <MIS_Header_Model> RetrieveData(SqlConnection connection, int idMISHeader)
{
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"
SELECT idMISHeader
,RequestNo
,Requestor
,ReferenceNo
,POCMNumber
,RequestDate
,PreparedBy
,Remarks
FROM a_MIS_Header
WHERE idMISHeader <> 0
");
if (idMISHeader != 0)
{
sQuery.Append("AND idMISHeader = @idMISHeader");
}
var lmodel = new List <MIS_Header_Model>();
connection.Open();
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
if (idMISHeader != 0)
{
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@idMISHeader",
SqlDbType = SqlDbType.Int,
Value = idMISHeader
};
cmd.Parameters.Add(parm2);
}
var oreader = cmd.ExecuteReader();
while (oreader.Read())
{
MIS_Header_Model oModel = new MIS_Header_Model
{
idMISHeader = (int)oreader["idMISHeader"],
RequestNo = (string)oreader["RequestNo"],
Requestor = (string)oreader["Requestor"],
ReferenceNo = (string)oreader["ReferenceNo"],
POCMNumber = (string)oreader["POCMNumber"],
RequestDate = (DateTime)oreader["RequestDate"],
PreparedBy = (string)oreader["PreparedBy"],
Remarks = (string)oreader["Remarks"]
};
lmodel.Add(oModel);
}
oreader.Close();
cmd.Dispose();
}
connection.Close();
return(lmodel);
}
public static int Save(SqlConnection connection, MIS_Header_Model model)
{
int returnValue = 0;
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"INSERT INTO a_MIS_Header
(RequestNo
,Requestor
,ReferenceNo
,POCMNumber
,RequestDate
,PreparedBy
,Remarks
)
VALUES
(@RequestNo
,@Requestor
,@ReferenceNo
,@POCMNumber
,@RequestDate
,@PreparedBy
,@Remarks
)
SELECT SCOPE_IDENTITY() as 'ID'
");
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@RequestNo",
SqlDbType = SqlDbType.NVarChar,
Value = model.RequestNo
};
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter
{
ParameterName = "@Requestor",
SqlDbType = SqlDbType.NVarChar,
Value = model.Requestor
};
cmd.Parameters.Add(parm3);
SqlParameter parm4 = new SqlParameter
{
ParameterName = "@ReferenceNo",
SqlDbType = SqlDbType.NVarChar,
Value = model.ReferenceNo
};
cmd.Parameters.Add(parm4);
SqlParameter parm5 = new SqlParameter
{
ParameterName = "@POCMNumber",
SqlDbType = SqlDbType.NVarChar,
Value = model.POCMNumber
};
cmd.Parameters.Add(parm5);
SqlParameter parm6 = new SqlParameter
{
ParameterName = "@RequestDate",
SqlDbType = SqlDbType.DateTime,
Value = model.RequestDate
};
cmd.Parameters.Add(parm6);
SqlParameter parm7 = new SqlParameter
{
ParameterName = "@PreparedBy",
SqlDbType = SqlDbType.NVarChar,
Value = model.PreparedBy
};
cmd.Parameters.Add(parm7);
SqlParameter parm11 = new SqlParameter
{
ParameterName = "@Remarks",
SqlDbType = SqlDbType.NVarChar,
Value = model.Remarks
};
cmd.Parameters.Add(parm11);
//if (cmd.ExecuteNonQuery() >= 1)
// returnValue = true;
var oreader = cmd.ExecuteReader();
try
{
while (oreader.Read())
{
returnValue = Convert.ToInt32(oreader["ID"].ToString());
}
oreader.Close();
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
catch
{
returnValue = 0;
oreader.Close();
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
return(returnValue);
}
public static bool Update(SqlConnection connection, MIS_Header_Model model)
{
bool returnValue = true;
StringBuilder sQuery = new StringBuilder();
try
{
sQuery.Append(@"UPDATE a_MIS_Header SET
RequestNo = @RequestNo
,Requestor = @Requestor
,ReferenceNo = @ReferenceNo
,POCMNumber = @POCMNumber
,RequestDate = @RequestDate
,PreparedBy = @PreparedBy
,Remarks = @Remarks
WHERE idMISHeader = @idMISHeader ");
connection.Open();
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm1 = new SqlParameter
{
ParameterName = "@idMISHeader",
SqlDbType = SqlDbType.Int,
Value = model.idMISHeader
};
cmd.Parameters.Add(parm1);
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@RequestNo",
SqlDbType = SqlDbType.NVarChar,
Value = model.RequestNo
};
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter
{
ParameterName = "@Requestor",
SqlDbType = SqlDbType.NVarChar,
Value = model.Requestor
};
cmd.Parameters.Add(parm3);
SqlParameter parm4 = new SqlParameter
{
ParameterName = "@ReferenceNo",
SqlDbType = SqlDbType.NVarChar,
Value = model.ReferenceNo
};
cmd.Parameters.Add(parm4);
SqlParameter parm5 = new SqlParameter
{
ParameterName = "@POCMNumber",
SqlDbType = SqlDbType.NVarChar,
Value = model.POCMNumber
};
cmd.Parameters.Add(parm5);
SqlParameter parm6 = new SqlParameter
{
ParameterName = "@RequestDate",
SqlDbType = SqlDbType.DateTime,
Value = model.RequestDate
};
cmd.Parameters.Add(parm6);
SqlParameter parm7 = new SqlParameter
{
ParameterName = "@PreparedBy",
SqlDbType = SqlDbType.NVarChar,
Value = model.PreparedBy
};
cmd.Parameters.Add(parm7);
SqlParameter parm11 = new SqlParameter
{
ParameterName = "@Remarks",
SqlDbType = SqlDbType.NVarChar,
Value = model.Remarks
};
cmd.Parameters.Add(parm11);
if (cmd.ExecuteNonQuery() >= 1)
{
returnValue = true;
}
cmd.Dispose();
cmd.Parameters.Clear();
}
connection.Close();
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
return(returnValue);
}
protected void btnSave_Click(object sender, EventArgs e)
{
MIS_Header_Model mIS_Header_Model = new MIS_Header_Model
{
RequestNo = txtRequestNo.Text,
ReferenceNo = txtRefNo.Text,
POCMNumber = txtPOCMNum.Text,
PreparedBy = txtPreparedBy.Text,
Remarks = txtRemarks.Text,
RequestDate = Convert.ToDateTime(txtDate.Text, System.Globalization.CultureInfo.InvariantCulture),
Requestor = txtRequestor.Text
};
int idmisheader = MIS_Header.Save(oCon, mIS_Header_Model);
DataTable dtTempData = (DataTable)Session["TempData"];
for (int x = 0; x <= dtTempData.Rows.Count - 1; x++)
{
int idItem = Convert.ToInt32(dtTempData.Rows[x][0].ToString());
string item_number = dtTempData.Rows[x][1].ToString();
int qty = Convert.ToInt32(dtTempData.Rows[x][2].ToString());
decimal cost = Convert.ToDecimal(dtTempData.Rows[x][3].ToString());
MIS_Detail_Model mIS_Detail_Model = new MIS_Detail_Model
{
idMISHeader = idmisheader,
Cost = cost,
Quantity = qty,
idItem = idItem
};
int idmisdetail = MIS_Detail.Save(oCon, mIS_Detail_Model);
string dtname = "Ser" + idItem.ToString() + "-" + item_number;
var dt = (DataTable)Session[dtname];
for (int y = 0; y <= dt.Rows.Count - 1; y++)
{
Item_Serial.UpdateStockStatus(oCon, dt.Rows[y][1].ToString(), "N");
MIS_Serial_Model mIS_Serial_Model = new MIS_Serial_Model
{
idMISDetail = idmisdetail,
idSerial = Convert.ToInt32(dt.Rows[y][0].ToString())
};
MIS_Serial.Save(oCon, mIS_Serial_Model);
Trans_History_Model trans_History_Model = new Trans_History_Model
{
Trans_Code = "MIS",
Item_Number = item_number,
Site = "WH-JMS",
UM = "UT",
Doc_No = txtRequestNo.Text,
Serial_No = dt.Rows[y][1].ToString(),
Reason_Code = "",
Trans_Date = DateTime.Now,
Order_No = "",
Invoice_No = "",
Reference_No = txtRefNo.Text,
Trans_Qty = -1,
Trans_Amt = cost,
Remarks = txtRemarks.Text,
user_domain = Session["User_Domain"].ToString()
};
Trans_History.Save(oCon, trans_History_Model);
}
}
Item_Master.InventoryCheckForError(oCon);
clearAll();
dvView.Visible = true;
dvCreate.Visible = false;
GetView();
HttpContext.Current.Response.Write("<script>alert(" + ControlChars.Quote + "MIS Saved!" + ControlChars.Quote + ");</script>");
}
