public IActionResult Get([FromQuery] string transactionId)
{
MutasiAPIModel model = new MutasiAPIModel();
string noReturn = "";
List <MutasiAPIModel> listModel = new List <MutasiAPIModel>();
List <MutasiLineAPIModel> listModelLine = new List <MutasiLineAPIModel>();
List <ObjectAPIModel> listParam = new List <ObjectAPIModel>();
DBSQLConnect connect = new DBSQLConnect();
try
{
connect.sqlCon().Open();
string cmd = "select* from vInventoryTransaction where TransactionId = @TransactionId";
var inputDate = new ObjectAPIModel();
inputDate.param = "@TransactionId";
inputDate.typeValue = DbType.String;
inputDate.value = transactionId;
listParam.Add(inputDate);
connect.sqlDataRd = connect.ExecuteDataReaderWithParams(cmd, connect.sqlCon(), listParam);
if (connect.sqlDataRd.HasRows)
{
while (connect.sqlDataRd.Read())
{
if (noReturn == "" || noReturn != connect.sqlDataRd["Id"].ToString())
{
if (model.lines != null)
{
listModel.Add(model);
}
model = new MutasiAPIModel();
noReturn = connect.sqlDataRd["Id"].ToString();
model.noReturn = noReturn;
DateTime transDate;
if (DateTime.TryParse(connect.sqlDataRd["TransactionDate"].ToString(), out transDate))
{
model.tanggalRetur = transDate;
}
model.showroom = connect.sqlDataRd["Warehouse Original Name"].ToString();
model.showroomTujuan = connect.sqlDataRd["Warehouse Destination Name"].ToString();
model.keterangan = connect.sqlDataRd["Remarks"].ToString();
model.status = connect.sqlDataRd["Status"].ToString();
model.lines = new List <MutasiLineAPIModel>();
var line = new MutasiLineAPIModel();
line.articleId = connect.sqlDataRd["ArticleId"].ToString();
line.articleName = connect.sqlDataRd["ArticleName"].ToString();
line.color = connect.sqlDataRd["Color"].ToString();
line.size = connect.sqlDataRd["Size"].ToString();
Decimal qtyResult = 0;
if (Decimal.TryParse(connect.sqlDataRd["Qty"].ToString(), out qtyResult))
{
line.qty = qtyResult;
}
Decimal totalResult = 0;
if (Decimal.TryParse(connect.sqlDataRd["Total"].ToString(), out totalResult))
{
line.total = totalResult;
}
if (line.qty > 0 && line.total > 0)
{
line.price = line.total / line.qty;
}
model.lines.Add(line);
}
else
{
var line = new MutasiLineAPIModel();
line.articleId = connect.sqlDataRd["ArticleId"].ToString();
line.articleName = connect.sqlDataRd["ArticleName"].ToString();
line.color = connect.sqlDataRd["Color"].ToString();
line.size = connect.sqlDataRd["Size"].ToString();
Decimal qtyResult = 0;
if (Decimal.TryParse(connect.sqlDataRd["Qty"].ToString(), out qtyResult))
{
line.qty = qtyResult;
}
Decimal totalResult = 0;
if (Decimal.TryParse(connect.sqlDataRd["Total"].ToString(), out totalResult))
{
line.total = totalResult;
}
if (line.qty > 0 && line.total > 0)
{
line.price = line.total / line.qty;
}
model.lines.Add(line);
}
}
if (model.lines != null)
{
listModel.Add(model);
}
}
}
catch (Exception e)
{
return(Ok(e.Message));
}
finally
{
if (connect.sqlDataRd != null)
{
connect.sqlDataRd.Close();
}
if (connect.sqlCon().State == ConnectionState.Open)
{
connect.sqlCon().Close();
}
}
return(Ok(listModel));
}
public IActionResult Get([FromQuery] int reportType, [FromQuery] string transDate)
{
int actualSalesValueResult = 0;
int actualSalesQtyResult = 0;
int actualSalesLastValueResult = 0;
int actualSalesLastQtyResult = 0;
int dailtGrowthValueResult = 0;
int dailtGrowthQtyResult = 0;
int dailyActualValueResult = 0;
int focecastValueResult = 0;
int focecastQtyResult = 0;
int growthValueResult = 0;
int growthQtyResult = 0;
int targetQtyResult = 0;
int targetValueResult = 0;
List <ObjectAPIModel> listParam = new List <ObjectAPIModel>();
List <Object> listModel = new List <Object>();
DBSQLConnect connect = new DBSQLConnect();
var inputDate = new ObjectAPIModel();
inputDate.param = "@transDate";
inputDate.typeValue = DbType.String;
inputDate.value = transDate;
listParam.Add(inputDate);
try
{
if (reportType == DAILY_SALES)
{
connect.sqlCon().Open();
string cmd = "select top 100[StoreType], SUM(qty) as actualSalesQty, SUM(TotalAmounTransaction) as actualSalesValue, " +
"(select SUM(Qty) from vTransactionStore where (year(TransactionDate) = year(@transDate) - 1) and month(TransactionDate) = month(@transDate) and day(TransactionDate) = day(@transDate)) as actualSalesLastQty, " +
"(select SUM(TotalAmounTransaction) from vTransactionStore where (year(TransactionDate) = year(@transDate) - 1) and month(TransactionDate) = month(@transDate) and day(TransactionDate) = day(@transDate)) as actualSalesLastValue," +
"((SUM(qty) - (select SUM(Qty) from vTransactionStore where (year(TransactionDate) = year(@transDate) - 1) and month(TransactionDate) = month(@transDate) and day(TransactionDate) = day(@transDate)))/ (select SUM(Qty) from vTransactionStore where (year(TransactionDate) = year(@transDate) - 1) and month(TransactionDate) = month(@transDate) and day(TransactionDate) = day(@transDate))) *100 as dailtGrowthQty ," +
"((SUM(TotalAmounTransaction) - (select SUM(TotalAmounTransaction) from vTransactionStore where (year(TransactionDate) = year(@transDate) - 1) and month(TransactionDate) = month(@transDate) and day(TransactionDate) = day(@transDate)))/ (select SUM(TotalAmounTransaction) from vTransactionStore where (year(TransactionDate) = year(@transDate) - 1) and month(TransactionDate) = month(@transDate) and day(TransactionDate) = day(@transDate))) *100 as dailtGrowthValue," +
"(select sum(Target) from StoreTarget ) / SUM(TotalAmounTransaction) as dailyActualValue " +
" from vTransactionStore vts where Month(TransactionDate) = Month(@transDate) group by [StoreType] ";
connect.sqlDataRd = connect.ExecuteDataReaderWithParams(cmd, connect.sqlCon(), listParam);
if (connect.sqlDataRd.HasRows)
{
while (connect.sqlDataRd.Read())
{
int.TryParse(connect.sqlDataRd["actualSalesQty"].ToString(), out actualSalesQtyResult);
int.TryParse(connect.sqlDataRd["actualSalesValue"].ToString(), out actualSalesValueResult);
int.TryParse(connect.sqlDataRd["actualSalesLastValue"].ToString(), out actualSalesLastValueResult);
int.TryParse(connect.sqlDataRd["actualSalesLastQty"].ToString(), out actualSalesLastQtyResult);
int.TryParse(connect.sqlDataRd["dailtGrowthValue"].ToString(), out dailtGrowthValueResult);
int.TryParse(connect.sqlDataRd["dailtGrowthQty"].ToString(), out dailtGrowthQtyResult);
int.TryParse(connect.sqlDataRd["dailyActualValue"].ToString(), out dailyActualValueResult);
var model = new
{
storeType = connect.sqlDataRd["StoreType"].ToString(),
actualSalesValue = actualSalesValueResult,
actualSalesQty = actualSalesQtyResult,
actualSalesLastValue = actualSalesLastValueResult,
actualSalesLastQty = actualSalesLastQtyResult,
dailtGrowthValue = dailtGrowthValueResult,
dailtGrowthQty = dailtGrowthQtyResult,
dailyActualValue = dailyActualValueResult,
dailyActualQty = 0,
dailyActualCompValue = 0,
dailyActualCompQty = 0
};
listModel.Add(model);
}
}
}
else
if (reportType == FORECAST_SALES)
{
connect.sqlCon().Open();
string cmd = "select top 100[StoreType], " +
"((select SUM(Qty) from vTransactionStore vtsi where (year(TransactionDate) = year(@transDate)) and month(TransactionDate) = month(@transDate) and vtsi.StoreType = vts.StoreType)/ day(@transDate))*31 as focecastQty, " +
"((select SUM(TotalAmounTransaction) from vTransactionStore vtsi where (year(TransactionDate) = year(@transDate)) and month(TransactionDate) = month(@transDate) and vtsi.StoreType = vts.StoreType)/ day(@transDate))*31 as focecastValue, " +
"(select SUM(Qty) from vTransactionStore vtsi where (year(TransactionDate) = year(@transDate) - 1) and month(TransactionDate) = month(@transDate) and vtsi.StoreType = vts.StoreType) as actualSalesQty, " +
"(select SUM(TotalAmounTransaction) from vTransactionStore vtsi where (year(TransactionDate) = year(@transDate) - 1) and month(TransactionDate) = month(@transDate) and vtsi.StoreType = vts.StoreType) as actualSalesValue " +
"from vTransactionStore vts where Month(TransactionDate) = Month(@transDate) group by [StoreType]";;
connect.sqlDataRd = connect.ExecuteDataReaderWithParams(cmd, connect.sqlCon(), listParam);
if (connect.sqlDataRd.HasRows)
{
while (connect.sqlDataRd.Read())
{
int.TryParse(connect.sqlDataRd["actualSalesValue"].ToString(), out actualSalesValueResult);
int.TryParse(connect.sqlDataRd["actualSalesQty"].ToString(), out actualSalesQtyResult);
int.TryParse(connect.sqlDataRd["focecastValue"].ToString(), out focecastValueResult);
int.TryParse(connect.sqlDataRd["focecastQty"].ToString(), out focecastQtyResult);
int.TryParse(connect.sqlDataRd["growthValue"].ToString(), out growthValueResult);
int.TryParse(connect.sqlDataRd["growthQty"].ToString(), out growthQtyResult);
var model = new
{
storeType = connect.sqlDataRd["StoreType"].ToString(),
actualSalesValue = actualSalesValueResult, // connect.sqlDataRd["actualSalesValue"] == null ? 0 : Convert.ToInt32(connect.sqlDataRd["actualSalesValue"].ToString()),
actualSalesQty = actualSalesQtyResult, // connect.sqlDataRd["actualSalesQty"] == null ? 0 : Convert.ToInt32(connect.sqlDataRd["actualSalesQty"].ToString()),
focecastValue = focecastValueResult, //connect.sqlDataRd["focecastValue"] == null ? 0 : Convert.ToInt32(connect.sqlDataRd["focecastValue"].ToString()),
focecastQty = focecastQtyResult, //connect.sqlDataRd["focecastQty"] == null ? 0 : Convert.ToInt32(connect.sqlDataRd["focecastQty"].ToString()),
growthValue = growthValueResult, // connect.sqlDataRd["growthValue"] == null ? 0 : Convert.ToInt32(connect.sqlDataRd["growthValue"].ToString()),
growthQty = growthQtyResult //connect.sqlDataRd["growthQty"] == null ? 0 : Convert.ToInt32(connect.sqlDataRd["growthQty"].ToString())
};
}
}
}
else
if (reportType == STORE_TARGET)
{
connect.sqlCon().Open();
string cmd = "select top 100 [StoreType], " +
"(select sum(TargetQty) from VStoreTarget st where st.StoreType = vts.StoreType) as targetQty, " +
"(select sum(target) from VStoreTarget st where st.StoreType = vts.StoreType) as targetValue, " +
"((select SUM(Qty) from vTransactionStore vtsi where (year(TransactionDate) = year(@transDate)) and month(TransactionDate) = month(@transDate) and vtsi.StoreType = vts.StoreType)/ day(@transDate))*31 as focecastValue, " +
"((select SUM(TotalAmounTransaction) from vTransactionStore vtsi where (year(TransactionDate) = year(@transDate)) and month(TransactionDate) = month(@transDate) and vtsi.StoreType = vts.StoreType)/ day(@transDate))*31 as focecastValue " +
" from vTransactionStore vts where Month(TransactionDate) = Month(@transDate) group by [StoreType]";
connect.sqlDataRd = connect.ExecuteDataReaderWithParams(cmd, connect.sqlCon(), listParam);
if (connect.sqlDataRd.HasRows)
{
while (connect.sqlDataRd.Read())
{
int.TryParse(connect.sqlDataRd["targetQty"].ToString(), out targetQtyResult);
int.TryParse(connect.sqlDataRd["targetValue"].ToString(), out targetValueResult);
int.TryParse(connect.sqlDataRd["focecastValue"].ToString(), out focecastValueResult);
int.TryParse(connect.sqlDataRd["focecastQty"].ToString(), out focecastQtyResult);
var model = new
{
storeType = connect.sqlDataRd["StoreType"].ToString(),
targetQty = targetQtyResult, // connect.sqlDataRd["targetQty"] == null ? 0 : Convert.ToInt32(connect.sqlDataRd["targetQty"].ToString()),
targetValue = targetValueResult, //connect.sqlDataRd["targetValue"] == null ? 0 : Convert.ToInt32(connect.sqlDataRd["targetValue"].ToString()),
focecastValue = focecastValueResult, //connect.sqlDataRd["focecastValue"] == null ? 0 : Convert.ToInt32(connect.sqlDataRd["focecastValue"].ToString()),
focecastQty = focecastQtyResult //connect.sqlDataRd["focecastQty"] == null ? 0 : Convert.ToInt32(connect.sqlDataRd["focecastQty"].ToString())
};
}
}
}
else
if (reportType == REGIONAL_ACHIEVEMENT)
{
connect.sqlCon().Open();
string cmd = "select top 100 [regional] as regional, " +
" SUM(qty) as actualSalesQty," +
" SUM(TotalAmounTransaction)'Actual Sales Value', " +
" (select sum(TargetQty) from VStoreTarget st where st.regional = vts.regional) as targetQty, " +
" (select sum(target) from VStoreTarget st where st.regional = vts.regional) as targetValue, " +
" ((select SUM(Qty) from vTransactionStore vtsi where (year(TransactionDate) = year(@transDate)) and month(TransactionDate) = month(@transDate) and vtsi.regional = vts.regional)/ day(@transDate))*31 as focecastQty," +
" ((select SUM(TotalAmounTransaction) from vTransactionStore vtsi where (year(TransactionDate) = year(@transDate)) and month(TransactionDate) = month(@transDate) and vtsi.regional = vts.regional)/ day(@transDate))*31 as focecastValue " +
" from vTransactionStore vts where Month(TransactionDate) = Month(@transDate) and regional is not null group by[regional]";
connect.sqlDataRd = connect.ExecuteDataReaderWithParams(cmd, connect.sqlCon(), listParam);
if (connect.sqlDataRd.HasRows)
{
while (connect.sqlDataRd.Read())
{
int.TryParse(connect.sqlDataRd["actualSalesValue"].ToString(), out actualSalesValueResult);
int.TryParse(connect.sqlDataRd["actualSalesQty"].ToString(), out actualSalesQtyResult);
int.TryParse(connect.sqlDataRd["targetQty"].ToString(), out targetQtyResult);
int.TryParse(connect.sqlDataRd["targetValue"].ToString(), out targetValueResult);
int.TryParse(connect.sqlDataRd["focecastValue"].ToString(), out focecastValueResult);
int.TryParse(connect.sqlDataRd["focecastQty"].ToString(), out focecastQtyResult);
var model = new
{
regional = connect.sqlDataRd["regional"].ToString(),
actualSalesQty = actualSalesQtyResult, //connect.sqlDataRd["actualSalesQty"] == null ? 0 : Convert.ToInt32(connect.sqlDataRd["actualSalesQty"].ToString()),
actualSalesValue = actualSalesValueResult, //connect.sqlDataRd["actualSalesValue"] == null ? 0 : Convert.ToInt32(connect.sqlDataRd["actualSalesValue"].ToString()),
targetQty = targetQtyResult, //connect.sqlDataRd["targetQty"] == null ? 0 : Convert.ToInt32(connect.sqlDataRd["targetQty"].ToString()),
targetValue = targetValueResult, //connect.sqlDataRd["targetValue"] == null ? 0 : Convert.ToInt32(connect.sqlDataRd["targetValue"].ToString()),
focecastValue = focecastValueResult, //connect.sqlDataRd["focecastValue"] == null ? 0 : Convert.ToInt32(connect.sqlDataRd["focecastValue"].ToString()),
focecastQty = focecastQtyResult //connect.sqlDataRd["focecastQty"] == null ? 0 : Convert.ToInt32(connect.sqlDataRd["focecastQty"].ToString())
};
}
}
}
}
catch (Exception e)
{
return(Ok(e.Message));
}
finally
{
if (connect.sqlDataRd != null)
{
connect.sqlDataRd.Close();
}
if (connect.sqlCon().State == ConnectionState.Open)
{
connect.sqlCon().Close();
}
}
return(Ok(listModel));
}
public IActionResult Get([FromQuery] string transDate)
{
BasketSizeAPIModel model;
List <BasketSizeAPIModel> listModel = new List <BasketSizeAPIModel>();
List <ObjectAPIModel> listParam = new List <ObjectAPIModel>();
DBSQLConnect connect = new DBSQLConnect();
try
{
connect.sqlCon().Open();
string cmd = "select tr.StoreCode as Code, s.Name as Name, TransactionMin15 = (select count(*) from [Transaction] where day(TransactionDate) <= 15 and StoreCode = tr.StoreCode and Month(TransactionDate) = MONTH(@InputDate) and YEAR(TransactionDate) = YEAR(@InputDate))," +
"QtyMin15 = (select sum(Qty) from [Transaction] where day(TransactionDate) <= 15 and StoreCode = tr.StoreCode and Month(TransactionDate) = MONTH(@InputDate) and YEAR(TransactionDate) = YEAR(@InputDate)), " +
"TransactionPlus15 = (select count(*) from [Transaction] where day(TransactionDate) > 15 and StoreCode = tr.StoreCode and Month(TransactionDate) = MONTH(@InputDate) and YEAR(TransactionDate) = YEAR(@InputDate))," +
"QtyPlus15 = (select sum(Qty) from [Transaction] where day(TransactionDate) > 15 and StoreCode = tr.StoreCode and Month(TransactionDate) = MONTH(@InputDate) and YEAR(TransactionDate) = YEAR(@InputDate)) " +
"from [Transaction] tr inner join Store s on s.Code = tr.StoreCode group by tr.StoreCode, s.Name";
var inputDate = new ObjectAPIModel();
inputDate.param = "@InputDate";
inputDate.typeValue = DbType.Date;
inputDate.value = Convert.ToDateTime(transDate);
listParam.Add(inputDate);
connect.sqlDataRd = connect.ExecuteDataReaderWithParams(cmd, connect.sqlCon(), listParam);
if (connect.sqlDataRd.HasRows)
{
while (connect.sqlDataRd.Read())
{
model = new BasketSizeAPIModel();
model.code = connect.sqlDataRd["Code"].ToString();
model.name = connect.sqlDataRd["Name"] == null ? "" : connect.sqlDataRd["Name"].ToString();
model.trns1 = connect.sqlDataRd["TransactionMin15"] == null ? 0 : Convert.ToInt32(connect.sqlDataRd["TransactionMin15"].ToString());
int result = 0;
if (int.TryParse(connect.sqlDataRd["QtyMin15"].ToString(), out result))
{
model.qty1 = result;
if (result > 0)
{
model.bs1 = model.qty1 / model.trns1;
}
}
model.trns2 = connect.sqlDataRd["TransactionPlus15"] == null ? 0 : Convert.ToInt32(connect.sqlDataRd["TransactionPlus15"].ToString());
if (int.TryParse(connect.sqlDataRd["QtyPlus15"].ToString(), out result))
{
model.qty2 = result;
if (result > 0)
{
model.bs2 = model.qty2 / model.trns2;
}
}
model.grandTrns = model.trns1 + model.trns2;
model.grandQty = model.qty1 + model.qty2;
model.grandBS = model.bs1 + model.bs2;
listModel.Add(model);
}
}
}
catch (Exception e)
{
return(Ok(e.Message));
}
finally
{
if (connect.sqlDataRd != null)
{
connect.sqlDataRd.Close();
}
if (connect.sqlCon().State == ConnectionState.Open)
{
connect.sqlCon().Close();
}
}
return(Ok(listModel));
}