public List <WarehouseItems> GetWarehouseOnly()
{
List <WarehouseItems> warehouseItems = new List <WarehouseItems>();
try
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(@" select distinct MC001, MC002 from CMSMC
order by MC001 ");
SqlTLVN2 sqlTLVN2 = new SqlTLVN2();
DataTable dt = new DataTable();
sqlTLVN2.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
warehouseItems.Add(new WarehouseItems
{
MC001_Wh = dt.Rows[i]["MC001"].ToString(),
MC002_WhName = dt.Rows[i]["MC002"].ToString(),
});
}
}
catch (Exception ex)
{
SystemLog.Output(SystemLog.MSG_TYPE.Err, "List<WarehouseItems> GetWarehouseItems()", ex.Message);
}
return(warehouseItems);
}
public bool CheckExistINVMC(Model.INVItems iNVItems)
{
try
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(" select * from INVMC ");
stringBuilder.Append(" where 1=1 ");
stringBuilder.Append(" and MC001 = '" + iNVItems.Product + "' ");
stringBuilder.Append(" and MC002 = '" + iNVItems.Warehouse + "' ");
SqlTLVN2 sqlTLVN2 = new SqlTLVN2();
DataTable dt = new DataTable();
sqlTLVN2.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt);
if (dt.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
SystemLog.Output(SystemLog.MSG_TYPE.Err, "CheckExtinctRow(string Table, string ID, string ID2)", ex.Message);
return(false);
}
}
public bool UpdateINVME(Model.INVItems iNVItems, DataTable dtCommonERP)
{
try
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(" update INVME ");
stringBuilder.Append(" set MODIFIER = '" + Class.valiballecommon.GetStorage().UserName + "', ");
stringBuilder.Append(" MODI_DATE = '" + DateTime.Now.ToString("yyyyMMdd") + "', ");
stringBuilder.Append(" FLAG = FLAG+1 , ");
stringBuilder.Append(" MODI_TIME = '" + DateTime.Now.ToString("HH:mm:ss") + "' ,");
stringBuilder.Append(" MODI_AP = '" + "SFT" + "' ,");
stringBuilder.Append(" MODI_PRID = '" + "MOCI05" + "' ");
stringBuilder.Append(" where ME001 ='" + iNVItems.Product + "' ");
stringBuilder.Append(" and ME002 ='" + iNVItems.Lot + "' ");
SqlTLVN2 sqlTLVN2 = new SqlTLVN2();
var result = sqlTLVN2.sqlExecuteNonQuery(stringBuilder.ToString(), false);
if (result == false)
{
SystemLog.Output(SystemLog.MSG_TYPE.War, "UpdateSFCTAForFinishedGoods(FinishedGoodsItems fgItems)", "");
return(false);
}
else
{
return(true);
}
}
catch (Exception ex)
{
SystemLog.Output(SystemLog.MSG_TYPE.Err, "UpdateSFCTAForFinishedGoods(FinishedGoodsItems fgItems, string TA003)", ex.Message);
}
return(false);
}
string GetFormat(string TF001)
{
string formatReturn = "";
string formatReturn_Temp = "";
///TB025 Tháng khai báo
///TB001 Loại phiếu chuyển
///TB002 Mã phiếu chuyển
///
string temp = @"select MQ006 from CMSMQ where MQ001 =@TF001";
string sqlQuerry = temp.Replace("@TF001", "'" + TF001.Trim() + "'");
SqlTLVN2 sqlTLVN2 = new SqlTLVN2();
formatReturn_Temp = sqlTLVN2.sqlExecuteScalarString(sqlQuerry).Trim();
if (formatReturn_Temp == "4")
{
formatReturn = "0001";
}
else if (formatReturn_Temp == "3")
{
formatReturn = "001";
}
else if (formatReturn_Temp == "5")
{
formatReturn = "00001";
}
else if (formatReturn_Temp == "6")
{
formatReturn = "000001";
}
return(formatReturn);
}
public string getTB002(string TB001)
{
string TB002 = "";
///TB025 Tháng khai báo
///TB001 Loại phiếu chuyển
///TB002 Mã phiếu chuyển
///
string format = GetFormat(TB001);
string temp = @" Declare @TF002 Varchar(20)
IF(NOT EXISTS(SELECT TOP 1 TF002 as MADON FROM MOCTF WHERE TF001 = @TF001 and SUBSTRING(TF003, 0, 7) = (SELECT LEFT(CONVERT(varchar, GetDate(), 112), 6)) UNION
SELECT TOP 1 TB002 as MADON FROM SFCTB WHERE TB001 = @TF001 and TB025 = (SELECT LEFT(CONVERT(varchar, GetDate(), 112), 6))))
SET @TF002 = (select convert(char(4), getdate(), 12)) + @Format_TT_Count
ELSE
SET @TF002 = (SELECT MAX(MaxValue) FROM
(SELECT ISNULL((MAX(TF002) + 1), 0) as MaxValue FROM MOCTF WHERE TF001 = @TF001 and SUBSTRING(TF003, 0, 7) = (SELECT LEFT(CONVERT(varchar, GetDate(), 112), 6))
UNION SELECT ISNULL(MAX(TB002) + 1, 0) as MaxValue FROM SFCTB WHERE TB001 = @TF001 and TB025 = (SELECT LEFT(CONVERT(varchar, GetDate(), 112), 6))) as T1)
select @TF002
";
string sqlQuerry = temp.Replace("@TF001", "'" + TB001.Trim() + "'").Replace("@Format_TT_Count", "'" + format.Trim() + "'");
//" select max(TB002)+1 from SFCTB where TB001 = '"+TB001+ "' and TB025 ='"+ DateTime.Now.ToString("yyyyMM")+"' " ;
SqlTLVN2 sqlTLVN2 = new SqlTLVN2();
TB002 = sqlTLVN2.sqlExecuteScalarString(sqlQuerry);
if (TB002 == "" || TB002 == string.Empty)
{
TB002 = DateTime.Now.ToString("yyMM") + "0001";
}
return(TB002);
}
public bool CheckExstinctRowSFCTA(string producCode, string TA003)
{
try
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(" select * from SFCTA ");
stringBuilder.Append(" where 1=1 ");
stringBuilder.Append(" and TA001 = '" + producCode.Split('-')[0] + "' ");
stringBuilder.Append(" and TA002 = '" + producCode.Split('-')[1] + "' ");
stringBuilder.Append(" and TA003 = '" + TA003 + "' ");
SqlTLVN2 sqlTLVN2 = new SqlTLVN2();
DataTable dt = new DataTable();
sqlTLVN2.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt);
if (dt.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
SystemLog.Output(SystemLog.MSG_TYPE.Err, "CheckExtinctRow(string Table, string ID, string ID2)", ex.Message);
return(false);
}
}
public static List <string> GetListForCombox()
{
string temp = null;
List <string> listReturn = new List <string>();
try
{
string m_query_MOCTF = @"select distinct TF001 from MOCTF"; /// included D301
string m_query_CMSMQ = @"select * from CMSMQ a where a.MQ003 like '%58%'"; /// None D301
SqlTLVN2 sqlTLVN2 = new SqlTLVN2();
DataTable dt = new DataTable();
sqlTLVN2.sqlDataAdapterFillDatatable(m_query_MOCTF, ref dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
listReturn.Add(dt.Rows[i]["TF001"].ToString().Trim());
}
dt = new DataTable();
sqlTLVN2.sqlDataAdapterFillDatatable(m_query_CMSMQ, ref dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
temp = dt.Rows[i]["MQ001"].ToString().Trim();
if (!listReturn.Contains(temp))
{
listReturn.Add(temp);
}
}
return(listReturn);
}
catch (Exception ex)
{
listReturn = new List <string>();
SystemLog.Output(SystemLog.MSG_TYPE.Err, "List<string> GetListDocNo()", ex.Message);
return(listReturn);
}
}
/// <summary>
/// 06042021 Cho trang thái hoàn công.
/// </summary>
/// <param name="PO"></param>
/// <returns></returns>
static public sql_CheckCondition.QueryResult Updatestatus_Product(string PO)
{
try
{
string fullText = File.ReadAllText(AppDomain.CurrentDomain.BaseDirectory + @"\NewQRcode\FileQuerySQL\Update_status_product.sql");
string script = null;
script = fullText.Replace("$VALUE_PO_NUMBER", PO);
SqlTLVN2 sqlTLVN2 = new SqlTLVN2();
string status = sqlTLVN2.sqlExecuteScalarString(script.ToString());
if (status == "End script with sucessful")
{
return(sql_CheckCondition.QueryResult.OK);
}
else
{
return(sql_CheckCondition.QueryResult.Exception);
}
}
catch (Exception ex)
{
SystemLog.Output(SystemLog.MSG_TYPE.Err, "Updatestatus_Product", "NameFile: " + "Update_status_product.sql" + "\n" + ex.Message);
return(sql_CheckCondition.QueryResult.Exception);
}
finally
{
conn.Close();
}
}
public List <WarehouseItems> GetWarehouseItems()
{
List <WarehouseItems> warehouseItems = new List <WarehouseItems>();
try
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(@" select MC001, MC002, ISNULL(NL002,'') as NL002, ISNULL(NL003,'') as NL003 ,ISNULL(NL004,'') as NL004 from CMSMC
left join CMSNL on NL001 = MC001
order by MC001");
SqlTLVN2 sqlTLVN2 = new SqlTLVN2();
DataTable dt = new DataTable();
sqlTLVN2.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
warehouseItems.Add(new WarehouseItems
{
MC001_Wh = dt.Rows[i]["MC001"].ToString(),
MC002_WhName = dt.Rows[i]["MC002"].ToString(),
NL002_Location = dt.Rows[i]["NL002"].ToString(),
NL003_LocationName = dt.Rows[i]["NL003"].ToString(),
NL004_LocationNote = dt.Rows[i]["NL004"].ToString(),
});
}
}
catch (Exception ex)
{
SystemLog.Output(SystemLog.MSG_TYPE.Err, "List<WarehouseItems> GetWarehouseItems()", ex.Message);
}
return(warehouseItems);
}
public DataTable GetProductionOrderStatus(string Code, string No, string STT)
{
DataTable dt = new DataTable();
ProductionOrderStatus POStatus = new ProductionOrderStatus();
try
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(" select * from MOCTA where 1=1 ");
stringBuilder.Append(" and TA026 ='" + Code + "' ");
stringBuilder.Append(" and TA027 = '" + No + "' ");
stringBuilder.Append(" and TA028 = '" + STT + "' ");
stringBuilder.Append(" and TA013 = '" + "Y" + "' ");
SqlTLVN2 sqlTLVN2 = new SqlTLVN2();
sqlTLVN2.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt);
if (dt.Rows.Count > 0)
{
}
}
catch (Exception ex)
{
SystemLog.Output(SystemLog.MSG_TYPE.Err, "GetProductionOrderStatus(string clientOrder)", ex.Message);
}
return(dt);
}
/// <summary>
/// INVMB - MB010 : có giá tri => có công đoạn ( trừ mã MB001 bắt dau bằng AD-, AT-, ED-, ET-, CD-, CT-)
/// </summary>
/// <param name="MB001_product"></param>
/// <returns></returns>
public static QueryResult Is_stageManagement(string MB001_product)
{
try
{
string temp = MB001_product.Trim().Substring(0, 3);
conn.Open();
string m_query_INVMB = @"select distinct MB011 from INVMB where MB001 = '" + MB001_product.Trim() + "'"; //
SqlTLVN2 sqlTLVN2 = new SqlTLVN2();
string GetString = sqlTLVN2.sqlExecuteScalarString(m_query_INVMB);
if (GetString != "" || GetString != string.Empty)
{
if (GetString != "A-02")
{
if (ListSpec.Contains(temp))// No managmentSTAGE
{
return(QueryResult.NG);
}
else
{
return(QueryResult.OK);
}
}
else
{
return(QueryResult.Exception_A_02);
}
}
else
{
return(QueryResult.NG);
}
}
//using (SqlCommand command = new SqlCommand(m_query_INVMB, conn))
//using (SqlDataReader reader = command.ExecuteReader())
//{
// // Kiểm tra có kết quả trả về
// if (reader.HasRows)
// {
// return QueryResult.OK;
// }
// else
// {
// return QueryResult.NG;
// }
//}
catch (Exception ex)
{
SystemLog.Output(SystemLog.MSG_TYPE.Err, "Is_stageManagement", ex.Message);
return(QueryResult.Exception);
}
finally
{
conn.Close();
}
}
public static double ConvertToWeightKg(string Model, double Weight)
{
double WeightKg = 0;
double Weight_MD003 = 0;
double Weight_MD004 = 0;
try
{
DataTable dt = new DataTable();
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(@"select MB001,MB004,MD004, MD003,MD002, MB091 from INVMB
left join INVMD on MB001 = MD001
where
((MB091 = 'Y'
and MD002 = 'KG' ) or (MB091 = 'N'
and MB004 = 'KG' )) ");
stringBuilder.Append(" and MB001 = '" + Model + "' ");
SqlTLVN2 con = new SqlTLVN2();
con.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt);
if (dt.Rows.Count == 1)
{
if (dt.Rows[0]["MB091"].ToString() == "Y")
{
Weight_MD003 = double.Parse(dt.Rows[0]["MD003"].ToString());
Weight_MD004 = double.Parse(dt.Rows[0]["MD004"].ToString());
if (Weight_MD003 == 0 || Weight_MD004 == 0)
{
return(0);
}
else
{
WeightKg = Weight * Weight_MD003 / Weight_MD004;
return(WeightKg);
}
}
else if (dt.Rows[0]["MB091"].ToString() == "N")
{
if (dt.Rows[0]["MB004"].ToString() == "KG")
{
return(Weight);
}
}
else
{
return(0);
}
}
else
{
return(0);
}
}
catch (Exception)
{
return(0);
}
return(0);
}
public static DataTable GetdtTop1COPTG()
{
DataTable dt = new DataTable();
string querry = " select top(1) * from COPTG ";
SqlTLVN2 sqlTLVN2 = new SqlTLVN2();
sqlTLVN2.sqlDataAdapterFillDatatable(querry, ref dt);
return(dt);
}
public static DataTable GetDataTableTienTe()
{
DataTable dt = new DataTable();
string querry = " select * from CMSMF ";
SqlTLVN2 sqlTLVN2 = new SqlTLVN2();
sqlTLVN2.sqlDataAdapterFillDatatable(querry, ref dt);
return(dt);
}
public DataTable GetDtADMFFByUser(string User)
{
DataTable dt = new DataTable();
string querry = " select * from ADMMF where MF001 ='" + User + "' ";
SqlTLVN2 sqlTLVN2 = new SqlTLVN2();
sqlTLVN2.sqlDataAdapterFillDatatable(querry, ref dt);
return(dt);
}
public DataTable GetDataTableSFCTB()
{
DataTable dt = new DataTable();
string sqlQuerry = "select top(1) * from SFCTB ";
SqlTLVN2 sqlTLVN2 = new SqlTLVN2();
sqlTLVN2.sqlDataAdapterFillDatatable(sqlQuerry, ref dt);
return(dt);
}
public static DataTable GetDataTableCThethong(string MQ003)
{
DataTable dt = new DataTable();
string querry = "select * from CMSMQ where MQ003 ='" + MQ003 + "' ";
SqlTLVN2 sqlTLVN2 = new SqlTLVN2();
sqlTLVN2.sqlDataAdapterFillDatatable(querry, ref dt);
return(dt);
}
public static DataTable GetDataTableKhachhangbyClientCode(string clientCode)
{
DataTable dt = new DataTable();
string querry = " select * from COPMA where MA001 = '" + clientCode + "' ";
SqlTLVN2 sqlTLVN2 = new SqlTLVN2();
sqlTLVN2.sqlDataAdapterFillDatatable(querry, ref dt);
return(dt);
}
public static bool IsHavePermisionUser(string User)
{
DataTable dt = new DataTable();
string querry = " select * from ADMMF where MF001 ='" + User + "' ";
SqlTLVN2 sqlTLVN2 = new SqlTLVN2();
sqlTLVN2.sqlDataAdapterFillDatatable(querry, ref dt);
if (dt.Rows.Count == 1)
{
return(true);
}
return(false);
}
public static DataTable GetdtConvetTienTe(string Currency, DateTime date)
{
DataTable dt = new DataTable();
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(@" SELECT * FROM CMSMG WHERE MG001 ='" + Currency + "' ");
stringBuilder.Append(" and MG002 = (select max(cast(MG002 as date)) from CMSMG where MG001 ='" + Currency + "' ");
stringBuilder.Append(" and cast(MG002 as date) < '" + date.ToString("yyyyMMdd") + "' )");
SqlTLVN2 sqlTLVN2 = new SqlTLVN2();
sqlTLVN2.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt);
return(dt);
}
public static DataTable GetDataTableSFCTA(string productCode)
{
DataTable dt = new DataTable();
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("select * from SFCTA where 1=1 and TA003 = '0020' ");
stringBuilder.Append(" and TA001 ='" + productCode.Split('-')[0] + "' ");
stringBuilder.Append(" and TA002 ='" + productCode.Split('-')[1] + "' ");
SqlTLVN2 sqlTLVN2 = new SqlTLVN2();
sqlTLVN2.sqlDataAdapterFillDatatable(stringBuilder.ToString(), ref dt);
return(dt);
}
public static string GetDescribeDocNo(string DocNo)
{
try
{
string m_query_CMSMQ = @"select distinct a.MQ002 from CMSMQ a where a.MQ001 ='" + DocNo.ToString() + "'";
SqlTLVN2 sqlTLVN2 = new SqlTLVN2();
return(sqlTLVN2.sqlExecuteScalarString(m_query_CMSMQ));
}
catch (Exception ex)
{
SystemLog.Output(SystemLog.MSG_TYPE.Err, "string GetDescribeDocNo()", ex.Message);
return(null);
}
}